The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT distinct m.CUSTOMER_MERGE_HEADER_ID
,m.customer_id, m.duplicate_id, hzca.party_id, yt.party_id
FROM CSC_CUSTOMERS yt, ra_customer_merges m, hz_cust_accounts hzca
WHERE ( yt.cust_account_id = m.duplicate_id AND m.customer_id = hzca.cust_account_id )
AND m.process_flag = 'N'
AND m.request_id = req_id
AND m.set_number = set_num;
INSERT INTO HZ_CUSTOMER_MERGE_LOG (
MERGE_LOG_ID,
TABLE_NAME,
MERGE_HEADER_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,
'CSC_CUSTOMERS',
MERGE_HEADER_ID_LIST(I),
NUM_COL1_ORIG_LIST(I),
NUM_COL1_NEW_LIST(I),
NUM_COL2_ORIG_LIST(I),
NUM_COL2_NEW_LIST(I),
'U',
req_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 CSC_CUSTOMERS yt SET
cust_account_id=NUM_COL1_NEW_LIST(I)
, party_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
, REQUEST_ID=req_id
, PROGRAM_APPLICATION_ID=arp_standard.profile.program_application_id
, PROGRAM_ID=arp_standard.profile.program_id
, PROGRAM_UPDATE_DATE=SYSDATE
WHERE cust_account_id in ( SELECT m.duplicate_id FROM
ra_customer_merges m WHERE
m.process_flag = 'N'
AND m.request_id = req_id
AND m.set_number = set_num )
;
arp_message.set_name('AR','AR_ROWS_UPDATED');
SELECT distinct CUSTOMER_MERGE_HEADER_ID
,m.customer_id, m.duplicate_id
FROM CSC_CUSTOMERS_AUDIT_HIST yt, ra_customer_merges m , hz_cust_accounts hzca
WHERE ( yt.cust_account_id = m.duplicate_id AND m.customer_id = hzca.cust_account_id )
AND m.process_flag = 'N'
AND m.request_id = req_id
AND m.set_number = set_num;
INSERT INTO HZ_CUSTOMER_MERGE_LOG (
MERGE_LOG_ID,
TABLE_NAME,
MERGE_HEADER_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,
'CSC_CUSTOMERS_AUDIT_HIST',
MERGE_HEADER_ID_LIST(I),
NUM_COL1_ORIG_LIST(I),
NUM_COL1_NEW_LIST(I),
'U',
req_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 CSC_CUSTOMERS_AUDIT_HIST 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
, REQUEST_ID=req_id
, PROGRAM_APPLICATION_ID=arp_standard.profile.program_application_id
, PROGRAM_ID=arp_standard.profile.program_id
, PROGRAM_UPDATE_DATE=SYSDATE
WHERE cust_account_id in ( SELECT m.duplicate_id FROM
ra_customer_merges m WHERE
m.process_flag = 'N'
AND m.request_id = req_id
AND m.set_number = set_num )
;
arp_message.set_name('AR','AR_ROWS_UPDATED');
SELECT distinct m.CUSTOMER_MERGE_HEADER_ID
,m.customer_id, m.duplicate_id, hzca.party_id, yt.party_id
FROM CSC_CUSTOMIZED_PLANS yt, ra_customer_merges m , hz_cust_accounts hzca
WHERE ( yt.cust_account_id = m.duplicate_id AND m.customer_id = hzca.cust_account_id )
AND m.process_flag = 'N'
AND m.request_id = req_id
AND m.set_number = set_num;
INSERT INTO HZ_CUSTOMER_MERGE_LOG (
MERGE_LOG_ID,
TABLE_NAME,
MERGE_HEADER_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,
'CSC_CUSTOMIZED_PLANS',
MERGE_HEADER_ID_LIST(I),
NUM_COL1_ORIG_LIST(I),
NUM_COL1_NEW_LIST(I),
NUM_COL2_ORIG_LIST(I),
NUM_COL2_NEW_LIST(I),
'U',
req_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 CSC_CUSTOMIZED_PLANS yt SET
cust_account_id=NUM_COL1_NEW_LIST(I)
, party_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
, REQUEST_ID=req_id
, PROGRAM_APPLICATION_ID=arp_standard.profile.program_application_id
, PROGRAM_ID=arp_standard.profile.program_id
, PROGRAM_UPDATE_DATE=SYSDATE
WHERE cust_account_id in ( SELECT m.duplicate_id FROM
ra_customer_merges m WHERE
m.process_flag = 'N'
AND m.request_id = req_id
AND m.set_number = set_num )
;
arp_message.set_name('AR','AR_ROWS_UPDATED');
SELECT distinct m.CUSTOMER_MERGE_HEADER_ID
,yt.cust_plan_id
,m.customer_id, m.duplicate_id, hzca.party_id, yt.party_id
FROM CSC_CUST_PLANS yt, ra_customer_merges m , hz_cust_accounts hzca
WHERE ( yt.cust_account_id = m.duplicate_id AND m.customer_id = hzca.cust_account_id )
AND m.process_flag = 'N'
AND m.request_id = req_id
AND m.set_number = set_num;
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,
'CSC_CUST_PLANS',
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',
req_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 CSC_CUST_PLANS yt SET
cust_account_id=NUM_COL1_NEW_LIST(I)
, party_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
, REQUEST_ID=req_id
, PROGRAM_APPLICATION_ID=arp_standard.profile.program_application_id
, PROGRAM_ID=arp_standard.profile.program_id
, PROGRAM_UPDATE_DATE=SYSDATE
WHERE cust_account_id in ( SELECT m.duplicate_id FROM
ra_customer_merges m WHERE
m.process_flag = 'N'
AND m.request_id = req_id
AND m.set_number = set_num )
AND plan_id not in ( SELECT yts.plan_id FROM
csc_cust_plans yts, ra_customer_merges m WHERE
yts.cust_account_id = m.customer_id
AND m.process_flag = 'N'
AND m.request_id = req_id
AND m.set_number = set_num )
;
DELETE FROM CSC_CUST_PLANS
WHERE cust_account_id in ( SELECT m.duplicate_id FROM
ra_customer_merges m WHERE
m.process_flag = 'N'
AND m.request_id = req_id
AND m.set_number = set_num )
AND plan_id in ( SELECT yts.plan_id FROM
csc_cust_plans yts, ra_customer_merges m WHERE
yts.cust_account_id = m.customer_id
AND m.process_flag = 'N'
AND m.request_id = req_id
AND m.set_number = set_num )
;
arp_message.set_name('AR','AR_ROWS_UPDATED');
SELECT distinct m.CUSTOMER_MERGE_HEADER_ID
,yt.plan_audit_id
,m.customer_id, m.duplicate_id, hzca.party_id, yt.party_id
FROM CSC_CUST_PLANS_AUDIT yt, ra_customer_merges m , hz_cust_accounts hzca
WHERE ( yt.cust_account_id = m.duplicate_id AND m.customer_id = hzca.cust_account_id )
AND m.process_flag = 'N'
AND m.request_id = req_id
AND m.set_number = set_num;
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,
'CSC_CUST_PLANS_AUDIT',
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',
req_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 CSC_CUST_PLANS_AUDIT yt SET
cust_account_id=NUM_COL1_NEW_LIST(I)
, party_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
, REQUEST_ID=req_id
, PROGRAM_APPLICATION_ID=arp_standard.profile.program_application_id
, PROGRAM_ID=arp_standard.profile.program_id
, PROGRAM_UPDATE_DATE=SYSDATE
WHERE cust_account_id in ( SELECT m.duplicate_id FROM
ra_customer_merges m WHERE
m.process_flag = 'N'
AND m.request_id = req_id
AND m.set_number = set_num )
AND plan_id not in ( SELECT yts.plan_id FROM
csc_cust_plans_audit yts, ra_customer_merges m WHERE
yts.cust_account_id = m.customer_id
AND m.process_flag = 'N'
AND m.request_id = req_id
AND m.set_number = set_num )
;
arp_message.set_name('AR','AR_ROWS_UPDATED');