[Home] [Help]
PACKAGE BODY: APPS.JTF_IH_IMPORT
Source
1 PACKAGE BODY JTF_IH_IMPORT AS
2 /* $Header: JTFIHIMB.pls 115.50 2004/07/28 12:35:11 nchouras ship $ */
3
4 G_PKG_NAME CONSTANT VARCHAR2(30) := 'JTF_IH_IMPORT';
5 -- Program History
6 -- 08/01/01 - Added one more status for Status_Fl: 0 - The Record is not tested or tested or imported with errors
7 -- 1 - The Record was tested sucesseful
8 -- 2 - The Record was imported
9 -- 08/02/01 - Added Close_Interaction.
10 -- 05/16/02 - Removed Security_Group_Id from Insert statements for all LOG tables.
11 -- 06/05/02 - Removed Resource_ID from Activity_Rec_Type
12 -- 08/05/02 - Fixed bug 2488553 - INTERACTION HISTORY DATA IMPORT DID NOT IMPORT
13 -- ANY DATA INTO IH TABLES
14 -- 03/10/03 - Fixed bug# 2841568 - TST1159.7:SOME LINES IN THE VIEW LOG FOR
15 -- DATAIMPORT IS NOT PSEUDO TRANSLATED
16 -- 03/07/03 - Enh# 3022511 - Added address column to JTF_IH_MEDIA_ITEMS table
17 -- 09/26/03 - Bug# 3163407 - THE PACKAGE HARD CODES "APPS" SCHEMA NAME WHEN REFERENCING JTF_IH_PUB.
18 -- 12/23/03 - Bug# 3184503 - ICFP-R:F -INTERACTION HISTORY TERMINATED: ORA-01422: EXACT FETCH RETURNS MORE TH
19 -- 05/07/04 - Fixed File.sql.35 issue.
20 --
21
22 nErr_InterCnt NUMBER := 0;
23 nErr_ActivCnt NUMBER := 0;
24 nErr_MediaCnt NUMBER := 0;
25
26 PROCEDURE Insert_Interaction_Log( nInteraction NUMBER, sComments VARCHAR2, nSessionNo in out nocopy NUMBER);
27 PROCEDURE Insert_Activity_Log( nActivityID NUMBER, nInteractionID NUMBER, sComments VARCHAR2, nSessionNo in out nocopy NUMBER);
28 PROCEDURE Insert_Media_Item_Log( nMediaID NUMBER, sComments VARCHAR2, nSessionNo in out nocopy NUMBER);
29 -- Check Duplicated. This procedure checks duplicates for Interacions or Media Items
30 -- Input paramters: sTableType accepted: INTERACTION or MEDIAITEM values
31 -- nId - Id Interaction or Media Item Id
32 -- nSessionNo - session number.
33 -- Returns TRUE (has duplicates) or FALSE.
34 FUNCTION ChkDuplicate(sTableType VARCHAR2, nId NUMBER, nSessionNo in out nocopy NUMBER) RETURN BOOLEAN AS
35 nCount NUMBER := 0;
36 --Perf fix for literal Usage
37 v_Session_no NUMBER;
38 --end Perf fix for literal Usage
39 BEGIN
40 --Perf fix for literal Usage
41 v_Session_no := 0;
42 --end Perf fix for literal Usage
43 --dbms_output.put_line('Check Duplicate for '||sTableType||' '||nId||' '||nvl(nSessionNo,-1));
44 IF sTableType = 'INTERACTION' THEN
45 SELECT COUNT(*) INTO nCount FROM jtf_ih_interactions_stg WHERE interaction_id = nId
46 AND (Session_No = pnSessionNo OR Session_No IS NULL OR Session_No = v_Session_no);
47 ELSIF sTableType = 'MEDIAITEM' THEN
48 SELECT COUNT(*) INTO nCount FROM jtf_ih_media_items_stg WHERE media_id = nId
49 AND (Session_No = pnSessionNo OR Session_No IS NULL OR Session_No = v_Session_no);
50 ELSE
51 RETURN FALSE;
52 END IF;
53 IF nCount > 1 THEN
54 --dbms_output.put_line('Duplicates! For '||sTableType||' '||nId||' '||nvl(nSessionNo,-1));
55 RETURN TRUE;
56 ELSE
57 --dbms_output.put_line('No Diplicates!');
58 RETURN FALSE;
59 END IF;
60 EXCEPTION
61 WHEN OTHERS THEN
62 FND_MESSAGE.SET_NAME('JTF','JTF_IH_IMPORT_ERROR');
63 FND_MESSAGE.SET_TOKEN('ERRORMSG', SQLERRM);
64 FND_FILE.PUT_LINE(FND_FILE.LOG,FND_MESSAGE.GET);
65 --dbms_output.put_line('RETURN FALSE');
66 RETURN FALSE;
67 END;
68
69 PROCEDURE GO_TEST AS
70 nCount NUMBER;
71 --Perf fix for literal Usage
72 v_Status_Fl0 NUMBER;
73 v_Status_Fl1 NUMBER;
74 v_Status_Fl2 NUMBER;
75 v_Session_no NUMBER;
76 --end Perf fix for literal Usage
77 nRowsCompleted NUMBER;
78 l_return_status VARCHAR2(1);
79 CURSOR curMediaItems IS SELECT
80 media_id,
81 source_id,
82 direction,
83 duration,
84 end_date_time,
85 interaction_performed,
86 start_date_time,
87 media_data,
88 source_item_create_date_time,
89 source_item_id,
90 media_item_type,
91 media_item_ref,
92 media_abandon_flag,
93 media_transferred_flag,
94 server_group_id,
95 dnis,
96 ani,
97 classification,
98 '' as bulk_writer_code ,
99 '' as bulk_batch_type,
100 NULL as bulk_batch_id,
101 NULL as bulk_interaction_id,
102 address
103 FROM JTF_IH_MEDIA_ITEMS_STG
104 WHERE
105 (Session_No = pnSessionNo OR Session_No IS NULL OR Session_No = v_Session_no)
106 AND (Status_FL = v_Status_Fl1 OR Status_FL IS NULL);
107
108 sMediaItems_rec JTF_IH_PUB.media_rec_type;
109 sMedia_ID_Stg NUMBER;
110 e_Errors EXCEPTION;
111 l_Message VARCHAR2(2000);
112 e_ErrorInteraction EXCEPTION;
113 e_ErrorMediaItem EXCEPTION;
114 n_Interaction_Id NUMBER;
115 BEGIN
116 --Perf fix for literal Usage
117 v_Status_Fl0 := 0;
118 v_Status_Fl1 := 1;
119 v_Status_Fl2 :=2 ;
120 v_Session_no := 0;
121 --end Perf fix for literal Usage
122 -- dbms_output.put_line(pnSessionNo);
123 SELECT Count(*) INTO nCount from jtf_ih_interactions_stg
124 WHERE (Session_No = pnSessionNo OR Session_No IS NULL OR Session_No = v_Session_no);
125 -- Control Session number;
126 IF nCount = 0 THEN
127 SELECT Count(*) INTO nCount from jtf_ih_media_items_stg
128 WHERE (Session_No = pnSessionNo OR Session_No IS NULL OR Session_No = v_Session_no);
129 IF nCount = 0 THEN
130 RAISE excNoSessionNo;
131 END IF;
132 END IF;
133 -- Get next session number.
134 --
135 -- dbms_output.put_line('Sessuib No:'||to_char(pnSessionNo));
136 SELECT JTF_IH_IMPORT_S1.NEXTVAL into nNxtSessionNo FROM DUAL;
137 if pnSessionNo IS NULL then
138 pnSessionNo := 0;
139 end if;
140 -- dbms_output.put_line('Current number Session is '||to_char(nNxtSessionNo));
141 --
142 -- This counter for counting how many records were completed successeful.
143 --
144 nRowsCompleted := 0;
145 -- Check all records for current sessino which has staus_fl = 0 (uncomplited or has some errors)
146 FOR curInteraction IN (select
147 distinct Interaction_ID FROM jtf_ih_interactions_stg
148 WHERE (Session_No IS NULL OR Session_No = pnSessionNo OR Session_No = v_Session_no )
149 AND (Status_FL IS NULL OR Status_FL = v_Status_Fl0) ORDER BY Interaction_ID) LOOP
150 --
151 BEGIN
152 n_Interaction_Id := curInteraction.Interaction_ID;
153 -- Looking for records in the JTF_IH_ACTIVITIES_STG
154 -- table which are depend with Current Interaction's record
155 -- if not found then leave STATUS_FL = 0.
156
157 -- dbms_output.put_line('Looking for Activities for current Interaction ');
158 IF ChkDuplicate('INTERACTION', curInteraction.Interaction_ID,pnSessionNo ) THEN
159 --dbms_output.put_line('Interaction has Duplicates!');
160 FND_MESSAGE.SET_NAME('JTF','JTF_IH_IMPORT_DUP_INTERACTION');
161 l_Message := FND_MESSAGE.GET;
162 RAISE e_ErrorInteraction;
163 END IF;
164
165 SELECT Count(*) INTO nCount FROM jtf_ih_activities_stg
166 WHERE Interaction_ID = curInteraction.Interaction_ID AND (Session_No = pnSessionNo OR Session_No IS NULL OR Session_No = v_Session_no);
167 IF nCount = 0 THEN
168 -- dbms_output.put_line('Activities were not found for Interaction No: '||curInteraction.Interaction_ID);
169 UPDATE jtf_ih_interactions_stg SET Status_Fl = v_Status_Fl0, Session_Date = SYSDATE, Session_No = nNxtSessionNo
170 WHERE Interaction_ID = curInteraction.Interaction_ID and (Session_No = pnSessionNo OR Session_No IS NULL OR Session_No = v_Session_no);
171 FND_MESSAGE.SET_NAME('JTF','JTF_IH_IMPORT_NO_ACTIVITY');
172 l_Message := FND_MESSAGE.GET;
173 Insert_Interaction_Log(curInteraction.Interaction_ID, l_Message, nNxtSessionNo);
174 ELSE
175 FOR curActivity IN (SELECT Activity_ID, Media_ID FROM jtf_ih_activities_stg WHERE
176 Interaction_ID = curInteraction.Interaction_Id and
177 (Session_No IS NULL OR Session_No = pnSessionNo OR Session_No = v_Session_no)) LOOP
178 IF (curActivity.Media_ID IS NOT NULL) AND (curActivity.Media_ID <> fnd_api.g_miss_num) THEN
179 /* Looking for Media_ID in the JTF_IH_MEDIA_ITEM_STG */
180 SELECT Count(*) INTO nCount FROM jtf_ih_media_items_stg
181 WHERE Media_ID = curActivity.Media_ID AND (Session_No IN (pnSessionNo, v_Session_no) OR Session_No IS NULL);
182 IF nCount = 0 THEN
183 FND_MESSAGE.SET_NAME('JTF','JTF_IH_IMPORT_NO_MEDIAITEM');
184 l_Message := FND_MESSAGE.GET;
185 raise e_ErrorInteraction;
186 END IF;
187 END IF;
188 END LOOP;
189
190 UPDATE jtf_ih_interactions_stg SET Status_Fl = v_Status_Fl1, Session_No = nNxtSessionNo, Session_Date = SYSDATE
191 WHERE Interaction_ID = curInteraction.Interaction_ID and (Session_No = pnSessionNo OR Session_No IS NULL OR Session_No = v_Session_no);
192 UPDATE jtf_ih_activities_stg SET Status_Fl = v_Status_Fl1, Session_No = nNxtSessionNo, Session_Date = SYSDATE
193 WHERE Interaction_ID = curInteraction.Interaction_ID and (Session_No = pnSessionNo OR Session_No IS NULL OR Session_No = v_Session_no);
194 END IF;
195 EXCEPTION
196 WHEN e_ErrorInteraction THEN
197 UPDATE jtf_ih_interactions_stg SET Status_Fl = v_Status_Fl0, Session_Date = SYSDATE, Session_No = nNxtSessionNo
198 WHERE Interaction_ID = curInteraction.Interaction_ID and (Session_No = pnSessionNo OR Session_No IS NULL OR Session_No = v_Session_no);
199 Insert_Interaction_Log(curInteraction.Interaction_ID, l_Message, nNxtSessionNo);
200
201 UPDATE jtf_ih_activities_stg SET Status_Fl = v_Status_Fl0, Session_Date = SYSDATE, Session_No = nNxtSessionNo
202 WHERE Interaction_ID = curInteraction.Interaction_ID and (Session_No = pnSessionNo OR Session_No IS NULL OR Session_No = v_Session_no);
203
204 FOR curErrAct IN (SELECT Activity_ID FROM jtf_ih_activities_stg WHERE interaction_id = n_Interaction_ID AND Session_No = nNxtSessionNo) LOOP
205 Insert_activity_Log(curErrAct.Activity_Id, curInteraction.Interaction_ID, l_Message, nNxtSessionNo);
206 END LOOP;
207 END;
208 -- Row's counter. When nRowsCompleted like nCntTransRows then make COMMIT
209 nRowsCompleted := nRowsCompleted + 1;
210 IF nRowsCompleted = nCntTransRows THEN
211 COMMIT;
212 nRowsCompleted := 0;
213 END IF;
214 END LOOP;
215
216 OPEN curMediaItems;
217 LOOP
218 BEGIN
219 FETCH curMediaItems INTO sMediaItems_rec;
220 IF curMediaItems%NOTFOUND THEN
221 EXIT;
222 END IF;
223 sMedia_ID_Stg := sMediaItems_rec.Media_ID;
224 sMediaItems_rec.Media_ID := NULL;
225 --dbms_output.put_line('Check MediaItem!');
226 IF ChkDuplicate('MEDIAITEM', sMedia_ID_Stg,pnSessionNo ) THEN
227 --dbms_output.put_line('Duplicate!');
228 RAISE e_ErrorMediaItem;
229 END IF;
230 UPDATE jtf_ih_media_items_stg SET Status_Fl = v_Status_Fl1, Session_No = nNxtSessionNo, Session_Date = SYSDATE
231 WHERE Media_ID = sMedia_ID_Stg AND (Session_No = pnSessionNo OR Session_No IS NULL OR Session_No = v_Session_no);
232 EXCEPTION
233 WHEN e_ErrorMediaItem THEN
234 FND_MESSAGE.SET_NAME('JTF','JTF_IH_IMPORT_DUP_MEDIA_ITEM');
235 l_Message := FND_MESSAGE.GET;
236 --dbms_output.put_line('sMedia_ID_Stg = '||sMedia_ID_Stg);
237 UPDATE jtf_ih_media_items_stg SET Status_Fl = v_Status_Fl0, Session_Date = SYSDATE, Session_No = nNxtSessionNo
238 WHERE Media_Id = sMedia_ID_Stg and (Session_No = pnSessionNo OR Session_No IS NULL OR Session_No = v_Session_no);
239 Insert_Media_Item_Log(sMedia_ID_Stg,l_Message, nNxtSessionNo);
240 END;
241 END LOOP;
242 COMMIT;
243 EXCEPTION
244 WHEN excNoSessionNo THEN
245 --dbms_output.put_line('Session number not found');
246 FND_MESSAGE.SET_NAME('JTF','JTF_IH_IMPORT_SESS_ERR');
247 FND_MESSAGE.SET_TOKEN('SESSN', pnSessionNo);
248 FND_FILE.PUT_LINE(FND_FILE.LOG,FND_MESSAGE.GET);
249 --DBMS_OUTPUT.PUT_LINE(FND_MESSAGE.GET);
250 WHEN OTHERS THEN
251 -- dbms_output.put_line(To_Char(SQLCODE)||' : '||SQLERRM);
252 FND_MESSAGE.SET_NAME('JTF','JTF_IH_IMPORT_ERROR');
253 FND_MESSAGE.SET_TOKEN('ERRORMSG', SQLERRM);
254 FND_FILE.PUT_LINE(FND_FILE.LOG,FND_MESSAGE.GET);
255 --DBMS_OUTPUT.PUT_LINE(FND_MESSAGE.GET);
256 END;
257
258 PROCEDURE GO_IMPORT(ERRBUF OUT NOCOPY VARCHAR2, RETCODE OUT NOCOPY VARCHAR2) AS
259 l_return_status VARCHAR2(30);
260 l_msg_count NUMBER;
261 l_msg_data VARCHAR2(2000);
262
263 --Perf fix for literal Usage
264 v_Status_Fl0 NUMBER;
265 v_Status_Fl1 NUMBER;
266 v_Status_Fl2 NUMBER;
267 v_Session_no NUMBER;
268 --end Perf fix for literal Usage
269
270 l_interaction_rec JTF_IH_PUB.interaction_rec_type;
271 l_interaction_id NUMBER;
272 l_jtf_note_id NUMBER;
273 n_Count NUMBER := 0;
274 m_active VARCHAR2(1);
275 p_interaction_id NUMBER;
276
277 --l_activity_rec JTF_IH_PUB.activity_rec_type;
278 l_activity_id_1 NUMBER;
279 l_activity_id_2 NUMBER;
280 m_activitycount NUMBER := 0;
281 m_activityactive VARCHAR2(1);
282 l_startdatecheck VARCHAR2(30);
283 l_enddatecheck VARCHAR2(30);
284 l_data VARCHAR2(2000);
285 l_msg_index_out NUMBER;
286 xInteraction_Count NUMBER;
287 cInteraction_Count VARCHAR2(80);
288 xparty_id NUMBER;
289 cparty_id VARCHAR2(80);
290 xresource_id NUMBER;
291 cresource_id VARCHAR2(80);
292 status NUMBER;
293 end_time_run DATE;
294 begin_time_run DATE;
295 total_time_run NUMBER;
296
297 l_media_item_rec JTF_IH_PUB.media_rec_type;
298 l_media_item_id NUMBER;
299
300 nRowsCompleted NUMBER;
301 nSessionNo NUMBER;
302 bInteractionError BOOLEAN := FALSE;
303
304 CURSOR curInteraction IS SELECT
305 interaction_id,
306 reference_form,
307 follow_up_action,
308 duration,
309 end_date_time,
310 inter_interaction_duration,
311 non_productive_time_amount,
312 preview_time_amount,
313 productive_time_amount,
314 start_date_time,
315 wrap_up_time_amount,
316 handler_id,
317 script_id,
318 outcome_id,
319 result_id,
320 reason_id,
321 resource_id,
322 party_id,
323 NULL,
324 object_id,
325 object_type,
326 source_code_id,
327 source_code,
328 attribute1,
329 attribute2,
330 attribute3,
331 attribute4,
332 attribute5,
333 attribute6,
334 attribute7,
335 attribute8,
336 attribute9,
337 attribute10,
338 attribute11,
339 attribute12,
340 attribute13,
341 attribute14,
342 attribute15,
343 attribute_category,
344 touchpoint1_type,
345 touchpoint2_type,
346 method_code,
347 primary_party_id,
348 contact_rel_party_id,
349 contact_party_id,
350 '' as bulk_writer_code ,
351 '' as bulk_batch_type,
352 NULL as bulk_batch_id,
353 NULL as bulk_interaction_id
354 FROM jtf_ih_interactions_stg WHERE
355 Status_Fl = v_Status_Fl1 AND (Session_No IN (nSessionNo, v_Session_no) OR Session_No IS NULL)
356 ORDER BY interaction_id;
357
358 l_interaction_stg_rec curInteraction%ROWTYPE;
359 l_interaction_stg NUMBER;
360
361 CURSOR curActivity ( nIntrctID NUMBER) IS
362 SELECT activity_id,
363 duration,
364 cust_account_id,
365 cust_org_id,
366 role,
367 end_date_time,
368 start_date_time,
369 task_id,
370 doc_id,
371 doc_ref,
372 doc_source_object_name,
373 media_id,
374 action_item_id,
375 interaction_id,
376 outcome_id,
377 result_id,
378 reason_id,
379 description,
380 action_id,
381 interaction_action_type,
382 object_id,
383 object_type,
384 source_code_id,
385 source_code,
386 script_trans_id,
387 attribute1,
388 attribute2,
389 attribute3,
390 attribute4,
391 attribute5,
392 attribute6,
393 attribute7,
394 attribute8,
395 attribute9,
396 attribute10,
397 attribute11,
398 attribute12,
399 attribute13,
400 attribute14,
401 attribute15,
402 attribute_category,
403 '' as bulk_writer_code ,
404 '' as bulk_batch_type,
405 NULL as bulk_batch_id,
406 NULL as bulk_interaction_id
407 -- Removed by IAleshin 06/05/2002
408 --,resource_id
409 FROM JTF_IH_ACTIVITIES_STG
410 WHERE Status_FL = v_Status_Fl1 AND Interaction_ID = nIntrctID
411 AND (Session_No IN (nSessionNo, v_Session_no) OR Session_No IS NULL);
412 --l_activity_rec curActivity%rowtype;
413 l_activity_rec JTF_IH_PUB.Activity_Rec_Type;
414 l_activity_stg NUMBER;
415 CURSOR curMediaItem IS SELECT
416 media_id,
417 source_id,
418 direction,
419 duration,
420 end_date_time,
421 interaction_performed,
422 start_date_time,
423 media_data,
424 source_item_create_date_time,
425 source_item_id,
426 media_item_type,
427 media_item_ref,
428 media_abandon_flag,
429 media_transferred_flag,
430 server_group_id,
431 dnis,
432 ani,
433 classification,
434 '' as bulk_writer_code ,
435 '' as bulk_batch_type,
436 NULL as bulk_batch_id,
437 NULL as bulk_interaction_id,
438 address
439 FROM JTF_IH_MEDIA_ITEMS_STG
440 WHERE Status_FL = v_Status_Fl1 AND (Session_No IN (nSessionNo,v_Session_no) OR Session_No IS NULL);
441 l_media_item_stg_rec JTF_IH_PUB.Media_Rec_Type;
442 l_media_id_stg NUMBER;
443
444 nCntInter NUMBER := 0;
445 nCntActiv NUMBER := 0;
446 nCntMdaItm NUMBER := 0;
447
448 nTotInter NUMBER := 0;
449 nTotActiv NUMBER := 0;
450 nTotMdaItm NUMBER := 0;
451
452 nActive VARCHAR2(1);
453 l_Message VARCHAR2(2000);
454 e_Errors EXCEPTION;
455 nDupInteraction NUMBER;
456 BEGIN
457
458 --Perf fix for literal Usage
459 v_Status_Fl0 := 0;
460 v_Status_Fl1 := 1;
461 v_Status_Fl2 :=2 ;
462 v_Session_no := 0;
463 --end Perf fix for literal Usage
464
465 nRowsCompleted := 0;
466
467 -- Begin testing of STG's tables
468 --
469 IF bTEST THEN
470 -- dbms_output.put_line('Begin testing ');
471 GO_TEST;
472 END IF;
473 -- dbms_output.put_line('Test is done ');
474 -- dbms_output.put_line('nNxtSessionNo '||nNxtSessionNo);
475 IF (nNxtSessionNo is not null) and (nNxtSessionNo <> 0) THEN
476 nSessionNo := nNxtSessionNo;
477 ELSE
478 IF pnSessionNo IS NULL THEN
479 pnSessionNo := 0;
480 END IF;
481 nSessionNo := pnSessionNo;
482 END IF;
483
484 nErr_InterCnt := 0;
485 nErr_ActivCnt := 0;
486 nErr_MediaCnt := 0;
487
488 -- dbms_output.put_line('nSessionNo - '||nSessionNo);
489 -- dbms_output.put_line('Session Number is '||to_char(nSessionNo));
490 /*Import from JTF_IH_Media_Items_STG*/
491
492 SELECT Count(Interaction_ID) INTO nTotInter FROM jtf_ih_interactions_stg
493 WHERE (Session_No IN (nSessionNo,v_Session_no) OR Session_No IS NULL);
494 SELECT Count(Activity_ID) INTO nTotActiv FROM jtf_ih_activities_stg
495 WHERE (Session_No IN (nSessionNo,v_Session_no) OR Session_No IS NULL);
496 SELECT Count(Media_ID) INTO nTotMdaItm FROM jtf_ih_media_items_stg
497 WHERE (Session_No IN (nSessionNo,v_Session_no) OR Session_No IS NULL);
498
499 OPEN curMediaItem;
500 LOOP
501 BEGIN
502 FETCH curMediaItem INTO l_media_item_stg_rec;
503 l_media_id_stg := l_media_item_stg_rec.media_id;
504 l_media_item_stg_rec.media_id := NULL;
505 IF curMediaItem%NOTFOUND THEN
506 EXIT;
507 END IF;
508 JTF_IH_PUB.Create_MediaItem(1.0,
509 'T',
510 'F',
511 fnd_global.resp_appl_id,
512 fnd_global.resp_id,
513 fnd_global.user_id,
514 fnd_global.login_id,
515 l_return_status,
516 l_msg_count,
517 l_msg_data,
518 l_media_item_stg_rec,
519 l_media_item_id);
520 IF l_return_status <> 'S' THEN
521 RAISE e_Errors;
522 END IF;
523 UPDATE jtf_ih_media_items_stg SET Status_Fl = v_Status_Fl2, Media_ID = l_media_item_id, Session_No = nSessionNo
524 WHERE Media_ID = l_media_id_stg AND (Session_No IN (nSessionNo,v_Session_no) OR Session_No IS NULL);
525 UPDATE jtf_ih_activities_stg SET Media_ID = l_media_item_id
526 WHERE Media_ID = l_media_id_stg AND (Session_No IN (nSessionNo,v_Session_no) OR Session_No IS NULL);
527 COMMIT;
528 nCntMdaItm := nCntMdaItm + 1;
529 EXCEPTION
530 WHEN e_Errors THEN
531 l_msg_data := '';
532 FOR j in 1..FND_MSG_PUB.Count_Msg LOOP
533 l_msg_data := l_msg_data || FND_MSG_PUB.Get(p_msg_index => j,
534 p_encoded=>'F');
535 END LOOP;
536 UPDATE jtf_ih_media_items_stg SET Status_Fl = v_Status_Fl0, Session_Date = SYSDATE, Session_No = nSessionNo
537 WHERE Media_ID = l_media_id_stg AND (Session_No IN (nSessionNo,v_Session_no) OR Session_No IS NULL);
538 --nErr_MediaCnt := nErr_MediaCnt + SQL%ROWCOUNT;
539 Insert_Media_Item_Log(l_media_id_stg, l_msg_data, nSessionNo);
540 -- Add into into Activities_Stg log
541 FOR curErrActive IN (SELECT Activity_ID, Interaction_ID FROM jtf_ih_activities_stg WHERE Interaction_ID = (
542 SELECT Interaction_ID FROM jtf_ih_activities_stg
543 WHERE Media_ID = l_media_id_stg AND (Session_No IN (nSessionNo,v_Session_no)
544 OR Session_No IS NULL))) LOOP
545 UPDATE jtf_ih_interactions_stg SET Status_Fl = v_Status_Fl0, Session_Date = SYSDATE, Session_No = nSessionNo
546 WHERE Interaction_Id = curErrActive.Interaction_ID AND (Session_No IN (nSessionNo,v_Session_no) OR Session_No IS NULL);
547 IF curErrActive.Interaction_ID <> nDupInteraction OR nDupInteraction IS NULL THEN
548 Insert_Interaction_Log(curErrActive.Interaction_ID, l_msg_data, nSessionNo);
549 nDupInteraction := curErrActive.Interaction_ID;
550 END IF;
551 UPDATE jtf_ih_activities_stg SET Status_Fl = v_Status_Fl0, Session_Date = SYSDATE, Session_No = nSessionNo
552 WHERE Activity_ID = curErrActive.Activity_ID AND Interaction_Id = curErrActive.Interaction_ID
553 AND (Session_No IN (nSessionNo,v_Session_no) OR Session_No IS NULL);
554 Insert_Activity_Log(curErrActive.Activity_ID, curErrActive.Interaction_ID, l_msg_data, nSessionNo);
555 --nErr_ActivCnt := nErr_ActivCnt + 1;
556 --nErr_InterCnt := nErr_InterCnt + 1;
557 END LOOP;
558 COMMIT;
559 WHEN OTHERS THEN
560 FND_MESSAGE.SET_NAME('JTF','JTF_IH_IMPORT_ERROR');
561 FND_MESSAGE.SET_TOKEN('ERRORMSG', SQLERRM);
562 FND_FILE.PUT_LINE(FND_FILE.LOG,FND_MESSAGE.GET);
563 END;
564 END LOOP;
565 CLOSE curMediaItem;
566 COMMIT;
567
568 OPEN curInteraction;
569 LOOP
570 BEGIN
571 FETCH curInteraction INTO l_interaction_rec;
572 IF curInteraction%NOTFOUND THEN
573 EXIT;
574 END IF;
575 l_interaction_stg := l_interaction_rec.interaction_id;
576 l_interaction_rec.Interaction_ID := NULL;
577 bInteractionError := FALSE;
578 JTF_IH_PUB.Open_Interaction(1.0,
579 'T',
580 'F',
581 fnd_global.resp_appl_id,
582 fnd_global.resp_id,
583 fnd_global.user_id,
584 fnd_global.login_id,
585 l_return_status,
586 l_msg_count,
587 l_msg_data,
588 l_interaction_rec,
589 l_interaction_id);
590
591 IF l_return_status <> 'S' THEN
592 RAISE e_Errors;
593 END IF;
594
595 OPEN curActivity(l_interaction_stg);
596 LOOP
597 FETCH curActivity INTO l_activity_rec;
598 IF curActivity%NOTFOUND THEN
599 EXIT;
600 END IF;
601 l_activity_stg := l_activity_rec.Activity_ID;
602 l_activity_rec.Activity_ID := NULL;
603 l_activity_rec.Interaction_Id := l_interaction_id;
604 JTF_IH_PUB.Add_Activity(1.0,
605 'T',
606 'F',
607 fnd_global.resp_appl_id,
608 fnd_global.resp_id,
609 fnd_global.user_id,
610 fnd_global.login_id,
611 l_return_status,
612 l_msg_count,
613 l_msg_data,
614 l_activity_rec,
615 l_activity_id_1);
616 IF l_return_status <> 'S' THEN
617 CLOSE curActivity;
618 RAISE e_Errors;
619 END IF;
620 UPDATE jtf_ih_activities_stg SET Activity_ID = l_activity_id_1, Status_Fl = v_Status_Fl2,
621 Session_Date = SYSDATE, Session_No = nSessionNo
622 WHERE Activity_ID = l_activity_stg
623 AND Interaction_ID = l_interaction_stg
624 AND (Session_No IN (nSessionNo,v_Session_no) OR Session_No IS NULL);
625 END LOOP;
626 CLOSE curActivity;
627 SELECT Active INTO nActive FROM jtf_ih_interactions_stg
628 WHERE Interaction_ID = l_interaction_stg
629 AND (Session_No IN (nSessionNo,v_Session_no) OR Session_No IS NULL);
630 IF nActive = 'N' THEN
631 -- dbms_output.put_line('Interaction No '||l_interaction_id||' was closed');
632 l_interaction_rec.Interaction_ID := l_interaction_id;
633 JTF_IH_PUB.Close_Interaction( 1.0,
634 'T',
635 'F',
636 fnd_global.resp_appl_id,
637 fnd_global.resp_id,
638 fnd_global.user_id,
639 fnd_global.login_id,
640 l_return_status,
641 l_msg_count,
642 l_msg_data,
643 l_interaction_rec);
644 IF l_return_status <> 'S' THEN
645 RAISE e_Errors;
646 END IF;
647 END IF;
648 UPDATE jtf_ih_interactions_stg SET Interaction_ID = l_interaction_id, Status_Fl = v_Status_Fl2,
649 Session_Date = SYSDATE, Session_No = nSessionNo
650 WHERE Interaction_ID = l_interaction_stg AND (Session_No IN (nSessionNo,v_Session_no) OR Session_No IS NULL);
651 nCntInter := nCntInter + 1;
652 UPDATE jtf_ih_activities_stg SET Interaction_ID = l_interaction_id, Status_Fl = v_Status_Fl2,
653 Session_Date = SYSDATE, Session_No = nSessionNo
654 WHERE Interaction_ID = l_interaction_stg AND (Session_No IN (nSessionNo,v_Session_no) OR Session_No IS NULL);
655 nCntActiv := nCntActiv + SQL%ROWCOUNT;
656 EXCEPTION
657 WHEN e_Errors THEN
658 l_msg_data := '';
659 FOR j in 1..FND_MSG_PUB.Count_Msg LOOP
660 l_msg_data := l_msg_data || FND_MSG_PUB.Get(p_msg_index => j, p_encoded=>'F');
661 END LOOP;
662 UPDATE jtf_ih_interactions_stg SET Status_Fl = v_Status_Fl0, Session_Date = SYSDATE, Session_No = nSessionNo
663 WHERE Interaction_ID = l_interaction_stg AND (Session_No IN (nSessionNo,v_Session_no) OR Session_No IS NULL);
664 Insert_Interaction_Log(l_interaction_stg, l_msg_data, nSessionNo);
665 nErr_InterCnt := nErr_InterCnt + SQL%ROWCOUNT;
666 UPDATE jtf_ih_activities_stg SET Status_Fl = v_Status_Fl0, Session_Date = SYSDATE, Session_No = nSessionNo
667 WHERE Interaction_ID = l_interaction_stg AND (Session_No IN (nSessionNo,v_Session_no) OR Session_No IS NULL);
668 nErr_ActivCnt := nErr_ActivCnt + SQL%ROWCOUNT;
669 FOR currActivErr IN (SELECT Activity_ID, Interaction_ID FROM jtf_ih_activities_stg WHERE Interaction_ID = l_interaction_stg
670 AND (Session_No IN (nSessionNo,v_Session_no) OR Session_No IS NULL)) LOOP
671 Insert_Activity_Log(currActivErr.Activity_ID,currActivErr.Interaction_ID, l_msg_data, nSessionNo);
672 END LOOP;
673 WHEN OTHERS THEN
674 FND_MESSAGE.SET_NAME('JTF','JTF_IH_IMPORT_ERROR');
675 FND_MESSAGE.SET_TOKEN('ERRORMSG', SQLERRM);
676 FND_FILE.PUT_LINE(FND_FILE.LOG,FND_MESSAGE.GET);
677 END;
678 END LOOP;
679 CLOSE curInteraction;
680 COMMIT;
681
682 FND_MESSAGE.SET_NAME('JTF','JTF_IH_IMPORT_REPORT');
683 FND_MESSAGE.SET_TOKEN('INTERACTIONS', to_char(nCntInter));
684 FND_MESSAGE.SET_TOKEN('ACTIVITIES', to_char(nCntActiv));
685 FND_MESSAGE.SET_TOKEN('MEDIAITEMS', to_char(nCntMdaItm));
686 FND_FILE.PUT_LINE(FND_FILE.LOG,FND_MESSAGE.GET);
687
688 -- If import has any errors then show message about them
689 SELECT COUNT(*) INTO nErr_InterCnt FROM
690 (SELECT interaction_id FROM jtf_ih_interactions_stg_log
691 WHERE session_no = nSessionNo);
692
693 SELECT COUNT(*) INTO nErr_ActivCnt FROM
694 (SELECT activity_id FROM jtf_ih_activities_stg_log
695 WHERE session_no = nSessionNo);
696
697 SELECT COUNT(*) INTO nErr_MediaCnt FROM
698 (SELECT media_id FROM jtf_ih_media_items_stg_log
699 WHERE session_no = nSessionNo);
700 IF (nErr_InterCnt <> 0) OR (nErr_ActivCnt<> 0) OR (nErr_MediaCnt <> 0) THEN
701 FND_MESSAGE.SET_NAME('JTF','JTF_IH_IMPORT_DONE_WITH_ERRORS');
702 FND_MESSAGE.SET_TOKEN('INTERACTIONS', to_char(nErr_InterCnt));
703 FND_MESSAGE.SET_TOKEN('ACTIVITIES', to_char(nErr_ActivCnt));
704 FND_MESSAGE.SET_TOKEN('MEDIAITEMS', to_char(nErr_MediaCnt));
705 FND_FILE.PUT_LINE(FND_FILE.LOG,FND_MESSAGE.GET);
706 END IF;
707 --DBMS_OUTPUT.PUT_LINE(FND_MESSAGE.GET);
708 EXCEPTION
709 WHEN excNoSessionNo THEN
710 -- dbms_output.put_line('Session number not found.');
711 FND_MESSAGE.SET_NAME('JTF','JTF_IH_IMPORT_ERROR');
712 FND_MESSAGE.SET_TOKEN('ERRORMSG', 'Session number not found.');
713 FND_FILE.PUT_LINE(FND_FILE.LOG,FND_MESSAGE.GET);
714 --DBMS_OUTPUT.PUT_LINE(FND_MESSAGE.GET);
715 WHEN NO_DATA_FOUND THEN
716 -- dbms_output.put_line('Data not found.');
717 FND_MESSAGE.SET_NAME('JTF','JTF_IH_IMPORT_ERROR');
718 FND_MESSAGE.SET_TOKEN('ERRORMSG', SQLERRM);
719 FND_FILE.PUT_LINE(FND_FILE.LOG,FND_MESSAGE.GET);
720 --DBMS_OUTPUT.PUT_LINE(FND_MESSAGE.GET);
721 -- If exception occurs in the Test module then rollback
722 -- w/o messages, because they are already there
723 WHEN OTHERS THEN
724 -- dbms_output.put_line(To_Char(SQLCODE)||' : '||SQLERRM);
725 FND_MESSAGE.SET_NAME('JTF','JTF_IH_IMPORT_ERROR');
726 FND_MESSAGE.SET_TOKEN('ERRORMSG', SQLERRM);
727 FND_FILE.PUT_LINE(FND_FILE.LOG,FND_MESSAGE.GET);
728 --DBMS_OUTPUT.PUT_LINE(FND_MESSAGE.GET);
729 END;
730
731
732 -- This function write a row into Interaction Log's table.
733
734 -- Program History
735 -- 05/16/02 - Removed Security_Group_Id from Insert statements for all LOG tables.
736 --
737 PROCEDURE Insert_Interaction_Log( nInteraction NUMBER, sComments VARCHAR2, nSessionNo IN OUT NOCOPY NUMBER) AS
738 l_Comments VARCHAR(2000);
739 BEGIN
740 l_Comments := sComments;
741 INSERT INTO JTF_IH_INTERACTIONS_STG_LOG(
742 interaction_id,
743 object_version_number,
744 creation_date,
745 created_by,
746 last_update_date,
747 last_updated_by,
748 last_update_login,
749 interaction_inters_id,
750 object_id,
751 object_type,
752 source_code,
753 source_code_id,
754 reference_form,
755 duration,
756 end_date_time,
757 follow_up_action,
758 non_productive_time_amount,
759 result_id,
760 reason_id,
761 start_date_time,
762 outcome_id,
763 preview_time_amount,
764 productive_time_amount,
765 handler_id,
766 inter_interaction_duration,
767 wrap_up_time_amount,
768 script_id,
769 party_id,
770 resource_id,
771 method_code,
772 org_id,
773 attribute_category,
774 attribute1,
775 attribute2,
776 attribute3,
777 attribute4,
778 attribute5,
779 attribute6,
780 attribute7,
781 attribute8,
782 attribute9,
783 attribute10,
784 attribute11,
785 attribute12,
786 attribute13,
787 attribute14,
788 attribute15,
789 active,
790 touchpoint1_type,
791 touchpoint2_type,
792 orig_system_reference,
793 orig_system_reference_id,
794 public_flag,
795 upgraded_status_flag,
796 upg_orig_system_ref,
797 upg_orig_system_ref_id,
798 program_id,
799 request_id,
800 program_application_id,
801 program_update_date,
802 error_message,
803 session_no,
804 session_date,
805 primary_party_id,
806 contact_rel_party_id,
807 contact_party_id
808 )
809 SELECT
810 interaction_id,
811 object_version_number,
812 NVL(creation_date, SYSDATE),
813 NVL(created_by, fnd_global.user_id),
814 NVL(last_update_date, SYSDATE),
815 NVL(last_updated_by,fnd_global.user_id),
816 NVL(last_update_login,fnd_global.login_id),
817 interaction_inters_id,
818 object_id,
819 object_type,
820 source_code,
821 source_code_id,
822 reference_form,
823 duration,
824 end_date_time,
825 follow_up_action,
826 non_productive_time_amount,
827 result_id,
828 reason_id,
829 start_date_time,
830 outcome_id,
831 preview_time_amount,
832 productive_time_amount,
833 handler_id,
834 inter_interaction_duration,
835 wrap_up_time_amount,
836 script_id,
837 party_id,
838 resource_id,
839 method_code,
840 org_id,
841 attribute_category,
842 attribute1,
843 attribute2,
844 attribute3,
845 attribute4,
846 attribute5,
847 attribute6,
848 attribute7,
849 attribute8,
850 attribute9,
851 attribute10,
852 attribute11,
853 attribute12,
854 attribute13,
855 attribute14,
856 attribute15,
857 active,
858 touchpoint1_type,
859 touchpoint2_type,
860 orig_system_reference,
861 orig_system_reference_id,
862 public_flag,
863 upgraded_status_flag,
864 upg_orig_system_ref,
865 upg_orig_system_ref_id,
866 program_id,
867 request_id,
868 program_application_id,
869 program_update_date,
870 l_Comments AS error_message,
871 nSessionNo AS session_no,
872 SYSDATE AS session_date,
873 primary_party_id,
874 contact_rel_party_id,
875 contact_party_id
876 FROM jtf_ih_interactions_stg WHERE interaction_id = nInteraction
877 AND session_no = nSessionNo;
878 EXCEPTION
879 WHEN OTHERS THEN
880 FND_MESSAGE.SET_NAME('JTF','JTF_IH_IMPORT_ERROR');
881 FND_MESSAGE.SET_TOKEN('ERRORMSG', SQLERRM);
882 FND_FILE.PUT_LINE(FND_FILE.LOG,FND_MESSAGE.GET);
883 --dbms_output.put_line(FND_MESSAGE.GET);
884 END;
885
886 -- Program History
887 -- 05/16/02 - Removed Security_Group_Id from Insert statements for all LOG tables.
888 --
889 PROCEDURE Insert_Media_Item_Log( nMediaID NUMBER, sComments VARCHAR2, nSessionNo IN OUT NOCOPY NUMBER) AS
890 l_Comments VARCHAR(2000);
891 BEGIN
892 l_Comments := sComments;
893 INSERT INTO jtf_ih_media_items_stg_log(
894 media_id,
895 object_version_number,
896 creation_date,
897 last_update_date,
898 created_by,
899 last_updated_by,
900 last_update_login,
901 duration,
902 direction,
903 end_date_time,
904 source_item_create_date_time,
905 interaction_performed,
906 source_item_id,
907 start_date_time,
908 source_id,
909 media_item_type,
910 media_item_ref,
911 media_data,
912 active,
913 media_abandon_flag,
914 media_transferred_flag,
915 session_no,
916 error_message,
917 session_date,
918 address)
919 SELECT
920 NVL(media_id,nMediaID) AS media_id,
921 object_version_number,
922 NVL(creation_date,SYSDATE) AS creation_date,
923 NVL(last_update_date,SYSDATE) AS last_update_date,
924 NVL(created_by,fnd_global.user_id) AS created_by,
925 NVL(last_updated_by,fnd_global.user_id) AS last_updated_by,
926 NVL(last_update_login,fnd_global.login_id) AS last_update_login,
927 duration,
928 direction,
929 end_date_time,
930 source_item_create_date_time,
931 interaction_performed,
932 source_item_id,
933 start_date_time,
934 source_id,
935 NVL(media_item_type,' ') AS media_item_type,
936 media_item_ref,
937 media_data,
938 NVL(active,' ') AS active,
939 media_abandon_flag,
940 media_transferred_flag,
941 nSessionNo AS session_no,
942 l_Comments AS error_message,
943 SYSDATE AS session_date,
944 address
945 FROM jtf_ih_media_items_stg
946 WHERE Media_Id = nMediaID AND session_no = nSessionNo;
947
948 EXCEPTION
949 WHEN OTHERS THEN
950 FND_MESSAGE.SET_NAME('JTF','JTF_IH_IMPORT_ERROR');
951 FND_MESSAGE.SET_TOKEN('ERRORMSG', SQLERRM);
952 FND_FILE.PUT_LINE(FND_FILE.LOG,FND_MESSAGE.GET);
953 --dbms_output.put_line(FND_MESSAGE.GET);
954 END;
955
956 -- Program History
957 -- 05/16/02 - Removed Security_Group_Id from Insert statements for all LOG tables.
958 --
959 PROCEDURE Insert_Activity_Log( nActivityID NUMBER, nInteractionID NUMBER, sComments VARCHAR2, nSessionNo IN OUT NOCOPY NUMBER) AS
960 l_Comments VARCHAR(2000);
961 BEGIN
962 l_Comments := sComments;
963 INSERT INTO jtf_ih_activities_stg_log(
964 activity_id,
965 object_version_number,
966 creation_date,
967 created_by,
968 last_updated_by,
969 last_update_date,
970 last_update_login,
971 doc_source_object_name,
972 cust_account_id,
973 cust_org_id,
974 interaction_id,
975 action_item_id,
976 object_id,
977 object_type,
978 source_code_id,
979 source_code,
980 doc_id,
981 doc_ref,
982 result_id,
983 reason_id,
984 media_id,
985 outcome_id,
986 task_id,
987 action_id,
988 duration,
989 description,
990 end_date_time,
991 role,
992 start_date_time,
993 interaction_action_type,
994 active,
995 attribute_category,
996 attribute1,
997 attribute2,
998 attribute3,
999 attribute4,
1000 attribute5,
1001 attribute6,
1002 attribute7,
1003 attribute8,
1004 attribute9,
1005 attribute10,
1006 attribute11,
1007 attribute12,
1008 attribute13,
1009 attribute14,
1010 attribute15,
1011 orig_system_reference,
1012 orig_system_reference_id,
1013 upgraded_status_flag,
1014 doc_source_status,
1015 upg_orig_system_ref,
1016 upg_orig_system_ref_id,
1017 cust_account_party_id,
1018 program_id,
1019 request_id,
1020 program_update_date,
1021 program_application_id,
1022 script_trans_id,
1023 error_message,
1024 session_no,
1025 session_date)
1026 SELECT DISTINCT
1027 activity_id,
1028 object_version_number,
1029 NVL(creation_date,SYSDATE),
1030 NVL(created_by,fnd_global.user_id),
1031 NVL(last_updated_by,fnd_global.user_id),
1032 NVL(last_update_date,SYSDATE),
1033 NVL(last_update_login,fnd_global.login_id),
1034 doc_source_object_name,
1035 cust_account_id,
1036 cust_org_id,
1037 interaction_id,
1038 action_item_id,
1039 object_id,
1040 object_type,
1041 source_code_id,
1042 source_code,
1043 doc_id,
1044 doc_ref,
1045 result_id,
1046 reason_id,
1047 media_id,
1048 outcome_id,
1049 task_id,
1050 action_id,
1051 duration,
1052 description,
1053 end_date_time,
1054 role,
1055 start_date_time,
1056 interaction_action_type,
1057 active,
1058 attribute_category,
1059 attribute1,
1060 attribute2,
1061 attribute3,
1062 attribute4,
1063 attribute5,
1064 attribute6,
1065 attribute7,
1066 attribute8,
1067 attribute9,
1068 attribute10,
1069 attribute11,
1070 attribute12,
1071 attribute13,
1072 attribute14,
1073 attribute15,
1074 orig_system_reference,
1075 orig_system_reference_id,
1076 upgraded_status_flag,
1077 doc_source_status,
1078 upg_orig_system_ref,
1079 upg_orig_system_ref_id,
1080 cust_account_party_id,
1081 program_id,
1082 request_id,
1083 program_update_date,
1084 program_application_id,
1085 script_trans_id,
1086 l_Comments AS error_message,
1087 nSessionNo AS session_no,
1088 SYSDATE AS session_date
1089 FROM jtf_ih_activities_stg
1090 WHERE activity_id = nActivityID
1091 AND Interaction_Id = nInteractionID
1092 AND session_no = nSessionNo;
1093 EXCEPTION
1094 WHEN OTHERS THEN
1095 FND_MESSAGE.SET_NAME('JTF','JTF_IH_IMPORT_ERROR');
1096 FND_MESSAGE.SET_TOKEN('ERRORMSG', SQLERRM);
1097 FND_FILE.PUT_LINE(FND_FILE.LOG,FND_MESSAGE.GET);
1098 --dbms_output.put_line(FND_MESSAGE.GET);
1099 END;
1100 END;