[Home] [Help]
1270: FUNCTION Is_PO_Closed(p_po_header_id IN NUMBER) RETURN VARCHAR2 IS
1271:
1272: --Modified by mpothuku on 16-Nov-06 for fixing the Bug 5673483
1273: CURSOR l_get_po_cstatus_csr(p_po_header_id IN NUMBER) IS
1274: SELECT NVL(CLOSED_CODE, G_PO_OPEN), NVL(CANCEL_FLAG, 'N') FROM PO_HEADERS_ALL
1275: WHERE PO_HEADER_ID = p_po_header_id;
1276:
1277: L_DEBUG_KEY CONSTANT VARCHAR2(150) := G_LOG_PREFIX || '.Is_PO_Closed';
1278: l_closed_status VARCHAR2(30);
2122: x_po_header_id OUT NOCOPY NUMBER) IS
2123:
2124: /*
2125: CURSOR l_get_po_hdr_csr(p_osp_order_id IN NUMBER) IS
2126: SELECT PO_HEADER_ID FROM PO_HEADERS_ALL
2127: WHERE REFERENCE_NUM = p_osp_order_id AND
2128: INTERFACE_SOURCE_CODE = AHL_GLOBAL.AHL_APP_SHORT_NAME;
2129: */
2130: -- Changes made by jaramana on December 19, 2005
2129: */
2130: -- Changes made by jaramana on December 19, 2005
2131: -- to improve the performace of this SQL by removing the Full Table Access
2132: CURSOR l_get_po_hdr_csr(p_osp_order_id IN NUMBER) IS
2133: SELECT PO.PO_HEADER_ID FROM PO_HEADERS_ALL PO, AHL_OSP_ORDERS_B OSP
2134: WHERE PO.REFERENCE_NUM = p_osp_order_id AND
2135: OSP.OSP_ORDER_ID = p_osp_order_id AND
2136: PO.VENDOR_ID = OSP.VENDOR_ID AND
2137: PO.VENDOR_SITE_ID = OSP.VENDOR_SITE_ID AND
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
2560: x_return_status OUT NOCOPY VARCHAR2) IS
2561:
2562: CURSOR l_get_approved_POs_csr IS
2563: SELECT OSP.OSP_ORDER_ID, OSP.PO_HEADER_ID, OSP.OE_HEADER_ID
2564: FROM AHL_OSP_ORDERS_B OSP, PO_HEADERS_ALL PO
2565: WHERE OSP.STATUS_CODE = G_OSP_PO_CREATED_STATUS AND -- PO Created
2566: OSP.PO_HEADER_ID = PO.PO_HEADER_ID AND -- Join
2567: -- Added by jaramana on April 7, 2008 for bug 6609988
2568: OSP.OPERATING_UNIT_ID = MO_GLOBAL.get_current_org_id() AND
2693: WHERE osp.status_code = G_OSP_PO_CREATED_STATUS AND
2694: osp.order_type_code in ('SERVICE', 'EXCHANGE') AND
2695: -- Added by jaramana on April 7, 2008 for bug 6609988
2696: osp.operating_unit_id = MO_GLOBAL.get_current_org_id() AND
2697: NOT EXISTS (SELECT 1 FROM po_headers_all where po_header_id = osp.po_header_id);
2698:
2699: CURSOR get_osp_line_dtls_csr(c_osp_order_id IN NUMBER) IS
2700: SELECT ospl.osp_order_id,
2701: ospl.osp_order_line_id,