[Home] [Help]
20: status NUMBER;
21: l_sysdate DATE ;
22: l_nir_rec_exists BOOLEAN;
23: l_flag_error NUMBER;
24: l_item_approval_status mtl_system_items_b.approval_status%TYPE; --Bug 4418037
25: l_dumm_status NUMBER;
26: l_inv_debug_level NUMBER := INVPUTLI.get_debug_level; --Bug: 4667452
27: l_err_text VARCHAR2(1000);
28:
38: -- micb.NEW_ITEM_REQ_CHANGE_TYPE_ID,
39: mp.ORGANIZATION_CODE,
40: mp.MASTER_ORGANIZATION_ID,
41: msii.TRANSACTION_TYPE
42: FROM MTL_SYSTEM_ITEMS_INTERFACE msii,
43: --MTL_ITEM_CATALOG_GROUPS_B micb,
44: MTL_PARAMETERS mp
45: WHERE ( (msii.organization_id + 0 = org_id) OR (all_Org = 1) )
46: AND msii.process_flag = cp_process_flag
94:
95: BEGIN
96: SELECT approval_status
97: INTO l_item_approval_status
98: FROM mtl_system_items_b
99: WHERE inventory_item_id = cur.INVENTORY_ITEM_ID
100: AND organization_id = cur.ORGANIZATION_ID;
101: EXCEPTION
102: WHEN OTHERS THEN
109:
110: BEGIN
111: SELECT msi.approval_status
112: INTO l_item_approval_status
113: FROM mtl_system_items_b msi
114: WHERE msi.inventory_item_id = cur.inventory_item_id
115: AND msi.organization_id = (SELECT mp.master_organization_id
116: FROM mtl_parameters mp
117: WHERE mp.organization_id = cur.organization_id);
120: l_item_approval_status := 'N';
121: END;
122:
123: IF l_item_approval_status <> 'A' THEN
124: UPDATE mtl_system_items_interface
125: SET process_flag = 3
126: WHERE CURRENT OF c_get_processed_records;
127:
128: l_dumm_status := INVPUOPI.mtl_log_interface_err (
134: request_id,
135: cur.transaction_id,
136: 'Unapproved Item cannot be assigned to a child org',
137: 'APPROVAL_STATUS',
138: 'MTL_SYSTEM_ITEMS_INTERFACE',
139: 'INV_NIR_NO_APPROVE',
140: l_err_text);
141: IF l_dumm_status < 0 then
142: raise LOGGING_ERR;
151: OR((cur.TRANSACTION_TYPE = 'UPDATE') AND (NVL(l_item_approval_status, 'A') <> 'A')))
152: THEN
153: l_nir_rec_exists := true;
154:
155: UPDATE MTL_SYSTEM_ITEMS_INTERFACE I
156: SET I.SET_PROCESS_ID = I.SET_PROCESS_ID + 3000000000000,
157: I.ENG_ITEM_FLAG = 'Y', --Bug 5519768
158: I.SUMMARY_FLAG = 'Y',
159: I.ENABLED_FLAG = 'Y',
497: END IF;
498:
499: IF (status = 0) THEN
500:
501: UPDATE MTL_SYSTEM_ITEMS_INTERFACE
502: --SET PROCESS_FLAG = 4 BUG 7255713
503: SET PROCESS_FLAG = 44 -- added for bug 7255713-ccsingh
504: WHERE PROCESS_FLAG = 41
505: AND ((SET_PROCESS_ID >= 3000000000000) OR (SET_PROCESS_ID = 3000000000000-999));
523: END IF ;
524:
525: IF (status = 0) THEN
526:
527: UPDATE MTL_SYSTEM_ITEMS_INTERFACE
528: SET PROCESS_FLAG = 4
529: WHERE PROCESS_FLAG = 45 --Changed the value from 41 to 45 as we need to validate primary attributes
530: AND ((SET_PROCESS_ID >= 3000000000000) OR (SET_PROCESS_ID = 3000000000000-999));
531:
575: IF l_inv_debug_level IN(101, 102) THEN
576: INVPUTLI.info('INVNIRIS.mtl_pr_validate_item: done with status :'||status);
577: END IF;
578:
579: UPDATE mtl_system_items_interface
580: SET SET_PROCESS_ID = xset_id
581: WHERE ((SET_PROCESS_ID >= 3000000000000) OR (SET_PROCESS_ID = 3000000000000-999));
582:
583: UPDATE mtl_item_revisions_interface
635: ,msb.eng_item_flag -- 5306178
636: ,msb.style_item_flag
637: ,msb.style_item_id
638: ,msb.gdsn_outbound_enabled_flag
639: FROM mtl_system_items_interface msi,
640: mtl_system_items_b msb
641: WHERE msi.process_flag = 1
642: AND msi.set_process_id = xset_id
643: AND ((msi.organization_id = org_id) or (all_org = 1))
636: ,msb.style_item_flag
637: ,msb.style_item_id
638: ,msb.gdsn_outbound_enabled_flag
639: FROM mtl_system_items_interface msi,
640: mtl_system_items_b msb
641: WHERE msi.process_flag = 1
642: AND msi.set_process_id = xset_id
643: AND ((msi.organization_id = org_id) or (all_org = 1))
644: AND msi.transaction_type = 'UPDATE'
648:
649: CURSOR c_check_attributes_policy IS
650: SELECT msi.rowid
651: ,msi.*
652: FROM mtl_system_items_interface msi
653: WHERE msi.process_flag = 1
654: AND msi.set_process_id = xset_id
655: AND ((msi.organization_id = org_id) or (all_org = 1))
656: AND msi.transaction_type = 'UPDATE';
664:
665:
666: cursor c_hold_status_codes is
667: SELECT msi.inventory_item_status_code, msi.eng_item_flag, msi.rowid
668: FROM mtl_system_items_interface msi
669: WHERE msi.process_flag = 1
670: AND msi.set_process_id = xset_id
671: AND ((msi.organization_id = org_id) or (all_org = 1))
672: AND msi.transaction_type = 'UPDATE';
672: AND msi.transaction_type = 'UPDATE';
673:
674: l_process_control VARCHAR2(50) := INV_EGO_REVISION_VALIDATE.Get_Process_Control;
675: l_ret_code NUMBER := 1;
676: l_msb_rec mtl_system_items%ROWTYPE;
677: l_attr_grps VARCHAR2(200);
678: l_eng_object VARCHAR2(10);
679: l_policy_value VARCHAR2(100);
680: l_error_logged NUMBER := 0;
691: l_structure_type_id NUMBER; --5216971
692: l_values_provided BOOLEAN := FALSE;
693: l_desc_status_change BOOLEAN := FALSE;
694: l_rowid ROWID;
695: l_status_code mtl_system_items_b.inventory_item_status_code%TYPE;
696: l_eng_item_flag mtl_system_items_b.eng_item_flag%TYPE;
697:
698: --Bug: 5532737
699: l_attr_grp_name EGO_ATTR_GROUPS_V.ATTR_GROUP_DISP_NAME%TYPE;
692: l_values_provided BOOLEAN := FALSE;
693: l_desc_status_change BOOLEAN := FALSE;
694: l_rowid ROWID;
695: l_status_code mtl_system_items_b.inventory_item_status_code%TYPE;
696: l_eng_item_flag mtl_system_items_b.eng_item_flag%TYPE;
697:
698: --Bug: 5532737
699: l_attr_grp_name EGO_ATTR_GROUPS_V.ATTR_GROUP_DISP_NAME%TYPE;
700: l_ch_policy_found BOOLEAN := FALSE;
718:
719: BEGIN
720:
721: -- Bug 10404086 : Added below hint
722: UPDATE /*+ first_rows index(MTL_SYSTEM_ITEMS_INTERFACE,MTL_SYSTEM_ITEMS_INTERFACE_N3) */
723: MTL_SYSTEM_ITEMS_INTERFACE
724: SET transaction_id = MTL_SYSTEM_ITEMS_INTERFACE_S.NEXTVAL
725: WHERE transaction_id IS NULL
726: AND set_process_id = xset_id;
719: BEGIN
720:
721: -- Bug 10404086 : Added below hint
722: UPDATE /*+ first_rows index(MTL_SYSTEM_ITEMS_INTERFACE,MTL_SYSTEM_ITEMS_INTERFACE_N3) */
723: MTL_SYSTEM_ITEMS_INTERFACE
724: SET transaction_id = MTL_SYSTEM_ITEMS_INTERFACE_S.NEXTVAL
725: WHERE transaction_id IS NULL
726: AND set_process_id = xset_id;
727:
720:
721: -- Bug 10404086 : Added below hint
722: UPDATE /*+ first_rows index(MTL_SYSTEM_ITEMS_INTERFACE,MTL_SYSTEM_ITEMS_INTERFACE_N3) */
723: MTL_SYSTEM_ITEMS_INTERFACE
724: SET transaction_id = MTL_SYSTEM_ITEMS_INTERFACE_S.NEXTVAL
725: WHERE transaction_id IS NULL
726: AND set_process_id = xset_id;
727:
728: --Assign Item Id and apply template.
748: OPEN c_hold_status_codes;
749:
750: --Default certain basic attributes which are required for policy check.
751: FOR cur IN c_populate_values LOOP
752: UPDATE mtl_system_items_interface
753: SET lifecycle_id = DECODE(lifecycle_id,NULL,cur.lifecycle_id,-999999,NULL,lifecycle_id)
754: ,current_phase_id = DECODE(current_phase_id,NULL,cur.current_phase_id,-999999,NULL,current_phase_id)
755: ,item_catalog_group_id = DECODE(item_catalog_group_id,NULL,cur.item_catalog_group_id,-999999, NULL,item_catalog_group_id)
756: ,inventory_item_status_code = NVL(inventory_item_status_code,cur.inventory_item_status_code)
807: LOOP
808: FETCH c_hold_status_codes
809: INTO l_status_code, l_eng_item_flag, l_rowid ;
810: EXIT WHEN c_hold_status_codes%NOTFOUND;
811: UPDATE mtl_system_items_interface
812: SET inventory_item_status_code = l_status_code
813: ,eng_item_flag = l_eng_item_flag
814: WHERE rowid = l_rowid;
815: END LOOP;
838: l_Err_Text := NULL;
839: l_error_logged := 0;
840:
841: SELECT * INTO l_msb_rec
842: FROM mtl_system_items
843: WHERE inventory_item_id = cur.inventory_item_id
844: AND organization_id = cur.organization_id;
845:
846: --5367962 Check for attribute changes if:
1309: l_policy_value := 'CHANGE_ORDER_REQUIRED';
1310: END IF; --cur.current_phase_id is not NULL
1311:
1312: IF l_policy_value = 'NOT_ALLOWED' THEN
1313: UPDATE mtl_system_items_interface
1314: SET process_flag = 3
1315: WHERE rowid = cur.rowid;
1316:
1317: SELECT ATTR_GROUP_DISP_NAME INTO l_attr_grp_name
1349: --4696529: Insert row into interface table to propogate changes to production.
1350: IF l_msb_rec.inventory_item_status_code <> NVL(cur.inventory_item_status_code,l_msb_rec.inventory_item_status_code)
1351: OR l_msb_rec.description <> NVL(cur.description,l_msb_rec.description)
1352: THEN
1353: INSERT INTO mtl_system_items_interface(
1354: organization_id
1355: ,inventory_item_id
1356: ,process_flag
1357: ,set_process_id
1366: ,cur.inventory_item_id
1367: ,1
1368: ,cur.set_process_id
1369: ,'UPDATE'
1370: ,mtl_system_items_interface_s.nextval
1371: ,cur.inventory_item_status_code --Bug 5383744
1372: ,cur.allowed_units_lookup_code
1373: ,cur.item_type
1374: ,NVL(cur.description,l_msb_rec.description));
1373: ,cur.item_type
1374: ,NVL(cur.description,l_msb_rec.description));
1375: END IF;
1376:
1377: UPDATE mtl_system_items_interface
1378: SET process_flag = 5,
1379: --bug 5383744
1380: inventory_item_status_code = NVL(cur.inventory_item_status_code,l_msb_rec.inventory_item_status_code)
1381: WHERE rowid = cur.rowid;
1400: --Start: 5383744: Donot move 5 for no attrib values
1401: FOR cur IN c_check_attributes_policy LOOP
1402:
1403: SELECT * INTO l_msb_rec
1404: FROM mtl_system_items
1405: WHERE inventory_item_id = cur.inventory_item_id
1406: AND organization_id = cur.organization_id;
1407:
1408: --Inventory Attribute group
1716: l_desc_status_change := FALSE;
1717: END IF;
1718:
1719: IF l_values_provided THEN
1720: UPDATE mtl_system_items_interface msi
1721: SET process_flag = 5,
1722: --Bug 5383744
1723: inventory_item_status_code = NVL(cur.inventory_item_status_code,l_msb_rec.inventory_item_status_code)
1724: WHERE rowid = cur.rowid;
1723: inventory_item_status_code = NVL(cur.inventory_item_status_code,l_msb_rec.inventory_item_status_code)
1724: WHERE rowid = cur.rowid;
1725:
1726: IF l_desc_status_change THEN
1727: INSERT INTO mtl_system_items_interface(
1728: organization_id
1729: ,inventory_item_id
1730: ,process_flag
1731: ,set_process_id
1740: ,cur.inventory_item_id
1741: ,1
1742: ,cur.set_process_id
1743: ,'UPDATE'
1744: ,mtl_system_items_interface_s.nextval
1745: ,cur.inventory_item_status_code --Bug 5383744
1746: ,cur.allowed_units_lookup_code
1747: ,cur.item_type
1748: ,NVL(cur.description,l_msb_rec.description));
1749: END IF;
1750:
1751: ELSIF NOT l_desc_status_change THEN
1752:
1753: UPDATE mtl_system_items_interface msi
1754: SET process_flag = 7
1755: WHERE rowid = cur.rowid;
1756:
1757: END IF; -- IF l_values_provided THEN