DBA Data[Home] [Help]

PACKAGE BODY: APPS.AHL_OSP_PO_PVT

Source


1 PACKAGE BODY AHL_OSP_PO_PVT AS
2 /* $Header: AHLVOPPB.pls 120.16.12020000.4 2013/04/04 20:11:50 jrotich ship $ */
3 
4 -----------------------
5 -- Declare Constants --
6 -----------------------
7 G_PKG_NAME          CONSTANT VARCHAR2(30) := 'AHL_OSP_PO_PVT';
8 
9 G_LOG_PREFIX        CONSTANT VARCHAR2(100) := 'ahl.plsql.AHL_OSP_PO_PVT';
10 
11 G_JSP_MODULE        CONSTANT VARCHAR2(30) := 'JSP';
12 
13 G_PO_APP_CODE       CONSTANT VARCHAR2(2)  := 'PO';
14 G_PDOI_CODE         CONSTANT VARCHAR2(30) := 'POXPOPDOI';
15 G_AHL_OSP_PREFIX    CONSTANT VARCHAR2(30) := 'AHL OSP Order ';
16 G_PROCESS_CODE      CONSTANT VARCHAR2(30) := 'PENDING';
17 G_ACTION_CODE       CONSTANT VARCHAR2(30) := 'ORIGINAL';
18 G_DOC_TYPE_CODE     CONSTANT VARCHAR2(30) := 'STANDARD';
19 G_INCOMPLETE_STATUS CONSTANT VARCHAR2(30) := 'INCOMPLETE';
20 
21 G_NO_FLAG           CONSTANT VARCHAR2(1)  := 'N';
22 G_YES_FLAG          CONSTANT VARCHAR2(1)  := 'Y';
23 
24 -- PO Closed codes
25 G_PO_CLOSED         CONSTANT VARCHAR2(30) := 'CLOSED';
26 G_PO_FINALLY_CLOSED CONSTANT VARCHAR2(30) := 'FINALLY CLOSED';
27 G_PO_OPEN           CONSTANT VARCHAR2(30) := 'OPEN';
28 
29 -- Default Values for One-time Items
30 -- Changed default price to zero: jaramana on June 22, 2005
31 --G_DEFAULT_PRICE     CONSTANT NUMBER       := 0.01;
32 G_DEFAULT_PRICE     CONSTANT NUMBER       := 0;
33 G_DEFAULT_CATEGORY  CONSTANT VARCHAR2(30) := 'MISC.MISC';
34 
35 -- OSP Order Statuses
36 G_OSP_ENTERED_STATUS    CONSTANT VARCHAR2(30) := 'ENTERED';
37 G_OSP_SUBMITTED_STATUS  CONSTANT VARCHAR2(30) := 'SUBMITTED';
38 G_OSP_SUB_FAILED_STATUS CONSTANT VARCHAR2(30) := 'SUBMISSION_FAILED';
39 G_OSP_PO_CREATED_STATUS CONSTANT VARCHAR2(30) := 'PO_CREATED';
40 -- Added by jaramana on January 7, 2008 for the Requisition ER 6034236
41 G_OSP_REQ_SUB_FAILED_STATUS CONSTANT VARCHAR2(30) := 'REQ_SUBMISSION_FAILED';
42 
43 -- OSP Order Line Statuses
44 G_OL_PO_CANCELLED_STATUS CONSTANT VARCHAR2(30) := 'PO_CANCELLED';
45 G_OL_PO_DELETED_STATUS   CONSTANT VARCHAR2(30) := 'PO_DELETED';
46 
47 -- Log Constants: Transaction Types
48 G_TXN_TYPE_PO_CREATION  CONSTANT VARCHAR2(30) := 'PO Creation';
49 G_TXN_TYPE_PO_SYNCH     CONSTANT VARCHAR2(30) := 'PO Synchronization';
50 G_TXN_TYPE_PO_UPDATE    CONSTANT VARCHAR2(30) := 'PO Update';
51 
52 -- Log Constants: Document Types
53 G_DOC_TYPE_OSP          CONSTANT VARCHAR2(30) := 'OSP';
54 G_DOC_TYPE_PO           CONSTANT VARCHAR2(30) := 'PO';
55 
56 -- PO Line Types
57 G_PO_LINE_TYPE_QUANTITY CONSTANT VARCHAR2(30) := 'QUANTITY';
58 -------------------------------------------------
59 -- Declare Locally used Record and Table Types --
60 -------------------------------------------------
61 
62 TYPE PO_Header_Rec_Type IS RECORD (
63         OSP_ORDER_ID            NUMBER,
64         VENDOR_ID               NUMBER,
65         VENDOR_SITE_ID          NUMBER,
66         BUYER_ID                NUMBER,
67         VENDOR_CONTACT_ID       NUMBER -- Added by jaramana on May 27, 2005 to support Inventory Service Order
68         );
69 
70 TYPE PO_Line_Rec_Type IS RECORD (
71         OSP_LINE_ID             NUMBER,
72         LINE_NUMBER             NUMBER,
73         PO_LINE_TYPE_ID         NUMBER,
74         ITEM_ID                 NUMBER,
75         ITEM_DESCRIPTION        VARCHAR2(240),
76         QUANTITY                NUMBER,
77         UOM_CODE                VARCHAR2(3),
78         NEED_BY_DATE            DATE,
79         SHIP_TO_ORG_ID          NUMBER,
80         SHIP_TO_LOC_ID          NUMBER
81         -- Added by mpothuku on 10-oct-2007 to fix bug 6431740
82         , WIP_ENTITY_ID         NUMBER
83         , PROJECT_ID            NUMBER
84         , TASK_ID               NUMBER
85         );
86 
87 TYPE PO_Line_Tbl_Type IS TABLE OF PO_Line_Rec_Type INDEX BY BINARY_INTEGER;
88 
89 ------------------------------
90 -- Declare Local Procedures --
91 ------------------------------
92 
93   -- Validate OSP Order for PO Header Creation
94   PROCEDURE Validate_PO_Header(
95      p_po_header_rec IN PO_Header_Rec_Type);
96 
97   -- Validate PO Lines
98   PROCEDURE Validate_PO_Lines(
99      p_po_line_tbl IN PO_Line_Tbl_Type,
100      p_osp_order_id IN NUMBER);
101 
102   -- Insert a record into the PO_HEADERS_INTERFACE table
103   PROCEDURE Insert_PO_Header(
104      p_po_header_rec  IN  PO_Header_Rec_Type,
105      x_intf_header_id OUT NOCOPY NUMBER,
106      x_batch_id       OUT NOCOPY NUMBER);
107 
108   -- Inserts records into the PO_LINES_INTERFACE table
109   PROCEDURE Insert_PO_Lines(
110      p_po_line_tbl    IN PO_Line_Tbl_Type,
111      p_intf_header_id IN NUMBER);
112 
113   -- Calls the Concurrent Program to Create Purchase Order
114   PROCEDURE Call_PDOI_Program(
115      p_batch_id   IN  NUMBER,
116      x_request_id OUT NOCOPY NUMBER);
117 
118   -- Calls the PDOI API directly to Create Purchase Order
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,
128      p_intf_hdr_id  IN NUMBER);
129 
130   -- This Local Procedure updates OSP Tables with PO Information for one OSP Order
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 
140   -- This Procedure updates AHL_OSP_ORDER_LINES.PO_LINE_ID
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.
150   -- This procedure commits its work if p_commit is set to true and
151   -- if there were no errors during the execution of this procedure.
152   -- It does not check the message list for performing the commit action
153   PROCEDURE Handle_Cancelled_PO_Lines(
154      p_commit         IN VARCHAR2,
155      x_return_status  OUT NOCOPY VARCHAR2);
156 
157   -- This Procedure handles deleted PO Lines and is Part of PO Synchronization.
158   -- This procedure commits its work if p_commit is set to true and
159   -- if there were no errors during the execution of this procedure.
160   -- It does not check the message list for performing the commit action
161   PROCEDURE Handle_Deleted_PO_Lines(
162      p_commit         IN VARCHAR2,
163      x_return_status  OUT NOCOPY VARCHAR2);
164 
165   -- This Procedure handles Approved POs and is Part of PO Synchronization.
166   -- This procedure commits its work if p_commit is set to true and
167   -- if there were no errors during the execution of this procedure.
168   -- It does not check the message list for performing the commit action
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,
178      p_request_id   IN NUMBER    := NULL,
179      p_status_code  IN VARCHAR2  := NULL,
180      p_po_header_id IN NUMBER    := NULL,
181      p_intf_hdr_id  IN NUMBER    := NULL);
182 
183   FUNCTION Get_Item_Price(
184      p_osp_line_id IN NUMBER) RETURN NUMBER;
185 
186 /** The following two procedures Handle_Deleted_PO_Headers and Handle_Deleted_Sales_Orders
187   * were added by jaramana on March 31, 2006 to implement the ER 5074660
188 ***/
189   -- This Procedure handles deleted PO Headers and is Part of PO Synchronization.
190   -- This procedure commits its work if p_commit is set to true and
191   -- if there were no errors during the execution of this procedure.
192   -- It does not check the message list for performing the commit action
193   PROCEDURE Handle_Deleted_PO_Headers(
194      p_commit         IN VARCHAR2,
195      x_return_status  OUT NOCOPY VARCHAR2);
196 
197   -- This Procedure handles deleted Sales Orders and is Part of PO Synchronization.
198   -- This procedure commits its work if p_commit is set to true and
199   -- if there were no errors during the execution of this procedure.
200   -- It does not check the message list for performing the commit action
201   PROCEDURE Handle_Deleted_Sales_Orders(
202      p_commit         IN VARCHAR2,
203      x_return_status  OUT NOCOPY VARCHAR2);
204 
205   --Added by mpothuku on 10-Oct-2007 for fixing the Bug 6436184
206   FUNCTION get_charge_account_id
207   (
208     p_inv_org_id  IN  NUMBER,
209     p_item_id IN  NUMBER
210   ) RETURN NUMBER;
211   --mpothuku End
212 
213   -- SATHAPLI::Bug 8583364, 21-Aug-2009
214   -- Function to create PO number, if PO numbering is set to 'MANUAL'. Accordingly, returns the created number, or NULL.
215   FUNCTION create_manual_PO_Number
216   (
217     p_org_id       IN NUMBER,
218     p_osp_order_id IN NUMBER
219   ) RETURN VARCHAR2;
220 
221 -------------------------------------
222 -- End Local Procedures Declaration--
223 -------------------------------------
224 
225 -----------------------------------------
226 -- Public Procedure Definitions follow --
227 -----------------------------------------
228 -- Start of Comments --
229 --  Procedure name    : Create_Purchase_Order
230 --  Type              : Private
231 --  Function          : Validates OSP Information and inserts records into PO Interface tables
232 --                      Launches Concurrent Program to initiate PO creation
233 --                      Updates OSP table with request id batch id and interface header id
234 --  Pre-reqs    :
235 --  Parameters  :
236 --
237 --  Standard IN  Parameters :
238 --      p_api_version                   IN      NUMBER       Required
239 --      p_init_msg_list                 IN      VARCHAR2     Default  FND_API.G_FALSE
240 --      p_commit                        IN      VARCHAR2     Default  FND_API.G_FALSE
241 --      p_validation_level              IN      NUMBER       Default  FND_API.G_VALID_LEVEL_FULL
242 --      p_default                       IN      VARCHAR2     Default  FND_API.G_TRUE
243 --      p_module_type                   IN      VARCHAR2     Default  NULL.
244 --
245 --  Standard OUT Parameters :
246 --      x_return_status                 OUT     VARCHAR2               Required
247 --      x_msg_count                     OUT     NUMBER                 Required
248 --      x_msg_data                      OUT     VARCHAR2               Required
249 --
250 --  Create_Purchase_Order Parameters:
251 --      p_osp_order_id                  IN      NUMBER  Required
252 --         The Id of the OSP Order for which to create the Purchase Order
253 --      x_batch_id                      OUT     NUMBER              Required
254 --         Contains the batch id if the concurrent program was launched successfuly.
255 --      x_request_id                    OUT     NUMBER              Required
256 --         Contains the concurrent request id if the concurrent program was launched successfuly.
257 --      x_interface_header_id           OUT     NUMBER              Required
258 --         Contains the interface header id generated for the po_headers_interface table.
259 --
260 --
261 --  Version :
262 --      Initial Version   1.0
263 --
264 --  End of Comments.
265 
266 PROCEDURE Create_Purchase_Order
267 (
268     p_api_version           IN            NUMBER,
269     p_init_msg_list         IN            VARCHAR2  := FND_API.G_FALSE,
270     p_commit                IN            VARCHAR2  := FND_API.G_FALSE,
271     p_validation_level      IN            NUMBER    := FND_API.G_VALID_LEVEL_FULL,
272     p_default               IN            VARCHAR2  := FND_API.G_TRUE,
273     p_module_type           IN            VARCHAR2  := NULL,
274     p_osp_order_id          IN            NUMBER    := NULL,  -- Required if Number is not given
275     p_osp_order_number      IN            NUMBER    := NULL,  -- Required if Id is not given
276     x_batch_id              OUT  NOCOPY   NUMBER,
277     x_request_id            OUT  NOCOPY   NUMBER,
278     x_interface_header_id   OUT  NOCOPY   NUMBER,
279     x_return_status         OUT  NOCOPY   VARCHAR2,
280     x_msg_count             OUT  NOCOPY   NUMBER,
281     x_msg_data              OUT  NOCOPY   VARCHAR2) IS
282 
283    l_api_version            CONSTANT NUMBER := 1.0;
284    l_api_name               CONSTANT VARCHAR2(30) := 'Create_Purchase_Order';
285    L_DEBUG_KEY              CONSTANT VARCHAR2(150) := G_LOG_PREFIX || '.Create_Purchase_Order';
286 
287   CURSOR l_osp_id_csr(p_osp_order_number IN NUMBER) IS
288     SELECT OSP_ORDER_ID
289     FROM AHL_OSP_ORDERS_B
290     WHERE OSP_ORDER_NUMBER = p_osp_order_number;
291 
292   CURSOR l_osp_dtls_csr(p_osp_order_id IN NUMBER) IS
293     -- VENDOR_CONTACT_ID added by jaramana on May 27, 2005
294     SELECT VENDOR_ID, VENDOR_SITE_ID, PO_AGENT_ID, PO_BATCH_ID, PO_INTERFACE_HEADER_ID, VENDOR_CONTACT_ID
295     , STATUS_CODE -- Added by jaramana on January 7, 2008 for the Requisition ER 6034236
296     FROM AHL_OSP_ORDERS_B
297     WHERE OSP_ORDER_ID = p_osp_order_id;
298 
299 -- Begin ER 266135 Fix
300 /*
301   CURSOR l_osp_line_dtls_csr(p_osp_order_id IN NUMBER) IS
302     SELECT OL.OSP_ORDER_LINE_ID, OL.OSP_LINE_NUMBER, OL.SERVICE_ITEM_ID,
303            OL.SERVICE_ITEM_DESCRIPTION, WO.QUANTITY, OL.NEED_BY_DATE,
304            OL.SERVICE_ITEM_UOM_CODE, OL.PO_LINE_TYPE_ID,
305            BOM.ORGANIZATION_ID, BOM.LOCATION_ID
306     FROM AHL_OSP_ORDER_LINES OL, AHL_WORKORDERS_V WO, BOM_DEPARTMENTS BOM
307     WHERE OL.OSP_ORDER_ID = p_osp_order_id AND
308           WO.WORKORDER_ID = OL.WORKORDER_ID AND
309           BOM.DEPARTMENT_ID (+) = WO.DEPARTMENT_ID;
310 */
311 -- Changed by jaramana on May 27, 2005 to support Inventory Service Orders
312 --  CURSOR l_osp_line_dtls_csr(p_osp_order_id IN NUMBER) IS
313 --    SELECT OL.OSP_ORDER_LINE_ID, OL.OSP_LINE_NUMBER, OL.SERVICE_ITEM_ID,
314 --           OL.SERVICE_ITEM_DESCRIPTION, OL.QUANTITY, OL.NEED_BY_DATE,
315 --           OL.SERVICE_ITEM_UOM_CODE, OL.PO_LINE_TYPE_ID,
316 ---- Changed by jaramana on May 26, 2005 to Fix bug 4393374
317 ----           BOM.ORGANIZATION_ID, BOM.LOCATION_ID
318 --           WO.ORGANIZATION_ID, BOM.LOCATION_ID
319 --    FROM AHL_OSP_ORDER_LINES OL, AHL_WORKORDERS_OSP_V WO, BOM_DEPARTMENTS BOM
320 --    WHERE OL.OSP_ORDER_ID = p_osp_order_id AND
321 --          WO.WORKORDER_ID = OL.WORKORDER_ID AND
322 --          BOM.DEPARTMENT_ID (+) = WO.DEPARTMENT_ID;
323 -- End ER 266135 Fix
324 
325 -- Changed by jaramana on October 26, 2005 for ER 4544642
326   CURSOR l_osp_line_dtls_csr(p_osp_order_id IN NUMBER) IS
327     SELECT OL.OSP_ORDER_LINE_ID, OL.OSP_LINE_NUMBER, OL.SERVICE_ITEM_ID,
328            OL.SERVICE_ITEM_DESCRIPTION, OL.QUANTITY, OL.NEED_BY_DATE,
329            OL.SERVICE_ITEM_UOM_CODE, OL.PO_LINE_TYPE_ID,
330 /**
331            OL.INVENTORY_ORG_ID, BOM.LOCATION_ID
332     FROM AHL_OSP_ORDER_LINES OL, AHL_WORKORDERS_OSP_V WO, BOM_DEPARTMENTS BOM
333 **/
334            OL.INVENTORY_ORG_ID, DECODE(OL.WORKORDER_ID, NULL, HAOU.LOCATION_ID, BOM.LOCATION_ID)
335            -- Added by mpothuku on 10-oct-2007 to fix bug 6431740
336            , WO.WIP_ENTITY_ID
337            , WDJ.PROJECT_ID
338            , WDJ.TASK_ID
339 /*
340     FROM AHL_OSP_ORDER_LINES OL, AHL_WORKORDERS_OSP_V WO, BOM_DEPARTMENTS BOM, HR_ALL_ORGANIZATION_UNITS HAOU
341 */
342     -- Changes made by jaramana on December 19, 2005
343     -- to improve the performace of this SQL.
344     -- Removed reference to AHL_WORKORDERS_OSP_V and instead joined directly with
345     -- WIP_DISCRETE_JOBS to get the work order department
346     FROM AHL_OSP_ORDER_LINES OL, AHL_WORKORDERS WO, BOM_DEPARTMENTS BOM, HR_ALL_ORGANIZATION_UNITS HAOU, WIP_DISCRETE_JOBS WDJ
347     WHERE OL.OSP_ORDER_ID = p_osp_order_id AND
348           WO.WORKORDER_ID (+) = OL.WORKORDER_ID AND
349 /**
350           BOM.DEPARTMENT_ID (+) = WO.DEPARTMENT_ID;
351 **/
352 /*
353           BOM.DEPARTMENT_ID (+) = WO.DEPARTMENT_ID AND
354 */
355           WDJ.WIP_ENTITY_ID (+) = WO.WIP_ENTITY_ID AND
356           BOM.DEPARTMENT_ID (+) = WDJ.OWNING_DEPARTMENT AND
357           HAOU.ORGANIZATION_ID = OL.INVENTORY_ORG_ID;
358 
359   -- Added by jaramana on June 24, 2005 to get the updated Return to Org
360   -- Updated by jaramana on March 20, 2006 to get the Org Location for fixing Bug 5104282
361   CURSOR get_return_to_org_csr(p_osp_line_id IN NUMBER) IS
362     SELECT oola.ship_from_org_id, HAOU.LOCATION_ID
363     FROM oe_order_lines_all oola, ahl_osp_order_lines aool, HR_ALL_ORGANIZATION_UNITS HAOU
364     WHERE oola.line_id = aool.oe_return_line_id and
365           HAOU.ORGANIZATION_ID = oola.ship_from_org_id and
366           aool.osp_order_line_id = p_osp_line_id;
367 
368    l_po_header              PO_Header_Rec_Type;
369    l_po_line_tbl            PO_Line_Tbl_Type;
370    l_intf_hdr_id            NUMBER;
371    l_batch_id               NUMBER;
372    l_old_batch_id           NUMBER := null;
373    l_old_intf_header_id     NUMBER := null;
374    l_request_id             NUMBER := 0;
375    l_temp_num               NUMBER := 0;
376    l_temp_ret_org_id        NUMBER;
377    l_temp_ret_org_loc_id    NUMBER;
378 
379    -- Added by jaramana on January 7, 2008 for the Requisition ER 6034236
380    l_curr_status            AHL_OSP_ORDERS_B.STATUS_CODE%TYPE;
381 
382 BEGIN
383 
384   -- Standard start of API savepoint
385   SAVEPOINT Create_Purchase_Order_pvt;
386 
387   IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
388     FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, L_DEBUG_KEY || '.begin', 'Entering Procedure');
389   END IF;
390 
391   -- Standard call to check for call compatibility
392   IF NOT FND_API.Compatible_API_Call(l_api_version, p_api_version, l_api_name,
393                                      G_PKG_NAME) THEN
394     RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
395   END IF;
396 
397     -- Initialize message list if p_init_msg_list is set to TRUE
398   IF FND_API.To_Boolean(p_init_msg_list) THEN
399     FND_MSG_PUB.Initialize;
400   END IF;
401 
402   -- Initialize API return status to success
403   x_return_status := FND_API.G_RET_STS_SUCCESS;
404 
405   -- Begin Processing
406 
407   IF FND_API.to_boolean( p_default ) THEN
408     -- No special default settings required in this API
409     NULL;
410   END IF;
411 
412   -- Start processing
413 
414   -- Create the Header Rec
415   IF (p_osp_order_id IS NOT NULL) THEN
416     l_po_header.OSP_ORDER_ID := p_osp_order_id;
417   ELSE
418     -- Convert the Order number to Id
419 --dbms_output.put_line('Perfoming OSP Order Number to Id conversion');
420     OPEN l_osp_id_csr(p_osp_order_number);
421     FETCH l_osp_id_csr INTO l_po_header.OSP_ORDER_ID;
422     CLOSE l_osp_id_csr;
423   END IF;
424 
425   OPEN l_osp_dtls_csr(l_po_header.OSP_ORDER_ID);
426   FETCH l_osp_dtls_csr INTO l_po_header.VENDOR_ID,
427                             l_po_header.VENDOR_SITE_ID,
428                             l_po_header.BUYER_ID,
429                             l_old_batch_id,  -- For Purging Interface table records
430                             l_old_intf_header_id,  -- -do-
431                             l_po_header.VENDOR_CONTACT_ID,
432                             l_curr_status; -- Added by jaramana on January 7, 2008 for the Requisition ER 6034236
433   CLOSE l_osp_dtls_csr;
434 --dbms_output.put_line('Got Header Rec ');
435   -- Validate Header
436   Validate_PO_Header(l_po_header);
437 --dbms_output.put_line('Validated Header Rec ');
438   -- Create the Lines Table
439   OPEN l_osp_line_dtls_csr(p_osp_order_id);
440   LOOP
441     FETCH l_osp_line_dtls_csr INTO l_po_line_tbl(l_temp_num).OSP_LINE_ID,
442                                    l_po_line_tbl(l_temp_num).LINE_NUMBER,
443                                    l_po_line_tbl(l_temp_num).ITEM_ID,
444                                    l_po_line_tbl(l_temp_num).ITEM_DESCRIPTION,
445                                    l_po_line_tbl(l_temp_num).QUANTITY,
446                                    l_po_line_tbl(l_temp_num).NEED_BY_DATE,
447                                    l_po_line_tbl(l_temp_num).UOM_CODE,
448                                    l_po_line_tbl(l_temp_num).PO_LINE_TYPE_ID,
449                                    l_po_line_tbl(l_temp_num).SHIP_TO_ORG_ID,
450                                    l_po_line_tbl(l_temp_num).SHIP_TO_LOC_ID,
451                                    -- Added by mpothuku on 10-oct-2007 to fix bug 6431740
452                                    l_po_line_tbl(l_temp_num).WIP_ENTITY_ID,
453                                    l_po_line_tbl(l_temp_num).PROJECT_ID,
454                                    l_po_line_tbl(l_temp_num).TASK_ID;
455     EXIT WHEN l_osp_line_dtls_csr%NOTFOUND;
456 
457     -- Added by mpothuku on 10-oct-2007 to fix bug 6431740
458     IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
459       FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, L_DEBUG_KEY,
460           ' Fetching from l_osp_line_dtls_csr. l_temp_num = ' || l_temp_num ||
461           ', OSP_LINE_ID = ' || l_po_line_tbl(l_temp_num).OSP_LINE_ID ||
462           ', WIP_ENTITY_ID = ' || l_po_line_tbl(l_temp_num).WIP_ENTITY_ID ||
463           ', PROJECT_ID = ' || l_po_line_tbl(l_temp_num).PROJECT_ID ||
464           ', TASK_ID = ' || l_po_line_tbl(l_temp_num).TASK_ID);
465     END IF;
466 
467     -- Added by jaramana on June 24, 2005 to get the updated Return to Org.
468     -- It will now be possible to change the Warehouse in the return lines of a Shipment.
469     -- This change will ensure that the PO Shipment gets this changed Org.
470     -- Updated by jaramana on March 20, 2006 for fixing Bug 5104282
471     OPEN get_return_to_org_csr(l_po_line_tbl(l_temp_num).OSP_LINE_ID);
472     FETCH get_return_to_org_csr INTO l_temp_ret_org_id, l_temp_ret_org_loc_id;
473     IF (get_return_to_org_csr%FOUND AND l_temp_ret_org_id IS NOT NULL) THEN
474       IF (l_temp_ret_org_id <> l_po_line_tbl(l_temp_num).SHIP_TO_ORG_ID) THEN
475         l_po_line_tbl(l_temp_num).SHIP_TO_ORG_ID := l_temp_ret_org_id;
476         -- Update the Ship To Location only if the Return To Org is different
477         -- from the Line's Inventory Org
478         l_po_line_tbl(l_temp_num).SHIP_TO_LOC_ID := l_temp_ret_org_loc_id;
479       END IF;
480     END IF;
481     CLOSE get_return_to_org_csr;
482 
483     l_temp_num := l_temp_num + 1;
484   END LOOP;
485   CLOSE l_osp_line_dtls_csr;
486   l_po_line_tbl.DELETE(l_temp_num);  -- Delete the last (null) record
487 --dbms_output.put_line('Created Lines Table ');
488   -- Validate Lines
489   Validate_PO_Lines(l_po_line_tbl, l_po_header.OSP_ORDER_ID);
490 --dbms_output.put_line('Validated Lines Table ');
491   -- Check Error Message stack.
492   x_msg_count := FND_MSG_PUB.count_msg;
493   IF x_msg_count > 0 THEN
494     RAISE  FND_API.G_EXC_ERROR;
495   END IF;
496 --dbms_output.put_line('no errors : going onto insert into po_headers_interface ');
497   -- Insert Row into PO_HEADERS_INTERFACE
498   Insert_PO_Header(p_po_header_rec  => l_po_header,
499                    x_intf_header_id => l_intf_hdr_id,
500                    x_batch_id       => l_batch_id);
501 --dbms_output.put_line('Inserted row into po_headers_interface. x_intf_header_id = ' || l_intf_hdr_id);
502 --dbms_output.put_line('x_batch_id = ' || l_batch_id);
503   -- Insert rows into PO_LINES_INTERFACE
504   Insert_PO_Lines(p_po_line_tbl    => l_po_line_tbl,
505                   p_intf_header_id => l_intf_hdr_id);
506 --dbms_output.put_line('inserted rows into po_lines_interface ');
507   -- Purge Error records from prior submission (if any)
508   -- Begin Changes by jaramana on January 7, 2008 for the Requisition ER 6034236
509   IF (l_old_intf_header_id IS NOT NULL AND l_curr_status = G_OSP_SUB_FAILED_STATUS) THEN
510     -- PO Submission had failed earlier
511     DELETE FROM PO_INTERFACE_ERRORS WHERE
512       INTERFACE_HEADER_ID = l_old_intf_header_id;
513     DELETE FROM PO_HEADERS_INTERFACE WHERE
514       INTERFACE_HEADER_ID = l_old_intf_header_id;
515     DELETE FROM PO_LINES_INTERFACE WHERE
516       INTERFACE_HEADER_ID = l_old_intf_header_id;
517     DELETE FROM PO_DISTRIBUTIONS_INTERFACE WHERE
518       INTERFACE_HEADER_ID = l_old_intf_header_id;
519   ELSIF (l_curr_status = G_OSP_REQ_SUB_FAILED_STATUS) THEN
520     -- Requisition Submission had failed earlier
521     -- Delete from the Errors table first so that the subquery can use po_requisitions_interface_all
522     DELETE FROM po_interface_errors
523      WHERE INTERFACE_TRANSACTION_ID in
524            (SELECT transaction_id
525               FROM po_requisitions_interface_all
526              WHERE INTERFACE_SOURCE_CODE = AHL_GLOBAL.AHL_APP_SHORT_NAME
527                AND INTERFACE_SOURCE_LINE_ID = l_po_header.OSP_ORDER_ID);
528     DELETE FROM po_requisitions_interface_all
529      WHERE INTERFACE_SOURCE_CODE = AHL_GLOBAL.AHL_APP_SHORT_NAME
530        AND INTERFACE_SOURCE_LINE_ID = l_po_header.OSP_ORDER_ID;
531   END IF;
532   -- End Changes by jaramana on January 7, 2008 for the Requisition ER 6034236
533 --dbms_output.put_line('Purged error records ');
534   -- Launch Concurrent Program to create PO
535 --dbms_output.put_line('About to call Concurrent program ');
536   Call_PDOI_Program(p_batch_id   => l_batch_id,
537                     x_request_id => l_request_id);
538   IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
539     FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, L_DEBUG_KEY, 'Concurrent Program Request Submitted. Request Id = ' || l_request_id);
540   END IF;
541 --dbms_output.put_line('Concurrent Program called. Request Id = ' || l_request_id);
542   -- Check if request was submitted without error
543   IF (l_request_id = 0) THEN
544     -- Add Error Message generated by Concurrent Manager to Message List
545     FND_MSG_PUB.ADD;
546     IF (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
547       FND_LOG.MESSAGE(FND_LOG.LEVEL_UNEXPECTED, L_DEBUG_KEY, FALSE);
548     END IF;
549     RAISE  FND_API.G_EXC_ERROR;
550   END IF;
551 
552 --dbms_output.put_line('Recording OSP Submission... ');
553   -- Update OSP Table with batch id, request id and interface header id
554   Record_OSP_Submission(p_osp_order_id => p_osp_order_id,
555                         p_batch_id     => l_batch_id,
556                         p_request_id   => l_request_id,
557                         p_intf_hdr_id  => l_intf_hdr_id);
558 
559 --dbms_output.put_line('Recorded OSP Submission ');
560   -- Set Return parameters
561   x_batch_id := l_batch_id;
562   x_request_id := l_request_id;
563   x_interface_header_id := l_intf_hdr_id;
564 
565 --dbms_output.put_line('Completed Processing ');
566   IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
567     FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, L_DEBUG_KEY, 'Completed Processing. Checking for errors');
568   END IF;
569   -- Check Error Message stack.
570   x_msg_count := FND_MSG_PUB.count_msg;
571   IF x_msg_count > 0 THEN
572     RAISE  FND_API.G_EXC_ERROR;
573   END IF;
574 
575   -- Log this transaction in the Log Table
576   AHL_OSP_UTIL_PKG.Log_Transaction(p_trans_type_code    => G_TXN_TYPE_PO_CREATION,
577                                    p_src_doc_id         => p_osp_order_id,
578                                    p_src_doc_type_code  => G_DOC_TYPE_OSP,
579                                    p_dest_doc_id        => l_batch_id,
580                                    p_dest_doc_type_code => G_DOC_TYPE_PO);
581 
582   -- Standard check of p_commit
583   IF FND_API.TO_BOOLEAN(p_commit) THEN
584 --dbms_output.put_line('About to commit work ');
585       COMMIT WORK;
586   END IF;
587 
588   -- Standard call to get message count and if count is 1, get message info
589   FND_MSG_PUB.Count_And_Get
590     ( p_count => x_msg_count,
591       p_data  => x_msg_data,
592       p_encoded => fnd_api.g_false
593     );
594 
595   IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
596     FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, L_DEBUG_KEY || '.end', 'Exiting Procedure');
597   END IF;
598 
599 --dbms_output.put_line('About to return from procedure ');
600 EXCEPTION
601  WHEN FND_API.G_EXC_ERROR THEN
602    ROLLBACK TO Create_Purchase_Order_pvt;
603    x_return_status := FND_API.G_RET_STS_ERROR;
604    FND_MSG_PUB.count_and_get( p_count => x_msg_count,
605                               p_data  => x_msg_data,
606                               p_encoded => fnd_api.g_false);
607    --AHL_UTIL_PKG.Err_Mesg_To_Table(x_err_mesg_tbl);
608 
609  WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
610    ROLLBACK TO Create_Purchase_Order_pvt;
611    x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
612    FND_MSG_PUB.count_and_get( p_count => x_msg_count,
613                               p_data  => x_msg_data,
614                               p_encoded => fnd_api.g_false);
615    --AHL_UTIL_PKG.Err_Mesg_To_Table(x_err_mesg_tbl);
616 
617  WHEN OTHERS THEN
618     ROLLBACK TO Create_Purchase_Order_pvt;
619     x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
620     IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
621        fnd_msg_pub.add_exc_msg(p_pkg_name       => G_PKG_NAME,
622                                p_procedure_name => 'Create_Purchase_Order',
623                                p_error_text     => SUBSTR(SQLERRM,1,240));
624     END IF;
625     FND_MSG_PUB.count_and_get( p_count => x_msg_count,
626                                p_data  => x_msg_data,
627                                p_encoded => fnd_api.g_false);
628     --AHL_UTIL_PKG.Err_Mesg_To_Table(x_err_mesg_tbl);
629 
630 END Create_Purchase_Order;
631 
632 ----------------------------------------
633 
634 -- Start of Comments --
635 --  Procedure name    : Associate_OSP_With_PO
636 --  Type              : Private
637 --  Function          : Updates AHL_OSP_ORDERS_B.PO_HEADER_ID and
638 --                      AHL_OSP_ORDER_LINES.PO_LINE_ID with PO_HEADER_ID and
639 --                      PO_LINE_ID respectively for a single submitted OSP Order.
640 --                      Does not give error if the OSP Order is already associated
641 --                      or if there is no corresponding PO yet.
642 --  Pre-reqs    :
643 --  Parameters  :
644 --
645 --  Standard IN  Parameters :
646 --      p_api_version                   IN      NUMBER       Required
647 --      p_init_msg_list                 IN      VARCHAR2     Default  FND_API.G_FALSE
648 --      p_commit                        IN      VARCHAR2     Default  FND_API.G_FALSE
649 --      p_validation_level              IN      NUMBER       Default  FND_API.G_VALID_LEVEL_FULL
650 --      p_default                       IN      VARCHAR2     Default  FND_API.G_TRUE
651 --      p_module_type                   IN      VARCHAR2     Default  NULL.
652 --
653 --  Standard OUT Parameters :
654 --      x_return_status                 OUT     VARCHAR2               Required
655 --      x_msg_count                     OUT     NUMBER                 Required
656 --      x_msg_data                      OUT     VARCHAR2               Required
657 --
658 --  Associate_New_PO_Lines Parameters:
659 --      p_osp_order_id                  IN      NUMBER                    Required
660 --         Id of the OSP Order containing the PO Lines
661 --      x_po_header_id                  OUT     NUMBER                    Required
662 --         Id of the associated PO Header if the association succeeded.
663 --
664 --
665 --  Version :
666 --      Initial Version   1.0
667 --
668 --  End of Comments.
669 PROCEDURE Associate_OSP_With_PO
670 (
671     p_api_version           IN            NUMBER,
672     p_init_msg_list         IN            VARCHAR2  := FND_API.G_FALSE,
673     p_commit                IN            VARCHAR2  := FND_API.G_FALSE,
674     p_validation_level      IN            NUMBER    := FND_API.G_VALID_LEVEL_FULL,
675     p_default               IN            VARCHAR2  := FND_API.G_TRUE,
676     p_module_type           IN            VARCHAR2  := NULL,
677     p_osp_order_id          IN            NUMBER,
678     x_po_header_id          OUT  NOCOPY   NUMBER,
679     x_return_status         OUT  NOCOPY   VARCHAR2,
680     x_msg_count             OUT  NOCOPY   NUMBER,
681     x_msg_data              OUT  NOCOPY   VARCHAR2) IS
682 
683   CURSOR l_validate_osp_csr(p_osp_order_id IN NUMBER) IS
684     SELECT PO_HEADER_ID FROM AHL_OSP_ORDERS_B
685     WHERE OSP_ORDER_ID = p_osp_order_id;
686 -- Don't throw error based on status
687 --      AND STATUS_CODE = G_OSP_SUBMITTED_STATUS;
688 
689    l_api_version            CONSTANT NUMBER := 1.0;
690    l_api_name               CONSTANT VARCHAR2(30) := 'Associate_OSP_With_PO';
691    L_DEBUG_KEY              CONSTANT VARCHAR2(150) := G_LOG_PREFIX || '.Associate_OSP_With_PO';
692    l_dummy                  VARCHAR2(1);
693    l_po_header_id           NUMBER;
694 
695 BEGIN
696 
697   IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
698     FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, L_DEBUG_KEY || '.begin', 'Entering Procedure');
699   END IF;
700 
701   -- Standard start of API savepoint
702   SAVEPOINT Associate_OSP_With_PO_pvt;
703 
704   -- Standard call to check for call compatibility
705   IF NOT FND_API.Compatible_API_Call(l_api_version, p_api_version, l_api_name,
706                                      G_PKG_NAME) THEN
707     RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
708   END IF;
709 
710     -- Initialize message list if p_init_msg_list is set to TRUE
711   IF FND_API.To_Boolean(p_init_msg_list) THEN
712     FND_MSG_PUB.Initialize;
713   END IF;
714 
715   -- Initialize API return status to success
716   x_return_status := FND_API.G_RET_STS_SUCCESS;
717 
718   IF FND_API.to_boolean( p_default ) THEN
719     -- No special default settings required in this API
720     NULL;
721   END IF;
722 
723 --dbms_output.put_line('Beginning Processing...  ');
724   -- Validate OSP Order Id
725   IF (p_osp_order_id IS NULL OR p_osp_order_id = FND_API.G_MISS_NUM) THEN
726     FND_MESSAGE.Set_Name('AHL', 'AHL_OSP_OSP_ID_NULL');
727     FND_MSG_PUB.ADD;
728     IF (FND_LOG.LEVEL_ERROR >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
729       FND_LOG.MESSAGE(FND_LOG.LEVEL_ERROR, L_DEBUG_KEY, FALSE);
730     END IF;
731 --dbms_output.put_line('OSP Order Id is null');
732     RAISE FND_API.G_EXC_ERROR;
733   ELSE
734     OPEN l_validate_osp_csr(p_osp_order_id);
735     FETCH l_validate_osp_csr INTO l_po_header_id;
736     IF (l_validate_osp_csr%NOTFOUND) THEN
737       FND_MESSAGE.Set_Name('AHL', 'AHL_OSP_OSP_ID_INVALID');
738       FND_MESSAGE.Set_Token('OSP_ID', p_osp_order_id);
739       FND_MSG_PUB.ADD;
740       IF (FND_LOG.LEVEL_ERROR >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
741         FND_LOG.MESSAGE(FND_LOG.LEVEL_ERROR, L_DEBUG_KEY, FALSE);
742       END IF;
743       CLOSE l_validate_osp_csr;
744 --dbms_output.put_line('OSP Order Id ' || p_osp_order_id || ' is invalid');
745       RAISE FND_API.G_EXC_ERROR;
746     ELSIF (l_po_header_id IS NOT NULL) THEN
747       IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
748         FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, L_DEBUG_KEY, 'OSP Order Id ' || p_osp_order_id || ' is already associated with PO ' || l_po_header_id);
749       END IF;
750 --dbms_output.put_line('OSP Order Id ' || p_osp_order_id || ' is already associated with PO ' || l_po_header_id);
751       -- No need to throw an exception
752       /*
753       FND_MESSAGE.Set_Name('AHL', 'AHL_OSP_PO_HDR_ASSCTD');
754       FND_MESSAGE.Set_Token('OSP_ID', p_osp_order_id);
755       FND_MSG_PUB.ADD;
756       CLOSE l_validate_osp_csr;
757       RAISE FND_API.G_EXC_ERROR;
758       */
759     ELSE
760       -- Make the association
761 --dbms_output.put_line('About to Make Association ');
762       Associate_OSP_PO(p_osp_order_id => p_osp_order_id,
763                        x_po_header_id => x_po_header_id);
764 --dbms_output.put_line('Completed Making Association ');
765     END IF;
766     CLOSE l_validate_osp_csr;
767   END IF;
768 
769   IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
770     FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, L_DEBUG_KEY, 'Completed Processing. Checking for errors');
771   END IF;
772 --dbms_output.put_line('Completed Processing. Checking for errors ');
773   -- Check Error Message stack.
774   x_msg_count := FND_MSG_PUB.count_msg;
775   IF x_msg_count > 0 THEN
776     RAISE  FND_API.G_EXC_ERROR;
777   END IF;
778 
779   -- Standard check of p_commit
780   IF FND_API.TO_BOOLEAN(p_commit) THEN
781 --dbms_output.put_line('About to commit');
782     IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
783       FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, L_DEBUG_KEY, 'About to commit');
784     END IF;
785     COMMIT WORK;
786   END IF;
787 
788   -- Standard call to get message count and if count is 1, get message info
789   FND_MSG_PUB.Count_And_Get
790     ( p_count => x_msg_count,
791       p_data  => x_msg_data,
792       p_encoded => fnd_api.g_false
793     );
794 
795   IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
796     FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, L_DEBUG_KEY || '.end', 'Exiting Procedure');
797   END IF;
798 --dbms_output.put_line('About to return ');
799 EXCEPTION
800  WHEN FND_API.G_EXC_ERROR THEN
801    ROLLBACK TO Associate_OSP_With_PO_pvt;
802    x_return_status := FND_API.G_RET_STS_ERROR;
803    FND_MSG_PUB.count_and_get( p_count => x_msg_count,
804                               p_data  => x_msg_data,
805                               p_encoded => fnd_api.g_false);
806 
807  WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
808    ROLLBACK TO Associate_OSP_With_PO_pvt;
809    x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
810    FND_MSG_PUB.count_and_get( p_count => x_msg_count,
811                               p_data  => x_msg_data,
812                               p_encoded => fnd_api.g_false);
813 
814  WHEN OTHERS THEN
815    ROLLBACK TO Associate_OSP_With_PO_pvt;
816    x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
817    IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
818      fnd_msg_pub.add_exc_msg(p_pkg_name       => G_PKG_NAME,
819                                p_procedure_name => 'Associate_OSP_With_PO',
820                                p_error_text     => SUBSTR(SQLERRM,1,240));
821    END IF;
822    FND_MSG_PUB.count_and_get( p_count => x_msg_count,
823                                p_data  => x_msg_data,
824                                p_encoded => fnd_api.g_false);
825 
826 END Associate_OSP_With_PO;
827 
828 ----------------------------------------
829 
830 -- Start of Comments --
831 --  Procedure name    : Associate_All_OSP_POs
832 --  Type              : Private
833 --  Function          : Updates AHL_OSP_ORDERS_B.PO_HEADER_ID and
834 --                      AHL_OSP_ORDER_LINES.PO_LINE_ID with PO_HEADER_ID and
835 --                      PO_LINE_ID respectively for all submitted OSP Orders.
836 --  Pre-reqs    :
837 --  Parameters  :
838 --
839 --  Standard IN  Parameters :
840 --      p_api_version                   IN      NUMBER       Required
841 --      p_init_msg_list                 IN      VARCHAR2     Default  FND_API.G_FALSE
842 --      p_commit                        IN      VARCHAR2     Default  FND_API.G_FALSE
843 --      p_validation_level              IN      NUMBER       Default  FND_API.G_VALID_LEVEL_FULL
844 --      p_default                       IN      VARCHAR2     Default  FND_API.G_TRUE
845 --      p_module_type                   IN      VARCHAR2     Default  NULL.
846 --
847 --  Standard OUT Parameters :
848 --      x_return_status                 OUT     VARCHAR2               Required
849 --      x_msg_count                     OUT     NUMBER                 Required
850 --      x_msg_data                      OUT     VARCHAR2               Required
851 --
852 --  Version :
853 --      Initial Version   1.0
854 --
855 --  End of Comments.
856 PROCEDURE Associate_All_OSP_POs
857 (
858     p_api_version           IN            NUMBER,
859     p_init_msg_list         IN            VARCHAR2  := FND_API.G_FALSE,
860     p_commit                IN            VARCHAR2  := FND_API.G_FALSE,
861     p_validation_level      IN            NUMBER    := FND_API.G_VALID_LEVEL_FULL,
862     p_default               IN            VARCHAR2  := FND_API.G_TRUE,
863     p_module_type           IN            VARCHAR2  := NULL,
864     x_return_status         OUT  NOCOPY   VARCHAR2,
865     x_msg_count             OUT  NOCOPY   NUMBER,
866     x_msg_data              OUT  NOCOPY   VARCHAR2) IS
867 
868   CURSOR l_get_osps_csr IS
869     SELECT OSP_ORDER_ID FROM AHL_OSP_ORDERS_B
870     WHERE STATUS_CODE = G_OSP_SUBMITTED_STATUS
871       AND PO_HEADER_ID IS NULL
872       AND PO_BATCH_ID IS NOT NULL
873       -- Added by jaramana on April 7, 2008 for bug 6609988
874       AND OPERATING_UNIT_ID = MO_GLOBAL.get_current_org_id();
875 
876    l_api_version            CONSTANT NUMBER := 1.0;
877    l_api_name               CONSTANT VARCHAR2(30) := 'Associate_All_OSP_POs';
878    L_DEBUG_KEY              CONSTANT VARCHAR2(150) := G_LOG_PREFIX || '.Associate_All_OSP_POs';
879    l_dummy                  VARCHAR2(1);
880    l_osp_order_id           NUMBER;
881    l_po_header_id           NUMBER;
882    l_temp_count             NUMBER := 0;
883 
884 BEGIN
885   IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
886     FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, L_DEBUG_KEY || '.begin', 'Entering Procedure');
887   END IF;
888 
889   -- Standard start of API savepoint
890   SAVEPOINT Associate_All_OSP_POs_pvt;
891 
892   -- Standard call to check for call compatibility
893   IF NOT FND_API.Compatible_API_Call(l_api_version, p_api_version, l_api_name,
894                                      G_PKG_NAME) THEN
895     RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
896   END IF;
897 
898     -- Initialize message list if p_init_msg_list is set to TRUE
899   IF FND_API.To_Boolean(p_init_msg_list) THEN
900     FND_MSG_PUB.Initialize;
901   END IF;
902 
903   -- Initialize API return status to success
904   x_return_status := FND_API.G_RET_STS_SUCCESS;
905 
906   IF FND_API.to_boolean( p_default ) THEN
907     -- No special default settings required in this API
908     NULL;
909   END IF;
910 
911   -- Start processing
912   OPEN l_get_osps_csr;
913   LOOP
914     FETCH l_get_osps_csr INTO l_osp_order_id;
915     EXIT WHEN l_get_osps_csr%NOTFOUND;
916     Associate_OSP_PO(p_osp_order_id => l_osp_order_id,
917                      x_po_header_id => l_po_header_id);
918     IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
919       FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, L_DEBUG_KEY, 'Associated OSP Order with Id ' || l_osp_order_id || ' to PO with Id ' || l_po_header_id);
920     END IF;
921     l_temp_count := l_temp_count + 1;
922   END LOOP;
923   --Added by jaramana on 7-JAN-2008
924   CLOSE l_get_osps_csr;
925   --jaramana End
926   IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
927     FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, L_DEBUG_KEY, 'Associated ' || l_temp_count || ' OSP Orders with POs');
928     FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, L_DEBUG_KEY, 'Completed Processing. Checking for errors');
929   END IF;
930   -- Check Error Message stack.
931   x_msg_count := FND_MSG_PUB.count_msg;
932   IF x_msg_count > 0 THEN
933     RAISE  FND_API.G_EXC_ERROR;
934   END IF;
935 
936   -- Standard check of p_commit
937   IF FND_API.TO_BOOLEAN(p_commit) THEN
938     IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
939       FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, L_DEBUG_KEY, 'About to COMMIT work.');
940     END IF;
941     COMMIT WORK;
942   END IF;
943 
944   -- Standard call to get message count and if count is 1, get message info
945   FND_MSG_PUB.Count_And_Get
946     ( p_count => x_msg_count,
947       p_data  => x_msg_data,
948       p_encoded => fnd_api.g_false
949     );
950 
951   IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
952     FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, L_DEBUG_KEY || '.end', 'Exiting Procedure');
953   END IF;
954 
955 EXCEPTION
956  WHEN FND_API.G_EXC_ERROR THEN
957    ROLLBACK TO Associate_All_OSP_POs_pvt;
958    x_return_status := FND_API.G_RET_STS_ERROR;
959    FND_MSG_PUB.count_and_get( p_count => x_msg_count,
960                               p_data  => x_msg_data,
961                               p_encoded => fnd_api.g_false);
962 
963  WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
964    ROLLBACK TO Associate_All_OSP_POs_pvt;
965    x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
966    FND_MSG_PUB.count_and_get( p_count => x_msg_count,
967                               p_data  => x_msg_data,
968                               p_encoded => fnd_api.g_false);
969 
970  WHEN OTHERS THEN
971    ROLLBACK TO Associate_All_OSP_POs_pvt;
972    x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
973    IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
974      fnd_msg_pub.add_exc_msg(p_pkg_name       => G_PKG_NAME,
975                                p_procedure_name => 'Associate_All_OSP_POs',
976                                p_error_text     => SUBSTR(SQLERRM,1,240));
977    END IF;
978    FND_MSG_PUB.count_and_get( p_count => x_msg_count,
979                                p_data  => x_msg_data,
980                                p_encoded => fnd_api.g_false);
981 
982 END Associate_All_OSP_POs;
983 
984 -- Start of Comments --
985 --  Procedure name    : PO_Synch_All_OSPs
986 --  Type              : Private
987 --  Function          : Synchronizes all OSPs based on the PO Status
988 --                      1. Handles successfully completed PO Submissions (Updates OSP tables)
989 --                      2. Handles failed PO Submissions (Updates OSP Status)
990 --                      3. Handles cancelled PO Lines (Updates OSP Line status, delete shipments)
991 --                      4. Handles deleted PO Lines  (Updates OSP Line status, delete shipments)
992 --                      5. Handles Approved POs (Books Shipment, notifies shipper?)
993 --  Pre-reqs    :
994 --  Parameters  :
995 --
996 --  Standard IN  Parameters :
997 --      p_api_version                   IN      NUMBER       Required
998 --      p_init_msg_list                 IN      VARCHAR2     Default  FND_API.G_FALSE
999 --      p_commit                        IN      VARCHAR2     Default  FND_API.G_FALSE
1000 --      p_validation_level              IN      NUMBER       Default  FND_API.G_VALID_LEVEL_FULL
1001 --      p_default                       IN      VARCHAR2     Default  FND_API.G_TRUE
1002 --      p_module_type                   IN      VARCHAR2     Default  NULL.
1003 --
1004 --  Standard OUT Parameters :
1005 --      x_return_status                 OUT     VARCHAR2               Required
1006 --      x_msg_count                     OUT     NUMBER                 Required
1007 --      x_msg_data                      OUT     VARCHAR2               Required
1008 --
1009 --  PO_Synch_All_OSPs Parameters:
1010 --      p_concurrent_flag               IN      VARCHAR2     Default  N.
1011 --        Writes debug Information to Concurrent Program's Log File if set to 'Y'
1012 --
1013 --  Version :
1014 --      Initial Version   1.0
1015 --
1016 --  End of Comments.
1017 
1018 PROCEDURE PO_Synch_All_OSPs
1019 (
1020     p_api_version           IN            NUMBER,
1021     p_init_msg_list         IN            VARCHAR2  := FND_API.G_FALSE,
1022     p_commit                IN            VARCHAR2  := FND_API.G_FALSE,
1023     p_validation_level      IN            NUMBER    := FND_API.G_VALID_LEVEL_FULL,
1024     p_default               IN            VARCHAR2  := FND_API.G_TRUE,
1025     p_module_type           IN            VARCHAR2  := NULL,
1026     p_concurrent_flag       IN            VARCHAR2  := 'N',
1027     x_return_status         OUT  NOCOPY   VARCHAR2,
1028     x_msg_count             OUT  NOCOPY   NUMBER,
1029     x_msg_data              OUT  NOCOPY   VARCHAR2) IS
1030 
1031    l_api_version            CONSTANT NUMBER := 1.0;
1032    l_api_name               CONSTANT VARCHAR2(30) := 'PO_Synch_All_OSPs';
1033    L_DEBUG_KEY              CONSTANT VARCHAR2(150) := G_LOG_PREFIX || '.PO_Synch_All_OSPs';
1034 
1035 BEGIN
1036   IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1037     FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, L_DEBUG_KEY || '.begin', 'Entering Procedure. Operating Unit = ' || MO_GLOBAL.get_current_org_id());
1038   END IF;
1039 
1040   -- No need of a Savepoint: Individual procedures commit or rollback
1041   -- within themselves.
1042   -- Standard start of API savepoint
1043   --SAVEPOINT PO_Synch_All_OSPs_pvt;
1044 
1045   -- Standard call to check for call compatibility
1046   IF NOT FND_API.Compatible_API_Call(l_api_version, p_api_version, l_api_name,
1047                                      G_PKG_NAME) THEN
1048     RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1049   END IF;
1050 
1051     -- Initialize message list if p_init_msg_list is set to TRUE
1052   IF FND_API.To_Boolean(p_init_msg_list) THEN
1053     FND_MSG_PUB.Initialize;
1054   END IF;
1055 
1056   -- Initialize API return status to success
1057   x_return_status := FND_API.G_RET_STS_SUCCESS;
1058 
1059   IF FND_API.to_boolean( p_default ) THEN
1060     -- No special default settings required in this API
1061     NULL;
1062   END IF;
1063 
1064   -- Start processing
1065   IF (p_concurrent_flag = 'Y') THEN
1066      fnd_file.put_line(fnd_file.log, 'Starting PO Synch process...');
1067   END IF;
1068 
1069   -- First make all associations (PO Header Id, PO Line Id, Status updates)
1070   ASSOCIATE_ALL_OSP_POs(p_api_version   => 1.0,
1071                         p_commit        => p_commit,  --Commit this independent of other operations
1072                         x_return_status => x_return_status,
1073                         x_msg_count     => x_msg_count,
1074                         x_msg_data      => x_msg_data);
1075   IF (p_concurrent_flag = 'Y') THEN
1076      fnd_file.put_line(fnd_file.log, 'Completed Associating OSPs with POs');
1077      fnd_file.put_line(fnd_file.log, 'Return Status = ' || x_return_status);
1078   END IF;
1079   IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1080     FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, L_DEBUG_KEY, 'Completed Associating OSPs with POs, Return Status = ' || x_return_status);
1081   END IF;
1082 
1083   /** The following calls to the Handle_Deleted_PO_Headers and Handle_Deleted_Sales_Orders
1084     * procedures were added by jaramana on March 31, 2006 to implement the ER 5074660
1085   ***/
1086   -- Handle Deleted PO Headers
1087   HANDLE_DELETED_PO_HEADERS(p_commit        => p_commit,  --Commit this independent of other operations
1088                             x_return_status => x_return_status);
1089   IF (p_concurrent_flag = 'Y') THEN
1090      fnd_file.put_line(fnd_file.log, 'Completed Handling Deleted PO Headers');
1091      fnd_file.put_line(fnd_file.log, 'Return Status = ' || x_return_status);
1092   END IF;
1093   IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1094     FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, L_DEBUG_KEY, 'Completed Handling Deleted PO Headers, Return Status = ' || x_return_status);
1095   END IF;
1096 
1097   -- Handle Deleted Sales Orders
1098   HANDLE_DELETED_SALES_ORDERS(p_commit        => p_commit,  --Commit this independent of other operations
1099                               x_return_status => x_return_status);
1100   IF (p_concurrent_flag = 'Y') THEN
1101      fnd_file.put_line(fnd_file.log, 'Completed Handling Deleted Sales Orders');
1102      fnd_file.put_line(fnd_file.log, 'Return Status = ' || x_return_status);
1103   END IF;
1104   IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1105     FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, L_DEBUG_KEY, 'Completed Handling Deleted Sales Orders, Return Status = ' || x_return_status);
1106   END IF;
1107 
1108   -- Handle Canceled POs
1109   HANDLE_CANCELLED_PO_LINES(p_commit        => p_commit,   --Commit this independent of other operations
1110                             x_return_status => x_return_status);
1111   IF (p_concurrent_flag = 'Y') THEN
1112      fnd_file.put_line(fnd_file.log, 'Completed Handling Cancelled PO Lines');
1113      fnd_file.put_line(fnd_file.log, 'Return Status = ' || x_return_status);
1114   END IF;
1115   IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1116     FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, L_DEBUG_KEY, 'Completed Handling Cancelled PO Lines, Return Status = ' || x_return_status);
1117   END IF;
1118 
1119   -- Handle Deleted PO Lines
1120   HANDLE_DELETED_PO_LINES(p_commit        => p_commit,  --Commit this independent of other operations
1121                           x_return_status => x_return_status);
1122   IF (p_concurrent_flag = 'Y') THEN
1123      fnd_file.put_line(fnd_file.log, 'Completed Handling Deleted PO Lines');
1124      fnd_file.put_line(fnd_file.log, 'Return Status = ' || x_return_status);
1125   END IF;
1126   IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1127     FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, L_DEBUG_KEY, 'Completed Handling Deleted PO Lines, Return Status = ' || x_return_status);
1128   END IF;
1129 
1130   -- Handle Approved POs
1131   HANDLE_APPROVED_POs(p_commit        => p_commit,  --Commit this independent of other operations
1132                       x_return_status => x_return_status);
1133   IF (p_concurrent_flag = 'Y') THEN
1134      fnd_file.put_line(fnd_file.log, 'Completed Handling Approved POs');
1135      fnd_file.put_line(fnd_file.log, 'Return Status = ' || x_return_status);
1136   END IF;
1137   IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1138     FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, L_DEBUG_KEY, 'Completed Handling Approved POs, Return Status = ' || x_return_status);
1139   END IF;
1140 
1141   -- Begin Changes by jaramana on January 7, 2008 for the Requisition ER 6034236
1142   IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1143     FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, L_DEBUG_KEY, 'Completed Processing Purchase Orders. About to start processing Requisitions.');
1144   END IF;
1145   IF (p_concurrent_flag = 'Y') THEN
1146      fnd_file.put_line(fnd_file.log, 'Completed PO Synch Process. About to start processing Requisitions by calling AHL_OSP_PO_REQ_PVT.PO_Synch_All_Requisitions.');
1147   END IF;
1148   AHL_OSP_PO_REQ_PVT.PO_Synch_All_Requisitions(p_api_version      => 1.0,
1149                                                p_init_msg_list    => FND_API.G_FALSE,
1150                                                p_commit           => p_commit, --Commit this independent of other operations
1151                                                p_validation_level => p_validation_level,
1152                                                p_default          => p_default,
1153                                                p_module_type      => p_module_type,
1154                                                p_concurrent_flag  => p_concurrent_flag,
1155                                                x_return_status    => x_return_status,
1156                                                x_msg_count        => x_msg_count,
1157                                                x_msg_data         => x_msg_data);
1158 
1159   IF (p_concurrent_flag = 'Y') THEN
1160      fnd_file.put_line(fnd_file.log, 'Completed processing Requisitions.');
1161      fnd_file.put_line(fnd_file.log, 'Return Status = ' || x_return_status);
1162   END IF;
1163   IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1164     FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, L_DEBUG_KEY, 'Returned from AHL_OSP_PO_REQ_PVT.PO_Synch_All_Requisitions, Return Status = ' || x_return_status);
1165   END IF;
1166   IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
1167     IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1168       FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, L_DEBUG_KEY, 'AHL_OSP_PO_REQ_PVT.PO_Synch_All_Requisitions Did not succeed');
1169     END IF;
1170     IF (x_return_status = FND_API.G_RET_STS_ERROR) THEN
1171       RAISE FND_API.G_EXC_ERROR;  -- Rollback and return error
1172     ELSE
1173       RAISE FND_API.G_EXC_UNEXPECTED_ERROR;  -- Rollback and return error
1174     END IF;
1175   END IF;
1176   IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1177     FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, L_DEBUG_KEY, 'Completed Processing Requisitions. About to check for errors.');
1178   END IF;
1179   IF (p_concurrent_flag = 'Y') THEN
1180      fnd_file.put_line(fnd_file.log, 'Completed Requisition Synch Process. Checking for errors');
1181   END IF;
1182   -- End changes by jaramana on January 7, 2008 for the Requisition ER 6034236
1183   -- Check Error Message stack.
1184   x_msg_count := FND_MSG_PUB.count_msg;
1185   IF x_msg_count > 0 THEN
1186     RAISE  FND_API.G_EXC_ERROR;  --Note that commit might already have taken place
1187   END IF;
1188 
1189   -- Standard check of p_commit
1190 -- No need to commit: Individual procedures commit or rollback
1191 -- within themselves.
1192 --  IF FND_API.TO_BOOLEAN(p_commit) THEN
1193 --      COMMIT WORK;   --Note that commit might already have taken place
1194 --  END IF;
1195 
1196   -- Standard call to get message count and if count is 1, get message info
1197   FND_MSG_PUB.Count_And_Get
1198     ( p_count => x_msg_count,
1199       p_data  => x_msg_data,
1200       p_encoded => fnd_api.g_false
1201     );
1202 
1203   IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1204     FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, L_DEBUG_KEY || '.end', 'Exiting Procedure');
1205   END IF;
1206 
1207 EXCEPTION
1208  WHEN FND_API.G_EXC_ERROR THEN
1209 -- No need to rollback: Individual procedures commit or rollback
1210 -- within themselves.
1211 --   ROLLBACK TO PO_Synch_All_OSPs_pvt;  --Note that commit might already have taken place
1212    x_return_status := FND_API.G_RET_STS_ERROR;
1213    FND_MSG_PUB.count_and_get( p_count => x_msg_count,
1214                               p_data  => x_msg_data,
1215                               p_encoded => fnd_api.g_false);
1216   IF (p_concurrent_flag = 'Y') THEN
1217      fnd_file.put_line(fnd_file.log, 'Caught Execution Exception: ' || x_msg_data);
1218   END IF;
1219   IF (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1220     FND_LOG.STRING(FND_LOG.LEVEL_UNEXPECTED, L_DEBUG_KEY, 'Caught Execution Exception: ' || x_msg_data);
1221   END IF;
1222 
1223  WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1224 -- No need to rollback: Individual procedures commit or rollback
1225 -- within themselves.
1226 --   ROLLBACK TO PO_Synch_All_OSPs_pvt;  --Note that commit might already have taken place
1227    x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1228    FND_MSG_PUB.count_and_get( p_count => x_msg_count,
1229                               p_data  => x_msg_data,
1230                               p_encoded => fnd_api.g_false);
1231   IF (p_concurrent_flag = 'Y') THEN
1232      fnd_file.put_line(fnd_file.log, 'Caught Unexpected Exception: ' || x_msg_data);
1233   END IF;
1234   IF (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1235     FND_LOG.STRING(FND_LOG.LEVEL_UNEXPECTED, L_DEBUG_KEY, 'Caught Unexpected Exception: ' || x_msg_data);
1236   END IF;
1237 
1238  WHEN OTHERS THEN
1239 -- No need to rollback: Individual procedures commit or rollback
1240 -- within themselves.
1241 --   ROLLBACK TO PO_Synch_All_OSPs_pvt;  --Note that commit might already have taken place
1242    x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1243    IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
1244      fnd_msg_pub.add_exc_msg(p_pkg_name       => G_PKG_NAME,
1245                                p_procedure_name => 'PO_Synch_All_OSPs',
1246                                p_error_text     => SUBSTR(SQLERRM,1,240));
1247    END IF;
1248    FND_MSG_PUB.count_and_get( p_count => x_msg_count,
1249                                p_data  => x_msg_data,
1250                                p_encoded => fnd_api.g_false);
1251   IF (p_concurrent_flag = 'Y') THEN
1252      fnd_file.put_line(fnd_file.log, 'Caught Unknown Exception: ' || x_msg_data);
1253   END IF;
1254   IF (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1255     FND_LOG.STRING(FND_LOG.LEVEL_UNEXPECTED, L_DEBUG_KEY, 'Caught Unknown Exception: ' || x_msg_data);
1256   END IF;
1257 
1258 END PO_Synch_All_OSPs;
1259 --------------------------------------
1260 -- End Public Procedure Definitions --
1261 --------------------------------------
1262 
1263 ----------------------------------------
1264 -- Public Function Definitions follow --
1265 ----------------------------------------
1266 
1267 ----------------------------------------
1268 -- This function determines if the specified Purchase Order is closed
1269 ----------------------------------------
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);
1279    l_cancel_flag    VARCHAR2(1);
1280 
1281 BEGIN
1282   IF (p_po_header_id IS NULL OR p_po_header_id = FND_API.G_MISS_NUM) THEN
1283     FND_MESSAGE.Set_Name('AHL', 'AHL_OSP_PO_ID_NULL');
1284     FND_MSG_PUB.ADD;
1285     IF (FND_LOG.LEVEL_ERROR >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1286       FND_LOG.MESSAGE(FND_LOG.LEVEL_ERROR, L_DEBUG_KEY, FALSE);
1287     END IF;
1288     RETURN 'N';
1289   END IF;
1290   OPEN l_get_po_cstatus_csr(p_po_header_id);
1291   FETCH l_get_po_cstatus_csr INTO l_closed_status,l_cancel_flag;
1292   IF (l_get_po_cstatus_csr%NOTFOUND) THEN
1293   --Modified by mpothuku on 16-Nov-06 for fixing the Bug 5673483
1294     FND_MESSAGE.Set_Name('AHL', 'AHL_OSP_PO_ID_INVALID');
1295     FND_MESSAGE.Set_Token('PO_ID', p_po_header_id);
1296     FND_MSG_PUB.ADD;
1297     IF (FND_LOG.LEVEL_ERROR >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1298       FND_LOG.MESSAGE(FND_LOG.LEVEL_ERROR, L_DEBUG_KEY, FALSE);
1299     END IF;
1300     --This would mean that the PO is deleted from PO forms and we may need to do a Synch
1301     CLOSE l_get_po_cstatus_csr;
1302     RETURN 'N';
1303   END IF;
1304   CLOSE l_get_po_cstatus_csr;
1305   IF ((l_closed_status = G_PO_CLOSED) OR (l_closed_status = G_PO_FINALLY_CLOSED) OR (l_cancel_flag = 'Y')) THEN
1306     RETURN 'Y';
1307   ELSE
1308     RETURN 'N';
1309   END IF;
1310 END Is_PO_Closed;
1311 
1312 ----------------------------------------
1313 -- This function determines if the specified OSP Order has any new PO Line
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
1323                                 WHERE OSP_ORDER_ID = p_osp_order_id);
1324   l_po_line_id NUMBER;
1325 
1326 BEGIN
1327   OPEN l_get_new_po_line_csr(p_osp_order_id);
1328   FETCH l_get_new_po_line_csr INTO l_po_line_id;
1329   IF (l_get_new_po_line_csr%NOTFOUND) THEN
1330     -- No new PO Line
1331     CLOSE l_get_new_po_line_csr;
1332     RETURN 'N';
1333   ELSE
1334     -- Has new PO Line(s)
1335     CLOSE l_get_new_po_line_csr;
1336     RETURN 'Y';
1337   END IF;
1338 END Has_New_PO_Line;
1339 
1340 -------------------------------------
1341 -- End Public Function Definitions --
1342 -------------------------------------
1343 
1344 ----------------------------------------
1345 -- Local Procedure Definitions follow --
1346 ----------------------------------------
1347 
1348 ----------------------------------------
1349 -- This Procedure validates the OSP Order for PO Header Creation
1350 ----------------------------------------
1351 PROCEDURE Validate_PO_Header(
1352    p_po_header_rec IN PO_Header_Rec_Type) IS
1353 
1354   CURSOR l_validate_osp_csr(p_osp_order_id IN NUMBER) IS
1355     SELECT 'x' FROM AHL_OSP_ORDERS_B
1356     WHERE OSP_ORDER_ID = p_osp_order_id
1357 -- The following condition commented out by jaramana on request of jeli
1358 -- on May 27, 2005 so that status update can be done later.
1359       -- AND STATUS_CODE = G_OSP_SUBMITTED_STATUS
1360       FOR UPDATE OF PO_BATCH_ID, PO_REQUEST_ID;  -- Lock Row
1361 
1362   CURSOR l_validate_supplier_csr(p_supplier_id IN NUMBER) IS
1363     SELECT 'x' FROM PO_VENDORS_VIEW
1364     WHERE VENDOR_ID = p_supplier_id
1365       AND ENABLED_FLAG = G_YES_FLAG
1366       AND NVL(VENDOR_START_DATE_ACTIVE, SYSDATE - 1) <= SYSDATE
1367       AND NVL(VENDOR_END_DATE_ACTIVE, SYSDATE + 1) > SYSDATE;
1368 
1369   CURSOR l_validate_supp_site_csr(p_supp_site_id IN NUMBER,
1370                                   p_supp_id      IN NUMBER) IS
1371     SELECT 'x' FROM PO_VENDOR_SITES
1372     WHERE VENDOR_SITE_ID = p_supp_site_id
1373     AND   VENDOR_ID = p_supp_id
1374     AND   NVL(INACTIVE_DATE, SYSDATE + 1) > SYSDATE
1375     AND   NVL(RFQ_ONLY_SITE_FLAG, G_NO_FLAG) = G_NO_FLAG
1376     AND   PURCHASING_SITE_FLAG = G_YES_FLAG;
1377     -- NOTE: Organization filtering is done by the PO_VENDOR_SITES view itself
1378 
1379   -- Added by jaramana on May 27, 2005 for Inventory Service Order
1380   CURSOR l_validate_vendor_contact_csr(p_vendor_contact_id IN NUMBER,
1381                                        p_supp_site_id      IN NUMBER) IS
1382     SELECT 'x' FROM PO_VENDOR_CONTACTS
1383     WHERE VENDOR_CONTACT_ID = p_vendor_contact_id
1384     AND   VENDOR_SITE_ID = p_supp_site_id;
1385     -- May have to check INACTIVE_DATE > SYSDATE also?
1386 
1387   CURSOR l_validate_buyer_csr(p_buyer_id IN NUMBER) IS
1388     SELECT 'x' FROM PO_AGENTS_NAME_V
1389     WHERE BUYER_ID = p_buyer_id;
1390     -- NOTE: Effective Date filtering is done by the PO_AGENTS_NAME_V view itself
1391 
1392   l_dummy       VARCHAR2(1);
1393   l_temp_count  NUMBER;
1394   l_org_id      NUMBER := NULL;
1395   L_DEBUG_KEY   CONSTANT VARCHAR2(150) := G_LOG_PREFIX || '.Validate_PO_Header';
1396 
1397 BEGIN
1398 
1399   IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1400     FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, L_DEBUG_KEY || '.begin', 'Entering Procedure');
1401   END IF;
1402 
1403   -- OSP Order Id
1404   IF (p_po_header_rec.OSP_ORDER_ID IS NULL OR p_po_header_rec.OSP_ORDER_ID = FND_API.G_MISS_NUM) THEN
1405     FND_MESSAGE.Set_Name('AHL', 'AHL_OSP_OSP_ID_NULL');
1406     FND_MSG_PUB.ADD;
1407     IF (FND_LOG.LEVEL_ERROR >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1408       FND_LOG.MESSAGE(FND_LOG.LEVEL_ERROR, L_DEBUG_KEY, FALSE);
1409     END IF;
1410   ELSE
1411     OPEN l_validate_osp_csr(p_po_header_rec.OSP_ORDER_ID);
1412     FETCH l_validate_osp_csr INTO l_dummy;
1413     IF (l_validate_osp_csr%NOTFOUND) THEN
1414       FND_MESSAGE.Set_Name('AHL', 'AHL_OSP_OSP_ID_INVALID');
1415       FND_MESSAGE.Set_Token('OSP_ID', p_po_header_rec.OSP_ORDER_ID);
1416       FND_MSG_PUB.ADD;
1417       IF (FND_LOG.LEVEL_ERROR >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1418         FND_LOG.MESSAGE(FND_LOG.LEVEL_ERROR, L_DEBUG_KEY, FALSE);
1419       END IF;
1420     END IF;
1421     CLOSE l_validate_osp_csr;
1422   END IF;
1423 
1424   -- Supplier
1425   IF (p_po_header_rec.VENDOR_ID IS NULL OR p_po_header_rec.VENDOR_ID = FND_API.G_MISS_NUM) THEN
1426     FND_MESSAGE.Set_Name('AHL', 'AHL_OSP_SUPPLIER_ID_NULL');
1427     FND_MSG_PUB.ADD;
1428     IF (FND_LOG.LEVEL_ERROR >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1429       FND_LOG.MESSAGE(FND_LOG.LEVEL_ERROR, L_DEBUG_KEY, FALSE);
1430     END IF;
1431   ELSE
1432     OPEN l_validate_supplier_csr(p_po_header_rec.VENDOR_ID);
1433     FETCH l_validate_supplier_csr INTO l_dummy;
1434     IF (l_validate_supplier_csr%NOTFOUND) THEN
1435       FND_MESSAGE.Set_Name('AHL', 'AHL_OSP_SUPP_INVALID');
1436       FND_MESSAGE.Set_Token('SUPP_ID', p_po_header_rec.VENDOR_ID);
1437       FND_MSG_PUB.ADD;
1438       IF (FND_LOG.LEVEL_ERROR >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1439         FND_LOG.MESSAGE(FND_LOG.LEVEL_ERROR, L_DEBUG_KEY, FALSE);
1440       END IF;
1441     END IF;
1442     CLOSE l_validate_supplier_csr;
1443   END IF;
1444 
1445   -- Supplier Site
1446   IF (p_po_header_rec.VENDOR_SITE_ID IS NULL OR p_po_header_rec.VENDOR_SITE_ID = FND_API.G_MISS_NUM) THEN
1447     FND_MESSAGE.Set_Name('AHL', 'AHL_OSP_SSITE_ID_NULL');
1448     FND_MSG_PUB.ADD;
1449     IF (FND_LOG.LEVEL_ERROR >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1450       FND_LOG.MESSAGE(FND_LOG.LEVEL_ERROR, L_DEBUG_KEY, FALSE);
1451     END IF;
1452   ELSE
1453     OPEN l_validate_supp_site_csr(p_po_header_rec.VENDOR_SITE_ID, p_po_header_rec.VENDOR_ID);
1454     FETCH l_validate_supp_site_csr INTO l_dummy;
1455     IF (l_validate_supp_site_csr%NOTFOUND) THEN
1456       FND_MESSAGE.Set_Name('AHL', 'AHL_OSP_SSITE_INVALID');
1457       FND_MESSAGE.Set_Token('SS_ID', p_po_header_rec.VENDOR_SITE_ID);
1458       FND_MSG_PUB.ADD;
1459       IF (FND_LOG.LEVEL_ERROR >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1460         FND_LOG.MESSAGE(FND_LOG.LEVEL_ERROR, L_DEBUG_KEY, FALSE);
1461       END IF;
1462     END IF;
1463     CLOSE l_validate_supp_site_csr;
1464   END IF;
1465 
1466   -- Added by jaramana on May 27, 2005 for Inventory Service Order
1467   -- Vendor Contact (Optional)
1468   IF (p_po_header_rec.VENDOR_CONTACT_ID IS NOT NULL AND p_po_header_rec.VENDOR_CONTACT_ID <> FND_API.G_MISS_NUM) THEN
1469     OPEN l_validate_vendor_contact_csr(p_po_header_rec.VENDOR_CONTACT_ID, p_po_header_rec.VENDOR_SITE_ID);
1470     FETCH l_validate_vendor_contact_csr INTO l_dummy;
1471     IF (l_validate_vendor_contact_csr%NOTFOUND) THEN
1472       FND_MESSAGE.Set_Name('AHL', 'AHL_OSP_VCONTACT_INVALID');
1473       FND_MESSAGE.Set_Token('V_CONTACT_ID', p_po_header_rec.VENDOR_CONTACT_ID);
1474       FND_MSG_PUB.ADD;
1475       IF (FND_LOG.LEVEL_ERROR >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1476         FND_LOG.MESSAGE(FND_LOG.LEVEL_ERROR, L_DEBUG_KEY, FALSE);
1477       END IF;
1478     END IF;
1479     CLOSE l_validate_vendor_contact_csr;
1480   END IF;
1481   -- End Change for Inventory Service Order
1482 
1483   -- Buyer
1484   IF (p_po_header_rec.BUYER_ID IS NULL OR p_po_header_rec.BUYER_ID = FND_API.G_MISS_NUM) THEN
1485     FND_MESSAGE.Set_Name('AHL', 'AHL_OSP_BUYER_ID_NULL');
1486     FND_MSG_PUB.ADD;
1487     IF (FND_LOG.LEVEL_ERROR >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1488       FND_LOG.MESSAGE(FND_LOG.LEVEL_ERROR, L_DEBUG_KEY, FALSE);
1489     END IF;
1490   ELSE
1491     OPEN l_validate_buyer_csr(p_po_header_rec.BUYER_ID);
1492     FETCH l_validate_buyer_csr INTO l_dummy;
1493     IF (l_validate_buyer_csr%NOTFOUND) THEN
1494       FND_MESSAGE.Set_Name('AHL', 'AHL_OSP_BUYER_INVALID');
1495       FND_MESSAGE.Set_Token('BUYER_ID', p_po_header_rec.BUYER_ID);
1496       FND_MSG_PUB.ADD;
1497       IF (FND_LOG.LEVEL_ERROR >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1498         FND_LOG.MESSAGE(FND_LOG.LEVEL_ERROR, L_DEBUG_KEY, FALSE);
1499       END IF;
1500     END IF;
1501     CLOSE l_validate_buyer_csr;
1502   END IF;
1503 
1504   IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1505     FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, L_DEBUG_KEY || '.end', 'Exiting Procedure');
1506   END IF;
1507 
1508 END Validate_PO_Header;
1509 
1510 ----------------------------------------
1511 -- This Procedure validates the PO Lines
1512 ----------------------------------------
1513 PROCEDURE Validate_PO_Lines(
1514    p_po_line_tbl  IN PO_Line_Tbl_Type,
1515    p_osp_order_id IN NUMBER) IS
1516 
1517   CURSOR l_validate_item_csr(p_item_id IN NUMBER,
1518                              p_org_id  IN NUMBER) IS
1519     SELECT 'x' FROM MTL_SYSTEM_ITEMS_KFV
1520     WHERE INVENTORY_ITEM_ID = p_item_id
1521       AND ENABLED_FLAG = G_YES_FLAG
1522       AND PURCHASING_ENABLED_FLAG = G_YES_FLAG
1523       AND INVENTORY_ITEM_FLAG = G_NO_FLAG -- No Physical Items
1524       AND NVL(START_DATE_ACTIVE, SYSDATE - 1) <= SYSDATE
1525       AND NVL(END_DATE_ACTIVE, SYSDATE + 1) > SYSDATE
1526       AND ORGANIZATION_ID = p_org_id
1527       AND NVL(OUTSIDE_OPERATION_FLAG, G_NO_FLAG) = G_NO_FLAG;
1528 
1529   CURSOR l_validate_line_type_csr(p_line_type_id IN NUMBER) IS
1530     SELECT 'x' FROM PO_LINE_TYPES
1531     WHERE ORDER_TYPE_LOOKUP_CODE = G_PO_LINE_TYPE_QUANTITY
1532     AND NVL(OUTSIDE_OPERATION_FLAG, G_NO_FLAG) = G_NO_FLAG
1533     AND LINE_TYPE_ID = p_line_type_id;
1534 
1535   L_DEBUG_KEY CONSTANT VARCHAR2(150) := G_LOG_PREFIX || '.Validate_PO_Lines';
1536   l_org_id    NUMBER := NULL;
1537   l_dummy     VARCHAR2(1);
1538 
1539 BEGIN
1540   IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1541     FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, L_DEBUG_KEY || '.begin', 'Entering Procedure');
1542   END IF;
1543 
1544   -- Get the current Org
1545   l_org_id := MO_GLOBAL.get_current_org_id();
1546 
1547   IF (l_org_id IS NULL) THEN
1548     FND_MESSAGE.Set_Name('AHL', 'AHL_OSP_ORG_NOT_SET');
1549     FND_MSG_PUB.ADD;
1550     IF (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1551       FND_LOG.MESSAGE(FND_LOG.LEVEL_UNEXPECTED, L_DEBUG_KEY, FALSE);
1552     END IF;
1553     RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1554   END IF;
1555 
1556   --dbms_output.put_line('p_po_line_tbl.COUNT = ' || p_po_line_tbl.COUNT);
1557   -- Non zero count
1558   IF (p_po_line_tbl.COUNT = 0) THEN
1559     FND_MESSAGE.Set_Name('AHL', 'AHL_OSP_NO_PO_LINES');
1560     FND_MSG_PUB.ADD;
1561     IF (FND_LOG.LEVEL_ERROR >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1562       FND_LOG.MESSAGE(FND_LOG.LEVEL_ERROR, L_DEBUG_KEY, FALSE);
1563     END IF;
1564     RETURN;  -- Cannot do any further validation
1565   END IF;
1566 
1567   FOR i IN p_po_line_tbl.FIRST..p_po_line_tbl.LAST LOOP
1568     -- Item
1569     IF (p_po_line_tbl(i).ITEM_ID IS NOT NULL AND p_po_line_tbl(i).ITEM_ID <> FND_API.G_MISS_NUM) THEN
1570       -- Non One-time Item
1571       -- Changed by jaramana on May 26, 2005 to fix Bug 4393374
1572       -- OPEN l_validate_item_csr(p_po_line_tbl(i).ITEM_ID, l_org_id);
1573       OPEN l_validate_item_csr(p_po_line_tbl(i).ITEM_ID, p_po_line_tbl(i).SHIP_TO_ORG_ID);
1574       FETCH l_validate_item_csr INTO l_dummy;
1575       IF (l_validate_item_csr%NOTFOUND) THEN
1576         FND_MESSAGE.Set_Name('AHL', 'AHL_OSP_ITEM_INVALID');
1577         FND_MESSAGE.Set_Token('ITEM', p_po_line_tbl(i).ITEM_ID);
1578         FND_MSG_PUB.ADD;
1579         IF (FND_LOG.LEVEL_ERROR >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1580           FND_LOG.MESSAGE(FND_LOG.LEVEL_ERROR, L_DEBUG_KEY, FALSE);
1581         END IF;
1582       END IF;
1583       CLOSE l_validate_item_csr;
1584     ELSE
1585       -- One-time Item: Description is mandatory
1586       IF (TRIM(p_po_line_tbl(i).ITEM_DESCRIPTION) IS NULL OR p_po_line_tbl(i).ITEM_DESCRIPTION = FND_API.G_MISS_CHAR) THEN
1587         FND_MESSAGE.Set_Name('AHL', 'AHL_OSP_IDESC_NULL');
1588         FND_MSG_PUB.ADD;
1589         IF (FND_LOG.LEVEL_ERROR >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1590           FND_LOG.MESSAGE(FND_LOG.LEVEL_ERROR, L_DEBUG_KEY, FALSE);
1591         END IF;
1592       END IF;
1593       -- One-time Item: UOM is mandatory
1594       IF (TRIM(p_po_line_tbl(i).UOM_CODE) IS NULL OR p_po_line_tbl(i).UOM_CODE = FND_API.G_MISS_CHAR) THEN
1595         FND_MESSAGE.Set_Name('AHL', 'AHL_OSP_UOM_CODE_NULL');
1596         FND_MSG_PUB.ADD;
1597         IF (FND_LOG.LEVEL_ERROR >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1598           FND_LOG.MESSAGE(FND_LOG.LEVEL_ERROR, L_DEBUG_KEY, FALSE);
1599         END IF;
1600       END IF;
1601     END IF;
1602 
1603     -- Quantity
1604     IF (p_po_line_tbl(i).QUANTITY IS NULL OR p_po_line_tbl(i).QUANTITY = FND_API.G_MISS_NUM) THEN
1605       FND_MESSAGE.Set_Name('AHL', 'AHL_OSP_QUANTITY_NULL');
1606       FND_MSG_PUB.ADD;
1607       IF (FND_LOG.LEVEL_ERROR >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1608         FND_LOG.MESSAGE(FND_LOG.LEVEL_ERROR, L_DEBUG_KEY, FALSE);
1609       END IF;
1610     ELSE
1611       IF (p_po_line_tbl(i).QUANTITY <= 0) THEN
1612         FND_MESSAGE.Set_Name('AHL', 'AHL_OSP_QUANTITY_INVALID');
1613         FND_MESSAGE.Set_Token('QUANTITY', p_po_line_tbl(i).QUANTITY);
1614         FND_MSG_PUB.ADD;
1615         IF (FND_LOG.LEVEL_ERROR >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1616           FND_LOG.MESSAGE(FND_LOG.LEVEL_ERROR, L_DEBUG_KEY, FALSE);
1617         END IF;
1618       END IF;
1619     END IF;
1620 
1621     -- Need By Date
1622     IF (p_po_line_tbl(i).NEED_BY_DATE IS NULL OR p_po_line_tbl(i).NEED_BY_DATE = FND_API.G_MISS_DATE) THEN
1623       FND_MESSAGE.Set_Name('AHL', 'AHL_OSP_NEED_BY_DATE_NULL');
1624       FND_MSG_PUB.ADD;
1625       IF (FND_LOG.LEVEL_ERROR >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1626         FND_LOG.MESSAGE(FND_LOG.LEVEL_ERROR, L_DEBUG_KEY, FALSE);
1627       END IF;
1628     ELSE
1629       IF (TRUNC(p_po_line_tbl(i).NEED_BY_DATE) < TRUNC(SYSDATE)) THEN
1630         FND_MESSAGE.Set_Name('AHL', 'AHL_OSP_NBDATE_INVALID');
1631         FND_MESSAGE.Set_Token('NBDATE', p_po_line_tbl(i).NEED_BY_DATE);
1632         FND_MSG_PUB.ADD;
1633         IF (FND_LOG.LEVEL_ERROR >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1634           FND_LOG.MESSAGE(FND_LOG.LEVEL_ERROR, L_DEBUG_KEY, FALSE);
1635         END IF;
1636       END IF;
1637     END IF;
1638 
1639     -- Ship To Organization
1640     IF (p_po_line_tbl(i).SHIP_TO_ORG_ID IS NULL OR p_po_line_tbl(i).SHIP_TO_ORG_ID = FND_API.G_MISS_NUM) THEN
1641       FND_MESSAGE.Set_Name('AHL', 'AHL_OSP_SHIP_TO_ORG_NULL');
1642       FND_MSG_PUB.ADD;
1643       IF (FND_LOG.LEVEL_ERROR >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1644         FND_LOG.MESSAGE(FND_LOG.LEVEL_ERROR, L_DEBUG_KEY, FALSE);
1645       END IF;
1646     END IF;
1647 
1648     -- Commented out by jaramana on May 27, 2005 for Inventory Service Orders.
1649     -- For Inventory based OSP Lines, it will not be possible to get this location.
1650     -- For workorder based lines, we get this from the workorder Department.
1651     -- Need to check if this field is mandatory for PO creation.
1652     -- Ship To Location
1653 /***
1654     IF (p_po_line_tbl(i).SHIP_TO_LOC_ID IS NULL OR p_po_line_tbl(i).SHIP_TO_LOC_ID = FND_API.G_MISS_NUM) THEN
1655       FND_MESSAGE.Set_Name('AHL', 'AHL_OSP_SHIP_TO_LOC_NULL');
1656       FND_MSG_PUB.ADD;
1657       IF (FND_LOG.LEVEL_ERROR >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1658         FND_LOG.MESSAGE(FND_LOG.LEVEL_ERROR, L_DEBUG_KEY, FALSE);
1659       END IF;
1660     END IF;
1661 ***/
1662     -- Line Type
1663     IF (p_po_line_tbl(i).PO_LINE_TYPE_ID IS NULL OR p_po_line_tbl(i).PO_LINE_TYPE_ID = FND_API.G_MISS_NUM) THEN
1664       FND_MESSAGE.Set_Name('AHL', 'AHL_OSP_LN_TYPE_ID_NULL');
1665       FND_MSG_PUB.ADD;
1666       IF (FND_LOG.LEVEL_ERROR >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1667         FND_LOG.MESSAGE(FND_LOG.LEVEL_ERROR, L_DEBUG_KEY, FALSE);
1668       END IF;
1669     ELSE
1670       OPEN l_validate_line_type_csr(p_po_line_tbl(i).PO_LINE_TYPE_ID);
1671       FETCH l_validate_line_type_csr INTO l_dummy;
1672       IF (l_validate_line_type_csr%NOTFOUND) THEN
1673         FND_MESSAGE.Set_Name('AHL', 'AHL_OSP_LN_TYPE_ID_INVALID');
1674         FND_MESSAGE.Set_Token('LINE_TYPE_ID', p_po_line_tbl(i).PO_LINE_TYPE_ID);
1675         FND_MSG_PUB.ADD;
1676         IF (FND_LOG.LEVEL_ERROR >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1677           FND_LOG.MESSAGE(FND_LOG.LEVEL_ERROR, L_DEBUG_KEY, FALSE);
1678         END IF;
1679       END IF;
1680       CLOSE l_validate_line_type_csr;
1681     END IF;
1682 
1683   END LOOP;
1684   IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1685     FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, L_DEBUG_KEY || '.end', 'Exiting Procedure');
1686   END IF;
1687 END Validate_PO_Lines;
1688 
1689 ----------------------------------------
1690 -- This Procedure inserts a record into the PO_HEADERS_INTERFACE table
1691 ----------------------------------------
1692 PROCEDURE Insert_PO_Header(
1693    p_po_header_rec  IN  PO_Header_Rec_Type,
1694    x_intf_header_id OUT NOCOPY NUMBER,
1695    x_batch_id       OUT NOCOPY NUMBER) IS
1696 
1697   CURSOR l_get_osp_order_dtls_csr(p_osp_order_id IN NUMBER) IS
1698     SELECT OSP_ORDER_NUMBER, DESCRIPTION FROM AHL_OSP_ORDERS_VL
1699     WHERE OSP_ORDER_ID = p_osp_order_id;
1700 
1701   l_description            VARCHAR2(256);
1702   l_OSP_description        VARCHAR2(256) := NULL;
1703   l_interface_src_code     VARCHAR2(30);
1704   l_intf_hdr_id            NUMBER;
1705   l_batch_id               NUMBER;
1706   l_currency_code          VARCHAR2(15) := NULL;
1707   l_temp_n                 NUMBER := 0;
1708   l_temp_v                 VARCHAR2(240) := NULL;
1709   l_curr_org_id            NUMBER;
1710   l_manual_po_number       PO_HEADERS_INTERFACE.DOCUMENT_NUM%TYPE := NULL;
1711 
1712   L_DEBUG_KEY CONSTANT VARCHAR2(150) := G_LOG_PREFIX || '.Insert_PO_Header';
1713 
1714 BEGIN
1715   IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1716     FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, L_DEBUG_KEY || '.begin', 'Entering Procedure');
1717   END IF;
1718   -- Get Batch Id
1719 /*
1720   SELECT NVL(MAX(batch_id), 0) + 1 INTO l_batch_id FROM po_headers_interface;
1721 */
1722    -- Changes made by jaramana on December 19, 2005
1723    -- to improve the performace of this SQL by removing the Full Index Scan
1724    -- Since batch_id is optional, no need to get it using the max value.
1725    -- Instead, hard code it to the OSP Order Id
1726    l_batch_id := p_po_header_rec.OSP_ORDER_ID;
1727 
1728   -- Generate PO Header Id
1729   SELECT PO_HEADERS_INTERFACE_S.NEXTVAL INTO l_intf_hdr_id FROM sys.dual;
1730 
1731   -- Description
1732   OPEN l_get_osp_order_dtls_csr(p_po_header_rec.OSP_ORDER_ID);
1733   FETCH l_get_osp_order_dtls_csr INTO l_temp_n, l_OSP_description;
1734   CLOSE l_get_osp_order_dtls_csr;
1735   l_description := G_AHL_OSP_PREFIX || l_temp_n;
1736   IF(l_OSP_description IS NOT NULL) THEN
1737     l_description := l_description || ' - ' || SUBSTR(l_OSP_description, 1, 200);
1738   END IF;
1739 
1740   -- Get currency if required
1741   -- If set either at Site or Supplier Level, no need to set.
1742   -- Else retrieve from Set-Of-Books and set explicitly
1743   BEGIN
1744     -- Check if currency is available at vendor site level
1745     SELECT invoice_currency_code INTO l_temp_v FROM po_vendor_sites
1746     WHERE vendor_site_id = p_po_header_rec.vendor_site_id AND
1747           vendor_id = p_po_header_rec.vendor_id;
1748     IF(l_temp_v IS NULL) THEN
1749       IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1750         FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, L_DEBUG_KEY, 'Could not get currency for Supplier ' || p_po_header_rec.vendor_id || ' Trying at Site ' || p_po_header_rec.vendor_site_id);
1751       END IF;
1752       -- If not check if available at vendor level
1753       SELECT invoice_currency_code INTO l_temp_v FROM po_vendors
1754       WHERE vendor_id = p_po_header_rec.vendor_id;
1755       IF(l_temp_v IS NULL) THEN
1756         IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1757           FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, L_DEBUG_KEY, 'Could not get currency for Supplier ' || p_po_header_rec.vendor_id || ' and Site ' || p_po_header_rec.vendor_site_id || ', Trying from set of Books.');
1758         END IF;
1759         -- If not, get currency from set_of_books and set l_currency_code
1760         SELECT GSB.currency_code INTO l_currency_code
1761           FROM   FINANCIALS_SYSTEM_PARAMETERS FSP, GL_SETS_OF_BOOKS GSB
1762           WHERE  FSP.set_of_books_id = GSB.set_of_books_id;
1763         IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1764           FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, L_DEBUG_KEY, 'Got currency from Set Of Books: ' || l_currency_code);
1765         END IF;
1766       END IF;
1767     END IF;
1768   EXCEPTION
1769     WHEN OTHERS THEN
1770       FND_MESSAGE.Set_Name('AHL', 'AHL_OSP_CURRENCY_NOT_SET');
1771       FND_MSG_PUB.ADD;
1772       IF (FND_LOG.LEVEL_ERROR >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1773         FND_LOG.MESSAGE(FND_LOG.LEVEL_ERROR, L_DEBUG_KEY, FALSE);
1774       END IF;
1775       RAISE;
1776   END;
1777 
1778   -- Added by jaramana on Sep 9, 2005 for MOAC Uptake
1779   l_curr_org_id := MO_GLOBAL.get_current_org_id();
1780 
1781   -- SATHAPLI::Bug 8583364, 21-Aug-2009, call create_manual_PO_Number to get the manual PO number
1782   l_manual_po_number := create_manual_PO_Number(l_curr_org_id, p_po_header_rec.OSP_ORDER_ID);
1783 
1784   -- Insert row into PO_HEADERS_INTERFACE
1785   INSERT INTO PO_HEADERS_INTERFACE (
1786     INTERFACE_HEADER_ID,
1787     BATCH_ID,
1788     INTERFACE_SOURCE_CODE,
1789     PROCESS_CODE,
1790     ACTION,
1791     DOCUMENT_TYPE_CODE,
1792     CURRENCY_CODE,
1793     AGENT_ID,
1794     VENDOR_ID,
1795     VENDOR_SITE_ID,
1796     VENDOR_CONTACT_ID, -- Added by jaramana on May 27, 2005 for Inventory Service Orders
1797     COMMENTS,
1798     PROGRAM_ID,
1799     PROGRAM_APPLICATION_ID,
1800     REFERENCE_NUM,
1801     DOCUMENT_NUM,     -- SATHAPLI::Bug 8583364, 21-Aug-2009, l_manual_po_number to be passed - either NULL or created
1802     ORG_ID            -- Added by jaramana on Sep 9, 2005 for MOAC Uptake
1803   ) VALUES (
1804     l_intf_hdr_id,
1805     l_batch_id,
1806     AHL_GLOBAL.AHL_APP_SHORT_NAME,   -- INTERFACE_SOURCE_CODE = 'AHL'
1807     G_PROCESS_CODE,     -- 'PENDING'
1808     G_ACTION_CODE,      -- 'ORIGINAL'
1809     G_DOC_TYPE_CODE,    -- 'STANDARD'
1810     l_currency_code,
1811     p_po_header_rec.BUYER_ID,
1812     p_po_header_rec.VENDOR_ID,
1813     p_po_header_rec.VENDOR_SITE_ID,
1814     p_po_header_rec.VENDOR_CONTACT_ID, -- Added by jaramana on May 27, 2005 for Inventory Service Orders
1815     l_description,
1816     AHL_GLOBAL.AHL_OSP_PROGRAM_ID,
1817     AHL_GLOBAL.AHL_APPLICATION_ID,
1818     p_po_header_rec.OSP_ORDER_ID,
1819     l_manual_po_number, -- SATHAPLI::Bug 8583364, 21-Aug-2009, l_manual_po_number to be passed - either NULL or created
1820     l_curr_org_id -- Added by jaramana on Sep 9, 2005 for MOAC Uptake
1821   );
1822 
1823   -- Set Output parameters
1824   x_intf_header_id := l_intf_hdr_id;
1825   x_batch_id := l_batch_id;
1826 
1827   IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1828     FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, L_DEBUG_KEY || '.end', 'Exiting Procedure');
1829   END IF;
1830 
1831 END Insert_PO_Header;
1832 
1833 ----------------------------------------
1834 -- This Procedure inserts a record into the PO_LINES_INTERFACE table
1835 ----------------------------------------
1836 PROCEDURE Insert_PO_Lines(
1837    p_po_line_tbl  IN  PO_Line_Tbl_Type,
1838    p_intf_header_id IN NUMBER) IS
1839 
1840 -- Commented out by jaramana on June 22, 2005
1841 -- Calling the new utility method Get_Item_Price instead
1842 /*
1843   CURSOR l_chk_price_csr(p_item_id IN NUMBER,
1844                          p_org_id  IN NUMBER) IS
1845     SELECT 'x' FROM MTL_SYSTEM_ITEMS_KFV
1846     WHERE INVENTORY_ITEM_ID = p_item_id
1847       AND ORGANIZATION_ID = p_org_id
1848       AND LIST_PRICE_PER_UNIT IS NOT NULL;
1849 */
1850 
1851   -- Added by mpothuku on 10-oct-2007 to fix bug 6431740
1852   CURSOR get_prj_task_comp_date_csr (c_task_id IN NUMBER) IS
1853     SELECT COMPLETION_DATE from pa_tasks
1854      where task_id = c_task_id;
1855   l_task_completion_date DATE;
1856 
1857   --Added by mpothuku on 12-oct-2007 as until the ER 5758813 is implemented, the visit task dates will not be propagated to projects.
1858   CURSOR get_vst_task_comp_date_csr (c_osp_line_id IN NUMBER) IS
1859     SELECT vtsk.end_date_time
1860       from ahl_visit_tasks_b vtsk,
1861            ahl_osp_order_lines ospl,
1862            ahl_workorders wo
1863      where ospl.osp_order_line_id = c_osp_line_id
1864        and ospl.workorder_id = wo.workorder_id
1865        and wo.visit_task_id = vtsk.visit_task_id;
1866   l_vst_task_completion_date DATE;
1867 
1868   l_expenditure_item_type pa_expenditure_types.expenditure_type%type;
1869 
1870   l_org_id           NUMBER := NULL;
1871   l_price            NUMBER := NULL;
1872   l_category         VARCHAR2(30) := NULL;
1873   l_dummy            VARCHAR2(1);
1874   l_line_num         NUMBER := 0;
1875   l_ship_to_org_id   NUMBER := NULL;
1876   l_ship_to_loc_id   NUMBER := NULL;
1877 
1878   -- Added by jaramana on Nov 28, 2005 for ER 4736326
1879   -- To create distributions automatically
1880   l_intf_line_id     NUMBER;
1881   --Added by mpothuku on 21-Aug-2007 to fix the Bug 6436184
1882   l_charge_acct_id   NUMBER := NULL;
1883 
1884   L_DEBUG_KEY CONSTANT VARCHAR2(150) := G_LOG_PREFIX || '.Insert_PO_Lines';
1885 
1886 BEGIN
1887 
1888   IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1889     FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, L_DEBUG_KEY || '.begin', 'Entering Procedure');
1890   END IF;
1891 
1892   -- Get the current Org to get Item Price
1893   l_org_id := MO_GLOBAL.get_current_org_id();
1894 
1895   FOR i IN p_po_line_tbl.FIRST..p_po_line_tbl.LAST LOOP
1896     IF (p_po_line_tbl(i).ITEM_ID IS NULL) THEN
1897       -- One time items defaulting
1898       l_price := G_DEFAULT_PRICE;
1899       l_category := G_DEFAULT_CATEGORY;
1900     ELSE
1901       l_category := null;  -- Purchasing defaults the category from the item
1902       -- Changed by jaramana on June 22, 2005
1903       l_price := Get_Item_Price(p_po_line_tbl(i).OSP_LINE_ID);
1904     END IF;
1905 
1906     -- Added by jaramana on Nov 28, 2005 for ER 4736326
1907     -- Generate PO Line Interface Id
1908     SELECT PO_LINES_INTERFACE_S.NEXTVAL INTO l_intf_line_id FROM sys.dual;
1909     -- Insert row into PO_LINES_INTERFACE
1910     INSERT INTO PO_LINES_INTERFACE (
1911       INTERFACE_LINE_ID,
1912       INTERFACE_HEADER_ID,
1913       LINE_NUM,
1914       LINE_TYPE_ID,
1915       ITEM_ID,
1916       ITEM_DESCRIPTION,
1917       CATEGORY,
1918       UOM_CODE,
1919       UNIT_PRICE,
1920       QUANTITY,
1921       NEED_BY_DATE,
1922       LINE_REFERENCE_NUM,
1923       SHIP_TO_ORGANIZATION_ID,
1924       SHIP_TO_LOCATION_ID,
1925       PROGRAM_ID,
1926       PROGRAM_APPLICATION_ID
1927     ) VALUES (
1928       l_intf_line_id,
1929       p_intf_header_id,
1930       p_po_line_tbl(i).LINE_NUMBER,
1931       p_po_line_tbl(i).PO_LINE_TYPE_ID,
1932       p_po_line_tbl(i).ITEM_ID,
1933       p_po_line_tbl(i).ITEM_DESCRIPTION,
1934       l_category,
1935       p_po_line_tbl(i).UOM_CODE,
1936       l_price,
1937       p_po_line_tbl(i).QUANTITY,
1938       p_po_line_tbl(i).NEED_BY_DATE,
1939       p_po_line_tbl(i).OSP_LINE_ID,
1940       p_po_line_tbl(i).SHIP_TO_ORG_ID,
1941       p_po_line_tbl(i).SHIP_TO_LOC_ID,
1942       AHL_GLOBAL.AHL_OSP_PROGRAM_ID,
1943       AHL_GLOBAL.AHL_APPLICATION_ID
1944     );
1945     -- Added by jaramana on Jan 5, 2006 for ER 4736326
1946     -- Check the profile OSP Default PO Distribution Creation to see if the Distribution is to be created
1947     IF (NVL(FND_PROFILE.VALUE('AHL_OSP_DEF_PO_DIST'), 'N') = 'Y') THEN
1948       -- Added by mpothuku on 10-oct-2007 to fix bug 6431740
1949       -- Insert row into PO_DISTRIBUTIONS_INTERFACE to create a distribution
1950       IF p_po_line_tbl(i).task_id IS NOT NULL THEN
1951         OPEN get_prj_task_comp_date_csr(p_po_line_tbl(i).task_id);
1952         FETCH get_prj_task_comp_date_csr INTO l_task_completion_date;
1953         CLOSE get_prj_task_comp_date_csr;
1954         --If the project task completion is not populated (ER 5758813), then use the visit task's end date.
1955         IF(l_task_completion_date is NULL) THEN
1956           OPEN get_vst_task_comp_date_csr(p_po_line_tbl(i).osp_line_id);
1957           FETCH get_vst_task_comp_date_csr INTO l_task_completion_date;
1958           CLOSE get_vst_task_comp_date_csr;
1959         END IF;
1960       ELSE
1961         l_task_completion_date := NULL;
1962       END IF;
1963 
1964       l_expenditure_item_type := FND_PROFILE.VALUE('AHL_OSP_EXPENDITURE_TYPE');
1965       IF(l_expenditure_item_type IS NULL) THEN
1966         l_expenditure_item_type := 'Outside Processing';
1967       END IF;
1968 
1969       --Fix for the  Bug 6436184
1970       l_charge_acct_id := get_charge_account_id(p_po_line_tbl(i).SHIP_TO_ORG_ID, p_po_line_tbl(i).ITEM_ID);
1971       IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1972         FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, L_DEBUG_KEY, 'l_charge_acct_id before inserting: '|| l_charge_acct_id);
1973         FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, L_DEBUG_KEY, 'l_task_completion_date : '|| to_char(l_task_completion_date, 'DD-MON-YYYY HH24:MI:SS'));
1974         FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, L_DEBUG_KEY, 'l_expenditure_item_type : '|| l_expenditure_item_type);
1975       END IF;
1976       --mpothuku End
1977 
1978       INSERT INTO PO_DISTRIBUTIONS_INTERFACE (
1979         INTERFACE_HEADER_ID,
1980         INTERFACE_LINE_ID,
1981         INTERFACE_DISTRIBUTION_ID,
1982         QUANTITY_ORDERED,
1983         PROGRAM_ID,
1984         PROGRAM_APPLICATION_ID,
1985         CREATION_DATE,
1986         CREATED_BY
1987         -- Added by mpothuku on 10-oct-2007 to fix bug 6431740
1988         ,WIP_ENTITY_ID
1989         ,PROJECT_RELEATED_FLAG
1990         ,PROJECT_ACCOUNTING_CONTEXT
1991         ,PROJECT_ID
1992         ,TASK_ID
1993         ,EXPENDITURE_TYPE
1994         ,EXPENDITURE_ORGANIZATION_ID
1995         ,EXPENDITURE_ITEM_DATE
1996         ,CHARGE_ACCOUNT_ID
1997 
1998       ) VALUES (
1999         p_intf_header_id,
2000         l_intf_line_id,
2001         PO_DISTRIBUTIONS_INTERFACE_S.NEXTVAL,
2002         p_po_line_tbl(i).QUANTITY,
2003         AHL_GLOBAL.AHL_OSP_PROGRAM_ID,
2004         AHL_GLOBAL.AHL_APPLICATION_ID,
2005         SYSDATE,
2006         FND_GLOBAL.USER_ID
2007         -- Added by mpothuku on 10-oct-2007 to fix bug 6431740
2008         ,p_po_line_tbl(i).wip_entity_id
2009         ,DECODE(p_po_line_tbl(i).project_id, null, null, 'Y')
2010         ,DECODE(p_po_line_tbl(i).project_id, null, null, 'Y')
2011         ,p_po_line_tbl(i).project_id
2012         ,p_po_line_tbl(i).task_id
2013         ,DECODE(p_po_line_tbl(i).project_id, null, null, l_expenditure_item_type)
2014         ,DECODE(p_po_line_tbl(i).project_id, null, null,p_po_line_tbl(i).SHIP_TO_ORG_ID)
2015         ,l_task_completion_date
2016         ,l_charge_acct_id
2017       );
2018        --Added by mpothuku on 10-oct-2007 to fix bug 6431740
2019       IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2020         FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, L_DEBUG_KEY,
2021             ' Inserting into PO_DISTRIBUTIONS_INTERFACE. INTERFACE_HEADER_ID = ' || p_intf_header_id ||
2022             ', INTERFACE_LINE_ID = ' || l_intf_line_id ||
2023             ', WIP_ENTITY_ID = ' || p_po_line_tbl(i).wip_entity_id ||
2024             ', PROJECT_ID = ' || p_po_line_tbl(i).project_id ||
2025             ', TASK_ID = ' || p_po_line_tbl(i).task_id ||
2026             ', EXPENDITURE_TYPE = ' ||l_expenditure_item_type ||
2027             ', EXPENDITURE_ORGANIZATION_ID = ' || p_po_line_tbl(i).SHIP_TO_ORG_ID ||
2028             ', EXPENDITURE_ITEM_DATE = ' || to_char(l_task_completion_date, 'DD-MON-YYYY HH24:MI:SS'));
2029       END IF;
2030     END IF;
2031   END LOOP;
2032   IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2033     FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, L_DEBUG_KEY || '.end', 'Exiting Procedure');
2034   END IF;
2035 END Insert_PO_Lines;
2036 
2037 ----------------------------------------
2038 -- This Procedure calls the Concurrent Program to Create
2039 -- Purchase Order
2040 ----------------------------------------
2041 PROCEDURE Call_PDOI_Program(
2042    p_batch_id   IN  NUMBER,
2043    x_request_id OUT NOCOPY NUMBER) IS
2044 
2045   L_DEBUG_KEY CONSTANT VARCHAR2(150) := G_LOG_PREFIX || '.Call_PDOI_Program';
2046   l_curr_org_id            NUMBER;
2047 
2048 BEGIN
2049   -- Added by jaramana on Sep 9, 2005 for MOAC Uptake
2050   l_curr_org_id := MO_GLOBAL.get_current_org_id();
2051   FND_REQUEST.SET_ORG_ID(l_curr_org_id);
2052 
2053   x_request_id := FND_REQUEST.SUBMIT_REQUEST(
2054           application => G_PO_APP_CODE,
2055           program     => G_PDOI_CODE,
2056           argument1   => NULL,  -- Buyer
2057           argument2   => G_DOC_TYPE_CODE,  -- Document Type
2058           argument3   => NULL,  -- Document Sub Type
2059           argument4   => G_NO_FLAG,  -- Create or Update Items
2060           argument5   => NULL,  -- Create Sourcing Rules
2061           argument6   => G_INCOMPLETE_STATUS,  -- Approval Status
2062           argument7   => NULL,  -- Release Generation Method
2063           argument8   => p_batch_id,  -- Batch Id
2064           argument9   => NULL, --Org Id
2065 	  argument10  => NULL,  --global agreement flag
2066           --Changes by jrotich 04/04/2013 for fixing bug 16088388 Begin
2067           argument11=>'',argument12=>'',argument13=>'',argument14=>'',argument15=>'',
2068           argument16=>'',argument17=>'',argument18=>'',argument19=>'',argument20=>'',
2069           argument21=>'',argument22=>'',argument23=>'',argument24=>'',argument25=>'',
2070           argument26=>'',argument27=>'',argument28=>'',argument29=>'',argument30=>'',
2071           argument31=>'',argument32=>'',argument33=>'',argument34=>'',argument35=>'',
2072           argument36=>'',argument37=>'',argument38=>'',argument39=>'',argument40=>'',
2073           argument41=>'',argument42=>'',argument43=>'',argument44=>'',argument45=>'',
2074           argument46=>'',argument47=>'',argument48=>'',argument49=>'',argument50=>'',
2075           argument51=>'',argument52=>'',argument53=>'',argument54=>'',argument55=>'',
2076           argument56=>'',argument57=>'',argument58=>'',argument59=>'',argument60=>'',
2077           argument61=>'',argument62=>'',argument63=>'',argument64=>'',argument65=>'',
2078           argument66=>'',argument67=>'',argument68=>'',argument69=>'',argument70=>'',
2079           argument71=>'',argument72=>'',argument73=>'',argument74=>'',argument75=>'',
2080           argument76=>'',argument77=>'',argument78=>'',argument79=>'',argument80=>'',
2081           argument81=>'',argument82=>'',argument83=>'',argument84=>'',argument85=>'',
2082           argument86=>'',argument87=>'',argument88=>'',argument89=>'',argument90=>'',
2083           argument91=>'',argument92=>'',argument93=>'',argument94=>'',argument95=>'',
2084           argument96=>'',argument97=>'',argument98=>'',argument99=>'',argument100=>''
2085           --Changes by jrotich end
2086           );
2087 
2088   IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2089     FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, L_DEBUG_KEY, 'Request Submitted. Request Id = ' || x_request_id);
2090   END IF;
2091 END Call_PDOI_Program;
2092 
2093 ----------------------------------------
2094 -- This Procedure calls the PDOI API directly to Create Purchase Order
2095 -- TO BE USED FOR DEBUGGING PURPOSE ONLY
2096 ----------------------------------------
2097 PROCEDURE Call_PDOI_API(p_batch_id IN NUMBER) IS
2098 BEGIN
2099   po_docs_interface_sv5.process_po_headers_interface(
2100           X_selected_batch_id   => p_batch_id,
2101           X_buyer_id            => NULL,
2102           X_document_type       => G_DOC_TYPE_CODE,
2103           X_document_subtype    => NULL,
2104           X_create_items        => G_NO_FLAG,
2105           X_create_sourcing_rules_flag  => G_NO_FLAG,
2106           X_rel_gen_method      => NULL,
2107           X_approved_status     => NULL,
2108           X_commit_interval     => 1,
2109           X_process_code        => G_PROCESS_CODE);
2110 
2111    --dbms_output.enable;
2112    --dbms_output.put_line('See log file of comops/comfam in /sqlcom/log directory.');
2113 
2114 END Call_PDOI_API;
2115 
2116 ----------------------------------------
2117 -- This Local Procedure updates OSP Tables with
2118 -- PO Information for one OSP Order
2119 ----------------------------------------
2120 PROCEDURE Associate_OSP_PO(
2121    p_osp_order_id IN  NUMBER,
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
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
2138     PO.INTERFACE_SOURCE_CODE = AHL_GLOBAL.AHL_APP_SHORT_NAME;
2139 
2140   CURSOR l_get_osp_lines_csr(p_osp_order_id IN NUMBER) IS
2141     SELECT OSP_ORDER_LINE_ID FROM AHL_OSP_ORDER_LINES
2142     WHERE PO_LINE_ID IS NULL
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
2152     SELECT PO_REQUEST_ID FROM AHL_OSP_ORDERS_B
2153     WHERE OSP_ORDER_ID = p_osp_order_id;
2154 
2155    L_DEBUG_KEY              CONSTANT VARCHAR2(150) := G_LOG_PREFIX || '.Associate_OSP_PO';
2156    l_po_header_id           NUMBER;
2157    l_osp_order_line_id      NUMBER;
2158    l_po_line_id             NUMBER;
2159    l_request_id             NUMBER;
2160    l_phase                  VARCHAR2(100);
2161    l_status                 VARCHAR2(100);
2162    l_dev_phase              VARCHAR2(100);
2163    l_dev_status             VARCHAR2(100);
2164    l_message                VARCHAR2(1000);
2165    l_retval                 BOOLEAN;
2166 
2167 BEGIN
2168   IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2169     FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, L_DEBUG_KEY || '.begin', 'Entering Procedure');
2170   END IF;
2171 
2172   OPEN l_get_po_hdr_csr(p_osp_order_id);
2173   FETCH l_get_po_hdr_csr INTO x_po_header_id;
2174   IF (l_get_po_hdr_csr%FOUND) THEN
2175     -- Update AHL_OSP_ORDERS_B's PO_HEADER_ID
2176 --dbms_output.put_line('About to Update  AHL_OSP_ORDERS_B.PO_HEADER_ID with ' || x_po_header_id);
2177     Set_PO_Header_Id(p_osp_order_id => p_osp_order_id,
2178                      p_po_header_id => x_po_header_id);
2179 
2180 --dbms_output.put_line('Updated po_header_id. Logging Transaction...');
2181     AHL_OSP_UTIL_PKG.Log_Transaction(p_trans_type_code    => G_TXN_TYPE_PO_SYNCH,
2182                                      p_src_doc_id         => p_osp_order_id,
2183                                      p_src_doc_type_code  => G_DOC_TYPE_OSP,
2184                                      p_dest_doc_id        => x_po_header_id,
2185                                      p_dest_doc_type_code => G_DOC_TYPE_PO);
2186 
2187     -- Get PO Lines for all OSP Lines
2188 --dbms_output.put_line('About to get all lines...');
2189     OPEN l_get_osp_lines_csr(p_osp_order_id);
2190     LOOP
2191       FETCH l_get_osp_lines_csr INTO l_osp_order_line_id;
2192       EXIT WHEN l_get_osp_lines_csr%NOTFOUND;
2193       OPEN l_get_po_line_csr(l_osp_order_line_id, x_po_header_id);
2194       FETCH l_get_po_line_csr INTO l_po_line_id;
2195       IF (l_get_po_line_csr%FOUND) THEN
2196       --dbms_output.put_line('About to set po_line_id ' || l_po_line_id || ' for osp line ' || l_osp_order_line_id );
2197         Set_PO_Line_Id(p_osp_order_line_id => l_osp_order_line_id,
2198                        p_po_line_id        => l_po_line_id);
2199       ELSE
2200 --dbms_output.put_line('OSP Line Id ' || l_osp_order_line_id || ' is not yet associated with a PO Line.');
2201         IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2202           FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, L_DEBUG_KEY, 'OSP Line Id ' || l_osp_order_line_id || ' is not yet associated with a PO Line');
2203         END IF;
2204       END IF;
2205       CLOSE l_get_po_line_csr;
2206     END LOOP;
2207     CLOSE l_get_osp_lines_csr;
2208   ELSE
2209 --dbms_output.put_line('No matching PO Header Id found.');
2210     -- Set Return PO Header Value to null
2211     x_po_header_id := null;
2212     -- Check if the Concurrent Program has completed
2213     OPEN l_get_request_id_csr(p_osp_order_id);
2214     FETCH l_get_request_id_csr INTO l_request_id;
2215     CLOSE l_get_request_id_csr;
2216     IF (l_request_id IS NOT NULL AND l_request_id <> 0) THEN
2217 --dbms_output.put_line('Getting Concurrent Program Status ');
2218       l_retval := FND_CONCURRENT.GET_REQUEST_STATUS(request_id => l_request_id,
2219                                                     phase      => l_phase,
2220                                                     status     => l_status,
2221                                                     dev_phase  => l_dev_phase,
2222                                                     dev_status => l_dev_status,
2223                                                     message    => l_message);
2224 --dbms_output.put_line('l_dev_phase = ' || l_dev_phase || ', l_dev_status = ' || l_dev_status );
2225 --dbms_output.put_line('l_message = ' || l_message);
2226 --      IF ((l_retval = TRUE) AND (l_dev_phase = 'COMPLETE') AND (l_dev_status <> 'NORMAL')) THEN
2227       -- Status can be NORMAL even if the PO Creation had failed.
2228       -- So setting status to SUBMISSION_FAILED if the Concurrent Program has completed
2229       -- but the PO Header is not set
2230       IF ((l_retval = TRUE) AND (l_dev_phase = 'COMPLETE')) THEN
2231         -- Abnormal Termination
2232 --dbms_output.put_line('Concurrent Program has completed. Setting Status to SUBMISSION_FAILED.');
2233         IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2234           FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, L_DEBUG_KEY, 'Concurrent Program with Request Id ' || l_request_id || ' has terminated. dev_status = ' || l_dev_status || ', message = ' || l_message);
2235         END IF;
2236         -- Set the Status of OSP Order to Submission Failed
2237         Set_Submission_Failed(p_osp_order_id);
2238       END IF;
2239     END IF;
2240   END IF;
2241   CLOSE l_get_po_hdr_csr;
2242 --dbms_output.put_line('About to exit Associate_OSP_PO ');
2243   IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2244     FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, L_DEBUG_KEY || '.end', 'Exiting Procedure');
2245   END IF;
2246 END Associate_OSP_PO;
2247 
2248 ----------------------------------------
2249 -- This Procedure handles cancelled PO Lines and is Part of PO Synchronization.
2250 -- This procedure commits its work if p_commit is set to true and
2251 -- if there were no errors during the execution of this procedure.
2252 -- It does not check the message list for performing the commit action
2253 ----------------------------------------
2254 PROCEDURE Handle_Cancelled_PO_Lines(
2255    p_commit         IN  VARCHAR2,
2256    x_return_status  OUT NOCOPY VARCHAR2) 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
2266           PO.REFERENCE_NUM = OL.OSP_ORDER_ID AND           -- Related to the OSP Order
2267           OSP.OSP_ORDER_ID = PO.REFERENCE_NUM AND
2268           -- Added by jaramana on April 7, 2008 for bug 6609988
2269           OSP.OPERATING_UNIT_ID = MO_GLOBAL.get_current_org_id() AND
2270           OL.PO_LINE_ID = PL.PO_LINE_ID AND
2271           NVL(OL.STATUS_CODE, ' ') <> G_OL_PO_CANCELLED_STATUS       -- Not yet updated
2272           ORDER BY PO.REFERENCE_NUM;                       -- One OSP Order at a time
2273 
2274    L_DEBUG_KEY              CONSTANT VARCHAR2(150) := G_LOG_PREFIX || '.Handle_Cancelled_PO_Lines';
2275    l_osp_order_id           NUMBER := -1;
2276    l_osp_order_line_id      NUMBER;
2277    l_po_line_id             NUMBER;
2278    l_prev_osp_order_id      NUMBER := -1;
2279    l_table_index            NUMBER := 0;
2280    l_return_status          VARCHAR2(1);
2281    l_msg_count              NUMBER;
2282    l_msg_data               VARCHAR2(1000);
2283    l_osp_order_rec          AHL_OSP_ORDERS_PVT.osp_order_rec_type;
2284    l_osp_order_lines_tbl    AHL_OSP_ORDERS_PVT.osp_order_lines_tbl_type;
2285    l_commit_flag            BOOLEAN := true;
2286    l_osp_obj_ver_num        NUMBER;
2287    l_ol_obj_ver_num         NUMBER;
2288 
2289 BEGIN
2290   IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2291     FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, L_DEBUG_KEY || '.begin', 'Entering Procedure');
2292   END IF;
2293   -- Standard start of API savepoint
2294   SAVEPOINT Handle_Cancelled_PO_Lines_pvt;
2295 
2296   -- Initialize API return status to success
2297   x_return_status := FND_API.G_RET_STS_SUCCESS;
2298 
2299   OPEN l_get_cancelled_po_lines_csr;
2300   LOOP
2301     FETCH l_get_cancelled_po_lines_csr INTO l_po_line_id,
2302                                             l_osp_order_line_id,
2303                                             l_osp_order_id,
2304                                             l_ol_obj_ver_num,
2305                                             l_osp_obj_ver_num;
2306     EXIT WHEN l_get_cancelled_po_lines_csr%NOTFOUND;
2307     IF (l_osp_order_id <> l_prev_osp_order_id) THEN
2308       IF (l_prev_osp_order_id <> -1) THEN
2309         -- Cancel all OSP Lines pertaining to the previous OSP Order
2310         IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2311           FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, L_DEBUG_KEY, 'Cancelling OSP Line for Order ' || l_prev_osp_order_id);
2312         END IF;
2313         AHL_OSP_ORDERS_PVT.process_osp_order(p_api_version           => 1.0,
2314                                              p_init_msg_list         => FND_API.G_FALSE,
2315                                              p_commit                => FND_API.G_FALSE,
2316                                              p_validation_level      => FND_API.G_VALID_LEVEL_FULL,
2317                                              p_x_osp_order_rec       => l_osp_order_rec,
2318                                              p_x_osp_order_lines_tbl => l_osp_order_lines_tbl,
2319                                              x_return_status         => l_return_status,
2320                                              x_msg_count             => l_msg_count,
2321                                              x_msg_data              => l_msg_data);
2322         IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
2323           IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2324             FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, L_DEBUG_KEY, 'Error while Cancelling OSP Line for OSP Order ' || l_prev_osp_order_id);
2325           END IF;
2326           l_commit_flag := false;
2327         END IF;
2328         -- Delete table used by prior call
2329         l_osp_order_lines_tbl.DELETE;
2330       END IF;
2331       -- Update API Record with new OSP Order Id
2332       l_osp_order_rec.OSP_ORDER_ID := l_osp_order_id;
2333       l_osp_order_rec.OBJECT_VERSION_NUMBER := l_osp_obj_ver_num;
2334       l_prev_osp_order_id := l_osp_order_id;
2335       l_table_index := 0;
2336     END IF;
2337     -- Copy OSP Line Id into API's Line Table at l_table_index
2338     l_osp_order_lines_tbl(l_table_index).OSP_ORDER_LINE_ID := l_osp_order_line_id;
2339     -- Copy Line's Object Version Nnumber into API's Line Table at l_table_index
2340     l_osp_order_lines_tbl(l_table_index).OBJECT_VERSION_NUMBER := l_ol_obj_ver_num;
2341     -- Set OSP Line Status to G_OL_PO_CANCELLED_STATUS in API's Line Table
2342     l_osp_order_lines_tbl(l_table_index).STATUS_CODE := G_OL_PO_CANCELLED_STATUS;
2343     -- Set Operation to Update in the line rec
2344     l_osp_order_lines_tbl(l_table_index).OPERATION_FLAG := AHL_OSP_ORDERS_PVT.G_OP_UPDATE;
2345 
2346     l_table_index := l_table_index + 1;
2347   END LOOP;
2348   CLOSE l_get_cancelled_po_lines_csr;
2349   IF (l_prev_osp_order_id <> -1) THEN
2350     -- Save the Last Cancellation
2351     IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2352       FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, L_DEBUG_KEY, 'Cancelling OSP Line for Order ' || l_prev_osp_order_id);
2353     END IF;
2354     AHL_OSP_ORDERS_PVT.process_osp_order(p_api_version           => 1.0,
2355                                          p_init_msg_list         => FND_API.G_FALSE,
2356                                          p_commit                => FND_API.G_FALSE,
2357                                          p_validation_level      => FND_API.G_VALID_LEVEL_FULL,
2358                                          p_x_osp_order_rec       => l_osp_order_rec,
2359                                          p_x_osp_order_lines_tbl => l_osp_order_lines_tbl,
2360                                          x_return_status         => l_return_status,
2361                                          x_msg_count             => l_msg_count,
2362                                          x_msg_data              => l_msg_data);
2363     IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
2364       IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2365         FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, L_DEBUG_KEY, 'Error while Cancelling OSP Line for OSP Order ' || l_prev_osp_order_id);
2366       END IF;
2367       l_commit_flag := false;
2368     END IF;
2369   END IF;
2370   IF (l_commit_flag = false) THEN
2371     RAISE FND_API.G_EXC_ERROR;  -- Rollback and return error
2372   END IF;
2373   -- No errors in current procedure: Check only passed in flag
2374   IF (FND_API.TO_BOOLEAN(p_commit)) THEN
2375     IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2376       FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, L_DEBUG_KEY, 'About to COMMIT work.');
2377     END IF;
2378     COMMIT WORK;
2379   END IF;
2380   IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2381     FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, L_DEBUG_KEY || '.end', 'Exiting Procedure');
2382   END IF;
2383 EXCEPTION
2384  WHEN FND_API.G_EXC_ERROR THEN
2385    ROLLBACK TO Handle_Cancelled_PO_Lines_pvt;
2386    x_return_status := FND_API.G_RET_STS_ERROR;
2387 
2388  WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2389    ROLLBACK TO Handle_Cancelled_PO_Lines_pvt;
2390    x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2391 
2392  WHEN OTHERS THEN
2393    ROLLBACK TO Handle_Cancelled_PO_Lines_pvt;
2394    x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2395    IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
2396      fnd_msg_pub.add_exc_msg(p_pkg_name       => G_PKG_NAME,
2397                                p_procedure_name => 'Handle_Cancelled_PO_Lines',
2398                                p_error_text     => SUBSTR(SQLERRM,1,240));
2399    END IF;
2400 
2401 END Handle_Cancelled_PO_Lines;
2402 
2403 ----------------------------------------
2404 -- This Procedure handles deleted PO Lines and is Part of PO Synchronization.
2405 -- This procedure commits its work if p_commit is set to true and
2406 -- if there were no errors during the execution of this procedure.
2407 -- It does not check the message list for performing the commit action.
2408 ----------------------------------------
2409 PROCEDURE Handle_Deleted_PO_Lines(
2410    p_commit         IN  VARCHAR2,
2411    x_return_status  OUT NOCOPY VARCHAR2) IS
2412 
2413   CURSOR l_get_deleted_po_lines_csr IS
2414     SELECT OL.OSP_ORDER_ID, OL.OSP_ORDER_LINE_ID,
2415            OL.OBJECT_VERSION_NUMBER, OSP.OBJECT_VERSION_NUMBER
2416     FROM AHL_OSP_ORDER_LINES OL, AHL_OSP_ORDERS_B OSP
2417     WHERE OL.PO_LINE_ID IS NOT NULL AND                -- PO Created
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;
2427    l_osp_order_line_id      NUMBER;
2428    l_prev_osp_order_id      NUMBER := -1;
2429    l_table_index            NUMBER := 0;
2430    l_return_status          VARCHAR2(1);
2431    l_msg_count              NUMBER;
2432    l_msg_data               VARCHAR2(1000);
2433    l_osp_order_rec          AHL_OSP_ORDERS_PVT.osp_order_rec_type;
2434    l_osp_order_lines_tbl    AHL_OSP_ORDERS_PVT.osp_order_lines_tbl_type;
2435    l_commit_flag            BOOLEAN := true;
2436    l_osp_obj_ver_num        NUMBER;
2437    l_ol_obj_ver_num         NUMBER;
2438 
2439 BEGIN
2440   IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2441     FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, L_DEBUG_KEY || '.begin', 'Entering Procedure');
2442   END IF;
2443   -- Standard start of API savepoint
2444   SAVEPOINT Handle_Deleted_PO_Lines_pvt;
2445 
2446   -- Initialize API return status to success
2447   x_return_status := FND_API.G_RET_STS_SUCCESS;
2448 
2449   OPEN l_get_deleted_po_lines_csr;
2450   LOOP
2451     FETCH l_get_deleted_po_lines_csr INTO l_osp_order_id,
2452                                           l_osp_order_line_id,
2453                                           l_ol_obj_ver_num,
2454                                           l_osp_obj_ver_num;
2455     EXIT WHEN l_get_deleted_po_lines_csr%NOTFOUND;
2456     IF (l_osp_order_id <> l_prev_osp_order_id) THEN
2457       IF (l_prev_osp_order_id <> -1) THEN
2458         -- PO Delete all OSP Lines pertaining to the previous OSP Order
2459         IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2460           FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, L_DEBUG_KEY, 'Deleting OSP Line for Order ' || l_prev_osp_order_id);
2461         END IF;
2462         AHL_OSP_ORDERS_PVT.process_osp_order(p_api_version           => 1.0,
2463                                              p_init_msg_list         => FND_API.G_FALSE,
2464                                              p_commit                => FND_API.G_FALSE,
2465                                              p_validation_level      => FND_API.G_VALID_LEVEL_FULL,
2466                                              p_x_osp_order_rec       => l_osp_order_rec,
2467                                              p_x_osp_order_lines_tbl => l_osp_order_lines_tbl,
2468                                              x_return_status         => l_return_status,
2469                                              x_msg_count             => l_msg_count,
2470                                              x_msg_data              => l_msg_data);
2471         IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
2472           IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2473             FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, L_DEBUG_KEY, 'Error while Deleting OSP Line for OSP Order ' || l_prev_osp_order_id);
2474           END IF;
2475           l_commit_flag := false;
2476         END IF;
2477         -- Delete table used by prior call
2478         l_osp_order_lines_tbl.DELETE;
2479       END IF;
2480       -- Update API Record with new OSP Order Id
2481       l_osp_order_rec.OSP_ORDER_ID := l_osp_order_id;
2482       l_osp_order_rec.OBJECT_VERSION_NUMBER := l_osp_obj_ver_num;
2483       l_prev_osp_order_id := l_osp_order_id;
2484       l_table_index := 0;
2485     END IF;
2486     -- Copy OSP Line Id into API's Line Table at l_table_index
2487     l_osp_order_lines_tbl(l_table_index).OSP_ORDER_LINE_ID := l_osp_order_line_id;
2488     -- Copy Line's Object Version Nnumber into API's Line Table at l_table_index
2489     l_osp_order_lines_tbl(l_table_index).OBJECT_VERSION_NUMBER := l_ol_obj_ver_num;
2490     -- Set  OSP Line Status to G_OL_PO_DELETED_STATUS in API's Line Table
2491     l_osp_order_lines_tbl(l_table_index).STATUS_CODE := G_OL_PO_DELETED_STATUS;
2492     -- Set Operation to Update in the line rec
2493     l_osp_order_lines_tbl(l_table_index).OPERATION_FLAG := AHL_OSP_ORDERS_PVT.G_OP_UPDATE;
2494     l_table_index := l_table_index + 1;
2495   END LOOP;
2496   CLOSE l_get_deleted_po_lines_csr;
2497   IF (l_prev_osp_order_id <> -1) THEN
2498     -- Save the Last Deletion
2499     IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2500       FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, L_DEBUG_KEY, 'Deleting OSP Line for Order ' || l_prev_osp_order_id);
2501     END IF;
2502     AHL_OSP_ORDERS_PVT.process_osp_order(p_api_version           => 1.0,
2503                                          p_init_msg_list         => FND_API.G_FALSE,
2504                                          p_commit                => FND_API.G_FALSE,
2505                                          p_validation_level      => FND_API.G_VALID_LEVEL_FULL,
2506                                          p_x_osp_order_rec       => l_osp_order_rec,
2507                                          p_x_osp_order_lines_tbl => l_osp_order_lines_tbl,
2508                                          x_return_status         => l_return_status,
2509                                          x_msg_count             => l_msg_count,
2510                                          x_msg_data              => l_msg_data);
2511     IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
2512       IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2513         FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, L_DEBUG_KEY, 'Error while Deleting OSP Line for OSP Order ' || l_prev_osp_order_id);
2514       END IF;
2515       l_commit_flag := false;
2516     END IF;
2517   END IF;
2518 
2519   IF (l_commit_flag = false) THEN
2520     RAISE FND_API.G_EXC_ERROR;  -- Rollback and return error
2521   END IF;
2522   -- No errors in current procedure: Check only passed in flag
2523   IF (FND_API.TO_BOOLEAN(p_commit)) THEN
2524     IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2525       FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, L_DEBUG_KEY, 'About to COMMIT work.');
2526     END IF;
2527     COMMIT WORK;
2528   END IF;
2529   IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2530     FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, L_DEBUG_KEY || '.end', 'Exiting Procedure');
2531   END IF;
2532 EXCEPTION
2533  WHEN FND_API.G_EXC_ERROR THEN
2534    ROLLBACK TO Handle_Deleted_PO_Lines_pvt;
2535    x_return_status := FND_API.G_RET_STS_ERROR;
2536 
2537  WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2538    ROLLBACK TO Handle_Deleted_PO_Lines_pvt;
2539    x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2540 
2541  WHEN OTHERS THEN
2542    ROLLBACK TO Handle_Deleted_PO_Lines_pvt;
2543    x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2544    IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
2545      fnd_msg_pub.add_exc_msg(p_pkg_name       => G_PKG_NAME,
2546                                p_procedure_name => 'Handle_Deleted_PO_Lines',
2547                                p_error_text     => SUBSTR(SQLERRM,1,240));
2548    END IF;
2549 
2550 END Handle_Deleted_PO_Lines;
2551 
2552 ----------------------------------------
2553 -- This Procedure handles Approved POs and is Part of PO Synchronization.
2554 -- This procedure commits its work if p_commit is set to true and
2555 -- if there were no errors during the execution of this procedure.
2556 -- It does not check the message list for performing the commit action.
2557 ----------------------------------------
2558 PROCEDURE Handle_Approved_POs(
2559    p_commit         IN  VARCHAR2,
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
2569           PO.APPROVED_FLAG = G_YES_FLAG AND                  -- Approved PO
2570           NVL(PO.CANCEL_FLAG, G_NO_FLAG) <> G_YES_FLAG AND   -- Not Cancelled
2571           NVL(PO.CLOSED_CODE, G_PO_OPEN) NOT LIKE '%CLOSED'; -- Not Closed
2572 
2573    L_DEBUG_KEY              CONSTANT VARCHAR2(150) := G_LOG_PREFIX || '.Handle_Approved_POs';
2574    l_osp_order_id           NUMBER;
2575    l_po_header_id           NUMBER;
2576    l_oe_header_id           NUMBER;
2577    l_temp_num               NUMBER := 0;
2578    l_return_status          VARCHAR2(1) := FND_API.G_RET_STS_SUCCESS;
2579    l_msg_count              NUMBER;
2580    l_msg_data               VARCHAR2(1000);
2581 
2582    l_shipment_IDs_Tbl       AHL_OSP_SHIPMENT_PUB.Ship_ID_Tbl_Type;
2583 
2584 BEGIN
2585   IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2586     FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, L_DEBUG_KEY || '.begin', 'Entering Procedure');
2587   END IF;
2588   -- Standard start of API savepoint
2589   SAVEPOINT Handle_Approved_POs_pvt;
2590 
2591   -- Initialize API return status to success
2592   x_return_status := FND_API.G_RET_STS_SUCCESS;
2593 
2594   OPEN l_get_approved_pos_csr;
2595   LOOP
2596     FETCH l_get_approved_POs_csr INTO l_osp_order_id,
2597                                       l_po_header_id,
2598                                       l_oe_header_id;
2599     EXIT WHEN l_get_approved_POs_csr%NOTFOUND;
2600     IF (l_oe_header_id IS NOT NULL) THEN
2601       l_temp_num := l_temp_num + 1;  -- One based index
2602       -- Populate the table with this Shipment Id
2603       l_shipment_IDs_Tbl(l_temp_num) := l_oe_header_id;
2604       IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2605         FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, L_DEBUG_KEY, 'Booking Shipment with Id: ' || l_oe_header_id);
2606       END IF;
2607     END IF;
2608   END LOOP;
2609   CLOSE l_get_approved_POs_csr;
2610   IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2611     FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, L_DEBUG_KEY, 'Number of Approvals to be submitted: ' || l_temp_num);
2612   END IF;
2613   IF (l_temp_num > 0) THEN
2614     -- Call Shipment API. This API will not throw an error or
2615     -- re-book if the shipment is already booked
2616     AHL_OSP_SHIPMENT_PUB.Book_Order(p_api_version      => 1.0,
2617                                     p_init_msg_list    => FND_API.G_FALSE,
2618                                     p_commit           => FND_API.G_FALSE,
2619                                     p_validation_level => FND_API.G_VALID_LEVEL_FULL,
2620                                     p_oe_header_tbl    => l_shipment_IDs_Tbl,
2621                                     x_return_status    => l_return_status,
2622                                     x_msg_count        => l_msg_count,
2623                                     x_msg_data         => l_msg_data);
2624   END IF;
2625 
2626   x_return_status := l_return_status;
2627   IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
2628     IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2629       FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, L_DEBUG_KEY, 'AHL_OSP_SHIPMENT_PUB.Book_Order Did not succeed');
2630     END IF;
2631     RAISE FND_API.G_EXC_ERROR;  -- Rollback and return error
2632   END IF;
2633   -- No errors in current procedure: Check only passed in flag
2634   IF (FND_API.TO_BOOLEAN(p_commit)) THEN
2635     IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2636       FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, L_DEBUG_KEY, 'AHL_OSP_SHIPMENT_PUB.Book_Order Succeeded. About to COMMIT work.');
2637     END IF;
2638     COMMIT WORK;
2639   END IF;
2640   IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2641     FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, L_DEBUG_KEY || '.end', 'Exiting Procedure');
2642   END IF;
2643 EXCEPTION
2644  WHEN FND_API.G_EXC_ERROR THEN
2645    ROLLBACK TO Handle_Approved_POs_pvt;
2646    x_return_status := FND_API.G_RET_STS_ERROR;
2647 
2648  WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2649    ROLLBACK TO Handle_Approved_POs_pvt;
2650    x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2651 
2652  WHEN OTHERS THEN
2653    ROLLBACK TO Handle_Approved_POs_pvt;
2654    x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2655    IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
2656      fnd_msg_pub.add_exc_msg(p_pkg_name       => G_PKG_NAME,
2657                                p_procedure_name => 'Handle_Approved_POs',
2658                                p_error_text     => SUBSTR(SQLERRM,1,240));
2659    END IF;
2660 
2661 END Handle_Approved_POs;
2662 
2663 /** The following two procedures Handle_Deleted_PO_Headers and Handle_Deleted_Sales_Orders
2664   * were added by jaramana on March 31, 2006 to implement the ER 5074660
2665 ***/
2666 ----------------------------------------
2667 -- This Procedure handles Deleted PO Headers and is Part of PO Synchronization.
2668 -- This procedure commits its work if p_commit is set to true and
2669 -- if there were no errors during the execution of this procedure.
2670 -- It does not check the message list for performing the commit action.
2671 -- Functionality:
2672 -- After a PO has been created for an OSP Order, it is possible for the PO
2673 -- to be manually deleted (using Purchasing responsibility) before the PO is approved.
2674 -- Since this deletion will result in an OSP Order referring to a non-existent PO,
2675 -- we need to change the OSP order to bring it to a consistent state.
2676 -- This procedure basically looks for OSP Orders for which the PO has been deleted
2677 -- and resets some values and corrects the status of the order as well as the lines
2678 -- so that the OSP Order can be resubmitted and a different PO can be created.
2679 -- This procedure does a direct update of the AHL_OSP_ORDERS_B and the AHL_OSP_ORDER_LINES
2680 -- tables and does not call the process_osp_order API to avoid unwanted validations
2681 ----------------------------------------
2682 PROCEDURE Handle_Deleted_PO_Headers(
2683    p_commit         IN  VARCHAR2,
2684    x_return_status  OUT NOCOPY VARCHAR2) IS
2685 
2686   CURSOR get_po_deleted_osps_csr IS
2687     SELECT osp.osp_order_id,
2688            osp.object_version_number,
2689            osp.po_header_id,
2690            osp.status_code,
2691            osp.order_type_code
2692     FROM ahl_osp_orders_b osp
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,
2702            ospl.object_version_number,
2703            ospl.status_code,
2704            ospl.po_line_id
2705     FROM ahl_osp_order_lines ospl
2706     WHERE ospl.osp_order_id = c_osp_order_id;
2707 
2708    L_DEBUG_KEY              CONSTANT VARCHAR2(150) := G_LOG_PREFIX || '.Handle_Deleted_PO_Headers';
2709    l_temp_num               NUMBER := 0;
2710    l_osp_details_rec        get_po_deleted_osps_csr%ROWTYPE;
2711    l_osp_line_details_rec   get_osp_line_dtls_csr%ROWTYPE;
2712 
2713 BEGIN
2714   IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2715     FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, L_DEBUG_KEY || '.begin', 'Entering Procedure');
2716   END IF;
2717   -- Standard start of API savepoint
2718   SAVEPOINT Handle_Deleted_PO_Headers_pvt;
2719 
2720   -- Initialize API return status to success
2721   x_return_status := FND_API.G_RET_STS_SUCCESS;
2722   -- Get all OSP Orders for which the PO Header has been deleted
2723   OPEN get_po_deleted_osps_csr;
2724   LOOP
2725     FETCH get_po_deleted_osps_csr into l_osp_details_rec;
2726     EXIT WHEN get_po_deleted_osps_csr%NOTFOUND;
2727     l_temp_num := l_temp_num + 1;
2728     IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2729       FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, L_DEBUG_KEY, 'Processing PO Deletion for OSP Order ' || l_osp_details_rec.osp_order_id);
2730     END IF;
2731     -- Get the Line Details
2732     OPEN get_osp_line_dtls_csr(c_osp_order_id => l_osp_details_rec.osp_order_id);
2733     LOOP
2734       FETCH get_osp_line_dtls_csr into l_osp_line_details_rec;
2735       EXIT WHEN get_osp_line_dtls_csr%NOTFOUND;
2736       IF (l_osp_line_details_rec.status_code IS NULL) THEN
2737         IF (l_osp_line_details_rec.po_line_id IS NOT NULL) THEN
2738           -- Reset the value of PO_LINE_ID and increment OVN (status_code is already null)
2739           IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2740             FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, L_DEBUG_KEY, 'Updating OSP Line with Id ' || l_osp_line_details_rec.osp_order_line_id);
2741           END IF;
2742           Set_PO_Line_ID(p_osp_order_line_id => l_osp_line_details_rec.osp_order_line_id,
2743                          p_po_line_id        => null);
2744         END IF;
2745       ELSE
2746         -- Physically delete this line (PO_DELETED or PO_CANCELLED)
2747         IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2748           FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, L_DEBUG_KEY, 'Deleting OSP Line with Id ' || l_osp_line_details_rec.osp_order_line_id);
2749         END IF;
2750         DELETE FROM ahl_osp_order_lines
2751         WHERE osp_order_line_id = l_osp_line_details_rec.osp_order_line_id;
2752       END IF;
2753     END LOOP;
2754     CLOSE get_osp_line_dtls_csr;
2755     -- Now for the OSP Order Header, reset PO_HEADER_ID, PO_BATCH_ID, PO_REQUEST_ID and PO_INTERFACE_HEADER_ID.
2756     -- set STATUS_CODE to "ENTERED" and increment OVN
2757     update ahl_osp_orders_b
2758     set po_header_id = null,
2759         po_batch_id = null,
2760         po_request_id = null,
2761         po_interface_header_id = null,
2762         status_code = G_OSP_ENTERED_STATUS,
2763         object_version_number =  l_osp_details_rec.object_version_number + 1,
2764         last_update_date    = TRUNC(sysdate),
2765         last_updated_by     = fnd_global.user_id,
2766         last_update_login   = fnd_global.login_id
2767     where osp_order_id = l_osp_details_rec.osp_order_id;
2768   END LOOP;
2769   CLOSE get_po_deleted_osps_csr;
2770 
2771   IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2772     FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, L_DEBUG_KEY, 'Processed PO Deletion for ' || l_temp_num || ' OSP Orders');
2773   END IF;
2774 
2775   IF (FND_API.TO_BOOLEAN(p_commit)) THEN
2776     IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2777       FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, L_DEBUG_KEY, 'About to COMMIT work.');
2778     END IF;
2779     COMMIT WORK;
2780   END IF;
2781   IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2782     FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, L_DEBUG_KEY || '.end', 'Exiting Procedure');
2783   END IF;
2784 EXCEPTION
2785  WHEN FND_API.G_EXC_ERROR THEN
2786    ROLLBACK TO Handle_Deleted_PO_Headers_pvt;
2787    x_return_status := FND_API.G_RET_STS_ERROR;
2788 
2789  WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2790    ROLLBACK TO Handle_Deleted_PO_Headers_pvt;
2791    x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2792 
2793  WHEN OTHERS THEN
2794    ROLLBACK TO Handle_Deleted_PO_Headers_pvt;
2795    x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2796    IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
2797      fnd_msg_pub.add_exc_msg(p_pkg_name       => G_PKG_NAME,
2798                                p_procedure_name => 'Handle_Deleted_PO_Headers',
2799                                p_error_text     => SUBSTR(SQLERRM,1,240));
2800    END IF;
2801 
2802 END Handle_Deleted_PO_Headers;
2803 
2804 ----------------------------------------
2805 -- This Procedure handles Deleted Sales Orders and is Part of PO Synchronization.
2806 -- This procedure commits its work if p_commit is set to true and
2807 -- if there were no errors during the execution of this procedure.
2808 -- It does not check the message list for performing the commit action.
2809 -- Functionality:
2810 -- After a Sales Order has been created for an OSP Order, it is possible for the SO
2811 -- to be manually deleted (using Order Management responsibility) before the SO is booked.
2812 -- Since this deletion will result in an OSP Order referring to a non-existent SO,
2813 -- we need to change the OSP order to bring it to a consistent state.
2814 -- This procedure basically looks for OSP Orders for which the SO has been deleted
2815 -- and resets some values of the order as well as the lines so that a new shipment
2816 -- can be created for the OSP Order if required.
2817 -- This procedure does a direct update of the AHL_OSP_ORDERS_B and the AHL_OSP_ORDER_LINES
2818 -- tables and does not call the process_osp_order API to avoid unwanted validations.
2819 ----------------------------------------
2820 PROCEDURE Handle_Deleted_Sales_Orders(
2821    p_commit         IN  VARCHAR2,
2822    x_return_status  OUT NOCOPY VARCHAR2) IS
2823 
2824   CURSOR get_so_deleted_osps_csr IS
2825     SELECT osp.osp_order_id,
2826            osp.object_version_number,
2827            osp.oe_header_id,
2828            osp.status_code
2829     FROM ahl_osp_orders_b osp
2830     WHERE osp.status_code <> 'CLOSED' AND
2831           osp.oe_header_id IS NOT NULL AND
2832           -- Added by jaramana on April 7, 2008 for bug 6609988
2833           osp.operating_unit_id = MO_GLOBAL.get_current_org_id() AND
2834           NOT EXISTS (SELECT 1 FROM oe_order_headers_all where header_id = osp.oe_header_id);
2835 
2836   CURSOR get_osp_line_dtls_csr(c_osp_order_id IN NUMBER) IS
2837     SELECT ospl.osp_order_line_id,
2838            ospl.object_version_number,
2839            ospl.oe_ship_line_id,
2840            ospl.oe_return_line_id
2841     FROM ahl_osp_order_lines ospl
2842     WHERE ospl.osp_order_id = c_osp_order_id AND
2843           (ospl.oe_ship_line_id IS NOT NULL OR ospl.oe_return_line_id IS NOT NULL);
2844 
2845    L_DEBUG_KEY              CONSTANT VARCHAR2(150) := G_LOG_PREFIX || '.Handle_Deleted_Sales_Orders';
2846    l_temp_num               NUMBER := 0;
2847    l_osp_details_rec        get_so_deleted_osps_csr%ROWTYPE;
2848    l_osp_line_details_rec   get_osp_line_dtls_csr%ROWTYPE;
2849 
2850 BEGIN
2851   IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2852     FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, L_DEBUG_KEY || '.begin', 'Entering Procedure');
2853   END IF;
2854   -- Standard start of API savepoint
2855   SAVEPOINT Handle_Deleted_SOs_pvt;
2856 
2857   -- Initialize API return status to success
2858   x_return_status := FND_API.G_RET_STS_SUCCESS;
2859 
2860   -- Get all OSP Orders for which the PO Header has been deleted
2861   OPEN get_so_deleted_osps_csr;
2862   LOOP
2863     FETCH get_so_deleted_osps_csr into l_osp_details_rec;
2864     EXIT WHEN get_so_deleted_osps_csr%NOTFOUND;
2865     l_temp_num := l_temp_num + 1;
2866     IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2867       FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, L_DEBUG_KEY, 'Processing SO Deletion for OSP Order ' || l_osp_details_rec.osp_order_id);
2868     END IF;
2869     -- Get the Details of lines that have a ship or a return line
2870     OPEN get_osp_line_dtls_csr(c_osp_order_id => l_osp_details_rec.osp_order_id);
2871     LOOP
2872       FETCH get_osp_line_dtls_csr into l_osp_line_details_rec;
2873       EXIT WHEN get_osp_line_dtls_csr%NOTFOUND;
2874       -- Reset the value of oe_ship_line_id and oe_return_line_id and increment OVN
2875       update ahl_osp_order_lines
2876       set oe_ship_line_id       = null,
2877           oe_return_line_id     = null,
2878           object_version_number = l_osp_line_details_rec.object_version_number + 1,
2879           last_update_date    = TRUNC(sysdate),
2880           last_updated_by     = fnd_global.user_id,
2881           last_update_login   = fnd_global.login_id
2882       where osp_order_line_id = l_osp_line_details_rec.osp_order_line_id;
2883     END LOOP;
2884     CLOSE get_osp_line_dtls_csr;
2885     -- Now for the OSP Order Header, reset OE_HEADER_ID and increment OVN
2886     update ahl_osp_orders_b
2887     set OE_HEADER_ID = null,
2888         object_version_number =  l_osp_details_rec.object_version_number + 1,
2889         last_update_date    = TRUNC(sysdate),
2890         last_updated_by     = fnd_global.user_id,
2891         last_update_login   = fnd_global.login_id
2892     where osp_order_id = l_osp_details_rec.osp_order_id;
2893   END LOOP;
2894   CLOSE get_so_deleted_osps_csr;
2895 
2896   IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2897     FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, L_DEBUG_KEY, 'Processed SO Deletion for ' || l_temp_num || ' OSP Orders');
2898   END IF;
2899 
2900   -- No errors in current procedure: Check only passed in flag
2901   IF (FND_API.TO_BOOLEAN(p_commit)) THEN
2902     IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2903       FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, L_DEBUG_KEY, 'About to COMMIT work.');
2904     END IF;
2905     COMMIT WORK;
2906   END IF;
2907   IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2908     FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, L_DEBUG_KEY || '.end', 'Exiting Procedure');
2909   END IF;
2910 EXCEPTION
2911  WHEN FND_API.G_EXC_ERROR THEN
2912    ROLLBACK TO Handle_Deleted_SOs_pvt;
2913    x_return_status := FND_API.G_RET_STS_ERROR;
2914 
2915  WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2916    ROLLBACK TO Handle_Deleted_SOs_pvt;
2917    x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2918 
2919  WHEN OTHERS THEN
2920    ROLLBACK TO Handle_Deleted_SOs_pvt;
2921    x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2922    IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
2923      fnd_msg_pub.add_exc_msg(p_pkg_name       => G_PKG_NAME,
2924                                p_procedure_name => 'Handle_Deleted_Sales_Orders',
2925                                p_error_text     => SUBSTR(SQLERRM,1,240));
2926    END IF;
2927 
2928 END Handle_Deleted_Sales_Orders;
2929 
2930 ----------------------------------------
2931 -- This Procedure updates AHL_OSP_ORDERS_B with the Batch Id, Request Id and Interface Header Id
2932 ----------------------------------------
2933 PROCEDURE Record_OSP_Submission(
2934    p_osp_order_id IN NUMBER,
2935    p_batch_id     IN NUMBER,
2936    p_request_id   IN NUMBER,
2937    p_intf_hdr_id  IN NUMBER) IS
2938 BEGIN
2939   Update_OSP_Order(p_osp_order_id => p_osp_order_id,
2940                    p_batch_id     => p_batch_id,
2941                    p_request_id   => p_request_id,
2942                    p_intf_hdr_id  => p_intf_hdr_id);
2943 END Record_OSP_Submission;
2944 
2945 ----------------------------------------
2946 -- This Procedure updates AHL_OSP_ORDERS_B's PO_HEADER_ID and sets STATUS_CODE to PO_CREATED
2947 ----------------------------------------
2948 PROCEDURE Set_PO_Header_ID(
2949    p_osp_order_id IN NUMBER,
2950    p_po_header_id IN NUMBER) IS
2951 BEGIN
2952   Update_OSP_Order(p_osp_order_id => p_osp_order_id,
2953                    p_po_header_id => p_po_header_id,
2954                    p_status_code => G_OSP_PO_CREATED_STATUS);
2955 END Set_PO_Header_ID;
2956 
2957 ----------------------------------------
2958 -- This Procedure updates AHL_OSP_ORDERS_B.STATUS_CODE to SUBMISSION_FAILED
2959 ----------------------------------------
2960 PROCEDURE Set_Submission_Failed(
2961    p_osp_order_id IN NUMBER) IS
2962 BEGIN
2963   Update_OSP_Order(p_osp_order_id => p_osp_order_id,
2964                    p_status_code  => G_OSP_SUB_FAILED_STATUS);
2965 END Set_Submission_Failed;
2966 
2967 ----------------------------------------
2968 -- This Procedure updates AHL_OSP_ORDER_LINES.PO_LINE_ID
2969 ----------------------------------------
2970 PROCEDURE Set_PO_Line_ID(
2971    p_osp_order_line_id IN NUMBER,
2972    p_po_line_id IN NUMBER) IS
2973 
2974   CURSOR l_osp_line_dtls_csr(p_osp_line_id IN NUMBER) IS
2975     SELECT
2976       OBJECT_VERSION_NUMBER,
2977       OSP_ORDER_ID,
2978       OSP_LINE_NUMBER,
2979       STATUS_CODE,
2980       PO_LINE_TYPE_ID,
2981       SERVICE_ITEM_ID,
2982       SERVICE_ITEM_DESCRIPTION,
2983       SERVICE_ITEM_UOM_CODE,
2984       NEED_BY_DATE,
2985       SHIP_BY_DATE,
2986       PO_LINE_ID,
2987       OE_SHIP_LINE_ID,
2988       OE_RETURN_LINE_ID,
2989       WORKORDER_ID,
2990       OPERATION_ID,
2991       QUANTITY,
2992       EXCHANGE_INSTANCE_ID,
2993       INVENTORY_ITEM_ID,
2994       INVENTORY_ORG_ID,
2995       INVENTORY_ITEM_UOM,
2996       INVENTORY_ITEM_QUANTITY,
2997       SUB_INVENTORY,
2998       LOT_NUMBER,
2999       SERIAL_NUMBER,
3000       ATTRIBUTE_CATEGORY,
3001       ATTRIBUTE1,
3002       ATTRIBUTE2,
3003       ATTRIBUTE3,
3004       ATTRIBUTE4,
3005       ATTRIBUTE5,
3006       ATTRIBUTE6,
3007       ATTRIBUTE7,
3008       ATTRIBUTE8,
3009       ATTRIBUTE9,
3010       ATTRIBUTE10,
3011       ATTRIBUTE11,
3012       ATTRIBUTE12,
3013       ATTRIBUTE13,
3014       ATTRIBUTE14,
3015       ATTRIBUTE15,
3016 -- Begin Changes by jaramana on January 7, 2008 for the Requisition ER 6034236
3017       PO_REQ_LINE_ID
3018 -- End Changes by jaramana on January 7, 2008 for the Requisition ER 6034236
3019     FROM AHL_OSP_ORDER_LINES
3020     WHERE OSP_ORDER_LINE_ID = p_osp_order_line_id;
3021 
3022     l_osp_line_dtls_rec l_osp_line_dtls_csr%ROWTYPE;
3023     L_DEBUG_KEY         CONSTANT VARCHAR2(150) := G_LOG_PREFIX || '.Set_PO_Line_ID';
3024 
3025 BEGIN
3026   IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3027     FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, L_DEBUG_KEY || '.begin', 'Entering Procedure');
3028   END IF;
3029   -- Retrieve the current record
3030   OPEN l_osp_line_dtls_csr(p_osp_order_line_id);
3031   FETCH l_osp_line_dtls_csr INTO l_osp_line_dtls_rec;
3032   IF (l_osp_line_dtls_csr%NOTFOUND) THEN
3033     FND_MESSAGE.Set_Name('AHL', 'AHL_OSP_OSP_LINE_ID_INVALID');
3034     FND_MESSAGE.Set_Token('OSP_LINE_ID', p_osp_order_line_id);
3035     FND_MSG_PUB.ADD;
3036     IF (FND_LOG.LEVEL_ERROR >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3037       FND_LOG.MESSAGE(FND_LOG.LEVEL_ERROR, L_DEBUG_KEY, FALSE);
3038     END IF;
3039     CLOSE l_osp_line_dtls_csr;
3040     RAISE FND_API.G_EXC_ERROR;
3041   END IF;
3042   CLOSE l_osp_line_dtls_csr;
3043 
3044   -- Update cursor variable's PO Line ID
3045   l_osp_line_dtls_rec.PO_LINE_ID := p_po_line_id;
3046 
3047   -- Call Table Handler
3048   AHL_OSP_ORDER_LINES_PKG.UPDATE_ROW(
3049         P_OSP_ORDER_LINE_ID         => p_osp_order_line_id,
3050         P_OBJECT_VERSION_NUMBER     => l_osp_line_dtls_rec.OBJECT_VERSION_NUMBER + 1,  -- Updated
3051         P_OSP_ORDER_ID              => l_osp_line_dtls_rec.OSP_ORDER_ID,
3052         P_OSP_LINE_NUMBER           => l_osp_line_dtls_rec.OSP_LINE_NUMBER,
3053         P_STATUS_CODE               => l_osp_line_dtls_rec.STATUS_CODE,
3054         P_PO_LINE_TYPE_ID           => l_osp_line_dtls_rec.PO_LINE_TYPE_ID,
3055         P_SERVICE_ITEM_ID           => l_osp_line_dtls_rec.SERVICE_ITEM_ID,
3056         P_SERVICE_ITEM_DESCRIPTION  => l_osp_line_dtls_rec.SERVICE_ITEM_DESCRIPTION,
3057         P_SERVICE_ITEM_UOM_CODE     => l_osp_line_dtls_rec.SERVICE_ITEM_UOM_CODE,
3058         P_NEED_BY_DATE              => l_osp_line_dtls_rec.NEED_BY_DATE,
3059         P_SHIP_BY_DATE              => l_osp_line_dtls_rec.SHIP_BY_DATE,
3060         P_PO_LINE_ID                => l_osp_line_dtls_rec.PO_LINE_ID,  -- Updated
3061         P_OE_SHIP_LINE_ID           => l_osp_line_dtls_rec.OE_SHIP_LINE_ID,
3062         P_OE_RETURN_LINE_ID         => l_osp_line_dtls_rec.OE_RETURN_LINE_ID,
3063         P_WORKORDER_ID              => l_osp_line_dtls_rec.WORKORDER_ID,
3064         P_OPERATION_ID              => l_osp_line_dtls_rec.OPERATION_ID,
3065         P_QUANTITY                  => l_osp_line_dtls_rec.QUANTITY,
3066         P_EXCHANGE_INSTANCE_ID      => l_osp_line_dtls_rec.EXCHANGE_INSTANCE_ID,
3067         P_INVENTORY_ITEM_ID         => l_osp_line_dtls_rec.INVENTORY_ITEM_ID,
3068         P_INVENTORY_ORG_ID          => l_osp_line_dtls_rec.INVENTORY_ORG_ID,
3069         P_INVENTORY_ITEM_UOM        => l_osp_line_dtls_rec.INVENTORY_ITEM_UOM,
3070         P_INVENTORY_ITEM_QUANTITY   => l_osp_line_dtls_rec.INVENTORY_ITEM_QUANTITY,
3071         P_SUB_INVENTORY             => l_osp_line_dtls_rec.SUB_INVENTORY,
3072         P_LOT_NUMBER                => l_osp_line_dtls_rec.LOT_NUMBER,
3073         P_SERIAL_NUMBER             => l_osp_line_dtls_rec.SERIAL_NUMBER,
3074         P_ATTRIBUTE_CATEGORY        => l_osp_line_dtls_rec.ATTRIBUTE_CATEGORY,
3075         P_ATTRIBUTE1                => l_osp_line_dtls_rec.ATTRIBUTE1,
3076         P_ATTRIBUTE2                => l_osp_line_dtls_rec.ATTRIBUTE2,
3077         P_ATTRIBUTE3                => l_osp_line_dtls_rec.ATTRIBUTE3,
3078         P_ATTRIBUTE4                => l_osp_line_dtls_rec.ATTRIBUTE4,
3079         P_ATTRIBUTE5                => l_osp_line_dtls_rec.ATTRIBUTE5,
3080         P_ATTRIBUTE6                => l_osp_line_dtls_rec.ATTRIBUTE6,
3081         P_ATTRIBUTE7                => l_osp_line_dtls_rec.ATTRIBUTE7,
3082         P_ATTRIBUTE8                => l_osp_line_dtls_rec.ATTRIBUTE8,
3083         P_ATTRIBUTE9                => l_osp_line_dtls_rec.ATTRIBUTE9,
3084         P_ATTRIBUTE10               => l_osp_line_dtls_rec.ATTRIBUTE10,
3085         P_ATTRIBUTE11               => l_osp_line_dtls_rec.ATTRIBUTE11,
3086         P_ATTRIBUTE12               => l_osp_line_dtls_rec.ATTRIBUTE12,
3087         P_ATTRIBUTE13               => l_osp_line_dtls_rec.ATTRIBUTE13,
3088         P_ATTRIBUTE14               => l_osp_line_dtls_rec.ATTRIBUTE14,
3089         P_ATTRIBUTE15               => l_osp_line_dtls_rec.ATTRIBUTE15,
3090 -- Begin Changes by jaramana on January 7, 2008 for the Requisition ER 6034236
3091         P_PO_REQ_LINE_ID            => l_osp_line_dtls_rec.PO_REQ_LINE_ID,
3092 -- End Changes by jaramana on January 7, 2008 for the Requisition ER 6034236
3093         P_LAST_UPDATE_DATE          => TRUNC(sysdate),  -- Updated
3094         P_LAST_UPDATED_BY           => fnd_global.user_id,  -- Updated
3095         P_LAST_UPDATE_LOGIN         => fnd_global.login_id);  -- Updated
3096 
3097   IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3098     FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, L_DEBUG_KEY || '.end', 'Exiting Procedure');
3099   END IF;
3100 END Set_PO_Line_ID;
3101 
3102 ----------------------------------------
3103 -- This Procedure updates a record of AHL_OSP_ORDERS_B using the table handler
3104 -- All updates to this table from this Package should go through this procedure only
3105 ----------------------------------------
3106 PROCEDURE Update_OSP_Order(
3107    p_osp_order_id IN NUMBER,
3108    p_batch_id     IN NUMBER    := NULL,
3109    p_request_id   IN NUMBER    := NULL,
3110    p_status_code  IN VARCHAR2  := NULL,
3111    p_po_header_id IN NUMBER    := NULL,
3112    p_intf_hdr_id  IN NUMBER    := NULL
3113    ) IS
3114 
3115   CURSOR l_osp_dtls_csr(p_osp_order_id IN NUMBER) IS
3116     SELECT
3117       OBJECT_VERSION_NUMBER,
3118       OSP_ORDER_NUMBER,
3119       ORDER_TYPE_CODE,
3120       SINGLE_INSTANCE_FLAG,
3121       PO_HEADER_ID,
3122       OE_HEADER_ID,
3123       VENDOR_ID,
3124       VENDOR_SITE_ID,
3125       VENDOR_CONTACT_ID,
3126       CUSTOMER_ID,
3127       ORDER_DATE,
3128       CONTRACT_ID,
3129       CONTRACT_TERMS,
3130       OPERATING_UNIT_ID,
3131       PO_SYNCH_FLAG,
3132       STATUS_CODE,
3133       PO_BATCH_ID,
3134       PO_INTERFACE_HEADER_ID,
3135       PO_REQUEST_ID,
3136       PO_AGENT_ID,
3137       ATTRIBUTE_CATEGORY,
3138       ATTRIBUTE1,
3139       ATTRIBUTE2,
3140       ATTRIBUTE3,
3141       ATTRIBUTE4,
3142       ATTRIBUTE5,
3143       ATTRIBUTE6,
3144       ATTRIBUTE7,
3145       ATTRIBUTE8,
3146       ATTRIBUTE9,
3147       ATTRIBUTE10,
3148       ATTRIBUTE11,
3149       ATTRIBUTE12,
3150       ATTRIBUTE13,
3151       ATTRIBUTE14,
3152       ATTRIBUTE15,
3153       DESCRIPTION,
3154       PO_REQ_HEADER_ID  -- Added by jaramana on January 7, 2008 for the Requisition ER 6034236
3155     FROM AHL_OSP_ORDERS_VL
3156     WHERE OSP_ORDER_ID = p_osp_order_id;
3157 
3158     l_osp_dtls_rec l_osp_dtls_csr%ROWTYPE;
3159     L_DEBUG_KEY         CONSTANT VARCHAR2(150) := G_LOG_PREFIX || '.Update_OSP_Order';
3160 
3161 BEGIN
3162   IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3163     FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, L_DEBUG_KEY || '.begin', 'Entering Procedure');
3164   END IF;
3165   -- Retrieve the current record
3166   OPEN l_osp_dtls_csr(p_osp_order_id);
3167   FETCH l_osp_dtls_csr INTO l_osp_dtls_rec;
3168   IF (l_osp_dtls_csr%NOTFOUND) THEN
3169     FND_MESSAGE.Set_Name('AHL', 'AHL_OSP_OSP_ID_INVALID');
3170     FND_MESSAGE.Set_Token('OSP_ID', p_osp_order_id);
3171     FND_MSG_PUB.ADD;
3172     IF (FND_LOG.LEVEL_ERROR >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3173       FND_LOG.MESSAGE(FND_LOG.LEVEL_ERROR, L_DEBUG_KEY, FALSE);
3174     END IF;
3175     CLOSE l_osp_dtls_csr;
3176     RAISE FND_API.G_EXC_ERROR;
3177   END IF;
3178   CLOSE l_osp_dtls_csr;
3179 
3180   -- Update non-null local variables into cursor variable
3181   IF (p_batch_id IS NOT NULL) THEN
3182     l_osp_dtls_rec.PO_BATCH_ID := p_batch_id;
3183   END IF;
3184   IF (p_request_id IS NOT NULL) THEN
3185     l_osp_dtls_rec.PO_REQUEST_ID := p_request_id;
3186   END IF;
3187   IF (p_status_code IS NOT NULL) THEN
3188     l_osp_dtls_rec.STATUS_CODE := p_status_code;
3189   END IF;
3190   IF (p_po_header_id IS NOT NULL) THEN
3191     l_osp_dtls_rec.PO_HEADER_ID := p_po_header_id;
3192   END IF;
3193   IF (p_intf_hdr_id IS NOT NULL) THEN
3194     l_osp_dtls_rec.PO_INTERFACE_HEADER_ID := p_intf_hdr_id;
3195   END IF;
3196 
3197   -- Call Table Handler
3198   AHL_OSP_ORDERS_PKG.UPDATE_ROW(
3199     X_OSP_ORDER_ID          => p_osp_order_id,
3200     X_OBJECT_VERSION_NUMBER => l_osp_dtls_rec.OBJECT_VERSION_NUMBER + 1,  -- Updated
3201     X_OSP_ORDER_NUMBER      => l_osp_dtls_rec.OSP_ORDER_NUMBER,
3202     X_ORDER_TYPE_CODE       => l_osp_dtls_rec.ORDER_TYPE_CODE,
3203     X_SINGLE_INSTANCE_FLAG  => l_osp_dtls_rec.SINGLE_INSTANCE_FLAG,
3204     X_PO_HEADER_ID          => l_osp_dtls_rec.PO_HEADER_ID,  -- Updated
3205     X_OE_HEADER_ID          => l_osp_dtls_rec.OE_HEADER_ID,
3206     X_VENDOR_ID             => l_osp_dtls_rec.VENDOR_ID,
3207     X_VENDOR_SITE_ID        => l_osp_dtls_rec.VENDOR_SITE_ID,
3208     X_VENDOR_CONTACT_ID     => l_osp_dtls_rec.VENDOR_CONTACT_ID,
3209     X_CUSTOMER_ID           => l_osp_dtls_rec.CUSTOMER_ID,
3210     X_ORDER_DATE            => l_osp_dtls_rec.ORDER_DATE,
3211     X_CONTRACT_ID           => l_osp_dtls_rec.CONTRACT_ID,
3212     X_CONTRACT_TERMS        => l_osp_dtls_rec.CONTRACT_TERMS,
3213     X_OPERATING_UNIT_ID     => l_osp_dtls_rec.OPERATING_UNIT_ID,
3214     X_PO_SYNCH_FLAG         => l_osp_dtls_rec.PO_SYNCH_FLAG,
3215     X_STATUS_CODE           => l_osp_dtls_rec.STATUS_CODE,  -- Updated
3216     X_PO_BATCH_ID           => l_osp_dtls_rec.PO_BATCH_ID,  -- Updated
3217     X_PO_REQUEST_ID         => l_osp_dtls_rec.PO_REQUEST_ID,  -- Updated
3218     X_PO_INTERFACE_HEADER_ID => l_osp_dtls_rec.PO_INTERFACE_HEADER_ID,  -- Updated
3219     X_PO_AGENT_ID           => l_osp_dtls_rec.PO_AGENT_ID,
3220     X_ATTRIBUTE_CATEGORY    => l_osp_dtls_rec.ATTRIBUTE_CATEGORY,
3221     X_ATTRIBUTE1            => l_osp_dtls_rec.ATTRIBUTE1,
3222     X_ATTRIBUTE2            => l_osp_dtls_rec.ATTRIBUTE2,
3223     X_ATTRIBUTE3            => l_osp_dtls_rec.ATTRIBUTE3,
3224     X_ATTRIBUTE4            => l_osp_dtls_rec.ATTRIBUTE4,
3225     X_ATTRIBUTE5            => l_osp_dtls_rec.ATTRIBUTE5,
3226     X_ATTRIBUTE6            => l_osp_dtls_rec.ATTRIBUTE6,
3227     X_ATTRIBUTE7            => l_osp_dtls_rec.ATTRIBUTE7,
3228     X_ATTRIBUTE8            => l_osp_dtls_rec.ATTRIBUTE8,
3229     X_ATTRIBUTE9            => l_osp_dtls_rec.ATTRIBUTE9,
3230     X_ATTRIBUTE10           => l_osp_dtls_rec.ATTRIBUTE10,
3231     X_ATTRIBUTE11           => l_osp_dtls_rec.ATTRIBUTE11,
3232     X_ATTRIBUTE12           => l_osp_dtls_rec.ATTRIBUTE12,
3233     X_ATTRIBUTE13           => l_osp_dtls_rec.ATTRIBUTE13,
3234     X_ATTRIBUTE14           => l_osp_dtls_rec.ATTRIBUTE14,
3235     X_ATTRIBUTE15           => l_osp_dtls_rec.ATTRIBUTE15,
3236     X_DESCRIPTION           => l_osp_dtls_rec.DESCRIPTION,
3237     X_PO_REQ_HEADER_ID      => l_osp_dtls_rec.PO_REQ_HEADER_ID, -- Added by jaramana on January 7, 2008 for the Requisition ER 6034236
3238     X_LAST_UPDATE_DATE      => TRUNC(sysdate),  -- Updated
3239     X_LAST_UPDATED_BY       => fnd_global.user_id,  -- Updated
3240     X_LAST_UPDATE_LOGIN     => fnd_global.login_id);  -- Updated
3241   IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3242     FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, L_DEBUG_KEY || '.end', 'Exiting Procedure');
3243   END IF;
3244 END Update_OSP_Order;
3245 
3246 ----------------------------------------
3247 -- This Function gets the price of an item.
3248 -- The current logic is very simple: If the price is set at the inventory org,
3249 -- this price is returned. If not, it returns null, letting Purchasing default in this case.
3250 -- This has been made a separate function rather than inlining so that in case this logic
3251 -- needs to be changed in the future, the impact will be localized.
3252 ----------------------------------------
3253 
3254 FUNCTION Get_Item_Price(p_osp_line_id IN NUMBER) RETURN NUMBER IS
3255 
3256   CURSOR l_get_org_price_csr IS
3257     SELECT LIST_PRICE_PER_UNIT FROM MTL_SYSTEM_ITEMS_B MSIB, AHL_OSP_ORDER_LINES ospl
3258     WHERE MSIB.INVENTORY_ITEM_ID = ospl.service_item_id
3259       AND MSIB.ORGANIZATION_ID = ospl.inventory_org_id
3260       AND ospl.osp_order_line_id = p_osp_line_id;
3261 /*
3262   CURSOR l_get_master_price_csr IS
3263     SELECT MSIK.LIST_PRICE_PER_UNIT FROM MTL_SYSTEM_ITEMS_KFV MSIK, MTL_PARAMETERS MP
3264     WHERE MSIK.INVENTORY_ITEM_ID = p_item_id
3265       AND MSIK.ORGANIZATION_ID = MP.MASTER_ORGANIZATION_ID
3266       AND MP.ORGANIZATION_ID = p_org_id;
3267 */
3268   L_DEBUG_KEY      CONSTANT VARCHAR2(150) := G_LOG_PREFIX || '.Get_Item_Price';
3269   l_price          NUMBER := NULL;
3270 
3271 BEGIN
3272   OPEN l_get_org_price_csr;
3273   FETCH l_get_org_price_csr INTO l_price;
3274   CLOSE l_get_org_price_csr;
3275   IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3276     FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, L_DEBUG_KEY, 'p_osp_line_id: ' || p_osp_line_id || ', Price: ' || l_price);
3277   END IF;
3278   RETURN l_price;
3279 END Get_Item_Price;
3280 
3281 --Added by mpothuku on 10-Oct-2007 for fixing the Bug 6436184
3282 ----------------------------------------
3283 -- This function derived the charge_account_id
3284 ----------------------------------------
3285 FUNCTION get_charge_account_id(
3286   p_inv_org_id  IN  NUMBER,
3287   p_item_id IN  NUMBER
3288 ) RETURN NUMBER IS
3289 
3290   CURSOR get_exp_acct_item_csr(c_inv_org_id IN NUMBER, c_item_id IN NUMBER) IS
3291     SELECT expense_account
3292       FROM mtl_system_items_b
3293      WHERE organization_id = c_inv_org_id
3294        AND inventory_item_id = c_item_id;
3295 
3296   CURSOR get_mtl_acct_org_csr(c_inv_org_id IN NUMBER) IS
3297     SELECT material_account
3298       FROM mtl_parameters
3299      WHERE organization_id = c_inv_org_id;
3300 
3301    L_DEBUG_KEY      CONSTANT VARCHAR2(150) := G_LOG_PREFIX || '.get_charge_account_id';
3302    l_charge_acct_id NUMBER := null;
3303    l_item_account_set BOOLEAN := FALSE;
3304 
3305 BEGIN
3306   IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3307     FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, L_DEBUG_KEY, 'p_inv_org_id: '|| p_inv_org_id);
3308     FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, L_DEBUG_KEY, 'p_item_id: '|| p_item_id);
3309   END IF;
3310 
3311   IF(p_item_id is NULL) THEN
3312     --It is a one-time item
3313     l_item_account_set := FALSE;
3314   ELSE
3315     --Item is present, retrieve the item's expense account.
3316     OPEN get_exp_acct_item_csr(p_inv_org_id, p_item_id);
3317     FETCH get_exp_acct_item_csr INTO l_charge_acct_id;
3318     IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3319       FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, L_DEBUG_KEY, 'l_charge_acct_id from item: '|| l_charge_acct_id);
3320     END IF;
3321     CLOSE get_exp_acct_item_csr;
3322     IF(l_charge_acct_id is not NULL) THEN
3323       l_item_account_set := TRUE;
3324     ELSE
3325       --Expense account not set at the Item Level.
3326       l_item_account_set := FALSE;
3327     END IF;
3328 
3329   END IF;
3330 
3331   IF (l_item_account_set = FALSE) THEN
3332     --Retrieve the Org's Material Account Id
3333     OPEN get_mtl_acct_org_csr(p_inv_org_id);
3334     FETCH get_mtl_acct_org_csr INTO l_charge_acct_id;
3335     IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3336       FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, L_DEBUG_KEY, 'l_charge_acct_id from Org: '|| l_charge_acct_id);
3337     END IF;
3338     CLOSE get_mtl_acct_org_csr;
3339   END IF;
3340 
3341   IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3342     FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, L_DEBUG_KEY, 'l_charge_acct_id: '|| l_charge_acct_id);
3343   END IF;
3344 
3345   RETURN l_charge_acct_id;
3346 
3347 END get_charge_account_id;
3348 --mpothuku End
3349 
3350 -- SATHAPLI::Bug 8583364, 21-Aug-2009
3351 -- Function to create PO number, if PO numbering is set to 'MANUAL'. Accordingly, returns the created number, or NULL.
3352 -- Creates PO number as the concatenation of the following:
3353 -- 1) Profile 'AHL_OSP_PO_NUMBER_PREFIX' value - NULL or otherwise (only if 'MANUAL' PO numbering is set to 'ALPHANUMERIC' type).
3354 -- 2) OSP Order Number.
3355 FUNCTION create_manual_PO_Number
3356 (
3357   p_org_id       IN NUMBER,
3358   p_osp_order_id IN NUMBER
3359 ) RETURN VARCHAR2 IS
3360 
3361   -- cursor to check if PO numbering is set to 'MANUAL'
3362   CURSOR chk_manual_PO_Numbering (c_org_id NUMBER) IS
3363     SELECT 'X'
3364     FROM   PO_SYSTEM_PARAMETERS_ALL
3365     WHERE  user_defined_po_num_code = 'MANUAL'
3366     AND    org_id                   = c_org_id;
3367 
3368   -- cursor to check if 'MANUAL' PO numbering type is set to 'ALPHANUMERIC'
3369   CURSOR chk_manual_PO_Num_Type (c_org_id NUMBER) IS
3370     SELECT 'X'
3371     FROM   PO_SYSTEM_PARAMETERS_ALL
3372     WHERE  manual_po_num_type = 'ALPHANUMERIC'
3373     AND    org_id             = c_org_id;
3374 
3375   -- cursor to get the OSP order number for a given order id
3376   CURSOR get_OSP_Order_Number (c_order_id NUMBER) IS
3377     SELECT osp_order_number
3378     FROM   AHL_OSP_ORDERS_B
3379     WHERE  osp_order_id = c_order_id;
3380 
3381   l_debug_key     CONSTANT VARCHAR2(150)                          := G_LOG_PREFIX || '.create_manual_PO_Number';
3382   l_manual_po_number       PO_HEADERS_INTERFACE.DOCUMENT_NUM%TYPE := NULL;
3383   l_osp_order_number       NUMBER;
3384   l_dummy                  VARCHAR2(1);
3385 
3386 BEGIN
3387 
3388   -- check if PO numbering is set to 'MANUAL'
3389   OPEN chk_manual_PO_Numbering(p_org_id);
3390   FETCH chk_manual_PO_Numbering INTO l_dummy;
3391   IF (chk_manual_PO_Numbering%FOUND) THEN
3392     -- fetch the OSP order number for the OSP order id
3393     OPEN get_OSP_Order_Number(p_osp_order_id);
3394     FETCH get_OSP_Order_Number INTO l_osp_order_number;
3395     CLOSE get_OSP_Order_Number;
3396 
3397     -- if l_manual_po_number to be created exceeds the DB precision, raise error
3398     BEGIN
3399       -- check if 'MANUAL' PO numbering type is set to 'ALPHANUMERIC'
3400       OPEN chk_manual_PO_Num_Type(p_org_id);
3401       FETCH chk_manual_PO_Num_Type INTO l_dummy;
3402       IF (chk_manual_PO_Num_Type%FOUND) THEN
3403         -- prepend profile 'AHL_OSP_PO_NUMBER_PREFIX' value
3404         l_manual_po_number := FND_PROFILE.VALUE('AHL_OSP_PO_NUMBER_PREFIX');
3405       END IF;
3406       CLOSE chk_manual_PO_Num_Type;
3407 
3408       l_manual_po_number := l_manual_po_number || l_osp_order_number;
3409     EXCEPTION
3410       WHEN OTHERS THEN
3411         IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3412           FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, l_debug_key,
3413                          'l_manual_po_number exceeded PO_HEADERS_INTERFACE.DOCUMENT_NUM%TYPE precision.');
3414         END IF;
3415 
3416         CLOSE chk_manual_PO_Numbering;
3417 
3418         FND_MESSAGE.SET_NAME('AHL', 'AHL_COM_FIGURE_LENGTH_INVALID'); -- Entered value for Figure exceeded the limit
3419         FND_MSG_PUB.ADD;
3420         RAISE;
3421     END;
3422   END IF;
3423   CLOSE chk_manual_PO_Numbering;
3424 
3425   IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3426     FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, l_debug_key,
3427                    'l_manual_po_number to be returned => '||l_manual_po_number);
3428   END IF;
3429 
3430   RETURN l_manual_po_number;
3431 
3432 END create_manual_PO_Number;
3433 
3434 -------------------------------
3435 -- End Local Procedures --
3436 -------------------------------
3437 
3438 END AHL_OSP_PO_PVT;