APPS.CN_UPGRADE_UTL_PKG SQL Statements

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

Line: 11

	SELECT start_date
	  FROM cn_period_statuses_all
	  WHERE period_id = p_period_id
	  AND ((org_id = p_org_id) OR (org_id IS NULL AND p_org_id IS NULL));

Line: 42

	SELECT end_date
	  FROM cn_period_statuses_all
	  WHERE period_id = p_period_id
	  AND ((org_id = p_org_id) OR (org_id IS NULL AND p_org_id IS NULL));

Line: 79

   SELECT profile_option_value
   from   fnd_profile_option_values
   where  profile_option_id =
     (select profile_option_id
      from   fnd_profile_options
      where  upper(profile_option_name) = 'CN_UPGRADING_FROM_RELEASE'
      and    application_id = 283)
   and    level_id = 10001
   and    application_id = 283;

Line: 131

   SELECT profile_option_value
   from   fnd_profile_option_values
   where  profile_option_id =
     (select profile_option_id
      from   fnd_profile_options
      where  upper(profile_option_name) = 'CN_UPGRADING_FROM_RELEASE'
      and    application_id = 283)
   and    level_id = 10001
   and    application_id = 283;

Line: 183

   SELECT profile_option_value
   from   fnd_profile_option_values
   where  profile_option_id =
     (select profile_option_id
      from   fnd_profile_options
      where  upper(profile_option_name) = 'CN_UPGRADING_FROM_RELEASE'
      and    application_id = 283)
   and    level_id = 10001
   and    application_id = 283;

Line: 235

   SELECT profile_option_value
   from   fnd_profile_option_values
   where  profile_option_id =
     (select profile_option_id
      from   fnd_profile_options
      where  upper(profile_option_name) = 'CN_UPGRADING_FROM_RELEASE'
      and    application_id = 283)
   and    level_id = 10001
   and    application_id = 283;

Line: 287

   SELECT profile_option_value
   from   fnd_profile_option_values
   where  profile_option_id =
     (select profile_option_id
      from   fnd_profile_options
      where  upper(profile_option_name) = 'CN_UPGRADING_FROM_RELEASE'
      and    application_id = 283)
   and    level_id = 10001
   and    application_id = 283;

Line: 332

   SELECT profile_option_value
   from   fnd_profile_option_values
   where  profile_option_id =
     (select profile_option_id
      from   fnd_profile_options
      where  profile_option_name = 'CN_UPGRADING_FROM_RELEASE'
      and    application_id = 283)
   and    level_id = 10001
   and    application_id = 283;

Line: 374

   SELECT profile_option_value
   from   fnd_profile_option_values
   where  profile_option_id =
     (select profile_option_id
      from   fnd_profile_options
      where  profile_option_name = 'CN_UPGRADING_FROM_RELEASE'
      and    application_id = 283)
   and    level_id = 10001
   and    application_id = 283;

Line: 421

      l_update_name  VARCHAR2(30) := 'CNSCNUPD12.0.9';

Line: 475

    ad_parallel_updates_pkg.initialize_rowid_range(
                   ad_parallel_updates_pkg.ROWID_RANGE,
                   l_table_owner,
                   l_table_name,
                   l_update_name,
                   l_worker_id,
                   l_num_workers,
                   l_batch_size, 0);

Line: 484

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

Line: 495

            UPDATE /*+ ROWID (clp) */ cn_comm_lines_api_all clp
            SET    clp.preserve_credit_override_flag = NVL(clp.preserve_credit_override_flag,'N'),
                   clp.adjust_status = NVL(clp.adjust_status,'NEW')
            WHERE  ROWID BETWEEN l_start_rowid AND l_end_rowid
            AND    ( CLP.PRESERVE_CREDIT_OVERRIDE_FLAG IS NULL
            OR     CLP.ADJUST_STATUS IS NULL)
            AND EXISTS ( SELECT NULL
                         FROM cn_period_statuses_all status
                         WHERE  status.org_id =  clp.org_id
                         AND  clp.processed_date BETWEEN status.start_date AND status.end_date
                         AND  status.period_status = 'O'
                        );

Line: 511

            fnd_file.put_line(FND_FILE.LOG, l_rows_processed ||' rows updated ');

Line: 515

    ad_parallel_updates_pkg.processed_rowid_range(l_rows_processed,l_END_rowid);

Line: 521

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

Line: 530

   fnd_file.put_line(FND_FILE.LOG, 'Total number of Comm Lines API rows that are updated with NVL(adjust_status,NEW); NVL(PRESERVE_CREDIT_OVERRIDE_FLAG,N) = '||l_total_rows);

Line: 551

      l_update_name  VARCHAR2(30) := 'CNADSUPD12.0.9';

Line: 604

    ad_parallel_updates_pkg.initialize_rowid_range(
                   ad_parallel_updates_pkg.ROWID_RANGE,
                   l_table_owner,
                   l_table_name,
                   l_update_name,
                   l_worker_id,
                   l_num_workers,
                   l_batch_size, 0);

Line: 613

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

Line: 624

            UPDATE /*+ ROWID (cha) */ CN_COMMISSION_HEADERS_ALL cha
            SET    cha.adjust_status = NVL(cha.adjust_status,'NEW')
            WHERE  ROWID BETWEEN l_start_rowid AND l_end_rowid
            AND    cha.adjust_status IS NULL
            AND EXISTS (SELECT NULL
                        FROM cn_period_statuses_all status
                        WHERE  status.org_id =  cha.org_id
                        AND  cha.processed_date BETWEEN status.start_date AND status.end_date
                        AND  status.period_status = 'O'
                        );

Line: 638

            fnd_file.put_line(FND_FILE.LOG, l_rows_processed ||' rows updated ');

Line: 642

    ad_parallel_updates_pkg.processed_rowid_range(l_rows_processed,l_END_rowid);

Line: 648

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

Line: 657

   fnd_file.put_line(FND_FILE.LOG, 'Total number of Commission Headers rows that are updated with NVL(adjust_status,NEW) = '||l_total_rows);

Line: 677

      l_update_name     VARCHAR2(30) := 'CNSRPTXN';

Line: 697

      SELECT release_name
        FROM FND_PRODUCT_GROUPS;

Line: 723

    l_update_name := 'CNSRPTXN';

Line: 731

          l_update_name := 'CNSRPTXN_2_3';

Line: 733

          l_update_name := 'CNSRPTXN';

Line: 749

    ad_parallel_updates_pkg.initialize_rowid_range(
                   ad_parallel_updates_pkg.ROWID_RANGE,
                   l_table_owner,
                   l_table_name,
                   l_update_name,
                   l_worker_id,
                   l_num_workers,
                   l_batch_size, 0);

Line: 758

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

Line: 766

    /* For bug 10149831 - only OPEN periods records are filtered and updated with this dml
       There should be another script which takes care of the CLOSED periods - this has to be
       performed to ensure closed period when opened again has correct reference to the
       transaction_amount_ptd
    */

    WHILE (l_any_rows_to_process = TRUE) LOOP

        BEGIN

        UPDATE  /*+ ROWID(srp) */ cn_srp_period_quotas_all srp
        SET    srp.transaction_amount_ptd = (
                   SELECT NVL(SUM(ch.transaction_amount), 0)
                     FROM cn_commission_lines_all cl,
                          cn_commission_headers_all ch
                    WHERE cl.credited_salesrep_id = srp.salesrep_id
                      AND cl.processed_period_id = srp.period_id
                      AND cl.quota_id = srp.quota_id
                      AND cl.srp_plan_assign_id = srp.srp_plan_assign_id
                      AND cl.status = 'CALC'
                      AND cl.commission_header_id = ch.commission_header_id)
        WHERE srp.ROWID BETWEEN l_start_rowid AND l_end_rowid
          AND EXISTS ( SELECT null
                         FROM CN_PERIOD_STATUSES_ALL psa
                        WHERE psa.period_status = 'O'
                          AND psa.period_id = srp.period_id
                          AND psa.org_id = srp.org_id);

Line: 798

            fnd_file.put_line(FND_FILE.LOG, l_rows_processed ||' rows updated ');

Line: 802

    ad_parallel_updates_pkg.processed_rowid_range(l_rows_processed,l_END_rowid);

Line: 808

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

Line: 817

   fnd_file.put_line(FND_FILE.LOG, 'Total number of SRP Period Quotas rows that are updated to populate transaction_amount_ptd  = '||l_total_rows);