IBM Mainframe Forum

by **claywilly** » Thu Oct 16, 2008 10:19 pm

Hi Frank,

I don't really need the 3 output files.
However, I do need a file that contains the PN numbers from OPVL that do not exist in the PASM file.

Yes, It is possible to have a match in all three datasets.

Here is the JCL I used.

Select all

//S1 EXEC PGM=ICETOOL
//PASM DD DSN=PFS.FS99U.DBSEQ.AFN5.PASM.EXTRACT(+0),DISP=SHR
//OPPH DD DSN=PFS.FS99U.DBSEQ.AFN5.OPPH.EXTRACT(+0),DISP=SHR
//OPVL DD DSN=PFS.FS99U.DBSEQ.AFN5.OPVL.EXTRACT,DISP=SHR
//TOOLMSG DD SYSOUT=*
//DFSMSG DD SYSOUT=*
//PASMOUT DD DSN=ACVE023.SEQ.AFN5.PASMOUT,
// DISP=(NEW,CATLG,DELETE),
// UNIT=DISK,
// SPACE=(CYL,(300,20),RLSE),
// DCB=(RECFM=FB,LRECL=300,BLKSIZE=0)
//OPVLOUT DD DSN=ACVE023.SEQ.AFN5.OPVLOUT,
// DISP=(NEW,CATLG,DELETE),
// UNIT=DISK,
// SPACE=(CYL,(300,20),RLSE),
// DCB=(RECFM=FB,LRECL=202,BLKSIZE=0)
//OPPHOUT DD DSN=ACVE023.SEQ.AFN5.OPPHOUT,
// DISP=(NEW,CATLG,DELETE),
// UNIT=DISK,
// SPACE=(CYL,(300,20),RLSE),
// DCB=(RECFM=FB,LRECL=306,BLKSIZE=0)
//TOOLIN DD *
COPY FROM(PASM) TO(PASMOUT) USING(CTL1)
SORT FROM(OPVL) USING(CTL2)
SORT FROM(OPPH) USING(CTL3)
/*
//CTL1CNTL DD *
INREC BUILD=(1:1,11,82:82,10,92:92,16)
/*
//CTL2CNTL DD *
INCLUDE COND=(187,2,CH,EQ,C'PN')
INREC BUILD=(101:101,16,146:146,10,187:187,16)
SORT FIELDS=(187,16,CH,A)
OUTFIL FNAMES=OPVLOUT,NODETAIL,REMOVECC,
SECTIONS=(187,16,
TRAILER3=(101:TOT=(101,16,SFF,EDIT=(SIIIIIIIIIIIT.TT),
SIGNS=(+,-)),146:146,10,187:187,16))
/*
//CTL3CNTL DD *
INCLUDE COND=(131,11,CH,NE,C' ')
INREC BUILD=(131:131,11,291:291,16)
SORT FIELDS=(131,11,CH,A)
OUTFIL FNAMES=OPPHOUT,NODETAIL,REMOVECC,
SECTIONS=(131,11,
TRAILER3=(131:131,11,
291:TOT=(291,16,SFF,EDIT=(SIIIIIIIIIIIT.TT),
SIGNS=(+,-))))
/*
//

What does "SFF" do when you total the amounts?

Thanks.

by **Frank Yaeger** » Fri Oct 17, 2008 1:41 am

SFF (signed free form format) handles numbers with decimal points, + and - signs, etc.

I don't really need the 3 output files.

Sigh. I interpreted your first post to mean you did.

However, I do need a file that contains the PN numbers from OPVL that do not exist in the PASM file.

Yes, It is possible to have a match in all three datasets.

I'm not sure what you mean by the first sentence. Is this another output data set besides the one with the joined fields? I don't think you showed either of these cases in your example.

I think we've gotten offtrack here. Let's start over. PLEASE show me an example of the records in your input files and what you want for output with all cases covered. Be specific about the output data set(s) you need, and the "rules" for getting from input to that output (if you want to talk about intermediate data sets, label them as such).

by **claywilly** » Fri Oct 17, 2008 1:58 am

Hi Frank,

I believe you are on the right track. I supposed that the 3 output datasets created so far would just be 'temporary' in that they would be spliced together to create the final output file. In that sense, if they are temporary, then I don't need to keep them.

I did suggest that another file be created for those PN numbers that do not match the PA numbers in the PASM file. - It would be nice to have.

I doubt we need to start over. The current output from the 3 datasets contain over 50k records each. I also know that there are PA and PN numbers that match in all 3 datasets. Sorry that the sample records I provided don't really indicate a match - I just got lazy than trying to match a few records out of 150k of them.

So currently the output the way it is now is correct, we just need to combine them all into one output file.
The intermediate data sets, I would assume would be the 3 output datasets we have now.

If then it is possible to splice or merge all three of these datasets into one, then perform the Remaining Balance calculation - we would be done.

What do you think?

by **Frank Yaeger** » Fri Oct 17, 2008 2:19 am

I think I'd like you to go back and create an example that shows what you want to do. If you don't want the intermediate data sets, then I don't need to create them the way I did when I thought you needed them. If you want two output data sets, it still isn't clear to me what they are. I'd prefer NOT to have to go back through all of the notes to figure out what you want (I do have a day job developing DFSORT). So if you want to pursue this, I'm going to put the burden on you to give me a complete example and rules I can work with.

by **claywilly** » Fri Oct 17, 2008 5:04 am

Maybe this is more clear ..
I currently have 3 input datasets:
1) PASM â€“ which contain fields (p:l):
PA Number (1:11)
End Date (82:10)
Authorized Amount (92:16) Already formatted (siiiiiiiiiiii.ii)
2) OPVL â€“ which contain fields (p:l):
OPVL Closed Amount (101:16) Already formatted (siiiiiiiiiiii.ii)
Last Check Date (146:10)
Ref.Trans.Code (187:2)
PN Number (192:11)
3) OPPH â€“ which contain fields (p:l)
Blanket Number (131:11)
Expended Amount (291:16) Already formatted (siiiiiiiiiiii.ii)

Intermediate files needed:

PASMout: (temp)
Sorted by PA Number (1:11)

OPVLout: (temp)
Only extract records containing â€œPNâ€ in Ref.Trans.Code (187:2)
Record layout:
PN Number (192:11), OPVL Closed Amount (101:16), Last Check Date (146:10)
Summarize OPVL Closed Amount by PN Numbers

OPPHout: (temp)
Only extract records where Blanket Number is not blank.
Record layout:
Blanket Number (131:11), Expended Amount (291:16)
Summarize Expended Amount by Blanket Numbers

MatchPN file: (temp)
Match OPVL (PN Number) with the PASM (PA Number)

MatchBLNKT file: (temp)
Match OPPH (Blanket Number) with the PASM (PA Number)

NonMatchPN file: (keep)
Records of PN Numbers that do not match with PASM (PA Number)

----
Final Output file: (keep)
Record layout: (combo of match files)
PA Number , End Date (PASM), Authorized Amount (PASM), OPVL Closed Amount (OPVLout), Expended Amount (OPPHout), Remaining Balance
Remaining Balance = (Authorized Amount â€“ OPVL Closed Amount â€“ OPPH Expended Amount)

(These input records are just examples, not the real data)
Sample PASM input records:

Select all

PA Number 1:11 End Date 82:10 Auth. Amount 92:16
D0000000022 â€¦â€¦ 4/30/1998 â€¦â€¦ +100000.00
D0000000025 â€¦â€¦ 11/15/1997 â€¦â€¦ +520000.00
D0000000030 â€¦â€¦ 5/24/1997 â€¦â€¦ +50000.00
N1000005282 â€¦â€¦ 9/30/1996 â€¦â€¦ +1200000.00
N2000002903 â€¦â€¦ 7/12/1999 â€¦â€¦ +53070.00

Sample OPVL input records:

Select all

1:100 Closed Amt. 101:16 Last Check Date 146:10 PN Number 187:16
â€¦. +2100.00 â€¦. 08/13/2002 â€¦. PN012D0000000025 â€¦.
â€¦. +39829.67 â€¦. 04/29/2002 â€¦. PO012RB000054.02 â€¦.
â€¦. +8866.50 â€¦. 04/29/2002 â€¦. PN012D0000000025 â€¦.
â€¦. -13726.56 â€¦. 05/30/2002 â€¦. PO012RB000054.02 â€¦.
â€¦. +14685.00 â€¦. 05/30/2002 â€¦. PN012D0000000025 â€¦.
â€¦.
Sorted / Summarized result (Just "PN" numbers are sorted and summed)
â€¦. +25651.50 â€¦. 05/30/2002 â€¦. PN012D0000000025

Sample OPPH input records:

Select all

1:130 Blanket Num. 131:11 Expended Amt. 291:16
â€¦. N2000002903 â€¦. +24948.00
â€¦. N3000002903 â€¦. +10695.00
â€¦. N1000005282 â€¦. +10070.00
â€¦. N1000005282 â€¦. +0.00
â€¦. N1000005282 â€¦. +13911.50

Sorted / Summarized result
â€¦. N2000002903 â€¦. 35643.00
â€¦. N1000005282 â€¦. 23981.50

JCL that creates the 3 output datasets (PASMout, OPVLout and OPPHout)

Select all

//S1 EXEC PGM=ICETOOL
//TOOLMSG DD SYSOUT=*
//DFSMSG DD SYSOUT=*
//PASM DD DSN=... PASM input file
//OPPL DD DSN=... OPPL input file
//OPPH DD DSN=... OPPH input file
//PASMOUT DD DSN=... PASM output file
//OPPLOUT DD DSN=... OPPL output file
//OPPHOUT DD DSN=... OPPH output file
//TOOLIN DD *
COPY FROM(PASM) TO(PASMOUT) USING(CTL1)
SORT FROM(OPPL) USING(CTL2)
SORT FROM(OPPH) USING(CTL3)
/*
//CTL1CNTL DD *
INREC BUILD=(1:1,11,82:82,10,92:92,16)
/*
//CTL2CNTL DD *
INCLUDE COND=(182,2,CH,EQ,C'PN')
INREC BUILD=(101:101,16,146:146,10,182:182,16)
SORT FIELDS=(182,16,CH,A)
OUTFIL FNAMES=OPPLOUT,NODETAIL,REMOVECC,
SECTIONS=(182,16,
TRAILER3=(101:TOT=(101,16,SFF,EDIT=(SIIIIIIIIIIIT.TT),
SIGNS=(+,-)),146:146,10,182:182,16))
/*
//CTL3CNTL DD *
INCLUDE COND=(131,11,CH,NE,C' ')
INREC BUILD=(131:131,11,291:291,16)
SORT FIELDS=(131,11,CH,A)
OUTFIL FNAMES=OPPHOUT,NODETAIL,REMOVECC,
SECTIONS=(131,11,
TRAILER3=(131:131,11,
291:TOT=(291,16,SFF,EDIT=(SIIIIIIIIIIIT.TT),
SIGNS=(+,-))))
/*

The following Data is real output.

Sample PASMout records:

Select all

0-15 82-107
----+----1----+ ----+----9----+----0----+--
*************** ***************************
D0000000012 11/30/1996 +55000.00
D0000000013 11/30/1996 +2506454.00
D0000000020 03/31/1997 +47000.00 <
D0000000022 04/30/1998 +100000.00 <
D0000000023 04/30/1995 +18000.00 <
D0000000025 11/15/1997 +520000.00
â€¦
N1000003319 11/15/1997 +750000.00 <

Sample OPVLout records:

Select all

100-120 145-156 185-202
0----+----1----+----2 +----5----+- +----9----+----0--
********************* *********** ******************
+64550.00 07/30/2008 PN001F2000002042
+128.46 06/14/2002 PN001N1000003319 <
+3629.25 02/22/2007 PN001N1000003488
+46099.66 02/15/2008 PN001N1000004520
+71642.94 01/10/2008 PN001N1000007362
+277.45 11/25/2003 PN001N1000009176
+326.38 02/21/2006 PN001N3000001358
+884.17 05/29/2002 PN001X4000000219

Sample OPPHout records:

Select all

130-142 293-306
3----+----4-- --+----0----+-
************* **************
DA00000015 +0.00
D0000000007 +18570.50
D0000000020 +17319.30 <
D0000000022 +3276.50 <
D0000000023 +1838.20 <
D0000000032 +8584.62
D0000000039 +27306.70
D0000000040 +107026.30

Sample MatchPN records:

Select all

â€¦
N1000003319 11/15/1997 +750000.00
â€¦

Sample MatchBLNKT records:

Select all

â€¦
D0000000020 +17319.30 <
D0000000022 +3276.50 <
D0000000023 +1838.20 <
â€¦

Sample NonMatchPN records:

Select all

â€¦
PN001F2000002042 +64550.00 07/30/2008
PN001X4000000219 +884.17 05/29/2002
â€¦

Sample expected Final Output file:

Select all

PA Number End Date Auth. Amount OPVL Closed Amt. OPPH Expended Amt. Rem.Balance
D0000000020 03/31/1997 +47000.00 +0.00 +17319.30 +29680.70
D0000000022 04/30/1995 +100000.00 +0.00 +3276.50 +96723.50
D0000000023 04/30/1998 +18000.00 +0.00 +1838.20 +16161.80
N1000003319 11/15/1997 +750000.00 +128.46 +0.00 +749871.54

â€¦..

by **Frank Yaeger** » Fri Oct 17, 2008 5:52 am

I'm still a bit confused.

You labelled 6 files as "Intermediate files needed". Do you actually need to keep any or all of these files? If so, which ones? If I can create the final output file without creating one or more of these intermediate files, do you care?

You don't show any final output records with values from OPVL and OPPH, although I thought you said that could happen. Can it happen? Could you have an output record with non-zero values for both "OPVL Closed Amt." and "OPPH Expended Amt."? I was hoping you would show all possible cases in your new example.

by **claywilly** » Fri Oct 17, 2008 6:05 am

I get more confused everytime I think about it.

The intermediate files that I don't need to keep are marked "temp". If you can work the code another way where these files are not needed, be my guest. The files marked "keep" are the ones I need - NonMatchPN's and the Final Output file.

The final output file consists of the matchPN's and matchBLNKT files. They both would have OPVL and OPPH records that match PA Numbers in the PASM file. (marked with '<' )

There could be non-zero and negative amounts. If it is a non-zero then it should be converted to "0.00".

Thanks.

by **Frank Yaeger** » Fri Oct 17, 2008 10:48 pm

I'm not clear on the answer to this question from my previous post.

Could you have an output record with non-zero values for both "OPVL Closed Amt." and "OPPH Expended Amt."?

You said:

There could be non-zero and negative amounts. If it is a non-zero then it should be converted to "0.00".

Where can there be negative amounts? Where can there be non-zero amounts that you want to change to"0.00"?

Can you show an example?

I'm trying to help, but without a clear explanation of what you want to do, it's rather difficult.

by **claywilly** » Fri Oct 17, 2008 11:29 pm

Could you have an output record with non-zero values for both "OPVL Closed Amt." and "OPPH Expended Amt."?

--yes there could be non-zero values for both fields.

The negative amounts could occur in the OPVL Closed Amount field.

Here is an example of an final output file

Select all

PA_Number    Auth_amount    Expended PN Closed Amount Remaining Balance
N1000004232 217,692.00 217,691.30    -7,256.40    7,257.10
N1000007503 9,948.00    8,348.00    -709.73    2,309.73
F2000002590 9,000.00    2,584.88    -171.32    6,586.44
F1000001135 1,000.00       -105.00    1,105.00
F2000000489 40,000.00       -102.83    40,102.83

The Remaining Balance is Auth.amount - expended- closed amount.

Hope it is getting clearer.

thanks.

by **Frank Yaeger** » Sat Oct 18, 2008 1:00 am

Yes, it is getting clearer. Now I just have to find time to work on it.

IBM Mainframe Forum

Help with Icetool, Splice, Sum and Calculate complex sort

Re: Help with Icetool, Splice, Sum and Calculate complex sort

Re: Help with Icetool, Splice, Sum and Calculate complex sort

Re: Help with Icetool, Splice, Sum and Calculate complex sort

Re: Help with Icetool, Splice, Sum and Calculate complex sort

Re: Help with Icetool, Splice, Sum and Calculate complex sort

Re: Help with Icetool, Splice, Sum and Calculate complex sort

Re: Help with Icetool, Splice, Sum and Calculate complex sort

Re: Help with Icetool, Splice, Sum and Calculate complex sort

Re: Help with Icetool, Splice, Sum and Calculate complex sort

Re: Help with Icetool, Splice, Sum and Calculate complex sort