[Home] [Help]
2327: )IS
2328: CURSOR val_workorder_id_csr(p_workorder_id IN NUMBER) IS
2329: --Modified by mpothuku on 27-Feb-06 to fix the Perf Bug #4919164
2330: /*
2331: SELECT 'x' FROM ahl_workorders_osp_v
2332: WHERE workorder_id = p_workorder_id
2333: AND upper(department_class_code) = 'VENDOR'
2334: AND job_status_code = G_OSP_WO_RELEASED;
2335: */
2333: AND upper(department_class_code) = 'VENDOR'
2334: AND job_status_code = G_OSP_WO_RELEASED;
2335: */
2336: SELECT 'x'
2337: FROM ahl_workorders wo,
2338: wip_discrete_jobs wdj,
2339: bom_departments bmd,
2340: ahl_visits_b vst,
2341: ahl_visit_tasks_b vts,
2382: p_service_item_id IN NUMBER,
2383: p_order_type_code IN VARCHAR2
2384: )IS
2385: CURSOR val_service_item_id_inv_csr(p_service_item_id IN VARCHAR2, p_workorder_id IN NUMBER) IS
2386: SELECT 'x' FROM mtl_system_items_kfv MTL, ahl_workorders_osp_v WO
2387: WHERE MTL.inventory_item_id = p_service_item_id
2388: AND MTL.enabled_flag = G_YES_FLAG
2389: AND MTL.inventory_item_flag = G_NO_FLAG
2390: AND MTL.stock_enabled_flag = G_NO_FLAG
2394: AND NVL(outside_operation_flag, G_NO_FLAG) = G_NO_FLAG
2395: AND MTL.organization_id = WO.organization_id
2396: AND WO.workorder_id = p_workorder_id;
2397: CURSOR val_service_item_id_wo_csr(p_workorder_id IN NUMBER, p_service_item_id IN NUMBER) IS
2398: SELECT 'x' from ahl_workorders_osp_v
2399: WHERE workorder_id = p_workorder_id
2400: AND NVL(service_item_id,p_service_item_id) = p_service_item_id;
2401: CURSOR val_service_item_id_nll_csr(p_workorder_id IN NUMBER) IS
2402: SELECT 'x' from ahl_workorders_osp_v
2398: SELECT 'x' from ahl_workorders_osp_v
2399: WHERE workorder_id = p_workorder_id
2400: AND NVL(service_item_id,p_service_item_id) = p_service_item_id;
2401: CURSOR val_service_item_id_nll_csr(p_workorder_id IN NUMBER) IS
2402: SELECT 'x' from ahl_workorders_osp_v
2403: WHERE workorder_id = p_workorder_id
2404: AND service_item_id IS NULL;
2405:
2406: CURSOR item_exists_in_wo_org_csr(p_workorder_id IN NUMBER) IS
2404: AND service_item_id IS NULL;
2405:
2406: CURSOR item_exists_in_wo_org_csr(p_workorder_id IN NUMBER) IS
2407: SELECT 'x'
2408: from ahl_workorders_osp_v wo, ahl_mtl_items_ou_v mtl
2409: where wo.workorder_id = p_workorder_id and
2410: wo.service_item_id = mtl.inventory_item_id and
2411: mtl.inventory_org_id = wo.organization_id;
2412: l_exist VARCHAR2(1);
2517: p_workorder_id IN NUMBER,
2518: p_service_item_id IN NUMBER
2519: )IS
2520: CURSOR val_service_item_id_inv_csr(p_service_item_id IN VARCHAR2, p_workorder_id IN NUMBER) IS
2521: SELECT 'x' FROM mtl_system_items_kfv MTL, ahl_workorders WO, AHL_VISITS_B VST
2522: WHERE MTL.inventory_item_id = p_service_item_id
2523: AND MTL.enabled_flag = G_YES_FLAG
2524: AND MTL.inventory_item_flag = G_NO_FLAG
2525: AND MTL.stock_enabled_flag = G_NO_FLAG
2531: AND VST.visit_id = WO.visit_id
2532: AND WO.workorder_id = p_workorder_id;
2533:
2534: CURSOR val_service_item_id_wo_csr(p_workorder_id IN NUMBER, p_service_item_id IN NUMBER) IS
2535: SELECT 'x' from ahl_workorders WO, AHL_ROUTES_B ARB
2536: WHERE WO.workorder_id = p_workorder_id
2537: AND WO.ROUTE_ID = ARB.ROUTE_ID (+)
2538: AND NVL(ARB.service_item_id, p_service_item_id) = p_service_item_id;
2539:
2537: AND WO.ROUTE_ID = ARB.ROUTE_ID (+)
2538: AND NVL(ARB.service_item_id, p_service_item_id) = p_service_item_id;
2539:
2540: CURSOR val_service_item_id_nll_csr(p_workorder_id IN NUMBER) IS
2541: SELECT 'x' from ahl_workorders WO, AHL_ROUTES_B ARB
2542: WHERE WO.workorder_id = p_workorder_id
2543: AND WO.ROUTE_ID = ARB.ROUTE_ID (+)
2544: AND ARB.service_item_id IS NULL;
2545:
2544: AND ARB.service_item_id IS NULL;
2545:
2546: CURSOR item_exists_in_wo_org_csr(p_workorder_id IN NUMBER) IS
2547: SELECT 'x'
2548: from ahl_workorders WO, AHL_ROUTES_B ARB, AHL_VISITS_B VST, ahl_mtl_items_ou_v mtl
2549: where wo.workorder_id = p_workorder_id and
2550: WO.ROUTE_ID = ARB.ROUTE_ID (+) and
2551: ARB.service_item_id = mtl.inventory_item_id and
2552: VST.visit_id = WO.visit_id and
2807: l_msg_count NUMBER;
2808: l_msg_data VARCHAR2(1000);
2809: l_shipment_IDs_Tbl AHL_OSP_SHIPMENT_PUB.Ship_ID_Tbl_Type;
2810: CURSOR val_all_wo_closed(p_osp_order_id IN NUMBER) IS
2811: --Modified by mpothuku on 27-Feb-06 to use ahl_workorders instead of ahl_workorders_osp_v
2812: --to fix the Perf Bug #4919164
2813: SELECT 'x' from ahl_osp_order_lines OL, ahl_workorders WO
2814: WHERE OL.osp_order_id = p_osp_order_id
2815: AND OL.workorder_id = WO.workorder_id
2809: l_shipment_IDs_Tbl AHL_OSP_SHIPMENT_PUB.Ship_ID_Tbl_Type;
2810: CURSOR val_all_wo_closed(p_osp_order_id IN NUMBER) IS
2811: --Modified by mpothuku on 27-Feb-06 to use ahl_workorders instead of ahl_workorders_osp_v
2812: --to fix the Perf Bug #4919164
2813: SELECT 'x' from ahl_osp_order_lines OL, ahl_workorders WO
2814: WHERE OL.osp_order_id = p_osp_order_id
2815: AND OL.workorder_id = WO.workorder_id
2816: AND OL.status_code IS NULL
2817: AND WO.status_code NOT IN(G_OSP_WO_CANCELLED,G_OSP_WO_CLOSED);
4402: --Modified by mpothuku on 27-Feb-06 to fix the Perf Bug #4919164
4403: /*
4404: SELECT organization_id, inventory_item_id, service_item_id
4405: INTO l_organization_id, l_inventory_item_id, l_service_item_id
4406: FROM ahl_workorders_osp_v
4407: WHERE workorder_id = p_x_osp_order_lines_tbl(i).workorder_id;
4408: */
4409: SELECT vst.organization_id, vts.inventory_item_id, arb.service_item_id
4410: INTO l_organization_id, l_inventory_item_id, l_service_item_id
4407: WHERE workorder_id = p_x_osp_order_lines_tbl(i).workorder_id;
4408: */
4409: SELECT vst.organization_id, vts.inventory_item_id, arb.service_item_id
4410: INTO l_organization_id, l_inventory_item_id, l_service_item_id
4411: FROM ahl_workorders wo,
4412: ahl_visits_b vst,
4413: ahl_visit_tasks_b vts,
4414: ahl_routes_b arb,
4415: inv_organization_info_v org
4764: item_instance_uom,
4765: service_item_id,
4766: service_item_description,
4767: service_item_uom
4768: FROM ahl_workorders_osp_v
4769: WHERE workorder_id = c_workorder_id;
4770: */
4771: SELECT vts.inventory_item_id,
4772: vst.organization_id,
4776: csii.unit_of_measure item_instance_uom,
4777: arb.service_item_id,
4778: mtls.description service_item_description,
4779: mtls.primary_uom_code service_item_uom
4780: FROM ahl_workorders wo,
4781: ahl_visits_b vst,
4782: ahl_visit_tasks_b vts,
4783: csi_item_instances csii,
4784: mtl_system_items_kfv mtls,
5427: csii.unit_of_measure item_instance_uom,
5428: arb.service_item_id,
5429: mtls.description service_item_description,
5430: mtls.primary_uom_code service_item_uom
5431: FROM ahl_workorders wo,
5432: ahl_visits_b vst,
5433: ahl_visit_tasks_b vts,
5434: csi_item_instances csii,
5435: mtl_system_items_kfv mtls,
6946: ospl.attribute13,
6947: ospl.attribute14,
6948: ospl.attribute15
6949: FROM ahl_osp_order_lines ospl,
6950: ahl_workorders wo,
6951: ahl_visits_b vst,
6952: ahl_visit_tasks_b vts,
6953: csi_item_instances csiwo,
6954: csi_item_instances csiex,
7349: CURSOR workorder_id_csr(p_job_number IN VARCHAR2) IS
7350: --Modified by mpothuku on 27-Feb-06 to fix the Perf Bug #4919164
7351: /*
7352: SELECT workorder_id
7353: FROM ahl_workorders_osp_v
7354: WHERE job_number = p_job_number;
7355: */
7356: SELECT workorder_id from ahl_workorders
7357: WHERE workorder_name = p_job_number;
7352: SELECT workorder_id
7353: FROM ahl_workorders_osp_v
7354: WHERE job_number = p_job_number;
7355: */
7356: SELECT workorder_id from ahl_workorders
7357: WHERE workorder_name = p_job_number;
7358:
7359: l_workorder_id NUMBER;
7360: CURSOR service_item_id_csr(p_service_item_number IN VARCHAR2, p_inventory_org_id NUMBER) IS
7843: CURSOR c_get_entl_for_wo(c_workorder_id NUMBER) IS
7844: SELECT ASWE.WARRANTY_ENTITLEMENT_ID, ASWE.OBJECT_VERSION_NUMBER
7845: FROM AHL_WARRANTY_ENTITLEMENTS ASWE
7846: WHERE ASWE.VISIT_TASK_ID = (SELECT wo.visit_task_id
7847: FROM ahl_workorders wo
7848: WHERE wo.workorder_id = c_workorder_id);
7849:
7850: BEGIN
7851: --------------------Start of API Body-----------------------------------