[Home] [Help]
762: j INTEGER;
763:
764: l_completed_quantity NUMBER;
765: l_object_version_number NUMBER;
766: l_uom_code AHL_SCHEDULE_MATERIALS.UOM%TYPE;
767: l_quantity NUMBER;
768:
769: l_concatenated_segments mtl_system_items_kfv.concatenated_segments%TYPE;
770: l_workorder_name ahl_workorders.workorder_name%TYPE;
788: -- Query for finding the scheduled materials
789: CURSOR Sch_Mtl_Cur(p_org_id NUMBER, p_workorder_Op_id NUMBER,
790: p_item_Id NUMBER) IS
791: SELECT COMPLETED_QUANTITY, UOM, object_version_number
792: FROM AHL_SCHEDULE_MATERIALS A
793: WHERE ORGANIZATION_ID = p_org_id
794: AND A.WORKORDER_OPERATION_ID =p_workorder_op_id
795: AND A.INVENTORY_ITEM_ID = p_item_Id
796: AND A.MATERIAL_REQUEST_TYPE <> 'FORECAST'
798: --Added for FP ER# 6310725.
799: AND A.status IN ('ACTIVE','HISTORY', 'IN-SERVICE')
800: FOR UPDATE OF COMPLETED_QUANTITY NOWAIT;
801:
802: -- Query to check existence of AHL_SCHEDULE_MATERIALS record.
803: CURSOR Sch_Mtl_Exists_Cur(p_org_id NUMBER, p_workorder_Op_id NUMBER,
804: p_item_Id NUMBER) IS
805: SELECT 'x'
806: FROM AHL_SCHEDULE_MATERIALS A
802: -- Query to check existence of AHL_SCHEDULE_MATERIALS record.
803: CURSOR Sch_Mtl_Exists_Cur(p_org_id NUMBER, p_workorder_Op_id NUMBER,
804: p_item_Id NUMBER) IS
805: SELECT 'x'
806: FROM AHL_SCHEDULE_MATERIALS A
807: WHERE ORGANIZATION_ID = p_org_id
808: AND A.WORKORDER_OPERATION_ID =p_workorder_op_id
809: AND A.INVENTORY_ITEM_ID = p_item_Id
810: AND A.MATERIAL_REQUEST_TYPE <> 'FORECAST'
997:
998: END IF;
999:
1000: -- For an issue transaction and return transaction (bug 5499575)
1001: -- IF Material requlrements are not existing in the AHL_SCHEDULE_MATERIALS
1002: -- then insert into the scheduled materials table with submitted status.
1003: OPEN Sch_Mtl_Exists_Cur(p_x_ahl_mtltxn_tbl(i).Organization_Id,
1004: p_x_ahl_mtltxn_tbl(i).Workorder_operation_Id,
1005: p_x_ahl_mtltxn_tbl(i).Inventory_Item_Id);
1006: FETCH Sch_Mtl_Exists_Cur INTO l_junk;
1007:
1008: If(Sch_Mtl_Exists_Cur%NOTFOUND) THEN
1009:
1010: --Check if the material exists in the AHL_SCHEDULE_MATERIALS table
1011: --for the given work order operation id.
1012: --IF (p_x_ahl_mtltxn_tbl(i).Transaction_Type_Id = WIP_CONSTANTS.ISSCOMP_TYPE) THEN
1013:
1014: Insert_Sch_Mtl_Row( p_mtl_txn_rec => p_x_ahl_mtltxn_tbl(i),
1133:
1134: FOR i IN l_txn_Id_Tbl.FIRST..l_txn_Id_Tbl.LAST LOOP
1135:
1136: -- Tamal: Bug #4095376: Begin
1137: -- For all cases (i.e. ISSUE / RETURN + whether found in AHL_SCHEDULE_MATERIALS) do the following
1138: l_quantity := p_x_ahl_mtltxn_tbl(i).quantity;
1139:
1140: -- The following depends on the fact that ahl_schedule_materials records all quantities in the primary_uom_code
1141: -- of the item, which is the case currently as it is a requirement from WIP
1136: -- Tamal: Bug #4095376: Begin
1137: -- For all cases (i.e. ISSUE / RETURN + whether found in AHL_SCHEDULE_MATERIALS) do the following
1138: l_quantity := p_x_ahl_mtltxn_tbl(i).quantity;
1139:
1140: -- The following depends on the fact that ahl_schedule_materials records all quantities in the primary_uom_code
1141: -- of the item, which is the case currently as it is a requirement from WIP
1142: SELECT PRIMARY_UOM_CODE INTO l_uom_code
1143: FROM MTL_SYSTEM_ITEMS_B
1144: WHERE INVENTORY_ITEM_ID = p_x_ahl_mtltxn_tbl(i).Inventory_Item_Id
1192:
1193: -- Update Completion quantity for cMRO-APS integration.
1194: IF (p_x_ahl_mtltxn_tbl(i).Transaction_Type_Id = WIP_CONSTANTS.ISSCOMP_TYPE) THEN
1195: -- Issue Txn.
1196: UPDATE AHL_SCHEDULE_MATERIALS
1197: SET completed_quantity = nvl(completed_quantity,0) + l_quantity,
1198: object_version_number = l_object_version_number + 1
1199: WHERE CURRENT OF Sch_Mtl_Cur;
1200: -- 11/20: Commented out updation of completed quantity to fix bug# 6598809
1200: -- 11/20: Commented out updation of completed quantity to fix bug# 6598809
1201: /*
1202: ELSIF (p_x_ahl_mtltxn_tbl(i).Transaction_Type_Id = WIP_CONSTANTS.RETCOMP_TYPE) THEN
1203: -- Return Txn.
1204: UPDATE AHL_SCHEDULE_MATERIALS
1205: SET completed_quantity = nvl(completed_quantity,0) - l_quantity,
1206: object_version_number = l_object_version_number + 1
1207: WHERE CURRENT OF Sch_Mtl_Cur;
1208: */
1608: IS
1609: SELECT COUNT(A.workorder_mtl_txn_id)
1610: FROM AHL_WORKORDER_MTL_TXNS A,
1611: AHL_WORKORDER_OPERATIONS_V B,
1612: AHL_SCHEDULE_MATERIALS C
1613: WHERE B.WORKORDER_ID=C_WRK_ID
1614: AND B.WORKORDER_OPERATION_ID=A.WORKORDER_OPERATION_ID
1615: AND B.WORKORDER_OPERATION_ID=C.WORKORDER_OPERATION_ID
1616: AND A.INVENTORY_ITEM_ID=C.INVENTORY_ITEM_ID
1826:
1827: /*
1828: CURSOR TRANSACTION_DATE_CUR(C_WORKORDER_OPERATION_ID NUMBER,C_WORKORDER_ID NUMBER,C_INV_ITEM_ID NUMBER) IS
1829: SELECT A.scheduled_start_date
1830: FROM AHL_WORKORDER_OPERATIONS_V A ,AHL_SCHEDULE_MATERIALS B
1831: WHERE A.WORKORDER_OPERATION_ID=C_WORKORDER_OPERATION_ID
1832: AND A.WORKORDER_ID=C_WORKORDER_ID
1833: AND A.WORKORDER_OPERATION_ID=B.WORKORDER_OPERATION_ID
1834: AND B.INVENTORY_ITEM_ID=C_INV_ITEM_ID
3282: RETURN ret;
3283: END IS_ITEM_TRACKABLE;
3284:
3285: /***************************************************
3286: This procedure inserts record in the AHL_SCHEDULE_MATERIALS table
3287: TBD to be corrected.
3288: ************************************************/
3289: PROCEDURE Insert_Sch_Mtl_Row(
3290: p_mtl_txn_Rec IN Ahl_Mtltxn_Rec_Type,
3300: l_Visit_Id NUMBER;
3301: l_visit_task_Id NUMBER;
3302: l_Item_Desc VARCHAR2(240);
3303: l_quantity NUMBER;
3304: l_primary_uom ahl_schedule_materials.UOM%TYPE;
3305:
3306: -- QWuey to select the work order dependent data to be passed to Schedule materials API.
3307: CURSOR Workop_Det_Cur(p_wo_id NUMBER) IS
3308: SELECT B.VISIT_ID,C.VISIT_TASK_ID,B.PROJECT_ID,C.PROJECT_TASK_ID
3434: END IF;
3435:
3436: /* will be updated after wip api is successful - modified for bug fix 5499575.
3437: -- Update Completed quantity.
3438: Update ahl_schedule_materials
3439: set completed_quantity = l_quantity
3440: where scheduled_material_id = Material_Tbl(0).schedule_material_id;
3441: */
3442: