[Home] [Help]
1306: ----------------------------------------
1307: FUNCTION Has_New_PO_Line(p_osp_order_id IN NUMBER) RETURN VARCHAR2 IS
1308: CURSOR l_get_new_po_line_csr(p_osp_order_id IN NUMBER) IS
1309: SELECT PL.PO_LINE_ID
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
2110: AND OSP_ORDER_ID = p_osp_order_id;
2111:
2112: CURSOR l_get_po_line_csr(p_osp_order_line_id IN NUMBER,
2113: p_po_header_id IN NUMBER) IS
2114: SELECT PO_LINE_ID FROM PO_LINES_ALL
2115: WHERE LINE_REFERENCE_NUM = p_osp_order_line_id AND
2116: PO_HEADER_ID = p_po_header_id;
2117:
2118: CURSOR l_get_request_id_csr(p_osp_order_id IN NUMBER) IS
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
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
2388: OSP.OPERATING_UNIT_ID = MO_GLOBAL.get_current_org_id() AND
2389: NOT EXISTS (SELECT PO_LINE_ID FROM PO_LINES_ALL WHERE PO_LINE_ID = OL.PO_LINE_ID) -- PO Line Deleted
2390: ORDER BY OL.OSP_ORDER_ID; -- One OSP Order at a time
2391:
2392: L_DEBUG_KEY CONSTANT VARCHAR2(150) := G_LOG_PREFIX || '.Handle_Deleted_PO_Lines';
2393: l_osp_order_id NUMBER := -1;