The following lines contain the word 'select', 'insert', 'update' or 'delete':
PROCEDURE insert_p(p_cc_error_hist IN ar_cc_error_history%ROWTYPE);
SELECT prof.collector_id INTO l_collector_id
FROM hz_customer_profiles prof
WHERE prof.cust_account_id = p_customer_id AND
prof.site_use_id = p_customer_site_use_id;
SELECT name
INTO l_collector_name
FROM ar_collectors
WHERE collector_id = l_collector_id;
SELECT prof.collector_id INTO l_collector_id
FROM hz_customer_profiles prof
WHERE prof.cust_account_id = p_customer_id AND
prof.site_use_id is NULL;
SELECT name
INTO l_collector_name
FROM ar_collectors
WHERE collector_id = l_collector_id;
SELECT 'Y'
INTO l_return
FROM dual
WHERE EXISTS (SELECT 1
FROM ar_cc_error_mappings
WHERE cc_error_code = p_cc_error_code
AND cc_trx_category = p_cc_trx_category
AND receipt_method_id = p_receipt_method_id);
SELECT cc_action_code,
cc_error_text,
no_days,
subsequent_action_code,
error_notes
INTO l_cc_action_code,
x_cc_error_desc,
l_no_days,
l_subsequent_action_code,
x_error_notes
FROM ar_cc_error_mappings
WHERE cc_error_code = p_cc_error_code
AND cc_trx_category = p_cc_trx_category
AND receipt_method_id = p_receipt_method_id;
SELECT 1
INTO l_dummy_number
FROM dual
WHERE NOT EXISTS ( SELECT 1
FROM ar_cc_error_history
WHERE cc_trx_id = p_cc_trx_id
AND cc_trx_category = p_cc_trx_category
AND customer_bank_account_id = p_customer_bank_account_id
AND cc_error_code = p_cc_error_code
AND cc_action_type_flag = 'S' );
SELECT cc_error_date
INTO l_first_cc_error_date
FROM ar_cc_error_history
WHERE cc_trx_id = p_cc_trx_id
AND cc_trx_category = p_cc_trx_category
AND customer_bank_account_id = p_customer_bank_account_id
AND cc_error_code = p_cc_error_code
AND first_record_flag = 'Y';
| insert_p
|
| DESCRIPTION
| Inserts the record into ar_cc_error_history
|
|
| SCOPE - PRIVATE
|
|
| ARGUMENTS : IN:
| p_cc_error_hist - record variable containing data to be inserted
| OUT: NONE
|
|
| RETURNS : None
|
| NOTES
|
| MODIFICATION HISTORY - Created by Srinivasa Kini - 25-Aug-2004
|
+===========================================================================*/
PROCEDURE insert_p(p_cc_error_hist IN ar_cc_error_history%ROWTYPE) IS
BEGIN
IF PG_DEBUG in ('Y', 'C') THEN
arp_standard.debug( 'ARP_CORRECT_CC_ERRORS.insert_p()+' );
INSERT INTO ar_cc_error_history
(
request_id,
cc_trx_category,
cc_trx_id,
cc_error_code,
cc_error_date,
cc_vendor_error_desc,
cc_error_text,
cc_action_code,
cc_action_type_flag,
payment_server_order_num,
customer_bank_account_id,
first_record_flag,
current_record_flag,
last_update_date,
creation_date,
created_by,
last_update_login,
last_updated_by
)
VALUES
(
p_cc_error_hist.request_id,
p_cc_error_hist.cc_trx_category ,
p_cc_error_hist.cc_trx_id,
p_cc_error_hist.cc_error_code,
sysdate,
p_cc_error_hist.cc_vendor_error_desc,
p_cc_error_hist.cc_error_text,
p_cc_error_hist.cc_action_code,
p_cc_error_hist.cc_action_type_flag,
p_cc_error_hist.payment_server_order_num,
p_cc_error_hist.customer_bank_account_id,
p_cc_error_hist.first_record_flag,
p_cc_error_hist.current_record_flag,
sysdate,
sysdate,
pg_user_id,
NVL(pg_login_id,pg_conc_login_id),
pg_user_id
);
arp_standard.debug( 'ARP_CORRECT_CC_ERRORS.insert_p()-' );
arp_standard.debug( 'EXCEPTION ARP_CORRECT_CC_ERRORS.insert_p()' );
END insert_p;
SELECT NVL(trx.cc_error_code,'Unknown'),
NVL(trx.cc_error_text,'Unknown Error'),
ARPT_SQL_FUNC_UTIL.get_lookup_meaning('CC_TRX_CATEGORY',p_cc_trx_category),
trx.trx_number,
trx.invoice_currency_code,
trx.trx_date,
party.party_name,
cust.account_number,
site_uses.location,
iby.card_number,
iby.card_expirydate,
trx.payment_server_order_num,
trx.approval_code,
rm.name,
ARP_CORRECT_CC_ERRORS.get_collector_name(trx.paying_customer_id,site_uses.site_use_id)
INTO l_cc_error_code,
l_cc_error_desc,
l_cc_trx_category_dsp,
l_cc_trx_number,
l_cc_trx_currency,
l_cc_trx_date,
l_customer_name,
l_customer_number,
l_customer_location,
l_cc_number,
l_expiration_date,
l_payment_server_id,
l_approval_code,
l_collector,
l_payment_method_name
FROM ra_customer_trx trx,
ar_receipt_methods rm,
hz_parties party,
hz_cust_accounts cust,
hz_cust_site_uses site_uses,
iby_trxn_extensions_v iby
WHERE trx.receipt_method_id = rm.receipt_method_id
AND rm.payment_channel_code = 'CREDIT_CARD'
AND trx.paying_customer_id = cust.cust_account_id (+)
AND cust.party_id = party.party_id (+)
AND trx.paying_site_use_id = site_uses.site_use_id (+)
AND iby.trxn_extension_id = trx.payment_trxn_extension_id
AND trx.customer_trx_id = p_customer_trx_id;
that this probably should select amount_due_original instead. Also,
I kept the other where conditions to insure that this code would
still raise an unhandled exception if there was no eligible PS.
My main concern was the status=OP part.. snort! we are reversing
a receipt on a closed transaction! */
SELECT sum(amount_due_remaining), trunc(sysdate) - trunc(max(due_date))
INTO l_cc_trx_amount, l_days_late
FROM ar_payment_schedules
WHERE customer_trx_id = p_customer_trx_id
AND selected_for_receipt_batch_id IS NULL
AND reserved_type IS NULL
AND reserved_value IS NULL;
SELECT substrb(RACO_BILL_PARTY.person_last_name,1,50) ||' ' ||
substrb(RACO_BILL_PARTY.person_first_name,1,50),
substrb(RA_SALES.NAME,1,50)
INTO l_billto_contact,
l_salesrep_name
FROM ra_customer_trx CT,
hz_cust_account_roles RACO_BILL,
hz_parties RACO_BILL_PARTY,
hz_relationships RACO_BILL_REL,
ra_salesreps RA_SALES
WHERE ct.bill_to_contact_id = raco_bill.cust_account_role_id(+)
and raco_bill.party_id = raco_bill_rel.party_id(+)
and raco_bill_rel.subject_table_name(+) = 'HZ_PARTIES'
and raco_bill_rel.object_table_name(+) = 'HZ_PARTIES'
and raco_bill_rel.directional_flag(+) = 'F'
and raco_bill.role_type(+) = 'CONTACT'
and raco_bill_rel.subject_id = raco_bill_party.party_id(+)
and raco_bill_rel.status(+) = 'A'
and ct.primary_salesrep_id = ra_sales.salesrep_id (+)
and ct.customer_trx_id = p_customer_trx_id;
UPDATE ra_customer_trx_all
SET payment_trxn_extension_id = NULL,
receipt_method_id = NULL,
cc_error_flag = NULL,
last_update_date = sysdate,
last_updated_by = pg_user_id,
last_update_login = NVL(pg_login_id,pg_conc_login_id)
WHERE customer_trx_id = p_customer_trx_id;
SELECT max(gl_date)
INTO l_gl_date
FROM ar_cash_receipt_history
WHERE cash_receipt_id = p_cash_receipt_id;
SELECT set_of_books_id
INTO l_sob_id
FROM ar_cash_receipts
WHERE cash_receipt_id = p_cash_receipt_id;
SELECT receipt_date
INTO l_receipt_date
FROM ar_cash_receipts
WHERE cash_receipt_id = p_cash_receipt_id;
l_list.DELETE;
l_list.DELETE;
SELECT name INTO l_org_name
FROM hr_all_organization_units
WHERE organization_id = fnd_profile.value( 'ORG_ID');
SELECT name
FROM wf_events
WHERE name = p_event_name;
INSERT INTO ar_notes
(
note_id,
note_type,
text,
customer_trx_id,
customer_call_id,
customer_call_topic_id,
call_action_id,
last_updated_by,
last_update_date,
last_update_login,
created_by,
creation_date
)
VALUES
(
ar_notes_s.nextval,
'MAINTAIN',
p_text,
p_customer_trx_id,
NULL,
NULL,
NULL,
pg_user_id,
sysdate,
NVL(pg_conc_login_id, pg_login_id),
pg_user_id,
sysdate
);
SELECT 'Y'
INTO l_return_status
FROM dual
WHERE EXISTS (SELECT 1
FROM ar_cash_receipts
WHERE request_id = p_request_id
AND cc_error_flag = 'Y');
SELECT 'Y'
INTO l_return_status
FROM dual
WHERE EXISTS (SELECT 1
FROM ra_customer_trx
WHERE request_id = p_request_id
AND cc_error_flag = 'Y');
Select cash_receipt_id,
type,
receipt_method_id,
customer_bank_account_id,
cc_error_code,
cc_error_text,
payment_server_order_num
FROM ar_cash_receipts
WHERE request_id = p_request_id
AND cc_error_flag = 'Y'
AND cc_error_code IS NOT NULL;
UPDATE ar_cc_error_history
SET current_record_flag = 'N',
last_update_date = sysdate,
last_updated_by = pg_user_id,
last_update_login = NVL(pg_login_id,pg_conc_login_id)
WHERE cc_trx_id = cr_rec.cash_receipt_id
AND cc_trx_category = cr_rec.type
AND customer_bank_account_id = cr_rec.customer_bank_account_id
AND cc_error_code = cr_rec.cc_error_code;
insert_p(l_cc_error_hist);
Select trx.customer_trx_id,
trx_type.type,
trx.receipt_method_id,
trx.customer_bank_account_id,
trx.cc_error_code,
trx.cc_error_text,
trx.payment_server_order_num
FROM ra_customer_trx trx,
ra_cust_trx_types trx_type
WHERE trx.cust_trx_type_id = trx_type.cust_trx_type_id
AND trx.request_id = p_request_id
AND trx.cc_error_flag = 'Y'
AND cc_error_code IS NOT NULL;
UPDATE ar_cc_error_history
SET current_record_flag = 'N',
last_update_date = sysdate,
last_updated_by = pg_user_id,
last_update_login = NVL(pg_login_id,pg_conc_login_id)
WHERE cc_trx_id = inv_rec.customer_trx_id
AND cc_trx_category = inv_rec.type
AND customer_bank_account_id = inv_rec.customer_bank_account_id
AND cc_error_code = inv_rec.cc_error_code;
insert_p(l_cc_error_hist);
UPDATE ar_cash_receipts
SET cc_error_flag = NULL,
cc_error_code = NULL,
cc_error_text = NULL,
customer_bank_account_id = p_customer_bank_account_id,
comments = DECODE(p_error_notes,NULL,comments,substrb('<'||l_error_notes||'>'||comments,1,2000)),
rec_version_number = rec_version_number+1
WHERE cash_receipt_id = p_cc_trx_id;
UPDATE ra_customer_trx
SET cc_error_flag =NULL,
cc_error_code = NULL,
cc_error_text = NULL,
customer_bank_account_id = customer_bank_account_id
WHERE customer_trx_id = p_cc_trx_id;
UPDATE ar_cash_receipts
SET payment_server_order_num = null,
approval_code = NULL,
cc_error_flag = NULL,
cc_error_code = NULL,
cc_error_text = NULL,
customer_bank_account_id = p_customer_bank_account_id,
comments = DECODE(p_error_notes,NULL,comments,substrb('<'||l_error_notes||'>'||comments,1,240))
WHERE cash_receipt_id = p_cc_trx_id;
SELECT trx.customer_trx_id,
trx.trx_number,
trx_types.type,
cr.receipt_number
FROM ra_customer_trx trx,
ar_receivable_applications ra,
ar_cash_receipts cr,
ra_cust_trx_types trx_types
WHERE trx.customer_trx_id = ra.applied_customer_trx_id
AND ra.cash_receipt_id = cr.cash_receipt_id
AND cr.customer_bank_account_id = trx.customer_bank_account_id
AND cr.receipt_method_id = trx.receipt_method_id
AND ra.display = 'Y'
AND trx_types.cust_trx_type_id = trx.cust_trx_type_id
AND cr.cash_receipt_id = p_cc_trx_id;
select description
into l_error_notes
from ar_lookups
where lookup_type = 'AR_CC_ERROR_NOTES'
and lookup_code = 'E1';
SELECT 1
INTO l_dummy_number
FROM ra_customer_trx
WHERE customer_trx_id = inv_cur.customer_trx_id
FOR UPDATE OF customer_trx_id;
Select NVL(cr.cc_error_code,'Unknown'),
NVL(cr.cc_error_text,'Unknown Error'),
ARPT_SQL_FUNC_UTIL.get_lookup_meaning('CC_TRX_CATEGORY',cr.type),
cr.receipt_number,
cr.currency_code,
cr.amount,
cr.receipt_date,
party.party_name,
cust.account_number,
site_uses.location,
decode(iby.INSTRUMENT_TYPE,'CREDITCARD',iby.CARD_NUMBER,iby.ACCOUNT_NUMBER),
decode(iby.INSTRUMENT_TYPE,'CREDITCARD',iby.CARD_EXPIRYDATE,null),
cr.payment_server_order_num,
cr.approval_code approval_code,
ARP_CORRECT_CC_ERRORS.get_collector_name(cr.pay_from_customer,cr.customer_site_use_id),
rm.name
INTO l_cc_error_code,
l_cc_error_desc,
l_cc_trx_category_dsp,
l_cc_trx_number,
l_cc_trx_currency,
l_cc_trx_amount,
l_cc_trx_date,
l_customer_name,
l_customer_number,
l_customer_location,
l_cc_number,
l_expiration_date,
l_payment_server_id,
l_approval_code,
l_collector,
l_payment_method_name
FROM ar_cash_receipts cr,
ar_receipt_methods rm,
hz_parties party,
hz_cust_accounts cust,
hz_cust_site_uses site_uses,
iby_trxn_extensions_v iby
WHERE rm.receipt_method_id = cr.receipt_method_id
AND cr.pay_from_customer = cust.cust_account_id (+)
AND cust.party_id = party.party_id (+)
AND cr.customer_site_use_id = site_uses.site_use_id (+)
AND cr.cc_error_flag = 'Y'
AND cr.selected_remittance_batch_id IS NULL
AND iby.trxn_extension_id = cr.payment_trxn_extension_id
AND cr.cash_receipt_id = p_cc_trx_id;
UPDATE ar_cash_receipts
SET comments = DECODE(p_error_notes,NULL,comments,substrb('<'||l_error_notes||'>'||comments,1,2000))
WHERE cash_receipt_id = p_cc_trx_id;
SELECT cr.cash_receipt_id,
cr.receipt_number
INTO l_cash_receipt_id,
l_rec_number
FROM ar_cash_receipts cr
WHERE cash_receipt_id in (SELECT ra.cash_receipt_id
FROM ar_receivable_applications ra
WHERE ra.application_ref_id = p_cc_trx_id
AND ra.applied_payment_schedule_id = -6);
SELECT 'lock'
FROM ar_cash_receipts
WHERE request_id = p_request_id
AND cc_error_flag = 'Y'
AND cc_error_code IS NOT NULL
FOR UPDATE OF cash_receipt_id;
SELECT 'lock'
FROM ra_customer_trx
WHERE request_id = p_request_id
AND cc_error_flag = 'Y'
AND cc_error_code IS NOT NULL
FOR UPDATE OF customer_trx_id;
UPDATE ar_cash_receipts
SET cc_error_flag = NULL,
cc_error_code = NULL,
cc_error_text = NULL,
comments = DECODE(p_error_notes,NULL,comments,substrb('<'||l_error_notes||'>'||comments,1,2000)),
rec_version_number = rec_version_number+1
WHERE cash_receipt_id = p_cc_trx_id;
UPDATE ra_customer_trx
SET cc_error_flag =NULL,
cc_error_code = NULL,
cc_error_text = NULL
WHERE customer_trx_id = p_cc_trx_id;
UPDATE ar_cash_receipts
SET cc_error_flag = NULL,
cc_error_code = NULL,
cc_error_text = NULL,
comments = DECODE(p_error_notes,NULL,comments,substrb('<'||l_error_notes||'>'||comments,1,2000)),
rec_version_number = rec_version_number+1
WHERE cash_receipt_id = p_cc_trx_id;
UPDATE ra_customer_trx
SET cc_error_flag =NULL,
cc_error_code = NULL,
cc_error_text = NULL
WHERE customer_trx_id = p_cc_trx_id;
ii) update receipt_method and payment info of Corresponding inv to null */
obtain_alternate_payment(p_cc_trx_id => p_cc_trx_id,
p_cc_trx_category => p_cc_trx_category,
p_error_notes => l_error_notes);
/* i) update receipt_method and payment info of RECEIPT to null */
UPDATE ar_cash_receipts_all
SET payment_trxn_extension_id = NULL,
receipt_method_id = NULL,
cc_error_flag = NULL,
last_update_date = sysdate,
last_updated_by = pg_user_id,
last_update_login = NVL(pg_login_id,pg_conc_login_id)
WHERE cash_receipt_id = p_cc_trx_id;
/* i) update receipt_method and payment info of inv to null */
UPDATE ra_customer_trx_all
SET payment_trxn_extension_id = NULL,
receipt_method_id = NULL,
cc_error_flag = NULL,
last_update_date = sysdate,
last_updated_by = pg_user_id,
last_update_login = NVL(pg_login_id,pg_conc_login_id)
WHERE customer_trx_id = p_cc_trx_id;
SELECT user_name
INTO pg_user_name
FROM fnd_user
WHERE user_id = pg_user_id;