IBM Mainframe Forum

Hello,
I have a somewhat complex problem that envolves 3 input files.
It might take several steps to get to the end result.
I would appreciate any suggestions.

The PASM input file has a record length of 380.
The OPPH input file has a record length of 398.
The OPVL input file has a record length of 380.

I need to extract all records from PASM and create a file containing the PA Number (1:11), Authorized Limit (92:16) and End Date (82:10).

I need to extract all records from OPVL where Ref.Trans.Code (182:2) = "PN" and create a file containing the whole PN Number (182:16), Closed Amount (101:16) and Last Check Date (146:10), then summarize Closed Amount by PN Number.

I need to extract all records from OPPH where the Blanket Number (131:11) is not spaces and create a file containing the Blanket Number (131:11) and Expended Amount (291:16), then summarize Expended Amount by Blanket Number.

I need to join the PASM file (PA Number) with the OPVL file (PN Number). Return all PASM records and only those OPVL records where PN Number = PA Number.

I need to join the PASM file (PA Number) with the OPPH file (Blanket Number). Return all PASM records and only those OPPH records where Blanket Number = PA Number.

The end result (file) should include all PA Numbers, End Date, Authorized Limit (from PASM), OPVL Closed Amount, OPPH Expended Amount, then calculate the Remaining Balance (Authorized Limit - OPVL Closed Amount - OPPH Expended amount)

If this is too complex, just let me know.
Thank you.

I forgot to mention that the amount fields are already in the format of Siiiiiiiiiiii.ii ( s is sign).

I need more information. Show an example of the records in each input file (relevant fields only), and the corresponding expected intermediate records, and final output records. If there can be duplicates at any point, show that in your example. Explain the "rules" in terms of the example.

Sample PASM records (with pos:length)


Sample OPVL records (with pos:length)


Sample OPPH records (with pos:length)


Join if the OPVL PN Number (from pos 192:11) equals the PA Number from PASM
Join if the OPPH Blanket Number equals the PA Number from PASM

Sample end result file:


There would be duplicate PA and PN Numbers, but then they would be summarized (on amounts) before the final output.

THanks.

Join if the OPVL PN Number (from pos 192:11) equals the PA Number from PASM
Join if the OPPH Blanket Number equals the PA Number from PASM

Huh? I don't see any matches between these numbers in your examples.

For the output,you show PA numbers of N2000002903 and N1000005282. I see blanket numbers in OPPH that match those numbers. But I don't see any values in PASM or OPVL that match those numbers. So your "rules" don't seem to match your examples.

You need to explain HOW you got those output records from the input records you show.

Here is a better example:

PASM file input records:


OPVL file input records:


OPPH file input records:


Join if the OPVL PN Number (from pos 192:11) equals the PA Number from PASM
--- D0000000025 is in the PASM file.

Join if the OPPH Blanket Number equals the PA Number from PASM
---N1000005282 and N2000002903 are in the PASM file

Sample end result file.


Does this help?
Thanks.

Where did the +35643.00 value come from? Shouldn't the value be +24948.00 from the sorted/summarized result for the N2000002903 record in OPPH?

Let's take this a step at a time to make sure I understand what you want. Let's start with the three extracted output files (before the join).

I need to extract all records from PASM and create a file containing the PA Number (1:11), Authorized Limit (92:16) and End Date (82:10).

I need to extract all records from OPVL where Ref.Trans.Code (182:2) = "PN" and create a file containing the whole PN Number (182:16), Closed Amount (101:16) and Last Check Date (146:10), then summarize Closed Amount by PN Number.

I need to extract all records from OPPH where the Blanket Number (131:11) is not spaces and create a file containing the Blanket Number (131:11) and Expended Amount (291:16), then summarize Expended Amount by Blanket Number.

Does this DFSORT/ICETOOL job give you the three extracted output data sets the way you want them?

Yes, you are correct on the amount should be +24948.00.

However, it should be OPVL not OPPL, in your code.

I am running the test now and will let you know the results.

OK. There were some typos (my fault) but I corrected them. > The PN number position starts at 187, not 182. I changed the code to reflect that. The OPPL was renamed to OPVL in all occurances.

The job ran ok after some minor output record length adjustments.

PASMOUT file :



OPVLOUT file:


OPPHOUT file:



So far so good.

Well, since you changed the job, please post the job you used.

Do you actually need to keep the PASMOUT, OPVLOUT and OPPHOUT data sets as separate data sets? Or are they just throwaway data sets to get us to the final output data set?

In your example, you only show matches for a particular key between two of the data sets at a time. Is it possible to have a match on a key in all three data sets?