[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:
369: -- Cursor to check
370: /*CURSOR c_wo_oper (x_id IN NUMBER) IS
371: SELECT TO_DATE(ACTUAL_START_DATE,'DD-MM-YYYY'), TO_DATE(ACTUAL_END_DATE,'DD-MM-YYYY'),
372: TO_DATE(SCHEDULED_START_DATE,'DD-MM-YYYY'), TO_DATE(SCHEDULED_END_DATE,'DD-MM-YYYY')
373: FROM AHL_WORKORDER_OPERATIONS_V WHERE WORKORDER_OPERATION_ID = x_id;*/
374: -- bug 4092197
375: -- no point in doing a date conversion on a date value
376: -- if the intent was to lose the time portion, then the trunc function can be used
377: -- but do not need to use trunc function
381: -- of the trunc(resource_date) = trunc(operation_date)
382: CURSOR c_wo_oper (x_id IN NUMBER) IS
383: SELECT ACTUAL_START_DATE, ACTUAL_END_DATE,
384: SCHEDULED_START_DATE, SCHEDULED_END_DATE
385: FROM AHL_WORKORDER_OPERATIONS_V WHERE WORKORDER_OPERATION_ID = x_id;
386:
387: BEGIN
388: IF G_DEBUG='Y' THEN
389: Ahl_Debug_Pub.debug('Check_Resrc_Require_Req_Items' || '--Operation ID =' || p_resrc_Require_rec.OPERATION_RESOURCE_ID);
1051:
1052: -- To find workorder_operation_id from ahl_workorder_operation_v view
1053: CURSOR c_wo_oper (x_id IN NUMBER, x_seq IN NUMBER) IS
1054: SELECT WORKORDER_OPERATION_ID FROM
1055: AHL_WORKORDER_OPERATIONS
1056: --AHL_WORKORDER_OPERATIONS_V
1057: WHERE WORKORDER_ID = x_id AND OPERATION_SEQUENCE_NUM = x_seq;
1058:
1059: -- To find the resource sequence nubmer from ahl_operation_resources
1052: -- To find workorder_operation_id from ahl_workorder_operation_v view
1053: CURSOR c_wo_oper (x_id IN NUMBER, x_seq IN NUMBER) IS
1054: SELECT WORKORDER_OPERATION_ID FROM
1055: AHL_WORKORDER_OPERATIONS
1056: --AHL_WORKORDER_OPERATIONS_V
1057: WHERE WORKORDER_ID = x_id AND OPERATION_SEQUENCE_NUM = x_seq;
1058:
1059: -- To find the resource sequence nubmer from ahl_operation_resources
1060: CURSOR c_resrc_seq (x_id IN NUMBER, x_oper_seq IN NUMBER, x_resrc_seq IN NUMBER) IS
1058:
1059: -- To find the resource sequence nubmer from ahl_operation_resources
1060: CURSOR c_resrc_seq (x_id IN NUMBER, x_oper_seq IN NUMBER, x_resrc_seq IN NUMBER) IS
1061: SELECT COUNT(*) FROM
1062: AHL_WORKORDER_OPERATIONS AWOV, AHL_OPERATION_RESOURCES AOR
1063: WHERE AWOV.WORKORDER_OPERATION_ID = AOR.WORKORDER_OPERATION_ID AND
1064: AWOV.WORKORDER_ID = x_id AND AWOV.OPERATION_SEQUENCE_NUM = x_oper_seq AND
1065: AOR.RESOURCE_SEQUENCE_NUM = x_resrc_seq;
1066:
1112: CURSOR wip_operation_dates (c_workorder_operation_id IN NUMBER)
1113: IS
1114: SELECT first_unit_start_date,
1115: last_unit_completion_date
1116: FROM wip_operations a, ahl_workorders b,ahl_workorder_operations c
1117: WHERE a.wip_entity_id = b.wip_entity_id
1118: AND b.workorder_id = c.workorder_id
1119: AND a.operation_seq_num = c.OPERATION_SEQUENCE_NUM
1120: AND c.workorder_operation_id = c_workorder_operation_id;
1136: IS
1137: SELECT
1138: MIN(wipor.schedule_seq_num)
1139: FROM
1140: ahl_workorder_operations awop,
1141: ahl_workorders awo,
1142: wip_operation_resources wipor
1143: WHERE
1144: awop.operation_sequence_num = wipor.operation_seq_num
2122: x_msg_data OUT NOCOPY VARCHAR2
2123: )
2124: IS
2125:
2126: -- To find the WORKORDER_ID from AHL_WORKORDER_OPERATIONS_V view
2127: CURSOR c_wo_oper (x_id IN NUMBER) IS
2128: SELECT WORKORDER_ID,OPERATION_SEQUENCE_NUM FROM
2129: AHL_WORKORDER_OPERATIONS_V
2130: WHERE WORKORDER_OPERATION_ID = x_id;
2125:
2126: -- To find the WORKORDER_ID from AHL_WORKORDER_OPERATIONS_V view
2127: CURSOR c_wo_oper (x_id IN NUMBER) IS
2128: SELECT WORKORDER_ID,OPERATION_SEQUENCE_NUM FROM
2129: AHL_WORKORDER_OPERATIONS_V
2130: WHERE WORKORDER_OPERATION_ID = x_id;
2131:
2132: -- To Get Wip Entity Id and Org Id
2133: CURSOR c_work_orders (x_id IN NUMBER) IS
2207: CURSOR wip_operation_dates (c_workorder_operation_id IN NUMBER)
2208: IS
2209: SELECT first_unit_start_date,
2210: last_unit_completion_date
2211: FROM wip_operations a, ahl_workorders b,ahl_workorder_operations c
2212: WHERE a.wip_entity_id = b.wip_entity_id
2213: AND b.workorder_id = c.workorder_id
2214: AND a.operation_seq_num = c.OPERATION_SEQUENCE_NUM
2215: AND c.workorder_operation_id = c_workorder_operation_id;
3039:
3040: CURSOR c_WIP_oper (x_id IN NUMBER) IS
3041: SELECT WORV.* FROM
3042: AHL_OPERATION_RESOURCES AOR,
3043: AHL_WORKORDER_OPERATIONS AWO,
3044: AHL_WORKORDERS AW,
3045: WIP_OPERATION_RESOURCES_V WORV
3046: WHERE WORV.OPERATION_SEQ_NUM = AWO.OPERATION_SEQUENCE_NUM
3047: AND WORV.RESOURCE_SEQ_NUM = AOR.RESOURCE_SEQUENCE_NUM
3052: c_WIP_oper_rec c_WIP_oper%ROWTYPE;
3053:
3054: CURSOR c_WO_oper(x_id IN NUMBER) IS
3055: SELECT OPERATION_SEQUENCE_NUM, SCHEDULED_START_DATE, SCHEDULED_END_DATE
3056: FROM AHL_WORKORDER_OPERATIONS_V
3057: WHERE WORKORDER_OPERATION_ID = x_id;
3058:
3059: /* R12 Perf Tuning
3060: * Balaji modified the query to use only base tables
3073: FROM
3074: BOM_RESOURCES BOM,
3075: MTL_UNITS_OF_MEASURE MUOM,
3076: AHL_OPERATION_RESOURCES AOR,
3077: AHL_WORKORDER_OPERATIONS AWO
3078: WHERE
3079: AOR.OPERATION_RESOURCE_ID = x_id AND
3080: AOR.RESOURCE_ID = BOM.RESOURCE_ID AND
3081: BOM.UNIT_OF_MEASURE = MUOM.UOM_CODE AND
3341:
3342: --
3343: CURSOR Get_wo_oper_cur (c_wo_operation_id IN NUMBER)
3344: IS
3345: SELECT * FROM ahl_workorder_operations_v
3346: WHERE workorder_operation_id = c_wo_operation_id;
3347:
3348: --
3349: CURSOR c_chk_assgn (oper_resrc_id IN NUMBER)