Oracle Apps Knowledge Sharing

Tested Instance: R12.2.4

 Tables Involved:

1. GL_BC_PACKETS_HISTS
2. GL_LEDGERS
3. HR_OPERATING_UNITS
4. GL_JE_BATCHES
5. GL_JE_HEADERS
6. GL_JE_LINES
7. GL_CODE_COMBINATIONS_KFV
8. FND_USER
9. GL_PERIODS

Query:

SELECT gl.name ledger_name,
       hou.name operating_unit,
       hou.short_code,   
       (SELECT gbv.budget_name
          FROM gl_bc_packets_hists glbp,
               gl_budget_versions gbv
         WHERE glbp.je_batch_id = glb.je_batch_id
           AND glbp.je_header_id = glh.je_header_id
           AND glbp.je_line_num = gll.je_line_num
           AND glbp.budget_version_id = gbv.budget_version_id
       ) budget_name,
       gl.currency_code,
       gl.period_set_name,
       glh.period_name,
       fu.user_name budget_user,
       glh.creation_date,
       (SELECT je_batch_name
          FROM gl_bc_packets_hists glbp
         WHERE glbp.je_batch_id = glb.je_batch_id
           AND glbp.je_header_id = glh.je_header_id
           AND glbp.je_line_num = gll.je_line_num
       ) budget_batch_name,
       glb.name Journal_batch_name,       
       glh.name journal_name,  
       gcc.concatenated_segments charge_account,
       gll.entered_cr,
       gll.entered_dr,    
       glh.default_effective_date,       
       DECODE(gll.status,
             'P','POSTED',
             'U','UNPOSTED',
             gll.status
             ) posted_status,  
       glh.posted_date,
       gp.start_date period_start_date,
       gp.end_date period_end_date,
       gp.period_year,
       gp.period_num,
       gp.quarter_num,
       hou.organization_id,
       gll.je_line_num,
       gll.je_header_id       
  FROM gl_ledgers gl,
       hr_operating_units hou,
       gl_je_batches glb,
       gl_je_headers glh,
       gl_je_lines gll,
       gl_code_combinations_kfv gcc,
       fnd_user fu,
       gl_periods gp
  WHERE glh.je_source = 'Budget Journal'
    AND glh.je_category = 'Budget'
    AND glb.je_batch_id = glh.je_batch_id
    AND glh.actual_flag = 'B'
    AND glh.je_header_id = gll.je_header_id
    AND gcc.code_combination_id = gll.code_combination_id
    AND fu.user_id = glh.created_by
    AND gp.period_name = glh.period_name
    AND gl.period_set_name = gp.period_set_name
    AND glh.ledger_id = gl.ledger_id
    AND hou.set_of_books_id = gl.ledger_id;