Sunday, June 18, 2017

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

No comments:

Post a Comment

Best Blogger TipsGet Flower Effect