[Home] [Help]
136: PROCEDURE Set_PO_Header_ID(
137: p_osp_order_id IN NUMBER,
138: p_po_header_id IN NUMBER);
139:
140: -- This Procedure updates AHL_OSP_ORDER_LINES.PO_LINE_ID
141: PROCEDURE Set_PO_Line_ID(
142: p_osp_order_line_id IN NUMBER,
143: p_po_line_id IN NUMBER);
144:
302: SELECT OL.OSP_ORDER_LINE_ID, OL.OSP_LINE_NUMBER, OL.SERVICE_ITEM_ID,
303: OL.SERVICE_ITEM_DESCRIPTION, WO.QUANTITY, OL.NEED_BY_DATE,
304: OL.SERVICE_ITEM_UOM_CODE, OL.PO_LINE_TYPE_ID,
305: BOM.ORGANIZATION_ID, BOM.LOCATION_ID
306: FROM AHL_OSP_ORDER_LINES OL, AHL_WORKORDERS_V WO, BOM_DEPARTMENTS BOM
307: WHERE OL.OSP_ORDER_ID = p_osp_order_id AND
308: WO.WORKORDER_ID = OL.WORKORDER_ID AND
309: BOM.DEPARTMENT_ID (+) = WO.DEPARTMENT_ID;
310: */
315: -- OL.SERVICE_ITEM_UOM_CODE, OL.PO_LINE_TYPE_ID,
316: ---- Changed by jaramana on May 26, 2005 to Fix bug 4393374
317: ---- BOM.ORGANIZATION_ID, BOM.LOCATION_ID
318: -- WO.ORGANIZATION_ID, BOM.LOCATION_ID
319: -- FROM AHL_OSP_ORDER_LINES OL, AHL_WORKORDERS_OSP_V WO, BOM_DEPARTMENTS BOM
320: -- WHERE OL.OSP_ORDER_ID = p_osp_order_id AND
321: -- WO.WORKORDER_ID = OL.WORKORDER_ID AND
322: -- BOM.DEPARTMENT_ID (+) = WO.DEPARTMENT_ID;
323: -- End ER 266135 Fix
328: OL.SERVICE_ITEM_DESCRIPTION, OL.QUANTITY, OL.NEED_BY_DATE,
329: OL.SERVICE_ITEM_UOM_CODE, OL.PO_LINE_TYPE_ID,
330: /**
331: OL.INVENTORY_ORG_ID, BOM.LOCATION_ID
332: FROM AHL_OSP_ORDER_LINES OL, AHL_WORKORDERS_OSP_V WO, BOM_DEPARTMENTS BOM
333: **/
334: OL.INVENTORY_ORG_ID, DECODE(OL.WORKORDER_ID, NULL, HAOU.LOCATION_ID, BOM.LOCATION_ID)
335: -- Added by mpothuku on 10-oct-2007 to fix bug 6431740
336: , WO.WIP_ENTITY_ID
336: , WO.WIP_ENTITY_ID
337: , WDJ.PROJECT_ID
338: , WDJ.TASK_ID
339: /*
340: FROM AHL_OSP_ORDER_LINES OL, AHL_WORKORDERS_OSP_V WO, BOM_DEPARTMENTS BOM, HR_ALL_ORGANIZATION_UNITS HAOU
341: */
342: -- Changes made by jaramana on December 19, 2005
343: -- to improve the performace of this SQL.
344: -- Removed reference to AHL_WORKORDERS_OSP_V and instead joined directly with
342: -- Changes made by jaramana on December 19, 2005
343: -- to improve the performace of this SQL.
344: -- Removed reference to AHL_WORKORDERS_OSP_V and instead joined directly with
345: -- WIP_DISCRETE_JOBS to get the work order department
346: FROM AHL_OSP_ORDER_LINES OL, AHL_WORKORDERS WO, BOM_DEPARTMENTS BOM, HR_ALL_ORGANIZATION_UNITS HAOU, WIP_DISCRETE_JOBS WDJ
347: WHERE OL.OSP_ORDER_ID = p_osp_order_id AND
348: WO.WORKORDER_ID (+) = OL.WORKORDER_ID AND
349: /**
350: BOM.DEPARTMENT_ID (+) = WO.DEPARTMENT_ID;
359: -- Added by jaramana on June 24, 2005 to get the updated Return to Org
360: -- Updated by jaramana on March 20, 2006 to get the Org Location for fixing Bug 5104282
361: CURSOR get_return_to_org_csr(p_osp_line_id IN NUMBER) IS
362: SELECT oola.ship_from_org_id, HAOU.LOCATION_ID
363: FROM oe_order_lines_all oola, ahl_osp_order_lines aool, HR_ALL_ORGANIZATION_UNITS HAOU
364: WHERE oola.line_id = aool.oe_return_line_id and
365: HAOU.ORGANIZATION_ID = oola.ship_from_org_id and
366: aool.osp_order_line_id = p_osp_line_id;
367:
634: -- Start of Comments --
635: -- Procedure name : Associate_OSP_With_PO
636: -- Type : Private
637: -- Function : Updates AHL_OSP_ORDERS_B.PO_HEADER_ID and
638: -- AHL_OSP_ORDER_LINES.PO_LINE_ID with PO_HEADER_ID and
639: -- PO_LINE_ID respectively for a single submitted OSP Order.
640: -- Does not give error if the OSP Order is already associated
641: -- or if there is no corresponding PO yet.
642: -- Pre-reqs :
830: -- Start of Comments --
831: -- Procedure name : Associate_All_OSP_POs
832: -- Type : Private
833: -- Function : Updates AHL_OSP_ORDERS_B.PO_HEADER_ID and
834: -- AHL_OSP_ORDER_LINES.PO_LINE_ID with PO_HEADER_ID and
835: -- PO_LINE_ID respectively for all submitted OSP Orders.
836: -- Pre-reqs :
837: -- Parameters :
838: --
1318: FROM PO_LINES_ALL PL, AHL_OSP_ORDERS_B OSP
1319: WHERE PL.PO_HEADER_ID = OSP.PO_HEADER_ID AND
1320: OSP.OSP_ORDER_ID = p_osp_order_id AND
1321: NVL(PL.CANCEL_FLAG, 'N') <> 'Y' AND
1322: PL.PO_LINE_ID NOT IN (SELECT PO_LINE_ID from AHL_OSP_ORDER_LINES
1323: WHERE OSP_ORDER_ID = p_osp_order_id);
1324: l_po_line_id NUMBER;
1325:
1326: BEGIN
1857: --Added by mpothuku on 12-oct-2007 as until the ER 5758813 is implemented, the visit task dates will not be propagated to projects.
1858: CURSOR get_vst_task_comp_date_csr (c_osp_line_id IN NUMBER) IS
1859: SELECT vtsk.end_date_time
1860: from ahl_visit_tasks_b vtsk,
1861: ahl_osp_order_lines ospl,
1862: ahl_workorders wo
1863: where ospl.osp_order_line_id = c_osp_line_id
1864: and ospl.workorder_id = wo.workorder_id
1865: and wo.visit_task_id = vtsk.visit_task_id;
2137: PO.VENDOR_SITE_ID = OSP.VENDOR_SITE_ID AND
2138: PO.INTERFACE_SOURCE_CODE = AHL_GLOBAL.AHL_APP_SHORT_NAME;
2139:
2140: CURSOR l_get_osp_lines_csr(p_osp_order_id IN NUMBER) IS
2141: SELECT OSP_ORDER_LINE_ID FROM AHL_OSP_ORDER_LINES
2142: WHERE PO_LINE_ID IS NULL
2143: AND OSP_ORDER_ID = p_osp_order_id;
2144:
2145: CURSOR l_get_po_line_csr(p_osp_order_line_id IN NUMBER,
2257:
2258: CURSOR l_get_cancelled_po_lines_csr IS
2259: SELECT PL.PO_LINE_ID, OL.OSP_ORDER_LINE_ID, PO.REFERENCE_NUM,
2260: OL.OBJECT_VERSION_NUMBER, OSP.OBJECT_VERSION_NUMBER
2261: FROM PO_LINES_ALL PL, PO_HEADERS_ALL PO, AHL_OSP_ORDER_LINES OL,
2262: AHL_OSP_ORDERS_B OSP
2263: WHERE PL.CANCEL_FLAG = 'Y' AND -- Canceled PO Line
2264: PL.PO_HEADER_ID = PO.PO_HEADER_ID AND
2265: PO.INTERFACE_SOURCE_CODE = AHL_GLOBAL.AHL_APP_SHORT_NAME AND -- AHL Created PO
2412:
2413: CURSOR l_get_deleted_po_lines_csr IS
2414: SELECT OL.OSP_ORDER_ID, OL.OSP_ORDER_LINE_ID,
2415: OL.OBJECT_VERSION_NUMBER, OSP.OBJECT_VERSION_NUMBER
2416: FROM AHL_OSP_ORDER_LINES OL, AHL_OSP_ORDERS_B OSP
2417: WHERE OL.PO_LINE_ID IS NOT NULL AND -- PO Created
2418: NVL(OL.STATUS_CODE, ' ') <> G_OL_PO_DELETED_STATUS AND -- Not yet updated
2419: OSP.OSP_ORDER_ID = OL.OSP_ORDER_ID AND
2420: -- Added by jaramana on April 7, 2008 for bug 6609988
2675: -- we need to change the OSP order to bring it to a consistent state.
2676: -- This procedure basically looks for OSP Orders for which the PO has been deleted
2677: -- and resets some values and corrects the status of the order as well as the lines
2678: -- so that the OSP Order can be resubmitted and a different PO can be created.
2679: -- This procedure does a direct update of the AHL_OSP_ORDERS_B and the AHL_OSP_ORDER_LINES
2680: -- tables and does not call the process_osp_order API to avoid unwanted validations
2681: ----------------------------------------
2682: PROCEDURE Handle_Deleted_PO_Headers(
2683: p_commit IN VARCHAR2,
2701: ospl.osp_order_line_id,
2702: ospl.object_version_number,
2703: ospl.status_code,
2704: ospl.po_line_id
2705: FROM ahl_osp_order_lines ospl
2706: WHERE ospl.osp_order_id = c_osp_order_id;
2707:
2708: L_DEBUG_KEY CONSTANT VARCHAR2(150) := G_LOG_PREFIX || '.Handle_Deleted_PO_Headers';
2709: l_temp_num NUMBER := 0;
2746: -- Physically delete this line (PO_DELETED or PO_CANCELLED)
2747: IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2748: FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, L_DEBUG_KEY, 'Deleting OSP Line with Id ' || l_osp_line_details_rec.osp_order_line_id);
2749: END IF;
2750: DELETE FROM ahl_osp_order_lines
2751: WHERE osp_order_line_id = l_osp_line_details_rec.osp_order_line_id;
2752: END IF;
2753: END LOOP;
2754: CLOSE get_osp_line_dtls_csr;
2813: -- we need to change the OSP order to bring it to a consistent state.
2814: -- This procedure basically looks for OSP Orders for which the SO has been deleted
2815: -- and resets some values of the order as well as the lines so that a new shipment
2816: -- can be created for the OSP Order if required.
2817: -- This procedure does a direct update of the AHL_OSP_ORDERS_B and the AHL_OSP_ORDER_LINES
2818: -- tables and does not call the process_osp_order API to avoid unwanted validations.
2819: ----------------------------------------
2820: PROCEDURE Handle_Deleted_Sales_Orders(
2821: p_commit IN VARCHAR2,
2837: SELECT ospl.osp_order_line_id,
2838: ospl.object_version_number,
2839: ospl.oe_ship_line_id,
2840: ospl.oe_return_line_id
2841: FROM ahl_osp_order_lines ospl
2842: WHERE ospl.osp_order_id = c_osp_order_id AND
2843: (ospl.oe_ship_line_id IS NOT NULL OR ospl.oe_return_line_id IS NOT NULL);
2844:
2845: L_DEBUG_KEY CONSTANT VARCHAR2(150) := G_LOG_PREFIX || '.Handle_Deleted_Sales_Orders';
2871: LOOP
2872: FETCH get_osp_line_dtls_csr into l_osp_line_details_rec;
2873: EXIT WHEN get_osp_line_dtls_csr%NOTFOUND;
2874: -- Reset the value of oe_ship_line_id and oe_return_line_id and increment OVN
2875: update ahl_osp_order_lines
2876: set oe_ship_line_id = null,
2877: oe_return_line_id = null,
2878: object_version_number = l_osp_line_details_rec.object_version_number + 1,
2879: last_update_date = TRUNC(sysdate),
2964: p_status_code => G_OSP_SUB_FAILED_STATUS);
2965: END Set_Submission_Failed;
2966:
2967: ----------------------------------------
2968: -- This Procedure updates AHL_OSP_ORDER_LINES.PO_LINE_ID
2969: ----------------------------------------
2970: PROCEDURE Set_PO_Line_ID(
2971: p_osp_order_line_id IN NUMBER,
2972: p_po_line_id IN NUMBER) IS
3015: ATTRIBUTE15,
3016: -- Begin Changes by jaramana on January 7, 2008 for the Requisition ER 6034236
3017: PO_REQ_LINE_ID
3018: -- End Changes by jaramana on January 7, 2008 for the Requisition ER 6034236
3019: FROM AHL_OSP_ORDER_LINES
3020: WHERE OSP_ORDER_LINE_ID = p_osp_order_line_id;
3021:
3022: l_osp_line_dtls_rec l_osp_line_dtls_csr%ROWTYPE;
3023: L_DEBUG_KEY CONSTANT VARCHAR2(150) := G_LOG_PREFIX || '.Set_PO_Line_ID';
3044: -- Update cursor variable's PO Line ID
3045: l_osp_line_dtls_rec.PO_LINE_ID := p_po_line_id;
3046:
3047: -- Call Table Handler
3048: AHL_OSP_ORDER_LINES_PKG.UPDATE_ROW(
3049: P_OSP_ORDER_LINE_ID => p_osp_order_line_id,
3050: P_OBJECT_VERSION_NUMBER => l_osp_line_dtls_rec.OBJECT_VERSION_NUMBER + 1, -- Updated
3051: P_OSP_ORDER_ID => l_osp_line_dtls_rec.OSP_ORDER_ID,
3052: P_OSP_LINE_NUMBER => l_osp_line_dtls_rec.OSP_LINE_NUMBER,
3253:
3254: FUNCTION Get_Item_Price(p_osp_line_id IN NUMBER) RETURN NUMBER IS
3255:
3256: CURSOR l_get_org_price_csr IS
3257: SELECT LIST_PRICE_PER_UNIT FROM MTL_SYSTEM_ITEMS_B MSIB, AHL_OSP_ORDER_LINES ospl
3258: WHERE MSIB.INVENTORY_ITEM_ID = ospl.service_item_id
3259: AND MSIB.ORGANIZATION_ID = ospl.inventory_org_id
3260: AND ospl.osp_order_line_id = p_osp_line_id;
3261: /*