E1: BSFN: How to Add Additional Selection Criteria Using JDB_SetSelection or JDB_SetSelectionX API for a Specific Function? (CASE STUDY of R41543)
(Doc ID 2381468.1)
Last updated on APRIL 02, 2018
Applies to:JD Edwards EnterpriseOne Tools - Version 8.98 and later
JD Edwards EnterpriseOne Inventory Management - Version 9.1 and later
Information in this document applies to any platform.
The purpose of this document is to go through Data Selection APIs in EnterpriseOne Business Function below,
Note that this document is a case study only on possible issue you may face in running R41543 (Item Ledger/Account Integrity) which compares the amount between F4111 (Item Ledger) and F0911 (General Ledger) to check integrity for inventory transactions. In a certain organization, you may have set DMAAI as below,
|1.1400||-100.00||DMAAI 4122 for Inventory|
|M30.1401||+100.00||DMAAI 4124 for Expense|
: In issuing 100.00 USD, the offset (which is to be expense account) points to inventory accounts. So existing routine simply add both lines, which results in 0.00 where the value of F4111 is -100.00 USD.
Note that R41543 reads the Object Account (F0901.OBJ) to determine whether the specific range of Object Account belong to the offset of Inventory which is hard coded. On the other hand, the enhanced report R41500 (Automatic Inventory to GL Reconciliation) reads F0911 only when F0911.GPF1 (Inventory Flag) = '1' which is the instruction for Inventory offset. For detail, refer to <Document 2377574.1> - E1:41 : R41543 and R41500 Comparison - Troubleshooting.
So the need can come to read F0911 only when F0911.GPF1 = '1' on top of exiting query from F0911.
This document is to explain how to add additional column in WHERE clause as below,
- [As - Is Query] SELECT * FROM PRODDTA.F0911 WHERE ( GLDOC = 9999.000000 AND GLDCT = 'II' AND GLKCO = '00200' AND GLDGJ >= 118081 AND GLDGJ <= 118081 ) ORDER BY GLDCT ASC,GLDOC ASC,GLKCO ASC,GLDGJ DESC,GLJELN ASC,GLLT ASC,GLEXTL ASC
- [To - Be Query] SELECT * FROM PRODDTA.F0911 WHERE ( GLDOC = 9999.000000 AND GLDCT = 'II' AND GLKCO = '00200' AND GLGPF1 = '1' AND GLDGJ >= 118081 AND GLDGJ <= 118081 ) ORDER BY GLDCT ASC,GLDOC ASC,GLKCO ASC,GLDGJ DESC,GLJELN ASC,GLLT ASC,GLEXTL ASC
Disclaimer: this document is a case study only hence Oracle Support does not warrant the outcome you may get. The audience of this document is the developer with proper knowledge in JDE APIs and Business Functions.
To view full details, sign in with your My Oracle Support account.
Don't have a My Oracle Support account? Click to get started!