[Home] [Help]
1262: FUNCTION Is_PO_Closed(p_po_header_id IN NUMBER) RETURN VARCHAR2 IS
1263:
1264: --Modified by mpothuku on 16-Nov-06 for fixing the Bug 5673483
1265: CURSOR l_get_po_cstatus_csr(p_po_header_id IN NUMBER) IS
1266: SELECT NVL(CLOSED_CODE, G_PO_OPEN), NVL(CANCEL_FLAG, 'N') FROM PO_HEADERS_ALL
1267: WHERE PO_HEADER_ID = p_po_header_id;
1268:
1269: L_DEBUG_KEY CONSTANT VARCHAR2(150) := G_LOG_PREFIX || '.Is_PO_Closed';
1270: l_closed_status VARCHAR2(30);
2089: x_po_header_id OUT NOCOPY NUMBER) IS
2090:
2091: /*
2092: CURSOR l_get_po_hdr_csr(p_osp_order_id IN NUMBER) IS
2093: SELECT PO_HEADER_ID FROM PO_HEADERS_ALL
2094: WHERE REFERENCE_NUM = p_osp_order_id AND
2095: INTERFACE_SOURCE_CODE = AHL_GLOBAL.AHL_APP_SHORT_NAME;
2096: */
2097: -- Changes made by jaramana on December 19, 2005
2096: */
2097: -- Changes made by jaramana on December 19, 2005
2098: -- to improve the performace of this SQL by removing the Full Table Access
2099: CURSOR l_get_po_hdr_csr(p_osp_order_id IN NUMBER) IS
2100: SELECT PO.PO_HEADER_ID FROM PO_HEADERS_ALL PO, AHL_OSP_ORDERS_B OSP
2101: WHERE PO.REFERENCE_NUM = p_osp_order_id AND
2102: OSP.OSP_ORDER_ID = p_osp_order_id AND
2103: PO.VENDOR_ID = OSP.VENDOR_ID AND
2104: PO.VENDOR_SITE_ID = OSP.VENDOR_SITE_ID AND
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
2527: x_return_status OUT NOCOPY VARCHAR2) IS
2528:
2529: CURSOR l_get_approved_POs_csr IS
2530: SELECT OSP.OSP_ORDER_ID, OSP.PO_HEADER_ID, OSP.OE_HEADER_ID
2531: FROM AHL_OSP_ORDERS_B OSP, PO_HEADERS_ALL PO
2532: WHERE OSP.STATUS_CODE = G_OSP_PO_CREATED_STATUS AND -- PO Created
2533: OSP.PO_HEADER_ID = PO.PO_HEADER_ID AND -- Join
2534: -- Added by jaramana on April 7, 2008 for bug 6609988
2535: OSP.OPERATING_UNIT_ID = MO_GLOBAL.get_current_org_id() AND
2660: WHERE osp.status_code = G_OSP_PO_CREATED_STATUS AND
2661: osp.order_type_code in ('SERVICE', 'EXCHANGE') AND
2662: -- Added by jaramana on April 7, 2008 for bug 6609988
2663: osp.operating_unit_id = MO_GLOBAL.get_current_org_id() AND
2664: NOT EXISTS (SELECT 1 FROM po_headers_all where po_header_id = osp.po_header_id);
2665:
2666: CURSOR get_osp_line_dtls_csr(c_osp_order_id IN NUMBER) IS
2667: SELECT ospl.osp_order_id,
2668: ospl.osp_order_line_id,