[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT DISTINCT customer_merge_header_id
,trxnmid
,cust_account_id
,acct_site_id
,acct_site_use_id
FROM iby_trxn_summaries_all yt, ra_customer_merges m
WHERE (
yt.cust_account_id = m.duplicate_id
OR yt.acct_site_id = m.duplicate_address_id
OR yt.acct_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,
table_name,
merge_header_id,
primary_key_id,
num_col1_orig,
num_col1_new,
num_col2_orig,
num_col2_new,
num_col3_orig,
num_col3_new,
action_flag,
request_id,
created_by,
creation_date,
last_update_login,
last_update_date,
last_updated_by
) VALUES ( hz_customer_merge_log_s.nextval,
'IBY_TRXN_SUMMARIES_ALL',
merge_header_id_list(i),
primary_key_id_list(i),
num_col1_orig_list(i),
num_col1_new_list(i),
num_col2_orig_list(i),
num_col2_new_list(i),
num_col3_orig_list(i),
num_col3_new_list(i),
'U',
req_id,
hz_utility_pub.created_by,
hz_utility_pub.creation_date,
hz_utility_pub.last_update_login,
hz_utility_pub.last_update_date,
hz_utility_pub.last_updated_by
);
Update the Payement Instrument Assignment Id with the new value
From the Payment Instrument Use Id, get the Instrument Id
Check if the same instrument id exists for the payer of the
target account.If exists, update the record with the new Payment Instrument Use Id
*/
FORALL i in 1..ext_payer_from_list.COUNT
UPDATE iby_trxn_summaries_all DUP
SET payer_instr_assignment_id =
(SELECT to_instr.instrument_payment_use_id FROM
iby_pmt_instr_uses_all from_inst,
iby_pmt_instr_uses_all to_instr
WHERE from_inst.instrument_payment_use_id = dup.payer_instr_assignment_id
AND to_instr.ext_pmt_party_id = ext_payer_to_list(i)
AND to_instr.instrument_id = from_inst.instrument_id
AND from_inst.ext_pmt_party_id = ext_payer_from_list(i)
AND to_instr.end_date > sysdate)
WHERE payer_instr_assignment_id IS NOT NULL
AND ext_payer_from_list(i) IS NOT NULL
AND ext_payer_to_list(i) IS NOT NULL
AND reqtype IN ('ORAPMTREQ','ORAPMTBATCHREQ')
AND status <> 0
AND EXISTS
(
SELECT to_instr.instrument_payment_use_id FROM
iby_pmt_instr_uses_all from_inst,
iby_pmt_instr_uses_all to_instr
WHERE from_inst.instrument_payment_use_id = dup.payer_instr_assignment_id
AND to_instr.ext_pmt_party_id = ext_payer_to_list(i)
AND to_instr.instrument_id = from_inst.instrument_id
AND from_inst.ext_pmt_party_id = ext_payer_from_list(i)
AND to_instr.end_date > sysdate
);
If exists, update the record with the new Payment Instrument Use Id
*/
FORALL i in 1..ext_payer_from_list.COUNT
UPDATE iby_trxn_summaries_all dup
SET payer_instr_assignment_id =
(SELECT to_uses.instrument_payment_use_id FROM
iby_pmt_instr_uses_all from_uses,
iby_pmt_instr_uses_all to_uses,
iby_creditcard from_cards,
iby_creditcard to_cards
WHERE from_uses.instrument_payment_use_id = dup.payer_instr_assignment_id
AND from_uses.instrument_id = from_cards.instrid
AND to_cards.cc_number_hash1 = from_cards.cc_number_hash1
AND to_cards.cc_number_hash2 = from_cards.cc_number_hash2
AND to_uses.instrument_id = to_cards.instrid
AND to_uses.ext_pmt_party_id = ext_payer_to_list(i)
AND from_uses.ext_pmt_party_id = ext_payer_from_list(i))
WHERE payer_instr_assignment_id IS NOT NULL
AND reqtype in ('ORAPMTREQ','ORAPMTBATCHREQ')
AND status <> 0
AND ext_payer_from_list(i) is not null
AND ext_payer_to_list(i) is not null
AND EXISTS
(
SELECT To_uses.instrument_payment_use_id FROM
iby_pmt_instr_uses_all from_uses,
iby_pmt_instr_uses_all to_uses,
iby_creditcard from_cards,
iby_creditcard to_cards
WHERE from_uses.instrument_payment_use_id = dup.payer_instr_assignment_id
AND from_uses.instrument_type = 'CREDITCARD'
AND from_uses.instrument_id = from_cards.instrid
AND to_cards.cc_number_hash1 = from_cards.cc_number_hash1
AND to_cards.cc_number_hash2 = from_cards.cc_number_hash2
AND to_uses.instrument_id = to_cards.instrid
AND to_uses.ext_pmt_party_id = ext_payer_to_list(i)
AND from_uses.ext_pmt_party_id = ext_payer_from_list(i)
) ;
If exists, update the record with the new Payment Instrument Use Id
*/
FORALL i in 1..ext_payer_from_list.COUNT
UPDATE iby_trxn_summaries_all dup
SET payer_instr_assignment_id =
(
SELECT To_uses.instrument_payment_use_id FROM
iby_pmt_instr_uses_all from_uses,
iby_pmt_instr_uses_all to_uses,
iby_ext_bank_accounts from_accounts,
iby_ext_bank_accounts to_accounts
WHERE from_uses.instrument_payment_use_id = dup.payer_instr_assignment_id
AND from_uses.instrument_type = 'BANKACCOUNT'
AND from_uses.instrument_id = from_accounts.ext_bank_account_id
AND to_accounts.iban_hash1 = from_accounts.iban_hash1
AND to_accounts.iban_hash2 = from_accounts.iban_hash2
AND to_uses.instrument_id = to_accounts.ext_bank_account_id
AND to_uses.ext_pmt_party_id = ext_payer_to_list(i)
AND from_uses.ext_pmt_party_id = ext_payer_from_list(i)
)
WHERE payer_instr_assignment_id IS NOT NULL
AND reqtype in ('ORAPMTREQ','ORAPMTBATCHREQ')
AND status <> 0
AND ext_payer_from_list(i) is not null
AND ext_payer_to_list(i) is not null
AND EXISTS
(
SELECT To_uses.instrument_payment_use_id FROM
iby_pmt_instr_uses_all from_uses,
iby_pmt_instr_uses_all to_uses,
iby_ext_bank_accounts from_accounts,
iby_ext_bank_accounts to_accounts
WHERE from_uses.instrument_payment_use_id = dup.payer_instr_assignment_id
AND from_uses.instrument_type = 'BANKACCOUNT'
AND from_uses.instrument_id = from_accounts.ext_bank_account_id
AND to_accounts.iban_hash1 = from_accounts.iban_hash1
AND to_accounts.iban_hash2 = from_accounts.iban_hash2
AND to_uses.instrument_id = to_accounts.ext_bank_account_id
AND to_uses.ext_pmt_party_id = ext_payer_to_list(i)
AND from_uses.ext_pmt_party_id = ext_payer_from_list(i)
) ;
Update the Cust Account and Site Use with the new values
*/
FORALL i in 1..merge_header_id_list.COUNT
UPDATE iby_trxn_summaries_all yt SET
cust_account_id=num_col1_new_list(i)
,acct_site_id=num_col2_new_list(i)
,acct_site_use_id=num_col3_new_list(i)
, last_update_date=SYSDATE
, last_updated_by=arp_standard.profile.user_id
, last_update_login=arp_standard.profile.last_update_login
WHERE trxnmid=primary_key_id_list(i)
AND reqtype IN ('ORAPMTREQ','ORAPMTBATCHREQ')
AND status <> 0
;
arp_message.set_name('AR','AR_ROWS_UPDATED');
UPDATE IBY_FNDCPT_TX_EXTENSIONS DUP
SET instr_assignment_id =
(SELECT to_instr.instrument_payment_use_id FROM
iby_pmt_instr_uses_all from_inst,
iby_pmt_instr_uses_all to_instr
WHERE from_inst.instrument_payment_use_id = dup.instr_assignment_id
AND to_instr.ext_pmt_party_id = ext_payer_to_list(i)
AND to_instr.instrument_id = from_inst.instrument_id
AND to_instr.end_date > sysdate)
WHERE ext_payer_id = ext_payer_from_list(i)
AND ext_payer_from_list(i) IS NOT NULL
AND ext_payer_to_list(i) IS NOT NULL
AND instr_assignment_id IS NOT NULL
AND ( EXISTS (
SELECT 1
FROM iby_trxn_summaries_all s
,iby_fndcpt_tx_operations o
WHERE o.trxn_extension_id = dup.trxn_extension_id
AND s.transactionid = o.transactionid
AND s.reqtype IN ('ORAPMTREQ','ORAPMTBATCHREQ')
AND s.status <> 0 )
OR NOT EXISTS
(
Select 1 from iby_fndcpt_tx_operations o
where o.trxn_extension_id = dup.trxn_extension_id
)
)
AND EXISTS
(
SELECT to_instr.instrument_payment_use_id FROM
iby_pmt_instr_uses_all from_inst,
iby_pmt_instr_uses_all to_instr
WHERE from_inst.instrument_payment_use_id = dup.instr_assignment_id
AND to_instr.ext_pmt_party_id = ext_payer_to_list(i)
AND to_instr.instrument_id = from_inst.instrument_id
AND to_instr.end_date > sysdate
);
UPDATE iby_fndcpt_tx_extensions dup
SET instr_assignment_id =
(SELECT to_uses.instrument_payment_use_id FROM
iby_pmt_instr_uses_all from_uses,
iby_pmt_instr_uses_all to_uses,
iby_creditcard from_cards,
iby_creditcard to_cards
WHERE from_uses.instrument_payment_use_id = dup.instr_assignment_id
AND from_uses.instrument_id = from_cards.instrid
AND to_cards.cc_number_hash1 = from_cards.cc_number_hash1
AND to_cards.cc_number_hash2 = from_cards.cc_number_hash2
AND to_uses.instrument_id = to_cards.instrid
AND to_uses.ext_pmt_party_id = ext_payer_to_list(i) )
WHERE ext_payer_id = ext_payer_from_list(i)
AND ext_payer_from_list(i) IS NOT NULL
AND ext_payer_to_list(i) IS NOT NULL
AND instr_assignment_id IS NOT NULL
AND ( EXISTS (
SELECT 1
FROM iby_trxn_summaries_all s
,iby_fndcpt_tx_operations o
WHERE o.trxn_extension_id = dup.trxn_extension_id
AND s.transactionid = o.transactionid
AND s.reqtype IN ('ORAPMTREQ','ORAPMTBATCHREQ')
AND s.status <> 0 )
OR NOT EXISTS
(
Select 1 from iby_fndcpt_tx_operations o
where o.trxn_extension_id = dup.trxn_extension_id
)
)
AND EXISTS
(
SELECT to_uses.instrument_payment_use_id FROM
iby_pmt_instr_uses_all from_uses,
iby_pmt_instr_uses_all to_uses,
iby_creditcard from_cards,
iby_creditcard to_cards
WHERE from_uses.instrument_payment_use_id = dup.instr_assignment_id
AND from_uses.instrument_type = 'CREDITCARD'
AND from_uses.instrument_id = from_cards.instrid
AND to_cards.cc_number_hash1 = from_cards.cc_number_hash1
AND to_cards.cc_number_hash2 = from_cards.cc_number_hash2
AND to_uses.instrument_id = to_cards.instrid
AND to_uses.ext_pmt_party_id = ext_payer_to_list(i)
) ; */
UPDATE iby_fndcpt_tx_extensions dup
SET instr_assignment_id =
(SELECT to_uses.instrument_payment_use_id FROM
iby_pmt_instr_uses_all from_uses,
iby_pmt_instr_uses_all to_uses,
iby_ext_bank_accounts from_accounts,
iby_ext_bank_accounts to_accounts
WHERE from_uses.instrument_payment_use_id = dup.instr_assignment_id
AND from_uses.instrument_type = 'BANKACCOUNT'
AND from_uses.instrument_id = from_accounts.ext_bank_account_id
AND to_accounts.iban_hash1 = from_accounts.iban_hash1
AND to_accounts.iban_hash2 = from_accounts.iban_hash2
AND to_uses.instrument_id = to_accounts.ext_bank_account_id
AND to_uses.ext_pmt_party_id = ext_payer_to_list(i) )
WHERE ext_payer_id = ext_payer_from_list(i)
AND ext_payer_from_list(i) IS NOT NULL
AND ext_payer_to_list(i) IS NOT NULL
AND instr_assignment_id IS NOT NULL
AND ( EXISTS (
SELECT 1
FROM iby_trxn_summaries_all s
,iby_fndcpt_tx_operations o
WHERE o.trxn_extension_id = dup.trxn_extension_id
AND s.transactionid = o.transactionid
AND s.reqtype IN ('ORAPMTREQ','ORAPMTBATCHREQ')
AND s.status <> 0 )
OR NOT EXISTS
(
Select 1 from iby_fndcpt_tx_operations o
where o.trxn_extension_id = dup.trxn_extension_id
)
)
AND EXISTS
(
SELECT to_uses.instrument_payment_use_id FROM
iby_pmt_instr_uses_all from_uses,
iby_pmt_instr_uses_all to_uses,
iby_ext_bank_accounts from_accounts,
iby_ext_bank_accounts to_accounts
WHERE from_uses.instrument_payment_use_id = dup.instr_assignment_id
AND from_uses.instrument_type = 'BANKACCOUNT'
AND from_uses.instrument_id = from_accounts.ext_bank_account_id
AND to_accounts.iban_hash1 = from_accounts.iban_hash1
AND to_accounts.iban_hash2 = from_accounts.iban_hash2
AND to_uses.instrument_id = to_accounts.ext_bank_account_id
AND to_uses.ext_pmt_party_id = ext_payer_to_list(i)
) ; */
/* Update the Payer with the new value */
iby_debug_pub.Add('Modifying the Payer value');
UPDATE iby_fndcpt_tx_extensions dup
SET ext_payer_id = ext_payer_to_list(i)
WHERE ext_payer_id = ext_payer_from_list(i)
AND ext_payer_from_list(i) IS NOT NULL
AND ext_payer_to_list(i) IS NOT NULL
AND
( EXISTS (
SELECT 1
FROM iby_trxn_summaries_all s
,iby_fndcpt_tx_operations o
WHERE o.trxn_extension_id = dup.trxn_extension_id
AND s.transactionid = o.transactionid
AND s.reqtype IN ('ORAPMTREQ','ORAPMTBATCHREQ')
AND s.status <> 0 )
OR NOT EXISTS
(
Select 1 from iby_fndcpt_tx_operations o
where o.trxn_extension_id = dup.trxn_extension_id
)
);
UPDATE iby_ext_party_pmt_mthds dup
SET ext_pmt_party_id = ext_payer_to_list(i),
last_update_date = SYSDATE,
last_updated_by = arp_standard.profile.user_id,
last_update_login = arp_standard.profile.last_update_login
WHERE ext_pmt_party_id = ext_payer_from_list(i)
AND ext_payer_from_list(i) IS NOT NULL
AND ext_payer_to_list(i) IS NOT NULL
AND NOT EXISTS
(
SELECT ext_party_pmt_mthd_id
FROM iby_ext_party_pmt_mthds mto
WHERE mto.ext_pmt_party_id = ext_payer_to_list(i)
AND mto.payment_method_code = dup.payment_method_code
AND mto.payment_flow = dup.payment_flow
AND mto.payment_function = dup.payment_function
);
UPDATE iby_ext_party_pmt_mthds dup
SET inactive_date = SYSDATE,
last_update_date = SYSDATE,
last_updated_by = arp_standard.profile.user_id,
last_update_login = arp_standard.profile.last_update_login
WHERE ext_pmt_party_id = ext_payer_from_list(i)
AND ext_payer_from_list(i) IS NOT NULL
AND ext_payer_to_list(i) IS NOT NULL
AND EXISTS
(
SELECT ext_party_pmt_mthd_id
FROM iby_ext_party_pmt_mthds mto
WHERE mto.ext_pmt_party_id = ext_payer_to_list(i)
AND mto.payment_method_code = dup.payment_method_code
AND mto.payment_flow = dup.payment_flow
AND mto.payment_function = dup.payment_function
);
UPDATE iby_pmt_instr_uses_all dup
SET ext_pmt_party_id = ext_payer_to_list(i),
last_update_date = SYSDATE,
last_updated_by = arp_standard.profile.user_id,
last_update_login = arp_standard.profile.last_update_login
WHERE ext_pmt_party_id = ext_payer_from_list(i)
AND ext_payer_from_list(i) IS NOT NULL
AND ext_payer_to_list(i) IS NOT NULL
AND payment_flow = 'FUNDS_CAPTURE'
AND payment_function = 'CUSTOMER_PAYMENT'
AND NOT EXISTS
(
SELECT instrument_id
FROM iby_pmt_instr_uses_all mto
WHERE mto.ext_pmt_party_id = ext_payer_to_list(i)
AND mto.instrument_id = dup.instrument_id
AND mto.payment_flow = dup.payment_flow
AND mto.payment_function = dup.payment_function
AND mto.instrument_type = dup.instrument_type
);
UPDATE iby_pmt_instr_uses_all dup
SET ext_pmt_party_id = ext_payer_to_list(i),
last_update_date = SYSDATE,
last_updated_by = arp_standard.profile.user_id,
last_update_login = arp_standard.profile.last_update_login
WHERE ext_pmt_party_id = ext_payer_from_list(i)
AND ext_payer_from_list(i) IS NOT NULL
AND ext_payer_to_list(i) IS NOT NULL
AND dup.instrument_type = 'CREDITCARD'
AND payment_flow = 'FUNDS_CAPTURE'
AND payment_function = 'CUSTOMER_PAYMENT'
AND NOT EXISTS
(
SELECT 1 FROM
iby_creditcard from_card,
iby_creditcard to_card,
iby_pmt_instr_uses_all uses
WHERE from_card.instrid = dup.instrument_id
AND to_card.cc_number_hash1 = from_card.cc_number_hash1
AND to_card.cc_number_hash2 = from_card.cc_number_hash2
AND to_card.instrid = uses.instrument_id
AND uses.ext_pmt_party_id = ext_payer_to_list(i)
);
UPDATE iby_pmt_instr_uses_all dup
SET ext_pmt_party_id = ext_payer_to_list(i),
last_update_date = SYSDATE,
last_updated_by = arp_standard.profile.user_id,
last_update_login = arp_standard.profile.last_update_login
WHERE ext_pmt_party_id = ext_payer_from_list(i)
AND ext_payer_from_list(i) IS NOT NULL
AND ext_payer_to_list(i) IS NOT NULL
AND dup.instrument_type = 'BANKACCOUNT'
AND payment_flow = 'FUNDS_CAPTURE'
AND payment_function = 'CUSTOMER_PAYMENT'
AND NOT EXISTS
(
SELECT 1 FROM
iby_ext_bank_accounts from_account,
iby_ext_bank_accounts to_account,
iby_pmt_instr_uses_all uses
WHERE from_account.ext_bank_account_id = dup.instrument_id
AND to_account.iban_hash1 = from_account.iban_hash1
AND to_account.iban_hash2 = from_account.iban_hash2
AND to_account.ext_bank_account_id = uses.instrument_id
AND uses.ext_pmt_party_id = ext_payer_to_list(i)
);
UPDATE iby_pmt_instr_uses_all dup
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
WHERE ext_pmt_party_id = ext_payer_from_list(i)
AND ext_payer_from_list(i) IS NOT NULL
AND ext_payer_to_list(i) IS NOT NULL
AND payment_flow = 'FUNDS_CAPTURE'
AND payment_function = 'CUSTOMER_PAYMENT'
AND EXISTS
(
SELECT instrument_id
FROM iby_pmt_instr_uses_all mto
WHERE mto.ext_pmt_party_id = ext_payer_to_list(i)
AND mto.instrument_id = dup.instrument_id
AND mto.payment_flow = dup.payment_flow
AND mto.payment_function = dup.payment_function
AND mto.instrument_type = dup.instrument_type
);
UPDATE iby_pmt_instr_uses_all dup
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
WHERE ext_pmt_party_id = ext_payer_from_list(i)
AND ext_payer_from_list(i) IS NOT NULL
AND ext_payer_to_list(i) IS NOT NULL
AND payment_flow = 'FUNDS_CAPTURE'
AND payment_function = 'CUSTOMER_PAYMENT'
AND dup.instrument_type = 'CREDITCARD'
AND EXISTS
(
SELECT 1 FROM
iby_creditcard from_card,
iby_creditcard to_card,
iby_pmt_instr_uses_all uses
WHERE from_card.instrid = dup.instrument_id
AND to_card.cc_number_hash1 = from_card.cc_number_hash1
AND to_card.cc_number_hash2 = from_card.cc_number_hash2
AND to_card.instrid = uses.instrument_id
AND uses.ext_pmt_party_id = ext_payer_to_list(i)
);
UPDATE iby_pmt_instr_uses_all dup
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
WHERE ext_pmt_party_id = ext_payer_from_list(i)
AND ext_payer_from_list(i) IS NOT NULL
AND ext_payer_to_list(i) IS NOT NULL
AND payment_flow = 'FUNDS_CAPTURE'
AND payment_function = 'CUSTOMER_PAYMENT'
AND dup.instrument_type = 'BANKACCOUNT'
AND EXISTS
(
SELECT 1 FROM
iby_ext_bank_accounts from_account,
iby_ext_bank_accounts to_account,
iby_pmt_instr_uses_all uses
WHERE from_account.ext_bank_account_id = dup.instrument_id
AND to_account.iban_hash1 = from_account.iban_hash1
AND to_account.iban_hash2 = from_account.iban_hash2
AND to_account.ext_bank_account_id = uses.instrument_id
AND uses.ext_pmt_party_id = ext_payer_to_list(i)
);
SELECT DISTINCT customer_merge_header_id
,ext_payer_id
,cust_account_id
,acct_site_use_id
FROM iby_external_payers_all yt, ra_customer_merges m
WHERE (
( yt.cust_account_id = m.duplicate_id
AND yt.acct_site_use_id is null
AND m.customer_id <> m.duplicate_id )
OR
( yt.cust_account_id = m.duplicate_id
AND yt.acct_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,
table_name,
merge_header_id,
primary_key_id,
num_col1_orig,
num_col1_new,
num_col2_orig,
num_col2_new,
action_flag,
request_id,
created_by,
creation_date,
last_update_login,
last_update_date,
last_updated_by
) VALUES ( hz_customer_merge_log_s.nextval,
'IBY_EXTERNAL_PAYERS_ALL',
merge_header_id_list(i),
primary_key_id_list(i),
num_col1_orig_list(i),
num_col1_new_list(i),
num_col2_orig_list(i),
num_col2_new_list(i),
'U',
req_id,
hz_utility_pub.created_by,
hz_utility_pub.creation_date,
hz_utility_pub.last_update_login,
hz_utility_pub.last_update_date,
hz_utility_pub.last_updated_by
);
iby_debug_pub.Add( 'checking if a site is already updated');
SELECT count(ext_payer_id) into l_payer_count
FROM IBY_EXTERNAL_PAYERS_ALL
WHERE cust_account_id = num_col1_new_list(i)
AND acct_site_use_id = num_col2_new_list(i)
AND ext_payer_from_list(i) is not null
AND ext_payer_to_list(i) is null;
SELECT ext_payer_id into l_dup_payer
FROM IBY_EXTERNAL_PAYERS_ALL
WHERE cust_account_id = num_col1_new_list(i)
AND acct_site_use_id = num_col2_new_list(i)
AND ext_payer_from_list(i) is not null
AND ext_payer_to_list(i) is null;
iby_debug_pub.Add( 'After checking of a site already updated');
UPDATE IBY_EXTERNAL_PAYERS_ALL yt SET
cust_account_id=num_col1_new_list(i)
,acct_site_use_id=num_col2_new_list(i)
, last_update_date=SYSDATE
, last_updated_by=arp_standard.profile.user_id
, last_update_login=arp_standard.profile.last_update_login
WHERE ext_payer_id=primary_key_id_list(i)
AND primary_key_id_list(i) = ext_payer_from_list(i)
AND ext_payer_from_list(i) is not null
AND ext_payer_to_list(i) is null;
arp_message.set_name('AR','AR_ROWS_UPDATED');