Friday, May 12, 2017

Supplier Accrual Balance Query in Oracle Applications

SELECT   fv.flex_value_meaning company, fv.description company_description,
         cc.segment4 ACCOUNT,
         NVL (sp.vendor_name, gs.user_je_source_name) supplier,
         sp.segment1 supplier_number,
         sp.vendor_type_lookup_code supplier_type, pt.NAME payment_terms,
         SUM (  NVL (NVL (xal.accounted_dr, gjl.accounted_dr), 0)
              - NVL (NVL (xal.accounted_cr, gjl.accounted_cr), 0)
             ) net
    FROM xla.xla_transaction_entities xte,
         xla.xla_ae_headers xah,
         xla.xla_ae_lines xal,
         gl.gl_import_references gir,
         gl.gl_je_lines gjl,
         gl.gl_je_headers gjh,
         gl.gl_code_combinations cc,
         gl.gl_je_batches gb,
         apps.gl_je_sources_vl gs,
         apps.ap_suppliers sp,
         apps.fnd_flex_value_sets vs,
         apps.fnd_flex_values_vl fv,
         apps.ap_terms_vl pt,
         apps.gl_je_categories_vl gc,
         gl.gl_periods gp
   WHERE 1 = 1
     -- AND xte.entity_code = 'AP_INVOICES'         --'AP_INVOICES' --'AP_PAYMENTS'
     AND NVL (xte.application_id, 200) = 200
     AND xte.entity_id(+) = xah.entity_id
     AND xah.ae_header_id(+) = xal.ae_header_id
     AND xal.gl_sl_link_id(+) = gir.gl_sl_link_id
     AND xal.gl_sl_link_table(+) = gir.gl_sl_link_table
     AND sp.vendor_id(+) = xal.party_id
     AND gir.je_header_id = gjl.je_header_id
     AND gir.je_line_num = gjl.je_line_num
     AND gjl.je_header_id = gjh.je_header_id
     AND gjh.je_source = gs.je_source_name
     AND gjh.period_name = gp.period_name
     AND gp.start_date >= '01-JAN-2015'
     AND gp.end_date < '01-MAR-2017'
--            AND  gp.period_name = 'FEB 2017'
--            and gp.period_set_name = 'USG CALENDAR'
     AND gjh.je_category = gc.je_category_name
     AND gc.je_category_name IN ('Treasury', 'Purchase Invoices', 'Payments')
--   AND xal.accounting_date(+) < TO_DATE ('01-APR-2017', 'DD-MON-YYYY')
--   AND xal.accounting_date(+) >= TO_DATE ('01-JAN-2017', 'DD-MON-YYYY')
--   AND xal.code_combination_id(+) = cc.code_combination_id
--   AND xal.ledger_id(+) = gjh.ledger_id
     AND cc.chart_of_accounts_id = 50241
     AND cc.segment4 = '201001'
     AND cc.segment1 = '101'
     AND gjh.je_batch_id = gb.je_batch_id
     AND gjl.code_combination_id = cc.code_combination_id
     AND vs.flex_value_set_name = 'XXX_CO'
     AND fv.flex_value_set_id = vs.flex_value_set_id
     AND fv.flex_value = cc.segment1
     AND pt.term_id(+) = sp.terms_id
GROUP BY fv.flex_value_meaning,
         fv.description,
         NVL (sp.vendor_name, gs.user_je_source_name),
         sp.segment1,
         cc.segment4,
         sp.vendor_type_lookup_code,
         pt.NAME
ORDER BY 2, 3, 4;

No comments:

Post a Comment