Oracle Payment Queries

SELECT          /*+ LEADING(asi) use_NL(asi, ai2, ai) */

       DISTINCT ai.payment_currency_code unused_prepays_pmt_cur,

                hr.NAME unused_prepays_org_name,

                ap_invoices_utility_pkg.get_prepay_amount_remaining (ai.invoice_id)  unapplied_prepays,

               

                DECODE (ap.vendor_type_lookup_code,

                        'EMPLOYEE', ap.vendor_name,

                        hz.party_name

                       ) party_name,

                DECODE (ai.invoice_type_lookup_code, 'PAYMENT REQUEST', hzps.party_site_name, ass.vendor_site_code ) party_site_name3

                ai.invoice_num, ai.earliest_settlement_date, ps.payment_num

           FROM ap_payment_schedules ps,

                ap_invoices ai,

                ap_selected_invoices asi,

                ap_invoices ai2,

                ap_supplier_sites ass,

                hz_parties hz,

                hz_party_sites hzps,

                hr_operating_units hr,

                ap_suppliers ap

          WHERE ai.invoice_id = ps.invoice_id

            AND hr.organization_id = ai.org_id

            AND ass.vendor_site_id(+) = ai.vendor_site_id

            AND ai.party_id = hz.party_id

            AND ap.party_id(+) = hz.party_id

            AND ai.party_site_id = hzps.party_site_id(+)

            AND ps.payment_status_flag IN ('P', 'Y')

            AND ai.invoice_type_lookup_code = 'PREPAYMENT'

            AND ai.earliest_settlement_date IS NOT NULL

            AND ps.checkrun_id IS NULL

            AND asi.checkrun_id = :p_checkrun_id

            AND ai2.invoice_id = asi.invoice_id

            AND ai2.vendor_id = ai.vendor_id

            AND ai.party_id = ai2.party_id

            AND ai.payment_currency_code = ai2.payment_currency_code

            AND (ps.invoice_id, ps.payment_num) NOT IN (

                                             SELECT invoice_id, payment_num

                                               FROM ap_unselected_invoices

                                              WHERE checkrun_id = :p_checkrun_id)

            AND ap_invoices_utility_pkg.get_prepay_amount_remaining (ai.invoice_id) > 0

            AND (   EXISTS ( SELECT 1 FROM ap_ou_group aog WHERE checkrun_id = :p_checkrun_id AND ai.org_id = aog.org_id)

                 OR NOT EXISTS (SELECT 1 FROM ap_ou_group WHERE checkrun_id = :p_checkrun_id)

                )

       ORDER BY 2 ASC, 1 ASC

SELECT   x.payment_currency_code unused_prepay_pmt_currency,

         SUM

            (ap_invoices_utility_pkg.get_prepay_amount_remaining (x.invoice_id)

            ) unapplied_prepays_sum

    FROM (SELECT          /*+ LEADING(asi) use_NL(asi, ai2, ai) */

                 DISTINCT ai.invoice_id, aps.payment_num,

                          ai.payment_currency_code

                     FROM ap_payment_schedules aps,

                          ap_invoices ai,

                          ap_selected_invoices asi,

                          ap_invoices ai2

                    WHERE ai.invoice_id = aps.invoice_id

                      AND aps.payment_status_flag IN ('P', 'Y')

                      AND ai.invoice_type_lookup_code = 'PREPAYMENT'

                      AND ai.earliest_settlement_date IS NOT NULL

                      AND aps.checkrun_id IS NULL

                      AND asi.checkrun_id = :p_checkrun_id

                      AND ai2.invoice_id = asi.invoice_id

                      AND ai2.vendor_id = ai.vendor_id         

                      AND ai.party_id = ai2.party_id

                      AND ai.payment_currency_code = ai2.payment_currency_code

                      AND (aps.invoice_id, aps.payment_num) NOT IN (

                                             SELECT invoice_id, payment_num

                                               FROM ap_unselected_invoices

                                              WHERE checkrun_id =

                                                                :p_checkrun_id)

                      AND ap_invoices_utility_pkg.get_prepay_amount_remaining

                                                                (ai.invoice_id) > 0  

    AND (   EXISTS (

                                 SELECT 1

                                   FROM ap_ou_group aog

                                  WHERE checkrun_id = :p_checkrun_id

                                    AND ai.org_id = aog.org_id)

                           OR NOT EXISTS (SELECT 1

                                            FROM ap_ou_group

                                           WHERE checkrun_id = :p_checkrun_id)

                          )

         ) x

GROUP BY x.payment_currency_code

SELECT          /*+ LEADING(asi) use_NL(asi, ai2, ai) index(ai AP_INVOICES_N2) */

       DISTINCT ai.payment_currency_code unused_credits_pmt_cur,

                hr.NAME unused_credits_org_name,

                DECODE (SIGN (ps.amount_remaining),-1, ps.amount_remaining,0) amount_remaining,                

                DECODE (ap.vendor_type_lookup_code,

                        'EMPLOYEE', ap.vendor_name,

                        hz.party_name

                       ) party_name,

               

                DECODE (ai.invoice_type_lookup_code,

                        'PAYMENT REQUEST', hzps.party_site_name,

                        ass.vendor_site_code

                       ) party_site_name1,

                ai.invoice_num,

                TO_CHAR (ps.due_date, 'YYYY-MM-DD"T"HH24:MI:SS') due_date,

                ps.payment_num

           FROM ap_payment_schedules ps,

                ap_invoices ai,

                ap_selected_invoices asi,

                ap_invoices ai2,

                --  ap_supplier_sites_all ass,  

                hz_parties hz,

                hz_party_sites hzps,

                ap_supplier_sites ass,                          

                hr_operating_units hr,

                ap_suppliers ap

          WHERE ai.invoice_id = ps.invoice_id

            AND ai.org_id = hr.organization_id

            AND ai.party_id = hz.party_id

            AND ap.party_id(+) = hz.party_id

            AND ai.party_site_id = hzps.party_site_id(+)

            AND ass.vendor_site_id(+) = ai.vendor_site_id

            AND ps.payment_status_flag IN ('P', 'N')

            AND ai.payment_status_flag IN ('P', 'N')

            AND ai.invoice_type_lookup_code IN ('CREDIT', 'CREDIT MEM', 'DEBIT', 'MIXED')

            AND ps.checkrun_id IS NULL

            AND asi.checkrun_id = :p_checkrun_id

            AND ai2.vendor_id = ai.vendor_id

            AND ai2.invoice_id = asi.invoice_id

            AND ai.party_id = ai2.party_id

            AND ai.payment_currency_code = ai2.payment_currency_code

            AND NOT EXISTS (

                   SELECT 'No'

                     FROM ap_unselected_invoices ui

                    WHERE checkrun_id = :p_checkrun_id

                      AND ui.invoice_id = ps.invoice_id

                      AND ui.payment_num = ps.payment_num)

            AND DECODE (SIGN (ps.amount_remaining),

                        -1, ps.amount_remaining,

                        0

                       ) < 0

            AND (   EXISTS (

                       SELECT 1

                         FROM ap_ou_group aog

                        WHERE checkrun_id = :p_checkrun_id

                          AND ai.org_id = aog.org_id)

                 OR NOT EXISTS (SELECT 1

                                  FROM ap_ou_group

                                 WHERE checkrun_id = :p_checkrun_id)

                )

       ORDER BY 2 ASC, 1 ASC