natarajanibm
Warnings : 1
New User
Joined: 21 Mar 2009
Posts: 9
Location: chennai
|
|
|
|
| Code: |
EXEC SQL DECLARE SUSPENSE CURSOR FOR
***UNRESERVED PAYMENTS ***
SELECT A.TRTYP,
A.CLNTID,
A.CNNO,
A.TRANNO,
A.CRRSN,
A.ORICRAMT,
A.LFTOVAMT,
A.CREFDT,
DAYS(:LP-PROCESSING-DT) - DAYS(A.CREFDT),
"N",
C.CMPCODE
FROM VLP1013 A, VLP1082 B, VLP1011 C
WHERE A.LFTOVAMT > 0
AND A.CASTATCD ¬= "XT"
AND A.CREFDT BETWEEN "1991-01-01"
AND :WS-NEXT-WORK-DT
AND A.TRANNO = B.TRANNO
AND A.TRANNO BETWEEN 0000000001 AND :WS-MAX-TRANNO
AND B.ACCTPRDT BETWEEN "1991-01-01"
AND :LP-PROCESSING-DT
AND A.CLNTID = C.CLNTID
UNION ALL
SELECT A.TRTYP, THER UNRESERVED CREDITS ***
A.CLNTID,
A.CNNO,
A.TRANNO,
A.CRRSN,
A.ORICRAMT,
A.LFTOVAMT,
A.CREFDT,
DAYS(:LP-PROCESSING-DT) - DAYS(A.CREFDT),
"N",
C.CMPCODE
FROM VLP1013 A, VLP1066 B, VLP1011 C
WHERE A.LFTOVAMT > 0
AND A.CASTATCD ¬= "XT"
AND A.CREFDT BETWEEN "1991-01-01" AND :WS-NEXT-RK-DT
AND A.TRANNO = B.TRANNO
AND A.TRANNO BETWEEN 0000000001 AND :WS-MAX-TRANNO
AND B.ACCTPRDT BETWEEN "1991-01-01" AND :LP-PROCESSING-DT
AND A.CLNTID = C.CLNTID
UNION ALL
SELECT A.TRTYP, *** RESERVED PAYMENTS FOR BILLED CHARGES ***
A.CLNTID,
A.CNNO,
A.TRANNO,
A.CRRSN,
A.ORICRAMT,
SUM(D.DBAMT - D.DBAMTPND),
A.CREFDT,
DAYS(:LP-PROCESSING-DT) - DAYS(A.CREFDT),
"Y",
E.CMPCODE
FROM VLP1013 A, VLP1122 B, VLP1148 C, VLP1019 D,
VLP1011 E
WHERE A.TRANNO = B.CRTRANNO
AND B.DBTRANNO = D.TRANNO
AND B.DBTRSQNO = D.DBTRSQNO
AND B.DBTRANNO = C.TRANNO
AND C.TRSTATCD NOT IN ("CM", "OK")
AND A.CASTATCD ¬= "XT"
AND A.CREFDT BETWEEN "1991-01-01" AND :WS-NEXT-WORK-DT
AND A.TRANNO BETWEEN 0000000001 AND :WS-MAX-TRANNO
AND A.CLNTID = E.CLNTID
GROUP BY A.TRTYP, A.CLNTID, A.CNNO, A.TRANNO, A.CRRSN,
A.ORICRAMT, A.CREFDT, E.CMPCODE
UNION ALL
SELECT A.TRTYP, *** OTHER RESERVED CREDITS ***
A.CLNTID,
A.CNNO,
A.TRANNO,
A.CRRSN,
A.ORICRAMT,
SUM(D.DBAMT - D.DBAMTPND),
A.CREFDT,
DAYS(:LP-PROCESSING-DT) - DAYS(A.CREFDT),
"Y",
E.CMPCODE
FROM VLP1013 A, VLP1122 B, VLP1066 C, VLP1019 D,
VLP1011 E
WHERE A.TRANNO = B.CRTRANNO
AND B.DBTRANNO = D.TRANNO
AND B.DBTRSQNO = D.DBTRSQNO
AND B.DBTRANNO = C.TRANNO
AND C.TRSTATCD ¬= "CM"
AND A.CASTATCD ¬= "XT"
AND A.CREFDT BETWEEN "1991-01-01" AND :WS-NEXT-WORK-DT
AND A.TRANNO BETWEEN 0000000001 AND :WS-MAX-TRANNO
AND A.CLNTID = E.CLNTID
GROUP BY A.TRTYP, A.CLNTID, A.CNNO, A.TRANNO, A.CRRSN,
A.ORICRAMT, A.CREFDT, E.CMPCODE
UNION ALL
SELECT "PO" *** PAYMENTS TO CNA
,B.CLNTID
,B.CNNO
,A.ASSOCTXN
," "
,SUM(A.TXAMT)
,SUM(A.TXAMT)
,A.PROCDTE
,DAYS(:LP-PROCESSING-DT) - DAYS(A.PROCDTE)
,"N"
,C.CMPCODE
FROM VLP1220 A, VLP1014 B, VLP1011 C
WHERE A.PROPNO = B.PROPNO
AND B.CLNTID = C.CLNTID
AND SENDIND = "Y"
AND ((STATUS = "WB" AND PROCDTE <= :LP-PROCESSING-DT)
OR (STATUS ¬= "WB" AND PROCDTE <= :LP-PROCESSING-DT AND ACCTPRDT > :LP-PROCESSING-DT))
GROUP BY C.CMPCODE, B.CLNTID, B.CNNO,
A.ASSOCTXN, A.PROCDTE
ORDER BY 11 |
Code'd |
|
