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;