GL
来源：互联网 发布：集体智慧编程 pdf 编辑：程序博客网 时间：2024/04/29 09:10
GL包含所有科目的余额，可用于资产负债表，利润表，所有者权益表等直接按科目取数的表。
损益科目用于利润表不需要分借贷。
加载目标是DATI_SADI_LORDI表。
SAP:
SELECT A.*,B.WAERS ACCWAERS FROM (SELECT RYEAR||RMONTH YEARMONTH,RYEAR, RMONTH,RBUKRS,RACCT,RBUSA,RFAREA,RTCUR, DMBTR, WRBTR,RCLNT,DRCRK, SH_DMBTR, SH_WRBTR ,RCNTR,CATEG,PRCTR FROM(SELECT RYEAR,RMONTH,RBUKRS,RACCT,RBUSA,RFAREA,RTCUR,SUM( DMBTR) DMBTR,SUM( WRBTR) WRBTR,RCLNT,DRCRK, SUM(SH_DMBTR) SH_DMBTR,SUM(SH_WRBTR) SH_WRBTR,RCNTR,CATEG,PRCTR FROM (select RYEAR, RMONTH,RBUKRS,RACCT,RBUSA,RFAREA,RTCUR, DMBTR,SH_DMBTR ,WRBTR, SH_WRBTR,RCLNT,DRCRK,RCNTR,CATEG,PRCTR from(SELECT RYEAR,'01' RMONTH,RBUKRS,RACCT,RBUSA,RFAREA,RTCUR,(HSLVT+HSL01) DMBTR, (HSL01) SH_DMBTR ,(TSLVT+TSL01 ) WRBTR, (TSL01) SH_WRBTR,RCLNT,DRCRK,RCNTR,'$AMOUNT' CATEG,substr(prctr,4,7) PRCTRFROM ODS.ODSS600_FAGLFLEXT@FMSLKwhere RBUKRS IN ({IN-ENTITY}) )WHERE RYEAR||RMONTH IN ({IN-YEARMONTH}) UNION ALLselect RYEAR, RMONTH,RBUKRS,RACCT,RBUSA,RFAREA,RTCUR, DMBTR,SH_DMBTR ,WRBTR, SH_WRBTR,RCLNT,DRCRK,RCNTR,CATEG,PRCTR from(SELECT RYEAR,'02' RMONTH,RBUKRS,RACCT,RBUSA,RFAREA,RTCUR,(HSLVT+HSL01+HSL02) DMBTR, (HSL01+HSL02) SH_DMBTR, (TSLVT+TSL01+TSL02 ) WRBTR, (TSL01+TSL02 ) SH_WRBTR,RCLNT,DRCRK,RCNTR,'$AMOUNT' CATEG,substr(prctr,4,7) PRCTRFROM ODS.ODSS600_FAGLFLEXT@FMSLK where RBUKRS IN ({IN-ENTITY}) )WHERE RYEAR||RMONTH IN ({IN-YEARMONTH}) UNION ALLselect RYEAR, RMONTH,RBUKRS,RACCT,RBUSA,RFAREA,RTCUR, DMBTR,SH_DMBTR ,WRBTR, SH_WRBTR,RCLNT,DRCRK,RCNTR,CATEG,PRCTR from(SELECT RYEAR,'03' RMONTH,RBUKRS,RACCT,RBUSA,RFAREA,RTCUR,(HSLVT+HSL01+HSL02+HSL03) DMBTR, (HSL01+HSL02+HSL03) SH_DMBTR ,(TSLVT+TSL01+TSL02+TSL03 ) WRBTR, (TSL01+TSL02+TSL03 ) SH_WRBTR,RCLNT,DRCRK,RCNTR,'$AMOUNT' CATEG,substr(prctr,4,7) PRCTRFROM ODS.ODSS600_FAGLFLEXT@FMSLK where RBUKRS IN ({IN-ENTITY}) )WHERE RYEAR||RMONTH IN ({IN-YEARMONTH}) UNION ALLselect RYEAR, RMONTH,RBUKRS,RACCT,RBUSA,RFAREA,RTCUR, DMBTR,SH_DMBTR ,WRBTR, SH_WRBTR,RCLNT,DRCRK,RCNTR,CATEG,PRCTR from(SELECT RYEAR,'04' RMONTH,RBUKRS,RACCT,RBUSA,RFAREA,RTCUR,(HSLVT+HSL01+HSL02+HSL03+HSL04) DMBTR ,(HSL01+HSL02+HSL03+HSL04) SH_DMBTR , (TSLVT+TSL01+TSL02+TSL03 +TSL04) WRBTR, (TSL01+TSL02+TSL03 +TSL04) SH_WRBTR,RCLNT,DRCRK,RCNTR,'$AMOUNT' CATEG,substr(prctr,4,7) PRCTRFROM ODS.ODSS600_FAGLFLEXT@FMSLK where RBUKRS IN ({IN-ENTITY}) )WHERE RYEAR||RMONTH IN ({IN-YEARMONTH}) UNION ALLselect RYEAR, RMONTH,RBUKRS,RACCT,RBUSA,RFAREA,RTCUR, DMBTR,SH_DMBTR ,WRBTR, SH_WRBTR,RCLNT,DRCRK,RCNTR,CATEG,PRCTR from(SELECT RYEAR,'05' RMONTH,RBUKRS,RACCT,RBUSA,RFAREA,RTCUR,(HSLVT+HSL01+HSL02+HSL03+HSL04+HSL05) DMBTR, (HSL01+HSL02+HSL03+HSL04+HSL05) SH_DMBTR , (TSLVT+TSL01+TSL02+TSL03 +TSL04+TSL05) WRBTR, (TSL01+TSL02+TSL03 +TSL04+TSL05) SH_WRBTR,RCLNT,DRCRK,RCNTR,'$AMOUNT' CATEG,substr(prctr,4,7) PRCTRFROM ODS.ODSS600_FAGLFLEXT@FMSLK where RBUKRS IN ({IN-ENTITY}) )WHERE RYEAR||RMONTH IN ({IN-YEARMONTH}) UNION ALLselect RYEAR, RMONTH,RBUKRS,RACCT,RBUSA,RFAREA,RTCUR, DMBTR,SH_DMBTR ,WRBTR, SH_WRBTR,RCLNT,DRCRK,RCNTR,CATEG,PRCTR from(SELECT RYEAR,'06' RMONTH,RBUKRS,RACCT,RBUSA,RFAREA,RTCUR,(HSLVT+HSL01+HSL02+HSL03+HSL04+HSL05+HSL06) DMBTR, (HSL01+HSL02+HSL03+HSL04+HSL05+HSL06) SH_DMBTR , (TSLVT+TSL01+TSL02+TSL03 +TSL04+TSL05+TSL06) WRBTR, (TSL01+TSL02+TSL03 +TSL04+TSL05+TSL06) SH_WRBTR,RCLNT,DRCRK,RCNTR,'$AMOUNT' CATEG,substr(prctr,4,7) PRCTRFROM ODS.ODSS600_FAGLFLEXT@FMSLK where RBUKRS IN ({IN-ENTITY}) )WHERE RYEAR||RMONTH IN ({IN-YEARMONTH}) UNION ALLselect RYEAR, RMONTH,RBUKRS,RACCT,RBUSA,RFAREA,RTCUR, DMBTR,SH_DMBTR ,WRBTR, SH_WRBTR,RCLNT,DRCRK,RCNTR,CATEG,PRCTR from(SELECT RYEAR,'07' RMONTH,RBUKRS,RACCT,RBUSA,RFAREA,RTCUR,(HSLVT+HSL01+HSL02+HSL03+HSL04+HSL05+HSL06+HSL07) DMBTR, (HSL01+HSL02+HSL03+HSL04+HSL05+HSL06+HSL07) SH_DMBTR , (TSLVT+TSL01+TSL02+TSL03 +TSL04+TSL05+TSL06+TSL07) WRBTR, (TSL01+TSL02+TSL03 +TSL04+TSL05+TSL06+TSL07) SH_WRBTR,RCLNT,DRCRK,RCNTR,'$AMOUNT' CATEG,substr(prctr,4,7) PRCTRFROM ODS.ODSS600_FAGLFLEXT@FMSLK where RBUKRS IN ({IN-ENTITY}) )WHERE RYEAR||RMONTH IN ({IN-YEARMONTH}) UNION ALLselect RYEAR, RMONTH,RBUKRS,RACCT,RBUSA,RFAREA,RTCUR, DMBTR,SH_DMBTR ,WRBTR, SH_WRBTR,RCLNT,DRCRK,RCNTR,CATEG,PRCTR from(SELECT RYEAR,'08' RMONTH,RBUKRS,RACCT,RBUSA,RFAREA,RTCUR,(HSLVT+HSL01+HSL02+HSL03+HSL04+HSL05+HSL06+HSL07+HSL08) DMBTR, (HSL01+HSL02+HSL03+HSL04+HSL05+HSL06+HSL07+HSL08) SH_DMBTR , (TSLVT+TSL01+TSL02+TSL03 +TSL04+TSL05+TSL06+TSL07+TSL08) WRBTR, (TSL01+TSL02+TSL03 +TSL04+TSL05+TSL06+TSL07+TSL08) SH_WRBTR,RCLNT,DRCRK,RCNTR,'$AMOUNT' CATEG,substr(prctr,4,7) PRCTRFROM ODS.ODSS600_FAGLFLEXT@FMSLK where RBUKRS IN ({IN-ENTITY}) )WHERE RYEAR||RMONTH IN ({IN-YEARMONTH}) UNION ALLselect RYEAR, RMONTH,RBUKRS,RACCT,RBUSA,RFAREA,RTCUR, DMBTR,SH_DMBTR ,WRBTR, SH_WRBTR,RCLNT,DRCRK,RCNTR,CATEG,PRCTR from(SELECT RYEAR,'09' RMONTH,RBUKRS,RACCT,RBUSA,RFAREA,RTCUR,(HSLVT+HSL01+HSL02+HSL03+HSL04+HSL05+HSL06+HSL07+HSL08+HSL09) DMBTR, (HSL01+HSL02+HSL03+HSL04+HSL05+HSL06+HSL07+HSL08+HSL09) SH_DMBTR , (TSLVT+TSL01+TSL02+TSL03 +TSL04+TSL05+TSL06+TSL07+TSL08+TSL09) WRBTR, (TSL01+TSL02+TSL03 +TSL04+TSL05+TSL06+TSL07+TSL08+TSL09) SH_WRBTR,RCLNT,DRCRK,RCNTR,'$AMOUNT' CATEG,substr(prctr,4,7) PRCTRFROM ODS.ODSS600_FAGLFLEXT@FMSLK where RBUKRS IN ({IN-ENTITY}) )WHERE RYEAR||RMONTH IN ({IN-YEARMONTH}) UNION ALLselect RYEAR, RMONTH,RBUKRS,RACCT,RBUSA,RFAREA,RTCUR, DMBTR,SH_DMBTR ,WRBTR, SH_WRBTR,RCLNT,DRCRK,RCNTR,CATEG,PRCTR from(SELECT RYEAR,'10' RMONTH,RBUKRS,RACCT,RBUSA,RFAREA,RTCUR,(HSLVT+HSL01+HSL02+HSL03+HSL04+HSL05+HSL06+HSL07+HSL08+HSL09+HSL10) DMBTR, (HSL01+HSL02+HSL03+HSL04+HSL05+HSL06+HSL07+HSL08+HSL09+HSL10) SH_DMBTR , (TSLVT+TSL01+TSL02+TSL03 +TSL04+TSL05+TSL06+TSL07+TSL08+TSL09+TSL10) WRBTR, (TSL01+TSL02+TSL03 +TSL04+TSL05+TSL06+TSL07+TSL08+TSL09+TSL10) SH_WRBTR,RCLNT,DRCRK,RCNTR,'$AMOUNT' CATEG,substr(prctr,4,7) PRCTRFROM ODS.ODSS600_FAGLFLEXT@FMSLK where RBUKRS IN ({IN-ENTITY}) )WHERE RYEAR||RMONTH IN ({IN-YEARMONTH}) UNION ALLselect RYEAR, RMONTH,RBUKRS,RACCT,RBUSA,RFAREA,RTCUR, DMBTR,SH_DMBTR ,WRBTR, SH_WRBTR,RCLNT,DRCRK,RCNTR,CATEG,PRCTR from(SELECT RYEAR,'11' RMONTH,RBUKRS,RACCT,RBUSA,RFAREA,RTCUR,(HSLVT+HSL01+HSL02+HSL03+HSL04+HSL05+HSL06+HSL07+HSL08+HSL09+HSL10+HSL11) DMBTR, (HSL01+HSL02+HSL03+HSL04+HSL05+HSL06+HSL07+HSL08+HSL09+HSL10+HSL11) SH_DMBTR , (TSLVT+TSL01+TSL02+TSL03 +TSL04+TSL05+TSL06+TSL07+TSL08+TSL09+TSL10+TSL11) WRBTR, (TSL01+TSL02+TSL03 +TSL04+TSL05+TSL06+TSL07+TSL08+TSL09+TSL10+TSL11) SH_WRBTR,RCLNT,DRCRK,RCNTR,'$AMOUNT' CATEG,substr(prctr,4,7) PRCTRFROM ODS.ODSS600_FAGLFLEXT@FMSLK where RBUKRS IN ({IN-ENTITY}) )WHERE RYEAR||RMONTH IN ({IN-YEARMONTH}) UNION ALLselect RYEAR, RMONTH,RBUKRS,RACCT,RBUSA,RFAREA,RTCUR, DMBTR,SH_DMBTR ,WRBTR, SH_WRBTR,RCLNT,DRCRK,RCNTR,CATEG,PRCTR from(SELECT RYEAR,'12' RMONTH,RBUKRS,RACCT,RBUSA,RFAREA,RTCUR,(HSLVT+HSL01+HSL02+HSL03+HSL04+HSL05+HSL06+HSL07+HSL08+HSL09+HSL10+HSL11+HSL12) DMBTR, (HSL01+HSL02+HSL03+HSL04+HSL05+HSL06+HSL07+HSL08+HSL09+HSL10+HSL11+HSL12) SH_DMBTR , (TSLVT+TSL01+TSL02+TSL03 +TSL04+TSL05+TSL06+TSL07+TSL08+TSL09+TSL10+TSL11+TSL12) WRBTR, (TSL01+TSL02+TSL03 +TSL04+TSL05+TSL06+TSL07+TSL08+TSL09+TSL10+TSL11+TSL12) SH_WRBTR,RCLNT,DRCRK,RCNTR,'$AMOUNT' CATEG,substr(prctr,4,7) PRCTRFROM ODS.ODSS600_FAGLFLEXT@FMSLK where RBUKRS IN ({IN-ENTITY}) )WHERE RYEAR||RMONTH IN ({IN-YEARMONTH}) )GROUP BY RYEAR, RMONTH,RBUKRS,RACCT,RBUSA,RFAREA,RTCUR,RCLNT,DRCRK,RCNTR,CATEG,PRCTR)WHERE DMBTR<>0 OR WRBTR<>0) ALEFT JOIN ODS.ODSS600_SKB1@FMSLK B ON A.RBUKRS = B.BUKRS AND A.RACCT = B.SAKNR特殊期间：SELECT A.*,B.WAERS ACCWAERS FROM (SELECT RYEAR||RMONTH YEARMONTH,RYEAR, (CASE WHEN SUBSTR({IN-YEARMONTH},5,2) > '12' THEN '12' ELSE SUBSTR({IN-YEARMONTH},5,2) END) RMONTH,RBUKRS,RACCT,RBUSA,RFAREA,RTCUR, DMBTR, WRBTR,RCLNT,DRCRK, SH_DMBTR, SH_WRBTR ,RCNTR,CATEG FROM(SELECT RYEAR,RMONTH,RBUKRS,RACCT,RBUSA,RFAREA,RTCUR,SUM( DMBTR) DMBTR,SUM( WRBTR) WRBTR,RCLNT,DRCRK, SUM(SH_DMBTR) SH_DMBTR,SUM(SH_WRBTR) SH_WRBTR,RCNTR,CATEG FROM (SELECT RYEAR,'16' RMONTH,RBUKRS,RACCT,RBUSA,RFAREA,RTCUR,(HSL13+HSL14+HSL15+HSL16) DMBTR, (HSL13+HSL14+HSL15+HSL16) SH_DMBTR , (TSL13+TSL14+TSL15+TSL16) WRBTR, (TSL13+TSL14+TSL15+TSL16) SH_WRBTR,RCLNT,DRCRK,RCNTR,'1SPA' CATEGFROM ODS.ODSS600_FAGLFLEXT@FMSLK WHERE RBUKRS IN ({IN-ENTITY}) )WHERE RYEAR||RMONTH ={IN-YEARMONTH}GROUP BY RYEAR, RMONTH,RBUKRS,RACCT,RBUSA,RFAREA,RTCUR,RCLNT,DRCRK,RCNTR,CATEG)WHERE DMBTR<>0 OR WRBTR<>0) ALEFT JOIN ODS.ODSS600_SKB1@FMSLK B ON A.RBUKRS = B.BUKRS AND A.RACCT = B.SAKNRK3:
with cc as( select fyear,lpad(cast(fperiod AS VARCHAR(2)),2,'0') fperiod,subjcode,subjname,subjlev,companycode,companyname, beginbalance,endbalance,localdebitamount, localcreditamount from ODS.ODSTTKD_v_MR_BALANCE@FMSLK where fcurrencyid = 0 and fdetailid = 0),S1 AS ( SELECT fyear,fperiod,subjcode,subjname,subjlev,companycode,companyname, SUM(beginbalance) beginbalance,SUM(endbalance) endbalance,SUM(localdebitamount) localdebitamount,SUM(localcreditamount) localcreditamount FROM( SELECT fyear,'01' fperiod,subjcode,subjname,subjlev,companycode,companyname, CASE FPERIOD WHEN '01' THEN beginbalance ELSE 0 END beginbalance, CASE FPERIOD WHEN '01' THEN endbalance ELSE 0 END endbalance, SUM(localdebitamount) localdebitamount,SUM(localcreditamount) localcreditamount FROM( select fyear,fperiod,subjcode,subjname,subjlev,companycode,companyname, sum(beginbalance) beginbalance,sum(endbalance) endbalance,sum(localdebitamount) localdebitamount,sum(localcreditamount) localcreditamount from cc WHERE FPERIOD <= '01' group by fyear,fperiod,subjcode,subjname,subjlev,companycode,companyname ORDER BY FYEAR,SUBJCODE ) group by fyear,fperiod,subjcode,subjname,subjlev,companycode,companyname,beginbalance,endbalance ) group by fyear,fperiod,subjcode,subjname,subjlev,companycode,companyname ORDER BY FYEAR,SUBJCODE),S2 AS ( SELECT fyear,fperiod,subjcode,subjname,subjlev,companycode,companyname, SUM(beginbalance) beginbalance,SUM(endbalance) endbalance,SUM(localdebitamount) localdebitamount,SUM(localcreditamount) localcreditamount FROM( SELECT fyear,'02' fperiod,subjcode,subjname,subjlev,companycode,companyname, CASE FPERIOD WHEN '02' THEN beginbalance ELSE 0 END beginbalance, CASE FPERIOD WHEN '02' THEN endbalance ELSE 0 END endbalance, SUM(localdebitamount) localdebitamount,SUM(localcreditamount) localcreditamount FROM( select fyear,fperiod,subjcode,subjname,subjlev,companycode,companyname, sum(beginbalance) beginbalance,sum(endbalance) endbalance,sum(localdebitamount) localdebitamount,sum(localcreditamount) localcreditamount from cc WHERE FPERIOD <= '02' group by fyear,fperiod,subjcode,subjname,subjlev,companycode,companyname ORDER BY FYEAR,SUBJCODE ) group by fyear,fperiod,subjcode,subjname,subjlev,companycode,companyname,beginbalance,endbalance ) group by fyear,fperiod,subjcode,subjname,subjlev,companycode,companyname ORDER BY FYEAR,SUBJCODE),S3 AS ( SELECT fyear,fperiod,subjcode,subjname,subjlev,companycode,companyname, SUM(beginbalance) beginbalance,SUM(endbalance) endbalance,SUM(localdebitamount) localdebitamount,SUM(localcreditamount) localcreditamount FROM( SELECT fyear,'03' fperiod,subjcode,subjname,subjlev,companycode,companyname, CASE FPERIOD WHEN '03' THEN beginbalance ELSE 0 END beginbalance, CASE FPERIOD WHEN '03' THEN endbalance ELSE 0 END endbalance, SUM(localdebitamount) localdebitamount,SUM(localcreditamount) localcreditamount FROM( select fyear,fperiod,subjcode,subjname,subjlev,companycode,companyname, sum(beginbalance) beginbalance,sum(endbalance) endbalance,sum(localdebitamount) localdebitamount,sum(localcreditamount) localcreditamount from cc WHERE FPERIOD <= '03' group by fyear,fperiod,subjcode,subjname,subjlev,companycode,companyname ORDER BY FYEAR,SUBJCODE ) group by fyear,fperiod,subjcode,subjname,subjlev,companycode,companyname,beginbalance,endbalance ) group by fyear,fperiod,subjcode,subjname,subjlev,companycode,companyname ORDER BY FYEAR,SUBJCODE),S4 AS ( SELECT fyear,fperiod,subjcode,subjname,subjlev,companycode,companyname, SUM(beginbalance) beginbalance,SUM(endbalance) endbalance,SUM(localdebitamount) localdebitamount,SUM(localcreditamount) localcreditamount FROM( SELECT fyear,'04' fperiod,subjcode,subjname,subjlev,companycode,companyname, CASE FPERIOD WHEN '04' THEN beginbalance ELSE 0 END beginbalance, CASE FPERIOD WHEN '04' THEN endbalance ELSE 0 END endbalance, SUM(localdebitamount) localdebitamount,SUM(localcreditamount) localcreditamount FROM( select fyear,fperiod,subjcode,subjname,subjlev,companycode,companyname, sum(beginbalance) beginbalance,sum(endbalance) endbalance,sum(localdebitamount) localdebitamount,sum(localcreditamount) localcreditamount from cc WHERE FPERIOD <= '04' group by fyear,fperiod,subjcode,subjname,subjlev,companycode,companyname ORDER BY FYEAR,SUBJCODE ) group by fyear,fperiod,subjcode,subjname,subjlev,companycode,companyname,beginbalance,endbalance ) group by fyear,fperiod,subjcode,subjname,subjlev,companycode,companyname ORDER BY FYEAR,SUBJCODE),S5 AS ( SELECT fyear,fperiod,subjcode,subjname,subjlev,companycode,companyname, SUM(beginbalance) beginbalance,SUM(endbalance) endbalance,SUM(localdebitamount) localdebitamount,SUM(localcreditamount) localcreditamount FROM( SELECT fyear,'05' fperiod,subjcode,subjname,subjlev,companycode,companyname, CASE FPERIOD WHEN '05' THEN beginbalance ELSE 0 END beginbalance, CASE FPERIOD WHEN '05' THEN endbalance ELSE 0 END endbalance, SUM(localdebitamount) localdebitamount,SUM(localcreditamount) localcreditamount FROM( select fyear,fperiod,subjcode,subjname,subjlev,companycode,companyname, sum(beginbalance) beginbalance,sum(endbalance) endbalance,sum(localdebitamount) localdebitamount,sum(localcreditamount) localcreditamount from cc WHERE FPERIOD <= '05' group by fyear,fperiod,subjcode,subjname,subjlev,companycode,companyname ORDER BY FYEAR,SUBJCODE ) group by fyear,fperiod,subjcode,subjname,subjlev,companycode,companyname,beginbalance,endbalance ) group by fyear,fperiod,subjcode,subjname,subjlev,companycode,companyname ORDER BY FYEAR,SUBJCODE),S6 AS ( SELECT fyear,fperiod,subjcode,subjname,subjlev,companycode,companyname, SUM(beginbalance) beginbalance,SUM(endbalance) endbalance,SUM(localdebitamount) localdebitamount,SUM(localcreditamount) localcreditamount FROM( SELECT fyear,'06' fperiod,subjcode,subjname,subjlev,companycode,companyname, CASE FPERIOD WHEN '06' THEN beginbalance ELSE 0 END beginbalance, CASE FPERIOD WHEN '06' THEN endbalance ELSE 0 END endbalance, SUM(localdebitamount) localdebitamount,SUM(localcreditamount) localcreditamount FROM( select fyear,fperiod,subjcode,subjname,subjlev,companycode,companyname, sum(beginbalance) beginbalance,sum(endbalance) endbalance,sum(localdebitamount) localdebitamount,sum(localcreditamount) localcreditamount from cc WHERE FPERIOD <= '06' group by fyear,fperiod,subjcode,subjname,subjlev,companycode,companyname ORDER BY FYEAR,SUBJCODE ) group by fyear,fperiod,subjcode,subjname,subjlev,companycode,companyname,beginbalance,endbalance ) group by fyear,fperiod,subjcode,subjname,subjlev,companycode,companyname ORDER BY FYEAR,SUBJCODE),S7 AS ( SELECT fyear,fperiod,subjcode,subjname,subjlev,companycode,companyname, SUM(beginbalance) beginbalance,SUM(endbalance) endbalance,SUM(localdebitamount) localdebitamount,SUM(localcreditamount) localcreditamount FROM( SELECT fyear,'07' fperiod,subjcode,subjname,subjlev,companycode,companyname, CASE FPERIOD WHEN '07' THEN beginbalance ELSE 0 END beginbalance, CASE FPERIOD WHEN '07' THEN endbalance ELSE 0 END endbalance, SUM(localdebitamount) localdebitamount,SUM(localcreditamount) localcreditamount FROM( select fyear,fperiod,subjcode,subjname,subjlev,companycode,companyname, sum(beginbalance) beginbalance,sum(endbalance) endbalance,sum(localdebitamount) localdebitamount,sum(localcreditamount) localcreditamount from cc WHERE FPERIOD <= '07' group by fyear,fperiod,subjcode,subjname,subjlev,companycode,companyname ORDER BY FYEAR,SUBJCODE ) group by fyear,fperiod,subjcode,subjname,subjlev,companycode,companyname,beginbalance,endbalance ) group by fyear,fperiod,subjcode,subjname,subjlev,companycode,companyname ORDER BY FYEAR,SUBJCODE),S8 AS ( SELECT fyear,fperiod,subjcode,subjname,subjlev,companycode,companyname, SUM(beginbalance) beginbalance,SUM(endbalance) endbalance,SUM(localdebitamount) localdebitamount,SUM(localcreditamount) localcreditamount FROM( SELECT fyear,'08' fperiod,subjcode,subjname,subjlev,companycode,companyname, CASE FPERIOD WHEN '08' THEN beginbalance ELSE 0 END beginbalance, CASE FPERIOD WHEN '08' THEN endbalance ELSE 0 END endbalance, SUM(localdebitamount) localdebitamount,SUM(localcreditamount) localcreditamount FROM( select fyear,fperiod,subjcode,subjname,subjlev,companycode,companyname, sum(beginbalance) beginbalance,sum(endbalance) endbalance,sum(localdebitamount) localdebitamount,sum(localcreditamount) localcreditamount from cc WHERE FPERIOD <= '08' group by fyear,fperiod,subjcode,subjname,subjlev,companycode,companyname ORDER BY FYEAR,SUBJCODE ) group by fyear,fperiod,subjcode,subjname,subjlev,companycode,companyname,beginbalance,endbalance ) group by fyear,fperiod,subjcode,subjname,subjlev,companycode,companyname ORDER BY FYEAR,SUBJCODE),S9 AS ( SELECT fyear,fperiod,subjcode,subjname,subjlev,companycode,companyname, SUM(beginbalance) beginbalance,SUM(endbalance) endbalance,SUM(localdebitamount) localdebitamount,SUM(localcreditamount) localcreditamount FROM( SELECT fyear,'09' fperiod,subjcode,subjname,subjlev,companycode,companyname, CASE FPERIOD WHEN '09' THEN beginbalance ELSE 0 END beginbalance, CASE FPERIOD WHEN '09' THEN endbalance ELSE 0 END endbalance, SUM(localdebitamount) localdebitamount,SUM(localcreditamount) localcreditamount FROM( select fyear,fperiod,subjcode,subjname,subjlev,companycode,companyname, sum(beginbalance) beginbalance,sum(endbalance) endbalance,sum(localdebitamount) localdebitamount,sum(localcreditamount) localcreditamount from cc WHERE FPERIOD <= '09' group by fyear,fperiod,subjcode,subjname,subjlev,companycode,companyname ORDER BY FYEAR,SUBJCODE ) group by fyear,fperiod,subjcode,subjname,subjlev,companycode,companyname,beginbalance,endbalance ) group by fyear,fperiod,subjcode,subjname,subjlev,companycode,companyname ORDER BY FYEAR,SUBJCODE),S10 AS ( SELECT fyear,fperiod,subjcode,subjname,subjlev,companycode,companyname, SUM(beginbalance) beginbalance,SUM(endbalance) endbalance,SUM(localdebitamount) localdebitamount,SUM(localcreditamount) localcreditamount FROM( SELECT fyear,'10' fperiod,subjcode,subjname,subjlev,companycode,companyname, CASE FPERIOD WHEN '10' THEN beginbalance ELSE 0 END beginbalance, CASE FPERIOD WHEN '10' THEN endbalance ELSE 0 END endbalance, SUM(localdebitamount) localdebitamount,SUM(localcreditamount) localcreditamount FROM( select fyear,fperiod,subjcode,subjname,subjlev,companycode,companyname, sum(beginbalance) beginbalance,sum(endbalance) endbalance,sum(localdebitamount) localdebitamount,sum(localcreditamount) localcreditamount from cc WHERE FPERIOD <= '10' group by fyear,fperiod,subjcode,subjname,subjlev,companycode,companyname ORDER BY FYEAR,SUBJCODE ) group by fyear,fperiod,subjcode,subjname,subjlev,companycode,companyname,beginbalance,endbalance ) group by fyear,fperiod,subjcode,subjname,subjlev,companycode,companyname ORDER BY FYEAR,SUBJCODE),S11 AS ( SELECT fyear,fperiod,subjcode,subjname,subjlev,companycode,companyname, SUM(beginbalance) beginbalance,SUM(endbalance) endbalance,SUM(localdebitamount) localdebitamount,SUM(localcreditamount) localcreditamount FROM( SELECT fyear,'11' fperiod,subjcode,subjname,subjlev,companycode,companyname, CASE FPERIOD WHEN '11' THEN beginbalance ELSE 0 END beginbalance, CASE FPERIOD WHEN '11' THEN endbalance ELSE 0 END endbalance, SUM(localdebitamount) localdebitamount,SUM(localcreditamount) localcreditamount FROM( select fyear,fperiod,subjcode,subjname,subjlev,companycode,companyname, sum(beginbalance) beginbalance,sum(endbalance) endbalance,sum(localdebitamount) localdebitamount,sum(localcreditamount) localcreditamount from cc WHERE FPERIOD <= '11' group by fyear,fperiod,subjcode,subjname,subjlev,companycode,companyname ORDER BY FYEAR,SUBJCODE ) group by fyear,fperiod,subjcode,subjname,subjlev,companycode,companyname,beginbalance,endbalance ) group by fyear,fperiod,subjcode,subjname,subjlev,companycode,companyname ORDER BY FYEAR,SUBJCODE),S12 AS ( SELECT fyear,fperiod,subjcode,subjname,subjlev,companycode,companyname, SUM(beginbalance) beginbalance,SUM(endbalance) endbalance,SUM(localdebitamount) localdebitamount,SUM(localcreditamount) localcreditamount FROM( SELECT fyear,'11' fperiod,subjcode,subjname,subjlev,companycode,companyname, CASE FPERIOD WHEN '12' THEN beginbalance ELSE 0 END beginbalance, CASE FPERIOD WHEN '12' THEN endbalance ELSE 0 END endbalance, SUM(localdebitamount) localdebitamount,SUM(localcreditamount) localcreditamount FROM( select fyear,fperiod,subjcode,subjname,subjlev,companycode,companyname, sum(beginbalance) beginbalance,sum(endbalance) endbalance,sum(localdebitamount) localdebitamount,sum(localcreditamount) localcreditamount from cc WHERE FPERIOD <= '12' group by fyear,fperiod,subjcode,subjname,subjlev,companycode,companyname ORDER BY FYEAR,SUBJCODE ) group by fyear,fperiod,subjcode,subjname,subjlev,companycode,companyname,beginbalance,endbalance ) group by fyear,fperiod,subjcode,subjname,subjlev,companycode,companyname ORDER BY FYEAR,SUBJCODE)select YEARMONTH,fyear,fperiod,subjcode,subjname,subjlev, companycode,companyname,beginbalance,endbalance, SHamount,SHMARK, CLIENT from (select YEARMONTH, fyear,fperiod,subjcode,subjname,subjlev, companycode,companyname,beginbalance,endbalance, SHamount,SHMARK, CLIENT from (select SUBJP,SUBJI, fyear||fperiod YEARMONTH, fyear,fperiod,subjcode,subjname,subjlev, companycode,companyname,beginbalance,endbalance, SHamount,SHMARK, CLIENT from ( SELECT companycode||fyear||lpad(cast(fperiod AS VARCHAR(2)),2,'0')||SUBSTR( subjcode,1,DECODE(subjlev,1,NULL,2,4,(subjlev -2)*3+4)) SUBJP, companycode||fyear||lpad(cast(fperiod AS VARCHAR(2)),2,'0')||subjcode SUBJI, fyear,fperiod,subjcode,subjname,subjlev,M.ELEDIM_OUTPUT1 companycode,companyname,beginbalance,endbalance, localcreditamount*-1 SHamount,'H' SHMARK,'K3' CLIENT FROM ( SELECT * FROM S1 UNION ALL SELECT * FROM S2 UNION ALL SELECT * FROM S3 UNION ALL SELECT * FROM S4 UNION ALL SELECT * FROM S5 UNION ALL SELECT * FROM S6 UNION ALL SELECT * FROM S7 UNION ALL SELECT * FROM S8 UNION ALL SELECT * FROM S9 UNION ALL SELECT * FROM S10 UNION ALL SELECT * FROM S11 UNION ALL SELECT * FROM S12 ) S LEFT JOIN MAP_REGOLA_TAB_ELEMENTO M ON M.COD_REGOLA_TAB = 'MAP_BUKRS_K3' AND M.COD_MAPPATURA = 'HI_DATA_GL' AND M.ELEDIM_INPUT2 = S.companycode ) WHERE CONNECT_BY_ISLEAF = 1 start with SUBJLEV = 1 connect by prior SUBJI = SUBJP )union all select YEARMONTH, fyear,fperiod,subjcode,subjname,subjlev, companycode,companyname,beginbalance,endbalance, SHamount,SHMARK, CLIENT from (select SUBJP,SUBJI, fyear||fperiod YEARMONTH, fyear,fperiod,subjcode,subjname,subjlev, companycode,companyname,beginbalance,endbalance, SHamount,SHMARK, CLIENTFROM (SELECT companycode||fyear||lpad(cast(fperiod AS VARCHAR(2)),2,'0')||SUBSTR( subjcode,1,DECODE(subjlev,1,NULL,2,4,(subjlev -2)*3+4)) SUBJP, companycode||fyear||lpad(cast(fperiod AS VARCHAR(2)),2,'0')||subjcode SUBJI, fyear,fperiod,subjcode,subjname,subjlev,M.ELEDIM_OUTPUT1 companycode,companyname,0 beginbalance,0 endbalance, localdebitamount SHamount,'S' SHMARK,'K3' CLIENTFROM ( SELECT * FROM S1 UNION ALL SELECT * FROM S2 UNION ALL SELECT * FROM S3 UNION ALL SELECT * FROM S4 UNION ALL SELECT * FROM S5 UNION ALL SELECT * FROM S6 UNION ALL SELECT * FROM S7 UNION ALL SELECT * FROM S8 UNION ALL SELECT * FROM S9 UNION ALL SELECT * FROM S10 UNION ALL SELECT * FROM S11 UNION ALL SELECT * FROM S12 ) S LEFT JOIN MAP_REGOLA_TAB_ELEMENTO M ON M.COD_REGOLA_TAB = 'MAP_BUKRS_K3' AND M.COD_MAPPATURA = 'HI_DATA_GL' AND M.ELEDIM_INPUT2 = S.companycode ) WHERE CONNECT_BY_ISLEAF = 1 start with SUBJLEV = 1 connect by prior SUBJI = SUBJP ))where companycode IN ({IN-ENTITY}) AND YEARMONTH IN ({IN-YEARMONTH})NC:
with cc as ( select * from ( select v.unitcode,v.unitname,v.yearV,v.subjcode,v.subjname,v.subjlev,v.ts,v.periodv,v.creditamount,v.debitamount,sum(v.creditamount* -1 + v.debitamount) balance from ODS.Odsreyy_V_Gl_Balance@FMSLK v where periodv <> '00' group by v.unitcode,v.unitname,v.yearV,v.subjcode,v.subjname,v.subjlev,v.ts,v.periodv,v.creditamount,v.debitamount ) union all select * from ( select v.unitcode,v.unitname,v.yearV,v.subjcode,v.subjname,v.subjlev,v.ts,v.periodv,0 creditamoun,0 debitamount,sum(v.creditamount* -1 + v.debitamount) balance from ODS.Odsreyy_V_Gl_Balance@FMSLK v where periodv = '00' group by unitcode,unitname,yearV,subjcode,subjname,subjlev,ts,periodv )),s1 as ( select unitcode ,unitname,subjcode,subjname,subjlev,yearv,periodv, sum(creditamount) creditamount , sum(debitamount) debitamount, sum(balance) balance from ( select unitcode ,unitname,subjcode,subjname,subjlev,yearv,'01' periodv,creditamount ,debitamount, balance from cc where periodv <= '01' ) group by unitcode ,unitname,subjcode,subjname,subjlev,yearv,periodv),s2 as ( select unitcode ,unitname,subjcode,subjname,subjlev,yearv,periodv, sum(creditamount) creditamount , sum(debitamount) debitamount, sum(balance) balance from ( select unitcode ,unitname,subjcode,subjname,subjlev,yearv,'02' periodv,creditamount ,debitamount, balance from cc where periodv <= '02' ) group by unitcode,unitname,yearV,subjcode,subjname,subjlev,periodv),s3 as ( select unitcode ,unitname,subjcode,subjname,subjlev,yearv,periodv, sum(creditamount) creditamount , sum(debitamount) debitamount, sum(balance) balance from ( select unitcode ,unitname,subjcode,subjname,subjlev,yearv,'03' periodv,creditamount ,debitamount, balance from cc where periodv <= '03' ) group by unitcode,unitname,yearV,subjcode,subjname,subjlev,periodv),s4 as ( select unitcode ,unitname,subjcode,subjname,subjlev,yearv,periodv, sum(creditamount) creditamount , sum(debitamount) debitamount, sum(balance) balance from ( select unitcode ,unitname,subjcode,subjname,subjlev,yearv,'04' periodv,creditamount ,debitamount, balance from cc where periodv <= '04' ) group by unitcode,unitname,yearV,subjcode,subjname,subjlev,periodv),s5 as ( select unitcode ,unitname,subjcode,subjname,subjlev,yearv,periodv, sum(creditamount) creditamount , sum(debitamount) debitamount, sum(balance) balance from ( select unitcode ,unitname,subjcode,subjname,subjlev,yearv,'05' periodv,creditamount ,debitamount, balance from cc where periodv <= '05' ) group by unitcode,unitname,yearV,subjcode,subjname,subjlev,periodv),s6 as ( select unitcode ,unitname,subjcode,subjname,subjlev,yearv,periodv, sum(creditamount) creditamount , sum(debitamount) debitamount, sum(balance) balance from ( select unitcode ,unitname,subjcode,subjname,subjlev,yearv,'06' periodv,creditamount ,debitamount, balance from cc where periodv <= '06' ) group by unitcode,unitname,yearV,subjcode,subjname,subjlev,periodv),s7 as ( select unitcode ,unitname,subjcode,subjname,subjlev,yearv,periodv, sum(creditamount) creditamount , sum(debitamount) debitamount, sum(balance) balance from ( select unitcode ,unitname,subjcode,subjname,subjlev,yearv,'07' periodv,creditamount ,debitamount, balance from cc where periodv <= '07' ) group by unitcode,unitname,yearV,subjcode,subjname,subjlev,periodv),s8 as ( select unitcode ,unitname,subjcode,subjname,subjlev,yearv,periodv, sum(creditamount) creditamount , sum(debitamount) debitamount, sum(balance) balance from ( select unitcode ,unitname,subjcode,subjname,subjlev,yearv,'08' periodv,creditamount ,debitamount, balance from cc where periodv <= '08' ) group by unitcode,unitname,yearV,subjcode,subjname,subjlev,periodv),s9 as ( select unitcode ,unitname,subjcode,subjname,subjlev,yearv,periodv, sum(creditamount) creditamount , sum(debitamount) debitamount, sum(balance) balance from ( select unitcode ,unitname,subjcode,subjname,subjlev,yearv,'09' periodv,creditamount ,debitamount, balance from cc where periodv <= '09' ) group by unitcode,unitname,yearV,subjcode,subjname,subjlev,periodv),s10 as ( select unitcode ,unitname,subjcode,subjname,subjlev,yearv,periodv, sum(creditamount) creditamount , sum(debitamount) debitamount, sum(balance) balance from ( select unitcode ,unitname,subjcode,subjname,subjlev,yearv,'10' periodv,creditamount ,debitamount, balance from cc where periodv <= '10' ) group by unitcode,unitname,yearV,subjcode,subjname,subjlev,periodv),s11 as ( select unitcode ,unitname,subjcode,subjname,subjlev,yearv,periodv, sum(creditamount) creditamount , sum(debitamount) debitamount, sum(balance) balance from ( select unitcode ,unitname,subjcode,subjname,subjlev,yearv,'11' periodv,creditamount ,debitamount, balance from cc where periodv <= '11' ) group by unitcode,unitname,yearV,subjcode,subjname,subjlev,periodv),s12 as ( select unitcode ,unitname,subjcode,subjname,subjlev,yearv,periodv, sum(creditamount) creditamount , sum(debitamount) debitamount, sum(balance) balance from ( select unitcode ,unitname,subjcode,subjname,subjlev,yearv,'12' periodv,creditamount ,debitamount, balance from cc where periodv <= '12' ) group by unitcode,unitname,yearV,subjcode,subjname,subjlev,periodv)SELECT YEARMONTH,unitcode ,unitname,subjcode,subjname,subjlev,yearv,periodv,SHamount,SHMARK,balance,'YONYOU' CLIENT FROM( SELECT YEARMONTH, M.ELEDIM_OUTPUT1 unitcode ,unitname,subjcode,subjname,subjlev,yearv,periodv, SHamount,SHMARK,balance FROM ( select yearv||periodv YEARMONTH,unitcode ,unitname,subjcode,subjname,subjlev,yearv,periodv, creditamount*-1 SHamount,'H' SHMARK,balance from( select unitcode ,unitname,subjcode,subjname,subjlev,yearv,periodv,creditamount ,debitamount,balance from ( select * from s1 union all select * from s2 union all select * from s3 union all select * from s4 union all select * from s5 union all select * from s6 union all select * from s7 union all select * from s8 union all select * from s9 union all select * from s10 union all select * from s11 union all select * from s12 ) order by subjcode , periodv ) union all select yearv||periodv YEARMONTH, unitcode ,unitname,subjcode,subjname,subjlev,yearv,periodv, debitamount SHamount,'S' SHMARK,balance from( select unitcode ,unitname,subjcode,subjname,subjlev,yearv,periodv,creditamount ,debitamount, 0 balance from ( select * from s1 union all select * from s2 union all select * from s3 union all select * from s4 union all select * from s5 union all select * from s6 union all select * from s7 union all select * from s8 union all select * from s9 union all select * from s10 union all select * from s11 union all select * from s12 ) order by subjcode , periodv ) ) S LEFT JOIN MAP_REGOLA_TAB_ELEMENTO M ON M.COD_REGOLA_TAB = 'MAP_BUKRS_YONYOU' AND M.COD_MAPPATURA = 'HI_DATA_GL' AND M.ELEDIM_INPUT2 = S.unitcode ) where unitcode is not null AND YEARMONTH IN ({IN-YEARMONTH}) AND UNITCODE IN ({IN-ENTITY}) order by subjcode阅读全文
0 0
- GL
- GL
- GL
- 5GL
- 4GL
- GL Studio
- gl参考
- gl.glTexParameterx
- GL入门
- new gl
- gl website
- open GL
- Open GL
- GL 编辑器
- gl 错误
- Qt error: GL/gl.h
- GL calls GL verts FPS
- Cocos2dx----GL calls GL verts
- LeetCode 552. Student Attendance Record II
- python数据类型小结
- 深入理解Java中的堆和栈
- SDUT 2562 相似三角形
- bzoj1854: [Scoi2010]游戏
- GL
- Python---数据类型内置/非内置方法
- 数据挖掘学习（一）
- 解决Eclipse建立Maven项目后无法建立src/main/java资源文件夹的办法
- 原生js实现的星级评分效果
- FileNotFoundError: No such file or directory: '/templates/header.html'
- 深入理解HTTP Session
- notepad++ 的c11，c++11环境设置
- 宏电GPRS DTU H7210调试报告（参数配置）
