APPS.IGI_IAC_EXTRACT_PKG SQL Statements

The following lines contain the word 'select', 'insert', 'update' or 'delete':

Line: 33

    select * from xla_events_gt
    where entity_code           = 'TRANSACTIONS'
    and  event_type_code       = 'INFLATION_REVALUATION';

Line: 40

    SELECT
          th.EVENT_ID                             ,
          bc.BOOK_TYPE_CODE                          ,
          bc.BOOK_TYPE_NAME                          ,
          bc.ORG_ID                                  ,
          th.revaluation_period                      ,
          decode(bc.GL_POSTING_ALLOWED_FLAG,'YES', 'Y', 'N') gl_transfer_flag,
          sysdate acc_date--Need to replace with period close date
      FROM fa_book_controls              bc,
           igi_iac_revaluations         th
     WHERE th.book_type_code = bc.book_type_code
	   and th.book_type_code = p_book_type_code
       and th.event_id = p_event_id
       AND th.revaluation_id    = p_reval_id;

Line: 57

   select
   adj.event_id,
   adj.book_type_code,
   th.category_id,
   adj.asset_id,
   adj.distribution_id,
   adj.set_of_books_id,
   adj.adjustment_id,
   amount_switch(adj.adjustment_type,adj.dr_cr_flag,adj.amount) amount,
   adj.dr_cr_flag,
   adj.adjustment_type,
   adj.transfer_to_gl_flag,
   adj.units_assigned,
   adj.period_counter,
   adj.adjustment_offset_type,
   adj.report_ccid,
   th.transaction_header_id,
   th.adjustment_id_out,
   th.transaction_type_code,
   th.transaction_sub_type,
   th.transaction_date_entered,
   th.mass_reference_id,
   th.adj_deprn_start_date,
   th.adjustment_status,
   th.revaluation_type_flag,
   lkp_adj.meaning adj_meaning,
   lkp_trn.meaning trn_meaning,
   decode(adj.adjustment_type,'BL RESERVE',code_combination_id,null) BL_RESERVE,
   decode(adj.adjustment_type,'OP EXPENSE',code_combination_id,null) OP_EXPENSE,
   decode(adj.adjustment_type,'GENERAL FUND',code_combination_id,null) GENERAL_FUND,
   decode(adj.adjustment_type,'REVAL RESERVE',code_combination_id,null) REVAL_RESERVE,
   decode(adj.adjustment_type,'REVAL RSV RET',code_combination_id,null) REVAL_RSV_RET,
   decode(adj.adjustment_type,'INTERCO AP',code_combination_id,null) INTERCO_AP,
   decode(adj.adjustment_type,'INTERCO AR',code_combination_id,null) INTERCO_AR,
   decode(adj.adjustment_type,'COST',code_combination_id,null) COST,
   decode(adj.adjustment_type,'RESERVE',code_combination_id,null) RESERVE,
   decode(adj.adjustment_type,'EXPENSE',code_combination_id,null) EXPENSE,
   decode(adj.adjustment_type,'NBV RETIRED',code_combination_id,null) NBV_RETIRED,
   sob.currency_code
   from igi_iac_adjustments adj, igi_iac_transaction_headers th,
   igi_lookups lkp_adj, igi_lookups lkp_trn, gl_sets_of_books sob
   where adj.adjustment_id = th.adjustment_id
   and adj.event_id = th.event_id
   and lkp_adj.lookup_type = 'IGI_IAC_ADJUSTMENT_TYPES'
   and lkp_trn.lookup_type = 'IGI_IAC_TRANSACTION_TYPES'
   and adj.adjustment_type = lkp_adj.lookup_code
   and th.transaction_type_code = lkp_trn.lookup_code
   and th.book_type_code = p_book_type_code
   and th.event_id = p_event_id
   and adj.set_of_books_id = sob.set_of_books_id
   and adj.transfer_to_gl_flag = 'Y';

Line: 120

          INSERT INTO FA_XLA_EXT_HEADERS_B_GT (
          event_id                                ,
          BOOK_TYPE_CODE                          ,
          BOOK_DESCRIPTION                        ,
          ORG_ID                                  ,
          PERIOD_COUNTER                          ,
          TRANSFER_TO_GL_FLAG                     ,
          accounting_date                         )
          values (
          j.EVENT_ID                             ,
          j.BOOK_TYPE_CODE                          ,
          j.BOOK_TYPE_NAME                          ,
          j.ORG_ID                                  ,
          j.revaluation_period                      ,
          j.gl_transfer_flag,
          j.acc_date );

Line: 141

      INSERT INTO FA_XLA_EXT_LINES_B_GT(
          EVENT_ID                             ,
          LINE_NUMBER                          ,
          DISTRIBUTION_TYPE_CODE               ,
          transaction_header_id                ,
          adjustment_line_id                   ,
          LEDGER_ID                            ,
          BOOK_TYPE_CODE                       ,
          ASSET_ID                             ,
          CAT_ID                               ,
          entered_amount                       ,
          currency_code)
      values (
          k.event_id,
          v_counter,
          'TRX',
          k.adjustment_id,
          v_counter,
          k.set_of_books_id,
          k.book_type_code,
          k.asset_id,
          k.category_id,
          k.amount,
          k.currency_code);

Line: 167

         insert into igi_iac_xla_lines_gt (
            IAC_EVENT_ID,
            IAC_LINE_NUMBER,
            IAC_BOOK_TYPE_CODE,
            IAC_CATEGORY_ID,
            IAC_ASSET_ID,
            IAC_DISTRIBUTION_ID,
            IAC_LEDGER_ID,
            IAC_ADJUSTMENT_ID,
            IAC_AMOUNT,
            IAC_DR_CR_FLAG,
            IAC_ADJUSTMENT_TYPE,
            IAC_TRANSFER_TO_GL_FLAG,
            IAC_UNITS_ASSIGNED,
            IAC_PERIOD_COUNTER,
            IAC_ADJUSTMENT_OFFSET_TYPE,
            IAC_REPORT_CCID,
            IAC_TRANSACTION_HEADER_ID,
            IAC_ADJUSTMENT_ID_OUT,
            IAC_TRANSACTION_TYPE_CODE,
            IAC_TRANSACTION_SUB_TYPE,
            IAC_TRANSACTION_DATE_ENTERED,
            IAC_MASS_REFERENCE_ID,
            IAC_ADJ_DEPRN_START_DATE,
            IAC_ADJUSTMENT_STATUS,
            IAC_REVALUATION_TYPE_FLAG,
            IAC_ADJUSTMENT_TYPE_MEANING,
            IAC_TRANSACTION_TYPE_MEANING,
            IAC_BACKLOG_DEPRN_RSV_CCID,
            IAC_OPERATING_EXPENSE_CCID,
            IAC_GENERAL_FUND_CCID,
            IAC_REVAL_RESERVE_CCID,
            IAC_REVAL_RESERVE_RET_CCID,
            IAC_INTERCO_AP_CCID,
            IAC_INTERCO_AR_CCID,
            IAC_ASSET_COST_CCID,
            IAC_DEPRN_RESERVE_CCID,
            IAC_DEPRN_EXPENSE_CCID,
            IAC_NBV_RETIRED_GAIN_CCID,
            IAC_CURRENCY_CODE)
            values (
            k.event_id,
            v_counter,
            k.book_type_code,
            k.category_id,
            k.asset_id,
            k.distribution_id,
            k.set_of_books_id,
            k.adjustment_id,
            k.amount,
            k.dr_cr_flag,
            k.adjustment_type,
            k.transfer_to_gl_flag,
            k.units_assigned,
            k.period_counter,
            k.adjustment_offset_type,
            k.report_ccid,
            k.transaction_header_id,
            k.adjustment_id_out,
            k.transaction_type_code,
            k.transaction_sub_type,
            k.transaction_date_entered,
            k.mass_reference_id,
            k.adj_deprn_start_date,
            k.adjustment_status,
            k.revaluation_type_flag,
            k.adj_meaning,
            k.trn_meaning,
            k.BL_RESERVE,
            k.OP_EXPENSE,
            k.GENERAL_FUND,
            k.REVAL_RESERVE,
            k.REVAL_RSV_RET,
            k.INTERCO_AP,
            k.INTERCO_AR,
            k.COST,
            k.RESERVE,
            k.EXPENSE,
            k.NBV_RETIRED,
            k.currency_code);

Line: 252

   /*delete from igi_iac_xla_lines_gt_tmp;

Line: 253

   insert into igi_iac_xla_lines_gt_tmp
   select * from igi_iac_xla_lines_gt;

Line: 256

   delete from FA_XLA_EXT_HEADERS_B_GT_tmp;

Line: 257

   insert into FA_XLA_EXT_HEADERS_B_GT_tmp
   select * from FA_XLA_EXT_HEADERS_B_GT;

Line: 260

   delete from FA_XLA_EXT_LINES_B_GT_tmp;

Line: 261

   INSERT INTO FA_XLA_EXT_LINES_B_GT_tmp
   select * from FA_XLA_EXT_LINES_B_GT;*/

Line: 281

    select * from xla_events_gt
    where entity_code in ('TRANSACTIONS','DEPRECIATION')
    and event_type_code in ('ADDITIONS','ADJUSTMENTS','TRANSFERS',
                            'CATEGORY_RECLASS', 'RETIREMENTS','REINSTATEMENTS','DEPRECIATION');

Line: 286

   select
   adj.event_id,
   adj.book_type_code,
   th.category_id,
   adj.asset_id,
   adj.distribution_id,
   adj.set_of_books_id,
   adj.adjustment_id,
   amount_switch(adj.adjustment_type,adj.dr_cr_flag,adj.amount) amount,
   adj.dr_cr_flag,
   adj.adjustment_type,
   adj.transfer_to_gl_flag,
   adj.units_assigned,
   adj.period_counter,
   adj.adjustment_offset_type,
   adj.report_ccid,
   th.transaction_header_id,
   th.adjustment_id_out,
   th.transaction_type_code,
   th.transaction_sub_type,
   th.transaction_date_entered,
   th.mass_reference_id,
   th.adj_deprn_start_date,
   th.adjustment_status,
   th.revaluation_type_flag,
   lkp_adj.meaning adj_meaning,
   lkp_trn.meaning trn_meaning,
   decode(adj.adjustment_type,'BL RESERVE',code_combination_id,null) BL_RESERVE,
   decode(adj.adjustment_type,'OP EXPENSE',code_combination_id,null) OP_EXPENSE,
   decode(adj.adjustment_type,'GENERAL FUND',code_combination_id,null) GENERAL_FUND,
   decode(adj.adjustment_type,'REVAL RESERVE',code_combination_id,null) REVAL_RESERVE,
   decode(adj.adjustment_type,'REVAL RSV RET',code_combination_id,null) REVAL_RSV_RET,
   decode(adj.adjustment_type,'INTERCO AP',code_combination_id,null) INTERCO_AP,
   decode(adj.adjustment_type,'INTERCO AR',code_combination_id,null) INTERCO_AR,
   decode(adj.adjustment_type,'COST',code_combination_id,null) COST,
   decode(adj.adjustment_type,'RESERVE',code_combination_id,null) RESERVE,
   decode(adj.adjustment_type,'EXPENSE',code_combination_id,null) EXPENSE,
   decode(adj.adjustment_type,'NBV RETIRED',code_combination_id,null) NBV_RETIRED,
   sob.currency_code
   from igi_iac_adjustments adj, igi_iac_transaction_headers th,
   igi_lookups lkp_adj, igi_lookups lkp_trn, gl_sets_of_books sob
   where adj.adjustment_id = th.adjustment_id
   and adj.event_id = th.event_id
   and lkp_adj.lookup_type = 'IGI_IAC_ADJUSTMENT_TYPES'
   and lkp_trn.lookup_type = 'IGI_IAC_TRANSACTION_TYPES'
   and adj.adjustment_type = lkp_adj.lookup_code
   and th.transaction_type_code = lkp_trn.lookup_code
   and th.book_type_code = p_book_type_code
   and th.event_id = p_event_id
   and adj.set_of_books_id = sob.set_of_books_id
   and adj.transfer_to_gl_flag = 'Y';

Line: 349

    select max(line_number) +1 into v_counter
    from FA_XLA_EXT_LINES_B_GT where
    event_id = i.event_id ;

Line: 356

      /*INSERT INTO FA_XLA_EXT_LINES_B_GT(
          EVENT_ID                             ,
          LINE_NUMBER                          ,
          DISTRIBUTION_TYPE_CODE               ,
          ledger_id,
          ASSET_ID                             ,
          deprn_run_id                         ,
          BOOK_TYPE_CODE                       ,
          distribution_id                      ,
          entered_amount                       ,
          currency_code
          )
      values (
          k.event_id,
          v_counter,
          'IAC',
          k.set_of_books_id,
          k.asset_id,
                   1,
          k.book_type_code,
          k.distribution_id,
          1,'USD');*/

Line: 379

           insert into fa_xla_ext_lines_b_gt (
           EVENT_ID                             ,
           LINE_NUMBER                          ,
           DISTRIBUTION_TYPE_CODE               ,
           LEDGER_ID                            ,
           CURRENCY_CODE                        ,
           ENTERED_AMOUNT                       ,
           BONUS_ENTERED_AMOUNT                 ,
           REVAL_ENTERED_AMOUNT                 ,
           GENERATED_CCID                       ,
           GENERATED_OFFSET_CCID                ,
           BONUS_GENERATED_CCID                 ,
           BONUS_GENERATED_OFFSET_CCID          ,
           REVAL_GENERATED_CCID                 ,
           REVAL_GENERATED_OFFSET_CCID          ,
           BOOK_TYPE_CODE                       ,
           ASSET_ID,
           BONUS_DEPRN_EXPENSE_ACCT,
           BONUS_RESERVE_ACCT,
           DEPRN_RESERVE_ACCT,
           REVAL_AMORT_ACCT,
           REVAL_RESERVE_ACCT,
           DEPRN_RUN_ID,
           DISTRIBUTION_ID,
           EXPENSE_ACCOUNT_CCID,
           TRANSACTION_HEADER_ID,
	   ADJUSTMENT_LINE_ID)
    select EVENT_ID                             ,
           v_counter                    ,
           'IAC'               ,
           LEDGER_ID                            ,
           CURRENCY_CODE                        ,
           ENTERED_AMOUNT                       ,
           BONUS_ENTERED_AMOUNT                 ,
           REVAL_ENTERED_AMOUNT                 ,
           GENERATED_CCID                       ,
           GENERATED_OFFSET_CCID                ,
           BONUS_GENERATED_CCID                 ,
           BONUS_GENERATED_OFFSET_CCID          ,
           REVAL_GENERATED_CCID                 ,
           REVAL_GENERATED_OFFSET_CCID          ,
           BOOK_TYPE_CODE                       ,
           ASSET_ID,
           BONUS_DEPRN_EXPENSE_ACCT,
           BONUS_RESERVE_ACCT,
           DEPRN_RESERVE_ACCT,
           REVAL_AMORT_ACCT,
           REVAL_RESERVE_ACCT,
           DEPRN_RUN_ID,
           DISTRIBUTION_ID,
           EXPENSE_ACCOUNT_CCID,
           TRANSACTION_HEADER_ID,
	   ADJUSTMENT_LINE_ID
           from fa_xla_ext_lines_b_gt
           where event_id = i.event_id
           and rownum = 1;

Line: 436

     insert into igi_iac_xla_lines_gt (
     IAC_EVENT_ID,
   IAC_LINE_NUMBER,
   IAC_BOOK_TYPE_CODE,
   IAC_CATEGORY_ID,
   IAC_ASSET_ID,
   IAC_DISTRIBUTION_ID,
   IAC_LEDGER_ID,
   IAC_ADJUSTMENT_ID,
   IAC_AMOUNT,
   IAC_DR_CR_FLAG,
   IAC_ADJUSTMENT_TYPE,
   IAC_TRANSFER_TO_GL_FLAG,
   IAC_UNITS_ASSIGNED,
   IAC_PERIOD_COUNTER,
   IAC_ADJUSTMENT_OFFSET_TYPE,
   IAC_REPORT_CCID,
   IAC_TRANSACTION_HEADER_ID,
   IAC_ADJUSTMENT_ID_OUT,
   IAC_TRANSACTION_TYPE_CODE,
   IAC_TRANSACTION_SUB_TYPE,
   IAC_TRANSACTION_DATE_ENTERED,
   IAC_MASS_REFERENCE_ID,
   IAC_ADJ_DEPRN_START_DATE,
   IAC_ADJUSTMENT_STATUS,
   IAC_REVALUATION_TYPE_FLAG,
   IAC_ADJUSTMENT_TYPE_MEANING,
   IAC_TRANSACTION_TYPE_MEANING,
   IAC_BACKLOG_DEPRN_RSV_CCID,
   IAC_OPERATING_EXPENSE_CCID,
   IAC_GENERAL_FUND_CCID,
   IAC_REVAL_RESERVE_CCID,
   IAC_REVAL_RESERVE_RET_CCID,
   IAC_INTERCO_AP_CCID,
   IAC_INTERCO_AR_CCID,
   IAC_ASSET_COST_CCID,
   IAC_DEPRN_RESERVE_CCID,
   IAC_DEPRN_EXPENSE_CCID,
   IAC_NBV_RETIRED_GAIN_CCID,
   IAC_CURRENCY_CODE)
    values (
            k.event_id,
            v_counter,
            k.book_type_code,
            k.category_id,
            k.asset_id,
            k.distribution_id,
            k.set_of_books_id,
            k.adjustment_id,
            k.amount,
            k.dr_cr_flag,
            k.adjustment_type,
            k.transfer_to_gl_flag,
            k.units_assigned,
            k.period_counter,
            k.adjustment_offset_type,
            k.report_ccid,
            k.transaction_header_id,
            k.adjustment_id_out,
            k.transaction_type_code,
            k.transaction_sub_type,
            k.transaction_date_entered,
            k.mass_reference_id,
            k.adj_deprn_start_date,
            k.adjustment_status,
            k.revaluation_type_flag,
            k.adj_meaning,
            k.trn_meaning,
            k.BL_RESERVE,
            k.OP_EXPENSE,
            k.GENERAL_FUND,
            k.REVAL_RESERVE,
            k.REVAL_RSV_RET,
            k.INTERCO_AP,
            k.INTERCO_AR,
            k.COST,
            k.RESERVE,
            k.EXPENSE,
            k.NBV_RETIRED,
            k.currency_code);

Line: 522

/*   delete from igi_iac_xla_lines_gt_tmp;

Line: 523

   insert into igi_iac_xla_lines_gt_tmp
   select * from igi_iac_xla_lines_gt;

Line: 526

   delete from FA_XLA_EXT_HEADERS_B_GT_tmp;

Line: 527

   insert into FA_XLA_EXT_HEADERS_B_GT_tmp
   select * from FA_XLA_EXT_HEADERS_B_GT;

Line: 530

   delete from FA_XLA_EXT_LINES_B_GT_tmp;

Line: 531

   INSERT INTO FA_XLA_EXT_LINES_B_GT_tmp
   select * from FA_XLA_EXT_LINES_B_GT;*/

Line: 549

    select * from igi_iac_xla_lines_gt;

Line: 557

   insert into igi_iac_xla_lines_gt (
   IAC_EVENT_ID,
   IAC_LINE_NUMBER,
   IAC_BOOK_TYPE_CODE,
   IAC_CATEGORY_ID,
   IAC_ASSET_ID,
   IAC_DISTRIBUTION_ID,
   IAC_LEDGER_ID,
   IAC_ADJUSTMENT_ID,
   IAC_AMOUNT,
   IAC_DR_CR_FLAG,
   IAC_ADJUSTMENT_TYPE,
   IAC_TRANSFER_TO_GL_FLAG,
   IAC_UNITS_ASSIGNED,
   IAC_PERIOD_COUNTER,
   IAC_ADJUSTMENT_OFFSET_TYPE,
   IAC_REPORT_CCID,
   IAC_TRANSACTION_HEADER_ID,
   IAC_ADJUSTMENT_ID_OUT,
   IAC_TRANSACTION_TYPE_CODE,
   IAC_TRANSACTION_SUB_TYPE,
   IAC_TRANSACTION_DATE_ENTERED,
   IAC_MASS_REFERENCE_ID,
   IAC_ADJ_DEPRN_START_DATE,
   IAC_ADJUSTMENT_STATUS,
   IAC_REVALUATION_TYPE_FLAG,
   IAC_ADJUSTMENT_TYPE_MEANING,
   IAC_TRANSACTION_TYPE_MEANING,
   IAC_BACKLOG_DEPRN_RSV_CCID,
   IAC_OPERATING_EXPENSE_CCID,
   IAC_GENERAL_FUND_CCID,
   IAC_REVAL_RESERVE_CCID,
   IAC_REVAL_RESERVE_RET_CCID,
   IAC_INTERCO_AP_CCID,
   IAC_INTERCO_AR_CCID,
   IAC_ASSET_COST_CCID,
   IAC_DEPRN_RESERVE_CCID,
   IAC_DEPRN_EXPENSE_CCID,
   IAC_NBV_RETIRED_GAIN_CCID,
   IAC_CURRENCY_CODE)
   select
   adj.event_id,
   rownum,
   -- adj.distribution_id,
   adj.book_type_code,
   th.category_id,
   adj.asset_id,
   adj.distribution_id,
   adj.set_of_books_id,
   adj.adjustment_id,
   amount_switch(adj.adjustment_type,adj.dr_cr_flag,adj.amount),
   adj.dr_cr_flag,
   adj.adjustment_type,
   adj.transfer_to_gl_flag,
   adj.units_assigned,
   adj.period_counter,
   adj.adjustment_offset_type,
   adj.report_ccid,
   th.transaction_header_id,
   th.adjustment_id_out,
   th.transaction_type_code,
   th.transaction_sub_type,
   th.transaction_date_entered,
   th.mass_reference_id,
   th.adj_deprn_start_date,
   th.adjustment_status,
   th.revaluation_type_flag,
   lkp_adj.meaning,
   lkp_trn.meaning,
   decode(adj.adjustment_type,'BL RESERVE',code_combination_id,null),
   decode(adj.adjustment_type,'OP EXPENSE',code_combination_id,null),
   decode(adj.adjustment_type,'GENERAL FUND',code_combination_id,null),
   decode(adj.adjustment_type,'REVAL RESERVE',code_combination_id,null),
   decode(adj.adjustment_type,'REVAL RSV RET',code_combination_id,null),
   decode(adj.adjustment_type,'INTERCO AP',code_combination_id,null),
   decode(adj.adjustment_type,'INTERCO AR',code_combination_id,null),
   decode(adj.adjustment_type,'COST',code_combination_id,null),
   decode(adj.adjustment_type,'RESERVE',code_combination_id,null),
   decode(adj.adjustment_type,'EXPENSE',code_combination_id,null),
   decode(adj.adjustment_type,'NBV RETIRED',code_combination_id,null),
   sob.currency_code
   from igi_iac_adjustments adj, igi_iac_transaction_headers th,
   igi_lookups lkp_adj, igi_lookups lkp_trn, xla_events_gt ctlgd,
   gl_sets_of_books sob
   where adj.adjustment_id = th.adjustment_id
   and adj.event_id = th.event_id
   and lkp_adj.lookup_type = 'IGI_IAC_ADJUSTMENT_TYPES'
   and lkp_trn.lookup_type = 'IGI_IAC_TRANSACTION_TYPES'
   and adj.adjustment_type = lkp_adj.lookup_code
   and th.transaction_type_code = lkp_trn.lookup_code
   and ctlgd.valuation_method      = th.book_type_code
   and ctlgd.event_id      = th.event_id
   and adj.set_of_books_id = sob.set_of_books_id
   and ctlgd.entity_code           ='DEPRECIATION'
   and ctlgd.event_type_code       ='DEPRECIATION'
   and adj.transfer_to_gl_flag = 'Y';

Line: 655

/*   delete from igi_iac_xla_lines_gt_tmp;

Line: 656

   insert into igi_iac_xla_lines_gt_tmp
   select * from igi_iac_xla_lines_gt;

Line: 659

   delete from FA_XLA_EXT_HEADERS_B_GT_tmp;

Line: 660

   insert into FA_XLA_EXT_HEADERS_B_GT_tmp
   select * from FA_XLA_EXT_HEADERS_B_GT;

Line: 663

   delete from FA_XLA_EXT_LINES_B_GT_tmp;

Line: 664

   INSERT INTO FA_XLA_EXT_LINES_B_GT_tmp
   select * from FA_XLA_EXT_LINES_B_GT;*/