Query : GL Journal Batch/Header/Lines

• SELECT 

         --BATCH

         batch.je_batch_id,

     batch.name "Batch Name",

     batch.status "Batch Status",

     glk2.meaning "Batch Status Meaning", 

     batch.description "Batch Description",

     batch.creation_date "Batch Creation Date", 

     batch.created_by "Batch Created By",

     batch.last_update_date "Batch Last Update Date", 

     batch.last_updated_by "Batch Last Updated by", 

     batch.posted_date "Batch Posted Date", 

     batch.approver_employee_id,

     ppnf.first_name||' '||ppnf.last_name "Approver Name",

     batch.approval_status_code "Batch Approval Status Code", 

     glk.meaning "Approval Status Meaning",

     batch.parent_je_batch_id, 

     

     

     

     --HEADER

         hdr.je_header_id, 

     hdr.name "Header Name", 

         hdr.description "Header Description",

         src.user_je_source_name,    

         cat.user_je_category_name,  

     hdr.accrual_rev_change_sign_flag,    

         hdr.date_created,

         hdr.default_effective_date,

         hdr.status,                 

         hdr.currency_code,

         hdr.external_reference "External Reference",

         hdr.accrual_rev_flag,

         hdr.accrual_rev_period_name,

     hdr.reversed_je_header_id, 

     

     

     --LINE

         line.je_line_num,           

         line.description "Line Description",        

         line.entered_dr,

         line.entered_cr,

         line.accounted_dr,

         line.accounted_cr,

         line.currency_conversion_rate,

         line.currency_conversion_type,

         line.currency_conversion_date,

         gcc.segment1,

         gcc.segment2,

         gcc.segment3,

         gcc.segment4,

         gcc.segment5,

         gcc.segment6,

         gcc.segment7,

         gcc.segment8,

         gcc.segment9,

         gcc.segment10,

         ledger.name ledgername,

         gp.period_name

  
  

     

    FROM gl_je_headers       hdr,

         gl_je_lines         line,

         gl_code_combinations gcc,

         gl_ledgers          ledger,

         gl_je_sources       src,

         gl_je_categories    cat,

         gl_je_batches       batch, 

         gl_periods          gp,

         gl_lookups          glk, 

     gl_lookups          glk2,

     per_person_names_f    ppnf

     

     

   WHERE 1 = 1

     AND hdr.je_header_id = line.je_header_id

     AND line.code_combination_id = gcc.code_combination_id

     AND hdr.ledger_id = ledger.ledger_id

     AND line.ledger_id = ledger.ledger_id

     AND hdr.je_source = src.je_source_name

     AND hdr.je_category = cat.je_category_name

     AND hdr.je_batch_id = batch.je_batch_id

     AND hdr.period_name = gp.period_name

     AND ledger.period_set_name = gp.period_set_name

     AND batch.approval_status_code = glk.lookup_code

     AND glk.lookup_type = 'JE_BATCH_APPROVAL_STATUS'  

     AND batch.status = glk2.lookup_code

     AND glk2.lookup_type = 'MJE_BATCH_STATUS'

     AND GP.period_name like '%21'

     AND batch.approver_employee_id = ppnf.person_id

----------------------------------------------------



select xl.*
from
xla_ae_headers xh,
xla_ae_lines xl

where xh.ae_header_id = xl.ae_header_id