[Home] [Help]
132: BEGIN
133: IF (p_resource_code IS NOT NULL) THEN
134: SELECT DISTINCT(BR.RESOURCE_ID)
135: INTO x_resource_id
136: FROM BOM_RESOURCES BR, BOM_DEPARTMENT_RESOURCES BDR, AHL_WORKORDER_OPERATIONS_V AWV
137: WHERE BR.RESOURCE_ID = BDR.RESOURCE_ID AND BDR.DEPARTMENT_ID = AWV.DEPARTMENT_ID
138: AND AWV.WORKORDER_ID = p_workorder_id AND BR.RESOURCE_CODE = p_resource_code;
139: END IF;
140:
377: -- Cursor to check
378: /*CURSOR c_wo_oper (x_id IN NUMBER) IS
379: SELECT TO_DATE(ACTUAL_START_DATE,'DD-MM-YYYY'), TO_DATE(ACTUAL_END_DATE,'DD-MM-YYYY'),
380: TO_DATE(SCHEDULED_START_DATE,'DD-MM-YYYY'), TO_DATE(SCHEDULED_END_DATE,'DD-MM-YYYY')
381: FROM AHL_WORKORDER_OPERATIONS_V WHERE WORKORDER_OPERATION_ID = x_id;*/
382: -- bug 4092197
383: -- no point in doing a date conversion on a date value
384: -- if the intent was to lose the time portion, then the trunc function can be used
385: -- but do not need to use trunc function
389: -- of the trunc(resource_date) = trunc(operation_date)
390: CURSOR c_wo_oper (x_id IN NUMBER) IS
391: SELECT ACTUAL_START_DATE, ACTUAL_END_DATE,
392: SCHEDULED_START_DATE, SCHEDULED_END_DATE
393: FROM AHL_WORKORDER_OPERATIONS_V WHERE WORKORDER_OPERATION_ID = x_id;
394:
395: BEGIN
396: IF G_DEBUG='Y' THEN
397: Ahl_Debug_Pub.debug('Check_Resrc_Require_Req_Items' || '--Operation ID =' || p_resrc_Require_rec.OPERATION_RESOURCE_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
1232: SELECT WORKORDER_OPERATION_ID FROM
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
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
1232: SELECT WORKORDER_OPERATION_ID FROM
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
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:
1290: CURSOR wip_operation_dates (c_workorder_operation_id IN NUMBER)
1291: IS
1292: SELECT first_unit_start_date,
1293: last_unit_completion_date
1294: FROM wip_operations a, ahl_workorders b,ahl_workorder_operations c
1295: WHERE a.wip_entity_id = b.wip_entity_id
1296: AND b.workorder_id = c.workorder_id
1297: AND a.operation_seq_num = c.OPERATION_SEQUENCE_NUM
1298: AND c.workorder_operation_id = c_workorder_operation_id;
1314: IS
1315: SELECT
1316: MIN(wipor.schedule_seq_num)
1317: FROM
1318: ahl_workorder_operations awop,
1319: ahl_workorders awo,
1320: wip_operation_resources wipor
1321: WHERE
1322: awop.operation_sequence_num = wipor.operation_seq_num
2320: x_msg_data OUT NOCOPY VARCHAR2
2321: )
2322: IS
2323:
2324: -- To find the WORKORDER_ID from AHL_WORKORDER_OPERATIONS_V view
2325: CURSOR c_wo_oper (x_id IN NUMBER) IS
2326: SELECT WORKORDER_ID,OPERATION_SEQUENCE_NUM FROM
2327: AHL_WORKORDER_OPERATIONS_V
2328: WHERE WORKORDER_OPERATION_ID = x_id;
2323:
2324: -- To find the WORKORDER_ID from AHL_WORKORDER_OPERATIONS_V view
2325: CURSOR c_wo_oper (x_id IN NUMBER) IS
2326: SELECT WORKORDER_ID,OPERATION_SEQUENCE_NUM FROM
2327: AHL_WORKORDER_OPERATIONS_V
2328: WHERE WORKORDER_OPERATION_ID = x_id;
2329:
2330: -- To Get Wip Entity Id and Org Id
2331: CURSOR c_work_orders (x_id IN NUMBER) IS
2405: CURSOR wip_operation_dates (c_workorder_operation_id IN NUMBER)
2406: IS
2407: SELECT first_unit_start_date,
2408: last_unit_completion_date
2409: FROM wip_operations a, ahl_workorders b,ahl_workorder_operations c
2410: WHERE a.wip_entity_id = b.wip_entity_id
2411: AND b.workorder_id = c.workorder_id
2412: AND a.operation_seq_num = c.OPERATION_SEQUENCE_NUM
2413: AND c.workorder_operation_id = c_workorder_operation_id;
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
3268: AND WOR.RESOURCE_SEQ_NUM = AOR.RESOURCE_SEQUENCE_NUM
3269: AND AOR.WORKORDER_OPERATION_ID = AWO.WORKORDER_OPERATION_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
3292: AND WORV.RESOURCE_SEQ_NUM = AOR.RESOURCE_SEQUENCE_NUM
3297: c_WIP_oper_rec c_WIP_oper%ROWTYPE;
3298:
3299: CURSOR c_WO_oper(x_id IN NUMBER) IS
3300: SELECT OPERATION_SEQUENCE_NUM, SCHEDULED_START_DATE, SCHEDULED_END_DATE
3301: FROM AHL_WORKORDER_OPERATIONS_V
3302: WHERE WORKORDER_OPERATION_ID = x_id;
3303:
3304: /* R12 Perf Tuning
3305: * Balaji modified the query to use only base tables
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
3326: BOM.UNIT_OF_MEASURE = MUOM.UOM_CODE AND
3586:
3587: --
3588: CURSOR Get_wo_oper_cur (c_wo_operation_id IN NUMBER)
3589: IS
3590: SELECT * FROM ahl_workorder_operations_v
3591: WHERE workorder_operation_id = c_wo_operation_id;
3592:
3593: --
3594: CURSOR c_chk_assgn (oper_resrc_id IN NUMBER)