--Step 1
--===========================================
create or replace type xxhf_aptb_obj as object (
LEDGER VARCHAR2 (50)
,INVOICE_NUM VARCHAR2 (150)
,INVOICE_DATE DATE
,INV_CURR VARCHAR2 (50)
,INV_AMOUNT NUMBER
,INV_REM_AMT NUMBER
,SEGMENT1 VARCHAR2 (100)
,VENDOR_NAME VARCHAR2 (360)
,CONCATENATED_SEGMENTS VARCHAR2 (240)
,REMAINING_AMOUNT NUMBER
,INVOICE_AMOUNT NUMBER
,PAYMENT_STATUS VARCHAR2 (140)
,SOURCE_ENTITY_ID NUMBER
,LEDGER_ID NUMBER (15)
,CODE_COMBINATION_ID NUMBER
,INVOICE_ID NUMBER (15)
,ORG_ID NUMBER (15)
);
--Step 2
--===========================================
create or replace type xxhf_aptb_nested_table as table of xxhf_aptb_obj;
/
--Step 3
--===========================================
CREATE OR REPLACE FUNCTION xxhf_ap_trial_bal_rpt
RETURN xxhf_aptb_nested_table
AS
v_ret xxhf_aptb_nested_table;
PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
mo_global.init ('SQLAP');
SELECT xxhf_aptb_obj (ledger,
invoice_num,
invoice_date,
inv_curr,
inv_amount,
inv_rem_amt,
segment1,
vendor_name,
concatenated_segments,
remaining_amount,
invoice_amount,
payment_status,
source_entity_id,
ledger_id,
code_combination_id,
invoice_id,
org_id
)
BULK COLLECT INTO v_ret
FROM (SELECT gl.NAME ledger, aia.invoice_num, aia.invoice_date,
aia.invoice_currency_code inv_curr,
aia.invoice_amount inv_amount,
SUM
(CASE
WHEN aia.invoice_currency_code =
aia.payment_currency_code
THEN (NVL (tb.entered_rounded_cr, 0))
- (NVL (tb.entered_rounded_dr, 0))
ELSE ( NVL (tb.entered_rounded_cr, 0)
- (( NVL (tb.entered_rounded_dr, 0)
* NVL
((SELECT conversion_rate
FROM gl_daily_rates
WHERE conversion_type =
aia.exchange_rate_type
AND TRUNC (conversion_date) =
TRUNC
(NVL
(aia.exchange_date,
tb.gl_date
)
)
AND from_currency =
aia.invoice_currency_code
AND to_currency =
aia.payment_currency_code),
0
)
)
)
)
END
) inv_rem_amt,
(SELECT segment1
FROM ap_suppliers
WHERE vendor_id = aia.vendor_id) segment1,
hp.party_name vendor_name, gcc.concatenated_segments,
SUM (NVL (tb.acctd_rounded_cr, 0))
- SUM (NVL (tb.acctd_rounded_dr, 0)) remaining_amount,
SUM
(CASE
WHEN tb.applied_to_entity_id IS NULL
THEN (NVL (tb.acctd_rounded_cr, 0))
- (NVL (tb.acctd_rounded_dr, 0))
ELSE 0
END
) invoice_amount,
flv_paystatus.meaning payment_status,
NVL (tb.applied_to_entity_id,
tb.source_entity_id
) source_entity_id,
tb.ledger_id, tb.code_combination_id, aia.invoice_id,
aia.org_id
FROM xla_transaction_entities xte,
xla_trial_balances tb,
ap_invoices_all aia,
hz_parties hp,
gl_code_combinations_kfv gcc,
gl_ledgers gl,
fnd_lookup_values flv_paystatus
WHERE 1 = 1
AND gl.ledger_id = tb.ledger_id
AND aia.party_id = hp.party_id
AND NVL (tb.applied_to_entity_id, tb.source_entity_id) =
xte.entity_id
AND xte.ledger_id = tb.ledger_id
AND xte.application_id = 200
AND tb.source_application_id = 200
AND aia.set_of_books_id = xte.ledger_id
AND aia.invoice_id = xte.source_id_int_1
AND tb.code_combination_id = gcc.code_combination_id
----lp_date
AND flv_paystatus.lookup_code = aia.payment_status_flag
AND flv_paystatus.LANGUAGE = 'US'
AND NVL (flv_paystatus.end_date_active, SYSDATE + 1) > SYSDATE
AND flv_paystatus.lookup_type = 'INVOICE PAYMENT STATUS'
AND TRUNC (tb.gl_date) <= TRUNC (SYSDATE)
GROUP BY aia.invoice_num,
aia.invoice_date,
aia.invoice_currency_code,
aia.invoice_amount,
( aia.invoice_amount
- ( NVL (aia.amount_paid, 0)
+ NVL (aia.discount_amount_taken, 0)
)
),
aia.vendor_id,
hp.party_name,
gcc.concatenated_segments,
aia.invoice_amount,
NVL (tb.applied_to_entity_id, tb.source_entity_id),
tb.ledger_id,
tb.code_combination_id,
aia.invoice_id,
aia.org_id,
gl.NAME,
flv_paystatus.meaning
HAVING SUM (NVL (tb.acctd_rounded_cr, 0)) <>
SUM (NVL (tb.acctd_rounded_dr, 0)));
RETURN v_ret;
END xxhf_ap_trial_bal_rpt;
/
--Step 4
--===========================================
CREATE OR REPLACE VIEW xxhf_ap_trial_bal_rpt_v
as select * from TABLE (xxhf_ap_trial_bal_rpt) order by 1
No comments:
Post a Comment