[Home] [Help]
1065: AOR.RESOURCE_SEQUENCE_NUM = x_resrc_seq;
1066:
1067: -- To find the resource sequence nubmer from ahl_operation_resources
1068: CURSOR c_workorder (x_id IN NUMBER) IS
1069: SELECT * FROM AHL_WORKORDERS
1070: WHERE WORKORDER_ID = x_id;
1071: c_workorder_rec c_workorder%ROWTYPE;
1072:
1073: -- To find the resource sequence nubmer from ahl_operation_resources
1080: CURSOR c_wo_dept (x_id IN NUMBER) IS
1081: SELECT --V.DEPARTMENT_ID, -- department should be from wip_operations
1082: V.ORGANIZATION_ID,
1083: WORKORDER_NAME, WIP_ENTITY_ID FROM
1084: AHL_VISITS_B V, AHL_VISIT_TASKS_B T, AHL_WORKORDERS W
1085: WHERE W.VISIT_TASK_ID = T.VISIT_TASK_ID AND T.VISIT_ID = V.VISIT_ID
1086: AND W.VISIT_TASK_ID = x_id;
1087:
1088: -- To find the UOM_CODE from MTL_UNITS_OF_MEASURE table
1103: c_op_seq_num IN NUMBER)
1104: IS
1105: SELECT first_unit_start_date,
1106: last_unit_completion_date
1107: FROM wip_operations a, ahl_workorders b
1108: WHERE a.wip_entity_id = b.wip_entity_id
1109: AND workorder_id = c_workorder_id
1110: AND operation_seq_num = c_op_seq_num;*/
1111: --fix for bug number 6211089
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;
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
1145: AND awo.wip_entity_id = wipor.wip_entity_id
2132: -- To Get Wip Entity Id and Org Id
2133: CURSOR c_work_orders (x_id IN NUMBER) IS
2134: SELECT WIP_ENTITY_ID,WORKORDER_NAME,
2135: ORGANIZATION_ID FROM
2136: AHL_WORKORDERS A, AHL_VISIT_TASKS_B B,
2137: AHL_VISITS_B C
2138: WHERE WORKORDER_ID = x_id
2139: AND A.VISIT_TASK_ID = B.VISIT_TASK_ID
2140: AND B.VISIT_ID = C.VISIT_ID;
2154: -- To find the resource sequence nubmer from ahl_operation_resources
2155: /*
2156: * R12 Perf Tuning
2157: * Balaji modified the query to use only base tables
2158: * instead of AHL_WORKORDERS_V
2159: */
2160: CURSOR c_workorder (x_id IN NUMBER) IS
2161: --SELECT * FROM AHL_WORKORDERS_V
2162: --WHERE WORKORDER_ID = x_id;
2157: * Balaji modified the query to use only base tables
2158: * instead of AHL_WORKORDERS_V
2159: */
2160: CURSOR c_workorder (x_id IN NUMBER) IS
2161: --SELECT * FROM AHL_WORKORDERS_V
2162: --WHERE WORKORDER_ID = x_id;
2163: SELECT
2164: WO.visit_task_id,
2165: WDJ.owning_department department_id
2163: SELECT
2164: WO.visit_task_id,
2165: WDJ.owning_department department_id
2166: FROM
2167: AHL_WORKORDERS WO,
2168: WIP_DISCRETE_JOBS WDJ
2169: WHERE
2170: WO.workorder_id = x_id AND
2171: WDJ.wip_entity_id = wo.wip_entity_id;
2197: c_op_seq_num IN NUMBER)
2198: IS
2199: SELECT first_unit_start_date,
2200: last_unit_completion_date, a.department_id
2201: FROM wip_operations a, ahl_workorders b
2202: WHERE a.wip_entity_id = b.wip_entity_id
2203: AND workorder_id = c_workorder_id
2204: AND operation_seq_num = c_op_seq_num;*/
2205:
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;
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
3048: AND WORV.WIP_ENTITY_ID = AW.WIP_ENTITY_ID
3323: WHERE operation_resource_id = c_op_resource_id;
3324: --
3325: /* R12 Perf Tuning
3326: * Balaji modified the query to use only base tables
3327: * instead of AHL_WORKORDERS_V
3328: */
3329: CURSOR Get_job_number(c_workorder_id IN NUMBER)
3330: IS
3331: SELECT
3332: wo.workorder_name,
3333: wdj.organization_id,
3334: wo.wip_entity_id
3335: FROM
3336: ahl_workorders wo,
3337: wip_discrete_jobs wdj
3338: WHERE
3339: wo.workorder_id = c_workorder_id AND
3340: wdj.wip_entity_id = wo.wip_entity_id;
3798: SELECT
3799: WDJ.firm_planned_flag
3800: FROM
3801: WIP_DISCRETE_JOBS WDJ,
3802: AHL_WORKORDERS AWO
3803: WHERE
3804: AWO.wip_entity_id = WDJ.wip_entity_id AND
3805: AWO.workorder_id = c_workorder_id;
3806: