46:
47: IF p_revision IS NULL THEN
48:
49: SELECT COUNT(1) INTO l_count
50: FROM ego_item_projects
51: WHERE inventory_item_id = p_inventory_item_id
52: AND organization_id = p_organization_id
53: AND revision_id IS NULL
54: AND association_type = G_PROJECT_ASSOC_TYPE
57:
58: ELSE
59:
60: SELECT COUNT(1) INTO l_count
61: FROM EGO_ITEM_PROJECTS a
62: WHERE inventory_item_id = p_inventory_item_id
63: AND organization_id = p_organization_id
64: AND EXISTS
65: (SELECT revision_id
211: IF FND_API.To_Boolean(p_init_msg_list) THEN
212: FND_MSG_PUB.Initialize;
213: END IF;
214:
215: --Check if there are any entries for it in EGO_ITEM_PROJECTS
216: SELECT
217: COUNT(*) INTO l_count
218: FROM
219: EGO_ITEM_PROJECTS
215: --Check if there are any entries for it in EGO_ITEM_PROJECTS
216: SELECT
217: COUNT(*) INTO l_count
218: FROM
219: EGO_ITEM_PROJECTS
220: WHERE
221: PROJECT_ID = p_project_id;
222:
223: IF (l_count > 0)
423: INVENTORY_ITEM_ID, ORGANIZATION_ID
424: INTO
425: l_inventory_item_id, l_organization_id
426: FROM
427: EGO_ITEM_PROJECTS
428: WHERE
429: PROJECT_ID = p_project_id
430: AND ASSOCIATION_TYPE = g_project_assoc_type
431: AND ASSOCIATION_CODE = g_lifecycle_tracking_code
616: END IF;
617:
618: -- This procedure returns TRUE if and only if the project ID
619: -- passed in belongs to a project that is:
620: -- 1). associated to an Item in the EGO_ITEM_PROJECTS table, and
621: -- 2). associated as a Lifecycle tracking project.
622:
623: SELECT COUNT(1) INTO l_count
624: FROM EGO_ITEM_PROJECTS
620: -- 1). associated to an Item in the EGO_ITEM_PROJECTS table, and
621: -- 2). associated as a Lifecycle tracking project.
622:
623: SELECT COUNT(1) INTO l_count
624: FROM EGO_ITEM_PROJECTS
625: WHERE PROJECT_ID = p_project_id
626: AND ASSOCIATION_TYPE = g_project_assoc_type
627: AND ASSOCIATION_CODE = g_lifecycle_tracking_code
628: AND ROWNUM = 1;
666: l_api_name VARCHAR2(30);
667:
668: CURSOR c_item_project (cp_project_id IN NUMBER) IS
669: SELECT inventory_item_id, organization_id
670: FROM ego_item_projects
671: WHERE project_id = cp_project_id;
672:
673: BEGIN
674:
704: RETURN;
705: END IF;
706: END LOOP;
707: DELETE
708: FROM EGO_ITEM_PROJECTS
709: WHERE PROJECT_ID = p_project_id;
710:
711: -- Standard check of p_commit.
712: IF FND_API.To_Boolean(p_commit)
775: inventory_item_id
776: ,organization_id
777: -- ,revision
778: ,revision_id
779: FROM EGO_ITEM_PROJECTS proj
780: WHERE project_id = p_project_id
781: AND association_type = G_PROJECT_ASSOC_TYPE
782: AND association_code = G_LIFECYCLE_TRACKING_CODE
783: -- sync phase changes of items which are not in the same phase of project
1280: ) IS
1281:
1282: l_api_version NUMBER;
1283: l_api_name VARCHAR2(30);
1284: l_item_project_id EGO_ITEM_PROJECTS.item_project_id%TYPE;
1285: l_revision_id EGO_ITEM_PROJECTS.revision_id%TYPE;
1286:
1287: BEGIN
1288:
1281:
1282: l_api_version NUMBER;
1283: l_api_name VARCHAR2(30);
1284: l_item_project_id EGO_ITEM_PROJECTS.item_project_id%TYPE;
1285: l_revision_id EGO_ITEM_PROJECTS.revision_id%TYPE;
1286:
1287: BEGIN
1288:
1289: l_api_version := 1.0;
1354: IF p_revision IS NULL AND p_revision_id IS NULL THEN
1355: code_debug (l_api_name ||' working in context of item ');
1356: l_revision_id := NULL;
1357: DELETE
1358: FROM EGO_ITEM_PROJECTS
1359: WHERE INVENTORY_ITEM_ID = p_inventory_item_id
1360: AND ORGANIZATION_ID = p_organization_id
1361: AND REVISION_ID IS NULL
1362: AND ASSOCIATION_TYPE = g_project_assoc_type
1371: ELSE
1372: l_revision_id := p_revision_id;
1373: END IF;
1374: DELETE
1375: FROM EGO_ITEM_PROJECTS
1376: WHERE INVENTORY_ITEM_ID = p_inventory_item_id
1377: AND ORGANIZATION_ID = p_organization_id
1378: AND REVISION_id = l_revision_id
1379: AND ASSOCIATION_TYPE = g_project_assoc_type
1381: END IF;
1382:
1383: END IF;
1384:
1385: SELECT EGO_ITEM_PROJECTS_S.NEXTVAL
1386: INTO l_item_project_id
1387: FROM DUAL;
1388:
1389: code_debug (l_api_name ||' revision id '||l_revision_id);
1392: -- Insert the new row into the item-projects table --
1393: --------------------------------------------------------------------------
1394:
1395: INSERT INTO
1396: EGO_ITEM_PROJECTS
1397: (
1398: ITEM_PROJECT_ID
1399: ,INVENTORY_ITEM_ID
1400: ,ORGANIZATION_ID
1564: ,cp_association_type IN VARCHAR2
1565: ,cp_association_code IN VARCHAR2
1566: ) IS
1567: SELECT project_id, task_id
1568: FROM ego_item_projects
1569: WHERE inventory_item_id = cp_inventory_item_id
1570: AND organization_id = cp_organization_id
1571: AND NVL(revision_id,-1) = NVL(cp_revision_id,-1) -- -1 is not a valid revision_id
1572: AND association_type = cp_association_type
1741: ,INVENTORY_ITEM_ID
1742: ,REVISION_ID
1743: ,ASSOCIATION_TYPE
1744: ,ASSOCIATION_CODE
1745: FROM EGO_ITEM_PROJECTS
1746: WHERE PROJECT_ID = cp_project_id;
1747:
1748: BEGIN
1749: