[Home] [Help]
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;
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:
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;
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;
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');
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;
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:
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
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
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;
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
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));
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,
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