Skip to Content
avatar image
Former Member

Error: 'Cannot Use Parameter Variable' while Converting SP from SQL SERVER to SAP HANA.

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

;

Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

1 Answer

  • avatar image
    Former Member
    Oct 01, 2015 at 09:48 AM

    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

    Add comment
    10|10000 characters needed characters exceeded

    • Former Member

      Hello Anil,

      Thanks for your reply.

      But it still giving the error of "Cannot use Parameter variable :FROM_POSTDATE.

      its not work 😔