WITH
GLH_ITEM AS
(
SELECT GLH.LHJNEN,GLH.LHJRF1,GHH.HHTRNO,AVM.VENDOR,AVM.VNDNAM,
trim(GCR.CRSG03)
||CASE WHEN TRIM(IFNULL(GCR.CRSG04,''))<>'' THEN '-'||TRIM(GCR.CRSG04) ELSE '' END
||CASE WHEN TRIM(IFNULL(GCR.CRSG05,''))<>'' THEN '-'||TRIM(GCR.CRSG05) ELSE '' END
||CASE WHEN TRIM(IFNULL(GCR.CRSG06,''))<>'' THEN '-'||TRIM(GCR.CRSG06) ELSE '' END
||CASE WHEN TRIM(IFNULL(GCR.CRSG07,''))<>'' THEN '-'||TRIM(GCR.CRSG07) ELSE '' END AS CRSG03,
trim(GSV.SVLDES )
||CASE WHEN trim(IFNULL(gsv4.svldes,'')) <> '' THEN '-' || TRIM(GSV4.SVLDES) ELSE '' END
||CASE WHEN trim(IFNULL((case when zcc.cccode <> '' then zcc.ccnot1 else gsv5.svldes END),'')) <> ''THEN '-' || TRIM(case when zcc.cccode <> '' then zcc.ccnot1 else gsv5.svldes END ) ELSE ''END
||CASE WHEN trim(IFNULL(gsv6.svldes,'')) <> '' THEN '-'||TRIM(GSV6.SVLDES) ELSE '' END
||CASE WHEN trim(IFNULL(gsv7.svldes,'')) <> '' THEN '-'||TRIM(GSV7.SVLDES) ELSE '' END AS CRDESC,
GHH.HHCURR,
SUBSTR(CAST(GHH.HHJDAT AS VARCHAR(60)),7,2)||'/'||SUBSTR(CAST(GHH.HHJDAT AS VARCHAR(60)),5,2)||'/'||SUBSTR(CAST(GHH.HHJDAT AS VARCHAR(60)),1,4) AS HHJDAT,
MAX(ITH.TREF) AS IDESC,SUM(GLH.LHDRAT) AS LHDRAT,SUM(GLH.LHCRAT) AS LHCRAT,SUM(LHDRAM) AS LHDRAM,SUM(LHCRAM) AS LHCRAM
FROM V820LIVEFG.GLH AS GLH
LEFT OUTER JOIN V820LIVEFG.GHH AS GHH ON GLH.LHLDGR=GHH.HHLDGR
AND GLH.LHBOOK=GHH.HHBOOK
AND GLH.LHYEAR=GHH.HHYEAR
AND GLH.LHPERD=GHH.HHPERD
AND GLH.LHJNEN=GHH.HHJNEN
AND GHH.HHID='HH'
LEFT OUTER JOIN V820LIVEFG.GCR AS GCR ON GLH.LHIAN=GCR.CRIAN
AND GCR.CRID='CR'
LEFT OUTER JOIN V820LIVEFG.GSV AS GSV ON GCR.CRSG03=GSV.SVSGVL
AND GSV.SVID='SV'
AND GSV.SVSGMN='ACCOUNT'
LEFT OUTER JOIN V820LIVEFG.GSV AS GSV4 ON IFNULL(GCR.CRSG04,'')=GSV4.SVSGVL
AND GSV4.SVID='SV'
AND trim(GSV4.SVSGMN)='SUBACC1'
LEFT OUTER JOIN V820LIVEFG.GSV AS GSV5 ON IFNULL(GCR.CRSG05,'')=GSV5.SVSGVL
AND GSV5.SVID='SV'
AND trim(GSV5.SVSGMN)='SUBACC2'
LEFT OUTER JOIN V820LIVEFG.ZCC AS ZCC ON ZCC.CCCODE = GCR.CRSG05
AND ZCC.CCTABL = 'SUBACC2'
LEFT OUTER JOIN V820LIVEFG.GSV AS GSV6 ON IFNULL(GCR.CRSG06,'')=GSV6.SVSGVL
AND GSV6.SVID='SV'
AND trim(GSV6.SVSGMN)='SUBACC3'
LEFT OUTER JOIN V820LIVEFG.GSV AS GSV7 ON IFNULL(GCR.CRSG07,'')=GSV7.SVSGVL
AND GSV7.SVID='SV'
AND trim(GSV7.SVSGMN)='SUBACC4'
LEFT OUTER JOIN V820LIVEFG.APH AS APH ON GLH.LHJRF1=APH.APINV
AND CAST(GLH.LHJRF2 AS NUMERIC(19))=APVNDR
LEFT OUTER JOIN V820LIVEFG.AVM AS AVM ON APH.APVNDR=AVM.VENDOR
LEFT OUTER JOIN
(
SELECT DISTINCT TRIM(SUBSTR(TCOM,5,32)) AS TCOM,TVEND,CAST(TREF AS VARCHAR(60)) AS TREF
FROM V820LIVEFG.ITH AS ITH
WHERE ITH.TTYPE='C'
AND ITH.TID='TH'
) AS ITH
ON GLH.LHJRF1=ITH.TCOM
AND CAST(GLH.LHJRF2 AS NUMERIC(19,6))=ITH.TVEND
WHERE GLH.LHID='LH'
AND GLH.LHREAS IN ('APINV','APINR','APINA')
AND APH.APSTAT<>'V'
AND (GLH.LHJNEN BETWEEN ?LHJNEN_FROM? AND ?LHJNEN_TO?)
AND (CAST(GHH.HHJDAT AS VARCHAR(60)) BETWEEN ?HHJDAT_FROM? AND ?HHJDAT_TO?)
AND IFNULL(AVM.VENDOR,0) BETWEEN ?SVEND? AND ?EVEND?
GROUP BY GLH.LHJNEN,GLH.LHJRF1,GHH.HHTRNO,AVM.VENDOR,AVM.VNDNAM,trim(GCR.CRSG03)
||CASE WHEN TRIM(IFNULL(GCR.CRSG04,''))<>'' THEN '-'||TRIM(GCR.CRSG04) ELSE '' END
||CASE WHEN TRIM(IFNULL(GCR.CRSG05,''))<>'' THEN '-'||TRIM(GCR.CRSG05) ELSE '' END
||CASE WHEN TRIM(IFNULL(GCR.CRSG06,''))<>'' THEN '-'||TRIM(GCR.CRSG06) ELSE '' END
||CASE WHEN TRIM(IFNULL(GCR.CRSG07,''))<>'' THEN '-'||TRIM(GCR.CRSG07) ELSE '' END ,
trim(GSV.SVLDES )
||CASE WHEN trim(IFNULL(gsv4.svldes,'')) <> '' THEN '-' || TRIM(GSV4.SVLDES) ELSE '' END
||CASE WHEN trim(IFNULL((case when zcc.cccode <> '' then zcc.ccnot1 else gsv5.svldes END),'')) <> ''THEN '-' || TRIM(case when zcc.cccode <> '' then zcc.ccnot1 else gsv5.svldes END ) ELSE ''END
||CASE WHEN trim(IFNULL(gsv6.svldes,'')) <> '' THEN '-'||TRIM(GSV6.SVLDES) ELSE '' END
||CASE WHEN trim(IFNULL(gsv7.svldes,'')) <> '' THEN '-'||TRIM(GSV7.SVLDES) ELSE '' END,
GHH.HHCURR,
SUBSTR(CAST(GHH.HHJDAT AS VARCHAR(60)),7,2)||'/'||SUBSTR(CAST(GHH.HHJDAT AS VARCHAR(60)),5,2)||'/'||SUBSTR(CAST(GHH.HHJDAT AS VARCHAR(60)),1,4)
),
GLH_TOTAL_1 AS
(
SELECT LHJNEN,SUM(LHDRAT) AS LHDRAT,SUM(LHCRAT) AS LHCRAT
FROM GLH_ITEM
GROUP BY LHJNEN
),
GLH_TOTAL_2 AS
(
SELECT SUM(LHDRAT) AS LHDRAT,SUM(LHCRAT) AS LHCRAT
FROM GLH_ITEM
),
GLH_TOTAL_3 AS
(
SELECT HHCURR,CRSG03,SUM(IFNULL(LHDRAT,0)-IFNULL(LHCRAT,0)) AS LHAT,SUM(IFNULL(LHDRAM,0)-IFNULL(LHCRAM,0)) AS LHAM
FROM GLH_ITEM
GROUP BY HHCURR,CRSG03
),
GLH_TOTAL_4 AS
(
SELECT CRSG03,HHCURR,SUM(IFNULL(LHDRAT,0)-IFNULL(LHCRAT,0)) AS LHAT,SUM(IFNULL(LHDRAM,0)-IFNULL(LHCRAM,0)) AS LHAM
FROM GLH_ITEM
GROUP BY CRSG03,HHCURR
),
GLH_TOTAL_5 AS
(
SELECT CRSG03,SUM(IFNULL(LHDRAM,0)-IFNULL(LHCRAM,0)) AS LHAM
FROM GLH_ITEM
GROUP BY CRSG03
),
ZCC AS
(
SELECT CCCODE,CCDESC
FROM V820LIVEFG.ZCC AS ZCC
WHERE CCTABL='FINRPT'
)
SELECT GLH_TYPE_3,CASE WHEN GLH_TYPE_2=1 AND GLH_TYPE_1=2 THEN NULL ELSE LHJNEN_2 END AS LHJNEN,LHJNEN_2 AS LHJNEN_3,HHJDAT,LHJRF1,HHTRNO,VENDOR,VNDNAM, /*?SVEND?||' - '||?EVEND? AS VEND_FROM_TO,*/
CASE WHEN GLH_TYPE_2=6 AND GLH_TYPE_1=2 THEN (SELECT CCDESC FROM ZCC WHERE CCCODE='42') ELSE CRSG03_2 END AS CRSG03,
CRDESC,HHCURR,IDESC,LHDRAT,LHCRAT,SUBSTR(?HHJDAT_FROM?,7,2)||'/'||SUBSTR(?HHJDAT_FROM?,5,2)||'/'||SUBSTR(?HHJDAT_FROM?,1,4)||' - '||
SUBSTR(?HHJDAT_TO?,7,2)||'/'||SUBSTR(?HHJDAT_TO?,5,2)||'/'||SUBSTR(?HHJDAT_TO?,1,4) AS HHJDAT_FROM_TO,?LHJNEN_FROM?||' - '||?LHJNEN_TO? AS LHJNEN_FROM_TO
FROM
(
SELECT 1 AS GLH_TYPE_3,1 AS GLH_TYPE_2,1 AS GLH_TYPE_1,LHJNEN AS LHJNEN_2,HHJDAT,LHJRF1,HHTRNO,VENDOR,VNDNAM,CRSG03 AS CRSG03_2,CRDESC,HHCURR,IDESC,LHDRAT,LHCRAT
FROM GLH_ITEM
UNION ALL
SELECT 1 AS GLH_TYPE_3,1 AS GLH_TYPE_2,2 AS GLH_TYPE_1,LHJNEN AS LHJNEN_2,'' AS HHJDAT,'' AS LHJRF1,CAST(NULL AS NUMERIC(19,6)) AS HHTRNO,CAST(NULL AS NUMERIC(19,6)) AS VENDOR,
'' AS VNDNAM,'' AS CRSG03_2,(SELECT CCDESC FROM ZCC WHERE CCCODE='49') AS CRDESC,'' AS HHCURR,'' AS IDESC,LHDRAT,LHCRAT
FROM GLH_TOTAL_1
UNION ALL
SELECT 2 AS GLH_TYPE_3,2 AS GLH_TYPE_2,0 AS GLH_TYPE_1,'' AS LHJNEN_2,'' AS HHJDAT,'' AS LHJRF1,CAST(NULL AS NUMERIC(19,6)) AS HHTRNO,CAST(NULL AS NUMERIC(19,6)) AS VENDOR,
'' AS VNDNAM,'' AS CRSG03_2,(SELECT CCDESC FROM ZCC WHERE CCCODE='50') AS CRDESC,'' AS HHCURR,'' AS IDESC,LHDRAT,LHCRAT
FROM GLH_TOTAL_2
UNION ALL
SELECT 3 AS GLH_TYPE_3,3 AS GLH_TYPE_2,0 AS GLH_TYPE_1,'' AS LHJNEN_2,'' AS HHJDAT,'' AS LHJRF1,CAST(NULL AS NUMERIC(19,6)) AS HHTRNO,CAST(NULL AS NUMERIC(19,6)) AS VENDOR,'' AS VNDNAM,
(SELECT CCDESC FROM ZCC WHERE CCCODE='52') AS CRSG03_2,(SELECT CCDESC FROM ZCC WHERE CCCODE='53') AS CRDESC,'' AS HHCURR,'' AS IDESC,CAST(NULL AS NUMERIC(19,6)) AS LHDRAT,CAST(NULL AS NUMERIC(19,6)) AS LHCRAT
FROM SYSIBM.SYSDUMMY1
UNION ALL
SELECT 3 AS GLH_TYPE_3,4 AS GLH_TYPE_2,0 AS GLH_TYPE_1,'' AS LHJNEN_2,'' AS HHJDAT,'' AS LHJRF1,CAST(NULL AS NUMERIC(19,6)) AS HHTRNO,CAST(NULL AS NUMERIC(19,6)) AS VENDOR,'' AS VNDNAM,HHCURR AS CRSG03_2,
CRSG03 AS CRDESC,'' AS HHCURR,'' AS IDESC,LHAT AS LHDRAT,LHAM AS LHCRAT
FROM GLH_TOTAL_3
) AS GLH
ORDER BY GLH_TYPE_3 ASC,GLH_TYPE_2 ASC,CASE WHEN GLH_TYPE_2=1 THEN LHJNEN_2 WHEN GLH_TYPE_2=6 THEN CRSG03_2 ELSE NULL END ASC,GLH_TYPE_1 ASC,LHJNEN_2 ASC,LHCRAT ASC
