[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
PROCEDURE Insert_Interaction_Log( nInteraction NUMBER, sComments VARCHAR2, nSessionNo in out nocopy NUMBER);
PROCEDURE Insert_Activity_Log( nActivityID NUMBER, nInteractionID NUMBER, sComments VARCHAR2, nSessionNo in out nocopy NUMBER);
PROCEDURE Insert_Media_Item_Log( nMediaID NUMBER, sComments VARCHAR2, nSessionNo in out nocopy NUMBER);
SELECT COUNT(*) INTO nCount FROM jtf_ih_interactions_stg WHERE interaction_id = nId
AND (Session_No = pnSessionNo OR Session_No IS NULL OR Session_No = v_Session_no);
SELECT COUNT(*) INTO nCount FROM jtf_ih_media_items_stg WHERE media_id = nId
AND (Session_No = pnSessionNo OR Session_No IS NULL OR Session_No = v_Session_no);
CURSOR curMediaItems IS SELECT
media_id,
source_id,
direction,
duration,
end_date_time,
interaction_performed,
start_date_time,
media_data,
source_item_create_date_time,
source_item_id,
media_item_type,
media_item_ref,
media_abandon_flag,
media_transferred_flag,
server_group_id,
dnis,
ani,
classification,
'' as bulk_writer_code ,
'' as bulk_batch_type,
NULL as bulk_batch_id,
NULL as bulk_interaction_id,
address
FROM JTF_IH_MEDIA_ITEMS_STG
WHERE
(Session_No = pnSessionNo OR Session_No IS NULL OR Session_No = v_Session_no)
AND (Status_FL = v_Status_Fl1 OR Status_FL IS NULL);
SELECT Count(*) INTO nCount from jtf_ih_interactions_stg
WHERE (Session_No = pnSessionNo OR Session_No IS NULL OR Session_No = v_Session_no);
SELECT Count(*) INTO nCount from jtf_ih_media_items_stg
WHERE (Session_No = pnSessionNo OR Session_No IS NULL OR Session_No = v_Session_no);
SELECT JTF_IH_IMPORT_S1.NEXTVAL into nNxtSessionNo FROM DUAL;
FOR curInteraction IN (select
distinct Interaction_ID FROM jtf_ih_interactions_stg
WHERE (Session_No IS NULL OR Session_No = pnSessionNo OR Session_No = v_Session_no )
AND (Status_FL IS NULL OR Status_FL = v_Status_Fl0) ORDER BY Interaction_ID) LOOP
--
BEGIN
n_Interaction_Id := curInteraction.Interaction_ID;
SELECT Count(*) INTO nCount FROM jtf_ih_activities_stg
WHERE Interaction_ID = curInteraction.Interaction_ID AND (Session_No = pnSessionNo OR Session_No IS NULL OR Session_No = v_Session_no);
UPDATE jtf_ih_interactions_stg SET Status_Fl = v_Status_Fl0, Session_Date = SYSDATE, Session_No = nNxtSessionNo
WHERE Interaction_ID = curInteraction.Interaction_ID and (Session_No = pnSessionNo OR Session_No IS NULL OR Session_No = v_Session_no);
Insert_Interaction_Log(curInteraction.Interaction_ID, l_Message, nNxtSessionNo);
FOR curActivity IN (SELECT Activity_ID, Media_ID FROM jtf_ih_activities_stg WHERE
Interaction_ID = curInteraction.Interaction_Id and
(Session_No IS NULL OR Session_No = pnSessionNo OR Session_No = v_Session_no)) LOOP
IF (curActivity.Media_ID IS NOT NULL) AND (curActivity.Media_ID <> fnd_api.g_miss_num) THEN
/* Looking for Media_ID in the JTF_IH_MEDIA_ITEM_STG */
SELECT Count(*) INTO nCount FROM jtf_ih_media_items_stg
WHERE Media_ID = curActivity.Media_ID AND (Session_No IN (pnSessionNo, v_Session_no) OR Session_No IS NULL);
UPDATE jtf_ih_interactions_stg SET Status_Fl = v_Status_Fl1, Session_No = nNxtSessionNo, Session_Date = SYSDATE
WHERE Interaction_ID = curInteraction.Interaction_ID and (Session_No = pnSessionNo OR Session_No IS NULL OR Session_No = v_Session_no);
UPDATE jtf_ih_activities_stg SET Status_Fl = v_Status_Fl1, Session_No = nNxtSessionNo, Session_Date = SYSDATE
WHERE Interaction_ID = curInteraction.Interaction_ID and (Session_No = pnSessionNo OR Session_No IS NULL OR Session_No = v_Session_no);
UPDATE jtf_ih_interactions_stg SET Status_Fl = v_Status_Fl0, Session_Date = SYSDATE, Session_No = nNxtSessionNo
WHERE Interaction_ID = curInteraction.Interaction_ID and (Session_No = pnSessionNo OR Session_No IS NULL OR Session_No = v_Session_no);
Insert_Interaction_Log(curInteraction.Interaction_ID, l_Message, nNxtSessionNo);
UPDATE jtf_ih_activities_stg SET Status_Fl = v_Status_Fl0, Session_Date = SYSDATE, Session_No = nNxtSessionNo
WHERE Interaction_ID = curInteraction.Interaction_ID and (Session_No = pnSessionNo OR Session_No IS NULL OR Session_No = v_Session_no);
FOR curErrAct IN (SELECT Activity_ID FROM jtf_ih_activities_stg WHERE interaction_id = n_Interaction_ID AND Session_No = nNxtSessionNo) LOOP
Insert_activity_Log(curErrAct.Activity_Id, curInteraction.Interaction_ID, l_Message, nNxtSessionNo);
UPDATE jtf_ih_media_items_stg SET Status_Fl = v_Status_Fl1, Session_No = nNxtSessionNo, Session_Date = SYSDATE
WHERE Media_ID = sMedia_ID_Stg AND (Session_No = pnSessionNo OR Session_No IS NULL OR Session_No = v_Session_no);
UPDATE jtf_ih_media_items_stg SET Status_Fl = v_Status_Fl0, Session_Date = SYSDATE, Session_No = nNxtSessionNo
WHERE Media_Id = sMedia_ID_Stg and (Session_No = pnSessionNo OR Session_No IS NULL OR Session_No = v_Session_no);
Insert_Media_Item_Log(sMedia_ID_Stg,l_Message, nNxtSessionNo);
CURSOR curInteraction IS SELECT
interaction_id,
reference_form,
follow_up_action,
duration,
end_date_time,
inter_interaction_duration,
non_productive_time_amount,
preview_time_amount,
productive_time_amount,
start_date_time,
wrap_up_time_amount,
handler_id,
script_id,
outcome_id,
result_id,
reason_id,
resource_id,
party_id,
NULL,
object_id,
object_type,
source_code_id,
source_code,
attribute1,
attribute2,
attribute3,
attribute4,
attribute5,
attribute6,
attribute7,
attribute8,
attribute9,
attribute10,
attribute11,
attribute12,
attribute13,
attribute14,
attribute15,
attribute_category,
touchpoint1_type,
touchpoint2_type,
method_code,
primary_party_id,
contact_rel_party_id,
contact_party_id,
'' as bulk_writer_code ,
'' as bulk_batch_type,
NULL as bulk_batch_id,
NULL as bulk_interaction_id
FROM jtf_ih_interactions_stg WHERE
Status_Fl = v_Status_Fl1 AND (Session_No IN (nSessionNo, v_Session_no) OR Session_No IS NULL)
ORDER BY interaction_id;
SELECT activity_id,
duration,
cust_account_id,
cust_org_id,
role,
end_date_time,
start_date_time,
task_id,
doc_id,
doc_ref,
doc_source_object_name,
media_id,
action_item_id,
interaction_id,
outcome_id,
result_id,
reason_id,
description,
action_id,
interaction_action_type,
object_id,
object_type,
source_code_id,
source_code,
script_trans_id,
attribute1,
attribute2,
attribute3,
attribute4,
attribute5,
attribute6,
attribute7,
attribute8,
attribute9,
attribute10,
attribute11,
attribute12,
attribute13,
attribute14,
attribute15,
attribute_category,
'' as bulk_writer_code ,
'' as bulk_batch_type,
NULL as bulk_batch_id,
NULL as bulk_interaction_id
-- Removed by IAleshin 06/05/2002
--,resource_id
FROM JTF_IH_ACTIVITIES_STG
WHERE Status_FL = v_Status_Fl1 AND Interaction_ID = nIntrctID
AND (Session_No IN (nSessionNo, v_Session_no) OR Session_No IS NULL);
CURSOR curMediaItem IS SELECT
media_id,
source_id,
direction,
duration,
end_date_time,
interaction_performed,
start_date_time,
media_data,
source_item_create_date_time,
source_item_id,
media_item_type,
media_item_ref,
media_abandon_flag,
media_transferred_flag,
server_group_id,
dnis,
ani,
classification,
'' as bulk_writer_code ,
'' as bulk_batch_type,
NULL as bulk_batch_id,
NULL as bulk_interaction_id,
address
FROM JTF_IH_MEDIA_ITEMS_STG
WHERE Status_FL = v_Status_Fl1 AND (Session_No IN (nSessionNo,v_Session_no) OR Session_No IS NULL);
SELECT Count(Interaction_ID) INTO nTotInter FROM jtf_ih_interactions_stg
WHERE (Session_No IN (nSessionNo,v_Session_no) OR Session_No IS NULL);
SELECT Count(Activity_ID) INTO nTotActiv FROM jtf_ih_activities_stg
WHERE (Session_No IN (nSessionNo,v_Session_no) OR Session_No IS NULL);
SELECT Count(Media_ID) INTO nTotMdaItm FROM jtf_ih_media_items_stg
WHERE (Session_No IN (nSessionNo,v_Session_no) OR Session_No IS NULL);
UPDATE jtf_ih_media_items_stg SET Status_Fl = v_Status_Fl2, Media_ID = l_media_item_id, Session_No = nSessionNo
WHERE Media_ID = l_media_id_stg AND (Session_No IN (nSessionNo,v_Session_no) OR Session_No IS NULL);
UPDATE jtf_ih_activities_stg SET Media_ID = l_media_item_id
WHERE Media_ID = l_media_id_stg AND (Session_No IN (nSessionNo,v_Session_no) OR Session_No IS NULL);
UPDATE jtf_ih_media_items_stg SET Status_Fl = v_Status_Fl0, Session_Date = SYSDATE, Session_No = nSessionNo
WHERE Media_ID = l_media_id_stg AND (Session_No IN (nSessionNo,v_Session_no) OR Session_No IS NULL);
Insert_Media_Item_Log(l_media_id_stg, l_msg_data, nSessionNo);
FOR curErrActive IN (SELECT Activity_ID, Interaction_ID FROM jtf_ih_activities_stg WHERE Interaction_ID = (
SELECT Interaction_ID FROM jtf_ih_activities_stg
WHERE Media_ID = l_media_id_stg AND (Session_No IN (nSessionNo,v_Session_no)
OR Session_No IS NULL))) LOOP
UPDATE jtf_ih_interactions_stg SET Status_Fl = v_Status_Fl0, Session_Date = SYSDATE, Session_No = nSessionNo
WHERE Interaction_Id = curErrActive.Interaction_ID AND (Session_No IN (nSessionNo,v_Session_no) OR Session_No IS NULL);
Insert_Interaction_Log(curErrActive.Interaction_ID, l_msg_data, nSessionNo);
UPDATE jtf_ih_activities_stg SET Status_Fl = v_Status_Fl0, Session_Date = SYSDATE, Session_No = nSessionNo
WHERE Activity_ID = curErrActive.Activity_ID AND Interaction_Id = curErrActive.Interaction_ID
AND (Session_No IN (nSessionNo,v_Session_no) OR Session_No IS NULL);
Insert_Activity_Log(curErrActive.Activity_ID, curErrActive.Interaction_ID, l_msg_data, nSessionNo);
UPDATE jtf_ih_activities_stg SET Activity_ID = l_activity_id_1, Status_Fl = v_Status_Fl2,
Session_Date = SYSDATE, Session_No = nSessionNo
WHERE Activity_ID = l_activity_stg
AND Interaction_ID = l_interaction_stg
AND (Session_No IN (nSessionNo,v_Session_no) OR Session_No IS NULL);
SELECT Active INTO nActive FROM jtf_ih_interactions_stg
WHERE Interaction_ID = l_interaction_stg
AND (Session_No IN (nSessionNo,v_Session_no) OR Session_No IS NULL);
UPDATE jtf_ih_interactions_stg SET Interaction_ID = l_interaction_id, Status_Fl = v_Status_Fl2,
Session_Date = SYSDATE, Session_No = nSessionNo
WHERE Interaction_ID = l_interaction_stg AND (Session_No IN (nSessionNo,v_Session_no) OR Session_No IS NULL);
UPDATE jtf_ih_activities_stg SET Interaction_ID = l_interaction_id, Status_Fl = v_Status_Fl2,
Session_Date = SYSDATE, Session_No = nSessionNo
WHERE Interaction_ID = l_interaction_stg AND (Session_No IN (nSessionNo,v_Session_no) OR Session_No IS NULL);
UPDATE jtf_ih_interactions_stg SET Status_Fl = v_Status_Fl0, Session_Date = SYSDATE, Session_No = nSessionNo
WHERE Interaction_ID = l_interaction_stg AND (Session_No IN (nSessionNo,v_Session_no) OR Session_No IS NULL);
Insert_Interaction_Log(l_interaction_stg, l_msg_data, nSessionNo);
UPDATE jtf_ih_activities_stg SET Status_Fl = v_Status_Fl0, Session_Date = SYSDATE, Session_No = nSessionNo
WHERE Interaction_ID = l_interaction_stg AND (Session_No IN (nSessionNo,v_Session_no) OR Session_No IS NULL);
FOR currActivErr IN (SELECT Activity_ID, Interaction_ID FROM jtf_ih_activities_stg WHERE Interaction_ID = l_interaction_stg
AND (Session_No IN (nSessionNo,v_Session_no) OR Session_No IS NULL)) LOOP
Insert_Activity_Log(currActivErr.Activity_ID,currActivErr.Interaction_ID, l_msg_data, nSessionNo);
SELECT COUNT(*) INTO nErr_InterCnt FROM
(SELECT interaction_id FROM jtf_ih_interactions_stg_log
WHERE session_no = nSessionNo);
SELECT COUNT(*) INTO nErr_ActivCnt FROM
(SELECT activity_id FROM jtf_ih_activities_stg_log
WHERE session_no = nSessionNo);
SELECT COUNT(*) INTO nErr_MediaCnt FROM
(SELECT media_id FROM jtf_ih_media_items_stg_log
WHERE session_no = nSessionNo);
PROCEDURE Insert_Interaction_Log( nInteraction NUMBER, sComments VARCHAR2, nSessionNo IN OUT NOCOPY NUMBER) AS
l_Comments VARCHAR(2000);
INSERT INTO JTF_IH_INTERACTIONS_STG_LOG(
interaction_id,
object_version_number,
creation_date,
created_by,
last_update_date,
last_updated_by,
last_update_login,
interaction_inters_id,
object_id,
object_type,
source_code,
source_code_id,
reference_form,
duration,
end_date_time,
follow_up_action,
non_productive_time_amount,
result_id,
reason_id,
start_date_time,
outcome_id,
preview_time_amount,
productive_time_amount,
handler_id,
inter_interaction_duration,
wrap_up_time_amount,
script_id,
party_id,
resource_id,
method_code,
org_id,
attribute_category,
attribute1,
attribute2,
attribute3,
attribute4,
attribute5,
attribute6,
attribute7,
attribute8,
attribute9,
attribute10,
attribute11,
attribute12,
attribute13,
attribute14,
attribute15,
active,
touchpoint1_type,
touchpoint2_type,
orig_system_reference,
orig_system_reference_id,
public_flag,
upgraded_status_flag,
upg_orig_system_ref,
upg_orig_system_ref_id,
program_id,
request_id,
program_application_id,
program_update_date,
error_message,
session_no,
session_date,
primary_party_id,
contact_rel_party_id,
contact_party_id
)
SELECT
interaction_id,
object_version_number,
NVL(creation_date, SYSDATE),
NVL(created_by, fnd_global.user_id),
NVL(last_update_date, SYSDATE),
NVL(last_updated_by,fnd_global.user_id),
NVL(last_update_login,fnd_global.login_id),
interaction_inters_id,
object_id,
object_type,
source_code,
source_code_id,
reference_form,
duration,
end_date_time,
follow_up_action,
non_productive_time_amount,
result_id,
reason_id,
start_date_time,
outcome_id,
preview_time_amount,
productive_time_amount,
handler_id,
inter_interaction_duration,
wrap_up_time_amount,
script_id,
party_id,
resource_id,
method_code,
org_id,
attribute_category,
attribute1,
attribute2,
attribute3,
attribute4,
attribute5,
attribute6,
attribute7,
attribute8,
attribute9,
attribute10,
attribute11,
attribute12,
attribute13,
attribute14,
attribute15,
active,
touchpoint1_type,
touchpoint2_type,
orig_system_reference,
orig_system_reference_id,
public_flag,
upgraded_status_flag,
upg_orig_system_ref,
upg_orig_system_ref_id,
program_id,
request_id,
program_application_id,
program_update_date,
l_Comments AS error_message,
nSessionNo AS session_no,
SYSDATE AS session_date,
primary_party_id,
contact_rel_party_id,
contact_party_id
FROM jtf_ih_interactions_stg WHERE interaction_id = nInteraction
AND session_no = nSessionNo;
PROCEDURE Insert_Media_Item_Log( nMediaID NUMBER, sComments VARCHAR2, nSessionNo IN OUT NOCOPY NUMBER) AS
l_Comments VARCHAR(2000);
INSERT INTO jtf_ih_media_items_stg_log(
media_id,
object_version_number,
creation_date,
last_update_date,
created_by,
last_updated_by,
last_update_login,
duration,
direction,
end_date_time,
source_item_create_date_time,
interaction_performed,
source_item_id,
start_date_time,
source_id,
media_item_type,
media_item_ref,
media_data,
active,
media_abandon_flag,
media_transferred_flag,
session_no,
error_message,
session_date,
address)
SELECT
NVL(media_id,nMediaID) AS media_id,
object_version_number,
NVL(creation_date,SYSDATE) AS creation_date,
NVL(last_update_date,SYSDATE) AS last_update_date,
NVL(created_by,fnd_global.user_id) AS created_by,
NVL(last_updated_by,fnd_global.user_id) AS last_updated_by,
NVL(last_update_login,fnd_global.login_id) AS last_update_login,
duration,
direction,
end_date_time,
source_item_create_date_time,
interaction_performed,
source_item_id,
start_date_time,
source_id,
NVL(media_item_type,' ') AS media_item_type,
media_item_ref,
media_data,
NVL(active,' ') AS active,
media_abandon_flag,
media_transferred_flag,
nSessionNo AS session_no,
l_Comments AS error_message,
SYSDATE AS session_date,
address
FROM jtf_ih_media_items_stg
WHERE Media_Id = nMediaID AND session_no = nSessionNo;
PROCEDURE Insert_Activity_Log( nActivityID NUMBER, nInteractionID NUMBER, sComments VARCHAR2, nSessionNo IN OUT NOCOPY NUMBER) AS
l_Comments VARCHAR(2000);
INSERT INTO jtf_ih_activities_stg_log(
activity_id,
object_version_number,
creation_date,
created_by,
last_updated_by,
last_update_date,
last_update_login,
doc_source_object_name,
cust_account_id,
cust_org_id,
interaction_id,
action_item_id,
object_id,
object_type,
source_code_id,
source_code,
doc_id,
doc_ref,
result_id,
reason_id,
media_id,
outcome_id,
task_id,
action_id,
duration,
description,
end_date_time,
role,
start_date_time,
interaction_action_type,
active,
attribute_category,
attribute1,
attribute2,
attribute3,
attribute4,
attribute5,
attribute6,
attribute7,
attribute8,
attribute9,
attribute10,
attribute11,
attribute12,
attribute13,
attribute14,
attribute15,
orig_system_reference,
orig_system_reference_id,
upgraded_status_flag,
doc_source_status,
upg_orig_system_ref,
upg_orig_system_ref_id,
cust_account_party_id,
program_id,
request_id,
program_update_date,
program_application_id,
script_trans_id,
error_message,
session_no,
session_date)
SELECT DISTINCT
activity_id,
object_version_number,
NVL(creation_date,SYSDATE),
NVL(created_by,fnd_global.user_id),
NVL(last_updated_by,fnd_global.user_id),
NVL(last_update_date,SYSDATE),
NVL(last_update_login,fnd_global.login_id),
doc_source_object_name,
cust_account_id,
cust_org_id,
interaction_id,
action_item_id,
object_id,
object_type,
source_code_id,
source_code,
doc_id,
doc_ref,
result_id,
reason_id,
media_id,
outcome_id,
task_id,
action_id,
duration,
description,
end_date_time,
role,
start_date_time,
interaction_action_type,
active,
attribute_category,
attribute1,
attribute2,
attribute3,
attribute4,
attribute5,
attribute6,
attribute7,
attribute8,
attribute9,
attribute10,
attribute11,
attribute12,
attribute13,
attribute14,
attribute15,
orig_system_reference,
orig_system_reference_id,
upgraded_status_flag,
doc_source_status,
upg_orig_system_ref,
upg_orig_system_ref_id,
cust_account_party_id,
program_id,
request_id,
program_update_date,
program_application_id,
script_trans_id,
l_Comments AS error_message,
nSessionNo AS session_no,
SYSDATE AS session_date
FROM jtf_ih_activities_stg
WHERE activity_id = nActivityID
AND Interaction_Id = nInteractionID
AND session_no = nSessionNo;