eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.
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;