GL DRILL Down Query From GL into Fixed Assets Sub ledger Module.

Introduction

This Post is about to GL DRILL Down Query From GL into Fixed Assets Sub ledger Module in oracle ebs r12.

The below query will provide the complete breakup of posted Journals with transaction distribution wise breakup details of Fixed assets(all sources) sub-ledger modules.

SELECT GCC.concatenated_segments segments,

GJL.period_name,

GJH.NAME journal_name,

GJB.NAME batch_name,

GJH.je_source journal_source,

GJH.je_category journal_category,

GCC.segment1 entity_segment,

GCC.segment2 project_segment,

FFV.attribute1 project_vertical_dff,

GCC.segment3,

GCC.segment4,

GCC.segment5,

GCC.segment6,

GCC.segment7,

TO_CHAR (GJH.doc_sequence_value) gl_doc_no,

TO_CHAR (GJH.default_effective_date, ‘DD-MON-YYYY’) gl_date,

TO_CHAR (GJH.posted_date, ‘DD-MON-YYYY’) posted_date,

NULL sla_event_class,

XAH.event_type_code sla_event_type,

TO_CHAR (FTH.asset_id) document_number,

FT.description document_description,

TO_CHAR (FB.date_placed_in_service, ‘DD-MON-YYYY’) document_date,

NVL ((XAL.unrounded_entered_cr) * -1,XAL.unrounded_entered_dr) transaction_cur_amount,

GL.currency_code entered_currency_code,

NVL((XAL.unrounded_accounted_cr)*-1,XAL.unrounded_accounted_dr) functional_currency_amount,

FROM xla_ae_lines XAL,

xla_ae_headers XAH,

xla.xla_transaction_entities XTE,

gl_je_headers GJH,

gl_je_lines GJL,

gl_je_batches GJB,

gl_import_references GIR,

gl_je_categories GJC,

gl_ledgers GL,

gl_code_combinations_kfv GCC,

fa_additions_tl FT,

fa_books FB,

fa_transaction_headers FTH,

fnd_flex_value_sets FVS,

fnd_flex_values FFV

WHERE 1 = 1

AND GJL.je_header_id = GJH.je_header_id

AND GJC.je_category_name = GJH.je_category

AND GCC.code_combination_id = GJL.code_combination_id

AND GIR.je_header_id = GJH.je_header_id

AND XAL.gl_sl_link_id = GIR.gl_sl_link_id

AND GIR.je_line_num = GJL.je_line_num

AND GJB.je_batch_id = GJH.je_batch_id

AND XAH.ae_header_id = XAL.ae_header_id

AND XTE.entity_id = XAH.entity_id

AND FB.date_ineffective IS NULL

AND GJH.actual_flag = ‘A’

AND GJH.status = ‘P’

AND GJH.je_source = ‘Assets’

AND GJC.user_je_category_name IN (‘Transfer’, ‘Reclass’)

AND GJH.je_source <> ‘Consolidation’

AND FT.asset_id = FTH.asset_id

AND FT.asset_id = FB.asset_id

AND XTE.source_id_int_1(+) = FTH.transaction_header_id

AND FB.book_type_code = FTH.book_type_code

AND GJH.ledger_id = GL.ledger_id

AND FFV.flex_value_set_id = FVS.flex_value_set_id(+)

AND GCC.segment2 = FFV.flex_value(+)

AND UPPER (FVS.flex_value_set_name) = UPPER (‘PLL_Project’)

AND GJH.default_effective_date >= lc_gl_date_from

AND GJH.default_effective_date <= lc_gl_date_to

AND TRUNC (gjh.posted_date) BETWEEN NVL (lc_gl_posted_from,TRUNC (gjh.posted_date))AND NVL (lc_gl_posted_to,TRUNC (gjh.posted_date))

AND GJH.je_source = NVL (p_gl_source, GJH.je_source)

AND GJH.je_category = NVL (p_gl_category, GJH.je_category)

— AND gcc.concatenated_segments BETWEEN (p_account_from) AND (p_account_to)

AND GCC.segment1 BETWEEN lc_segment_from(1) AND lc_segment_to(1)

AND GCC.segment2 BETWEEN lc_segment_from(2) AND lc_segment_to(2)

AND GCC.segment3 BETWEEN lc_segment_from(3) AND lc_segment_to(3)

AND GCC.segment4 BETWEEN lc_segment_from(4) AND lc_segment_to(4)

AND GCC.segment5 BETWEEN lc_segment_from(5) AND lc_segment_to(5)

AND GCC.segment6 BETWEEN lc_segment_from(6) AND lc_segment_to(6)

AND GCC.segment7 BETWEEN lc_segment_from(7) AND lc_segment_to(7)

AND NVL (FFV.attribute1, ‘-1’) = NVL (p_proj_vertical_dff, NVL (FFV.attribute1, ‘-1’))

UNION ALL