[Home] [Help]
PACKAGE BODY: APPS.IEC_REPORTS_PVT
Source
1 PACKAGE BODY IEC_REPORTS_PVT AS
2 /* $Header: IECREPB.pls 115.33 2003/07/16 16:53:33 alromero ship $ */
3
4 g_pkg_name CONSTANT VARCHAR2(30) := 'IEC_REPORTS_PVT';
5
6 G_SOURCE_ID NUMBER(15);
7
8 PROCEDURE Log ( p_activity_desc IN VARCHAR2
9 , p_method_name IN VARCHAR2
10 , p_sub_method IN VARCHAR2
11 , p_sql_code IN NUMBER
12 , p_sql_errm IN VARCHAR2)
13 IS
14 l_error_msg VARCHAR2(2048);
15 BEGIN
16
17 IEC_OCS_LOG_PVT.LOG_INTERNAL_PLSQL_ERROR
18 ( 'IEC_REPORTS_PVT'
19 , p_method_name
20 , p_sub_method
21 , p_activity_desc
22 , p_sql_code
23 , p_sql_errm
24 , l_error_msg
25 );
26
27 END Log;
28
29
30 /* Reset record counts for subsets in IEC_G_REP_SUBSET_COUNTS and IEC_G_MKTG_ITEM_CC_TZS
31 Three entry points - Reset_AllRecordCounts
32 - Reset_CampaignRecordCounts
33 - Reset_ListRecordCounts
34 */
35
36 PROCEDURE Calculate_SubsetCounts
37 ( p_schedule_id IN NUMBER
38 , p_list_id IN NUMBER
39 , p_subset_id_col IN SYSTEM.number_tbl_type
40 , x_subset_record_loaded_col IN OUT NOCOPY SYSTEM.number_tbl_type
41 , x_subset_record_called_col IN OUT NOCOPY SYSTEM.number_tbl_type)
42 IS
43 BEGIN
44
45 x_subset_record_loaded_col := SYSTEM.number_tbl_type();
46 x_subset_record_called_col := SYSTEM.number_tbl_type();
47
48 IF p_subset_id_col IS NOT NULL AND p_subset_id_col.COUNT > 0 THEN
49
50 FOR i IN 1..p_subset_id_col.LAST LOOP
51
52 x_subset_record_loaded_col.EXTEND(1);
53 x_subset_record_called_col.EXTEND(1);
54
55 -- RECORDS LOADED
56 BEGIN
57 EXECUTE IMMEDIATE
58 'SELECT COUNT(*)
59 FROM IEC_G_RETURN_ENTRIES
60 WHERE SUBSET_ID = :subset_id'
61 INTO x_subset_record_loaded_col(x_subset_record_loaded_col.LAST)
62 USING p_subset_id_col(i);
63 EXCEPTION
64 WHEN OTHERS THEN
65 Log( 'Generation of number of loaded records for subset ' || p_subset_id_col(i)
66 , 'Calculate_SubsetCounts'
67 , 'GET_RECORDS_LOADED_COUNT'
68 , SQLCODE
69 , SQLERRM
70 );
71 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
72 END;
73
74 -- RECORDS CALLED ONCE
75 BEGIN
76 EXECUTE IMMEDIATE
77 'SELECT NVL(SUM(DECODE(NVL(B.CALL_ATTEMPT, 0), 0, 0, 1)), 0)
78 FROM IEC_G_RETURN_ENTRIES A, IEC_O_RCY_CALL_HISTORIES B
79 WHERE A.RETURNS_ID = B.RETURNS_ID
80 AND A.SUBSET_ID = :subset_id'
81 INTO x_subset_record_called_col(x_subset_record_called_col.LAST)
82 USING p_subset_id_col(i);
83
84 EXCEPTION
85 WHEN OTHERS THEN
86 Log( 'Generation of number of records called once for subset ' || p_subset_id_col(i)
87 , 'Calculate_SubsetCounts'
88 , 'GET_RECORDS_CALLED_ONCE_COUNT'
89 , SQLCODE
90 , SQLERRM
91 );
92 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
93 END;
94
95 END LOOP;
96
97 END IF;
98
99 EXCEPTION
100 WHEN FND_API.G_EXC_ERROR THEN
101 RAISE;
102 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
103 RAISE;
104 WHEN OTHERS THEN
105 Log( 'Reset of report counts for subsets belonging to list ' || p_list_id
106 , 'Calculate_SubsetCounts'
107 , 'MAIN'
108 , SQLCODE
109 , SQLERRM
110 );
111 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
112
113 END Calculate_SubsetCounts;
114
115 PROCEDURE Update_SubsetCounts
116 ( p_schedule_id IN NUMBER
117 , p_list_id IN NUMBER
118 , p_subset_id_col IN SYSTEM.number_tbl_type
119 , p_subset_record_loaded_col IN SYSTEM.number_tbl_type
120 , p_subset_record_called_col IN SYSTEM.number_tbl_type)
121
122 IS
123 BEGIN
124
125 EXECUTE IMMEDIATE
126 'DELETE FROM IEC_G_REP_SUBSET_COUNTS
127 WHERE LIST_HEADER_ID = :list_id'
128 USING p_list_id;
129
130 IF p_subset_id_col IS NOT NULL AND p_subset_id_col.COUNT > 0 THEN
131
132 FORALL i IN 1..p_subset_id_col.LAST
133 INSERT INTO IEC_G_REP_SUBSET_COUNTS
134 ( SUBSET_COUNT_ID
135 , SCHEDULE_ID
136 , LIST_HEADER_ID
137 , SUBSET_ID
138 , RECORD_LOADED
139 , RECORD_CALLED_ONCE
140 , RECORD_CALLED_AND_REMOVED
141 , RECORD_CALLED_AND_REMOVED_COPY
142 , LAST_COPY_TIME
143 , CREATED_BY
144 , CREATION_DATE
145 , LAST_UPDATE_LOGIN
146 , LAST_UPDATE_DATE
147 , LAST_UPDATED_BY
148 , OBJECT_VERSION_NUMBER
149 )
150 VALUES
151 (IEC_G_REP_SUBSET_COUNTS_S.NEXTVAL
152 , p_schedule_id
153 , p_list_id
154 , p_subset_id_col(i)
155 , p_subset_record_loaded_col(i)
156 , p_subset_record_called_col(i)
157 , 0
158 , 0
159 , SYSDATE
160 , 1
161 , SYSDATE
162 , 1
163 , SYSDATE
164 , 0
165 , 0);
166
167 END IF;
168
169 EXCEPTION
170 WHEN OTHERS THEN
171 Log( 'Reset of report counts in IEC_G_REP_SUBSET_COUNTS for subsets belonging to list ' || p_list_id
172 , 'Update_SubsetCounts'
173 , 'MAIN'
174 , SQLCODE
175 , SQLERRM
176 );
177 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
178
179 END Update_SubsetCounts;
180
181 PROCEDURE Reset_ListRecordCounts
182 ( p_schedule_id IN NUMBER
183 , p_list_id IN NUMBER
184 , p_source_id IN NUMBER
185 , x_return_code IN OUT NOCOPY VARCHAR2)
186 IS
187
188 l_subset_id_col SYSTEM.number_tbl_type := SYSTEM.number_tbl_type();
189 l_subset_record_loaded_col SYSTEM.number_tbl_type := SYSTEM.number_tbl_type();
190 l_subset_record_called_col SYSTEM.number_tbl_type := SYSTEM.number_tbl_type();
191
192 l_cc_tz_id_col SYSTEM.number_tbl_type := SYSTEM.number_tbl_type();
193 l_cc_tz_count_col SYSTEM.number_tbl_type := SYSTEM.number_tbl_type();
194
195 BEGIN
196
197 x_return_code := 'S';
198 g_source_id := p_source_id;
199
200 SAVEPOINT reset_counts_list;
201
202 BEGIN
203 EXECUTE IMMEDIATE
204 'BEGIN
205 SELECT LIST_SUBSET_ID
206 BULK COLLECT INTO :l_subset_id_col
207 FROM IEC_G_LIST_SUBSETS
208 WHERE LIST_HEADER_ID = :list_id
209 AND STATUS_CODE = ''ACTIVE'';
210 END;'
211 USING OUT l_subset_id_col
212 , p_list_id;
213
214 EXCEPTION
215 WHEN OTHERS THEN
216 Log( 'Retrieval of subsets for list ' || p_list_id
217 , 'Reset_ListRecordCounts'
218 , 'GET_LIST_SUBSETS'
219 , SQLCODE
220 , SQLERRM
221 );
222 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
223 END;
224
225 Calculate_SubsetCounts( p_schedule_id
226 , p_list_id
227 , l_subset_id_col
228 , l_subset_record_loaded_col
229 , l_subset_record_called_col);
230
231 Update_SubsetCounts( p_schedule_id
232 , p_list_id
233 , l_subset_id_col
234 , l_subset_record_loaded_col
235 , l_subset_record_called_col);
236
237 --Update Record Counts by CC_TZ_ID in IEC_G_MKTG_ITEM_CC_TZS (Used to derive available and unavailable record counts)
238 BEGIN
239
240 EXECUTE IMMEDIATE
241 'BEGIN
242 SELECT ITM_CC_TZ_ID, COUNT(*)
243 BULK COLLECT INTO :cc_tz_id_col, :cc_tz_count_col
244 FROM IEC_G_RETURN_ENTRIES
245 WHERE LIST_HEADER_ID = :list_id
246 AND DO_NOT_USE_FLAG = ''N''
247 GROUP BY ITM_CC_TZ_ID;
248 END;'
249 USING OUT l_cc_tz_id_col
250 , OUT l_cc_tz_count_col
251 , p_list_id;
252
253 EXECUTE IMMEDIATE
254 'UPDATE IEC_G_MKTG_ITEM_CC_TZS
255 SET RECORD_COUNT = 0
256 , LAST_UPDATE_DATE = SYSDATE
257 WHERE LIST_HEADER_ID = :list_id'
258 USING p_list_id;
259
260 IF l_cc_tz_id_col IS NOT NULL AND l_cc_tz_id_col.COUNT > 0 THEN
261 FORALL i IN 1..l_cc_tz_id_col.LAST
262 UPDATE IEC_G_MKTG_ITEM_CC_TZS
263 SET RECORD_COUNT = l_cc_tz_count_col(i)
264 , LAST_UPDATE_DATE = SYSDATE
265 WHERE ITM_CC_TZ_ID = l_cc_tz_id_col(i);
266
267 END IF;
268
269 EXCEPTION
270 WHEN OTHERS THEN
271 Log( 'Reset of record counts in IEC_G_MKTG_ITEM_CC_TZS for list ' || p_list_id
272 , 'Reset_ListRecordCounts'
273 , 'UPDATE_CALLABLE_ZONE_COUNTS'
274 , SQLCODE
275 , SQLERRM
276 );
277 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
278 END;
279
280 COMMIT;
281
282 EXCEPTION
283 WHEN FND_API.G_EXC_ERROR THEN
284 x_return_code := 'E';
285 ROLLBACK TO reset_counts_list;
286 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
287 x_return_code := 'U';
288 ROLLBACK TO reset_counts_list;
289 WHEN OTHERS THEN
290 Log( 'Reset of record counts for list' || p_list_id
291 , 'Reset_ListRecordCounts'
292 , 'MAIN'
293 , SQLCODE
294 , SQLERRM
295 );
296 x_return_code := 'E';
297 ROLLBACK TO reset_counts_list;
298
299 END Reset_ListRecordCounts;
300
301 PROCEDURE Reset_CampaignRecordCounts
302 ( p_schedule_id IN NUMBER
303 , p_source_id IN NUMBER
304 , x_return_code IN OUT NOCOPY VARCHAR2)
305 IS
306
307 l_list_id_col SYSTEM.number_tbl_type := SYSTEM.number_tbl_type();
308 l_return_code VARCHAR2(1);
309
310 BEGIN
311
312 x_return_code := 'S';
313 g_source_id := p_source_id;
314
315 SAVEPOINT reset_counts_campaign;
316
317 BEGIN
318 EXECUTE IMMEDIATE
319 'BEGIN
320 SELECT LIST_HEADER_ID
321 BULK COLLECT INTO :list_id_col
322 FROM AMS_ACT_LISTS
323 WHERE LIST_USED_BY_ID = :schedule_id
324 AND LIST_ACT_TYPE = ''TARGET''
325 AND LIST_USED_BY = ''CSCH'';
326 END;'
327 USING OUT l_list_id_col
328 , p_schedule_id;
329 EXCEPTION
330 WHEN OTHERS THEN
331 Log( 'Retrieval of lists for campaign schedule ' || p_schedule_id
332 , 'Reset_CampaignRecordCounts'
333 , 'GET_SCHEDULE_LISTS'
334 , SQLCODE
335 , SQLERRM
336 );
337 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
338 END;
339
340 IF l_list_id_col IS NOT NULL AND l_list_id_col.COUNT > 0 THEN
341
342 FOR i IN 1..l_list_id_col.LAST LOOP
343
344 Reset_ListRecordCounts ( p_schedule_id
345 , l_list_id_col(i)
346 , p_source_id
347 , l_return_code);
348
349 IF l_return_code <> FND_API.G_RET_STS_SUCCESS THEN
350 RAISE fnd_api.g_exc_unexpected_error;
351 END IF;
352
353 END LOOP;
354
355 END IF;
356
357 COMMIT;
358
359 EXCEPTION
360 WHEN FND_API.G_EXC_ERROR THEN
361 x_return_code := 'E';
362 ROLLBACK TO reset_counts_campaign;
363 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
364 x_return_code := 'U';
365 ROLLBACK TO reset_counts_campaign;
366 WHEN OTHERS THEN
367 Log( 'Reset of record counts for campaign schedule ' || p_schedule_id
368 , 'Reset_CampaignRecordCounts'
369 , 'MAIN'
370 , SQLCODE
371 , SQLERRM
372 );
373 x_return_code := 'E';
374 ROLLBACK TO reset_counts_campaign;
375
376 END Reset_CampaignRecordCounts;
377
378 PROCEDURE Reset_AllRecordCounts
379 ( p_source_id IN NUMBER
380 , x_return_code IN OUT NOCOPY VARCHAR2)
381 IS
382
383 l_schedule_id_col SYSTEM.number_tbl_type := SYSTEM.number_tbl_type();
384 l_return_code VARCHAR2(1);
385
386 BEGIN
387
388 x_return_code := 'S';
389 g_source_id := p_source_id;
390
391 SAVEPOINT reset_counts_all;
392
393 BEGIN
394 EXECUTE IMMEDIATE
395 'BEGIN
396 SELECT SCHEDULE_ID
397 BULK COLLECT INTO :schedule_id_col
398 FROM IEC_G_EXECUTING_SCHEDULES_V;
399 END;'
400 USING OUT l_schedule_id_col;
401 EXCEPTION
402 WHEN OTHERS THEN
403 Log( 'Retrieval of all executing campaign schedules'
404 , 'Reset_AllRecordCounts'
405 , 'GET_EXECUTING_SCHEDULES'
406 , SQLCODE
407 , SQLERRM
408 );
409 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
410 END;
411
412 IF l_schedule_id_col IS NOT NULL AND l_schedule_id_col.COUNT > 0 THEN
413
414 FOR i IN 1..l_schedule_id_col.LAST LOOP
415
416 Reset_CampaignRecordCounts ( l_schedule_id_col(i)
417 , p_source_id
418 , l_return_code);
419
420 IF l_return_code <> FND_API.G_RET_STS_SUCCESS THEN
421 RAISE fnd_api.g_exc_unexpected_error;
422 END IF;
423
424 END LOOP;
425
426 END IF;
427
428 COMMIT;
429
430 EXCEPTION
431 WHEN FND_API.G_EXC_ERROR THEN
432 x_return_code := 'E';
433 ROLLBACK TO reset_counts_all;
434 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
435 x_return_code := 'U';
436 ROLLBACK TO reset_counts_all;
437 WHEN OTHERS THEN
438 Log( 'Reset of record counts for all campaign schedules'
439 , 'Reset_AllRecordCounts'
440 , 'MAIN'
441 , SQLCODE
442 , SQLERRM
443 );
444 x_return_code := 'E';
445 ROLLBACK TO reset_counts_all;
446
447 END Reset_AllRecordCounts;
448
449 END IEC_REPORTS_PVT;