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