APPS.EGO_LIFECYCLE_USER_PUB SQL Statements

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

Line: 49

      SELECT COUNT(1) INTO l_count
        FROM ego_item_projects
       WHERE inventory_item_id = p_inventory_item_id
         AND organization_id = p_organization_id
         AND revision_id IS NULL
         AND association_type = G_PROJECT_ASSOC_TYPE
         AND association_code = G_LIFECYCLE_TRACKING_CODE
         AND ROWNUM = 1;

Line: 60

      SELECT COUNT(1) INTO l_count
        FROM EGO_ITEM_PROJECTS a
       WHERE inventory_item_id = p_inventory_item_id
         AND organization_id = p_organization_id
         AND EXISTS
             (SELECT revision_id
                FROM mtl_item_revisions_b
               WHERE inventory_item_id = p_inventory_item_id
                 AND organization_id   = p_organization_id
                 AND revision          = p_revision
              )
         AND association_type = G_PROJECT_ASSOC_TYPE
         AND association_code = G_LIFECYCLE_TRACKING_CODE
         AND ROWNUM = 1;

Line: 108

    SELECT inst.status
    FROM   fnd_product_installations inst, fnd_application app
    WHERE  inst.application_id = app.application_id
      AND  app.application_short_name = cp_app_short_name
      AND  inst.status <> 'N';*/

Line: 122

      SELECT count(*)
      INTO  l_count
      FROM eng_change_mgmt_types_vl
      WHERE disable_flag = 'N';

Line: 166

      l_dynamic_sql := ' SELECT change_notice FROM eng_engineering_changes'
                     ||' WHERE change_id = :p_change_id' ; --||TO_CHAR(p_change_id);

Line: 179

PROCEDURE Check_Delete_Project_OK
(
     p_api_version             IN      NUMBER
   , p_project_id              IN      NUMBER
   , p_init_msg_list           IN      VARCHAR2   DEFAULT FND_API.G_FALSE
   , x_delete_ok               OUT     NOCOPY VARCHAR2
   , x_return_status           OUT     NOCOPY VARCHAR2
   , x_errorcode               OUT     NOCOPY NUMBER
   , x_msg_count               OUT     NOCOPY NUMBER
   , x_msg_data                OUT     NOCOPY VARCHAR2
)
IS

    l_api_version  NUMBER;

Line: 200

    l_api_name     := 'Check_Delete_Project_OK';

Line: 216

    SELECT
      COUNT(*) INTO l_count
    FROM
      EGO_ITEM_PROJECTS
    WHERE
      PROJECT_ID = p_project_id;

Line: 225

      x_delete_ok := FND_API.G_FALSE;

Line: 245

      x_delete_ok := FND_API.G_FALSE;

Line: 258

END Check_Delete_Project_OK;

Line: 349

     SELECT ic.item_catalog_group_id
       FROM MTL_ITEM_CATALOG_GROUPS_B ic
      WHERE EXISTS (
              SELECT olc.object_classification_code CatalogId
                FROM  ego_obj_type_lifecycles olc, fnd_objects o
               WHERE o.obj_name =  'EGO_ITEM'
                 AND olc.object_id = o.object_id
                 AND olc.lifecycle_id = cp_lifecycle_id
                 AND olc.object_classification_code = item_catalog_group_id
                   )
     CONNECT BY PRIOR parent_catalog_group_id = item_catalog_group_id
     START WITH item_catalog_group_id = cp_catalog_category_id;

Line: 383

      SELECT
        P1.DISPLAY_SEQUENCE INTO l_curr_sequence
      FROM
        PA_PROJ_ELEMENT_VERSIONS P1
       ,PA_PROJ_ELEMENT_VERSIONS P2
      WHERE
        P1.PROJ_ELEMENT_ID = p_curr_phase_id
        AND P1.PARENT_STRUCTURE_VERSION_ID = P2.ELEMENT_VERSION_ID
        AND P2.PROJ_ELEMENT_ID = p_lifecycle_id;

Line: 393

      SELECT P1.DISPLAY_SEQUENCE INTO l_future_sequence
      FROM
        PA_PROJ_ELEMENT_VERSIONS P1
       ,PA_PROJ_ELEMENT_VERSIONS P2
      WHERE
        P1.PROJ_ELEMENT_ID = p_future_phase_id
        AND P1.PARENT_STRUCTURE_VERSION_ID = P2.ELEMENT_VERSION_ID
        AND P2.PROJ_ELEMENT_ID = p_lifecycle_id;

Line: 422

      SELECT
        INVENTORY_ITEM_ID, ORGANIZATION_ID
      INTO
        l_inventory_item_id, l_organization_id
      FROM
        EGO_ITEM_PROJECTS
      WHERE
        PROJECT_ID = p_project_id
        AND ASSOCIATION_TYPE = g_project_assoc_type
        AND ASSOCIATION_CODE = g_lifecycle_tracking_code
        AND ROWNUM = 1;

Line: 438

    SELECT
      ITEM_CATALOG_GROUP_ID into l_current_catalog_category_id
    FROM
      MTL_SYSTEM_ITEMS_B
    WHERE
      ORGANIZATION_ID = l_organization_id
      AND INVENTORY_ITEM_ID = l_inventory_item_id;

Line: 509

    SELECT
      POLICY_CODE INTO x_policy_code
    FROM
      EGO_LCPHASE_POLICY
    WHERE
      PHASE_ID = p_curr_phase_id
      AND ACTION_CODE = l_phase_change_code
      AND LIFECYCLE_ID = p_lifecycle_id
      AND ITEM_CATALOG_GROUP_ID = l_catalog_category_id;

Line: 623

  SELECT COUNT(1) INTO l_count
    FROM EGO_ITEM_PROJECTS
   WHERE PROJECT_ID = p_project_id
     AND ASSOCIATION_TYPE = g_project_assoc_type
     AND ASSOCIATION_CODE = g_lifecycle_tracking_code
     AND ROWNUM = 1;

Line: 654

PROCEDURE Delete_All_Item_Assocs
 (
     p_api_version             IN     NUMBER
   , p_project_id              IN     NUMBER
   , p_commit                  IN     VARCHAR2  DEFAULT fnd_api.g_FALSE
   , x_return_status           OUT    NOCOPY VARCHAR2
   , x_errorcode               OUT    NOCOPY NUMBER
   , x_msg_count               OUT    NOCOPY NUMBER
   , x_msg_data                OUT    NOCOPY VARCHAR2
)
IS
  l_api_version  NUMBER;

Line: 669

    SELECT inventory_item_id, organization_id
    FROM  ego_item_projects
    WHERE project_id = cp_project_id;

Line: 676

  l_api_name     := 'Delete_All_Item_Assocs';

Line: 707

  DELETE
    FROM EGO_ITEM_PROJECTS
   WHERE PROJECT_ID = p_project_id;

Line: 735

END Delete_All_Item_Assocs;

Line: 774

    SELECT
      inventory_item_id
     ,organization_id
--     ,revision
     ,revision_id
    FROM EGO_ITEM_PROJECTS proj
    WHERE project_id = p_project_id
      AND association_type = G_PROJECT_ASSOC_TYPE
      AND association_code = G_LIFECYCLE_TRACKING_CODE
--  sync phase changes of items which are not in the same phase of project
      AND ( (revision_id IS NULL
             AND NOT EXISTS
               (SELECT 'X'
                FROM mtl_system_items_b item
                WHERE item.inventory_item_id = proj.inventory_item_id
                  AND item.organization_id = proj.organization_id
                  AND item.lifecycle_id = p_lifecycle_id
                  AND item.current_phase_id = p_phase_id
                )
             )
             OR
             (revision_id IS NOT NULL
             AND NOT EXISTS
               (SELECT 'X'
                FROM mtl_item_revisions_b rev
                WHERE rev.inventory_item_id = proj.inventory_item_id
                  AND rev.organization_id = proj.organization_id
                  AND rev.revision_id = proj.revision_id
                  AND rev.lifecycle_id = p_lifecycle_id
                  AND rev.current_phase_id = p_phase_id
                )
             )
          );

Line: 842

        SELECT msi.INVENTORY_ITEM_STATUS_CODE
          INTO l_status_code
          FROM MTL_SYSTEM_ITEMS_B msi
         WHERE msi.INVENTORY_ITEM_ID = l_item_record.INVENTORY_ITEM_ID
           AND msi.ORGANIZATION_ID = l_item_record.ORGANIZATION_ID;

Line: 849

          SELECT status.ITEM_STATUS_CODE
            INTO l_status_code
            FROM EGO_LCPHASE_ITEM_STATUS status
                ,PA_EGO_PHASES_V phases
           WHERE phases.PROJ_ELEMENT_ID = p_phase_id
             AND status.PHASE_CODE = phases.PHASE_CODE
             AND status.ITEM_STATUS_CODE = l_status_code;

Line: 1107

      SELECT
        CURRENT_PHASE_ID INTO l_current_phase_id
      FROM
        MTL_SYSTEM_ITEMS_B
      WHERE
        INVENTORY_ITEM_ID = p_inventory_item_id
        AND ORGANIZATION_ID = p_organization_id;

Line: 1117

      SELECT
--Bug: 2871650 getting Current phase id to compare
        REVISION_ID, CURRENT_PHASE_ID INTO l_revision_id, l_current_phase_id
      FROM
        MTL_ITEM_REVISIONS_B
      WHERE
        INVENTORY_ITEM_ID = p_inventory_item_id
        AND ORGANIZATION_ID = p_organization_id
        AND REVISION = p_revision;

Line: 1135

        SELECT PHASE_CODE INTO l_phase_code
        FROM PA_EGO_LIFECYCLES_PHASES_V
        WHERE PROJ_ELEMENT_ID = p_phase_id;

Line: 1141

          SELECT ITEM_STATUS_CODE INTO l_status_code
          FROM
            EGO_LCPHASE_ITEM_STATUS
          WHERE
            PHASE_CODE = l_phase_code
            AND DEFAULT_FLAG = 'Y';

Line: 1156

      INSERT INTO MTL_PENDING_ITEM_STATUS
      (
         INVENTORY_ITEM_ID
        ,ORGANIZATION_ID
        ,STATUS_CODE
        ,EFFECTIVE_DATE
        ,PENDING_FLAG
        ,LAST_UPDATE_DATE
        ,LAST_UPDATED_BY
        ,CREATION_DATE
        ,CREATED_BY
        ,LAST_UPDATE_LOGIN
        ,LIFECYCLE_ID
        ,PHASE_ID
        ,REVISION_ID
      )
      VALUES
      (
         p_inventory_item_id
        ,p_organization_id
        ,l_status_code
        ,NVL(p_effective_date,SYSDATE)
        ,'Y'
        ,SYSDATE
        ,g_current_login_id
        ,SYSDATE
        ,g_current_login_id
        ,g_current_login_id
        ,p_lifecycle_id
        ,p_phase_id
        ,l_revision_id
      );

Line: 1191

      SELECT DECODE(LOOKUP_CODE2, 1, FND_API.G_TRUE, 2, FND_API.G_FALSE, FND_API.G_FALSE) INTO l_status_master_controlled
      FROM MTL_ITEM_ATTRIBUTES_V
      WHERE ATTRIBUTE_NAME = 'MTL_SYSTEM_ITEMS.INVENTORY_ITEM_STATUS_CODE';

Line: 1197

      SELECT
        DECODE(MP.ORGANIZATION_ID,MP.MASTER_ORGANIZATION_ID, FND_API.G_TRUE, FND_API.G_FALSE) INTO l_is_master_org
      FROM MTL_PARAMETERS MP
      WHERE MP.ORGANIZATION_ID = p_organization_id;

Line: 1357

        DELETE
          FROM EGO_ITEM_PROJECTS
         WHERE INVENTORY_ITEM_ID = p_inventory_item_id
           AND ORGANIZATION_ID = p_organization_id
           AND REVISION_ID IS NULL
           AND ASSOCIATION_TYPE = g_project_assoc_type
           AND ASSOCIATION_CODE = g_lifecycle_tracking_code;

Line: 1374

        DELETE
          FROM EGO_ITEM_PROJECTS
         WHERE INVENTORY_ITEM_ID = p_inventory_item_id
           AND ORGANIZATION_ID = p_organization_id
           AND REVISION_id = l_revision_id
           AND ASSOCIATION_TYPE = g_project_assoc_type
           AND ASSOCIATION_CODE = g_lifecycle_tracking_code;

Line: 1385

    SELECT EGO_ITEM_PROJECTS_S.NEXTVAL
      INTO l_item_project_id
      FROM DUAL;

Line: 1395

    INSERT INTO
    EGO_ITEM_PROJECTS
    (
      ITEM_PROJECT_ID
     ,INVENTORY_ITEM_ID
     ,ORGANIZATION_ID
--     ,REVISION
     ,REVISION_ID
     ,PROJECT_ID
     ,TASK_ID
     ,ASSOCIATION_TYPE
     ,ASSOCIATION_CODE
     ,CREATED_BY
     ,CREATION_DATE
     ,LAST_UPDATED_BY
     ,LAST_UPDATE_DATE
     ,LAST_UPDATE_LOGIN
    )
    VALUES
    (
      l_item_project_id
     ,p_inventory_item_id
     ,p_organization_id
--     ,p_revision
     ,l_revision_id
     ,p_project_id
     ,p_task_id
     ,p_association_type
     ,p_association_code
     ,G_CURRENT_USER_ID
     ,SYSDATE
     ,G_CURRENT_USER_ID
     ,SYSDATE
     ,G_CURRENT_LOGIN_ID
    );

Line: 1567

  SELECT project_id, task_id
    FROM ego_item_projects
   WHERE inventory_item_id = cp_inventory_item_id
     AND organization_id   = cp_organization_id
     AND NVL(revision_id,-1) = NVL(cp_revision_id,-1)  -- -1 is not a valid revision_id
     AND association_type  = cp_association_type
     AND association_code  = cp_association_code ;

Line: 1740

    SELECT DISTINCT ORGANIZATION_ID
                   ,INVENTORY_ITEM_ID
                   ,REVISION_ID
                   ,ASSOCIATION_TYPE
                   ,ASSOCIATION_CODE
               FROM EGO_ITEM_PROJECTS
              WHERE PROJECT_ID = cp_project_id;