[Home] [Help]
778: j INTEGER;
779:
780: l_completed_quantity NUMBER;
781: l_object_version_number NUMBER;
782: l_uom_code AHL_SCHEDULE_MATERIALS.UOM%TYPE;
783: l_quantity NUMBER;
784:
785: l_concatenated_segments mtl_system_items_kfv.concatenated_segments%TYPE;
786: l_workorder_name ahl_workorders.workorder_name%TYPE;
824: -- Query for finding the scheduled materials
825: CURSOR Sch_Mtl_Cur(p_org_id NUMBER, p_workorder_Op_id NUMBER,
826: p_item_Id NUMBER) IS
827: SELECT COMPLETED_QUANTITY, UOM, object_version_number
828: FROM AHL_SCHEDULE_MATERIALS A
829: WHERE ORGANIZATION_ID = p_org_id
830: AND A.WORKORDER_OPERATION_ID =p_workorder_op_id
831: AND A.INVENTORY_ITEM_ID = p_item_Id
832: AND A.MATERIAL_REQUEST_TYPE <> 'FORECAST'
834: --Added for FP ER# 6310725.
835: AND A.status IN ('ACTIVE','HISTORY', 'IN-SERVICE')
836: FOR UPDATE OF COMPLETED_QUANTITY NOWAIT;
837:
838: -- Query to check existence of AHL_SCHEDULE_MATERIALS record.
839: CURSOR Sch_Mtl_Exists_Cur(p_org_id NUMBER, p_workorder_Op_id NUMBER,
840: p_item_Id NUMBER) IS
841: SELECT 'x'
842: FROM AHL_SCHEDULE_MATERIALS A
838: -- Query to check existence of AHL_SCHEDULE_MATERIALS record.
839: CURSOR Sch_Mtl_Exists_Cur(p_org_id NUMBER, p_workorder_Op_id NUMBER,
840: p_item_Id NUMBER) IS
841: SELECT 'x'
842: FROM AHL_SCHEDULE_MATERIALS A
843: WHERE ORGANIZATION_ID = p_org_id
844: AND A.WORKORDER_OPERATION_ID =p_workorder_op_id
845: AND A.INVENTORY_ITEM_ID = p_item_Id
846: AND A.MATERIAL_REQUEST_TYPE <> 'FORECAST'
1087:
1088: END IF;
1089:
1090: -- For an issue transaction and return transaction (bug 5499575)
1091: -- IF Material requlrements are not existing in the AHL_SCHEDULE_MATERIALS
1092: -- then insert into the scheduled materials table with submitted status.
1093: OPEN Sch_Mtl_Exists_Cur(p_x_ahl_mtltxn_tbl(i).Organization_Id,
1094: p_x_ahl_mtltxn_tbl(i).Workorder_operation_Id,
1095: p_x_ahl_mtltxn_tbl(i).Inventory_Item_Id);
1096: FETCH Sch_Mtl_Exists_Cur INTO l_junk;
1097:
1098: If(Sch_Mtl_Exists_Cur%NOTFOUND) THEN
1099:
1100: --Check if the material exists in the AHL_SCHEDULE_MATERIALS table
1101: --for the given work order operation id.
1102: --IF (p_x_ahl_mtltxn_tbl(i).Transaction_Type_Id = WIP_CONSTANTS.ISSCOMP_TYPE) THEN
1103:
1104: Insert_Sch_Mtl_Row( p_mtl_txn_rec => p_x_ahl_mtltxn_tbl(i),
1258:
1259: FOR i IN l_txn_Id_Tbl.FIRST..l_txn_Id_Tbl.LAST LOOP
1260:
1261: -- Tamal: Bug #4095376: Begin
1262: -- For all cases (i.e. ISSUE / RETURN + whether found in AHL_SCHEDULE_MATERIALS) do the following
1263: l_quantity := p_x_ahl_mtltxn_tbl(i).quantity;
1264:
1265: -- The following depends on the fact that ahl_schedule_materials records all quantities in the primary_uom_code
1266: -- of the item, which is the case currently as it is a requirement from WIP
1261: -- Tamal: Bug #4095376: Begin
1262: -- For all cases (i.e. ISSUE / RETURN + whether found in AHL_SCHEDULE_MATERIALS) do the following
1263: l_quantity := p_x_ahl_mtltxn_tbl(i).quantity;
1264:
1265: -- The following depends on the fact that ahl_schedule_materials records all quantities in the primary_uom_code
1266: -- of the item, which is the case currently as it is a requirement from WIP
1267: SELECT PRIMARY_UOM_CODE INTO l_uom_code
1268: FROM MTL_SYSTEM_ITEMS_B
1269: WHERE INVENTORY_ITEM_ID = p_x_ahl_mtltxn_tbl(i).Inventory_Item_Id
1317:
1318: -- Update Completion quantity for cMRO-APS integration.
1319: IF (p_x_ahl_mtltxn_tbl(i).Transaction_Type_Id = WIP_CONSTANTS.ISSCOMP_TYPE) THEN
1320: -- Issue Txn.
1321: UPDATE AHL_SCHEDULE_MATERIALS
1322: SET completed_quantity = nvl(completed_quantity,0) + l_quantity,
1323: object_version_number = l_object_version_number + 1
1324: WHERE CURRENT OF Sch_Mtl_Cur;
1325: -- 11/20: Commented out updation of completed quantity to fix bug# 6598809
1325: -- 11/20: Commented out updation of completed quantity to fix bug# 6598809
1326: /*
1327: ELSIF (p_x_ahl_mtltxn_tbl(i).Transaction_Type_Id = WIP_CONSTANTS.RETCOMP_TYPE) THEN
1328: -- Return Txn.
1329: UPDATE AHL_SCHEDULE_MATERIALS
1330: SET completed_quantity = nvl(completed_quantity,0) - l_quantity,
1331: object_version_number = l_object_version_number + 1
1332: WHERE CURRENT OF Sch_Mtl_Cur;
1333: */
1897: IS
1898: SELECT COUNT(A.workorder_mtl_txn_id)
1899: FROM AHL_WORKORDER_MTL_TXNS A,
1900: AHL_WORKORDER_OPERATIONS_V B,
1901: AHL_SCHEDULE_MATERIALS C
1902: WHERE B.WORKORDER_ID=C_WRK_ID
1903: AND B.WORKORDER_OPERATION_ID=A.WORKORDER_OPERATION_ID
1904: AND B.WORKORDER_OPERATION_ID=C.WORKORDER_OPERATION_ID
1905: AND A.INVENTORY_ITEM_ID=C.INVENTORY_ITEM_ID
2115:
2116: /*
2117: CURSOR TRANSACTION_DATE_CUR(C_WORKORDER_OPERATION_ID NUMBER,C_WORKORDER_ID NUMBER,C_INV_ITEM_ID NUMBER) IS
2118: SELECT A.scheduled_start_date
2119: FROM AHL_WORKORDER_OPERATIONS_V A ,AHL_SCHEDULE_MATERIALS B
2120: WHERE A.WORKORDER_OPERATION_ID=C_WORKORDER_OPERATION_ID
2121: AND A.WORKORDER_ID=C_WORKORDER_ID
2122: AND A.WORKORDER_OPERATION_ID=B.WORKORDER_OPERATION_ID
2123: AND B.INVENTORY_ITEM_ID=C_INV_ITEM_ID
3849: RETURN ret;
3850: END IS_ITEM_TRACKABLE;
3851:
3852: /***************************************************
3853: This procedure inserts record in the AHL_SCHEDULE_MATERIALS table
3854: TBD to be corrected.
3855: ************************************************/
3856: PROCEDURE Insert_Sch_Mtl_Row(
3857: p_mtl_txn_Rec IN Ahl_Mtltxn_Rec_Type,
3867: l_Visit_Id NUMBER;
3868: l_visit_task_Id NUMBER;
3869: l_Item_Desc VARCHAR2(240);
3870: l_quantity NUMBER;
3871: l_primary_uom ahl_schedule_materials.UOM%TYPE;
3872:
3873: -- QWuey to select the work order dependent data to be passed to Schedule materials API.
3874: CURSOR Workop_Det_Cur(p_wo_id NUMBER) IS
3875: SELECT B.VISIT_ID,C.VISIT_TASK_ID,B.PROJECT_ID,C.PROJECT_TASK_ID
4011: END IF;
4012:
4013: /* will be updated after wip api is successful - modified for bug fix 5499575.
4014: -- Update Completed quantity.
4015: Update ahl_schedule_materials
4016: set completed_quantity = l_quantity
4017: where scheduled_material_id = Material_Tbl(0).schedule_material_id;
4018: */
4019: