Table I/O Select Fetch Next

[accesssoln]

I'm very new to JDE development and I'm trying to calculate the average of a field (Amount-unit cost) by doing SUM((quantity available/total quantity available)*Price) for all the values of unit cost for a certain item number. This is being done in the level break footer section that breaks with a new 2nd Item number, and I'm doing this manually via ER and Table I/O because the built in JDE aggregate for average doesn't work for my purposes. In the level break footer -- do section I have the following code:

V4111A.Open
V4111A.Select

BC 2nd Item Number (F4111)(LITM) = TK 2nd Item Number
PO szCostCenter = TK Business Unit​

V4111A.Fetch Next

BC Quantity Available (F4111)(TRQT) <- TK Quantity Available
BC Amount - Unit Cost (F4111)(UNCS) <- TK Amount - Unit Cost

​

While SV File_IO_Status is equal to CO_SUCCESS

VA sec_mn_CalcUnitMeasure = ([BC Quantity Available (F4111)(TRQT)]/[RV Total of Transaction Quantity])*[BC Amount - Unit Cost (F4111)(UNCS)]​

VA rpt_mn_AverageUnitCost = [VA rpt_mn_AverageUnitCost]+[VA sec_mn_CalcUnitMeasure]​

V4111A.Fetch Next​

BC Quantity Available (F4111)(TRQT) <- TK Quantity Available​

BC Amount - Unit Cost (F4111)(UNCS) <- TK Amount - Unit Cost​

End While
V4111A.Close
RV Average Unit Cost = VA rpt_mn_AverageUnitCost


I'm trying to do: Select Quantity Available, Amount - Unit Cost from V4111A where 2nd Item Number = BC 2nd Item Number AND Business Unit = PO szCostCenter. But with the code above, it never goes into the CO_SUCCESS while loop. If I move the fetch next at the beginning of the while loop it spits out some numbers but it's not accurate. Am I missing something small or not understanding how table I/O works? Any help is greatly appreciated. Thank you!

 

[craig_welton]

Welcome to the party.

In a level break footer section, you want to use the PC 2nd Item number because the BC value will have the "next" 2nd Item number (what caused the break). PC is the previous record which is what you want. If you're running this report for one item, that BC column will be null.

Get familiar with turning on tracing which writes the SQL and other info to a jdedebug.log file. Very useful.

Craig

 

[nkuebelbeck]

welcome, i'll suggest using the event rule debugger tool.(C:\E910\system\bin32\Debugger.exe on my machine) it'll let you set break points in your report NER, set variable watch.

...and get jdetrace and objectbrowser.

 

[accesssoln]

Thank you very much! Turns out changing the BC to PC and looking at the output of my variables is what I had to do. All working perfectly now!