GL

最新推荐文章于 2023-08-10 11:24:21 发布

RJ_Dream

最新推荐文章于 2023-08-10 11:24:21 发布

阅读量450

点赞数

本文链接：https://blog.csdn.net/RJ_Dream/article/details/73744518

版权

Tagetik 草稿专栏收录该内容

5 篇文章 0 订阅

订阅专栏

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) PRCTR
FROM ODS.ODSS600_FAGLFLEXT@FMSLK
where RBUKRS IN ({IN-ENTITY})    
)
WHERE RYEAR||RMONTH  IN ({IN-YEARMONTH})               

UNION ALL
select 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) PRCTR
FROM ODS.ODSS600_FAGLFLEXT@FMSLK  
where RBUKRS IN ({IN-ENTITY})   
)
WHERE RYEAR||RMONTH  IN ({IN-YEARMONTH})              


UNION ALL
select 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) PRCTR
FROM ODS.ODSS600_FAGLFLEXT@FMSLK 
where RBUKRS IN ({IN-ENTITY})         
)
WHERE RYEAR||RMONTH  IN ({IN-YEARMONTH})     

UNION ALL
select 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) PRCTR
FROM ODS.ODSS600_FAGLFLEXT@FMSLK 
      
where RBUKRS IN ({IN-ENTITY})    
)
WHERE RYEAR||RMONTH  IN ({IN-YEARMONTH})            

UNION ALL
select 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) PRCTR
FROM ODS.ODSS600_FAGLFLEXT@FMSLK 
      
where RBUKRS IN ({IN-ENTITY})         
)
WHERE RYEAR||RMONTH  IN ({IN-YEARMONTH})     

UNION ALL
select 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) PRCTR
FROM ODS.ODSS600_FAGLFLEXT@FMSLK 
      
where RBUKRS IN ({IN-ENTITY})  
)
WHERE RYEAR||RMONTH  IN ({IN-YEARMONTH})                 



UNION ALL
select 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) PRCTR
FROM ODS.ODSS600_FAGLFLEXT@FMSLK 
      
where RBUKRS IN ({IN-ENTITY})     
)
WHERE RYEAR||RMONTH  IN ({IN-YEARMONTH})       


UNION ALL
select 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) PRCTR
FROM ODS.ODSS600_FAGLFLEXT@FMSLK 
      
where RBUKRS IN ({IN-ENTITY})        
)
WHERE RYEAR||RMONTH  IN ({IN-YEARMONTH})      

UNION ALL
select 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) PRCTR
FROM ODS.ODSS600_FAGLFLEXT@FMSLK 
      
where RBUKRS IN ({IN-ENTITY})       
)
WHERE RYEAR||RMONTH  IN ({IN-YEARMONTH})          


UNION ALL
select 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) PRCTR
FROM ODS.ODSS600_FAGLFLEXT@FMSLK 
      
where RBUKRS IN ({IN-ENTITY})    
)
WHERE RYEAR||RMONTH  IN ({IN-YEARMONTH})            




UNION ALL
select 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) PRCTR
FROM ODS.ODSS600_FAGLFLEXT@FMSLK 
      
where RBUKRS IN ({IN-ENTITY})         
)
WHERE RYEAR||RMONTH  IN ({IN-YEARMONTH})           




UNION ALL
select 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) PRCTR
FROM 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
) A
LEFT 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' CATEG
FROM 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
) A
LEFT JOIN ODS.ODSS600_SKB1@FMSLK B ON A.RBUKRS = B.BUKRS AND A.RACCT = B.SAKNR

K3:

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, 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,0 beginbalance,0 endbalance, 
        localdebitamount SHamount,'S' 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 
    )
)
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

RJ_Dream

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
GL

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 RYE
复制链接

扫一扫