[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT 1
FROM okc_k_party_roles_b kpr
WHERE kpr.jtot_object1_code IN (SELECT ojt.object_code
FROM jtf_objects_b ojt
,jtf_object_usages oue
WHERE ojt.object_code = oue.object_code
AND oue.object_user_code = b_object_use
)
AND kpr.object1_id1 IN (SELECT to_char(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 1
FROM okc_rules_b rle
WHERE rle.jtot_object1_code IN (SELECT ojt.object_code
FROM jtf_objects_b ojt
,jtf_object_usages oue
WHERE ojt.object_code = oue.object_code
AND oue.object_user_code = b_object_use
)
AND rle.object1_id1 IN (SELECT to_char(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 1
FROM okc_rules_b rle
WHERE rle.jtot_object2_code IN (SELECT ojt.object_code
FROM jtf_objects_b ojt
,jtf_object_usages oue
WHERE ojt.object_code = oue.object_code
AND oue.object_user_code = b_object_use
)
AND rle.object2_id1 IN (SELECT to_char(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 1
FROM okc_rules_b rle
WHERE rle.jtot_object3_code IN (SELECT ojt.object_code
FROM jtf_objects_b ojt
,jtf_object_usages oue
WHERE ojt.object_code = oue.object_code
AND oue.object_user_code = b_object_use
)
AND rle.object3_id1 IN (SELECT to_char(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 1
FROM okc_k_items cim
WHERE cim.jtot_object1_code IN (SELECT ojt.object_code
FROM jtf_objects_b ojt
,jtf_object_usages oue
WHERE ojt.object_code = oue.object_code
AND oue.object_user_code = b_object_use
)
AND cim.object1_id1 IN (SELECT to_char(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 kpr.dnz_chr_id
FROM okc_k_party_roles_b kpr
WHERE kpr.jtot_object1_code IN (SELECT ojt.object_code
FROM jtf_objects_b ojt
,jtf_object_usages oue
WHERE ojt.object_code = oue.object_code
AND oue.object_user_code = c_party
)
AND kpr.object1_id1 = to_char(b_party_id)
;
select object1_id1, object2_id1, object3_id1
from okc_rules_b
where dnz_chr_id = b_dnz_chr_id
and rule_information_category IN ('BTO', 'STO')
union
select to_char(bill_to_site_use_id), to_char(ship_to_site_use_id), to_char(cust_acct_id)
from okc_k_headers_b
where id = b_dnz_chr_id
union
select to_char(bill_to_site_use_id), to_char(ship_to_site_use_id), to_char(cust_acct_id)
from okc_k_lines_b
where chr_id = b_dnz_chr_id ;
select count(*)
from (select okr1.object1_id1 from okc_rules_b okr1
where okr1.object1_id1 in (
select TO_CHAR(cust_account_id) from hz_cust_accounts where party_id = l_source_party_id
) and okr1.dnz_chr_id = b_chr_id
and okr1.rule_information_category IN ('BTO', 'STO')
union
select okr2.object2_id1 from okc_rules_b okr2
where okr2.object2_id1 in (
select TO_CHAR(cust_account_id) from hz_cust_accounts where party_id = l_source_party_id
) and okr2.dnz_chr_id = b_chr_id
and okr2.rule_information_category IN ('BTO', 'STO')
union
select okr3.object3_id1 from okc_rules_b okr3
where okr3.object3_id1 in (
select TO_CHAR(cust_account_id) from hz_cust_accounts where party_id = l_source_party_id
) and okr3.dnz_chr_id = b_chr_id
and okr3.rule_information_category IN ('BTO', 'STO')
union
(select to_char(cust_account_id) from OKX_CUST_SITE_USES_V
where id1 IN (select bill_to_site_use_id
from okc_k_headers_b where id = b_chr_id)
and party_id = l_source_party_id
and SITE_USE_CODE = 'BILL_TO')
union
(select to_char(cust_account_id) from OKX_CUST_SITE_USES_V
where id1 IN (select ship_to_site_use_id
from okc_k_headers_b where id = b_chr_id )
and party_id =l_source_party_id
and SITE_USE_CODE = 'SHIP_TO')
union
(select to_char(cust_account_id) from OKX_CUST_SITE_USES_V
where id1 IN (select bill_to_site_use_id
from okc_k_lines_b where dnz_chr_id = b_chr_id)
and party_id = l_source_party_id
and SITE_USE_CODE = 'BILL_TO')
union
(select to_char(cust_account_id) from OKX_CUST_SITE_USES_V
where id1 IN (select ship_to_site_use_id
from okc_k_lines_b where dnz_chr_id = b_chr_id )
and party_id =l_source_party_id
and SITE_USE_CODE = 'SHIP_TO')
union
(select to_char(cust_account_id) from hz_cust_accounts
where cust_account_id IN (select cust_acct_id
from okc_k_lines_b where dnz_chr_id = b_chr_id )
-- For Bug# 6861077
and party_id =l_source_party_id)
union
(select to_char(cust_account_id) from hz_cust_accounts
where cust_account_id IN (select cust_acct_id
from okc_k_headers_b where id = b_chr_id )
-- Changes for Bug# 6861077 Ends
and party_id =l_source_party_id));
SELECT contract_number, contract_number_modifier
FROM okc_k_headers_b
WHERE id=b_chr_id;
SELECT kpr.id
FROM okc_k_party_roles_b kpr
WHERE kpr.object1_id1 = l_target_party_id
AND kpr.dnz_chr_id = b_chr_id;
arp_message.set_line('Contract ' || l_contract_number || ' should be manually updated');
UPDATE okc_k_party_roles_b kpr
SET kpr.object1_id1 = l_target_party_id
,kpr.object_version_number = kpr.object_version_number + 1
,kpr.last_update_date = SYSDATE
,kpr.last_updated_by = arp_standard.profile.user_id
,kpr.last_update_login = arp_standard.profile.last_update_login
WHERE kpr.object1_id1 = l_source_party_id
AND kpr.dnz_chr_id = l_chr_id;
arp_message.set_line('Contract ' || l_contract_number || ' is updated');
INSERT INTO HZ_CUSTOMER_MERGE_LOG (
MERGE_LOG_ID,
TABLE_NAME,
PRIMARY_KEY_ID1
) SELECT HZ_CUSTOMER_MERGE_LOG_s.nextval,
'OKC_K_PARTY_ROLES_B',
l_log_party_id
FROM DUAL;
UPDATE HZ_CUSTOMER_MERGE_LOG hz
SET hz.MERGE_HEADER_ID = (SELECT distinct CUSTOMER_MERGE_HEADER_ID
FROM ra_customer_merges rcm
WHERE rcm.request_id = req_id
and rcm.set_number = set_number
and rcm.process_flag = 'N')
,hz.VCHAR_COL1_ORIG = l_source_party_id
,hz.VCHAR_COL1_NEW = l_target_party_id
,hz.REQUEST_ID = req_id
,hz.CREATED_BY = hz_utility_pub.CREATED_BY
,hz.CREATION_DATE = hz_utility_pub.CREATION_DATE
,hz.LAST_UPDATE_LOGIN = hz_utility_pub.LAST_UPDATE_LOGIN
,hz.LAST_UPDATE_DATE = hz_utility_pub.LAST_UPDATE_DATE
,hz.LAST_UPDATED_BY = hz_utility_pub.LAST_UPDATED_BY
,hz.ACTION_FLAG = 'U'
WHERE hz.PRIMARY_KEY_ID1 = l_log_party_id;
-- Fix for bug 4105272 Insert into okc_k_vers_numbers_h
INSERT INTO OKC_K_VERS_NUMBERS_H(
chr_id,
major_version,
minor_version,
object_version_number,
created_by,
creation_date,
last_updated_by,
last_update_date,
last_update_login)
(SELECT
chr_id,
major_version,
minor_version,
object_version_number,
created_by,
creation_date,
last_updated_by,
last_update_date,
last_update_login
FROM OKC_K_VERS_NUMBERS
WHERE chr_id = l_chr_id);
UPDATE okc_k_vers_numbers ver
SET ver.minor_version = ver.minor_version + 1
,ver.object_version_number = ver.object_version_number + 1
,ver.last_update_date = SYSDATE
,ver.last_updated_by = arp_standard.profile.user_id
,ver.last_update_login = arp_standard.profile.last_update_login
WHERE chr_id = l_chr_id;
arp_message.set_line('Please update the above mentioned contracts and run customer merge again');
SELECT distinct(okl.chr_id)
FROM okc_k_lines_b okl
WHERE okl.cust_acct_id IN (SELECT DISTINCT (rcm.duplicate_id)
FROM ra_customer_merges rcm
WHERE rcm.process_flag = 'N'
AND rcm.request_id = req_id
AND rcm.set_number = set_number);
INSERT INTO HZ_CUSTOMER_MERGE_LOG (
MERGE_LOG_ID,
TABLE_NAME,
MERGE_HEADER_ID,
PRIMARY_KEY_ID1,
VCHAR_COL1_ORIG,
VCHAR_COL1_NEW,
REQUEST_ID,
ACTION_FLAG,
CREATED_BY,
CREATION_DATE,
LAST_UPDATE_LOGIN,
LAST_UPDATE_DATE,
LAST_UPDATED_BY
) SELECT HZ_CUSTOMER_MERGE_LOG_s.nextval,
'OKC_K_PARTY_ROLES_B',
CUSTOMER_MERGE_HEADER_ID,
kpr.ID,
kpr.object1_id1,
to_char(rcm.customer_id),
req_id,
'U',
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
FROM OKC_K_PARTY_ROLES_B kpr, ra_customer_merges rcm
WHERE (
kpr.object1_id1 = to_char(rcm.duplicate_id)
) AND rcm.process_flag = 'N'
AND rcm.request_id = req_id
AND rcm.set_number = set_number
AND kpr.object1_id1 IN (SELECT to_char(rcm.duplicate_id)
FROM ra_customer_merges rcm
WHERE rcm.process_flag = 'N'
AND rcm.request_id = req_id
AND rcm.set_number = set_number)
AND kpr.jtot_object1_code IN (SELECT ojt.object_code
FROM jtf_objects_b ojt
,jtf_object_usages oue
WHERE ojt.object_code = oue.object_code
AND oue.object_user_code = c_account);
UPDATE okc_k_party_roles_b kpr
SET kpr.object1_id1 = (SELECT DISTINCT to_char(rcm.customer_id)
FROM ra_customer_merges rcm
WHERE kpr.object1_id1 = rcm.duplicate_id
AND rcm.process_flag = 'N'
AND rcm.request_id = req_id
AND rcm.set_number = set_number)
,kpr.object_version_number = kpr.object_version_number + 1
,kpr.last_update_date = SYSDATE
,kpr.last_updated_by = arp_standard.profile.user_id
,kpr.last_update_login = arp_standard.profile.last_update_login
WHERE kpr.object1_id1 IN (SELECT to_char(rcm.duplicate_id)
FROM ra_customer_merges rcm
WHERE rcm.process_flag = 'N'
AND rcm.request_id = req_id
AND rcm.set_number = set_number)
AND kpr.jtot_object1_code IN (SELECT ojt.object_code
FROM jtf_objects_b ojt
,jtf_object_usages oue
WHERE ojt.object_code = oue.object_code
AND oue.object_user_code = c_account)
;
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,
VCHAR_COL1_ORIG,
VCHAR_COL1_NEW,
REQUEST_ID,
ACTION_FLAG,
CREATED_BY,
CREATION_DATE,
LAST_UPDATE_LOGIN,
LAST_UPDATE_DATE,
LAST_UPDATED_BY
) SELECT HZ_CUSTOMER_MERGE_LOG_s.nextval,
'OKC_RULES_B',
CUSTOMER_MERGE_HEADER_ID,
rle.ID,
rle.object1_id1,
to_char(rcm.customer_id),
req_id,
'U',
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
FROM OKC_RULES_B rle, ra_customer_merges rcm
WHERE (
rle.object1_id1 = to_char(rcm.duplicate_id)
) AND rcm.process_flag = 'N'
AND rcm.request_id = req_id
AND rcm.set_number = set_number
AND rle.object1_id1 IN (SELECT to_char(rcm.duplicate_id)
FROM ra_customer_merges rcm
WHERE rcm.process_flag = 'N'
AND rcm.request_id = req_id
AND rcm.set_number = set_number)
AND rle.jtot_object1_code IN (SELECT ojt.object_code
FROM jtf_objects_b ojt
,jtf_object_usages oue
WHERE ojt.object_code = oue.object_code
AND oue.object_user_code = c_account);
UPDATE okc_rules_b rle
SET rle.object1_id1 = (SELECT DISTINCT to_char(rcm.customer_id)
FROM ra_customer_merges rcm
WHERE rle.object1_id1 = rcm.duplicate_id
AND rcm.process_flag = 'N'
AND rcm.request_id = req_id
AND rcm.set_number = set_number)
,rle.object_version_number = rle.object_version_number + 1
,rle.last_update_date = SYSDATE
,rle.last_updated_by = arp_standard.profile.user_id
,rle.last_update_login = arp_standard.profile.last_update_login
WHERE rle.object1_id1 IN (SELECT to_char(rcm.duplicate_id)
FROM ra_customer_merges rcm
WHERE rcm.process_flag = 'N'
AND rcm.request_id = req_id
AND rcm.set_number = set_number)
AND rle.jtot_object1_code IN (SELECT ojt.object_code
FROM jtf_objects_b ojt
,jtf_object_usages oue
WHERE ojt.object_code = oue.object_code
AND oue.object_user_code = c_account)
;
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,
VCHAR_COL1_ORIG,
VCHAR_COL1_NEW,
REQUEST_ID,
ACTION_FLAG,
CREATED_BY,
CREATION_DATE,
LAST_UPDATE_LOGIN,
LAST_UPDATE_DATE,
LAST_UPDATED_BY
) SELECT HZ_CUSTOMER_MERGE_LOG_s.nextval,
'OKC_RULES_B',
CUSTOMER_MERGE_HEADER_ID,
rle.ID,
rle.object2_id1,
to_char(rcm.customer_id),
req_id,
'U',
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
FROM OKC_RULES_B rle, ra_customer_merges rcm
WHERE (
rle.object2_id1 = to_char(rcm.duplicate_id)
) AND rcm.process_flag = 'N'
AND rcm.request_id = req_id
AND rcm.set_number = set_number
AND rle.object2_id1 IN (SELECT to_char(rcm.duplicate_id)
FROM ra_customer_merges rcm
WHERE rcm.process_flag = 'N'
AND rcm.request_id = req_id
AND rcm.set_number = set_number)
AND rle.jtot_object2_code IN (SELECT ojt.object_code
FROM jtf_objects_b ojt
,jtf_object_usages oue
WHERE ojt.object_code = oue.object_code
AND oue.object_user_code = c_account);
UPDATE okc_rules_b rle
SET rle.object2_id1 = (SELECT DISTINCT to_char(rcm.customer_id)
FROM ra_customer_merges rcm
WHERE rle.object2_id1 = rcm.duplicate_id
AND rcm.process_flag = 'N'
AND rcm.request_id = req_id
AND rcm.set_number = set_number)
,rle.object_version_number = rle.object_version_number + 1
,rle.last_update_date = SYSDATE
,rle.last_updated_by = arp_standard.profile.user_id
,rle.last_update_login = arp_standard.profile.last_update_login
WHERE rle.object2_id1 IN (SELECT to_char(rcm.duplicate_id)
FROM ra_customer_merges rcm
WHERE rcm.process_flag = 'N'
AND rcm.request_id = req_id
AND rcm.set_number = set_number)
AND rle.jtot_object2_code IN (SELECT ojt.object_code
FROM jtf_objects_b ojt
,jtf_object_usages oue
WHERE ojt.object_code = oue.object_code
AND oue.object_user_code = c_account)
;
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,
VCHAR_COL1_ORIG,
VCHAR_COL1_NEW,
REQUEST_ID,
ACTION_FLAG,
CREATED_BY,
CREATION_DATE,
LAST_UPDATE_LOGIN,
LAST_UPDATE_DATE,
LAST_UPDATED_BY
) SELECT HZ_CUSTOMER_MERGE_LOG_s.nextval,
'OKC_RULES_B',
CUSTOMER_MERGE_HEADER_ID,
rle.ID,
rle.object3_id1,
to_char(rcm.customer_id),
req_id,
'U',
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
FROM OKC_RULES_B rle, ra_customer_merges rcm
WHERE (
rle.object3_id1 = to_char(rcm.duplicate_id)
) AND rcm.process_flag = 'N'
AND rcm.request_id = req_id
AND rcm.set_number = set_number
AND rle.object3_id1 IN (SELECT to_char(rcm.duplicate_id)
FROM ra_customer_merges rcm
WHERE rcm.process_flag = 'N'
AND rcm.request_id = req_id
AND rcm.set_number = set_number)
AND rle.jtot_object3_code IN (SELECT ojt.object_code
FROM jtf_objects_b ojt
,jtf_object_usages oue
WHERE ojt.object_code = oue.object_code
AND oue.object_user_code = c_account);
UPDATE okc_rules_b rle
SET rle.object3_id1 = (SELECT DISTINCT to_char(rcm.customer_id)
FROM ra_customer_merges rcm
WHERE rle.object3_id1 = rcm.duplicate_id
AND rcm.process_flag = 'N'
AND rcm.request_id = req_id
AND rcm.set_number = set_number)
,rle.object_version_number = rle.object_version_number + 1
,rle.last_update_date = SYSDATE
,rle.last_updated_by = arp_standard.profile.user_id
,rle.last_update_login = arp_standard.profile.last_update_login
WHERE rle.object3_id1 IN (SELECT to_char(rcm.duplicate_id)
FROM ra_customer_merges rcm
WHERE rcm.process_flag = 'N'
AND rcm.request_id = req_id
AND rcm.set_number = set_number)
AND rle.jtot_object3_code IN (SELECT ojt.object_code
FROM jtf_objects_b ojt
,jtf_object_usages oue
WHERE ojt.object_code = oue.object_code
AND oue.object_user_code = c_account)
;
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,
VCHAR_COL1_ORIG,
VCHAR_COL1_NEW,
REQUEST_ID,
ACTION_FLAG,
CREATED_BY,
CREATION_DATE,
LAST_UPDATE_LOGIN,
LAST_UPDATE_DATE,
LAST_UPDATED_BY
) SELECT HZ_CUSTOMER_MERGE_LOG_s.nextval,
'OKC_K_HEADERS_B',
CUSTOMER_MERGE_HEADER_ID,
okh.ID,
to_char(okh.cust_acct_id),
to_char(rcm.customer_id),
req_id,
'U',
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
FROM OKC_K_HEADERS_B okh, ra_customer_merges rcm
WHERE (
okh.cust_acct_id = rcm.duplicate_id
) AND rcm.process_flag = 'N'
AND rcm.request_id = req_id
AND rcm.set_number = set_number
AND okh.cust_acct_id IN (SELECT rcm.duplicate_id
FROM ra_customer_merges rcm
WHERE rcm.process_flag = 'N'
AND rcm.request_id = req_id
AND rcm.set_number = set_number);
UPDATE okc_k_headers_b okh
SET okh.cust_acct_id = (SELECT DISTINCT (rcm.customer_id)
FROM ra_customer_merges rcm
WHERE okh.cust_acct_id = rcm.duplicate_id
AND rcm.process_flag = 'N'
AND rcm.request_id = req_id
AND rcm.set_number = set_number)
,okh.object_version_number = okh.object_version_number + 1
,okh.last_update_date = SYSDATE
,okh.last_updated_by = arp_standard.profile.user_id
,okh.last_update_login = arp_standard.profile.last_update_login
WHERE okh.cust_acct_id IN (SELECT DISTINCT (rcm.duplicate_id)
FROM ra_customer_merges rcm
WHERE rcm.process_flag = 'N'
AND rcm.request_id = req_id
AND rcm.set_number = set_number);
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,
VCHAR_COL1_ORIG,
VCHAR_COL1_NEW,
REQUEST_ID,
ACTION_FLAG,
CREATED_BY,
CREATION_DATE,
LAST_UPDATE_LOGIN,
LAST_UPDATE_DATE,
LAST_UPDATED_BY
) SELECT HZ_CUSTOMER_MERGE_LOG_s.nextval,
'OKC_K_LINES_B',
CUSTOMER_MERGE_HEADER_ID,
okl.ID,
to_char(okl.cust_acct_id),
to_char(rcm.customer_id),
req_id,
'U',
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
FROM OKC_K_LINES_B okl, ra_customer_merges rcm
WHERE (
okl.cust_acct_id = rcm.duplicate_id
) AND rcm.process_flag = 'N'
AND rcm.request_id = req_id
AND rcm.set_number = set_number
AND okl.cust_acct_id IN (SELECT rcm.duplicate_id
FROM ra_customer_merges rcm
WHERE rcm.process_flag = 'N'
AND rcm.request_id = req_id
AND rcm.set_number = set_number);
UPDATE okc_k_lines_b okl
SET okl.cust_acct_id = (SELECT DISTINCT (rcm.customer_id)
FROM ra_customer_merges rcm
WHERE okl.cust_acct_id = rcm.duplicate_id
AND rcm.process_flag = 'N'
AND rcm.request_id = req_id
AND rcm.set_number = set_number)
,okl.object_version_number = okl.object_version_number + 1
,okl.last_update_date = SYSDATE
,okl.last_updated_by = arp_standard.profile.user_id
,okl.last_update_login = arp_standard.profile.last_update_login
WHERE okl.cust_acct_id IN (SELECT DISTINCT (rcm.duplicate_id)
FROM ra_customer_merges rcm
WHERE rcm.process_flag = 'N'
AND rcm.request_id = req_id
AND rcm.set_number = set_number);
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,
VCHAR_COL1_ORIG,
VCHAR_COL1_NEW,
REQUEST_ID,
ACTION_FLAG,
CREATED_BY,
CREATION_DATE,
LAST_UPDATE_LOGIN,
LAST_UPDATE_DATE,
LAST_UPDATED_BY
) SELECT HZ_CUSTOMER_MERGE_LOG_s.nextval,
'OKC_K_PARTY_ROLES_B',
CUSTOMER_MERGE_HEADER_ID,
okpr.ID,
to_char(okpr.cust_acct_id),
to_char(rcm.customer_id),
req_id,
'U',
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
FROM OKC_K_PARTY_ROLES_B okpr, ra_customer_merges rcm
WHERE (
okpr.cust_acct_id = rcm.duplicate_id
) AND rcm.process_flag = 'N'
AND rcm.request_id = req_id
AND rcm.set_number = set_number
AND okpr.cust_acct_id IN (SELECT rcm.duplicate_id
FROM ra_customer_merges rcm
WHERE rcm.process_flag = 'N'
AND rcm.request_id = req_id
AND rcm.set_number = set_number);
UPDATE okc_k_party_roles_b okpr
SET okpr.cust_acct_id = (SELECT DISTINCT (rcm.customer_id)
FROM ra_customer_merges rcm
WHERE okpr.cust_acct_id = rcm.duplicate_id
AND rcm.process_flag = 'N'
AND rcm.request_id = req_id
AND rcm.set_number = set_number)
,okpr.object_version_number = okpr.object_version_number + 1
,okpr.last_update_date = SYSDATE
,okpr.last_updated_by = arp_standard.profile.user_id
,okpr.last_update_login = arp_standard.profile.last_update_login
WHERE okpr.cust_acct_id IN (SELECT DISTINCT (rcm.duplicate_id)
FROM ra_customer_merges rcm
WHERE rcm.process_flag = 'N'
AND rcm.request_id = req_id
AND rcm.set_number = set_number);
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,
VCHAR_COL1_ORIG,
VCHAR_COL1_NEW,
REQUEST_ID,
ACTION_FLAG,
CREATED_BY,
CREATION_DATE,
LAST_UPDATE_LOGIN,
LAST_UPDATE_DATE,
LAST_UPDATED_BY
) SELECT HZ_CUSTOMER_MERGE_LOG_s.nextval,
'OKC_K_ITEMS',
CUSTOMER_MERGE_HEADER_ID,
cim.ID,
cim.object1_id1,
to_char(rcm.customer_id),
req_id,
'U',
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
FROM OKC_K_ITEMS cim, ra_customer_merges rcm
WHERE (
cim.object1_id1 = to_char(rcm.duplicate_id)
) AND rcm.process_flag = 'N'
AND rcm.request_id = req_id
AND rcm.set_number = set_number
AND cim.object1_id1 IN (SELECT to_char(rcm.duplicate_id)
FROM ra_customer_merges rcm
WHERE rcm.process_flag = 'N'
AND rcm.request_id = req_id
AND rcm.set_number = set_number)
AND cim.jtot_object1_code IN (SELECT ojt.object_code
FROM jtf_objects_b ojt
,jtf_object_usages oue
WHERE ojt.object_code = oue.object_code
AND oue.object_user_code = c_account);
UPDATE okc_k_items cim
SET cim.object1_id1 = (SELECT DISTINCT to_char(rcm.customer_id)
FROM ra_customer_merges rcm
WHERE cim.object1_id1 = rcm.duplicate_id
AND rcm.process_flag = 'N'
AND rcm.request_id = req_id
AND rcm.set_number = set_number)
,cim.object_version_number = cim.object_version_number + 1
,cim.last_update_date = SYSDATE
,cim.last_updated_by = arp_standard.profile.user_id
,cim.last_update_login = arp_standard.profile.last_update_login
WHERE cim.object1_id1 IN (SELECT to_char(rcm.duplicate_id)
FROM ra_customer_merges rcm
WHERE rcm.process_flag = 'N'
AND rcm.request_id = req_id
AND rcm.set_number = set_number)
AND cim.jtot_object1_code IN (SELECT ojt.object_code
FROM jtf_objects_b ojt
,jtf_object_usages oue
WHERE ojt.object_code = oue.object_code
AND oue.object_user_code = c_account)
;
arp_message.set_name('AR','AR_ROWS_UPDATED');
INSERT INTO OKC_K_VERS_NUMBERS_H(
chr_id,
major_version,
minor_version,
object_version_number,
created_by,
creation_date,
last_updated_by,
last_update_date,
last_update_login)
(SELECT
chr_id,
major_version,
minor_version,
object_version_number,
created_by,
creation_date,
last_updated_by,
last_update_date,
last_update_login
FROM OKC_K_VERS_NUMBERS
WHERE chr_id = l_chr_id(k));
UPDATE okc_k_vers_numbers ver
SET ver.minor_version = ver.minor_version + 1
,ver.object_version_number = ver.object_version_number + 1
,ver.last_update_date = SYSDATE
,ver.last_updated_by = arp_standard.profile.user_id
,ver.last_update_login = arp_standard.profile.last_update_login
WHERE chr_id = l_chr_id(k);
SELECT distinct(okl.chr_id)
FROM okc_k_lines_b okl
WHERE (okl.ship_to_site_use_id IN (SELECT DISTINCT (rcm.duplicate_site_id)
FROM ra_customer_merges rcm
WHERE rcm.process_flag = 'N'
AND rcm.request_id = req_id
AND rcm.set_number = set_number))
UNION
SELECT distinct(okl.chr_id)
FROM okc_k_lines_b okl
WHERE (okl.bill_to_site_use_id IN (SELECT DISTINCT (rcm.duplicate_site_id)
FROM ra_customer_merges rcm
WHERE rcm.process_flag = 'N'
AND rcm.request_id = req_id
AND rcm.set_number = set_number));
INSERT INTO HZ_CUSTOMER_MERGE_LOG (
MERGE_LOG_ID,
TABLE_NAME,
MERGE_HEADER_ID,
PRIMARY_KEY_ID1,
VCHAR_COL1_ORIG,
VCHAR_COL1_NEW,
REQUEST_ID,
ACTION_FLAG,
CREATED_BY,
CREATION_DATE,
LAST_UPDATE_LOGIN,
LAST_UPDATE_DATE,
LAST_UPDATED_BY
) SELECT HZ_CUSTOMER_MERGE_LOG_s.nextval,
'OKC_RULES_B',
CUSTOMER_MERGE_HEADER_ID,
rle.ID,
rle.object1_id1,
to_char(customer_address_id),
req_id,
'U',
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
FROM OKC_RULES_B rle, ra_customer_merges rcm
WHERE (
rle.object1_id1 = to_char(rcm.duplicate_address_id)
) AND rcm.process_flag = 'N'
AND rcm.request_id = req_id
AND rcm.set_number = set_number
AND rle.object1_id1 IN (SELECT to_char(rcm.duplicate_address_id)
FROM ra_customer_merges rcm
WHERE rcm.process_flag = 'N'
AND rcm.request_id = req_id
AND rcm.set_number = set_number)
AND rle.jtot_object1_code IN (SELECT ojt.object_code
FROM jtf_objects_b ojt
,jtf_object_usages oue
WHERE ojt.object_code = oue.object_code
AND oue.object_user_code = c_c_site);
UPDATE okc_rules_b rle
SET rle.object1_id1 = (SELECT DISTINCT to_char(rcm.customer_address_id)
FROM ra_customer_merges rcm
WHERE rle.object1_id1 = rcm.duplicate_address_id
AND rcm.process_flag = 'N'
AND rcm.request_id = req_id
AND rcm.set_number = set_number
AND ROWNUM=1)
,rle.object_version_number = rle.object_version_number + 1
,rle.last_update_date = SYSDATE
,rle.last_updated_by = arp_standard.profile.user_id
,rle.last_update_login = arp_standard.profile.last_update_login
WHERE rle.object1_id1 IN (SELECT to_char(rcm.duplicate_address_id)
FROM ra_customer_merges rcm
WHERE rcm.process_flag = 'N'
AND rcm.request_id = req_id
AND rcm.set_number = set_number)
AND rle.jtot_object1_code IN (SELECT ojt.object_code
FROM jtf_objects_b ojt
,jtf_object_usages oue
WHERE ojt.object_code = oue.object_code
AND oue.object_user_code = c_c_site)
;
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,
VCHAR_COL1_ORIG,
VCHAR_COL1_NEW,
REQUEST_ID,
ACTION_FLAG,
CREATED_BY,
CREATION_DATE,
LAST_UPDATE_LOGIN,
LAST_UPDATE_DATE,
LAST_UPDATED_BY
) SELECT HZ_CUSTOMER_MERGE_LOG_s.nextval,
'OKC_RULES_B',
CUSTOMER_MERGE_HEADER_ID,
rle.ID,
rle.object2_id1,
to_char(customer_address_id),
req_id,
'U',
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
FROM OKC_RULES_B rle, ra_customer_merges rcm
WHERE (
rle.object2_id1 = to_char(rcm.duplicate_address_id)
) AND rcm.process_flag = 'N'
AND rcm.request_id = req_id
AND rcm.set_number = set_number
AND rle.object2_id1 IN (SELECT to_char(rcm.duplicate_address_id)
FROM ra_customer_merges rcm
WHERE rcm.process_flag = 'N'
AND rcm.request_id = req_id
AND rcm.set_number = set_number)
AND rle.jtot_object2_code IN (SELECT ojt.object_code
FROM jtf_objects_b ojt
,jtf_object_usages oue
WHERE ojt.object_code = oue.object_code
AND oue.object_user_code = c_c_site);
UPDATE okc_rules_b rle
SET rle.object2_id1 = (SELECT DISTINCT to_char(rcm.customer_address_id)
FROM ra_customer_merges rcm
WHERE rle.object2_id1 = rcm.duplicate_address_id
AND rcm.process_flag = 'N'
AND rcm.request_id = req_id
AND rcm.set_number = set_number
AND ROWNUM=1)
,rle.object_version_number = rle.object_version_number + 1
,rle.last_update_date = SYSDATE
,rle.last_updated_by = arp_standard.profile.user_id
,rle.last_update_login = arp_standard.profile.last_update_login
WHERE rle.object2_id1 IN (SELECT to_char(rcm.duplicate_address_id)
FROM ra_customer_merges rcm
WHERE rcm.process_flag = 'N'
AND rcm.request_id = req_id
AND rcm.set_number = set_number)
AND rle.jtot_object2_code IN (SELECT ojt.object_code
FROM jtf_objects_b ojt
,jtf_object_usages oue
WHERE ojt.object_code = oue.object_code
AND oue.object_user_code = c_c_site)
;
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,
VCHAR_COL1_ORIG,
VCHAR_COL1_NEW,
REQUEST_ID,
ACTION_FLAG,
CREATED_BY,
CREATION_DATE,
LAST_UPDATE_LOGIN,
LAST_UPDATE_DATE,
LAST_UPDATED_BY
) SELECT HZ_CUSTOMER_MERGE_LOG_s.nextval,
'OKC_RULES_B',
CUSTOMER_MERGE_HEADER_ID,
rle.ID,
rle.object3_id1,
to_char(customer_address_id),
req_id,
'U',
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
FROM OKC_RULES_B rle, ra_customer_merges rcm
WHERE (
rle.object3_id1 = to_char(rcm.duplicate_address_id)
) AND rcm.process_flag = 'N'
AND rcm.request_id = req_id
AND rcm.set_number = set_number
AND rle.object3_id1 IN (SELECT to_char(rcm.duplicate_address_id)
FROM ra_customer_merges rcm
WHERE rcm.process_flag = 'N'
AND rcm.request_id = req_id
AND rcm.set_number = set_number)
AND rle.jtot_object3_code IN (SELECT ojt.object_code
FROM jtf_objects_b ojt
,jtf_object_usages oue
WHERE ojt.object_code = oue.object_code
AND oue.object_user_code = c_c_site);
UPDATE okc_rules_b rle
SET rle.object3_id1 = (SELECT DISTINCT to_char(rcm.customer_address_id)
FROM ra_customer_merges rcm
WHERE rle.object3_id1 = rcm.duplicate_address_id
AND rcm.process_flag = 'N'
AND rcm.request_id = req_id
AND rcm.set_number = set_number
AND ROWNUM=1)
,rle.object_version_number = rle.object_version_number + 1
,rle.last_update_date = SYSDATE
,rle.last_updated_by = arp_standard.profile.user_id
,rle.last_update_login = arp_standard.profile.last_update_login
WHERE rle.object3_id1 IN (SELECT to_char(rcm.duplicate_address_id)
FROM ra_customer_merges rcm
WHERE rcm.process_flag = 'N'
AND rcm.request_id = req_id
AND rcm.set_number = set_number)
AND rle.jtot_object3_code IN (SELECT ojt.object_code
FROM jtf_objects_b ojt
,jtf_object_usages oue
WHERE ojt.object_code = oue.object_code
AND oue.object_user_code = c_c_site)
;
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,
VCHAR_COL1_ORIG,
VCHAR_COL1_NEW,
REQUEST_ID,
ACTION_FLAG,
CREATED_BY,
CREATION_DATE,
LAST_UPDATE_LOGIN,
LAST_UPDATE_DATE,
LAST_UPDATED_BY
) SELECT HZ_CUSTOMER_MERGE_LOG_s.nextval,
'OKC_K_ITEMS',
CUSTOMER_MERGE_HEADER_ID,
cim.ID,
cim.object1_id1,
to_char(rcm.customer_address_id),
req_id,
'U',
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
FROM OKC_K_ITEMS cim, ra_customer_merges rcm
WHERE (
cim.object1_id1 = to_char(rcm.duplicate_address_id)
) AND rcm.process_flag = 'N'
AND rcm.request_id = req_id
AND rcm.set_number = set_number
AND cim.object1_id1 IN (SELECT to_char(rcm.duplicate_address_id)
FROM ra_customer_merges rcm
WHERE rcm.process_flag = 'N'
AND rcm.request_id = req_id
AND rcm.set_number = set_number)
AND cim.jtot_object1_code IN (SELECT ojt.object_code
FROM jtf_objects_b ojt
,jtf_object_usages oue
WHERE ojt.object_code = oue.object_code
AND oue.object_user_code = c_c_site);
UPDATE okc_k_items cim
SET cim.object1_id1 = (SELECT DISTINCT to_char(rcm.customer_address_id)
FROM ra_customer_merges rcm
WHERE cim.object1_id1 = rcm.duplicate_address_id
AND rcm.process_flag = 'N'
AND rcm.request_id = req_id
AND rcm.set_number = set_number
AND ROWNUM=1)
,cim.object_version_number = cim.object_version_number + 1
,cim.last_update_date = SYSDATE
,cim.last_updated_by = arp_standard.profile.user_id
,cim.last_update_login = arp_standard.profile.last_update_login
WHERE cim.object1_id1 IN (SELECT to_char(rcm.duplicate_address_id)
FROM ra_customer_merges rcm
WHERE rcm.process_flag = 'N'
AND rcm.request_id = req_id
AND rcm.set_number = set_number)
AND cim.jtot_object1_code IN (SELECT ojt.object_code
FROM jtf_objects_b ojt
,jtf_object_usages oue
WHERE ojt.object_code = oue.object_code
AND oue.object_user_code = c_c_site)
;
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,
VCHAR_COL1_ORIG,
VCHAR_COL1_NEW,
REQUEST_ID,
ACTION_FLAG,
CREATED_BY,
CREATION_DATE,
LAST_UPDATE_LOGIN,
LAST_UPDATE_DATE,
LAST_UPDATED_BY
) SELECT HZ_CUSTOMER_MERGE_LOG_s.nextval,
'OKC_K_PARTY_ROLES_B',
CUSTOMER_MERGE_HEADER_ID,
rle.ID,
rle.object1_id1,
to_char(rcm.customer_site_id),
req_id,
'U',
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
FROM OKC_K_PARTY_ROLES_B rle, ra_customer_merges rcm
WHERE (
rle.object1_id1 = to_char(rcm.duplicate_site_id)
) AND rcm.process_flag = 'N'
AND rcm.request_id = req_id
AND rcm.set_number = set_number
AND rle.object1_id1 IN (SELECT to_char(rcm.duplicate_site_id)
FROM ra_customer_merges rcm
WHERE rcm.process_flag = 'N'
AND rcm.request_id = req_id
AND rcm.set_number = set_number)
AND rle.jtot_object1_code IN (SELECT ojt.object_code
FROM jtf_objects_b ojt
,jtf_object_usages oue
WHERE ojt.object_code = oue.object_code
AND oue.object_user_code = c_c_site_use);
UPDATE okc_k_party_roles_b rle
SET rle.object1_id1 = (SELECT DISTINCT to_char(rcm.customer_site_id)
FROM ra_customer_merges rcm
WHERE rle.object1_id1 = rcm.duplicate_site_id
AND rcm.process_flag = 'N'
AND rcm.request_id = req_id
AND rcm.set_number = set_number)
,rle.object_version_number = rle.object_version_number + 1
,rle.last_update_date = SYSDATE
,rle.last_updated_by = arp_standard.profile.user_id
,rle.last_update_login = arp_standard.profile.last_update_login
WHERE rle.object1_id1 IN ( SELECT to_char(rcm.duplicate_site_id)
FROM ra_customer_merges rcm
WHERE rcm.process_flag = 'N'
AND rcm.request_id = req_id
AND rcm.set_number = set_number)
AND rle.jtot_object1_code IN (SELECT ojt.object_code
FROM jtf_objects_b ojt,jtf_object_usages oue
WHERE ojt.object_code =oue.object_code
AND oue.object_user_code = c_c_site_use)
AND rle.dnz_chr_id in (select k_header_id from oke_k_headers);
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,
VCHAR_COL1_ORIG,
VCHAR_COL1_NEW,
REQUEST_ID,
ACTION_FLAG,
CREATED_BY,
CREATION_DATE,
LAST_UPDATE_LOGIN,
LAST_UPDATE_DATE,
LAST_UPDATED_BY
) SELECT HZ_CUSTOMER_MERGE_LOG_s.nextval,
'OKC_RULES_B',
CUSTOMER_MERGE_HEADER_ID,
rle.ID,
rle.object1_id1,
to_char(rcm.customer_site_id),
req_id,
'U',
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
FROM OKC_RULES_B rle, ra_customer_merges rcm
WHERE (
rle.object1_id1 = to_char(rcm.duplicate_site_id)
) AND rcm.process_flag = 'N'
AND rcm.request_id = req_id
AND rcm.set_number = set_number
AND rle.object1_id1 IN (SELECT to_char(rcm.duplicate_site_id)
FROM ra_customer_merges rcm
WHERE rcm.process_flag = 'N'
AND rcm.request_id = req_id
AND rcm.set_number = set_number)
AND rle.jtot_object1_code IN (SELECT ojt.object_code
FROM jtf_objects_b ojt
,jtf_object_usages oue
WHERE ojt.object_code = oue.object_code
AND oue.object_user_code = c_c_site_use);
UPDATE okc_rules_b rle
SET rle.object1_id1 = (SELECT DISTINCT to_char(rcm.customer_site_id)
FROM ra_customer_merges rcm
WHERE rle.object1_id1 = rcm.duplicate_site_id
AND rcm.process_flag = 'N'
AND rcm.request_id = req_id
AND rcm.set_number = set_number)
,rle.object_version_number = rle.object_version_number + 1
,rle.last_update_date = SYSDATE
,rle.last_updated_by = arp_standard.profile.user_id
,rle.last_update_login = arp_standard.profile.last_update_login
WHERE rle.object1_id1 IN (SELECT to_char(rcm.duplicate_site_id)
FROM ra_customer_merges rcm
WHERE rcm.process_flag = 'N'
AND rcm.request_id = req_id
AND rcm.set_number = set_number)
AND rle.jtot_object1_code IN (SELECT ojt.object_code
FROM jtf_objects_b ojt
,jtf_object_usages oue
WHERE ojt.object_code = oue.object_code
AND oue.object_user_code = c_c_site_use)
;
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,
VCHAR_COL1_ORIG,
VCHAR_COL1_NEW,
REQUEST_ID,
ACTION_FLAG,
CREATED_BY,
CREATION_DATE,
LAST_UPDATE_LOGIN,
LAST_UPDATE_DATE,
LAST_UPDATED_BY
) SELECT HZ_CUSTOMER_MERGE_LOG_s.nextval,
'OKC_RULES_B',
CUSTOMER_MERGE_HEADER_ID,
rle.ID,
rle.object2_id1,
to_char(rcm.customer_site_id),
req_id,
'U',
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
FROM OKC_RULES_B rle, ra_customer_merges rcm
WHERE (
rle.object2_id1 = to_char(rcm.duplicate_site_id)
) AND rcm.process_flag = 'N'
AND rcm.request_id = req_id
AND rcm.set_number = set_number
AND rle.object2_id1 IN (SELECT to_char(rcm.duplicate_site_id)
FROM ra_customer_merges rcm
WHERE rcm.process_flag = 'N'
AND rcm.request_id = req_id
AND rcm.set_number = set_number)
AND rle.jtot_object2_code IN (SELECT ojt.object_code
FROM jtf_objects_b ojt
,jtf_object_usages oue
WHERE ojt.object_code = oue.object_code
AND oue.object_user_code = c_c_site_use);
UPDATE okc_rules_b rle
SET rle.object2_id1 = (SELECT DISTINCT to_char(rcm.customer_site_id)
FROM ra_customer_merges rcm
WHERE rle.object2_id1 = rcm.duplicate_site_id
AND rcm.process_flag = 'N'
AND rcm.request_id = req_id
AND rcm.set_number = set_number)
,rle.object_version_number = rle.object_version_number + 1
,rle.last_update_date = SYSDATE
,rle.last_updated_by = arp_standard.profile.user_id
,rle.last_update_login = arp_standard.profile.last_update_login
WHERE rle.object2_id1 IN (SELECT to_char(rcm.duplicate_site_id)
FROM ra_customer_merges rcm
WHERE rcm.process_flag = 'N'
AND rcm.request_id = req_id
AND rcm.set_number = set_number)
AND rle.jtot_object2_code IN (SELECT ojt.object_code
FROM jtf_objects_b ojt
,jtf_object_usages oue
WHERE ojt.object_code = oue.object_code
AND oue.object_user_code = c_c_site_use)
;
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,
VCHAR_COL1_ORIG,
VCHAR_COL1_NEW,
REQUEST_ID,
ACTION_FLAG,
CREATED_BY,
CREATION_DATE,
LAST_UPDATE_LOGIN,
LAST_UPDATE_DATE,
LAST_UPDATED_BY
) SELECT HZ_CUSTOMER_MERGE_LOG_s.nextval,
'OKC_RULES_B',
CUSTOMER_MERGE_HEADER_ID,
rle.ID,
rle.object3_id1,
to_char(rcm.customer_site_id),
req_id,
'U',
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
FROM OKC_RULES_B rle, ra_customer_merges rcm
WHERE (
rle.object3_id1 = to_char(rcm.duplicate_site_id)
) AND rcm.process_flag = 'N'
AND rcm.request_id = req_id
AND rcm.set_number = set_number
AND rle.object3_id1 IN (SELECT to_char(rcm.duplicate_site_id)
FROM ra_customer_merges rcm
WHERE rcm.process_flag = 'N'
AND rcm.request_id = req_id
AND rcm.set_number = set_number)
AND rle.jtot_object3_code IN (SELECT ojt.object_code
FROM jtf_objects_b ojt
,jtf_object_usages oue
WHERE ojt.object_code = oue.object_code
AND oue.object_user_code = c_c_site_use);
UPDATE okc_rules_b rle
SET rle.object3_id1 = (SELECT DISTINCT to_char(rcm.customer_site_id)
FROM ra_customer_merges rcm
WHERE rle.object3_id1 = rcm.duplicate_site_id
AND rcm.process_flag = 'N'
AND rcm.request_id = req_id
AND rcm.set_number = set_number)
,rle.object_version_number = rle.object_version_number + 1
,rle.last_update_date = SYSDATE
,rle.last_updated_by = arp_standard.profile.user_id
,rle.last_update_login = arp_standard.profile.last_update_login
WHERE rle.object3_id1 IN (SELECT to_char(rcm.duplicate_site_id)
FROM ra_customer_merges rcm
WHERE rcm.process_flag = 'N'
AND rcm.request_id = req_id
AND rcm.set_number = set_number)
AND rle.jtot_object3_code IN (SELECT ojt.object_code
FROM jtf_objects_b ojt
,jtf_object_usages oue
WHERE ojt.object_code = oue.object_code
AND oue.object_user_code = c_c_site_use)
;
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,
VCHAR_COL1_ORIG,
VCHAR_COL1_NEW,
REQUEST_ID,
ACTION_FLAG,
CREATED_BY,
CREATION_DATE,
LAST_UPDATE_LOGIN,
LAST_UPDATE_DATE,
LAST_UPDATED_BY
) SELECT HZ_CUSTOMER_MERGE_LOG_s.nextval,
'OKC_K_HEADERS_B',
CUSTOMER_MERGE_HEADER_ID,
okh.ID,
to_char(okh.ship_to_site_use_id),
to_char(rcm.customer_site_id),
req_id,
'U',
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
FROM OKC_K_HEADERS_B okh, ra_customer_merges rcm
WHERE (
okh.ship_to_site_use_id = rcm.duplicate_site_id
) AND rcm.process_flag = 'N'
AND rcm.request_id = req_id
AND rcm.set_number = set_number
AND okh.ship_to_site_use_id IN (SELECT rcm.duplicate_site_id
FROM ra_customer_merges rcm
WHERE rcm.process_flag = 'N'
AND rcm.request_id = req_id
AND rcm.set_number = set_number);
UPDATE okc_k_headers_b okh
SET okh.ship_to_site_use_id = (SELECT DISTINCT (rcm.customer_site_id)
FROM ra_customer_merges rcm
WHERE okh.ship_to_site_use_id = rcm.duplicate_site_id
AND rcm.process_flag = 'N'
AND rcm.request_id = req_id
AND rcm.set_number = set_number)
,okh.object_version_number = okh.object_version_number + 1
,okh.last_update_date = SYSDATE
,okh.last_updated_by = arp_standard.profile.user_id
,okh.last_update_login = arp_standard.profile.last_update_login
WHERE okh.ship_to_site_use_id IN (SELECT DISTINCT (rcm.duplicate_site_id)
FROM ra_customer_merges rcm
WHERE rcm.process_flag = 'N'
AND rcm.request_id = req_id
AND rcm.set_number = set_number);
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,
VCHAR_COL1_ORIG,
VCHAR_COL1_NEW,
REQUEST_ID,
ACTION_FLAG,
CREATED_BY,
CREATION_DATE,
LAST_UPDATE_LOGIN,
LAST_UPDATE_DATE,
LAST_UPDATED_BY
) SELECT HZ_CUSTOMER_MERGE_LOG_s.nextval,
'OKC_K_HEADERS_B',
CUSTOMER_MERGE_HEADER_ID,
okh.ID,
to_char(okh.bill_to_site_use_id),
to_char(rcm.customer_site_id),
req_id,
'U',
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
FROM OKC_K_HEADERS_B okh, ra_customer_merges rcm
WHERE (
okh.bill_to_site_use_id = rcm.duplicate_site_id
) AND rcm.process_flag = 'N'
AND rcm.request_id = req_id
AND rcm.set_number = set_number
AND okh.bill_to_site_use_id IN (SELECT rcm.duplicate_site_id
FROM ra_customer_merges rcm
WHERE rcm.process_flag = 'N'
AND rcm.request_id = req_id
AND rcm.set_number = set_number);
UPDATE okc_k_headers_b okh
SET okh.bill_to_site_use_id = (SELECT DISTINCT (rcm.customer_site_id)
FROM ra_customer_merges rcm
WHERE okh.bill_to_site_use_id = rcm.duplicate_site_id
AND rcm.process_flag = 'N'
AND rcm.request_id = req_id
AND rcm.set_number = set_number)
,okh.object_version_number = okh.object_version_number + 1
,okh.last_update_date = SYSDATE
,okh.last_updated_by = arp_standard.profile.user_id
,okh.last_update_login = arp_standard.profile.last_update_login
WHERE okh.bill_to_site_use_id IN (SELECT DISTINCT (rcm.duplicate_site_id)
FROM ra_customer_merges rcm
WHERE rcm.process_flag = 'N'
AND rcm.request_id = req_id
AND rcm.set_number = set_number);
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,
VCHAR_COL1_ORIG,
VCHAR_COL1_NEW,
REQUEST_ID,
ACTION_FLAG,
CREATED_BY,
CREATION_DATE,
LAST_UPDATE_LOGIN,
LAST_UPDATE_DATE,
LAST_UPDATED_BY
) SELECT HZ_CUSTOMER_MERGE_LOG_s.nextval,
'OKC_K_LINES_B',
CUSTOMER_MERGE_HEADER_ID,
okl.ID,
to_char(okl.ship_to_site_use_id),
to_char(rcm.customer_site_id),
req_id,
'U',
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
FROM OKC_K_LINES_B okl, ra_customer_merges rcm
WHERE (
okl.ship_to_site_use_id = rcm.duplicate_site_id
) AND rcm.process_flag = 'N'
AND rcm.request_id = req_id
AND rcm.set_number = set_number
AND okl.ship_to_site_use_id IN (SELECT rcm.duplicate_site_id
FROM ra_customer_merges rcm
WHERE rcm.process_flag = 'N'
AND rcm.request_id = req_id
AND rcm.set_number = set_number);
UPDATE okc_k_lines_b okl
SET okl.ship_to_site_use_id = (SELECT DISTINCT (rcm.customer_site_id)
FROM ra_customer_merges rcm
WHERE okl.ship_to_site_use_id = rcm.duplicate_site_id
AND rcm.process_flag = 'N'
AND rcm.request_id = req_id
AND rcm.set_number = set_number)
,okl.object_version_number = okl.object_version_number + 1
,okl.last_update_date = SYSDATE
,okl.last_updated_by = arp_standard.profile.user_id
,okl.last_update_login = arp_standard.profile.last_update_login
WHERE okl.ship_to_site_use_id IN (SELECT DISTINCT (rcm.duplicate_site_id)
FROM ra_customer_merges rcm
WHERE rcm.process_flag = 'N'
AND rcm.request_id = req_id
AND rcm.set_number = set_number);
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,
VCHAR_COL1_ORIG,
VCHAR_COL1_NEW,
REQUEST_ID,
ACTION_FLAG,
CREATED_BY,
CREATION_DATE,
LAST_UPDATE_LOGIN,
LAST_UPDATE_DATE,
LAST_UPDATED_BY
) SELECT HZ_CUSTOMER_MERGE_LOG_s.nextval,
'OKC_K_LINES_B',
CUSTOMER_MERGE_HEADER_ID,
okl.ID,
to_char(okl.bill_to_site_use_id),
to_char(rcm.customer_site_id),
req_id,
'U',
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
FROM OKC_K_LINES_B okl, ra_customer_merges rcm
WHERE (
okl.bill_to_site_use_id = rcm.duplicate_site_id
) AND rcm.process_flag = 'N'
AND rcm.request_id = req_id
AND rcm.set_number = set_number
AND okl.bill_to_site_use_id IN (SELECT rcm.duplicate_site_id
FROM ra_customer_merges rcm
WHERE rcm.process_flag = 'N'
AND rcm.request_id = req_id
AND rcm.set_number = set_number);
UPDATE okc_k_lines_b okl
SET okl.bill_to_site_use_id = (SELECT DISTINCT (rcm.customer_site_id)
FROM ra_customer_merges rcm
WHERE okl.bill_to_site_use_id = rcm.duplicate_site_id
AND rcm.process_flag = 'N'
AND rcm.request_id = req_id
AND rcm.set_number = set_number)
,okl.object_version_number = okl.object_version_number + 1
,okl.last_update_date = SYSDATE
,okl.last_updated_by = arp_standard.profile.user_id
,okl.last_update_login = arp_standard.profile.last_update_login
WHERE okl.bill_to_site_use_id IN (SELECT DISTINCT (rcm.duplicate_site_id)
FROM ra_customer_merges rcm
WHERE rcm.process_flag = 'N'
AND rcm.request_id = req_id
AND rcm.set_number = set_number);
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,
VCHAR_COL1_ORIG,
VCHAR_COL1_NEW,
REQUEST_ID,
ACTION_FLAG,
CREATED_BY,
CREATION_DATE,
LAST_UPDATE_LOGIN,
LAST_UPDATE_DATE,
LAST_UPDATED_BY
) SELECT HZ_CUSTOMER_MERGE_LOG_s.nextval,
'OKC_K_PARTY_ROLES_B',
CUSTOMER_MERGE_HEADER_ID,
okpr.ID,
to_char(okpr.bill_to_site_use_id),
to_char(rcm.customer_site_id),
req_id,
'U',
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
FROM OKC_K_PARTY_ROLES_B okpr, ra_customer_merges rcm
WHERE (
okpr.bill_to_site_use_id = rcm.duplicate_site_id
) AND rcm.process_flag = 'N'
AND rcm.request_id = req_id
AND rcm.set_number = set_number
AND okpr.bill_to_site_use_id IN (SELECT rcm.duplicate_site_id
FROM ra_customer_merges rcm
WHERE rcm.process_flag = 'N'
AND rcm.request_id = req_id
AND rcm.set_number = set_number);
UPDATE okc_k_party_roles_b okpr
SET okpr.bill_to_site_use_id = (SELECT DISTINCT (rcm.customer_site_id)
FROM ra_customer_merges rcm
WHERE okpr.bill_to_site_use_id = rcm.duplicate_site_id
AND rcm.process_flag = 'N'
AND rcm.request_id = req_id
AND rcm.set_number = set_number)
,okpr.object_version_number = okpr.object_version_number + 1
,okpr.last_update_date = SYSDATE
,okpr.last_updated_by = arp_standard.profile.user_id
,okpr.last_update_login = arp_standard.profile.last_update_login
WHERE okpr.bill_to_site_use_id IN (SELECT DISTINCT (rcm.duplicate_site_id)
FROM ra_customer_merges rcm
WHERE rcm.process_flag = 'N'
AND rcm.request_id = req_id
AND rcm.set_number = set_number);
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,
VCHAR_COL1_ORIG,
VCHAR_COL1_NEW,
REQUEST_ID,
ACTION_FLAG,
CREATED_BY,
CREATION_DATE,
LAST_UPDATE_LOGIN,
LAST_UPDATE_DATE,
LAST_UPDATED_BY
) SELECT HZ_CUSTOMER_MERGE_LOG_s.nextval,
'OKC_K_ITEMS',
CUSTOMER_MERGE_HEADER_ID,
cim.ID,
cim.object1_id1,
to_char(rcm.customer_site_id),
req_id,
'U',
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
FROM OKC_K_ITEMS cim, ra_customer_merges rcm
WHERE (
cim.object1_id1 = to_char(rcm.duplicate_site_id)
) AND rcm.process_flag = 'N'
AND rcm.request_id = req_id
AND rcm.set_number = set_number
AND cim.object1_id1 IN (SELECT to_char(rcm.duplicate_site_id)
FROM ra_customer_merges rcm
WHERE rcm.process_flag = 'N'
AND rcm.request_id = req_id
AND rcm.set_number = set_number)
AND cim.jtot_object1_code IN (SELECT ojt.object_code
FROM jtf_objects_b ojt
,jtf_object_usages oue
WHERE ojt.object_code = oue.object_code
AND oue.object_user_code = c_c_site_use);
UPDATE okc_k_items cim
SET cim.object1_id1 = (SELECT DISTINCT to_char(rcm.customer_site_id)
FROM ra_customer_merges rcm
WHERE cim.object1_id1 = rcm.duplicate_site_id
AND rcm.process_flag = 'N'
AND rcm.request_id = req_id
AND rcm.set_number = set_number)
,cim.object_version_number = cim.object_version_number + 1
,cim.last_update_date = SYSDATE
,cim.last_updated_by = arp_standard.profile.user_id
,cim.last_update_login = arp_standard.profile.last_update_login
WHERE cim.object1_id1 IN (SELECT to_char(rcm.duplicate_site_id)
FROM ra_customer_merges rcm
WHERE rcm.process_flag = 'N'
AND rcm.request_id = req_id
AND rcm.set_number = set_number)
AND cim.jtot_object1_code IN (SELECT ojt.object_code
FROM jtf_objects_b ojt
,jtf_object_usages oue
WHERE ojt.object_code = oue.object_code
AND oue.object_user_code = c_c_site_use)
;
arp_message.set_name('AR','AR_ROWS_UPDATED');
INSERT INTO OKC_K_VERS_NUMBERS_H(
chr_id,
major_version,
minor_version,
object_version_number,
created_by,
creation_date,
last_updated_by,
last_update_date,
last_update_login)
(SELECT
chr_id,
major_version,
minor_version,
object_version_number,
created_by,
creation_date,
last_updated_by,
last_update_date,
last_update_login
FROM OKC_K_VERS_NUMBERS
WHERE chr_id = l_chr_id(k));
UPDATE okc_k_vers_numbers ver
SET ver.minor_version = ver.minor_version + 1
,ver.object_version_number = ver.object_version_number + 1
,ver.last_update_date = SYSDATE
,ver.last_updated_by = arp_standard.profile.user_id
,ver.last_update_login = arp_standard.profile.last_update_login
WHERE chr_id = l_chr_id(k);
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 = b_request_id
AND cme.set_number = b_set_number
AND cme.process_flag = 'N'
;
SELECT party_id
FROM hz_cust_accounts
WHERE cust_account_id = b_account_id
;
SELECT kpr.dnz_chr_id
FROM okc_k_party_roles_b kpr
WHERE kpr.jtot_object1_code IN (SELECT ojt.object_code
FROM jtf_objects_b ojt
,jtf_object_usages oue
WHERE ojt.object_code = oue.object_code
AND oue.object_user_code = c_party
)
AND kpr.object1_id1 = to_char(b_party_id)
;
SELECT cim.dnz_chr_id
FROM okc_k_items cim
WHERE cim.jtot_object1_code IN (SELECT ojt.object_code
FROM jtf_objects_b ojt
,jtf_object_usages oue
WHERE ojt.object_code = oue.object_code
AND oue.object_user_code = c_party
)
AND cim.object1_id1 = to_char(b_party_id)
;
OKS_CODE_HOOK.Update_Merge_Account(req_id => req_id
,set_number => set_number
,process_mode => process_mode
,x_hook =>l_hook);
arp_message.set_line('OKS_CODE_HOOK.Update_Merge_Account()-');
arp_message.set_line(' Exception raised in OKS_CODE_HOOK.Update_Merge_Account()-');