Chinaunix首页 | 论坛 | 博客
  • 博客访问: 66084
  • 博文数量: 11
  • 博客积分: 1455
  • 博客等级: 上尉
  • 技术积分: 225
  • 用 户 组: 普通用户
  • 注册时间: 2008-01-10 12:28
文章分类

全部博文(11)

文章存档

2018年(1)

2012年(1)

2011年(5)

2009年(1)

2008年(3)

我的朋友
最近访客

分类:

2011-04-29 00:01:18

/*報表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

阅读(10421) | 评论(0) | 转发(0) |
0

上一篇:SQL

下一篇:PUR02

给主人留下些什么吧!~~