[Home] [Help]
1: PACKAGE BODY INVUPD2B AS
2: /* $Header: INVUPD2B.pls 120.28.12010000.3 2008/11/25 02:39:01 akbharga ship $ */
3: -- GlobalVar to hold control level for each attribute Bug Fix 3005880
4:
5: A_ALLOWED_UNITS_LOOKUP_CODE NUMBER := 2;
1: PACKAGE BODY INVUPD2B AS
2: /* $Header: INVUPD2B.pls 120.28.12010000.3 2008/11/25 02:39:01 akbharga ship $ */
3: -- GlobalVar to hold control level for each attribute Bug Fix 3005880
4:
5: A_ALLOWED_UNITS_LOOKUP_CODE NUMBER := 2;
6: A_INVENTORY_ITEM_STATUS_CODE NUMBER := 2;
389:
390: BEGIN
391:
392: IF l_inv_debug_level IN(101, 102) THEN
393: INVPUTLI.info('INVUPD2B: Inside validate_item_update_master'|| '***orgid: ' || TO_CHAR(org_id));
394: END IF;
395:
396: -- Set the attribute level for those attributes under master control.
397: -- Bug fix 3005880
708:
709: tmp_xset_id := tmp_xset_id +1 ; --5351611
710:
711: -- call additional update validations on master record
712: ret_code_update := INVUPD2B.update_validations(
713: rec.ROWID,
714: rec.ORGANIZATION_ID,
715: t_trans_id,
716: user_id,
724: WHERE rowid = rec.rowid;
725:
726:
727: if (ret_code_update = 0 AND m_process_flag=4) THEN
728: ret_code := INVUPD2B.check_child_records(
729: rec.ROWID,
730: rec.INVENTORY_ITEM_ID,
731: rec.ORGANIZATION_ID,
732: t_trans_id,
765: null,
766: 'MTL_SYSTEM_ITEMS_INTERFACE',
767: 'INV_CHILD_VIOLATION_ERROR',
768: err_text);
769: dumm_status := INVUPD2B.set_process_flag3(rec.ROWID,user_id,login_id,prog_appid,prog_id,request_id);
770: end if;
771:
772: end if;
773:
1405: IF (c_master_attributes%ISOPEN) THEN
1406: CLOSE c_master_attributes;
1407: END IF;
1408: check_create_child := TRUE;
1409: INVPUTLI.info('INVUPD2B.check_create_child_records : Exception ' || SQLERRM );
1410:
1411: END Check_create_child_records;
1412:
1413: FUNCTION check_child_records
1457:
1458: BEGIN
1459:
1460: IF l_inv_debug_level IN(101, 102) THEN
1461: INVPUTLI.info('INVUPD2B: Inside check_child_records'|| '***orgid: ' || TO_CHAR(org_id));
1462: END IF;
1463:
1464: -- for each record in msi which is a child record of the item
1465:
1469: item_id => crec.inventory_item_id,
1470: org_id => crec.organization_id,
1471: check_create_child => l_check_create_child);
1472: IF l_check_create_child = TRUE THEN
1473: ret_code := INVUPD2B.create_child_update_mast_attr(master_row_id, crec.INVENTORY_ITEM_ID, crec.ORGANIZATION_ID, xset_id);
1474: l_created_child := TRUE;
1475: END IF;
1476: end loop; -- } -- msi_child loop
1477:
1501: err_text,
1502: xset_id + 1000000000000);
1503:
1504: for rec in C_msii_forupdate_records loop
1505: ret_code_update := INVUPD2B.update_validations(
1506: rec.ROWID,
1507: rec.ORGANIZATION_ID,
1508: trans_id,
1509: user_id,
1556: l_inv_debug_level NUMBER := INVPUTLI.get_debug_level; --Bug: 4667452
1557: BEGIN
1558:
1559: IF l_inv_debug_level IN(101, 102) THEN
1560: INVPUTLI.info('INVUPD2B: Inside create_child_update_mast_attr'|| '***orgid: ' || TO_CHAR(org_id));
1561: END IF;
1562: -- Insert msi data onto MSII for child record.
1563: -- Need to verify these attributes.
1564: --
2368:
2369: --commit;
2370:
2371: -- copy master item data to child record created in msii
2372: ret_code := INVUPD2B.copy_master_to_child(master_row_id, inv_item_id, org_id, xset_id);
2373:
2374: --commit;
2375:
2376: RETURN (ret_code);
2412:
2413: BEGIN
2414:
2415: IF l_inv_debug_level IN(101, 102) THEN
2416: INVPUTLI.info('INVUPD2B: Inside copy_master_to_child'|| '***orgid: ' || TO_CHAR(org_id));
2417: END IF;
2418:
2419: -- Get master record info into msii_master_temp
2420: --
3448:
3449: BEGIN -- {
3450:
3451: IF l_inv_debug_level IN(101, 102) THEN
3452: INVPUTLI.info('INVUPD2B: Inside validate_item_update_child'|| '***orgid: ' || TO_CHAR(org_id));
3453: END IF;
3454:
3455: for crec in C_msii_child_records loop -- {
3456: -- child record validation here
3453: END IF;
3454:
3455: for crec in C_msii_child_records loop -- {
3456: -- child record validation here
3457: ret_code_update := INVUPD2B.update_validations(
3458: crec.ROWID,
3459: crec.ORGANIZATION_ID,
3460: crec.TRANSACTION_ID,
3461: user_id,
3578: l_item_has_lot_comp NUMBER;
3579: BEGIN
3580:
3581: IF l_inv_debug_level IN(101, 102) THEN
3582: INVPUTLI.info('INVUPD2B: Inside update_validations'|| '***orgid: ' || TO_CHAR(org_id));
3583: END IF;
3584: -- initialize status = 0
3585: status := 0;
3586:
3850: X_serial_control => serial_control -- Bug 6501149
3851: );
3852:
3853: IF l_inv_debug_level IN(101, 102) THEN
3854: INVPUTLI.info('INVUPD2B: Inside update_validations: After Table Quries');
3855: END IF;
3856: -- validate LOT_CONTROL_CODE
3857: -- cannot update if there is onhand or transactions pending or lots exist
3858: if (msi_temp.lot_control_code <> msii_temp.lot_control_code) AND (onhand_lot = 1) then
3869: 'MTL_SYSTEM_ITEMS_INTERFACE',
3870: 'INV_LOT_QOH_CANNOT_UPDATE',
3871: err_text);
3872:
3873: dumm_status := INVUPD2B.set_process_flag3(row_id,user_id,login_id,prog_appid,prog_id,request_id);
3874:
3875: /* Bug 3058650 : Validation added as part of Item attribute checks ER */
3876: elsif (msi_temp.lot_control_code <> msii_temp.lot_control_code) AND (pendadj_lot = 1) then
3877: dumm_status := INVPUOPI.mtl_log_interface_err(
3887: 'MTL_SYSTEM_ITEMS_INTERFACE',
3888: 'INV_PENDING_ADJUSTMENT',
3889: err_text);
3890:
3891: dumm_status := INVUPD2B.set_process_flag3(row_id,user_id,login_id,prog_appid,prog_id,request_id);
3892:
3893: /* End Bug 3058650 */
3894: elsif (msi_temp.lot_control_code <> msii_temp.lot_control_code) AND (intr_ship_lot = 1) then -- Bug 4387538
3895: dumm_status := INVPUOPI.mtl_log_interface_err(
3905: 'MTL_SYSTEM_ITEMS_INTERFACE',
3906: 'INV_INTRANSIT_CANNOT_UPDATE',
3907: err_text);
3908:
3909: dumm_status := INVUPD2B.set_process_flag3(row_id,user_id,login_id,prog_appid,prog_id,request_id);
3910: /*Bug 6501149 Added code */
3911: elsif (msi_temp.lot_control_code <> msii_temp.lot_control_code) AND (lot_control = 1) then
3912: dumm_status := INVPUOPI.mtl_log_interface_err(
3913: org_id,
3922: 'MTL_SYSTEM_ITEMS_INTERFACE',
3923: 'INV_DELIVER_CANNOT_UPDATE',
3924: err_text);
3925:
3926: dumm_status := INVUPD2B.set_process_flag3(row_id,user_id,login_id,prog_appid,prog_id,request_id);
3927: /* Bug 6501149 Code ended */
3928: end if;
3929:
3930: /* Start Bug 3713912 */
3944: 'MTL_SYSTEM_ITEMS_INTERFACE',
3945: 'INV_LOT_QOH_CANNOT_UPDATE',
3946: err_text);
3947:
3948: dumm_status := INVUPD2B.set_process_flag3(row_id,user_id,login_id,prog_appid,prog_id,request_id);
3949:
3950: elsif (msi_temp.child_lot_flag <> msii_temp.child_lot_flag) AND (pendadj_child_lot = 1) then
3951: dumm_status := INVPUOPI.mtl_log_interface_err(
3952: org_id,
3961: 'MTL_SYSTEM_ITEMS_INTERFACE',
3962: 'INV_PENDING_ADJUSTMENT',
3963: err_text);
3964:
3965: dumm_status := INVUPD2B.set_process_flag3(row_id,user_id,login_id,prog_appid,prog_id,request_id);
3966:
3967: end if;
3968:
3969: -- validate GRADE_CONTROL_FLAG
3982: 'MTL_SYSTEM_ITEMS_INTERFACE',
3983: 'INV_LOT_QOH_CANNOT_UPDATE',
3984: err_text);
3985:
3986: dumm_status := INVUPD2B.set_process_flag3(row_id,user_id,login_id,prog_appid,prog_id,request_id);
3987:
3988: elsif (msi_temp.grade_control_flag <> msii_temp.grade_control_flag) AND (pendadj_grade = 1) then
3989: dumm_status := INVPUOPI.mtl_log_interface_err(
3990: org_id,
3999: 'MTL_SYSTEM_ITEMS_INTERFACE',
4000: 'INV_PENDING_ADJUSTMENT',
4001: err_text);
4002:
4003: dumm_status := INVUPD2B.set_process_flag3(row_id,user_id,login_id,prog_appid,prog_id,request_id);
4004:
4005: end if;
4006:
4007: -- validate LOT_DIVISIBLE_FLAG
4020: 'MTL_SYSTEM_ITEMS_INTERFACE',
4021: 'INV_LOT_QOH_CANNOT_UPDATE',
4022: err_text);
4023:
4024: dumm_status := INVUPD2B.set_process_flag3(row_id,user_id,login_id,prog_appid,prog_id,request_id);
4025:
4026: elsif (msi_temp.lot_divisible_flag <> msii_temp.lot_divisible_flag) AND (pendadj_lot_divisible = 1) then
4027: dumm_status := INVPUOPI.mtl_log_interface_err(
4028: org_id,
4037: 'MTL_SYSTEM_ITEMS_INTERFACE',
4038: 'INV_PENDING_ADJUSTMENT',
4039: err_text);
4040:
4041: dumm_status := INVUPD2B.set_process_flag3(row_id,user_id,login_id,prog_appid,prog_id,request_id);
4042:
4043: end if;
4044:
4045: -- validate SHELF_LIFE_CODE
4065: 'MTL_SYSTEM_ITEMS_INTERFACE',
4066: 'INV_LOT_QOH_CANNOT_UPDATE',
4067: err_text);
4068:
4069: dumm_status := INVUPD2B.set_process_flag3(row_id,user_id,login_id,prog_appid,prog_id,request_id);
4070: end if;
4071:
4072: /* End Bug 3713912 */
4073:
4089: 'MTL_SYSTEM_ITEMS_INTERFACE',
4090: 'INV_LOC_CONTROL_CODE_ERR',
4091: err_text);
4092:
4093: dumm_status := INVUPD2B.set_process_flag3(row_id,user_id,login_id,prog_appid,prog_id,request_id);
4094:
4095: elsif (msi_temp.LOCATION_CONTROL_CODE <> msii_temp.LOCATION_CONTROL_CODE) AND (pendadj_loc = 1) then
4096: dumm_status := INVPUOPI.mtl_log_interface_err(
4097: org_id,
4106: 'MTL_SYSTEM_ITEMS_INTERFACE',
4107: 'INV_PENDING_ADJUSTMENT',
4108: err_text);
4109:
4110: dumm_status := INVUPD2B.set_process_flag3(row_id,user_id,login_id,prog_appid,prog_id,request_id);
4111:
4112: /* End Bug 3058650 */
4113:
4114: end if;
4130: 'MTL_SYSTEM_ITEMS_INTERFACE',
4131: 'INV_RESERVABLE_TYPE_ERR',
4132: err_text);
4133:
4134: dumm_status := INVUPD2B.set_process_flag3(row_id,user_id,login_id,prog_appid,prog_id,request_id);
4135: end if;
4136:
4137: /* Bug 3058650 : Validation added as part of Item attribute checks ER */
4138:
4152: 'RESERVABLE_TYPE',
4153: 'MTL_SYSTEM_ITEMS_INTERFACE',
4154: 'INV_RESERVABLE_NO_YES',
4155: err_text);
4156: dumm_status := INVUPD2B.set_process_flag3(row_id,user_id,login_id,prog_appid,prog_id,request_id);
4157: end if;
4158: end if;
4159:
4160:
4180: 'TRANSACTIONS_ENABLED_FLAG',
4181: 'MTL_SYSTEM_ITEMS_INTERFACE',
4182: 'INV_TRANSACTABLE_YES_NO',
4183: err_text);
4184: dumm_status := INVUPD2B.set_process_flag3(row_id,user_id,login_id,prog_appid,prog_id,request_id);
4185: end if;
4186:
4187: end if;
4188: /* End Bug 3058650 */
4207: 'SHIPPABLE_ITEM_FLAG',
4208: 'MTL_SYSTEM_ITEMS_INTERFACE',
4209: 'INV_OPEN_SO',
4210: err_text);
4211: dumm_status := INVUPD2B.set_process_flag3(row_id,user_id,login_id,prog_appid,prog_id,request_id);
4212: elsif (msi_temp.SHIPPABLE_ITEM_FLAG = 'N' ) and (msii_temp.SHIPPABLE_ITEM_FLAG = 'Y') then
4213: if (INV_ATTRIBUTE_CONTROL_PVT.shippable_check(msii_temp.organization_id,
4214: msii_temp.inventory_item_id)) then
4215: dumm_status := INVPUOPI.mtl_log_interface_err(
4224: 'SHIPPABLE_ITEM_FLAG',
4225: 'MTL_SYSTEM_ITEMS_INTERFACE',
4226: 'INV_TRANSACTABLE_NO_YES',
4227: err_text);
4228: dumm_status := INVUPD2B.set_process_flag3(row_id,user_id,login_id,prog_appid,prog_id,request_id);
4229: end if;
4230: end if;
4231:
4232: /* End Bug 3058650 */
4250: 'REPLENISH_TO_ORDER_FLAG',
4251: 'MTL_SYSTEM_ITEMS_INTERFACE',
4252: 'INV_ATO_YES_NO',
4253: err_text);
4254: dumm_status := INVUPD2B.set_process_flag3(row_id,user_id,login_id,prog_appid,prog_id,request_id);
4255: end if;
4256:
4257: /* End Bug 3058650 */
4258:
4273: 'MTL_SYSTEM_ITEMS_INTERFACE',
4274: 'INV_REV_QTY_CNTRL_CODE_ERR',
4275: err_text);
4276:
4277: dumm_status := INVUPD2B.set_process_flag3(row_id,user_id,login_id,prog_appid,prog_id,request_id);
4278: /*Bug 6501149 Code starts */
4279: elsif (msi_temp.REVISION_QTY_CONTROL_CODE <> msii_temp.REVISION_QTY_CONTROL_CODE) AND (revision_control = 1) then
4280: dumm_status := INVPUOPI.mtl_log_interface_err(
4281: org_id,
4289: 'REVISION_QTY_CONTROL_CODE',
4290: 'MTL_SYSTEM_ITEMS_INTERFACE',
4291: 'INV_DELIVER_CANNOT_UPDATE',
4292: err_text);
4293: dumm_status := INVUPD2B.set_process_flag3(row_id,user_id,login_id,prog_appid,prog_id,request_id);
4294: /*Bug 6501149 Code ends */
4295:
4296: /* Bug 3058650 : Validation added as part of Item attribute checks ER */
4297: elsif (msi_temp.REVISION_QTY_CONTROL_CODE <> msii_temp.REVISION_QTY_CONTROL_CODE) AND (pendadj_rev = 1) then
4308: 'MTL_SYSTEM_ITEMS_INTERFACE',
4309: 'INV_PENDING_ADJUSTMENT',
4310: err_text);
4311:
4312: dumm_status := INVUPD2B.set_process_flag3(row_id,user_id,login_id,prog_appid,prog_id,request_id);
4313: /* End Bug 3058650 */
4314: end if;
4315:
4316:
4338: 'SERIAL_NUMBER_CONTROL_CODE',
4339: 'MTL_SYSTEM_ITEMS_INTERFACE',
4340: 'INV_QTY_ON_HAND',
4341: err_text);
4342: dumm_status := INVUPD2B.set_process_flag3(row_id,user_id,login_id,prog_appid,prog_id,request_id);
4343: elsif (msi_temp.SERIAL_NUMBER_CONTROL_CODE = 1 and
4344: msii_temp.SERIAL_NUMBER_CONTROL_CODE in (2,5,6))then
4345:
4346: if (INV_ATTRIBUTE_CONTROL_PVT.serial_check(msii_temp.organization_id,
4357: 'SERIAL_NUMBER_CONTROL_CODE',
4358: 'MTL_SYSTEM_ITEMS_INTERFACE',
4359: 'INV_SERIAL_NO_YES',
4360: err_text);
4361: dumm_status := INVUPD2B.set_process_flag3(row_id,user_id,login_id,prog_appid,prog_id,request_id);
4362: end if;
4363: /* End Bug 3058650 */
4364:
4365: /* Start Bug 4387538 */
4381: 'MTL_SYSTEM_ITEMS_INTERFACE',
4382: 'INV_INTRANSIT_CANNOT_UPDATE',
4383: err_text);
4384:
4385: dumm_status := INVUPD2B.set_process_flag3(row_id,user_id,login_id,prog_appid,prog_id,request_id);
4386:
4387: /* Bug 6501149 Code start */
4388: elsif ((msi_temp.SERIAL_NUMBER_CONTROL_CODE in (1,6) and msii_temp.SERIAL_NUMBER_CONTROL_CODE in (2,5)) and
4389: (serial_control = 1)) or
4402: 'SERIAL_NUMBER_CONTROL_CODE',
4403: 'MTL_SYSTEM_ITEMS_INTERFACE',
4404: 'INV_DELIVER_CANNOT_UPDATE',
4405: err_text);
4406: dumm_status := INVUPD2B.set_process_flag3(row_id,user_id,login_id,prog_appid,prog_id,request_id);
4407: /* Bug 6501149 Code ended */
4408: end if;
4409: /* End Bug 4387538 */
4410:
4426: 'MTL_SYSTEM_ITEMS_INTERFACE',
4427: 'INV_BOM_ENABLED_FLAG_ERR',
4428: err_text);
4429:
4430: dumm_status := INVUPD2B.set_process_flag3(row_id,user_id,login_id,prog_appid,prog_id,request_id);
4431: end if;
4432:
4433: -- validate BOM_ITEM_TYPE (1)
4434: -- Not updateable when row exists in bom_substitute_components or
4451: 'MTL_SYSTEM_ITEMS_INTERFACE',
4452: 'INV_BOM_ITEM_TYPE_ERR1',
4453: err_text);
4454:
4455: dumm_status := INVUPD2B.set_process_flag3(row_id,user_id,login_id,prog_appid,prog_id,request_id);
4456: end if;
4457:
4458: -- Start : 3436435 Item in BOM,BOM Enabled cannot be N
4459: /* commented for bug 5479302
4471: 'MTL_SYSTEM_ITEMS_INTERFACE',
4472: 'INV_IOI_ITEM_BOM_EXISTS',
4473: err_text);
4474:
4475: dumm_status := INVUPD2B.set_process_flag3(row_id,user_id,login_id,prog_appid,prog_id,request_id);
4476: end if; */
4477: -- End : 3436435 Item in BOM,BOM Enabled cannot be N
4478:
4479: -- validate BOM_ITEM_TYPE (2)
4507: 'MTL_SYSTEM_ITEMS_INTERFACE',
4508: 'INV_BOM_ITEM_TYPE_ERR2',
4509: err_text);
4510:
4511: dumm_status := INVUPD2B.set_process_flag3(row_id,user_id,login_id,prog_appid,prog_id,request_id);
4512:
4513: end if;
4514: */
4515:
4532: 'MTL_SYSTEM_ITEMS_INTERFACE',
4533: 'INV_BOM_ITEM_TYPE_ERR3',
4534: err_text);
4535:
4536: dumm_status := INVUPD2B.set_process_flag3(row_id,user_id,login_id,prog_appid,prog_id,request_id);
4537: end if;
4538:
4539: -- Added for Bug 5143614
4540: -- Validate update of pick_components flag
4567: 'PICK_COMPONENTS_FLAG',
4568: 'MTL_SYSTEM_ITEMS_INTERFACE',
4569: 'INV_ITEM_LOT_COMP',
4570: err_text);
4571: dumm_status := INVUPD2B.set_process_flag3(row_id,user_id,login_id,prog_appid,prog_id,request_id);
4572: END IF;
4573: end if;
4574:
4575: -- validate COSTING_ENABLED_FLAG
4593: 'MTL_SYSTEM_ITEMS_INTERFACE',
4594: 'INV_COSTING_ENABLED_FLAG_ERR',
4595: err_text);
4596:
4597: dumm_status := INVUPD2B.set_process_flag3(row_id,user_id,login_id,prog_appid,prog_id,request_id);
4598: end if;
4599:
4600:
4601: -- validate INVENTORY_ASSET_FLAG
4619: 'MTL_SYSTEM_ITEMS_INTERFACE',
4620: 'INV_INVENTORY_ASSET_FLAG_ERR',
4621: err_text);
4622:
4623: dumm_status := INVUPD2B.set_process_flag3(row_id,user_id,login_id,prog_appid,prog_id,request_id);
4624: end if;
4625:
4626:
4627: -- validate FIXED_ORDER_QUANTITY
4641: 'MTL_SYSTEM_ITEMS_INTERFACE',
4642: 'INV_FIXED_ORDER_QUANTITY_ERR',
4643: err_text);
4644:
4645: dumm_status := INVUPD2B.set_process_flag3(row_id,user_id,login_id,prog_appid,prog_id,request_id);
4646: end if;
4647:
4648:
4649: -- validate PLANNING_MAKE_BUY_CODE
4664: 'MTL_SYSTEM_ITEMS_INTERFACE',
4665: 'INV_PLANNING_MAKE_BUY_CODE',
4666: err_text);
4667:
4668: dumm_status := INVUPD2B.set_process_flag3(row_id,user_id,login_id,prog_appid,prog_id,request_id);
4669: end if;
4670:
4671:
4672: -- validate ACCEPTABLE_EARLY_DAYS
4686: 'MTL_SYSTEM_ITEMS_INTERFACE',
4687: 'INV_ACCEPTABLE_EARLY_DAYS_ERR',
4688: err_text);
4689:
4690: dumm_status := INVUPD2B.set_process_flag3(row_id,user_id,login_id,prog_appid,prog_id,request_id);
4691: end if;
4692:
4693:
4694: -- validate ACCEPTABLE_RATE_INCREASE
4708: 'MTL_SYSTEM_ITEMS_INTERFACE',
4709: 'INV_ACCEPT_RATE_INCREASE_ERR',
4710: err_text);
4711:
4712: dumm_status := INVUPD2B.set_process_flag3(row_id,user_id,login_id,prog_appid,prog_id,request_id);
4713: end if;
4714:
4715:
4716: -- validate ACCEPTABLE_RATE_DECREASE
4730: 'MTL_SYSTEM_ITEMS_INTERFACE',
4731: 'INV_ACCEPT_RATE_DECREASE_ERR',
4732: err_text);
4733:
4734: dumm_status := INVUPD2B.set_process_flag3(row_id,user_id,login_id,prog_appid,prog_id,request_id);
4735: end if;
4736:
4737:
4738: -- validate DEMAND_TIME_FENCE_DAYS
4753: 'MTL_SYSTEM_ITEMS_INTERFACE',
4754: 'INV_DEMAND_TIME_FENCE_DAYS_ERR',
4755: err_text);
4756:
4757: dumm_status := INVUPD2B.set_process_flag3(row_id,user_id,login_id,prog_appid,prog_id,request_id);
4758: end if;
4759:
4760:
4761: -- validate PLANNING_TIME_FENCE_DAYS
4776: 'MTL_SYSTEM_ITEMS_INTERFACE',
4777: 'INV_PLAN_TIME_FENCE_DAYS_ERR',
4778: err_text);
4779:
4780: dumm_status := INVUPD2B.set_process_flag3(row_id,user_id,login_id,prog_appid,prog_id,request_id);
4781: end if;
4782:
4783:
4784: -- validate REPETITIVE_PLANNING_FLAG
4799: 'MTL_SYSTEM_ITEMS_INTERFACE',
4800: 'INV_REPET_PLANNING_FLAG_ERR',
4801: err_text);
4802:
4803: dumm_status := INVUPD2B.set_process_flag3(row_id,user_id,login_id,prog_appid,prog_id,request_id);
4804: end if;
4805:
4806:
4807: -- validate SHRINKAGE_RATE
4823: 'MTL_SYSTEM_ITEMS_INTERFACE',
4824: 'INV_SHRINKAGE_RATE_ERR',
4825: err_text);
4826:
4827: dumm_status := INVUPD2B.set_process_flag3(row_id,user_id,login_id,prog_appid,prog_id,request_id);
4828:
4829: end if;
4830:
4831:
4847: 'MTL_SYSTEM_ITEMS_INTERFACE',
4848: 'INV_FULL_LEAD_TIME_ERR',
4849: err_text);
4850:
4851: dumm_status := INVUPD2B.set_process_flag3(row_id,user_id,login_id,prog_appid,prog_id,request_id);
4852: end if;
4853:
4854:
4855: -- validate LEAD_TIME_LOT_SIZE
4885: 'MTL_SYSTEM_ITEMS_INTERFACE',
4886: 'INV_POSTPROC_LEAD_TIME_ERR',
4887: err_text);
4888:
4889: dumm_status := INVUPD2B.set_process_flag3(row_id,user_id,login_id,prog_appid,prog_id,request_id);
4890: end if;
4891:
4892:
4893: -- validate VARIABLE_LEAD_TIME
4907: 'MTL_SYSTEM_ITEMS_INTERFACE',
4908: 'INV_VARIABLE_LEAD_TIME_ERR',
4909: err_text);
4910:
4911: dumm_status := INVUPD2B.set_process_flag3(row_id,user_id,login_id,prog_appid,prog_id,request_id);
4912: end if;
4913:
4914: /* Validation added for Bug 4139304 - Anmurali */
4915: if ((msi_temp.effectivity_control <> msii_temp.effectivity_control) AND (bom_item = 1 OR onhand_all = 1)) then
4926: 'MTL_SYSTEM_ITEMS_INTERFACE',
4927: 'ITM-EFFC-ITEM IS BILL OR COMP',
4928: err_text);
4929:
4930: dumm_status := INVUPD2B.set_process_flag3(row_id,user_id,login_id,prog_appid,prog_id,request_id);
4931: end if;
4932: /* End of validation for Bug 4139304 */
4933:
4934: --ToDo (SERVICE_ITEM_FLAG) :
4951: 'MTL_SYSTEM_ITEMS_INTERFACE',
4952: 'INV_BUILD_IN_WIP_FLAG_ERR',
4953: err_text);
4954:
4955: dumm_status := INVUPD2B.set_process_flag3(row_id,user_id,login_id,prog_appid,prog_id,request_id);
4956: end if;
4957: */
4958:
4959: -- validate SERVICE_ITEM_FLAG
4974: 'MTL_SYSTEM_ITEMS_INTERFACE',
4975: 'INV_SERVICE_ITEM_FLAG_ERR',
4976: err_text);
4977:
4978: dumm_status := INVUPD2B.set_process_flag3(row_id,user_id,login_id,prog_appid,prog_id,request_id);
4979: end if;
4980: */
4981:
4982: -- validate SERVICE_STARTING_DELAY
4996: 'MTL_SYSTEM_ITEMS_INTERFACE',
4997: 'INV_SERVICE_STARTING_DELAY_ERR',
4998: err_text);
4999:
5000: dumm_status := INVUPD2B.set_process_flag3(row_id,user_id,login_id,prog_appid,prog_id,request_id);
5001: end if;
5002:
5003: -- validate VEHICLE_ITEM_FLAG
5004: -- Not updateable when row exists in FTE_VEHICLE_TYPES
5017: 'MTL_SYSTEM_ITEMS_INTERFACE',
5018: 'INV_VEHICLE_CANNOT_UPDATE',
5019: err_text);
5020:
5021: dumm_status := INVUPD2B.set_process_flag3(row_id,user_id,login_id,prog_appid,prog_id,request_id);
5022: end if;
5023: -- Bug: 2710463
5024: -- validate COMMS_NL_TRACKABLE_FLAG
5025: -- Not updateable when Onhand Trackable exists
5038: 'MTL_SYSTEM_ITEMS_INTERFACE',
5039: 'INV_ONHAND_TRACKABLE_EXIST',
5040: err_text);
5041:
5042: dumm_status := INVUPD2B.set_process_flag3(row_id,user_id,login_id,prog_appid,prog_id,request_id);
5043: end if;
5044:
5045: IF l_inv_debug_level IN(101, 102) THEN
5046: INVPUTLI.info('INVUPD2B: Inside update_validations: 11.5.10 validations started');
5042: dumm_status := INVUPD2B.set_process_flag3(row_id,user_id,login_id,prog_appid,prog_id,request_id);
5043: end if;
5044:
5045: IF l_inv_debug_level IN(101, 102) THEN
5046: INVPUTLI.info('INVUPD2B: Inside update_validations: 11.5.10 validations started');
5047: END IF;
5048: -- Added for 11.5.10
5049: -- validate OUTSIDE_OPERATION_FLAG, EAM_ITEM_TYPE, MTL_TRANSACTIONS_ENABLED_FLAG, STOCK_ENABLED_FLAG and INVENTORY_ASSET_FLAG
5050: -- validate Not updateable fields
5062: 'STOCK_ENABLED_FLAG',
5063: 'MTL_SYSTEM_ITEMS_INTERFACE',
5064: 'INV_DELIVER_CANNOT_UPDATE',
5065: err_text);
5066: dumm_status := INVUPD2B.set_process_flag3(row_id,user_id,login_id,prog_appid,prog_id,request_id);
5067: end if;
5068: /*Bug 6501149 code ends */
5069:
5070: l_column_name := NULL;
5083: l_column_name := 'INVENTORY_ASSET_FLAG';
5084: end if;
5085: if (l_column_name IS NOT NULL) then
5086: IF l_inv_debug_level IN(101, 102) THEN
5087: INVPUTLI.info('INVUPD2B: Inside update_validations: VMI validations failed');
5088: END IF;
5089:
5090: dumm_status := INVPUOPI.mtl_log_interface_err(
5091: org_id,
5099: l_column_name,
5100: 'MTL_SYSTEM_ITEMS_INTERFACE',
5101: 'INV_VMI_ENABLED_ITEM',
5102: err_text);
5103: dumm_status := INVUPD2B.set_process_flag3(row_id,user_id,login_id,prog_appid,prog_id,request_id);
5104: end if;
5105: IF l_inv_debug_level IN(101, 102) THEN
5106: INVPUTLI.info('INVUPD2B: Inside update_validations: 11.5.10 validations Ended');
5107: END IF;
5102: err_text);
5103: dumm_status := INVUPD2B.set_process_flag3(row_id,user_id,login_id,prog_appid,prog_id,request_id);
5104: end if;
5105: IF l_inv_debug_level IN(101, 102) THEN
5106: INVPUTLI.info('INVUPD2B: Inside update_validations: 11.5.10 validations Ended');
5107: END IF;
5108:
5109: /* Start Bug 3713912 */
5110: -- validate TRACKING_QUANTITY_IND
5110: -- validate TRACKING_QUANTITY_IND
5111: -- cannot update if there is onhand or transactions pending
5112: if (msi_temp.TRACKING_QUANTITY_IND <> msii_temp.TRACKING_QUANTITY_IND) AND (onhand_tracking_qty_ind = 1) then
5113: IF l_inv_debug_level IN(101, 102) THEN
5114: INVPUTLI.info('INVUPD2B: Inside update_validations: TRACKING_QUANTITY_IND onhand validations failed');
5115: END IF;
5116: dumm_status := INVPUOPI.mtl_log_interface_err(
5117: org_id,
5118: user_id,
5126: 'MTL_SYSTEM_ITEMS_INTERFACE',
5127: 'INV_QOH_CANNOT_UPDATE',
5128: err_text);
5129:
5130: dumm_status := INVUPD2B.set_process_flag3(row_id,user_id,login_id,prog_appid,prog_id,request_id);
5131:
5132: elsif (msi_temp.TRACKING_QUANTITY_IND <> msii_temp.TRACKING_QUANTITY_IND) AND (pendadj_tracking_qty_ind = 1) then
5133: IF l_inv_debug_level IN(101, 102) THEN
5134: INVPUTLI.info('INVUPD2B: Inside update_validations: TRACKING_QUANTITY_IND pending validations failed');
5130: dumm_status := INVUPD2B.set_process_flag3(row_id,user_id,login_id,prog_appid,prog_id,request_id);
5131:
5132: elsif (msi_temp.TRACKING_QUANTITY_IND <> msii_temp.TRACKING_QUANTITY_IND) AND (pendadj_tracking_qty_ind = 1) then
5133: IF l_inv_debug_level IN(101, 102) THEN
5134: INVPUTLI.info('INVUPD2B: Inside update_validations: TRACKING_QUANTITY_IND pending validations failed');
5135: END IF;
5136: dumm_status := INVPUOPI.mtl_log_interface_err(
5137: org_id,
5138: user_id,
5146: 'MTL_SYSTEM_ITEMS_INTERFACE',
5147: 'INV_PENDING_ADJUSTMENT',
5148: err_text);
5149:
5150: dumm_status := INVUPD2B.set_process_flag3(row_id,user_id,login_id,prog_appid,prog_id,request_id);
5151:
5152: end if;
5153:
5154: -- validate SECONDARY_UOM_CODE
5154: -- validate SECONDARY_UOM_CODE
5155: -- cannot update if there is onhand or transactions pending
5156: if (msi_temp.SECONDARY_UOM_CODE <> msii_temp.SECONDARY_UOM_CODE) AND (onhand_secondary_uom = 1) then
5157: IF l_inv_debug_level IN(101, 102) THEN
5158: INVPUTLI.info('INVUPD2B: Inside update_validations: SECONDARY_UOM_CODE onhand validations failed');
5159: END IF;
5160: dumm_status := INVPUOPI.mtl_log_interface_err(
5161: org_id,
5162: user_id,
5170: 'MTL_SYSTEM_ITEMS_INTERFACE',
5171: 'INV_QOH_CANNOT_UPDATE',
5172: err_text);
5173:
5174: dumm_status := INVUPD2B.set_process_flag3(row_id,user_id,login_id,prog_appid,prog_id,request_id);
5175:
5176: elsif (msi_temp.SECONDARY_UOM_CODE <> msii_temp.SECONDARY_UOM_CODE) AND (pendadj_secondary_uom = 1) then
5177: IF l_inv_debug_level IN(101, 102) THEN
5178: INVPUTLI.info('INVUPD2B: Inside update_validations: SECONDARY_UOM_CODE pending validations failed');
5174: dumm_status := INVUPD2B.set_process_flag3(row_id,user_id,login_id,prog_appid,prog_id,request_id);
5175:
5176: elsif (msi_temp.SECONDARY_UOM_CODE <> msii_temp.SECONDARY_UOM_CODE) AND (pendadj_secondary_uom = 1) then
5177: IF l_inv_debug_level IN(101, 102) THEN
5178: INVPUTLI.info('INVUPD2B: Inside update_validations: SECONDARY_UOM_CODE pending validations failed');
5179: END IF;
5180: dumm_status := INVPUOPI.mtl_log_interface_err(
5181: org_id,
5182: user_id,
5190: 'MTL_SYSTEM_ITEMS_INTERFACE',
5191: 'INV_PENDING_ADJUSTMENT',
5192: err_text);
5193:
5194: dumm_status := INVUPD2B.set_process_flag3(row_id,user_id,login_id,prog_appid,prog_id,request_id);
5195:
5196: end if;
5197:
5198: -- validate SECONDARY_DEFAULT_IND
5198: -- validate SECONDARY_DEFAULT_IND
5199: -- cannot update if there is onhand or transactions pending
5200: if (msi_temp.SECONDARY_DEFAULT_IND <> msii_temp.SECONDARY_DEFAULT_IND) AND (onhand_sec_default_ind = 1) then
5201: IF l_inv_debug_level IN(101, 102) THEN
5202: INVPUTLI.info('INVUPD2B: Inside update_validations: SECONDARY_DEFAULT_IND onhand validations failed');
5203: END IF;
5204: dumm_status := INVPUOPI.mtl_log_interface_err(
5205: org_id,
5206: user_id,
5214: 'MTL_SYSTEM_ITEMS_INTERFACE',
5215: 'INV_QOH_CANNOT_UPDATE',
5216: err_text);
5217:
5218: dumm_status := INVUPD2B.set_process_flag3(row_id,user_id,login_id,prog_appid,prog_id,request_id);
5219:
5220: elsif (msi_temp.SECONDARY_DEFAULT_IND <> msii_temp.SECONDARY_DEFAULT_IND) AND (pendadj_sec_default_ind = 1) then
5221: IF l_inv_debug_level IN(101, 102) THEN
5222: INVPUTLI.info('INVUPD2B: Inside update_validations: SECONDARY_DEFAULT_IND pending validations failed');
5218: dumm_status := INVUPD2B.set_process_flag3(row_id,user_id,login_id,prog_appid,prog_id,request_id);
5219:
5220: elsif (msi_temp.SECONDARY_DEFAULT_IND <> msii_temp.SECONDARY_DEFAULT_IND) AND (pendadj_sec_default_ind = 1) then
5221: IF l_inv_debug_level IN(101, 102) THEN
5222: INVPUTLI.info('INVUPD2B: Inside update_validations: SECONDARY_DEFAULT_IND pending validations failed');
5223: END IF;
5224: dumm_status := INVPUOPI.mtl_log_interface_err(
5225: org_id,
5226: user_id,
5234: 'MTL_SYSTEM_ITEMS_INTERFACE',
5235: 'INV_PENDING_ADJUSTMENT',
5236: err_text);
5237:
5238: dumm_status := INVUPD2B.set_process_flag3(row_id,user_id,login_id,prog_appid,prog_id,request_id);
5239:
5240: end if;
5241:
5242: -- validate PRIMARY_UOM_CODE
5242: -- validate PRIMARY_UOM_CODE
5243: -- cannot update if there is onhand or transactions pending
5244: if (msi_temp.PRIMARY_UOM_CODE <> msii_temp.PRIMARY_UOM_CODE) AND (onhand_primary_uom = 1) then
5245: IF l_inv_debug_level IN(101, 102) THEN
5246: INVPUTLI.info('INVUPD2B: Inside update_validations: PRIMARY_UOM_CODE onhand validations failed');
5247: END IF;
5248: dumm_status := INVPUOPI.mtl_log_interface_err(
5249: org_id,
5250: user_id,
5258: 'MTL_SYSTEM_ITEMS_INTERFACE',
5259: 'INV_QOH_CANNOT_UPDATE',
5260: err_text);
5261:
5262: dumm_status := INVUPD2B.set_process_flag3(row_id,user_id,login_id,prog_appid,prog_id,request_id);
5263:
5264: elsif (msi_temp.PRIMARY_UOM_CODE <> msii_temp.PRIMARY_UOM_CODE) AND (pendadj_primary_uom = 1) then
5265: IF l_inv_debug_level IN(101, 102) THEN
5266: INVPUTLI.info('INVUPD2B: Inside update_validations: PRIMARY_UOM_CODE pending validations failed');
5262: dumm_status := INVUPD2B.set_process_flag3(row_id,user_id,login_id,prog_appid,prog_id,request_id);
5263:
5264: elsif (msi_temp.PRIMARY_UOM_CODE <> msii_temp.PRIMARY_UOM_CODE) AND (pendadj_primary_uom = 1) then
5265: IF l_inv_debug_level IN(101, 102) THEN
5266: INVPUTLI.info('INVUPD2B: Inside update_validations: PRIMARY_UOM_CODE pending validations failed');
5267: END IF;
5268: dumm_status := INVPUOPI.mtl_log_interface_err(
5269: org_id,
5270: user_id,
5278: 'MTL_SYSTEM_ITEMS_INTERFACE',
5279: 'INV_PENDING_ADJUSTMENT',
5280: err_text);
5281:
5282: dumm_status := INVUPD2B.set_process_flag3(row_id,user_id,login_id,prog_appid,prog_id,request_id);
5283:
5284: end if;
5285:
5286: -- validate DUAL_UOM_DEVIATION_HIGH
5286: -- validate DUAL_UOM_DEVIATION_HIGH
5287: -- cannot update if there is onhand or transactions pending
5288: if (msi_temp.DUAL_UOM_DEVIATION_HIGH <> msii_temp.DUAL_UOM_DEVIATION_HIGH) AND (onhand_deviation_high = 1) then
5289: IF l_inv_debug_level IN(101, 102) THEN
5290: INVPUTLI.info('INVUPD2B: Inside update_validations: DUAL_UOM_DEVIATION_HIGH onhand validations failed');
5291: END IF;
5292: dumm_status := INVPUOPI.mtl_log_interface_err(
5293: org_id,
5294: user_id,
5302: 'MTL_SYSTEM_ITEMS_INTERFACE',
5303: 'INV_QOH_CANNOT_UPDATE',
5304: err_text);
5305:
5306: dumm_status := INVUPD2B.set_process_flag3(row_id,user_id,login_id,prog_appid,prog_id,request_id);
5307:
5308: elsif (msi_temp.DUAL_UOM_DEVIATION_HIGH <> msii_temp.DUAL_UOM_DEVIATION_HIGH) AND (pendadj_deviation_high = 1) then
5309: IF l_inv_debug_level IN(101, 102) THEN
5310: INVPUTLI.info('INVUPD2B: Inside update_validations: DUAL_UOM_DEVIATION_HIGH pending validations failed');
5306: dumm_status := INVUPD2B.set_process_flag3(row_id,user_id,login_id,prog_appid,prog_id,request_id);
5307:
5308: elsif (msi_temp.DUAL_UOM_DEVIATION_HIGH <> msii_temp.DUAL_UOM_DEVIATION_HIGH) AND (pendadj_deviation_high = 1) then
5309: IF l_inv_debug_level IN(101, 102) THEN
5310: INVPUTLI.info('INVUPD2B: Inside update_validations: DUAL_UOM_DEVIATION_HIGH pending validations failed');
5311: END IF;
5312: dumm_status := INVPUOPI.mtl_log_interface_err(
5313: org_id,
5314: user_id,
5322: 'MTL_SYSTEM_ITEMS_INTERFACE',
5323: 'INV_PENDING_ADJUSTMENT',
5324: err_text);
5325:
5326: dumm_status := INVUPD2B.set_process_flag3(row_id,user_id,login_id,prog_appid,prog_id,request_id);
5327:
5328: end if;
5329:
5330: -- validate DUAL_UOM_DEVIATION_LOW
5330: -- validate DUAL_UOM_DEVIATION_LOW
5331: -- cannot update if there is onhand or transactions pending
5332: if (msi_temp.DUAL_UOM_DEVIATION_LOW <> msii_temp.DUAL_UOM_DEVIATION_LOW) AND (onhand_deviation_low = 1) then
5333: IF l_inv_debug_level IN(101, 102) THEN
5334: INVPUTLI.info('INVUPD2B: Inside update_validations: DUAL_UOM_DEVIATION_LOW onhand validations failed');
5335: END IF;
5336: dumm_status := INVPUOPI.mtl_log_interface_err(
5337: org_id,
5338: user_id,
5346: 'MTL_SYSTEM_ITEMS_INTERFACE',
5347: 'INV_QOH_CANNOT_UPDATE',
5348: err_text);
5349:
5350: dumm_status := INVUPD2B.set_process_flag3(row_id,user_id,login_id,prog_appid,prog_id,request_id);
5351:
5352: elsif (msi_temp.DUAL_UOM_DEVIATION_LOW <> msii_temp.DUAL_UOM_DEVIATION_LOW) AND (pendadj_deviation_low = 1) then
5353: IF l_inv_debug_level IN(101, 102) THEN
5354: INVPUTLI.info('INVUPD2B: Inside update_validations: DUAL_UOM_DEVIATION_LOW pending validations failed');
5350: dumm_status := INVUPD2B.set_process_flag3(row_id,user_id,login_id,prog_appid,prog_id,request_id);
5351:
5352: elsif (msi_temp.DUAL_UOM_DEVIATION_LOW <> msii_temp.DUAL_UOM_DEVIATION_LOW) AND (pendadj_deviation_low = 1) then
5353: IF l_inv_debug_level IN(101, 102) THEN
5354: INVPUTLI.info('INVUPD2B: Inside update_validations: DUAL_UOM_DEVIATION_LOW pending validations failed');
5355: END IF;
5356: dumm_status := INVPUOPI.mtl_log_interface_err(
5357: org_id,
5358: user_id,
5366: 'MTL_SYSTEM_ITEMS_INTERFACE',
5367: 'INV_PENDING_ADJUSTMENT',
5368: err_text);
5369:
5370: dumm_status := INVUPD2B.set_process_flag3(row_id,user_id,login_id,prog_appid,prog_id,request_id);
5371:
5372: end if;
5373:
5374: /* End Bug 3713912 */
5579: ,P_Set_id => xset_id);
5580: -- End 3637854 : Pending ECO check and sync lifecycles
5581:
5582: IF l_inv_debug_level IN(101, 102) THEN
5583: INVPUTLI.info('INVUPD2B.inproit_process_item_update : begin');
5584: END IF;
5585:
5586: -- Identify the current session language as Base ('B') or Installed ('I')
5587: --
5754: (NVL(INV_EGO_REVISION_VALIDATE.Get_Process_Control(),'X')<> 'PLM_UI:Y') ) or
5755: (rec.contract_type <> rec.CONTRACT_ITEM_TYPE_CODE) then
5756:
5757: IF l_inv_debug_level IN(101, 102) THEN
5758: INVPUTLI.info('INVUPD2B: inserting the item category assignments for default category sets ');
5759: END IF;
5760:
5761: -- Creating item category assignments for the default category sets
5762: -- for the functional areas having the defining attribute enabled.
6538: --6417028 propagate item number update to all org hierarchies
6539: FOR item_csr IN c_item_number_updated
6540: LOOP
6541: IF l_inv_debug_level IN(101, 102) THEN
6542: INVPUTLI.info('INVUPD2B: Update: III' || item_csr.inventory_item_id ||
6543: 'segment1' || item_csr.segment1);
6544: END IF;
6545:
6546: UPDATE mtl_system_items_b
6802: l_inv_debug_level NUMBER := INVPUTLI.get_debug_level; --Bug: 4667452
6803: BEGIN
6804:
6805: IF l_inv_debug_level IN(101, 102) THEN
6806: INVPUTLI.info('INVUPD2B: Inside set_process_flag3');
6807: END IF;
6808: update MTL_SYSTEM_ITEMS_INTERFACE
6809: set PROCESS_FLAG = l_process_flag_3,
6810: request_id = reqst_id,
6842: l_inv_debug_level NUMBER := INVPUTLI.get_debug_level; --Bug: 4667452
6843: BEGIN
6844:
6845: IF l_inv_debug_level IN(101, 102) THEN
6846: INVPUTLI.info('INVUPD2B: inside get_message');
6847: END IF;
6848: FND_MESSAGE.SET_NAME('INV', SUBSTRB(msg_name, 1,30));
6849: error_text := FND_MESSAGE.GET;
6850: return (0);
6859:
6860: END get_message;
6861:
6862:
6863: end INVUPD2B;