Wednesday, 3 January 2018

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

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.

3 comments:

  1. good blog. Thank you so much sharing that valuable blog.. Inventory Audit | Stock Audit | Fixed Assets Audit


    ReplyDelete
  2. Thanks for sharing; I would like to see your updates regularly so keep blogging.
    Regards, Duplicate Payment Recovery
    Continuous Transaction Monitoring
    AP Vendor Helpdesk

    ReplyDelete
  3. Thanks for sharing such a great article about Punchout CXML, it will help me in my work.
    Punchout CXML Catlog




    ReplyDelete