Monday, July 31, 2017

Query to Get Oracle Payments Details

SELECT  /*+ ORDERED */
  ECODE (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_name,
  ai.invoice_num,
  ai.description,
  (SELECT MASKED_BANK_ACCOUNT_NUM
     FROM iby_ext_bank_accounts
    WHERE ext_bank_account_id = ps.external_bank_account_id)
     bank_account_num,
  TO_CHAR (asi.due_date, 'YYYY-MM-DD"T"HH24:MI:SS') due_date,
  ps.amount_remaining,
  NVL (asi.withholding_amount, 0) withheld_amount,
  NVL (asi.discount_amount, 0) discount_amount,
  -- asi.amount_remaining payment_amount
  asi.payment_amount payment_amount,
  NVL (
     (SELECT amount_remaining
        FROM ap_selected_invoices_all asi2
       WHERE asi2.original_invoice_id = TO_CHAR (asi.invoice_id)
             AND asi2.payment_num = asi.payment_num),
     0)
     AS interest_amount,
  asi.payment_currency_code selected_ps_pmt_currency,
  hr.name selected_ps_org_name
FROM ap_selected_invoices_all asi,
  ap_payment_schedules_all ps,
  ap_invoices_all ai,
  hz_parties hz,
  hz_party_sites hzps,
  ap_supplier_sites_all ass,
  hr_operating_units hr,
  ap_suppliers ap
WHERE     1 = 1
  AND asi.checkrun_id = :p_checkrun_id
  AND asi.org_id = hr.organization_id
  AND asi.original_invoice_id IS NULL
  AND asi.invoice_id = ps.invoice_id
  AND asi.payment_num = ps.payment_num
  AND ps.invoice_id = ai.invoice_id
  AND ai.party_id = hz.party_id
  AND ap.party_id(+) = hz.party_id
  AND ai.vendor_id = DECODE (SIGN (ai.vendor_id), -1, ai.vendor_id, NVL (ap.vendor_id, ai.vendor_id))
  AND ai.party_site_id = hzps.party_site_id(+)
  AND ai.vendor_site_id = ass.vendor_site_id(+)

ORDER BY party_name;

No comments:

Post a Comment

Best Blogger TipsGet Flower Effect