1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
| create or replace PROCEDURE PROC_FMS406R_TAB6_FCST_VALUES
(
INPUT_YEAR IN VARCHAR2
, INPUT_QUARTER IN VARCHAR2
, INPUT_USER_ID IN VARCHAR2
) AS
BEGIN
NULL;
IF INPUT_QUARTER = '1' THEN
dbms_output.put_line('Q1');
--1.2.9 撈出本年度收入數之預估數，寫入﹝FMS406FI﹞國庫現金收支估測分析表
--1.2.9.1 當前端選擇第一季(本年度1~3月都是預估數)
MERGE INTO FMS406FI T1
USING
(
SELECT YYY_MM, ALC1, ALC2, ALC3
FROM
(
SELECT *
FROM FMS406FG
WHERE SEQ = 2 AND (YYY_MM BETWEEN INPUT_YEAR||'01' AND INPUT_YEAR||'03')
ORDER BY YYY_MM DESC
)
WHERE ROWNUM = 1
) T2
ON (T1.YEAR = INPUT_YEAR AND T1.TYPE='1' AND T1.QUARTER = INPUT_QUARTER)
WHEN MATCHED THEN UPDATE
SET T1.AMT1 = T2.ALC1, T1.AMT2 = T2.ALC2, T1.AMT3 = T2.ALC3
;
dbms_output.put_line('execute 1.2.9 撈出本年度第一季收入數之預估數，寫入﹝FMS406FI﹞國庫現金收支估測分析表');
ELSIF INPUT_QUARTER = '2' THEN
dbms_output.put_line('Q2');
--1.2.9 撈出本年度收入數之預估數，寫入﹝FMS406FI﹞國庫現金收支估測分析表
--1.2.9.2 當前端選擇第二季 (本年度4~6月都是預估數)
MERGE INTO FMS406FI T1
USING
(
SELECT YYY_MM, ALC4, ALC5, ALC6
FROM
(
SELECT *
FROM FMS406FG
WHERE SEQ = 2 AND (YYY_MM BETWEEN INPUT_YEAR||'01' AND INPUT_YEAR||'06')
ORDER BY YYY_MM DESC
)
WHERE ROWNUM = 1
) T2
ON (T1.YEAR = INPUT_YEAR AND T1.TYPE='1' AND T1.QUARTER = INPUT_QUARTER)
WHEN MATCHED THEN UPDATE
SET T1.AMT4 = T2.ALC4, T1.AMT5 = T2.ALC5, T1.AMT6 = T2.ALC6
;
dbms_output.put_line('execute 1.2.9 撈出本年度第二季收入數之預估數，寫入﹝FMS406FI﹞國庫現金收支估測分析表');
ELSIF INPUT_QUARTER = '3' THEN
dbms_output.put_line('Q3');
--1.2.9 撈出本年度收入數之預估數，寫入﹝FMS406FI﹞國庫現金收支估測分析表
--1.2.9.3 當前端選擇第三季 (本年度7~9月都是預估數)
MERGE INTO FMS406FI T1
USING
(
SELECT YYY_MM, ALC7, ALC8, ALC9
FROM
(
SELECT *
FROM FMS406FG
WHERE SEQ = 2 AND (YYY_MM BETWEEN INPUT_YEAR||'01' AND INPUT_YEAR||'09')
ORDER BY YYY_MM DESC
)
WHERE ROWNUM = 1
) T2
ON (T1.YEAR = INPUT_YEAR AND T1.TYPE='1' AND T1.QUARTER = INPUT_QUARTER)
WHEN MATCHED THEN UPDATE
SET T1.AMT7 = T2.ALC7, T1.AMT8 = T2.ALC8, T1.AMT9 = T2.ALC9
;
dbms_output.put_line('execute 1.2.9 撈出本年度第三季收入數之預估數，寫入﹝FMS406FI﹞國庫現金收支估測分析表');
ELSIF INPUT_QUARTER = '4' THEN
dbms_output.put_line('Q4');
--1.2.9 撈出本年度收入數之預估數，寫入﹝FMS406FI﹞國庫現金收支估測分析表
--1.2.9.4 當前端選擇第四季 (本年度10~12月都是預估數)
MERGE INTO FMS406FI T1
USING
(
SELECT YYY_MM, ALC10, ALC11, ALC12
FROM
(
SELECT *
FROM FMS406FG
WHERE SEQ = 2 AND (YYY_MM BETWEEN INPUT_YEAR||'01' AND INPUT_YEAR||'12')
ORDER BY YYY_MM DESC
)
WHERE ROWNUM = 1
) T2
ON (T1.YEAR = INPUT_YEAR AND T1.TYPE='1' AND T1.QUARTER = INPUT_QUARTER)
WHEN MATCHED THEN UPDATE
SET T1.AMT10 = T2.ALC10, T1.AMT11 = T2.ALC11, T1.AMT12 = T2.ALC12
;
dbms_output.put_line('execute 1.2.9 撈出本年度第四季 收入數之預估數，寫入﹝FMS406FI﹞國庫現金收支估測分析表');
END IF;
END PROC_FMS406R_TAB6_FCST_VALUES;
|
