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
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 GJH.ledger_id = GL.ledger_id
AND GJH.actual_flag = ‘A’
AND GJH.status = ‘P’
AND GJH.je_source = ‘Assets’
AND FB.date_ineffective IS NULL
AND GJC.user_je_category_name IN(‘Addition’, ‘CIP Addition’, ‘Adjustment’, ‘CIP Adjustment’,’Retirement’,’CIP Retirement’)
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.default_effective_date >= lc_gl_date_from
AND GJH.default_effective_date <= lc_gl_date_to
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 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
SELECT GLCC.concatenated_segments segments
,GJL.period_name
,GJH.name journal_name
,GJB.name batch_name
,GJH.je_source journal_source
,GJH.je_category journal_category
,GLCC.segment1 entity_segment
,GLCC.segment2 project_segment
,FFV.attribute1 project_vertical_dff
,GLCC.segment3
,GLCC.segment4
,GLCC.segment5
,GLCC.segment6
,GLCC.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
,(SELECT NAME
FROM xla_event_classes_tl XECT
WHERE XECT.event_class_code = XDL.event_class_code
AND XECT.application_id = XDL.application_id) sla_event_class
,XAH.event_type_code sla_event_type
,TO_CHAR(FDS.asset_id) document_number
,FAD.description document_description
,TO_CHAR (FB.date_placed_in_service, ‘DD-MON-YYYY’) document_date
,NVL((XDL.unrounded_accounted_cr)*-1,(XDL.unrounded_accounted_dr)) transaction_cur_amount
,GL.currency_code entered_currency_code
,NVL((XDL.unrounded_accounted_cr)*-1,(XDL.unrounded_accounted_dr)) functional_currency_amount
FROM fa_additions FAD
,fa_books FB
,fa_book_controls FBC
,fa_deprn_summary FDS
,xla_distribution_links XDL
,xla_ae_lines XAL
,xla_ae_headers XAH
,gl_import_references GIR
,gl_je_lines GJL
,gl_je_headers GJH
,gl_je_batches GJB
,gl_code_combinations_kfv GLCC
,gl_ledgers GL
,fnd_flex_value_sets FVS
,fnd_flex_values FFV
WHERE 1=1
AND FAD.asset_id = FB.asset_id
AND FB.book_type_code = FBC.book_type_code
AND FB.DATE_INEFFECTIVE IS NULL
AND FAD.asset_id = FDS.asset_id
AND FB.book_type_code = FDS.book_type_code
AND FBC.book_type_code = FDS.book_type_code
AND fds.deprn_source_code =’DEPRN’
AND FDS.asset_id = XDL.source_distribution_id_num_1
AND FB.book_type_code = XDL.source_distribution_id_char_4
AND FBC.set_of_books_id = GL.ledger_id
AND XDL.ae_header_id = XAL.ae_header_id
AND XDL.ae_line_num = XAL.ae_line_num
AND XAL.ae_header_id = XAH.ae_header_id
AND XAH.application_id = XAL.application_id
AND XAL.gl_sl_link_id = GIR.gl_sl_link_id
AND XAL.gl_sl_link_table = GIR.gl_sl_link_table
AND GIR.je_line_num = GJL.je_line_num
AND GIR.je_header_id = GJL.je_header_id
AND GIR.je_header_id = GJH.je_header_id
AND GJH.je_batch_id = GJB.je_batch_id
AND GJL.code_combination_id = GLCC.code_combination_id
AND GJH.status = ‘P’
— and UPPER(XDL.rounding_class_code) =’ASSET’
AND UPPER(FDS.deprn_source_code) IN (‘BOOKS’,’DEPRN’)
AND UPPER (GJH.je_source) = ‘ASSETS’
AND UPPER (GJH.je_category) = ‘DEPRECIATION’
AND FFV.flex_value_set_id = FVS.flex_value_set_id(+)
AND GLCC.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 GLCC.segment1 BETWEEN lc_segment_from(1) AND lc_segment_to(1)
AND GLCC.segment2 BETWEEN lc_segment_from(2) AND lc_segment_to(2)
AND GLCC.segment3 BETWEEN lc_segment_from(3) AND lc_segment_to(3)
AND GLCC.segment4 BETWEEN lc_segment_from(4) AND lc_segment_to(4)
AND GLCC.segment5 BETWEEN lc_segment_from(5) AND lc_segment_to(5)
AND GLCC.segment6 BETWEEN lc_segment_from(6) AND lc_segment_to(6)
AND GLCC.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’))
–SK.
Recommended Posts
