DBA Data[Home] [Help]

APPS.FA_MASS_RET_PKG SQL Statements

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

Line: 133

   G_last_updated_by    number := FND_GLOBAL.USER_ID;
Line: 134

   G_last_update_login  number := FND_GLOBAL.CONC_LOGIN_ID;
Line: 254

   SELECT fdh.assigned_to,
          fdh.location_id,
          gcc.segment1,           gcc.segment2,
          gcc.segment3,           gcc.segment4,
          gcc.segment5,           gcc.segment6,
          gcc.segment7,           gcc.segment8,
          gcc.segment9,           gcc.segment10,
          gcc.segment11,          gcc.segment12,
          gcc.segment13,          gcc.segment14,
          gcc.segment15,          gcc.segment16,
          gcc.segment17,          gcc.segment18,
          gcc.segment19,          gcc.segment20,
          gcc.segment21,          gcc.segment22,
          gcc.segment23,          gcc.segment24,
          gcc.segment25,          gcc.segment26,
          gcc.segment27,          gcc.segment28,
          gcc.segment29,          gcc.segment30
     FROM fa_distribution_history fdh,
          gl_code_combinations    gcc
    WHERE fdh.asset_id             = X_Asset_Id
      AND fdh.code_combination_id  = gcc.code_combination_id
      AND fdh.date_ineffective    IS NULL;
Line: 398

FUNCTION Insert_details( p_asset_id                     IN NUMBER,
                         p_units_assigned               IN NUMBER,
                         p_code_combination_id          IN NUMBER,
                         p_location_id                  IN NUMBER,
                         p_assigned_to                  IN NUMBER,
                         p_cost                         IN NUMBER,
                         p_current_units                IN NUMBER)
                        RETURN BOOLEAN IS

  error         varchar2(100);
Line: 410

  insert into fa_mass_ext_retirements
             (batch_name,
              mass_external_retire_id,
              book_type_code,
              review_status,
              asset_id,
              calc_gain_loss_flag,
              created_by,
              creation_date,
              last_updated_by,
              last_update_date,
              last_update_login,
              cost_retired,
              cost_of_removal,
              proceeds_of_sale,
              retirement_type_code,
              date_retired,
              transaction_name,
              units,
              code_combination_id,
              location_id,
              assigned_to
              )
              VALUES
              (
              g_batch_name,
              fa_mass_ext_retirements_s.nextval,
              g_book_type_code,
              'POST',
              p_asset_id,
              'YES', -- calc_gain_loss_flag
              g_last_updated_by,
              sysdate,
              g_last_updated_by,
              sysdate,
              g_last_update_login,
              ((p_units_assigned / p_current_units) * p_cost),
              0,
              0,
              G_retirement_type_code,
              G_retirement_date,
              G_transaction_name,
              p_units_assigned,
              p_code_combination_id,
              p_location_id,
              p_assigned_to);
Line: 464

      'Insert_Details',
        error,
        '', p_log_level_rec => g_log_level_rec);
Line: 469

END Insert_details;
Line: 490

    SELECT fad.code_combination_id,
           fad.location_id,
           fad.assigned_to,
           fad.units_assigned
    FROM fa_distribution_history fad,
         gl_code_combinations    gcc
    WHERE fad.asset_id = x_asset_id
    AND fad.date_ineffective IS NULL
    AND fad.code_combination_id = gcc.code_combination_id
    AND (fad.assigned_to = G_Employee_Id
                          OR G_Employee_Id IS NULL)
    AND (fad.location_id = G_Location_Id
                           OR G_Location_Id IS NULL)
    AND ((gcc.segment1 BETWEEN G_Segment1_Low
                          AND G_Segment1_High
                          OR G_Segment1_Low IS NULL)
    AND (gcc.segment2 BETWEEN G_Segment2_Low
                          AND G_Segment2_High
                          OR G_Segment2_Low IS NULL)
    AND (gcc.segment3 BETWEEN G_Segment3_Low
                          AND G_Segment3_High
                          OR G_Segment3_Low IS NULL)
    AND (gcc.segment4 BETWEEN G_Segment4_Low
                          AND G_Segment4_High
                          OR G_Segment4_Low IS NULL)
    AND (gcc.segment5 BETWEEN G_Segment5_Low
                          AND G_Segment5_High
                          OR G_Segment5_Low IS NULL)
    AND (gcc.segment6 BETWEEN G_Segment6_Low
                          AND G_Segment6_High
                          OR G_Segment6_Low IS NULL)
    AND (gcc.segment7 BETWEEN G_Segment7_Low
                          AND G_Segment7_High
                          OR G_Segment7_Low IS NULL)
    AND (gcc.segment8 BETWEEN G_Segment8_Low
                          AND G_Segment8_High
                          OR G_Segment8_Low IS NULL)
    AND (gcc.segment9 BETWEEN G_Segment9_Low
                          AND G_Segment9_High
                          OR G_Segment9_Low IS NULL)
    AND (gcc.segment10 BETWEEN G_Segment10_Low
                           AND G_Segment10_High
                           OR G_Segment10_Low IS NULL)
    AND (gcc.segment11 BETWEEN G_Segment11_Low
                           AND G_Segment11_High
                           OR G_Segment11_Low IS NULL)
    AND (gcc.segment12 BETWEEN G_Segment12_Low
                           AND G_Segment12_High
                           OR G_Segment12_Low IS NULL)
    AND (gcc.segment13 BETWEEN G_Segment13_Low
                           AND G_Segment13_High
                         OR G_Segment13_Low IS NULL)
    AND (gcc.segment14 BETWEEN G_Segment14_Low
                           AND G_Segment14_High
                           OR G_Segment14_Low IS NULL)
    AND (gcc.segment15 BETWEEN G_Segment15_Low
                           AND G_Segment15_High
                           OR G_Segment15_Low IS NULL)
    AND (gcc.segment16 BETWEEN G_Segment16_Low
                           AND G_Segment16_High
                           OR G_Segment16_Low IS NULL)
    AND (gcc.segment17 BETWEEN G_Segment17_Low
                           AND G_Segment17_High
                          OR G_Segment17_Low IS NULL)
    AND (gcc.segment18 BETWEEN G_Segment18_Low
                           AND G_Segment18_High
                          OR G_Segment18_Low IS NULL)
    AND (gcc.segment19 BETWEEN G_Segment19_Low
                           AND G_Segment19_High
                           OR G_segment19_Low IS NULL)
    AND (gcc.segment20 BETWEEN G_Segment20_Low
                           AND G_Segment20_High
                           OR G_segment20_Low IS NULL)
    AND (gcc.segment21 BETWEEN G_Segment21_Low
                           AND G_Segment21_High
                           OR G_segment21_Low IS NULL)
    AND (gcc.segment22 BETWEEN G_Segment22_Low
                           AND G_Segment22_High
                           OR G_segment22_Low IS NULL)
    AND (gcc.segment23 BETWEEN G_Segment23_Low
                           AND G_Segment23_High
                           OR G_segment23_Low IS NULL)
    AND (gcc.segment24 BETWEEN G_Segment24_Low
                           AND G_Segment24_High
                           OR G_segment24_Low IS NULL)
    AND (gcc.segment25 BETWEEN G_Segment25_Low
                           AND G_Segment25_High
                           OR G_segment25_Low IS NULL)
    AND (gcc.segment26 BETWEEN G_Segment26_Low
                           AND G_Segment26_High
                           OR G_segment26_Low IS NULL)
    AND (gcc.segment27 BETWEEN G_Segment27_Low
                           AND G_Segment27_High
                           OR G_segment27_Low IS NULL)
    AND (gcc.segment28 BETWEEN G_Segment28_Low
                           AND G_Segment28_High
                           OR G_segment28_Low IS NULL)
    And (gcc.segment29 BETWEEN G_Segment29_Low
                           AND G_Segment29_High
                           OR G_segment29_Low IS NULL)
    AND (gcc.segment30 BETWEEN G_Segment30_Low
                           AND G_Segment30_High
                           OR G_segment30_Low IS NULL)
    );
Line: 811

   if (p_message = 'FA_SHARED_INSERT_DEBUG') then
      fnd_message.set_token('TABLE', 'retirement batch');
Line: 826

   if p_message <> 'FA_SHARED_INSERT_DEBUG' then
     if p_message = 'FA_MASSRET_NOT_ENOUGH_UNITS' then
        fa_srvr_msg.add_message
          (calling_fn => l_calling_fn,
           name       => p_message,
           token1     => 'UNITS',
           value1     => p_token1,
           token2     => 'TOTALUNITS',
           value2     => p_token2 , p_log_level_rec => g_log_level_rec);
Line: 859

              select 'FA_SHARED_PENDING_RETIREMENT'
              from   fa_retirements frt
              where  frt.asset_id = p_Asset_Id
                     AND frt.book_type_code = G_Book_Type_Code
                     AND frt.status IN  ('PENDING','REINSTATE');
Line: 866

              select 'FA_REC_RETIRED'
              from   fa_retirements frt,
                     fa_books bk
              where  frt.asset_id = p_Asset_Id
                     AND bk.asset_id = frt.asset_id
                     AND bk.period_counter_fully_retired is NOT NULL
                     AND bk.transaction_header_id_in =
                                         frt.transaction_header_id_in
                     AND bk.date_ineffective is null
                     AND frt.transaction_header_id_out is NULL
                     AND frt.status = 'PROCESSED'
                     AND frt.book_type_code = G_Book_Type_Code
                     AND bk.book_type_code = frt.book_type_code;
Line: 881

               select 'FA_RET_CANT_RET_NONDEPRN'
               from   fa_transaction_headers th,
                      fa_book_controls bc,
                      fa_deprn_periods dp
               where  th.asset_id = p_Asset_id
                          AND th.book_type_code = G_Book_Type_Code
                          AND bc.book_type_code = th.book_type_code
                          AND th.transaction_type_code||''
                          = decode(bc.book_class,'CORPORATE','TRANSFER IN',
                                                               'ADDITION')
                          AND th.date_effective
                              BETWEEN dp.period_open_date
                                  AND nvl(dp.period_close_date,sysdate)
                          AND dp.book_type_code = th.book_type_code
                          AND dp.period_close_date is NULL;
Line: 897

               select 'FA_SHARED_OTHER_TRX'
               from   fa_transaction_headers fth
               where  fth.asset_id = p_Asset_id
               and    fth.book_type_code = G_Book_Type_Code
               and    (fth.transaction_date_entered > G_Retirement_Date
               and     fth.transaction_type_code in ('TAX', 'REVALUATION'));
Line: 968

           l_check := 'Not inserted';
Line: 971

                   l_check := 'Already inserted';
Line: 974

           if l_check <> 'Already inserted' then
                p_num_msg := p_num_msg + 1;
Line: 996

   select 'EXTENDED LIFE'
     from fa_books bk,
          fa_deprn_periods dp
    where bk.asset_id                      = X_Asset_Id
      AND bk.book_type_code                = G_Book_Type_Code
      AND nvl(period_Counter_fully_reserved,99) <> bk.period_counter_life_complete
      AND dp.book_type_code                = bk.book_type_code
      AND bk.period_counter_life_complete is not NULL
      AND bk.date_ineffective             is null
      AND dp.period_close_date            is null
      AND G_Retirement_Date               < dp.calendar_period_open_date;
Line: 1077

  Select  ad.asset_id,
          ad.asset_number,
          ad.current_units,
          bk.cost,
          mer.units mer_units,
          mer.mass_external_retire_id,
          mer.code_combination_id,
          mer.location_id,
          mer.assigned_to
  From  fa_mass_ext_retirements mer,
        fa_books bk,
        fa_additions ad
  Where  mer.batch_name = G_batch_name   -- current batch
  And    mer.asset_id = bk.asset_id
  And    mer.book_type_code = bk.book_type_code
  And    bk.date_ineffective is null
  And    bk.date_placed_in_service
                between nvl(temp_from_dpis, bk.date_placed_in_service -1)
                and nvl(temp_to_dpis, bk.date_placed_in_service +1)
  and    ad.asset_id = mer.asset_id
  order by bk.date_placed_in_service, bk.asset_id;
Line: 1122

    SELECT
           faa.asset_id,
           faa.asset_number,
           fab.date_placed_in_service,
           fab.cost,
           faa.current_units
      FROM fa_book_controls fbc,
           fa_books         fab,
           fa_additions_b   faa
     WHERE faa.asset_id = fab.asset_id
       AND (faa.asset_key_ccid = G_Asset_Key_Id
             OR G_Asset_Key_Id IS NULL)
       AND faa.asset_category_id = nvl(G_Category_Id,faa.asset_category_id)
       AND fab.cost >= nvl(G_From_Cost,fab.cost)
       AND fab.cost <= nvl(G_To_Cost,fab.cost)
       AND ((G_group_asset_id = -1 and -- group change
             fab.group_asset_id is null) OR -- group change
            (G_group_asset_id = -99) OR -- group change
            (G_group_asset_id > 0 and -- group change
             nvl(fab.group_asset_id, -999) = g_group_asset_id)) -- group change
       AND nvl(fab.period_counter_fully_reserved,-99999) =
          decode(G_Fully_Rsvd_Flag,
               'YES',fab.period_counter_fully_reserved,
               'NO',-99999,
               nvl(fab.period_counter_fully_reserved,-99999))
       AND faa.asset_number                >=
           nvl(G_From_Asset_Number, faa.asset_number)
       AND faa.asset_number                <=
           nvl(G_To_Asset_Number,  faa.asset_number)
       AND fab.date_placed_in_service
             NOT BETWEEN nvl(Temp_From_DPIS,fab.date_placed_in_service-1)
                  AND nvl(Temp_To_DPIS  ,fab.date_placed_in_service+1)
       AND (faa.model_number = G_model_number
                OR G_model_number IS NULL)
       AND (faa.serial_number  = G_serial_number
                OR G_serial_number IS NULL)
       AND (faa.tag_number      = G_tag_number
                OR G_tag_number IS NULL)
       AND (faa.manufacturer_name = G_manufacturer_name
                OR G_manufacturer_name IS NULL)
       AND fab.book_type_code = fbc.book_type_code
       AND fbc.date_ineffective is null
       AND EXISTS (SELECT null
                     FROM fa_distribution_history fad,
                          gl_code_combinations    gcc
                    WHERE fad.asset_id = faa.asset_id
                      AND fad.code_combination_id = gcc.code_combination_id
                      AND (fad.assigned_to = G_Employee_Id
                           OR G_Employee_Id IS NULL)
                      AND (fad.location_id = G_Location_Id
                           OR G_Location_Id IS NULL)
                      AND fad.date_ineffective IS NULL
                      AND (gcc.segment1 BETWEEN G_Segment1_Low
                                            AND G_Segment1_High
                           OR G_Segment1_Low IS NULL)
                      AND (gcc.segment2 BETWEEN G_Segment2_Low
                                            AND G_Segment2_High
                           OR G_Segment2_Low IS NULL)
                      AND (gcc.segment3 BETWEEN G_Segment3_Low
                                            AND G_Segment3_High
                           OR G_Segment3_Low IS NULL)
                      AND (gcc.segment4 BETWEEN G_Segment4_Low
                                            AND G_Segment4_High
                           OR G_Segment4_Low IS NULL)
                      AND (gcc.segment5 BETWEEN G_Segment5_Low
                                            AND G_Segment5_High
                           OR G_Segment5_Low IS NULL)
                      AND (gcc.segment6 BETWEEN G_Segment6_Low
                                            AND G_Segment6_High
                           OR G_Segment6_Low IS NULL)
                      AND (gcc.segment7 BETWEEN G_Segment7_Low
                                            AND G_Segment7_High
                           OR G_Segment7_Low IS NULL)
                      AND (gcc.segment8 BETWEEN G_Segment8_Low
                                            AND G_Segment8_High
                           OR G_Segment8_Low IS NULL)
                      AND (gcc.segment9 BETWEEN G_Segment9_Low
                                            AND G_Segment9_High
                           OR G_Segment9_Low IS NULL)
                      AND (gcc.segment10 BETWEEN G_Segment10_Low
                                             AND G_Segment10_High
                           OR G_Segment10_Low IS NULL)
                      AND (gcc.segment11 BETWEEN G_Segment11_Low
                                             AND G_Segment11_High
                           OR G_Segment11_Low IS NULL)
                      AND (gcc.segment12 BETWEEN G_Segment12_Low
                                             AND G_Segment12_High
                           OR G_Segment12_Low IS NULL)
                      AND (gcc.segment13 BETWEEN G_Segment13_Low
                                             AND G_Segment13_High
                           OR G_Segment13_Low IS NULL)
                      AND (gcc.segment14 BETWEEN G_Segment14_Low
                                             AND G_Segment14_High
                           OR G_Segment14_Low IS NULL)
                      AND (gcc.segment15 BETWEEN G_Segment15_Low
                                             AND G_Segment15_High
                           OR G_Segment15_Low IS NULL)
                      AND (gcc.segment16 BETWEEN G_Segment16_Low
                                             AND G_Segment16_High
                           OR G_Segment16_Low IS NULL)
                      AND (gcc.segment17 BETWEEN G_Segment17_Low
                                             AND G_Segment17_High
                           OR G_Segment17_Low IS NULL)
                      AND (gcc.segment18 BETWEEN G_Segment18_Low
                                             AND G_Segment18_High
                           OR G_Segment18_Low IS NULL)
                      AND (gcc.segment19 BETWEEN G_Segment19_Low
                                             AND G_Segment19_High
                           OR G_segment19_Low IS NULL)
                      AND (gcc.segment20 BETWEEN G_Segment20_Low
                                             AND G_Segment20_High
                           OR G_segment20_Low IS NULL)
                      AND (gcc.segment21 BETWEEN G_Segment21_Low
                                             AND G_Segment21_High
                           OR G_segment21_Low IS NULL)
                      AND (gcc.segment22 BETWEEN G_Segment22_Low
                                             AND G_Segment22_High
                           OR G_segment22_Low IS NULL)
                      AND (gcc.segment23 BETWEEN G_Segment23_Low
                                             AND G_Segment23_High
                           OR G_segment23_Low IS NULL)
                      AND (gcc.segment24 BETWEEN G_Segment24_Low
                                             AND G_Segment24_High
                           OR G_segment24_Low IS NULL)
                      AND (gcc.segment25 BETWEEN G_Segment25_Low
                                             AND G_Segment25_High
                           OR G_segment25_Low IS NULL)
                      AND (gcc.segment26 BETWEEN G_Segment26_Low
                                             AND G_Segment26_High
                           OR G_segment26_Low IS NULL)
                      AND (gcc.segment27 BETWEEN G_Segment27_Low
                                             AND G_Segment27_High
                           OR G_segment27_Low IS NULL)
                      AND (gcc.segment28 BETWEEN G_Segment28_Low
                                             AND G_Segment28_High
                           OR G_segment28_Low IS NULL)
                      And (gcc.segment29 BETWEEN G_Segment29_Low
                                             AND G_Segment29_High
                           OR G_segment29_Low IS NULL)
                      AND (gcc.segment30 BETWEEN G_Segment30_Low
                                             AND G_Segment30_High
                           OR G_segment30_Low IS NULL))
       AND (faa.asset_type = G_Asset_Type OR G_Asset_Type IS NULL)
       AND faa.asset_type IN ('CIP','CAPITALIZED','EXPENSED')
       AND fbc.book_type_code = G_Book_Type_Code
       AND fab.date_ineffective IS NULL
     ORDER BY fab.date_placed_in_service;
Line: 1272

  select units
  from fa_mass_ext_retirements
  where book_type_code = G_book_type_code
  and     asset_id  = l_asset_id
  and     review_status = 'POST'
  and     units is not null
  and     batch_name <> G_batch_name;
Line: 1282

  select *
  from fa_distribution_history dh
  where dh.asset_id = l_asset_id
  and dh.location_id = nvl(aurec.location_id, dh.location_id)
  and dh.code_combination_id = nvl(aurec.code_combination_id, dh.code_combination_id)
  and nvl(dh.assigned_to, -9999) = nvl(aurec.assigned_to, -9999)
  and dh.date_ineffective is null
  order by distribution_id;
Line: 1324

  msg_tbl.delete;
Line: 1371

           Update fa_mass_ext_retirements
           Set review_status = 'POST',
               calc_gain_loss_flag = 'YES'
           Where mass_external_retire_id = aurec.mass_external_retire_id;
Line: 1387

           select nvl(sum(units),0)
           into l_asset_post_units
           from fa_mass_ext_retirements
           where book_type_code = g_book_type_code
           and   asset_id = l_asset_id
           and   review_status = 'POST'
           and   batch_name <> g_batch_name;
Line: 1419

                    select nvl(sum(units),0)
                    into l_dist_post_units
                    from fa_mass_ext_retirements
                    where book_type_code = g_book_type_code
                    and   asset_id = l_asset_id
                    and   review_status = 'POST'
                    and   batch_name <> g_batch_name
                    and   code_combination_id= dhrec.code_combination_id
                    and   location_id = dhrec.location_id
                    and   nvl(assigned_to,-99) = nvl(dhrec.assigned_to,-99);
Line: 1434

                           if not insert_details(
                                aurec.asset_id,
                                l_dist_diff,
                                dhrec.code_combination_id,
                                dhrec.location_id,
                                dhrec.assigned_to,
                                aurec.cost,
                                aurec.current_units) then

                                raise dml_error;
Line: 1447

                             fa_debug_pkg.add(l_calling_fn,'insert details A',
                                l_dist_diff, p_log_level_rec => g_log_level_rec);
Line: 1490

                             if not insert_details(
                                        aurec.asset_id,
                                        l_dist_diff,
                                        dhrec.code_combination_id,
                                        dhrec.location_id,
                                        dhrec.assigned_to,
                                        aurec.cost,
                                        aurec.current_units) then
                                raise dml_error;
Line: 1501

                               fa_debug_pkg.add(l_calling_fn,'insert details B',
                                      l_dist_diff, p_log_level_rec => g_log_level_rec);
Line: 1543

                 select nvl(sum(units),0)
                 into l_dist_post_units
                 from fa_mass_ext_retirements
                 where book_type_code = g_book_type_code
                 and   asset_id = l_asset_id
                 and   review_status = 'POST'
                 and   batch_name <> g_batch_name
                 and   code_combination_id =
                        aurec.code_combination_id
                 and   location_id =
                        aurec.location_id
                 and   nvl(assigned_to,-99) =
                        nvl(aurec.assigned_to,-99);
Line: 1568

                       if not insert_details(
                          aurec.asset_id,
                          l_remaining_units,
                          aurec.code_combination_id,
                          aurec.location_id,
                          aurec.assigned_to,
                          aurec.cost,
                          aurec.current_units) then
                                raise dml_error;
Line: 1585

                        if not insert_details(
                          aurec.asset_id,
                          aurec.mer_units - l_dist_post_units,
                          aurec.code_combination_id,
                          aurec.location_id,
                          aurec.assigned_to,
                          aurec.cost,
                          aurec.current_units) then
                                raise dml_error;
Line: 1598

                         fa_debug_pkg.add(l_calling_fn,'insert details C',
                                l_slask, p_log_level_rec => g_log_level_rec);
Line: 1643

                        'Before Insert details B, units_assigned',
                        dhrec.units_assigned, p_log_level_rec => g_log_level_rec);
Line: 1648

                  if not insert_details(
                        aurec.asset_id,
                        l_remaining_units,
                        dhrec.code_combination_id,
                        dhrec.location_id,
                        dhrec.assigned_to,
                        aurec.cost,
                        aurec.current_units) then

                       raise dml_error;
Line: 1661

                            fa_debug_pkg.add(l_calling_fn,'insert details D',
                                l_remaining_units, p_log_level_rec => g_log_level_rec);
Line: 1679

                         'Before Insert details C, units_assigned',
                          dhrec.units_assigned, p_log_level_rec => g_log_level_rec);
Line: 1683

                   if not insert_details(
                        aurec.asset_id,
                        dhrec.units_assigned,
                        dhrec.code_combination_id,
                        dhrec.location_id,
                        dhrec.assigned_to,
                        aurec.cost,
                        aurec.current_units) then

                         raise dml_error;
Line: 1697

                         fa_debug_pkg.add(l_calling_fn,'insert details E',
                                dhrec.units_assigned, p_log_level_rec => g_log_level_rec);
Line: 1725

             select nvl(sum(units),0)
             into l_asset_post_units
             from fa_mass_ext_retirements
             where book_type_code = g_book_type_code
             and   asset_id = l_asset_id
             and   review_status = 'POST'
             and   batch_name <> g_batch_name;
Line: 1756

                      select nvl(sum(units),0)
                      into l_dist_post_units
                      from fa_mass_ext_retirements
                      where book_type_code = g_book_type_code
                      and   asset_id = l_asset_id
                      and   review_status = 'POST'
                      and   batch_name <> g_batch_name
                      and   code_combination_id= dhrec.code_combination_id
                      and   location_id = dhrec.location_id
                      and   nvl(assigned_to,-99) = nvl(dhrec.assigned_to,-99);
Line: 1785

                              if not insert_details(
                                aurec.asset_id,
                                l_dist_diff,
                                dhrec.code_combination_id,
                                dhrec.location_id,
                                dhrec.assigned_to,
                                aurec.cost,
                                aurec.current_units) then

                                        raise dml_error;
Line: 1797

                                 fa_debug_pkg.add(l_calling_fn,'insert details F',
                                    l_dist_diff, p_log_level_rec => g_log_level_rec);
Line: 1808

                              if not insert_details(
                                aurec.asset_id,
                                l_remaining_units,
                                dhrec.code_combination_id,
                                dhrec.location_id,
                                dhrec.assigned_to,
                                aurec.cost,
                                aurec.current_units) then

                                        raise dml_error;
Line: 1820

                                 fa_debug_pkg.add(l_calling_fn,'insert details FF',
                                    l_remaining_units, p_log_level_rec => g_log_level_rec);
Line: 1867

                            if not insert_details(
                                        aurec.asset_id,
                                        l_dist_diff,
                                        dhrec.code_combination_id,
                                        dhrec.location_id,
                                        dhrec.assigned_to,
                                        aurec.cost,
                                        aurec.current_units) then

                                         raise dml_error;
Line: 1879

                            fa_debug_pkg.add(l_calling_fn,'insert details G',
                                l_dist_diff, p_log_level_rec => g_log_level_rec);
Line: 1890

                              if not insert_details(
                                aurec.asset_id,
                                l_remaining_units,
                                dhrec.code_combination_id,
                                dhrec.location_id,
                                dhrec.assigned_to,
                                aurec.cost,
                                aurec.current_units) then

                                        raise dml_error;
Line: 1902

                                 fa_debug_pkg.add(l_calling_fn,'insert details GG',
                                    l_remaining_units, p_log_level_rec => g_log_level_rec);
Line: 1944

                 select nvl(sum(units),0)
                 into l_dist_post_units
                 from fa_mass_ext_retirements
                 where book_type_code = g_book_type_code
                 and   asset_id = l_asset_id
                 and   review_status = 'POST'
                 and   batch_name <> g_batch_name
                 and   code_combination_id =
                        nvl(aurec.code_combination_id,code_combination_id)
                 and   location_id =
                        nvl(aurec.location_id, location_id)
                 and   nvl(assigned_to,-99) =
                        nvl(aurec.assigned_to,-99);
Line: 1962

                       if not insert_details(
                          aurec.asset_id,
                          l_remaining_units,
                          aurec.code_combination_id,
                          aurec.location_id,
                          aurec.assigned_to,
                          aurec.cost,
                          aurec.current_units) then

                           raise dml_error;
Line: 1980

                       if not insert_details(
                          aurec.asset_id,
                          aurec.mer_units - l_dist_post_units,
                          aurec.code_combination_id,
                          aurec.location_id,
                          aurec.assigned_to,
                          aurec.cost,
                          aurec.current_units) then

                             raise dml_error;
Line: 1994

                          fa_debug_pkg.add(l_calling_fn,'insert details H',
                                l_slask, p_log_level_rec => g_log_level_rec);
Line: 2056

        l_asset_dist_tbl.delete;
Line: 2151

                      fa_debug_pkg.add(l_calling_fn, 'Before Insert details A, units',
                        l_asset_dist_tbl(l_dist_count).units_assigned);
Line: 2155

                     if not insert_details(
                        l_2nd_asset_id,
                        l_asset_dist_tbl(l_dist_count).units_assigned,
                        l_asset_dist_tbl(l_dist_count).expense_ccid,
                        l_asset_dist_tbl(l_dist_count).location_ccid,
                        l_asset_dist_tbl(l_dist_count).assigned_to,
                        l_2nd_cost_retired,
                        l_asset_dist_tbl(l_dist_count).units_assigned) then

                        raise dml_error;
Line: 2168

                            fa_debug_pkg.add(l_calling_fn,'insert details I',
                                l_asset_dist_tbl(l_dist_count).units_assigned);
Line: 2185

                      select nvl(sum(units),0)
                      into l_asset_post_units
                      from fa_mass_ext_retirements
                      where book_type_code = g_book_type_code
                      and   asset_id = l_asset_id
                      and   review_status = 'POST'
                      and   batch_name <> g_batch_name;
Line: 2224

                                  select nvl(sum(units),0)
                                  into l_dist_post_units
                                  from fa_mass_ext_retirements
                                  where book_type_code = g_book_type_code
                                  and   asset_id = l_asset_id
                                  and   review_status = 'POST'
                                  and   batch_name <> g_batch_name
                                  and   code_combination_id= dhrec.code_combination_id
                                  and   location_id = dhrec.location_id
                                  and   nvl(assigned_to,-99) = nvl(dhrec.assigned_to,-99);
Line: 2245

                                        if not insert_details(
                                                l_2nd_asset_id,
                                                l_dist_diff,
                                                dhrec.code_combination_id,
                                                dhrec.location_id,
                                                dhrec.assigned_to,
                                                l_2nd_cost_retired,
                                                l_2nd_current_units) then

                                                raise dml_error;
Line: 2258

                                            fa_debug_pkg.add(l_calling_fn,'insert details J',l_dist_diff, p_log_level_rec => g_log_level_rec);
Line: 2288

                                             if not insert_details(
                                                l_2nd_asset_id,
                                                l_dist_diff,
                                                dhrec.code_combination_id,
                                                dhrec.location_id,
                                                dhrec.assigned_to,
                                                l_2nd_cost_retired,
                                                l_2nd_current_units) then

                                                raise dml_error;
Line: 2301

                                                    fa_debug_pkg.add(l_calling_fn,'insert details K',l_dist_diff, p_log_level_rec => g_log_level_rec);
Line: 2326

                           select nvl(sum(units),0)
                           into l_dist_post_units
                           from fa_mass_ext_retirements
                           where book_type_code = g_book_type_code
                           and   asset_id = l_asset_id
                           and   review_status = 'POST'
                           and   batch_name <> g_batch_name
                           and   code_combination_id =
                                l_asset_dist_tbl(l_dist_count).expense_ccid
                           and   location_id =
                                l_asset_dist_tbl(l_dist_count).location_ccid
                           and   nvl(assigned_to,-99) =
                                nvl(l_asset_dist_tbl(l_dist_count).assigned_to,-99);
Line: 2343

                                  if not insert_details(
                                    l_2nd_asset_id,
                                    l_remaining_units,
                                    l_asset_dist_tbl(l_dist_count).expense_ccid,
                                    l_asset_dist_tbl(l_dist_count).location_ccid,
                                    l_asset_dist_tbl(l_dist_count).assigned_to,
                                    l_2nd_cost_retired,
                                    l_2nd_current_units) then

                                    raise dml_error;
Line: 2361

                              if not insert_details(
                                l_2nd_asset_id,
                                l_asset_dist_tbl(l_dist_count).units_assigned - l_dist_post_units,
                                l_asset_dist_tbl(l_dist_count).expense_ccid,
                                l_asset_dist_tbl(l_dist_count).location_ccid,
                                l_asset_dist_tbl(l_dist_count).assigned_to,
                                l_2nd_cost_retired,
                                l_2nd_current_units) then

                                raise dml_error;
Line: 2374

                                    fa_debug_pkg.add(l_calling_fn,'insert details KK',l_slask, p_log_level_rec => g_log_level_rec);
Line: 2419

                              fa_debug_pkg.add(l_calling_fn, 'Before Insert details B, units',
                                l_asset_dist_tbl(l_dist_count).units_assigned);
Line: 2425

                             if not insert_details(
                                l_2nd_asset_id,
                                l_remaining_units,
                                dhrec.code_combination_id,
                                dhrec.location_id,
                                dhrec.assigned_to,
                                l_2nd_cost_retired,
                                l_2nd_current_units) then

                               raise dml_error;
Line: 2438

                                                    fa_debug_pkg.add(l_calling_fn,'insert details L',l_remaining_units, p_log_level_rec => g_log_level_rec);
Line: 2456

                              fa_debug_pkg.add(l_calling_fn, 'Before Insert details C, units',
                                l_asset_dist_tbl(l_dist_count).units_assigned);
Line: 2460

                             if not insert_details(
                                l_2nd_asset_id,
                                dhrec.units_assigned,
                                dhrec.code_combination_id,
                                dhrec.location_id,
                                dhrec.assigned_to,
                                l_2nd_cost_retired,
                                l_2nd_current_units) then

                                raise dml_error;
Line: 2473

                                    fa_debug_pkg.add(l_calling_fn,'insert details M', dhrec.units_assigned, p_log_level_rec => g_log_level_rec);
Line: 2493

                        select nvl(sum(units),0)
                        into l_asset_post_units
                        from fa_mass_ext_retirements
                        where book_type_code = g_book_type_code
                        and   asset_id = l_asset_id
                        and   review_status = 'POST'
                        and   batch_name <> g_batch_name;
Line: 2512

                                   select nvl(sum(units),0)
                                   into l_dist_post_units
                                   from fa_mass_ext_retirements
                                   where book_type_code = g_book_type_code
                                   and   asset_id = l_asset_id
                                   and   review_status = 'POST'
                                   and   batch_name <> g_batch_name
                                   and   code_combination_id= dhrec.code_combination_id
                                   and   location_id = dhrec.location_id
                                   and   nvl(assigned_to,-99) = nvl(dhrec.assigned_to,-99);
Line: 2528

                                      if not insert_details(
                                        l_2nd_asset_id,
                                        l_dist_diff,
                                        dhrec.code_combination_id,
                                        dhrec.location_id,
                                        dhrec.assigned_to,
                                        l_2nd_cost_retired,
                                        l_2nd_current_units) then

                                                raise dml_error;
Line: 2541

                                                    fa_debug_pkg.add(l_calling_fn,'insert details M',l_dist_diff, p_log_level_rec => g_log_level_rec);
Line: 2551

                                             if not insert_details(
                                                l_2nd_asset_id,
                                                l_remaining_units,
                                                dhrec.code_combination_id,
                                                dhrec.location_id,
                                                dhrec.assigned_to,
                                                l_2nd_cost_retired,
                                                l_2nd_current_units) then

                                                raise dml_error;
Line: 2564

                                                    fa_debug_pkg.add(l_calling_fn,'insert details O',l_dist_diff, p_log_level_rec => g_log_level_rec);
Line: 2596

                                             if not insert_details(
                                                l_2nd_asset_id,
                                                l_dist_diff,
                                                dhrec.code_combination_id,
                                                dhrec.location_id,
                                                dhrec.assigned_to,
                                                l_2nd_cost_retired,
                                                l_2nd_current_units) then

                                                raise dml_error;
Line: 2609

                                                    fa_debug_pkg.add(l_calling_fn,'insert details O',l_dist_diff, p_log_level_rec => g_log_level_rec);
Line: 2622

                                             if not insert_details(
                                                l_2nd_asset_id,
                                                l_remaining_units,
                                                dhrec.code_combination_id,
                                                dhrec.location_id,
                                                dhrec.assigned_to,
                                                l_2nd_cost_retired,
                                                l_2nd_current_units) then

                                                raise dml_error;
Line: 2635

                                                    fa_debug_pkg.add(l_calling_fn,'insert details O',l_dist_diff, p_log_level_rec => g_log_level_rec);
Line: 2661

                           select nvl(sum(units),0)
                           into l_dist_post_units
                           from fa_mass_ext_retirements
                           where book_type_code = g_book_type_code
                           and   asset_id = l_asset_id
                           and   review_status = 'POST'
                           and   batch_name <> g_batch_name
                           and   code_combination_id =
                                l_asset_dist_tbl(l_dist_count).expense_ccid
                           and   location_id =
                                l_asset_dist_tbl(l_dist_count).location_ccid
                           and   nvl(assigned_to,-99) =
                                nvl(l_asset_dist_tbl(l_dist_count).assigned_to,-99);
Line: 2680

                                  if not insert_details(
                                    l_2nd_asset_id,
                                    l_remaining_units,
                                    l_asset_dist_tbl(l_dist_count).expense_ccid,
                                    l_asset_dist_tbl(l_dist_count).location_ccid,
                                    l_asset_dist_tbl(l_dist_count).assigned_to,
                                    l_2nd_cost_retired,
                                    l_2nd_current_units) then

                                        raise dml_error;
Line: 2697

                                   if not insert_details(
                                     l_2nd_asset_id,
                                     l_asset_dist_tbl(l_dist_count).units_assigned - l_dist_post_units,
                                     l_asset_dist_tbl(l_dist_count).expense_ccid,
                                     l_asset_dist_tbl(l_dist_count).location_ccid,
                                     l_asset_dist_tbl(l_dist_count).assigned_to,
                                     l_2nd_cost_retired,
                                     l_2nd_current_units) then

                                        raise dml_error;
Line: 2712

                                        fa_debug_pkg.add(l_calling_fn,'insert details P',l_slask, p_log_level_rec => g_log_level_rec);
Line: 2762

           fa_debug_pkg.add(l_calling_fn, 'Update status',l_remaining_units , p_log_level_rec => g_log_level_rec);
Line: 2765

        Delete from fa_mass_ext_retirements
        Where batch_name = g_batch_name;
Line: 2768

        Update fa_mass_retirements
        Set status = 'ON_HOLD'
        Where mass_retirement_id = G_Mass_Retirement_id;
Line: 2775

        msg_tbl.delete;
Line: 2790

           fa_debug_pkg.add(l_calling_fn, 'Update status <= 0 ',l_remaining_units , p_log_level_rec => g_log_level_rec);
Line: 2796

        Delete from fa_mass_ext_retirements
        Where batch_name = g_batch_name
        And review_status = 'DELETE';
Line: 2804

             Write_Message(msg_tbl(i).asset_number,'FA_SHARED_INSERT_DEBUG',
                        '','');
Line: 2809

        Update fa_mass_retirements
        Set status = 'CREATED_RET'
        Where mass_retirement_id = G_Mass_Retirement_id;
Line: 2915

    SELECT fmr.mass_retirement_id,
           fmr.book_type_code,
           fmr.retirement_date,
           substrb(fmr.description, 1, 30),
           fmr.retire_subcomponents_flag,
           fmr.status,
           nvl(fmr.proceeds_of_sale,0),
           nvl(fmr.cost_of_removal,0) ,
           fmr.retirement_type_code,
           fmr.asset_type,
           fmr.location_id,
           fmr.employee_id,
           fmr.category_id,
           fmr.asset_key_id,
           fmr.from_asset_number,
           fmr.to_asset_number,
           fmr.from_date_placed_in_service,
           fmr.to_date_placed_in_service,
           fmr.model_number,
           fmr.serial_number,
           fmr.tag_number,
           fmr.manufacturer_name,
           fmr.units_to_retire,
           fmr.attribute1,
           fmr.attribute2,
           fmr.attribute3,             fmr.attribute4,
           fmr.attribute5,             fmr.attribute6,
           fmr.attribute7,             fmr.attribute8,
           fmr.attribute9,             fmr.attribute10,
           fmr.attribute11,            fmr.attribute12,
           fmr.attribute13,            fmr.attribute14,
           fmr.attribute15,            fmr.attribute_category_code,
           fmr.segment1_low,           fmr.segment2_low,
           fmr.segment3_low,           fmr.segment4_low,
           fmr.segment5_low,           fmr.segment6_low,
           fmr.segment7_low,           fmr.segment8_low,
           fmr.segment9_low,           fmr.segment10_low,
           fmr.segment11_low,          fmr.segment12_low,
           fmr.segment13_low,          fmr.segment14_low,
           fmr.segment15_low,          fmr.segment16_low,
           fmr.segment17_low,          fmr.segment18_low,
           fmr.segment19_low,          fmr.segment20_low,
           fmr.segment21_low,          fmr.segment22_low,
           fmr.segment23_low,          fmr.segment24_low,
           fmr.segment25_low,          fmr.segment26_low,
           fmr.segment27_low,          fmr.segment28_low,
           fmr.segment29_low,          fmr.segment30_low,
           fmr.segment1_high,          fmr.segment2_high,
           fmr.segment3_high,          fmr.segment4_high,
           fmr.segment5_high,          fmr.segment6_high,
           fmr.segment7_high,          fmr.segment8_high,
           fmr.segment9_high,          fmr.segment10_high,
           fmr.segment11_high,         fmr.segment12_high,
           fmr.segment13_high,         fmr.segment14_high,
           fmr.segment15_high,         fmr.segment16_high,
           fmr.segment17_high,         fmr.segment18_high,
           fmr.segment19_high,         fmr.segment20_high,
           fmr.segment21_high,         fmr.segment22_high,
           fmr.segment23_high,         fmr.segment24_high,
           fmr.segment25_high,         fmr.segment26_high,
           fmr.segment27_high,         fmr.segment28_high,
           fmr.segment29_high,         fmr.segment30_high,
           sob.currency_code,
           fbc.book_class,
           fmr.from_cost,
           fmr.to_cost,
           fmr.include_fully_rsvd_flag,
           fmr.group_asset_id,  -- crl, no reason to break out
           fmr.group_association
      FROM fa_mass_retirements fmr,
           fa_book_controls fbc,
           gl_sets_of_books sob
     WHERE fmr.mass_retirement_id = p_Mass_Retirement_Id
       AND fmr.book_type_code     = fbc.book_type_code
       AND fbc.set_of_books_id    = sob.set_of_books_id;
Line: 2993

    SELECT /* ORDERED INDEX (FAB FA_BOOKS_N2) */
           faa.asset_id,
           faa.asset_number,
           fab.cost,
           faa.current_units
      FROM fa_books         fab,
           fa_book_controls fbc,
           fa_additions_b   faa
     WHERE faa.asset_id = fab.asset_id
       AND (faa.asset_key_ccid = G_Asset_Key_Id
             OR G_Asset_Key_Id IS NULL)
       AND faa.asset_category_id = nvl(G_Category_Id,faa.asset_category_id)
       AND fab.cost >= nvl(G_From_Cost,fab.cost)
       AND fab.cost <= nvl(G_To_Cost,fab.cost)
       -- crl - no reason to make this conditional
       AND ((G_group_asset_id = -1 and -- group change
             fab.group_asset_id is null) OR -- group change
            (G_group_asset_id = -99) OR -- group change
            (G_group_asset_id > 0 and -- group change
             nvl(fab.group_asset_id, -999) = g_group_asset_id)) -- group change
       AND nvl(fab.period_counter_fully_reserved,-99999) =
          decode(G_Fully_Rsvd_Flag,
               'YES',fab.period_counter_fully_reserved,
               'NO',-99999,
               nvl(fab.period_counter_fully_reserved,-99999))
       AND faa.asset_number                >=
           nvl(G_From_Asset_Number, faa.asset_number)
       AND faa.asset_number                <=
           nvl(G_To_Asset_Number,  faa.asset_number)
       AND fab.date_placed_in_service
              BETWEEN nvl(G_From_DPIS,fab.date_placed_in_service-1)
                  AND nvl(G_To_DPIS  ,fab.date_placed_in_service+1)
       AND (faa.model_number = G_model_number
                OR G_model_number IS NULL)
       AND (faa.serial_number  = G_serial_number
                OR G_serial_number IS NULL)
       AND (faa.tag_number      = G_tag_number
                OR G_tag_number IS NULL)
       AND (faa.manufacturer_name = G_manufacturer_name
                OR G_manufacturer_name IS NULL)
       AND fab.book_type_code = G_Book_Type_Code -- 8264324 fbc.book_type_code
       AND fbc.date_ineffective is null
       AND EXISTS (SELECT null
                     FROM fa_distribution_history fad,
                          gl_code_combinations    gcc
                    WHERE fad.asset_id = faa.asset_id
                      AND fad.code_combination_id = gcc.code_combination_id
                      AND (fad.assigned_to = G_Employee_Id
                           OR G_Employee_Id IS NULL)
                      AND (fad.location_id = G_Location_Id
                           OR G_Location_Id IS NULL)
                      AND fad.date_ineffective IS NULL
                      AND (gcc.segment1 BETWEEN G_Segment1_Low
                                            AND G_Segment1_High
                           OR G_Segment1_Low IS NULL)
                      AND (gcc.segment2 BETWEEN G_Segment2_Low
                                            AND G_Segment2_High
                           OR G_Segment2_Low IS NULL)
                      AND (gcc.segment3 BETWEEN G_Segment3_Low
                                            AND G_Segment3_High
                           OR G_Segment3_Low IS NULL)
                      AND (gcc.segment4 BETWEEN G_Segment4_Low
                                            AND G_Segment4_High
                           OR G_Segment4_Low IS NULL)
                      AND (gcc.segment5 BETWEEN G_Segment5_Low
                                            AND G_Segment5_High
                           OR G_Segment5_Low IS NULL)
                      AND (gcc.segment6 BETWEEN G_Segment6_Low
                                            AND G_Segment6_High
                           OR G_Segment6_Low IS NULL)
                      AND (gcc.segment7 BETWEEN G_Segment7_Low
                                            AND G_Segment7_High
                           OR G_Segment7_Low IS NULL)
                      AND (gcc.segment8 BETWEEN G_Segment8_Low
                                            AND G_Segment8_High
                           OR G_Segment8_Low IS NULL)
                      AND (gcc.segment9 BETWEEN G_Segment9_Low
                                            AND G_Segment9_High
                           OR G_Segment9_Low IS NULL)
                      AND (gcc.segment10 BETWEEN G_Segment10_Low
                                             AND G_Segment10_High
                           OR G_Segment10_Low IS NULL)
                      AND (gcc.segment11 BETWEEN G_Segment11_Low
                                             AND G_Segment11_High
                           OR G_Segment11_Low IS NULL)
                      AND (gcc.segment12 BETWEEN G_Segment12_Low
                                             AND G_Segment12_High
                           OR G_Segment12_Low IS NULL)
                      AND (gcc.segment13 BETWEEN G_Segment13_Low
                                             AND G_Segment13_High
                           OR G_Segment13_Low IS NULL)
                      AND (gcc.segment14 BETWEEN G_Segment14_Low
                                             AND G_Segment14_High
                           OR G_Segment14_Low IS NULL)
                      AND (gcc.segment15 BETWEEN G_Segment15_Low
                                             AND G_Segment15_High
                           OR G_Segment15_Low IS NULL)
                      AND (gcc.segment16 BETWEEN G_Segment16_Low
                                             AND G_Segment16_High
                           OR G_Segment16_Low IS NULL)
                      AND (gcc.segment17 BETWEEN G_Segment17_Low
                                             AND G_Segment17_High
                           OR G_Segment17_Low IS NULL)
                      AND (gcc.segment18 BETWEEN G_Segment18_Low
                                             AND G_Segment18_High
                           OR G_Segment18_Low IS NULL)
                      AND (gcc.segment19 BETWEEN G_Segment19_Low
                                             AND G_Segment19_High
                           OR G_segment19_Low IS NULL)
                      AND (gcc.segment20 BETWEEN G_Segment20_Low
                                             AND G_Segment20_High
                           OR G_segment20_Low IS NULL)
                      AND (gcc.segment21 BETWEEN G_Segment21_Low
                                             AND G_Segment21_High
                           OR G_segment21_Low IS NULL)
                      AND (gcc.segment22 BETWEEN G_Segment22_Low
                                             AND G_Segment22_High
                           OR G_segment22_Low IS NULL)
                      AND (gcc.segment23 BETWEEN G_Segment23_Low
                                             AND G_Segment23_High
                           OR G_segment23_Low IS NULL)
                      AND (gcc.segment24 BETWEEN G_Segment24_Low
                                             AND G_Segment24_High
                           OR G_segment24_Low IS NULL)
                      AND (gcc.segment25 BETWEEN G_Segment25_Low
                                             AND G_Segment25_High
                           OR G_segment25_Low IS NULL)
                      AND (gcc.segment26 BETWEEN G_Segment26_Low
                                             AND G_Segment26_High
                           OR G_segment26_Low IS NULL)
                      AND (gcc.segment27 BETWEEN G_Segment27_Low
                                             AND G_Segment27_High
                           OR G_segment27_Low IS NULL)
                      AND (gcc.segment28 BETWEEN G_Segment28_Low
                                             AND G_Segment28_High
                           OR G_segment28_Low IS NULL)
                      And (gcc.segment29 BETWEEN G_Segment29_Low
                                             AND G_Segment29_High
                           OR G_segment29_Low IS NULL)
                      AND (gcc.segment30 BETWEEN G_Segment30_Low
                                             AND G_Segment30_High
                           OR G_segment30_Low IS NULL))
       AND (faa.asset_type = G_Asset_Type OR G_Asset_Type IS NULL)
       AND faa.asset_type IN ('CIP','CAPITALIZED','EXPENSED')
       -- YYOON 12/13/01: Performance Bug#2134816: Changed driving table from fa_books to fa_book_controls
       AND fbc.book_type_code = G_Book_Type_Code
       --AND fab.date_ineffective IS NULL
       AND fab.TRANSACTION_HEADER_ID_OUT IS NULL --bug 8264324
     ORDER BY fab.date_placed_in_service, faa.asset_number;
Line: 3149

   SELECT asset_id,
          asset_number,
          parent_asset_id,
          current_units
     FROM fa_additions_b
    WHERE parent_asset_id is not null
      AND parent_asset_id = p_parent_asset_id
   MINUS
   SELECT faa.asset_id, faa.asset_number, faa.parent_asset_id, faa.current_units
   FROM fa_books         fab,
        fa_additions_b   faa
    WHERE faa.parent_asset_id is not null
      AND faa.parent_asset_id = p_parent_asset_id
      AND  faa.asset_id = fab.asset_id
               AND (faa.asset_key_ccid = G_Asset_Key_Id OR G_Asset_Key_Id IS NULL)
               AND faa.asset_category_id = nvl(G_Category_Id,faa.asset_category_id)
               AND fab.period_counter_fully_retired IS NULL
               AND ((G_group_asset_id = -1 and -- group change
                     fab.group_asset_id is null) OR -- group change
                    (G_group_asset_id = -99) OR -- group change
                    (G_group_asset_id > 0 and -- group change
                     nvl(fab.group_asset_id, -999) = g_group_asset_id)) -- group change
               AND faa.asset_number                >=
                   nvl(G_From_Asset_Number, faa.asset_number)
               AND faa.asset_number                <=
                   nvl(G_To_Asset_Number,  faa.asset_number)
               AND fab.date_placed_in_service
              BETWEEN nvl(G_From_DPIS,fab.date_placed_in_service-1)
                  AND nvl(G_To_DPIS  ,fab.date_placed_in_service+1)
               AND (faa.model_number = G_model_number
                        OR G_model_number IS NULL)
               AND (faa.serial_number  = G_serial_number
                        OR G_serial_number IS NULL)
               AND (faa.tag_number      = G_tag_number
                        OR G_tag_number IS NULL)
               AND (faa.manufacturer_name = G_manufacturer_name
                        OR G_manufacturer_name IS NULL)
               AND NOT EXISTS (SELECT null
                                 FROM FA_TRANSACTION_HEADERS fth
                                WHERE fth.asset_id = fab.asset_id
                                  AND fth.book_type_code = fab.book_type_code
                                  AND (fth.transaction_date_entered > G_Retirement_Date and
                                       fth.transaction_type_code not in ('FULL RETIREMENT','REINSTATEMENT')))
               AND faa.asset_id in (SELECT faa2.asset_id
                                      FROM fa_additions_b faa2,
                                           gl_code_combinations gcc,
                                           fa_distribution_history fad
                                      WHERE fad.asset_id = faa2.asset_id
                                       AND fad.code_combination_id = gcc.code_combination_id
                                       AND (fad.assigned_to = G_Employee_Id
                                            OR G_Employee_Id IS NULL)
                                       AND (fad.location_id = G_Location_ID
                                            OR G_Location_Id IS NULL)
                                       AND fad.date_ineffective IS NULL
                                       AND (gcc.segment1 BETWEEN G_Segment1_Low
                                                             AND G_Segment1_High
                                            OR G_Segment1_Low IS NULL)
                                       AND (gcc.segment2 BETWEEN G_Segment2_Low
                                                             AND G_Segment2_High
                                            OR G_Segment2_Low IS NULL)
                                       AND (gcc.segment3 BETWEEN G_Segment3_Low
                                                             AND G_Segment3_High
                                            OR G_Segment3_Low IS NULL)
                                       AND (gcc.segment4 BETWEEN G_Segment4_Low
                                                             AND G_Segment4_High
                                            OR G_Segment4_Low IS NULL)
                                       AND (gcc.segment5 BETWEEN G_Segment5_Low
                                                             AND G_Segment5_High
                                            OR G_Segment5_Low IS NULL)
                                       AND (gcc.segment6 BETWEEN G_Segment6_Low
                                                             AND G_Segment6_High
                                            OR G_Segment6_Low IS NULL)
                                       AND (gcc.segment7 BETWEEN G_Segment7_Low
                                                             AND G_Segment7_High
                                            OR G_Segment7_Low IS NULL)
                                       AND (gcc.segment8 BETWEEN G_Segment8_Low
                                                             AND G_Segment8_High
                                            OR G_Segment8_Low IS NULL)
                                       AND (gcc.segment9 BETWEEN G_Segment9_Low
                                                             AND G_Segment9_High
                                            OR G_Segment9_Low IS NULL)
                                       AND (gcc.segment10 BETWEEN G_Segment10_Low
                                                              AND G_Segment10_High
                                            OR G_Segment10_Low IS NULL)
                                       AND (gcc.segment11 BETWEEN G_Segment11_Low
                                                              AND G_Segment11_High
                                            OR G_Segment11_Low IS NULL)
                                       AND (gcc.segment12 BETWEEN G_Segment12_Low
                                                              AND G_Segment12_High
                                            OR G_Segment12_Low IS NULL)
                                       AND (gcc.segment13 BETWEEN G_Segment13_Low
                                                              AND G_Segment13_High
                                            OR G_Segment13_Low IS NULL)
                                       AND (gcc.segment14 BETWEEN G_Segment14_Low
                                                              AND G_Segment14_High
                                            OR G_Segment14_Low IS NULL)
                                       AND (gcc.segment15 BETWEEN G_Segment15_Low
                                                              AND G_Segment15_High
                                            OR G_Segment15_Low IS NULL)
                                       AND (gcc.segment16 BETWEEN G_Segment16_Low
                                                              AND G_Segment16_High
                                            OR G_Segment16_Low IS NULL)
                                       AND (gcc.segment17 BETWEEN G_Segment17_Low
                                                              AND G_Segment17_High
                                            OR G_Segment17_Low IS NULL)
                                       AND (gcc.segment18 BETWEEN G_Segment18_Low
                                                              AND G_Segment18_High
                                            OR G_Segment18_Low IS NULL)
                                       AND (gcc.segment19 BETWEEN G_Segment19_Low
                                                              AND G_Segment19_High
                                            OR G_segment19_Low IS NULL)
                                       AND (gcc.segment20 BETWEEN G_Segment20_Low
                                                              AND G_Segment20_High
                                            OR G_segment20_Low IS NULL)
                                       AND (gcc.segment21 BETWEEN G_Segment21_Low
                                                              AND G_Segment21_High
                                            OR G_segment21_Low IS NULL)
                                       AND (gcc.segment22 BETWEEN G_Segment22_Low
                                                              AND G_Segment22_High
                                            OR G_segment22_Low IS NULL)
                                       AND (gcc.segment23 BETWEEN G_Segment23_Low
                                                              AND G_Segment23_High
                                            OR G_segment23_Low IS NULL)
                                       AND (gcc.segment24 BETWEEN G_Segment24_Low
                                                              AND G_Segment24_High
                                            OR G_segment24_Low IS NULL)
                                       AND (gcc.segment25 BETWEEN G_Segment25_Low
                                                              AND G_Segment25_High
                                            OR G_segment25_Low IS NULL)
                                       AND (gcc.segment26 BETWEEN G_Segment26_Low
                                                              AND G_Segment26_High
                                            OR G_segment26_Low IS NULL)
                                       AND (gcc.segment27 BETWEEN G_Segment27_Low
                                                              AND G_Segment27_High
                                            OR G_segment27_Low IS NULL)
                                       AND (gcc.segment28 BETWEEN G_Segment28_Low
                                                              AND G_Segment28_High
                                            OR G_segment28_Low IS NULL)
                                       And (gcc.segment29 BETWEEN G_Segment29_Low
                                                              AND G_Segment29_High
                                            OR G_segment29_Low IS NULL)
                                       AND (gcc.segment30 BETWEEN G_Segment30_Low
                                                              AND G_Segment30_High
                                            OR G_segment30_Low IS NULL))
               AND (FAA.asset_type = G_Asset_Type OR G_Asset_Type IS NULL)
               AND faa.asset_type IN ('CIP','CAPITALIZED','EXPENSED')
               AND fab.book_type_code = G_Book_Type_Code
               AND fab.date_ineffective IS NULL;
Line: 3299

   SELECT fab.cost
     FROM fa_additions_b   faa,
          fa_books         fab
    WHERE faa.asset_id = fab.asset_id
      AND faa.asset_id = p_asset_id
      AND fab.period_counter_fully_retired IS NULL
      AND faa.asset_type IN ('CIP','CAPITALIZED','EXPENSED')
      AND fab.book_type_code = G_Book_Type_Code
      And fab.date_ineffective IS NULL;
Line: 3311

   SELECT asset_id,
          cost_retired
     FROM fa_mass_ext_retirements
    WHERE batch_name = p_batch_name;
Line: 3319

   SELECT fab2.recognize_gain_loss
     FROM fa_books fab1, fa_books fab2
    WHERE fab1.book_type_code = G_Book_Type_Code
      AND fab1.asset_id = p_asset_id
      AND fab1.transaction_header_id_out IS NULL
      AND fab2.asset_id = fab1.group_asset_id
      AND fab2.book_type_code = fab1.book_type_code
      AND fab2.transaction_header_id_out IS NULL;
Line: 3522

      delete from fa_mass_ext_retirements
       where batch_name = g_batch_name;
Line: 3531

   SELECT Precision, Extended_precision, MINIMUM_ACCOUNTABLE_UNIT
     INTO G_Precision, G_Ext_Precision, G_Min_Acct_Unit
     FROM FND_CURRENCIES
    WHERE Currency_code = G_Currency_Code;
Line: 3708

                        Write_Message(l_sc_Asset_Number(l_loop_count_sub),'FA_SHARED_INSERT_DEBUG','','');
Line: 3782

                Write_Message(l_Asset_Number(l_loop_count),'FA_SHARED_INSERT_DEBUG','','');
Line: 3838

            l_asset_dist_tbl.delete;
Line: 3871

                        'Before insert into fa_mass_ext_ret ccid',
                        l_asset_dist_tbl(l_dist_count).expense_ccid   );
Line: 3875

         insert into fa_mass_ext_retirements
             (batch_name,
              mass_external_retire_id,
              book_type_code,
              review_status,
              asset_id,
              calc_gain_loss_flag,
              created_by,
              creation_date,
              last_updated_by,
              last_update_date,
              last_update_login,
              cost_retired,
              cost_of_removal,
              proceeds_of_sale,
              retirement_type_code,
              date_retired,
              transaction_name,
              units,
              code_combination_id,
              location_id,
              assigned_to,
	      attribute_category,   --bug#7287382
              attribute1,
              attribute2,
              attribute3,
              attribute4,
              attribute5,
              attribute6,
              attribute7,
              attribute8,
              attribute9,
              attribute10,
              attribute11,
              attribute12,
              attribute13,
              attribute14,
              attribute15,
	      th_attribute_category, --bug#7287382
              th_attribute1,
              th_attribute2,
              th_attribute3,
              th_attribute4,
              th_attribute5,
              th_attribute6,
              th_attribute7,
              th_attribute8,
              th_attribute9,
              th_attribute10,
              th_attribute11,
              th_attribute12,
              th_attribute13,
              th_attribute14,
              th_attribute15,
              recognize_gain_loss
              )
         values
             (g_batch_name,
              fa_mass_ext_retirements_s.nextval,
              G_book_type_code,
              decode(nvl(g_units,0),'0','POST','DELETE'),
              l_candidate_asset_id(l_count),
              'NO',
              g_last_updated_by,
              sysdate,
              g_last_updated_by,
              sysdate,
              g_last_update_login,
              l_candidate_cost_retired(l_count),
              0,
              0,
              G_retirement_type_code,
              G_retirement_date,
              G_transaction_name,
              l_asset_dist_tbl(l_dist_count).units_assigned,
              l_asset_dist_tbl(l_dist_count).expense_ccid,
              l_asset_dist_tbl(l_dist_count).location_ccid,
              l_asset_dist_tbl(l_dist_count).assigned_to,
	      g_attribute_category_code,    --bug#7287382
              g_attribute1,
              g_attribute2,
              g_attribute3,
              g_attribute4,
              g_attribute5,
              g_attribute6,
              g_attribute7,
              g_attribute8,
              g_attribute9,
              g_attribute10,
              g_attribute11,
              g_attribute12,
              g_attribute13,
              g_attribute14,
              g_attribute15,
	      g_attribute_category_code,    --bug#7287382
              g_attribute1,
              g_attribute2,
              g_attribute3,
              g_attribute4,
              g_attribute5,
              g_attribute6,
              g_attribute7,
              g_attribute8,
              g_attribute9,
              g_attribute10,
              g_attribute11,
              g_attribute12,
              g_attribute13,
              g_attribute14,
              g_attribute15,
              l_recognize_gain_loss
                );
Line: 3990

                        'After insert into FA_MASS_EXT_RETIREMENTS table, asset_id',
              l_candidate_asset_id(l_count));
Line: 3993

                        'After insert into FA_MASS_EXT_RETIREMENTS table, units_assigned',
                        l_asset_dist_tbl(l_dist_count).units_assigned );
Line: 3996

                        'After insert into FA_MASS_EXT_RETIREMENTS table, cost_retired',
                              l_candidate_cost_retired(l_count));
Line: 4000

                        'After insert into FA_MASS_EXT_RETIREMENTS table',
                        l_candidate_units(l_count) );
Line: 4008

                fa_debug_pkg.add(l_calling_fn, 'After dh-insert loop', ''  , p_log_level_rec => g_log_level_rec);
Line: 4010

           l_asset_dist_tbl.delete;
Line: 4018

      l_asset_id.delete;
Line: 4019

      l_asset_number.delete;
Line: 4020

      l_cost_retired.delete;
Line: 4021

      l_SC_Asset_Id.delete;
Line: 4022

      l_SC_Asset_Number.delete;
Line: 4023

      l_parent_asset_id.delete;
Line: 4024

      l_candidate_asset_id.delete;
Line: 4025

      l_candidate_cost_retired.delete;
Line: 4027

      l_candidate_units.delete;
Line: 4060

   select count(*),
          sum(abs(cost_retired))
     into l_total_count_retired,
          l_total_cost_retired
     from fa_mass_ext_retirements
    where batch_name = g_batch_name;
Line: 4097

         select count(*)
           into l_msg_count
           from fa_mass_ext_retirements
          where batch_name = g_batch_name;
Line: 4165

         UPDATE fa_mass_ext_retirements
            SET cost_of_removal    = l_prorated_cost_of_removal(l_count),
                proceeds_of_sale   = l_prorated_proceeds_of_sale(l_count)
          where asset_id           = l_asset_id(l_count)
            and batch_name         = g_batch_name;
Line: 4171

         l_asset_id.delete;
Line: 4172

         l_cost_retired.delete;
Line: 4173

         l_prorated_cost_of_removal.delete;
Line: 4174

         l_prorated_proceeds_of_sale.delete;
Line: 4187

   if nvl(g_units,0) = 0 then  -- status already inserted in allocate_units.
     update fa_mass_retirements
        set status = 'CREATED_RET'
      where mass_retirement_id = G_Mass_Retirement_ID;
Line: 4259

        update fa_mass_retirements
           set status = 'FAILED_CRE'
         where mass_retirement_id = G_Mass_Retirement_ID;
Line: 4280

        update fa_mass_retirements
           set status = 'FAILED_CRE'
         where mass_retirement_id = G_Mass_Retirement_ID;