on 10-01-2015 8:29 AM
Hey All,
I have converted the store procedure from SQL Server to SAP HANA.
In my enclosed SP I have commented some code of blocks(as they are not allowing to create SP).
While I am uncommenting them, Error is generated of "SAP DBTech JDBC: [467] (at 4250): cannot use parameter variable: TO_POSTDATE:".
I dont know what is going wrong in that.
Please help.
DROP PROCEDURE "SP_TEMPORARY";
CREATE PROCEDURE "SP_TEMPORARY" (
IN INTERVAL integer,
IN AGEING_DATE varchar(10),
IN FROM_POSTDATE varchar(10),
IN TO_POSTDATE varchar(10),
IN FROM_DUEDATE varchar(10),
IN TO_DUEDATE varchar(10),
IN FROM_DOCDATE varchar(10),
IN TO_DOCDATE varchar(10),
IN CARDTYPE varchar(1),
IN FROMBPCODE varchar(20),
IN TOBPCODE varchar(20),
IN FROM_VERTICLE varchar(10),
IN TO_VERTICLE varchar(10),
IN FROM_DIVISION varchar(10),
IN TO_DIVISION varchar(10),
IN FROM_PROJECT varchar(100),
IN TO_PROJECT varchar(100),
IN AGE_BY varchar(10),
IN REPORT_TYPE varchar(10),
IN STATE varchar(10),
IN ZERO_BAL_RQD varchar(2),
IN DSP_RECON_TRN varchar(20)) AS
BEGIN
CREATE LOCAL TEMPORARY TABLE #INTRVL_TBL (COL1 integer,
COL2 integer,
COL3 integer,
COL4 integer,
COL5 integer,
COL6 integer,
COL7 integer,
COL8 integer,
COL9 integer,
COL10 integer,
COL11 integer,
COL12 integer)
;
INSERT
INTO #INTRVL_TBL (SELECT
0,
:INTERVAL,
:INTERVAL + 1,
:INTERVAL * 2,
:INTERVAL * 2 + 1,
:INTERVAL * 3,
:INTERVAL * 3 + 1,
:INTERVAL * 4,
:INTERVAL * 4 + 1,
:INTERVAL * 5,
:INTERVAL * 5 + 1,
:INTERVAL * 6
FROM DUMMY)
;
CREATE LOCAL TEMPORARY TABLE #UNADJ_AMT_TBL (CARDCODE varchar(20),
PROJECT varchar(20),
DOCNUM integer,
UNADJ_AMT decimal(19,2),
ADV_DUE_DY decimal(19,2))
;
IF (:CARDTYPE = 'S') THEN
INSERT
INTO #UNADJ_AMT_TBL (SELECT
OCRD."CardCode",
ODPO."Project",
ODPO."DocNum",
(CASE WHEN ODPO."VatSum" > 0
AND ODPO."DpmAppl" > 0
THEN SUM(ODPO."DpmAmnt" - ODPO."DpmAppl")
ELSE SUM(ODPO."DocTotal" + ODPO."WTSum" - ODPO."DpmAppl")
END),
ABS(DAYS_BETWEEN(ODPO."DocDate",ADD_DAYS('1900-01-01 00:00:00.000',:AGEING_DATE)))
FROM OCRD,
ODPO
WHERE OCRD."CardCode" = ODPO."CardCode"
AND OCRD."CardType" = IFNULL(:CARDTYPE,'')
AND ODPO."DocStatus" = 'C'
AND (CASE WHEN :FROM_POSTDATE = ''
THEN ''
ELSE ODPO."DocDate"
END) >= (CASE WHEN :FROM_POSTDATE = ''
THEN ''
ELSE :FROM_POSTDATE
END)
AND (CASE WHEN :TO_POSTDATE = ''
THEN ''
ELSE ODPO."DocDate"
END) <= (CASE WHEN :TO_POSTDATE = ''
THEN ''
ELSE :TO_POSTDATE
END)
AND (CASE WHEN :FROM_DUEDATE = ''
THEN ''
ELSE ODPO."DocDueDate"
END) >= (CASE WHEN :FROM_DUEDATE = ''
THEN ''
ELSE :FROM_DUEDATE
END)
AND (CASE WHEN :TO_DUEDATE = ''
THEN ''
ELSE ODPO."DocDueDate"
END) <= (CASE WHEN :TO_DUEDATE = ''
THEN ''
ELSE :TO_DUEDATE
END)
AND (CASE WHEN :FROM_DOCDATE = ''
THEN ''
ELSE ODPO."TaxDate"
END) >= (CASE WHEN :FROM_DOCDATE = ''
THEN ''
ELSE :FROM_DOCDATE
END)
AND (CASE WHEN :TO_DOCDATE = ''
THEN ''
ELSE ODPO."TaxDate"
END) <= (CASE WHEN :TO_DOCDATE = ''
THEN ''
ELSE :TO_DOCDATE
END)
AND (CASE WHEN :FROM_PROJECT = ''
THEN ''
ELSE ODPO."Project"
END) >= (CASE WHEN :FROM_PROJECT = ''
THEN ''
ELSE :FROM_PROJECT
END)
AND (CASE WHEN :TO_PROJECT = ''
THEN ''
ELSE ODPO."Project"
END) <= (CASE WHEN :TO_PROJECT = ''
THEN ''
ELSE :TO_PROJECT
END)
GROUP BY OCRD."CardCode",
ODPO."Project",
ODPO."DocNum",
ODPO."DocDate",
ODPO."VatSum",
ODPO."DpmAppl")
;
END
IF
;
IF (:CARDTYPE = 'C')
THEN INSERT
INTO "#UNADJ_AMT_TBL" (SELECT
OCRD."CardCode",
ODPI."Project",
ODPI."DocNum",
(CASE WHEN ODPI."VatSum" > 0
AND ODPI."DpmAppl" > 0
THEN SUM(ODPI."DpmAmnt" - ODPI."DpmAppl")
ELSE SUM(ODPI."DocTotal" + ODPI."WTSum" - ODPI."DpmAppl")
END),
ABS(DAYS_BETWEEN(ODPI."DocDate",ADD_DAYS('1900-01-01 00:00:00.000',:AGEING_DATE)))
FROM OCRD,
ODPI
WHERE OCRD."CardCode" = ODPI."CardCode"
AND OCRD."CardType" = IFNULL(:CARDTYPE,'')
AND ODPI."DocStatus" = 'C'
AND ODPI."ReceiptNum" IS NOT NULL
AND (CASE WHEN :FROM_POSTDATE = ''
THEN ''
ELSE ODPI."DocDate"
END) >= (CASE WHEN :FROM_POSTDATE = ''
THEN ''
ELSE :FROM_POSTDATE
END)
AND (CASE WHEN :TO_POSTDATE = ''
THEN ''
ELSE ODPI."DocDate"
END) <= (CASE WHEN :TO_POSTDATE = ''
THEN ''
ELSE :TO_POSTDATE
END)
AND (CASE WHEN :FROM_DUEDATE = ''
THEN ''
ELSE ODPI."DocDueDate"
END) >= (CASE WHEN :FROM_DUEDATE = ''
THEN ''
ELSE :FROM_DUEDATE
END)
AND (CASE WHEN :TO_DUEDATE = ''
THEN ''
ELSE ODPI."DocDueDate"
END) <= (CASE WHEN :TO_DUEDATE = ''
THEN ''
ELSE :TO_DUEDATE
END)
AND (CASE WHEN :FROM_DOCDATE = ''
THEN ''
ELSE ODPI."TaxDate"
END) >= (CASE WHEN :FROM_DOCDATE = ''
THEN ''
ELSE :FROM_DOCDATE
END)
AND (CASE WHEN :TO_DOCDATE = ''
THEN ''
ELSE ODPI."TaxDate"
END) <= (CASE WHEN :TO_DOCDATE = ''
THEN ''
ELSE :TO_DOCDATE
END)
AND (CASE WHEN :FROM_PROJECT = ''
THEN ''
ELSE ODPI."Project"
END) >= (CASE WHEN :FROM_PROJECT = ''
THEN ''
ELSE :FROM_PROJECT
END)
AND (CASE WHEN :TO_PROJECT = ''
THEN ''
ELSE ODPI."Project"
END) <= (CASE WHEN :TO_PROJECT = ''
THEN ''
ELSE :TO_PROJECT
END)
GROUP BY OCRD."CardCode",
ODPI."Project",
ODPI."DocNum",
ODPI."DocDate",
ODPI."VatSum",
ODPI."DpmAppl")
;
END
IF
;
CREATE LOCAL TEMPORARY TABLE #FUTURE_REMIT_TBL (CARDCODE varchar(20),
PROJECT varchar(20),
FUTURE_REMIT decimal(19,2))
;
IF :AGE_BY = 'DUE'
THEN INSERT
INTO #FUTURE_REMIT_TBL (SELECT
JDT1."ShortName",
JDT1."Project",
SUM(JDT1."Debit" - JDT1."Credit")
FROM OJDT,
JDT1,
OCRD
WHERE OJDT."TransId" = JDT1."TransId"
AND JDT1."ShortName" = OCRD."CardCode"
AND OCRD."CardType" = IFNULL(:CARDTYPE,'')
AND JDT1."RefDate" <= :AGEING_DATE
AND JDT1."DueDate" > :AGEING_DATE
AND (CASE WHEN :FROM_POSTDATE = ''
THEN ''
ELSE JDT1."RefDate"
END) >= (CASE WHEN :FROM_POSTDATE = ''
THEN ''
ELSE :FROM_POSTDATE
END)
AND (CASE WHEN :TO_POSTDATE = ''
THEN ''
ELSE JDT1."RefDate"
END) <= (CASE WHEN '' = ''
THEN ''
ELSE :TO_POSTDATE
END)
AND (CASE WHEN :FROM_DUEDATE = ''
THEN ''
ELSE JDT1."DueDate"
END) >= (CASE WHEN :FROM_DUEDATE = ''
THEN ''
ELSE :FROM_DUEDATE
END)
AND (CASE WHEN :TO_DUEDATE = ''
THEN ''
ELSE JDT1."DueDate"
END) <= (CASE WHEN :TO_DUEDATE = ''
THEN ''
ELSE :TO_DUEDATE
END)
AND (CASE WHEN :FROM_DOCDATE = ''
THEN ''
ELSE JDT1."TaxDate"
END) >= (CASE WHEN :FROM_DOCDATE = ''
THEN ''
ELSE :FROM_DOCDATE
END)
AND (CASE WHEN :TO_DOCDATE = ''
THEN ''
ELSE JDT1."TaxDate"
END) <= (CASE WHEN :TO_DOCDATE = ''
THEN ''
ELSE :TO_DOCDATE
END)
AND (CASE WHEN :FROM_VERTICLE = ''
THEN ''
ELSE JDT1."ProfitCode"
END) >= (CASE WHEN :FROM_VERTICLE = ''
THEN ''
ELSE :FROM_VERTICLE
END)
AND (CASE WHEN :TO_VERTICLE = ''
THEN ''
ELSE JDT1."ProfitCode"
END) <= (CASE WHEN :TO_VERTICLE = ''
THEN ''
ELSE :TO_VERTICLE
END)
AND (CASE WHEN :FROM_DIVISION = ''
THEN ''
ELSE JDT1."OcrCode4"
END) >= (CASE WHEN :FROM_DIVISION = ''
THEN ''
ELSE :FROM_DIVISION
END)
AND (CASE WHEN :TO_DIVISION = ''
THEN ''
ELSE JDT1."OcrCode4"
END) <= (CASE WHEN :TO_DIVISION = ''
THEN ''
ELSE :TO_DIVISION
END)
AND (CASE WHEN :FROM_PROJECT = ''
THEN ''
ELSE JDT1."Project"
END) >= (CASE WHEN :FROM_PROJECT = ''
THEN ''
ELSE :FROM_PROJECT
END)
AND (CASE WHEN :TO_PROJECT = ''
THEN ''
ELSE JDT1."Project"
END) <= (CASE WHEN :TO_PROJECT = ''
THEN ''
ELSE :TO_PROJECT
END)
AND (CASE WHEN :STATE = ''
THEN ''
ELSE JDT1."OcrCode3"
END) = (CASE WHEN :STATE = ''
THEN ''
ELSE :STATE
END)
GROUP BY JDT1."ShortName",
JDT1."Project")
;
END
IF
;
CREATE LOCAL TEMPORARY TABLE #MAIN_TBL (CARDCODE varchar(20),
CARDNAME varchar(100),
PROJECT varchar(20),
TRANS_TYPE varchar(10),
BASEDOCNUM integer,
POST_DATE timestamp,
VERTICLE varchar(20),
COSTCENTRE varchar(20),
STATE varchar(20),
DIVISION varchar(20),
BILL_NO varchar(100),
BILL_DATE timestamp,
JRNL_MEMO varchar(254),
ORIGIN_AMT decimal(19,2),
BALANCE decimal(19,2),
INTERVAL1 decimal(19,2),
INTERVAL2 decimal(19,2),
INTERVAL3 decimal(19,2),
INTERVAL4 decimal(19,2),
INTERVAL5 decimal(19,2),
INTERVAL6 decimal(19,2))
;
INSERT
INTO #MAIN_TBL (SELECT
JDT1."ShortName",
OCRD."CardName",
JDT1."Project",
(CASE JDT1."TransType" WHEN '46'
THEN 'PS' WHEN '13'
THEN 'IN' WHEN '14'
THEN 'CN' WHEN '18'
THEN 'PU' WHEN '19'
THEN 'PC' WHEN '24'
THEN 'RC' WHEN '203'
THEN 'DT' WHEN '204'
THEN 'DT' WHEN '30'
THEN 'JE' WHEN '-2'
THEN 'OB' WHEN '321'
THEN 'JR'
END) AS "TRANS_TYPE",
JDT1."BaseRef",
JDT1."RefDate",
JDT1."ProfitCode" AS "VERTICLE",
JDT1."OcrCode2" AS "COSTCENTRE",
JDT1."OcrCode3" AS "STATE",
JDT1."OcrCode4" AS "DIVISION",
JDT1."Ref2",
JDT1."TaxDate",
JDT1."LineMemo",
(CASE WHEN JDT1."DebCred" = 'D'
THEN JDT1."Debit"
ELSE 0 - JDT1."Credit"
END) AS "ORIGN_AMT",
(CASE WHEN JDT1."Debit" - JDT1."Credit" > 0
THEN ((JDT1."Debit") - (JDT1."Credit") - ABS(IFNULL((SELECT
SUM(ITR1."ReconSum")
FROM OITR,
ITR1
WHERE OITR."ReconNum" = ITR1."ReconNum"
AND ITR1."TransId" = OJDT."TransId"
AND ITR1."SrcObjAbs" = JDT1."CreatedBy"
AND ITR1."SrcObjTyp" = OJDT."TransType"
AND ITR1."ShortName" = JDT1."ShortName"
AND ITR1."TransRowId" = JDT1."Line_ID"
AND OITR."ReconDate" <= :AGEING_DATE),
0)))
ELSE (((JDT1."Debit") - (JDT1."Credit")) + ABS(IFNULL((SELECT
SUM(ITR1."ReconSum")
FROM OITR,
ITR1
WHERE OITR."ReconNum" = ITR1."ReconNum"
AND ITR1."TransId" = OJDT."TransId"
AND ITR1."SrcObjAbs" = JDT1."CreatedBy"
AND ITR1."TransRowId" = JDT1."Line_ID"
AND ITR1."SrcObjTyp" = OJDT."TransType"
AND ITR1."ShortName" = JDT1."ShortName"
AND OITR."ReconDate" <= :AGEING_DATE),
0)))
END) AS "BALANCE_DUE",
(CASE WHEN :AGE_BY = 'POST'
AND DAYS_BETWEEN(JDT1."RefDate",ADD_DAYS('1900-01-01 00:00:00.000',:AGEING_DATE)) <= :INTERVAL
THEN (CASE WHEN :DSP_RECON_TRN = 'Y'
THEN (CASE WHEN JDT1."DebCred" = 'D'
THEN JDT1."Debit"
ELSE 0 - JDT1."Credit"
END)
ELSE (CASE WHEN JDT1."Debit" - JDT1."Credit" > 0
THEN ((JDT1."Debit") - (JDT1."Credit") - ABS(IFNULL((SELECT
SUM(ITR1."ReconSum")
FROM OITR,
ITR1
WHERE OITR."ReconNum" = ITR1."ReconNum"
AND ITR1."TransId" = OJDT."TransId"
AND ITR1."SrcObjAbs" = JDT1."CreatedBy"
AND ITR1."SrcObjTyp" = OJDT."TransType"
AND ITR1."ShortName" = JDT1."ShortName"
AND ITR1."TransRowId" = JDT1."Line_ID"
AND OITR."ReconDate" <= :AGEING_DATE),
0)))
ELSE (((JDT1."Debit") - (JDT1."Credit")) + ABS(IFNULL((SELECT
SUM(ITR1."ReconSum")
FROM OITR,
ITR1
WHERE OITR."ReconNum" = ITR1."ReconNum"
AND ITR1."TransId" = OJDT."TransId"
AND ITR1."SrcObjAbs" = JDT1."CreatedBy"
AND ITR1."TransRowId" = JDT1."Line_ID"
AND ITR1."SrcObjTyp" = OJDT."TransType"
AND ITR1."ShortName" = JDT1."ShortName"
AND OITR."ReconDate" <= :AGEING_DATE),
0)))
END)
END) WHEN :AGE_BY = 'DUE'
AND DAYS_BETWEEN(JDT1."DueDate",ADD_DAYS('1900-01-01 00:00:00.000',:AGEING_DATE)) <= :INTERVAL
THEN (CASE WHEN :DSP_RECON_TRN = 'Y'
THEN (CASE WHEN JDT1."DebCred" = 'D'
THEN JDT1."Debit"
ELSE 0 - JDT1."Credit"
END)
ELSE (CASE WHEN JDT1."Debit" - JDT1."Credit" > 0
THEN ((JDT1."Debit") - (JDT1."Credit") - ABS(IFNULL((SELECT
SUM(ITR1."ReconSum")
FROM OITR,
ITR1
WHERE OITR."ReconNum" = ITR1."ReconNum"
AND ITR1."TransId" = OJDT."TransId"
AND ITR1."SrcObjAbs" = JDT1."CreatedBy"
AND ITR1."SrcObjTyp" = OJDT."TransType"
AND ITR1."ShortName" = JDT1."ShortName"
AND ITR1."TransRowId" = JDT1."Line_ID"
AND OITR."ReconDate" <= :AGEING_DATE),
0)))
ELSE (((JDT1."Debit") - (JDT1."Credit")) + ABS(IFNULL((SELECT
SUM(ITR1."ReconSum")
FROM OITR,
ITR1
WHERE OITR."ReconNum" = ITR1."ReconNum"
AND ITR1."TransId" = OJDT."TransId"
AND ITR1."SrcObjAbs" = JDT1."CreatedBy"
AND ITR1."TransRowId" = JDT1."Line_ID"
AND ITR1."SrcObjTyp" = OJDT."TransType"
AND ITR1."ShortName" = JDT1."ShortName"
AND OITR."ReconDate" <= :AGEING_DATE),
0)))
END)
END) WHEN :AGE_BY = 'DOC'
AND DAYS_BETWEEN(JDT1."TaxDate",ADD_DAYS('1900-01-01 00:00:00.000',:AGEING_DATE)) <= :INTERVAL
THEN (CASE WHEN :DSP_RECON_TRN = 'Y'
THEN (CASE WHEN JDT1."DebCred" = 'D'
THEN JDT1."Debit"
ELSE 0 - JDT1."Credit"
END)
ELSE (CASE WHEN JDT1."Debit" - JDT1."Credit" > 0
THEN ((JDT1."Debit") - (JDT1."Credit") - ABS(IFNULL((SELECT
SUM(ITR1."ReconSum")
FROM OITR,
ITR1
WHERE OITR."ReconNum" = ITR1."ReconNum"
AND ITR1."TransId" = OJDT."TransId"
AND ITR1."SrcObjAbs" = JDT1."CreatedBy"
AND ITR1."SrcObjTyp" = OJDT."TransType"
AND ITR1."ShortName" = JDT1."ShortName"
AND ITR1."TransRowId" = JDT1."Line_ID"
AND OITR."ReconDate" <= :AGEING_DATE),
0)))
ELSE (((JDT1."Debit") - (JDT1."Credit")) + ABS(IFNULL((SELECT
SUM(ITR1."ReconSum")
FROM OITR,
ITR1
WHERE OITR."ReconNum" = ITR1."ReconNum"
AND ITR1."TransId" = OJDT."TransId"
AND ITR1."SrcObjAbs" = JDT1."CreatedBy"
AND ITR1."TransRowId" = JDT1."Line_ID"
AND ITR1."SrcObjTyp" = OJDT."TransType"
AND ITR1."ShortName" = JDT1."ShortName"
AND OITR."ReconDate" <= :AGEING_DATE),
0)))
END)
END)
END) AS "0_30",
(CASE WHEN :AGE_BY = 'POST'
AND DAYS_BETWEEN(JDT1."RefDate",ADD_DAYS('1900-01-01 00:00:00.000',:AGEING_DATE)) > :INTERVAL
AND DAYS_BETWEEN(JDT1."RefDate",ADD_DAYS('1900-01-01 00:00:00.000',:AGEING_DATE)) <= (:INTERVAL * 2)
THEN (CASE WHEN :DSP_RECON_TRN = 'Y'
THEN (CASE WHEN JDT1."DebCred" = 'D'
THEN JDT1."Debit"
ELSE 0 - JDT1."Credit"
END)
ELSE (CASE WHEN JDT1."Debit" - JDT1."Credit" > 0
THEN ((JDT1."Debit") - (JDT1."Credit") - ABS(IFNULL((SELECT
SUM(ITR1."ReconSum")
FROM OITR,
ITR1
WHERE OITR."ReconNum" = ITR1."ReconNum"
AND ITR1."TransId" = OJDT."TransId"
AND ITR1."SrcObjAbs" = JDT1."CreatedBy"
AND ITR1."SrcObjTyp" = OJDT."TransType"
AND ITR1."ShortName" = JDT1."ShortName"
AND ITR1."TransRowId" = JDT1."Line_ID"
AND OITR."ReconDate" <= :AGEING_DATE),
0)))
ELSE (((JDT1."Debit") - (JDT1."Credit")) + ABS(IFNULL((SELECT
SUM(ITR1."ReconSum")
FROM OITR,
ITR1
WHERE OITR."ReconNum" = ITR1."ReconNum"
AND ITR1."TransId" = OJDT."TransId"
AND ITR1."SrcObjAbs" = JDT1."CreatedBy"
AND ITR1."TransRowId" = JDT1."Line_ID"
AND ITR1."SrcObjTyp" = OJDT."TransType"
AND ITR1."ShortName" = JDT1."ShortName"
AND OITR."ReconDate" <= :AGEING_DATE),
0)))
END)
END) WHEN :AGE_BY = 'DUE'
AND DAYS_BETWEEN(JDT1."DueDate",ADD_DAYS('1900-01-01 00:00:00.000',:AGEING_DATE)) > :INTERVAL
AND DAYS_BETWEEN(JDT1."DueDate",ADD_DAYS('1900-01-01 00:00:00.000',:AGEING_DATE)) <= (:INTERVAL * 2)
THEN (CASE WHEN :DSP_RECON_TRN = 'Y'
THEN (CASE WHEN JDT1."DebCred" = 'D'
THEN JDT1."Debit"
ELSE 0 - JDT1."Credit"
END)
ELSE (CASE WHEN JDT1."Debit" - JDT1."Credit" > 0
THEN ((JDT1."Debit") - (JDT1."Credit") - ABS(IFNULL((SELECT
SUM(ITR1."ReconSum")
FROM OITR,
ITR1
WHERE OITR."ReconNum" = ITR1."ReconNum"
AND ITR1."TransId" = OJDT."TransId"
AND ITR1."SrcObjAbs" = JDT1."CreatedBy"
AND ITR1."SrcObjTyp" = OJDT."TransType"
AND ITR1."ShortName" = JDT1."ShortName"
AND ITR1."TransRowId" = JDT1."Line_ID"
AND OITR."ReconDate" <= :AGEING_DATE),
0)))
ELSE (((JDT1."Debit") - (JDT1."Credit")) + ABS(IFNULL((SELECT
SUM(ITR1."ReconSum")
FROM OITR,
ITR1
WHERE OITR."ReconNum" = ITR1."ReconNum"
AND ITR1."TransId" = OJDT."TransId"
AND ITR1."SrcObjAbs" = JDT1."CreatedBy"
AND ITR1."TransRowId" = JDT1."Line_ID"
AND ITR1."SrcObjTyp" = OJDT."TransType"
AND ITR1."ShortName" = JDT1."ShortName"
AND OITR."ReconDate" <= :AGEING_DATE),
0)))
END)
END) WHEN :AGE_BY = 'DOC'
AND DAYS_BETWEEN(JDT1."TaxDate",ADD_DAYS('1900-01-01 00:00:00.000',:AGEING_DATE)) > :INTERVAL
AND DAYS_BETWEEN(JDT1."TaxDate",ADD_DAYS('1900-01-01 00:00:00.000',:AGEING_DATE)) <= (:INTERVAL * 2)
THEN (CASE WHEN :DSP_RECON_TRN = 'Y'
THEN (CASE WHEN JDT1."DebCred" = 'D'
THEN JDT1."Debit"
ELSE 0 - JDT1."Credit"
END)
ELSE (CASE WHEN JDT1."Debit" - JDT1."Credit" > 0
THEN ((JDT1."Debit") - (JDT1."Credit") - ABS(IFNULL((SELECT
SUM(ITR1."ReconSum")
FROM OITR,
ITR1
WHERE OITR."ReconNum" = ITR1."ReconNum"
AND ITR1."TransId" = OJDT."TransId"
AND ITR1."SrcObjAbs" = JDT1."CreatedBy"
AND ITR1."SrcObjTyp" = OJDT."TransType"
AND ITR1."ShortName" = JDT1."ShortName"
AND ITR1."TransRowId" = JDT1."Line_ID"
AND OITR."ReconDate" <= :AGEING_DATE),
0)))
ELSE (((JDT1."Debit") - (JDT1."Credit")) + ABS(IFNULL((SELECT
SUM(ITR1."ReconSum")
FROM OITR,
ITR1
WHERE OITR."ReconNum" = ITR1."ReconNum"
AND ITR1."TransId" = OJDT."TransId"
AND ITR1."SrcObjAbs" = JDT1."CreatedBy"
AND ITR1."TransRowId" = JDT1."Line_ID"
AND ITR1."SrcObjTyp" = OJDT."TransType"
AND ITR1."ShortName" = JDT1."ShortName"
AND OITR."ReconDate" <= :AGEING_DATE),
0)))
END)
END)
END) AS "31_60",
(CASE WHEN :AGE_BY = 'POST'
AND DAYS_BETWEEN(JDT1."RefDate",ADD_DAYS('1900-01-01 00:00:00.000',:AGEING_DATE)) > (:INTERVAL * 2)
AND DAYS_BETWEEN(JDT1."RefDate",ADD_DAYS('1900-01-01 00:00:00.000',:AGEING_DATE)) <= (:INTERVAL * 3)
THEN (CASE WHEN :DSP_RECON_TRN = 'Y'
THEN (CASE WHEN JDT1."DebCred" = 'D'
THEN JDT1."Debit"
ELSE 0 - JDT1."Credit"
END)
ELSE (CASE WHEN JDT1."Debit" - JDT1."Credit" > 0
THEN ((JDT1."Debit") - (JDT1."Credit") - ABS(IFNULL((SELECT
SUM(ITR1."ReconSum")
FROM OITR,
ITR1
WHERE OITR."ReconNum" = ITR1."ReconNum"
AND ITR1."TransId" = OJDT."TransId"
AND ITR1."SrcObjAbs" = JDT1."CreatedBy"
AND ITR1."SrcObjTyp" = OJDT."TransType"
AND ITR1."ShortName" = JDT1."ShortName"
AND ITR1."TransRowId" = JDT1."Line_ID"
AND OITR."ReconDate" <= :AGEING_DATE),
0)))
ELSE (((JDT1."Debit") - (JDT1."Credit")) + ABS(IFNULL((SELECT
SUM(ITR1."ReconSum")
FROM OITR,
ITR1
WHERE OITR."ReconNum" = ITR1."ReconNum"
AND ITR1."TransId" = OJDT."TransId"
AND ITR1."SrcObjAbs" = JDT1."CreatedBy"
AND ITR1."TransRowId" = JDT1."Line_ID"
AND ITR1."SrcObjTyp" = OJDT."TransType"
AND ITR1."ShortName" = JDT1."ShortName"
AND OITR."ReconDate" <= :AGEING_DATE),
0)))
END)
END) WHEN :AGE_BY = 'DUE'
AND DAYS_BETWEEN(JDT1."DueDate",ADD_DAYS('1900-01-01 00:00:00.000',:AGEING_DATE)) > (:INTERVAL * 2)
AND DAYS_BETWEEN(JDT1."DueDate",ADD_DAYS('1900-01-01 00:00:00.000',:AGEING_DATE)) <= (:INTERVAL * 3)
THEN (CASE WHEN :DSP_RECON_TRN = 'Y'
THEN (CASE WHEN JDT1."DebCred" = 'D'
THEN JDT1."Debit"
ELSE 0 - JDT1."Credit"
END)
ELSE (CASE WHEN JDT1."Debit" - JDT1."Credit" > 0
THEN ((JDT1."Debit") - (JDT1."Credit") - ABS(IFNULL((SELECT
SUM(ITR1."ReconSum")
FROM OITR,
ITR1
WHERE OITR."ReconNum" = ITR1."ReconNum"
AND ITR1."TransId" = OJDT."TransId"
AND ITR1."SrcObjAbs" = JDT1."CreatedBy"
AND ITR1."SrcObjTyp" = OJDT."TransType"
AND ITR1."ShortName" = JDT1."ShortName"
AND ITR1."TransRowId" = JDT1."Line_ID"
AND OITR."ReconDate" <= :AGEING_DATE),
0)))
ELSE (((JDT1."Debit") - (JDT1."Credit")) + ABS(IFNULL((SELECT
SUM(ITR1."ReconSum")
FROM OITR,
ITR1
WHERE OITR."ReconNum" = ITR1."ReconNum"
AND ITR1."TransId" = OJDT."TransId"
AND ITR1."SrcObjAbs" = JDT1."CreatedBy"
AND ITR1."TransRowId" = JDT1."Line_ID"
AND ITR1."SrcObjTyp" = OJDT."TransType"
AND ITR1."ShortName" = JDT1."ShortName"
AND OITR."ReconDate" <= :AGEING_DATE),
0)))
END)
END) WHEN :AGE_BY = 'DOC'
AND DAYS_BETWEEN(JDT1."TaxDate",ADD_DAYS('1900-01-01 00:00:00.000',:AGEING_DATE)) > (:INTERVAL * 2)
AND DAYS_BETWEEN(JDT1."TaxDate",ADD_DAYS('1900-01-01 00:00:00.000',:AGEING_DATE)) <= (:INTERVAL * 3)
THEN (CASE WHEN :DSP_RECON_TRN = 'Y'
THEN (CASE WHEN JDT1."DebCred" = 'D'
THEN JDT1."Debit"
ELSE 0 - JDT1."Credit"
END)
ELSE (CASE WHEN JDT1."Debit" - JDT1."Credit" > 0
THEN ((JDT1."Debit") - (JDT1."Credit") - ABS(IFNULL((SELECT
SUM(ITR1."ReconSum")
FROM OITR,
ITR1
WHERE OITR."ReconNum" = ITR1."ReconNum"
AND ITR1."TransId" = OJDT."TransId"
AND ITR1."SrcObjAbs" = JDT1."CreatedBy"
AND ITR1."SrcObjTyp" = OJDT."TransType"
AND ITR1."ShortName" = JDT1."ShortName"
AND ITR1."TransRowId" = JDT1."Line_ID"
AND OITR."ReconDate" <= :AGEING_DATE),
0)))
ELSE (((JDT1."Debit") - (JDT1."Credit")) + ABS(IFNULL((SELECT
SUM(ITR1."ReconSum")
FROM OITR,
ITR1
WHERE OITR."ReconNum" = ITR1."ReconNum"
AND ITR1."TransId" = OJDT."TransId"
AND ITR1."SrcObjAbs" = JDT1."CreatedBy"
AND ITR1."TransRowId" = JDT1."Line_ID"
AND ITR1."SrcObjTyp" = OJDT."TransType"
AND ITR1."ShortName" = JDT1."ShortName"
AND OITR."ReconDate" <= :AGEING_DATE),
0)))
END)
END)
END) AS "61_90",
(CASE WHEN :AGE_BY = 'POST'
AND DAYS_BETWEEN(JDT1."RefDate",ADD_DAYS('1900-01-01 00:00:00.000',:AGEING_DATE)) > (:INTERVAL * 3)
AND DAYS_BETWEEN(JDT1."RefDate",ADD_DAYS('1900-01-01 00:00:00.000',:AGEING_DATE)) <= (:INTERVAL * 4)
THEN (CASE WHEN :DSP_RECON_TRN = 'Y'
THEN (CASE WHEN JDT1."DebCred" = 'D'
THEN JDT1."Debit"
ELSE 0 - JDT1."Credit"
END)
ELSE (CASE WHEN JDT1."Debit" - JDT1."Credit" > 0
THEN ((JDT1."Debit") - (JDT1."Credit") - ABS(IFNULL((SELECT
SUM(ITR1."ReconSum")
FROM OITR,
ITR1
WHERE OITR."ReconNum" = ITR1."ReconNum"
AND ITR1."TransId" = OJDT."TransId"
AND ITR1."SrcObjAbs" = JDT1."CreatedBy"
AND ITR1."SrcObjTyp" = OJDT."TransType"
AND ITR1."ShortName" = JDT1."ShortName"
AND ITR1."TransRowId" = JDT1."Line_ID"
AND OITR."ReconDate" <= :AGEING_DATE),
0)))
ELSE (((JDT1."Debit") - (JDT1."Credit")) + ABS(IFNULL((SELECT
SUM(ITR1."ReconSum")
FROM OITR,
ITR1
WHERE OITR."ReconNum" = ITR1."ReconNum"
AND ITR1."TransId" = OJDT."TransId"
AND ITR1."SrcObjAbs" = JDT1."CreatedBy"
AND ITR1."TransRowId" = JDT1."Line_ID"
AND ITR1."SrcObjTyp" = OJDT."TransType"
AND ITR1."ShortName" = JDT1."ShortName"
AND OITR."ReconDate" <= :AGEING_DATE),
0)))
END)
END) WHEN :AGE_BY = 'DUE'
AND DAYS_BETWEEN(JDT1."DueDate",ADD_DAYS('1900-01-01 00:00:00.000',:AGEING_DATE)) > (:INTERVAL * 3)
AND DAYS_BETWEEN(JDT1."DueDate",ADD_DAYS('1900-01-01 00:00:00.000',:AGEING_DATE)) <= (:INTERVAL * 4)
THEN (CASE WHEN :DSP_RECON_TRN = 'Y'
THEN (CASE WHEN JDT1."DebCred" = 'D'
THEN JDT1."Debit"
ELSE 0 - JDT1."Credit"
END)
ELSE (CASE WHEN JDT1."Debit" - JDT1."Credit" > 0
THEN ((JDT1."Debit") - (JDT1."Credit") - ABS(IFNULL((SELECT
SUM(ITR1."ReconSum")
FROM OITR,
ITR1
WHERE OITR."ReconNum" = ITR1."ReconNum"
AND ITR1."TransId" = OJDT."TransId"
AND ITR1."SrcObjAbs" = JDT1."CreatedBy"
AND ITR1."SrcObjTyp" = OJDT."TransType"
AND ITR1."ShortName" = JDT1."ShortName"
AND ITR1."TransRowId" = JDT1."Line_ID"
AND OITR."ReconDate" <= :AGEING_DATE),
0)))
ELSE (((JDT1."Debit") - (JDT1."Credit")) + ABS(IFNULL((SELECT
SUM(ITR1."ReconSum")
FROM OITR,
ITR1
WHERE OITR."ReconNum" = ITR1."ReconNum"
AND ITR1."TransId" = OJDT."TransId"
AND ITR1."SrcObjAbs" = JDT1."CreatedBy"
AND ITR1."TransRowId" = JDT1."Line_ID"
AND ITR1."SrcObjTyp" = OJDT."TransType"
AND ITR1."ShortName" = JDT1."ShortName"
AND OITR."ReconDate" <= :AGEING_DATE),
0)))
END)
END) WHEN :AGE_BY = 'DOC'
AND DAYS_BETWEEN(JDT1."TaxDate",ADD_DAYS('1900-01-01 00:00:00.000',:AGEING_DATE)) > (:INTERVAL * 3)
AND DAYS_BETWEEN(JDT1."TaxDate",ADD_DAYS('1900-01-01 00:00:00.000',:AGEING_DATE)) <= (:INTERVAL * 4)
THEN (CASE WHEN :DSP_RECON_TRN = 'Y'
THEN (CASE WHEN JDT1."DebCred" = 'D'
THEN JDT1."Debit"
ELSE 0 - JDT1."Credit"
END)
ELSE (CASE WHEN JDT1."Debit" - JDT1."Credit" > 0
THEN ((JDT1."Debit") - (JDT1."Credit") - ABS(IFNULL((SELECT
SUM(ITR1."ReconSum")
FROM OITR,
ITR1
WHERE OITR."ReconNum" = ITR1."ReconNum"
AND ITR1."TransId" = OJDT."TransId"
AND ITR1."SrcObjAbs" = JDT1."CreatedBy"
AND ITR1."SrcObjTyp" = OJDT."TransType"
AND ITR1."ShortName" = JDT1."ShortName"
AND ITR1."TransRowId" = JDT1."Line_ID"
AND OITR."ReconDate" <= :AGEING_DATE),
0)))
ELSE (((JDT1."Debit") - (JDT1."Credit")) + ABS(IFNULL((SELECT
SUM(ITR1."ReconSum")
FROM OITR,
ITR1
WHERE OITR."ReconNum" = ITR1."ReconNum"
AND ITR1."TransId" = OJDT."TransId"
AND ITR1."SrcObjAbs" = JDT1."CreatedBy"
AND ITR1."TransRowId" = JDT1."Line_ID"
AND ITR1."SrcObjTyp" = OJDT."TransType"
AND ITR1."ShortName" = JDT1."ShortName"
AND OITR."ReconDate" <= :AGEING_DATE),
0)))
END)
END)
END) AS "91_120",
(CASE WHEN :AGE_BY = 'POST'
AND DAYS_BETWEEN(JDT1."RefDate",ADD_DAYS('1900-01-01 00:00:00.000',:AGEING_DATE)) > (:INTERVAL * 4)
AND DAYS_BETWEEN(JDT1."RefDate",ADD_DAYS('1900-01-01 00:00:00.000',:AGEING_DATE)) <= (:INTERVAL * 5)
THEN (CASE WHEN :DSP_RECON_TRN = 'Y'
THEN (CASE WHEN JDT1."DebCred" = 'D'
THEN JDT1."Debit"
ELSE 0 - JDT1."Credit"
END)
ELSE (CASE WHEN JDT1."Debit" - JDT1."Credit" > 0
THEN ((JDT1."Debit") - (JDT1."Credit") - ABS(IFNULL((SELECT
SUM(ITR1."ReconSum")
FROM OITR,
ITR1
WHERE OITR."ReconNum" = ITR1."ReconNum"
AND ITR1."TransId" = OJDT."TransId"
AND ITR1."SrcObjAbs" = JDT1."CreatedBy"
AND ITR1."SrcObjTyp" = OJDT."TransType"
AND ITR1."ShortName" = JDT1."ShortName"
AND ITR1."TransRowId" = JDT1."Line_ID"
AND OITR."ReconDate" <= :AGEING_DATE),
0)))
ELSE (((JDT1."Debit") - (JDT1."Credit")) + ABS(IFNULL((SELECT
SUM(ITR1."ReconSum")
FROM OITR,
ITR1
WHERE OITR."ReconNum" = ITR1."ReconNum"
AND ITR1."TransId" = OJDT."TransId"
AND ITR1."SrcObjAbs" = JDT1."CreatedBy"
AND ITR1."TransRowId" = JDT1."Line_ID"
AND ITR1."SrcObjTyp" = OJDT."TransType"
AND ITR1."ShortName" = JDT1."ShortName"
AND OITR."ReconDate" <= :AGEING_DATE),
0)))
END)
END) WHEN :AGE_BY = 'DUE'
AND DAYS_BETWEEN(JDT1."DueDate",ADD_DAYS('1900-01-01 00:00:00.000',:AGEING_DATE)) > (:INTERVAL * 4)
AND DAYS_BETWEEN(JDT1."DueDate",ADD_DAYS('1900-01-01 00:00:00.000',:AGEING_DATE)) <= (:INTERVAL * 5)
THEN (CASE WHEN :DSP_RECON_TRN = 'Y'
THEN (CASE WHEN JDT1."DebCred" = 'D'
THEN JDT1."Debit"
ELSE 0 - JDT1."Credit"
END)
ELSE (CASE WHEN JDT1."Debit" - JDT1."Credit" > 0
THEN ((JDT1."Debit") - (JDT1."Credit") - ABS(IFNULL((SELECT
SUM(ITR1."ReconSum")
FROM OITR,
ITR1
WHERE OITR."ReconNum" = ITR1."ReconNum"
AND ITR1."TransId" = OJDT."TransId"
AND ITR1."SrcObjAbs" = JDT1."CreatedBy"
AND ITR1."SrcObjTyp" = OJDT."TransType"
AND ITR1."ShortName" = JDT1."ShortName"
AND ITR1."TransRowId" = JDT1."Line_ID"
AND OITR."ReconDate" <= :AGEING_DATE),
0)))
ELSE (((JDT1."Debit") - (JDT1."Credit")) + ABS(IFNULL((SELECT
SUM(ITR1."ReconSum")
FROM OITR,
ITR1
WHERE OITR."ReconNum" = ITR1."ReconNum"
AND ITR1."TransId" = OJDT."TransId"
AND ITR1."SrcObjAbs" = JDT1."CreatedBy"
AND ITR1."TransRowId" = JDT1."Line_ID"
AND ITR1."SrcObjTyp" = OJDT."TransType"
AND ITR1."ShortName" = JDT1."ShortName"
AND OITR."ReconDate" <= :AGEING_DATE),
0)))
END)
END) WHEN :AGE_BY = 'DOC'
AND DAYS_BETWEEN(JDT1."TaxDate",ADD_DAYS('1900-01-01 00:00:00.000',:AGEING_DATE)) > (:INTERVAL * 4)
AND DAYS_BETWEEN(JDT1."TaxDate",ADD_DAYS('1900-01-01 00:00:00.000',:AGEING_DATE)) <= (:INTERVAL * 5)
THEN (CASE WHEN :DSP_RECON_TRN = 'Y'
THEN (CASE WHEN JDT1."DebCred" = 'D'
THEN JDT1."Debit"
ELSE 0 - JDT1."Credit"
END)
ELSE (CASE WHEN JDT1."Debit" - JDT1."Credit" > 0
THEN ((JDT1."Debit") - (JDT1."Credit") - ABS(IFNULL((SELECT
SUM(ITR1."ReconSum")
FROM OITR,
ITR1
WHERE OITR."ReconNum" = ITR1."ReconNum"
AND ITR1."TransId" = OJDT."TransId"
AND ITR1."SrcObjAbs" = JDT1."CreatedBy"
AND ITR1."SrcObjTyp" = OJDT."TransType"
AND ITR1."ShortName" = JDT1."ShortName"
AND ITR1."TransRowId" = JDT1."Line_ID"
AND OITR."ReconDate" <= :AGEING_DATE),
0)))
ELSE (((JDT1."Debit") - (JDT1."Credit")) + ABS(IFNULL((SELECT
SUM(ITR1."ReconSum")
FROM OITR,
ITR1
WHERE OITR."ReconNum" = ITR1."ReconNum"
AND ITR1."TransId" = OJDT."TransId"
AND ITR1."SrcObjAbs" = JDT1."CreatedBy"
AND ITR1."TransRowId" = JDT1."Line_ID"
AND ITR1."SrcObjTyp" = OJDT."TransType"
AND ITR1."ShortName" = JDT1."ShortName"
AND OITR."ReconDate" <= :AGEING_DATE),
0)))
END)
END)
END) AS "121_150",
(CASE WHEN :AGE_BY = 'POST'
AND DAYS_BETWEEN(JDT1."RefDate",ADD_DAYS('1900-01-01 00:00:00.000',:AGEING_DATE)) > (:INTERVAL * 5)
THEN (CASE WHEN :DSP_RECON_TRN = 'Y'
THEN (CASE WHEN JDT1."DebCred" = 'D'
THEN JDT1."Debit"
ELSE 0 - JDT1."Credit"
END)
ELSE (CASE WHEN JDT1."Debit" - JDT1."Credit" > 0
THEN ((JDT1."Debit") - (JDT1."Credit") - ABS(IFNULL((SELECT
SUM(ITR1."ReconSum")
FROM OITR,
ITR1
WHERE OITR."ReconNum" = ITR1."ReconNum"
AND ITR1."TransId" = OJDT."TransId"
AND ITR1."SrcObjAbs" = JDT1."CreatedBy"
AND ITR1."SrcObjTyp" = OJDT."TransType"
AND ITR1."ShortName" = JDT1."ShortName"
AND ITR1."TransRowId" = JDT1."Line_ID"
AND OITR."ReconDate" <= :AGEING_DATE),
0)))
ELSE (((JDT1."Debit") - (JDT1."Credit")) + ABS(IFNULL((SELECT
SUM(ITR1."ReconSum")
FROM OITR,
ITR1
WHERE OITR."ReconNum" = ITR1."ReconNum"
AND ITR1."TransId" = OJDT."TransId"
AND ITR1."SrcObjAbs" = JDT1."CreatedBy"
AND ITR1."TransRowId" = JDT1."Line_ID"
AND ITR1."SrcObjTyp" = OJDT."TransType"
AND ITR1."ShortName" = JDT1."ShortName"
AND OITR."ReconDate" <= :AGEING_DATE),
0)))
END)
END) WHEN :AGE_BY = 'DUE'
AND DAYS_BETWEEN(JDT1."DueDate",ADD_DAYS('1900-01-01 00:00:00.000',:AGEING_DATE)) > (:INTERVAL * 5)
THEN (CASE WHEN :DSP_RECON_TRN = 'Y'
THEN (CASE WHEN JDT1."DebCred" = 'D'
THEN JDT1."Debit"
ELSE 0 - JDT1."Credit"
END)
ELSE (CASE WHEN JDT1."Debit" - JDT1."Credit" > 0
THEN ((JDT1."Debit") - (JDT1."Credit") - ABS(IFNULL((SELECT
SUM(ITR1."ReconSum")
FROM OITR,
ITR1
WHERE OITR."ReconNum" = ITR1."ReconNum"
AND ITR1."TransId" = OJDT."TransId"
AND ITR1."SrcObjAbs" = JDT1."CreatedBy"
AND ITR1."SrcObjTyp" = OJDT."TransType"
AND ITR1."ShortName" = JDT1."ShortName"
AND ITR1."TransRowId" = JDT1."Line_ID"
AND OITR."ReconDate" <= :AGEING_DATE),
0)))
ELSE (((JDT1."Debit") - (JDT1."Credit")) + ABS(IFNULL((SELECT
SUM(ITR1."ReconSum")
FROM OITR,
ITR1
WHERE OITR."ReconNum" = ITR1."ReconNum"
AND ITR1."TransId" = OJDT."TransId"
AND ITR1."SrcObjAbs" = JDT1."CreatedBy"
AND ITR1."TransRowId" = JDT1."Line_ID"
AND ITR1."SrcObjTyp" = OJDT."TransType"
AND ITR1."ShortName" = JDT1."ShortName"
AND OITR."ReconDate" <= :AGEING_DATE),
0)))
END)
END) WHEN :AGE_BY = 'DOC'
AND DAYS_BETWEEN(JDT1."TaxDate",ADD_DAYS('1900-01-01 00:00:00.000',:AGEING_DATE)) > (:INTERVAL * 5)
THEN (CASE WHEN :DSP_RECON_TRN = 'Y'
THEN (CASE WHEN JDT1."DebCred" = 'D'
THEN JDT1."Debit"
ELSE 0 - JDT1."Credit"
END)
ELSE (CASE WHEN JDT1."Debit" - JDT1."Credit" > 0
THEN ((JDT1."Debit") - (JDT1."Credit") - ABS(IFNULL((SELECT
SUM(ITR1."ReconSum")
FROM OITR,
ITR1
WHERE OITR."ReconNum" = ITR1."ReconNum"
AND ITR1."TransId" = OJDT."TransId"
AND ITR1."SrcObjAbs" = JDT1."CreatedBy"
AND ITR1."SrcObjTyp" = OJDT."TransType"
AND ITR1."ShortName" = JDT1."ShortName"
AND ITR1."TransRowId" = JDT1."Line_ID"
AND OITR."ReconDate" <= :AGEING_DATE),
0)))
ELSE (((JDT1."Debit") - (JDT1."Credit")) + ABS(IFNULL((SELECT
SUM(ITR1."ReconSum")
FROM OITR,
ITR1
WHERE OITR."ReconNum" = ITR1."ReconNum"
AND ITR1."TransId" = OJDT."TransId"
AND ITR1."SrcObjAbs" = JDT1."CreatedBy"
AND ITR1."TransRowId" = JDT1."Line_ID"
AND ITR1."SrcObjTyp" = OJDT."TransType"
AND ITR1."ShortName" = JDT1."ShortName"
AND OITR."ReconDate" <= :AGEING_DATE),
0)))
END)
END)
END) AS "150+"
FROM OJDT,
JDT1,
OCRD
WHERE OJDT."TransId" = JDT1."TransId"
AND JDT1."ShortName" = OCRD."CardCode"
AND OCRD."CardType" = IFNULL(:CARDTYPE,'')
AND (CASE WHEN :FROM_POSTDATE = ''
THEN ''
ELSE JDT1."RefDate"
END) >= (CASE WHEN :FROM_POSTDATE = ''
THEN ''
ELSE :FROM_POSTDATE
END)
AND (CASE WHEN :TO_POSTDATE = ''
THEN ''
ELSE JDT1."RefDate"
END) <= (CASE WHEN :TO_POSTDATE = ''
THEN ''
ELSE :TO_POSTDATE
END)
AND (CASE WHEN :FROM_DUEDATE = ''
THEN ''
ELSE JDT1."DueDate"
END) >= (CASE WHEN :FROM_DUEDATE = ''
THEN ''
ELSE :FROM_DUEDATE
END)
AND (CASE WHEN :TO_DUEDATE = ''
THEN ''
ELSE JDT1."DueDate"
END) <= (CASE WHEN :TO_DUEDATE = ''
THEN ''
ELSE :TO_DUEDATE
END)
AND (CASE WHEN :FROM_DOCDATE = ''
THEN ''
ELSE JDT1."TaxDate"
END) >= (CASE WHEN :FROM_DOCDATE = ''
THEN ''
ELSE :FROM_DOCDATE
END)
AND (CASE WHEN :TO_DOCDATE = ''
THEN ''
ELSE JDT1."TaxDate"
END) <= (CASE WHEN :TO_DOCDATE = ''
THEN ''
ELSE :TO_DOCDATE
END)
AND (CASE WHEN :FROMBPCODE = ''
THEN ''
ELSE OCRD."CardCode"
END) >= (CASE WHEN :FROMBPCODE = ''
THEN ''
ELSE :FROMBPCODE
END)
AND (CASE WHEN :TOBPCODE = ''
THEN ''
ELSE OCRD."CardCode"
END) <= (CASE WHEN :TOBPCODE = ''
THEN ''
ELSE :TOBPCODE
END)
AND (CASE WHEN :FROM_PROJECT = ''
THEN ''
ELSE JDT1."Project"
END) >= (CASE WHEN :FROM_PROJECT = ''
THEN ''
ELSE :FROM_PROJECT
END)
AND (CASE WHEN :TO_PROJECT = ''
THEN ''
ELSE JDT1."Project"
END) <= (CASE WHEN :TO_PROJECT = ''
THEN ''
ELSE :TO_PROJECT
END)
AND (CASE WHEN :DSP_RECON_TRN = 'N'
THEN (CASE WHEN JDT1."Debit" - JDT1."Credit" > 0
THEN ((JDT1."Debit") - (JDT1."Credit") - ABS(IFNULL((SELECT
SUM(ITR1."ReconSum")
FROM OITR,
ITR1
WHERE OITR."ReconNum" = ITR1."ReconNum"
AND ITR1."TransId" = OJDT."TransId"
AND ITR1."SrcObjAbs" = JDT1."CreatedBy"
AND ITR1."SrcObjTyp" = OJDT."TransType"
AND ITR1."ShortName" = JDT1."ShortName"
AND ITR1."TransRowId" = JDT1."Line_ID"
AND OITR."ReconDate" <= :AGEING_DATE),
0)))
ELSE (((JDT1."Debit") - (JDT1."Credit")) + ABS(IFNULL((SELECT
SUM(ITR1."ReconSum")
FROM OITR,
ITR1
WHERE OITR."ReconNum" = ITR1."ReconNum"
AND ITR1."TransId" = OJDT."TransId"
AND ITR1."SrcObjAbs" = JDT1."CreatedBy"
AND ITR1."TransRowId" = JDT1."Line_ID"
AND ITR1."SrcObjTyp" = OJDT."TransType"
AND ITR1."ShortName" = JDT1."ShortName"
AND OITR."ReconDate" <= :AGEING_DATE),
0)))
END)
ELSE 1
END) <> (CASE WHEN :DSP_RECON_TRN = 'N'
THEN 0
ELSE -1
END))
;
CREATE LOCAL TEMPORARY TABLE #BP_BALANCE (CARDCODE varchar(20),
BALANCE decimal(19,2))
;
IF :ZERO_BAL_RQD = 'Y'
THEN INSERT
INTO #BP_BALANCE (SELECT
M.CARDCODE,
SUM(M.BALANCE + IFNULL(U.UNADJ_AMT,0))
FROM #MAIN_TBL M
LEFT OUTER JOIN #UNADJ_AMT_TBL U ON M.CARDCODE = U.CARDCODE
AND M.PROJECT = U.PROJECT
AND M.BASEDOCNUM = U.DOCNUM
GROUP BY M.CARDCODE)
;
ELSE INSERT
INTO #BP_BALANCE (SELECT
M.CARDCODE,
SUM(M.BALANCE + IFNULL(U.UNADJ_AMT,0))
FROM #MAIN_TBL M
LEFT OUTER JOIN #UNADJ_AMT_TBL U ON M.CARDCODE = U.CARDCODE
AND M.PROJECT = U.PROJECT
AND M.BASEDOCNUM = U.DOCNUM
GROUP BY M.CARDCODE HAVING SUM(M.BALANCE + IFNULL(U.UNADJ_AMT,0)) <> 0)
;
END IF;
IF :REPORT_TYPE = 'SMRY'
THEN
CREATE LOCAL TEMPORARY TABLE "#temp_final" AS ( SELECT
ROW_NUMBER() OVER (PARTITION BY OPRJ."PrjCode",
OPRJ."PrjName"
ORDER BY OPRJ."PrjCode",OPRJ."PrjName") AS "RNK",
OPRJ."PrjCode",
OPRJ."PrjName" AS "PROJECT",
M.CARDCODE AS "BPCODE",
M.CARDNAME AS "BPNAME",
CAST((CAST(IFNULL((SELECT
SUM(TT.TOTAL)
FROM (SELECT
IFNULL(SUM(OINV."DocTotal"),0.0) AS "TOTAL"
FROM OINV
WHERE
/*(CASE WHEN :FROM_POSTDATE = ''
THEN ''
ELSE OINV."DocDate"
END) >= (CASE WHEN :FROM_POSTDATE = ''
THEN ''
ELSE :FROM_POSTDATE
END)
AND (CASE WHEN :TO_POSTDATE = ''
THEN ''
ELSE OINV."DocDate"
END) <= (CASE WHEN :TO_POSTDATE = ''
THEN ''
ELSE :TO_POSTDATE
END) AND */
UPPER(OINV."Project") = UPPER(M."PROJECT") --collate
--AND IFNULL(:CARDTYPE,'') = 'C'
AND UPPER(OINV."CardCode") = UPPER(M."CARDCODE") --collate
UNION ALL
SELECT
IFNULL((OPCH."DocTotal"),0.0) AS "TOTAL"
FROM OPCH
WHERE OPCH."CardCode" = M.CARDCODE --collate
/*AND (CASE WHEN :FROM_POSTDATE = ''
THEN ''
ELSE OPCH."DocDate"
END) >= (CASE WHEN :FROM_POSTDATE = ''
THEN ''
ELSE :FROM_POSTDATE
END)
AND (CASE WHEN :TO_POSTDATE = ''
THEN ''
ELSE OPCH."DocDate"
END) <= (CASE WHEN :TO_POSTDATE = ''
THEN ''
ELSE :TO_POSTDATE
END) */
AND OPCH."Project" = M.PROJECT --collate
--AND IFNULL(:CARDTYPE,'') = 'S'
) AS TT),0.0) AS decimal(18,4)) - CAST(IFNULL((SELECT
SUM(B."Total")
FROM (SELECT
DISTINCT (ORIN."DocTotal") AS "Total"
FROM RIN1
INNER JOIN ORIN ON RIN1."DocEntry" = ORIN."DocEntry"
WHERE "BaseType" = '13'
AND ORIN."CardCode" = M.CARDCODE --collate
/* AND (CASE WHEN :FROM_POSTDATE = ''
THEN ''
ELSE ORIN."DocDate"
END) >= (CASE WHEN :FROM_POSTDATE = ''
THEN ''
ELSE :FROM_POSTDATE
END)
AND (CASE WHEN :TO_POSTDATE = ''
THEN ''
ELSE ORIN."DocDate"
END) <= (CASE WHEN :TO_POSTDATE = ''
THEN ''
ELSE :TO_POSTDATE
END) */
AND ORIN."Project" = M.PROJECT --collate
--AND IFNULL(:CARDTYPE,'') = 'C'
UNION ALL SELECT
DISTINCT (ORPC."DocTotal") AS "Total"
FROM ORPC
INNER JOIN RPC1 ON ORPC."DocEntry" = RPC1."DocEntry"
WHERE RPC1."BaseType" = '18'
AND ORPC."CardCode" = M.CARDCODE --collate
/* AND (CASE WHEN :FROM_POSTDATE = ''
THEN ''
ELSE ORPC."DocDate"
END) >= (CASE WHEN :FROM_POSTDATE = ''
THEN ''
ELSE :FROM_POSTDATE
END)
AND (CASE WHEN :TO_POSTDATE = ''
THEN ''
ELSE ORPC."DocDate"
END) <= (CASE WHEN :TO_POSTDATE = ''
THEN ''
ELSE :TO_POSTDATE
END) */
AND ORPC."Project" = M.PROJECT --collate
--AND IFNULL(:CARDTYPE,'')='S'
) AS B),0.0) AS decimal(18,4))) AS decimal(18,4)) AS "SALES",
CAST((CAST(IFNULL((SELECT
SUM(F.TOTAL)
FROM (SELECT
IFNULL(SUM(ORCT."DocTotal"),0.0) AS "TOTAL"
FROM ORCT
WHERE "DocType" = 'C'
AND ORCT."CardCode" = M.CARDCODE --collate
/* AND (CASE WHEN :FROM_POSTDATE = ''
THEN ''
ELSE ORCT."DocDate"
END) >= (CASE WHEN :FROM_POSTDATE = ''
THEN ''
ELSE :FROM_POSTDATE
END) */
--AND "DocDate" <= :TO_POSTDATE
AND ORCT."PrjCode" = M.PROJECT --collate
AND ORCT."Canceled" = 'N'
UNION ALL SELECT
IFNULL(SUM(OVPM."DocTotal"),0.0) AS "TOTAL"
FROM OVPM
WHERE "DocType" = 'S'
/*AND (CASE WHEN :FROM_POSTDATE = ''
THEN ''
ELSE OVPM."DocDate"
END) >= (CASE WHEN :FROM_POSTDATE = ''
THEN ''
ELSE :FROM_POSTDATE
END) */
AND cast(OVPM."CardCode" as nvarchar(20)) = cast(M."CARDCODE" as nvarchar(20)) --collate
AND OVPM."DocDate" <= '20200101'--:TO_POSTDATE
AND cast(OVPM."PrjCode" as nvarchar(20)) = cast(M."PROJECT" as nvarchar(20)) --collate
AND OVPM."Canceled" = 'N') AS F),0.0) AS decimal(18,4))) AS decimal(18,4)) AS "Collection",
IFNULL(SUM(M."INTERVAL1"), 0) + IFNULL(SUM(M."INTERVAL2"), 0) + IFNULL(SUM(M."INTERVAL3"), 0) + IFNULL(SUM(M."INTERVAL4"), 0) + IFNULL(SUM(M."INTERVAL5"), 0) + IFNULL(SUM(M."INTERVAL6"), 0) AS "BALANCE_DUE",
IFNULL(SUM(U."UNADJ_AMT"),0) AS "UNADJ_AMT",
IFNULL(F."FUTURE_REMIT", 0) AS "FUTURE_REMIT",
SUM(M."INTERVAL1") AS "INTRVL_1",
SUM(M."INTERVAL2") AS "INTRVL_2",
SUM(M."INTERVAL3") AS "INTRVL_3",
SUM(M."INTERVAL4") AS "INTRVL_4",
SUM(M."INTERVAL5") AS "INTRVL_5",
SUM(M."INTERVAL6") AS "INTRVL_6"
FROM "#MAIN_TBL" M
INNER JOIN "#BP_BALANCE" B ON M."CARDCODE" = B."CARDCODE"
INNER JOIN OPRJ ON OPRJ."PrjCode" = M."PROJECT" --collate
LEFT OUTER JOIN "#UNADJ_AMT_TBL" U ON M."CARDCODE" = U."CARDCODE"
AND M."BASEDOCNUM" = U."DOCNUM"
LEFT OUTER JOIN "#FUTURE_REMIT_TBL" F ON M."CARDCODE" = F."CARDCODE"
AND M."PROJECT" = F."PROJECT"
AND F."FUTURE_REMIT" IS NOT NULL
GROUP BY M."CARDCODE",
M."CARDNAME",
M."PROJECT",
F."FUTURE_REMIT",
OPRJ."PrjCode",
OPRJ."PrjName"
ORDER BY "PROJECT")
;
(SELECT "PrjCode", (CASE "PrjCode" WHEN 'Z_Grand Total' THEN '' ELSE "PROJECT" end) "PROJECT","BPCODE","BPNAME","SALES","Collection","BALANCE_DUE",UNADJ_AMT,FUTURE_REMIT,INTRVL_1,INTRVL_2,INTRVL_3,INTRVL_4,INTRVL_5,INTRVL_6 FROM (
SELECT (CASE WHEN GROUPING(data."PrjCode" ) = 0 THEN data."PrjCode" ELSE 'Z_Grand Total' END) "PrjCode" ,
(CASE WHEN GROUPING(data."PROJECT" ) = 0 THEN data."PROJECT" ELSE 'Project Wise Total' END) "PROJECT" ,
data."BPCODE" , (CASE WHEN GROUPING(data."PROJECT" ) = 0 THEN MAX(data."BPNAME") ELSE '' END) "BPNAME" ,
SUM(data."SALES") "SALES" ,SUM(data."Collection" ) "Collection" , SUM(data."BALANCE_DUE") "BALANCE_DUE",SUM(data."UNADJ_AMT") UNADJ_AMT,
SUM(data.FUTURE_REMIT) FUTURE_REMIT, SUM(data.INTRVL_1) INTRVL_1,SUM(data.INTRVL_2) INTRVL_2 , SUM(data.INTRVL_3) INTRVL_3 , SUM(data.INTRVL_4) INTRVL_4 , SUM(data.INTRVL_5) INTRVL_5 ,SUM( data.INTRVL_6) INTRVL_6
FROM "#temp_final" data GROUP BY data."PrjCode",data."PROJECT",data."RNK" ,data."BPCODE"
) AS data WHERE "BPCODE" IS NOT NULL OR "PROJECT" ='Project Wise Total')
;
END
IF
;
IF :REPORT_TYPE = 'DET'
THEN SELECT
OPRJ."PrjCode",
OPRJ."PrjName" AS "PROJECT",
M.CARDCODE AS "BPCODE",
M.CARDNAME AS "BPNAME",
M.TRANS_TYPE,
M.BASEDOCNUM,
M.POST_DATE,
M.BILL_NO,
M.BILL_DATE,
M.JRNL_MEMO,
M.ORIGIN_AMT,
IFNULL(M.INTERVAL1,0) + IFNULL(M.INTERVAL2,0) + IFNULL(M.INTERVAL3,0) + IFNULL(M.INTERVAL4,0) + IFNULL(M.INTERVAL5,0) + IFNULL(M.INTERVAL6,0) AS "BALANCE_DUE",
IFNULL(U.UNADJ_AMT,0) AS "UNADJ_AMT",
(CASE WHEN IFNULL(U.UNADJ_AMT,0) <> 0
THEN U.ADV_DUE_DY
END) AS "ADV_DUE_DY",
F.FUTURE_REMIT,
(M.INTERVAL1) AS "INTRVL_1",
(M.INTERVAL2) AS "INTRVL_2",
(M.INTERVAL3) AS "INTRVL_3",
(M.INTERVAL4) AS "INTRVL_4",
(M.INTERVAL5) AS "INTRVL_5",
(M.INTERVAL6) AS "INTRVL_6"
FROM #MAIN_TBL M
INNER JOIN #BP_BALANCE B ON M.CARDCODE = B.CARDCODE
INNER JOIN OPRJ ON OPRJ."PrjCode" = M.PROJECT --collate
LEFT OUTER JOIN #UNADJ_AMT_TBL U ON M.CARDCODE = U.CARDCODE
AND M.BASEDOCNUM = U.DOCNUM
LEFT OUTER JOIN #FUTURE_REMIT_TBL F ON M.CARDCODE = F.CARDCODE
AND M.PROJECT = F.PROJECT
AND F.FUTURE_REMIT IS NOT NULL
UNION ALL SELECT
OPRJ."PrjCode" || ' TOTAL ',
OPRJ."PrjName" AS "PROJECT",
'',
'',
'' AS "TRANS_TYPE",
NULL AS "BASEDOCNUM",
NULL AS "POST_DATE",
NULL AS "BILL_NO",
NULL AS "BILL_DATE",
NULL AS "JRNL_MEMO",
SUM(ORIGIN_AMT),
SUM(IFNULL(M.INTERVAL1,0) + IFNULL(M.INTERVAL2,0) + IFNULL(M.INTERVAL3,0) + IFNULL(M.INTERVAL4,0) + IFNULL(M.INTERVAL5,0) + IFNULL(M.INTERVAL6,0)) AS "BALANCE_DUE",
SUM(U.UNADJ_AMT) AS "UNADJ_AMT",
NULL AS "ADV_DUE_DY",
SUM(F.FUTURE_REMIT),
SUM(M.INTERVAL1) AS "INTRVL_1",
SUM(M.INTERVAL2) AS "INTRVL_2",
SUM(M.INTERVAL3) AS "INTRVL_3",
SUM(M.INTERVAL4) AS "INTRVL_4",
SUM(M.INTERVAL5) AS "INTRVL_5",
SUM(M.INTERVAL6) AS "INTRVL_6"
FROM #MAIN_TBL M
INNER JOIN #BP_BALANCE B ON M.CARDCODE = B.CARDCODE
INNER JOIN OPRJ ON OPRJ."PrjCode" = M.PROJECT --collate
LEFT OUTER JOIN #UNADJ_AMT_TBL U ON M.CARDCODE = U.CARDCODE
AND M.BASEDOCNUM = U.DOCNUM
LEFT OUTER JOIN #FUTURE_REMIT_TBL F ON M.CARDCODE = F.CARDCODE
AND M.PROJECT = F.PROJECT
AND F.FUTURE_REMIT IS NOT NULL
GROUP BY OPRJ."PrjCode",
OPRJ."PrjName"
UNION ALL SELECT
'zz_GRAND TOTAL ',
'' AS "PROJECT",
'',
'',
'' AS "TRANS_TYPE",
NULL AS "BASEDOCNUM",
NULL AS "POST_DATE",
NULL AS "BILL_NO",
NULL AS "BILL_DATE",
NULL AS "JRNL_MEMO",
SUM(ORIGIN_AMT),
SUM(IFNULL(M.INTERVAL1,0) + IFNULL(M.INTERVAL2,0) + IFNULL(M.INTERVAL3,0) + IFNULL(M.INTERVAL4,0) + IFNULL(M.INTERVAL5,0) + IFNULL(M.INTERVAL6,0)) AS "BALANCE_DUE",
SUM(U.UNADJ_AMT) AS "UNADJ_AMT",
NULL AS "ADV_DUE_DY",
SUM(F.FUTURE_REMIT),
SUM(M.INTERVAL1) AS "INTRVL_1",
SUM(M.INTERVAL2) AS "INTRVL_2",
SUM(M.INTERVAL3) AS "INTRVL_3",
SUM(M.INTERVAL4) AS "INTRVL_4",
SUM(M.INTERVAL5) AS "INTRVL_5",
SUM(M.INTERVAL6) AS "INTRVL_6"
FROM #MAIN_TBL M
INNER JOIN #BP_BALANCE B ON M.CARDCODE = B.CARDCODE
INNER JOIN OPRJ ON OPRJ."PrjCode" = M.PROJECT --collate
LEFT OUTER JOIN #UNADJ_AMT_TBL U ON M.CARDCODE = U.CARDCODE
AND M.BASEDOCNUM = U.DOCNUM
LEFT OUTER JOIN #FUTURE_REMIT_TBL F ON M.CARDCODE = F.CARDCODE
AND M.PROJECT = F.PROJECT
AND F.FUTURE_REMIT IS NOT NULL
ORDER BY PROJECT
;
END
IF
;
SELECT
OADM."CompnyName",
"CompnyAddr"
FROM OADM
;
SELECT
:INTERVAL AS "INTERVAL",
CAST(:AGEING_DATE AS timestamp) AS "AGING_DATE",
CAST((CASE WHEN :FROM_POSTDATE = ''
THEN NULL
ELSE :FROM_POSTDATE
END) AS timestamp) AS "FRM_POSTDT",
CAST(:TO_POSTDATE AS timestamp) AS "TO_POSTDT",
CAST((CASE WHEN :FROM_DUEDATE = ''
THEN NULL
ELSE :FROM_DUEDATE
END) AS timestamp) AS "FRM_DUEDT",
CAST((CASE WHEN :TO_DUEDATE = ''
THEN NULL
ELSE :TO_DUEDATE
END) AS timestamp) AS "TO_DUEDT",
CAST((CASE WHEN :FROM_DOCDATE = ''
THEN NULL
ELSE :FROM_DOCDATE
END) AS timestamp) AS "FRM_DOCDT",
CAST((CASE WHEN :TO_DOCDATE = ''
THEN NULL
ELSE :TO_DOCDATE
END) AS timestamp) AS "TO_DOCDT",
:CARDTYPE AS "CARDTYPE",
:FROMBPCODE AS "FROM_BP",
:TOBPCODE AS "TO_BP",
:FROM_VERTICLE AS "FROM_VERT",
:TO_VERTICLE AS "TO_VERT",
:FROM_DIVISION AS "FROM_DIV",
:TO_DIVISION AS "TO_DIV",
:FROM_PROJECT AS "FROM_PROJ",
:TO_PROJECT AS "TO_PROJ",
(CASE :AGE_BY WHEN 'DUE'
THEN 'Due Date' WHEN 'POST'
THEN 'Post Date' WHEN 'DOC'
THEN 'Doc Date'
END) AS "AGE_BY",
:REPORT_TYPE AS "RPT_TYPE"
FROM DUMMY
;
DROP TABLE "#INTRVL_TBL";
DROP TABLE "#UNADJ_AMT_TBL";
DROP TABLE "#FUTURE_REMIT_TBL";
DROP TABLE "#MAIN_TBL";
DROP TABLE "#BP_BALANCE";
DROP TABLE "#temp_final";
END
;
Hi,
I couldn't run the SP you had posted as there are so many tables you are referring inside it.
I looked at the commented blocks of code.
try replacing
/* AND (CASE WHEN :FROM_POSTDATE = ''
THEN ''
ELSE ORCT."DocDate"
END) >= (CASE WHEN :FROM_POSTDATE = ''
THEN ''
ELSE :FROM_POSTDATE
END) */
commented block mentioned by you by
AND (MAP(LENTH(:FROM_POSTDATE), 0, :FROM_POSTDATE, ORCT."DocDate") >= :FROM_POSTDATE)
As both would produce the same result I guess.
Regards
-Anil
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
User | Count |
---|---|
93 | |
10 | |
10 | |
9 | |
9 | |
7 | |
6 | |
5 | |
5 | |
4 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.