[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
| Procedure to create a deduction claim in Trade Management and update
| receivable applications, appropriately with Deduction ID, deduction
| number and customer reason code. Handles Non Trx realted claims
| and On Transaction related claims (short pays).
| This procedure is initiated by the Post Batch process.
|
| CALLED FROM PROCEDURES/FUNCTIONS
| arccbp.lpc (Postbatch)
|
| CALLS PROCEDURES/FUNCTIONS
| arp_process_application.create_claim
| app_exception.invalid_argument
|
| PARAMETERS
| IN : p_request_id
| p_matched_claim_creation_flag
| p_matched_claim_excl_cm_flag
| OUT : x_return_status
|
| KNOWN ISSUES
| This procedure will not create Cross Currency Claims.
|
| NOTES
| This procedure should only do claim creation for those records
| which have been created by postbatch during this request id,
| Since the receipt api is called during the postbatch process, it
| is possible that the receipt api will create 'OTHER ACC' (Claim
| Investigation Applications), however we do not want to create
| a claim for those records. To deal with this, we are explicitly
| looking for claims created with an application rule = 90 (ie.
| OTHER ACC records which have come from postbatch)
|
| MODIFICATION HISTORY
| Date Author Description of Changes
| 19-DEC-2002 cthangai Created
| 07-FEB-2003 cthangai Added parameters customer_reason,
| x_claim_reason_name to the routine call
| arp_process_application.create_claim
| Also modified the cursors to fetch RA info
| and associated composite data structures
| 17-FEB-2003 cthangai Added qualifier to exclude claim processing
| for class = 'PMT'
| 24-FEB-2003 cthangai Qualified cursor get_ra_info by request_id
| 26-FEB-2003 cthangai Pass amount_applied to TM for claim creation
| 27-FEB-2003 cthangai Added OUT parameter x_return_status
| 03-MAR-2003 cthangai Qualified the update that stamps TRX based claims
| with claim info by applied_payment_schedule_id
| and applied_customer_trx_id
| Qaulified the get_trx_app_info cursor by request_id
| 05-MAR-2003 cthangai Fail Recover - Full recover when the initial UNAPP
| amount_applied is less than the sum of
| amount applied for the paired UNAPP records
| else partial recover.
| 14-OCT-2005 jbeckett Bug 4565758 - AR/TM legal entity uptake
*=======================================================================*/
PROCEDURE claim_creation
(p_request_id IN ar_receivable_applications.request_id%TYPE DEFAULT NULL
,p_matched_claim_creation_flag IN ar_system_parameters.matched_claim_creation_flag%TYPE
,p_matched_claim_excl_cm_flag IN ar_system_parameters.matched_claim_excl_cm_flag%TYPE
,x_return_status OUT NOCOPY VARCHAR2
)IS
--Primary cursor for processing Non TRX based claims
CURSOR get_claim_rec IS
SELECT
ra.rowid
,ra.receivable_application_id
,ra.amount_applied
,ra.payment_schedule_id
,ra.applied_payment_schedule_id
,ra.applied_customer_trx_id
,ra.comments
,ra.attribute_category
,ra.attribute1
,ra.attribute2
,ra.attribute3
,ra.attribute4
,ra.attribute5
,ra.attribute6
,ra.attribute7
,ra.attribute8
,ra.attribute9
,ra.attribute10
,ra.attribute11
,ra.attribute12
,ra.attribute13
,ra.attribute14
,ra.attribute15
,ra.application_ref_num
,ra.secondary_application_ref_id
,ra.application_ref_reason
,ra.customer_reason
,ra.customer_reference
,NULL -- x_return_status
,NULL -- x_msg_count
,NULL -- x_msg_data
,NULL -- x_claim_reason_name
,apply_date -- bug 5495310
FROM ar_receivable_applications ra
WHERE ra.application_ref_type = 'CLAIM'
AND ra.status = 'OTHER ACC'
AND ra.application_ref_num IS NULL
AND ra.application_rule = 90
AND ra.request_id = p_request_id;
SELECT
ra.receivable_application_id
,ra.amount_applied
,ra.payment_schedule_id
,ra.applied_payment_schedule_id
,ra.applied_customer_trx_id
,ra.comments
,ra.attribute_category
,ra.attribute1
,ra.attribute2
,ra.attribute3
,ra.attribute4
,ra.attribute5
,ra.attribute6
,ra.attribute7
,ra.attribute8
,ra.attribute9
,ra.attribute10
,ra.attribute11
,ra.attribute12
,ra.attribute13
,ra.attribute14
,ra.attribute15
,ra.application_ref_num
,ra.secondary_application_ref_id
,ra.application_ref_reason
,ra.customer_reason
,ra.customer_reference
,NULL -- x_return_status
,NULL -- x_msg_count
,NULL -- x_msg_data
,NULL -- x_claim_reason_name
FROM ar_receivable_applications ra
WHERE ra.applied_payment_schedule_id = p_trx_ps_id
AND ra.request_id = p_request_id
AND ra.display = 'Y'
AND ra.receivable_application_id = (
select max(ra1.receivable_application_id)
from ar_receivable_applications ra1
where ra1.applied_payment_schedule_id
= ra.applied_payment_schedule_id);
SELECT distinct ra.applied_payment_schedule_id
FROM ar_receivable_applications ra
WHERE ra.applied_payment_schedule_id NOT IN (-4,-1)
AND ra.status = 'APP'
AND ra.display = 'Y'
AND ra.request_id = p_request_id;
SELECT ct.customer_trx_id --customer_trx_id
,ct.trx_number --trx_number
,ct.cust_trx_type_id --trx_type_id
,ct.invoice_currency_code --currency_code
,ct.exchange_rate_type --exchange_rate_type
,ct.exchange_date --exchange_date
,ct.exchange_rate --exchange_rate
,ct.bill_to_customer_id --customer_id
,ct.bill_to_site_use_id --bill_to_site_use_id
,ct.ship_to_site_use_id --ship_to_site_use_id
,ct.primary_salesrep_id --salesrep_id
,ps.amount_due_remaining --amount_due_remaining
,ps.amount_due_original --amount_due_original
,ps.class --class
,ps.active_claim_flag --active_claim_flag
,ct.legal_entity_id
FROM ra_customer_trx ct
,ar_payment_schedules ps
WHERE ct.customer_trx_id = ps.customer_trx_id
AND ps.payment_schedule_id = p_trx_ps_id
AND ps.class <> 'PMT';
SELECT ps.cash_receipt_id --cash_receipt_id
,cr.receipt_number --receipt_number
,cr.currency_code --currency_code
,cr.exchange_rate_type --exchange_rate_type
,cr.exchange_date --exchange_date
,cr.exchange_rate --exchange_rate
,cr.pay_from_customer --customer_id
,cr.customer_site_use_id --bill_to_site_use_id
,NULL --ship_to_site_use_id
,cr.legal_entity_id
FROM ar_payment_schedules ps
,ar_cash_receipts cr
,ar_cash_receipt_history crh
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';
SELECT ps.cash_receipt_id --cash_receipt_id
,cr.receipt_number --receipt_number
FROM ar_payment_schedules ps
,ar_cash_receipts cr
WHERE ps.payment_schedule_id = p_receipt_ps_id
AND cr.cash_receipt_id = ps.cash_receipt_id;
SELECT SUM(ra.amount_applied), MAX(ra.apply_date)
FROM ar_receivable_applications ra
WHERE ra.applied_payment_schedule_id = p_trx_ps_id
AND ra.request_id = p_request_id
AND ra.status = 'APP'
AND ra.display = 'Y';
select ra.amount_applied
from ar_receivable_applications ra
,ar_distributions ard
where ra.cash_receipt_id = p_cr_id
and ra.status = 'UNAPP'
and nvl(ra.confirmed_flag,'Y') = 'Y'
and ra.receivable_application_id = ard.source_id
and ard.source_table = 'RA'
and ard.source_id_secondary IS NULL;
select sum(ra.amount_applied)
from ar_receivable_applications ra
,ar_distributions ard
where ra.cash_receipt_id = p_cr_id
and ra.status = 'UNAPP'
and nvl(ra.confirmed_flag,'Y') = 'Y'
and ra.receivable_application_id = ard.source_id
and ard.source_table = 'RA'
and ard.source_id_secondary IS NOT NULL;
select ra.receivable_application_id
from ar_receivable_applications ra
where ra.cash_receipt_id = p_cr_id
and ra.status <> 'UNAPP'
and nvl(ra.confirmed_flag,'Y') = 'Y';
jg_ar_receivable_applications.delete_interest_adjustment(
p_cash_receipt_id => l_cash_receipt_id,
x_return_status => jg_return_status);
arp_standard.debug('Error from jg_ar_receivable_applications.delete_interst_adjustment');
arp_deduction.update_claim_create_status
(p_ps_id => claim_tbl.payment_schedule_id(i)
,p_claim_status => l_claim_status
);
arp_standard.debug('Bulk Update RA with DED No, ID and translated oracle reason');
UPDATE ar_receivable_applications
SET secondary_application_ref_id = claim_tbl.secondary_application_ref_id(i)
,application_ref_num = claim_tbl.application_ref_num(i)
WHERE rowid = claim_tbl.l_rowid(i);
arp_deduction.update_claim
(p_claim_id => claim_rec.secondary_application_ref_id
,p_claim_number => claim_rec.application_ref_num
,p_amount => l_claim_amount
,p_currency_code => l_currency_code
,p_exchange_rate_type => l_exchange_rate_type
,p_exchange_rate_date => l_exchange_date
,p_exchange_rate => l_exchange_rate
,p_customer_trx_id => l_customer_trx_id
,p_invoice_ps_id => claim_rec.applied_payment_schedule_id
,p_cust_trx_type_id => l_cust_trx_type_id
,p_trx_number => l_trx_number
,p_cust_account_id => l_customer_id
,p_bill_to_site_id => l_bill_to_site_use_id
,p_ship_to_site_id => l_ship_to_site_use_id
,p_salesrep_id => l_salesrep_id
,p_customer_ref_date => NULL
,p_customer_ref_number => claim_rec.customer_reference
,p_cash_receipt_id => l_cash_receipt_id
,p_receipt_number => l_receipt_number
,p_reason_id => to_number(claim_rec.application_ref_reason)
,p_comments => claim_rec.comments
,p_attribute_category => claim_rec.attribute_category
,p_attribute1 => claim_rec.attribute1
,p_attribute2 => claim_rec.attribute2
,p_attribute3 => claim_rec.attribute3
,p_attribute4 => claim_rec.attribute4
,p_attribute5 => claim_rec.attribute5
,p_attribute6 => claim_rec.attribute6
,p_attribute7 => claim_rec.attribute7
,p_attribute8 => claim_rec.attribute8
,p_attribute9 => claim_rec.attribute9
,p_attribute10 => claim_rec.attribute10
,p_attribute11 => claim_rec.attribute11
,p_attribute12 => claim_rec.attribute12
,p_attribute13 => claim_rec.attribute13
,p_attribute14 => claim_rec.attribute14
,p_attribute15 => claim_rec.attribute15
,p_applied_date => l_applied_date
,p_applied_action_type => l_applied_action_type
,p_amount_applied => l_amount_applied
,p_applied_receipt_id => l_applied_receipt_id
,p_applied_receipt_number=> l_applied_receipt_number
,x_return_status => claim_rec.return_status
,x_msg_count => claim_rec.msg_count
,x_msg_data => claim_rec.msg_data
,x_object_version_number => l_object_version_number
,x_claim_reason_code_id => l_claim_reason_code_id
,x_claim_reason_name => l_claim_reason_name
,x_claim_id => claim_rec.secondary_application_ref_id
,x_claim_number => claim_rec.application_ref_num
);
arp_standard.debug('Update PS with active_claim_flag = C');
arp_deduction.update_claim_create_status
(p_ps_id => claim_rec.applied_payment_schedule_id
,p_claim_status => l_claim_status
);
arp_standard.debug('Update RA with Ded No,Ded ID,Type,receivables_trx_id');
UPDATE ar_receivable_applications
SET secondary_application_ref_id = claim_rec.secondary_application_ref_id
,application_ref_num = claim_rec.application_ref_num
,application_ref_type = 'CLAIM'
,receivables_trx_id = ARP_DEDUCTION.GET_RECEIVABLES_TRX_ID(l_cash_receipt_id)
WHERE applied_payment_schedule_id = claim_rec.applied_payment_schedule_id
AND applied_customer_trx_id = claim_rec.applied_customer_trx_id;
| update_claim |
| |
| DESCRIPTION |
| Calls iClaim group API to update a deduction claim. |
| |
| SCOPE - PUBLIC |
| |
| EXETERNAL PROCEDURES/FUNCTIONS ACCESSED - |
| OZF_Claim_GRP.Update_Deduction - Group API to update a claim from AR |
| |
| ARGUMENTS : IN: |
| OUT: X_RETURN_STATUS ('S' for success, 'E' or 'U' for Error |
| |
| RETURNS : NONE |
| |
| |
| MODIFICATION HISTORY |
| CTHANGAI 03-FEB-2003 Created |
| CTHANGAI 27-FEB-2003 Added OUT parameters x_claim_id,x_claim_number |
+===========================================================================*/
PROCEDURE update_claim
( p_claim_id IN NUMBER
, p_claim_number IN VARCHAR2
, p_amount IN NUMBER
, p_currency_code IN VARCHAR2
, p_exchange_rate_type IN VARCHAR2
, p_exchange_rate_date IN DATE
, p_exchange_rate IN NUMBER
, p_customer_trx_id IN NUMBER
, p_invoice_ps_id IN NUMBER
, p_cust_trx_type_id IN NUMBER
, p_trx_number IN VARCHAR2
, p_cust_account_id IN NUMBER
, p_bill_to_site_id IN NUMBER
, p_ship_to_site_id IN NUMBER
, p_salesrep_id IN NUMBER
, p_customer_ref_date IN DATE
, p_customer_ref_number IN VARCHAR2
, p_cash_receipt_id IN NUMBER
, p_receipt_number IN VARCHAR2
, p_reason_id IN NUMBER
, p_comments IN VARCHAR2
, 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_applied_date IN DATE
, p_applied_action_type IN VARCHAR2
, p_amount_applied IN NUMBER
, p_applied_receipt_id IN NUMBER
, p_applied_receipt_number IN VARCHAR2
, x_return_status OUT NOCOPY VARCHAR2
, x_msg_count OUT NOCOPY NUMBER
, x_msg_data OUT NOCOPY VARCHAR2
, x_object_version_number OUT NOCOPY NUMBER
, x_claim_reason_code_id OUT NOCOPY NUMBER
, x_claim_reason_name OUT NOCOPY VARCHAR2
, x_claim_id OUT NOCOPY NUMBER
, x_claim_number OUT NOCOPY VARCHAR2
) IS
l_claim_rec OZF_Claim_GRP.Deduction_Rec_Type;
l_last_update_login NUMBER;
arp_standard.debug('update_action: ' || 'arp_deduction.update_claim()+' );
OZF_Claim_GRP.Update_Deduction
(p_api_version_number => 1.0
,p_init_msg_list => FND_API.G_TRUE
,p_commit => FND_API.G_FALSE
,x_return_status => l_return_status
,x_msg_count => x_msg_count
,x_msg_data => x_msg_data
,p_deduction => l_claim_rec
,x_object_version_number => x_object_version_number
,x_claim_reason_code_id => x_claim_reason_code_id
,x_claim_reason_name => x_claim_reason_name
,x_claim_id => x_claim_id
,x_claim_number => x_claim_number
);
arp_standard.debug('insert_trx_notes: arp_deduction.update_claim');
l_last_update_login := arp_standard.profile.last_update_login;
arp_notes_pkg.insert_cover(
p_note_type => 'MAINTAIN',
p_text => l_text,
p_customer_call_id => NULL,
p_customer_call_topic_id => NULL,
p_call_action_id => NULL,
p_customer_trx_id => p_customer_trx_id,
p_note_id => l_note_id,
p_last_updated_by => l_user_id,
p_last_update_date => l_sysdate,
p_last_update_login => l_last_update_login,
p_created_by => l_user_id,
p_creation_date => l_sysdate);
arp_standard.debug('insert_trx_notes: Note ID = '||to_char(l_note_id));
arp_standard.debug('put_trx_in_dispute: arp_deduction.update_claim');
arp_process_application.update_dispute_on_trx
(p_invoice_ps_id
,'Y' --p_active_claim
,p_amount_applied
);
arp_standard.debug('update_action: arp_deduction.update_claim()-');
arp_standard.debug('update_action: EXCEPTION: arp_deduction.update_claim');
END update_claim;
| Insert 2 RA rows - One as -ve UNAPP row and the second as 'OTHER ACC' |
| The on-account ACC row is similar to the 'OTHER ACC'. |
| This new procedure is introduced for creating special applications like|
| claim.The RA records for 'ACC' or 'OTHER ACC' are created first along |
| with thier corresponding distributions records. After which the |
| negative UNAPP record is created along with its PAIRED distribution |
| record. |
| |
| SCOPE - PUBLIC |
| |
| EXETERNAL PROCEDURES/FUNCTIONS ACCESSED - |
| arp_app_pkg.insert_p - Insert a row into RA table |
| arp_acct_main.Create_Acct_Entry - Insert a row into Distributions table |
| |
| ARGUMENTS : IN: |
| |
| OUT: x_return_status (S=Success; E=Error/Failure |
,p_last_updated_by IN ar_receivable_applications.last_updated_by%TYPE
,p_program_application_id IN ar_receivable_applications.program_application_id%TYPE
,p_program_id IN ar_receivable_applications.program_id%TYPE
,p_request_id IN ar_receivable_applications.request_id%TYPE
,p_sob_id IN ar_receivable_applications.set_of_books_id%TYPE
,p_apply_date IN ar_receivable_applications.apply_date%TYPE
,p_ussgl_transaction_code IN ar_receivable_applications.ussgl_transaction_code%TYPE
,p_receipt_ps_id IN ar_receivable_applications.payment_schedule_id%TYPE
,p_unapp_application_rule IN ar_receivable_applications.application_rule%TYPE
,p_other_application_rule IN ar_receivable_applications.application_rule%TYPE
,p_acc_application_rule IN ar_receivable_applications.application_rule%TYPE
,p_on_account_customer IN ar_receivable_applications.on_account_customer%TYPE
,p_receivables_trx_id IN ar_receivable_applications.receivables_trx_id%TYPE
,p_customer_reference IN ar_receivable_applications.customer_reference%TYPE
,p_customer_reason IN ar_receivable_applications.customer_reason%TYPE
,p_attribute_category IN ar_receivable_applications.attribute_category%TYPE
,p_attribute1 IN ar_receivable_applications.attribute1%TYPE
,p_attribute2 IN ar_receivable_applications.attribute2%TYPE
,p_attribute3 IN ar_receivable_applications.attribute3%TYPE
,p_attribute4 IN ar_receivable_applications.attribute4%TYPE
,p_attribute5 IN ar_receivable_applications.attribute5%TYPE
,p_attribute6 IN ar_receivable_applications.attribute6%TYPE
,p_attribute7 IN ar_receivable_applications.attribute7%TYPE
,p_attribute8 IN ar_receivable_applications.attribute8%TYPE
,p_attribute9 IN ar_receivable_applications.attribute9%TYPE
,p_attribute10 IN ar_receivable_applications.attribute10%TYPE
,p_attribute11 IN ar_receivable_applications.attribute11%TYPE
,p_attribute12 IN ar_receivable_applications.attribute12%TYPE
,p_attribute13 IN ar_receivable_applications.attribute13%TYPE
,p_attribute14 IN ar_receivable_applications.attribute14%TYPE
,p_attribute15 IN ar_receivable_applications.attribute15%TYPE
,x_return_status OUT NOCOPY VARCHAR2
) IS
l_ra_rec ar_receivable_applications%ROWTYPE;
l_ra_rec.last_updated_by := p_last_updated_by;
l_ra_rec.last_update_date := SYSDATE;
l_ra_rec.program_update_date := SYSDATE;
arp_app_pkg.insert_p( l_ra_rec, l_ra_rec.receivable_application_id );
ar_mrc_engine3.insert_ra_rec_quickcash(
p_rec_app_id => l_ra_rec.receivable_application_id);
l_ra_rec.receivable_application_id := NULL; --Sequence generated while insert
arp_app_pkg.insert_p( l_ra_rec, l_ra_rec.receivable_application_id );
| records for the claim are deleted. The payment schedule amounts are
| updated appropriately.
|
| CALLED FROM PROCEDURES/FUNCTIONS
| claim_creation
|
| CALLS PROCEDURES/FUNCTIONS
| arp_app_pkg.delete_p
| arp_acct_main.Delete_Acct_Entry(l_ae_doc_rec);
select ra.* --'APP' OR 'OTHER ACC'
from ar_receivable_applications ra
where ra.receivable_application_id = p_rapp_id
and nvl(ra.confirmed_flag,'Y') = 'Y'
and exists (select 'x'
from ar_distributions_all ard
where ard.source_table = 'RA'
and ard.source_id = ra.receivable_application_id)
UNION
select ra.* --'UNAPP'
from ar_receivable_applications ra
, ar_distributions ard
where ra.receivable_application_id = ard.SOURCE_ID
and nvl(ra.confirmed_flag,'Y') = 'Y'
and ard.source_table = 'RA'
and ard.source_id_secondary =
( select ra1.receivable_application_id
from ar_receivable_applications ra1
where ra1.receivable_application_id = p_rapp_id );
arp_acct_main.Delete_Acct_Entry(l_ae_doc_rec);
arp_app_pkg.delete_p(ln_ra_id);
p_event_mode => 'DELETE',
p_table_name => 'AR_RECEIVABLE_APPLICATIONS',
p_mode => 'SINGLE',
p_key_value => ln_ra_id);
arp_app_pkg.delete_p(ln_unapp_ra_id);
p_event_mode => 'DELETE',
p_table_name => 'AR_RECEIVABLE_APPLICATIONS',
p_mode => 'SINGLE',
p_key_value => ln_ra_id);
SELECT gl.currency_code
INTO l_currency_code
FROM gl_sets_of_books gl
,ar_system_parameters ar
WHERE gl.set_of_books_id = ar.set_of_books_id;
SELECT rt.receivables_trx_id
INTO l_receivables_trx_id
FROM ar_receivables_trx rt
WHERE rt.receivables_trx_id = (
SELECT rma.claim_receivables_trx_id
FROM ar_receipt_method_accounts rma, ar_cash_receipts cr
WHERE rma.receipt_method_id = cr.receipt_method_id
AND cr.cash_receipt_id = p_cash_receipt_id
AND rma.primary_flag = 'Y' );
| PUBLIC PROCEDURE UPDATE_CLAIM_CREATE_STATUS
|
| DESCRIPTION
| This function is called to update ar_payment_schedules,
| active_claim_flag column with the appropriate claim status returned
| from TM
|
| SCOPE - PUBLIC
|
| EXTERNAL PROCEDURE/FUNCTIONS ACCESSED - NONE
|
| ARGUMENTS :
| IN : p_ps_id --payment_schedule_id
| p_claim_status --claim status
|
| OUT: NONE
|
| NOTES :
|
| MODIFICATION HISTORY
| Date Author Description of Changes
| 21-JAN-2003 cthangai Update ar_payment_schdeules.active_claim_flag
| based on the claim status in TM
| DD-MON-YYYY Name Bug #####, modified amount ..
|
*=======================================================================*/
PROCEDURE UPDATE_CLAIM_CREATE_STATUS
(p_ps_id IN ar_payment_schedules.payment_schedule_id%type
,p_claim_status IN ar_payment_schedules.active_claim_flag%type
) IS
BEGIN
-------------------------
-- Update claim status
-------------------------
UPDATE ar_payment_schedules
SET active_claim_flag = p_claim_status
WHERE payment_schedule_id = p_ps_id;
arp_standard.debug('EXCEPTION: ARP_DEDUCTION.UPDATE_CLAIM_CREATE_STATUS');
END UPDATE_CLAIM_CREATE_STATUS;
SELECT amount_applied
INTO ln_amount_applied
FROM ar_receivable_applications
WHERE receivable_application_id = p_rapp_id;
SELECT sum(amount_applied)
INTO ln_amount_applied
FROM ar_receivable_applications
WHERE cash_receipt_id = p_cr_id
AND status = 'UNAPP';
' SELECT rc.name FROM ozf_reason_codes_vl rc, ozf_claims c '||
' WHERE c.reason_code_id = rc.reason_code_id '||
' AND c.claim_id = :claim_id ';
| updates to payment schedules and the synch up with Trade management
| if necessary.
| This function will write messages to the concurrent request output file
|
| SCOPE - PUBLIC
|
| EXTERNAL PROCEDURE/FUNCTIONS ACCESSED
|
| AR_RECEIPT_API_PUB.APPLY_OPEN_RECEIPT
|
| ARGUMENTS :
| IN : p_cash_receipt_id
| p_applied_payment_schedule_id
| p_open_rec_app_id
| p_amount_applied
| 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
|
| OUT: X_RETURN_STAUS (S=success, E=Error, U=Unidentified Error)
| x_receipt_number
| X_APPLY_TYPE (F=Full, P=Partial)
|
| NOTES
| This routine currently does not support receipt to receipt applications
| which are in a currency other then functional currency.
|
| MODIFICATION HISTORY
| Date Author Description of Changes
| 19-FEB-2003 cthangai Created
| Modify get_open_cr_id cursor to include receipt
| number by joining to ar_cash_receipts
| Modify cursor c1_validate to get amount_applied
| Modify cursor c2_validate to get
| sum(amount_applied)
| Amount_applied retreived is used in determining
| Full or Partial payment
| Add OUT parameters x_return_status, x_apply_type
| 03-MAR-2003 Debbie Jancis Added Comments and formatting. Fixed how
| x_apply_type was figuring out whether
| application was full or partial. For a
| receipt to receipt application, the sign
| of the the p_amount_applied variable will
| always be the opposite sign of the
| l_amount_applied value retrieved from the
| receivable apps record of the applied receipt.
| 07-MAR-2003 cthangai Added OUT parameter x_application_ref_num
| for the TM API call to apply_open_receipt
| local variable l_application_ref_num
|
*=======================================================================*/
PROCEDURE apply_open_receipt_cover
(p_cash_receipt_id IN ar_cash_receipts.cash_receipt_id%TYPE,
p_applied_payment_schedule_id IN ar_payment_schedules.payment_schedule_id%TYPE
,p_open_rec_app_id IN
ar_receivable_applications.receivable_application_id%TYPE
,p_amount_applied IN
ar_receivable_applications.amount_applied%TYPE
,p_attribute_category IN
ar_receivable_applications.attribute_category%TYPE
,p_attribute1 IN ar_receivable_applications.attribute1%TYPE
,p_attribute2 IN ar_receivable_applications.attribute2%TYPE
,p_attribute3 IN ar_receivable_applications.attribute3%TYPE
,p_attribute4 IN ar_receivable_applications.attribute4%TYPE
,p_attribute5 IN ar_receivable_applications.attribute5%TYPE
,p_attribute6 IN ar_receivable_applications.attribute6%TYPE
,p_attribute7 IN ar_receivable_applications.attribute7%TYPE
,p_attribute8 IN ar_receivable_applications.attribute8%TYPE
,p_attribute9 IN ar_receivable_applications.attribute9%TYPE
,p_attribute10 IN ar_receivable_applications.attribute10%TYPE
,p_attribute11 IN ar_receivable_applications.attribute11%TYPE
,p_attribute12 IN ar_receivable_applications.attribute12%TYPE
,p_attribute13 IN ar_receivable_applications.attribute13%TYPE
,p_attribute14 IN ar_receivable_applications.attribute14%TYPE
,p_attribute15 IN ar_receivable_applications.attribute15%TYPE
,x_return_status OUT NOCOPY VARCHAR2
,x_receipt_number OUT NOCOPY ar_cash_receipts.receipt_number%TYPE
,x_apply_type OUT NOCOPY VARCHAR2
) IS
--Fetch open_cash_receipt_id
CURSOR get_open_cr_id
(p_applied_ps_id ar_payment_schedules.payment_schedule_id%TYPE) IS
SELECT ps.cash_receipt_id, cr.receipt_number
FROM ar_payment_schedules ps
,ar_cash_receipts cr
WHERE ps.payment_schedule_id = p_applied_ps_id
AND ps.cash_receipt_id = cr.cash_receipt_id;
SELECT amount_applied --'Y'
FROM ar_receivable_applications
WHERE receivable_application_id = p_rapp_id
AND display = 'Y';
SELECT sum(amount_applied)
FROM ar_receivable_applications
WHERE cash_receipt_id = p_cr_id
AND status = 'UNAPP'
HAVING sum(amount_applied) >= p_amount_applied;
arp_standard.debug('update_action: arp_deduction.apply_open_receipt_cover()+');
arp_standard.debug('update_action: ARP_DEDUCTION.apply_open_receipt_cover()-');
SELECT nvl(active_claim_flag,'N')
INTO l_active_claim_flag
FROM ar_payment_schedules
WHERE payment_schedule_id = p_payment_schedule_id;