Press ESC to close

Query to find all GL transactions on oracle EBS R12, Easy and fast!


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

Read More on ERP Scope:

Leave a Reply