APPS.AS_STATUSES_PKG SQL Statements

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

Line: 19

procedure INSERT_ROW (
  X_ROWID in out NOCOPY VARCHAR2,
  X_STATUS_CODE in VARCHAR2,
  X_ENABLED_FLAG in VARCHAR2,
  X_LEAD_FLAG in VARCHAR2,
  X_OPP_FLAG in VARCHAR2,
  X_OPP_OPEN_STATUS_FLAG in VARCHAR2,
  X_OPP_DECISION_DATE_FLAG in VARCHAR2,
  X_STATUS_RANK in NUMBER,
  X_FORECAST_ROLLUP_FLAG in VARCHAR2,
  X_WIN_LOSS_INDICATOR in VARCHAR2,
  X_USAGE_INDICATOR in VARCHAR2,
  X_ATTRIBUTE_CATEGORY in VARCHAR2,
  X_ATTRIBUTE1 in VARCHAR2,
  X_ATTRIBUTE2 in VARCHAR2,
  X_ATTRIBUTE3 in VARCHAR2,
  X_ATTRIBUTE4 in VARCHAR2,
  X_ATTRIBUTE5 in VARCHAR2,
  X_ATTRIBUTE6 in VARCHAR2,
  X_ATTRIBUTE7 in VARCHAR2,
  X_ATTRIBUTE8 in VARCHAR2,
  X_ATTRIBUTE9 in VARCHAR2,
  X_ATTRIBUTE10 in VARCHAR2,
  X_ATTRIBUTE11 in VARCHAR2,
  X_ATTRIBUTE12 in VARCHAR2,
  X_ATTRIBUTE13 in VARCHAR2,
  X_ATTRIBUTE14 in VARCHAR2,
  X_ATTRIBUTE15 in VARCHAR2,
  X_MEANING in VARCHAR2,
  X_DESCRIPTION in VARCHAR2,
  X_CREATION_DATE in DATE,
  X_CREATED_BY in NUMBER,
  X_LAST_UPDATE_DATE in DATE,
  X_LAST_UPDATED_BY in NUMBER,
  X_LAST_UPDATE_LOGIN in NUMBER
) is
  cursor C is select ROWID from AS_STATUSES_B
    where STATUS_CODE = X_STATUS_CODE
    ;

Line: 59

  insert into AS_STATUSES_B (
    STATUS_CODE,
    ENABLED_FLAG,
    LEAD_FLAG,
    OPP_FLAG,
    OPP_OPEN_STATUS_FLAG,
    OPP_DECISION_DATE_FLAG,
    STATUS_RANK,
    FORECAST_ROLLUP_FLAG,
    WIN_LOSS_INDICATOR,
    USAGE_INDICATOR,
    ATTRIBUTE_CATEGORY,
    ATTRIBUTE1,
    ATTRIBUTE2,
    ATTRIBUTE3,
    ATTRIBUTE4,
    ATTRIBUTE5,
    ATTRIBUTE6,
    ATTRIBUTE7,
    ATTRIBUTE8,
    ATTRIBUTE9,
    ATTRIBUTE10,
    ATTRIBUTE11,
    ATTRIBUTE12,
    ATTRIBUTE13,
    ATTRIBUTE14,
    ATTRIBUTE15,
    CREATION_DATE,
    CREATED_BY,
    LAST_UPDATE_DATE,
    LAST_UPDATED_BY,
    LAST_UPDATE_LOGIN
  ) values (
    X_STATUS_CODE,
    X_ENABLED_FLAG,
    X_LEAD_FLAG,
    X_OPP_FLAG,
    X_OPP_OPEN_STATUS_FLAG,
    X_OPP_DECISION_DATE_FLAG,
    X_STATUS_RANK,
    X_FORECAST_ROLLUP_FLAG,
    X_WIN_LOSS_INDICATOR,
    X_USAGE_INDICATOR,
    X_ATTRIBUTE_CATEGORY,
    X_ATTRIBUTE1,
    X_ATTRIBUTE2,
    X_ATTRIBUTE3,
    X_ATTRIBUTE4,
    X_ATTRIBUTE5,
    X_ATTRIBUTE6,
    X_ATTRIBUTE7,
    X_ATTRIBUTE8,
    X_ATTRIBUTE9,
    X_ATTRIBUTE10,
    X_ATTRIBUTE11,
    X_ATTRIBUTE12,
    X_ATTRIBUTE13,
    X_ATTRIBUTE14,
    X_ATTRIBUTE15,
    X_CREATION_DATE,
    X_CREATED_BY,
    X_LAST_UPDATE_DATE,
    X_LAST_UPDATED_BY,
    X_LAST_UPDATE_LOGIN
  );

Line: 125

  insert into AS_STATUSES_TL (
    STATUS_CODE,
    LAST_UPDATE_DATE,
    LAST_UPDATED_BY,
    CREATION_DATE,
    CREATED_BY,
    LAST_UPDATE_LOGIN,
    MEANING,
    DESCRIPTION,
    LANGUAGE,
    SOURCE_LANG
  ) select
    X_STATUS_CODE,
    X_LAST_UPDATE_DATE,
    X_LAST_UPDATED_BY,
    X_CREATION_DATE,
    X_CREATED_BY,
    X_LAST_UPDATE_LOGIN,
    X_MEANING,
    X_DESCRIPTION,
    L.LANGUAGE_CODE,
    userenv('LANG')
  from FND_LANGUAGES L
  where L.INSTALLED_FLAG in ('I', 'B')
  and not exists
    (select NULL
    from AS_STATUSES_TL T
    where T.STATUS_CODE = X_STATUS_CODE
    and T.LANGUAGE = L.LANGUAGE_CODE);

Line: 163

end INSERT_ROW;

Line: 195

  cursor c is select
      ENABLED_FLAG,
      LEAD_FLAG,
      OPP_FLAG,
      OPP_OPEN_STATUS_FLAG,
      OPP_DECISION_DATE_FLAG,
      STATUS_RANK,
      FORECAST_ROLLUP_FLAG,
      WIN_LOSS_INDICATOR,
      USAGE_INDICATOR,
      ATTRIBUTE_CATEGORY,
      ATTRIBUTE1,
      ATTRIBUTE2,
      ATTRIBUTE3,
      ATTRIBUTE4,
      ATTRIBUTE5,
      ATTRIBUTE6,
      ATTRIBUTE7,
      ATTRIBUTE8,
      ATTRIBUTE9,
      ATTRIBUTE10,
      ATTRIBUTE11,
      ATTRIBUTE12,
      ATTRIBUTE13,
      ATTRIBUTE14,
      ATTRIBUTE15
    from AS_STATUSES_B
    where STATUS_CODE = X_STATUS_CODE
    for update of STATUS_CODE nowait;

Line: 226

  cursor c1 is select
      MEANING,
      DESCRIPTION,
      decode(LANGUAGE, userenv('LANG'), 'Y', 'N') BASELANG
    from AS_STATUSES_TL
    where STATUS_CODE = X_STATUS_CODE
    and userenv('LANG') in (LANGUAGE, SOURCE_LANG)
    for update of STATUS_CODE nowait;

Line: 239

    fnd_message.set_name('FND', 'FORM_RECORD_DELETED');

Line: 316

procedure UPDATE_ROW (
  X_STATUS_CODE in VARCHAR2,
  X_ENABLED_FLAG in VARCHAR2,
  X_LEAD_FLAG in VARCHAR2,
  X_OPP_FLAG in VARCHAR2,
  X_OPP_OPEN_STATUS_FLAG in VARCHAR2,
  X_OPP_DECISION_DATE_FLAG in VARCHAR2,
  X_STATUS_RANK in NUMBER,
  X_FORECAST_ROLLUP_FLAG in VARCHAR2,
  X_WIN_LOSS_INDICATOR in VARCHAR2,
  X_USAGE_INDICATOR in VARCHAR2,
  X_ATTRIBUTE_CATEGORY in VARCHAR2,
  X_ATTRIBUTE1 in VARCHAR2,
  X_ATTRIBUTE2 in VARCHAR2,
  X_ATTRIBUTE3 in VARCHAR2,
  X_ATTRIBUTE4 in VARCHAR2,
  X_ATTRIBUTE5 in VARCHAR2,
  X_ATTRIBUTE6 in VARCHAR2,
  X_ATTRIBUTE7 in VARCHAR2,
  X_ATTRIBUTE8 in VARCHAR2,
  X_ATTRIBUTE9 in VARCHAR2,
  X_ATTRIBUTE10 in VARCHAR2,
  X_ATTRIBUTE11 in VARCHAR2,
  X_ATTRIBUTE12 in VARCHAR2,
  X_ATTRIBUTE13 in VARCHAR2,
  X_ATTRIBUTE14 in VARCHAR2,
  X_ATTRIBUTE15 in VARCHAR2,
  X_MEANING in VARCHAR2,
  X_DESCRIPTION in VARCHAR2,
  X_LAST_UPDATE_DATE in DATE,
  X_LAST_UPDATED_BY in NUMBER,
  X_LAST_UPDATE_LOGIN in NUMBER
) is
 l_old_opp_open_status_flag VARCHAR2(1);

Line: 351

 l_module CONSTANT VARCHAR2(255) := 'as.plsql.stapk.UPDATE_ROW';

Line: 355

   SELECT opp_open_status_flag INTO l_old_opp_open_status_flag
     FROM AS_STATUSES_B
    WHERE ltrim(nls_upper(STATUS_CODE)) = nls_upper(X_STATUS_CODE);

Line: 359

  update AS_STATUSES_B set
    ENABLED_FLAG = X_ENABLED_FLAG,
    LEAD_FLAG = X_LEAD_FLAG,
    OPP_FLAG = X_OPP_FLAG,
    OPP_OPEN_STATUS_FLAG = X_OPP_OPEN_STATUS_FLAG,
    OPP_DECISION_DATE_FLAG = X_OPP_DECISION_DATE_FLAG,
    STATUS_RANK = X_STATUS_RANK,
    FORECAST_ROLLUP_FLAG = X_FORECAST_ROLLUP_FLAG,
    WIN_LOSS_INDICATOR = X_WIN_LOSS_INDICATOR,
    USAGE_INDICATOR = X_USAGE_INDICATOR,
    ATTRIBUTE_CATEGORY = X_ATTRIBUTE_CATEGORY,
    ATTRIBUTE1 = X_ATTRIBUTE1,
    ATTRIBUTE2 = X_ATTRIBUTE2,
    ATTRIBUTE3 = X_ATTRIBUTE3,
    ATTRIBUTE4 = X_ATTRIBUTE4,
    ATTRIBUTE5 = X_ATTRIBUTE5,
    ATTRIBUTE6 = X_ATTRIBUTE6,
    ATTRIBUTE7 = X_ATTRIBUTE7,
    ATTRIBUTE8 = X_ATTRIBUTE8,
    ATTRIBUTE9 = X_ATTRIBUTE9,
    ATTRIBUTE10 = X_ATTRIBUTE10,
    ATTRIBUTE11 = X_ATTRIBUTE11,
    ATTRIBUTE12 = X_ATTRIBUTE12,
    ATTRIBUTE13 = X_ATTRIBUTE13,
    ATTRIBUTE14 = X_ATTRIBUTE14,
    ATTRIBUTE15 = X_ATTRIBUTE15,
    LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
    LAST_UPDATED_BY = X_LAST_UPDATED_BY,
    LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN
  where STATUS_CODE = X_STATUS_CODE;

Line: 394

  update AS_STATUSES_TL set
    MEANING = X_MEANING,
    DESCRIPTION = X_DESCRIPTION,
    LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
    LAST_UPDATED_BY = X_LAST_UPDATED_BY,
    LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN,
    SOURCE_LANG = userenv('LANG')
  where STATUS_CODE = X_STATUS_CODE
  and userenv('LANG') in (LANGUAGE, SOURCE_LANG);

Line: 408

end UPDATE_ROW;

Line: 410

procedure DELETE_ROW (
  X_STATUS_CODE in VARCHAR2
) is
begin
  delete from AS_STATUSES_TL
  where STATUS_CODE = X_STATUS_CODE;

Line: 421

  delete from AS_STATUSES_B
  where STATUS_CODE = X_STATUS_CODE;

Line: 427

end DELETE_ROW;

Line: 468

    select 'Y'
    from AS_STATUSES_B
    where last_updated_by <> 1
    and status_code = p_STATUS_CODE;

Line: 491

      AS_STATUSES_PKG.UPDATE_ROW(
          X_STATUS_CODE               => X_STATUS_CODE,
          X_ENABLED_FLAG              => X_ENABLED_FLAG,
          X_LEAD_FLAG                 => X_LEAD_FLAG,
          X_OPP_FLAG                  => X_OPP_FLAG,
          X_OPP_OPEN_STATUS_FLAG      => X_OPP_OPEN_STATUS_FLAG,
          X_OPP_DECISION_DATE_FLAG    => X_OPP_DECISION_DATE_FLAG,
          X_STATUS_RANK               => X_STATUS_RANK,
          X_FORECAST_ROLLUP_FLAG      => X_FORECAST_ROLLUP_FLAG,
          X_WIN_LOSS_INDICATOR       => X_WIN_LOSS_INDICATOR,
          X_USAGE_INDICATOR          => X_USAGE_INDICATOR,
          X_ATTRIBUTE_CATEGORY        => X_ATTRIBUTE_CATEGORY,
          X_ATTRIBUTE1           => X_ATTRIBUTE1,
          X_ATTRIBUTE2           => X_ATTRIBUTE2,
          X_ATTRIBUTE3           => X_ATTRIBUTE3,
          X_ATTRIBUTE4           => X_ATTRIBUTE4,
          X_ATTRIBUTE5           => X_ATTRIBUTE5,
          X_ATTRIBUTE6           => X_ATTRIBUTE6,
          X_ATTRIBUTE7           => X_ATTRIBUTE7,
          X_ATTRIBUTE8           => X_ATTRIBUTE8,
          X_ATTRIBUTE9           => X_ATTRIBUTE9,
          X_ATTRIBUTE10               => X_ATTRIBUTE10,
          X_ATTRIBUTE11               => X_ATTRIBUTE11,
          X_ATTRIBUTE12               => X_ATTRIBUTE12,
          X_ATTRIBUTE13               => X_ATTRIBUTE13,
          X_ATTRIBUTE14               => X_ATTRIBUTE14,
          X_ATTRIBUTE15               => X_ATTRIBUTE15,
          X_MEANING                   => X_MEANING,
          X_DESCRIPTION               => X_DESCRIPTION,
          X_LAST_UPDATE_DATE         => sysdate,
          X_LAST_UPDATED_BY          => user_id,
          X_LAST_UPDATE_LOGIN        => 0
          );

Line: 527

      AS_STATUSES_PKG.INSERT_ROW(
       X_ROWID                       => row_id,
          X_STATUS_CODE                 => X_STATUS_CODE,
          X_ENABLED_FLAG                => X_ENABLED_FLAG,
          X_LEAD_FLAG                   => X_LEAD_FLAG,
          X_OPP_FLAG                    => X_OPP_FLAG,
          X_OPP_OPEN_STATUS_FLAG        => X_OPP_OPEN_STATUS_FLAG,
          X_OPP_DECISION_DATE_FLAG      => X_OPP_DECISION_DATE_FLAG,
          X_STATUS_RANK                 => X_STATUS_RANK,
          X_FORECAST_ROLLUP_FLAG        => X_FORECAST_ROLLUP_FLAG,
          X_WIN_LOSS_INDICATOR          => X_WIN_LOSS_INDICATOR,
          X_USAGE_INDICATOR             => X_USAGE_INDICATOR,
          X_ATTRIBUTE_CATEGORY          => X_ATTRIBUTE_CATEGORY,
          X_ATTRIBUTE1             => X_ATTRIBUTE1,
          X_ATTRIBUTE2             => X_ATTRIBUTE2,
          X_ATTRIBUTE3             => X_ATTRIBUTE3,
          X_ATTRIBUTE4             => X_ATTRIBUTE4,
          X_ATTRIBUTE5             => X_ATTRIBUTE5,
          X_ATTRIBUTE6             => X_ATTRIBUTE6,
          X_ATTRIBUTE7             => X_ATTRIBUTE7,
          X_ATTRIBUTE8             => X_ATTRIBUTE8,
          X_ATTRIBUTE9             => X_ATTRIBUTE9,
          X_ATTRIBUTE10                 => X_ATTRIBUTE10,
          X_ATTRIBUTE11                 => X_ATTRIBUTE11,
          X_ATTRIBUTE12                 => X_ATTRIBUTE12,
          X_ATTRIBUTE13                 => X_ATTRIBUTE13,
          X_ATTRIBUTE14                 => X_ATTRIBUTE14,
          X_ATTRIBUTE15                 => X_ATTRIBUTE15,
          X_MEANING                     => X_MEANING,
          X_DESCRIPTION                 => X_DESCRIPTION,
       X_CREATION_DATE               => sysdate,
       X_CREATED_BY                  => 0,
          X_LAST_UPDATE_DATE           => sysdate,
          X_LAST_UPDATED_BY            => user_id,
          X_LAST_UPDATE_LOGIN          => 0
      );

Line: 574

  delete from AS_STATUSES_TL T
  where not exists
    (select NULL
    from AS_STATUSES_B B
    where B.STATUS_CODE = T.STATUS_CODE
    );

Line: 581

  update AS_STATUSES_TL T set (
      MEANING,
      DESCRIPTION
    ) = (select
      B.MEANING,
      B.DESCRIPTION
    from AS_STATUSES_TL B
    where B.STATUS_CODE = T.STATUS_CODE
    and B.LANGUAGE = T.SOURCE_LANG)
  where (
      T.STATUS_CODE,
      T.LANGUAGE
  ) in (select
      SUBT.STATUS_CODE,
      SUBT.LANGUAGE
    from AS_STATUSES_TL SUBB, AS_STATUSES_TL SUBT
    where SUBB.STATUS_CODE = SUBT.STATUS_CODE
    and SUBB.LANGUAGE = SUBT.SOURCE_LANG
    and (SUBB.MEANING <> SUBT.MEANING
      or (SUBB.MEANING is null and SUBT.MEANING is not null)
      or (SUBB.MEANING is not null and SUBT.MEANING is null)
      or SUBB.DESCRIPTION <> SUBT.DESCRIPTION
      or (SUBB.DESCRIPTION is null and SUBT.DESCRIPTION is not null)
      or (SUBB.DESCRIPTION is not null and SUBT.DESCRIPTION is null)
  ));

Line: 607

  insert into AS_STATUSES_TL (
    STATUS_CODE,
    LAST_UPDATE_DATE,
    LAST_UPDATED_BY,
    CREATION_DATE,
    CREATED_BY,
    LAST_UPDATE_LOGIN,
    MEANING,
    DESCRIPTION,
    LANGUAGE,
    SOURCE_LANG
  ) select
    B.STATUS_CODE,
    B.LAST_UPDATE_DATE,
    B.LAST_UPDATED_BY,
    B.CREATION_DATE,
    B.CREATED_BY,
    B.LAST_UPDATE_LOGIN,
    B.MEANING,
    B.DESCRIPTION,
    L.LANGUAGE_CODE,
    B.SOURCE_LANG
  from AS_STATUSES_TL B, FND_LANGUAGES L
  where L.INSTALLED_FLAG in ('I', 'B')
  and B.LANGUAGE = userenv('LANG')
  and not exists
    (select NULL
    from AS_STATUSES_TL T
    where T.STATUS_CODE = B.STATUS_CODE
    and T.LANGUAGE = L.LANGUAGE_CODE);

Line: 648

    update AS_STATUSES_TL
      set description = X_DESCRIPTION,
         meaning = X_MEANING,
         source_lang = userenv('LANG'),
         last_update_date = sysdate,
         last_updated_by = decode(X_OWNER, 'SEED', 1, 0),
         last_update_login = 0
         where status_code = X_STATUS_CODE
        and userenv('LANG') in (language, source_lang);

Line: 662

PROCEDURE PRE_UPDATE(
  ERRBUF   OUT  NOCOPY  VARCHAR2,
  RETCODE  OUT  NOCOPY  VARCHAR2) IS
  l_status BOOLEAN;

Line: 666

  l_module CONSTANT VARCHAR2(255) := 'as.plsql.stapk.PRE_UPDATE';

Line: 673

    Create_Temp_Index('AS_ACCESSES_ALL_ALL','ACCESS_ID,DELETE_FLAG,SALES_LEAD_ID',G_INDEX_SUFFIX1);

Line: 677

    Create_Temp_Index('AS_ACCESSES_ALL_ALL','ACCESS_ID,DELETE_FLAG,LEAD_ID',G_INDEX_SUFFIX2);

Line: 690

END PRE_UPDATE;

Line: 692

PROCEDURE POST_UPDATE(
  ERRBUF   OUT  NOCOPY  VARCHAR2,
  RETCODE  OUT  NOCOPY  VARCHAR2) IS
BEGIN
 -- Drop temporary index for Leads
 Drop_Temp_Index('AS_SALES_LEADS',G_INDEX_SUFFIX1);

Line: 705

END POST_UPDATE;

Line: 745

       SELECT MIN(TO_NUMBER(v.value))
       INTO l_dop
       FROM v$parameter v
       WHERE v.name = 'parallel_max_servers'
       OR v.name = 'cpu_count';

Line: 807

PROCEDURE update_accesses_Main
          (
           errbuf OUT NOCOPY VARCHAR2,
           retcode OUT NOCOPY NUMBER,
           x_open_flag   IN VARCHAR2,
           x_status_code IN VARCHAR2,
           p_num_workers IN NUMBER,
           p_batch_size  IN NUMBER,
           p_debug_flag  IN VARCHAR2
          )
IS
  l_api_name                     CONSTANT VARCHAR2(30) :=
    'update_accesses_Main';

Line: 821

    'as.plsql.as_statuses_pkg.update_accesses_Main';

Line: 833

  select AS_ACCESSES_S.nextval
  from dual;

Line: 837

  select  min(access_id)
  from    as_accesses_all_all;

Line: 1079

END update_accesses_Main;

Line: 1083

procedure UPDATE_ACCESSES_SUB(
   ERRBUF   	OUT NOCOPY   VARCHAR2,
   RETCODE  	OUT NOCOPY   VARCHAR2,
   x_open_flag   IN VARCHAR2,
   x_status_code IN VARCHAR2,
   p_start_id 	 IN VARCHAR2,
   p_end_id 	 IN VARCHAR2,
   p_batch_size  IN NUMBER,
   p_debug_flag  IN VARCHAR2
)
IS
    l_count             NUMBER    := 0;

Line: 1098

    l_last_updated_by   NUMBER    := fnd_global.user_id;

Line: 1099

    l_last_update_login NUMBER    := fnd_global.conc_login_id;

Line: 1107

    select NVL(LEAD_FLAG,'N'),NVL(OPP_FLAG,'N')
      from AS_STATUSES_B
     where status_code = c_in_param_2
       and UPPER(opp_open_status_flag) = UPPER(c_in_param_1);

Line: 1112

l_module CONSTANT VARCHAR2(255) := 'as.plsql.stapk.UPDATE_ACCESSES_SUB';

Line: 1163

          UPDATE /*+ INDEX(acc AS_ACCESSES_ALL_ALL_MT1)*/ AS_ACCESSES_ALL acc
             SET object_version_number =  nvl(object_version_number,0) + 1,
                         acc.OPEN_FLAG = Upper(X_OPEN_FLAG),
                         acc.last_update_date = sysdate,
                         acc.last_updated_by = l_last_updated_by,
                         acc.last_update_login = l_last_update_login
           WHERE acc.ACCESS_ID >= l_count
             AND acc.ACCESS_ID < l_count+G_BATCH_SIZE
              AND acc.ACCESS_ID < l_max_id
             AND (l_lead_flag = 'Y' AND EXISTS
                                    ( SELECT /*+ INDEX(slds AS_SALES_LEADS_MT1)*/ 1
                                        FROM AS_SALES_LEADS slds
                                       WHERE slds.sales_lead_id = acc.sales_lead_id
                                         AND slds.status_code  = X_STATUS_CODE
                                         AND acc.sales_lead_id is not null )
                 )
             AND acc.sales_lead_id is not null;

Line: 1182

          UPDATE /*+ INDEX(acc AS_ACCESSES_ALL_ALL_MT2)*/ AS_ACCESSES_ALL acc
             SET object_version_number =  nvl(object_version_number,0) + 1,
                         acc.OPEN_FLAG = Upper(X_OPEN_FLAG),
                         acc.last_update_date = sysdate,
                         acc.last_updated_by = l_last_updated_by,
                         acc.last_update_login = l_last_update_login
           WHERE acc.ACCESS_ID >= l_count
             AND acc.ACCESS_ID < l_count+G_BATCH_SIZE
             AND acc.ACCESS_ID < l_max_id
             AND (l_opp_flag = 'Y' AND EXISTS
                                    ( SELECT /*+ INDEX(lds AS_LEADS_ALL_MT2)*/ 1
                                        FROM AS_LEADS_ALL lds
                                       WHERE lds.lead_id = acc.lead_id
                                         AND lds.status  = X_STATUS_CODE
                                         AND acc.lead_id is not null ))
             AND acc.lead_id is not null;

Line: 1200

          UPDATE /*+ INDEX(acc AS_ACCESSES_ALL_ALL_MT1)*/ AS_ACCESSES_ALL acc
             SET object_version_number =  nvl(object_version_number,0) + 1,
                         acc.OPEN_FLAG = (SELECT /*+ INDEX(slds AS_SALES_LEADS_MT1)*/ st.opp_open_status_flag
                                              FROM AS_STATUSES_B st,AS_SALES_LEADS slds
                                             WHERE st.status_code = slds.status_code
                                               AND slds.sales_lead_id = acc.sales_lead_id
                                               AND acc.sales_lead_id is not null
                                               AND st.lead_flag = 'Y'),
                         acc.last_update_date = sysdate,
                         acc.last_updated_by = l_last_updated_by,
                         acc.last_update_login = l_last_update_login
           WHERE acc.ACCESS_ID >= l_count
             AND acc.ACCESS_ID < l_count+G_BATCH_SIZE
             AND acc.ACCESS_ID < l_max_id
             AND acc.sales_lead_id is not null;

Line: 1218

          UPDATE /*+ INDEX(acc AS_ACCESSES_ALL_ALL_MT2)*/ AS_ACCESSES_ALL acc
             SET object_version_number =  nvl(object_version_number,0) + 1,
                         acc.OPEN_FLAG = (SELECT /*+ INDEX(lds AS_LEADS_ALL_MT2)*/ st.opp_open_status_flag
                                              FROM AS_STATUSES_B st,AS_LEADS_ALL lds
                                             WHERE st.status_code = lds.status
                                               AND lds.lead_id = acc.lead_id
                                               AND acc.lead_id is not null
                                               AND st.opp_flag = 'Y'),
                         acc.last_update_date = sysdate,
                         acc.last_updated_by = l_last_updated_by,
                         acc.last_update_login = l_last_update_login
           WHERE acc.ACCESS_ID >= l_count
             AND acc.ACCESS_ID < l_count+G_BATCH_SIZE
             AND acc.ACCESS_ID < l_max_id
             AND acc.lead_id is not null;

Line: 1243

        FND_FILE.PUT_LINE(FND_FILE.LOG,'Update of denormed open flag in as_accesses_all_all (for leads and opps) finished successfully');

Line: 1254

         Write_log (l_module, 1, 'Error in as_statuses_pkg.update_oppty_accesses');

Line: 1265

         Write_Log(l_module, 1, 'Unexpected error in as_statuses_pkg.update_oppty_accesses');

Line: 1274

        Write_Log(l_module, 1, 'Exception: others in as_statuses_pkg.update_oppty_accesses');

Line: 1281

end UPDATE_ACCESSES_SUB;

Line: 1286

PROCEDURE update_leads_Main
          (
           errbuf OUT NOCOPY VARCHAR2,
           retcode OUT NOCOPY NUMBER,
           x_open_flag   IN VARCHAR2,
           x_status_code IN VARCHAR2,
           p_num_workers IN NUMBER,
           p_batch_size  IN NUMBER,
           p_debug_flag  IN VARCHAR2
          )
IS
  l_api_name                     CONSTANT VARCHAR2(30) :=
    'update_leads_Main';

Line: 1300

    'as.plsql.as_statuses_pkg.update_leads_Main';

Line: 1312

  select AS_SALES_LEADS_S.nextval
  from dual;

Line: 1316

  select  min(sales_lead_id)
  from    as_sales_leads;

Line: 1557

END update_leads_Main;

Line: 1559

procedure UPDATE_LEADS_ACCESSES(ERRBUF   OUT NOCOPY   VARCHAR2,
   RETCODE  OUT NOCOPY   VARCHAR2,
   x_open_flag   IN VARCHAR2,
   x_status_code IN VARCHAR2,
   p_start_id 	 IN VARCHAR2,
   p_end_id 	 IN VARCHAR2,
   p_batch_size  IN NUMBER,
   p_debug_flag  IN VARCHAR2)
IS
    l_count  NUMBER := 0;

Line: 1572

    l_last_updated_by NUMBER:= fnd_global.user_id;

Line: 1573

    l_last_update_login NUMBER:= fnd_global.conc_login_id;

Line: 1581

    select  min(sales_lead_id)
    from  as_sales_leads;

Line: 1585

    select AS_SALES_LEADS_S.nextval
    from dual;

Line: 1589

    select NVL(LEAD_FLAG,'N')
      from AS_STATUSES_B
     where status_code = c_in_param_2
       and UPPER(opp_open_status_flag) = UPPER(c_in_param_1);

Line: 1594

l_module CONSTANT VARCHAR2(255) := 'as.plsql.stapk.UPDATE_LEADS_ACCESSES';

Line: 1652

              UPDATE /*+ INDEX(sld AS_SALES_LEADS_MT1)*/ AS_SALES_LEADS sld
                     SET (sld.STATUS_OPEN_FLAG , sld.CLOSE_REASON) =
                             (SELECT opp_open_status_flag,
                                 DECODE(opp_open_status_flag,'Y',NULL,'N',
                                        DECODE(st.status_code,'CONVERTED_TO_OPPORTUNITY','CONVERTED_TO_OPPORTUNITY',
                                                NVL(sld.close_reason,'NOT_SPECIFIED')
                                                )
                                        )
                            FROM AS_STATUSES_B st
                               WHERE st.status_code = sld.status_code
                                 AND st.lead_flag = 'Y')
                       , sld.last_update_date = sysdate
                       , sld.last_updated_by = l_last_updated_by
                       , sld.last_update_login = l_last_update_login
                   WHERE sld.sales_lead_id >= l_count
                     AND sld.sales_lead_id < l_count+G_BATCH_SIZE
                     AND sld.sales_lead_id < l_max_id
                     AND sld.status_code is not null;

Line: 1672

                 UPDATE /*+ INDEX(sld AS_SALES_LEADS_MT1)*/ AS_SALES_LEADS sld
                    SET sld.STATUS_OPEN_FLAG = Upper(X_OPEN_FLAG)
                      , sld.CLOSE_REASON = DECODE(Upper(X_OPEN_FLAG),'Y',NULL,'N',
                                                 DECODE(X_STATUS_CODE,'CONVERTED_TO_OPPORTUNITY','CONVERTED_TO_OPPORTUNITY',
                                                        NVL(sld.close_reason,'NOT_SPECIFIED')
                                                       )
                                                  )
                      , sld.last_update_date = sysdate
                      , sld.last_updated_by = l_last_updated_by
                      , sld.last_update_login = l_last_update_login
                  WHERE sld.sales_lead_id >= l_count
                    AND sld.sales_lead_id < l_count+G_BATCH_SIZE
                    AND sld.sales_lead_id < l_max_id
                    AND sld.STATUS_CODE = X_STATUS_CODE
                    AND sld.status_code is not null;

Line: 1696

             FND_FILE.PUT_LINE(FND_FILE.LOG,'Update of denormed open flag in sales leads finished successfully');

Line: 1708

         Write_log (l_module, 1, 'Error in as_statuses_pkg.update_leads_accesses');

Line: 1719

         Write_Log(l_module, 1, 'Unexpected error in as_statuses_pkg.update_leads_accesses');

Line: 1728

        Write_Log(l_module, 1, 'Exception: others in as_statuses_pkg.update_leads_accesses');

Line: 1735

end UPDATE_LEADS_ACCESSES;

Line: 1738

PROCEDURE update_oppty_Main
          (
           errbuf OUT NOCOPY VARCHAR2,
           retcode OUT NOCOPY NUMBER,
           x_open_flag   IN VARCHAR2,
           x_status_code IN VARCHAR2,
           p_num_workers IN NUMBER,
           p_batch_size  IN NUMBER,
           p_debug_flag  IN VARCHAR2
          )
IS
  l_api_name                     CONSTANT VARCHAR2(30) :=
    'update_oppty_Main';

Line: 1752

    'as.plsql.as_statuses_pkg.update_oppty_Main';

Line: 1764

  select AS_LEADS_S.nextval
  from dual;

Line: 1768

  select  min(lead_id)
  from    as_leads_all;

Line: 2009

END update_oppty_Main;

Line: 2012

procedure UPDATE_OPPTY_ACCESSES(ERRBUF   OUT NOCOPY   VARCHAR2,
   RETCODE  OUT NOCOPY   VARCHAR2,
   x_open_flag   IN VARCHAR2,
   x_status_code IN VARCHAR2,
   p_start_id 	 IN VARCHAR2,
   p_end_id 	 IN VARCHAR2,
   p_batch_size  IN NUMBER,
   p_debug_flag  IN VARCHAR2)
IS
    l_count  NUMBER := 0;

Line: 2025

    l_last_updated_by NUMBER:= fnd_global.user_id;

Line: 2026

    l_last_update_login NUMBER:= fnd_global.conc_login_id;

Line: 2033

    select  min(lead_id)
    from  as_leads_all;

Line: 2037

    select AS_LEADS_S.nextval
    from dual;

Line: 2042

    select NVL(OPP_FLAG,'N')
      from AS_STATUSES_B
     where status_code = c_in_param_2
       and UPPER(opp_open_status_flag) = UPPER(c_in_param_1);

Line: 2047

l_module CONSTANT VARCHAR2(255) := 'as.plsql.stapk.UPDATE_OPPTY_ACCESSES';

Line: 2101

                 execute immediate 'UPDATE /*+ INDEX(ld AS_LEADS_ALL_MT2)*/ AS_LEADS_ALL ld
                     SET ld.close_reason = (DECODE((SELECT opp_open_status_flag
                                          FROM AS_STATUSES_B st
                                                     WHERE st.status_code = ld.status
                                                       AND st.opp_flag = ''Y''),''Y'',NULL,''N'',nvl(ld.CLOSE_REASON,''NOT_SPECIFIED'')))
                       , ld.last_update_date = sysdate
                       , ld.last_updated_by = :l_last_updated_by
                       , ld.last_update_login = :l_last_update_login
                   WHERE ld.lead_id >= :l_count
                     AND ld.lead_id < :l_count1+:G_BATCH_SIZE
                     AND ld.lead_id < :l_max_id
                     AND ld.status is not null' using l_last_updated_by,l_last_update_login,l_count,l_count,G_BATCH_SIZE,l_max_id ;

Line: 2114

                 execute immediate 'UPDATE /*+ INDEX(ld AS_LEADS_ALL_MT2)*/ AS_LEADS_ALL ld
                    SET ld.close_reason = null
                      , ld.last_update_date = sysdate
                      , ld.last_updated_by = :l_last_updated_by
                      , ld.last_update_login = :l_last_update_login
                  WHERE ld.lead_id >= :l_count
                    AND ld.lead_id < :l_count1+:G_BATCH_SIZE
                    AND ld.lead_id < :l_max_id
                    AND ld.status = :X_STATUS_CODE
                    AND ld.status is not null
                    AND ld.close_reason is not null' using l_last_updated_by,l_last_update_login,l_count,l_count,G_BATCH_SIZE,l_max_id,X_STATUS_CODE ;

Line: 2126

                 execute immediate 'UPDATE /*+ INDEX(ld AS_LEADS_ALL_MT2)*/ AS_LEADS_ALL ld
                    SET ld.close_reason = ''NOT_SPECIFIED''
                      , ld.last_update_date = sysdate
                      , ld.last_updated_by = :l_last_updated_by
                      , ld.last_update_login = :l_last_update_login
                  WHERE ld.lead_id >= :l_count
                    AND ld.lead_id < :l_count1+:G_BATCH_SIZE
                    AND ld.lead_id < :l_max_id
                    AND ld.status = :X_STATUS_CODE
                    AND ld.status is not null
                    AND ld.close_reason is null' using l_last_updated_by,l_last_update_login,l_count,l_count,G_BATCH_SIZE,l_max_id,X_STATUS_CODE ;

Line: 2146

             FND_FILE.PUT_LINE(FND_FILE.LOG,'Update of close reason in opptys finished successfully');

Line: 2158

         Write_log (l_module, 1, 'Error in as_statuses_pkg.update_oppty_accesses');

Line: 2169

         Write_Log(l_module, 1, 'Unexpected error in as_statuses_pkg.update_oppty_accesses');

Line: 2178

        Write_Log(l_module, 1, 'Exception: others in as_statuses_pkg.update_oppty_accesses');

Line: 2185

end UPDATE_OPPTY_ACCESSES;