1 PACKAGE BODY CE_CEFPURGE_XMLP_PKG AS
2 /* $Header: CEFPURGEB.pls 120.1 2008/01/07 21:22:17 abraghun noship $ */
3 FUNCTION BEFOREREPORT RETURN BOOLEAN IS
4 BEGIN
5 BEGIN
6 P_CONC_REQUEST_ID := FND_GLOBAL.CONC_REQUEST_ID;
7 IF (P_SQL_TRACE = 'Y') THEN
8 EXECUTE IMMEDIATE
9 'alter session set sql_trace=true';
10 END IF;
11 IF (P_FORECAST_HEADER_ID IS NOT NULL) THEN
12 SELECT
13 NAME
14 INTO
15 C_TEMP_NAME
16 FROM
17 CE_FORECAST_HEADERS
18 WHERE FORECAST_HEADER_ID = P_FORECAST_HEADER_ID;
19 END IF;
20 IF (P_FORECAST_BY = 'D') THEN
21 C_PURGE_OPTION := 'Purge Forecasts by Days';
22 ELSIF (P_FORECAST_BY = 'A') THEN
23 C_PURGE_OPTION := 'Purge Forecasts by GL Periods';
24 ELSE
25 C_PURGE_OPTION := 'Purge All';
26 END IF;
27 P_FORECAST_START_DATE_1 := to_char(TO_DATE(P_FORECAST_START_DATE
28 ,'YYYY/MM/DD HH24:MI:SS'),'DD-MON-YY');
29 P_FORECAST_END_DATE_1 := to_char(TO_DATE(P_FORECAST_END_DATE
30 ,'YYYY/MM/DD HH24:MI:SS'),'DD-MON-YY');
31 IF (P_FORECAST_BY in ('D','B')) THEN
32 SELECT
33 count(*)
34 INTO
35 C_COUNT
36 FROM
37 CE_FORECASTS
38 WHERE FORECAST_HEADER_ID = NVL(P_FORECAST_HEADER_ID
39 ,FORECAST_HEADER_ID)
40 AND START_DATE is not null
41 AND TRUNC(START_DATE) >= NVL(P_FORECAST_START_DATE_1
42 ,TRUNC(START_DATE))
43 AND TRUNC(START_DATE) <= NVL(P_FORECAST_END_DATE_1
44 ,TRUNC(START_DATE));
45 END IF;
46 IF (P_FORECAST_BY in ('A','B')) THEN
47 SELECT
48 count(*) + NVL(C_COUNT
49 ,0)
50 INTO
51 C_COUNT
52 FROM
53 CE_FORECASTS CF
54 WHERE CF.FORECAST_HEADER_ID = NVL(P_FORECAST_HEADER_ID
55 ,CF.FORECAST_HEADER_ID)
56 AND START_PERIOD in (
57 SELECT
58 GLP.PERIOD_NAME
59 FROM
60 GL_PERIODS GLP
61 WHERE GLP.PERIOD_SET_NAME = CF.PERIOD_SET_NAME
62 AND TRUNC(GLP.START_DATE) >= NVL(P_FORECAST_START_DATE_1
63 ,TRUNC(GLP.START_DATE))
64 AND TRUNC(GLP.START_DATE) <= NVL(P_FORECAST_END_DATE_1
65 ,TRUNC(GLP.START_DATE)) );
66 END IF;
67 IF (P_DISPLAY_DEBUG = 'Y') THEN
68 NULL;
69 END IF;
70 END;
71 RETURN (TRUE);
72 END BEFOREREPORT;
73
74 FUNCTION AFTERREPORT RETURN BOOLEAN IS
75 BEGIN
76 BEGIN
77 IF (P_FORECAST_BY in ('D','B')) THEN
78 DELETE FROM CE_FORECAST_CELLS
79 WHERE FORECAST_ID in (
80 SELECT
81 FORECAST_ID
82 FROM
83 CE_FORECASTS
84 WHERE FORECAST_HEADER_ID = NVL(P_FORECAST_HEADER_ID
85 ,FORECAST_HEADER_ID)
86 AND START_DATE is not null
87 AND TRUNC(START_DATE) >= NVL(P_FORECAST_START_DATE_1
88 ,TRUNC(START_DATE))
89 AND TRUNC(START_DATE) <= NVL(P_FORECAST_END_DATE_1
90 ,TRUNC(START_DATE)) );
91 DELETE FROM CE_FORECAST_TRX_CELLS
92 WHERE FORECAST_ID in (
93 SELECT
94 FORECAST_ID
95 FROM
96 CE_FORECASTS
97 WHERE FORECAST_HEADER_ID = NVL(P_FORECAST_HEADER_ID
98 ,FORECAST_HEADER_ID)
99 AND START_DATE is not null
100 AND TRUNC(START_DATE) >= NVL(P_FORECAST_START_DATE_1
101 ,TRUNC(START_DATE))
102 AND TRUNC(START_DATE) <= NVL(P_FORECAST_END_DATE_1
103 ,TRUNC(START_DATE)) );
104 DELETE FROM CE_FORECAST_OPENING_BAL
105 WHERE FORECAST_ID in (
106 SELECT
107 FORECAST_ID
108 FROM
109 CE_FORECASTS
110 WHERE FORECAST_HEADER_ID = NVL(P_FORECAST_HEADER_ID
111 ,FORECAST_HEADER_ID)
112 AND START_DATE is not null
113 AND TRUNC(START_DATE) >= NVL(P_FORECAST_START_DATE_1
114 ,TRUNC(START_DATE))
115 AND TRUNC(START_DATE) <= NVL(P_FORECAST_END_DATE_1
116 ,TRUNC(START_DATE)) );
117 DELETE FROM CE_FORECAST_ERRORS
118 WHERE FORECAST_ID in (
119 SELECT
120 FORECAST_ID
121 FROM
122 CE_FORECASTS
123 WHERE FORECAST_HEADER_ID = NVL(P_FORECAST_HEADER_ID
124 ,FORECAST_HEADER_ID)
125 AND START_DATE is not null
126 AND TRUNC(START_DATE) >= NVL(P_FORECAST_START_DATE_1
127 ,TRUNC(START_DATE))
128 AND TRUNC(START_DATE) <= NVL(P_FORECAST_END_DATE_1
129 ,TRUNC(START_DATE)) );
130 DELETE FROM CE_FORECASTS
131 WHERE FORECAST_HEADER_ID = NVL(P_FORECAST_HEADER_ID
132 ,FORECAST_HEADER_ID)
133 AND START_DATE is not null
134 AND TRUNC(START_DATE) >= NVL(P_FORECAST_START_DATE_1
135 ,TRUNC(START_DATE))
136 AND TRUNC(START_DATE) <= NVL(P_FORECAST_END_DATE_1
137 ,TRUNC(START_DATE));
138 END IF;
139 IF (P_FORECAST_BY in ('A','B')) THEN
140 DELETE FROM CE_FORECAST_CELLS
141 WHERE FORECAST_ID in (
142 SELECT
143 FORECAST_ID
144 FROM
145 CE_FORECASTS CF
146 WHERE FORECAST_HEADER_ID = NVL(P_FORECAST_HEADER_ID
147 ,FORECAST_HEADER_ID)
148 AND START_PERIOD in (
149 SELECT
150 GLP.PERIOD_NAME
151 FROM
152 GL_PERIODS GLP
153 WHERE GLP.PERIOD_SET_NAME = CF.PERIOD_SET_NAME
154 AND TRUNC(GLP.START_DATE) >= NVL(P_FORECAST_START_DATE_1
155 ,TRUNC(GLP.START_DATE))
156 AND TRUNC(GLP.START_DATE) <= NVL(P_FORECAST_END_DATE_1
157 ,TRUNC(GLP.START_DATE)) ) );
158 DELETE FROM CE_FORECAST_TRX_CELLS
159 WHERE FORECAST_ID in (
160 SELECT
161 FORECAST_ID
162 FROM
163 CE_FORECASTS CF
164 WHERE FORECAST_HEADER_ID = NVL(P_FORECAST_HEADER_ID
165 ,FORECAST_HEADER_ID)
166 AND START_PERIOD in (
167 SELECT
168 GLP.PERIOD_NAME
169 FROM
170 GL_PERIODS GLP
171 WHERE GLP.PERIOD_SET_NAME = CF.PERIOD_SET_NAME
172 AND TRUNC(GLP.START_DATE) >= NVL(P_FORECAST_START_DATE_1
173 ,TRUNC(GLP.START_DATE))
174 AND TRUNC(GLP.START_DATE) <= NVL(P_FORECAST_END_DATE_1
175 ,TRUNC(GLP.START_DATE)) ) );
176 DELETE FROM CE_FORECAST_OPENING_BAL
177 WHERE FORECAST_ID in (
178 SELECT
179 FORECAST_ID
180 FROM
181 CE_FORECASTS CF
182 WHERE FORECAST_HEADER_ID = NVL(P_FORECAST_HEADER_ID
183 ,FORECAST_HEADER_ID)
184 AND START_PERIOD in (
185 SELECT
186 GLP.PERIOD_NAME
187 FROM
188 GL_PERIODS GLP
189 WHERE GLP.PERIOD_SET_NAME = CF.PERIOD_SET_NAME
190 AND TRUNC(GLP.START_DATE) >= NVL(P_FORECAST_START_DATE_1
191 ,TRUNC(GLP.START_DATE))
192 AND TRUNC(GLP.START_DATE) <= NVL(P_FORECAST_END_DATE_1
193 ,TRUNC(GLP.START_DATE)) ) );
194 DELETE FROM CE_FORECAST_ERRORS
195 WHERE FORECAST_ID in (
196 SELECT
197 FORECAST_ID
198 FROM
199 CE_FORECASTS CF
200 WHERE FORECAST_HEADER_ID = NVL(P_FORECAST_HEADER_ID
201 ,FORECAST_HEADER_ID)
202 AND START_PERIOD in (
203 SELECT
204 GLP.PERIOD_NAME
205 FROM
206 GL_PERIODS GLP
207 WHERE GLP.PERIOD_SET_NAME = CF.PERIOD_SET_NAME
208 AND TRUNC(GLP.START_DATE) >= NVL(P_FORECAST_START_DATE_1
209 ,TRUNC(GLP.START_DATE))
210 AND TRUNC(GLP.START_DATE) <= NVL(P_FORECAST_END_DATE_1
211 ,TRUNC(GLP.START_DATE)) ) );
212 DELETE FROM CE_FORECASTS CF
213 WHERE FORECAST_HEADER_ID = NVL(P_FORECAST_HEADER_ID
214 ,FORECAST_HEADER_ID)
215 AND START_PERIOD in (
216 SELECT
217 GLP.PERIOD_NAME
218 FROM
219 GL_PERIODS GLP
220 WHERE GLP.PERIOD_SET_NAME = CF.PERIOD_SET_NAME
221 AND TRUNC(GLP.START_DATE) >= NVL(P_FORECAST_START_DATE_1
222 ,TRUNC(GLP.START_DATE))
223 AND TRUNC(GLP.START_DATE) <= NVL(P_FORECAST_END_DATE_1
224 ,TRUNC(GLP.START_DATE)) );
225 END IF;
226 COMMIT;
227 END;
228 RETURN (TRUE);
229 END AFTERREPORT;
230
231 FUNCTION C_COUNT_P RETURN NUMBER IS
232 BEGIN
233 RETURN C_COUNT;
234 END C_COUNT_P;
235
236 FUNCTION C_PURGE_OPTION_P RETURN VARCHAR2 IS
237 BEGIN
238 RETURN C_PURGE_OPTION;
239 END C_PURGE_OPTION_P;
240
241 FUNCTION C_TEMP_NAME_P RETURN VARCHAR2 IS
242 BEGIN
243 RETURN C_TEMP_NAME;
244 END C_TEMP_NAME_P;
245
246 END CE_CEFPURGE_XMLP_PKG;
247
[Home] [Help]