[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
PROCEDURE val_update_rev_actions( p_ps_id IN NUMBER );
| update_reverse_actions |
| |
| DESCRIPTION |
| This procedure performs all actions to modify the passed in |
| user defined application record and prepares to update payment schedule|
| table |
| |
| SCOPE - PUBLIC |
| |
| EXETERNAL PROCEDURES/FUNCTIONS ACCESSED - |
| arp_ps_pkg.update_p - Update a row in AR_PAYMENT_SCHEDULES table |
| arp_ps_pkg.fetch_p - fetch a row in AR_PAYMENT_SCHEDULES table |
| |
| ARGUMENTS : IN: |
| p_app_rec - Application Record structure |
| p_module_name _ Name of module that called this procedure|
| p_module_version - Version of module that called this |
| procedure |
| OUT: |
| |
| RETURNS : NONE |
| |
| NOTES |
| |
| MODIFICATION HISTORY - Created by Ganesh Vaidee - 04/25/95 |
| 8/2/1996 Harri Kaukovuo Bug fix 387827, chargebacks did not get |
| closed when receipt was reversed. |
| 03/07/1997 Karen Lawrance Bug fix #446386. Discounts taken where not |
| included in reversing calculations, should |
| affect line total. |
| 05/15/1997 Karen Lawrance Bug fix #464203. Added to if constructs |
| using trx_type AR_APP and AR_CM. |
| Refer to comments in code. |
+===========================================================================*/
PROCEDURE update_reverse_actions(
p_app_rec IN arp_global.app_rec_type,
p_module_name IN VARCHAR2,
p_module_version IN VARCHAR2) IS
l_ps_rec ar_payment_schedules%ROWTYPE;
arp_util.debug('val_update_rev_actions: ' || 'arp_ps_util.update_reverse_actions()+' );
arp_util.debug('val_update_rev_actions: ' || 'What is the type?' );
arp_util.debug('val_update_rev_actions: ' || 'p_app_rec.trx_type = ' || p_app_rec.trx_type );
This procedure is called to update the applied transaction as
well as the Receipt or Credit Memo in payment schedules.
When we are updating the Receipt, Credit Memo, or the Transaction
for the Receipt Application, we want to reset to Amount Applied.
When we are updating the Transaction for the Credit Memo
Application, we want to reset the Amount Credited.
*/
--
-- Update amount_applied
--
IF ( p_app_rec.trx_type = 'AR_APP' ) OR
( p_app_rec.trx_type IN ('AR_CM','AR_CM_REF') and
l_ps_rec.class = 'CM' ) THEN
l_ps_rec.amount_applied := NVL( l_ps_rec.amount_applied, 0) +
NVL( p_app_rec.amount_applied, 0 );
l_ps_rec.selected_for_receipt_batch_id := NULL;
arp_ps_pkg.update_p( l_ps_rec );
arp_util.debug('val_update_rev_actions: ' || 'arp_ps_util.update_reverse_actions()-' );
arp_util.debug('val_update_rev_actions: ' ||
'EXCEPTION: arp_ps_util.update_reverse_actions' );
END update_reverse_actions;
| val_update_rev_actions |
| |
| DESCRIPTION |
| This procedure validated arguments passed to update_reverse_actions |
| |
| SCOPE - PRIVATE |
| |
| EXETERNAL PROCEDURES/FUNCTIONS ACCESSED - NONE |
| |
| ARGUMENTS : IN: |
| p_ps_id - Payment Schedule Id from user define appln |
| record |
| OUT: |
| |
| RETURNS : NONE |
| |
| NOTES |
| |
| MODIFICATION HISTORY - Created by Ganesh Vaidee - 04/25/95 |
| |
+===========================================================================*/
PROCEDURE val_update_rev_actions( p_ps_id IN NUMBER ) IS
BEGIN
IF PG_DEBUG in ('Y', 'C') THEN
arp_util.debug( 'arp_ps_util.val_update_rev_actions()+' );
arp_util.debug( 'arp_ps_util.val_update_rev_actions()-' );
arp_util.debug('val_update_rev_actions: ' ||
'EXCEPTION: arp_ps_util.val_update_rev_actions' );
END val_update_rev_actions;
| update_receipt_related_columns |
| |
| DESCRIPTION |
| This procedure updates the receipt related rows of a payment schedule|
| The passed in PS ID is assumed to belong to a receipt. The procedure |
| sets the gl_date and gl_date_closed and amount applied. The procedure|
| should be called whenever a receipt is applied to an invoice. |
| The procedure also return the acctd_amount_applied to populate the |
| acctd_amount_applied_from column during AR_RA row insertion |
| |
| SCOPE - PUBLIC |
| |
| PARAMETERS : |
| IN : p_payment_schedule_id - payment_schedule_id of payment |
| schedule |
| p_gldate - GL date of the receipt |
| p_apply_date - Apply Date of the receipt |
| p_amount_applied - Amount of the receipt applied to the |
| invoice. |
| p_module_name - Name of module that called this routine |
| p_module_version - Version of module that called this routine|
| OUT NOCOPY : p_acctd_amount_applied_out - Accounted amount applied used to|
| populate acctd_amount_applied_from in AR_RA table |
| |
| EXTERNAL PROCEDURES/FUNCTION |
| arp_util.calc_acctd_amount and arp_util.debug |
| arp_ps_pkg.fetch_p and arp_ps_pkg.update_p |
| |
| NOTES - Also Calls populate_closed_dates. This procedure is in this file. |
| |
| HISTORY - Created By - Ganesh Vaidee - 08/24/1995 |
| |
+===========================================================================*/
PROCEDURE update_receipt_related_columns(
p_ps_id IN ar_payment_schedules.payment_schedule_id%TYPE,
p_amount_applied IN ar_payment_schedules.amount_applied%TYPE,
p_apply_date IN ar_payment_schedules.gl_date%TYPE,
p_gl_date IN ar_payment_schedules.gl_date%TYPE,
p_acctd_amount_applied OUT NOCOPY
ar_receivable_applications.acctd_amount_applied_from%TYPE,
p_ps_rec IN ar_payment_schedules%ROWTYPE ) IS
--
l_ps_rec ar_payment_schedules%ROWTYPE;
arp_util.debug( 'arp_ps_util.update_receipt_related_columns()+' );
l_ps_rec.selected_for_receipt_batch_id := NULL;
gl_date and receipt_confirmed_flag also needs to be updated */
/* Bug fix 3721519
The WHO columns should be updated as in arp_ps_pkg.update_p */
/* Bug 5569488, do not update payment schedule if the receipt requires
confirmation. Added the If condition */
IF NVL(l_ps_rec.receipt_confirmed_flag,'Y') <> 'N' THEN
UPDATE ar_payment_schedules
set acctd_amount_due_remaining = l_ps_rec.acctd_amount_due_remaining,
amount_due_remaining = l_ps_rec.amount_due_remaining,
amount_applied = l_ps_rec.amount_applied,
selected_for_receipt_batch_id = l_ps_rec.selected_for_receipt_batch_id,
status = l_ps_rec.status,
reserved_type = l_ps_rec.reserved_type,
reserved_value = l_ps_rec.reserved_value,
gl_date_closed = l_ps_rec.gl_date_closed,
actual_date_closed = l_ps_rec.actual_date_closed ,
gl_date = l_ps_rec.gl_date,
receipt_confirmed_flag = l_ps_rec.receipt_confirmed_flag,
last_updated_by = arp_global.last_updated_by,
last_update_date = SYSDATE,
last_update_login = NVL(arp_standard.profile.last_update_login,l_ps_rec.last_update_login ),
request_id = NVL(arp_standard.profile.request_id,l_ps_rec.request_id),
program_application_id = NVL(arp_standard.profile.program_application_id,
l_ps_rec.program_application_id ),
program_id = NVL(arp_standard.profile.program_id, l_ps_rec.program_id),
program_update_date = DECODE(arp_standard.profile.program_id,
NULL, l_ps_rec.program_update_date,SYSDATE)
where
payment_schedule_id = l_ps_rec.payment_schedule_id;
arp_util.debug( 'arp_ps_util.update_receipt_related_columns()-' );
'EXCEPTION: arp_ps_util.update_receipt_related_columns' );
| Validate arguments passed to update_receipt_related_cols, |
| update_cm_related_cols and update_adj_related_cols procedure |
| |
| SCOPE - PUBLIC |
| |
| PARAMETERS : p_payment_schedule_id - payment_schedule_id of payment |
| schedule |
| p_amount_applied - Inout applied amount |
| p_gldate - GL date of the receipt |
| p_apply_date - Apply Date of the receipt |
| |
| HISTORY - Created By - Ganesh Vaidee - 08/24/1995 |
| |
| NOTES - |
| |
+===========================================================================*/
PROCEDURE validate_args_upd_rel_cols(
p_ps_id IN ar_payment_schedules.payment_schedule_id%TYPE,
p_amount_applied IN ar_payment_schedules.amount_applied%TYPE,
p_apply_date IN ar_payment_schedules.gl_date%TYPE,
p_gl_date IN ar_payment_schedules.gl_date%TYPE ) IS
BEGIN
IF PG_DEBUG in ('Y', 'C') THEN
arp_util.debug( 'arp_ps_util.validate_args_upd_rel_cols()+' );
| update_invoice_related_columns |
| |
| DESCRIPTION |
| This procedure updates the invoice related rows of a payment schedule|
| The passed in PS ID is assumed to belong to a invoice. The procedure |
| sets the gl_date and gl_date_closed and amount(s) applied. The |
| procedure should be called whenever a receipt is applied to an |
| invoice. The procedure also returns the acctd_amount_applied, |
| acctd_earned_discount_taken, acctd_unearned_discount_taken columns, |
| line_applied, tax_applied, freight_applied, charges_applied columns |
| to populate the RA columns during AR_RA row insertion |
| insertion |
| |
| SCOPE - PUBLIC |
| |
| PARAMETERS : |
| IN : p_app_type - Indicates the type of application |
| Valid values are CASH for receipt |
| application and CM fro credit memo appln. |
| p_payment_schedule_id - payment_schedule_id of payment |
| schedule |
| p_gldate - GL date of the receipt |
| p_apply_date - Apply Date of the receipt |
| p_amount_applied - Amount of the receipt applied to the |
| invoice |
| p_discount_taken_earned - Earned discount taken(NULL if CM |
| appln |
| p_discount_taken_unearned - Unearned discount taken(NULL if CM |
| appln |
| p_ps_rec - Payment Schedule record, If this field|
| is not null, the PS record is not |
| fetched using p_ps_id. This PS record |
| is used |
| OUT NOCOPY : p_acctd_amount_applied - Accounted amount applied used to |
| populate acctd_amount_applied_from in|
| AR_RA table |
| p_acctd_discount_taken_earned - Accounted discount taken earned to|
| populate acctd_discount_taken_earned |
| AR_RA table. This field is not |
| populated if application is of type |
| CM. It is NULL is app. type is CM. |
| p_acctd_disc_taken_unearned - Accounted discount taken unearned to|
| populate acctd_discount_taken_uneard |
| AR_RA table. This field is not |
| populated if application is of type |
| CM. It is NULL is app. type is CM. |
| p_tax_applied - Part of the applied amount applied to|
| tax |
| p_freight_applied - Part of the applied amount applied to|
| freight |
| p_line_applied - Part of the applied amount applied to|
| lines |
| p_charges_applied - Part of the applied amount applied to|
| receivable charges |
| |
| EXTERNAL PROCEDURES/FUNCTION |
| arp_util.calc_acctd_amount and arp_util.debug |
| arp_ps_pkg.fetch_p and arp_ps_pkg.update_p |
| |
| NOTES - Also Calls populate_closed_dates. This procedure is in this file. |
| |
| HISTORY - Created By - Ganesh Vaidee - 08/24/1995 |
| 06/23/1998 Guat Eng Tan Bug fix #658876. Added IF conditions before |
| the three calls to calc_acctd_amount. |
| |
| 08/29/2005 M Raymond 4566510 - Modified for etax. Made discount
| parameters IN/OUT and modified calls
| to calc_amount_and_remaining so that they
| would only happen if discounts were not
| already prorated.
| 12/18/2006 M Raymond 5677984 - Update l_line_remaining and
| l_tax_remaining after skipping
| calc_applied_and_remaining
+===========================================================================*/
PROCEDURE update_invoice_related_columns(
p_app_type IN VARCHAR2,
p_ps_id IN ar_payment_schedules.payment_schedule_id%TYPE,
p_amount_applied IN ar_payment_schedules.amount_applied%TYPE,
p_discount_taken_earned IN ar_payment_schedules.discount_taken_earned%TYPE,
p_discount_taken_unearned IN ar_payment_schedules.discount_taken_unearned%TYPE,
p_apply_date IN ar_payment_schedules.gl_date%TYPE,
p_gl_date IN ar_payment_schedules.gl_date%TYPE,
p_acctd_amount_applied OUT NOCOPY ar_receivable_applications.acctd_amount_applied_to%TYPE,
p_acctd_earned_discount_taken OUT NOCOPY ar_receivable_applications.earned_discount_taken%TYPE,
p_acctd_unearned_disc_taken OUT NOCOPY ar_receivable_applications.acctd_unearned_discount_taken%TYPE,
p_line_applied OUT NOCOPY ar_receivable_applications.line_applied%TYPE,
p_tax_applied OUT NOCOPY ar_receivable_applications.tax_applied%TYPE,
p_freight_applied OUT NOCOPY ar_receivable_applications.freight_applied%TYPE,
p_charges_applied OUT NOCOPY ar_receivable_applications.receivables_charges_applied%TYPE,
p_line_ediscounted IN OUT NOCOPY ar_receivable_applications.line_applied%TYPE,
p_tax_ediscounted IN OUT NOCOPY ar_receivable_applications.tax_applied%TYPE,
p_freight_ediscounted OUT NOCOPY ar_receivable_applications.freight_applied%TYPE,
p_charges_ediscounted OUT NOCOPY ar_receivable_applications.receivables_charges_applied%TYPE,
p_line_uediscounted IN OUT NOCOPY ar_receivable_applications.line_applied%TYPE,
p_tax_uediscounted IN OUT NOCOPY ar_receivable_applications.tax_applied%TYPE,
p_freight_uediscounted OUT NOCOPY ar_receivable_applications.freight_applied%TYPE,
p_charges_uediscounted OUT NOCOPY ar_receivable_applications.receivables_charges_applied%TYPE,
p_rule_set_id OUT NOCOPY number,
p_ps_rec IN ar_payment_schedules%ROWTYPE) IS
l_ra_app_id NUMBER;
update_invoice_related_columns(
p_app_type,
p_ps_id,
p_amount_applied,
p_discount_taken_earned,
p_discount_taken_unearned,
p_apply_date,
p_gl_date,
p_acctd_amount_applied,
p_acctd_earned_discount_taken,
p_acctd_unearned_disc_taken,
p_line_applied,
p_tax_applied,
p_freight_applied,
p_charges_applied,
p_line_ediscounted,
p_tax_ediscounted,
p_freight_ediscounted,
p_charges_ediscounted,
p_line_uediscounted,
p_tax_uediscounted,
p_freight_uediscounted,
p_charges_uediscounted,
p_rule_set_id,
p_ps_rec,
to_number(NULL),
l_ra_app_id,
l_gt_id);
END update_invoice_related_columns;
| update_invoice_related_columns |
| |
| DESCRIPTION |
| This procedure updates the invoice related rows of a payment schedule|
| The passed in PS ID is assumed to belong to a invoice. The procedure |
| sets the gl_date and gl_date_closed and amount(s) applied. The |
| procedure should be called whenever a receipt is applied to an |
| invoice. The procedure also returns the acctd_amount_applied, |
| acctd_earned_discount_taken, acctd_unearned_discount_taken columns, |
| line_applied, tax_applied, freight_applied, charges_applied columns |
| to populate the RA columns during AR_RA row insertion |
| insertion |
| |
| SCOPE - PUBLIC |
| |
| PARAMETERS : |
| IN : p_app_type - Indicates the type of application |
| Valid values are CASH for receipt |
| application and CM fro credit memo appln. |
| p_payment_schedule_id - payment_schedule_id of payment |
| schedule |
| p_gldate - GL date of the receipt |
| p_apply_date - Apply Date of the receipt |
| p_amount_applied - Amount of the receipt applied to the |
| invoice |
| p_discount_taken_earned - Earned discount taken(NULL if CM |
| appln |
| p_discount_taken_unearned - Unearned discount taken(NULL if CM |
| appln |
| p_ps_rec - Payment Schedule record, If this field|
| is not null, the PS record is not |
| fetched using p_ps_id. This PS record |
| is used |
| OUT NOCOPY : p_acctd_amount_applied - Accounted amount applied used to |
| populate acctd_amount_applied_from in|
| AR_RA table |
| p_acctd_discount_taken_earned - Accounted discount taken earned to|
| populate acctd_discount_taken_earned |
| AR_RA table. This field is not |
| populated if application is of type |
| CM. It is NULL is app. type is CM. |
| p_acctd_disc_taken_unearned - Accounted discount taken unearned to|
| populate acctd_discount_taken_uneard |
| AR_RA table. This field is not |
| populated if application is of type |
| CM. It is NULL is app. type is CM. |
| p_tax_applied - Part of the applied amount applied to|
| tax |
| p_freight_applied - Part of the applied amount applied to|
| freight |
| p_line_applied - Part of the applied amount applied to|
| lines |
| p_charges_applied - Part of the applied amount applied to|
| receivable charges |
| |
| EXTERNAL PROCEDURES/FUNCTION |
| arp_util.calc_acctd_amount and arp_util.debug |
| arp_ps_pkg.fetch_p and arp_ps_pkg.update_p |
| |
| NOTES - Also Calls populate_closed_dates. This procedure is in this file. |
| |
| HISTORY - Created By - Ganesh Vaidee - 08/24/1995 |
| 06/23/1998 Guat Eng Tan Bug fix #658876. Added IF conditions before |
| the three calls to calc_acctd_amount. |
| |
| 08/29/2005 M Raymond 4566510 - Modified for etax. Made discount
| parameters IN/OUT and modified calls
| to calc_amount_and_remaining so that they
| would only happen if discounts were not
| already prorated.
| 12/18/2006 M Raymond 5677984 - Update l_line_remaining and
| l_tax_remaining after skipping
| calc_applied_and_remaining
+===========================================================================*/
PROCEDURE update_invoice_related_columns(
p_app_type IN VARCHAR2,
p_ps_id IN ar_payment_schedules.payment_schedule_id%TYPE,
p_amount_applied IN ar_payment_schedules.amount_applied%TYPE,
p_discount_taken_earned IN ar_payment_schedules.discount_taken_earned%TYPE,
p_discount_taken_unearned IN ar_payment_schedules.discount_taken_unearned%TYPE,
p_apply_date IN ar_payment_schedules.gl_date%TYPE,
p_gl_date IN ar_payment_schedules.gl_date%TYPE,
p_acctd_amount_applied OUT NOCOPY ar_receivable_applications.acctd_amount_applied_to%TYPE,
p_acctd_earned_discount_taken OUT NOCOPY ar_receivable_applications.earned_discount_taken%TYPE,
p_acctd_unearned_disc_taken OUT NOCOPY ar_receivable_applications.acctd_unearned_discount_taken%TYPE,
p_line_applied OUT NOCOPY ar_receivable_applications.line_applied%TYPE,
p_tax_applied OUT NOCOPY ar_receivable_applications.tax_applied%TYPE,
p_freight_applied OUT NOCOPY ar_receivable_applications.freight_applied%TYPE,
p_charges_applied OUT NOCOPY ar_receivable_applications.receivables_charges_applied%TYPE,
p_line_ediscounted IN OUT NOCOPY ar_receivable_applications.line_applied%TYPE,
p_tax_ediscounted IN OUT NOCOPY ar_receivable_applications.tax_applied%TYPE,
p_freight_ediscounted OUT NOCOPY ar_receivable_applications.freight_applied%TYPE,
p_charges_ediscounted OUT NOCOPY ar_receivable_applications.receivables_charges_applied%TYPE,
p_line_uediscounted IN OUT NOCOPY ar_receivable_applications.line_applied%TYPE,
p_tax_uediscounted IN OUT NOCOPY ar_receivable_applications.tax_applied%TYPE,
p_freight_uediscounted OUT NOCOPY ar_receivable_applications.freight_applied%TYPE,
p_charges_uediscounted OUT NOCOPY ar_receivable_applications.receivables_charges_applied%TYPE,
p_rule_set_id OUT NOCOPY number,
p_ps_rec IN ar_payment_schedules%ROWTYPE,
p_cash_receipt_id IN ar_receivable_applications_all.cash_receipt_id%TYPE,
p_ra_app_id OUT NOCOPY ar_receivable_applications.receivable_application_id%TYPE,
p_gt_id OUT NOCOPY NUMBER) IS
--
-- Temp variables
--
l_ps_rec ar_payment_schedules%ROWTYPE;
arp_util.debug( 'arp_ps_util.update_invoice_related_columns()+' );
SELECT customer_trx_id
INTO l_ebt_customer_trx_id
FROM ar_payment_schedules
WHERE payment_schedule_id = p_ps_id;
SELECT customer_trx_id
INTO l_ebt_customer_trx_id
FROM ar_payment_schedules
WHERE payment_schedule_id = p_ps_id;
/* Bug 5569488, do not update payment schedule if the receipt requires
confirmation. Added the If condition */
IF p_ps_rec.cash_receipt_id IS NOT NULL THEN
Select receipt_confirmed_flag
into l_receipt_confirmed_flag
from ar_payment_schedules_all
where cash_receipt_id = p_ps_rec.cash_receipt_id ;
arp_ps_pkg.update_p( l_ps_rec );
arp_util.debug( 'arp_ps_util.update_invoice_related_columns()-' );
'EXCEPTION: arp_ps_util.update_invoice_related_columns' );
END update_invoice_related_columns;
| Validate arguments passed to update_receipt_related_cols procedure |
| |
| SCOPE - PUBLIC |
| |
| PARAMETERS : p_payment_schedule_id - payment_schedule_id of payment |
| schedule |
| p_amount_applied - Inout amount applied |
| p_earned_discount - Earned discount |
| p_unearned_discount - Unearned discount |
| p_gldate - GL date of the receipt |
| p_apply_date - Apply Date of the receipt |
| |
| HISTORY - Created By - Ganesh Vaidee - 08/24/1995 |
| |
| NOTES - |
| |
+===========================================================================*/
PROCEDURE validate_args_upd_inv_rel_cols(
p_ps_id IN ar_payment_schedules.payment_schedule_id%TYPE,
p_amount_applied IN ar_payment_schedules.amount_applied%TYPE,
p_earned_discount IN
ar_payment_schedules.discount_taken_earned%TYPE,
p_unearned_discount IN
ar_payment_schedules.discount_taken_unearned%TYPE,
p_apply_date IN ar_payment_schedules.gl_date%TYPE,
p_gl_date IN ar_payment_schedules.gl_date%TYPE ) IS
BEGIN
IF PG_DEBUG in ('Y', 'C') THEN
arp_util.debug( 'arp_ps_util.validate_args_upd_inv_rel_cols()+' );
| update_cm_related_columns |
| |
| DESCRIPTION |
| This procedure updates the CM related rows of a payment schedule|
| The passed in PS ID is assumed to belong to a CM. The procedure |
| sets the gl_date and gl_date_closed and amount(s) applied. The |
| procedure should be called whenever a receipt is applied to an |
| invoice. |
| |
| SCOPE - PUBLIC |
| |
| PARAMETERS : |
| IN : p_payment_schedule_id - payment_schedule_id of Credir Memo |
| p_gldate - GL date of the receipt |
| p_apply_date - Apply Date of the receipt |
| p_amount_applied - Amount of the CM applied to the |
| invoice |
| p_ps_rec - Payment Schedule record, If this field|
| is not null, the PS record is not |
| fetched using p_ps_id. This PS record |
| is used |
| p_update_credit_flag - For CM refunds, to indicate if amount |
| credited should be updated |
| OUT NOCOPY : p_acctd_amount_applied - Accounted amount applied used to |
| populate acctd_amount_applied_from in|
| AR_RA table |
| p_tax_applied - Part of the applied amount applied to|
| tax, This field will populate |
| TAX_REMAINING in RA table |
| p_freight_applied - Part of the applied amount applied to|
| freight, This field will populate |
| FREIGHT_REMAINING in RA table |
| p_line_applied - Part of the applied amount applied to|
| lines, This field will populate |
| LINE_REMAINING in RA table |
| p_charges_applied - Part of the applied amount applied to|
| receivable charges, This field will |
| populate CHARGES_REMAINING in RA |
| table |
| |
| EXTERNAL PROCEDURES/FUNCTION |
| arp_util.calc_acctd_amount and arp_util.debug |
| arp_ps_pkg.fetch_p and arp_ps_pkg.update_p |
| |
| NOTES - Also Calls populate_closed_dates. This procedure is in this file. |
| |
| HISTORY - Created By - Ganesh Vaidee - 08/24/1995 |
| |
+===========================================================================*/
PROCEDURE update_cm_related_columns(
p_ps_id IN ar_payment_schedules.payment_schedule_id%TYPE,
p_amount_applied IN ar_payment_schedules.amount_applied%TYPE,
p_line_applied IN ar_receivable_applications.line_applied%TYPE,
p_tax_applied IN ar_receivable_applications.tax_applied%TYPE,
p_freight_applied IN
ar_receivable_applications.freight_applied%TYPE,
p_charges_applied IN
ar_receivable_applications.receivables_charges_applied%TYPE,
p_apply_date IN ar_payment_schedules.gl_date%TYPE,
p_gl_date IN ar_payment_schedules.gl_date%TYPE,
p_acctd_amount_applied OUT NOCOPY
ar_receivable_applications.acctd_amount_applied_to%TYPE,
p_ps_rec IN ar_payment_schedules%ROWTYPE,
p_update_credit_flag IN VARCHAR2 ) IS
--
l_ps_rec ar_payment_schedules%ROWTYPE;
arp_util.debug( 'arp_ps_util.update_cm_related_columns()+' );
arp_util.debug('update_cm_related_columns: ' || 'PS ID : '||p_ps_id );
arp_util.debug('update_cm_related_columns: ' || 'Amount Applied : '||p_amount_applied );
arp_util.debug('update_cm_related_columns: ' || 'Apply Date : '||TO_CHAR( p_apply_date ) );
arp_util.debug('update_cm_related_columns: ' || 'GL Date : '||TO_CHAR( p_gl_date ) );
IF ( NVL(p_update_credit_flag,'N') = 'Y' ) THEN
l_ps_rec.amount_credited := NVL( l_ps_rec.amount_credited, 0 ) +
p_amount_applied ;
arp_ps_pkg.update_p( l_ps_rec );
arp_util.debug( 'arp_ps_util.update_cm_related_columns()-' );
arp_util.debug('update_cm_related_columns: ' ||
'EXCEPTION: arp_ps_util.update_cm_related_columns' );
END update_cm_related_columns;
| update_adj_related_columns |
| |
| DESCRIPTION |
| This procedure updates the Adjustments related rows of a PS record |
| The passed in PS ID is assumed to belong to an adjustment. Procedure |
| sets the gl_date and gl_date_closed and amount(s) applied. The |
| procedure should be called whenever an invoice is adjusted. |
| In case of an invoice adjustment, the procedure also calculates the |
| line_adjusted, tax_adjusted, charges_adjusted and freight_adjusted |
| amounts. |
| |
| SCOPE - PUBLIC |
| |
| PARAMETERS : |
| IN : p_payment_schedule_id - payment_schedule_id of payment |
| schedule |
| p_type - Adjustment type - valid values are |
| 'INVOICE', 'FREIGHT', 'TAX', 'LINE', |
| 'CHARGES', NULL(In case of pendings only) |
| There is no explicit check to make sure |
| that the type value is one of the above |
| p_gldate - GL date of the receipt |
| p_apply_date - Apply Date of the receipt |
| p_amount_adjusted - Amount adjusted if type is not 'INVOICE' |
| p_amount_adjusted_pending - Amount adjusted pending if any. |
| IN : p_line_adjusted - Line adjusted - In case of INVOICE adj. |
| p_tax_adjusted - Tax adjusted - In case of INVOICE adj. |
| p_charges_adjusted - charges adjusted - In case of INVOICE adj.|
| p_freight_adjusted - freight adjusted - In case of INVOICE adj.|
| |
| EXTERNAL PROCEDURES/FUNCTION |
| arp_util.calc_acctd_amount and arp_util.debug |
| arp_ps_pkg.fetch_p and arp_ps_pkg.update_p |
| |
| NOTES - Also Calls populate_closed_dates. This procedure is in this file. |
| At present this is an overloaded procedure |
|
| HISTORY
| 8/24/1995 Ganesh Vaidee Created
| 4/17/1996 Harri Kaukovuo Added functionality to handle
| chargebacks properly.
| 02/02/2000 Saloni Shah Modified the parameter p_freight_Adjusted
| to IN OUT NOCOPY parameter.
| Changes made for reverse adjustment:
| when called from create_adjustments
| the p_type being sent from create_adjustment
| is set to REVERSE while updating the
| payment schedules. When the value is set
| to 'REVERSE' the amounts being passed
| for p_amount_adjusted, p_tax_adjusted,
| p_freight_adjusted and p_line_adjusted
| should not be modified, they should be
| set to the values passed by create_adjustment.
| 06/15/00 Satheesh Nambiar Bug 1290698- When partial amount is passed,
| default the line,tax,freight,charges adjusted
| to actual values instead of taking the full
| amounts from PS if the those values are not null
| or zero.
| 02/13/03 Ajay Pandit Made the p_ps_rec an IN OUT parameter.
| 07/21/04 Ravi Sharma Receivables_charges_charged should be gross charges
| inclusive of tax component in case of Activity of type
| Finance Charges having tax code source attached.
+===========================================================================*/
PROCEDURE update_adj_related_columns(
p_ps_id IN ar_payment_schedules.payment_schedule_id%TYPE,
p_type IN ar_adjustments.type%TYPE,
p_amount_adjusted IN ar_payment_schedules.amount_adjusted%TYPE,
p_amount_adjusted_pending IN ar_payment_schedules.amount_adjusted_pending%TYPE,
p_line_adjusted IN OUT NOCOPY ar_receivable_applications.line_applied%TYPE,
p_tax_adjusted IN OUT NOCOPY ar_receivable_applications.tax_applied%TYPE,
p_freight_adjusted IN OUT NOCOPY ar_receivable_applications.freight_applied%TYPE,
p_charges_adjusted IN OUT NOCOPY ar_receivable_applications.receivables_charges_applied%TYPE,
p_apply_date IN ar_payment_schedules.gl_date%TYPE,
p_gl_date IN ar_payment_schedules.gl_date%TYPE,
p_acctd_amount_adjusted OUT NOCOPY ar_receivable_applications.acctd_amount_applied_to%TYPE,
p_ps_rec IN OUT NOCOPY ar_payment_schedules%ROWTYPE) IS
--
-- deleted 'DEFAULT NULL' for p_ps_rec Rowtype attribute -bug460979 for Oracle8
l_ps_rec ar_payment_schedules%ROWTYPE;
arp_util.debug( 'arp_ps_util.update_adj_related_columns()+' );
arp_util.debug('update_adj_related_columns: ' || 'PS ID : '||p_ps_id );
arp_util.debug('update_adj_related_columns: ' || 'PS REC ID : '||to_char(p_ps_rec.payment_schedule_id));
arp_util.debug('update_adj_related_columns: ' || 'Type : '||p_type );
arp_util.debug('update_adj_related_columns: ' || 'Amount adjusted : '||p_amount_adjusted );
arp_util.debug('update_adj_related_columns: ' || 'Amount Adjusted Pend : '||p_amount_adjusted_pending );
arp_util.debug('update_adj_related_columns: ' || 'Line Adjusted : '||p_line_adjusted);
arp_util.debug('update_adj_related_columns: ' || 'Tax Adjusted : '||p_tax_adjusted);
arp_util.debug('update_adj_related_columns: ' || 'Freight Adjusted : '||p_freight_adjusted);
arp_util.debug('update_adj_related_columns: ' || 'Charges Adjusted : '||p_charges_adjusted);
arp_util.debug('update_adj_related_columns: ' || 'Apply Date : '||TO_CHAR( p_apply_date ) );
arp_util.debug('update_adj_related_columns: ' || 'GL Date : '||TO_CHAR( p_gl_date ) );
arp_util.debug('update_adj_related_columns: ' || 'Line Adjusted : '||p_line_adjusted );
arp_util.debug('update_adj_related_columns: ' || 'TAx Adjusted : '||p_tax_adjusted );
arp_util.debug('update_adj_related_columns: ' || 'Freight Adjusted : '||p_freight_adjusted );
arp_util.debug('update_adj_related_columns: ' || 'Charges Adjusted : '||p_charges_adjusted );
update_adj_related_columns(
l_ps_rec.payment_schedule_id,
l_line_adjusted,
l_tax_adjusted,
l_freight_adjusted,
l_charges_adjusted,
p_amount_adjusted_pending,
p_apply_date,
p_gl_date,
l_acctd_amount_adjusted,
l_ps_rec);
arp_util.debug( 'arp_ps_util.update_adj_related_columns()-' );
arp_util.debug('update_adj_related_columns: ' ||
'EXCEPTION: arp_ps_util.update_adj_related_columns' );
END update_adj_related_columns;
| Validate arguments passed to update_receipt_related_cols procedure |
| |
| SCOPE - PUBLIC |
| |
| PARAMETERS : |
| IN : p_payment_schedule_id - payment_schedule_id of payment |
| schedule |
| p_gldate - GL date of the receipt |
| p_apply_date - Apply Date of the receipt |
| |
| HISTORY - Created By - Ganesh Vaidee - 08/24/1995 |
| |
| NOTES - At present this is an overloaded procedure |
| |
+===========================================================================*/
PROCEDURE validate_args_upd_adj_rel_cols(
p_ps_id IN ar_payment_schedules.payment_schedule_id%TYPE,
p_apply_date IN ar_payment_schedules.gl_date%TYPE,
p_gl_date IN ar_payment_schedules.gl_date%TYPE ) IS
BEGIN
IF PG_DEBUG in ('Y', 'C') THEN
arp_util.debug('update_adj_related_columns: ' || 'arp_ps_util.validate_args_upd_adj_rel_cols()+' );
arp_util.debug('update_adj_related_columns: ' || 'arp_util.validate_args_upd_adj_rel_cols()-' );
arp_util.debug('update_adj_related_columns: ' ||
'EXCEPTION: arp_ps_util.validate_args_upd_adj_rel_cols' );
| update_adj_related_columns |
| |
| DESCRIPTION |
| This procedure updates the Adjustments related rows of a PS record |
| The passed in PS ID is assumed to belong to an adjustment. Procedure |
| sets the gl_date and gl_date_closed and amount(s) applied. The |
| procedure should be called whenever an invoice is adjusted. |
| |
| SCOPE - PUBLIC |
| |
| PARAMETERS : |
| IN : p_payment_schedule_id - payment_schedule_id of payment |
| schedule |
| p_gldate - GL date of the receipt |
| p_apply_date - Apply Date of the receipt |
| p_tax_adjusted - Part of the adjusted amount to be applied |
| to tax |
| p_freight_applied - Part of the adjusted amount to be applied |
| to freight |
| p_line_applied - Part of the adjusted amount to be applied |
| to lines |
| p_charges_applied - Part of the adjusted amount to be applied |
| to receivable charges |
| p_amount_adjusted_pending - Amount adjsuted pending if any. |
| |
| EXTERNAL PROCEDURES/FUNCTION |
| arp_util.calc_acctd_amount and arp_util.debug |
| arp_ps_pkg.fetch_p and arp_ps_pkg.update_p |
| |
| NOTES - Also Calls populate_closed_dates. This procedure is in this file. |
| |
| HISTORY - Created By - Ganesh Vaidee - 08/24/1995 |
| |
|02/13/03 Ajay Pandit Made the p_ps_rec an IN OUT parameter |
+===========================================================================*/
PROCEDURE update_adj_related_columns(
p_ps_id IN ar_payment_schedules.payment_schedule_id%TYPE,
p_line_adjusted IN ar_receivable_applications.line_applied%TYPE,
p_tax_adjusted IN ar_receivable_applications.tax_applied%TYPE,
p_freight_adjusted IN ar_receivable_applications.freight_applied%TYPE,
p_charges_adjusted IN ar_receivable_applications.receivables_charges_applied%TYPE,
p_amount_adjusted_pending IN ar_payment_schedules.amount_adjusted_pending%TYPE,
p_apply_date IN ar_payment_schedules.gl_date%TYPE,
p_gl_date IN ar_payment_schedules.gl_date%TYPE,
p_acctd_amount_adjusted OUT NOCOPY ar_receivable_applications.acctd_amount_applied_to%TYPE,
p_ps_rec IN OUT NOCOPY ar_payment_schedules%ROWTYPE) IS
--
-- deleted 'DEFAULT NULL' for p_ps_rec Rowtype attribute -bug460979 for Oracle8
l_ps_rec ar_payment_schedules%ROWTYPE;
arp_util.debug( 'arp_ps_util.update_adj_related_columns()+' );
arp_util.debug('update_adj_related_columns: ' || 'PS ID : '||p_ps_id );
arp_util.debug('update_adj_related_columns: ' || 'Line Adjusted : '||p_line_adjusted );
arp_util.debug('update_adj_related_columns: ' || 'TAx Adjusted : '||p_tax_adjusted );
arp_util.debug('update_adj_related_columns: ' || 'Freight Adjusted : '||p_freight_adjusted );
arp_util.debug('update_adj_related_columns: ' || 'Charges Adjusted : '||p_charges_adjusted );
arp_util.debug('update_adj_related_columns: ' || 'Amount Adjusted Pend : '||p_amount_adjusted_pending );
arp_util.debug('update_adj_related_columns: ' || 'Apply Date : '||TO_CHAR( p_apply_date ) );
arp_util.debug('update_adj_related_columns: ' || 'GL Date : '||TO_CHAR( p_gl_date ) );
arp_ps_pkg.update_p( l_ps_rec );
arp_util.debug( 'arp_ps_util.update_adj_related_columns()-' );
arp_util.debug('update_adj_related_columns: ' ||
'EXCEPTION: arp_ps_util.update_adj_related_columns' );
END update_adj_related_columns;
| Validate arguments passed to update_receipt_related_cols procedure |
| |
| SCOPE - PUBLIC |
| |
| PARAMETERS : |
| IN : p_payment_schedule_id - payment_schedule_id of payment |
| schedule |
| p_gldate - GL date of the receipt |
| p_apply_date - Apply Date of the receipt |
| p_tax_adjusted - Part of the adjusted amount to be applied |
| to tax |
| p_freight_applied - Part of the adjusted amount to be applied |
| to freight |
| p_line_applied - Part of the adjusted amount to be applied |
| to lines |
| p_charges_applied - Part of the adjusted amount to be applied |
| to receivable charges |
| p_amount_adjusted_pending - Amount adjsuted pending if any. |
| |
| HISTORY - Created By - Ganesh Vaidee - 08/24/1995 |
| |
| NOTES - |
| |
+===========================================================================*/
PROCEDURE validate_args_upd_adj_rel_cols(
p_ps_id IN ar_payment_schedules.payment_schedule_id%TYPE,
p_line_adjusted IN
ar_receivable_applications.line_applied%TYPE,
p_tax_adjusted IN
ar_receivable_applications.tax_applied%TYPE,
p_freight_adjusted IN
ar_receivable_applications.freight_applied%TYPE,
p_charges_adjusted IN
ar_receivable_applications.receivables_charges_applied%TYPE,
p_amount_adjusted_pending IN
ar_payment_schedules.amount_adjusted_pending%TYPE,
p_apply_date IN ar_payment_schedules.gl_date%TYPE,
p_gl_date IN ar_payment_schedules.gl_date%TYPE ) IS
BEGIN
IF PG_DEBUG in ('Y', 'C') THEN
arp_util.debug( 'arp_ps_util.validate_args_upd_adj_rel_cols()+' );
| will be less than the correct ones because this function selects |
| only from ar_receivable_applications, ar_adjustments and the input |
| "current" dates.
| |
| |
| SCOPE - PUBLIC |
| |
| PARAMETERS : p_payment_schedule_id - payment_schedule_id of payment |
| schedule |
| p_gl_reversal_date - gl_date of current uncommitted |
| transaction |
| p_reversal_date - apply date of current uncommitted xtion |
| p_gl_date_closed - greatest of ar_adjustments.gl_date, |
| ar_receivable_applications.gl_date, and |
| current_gl_date. |
| p_actual_date_closed - (output) greatest of |
| ar_adjustments.apply_date, |
| ar_receivable_applications.apply_date, |
| and current_apply_date. |
| |
| MODIFICATION HISTORY - |
| |
| 08/18/98 Sushama Borde Fixed bug 705906. Modified select statment|
| that gets the max gl_date and apply_date |
| from ar_receivable_applications. Now check|
| s the reversal_gl_date, while selecting |
| apply_date, and excludes applications that|
| have been reversed.
| 07/04/04 S.A.P.N.Sarma The actual_date_closed and gl_date_Closed |
| values have to be retrieved only for those|
| txns whose payment_Schedule_id > 0. We |
| need not retrieve the values for activity |
| aplications. Bug 3382570. |
+===========================================================================*/
PROCEDURE get_closed_dates( p_ps_id IN NUMBER,
p_gl_reversal_date IN DATE,
p_reversal_date IN DATE,
p_gl_date_closed OUT NOCOPY DATE,
p_actual_date_closed OUT NOCOPY DATE ,
p_app_type IN CHAR ) IS
l_gl_adj DATE;
SELECT MAX(DECODE(reversal_gl_date, NULL, gl_date, NULL)), /* Bug 5858538 */
MAX(DECODE(reversal_gl_date, NULL, apply_date, NULL)) -- Bug 705906: Not to include
-- application reversals while
-- getting the max apply_date.
INTO l_gl_app,
l_act_app
FROM ar_receivable_applications
WHERE ( applied_payment_schedule_id = p_ps_id -- "Trx" that was applied
or payment_schedule_id = p_ps_id ) -- "Payment" or "Credit Memo" being
-- applied.
AND nvl( confirmed_flag, 'Y' ) = 'Y';
arp_util.debug('get_closed_dates: ' || ' after select appln' );
SELECT MAX(gl_date),
MAX(apply_date)
INTO l_gl_adj,
l_act_adj
FROM ar_adjustments
WHERE status = 'A'
AND payment_schedule_id = p_ps_id;