AP To GL Extraction Queries

GL_JE_Extract_Payables
/* Please change the period start date and end date for extracting data for required period */
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

GL_JE_Extract_GL
/* Please change the period start date and end date for extracting data for required period */
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",
gjh.doc_sequence_value "Voucher",
gjl.description "Line Description"

FROM gl_je_lines gjl,
gl_je_headers gjh,
gl_code_combinations gcc,
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 gjl.set_of_books_id = 52
AND gjh.je_source NOT IN ('Payables', 'Recievables')
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 gjh.creation_date BETWEEN '01-JAN-2006' AND '15-JAN-2007'
ORDER BY gjl.description,gjh.period_name