[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
| account site id has been deleted by ra_addr
| and account id has been deleted by ra_cust.
| Jianying Huang 17-DEC-00 Bug 1535542: Since we will not physically
| delete rows till the end of merge, we can move
| the call of 'copy_contacts' right before we migrate
| org contacts and contact points.
|
+===========================================================================*/
PROCEDURE merge (
req_id NUMBER,
set_num NUMBER,
process_mode VARCHAR2
) IS
BEGIN
arp_message.set_line( 'ARP_CMERGE_ARCUS.MERGE()+' );
| update statements for better
| performance.
+===========================================================================*/
PROCEDURE ra_bill_to_location (
req_id NUMBER,
set_num NUMBER,
process_mode VARCHAR2
) IS
CURSOR c1 IS
SELECT merge.duplicate_site_id,merge.customer_site_id,
cust.bill_to_site_use_id,merge.duplicate_site_code,
merge.customer_createsame,cust.org_id --SSUptake
FROM hz_cust_site_uses_all cust, ra_customer_merges merge --SSUptake
WHERE merge.request_id = req_id
AND merge.set_number = set_num
AND merge.process_flag = 'N'
AND cust.site_use_id = merge.duplicate_site_id
AND cust.org_id = merge.org_id --SSUptake
FOR UPDATE NOWAIT;
select customer_site_id into l_ra_bill_to_site_use_id
from ra_customer_merges
where duplicate_site_id = l_bill_to_site_use_id
and process_flag = 'N'
and request_id = req_id
and set_number = set_num;
update hz_cust_site_uses_all --SSUptake
set bill_to_site_use_id = l_ra_bill_to_site_use_id
where site_use_id = l_cust_site_id
and org_id = l_org_id; --SSUptake
update hz_cust_site_uses_all --SSUptake
set bill_to_site_use_id = l_cust_site_id
where org_id = l_org_id --SSUptake
and site_use_id in (
SELECT site_use_id
FROM hz_cust_site_uses_all su, --SSUptake
hz_cust_acct_sites_all site --SSUptake
WHERE su.org_id = l_org_id --SSUptake
AND su.org_id = site.org_id --SSUptake
AND site.cust_acct_site_id = su.cust_acct_site_id
AND su.site_use_code='SHIP_TO'
AND su.bill_to_site_use_id = l_dup_site_id
AND site.cust_account_id in (
SELECT unique(customer_id)
FROM ra_customer_merges merge
WHERE merge.process_flag = 'N'
and merge.request_id = req_id
and merge.set_number = set_num
and merge.org_id = site.org_id --SSUptake
UNION
SELECT related_cust_account_id
FROM hz_cust_acct_relate_all rel --SSUptake
WHERE rel.org_id = l_org_id --SSUptake
AND rel.cust_account_id in (
select unique(customer_id)
from ra_customer_merges merge
where merge.process_flag = 'N'
and merge.request_id = req_id
and merge.set_number = set_num
and merge.org_id = rel.org_id ) --SSUptake
)
);
| products, we need to mark deleted rows here
| first and physically delete them after merging one
| set in 'delete_rows'.
| Jianying Huang 08-MAR-01 Bug 1610924: Modified the procedure based on
| the new om enhancement: allow merging all of the site uses
| of a customer.
|
+===========================================================================*/
PROCEDURE ra_su (
req_id NUMBER,
set_num NUMBER,
process_mode VARCHAR2
) IS
l_orig_system_ref_rec HZ_ORIG_SYSTEM_REF_PUB.ORIG_SYS_REFERENCE_REC_TYPE;
SELECT site_use_id
FROM hz_cust_site_uses_all su, ra_customer_merges m --SSUptake
WHERE m.request_id = req_id
AND m.process_flag = 'N'
AND m.set_number = set_num
AND m.org_id = su.org_id --SSUptake
AND su.cust_acct_site_id = m.duplicate_address_id
FOR UPDATE NOWAIT;
SELECT distinct(m.customer_site_id), m.duplicate_site_id
FROM ra_customer_merges m
WHERE m.process_flag = 'N'
AND m.request_id = req_id
AND m.set_number = set_num
AND (m.delete_duplicate_flag = 'N' OR m.delete_duplicate_flag = 'Y');
UPDATE HZ_CUST_SITE_USES_ALL yt --SSUptake
SET status = 'I',
last_update_date = sysdate,
last_updated_by =hz_utility_v2pub.user_id,-- arp_standard.profile.user_id,
last_update_login =hz_utility_v2pub.last_update_login,-- arp_standard.profile.last_update_login,
request_id = req_id,
program_application_id =hz_utility_v2pub.program_application_id,-- arp_standard.profile.program_application_id,
program_id =hz_utility_v2pub.program_id,-- arp_standard.profile.program_id,
program_update_date = sysdate
WHERE EXISTS (
SELECT 'Y'
FROM ra_customer_merges m
WHERE m.duplicate_site_id = yt.site_use_id
AND m.org_id = yt.org_id
AND m.process_flag = 'N'
AND m.request_id = req_id
AND m.set_number = set_num
AND m.delete_duplicate_flag = 'N' );
arp_message.set_name( 'AR', 'AR_ROWS_UPDATED' );
UPDATE hz_cust_site_uses_all asu
SET primary_flag = 'Y',
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 asu.site_use_id IN( SELECT customer_site_id
FROM ra_customer_merges m
WHERE m.request_id = req_id
AND m.set_number = set_num
AND m.customer_createsame = 'N'
AND m.org_id = asu.org_id
AND m.process_flag = 'N'
AND m.duplicate_primary_flag = 'Y'
AND not exists (SELECT 'EXISTS'
FROM hz_cust_acct_sites_all s, hz_cust_site_uses_all su
WHERE s.cust_account_id = m.customer_id
AND su.cust_acct_site_id = s.cust_acct_site_id
AND s.org_id = m.org_id
AND su.org_id = s.org_id
AND su.site_use_code = m.duplicate_site_code
AND su.primary_flag = 'Y'
AND su.status = 'A'));
arp_message.set_name( 'AR', 'AR_ROWS_UPDATED' );
UPDATE HZ_CUST_SITE_USES su1
SET status = '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_acct_site_id IN (
SELECT m.duplicate_address_id
FROM ra_customer_merges m
WHERE m.process_flag = 'N'
AND m.request_id = req_id
AND m.set_number = set_num
AND m.delete_duplicate_flag = 'N' )
AND site_use_code NOT IN ('BILL_TO', 'SHIP_TO', 'MARKET' )
AND NOT EXISTS (
SELECT 'active bill/ship/market site uses exist'
FROM HZ_CUST_SITE_USES su
WHERE su.cust_acct_site_id = su1.cust_acct_site_id
AND su.site_use_code IN
( 'BILL_TO','SHIP_TO', 'MARKET' )
AND status = 'A' );
arp_message.set_name( 'AR', 'AR_ROWS_UPDATED' );
/*************** 'delete' mode ***************/
--Bug 1535542: Mark the rows need to be deleted by setting status to 'D'.
--Physically delete them after merge.
arp_message.set_name( 'AR', 'AR_UPDATING_TABLE' );
UPDATE HZ_CUST_SITE_USES_ALL yt
SET status = 'D'
WHERE EXISTS (
SELECT 'Y'
FROM ra_customer_merges m
WHERE m.duplicate_site_id = yt.site_use_id
AND m.org_id = yt.org_id
AND m.process_flag = 'N'
AND m.request_id = req_id
AND m.set_number = set_num
AND m.delete_duplicate_flag = 'Y' );
arp_message.set_name( 'AR', 'AR_ROWS_UPDATED' );
UPDATE HZ_CUST_SITE_USES su1
SET status = 'D'
WHERE cust_acct_site_id IN (
SELECT m.duplicate_address_id
FROM ra_customer_merges m
WHERE m.process_flag = 'N'
AND m.request_id = req_id
AND m.set_number = set_num
AND m.delete_duplicate_flag = 'Y' )
AND site_use_code NOT IN ('BILL_TO', 'SHIP_TO', 'MARKET' )
AND NOT EXISTS (
SELECT 'bill/ship/market site uses exist'
FROM HZ_CUST_SITE_USES su
WHERE su.cust_acct_site_id = su1.cust_acct_site_id
AND su.site_use_code IN
( 'BILL_TO', 'SHIP_TO', 'MARKET' )
AND status <> 'D' );
arp_message.set_name( 'AR', 'AR_ROWS_UPDATED' );
| products, we need to mark deleted rows here
| first and physically delete them after merging one
| set in 'delete_rows'.
|
+===========================================================================*/
PROCEDURE ra_addr (
req_id NUMBER,
set_num NUMBER,
process_mode VARCHAR2
) IS
l_orig_system_ref_rec HZ_ORIG_SYSTEM_REF_PUB.ORIG_SYS_REFERENCE_REC_TYPE;
SELECT yt.cust_acct_site_id
FROM hz_cust_acct_sites_all yt, ra_customer_merges m --SSUptake
WHERE yt.cust_acct_site_id = m.duplicate_address_id
AND m.org_id = yt.org_id --SSUptake
AND m.request_id = req_id
AND m.process_flag = 'N'
AND m.set_number = set_num
AND m.delete_duplicate_flag = 'N'
AND NOT EXISTS (
SELECT 'active site uses exist'
FROM HZ_CUST_SITE_USES_ALL su --SSUptake
WHERE su.org_id = yt.org_id --SSUptake
AND su.cust_acct_site_id = yt.cust_acct_site_id
AND su.status = 'A' )
FOR UPDATE NOWAIT;
SELECT yt.cust_acct_site_id
FROM hz_cust_acct_sites_all yt, ra_customer_merges m --SSUptake
WHERE m.request_id = req_id
AND m.process_flag = 'N'
AND m.set_number = set_num
AND m.delete_duplicate_flag = 'Y'
AND m.org_id = yt.org_id --SSUptake
AND yt.cust_acct_site_id = m.duplicate_address_id
AND NOT EXISTS (
SELECT 'site uses exist'
FROM HZ_CUST_SITE_USES_ALL su --SSUptake
WHERE su.cust_acct_site_id = yt.cust_acct_site_id
AND su.org_id = yt.org_id --SSUptake
AND su.status <> 'D' )
FOR UPDATE NOWAIT;
SELECT distinct(m.customer_address_id), m.duplicate_address_id
FROM ra_customer_merges m
WHERE m.process_flag = 'N'
AND m.request_id = req_id
AND m.set_number = set_num
AND (m.delete_duplicate_flag = 'N' or m.delete_duplicate_flag = 'Y') --5571559
AND NOT EXISTS (SELECT 'site uses exist'
FROM HZ_CUST_SITE_USES_ALL su --SSUptake
WHERE su.org_id = m.org_id --SSUptake
AND su.cust_acct_site_id = m.duplicate_address_id
AND su.status = 'A' );
UPDATE HZ_CUST_ACCT_SITES_ALL yt --SSUptake
SET status = 'I',
last_update_date = sysdate,
last_updated_by = hz_utility_v2pub.user_id,--arp_standard.profile.user_id,
last_update_login =hz_utility_v2pub.last_update_login,-- arp_standard.profile.last_update_login,
request_id = req_id,
program_application_id =hz_utility_v2pub.program_application_id,-- arp_standard.profile.program_application_id,
program_id =hz_utility_v2pub.program_id,-- arp_standard.profile.program_id,
program_update_date = sysdate
WHERE EXISTS (
SELECT 'Y'
FROM ra_customer_merges m
WHERE m.process_flag = 'N'
AND m.request_id = req_id
AND m.set_number = set_num
AND m.delete_duplicate_flag = 'N'
AND m.duplicate_address_id = yt.cust_acct_site_id
AND m.org_id = yt.org_id) --SSUptake
AND NOT EXISTS (
SELECT 'active site uses exist'
FROM HZ_CUST_SITE_USES_ALL su
WHERE su.cust_acct_site_id = yt.cust_acct_site_id
AND su.org_id = yt.org_id
AND su.status = 'A' );
arp_message.set_name( 'AR', 'AR_ROWS_UPDATED' );
UPDATE HZ_CUST_ACCT_SITES_ALL yt --SSUptake
SET bill_to_flag = null,
last_update_date = sysdate,
last_updated_by = hz_utility_v2pub.user_id,--arp_standard.profile.user_id,
last_update_login =hz_utility_v2pub.last_update_login,-- arp_standard.profile.last_update_login,
request_id = req_id,
program_application_id =hz_utility_v2pub.program_application_id,-- arp_standard.profile.program_application_id,
program_id =hz_utility_v2pub.program_id,-- arp_standard.profile.program_id,
program_update_date = sysdate
WHERE EXISTS (
SELECT 'Y'
FROM ra_customer_merges m
WHERE m.process_flag = 'N'
AND m.request_id = req_id
AND m.set_number = set_num
AND m.duplicate_address_id = yt.cust_acct_site_id
AND m.org_id = yt.org_id
AND m.delete_duplicate_flag = 'N' )
AND NOT EXISTS (
SELECT 'no active bill to'
FROM HZ_CUST_SITE_USES_ALL s --SSUptake
WHERE s.cust_acct_site_id = yt.cust_acct_site_id
AND s.org_id = yt.org_id
AND s.site_use_code = 'BILL_TO'
AND status = 'A' )
AND NVL(bill_to_flag, 'N') <> 'N';
arp_message.set_name( 'AR', 'AR_ROWS_UPDATED' );
UPDATE HZ_CUST_ACCT_SITES_ALL yt --SSUptake
SET ship_to_flag = null,
last_update_date = sysdate,
last_updated_by = hz_utility_v2pub.user_id,--arp_standard.profile.user_id,
last_update_login =hz_utility_v2pub.last_update_login,-- arp_standard.profile.last_update_login,
request_id = req_id,
program_application_id =hz_utility_v2pub.program_application_id,-- arp_standard.profile.program_application_id,
program_id =hz_utility_v2pub.program_id,-- arp_standard.profile.program_id,
program_update_date = sysdate
WHERE EXISTS (
SELECT 'Y'
FROM ra_customer_merges m
WHERE m.process_flag = 'N'
AND m.request_id = req_id
AND m.set_number = set_num
AND m.delete_duplicate_flag = 'N'
AND m.duplicate_address_id = yt.cust_acct_site_id
AND m.org_id = yt.org_id )
AND NOT EXISTS (
SELECT 'no active ship to'
FROM HZ_CUST_SITE_USES_ALL s --SSUptake
WHERE s.cust_acct_site_id = yt.cust_acct_site_id
AND s.org_id = yt.org_id
AND s.site_use_code = 'SHIP_TO'
AND status = 'A' )
AND NVL(ship_to_flag, 'N') <> 'N';
arp_message.set_name( 'AR', 'AR_ROWS_UPDATED' );
UPDATE HZ_CUST_ACCT_SITES_ALL yt --SSUptake
SET market_flag = null,
last_update_date = sysdate,
last_updated_by = hz_utility_v2pub.user_id,--arp_standard.profile.user_id,
last_update_login = hz_utility_v2pub.last_update_login,--arp_standard.profile.last_update_login,
request_id = req_id,
program_application_id =hz_utility_v2pub.program_application_id,-- arp_standard.profile.program_application_id,
program_id =hz_utility_v2pub.program_id,-- arp_standard.profile.program_id,
program_update_date = sysdate
WHERE EXISTS (
SELECT 'Y'
FROM ra_customer_merges m
WHERE m.process_flag = 'N'
AND m.request_id = req_id
AND m.set_number = set_num
AND m.delete_duplicate_flag = 'N'
AND m.org_id = yt.org_id
AND m.duplicate_address_id = yt.cust_acct_site_id)
AND NOT EXISTS (
SELECT 'no active market site'
FROM HZ_CUST_SITE_USES_ALL s --SSUptake
WHERE s.cust_acct_site_id = yt.cust_acct_site_id
AND s.org_id = yt.org_id
AND s.site_use_code = 'MARKET'
AND status = 'A' )
AND NVL(market_flag, 'N') <> 'N';
arp_message.set_name( 'AR', 'AR_ROWS_UPDATED' );
UPDATE hz_cust_acct_sites_all yt
SET bill_to_flag = 'P',
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 EXISTS ( SELECT 1
FROM hz_cust_site_uses_all su, ra_customer_merges m
WHERE m.request_id = req_id
AND m.set_number = set_num
AND m.customer_createsame = 'N'
AND m.process_flag = 'N'
AND m.duplicate_primary_flag = 'Y'
AND yt.cust_acct_site_id = m.customer_address_id
AND su.site_use_code = 'BILL_TO'
AND su.site_use_code = m.customer_site_code
AND su.site_use_id = m.customer_site_id
AND su.primary_flag = 'Y'
AND su.request_id = req_id
AND yt.org_id = m.org_id
AND su.org_id = m.org_id);
arp_message.set_name( 'AR', 'AR_ROWS_UPDATED' );
UPDATE hz_cust_acct_sites_all yt
SET ship_to_flag = 'P',
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 EXISTS ( SELECT 1
FROM hz_cust_site_uses_all su, ra_customer_merges m
WHERE m.request_id = req_id
AND m.set_number = set_num
AND m.customer_createsame = 'N'
AND m.process_flag = 'N'
AND m.duplicate_primary_flag = 'Y'
AND yt.cust_acct_site_id = m.customer_address_id
AND su.site_use_code = 'SHIP_TO'
AND su.site_use_code = m.customer_site_code
AND su.site_use_id = m.customer_site_id
AND su.primary_flag = 'Y'
AND su.request_id = req_id
AND su.org_id = m.org_id
AND yt.org_id = m.org_id);
arp_message.set_name( 'AR', 'AR_ROWS_UPDATED' );
UPDATE hz_cust_acct_sites_all yt
SET market_flag = 'P',
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 EXISTS ( SELECT 1
FROM hz_cust_site_uses_all su, ra_customer_merges m
WHERE m.request_id = req_id
AND m.set_number = set_num
AND m.customer_createsame = 'N'
AND m.process_flag = 'N'
AND m.duplicate_primary_flag = 'Y'
AND yt.cust_acct_site_id = m.customer_address_id
AND su.site_use_code = 'MARKET'
AND su.site_use_code = m.customer_site_code
AND su.site_use_id = m.customer_site_id
AND su.primary_flag = 'Y'
AND su.request_id = req_id
AND su.org_id = m.org_id
AND yt.org_id = m.org_id);
arp_message.set_name( 'AR', 'AR_ROWS_UPDATED' );
/*************** 'delete' mode ***************/
--Bug 1535542: Mark the rows need to be deleted by setting status to 'D'.
--Physically delete them after merge.
arp_message.set_name( 'AR', 'AR_UPDATING_TABLE' );
UPDATE HZ_CUST_ACCT_SITES_ALL yt --SSUptake
SET status = 'D'
WHERE EXISTS (
SELECT 'Y'
FROM ra_customer_merges m
WHERE m.process_flag = 'N'
AND m.request_id = req_id
AND m.set_number = set_num
AND m.delete_duplicate_flag = 'Y'
AND m.duplicate_address_id = yt.cust_acct_site_id
AND m.org_id = yt.org_id)
AND NOT EXISTS (
SELECT 'site uses exist'
FROM HZ_CUST_SITE_USES_ALL su --SSUptake
WHERE su.cust_acct_site_id = yt.cust_acct_site_id
AND su.org_id = yt.org_id
AND su.status <> 'D' );
arp_message.set_name( 'AR', 'AR_ROWS_UPDATED' );
| products, we need to mark deleted rows here
| first and physically delete them after merging one
| set in 'delete_rows'.
|
+===========================================================================*/
PROCEDURE ra_cust (
req_id NUMBER,
set_num NUMBER,
process_mode VARCHAR2
) IS
l_orig_system_ref_rec HZ_ORIG_SYSTEM_REF_PUB.ORIG_SYS_REFERENCE_REC_TYPE;
SELECT yt.cust_account_id
FROM hz_cust_accounts yt, ra_customer_merges m
WHERE 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 = 'N'
/* no active addresses */
AND NOT EXISTS (
SELECT 'active addresses exist'
FROM hz_cust_acct_sites_all addr
WHERE addr.cust_account_id = yt.cust_account_id
AND addr.status = 'A' )
FOR UPDATE NOWAIT;
SELECT cust_account_id
FROM hz_cust_accounts yt, ra_customer_merges m
WHERE 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'
/* no addresses */
AND NOT EXISTS (
SELECT 'addresses exist'
FROM hz_cust_acct_sites_all addr
WHERE addr.cust_account_id = yt.cust_account_id
AND addr.status <> 'D' )
FOR UPDATE NOWAIT;
SELECT distinct(m.customer_id), 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 (m.delete_duplicate_flag = 'N' OR m.delete_duplicate_flag = 'Y')
AND NOT EXISTS (
SELECT 'addresses exist'
FROM hz_cust_acct_sites_all addr
WHERE addr.cust_account_id = m.duplicate_id
AND addr.status = 'A');
UPDATE HZ_CUST_ACCOUNTS yt
SET status = 'I',
last_update_date = sysdate,
last_updated_by = hz_utility_v2pub.user_id,--arp_standard.profile.user_id,
last_update_login =hz_utility_v2pub.last_update_login,-- arp_standard.profile.last_update_login,
request_id = req_id,
program_application_id =hz_utility_v2pub.program_application_id,-- arp_standard.profile.program_application_id,
program_id = hz_utility_v2pub.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 m.delete_duplicate_flag = 'N' )
/* no active addresses */
AND NOT EXISTS (
SELECT 'active addresses exist'
FROM hz_cust_acct_sites_all addr
WHERE addr.cust_account_id = yt.cust_account_id
AND addr.status = 'A' );
arp_message.set_name( 'AR', 'AR_ROWS_UPDATED' );
/*************** 'delete' mode ***************/
--Bug 1535542: Mark the rows need to be deleted by setting status to 'D'.
--Physically delete them after merge.
arp_message.set_name( 'AR', 'AR_UPDATING_TABLE' );
UPDATE HZ_CUST_ACCOUNTS yt
SET status = 'D'
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 m.delete_duplicate_flag = 'Y' )
/* no addresses */
AND NOT EXISTS (
SELECT 'addresses exist'
FROM hz_cust_acct_sites_all addr
WHERE addr.cust_account_id = yt.cust_account_id
AND addr.status <> 'D' );
arp_message.set_name( 'AR', 'AR_ROWS_UPDATED' );
| tables) as opposed to a delete/inactivate.
|
| MODIFICATION HISTORY
| Jianying Huang 25-OCT-00 Customer account is global while account
| site is stripped by operating unit. We need to
| check if this account has (active)sites in
| HZ_CUST_ACCT_SITES_ALL.
| Jianying Huang 12-DEC-00 Modified cursor c1 and c2. Replace 'active
| account sites exist' with 'active accounts exist'
| Jianying Huang 20-DEC-00 Bug 1535542: Since we need to change
| the merging order, merge HZ tables before merging
| products, we need to mark deleted rows here
| first and physically delete them after merging one
| set in 'delete_rows'.
| S V Sowjanya 11-APR-06 Bug No: 4527935. Modified the code to handle the customer
| account relationship merge properly.
|
+===========================================================================*/
PROCEDURE ra_cr (
req_id NUMBER,
set_num NUMBER,
process_mode VARCHAR2
) IS
--cursor c1 is for from cust account
CURSOR c1 IS
SELECT yt.cust_account_id
FROM hz_cust_acct_relate_all yt, ra_customer_merges m --SSUptake
WHERE
--NOT EXISTS (
-- SELECT 'active accounts exist'
-- FROM hz_cust_accounts acct
-- WHERE acct.cust_account_id = yt.cust_account_id
-- AND acct.status = 'A' )
m.request_id = req_id
AND m.process_flag = 'N'
AND m.set_number = set_num
AND m.org_id = yt.org_id --SSUptake
AND yt.cust_account_id = m.duplicate_id
FOR UPDATE NOWAIT;
SELECT yt.related_cust_account_id
FROM hz_cust_acct_relate_all yt, ra_customer_merges m --SSUptake
WHERE
--NOT EXISTS (
-- SELECT 'active accounts exist'
-- FROM hz_cust_accounts acct
-- WHERE acct.cust_account_id = yt.related_cust_account_id
-- AND acct.status = 'A' )
m.request_id = req_id
AND m.process_flag = 'N'
AND m.set_number = set_num
AND m.org_id = yt.org_id --SSUptake
AND yt.related_cust_account_id = m.duplicate_id
FOR UPDATE NOWAIT;
SELECT unique yt.cust_acct_relate_id,cm.customer_id,yt.cust_account_id,yt.related_cust_account_id,
yt.customer_reciprocal_flag,nvl(yt.bill_to_flag,'N') bill_to_flag,
nvl(yt.ship_to_flag,'N') ship_to_flag,yt.rowid,yt.org_id
FROM hz_cust_acct_relate_all yt, ra_customer_merges cm
WHERE cm.request_id = req_id
AND cm.process_flag = 'N'
AND cm.set_number = set_num
AND cm.duplicate_id <> cm.customer_id --merging sites for same customer
AND cm.duplicate_id = yt.cust_account_id
AND cm.customer_id <> yt.related_cust_account_id --relationship to self not allowed
AND yt.status ='A'
AND cm.org_id = yt.org_id;
SELECT unique yt.cust_acct_relate_id,cm.customer_id,yt.cust_account_id,yt.related_cust_account_id,
yt.customer_reciprocal_flag,nvl(yt.bill_to_flag,'N') bill_to_flag,
nvl(yt.ship_to_flag,'N') ship_to_flag,yt.rowid,yt.org_id
FROM hz_cust_acct_relate_all yt, ra_customer_merges cm
WHERE cm.request_id = req_id
AND cm.process_flag = 'N'
AND cm.set_number = set_num
AND cm.duplicate_id <> cm.customer_id --merging sites for same customer
AND cm.duplicate_id = yt.related_cust_account_id
AND cm.customer_id <> yt.cust_account_id --relationship to self not allowed
AND yt.status ='A'
AND cm.org_id = yt.org_id;
SELECT cust_account_id,related_cust_account_id,customer_reciprocal_flag,
nvl(bill_to_flag,'N') bill_to_flag,nvl(ship_to_flag,'N') ship_to_flag
FROM hz_cust_acct_relate_all yt
WHERE yt.cust_account_id = p_cust_account_id
AND yt.related_cust_account_id = p_related_cust_account_id
AND yt.status = 'A'
AND yt.org_id = p_org_id
AND ROWNUM =1;
SELECT account_number FROM hz_cust_accounts
WHERE cust_account_id=p_acct_id;
l_update_flag BOOLEAN;
/************** from account update ************/
arp_message.set_name( 'AR', 'AR_UPDATING_TABLE' );
l_update_flag := false;
l_update_flag := true;
l_update_flag := true;
IF(l_update_flag) THEN
UPDATE hz_cust_acct_relate_all SET bill_to_flag = l_to_bill_to_flag,ship_to_flag=l_to_ship_to_flag
WHERE cust_account_id = l_to_cust_account_id
AND related_cust_account_id = l_to_related_cust_account_id
AND org_id = from_rec.org_id
AND STATUS = 'A';
UPDATE hz_cust_acct_relate_all yt SET
status = 'I',
last_update_date = sysdate,
last_updated_by = hz_utility_v2pub.user_id,
last_update_login = hz_utility_v2pub.last_update_login,
request_id = req_id,
program_application_id = hz_utility_v2pub.program_application_id,
program_id = hz_utility_v2pub.program_id,
program_update_date = sysdate
WHERE status = 'A'
AND yt.cust_account_id = from_rec.cust_account_id
AND yt.related_cust_account_id = from_rec.related_cust_account_id
AND yt.org_id = from_rec.org_id;
/************** to account update ************/
FOR from_rec in c_from_cust_rel_id
LOOP
l_to_cust_account_id := null;
l_update_flag := false;
l_update_flag := true;
l_update_flag := true;
IF(l_update_flag) THEN
UPDATE hz_cust_acct_relate_all SET bill_to_flag = l_to_bill_to_flag,ship_to_flag=l_to_ship_to_flag
WHERE cust_account_id = l_to_cust_account_id
AND related_cust_account_id = l_to_related_cust_account_id
AND org_id = from_rec.org_id
AND STATUS = 'A';
UPDATE hz_cust_acct_relate_all yt SET
status = 'I',
last_update_date = sysdate,
last_updated_by =hz_utility_v2pub.user_id,
last_update_login = hz_utility_v2pub.last_update_login,
request_id = req_id,
program_application_id =hz_utility_v2pub.program_application_id,
program_id = hz_utility_v2pub.program_id,
program_update_date = sysdate
WHERE status = 'A'
AND yt.cust_account_id = from_rec.cust_account_id
AND yt.related_cust_account_id = from_rec.related_cust_account_id
AND yt.org_id = from_rec.org_id;
arp_message.set_name( 'AR', 'AR_ROWS_UPDATED' );
/*UPDATE HZ_CUST_ACCT_RELATE_ALL yt
SET status = '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 EXISTS (
SELECT 'relationship to self not allowed'
FROM ra_customer_merges m
WHERE m.customer_id = yt.related_cust_account_id
AND m.duplicate_id = yt.cust_account_id
AND m.process_flag = 'N'
AND m.request_id = req_id
AND m.set_number = set_num
AND m.org_id = yt.org_id
AND m.delete_duplicate_flag = 'N' )
OR EXISTS (
SELECT 'relationship to self not allowed'
FROM ra_customer_merges m
WHERE m.customer_id = yt.cust_account_id
AND m.duplicate_id = yt.related_cust_account_id
AND m.process_flag = 'N'
AND m.request_id = req_id
AND m.set_number = set_num
AND m.org_id = yt.org_id
AND m.delete_duplicate_flag = 'N' );*/
UPDATE HZ_CUST_ACCT_RELATE_ALL YT
SET STATUS = 'I',
LAST_UPDATE_DATE = SYSDATE,
LAST_UPDATED_BY = hz_utility_v2pub.user_id ,
LAST_UPDATE_LOGIN = hz_utility_v2pub.last_update_login ,
REQUEST_ID = req_id,
PROGRAM_APPLICATION_ID = hz_utility_v2pub.program_application_id ,
PROGRAM_ID = hz_utility_v2pub.program_id ,
PROGRAM_UPDATE_DATE = SYSDATE
WHERE (RELATED_CUST_ACCOUNT_ID,CUST_ACCOUNT_ID,ORG_ID ) IN
(SELECT CUSTOMER_ID,
DUPLICATE_ID,
ORG_ID
FROM RA_CUSTOMER_MERGES M
WHERE M.PROCESS_FLAG = 'N'
AND M.REQUEST_ID = req_id
AND M.SET_NUMBER = set_num
AND M.DELETE_DUPLICATE_FLAG = 'N' );
UPDATE HZ_CUST_ACCT_RELATE_ALL YT
SET STATUS = 'I',
LAST_UPDATE_DATE = SYSDATE,
LAST_UPDATED_BY = hz_utility_v2pub.user_id ,
LAST_UPDATE_LOGIN = hz_utility_v2pub.last_update_login ,
REQUEST_ID = req_id,
PROGRAM_APPLICATION_ID = hz_utility_v2pub.program_application_id ,
PROGRAM_ID = hz_utility_v2pub.program_id ,
PROGRAM_UPDATE_DATE = SYSDATE
WHERE (CUST_ACCOUNT_ID,RELATED_CUST_ACCOUNT_ID,ORG_ID ) IN
(SELECT CUSTOMER_ID,
DUPLICATE_ID,
ORG_ID
FROM RA_CUSTOMER_MERGES M
WHERE M.PROCESS_FLAG = 'N'
AND M.REQUEST_ID = req_id
AND M.SET_NUMBER = set_num
AND M.DELETE_DUPLICATE_FLAG = 'N' );
arp_message.set_name( 'AR', 'AR_ROWS_UPDATED' );
/*************** for 'delete' mode *************/
--delete those relationships that could not be updated
--because it would produce:
--duplicate relationships
--self-relationship
--Bug 1535542: Mark the rows need to be deleted by setting status to 'D'.
--Physically delete them after merge.
arp_message.set_name( 'AR', 'AR_UPDATING_TABLE' );
/************** from account update ************/
UPDATE HZ_CUST_ACCT_RELATE_ALL yt --SSUptake
SET status = 'D'
WHERE
--NOT EXISTS (
-- SELECT 'accounts exist'
-- FROM hz_cust_accounts acct
-- WHERE acct.cust_account_id = yt.cust_account_id
-- AND acct.status <> 'D' )
EXISTS ( --SSUptake
SELECT 'Y'
FROM ra_customer_merges m
WHERE m.duplicate_id = yt.cust_account_id
AND m.process_flag = 'N'
AND m.request_id = req_id
AND m.set_number = set_num
AND m.delete_duplicate_flag = 'Y'
AND m.org_id = yt.org_id ) --SSUptake
AND ( EXISTS (
SELECT 'relationship already exists, cannot update'
FROM HZ_CUST_ACCT_RELATE_ALL r, --SSUptake
ra_customer_merges m
WHERE m.customer_id = r.cust_account_id
AND m.duplicate_id = yt.cust_account_id
AND r.related_cust_account_id = yt.related_cust_account_id
AND r.org_id = yt.org_id --SSUptake
AND m.process_flag = 'N'
AND m.request_id = req_id
AND m.set_number = set_num
AND m.delete_duplicate_flag = 'Y'
AND m.org_id = r.org_id ) --SSUptake
OR EXISTS (
SELECT 'relationship to self not allowed'
FROM ra_customer_merges m
WHERE m.customer_id = yt.related_cust_account_id
AND m.duplicate_id = yt.cust_account_id
AND m.process_flag = 'N'
AND m.request_id = req_id
AND m.set_number = set_num
AND m.delete_duplicate_flag = 'Y'
AND m.org_id = yt.org_id ) ) --SSUptake
--Bug fix 2909303
AND NOT EXISTS(
select 'merging sites for same customer, cannot update'
from ra_customer_merges m
where m.duplicate_id = m.customer_id
and m.duplicate_id = yt.cust_account_id
AND m.process_flag = 'N'
AND m.request_id = req_id
AND m.set_number = set_num
AND m.org_id = yt.org_id); --SSUptake
arp_message.set_name( 'AR', 'AR_ROWS_UPDATED' );
/************** to account update ************/
arp_message.set_name( 'AR', 'AR_UPDATING_TABLE' );
UPDATE HZ_CUST_ACCT_RELATE_ALL yt
SET status = 'D'
WHERE
--NOT EXISTS (
-- SELECT 'accounts exist'
-- FROM hz_cust_accounts acct
-- WHERE acct.cust_account_id = yt.related_cust_account_id
-- AND acct.status <> 'D' )
EXISTS (
SELECT 'Y'
FROM ra_customer_merges m
WHERE yt.related_cust_account_id = m.duplicate_id
AND m.org_id = yt.org_id --SSUptake
AND m.process_flag = 'N'
AND m.request_id = req_id
AND m.set_number = set_num
AND m.delete_duplicate_flag = 'Y')
AND ( EXISTS (
SELECT 'relationship already exists, cannot update'
FROM HZ_CUST_ACCT_RELATE_ALL r,
ra_customer_merges m
WHERE m.customer_id = r.related_cust_account_id
AND m.duplicate_id = yt.related_cust_account_id
AND r.cust_account_id = yt.cust_account_id
AND r.org_id = yt.org_id --SSUptake
AND m.org_id = r.org_id
AND m.process_flag = 'N'
AND m.request_id = req_id
AND m.set_number = set_num
AND m.delete_duplicate_flag = 'Y')--SSUptake
OR EXISTS (
SELECT 'relationship to self not allowed'
FROM ra_customer_merges m
WHERE m.customer_id = yt.cust_account_id
AND m.duplicate_id = yt.related_cust_account_id
AND m.org_id = yt.org_id --SSUptake
AND m.process_flag = 'N'
AND m.request_id = req_id
AND m.set_number = set_num
AND m.delete_duplicate_flag = 'Y' ) )
--Bug fix 2909303
AND NOT EXISTS(
select 'merging sites for same customer, cannot update'
from ra_customer_merges m
where m.duplicate_id = m.customer_id
and m.duplicate_id = yt.related_cust_account_id
AND m.org_id = yt.org_id --SSUptake
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' );
| not have status column, we need to move the delete part
| to 'delete_rows' procedure.
| Victoria Crisostomo 01-FEB-01
| Bug 1611619 : include customer_id in where condition
| of update statement to force use of an existing index
| S.V.Sowjanya 29-JUL-2004 Bug No 3786802: Declared 3 pl/sql tables "header_id,receipt_id,end_date"
| and 1 local variable "new_date".
| Customer_merge_header_id , Cust_receipt_method_id and
| end_date are bulk collected into the pl/sql tables
| header_id,receipt_id,end_date.
| While inserting auditing values into the table
| HZ_CUSTOMER_MERGE_LOG and updating RA_CUST_RECEIPT_METHODS
| , values stored in pl/sql tables are used.
| Commented the insert statement and update statements.
|
+===========================================================================*/
PROCEDURE ra_crm (
req_id NUMBER,
set_num NUMBER,
process_mode VARCHAR2
) IS
l_count NUMBER;
SELECT CUST_RECEIPT_METHOD_ID
FROM RA_CUST_RECEIPT_METHODS ra, ra_customer_merges m
WHERE ra.customer_id = m.duplicate_id
AND site_use_id = m.duplicate_site_id
AND m.process_flag = 'N'
AND m.request_id = req_id
AND m.set_number = set_num
AND m.delete_duplicate_flag = 'N'
FOR UPDATE NOWAIT;
SELECT CUST_RECEIPT_METHOD_ID
FROM RA_CUST_RECEIPT_METHODS yt, ra_customer_merges m
WHERE yt.customer_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 = 'N'
AND site_use_id IS NULL
AND NOT EXISTS (
SELECT 'active accounts exist'
FROM hz_cust_accounts acct
WHERE acct.cust_account_id = yt.customer_id
AND acct.status = 'A' )
FOR UPDATE NOWAIT;
SELECT distinct CUSTOMER_MERGE_HEADER_ID,
CUST_RECEIPT_METHOD_ID,
END_DATE
BULK COLLECT INTO header_id,receipt_id,end_date
FROM RA_CUST_RECEIPT_METHODS yt, ra_customer_merges m
WHERE (yt.CUSTOMER_ID = m.DUPLICATE_ID
AND ( ( yt.SITE_USE_ID IS NULL
AND NOT EXISTS (
SELECT 'active accounts exist'
FROM hz_cust_accounts acct
WHERE acct.cust_account_id = yt.customer_id
AND acct.status = 'A' )
)
OR (yt.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,
MERGE_HEADER_ID,
TABLE_NAME,
PRIMARY_KEY_ID,
DATE_COL1_ORIG,
DATE_COL1_NEW,
REQUEST_ID,
-- Bug 2707587 : Added standard who columns (created_by, creation_date,
-- last_update_by, last_update_date, last_update_login)
-- and ACTION_FLAG column into insert statemnt.
CREATED_BY,
CREATION_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATE_LOGIN,
ACTION_FLAG
)
VALUES (
HZ_CUSTOMER_MERGE_LOG_s.nextval,
header_id(i),
'RA_CUST_RECEIPT_METHODS',
receipt_id(i),
end_date(i),
new_date,
req_id,
hz_utility_v2pub.CREATED_BY,
hz_utility_v2pub.CREATION_DATE,
hz_utility_v2pub.LAST_UPDATED_BY,
hz_utility_v2pub.LAST_UPDATE_DATE,
hz_utility_v2pub.LAST_UPDATE_LOGIN,
'U'
) ;
/* INSERT INTO HZ_CUSTOMER_MERGE_LOG (
MERGE_LOG_ID,
MERGE_HEADER_ID,
TABLE_NAME,
PRIMARY_KEY_ID,
DATE_COL1_ORIG,
DATE_COL1_NEW,
REQUEST_ID,
-- Bug 2707587 : Added standard who columns (created_by, creation_date,
-- last_update_by, last_update_date, last_update_login)
-- and ACTION_FLAG column into insert statemnt.
CREATED_BY,
CREATION_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATE_LOGIN,
ACTION_FLAG
) SELECT HZ_CUSTOMER_MERGE_LOG_s.nextval,
CUSTOMER_MERGE_HEADER_ID,
'RA_CUST_RECEIPT_METHODS',
CUST_RECEIPT_METHOD_ID,
END_DATE,
SYSDATE,
req_id,
hz_utility_v2pub.CREATED_BY,
hz_utility_v2pub.CREATION_DATE,
hz_utility_v2pub.LAST_UPDATED_BY,
hz_utility_v2pub.LAST_UPDATE_DATE,
hz_utility_v2pub.LAST_UPDATE_LOGIN,
'U'
FROM (
SELECT distinct CUSTOMER_MERGE_HEADER_ID,
CUST_RECEIPT_METHOD_ID,
END_DATE
FROM RA_CUST_RECEIPT_METHODS yt, ra_customer_merges m
WHERE (yt.CUSTOMER_ID = m.DUPLICATE_ID
AND ( ( yt.SITE_USE_ID IS NULL
AND NOT EXISTS (
SELECT 'active accounts exist'
FROM hz_cust_accounts acct
WHERE acct.cust_account_id = yt.customer_id
AND acct.status = 'A' )
)
OR (yt.site_use_id = m.DUPLICATE_SITE_ID)
)
)
AND m.process_flag = 'N'
AND m.request_id = req_id
AND m.set_number = set_num
);*/
| UPDATE RA_CUST_RECEIPT_METHODS yt
| SET end_date = sysdate,
| 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_id, site_use_id) IN (
| SELECT m.duplicate_id, m.duplicate_site_id
| FROM ra_customer_merges m
| WHERE m.process_flag = 'N'
| AND m.request_id = req_id
| AND m.set_number = set_num
| AND m.delete_duplicate_flag = 'N' ); */
UPDATE RA_CUST_RECEIPT_METHODS yt SET (
END_DATE) = (
SELECT DATE_COL1_NEW
FROM HZ_CUSTOMER_MERGE_LOG l
WHERE l.REQUEST_ID = req_id
AND l.TABLE_NAME = 'RA_CUST_RECEIPT_METHODS'
AND l.PRIMARY_KEY_ID = CUST_RECEIPT_METHOD_ID
)
, 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_RECEIPT_METHOD_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 = 'RA_CUST_RECEIPT_METHODS'
AND l1.REQUEST_ID = req_id
AND h.set_number = set_num);
UPDATE RA_CUST_RECEIPT_METHODS yt SET
END_DATE = new_date
, LAST_UPDATE_DATE=sysdate
, last_updated_by=hz_utility_v2pub.user_id--arp_standard.profile.user_id
, last_update_login=hz_utility_v2pub.last_update_login--arp_standard.profile.last_update_login
, REQUEST_ID=req_id
, PROGRAM_APPLICATION_ID=hz_utility_v2pub.program_application_id--arp_standard.profile.program_application_id
, PROGRAM_ID=hz_utility_v2pub.program_id--arp_standard.profile.program_id
, PROGRAM_UPDATE_DATE=SYSDATE
WHERE CUST_RECEIPT_METHOD_ID = receipt_id(i);
arp_message.set_name( 'AR', 'AR_ROWS_UPDATED' );
| UPDATE RA_CUST_RECEIPT_METHODS yt
| set end_date = sysdate,
| 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_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 m.delete_duplicate_flag = 'N' )
| AND site_use_id IS NULL
| AND NOT EXISTS (
| SELECT 'active accounts exist'
| FROM hz_cust_accounts acct
| WHERE acct.cust_account_id = yt.customer_id
| AND acct.status = 'A' );
| arp_message.set_name( 'AR', 'AR_ROWS_UPDATED' );
| delete rows till the end of merge, we can move
| the call of 'copy_contacts' right before we migrate
| org contacts and contact points.
|
+===========================================================================*/
PROCEDURE copy_contacts (
req_id NUMBER,
set_num NUMBER,
process_mode VARCHAR2
) IS
--party ids
from_party_id NUMBER;
SELECT DISTINCT duplicate_id, customer_id
FROM ra_customer_merges
WHERE process_flag = 'N'
AND request_id = req_id
AND set_number = set_num
AND duplicate_id <> customer_id;
SELECT party_id, status into from_party_id, from_account_status
FROM hz_cust_accounts
WHERE cust_account_id = from_account_id;
SELECT party_id into to_party_id
FROM hz_cust_accounts
WHERE cust_account_id = to_account_id;
SELECT DISTINCT duplicate_id, customer_id,
duplicate_address_id, customer_address_id
FROM ra_customer_merges
WHERE process_flag = 'N'
AND request_id = req_id
AND set_number = set_num
AND duplicate_address_id <> -1; --4693912
SELECT party_site_id into from_party_site_id
FROM hz_cust_acct_sites_all
WHERE cust_acct_site_id = from_site_id;
SELECT party_site_id into to_party_site_id
FROM hz_cust_acct_sites_all
WHERE cust_acct_site_id = to_site_id;
SELECT cust_account_role_id
FROM hz_cust_account_roles yt, ra_customer_merges m
WHERE cust_acct_site_id = m.duplicate_address_id
AND m.process_flag = 'N'
AND m.request_id = req_id
AND m.set_number = set_num
AND role_type = 'CONTACT'
FOR UPDATE NOWAIT;
SELECT cust_account_role_id
FROM hz_cust_account_roles yt, ra_customer_merges m
WHERE cust_account_id = m.duplicate_id
AND m.process_flag = 'N'
AND m.request_id = req_id
AND m.set_number = set_num
AND cust_acct_site_id IS NULL
AND role_type = 'CONTACT'
FOR UPDATE NOWAIT;
SELECT cust_account_role_id
FROM hz_cust_account_roles yt, ra_customer_merges m, hz_cust_accounts c
WHERE yt.cust_account_id = m.duplicate_id
AND c.cust_account_id = m.duplicate_id
AND c.status <> 'A'
AND m.process_flag = 'N'
AND m.request_id = req_id
AND m.set_number = set_num
AND yt.cust_acct_site_id IS NULL
AND yt.role_type = 'CONTACT'
FOR UPDATE NOWAIT;
SELECT yt.party_id
FROM HZ_CUST_ACCOUNT_ROLES yt,ra_customer_merges m,HZ_CUST_ACCOUNTS ca,hz_relationships rel
WHERE m.customer_id=ca.cust_account_id
AND ((ca.status = 'A' and yt.cust_acct_site_id is not null) or (ca.status <> 'A'))
AND m.duplicate_id = yt.cust_account_id
AND rel.party_id = yt.party_id
AND rel.subject_type = 'PERSON'
AND rel.subject_id = ca.party_id
AND yt.role_type = 'CONTACT'
AND m.request_id = req_id
AND m.process_flag ='N'
AND m.set_number =set_num
AND rownum =1;
SELECT yt.party_id
FROM HZ_CUST_ACCOUNT_ROLES yt,ra_customer_merges m,HZ_CUST_ACCOUNTS ca,hz_relationships rel
WHERE m.customer_id=ca.cust_account_id
AND m.duplicate_id = yt.cust_account_id
AND rel.party_id = yt.party_id
AND rel.subject_type = 'PERSON'
AND rel.subject_id = ca.party_id
AND yt.role_type = 'CONTACT'
AND m.request_id = req_id
AND m.process_flag ='N'
AND m.set_number =set_num
AND rownum =1;
UPDATE HZ_CUST_ACCOUNT_ROLES yt
SET status = 'I',
last_update_date = sysdate,
last_updated_by = hz_utility_v2pub.user_id,
last_update_login = hz_utility_v2pub.last_update_login,
request_id = req_id,
program_application_id = hz_utility_v2pub.program_application_id,
program_id = hz_utility_v2pub.program_id,
program_update_date = sysdate
WHERE party_id = l_party_id
AND nvl(status,'A') ='A';
arp_message.set_name( 'AR', 'AR_ROWS_UPDATED' );
/************** account site level update ************/
arp_message.set_name( 'AR', 'AR_UPDATING_TABLE');
UPDATE HZ_CUST_ACCOUNT_ROLES yt
SET (cust_account_id, cust_acct_site_id) = (
SELECT MIN(m.customer_id), MIN(m.customer_address_id)
FROM ra_customer_merges m
WHERE yt.cust_account_id = m.duplicate_id
AND yt.cust_acct_site_id = m.duplicate_address_id
AND m.request_id = req_id
AND m.set_number = set_num
AND m.process_flag = 'N' ),
last_update_date = sysdate,
last_updated_by = hz_utility_v2pub.user_id,--arp_standard.profile.user_id,
last_update_login =hz_utility_v2pub.last_update_login,-- arp_standard.profile.last_update_login,
request_id = req_id,
program_application_id =hz_utility_v2pub.program_application_id,-- arp_standard.profile.program_application_id,
program_id =hz_utility_v2pub.program_id,-- arp_standard.profile.program_id,
program_update_date = sysdate
WHERE cust_acct_site_id IN (
SELECT m.duplicate_address_id
FROM ra_customer_merges m
WHERE m.process_flag = 'N'
AND m.request_id = req_id
AND m.set_number = set_num )
AND role_type = 'CONTACT'
AND party_id <> nvl(l_party_id,-99);---Bug No. 5067291
arp_message.set_name( 'AR', 'AR_ROWS_UPDATED' );
/************** account level update ************/
arp_message.set_name( 'AR', 'AR_UPDATING_TABLE' );
UPDATE HZ_CUST_ACCOUNT_ROLES yt
SET cust_account_id = (
SELECT m.customer_id
FROM ra_customer_merges m
WHERE yt.cust_account_id = m.duplicate_id
AND m.request_id = req_id
AND m.process_flag = 'N'
AND m.set_number = set_num
AND ROWNUM = 1 ),
last_update_date = sysdate,
last_updated_by = hz_utility_v2pub.user_id,--arp_standard.profile.user_id,
last_update_login =hz_utility_v2pub.last_update_login,-- arp_standard.profile.last_update_login,
request_id = req_id,
program_application_id =hz_utility_v2pub.program_application_id,-- arp_standard.profile.program_application_id,
program_id =hz_utility_v2pub.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 cust_acct_site_id IS NULL
AND role_type = 'CONTACT'
AND party_id <> nvl(l_party_id,-99);--Bug NO. 5067291
SELECT min(cust_account_role_id) cust_account_role_id, cust_account_id,
nvl(cust_acct_site_id,-1) cust_acct_site_id,party_id
FROM hz_cust_account_roles r, ra_customer_merges m
where m.request_id = req_id AND m.set_number = set_num
AND r.cust_account_id = m.customer_id AND m.process_flag = 'N'
GROUP BY cust_account_id,cust_acct_site_id,status,party_id
HAVING NVL(STATUS,'A') ='A' AND count(1) > 1) LOOP
UPDATE hz_cust_account_roles SET status ='I'
WHERE cust_account_role_id <> rec.cust_account_role_id
AND cust_account_id = rec.cust_account_id
AND party_id = rec.party_id
AND nvl(cust_acct_site_id,-1) = rec.cust_acct_site_id
AND nvl(status,'A') = 'A';
arp_message.set_name( 'AR', 'AR_ROWS_UPDATED' );
UPDATE HZ_CUST_ACCOUNT_ROLES yt
SET status = 'I',
last_update_date = sysdate,
last_updated_by = hz_utility_v2pub.user_id,
last_update_login = hz_utility_v2pub.last_update_login,
request_id = req_id,
program_application_id = hz_utility_v2pub.program_application_id,
program_id = hz_utility_v2pub.program_id,
program_update_date = sysdate
WHERE party_id = l_party_id
AND nvl(status,'A') ='A';
arp_message.set_name( 'AR', 'AR_ROWS_UPDATED' );
/************** account site level update ************/
arp_message.set_name( 'AR', 'AR_UPDATING_TABLE');
UPDATE HZ_CUST_ACCOUNT_ROLES yt
SET (cust_account_id, cust_acct_site_id) = (
SELECT MIN(m.customer_id), MIN(m.customer_address_id)
FROM ra_customer_merges m
WHERE yt.cust_account_id = m.duplicate_id
AND yt.cust_acct_site_id = m.duplicate_address_id
AND m.request_id = req_id
AND m.set_number = set_num
AND m.process_flag = 'N' ),
last_update_date = sysdate,
last_updated_by = hz_utility_v2pub.user_id,--arp_standard.profile.user_id,
last_update_login =hz_utility_v2pub.last_update_login,-- arp_standard.profile.last_update_login,
request_id = req_id,
program_application_id =hz_utility_v2pub.program_application_id,-- arp_standard.profile.program_application_id,
program_id =hz_utility_v2pub.program_id,-- arp_standard.profile.program_id,
program_update_date = sysdate
WHERE cust_acct_site_id IN (
SELECT m.duplicate_address_id
FROM ra_customer_merges m
WHERE M.PROCESS_FLAG = 'N'
AND m.request_id = req_id
AND M.SET_NUMBER = SET_NUM )
AND role_type = 'CONTACT'
AND party_id <> nvl(l_party_id,-99);---Bug No. 5067291
ARP_MESSAGE.SET_NAME( 'AR', 'AR_ROWS_UPDATED' );
/************** account level update ************/
arp_message.set_name( 'AR', 'AR_UPDATING_TABLE' );
UPDATE HZ_CUST_ACCOUNT_ROLES yt
SET cust_account_id = (
SELECT m.customer_id
FROM ra_customer_merges m
WHERE yt.cust_account_id = m.duplicate_id
AND m.request_id = req_id
AND m.process_flag = 'N'
AND m.set_number = set_num
AND ROWNUM = 1 ),
last_update_date = sysdate,
last_updated_by = hz_utility_v2pub.user_id,--arp_standard.profile.user_id,
last_update_login =hz_utility_v2pub.last_update_login,-- arp_standard.profile.last_update_login,
request_id = req_id,
program_application_id =hz_utility_v2pub.program_application_id,-- arp_standard.profile.program_application_id,
program_id =hz_utility_v2pub.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, hz_cust_accounts c
WHERE m.process_flag = 'N'
AND m.request_id = req_id
AND m.set_number = set_num
AND c.cust_account_id = m.duplicate_id
AND c.status <> 'A')
AND cust_acct_site_id IS NULL
AND ROLE_TYPE = 'CONTACT'
AND party_id <> nvl(l_party_id,-99);--Bug NO. 5067291
SELECT min(cust_account_role_id) cust_account_role_id, cust_account_id,
nvl(cust_acct_site_id,-1) cust_acct_site_id,party_id
FROM hz_cust_account_roles r, ra_customer_merges m
where m.request_id = req_id AND m.set_number = set_num
AND r.cust_account_id = m.customer_id AND m.process_flag = 'N'
GROUP BY cust_account_id,cust_acct_site_id,status,party_id
HAVING NVL(STATUS,'A') ='A' AND count(1) > 1) LOOP
UPDATE hz_cust_account_roles SET status ='I'
WHERE cust_account_role_id <> rec.cust_account_role_id
AND cust_account_id = rec.cust_account_id
AND party_id = rec.party_id
AND nvl(cust_acct_site_id,-1) = rec.cust_acct_site_id
AND nvl(status,'A') = 'A';
arp_message.set_name( 'AR', 'AR_ROWS_UPDATED' );
| do not delete rows in hz_cust_contact_points.
| Jianying Huang 16-DEC-00 As per discussion with Gautam Prothia,
| since we changed veiw RA_PHONES (see bug 1487607),
| we will migrate phones and org contacts no matter the
| account is active or inactive after merge. Also, we
| will migrate these contact points in party level, not
| in account level.
| Jianying Huang 03-APR-01 The procedure is not being calling anymore
| because table hz_cust_contact_points has been obsoleted.
|
+===========================================================================*/
PROCEDURE ra_ph (
req_id NUMBER,
set_num NUMBER,
process_mode VARCHAR2
) IS
--cursor c1 and c2 are used in 'inactivate' mode
CURSOR c1 IS
SELECT cust_contact_point_id
FROM hz_cust_contact_points yt, ra_customer_merges m
WHERE cust_account_site_id = m.duplicate_address_id
AND m.process_flag = 'N'
AND m.request_id = req_id
AND m.set_number = set_num
FOR UPDATE NOWAIT;
SELECT cust_contact_point_id
FROM hz_cust_contact_points yt, ra_customer_merges m
WHERE cust_account_id = m.duplicate_id
AND m.process_flag = 'N'
AND m.request_id = req_id
AND m.set_number = set_num
AND cust_account_site_id IS NULL
FOR UPDATE NOWAIT;
UPDATE HZ_CUST_CONTACT_POINTS yt
SET (cust_account_id, cust_account_site_id) = (
SELECT min(m.customer_id), min(m.customer_address_id)
FROM ra_customer_merges m
WHERE yt.cust_account_id = m.duplicate_id
AND yt.cust_account_site_id = m.duplicate_address_id
AND m.request_id = req_id
AND m.set_number = set_num
AND m.process_flag = 'N' ),
last_update_date = sysdate,
last_updated_by = hz_utility_v2pub.user_id,--arp_standard.profile.user_id,
last_update_login =hz_utility_v2pub.last_update_login,-- arp_standard.profile.last_update_login,
request_id = req_id,
program_application_id =hz_utility_v2pub.program_application_id,-- arp_standard.profile.program_application_id,
program_id =hz_utility_v2pub.program_id,-- arp_standard.profile.program_id,
program_update_date = sysdate
WHERE cust_account_site_id IN (
SELECT m.duplicate_address_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' );
UPDATE HZ_CUST_CONTACT_POINTS yt
SET cust_account_id = (
SELECT DISTINCT m.customer_id
FROM ra_customer_merges m
WHERE yt.cust_account_id = m.duplicate_id
AND m.request_id = req_id
AND m.process_flag = 'N'
AND m.set_number = set_num),
last_update_date = sysdate,
last_updated_by = hz_utility_v2pub.user_id,--arp_standard.profile.user_id,
last_update_login =hz_utility_v2pub.last_update_login,-- arp_standard.profile.last_update_login,
request_id = req_id,
program_application_id =hz_utility_v2pub.program_application_id,-- arp_standard.profile.program_application_id,
program_id =hz_utility_v2pub.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 cust_account_site_id IS NULL;
arp_message.set_name( 'AR', 'AR_ROWS_UPDATED' );
| S V Sowjanya 17-AUG-2005 Bug No:4558247. Changed the WHERE clause of second UPDATE statement
| so that it will update only if m.delete_duplicate_flag='Y'
|
|
| 28-OCT-2005 Anuj Singhal Bug No:4558392. In procedure ra_usg,added an update to inactivate active 'CUSTOMER' usage
| of merge-from party when delete after merge is checked for the following condition.If
| there exists any other Inactive accounts AND doesnot exist any active accounts associated
| with the merge-from-party AND there is no other customer usage associated
| with that party. Also changed the where clause of the third update of the | same procedure so that it deletes the customer usage there are any other
| customer usages associated with that party.
| Also updated the who columns in the HZ_PARTY_USG_ASSIGNMENTS table
| while inactivating the party usg assignment.
|
|
+===========================================================================*/
PROCEDURE ra_usg (
req_id NUMBER,
set_num NUMBER,
process_mode VARCHAR2
) IS
l_dummy varchar2(20);
SELECT party_usg_assignment_id
FROM hz_party_usg_assignments pu
WHERE party_usage_code = 'CUSTOMER'
AND EXISTS (SELECT 'Y'
FROM ra_customer_merges m, hz_cust_accounts c1
WHERE m.duplicate_id = c1.cust_account_id
AND c1.party_id = pu.party_id
AND m.process_flag = 'N'
AND m.request_id = req_id
AND m.set_number = set_num
);
UPDATE hz_party_usg_assignments pu
SET effective_end_date = trunc(sysdate),
status_flag = 'I',
--Bug No. 4558392
last_update_date=sysdate,
last_updated_by = hz_utility_v2pub.user_id,--arp_standard.profile.user_id,
last_update_login =hz_utility_v2pub.last_update_login,-- arp_standard.profile.last_update_login,
request_id = req_id,
program_application_id =hz_utility_v2pub.program_application_id,-- arp_standard.profile.program_application_id,
program_id =hz_utility_v2pub.program_id-- arp_standard.profile.program_id
--Bug No. 4558392
WHERE party_usage_code = 'CUSTOMER'
AND nvl(effective_end_date,sysdate+1)>=sysdate
AND status_flag = 'A'
AND NOT EXISTS( SELECT 'Y' FROM hz_cust_accounts c
WHERE c.party_id = pu.party_id
AND c.status ='A'
)
AND EXISTS (SELECT 'Y'
FROM ra_customer_merges m, hz_cust_accounts c1
WHERE m.request_id = req_id
AND m.process_flag = 'N'
AND m.set_number = set_num
AND m.delete_duplicate_flag = 'N'
AND m.customer_id <> c1.cust_account_id --Site merge
AND m.duplicate_id = c1.cust_account_id
AND c1.party_ID = pu.party_id
AND c1.status = 'I'
);
arp_message.set_name( 'AR', 'AR_ROWS_UPDATED' );
/*************** 'delete' mode ***************/
---Bug No. 4558392
arp_message.set_name( 'AR', 'AR_UPDATING_TABLE' );
UPDATE hz_party_usg_assignments pu
SET effective_end_date = trunc(sysdate),
status_flag = 'I',
last_update_date=sysdate,
last_updated_by = hz_utility_v2pub.user_id,--arp_standard.profile.user_id,
last_update_login =hz_utility_v2pub.last_update_login,-- arp_standard.profile.last_update_login,
request_id = req_id,
program_application_id =hz_utility_v2pub.program_application_id,-- arp_standard.profile.program_application_id,
program_id =hz_utility_v2pub.program_id-- arp_standard.profile.program_id
WHERE party_usage_code = 'CUSTOMER'
AND nvl(effective_end_date,sysdate+1)>=sysdate
AND status_flag = 'A'
AND EXISTS( SELECT 'Y' FROM hz_cust_accounts c
WHERE c.party_id = pu.party_id
AND c.status ='I'
AND rownum=1
)
AND NOT EXISTS( SELECT 'Y' FROM hz_cust_accounts c
WHERE c.party_id = pu.party_id
AND c.status ='A'
AND rownum=1
)
AND NOT EXISTS (SELECT 'Y'
from hz_party_usg_assignments
where party_id=pu.party_id
and party_usage_code='CUSTOMER'
and party_usg_assignment_id <> pu.party_usg_assignment_id
and rownum=1)
AND EXISTS (SELECT 'Y'
FROM ra_customer_merges m, hz_cust_accounts c1
WHERE m.request_id = req_id
AND m.process_flag = 'N'
AND m.set_number = set_num
AND m.delete_duplicate_flag = 'Y'--Bug No. 4558392
AND m.customer_id <> c1.cust_account_id --Site merge
AND m.duplicate_id = c1.cust_account_id
AND c1.party_ID = pu.party_id
AND c1.status = 'D'--Bug No. 4558392
);
arp_message.set_name( 'AR', 'AR_ROWS_UPDATED' );
/*************** 'delete' mode ***************/
arp_message.set_name( 'AR', 'AR_UPDATING_TABLE' );
--Delete all 'CUSTOMER' usage of merge-from party
--if the merge-from account is the only account for merge-from party OR there are any other customer usages associated with that party.
UPDATE hz_party_usg_assignments pu
SET effective_end_date = trunc(sysdate),
status_flag = 'D'
WHERE pu.party_usage_code = 'CUSTOMER'
AND (
NOT EXISTS (SELECT 'Y'
FROM hz_cust_accounts c --delete if from account is the only account for merge-from party
WHERE c.party_id = pu.party_id
AND c.status in ('A','I')
)
--Bug No.4558392
OR ( pu.status_flag='A' AND nvl(pu.effective_end_date,sysdate+1)>=sysdate AND
exists (SELECT 'Y'
from hz_party_usg_assignments
where party_id=pu.party_id
and party_usage_code='CUSTOMER'
and party_usg_assignment_id <> pu.party_usg_assignment_id
and rownum=1)
)
)
/* OR (pu.status_flag = 'A' AND trunc(sysdate) < pu.effective_end_date --delete only active usage if merge-from party has inactive accounts
AND NOT EXISTS (SELECT 'Y' FROM hz_cust_accounts c
WHERE c.party_id = pu.party_id
AND c.status = 'A')
)*/
----Bug No.4558392
AND EXISTS (SELECT 'Y'
FROM ra_customer_merges m, hz_cust_accounts c1
WHERE m.request_id = req_id
AND m.process_flag = 'N'
AND m.set_number = set_num
AND m.delete_duplicate_flag = 'Y'
AND m.customer_id <> c1.cust_account_id --Site merge
AND m.duplicate_id = c1.cust_account_id
AND c1.party_ID = pu.party_id
AND c1.status = 'D'
);
arp_message.set_name( 'AR', 'AR_ROWS_UPDATED' );
| do_update_dup_party_rec
| do_update_dup_party_rel_rec
| because HZ_PARTY_PUB.create_org_contact will
| create party_relationship and party automatically.
| The new party_relationship_rec =
| org_contact_rec.party_rel_rec ) and
| the new party_rec =
| org_contact_rec.party_rel_rec.party_rec
| Jianying Huang 25-OCT-00 After call create_org_contact API,
| if return_status is not 'success', we need to
| populate an exception.
| Jianying Huangn 27-OCT-00 Since contacts of 'phone' type also goes
| as customer contact points, we'd better to migrate
| it here because it should be a phone number for
| contacts as well as for accounts.
| Jianying Huang 16-DEC-00 Move common code to do_merge_contacts.
|
|
+===========================================================================*/
PROCEDURE do_cust_merge_contacts (
p_from_party_id NUMBER,
p_to_party_id NUMBER,
p_from_account_id NUMBER,
p_to_account_id NUMBER
) IS
l_org_contact_id NUMBER;
SELECT
--Account Role
cust_account_role_id,
--FOR PARTY REC
rel.party_id,
--FOR PARTY REL REC
rel.relationship_id,
-- FOR ORG-CONTACT REC
org.org_contact_id
FROM hz_cust_account_roles acct_role,
hz_org_contacts org,
hz_relationships rel,
hz_cust_accounts acct
WHERE acct_role.role_type = 'CONTACT'
AND acct_role.cust_account_id = p_from_account_id
AND acct_role.cust_acct_site_id IS NULL
AND acct_role.party_id = rel.party_id
AND org.party_relationship_id = rel.relationship_id
AND rel.subject_table_name = 'HZ_PARTIES'
AND rel.object_table_name = 'HZ_PARTIES'
AND acct_role.cust_account_id = acct.cust_account_id
AND acct.party_id = rel.object_id
AND rel.subject_id <> p_to_party_id ;--5067291
UPDATE hz_cust_account_roles
SET party_id = x_org_party_id
WHERE cust_account_role_id = l_cust_acct_role_id;
| do_update_dup_party_rec
| do_update_dup_party_rel_rec
| because HZ_PARTY_PUB.create_org_contact will
| create party_relationship and party automatically.
| The new party_relationship_rec =
| org_contact_rec.party_rel_rec ) and
| the new party_rec =
| org_contact_rec.party_rel_rec.party_rec
| Jianying Huang 25-OCT-00 After call create_org_contact API,
| if return_status is not 'success', we need to
| populate an exception.
| Jianying Huangn 27-OCT-00 Since contacts of 'phone' type also goes
| as customer contact points, we'd better to migrate
| it here because it should be a phone number for
| contacts as well as for accounts.
| Jianying Huang 16-DEC-00 Move common code to do_merge_contacts.
| Jianying Huang 28-DEC-00 Since we ignore hz_org_contacts.party_site_id
| in account merge context, we do not need select merge-to's
| address id.
|
+===========================================================================*/
PROCEDURE do_site_merge_contacts(
p_from_party_id NUMBER,
p_to_party_id NUMBER,
p_from_account_id NUMBER,
p_to_account_id NUMBER,
p_req_id NUMBER,
p_set_num NUMBER
) IS
--account site ids
from_site_id NUMBER;
SELECT
-- Account Role
acct_role.cust_account_role_id,
-- FOR PARTY REC
rel.party_id,
-- FOR PARTY REL REC
rel.relationship_id,
-- FOR ORG-CONTACT REC
org.org_contact_id
--as per discussion with Gautam Prothia, we ignore party_site_id in customer
--merge context
-- org.party_site_id
FROM hz_cust_account_roles acct_role,
hz_org_contacts org,
hz_relationships rel,
hz_cust_accounts acct
WHERE acct_role.role_type = 'CONTACT'
AND acct_role.cust_account_id = p_from_account_id
AND acct_role.cust_acct_site_id = from_site_id
AND acct_role.party_id = rel.party_id
AND org.party_relationship_id = rel.relationship_id
AND rel.subject_table_name = 'HZ_PARTIES'
AND rel.object_table_name = 'HZ_PARTIES'
AND acct_role.cust_account_id = acct.cust_account_id
AND acct.party_id = rel.object_id
AND rel.subject_id <> p_to_party_id;--5067291
SELECT DISTINCT duplicate_address_id --, customer_address_id
FROM ra_customer_merges
WHERE duplicate_id = p_from_account_id
AND customer_id = p_to_account_id
AND process_flag = 'N'
AND request_id = p_req_id
AND set_number = p_set_num;
SELECT ass.party_site_id into from_party_site_id
FROM hz_cust_acct_sites ass
WHERE cust_acct_site_id = from_site_id;
SELECT ass.party_site_id into to_party_site_id
FROM hz_cust_acct_sites ass
WHERE cust_acct_site_id = to_site_id;
UPDATE hz_cust_account_roles
SET party_id = x_org_party_id
WHERE cust_account_role_id = l_cust_acct_role_id;
l_insert VARCHAR2(10) := FND_API.G_FALSE;
CURSOR c_party_type(p_party_id NUMBER) IS SELECT party_type from hz_parties
where party_id = p_party_id;
l_sql := 'SELECT new_id ' ||
'FROM ' || g_table_name || ' ' ||
'WHERE old_id = :id' || ' ' ||
'AND type = ''ORG_CONTACT''';
l_insert := FND_API.G_TRUE;
SELECT party_id INTO x_org_party_id
FROM hz_relationships
WHERE relationship_id = (
SELECT party_relationship_id
FROM hz_org_contacts
WHERE org_contact_id = x_org_contact_id )
AND subject_table_name = 'HZ_PARTIES'
AND object_table_name = 'HZ_PARTIES'
AND rownum = 1;
l_insert := FND_API.G_TRUE;
IF l_insert = FND_API.G_TRUE THEN
--Bug 1535542: insert the mapping of old org_contact_id and new org_contact_id
BEGIN
l_sql := 'INSERT INTO ' || g_table_name || ' ' ||
'VALUES (' ||
'''ORG_CONTACT''' || ',' ||
to_char(p_org_contact_id) || ', ' ||
to_char(x_org_contact_id) || ')';
SELECT contact_point_id
FROM hz_contact_points
WHERE owner_table_name = p_owner_table_name
AND owner_table_id = p_from_id;
UPDATE hz_cust_contact_points
SET contact_point_id = x_contact_point_id
WHERE contact_point_id = l_contact_point_id
AND cust_account_id = p_from_account_id;
SELECT
MIN(ORG_CONTACT_ID)
FROM HZ_ORG_CONTACTS org
WHERE DEPARTMENT_CODE ||
DEPARTMENT ||
TITLE ||
JOB_TITLE ||
MAIL_STOP ||
DECISION_MAKER_FLAG ||
JOB_TITLE_CODE ||
TO_CHAR(MANAGED_BY) ||
REFERENCE_USE_FLAG ||
RANK ||
STATUS = (
SELECT DEPARTMENT_CODE ||
DEPARTMENT ||
TITLE ||
JOB_TITLE ||
MAIL_STOP ||
DECISION_MAKER_FLAG ||
JOB_TITLE_CODE ||
TO_CHAR(MANAGED_BY) ||
REFERENCE_USE_FLAG ||
RANK ||
STATUS
FROM HZ_ORG_CONTACTS
WHERE ORG_CONTACT_ID = p_from_org_contact_id )
AND EXISTS (
SELECT 'same relationships'
FROM HZ_RELATIONSHIPS rel
WHERE rel.RELATIONSHIP_ID = org.PARTY_RELATIONSHIP_ID
AND rel.OBJECT_ID = p_to_party_id
AND SUBJECT_TABLE_NAME = 'HZ_PARTIES'
AND OBJECT_TABLE_NAME = 'HZ_PARTIES'
/* AND DIRECTIONAL_FLAG = 'F' */ /* Bug No : 2359461 */
AND TO_CHAR(SUBJECT_ID) ||
RELATIONSHIP_CODE = (
SELECT TO_CHAR(SUBJECT_ID) ||
RELATIONSHIP_CODE
FROM HZ_RELATIONSHIPS,HZ_CUST_ACCOUNTS ACCT
WHERE RELATIONSHIP_ID =
p_from_party_rel_id
AND OBJECT_ID = ACCT.PARTY_ID
AND ACCT.CUST_ACCOUNT_ID = p_from_account_id
AND SUBJECT_TABLE_NAME = 'HZ_PARTIES'
AND OBJECT_TABLE_NAME = 'HZ_PARTIES'
/* AND DIRECTIONAL_FLAG = 'F' */ /* Bug No : 2359461 */
) )
AND ORG_CONTACT_ID <> p_from_org_contact_id;
SELECT
MIN(CONTACT_POINT_ID)
FROM HZ_CONTACT_POINTS
WHERE OWNER_TABLE_ID = p_to_owner_table_id
AND
OWNER_TABLE_NAME ||
CONTACT_POINT_TYPE ||
STATUS ||
EDI_TRANSACTION_HANDLING ||
EDI_ID_NUMBER ||
EDI_PAYMENT_METHOD ||
EDI_PAYMENT_FORMAT ||
EDI_REMITTANCE_METHOD ||
EDI_REMITTANCE_INSTRUCTION ||
EMAIL_FORMAT ||
TO_CHAR(BEST_TIME_TO_CONTACT_START, 'DD-MON-YYYY') ||
TO_CHAR(BEST_TIME_TO_CONTACT_END, 'DD-MON-YYYY') ||
PHONE_CALLING_CALENDAR ||
DECLARED_BUSINESS_PHONE_FLAG ||
-- phone_referred_order has been obsoleted.
-- PHONE_PREFERRED_ORDER ||
TELEPHONE_TYPE ||
TIME_ZONE ||
PHONE_TOUCH_TONE_TYPE_FLAG ||
PHONE_AREA_CODE ||
PHONE_COUNTRY_CODE ||
PHONE_NUMBER ||
PHONE_EXTENSION ||
PHONE_LINE_TYPE ||
TELEX_NUMBER ||
CONTENT_SOURCE_TYPE ||
WEB_TYPE ||
CONTACT_POINT_PURPOSE
= (SELECT
OWNER_TABLE_NAME ||
CONTACT_POINT_TYPE ||
STATUS ||
EDI_TRANSACTION_HANDLING ||
EDI_ID_NUMBER ||
EDI_PAYMENT_METHOD ||
EDI_PAYMENT_FORMAT ||
EDI_REMITTANCE_METHOD ||
EDI_REMITTANCE_INSTRUCTION ||
EMAIL_FORMAT ||
TO_CHAR(BEST_TIME_TO_CONTACT_START, 'DD-MON-YYYY') ||
TO_CHAR(BEST_TIME_TO_CONTACT_END, 'DD-MON-YYYY') ||
PHONE_CALLING_CALENDAR ||
DECLARED_BUSINESS_PHONE_FLAG ||
-- PHONE_PREFERRED_ORDER ||
TELEPHONE_TYPE ||
TIME_ZONE ||
PHONE_TOUCH_TONE_TYPE_FLAG ||
PHONE_AREA_CODE ||
PHONE_COUNTRY_CODE ||
PHONE_NUMBER ||
PHONE_EXTENSION ||
PHONE_LINE_TYPE ||
TELEX_NUMBER ||
CONTENT_SOURCE_TYPE ||
WEB_TYPE ||
CONTACT_POINT_PURPOSE
FROM HZ_CONTACT_POINTS
WHERE CONTACT_POINT_ID = p_from_contact_point_id)
AND nvl(EMAIL_ADDRESS,'NOEMAIL') = (
SELECT nvl(EMAIL_ADDRESS,'NOEMAIL')
FROM HZ_CONTACT_POINTS
WHERE CONTACT_POINT_ID = p_from_contact_point_id)
AND nvl(URL, 'NOURL') = (
SELECT nvl(URL, 'NOURL')
FROM HZ_CONTACT_POINTS
WHERE CONTACT_POINT_ID = p_from_contact_point_id)
AND CONTACT_POINT_ID <> p_from_contact_point_id;
SELECT 'Y' INTO l_exist
FROM user_tables
WHERE table_name = g_table_name
AND ROWNUM = 1;
l_sql := 'DELETE ' || g_table_name;
| delete_rows
|
| DESCRIPTION physically delete the rows we marked in customer tables after
| we merging eact set.
|
| SCOPE - PUBLIC
|
| EXETERNAL PROCEDURES/FUNCTIONS ACCESSED
|
| ARGUMENTS : IN:
| OUT:
| IN/ OUT:
|
| RETURNS : NONE
|
| NOTES
|
| MODIFICATION HISTORY
| Jianying Huang 19-DEC-00 Bug 1535542: physically delete rows in
| customer tables after merging each set.
| Jianying Huang 29-DEC-00 Modified 'delete_rows' for performance issue.
| Jianying Huang 09-APR-01 Bug 1725662: rewrite sql statement on delete
| ra_cust_receipt_methods to use index.
|
+===========================================================================*/
PROCEDURE delete_rows(
req_id NUMBER,
set_num NUMBER
) IS
CURSOR cust_site_uses IS
SELECT site_use_id
FROM HZ_CUST_SITE_USES_ALL su, ra_customer_merges m --SSUptake
WHERE cust_acct_site_id = m.duplicate_address_id
AND m.org_id = su.org_id --SSUptake
AND m.process_flag = 'N'
AND m.request_id = req_id
AND m.set_number = set_num
AND m.delete_duplicate_flag = 'Y'
AND su.status = 'D'
FOR UPDATE NOWAIT;
SELECT cust_acct_site_id
FROM HZ_CUST_ACCT_SITES_ALL addr, ra_customer_merges m --SSUptake
WHERE cust_acct_site_id = m.duplicate_address_id
AND m.org_id = addr.org_id --SSUptake
AND m.process_flag = 'N'
AND m.request_id = req_id
AND m.set_number = set_num
AND m.delete_duplicate_flag = 'Y'
AND addr.status = 'D'
FOR UPDATE NOWAIT;
SELECT cust_account_id
FROM HZ_CUST_ACCOUNTS acct, ra_customer_merges m
WHERE 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'
AND acct.status = 'D'
FOR UPDATE NOWAIT;
SELECT rel.cust_account_id
FROM HZ_CUST_ACCT_RELATE_ALL rel, ra_customer_merges m --SSUptake
WHERE cust_account_id = m.duplicate_id
AND m.org_id = rel.org_id --SSUptake
AND m.process_flag = 'N'
AND m.request_id = req_id
AND m.set_number = set_num
AND m.delete_duplicate_flag = 'Y'
AND rel.status = 'D'
FOR UPDATE NOWAIT;
SELECT rel.related_cust_account_id
FROM HZ_CUST_ACCT_RELATE_ALL rel, ra_customer_merges m --SSUptake
WHERE related_cust_account_id = m.duplicate_id
AND m.org_id = rel.org_id --SSUptake
AND m.process_flag = 'N'
AND m.request_id = req_id
AND m.set_number = set_num
AND m.delete_duplicate_flag = 'Y'
AND rel.status = 'D'
FOR UPDATE NOWAIT;
SELECT CUST_RECEIPT_METHOD_ID
FROM RA_CUST_RECEIPT_METHODS yt, ra_customer_merges m
WHERE yt.customer_id = m.duplicate_id
AND yt.site_use_id = m.duplicate_site_id
AND m.process_flag = 'N'
AND m.request_id = req_id
AND m.set_number = set_num
AND m.delete_duplicate_flag = 'Y'
FOR UPDATE NOWAIT;
SELECT CUST_RECEIPT_METHOD_ID
FROM RA_CUST_RECEIPT_METHODS yt, ra_customer_merges m
WHERE yt.customer_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'
AND site_use_id IS NULL
AND NOT EXISTS (
SELECT 'accounts exist'
FROM hz_cust_accounts acct
WHERE acct.cust_account_id = yt.customer_id
AND acct.status <> 'D' )
FOR UPDATE NOWAIT;
SELECT party_usg_assignment_id
FROM hz_party_usg_assignments u
WHERE party_usage_code = 'CUSTOMER'
AND status_flag = 'D'
AND party_id in (SELECT DISTINCT c.party_id from hz_cust_accounts c, ra_customer_merges m
WHERE c.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');
arp_message.set_line( 'ARP_CMERGE_ARCUS.delete_rows()+' );
DELETE FROM hz_party_usg_assignments u
WHERE party_usage_code = 'CUSTOMER'
AND status_flag = 'D'
AND party_id in (SELECT DISTINCT c.party_id from hz_cust_accounts c, ra_customer_merges m
WHERE c.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');
arp_message.set_name( 'AR', 'AR_ROWS_DELETED' );
delete from hz_orig_sys_references where
owner_table_name = 'HZ_CUST_SITE_USES_ALL' and
owner_table_id in (
select site_use_id from hz_cust_site_uses_all su
where status = 'D'
and exists ( SELECT 'Y'
FROM ra_customer_merges m
WHERE su.cust_acct_site_id = m.duplicate_address_id
AND su.org_id = m.org_id
AND m.process_flag = 'N'
AND m.request_id = req_id
AND m.set_number = set_num
AND m.delete_duplicate_flag = 'Y'
)
);
DELETE FROM HZ_CUST_SITE_USES_ALL su
WHERE EXISTS (
SELECT 'Y'
FROM ra_customer_merges m
WHERE m.duplicate_address_id = su.cust_acct_site_id
AND m.org_id = su.org_id
AND m.process_flag = 'N'
AND m.request_id = req_id
AND m.set_number = set_num
AND m.delete_duplicate_flag = 'Y' )
AND status = 'D';
arp_message.set_name( 'AR', 'AR_ROWS_DELETED' );
delete from hz_orig_sys_references where
owner_table_name = 'HZ_CUST_ACCT_SITES_ALL' and
owner_table_id in (
select cust_acct_site_id from hz_cust_acct_sites_all sites --SSuptake
where status = 'D'
and EXISTS
( SELECT 'Y'
FROM ra_customer_merges m
WHERE m.duplicate_address_id = sites.cust_acct_site_id
AND m.org_id = sites.org_id
AND m.process_flag = 'N'
AND m.request_id = req_id
AND m.set_number = set_num
AND m.delete_duplicate_flag = 'Y' ));
DELETE FROM HZ_CUST_ACCT_SITES_ALL yt --SSUptake
WHERE EXISTS (
SELECT 'Y'
FROM ra_customer_merges m
WHERE m.duplicate_address_id = yt.cust_acct_site_id
AND m.org_id = yt.org_id
AND m.process_flag = 'N'
AND m.request_id = req_id
AND m.set_number = set_num
AND m.delete_duplicate_flag = 'Y' )
AND status = 'D';
arp_message.set_name( 'AR', 'AR_ROWS_DELETED' );
delete from hz_orig_sys_references where
owner_table_name = 'HZ_CUST_ACCOUNTS' and
owner_table_id in (
select cust_account_id from hz_cust_accounts where
status = 'D' and 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 m.delete_duplicate_flag = 'Y' ));
DELETE FROM HZ_CUST_ACCOUNTS
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 m.delete_duplicate_flag = 'Y' )
AND status = 'D';
arp_message.set_name( 'AR', 'AR_ROWS_DELETED' );
DELETE FROM HZ_CUST_ACCT_RELATE_ALL rel --SSUptake
WHERE EXISTS (
SELECT 'Y'
FROM ra_customer_merges m
WHERE m.duplicate_id = rel.cust_account_id
AND m.org_id = rel.org_id
AND m.process_flag = 'N'
AND m.request_id = req_id
AND m.set_number = set_num
AND m.delete_duplicate_flag = 'Y'
AND m.org_id = rel.org_id) --SSUptake
AND status = 'D';
arp_message.set_name( 'AR', 'AR_ROWS_DELETED' );
DELETE FROM HZ_CUST_ACCT_RELATE_ALL rel --SSUptake
WHERE EXISTS (
SELECT 'Y'
FROM ra_customer_merges m
WHERE m.duplicate_id = rel.related_cust_account_id
AND m.org_id = rel.org_id
AND m.process_flag = 'N'
AND m.request_id = req_id
AND m.set_number = set_num
AND m.delete_duplicate_flag = 'Y') --SSUptake
AND status = 'D';
arp_message.set_name( 'AR', 'AR_ROWS_DELETED' );
/************** account site level delete ************/
arp_message.set_name( 'AR', 'AR_DELETING_TABLE' );
DELETE FROM RA_CUST_RECEIPT_METHODS yt
WHERE (customer_id, site_use_id) in (
SELECT m.duplicate_id, m.duplicate_site_id
FROM ra_customer_merges m
WHERE m.process_flag = 'N'
AND m.request_id = req_id
AND m.set_number = set_num
AND m.delete_duplicate_flag = 'Y');
arp_message.set_name( 'AR', 'AR_ROWS_DELETED' );
/************** account level delete ************/
arp_message.set_name( 'AR', 'AR_DELETING_TABLE' );
DELETE FROM RA_CUST_RECEIPT_METHODS yt
WHERE customer_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 site_use_id IS NULL
AND NOT EXISTS (
SELECT 'accounts exist'
FROM hz_cust_accounts acct
WHERE acct.cust_account_id = yt.customer_id
AND status <> 'D' );
arp_message.set_name( 'AR', 'AR_ROWS_DELETED' );
arp_message.set_line( 'ARP_CMERGE_ARCUS.delete_rows()-' );
arp_message.set_error( 'ARP_CMERGE_ARCUS.delete_rows' );
END delete_rows;
| One of the merge rules is we should not update data in party level.
| So we should not update, for example, merge-to's party site with merge-from's
| party site if there exists a party site on the location for merge-to's party.
|
| After create account site for merge-to customer,
| we need to fill out the columns of ra_customer_merges for later query.
| -- customer_address_id <-- cust_acct_site_id
| -- customer_site_id <-- site_use_id
| -- customer_ref <-- orig_system_reference
| -- customer_primary_flag <-- primary_flag
| -- customer_location <-- location
| -- customer_site_code is created by merge form.
|
| MODIFICATION HISTORY
| Jianying Huang 27-NOV-00 After call create_* API,
| if return_status is not 'success', we need to
| populate an exception.
| Jianying Huang 27-NOV-00 Remove 'UPDATE hz_org_contacts ..'
| and 'UPDATE hz_contact_points..' part. We should
| not modify contacts data in party level. Instead,
| we should migrate those data during merge process.
| Jianying Huang 27-NOV-00 Added condition that we create profile amts
| if only if there is profile exist for the site use.
| Jianying Huang 07-DEC-OO Bug 1391134: Move the call of createSites
| in set-based merge from merge-form becuase we
| have to commit data for every set.
| Jianying Huang 07-DEC-00 Bug 1512300: Modify createSites to copy GL
| accounts.
| Jianying Huang 07-DEC-00 Bug 1472578: Modify createSites to
| 'create site/site use' in different scenario.
| Jianying Huang 07-DEC-00 Bug 1227593: Added column 'ADDRESSEE'
| when we create new party site.
| Jianying Huang 07-DEC-OO Should not copy tp_header_id. It is an unique
| column in hz_cust_acct_sites_all.
| Jianying Huang 12-DEC-00 Check 'x_return_status' after call
| 'create_cust_prof_amt'
| Jianying Huang 20-DEC-00 Bug 1535542: Since we will call customer merge
| before merging prEoducts, we should move 'createSites'
| in arplbmst.sql's merge_customers procedure. However,
| to avoid later calling order change, I rename it to '
| create_same_sites', move it to here(because it is related
| to customer tables), make it public and call it from
| merge report.
| Jianying Huang 28-DEC-00 When create new account site use, should not
| copy location. Instead, should enforce the API
| generate location.
| Jianying Huang 09-APR-01 When copy site use, should copy 'contact_id'
| because contact_id which references to cust_account_role_id
| will to move to merge-to customer after merge.
| Jianying Huang 22-JUL-01 Removed 'FOR UPDATE NOWAIT' for cursor
| 'sites_need_to_create'. This is a workaround of
| 'fetch out of sequence error' (see bug 1375214)
| on 8.1.6.2 onwards (the fix are done in 8.1.7.2).
| Jianying Huang 19-OCT-01 Bug 2062466: Modified procedure 'create_same_sites'
| to reset initial value of local varibles.
| Jianying Huang 26-OCT-01 Bug 2077604: Modified procedure 'create_same_sites'
| to add 'cust_account_profile_id = l_cust..' when
| select merge-from site use's profile amounts.
| Jyoti Pandey 06-NOV-01 Bug:2098728 Changing all API call outs to call
| Package hz_cust_account_merge_v2pvt
| Sisir 13-MAR-02 Bug:2241033;Written code for creating Payment
| Rajeshwari P 12-APR-02 Bug 2183072.Handled exception for Select from
| ar_system_parameters.
| Jyoti Pandey 20-MAY-02 Bug:2376975 create site use,profiles, amts etc. only
| is dup_site_use_id <> -99 . Form sets to -99 if
| there is no site use for a given site.
| P.Suresh 13-JUN-02 Bug No : 2403263. Added contact_id to
| hz_cust_site_uses.
| Rajeshwari P 10-OCT-02 Bug No:2529143.Added another parameter 'Credit_classification'
| during creation of Profile for a customer
| in create_same_sites procedure.
| Dhaval Mehta 28-JUL-03 Bug 2971149. Added the code back to copy
| party_site_uses when the create_same_site
| flag is checked. Added a call to
| hz_cust_account_merge_v2pvt.create_party_site_use
| in procedure create_same_sites.
| S.V.Sowjanya 02-DEC-04 Bug No: 3959776. Updated column customer_site_number
| in ra_customer_merges.
| S.V.Sowjanya 04-JAN-05 Bug No: 4018346. Assigned null values to l_customer_location,
| l_customer_site_id in the beginning of the loop for
| cursor sites_need_to_create and removed nvl condition
| for l_customer_location in the update statement of ra_customer_merges
| at the end of the loop for cursor sites_need_to_create.
| S V Sowjanya 10-AUG-05 Bug No:4492628. Moved code that copies party_site_uses
| to copy party_site_use after the creation of account_site
| Kalyana 12-Oct-07 Bug No: 6469732 Modified the procedure create_same_site so that if already an Active Customer
| Chakravarthy site use of Dunning or Statement exists for TO Party and FROM Party also have an Active use of
| Dunning or statement then it create the site use for TO Party in Status Inactive.
+===========================================================================*/
PROCEDURE create_same_sites(
req_id NUMBER,
set_num NUMBER,
status OUT NOCOPY NUMBER,
error_text OUT NOCOPY VARCHAR2
) IS
--The rows selected in the cursor are rows we need to create
--new sites.
CURSOR sites_need_to_create IS
SELECT duplicate_id, duplicate_address_id, duplicate_site_id,
duplicate_site_code, customer_id,org_id, delete_duplicate_flag --SSUptake
FROM ra_customer_merges
WHERE duplicate_id <> customer_id
AND process_flag = 'N'
AND request_id = req_id
AND set_number = set_num
AND customer_createsame = 'Y'
ORDER BY duplicate_site_code desc;
l_delete_flag VARCHAR2(1);
SELECT
currency_code,
trx_credit_limit,
overall_credit_limit,
min_dunning_amount,
min_dunning_invoice_amount,
max_interest_charge,
min_statement_amount,
auto_rec_min_receipt_amount,
interest_rate,
attribute_category,
attribute1,
attribute2,
attribute3,
attribute4,
attribute5,
attribute6,
attribute7,
attribute8,
attribute9,
attribute10,
attribute11,
attribute12,
attribute13,
attribute14,
attribute15,
min_fc_balance_amount,
min_fc_invoice_amount,
expiration_date,
--Bug:2098728 obsoleted wh_update_date,
jgzz_attribute_category,
jgzz_attribute1,
jgzz_attribute2,
jgzz_attribute3,
jgzz_attribute4,
jgzz_attribute5,
jgzz_attribute6,
jgzz_attribute7,
jgzz_attribute8,
jgzz_attribute9,
jgzz_attribute10,
jgzz_attribute11,
jgzz_attribute12,
jgzz_attribute13,
jgzz_attribute14,
jgzz_attribute15,
global_attribute1,
global_attribute2,
global_attribute3,
global_attribute4,
global_attribute5,
global_attribute6,
global_attribute7,
global_attribute8,
global_attribute9,
global_attribute10,
global_attribute11,
global_attribute12,
global_attribute13,
global_attribute14,
global_attribute15,
global_attribute16,
global_attribute17,
global_attribute18,
global_attribute19,
global_attribute20,
global_attribute_category,
--Bug 5040679 - AR new columns
exchange_rate_type,
min_fc_invoice_overdue_type,
min_fc_invoice_percent,
min_fc_balance_overdue_type,
min_fc_balance_percent,
interest_type,
interest_fixed_amount,
interest_schedule_id,
penalty_type,
penalty_rate,
min_interest_charge,
penalty_fixed_amount,
penalty_schedule_id
FROM hz_cust_profile_amts
WHERE cust_account_id = l_duplicate_id
AND site_use_id = l_duplicate_site_id
AND cust_account_profile_id = l_cust_account_profile_id;
select
CUST_RECEIPT_METHOD_ID,
CUSTOMER_ID,
RECEIPT_METHOD_ID,
PRIMARY_FLAG,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_LOGIN,
-- REQUEST_ID,
-- PROGRAM_APPLICATION_ID,
-- PROGRAM_ID,
-- PROGRAM_UPDATE_DATE,
SITE_USE_ID,
START_DATE,
END_DATE,
ATTRIBUTE_CATEGORY,
ATTRIBUTE1,
ATTRIBUTE2,
ATTRIBUTE3,
ATTRIBUTE4,
ATTRIBUTE5,
ATTRIBUTE6,
ATTRIBUTE7,
ATTRIBUTE8,
ATTRIBUTE9,
ATTRIBUTE10,
ATTRIBUTE11,
ATTRIBUTE12,
ATTRIBUTE13,
ATTRIBUTE14,
ATTRIBUTE15
from RA_CUST_RECEIPT_METHODS
WHERE CUSTOMER_ID = l_duplicate_id
AND site_use_id = l_duplicate_site_id;
l_duplicate_site_code, l_customer_id,m_org_id, l_delete_flag;
SELECT party_site_id INTO l_duplicate_party_site_id
FROM hz_cust_acct_sites_all --SSUptake
WHERE cust_acct_site_id = l_duplicate_address_id
and org_id = m_org_id; --SSUptake
SELECT location_id INTO l_location_id
FROM hz_party_sites
WHERE party_site_id = l_duplicate_party_site_id;
SELECT party_id INTO l_merge_to_party_id
FROM hz_cust_accounts
WHERE cust_account_id = l_customer_id;
SELECT 'Y' INTO l_exist
FROM hz_cust_acct_sites_all --SSUptake
WHERE cust_account_id = l_customer_id
AND org_id = m_org_id --SSUptake
AND party_site_id IN (
SELECT party_site_id
FROM hz_party_sites
WHERE location_id = l_location_id
AND party_id = l_merge_to_party_id )
AND ROWNUM = 1;
SELECT party_site_id INTO l_party_site_id
FROM hz_party_sites
WHERE party_id = l_merge_to_party_id
AND location_id = l_location_id
AND ROWNUM = 1;
SELECT MIN(party_site_id) INTO l_party_site_id
FROM hz_party_sites ps
WHERE party_id = l_merge_to_party_id
AND location_id = l_location_id
AND EXISTS ( --'same site usage'
SELECT party_site_use_id
FROM hz_party_site_uses su
WHERE su.party_site_id = ps.party_site_id
AND site_use_type = l_duplicate_site_code );
SELECT MIN(party_site_id) INTO l_party_site_id
FROM hz_party_sites
WHERE party_id = l_merge_to_party_id
AND location_id = l_location_id;
select party_site_use_id into l_duplicate_party_site_use_id
from hz_party_site_uses
where site_use_type = l_duplicate_site_code
and party_site_id = l_duplicate_party_site_id
and ROWNUM=1;
SELECT --Bug:2098728 obsoleted wh_update_date,
attribute_category,
attribute1,
attribute2,
attribute3,
attribute4,
attribute5,
attribute6,
attribute7,
attribute8,
attribute9,
attribute10,
attribute11,
attribute12,
attribute13,
attribute14,
attribute15,
attribute16,
attribute17,
attribute18,
attribute19,
attribute20,
global_attribute_category,
global_attribute1,
global_attribute2,
global_attribute3,
global_attribute4,
global_attribute5,
global_attribute6,
global_attribute7,
global_attribute8,
global_attribute9,
global_attribute10,
global_attribute11,
global_attribute12,
global_attribute13,
global_attribute14,
global_attribute15,
global_attribute16,
global_attribute17,
global_attribute18,
global_attribute19,
global_attribute20,
--Cannot copy, org_system_reference has unique index.
--If no input, orig_system_reference is defaulted to cust_acct_site_id.
orig_system_reference,
status,
customer_category_code,
language,
key_account_flag,
--Should not copy tp related columns. They are unique columns in hz_cust_acct_sites_all.
-- tp_header_id,
-- ece_tp_location_code,
--Bug:2098728 obsoleted service_territory_id,
primary_specialist_id,
secondary_specialist_id,
territory_id,
territory,
org_id ---To pass this org_id to create_cust_acct_site
--Should not copy. The customer name should be merge-to's.
-- translated_customer_name
INTO
--Bug:2098728 obsoleted cust_site_rec.wh_update_date,
cust_site_rec.attribute_category,
cust_site_rec.attribute1,
cust_site_rec.attribute2,
cust_site_rec.attribute3,
cust_site_rec.attribute4,
cust_site_rec.attribute5,
cust_site_rec.attribute6,
cust_site_rec.attribute7,
cust_site_rec.attribute8,
cust_site_rec.attribute9,
cust_site_rec.attribute10,
cust_site_rec.attribute11,
cust_site_rec.attribute12,
cust_site_rec.attribute13,
cust_site_rec.attribute14,
cust_site_rec.attribute15,
cust_site_rec.attribute16,
cust_site_rec.attribute17,
cust_site_rec.attribute18,
cust_site_rec.attribute19,
cust_site_rec.attribute20,
cust_site_rec.global_attribute_category,
cust_site_rec.global_attribute1,
cust_site_rec.global_attribute2,
cust_site_rec.global_attribute3,
cust_site_rec.global_attribute4,
cust_site_rec.global_attribute5,
cust_site_rec.global_attribute6,
cust_site_rec.global_attribute7,
cust_site_rec.global_attribute8,
cust_site_rec.global_attribute9,
cust_site_rec.global_attribute10,
cust_site_rec.global_attribute11,
cust_site_rec.global_attribute12,
cust_site_rec.global_attribute13,
cust_site_rec.global_attribute14,
cust_site_rec.global_attribute15,
cust_site_rec.global_attribute16,
cust_site_rec.global_attribute17,
cust_site_rec.global_attribute18,
cust_site_rec.global_attribute19,
cust_site_rec.global_attribute20,
cust_site_rec.orig_system_reference,
cust_site_rec.status,
cust_site_rec.customer_category_code,
cust_site_rec.language,
cust_site_rec.key_account_flag,
-- cust_site_rec.tp_header_id,
-- cust_site_rec.ece_tp_location_code,
--Bug:2098728 obsoleted cust_site_rec.service_territory_id,
cust_site_rec.primary_specialist_id,
cust_site_rec.secondary_specialist_id,
cust_site_rec.territory_id,
cust_site_rec.territory,
l_org_id
-- cust_site_rec.translated_customer_name
FROM hz_cust_acct_sites_all
WHERE cust_account_id = l_duplicate_id
AND cust_acct_site_id = l_duplicate_address_id;
IF l_delete_flag = 'Y' THEN
UPDATE hz_cust_acct_sites
SET orig_system_reference = substr(l_duplicate_address_id||sysdate,1,240)
WHERE cust_account_id = l_duplicate_id
AND cust_acct_site_id = l_duplicate_address_id;
SELECT MIN(cust_acct_site_id) INTO l_customer_address_id
FROM hz_cust_acct_sites_all cas --SSUptake
WHERE cust_account_id = l_customer_id
AND org_id = m_org_id --SSUptake
AND party_site_id IN (
SELECT party_site_id
FROM hz_party_sites
WHERE location_id = l_location_id
AND party_id = l_merge_to_party_id )
AND EXISTS ( --'same site usage'
SELECT site_use_id
FROM HZ_CUST_SITE_USES_ALL csu --SSUptake
WHERE cas.cust_acct_site_id = csu.cust_acct_site_id
AND site_use_code = l_duplicate_site_code
AND csu.org_id = cas.org_id ); --SSUptake
SELECT MIN(cust_acct_site_id) INTO l_customer_address_id
FROM hz_cust_acct_sites_all cas --SSUptake
WHERE cust_account_id = l_customer_id
and org_id = m_org_id --SSUptake
AND party_site_id IN (
SELECT party_site_id
FROM hz_party_sites
WHERE location_id = l_location_id
AND party_id = l_merge_to_party_id );
SELECT MIN(site_use_id) INTO l_customer_site_id
FROM HZ_CUST_SITE_USES_ALL --SSUptake
WHERE cust_acct_site_id = l_customer_address_id
AND org_id = m_org_id --SSUptake
AND site_use_code = l_duplicate_site_code;
SELECT MIN(party_site_id) INTO l_party_site_id
FROM hz_party_sites ps
WHERE party_id = l_merge_to_party_id
AND location_id = l_location_id
AND EXISTS ( --'same site usage'
SELECT party_site_use_id
FROM hz_party_site_uses su
WHERE su.party_site_id = ps.party_site_id
AND site_use_type = l_duplicate_site_code );
SELECT MIN(party_site_id) INTO l_party_site_id
FROM hz_party_sites
WHERE party_id = l_merge_to_party_id
AND location_id = l_location_id;
select party_site_use_id into l_duplicate_party_site_use_id
from hz_party_site_uses
where site_use_type = l_duplicate_site_code
and party_site_id = l_duplicate_party_site_id
and ROWNUM=1;
SELECT
cust_account_profile_id,
status,
collector_id,
credit_analyst_id,
credit_checking,
next_credit_review_date,
tolerance,
discount_terms,
dunning_letters,
interest_charges,
send_statements,
credit_balance_statements,
credit_hold,
profile_class_id,
credit_rating,
risk_code,
standard_terms,
override_terms,
dunning_letter_set_id,
interest_period_days,
payment_grace_days,
discount_grace_days,
statement_cycle_id,
account_status,
percent_collectable,
autocash_hierarchy_id,
attribute_category,
attribute1,
attribute2,
attribute3,
attribute4,
attribute5,
attribute6,
attribute7,
attribute8,
attribute9,
attribute10,
attribute11,
attribute12,
attribute13,
attribute14,
attribute15,
--Bug:2098728 obsoleted wh_update_date,
auto_rec_incl_disputed_flag,
tax_printing_option,
charge_on_finance_charge_flag,
grouping_rule_id,
clearing_days,
jgzz_attribute_category,
jgzz_attribute1,
jgzz_attribute2,
jgzz_attribute3,
jgzz_attribute4,
jgzz_attribute5,
jgzz_attribute6,
jgzz_attribute7,
jgzz_attribute8,
jgzz_attribute9,
jgzz_attribute10,
jgzz_attribute11,
jgzz_attribute12,
jgzz_attribute13,
jgzz_attribute14,
jgzz_attribute15,
global_attribute1,
global_attribute2,
global_attribute3,
global_attribute4,
global_attribute5,
global_attribute6,
global_attribute7,
global_attribute8,
global_attribute9,
global_attribute10,
global_attribute11,
global_attribute12,
global_attribute13,
global_attribute14,
global_attribute15,
global_attribute16,
global_attribute17,
global_attribute18,
global_attribute19,
global_attribute20,
global_attribute_category,
cons_inv_flag,
cons_inv_type,
autocash_hierarchy_id_for_adr,
lockbox_matching_option,
review_cycle,
last_credit_review_date,
party_id,
credit_classification,
--Bug 5040679 - AR new columns
cons_bill_level,
late_charge_calculation_trx,
credit_items_flag,
disputed_transactions_flag,
late_charge_type,
late_charge_term_id,
interest_calculation_period,
hold_charged_invoices_flag,
message_text_id,
multiple_interest_rates_flag,
charge_begin_date,
automatch_set_id --bug 8477178 . AR new column
INTO
l_cust_account_profile_id,
cust_prof_rec.status,
cust_prof_rec.collector_id,
cust_prof_rec.credit_analyst_id,
cust_prof_rec.credit_checking,
cust_prof_rec.next_credit_review_date,
cust_prof_rec.tolerance,
cust_prof_rec.discount_terms,
cust_prof_rec.dunning_letters,
cust_prof_rec.interest_charges,
cust_prof_rec.send_statements,
cust_prof_rec.credit_balance_statements,
cust_prof_rec.credit_hold,
cust_prof_rec.profile_class_id,
cust_prof_rec.credit_rating,
cust_prof_rec.risk_code,
cust_prof_rec.standard_terms,
cust_prof_rec.override_terms,
cust_prof_rec.dunning_letter_set_id,
cust_prof_rec.interest_period_days,
cust_prof_rec.payment_grace_days,
cust_prof_rec.discount_grace_days,
cust_prof_rec.statement_cycle_id,
cust_prof_rec.account_status,
cust_prof_rec.percent_collectable,
cust_prof_rec.autocash_hierarchy_id,
cust_prof_rec.attribute_category,
cust_prof_rec.attribute1,
cust_prof_rec.attribute2,
cust_prof_rec.attribute3,
cust_prof_rec.attribute4,
cust_prof_rec.attribute5,
cust_prof_rec.attribute6,
cust_prof_rec.attribute7,
cust_prof_rec.attribute8,
cust_prof_rec.attribute9,
cust_prof_rec.attribute10,
cust_prof_rec.attribute11,
cust_prof_rec.attribute12,
cust_prof_rec.attribute13,
cust_prof_rec.attribute14,
cust_prof_rec.attribute15,
--Bug:2098728 obsoleted cust_prof_rec.wh_update_date,
cust_prof_rec.auto_rec_incl_disputed_flag,
cust_prof_rec.tax_printing_option,
cust_prof_rec.charge_on_finance_charge_flag,
cust_prof_rec.grouping_rule_id,
cust_prof_rec.clearing_days,
cust_prof_rec.jgzz_attribute_category,
cust_prof_rec.jgzz_attribute1,
cust_prof_rec.jgzz_attribute2,
cust_prof_rec.jgzz_attribute3,
cust_prof_rec.jgzz_attribute4,
cust_prof_rec.jgzz_attribute5,
cust_prof_rec.jgzz_attribute6,
cust_prof_rec.jgzz_attribute7,
cust_prof_rec.jgzz_attribute8,
cust_prof_rec.jgzz_attribute9,
cust_prof_rec.jgzz_attribute10,
cust_prof_rec.jgzz_attribute11,
cust_prof_rec.jgzz_attribute12,
cust_prof_rec.jgzz_attribute13,
cust_prof_rec.jgzz_attribute14,
cust_prof_rec.jgzz_attribute15,
cust_prof_rec.global_attribute1,
cust_prof_rec.global_attribute2,
cust_prof_rec.global_attribute3,
cust_prof_rec.global_attribute4,
cust_prof_rec.global_attribute5,
cust_prof_rec.global_attribute6,
cust_prof_rec.global_attribute7,
cust_prof_rec.global_attribute8,
cust_prof_rec.global_attribute9,
cust_prof_rec.global_attribute10,
cust_prof_rec.global_attribute11,
cust_prof_rec.global_attribute12,
cust_prof_rec.global_attribute13,
cust_prof_rec.global_attribute14,
cust_prof_rec.global_attribute15,
cust_prof_rec.global_attribute16,
cust_prof_rec.global_attribute17,
cust_prof_rec.global_attribute18,
cust_prof_rec.global_attribute19,
cust_prof_rec.global_attribute20,
cust_prof_rec.global_attribute_category,
cust_prof_rec.cons_inv_flag,
cust_prof_rec.cons_inv_type,
cust_prof_rec.autocash_hierarchy_id_for_adr,
cust_prof_rec.lockbox_matching_option,
cust_prof_rec.review_cycle,
cust_prof_rec.last_credit_review_date,
cust_prof_rec.party_id,
cust_prof_rec.credit_classification,
--Bug 5040679 - AR new columns
cust_prof_rec.cons_bill_level,
cust_prof_rec.late_charge_calculation_trx,
cust_prof_rec.credit_items_flag,
cust_prof_rec.disputed_transactions_flag,
cust_prof_rec.late_charge_type,
cust_prof_rec.late_charge_term_id,
cust_prof_rec.interest_calculation_period,
cust_prof_rec.hold_charged_invoices_flag,
cust_prof_rec.message_text_id,
cust_prof_rec.multiple_interest_rates_flag,
cust_prof_rec.charge_begin_date,
cust_prof_rec.automatch_set_id --8477178
FROM hz_customer_profiles
WHERE cust_account_id = l_duplicate_id
AND site_use_id = l_duplicate_site_id
AND ROWNUM = 1; -- in case of data problem: one site use has 2 profiles.
SELECT site_use_code,
--We should set primary in customer merge context if the two customer doesnt have a same primary site usage in that org_id
primary_flag,--Bug No.5211233
--Set the merge-to site uses status to 'Active'
--
--Bug 2071810: keep the old status
status,
--Bug fix 2588321 Changed the logic for copying value into location field
--depending upon certain conditions.
--location is unique per customer+business purpose combination. We should
--not copy it in customer merge context
-- location,
contact_id,
--We should not set bill_to_site_use_id in customer merge context
bill_to_site_use_id,
orig_system_reference,
sic_code,
payment_term_id,
gsa_indicator,
ship_partial,
ship_via,
fob_point,
order_type_id,
price_list_id,
freight_term,
warehouse_id,
territory_id,
attribute_category,
attribute1,
attribute2,
attribute3,
attribute4,
attribute5,
attribute6,
attribute7,
attribute8,
attribute9,
attribute10,
attribute11,
attribute12,
attribute13,
attribute14,
attribute15,
attribute16,
attribute17,
attribute18,
attribute19,
attribute20,
attribute21,
attribute22,
attribute23,
attribute24,
attribute25,
global_attribute_category,
global_attribute1,
global_attribute2,
global_attribute3,
global_attribute4,
global_attribute5,
global_attribute6,
global_attribute7,
global_attribute8,
global_attribute9,
global_attribute10,
global_attribute11,
global_attribute12,
global_attribute13,
global_attribute14,
global_attribute15,
global_attribute16,
global_attribute17,
global_attribute18,
global_attribute19,
global_attribute20,
tax_reference,
sort_priority,
tax_code,
--Bug:2098728 obsoleted last_accrue_charge_date,
--Bug:2098728 obsoleted second_last_accrue_charge_date,
--Bug:2098728 obsoleted last_unaccrue_charge_date,
--Bug:2098728 obsoleted second_last_unaccrue_chrg_date,
demand_class_code,
tax_header_level_flag,
tax_rounding_rule,
--Bug:2098728 obsoleted wh_update_date,
primary_salesrep_id,
finchrg_receivables_trx_id,
dates_negative_tolerance,
dates_positive_tolerance,
date_type_preference,
over_shipment_tolerance,
under_shipment_tolerance,
item_cross_ref_pref,
ship_sets_include_lines_flag,
arrivalsets_include_lines_flag,
sched_date_push_flag,
invoice_quantity_rule,
over_return_tolerance,
under_return_tolerance,
pricing_event,
--Bug 1512300: Modify create_same_sites to copy GL accounts.
gl_id_rec,
gl_id_rev,
gl_id_tax,
gl_id_freight,
gl_id_clearing,
gl_id_unbilled,
gl_id_unearned,
gl_id_unpaid_rec,
gl_id_remittance,
gl_id_factor,
tax_classification,
org_id --To pass org_id while creating cust_site_use
INTO
cust_site_use_rec.site_use_code,
cust_site_use_rec.primary_flag,--Bug No. 5211233
cust_site_use_rec.status,
-- cust_site_use_rec.location,
--Bug:2098728 obsoleted cust_site_use_rec.contact_id,
--Bug:2403263 Added cust_site_use_rec.contact_id.
cust_site_use_rec.contact_id,
-- cust_site_use_rec.bill_to_site_use_id,
cust_site_use_rec.bill_to_site_use_id,
cust_site_use_rec.orig_system_reference,
cust_site_use_rec.sic_code,
cust_site_use_rec.payment_term_id,
cust_site_use_rec.gsa_indicator,
cust_site_use_rec.ship_partial,
cust_site_use_rec.ship_via,
cust_site_use_rec.fob_point,
cust_site_use_rec.order_type_id,
cust_site_use_rec.price_list_id,
cust_site_use_rec.freight_term,
cust_site_use_rec.warehouse_id,
cust_site_use_rec.territory_id,
cust_site_use_rec.attribute_category,
cust_site_use_rec.attribute1,
cust_site_use_rec.attribute2,
cust_site_use_rec.attribute3,
cust_site_use_rec.attribute4,
cust_site_use_rec.attribute5,
cust_site_use_rec.attribute6,
cust_site_use_rec.attribute7,
cust_site_use_rec.attribute8,
cust_site_use_rec.attribute9,
cust_site_use_rec.attribute10,
cust_site_use_rec.attribute11,
cust_site_use_rec.attribute12,
cust_site_use_rec.attribute13,
cust_site_use_rec.attribute14,
cust_site_use_rec.attribute15,
cust_site_use_rec.attribute16,
cust_site_use_rec.attribute17,
cust_site_use_rec.attribute18,
cust_site_use_rec.attribute19,
cust_site_use_rec.attribute20,
cust_site_use_rec.attribute21,
cust_site_use_rec.attribute22,
cust_site_use_rec.attribute23,
cust_site_use_rec.attribute24,
cust_site_use_rec.attribute25,
cust_site_use_rec.global_attribute_category,
cust_site_use_rec.global_attribute1,
cust_site_use_rec.global_attribute2,
cust_site_use_rec.global_attribute3,
cust_site_use_rec.global_attribute4,
cust_site_use_rec.global_attribute5,
cust_site_use_rec.global_attribute6,
cust_site_use_rec.global_attribute7,
cust_site_use_rec.global_attribute8,
cust_site_use_rec.global_attribute9,
cust_site_use_rec.global_attribute10,
cust_site_use_rec.global_attribute11,
cust_site_use_rec.global_attribute12,
cust_site_use_rec.global_attribute13,
cust_site_use_rec.global_attribute14,
cust_site_use_rec.global_attribute15,
cust_site_use_rec.global_attribute16,
cust_site_use_rec.global_attribute17,
cust_site_use_rec.global_attribute18,
cust_site_use_rec.global_attribute19,
cust_site_use_rec.global_attribute20,
cust_site_use_rec.tax_reference,
cust_site_use_rec.sort_priority,
cust_site_use_rec.tax_code,
--Bug:2098728 obsoleted cust_site_use_rec.last_accrue_charge_date,
--Bug:2098728 obsoleted cust_site_use_rec.second_last_accrue_charge_date,
--Bug:2098728 obsoleted cust_site_use_rec.last_unaccrue_charge_date,
--Bug:2098728 obsoleted cust_site_use_rec.second_last_unaccrue_chrg_date,
cust_site_use_rec.demand_class_code,
cust_site_use_rec.tax_header_level_flag,
cust_site_use_rec.tax_rounding_rule,
--Bug:2098728 obsoleted cust_site_use_rec.wh_update_date,
cust_site_use_rec.primary_salesrep_id,
cust_site_use_rec.finchrg_receivables_trx_id,
cust_site_use_rec.dates_negative_tolerance,
cust_site_use_rec.dates_positive_tolerance,
cust_site_use_rec.date_type_preference,
cust_site_use_rec.over_shipment_tolerance,
cust_site_use_rec.under_shipment_tolerance,
cust_site_use_rec.item_cross_ref_pref,
cust_site_use_rec.ship_sets_include_lines_flag,
cust_site_use_rec.arrivalsets_include_lines_flag,
cust_site_use_rec.sched_date_push_flag,
cust_site_use_rec.invoice_quantity_rule,
cust_site_use_rec.over_return_tolerance,
cust_site_use_rec.under_return_tolerance,
cust_site_use_rec.pricing_event,
--Bug 1512300: Modify create_same_sites to copy GL accounts.
cust_site_use_rec.gl_id_rec,
cust_site_use_rec.gl_id_rev,
cust_site_use_rec.gl_id_tax,
cust_site_use_rec.gl_id_freight,
cust_site_use_rec.gl_id_clearing,
cust_site_use_rec.gl_id_unbilled,
cust_site_use_rec.gl_id_unearned,
cust_site_use_rec.gl_id_unpaid_rec,
cust_site_use_rec.gl_id_remittance,
cust_site_use_rec.gl_id_factor,
cust_site_use_rec.tax_classification,
site_use_org_id
FROM HZ_CUST_SITE_USES_ALL --SSUptake
WHERE site_use_id = l_duplicate_site_id
AND org_id = m_org_id; --SSUptake
Select NULL INTO cust_site_use_rec.primary_flag
from hz_cust_site_uses_all
where CUST_ACCT_SITE_ID in (select CUST_ACCT_SITE_ID from hz_cust_acct_sites_all
Where cust_account_id = l_customer_id
AND org_id = site_use_org_id)
AND SITE_USE_CODE = cust_site_use_rec.site_use_code
AND PRIMARY_FLAG = 'Y'
AND nvl(status,'A') = 'A'
AND org_id = site_use_org_id;
select 1
into l_count
from HZ_CUST_SITE_USES_ALL
where site_use_id = cust_site_use_rec.bill_to_site_use_id
and site_use_code = 'BILL_TO';
SELECT customer_location into l_customer_location
FROM ra_customer_merges
WHERE customer_id = l_customer_id
AND duplicate_site_id = l_duplicate_site_id
--Bug Fix 2929527
AND ROWNUM=1;
SELECT auto_site_numbering INTO l_gen_loc
FROM ar_system_parameters;
UPDATE ar_system_parameters
SET auto_site_numbering = 'Y';
SELECT 'Y' INTO l_dun_exists
FROM hz_cust_acct_sites_all as1, hz_cust_site_uses_all asu
WHERE as1.cust_account_id = l_customer_id
AND asu.cust_acct_site_id = as1.cust_acct_site_id
AND as1.org_id = m_org_id
AND asu.site_use_code = l_duplicate_site_code
AND asu.org_id = m_org_id
AND asu.status = 'A'
AND ROWNUM = 1 ;
UPDATE ar_system_parameters
SET auto_site_numbering = 'N';
SELECT cust_account_profile_id INTO l_to_cust_account_profile_id
FROM hz_customer_profiles
WHERE cust_account_id = l_customer_id
AND site_use_id = l_customer_site_id;
arp_CRM_PKG.Insert_Row(X_Rowid => l_row_id ,
X_Cust_Receipt_Method_Id => l_Cust_Receipt_Method_Id,
X_Created_By => hz_utility_v2pub.user_id,--arp_standard.profile.user_id ,
X_Creation_Date => sysdate,
X_Customer_Id => l_customer_id,
X_Last_Updated_By =>hz_utility_v2pub.user_id,-- arp_standard.profile.user_id,
X_Last_Update_Date => sysdate,
X_Primary_Flag => merge_from_pay_method_row.Primary_Flag,
X_Receipt_Method_Id => merge_from_pay_method_row.Receipt_Method_Id,
X_Start_Date => merge_from_pay_method_row.Start_Date,
X_End_Date => merge_from_pay_method_row.End_Date,
X_Last_Update_Login =>hz_utility_v2pub.last_update_login,-- arp_standard.profile.last_update_login,
X_Site_Use_Id => l_customer_site_id,
X_Attribute_Category => merge_from_pay_method_row.Attribute_Category,
X_Attribute1 => merge_from_pay_method_row.Attribute1,
X_Attribute2 => merge_from_pay_method_row.Attribute2,
X_Attribute3 => merge_from_pay_method_row.Attribute3,
X_Attribute4 => merge_from_pay_method_row.Attribute4,
X_Attribute5 => merge_from_pay_method_row.Attribute5,
X_Attribute6 => merge_from_pay_method_row.Attribute6,
X_Attribute7 => merge_from_pay_method_row.Attribute7,
X_Attribute8 => merge_from_pay_method_row.Attribute8,
X_Attribute9 => merge_from_pay_method_row.Attribute9,
X_Attribute10 => merge_from_pay_method_row.Attribute10,
X_Attribute11 => merge_from_pay_method_row.Attribute11,
X_Attribute12 => merge_from_pay_method_row.Attribute12,
X_Attribute13 => merge_from_pay_method_row.Attribute13,
X_Attribute14 => merge_from_pay_method_row.Attribute14,
X_Attribute15 => merge_from_pay_method_row.Attribute15
);
--The above table handler does not insert all the columns of
--the table.So the following update statement is created to
--update rest of the fields.
if l_row_id is not null then
update RA_CUST_RECEIPT_METHODS
set
last_update_date = sysdate,
last_updated_by =hz_utility_v2pub.user_id,--arp_standard.profile.user_id,
last_update_login =hz_utility_v2pub.last_update_login,-- arp_standard.profile.last_update_login,
request_id = req_id,
program_application_id =hz_utility_v2pub.program_application_id,-- arp_standard.profile.program_application_id,
program_id =hz_utility_v2pub.program_id,-- arp_standard.profile.program_id,
program_update_date = sysdate
where rowid = l_row_id;
SELECT orig_system_reference
INTO l_customer_ref
FROM hz_cust_acct_sites_all
WHERE cust_acct_site_id = l_customer_address_id;
SELECT primary_flag, location
INTO l_customer_primary_flag, l_customer_location
FROM HZ_CUST_SITE_USES_ALL
WHERE site_use_id = l_customer_site_id;
SELECT party_site_number INTO l_party_site_number
FROM hz_party_sites
WHERE party_site_id = ( SELECT party_site_id
FROM hz_cust_acct_sites_all
WHERE cust_acct_site_id = l_customer_address_id);
UPDATE ra_customer_merges
SET customer_address_id = l_customer_address_id,
customer_ref = l_customer_ref,
customer_primary_flag = nvl(l_customer_primary_flag,'N'),
customer_site_id = nvl(l_customer_site_id,-99),
customer_location = l_customer_location, --bug 4018346 removed nvl condition
customer_site_number = nvl(l_party_site_number,-99), ---bug 3959776 updated customer_site_number
last_update_date = sysdate,
last_updated_by = hz_utility_v2pub.user_id,--arp_standard.profile.user_id,
last_update_login =hz_utility_v2pub.last_update_login,-- arp_standard.profile.last_update_login,
program_application_id =hz_utility_v2pub.program_application_id,-- arp_standard.profile.program_application_id,
program_id =hz_utility_v2pub.program_id,-- arp_standard.profile.program_id,
program_update_date = sysdate
WHERE duplicate_id = l_duplicate_id
AND duplicate_site_id = l_duplicate_site_id
AND duplicate_address_id = l_duplicate_address_id -- bug 7851438
AND customer_id = l_customer_id
AND process_flag = 'N'
AND request_id = req_id
AND set_number = set_num
AND customer_createsame = 'Y';
INSERT INTO HZ_CUST_ACCOUNTS_M(
customer_merge_header_id,
cust_account_id,
party_id ,
last_update_date ,
account_number ,
last_updated_by ,
creation_date ,
created_by ,
last_update_login,
request_id ,
program_application_id,
program_id ,
program_update_date ,
attribute_category ,
attribute1 ,
attribute2 ,
attribute3 ,
attribute4 ,
attribute5 ,
attribute6 ,
attribute7,
attribute8,
attribute9 ,
attribute10,
attribute11,
attribute12,
attribute13,
attribute14,
attribute15,
attribute16,
attribute17,
attribute18,
attribute19,
attribute20,
global_attribute_category,
global_attribute1,
global_attribute2,
global_attribute3,
global_attribute4,
global_attribute5,
global_attribute6,
global_attribute7,
global_attribute8,
global_attribute9,
global_attribute10,
global_attribute11,
global_attribute12,
global_attribute13,
global_attribute14,
global_attribute15,
global_attribute16,
global_attribute17,
global_attribute18,
global_attribute19,
global_attribute20,
orig_system_reference,
status,
customer_type,
customer_class_code,
primary_salesrep_id,
sales_channel_code ,
order_type_id ,
price_list_id ,
tax_code ,
fob_point ,
freight_term ,
ship_partial ,
ship_via ,
warehouse_id ,
tax_header_level_flag,
tax_rounding_rule ,
coterminate_day_month,
primary_specialist_id,
secondary_specialist_id ,
account_liable_flag ,
current_balance ,
account_established_date,
account_termination_date ,
account_activation_date ,
department ,
held_bill_expiration_date,
hold_bill_flag ,
realtime_rate_flag ,
acct_life_cycle_status,
account_name ,
deposit_refund_method ,
dormant_account_flag ,
npa_number ,
suspension_date ,
Source_code ,
competitor_type ,
comments ,
dates_negative_tolerance,
dates_positive_tolerance,
date_type_preference ,
over_shipment_tolerance ,
under_shipment_tolerance,
over_return_tolerance ,
under_return_tolerance ,
item_cross_ref_pref ,
ship_sets_include_lines_flag ,
arrivalsets_include_lines_flag,
sched_date_push_flag ,
invoice_quantity_rule ,
pricing_event ,
status_update_date ,
autopay_flag ,
notify_flag ,
last_batch_id ,
org_id ,
object_version_number ,
created_by_module ,
application_id ,
selling_party_id ,
federal_entity_type ,
trading_partner_agency_id,
duns_extension ,
advance_payment_indicator,
merge_request_id
)
SELECT
customer_merge_header_id,
cust_account_id,
party_id ,
c.last_update_date ,
c.account_number ,
c.last_updated_by ,
c.creation_date ,
c.created_by ,
c.last_update_login,
c.request_id ,
c.program_application_id,
c.program_id ,
c.program_update_date ,
c.attribute_category ,
c.attribute1 ,
c.attribute2 ,
c.attribute3 ,
c.attribute4 ,
c.attribute5 ,
c.attribute6 ,
c.attribute7,
c.attribute8,
c.attribute9 ,
c.attribute10,
c.attribute11,
c.attribute12,
c.attribute13,
c.attribute14,
c.attribute15,
c.attribute16,
c.attribute17,
c.attribute18,
c.attribute19,
c.attribute20,
c.global_attribute_category,
c.global_attribute1,
c.global_attribute2,
c.global_attribute3,
c.global_attribute4,
c.global_attribute5,
c.global_attribute6,
c.global_attribute7,
c.global_attribute8,
c.global_attribute9,
c.global_attribute10,
c.global_attribute11,
c.global_attribute12,
c.global_attribute13,
c.global_attribute14,
c.global_attribute15,
c.global_attribute16,
c.global_attribute17,
c.global_attribute18,
c.global_attribute19,
c.global_attribute20,
c.orig_system_reference,
c.status,
c.customer_type,
c.customer_class_code,
c.primary_salesrep_id,
c.sales_channel_code ,
c.order_type_id ,
c.price_list_id ,
c.tax_code ,
c.fob_point ,
c.freight_term ,
c.ship_partial ,
c.ship_via ,
c.warehouse_id ,
c.tax_header_level_flag,
c.tax_rounding_rule ,
c.coterminate_day_month,
c.primary_specialist_id,
c.secondary_specialist_id ,
c.account_liable_flag ,
c.current_balance ,
c.account_established_date,
c.account_termination_date ,
c.account_activation_date ,
c.department ,
c.held_bill_expiration_date,
c.hold_bill_flag ,
c.realtime_rate_flag ,
c.acct_life_cycle_status,
c.account_name ,
c.deposit_refund_method ,
c.dormant_account_flag ,
c.npa_number ,
c.suspension_date ,
c.source_code ,
c.competitor_type ,
c.comments ,
c.dates_negative_tolerance,
c.dates_positive_tolerance,
c.date_type_preference ,
c.over_shipment_tolerance ,
c.under_shipment_tolerance,
c.over_return_tolerance ,
c.under_return_tolerance ,
c.item_cross_ref_pref ,
c.ship_sets_include_lines_flag ,
c.arrivalsets_include_lines_flag,
c.sched_date_push_flag ,
c.invoice_quantity_rule ,
c.pricing_event ,
c.status_update_date ,
c.autopay_flag ,
c.notify_flag ,
c.last_batch_id ,
c.org_id ,
c.object_version_number ,
c.created_by_module ,
c.application_id ,
c.selling_party_id ,
c.federal_entity_type ,
c.trading_partner_agency_id,
c.duns_extension ,
c.advance_payment_indicator,
req_id
FROM (select distinct duplicate_id , customer_merge_header_id , customer_id
from ra_customer_merges cm
where cm.process_flag = 'N'
and cm.request_id = req_id
and cm.set_number = set_num) , HZ_CUST_ACCOUNTS c
WHERE c.cust_account_id = duplicate_id
AND duplicate_id <> customer_id;
arp_message.set_line(SQL%ROWCOUNT||' '|| 'Row(s) inserted in HZ_CUST_ACCOUNTS_M');
INSERT INTO hz_cust_account_roles_m(
customer_merge_header_id,
cust_account_role_id ,
party_id ,
cust_account_id ,
cust_acct_site_id ,
primary_flag ,
role_type ,
last_update_date ,
source_code ,
last_updated_by ,
creation_date ,
created_by ,
last_update_login ,
request_id ,
program_application_id ,
program_id ,
program_update_date ,
attribute_category ,
attribute1 ,
attribute2 ,
attribute3 ,
attribute4 ,
attribute5 ,
attribute6 ,
attribute7 ,
attribute8 ,
attribute9 ,
attribute10 ,
attribute11 ,
attribute12 ,
attribute13 ,
attribute14 ,
attribute15 ,
attribute16 ,
attribute17 ,
attribute18 ,
attribute19 ,
attribute20 ,
attribute21 ,
attribute22 ,
attribute23,
attribute24,
global_attribute_category ,
global_attribute1 ,
global_attribute2 ,
global_attribute3 ,
global_attribute4 ,
global_attribute5 ,
global_attribute6 ,
global_attribute7 ,
global_attribute8 ,
global_attribute9 ,
global_attribute10 ,
global_attribute11 ,
global_attribute12 ,
global_attribute13 ,
global_attribute14 ,
global_attribute15 ,
global_attribute16 ,
global_attribute17 ,
global_attribute18 ,
global_attribute19 ,
global_attribute20 ,
orig_system_reference ,
attribute25 ,
status ,
object_version_number,
created_by_module ,
application_id ,
merge_request_id
)
SELECT distinct
customer_merge_header_id,
ar.cust_account_role_id ,
ar.party_id ,
ar.cust_account_id ,
ar.cust_acct_site_id ,
ar.primary_flag ,
ar.role_type ,
ar.last_update_date ,
ar.source_code ,
ar.last_updated_by ,
ar.creation_date ,
ar.created_by ,
ar.last_update_login ,
ar.request_id ,
ar.program_application_id ,
ar.program_id ,
ar.program_update_date ,
ar.attribute_category ,
ar.attribute1 ,
ar.attribute2 ,
ar.attribute3 ,
ar.attribute4 ,
ar.attribute5 ,
ar.attribute6 ,
ar.attribute7 ,
ar.attribute8 ,
ar.attribute9 ,
ar.attribute10 ,
ar.attribute11 ,
ar.attribute12 ,
ar.attribute13 ,
ar.attribute14 ,
ar.attribute15 ,
ar.attribute16 ,
ar.attribute17 ,
ar.attribute18 ,
ar.attribute19 ,
ar.attribute20 ,
ar.attribute21 ,
ar.attribute22 ,
ar.attribute23,
ar.attribute24,
ar.global_attribute_category ,
ar.global_attribute1 ,
ar.global_attribute2 ,
ar.global_attribute3 ,
ar.global_attribute4 ,
ar.global_attribute5 ,
ar.global_attribute6 ,
ar.global_attribute7 ,
ar.global_attribute8 ,
ar.global_attribute9 ,
ar.global_attribute10 ,
ar.global_attribute11 ,
ar.global_attribute12 ,
ar.global_attribute13 ,
ar.global_attribute14 ,
ar.global_attribute15 ,
ar.global_attribute16 ,
ar.global_attribute17 ,
ar.global_attribute18 ,
ar.global_attribute19 ,
ar.global_attribute20 ,
ar.orig_system_reference ,
ar.attribute25 ,
ar.status ,
ar.object_version_number,
ar.created_by_module ,
ar.application_id ,
req_id
FROM(select distinct duplicate_id,duplicate_address_id,customer_merge_header_id
from ra_customer_merges cm
where cm.process_flag = 'N'
and cm.request_id = req_id
and cm.set_number = set_num
and cm.duplicate_id <> cm.customer_id), hz_cust_account_roles ar
WHERE ( ar.cust_account_id = duplicate_id OR
ar.cust_acct_site_id = duplicate_address_id )
AND ar.role_type = 'CONTACT';
arp_message.set_line(SQL%ROWCOUNT||' '|| 'Row(s) inserted in HZ_CUST_ACCOUNT_ROLES_M');
INSERT INTO hz_customer_profiles_m(
customer_merge_header_id,
cust_account_profile_id,
last_updated_by ,
last_update_date ,
last_update_login ,
created_by ,
creation_date ,
cust_account_id ,
status ,
collector_id ,
credit_analyst_id ,
credit_checking ,
next_credit_review_date ,
tolerance ,
discount_terms ,
dunning_letters ,
interest_charges ,
send_statements ,
credit_balance_statements,
credit_hold ,
profile_class_id ,
site_use_id ,
credit_rating ,
risk_code ,
standard_terms ,
override_terms ,
dunning_letter_set_id ,
interest_period_days ,
payment_grace_days ,
discount_grace_days ,
statement_cycle_id ,
account_status ,
percent_collectable ,
autocash_hierarchy_id ,
attribute_category ,
attribute1 ,
attribute2 ,
attribute3 ,
attribute4 ,
attribute5 ,
attribute6 ,
attribute7 ,
attribute8 ,
attribute9 ,
attribute10 ,
program_application_id ,
program_id ,
program_update_date ,
request_id ,
wh_update_date ,
attribute11 ,
attribute12 ,
attribute13 ,
attribute14 ,
attribute15 ,
auto_rec_incl_disputed_flag ,
tax_printing_option ,
charge_on_finance_charge_flag ,
grouping_rule_id ,
clearing_days ,
jgzz_attribute_category ,
jgzz_attribute1 ,
jgzz_attribute2 ,
jgzz_attribute3 ,
jgzz_attribute4 ,
jgzz_attribute5 ,
jgzz_attribute6 ,
jgzz_attribute7 ,
jgzz_attribute8 ,
jgzz_attribute9 ,
jgzz_attribute10 ,
jgzz_attribute11 ,
jgzz_attribute12 ,
jgzz_attribute13,
jgzz_attribute14 ,
jgzz_attribute15,
global_attribute1,
global_attribute2,
global_attribute3,
global_attribute4,
global_attribute5,
global_attribute6,
global_attribute7,
global_attribute8,
global_attribute9,
global_attribute10,
global_attribute11 ,
global_attribute12 ,
global_attribute13 ,
global_attribute14 ,
global_attribute15 ,
global_attribute16 ,
global_attribute17 ,
global_attribute18 ,
global_attribute19 ,
global_attribute20 ,
global_attribute_category ,
cons_inv_flag ,
cons_inv_type ,
autocash_hierarchy_id_for_adr ,
lockbox_matching_option ,
object_version_number ,
created_by_module ,
application_id ,
review_cycle ,
party_id ,
last_credit_review_date ,
merge_request_id,
automatch_set_id --8477178
)
SELECT distinct
customer_merge_header_id,
cp.cust_account_profile_id,
cp.last_updated_by ,
cp.last_update_date ,
cp.last_update_login ,
cp.created_by ,
cp.creation_date ,
cp.cust_account_id ,
cp.status ,
cp.collector_id ,
cp.credit_analyst_id ,
cp.credit_checking ,
cp.next_credit_review_date ,
cp.tolerance ,
cp.discount_terms ,
cp.dunning_letters ,
cp.interest_charges ,
cp.send_statements ,
cp.credit_balance_statements,
cp.credit_hold ,
cp.profile_class_id ,
cp.site_use_id ,
cp.credit_rating ,
cp.risk_code ,
cp.standard_terms ,
cp.override_terms ,
cp.dunning_letter_set_id ,
cp.interest_period_days ,
cp.payment_grace_days ,
cp.discount_grace_days ,
cp.statement_cycle_id ,
cp.account_status ,
cp.percent_collectable ,
cp.autocash_hierarchy_id ,
cp.attribute_category ,
cp.attribute1 ,
cp.attribute2 ,
cp.attribute3 ,
cp.attribute4 ,
cp.attribute5 ,
cp.attribute6 ,
cp.attribute7 ,
cp.attribute8 ,
cp.attribute9 ,
cp.attribute10 ,
cp.program_application_id ,
cp.program_id ,
cp.program_update_date ,
cp.request_id ,
cp.wh_update_date ,
cp.attribute11 ,
cp.attribute12 ,
cp.attribute13 ,
cp.attribute14 ,
cp.attribute15 ,
cp.auto_rec_incl_disputed_flag ,
cp.tax_printing_option ,
cp.charge_on_finance_charge_flag ,
cp.grouping_rule_id ,
cp.clearing_days ,
cp.jgzz_attribute_category ,
cp.jgzz_attribute1 ,
cp.jgzz_attribute2 ,
cp.jgzz_attribute3 ,
cp.jgzz_attribute4 ,
cp.jgzz_attribute5 ,
cp.jgzz_attribute6 ,
cp.jgzz_attribute7 ,
cp.jgzz_attribute8 ,
cp.jgzz_attribute9 ,
cp.jgzz_attribute10 ,
cp.jgzz_attribute11 ,
cp.jgzz_attribute12 ,
cp.jgzz_attribute13,
cp.jgzz_attribute14 ,
cp.jgzz_attribute15,
cp.global_attribute1,
cp.global_attribute2,
cp.global_attribute3,
cp.global_attribute4,
cp.global_attribute5,
cp.global_attribute6,
cp.global_attribute7,
cp.global_attribute8,
cp.global_attribute9,
cp.global_attribute10,
cp.global_attribute11 ,
cp.global_attribute12 ,
cp.global_attribute13 ,
cp.global_attribute14 ,
cp.global_attribute15 ,
cp.global_attribute16 ,
cp.global_attribute17 ,
cp.global_attribute18 ,
cp.global_attribute19 ,
cp.global_attribute20 ,
cp.global_attribute_category ,
cp.cons_inv_flag ,
cp.cons_inv_type ,
cp.autocash_hierarchy_id_for_adr ,
cp.lockbox_matching_option ,
cp.object_version_number ,
cp.created_by_module ,
cp.application_id ,
cp.review_cycle ,
cp.party_id ,
cp.last_credit_review_date ,
req_id,
cp.automatch_set_id --bug 8477178
FROM(select distinct duplicate_id,duplicate_site_id,customer_merge_header_id
from ra_customer_merges cm
where cm.process_flag = 'N'
and cm.request_id = req_id
and cm.set_number = set_num
and cm.duplicate_id <> cm.customer_id ), hz_customer_profiles cp
WHERE ( cp.cust_account_id = duplicate_id AND cp.site_use_id is NULL)
OR (cp.site_use_id = duplicate_site_id );
arp_message.set_line(SQL%ROWCOUNT||' '|| 'Row(s) inserted in HZ_CUSTOMER_PROFILES_M');
INSERT INTO hz_cust_profile_amts_m(
customer_merge_header_id,
cust_acct_profile_amt_id ,
last_updated_by ,
last_update_date ,
created_by ,
creation_date ,
cust_account_profile_id ,
currency_code ,
last_update_login ,
trx_credit_limit ,
overall_credit_limit ,
min_dunning_amount ,
min_dunning_invoice_amount ,
max_interest_charge ,
min_statement_amount ,
auto_rec_min_receipt_amount,
interest_rate ,
attribute_category ,
attribute1 ,
attribute2 ,
attribute3 ,
attribute4 ,
attribute5 ,
attribute6 ,
attribute7 ,
attribute8 ,
attribute9 ,
attribute10 ,
attribute11 ,
attribute12 ,
attribute13 ,
attribute14 ,
attribute15 ,
min_fc_balance_amount ,
min_fc_invoice_amount ,
cust_account_id ,
site_use_id ,
expiration_date ,
request_id ,
program_application_id ,
program_id ,
program_update_date ,
wh_update_date ,
jgzz_attribute_category ,
jgzz_attribute1 ,
jgzz_attribute2 ,
jgzz_attribute3 ,
jgzz_attribute4 ,
jgzz_attribute5 ,
jgzz_attribute6 ,
jgzz_attribute7 ,
jgzz_attribute8 ,
jgzz_attribute9 ,
jgzz_attribute10 ,
jgzz_attribute11 ,
jgzz_attribute12 ,
jgzz_attribute13 ,
jgzz_attribute14 ,
jgzz_attribute15 ,
global_attribute1 ,
global_attribute2 ,
global_attribute3 ,
global_attribute4 ,
global_attribute5 ,
global_attribute6 ,
global_attribute7 ,
global_attribute8 ,
global_attribute9 ,
global_attribute10 ,
global_attribute11 ,
global_attribute12 ,
global_attribute13 ,
global_attribute14 ,
global_attribute15 ,
global_attribute16 ,
global_attribute17 ,
global_attribute18 ,
global_attribute19 ,
global_attribute20 ,
global_attribute_category ,
object_version_number ,
created_by_module ,
application_id ,
merge_request_id
)
select distinct
customer_merge_header_id,
pa.cust_acct_profile_amt_id ,
pa.last_updated_by ,
pa.last_update_date ,
pa.created_by ,
pa.creation_date ,
pa.cust_account_profile_id ,
pa.currency_code ,
pa.last_update_login ,
pa.trx_credit_limit ,
pa.overall_credit_limit ,
pa.min_dunning_amount ,
pa.min_dunning_invoice_amount ,
pa.max_interest_charge ,
pa.min_statement_amount ,
pa.auto_rec_min_receipt_amount,
pa.interest_rate ,
pa.attribute_category ,
pa.attribute1 ,
pa.attribute2 ,
pa.attribute3 ,
pa.attribute4 ,
pa.attribute5 ,
pa.attribute6 ,
pa.attribute7 ,
pa.attribute8 ,
pa.attribute9 ,
pa.attribute10 ,
pa.attribute11 ,
pa.attribute12 ,
pa.attribute13 ,
pa.attribute14 ,
pa.attribute15 ,
pa.min_fc_balance_amount ,
pa.min_fc_invoice_amount ,
pa.cust_account_id ,
pa.site_use_id ,
pa.expiration_date ,
pa.request_id ,
pa.program_application_id ,
pa.program_id ,
pa.program_update_date ,
pa.wh_update_date ,
pa.jgzz_attribute_category ,
pa.jgzz_attribute1 ,
pa.jgzz_attribute2 ,
pa.jgzz_attribute3 ,
pa.jgzz_attribute4 ,
pa.jgzz_attribute5 ,
pa.jgzz_attribute6 ,
pa.jgzz_attribute7 ,
pa.jgzz_attribute8 ,
pa.jgzz_attribute9 ,
pa.jgzz_attribute10 ,
pa.jgzz_attribute11 ,
pa.jgzz_attribute12 ,
pa.jgzz_attribute13 ,
pa.jgzz_attribute14 ,
pa.jgzz_attribute15 ,
pa.global_attribute1 ,
pa.global_attribute2 ,
pa.global_attribute3 ,
pa.global_attribute4 ,
pa.global_attribute5 ,
pa.global_attribute6 ,
pa.global_attribute7 ,
pa.global_attribute8 ,
pa.global_attribute9 ,
pa.global_attribute10 ,
pa.global_attribute11 ,
pa.global_attribute12 ,
pa.global_attribute13 ,
pa.global_attribute14 ,
pa.global_attribute15 ,
pa.global_attribute16 ,
pa.global_attribute17 ,
pa.global_attribute18 ,
pa.global_attribute19 ,
pa.global_attribute20 ,
pa.global_attribute_category ,
pa.object_version_number ,
pa.created_by_module ,
pa.application_id ,
req_id
FROM (select distinct duplicate_id,duplicate_site_id,customer_merge_header_id
from ra_customer_merges cm
where cm.process_flag = 'N'
and cm.request_id = req_id
and cm.set_number = set_num ),hz_cust_profile_amts pa
WHERE (pa.cust_account_id = duplicate_id and pa.site_use_id is NULL)
OR (pa.site_use_id = duplicate_site_id);
arp_message.set_line(SQL%ROWCOUNT||' '|| 'Row(s) inserted in HZ_CUST_PROFILE_AMTS_M');
INSERT INTO hz_cust_acct_sites_all_m(
customer_merge_header_id,
cust_acct_site_id ,
cust_account_id ,
party_site_id ,
last_update_date ,
last_updated_by ,
creation_date ,
created_by ,
last_update_login ,
request_id ,
program_application_id ,
program_id ,
program_update_date ,
--wh_update_date ,
attribute_category ,
attribute1 ,
attribute2 ,
attribute3 ,
attribute4 ,
attribute5 ,
attribute6 ,
attribute7 ,
attribute8 ,
attribute9 ,
attribute10 ,
attribute11 ,
attribute12 ,
attribute13 ,
attribute14 ,
attribute15 ,
attribute16 ,
attribute17 ,
attribute18 ,
attribute19 ,
attribute20 ,
global_attribute_category ,
global_attribute1 ,
global_attribute2 ,
global_attribute3 ,
global_attribute4 ,
global_attribute5 ,
global_attribute6 ,
global_attribute7 ,
global_attribute8 ,
global_attribute9 ,
global_attribute10 ,
global_attribute11 ,
global_attribute12 ,
global_attribute13 ,
global_attribute14 ,
global_attribute15 ,
global_attribute16 ,
global_attribute17 ,
global_attribute18 ,
global_attribute19 ,
global_attribute20 ,
orig_system_reference ,
status ,
org_id ,
bill_to_flag ,
market_flag ,
ship_to_flag ,
customer_category_code ,
language ,
key_account_flag ,
tp_header_id ,
ece_tp_location_code ,
--service_territory_id ,
primary_specialist_id ,
secondary_specialist_id ,
territory_id ,
address_text ,
territory ,
translated_customer_name ,
object_version_number ,
created_by_module ,
application_id ,
merge_request_id
)
select
customer_merge_header_id,
acs.cust_acct_site_id ,
acs.cust_account_id ,
acs.party_site_id ,
acs.last_update_date ,
acs.last_updated_by ,
acs.creation_date ,
acs.created_by ,
acs.last_update_login ,
acs.request_id ,
acs.program_application_id ,
acs.program_id ,
acs.program_update_date ,
--wh_update_date ,
acs.attribute_category ,
acs.attribute1 ,
acs.attribute2 ,
acs.attribute3 ,
acs.attribute4 ,
acs.attribute5 ,
acs.attribute6 ,
acs.attribute7 ,
acs.attribute8 ,
acs.attribute9 ,
acs.attribute10 ,
acs.attribute11 ,
acs.attribute12 ,
acs.attribute13 ,
acs.attribute14 ,
acs.attribute15 ,
acs.attribute16 ,
acs.attribute17 ,
acs.attribute18 ,
acs.attribute19 ,
acs.attribute20 ,
acs.global_attribute_category ,
acs.global_attribute1 ,
acs.global_attribute2 ,
acs.global_attribute3 ,
acs.global_attribute4 ,
acs.global_attribute5 ,
acs.global_attribute6 ,
acs.global_attribute7 ,
acs.global_attribute8 ,
acs.global_attribute9 ,
acs.global_attribute10 ,
acs.global_attribute11 ,
acs.global_attribute12 ,
acs.global_attribute13 ,
acs.global_attribute14 ,
acs.global_attribute15 ,
acs.global_attribute16 ,
acs.global_attribute17 ,
acs.global_attribute18 ,
acs.global_attribute19 ,
acs.global_attribute20 ,
acs.orig_system_reference ,
acs.status ,
acs.org_id ,
acs.bill_to_flag ,
acs.market_flag ,
acs.ship_to_flag ,
acs.customer_category_code ,
acs.language ,
acs.key_account_flag ,
acs.tp_header_id ,
acs.ece_tp_location_code ,
--service_territory_id ,
acs.primary_specialist_id ,
acs.secondary_specialist_id ,
acs.territory_id ,
acs.address_text ,
acs.territory ,
acs.translated_customer_name ,
acs.object_version_number ,
acs.created_by_module ,
acs.application_id ,
req_id
FROM (select distinct duplicate_id,duplicate_address_id,customer_merge_header_id,org_id
from ra_customer_merges cm
where cm.process_flag = 'N'
and cm.request_id = req_id
and cm.set_number = set_num ) m,hz_cust_acct_sites_all acs
WHERE acs.cust_acct_site_id = duplicate_address_id
AND acs.org_id = m.org_id ;
arp_message.set_line(SQL%ROWCOUNT||' '|| 'Row(s) inserted in HZ_CUST_ACCT_SITES_ALL_M');
INSERT INTO hz_cust_site_uses_all_m(
customer_merge_header_id,
site_use_id ,
cust_acct_site_id ,
last_update_date ,
last_updated_by ,
creation_date ,
created_by ,
site_use_code ,
primary_flag ,
status ,
location ,
last_update_login ,
contact_id ,
bill_to_site_use_id ,
orig_system_reference ,
sic_code ,
payment_term_id ,
gsa_indicator ,
ship_partial ,
ship_via ,
fob_point ,
order_type_id ,
price_list_id ,
freight_term ,
warehouse_id ,
territory_id ,
attribute_category ,
attribute1 ,
attribute2 ,
attribute3 ,
attribute4 ,
attribute5 ,
attribute6 ,
attribute7 ,
attribute8 ,
attribute9 ,
attribute10 ,
request_id ,
program_application_id ,
program_id ,
program_update_date ,
tax_reference ,
sort_priority ,
tax_code ,
attribute11 ,
attribute12 ,
attribute13 ,
attribute14 ,
attribute15 ,
attribute16 ,
attribute17 ,
attribute18 ,
attribute19 ,
attribute20 ,
attribute21 ,
attribute22 ,
attribute23 ,
attribute24 ,
attribute25 ,
last_accrue_charge_date ,
second_last_accrue_charge_date ,
last_unaccrue_charge_date ,
second_last_unaccrue_chrg_date ,
demand_class_code ,
org_id,
tax_header_level_flag ,
tax_rounding_rule ,
--wh_update_date ,
global_attribute1 ,
global_attribute2 ,
global_attribute3 ,
global_attribute4 ,
global_attribute5 ,
global_attribute6 ,
global_attribute7 ,
global_attribute8 ,
global_attribute9 ,
global_attribute10 ,
global_attribute11 ,
global_attribute12 ,
global_attribute13 ,
global_attribute14 ,
global_attribute15 ,
global_attribute16 ,
global_attribute17 ,
global_attribute18 ,
global_attribute19 ,
global_attribute20 ,
global_attribute_category ,
primary_salesrep_id ,
finchrg_receivables_trx_id ,
dates_negative_tolerance ,
dates_positive_tolerance ,
date_type_preference ,
over_shipment_tolerance ,
under_shipment_tolerance ,
item_cross_ref_pref ,
over_return_tolerance ,
under_return_tolerance ,
ship_sets_include_lines_flag ,
arrivalsets_include_lines_flag ,
sched_date_push_flag ,
invoice_quantity_rule ,
pricing_event ,
gl_id_rec ,
gl_id_rev ,
gl_id_tax ,
gl_id_freight ,
gl_id_clearing ,
gl_id_unbilled ,
gl_id_unearned ,
gl_id_unpaid_rec ,
gl_id_remittance ,
gl_id_factor ,
tax_classification ,
object_version_number ,
created_by_module ,
application_id ,
merge_request_id
)
select
customer_merge_header_id,
su.site_use_id ,
su.cust_acct_site_id ,
su.last_update_date ,
su.last_updated_by ,
su.creation_date ,
su.created_by ,
su.site_use_code ,
su.primary_flag ,
su.status ,
su.location ,
su.last_update_login ,
su.contact_id ,
su.bill_to_site_use_id ,
su.orig_system_reference ,
su.sic_code ,
su.payment_term_id ,
su.gsa_indicator ,
su.ship_partial ,
su.ship_via ,
su.fob_point ,
su.order_type_id ,
su.price_list_id ,
su.freight_term ,
su.warehouse_id ,
su.territory_id ,
su.attribute_category ,
su.attribute1 ,
su.attribute2 ,
su.attribute3 ,
su.attribute4 ,
su.attribute5 ,
su.attribute6 ,
su.attribute7 ,
su.attribute8 ,
su.attribute9 ,
su.attribute10 ,
su.request_id ,
su.program_application_id ,
su.program_id ,
su.program_update_date ,
su.tax_reference ,
su.sort_priority ,
su.tax_code ,
su.attribute11 ,
su.attribute12 ,
su.attribute13 ,
su.attribute14 ,
su.attribute15 ,
su.attribute16 ,
su.attribute17 ,
su.attribute18 ,
su.attribute19 ,
su.attribute20 ,
su.attribute21 ,
su.attribute22 ,
su.attribute23 ,
su.attribute24 ,
su.attribute25 ,
su.last_accrue_charge_date ,
su.second_last_accrue_charge_date ,
su.last_unaccrue_charge_date ,
su.second_last_unaccrue_chrg_date ,
su.demand_class_code ,
su.org_id,
su.tax_header_level_flag ,
su.tax_rounding_rule ,
--wh_update_date ,
su.global_attribute1 ,
su.global_attribute2 ,
su.global_attribute3 ,
su.global_attribute4 ,
su.global_attribute5 ,
su.global_attribute6 ,
su.global_attribute7 ,
su.global_attribute8 ,
su.global_attribute9 ,
su.global_attribute10 ,
su.global_attribute11 ,
su.global_attribute12 ,
su.global_attribute13 ,
su.global_attribute14 ,
su.global_attribute15 ,
su.global_attribute16 ,
su.global_attribute17 ,
su.global_attribute18 ,
su.global_attribute19 ,
su.global_attribute20 ,
su.global_attribute_category ,
su.primary_salesrep_id ,
su.finchrg_receivables_trx_id ,
su.dates_negative_tolerance ,
su.dates_positive_tolerance ,
su.date_type_preference ,
su.over_shipment_tolerance ,
su.under_shipment_tolerance ,
su.item_cross_ref_pref ,
su.over_return_tolerance ,
su.under_return_tolerance ,
su.ship_sets_include_lines_flag ,
su.arrivalsets_include_lines_flag ,
su.sched_date_push_flag ,
su.invoice_quantity_rule ,
su.pricing_event ,
su.gl_id_rec ,
su.gl_id_rev ,
su.gl_id_tax ,
su.gl_id_freight ,
su.gl_id_clearing ,
su.gl_id_unbilled ,
su.gl_id_unearned ,
su.gl_id_unpaid_rec ,
su.gl_id_remittance ,
su.gl_id_factor ,
su.tax_classification ,
su.object_version_number ,
su.created_by_module ,
su.application_id ,
req_id
FROM (select distinct duplicate_site_id,customer_merge_header_id,org_id
from ra_customer_merges cm
where cm.process_flag = 'N'
and cm.request_id = req_id
and cm.set_number = set_num ) m,hz_cust_site_uses_all su --SSUptake
WHERE su.site_use_id = duplicate_site_id
AND su.org_id = m.org_id; --SSUptake
arp_message.set_line(SQL%ROWCOUNT||' '|| 'Row(s) inserted in HZ_CUST_SITE_USES_ALL_M');
INSERT INTO hz_cust_acct_relate_all_m(
customer_merge_header_id,
cust_account_id ,
related_cust_account_id ,
last_update_date ,
last_updated_by ,
creation_date ,
created_by ,
last_update_login ,
relationship_type ,
comments ,
attribute_category ,
attribute1 ,
attribute2 ,
attribute3 ,
attribute4 ,
attribute5 ,
attribute6 ,
attribute7 ,
attribute8 ,
attribute9 ,
attribute10 ,
request_id ,
program_application_id ,
program_id ,
program_update_date ,
customer_reciprocal_flag ,
status ,
attribute11 ,
attribute12 ,
attribute13 ,
attribute14 ,
attribute15 ,
org_id ,
bill_to_flag ,
ship_to_flag ,
object_version_number ,
created_by_module ,
application_id ,
merge_request_id ,
cust_acct_relate_id --bug 7593763
)
SELECT
customer_merge_header_id,
yt.cust_account_id ,
yt.related_cust_account_id ,
yt.last_update_date ,
yt.last_updated_by ,
yt.creation_date ,
yt.created_by ,
yt.last_update_login ,
yt.relationship_type ,
yt.comments ,
yt.attribute_category ,
yt.attribute1 ,
yt.attribute2 ,
yt.attribute3 ,
yt.attribute4 ,
yt.attribute5 ,
yt.attribute6 ,
yt.attribute7 ,
yt.attribute8 ,
yt.attribute9 ,
yt.attribute10 ,
yt.request_id ,
yt.program_application_id ,
yt.program_id ,
yt.program_update_date ,
yt.customer_reciprocal_flag ,
yt.status ,
yt.attribute11 ,
yt.attribute12 ,
yt.attribute13 ,
yt.attribute14 ,
yt.attribute15 ,
yt.org_id ,
yt.bill_to_flag ,
yt.ship_to_flag ,
yt.object_version_number ,
yt.created_by_module ,
yt.application_id ,
req_id ,
cust_acct_relate_id
FROM (select distinct duplicate_id, customer_merge_header_id,org_id
from ra_customer_merges cm
where cm.process_flag = 'N'
and cm.request_id = req_id
and cm.set_number = set_num
and cm.duplicate_id <> cm.customer_id) m,hz_cust_acct_relate_all yt --SSUptake
WHERE ( yt.cust_account_id = duplicate_id OR
yt.related_cust_account_id = duplicate_id )
AND m.org_id = yt.org_id ; --SSUptake
arp_message.set_line(SQL%ROWCOUNT||' '|| 'Row(s) inserted in HZ_CUST_ACCT_RELATE_ALL_M');
HZ_CUST_ACCT_RELATE_PKG.Insert_Row (
X_CUST_ACCOUNT_ID => p_cust_acct_relate_rec.cust_account_id,
X_RELATED_CUST_ACCOUNT_ID => p_cust_acct_relate_rec.related_cust_account_id,
X_RELATIONSHIP_TYPE => p_cust_acct_relate_rec.relationship_type,
X_COMMENTS => p_cust_acct_relate_rec.comments,
X_ATTRIBUTE_CATEGORY => p_cust_acct_relate_rec.attribute_category,
X_ATTRIBUTE1 => p_cust_acct_relate_rec.attribute1,
X_ATTRIBUTE2 => p_cust_acct_relate_rec.attribute2,
X_ATTRIBUTE3 => p_cust_acct_relate_rec.attribute3,
X_ATTRIBUTE4 => p_cust_acct_relate_rec.attribute4,
X_ATTRIBUTE5 => p_cust_acct_relate_rec.attribute5,
X_ATTRIBUTE6 => p_cust_acct_relate_rec.attribute6,
X_ATTRIBUTE7 => p_cust_acct_relate_rec.attribute7,
X_ATTRIBUTE8 => p_cust_acct_relate_rec.attribute8,
X_ATTRIBUTE9 => p_cust_acct_relate_rec.attribute9,
X_ATTRIBUTE10 => p_cust_acct_relate_rec.attribute10,
X_CUSTOMER_RECIPROCAL_FLAG => p_cust_acct_relate_rec.customer_reciprocal_flag,
X_STATUS => p_cust_acct_relate_rec.status,
X_ATTRIBUTE11 => p_cust_acct_relate_rec.attribute11,
X_ATTRIBUTE12 => p_cust_acct_relate_rec.attribute12,
X_ATTRIBUTE13 => p_cust_acct_relate_rec.attribute13,
X_ATTRIBUTE14 => p_cust_acct_relate_rec.attribute14,
X_ATTRIBUTE15 => p_cust_acct_relate_rec.attribute15,
X_BILL_TO_FLAG => p_cust_acct_relate_rec.bill_to_flag,
X_SHIP_TO_FLAG => p_cust_acct_relate_rec.ship_to_flag,
X_OBJECT_VERSION_NUMBER => 1,
X_CREATED_BY_MODULE => p_cust_acct_relate_rec.created_by_module,
X_APPLICATION_ID => p_cust_acct_relate_rec.application_id,
X_ORG_ID => p_cust_acct_relate_rec.org_id,
X_CUST_ACCT_RELATE_ID => l_cust_acct_relate_id
);