[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:
294: SELECT OL.OSP_ORDER_LINE_ID, OL.OSP_LINE_NUMBER, OL.SERVICE_ITEM_ID,
295: OL.SERVICE_ITEM_DESCRIPTION, WO.QUANTITY, OL.NEED_BY_DATE,
296: OL.SERVICE_ITEM_UOM_CODE, OL.PO_LINE_TYPE_ID,
297: BOM.ORGANIZATION_ID, BOM.LOCATION_ID
298: FROM AHL_OSP_ORDER_LINES OL, AHL_WORKORDERS_V WO, BOM_DEPARTMENTS BOM
299: WHERE OL.OSP_ORDER_ID = p_osp_order_id AND
300: WO.WORKORDER_ID = OL.WORKORDER_ID AND
301: BOM.DEPARTMENT_ID (+) = WO.DEPARTMENT_ID;
302: */
307: -- OL.SERVICE_ITEM_UOM_CODE, OL.PO_LINE_TYPE_ID,
308: ---- Changed by jaramana on May 26, 2005 to Fix bug 4393374
309: ---- BOM.ORGANIZATION_ID, BOM.LOCATION_ID
310: -- WO.ORGANIZATION_ID, BOM.LOCATION_ID
311: -- FROM AHL_OSP_ORDER_LINES OL, AHL_WORKORDERS_OSP_V WO, BOM_DEPARTMENTS BOM
312: -- WHERE OL.OSP_ORDER_ID = p_osp_order_id AND
313: -- WO.WORKORDER_ID = OL.WORKORDER_ID AND
314: -- BOM.DEPARTMENT_ID (+) = WO.DEPARTMENT_ID;
315: -- End ER 266135 Fix
320: OL.SERVICE_ITEM_DESCRIPTION, OL.QUANTITY, OL.NEED_BY_DATE,
321: OL.SERVICE_ITEM_UOM_CODE, OL.PO_LINE_TYPE_ID,
322: /**
323: OL.INVENTORY_ORG_ID, BOM.LOCATION_ID
324: FROM AHL_OSP_ORDER_LINES OL, AHL_WORKORDERS_OSP_V WO, BOM_DEPARTMENTS BOM
325: **/
326: OL.INVENTORY_ORG_ID, DECODE(OL.WORKORDER_ID, NULL, HAOU.LOCATION_ID, BOM.LOCATION_ID)
327: -- Added by mpothuku on 10-oct-2007 to fix bug 6431740
328: , WO.WIP_ENTITY_ID
328: , WO.WIP_ENTITY_ID
329: , WDJ.PROJECT_ID
330: , WDJ.TASK_ID
331: /*
332: FROM AHL_OSP_ORDER_LINES OL, AHL_WORKORDERS_OSP_V WO, BOM_DEPARTMENTS BOM, HR_ALL_ORGANIZATION_UNITS HAOU
333: */
334: -- Changes made by jaramana on December 19, 2005
335: -- to improve the performace of this SQL.
336: -- Removed reference to AHL_WORKORDERS_OSP_V and instead joined directly with
334: -- Changes made by jaramana on December 19, 2005
335: -- to improve the performace of this SQL.
336: -- Removed reference to AHL_WORKORDERS_OSP_V and instead joined directly with
337: -- WIP_DISCRETE_JOBS to get the work order department
338: FROM AHL_OSP_ORDER_LINES OL, AHL_WORKORDERS WO, BOM_DEPARTMENTS BOM, HR_ALL_ORGANIZATION_UNITS HAOU, WIP_DISCRETE_JOBS WDJ
339: WHERE OL.OSP_ORDER_ID = p_osp_order_id AND
340: WO.WORKORDER_ID (+) = OL.WORKORDER_ID AND
341: /**
342: BOM.DEPARTMENT_ID (+) = WO.DEPARTMENT_ID;
351: -- Added by jaramana on June 24, 2005 to get the updated Return to Org
352: -- Updated by jaramana on March 20, 2006 to get the Org Location for fixing Bug 5104282
353: CURSOR get_return_to_org_csr(p_osp_line_id IN NUMBER) IS
354: SELECT oola.ship_from_org_id, HAOU.LOCATION_ID
355: FROM oe_order_lines_all oola, ahl_osp_order_lines aool, HR_ALL_ORGANIZATION_UNITS HAOU
356: WHERE oola.line_id = aool.oe_return_line_id and
357: HAOU.ORGANIZATION_ID = oola.ship_from_org_id and
358: aool.osp_order_line_id = p_osp_line_id;
359:
626: -- Start of Comments --
627: -- Procedure name : Associate_OSP_With_PO
628: -- Type : Private
629: -- Function : Updates AHL_OSP_ORDERS_B.PO_HEADER_ID and
630: -- AHL_OSP_ORDER_LINES.PO_LINE_ID with PO_HEADER_ID and
631: -- PO_LINE_ID respectively for a single submitted OSP Order.
632: -- Does not give error if the OSP Order is already associated
633: -- or if there is no corresponding PO yet.
634: -- Pre-reqs :
822: -- Start of Comments --
823: -- Procedure name : Associate_All_OSP_POs
824: -- Type : Private
825: -- Function : Updates AHL_OSP_ORDERS_B.PO_HEADER_ID and
826: -- AHL_OSP_ORDER_LINES.PO_LINE_ID with PO_HEADER_ID and
827: -- PO_LINE_ID respectively for all submitted OSP Orders.
828: -- Pre-reqs :
829: -- Parameters :
830: --
1310: FROM PO_LINES_ALL PL, AHL_OSP_ORDERS_B OSP
1311: WHERE PL.PO_HEADER_ID = OSP.PO_HEADER_ID AND
1312: OSP.OSP_ORDER_ID = p_osp_order_id AND
1313: NVL(PL.CANCEL_FLAG, 'N') <> 'Y' AND
1314: PL.PO_LINE_ID NOT IN (SELECT PO_LINE_ID from AHL_OSP_ORDER_LINES
1315: WHERE OSP_ORDER_ID = p_osp_order_id);
1316: l_po_line_id NUMBER;
1317:
1318: BEGIN
1843: --Added by mpothuku on 12-oct-2007 as until the ER 5758813 is implemented, the visit task dates will not be propagated to projects.
1844: CURSOR get_vst_task_comp_date_csr (c_osp_line_id IN NUMBER) IS
1845: SELECT vtsk.end_date_time
1846: from ahl_visit_tasks_b vtsk,
1847: ahl_osp_order_lines ospl,
1848: ahl_workorders wo
1849: where ospl.osp_order_line_id = c_osp_line_id
1850: and ospl.workorder_id = wo.workorder_id
1851: and wo.visit_task_id = vtsk.visit_task_id;
2104: PO.VENDOR_SITE_ID = OSP.VENDOR_SITE_ID AND
2105: PO.INTERFACE_SOURCE_CODE = AHL_GLOBAL.AHL_APP_SHORT_NAME;
2106:
2107: CURSOR l_get_osp_lines_csr(p_osp_order_id IN NUMBER) IS
2108: SELECT OSP_ORDER_LINE_ID FROM AHL_OSP_ORDER_LINES
2109: WHERE PO_LINE_ID IS NULL
2110: AND OSP_ORDER_ID = p_osp_order_id;
2111:
2112: CURSOR l_get_po_line_csr(p_osp_order_line_id IN NUMBER,
2224:
2225: CURSOR l_get_cancelled_po_lines_csr IS
2226: SELECT PL.PO_LINE_ID, OL.OSP_ORDER_LINE_ID, PO.REFERENCE_NUM,
2227: OL.OBJECT_VERSION_NUMBER, OSP.OBJECT_VERSION_NUMBER
2228: FROM PO_LINES_ALL PL, PO_HEADERS_ALL PO, AHL_OSP_ORDER_LINES OL,
2229: AHL_OSP_ORDERS_B OSP
2230: WHERE PL.CANCEL_FLAG = 'Y' AND -- Canceled PO Line
2231: PL.PO_HEADER_ID = PO.PO_HEADER_ID AND
2232: PO.INTERFACE_SOURCE_CODE = AHL_GLOBAL.AHL_APP_SHORT_NAME AND -- AHL Created PO
2379:
2380: CURSOR l_get_deleted_po_lines_csr IS
2381: SELECT OL.OSP_ORDER_ID, OL.OSP_ORDER_LINE_ID,
2382: OL.OBJECT_VERSION_NUMBER, OSP.OBJECT_VERSION_NUMBER
2383: FROM AHL_OSP_ORDER_LINES OL, AHL_OSP_ORDERS_B OSP
2384: WHERE OL.PO_LINE_ID IS NOT NULL AND -- PO Created
2385: NVL(OL.STATUS_CODE, ' ') <> G_OL_PO_DELETED_STATUS AND -- Not yet updated
2386: OSP.OSP_ORDER_ID = OL.OSP_ORDER_ID AND
2387: -- Added by jaramana on April 7, 2008 for bug 6609988
2642: -- we need to change the OSP order to bring it to a consistent state.
2643: -- This procedure basically looks for OSP Orders for which the PO has been deleted
2644: -- and resets some values and corrects the status of the order as well as the lines
2645: -- so that the OSP Order can be resubmitted and a different PO can be created.
2646: -- This procedure does a direct update of the AHL_OSP_ORDERS_B and the AHL_OSP_ORDER_LINES
2647: -- tables and does not call the process_osp_order API to avoid unwanted validations
2648: ----------------------------------------
2649: PROCEDURE Handle_Deleted_PO_Headers(
2650: p_commit IN VARCHAR2,
2668: ospl.osp_order_line_id,
2669: ospl.object_version_number,
2670: ospl.status_code,
2671: ospl.po_line_id
2672: FROM ahl_osp_order_lines ospl
2673: WHERE ospl.osp_order_id = c_osp_order_id;
2674:
2675: L_DEBUG_KEY CONSTANT VARCHAR2(150) := G_LOG_PREFIX || '.Handle_Deleted_PO_Headers';
2676: l_temp_num NUMBER := 0;
2713: -- Physically delete this line (PO_DELETED or PO_CANCELLED)
2714: IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2715: FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, L_DEBUG_KEY, 'Deleting OSP Line with Id ' || l_osp_line_details_rec.osp_order_line_id);
2716: END IF;
2717: DELETE FROM ahl_osp_order_lines
2718: WHERE osp_order_line_id = l_osp_line_details_rec.osp_order_line_id;
2719: END IF;
2720: END LOOP;
2721: CLOSE get_osp_line_dtls_csr;
2780: -- we need to change the OSP order to bring it to a consistent state.
2781: -- This procedure basically looks for OSP Orders for which the SO has been deleted
2782: -- and resets some values of the order as well as the lines so that a new shipment
2783: -- can be created for the OSP Order if required.
2784: -- This procedure does a direct update of the AHL_OSP_ORDERS_B and the AHL_OSP_ORDER_LINES
2785: -- tables and does not call the process_osp_order API to avoid unwanted validations.
2786: ----------------------------------------
2787: PROCEDURE Handle_Deleted_Sales_Orders(
2788: p_commit IN VARCHAR2,
2804: SELECT ospl.osp_order_line_id,
2805: ospl.object_version_number,
2806: ospl.oe_ship_line_id,
2807: ospl.oe_return_line_id
2808: FROM ahl_osp_order_lines ospl
2809: WHERE ospl.osp_order_id = c_osp_order_id AND
2810: (ospl.oe_ship_line_id IS NOT NULL OR ospl.oe_return_line_id IS NOT NULL);
2811:
2812: L_DEBUG_KEY CONSTANT VARCHAR2(150) := G_LOG_PREFIX || '.Handle_Deleted_Sales_Orders';
2838: LOOP
2839: FETCH get_osp_line_dtls_csr into l_osp_line_details_rec;
2840: EXIT WHEN get_osp_line_dtls_csr%NOTFOUND;
2841: -- Reset the value of oe_ship_line_id and oe_return_line_id and increment OVN
2842: update ahl_osp_order_lines
2843: set oe_ship_line_id = null,
2844: oe_return_line_id = null,
2845: object_version_number = l_osp_line_details_rec.object_version_number + 1,
2846: last_update_date = TRUNC(sysdate),
2931: p_status_code => G_OSP_SUB_FAILED_STATUS);
2932: END Set_Submission_Failed;
2933:
2934: ----------------------------------------
2935: -- This Procedure updates AHL_OSP_ORDER_LINES.PO_LINE_ID
2936: ----------------------------------------
2937: PROCEDURE Set_PO_Line_ID(
2938: p_osp_order_line_id IN NUMBER,
2939: p_po_line_id IN NUMBER) IS
2982: ATTRIBUTE15,
2983: -- Begin Changes by jaramana on January 7, 2008 for the Requisition ER 6034236
2984: PO_REQ_LINE_ID
2985: -- End Changes by jaramana on January 7, 2008 for the Requisition ER 6034236
2986: FROM AHL_OSP_ORDER_LINES
2987: WHERE OSP_ORDER_LINE_ID = p_osp_order_line_id;
2988:
2989: l_osp_line_dtls_rec l_osp_line_dtls_csr%ROWTYPE;
2990: L_DEBUG_KEY CONSTANT VARCHAR2(150) := G_LOG_PREFIX || '.Set_PO_Line_ID';
3011: -- Update cursor variable's PO Line ID
3012: l_osp_line_dtls_rec.PO_LINE_ID := p_po_line_id;
3013:
3014: -- Call Table Handler
3015: AHL_OSP_ORDER_LINES_PKG.UPDATE_ROW(
3016: P_OSP_ORDER_LINE_ID => p_osp_order_line_id,
3017: P_OBJECT_VERSION_NUMBER => l_osp_line_dtls_rec.OBJECT_VERSION_NUMBER + 1, -- Updated
3018: P_OSP_ORDER_ID => l_osp_line_dtls_rec.OSP_ORDER_ID,
3019: P_OSP_LINE_NUMBER => l_osp_line_dtls_rec.OSP_LINE_NUMBER,
3220:
3221: FUNCTION Get_Item_Price(p_osp_line_id IN NUMBER) RETURN NUMBER IS
3222:
3223: CURSOR l_get_org_price_csr IS
3224: SELECT LIST_PRICE_PER_UNIT FROM MTL_SYSTEM_ITEMS_B MSIB, AHL_OSP_ORDER_LINES ospl
3225: WHERE MSIB.INVENTORY_ITEM_ID = ospl.service_item_id
3226: AND MSIB.ORGANIZATION_ID = ospl.inventory_org_id
3227: AND ospl.osp_order_line_id = p_osp_line_id;
3228: /*