[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
g_acctnum_list.DELETE;
FOR CUST IN (SELECT distinct m.customer_id, acct.account_number
FROM ra_customer_merges m
, hz_cust_accounts acct
WHERE m.set_number = p_set_num
AND m.request_id = p_request_id
AND m.process_flag = 'N'
AND acct.cust_account_id = m.customer_id
)
LOOP
g_acctnum_list(CUST.customer_id) := CUST.account_number;
SELECT distinct CUSTOMER_MERGE_HEADER_ID
,task_id
,cust_account_id
FROM JTF_TASKS_B yt, ra_customer_merges m
WHERE (
yt.cust_account_id = m.DUPLICATE_ID
) AND m.process_flag = 'N'
AND m.request_id = p_request_id
AND m.set_number = p_set_number;
INSERT INTO HZ_CUSTOMER_MERGE_LOG (
MERGE_LOG_ID,
TABLE_NAME,
MERGE_HEADER_ID,
PRIMARY_KEY_ID,
NUM_COL1_ORIG,
NUM_COL1_NEW,
ACTION_FLAG,
REQUEST_ID,
CREATED_BY,
CREATION_DATE,
LAST_UPDATE_LOGIN,
LAST_UPDATE_DATE,
LAST_UPDATED_BY
) VALUES (
HZ_CUSTOMER_MERGE_LOG_s.nextval,
'JTF_TASKS_B',
MERGE_HEADER_ID_LIST(I),
PRIMARY_KEY_ID_LIST(I),
NUM_COL1_ORIG_LIST(I),
NUM_COL1_NEW_LIST(I),
'U',
p_request_id,
hz_utility_pub.CREATED_BY,
hz_utility_pub.CREATION_DATE,
hz_utility_pub.LAST_UPDATE_LOGIN,
hz_utility_pub.LAST_UPDATE_DATE,
hz_utility_pub.LAST_UPDATED_BY
);
UPDATE JTF_TASKS_B yt SET
cust_account_id=NUM_COL1_NEW_LIST(I)
, LAST_UPDATE_DATE=SYSDATE
, last_updated_by=arp_standard.profile.user_id
, last_update_login=arp_standard.profile.last_update_login
WHERE task_id=PRIMARY_KEY_ID_LIST(I);
arp_message.set_name('AR','AR_ROWS_UPDATED');
SELECT distinct CUSTOMER_MERGE_HEADER_ID
,task_audit_id
,old_cust_account_id
,new_cust_account_id
FROM JTF_TASK_AUDITS_B yt, ra_customer_merges m
WHERE (
yt.old_cust_account_id = m.DUPLICATE_ID
OR yt.new_cust_account_id = m.DUPLICATE_ID
) AND m.process_flag = 'N'
AND m.request_id = p_request_id
AND m.set_number = p_set_number;
INSERT INTO HZ_CUSTOMER_MERGE_LOG (
MERGE_LOG_ID,
TABLE_NAME,
MERGE_HEADER_ID,
PRIMARY_KEY_ID,
NUM_COL1_ORIG,
NUM_COL1_NEW,
NUM_COL2_ORIG,
NUM_COL2_NEW,
ACTION_FLAG,
REQUEST_ID,
CREATED_BY,
CREATION_DATE,
LAST_UPDATE_LOGIN,
LAST_UPDATE_DATE,
LAST_UPDATED_BY
) VALUES (
HZ_CUSTOMER_MERGE_LOG_s.nextval,
'JTF_TASK_AUDITS_B',
MERGE_HEADER_ID_LIST(I),
PRIMARY_KEY_ID_LIST(I),
NUM_COL1_ORIG_LIST(I),
NUM_COL1_NEW_LIST(I),
NUM_COL2_ORIG_LIST(I),
NUM_COL2_NEW_LIST(I),
'U',
p_request_id,
hz_utility_pub.CREATED_BY,
hz_utility_pub.CREATION_DATE,
hz_utility_pub.LAST_UPDATE_LOGIN,
hz_utility_pub.LAST_UPDATE_DATE,
hz_utility_pub.LAST_UPDATED_BY
);
UPDATE JTF_TASK_AUDITS_B yt SET
old_cust_account_id=NUM_COL1_NEW_LIST(I)
, new_cust_account_id=NUM_COL2_NEW_LIST(I)
, LAST_UPDATE_DATE=SYSDATE
, last_updated_by=arp_standard.profile.user_id
, last_update_login=arp_standard.profile.last_update_login
WHERE task_audit_id=PRIMARY_KEY_ID_LIST(I)
;
arp_message.set_name('AR','AR_ROWS_UPDATED');
SELECT distinct CUSTOMER_MERGE_HEADER_ID
,yt.QUERY_PARAM_ID
,yt.PARAMETER_VALUE
,cn.QUERY_PARAM_ID
,cn.PARAMETER_VALUE
FROM JTF_PERZ_QUERY_PARAM yt
, JTF_PERZ_QUERY_PARAM cn
, ra_customer_merges m
WHERE yt.PARAMETER_VALUE = to_char(m.DUPLICATE_ID)
AND m.process_flag = 'N'
AND m.request_id = p_request_id
AND m.set_number = p_set_number
AND cn.query_id = yt.query_id
AND cn.parameter_name = 'CUSTOMER_NAME'
AND yt.parameter_name = 'CUSTOMER_ID'
AND yt.query_id IN (SELECT q.query_id
FROM jtf_perz_query q,
jtf_perz_query_param p
WHERE q.query_type= 'JTF_TASK'
AND q.application_id = 690
AND p.query_id = q.query_id
AND p.parameter_name = 'CUSTOMER'
AND p.parameter_value = 'ACCOUNT');
INSERT INTO HZ_CUSTOMER_MERGE_LOG (
MERGE_LOG_ID,
TABLE_NAME,
MERGE_HEADER_ID,
PRIMARY_KEY_ID,
VCHAR_COL1_ORIG,
VCHAR_COL1_NEW,
ACTION_FLAG,
REQUEST_ID,
CREATED_BY,
CREATION_DATE,
LAST_UPDATE_LOGIN,
LAST_UPDATE_DATE,
LAST_UPDATED_BY
) VALUES (
HZ_CUSTOMER_MERGE_LOG_s.nextval,
'JTF_PERZ_QUERY_PARAM',
MERGE_HEADER_ID_LIST(I),
PRIMARY_KEY_ID1_LIST(I),
VCHAR_COL1_ORIG_LIST(I),
VCHAR_COL1_NEW_LIST(I),
'U',
p_request_id,
hz_utility_pub.CREATED_BY,
hz_utility_pub.CREATION_DATE,
hz_utility_pub.LAST_UPDATE_LOGIN,
hz_utility_pub.LAST_UPDATE_DATE,
hz_utility_pub.LAST_UPDATED_BY
);
INSERT INTO HZ_CUSTOMER_MERGE_LOG (
MERGE_LOG_ID,
TABLE_NAME,
MERGE_HEADER_ID,
PRIMARY_KEY_ID,
VCHAR_COL1_ORIG,
VCHAR_COL1_NEW,
ACTION_FLAG,
REQUEST_ID,
CREATED_BY,
CREATION_DATE,
LAST_UPDATE_LOGIN,
LAST_UPDATE_DATE,
LAST_UPDATED_BY
) VALUES (
HZ_CUSTOMER_MERGE_LOG_s.nextval,
'JTF_PERZ_QUERY_PARAM',
MERGE_HEADER_ID_LIST(I),
PRIMARY_KEY_ID2_LIST(I),
VCHAR_CN_ORIG_LIST(I),
VCHAR_CN_NEW_LIST(I),
'U',
p_request_id,
hz_utility_pub.CREATED_BY,
hz_utility_pub.CREATION_DATE,
hz_utility_pub.LAST_UPDATE_LOGIN,
hz_utility_pub.LAST_UPDATE_DATE,
hz_utility_pub.LAST_UPDATED_BY
);
UPDATE JTF_PERZ_QUERY_PARAM yt SET
PARAMETER_VALUE=VCHAR_COL1_NEW_LIST(I)
, LAST_UPDATE_DATE=SYSDATE
, last_updated_by=arp_standard.profile.user_id
, last_update_login=arp_standard.profile.last_update_login
WHERE QUERY_PARAM_ID=PRIMARY_KEY_ID1_LIST(I);
UPDATE JTF_PERZ_QUERY_PARAM yt SET
PARAMETER_VALUE=VCHAR_CN_NEW_LIST(I)
, LAST_UPDATE_DATE=SYSDATE
, last_updated_by=arp_standard.profile.user_id
, last_update_login=arp_standard.profile.last_update_login
WHERE QUERY_PARAM_ID=PRIMARY_KEY_ID2_LIST(I);
arp_message.set_name('AR','AR_ROWS_UPDATED');