AR customer Statement (overdue payment last month)

Create or replace function amountfn
(
Customernum varchar2,
Startdate date
) Return char is
V_amount number;
V_receipt_amount number;
Refundamount number;
Lastdebt number;
Begin
**************-------------------------------
Select sum (nvl (RDBMS. extended_amount_tax, 0) extended_amount -- all payments before the current parameter date
Into v_amount
From (select ctl. customer_trx_line_id,
Ct. customer_trx_id,
Ct. initial_customer_trx_id,
Ct. trx_number,
Nvl (cux_ar_utl_pkg.get_trx_gl_date (ct. customer_trx_id ),
Ct. trx_date) trx_date,
Ct. bill_to_customer_id,
Nvl (ctl. gross_extended_amount, ctl. extended_amount) extended_amount_tax
From ra_customer_trx_all ct,
Ra_customer_trx_lines_all ctl,
Hz_parties hpt,
Hz_cust_accounts hca,
Ra_cust_trx_types_all ctt,
Mtl_units_of_measure uom
Where ct. customer_trx_id = ctl. customer_trx_id
And ct.org _ id = ctl.org _ id
And ct. bill_to_customer_id = hca. cust_account_id
And hca. party_id = hpt. party_id
And ctt. cust_trx_type_id = ct. cust_trx_type_id
And ctl. line_type! = 'Tax'
And ctt. type in ('inv', 'cm ')
And ctt. cust_trx_type_id! =
Cux_ar_utl_pkg.get_return_commitment_type
And ctl. uom_code = uom. uom_code (+) RDBMS
Where 1 = 1
And RDBMS. bill_to_customer_id =
(Select a. customer_id
From ar_customers
Where a. customer_number = customernum)
And RDBMS. trx_date <= trunc (startdate, 'month')-1;
Dbms_output.put_line (v_amount |
': V_amountv_amountv_amountv_amountv_amountv_amount ');
Select nvl (sum (acr. functional_amount), 0) -- all payables before the current parameter date
Into v_receipt_amount
From ar_cash_receipts_v acr
Where acr. gl_date <= trunc (startdate, 'month')-1
And acr. state! = 'Reversed'
And acr. customer_id =
(Select a. customer_id
From ar_customers
Where a. customer_number = customernum );
Dbms_output.put_line (v_receipt_amount |
': V_receipt_amountv_receipt_amountv_receipt_amount ');
Select nvl (sum (araa. amount_applied), 0) -- all refunds before the current parameter date
Into refundamount
From ar_cash_receipt_history_all acrh,
Ar_cash_receipts_all acr,
Ar_receivable_applications_all araa
Where acrh. gl_date <= trunc (startdate, 'month')-1
And acrh. status! = 'Reversed'
And acr. cash_receipt_id = acrh. cash_receipt_id
And acrh. cash_receipt_history_id = araa. cash_receipt_history_id
And araa. applied_customer_trx_id is null
And acrh.org _ id = acr.org _ id
And araa. display = 'y'
And araa. status = 'active'
And acr. pay_from_customer =
(Select a. customer_id
From ar_customers
Where a. customer_number = customernum );
Dbms_output.put_line (refundamount |
': RefundAmountrefundAmountrefundAmount ');
Lastdebt: = (v_amount-v_receipt_amount + refundamount); -- all payments earlier than the current date minus all payables earlier than the current date plus all refunds earlier than the current date = overdue payments for the previous month

Return lastdebt;
End;