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.BIX_POP_AO_SUM_PKG

Source


1 PACKAGE BODY BIX_POP_AO_SUM_PKG AS
2 /* $Header: bixxaosb.plb 115.4 2003/01/09 20:20:20 achanda noship $ */
3 
4   g_request_id                  NUMBER := FND_GLOBAL.CONC_REQUEST_ID();
5   g_program_appl_id             NUMBER := FND_GLOBAL.PROG_APPL_ID();
6   g_program_id                  NUMBER := FND_GLOBAL.CONC_PROGRAM_ID();
7   g_user_id                     NUMBER := FND_GLOBAL.USER_ID();
8   g_program_start_date          DATE := SYSDATE;
9   g_collect_start_date          DATE ;
10   g_collect_end_date            DATE ;
11   g_error_mesg                  VARCHAR2(4000)  := NULL;
12   g_status                      VARCHAR2(50)  := 'FAILURE';
13   g_commit_chunk_size           NUMBER := 100;
14   g_ao_installed                CHAR;
15   g_delete_count                NUMBER := 0;
16   g_insert_count                NUMBER := 0;
17 
18 /*===================================================================================================+
19 | INSERT_LOG procedure inserts collection concurrent program status into BIX_DM_COLLECT_LOG table     |
20 | It inserts a row with the following details :                                                       |
21 |                                                                                                     |
22 | COLLECT_STATUS column equals to  FAILURE if the program failed otherwise SUCCESS                    |
23 | COLLECT_EXCEP_MESG as error message if the program failed otherwise NULL                            |
24 | RUN_START_DATE equals to start date time when  the collection program started runnning              |
25 | RUN_END_DATE  equals  end date time of the collection program finished                              |
26 | COLLECT_START_DATE Collection start date specified by the user in the cuncurrent program parameter  |
27 | COLLECT_END_DATE Collection end date specified by the user in the cuncurrent program parameter      |
28 ====================================================================================================+*/
29 
30 PROCEDURE INSERT_LOG
31 AS
32 BEGIN
33       fnd_file.put_line(fnd_file.log,TO_CHAR(SYSDATE,'DD-MON-YYYY HH24:MI:SS')||
34                          ' BIX_POP_AO_SUM_PKG.INSERT_LOG:' ||
35                          'Start inserting collection status into BIX_DM_COLLECT_LOG table');
36 
37 /* Insert status into log table */
38 
39      INSERT INTO BIX_DM_COLLECT_LOG
40         (
41         COLLECT_ID,
42         COLLECT_CONCUR_ID,
43         OBJECT_NAME,
44         OBJECT_TYPE,
45         RUN_START_DATE,
46         RUN_END_DATE,
47         COLLECT_START_DATE,
48         COLLECT_END_DATE,
49         COLLECT_STATUS,
50         COLLECT_EXCEP_MESG,
51         LAST_UPDATE_DATE,
52         LAST_UPDATED_BY,
53         CREATION_DATE,
54         CREATED_BY,
55         LAST_UPDATE_LOGIN,
56         ROWS_INSERTED,
57         ROWS_DELETED,
58         REQUEST_ID,
59         PROGRAM_APPLICATION_ID,
60         PROGRAM_ID,
61         PROGRAM_UPDATE_DATE
62         )
63         VALUES
64         (
65         BIX_DM_COLLECT_LOG_S.NEXTVAL,
66         NULL,
67         'BIX_DM_ADVANCED_OUTBOUND_SUM',
68         'TABLE',
69         g_program_start_date,
70         SYSDATE,
71         g_collect_start_date,
72         g_collect_end_date,
73         g_status,
74         g_error_mesg,
75         SYSDATE,
76         g_user_id,
77         SYSDATE,
78         g_user_id,
79         g_user_id,
80         g_insert_count,
81         g_delete_count,
82         g_request_id,
83         g_program_appl_id,
84         g_program_id,
85         SYSDATE
86         );
87  COMMIT;
88 
89   fnd_file.put_line(fnd_file.log,TO_CHAR(SYSDATE,'DD-MON-YYYY HH24:MI:SS')||
90                      ' BIX_POP_AO_SUM_PKG.INSERT_LOG:'||
91                      'Finished inserting collection status into BIX_DM_COLLECT_LOG table');
92   EXCEPTION
93   WHEN OTHERS THEN
94    fnd_file.put_line(fnd_file.log, TO_CHAR(SYSDATE,'DD-MON-YYYY HH24:MI:SS')||
95                       'BIX_POP_AO_SUM_PKG.INSERT_LOG:  ' ||
96                       ' Failed to insert rows into BIX_DM_COLLECT_LOG table: '|| sqlerrm);
97   RAISE;
98   END INSERT_LOG;
99 
100 /*===================================================================================================+
101 | CLEAN_UP procedure writes error message into FND log file,Rollback the data written into AO        |
102 | summary tables and also calls INSERT_LOG procedure to log error messge in BIX_DM_COLLECT_LOG table |
103 +===================================================================================================*/
104 
105 PROCEDURE clean_up IS
106 	BEGIN
107 	fnd_file.put_line(fnd_file.log,TO_CHAR(SYSDATE,'DD-MON-YYYY HH24:MI:SS')||g_error_mesg);
108         fnd_file.put_line(fnd_file.log,TO_CHAR(SYSDATE,'DD-MON-YYYY HH24:MI:SS')||
109                           'Start rolling back from BIX_DM_ADVANCED_OUTBOUND_SUMtable ');
110 
111 /* Delete from AO summary table */
112 
113   LOOP
114 	DELETE bix_dm_advanced_outbound_sum
115 	WHERE  last_update_date > g_program_start_date
116         AND  rownum <= g_commit_chunk_size ;
117 
118      IF(SQL%ROWCOUNT < g_commit_chunk_size) THEN
119         COMMIT;
120         EXIT;
121      ELSE
122 	   COMMIT;
123      END IF;
124 
125  END LOOP;
126 
127     fnd_file.put_line(fnd_file.log,TO_CHAR(SYSDATE,'DD-MON-YYYY HH24:MI:SS')||
128                  	    ' BIX_POP_AO_SUM_PKG.CLEAN_UP: '||
129 			    ' Finished  rollback from BIM_DM_AO_SUM table: ' );
130 
131    INSERT_LOG;
132 
133 	EXCEPTION
134 	WHEN OTHERS THEN
135 	fnd_file.put_line(fnd_file.log,TO_CHAR(SYSDATE,'DD-MON-YYYY HH24:MI:SS')||
136 			   'CLEAN_UP:  ERROR: '||  SQLERRM);
137 	RAISE;
138 END CLEAN_UP;
139 
140 
141 PROCEDURE populate(p_start_date_time in date, p_end_date_time in date) AS
142 
143    CURSOR ao_sum IS
144     SELECT
145        campaign_schedule_id,
146        source_list_id ,
147        sublist_id,
148        period_start_date,
149        period_start_time,
150        period_start_date_time,
151        COUNT(DISTINCT resource_id) agent_work_on,
152        SUM(DECODE(row_type,'C',1,0)) DIALED,
153        SUM(DECODE(outcome_id,7,1,0)) connected,
154        SUM(DECODE(outcome_id,11,1,0)) abandoned,
155        SUM(DECODE(outcome_id,2,1,0)) busy,
156        SUM(DECODE(outcome_id,1,1,0)) ring_no_ansewr,
157        SUM(DECODE(outcome_id,6,1,0)) answering_machine,
158        SUM(DECODE(outcome_id,22,1,23,1,24,1,25,1,0)) sit,
159        SUM(out_talk_time) talk_time,
160        SUM(out_wrap_time) wrap_time,
161        SUM(leads_created) leads_created,
162        SUM(LEADS_AMOUNT) leads_amount,
163        SUM(LEADS_AMOUNT_TXN) leads_amount_txn,
164        SUM(OPPORTUNITIES_WON + OPPORTUNITIES_CROSS_SOLD + OPPORTUNITIES_UP_SOLD) OPPORTUNITIES_WON,
165        SUM(OPPORTUNITIES_WON_AMOUNT) OPPORTUNITIES_WON_AMOUNT,
166        SUM(OPPORTUNITIES_WON_AMOUNT_TXN) OPPORTUNITIES_WON_AMOUNT_TXN
167     FROM   BIX_DM_INTERFACE
168     WHERE  direction = 1    --OUTBOUND
169     AND    period_start_date_time BETWEEN  p_start_date_time AND p_end_date_time
170     GROUP BY
171        campaign_schedule_id,
172        source_list_id ,
173        sublist_id,
174        period_start_date,
175        period_start_time,
176        period_start_date_time;
177 
178   TYPE campaign_schedule_id IS TABLE OF bix_dm_advanced_outbound_sum.campaign_schedule_id%TYPE;
179   TYPE source_list_id IS TABLE OF bix_dm_advanced_outbound_sum.source_list_id%TYPE;
180   TYPE sublist_id  IS TABLE OF bix_dm_advanced_outbound_sum.sublist_id%TYPE;
181   TYPE period_start_date IS TABLE OF bix_dm_advanced_outbound_sum.period_start_date%TYPE;
182   TYPE period_start_time IS TABLE OF bix_dm_advanced_outbound_sum.period_start_time%TYPE;
183   TYPE period_start_date_time IS TABLE OF bix_dm_advanced_outbound_sum.period_start_date_time%TYPE;
184   TYPE agent_work_on IS TABLE OF bix_dm_advanced_outbound_sum.agent_work_on%TYPE;
185   TYPE talk_time IS TABLE OF bix_dm_advanced_outbound_sum.talk_time%TYPE;
186   TYPE wrap_time IS TABLE OF bix_dm_advanced_outbound_sum.wrap_time%TYPE;
187   TYPE leads_created IS TABLE OF bix_dm_advanced_outbound_sum.leads_created%TYPE;
188   TYPE leads_amount IS TABLE OF bix_dm_advanced_outbound_sum.leads_amount%TYPE;
189   TYPE leads_amount_txn IS TABLE OF bix_dm_advanced_outbound_sum.leads_amount_txn%TYPE;
190   TYPE opportunities_won IS TABLE OF bix_dm_advanced_outbound_sum.opportunities_won%TYPE;
191   TYPE opportunities_won_amount IS TABLE OF bix_dm_advanced_outbound_sum.opportunities_won_amount%TYPE;
192   TYPE opportunities_won_amount_txn IS TABLE OF bix_dm_advanced_outbound_sum.opportunities_won_amount_txn%TYPE;
193   TYPE dialed IS TABLE OF bix_dm_advanced_outbound_sum.dialed_count%TYPE;
194   TYPE connected IS TABLE OF bix_dm_advanced_outbound_sum.connected_count%TYPE;
195   TYPE abandoned IS TABLE OF bix_dm_advanced_outbound_sum.abandoned_count%TYPE;
196   TYPE busy IS TABLE OF bix_dm_advanced_outbound_sum.busy_count%TYPE;
197   TYPE ring_no_answer IS TABLE OF bix_dm_advanced_outbound_sum.ring_no_answer_count%TYPE;
198   TYPE answering_machine IS TABLE OF bix_dm_advanced_outbound_sum.answering_machine_count%TYPE;
199   TYPE sit IS TABLE OF bix_dm_advanced_outbound_sum.sit_count%TYPE;
200 
201 
202   l_campaign_schedule_id campaign_schedule_id;
203   l_source_list_id source_list_id;
204   l_sublist_id sublist_id;
205   l_period_start_date period_start_date;
206   l_period_start_time period_start_time;
207   l_period_start_date_time period_start_date_time;
208   l_agent_work_on agent_work_on;
209   l_talk_time talk_time;
210   l_wrap_time wrap_time;
211   l_leads_created leads_created;
212   l_leads_amount leads_amount;
213   l_leads_amount_txn leads_amount_txn;
214   l_opportunities_won opportunities_won;
215   l_opportunities_won_amount opportunities_won_amount;
216   l_opportunities_won_amount_txn opportunities_won_amount_txn;
217   l_dialed dialed;
218   l_connected  connected;
219   l_abandoned abandoned;
220   l_busy busy;
221   l_ring_no_answer ring_no_answer;
222   l_answering_machine answering_machine;
223   l_sit sit;
224 
225 BEGIN
226 
227   /* get the commit chunk size */
228 
229     IF (FND_PROFILE.DEFINED('BIX_DM_DELETE_SIZE')) THEN
230       g_commit_chunk_size := TO_NUMBER(FND_PROFILE.VALUE('BIX_DM_DELETE_SIZE'));
231     ELSE
232       g_commit_chunk_size := 100;
233     END IF;
234 
235   g_collect_start_date := p_start_date_time;
236   g_collect_end_date  := p_end_date_time;
237 
238      fnd_file.put_line(fnd_file.log,TO_CHAR(SYSDATE,'DD-MON-YYYY HH24:MI:SS')||
239                                    ' BIX_POP_AO_SUM_PKG.POPULATE: '||
240                                    ' Start  Deleting rows in BIM_DM_AO_SUM table: ' );
241  LOOP
242        --dbms_output.put_line('Start deleting................');
243         DELETE bix_dm_advanced_outbound_sum
244         WHERE PERIOD_START_DATE_TIME >= p_start_date_time
245         AND   PERIOD_START_DATE_TIME <= p_end_date_time
246         AND  rownum <= g_commit_chunk_size ;
247 
248      IF(SQL%ROWCOUNT < g_commit_chunk_size) THEN
249         g_delete_count := g_delete_count + SQL%ROWCOUNT;
250         COMMIT;
251         EXIT;
252      ELSE
253            g_delete_count := g_delete_count + SQL%ROWCOUNT;
254            COMMIT;
255      END IF;
256  END LOOP;
257 
258      fnd_file.put_line(fnd_file.log,TO_CHAR(SYSDATE,'DD-MON-YYYY HH24:MI:SS')||
259                                    ' BIX_POP_AO_SUM_PKG.POPULATE: '||
260                                    ' Finished  Deleting rows in BIM_DM_AO_SUM table: '||
261                                    'Row count: ' || g_delete_count );
262  OPEN ao_sum;
263 
264      fnd_file.put_line(fnd_file.log,TO_CHAR(SYSDATE,'DD-MON-YYYY HH24:MI:SS')||
265                                    ' BIX_POP_AO_SUM_PKG.POPULATE: '||
266                                    ' Start  inserting rows in BIM_DM_AO_SUM table: ' );
267 
268  LOOP
269 
270   FETCH ao_sum  BULK COLLECT INTO
271   l_campaign_schedule_id,
272   l_source_list_id,
273   l_sublist_id,
274   l_period_start_date,
275   l_period_start_time,
276   l_period_start_date_time,
277   l_agent_work_on,
278   l_dialed,
279   l_connected,
280   l_abandoned,
281   l_busy,
282   l_ring_no_answer,
283   l_answering_machine,
284   l_sit,
285   l_talk_time,
286   l_wrap_time,
287   l_leads_created,
288   l_leads_amount,
289   l_leads_amount_txn,
290   l_opportunities_won,
291   l_opportunities_won_amount,
292   l_opportunities_won_amount_txn
293   LIMIT g_commit_chunk_size;
294 
295  --dbms_output.put_line('number of rows: ' || l_campaign_schedule_id.COUNT);
296 
297  IF( l_campaign_schedule_id.COUNT > 0) THEN
298  FORALL i IN l_campaign_schedule_id.FIRST .. l_campaign_schedule_id.LAST
299 
300 	INSERT INTO  BIX_DM_ADVANCED_OUTBOUND_SUM
301 	(
302 	 ADVANCED_OUTBOUND_ID,
303 	 CAMPAIGN_SCHEDULE_ID,
304 	 SOURCE_LIST_ID,
305 	 SUBLIST_ID,
306 	 PERIOD_START_DATE,
307 	 PERIOD_START_TIME,
308 	 PERIOD_START_DATE_TIME,
309 	 LAST_UPDATE_DATE,
310 	 LAST_UPDATED_BY,
311 	 CREATION_DATE,
312 	 CREATED_BY,
313 	 AGENT_WORK_ON,
314 	 DIALED_COUNT,
315 	 CONNECTED_COUNT,
316 	 ABANDONED_COUNT,
317 	 BUSY_COUNT,
318 	 RING_NO_ANSWER_COUNT,
319 	 ANSWERING_MACHINE_COUNT,
320 	 SIT_COUNT,
321 	 TALK_TIME,
322 	 WRAP_TIME,
323 	 LEADS_CREATED,
324 	 LEADS_AMOUNT,
325 	 LEADS_AMOUNT_TXN,
326 	 OPPORTUNITIES_WON,
327 	 OPPORTUNITIES_WON_AMOUNT,
328 	 OPPORTUNITIES_WON_AMOUNT_TXN,
329 	 REQUEST_ID,
330 	 PROGRAM_APPLICATION_ID,
331 	 PROGRAM_ID,
332 	 PROGRAM_UPDATE_DATE
333 	 )
334 	VALUES (
335 	bix_dm_advanced_outbound_sum_s.NEXTVAL,
336 	l_campaign_schedule_id(i),
337 	l_source_list_id(i),
338 	l_sublist_id(i),
339 	l_period_start_date(i),
340 	l_period_start_time(i),
341 	l_period_start_date_time(i),
342 	SYSDATE,
343 	g_user_id ,
344 	SYSDATE,
345 	g_user_id,
346 	l_agent_work_on(i),
347 	l_dialed(i),
348 	l_connected(i),
349 	l_abandoned(i),
350 	l_busy(i),
351 	l_ring_no_answer(i),
352 	l_answering_machine(i),
353 	l_sit(i),
354 	l_talk_time(i),
355 	l_wrap_time(i),
356 	l_leads_created(i),
357 	l_leads_amount(i),
358 	l_leads_amount_txn(i),
359 	l_opportunities_won(i),
360 	l_opportunities_won_amount(i),
361 	l_opportunities_won_amount_txn(i),
362 	g_request_id,
363 	g_program_appl_id,
364 	g_program_id,
365 	SYSDATE
366 	);
367 
368      g_insert_count := g_insert_count + l_campaign_schedule_id.COUNT;
369  END IF;
370 
371      EXIT WHEN ao_sum%NOTFOUND;
372 
373    END LOOP;
374      fnd_file.put_line(fnd_file.log,TO_CHAR(SYSDATE,'DD-MON-YYYY HH24:MI:SS')||
375                                    ' BIX_POP_AO_SUM_PKG.POPULATE: '||
376                                    ' finished inserting rows in BIM_DM_AO_SUM table: ' );
377  g_status := 'SUCCESS';
378  insert_log;
379 
380  EXCEPTION
381    WHEN OTHERS THEN
382       g_error_mesg :=  'BIX_POP_AO_SUM_PKG.POPULATE:  ERROR: '|| sqlerrm;
383       g_status := 'FAILURE';
384       clean_up;
385 END populate;
386 
387 END BIX_POP_AO_SUM_PKG;