[Home] [Help]
87:
88: IF (p_serial_number IS NOT NULL) THEN
89: /*SELECT DISTINCT(instance_id)
90: INTO x_serial_id
91: FROM BOM_DEPT_RES_INSTANCES BDRI, AHL_OPERATION_RESOURCES AOR, AHL_WORKORDERS_V AWO
92: WHERE BDRI.resource_id = AOR.resource_id
93: AND BDRI.department_id = AWO.department_id
94: AND AWO.workorder_id = p_workorder_id
95: AND AOR.operation_resource_id = p_oper_resrc_id
152: BEGIN
153: IF (p_employee_number IS NOT NULL) THEN
154: /*SELECT DISTINCT(PPF.PERSON_ID)
155: INTO x_employee_id
156: FROM PER_PEOPLE_F PPF, BOM_RESOURCE_EMPLOYEES BRE, AHL_OPERATION_RESOURCES AOR,
157: PER_PERSON_TYPES PEPT, AHL_WORKORDERS AWV, AHL_VISITS_B VTB
158: WHERE PPF.PERSON_ID = BRE.PERSON_ID AND BRE.RESOURCE_ID = AOR.RESOURCE_ID
159: AND AWV.VISIT_ID = VTB.VISIT_ID
160: AND BRE.ORGANIZATION_ID = VTB.ORGANIZATION_ID
170: FROM
171: bom_dept_res_instances bdri,
172: wip_operation_resources wor,
173: wip_operations wo
174: ,ahl_workorders awo, ahl_operation_resources aor
175: , mtl_employees_current_view pf
176: ,bom_resource_employees bre
177: where awo.wip_entity_id = wor.wip_entity_id
178: and awo.workorder_id = p_workorder_id
248: /*SELECT PPF.PERSON_ID, PPF.FULL_NAME
249: INTO x_employee_id, x_employee_name
250: FROM PER_PEOPLE_F PPF,
251: BOM_RESOURCE_EMPLOYEES BRE,
252: AHL_OPERATION_RESOURCES AOR,
253: PER_PERSON_TYPES PEPT,
254: AHL_WORKORDERS AWV,
255: AHL_VISITS_B VTB
256: WHERE PPF.PERSON_ID = BRE.PERSON_ID
272: from
273: bom_dept_res_instances bdri,
274: wip_operation_resources wor,
275: wip_operations wo
276: ,ahl_workorders awo, ahl_operation_resources aor
277: , mtl_employees_current_view pf
278: ,bom_resource_employees bre
279: where awo.wip_entity_id = wor.wip_entity_id
280: and awo.workorder_id = p_workorder_id
1036: CURSOR c_oper_resrc (x_id IN NUMBER, x_oper IN NUMBER, x_resrc IN NUMBER) IS
1037: SELECT OPR.operation_resource_id requirement_id ,
1038: WOP.workorder_operation_id operation_id ,
1039: OPR.resource_id
1040: FROM ahl_operation_resources OPR,
1041: ahl_workorder_operations WOP
1042: WHERE OPR.workorder_operation_id = WOP.workorder_operation_id
1043: AND WOP.operation_sequence_num = x_oper
1044: AND OPR.resource_sequence_num = x_resrc
1054: */
1055: --Modified by Srini for performance fix
1056: CURSOR c_resource (x_oper IN NUMBER, x_res IN NUMBER, x_id IN NUMBER) IS
1057: SELECT resource_type resource_type_code
1058: FROM ahl_operation_resources OPR,
1059: ahl_workorder_operations WOP,
1060: bom_resources BOM
1061: WHERE OPR.workorder_operation_id = WOP.workorder_operation_id
1062: AND OPR.resource_id = BOM.resource_id
1074: --Modified by Srini for performance fix
1075: CURSOR c_assign (x_id IN NUMBER, x_oper IN NUMBER, x_resrc IN NUMBER) IS
1076: SELECT OPR.resource_id
1077: FROM ahl_workorder_operations WOP,
1078: ahl_operation_resources OPR
1079: WHERE WOP.workorder_operation_id = OPR.workorder_operation_id
1080: AND WOP.operation_sequence_num = x_oper
1081: AND OPR.resource_sequence_num = x_resrc
1082: AND WOP.workorder_id = x_id;
1129: -- cursor to get the resource req dates
1130: /* CURSOR resrc_req_dates(x_id IN NUMBER, x_oper IN NUMBER, x_resrc IN NUMBER) IS
1131: SELECT scheduled_start_date, scheduled_end_date
1132: FROM ahl_workorder_operations WOP,
1133: ahl_operation_resources OPR
1134: WHERE WOP.workorder_operation_id = OPR.workorder_operation_id
1135: AND WOP.operation_sequence_num = x_oper
1136: AND OPR.resource_sequence_num = x_resrc
1137: AND WOP.workorder_id = x_id;
2004: x_msg_count OUT NOCOPY NUMBER,
2005: x_msg_data OUT NOCOPY VARCHAR2
2006: )
2007: IS
2008: -- To find all information from AHL_OPERATION_RESOURCES view
2009: CURSOR c_assign (x_id IN NUMBER) IS
2010: SELECT * FROM AHL_WORK_ASSIGNMENTS
2011: WHERE ASSIGNMENT_ID = x_id;
2012: c_assign_rec c_assign%ROWTYPE;
2020: SELECT WOA.operation_resource_id requirement_id,
2021: WOP.workorder_id job_id,
2022: BOM.resource_type resource_type_code,
2023: OPR.resource_id
2024: FROM ahl_operation_resources OPR,
2025: ahl_work_assignments WOA,
2026: ahl_workorder_operations WOP,
2027: bom_resources BOM
2028: WHERE OPR.operation_resource_id = WOA.operation_resource_id
2041: --Modified by Srini for Performance fix
2042: CURSOR c_assign1 (x_id IN NUMBER, x_oper IN NUMBER, x_resrc IN NUMBER) IS
2043: SELECT OPR.resource_id
2044: FROM ahl_workorder_operations WOP,
2045: ahl_operation_resources OPR
2046: WHERE WOP.workorder_operation_id = OPR.workorder_operation_id
2047: AND WOP.operation_sequence_num = x_oper
2048: AND OPR.resource_sequence_num = x_resrc
2049: AND WOP.workorder_id = x_id;
2097: -- cursor to get the resource req dates
2098: CURSOR resrc_req_dates(x_id IN NUMBER, x_oper IN NUMBER, x_resrc IN NUMBER) IS
2099: SELECT scheduled_start_date, scheduled_end_date
2100: FROM ahl_workorder_operations WOP,
2101: ahl_operation_resources OPR
2102: WHERE WOP.workorder_operation_id = OPR.workorder_operation_id
2103: AND WOP.operation_sequence_num = x_oper
2104: AND OPR.resource_sequence_num = x_resrc
2105: AND WOP.workorder_id = x_id;
2920: IS
2921: SELECT operation_resource_id,resource_sequence_num,
2922: operation_sequence_num,resource_id,b.workorder_operation_id,
2923: c.workorder_id,wip_entity_id,organization_id,department_id
2924: FROM ahl_operation_resources a, ahl_workorder_operations b, ahl_workorders_v c
2925: WHERE a.WORKORDER_OPERATION_id = b.workorder_operation_id
2926: AND b.workorder_id = c.workorder_id
2927: AND a.operation_resource_id = c_oper_resource_id;
2928: */
2931: IS
2932: SELECT operation_resource_id,resource_sequence_num,
2933: operation_sequence_num,resource_id,b.workorder_operation_id,
2934: c.workorder_id,c.wip_entity_id, d.organization_id,department_id
2935: FROM ahl_operation_resources a, ahl_workorder_operations b,
2936: ahl_workorders c , wip_discrete_jobs d, wip_operations e
2937: WHERE a.WORKORDER_OPERATION_id = b.workorder_operation_id
2938: AND b.workorder_id = c.workorder_id
2939: AND c.wip_entity_id = d.wip_entity_id