[Home] [Help]
119: -- TO BE USED FOR DEBUGGING PURPOSE ONLY
120: PROCEDURE Call_PDOI_API(
121: p_batch_id IN NUMBER);
122:
123: -- This Procedure updates AHL_OSP_ORDERS_B with the Batch Id and Request Id
124: PROCEDURE Record_OSP_Submission(
125: p_osp_order_id IN NUMBER,
126: p_batch_id IN NUMBER,
127: p_request_id IN NUMBER,
131: PROCEDURE Associate_OSP_PO(
132: p_osp_order_id IN NUMBER,
133: x_po_header_id OUT NOCOPY NUMBER);
134:
135: -- This Procedure updates AHL_OSP_ORDERS_B's PO_HEADER_ID and sets STATUS_CODE to PO_CREATED
136: PROCEDURE Set_PO_Header_ID(
137: p_osp_order_id IN NUMBER,
138: p_po_header_id IN NUMBER);
139:
141: PROCEDURE Set_PO_Line_ID(
142: p_osp_order_line_id IN NUMBER,
143: p_po_line_id IN NUMBER);
144:
145: -- This Procedure updates AHL_OSP_ORDERS_B.STATUS_CODE to SUBMISSION_FAILED
146: PROCEDURE Set_Submission_Failed(
147: p_osp_order_id IN NUMBER);
148:
149: -- This Procedure handles cancelled PO Lines and is Part of PO Synchronization.
169: PROCEDURE Handle_Approved_POs(
170: p_commit IN VARCHAR2,
171: x_return_status OUT NOCOPY VARCHAR2);
172:
173: -- This Procedure updates a record of AHL_OSP_ORDERS_B using the table handler.
174: -- All updates to this table from this Package should go through this procedure only
175: PROCEDURE Update_OSP_Order(
176: p_osp_order_id IN NUMBER,
177: p_batch_id IN NUMBER := NULL,
277: L_DEBUG_KEY CONSTANT VARCHAR2(150) := G_LOG_PREFIX || '.Create_Purchase_Order';
278:
279: CURSOR l_osp_id_csr(p_osp_order_number IN NUMBER) IS
280: SELECT OSP_ORDER_ID
281: FROM AHL_OSP_ORDERS_B
282: WHERE OSP_ORDER_NUMBER = p_osp_order_number;
283:
284: CURSOR l_osp_dtls_csr(p_osp_order_id IN NUMBER) IS
285: -- VENDOR_CONTACT_ID added by jaramana on May 27, 2005
284: CURSOR l_osp_dtls_csr(p_osp_order_id IN NUMBER) IS
285: -- VENDOR_CONTACT_ID added by jaramana on May 27, 2005
286: SELECT VENDOR_ID, VENDOR_SITE_ID, PO_AGENT_ID, PO_BATCH_ID, PO_INTERFACE_HEADER_ID, VENDOR_CONTACT_ID
287: , STATUS_CODE -- Added by jaramana on January 7, 2008 for the Requisition ER 6034236
288: FROM AHL_OSP_ORDERS_B
289: WHERE OSP_ORDER_ID = p_osp_order_id;
290:
291: -- Begin ER 266135 Fix
292: /*
368: l_temp_ret_org_id NUMBER;
369: l_temp_ret_org_loc_id NUMBER;
370:
371: -- Added by jaramana on January 7, 2008 for the Requisition ER 6034236
372: l_curr_status AHL_OSP_ORDERS_B.STATUS_CODE%TYPE;
373:
374: BEGIN
375:
376: -- Standard start of API savepoint
625:
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.
672: x_msg_count OUT NOCOPY NUMBER,
673: x_msg_data OUT NOCOPY VARCHAR2) IS
674:
675: CURSOR l_validate_osp_csr(p_osp_order_id IN NUMBER) IS
676: SELECT PO_HEADER_ID FROM AHL_OSP_ORDERS_B
677: WHERE OSP_ORDER_ID = p_osp_order_id;
678: -- Don't throw error based on status
679: -- AND STATUS_CODE = G_OSP_SUBMITTED_STATUS;
680:
821:
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 :
857: x_msg_count OUT NOCOPY NUMBER,
858: x_msg_data OUT NOCOPY VARCHAR2) IS
859:
860: CURSOR l_get_osps_csr IS
861: SELECT OSP_ORDER_ID FROM AHL_OSP_ORDERS_B
862: WHERE STATUS_CODE = G_OSP_SUBMITTED_STATUS
863: AND PO_HEADER_ID IS NULL
864: AND PO_BATCH_ID IS NOT NULL
865: -- Added by jaramana on April 7, 2008 for bug 6609988
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
1343: PROCEDURE Validate_PO_Header(
1344: p_po_header_rec IN PO_Header_Rec_Type) IS
1345:
1346: CURSOR l_validate_osp_csr(p_osp_order_id IN NUMBER) IS
1347: SELECT 'x' FROM AHL_OSP_ORDERS_B
1348: WHERE OSP_ORDER_ID = p_osp_order_id
1349: -- The following condition commented out by jaramana on request of jeli
1350: -- on May 27, 2005 so that status update can be done later.
1351: -- AND STATUS_CODE = G_OSP_SUBMITTED_STATUS
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
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
2119: SELECT PO_REQUEST_ID FROM AHL_OSP_ORDERS_B
2120: WHERE OSP_ORDER_ID = p_osp_order_id;
2121:
2122: L_DEBUG_KEY CONSTANT VARCHAR2(150) := G_LOG_PREFIX || '.Associate_OSP_PO';
2123: l_po_header_id NUMBER;
2138:
2139: OPEN l_get_po_hdr_csr(p_osp_order_id);
2140: FETCH l_get_po_hdr_csr INTO x_po_header_id;
2141: IF (l_get_po_hdr_csr%FOUND) THEN
2142: -- Update AHL_OSP_ORDERS_B's PO_HEADER_ID
2143: --dbms_output.put_line('About to Update AHL_OSP_ORDERS_B.PO_HEADER_ID with ' || x_po_header_id);
2144: Set_PO_Header_Id(p_osp_order_id => p_osp_order_id,
2145: p_po_header_id => x_po_header_id);
2146:
2139: OPEN l_get_po_hdr_csr(p_osp_order_id);
2140: FETCH l_get_po_hdr_csr INTO x_po_header_id;
2141: IF (l_get_po_hdr_csr%FOUND) THEN
2142: -- Update AHL_OSP_ORDERS_B's PO_HEADER_ID
2143: --dbms_output.put_line('About to Update AHL_OSP_ORDERS_B.PO_HEADER_ID with ' || x_po_header_id);
2144: Set_PO_Header_Id(p_osp_order_id => p_osp_order_id,
2145: p_po_header_id => x_po_header_id);
2146:
2147: --dbms_output.put_line('Updated po_header_id. Logging Transaction...');
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
2233: PO.REFERENCE_NUM = OL.OSP_ORDER_ID AND -- Related to the OSP Order
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
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
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,
2655: osp.object_version_number,
2656: osp.po_header_id,
2657: osp.status_code,
2658: osp.order_type_code
2659: FROM ahl_osp_orders_b osp
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
2720: END LOOP;
2721: CLOSE get_osp_line_dtls_csr;
2722: -- Now for the OSP Order Header, reset PO_HEADER_ID, PO_BATCH_ID, PO_REQUEST_ID and PO_INTERFACE_HEADER_ID.
2723: -- set STATUS_CODE to "ENTERED" and increment OVN
2724: update ahl_osp_orders_b
2725: set po_header_id = null,
2726: po_batch_id = null,
2727: po_request_id = null,
2728: po_interface_header_id = null,
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,
2792: SELECT osp.osp_order_id,
2793: osp.object_version_number,
2794: osp.oe_header_id,
2795: osp.status_code
2796: FROM ahl_osp_orders_b osp
2797: WHERE osp.status_code <> 'CLOSED' AND
2798: osp.oe_header_id IS NOT NULL AND
2799: -- Added by jaramana on April 7, 2008 for bug 6609988
2800: osp.operating_unit_id = MO_GLOBAL.get_current_org_id() AND
2849: where osp_order_line_id = l_osp_line_details_rec.osp_order_line_id;
2850: END LOOP;
2851: CLOSE get_osp_line_dtls_csr;
2852: -- Now for the OSP Order Header, reset OE_HEADER_ID and increment OVN
2853: update ahl_osp_orders_b
2854: set OE_HEADER_ID = null,
2855: object_version_number = l_osp_details_rec.object_version_number + 1,
2856: last_update_date = TRUNC(sysdate),
2857: last_updated_by = fnd_global.user_id,
2894:
2895: END Handle_Deleted_Sales_Orders;
2896:
2897: ----------------------------------------
2898: -- This Procedure updates AHL_OSP_ORDERS_B with the Batch Id, Request Id and Interface Header Id
2899: ----------------------------------------
2900: PROCEDURE Record_OSP_Submission(
2901: p_osp_order_id IN NUMBER,
2902: p_batch_id IN NUMBER,
2909: p_intf_hdr_id => p_intf_hdr_id);
2910: END Record_OSP_Submission;
2911:
2912: ----------------------------------------
2913: -- This Procedure updates AHL_OSP_ORDERS_B's PO_HEADER_ID and sets STATUS_CODE to PO_CREATED
2914: ----------------------------------------
2915: PROCEDURE Set_PO_Header_ID(
2916: p_osp_order_id IN NUMBER,
2917: p_po_header_id IN NUMBER) IS
2921: p_status_code => G_OSP_PO_CREATED_STATUS);
2922: END Set_PO_Header_ID;
2923:
2924: ----------------------------------------
2925: -- This Procedure updates AHL_OSP_ORDERS_B.STATUS_CODE to SUBMISSION_FAILED
2926: ----------------------------------------
2927: PROCEDURE Set_Submission_Failed(
2928: p_osp_order_id IN NUMBER) IS
2929: BEGIN
3066: END IF;
3067: END Set_PO_Line_ID;
3068:
3069: ----------------------------------------
3070: -- This Procedure updates a record of AHL_OSP_ORDERS_B using the table handler
3071: -- All updates to this table from this Package should go through this procedure only
3072: ----------------------------------------
3073: PROCEDURE Update_OSP_Order(
3074: p_osp_order_id IN NUMBER,