Friday, June 5, 2020
Query to fetch GL Budget Journals in Oracle Apps R12
Tested Instance: R12.2.4
Tables Involved:
- GL_BC_PACKETS_HISTS
- GL_LEDGERS
- HR_OPERATING_UNITS
- GL_JE_BATCHES
- GL_JE_HEADERS
- GL_JE_LINES
- GL_CODE_COMBINATIONS_KFV
- FND_USER
- 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;
Do you think this Article is useful?
Subscribe to:
Post Comments (Atom)
Disclaimer
The ideas, thoughts and concepts expressed here are my own. They, in no way reflect those of my employer or any other organization/client that I am associated. The articles presented doesn't imply to any particular organization or client and are meant only for knowledge Sharing purpose. The articles can't be reproduced or copied without the Owner's knowledge or permission.
0 Responses to “Query to fetch GL Budget Journals in Oracle Apps R12”
Post a Comment