This GL Query can used for the following requirements:
Extract EBS GL Transactions for fusion migration.
Extract all GL transactions for Auditing purpose.
Extract all GL Journal Entries Transactions based on user request.
Query to Find Journal Entries Transactions.
SELECT GJH.ledger_Id,
to_char(gjl.EFFECTIVE_DATE, 'YYYY/MM/DD')"Effective Date of Transaction" ,
gjh.JE_SOURCE "Journal Source",
gjh.JE_CATEGORY "Journal Categor",
gjh.CURRENCY_CODE "Currency Code",
to_char(gjh.DATE_CREATED,'YYYY/MM/DD') "Journal Entry Creation Date",
gjh.ACTUAL_FLAG "Actual Flag",
gcc.segment1 ,
gcc.segment2,
gcc.segment3,
gcc.segment4,
gcc.segment5,
gcc.segment6,
gcc.segment7 ,
gcc.segment8,
gcc.segment9,
gcc.segment10,
gcc.segment11,
gcc.segment12,
gcc.segment13,
gcc.segment14,
gcc.segment15,
gcc.segment16,
gcc.segment17,
gcc.segment18,
gcc.segment19,
gcc.segment20,
gcc.segment21,
gcc.segment22,
gcc.segment23,
gcc.segment24,
gcc.segment25,
gcc.segment26,
gcc.segment27,
gcc.segment28,
gcc.segment29,
gcc.segment30,
gjl.entered_dr "Entered Debit Amount",
gjl.entered_cr "Entered Credit Amount",
gjl.accounted_dr "Converted Debit Amount",
gjl.accounted_cr "Converted Credit Amount",
gjb.name "reference_1" ,
gjb.description "reference_2 BT desc",
gjl.reference_3 ,
gjh.name "reference_4",
gjh.DESCRIPTION "reference_5" ,
gjh.DOC_SEQUENCE_VALUE "reference_6 Reference" ,
decode (gjh.accrual_rev_status,'R', 'Y', 'N') "reference_7" ,
gjh.accrual_rev_period_name "reference_8",
gjh.accrual_rev_change_sign_flag "reference_9",
gjl.description "reference_10" ,
gjl.stat_amount "Statistical Amount",
gjh.currency_conversion_type "Currency Conversion Type",
to_char(gjh.currency_conversion_date , 'YYYY/MM/DD') "Currency Conversion Date",
gjh.currency_conversion_rate "Currency Conversion Rate",
null "Interface Group Identifier",
NULL "Context field for Journal Entry Line DFF",
NULL "ATTRIBUTE1 Value for Journal Entry Line DFF",
NULL "ATTRIBUTE2 Value for Journal Entry Line DFF",
NULL "Attribute3 Value for Journal Entry Line DFF",
NULL "Attribute4 Value for Journal Entry Line DFF",
NULL "Attribute5 Value for Journal Entry Line DFF" ,
NULL "Attribute6 Value for Journal Entry Line DFF",
NULL "Attribute7 Value for Journal Entry Line DFF" ,
NULL "Attribute8 Value for Journal Entry Line DFF",
NULL "Attribute9 Value for Journal Entry Line DFF",
NULL "Attribute10 Value for Journal Entry Line DFF",
NULL "Attribute11 Value for Captured Information DFF" ,
NULL "Attribute12 Value for Captured Information DFF" ,
NULL "Attribute13 Value for Captured Information DFF" ,
NULL "Attribute14 Value for Captured Information DFF" ,
NULL "Attribute15 Value for Captured Information DFF" ,
NULL "Attribute16 Value for Captured Information DFF" ,
NULL "Attribute17 Value for Captured Information DFF" ,
NULL "Attribute18 Value for Captured Information DFF" ,
NULL "Attribute19 Value for Captured Information DFF" ,
NULL "Attribute20 Value for Captured Information DFF" ,
NULL "Context field for Captured Information DFF" ,
NULL "Clearing Company",
gled.NAME "Ledger Name" ,
gjh.encumbrance_type_id "Encumbrance Type ID",
NULL "Reconciliation Reference" ,
gjh.PERIOD_NAME "Period Name" ,
NULL "Attribute Date 1",
NULL "Attribute Date 2",
NULL "Attribute Date 3",
NULL "Attribute Date 4",
NULL "Attribute Date 5",
NULL "Attribute Date 6",
NULL "Attribute Date 7",
NULL "Attribute Date 8",
NULL "Attribute Date 9",
NULL "Attribute Date 10",
NULL "Attribute Number 1",
NULL "Attribute Number 2",
NULL "Attribute Number 3",
NULL "Attribute Number 4",
NULL "Attribute Number 5",
NULL "Attribute Number 6",
NULL "Attribute Number 7",
NULL "Attribute Number 8",
NULL "Attribute Number 9",
NULL "Attribute Number 10",
NULL "Global Attribute Category",
NULL "Global Attribute 1",
NULL "Global Attribute 2",
NULL "Global Attribute 3",
NULL "Global Attribute 4",
NULL "Global Attribute 5",
NULL "Global Attribute 6",
NULL "Global Attribute 7",
NULL "Global Attribute 8",
NULL "Global Attribute 9",
NULL "Global Attribute 10",
NULL "Global Attribute 11",
NULL "Global Attribute 12",
NULL "Global Attribute 13",
NULL "Global Attribute 14",
NULL "Global Attribute 15",
NULL "Global Attribute 16",
NULL "Global Attribute 17",
NULL "Global Attribute 18",
NULL "Global Attribute 19",
NULL "Global Attribute 20",
NULL "Global Attribute Date 1" ,
NULL "Global Attribute Date 2" ,
NULL "Global Attribute Date 3" ,
NULL "Global Attribute Date 4" ,
NULL "Global Attribute Date 5" ,
NULL "Global Attribute Number 1",
NULL "Global Attribute Number 2",
NULL "Global Attribute Number 3",
NULL "Global Attribute Number 4",
NULL "Global Attribute Number 5"
From gl_je_headers gjh,
gl_code_combinations gcc,
gl_je_lines gjl,
gl_ledgers gled,
gl_je_batches gjb,
fnd_flex_values_vl s5,
fnd_flex_values_vl s4,
fnd_flex_values_vl s3,
fnd_flex_values_vl s2,
fnd_flex_values_vl s1
WHERE 1 = 1
AND gcc.code_combination_id = gjl.code_combination_id
AND gjl.je_header_id = gjh.je_header_id
AND gjh.ledger_id=gled.ledger_id
AND gjh.je_batch_id = gjb.je_batch_id
AND s1.flex_value_set_id = :P_SEGMENT1 -- Segment1 Value Set ID
AND s1.flex_value = gcc.segment1
AND s2.flex_value_set_id = :P_SEGMENT2 -- Segment2 Value Set ID
AND s2.flex_value = gcc.segment2
AND s3.flex_value_set_id = :P_SEGMENT3 -- Segment3 Value Set ID
AND s3.flex_value = gcc.segment3
AND s4.flex_value_set_id = :P_SEGMENT4 -- Segment4 Value Set ID
AND s4.flex_value = gcc.segment4
AND s5.flex_value_set_id = :P_SEGMENT5 -- Segment5 Value Set ID
AND s5.flex_value = gcc.segment5
AND GJH.ledger_id= :P_LEDGER
AND GJH.ACTUAL_FLAG='A'
AND gjl.EFFECTIVE_DATE <= :p_date -- all your transactions before this date
ORDER BY
gjh.je_header_id,
gjl.je_line_num
Leave a Reply