--Step 1
--===========================================
create or replace type xxhf_cobl as object (
REQUEST_ID NUMBER,
AS_OF_DATE DATE,
ORGANIZATION_NAME VARCHAR2 (60),
FUNCTIONAL_CURRENCY_CODE VARCHAR2 (15),
CUSTOMER_NAME VARCHAR2 (360),
CUSTOMER_NUMBER VARCHAR2 (30),
TAX_REFERENCE_NUM VARCHAR2 (50),
ADDRESS_LINE1 VARCHAR2 (240),
ADDRESS_LINE2 VARCHAR2 (240),
ADDRESS_LINE3 VARCHAR2 (240),
ADDRESS_LINE4 VARCHAR2 (240),
CITY VARCHAR2 (60),
STATE VARCHAR2 (60),
ZIP VARCHAR2 (60),
COUNTRY VARCHAR2 (60),
TRANS_TYPE VARCHAR2 (20),
TRX_NUMBER VARCHAR2 (30),
TRANSACTION_DATE DATE,
TRX_CURRENCY_CODE VARCHAR2 (15),
TRANS_AMOUNT NUMBER,
TRANS_AMOUNT_REMAINING NUMBER,
RECEIPT_AMOUNT NUMBER,
ADJUSTMENT_AMOUNT NUMBER,
EARNED_DISCOUNT_AMOUNT NUMBER,
UNEARNED_DISCOUNT_AMOUNT NUMBER,
INVOICE_CREDIT_AMOUNT NUMBER,
BANK_CHARGE NUMBER,
ON_ACCOUNT_CREDIT_AMOUNT NUMBER,
ON_ACCOUNT_RECEIPTS NUMBER,
UNAPPLIED_RECEIPTS NUMBER
);
/
--Step 2
--===========================================
create or replace type xxhf_cobl_nested_table as table of xxhf_cobl;
/
--Step 3
--===========================================
CREATE OR REPLACE FUNCTION xxhf_cust_open_bal_letter
RETURN xxhf_cobl_nested_table
AS
v_ret xxhf_cobl_nested_table;
p_request_id NUMBER;
p_set_of_books_id NUMBER;
p_as_of_date DATE;
p_customer_name_from VARCHAR2 (200);
p_customer_name_to VARCHAR2 (200);
p_customer_number_low VARCHAR2 (200);
p_customer_number_high VARCHAR2 (200);
p_currency VARCHAR2 (200);
p_min_invoice_balance NUMBER;
p_min_open_balance NUMBER;
p_account_credits VARCHAR2 (200);
p_account_receipts VARCHAR2 (200);
p_unapp_receipts VARCHAR2 (200);
p_uncleared_receipts VARCHAR2 (200);
p_ref_no VARCHAR2 (200);
p_debug_flag VARCHAR2 (200);
p_trace_flag VARCHAR2 (200);
PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
v_ret := xxhf_cobl_nested_table ();
p_request_id := 2;
p_set_of_books_id := NULL;
p_as_of_date := SYSDATE;
p_customer_name_from := NULL;
p_customer_name_to := NULL;
p_customer_number_low := NULL;
p_customer_number_high := NULL;
p_currency := NULL;
p_min_invoice_balance := NULL;
p_min_open_balance := NULL;
p_account_credits := NULL;
p_account_receipts := NULL;
p_unapp_receipts := NULL;
p_uncleared_receipts := NULL;
p_ref_no := NULL;
p_debug_flag := 'N';
p_trace_flag := 'N';
mo_global.init ('AR');
DELETE FROM ar_customer_balance_itf
WHERE request_id = 2;
COMMIT;
apps.ar_get_customer_balance_pkg.ar_get_customer_balance
(p_request_id,
p_set_of_books_id,
p_as_of_date,
p_customer_name_from,
p_customer_name_to,
p_customer_number_low,
p_customer_number_high,
p_currency,
p_min_invoice_balance,
p_min_open_balance,
p_account_credits,
p_account_receipts,
p_unapp_receipts,
p_uncleared_receipts,
p_ref_no,
p_debug_flag,
p_trace_flag
);
COMMIT;
SELECT xxhf_cobl (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
)
BULK COLLECT INTO v_ret
FROM ar_customer_balance_itf
WHERE 1 = 1 AND request_id = 2;
RETURN v_ret;
END xxhf_cust_open_bal_letter;
/
--Step 4
--===========================================
CREATE OR REPLACE VIEW xxhf_cust_open_bal_letter_v
AS
SELECT organization_name, as_of_date, customer_name, customer_number,
tax_reference_num, address_line1, address_line2, address_line3,
address_line4, city, state, zip, country,
DECODE (trans_type,
'INV', 'Standard',
'DM', 'Debit Memo',
'CB', 'Chargeback',
'DEP', 'Deposit'
) trans_type,
trx_number, transaction_date, trx_currency_code,
NVL (trans_amount, 0) trans_amount,
NVL (trans_amount_remaining, 0) trans_amount_remaining,
NVL (receipt_amount, 0) receipt_amount,
NVL (adjustment_amount, 0) adjustment_amount,
NVL (earned_discount_amount, 0) earned_discount_amount,
NVL (unearned_discount_amount, 0) unearned_discount_amount,
NVL (invoice_credit_amount, 0) invoice_credit_amount,
NVL (bank_charge, 0) bank_charge,
NVL (on_account_credit_amount, 0) on_account_credit_amount,
NVL (on_account_receipts, 0) on_account_receipts,
NVL (unapplied_receipts, 0) unapplied_receipts
--nvl(uncleared_receipts,0) uncleared_receipts
FROM TABLE (xxhf_cust_open_bal_letter)
WHERE request_id = 2
ORDER BY transaction_date, trx_number
No comments:
Post a Comment