APPS.ENI_UPD_ASSGN SQL Statements

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

Line: 6

PROCEDURE UPDATE_ASSGN_FLAG(
      p_new_category_id  IN NUMBER,
      p_old_category_id  IN NUMBER,
      x_return_status    OUT NOCOPY VARCHAR2,
      x_msg_count        OUT NOCOPY NUMBER,
      x_msg_data         OUT NOCOPY VARCHAR2) IS

  l_user_id          NUMBER := FND_GLOBAL.USER_ID; -- Bug# 3045649, user_id to be updated in last_updated_by column

Line: 15

  l_rows_updated     NUMBER; --Bug 4598106

Line: 20

  UPDATE ENI_DENORM_HIERARCHIES B
  SET ITEM_ASSGN_FLAG = 'Y',
      LAST_UPDATE_DATE = SYSDATE,
      LAST_UPDATED_BY = l_user_id,
      LAST_UPDATE_LOGIN = l_user_id
  WHERE OBJECT_TYPE = 'CATEGORY_SET'
    AND OBJECT_ID = g_catset_id
    AND ITEM_ASSGN_FLAG = 'N'
    AND CHILD_ID = NVL(p_new_category_id, -1);

Line: 30

  l_rows_updated := SQL%ROWCOUNT;

Line: 34

  UPDATE ENI_DENORM_HIERARCHIES B
  SET ITEM_ASSGN_FLAG = 'N',
      LAST_UPDATE_DATE = SYSDATE,
      LAST_UPDATED_BY = l_user_id,
      LAST_UPDATE_LOGIN = l_user_id
  WHERE OBJECT_TYPE = 'CATEGORY_SET'
    AND OBJECT_ID = g_catset_id
    AND CHILD_ID = NVL(p_old_category_id, -1)
    AND ITEM_ASSGN_FLAG = 'Y'
    AND CHILD_ID <> -1
    AND NOT EXISTS (SELECT NULL
                    FROM MTL_ITEM_CATEGORIES C
                    WHERE C.CATEGORY_SET_ID = g_catset_id
                      AND C.CATEGORY_ID = B.CHILD_ID);

Line: 49

  l_rows_updated := l_rows_updated + SQL%ROWCOUNT;

Line: 52

  If no rows updated then the old_category and new category id are not
  product categories hence no need to execute further*/
  IF l_rows_updated = 0 then
     return;

Line: 66

    SELECT 1 INTO l_count
    FROM MTL_SYSTEM_ITEMS_B IT
    WHERE ROWNUM = 1
      AND NOT EXISTS (SELECT NULL FROM MTL_ITEM_CATEGORIES C
                      WHERE C.CATEGORY_SET_ID = g_catset_id
                        AND C.INVENTORY_ITEM_ID = IT.INVENTORY_ITEM_ID
                        AND C.ORGANIZATION_ID = IT.ORGANIZATION_ID);*/

Line: 74

    SELECT 1 INTO l_count
    FROM ENI_OLTP_ITEM_STAR star
    WHERE star.vbh_category_id = -1
      AND rownum = 1;

Line: 83

  UPDATE ENI_DENORM_HIERARCHIES B
  SET
    ITEM_ASSGN_FLAG = DECODE(l_count, 0, 'N', 'Y'),
    LAST_UPDATE_DATE = SYSDATE,
    LAST_UPDATED_BY = l_user_id,
    LAST_UPDATE_LOGIN = l_user_id
  WHERE B.OBJECT_TYPE = 'CATEGORY_SET'
    AND B.OBJECT_ID = g_catset_id
    AND B.ITEM_ASSGN_FLAG = DECODE(l_count, 0, 'Y', 'N')
    AND B.CHILD_ID = -1
    AND B.PARENT_ID = -1;

Line: 101

    FND_MSG_PUB.ADD_EXC_MSG('ENI_UPD_ASSGN', 'UPDATE_ASSGN_FLAG', SQLERRM);

Line: 104

END UPDATE_ASSGN_FLAG;