[Home] [Help]
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 INSTR_ASSIGNMENT_ID INTO l_instr_assignment_id
FROM IBY_FNDCPT_TX_EXTENSIONS
WHERE TRXN_EXTENSION_ID = p_payment_trxn_extension_id;
SELECT 1
INTO l_dummy_number
FROM dual
WHERE NOT EXISTS ( SELECT 1
FROM ar_cc_error_history cc, iby_fndcpt_tx_extensions b
WHERE cc.cc_trx_id = p_cc_trx_id
AND cc.cc_trx_category = p_cc_trx_category
AND cc.payment_trxn_extension_id = b.trxn_extension_id
AND b.instr_assignment_id = l_instr_assignment_id
AND cc.cc_error_code = p_cc_error_code
AND cc.cc_action_type_flag = 'S' );
SELECT cc.cc_error_date
INTO l_first_cc_error_date
FROM ar_cc_error_history cc, iby_fndcpt_tx_extensions b
WHERE cc.cc_trx_id = p_cc_trx_id
AND cc.cc_trx_category = p_cc_trx_category
AND cc.payment_trxn_extension_id = b.trxn_extension_id
AND b.instr_assignment_id = l_instr_assignment_id
AND cc.cc_error_code = p_cc_error_code
AND cc.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_trxn_extension_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_trxn_extension_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,
trx.payment_trxn_extension_id,
trx.approval_code,
ARP_CORRECT_CC_ERRORS.get_collector_name(trx.paying_customer_id,site_uses.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_date,
l_customer_name,
l_customer_number,
l_customer_location,
l_cc_number,
l_payment_trxn_extension_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,
iby_fndcpt_pmt_chnnls_b pc
WHERE trx.receipt_method_id = rm.receipt_method_id
AND rm.payment_channel_code = pc.payment_channel_code
AND pc.instrument_type = 'CREDITCARD'
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
SET payment_trxn_extension_id = NULL,
receipt_method_id = NULL,
cc_error_flag = NULL,
cc_error_code = NULL,
cc_error_text = 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 IN ( select request_id
from fnd_concurrent_requests
where request_id = p_request_id
or parent_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 IN ( select request_id
from fnd_concurrent_requests
where request_id = p_request_id
or parent_request_id = p_request_id )
AND cc_error_flag = 'Y');
Select cash_receipt_id,
type,
receipt_method_id,
payment_trxn_extension_id,
cc_error_code,
cc_error_text,
request_id
FROM ar_cash_receipts
WHERE request_id IN ( select request_id
from fnd_concurrent_requests
where request_id = p_request_id
or parent_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 payment_trxn_extension_id = cr_rec.payment_trxn_extension_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.payment_trxn_extension_id,
trx.cc_error_code,
trx.cc_error_text,
trx.request_id
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 IN ( select request_id
from fnd_concurrent_requests
where request_id = p_request_id
or parent_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 payment_trxn_extension_id = inv_rec.payment_trxn_extension_id
AND cc_error_code = inv_rec.cc_error_code;
arp_util.debug('Update current_record_flag. Sql%rowcount '|| sql%rowcount);
arp_util.debug('Calling Insert into ar_cc_error_history');
insert_p(l_cc_error_hist);
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;
arp_util.debug ('Sql rows updated ' || sql%rowcount);
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;
arp_util.debug ('Sql rows updated ' || sql%rowcount);
UPDATE ar_cash_receipts
SET approval_code = NULL,
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,240))
WHERE cash_receipt_id = p_cc_trx_id;
SELECT
P.PARTY_ID,
P.CUST_ACCOUNT_ID,
P.ACCT_SITE_USE_ID,
P.ORG_ID,
P.ORG_TYPE,
U.PAYMENT_FUNCTION,
B.INSTR_ASSIGNMENT_ID,
B.PAYMENT_CHANNEL_CODE,
CR.RECEIPT_NUMBER
INTO
l_party_id,
l_cust_account_id,
l_cust_site_use_id,
l_org_id,
l_org_type,
l_payment_function,
l_instrument_assignment_id,
l_payment_channel,
l_receipt_number
FROM
IBY_FNDCPT_TX_EXTENSIONS B,
IBY_EXTERNAL_PAYERS_ALL P,
IBY_PMT_INSTR_USES_ALL U,
AR_CASH_RECEIPTS CR
WHERE B.TRXN_EXTENSION_ID = p_payment_trxn_extension_id
AND B.INSTR_ASSIGNMENT_ID = U.INSTRUMENT_PAYMENT_USE_ID
AND U.EXT_PMT_PARTY_ID = P.EXT_PAYER_ID
AND CR.CASH_RECEIPT_ID = p_cc_trx_id
AND B.TRXN_EXTENSION_ID = CR.PAYMENT_TRXN_EXTENSION_ID;
update ar_cash_receipts set
payment_trxn_extension_id = l_payment_trxn_extension_id
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.payment_trxn_extension_id = trx.payment_trxn_extension_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),
cr.payment_trxn_extension_id,
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_payment_trxn_extension_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 IN ( select request_id
from fnd_concurrent_requests
where request_id = p_request_id
or parent_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 IN ( select request_id
from fnd_concurrent_requests
where request_id = p_request_id
or parent_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;