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;
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;
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:
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:
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
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
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
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;
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;
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);
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);
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
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);
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');
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);
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;
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:
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
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);
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;
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;
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;
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;
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;
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;
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);
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:
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
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
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
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);
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;
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);
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
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
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');
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
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.');
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');
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);
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
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
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
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);
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;
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:
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:
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.
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;
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:
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
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.
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;
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:
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
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.
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;
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;
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;
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;