[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
246: /*SELECT PPF.PERSON_ID, PPF.FULL_NAME
247: INTO x_employee_id, x_employee_name
248: FROM PER_PEOPLE_F PPF,
249: BOM_RESOURCE_EMPLOYEES BRE,
250: AHL_OPERATION_RESOURCES AOR,
251: PER_PERSON_TYPES PEPT,
252: AHL_WORKORDERS AWV,
253: AHL_VISITS_B VTB
254: WHERE PPF.PERSON_ID = BRE.PERSON_ID
270: from
271: bom_dept_res_instances bdri,
272: wip_operation_resources wor,
273: wip_operations wo
274: ,ahl_workorders awo, ahl_operation_resources aor
275: , mtl_employees_current_view pf
276: ,bom_resource_employees bre
277: where awo.wip_entity_id = wor.wip_entity_id
278: and awo.workorder_id = p_workorder_id
1034: CURSOR c_oper_resrc (x_id IN NUMBER, x_oper IN NUMBER, x_resrc IN NUMBER) IS
1035: SELECT OPR.operation_resource_id requirement_id ,
1036: WOP.workorder_operation_id operation_id ,
1037: OPR.resource_id
1038: FROM ahl_operation_resources OPR,
1039: ahl_workorder_operations WOP
1040: WHERE OPR.workorder_operation_id = WOP.workorder_operation_id
1041: AND WOP.operation_sequence_num = x_oper
1042: AND OPR.resource_sequence_num = x_resrc
1052: */
1053: --Modified by Srini for performance fix
1054: CURSOR c_resource (x_oper IN NUMBER, x_res IN NUMBER, x_id IN NUMBER) IS
1055: SELECT resource_type resource_type_code
1056: FROM ahl_operation_resources OPR,
1057: ahl_workorder_operations WOP,
1058: bom_resources BOM
1059: WHERE OPR.workorder_operation_id = WOP.workorder_operation_id
1060: AND OPR.resource_id = BOM.resource_id
1072: --Modified by Srini for performance fix
1073: CURSOR c_assign (x_id IN NUMBER, x_oper IN NUMBER, x_resrc IN NUMBER) IS
1074: SELECT OPR.resource_id
1075: FROM ahl_workorder_operations WOP,
1076: ahl_operation_resources OPR
1077: WHERE WOP.workorder_operation_id = OPR.workorder_operation_id
1078: AND WOP.operation_sequence_num = x_oper
1079: AND OPR.resource_sequence_num = x_resrc
1080: AND WOP.workorder_id = x_id;
1127: -- cursor to get the resource req dates
1128: /* CURSOR resrc_req_dates(x_id IN NUMBER, x_oper IN NUMBER, x_resrc IN NUMBER) IS
1129: SELECT scheduled_start_date, scheduled_end_date
1130: FROM ahl_workorder_operations WOP,
1131: ahl_operation_resources OPR
1132: WHERE WOP.workorder_operation_id = OPR.workorder_operation_id
1133: AND WOP.operation_sequence_num = x_oper
1134: AND OPR.resource_sequence_num = x_resrc
1135: AND WOP.workorder_id = x_id;
2002: x_msg_count OUT NOCOPY NUMBER,
2003: x_msg_data OUT NOCOPY VARCHAR2
2004: )
2005: IS
2006: -- To find all information from AHL_OPERATION_RESOURCES view
2007: CURSOR c_assign (x_id IN NUMBER) IS
2008: SELECT * FROM AHL_WORK_ASSIGNMENTS
2009: WHERE ASSIGNMENT_ID = x_id;
2010: c_assign_rec c_assign%ROWTYPE;
2018: SELECT WOA.operation_resource_id requirement_id,
2019: WOP.workorder_id job_id,
2020: BOM.resource_type resource_type_code,
2021: OPR.resource_id
2022: FROM ahl_operation_resources OPR,
2023: ahl_work_assignments WOA,
2024: ahl_workorder_operations WOP,
2025: bom_resources BOM
2026: WHERE OPR.operation_resource_id = WOA.operation_resource_id
2039: --Modified by Srini for Performance fix
2040: CURSOR c_assign1 (x_id IN NUMBER, x_oper IN NUMBER, x_resrc IN NUMBER) IS
2041: SELECT OPR.resource_id
2042: FROM ahl_workorder_operations WOP,
2043: ahl_operation_resources OPR
2044: WHERE WOP.workorder_operation_id = OPR.workorder_operation_id
2045: AND WOP.operation_sequence_num = x_oper
2046: AND OPR.resource_sequence_num = x_resrc
2047: AND WOP.workorder_id = x_id;
2095: -- cursor to get the resource req dates
2096: CURSOR resrc_req_dates(x_id IN NUMBER, x_oper IN NUMBER, x_resrc IN NUMBER) IS
2097: SELECT scheduled_start_date, scheduled_end_date
2098: FROM ahl_workorder_operations WOP,
2099: ahl_operation_resources OPR
2100: WHERE WOP.workorder_operation_id = OPR.workorder_operation_id
2101: AND WOP.operation_sequence_num = x_oper
2102: AND OPR.resource_sequence_num = x_resrc
2103: AND WOP.workorder_id = x_id;
2918: IS
2919: SELECT operation_resource_id,resource_sequence_num,
2920: operation_sequence_num,resource_id,b.workorder_operation_id,
2921: c.workorder_id,wip_entity_id,organization_id,department_id
2922: FROM ahl_operation_resources a, ahl_workorder_operations b, ahl_workorders_v c
2923: WHERE a.WORKORDER_OPERATION_id = b.workorder_operation_id
2924: AND b.workorder_id = c.workorder_id
2925: AND a.operation_resource_id = c_oper_resource_id;
2926: */
2929: IS
2930: SELECT operation_resource_id,resource_sequence_num,
2931: operation_sequence_num,resource_id,b.workorder_operation_id,
2932: c.workorder_id,c.wip_entity_id, d.organization_id,department_id
2933: FROM ahl_operation_resources a, ahl_workorder_operations b,
2934: ahl_workorders c , wip_discrete_jobs d, wip_operations e
2935: WHERE a.WORKORDER_OPERATION_id = b.workorder_operation_id
2936: AND b.workorder_id = c.workorder_id
2937: AND c.wip_entity_id = d.wip_entity_id