[Home] [Help]
168: FROM mtl_item_revisions_b item_rev
169: WHERE inventory_item_id = cp_item_id
170: AND EXISTS
171: (SELECT P2.ORGANIZATION_ID
172: FROM MTL_PARAMETERS P1,
173: MTL_PARAMETERS P2
174: WHERE P1.ORGANIZATION_ID = cp_org_id
175: AND P1.MASTER_ORGANIZATION_ID = P2.MASTER_ORGANIZATION_ID
176: AND p2.organization_id = item_rev.organization_id
169: WHERE inventory_item_id = cp_item_id
170: AND EXISTS
171: (SELECT P2.ORGANIZATION_ID
172: FROM MTL_PARAMETERS P1,
173: MTL_PARAMETERS P2
174: WHERE P1.ORGANIZATION_ID = cp_org_id
175: AND P1.MASTER_ORGANIZATION_ID = P2.MASTER_ORGANIZATION_ID
176: AND p2.organization_id = item_rev.organization_id
177: )
341: FROM mtl_system_items_kfv item -- changed for Business Event Enh.
342: WHERE inventory_item_id = cp_item_id
343: AND EXISTS
344: (SELECT P2.ORGANIZATION_ID
345: FROM MTL_PARAMETERS P1,
346: MTL_PARAMETERS P2
347: WHERE P1.ORGANIZATION_ID = cp_org_id
348: AND P1.MASTER_ORGANIZATION_ID = P2.MASTER_ORGANIZATION_ID
349: AND P2.ORGANIZATION_ID = item.ORGANIZATION_ID
342: WHERE inventory_item_id = cp_item_id
343: AND EXISTS
344: (SELECT P2.ORGANIZATION_ID
345: FROM MTL_PARAMETERS P1,
346: MTL_PARAMETERS P2
347: WHERE P1.ORGANIZATION_ID = cp_org_id
348: AND P1.MASTER_ORGANIZATION_ID = P2.MASTER_ORGANIZATION_ID
349: AND P2.ORGANIZATION_ID = item.ORGANIZATION_ID
350: );
382: l_curr_cc_id NUMBER;
383: l_curr_status_code MTL_SYSTEM_ITEMS_B.inventory_item_status_code%TYPE;
384: --Start 4105841
385: l_event_ret_status VARCHAR2(1);
386: l_org_code MTL_PARAMETERS.ORGANIZATION_CODE%TYPE;
387: l_raise_event VARCHAR2(1);
388: --Start 4105841
389: l_item_description VARCHAR2(2000); --R12
390: l_item_number VARCHAR2(2000); --R12
605: IF l_raise_event = 'Y'
606: AND NVL(l_curr_cc_id,-1) = NVL(p_new_catalog_category_id,-1)
607: THEN
608: SELECT ORGANIZATION_CODE INTO l_org_code
609: FROM MTL_PARAMETERS
610: WHERE ORGANIZATION_ID = p_organization_id;
611: EGO_WF_WRAPPER_PVT.Raise_Item_Create_Update_Event(
612: p_event_name => EGO_WF_WRAPPER_PVT.G_ITEM_UPDATE_EVENT
613: ,p_Organization_Id => p_Organization_Id
724: FOR UPDATE OF lifecycle_id,current_phase_id;
725:
726: CURSOR c_get_master_org_details (cp_org_id IN NUMBER) IS
727: SELECT organization_id
728: FROM mtl_parameters
729: WHERE organization_id = master_organization_id
730: and organization_id = NVL(cp_org_id, organization_id);
731:
732: /* Bug: 3007563
754: l_api_name VARCHAR2(30);
755: --Start 4105841
756: l_msg_data VARCHAR2(4000);
757: l_event_ret_status VARCHAR2(1);
758: l_org_code MTL_PARAMETERS.ORGANIZATION_CODE%TYPE;
759: l_item_number mtl_system_items_kfv.CONCATENATED_SEGMENTS%TYPE;
760: l_item_desc mtl_system_items_kfv.DESCRIPTION%TYPE;
761: -- End 4105841
762: -- start bug 13473691
813: ,last_update_date = l_sysdate
814: ,last_updated_by = l_user_id
815: ,last_update_login = l_login_id
816: WHERE inventory_item_id = p_inventory_item_id
817: AND organization_id IN (SELECT organization_id FROM mtl_parameters WHERE master_organization_id=p_organization_id);
818:
819: -- Bug: 3072079 Added code to update descriptive values of new item catalog group for an item
820: INVIDIT2.Match_Catalog_Descr_Elements (p_inventory_item_id, p_new_catalog_group_id);
821:
825:
826: --Delete AG which doesnt fall under new catalog hierarchy.
827: DELETE ego_mtl_sy_items_ext_b ext
828: WHERE inventory_item_id = p_inventory_item_id
829: AND organization_id IN (SELECT organization_id FROM mtl_parameters WHERE master_organization_id=p_organization_id) /* Changed WHERE clause for the Bug 9094912 */
830: AND EXISTS (SELECT NULL
831: FROM ego_obj_attr_grp_assocs_v
832: WHERE attr_group_id = ext.attr_group_id
833: AND classification_code IN
848: do not run delete on ego_mtl_sy_items_ext_tl.*/
849: IF(SQL%ROWCOUNT > 0) THEN
850: DELETE ego_mtl_sy_items_ext_tl extl
851: WHERE inventory_item_id = p_inventory_item_id
852: AND organization_id IN (SELECT organization_id FROM mtl_parameters WHERE master_organization_id=p_organization_id) /* Changed WHERE clause for the Bug 9094912 */
853: AND EXISTS (SELECT NULL
854: FROM ego_obj_attr_grp_assocs_v
855: WHERE attr_group_id = extl.attr_group_id
856: AND classification_code IN
875: ,last_update_date = l_sysdate
876: ,last_updated_by = l_user_id
877: ,last_update_login = l_login_id
878: WHERE inventory_item_id = p_inventory_item_id
879: AND organization_id IN (SELECT organization_id FROM mtl_parameters WHERE master_organization_id=p_organization_id); /* Changed WHERE clause for the Bug 9094912 */
880:
881: /* Fix for Bug 10404086- If there are no recs in ego_mtl_sy_items_ext_b ,
882: do not run update on ego_mtl_sy_items_ext_tl.*/
883: IF(SQL%ROWCOUNT > 0) THEN
883: IF(SQL%ROWCOUNT > 0) THEN
884: UPDATE ego_mtl_sy_items_ext_tl
885: SET item_catalog_group_id = p_new_catalog_group_id
886: WHERE inventory_item_id = p_inventory_item_id
887: AND organization_id IN (SELECT organization_id FROM mtl_parameters WHERE master_organization_id=p_organization_id); /* Changed WHERE clause for the Bug 9094912 */
888: End If; /* Fix for bug 10404086 - end of if for update on ego_mtl_sy_items_ext_tl */
889:
890: --Delete attachments which doesnt fall under new catalog hierarchy.
891: DELETE fnd_attached_documents docs
889:
890: --Delete attachments which doesnt fall under new catalog hierarchy.
891: DELETE fnd_attached_documents docs
892: WHERE pk2_value = to_char(p_inventory_item_id)
893: AND pk1_value IN (SELECT to_Char(organization_id) FROM mtl_parameters WHERE master_organization_id=p_organization_id) /* Changed WHERE clause for the Bug 9094912 */
894: AND entity_name IN ('MTL_ITEM_REVISIONS','MTL_SYSTEM_ITEMS')
895: AND EXISTS (SELECT NULL
896: FROM ego_objtype_attach_cats
897: WHERE attach_category_id = docs.category_id
925:
926: --Start 4105841 Raise the event for the organizations to which item is assigned.
927: --Raise only for the master item
928: SELECT ORGANIZATION_CODE INTO l_org_code
929: FROM MTL_PARAMETERS
930: WHERE ORGANIZATION_ID = p_organization_id;
931: --Added for Bug 4586769
932: SELECT CONCATENATED_SEGMENTS, DESCRIPTION
933: INTO l_item_number, l_item_desc
1366: FROM ego_item_projects item_proj
1367: WHERE inventory_item_id = cp_item_id
1368: AND EXISTS
1369: (SELECT P2.ORGANIZATION_ID
1370: FROM MTL_PARAMETERS P1, MTL_PARAMETERS P2
1371: WHERE P1.ORGANIZATION_ID = cp_org_id
1372: AND P1.MASTER_ORGANIZATION_ID = P2.MASTER_ORGANIZATION_ID
1373: AND P2.ORGANIZATION_ID = item_proj.organization_id
1374: )
1385: FROM ego_item_projects rev_proj
1386: WHERE inventory_item_id = cp_item_id
1387: AND EXISTS
1388: (SELECT P2.ORGANIZATION_ID
1389: FROM MTL_PARAMETERS P1, MTL_PARAMETERS P2
1390: WHERE P1.ORGANIZATION_ID = cp_org_id
1391: AND P1.MASTER_ORGANIZATION_ID = P2.MASTER_ORGANIZATION_ID
1392: AND P2.ORGANIZATION_ID = rev_proj.organization_id
1393: )
1409: FROM mtl_pending_item_status mpis
1410: WHERE inventory_item_id = cp_item_id
1411: AND EXISTS
1412: (SELECT P2.ORGANIZATION_ID
1413: FROM MTL_PARAMETERS P1, MTL_PARAMETERS P2
1414: WHERE P1.ORGANIZATION_ID = cp_org_id
1415: AND P1.MASTER_ORGANIZATION_ID = P2.MASTER_ORGANIZATION_ID
1416: AND P2.ORGANIZATION_ID = mpis.organization_id
1417: )
1585: -- AND revision IS NULL
1586: AND revision_id IS NULL
1587: AND EXISTS
1588: (SELECT 'X'
1589: FROM mtl_parameters p1, mtl_parameters p2
1590: WHERE p1.organization_id = p_organization_id
1591: AND p1.master_organization_id = p2.master_organization_id
1592: AND p2.organization_id = proj.organization_id
1593: )
1747: FROM mtl_item_revisions_b item_rev
1748: WHERE item_rev.inventory_item_id = cp_item_id
1749: AND EXISTS
1750: (SELECT 'X'
1751: FROM mtl_parameters p1, mtl_parameters p2
1752: WHERE p1.organization_id = cp_org_id
1753: AND p1.master_organization_id = p2.master_organization_id
1754: AND p2.organization_id = item_rev.organization_id
1755: )
1920: 'WHERE revised_item_id = :1' ||
1921: 'AND change_notice <> :2'||
1922: 'AND revised_item_sequence_id <> :3' ||
1923: 'AND EXISTS ' ||
1924: ' (SELECT ''X'' FROM mtl_parameters p1, mtl_parameters p2 ' ||
1925: ' WHERE p1.organization_id = :4 ' ||
1926: ' AND p1.master_organization_id = p2.master_organization_id ' ||
1927: ' AND p2.organization_id = change.organization_id )' ||
1928: ' AND status_type NOT IN (5,6) ' ||
1996: ' WHERE change.revised_item_id = :1 ' ||
1997: ' AND change.change_notice <> :2 ' ||
1998: ' AND change.revised_item_sequence_id <> :3 ' ||
1999: ' AND EXISTS ' ||
2000: ' (SELECT ''X'' FROM mtl_parameters p1, mtl_parameters p2 ' ||
2001: ' WHERE p1.organization_id = :4 ' ||
2002: ' AND p1.master_organization_id = p2.master_organization_id ' ||
2003: ' AND p2.organization_id = change.organization_id ' ||
2004: ' ) ' ||