[Home] [Help]
PACKAGE BODY: APPS.CE_FORECAST_SUMMARY2_PKG
Source
1 PACKAGE BODY CE_FORECAST_SUMMARY2_PKG as
2 /* $Header: cefsum2b.pls 120.1 2002/11/12 21:34:37 bhchung ship $ */
3 PROCEDURE select_summary( X_forecast_id NUMBER,
4 R_total41 IN OUT NOCOPY NUMBER, R_total42 IN OUT NOCOPY NUMBER,
5 R_total43 IN OUT NOCOPY NUMBER, R_total44 IN OUT NOCOPY NUMBER,
6 R_total45 IN OUT NOCOPY NUMBER, R_total46 IN OUT NOCOPY NUMBER,
7 R_total47 IN OUT NOCOPY NUMBER, R_total48 IN OUT NOCOPY NUMBER,
8 R_total49 IN OUT NOCOPY NUMBER, R_total50 IN OUT NOCOPY NUMBER,
9 R_total51 IN OUT NOCOPY NUMBER, R_total52 IN OUT NOCOPY NUMBER,
10 R_total53 IN OUT NOCOPY NUMBER, R_total54 IN OUT NOCOPY NUMBER,
11 R_total55 IN OUT NOCOPY NUMBER, R_total56 IN OUT NOCOPY NUMBER,
12 R_total57 IN OUT NOCOPY NUMBER, R_total58 IN OUT NOCOPY NUMBER,
13 R_total59 IN OUT NOCOPY NUMBER, R_total60 IN OUT NOCOPY NUMBER,
14 R_total61 IN OUT NOCOPY NUMBER, R_total62 IN OUT NOCOPY NUMBER,
15 R_total63 IN OUT NOCOPY NUMBER, R_total64 IN OUT NOCOPY NUMBER,
16 R_total65 IN OUT NOCOPY NUMBER, R_total66 IN OUT NOCOPY NUMBER,
17 R_total67 IN OUT NOCOPY NUMBER, R_total68 IN OUT NOCOPY NUMBER,
18 R_total69 IN OUT NOCOPY NUMBER, R_total70 IN OUT NOCOPY NUMBER,
19 R_total71 IN OUT NOCOPY NUMBER, R_total72 IN OUT NOCOPY NUMBER,
20 R_total73 IN OUT NOCOPY NUMBER, R_total74 IN OUT NOCOPY NUMBER,
21 R_total75 IN OUT NOCOPY NUMBER, R_total76 IN OUT NOCOPY NUMBER,
22 R_total77 IN OUT NOCOPY NUMBER, R_total78 IN OUT NOCOPY NUMBER,
23 R_total79 IN OUT NOCOPY NUMBER, R_total80 IN OUT NOCOPY NUMBER,
24 E_total41 IN OUT NOCOPY NUMBER, E_total42 IN OUT NOCOPY NUMBER,
25 E_total43 IN OUT NOCOPY NUMBER, E_total44 IN OUT NOCOPY NUMBER,
26 E_total45 IN OUT NOCOPY NUMBER, E_total46 IN OUT NOCOPY NUMBER,
27 E_total47 IN OUT NOCOPY NUMBER, E_total48 IN OUT NOCOPY NUMBER,
28 E_total49 IN OUT NOCOPY NUMBER, E_total50 IN OUT NOCOPY NUMBER,
29 E_total51 IN OUT NOCOPY NUMBER, E_total52 IN OUT NOCOPY NUMBER,
30 E_total53 IN OUT NOCOPY NUMBER, E_total54 IN OUT NOCOPY NUMBER,
31 E_total55 IN OUT NOCOPY NUMBER, E_total56 IN OUT NOCOPY NUMBER,
32 E_total57 IN OUT NOCOPY NUMBER, E_total58 IN OUT NOCOPY NUMBER,
33 E_total59 IN OUT NOCOPY NUMBER, E_total60 IN OUT NOCOPY NUMBER,
34 E_total61 IN OUT NOCOPY NUMBER, E_total62 IN OUT NOCOPY NUMBER,
35 E_total63 IN OUT NOCOPY NUMBER, E_total64 IN OUT NOCOPY NUMBER,
36 E_total65 IN OUT NOCOPY NUMBER, E_total66 IN OUT NOCOPY NUMBER,
37 E_total67 IN OUT NOCOPY NUMBER, E_total68 IN OUT NOCOPY NUMBER,
38 E_total69 IN OUT NOCOPY NUMBER, E_total70 IN OUT NOCOPY NUMBER,
39 E_total71 IN OUT NOCOPY NUMBER, E_total72 IN OUT NOCOPY NUMBER,
40 E_total73 IN OUT NOCOPY NUMBER, E_total74 IN OUT NOCOPY NUMBER,
41 E_total75 IN OUT NOCOPY NUMBER, E_total76 IN OUT NOCOPY NUMBER,
42 E_total77 IN OUT NOCOPY NUMBER, E_total78 IN OUT NOCOPY NUMBER,
43 E_total79 IN OUT NOCOPY NUMBER, E_total80 IN OUT NOCOPY NUMBER) IS
44 CURSOR C_project IS
45 SELECT project_id
46 FROM ce_forecasts_v
47 WHERE forecast_id = X_forecast_id;
48 l_dummy NUMBER;
49 BEGIN
50 OPEN C_project;
51 FETCH C_project INTO l_dummy;
52 CLOSE C_project;
53
54 IF l_dummy IS NULL THEN
55 select sum(column41), sum(column42), sum(column43), sum(column44), sum(column45),
56 sum(column46), sum(column47), sum(column48), sum(column49), sum(column50),
57 sum(column51), sum(column52), sum(column53), sum(column54), sum(column55),
58 sum(column56), sum(column57), sum(column58), sum(column59), sum(column60),
59 sum(column61), sum(column62), sum(column63), sum(column64), sum(column65),
60 sum(column66), sum(column67), sum(column68), sum(column69), sum(column70),
61 sum(column71), sum(column72), sum(column73), sum(column74), sum(column75),
62 sum(column76), sum(column77), sum(column78), sum(column79), sum(column80)
63 into R_total41, R_total42, R_total43, R_total44, R_total45,
64 R_total46, R_total47, R_total48, R_total49, R_total50,
65 R_total51, R_total52, R_total53, R_total54, R_total55,
66 R_total56, R_total57, R_total58, R_total59, R_total60,
67 R_total61, R_total62, R_total63, R_total64, R_total65,
68 R_total66, R_total67, R_total68, R_total69, R_total70,
69 R_total71, R_total72, R_total73, R_total74, R_total75,
70 R_total76, R_total77, R_total78, R_total79, R_total80
71 FROM CE_FORECAST_SUMMARY_V
72 WHERE forecast_id = X_forecast_id
73 AND TRX_TYPE IN ('ARI', 'ARR', 'ASF', 'UDI', 'OEO', 'XTR', 'OII');
74
75 select sum(column41), sum(column42), sum(column43), sum(column44), sum(column45),
76 sum(column46), sum(column47), sum(column48), sum(column49), sum(column50),
77 sum(column51), sum(column52), sum(column53), sum(column54), sum(column55),
78 sum(column56), sum(column57), sum(column58), sum(column59), sum(column60),
79 sum(column61), sum(column62), sum(column63), sum(column64), sum(column65),
80 sum(column66), sum(column67), sum(column68), sum(column69), sum(column70),
81 sum(column71), sum(column72), sum(column73), sum(column74), sum(column75),
82 sum(column76), sum(column77), sum(column78), sum(column79), sum(column80)
83 into E_total41, E_total42, E_total43, E_total44, E_total45,
84 E_total46, E_total47, E_total48, E_total49, E_total50,
85 E_total51, E_total52, E_total53, E_total54, E_total55,
86 E_total56, E_total57, E_total58, E_total59, E_total60,
87 E_total61, E_total62, E_total63, E_total64, E_total65,
88 E_total66, E_total67, E_total68, E_total69, E_total70,
89 E_total71, E_total72, E_total73, E_total74, E_total75,
90 E_total76, E_total77, E_total78, E_total79, E_total80
91 FROM CE_FORECAST_SUMMARY_V
92 WHERE forecast_id = X_forecast_id
93 AND TRX_TYPE IN ('APP', 'API', 'UDO',
94 'APX', 'PAY', 'POP', 'POR', 'OIO');
95 ELSE
96 select sum(column41), sum(column42), sum(column43), sum(column44), sum(column45),
97 sum(column46), sum(column47), sum(column48), sum(column49), sum(column50),
98 sum(column51), sum(column52), sum(column53), sum(column54), sum(column55),
99 sum(column56), sum(column57), sum(column58), sum(column59), sum(column60),
100 sum(column61), sum(column62), sum(column63), sum(column64), sum(column65),
101 sum(column66), sum(column67), sum(column68), sum(column69), sum(column70),
102 sum(column71), sum(column72), sum(column73), sum(column74), sum(column75),
103 sum(column76), sum(column77), sum(column78), sum(column79), sum(column80)
104 into R_total41, R_total42, R_total43, R_total44, R_total45,
105 R_total46, R_total47, R_total48, R_total49, R_total50,
106 R_total51, R_total52, R_total53, R_total54, R_total55,
107 R_total56, R_total57, R_total58, R_total59, R_total60,
108 R_total61, R_total62, R_total63, R_total64, R_total65,
109 R_total66, R_total67, R_total68, R_total69, R_total70,
110 R_total71, R_total72, R_total73, R_total74, R_total75,
111 R_total76, R_total77, R_total78, R_total79, R_total80
112 FROM CE_FORECAST_SUMMARY_V
113 WHERE forecast_id = X_forecast_id
114 AND TRX_TYPE IN ('ARI', 'PAI', 'PAB', 'UDI', 'OEO', 'OII');
115
116 select sum(column41), sum(column42), sum(column43), sum(column44), sum(column45),
117 sum(column46), sum(column47), sum(column48), sum(column49), sum(column50),
118 sum(column51), sum(column52), sum(column53), sum(column54), sum(column55),
119 sum(column56), sum(column57), sum(column58), sum(column59), sum(column60),
120 sum(column61), sum(column62), sum(column63), sum(column64), sum(column65),
121 sum(column66), sum(column67), sum(column68), sum(column69), sum(column70),
122 sum(column71), sum(column72), sum(column73), sum(column74), sum(column75),
123 sum(column76), sum(column77), sum(column78), sum(column79), sum(column80)
124 into E_total41, E_total42, E_total43, E_total44, E_total45,
125 E_total46, E_total47, E_total48, E_total49, E_total50,
126 E_total51, E_total52, E_total53, E_total54, E_total55,
127 E_total56, E_total57, E_total58, E_total59, E_total60,
128 E_total61, E_total62, E_total63, E_total64, E_total65,
129 E_total66, E_total67, E_total68, E_total69, E_total70,
130 E_total71, E_total72, E_total73, E_total74, E_total75,
131 E_total76, E_total77, E_total78, E_total79, E_total80
132 FROM CE_FORECAST_SUMMARY_V
133 WHERE forecast_id = X_forecast_id
134 AND TRX_TYPE IN ('API', 'PAO', 'PAT', 'UDO',
135 'APX', 'POP', 'POR', 'OIO');
136 END IF;
137
138 IF R_total41 IS NULL THEN
139 R_total41 := 0; R_total42 := 0; R_total43 := 0; R_total44 := 0; R_total45 := 0;
140 R_total46 := 0; R_total47 := 0; R_total48 := 0; R_total49 := 0; R_total50 := 0;
141 R_total51 := 0; R_total52 := 0; R_total53 := 0; R_total54 := 0; R_total55 := 0;
142 R_total61 := 0; R_total62 := 0; R_total63 := 0; R_total64 := 0; R_total65 := 0;
143 R_total66 := 0; R_total67 := 0; R_total68 := 0; R_total69 := 0; R_total70 := 0;
144 R_total71 := 0; R_total72 := 0; R_total73 := 0; R_total74 := 0; R_total75 := 0;
145 R_total76 := 0; R_total77 := 0; R_total78 := 0; R_total79 := 0; R_total80 := 0;
146 ELSIF E_total41 IS NULL THEN
147 E_total41 := 0; E_total42 := 0; E_total43 := 0; E_total44 := 0; E_total45 := 0;
148 E_total46 := 0; E_total47 := 0; E_total48 := 0; E_total49 := 0; E_total50 := 0;
149 E_total51 := 0; E_total52 := 0; E_total53 := 0; E_total54 := 0; E_total55 := 0;
150 E_total56 := 0; E_total57 := 0; E_total58 := 0; E_total59 := 0; E_total60 := 0;
151 E_total61 := 0; E_total62 := 0; E_total63 := 0; E_total64 := 0; E_total65 := 0;
152 E_total66 := 0; E_total67 := 0; E_total68 := 0; E_total69 := 0; E_total70 := 0;
153 E_total71 := 0; E_total72 := 0; E_total73 := 0; E_total74 := 0; E_total75 := 0;
154 E_total76 := 0; E_total77 := 0; E_total78 := 0; E_total79 := 0; E_total80 := 0;
155 END IF;
156 EXCEPTION
157 WHEN OTHERS THEN
158 RAISE;
159 END;
160
161 END CE_FORECAST_SUMMARY2_PKG;