APPS.AR_UPGHARNESS_PKG SQL Statements

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

Line: 16

PROCEDURE  update_process_status
(p_request_id    IN NUMBER
,p_status        IN VARCHAR2);

Line: 20

PROCEDURE insert_req_control
(p_ins_rec            IN         xla_upgrade_requests%ROWTYPE,
 x_request_control_id OUT NOCOPY NUMBER);

Line: 24

PROCEDURE update_req_control
(p_request_control_id    IN NUMBER,
 p_status                IN VARCHAR2);

Line: 81

PROCEDURE update_gl_period
IS
  CURSOR c IS
  SELECT 'Y'
    FROM ar_submission_ctrl_gt
   WHERE STATUS <> 'NORMAL';

Line: 88

  CURSOR update_gl_periods IS
  SELECT application_id,
         ledger_id,
         period_name,
         period_year
    FROM gl_period_statuses gps
   WHERE gps.migration_status_code = 'P'
     AND gps.application_id (+)    = 222;

Line: 98

  update_gps_error        EXCEPTION;

Line: 104

    FOR l_update_gl_periods IN update_gl_periods LOOP
       l_status := xla_upgrade_pub.set_migration_status_code
                   ( 222,
                     l_update_gl_periods.ledger_id,
                     l_update_gl_periods.period_name,
                     l_update_gl_periods.period_year);

Line: 111

         RAISE update_gps_error;

Line: 122

  WHEN UPDATE_GPS_ERROR THEN
     RAISE;

Line: 134

  SELECT 'Y'
    FROM gl_mc_reporting_options_11i
   WHERE application_id = 222;

Line: 159

  INSERT INTO ar_upg_120_control
  (script_name,
   processed_flag,
   action_flag,
   batch_id,
   creation_date,
   created_by,
   last_update_date,
   last_updated_by) VALUES
     (p_script_name,
      'A',
      'R', --downtime
      p_batch_id,
      sysdate,
      -2005,
      sysdate,
      -2005);

Line: 187

  SELECT request_id
    FROM ar_submission_ctrl_gt
   WHERE batch_id   = p_batch_id
     AND status     = 'SUBMITTED';

Line: 231

     update_process_status
	  (p_request_id => l_request_id
	  ,p_status     => l_dev_status);

Line: 253

  SELECT script_name     script_name,
         table_name      table_name,
         worker_id       worker_num,
         order_num       order_num,
         rowid
    FROM ar_submission_ctrl_gt
   WHERE status = 'INSERTED'
   AND batch_id = p_batch_id
   ORDER BY order_num ASC;

Line: 265

  SELECT COUNT(*)
    FROM ar_submission_ctrl_gt
   WHERE status     = 'SUBMITTED'
     AND batch_id   = p_batch_id;

Line: 290

    SELECT MAX(worker_id)   max_worker
      FROM ar_submission_ctrl_gt
     WHERE batch_id       = p_batch_id
       AND table_name     = p_table_name
       AND status         <> 'INSERTED';

Line: 349

        UPDATE ar_submission_ctrl_gt
           SET request_id   = -9,
               status = 'ABORTED'
         WHERE rowid    = l_rowid;

Line: 359

        UPDATE ar_submission_ctrl_gt
           SET request_id   = l_req_id,
               status       = 'SUBMITTED'
         WHERE rowid        = l_rowid;

Line: 381

PROCEDURE  update_process_status
(p_request_id   IN NUMBER
,p_status       IN VARCHAR2)
IS
BEGIN
  log('updating ar_submission_ctrl_gt the process status to '||p_status||' for request_id '||p_request_id);

Line: 387

  UPDATE ar_submission_ctrl_gt
     SET status = p_status
   WHERE request_id = p_request_id;

Line: 392

    log('EXCEPTION OTHERS in update_process_status :'||SQLERRM);

Line: 411

  SELECT request_control_id
    FROM xla_upgrade_requests
   WHERE table_name  = p_table_name
     AND script_name = p_script_name
     AND workers_num = p_num_workers
     AND worker_id   = p_worker_id
     AND batch_size  = p_batch_size
	 AND application_id = 222;

Line: 422

  SELECT request_control_id
        ,ledger_id
        ,period_name
        ,worker_id
        ,workers_num
        ,batch_size
        ,batch_id
        ,start_date
        ,end_date
    FROM xla_upgrade_requests
   WHERE phase_num     = 0
     AND status_code    <> 'S'
     AND program_code = 'XLA_UPG'
	 AND application_id = 222;

Line: 441

  SELECT order_num
    FROM ar_submission_ctrl_gt
   WHERE table_name  = p_table_name
     AND script_name = p_script_name
     AND worker_id   = p_worker_id;

Line: 492

    insert_req_control(l_ins_rec,x_request_control_id);

Line: 496

    update_req_control(l_request_control_id,p_status);

Line: 518

PROCEDURE insert_req_control
(p_ins_rec            IN         xla_upgrade_requests%ROWTYPE,
 x_request_control_id OUT NOCOPY NUMBER)
IS
 l_request_control_id    NUMBER;

Line: 524

  log( message  => 'Inserting a record in ar_reqrest_control');

Line: 525

  SELECT xla_upgrade_requests_s.NEXTVAL
    INTO l_request_control_id
    FROM DUAL;

Line: 546

  INSERT INTO xla_upgrade_requests(
     request_control_id
    ,parent_request_control_id
    ,phase_num
    ,program_code
    ,description
    ,status_code
    ,ledger_id
    ,period_name
    ,worker_id
    ,workers_num
    ,table_name
    ,script_name
    ,batch_size
    ,batch_id
    ,order_num
    ,start_date
    ,end_date
    ,CREATION_DATE
    ,CREATED_BY
    ,LAST_UPDATE_DATE
    ,LAST_UPDATED_BY
    ,LAST_UPDATE_LOGIN
	,application_id )
   VALUES (
     l_request_control_id
    ,p_ins_rec.parent_request_control_id
    ,p_ins_rec.phase_num
    ,p_ins_rec.program_code
    ,p_ins_rec.description
    ,p_ins_rec.status_code
    ,p_ins_rec.ledger_id
    ,p_ins_rec.period_name
    ,p_ins_rec.worker_id
    ,p_ins_rec.workers_num
    ,p_ins_rec.table_name
    ,p_ins_rec.script_name
    ,p_ins_rec.batch_size
    ,p_ins_rec.batch_id
    ,p_ins_rec.order_num
    ,p_ins_rec.start_date
    ,p_ins_rec.end_date
    ,SYSDATE
    ,nvl(FND_GLOBAL.user_id,-1)
    ,SYSDATE
    ,nvl(FND_GLOBAL.user_id,-1)
    ,nvl(FND_GLOBAL.conc_login_id,FND_GLOBAL.login_id)
	,222 );

Line: 600

PROCEDURE update_req_control
(p_request_control_id    IN NUMBER,
 p_status                IN VARCHAR2)
IS
BEGIN
  log( message  => 'Updating control record with request_control_id :'||p_request_control_id||'
 by setting the status to:'||p_status);

Line: 607

  UPDATE xla_upgrade_requests
     SET status_code         = p_status
        ,LAST_UPDATE_DATE    = SYSDATE
        ,LAST_UPDATED_BY     = nvl(FND_GLOBAL.user_id,-1)
        ,LAST_UPDATE_LOGIN   = nvl(FND_GLOBAL.conc_login_id,FND_GLOBAL.login_id)
   WHERE request_control_id  = p_request_control_id
     AND application_id      = 222;

Line: 721

  SELECT batch_id
  INTO l_batch_id
  FROM xla_upgrade_requests
  WHERE worker_id = l_worker_id
  AND script_name = l_script_name
  AND table_name = l_table_name ;

Line: 898

    ARP_XLA_UPGRADE.update_gl_sla_link(l_table_owner  => l_table_owner,
                       l_table_name   => l_table_name,
                       l_script_name  => l_script_name,
                       l_worker_id    => l_worker_id,
                       l_num_workers  => l_num_workers,
                       l_batch_size   => l_batch_size,
                       l_batch_id     => l_batch_id,
                       l_action_flag  => 'P');

Line: 916

  update_req_control
  (p_request_control_id   => x_request_control_id,
   p_status               => 'S');

Line: 969

  x_nb_inserted      OUT NOCOPY NUMBER)
IS
  l_program_code     VARCHAR2(30);

Line: 993

   INSERT INTO ar_submission_ctrl_gt
   (worker_id         , --worker_number
    batch_id          , --batch_id
    script_name       , --script_name
    status            , --INSERTED, SUBMITTED, (NORMAL, ERROR, WARNING, CANCELLED, TERMINATED)
    order_num         , --order helper number
    request_id        , --request_id
    table_name        ) --table_name
     SELECT worker_id,
            batch_id,
            script_name,
            'INSERTED',
            order_num,
            request_control_id,
            table_name
       FROM xla_upgrade_requests
      WHERE batch_id                  = p_batch_id
        AND parent_request_control_id = p_parent_req_control_id
        AND phase_num                     = l_phase
        AND program_code              = l_program_code
        AND status_code              <> 'S'
		AND application_id            = 222;

Line: 1016

    x_nb_inserted := SQL%ROWCOUNT;

Line: 1044

      tab_status(i)            := 'INSERTED';

Line: 1051

    INSERT INTO ar_submission_ctrl_gt
    (worker_id         , --worker_number
     batch_id          , --batch_id
     script_name       , --script_name
     status            , --INSERTED, SUBMITTED, (NORMAL, ERROR, WARNING, CANCELLED, TERMINATED)
     order_num         , --order helper number
     request_id        , --request_id
     table_name        ) --table_name
    VALUES
    (tab_worker_number(i),
     tab_batch_id(i),
     tab_script_name(i),
     tab_status(i),
     tab_order(i),
     tab_request_id(i),
     tab_table_name(i));

Line: 1076

 x_nb_inserted      OUT NOCOPY NUMBER)
IS
  l_script_name      VARCHAR2(30);

Line: 1080

  x_nb_inserted  := 0;

Line: 1129

    x_nb_inserted  := 5 * p_workers_num;

Line: 1162

      x_nb_inserted  := 3 * p_workers_num;

Line: 1179

       x_nb_inserted  := p_workers_num;

Line: 1197

       x_nb_inserted  := x_nb_inserted + p_workers_num;

Line: 1212

       x_nb_inserted  := p_workers_num;

Line: 1225

    x_nb_inserted  := p_workers_num;

Line: 1242

  SELECT status,
         request_id
    FROM ar_submission_ctrl_gt
   WHERE batch_id   = p_batch_id
     AND status <> 'NORMAL';

Line: 1280

  x_nb_inserted         NUMBER := 0;

Line: 1287

  SELECT 'Y'
    FROM ar_submission_ctrl_gt
   WHERE status IN ('INSERTED','SUBMITTED')
     AND batch_id   = p_batch_id;

Line: 1317

       x_nb_inserted           => x_nb_inserted);

Line: 1325

       x_nb_inserted           => x_nb_inserted);

Line: 1333

       x_nb_inserted           => x_nb_inserted);

Line: 1341

       x_nb_inserted           => x_nb_inserted);

Line: 1350

       x_nb_inserted           => x_nb_inserted);

Line: 1356

    IF x_nb_inserted > 0 THEN
      execution_req := 'Y';

Line: 1371

       x_nb_inserted           => x_nb_inserted);

Line: 1381

       x_nb_inserted           => x_nb_inserted);

Line: 1390

       x_nb_inserted           => x_nb_inserted);

Line: 1399

       x_nb_inserted           => x_nb_inserted);

Line: 1409

       x_nb_inserted           => x_nb_inserted);

Line: 1415

    IF x_nb_inserted > 0 THEN
      execution_req := 'Y';

Line: 1484

  SELECT start_date,
         end_date
    FROM xla_upgrade_dates
   WHERE ledger_id = p_ledger_id;

Line: 1491

   SELECT b.start_date,
          b.end_date
     FROM xla_upgrade_requests b
    WHERE b.ledger_id    = p_ledger_id
      AND b.program_code = 'XLA_UPG'
      AND b.status_code  = 'S'
      AND b.phase_num        = 0
      AND b.application_id = 222
      AND EXISTS (SELECT NULL
                   FROM xla_upgrade_dates a
                  WHERE (a.start_date BETWEEN b.start_date AND b.end_date OR
                         a.end_date   BETWEEN b.start_date AND b.end_date)
                    AND a.ledger_id = b.ledger_id);

Line: 1606

     DELETE FROM ar_submission_ctrl_gt;

Line: 1626

     DELETE FROM ar_submission_ctrl_gt;

Line: 1645

     DELETE FROM ar_submission_ctrl_gt;

Line: 1664

     DELETE FROM ar_submission_ctrl_gt;

Line: 1684

     DELETE FROM ar_submission_ctrl_gt;

Line: 1704

     DELETE FROM ar_submission_ctrl_gt;

Line: 1723

     DELETE FROM ar_submission_ctrl_gt;

Line: 1746

     DELETE FROM ar_submission_ctrl_gt;

Line: 1765

     DELETE FROM ar_submission_ctrl_gt;

Line: 1784

     DELETE FROM ar_submission_ctrl_gt;

Line: 1804

     DELETE FROM ar_submission_ctrl_gt;

Line: 1822

     DELETE FROM ar_submission_ctrl_gt;

Line: 1842

     DELETE FROM ar_submission_ctrl_gt;

Line: 1881

  SELECT xla_upg_batches_s.NEXTVAL
    FROM dual;

Line: 1887

  SELECT *
    FROM xla_upgrade_requests
   WHERE program_code = 'XLA_UPG'
     AND phase_num        = 0
     AND status_code  = 'A'
     AND ledger_id    = p_ledger_id
	 AND application_id = 222;

Line: 1900

  SELECT DECODE(program_code,'AR_UPG' ,'AR' ,
                             'GIR_UPG' ,'GL' , --GIR
                             'MRC_UPG','MRC',
                             'JL_UPG' ,'JL',
							 'PSA_UPG','PSA')
    FROM xla_upgrade_requests
   WHERE program_code <> 'XLA_UPG'
     AND phase_num        <> 0
     AND status_code  =  'A'
     AND ledger_id    =  p_ledger_id
	 AND application_id = 222;

Line: 1915

  SELECT start_date,
         end_date
    FROM xla_upgrade_dates
   WHERE ledger_id    =  p_ledger_id;

Line: 1922

  SELECT name
    FROM gl_ledgers
   WHERE ledger_id = p_ledger_id;

Line: 2058

     insert_req_control
      (p_ins_rec            => l_xla_upg_rec,
       x_request_control_id => x_request_control_id);

Line: 2104

         update_req_control
         (p_request_control_id    => l_xla_upg_rec.request_control_id,
          p_status                => 'S');

Line: 2109

         update_gl_period;