SELECT ACCOUNTNAME,
SUBLEDGERNAME,
DOCID,
DOCDATE,
CURRENCY,
REFNO,
CREDITDAYS,
DUEDATE,
NDAYS,
AMOUNT,
SETTLED,
OUTSTANDING,
AGE1,
AGE2,
AGE3,
AGE4,
AGE5,
AGE6,
AGE7,
AGE8,
REFBLDATE,
REFBLNO,
DAMT
FROM (SELECT C.ACCOUNTNAME,
B.SUBLEDGERNAME,
A.DOCID,
A.DOCDATE,
A.CURRENCY,
A.REFNO,
A.CREDITDAYS,
A.DUEDATE,
TO_DATE (A.DUEDATE) - TO_DATE ( :TODT) AS NDAYS,
DECODE (SIGN ( (ABS (A.NAMOUNT) + RAMOUNT)),
-1, 0,
(ABS (A.NAMOUNT) + RAMOUNT))
AMOUNT,
DECODE (SIGN ( (A.NAMOUNT + RAMOUNT)),
-1, (A.NAMOUNT + RAMOUNT),
0)
+ NVL (X.SETTLED, 0)
SETTLED,
(A.NAMOUNT + RAMOUNT + NVL (X.SETTLED, 0)) OUTSTANDING,
A.REFBLDATE,
A.REFBLNO,
DECODE (SIGN ( (TO_DATE (A.DUEDATE) - TO_DATE ( :TODT))),
1, 0,
(A.NAMOUNT + RAMOUNT + NVL (X.SETTLED, 0)))
DAMT,
(CASE
WHEN TO_DATE ( :TODT)
- DECODE ( :PDATE, 'Due_Date', A.DUEDATE, A.DOCDATE) <
:SLAB1
THEN
(A.NAMOUNT + RAMOUNT + NVL (X.SETTLED, 0))
ELSE
0
END)
AS AGE1,
(CASE
WHEN TO_DATE ( :TODT)
- DECODE ( :PDATE, 'Due_Date', A.DUEDATE, A.DOCDATE) BETWEEN :SLAB1
AND ( :SLAB2
- 1)
THEN
(A.NAMOUNT + RAMOUNT + NVL (X.SETTLED, 0))
ELSE
0
END)
AS AGE2,
(CASE
WHEN TO_DATE ( :TODT)
- DECODE ( :PDATE, 'Due_Date', A.DUEDATE, A.DOCDATE) BETWEEN :SLAB2
AND ( :SLAB3
- 1)
THEN
(A.NAMOUNT + RAMOUNT + NVL (X.SETTLED, 0))
ELSE
0
END)
AS AGE3,
(CASE
WHEN TO_DATE ( :TODT)
- DECODE ( :PDATE, 'Due_Date', A.DUEDATE, A.DOCDATE) BETWEEN :SLAB3
AND ( :SLAB4
- 1)
THEN
(A.NAMOUNT + RAMOUNT + NVL (X.SETTLED, 0))
ELSE
0
END)
AS AGE4,
(CASE
WHEN TO_DATE ( :TODT)
- DECODE ( :PDATE, 'Due_Date', A.DUEDATE, A.DOCDATE) BETWEEN :SLAB4
AND ( :SLAB5
- 1)
THEN
(A.NAMOUNT + RAMOUNT + NVL (X.SETTLED, 0))
ELSE
0
END)
AS AGE5,
(CASE
WHEN TO_DATE ( :TODT)
- DECODE ( :PDATE, 'Due_Date', A.DUEDATE, A.DOCDATE) BETWEEN :SLAB5
AND ( :SLAB6
- 1)
THEN
(A.NAMOUNT + RAMOUNT + NVL (X.SETTLED, 0))
ELSE
0
END)
AS AGE6,
(CASE
WHEN TO_DATE ( :TODT)
- DECODE ( :PDATE, 'Due_Date', A.DUEDATE, A.DOCDATE) BETWEEN :SLAB6
AND ( :SLAB7
- 1)
THEN
(A.NAMOUNT + RAMOUNT + NVL (X.SETTLED, 0))
ELSE
0
END)
AS AGE7,
(CASE
WHEN TO_DATE ( :TODT)
- DECODE ( :PDATE, 'Due_Date', A.DUEDATE, A.DOCDATE) >=
:SLAB7
THEN
(A.NAMOUNT + RAMOUNT + NVL (X.SETTLED, 0))
ELSE
0
END)
AS AGE8
FROM ARAPDETAILS A,
MG_SUBLEDGER B,
MG_ACCOUNT C,
COMPANY CO,
BRANCH BR,
CURRENCYS CU,
( SELECT REFNO,
NVL (SUM (SETTLED), 0) AS SETTLED,
SUBLEDGERCODE
FROM (SELECT A.DOCID AS REFNO,
A.NAMOUNT AS SETTLED,
A.SUBLEDGERCODE
FROM ARAPADJUSTMENTS A,
MG_SUBLEDGER B,
MG_ACCOUNT C,
COMPANY CO,
BRANCH BR,
CURRENCYS CU
WHERE A.CANCEL = 'F'
AND A.SUBLEDGERCODE = B.SUBLEDGERCODE
AND B.CONTROLACCOUNT = C.MG_ACCOUNTID
AND A.COMPANY = CO.COMPANYID
AND A.BRANCH = BR.BRANCHID
AND C.ACATCODE = 'r'
AND CU.CNAME = C.ACURRENCY
AND C.ACCOUNTNAME = :PNAME
AND ( LOWER (B.SUBLEDGERNAME) =
LOWER ( :PSUBLEDGERNAME)
OR 'all' = LOWER ( :PSUBLEDGERNAME))
AND CO.COMPANYNAME = ''
AND A.DOCDATE <= :TODT
AND 'native' = LOWER ( :PCURRENCY)
AND A.MAPNAME <> 'crrec2'
AND A.NAMOUNT < 0
UNION ALL
SELECT Z.DOCID, A.NAMOUNT AS SETTLED, A.SUBLEDGERCODE
FROM ARAPADJUSTMENTS A,
MG_SUBLEDGER B,
MG_ACCOUNT C,
COMPANY CO,
BRANCH BR,
ARRECEIPTDTL H,
ARAPDETAILS Z,
CURRENCYS CU
WHERE A.CANCEL = 'F'
AND A.SUBLEDGERCODE = B.SUBLEDGERCODE
AND B.CONTROLACCOUNT = C.MG_ACCOUNTID
AND A.COMPANY = CO.COMPANYID
AND A.BRANCH = BR.BRANCHID
AND C.ACATCODE = 'r'
AND CU.CNAME = C.ACURRENCY
AND C.ACCOUNTNAME = :PNAME
AND ( LOWER (B.SUBLEDGERNAME) =
LOWER ( :PSUBLEDGERNAME)
OR 'all' = LOWER ( :PSUBLEDGERNAME))
AND CO.COMPANYNAME = ''
AND A.DOCDATE <= :TODT
AND 'native' = LOWER ( :PCURRENCY)
AND A.MAPNAME = 'crrec2'
AND A.NAMOUNT < 0
AND H.ARRECEIPTDTLID = A.SOURCEID
AND Z.ARAPDETAILSID = H.ARAPDETAILSID
AND Z.CANCEL = 'F')
GROUP BY REFNO, SUBLEDGERCODE) X
WHERE A.CANCEL = 'F'
AND A.SUBLEDGERCODE = B.SUBLEDGERCODE
AND B.CONTROLACCOUNT = C.MG_ACCOUNTID
AND A.COMPANY = CO.COMPANYID
AND A.BRANCH = BR.BRANCHID
AND C.ACATCODE = 'r'
AND CU.CNAME = C.ACURRENCY
AND C.ACCOUNTNAME = :PNAME
AND ( LOWER (B.SUBLEDGERNAME) = LOWER ( :PSUBLEDGERNAME)
OR 'all' = LOWER ( :PSUBLEDGERNAME))
AND CO.COMPANYNAME = ''
AND A.DOCDATE <= :TODT
AND 'native' = LOWER ( :PCURRENCY)
AND A.DOCID = X.REFNO(+)
AND A.SUBLEDGERCODE = X.SUBLEDGERCODE(+)
AND A.NAMOUNT > 0
UNION ALL
SELECT C.ACCOUNTNAME,
B.SUBLEDGERNAME,
A.DOCID,
A.DOCDATE,
A.CURRENCY,
A.REFNO,
A.CREDITDAYS,
A.DUEDATE,
TO_DATE (A.DUEDATE) - TO_DATE ( :TODT) AS NDAYS,
DECODE (SIGN ( (ABS (A.NAMOUNT) + RAMOUNT)),
-1, 0,
(ABS (A.NAMOUNT) + RAMOUNT))
AMOUNT,
DECODE (SIGN ( (A.NAMOUNT + RAMOUNT)),
-1, (A.NAMOUNT + RAMOUNT),
0)
+ NVL (X.SETTLED, 0)
SETTLED,
(A.NAMOUNT + RAMOUNT + NVL (X.SETTLED, 0)) OUTSTANDING,
A.REFBLDATE,
A.REFBLNO,
DECODE (SIGN ( (TO_DATE (A.DUEDATE) - TO_DATE ( :TODT))),
1, 0,
(A.NAMOUNT + RAMOUNT + NVL (X.SETTLED, 0)))
DAMT,
(CASE
WHEN TO_DATE ( :TODT)
- DECODE ( :PDATE, 'Due_Date', A.DUEDATE, A.DOCDATE) <
:SLAB1
THEN
(A.NAMOUNT + RAMOUNT + NVL (X.SETTLED, 0))
ELSE
0
END)
AS AGE1,
(CASE
WHEN TO_DATE ( :TODT)
- DECODE ( :PDATE, 'Due_Date', A.DUEDATE, A.DOCDATE) BETWEEN :SLAB1
AND ( :SLAB2
- 1)
THEN
(A.NAMOUNT + RAMOUNT + NVL (X.SETTLED, 0))
ELSE
0
END)
AS AGE2,
(CASE
WHEN TO_DATE ( :TODT)
- DECODE ( :PDATE, 'Due_Date', A.DUEDATE, A.DOCDATE) BETWEEN :SLAB2
AND ( :SLAB3
- 1)
THEN
(A.NAMOUNT + RAMOUNT + NVL (X.SETTLED, 0))
ELSE
0
END)
AS AGE3,
(CASE
WHEN TO_DATE ( :TODT)
- DECODE ( :PDATE, 'Due_Date', A.DUEDATE, A.DOCDATE) BETWEEN :SLAB3
AND ( :SLAB4
- 1)
THEN
(A.NAMOUNT + RAMOUNT + NVL (X.SETTLED, 0))
ELSE
0
END)
AS AGE4,
(CASE
WHEN TO_DATE ( :TODT)
- DECODE ( :PDATE, 'Due_Date', A.DUEDATE, A.DOCDATE) BETWEEN :SLAB4
AND ( :SLAB5
- 1)
THEN
(A.NAMOUNT + RAMOUNT + NVL (X.SETTLED, 0))
ELSE
0
END)
AS AGE5,
(CASE
WHEN TO_DATE ( :TODT)
- DECODE ( :PDATE, 'Due_Date', A.DUEDATE, A.DOCDATE) BETWEEN :SLAB5
AND ( :SLAB6
- 1)
THEN
(A.NAMOUNT + RAMOUNT + NVL (X.SETTLED, 0))
ELSE
0
END)
AS AGE6,
(CASE
WHEN TO_DATE ( :TODT)
- DECODE ( :PDATE, 'Due_Date', A.DUEDATE, A.DOCDATE) BETWEEN :SLAB6
AND ( :SLAB7
- 1)
THEN
(A.NAMOUNT + RAMOUNT + NVL (X.SETTLED, 0))
ELSE
0
END)
AS AGE7,
(CASE
WHEN TO_DATE ( :TODT)
- DECODE ( :PDATE, 'Due_Date', A.DUEDATE, A.DOCDATE) >=
:SLAB7
THEN
(A.NAMOUNT + RAMOUNT + NVL (X.SETTLED, 0))
ELSE
0
END)
AS AGE8
FROM ARAPDETAILS A,
MG_SUBLEDGER B,
MG_ACCOUNT C,
COMPANY CO,
BRANCH BR,
CURRENCYS CU,
( SELECT REFNO,
NVL (SUM (SETTLED), 0) AS SETTLED,
SUBLEDGERCODE
FROM (SELECT A.DOCID AS REFNO,
A.NAMOUNT AS SETTLED,
A.SUBLEDGERCODE
FROM ARAPADJUSTMENTS A,
MG_SUBLEDGER B,
MG_ACCOUNT C,
COMPANY CO,
BRANCH BR,
CURRENCYS CU
WHERE A.CANCEL = 'F'
AND A.SUBLEDGERCODE = B.SUBLEDGERCODE
AND B.CONTROLACCOUNT = C.MG_ACCOUNTID
AND A.COMPANY = CO.COMPANYID
AND A.BRANCH = BR.BRANCHID
AND C.ACATCODE = 'r'
AND CU.CNAME = C.ACURRENCY
AND C.ACCOUNTNAME = :PNAME
AND ( LOWER (B.SUBLEDGERNAME) =
LOWER ( :PSUBLEDGERNAME)
OR 'all' = LOWER ( :PSUBLEDGERNAME))
AND CO.COMPANYNAME = ''
AND A.DOCDATE <= :TODT
AND 'native' = LOWER ( :PCURRENCY)
AND A.MAPNAME <> 'crrec3'
AND A.NAMOUNT > 0
UNION ALL
SELECT A.REFNO, A.NAMOUNT AS SETTLED, A.SUBLEDGERCODE
FROM ARAPADJUSTMENTS A,
MG_SUBLEDGER B,
MG_ACCOUNT C,
COMPANY CO,
BRANCH BR,
CURRENCYS CU
WHERE A.CANCEL = 'F'
AND A.SUBLEDGERCODE = B.SUBLEDGERCODE
AND B.CONTROLACCOUNT = C.MG_ACCOUNTID
AND A.COMPANY = CO.COMPANYID
AND A.BRANCH = BR.BRANCHID
AND C.ACATCODE = 'r'
AND CU.CNAME = C.ACURRENCY
AND C.ACCOUNTNAME = :PNAME
AND ( LOWER (B.SUBLEDGERNAME) =
LOWER ( :PSUBLEDGERNAME)
OR 'all' = LOWER ( :PSUBLEDGERNAME))
AND CO.COMPANYNAME = ''
AND A.DOCDATE <= :TODT
AND 'native' = LOWER ( :PCURRENCY)
AND A.MAPNAME = 'crrec3'
AND A.NAMOUNT > 0)
GROUP BY REFNO, SUBLEDGERCODE) X
WHERE A.CANCEL = 'F'
AND A.SUBLEDGERCODE = B.SUBLEDGERCODE
AND B.CONTROLACCOUNT = C.MG_ACCOUNTID
AND A.COMPANY = CO.COMPANYID
AND A.BRANCH = BR.BRANCHID
AND C.ACATCODE = 'r'
AND CU.CNAME = C.ACURRENCY
AND C.ACCOUNTNAME = :PNAME
AND ( LOWER (B.SUBLEDGERNAME) = LOWER ( :PSUBLEDGERNAME)
OR 'all' = LOWER ( :PSUBLEDGERNAME))
AND CO.COMPANYNAME = ''
AND A.DOCDATE <= :TODT
AND 'native' = LOWER ( :PCURRENCY)
AND A.DOCID = X.REFNO(+)
AND A.SUBLEDGERCODE = X.SUBLEDGERCODE(+)
AND A.NAMOUNT < 0)
WHERE OUTSTANDING <> 0
ORDER BY 1, 2
Donate. I desperately need donations to survive due to my health
Get paid by answering surveys Click here
Click here to donate
Remote/Work from Home jobs
Comments
Post a Comment