[Home] [Help]
232: BEGIN
233: IF (p_workorder_id IS NOT NULL AND
234: p_workorder_id <> FND_API.G_MISS_NUM) THEN
235: SELECT visit_task_id INTO x_visit_task_id
236: FROM AHL_WORKORDERS
237: WHERE workorder_id = p_workorder_id;
238: END IF;
239: x_return_status := Fnd_Api.G_RET_STS_SUCCESS;
240: IF G_DEBUG='Y' THEN
264: --
265: IF p_workorder_id IS NOT NULL THEN
266: --
267: SELECT workorder_id INTO x_workorder_id
268: FROM AHL_WORKORDERS
269: WHERE workorder_id = p_workorder_id;
270: ELSE
271: --
272: SELECT workorder_id INTO x_workorder_id
269: WHERE workorder_id = p_workorder_id;
270: ELSE
271: --
272: SELECT workorder_id INTO x_workorder_id
273: FROM AHL_WORKORDERS
274: WHERE workorder_name = p_job_number;
275: END IF;
276: x_return_status := Fnd_Api.G_RET_STS_SUCCESS;
277: IF G_DEBUG='Y' THEN
1249: CURSOR Get_job_number(c_workorder_id IN NUMBER)
1250: IS
1251: SELECT workorder_name job_number,
1252: wip_entity_id
1253: FROM ahl_workorders
1254: WHERE workorder_id = c_workorder_id;
1255: -- Fix for bug# 6594189. Allow for statuses all statuses other than closed,
1256: -- cancelled, parts hold etc.
1257: --Check for status
1256: -- cancelled, parts hold etc.
1257: --Check for status
1258: CURSOR Check_wo_status_cur(c_workorder_id IN NUMBER)
1259: IS
1260: SELECT 1 FROM ahl_workorders
1261: WHERE workorder_id = c_workorder_id
1262: /*
1263: AND (status_code = 3 or
1264: status_code = 1);
1270: */
1271: CURSOR Check_stg_workorder_csr (c_workorder_id NUMBER)
1272: IS
1273: SELECT 1
1274: FROM AHL_WORKORDERS AHLW,
1275: AHL_VISIT_TASKS_B AVTB
1276:
1277: WHERE AHLW.workorder_id = c_workorder_id
1278: AND AHLW.status_code = '17'
1341: CURSOR Get_Alt_Mat_Requirment_Count(c_workorder_id IN NUMBER)
1342: IS
1343: SELECT COUNT(scheduled_material_id)
1344: FROM AHL_SCHEDULE_MATERIALS MAT,
1345: AHL_WORKORDERS WO
1346: WHERE WO.WORKORDER_ID = c_workorder_id
1347: AND WO.VISIT_TASK_ID = MAT.VISIT_TASK_ID
1348: AND NVL(MAT.STATUS,'NOTDEL') <> 'DELETED'
1349: AND
2745: l_WorkOrders_Table(nvl(l_WorkOrders_Table.LAST,-1) + 1) := p_x_req_material_tbl(i).workorder_id;
2746: END IF;
2747: END LOOP;
2748: END IF;
2749: --change set ITEM_ALTERNATES_EXIST to Y in AHL_WORKORDERS if workorder contains
2750: --item group based or position based material requirements
2751: IF ( l_WorkOrders_Table.COUNT > 0 ) THEN
2752: FOR i IN l_WorkOrders_Table.FIRST..l_WorkOrders_Table.LAST
2753: LOOP
2755: OPEN Get_Alt_Mat_Requirment_Count(l_WorkOrders_Table(i));
2756: FETCH Get_Alt_Mat_Requirment_Count INTO l_alt_mat_req_count;
2757: CLOSE Get_Alt_Mat_Requirment_Count;
2758: IF (l_alt_mat_req_count > 0) THEN
2759: UPDATE AHL_WORKORDERS
2760: SET ITEM_ALTERNATES_EXIST = 'Y'
2761: WHERE WORKORDER_ID = l_WorkOrders_Table(i);
2762: END IF;
2763: END LOOP;
2945: CURSOR Check_wo_status_cur(c_workorder_id IN NUMBER,
2946: c_module_type IN VARCHAR2)
2947: IS
2948: SELECT 1
2949: FROM ahl_workorders
2950: WHERE workorder_id = c_workorder_id
2951: AND (status_code = 3 or status_code = 1 or
2952: (c_module_type = 'VWP' and status_code = 17));
2953:
2962: -- Get job number details
2963: CURSOR Get_job_number(c_workorder_id IN NUMBER)
2964: IS
2965: SELECT workorder_name
2966: FROM ahl_workorders
2967: WHERE workorder_id = c_workorder_id;
2968: -- rroy
2969: -- ACL Changes
2970: -- R12: Serial Reservation changes.
3797: --Get job number
3798: CURSOR Get_job_details(c_workorder_id IN NUMBER)
3799: IS
3800: SELECT workorder_name
3801: FROM ahl_workorders
3802: WHERE workorder_id = c_workorder_id;
3803: -- Bug # 6680137 - begin.
3804: CURSOR c_get_wo_status(c_workorder_id IN NUMBER)
3805: IS
3805: IS
3806: SELECT
3807: AWO.status_code
3808: FROM
3809: AHL_WORKORDERS AWO
3810: WHERE
3811: workorder_id = c_workorder_id;
3812: -- Bug # 6680137 - end
3813: -- Get schedule material details
3859: AWOS.wip_entity_id,
3860: --debadey: Marshalling FDD section 5.15, AOG flag updation
3861: ASML.aog_flag
3862: FROM
3863: AHL_WORKORDERS AWOS,
3864: AHL_SCHEDULE_MATERIALS ASML,
3865: wip_requirement_operations wipr,
3866: MTL_SYSTEM_ITEMS_VL MSIV,
3867: AHL_VISITS_VL AVST,
4275: AND mrv.organization_id = asmt.organization_id
4276: AND mrv.demand_source_line_detail = c_schedule_material_id) reserved_quantity,
4277: wo.workorder_id,
4278: vst.organization_id
4279: FROM ahl_schedule_materials asm, ahl_workorders wo, ahl_visits_b vst
4280: WHERE asm.visit_task_id = wo.visit_task_id
4281: AND vst.visit_id = asm.visit_id
4282: AND scheduled_material_id = c_schedule_material_id;
4283:
4283:
4284: CURSOR Get_job_details(c_workorder_id IN NUMBER)
4285: IS
4286: SELECT workorder_name
4287: FROM ahl_workorders
4288: WHERE workorder_id = c_workorder_id
4289: AND (status_code = 3 or
4290: status_code = 1 or
4291: status_code = 17);
4294: --Following cursor will be used when call is from Marshling.
4295: CURSOR Get_job_details_for_exe(c_workorder_id IN NUMBER)
4296: IS
4297: SELECT workorder_name
4298: FROM ahl_workorders
4299: WHERE workorder_id = c_workorder_id
4300: AND status_code NOT IN (12,17,18,22,4,5,7,14,21); --Closed,Draft,Deffered,Deleted,complete,Complete No-charge,Cancelled,Pending Close,Pending Defer/Cancel Approval
4301:
4302: --sukhwsin::Marshling Enhancements. Code Changes ends
4311: wip.organization_id,
4312: wip.department_id
4313: FROM wip_requirement_operations wip,
4314: ahl_schedule_materials asm,
4315: ahl_workorders wo
4316: WHERE wip.wip_entity_id = wo.wip_entity_id
4317: AND wo.visit_task_id = asm.visit_task_id
4318: AND asm.operation_sequence = wip.operation_seq_num
4319: AND asm.inventory_item_id = wip.inventory_item_id
5654: IS
5655: SELECT a.visit_id,
5656: visit_task_id,
5657: organization_id
5658: FROM ahl_workorders A,
5659: ahl_visits_b b
5660: WHERE workorder_id = c_workorder_id
5661: AND a.visit_id = b.visit_id;
5662: CURSOR Material_Detail_Cur (c_operation_id IN NUMBER,
6472: ,asm.position_key
6473: ,asm.item_group_id
6474: ,wo.workorder_id
6475: ,wo.status_code
6476: FROM ahl_schedule_materials asm, ahl_workorders wo
6477: WHERE
6478: asm.scheduled_material_id = c_asm_id
6479: AND asm.status = 'ACTIVE'
6480: AND asm.visit_task_id = wo.visit_task_id;