[Home] [Help]
1314: ----------------------------------------
1315: FUNCTION Has_New_PO_Line(p_osp_order_id IN NUMBER) RETURN VARCHAR2 IS
1316: CURSOR l_get_new_po_line_csr(p_osp_order_id IN NUMBER) IS
1317: SELECT PL.PO_LINE_ID
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
2143: AND OSP_ORDER_ID = p_osp_order_id;
2144:
2145: CURSOR l_get_po_line_csr(p_osp_order_line_id IN NUMBER,
2146: p_po_header_id IN NUMBER) IS
2147: SELECT PO_LINE_ID FROM PO_LINES_ALL
2148: WHERE LINE_REFERENCE_NUM = p_osp_order_line_id AND
2149: PO_HEADER_ID = p_po_header_id;
2150:
2151: CURSOR l_get_request_id_csr(p_osp_order_id IN NUMBER) IS
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
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
2421: OSP.OPERATING_UNIT_ID = MO_GLOBAL.get_current_org_id() AND
2422: NOT EXISTS (SELECT PO_LINE_ID FROM PO_LINES_ALL WHERE PO_LINE_ID = OL.PO_LINE_ID) -- PO Line Deleted
2423: ORDER BY OL.OSP_ORDER_ID; -- One OSP Order at a time
2424:
2425: L_DEBUG_KEY CONSTANT VARCHAR2(150) := G_LOG_PREFIX || '.Handle_Deleted_PO_Lines';
2426: l_osp_order_id NUMBER := -1;