Using Z File Processing
Combined With SQL To Update the Item Master and Item Branch
Written by Jeff Geiger
AT MANY JD EDWARDS ENTERPRISEONE CLIENTS, IT IS IMPERATIVE TO UPDATE VARIOUS FIELDS WITHIN THE ITEM MASTER AND ITEM BRANCH FILES. ENTERPRISEONE HAS PROVIDED SOME TOOLS TO UPDATE A HANDFUL OF THE VARIOUS FIELDS SUCH AS THE ITEM BRANCH AND ITEM MASTER FIELDS UPDATE UBES (R41804 AND R41805). HOWEVER, IF YOU WISH TO UPDATE ANY OR ALL OF THE FIELDS AND HAVE A METHOD FOR VALIDATING THE DATA, YOU MUST RESORT TO CREATING A CUSTOM UBE TO CALL THE NECESSARY BUSINESS FUNCTIONS OR USE Z FILE PROCESSING.
Using Z File processing is an excellent solution for updating thousands of records. In this example, I will illustrate how you can use Z Files and SQL to quickly, efficiently and safely update thousands or hundreds of thousands of records. Just follow these simple steps:
Create a custom copy of the F4101 – Item Master and F4102 – Item Branch with the names F554101 and F554102. Use the Object Management Workbench (OMW) to create a copy then generate the copies. See a development resource if you have questions on copying objects or table operations.
Create SQL to copy the existing F4101 and F4102 tables to the custom files. (See Below.)
– Clear the work files.
DELETE FROM F554101;
DELETE FROM F554102;
– Copy F4101 into F554101 work file
insert into F554101 select * from proddta.f4101;
– Copy F4102 into F554102 work file
insert into F554102 select * from proddta.f4102
Create SQL to update the work files with all the new values. You can update the F55 files all you want, testing and retesting until the values are exactly what you feel are right. This will not impact the system or business processes at all because they have not yet been moved to the final files. You could have dozens of update SQL code lines to update your Item Master and Item Branch. An example might include:
SAMPLE ONLY:
UPDATE PRODDTA.F554101 SET IMPRP6 = 'J' WHERE IMSTKT = 'P'
AND IMLNTY = 'N' AND IMITM IN(SELECT IMITM
FROM PRODDTA.F554102,PRODDTA.F554101 WHERE IBMCU = ' HOME01' AND IBITM = IMITM) AND IMPRP6 <> 'A'
Create SQL to combine the data from the F55 files into the Z Files. At this point, you still have not updated the system values. It will have no impact on the values in the system until you have processed the Z files with R4101Z1I – Item Master Unedited Transactions. This is important to know, because it is part of the safety included in this method. This piece can be run and rerun as well. You will have to delete the records from the Z File first.
INSERT INTO PRODDTA.F4101Z1 (SELECT 'EDUS' , 'F4102', DIGITS(A.IMITM),0, ' ', ' ' , ' ' , 0 , ' ' , 0 , 'N', ' ', 'C' , '2' , IBITM, 0 ,IBLITM, IBAITM, A.IMDSC1, A.IMDSC2, A.IMSRTX, A.IMALN, IBSRP1, IBSRP2, IBSRP3, IBSRP4, IBSRP5, IBSRP6, IBSRP7, IBSRP8, IBSRP9, IBSRP0, IBPRP1, IBPRP2, IBPRP3, IBPRP4, IBPRP5, IBPRP6, IBPRP7, IBPRP8, IBPRP9, IBPRP0, IBCDCD, IBPDGR, IBDSGP, IBPRGR, IBRPRC, IBORPR, IBBUYR, A.IMDRAW, A.IMRVNO,' ', A.IMVCUD, IBCARS, IBCARP, IBSHCN, IBSHCM, A.IMUOM1, A.IMUOM2, A.IMUOM3, A.IMUOM4, A.IMUOM6, A.IMUOM8, A.IMUOM9, A.IMUWUM, A.IMUVM1, A.IMSUTM, A.IMUMVW, IBCYCL, IBGLPT, A.IMPLEV, A.IMPPLV, A.IMCLEV, A.IMPRPO, IBCKAV, A.IMBPFG, IBSRCE, IBOT1Y, IBOT2Y, IBSTDP, IBFRMP, IBTHRP, IBSTDG, IBFRGD, IBTHGD, IBCOTY, IBSTKT, IBLNTY, A.IMCONT, IBBACK, IBIFLA, IBTFLA, IBINMG, IBABCS, IBABCM, IBABCI, IBOVR, IBWARR, IBCMCG, IBSRNR, A.IMPMTH, IBFIFO, IBLOTS, IBSLD, IBANPL, IBMPST, IBPCTM, IBMMPC, A.IMPTSC, IBSNS, IBLTLV, IBLTMF, IBLTCM,IBOPC, IBOPV, IBACQ, IBMLQ, IBLTPU, IBMPSP, IBMRPP, IBITC, IBORDW, 0,0,0,0,0, 0, IBEXPD, IBDEFD, IBSFLT, IBMAKE, A.IMCOBY, IBLLX, IBCMGL, IBCOMH, A.IMCSNN, B.IMAVRT, B.IMUPCN, B.IMSCC0, B.IMUMDF, B.IMUMS0, B.IMUMUP, B.IMUMS1, B.IMUMS2,B.IMUMS3, B.IMUMS4, B.IMUMS5, B.IMUMS6, B.IMUMS7, B.IMUMS8,0, B.IMWTRQ, B.IMEQTY,' ', ' ', ' ', ' ', 0, IBMCU, ' ', IBVEND,IBORIG, IBROPI, IBROQI, IBRQMX, IBRQMN, IBWOMO, IBSERV, IBSAFE, IBFUF1, IBTX, IBTAX1, IBMRPD, IBMRPC, IBUPC, IBMERL, IBECO, IBECTY, IBECOD, IBMOVD, IBQUED, IBSETL, IBSRNK, IBSRKF, IBTIMB, IBBQTY, IBMULT, IBLFDJ, ' ', ' ', ' ',IBURCD, IBURDT, IBURAT, IBURAB, IBURRF, ' ' , ' ', 0, 0, ' ', ' ' , ' ', ' ', 105251, 0, B.IMTMPL, B.IMSEG1, B.IMSEG2,B.IMSEG3, B.IMSEG4, B.IMSEG5, B.IMSEG6, B.IMSEG7, B.IMSEG8, B.IMSEG9, B.IMSEG0 FROM PRODDTA.F554101 A, PRODDTA.F4101T B, PRODDTA.F554102 WHERE IBITM=A.IMITM AND IBITM=B.IMITM AND IBMCU = B.IMMCU;
Note: This must be done once for the Item Master and once for the Item Branch. You must merely substitute the second argument in the Select statement 'F4102' with ‘F4101’ for the Item Branch.
Also, you might have to make minor changes to the SQL above to make sure you get all the values in all the fields. This sample might not completely match your company’s needs. Spend time reviewing this for your individual needs.
Finally, you can run the Z File processor R4101Z1I to process the records into the JD Edwards tables. I recommend testing this in the DEV or PROTOTYPE environments several times and validating the data before plunging into PRODUCTION. Also, you should consider refreshing (copying the PROD environment) PY or DV with the latest PROD data.
All of the issues that need to be resolved will be identified in the user’s Work Center messages list. The list may start out huge, but once you start hitting the errors with the most issues and getting them resolved in the test environment and in your production environment, the process will run faster and faster.
In summary, using Z File processing combined with SQL to update your Item Master and Item Branch is a fast, safe and relatively easy way to get the job done. The great thing about this process is that you combine the speed and ease of SQL with the reliability and integrity of the Z File processing. Just about every technical IT Professional knows enough SQL to do pretty much anything you think you might need. For further SQL help, consult the book SQL Pocket Guide by Jonathan Gennick (O’Reilly Media, Inc).
You can rest assured that all the information processed goes through the normal JD Edwards Business Functions and is validated, field by field.