R4102P and Tables Related per Processing Option Value

Goal

The audience for this note is someone with developer level knowledge.

Question 1> What are the possible values can be set for Processing Option item #3. Delete the item/Branch Record of R4102P?
Question 2> What are the routine per values defined above?
Question 3> What are the index and Keys used to SELECT and DELETE records from related tables?
Question 4> What are the Business Functions related with this transaction?
Question 5> What will be the best practice in running R4102P?

Solution

This document is to explain what are the tables related in validating/deleting per Processing Option value of cDeleteAllTables defined. Possibly this document can be used when there is performance issue in running Item Balance Purge (F4102) (R4102P).

Note that,

R4102P is validating and (if validation is successful) deleting tables
It does not deleted any transaction files (for this, refer to <Document 1082128.1> - E1: ENV: Archive and Purging within JD Edwards EnterpriseOne
Only limited tables F4105, F30026, F41002, F41021, F4102 (this is conditional based on the processing option parameters which is explained in this document), F41022 and F41171
This routine is different from deletion of Item Branch (F4102) via P41026
Validation to be performed
- Check whether all quantity buckets from F41021 zero (if any of them are not an zero, exit routine)
- Check Open orders for Sales Order and Purchase Order (exclude orders with status code. Refer to SELECT statement below)
- Check Open Work Order (when the item is parent item) then check the status. If F4801.SRST NOT IN ( '99','91','MJ','MM','MK' ) then it is considered open (work) order
- Read part list (F3111 Get Parts List Rows) then check the work order status on F4801.DOCO = F3111.DOCO on Item and Branch combination. If parent work order status meets above '3', then do not delete F4102/F41021 etc.
- If item to delete is in use (though all quantity buckets are zero from F41021) in a certain BOM then do not delete BOM (F3002)
- Check whether the item to delete is a component in a certain BOM then do not delete it from F3002

Question 1> What are the possible values can be set for Processing Option item #3. Delete the item/Branch Record of R4102P?

Answer 1> Currently available processing option values are, :

PO Value	Description	Simplified Routine
Blank	Purge the Item Location (F41021) records with zero quantities if there are no open orders for the Item and Branch combination. The primary location will not be purged under any circumstances.
1	Purge the Item Branch (F4102) record if every Item Location (F41021) record for the Item and Branch has zero quantities and no open orders. If the Item Branch record cannot be purged, the primary location will not be purged.
2	Purge any Item Location (F41021) record that has all zero quantities and no open orders. The primary location will not be purged under any circumstances.	User does not want to delete the F4102 record so delete any F41021 record that is not primary when it does not have an open order against it and has no quantities.
3	Purge the Item Branch (F4102) record and purge every Item Location (F41021) record that has zero quantities and no open orders against it. If all the Item Locations could not be deleted, the Item Branch record and the primary location record will not be deleted.	User wants to delete the F4102 record so we must delete all the F41021 records, including the primary location. R4102P must delete any cost records for the item and branch and delete UOM conversions by branch.

Question 2> What are the routine per values defined above?

Answer 2> Below table describe detail routine for validation and deletion (PO = PO cDeleteAllTables):

Case (cDeleteAllTables = Blank)

Validation	Deletion	Notes
F41021 Verify All Locations for Zero Quantity Verify Open Orders (F4211, F4311 and F43121) F4801, Verify Open Work Order Exist F3111 Get Parts List Rows F4801, Verify Open Work Order Exist	Delete F4105 Item Locations Costs F41021 Delete Locations for Item Branch F4102 Record Reservation (to release)	Validation 5 will be occurred only when item is used for F3111 Validation 3 is done based on MCU and ITM combination whereas validation 5 takes place for ITM Deletion 1 takes place only when F4101.CLEV = '3' If validation 1 failed then it does not take additional validation routine and deletion routine so did validation routine 2

Case (cDeleteAllTables = 1)

Validation	Deletion	Notes
F41021 Verify All Locations for Zero Quantity Verify Open Orders (F4211, F4311 and F43121) F4801, Verify Open Work Order Exist F3111 Get Parts List Rows F4801, Verify Open Work Order Exist F3002 Verify BOM Parent Existance F3002 Verify BOM Component Existance	F4105 Delete Cost Records F30026 Delete Cost Component Records F41002 Delete Item Units of Measure F4102 Record Reservation F41021 Delete Locations for Item Branch F4102 Delete Item Branch F4102 Record Reservation (to release)	Validation 3 is done based on MCU and ITM combination whereas validation 5 takes place for ITM Validation 6 & 7 are done based on ITM and MCU combination Deletion 1 & 2 take place only when F4101.CLEV = '2' OR '3' Deletion 2 was implemented through SAR 8941881 - If the cost records are deleted, delete cost component records also Deletion 3 will take place only when F41001.BUMC is 'Y' that is Process UOM by Branch is ON

Case (cDeleteAllTables = 2 )

Validation	Deletion	Notes
Open Quantities from F41021 Verify Open Orders (F4211, F4311 and F43121) F4801, Verify Open Work Order Exist Verify Item Parts List	F4105 Delete Cost Records F41021 Delete Location	Open quantity check is to be done one by one as we have grouped columns above. That is 1st level has quantity written then it will skip 2nd level and so on. The routine of processing option value 2 and 3 are alike except that input parameter 2 does not delete where F41021.PBIN = 'P' Zero Quantity against F41021 is not checked through Business Function for this example, rather it handles sequentially If PO Value is 2 then it only checks for non primary bin from F41021 Deletion 1 takes place only when F4101.CLEV = '3' (CostLevel - Level - Inventory Cost)

Case (cDeleteAllTables = 3 )

Validation	Deletion	Notes
Open Quantities from F41021 Verify Open Orders (F4211, F4311 and F43121) F4801, Verify Open Work Order Exist Verify Item Parts List	F4105 Delete Cost Records F41021 Delete Location	Open quantity check is to be done one by one as we have grouped columns above. That is 1st level has quantity written then it will skip 2nd level and so on. The routine of processing option value 2 and 3 are alike except that input parameter 2 does not delete where F41021.PBIN = 'P' Zero Quantity against F41021 is not checked through Business Function for this example, rather it handles sequentially If PO Value is 2 then it only checks for non primary bin from F41021 Deletion 1 takes place only when F4101.CLEV = '3' (CostLevel - Level - Inventory Cost)

Note:

The routine of processing option value 2 and 3 are alike except that input parameter 2 does not delete where F41021.PBIN = 'P'
If PO Value is 2 then it only checks for non primary bin from F41021
Quantity Validation Sequence against F41021 (when PO value is 2 or 3):
1: QTTR, QTO2, QTO1, QTIN, SWSC
2: SWHC, SCMS, SOBW, SQWO, SIBW
3: HCMS, PCOM, QTRO, QWBO, PREQ
4: SREQ, PQOH, SQOH, FCOM, PBCK
5: QTRI, OT2P, OT1P, FUN1, QOWO
6: OT1A, QONL, HCOM, QTY2, QTY1

Question 3> What are the index and Keys used to SELECT and DELETE records from related tables?

Answer 3> Below are list of tables and example of query issued:

Table	Index	Key	Business Function	Query (DML statement)
F4102		2		This table is to be accessed through business view as this report R4102P is running based on F4102
F41021	1	2	CheckForZeroQuantity	SELECT * FROM PRODDTA.F41021 WHERE ( LIITM = 1.000000 AND LIMCU = ' M30' ) ORDER BY LIITM ASC,LIMCU ASC,LILOCN ASC,LILOTN ASC
F41021		2		when processing option value is 2 or 3, table F41021 is to be accessed through business view
F4211		2	VerifyOpenOrders	SELECT * FROM PRODDTA.F4211 WHERE ( SDMCU = ' M30' AND SDITM = 1.000000 AND SDNXTR <> '999' ) ORDER BY SDITM ASC,SDMCU ASC,SDPDDJ ASC,SDPDTT ASC
F4311		2	VerifyOpenOrders	SELECT * FROM PRODDTA.F4311 WHERE ( PDMCU = ' M30' AND PDITM = 1.000000 AND PDNXTR <> '999' ) ORDER BY PDDOCO ASC,PDDCTO ASC,PDKCOO ASC,PDSFXO ASC,PDLNID ASC
F43121		2	VerifyOpenOrders	SELECT * FROM PRODDTA.F43121 WHERE ( PRMATC < '3' AND PRITM = 1.000000 AND PRMCU = ' M30' ) ORDER BY PRMATC ASC,PRITM ASC,PRMCU ASC,PRPDDJ ASC
F4801	4	2	F4801VerifyOpenWorkOrderExist	SELECT * FROM PRODDTA.F4801 WHERE ( WAITM = 1.000000 AND WAMMCU = ' M30' ) AND WASRST NOT IN ( '99','91','MJ','MM','MK' ) ) ORDER BY WAITM ASC,WAMMCU ASC,WADRQJ ASC
F3111	2	2	F3111GetPartsListRows	SELECT * FROM PRODDTA.F3111 WHERE ( WMCPIT = 1.000000 AND WMCMCU = ' M30' ) ORDER BY WMCPIT ASC,WMCMCU ASC,WMDRQJ ASC
F3002	1	2	VerifyItemAsParentInBOM	SELECT * FROM PRODDTA.F3002 WHERE ( IXKIT = 1.000000 AND IXMMCU = ' M30' )
F3002	1	2	VerifyItemAsComponentInBOM	SELECT * FROM PRODDTA.F3002 WHERE ( IXITM = 1.000000 AND IXCMCU = ' M30' )
F4105		2	DeleteAllRelatedCostRecords	DELETE FROM PRODDTA.F4105 WHERE ( COITM = 1.000000 AND COMCU = ' M30' )
F30026		2	F30026DeleteCostComponentRecords	DELETE FROM PRODDTA.F30026 WHERE ( IEITM = 1.000000 AND IEMMCU = ' M30' )
F41002		2	DeleteItemBranchUOM	DELETE FROM PRODDTA.F41002 WHERE (UMITM = 1.000000 AND UMMCU = ' M30' )
F41021		2	DeleteAllRelatedLocations	DELETE FROM PRODDTA.F41021 WHERE ( LIITM = 1.000000 AND LIMCU = ' M30' )
F4102		2	DeleteItemBranch	DELETE FROM PRODDTA.F4102 WHERE ( IBMCU = ' M30' AND IBITM = 1.000000 )
F41022			DeleteItemBranch	DELETE FROM PRODDTA.F41022 WHERE ( PNMCU = ' ' AND PNITM = 0.000000 )
F41171	1	2	F41171DeleteSWRecord	DELETE FROM PRODDTA.F41171 WHERE ( PBITM = 1.000000 AND PBMCU = ' M30' ) <Bug 18310721> - R4102P DOES NOT PURGE SWM BRANCH EXTENSION F41171

Note

The BSFN FreeHuserAndHrequest has to be called if validation of data from F3111 is done through F3111GetPartsListRows as this BSFN returns pointer to be killed/terminated
If you have chosen the processing option to delete F4102 it will check table F41022 and it contains any data which is to be deleted
Keys for F4211/F4311/F43121 is depends on input value of cDeleteAllTables

cDeleteAllTables (Blank or 1): then fetch data based on MCU and ITM
cDeleteAllTables (2 or 3): then fetch data based on MCU, ITM, LOCN and LOTN
For F4211/F4311 next status '999' will be excluded and for F43121 it will check only when F43121.MATC is either '1' or '2'

JDB_SelectKeyed() will be constructed based on input value when a certain BSFN gets called

Question 4> What are the Business Functions related with this transaction?

Answer 4> Below are list of Business Functions in selecting/deleting data:

Business Function	ID	Description
CheckForZeroQuantity	B4100110	F41021 Verify All Locations for Zero Quantity
VerifyOpenOrders	N4101820	Verify Open Orders
F4801VerifyOpenWorkOrderExist	B4100980	F4801, Verify Open Work Order Exist
F3111GetPartsListRows	B3101020	F3111 Get Parts List Rows
VerifyItemAsParentInBOM	B4100060	F3002 Verify BOM Parent Existance
DeleteAllRelatedCostRecords	XF4105	F4105 Delete Cost Records
F30026DeleteCostComponentRecords	B4100710	F30026 Delete Cost Component Records
DeleteItemBranchUOM	B4100700	F41002 Delete Item Units of Measure
DeleteAllRelatedLocations	B4100201	F41021 Delete Locations for Item Branch
DeleteItemBranch	B4100590	F4102 Delete Item Branch
F41171DeleteSWRecord	B4101851	Delete Service/Warranty Extension - F41171

Question 5> What will be the best practice in running R4102P?

Growing data from F4102/F41021 and F4105/F30026 may impact performance across EnterpriseOne. So it is important to run R4102P in regular basis
If possible, run this job after office hour as indicated above table F41021/F4105 will be heavily used across EnterpriseOne
If there is any update routine against mentioned tables, this report may need to wait till update lock (UPDLOCK) is release which result in slow performance
Refer <Note 625602.1> to purge F4211
Refer <Note 661344.1> to purge F4311
Refer <Note 1082128.1> to determine how to maintain database light and dynamic
Above listed tables are related with Supply/Demand so purging data from listed tables and transaction files will impact system performance greatly
If needed, may define index for tables which you determine the root cause of slow performance or through DBA action tune performance

Note: Purge is removing data from the Database fully so all the implementation has to be done with care.