JD Edwards World software may store both header and component records in the same file, the F3002, but it can be challenging to extract a report that shows those records. Here’s how we did it.
Files
F3002 – Bill of Materials Master File
F4101 – Item Master (IM)
F4102 – Item Branch Plant (IB)
F4102UJ – Joined logical on the F4102, F4101, F58X21
Request
The end user requested a report with the header records, component records, G/L Category, and the Item Number description.
Solution
When Spinnaker Support first approached this problem, we considered working within World Writer. However, in some World software versions, there’s a limit of 13,000 records that can be written to the file before it’s full and sends a message to QSYSOPR, telling it to expand the file by entering 9999. This isn’t good for an end user because they generally won’t have access to such messages, so they won’t be able to see them or respond.
Additionally, you can’t increase the size of the file with a CHGPF command because both Query/400 and World Writer delete and recreate the file during every run.
The problem with writing this report using Query/400 is that Query/400 does not use the JD Edwards Data Dictionary to deal with decimals — nor does it convert the date from Julian to Gregorian format.
Solution
The solution was to make a Query/400 query that would create the initial working file within the header records, then extract the Component records by reading the working file with World Writer.
Query/400
The first step is to create the work file.
When Spinnaker Support first approached this issue, we tried to join together the F3002, F4102, and F4101. This produced the expected reports, but also a lot of duplicate records. So then we used a joined logical file, built over the F4102 and the F4101, which eliminated the duplicate-record issue. The process looked like this:
- Create a Query/400 by joining the F3002 with the F4102UJ and showing matched records.
- Because the work file will be joined to the F3002 in the World Writer, two fields will have to be defined as result fields for the subsequent World Writers.
Set IBGPLT = SPGLPT (Spinnaker GLPT)
Set IMDSC1 = SPDSC1 (Spinnaker DSC1)
- Select the fields necessary to meet the report requirements, using the SP fields to bring in the G/L Category and description.
- Set any required data selection.
- Sort the records by Parent Item Number (IXKIT) and Component Line Number (IXCPNT).
- Set the Output type to 3 (Database file), and specify the file name and the library that the file will be created in.
World Writer
Once the work file is made, create the matching World Writer to produce the file that the user will download.
- Join the new work file with the F4102UJ, and set IXITM = IBITM.
- Select the needed fields, remembering to pull in the two SP fields for the parent record.
- Make any necessary data selection. To avoid duplicate records, I had to select IXCMCU and IBMCU. (Data sequencing was unnecessary.)
- For Additional Parameters, set the following values:
Print Cover Page – N.
Maximum Form Width – 378. Note: In some versions of World Writer, changing the Max Form width will not increase the width of the report, but it will increase the width of the output file.
Output Media – F.
Output File – Name the output file. Note: I ended the names of all output files with A, B, C… Thus, the work file ended in A and the output from the World Writer ended in B.
Output Library – Specify the name of the library where the file will be created. Note: Use the same library for both files.
Conclusion
If this solution is to be used by an end user who will be running this multiple times, create a Job Stream Submission menu (menu name starting with %). Selection one will be the Query/400 query, and selection two will be the JD Edwards World Writer.
