Inventory QOH/ATP always 0

[Leonard Lin-2]

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:    [hidden email]

1136 N Larrabee Street, Apt 425
West Hollywood, CA 90069
Phone:  [phone]

Am Pfisterhölzli 2
CH - 8606 Greifensee
Switzerland
Phone:  [phone]

[Leonard Lin-2]

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/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:    [hidden email]

1136 N Larrabee Street, Apt 425
West Hollywood, CA 90069
Phone:  [phone]

Am Pfisterhölzli 2
CH - 8606 Greifensee
Switzerland
Phone:  [phone]

[Mike Z]

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 <[hidden email]> 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/**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:    [hidden email]
>
> 1136 N Larrabee Street, Apt 425
> West Hollywood, CA 90069
> Phone:  [phone]
>
> Am Pfisterhölzli 2
> CH - 8606 Greifensee
> Switzerland
> Phone:  [phone]
>
>

[Leonard Lin-2]

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 <[hidden email]> 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/**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:    [hidden email]
>>
>> 1136 N Larrabee Street, Apt 425
>> West Hollywood, CA 90069
>> Phone:  [phone]
>>
>> Am Pfisterhölzli 2
>> CH - 8606 Greifensee
>> Switzerland
>> Phone:  [phone]
>>
>>

--

Leonard Lin
Email:    [hidden email]

1136 N Larrabee Street, Apt 425
West Hollywood, CA 90069
Phone:  [phone]

Am Pfisterhölzli 2
CH - 8606 Greifensee
Switzerland
Phone:  [phone]

[Leonard Lin-2]

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 <[hidden email]>
>> 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/**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:    [hidden email]
>>>
>>> 1136 N Larrabee Street, Apt 425
>>> West Hollywood, CA 90069
>>> Phone:  [phone]
>>>
>>> Am Pfisterhölzli 2
>>> CH - 8606 Greifensee
>>> Switzerland
>>> Phone:  [phone]
>>>
>>>
>

--

Leonard Lin
Email:    [hidden email]

1136 N Larrabee Street, Apt 425
West Hollywood, CA 90069
Phone:  [phone]

Am Pfisterhölzli 2
CH - 8606 Greifensee
Switzerland
Phone:  [phone]

[Jacques Le Roux]

Not sure about it, but could related to MySQL 5.5 see
http://markmail.org/message/r27vavebgy3rnp2s
http://markmail.org/message/4be7wsehyxpddnrv

You can find more using http://ofbiz.markmail.org/search/?q=MySQL+milliseconds

Jacques

From: "Leonard Lin" <[hidden email]>

>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 <[hidden email]> 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/**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:    [hidden email]
>>>>
>>>> 1136 N Larrabee Street, Apt 425
>>>> West Hollywood, CA 90069
>>>> Phone:  [phone]
>>>>
>>>> Am Pfisterhölzli 2
>>>> CH - 8606 Greifensee
>>>> Switzerland
>>>> Phone:  [phone]
>>>>
>>>>
>>
>
> --
>
> Leonard Lin
> Email:    [hidden email]
>
> 1136 N Larrabee Street, Apt 425
> West Hollywood, CA 90069
> Phone:  [phone]
>
> Am Pfisterhölzli 2
> CH - 8606 Greifensee
> Switzerland
> Phone:  [phone]
>

[Leonard Lin-2]

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/4be7wsehyxpddnrv
>
> You can find more using
> http://ofbiz.markmail.org/search/?q=MySQL+milliseconds
>
> Jacques
>
> From: "Leonard Lin" <[hidden email]>
>> 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 <[hidden email]>
>>>> 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/**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:    [hidden email]
>>>>>
>>>>> 1136 N Larrabee Street, Apt 425
>>>>> West Hollywood, CA 90069
>>>>> Phone:  [phone]
>>>>>
>>>>> Am Pfisterhölzli 2
>>>>> CH - 8606 Greifensee
>>>>> Switzerland
>>>>> Phone:  [phone]
>>>>>
>>>>>
>>>
>>
>> --
>>
>> Leonard Lin
>> Email:    [hidden email]
>>
>> 1136 N Larrabee Street, Apt 425
>> West Hollywood, CA 90069
>> Phone:  [phone]
>>
>> Am Pfisterhölzli 2
>> CH - 8606 Greifensee
>> Switzerland
>> Phone:  [phone]
>>

--

Leonard Lin
Email:    [hidden email]

1136 N Larrabee Street, Apt 425
West Hollywood, CA 90069
Phone:  [phone]

Am Pfisterhölzli 2
CH - 8606 Greifensee
Switzerland
Phone:  [phone]

[Ruth Hoffman-2]

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/4be7wsehyxpddnrv
>>
>> You can find more using
>> http://ofbiz.markmail.org/search/?q=MySQL+milliseconds
>>
>> Jacques
>>
>> From: "Leonard Lin" <[hidden email]>
>>> 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 <[hidden email]>
>>>>> 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/**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:    [hidden email]
>>>>>>
>>>>>> 1136 N Larrabee Street, Apt 425
>>>>>> West Hollywood, CA 90069
>>>>>> Phone:  [phone]
>>>>>>
>>>>>> Am Pfisterhölzli 2
>>>>>> CH - 8606 Greifensee
>>>>>> Switzerland
>>>>>> Phone:  [phone]
>>>>>>
>>>>>>
>>>>
>>>
>>> --
>>>
>>> Leonard Lin
>>> Email:    [hidden email]
>>>
>>> 1136 N Larrabee Street, Apt 425
>>> West Hollywood, CA 90069
>>> Phone:  [phone]
>>>
>>> Am Pfisterhölzli 2
>>> CH - 8606 Greifensee
>>> Switzerland
>>> Phone:  [phone]
>>>
>

[Leonard Lin-2]

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/4be7wsehyxpddnrv
>>>
>>> You can find more using
>>> http://ofbiz.markmail.org/search/?q=MySQL+milliseconds
>>>
>>> Jacques
>>>
>>> From: "Leonard Lin" <[hidden email]>
>>>> 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
>>>>>> <[hidden email]> 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/**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:    [hidden email]
>>>>>>>
>>>>>>> 1136 N Larrabee Street, Apt 425
>>>>>>> West Hollywood, CA 90069
>>>>>>> Phone:  [phone]
>>>>>>>
>>>>>>> Am Pfisterhölzli 2
>>>>>>> CH - 8606 Greifensee
>>>>>>> Switzerland
>>>>>>> Phone:  [phone]
>>>>>>>
>>>>>>>
>>>>>
>>>>
>>>> --
>>>>
>>>> Leonard Lin
>>>> Email:    [hidden email]
>>>>
>>>> 1136 N Larrabee Street, Apt 425
>>>> West Hollywood, CA 90069
>>>> Phone:  [phone]
>>>>
>>>> Am Pfisterhölzli 2
>>>> CH - 8606 Greifensee
>>>> Switzerland
>>>> Phone:  [phone]
>>>>
>>
>

--

Leonard Lin
Email:    [hidden email]

1136 N Larrabee Street, Apt 425
West Hollywood, CA 90069
Phone:  [phone]

Am Pfisterhölzli 2
CH - 8606 Greifensee
Switzerland
Phone:  [phone]

[Leonard Lin-2]

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&amp;useServerPrepStmts=true
   (workaround for: 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="inventoryItemCheckSetDefaultValues"/>
         <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/4be7wsehyxpddnrv
>>>>
>>>> You can find more using
>>>> http://ofbiz.markmail.org/search/?q=MySQL+milliseconds
>>>>
>>>> Jacques
>>>>
>>>> From: "Leonard Lin" <[hidden email]>
>>>>> 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
>>>>>>> <[hidden email]> 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/**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:    [hidden email]
>>>>>>>>
>>>>>>>> 1136 N Larrabee Street, Apt 425
>>>>>>>> West Hollywood, CA 90069
>>>>>>>> Phone:  [phone]
>>>>>>>>
>>>>>>>> Am Pfisterhölzli 2
>>>>>>>> CH - 8606 Greifensee
>>>>>>>> Switzerland
>>>>>>>> Phone:  [phone]
>>>>>>>>
>>>>>>>>
>>>>>>
>>>>>
>>>>> --
>>>>>
>>>>> Leonard Lin
>>>>> Email:    [hidden email]
>>>>>
>>>>> 1136 N Larrabee Street, Apt 425
>>>>> West Hollywood, CA 90069
>>>>> Phone:  [phone]
>>>>>
>>>>> Am Pfisterhölzli 2
>>>>> CH - 8606 Greifensee
>>>>> Switzerland
>>>>> Phone:  [phone]
>>>>>
>>>
>>
>

--

Leonard Lin
Email:    [hidden email]

1136 N Larrabee Street, Apt 425
West Hollywood, CA 90069
Phone:  [phone]

Am Pfisterhölzli 2
CH - 8606 Greifensee
Switzerland
Phone:  [phone]

[Mike Z]

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 <[hidden email]> 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&amp;**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" <[hidden email]>
>>>>>
>>>>>> 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 <[hidden email]>
>>>>>>>> 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:    [hidden email]
>>>>>>>>>
>>>>>>>>> 1136 N Larrabee Street, Apt 425
>>>>>>>>> West Hollywood, CA 90069
>>>>>>>>> Phone:  [phone]
>>>>>>>>>
>>>>>>>>> Am Pfisterhölzli 2
>>>>>>>>> CH - 8606 Greifensee
>>>>>>>>> Switzerland
>>>>>>>>> Phone:  [phone]
>>>>>>>>>
>>>>>>>>>
>>>>>>>>>
>>>>>>>
>>>>>> --
>>>>>>
>>>>>> Leonard Lin
>>>>>> Email:    [hidden email]
>>>>>>
>>>>>> 1136 N Larrabee Street, Apt 425
>>>>>> West Hollywood, CA 90069
>>>>>> Phone:  [phone]
>>>>>>
>>>>>> Am Pfisterhölzli 2
>>>>>> CH - 8606 Greifensee
>>>>>> Switzerland
>>>>>> Phone:  [phone]
>>>>>>
>>>>>>
>>>>
>>>
>>
> --
>
> Leonard Lin
> Email:    [hidden email]
>
> 1136 N Larrabee Street, Apt 425
> West Hollywood, CA 90069
> Phone:  [phone]
>
> Am Pfisterhölzli 2
> CH - 8606 Greifensee
> Switzerland
> Phone:  [phone]
>
>

[Leonard Lin-2]

I got the inventory to work on my MySQL installation.

First: Mike, Jacques, Ruth, thanks a lot for the pointers along my code
tracing etc.

I'm almost too ashamed to write the solution:
- My ECA wasn't enabled for MySQL config

I had this line flooding my logs since ever:
"Entity ECA Handler disabled for delegator [default]"
I ignored it as I thought it's probably because logging is on
super-verbose mode.

I stumbled upon "updateInventoryItemFromDetail" today and realized that
this function is triggered by ECA.

you can probably figure out the rest.

The problem was caused as about 2 weeks ago, beeing a newb, I just
copy/pasted some configs of the web and did obviously a mistake there.

Sorry for the disturbance on the mailing list and thanks again for
everybody's help.

I hope at least my MySQL 5.6 microsecond analysis will be helpful for
someone else in the future.

On 8/12/2012 10:33, Leonard Lin 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&amp;useServerPrepStmts=true
>   (workaround for: 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="inventoryItemCheckSetDefaultValues"/>
>         <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/4be7wsehyxpddnrv
>>>>>
>>>>> You can find more using
>>>>> http://ofbiz.markmail.org/search/?q=MySQL+milliseconds
>>>>>
>>>>> Jacques
>>>>>
>>>>> From: "Leonard Lin" <[hidden email]>
>>>>>> 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
>>>>>>>> <[hidden email]> 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/**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:    [hidden email]
>>>>>>>>>
>>>>>>>>> 1136 N Larrabee Street, Apt 425
>>>>>>>>> West Hollywood, CA 90069
>>>>>>>>> Phone:  [phone]
>>>>>>>>>
>>>>>>>>> Am Pfisterhölzli 2
>>>>>>>>> CH - 8606 Greifensee
>>>>>>>>> Switzerland
>>>>>>>>> Phone:  [phone]
>>>>>>>>>
>>>>>>>>>
>>>>>>>
>>>>>>
>>>>>> --
>>>>>>
>>>>>> Leonard Lin
>>>>>> Email:    [hidden email]
>>>>>>
>>>>>> 1136 N Larrabee Street, Apt 425
>>>>>> West Hollywood, CA 90069
>>>>>> Phone:  [phone]
>>>>>>
>>>>>> Am Pfisterhölzli 2
>>>>>> CH - 8606 Greifensee
>>>>>> Switzerland
>>>>>> Phone:  [phone]
>>>>>>
>>>>
>>>
>>
>

--

Leonard Lin
Email:    [hidden email]

1136 N Larrabee Street, Apt 425
West Hollywood, CA 90069
Phone:  [phone]

Am Pfisterhölzli 2
CH - 8606 Greifensee
Switzerland
Phone:  [phone]