[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT distinct CUSTOMER_MERGE_HEADER_ID
,cash_receipt_id
,pay_from_customer
,customer_site_use_id
FROM AR_CASH_RECEIPTS yt, ra_customer_merges m
WHERE ( (yt.pay_from_customer = m.DUPLICATE_ID AND
nvl(yt.customer_site_use_id,m.DUPLICATE_SITE_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,
'AR_CASH_RECEIPTS',
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 AR_CASH_RECEIPTS yt SET
pay_from_customer=NUM_COL1_NEW_LIST(I)
,customer_site_use_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 cash_receipt_id=PRIMARY_KEY_ID_LIST(I)
;
arp_message.set_name('AR','AR_ROWS_UPDATED');
SELECT distinct CUSTOMER_MERGE_HEADER_ID
,payment_schedule_id
,yt.customer_id
,customer_site_use_id
FROM AR_PAYMENT_SCHEDULES yt, ra_customer_merges m
WHERE ( yt.customer_id = m.DUPLICATE_ID AND
nvl(yt.customer_site_use_id, m.DUPLICATE_SITE_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,
'AR_PAYMENT_SCHEDULES',
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 AR_PAYMENT_SCHEDULES yt SET
customer_id=NUM_COL1_NEW_LIST(I)
,customer_site_use_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 payment_schedule_id=PRIMARY_KEY_ID_LIST(I)
;
arp_message.set_name('AR','AR_ROWS_UPDATED');
SELECT distinct CUSTOMER_MERGE_HEADER_ID
,customer_trx_id
,bill_to_customer_id
,bill_to_site_use_id
,paying_customer_id
,paying_site_use_id
,ship_to_customer_id
,ship_to_site_use_id
,sold_to_customer_id
,sold_to_site_use_id
FROM RA_CUSTOMER_TRX 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)
OR (yt.paying_customer_id = m.DUPLICATE_ID AND
yt.paying_site_use_id = m.DUPLICATE_SITE_ID)
OR (yt.ship_to_customer_id = m.DUPLICATE_ID AND
nvl(yt.ship_to_site_use_id,m.DUPLICATE_SITE_ID) = m.DUPLICATE_SITE_ID)
OR (yt.sold_to_customer_id = m.DUPLICATE_ID AND
nvl(yt.sold_to_site_use_id,m.DUPLICATE_SITE_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,
NUM_COL3_ORIG,
NUM_COL3_NEW,
NUM_COL4_ORIG,
NUM_COL4_NEW,
NUM_COL5_ORIG,
NUM_COL5_NEW,
NUM_COL6_ORIG,
NUM_COL6_NEW,
NUM_COL7_ORIG,
NUM_COL7_NEW,
NUM_COL8_ORIG,
NUM_COL8_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,
'RA_CUSTOMER_TRX',
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),
NUM_COL3_ORIG_LIST(I),
NUM_COL3_NEW_LIST(I),
NUM_COL4_ORIG_LIST(I),
NUM_COL4_NEW_LIST(I),
NUM_COL5_ORIG_LIST(I),
NUM_COL5_NEW_LIST(I),
NUM_COL6_ORIG_LIST(I),
NUM_COL6_NEW_LIST(I),
NUM_COL7_ORIG_LIST(I),
NUM_COL7_NEW_LIST(I),
NUM_COL8_ORIG_LIST(I),
NUM_COL8_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 RA_CUSTOMER_TRX yt SET
bill_to_customer_id=NUM_COL1_NEW_LIST(I)
,bill_to_site_use_id=NUM_COL2_NEW_LIST(I)
,paying_customer_id=NUM_COL3_NEW_LIST(I)
,paying_site_use_id=NUM_COL4_NEW_LIST(I)
,ship_to_customer_id=NUM_COL5_NEW_LIST(I)
,ship_to_site_use_id=NUM_COL6_NEW_LIST(I)
,sold_to_customer_id=NUM_COL7_NEW_LIST(I)
,sold_to_site_use_id=NUM_COL8_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 customer_trx_id=PRIMARY_KEY_ID_LIST(I)
;
arp_message.set_name('AR','AR_ROWS_UPDATED');
SELECT distinct CUSTOMER_MERGE_HEADER_ID
,rai.ROWID
,ORIG_SYSTEM_BILL_CUSTOMER_ID
,ORIG_SYSTEM_SHIP_CUSTOMER_ID
,ORIG_SYSTEM_SOLD_CUSTOMER_ID
,ORIG_SYSTEM_BILL_ADDRESS_ID
,ORIG_SYSTEM_SHIP_ADDRESS_ID
,ORIG_SYSTEM_BILL_CUSTOMER_REF
,ORIG_SYSTEM_SHIP_CUSTOMER_REF
,ORIG_SYSTEM_SOLD_CUSTOMER_REF
,ORIG_SYSTEM_BILL_ADDRESS_REF
,ORIG_SYSTEM_SHIP_ADDRESS_REF
,m.CUSTOMER_REF
from ra_interface_lines rai,
ra_customer_merges m
where nvl(rai.interface_status,'N') <> 'P' /* bug 1611619 : check interface_status */
and (
m.duplicate_id = rai.orig_system_bill_customer_id
or (m.duplicate_ref = rai.orig_system_bill_customer_ref)
or (m.duplicate_address_id = rai.orig_system_bill_address_id)
or (m.duplicate_id = rai.orig_system_ship_customer_id)
or (m.duplicate_ref = rai.orig_system_ship_customer_ref)
or (m.duplicate_address_id = rai.orig_system_ship_address_id)
or (m.duplicate_id = rai.orig_system_sold_customer_id)
or (m.duplicate_ref = rai.orig_system_sold_customer_ref)
or (rai.orig_system_bill_address_ref IN ( select
ra.orig_system_reference
from hz_cust_acct_sites ra
where m.duplicate_address_id = ra.cust_acct_site_id)
)
or (rai.orig_system_ship_address_ref IN (select
ra.orig_system_reference
from hz_cust_acct_sites ra
where m.duplicate_address_id = ra.cust_acct_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_KEY1,
NUM_COL1_ORIG,
NUM_COL1_NEW,
NUM_COL2_ORIG,
NUM_COL2_NEW,
NUM_COL3_ORIG,
NUM_COL3_NEW,
NUM_COL4_ORIG,
NUM_COL4_NEW,
NUM_COL5_ORIG,
NUM_COL5_NEW,
VCHAR_COL1_ORIG,
VCHAR_COL1_NEW,
VCHAR_COL2_ORIG,
VCHAR_COL2_NEW,
VCHAR_COL3_ORIG,
VCHAR_COL3_NEW,
VCHAR_COL4_ORIG,
VCHAR_COL4_NEW,
VCHAR_COL5_ORIG,
VCHAR_COL5_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,
'RA_INTERFACE_LINES',
MERGE_HEADER_ID_LIST(I),
PRIMARY_KEY1_LIST(I),
NUM_COL1_ORIG_LIST(I),
NUM_COL1_NEW_LIST(I),
NUM_COL2_ORIG_LIST(I),
NUM_COL2_NEW_LIST(I),
NUM_COL3_ORIG_LIST(I),
NUM_COL3_NEW_LIST(I),
NUM_COL4_ORIG_LIST(I),
NUM_COL4_NEW_LIST(I),
NUM_COL5_ORIG_LIST(I),
NUM_COL5_NEW_LIST(I),
VCHAR_COL1_ORIG_LIST(I),
VCHAR_COL1_NEW_LIST(I),
VCHAR_COL2_ORIG_LIST(I),
VCHAR_COL2_NEW_LIST(I),
VCHAR_COL3_ORIG_LIST(I),
VCHAR_COL3_NEW_LIST(I),
VCHAR_COL4_ORIG_LIST(I),
VCHAR_COL4_NEW_LIST(I),
VCHAR_COL5_ORIG_LIST(I),
VCHAR_COL5_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 RA_INTERFACE_LINES yt SET
ORIG_SYSTEM_BILL_CUSTOMER_ID=NUM_COL1_NEW_LIST(I)
,ORIG_SYSTEM_SHIP_CUSTOMER_ID=NUM_COL2_NEW_LIST(I)
,ORIG_SYSTEM_SOLD_CUSTOMER_ID=NUM_COL3_NEW_LIST(I)
,ORIG_SYSTEM_BILL_ADDRESS_ID=NUM_COL4_NEW_LIST(I)
,ORIG_SYSTEM_SHIP_ADDRESS_ID=NUM_COL5_NEW_LIST(I)
,ORIG_SYSTEM_BILL_CUSTOMER_REF=VCHAR_COL1_NEW_LIST(I)
,ORIG_SYSTEM_SHIP_CUSTOMER_REF=VCHAR_COL2_NEW_LIST(I)
,ORIG_SYSTEM_SOLD_CUSTOMER_REF=VCHAR_COL3_NEW_LIST(I)
,ORIG_SYSTEM_BILL_ADDRESS_REF=VCHAR_COL4_NEW_LIST(I)
,ORIG_SYSTEM_SHIP_ADDRESS_REF=VCHAR_COL5_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 ROWID=PRIMARY_KEY1_LIST(I)
;
arp_message.set_name('AR','AR_ROWS_UPDATED');
SELECT distinct CUSTOMER_MERGE_HEADER_ID
,line_id
,third_party_id
,third_party_sub_id
FROM AR_DISTRIBUTIONS yt, ra_customer_merges m
WHERE ( (yt.third_party_id = m.DUPLICATE_ID AND
nvl(yt.third_party_sub_id,m.DUPLICATE_SITE_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,
'AR_DISTRIBUTIONS',
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 AR_DISTRIBUTIONS yt SET
third_party_id=NUM_COL1_NEW_LIST(I)
,third_party_sub_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 line_id=PRIMARY_KEY_ID_LIST(I)
;
arp_message.set_name('AR','AR_ROWS_UPDATED');