Thursday 13 June 2013

gl to fa adjustments link

/* Formatted on 6/13/2013 1:20:20 PM (QP5 v5.114.809.3010) */
SELECT   jb.je_batch_id,
         jb.name Batch_Name,
         jb.description Batch_Description,
         jh.je_header_id,
         le.name ledger_name,
         jes.user_je_source_name Journal_Source,
         jec.user_je_category_name Journal_Category,
         jh.period_name Journal_Period,
         jh.name Journal_Name,
         jh.description Journal_Description,
         jh.currency_code Journal_Currency,
         DECODE (
            jh.status,
            'P',
            'Posted',
            'U',
            'Unposted',
            'F',
            'Error7 - Showing invalid journal entry lines or no journal entry lines',
            'K',
            'Error10 - Showing unbalanced intercompany journal entry',
            'Z',
            'Error7 - Showing invalid journal entry lines or no journal entry lines',
            'Unknown'
         )
            Journal_Status,
         DECODE (jh.actual_flag,
                 'A',
                 'Actual',
                 'B',
                 'Budget',
                 'E',
                 'Encumbrance')
            Journal_Type,
         jh.posted_date Journal_Posted_Date,
         jh.creation_date Journal_Creation_Date,
         jh.accrual_rev_effective_date,
         jh.accrual_rev_period_name,
         jh.accrual_rev_status,
         jh.accrual_rev_je_header_id,
         jh.reversed_je_header_id,
         jh.currency_conversion_rate Journal_Curr_Conv_Rate,
         jh.currency_conversion_type Journal_Curr_Conv_Type,
         jh.currency_conversion_date Journal_Curr_Conv_Date,
         jh.external_reference Journal_external_Ref,
         jl.je_line_num Journal_Line_Num,
         jl.effective_date Journal_Effective_Date,
         jl.entered_dr Journal_Entered_DR,
         jl.entered_cr Journal_Entered_CR,
         jl.accounted_dr Journal_Accounted_DR,
         jl.accounted_cr Journal_Accounted_CR,
         jl.description Jonal_Description,
         jl.gl_sl_link_id,
         jl.gl_sl_link_table,
         gcc.code_combination_id gl_account_cc_id,
         gcc.concatenated_segments GL_Account_String,
         gcca.code_combination_id sla_account_cc_id,
         gcca.concatenated_segments sla_Account_String,
         NULL budget_name,
         NULL budget_type,
         NULL budget_version_id,
         NULL Budget_Status,
         NULL Budget_description,
         NULL encumbrance_type--from assets
         ,
         faa.transaction_header_id,
         faa.source_type_code,
         faa.adjustment_type,
         DECODE (xdl.application_id,
                 NULL, ael.entered_dr,
                 xdl.unrounded_entered_dr)
            sla_entered_dr,
         DECODE (xdl.application_id,
                 NULL, ael.entered_cr,
                 xdl.unrounded_entered_cr)
            sla_entered_cr,
         DECODE (xdl.application_id,
                 NULL, ael.accounted_dr,
                 xdl.unrounded_accounted_dr)
            sla_accounted_dr,
         DECODE (xdl.application_id,
                 NULL, ael.accounted_cr,
                 xdl.unrounded_accounted_cr)
            sla_accounted_cr,
         (NVL (
             DECODE (xdl.application_id,
                     NULL, ael.entered_dr,
                     xdl.unrounded_entered_dr),
             0
          )
          - NVL (
               DECODE (xdl.application_id,
                       NULL, ael.entered_cr,
                       xdl.unrounded_entered_cr),
               0
            ))
            sla_entered_net,
         (NVL (
             DECODE (xdl.application_id,
                     NULL, ael.accounted_dr,
                     xdl.unrounded_accounted_dr),
             0
          )
          - NVL (
               DECODE (xdl.application_id,
                       NULL, ael.accounted_cr,
                       xdl.unrounded_accounted_cr),
               0
            ))
            sla_accounted_net,
         faa.code_combination_id,
         faa.book_type_code,
         faa.asset_id,
         faa.distribution_id,
         faa.annualized_adjustment,
         faa.period_counter_adjusted,
         faa.period_counter_created,
         faa.asset_invoice_id,
         fa.asset_number,
         fa.description Asset_description,
         fa.current_units,
         fa.asset_type,
         fa.tag_number,
         fa.serial_number,
         fa.model_number,
         fa.property_type_code,
         ael.description sla_description                         --Unique Keys
                                        ,
         jl.je_line_num,
         ael.ae_header_id,
         ael.ae_line_num,
         ael.application_id,
         xdl.ref_ae_header_id,
         xdl.temp_line_num,
         faa.adjustment_line_id
  FROM   gl_ledgers le,
         gl_je_batches jb,
         gl_je_headers jh,
         gl_je_lines jl,
         gl_code_combinations_kfv gcc,
         gl_code_combinations_kfv gcca,
         gl_je_sources jes,
         gl_je_categories jec,
         gl_import_references gir,
         xla_ae_lines ael,
         xla_distribution_links xdl,
         fa_adjustments faa,
         fa_additions fa
 WHERE       le.ledger_id = jh.ledger_id
         AND jb.je_batch_id = jh.je_batch_id
         AND jh.je_header_id = jl.je_header_id
         AND jl.code_combination_id = gcc.code_combination_id
         AND jh.je_source = jes.je_source_name
         AND jh.je_category = jec.je_category_name
         AND jl.je_header_id = gir.je_header_id
         AND jl.je_line_num = gir.je_line_num
         AND gir.gl_sl_link_table = 'XLAJEL'
         AND gir.gl_sl_link_id = ael.gl_sl_link_id(+)
         AND gir.gl_sl_link_table = ael.gl_sl_link_table(+)
         AND ael.code_combination_id = gcca.code_combination_id(+)
         AND ael.ae_header_id = xdl.ae_header_id(+)
         AND ael.ae_line_num = xdl.ae_line_num(+)
         AND xdl.event_class_code(+) <> 'DEPRECIATION'
         AND xdl.source_distribution_id_num_2 = faa.adjustment_line_id(+)
         AND faa.asset_id = fa.asset_id(+)
         AND le.object_type_code = 'L'
        -- AND gl_security_pkg.validate_access (jh.ledger_id) = 'TRUE'
         AND jh.je_source = 'Assets'
         AND jh.je_category = 'Addition'

No comments:

Post a Comment