Create table using function ( along with procedure) - Example 2 - ar_get_customer_balance_pkg.ar_get_customer_balance

 --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

  

Create table using function ( along with procedure) - Example 1

 --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

AP Trial Balance Report query

 SELECT

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,

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 aia

         , hz_parties hp

         , gl_code_combinations_kfv gcc

WHERE  1=1 

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 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 

HAVING SUM (nvl(tb.acctd_rounded_cr,0)) <> SUM (nvl(tb.acctd_rounded_dr,0))

begin

mo_global.set_policy_context('S',29830);

end;