The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT 1
FROM oks_billing_profiles_b bpe
WHERE bpe.dependent_cust_acct_id1 IN (SELECT cme.duplicate_id
FROM ra_customer_merges cme
WHERE cme.process_flag = 'N'
AND cme.request_id = req_id
AND cme.set_number = set_number
)
FOR UPDATE NOWAIT;
SELECT distinct CUSTOMER_MERGE_HEADER_ID
,ID
,DEPENDENT_CUST_ACCT_ID1
,OBJECT_VERSION_NUMBER
FROM OKS_BILLING_PROFILES_B yt, ra_customer_merges m
WHERE (
yt.DEPENDENT_CUST_ACCT_ID1 = m.DUPLICATE_ID
OR yt.OBJECT_VERSION_NUMBER = m.DUPLICATE_SITE_ID
) AND m.process_flag = 'N'
AND m.request_id = req_id
AND m.set_number = set_number;
INSERT INTO HZ_CUSTOMER_MERGE_LOG (
MERGE_LOG_ID,
TABLE_NAME,
MERGE_HEADER_ID,
PRIMARY_KEY_ID1,
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,
'OKS_BILLING_PROFILES_B',
MERGE_HEADER_ID_LIST(I),
PRIMARY_KEY_ID1_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 OKS_BILLING_PROFILES_B yt SET
DEPENDENT_CUST_ACCT_ID1=NUM_COL1_NEW_LIST(I)
,OBJECT_VERSION_NUMBER=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 ID=PRIMARY_KEY_ID1_LIST(I)
;
arp_message.set_name('AR','AR_ROWS_UPDATED');
INSERT INTO HZ_CUSTOMER_MERGE_LOG (
MERGE_LOG_ID,
MERGE_HEADER_ID,
TABLE_NAME,
PRIMARY_KEY_ID,
NUM_COL1_ORIG,
NUM_COL1_NEW,
NUM_COL2_ORIG,
NUM_COL2_NEW,
REQUEST_ID
) SELECT HZ_CUSTOMER_MERGE_LOG_s.nextval,
CUSTOMER_MERGE_HEADER_ID,
'OKS_BILLING_PROFILES_B',
ID,
DEPENDENT_CUST_ACCT_ID1,
decode(yt.DEPENDENT_CUST_ACCT_ID1,m.DUPLICATE_ID,m.CUSTOMER_ID,yt.DEPENDENT_CUST_ACCT_ID1),
OBJECT_VERSION_NUMBER,
OBJECT_VERSION_NUMBER,
request_id
FROM OKS_BILLING_PROFILES_B yt, ra_customer_merges m
WHERE (
yt.DEPENDENT_CUST_ACCT_ID1 = m.DUPLICATE_ID
-- OR yt.BILL_TO_ADDRESS_ID1 = m.DUPLICATE_SITE_ID
) AND m.process_flag = 'N'
AND m.request_id = req_id
AND m.set_number = set_number;
UPDATE OKS_BILLING_PROFILES_B yt SET (
DEPENDENT_CUST_ACCT_ID1, OBJECT_VERSION_NUMBER) = (
SELECT NUM_COL1_NEW, NUM_COL2_NEW
FROM HZ_CUSTOMER_MERGE_LOG l
WHERE l.REQUEST_ID = req_id
AND l.TABLE_NAME = 'OKS_BILLING_PROFILES_B'
AND l.PRIMARY_KEY_ID = ID
AND DEPENDENT_CUST_ACCT_ID1 = NUM_COL1_ORIG
and rownum <2
)
, LAST_UPDATE_DATE=SYSDATE
, last_updated_by=arp_standard.profile.user_id
, last_update_login=arp_standard.profile.last_update_login
WHERE (ID) in (
SELECT PRIMARY_KEY_ID
FROM HZ_CUSTOMER_MERGE_LOG l1, RA_CUSTOMER_MERGES h
WHERE h.CUSTOMER_MERGE_HEADER_ID = l1.MERGE_HEADER_ID
AND l1.TABLE_NAME = 'OKS_BILLING_PROFILES_B'
AND l1.REQUEST_ID = req_id
AND h.set_number = set_number);
arp_message.set_name('AR','AR_ROWS_UPDATED');
SELECT distinct CUSTOMER_MERGE_HEADER_ID
,ID
,BILL_TO_ADDRESS_ID1
,OBJECT_VERSION_NUMBER
FROM OKS_BILLING_PROFILES_B yt, ra_customer_merges m
WHERE (
yt.BILL_TO_ADDRESS_ID1 = m.DUPLICATE_SITE_ID
) AND m.process_flag = 'N'
AND m.request_id = req_id
AND m.set_number = set_number;
SELECT distinct CUSTOMER_MERGE_HEADER_ID
,ID
,QUOTE_TO_SITE_ID
,OBJECT_VERSION_NUMBER
FROM OKS_K_HEADERS_B hdr, ra_customer_merges m
WHERE (
hdr.QUOTE_TO_SITE_ID = m.DUPLICATE_ADDRESS_ID
) AND m.process_flag = 'N'
AND m.request_id = req_id
AND m.set_number = set_number;
INSERT INTO HZ_CUSTOMER_MERGE_LOG (
MERGE_LOG_ID,
TABLE_NAME,
MERGE_HEADER_ID,
PRIMARY_KEY_ID1,
NUM_COL3_ORIG,
NUM_COL3_NEW,
NUM_COL4_ORIG,
NUM_COL4_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,
'OKS_BILLING_PROFILES_B',
MERGE_HEADER_ID_LIST(I),
PRIMARY_KEY_ID1_LIST(I),
NUM_COL3_ORIG_LIST(I),
NUM_COL3_NEW_LIST(I),
NUM_COL4_ORIG_LIST(I),
NUM_COL4_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 OKS_BILLING_PROFILES_B yt SET
BILL_TO_ADDRESS_ID1=NUM_COL3_NEW_LIST(I)
,OBJECT_VERSION_NUMBER=NUM_COL4_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 ID=PRIMARY_KEY_ID1_LIST(I)
;
arp_message.set_name('AR','AR_ROWS_UPDATED');
INSERT INTO HZ_CUSTOMER_MERGE_LOG (
MERGE_LOG_ID,
TABLE_NAME,
MERGE_HEADER_ID,
PRIMARY_KEY_ID1,
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,
'OKS_K_HEADERS_B',
MERGE_HEADER_ID_LIST_QUOTE(I),
PRIMARY_KEY_ID1_LIST_QUOTE(I),
NUM_COL1_ORIG_LIST_QUOTE(I),
NUM_COL1_NEW_LIST_QUOTE(I),
NUM_COL2_ORIG_LIST_QUOTE(I),
NUM_COL2_NEW_LIST_QUOTE(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 OKS_K_HEADERS_B yt SET
QUOTE_TO_SITE_ID=NUM_COL1_NEW_LIST_QUOTE(I)
,OBJECT_VERSION_NUMBER=NUM_COL2_NEW_LIST_QUOTE(I)
, LAST_UPDATE_DATE=SYSDATE
, last_updated_by=arp_standard.profile.user_id
, last_update_login=arp_standard.profile.last_update_login
WHERE ID=PRIMARY_KEY_ID1_LIST_QUOTE(I);
arp_message.set_name('AR','AR_ROWS_UPDATED');
SELECT cmh.merge_reason_code
FROM ra_customer_merge_headers cmh
,ra_customer_merges cme
WHERE cmh.customer_merge_header_id = cme.customer_merge_header_id
AND cme.request_id = req_id
AND cme.set_number = set_number
AND cme.process_flag = 'N'
;
SELECT customer_id, duplicate_id
FROM ra_customer_merges cme
WHERE cme.request_id = req_id
AND cme.set_number = set_number
AND cme.process_flag = 'N'
;
SELECT party_id
FROM hz_cust_accounts
WHERE cust_account_id = b_account_id
;
SELECT 1
FROM oks_billing_profiles_b bpe
WHERE bpe.owned_party_id1 = b_party_id
;