Wednesday, August 31, 2016

Query to get the Oracle Payment Details

SELECT   (SELECT NAME
            FROM hr_operating_units
           WHERE organization_id = aph.org_id) ou_name, aca.check_number,
         aca.check_date, aca.status_lookup_code, aca.vendor_name,
         aph.accounting_date, xal.accounting_class_code,
         (SELECT concatenated_segments
            FROM gl_code_combinations_kfv
           WHERE code_combination_id = xal.code_combination_id) ac_code,
         SUM (NVL (xal.entered_dr, 0)) entered_debit,
         SUM (NVL (xal.entered_cr, 0)) entered_credit,
         SUM (NVL (xal.accounted_dr, 0)) accounted_debit,
         SUM (NVL (xal.accounted_cr, 0)) accounted_credit
    FROM ap_payment_history_all aph,
         xla_ae_headers xah,
         xla_ae_lines xal,
         ap_checks_all aca
   WHERE aph.accounting_event_id = xah.event_id
     AND aca.check_id = aph.check_id
     AND aca.org_id = aph.org_id
     AND xah.application_id = 200
     AND aph.org_id  = 204
     AND xah.ae_header_id = xal.ae_header_id
  HAVING (  SUM (NVL (xal.entered_dr, 0))
          + SUM (NVL (xal.entered_cr, 0))
          + SUM (NVL (xal.accounted_dr, 0))
          + SUM (NVL (xal.accounted_cr, 0))
         ) <> 0
GROUP BY aph.org_id,
         aca.check_number,
         aca.check_date,
         aca.status_lookup_code,
         aca.vendor_name,
         aph.accounting_date,
         xal.accounting_class_code,

         xal.code_combination_id

No comments:

Post a Comment

Best Blogger TipsGet Flower Effect