APPS.FA_UPGHARNESS_PKG SQL Statements

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

Line: 68

   select count(*)
   into   l_already_running
   from   fa_deprn_periods
   where  substr(xla_conversion_status, 1, 1) in
             ('0', '1', '2', '3', '4', '5', '6', '7', '8', '9');

Line: 78

   select count(*)
   into   l_existing_error
   from   fa_deprn_periods
   where  substr(xla_conversion_status, 1, 1) = 'E';

Line: 87

   update fa_deprn_periods dp
   set    dp.xla_conversion_status = 'H'
   where dp.xla_conversion_status is not null
   and   dp.xla_conversion_status not like 'U%'
   and   exists
   (
    select 'x'
    from   gl_period_statuses ps,
           fa_book_controls bc
    where  ps.application_id = 101
    and    ps.migration_status_code in ('P', 'U')
    and    bc.set_of_books_id = ps.set_of_books_id
    and    dp.book_type_code = bc.book_type_code
    and    dp.period_name = ps.period_name
   );

Line: 103

   select xla_upg_batches_s.nextval
   into   l_batch_id
   from   dual;

Line: 145

   l_worker.delete;

Line: 181

   select count(*)
   into   l_errors
   from   fa_deprn_periods
   where  nvl(xla_conversion_status, 'UA') not in ('UT', 'UD', 'UA', 'H');

Line: 255

/*   select xla_upg_batches_s.nextval
   into   l_batch_id
   from   dual; */

Line: 306

/*   select xla_upg_batches_s.nextval
   into   l_batch_id
   from   dual;*/

Line: 354

   select bc.book_type_code, ps.period_name
   from   gl_period_statuses ps,
          fa_book_controls bc
   where  ps.application_id = 101
   and    ps.migration_status_code in ('P', 'U')
   and    bc.set_of_books_id = ps.set_of_books_id;

Line: 370

   ad_parallel_updates_pkg.initialize_rowid_range(
           ad_parallel_updates_pkg.ROWID_RANGE,
           p_table_owner,
           l_table_name1,
           p_script_name,
           p_worker_id,
           p_workers_num,
           l_batch_size, 0);

Line: 379

   ad_parallel_updates_pkg.get_rowid_range(
           l_start_rowid,
           l_end_rowid,
           l_any_rows_to_process,
           l_batch_size,
           TRUE);

Line: 399

      ad_parallel_updates_pkg.processed_rowid_range(
          l_rows_processed,
          l_end_rowid);

Line: 408

      ad_parallel_updates_pkg.get_rowid_range(
         l_start_rowid,
         l_end_rowid,
         l_any_rows_to_process,
         l_batch_size,
         FALSE);

Line: 421

   select count(*)
   into   l_group_books
   from   fa_book_controls
   where  allow_group_deprn_flag = 'Y';

Line: 426

   ad_parallel_updates_pkg.initialize_rowid_range(
           ad_parallel_updates_pkg.ROWID_RANGE,
           p_table_owner,
           l_table_name2,
           p_script_name,
           p_worker_id,
           p_workers_num,
           l_batch_size, 0);

Line: 435

   ad_parallel_updates_pkg.get_rowid_range(
           l_start_rowid,
           l_end_rowid,
           l_any_rows_to_process,
           l_batch_size,
           TRUE);

Line: 467

      ad_parallel_updates_pkg.processed_rowid_range(
          l_rows_processed,
          l_end_rowid);

Line: 477

      ad_parallel_updates_pkg.get_rowid_range(
         l_start_rowid,
         l_end_rowid,
         l_any_rows_to_process,
         l_batch_size,
         FALSE);

Line: 497

         update fa_deprn_periods dp
         set    dp.xla_conversion_status =
                decode (substr(dp.xla_conversion_status, 1, 1),
                        'H', '1',
                        'E', '1',
                        'U', '1',
                        '0', to_char(to_number(dp.xla_conversion_status) + 1),
                        '1', to_char(to_number(dp.xla_conversion_status) + 1),
                        '2', to_char(to_number(dp.xla_conversion_status) + 1),
                        '3', to_char(to_number(dp.xla_conversion_status) + 1),
                        '4', to_char(to_number(dp.xla_conversion_status) + 1),
                        '5', to_char(to_number(dp.xla_conversion_status) + 1),
                        '6', to_char(to_number(dp.xla_conversion_status) + 1),
                        '7', to_char(to_number(dp.xla_conversion_status) + 1),
                        '8', to_char(to_number(dp.xla_conversion_status) + 1),
                        '9', to_char(to_number(dp.xla_conversion_status) + 1),
                        dp.xla_conversion_status)
         where  dp.book_type_code = l_book_type_code_tbl(i)
         and   dp.period_name = l_period_name_tbl(i)
         and   dp.xla_conversion_status is not null
         and   dp.xla_conversion_status not in ('UA', 'UT');

Line: 545

   update fa_deprn_periods dp
   set    dp.xla_conversion_status = 'UT'
   where  dp.xla_conversion_status = to_char (p_workers_num)
   and    exists
   (
    select 'x'
    from   gl_period_statuses ps,
           fa_book_controls bc
    where  ps.application_id = 101
    and    ps.migration_status_code in ('P', 'U')
    and    bc.set_of_books_id = ps.set_of_books_id
    and    dp.book_type_code = bc.book_type_code
    and    ps.period_name = dp.period_name
   );

Line: 561

   update fa_deprn_periods dp
   set    dp.xla_conversion_status = 'ET'
   where  dp.xla_conversion_status <> to_char (p_workers_num)
   and    dp.xla_conversion_status not in ('UA', 'UT')
   and    dp.xla_conversion_status is not null
   and    exists
   (
    select 'x'
    from   gl_period_statuses ps,
           fa_book_controls bc
    where  ps.application_id = 101
    and    ps.migration_status_code in ('P', 'U')
    and    bc.set_of_books_id = ps.set_of_books_id
    and    dp.book_type_code = bc.book_type_code
    and    ps.period_name = dp.period_name
   );

Line: 614

   select bc.book_type_code, ps.period_name
   from   gl_period_statuses ps,
          fa_book_controls bc
   where  ps.application_id = 101
   and    ps.migration_status_code in ('P', 'U')
   and    bc.set_of_books_id = ps.set_of_books_id;

Line: 632

    select count(*)
    into   l_deprn_run
    from   fa_deprn_periods dp
    where  dp.period_close_date is null
    and    dp.deprn_run = 'Y'
    and    dp.xla_conversion_status is not null
    and    dp.xla_conversion_status not in ('UA', 'UD')
    and   exists
    (
     select 'x'
     from   gl_period_statuses ps,
            fa_book_controls bc
     where  ps.application_id = 101
     and    ps.migration_status_code in ('P', 'U')
     and    bc.set_of_books_id = ps.set_of_books_id
     and    dp.book_type_code = bc.book_type_code
     and    dp.period_name = ps.period_name
    );

Line: 655

   ad_parallel_updates_pkg.initialize_rowid_range(
           ad_parallel_updates_pkg.ROWID_RANGE,
           p_table_owner,
           l_table_name1,
           p_script_name,
           p_worker_id,
           p_workers_num,
           l_batch_size, 0);

Line: 664

   ad_parallel_updates_pkg.get_rowid_range(
           l_start_rowid,
           l_end_rowid,
           l_any_rows_to_process,
           l_batch_size,
           TRUE);

Line: 685

      ad_parallel_updates_pkg.processed_rowid_range(
          l_rows_processed,
          l_end_rowid);

Line: 694

      ad_parallel_updates_pkg.get_rowid_range(
         l_start_rowid,
         l_end_rowid,
         l_any_rows_to_process,
         l_batch_size,
         FALSE);

Line: 714

   ad_parallel_updates_pkg.initialize_rowid_range(
           ad_parallel_updates_pkg.ROWID_RANGE,
           p_table_owner,
           l_table_name2,
           p_script_name,
           1,                   -- p_worker_id
           1,                   -- p_workers_num
           l_batch_size, 0);

Line: 723

   ad_parallel_updates_pkg.get_rowid_range(
           l_start_rowid,
           l_end_rowid,
           l_any_rows_to_process,
           l_batch_size,
           TRUE);

Line: 743

      ad_parallel_updates_pkg.processed_rowid_range(
          l_rows_processed,
          l_end_rowid);

Line: 752

      ad_parallel_updates_pkg.get_rowid_range(
         l_start_rowid,
         l_end_rowid,
         l_any_rows_to_process,
         l_batch_size,
         FALSE);

Line: 767

      update fa_deprn_periods dp
      set    dp.xla_conversion_status =
             decode (substr(dp.xla_conversion_status, 1, 1),
                     'U', '1',
                     'E', '1',
                     '0', to_char(to_number(dp.xla_conversion_status) + 1),
                     '1', to_char(to_number(dp.xla_conversion_status) + 1),
                     '2', to_char(to_number(dp.xla_conversion_status) + 1),
                     '3', to_char(to_number(dp.xla_conversion_status) + 1),
                     '4', to_char(to_number(dp.xla_conversion_status) + 1),
                     '5', to_char(to_number(dp.xla_conversion_status) + 1),
                     '6', to_char(to_number(dp.xla_conversion_status) + 1),
                     '7', to_char(to_number(dp.xla_conversion_status) + 1),
                     '8', to_char(to_number(dp.xla_conversion_status) + 1),
                     '9', to_char(to_number(dp.xla_conversion_status) + 1),
                     dp.xla_conversion_status)
      where dp.xla_conversion_status is not null
      and   dp.xla_conversion_status not in ('UA', 'UD', 'H')
      and   dp.period_close_date is null;

Line: 800

         update fa_deprn_periods dp
         set    dp.xla_conversion_status =
                decode (substr(dp.xla_conversion_status, 1, 1),
                        'U', '1',
                        'E', '1',
                        '0', to_char(to_number(dp.xla_conversion_status) + 1),
                        '1', to_char(to_number(dp.xla_conversion_status) + 1),
                        '2', to_char(to_number(dp.xla_conversion_status) + 1),
                        '3', to_char(to_number(dp.xla_conversion_status) + 1),
                        '4', to_char(to_number(dp.xla_conversion_status) + 1),
                        '5', to_char(to_number(dp.xla_conversion_status) + 1),
                        '6', to_char(to_number(dp.xla_conversion_status) + 1),
                        '7', to_char(to_number(dp.xla_conversion_status) + 1),
                        '8', to_char(to_number(dp.xla_conversion_status) + 1),
                        '9', to_char(to_number(dp.xla_conversion_status) + 1),
                        dp.xla_conversion_status)
         where dp.book_type_code = l_book_type_code_tbl(i)
         and   dp.period_name = l_period_name_tbl(i)
         and   dp.xla_conversion_status is not null
         and   dp.xla_conversion_status not in ('UA', 'UD', 'H');

Line: 852

	     select distinct nvl(xla_conversion_status,'N') xla_conversion_status
	     from   fa_deprn_periods
	     where  period_name = cp_period_name
	      and    book_type_code in ( select book_type_code
					    from   fa_book_controls
					   where  set_of_books_id = cp_ledger_id )
	      order by xla_conversion_status
	    ;

Line: 916

         UPDATE gl_period_statuses ps
           SET ps.migration_status_code = 'U'
         WHERE ps.migration_status_code = 'P'
           AND ps.application_id = 101
           AND (ps.ledger_id, ps.period_name) not in
             (
                  SELECT DISTINCT fbc.set_of_books_id, fdp.period_name
                    FROM fa_deprn_periods fdp, fa_book_controls fbc
                   WHERE fdp.book_type_code = fbc.book_type_code
             );

Line: 940

	     SELECT period_name, ledger_id
	     FROM   gl_period_statuses
	     WHERE  migration_status_code = 'P'
	       AND  application_id = 101
	      ORDER BY ledger_id, period_name
	      FOR UPDATE OF migration_status_code;

Line: 962

		      UPDATE gl_period_statuses
		      SET    migration_status_code = 'P'
		      WHERE CURRENT OF c_periods;

Line: 968

                      UPDATE gl_period_statuses
		      SET    migration_status_code = 'U'
		      WHERE CURRENT OF c_periods;

Line: 974

                      UPDATE gl_period_statuses
		      SET    migration_status_code = null /* this will be reset to P when hot patch is re-run */
		      WHERE CURRENT OF c_periods;

Line: 1001

   update fa_deprn_periods dp
   set    dp.xla_conversion_status = 'UA'
   where  dp.xla_conversion_status = to_char (p_workers_num)
   and    exists
   (
    select 'x'
    from   gl_period_statuses ps,
           fa_book_controls bc
    where  ps.application_id = 101
    and    ps.migration_status_code in ('P', 'U')
    and    bc.set_of_books_id = ps.set_of_books_id
    and    dp.book_type_code = bc.book_type_code
    and    ps.period_name = dp.period_name
   );

Line: 1019

   update fa_deprn_periods dp
   set    dp.xla_conversion_status = 'ED'
   where  dp.xla_conversion_status <> to_char (p_workers_num)
   and    dp.xla_conversion_status not in ('UA', 'UT', 'UD')
   and    dp.xla_conversion_status is not null
   and    exists
   (
    select 'x'
    from   gl_period_statuses ps,
           fa_book_controls bc
    where  ps.application_id = 101
    and    ps.migration_status_code in ('P', 'U')
    and    bc.set_of_books_id = ps.set_of_books_id
    and    dp.book_type_code = bc.book_type_code
    and    ps.period_name = dp.period_name
   );