[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
PROCEDURE update_current_cr_rec(
p_cr_rec IN OUT NOCOPY ar_cash_receipts%ROWTYPE,
p_reversal_category IN ar_cash_receipts.reversal_category%TYPE,
p_reversal_date IN ar_cash_receipts.reversal_date%TYPE,
p_reversal_reason_code IN ar_cash_receipts.reversal_reason_code%TYPE,
p_reversal_comments IN ar_cash_receipts.reversal_comments%TYPE,
p_attribute_category IN ar_cash_receipts.attribute_category%TYPE,
p_attribute1 IN ar_cash_receipts.attribute1%TYPE,
p_attribute2 IN ar_cash_receipts.attribute2%TYPE,
p_attribute3 IN ar_cash_receipts.attribute3%TYPE,
p_attribute4 IN ar_cash_receipts.attribute4%TYPE,
p_attribute5 IN ar_cash_receipts.attribute5%TYPE,
p_attribute6 IN ar_cash_receipts.attribute6%TYPE,
p_attribute7 IN ar_cash_receipts.attribute7%TYPE,
p_attribute8 IN ar_cash_receipts.attribute8%TYPE,
p_attribute9 IN ar_cash_receipts.attribute9%TYPE,
p_attribute10 IN ar_cash_receipts.attribute10%TYPE,
p_attribute11 IN ar_cash_receipts.attribute11%TYPE,
p_attribute12 IN ar_cash_receipts.attribute12%TYPE,
p_attribute13 IN ar_cash_receipts.attribute13%TYPE,
p_attribute14 IN ar_cash_receipts.attribute14%TYPE,
p_attribute15 IN ar_cash_receipts.attribute15%TYPE );
PROCEDURE update_current_crh_record(
p_crh_rec IN OUT NOCOPY ar_cash_receipt_history%ROWTYPE,
p_reversal_gl_date IN ar_misc_cash_distributions.gl_date%TYPE,
p_reversal_date IN ar_misc_cash_distributions.apply_date%TYPE,
p_crh_id_new IN ar_cash_receipt_history.cash_receipt_history_id%TYPE );
PROCEDURE insert_reversal_crh_record(
p_crh_rec IN OUT NOCOPY ar_cash_receipt_history%ROWTYPE,
p_reversal_gl_date IN ar_misc_cash_distributions.gl_date%TYPE,
p_reversal_date IN ar_misc_cash_distributions.apply_date%TYPE,
p_clear_batch_id IN VARCHAR2,
p_crh_id OUT NOCOPY ar_cash_receipt_history.cash_receipt_history_id%TYPE
);
PROCEDURE insert_reversal_mcd_record(
p_mcd_rec IN OUT NOCOPY ar_misc_cash_distributions%ROWTYPE,
p_reversal_gl_date IN ar_misc_cash_distributions.gl_date%TYPE,
p_reversal_date IN ar_misc_cash_distributions.apply_date%TYPE );
PROCEDURE insert_reversal_dist_rec(
p_cr_id IN ar_cash_receipts.cash_receipt_id%TYPE,
p_crh_id IN ar_cash_receipt_history.cash_receipt_history_id%TYPE );
PROCEDURE modify_update_ps_rec(
p_cr_id IN ar_payment_schedules.cash_receipt_id%TYPE,
p_reversal_gl_date IN DATE,
p_reversal_date IN DATE );
PROCEDURE modify_update_bat_rec( p_bat_id IN ar_batches.batch_id%TYPE,
p_cr_amount IN ar_cash_receipts.amount%TYPE,
p_status IN VARCHAR2 );
SELECT receivable_application_id
, receivables_trx_id
, applied_payment_schedule_id
, application_ref_id
FROM ar_receivable_applications
WHERE cash_receipt_id = p_cr_id
AND ( (
status||'' in ('APP', 'ACC','ACTIVITY','OTHER ACC')
AND display = 'Y'
)
OR
(
status||'' in ('UNAPP', 'UNID')
)
)
AND reversal_gl_date is NULL
ORDER BY decode(status,'APP',1,'ACTIVITY',2,'ACC',3,'OTHER ACC',4,'UNID',5,'UNAPP',6); --VAT 11.5 for pairing UNAPP records
SELECT *
FROM ar_misc_cash_distributions
WHERE cash_receipt_id = p_cr_id
AND reversal_gl_date is null;
SELECT ra.receivable_application_id
FROM ar_receivable_applications ra ,ar_cash_receipts cr
WHERE cr.reference_id = ra.cash_receipt_id
AND cr.cash_receipt_id = p_cr_id
AND ra.application_ref_id = p_cr_id
AND ra.application_ref_type = 'MISC_RECEIPT';
Select payment_channel_code
FROM ar_receipt_methods
where receipt_method_id = p_receipt_method_id;
select 'Y' into l_llca_exist
from ar_activity_details
where cash_receipt_id = p_cr_id
and nvl(CURRENT_ACTIVITY_FLAG,'Y') = 'Y';
INSERT INTO AR_ACTIVITY_DETAILS(
CASH_RECEIPT_ID,
CUSTOMER_TRX_LINE_ID,
ALLOCATED_RECEIPT_AMOUNT,
AMOUNT,
TAX,
FREIGHT,
CHARGES,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
LINE_DISCOUNT,
TAX_DISCOUNT,
FREIGHT_DISCOUNT,
LINE_BALANCE,
TAX_BALANCE,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_LOGIN,
COMMENTS,
APPLY_TO,
ATTRIBUTE1,
ATTRIBUTE2,
ATTRIBUTE3,
ATTRIBUTE4,
ATTRIBUTE5,
ATTRIBUTE6,
ATTRIBUTE7,
ATTRIBUTE8,
ATTRIBUTE9,
ATTRIBUTE10,
ATTRIBUTE11,
ATTRIBUTE12,
ATTRIBUTE13,
ATTRIBUTE14,
ATTRIBUTE15,
ATTRIBUTE_CATEGORY,
GROUP_ID,
REFERENCE1,
REFERENCE2,
REFERENCE3,
REFERENCE4,
REFERENCE5,
OBJECT_VERSION_NUMBER,
CREATED_BY_MODULE,
SOURCE_ID,
SOURCE_TABLE,
LINE_ID,
CURRENT_ACTIVITY_FLAG)
SELECT
LLD.CASH_RECEIPT_ID,
LLD.CUSTOMER_TRX_LINE_ID,
LLD.ALLOCATED_RECEIPT_AMOUNT*-1,
LLD.AMOUNT*-1,
LLD.TAX*-1,
LLD.FREIGHT*-1,
LLD.CHARGES*-1,
LLD.LAST_UPDATE_DATE,
LLD.LAST_UPDATED_BY,
LLD.LINE_DISCOUNT,
LLD.TAX_DISCOUNT,
LLD.FREIGHT_DISCOUNT,
LLD.LINE_BALANCE,
LLD.TAX_BALANCE,
LLD.CREATION_DATE,
LLD.CREATED_BY,
LLD.LAST_UPDATE_LOGIN,
LLD.COMMENTS,
LLD.APPLY_TO,
LLD.ATTRIBUTE1,
LLD.ATTRIBUTE2,
LLD.ATTRIBUTE3,
LLD.ATTRIBUTE4,
LLD.ATTRIBUTE5,
LLD.ATTRIBUTE6,
LLD.ATTRIBUTE7,
LLD.ATTRIBUTE8,
LLD.ATTRIBUTE9,
LLD.ATTRIBUTE10,
LLD.ATTRIBUTE11,
LLD.ATTRIBUTE12,
LLD.ATTRIBUTE13,
LLD.ATTRIBUTE14,
LLD.ATTRIBUTE15,
LLD.ATTRIBUTE_CATEGORY,
LLD.GROUP_ID,
LLD.REFERENCE1,
LLD.REFERENCE2,
LLD.REFERENCE3,
LLD.REFERENCE4,
LLD.REFERENCE5,
LLD.OBJECT_VERSION_NUMBER,
LLD.CREATED_BY_MODULE,
LLD.SOURCE_ID,
LLD.SOURCE_TABLE,
ar_Activity_details_s.nextval,
'R'
FROM ar_Activity_details LLD
where LLD.cash_receipt_id = p_cr_id
and nvl(LLD.CURRENT_ACTIVITY_FLAG, 'Y') = 'Y';
UPDATE ar_Activity_details dtl
set CURRENT_ACTIVITY_FLAG = 'N'
where dtl.cash_receipt_id = p_cr_id
and nvl(dtl.CURRENT_ACTIVITY_FLAG, 'Y') = 'Y';
insert_reversal_crh_record( l_crh_rec,
p_reversal_gl_date, p_reversal_date,
p_clear_batch_id, p_crh_id ); /* Bug fix 3079331*/
update_current_crh_record( l_crh_rec_old, p_reversal_gl_date,
p_reversal_date, l_crh_rec.cash_receipt_history_id );
insert_reversal_dist_rec( p_cr_id,l_crh_rec.cash_receipt_history_id );
insert_reversal_mcd_record( l_mcd_rec, p_reversal_gl_date,
p_reversal_date );
modify_update_ps_rec( p_cr_id, p_reversal_gl_date,
p_reversal_date);
update_current_cr_rec( l_cr_rec, p_reversal_category,
p_reversal_date, p_reversal_reason_code,
p_reversal_comments,
p_attribute_category, p_attribute1,
p_attribute2, p_attribute3, p_attribute4,
p_attribute5, p_attribute6, p_attribute7,
p_attribute8, p_attribute9, p_attribute10,
p_attribute11, p_attribute12, p_attribute13,
p_attribute14, p_attribute15 );
modify_update_bat_rec( l_batch_id, l_cr_rec.amount, p_reversal_category );
| insert_reversal_dist_rec |
| |
| DESCRIPTION |
| Prepare the ar_distributions record for insertion into AR_DISTRIBUTIONs|
| table |
| |
| SCOPE - PRIVATE |
| |
| EXETERNAL PROCEDURES/FUNCTIONS ACCESSED - NONE |
| arp_distributions_pkg.insert_p - Insert table handler for |
| AR_DISTRIBUTIONS table |
| |
| ARGUMENTS : IN: |
| p_crh_id - Cash receipt history id |
| OUT: |
| |
| RETURNS : NONE |
| |
| NOTES |
| |
| MODIFICATION HISTORY - Created by Ganesh Vaidee - 04/25/95 |
| 04-JAN-99 D. Jancis Modified for VAT changes for 11.5, added |
| currency_code, currency_conversion_rate, |
| currency_conversion_type, |
| currency_conversion_date, third_party_id, |
| third_pary_sub_id. |
| |
| 27-Jun-02 D.Jancis Modified for mrc trigger replacement. |
| added call to ar_mrc_engine2 for processing|
| ar_distributions inserts. |
+===========================================================================*/
PROCEDURE insert_reversal_dist_rec(
p_cr_id IN ar_cash_receipts.cash_receipt_id%TYPE,
p_crh_id IN ar_cash_receipt_history.cash_receipt_history_id%TYPE ) IS
--
l_dist_rec ar_distributions%ROWTYPE;
SELECT
dist.source_type,
dist.code_combination_id,
nvl(SUM( nvl(dist.AMOUNT_DR,0)),0) -
nvl(SUM( nvl(dist.AMOUNT_CR,0) ),0) amount_cr,
nvl(SUM( nvl(dist.ACCTD_AMOUNT_DR,0) ),0) -
nvl(SUM( nvl(dist.ACCTD_AMOUNT_CR,0) ),0) acctd_amount_cr
FROM ar_distributions dist,
ar_cash_receipt_history crh
WHERE dist.source_id = crh.cash_receipt_history_id
AND crh.cash_receipt_id = p_cr_id
ANd dist.source_table = 'CRH'
GROUP BY dist.source_type,
dist.code_combination_id;
arp_standard.debug( 'arp_reverse_receipt.insert_reversal_dist_rec() +');
arp_standard.debug('insert_reversal_dist_rec: ' || '-- cr_id = '||to_char( p_cr_id ) );
arp_standard.debug('insert_reversal_dist_rec: ' || '-- crh_id = '||to_char( p_crh_id ) );
arp_standard.debug('insert_reversal_dist_rec: ' || '-- Fetching the cash receipt record -- ');
arp_distributions_pkg.insert_p( l_dist_rec, l_dist_rec.line_id );
/* need to insert records into the MRC table. Calling new
mrc engine */
ar_mrc_engine2.maintain_mrc_data2(
p_event_mode => 'INSERT',
p_table_name => 'AR_DISTRIBUTIONS',
p_mode => 'SINGLE',
p_key_value => l_dist_rec.line_id,
p_row_info => l_dist_rec);
arp_standard.debug( 'arp_reverse_receipt.insert_reversal_dist_rec() -');
arp_standard.debug('insert_reversal_dist_rec: ' ||
'EXCEPTION: arp_reverse_receipt.insert_reversal_dist_rec' );
END insert_reversal_dist_rec;
| insert_reversal_crh_record |
| |
| DESCRIPTION |
| Prepare for insertion of Insert the reversal cash receipt history recor|
| |
| SCOPE - PRIVATE |
| |
| EXETERNAL PROCEDURES/FUNCTIONS ACCESSED - NONE |
| arp_cr_history_pkg.insert_p -Insertion table handler for |
| AR_CASH_RECEIPTS_HISTORY table |
| |
| ARGUMENTS : IN: |
| p_crh_rec - Cash receipt history record structure |
| p_reversal_gl_date - Reversal GL date |
| p_reversal_date - Reversal Date |
| p_clear_batch_id - Flag to denote if the batch Id |
| should be nulled out NOCOPY or not |
| this procedure |
| OUT: |
| p_crh_id - Id of inserted ar_cash_receipt_history row |
| |
| RETURNS : NONE |
| |
| NOTES |
| |
| MODIFICATION HISTORY - Created by Ganesh Vaidee - 04/25/95 |
| 05/02/95 - Removed comments around |
| p_crh_rec.created_from variable |
| - Assigned 'ARP_PROCESS_RECIPTS.REVERSE'
| created_from variables |
| |
+===========================================================================*/
PROCEDURE insert_reversal_crh_record(
p_crh_rec IN OUT NOCOPY ar_cash_receipt_history%ROWTYPE,
p_reversal_gl_date IN ar_misc_cash_distributions.gl_date%TYPE,
p_reversal_date IN ar_misc_cash_distributions.apply_date%TYPE,
p_clear_batch_id IN VARCHAR2,
p_crh_id OUT NOCOPY ar_cash_receipt_history.cash_receipt_history_id%TYPE
) IS
--Bug#2750340
l_xla_ev_rec arp_xla_events.xla_events_type;
arp_standard.debug( 'arp_reverse_receipt.insert_reversal_crh_record() +');
arp_cr_history_pkg.insert_p( p_crh_rec, p_crh_rec.cash_receipt_history_id );
arp_standard.debug( 'arp_reverse_receipt.insert_reversal_crh_record() -');
arp_standard.debug('insert_reversal_crh_record: ' ||
'EXCEPTION: arp_reverse_receipt.insert_reversal_crh_record' );
END insert_reversal_crh_record;
| update_current_crh_record |
| |
| DESCRIPTION |
| Update the current Cash receipt history record |
| |
| SCOPE - PRIVATE |
| |
| EXETERNAL PROCEDURES/FUNCTIONS ACCESSED - NONE |
| arp_cr_history_pkg.update_p - cash receipt history update table |
| handler |
| |
| ARGUMENTS : IN: |
| p_crh_rec - cash receipt history record structure |
| p_reversal_gl_date - Reversal GL date |
| p_reversal_date - Reversal Date |
| p_crh_id_new - Id of newly inserted cash receipt |
| history row |
| OUT: |
| |
| RETURNS : NONE |
| |
| NOTES |
| |
| MODIFICATION HISTORY - Created by Ganesh Vaidee - 04/25/95 |
| |
+===========================================================================*/
PROCEDURE update_current_crh_record(
p_crh_rec IN OUT NOCOPY ar_cash_receipt_history%ROWTYPE,
p_reversal_gl_date IN ar_misc_cash_distributions.gl_date%TYPE,
p_reversal_date IN ar_misc_cash_distributions.apply_date%TYPE,
p_crh_id_new IN ar_cash_receipt_history.cash_receipt_history_id%TYPE ) IS
--Bug#2750340
l_xla_ev_rec arp_xla_events.xla_events_type;
arp_standard.debug( 'arp_reverse_receipt.update_current_crh_record() +');
arp_standard.debug('update_current_crh_record: ' || 'crh_id_new = '||to_char( p_crh_id_new ) );
arp_cr_history_pkg.update_p( p_crh_rec );
arp_standard.debug( 'arp_reverse_receipt.update_current_crh_record() -');
arp_standard.debug('update_current_crh_record: ' ||
'EXCEPTION: arp_reverse_receipt.update_current_crh_record' );
END update_current_crh_record;
| insert_reversal_mcd_record |
| |
| DESCRIPTION |
| Prepare for insertion of Insert the reversal misc cash distribution rec|
| |
| SCOPE - PRIVATE |
| |
| EXETERNAL PROCEDURES/FUNCTIONS ACCESSED - NONE |
| arp_misc_cash_dist_pkg.insert_p - Insertion table handler for |
| AR_MISC_CASH_DISTRIBUTIONS table|
| |
| ARGUMENTS : IN: |
| p_mcd_rec - Misc cash distributions receord structure |
| p_reversal_gl_date - Reversal GL date |
| p_reversal_date - Reversal Date |
| OUT: |
| |
| RETURNS : NONE |
| |
| NOTES |
| |
| MODIFICATION HISTORY - Created by Ganesh Vaidee - 04/25/95 |
| 05/02/95 - Removed comments around |
| p_mcd_rec.created_from variable |
| - Assigned 'ARP_PROCESS_RECIPTS.REVERSE'
| created_from variables |
| |
+===========================================================================*/
PROCEDURE insert_reversal_mcd_record(
p_mcd_rec IN OUT NOCOPY ar_misc_cash_distributions%ROWTYPE,
p_reversal_gl_date IN ar_misc_cash_distributions.gl_date%TYPE,
p_reversal_date IN ar_misc_cash_distributions.apply_date%TYPE ) IS
l_mcd_id ar_misc_cash_distributions.misc_cash_distribution_id%TYPE;
arp_standard.debug( 'arp_reverse_receipt.insert_reversal_mcd_record() +');
arp_misc_cash_dist_pkg.insert_p( p_mcd_rec, l_mcd_id );
arp_standard.debug( 'arp_reverse_receipt.insert_reversal_mcd_record() -');
arp_standard.debug('insert_reversal_mcd_record: ' ||
'EXCEPTION: arp_reverse_receipt.insert_reversal_mcd_record' );
END insert_reversal_mcd_record;
SELECT COUNT(*)
INTO l_count
FROM ar_payment_schedules ps,
ra_cust_trx_line_gl_dist ctlg
WHERE ps.associated_cash_receipt_id = p_cr_id
AND ps.class = 'CB'
AND ps.customer_trx_id = ctlg.customer_trx_id
AND ( NVL( ps.amount_applied, 0 ) <> 0
OR NVL( ps.amount_credited, 0 ) <> 0
OR 0 <> ( SELECT sum( adj.amount )
FROM ar_adjustments adj
WHERE adj.payment_schedule_id = ps.payment_schedule_id
AND adj.receivables_trx_id <>
arp_global.G_CB_REV_RT_ID
)
);
| modify_update_ps_rec |
| |
| DESCRIPTION |
| Prepare for updation into payment schedule record |
| |
| SCOPE - PRIVATE |
| |
| EXTERNAL PROCEDURES/FUNCTIONS ACCESSED - NONE |
| arp_ps_util.get_closed_dates - Get closed dates |
| arp_ps_pkg.fetch_f_cr_id - Fetch from payment |
| Schedule table handler useing cash receipt id |
| arp_ps_pkg.update_p - update payment schedule row |
| |
| ARGUMENTS : IN: |
| p_cr_id - Cash receipt id |
| p_reversal_gl_date - Reversal GL date |
| p_reversal_date - Reversal Date |
| this procedure |
| OUT: |
| |
| RETURNS : NONE |
| |
| NOTES |
| |
| MODIFICATION HISTORY - Created by Ganesh Vaidee - 04/25/95 |
| |
+===========================================================================*/
PROCEDURE modify_update_ps_rec(
p_cr_id IN ar_payment_schedules.cash_receipt_id%TYPE,
p_reversal_gl_date IN DATE,
p_reversal_date IN DATE ) IS
l_gl_date_closed DATE;
arp_standard.debug( 'arp_reverse_receipt.modify_update_ps_rec() +' );
arp_standard.debug('modify_update_ps_rec: ' || to_char( p_cr_id ) );
arp_standard.debug('modify_update_ps_rec: ' || 'cr_id = '||to_char( p_cr_id ) );
arp_ps_pkg.update_p( l_ps_rec );
arp_standard.debug( 'arp_reverse_receipt.modify_update_ps_rec() +' );
arp_standard.debug('modify_update_ps_rec: ' ||
'EXCEPTION: arp_reverse_receipt.modify_update_ps_rec' );
END modify_update_ps_rec;
| update_current_cr_rec |
| |
| DESCRIPTION |
| Prepare for updation of current cash receipts row |
| |
| SCOPE - PRIVATE |
| |
| EXTERNAL PROCEDURES/FUNCTIONS ACCESSED - NONE |
| arp_cash_receipts_pkg.update_p - Update payment schedule handler |
| |
| ARGUMENTS : IN: |
| p_crh_rec - Cash receipt history record structure |
| p_reversal_gl_date - Reversal GL date |
| p_reversal_date - Reversal Date |
| p_clear_batch_id - Flag to denote if the batch Id |
| should be nulled out NOCOPY or not |
| this procedure |
| OUT: |
| |
| RETURNS : NONE |
| |
| NOTES |
| |
| MODIFICATION HISTORY - Created by Ganesh Vaidee - 04/25/95 |
| 05/10/95- Nulled out NOCOPY selected_remittance_batch_id |
| column |
| |
+===========================================================================*/
PROCEDURE update_current_cr_rec(
p_cr_rec IN OUT NOCOPY ar_cash_receipts%ROWTYPE,
p_reversal_category IN ar_cash_receipts.reversal_category%TYPE,
p_reversal_date IN ar_cash_receipts.reversal_date%TYPE,
p_reversal_reason_code IN ar_cash_receipts.reversal_reason_code%TYPE,
p_reversal_comments IN ar_cash_receipts.reversal_comments%TYPE,
p_attribute_category IN ar_cash_receipts.attribute_category%TYPE,
p_attribute1 IN ar_cash_receipts.attribute1%TYPE,
p_attribute2 IN ar_cash_receipts.attribute2%TYPE,
p_attribute3 IN ar_cash_receipts.attribute3%TYPE,
p_attribute4 IN ar_cash_receipts.attribute4%TYPE,
p_attribute5 IN ar_cash_receipts.attribute5%TYPE,
p_attribute6 IN ar_cash_receipts.attribute6%TYPE,
p_attribute7 IN ar_cash_receipts.attribute7%TYPE,
p_attribute8 IN ar_cash_receipts.attribute8%TYPE,
p_attribute9 IN ar_cash_receipts.attribute9%TYPE,
p_attribute10 IN ar_cash_receipts.attribute10%TYPE,
p_attribute11 IN ar_cash_receipts.attribute11%TYPE,
p_attribute12 IN ar_cash_receipts.attribute12%TYPE,
p_attribute13 IN ar_cash_receipts.attribute13%TYPE,
p_attribute14 IN ar_cash_receipts.attribute14%TYPE,
p_attribute15 IN ar_cash_receipts.attribute15%TYPE ) IS
BEGIN
IF PG_DEBUG in ('Y', 'C') THEN
arp_standard.debug( 'arp_reverse_receipt.update_current_cr_rec() +' );
p_cr_rec.selected_remittance_batch_id := NULL;
arp_cash_receipts_pkg.update_p( p_cr_rec );
arp_standard.debug( 'arp_reverse_receipt.update_current_cr_rec() -');
arp_standard.debug('update_current_cr_rec: ' ||
'EXCEPTION: arp_reverse_receipt.update_current_cr_rec' );
END update_current_cr_rec;
| modify_update_bat_rec |
| |
| DESCRIPTION |
| Prepare for updation into AR_BATCHES table |
| |
| SCOPE - PRIVATE |
| |
| EXTERNAL PROCEDURES/FUNCTIONS ACCESSED - NONE |
| arp_cr_batches_pkg.fetch_p - Fetch a row from AR_BATCHES row |
| arp_cr_batches_pkg.update_p - Update a row in AR_BATCHES row |
| |
| ARGUMENTS : IN: |
| p_bat_id - AR_BATCHES batch id |
| p_cr_amount - Cash receipt amount |
| p_status - Reversal category of receipt |
| OUT: |
| |
| RETURNS : NONE |
| |
| NOTES |
| |
| MODIFICATION HISTORY - Created by Ganesh Vaidee - 04/25/95 |
| |
| 30-SEP-96 Shiv Ragunat Bug 398344, Commented Out NOCOPY Updation of |
| Control_Count And Control_Amount in |
| ar_batches . |
| By definition - These 2 columns will no |
| longer will be updated. |
+===========================================================================*/
PROCEDURE modify_update_bat_rec( p_bat_id IN ar_batches.batch_id%TYPE,
p_cr_amount IN ar_cash_receipts.amount%TYPE,
p_status IN VARCHAR2 ) IS
l_bat_rec ar_batches%ROWTYPE;
arp_standard.debug( 'arp_reverse_receipt.modify_update_bat_rec() +');
arp_standard.debug('modify_update_bat_rec: ' || 'cr_count = '||to_char( p_cr_amount ) );
arp_standard.debug('modify_update_bat_rec: ' || 'cr_amount = '||to_char( l_bat_rec.control_amount ) );
SELECT *
INTO l_bat_rec
FROM ar_batches
WHERE batch_id = p_bat_id;
arp_standard.debug('modify_update_bat_rec: ' || 'No Batches associated with the receipt' );
arp_standard.debug('modify_update_bat_rec: ' ||
'EXCEPTION: arp_reverse_receipt.modify_update_bat_rec' );
arp_standard.debug('modify_update_bat_rec: ' || 'p_status = '||p_status );
arp_cr_batches_pkg.update_p( l_bat_rec );
arp_standard.debug( 'arp_reverse_receipt.modify_update_bat_rec() -');
arp_standard.debug('modify_update_bat_rec: ' ||
'EXCEPTION: arp_reverse_receipt.modify_update_bat_rec' );
END modify_update_bat_rec;
| procedure insert_line for Rel. 11. |
| 21-NOV-1997 Karen Murphy Bug 522837. |
| Removed TO_NUMBER( p_module_version ) |
| in calls to the transactions workbench |
| as this causes problems in environments |
| using different number formatting, e.g. Spain|
| and Germany. Passing NULL instead as this |
| functionality has not been implemented. |
| 09-FEB-1999 Debbie Jancis Modified for 11.5 BOE changes to pass the |
| trx_number if the dm_inherit_receipt_num_flag|
| is set |
| 17-FEB-1999 Ramakant Alat Updating the TRX_NUMBER with Document Number |
| if the COPY option is set at Bacth Source |
| 08-SEP-1999 J Rautiainen BugFix for bug 976703. Cursor customer_C was |
| split into two separate cursors for |
| performance reasons |
| 29-MAR-2000 V Crisostomo Bug 753554 : Modify method of selecting crh |
| to get ccids from |
| 12-APR-2000 Skoukunt Bug 1063133 : Added 2nd i/p parameter for the|
| cursor bill_to_customer_C |
+===========================================================================*/
PROCEDURE debit_memo_reversal(
p_cr_rec IN OUT NOCOPY ar_cash_receipts%ROWTYPE,
p_cc_id IN ra_cust_trx_line_gl_dist.code_combination_id%TYPE,
p_cust_trx_type_id IN ra_cust_trx_types.cust_trx_type_id%TYPE,
p_cust_trx_type IN ra_cust_trx_types.name%TYPE,
p_reversal_gl_date IN ar_cash_receipt_history.reversal_gl_date%TYPE,
p_reversal_date IN ar_cash_receipts.reversal_date%TYPE,
p_reversal_category IN ar_cash_receipts.reversal_category%TYPE,
p_reversal_reason_code IN
ar_cash_receipts.reversal_reason_code%TYPE,
p_reversal_comments IN ar_cash_receipts.reversal_comments%TYPE,
p_attribute_category IN ar_cash_receipts.attribute_category%TYPE,
p_attribute1 IN ar_cash_receipts.attribute1%TYPE,
p_attribute2 IN ar_cash_receipts.attribute2%TYPE,
p_attribute3 IN ar_cash_receipts.attribute3%TYPE,
p_attribute4 IN ar_cash_receipts.attribute4%TYPE,
p_attribute5 IN ar_cash_receipts.attribute5%TYPE,
p_attribute6 IN ar_cash_receipts.attribute6%TYPE,
p_attribute7 IN ar_cash_receipts.attribute7%TYPE,
p_attribute8 IN ar_cash_receipts.attribute8%TYPE,
p_attribute9 IN ar_cash_receipts.attribute9%TYPE,
p_attribute10 IN ar_cash_receipts.attribute10%TYPE,
p_attribute11 IN ar_cash_receipts.attribute11%TYPE,
p_attribute12 IN ar_cash_receipts.attribute12%TYPE,
p_attribute13 IN ar_cash_receipts.attribute13%TYPE,
p_attribute14 IN ar_cash_receipts.attribute14%TYPE,
p_attribute15 IN ar_cash_receipts.attribute15%TYPE,
p_dm_number OUT NOCOPY ar_payment_schedules.trx_number%TYPE,
p_dm_doc_sequence_value IN ra_customer_trx.doc_sequence_value%TYPE,
p_dm_doc_sequence_id IN ra_customer_trx.doc_sequence_id%TYPE,
p_status IN OUT NOCOPY VARCHAR2,
p_module_name IN VARCHAR2,
p_module_version IN VARCHAR2 ) IS
l_receipt_method_name ar_receipt_methods.name%TYPE;
SELECT su1.site_use_id site_use_id,
su1.cust_acct_site_id address_id
FROM hz_cust_site_uses su1,
hz_cust_acct_sites add1
WHERE add1.cust_account_id = l_customer_id
AND add1.cust_acct_site_id = su1.cust_acct_site_id
/* 02-JUN-2000 J Rautiainen BR Implementation
* The site can also be DRAWEE */
AND su1.site_use_code in ('BILL_TO','DRAWEE')
AND su1.site_use_id = nvl(l_site_use_id,su1.site_use_id)
ORDER BY su1.primary_flag desc;
SELECT DECODE( su2.site_use_id,
NULL, NULL, add2.cust_account_id ) ship_to_customer_id,
su2.site_use_id ship_to_site_use_id
FROM hz_cust_site_uses su2,
hz_cust_acct_sites add2
WHERE add2.cust_account_id = l_customer_id
AND add2.cust_acct_site_id = su2.cust_acct_site_id
AND su2.site_use_code = 'SHIP_TO'
ORDER BY su2.primary_flag desc;
SELECT rm.name,
cr.pay_from_customer,
cr.receipt_number,
currency_code,
exchange_rate_type,
exchange_rate,
exchange_date,
amount,
rm.dm_inherit_receipt_num_flag,
cr.legal_entity_id
INTO l_receipt_method_name,
l_customer_id,
l_receipt_number,
l_currency_code,
l_exchange_rate_type,
l_exchange_rate,
l_exchange_date,
l_amount,
l_dm_inherit_rcpt_num_flag,
l_legal_entity_id --5126184
FROM
ar_receipt_methods rm
, ar_cash_receipts cr
WHERE cr.cash_receipt_id = l_cr_id
AND rm.receipt_method_id = cr.receipt_method_id;
SELECT REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(replace(T.description,
'&'||'payment_number'||'&',
l_receipt_number),
'&'||'PAYMENT_NUMBER'||'&',
l_receipt_number),
'&'||'receipt_number'||'&',
l_receipt_number),
'&'||'RECEIPT_NUMBER'||'&',
l_receipt_number),
'&'||'payment_method'||'&',
l_receipt_method_name ),
'&'||'PAYMENT_METHOD'||'&',
l_receipt_method_name )
INTO l_description
FROM ar_memo_lines_b B ,ar_memo_lines_tl T
WHERE B.MEMO_LINE_ID = T.MEMO_LINE_ID
AND NVL(B.ORG_ID, -99) = NVL(T.ORG_ID, -99)
AND T.LANGUAGE = userenv('LANG')
AND mo_global.check_access(B.ORG_ID) = 'Y'
AND B.memo_line_id = 2;
SELECT max(cash_receipt_history_id)
INTO l_crhid
FROM ar_cash_receipt_history
WHERE cash_receipt_id = l_cr_id
AND status <> 'RISK_ELIMINATED';
SELECT account_code_combination_id,
bank_charge_account_ccid,
amount,
NVL(factor_discount_amount,0)
INTO l_rev1_cc_id,
l_rev2_cc_id,
l_rev1_amount,
l_rev2_amount
FROM ar_cash_receipt_history
WHERE cash_receipt_id = l_cr_id
AND cash_receipt_history_id = l_crhid;
select end_date_active
into l_term_end_date
from ra_terms where term_id = 5;
SELECT decode ( max(dummy), NULL, 'N','Y')
INTO l_does_it_exist
from dual
where exists (select trx_number from
ra_customer_trx, ra_cust_trx_types
where trx_number = l_ct_rec.trx_number
and batch_source_id = l_ct_rec.batch_source_id
and ra_customer_trx.cust_trx_type_id =
ra_cust_trx_types.cust_trx_type_id
and ra_cust_trx_types.type = 'DM');
arp_process_header.insert_header( p_module_name,
NULL, -- p_module_version
l_ct_rec, 'DM_REV', p_reversal_gl_date,
NULL, l_comm_rec, l_trx_number,
l_customer_trx_id,
l_commit_cust_trx_line_id,
l_rowid,
p_status,
p_cc_id );
arp_standard.debug('Debit_Memo_Reversal: Before calling arp_p_l.insert_line');
/* added two new NULL parameters to call to insert_line, due to spec
changes in transaction package (OSTEINME, 8/19/97) */
-- 11/21/97 Karen Murphy
-- Bug 522837
-- Removed TO_NUMBER( p_module_version ) as this causes problems
-- in envrionments using different number formatting, e.g. Spain
-- and Germany. Passing NULL instead as this functionality has
-- not been implemented.
arp_process_line.insert_line( p_module_name,
NULL, -- p_module_version
l_ct_lines_rec,
NULL,
l_customer_trx_line_id,
'DM_REV',
l_rev1_cc_id,
l_rev2_cc_id,
l_rev1_amount,
l_rev2_amount,
l_rule_start_date,
l_accounting_rule_duration,
l_gl_date_dummy,
l_trx_date_dummy,
NULL, -- added for Rel. 11
NULL, -- added for Rel. 11
l_status_dummy );
arp_standard.debug('debit_memo_reversal: ' || 'l_status_dummy after insert_line:' || l_status_dummy);
SELECT
NVL(copy_doc_number_flag, 'N')
INTO
l_copy_doc_number_flag
FROM
ra_batch_sources
WHERE
batch_source_id = l_ct_rec.batch_source_id;
/* Bug3328690 To update the reversed cash_receipt id in ra_customer_trx */
/* Bug3347452 To Type Cast explicit p_dm_sequence_value to character
in NVL as it raises -ORA-01722 invalid number */
IF l_copy_doc_number_flag = 'Y' THEN
UPDATE ra_customer_trx
SET DOC_SEQUENCE_VALUE = p_dm_doc_sequence_value,
DOC_SEQUENCE_ID = p_dm_doc_sequence_id,
/* Bug3347452 */
/*TRX_NUMBER = NVL(p_dm_doc_sequence_value, TRX_NUMBER),*/
TRX_NUMBER = NVL(TO_CHAR(p_dm_doc_sequence_value),TRX_NUMBER),
OLD_TRX_NUMBER = DECODE(p_dm_doc_sequence_value, null,
OLD_TRX_NUMBER,
TRX_NUMBER),
REVERSED_CASH_RECEIPT_ID=l_cr_id /*3328690 */
WHERE customer_trx_id = (
select customer_trx_id
from ar_payment_schedules
where class = 'DM'
and reversed_cash_receipt_id = l_cr_id
);
UPDATE ar_payment_schedules
/* Bug3347452 */
/*SET TRX_NUMBER = NVL(p_dm_doc_sequence_value, TRX_NUMBER)*/
SET TRX_NUMBER = NVL(TO_CHAR(p_dm_doc_sequence_value), TRX_NUMBER)
WHERE reversed_cash_receipt_id = l_cr_id;
UPDATE ra_customer_trx
SET DOC_SEQUENCE_VALUE = p_dm_doc_sequence_value,
DOC_SEQUENCE_ID = p_dm_doc_sequence_id,
REVERSED_CASH_RECEIPT_ID=l_cr_id /*3328690 */
WHERE customer_trx_id = (
select customer_trx_id
from ar_payment_schedules
where class = 'DM'
and reversed_cash_receipt_id = l_cr_id
);
SELECT trx_number
INTO l_dm_number
FROM ar_payment_schedules
WHERE reversed_cash_receipt_id = l_cr_id;
update_current_cr_rec( p_cr_rec, p_reversal_category,
p_reversal_date, p_reversal_reason_code,
p_reversal_comments,
p_attribute_category, p_attribute1,
p_attribute2, p_attribute3, p_attribute4,
p_attribute5, p_attribute6, p_attribute7,
p_attribute8, p_attribute9, p_attribute10,
p_attribute11, p_attribute12, p_attribute13,
p_attribute14, p_attribute15 );
SELECT count(*)
FROM ar_receivable_applications
WHERE cash_receipt_id = p_cr_id
AND display = 'Y'
AND applied_payment_schedule_id = DECODE(p_include_trx_claims,
'N',-4,applied_payment_schedule_id)
AND application_ref_type = 'CLAIM';
SELECT secondary_application_ref_id
FROM ar_receivable_applications
WHERE cash_receipt_id = p_cr_id
AND display = 'Y'
AND applied_payment_schedule_id = DECODE(p_include_trx_claims,
'N',-4,applied_payment_schedule_id)
AND application_ref_type = 'CLAIM';
| jbeckett 28-FEB-03 Bug 2751910 - update claims to 0 instead of cancelling |
+===========================================================================*/
PROCEDURE cancel_claims (p_cr_id IN NUMBER,
p_include_trx_claims IN VARCHAR2,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2)
IS
l_claim_id NUMBER;
SELECT secondary_application_ref_id,
application_ref_num,
applied_payment_schedule_id,
applied_customer_trx_id,
amount_applied,
apply_date
FROM ar_receivable_applications
WHERE cash_receipt_id = p_cr_id
AND display = 'Y'
AND applied_payment_schedule_id = DECODE(p_include_trx_claims,
'N',-4,applied_payment_schedule_id)
AND application_ref_type = 'CLAIM';
SELECT amount_due_remaining
INTO l_claim_amount
FROM ar_payment_schedules
WHERE payment_schedule_id = l_invoice_ps_id;
arp_process_application.update_claim(
p_claim_id => l_claim_id
, p_invoice_ps_id => l_invoice_ps_id
, p_customer_trx_id => c1.applied_customer_trx_id
, p_amount => l_claim_amount
, p_amount_applied => c1.amount_applied
, p_apply_date => c1.apply_date
, p_cash_receipt_id => p_cr_id
, p_receipt_number => null
, p_action_type => 'U'
, x_claim_reason_code_id => l_claim_reason_code_id
, x_claim_reason_name => l_claim_reason_name
, x_claim_number => l_claim_number
, x_return_status => x_return_status
, x_msg_count => x_msg_count
, x_msg_data => x_msg_data);
SELECT count(*)
FROM ar_receivable_applications
WHERE cash_receipt_id = p_cr_id
AND display = 'Y'
AND application_ref_type = 'CLAIM';
SELECT ps.cash_receipt_id, app.amount_applied
FROM ar_receivable_applications app,
ar_payment_schedules ps
WHERE app.applied_payment_schedule_id = ps.payment_schedule_id
AND app.cash_receipt_id = p_cr_id
AND app.display = 'Y'
AND app.receivables_trx_id = -16;
SELECT application_ref_id
FROM ar_receivable_applications
WHERE cash_receipt_id = p_cr_id
AND display = 'Y'
AND applied_payment_schedule_id = -8;
select 'Y'
from ar_cash_receipts cr
where cr.payment_trxn_extension_id = p_extension_id
and exists (
Select 1 from xla_transaction_entities
where entity_code = 'RECEIPTS'
and nvl(source_id_int_1 , -99) = cr.cash_receipt_id
and application_id = 222
and ledger_id = cr.set_of_books_id
and upg_batch_id is not null ) ;
SELECT summ.status
FROM iby_fndcpt_tx_operations op, iby_trxn_summaries_all summ
WHERE op.trxn_extension_id = p_extension_id
AND op.transactionid = summ.transactionid
AND summ.reqtype in ('ORAPMTCAPTURE', 'ORAPMTRETURN',
'ORAPMTCREDIT', 'ORAPMTVOID', 'ORAPMTBATCHREQ', 'ORAPMTEFTCLOSEBATCH')
ORDER BY summ.trxnmid desc;