Tuesday, 8 July 2014

11i AP to GL Account Name and Detail Query

/* Formatted on 7/8/2014 8:39:44 AM (QP5 v5.115.810.9015) */
SELECT gjh.period_name "Period",
       gjh.default_effective_date "Document Date",
       gcc.segment1 "Company",
       gcc.segment2 "Department",
       gcc.segment3 "Account",
       ffvt2.description "Account Name",
       gjh.je_source "Source",
       gjh.currency_code "Currency Type",
       gjl.entered_dr "Entered DR",
       gjl.entered_cr "Entered CR",
       gjl.reference_2 "invoice number",
       gjl.subledger_doc_sequence_value "Voucher",
       gjl.reference_1 "Supplier Name",
       gjl.description "Line Description",
       atc.name "TAX Code"
FROM gl_je_lines gjl,
     gl_je_headers gjh,
     gl_code_combinations gcc,
     ap_ae_lines_all ael,
     ap_accounting_events_all aea,
     ap_ae_headers_all aeh,
     ap_tax_codes_all atc,
     fnd_id_flex_structures_tl fifs2,
     fnd_id_flex_segments fidt2,
     fnd_flex_values ffv2,
     fnd_flex_values_tl ffvt2
WHERE     1 = 1
      AND gjh.je_header_id = gjl.je_header_id
      AND gcc.segment1 = '30'
      AND gcc.code_combination_id = gjl.code_combination_id
      AND ael.gl_sl_link_id = gjl.gl_sl_link_id
      AND aeh.accounting_event_id = aea.accounting_event_id
      AND aeh.ae_header_id = ael.ae_header_id
      AND ael.tax_code_id = atc.tax_id(+)
      --and gjh.doc_sequence_value = '291'
      --and gjh.name = 'Payments EUR'
      AND fifs2.id_flex_structure_name = 'SSFT Corp'
      AND fifs2.id_flex_num = fidt2.id_flex_num
      AND fidt2.application_column_name = 'SEGMENT3'
      AND fidt2.segment_name = 'Account'
      AND fidt2.flex_value_set_id = ffv2.flex_value_set_id
      AND ffv2.flex_value = gcc.segment3
      AND ffv2.flex_value_id = ffvt2.flex_value_id
      AND TRUNC (gjh.creation_date) BETWEEN '01-JAN-2004' AND '15-JAN-2005'
      AND ael.org_id = 711
ORDER BY gjl.reference_2

No comments:

Post a Comment