[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
based on the parameters entered. It then inserts one row for each invoice,
credit memo, unapplied receipts,on account receipts and uncleared receipts
used for calculating the customer balance into the table AR_CUSTOMER_BALANCE_ITF
--------------------------------------------------------------------------------*/
/* bug2466471 : Re-create PROCEDURE ar_get_customer_balance.
At first, get amount_due_original of ps of transactions.
After that, get application and adjustment information of which gl_date is earliner than p_as_of_date in order to get invoice balance as of p_as_of_date.
Also, get unapplied and on-account receipts of which gl_date is earlier than p_as_of_date. (Of course, don't get application , unapplied and on-account information of uncleared receipt if p_uncleared_receipts is not 'Y'.)
And get customer balance on basis of unapplied and on-account receipts and invoice balance as of p_as_of_date.
Finally, insert these record into AR_CUSTOMER_BALANCE_ITF table.
*/
/* bug 2657118 Changed the logic, instead of comparing as_of_date with gl_date of
transactions and as well as receipts , we'll now compare as_of_date with trx_date and apply_date whatever may be applicable.
*/
PROCEDURE ar_get_customer_balance ( p_request_id in number
,p_set_of_books_id in number
,p_as_of_date in date
,p_customer_name_from in varchar
,p_customer_name_to in varchar
,P_CUSTOMER_NUMBER_LOW in varchar
,P_CUSTOMER_NUMBER_HIGH in varchar
,p_currency in varchar
,p_min_invoice_balance in number
,p_min_open_balance in number
,p_account_credits varchar
,p_account_receipts varchar
,p_unapp_receipts varchar
,p_uncleared_receipts varchar
,p_ref_no varchar
,p_debug_flag in varchar
,p_trace_flag in varchar
) is
l_organization_name gl_sets_of_books.name%TYPE;
SELECT cust_acct.cust_account_id customer_id ,
cust_acct.account_number ,
party.party_name ,
party.tax_reference
FROM hz_cust_accounts cust_acct,
hz_parties party
WHERE cust_acct.party_id = party.party_id
AND (p_customer_name_from is null or
upper(party.party_name) >= upper(p_customer_name_from))
AND (p_customer_name_to is null or
upper(party.party_name) <= upper(p_customer_name_to))
AND (p_customer_number_low is null or
upper(cust_acct.account_number) >= upper(p_customer_number_low))
AND (p_customer_number_high is null or
upper(cust_acct.account_number) <= upper(p_customer_number_high));
SELECT site_uses.site_use_id ,
acct_site.translated_customer_name,
loc.address1,
loc.address2,
loc.address3,
loc.address4,
loc.city,
loc.state,
loc.postal_code,
loc.country
FROM hz_cust_acct_sites acct_site,
hz_party_sites party_site,
hz_locations loc,
hz_cust_site_uses site_uses
WHERE acct_site.cust_account_id =p_customer_id
AND nvl(acct_site.status,'A') = 'A'
AND acct_site.bill_to_flag in ( 'Y', 'P' )
AND acct_site.party_site_id = party_site.party_site_id
AND loc.location_id = party_site.location_id
AND nvl(site_uses.status,'A') = 'A'
AND acct_site.cust_acct_site_id = site_uses.cust_acct_site_id
AND site_uses.site_use_code = 'BILL_TO'
AND nvl(loc.language,p_base_language)=p_session_language;
SELECT distinct(invoice_currency_code) currency_code
FROM ar_payment_schedules ps
WHERE ps.customer_id=p_customer_id
AND PS.customer_site_use_id=p_site_use_id
AND ps.invoice_currency_code=nvl(p_currency,ps.invoice_currency_code);
SELECT payment_schedule_id ,
class,
trx_number ,
trx_date ,
invoice_currency_code,
amount_due_original
FROM ar_payment_schedules ps
WHERE TRUNC(ps.trx_date) <= p_as_of_date
AND ps.class not in ( 'PMT' ,decode(p_account_credits, 'Y', 'PMT','CM') )
AND ps.invoice_currency_code=p_currency
AND ps.customer_id= p_customer_id
AND PS.customer_site_use_id=p_site_use_id
AND ps.actual_date_closed > p_as_of_date ;
SELECT substrb(userenv('LANG'),1,4)
INTO l_session_language
FROM dual;
SELECT language_code
INTO l_base_language
FROM fnd_languages
WHERE installed_flag='B';
SELECT sob.name
INTO l_organization_name
FROM gl_sets_of_books sob,ar_system_parameters ar
WHERE sob.set_of_books_id = ar.set_of_books_id;
SELECT currency_code
INTO l_functional_currency_code
FROM gl_sets_of_books sob,ar_system_parameters ar
WHERE sob.set_of_books_id = ar.set_of_books_id;
SELECT NVL(SUM(amount_applied) , 0 ) amount_applied
INTO l_amount_applied_cm /*bug4502121*/
FROM ar_receivable_applications
WHERE payment_schedule_id = tot_inv_rec.payment_schedule_id
AND apply_date <= p_as_of_date
AND status||'' = 'APP';
SELECT NVL(SUM(amount_applied) , 0 ) amount_applied,
NVL(SUM(earned_discount_taken) ,0) earned_discount_taken,
NVL(SUM(unearned_discount_taken) ,0) unearned_discount_taken
INTO l_amount_applied
, l_earned_discount_taken
, l_unearned_discount_taken
FROM ar_receivable_applications ra
WHERE applied_payment_schedule_id = tot_inv_rec.payment_schedule_id
AND apply_date <= p_as_of_date
AND status||'' = 'APP'
AND application_type= 'CASH'
AND NOT EXISTS (
SELECT 'reversed'
FROM ar_cash_receipt_history crh
WHERE ra.cash_receipt_id = crh.cash_receipt_id
AND crh.status = 'REVERSED'
AND crh.trx_date+0 <= p_as_of_date ) ;
SELECT NVL(SUM(amount_applied) , 0 ) amount_applied,
NVL(SUM(earned_discount_taken) ,0) earned_discount_taken,
NVL(SUM(unearned_discount_taken) ,0) unearned_discount_taken
INTO l_amount_applied
, l_earned_discount_taken
, l_unearned_discount_taken
FROM ar_receivable_applications ra
WHERE applied_payment_schedule_id = tot_inv_rec.payment_schedule_id
AND apply_date<= p_as_of_date
AND status||'' = 'APP'
AND application_type= 'CASH'
AND NOT EXISTS (
SELECT 'reversed'
FROM ar_cash_receipt_history crh
WHERE ra.cash_receipt_id = crh.cash_receipt_id
AND crh.status = 'REVERSED'
AND crh.trx_date+0 <= p_as_of_date )
AND EXISTS (
SELECT 'cleared'
FROM ar_cash_receipt_history crh
WHERE ra.cash_receipt_id = crh.cash_receipt_id
AND crh.status = 'CLEARED'
AND crh.trx_date+0 <= p_as_of_date ) ;
SELECT NVL(SUM(amount_applied) , 0 ) amount_applied
INTO l_amount_credited
FROM ar_receivable_applications
WHERE applied_payment_schedule_id = tot_inv_rec.payment_schedule_id
AND apply_date <= p_as_of_date
AND status||'' = 'APP'
AND application_type= 'CM' ;
SELECT NVL(SUM(amount),0)
INTO l_amount_adj
FROM ar_adjustments
WHERE payment_schedule_id = tot_inv_rec.payment_schedule_id
AND apply_date+0 <= p_as_of_date
AND status = 'A';
INSERT INTO AR_CUSTOMER_BALANCE_ITF(Request_id,
as_of_date,
organization_name,
functional_currency_code,
customer_name,
customer_number,
tax_reference_num,
address_line1,
address_line2,
address_line3,
address_line4,
city,
state,
zip,
country,
trans_type,
trx_number,
transaction_date,
trx_currency_code,
trans_amount,
trans_amount_remaining,
receipt_amount,
adjustment_amount,
earned_discount_amount,
unearned_discount_amount,
invoice_credit_amount,
bank_charge,
on_account_credit_amount,
on_account_receipts,
unapplied_receipts)
VALUES (p_request_id,
p_as_of_date,
l_organization_name,
l_functional_currency_code,
nvl(siteinfo_rec.translated_customer_name,cusinfo_rec.party_name),
cusinfo_rec.account_number,
cusinfo_rec.tax_reference,
siteinfo_rec.address1,
siteinfo_rec.address2,
siteinfo_rec.address3,
siteinfo_rec.address4,
siteinfo_rec.city,
siteinfo_rec.state,
siteinfo_rec.postal_code,
siteinfo_rec.country,
tot_inv_rec.class ,
tot_inv_rec.trx_number,
tot_inv_rec.trx_date,
tot_inv_rec.invoice_currency_code ,
decode(tot_inv_rec.class , 'CM', 0, nvl(tot_inv_rec.amount_due_original,0)) ,
decode(tot_inv_rec.class , 'CM', 0, nvl(l_inv_open_bal,0) ) ,
nvl(l_amount_applied,0),
nvl(l_amount_adj,0),
nvl(l_earned_discount_taken ,0),
nvl(l_unearned_discount_taken ,0),
nvl(l_amount_credited,0),
0,
decode(tot_inv_rec.class , 'CM', nvl(l_inv_open_bal ,0) , 0 ) ,
0,
0 ) ;
SELECT nvl(sum(decode(ra.status , 'ACC', amount_applied, 0 )),0),
nvl(sum(decode(ra.status ,'UNAPP',amount_applied, 0 )),0)
INTO l_on_acct_receipts ,
l_unapp_receipts
FROM ar_receivable_applications ra,
ar_cash_receipts cr
WHERE ra.cash_receipt_id = cr.cash_receipt_id
AND cr.pay_from_customer = cusinfo_rec.customer_id
AND cr.customer_site_use_id = siteinfo_rec.site_use_id
AND cr.currency_code = currency_rec.currency_code
AND ra.apply_date+0 <= p_as_of_date
AND ra.status in ('ACC' , 'UNAPP' )
AND NOT EXISTS (
SELECT 'reversed'
FROM ar_cash_receipt_history crh
WHERE ra.cash_receipt_id = crh.cash_receipt_id
AND crh.status = 'REVERSED'
AND crh.trx_date+0 <= p_as_of_date ) ;
SELECT nvl(sum(decode(ra.status , 'ACC', amount_applied, 0 )),0),
nvl(sum(decode(ra.status , 'UNAPP', amount_applied, 0)),0)
INTO l_on_acct_receipts ,
l_unapp_receipts
FROM ar_receivable_applications ra,
ar_cash_receipts cr
WHERE ra.cash_receipt_id = cr.cash_receipt_id
AND cr.pay_from_customer = cusinfo_rec.customer_id
AND cr.currency_code = currency_rec.currency_code
AND cr.customer_site_use_id = siteinfo_rec.site_use_id
AND apply_date+0 <= p_as_of_date
AND ra.status in ('ACC' , 'UNAPP' )
AND NOT EXISTS (
SELECT 'reversed'
FROM ar_cash_receipt_history crh
WHERE ra.cash_receipt_id = crh.cash_receipt_id
AND crh.status = 'REVERSED'
AND crh.trx_date+0 <= p_as_of_date )
AND EXISTS (
SELECT 'cleared'
FROM ar_cash_receipt_history crh
WHERE ra.cash_receipt_id = crh.cash_receipt_id
AND crh.status = 'CLEARED'
AND crh.trx_date+0 <= p_as_of_date ) ;
INSERT INTO AR_CUSTOMER_BALANCE_ITF(Request_id,
as_of_date,
organization_name,
functional_currency_code,
customer_name,
customer_number,
tax_reference_num,
address_line1,
address_line2,
address_line3,
address_line4,
city,
state,
zip,
country,
trans_type,
trx_number,
transaction_date,
trx_currency_code,
trans_amount,
trans_amount_remaining,
receipt_amount,
adjustment_amount,
earned_discount_amount,
unearned_discount_amount,
invoice_credit_amount,
bank_charge,
on_account_credit_amount,
on_account_receipts,
unapplied_receipts)
VALUES (p_request_id,
p_as_of_date,
l_organization_name,
l_functional_currency_code,
nvl(siteinfo_rec.translated_customer_name,cusinfo_rec.party_name),
cusinfo_rec.account_number,
cusinfo_rec.tax_reference,
siteinfo_rec.address1,
siteinfo_rec.address2,
siteinfo_rec.address3,
siteinfo_rec.address4,
siteinfo_rec.city,
siteinfo_rec.state,
siteinfo_rec.postal_code,
siteinfo_rec.country,
'PMT' ,
'On Account Receipt' ,
p_as_of_date,
currency_rec.currency_code,
0,
0,
0,
0,
0,
0,
0,
0,
0,
l_on_acct_receipts*(-1),
l_unapp_receipts*(-1) ) ;