[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
| update_application |
| |
| DESCRIPTION |
| This procedure is used to update an application, e.g. USSGL |
| Transaction Code, Cross Currency Rate etc. Columns that can be |
| modified without having to reverse the original rows and create |
| new ones. We simply update the APP row with the new value. |
| |
| SCOPE - PUBLIC |
| |
| EXETERNAL PROCEDURES/FUNCTIONS ACCESSED - NONE |
| |
| ARGUMENTS : IN: |
| |
| OUT: |
| |
| RETURNS : NONE |
| |
| NOTES |
| |
| MODIFICATION HISTORY |
| |
| 07/29/1997 Karen Lawrance Release 11. |
| Added trans_to_receipt_rate to update call |
| for cross currency. |
| Also included acctd amount applied to and |
| from as OUT NOCOPY parameters. These are used to |
| update the form with accurate values. |
| 08/21/1997 Tasman Tang Added global_attribute_category, |
| global_attribute[1-20] for global |
| descriptive flexfield |
| 05/24/1999 Debbie Jancis Bug fix 874714 |
| update_application should not update anything|
| having to do with amount columns because |
| amount columns affect posting. Also, |
| apply_date or gl_date |
| 06/06/2001 S.Nambiar Bug 1815528 - Added claim related parameters |
| 07/31/2001 jbeckett Bug 1905659 - For invoice related claim, pass|
| trx info to create_claim |
| 08/03/2001 jbeckett Bug 1905659 - Added parameter |
| p_amount_due_remaining |
| 08/10/2001 S.Nambiar Migrated chargeback_customer_trx_id to |
| secondary_application_ref_id
| 03/15/2002 jbeckett Added new parameters p_application_ref_reason|
| and p_customer_reference (bug 2254777). |
| 05/09/2002 jbeckett Passes primary_salesrep_id to create_claim |
| for invoice related deductions |
| 02/20/2002 jbeckett Bug 2751910 - Added p_customer_reason and |
| p_applied_rec_app_id to update_application |
| 10/25/2005 jbeckett Bug 4565758 - legal_entity_id passed to |
| create_claim.
+===========================================================================*/
PROCEDURE update_application(
p_ra_id IN NUMBER,
p_receipt_ps_id IN NUMBER,
p_invoice_ps_id IN NUMBER,
p_ussgl_transaction_code IN VARCHAR2,
p_application_ref_type IN
ar_receivable_applications.application_ref_type%TYPE,
p_application_ref_id IN
ar_receivable_applications.application_ref_id%TYPE,
p_application_ref_num IN
ar_receivable_applications.application_ref_num%TYPE,
p_secondary_application_ref_id IN
ar_receivable_applications.secondary_application_ref_id%TYPE,
p_receivable_trx_id IN ar_receivable_applications.receivables_trx_id%TYPE,
p_attribute_category IN VARCHAR2,
p_attribute1 IN VARCHAR2,
p_attribute2 IN VARCHAR2,
p_attribute3 IN VARCHAR2,
p_attribute4 IN VARCHAR2,
p_attribute5 IN VARCHAR2,
p_attribute6 IN VARCHAR2,
p_attribute7 IN VARCHAR2,
p_attribute8 IN VARCHAR2,
p_attribute9 IN VARCHAR2,
p_attribute10 IN VARCHAR2,
p_attribute11 IN VARCHAR2,
p_attribute12 IN VARCHAR2,
p_attribute13 IN VARCHAR2,
p_attribute14 IN VARCHAR2,
p_attribute15 IN VARCHAR2,
p_global_attribute_category IN VARCHAR2,
p_global_attribute1 IN VARCHAR2,
p_global_attribute2 IN VARCHAR2,
p_global_attribute3 IN VARCHAR2,
p_global_attribute4 IN VARCHAR2,
p_global_attribute5 IN VARCHAR2,
p_global_attribute6 IN VARCHAR2,
p_global_attribute7 IN VARCHAR2,
p_global_attribute8 IN VARCHAR2,
p_global_attribute9 IN VARCHAR2,
p_global_attribute10 IN VARCHAR2,
p_global_attribute11 IN VARCHAR2,
p_global_attribute12 IN VARCHAR2,
p_global_attribute13 IN VARCHAR2,
p_global_attribute14 IN VARCHAR2,
p_global_attribute15 IN VARCHAR2,
p_global_attribute16 IN VARCHAR2,
p_global_attribute17 IN VARCHAR2,
p_global_attribute18 IN VARCHAR2,
p_global_attribute19 IN VARCHAR2,
p_global_attribute20 IN VARCHAR2,
p_comments IN VARCHAR2, -- Added for bug 1839744
p_gl_date OUT NOCOPY DATE,
p_customer_trx_line_id IN NUMBER,
p_module_name IN VARCHAR2,
p_module_version IN VARCHAR2,
x_application_ref_id OUT NOCOPY
ar_receivable_applications.application_ref_id%TYPE,
x_application_ref_num OUT NOCOPY
ar_receivable_applications.application_ref_num%TYPE,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2,
p_acctd_amount_applied_to OUT NOCOPY NUMBER,
p_acctd_amount_applied_from OUT NOCOPY NUMBER,
p_amount_due_remaining IN ar_payment_schedules.amount_due_remaining%TYPE,
p_application_ref_reason IN ar_receivable_applications.application_ref_reason%TYPE,
p_customer_reference IN ar_receivable_applications.customer_reference%TYPE,
p_customer_reason IN ar_receivable_applications.customer_reason%TYPE,
p_applied_rec_app_id IN ar_receivable_applications.applied_rec_app_id%TYPE,
x_claim_reason_name OUT NOCOPY VARCHAR2) IS
l_rec_ra_rec ar_receivable_applications%ROWTYPE;
arp_standard.debug( 'arp_process_application.update_application()+');
'ARP_PROCESS_APPLICATION.UPDATE_APPLICATION'
, 'P_RA_ID'
, 'NULL');
'ARP_PROCESS_APPLICATION.UPDATE_APPLICATION'
, 'p_invoice_ps_id'
, 'NULL');
SELECT ps.cash_receipt_id
, cr.currency_code
, cr.exchange_rate_type
, cr.exchange_date
, cr.exchange_rate
, cr.pay_from_customer
, cr.customer_site_use_id
, NULL
, cr.receipt_number
, cr.legal_entity_id
INTO l_rec_ra_rec.cash_receipt_id
, l_currency_code
, l_exchange_rate_type
, l_exchange_rate_date
, l_exchange_rate
, l_customer_id
, l_bill_to_site_use_id
, l_ship_to_site_use_id
, l_receipt_number
, l_legal_entity_id
FROM ar_payment_schedules ps
, ar_cash_receipts cr
, ar_cash_receipt_history crh
, ar_receipt_methods rm
, ce_bank_acct_uses ba
, ar_receipt_method_accounts rma
WHERE ps.payment_schedule_id = p_receipt_ps_id
AND cr.cash_receipt_id = ps.cash_receipt_id
AND crh.cash_receipt_id = cr.cash_receipt_id
AND crh.current_record_flag = 'Y'
AND rm.receipt_method_id = cr.receipt_method_id
AND ba.bank_acct_use_id = cr.remit_bank_acct_use_id
AND rma.remit_bank_acct_use_id = ba.bank_acct_use_id
AND rma.receipt_method_id = rm.receipt_method_id;
SELECT t.invoice_currency_code
, t.exchange_rate_type
, t.exchange_date
, t.exchange_rate
, t.customer_trx_id
, t.trx_number
, t.cust_trx_type_id
, t.bill_to_customer_id
, t.bill_to_site_use_id
, t.ship_to_site_use_id
, p.amount_due_remaining
, t.primary_salesrep_id
, t.legal_entity_id
INTO l_currency_code
, l_exchange_rate_type
, l_exchange_rate_date
, l_exchange_rate
, l_customer_trx_id
, l_trx_number
, l_cust_trx_type_id
, l_customer_id
, l_bill_to_site_use_id
, l_ship_to_site_use_id
, l_amount_due_remaining
, l_salesrep_id -- bug 2361331
, l_legal_entity_id
FROM ra_customer_trx t
, ar_payment_schedules p
WHERE t.customer_trx_id = p.customer_trx_id
AND p.payment_schedule_id = p_invoice_ps_id;
SELECT cr.cash_receipt_id, cr.receipt_number
INTO l_rec_ra_rec.cash_receipt_id
, l_receipt_number
FROM ar_cash_receipts cr,
ar_payment_schedules ps
WHERE ps.payment_schedule_id = p_receipt_ps_id
AND cr.cash_receipt_id = ps.cash_receipt_id;
arp_app_pkg.update_p(l_rec_ra_rec);
arp_standard.debug( 'arp_process_application.update_application()-');
END update_application;
| delete_selected_transaction
|
| DESCRIPTION
| This procedure is used to delete an application that has been
| created through the automatic receipt creation process.
|
| SCOPE - PUBLIC
|
| EXETERNAL PROCEDURES/FUNCTIONS ACCESSED - NONE
|
| ARGUMENTS : IN:
| p_ra_id Id of application to be deleted.
| p_app_ps_id Payment Schedule Id of the applied
| Transaction.
|
| OUT:
|
| RETURNS : NONE
|
| NOTES
|
| MODIFICATION HISTORY
| 12/06/1996 Karen Lawrance Created
| 10/22/1997 Karen Murphy Bug #567872. Added code to update the
| UNAPP row in receivable applications
| when an APP row is deleted.
12/04/1997 Karen Murphy Bug fix #567872. Added the setting of the
acctd_amount_applied_from for the UNAPP row.
| 24/03/1998 Vikram Ahluwalia Plugin calls one delete for the APP record
| and a combination of delete followed by
| create for the UNAPP record accounting.
| Though this appears to be specifically
| written for Unconfirmed Autoreceipts (APP
| and UNAPP combination it patched for
| completeness - notice the confirmed flag
| check in delete cursor and create call
+===========================================================================*/
PROCEDURE delete_selected_transaction (
p_ra_id IN NUMBER
, p_app_ps_id IN NUMBER
) IS
CURSOR get_app_C(l_app_id NUMBER) IS
select app.receivable_application_id app_id,
app.cash_receipt_id cr_id
from ar_receivable_applications app
where app.receivable_application_id = l_app_id
and nvl(app.confirmed_flag,'Y') = 'Y' --confirmed records have accounting only
and exists (select 'x'
from ar_distributions ard
where ard.source_table = 'RA'
and ard.source_id = app.receivable_application_id);
arp_standard.debug( 'arp_process_application.delete_selected_transaction()+');
'ARP_PROCESS_APPLICATION.DELETE_AUTOMATIC_APPLICATION'
, 'P_RA_ID'
, 'NULL');
'ARP_PROCESS_APPLICATION.DELETE_AUTOMATIC_APPLICATION'
, 'P_APP_PS_ID'
, 'NULL');
select ra.cash_receipt_id,
ra.amount_applied,
ra.acctd_amount_applied_from
into ln_cash_receipt_id,
ln_amount_applied,
ln_acctd_amount_applied_from
from ar_receivable_applications ra
where ra.receivable_application_id = p_ra_id;
arp_acct_main.Delete_Acct_Entry(l_ae_doc_rec);
arp_app_pkg.delete_p(p_ra_id);
p_event_mode => 'DELETE',
p_table_name => 'AR_RECEIVABLE_APPLICATIONS',
p_mode => 'SINGLE',
p_key_value => p_ra_id);
ARP_XLA_EVENTS.delete_event
( p_document_id => p_ra_id,
p_doc_table => 'APP');
lr_ps_rec.selected_for_receipt_batch_id := null;
arp_ps_pkg.update_p(lr_ps_rec, p_app_ps_id);
arp_standard.debug('delete_selected_transaction: ' || 'Getting the Receivable Application Id for the UNAPP row');
select ra.receivable_application_id
into ln_unapp_ra_id
from ar_receivable_applications ra
where ra.cash_receipt_id = ln_cash_receipt_id
and ra.status = 'UNAPP';
arp_standard.debug('delete_selected_transaction: ' || 'Fetch the UNAPP row');
arp_standard.debug('delete_selected_transaction: ' || 'New UNAPP amount: ' || to_char(lr_ra_rec.amount_applied));
arp_standard.debug('delete_selected_transaction: ' || 'Update the UNAPP row');
arp_acct_main.Delete_Acct_Entry(l_ae_doc_rec);
arp_app_pkg.update_p(lr_ra_rec);
arp_standard.debug('delete_selected_transaction: ' || 'arp_process_application.delete_receivable_application()-');
arp_standard.debug('delete_selected_transaction: ' || '-- EXCEPTION:');
arp_standard.debug('delete_selected_transaction: ' || 'Printing procedure parameter values:');
arp_standard.debug('delete_selected_transaction: ' || '-- p_ra_id = '||TO_CHAR(p_ra_id));
arp_standard.debug('delete_selected_transaction: ' || '-- p_app_ps_id = '||TO_CHAR(p_app_ps_id));
END delete_selected_transaction;