[Home] [Help]
195: X_RT_OPER_RESOURCE_ID IN NUMBER
196: -- SUKHWSIN ER # 9014609 -- end
197: ) IS
198: BEGIN
199: INSERT INTO AHL_OPERATION_RESOURCES (
200: OPERATION_RESOURCE_ID,
201: OBJECT_VERSION_NUMBER,
202: LAST_UPDATE_DATE,
203: LAST_UPDATED_BY,
305: )
306: IS
307:
308: BEGIN
309: UPDATE AHL_OPERATION_RESOURCES SET
310: OBJECT_VERSION_NUMBER = X_OBJECT_VERSION_NUMBER + 1,
311: RESOURCE_ID = X_RESOURCE_ID ,
312: WORKORDER_OPERATION_ID = X_WORKORDER_OPERATION_ID ,
313: RESOURCE_SEQUENCE_NUM = X_RESOURCE_SEQ_NUMBER ,
347: PROCEDURE DELETE_ROW (
348: X_OPERATION_RESOURCE_ID IN NUMBER
349: ) IS
350: BEGIN
351: DELETE FROM AHL_OPERATION_RESOURCES
352: WHERE OPERATION_RESOURCE_ID = X_OPERATION_RESOURCE_ID;
353: END DELETE_ROW;
354:
355: ---------------------------------------------------------------------
367: l_eff_end_date DATE;
368: l_sch_st_date DATE;
369: l_sch_end_date DATE;
370:
371: -- To find all information from AHL_OPERATION_RESOURCES view
372: CURSOR c_oper_req (x_id IN NUMBER) IS
373: SELECT * FROM AHL_OPERATION_RESOURCES
374: WHERE OPERATION_RESOURCE_ID = x_id;
375: c_oper_req_rec c_oper_req%ROWTYPE;
369: l_sch_end_date DATE;
370:
371: -- To find all information from AHL_OPERATION_RESOURCES view
372: CURSOR c_oper_req (x_id IN NUMBER) IS
373: SELECT * FROM AHL_OPERATION_RESOURCES
374: WHERE OPERATION_RESOURCE_ID = x_id;
375: c_oper_req_rec c_oper_req%ROWTYPE;
376:
377: -- Cursor to check
800: Ahl_Debug_Pub.debug( 'QUERY -->' || 'RESOURCE_SEQUENCE_NUM = ' || p_resrc_Require_rec.RESOURCE_SEQ_NUMBER ||
801: ' AND WORKORDER_OPERATION_ID = ' || p_resrc_Require_rec.WORKORDER_OPERATION_ID);
802:
803: l_valid_flag := Ahl_Utility_Pvt.check_uniqueness (
804: 'AHL_OPERATION_RESOURCES',
805: 'RESOURCE_SEQUENCE_NUM = ' || p_resrc_Require_rec.RESOURCE_SEQ_NUMBER ||
806: ' AND WORKORDER_OPERATION_ID = ' || p_resrc_Require_rec.WORKORDER_OPERATION_ID
807: );
808: ELSE
811: ' AND WORKORDER_OPERATION_ID = ' || p_resrc_Require_rec.WORKORDER_OPERATION_ID ||
812: ' AND OPERATION_RESOURCE_ID <> ' || p_resrc_Require_rec.OPERATION_RESOURCE_ID);
813:
814: l_valid_flag := Ahl_Utility_Pvt.check_uniqueness (
815: 'AHL_OPERATION_RESOURCES',
816: 'RESOURCE_SEQUENCE_NUM = ' || p_resrc_Require_rec.RESOURCE_SEQ_NUMBER ||
817: ' AND WORKORDER_OPERATION_ID = ' || p_resrc_Require_rec.WORKORDER_OPERATION_ID ||
818: ' AND OPERATION_RESOURCE_ID <> ' || p_resrc_Require_rec.OPERATION_RESOURCE_ID
819: );
1223: -- Check to see Operation Resource Id exists
1224: CURSOR Sch_id_exists (x_id IN NUMBER) IS
1225: SELECT 1 FROM dual
1226: WHERE EXISTS (SELECT 1
1227: FROM AHL_OPERATION_RESOURCES
1228: WHERE OPERATION_RESOURCE_ID = x_id);
1229:
1230: -- To find workorder_operation_id from ahl_workorder_operation_v view
1231: CURSOR c_wo_oper (x_id IN NUMBER, x_seq IN NUMBER) IS
1233: AHL_WORKORDER_OPERATIONS
1234: --AHL_WORKORDER_OPERATIONS_V
1235: WHERE WORKORDER_ID = x_id AND OPERATION_SEQUENCE_NUM = x_seq;
1236:
1237: -- To find the resource sequence nubmer from ahl_operation_resources
1238: CURSOR c_resrc_seq (x_id IN NUMBER, x_oper_seq IN NUMBER, x_resrc_seq IN NUMBER) IS
1239: SELECT COUNT(*) FROM
1240: AHL_WORKORDER_OPERATIONS AWOV, AHL_OPERATION_RESOURCES AOR
1241: WHERE AWOV.WORKORDER_OPERATION_ID = AOR.WORKORDER_OPERATION_ID AND
1236:
1237: -- To find the resource sequence nubmer from ahl_operation_resources
1238: CURSOR c_resrc_seq (x_id IN NUMBER, x_oper_seq IN NUMBER, x_resrc_seq IN NUMBER) IS
1239: SELECT COUNT(*) FROM
1240: AHL_WORKORDER_OPERATIONS AWOV, AHL_OPERATION_RESOURCES AOR
1241: WHERE AWOV.WORKORDER_OPERATION_ID = AOR.WORKORDER_OPERATION_ID AND
1242: AWOV.WORKORDER_ID = x_id AND AWOV.OPERATION_SEQUENCE_NUM = x_oper_seq AND
1243: AOR.RESOURCE_SEQUENCE_NUM = x_resrc_seq;
1244:
1241: WHERE AWOV.WORKORDER_OPERATION_ID = AOR.WORKORDER_OPERATION_ID AND
1242: AWOV.WORKORDER_ID = x_id AND AWOV.OPERATION_SEQUENCE_NUM = x_oper_seq AND
1243: AOR.RESOURCE_SEQUENCE_NUM = x_resrc_seq;
1244:
1245: -- To find the resource sequence nubmer from ahl_operation_resources
1246: CURSOR c_workorder (x_id IN NUMBER) IS
1247: SELECT * FROM AHL_WORKORDERS
1248: WHERE WORKORDER_ID = x_id;
1249: c_workorder_rec c_workorder%ROWTYPE;
1247: SELECT * FROM AHL_WORKORDERS
1248: WHERE WORKORDER_ID = x_id;
1249: c_workorder_rec c_workorder%ROWTYPE;
1250:
1251: -- To find the resource sequence nubmer from ahl_operation_resources
1252: CURSOR c_resources (x_id IN NUMBER) IS
1253: SELECT DEPARTMENT_ID FROM
1254: BOM_DEPARTMENT_RESOURCES
1255: WHERE RESOURCE_ID = x_id;
1253: SELECT DEPARTMENT_ID FROM
1254: BOM_DEPARTMENT_RESOURCES
1255: WHERE RESOURCE_ID = x_id;
1256:
1257: -- To find the resource sequence nubmer from ahl_operation_resources
1258: CURSOR c_wo_dept (x_id IN NUMBER) IS
1259: SELECT --V.DEPARTMENT_ID, -- department should be from wip_operations
1260: V.ORGANIZATION_ID,
1261: WORKORDER_NAME, WIP_ENTITY_ID FROM
2097: END IF;
2098:
2099: --
2100: -- Get Sequence Number for Resource Requirement ID
2101: SELECT AHL_OPERATION_RESOURCES_S.NEXTVAL
2102: INTO l_Resrc_Require_Rec.Operation_Resource_id
2103: FROM DUAL;
2104:
2105: --Check for Record Exists
2336: WHERE WORKORDER_ID = x_id
2337: AND A.VISIT_TASK_ID = B.VISIT_TASK_ID
2338: AND B.VISIT_ID = C.VISIT_ID;
2339:
2340: -- To find the RESOURCE_SEQUENCE_NUM from AHL_OPERATION_RESOURCES view
2341: CURSOR c_oper_resrc (x_id IN NUMBER) IS
2342: SELECT RESOURCE_SEQUENCE_NUM FROM
2343: AHL_OPERATION_RESOURCES
2344: WHERE WORKORDER_OPERATION_ID = x_id;
2339:
2340: -- To find the RESOURCE_SEQUENCE_NUM from AHL_OPERATION_RESOURCES view
2341: CURSOR c_oper_resrc (x_id IN NUMBER) IS
2342: SELECT RESOURCE_SEQUENCE_NUM FROM
2343: AHL_OPERATION_RESOURCES
2344: WHERE WORKORDER_OPERATION_ID = x_id;
2345:
2346: -- To find all information from AHL_OPERATION_RESOURCES view
2347: CURSOR c_oper_req (x_id IN NUMBER) IS
2342: SELECT RESOURCE_SEQUENCE_NUM FROM
2343: AHL_OPERATION_RESOURCES
2344: WHERE WORKORDER_OPERATION_ID = x_id;
2345:
2346: -- To find all information from AHL_OPERATION_RESOURCES view
2347: CURSOR c_oper_req (x_id IN NUMBER) IS
2348: SELECT * FROM AHL_OPERATION_RESOURCES
2349: WHERE OPERATION_RESOURCE_ID = x_id;
2350: c_oper_req_rec c_oper_req%ROWTYPE;
2344: WHERE WORKORDER_OPERATION_ID = x_id;
2345:
2346: -- To find all information from AHL_OPERATION_RESOURCES view
2347: CURSOR c_oper_req (x_id IN NUMBER) IS
2348: SELECT * FROM AHL_OPERATION_RESOURCES
2349: WHERE OPERATION_RESOURCE_ID = x_id;
2350: c_oper_req_rec c_oper_req%ROWTYPE;
2351:
2352: -- To find the resource sequence nubmer from ahl_operation_resources
2348: SELECT * FROM AHL_OPERATION_RESOURCES
2349: WHERE OPERATION_RESOURCE_ID = x_id;
2350: c_oper_req_rec c_oper_req%ROWTYPE;
2351:
2352: -- To find the resource sequence nubmer from ahl_operation_resources
2353: /*
2354: * R12 Perf Tuning
2355: * Balaji modified the query to use only base tables
2356: * instead of AHL_WORKORDERS_V
2369: WDJ.wip_entity_id = wo.wip_entity_id;
2370:
2371: c_workorder_rec c_workorder%ROWTYPE;
2372:
2373: -- To find the resource sequence nubmer from ahl_operation_resources
2374: CURSOR c_resources (x_id IN NUMBER) IS
2375: SELECT DEPARTMENT_ID FROM
2376: BOM_DEPARTMENT_RESOURCES
2377: WHERE RESOURCE_ID = x_id;
3246: oper_end_date DATE;
3247:
3248: --pekambar start : changed for :: 9089320 FP for bug#8532919
3249: /*CURSOR c_res_req (x_id IN NUMBER) IS
3250: SELECT * FROM AHL_OPERATION_RESOURCES
3251: WHERE OPERATION_RESOURCE_ID = x_id;*/
3252:
3253: CURSOR c_res_req (x_id IN NUMBER) IS
3254: SELECT AOR.workorder_operation_id,
3259: AOR.QUANTITY,
3260: AOR.RESOURCE_SEQUENCE_NUM,
3261: WOR.START_DATE "SCHEDULED_START_DATE",
3262: WOR.COMPLETION_DATE "SCHEDULED_END_DATE"
3263: FROM AHL_OPERATION_RESOURCES AOR,
3264: WIP_OPERATION_RESOURCES WOR,
3265: AHL_WORKORDER_OPERATIONS AWO ,
3266: AHL_WORKORDERS AWJ
3267: WHERE AOR.RESOURCE_ID = WOR.RESOURCE_ID
3275: c_resrc_req c_res_req%ROWTYPE;
3276:
3277: CURSOR c_resource (x_id IN NUMBER) IS
3278: SELECT ML.MEANING, BR.RESOURCE_TYPE, BR.RESOURCE_CODE
3279: FROM BOM_RESOURCES BR, MFG_LOOKUPS ML, AHL_OPERATION_RESOURCES AOR
3280: WHERE BR.RESOURCE_TYPE = ML.LOOKUP_CODE
3281: AND ML.LOOKUP_TYPE= 'BOM_RESOURCE_TYPE'
3282: AND AOR.RESOURCE_ID = BR.RESOURCE_ID
3283: AND AOR.OPERATION_RESOURCE_ID = x_id;
3283: AND AOR.OPERATION_RESOURCE_ID = x_id;
3284:
3285: CURSOR c_WIP_oper (x_id IN NUMBER) IS
3286: SELECT WORV.* FROM
3287: AHL_OPERATION_RESOURCES AOR,
3288: AHL_WORKORDER_OPERATIONS AWO,
3289: AHL_WORKORDERS AW,
3290: WIP_OPERATION_RESOURCES_V WORV
3291: WHERE WORV.OPERATION_SEQ_NUM = AWO.OPERATION_SEQUENCE_NUM
3317: AWO.workorder_id job_id
3318: FROM
3319: BOM_RESOURCES BOM,
3320: MTL_UNITS_OF_MEASURE MUOM,
3321: AHL_OPERATION_RESOURCES AOR,
3322: AHL_WORKORDER_OPERATIONS AWO
3323: WHERE
3324: AOR.OPERATION_RESOURCE_ID = x_id AND
3325: AOR.RESOURCE_ID = BOM.RESOURCE_ID AND
3432:
3433: l_Resrc_Require_Rec.REQ_START_DATE := c_resrc_req.SCHEDULED_START_DATE ;
3434: l_Resrc_Require_Rec.REQ_END_DATE := c_resrc_req.SCHEDULED_END_DATE ;
3435: -- change for ER 3974014
3436: -- the duration entered into AHL_OPERATION_RESOURCES is now the total_duration of all
3437: -- the resources
3438: -- so total_required := c_resrc_rec.duration
3439: -- duration := c_resrc_rec.duration/quantity
3440:
3563: IS
3564: --
3565: CURSOR Get_resource_cur (c_op_resource_id IN NUMBER)
3566: IS
3567: SELECT * FROM AHL_OPERATION_RESOURCES
3568: WHERE operation_resource_id = c_op_resource_id;
3569: --
3570: /* R12 Perf Tuning
3571: * Balaji modified the query to use only base tables
3901: Fnd_Msg_Pub.ADD;
3902: ELSE
3903: Ahl_Debug_Pub.debug ('Count in Assignments table' || l_count);
3904: Ahl_Debug_Pub.debug ('BEFORE DELETE RESOURCES' || p_x_resrc_Require_tbl(i).Operation_Resource_Id);
3905: DELETE FROM AHL_OPERATION_RESOURCES
3906: WHERE OPERATION_RESOURCE_ID = p_x_resrc_Require_tbl(i).operation_resource_id;
3907: END IF;
3908: END LOOP;
3909: END IF;--Count