[Home] [Help]
1032: SELECT WO.workorder_id workorder_id,
1033: WO.object_version_number object_version_number,
1034: WO.wip_entity_id wip_entity_id,
1035: WO.status_code status_code
1036: FROM AHL_WORKORDERS WO,
1037: WIP_SCHED_RELATIONSHIPS WOR
1038: WHERE WO.wip_entity_id = WOR.parent_object_id
1039: AND WO.master_workorder_flag = 'Y'
1040: AND WO.status_code <> '22'
1047: IS
1048: SELECT
1049: wipj.scheduled_completion_date
1050: FROM
1051: ahl_workorders awo,
1052: wip_discrete_jobs wipj
1053: WHERE
1054: wipj.wip_entity_id = awo.wip_entity_id
1055: AND awo.master_workorder_flag = 'Y'
1057: AND awo.visit_id = (
1058: SELECT
1059: awov.visit_id
1060: FROM
1061: ahl_workorders awov
1062: WHERE
1063: awov.workorder_id = c_workorder_id
1064: );
1065:
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;
1250:
1251: -- 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
1262: AHL_VISITS_B V, AHL_VISIT_TASKS_B T, AHL_WORKORDERS W
1263: WHERE W.VISIT_TASK_ID = T.VISIT_TASK_ID AND T.VISIT_ID = V.VISIT_ID
1264: AND W.VISIT_TASK_ID = x_id;
1265:
1266: -- To find the UOM_CODE from MTL_UNITS_OF_MEASURE table
1281: c_op_seq_num IN NUMBER)
1282: IS
1283: SELECT first_unit_start_date,
1284: last_unit_completion_date
1285: FROM wip_operations a, ahl_workorders b
1286: WHERE a.wip_entity_id = b.wip_entity_id
1287: AND workorder_id = c_workorder_id
1288: AND operation_seq_num = c_op_seq_num;*/
1289: --fix for bug number 6211089
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;
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
1323: AND awo.wip_entity_id = wipor.wip_entity_id
2330: -- To Get Wip Entity Id and Org Id
2331: CURSOR c_work_orders (x_id IN NUMBER) IS
2332: SELECT WIP_ENTITY_ID,WORKORDER_NAME,
2333: ORGANIZATION_ID FROM
2334: AHL_WORKORDERS A, AHL_VISIT_TASKS_B B,
2335: AHL_VISITS_B C
2336: WHERE WORKORDER_ID = x_id
2337: AND A.VISIT_TASK_ID = B.VISIT_TASK_ID
2338: AND B.VISIT_ID = C.VISIT_ID;
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
2357: */
2358: CURSOR c_workorder (x_id IN NUMBER) IS
2359: --SELECT * FROM AHL_WORKORDERS_V
2360: --WHERE WORKORDER_ID = x_id;
2355: * Balaji modified the query to use only base tables
2356: * instead of AHL_WORKORDERS_V
2357: */
2358: CURSOR c_workorder (x_id IN NUMBER) IS
2359: --SELECT * FROM AHL_WORKORDERS_V
2360: --WHERE WORKORDER_ID = x_id;
2361: SELECT
2362: WO.visit_task_id,
2363: WDJ.owning_department department_id
2361: SELECT
2362: WO.visit_task_id,
2363: WDJ.owning_department department_id
2364: FROM
2365: AHL_WORKORDERS WO,
2366: WIP_DISCRETE_JOBS WDJ
2367: WHERE
2368: WO.workorder_id = x_id AND
2369: WDJ.wip_entity_id = wo.wip_entity_id;
2395: c_op_seq_num IN NUMBER)
2396: IS
2397: SELECT first_unit_start_date,
2398: last_unit_completion_date, a.department_id
2399: FROM wip_operations a, ahl_workorders b
2400: WHERE a.wip_entity_id = b.wip_entity_id
2401: AND workorder_id = c_workorder_id
2402: AND operation_seq_num = c_op_seq_num;*/
2403:
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;
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
3270: AND AWO.OPERATION_SEQUENCE_NUM = WOR.OPERATION_SEQ_NUM
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
3293: AND WORV.WIP_ENTITY_ID = AW.WIP_ENTITY_ID
3568: WHERE operation_resource_id = c_op_resource_id;
3569: --
3570: /* R12 Perf Tuning
3571: * Balaji modified the query to use only base tables
3572: * instead of AHL_WORKORDERS_V
3573: */
3574: CURSOR Get_job_number(c_workorder_id IN NUMBER)
3575: IS
3576: SELECT
3577: wo.workorder_name,
3578: wdj.organization_id,
3579: wo.wip_entity_id
3580: FROM
3581: ahl_workorders wo,
3582: wip_discrete_jobs wdj
3583: WHERE
3584: wo.workorder_id = c_workorder_id AND
3585: wdj.wip_entity_id = wo.wip_entity_id;
4043: SELECT
4044: WDJ.firm_planned_flag
4045: FROM
4046: WIP_DISCRETE_JOBS WDJ,
4047: AHL_WORKORDERS AWO
4048: WHERE
4049: AWO.wip_entity_id = WDJ.wip_entity_id AND
4050: AWO.workorder_id = c_workorder_id;
4051: