[Home] [Help]
PACKAGE BODY: APPS.CE_FORECAST_SUMMARY_PKG
Source
1 PACKAGE BODY CE_FORECAST_SUMMARY_PKG as
2 /* $Header: cefssumb.pls 120.1 2002/11/12 21:26:16 bhchung ship $ */
3 PROCEDURE select_summary( X_forecast_id NUMBER,
4 R_total0 IN OUT NOCOPY NUMBER,
5 R_total1 IN OUT NOCOPY NUMBER, R_total2 IN OUT NOCOPY NUMBER,
6 R_total3 IN OUT NOCOPY NUMBER, R_total4 IN OUT NOCOPY NUMBER,
7 R_total5 IN OUT NOCOPY NUMBER, R_total6 IN OUT NOCOPY NUMBER,
8 R_total7 IN OUT NOCOPY NUMBER, R_total8 IN OUT NOCOPY NUMBER,
9 R_total9 IN OUT NOCOPY NUMBER, R_total10 IN OUT NOCOPY NUMBER,
10 R_total11 IN OUT NOCOPY NUMBER, R_total12 IN OUT NOCOPY NUMBER,
11 R_total13 IN OUT NOCOPY NUMBER, R_total14 IN OUT NOCOPY NUMBER,
12 R_total15 IN OUT NOCOPY NUMBER, R_total16 IN OUT NOCOPY NUMBER,
13 R_total17 IN OUT NOCOPY NUMBER, R_total18 IN OUT NOCOPY NUMBER,
14 R_total19 IN OUT NOCOPY NUMBER, R_total20 IN OUT NOCOPY NUMBER,
15 R_total21 IN OUT NOCOPY NUMBER, R_total22 IN OUT NOCOPY NUMBER,
16 R_total23 IN OUT NOCOPY NUMBER, R_total24 IN OUT NOCOPY NUMBER,
17 R_total25 IN OUT NOCOPY NUMBER, R_total26 IN OUT NOCOPY NUMBER,
18 R_total27 IN OUT NOCOPY NUMBER, R_total28 IN OUT NOCOPY NUMBER,
19 R_total29 IN OUT NOCOPY NUMBER, R_total30 IN OUT NOCOPY NUMBER,
20 R_total31 IN OUT NOCOPY NUMBER, R_total32 IN OUT NOCOPY NUMBER,
21 R_total33 IN OUT NOCOPY NUMBER, R_total34 IN OUT NOCOPY NUMBER,
22 R_total35 IN OUT NOCOPY NUMBER, R_total36 IN OUT NOCOPY NUMBER,
23 R_total37 IN OUT NOCOPY NUMBER, R_total38 IN OUT NOCOPY NUMBER,
24 R_total39 IN OUT NOCOPY NUMBER, R_total40 IN OUT NOCOPY NUMBER,
25 E_total0 IN OUT NOCOPY NUMBER,
26 E_total1 IN OUT NOCOPY NUMBER, E_total2 IN OUT NOCOPY NUMBER,
27 E_total3 IN OUT NOCOPY NUMBER, E_total4 IN OUT NOCOPY NUMBER,
28 E_total5 IN OUT NOCOPY NUMBER, E_total6 IN OUT NOCOPY NUMBER,
29 E_total7 IN OUT NOCOPY NUMBER, E_total8 IN OUT NOCOPY NUMBER,
30 E_total9 IN OUT NOCOPY NUMBER, E_total10 IN OUT NOCOPY NUMBER,
31 E_total11 IN OUT NOCOPY NUMBER, E_total12 IN OUT NOCOPY NUMBER,
32 E_total13 IN OUT NOCOPY NUMBER, E_total14 IN OUT NOCOPY NUMBER,
33 E_total15 IN OUT NOCOPY NUMBER, E_total16 IN OUT NOCOPY NUMBER,
34 E_total17 IN OUT NOCOPY NUMBER, E_total18 IN OUT NOCOPY NUMBER,
35 E_total19 IN OUT NOCOPY NUMBER, E_total20 IN OUT NOCOPY NUMBER,
36 E_total21 IN OUT NOCOPY NUMBER, E_total22 IN OUT NOCOPY NUMBER,
37 E_total23 IN OUT NOCOPY NUMBER, E_total24 IN OUT NOCOPY NUMBER,
38 E_total25 IN OUT NOCOPY NUMBER, E_total26 IN OUT NOCOPY NUMBER,
39 E_total27 IN OUT NOCOPY NUMBER, E_total28 IN OUT NOCOPY NUMBER,
40 E_total29 IN OUT NOCOPY NUMBER, E_total30 IN OUT NOCOPY NUMBER,
41 E_total31 IN OUT NOCOPY NUMBER, E_total32 IN OUT NOCOPY NUMBER,
42 E_total33 IN OUT NOCOPY NUMBER, E_total34 IN OUT NOCOPY NUMBER,
43 E_total35 IN OUT NOCOPY NUMBER, E_total36 IN OUT NOCOPY NUMBER,
44 E_total37 IN OUT NOCOPY NUMBER, E_total38 IN OUT NOCOPY NUMBER,
45 E_total39 IN OUT NOCOPY NUMBER, E_total40 IN OUT NOCOPY NUMBER) IS
46 CURSOR C_project IS
47 SELECT project_id
48 FROM ce_forecasts_v
49 WHERE forecast_id = X_forecast_id;
50 l_dummy NUMBER;
51 BEGIN
52 OPEN C_project;
53 FETCH C_project INTO l_dummy;
54 CLOSE C_project;
55
56 IF l_dummy IS NULL THEN
57 SELECT sum(column0),
58 sum(column1), sum(column2), sum(column3), sum(column4), sum(column5),
59 sum(column6), sum(column7), sum(column8), sum(column9), sum(column10),
60 sum(column11), sum(column12), sum(column13), sum(column14), sum(column15),
61 sum(column16), sum(column17), sum(column18), sum(column19), sum(column20),
62 sum(column21), sum(column22), sum(column23), sum(column24), sum(column25),
63 sum(column26), sum(column27), sum(column28), sum(column29), sum(column30),
64 sum(column31), sum(column32), sum(column33), sum(column34), sum(column35),
65 sum(column36), sum(column37), sum(column38), sum(column39), sum(column40)
66 INTO R_total0, R_total1, R_total2, R_total3, R_total4, R_total5,
67 R_total6, R_total7, R_total8, R_total9, R_total10,
68 R_total11, R_total12, R_total13, R_total14, R_total15,
69 R_total16, R_total17, R_total18, R_total19, R_total20,
70 R_total21, R_total22, R_total23, R_total24, R_total25,
71 R_total26, R_total27, R_total28, R_total29, R_total30,
72 R_total31, R_total32, R_total33, R_total34, R_total35,
73 R_total36, R_total37, R_total38, R_total39, R_total40
74 FROM CE_FORECAST_SUMMARY_V
75 WHERE forecast_id = X_forecast_id
76 AND TRX_TYPE IN ('ARI', 'ARR', 'ASF', 'UDI', 'OEO', 'OII', 'XTR');
77
78 select sum(column0),
79 sum(column1), sum(column2), sum(column3), sum(column4), sum(column5),
80 sum(column6), sum(column7), sum(column8), sum(column9), sum(column10),
81 sum(column11), sum(column12), sum(column13), sum(column14), sum(column15),
82 sum(column16), sum(column17), sum(column18), sum(column19), sum(column20),
83 sum(column21), sum(column22), sum(column23), sum(column24), sum(column25),
84 sum(column26), sum(column27), sum(column28), sum(column29), sum(column30),
85 sum(column31), sum(column32), sum(column33), sum(column34), sum(column35),
86 sum(column36), sum(column37), sum(column38), sum(column39), sum(column40)
87 INTO E_total0, E_total1, E_total2, E_total3, E_total4, E_total5,
88 E_total6, E_total7, E_total8, E_total9, E_total10,
89 E_total11, E_total12, E_total13, E_total14, E_total15,
90 E_total16, E_total17, E_total18, E_total19, E_total20,
91 E_total21, E_total22, E_total23, E_total24, E_total25,
92 E_total26, E_total27, E_total28, E_total29, E_total30,
93 E_total31, E_total32, E_total33, E_total34, E_total35,
94 E_total36, E_total37, E_total38, E_total39, E_total40
95 FROM CE_FORECAST_SUMMARY_V
96 WHERE forecast_id = X_forecast_id
97 AND TRX_TYPE IN ('APP', 'API', 'UDO',
98 'APX', 'PAY', 'POP', 'POR', 'OIO');
99 ELSE
100 SELECT sum(column0),
101 sum(column1), sum(column2), sum(column3), sum(column4), sum(column5),
102 sum(column6), sum(column7), sum(column8), sum(column9), sum(column10),
103 sum(column11), sum(column12), sum(column13), sum(column14), sum(column15),
104 sum(column16), sum(column17), sum(column18), sum(column19), sum(column20),
105 sum(column21), sum(column22), sum(column23), sum(column24), sum(column25),
106 sum(column26), sum(column27), sum(column28), sum(column29), sum(column30),
107 sum(column31), sum(column32), sum(column33), sum(column34), sum(column35),
108 sum(column36), sum(column37), sum(column38), sum(column39), sum(column40)
109 INTO R_total0, R_total1, R_total2, R_total3, R_total4, R_total5,
110 R_total6, R_total7, R_total8, R_total9, R_total10,
111 R_total11, R_total12, R_total13, R_total14, R_total15,
112 R_total16, R_total17, R_total18, R_total19, R_total20,
113 R_total21, R_total22, R_total23, R_total24, R_total25,
114 R_total26, R_total27, R_total28, R_total29, R_total30,
115 R_total31, R_total32, R_total33, R_total34, R_total35,
116 R_total36, R_total37, R_total38, R_total39, R_total40
117 FROM CE_FORECAST_SUMMARY_V
118 WHERE forecast_id = X_forecast_id
119 AND TRX_TYPE IN ('ARI', 'PAI', 'PAB', 'UDI', 'OEO', 'OII');
120
121 select sum(column0),
122 sum(column1), sum(column2), sum(column3), sum(column4), sum(column5),
123 sum(column6), sum(column7), sum(column8), sum(column9), sum(column10),
124 sum(column11), sum(column12), sum(column13), sum(column14), sum(column15),
125 sum(column16), sum(column17), sum(column18), sum(column19), sum(column20),
126 sum(column21), sum(column22), sum(column23), sum(column24), sum(column25),
127 sum(column26), sum(column27), sum(column28), sum(column29), sum(column30),
128 sum(column31), sum(column32), sum(column33), sum(column34), sum(column35),
129 sum(column36), sum(column37), sum(column38), sum(column39), sum(column40)
130 INTO E_total0, E_total1, E_total2, E_total3, E_total4, E_total5,
131 E_total6, E_total7, E_total8, E_total9, E_total10,
132 E_total11, E_total12, E_total13, E_total14, E_total15,
133 E_total16, E_total17, E_total18, E_total19, E_total20,
134 E_total21, E_total22, E_total23, E_total24, E_total25,
135 E_total26, E_total27, E_total28, E_total29, E_total30,
136 E_total31, E_total32, E_total33, E_total34, E_total35,
137 E_total36, E_total37, E_total38, E_total39, E_total40
138 FROM CE_FORECAST_SUMMARY_V
139 WHERE forecast_id = X_forecast_id
140 AND TRX_TYPE IN ('API', 'PAO', 'PAT', 'UDO',
141 'APX', 'POP', 'POR', 'OIO');
142 END IF;
143
144 IF R_total0 IS NULL THEN
145 R_total0 := 0; R_total1 := 0; R_total2 := 0; R_total3 := 0; R_total4 := 0; R_total5 := 0;
146 R_total6 := 0; R_total7 := 0; R_total8 := 0; R_total9 := 0; R_total10 := 0;
147 R_total11 := 0; R_total12 := 0; R_total13 := 0; R_total14 := 0; R_total15 := 0;
148 R_total16 := 0; R_total17 := 0; R_total18 := 0; R_total19 := 0; R_total20 := 0;
149 R_total21 := 0; R_total22 := 0; R_total23 := 0; R_total24 := 0; R_total25 := 0;
150 R_total26 := 0; R_total27 := 0; R_total28 := 0; R_total29 := 0; R_total30 := 0;
151 R_total31 := 0; R_total32 := 0; R_total33 := 0; R_total34 := 0; R_total35 := 0;
152 R_total36 := 0; R_total37 := 0; R_total38 := 0; R_total39 := 0; R_total40 := 0;
153 ELSIF E_total0 IS NULL THEN
154 E_total0 := 0; E_total1 := 0; E_total2 := 0; E_total3 := 0; E_total4 := 0; E_total5 := 0;
155 E_total6 := 0; E_total7 := 0; E_total8 := 0; E_total9 := 0; E_total10 := 0;
156 E_total11 := 0; E_total12 := 0; E_total13 := 0; E_total14 := 0; E_total15 := 0;
157 E_total16 := 0; E_total17 := 0; E_total18 := 0; E_total19 := 0; E_total20 := 0;
158 E_total21 := 0; E_total22 := 0; E_total23 := 0; E_total24 := 0; E_total25 := 0;
159 E_total26 := 0; E_total27 := 0; E_total28 := 0; E_total29 := 0; E_total30 := 0;
160 E_total31 := 0; E_total32 := 0; E_total33 := 0; E_total34 := 0; E_total35 := 0;
161 E_total36 := 0; E_total37 := 0; E_total38 := 0; E_total39 := 0; E_total40 := 0;
162 END IF;
163 EXCEPTION
164 WHEN OTHERS THEN
165 RAISE;
166 END;
167
168 END CE_FORECAST_SUMMARY_PKG;