[Home] [Help]
2306: )IS
2307: CURSOR val_workorder_id_csr(p_workorder_id IN NUMBER) IS
2308: --Modified by mpothuku on 27-Feb-06 to fix the Perf Bug #4919164
2309: /*
2310: SELECT 'x' FROM ahl_workorders_osp_v
2311: WHERE workorder_id = p_workorder_id
2312: AND upper(department_class_code) = 'VENDOR'
2313: AND job_status_code = G_OSP_WO_RELEASED;
2314: */
2312: AND upper(department_class_code) = 'VENDOR'
2313: AND job_status_code = G_OSP_WO_RELEASED;
2314: */
2315: SELECT 'x'
2316: FROM ahl_workorders wo,
2317: wip_discrete_jobs wdj,
2318: bom_departments bmd,
2319: ahl_visits_b vst,
2320: ahl_visit_tasks_b vts,
2361: p_service_item_id IN NUMBER,
2362: p_order_type_code IN VARCHAR2
2363: )IS
2364: CURSOR val_service_item_id_inv_csr(p_service_item_id IN VARCHAR2, p_workorder_id IN NUMBER) IS
2365: SELECT 'x' FROM mtl_system_items_kfv MTL, ahl_workorders_osp_v WO
2366: WHERE MTL.inventory_item_id = p_service_item_id
2367: AND MTL.enabled_flag = G_YES_FLAG
2368: AND MTL.inventory_item_flag = G_NO_FLAG
2369: AND MTL.stock_enabled_flag = G_NO_FLAG
2373: AND NVL(outside_operation_flag, G_NO_FLAG) = G_NO_FLAG
2374: AND MTL.organization_id = WO.organization_id
2375: AND WO.workorder_id = p_workorder_id;
2376: CURSOR val_service_item_id_wo_csr(p_workorder_id IN NUMBER, p_service_item_id IN NUMBER) IS
2377: SELECT 'x' from ahl_workorders_osp_v
2378: WHERE workorder_id = p_workorder_id
2379: AND NVL(service_item_id,p_service_item_id) = p_service_item_id;
2380: CURSOR val_service_item_id_nll_csr(p_workorder_id IN NUMBER) IS
2381: SELECT 'x' from ahl_workorders_osp_v
2377: SELECT 'x' from ahl_workorders_osp_v
2378: WHERE workorder_id = p_workorder_id
2379: AND NVL(service_item_id,p_service_item_id) = p_service_item_id;
2380: CURSOR val_service_item_id_nll_csr(p_workorder_id IN NUMBER) IS
2381: SELECT 'x' from ahl_workorders_osp_v
2382: WHERE workorder_id = p_workorder_id
2383: AND service_item_id IS NULL;
2384:
2385: CURSOR item_exists_in_wo_org_csr(p_workorder_id IN NUMBER) IS
2383: AND service_item_id IS NULL;
2384:
2385: CURSOR item_exists_in_wo_org_csr(p_workorder_id IN NUMBER) IS
2386: SELECT 'x'
2387: from ahl_workorders_osp_v wo, ahl_mtl_items_ou_v mtl
2388: where wo.workorder_id = p_workorder_id and
2389: wo.service_item_id = mtl.inventory_item_id and
2390: mtl.inventory_org_id = wo.organization_id;
2391: l_exist VARCHAR2(1);
2496: p_workorder_id IN NUMBER,
2497: p_service_item_id IN NUMBER
2498: )IS
2499: CURSOR val_service_item_id_inv_csr(p_service_item_id IN VARCHAR2, p_workorder_id IN NUMBER) IS
2500: SELECT 'x' FROM mtl_system_items_kfv MTL, ahl_workorders WO, AHL_VISITS_B VST
2501: WHERE MTL.inventory_item_id = p_service_item_id
2502: AND MTL.enabled_flag = G_YES_FLAG
2503: AND MTL.inventory_item_flag = G_NO_FLAG
2504: AND MTL.stock_enabled_flag = G_NO_FLAG
2510: AND VST.visit_id = WO.visit_id
2511: AND WO.workorder_id = p_workorder_id;
2512:
2513: CURSOR val_service_item_id_wo_csr(p_workorder_id IN NUMBER, p_service_item_id IN NUMBER) IS
2514: SELECT 'x' from ahl_workorders WO, AHL_ROUTES_B ARB
2515: WHERE WO.workorder_id = p_workorder_id
2516: AND WO.ROUTE_ID = ARB.ROUTE_ID (+)
2517: AND NVL(ARB.service_item_id, p_service_item_id) = p_service_item_id;
2518:
2516: AND WO.ROUTE_ID = ARB.ROUTE_ID (+)
2517: AND NVL(ARB.service_item_id, p_service_item_id) = p_service_item_id;
2518:
2519: CURSOR val_service_item_id_nll_csr(p_workorder_id IN NUMBER) IS
2520: SELECT 'x' from ahl_workorders WO, AHL_ROUTES_B ARB
2521: WHERE WO.workorder_id = p_workorder_id
2522: AND WO.ROUTE_ID = ARB.ROUTE_ID (+)
2523: AND ARB.service_item_id IS NULL;
2524:
2523: AND ARB.service_item_id IS NULL;
2524:
2525: CURSOR item_exists_in_wo_org_csr(p_workorder_id IN NUMBER) IS
2526: SELECT 'x'
2527: from ahl_workorders WO, AHL_ROUTES_B ARB, AHL_VISITS_B VST, ahl_mtl_items_ou_v mtl
2528: where wo.workorder_id = p_workorder_id and
2529: WO.ROUTE_ID = ARB.ROUTE_ID (+) and
2530: ARB.service_item_id = mtl.inventory_item_id and
2531: VST.visit_id = WO.visit_id and
2786: l_msg_count NUMBER;
2787: l_msg_data VARCHAR2(1000);
2788: l_shipment_IDs_Tbl AHL_OSP_SHIPMENT_PUB.Ship_ID_Tbl_Type;
2789: CURSOR val_all_wo_closed(p_osp_order_id IN NUMBER) IS
2790: --Modified by mpothuku on 27-Feb-06 to use ahl_workorders instead of ahl_workorders_osp_v
2791: --to fix the Perf Bug #4919164
2792: SELECT 'x' from ahl_osp_order_lines OL, ahl_workorders WO
2793: WHERE OL.osp_order_id = p_osp_order_id
2794: AND OL.workorder_id = WO.workorder_id
2788: l_shipment_IDs_Tbl AHL_OSP_SHIPMENT_PUB.Ship_ID_Tbl_Type;
2789: CURSOR val_all_wo_closed(p_osp_order_id IN NUMBER) IS
2790: --Modified by mpothuku on 27-Feb-06 to use ahl_workorders instead of ahl_workorders_osp_v
2791: --to fix the Perf Bug #4919164
2792: SELECT 'x' from ahl_osp_order_lines OL, ahl_workorders WO
2793: WHERE OL.osp_order_id = p_osp_order_id
2794: AND OL.workorder_id = WO.workorder_id
2795: AND OL.status_code IS NULL
2796: AND WO.status_code NOT IN(G_OSP_WO_CANCELLED,G_OSP_WO_CLOSED);
4060: --Modified by mpothuku on 27-Feb-06 to fix the Perf Bug #4919164
4061: /*
4062: SELECT organization_id, inventory_item_id, service_item_id
4063: INTO l_organization_id, l_inventory_item_id, l_service_item_id
4064: FROM ahl_workorders_osp_v
4065: WHERE workorder_id = p_x_osp_order_lines_tbl(i).workorder_id;
4066: */
4067: SELECT vst.organization_id, vts.inventory_item_id, arb.service_item_id
4068: INTO l_organization_id, l_inventory_item_id, l_service_item_id
4065: WHERE workorder_id = p_x_osp_order_lines_tbl(i).workorder_id;
4066: */
4067: SELECT vst.organization_id, vts.inventory_item_id, arb.service_item_id
4068: INTO l_organization_id, l_inventory_item_id, l_service_item_id
4069: FROM ahl_workorders wo,
4070: ahl_visits_b vst,
4071: ahl_visit_tasks_b vts,
4072: ahl_routes_b arb,
4073: inv_organization_info_v org
4422: item_instance_uom,
4423: service_item_id,
4424: service_item_description,
4425: service_item_uom
4426: FROM ahl_workorders_osp_v
4427: WHERE workorder_id = c_workorder_id;
4428: */
4429: SELECT vts.inventory_item_id,
4430: vst.organization_id,
4434: csii.unit_of_measure item_instance_uom,
4435: arb.service_item_id,
4436: mtls.description service_item_description,
4437: mtls.primary_uom_code service_item_uom
4438: FROM ahl_workorders wo,
4439: ahl_visits_b vst,
4440: ahl_visit_tasks_b vts,
4441: csi_item_instances csii,
4442: mtl_system_items_kfv mtls,
5077: csii.unit_of_measure item_instance_uom,
5078: arb.service_item_id,
5079: mtls.description service_item_description,
5080: mtls.primary_uom_code service_item_uom
5081: FROM ahl_workorders wo,
5082: ahl_visits_b vst,
5083: ahl_visit_tasks_b vts,
5084: csi_item_instances csii,
5085: mtl_system_items_kfv mtls,
6497: ospl.attribute13,
6498: ospl.attribute14,
6499: ospl.attribute15
6500: FROM ahl_osp_order_lines ospl,
6501: ahl_workorders wo,
6502: ahl_visits_b vst,
6503: ahl_visit_tasks_b vts,
6504: csi_item_instances csiwo,
6505: csi_item_instances csiex,
6900: CURSOR workorder_id_csr(p_job_number IN VARCHAR2) IS
6901: --Modified by mpothuku on 27-Feb-06 to fix the Perf Bug #4919164
6902: /*
6903: SELECT workorder_id
6904: FROM ahl_workorders_osp_v
6905: WHERE job_number = p_job_number;
6906: */
6907: SELECT workorder_id from ahl_workorders
6908: WHERE workorder_name = p_job_number;
6903: SELECT workorder_id
6904: FROM ahl_workorders_osp_v
6905: WHERE job_number = p_job_number;
6906: */
6907: SELECT workorder_id from ahl_workorders
6908: WHERE workorder_name = p_job_number;
6909:
6910: l_workorder_id NUMBER;
6911: CURSOR service_item_id_csr(p_service_item_number IN VARCHAR2, p_inventory_org_id NUMBER) IS