[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
procedure delete_customer_alt_names(
req_id NUMBER,
set_num NUMBER
);
procedure update_merge_as_failed(
p_request_id NUMBER,
p_set_num NUMBER,
p_customer_merge_header_id NUMBER,
p_error_text VARCHAR2
);
SELECT hr.name
FROM hr_operating_units hr
WHERE mo_global.check_access(hr.organization_id) = 'Y'
ORDER BY hr.name;
SELECT COUNT(*) INTO l_count
FROM hr_operating_units hr
WHERE mo_global.check_access(hr.organization_id) = 'Y';
| update_merge_as_failed
|
| DESCRIPTION
| Update a bad merge record as failed.
| SCOPE - PRIVATE
|
| EXETERNAL PROCEDURES/FUNCTIONS ACCESSED
|
| ARGUMENTS : IN:
| req_id
| set_num
| customer_merge_header_id
| p_error_text
| OUT:
| IN/ OUT:
|
| RETURNS : NONE
|
| NOTES
|
| MODIFICATION HISTORY
| 04-02-2003 Rajeshwari Bug 2669389 Created.
| 04-07-2003 Rajeshwari Removed the update of set_number
| to -1.
+===========================================================================*/
procedure update_merge_as_failed(
p_request_id IN NUMBER,
p_set_num IN NUMBER,
p_customer_merge_header_id IN NUMBER,
p_error_text IN VARCHAR2
) IS
BEGIN
UPDATE ra_customer_merges set process_flag = 'FAILED'
WHERE request_id = p_request_id
AND customer_merge_header_id = p_customer_merge_header_id
;
UPDATE ra_customer_merge_headers
SET process_flag = 'FAILED',merge_fail_msg = p_error_text
WHERE request_id = p_request_id
AND customer_merge_header_id = p_customer_merge_header_id
;
END update_merge_as_failed;
select customer_merge_header_id into l_customer_merge_header_id
from ra_customer_merges
where request_id = req_id
AND set_number = v_current_set
AND process_flag = 'N'
AND ROWNUM = 1
FOR UPDATE NOWAIT;
update_merge_as_failed (
req_id,
v_current_set,
l_customer_merge_header_id,
error_text
);
update ra_customer_merges
set set_number = set_num
WHERE request_id = req_id
AND (process_flag = 'N' or process_flag = 'FAILED')
AND set_number = v_current_set;
SELECT count(*) INTO l_count
FROM ra_customer_merges
WHERE request_id = req_id
AND set_number = set_num
AND process_flag = 'FAILED'
;
SELECT 'Y' INTO l_exist
FROM sys.all_tables
WHERE table_name = 'JTF_HOOKS_DATA'
AND ROWNUM = 1 and owner = l_schema;
cur_sql := 'SELECT hook_package, hook_api,product_code ' ||
'FROM jtf_hooks_data '||
'WHERE package_name = ''ARP_CMERGE_MASTER'' ' ||
'AND api_name = ''MERGE_PRODUCTS'' ' ||
'AND execute_flag = ''Y'' ' ||
'ORDER BY execution_order ';
| delete_rows
|
| DESCRIPTION
| Delete marked rows in customer tables.
|
| SCOPE - PUBLIC
|
| EXETERNAL PROCEDURES/FUNCTIONS ACCESSED
|
| ARGUMENTS : IN:
| req_id
| set_num
| OUT:
| status
| IN/ OUT:
|
| RETURNS : NONE
|
| NOTES
|
| MODIFICATION HISTORY
| Jianying Huang 20-DEC-00 Created for bug 1535542.
|
+===========================================================================*/
PROCEDURE delete_rows (
req_id NUMBER,
set_num NUMBER,
status OUT NOCOPY NUMBER
) IS
BEGIN
arp_message.set_line( 'ARP_CMERGE_MASTER.delete_rows()+' );
arp_cmerge_arcus.delete_rows( req_id, set_num );
arp_cmerge_arcpf.delete_rows( req_id, set_num );
arp_message.set_line( 'ARP_CMERGE_MASTER.delete_rows()-' );
arp_message.set_error( 'ARP_CMERGE_MASTER.delete_rows');
END delete_rows;
| S V Sowjanya 16-NOV-04 Bug 4693912: Modified update statement in mark_merge_rows
|
+===========================================================================*/
PROCEDURE mark_merge_rows (
req_id NUMBER,
p_process_flag VARCHAR2,
p_merge_rule VARCHAR2,
p_priority VARCHAR2,
p_number_of_merges NUMBER
) IS
CURSOR c_requests(c_priority varchar2) IS
SELECT distinct request_id, process_flag
FROM ra_customer_merge_headers
WHERE process_flag IN ('PROCESSING', 'N')
AND priority = c_priority;
UPDATE ra_customer_merge_headers
SET process_flag = p_process_flag
WHERE request_id = req_id;
SELECT customer_merge_header_id, process_flag
BULK COLLECT INTO l_header_id_t,l_process_flag_t
FROM ra_customer_merge_headers mh
WHERE process_flag IN ('PROCESSING', 'N')
AND priority = p_priority;
UPDATE ra_customer_merge_headers
SET process_flag = l_new_process_flag
WHERE request_id = l_request_id
AND process_flag = l_process_flag
AND priority = p_priority; --3897822
UPDATE ra_customer_merge_headers
SET process_flag = l_new_process_flag
WHERE request_id IS NULL
AND process_flag = l_process_flag
AND priority = p_priority; --3897822
UPDATE ra_customer_merge_headers
SET process_flag = l_new_process_flag
WHERE process_flag LIKE 'ERROR%';
UPDATE ra_customer_merge_headers
SET process_flag = l_new_process_flag
WHERE process_flag = p_process_flag;
UPDATE ra_customer_merge_headers mh
SET request_id = req_id ,
merge_fail_msg = NULL
WHERE process_flag = l_new_process_flag
AND customer_merge_header_id IN
(SELECT customer_merge_header_id
FROM
(SELECT customer_merge_header_id
FROM ra_customer_merge_headers mh1
WHERE mh1.priority = p_priority
AND mh1.process_flag = l_new_process_flag
AND NOT EXISTS (
SELECT 'Y' FROM ra_customer_merges m
WHERE m.customer_merge_header_id = mh1.customer_merge_header_id
AND mo_global.check_access(m.org_id) <> 'Y'
AND ROWNUM =1
)
AND ( EXISTS (
SELECT 'Y'
FROM ra_customer_merges m, hz_cust_acct_sites site
WHERE m.customer_merge_header_id = mh1.customer_merge_header_id
AND m.duplicate_address_id = site.cust_acct_site_id
AND ROWNUM = 1)
OR EXISTS(
SELECT 'Y'
FROM ra_customer_merges m
WHERE m.customer_merge_header_id = mh1.customer_merge_header_id
AND m.duplicate_address_id = -1)
)
ORDER BY last_update_date
)
WHERE ROWNUM <= p_number_of_merges);
UPDATE ra_customer_merge_headers mh
SET process_flag = l_process_flag_t(i)
WHERE request_id <> req_id
AND mh.customer_merge_header_id = l_header_id_t(i);
UPDATE ra_customer_merge_headers mh
SET process_flag = 'Y'
WHERE request_id = req_id
AND process_flag = l_new_process_flag
AND priority = p_priority --3897822
AND EXISTS (
SELECT 'Y'
FROM ra_customer_merges m
WHERE m.customer_merge_header_id = mh.customer_merge_header_id
AND m.process_flag = 'Y'
AND ROWNUM = 1 );
UPDATE ra_customer_merges m
SET (request_id,
process_flag) = (
SELECT mh.request_id, mh.process_flag
FROM ra_customer_merge_headers mh
WHERE mh.customer_merge_header_id = m.customer_merge_header_id
AND mh.process_flag = l_new_process_flag )
WHERE m.customer_merge_header_id IN (
SELECT mh.customer_merge_header_id
FROM ra_customer_merge_headers mh
WHERE mh.process_flag = l_new_process_flag
AND request_id = req_id
AND mh.priority = p_priority); --3897822
INSERT into ar_conc_process_requests
(request_id, concurrent_program_name)
VALUES
(req_id, program_name);
Select
all sites that must be merged
MINUS
all sites specified in ra_customer_merges
Migration to new customer model.
-------------------------------
With the new cust. model, cust acct and sites are already
striped by ou. The cust accts are no longer global.
Because the sites will not be referenced in other ou,
the tables that will replace RA_ADDRESSES and RA_SITE_USES
will be HZ_CUST_ACCT_SITES and HZ_CUST_SITE_USES.
Columns will be changed correspondingly.
*/
--Bug 1725662: rewrite query to use index on
--ra_customer_merge_headers.(request_id, process_flag);
SELECT su.site_use_id site_use_id,
mh.duplicate_id duplicate_id
FROM hz_cust_acct_sites addr,
hz_cust_site_uses su,
ra_customer_merge_headers mh
WHERE mh.request_id = req_id
AND mh.process_flag = p_process_flag
AND (mh.org_id = -1 OR (mh.org_id <> -1 AND addr.org_id = mh.org_id)) --SSUptake
AND mh.duplicate_id <> mh.customer_id
AND su.cust_acct_site_id = addr.cust_acct_site_id
AND addr.cust_account_id = mh.duplicate_id
AND NOT EXISTS (
SELECT 'same site in merge detail'
FROM ra_customer_merges m
WHERE m.customer_merge_header_id = mh.customer_merge_header_id
AND m.duplicate_site_id = su.site_use_id
AND m.org_id = su.org_id
);
SELECT m.duplicate_id duplicate_id
FROM ra_customer_merges m
WHERE m.process_flag = p_process_flag
AND m.request_id = req_id
AND duplicate_address_id <> -1 --4693912
-- AND (m.duplicate_site_id <> -99 AND m.customer_site_id <> -99)
AND ((m.customer_createsame <> 'Y'
AND (m.customer_site_id IN (
SELECT m2.duplicate_site_id
FROM ra_customer_merges m2
WHERE m2.rowid <> m.rowid
AND m2.process_flag = p_process_flag
AND m2.duplicate_address_id = m.customer_address_id )
OR m.duplicate_site_id IN (
SELECT m2.customer_site_id
FROM ra_customer_merges m2
WHERE m2.rowid <> m.rowid
AND m2.process_flag = p_process_flag
AND m2.customer_address_id = m.duplicate_address_id )))
OR m.duplicate_site_id IN (
SELECT m2.duplicate_site_id
FROM ra_customer_merges m2
WHERE m2.rowid <> m.rowid
AND m2.process_flag = p_process_flag
--AND m2.duplicate_site_id <> -99
AND m2.duplicate_address_id = m.duplicate_address_id));
UPDATE ra_customer_merges
SET process_flag = 'ERROR 1'
WHERE duplicate_id = missing_sites.duplicate_id
AND request_id = req_id
AND process_flag = p_process_flag;
UPDATE ra_customer_merge_headers
SET process_flag = 'ERROR 1'
WHERE duplicate_id = missing_sites.duplicate_id
AND request_id = req_id
AND process_flag = p_process_flag;
UPDATE ra_customer_merges
SET process_flag = 'ERROR 2'
WHERE duplicate_id = invalid_merges.duplicate_id
AND request_id = req_id
AND process_flag = p_process_flag;
UPDATE ra_customer_merge_headers
SET process_flag = 'ERROR 2'
WHERE duplicate_id = invalid_merges.duplicate_id
AND request_id = req_id
AND process_flag = p_process_flag;
SELECT rowid, duplicate_id
FROM ra_customer_merges
WHERE request_id = req_id
AND process_flag = p_process_flag
ORDER BY duplicate_id;
UPDATE ra_customer_merges
SET set_number = v_last_set
WHERE rowid = v_rowid;
SELECT rowid, duplicate_id
FROM ra_customer_merges
WHERE request_id = req_id
AND set_number = set_num
ORDER BY duplicate_id;
UPDATE ra_customer_merges
SET set_number = v_last_set
WHERE request_id = req_id
AND rowid = v_rowid;
select set_number into l_count
from ra_customer_merges
where request_id = req_id
AND set_number = v_last_set
AND rowid = v_rowid;
arp_cmerge_master.delete_customer_alt_names ( req_id , set_num ) ;
UPDATE ra_customer_merges
SET process_flag = 'Y',
last_update_date = sysdate,
last_updated_by = hz_utility_v2pub.user_id,
last_update_login = hz_utility_v2pub.last_update_login,
program_application_id = hz_utility_v2pub.program_application_id,
program_id = hz_utility_v2pub.program_id,
program_update_date = sysdate
WHERE request_id = req_id
AND set_number = set_num
AND process_flag = 'N';
UPDATE ra_customer_merge_headers mh
SET (process_flag,
-- request_id,
last_update_date,
last_updated_by,
last_update_login,
program_application_id,
program_id,
program_update_date) = (
SELECT
m.process_flag,
-- m.request_id,
sysdate,
m.last_updated_by,
m.last_update_login,
m.program_application_id,
m.program_id,
sysdate
FROM ra_customer_merges m
WHERE m.request_id = req_id
AND m.set_number = set_num
AND m.process_flag = 'Y'
AND mh.customer_merge_header_id =
m.customer_merge_header_id
AND ROWNUM = 1)
WHERE mh.customer_merge_header_id IN (
SELECT m.customer_merge_header_id
FROM ra_customer_merges m
WHERE m.request_id = req_id
AND m.process_flag = 'Y'
AND m.set_number = set_num );
| Jianying Huang 07-JUN-01 Should not update set_number to NULL when
| update ra_customer_merges. Commented the statement
| out.
|
+===========================================================================*/
PROCEDURE reset_merge_rows (
req_id NUMBER,
set_num NUMBER,
p_process_flag VARCHAR2
) IS
BEGIN
--arp_message.set_line( 'ARP_CMERGE_MASTER.reset_merge_rows()+' );
UPDATE ra_customer_merges
SET
-- set_number = null,
-- request_id = null,
process_flag = 'SAVED',
last_update_date = sysdate,
last_updated_by = hz_utility_v2pub.user_id,
last_update_login = hz_utility_v2pub.last_update_login,
program_application_id = hz_utility_v2pub.program_application_id,
program_id = hz_utility_v2pub.program_id,
program_update_date = sysdate
WHERE request_id = req_id
AND set_number = set_num
AND process_flag = p_process_flag;
UPDATE ra_customer_merge_headers mh
SET (process_flag,
last_update_date,
last_updated_by,
last_update_login,
program_application_id,
program_id,
program_update_date) = (
SELECT
m.process_flag,
sysdate,
m.last_updated_by,
m.last_update_login,
m.program_application_id,
m.program_id,
sysdate
FROM ra_customer_merges m
WHERE m.request_id = req_id
AND m.set_number = set_num
AND m.process_flag = 'SAVED'
AND mh.customer_merge_header_id =
m.customer_merge_header_id
AND ROWNUM = 1)
WHERE mh.customer_merge_header_id IN (
SELECT m.customer_merge_header_id
FROM ra_customer_merges m
WHERE m.request_id = req_id
AND m.process_flag = 'SAVED'
AND m.set_number = set_num );
UPDATE ra_customer_merges
SET
-- set_number = null,
-- request_id = null,
process_flag = 'FAILED',
last_update_date = sysdate,
last_updated_by = hz_utility_v2pub.user_id,
last_update_login = hz_utility_v2pub.last_update_login,
program_application_id = hz_utility_v2pub.program_application_id,
program_id = hz_utility_v2pub.program_id,
program_update_date = sysdate
WHERE request_id = req_id
AND set_number = set_num
AND process_flag = p_process_flag;
UPDATE ra_customer_merge_headers mh
SET (process_flag,
last_update_date,
last_updated_by,
last_update_login,
program_application_id,
program_id,
program_update_date) = (
SELECT
m.process_flag,
sysdate,
m.last_updated_by,
m.last_update_login,
m.program_application_id,
m.program_id,
sysdate
FROM ra_customer_merges m
WHERE m.request_id = req_id
AND m.set_number = set_num
AND m.process_flag = 'FAILED'
AND mh.customer_merge_header_id =
m.customer_merge_header_id
AND ROWNUM = 1)
WHERE mh.customer_merge_header_id IN (
SELECT m.customer_merge_header_id
FROM ra_customer_merges m
WHERE m.request_id = req_id
AND m.process_flag = 'FAILED'
AND m.set_number = set_num );
UPDATE ra_customer_merge_headers
SET process_flag = decode(process_flag,
'ERROR 1', 'FAILED',
'ERROR 2', 'FAILED'),
-- request_id = null,
last_update_date = sysdate,
last_updated_by = hz_utility_v2pub.user_id,
last_update_login = hz_utility_v2pub.last_update_login,
program_application_id = hz_utility_v2pub.program_application_id,
program_id = hz_utility_v2pub.program_id,
program_update_date = sysdate
WHERE process_flag in ('ERROR 1', 'ERROR 2')
AND request_id = req_id;
UPDATE ra_customer_merges m
SET (process_flag,
last_update_date,
last_updated_by,
last_update_login,
program_application_id,
program_id,
program_update_date) = (
SELECT
mh.process_flag,
sysdate,
mh.last_updated_by,
mh.last_update_login,
mh.program_application_id,
mh.program_id,
sysdate
FROM ra_customer_merge_headers mh
WHERE mh.request_id = req_id
AND mh.process_flag = 'FAILED'
AND mh.customer_merge_header_id =
m.customer_merge_header_id
AND ROWNUM = 1)
WHERE m.customer_merge_header_id IN (
SELECT mh.customer_merge_header_id
FROM ra_customer_merge_headers mh
WHERE mh.request_id = req_id
AND mh.process_flag = 'FAILED' )
AND process_flag in ('ERROR 1', 'ERROR 2');
DELETE FROM ar_conc_process_requests
WHERE request_id = req_id;
| delete_customer_alt_names
|
| DESCRIPTION
| Deletes customer alternative names.
|
| SCOPE - PRIVATE
|
| EXETERNAL PROCEDURES/FUNCTIONS ACCESSED
|
| ARGUMENTS : IN:
| req_id
| set_num
| IN/ OUT:
|
| RETURNS : NONE
|
| NOTES
|
| MODIFICATION HISTORY
|
+===========================================================================*/
PROCEDURE delete_customer_alt_names(
req_id NUMBER,
set_num NUMBER
) IS
v_prev_duplicate_id NUMBER := null ;
SELECT duplicate_id , customer_id , duplicate_site_id
FROM ra_customer_merges
WHERE request_id = req_id
AND set_number = set_num
AND process_flag = 'N'
ORDER BY duplicate_id ;
arp_cust_alt_match_pkg.delete_match ( v_duplicate_id, NULL, NULL );
arp_cust_alt_match_pkg.delete_match (
v_duplicate_id, v_duplicate_site_id, NULL );
arp_message.set_error( 'ARP_CMERGE_MASTER.delete_customer_alt_names' );
END delete_customer_alt_names;
'SELECT type, old_id, new_id ' ||
'FROM ' || arp_cmerge_arcus.g_table_name || ';' ||
| veto_delete
|
| DESCRIPTION
| For preventing the delete of accounts and other records off accounts
|
|
| SCOPE - PUBLIC
|
| EXETERNAL PROCEDURES/FUNCTIONS ACCESSED
|
| ARGUMENTS : IN: req_id NUMBER , set_num NUMBER, from_customer_id NUMBER
| veto_reason VARCHAR2
|
| OUT:
| IN/ OUT:
|
| RETURNS : NONE
|
| NOTES
|
| MODIFICATION HISTORY -
| Jyoti Pandey 02-10-2002 Created.
|
+===========================================================================*/
PROCEDURE veto_delete(req_id NUMBER,
set_num NUMBER,
from_customer_id NUMBER ,
veto_reason VARCHAR2,
part_delete VARCHAR2 DEFAULT 'N') IS
BEGIN
arp_message.set_line( 'ARP_CMERGE_MASTER.Veto_Delete()+' );
/*--Unset the delete_duplicate_flag in ra_customer_merges --*/
UPDATE ra_customer_merges m
SET delete_duplicate_flag = 'N'
WHERE m.duplicate_id = from_customer_id
AND m.process_flag = 'N'
AND m.request_id = req_id
AND m.set_number = set_num
AND part_delete = 'N'; --5747129
/*--Also unset the delete duplicate flag in merge header table --*/
UPDATE ra_customer_merge_headers
SET delete_duplicate_flag = 'N'
WHERE customer_merge_header_id in
(select customer_merge_header_id
from ra_customer_merges m
where m.duplicate_id = from_customer_id
AND m.process_flag = 'N'
AND m.request_id = req_id
AND m.set_number = set_num)
AND part_delete = 'N'; --5747129
UPDATE HZ_CUST_SITE_USES_ALL su --SSUptake
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 EXISTS
( select 'Y'
from hz_cust_acct_sites_ALL site,ra_customer_merges m --SSUptake
where site.cust_account_id = from_customer_id
and m.duplicate_address_id = site.cust_acct_site_id
and su.cust_acct_site_id = site.cust_acct_site_id
and m.request_id = req_id
and m.process_flag = 'N'
and m.set_number = set_num
and m.duplicate_id = from_customer_id
and m.org_id = site.org_id --SSUptake
and su.org_id = site.org_id --SSUptake
)
AND status = 'D'
AND part_delete = 'N'; --5747129
arp_message.set_line(SQL%ROWCOUNT||' '||'Row(s) updated in HZ_CUST_SITE_USES');
UPDATE HZ_CUST_ACCT_SITES_ALL addr --SSUptake
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 addr.cust_account_id = from_customer_id
AND EXISTS (select 'Y' from ra_customer_merges m
where m.request_id = req_id
and m.process_flag = 'N'
and m.set_number = set_num
and m.duplicate_id = from_customer_id
and m.duplicate_address_id = addr.cust_acct_site_id
and m.org_id = addr.org_id --SSUptake
)
AND addr.status = 'D'
AND part_delete = 'N'; --5747129
arp_message.set_line(SQL%ROWCOUNT||' '||'Row(s) updated in HZ_CUST_ACCT_SITES');
UPDATE HZ_CUST_ACCOUNTS acct
set status = decode(part_delete,'N','I','A'),
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 acct.cust_account_id = from_customer_id
and status ='D';
arp_message.set_line(SQL%ROWCOUNT||' '|| 'Row(s) updated in HZ_CUST_ACCOUNTS');
UPDATE HZ_CUST_ACCT_RELATE_ALL rel
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 rel.cust_account_id = from_customer_id
AND EXISTS (select 'Y' from ra_customer_merges m
where m.request_id = req_id
and m.process_flag = 'N'
and m.set_number = set_num
and m.duplicate_id = rel.cust_account_id
and m.org_id = rel.org_id --SSUptake
)
AND status ='D'
AND part_delete = 'N'; --5747129
arp_message.set_line( SQL%ROWCOUNT||' '|| 'Row(s) updated in HZ_CUST_ACCT_RELATE for cust_account_id' );
UPDATE HZ_CUST_ACCT_RELATE_ALL rel2 --SSUptake
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 = from_customer_id
AND EXISTS (select 'Y' from ra_customer_merges m
where m.request_id = req_id
and m.process_flag = 'N'
and m.set_number = set_num
and m.duplicate_id = rel2.related_cust_account_id
and m.org_id = rel2.org_id --SSUptake
)
AND status ='D'
AND part_delete = 'N'; --5747129
arp_message.set_line( SQL%ROWCOUNT||' '|| 'Rows updated in HZ_CUST_ACCT_RELATE for related_cust_account_id' );
*--delete is prevented by delete_duplicate_flag='N' */
UPDATE RA_CUST_RECEIPT_METHODS yt
set end_date = null,
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 customer_id = from_customer_id
AND 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_line(SQL%ROWCOUNT||' '|| 'Row(s) updated in RA_CUST_RECEIPT_METHODS');
UPDATE hz_customer_profiles 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 yt.cust_account_id = from_customer_id
AND status = 'D'
AND part_delete = 'N'; --5747129
UPDATE hz_customer_profiles yt --5634398
SET status = 'A',
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 part_delete = 'Y'
AND status = 'D'
AND yt.cust_account_id = from_customer_id
AND site_use_id is NULL ;
arp_message.set_line(SQL%ROWCOUNT||' '|| 'Row(s) updated in HZ_CUSTOMER_PROFILES');
UPDATE RA_CUSTOMER_MERGE_HEADERS
SET MERGE_FAIL_MSG= veto_reason
WHERE customer_merge_header_id in
(select customer_merge_header_id
from ra_customer_merges m
where m.duplicate_id = from_customer_id
AND m.process_flag = 'N'
AND m.request_id = req_id
AND m.set_number = set_num);
arp_message.set_line( SQL%ROWCOUNT || ' '|| 'Rows updated in RA_CUSTOMER_MERGE_HEADERS with veto reason :' ||' '|| veto_reason );
arp_message.set_line( 'ARP_CMERGE_MASTER.Veto_Delete()-' );
END veto_delete;
SELECT customer_merge_header_id
FROM ra_customer_merge_headers
WHERE request_id = p_req_id
AND process_flag = 'Y';
l_list.DELETE;