eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.

APPS.FA_ASSET_TRACE_PKG SQL Statements

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

Line: 180


     SELECT asset_number
     INTO g_asset_number
     FROM fa_additions_b
     where asset_id = g_asset_id;

Line: 187


        SELECT asset_id
          INTO l_asset_id
          FROM fa_additions_b
         WHERE asset_number = p_asset_number;

Line: 194


        SELECT count(*)
          INTO l_count
          FROM fa_books
         WHERE asset_id = g_asset_id
           AND book_type_code = g_book;

Line: 283


   select release_name
   into l_app_version
   from fnd_product_groups;

Line: 424


   select count(1)
   into l_check_reval
   from fa_transaction_headers
   where transaction_type_code = 'REVALUATION'
   and asset_id = g_asset_id
   and book_type_code = g_book;

Line: 433


   l_options_tbl(l_idx).l_gen_select    := 'Y';

Line: 437


   l_options_tbl(l_idx).l_gen_select    := 'Y';

Line: 440


   l_options_tbl(l_idx).l_gen_select    := 'Y';

Line: 451


   l_options_tbl(l_idx).l_cnt_stmt      := 'SELECT count(1) FROM FA_TRANSACTION_HEADERS'
    ||' WHERE asset_id = '||g_asset_id||' and book_type_code in ('||''''||g_source_book||''''||','||''''||g_book||''''||')';

Line: 455


   l_options_tbl(l_idx).l_gen_select    := 'Y';

Line: 458


   l_options_tbl(l_idx).l_gen_select    := 'Y';

Line: 468


   l_options_tbl(l_idx).l_cnt_stmt      := 'SELECT count(1) FROM FA_DISTRIBUTION_HISTORY'
    ||' WHERE asset_id = '||g_asset_id||' and book_type_code = '||''''||g_source_book||'''';

Line: 472


   l_options_tbl(l_idx).l_gen_select    := 'Y';

Line: 482


   l_options_tbl(l_idx).l_gen_select    := 'Y';

Line: 494


     l_options_tbl(l_idx).l_gen_select   := 'Y';

Line: 499


       ||' and mass_reval_id in (select mass_transaction_id from fa_transaction_headers where transaction_type_code = ''REVALUATION'''
	   ||' and asset_id = '||g_asset_id||' and book_type_code = '||'''' || g_book || ''''||')';

Line: 501


     l_options_tbl(l_idx).l_cnt_stmt     := 'Select count(1) '||l_options_tbl(l_idx).l_add_clause;

Line: 504


     l_options_tbl(l_idx).l_gen_select   := 'Y';

Line: 509


       ||' and mass_reval_id in (select mass_transaction_id from fa_transaction_headers where transaction_type_code = ''REVALUATION'''
	   ||' and asset_id = '||g_asset_id||' and book_type_code = '||'''' || g_book || ''''||')';

Line: 511


     l_options_tbl(l_idx).l_cnt_stmt     := 'Select count(1) '||l_options_tbl(l_idx).l_add_clause;

Line: 515


   l_options_tbl(l_idx).l_gen_select    := 'Y';

Line: 527


   l_options_tbl(l_idx).l_gen_select    := 'Y';

Line: 530


   l_options_tbl(l_idx).l_gen_select    := 'Y';

Line: 539


   l_options_tbl(l_idx).l_gen_select    := 'Y';

Line: 548


   l_options_tbl(l_idx).l_gen_select   := 'Y';

Line: 557


   l_options_tbl(l_idx).l_gen_select   := 'Y';

Line: 566


   l_options_tbl(l_idx).l_gen_select    := 'Y';

Line: 576


   l_options_tbl(l_idx).l_gen_select    := 'Y';

Line: 585


   l_options_tbl(l_idx).l_gen_select    := 'Y';

Line: 595


   l_options_tbl(l_idx).l_gen_select    := 'Y';

Line: 604


   l_options_tbl(l_idx).l_gen_select   := 'Y';

Line: 612


   l_options_tbl(l_idx).l_cnt_stmt     := 'Select count(1) '||l_options_tbl(l_idx).l_add_clause;

Line: 615


   l_options_tbl(l_idx).l_gen_select   := 'Y';

Line: 623


   l_options_tbl(l_idx).l_gen_select   := 'Y';

Line: 632


   l_options_tbl(l_idx).l_gen_select   := 'Y';

Line: 642


   l_options_tbl(l_idx).l_gen_select   := 'Y';

Line: 652


   l_options_tbl(l_idx).l_gen_select   := 'Y';

Line: 661


   l_options_tbl(l_idx).l_gen_select   := 'Y';

Line: 670


   l_options_tbl(l_idx).l_gen_select   := 'Y';

Line: 677


   l_options_tbl(l_idx).l_cnt_stmt     := 'SELECT count(it.invoice_transaction_id) FROM FA_INVOICE_TRANSACTIONS it, fa_asset_invoices ai'
     ||' where ai.asset_id = ' || g_asset_id ||' and (ai.invoice_transaction_id_in = it.invoice_transaction_id '
     ||' or ai.invoice_transaction_id_out = it.invoice_transaction_id)';

Line: 682


   l_options_tbl(l_idx).l_gen_select   := 'Y';

Line: 689


   l_options_tbl(l_idx).l_cnt_stmt     := 'SELECT count(1) FROM FA_TRX_REFERENCES WHERE book_type_code = '||''''||g_book||''''
     ||' and (member_asset_id = '||g_asset_id||' or src_asset_id = '||g_asset_id||' or dest_asset_id = '||g_asset_id||')';

Line: 691


   l_options_tbl(l_idx).l_cnt_stmt     := 'Select count(1) '||l_options_tbl(l_idx).l_add_clause;

Line: 694


   l_options_tbl(l_idx).l_gen_select   := 'Y';

Line: 702


   l_options_tbl(l_idx).l_gen_select    := 'Y';

Line: 743


     g_options_tbl.delete;

Line: 781


   l_options_tbl(l_idx).l_gen_select   := 'Y';

Line: 792


   l_options_tbl(l_idx).l_gen_select   := 'Y';

Line: 803


   l_options_tbl(l_idx).l_gen_select   := 'Y';

Line: 812


   l_options_tbl(l_idx).l_cnt_stmt     := 'SELECT count(*) FROM fa_mass_additions WHERE asset_number = '
     ||''''||g_asset_number||'''' ||' OR add_to_asset_id = '||g_asset_id;

Line: 816


   l_options_tbl(l_idx).l_gen_select   := 'Y';

Line: 822


   l_options_tbl(l_idx).l_add_clause   := ' FROM FA_MASSADD_DISTRIBUTIONS WHERE mass_addition_id IN (SELECT mass_addition_id FROM '
     ||'fa_mass_additions WHERE asset_number =' ||''''||g_asset_number||''''||')';

Line: 824


   l_options_tbl(l_idx).l_cnt_stmt     := 'SELECT count(*) FROM fa_mass_additions WHERE asset_number = '
     ||''''||g_asset_number||'''' ||' OR add_to_asset_id = '||g_asset_id;

Line: 828


   l_options_tbl(l_idx).l_gen_select   := 'Y';

Line: 834


   l_options_tbl(l_idx).l_add_clause   := ' FROM FA_MC_MASS_RATES WHERE mass_addition_id IN (SELECT mass_addition_id FROM fa_mass_additions '
     ||'WHERE asset_number =' ||''''||g_asset_number||''''||')';

Line: 836


   l_options_tbl(l_idx).l_cnt_stmt     := 'SELECT count(*) FROM fa_mass_additions WHERE asset_number = '
     ||''''||g_asset_number||'''' ||' OR add_to_asset_id = '||g_asset_id;

Line: 840


   l_options_tbl(l_idx).l_gen_select   := 'Y';

Line: 844


   l_options_tbl(l_idx).l_gen_select   := 'Y';

Line: 848


   l_options_tbl(l_idx).l_gen_select    := 'Y';

Line: 858


   l_options_tbl(l_idx).l_gen_select   := 'Y';

Line: 862


   l_options_tbl(l_idx).l_gen_select   := 'Y';

Line: 867


   l_options_tbl(l_idx).l_add_clause   := ' FROM FA_CATEGORIES_B WHERE category_id IN (SELECT DISTINCT ah.category_id FROM fa_asset_history ah'
     ||' WHERE ah.asset_id = '||g_asset_id|| ') ORDER BY category_id';

Line: 871


   l_options_tbl(l_idx).l_gen_select   := 'Y';

Line: 876


   l_options_tbl(l_idx).l_add_clause   := ' FROM FA_CATEGORIES_TL WHERE category_id IN (SELECT DISTINCT ah.category_id FROM fa_asset_history ah'
     ||' WHERE ah.asset_id = '||g_asset_id|| ') ORDER BY category_id';

Line: 880


   l_options_tbl(l_idx).l_gen_select   := 'Y';

Line: 885


   l_options_tbl(l_idx).l_add_clause   := ' FROM fa_category_books WHERE category_id IN (SELECT DISTINCT ah.category_id FROM fa_asset_history ah'
	 ||' WHERE ah.asset_id = '||g_asset_id|| ') AND book_type_code = '||''''||g_book||'''' ||' ORDER BY category_id';

Line: 889


   l_options_tbl(l_idx).l_gen_select   := 'Y';

Line: 894


   l_options_tbl(l_idx).l_add_clause   := ' FROM fa_category_book_defaults WHERE category_id IN (SELECT DISTINCT ah.category_id '
     ||'FROM fa_asset_history ah WHERE ah.asset_id = '||g_asset_id||') AND book_type_code = '||''''||g_book||''''
	 ||' ORDER BY category_id, start_dpis';

Line: 920


   l_select_clause   varchar2(2000);

Line: 933


      SELECT APPLICATION_COLUMN_NAME, SEGMENT_NAME
      from fnd_id_flex_segments
      where application_id = 101
      and id_flex_code = 'GL#'
      AND ID_FLEX_NUM = l_flex_num;

Line: 1041


      l_select_clause := l_select_clause||','||l_dist_tbl(i);

Line: 1044


   l_select_clause := substr(l_select_clause,2,length(l_select_clause));

Line: 1048


     fa_debug_pkg.add(l_calling_fn, 'l_select_clause', l_select_clause,
                               p_log_level_rec => p_log_level_rec);

Line: 1067


     l_options_tbl(l_idx).l_gen_select   := 'N';

Line: 1069


     l_options_tbl(l_idx).l_col_order    := l_select_clause;

Line: 1075


             (SELECT '||l_tbl_cols(i).cCol||' from fa_category_books where book_type_code = '||''''||g_book||'''' ||
                ' and category_id in (SELECT DISTINCT ah.category_id FROM fa_asset_history ah
                WHERE ah.asset_id = '||g_asset_id||')) ORDER BY code_combination_id';

Line: 1078


       l_stmt := 'select count(1) '||l_options_tbl(l_idx).l_add_clause;

Line: 1081


             (SELECT distinct code_combination_id from fa_adjustments where book_type_code = '||''''||g_book||''''||
              ' and asset_id = '||g_asset_id||' and code_combination_id is not null) ORDER BY code_combination_id';

Line: 1083


       l_stmt := 'select count(1) '||l_options_tbl(l_idx).l_add_clause;

Line: 1086


             (select distinct code_combination_id from XLA_AE_LINES where application_id = 140 and ae_header_id in
             (select ae_header_id from xla_ae_headers where application_id = 140 and event_id '||get_event_list
             ||')) ORDER BY code_combination_id';

Line: 1089


       l_stmt := 'select count(1) '||l_options_tbl(l_idx).l_add_clause;

Line: 1092


             (SELECT code_combination_id FROM fa_distribution_history
              WHERE asset_id = '||g_asset_id||' AND transaction_header_id_out IS NULL) ORDER BY code_combination_id';

Line: 1094


       l_stmt := 'select count(1) '||l_options_tbl(l_idx).l_add_clause;

Line: 1097


             (SELECT FLEXBUILDER_DEFAULTS_CCID FROM fa_book_controls WHERE book_type_code = '||''''||g_book||''''||')';

Line: 1098


       l_stmt := 'select count(1) '||l_options_tbl(l_idx).l_add_clause;

Line: 1101


             (SELECT '||l_tbl_cols(i).cCol||' from fa_distribution_accounts WHERE distribution_id IN
               (SELECT distribution_id FROM fa_distribution_history WHERE asset_id = '||g_asset_id||'))
              ORDER BY code_combination_id';

Line: 1104


       l_stmt := 'select count(1) '||l_options_tbl(l_idx).l_add_clause;

Line: 1109


       l_options_tbl.delete(l_idx);

Line: 1116


   l_dist_tbl.delete;

Line: 1125


   l_options_tbl(l_idx).l_gen_select   := 'N';

Line: 1130


      ||',SETS.SECURITY_ENABLED_FLAG,STRU.FREEZE_FLEX_DEFINITION_FLAG,STRU.DYNAMIC_INSERTS_ALLOWED_FLAG,STRU.CROSS_SEGMENT_VALIDATION_FLAG';

Line: 1163


   l_select_clause         varchar2(4000);

Line: 1186


   l_options_tbl(l_idx).l_gen_select   := 'N';

Line: 1198


   l_options_tbl(l_idx).l_gen_select   := 'N';

Line: 1205


     ' AND (CO.PRORATE_CONVENTION_CODE IN (select distinct PRORATE_CONVENTION_CODE from fa_books '||
     ' where asset_id = '||g_asset_id||' and book_type_code = '||'''' || g_book || ''''||') '||
     ' OR CO.PRORATE_CONVENTION_CODE in (select distinct RETIREMENT_PRORATE_CONVENTION from fa_retirements '||
     ' where asset_id = '||g_asset_id|| ' and book_type_code = '||'''' || g_book || ''''||')) '||
     ' AND CO.START_DATE BETWEEN FY.START_DATE AND FY.END_DATE '||
	 ' AND CO.PRORATE_CONVENTION_CODE = CT.PRORATE_CONVENTION_CODE ORDER BY CT.PRORATE_CONVENTION_CODE, CO.START_DATE';

Line: 1213


   l_options_tbl(l_idx).l_gen_select   := 'N';

Line: 1217


     ' where BR.BONUS_RULE=BRL.BONUS_RULE and BR.BONUS_RULE IN (select distinct BONUS_RULE from fa_books '||
     ' where asset_id = '||g_asset_id||' and book_type_code = '||'''' || g_book || ''''||') '||
     ' order by BR.Bonus_Rule,BR.Start_Year';

Line: 1220


   l_options_tbl(l_idx).l_cnt_stmt     := 'select count(1) '||l_options_tbl(l_idx).l_add_clause;

Line: 1223


   l_options_tbl(l_idx).l_gen_select   := 'N';

Line: 1228


     ||' WHERE cl.ceiling_name = ct.ceiling_name AND (cl.ceiling_name in (select distinct Ceiling_Name '||
     ' from fa_books where asset_id = '||g_asset_id||' and book_type_code = '||'''' || g_book || ''''||') '
     ||' OR  cl.ceiling_name in (select Ceiling_Name from fa_category_book_defaults '||
     ' where category_id in (SELECT DISTINCT ah.category_id FROM fa_asset_history ah '||
     ' WHERE ah.asset_id = '||g_asset_id|| ') and book_type_code = '||'''' || g_book || ''''||'))';

Line: 1233


   l_options_tbl(l_idx).l_cnt_stmt     := 'select count(1) '||l_options_tbl(l_idx).l_add_clause;

Line: 1236


   l_options_tbl(l_idx).l_gen_select   := 'N';

Line: 1245


   l_options_tbl(l_idx).l_cnt_stmt     := 'select count(1) '||l_options_tbl(l_idx).l_add_clause;

Line: 1255


     ||' IN (select DEPRN_METHOD_CODE||to_char(nvl(LIFE_IN_MONTHS,99999)) from fa_books'
     ||' where book_type_code = '||'''' || g_book || ''''||' and asset_id = '||g_asset_id||')';

Line: 1259


   l_options_tbl(l_idx).l_add_clause   := ' FROM fa_deprn_basis_rules WHERE deprn_basis_rule_id IN (SELECT deprn_basis_rule_id'
     ||' FROM fa_methods WHERE METHOD_CODE||to_char(nvl(LIFE_IN_MONTHS,99999)) IN '
	 ||'(select DEPRN_METHOD_CODE||to_char(nvl(LIFE_IN_MONTHS,99999)) from fa_books where book_type_code = '
	 ||'''' || g_book || ''''||' and asset_id = '||g_asset_id||'))';

Line: 1263


   l_options_tbl(l_idx).l_cnt_stmt     := 'select count(1) '||l_options_tbl(l_idx).l_add_clause;

Line: 1267


     ||'(SELECT to_char(mt.method_id) || to_char(basic_rate) FROM FA_METHODS mt, fa_books bk '
     ||'where mt.method_code = bk.deprn_method_code and nvl(mt.life_in_months,0) = nvl(bk.life_in_months,0) '
     ||'and bk.book_type_code = '||'''' || g_book || ''''||' and bk.asset_id = '||g_asset_id||')';

Line: 1270


   l_options_tbl(l_idx).l_cnt_stmt     := 'select count(1) '||l_options_tbl(l_idx).l_add_clause;

Line: 1282


   l_options_tbl(l_idx).l_add_clause   := ' From fa_formulas Where method_id in (SELECT mt.method_id '
     ||'FROM FA_METHODS mt, fa_books bk where mt.method_code = bk.deprn_method_code '
	 ||'and nvl(mt.life_in_months,0) = nvl(bk.life_in_months,0) and bk.book_type_code = '||'''' || g_book || ''''
	 ||' and bk.asset_id = '||g_asset_id||')';

Line: 1286


   l_options_tbl(l_idx).l_cnt_stmt     := 'select count(1) '||l_options_tbl(l_idx).l_add_clause;

Line: 1311


    select ev.event_id, en.entity_code
    from fa_transaction_headers th, xla_transaction_entities en, xla_events ev
    where th.book_type_code = g_book
    and th.asset_id         = g_asset_id
    and en.application_id   = l_appid
    and en.ledger_id        = g_sob_id
    and en.entity_code      = 'TRANSACTIONS'
    and nvl(en.source_id_int_1, (-99)) = th.transaction_header_id
    and ev.application_id              = l_appid
    and ev.entity_id                   = en.entity_id
    union
    select ev.event_id, en.entity_code
    from fa_transaction_headers th, xla_transaction_entities en, xla_events ev
    where th.book_type_code   = g_book
    and th.asset_id           = g_asset_id
    and en.application_id     = l_appid
    and en.ledger_id          = g_sob_id
    and en.entity_code        = 'INTER_ASSET_TRANSACTIONS'
    and nvl(en.source_id_int_1, (-99)) = th.trx_reference_id
    and ev.application_id              = l_appid
    and ev.entity_id                   = en.entity_id
    union
    select ev.event_id, en.entity_code
    from xla_transaction_entities en, xla_events ev, fa_book_controls bc
    where bc.book_type_code   = g_book
    and en.application_id     = l_appid
    and en.ledger_id          = g_sob_id
    and en.entity_code = 'DEPRECIATION'
    and nvl(en.source_id_int_1, (-99)) = g_asset_id
    and nvl(en.source_id_char_1, '') = bc.book_type_code
    and ev.application_id = l_appid
    and ev.entity_id                  = en.entity_id;

Line: 1402


     l_options_tbl(l_idx).l_gen_select   := 'Y';

Line: 1409


     l_options_tbl(l_idx).l_cnt_stmt     := 'Select count(1) '||l_options_tbl(l_idx).l_add_clause;

Line: 1413


     l_options_tbl(l_idx).l_gen_select   := 'Y';

Line: 1420


           ||' and entity_id in (select entity_id from xla_events where event_id '||l_event_list||')';

Line: 1421


     l_options_tbl(l_idx).l_cnt_stmt     := 'Select count(1) '||l_options_tbl(l_idx).l_add_clause;

Line: 1425


     l_options_tbl(l_idx).l_gen_select   := 'Y';

Line: 1431


     l_options_tbl(l_idx).l_cnt_stmt     := 'Select count(1) '||l_options_tbl(l_idx).l_add_clause;

Line: 1434


     l_options_tbl(l_idx).l_cnt_stmt     := 'Select count(1) '||l_options_tbl(l_idx).l_add_clause;

Line: 1438


     l_options_tbl(l_idx).l_gen_select   := 'Y';

Line: 1445


           ||' and ae_header_id in (select ae_header_id from xla_ae_headers where application_id = '||l_appid||' and event_id '||l_event_list||')';

Line: 1446


     l_options_tbl(l_idx).l_cnt_stmt     := 'Select count(1) '||l_options_tbl(l_idx).l_add_clause;

Line: 1450


     l_options_tbl(l_idx).l_gen_select   := 'Y';

Line: 1456


     l_options_tbl(l_idx).l_cnt_stmt     := 'Select count(1) '||l_options_tbl(l_idx).l_add_clause;

Line: 1460


     l_options_tbl(l_idx).l_gen_select   := 'Y';

Line: 1466


        '(select SLA_ACCOUNTING_METHOD_CODE from gl_ledgers where ledger_id = '||g_sob_id||')';

Line: 1467


     l_options_tbl(l_idx).l_cnt_stmt     := 'Select count(1) '||l_options_tbl(l_idx).l_add_clause;

Line: 1471


     l_options_tbl(l_idx).l_gen_select   := 'Y';

Line: 1477


        '(select distinct product_rule_code from xla_ae_headers where application_id = '||l_appid||' and event_id '||l_event_list||')';

Line: 1478


     l_options_tbl(l_idx).l_cnt_stmt     := 'Select count(1) '||l_options_tbl(l_idx).l_add_clause;

Line: 1482


     l_options_tbl(l_idx).l_gen_select   := 'Y';

Line: 1487


     l_options_tbl(l_idx).l_add_clause   := ' from xla_ledger_options where (ledger_id = '||g_sob_id||' or ledger_id in (select target_ledger_id '||
        ' FROM gl_ledger_relationships WHERE primary_ledger_id = '||g_sob_id||' AND relationship_type_code <> ''NONE'' '||
        ' AND application_id IN (101,140) AND relationship_enabled_flag = ''Y'')) and application_id IN (101,140) order by APPLICATION_ID, LEDGER_ID';

Line: 1490


     l_options_tbl(l_idx).l_cnt_stmt     := 'Select count(1) '||l_options_tbl(l_idx).l_add_clause;

Line: 1494


     l_options_tbl(l_idx).l_gen_select   := 'N';

Line: 1501


     l_options_tbl(l_idx).l_add_clause   := ' from gl_ledgers where ledger_id = '||g_sob_id||' or ledger_id in (select target_ledger_id '||
        ' FROM gl_ledger_relationships WHERE primary_ledger_id = '||g_sob_id||' AND relationship_type_code <> ''NONE'' '||
        ' AND application_id IN (101,140) AND relationship_enabled_flag = ''Y'') order by ledger_id';

Line: 1504


     l_options_tbl(l_idx).l_cnt_stmt     := 'Select count(1) '||l_options_tbl(l_idx).l_add_clause;

Line: 1507


     l_idx:= l_idx+ 1;   l_col_exclusions(l_idx).cValue  := 'LAST_UPDATE_LOGIN';

Line: 1743


    log('prt_opt_tbl',p_options_tbl(i).l_gen_select);