Example: Creating Purge Programs as a Batch Delete
This example illustrates how you can create a table conversion to delete records from the input environment. Designing purge programs as batch delete programs enables you to purge records with control and accuracy. You can archive the purged data or remove it from the system permanently. The archiving process is explained in this example.
Before you start this example, create a handle for the table.
See "Understanding Handles" in the JD Edwards EnterpriseOne Tools Table Design Guide.
On the introduction form of the JD Edwards EnterpriseOne Table Conversion director, select Batch Delete and click Next.
On the External Data form, select the Purge Processing Option (T42000P) processing option template, and click Next.
On the Select Environment form, select <LOGIN ENV> as the source environment, select Force Version to Override Input Environment, and click Next.
The Force Version to Override Input Environment option prompts the person who runs the purge program to provide a valid source environment from which to run the batch-delete.
On the Select Input form, drag the User Defined Code Types (F0004) table to the Description column, and click Next.
On the Table Options form, select Run Currency Triggers, and click Next.
On the Data Selection form, define data selection as Where IC UCD1 (User Defined Code - Class Code 1) is equal to Null, and click Next.
On the Logging Options form, do not select any options, and click Next.
On the Finish form, select Yes, create a version of this table conversion, and enter VER0001 as the version name.
On the warning form that indicates that the table conversion needs to be saved, click OK.
On the Selection for Batch Delete form, select the Process Begin event, and click Advanced ER.
In Event Rules Design, add these event rule variables with a report scope:
FXXXXHandle_HFXXXX
szArchiveDataSource_DATS
szPurgeDataSource_DATS
szErrorCode_DTAI
cRenameFlag_EV01
mnErrorNumber_MATH01
Enter these begin process event rules from R42119P, save the event rules, and quit Event Rules Design:
0001 // Check to see if the purged data is being archived 0002 If PO cArchive⇒ Records is equal to "1" 0003 // If the environment processing option is blank, stop⇒ processing. 0004 If PO szArchiveEnvironmentName is equal to <Blank> Or PO sz⇒ ArchiveEnvironmentName is equal to <Null> 0005 Stop Conversion Processing("The⇒ archive environment is invalid.") 0006 Else 0007 // Check to make sure that the⇒ archive environment and data source is not the 0008 // same as the input⇒ environment and data source 0009 If PO szArchiveEnvironmentName is equal to SL⇒ SourceEnvironment 0010 Stop Conversion Processing("The source and archive⇒ environments are the same") 0011 Else 0012 //Get the data source of the archive⇒ environment 0013 Get and validate the data source for an environment/table ⇒ (B98700) PO szArchiveEnvironmentName -> szEnvironment "FXXXX" -> szTable⇒ Name (Replace x's with the name of your table.) VA rpt_szArchiveDataSource_⇒ DATS <- szDataSource VA rpt_szErrorCode_DTAI <- szErrorDataItem VA rpt_mn⇒ ErrorNumber_MATH01 <- mnErrorNumber 0014 //SAR #4337575 - B98700 has been modified⇒ to return an error if the table is 0015 //not found in the data source. Since we⇒ do not want the table to exist in the 0016 //archive environment, we need to⇒ bypass that error condition. 0017 If VA rpt_szErrorCode_DTAI is not equal to⇒ <Blank> And VA rpt_szErrorCode_DTAI is not equal to <Null> And VA rpt_⇒ szErrorCode_DTAI is not equal to "072W" 0018 Stop Conversion Processing("No⇒ data source was found for the archive environment") 0019 Else 0020 //Get the⇒ data source of the source environment 0021 Get and validate the data source for⇒ an environment/table (B98700) SL SourceEnvironment -> szEnvironment ⇒ "FXXXX" -> szTableName (Replace x's with the name of your table.) VA⇒ rpt_szPurgeDataSource_DATS <- szDataSource VA rpt_szErrorCode_DTAI <- sz⇒ ErrorDataItem VA rpt_mnErrorNumber_MATH01 <- mnErrorNumber 0022 If VA rpt_sz⇒ ErrorCode_DTAI is equalto "072W" 0023 Stop Conversion Processing ("The table⇒ could not be found in the source environment.") 0024 Else 0025 If VA rpt_sz⇒ ErrorCode_DTAI is not equal to <Blank> And VA rpt_szErrorCode_DTAI is not⇒ equal to <Null> 0026 Stop Conversion Processing ("No data source was found for ⇒ the source environment.") 0027 End If 0028 End If 0029 If VA rpt_szArchive⇒ DataSource_DATS is equal to VA rpt_szPurgeDataSource_DATS 0030 Stop Conversion⇒ Processing ("The source and archive environments have the same data source") 0031 ⇒ Else 0032 // Open a table with the same table name in the output environment.⇒ The table 0033 // will be renamed later if the table name processing option was⇒ populated. 0034 //Replace the X's in the following statement to reflect the name⇒ of your table. 0035 Copy Table Environment("FXXXX", <None>, SL Source⇒ Environment, PO szArchiveEnvironmentName, <Yes>, <Yes>, <No>, <None>, <None>,⇒ <Null>, <Null>) 0036 // Open a handle to the archive table 0037 //⇒ Replace the X's in the following statements to reflect the name of your handle and⇒ table. 0038 VA rpt_FXXXXHandle_HFXXXX = FXXXX.Open Handle 0039 If VA rpt_⇒ FXXXXHandle_HFXXXX is equal to <Null> 0040 Stop Conversion Processing ("Failed⇒ to open FXXXX in the archive environment") 0041 End If 0042 End If 0043 End If⇒ ⇒ ⇒ ⇒ ⇒ ⇒ ⇒ 0044 End If 0045 End If 0046 End IfNote: Ensure that you map all event rule variables, even if you do not use every value.In this example, the system writes log messages to the JDE.log and JDEDEBUG.log files on the Stop Conversion Processing event.
On the Selection for Batch Delete form, select the Row Fetched event, and click Advanced ER.
On Event Rules Design, enter these row fetched event rules from R42119P, save the event rules, and quit Event Rules Design:
001 // If we are archiving the purged records, write the record to the archive⇒ table 0002 //Replace the X's in the following statements to reflect the name of ⇒ your handle and table. Map the fields included in your table. 0003 If PO cArchive⇒ Records is equal to "1" 0004 FXXXX(VA rpt_FXXXXHandle_HFXXXX).Insert IC Order⇒ Company(Order Number) -> TK Order Company(Order Number) IC Document(Order⇒ No,Invoice,etc.) -> TK Document(Order No,Invoice,etc.) IC Order Type -> TK⇒ Order Type IC Line Number -> TK Line Number IC Order Suffix -> TK Order Suffix ⇒ ⇒ ⇒ ⇒ ⇒ ⇒ ⇒ IC Business Unit -> TK Business Unit IC Company -> TK Company IC Document⇒ Company(Original Order) -> TK Document Company(Original Order) IC Original⇒ Order Number -> TK Original Order Number IC Original Order Type -> TK Original⇒ Order Type IC Original Line Number -> TK Original Line Number IC Company-Key ⇒ (Related Order) -> TK Company-Key (Related Order) IC Related PO/SO/WO Number ->⇒ TK Related PO/SO/WO Number IC Related PO/SO/WO Order Type -> TK Related PO/SO⇒ /WO Order Type IC Related PO/SO Line Number -> TK Related PO/SO Line Number IC⇒ Agreement Number-Distribution -> TK Agreement Number-Distribution IC Agreement⇒ Supplement-Distribution -> TK Agreement Supplement-Distribution IC Address⇒ Number -> TK Address Number IC Address Number-Ship To -> TK Address Number-Ship⇒ To IC Address Number-Parent -> TK Address Number-Parent IC Date-Requested ->⇒ TK Date-Requested IC Date-Order/Transaction -> TK Date-Order/Transaction IC⇒ Date-Scheduled Pick -> TK Date-Scheduled Pick IC Date-Actual Ship Date -> TK⇒ Date-Actual Ship Date IC Date-Invoice -> TK Date-Invoice IC Date-Cancel -> TK⇒ Date-Cancel IC Date-For G/L(and Voucher) -> TK Date-For G/L(and Voucher) IC⇒ Date-Promised Delivery -> TK Date-Promised Delivery IC Date-Price Effective⇒ Date -> TK Date-Price Effective Date IC Date-Promised Shipment -> TK Date-⇒ Promised Shipment IC Reference -> TK Reference IC Reference 2 -> TK Reference⇒ 2 IC Item Number-Short -> TK Item Number-Short IC 2nd Item Number -> TK 2nd⇒ Item Number IC 3rd Item Number -> TK 3rd Item Number IC Location -> TK⇒ Location IC Lot/Serial Number -> TK Lot/Serial Number IC From Grade -> TK⇒ From Grade IC Thru Grade -> TK Thru Grade IC From Potency -> TK From Potency ⇒ IC Thru Potency -> TK Thru Potency IC Days Before Expiration -> TK Days Before⇒ Expiration IC Description -> TK Description IC Description-Line 2 -> TK⇒ Description-Line 2 IC Line Type -> TK Line Type IC Status Code-Next -> TK⇒ Status Code-Next IC Status Code-Last -> TK Status Code-Last IC Business Unit -⇒ ⇒ ⇒ ⇒ ⇒ ⇒ ⇒ Header -> TK Business Unit - Header IC Item Number - Related (Kit) -> TK Item⇒ Number - Related (Kit) IC Kit Master Line Number -> TK Kit Master Line Number ⇒ IC Component Line Number -> TK Component Line Number IC Related Kit Component -⇒ > TK Related Kit Component IC Number of Component Per Parent -> TK Number of⇒ Component Per Parent IC Sales Catalog Section -> TK Sales Catalog Section IC⇒ Sub Section -> TK Sub Section IC Sales Category Code 3 -> TK Sales Category⇒ Code 3 IC Sales Category Code 4 -> TK Sales Category Code 4 IC Sales Category⇒ Code 5 -> TK Sales Category Code 5 IC Commodity Class -> TK Commodity Class ⇒ IC Commodity Sub Class -> TK Commodity Sub Class IC Supplier Rebate Code -> TK⇒ Supplier Rebate Code IC Master Planning Family -> TK Master Planning Family ⇒ IC Purchasing Category Code 5 -> TK Purchasing Category Code 5 IC Unit of⇒ Measure as Input -> TK Unit of Measure as Input IC Units-Order/Transaction⇒ Quantity -> TK Units-Order/Transaction Quantity IC Quantity Shipped -> TK⇒ Quantity Shipped IC Units-Qty Backordered/Held -> TK Units-Qty Backordered/Held ⇒ IC Units-Quantity Canceled/Scrapped -> TK Units-Quantity Canceled/Scrapped IC⇒ Units-Future Quantity Committed -> TK Units-Future Quantity Committed IC Units-⇒ Open -> TK Units-Open IC Units-Shipped to Date -> TK Units-Shipped to Date IC⇒ Units-Relieved -> TK Units-Relieved IC Committed (H/S) -> TK Committed (H/S) ⇒ IC Other Quantity (1/2) -> TK Other Quantity (1/2) IC Amount-Price per Unit ->⇒ TK Amount-Price per Unit IC Amount-Extended Price -> TK Amount-Extended Price ⇒ IC Amount-Open -> TK Amount-Open IC Price Override Code -> TK Price Override⇒ Code IC Temporary Price (Y/N) -> TK Temporary Price (Y/N) IC Unit of Measure-⇒ Entered for Unit Price -> TK Unit of Measure- Entered for Unit Price IC Amount-⇒ List Price -> TK Amount-List Price IC Amount-Unit Cost -> TK Amount-Unit Cost ⇒ IC Amount-Extended Cost -> TK Amount-Extended Cost IC Cost Override Code -> TK⇒ Cost Override Code IC Extended Cost-Transfer -> TK Extended Cost-Transfer IC⇒ Print Message -> TK Print Message IC Payment Terms Code -> TK Payment Terms Code ⇒ ⇒ ⇒ ⇒ ⇒ ⇒ ⇒ IC Payment Instrument -> TK Payment Instrument IC Based on Date -> TK Based⇒ on Date IC Discount-Trade -> TK Discount-Trade IC Trade Discount (Old) -> TK⇒ Trade Discount (Old) IC Price and Adjustment Schedule -> TK Price and⇒ Adjustment Schedule IC Item Price Group -> TK Item Price Group IC Pricing⇒ Category Level -> TK Pricing Category Level IC Discount %-Cash -> TK Discount %-⇒ ⇒ ⇒ ⇒ ⇒ ⇒ ⇒ Cash IC Document Company -> TK Document Company IC Document⇒ (Voucher,Invoice,etc.) -> TK Document(Voucher,Invoice,etc.) IC Document Type ->⇒ TK Document Type IC Document-Original -> TK Document-Original IC Document⇒ Type-Original -> TK Document Type-Original IC Document Company-Original -> TK⇒ Document Company-Original IC Pick Slip Number -> TK Pick Slip Number IC⇒ Delivery Number -> TK Delivery Number IC Sales Taxable(Y/N) -> TK Sales Taxable⇒ (Y/N) IC Tax Rate/Area -> TK Tax Rate/Area IC Tax Expl Code 1 -> TK Tax Expl⇒ Code 1 IC Associated Text -> TK Associated Text IC Priority-Processing -> TK⇒ Priority-Processing IC Printed Code -> TK Printed Code IC Backorders Allowed ⇒ (Y/N) -> TK Backorders Allowed (Y/N) IC Substitutes Allowed (Y/N) -> TK⇒ Substitutes Allowed (Y/N) IC Partial Line Shipments Allowed (Y/N) -> TK Partial⇒ Line Shipments Allowed (Y/N) IC Line of Business -> TK Line of Business IC⇒ End Use -> TK End Use IC Duty Status -> TK Duty Status IC Nature of⇒ Transaction -> TK Nature of Transaction IC Primary/Last Supplier Number -> TK⇒ Primary/Last Supplier Number IC Carrier Number -> TK Carrier Number IC Mode⇒ of Transport -> TK Mode of Transport IC Route Code -> TK Route Code IC Stop⇒ Code -> TK Stop Code IC Zone Number -> TK Zone Number IC Container I.D. -> TK⇒ Container I.D. IC Freight Handling Code -> TK Freight Handling Code IC⇒ Shipping Commodity Class -> TK Shipping Commodity Class IC Shipping Conditions⇒ Code -> TK Shipping Conditions Code IC Serial Number-Lot -> TK Serial Number-Lot ⇒ ⇒ ⇒ ⇒ ⇒ ⇒ ⇒ IC Unit of Measure-Primary -> TK Unit of Measure-Primary IC Units-Primary⇒ Quantity Ordered -> TK Units-Primary Quantity Ordered IC Unit of Measure-⇒ Secondary -> TK Unit of Measure-Secondary IC Units-Secondary Quantity Ordered -⇒ > TK Units-Secondary Quantity Ordered IC Unit of Measure-Pricing -> TK Unit⇒ of Measure-Pricing IC Unit Weight -> TK Unit Weight IC Weight Unit of Measure⇒ -> TK Weight Unit of Measure IC Unit Volume -> TK Unit Volume IC Volume Unit⇒ of Measure -> TK Volume Unit of Measure IC Reprice (Basket Price) Category ->⇒ TK Reprice (Basket Price) Category IC Order Reprice Category -> TK Order⇒ Reprice Category IC Order Repriced Indicator -> TK Order Repriced Indicator ⇒ IC Costing Method-Inventory -> TK Costing Method-Inventory IC G/L Offset -> TK⇒ G/L Offset IC Century -> TK Century IC Fiscal Year -> TK Fiscal Year IC⇒ Inter Branch Sales -> TK Inter Branch Sales IC On Hand Updated -> TK On Hand⇒ Updated IC Configurator Print Flag -> TK Configurator Print Flag IC Sales⇒ Order Status 04 -> TK Sales Order Status 04 IC Substitute Item Indicator -> TK⇒ Substitute Item Indicator IC Preference Commitment Indicator -> TK Preference⇒ Commitment Indicator IC Ship date (PDDJ) overridden -> TK Ship date (PDDJ)⇒ overridden IC Price Adjustment Line Indicator -> TK Price Adjustment Line⇒ Indicator IC Price Adj. History Indicator -> TK Price Adj. History Indicator ⇒ IC Preference Production Allocation -> TK Preference Production Allocation IC⇒ Transfer/Direct Ship/Intercompany Flag -> TK Transfer/Direct Ship/ Intercompany⇒ Flag IC Deferred entries flag -> TK Deferred entries flag IC Euro Conversion⇒ Status Flag -> TK Euro Conversion Status Flag IC Sales Order Status 14 -> TK⇒ Sales Order Status 14 IC Sales Order Status 15 -> TK Sales Order Status 15 IC⇒ Apply Commission(Y/N) -> TK Apply Commission(Y/N) IC Commission Category -> TK⇒ Commission Category IC Reason Code -> TK Reason Code IC Gross Weight -> TK⇒ Gross Weight IC Gross Weight Unit of Measure -> TK Gross Weight Unit of Measure ⇒ IC Subledger-G/L -> TK Subledger-G/L IC Subledger Type -> TK Subledger Type ⇒ IC Code-Location Tax Status -> TK Code-Location Tax Status IC Price Code 1 ->⇒ TK Price Code 1 IC Price Code 2 -> TK Price Code 2 IC Price Code 3 -> TK⇒ Price Code 3 IC Status-In Warehouse -> TK Status-In Warehouse IC Work Order⇒ Freeze Code -> TK Work Order Freeze Code IC Send Method -> TK Send Method IC⇒ Currency Code-From -> TK Currency Code-From IC Currency Conversion Rate-Spot⇒ Rate -> TK Currency Conversion Rate-Spot Rate IC Amount-List Price per Unit ->⇒ TK Amount-List Price per Unit IC Amount-Foreign Price per Unit -> TK Amount-⇒ Foreign Price per Unit IC Amount-Foreign Extended Price -> TK Amount-Foreign⇒ Extended Price IC Amount-Foreign Unit Cost -> TK Amount-Foreign Unit Cost IC⇒ Amount-Foreign Extended Cost -> TK Amount-Foreign Extended Cost IC User⇒ Reserved Code -> TK User Reserved Code IC User Reserved Date -> TK User⇒ Reserved Date IC User Reserved Amount -> TK User Reserved Amount IC User⇒ Reserved Number -> TK User Reserved Number IC User Reserved Reference -> TK⇒ User Reserved Reference IC Transaction Originator -> TK Transaction Originator ⇒ IC User ID -> TK User ID IC Program ID -> TK Program ID IC Work Station ID -⇒ > TK Work Station ID IC Date-Updated -> TK Date-Updated IC Time of Day ->⇒ TK Time of Day IC Manufacturing Variance Accounting Flag -> TK Manufacturing ⇒ Variance Accounting Flag IC Sales Order Status 17 -> TK Sales Order Status 17 ⇒ IC Sales Order Status 18 -> TK Sales Order Status 18 IC Sales Order Status 19 -⇒ > TK Sales Order Status 19 IC Sales Order Status 20 -> TK Sales Order Status⇒ 20 IC Integration Reference 01 -> TK Integration Reference 01 IC Integration⇒ Reference 02 -> TK Integration Reference 02 IC Integration Reference 03 -> TK⇒ Integration Reference 03 IC Integration Reference 04 -> TK Integration⇒ Reference 04 IC Integration Reference 05 -> TK Integration Reference 05 IC⇒ Source of Order -> TK Source of Order IC Reference -> TK Reference IC Demand⇒ Unique Key ID -> TK Demand Unique Key ID IC Pull Signal -> TK Pull Signal IC⇒ Release Number -> TK Release Number IC Scheduled Shipment Time -> TK Scheduled⇒ Shipment Time IC Time-Rlease -> TK Time-Rlease IC Date-Rlease -> TK Date-⇒ Rlease IC Requested Delivery Time -> TK Requested Delivery Time IC Actual⇒ Shipment Time -> TK Actual Shipment Time IC Time-Original Promised Delivery ->⇒ TK Time-Original Promised Delivery IC Time-Scheduled Pick -> TK Time-Scheduled⇒ Pick IC Time-Future Time 2 -> TK Time-Future Time 2 IC Cross-Docking Flag ->⇒ TK Cross-Docking Flag IC Cross-Docking Priority for Sales Orders -> TK Cross-⇒ Docking Priority for Sales Orders IC Dual Unit of Measure Item -> TK Dual Unit⇒ of Measure Item IC Buying Segment Code -> TK Buying Segment Code IC Current⇒ Buying Segment Code -> TK Current Buying Segment Code IC Change Order Number ->⇒ TK Change Order Number IC Address Number-Deliver To -> TK Address Number-⇒ Deliver To IC Pending Approval Flag -> TK Pending Approval Flag IC Revision⇒ Reason -> TK Revision Reason IC Matrix Control Line Number -> TK Matrix⇒ Control Line Number IC Shipment Number -> TK Shipment Number IC Promised⇒ Delivery Time -> TK Promised Delivery Time IC Project Number -> TK Project⇒ Number IC Sequence Number -> TK Sequence Number IC Item Revision Level -> TK⇒ Item Revision Level IC Hold Orders Code -> TK Hold Orders Code IC Business⇒ Unit-Header -> TK Business Unit-Header IC Business Unit-Demand -> TK Business⇒ Unit-Demand IC Currency Code-Base -> TK Currency Code-Base IC Document Line⇒ Number-Original -> TK Document Line Number-Original IC Date-Original Promised⇒ Delivery -> TK Date-Original Promised Delivery IC Cross Dock Order Company⇒ (OrderNumber) -> TK Cross Dock Order Company(OrderNumber) IC Cross Dock Order No -⇒ ⇒ ⇒ ⇒ ⇒ ⇒ ⇒ > TK Cross Dock Order No IC Cross Dock Order Type -> TK Cross Dock Order Type ⇒ IC Cross Dock Line Number -> TK Cross Dock Line Number IC Cross Dock Order⇒ Suffix -> TK Cross Dock Order Suffix IC Port of Entry or Exit -> TK Port of⇒ Entry or Exit IC Payment Terms Override Code -> TK Payment Terms Override Code ⇒ IC Buyer Number -> TK Buyer Number IC Promotion ID -> TK Promotion ID IC⇒ Asset Item Number -> TK Asset Item Number IC Parent Number -> TK Parent Number ⇒ 0005 // Do not delete the record if the insert to the archive table failed. 0006 If⇒ SV Error_Status is not equal to CO ERROR 0007 Delete Current Input Row 0008 End⇒ If 0009 Else 0010 Delete Current Input Row 0011 End If
Note: Ensure that you map all parameters to a field, even if you do not use every value.On the Selection for Batch Delete form, select the Process End event, and click Advanced ER.
On Event Rules Design, enter these process end event rules from R42119P, save the event rules, and quit Event Rules Design:
0001 If PO cArchiveRecords is equal to "1" 0002 // Close the table 0003 //Replace⇒ the X's in the following statement to reflect the name of your handle and table. ⇒ 0004 FXXXX(VA rpt_FXXXXHandle_HFXXXX).Close 0005 // If the data was archived and⇒ the table name processing option was populated, 0006 // rename the table. 0007 If PO⇒ szArchiveTableName is not equal to <Blank> And PO szArchiveTableName is not⇒ equal to <Null> 0008 Rename Table (B0000202) "FXXXX" -> szOldTableName PO sz⇒ ArchiveTableName -> szNewTableName "<Blank>" -> szTableOwnerID "<Blank>" -> sz⇒ Password VA rpt_szArchiveDataSource_DATS -> szDataSource VA rpt_cRenameFlag_⇒ EV01 <- cRenameTableSuccessful 0009 End If 0010 End If
Note: Ensure that you map all parameters to a field, even if you do not use every value.