APPS.IGI_IAC_WEBADI_PKG SQL Statements

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

Line: 14

   Select 'X'
   From igi_iac_upload_headers
   Where file_name = cp_file_name
   and status_flag = 'T';

Line: 21

   Select hd.Book_Type_Code,
      hd.Period_Counter,
      hd.Currency_Code,
      hd.Status_Flag Hdr_Status_Flag,
      hd.Tolerance_Flag,
      hd.Tolerance_Amount,
      hd.Tolerance_Percent,
      hd.Revaluation_Id,
      ln.Asset_Id,
      ln.Line_Num,
      ln.Category_Id,
      ln.Original_Cost,
      ln.New_Cost,
      ln.status_flag Line_Status_Flag,
      ln.Gross_Flag,
      ln.Percentage_Diff,
      ln.Amount_Diff,
      ln.Exception_Message,
      ln.Comments
   From igi_iac_upload_headers hd,
        igi_iac_upload_lines ln,
        fa_additions fa
   Where fa.asset_number = cp_asset_number
   and hd.file_name = cp_file_name
   and ln.file_name = hd.file_name
   and ln.asset_id = fa.asset_id;

Line: 51

   Select hd.Book_Type_Code,
      hd.Period_Counter,
      hd.Currency_Code,
      hd.Status_Flag Hdr_Status_Flag,
      hd.Tolerance_Flag,
      hd.Tolerance_Amount,
      hd.Tolerance_Percent,
      hd.Revaluation_Id,
      ln.Asset_Id,
      ln.Line_Num,
      ln.Category_Id,
      ln.Original_Cost,
      ln.New_Cost,
      ln.status_flag Line_Status_Flag,
      ln.Gross_Flag,
      ln.Percentage_Diff,
      ln.Amount_Diff,
      ln.Exception_Message,
      ln.Comments
   From igi_iac_upload_headers hd,
        igi_iac_upload_lines ln,
        fa_additions fa
   Where fa.asset_number = cp_asset_number
   and hd.file_name = cp_file_name
   and ln.file_name = hd.file_name
   and ln.asset_id = fa.asset_id
   and ln.line_num = cp_line_num;

Line: 83

   Select bk.period_counter_fully_retired
   From fa_books bk
   Where bk.book_type_code = cp_book_type_code
   and bk.asset_id = cp_asset_id
   and bk.transaction_header_id_out is null;

Line: 114

      Select ct.calendar_type, ct.number_per_fiscal_year, bk.life_in_months
      Into l_calendar_type, l_number_per_fiscal_year, l_life_in_months
      From fa_calendar_types ct, fa_book_controls bc, fa_books bk
      Where ct.calendar_type = bc.deprn_calendar
      and bk.book_type_code = p_book_type_code
      and bk.date_ineffective is null
      and bk.asset_id = p_asset_id
      and bc.date_ineffective is null
      and bc.book_type_code = p_book_type_code;

Line: 161

   Procedure Delete_Line(p_file_name IN igi_iac_upload_headers.file_name%type,
                         p_asset_id IN igi_iac_upload_lines.asset_id%type,
                         p_line_num IN igi_iac_upload_lines.line_num%type) Is
   Begin
      Delete from igi_iac_upload_lines
      Where file_name = p_file_name
      and asset_id = p_asset_id
      and line_num = p_line_num;

Line: 171

   Procedure Insert_Header(p_upload_record IN upload_record) Is
   Begin
      Insert into igi_iac_upload_headers(
         File_Name,
         Book_Type_Code,
         Period_Counter,
         Currency_Code,
         Status_Flag,
         Tolerance_Flag,
         Tolerance_Amount,
         Tolerance_Percent,
         Revaluation_Id,
         Created_By,
         Creation_Date,
         Last_Update_Login,
         Last_Update_Date,
         Last_Updated_By)
      Values(
         p_upload_record.File_Name,
         p_upload_record.Book_Type_Code,
         p_upload_record.Period_Counter,
         p_upload_record.Currency_Code,
         p_upload_record.Hdr_Status_Flag,
         p_upload_record.Tolerance_Flag,
         p_upload_record.Tolerance_Amount,
         p_upload_record.Tolerance_Percent,
         p_upload_record.Revaluation_Id,
         l_global_user_id,
         l_global_date,
         l_global_login_id,
         l_global_date,
         l_global_user_id);

Line: 203

   End Insert_Header;

Line: 205

   Procedure Insert_Line(p_upload_record IN upload_record) Is
   Begin
      Insert into igi_iac_upload_lines(
         File_Name,
         Asset_Id,
         Line_Num,
         Category_Id,
         Original_Cost,
         New_Cost,
         Status_Flag,
         Gross_Flag,
         Percentage_Diff,
         Amount_Diff,
         Exception_Message,
         Comments,
         Created_By,
         Creation_Date,
         Last_Update_Login,
         Last_Update_Date,
         Last_Updated_By)
      Values (
         p_upload_record.file_name,
         p_upload_record.asset_id,
         p_upload_record.line_num,
         p_upload_record.category_id,
         p_upload_record.original_cost,
         p_upload_record.new_cost,
         p_upload_record.line_status_flag,
         p_upload_record.gross_flag,
         p_upload_record.percentage_diff,
         p_upload_record.amount_diff,
         p_upload_record.exception_message,
         p_upload_record.comments,
         l_global_user_id,
         l_global_date,
         l_global_login_id,
         l_global_date,
         l_global_user_id);

Line: 243

   End Insert_Line;

Line: 245

   Procedure Update_Header(p_upload_record IN upload_record) Is
   Begin
      Update igi_iac_upload_headers set
         Tolerance_Flag = p_upload_record.tolerance_flag,
         Tolerance_Amount = p_upload_record.tolerance_amount,
         Tolerance_Percent = p_upload_record.tolerance_percent,
         Revaluation_Id = p_upload_record.revaluation_id,
         Last_Update_Login = l_global_login_id,
         Last_Update_Date = l_global_date,
         Last_Updated_By = l_global_user_id
      Where file_name = p_upload_record.file_name;

Line: 256

   End Update_Header;

Line: 262

      Select date_placed_in_service, life_in_months
      From fa_books
      Where book_type_code = cp_book_type_code
      and asset_id = cp_asset_id
      and date_ineffective is null;

Line: 302

   Procedure Update_Duplicate_Assets(
                p_file_name IN igi_iac_upload_lines.file_name%type,
                p_book_type_code IN igi_iac_upload_headers.book_type_code%type,
                p_period_counter IN igi_iac_upload_headers.period_counter%type,
                p_asset_id IN igi_iac_upload_lines.asset_id%type,
                p_line_num IN igi_iac_upload_lines.line_num%type,
                p_message IN igi_iac_upload_lines.exception_message%type) Is
      Cursor C_Dup_Asset_Info(
                cp_file_name IN igi_iac_upload_lines.file_name%type,
                cp_asset_id IN igi_iac_upload_lines.asset_id%type,
                cp_line_num IN igi_iac_upload_lines.line_num%type) IS
      Select Line_Num, New_Cost, Gross_Flag, Status_Flag
      From igi_iac_upload_lines
      Where file_name = cp_file_name
      and asset_id = cp_asset_id
      and line_num <> cp_line_num;

Line: 322

      Select date_placed_in_service, life_in_months
      From fa_books
      Where book_type_code = cp_book_type_code
      and asset_id = cp_asset_id
      and date_ineffective is null;

Line: 365

                Update igi_iac_upload_lines
                Set New_Cost = C_Dup_Asset_Info_Rec.new_cost,
                    Status_Flag = 'E',
                    Amount_Diff = null,
                    Percentage_Diff = null,
                    Exception_Message = p_message
                Where file_name = p_file_name
                and asset_id = p_asset_id
                and line_num = C_Dup_Asset_Info_Rec.line_num;

Line: 377

   End Update_Duplicate_Assets;

Line: 435

      Select count(*)
      From igi_iac_upload_lines
      Where file_name = cp_file_name
      and asset_id = cp_asset_id;

Line: 443

      Select allow_prof_reval_flag
      From igi_iac_category_books
      Where book_type_code = cp_book_type_code
      and category_id = cp_category_id;

Line: 450

      Select max(period_counter)
      From fa_deprn_summary
      Where book_type_code = cp_book_type_code;

Line: 456

      Select last_period_counter
      From fa_book_controls
      Where book_type_code = cp_book_type_code;

Line: 544

         Update_Duplicate_Assets(p_upload_record.file_name,
                                 p_upload_record.book_type_code,
                                 p_upload_record.period_counter,
                                 p_upload_record.asset_id,
                                 p_upload_record.line_num,
                                 p_upload_record.exception_message);

Line: 568

   Procedure Update_Line(p_upload_record IN upload_record) Is
   Begin
      Update igi_iac_upload_lines
      Set new_cost = p_upload_record.new_cost,
         original_cost = p_upload_record.original_cost,
         percentage_diff = p_upload_record.percentage_diff,
         amount_diff = p_upload_record.amount_diff,
         status_flag = p_upload_record.line_status_flag,
         gross_flag = p_upload_record.gross_flag,
         exception_message = p_upload_record.exception_message,
         comments = p_upload_record.comments
      Where file_name = p_upload_record.file_name
      and asset_id = p_upload_record.asset_id
      and line_num = p_upload_record.line_num;

Line: 582

   End Update_Line;

Line: 584

   Procedure Update_Header_Status(
                p_file_name IN igi_iac_upload_headers.file_name%type) Is
      Cursor C_Lines(cp_file_name IN igi_iac_upload_headers.file_name%type) Is
      Select count(*) line_cnt
      From igi_iac_upload_lines
      Where file_name = cp_file_name;

Line: 593

      Select count(*) exp_cnt
      From igi_iac_upload_lines
      Where file_name = cp_file_name
      and status_flag = cp_status_flag;

Line: 604

         Delete from igi_iac_upload_headers where file_name = p_file_name;

Line: 610

            Update igi_iac_upload_headers
            Set status_flag = 'E'
            Where file_name = p_file_name;

Line: 619

               Update igi_iac_upload_headers
               Set status_flag = 'L'
       	       Where file_name = p_file_name;

Line: 623

       	       Update igi_iac_upload_headers
       	       Set status_flag = 'A'
       	       Where file_name = p_file_name;

Line: 638

   End Update_Header_Status;

Line: 648

      Select count(*)
      From igi_iac_upload_lines
      Where file_name = cp_file_name
      and asset_id = cp_asset_id
      and line_num <> cp_line_num
      and status_flag = 'E';

Line: 679

      Update_Line(l_dup_record);

Line: 700

      Select fa.last_period_counter, gl.currency_code
      From fa_book_controls fa, gl_sets_of_books gl
      Where book_type_code = cp_book_type_code
      and gl.set_of_books_id = fa.set_of_books_id;

Line: 708

      Select bk.cost,
             bk.period_counter_fully_retired,
             ad.asset_id,
             ad.asset_category_id
      From fa_books bk, fa_additions ad
      Where bk.book_type_code = cp_book_type_code
      and bk.transaction_header_id_out is null
      and ad.asset_id = bk.asset_id
      and ad.asset_number = cp_asset_number;

Line: 722

      Select nvl(adjusted_cost,0)adjusted_cost
      From igi_iac_asset_balances
      Where book_type_code = cp_book_type_code
      and asset_id = cp_asset_id
      and period_counter = cp_period_counter;

Line: 730

      Select status_flag, period_counter, currency_code
      From igi_iac_upload_headers
      Where file_name = cp_file_name;

Line: 736

      Select nvl(max(line_num),0) + 1 Line_Num
      From igi_iac_upload_lines
      Where file_name =  cp_file_name;

Line: 806

         Insert_Header(l_upload_record);

Line: 808

         Insert_Line(l_upload_record);

Line: 816

            Update_Header(l_upload_record);

Line: 820

            Insert_Line(l_upload_record);

Line: 833

      Update_Line(l_upload_record);

Line: 834

      Update_Header_Status(l_upload_record.file_name);

Line: 904

         Delete_Line(p_file_name,
                     l_upload_record.asset_id,
                     l_upload_record.line_num);

Line: 917

            Delete_Line(p_file_name,
                        l_upload_record.asset_Id,
                        l_upload_record.line_num);

Line: 945

         Update_Line(l_upload_record);

Line: 947

      Update_Header_Status(l_upload_record.file_name);

Line: 1034

         Delete_Line(p_file_name,
                     l_upload_record.asset_id,
                     l_upload_record.line_num);

Line: 1046

            Delete_Line(p_file_name,
                        l_upload_record.asset_id,
                        l_upload_record.line_num);

Line: 1068

         Update_Line(l_upload_record);

Line: 1075

      Update_Header_Status(l_upload_record.file_name);

Line: 1122

         Delete from igi_iac_upload_headers where file_name = p_file_name;

Line: 1123

         Delete from igi_iac_upload_lines where file_name = p_file_name;

Line: 1133

            Delete_Line(p_file_name,
                        l_upload_record.asset_id,
                        l_upload_record.line_num);

Line: 1147

                Update_Line(l_upload_record);

Line: 1150

         Update_Header_Status(l_upload_record.file_name);

Line: 1173

      Select * from igi_iac_upload_headers
      Where file_name = cp_file_name;

Line: 1178

      Select * from igi_iac_upload_lines
      Where file_name = cp_file_name;

Line: 1183

      Select distinct category_id from igi_iac_upload_lines
      where file_name = cp_file_name;

Line: 1188

      Select max(period_counter) max_period_counter
      From fa_deprn_summary
      Where book_type_code = cp_book_type_code;

Line: 1194

      Select last_period_counter
      From fa_book_controls
      Where book_type_code = cp_book_type_code;

Line: 1201

      SELECT irar.asset_id,
             fa.asset_number,
             irar.selected_for_reval_flag,
             iir.status
      FROM igi_iac_reval_asset_rules irar,
           igi_iac_revaluations iir,
           fa_additions fa
      WHERE irar.book_type_code = cp_book_type_code
      AND fa.asset_id = irar.asset_id
      AND irar.revaluation_id = iir.revaluation_id
      AND irar.revaluation_id = (SELECT max(revaluation_id)
                                 FROM igi_iac_revaluations
                                 WHERE book_type_code = cp_book_type_code
                                 AND   calling_program IN ('IGIIAIAR', 'SSUPLOAD') -- bug 3510376, add
                                 AND   status in ('PREVIEWED', 'NEW', 'FAILED_PRE',
                                                  'FAILED_RUN', 'PREVIEW', 'UPDATED'));

Line: 1228

      SELECT bk.cost
      FROM fa_books bk,
           fa_additions ad
      WHERE bk.book_type_code = cp_book_type_code
      AND bk.transaction_header_id_out IS NULL
      AND ad.asset_id = bk.asset_id
      AND ad.asset_id = cp_asset_id;

Line: 1242

      SELECT nvl(adjusted_cost,0)  adjusted_cost
      FROM igi_iac_asset_balances
      WHERE book_type_code = cp_book_type_code
      AND asset_id = cp_asset_id
      AND period_counter = cp_period_counter;

Line: 1282

      Select igi_iac_revaluations_s.NEXTVAL
      Into l_reval_id
      From dual;

Line: 1302

         Select igi_iac_revaluations_s.NEXTVAL
         Into l_reval_id
         From dual;

Line: 1334

         Insert into igi_iac_revaluations(
            Revaluation_Id,
            Book_Type_Code,
            Revaluation_Date ,
            Revaluation_Period ,
            Status,
            Reval_Request_Id ,
            Create_Request_Id ,
            Calling_Program ,
            Last_Update_Date,
            Created_By ,
            Last_Update_Login  ,
            Last_Updated_By ,
            Creation_Date)
         Values(
            l_reval_id,
            C_Upload_Hdr_Rec.book_type_code,
            l_reval_date,
            C_Upload_Hdr_Rec.period_counter,
            'NEW',
            null,
            null,
            'SSUPLOAD',
            l_global_date,
            l_global_user_id,
            l_global_login_id,
            l_global_user_id,
            l_global_date);

Line: 1364

            Insert into igi_iac_reval_categories(
               Revaluation_Id,
               Book_Type_Code,
               Category_Id,
               Select_Category,
               Last_Update_Date,
               Created_By ,
               Last_Update_Login  ,
               Last_Updated_By ,
               Creation_Date)
            Values(
               l_reval_id,
               C_Upload_Hdr_Rec.book_type_code,
               C_Categories_Rec.category_id,
               'Y',
               l_global_date,
               l_global_user_id,
               l_global_login_id,
               l_global_user_id,
               l_global_date);

Line: 1419

            Insert into igi_iac_reval_asset_rules(
               Revaluation_Id,
               Book_Type_Code,
               Category_Id,
               Asset_Id,
               Revaluation_Factor,
               Revaluation_Type,
               New_Cost,
               Current_Cost,
               Selected_For_Reval_Flag,
               Selected_For_Calc_Flag,
               Allow_Prof_Update,
               Created_By,
               Creation_Date,
               Last_Update_Login,
               Last_Update_Date,
               Last_Updated_By)
            Values(
               l_reval_id,
               C_Upload_Hdr_Rec.book_type_code,
               C_Upload_Lines_Rec.category_id,
               C_Upload_Lines_Rec.asset_id,
               l_reval_factor, -- bug 3412940 1,
               'P',
               C_Upload_Lines_Rec.new_cost,
               l_current_cost, -- bug 3536362 C_Upload_Lines_Rec.original_cost,
               'Y',
               null,
               null,
               l_global_user_id,
               l_global_date,
               l_global_login_id,
               l_global_date,
               l_global_user_id);

Line: 1458

         Update igi_iac_upload_headers
         Set status_flag = 'T', revaluation_id = l_reval_id
         Where file_name = p_file_name;