[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT distinct m.CUSTOMER_MERGE_HEADER_ID,
yt.PAYMENT_SCHEDULE_ID,
yt.BILL_TO_CUSTOMER_ID,
yt.BILL_TO_SITE_USE_ID,
m.CUSTOMER_ID,
m.customer_site_id
FROM FII_AR_PMT_SCHEDULES_F yt,
ra_customer_merges m
WHERE (yt.BILL_TO_CUSTOMER_ID = m.duplicate_id
AND yt.BILL_TO_SITE_USE_ID = m.duplicate_site_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,
'FII_AR_PMT_SCHEDULES_F',
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 FII_AR_PMT_SCHEDULES_F yt
SET BILL_TO_CUSTOMER_ID=NUM_COL1_NEW_LIST(I),
BILL_TO_SITE_USE_ID=NUM_COL2_NEW_LIST(I)
WHERE PAYMENT_SCHEDULE_ID=PRIMARY_KEY_ID_LIST(I);
UPDATE FII_AR_TRANSACTIONS_F yt
SET BILL_TO_CUSTOMER_ID = NUM_COL1_NEW_LIST(I),
BILL_TO_SITE_USE_ID = NUM_COL2_NEW_LIST(I)
WHERE BILL_TO_CUSTOMER_ID = NUM_COL1_ORIG_LIST(I)
AND BILL_TO_SITE_USE_ID = NUM_COL2_ORIG_LIST(I);
UPDATE FII_AR_RECEIPTS_F yt
SET BILL_TO_CUSTOMER_ID = NUM_COL1_NEW_LIST(I),
BILL_TO_SITE_USE_ID = NUM_COL2_NEW_LIST(I)
WHERE BILL_TO_CUSTOMER_ID = NUM_COL1_ORIG_LIST(I)
AND BILL_TO_SITE_USE_ID = NUM_COL2_ORIG_LIST(I);
UPDATE FII_AR_RECEIPTS_F yt
SET COLLECTOR_BILL_TO_CUSTOMER_ID = NUM_COL1_NEW_LIST(I),
COLLECTOR_BILL_TO_SITE_USE_ID = NUM_COL2_NEW_LIST(I)
WHERE COLLECTOR_BILL_TO_CUSTOMER_ID = NUM_COL1_ORIG_LIST(I)
AND COLLECTOR_BILL_TO_SITE_USE_ID = NUM_COL2_ORIG_LIST(I);
UPDATE FII_AR_ADJUSTMENTS_F yt
SET BILL_TO_CUSTOMER_ID = NUM_COL1_NEW_LIST(I),
BILL_TO_SITE_USE_ID = NUM_COL2_NEW_LIST(I)
WHERE BILL_TO_CUSTOMER_ID = NUM_COL1_ORIG_LIST(I)
AND BILL_TO_SITE_USE_ID = NUM_COL2_ORIG_LIST(I);
UPDATE FII_AR_DISPUTE_HISTORY_F yt
SET BILL_TO_CUSTOMER_ID = NUM_COL1_NEW_LIST(I),
BILL_TO_SITE_USE_ID = NUM_COL2_NEW_LIST(I)
WHERE BILL_TO_CUSTOMER_ID = NUM_COL1_ORIG_LIST(I)
AND BILL_TO_SITE_USE_ID = NUM_COL2_ORIG_LIST(I);
UPDATE FII_AR_AGING_RECEIVABLES yt
SET BILL_TO_CUSTOMER_ID = NUM_COL1_NEW_LIST(I),
BILL_TO_SITE_USE_ID = NUM_COL2_NEW_LIST(I)
WHERE BILL_TO_CUSTOMER_ID = NUM_COL1_ORIG_LIST(I)
AND BILL_TO_SITE_USE_ID = NUM_COL2_ORIG_LIST(I);
UPDATE FII_AR_AGING_RECEIPTS yt
SET BILL_TO_CUSTOMER_ID = NUM_COL1_NEW_LIST(I),
BILL_TO_SITE_USE_ID = NUM_COL2_NEW_LIST(I)
WHERE BILL_TO_CUSTOMER_ID = NUM_COL1_ORIG_LIST(I)
AND BILL_TO_SITE_USE_ID = NUM_COL2_ORIG_LIST(I);
UPDATE FII_AR_AGING_DISPUTES yt
SET BILL_TO_CUSTOMER_ID = NUM_COL1_NEW_LIST(I),
BILL_TO_SITE_USE_ID = NUM_COL2_NEW_LIST(I)
WHERE BILL_TO_CUSTOMER_ID = NUM_COL1_ORIG_LIST(I)
AND BILL_TO_SITE_USE_ID = NUM_COL2_ORIG_LIST(I);
arp_message.set_name('AR','AR_ROWS_UPDATED');
SELECT distinct m.CUSTOMER_MERGE_HEADER_ID,
yt.cust_account_id,
yt.site_use_id,
yt.cust_account_id,
yt.site_use_id,
m.CUSTOMER_ID,
m.customer_site_id
FROM FII_COLLECTORS yt,
ra_customer_merges m
WHERE (yt.cust_account_id = m.duplicate_id
AND yt.site_use_id = m.duplicate_site_id)
AND (m.customer_id, m.customer_site_id) not in
(select cust_account_id, site_use_id
from fii_collectors)
AND m.process_flag = 'N'
AND m.request_id = req_id
AND m.set_number = set_num;
CURSOR deleted_records IS
SELECT distinct m.CUSTOMER_MERGE_HEADER_ID,
yt.cust_account_id,
yt.site_use_id,
yt.party_id,
yt.collector_id
FROM FII_COLLECTORS yt,
ra_customer_merges m
WHERE yt.cust_account_id = m.duplicate_id
AND m.customer_id in
(select cust_account_id
from fii_collectors)
AND m.process_flag = 'N'
AND m.request_id = req_id
AND m.set_number = set_num;
The following code will update records, which when updated with the surviving account/site_use
will not result in a primary key violation, since the surviving account/site_use combination
is new to fii_collectors.
*/
open merged_records;
INSERT INTO HZ_CUSTOMER_MERGE_LOG (
MERGE_LOG_ID,
TABLE_NAME,
MERGE_HEADER_ID,
PRIMARY_KEY_ID,
PRIMARY_KEY_ID1, PRIMARY_KEY_ID2,
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,
'FII_COLLECTORS',
MERGE_HEADER_ID_LIST(I),
null,
PRIMARY_KEY_ID1_LIST(I), PRIMARY_KEY_ID2_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 FII_COLLECTORS yt
SET CUST_ACCOUNT_ID = NUM_COL1_NEW_LIST(I),
SITE_USE_ID = NUM_COL2_NEW_LIST(I)
WHERE CUST_ACCOUNT_ID = PRIMARY_KEY_ID1_LIST(I)
AND SITE_USE_ID = PRIMARY_KEY_ID2_LIST(I);
arp_message.set_name('AR','AR_ROWS_UPDATED');
The following code will delete records, which if updated with the surviving account/site_use
would have resulted in a primary key violation, since the surviving account/site_use combination
already exists in fii_collectors.
*/
l_last_fetch := FALSE;
open deleted_records;
FETCH deleted_records BULK COLLECT INTO
MERGE_HEADER_ID_LIST,
PRIMARY_KEY_ID1_LIST,
PRIMARY_KEY_ID2_LIST,
PARTY_ID_LIST,
COLLECTOR_ID_LIST;
IF deleted_records%NOTFOUND THEN
l_last_fetch := TRUE;
INSERT INTO HZ_CUSTOMER_MERGE_LOG (
MERGE_LOG_ID,
TABLE_NAME,
MERGE_HEADER_ID,
PRIMARY_KEY_ID1, PRIMARY_KEY_ID2,
DEL_COL1,
DEL_COL2,
ACTION_FLAG,
REQUEST_ID,
CREATED_BY,
CREATION_DATE,
LAST_UPDATE_LOGIN,
LAST_UPDATE_DATE,
LAST_UPDATED_BY
) VALUES (HZ_CUSTOMER_MERGE_LOG_s.nextval,
'FII_COLLECTORS',
MERGE_HEADER_ID_LIST(I),
PRIMARY_KEY_ID1_LIST(I), PRIMARY_KEY_ID2_LIST(I),
PARTY_ID_LIST(I),
COLLECTOR_ID_LIST(I),
'D',
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
);
DELETE FROM FII_COLLECTORS
WHERE CUST_ACCOUNT_ID = PRIMARY_KEY_ID1_LIST(I);
SELECT M.Customer_Merge_Header_ID,
CA.Cust_Account_ID,
CA.Account_Owner_Party_ID,
CA.Parent_Party_ID
FROM FII_Cust_Accounts CA,
RA_Customer_Merges M
WHERE CA.Cust_Account_ID = M.Duplicate_ID
AND M.Process_Flag = 'N'
AND M.Request_ID = Req_ID
AND M.Set_Number = Set_Num
AND M.Delete_Duplicate_Flag = 'Y';
IF Process_Mode <> 'LOCK' THEN --Process_Mode = 'UPDATE'
ARP_MESSAGE.SET_NAME('FII','FII_DELETING_TABLE');
INSERT INTO HZ_Customer_Merge_Log(
MERGE_LOG_ID,
TABLE_NAME,
MERGE_HEADER_ID,
PRIMARY_KEY_ID1,
PRIMARY_KEY_ID2,
PRIMARY_KEY_ID3,
DEL_COL1,
DEL_COL2,
DEL_COL3,
ACTION_FLAG,
REQUEST_ID,
CREATED_BY,
CREATION_DATE,
LAST_UPDATE_LOGIN,
LAST_UPDATE_DATE,
LAST_UPDATED_BY)
VALUES (
HZ_Customer_Merge_Log_S.nextval,
'FII_CUST_ACCOUNTS',
Merge_Header_ID_List(i),
Cust_Account_ID_List(i),
Account_Owner_Party_ID_List(i),
Parent_Party_ID_List(i),
Cust_Account_ID_List(i),
Account_Owner_Party_ID_List(i),
Parent_Party_ID_List(i),
'D',
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);
DELETE FROM FII_Cust_Accounts
WHERE Cust_Account_ID = Cust_Account_ID_List(i)
AND Account_Owner_Party_ID = Account_Owner_Party_ID_List(i)
AND Parent_Party_ID = Parent_Party_ID_List(i);
ARP_MESSAGE.SET_NAME('FII','FII_ROWS_DELETED');