[Home] [Help]
273: WHERE NVL(NEW_ITEM_REQUEST_REQD,'N') = 'Y'
274: AND item_Catalog_group_id = P_Catalog_Group_Id
275: )
276: AND EXISTS ( SELECT NULL
277: FROM mtl_system_items_interface
278: WHERE NVL(eng_item_flag,'N')='N'
279: AND rowid =P_Rowid);
280:
281: l_valid_status VARCHAR2(1) := 'N';
354: SELECT lifecycle_id,
355: current_phase_id,
356: item_catalog_group_id,
357: approval_status -- Added for 4046435
358: FROM mtl_system_items_b
359: WHERE inventory_item_id = P_Item_Id
360: AND organization_id IN
361: (SELECT organization_id
362: FROM mtl_parameters
368: ,cp_status_code VARCHAR2)IS
369: SELECT 'Y'
370: FROM DUAL
371: WHERE EXISTS ( SELECT NULL
372: FROM mtl_system_items_b
373: WHERE inventory_item_id = P_Item_Id
374: AND organization_id = P_Org_Id
375: AND NVL(approval_status,'A') <> 'A'
376: )
388: SELECT display_sequence
389: FROM pa_ego_phases_v
390: WHERE proj_element_id = cp_phase_id ;
391:
392: l_Old_Phase_Id mtl_system_items_b.current_phase_id%TYPE;
393: l_Old_Lifecycle_Id mtl_system_items_b.lifecycle_id%TYPE;
394: l_Old_catalog_group_Id mtl_system_items_b.item_catalog_group_id%TYPE;
395: l_valid_status VARCHAR2(1) := 'N';
396: l_Policy_Code VARCHAR2(20);
389: FROM pa_ego_phases_v
390: WHERE proj_element_id = cp_phase_id ;
391:
392: l_Old_Phase_Id mtl_system_items_b.current_phase_id%TYPE;
393: l_Old_Lifecycle_Id mtl_system_items_b.lifecycle_id%TYPE;
394: l_Old_catalog_group_Id mtl_system_items_b.item_catalog_group_id%TYPE;
395: l_valid_status VARCHAR2(1) := 'N';
396: l_Policy_Code VARCHAR2(20);
397: l_Return_Status VARCHAR2(1);
390: WHERE proj_element_id = cp_phase_id ;
391:
392: l_Old_Phase_Id mtl_system_items_b.current_phase_id%TYPE;
393: l_Old_Lifecycle_Id mtl_system_items_b.lifecycle_id%TYPE;
394: l_Old_catalog_group_Id mtl_system_items_b.item_catalog_group_id%TYPE;
395: l_valid_status VARCHAR2(1) := 'N';
396: l_Policy_Code VARCHAR2(20);
397: l_Return_Status VARCHAR2(1);
398: l_Error_Code NUMBER;
397: l_Return_Status VARCHAR2(1);
398: l_Error_Code NUMBER;
399: l_Msg_Count NUMBER;
400: l_Msg_Data VARCHAR2(2000);
401: l_approval_status mtl_system_items_b.approval_status%TYPE; --Bug 4046435
402: -- Bug 5375723
403: l_old_disp_seq NUMBER;
404: l_new_disp_seq NUMBER;
405:
544: ,X_Error_Code OUT NOCOPY VARCHAR2) IS
545:
546: CURSOR c_get_master_details(cp_item_id NUMBER) IS
547: SELECT Lifecycle_id, Current_Phase_Id
548: FROM mtl_system_items_b
549: WHERE inventory_item_id = cp_item_id
550: AND organization_id IN
551: (SELECT organization_id
552: FROM mtl_parameters
552: FROM mtl_parameters
553: WHERE organization_id = master_organization_id)
554: UNION
555: SELECT Lifecycle_id, Current_Phase_Id
556: FROM mtl_system_items_interface
557: WHERE inventory_item_id = cp_item_id
558: AND process_flag = 4
559: AND organization_id IN
560: (SELECT organization_id
563:
564: CURSOR c_get_control_level IS
565: SELECT control_level
566: FROM mtl_item_attributes
567: WHERE attribute_name = 'MTL_SYSTEM_ITEMS.INVENTORY_ITEM_STATUS_CODE';
568:
569:
570: l_Master_Phase_Id mtl_system_items_b.current_phase_id%TYPE;
571: l_Master_Lifecycle_Id mtl_system_items_b.lifecycle_id%TYPE;
566: FROM mtl_item_attributes
567: WHERE attribute_name = 'MTL_SYSTEM_ITEMS.INVENTORY_ITEM_STATUS_CODE';
568:
569:
570: l_Master_Phase_Id mtl_system_items_b.current_phase_id%TYPE;
571: l_Master_Lifecycle_Id mtl_system_items_b.lifecycle_id%TYPE;
572: l_status_control NUMBER(2) := 1; --Master controlled
573: BEGIN
574: X_Error_Code := NULL;
567: WHERE attribute_name = 'MTL_SYSTEM_ITEMS.INVENTORY_ITEM_STATUS_CODE';
568:
569:
570: l_Master_Phase_Id mtl_system_items_b.current_phase_id%TYPE;
571: l_Master_Lifecycle_Id mtl_system_items_b.lifecycle_id%TYPE;
572: l_status_control NUMBER(2) := 1; --Master controlled
573: BEGIN
574: X_Error_Code := NULL;
575: X_Error_Column := NULL;
692: style_item_flag,
693: trade_item_descriptor,
694: gdsn_outbound_enabled_flag,
695: primary_uom_code
696: FROM mtl_system_items_interface int
697: WHERE (int.organization_id = P_Org_Id OR P_All_Org = 1)
698: AND int.set_process_id = P_Set_id
699: AND int.process_flag = P_Process_Flag
700: AND EXISTS -- organization_id IN
720: style_item_flag,
721: trade_item_descriptor,
722: gdsn_outbound_enabled_flag,
723: primary_uom_code
724: FROM mtl_system_items_interface int
725: WHERE (int.organization_id = P_Org_Id OR P_All_Org = 1)
726: AND int.set_process_id = P_Set_id
727: AND int.process_flag = P_Process_Flag
728: AND NOT EXISTS -- organization_id NOT IN
742: IS
743: SELECT item_catalog_group_Id,approval_status
744: ,unit_weight, weight_uom_code,trade_item_descriptor
745: ,gdsn_outbound_enabled_flag
746: FROM mtl_system_items
747: WHERE inventory_item_id = cp_inventory_item_id
748: AND organization_id = cp_organization_id;
749:
750: CURSOR c_pack_item_type (cp_pack_item_type IN VARCHAR2)
758: SELECT base_uom_flag
759: FROM mtl_units_of_measure
760: WHERE uom_code = cp_primary_uom_code;
761:
762: l_old_pack_item_type MTL_SYSTEM_ITEMS_B.trade_item_descriptor%TYPE;
763: l_ret_status VARCHAR2(100);
764: l_valid_pack_type NUMBER := 0;
765: l_item_in_pack VARCHAR2(1) := FND_API.G_FALSE;
766: l_old_gdsn_flag VARCHAR2(1);
778: X_MSG_COUNT NUMBER;
779: X_MSG_DATA VARCHAR2(240);
780: l_old_catalog_group_id NUMBER;
781: l_approval_status VARCHAR2(1);
782: l_old_unit_weight MTL_SYSTEM_ITEMS_B.UNIT_WEIGHT%TYPE;
783: l_old_weight_uom_code MTL_SYSTEM_ITEMS_B.WEIGHT_UOM_CODE%TYPE;
784: l_valid VARCHAR2(100) := FND_API.G_TRUE;
785: l_is_gdsn NUMBER;
786: l_unit_wt_disp_name VARCHAR2(1000);
779: X_MSG_DATA VARCHAR2(240);
780: l_old_catalog_group_id NUMBER;
781: l_approval_status VARCHAR2(1);
782: l_old_unit_weight MTL_SYSTEM_ITEMS_B.UNIT_WEIGHT%TYPE;
783: l_old_weight_uom_code MTL_SYSTEM_ITEMS_B.WEIGHT_UOM_CODE%TYPE;
784: l_valid VARCHAR2(100) := FND_API.G_TRUE;
785: l_is_gdsn NUMBER;
786: l_unit_wt_disp_name VARCHAR2(1000);
787: l_unit_wt_uom_disp_name VARCHAR2(1000);
826: P_Request_id,
827: cur.transaction_id,
828: l_error_msg,
829: X_Error_Column,
830: 'MTL_SYSTEM_ITEMS_INTERFACE',
831: X_Error_Code,
832: X_Err_Text);
833: IF l_error_logged < 0 THEN
834: Raise LOGGING_ERR;
863: P_Request_id,
864: cur.transaction_id,
865: l_error_msg,
866: X_Error_Column,
867: 'MTL_SYSTEM_ITEMS_INTERFACE',
868: X_Error_Code,
869: X_Err_Text);
870:
871: IF l_error_logged < 0 THEN
887: P_Request_id,
888: cur.transaction_id,
889: l_error_msg,
890: 'CURRENT_PHASE_ID',
891: 'MTL_SYSTEM_ITEMS_INTERFACE',
892: 'INV_IOI_LIFECYCLE_MANDATORY',
893: X_Err_Text);
894:
895: IF l_error_logged < 0 THEN
941: P_Request_id,
942: cur.transaction_id,
943: l_error_msg,
944: 'UNIT_WEIGHT',
945: 'MTL_SYSTEM_ITEMS_INTERFACE',
946: 'INV_IOI_ERR',
947: X_Err_Text);
948:
949: IF l_error_logged < 0 THEN
959: p_xset_id => P_Set_id,
960: x_err_text => x_err_text);
961: IF l_ret_status <> 0 THEN
962:
963: UPDATE mtl_system_items_interface
964: SET process_flag = 3
965: WHERE rowid = cur.rowid;
966:
967: l_error_logged := INVPUOPI.mtl_log_interface_err(
973: P_Request_id,
974: cur.transaction_id,
975: SQLERRM,
976: 'STYLE_ITEM_FLAG',
977: 'MTL_SYSTEM_ITEMS_INTERFACE',
978: 'INV_IOI_ERR',
979: x_err_text);
980: IF l_error_logged < 0 THEN
981: Raise LOGGING_ERR;
1012: P_Request_id,
1013: cur.transaction_id,
1014: X_MSG_DATA,
1015: 'ITEM_CATALOG_GROUP_ID',
1016: 'MTL_SYSTEM_ITEMS_INTERFACE',
1017: 'INV_IOI_ERR',
1018: X_ERR_TEXT);
1019:
1020: IF l_error_logged < 0 THEN
1051: P_Request_id,
1052: cur.transaction_id,
1053: l_error_msg,
1054: 'ITEM_CATALOG_GROUP_ID',
1055: 'MTL_SYSTEM_ITEMS_INTERFACE',
1056: x_err_text,
1057: x_err_text);
1058:
1059: IF l_error_logged < 0 THEN
1077: P_Request_id,
1078: cur.transaction_id,
1079: l_error_msg,
1080: 'GDSN_OUTBOUND_ENABLED_FLAG',
1081: 'MTL_SYSTEM_ITEMS_INTERFACE',
1082: 'INV_GDSN_UPD_NO_INVALID',
1083: X_Err_Text);
1084: l_error_status := 1;
1085: END IF;
1104: P_Request_id,
1105: cur.transaction_id,
1106: l_error_msg,
1107: 'TRADE_ITEM_DESCRIPTOR',
1108: 'MTL_SYSTEM_ITEMS_INTERFACE',
1109: 'INV_INVALID_PACK_TYPE',
1110: X_Err_Text);
1111: l_error_status := 1;
1112: END IF;
1128: P_Request_id,
1129: cur.transaction_id,
1130: l_err_text,
1131: 'TRADE_ITEM_DESCRIPTOR',
1132: 'MTL_SYSTEM_ITEMS_INTERFACE',
1133: 'INV_IOI_ERR',
1134: X_Err_Text);
1135: l_error_status := 1;
1136: END IF;
1176: P_Request_id,
1177: cur.transaction_id,
1178: l_error_msg,
1179: 'TRADE_ITEM_DESCRIPTOR',
1180: 'MTL_SYSTEM_ITEMS_INTERFACE',
1181: 'INV_ITEM_IN_PACK',
1182: X_Err_Text);
1183: l_error_status := 1;
1184: END IF;
1202: P_Request_id,
1203: cur.transaction_id,
1204: x_msg_data,
1205: 'TRADE_ITEM_DESCRIPTOR',
1206: 'MTL_SYSTEM_ITEMS_INTERFACE',
1207: 'INV_IOI_ERR',
1208: X_Err_Text);
1209: l_error_status := 1;
1210: END IF;
1214: END IF; --R12 C validations ONLY at the end
1215:
1216: IF l_error_status = 1 THEN
1217: --Set process flag since failed in LC validation
1218: UPDATE mtl_system_items_interface
1219: SET process_flag = 3
1220: WHERE rowid = cur.rowid;
1221: END IF;
1222:
1252: P_Request_id,
1253: cur.transaction_id,
1254: l_error_msg,
1255: X_Error_Column,
1256: 'MTL_SYSTEM_ITEMS_INTERFACE',
1257: X_Error_Code,
1258: X_Err_Text);
1259:
1260: IF l_error_logged < 0 THEN
1275: P_Request_id,
1276: cur.transaction_id,
1277: l_error_msg,
1278: 'CURRENT_PHASE_ID',
1279: 'MTL_SYSTEM_ITEMS_INTERFACE',
1280: 'INV_IOI_LIFECYCLE_MANDATORY',
1281: X_Err_Text);
1282:
1283: IF l_error_logged < 0 THEN
1321: P_Request_id,
1322: cur.transaction_id,
1323: l_error_msg,
1324: 'UNIT_WEIGHT',
1325: 'MTL_SYSTEM_ITEMS_INTERFACE',
1326: 'INV_IOI_ERR',
1327: X_Err_Text);
1328:
1329: IF l_error_logged < 0 THEN
1341: p_xset_id => P_Set_id,
1342: x_err_text => x_err_text);
1343: IF l_error_status <> 0 THEN
1344:
1345: UPDATE mtl_system_items_interface
1346: SET process_flag = 3
1347: WHERE rowid = cur.rowid;
1348:
1349: l_error_logged := INVPUOPI.mtl_log_interface_err(
1355: P_Request_id,
1356: cur.transaction_id,
1357: SQLERRM,
1358: 'STYLE_ITEM_FLAG',
1359: 'MTL_SYSTEM_ITEMS_INTERFACE',
1360: 'INV_IOI_ERR',
1361: x_err_text);
1362: IF l_error_logged < 0 THEN
1363: Raise LOGGING_ERR;
1394: P_Request_id,
1395: cur.transaction_id,
1396: X_MSG_DATA,
1397: 'ITEM_CATALOG_GROUP_ID',
1398: 'MTL_SYSTEM_ITEMS_INTERFACE',
1399: 'INV_IOI_ERR',
1400: X_ERR_TEXT);
1401:
1402: IF l_error_logged < 0 THEN
1433: P_Request_id,
1434: cur.transaction_id,
1435: l_error_msg,
1436: 'ITEM_CATALOG_GROUP_ID',
1437: 'MTL_SYSTEM_ITEMS_INTERFACE',
1438: x_err_text,
1439: x_err_text);
1440:
1441: IF l_error_logged < 0 THEN
1459: P_Request_id,
1460: cur.transaction_id,
1461: l_error_msg,
1462: 'GDSN_OUTBOUND_ENABLED_FLAG',
1463: 'MTL_SYSTEM_ITEMS_INTERFACE',
1464: 'INV_GDSN_UPD_NO_INVALID',
1465: X_Err_Text);
1466: l_error_status := 1;
1467: END IF;
1486: P_Request_id,
1487: cur.transaction_id,
1488: l_error_msg,
1489: 'TRADE_ITEM_DESCRIPTOR',
1490: 'MTL_SYSTEM_ITEMS_INTERFACE',
1491: 'INV_INVALID_PACK_TYPE',
1492: X_Err_Text);
1493: l_error_status := 1;
1494: END IF;
1511: P_Request_id,
1512: cur.transaction_id,
1513: l_err_text,
1514: 'TRADE_ITEM_DESCRIPTOR',
1515: 'MTL_SYSTEM_ITEMS_INTERFACE',
1516: 'INV_IOI_ERR',
1517: X_Err_Text);
1518: l_error_status := 1;
1519: END IF;
1558: P_Request_id,
1559: cur.transaction_id,
1560: l_error_msg,
1561: 'TRADE_ITEM_DESCRIPTOR',
1562: 'MTL_SYSTEM_ITEMS_INTERFACE',
1563: 'INV_ITEM_IN_PACK',
1564: X_Err_Text);
1565: l_error_status := 1;
1566: END IF;
1584: P_Request_id,
1585: cur.transaction_id,
1586: x_msg_data,
1587: 'TRADE_ITEM_DESCRIPTOR',
1588: 'MTL_SYSTEM_ITEMS_INTERFACE',
1589: 'INV_IOI_ERR',
1590: X_Err_Text);
1591: l_error_status := 1;
1592: END IF;
1596: END IF; --process flag 4
1597:
1598: IF l_error_status = 1 THEN
1599: --Set process flag since failed in LC validation
1600: UPDATE mtl_system_items_interface
1601: SET process_flag = 3
1602: WHERE rowid = cur.rowid;
1603: END IF;
1604:
1714: msii.transaction_type,
1715: msii.item_number, --5522789
1716: mp.master_organization_id,
1717: msii.created_by
1718: FROM mtl_system_items_interface msii,
1719: mtl_parameters mp
1720: WHERE (msii.organization_id = P_Org_Id OR P_All_Org = 1)
1721: AND msii.set_process_id = P_Set_id
1722: AND msii.process_flag = P_Process_flag
1728: IS
1729: SELECT item_catalog_group_id, approval_status,
1730: inventory_item_status_code,lifecycle_id,
1731: current_phase_id
1732: FROM mtl_system_items_b
1733: WHERE organization_id = cp_org_id
1734: AND inventory_item_id = cp_item_id;
1735:
1736: l_has_privilege VARCHAR2(1) := 'F';
1742: l_error_code VARCHAR2(70);
1743: l_err_text VARCHAR2(240);
1744: l_item_catalog NUMBER := 0;
1745: l_has_access VARCHAR2(1):= 'F';
1746: l_approval_status mtl_system_items_b.approval_status%TYPE;
1747: l_inventory_item_status_code mtl_system_items_b.inventory_item_status_code%TYPE;
1748: l_lifecycle_id mtl_system_items.lifecycle_id%TYPE;
1749: l_current_phase_id mtl_system_items.current_phase_id%TYPE;
1750: l_inv_debug_level NUMBER := INVPUTLI.get_debug_level;
1743: l_err_text VARCHAR2(240);
1744: l_item_catalog NUMBER := 0;
1745: l_has_access VARCHAR2(1):= 'F';
1746: l_approval_status mtl_system_items_b.approval_status%TYPE;
1747: l_inventory_item_status_code mtl_system_items_b.inventory_item_status_code%TYPE;
1748: l_lifecycle_id mtl_system_items.lifecycle_id%TYPE;
1749: l_current_phase_id mtl_system_items.current_phase_id%TYPE;
1750: l_inv_debug_level NUMBER := INVPUTLI.get_debug_level;
1751: BEGIN
1744: l_item_catalog NUMBER := 0;
1745: l_has_access VARCHAR2(1):= 'F';
1746: l_approval_status mtl_system_items_b.approval_status%TYPE;
1747: l_inventory_item_status_code mtl_system_items_b.inventory_item_status_code%TYPE;
1748: l_lifecycle_id mtl_system_items.lifecycle_id%TYPE;
1749: l_current_phase_id mtl_system_items.current_phase_id%TYPE;
1750: l_inv_debug_level NUMBER := INVPUTLI.get_debug_level;
1751: BEGIN
1752:
1745: l_has_access VARCHAR2(1):= 'F';
1746: l_approval_status mtl_system_items_b.approval_status%TYPE;
1747: l_inventory_item_status_code mtl_system_items_b.inventory_item_status_code%TYPE;
1748: l_lifecycle_id mtl_system_items.lifecycle_id%TYPE;
1749: l_current_phase_id mtl_system_items.current_phase_id%TYPE;
1750: l_inv_debug_level NUMBER := INVPUTLI.get_debug_level;
1751: BEGIN
1752:
1753: IF INV_EGO_REVISION_VALIDATE.Get_Process_Control ='EGO_ITEM_BULKLOAD' THEN
1929: P_Request_id,
1930: cur.transaction_id,
1931: l_error_msg,
1932: l_error_column,
1933: 'MTL_SYSTEM_ITEMS_INTERFACE',
1934: l_error_code,
1935: l_err_text);
1936: IF l_error_logged < 0 THEN
1937: Raise LOGGING_ERR;
1936: IF l_error_logged < 0 THEN
1937: Raise LOGGING_ERR;
1938: END IF;
1939:
1940: UPDATE mtl_system_items_interface
1941: SET process_flag = 3
1942: WHERE rowid = cur.rowid;
1943:
1944: END IF;
1973: interface.template_id,
1974: interface.transaction_id,
1975: interface.transaction_type,
1976: interface.rowid
1977: FROM mtl_system_items_interface interface
1978: WHERE interface.set_process_id = cp_set_process_id
1979: AND interface.process_flag = 4
1980: AND interface.transaction_type = 'CREATE'
1981: --4676088AND interface.transaction_type IN ('CREATE','UPDATE')
2107: ,x_msg_data => l_msg_data);
2108:
2109: IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2110:
2111: UPDATE mtl_system_items_interface
2112: SET process_flag = 3
2113: WHERE rowid = cur.rowid;
2114: l_error_logged := INVPUOPI.mtl_log_interface_err(
2115: cur.organization_id,
2120: FND_GLOBAL.CONC_REQUEST_ID,
2121: cur.transaction_id,
2122: l_msg_data,
2123: 'TEMPLATE_ID',
2124: 'MTL_SYSTEM_ITEMS_INTERFACE',
2125: 'INV_IOI_ERR',
2126: l_err_text);
2127: IF l_error_logged < 0 THEN
2128: Raise LOGGING_ERR;
2146: FND_MESSAGE.SET_TOKEN ('USER_ID', FND_GLOBAL.User_Id);
2147: l_msg_data := FND_MESSAGE.GET;
2148: For exc IN c_get_processed_records (P_Set_Id)
2149: LOOP
2150: UPDATE mtl_system_items_interface
2151: SET process_flag = 3
2152: WHERE rowid = exc.rowid;
2153: l_error_logged := INVPUOPI.mtl_log_interface_err(
2154: exc.organization_id,
2159: FND_GLOBAL.CONC_REQUEST_ID,
2160: exc.transaction_id,
2161: l_msg_data,
2162: 'LOGIN_USER_ID',
2163: 'MTL_SYSTEM_ITEMS_INTERFACE',
2164: 'INV_IOI_ERR',
2165: l_err_text);
2166: IF l_error_logged < 0 THEN
2167: Raise LOGGING_ERR;
2322: ,current_phase_id
2323: ,transaction_id
2324: ,inventory_item_status_code
2325: ,rowid
2326: FROM MTL_SYSTEM_ITEMS_INTERFACE
2327: WHERE (set_process_id = p_set_id OR set_process_id = p_set_id + 1000000000000)
2328: AND transaction_type IN ('UPDATE', 'AUTO_CHILD')
2329: AND process_flag = 4;
2330:
2331: CURSOR c_get_existing_item_record(cp_item_id NUMBER, cp_org_id NUMBER) IS
2332: SELECT item_catalog_group_id
2333: ,lifecycle_id
2334: ,current_phase_id
2335: FROM MTL_SYSTEM_ITEMS_B
2336: WHERE inventory_item_id = cp_item_id
2337: AND organization_id = cp_org_id;
2338:
2339:
2483: END IF;
2484:
2485: IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2486:
2487: UPDATE mtl_system_items_interface
2488: SET process_flag = 3
2489: WHERE rowid = cur.rowid;
2490:
2491: dumm_status := INVPUOPI.mtl_log_interface_err(
2497: ,P_Request_Id
2498: ,cur.transaction_id
2499: ,l_msg_text
2500: ,l_column_name
2501: ,'MTL_SYSTEM_ITEMS_INTERFACE'
2502: ,'INV_IOI_ERR'
2503: ,err_text);
2504: END IF;
2505: END LOOP;
2819: CURSOR c_nir_rec (cp_batch_id IN NUMBER)
2820: IS
2821: SELECT interface.inventory_item_id, interface.organization_id,
2822: interface.item_catalog_group_id
2823: FROM mtl_system_items_interface interface,
2824: mtl_parameters mp
2825: WHERE interface.SET_PROCESS_ID = p_set_process_id
2826: AND interface.PROCESS_FLAG = 4
2827: AND interface.TRANSACTION_TYPE = 'CREATE'
2874: FETCH c_nir_reqd INTO l_nir_reqd;
2875: CLOSE c_nir_reqd;
2876:
2877: IF l_nir_reqd = 'Y' THEN
2878: UPDATE MTL_SYSTEM_ITEMS_B
2879: SET INVENTORY_ITEM_STATUS_CODE='Pending'
2880: ,APPROVAL_STATUS = 'N'
2881: ,CURRENT_PHASE_ID = DECODE(LIFECYCLE_ID,NULL,NULL,Get_Initial_Lifecycle_Phase(LIFECYCLE_ID))
2882: WHERE INVENTORY_ITEM_ID = nir_item.inventory_item_id
2903:
2904: IF l_nir_import_option <> 'N' THEN
2905:
2906: /* R12C Modifying stmt to support hierarchy enabled catalogs for NIR reqd */
2907: UPDATE mtl_system_items_interface msii
2908: SET msii.process_flag = 5
2909: WHERE msii.ROWID IN
2910: ( SELECT interface.ROWID
2911: FROM mtl_system_items_interface interface,
2907: UPDATE mtl_system_items_interface msii
2908: SET msii.process_flag = 5
2909: WHERE msii.ROWID IN
2910: ( SELECT interface.ROWID
2911: FROM mtl_system_items_interface interface,
2912: -- MTL_ITEM_CATALOG_GROUPS_B micb,
2913: MTL_PARAMETERS mp
2914: WHERE interface.SET_PROCESS_ID = p_set_process_id
2915: AND interface.PROCESS_FLAG = 4
2934: x_msg_data => l_msg_data,
2935: x_msg_count => l_msg_count );
2936:
2937: IF (l_ret_status = FND_API.G_RET_STS_SUCCESS )THEN
2938: UPDATE mtl_system_items_interface
2939: SET process_flag = 4
2940: WHERE set_process_id = p_set_process_id
2941: AND process_flag = 5;
2942: ELSE
2944: INVPUTLI.info('INV_EGO_REVISION_VALIDATE.Create_New_Item_Request: Following exception from CM during NIR creation');
2945: INVPUTLI.info(l_msg_data);
2946: END IF;
2947:
2948: UPDATE mtl_system_items_interface
2949: SET process_flag = 3,
2950: change_id = NULL,
2951: change_line_id = NULL
2952: WHERE set_process_id = p_set_process_id
2960: IF l_inv_debug_level IN(101, 102) THEN
2961: INVPUTLI.info('INV_EGO_REVISION_VALIDATE.Create_New_Item_Request: Exception'||substr(SQLERRM,1,200));
2962: END IF;
2963:
2964: UPDATE mtl_system_items_interface
2965: SET process_flag = 3,
2966: change_id = NULL,
2967: change_line_id = NULL
2968: WHERE set_process_id = p_set_process_id
2983: interface.ITEM_NUMBER,
2984: interface.rowid,
2985: micb.NEW_ITEM_REQ_CHANGE_TYPE_ID,
2986: mp.ORGANIZATION_CODE
2987: FROM MTL_SYSTEM_ITEMS_INTERFACE interface,
2988: MTL_ITEM_CATALOG_GROUPS_B micb,
2989: MTL_PARAMETERS mp
2990: WHERE interface.SET_PROCESS_ID = cp_set_process_id
2991: AND interface.PROCESS_FLAG = 4
3015: FOR i in c_get_processed_records(p_set_process_id) LOOP
3016: --Ideally this update should be done only after NIR created succesfully
3017: -- but for technical reasons we are not stoping item creation even though
3018: -- NIR is not created.
3019: UPDATE MTL_SYSTEM_ITEMS_B
3020: SET INVENTORY_ITEM_STATUS_CODE='Pending'
3021: ,APPROVAL_STATUS = 'N'
3022: ,CURRENT_PHASE_ID = DECODE(LIFECYCLE_ID,NULL,NULL,Get_Initial_Lifecycle_Phase(LIFECYCLE_ID))
3023: WHERE INVENTORY_ITEM_ID = i.inventory_item_id
3059: ELSE
3060: l_error := true;
3061: END IF;
3062: IF (l_error OR l_return_status ='G' )THEN
3063: UPDATE MTL_SYSTEM_ITEMS_INTERFACE
3064: SET process_flag = 3
3065: WHERE rowid = i.rowid;
3066:
3067: dumm_status := INVPUOPI.mtl_log_interface_err(
3073: FND_GLOBAL.CONC_REQUEST_ID
3074: ,i.transaction_id
3075: ,err_text
3076: ,'ITEM_NUMBER'
3077: ,'MTL_SYSTEM_ITEMS_INTERFACE'
3078: ,'INV_IOI_NO_AUTO_NIR'
3079: ,err_text);
3080: ELSIF (l_return_status <> FND_API.G_RET_STS_SUCCESS )THEN
3081: Error_Handler.Get_Message_List( x_message_list => l_error_table);
3078: ,'INV_IOI_NO_AUTO_NIR'
3079: ,err_text);
3080: ELSIF (l_return_status <> FND_API.G_RET_STS_SUCCESS )THEN
3081: Error_Handler.Get_Message_List( x_message_list => l_error_table);
3082: UPDATE MTL_SYSTEM_ITEMS_INTERFACE
3083: SET process_flag = 3
3084: WHERE rowid = i.rowid;
3085:
3086: dumm_status := INVPUOPI.mtl_log_interface_err(
3092: FND_GLOBAL.CONC_REQUEST_ID
3093: ,i.transaction_id
3094: ,l_error_table(1).message_text
3095: ,'ITEM_CATALOG_GORUP_ID'
3096: ,'MTL_SYSTEM_ITEMS_INTERFACE'
3097: ,'INV_IOI_ERR'
3098: ,err_text);
3099: ELSE
3100: UPDATE MTL_SYSTEM_ITEMS_INTERFACE
3096: ,'MTL_SYSTEM_ITEMS_INTERFACE'
3097: ,'INV_IOI_ERR'
3098: ,err_text);
3099: ELSE
3100: UPDATE MTL_SYSTEM_ITEMS_INTERFACE
3101: SET CHANGE_ID = l_change_id
3102: WHERE rowid = i.rowid;
3103: END IF;
3104: END LOOP;
3108: END IF;
3109:
3110: EXCEPTION
3111: WHEN OTHERS THEN
3112: UPDATE MTL_SYSTEM_ITEMS_INTERFACE
3113: SET process_flag = 3
3114: WHERE set_process_id= p_set_process_id;
3115:
3116:
3127: FND_GLOBAL.CONC_REQUEST_ID,
3128: p_set_process_id--i.transaction_id
3129: ,'CO NIR is having problem for this set proecess id'||p_set_process_id
3130: ,'ITEM_CATALOG_GORUP_ID'
3131: ,'MTL_SYSTEM_ITEMS_INTERFACE'
3132: ,'INV_IOI_ERR'
3133: ,err_text);
3134: END Create_New_Item_Request; */
3135: ------------------------------------------------------------------------------------------
3395:
3396: IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
3397: l_error_occured := TRUE;
3398:
3399: UPDATE MTL_SYSTEM_ITEMS_INTERFACE
3400: SET process_flag = 3
3401: WHERE rowid = p_rowid;
3402:
3403: dumm_status := INVPUOPI.mtl_log_interface_err(
3409: p_request_id,
3410: p_transaction_id,
3411: l_error_msg,
3412: 'ITEM_CATALOG_GORUP_ID',
3413: 'MTL_SYSTEM_ITEMS_INTERFACE',
3414: 'INV_IOI_CANCEL_NIR_FAILED',
3415: x_err_text);
3416: ELSIF (l_return_status <> FND_API.G_RET_STS_SUCCESS )THEN
3417: l_error_occured := TRUE;
3416: ELSIF (l_return_status <> FND_API.G_RET_STS_SUCCESS )THEN
3417: l_error_occured := TRUE;
3418: Error_Handler.Get_Message_List( x_message_list => l_error_table);
3419:
3420: UPDATE MTL_SYSTEM_ITEMS_INTERFACE
3421: SET process_flag = 3
3422: WHERE rowid = p_rowid;
3423:
3424: dumm_status := INVPUOPI.mtl_log_interface_err(
3430: p_request_id,
3431: p_transaction_id,
3432: l_error_table(1).message_text,
3433: 'ITEM_CATALOG_GORUP_ID',
3434: 'MTL_SYSTEM_ITEMS_INTERFACE',
3435: 'INV_IOI_ERR',
3436: x_err_text);
3437: END IF;
3438: END IF; --IF l_raise_cancel_nir THEN
3449:
3450: IF (l_return_status ='G' )THEN
3451: l_error_occured := TRUE;
3452:
3453: UPDATE MTL_SYSTEM_ITEMS_INTERFACE
3454: SET process_flag = 3
3455: WHERE rowid = p_rowid;
3456:
3457: dumm_status := INVPUOPI.mtl_log_interface_err(
3463: ,p_request_id
3464: ,p_transaction_id
3465: ,l_error_msg
3466: ,'ITEM_NUMBER'
3467: ,'MTL_SYSTEM_ITEMS_INTERFACE'
3468: ,'INV_IOI_NO_AUTO_NIR'
3469: ,x_err_text);
3470: ELSIF (l_return_status <> FND_API.G_RET_STS_SUCCESS )THEN
3471: l_error_occured := TRUE;
3469: ,x_err_text);
3470: ELSIF (l_return_status <> FND_API.G_RET_STS_SUCCESS )THEN
3471: l_error_occured := TRUE;
3472: Error_Handler.Get_Message_List( x_message_list => l_error_table);
3473: UPDATE MTL_SYSTEM_ITEMS_INTERFACE
3474: SET process_flag = 3
3475: WHERE rowid = p_rowid;
3476:
3477: dumm_status := INVPUOPI.mtl_log_interface_err(
3483: ,p_request_id
3484: ,p_transaction_id
3485: ,l_error_table(1).message_text
3486: ,'ITEM_CATALOG_GORUP_ID'
3487: ,'MTL_SYSTEM_ITEMS_INTERFACE'
3488: ,'INV_IOI_ERR'
3489: ,x_err_text);
3490: END IF;
3491: END IF; --IF l_raise_create_nir AND NOT l_error_occured THEN
3490: END IF;
3491: END IF; --IF l_raise_create_nir AND NOT l_error_occured THEN
3492:
3493: IF l_update_msb AND NOT l_error_occured THEN
3494: UPDATE mtl_system_items_b
3495: SET approval_status = 'A'
3496: WHERE inventory_item_id = p_inventory_item_id
3497: AND organization_id = p_organization_id;
3498: END IF;
3595: SELECT new_item_req_change_type_id INTO l_type_id
3596: FROM mtl_item_catalog_groups_b
3597: WHERE item_catalog_group_id = p_item_catalog_group_id;
3598:
3599: UPDATE MTL_SYSTEM_ITEMS_B
3600: SET INVENTORY_ITEM_STATUS_CODE='Pending'
3601: ,APPROVAL_STATUS = 'N'
3602: ,CURRENT_PHASE_ID = DECODE(LIFECYCLE_ID,NULL,NULL,Get_Initial_Lifecycle_Phase(LIFECYCLE_ID))
3603: WHERE INVENTORY_ITEM_ID = p_inventory_item_id
3663: l_msg_count NUMBER;
3664: l_inv_debug_level NUMBER := INVPUTLI.get_debug_level; --Bug: 4667452
3665: l_proc_flag NUMBER;
3666: BEGIN
3667: UPDATE MTL_SYSTEM_ITEMS_B
3668: SET INVENTORY_ITEM_STATUS_CODE='Pending'
3669: ,APPROVAL_STATUS = 'N'
3670: ,CURRENT_PHASE_ID = DECODE(LIFECYCLE_ID,NULL,NULL,Get_Initial_Lifecycle_Phase(LIFECYCLE_ID))
3671: WHERE INVENTORY_ITEM_ID = p_inventory_item_id
3670: ,CURRENT_PHASE_ID = DECODE(LIFECYCLE_ID,NULL,NULL,Get_Initial_Lifecycle_Phase(LIFECYCLE_ID))
3671: WHERE INVENTORY_ITEM_ID = p_inventory_item_id
3672: AND ORGANIZATION_ID = p_organization_id;
3673:
3674: UPDATE mtl_system_items_interface
3675: SET process_flag = 5
3676: WHERE rowid = p_row_id;
3677:
3678: ENG_NEW_ITEM_REQ_UTIL.Create_New_Item_Requests( p_batch_id => p_xset_id
3683:
3684: x_return_status := l_return_status;
3685:
3686: IF (l_return_status = FND_API.G_RET_STS_SUCCESS )THEN
3687: UPDATE mtl_system_items_interface
3688: SET process_flag = p_process_flag
3689: WHERE rowid = p_row_id;
3690: ELSE
3691: IF l_inv_debug_level IN(101, 102) THEN
3692: INVPUTLI.info('INV_EGO_REVISION_VALIDATE.Create_New_Item_Request: Following exception from CM during NIR creation');
3693: INVPUTLI.info(l_err_text);
3694: END IF;
3695:
3696: UPDATE mtl_system_items_interface
3697: SET process_flag = 3,
3698: change_id = NULL,
3699: change_line_id = NULL
3700: WHERE rowid = p_row_id;
3707: END IF;
3708:
3709: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3710:
3711: UPDATE mtl_system_items_interface
3712: SET process_flag = 3,
3713: change_id = NULL,
3714: change_line_id = NULL
3715: WHERE rowid = p_row_id;
3745: organization_id,
3746: transaction_type,
3747: request_id,
3748: transaction_id
3749: FROM mtl_system_items_interface
3750: WHERE rowid = cp_row_id;
3751:
3752: CURSOR c_master_org (cp_organization_id IN NUMBER)
3753: IS
3759: Cp_organization_id IN NUMBER)
3760: IS
3761: SELECT style_item_flag,style_item_id,
3762: item_catalog_group_id
3763: FROM mtl_system_items_b
3764: WHERE inventory_item_id = cp_inventory_item_id
3765: AND organization_id = cp_organization_id;
3766:
3767: l_user_id NUMBER := FND_GLOBAL.User_Id;
3825: l_request_id,
3826: l_transaction_id,
3827: l_err_text,
3828: 'ITEM_CATALOG_GROUP_ID',
3829: 'MTL_SYSTEM_ITEMS_INTERFACE',
3830: 'INV_STYLE_SKU_REQUIRED_ICC',
3831: X_Err_Text);
3832: l_error_exists := 1;
3833: END IF;
3846: l_request_id,
3847: l_transaction_id,
3848: l_err_text,
3849: 'STYLE_ITEM_FLAG',
3850: 'MTL_SYSTEM_ITEMS_INTERFACE',
3851: 'INV_STYLE_SKU_NOT_UPD',
3852: X_Err_Text);
3853: l_error_exists := 1;
3854: END IF;
3867: l_request_id,
3868: l_transaction_id,
3869: l_err_text,
3870: 'STYLE_ITEM_FLAG',
3871: 'MTL_SYSTEM_ITEMS_INTERFACE',
3872: 'INV_STYLE_ID_NOT_UPD',
3873: X_Err_Text);
3874: l_error_exists := 1;
3875: END IF;
3887: l_request_id,
3888: l_transaction_id,
3889: l_err_text,
3890: 'STYLE_ITEM_FLAG',
3891: 'MTL_SYSTEM_ITEMS_INTERFACE',
3892: 'INV_STYLE_NOT_GDSN',
3893: X_Err_Text);
3894: l_error_exists := 1;
3895: END IF;
3898: (l_transaction_type = 'UPDATE' AND NVL(l_msi_icc_id,l_null_icc_id) <> NVL(l_msii_icc_id,l_null_icc_id)) )THEN
3899: /* Styles to be created in only those ICCs that contain Variant AGs - Sec 2.5.1 -Condition 2 */
3900: IF l_msii_style_item_flag = 'Y' THEN
3901: SELECT COUNT(*) INTO l_sku_exists
3902: FROM mtl_system_items_b
3903: WHERE style_item_id = l_msii_inv_item_id
3904: AND organization_id = l_msii_org_id;
3905:
3906: IF l_sku_exists <> 0 THEN
3912: l_request_id,
3913: l_transaction_id,
3914: l_err_text,
3915: 'ITEM_CATALOG_GROUP_ID',
3916: 'MTL_SYSTEM_ITEMS_INTERFACE',
3917: 'INV_STYLE_ICC_SKU_EXISTS',
3918: X_Err_Text);
3919: l_error_exists := 1;
3920: ELSE
3939: l_request_id,
3940: l_transaction_id,
3941: l_err_text,
3942: 'ITEM_CATALOG_GROUP_ID',
3943: 'MTL_SYSTEM_ITEMS_INTERFACE',
3944: 'INV_STYLE_INVALID_ICC',
3945: X_Err_Text);
3946: l_error_exists := 1;
3947: ELSE
3963: l_request_id,
3964: l_transaction_id,
3965: l_err_text,
3966: 'ITEM_CATALOG_GROUP_ID',
3967: 'MTL_SYSTEM_ITEMS_INTERFACE',
3968: 'INV_IOI_ERR',
3969: X_Err_Text);
3970: l_error_exists := 1;
3971: END IF; --Create
3974: END IF; --Style Item
3975: ELSE
3976: BEGIN
3977: SELECT item_catalog_group_id INTO l_style_icc_id
3978: FROM mtl_system_items_b
3979: WHERE inventory_item_id = l_msii_style_item_id
3980: AND organization_id = l_msii_org_id;
3981: EXCEPTION
3982: WHEN no_data_found THEN
3995: l_request_id,
3996: l_transaction_id,
3997: l_err_text,
3998: 'ITEM_CATALOG_GROUP_ID',
3999: 'MTL_SYSTEM_ITEMS_INTERFACE',
4000: 'INV_SKU_INVALID_ICC',
4001: X_Err_Text);
4002: l_error_exists := 1;
4003: END IF;
4026: l_request_id,
4027: l_transaction_id,
4028: l_err_text,
4029: 'VARIANT_ATTRIBUTE_COMB',
4030: 'MTL_SYSTEM_ITEMS_INTERFACE',
4031: 'INV_SKU_VAR_NO_UNIQUE',
4032: X_Err_Text);
4033: l_error_exists := 1;
4034: ELSIF l_error_logged <> 0 THEN
4040: l_request_id,
4041: l_transaction_id,
4042: l_err_text,
4043: 'VARIANT_ATTRIBUTE_COMB',
4044: 'MTL_SYSTEM_ITEMS_INTERFACE',
4045: 'INV_IOI_ERR',
4046: X_Err_Text);
4047: l_error_exists := 1;
4048: ELSIF l_var_attrs_missing = TRUE THEN
4054: l_request_id,
4055: l_transaction_id,
4056: l_err_text,
4057: 'VARIANT_ATTRIBUTE_COMB',
4058: 'MTL_SYSTEM_ITEMS_INTERFACE',
4059: 'INV_SKU_VAR_ATTR_MISSING',
4060: X_Err_Text);
4061: l_error_exists := 1;
4062: END IF;
4063: END IF;
4064: END IF;
4065:
4066: IF l_error_exists = 1 THEN
4067: UPDATE mtl_system_items_interface
4068: SET process_flag = 3
4069: WHERE rowid = p_row_id;
4070: END IF;
4071: