/*報表SQL語句,這份報表採用了調用函數(V820FNC.GL013_V820LIVEFH)的形式來獲取相關的數據*/
SELECT GL.*,CASE WHEN ?ACCOUNT_BOOK?=2 THEN '(HK)' ELSE '' END AS HK_RPT
FROM TABLE (V820FNC.GL013_V820LIVEFH
(?FISCAL_YEAR?,?FISCAL_PERIOD?,?ACCOUNT_BOOK?,?HHSTAT_Y_N?,?YEAR_AMT?)
) AS GL
-- 建立SQL
-- 版本： V5R3M0 040528
-- 產生在： 25/02/08 13:38:46
-- 關聯式資料庫： S6512C4C
-- 標準選項： DB2 UDB iSeries
SET PATH "QSYS","QSYS2","LMRPT02" ;
/*創建函數*/
CREATE FUNCTION V820FNC.GL013_V820LIVEFH (
FISCAL_YEAR_RPT NUMERIC(19, 0) ,
FISCAL_PERIOD_RPT NUMERIC(19, 0) ,
ACCOUNT_BOOK_RPT NUMERIC(19, 0) ,
HHSTAT_Y_N_RPT VARCHAR(60) ,
YEAR_AMT_RPT VARCHAR(60) )
RETURNS TABLE (
GL_TYPE NUMERIC(19, 6) ,
MC CHAR(250) CCSID 937 ,
HC NUMERIC(19, 6) ,
BYS NUMERIC(19, 6) ,
BNLJS NUMERIC(19, 6) ,
PLEDAT NUMERIC(19, 0) )
LANGUAGE SQL
SPECIFIC V820FNC.GL013_V820LIVEFH
NOT DETERMINISTIC
MODIFIES SQL DATA
CALLED ON NULL INPUT
DISALLOW PARALLEL
CARDINALITY 100000
BEGIN
/*聲明變量*/
DECLARE USER_ID_RPT , PLSDAT_1_RPT , PLEDAT_1_RPT , PLSDAT_2_RPT , PLEDAT_2_RPT NUMERIC ( 19 , 0 ) ;
DECLARE RPT_1 , RPT_2 VARCHAR ( 60 ) ;
DECLARE BYS_1_RPT , BNLJS_1_RPT NUMERIC ( 19 , 6 ) ;
/*設定參數*/
SET USER_ID_RPT = HOUR ( NOW ( ) ) * 10000 + MINUTE ( NOW ( ) ) * 100 + SECOND ( NOW ( ) ) ;
SET RPT_2 = SPACE ( 5 ) ;
DELETE FROM V820TMP . RPTTMPF1 WHERE UID = USER_ID_RPT ;
SET RPT_1 = CASE ACCOUNT_BOOK_RPT WHEN 1 THEN 'PERIOD CN' WHEN 2 THEN 'PERIOD HK' END ; /*設置帳簿*/
/*查找當時會計區間的開始日期及結束日期*/
SELECT PLSDAT , PLEDAT
INTO PLSDAT_1_RPT , PLEDAT_1_RPT
FROM V820LIVEFH . GPL
WHERE PLPTAB = RPT_1
AND PLYEAR = FISCAL_YEAR_RPT
AND PLPERD = FISCAL_PERIOD_RPT ;
/*查找會計區間從"1"到當時會計區間的最小日期及最大日期*/
SELECT MIN ( PLSDAT ) , MAX ( PLEDAT )
INTO PLSDAT_2_RPT , PLEDAT_2_RPT
FROM V820LIVEFH . GPL
WHERE PLPTAB = RPT_1
AND PLYEAR = FISCAL_YEAR_RPT
AND PLPERD BETWEEN 1 AND FISCAL_PERIOD_RPT ;
INSERT INTO V820TMP . RPTTMPF1 ( UID , C1 , C2 , N3 , N4 )
SELECT USER_ID_RPT , 'GL_ITEM' , GHH . CRSG03 , SUM ( AMT1 ) , SUM ( AMT2 ) FROM
(
SELECT
CASE
WHEN
(
ACCOUNT_BOOK_RPT = 2
AND YEAR_AMT_RPT = 'Y'
AND GSV . SVSGTP IN ( 2 , 3 )
AND NJ_CN . NJ IS NOT NULL
AND GHH . HHJDAT BETWEEN FISCAL_YEAR_RPT * 10000 + 101
AND FISCAL_YEAR_RPT * 10000 + 331
)
OR
(
ACCOUNT_BOOK_RPT = 2
AND YEAR_AMT_RPT = 'Y'
AND GSV . SVSGTP IN ( 2 , 3 )
AND NJ_CN . NJ IS NULL
AND GHH . HHJDAT BETWEEN ( FISCAL_YEAR_RPT - 1 ) * 10000 + 101
AND FISCAL_YEAR_RPT * 10000 + 331
)
OR
(
ACCOUNT_BOOK_RPT = 2
AND YEAR_AMT_RPT = 'N'
AND GSV . SVSGTP IN ( 2 , 3 )
AND GHH . HHJDAT BETWEEN ( FISCAL_YEAR_RPT - 1 ) * 10000 + 101
AND ( FISCAL_YEAR_RPT - 1 ) * 10000 + 331
)
THEN '4300'
ELSE GCR . CRSG03
END AS CRSG03 ,
CASE WHEN GHH . HHJDAT BETWEEN PLSDAT_1_RPT AND PLEDAT_1_RPT THEN IFNULL ( GLH . LHDRAM , 0 ) - IFNULL ( GLH . LHCRAM , 0 ) ELSE 0 END AS AMT1 ,
CASE WHEN GHH . HHJDAT BETWEEN PLSDAT_2_RPT AND PLEDAT_2_RPT THEN IFNULL ( GLH . LHDRAM , 0 ) - IFNULL ( GLH . LHCRAM , 0 ) ELSE 0 END AS AMT2
FROM V820LIVEFH . GLH AS GLH
LEFT OUTER JOIN V820LIVEFH . 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 V820LIVEFH . GCR AS GCR ON GLH . LHIAN = GCR . CRIAN
AND GCR . CRID = 'CR'
LEFT OUTER JOIN V820LIVEFH . GSV AS GSV ON GCR . CRSG03 = GSV . SVSGVL
AND GSV . SVID = 'SV'
AND GSV . SVSGMN = 'ACCOUNT'
LEFT OUTER JOIN
(
SELECT MAX ( 'NJ' ) AS NJ
FROM V820LIVEFH . GLH
WHERE LHYEAR = FISCAL_YEAR_RPT
AND LHPERD = 0
) AS NJ_CN
ON 0 = 0
WHERE GLH . LHID = 'LH'
AND CASE WHEN GHH . HHSTAT = 3 THEN 'Y' ELSE 'N' END LIKE CASE WHEN HHSTAT_Y_N_RPT = '' THEN 'Y' WHEN HHSTAT_Y_N_RPT = 'A' THEN '' ELSE HHSTAT_Y_N_RPT END || '%'
) AS GHH
GROUP BY GHH . CRSG03 ;
/*以上的SQL語句主要是進行把从会计期间为"1"的最小日期值到会计期间为输入值的最大日期之间的明细數據*/
/*存放進臨時表V820TMP.RPTTMPF1 ,C2代表会计科目编号*/
/*N3代表报表中"本月數"的金额数,N4代表报表中"本年累計數"的金额数*/
/*要注意在以下SQL中N1,N2的变化,N1代表會計科目的排序順序,N2的變化是行次數的變化,具體請運行一份報表進行對照即可.*/
/*注意BYS_1_RPT为会计科目"一 ` 主營業務收入"本月数,BNLJS_1_RPT为会计科目"一 ` 主營業務收入"本年累計数,这两项是从会计科目('7110' , '7120' , '7141' , '7150' )上取数*/
SELECT SUM ( ABS ( N3 ) ) , SUM ( ABS ( N4 ) ) INTO BYS_1_RPT , BNLJS_1_RPT FROM V820TMP . RPTTMPF1 WHERE UID = USER_ID_RPT AND C1 = 'GL_ITEM'
AND C2 IN ( '7110' , '7120' , '7141' , '7150' ) ;
/*把抓取到的本月數及本年累計數及相關數據放進臨時表V820TMP.RPTTMPF1中.*/
INSERT INTO V820TMP . RPTTMPF1
( UID , C1 , N1 , C30 , N2 , N3 , N4 )
VALUES
( USER_ID_RPT , 'GL' , 1 , '一 ` 主營業務收入' , 1 , BYS_1_RPT , BNLJS_1_RPT ) ;
/*注意BYS_1_RPT为会计科目"減 : 主營業務成本"本月数,BNLJS_1_RPT为会计科目"減 : 主營業務成本"本年累計数,这两项是从会计科目
('5110-5119' , '5120-5199' , '6000-6099' , '5220-5299', '6110-6199', '8000-8099', '5210-5219' )上取数*/
SELECT SUM ( N3 ) , SUM ( N4 ) INTO BYS_1_RPT , BNLJS_1_RPT FROM V820TMP . RPTTMPF1 WHERE UID = USER_ID_RPT AND C1 = 'GL_ITEM'
AND
(
C2 BETWEEN '5110' AND '5119'
OR C2 BETWEEN '5120' AND '5199'
OR C2 BETWEEN '6000' AND '6099'
OR C2 BETWEEN '5220' AND '5299'
OR C2 BETWEEN '6110' AND '6199'
OR C2 BETWEEN '8000' AND '8099'
OR C2 BETWEEN '5210' AND '5219'
) ;
/*把抓取到的本月數及本年累計數及相關數據放進臨時表V820TMP.RPTTMPF1中.*/
INSERT INTO V820TMP . RPTTMPF1
( UID , C1 , N1 , C30 , N2 , N3 , N4 )
VALUES
( USER_ID_RPT , 'GL' , 1 , RPT_2 || '減 : 主營業務成本' , 4 , BYS_1_RPT , BNLJS_1_RPT ) ;
/*科目"主營業務稅金及附加"的本月數及本年累計數都設置為零並放進臨時表V820TMP.RPTTMPF1中*/
INSERT INTO V820TMP . RPTTMPF1
( UID , C1 , N1 , C30 , N2 , N3 , N4 )
VALUES
( USER_ID_RPT , 'GL' , 1 , RPT_2 || RPT_2 || '主營業務稅金及附加' , 5 , 0 , 0 ) ;
/*將以上N2=(1,4,5)的本月數及本年累計數進行合計.具體請看SQL中的CASE語句段,並把合計出來的數據作為科目"二 ` 主營業務利潤(虧損以"-"號填列)"的本月數及本年累計數*/
SELECT SUM ( CASE WHEN N2 IN ( 4 , 5 ) THEN 0 - N3 ELSE N3 END ) , SUM ( CASE WHEN N2 IN ( 4 , 5 ) THEN 0 - N4 ELSE N4 END ) INTO BYS_1_RPT , BNLJS_1_RPT
FROM V820TMP . RPTTMPF1 WHERE UID = USER_ID_RPT AND C1 = 'GL' AND N2 IN ( 1 , 4 , 5 ) ;
/*把抓取到的本月數及本年累計數及相關數據放進臨時表V820TMP.RPTTMPF1中.*/
INSERT INTO V820TMP . RPTTMPF1
( UID , C1 , N1 , C30 , N2 , N3 , N4 )
VALUES
( USER_ID_RPT , 'GL' , 2 , '二 ` 主營業務利潤(虧損以"-"號填列)' , 10 , BYS_1_RPT , BNLJS_1_RPT ) ;
/*以下這兩段SQL的意思是從科目編號為(7320,7330,7340)中取出本月數及本年累計數進行合計,然後把合計到的數據再減去科目編號 7410的數據.最後把得到的數據作為
科目"加 : 其他業務利潤(虧損以"-"號填列)"的本月數及本年累計數*/
SELECT SUM ( ABS ( N3 ) ) , SUM ( ABS ( N4 ) ) INTO BYS_1_RPT , BNLJS_1_RPT
FROM V820TMP . RPTTMPF1 WHERE UID = USER_ID_RPT AND C1 = 'GL_ITEM' AND C2 IN ( '7320' , '7330' , '7340' ) ;
SELECT BYS_1_RPT - SUM ( ABS ( N3 ) ) , BNLJS_1_RPT - SUM ( ABS ( N4 ) ) INTO BYS_1_RPT , BNLJS_1_RPT
FROM V820TMP . RPTTMPF1 WHERE UID = USER_ID_RPT AND C1 = 'GL_ITEM' AND C2 = '7410' ;
/*把抓取到的本月數及本年累計數及相關數據放進臨時表V820TMP.RPTTMPF1中.*/
INSERT INTO V820TMP . RPTTMPF1
( UID , C1 , N1 , C30 , N2 , N3 , N4 )
VALUES
( USER_ID_RPT , 'GL' , 2 , RPT_2 || '加 : 其他業務利潤(虧損以"-"號填列)' , 11 , BYS_1_RPT , BNLJS_1_RPT ) ;
/*注意BYS_1_RPT为会计科目"減 : 營業費用"本月数,BNLJS_1_RPT为会计科目"減 : 營業費用"本年累計数,这两项是从会计科目
('8110' , '8111' )上取数*/
SELECT SUM ( ABS ( N3 ) ) , SUM ( ABS ( N4 ) ) INTO BYS_1_RPT , BNLJS_1_RPT FROM V820TMP . RPTTMPF1 WHERE UID = USER_ID_RPT AND C1 = 'GL_ITEM'
AND C2 IN ( '8110' , '8111' ) ;
/*把抓取到的本月數及本年累計數及相關數據放進臨時表V820TMP.RPTTMPF1中.*/
INSERT INTO V820TMP . RPTTMPF1
( UID , C1 , N1 , C30 , N2 , N3 , N4 )
VALUES
( USER_ID_RPT , 'GL' , 2 , RPT_2 || '減 : 營業費用' , 14 , BYS_1_RPT , BNLJS_1_RPT ) ;
/*注意BYS_1_RPT为会计科目"管理費用"本月数,BNLJS_1_RPT为会计科目"管理費用"本年累計数,这两项是从会计科目
( '8201' , '8202' , '8203' , '8206' , '8207' , '8209' , '8211' , '8214' , '8215' , '8216' , '8217' , '8218' , '8219' , '8220' , '8221' , '8222' , '8223' , '8224' , '8225' , '8228' , '8229' , '8230' , '8231'
, '8239' , '8241' , '8242')上取数*/
SELECT SUM ( ABS ( N3 ) ) , SUM ( ABS ( N4 ) ) INTO BYS_1_RPT , BNLJS_1_RPT FROM V820TMP . RPTTMPF1 WHERE UID = USER_ID_RPT AND C1 = 'GL_ITEM'
AND C2 IN ( '8201' , '8202' , '8203' , '8206' , '8207' , '8209' , '8211' , '8214' , '8215' , '8216' , '8217' , '8218' , '8219' , '8220' ,
'8221' , '8222' , '8223' , '8224' , '8225' , '8228' , '8229' , '8230' , '8231' , '8239' , '8241' , '8242' ) ;
/*把抓取到的本月數及本年累計數及相關數據放進臨時表V820TMP.RPTTMPF1中.*/
INSERT INTO V820TMP . RPTTMPF1
( UID , C1 , N1 , C30 , N2 , N3 , N4 )
VALUES
( USER_ID_RPT , 'GL' , 2 , RPT_2 || RPT_2 || '管理費用' , 15 , BYS_1_RPT , BNLJS_1_RPT ) ;
/*注意以下兩段SQL:BYS_1_RPT为会计科目"財務費用"本月数,BNLJS_1_RPT为会计科目"財務費用"本年累計数,这两项是从会计科目
( '8301' , '8303' , '8302' )上取数*/
SELECT SUM ( ABS ( N3 ) ) , SUM ( ABS ( N4 ) ) INTO BYS_1_RPT , BNLJS_1_RPT FROM V820TMP . RPTTMPF1 WHERE UID = USER_ID_RPT AND C1 = 'GL_ITEM'
AND C2 IN ( '8301' , '8303' ) ;
SELECT BYS_1_RPT + SUM ( N3 ) , BNLJS_1_RPT + SUM ( N4 ) INTO BYS_1_RPT , BNLJS_1_RPT FROM V820TMP . RPTTMPF1 WHERE UID = USER_ID_RPT
AND C1 ='GL_ITEM' AND C2 = '8302' ;
/*把抓取到的本月數及本年累計數及相關數據放進臨時表V820TMP.RPTTMPF1中.*/
INSERT INTO V820TMP . RPTTMPF1
( UID , C1 , N1 , C30 , N2 , N3 , N4 )
VALUES
( USER_ID_RPT , 'GL' , 2 , RPT_2 || RPT_2 || '財務費用' , 16 , BYS_1_RPT , BNLJS_1_RPT ) ;
/*將以上N2=(10 , 11 , 14 , 15 , 16)的本月數及本年累計數進行合計.具體請看SQL中的CASE語句段,
並把合計出來的數據作為科目"三 ` 營業利潤(虧損以"-"號填列)"的本月數及本年累計數*/
SELECT SUM ( CASE WHEN N2 IN ( 14 , 15 , 16 ) THEN 0 - N3 ELSE N3 END ) , SUM ( CASE WHEN N2 IN ( 14 , 15 , 16 ) THEN 0 - N4 ELSE N4 END ) INTO BYS_1_RPT , BNLJS_1_RPT
FROM V820TMP . RPTTMPF1 WHERE UID = USER_ID_RPT AND C1 = 'GL' AND N2 IN ( 10 , 11 , 14 , 15 , 16 ) ;
INSERT INTO V820TMP . RPTTMPF1
( UID , C1 , N1 , C30 , N2 , N3 , N4 )
VALUES
( USER_ID_RPT , 'GL' , 3 , '三 ` 營業利潤(虧損以"-"號填列)' , 18 , BYS_1_RPT , BNLJS_1_RPT ) ;
/*科目"加 : 投資收益(虧損以"-"號填列)"的本月數及本年累計數都設置為零並放進臨時表V820TMP.RPTTMPF1中*/
INSERT INTO V820TMP . RPTTMPF1
( UID , C1 , N1 , C30 , N2 , N3 , N4 )
VALUES
( USER_ID_RPT , 'GL' , 3 , RPT_2 || '加 : 投資收益(虧損以"-"號填列)' , 19 , 0 , 0 ) ;
/*科目"補貼收入"的本月數及本年累計數都設置為零並放進臨時表V820TMP.RPTTMPF1中*/
INSERT INTO V820TMP . RPTTMPF1
( UID , C1 , N1 , C30 , N2 , N3 , N4 )
VALUES
( USER_ID_RPT , 'GL' , 3 , RPT_2 || RPT_2 || '補貼收入' , 22 , 0 , 0 ) ;
/*注意BYS_1_RPT为会计科目"營業外收入"本月数,BNLJS_1_RPT为会计科目"營業外收入"本年累計数,这两项是从会计科目
('7310' , '7312' )上取数*/
SELECT SUM ( ABS ( N3 ) ) , SUM ( ABS ( N4 ) ) INTO BYS_1_RPT , BNLJS_1_RPT FROM V820TMP . RPTTMPF1 WHERE UID = USER_ID_RPT AND C1 = 'GL_ITEM'
AND C2 IN ( '7310' , '7312' ) ;
/*把抓取到的本月數及本年累計數及相關數據放進臨時表V820TMP.RPTTMPF1中.*/
INSERT INTO V820TMP . RPTTMPF1
( UID , C1 , N1 , C30 , N2 , N3 , N4 )
VALUES
( USER_ID_RPT , 'GL' , 3 , RPT_2 || RPT_2 || '營業外收入' , 23 , BYS_1_RPT , BNLJS_1_RPT ) ;
/*科目"減 : 營業外支出"的本月數及本年累計數都設置為零並放進臨時表V820TMP.RPTTMPF1中*/
INSERT INTO V820TMP . RPTTMPF1
( UID , C1 , N1 , C30 , N2 , N3 , N4 )
VALUES
( USER_ID_RPT , 'GL' , 3 , RPT_2 || '減 : 營業外支出' , 25 , 0 , 0 ) ;
/*將以上N2=( 18 , 19 , 22 , 23 , 25)的本月數及本年累計數進行合計.具體請看SQL中的CASE語句段,
並把合計出來的數據作為科目"四 ` 利潤總額(虧損以"-"號填列)"的本月數及本年累計數*/
SELECT SUM ( CASE N2 WHEN 25 THEN 0 - N3 ELSE N3 END ) , SUM ( CASE N2 WHEN 25 THEN 0 - N4 ELSE N4 END ) INTO BYS_1_RPT , BNLJS_1_RPT
FROM V820TMP . RPTTMPF1 WHERE UID = USER_ID_RPT AND C1 = 'GL' AND N2 IN ( 18 , 19 , 22 , 23 , 25 ) ;
/*把抓取到的本月數及本年累計數及相關數據放進臨時表V820TMP.RPTTMPF1中.*/
INSERT INTO V820TMP . RPTTMPF1
( UID , C1 , N1 , C30 , N2 , N3 , N4 )
VALUES
( USER_ID_RPT , 'GL' , 4 , '四 ` 利潤總額(虧損以"-"號填列)' , 27 , BYS_1_RPT , BNLJS_1_RPT ) ;
/*科目"減 : 所得稅"的本月數及本年累計數都設置為零並放進臨時表V820TMP.RPTTMPF1中*/
INSERT INTO V820TMP . RPTTMPF1
( UID , C1 , N1 , C30 , N2 , N3 , N4 )
VALUES
( USER_ID_RPT , 'GL' , 4 , RPT_2 || '減 : 所得稅' , 28 , 0 , 0 ) ;
/*將以上N2=( 27 , 28)的本月數及本年累計數進行合計.具體請看SQL中的CASE語句段,
並把合計出來的數據作為科目"五 ` 淨利潤"的本月數及本年累計數*/
SELECT SUM ( CASE N2 WHEN 28 THEN 0 - N3 ELSE N3 END ) , SUM ( CASE N2 WHEN 28 THEN 0 - N4 ELSE N4 END ) INTO BYS_1_RPT , BNLJS_1_RPT
FROM V820TMP . RPTTMPF1 WHERE UID = USER_ID_RPT AND C1 = 'GL' AND N2 IN ( 27 , 28 ) ;
/*把抓取到的本月數及本年累計數及相關數據放進臨時表V820TMP.RPTTMPF1中.*/
INSERT INTO V820TMP . RPTTMPF1
( UID , C1 , N1 , C30 , N2 , N3 , N4 )
VALUES
( USER_ID_RPT , 'GL' , 5 , '五 ` 淨利潤' , 30 , BYS_1_RPT , BNLJS_1_RPT ) ;
/*返回報表所需的數據*/
RETURN
SELECT N1 , C30 , N2 , N3 , N4 , UID
FROM V820TMP . RPTTMPF1
WHERE UID = USER_ID_RPT
AND C1 = 'GL' ;
END
