The demo data for GZ-1000 inventory seems to be
in specialpurpose/ecommerce/data/DemoProduct.xml
<ProductFacility productId="GZ-1000" facilityId="WebStoreWarehouse"
minimumStock="2" reorderQuantity="10" daysToShip="1"/>
<ProductFacilityLocation productId="GZ-1000"
facilityId="WebStoreWarehouse" locationSeqId="TLTLTLUL01" minimumStock="5"
moveQuantity="20"/>
<InventoryItem facilityId="WebStoreWarehouse" inventoryItemId="9026"
inventoryItemTypeId="NON_SERIAL_INV_ITEM" datetimeReceived="2008-08-01
08:00:00.000" productId="GZ-1000" ownerPartyId="Company"
currencyUomId="USD" unitCost="2.4"/>
<InventoryItemDetail inventoryItemId="9026"
inventoryItemDetailSeqId="0001" effectiveDate="2001-05-13 12:00:00.0"
availableToPromiseDiff="8" quantityOnHandDiff="8"
accountingQuantityDiff="8"/>
The dates are not consistent. I don't know if that matters. In the above
example, InventoryItem is in the future of IndentoryItemDetail.
On Sun, Aug 12, 2012 at 10:33 AM, Leonard Lin <[email protected]> wrote:
> Because of project deadline, I can't work full time on this issue.
> I'll keep developing using pgsql but I hope that on the side I can work on
> mysql things.
> but I post my findings so far, in case someone wants to join in the effort
> or has some ideas.
>
> Summary:
> - Use MySQl 5.6.6
> - Use JDBC Drivers (5.1.21)
>
> - framework\entity\fieldtype\**fieldtypemysql.xml (changed DATETIME to
> DATETIME(6), TIME to TIME(6))
> - framework\entity\config\**entityengine.xml
> change jdbc-uri to :jdbc:mysql://127.0.0.1/ofbiz?**
> autoReconnect=true&**useServerPrepStmts=true<http://127.0.0.1/ofbiz?autoReconnect=true&useServerPrepStmts=true>
> (workaround for:
> http://bugs.mysql.com/bug.php?**id=60584<http://bugs.mysql.com/bug.php?id=60584>
> )
> - rebuild database (run-install)
>
> Result:
> - JDBC writes now milliseconds in the database
> - JDBC also reads milliseconds (verified using debugger, intercepting
> entity engine calls)
> -> inventory table: still whack
>
> If I do a receive_inventory, I see that this sql is created:
> INSERT INTO INVENTORY_ITEM (INVENTORY_ITEM_ID, INVENTORY_ITEM_TYPE_ID,
> PRODUCT_ID, PARTY_ID, OWNER_PARTY_ID, STATUS_ID, DATETIME_RECEIVED,
> DATETIME_MANUFACTURED, EXPIRE_DATE, FACILITY_ID, CONTAINER_ID, LOT_ID,
> UOM_ID, BIN_NUMBER, LOCATION_SEQ_ID, COMMENTS, QUANTITY_ON_HAND_TOTAL,
> AVAILABLE_TO_PROMISE_TOTAL, ACCOUNTING_QUANTITY_TOTAL, QUANTITY_ON_HAND,
> AVAILABLE_TO_PROMISE, SERIAL_NUMBER, SOFT_IDENTIFIER, ACTIVATION_NUMBER,
> ACTIVATION_VALID_THRU, UNIT_COST, CURRENCY_UOM_ID, LAST_UPDATED_STAMP,
> LAST_UPDATED_TX_STAMP, CREATED_STAMP, CREATED_TX_STAMP) VALUES ('10000',
> 'NON_SERIAL_INV_ITEM', 'GZ-1000', NULL, 'Company', NULL, '2012-08-12
> 10:01:31', NULL, NULL, 'WebStoreWarehouse', NULL, NULL, NULL, NULL,
> 'TLTLTLUL01', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL,
> 3.75, 'USD', '2012-08-12 10:01:37', '2012-08-12 10:01:36', '2012-08-12
> 10:01:37', '2012-08-12 10:01:36')
> // the time fields are displayed without microseconds, but they are stored
> in the DB
> // qoh/atp=NULL
>
> The mini-lang code generating this statement is:
> InventoryServices.xml
> <simple-method method-name="**createInventoryItem"
> short-description="Create an InventoryItem">
>
> <make-value value-field="inventoryItem"
> entity-name="InventoryItem"/>
> <!-- TODO: make sure availableToPromiseTotal and
> quantityOnHandTotal are not changed -->
> <set-nonpk-fields map="parameters" value-field="inventoryItem"/>
>
> <call-simple-method method-name="**inventoryItemCheckSetDefaultVa*
> *lues"/>
> <check-errors/>
>
> <sequenced-id sequence-name="InventoryItem" field="inventoryItem.*
> *inventoryItemId"/>
> <create-value value-field="inventoryItem"/>
> <field-to-result field="inventoryItem.**inventoryItemId"
> result-name="inventoryItemId"/**>
> </simple-method>
>
> Which is in turn called by:
> ShipmentReceipt.xml: receiveInventoryProduct
> I can't find any line in there setting qoh/atp.
>
> - What's the best way to debug mini-lang code? Log statements? there are
> quiet a few fields and parameter-maps I'd like to examine.
> - Can someone tell me how demo data is inserted? (direct sql calls,
> mini-lang calls or entity makeValue calls?) I couldn't find the demo data
> for inventory_item in the whole installation dir )-;
> - I'm thinking if the demo-data seeding already creates an invalid
> state, so the subsequent operations on the table fail anyway and I'd like
> to trace/fix that part first.
>
> The same code works for pgsql/derby so the only difference is on level
> JDBC and below, but that part I just verified. I therefore think there must
> be something else at work too (field type conversion, null-handling or
> something).
>
>
>
> On 8/11/2012 10:40, Leonard Lin wrote:
>
>> Hi guys,
>>
>> Sorry for my stubbornness. I was testing different database yesterday and
>> need some help.
>> With some help, I think we may be able to bring MySQL back on the map.
>>
>> My criteria of success were
>> - After "ant run-install" the Inventory_Item table has inventory with
>> qoh/atp<>NULL
>> - I can do a "receive inventory" and the QOH/ATP reflects the change
>>
>> I used Postgres 9.1 and default Derby, both of them handle inventory
>> correctly.
>>
>> MySQL (given the time precision problem):
>> - installed 5.6.6-m9
>> - installed latest jdbc connector 5.1.21 (according to posts this should
>> handle microsecond)
>> - framework\entity\fieldtype\**fieldtypemysql.xml (changed DATETIME to
>> DATETIME(6), TIME to TIME(6))
>>
>> I drop the database and rerun the "ant run-install"
>> when i look at "InventoryItem" it is still the same as with MySQL 5.5.
>> All entries have QOH/ATP=NULL,except one item has QOH/ATP=2 (that was with
>> MySQL 5.5 already)
>>
>> when I try to receive inventory now however I get a different error
>> message:
>> ----start copy---------------
>>
>> Error with comparison in if-compare-field between fields
>> [.reserveQtyAccum] with value [0.0] and
>> [.inventoryItem.**quantityOnHandTotal]
>> with value [null] with operator [less] and type [Double]:
>>
>> Right value is null, cannot complete compare for the operator less
>>
>> ----end copy---------------
>>
>> - Is there a way to manually trigger the demo-data import for inventory
>> data (recreating tables on mysql takes 2-3hours)
>> - What's the best way to trace/debug mini-language / db calls?
>> - any other advise?
>>
>> My feeling is: the write inventory to db method doesn't work. Either the
>> reading current stock to make alterations or the writing of it fails.
>> But since the change in DATATYPE creates now a different error message
>> I'm hopeful that milliseconds now do work and there are other things that
>> aft strangely.
>>
>> Thanks for any help
>>
>>
>> On 8/10/2012 16:54, Ruth Hoffman wrote:
>>
>>> Hi Leonard:
>>> It doesn't have to be part of the primary-key to be a problem.
>>>
>>> If your query includes any Timestamp fields, it could fail to retrieve
>>> the correct values. For example, if the query is searching on
>>> INVENTORY_ITEM.expireDate, (which is a Timestamp field) it could fail to
>>> find the correct INVENTORY_ITEM record.
>>>
>>> From what I have heard, this is still an issue with version 5.6.
>>>
>>> Regards,
>>> Ruth Hoffman
>>>
>>> On 8/10/12 7:29 PM, Leonard Lin wrote:
>>>
>>>> Thanks for the hint.
>>>>
>>>> I just checked.
>>>> -I'm not getting any sql exception
>>>> -timestamp is not part of the key in inventory_item
>>>> however it might be query relying on getting only 1 item.
>>>> - Just to make sure I installed Mysql 5.6, unfortunately it didn't help.
>>>>
>>>> I'm thinking it is a writing issue of columns not rows.
>>>> inventory_item does create new lines for new bookings (unlike for
>>>> key-violation issue)
>>>> however the columns ATP/QOH have value 'NULL'
>>>>
>>>> I checked if it could by a reading issue and I set the values all to
>>>> '0' zero, but it didn't help.
>>>>
>>>>
>>>> On 8/10/2012 14:57, Jacques Le Roux wrote:
>>>>
>>>>> Not sure about it, but could related to MySQL 5.5 see
>>>>> http://markmail.org/message/**r27vavebgy3rnp2s<http://markmail.org/message/r27vavebgy3rnp2s>
>>>>> http://markmail.org/message/**4be7wsehyxpddnrv<http://markmail.org/message/4be7wsehyxpddnrv>
>>>>>
>>>>> You can find more using http://ofbiz.markmail.org/**
>>>>> search/?q=MySQL+milliseconds<http://ofbiz.markmail.org/search/?q=MySQL+milliseconds>
>>>>>
>>>>> Jacques
>>>>>
>>>>> From: "Leonard Lin" <[email protected]>
>>>>>
>>>>>> I changed my 10.04.02 version to back to derby
>>>>>>
>>>>>> and see there the inventory shows up correctly.
>>>>>>
>>>>>> I'm using MySQL 5.5 on Windows with latest java connector 5.1.21.jar
>>>>>>
>>>>>> are there any known configuration issues with MySQL that may lead to
>>>>>> this problem?
>>>>>>
>>>>>> On 8/10/2012 09:34, Leonard Lin wrote:
>>>>>>
>>>>>>> I have hardcoded the line like this:
>>>>>>> <find-by-and entity-name="InventoryItem" map="lookupFieldMap"
>>>>>>> list="inventoryItems" use-iterator="true" use-cache="false"/>
>>>>>>>
>>>>>>> no change after restart,
>>>>>>> flushed cache.
>>>>>>>
>>>>>>> Is it maybe a db thing?
>>>>>>> I use on the 10.04.02 version mysql
>>>>>>> for the trunk I use derby.
>>>>>>>
>>>>>>> On 8/10/2012 09:20, Mike wrote:
>>>>>>>
>>>>>>>> Just curious. Have you tried the 10.04.02 version with just
>>>>>>>> "cache=false"?
>>>>>>>>
>>>>>>>> On Fri, Aug 10, 2012 at 9:14 AM, Leonard Lin <[email protected]>
>>>>>>>> wrote:
>>>>>>>>
>>>>>>>> Hi,
>>>>>>>>>
>>>>>>>>> Sorry I forgot to mention that I'm using 10.04.02.
>>>>>>>>> It looks kind of similar to:
>>>>>>>>> http://markmail.org/message/****lnyki6w2vmrmhgup<http://markmail.org/message/**lnyki6w2vmrmhgup>
>>>>>>>>> <http://**markmail.org/message/**lnyki6w2vmrmhgup<http://markmail.org/message/lnyki6w2vmrmhgup>>
>>>>>>>>>
>>>>>>>>> http://markmail.org/message/****ldbyr5sm5jpthcea<http://markmail.org/message/**ldbyr5sm5jpthcea>
>>>>>>>>> <http://**markmail.org/message/**ldbyr5sm5jpthcea<http://markmail.org/message/ldbyr5sm5jpthcea>>
>>>>>>>>>
>>>>>>>>>
>>>>>>>>> I compared the patch from that mentioned thread to the 10.04.02,
>>>>>>>>> as far as
>>>>>>>>> I can tell, the changes are included in 10.04.02
>>>>>>>>>
>>>>>>>>> I downloaded ofbiz-trunk-2012-08-09-****v1371163 and checked
>>>>>>>>> there.
>>>>>>>>> The inventory shows up there correctly and inventory bookings work
>>>>>>>>> as
>>>>>>>>> expected.
>>>>>>>>>
>>>>>>>>> I compared the InventoryServices.xml
>>>>>>>>>
>>>>>>>>> Trunk Version:
>>>>>>>>> <if-compare field="parameters.useCache" operator="equals"
>>>>>>>>> value="true" type="Boolean">
>>>>>>>>> <!-- if caching was requested, don't use an iterator
>>>>>>>>> -->
>>>>>>>>> <find-by-and entity-name="InventoryItem"
>>>>>>>>> map="lookupFieldMap"
>>>>>>>>> list="inventoryItems" use-cache="true"/>
>>>>>>>>> <else>
>>>>>>>>> <find-by-and entity-name="InventoryItem"
>>>>>>>>> map="lookupFieldMap" list="inventoryItems" use-iterator="true"
>>>>>>>>> use-cache="false"/>
>>>>>>>>> </else>
>>>>>>>>> </if-compare>
>>>>>>>>>
>>>>>>>>>
>>>>>>>>> <if-compare field="inventoryItem.statusId"
>>>>>>>>> operator="equals"
>>>>>>>>> value="INV_NS_RETURNED"/>
>>>>>>>>>
>>>>>>>>> 10.04.02 version has:
>>>>>>>>> <find-by-and entity-name="InventoryItem" map="lookupFieldMap"
>>>>>>>>> list="inventoryItems" use-iterator="true"
>>>>>>>>> use-cache="${parameters.**
>>>>>>>>> useCache}"/>
>>>>>>>>>
>>>>>>>>> I applied the differences to my 10.04.02 installation, restarted.
>>>>>>>>> but still same results.
>>>>>>>>>
>>>>>>>>> Can someone give me any recommendation?
>>>>>>>>> I assume it is an known issue?
>>>>>>>>> Should I just go with the trunk version?
>>>>>>>>>
>>>>>>>>>
>>>>>>>>> Thanks in advance for any advice
>>>>>>>>>
>>>>>>>>> Leonard
>>>>>>>>>
>>>>>>>>>
>>>>>>>>>
>>>>>>>>> On 8/9/2012 15:53, Leonard Lin wrote:
>>>>>>>>>
>>>>>>>>> Hi,
>>>>>>>>>>
>>>>>>>>>> I've been using the "Getting Started With Apache OFBiz
>>>>>>>>>> Manufacturing &
>>>>>>>>>> MRP In 5 Easy Steps"
>>>>>>>>>> guide. After running MRP, Unfortunately I can't get the Inventory
>>>>>>>>>> to work.
>>>>>>>>>>
>>>>>>>>>> When I go to:
>>>>>>>>>> Facility Manager->Facilities->(Select my
>>>>>>>>>> Facility)->Inventory->Click on
>>>>>>>>>> "Find"
>>>>>>>>>> it always shows for all products QoH = 0 and ATP = 0.
>>>>>>>>>>
>>>>>>>>>> I tried "physical inventory", "receive inventory" through
>>>>>>>>>> purchase orders
>>>>>>>>>> as well as"receive inventory" without purchase order.
>>>>>>>>>> - I do see Inventory Items created
>>>>>>>>>> - I see in Inventory Items Detail:
>>>>>>>>>> Quantity on Hand Diff: 150 (the right amount)
>>>>>>>>>> Available To Promise Diff: 150
>>>>>>>>>>
>>>>>>>>>> But the Inventory Tab and the "Inventory Item Totals" Tab always
>>>>>>>>>> remains
>>>>>>>>>> QoH=(Empty), ATP=(Empty)
>>>>>>>>>>
>>>>>>>>>> Subsequently my production job doesn't run.
>>>>>>>>>>
>>>>>>>>>> Could someone help me telling me what I'm do wrong?
>>>>>>>>>>
>>>>>>>>>> Thanks in advance for any help
>>>>>>>>>>
>>>>>>>>>> Best
>>>>>>>>>> Leonard
>>>>>>>>>>
>>>>>>>>>>
>>>>>>>>>> --
>>>>>>>>>
>>>>>>>>> Leonard Lin
>>>>>>>>> Email: [email protected]
>>>>>>>>>
>>>>>>>>> 1136 N Larrabee Street, Apt 425
>>>>>>>>> West Hollywood, CA 90069
>>>>>>>>> Phone: +1 (213) 249 3260
>>>>>>>>>
>>>>>>>>> Am Pfisterhölzli 2
>>>>>>>>> CH - 8606 Greifensee
>>>>>>>>> Switzerland
>>>>>>>>> Phone: +41 (0)79 416 01 07
>>>>>>>>>
>>>>>>>>>
>>>>>>>>>
>>>>>>>
>>>>>> --
>>>>>>
>>>>>> Leonard Lin
>>>>>> Email: [email protected]
>>>>>>
>>>>>> 1136 N Larrabee Street, Apt 425
>>>>>> West Hollywood, CA 90069
>>>>>> Phone: +1 (213) 249 3260
>>>>>>
>>>>>> Am Pfisterhölzli 2
>>>>>> CH - 8606 Greifensee
>>>>>> Switzerland
>>>>>> Phone: +41 (0)79 416 01 07
>>>>>>
>>>>>>
>>>>
>>>
>>
> --
>
> Leonard Lin
> Email: [email protected]
>
> 1136 N Larrabee Street, Apt 425
> West Hollywood, CA 90069
> Phone: +1 (213) 249 3260
>
> Am Pfisterhölzli 2
> CH - 8606 Greifensee
> Switzerland
> Phone: +41 (0)79 416 01 07
>
>
