1 PACKAGE BODY AHL_OSP_PO_REQ_PVT AS
2 /* $Header: AHLVPRQB.pls 120.3 2008/04/08 23:13:25 jaramana noship $ */
3
4 G_PKG_NAME CONSTANT VARCHAR2(30) := 'AHL_OSP_PO_REQ_PVT';
5 G_LOG_PREFIX CONSTANT VARCHAR2(100) := 'ahl.plsql.AHL_OSP_PO_REQ_PVT';
6
7 G_NO_FLAG CONSTANT VARCHAR2(1) := 'N';
8 G_YES_FLAG CONSTANT VARCHAR2(1) := 'Y';
9
10 G_PO_APP_CODE CONSTANT VARCHAR2(2) := 'PO';
11 G_REQIMPORT_PROG_CODE CONSTANT VARCHAR2(30) := 'REQIMPORT';
12
13 -- OSP Order Statuses
14 G_OSP_ENTERED_STATUS CONSTANT VARCHAR2(30) := 'ENTERED';
15 G_OSP_SUBMITTED_STATUS CONSTANT VARCHAR2(30) := 'SUBMITTED';
16 G_OSP_SUB_FAILED_STATUS CONSTANT VARCHAR2(30) := 'SUBMISSION_FAILED';
17 G_OSP_REQ_SUB_FAILED_STATUS CONSTANT VARCHAR2(30) := 'REQ_SUBMISSION_FAILED';
18 G_OSP_PO_CREATED_STATUS CONSTANT VARCHAR2(30) := 'PO_CREATED';
19 G_OSP_REQ_CREATED_STATUS CONSTANT VARCHAR2(30) := 'REQ_CREATED';
20 G_OSP_REQ_SUBMITTED_STATUS CONSTANT VARCHAR2(30) := 'REQ_SUBMITTED';
21 G_OSP_CLOSED_STATUS CONSTANT VARCHAR2(30) := 'CLOSED';
22
23 -- OSP Order Line Statuses
24 G_OL_REQ_CANCELLED_STATUS CONSTANT VARCHAR2(30) := 'REQ_CANCELLED';
25 G_OL_REQ_DELETED_STATUS CONSTANT VARCHAR2(30) := 'REQ_DELETED';
26
27 -- Log Constants: Transaction Types
28 G_TXN_TYPE_PO_REQ_CREATION CONSTANT VARCHAR2(30) := 'Requisition Creation';
29 G_TXN_TYPE_PO_SYNCH CONSTANT VARCHAR2(30) := 'PO Synchronization';
30
31 -- Log Constants: Document Types
32 G_DOC_TYPE_OSP CONSTANT VARCHAR2(30) := 'OSP';
33 G_DOC_TYPE_PO CONSTANT VARCHAR2(30) := 'PO';
34 G_DOC_TYPE_PO_REQ CONSTANT VARCHAR2(30) := 'REQ';
35
36 -- Default Values for One-time Items
37 G_DEFAULT_PRICE CONSTANT NUMBER := 0;
38 G_DEFAULT_CATEGORY_SEG1 CONSTANT VARCHAR2(40) := 'MISC';
39 G_DEFAULT_CATEGORY_SEG2 CONSTANT VARCHAR2(40) := 'MISC';
40
41 -- PO Line Types
42 G_PO_LINE_TYPE_QUANTITY CONSTANT VARCHAR2(30) := 'QUANTITY';
43
44 -------------------------------------------------
45 -- Declare Locally used Record and Table Types --
46 -------------------------------------------------
47
48 TYPE PO_Req_Header_Rec_Type IS RECORD (
49 OSP_ORDER_ID NUMBER,
50 OPERATING_UNIT_ID NUMBER,
51 VENDOR_ID NUMBER,
52 VENDOR_SITE_ID NUMBER,
53 BUYER_ID NUMBER,
54 DESCRIPTION VARCHAR2(240),
55 VENDOR_CONTACT_ID NUMBER
56 );
57
58 TYPE PO_Req_Line_Rec_Type IS RECORD (
59 OSP_LINE_ID NUMBER,
60 OBJECT_VERSION_NUMBER NUMBER,
61 LINE_NUMBER NUMBER,
62 PO_LINE_TYPE_ID NUMBER,
63 ITEM_ID NUMBER,
64 ITEM_DESCRIPTION VARCHAR2(240),
65 QUANTITY NUMBER,
66 UOM_CODE VARCHAR2(3),
67 NEED_BY_DATE DATE,
68 SHIP_TO_ORG_ID NUMBER,
69 SHIP_TO_LOC_ID NUMBER,
70 WIP_ENTITY_ID NUMBER,
71 PROJECT_ID NUMBER,
72 TASK_ID NUMBER
73 );
74
75 TYPE PO_Req_Line_Tbl_Type IS TABLE OF PO_Req_Line_Rec_Type INDEX BY BINARY_INTEGER;
76
77 ------------------------------
78 -- Declare Local Procedures --
79 ------------------------------
80
81 -- Validate OSP Order for Req Header Creation
82 PROCEDURE Validate_PO_Req_Header(
83 p_po_Req_header_rec IN PO_Req_Header_Rec_Type);
84
85 -- Validate Requisition Lines
86 PROCEDURE Validate_PO_Req_Lines(
87 p_po_req_line_tbl IN PO_Req_Line_Tbl_Type,
88 p_osp_order_id IN NUMBER);
89
90 -- Insert a record into the PO_REQUISITIONS_INTERFACE_ALL table
91 PROCEDURE Insert_Into_Req_Interface(
92 p_po_req_hdr_rec IN PO_Req_Header_Rec_Type,
93 p_po_req_line_tbl IN PO_Req_Line_Tbl_Type,
94 x_batch_id OUT NOCOPY NUMBER);
95
96 -- Calls the Concurrent Program to Create Requisition
97 PROCEDURE Call_Req_Import_Program(
98 p_batch_id IN NUMBER,
99 p_osp_order_id IN NUMBER,
100 x_request_id OUT NOCOPY NUMBER);
101
102 -- This Procedure updates a record of AHL_OSP_ORDERS_B using the table handler.
103 -- All updates to this table from this Package should go through this procedure only
104 PROCEDURE Update_OSP_Order(
105 p_osp_order_id IN NUMBER,
106 p_po_req_header_id IN NUMBER := NULL,
107 p_batch_id IN NUMBER := NULL,
108 p_request_id IN NUMBER := NULL,
109 p_status_code IN VARCHAR2 := NULL
110 );
111
112 -- This Local Procedure updates OSP Tables with Requisition Information for one OSP Order
113 PROCEDURE Associate_New_Req(
114 p_osp_order_id IN NUMBER,
115 x_po_req_header_id OUT NOCOPY NUMBER);
116
117 -- This Procedure updates AHL_OSP_ORDERS_B's PO_REQ_HEADER_ID and sets STATUS_CODE to REQUISITION_CREATED
118 PROCEDURE Set_PO_Req_Header_ID(
119 p_osp_order_id IN NUMBER,
120 p_po_req_header_id IN NUMBER);
121
122 -- This Procedure updates AHL_OSP_ORDER_LINES.P_PO_REQ_LINE_ID
123 PROCEDURE Update_Osp_Order_Lines(
124 p_osp_order_line_id IN NUMBER,
125 p_po_req_line_id IN NUMBER := NULL);
126
127 -- This Procedure updates AHL_OSP_ORDERS_B.STATUS_CODE to SUBMISSION_FAILED
128 PROCEDURE Set_Req_Submission_Failed(
129 p_osp_order_id IN NUMBER);
130
131 -- This Procedure handles deleted Requisition Headers from Purchasing and is Part of PO Synchronization.
132 -- This procedure commits its work if p_commit is set to true and
133 -- if there were no errors during the execution of this procedure.
134 -- It does not check the message list for performing the commit action
135 PROCEDURE Handle_Deleted_Req_Headers(
136 p_commit IN VARCHAR2,
137 x_return_status OUT NOCOPY VARCHAR2);
138
139 -- This Procedure handles cancelled Req Lines and is Part of PO Synchronization.
140 -- This procedure commits its work if p_commit is set to true and
141 -- if there were no errors during the execution of this procedure.
142 -- It does not check the message list for performing the commit action
143 PROCEDURE Handle_Cancelled_Req_Lines(
144 p_commit IN VARCHAR2,
145 x_return_status OUT NOCOPY VARCHAR2);
146
147 -- This Procedure handles deleted Req Lines and is Part of PO Synchronization.
148 -- This procedure commits its work if p_commit is set to true and
149 -- if there were no errors during the execution of this procedure.
150 -- It does not check the message list for performing the commit action
151 PROCEDURE Handle_Deleted_Req_Lines(
152 p_commit IN VARCHAR2,
153 x_return_status OUT NOCOPY VARCHAR2);
154
155 -- Helper function to get the Charge Account Id to fill
156 -- po_requisitions_interface.CHARGE_ACCOUNT_ID, given the Item and Org.
157 FUNCTION get_charge_account_id
158 (
159 p_inv_org_id IN NUMBER,
160 p_item_id IN NUMBER
161 ) RETURN NUMBER;
162
163 -----------------------------------------
164 -- Public Procedure Definitions follow --
165 -----------------------------------------
166 -- Start of Comments --
167 -- Procedure name : Create_PO_Requisition
168 -- Type : Private
169 -- Function : Validates OSP Information and inserts records into PO Requisition Interface tables
170 -- Launches Concurrent Program to initiate PO Requisition creation
171 -- Updates OSP table with request id, batch id and interface header id
172 -- Pre-reqs :
173 -- Parameters :
174 --
175 -- Standard IN Parameters :
176 -- p_api_version IN NUMBER Required
177 -- p_init_msg_list IN VARCHAR2 Default FND_API.G_FALSE
178 -- p_commit IN VARCHAR2 Default FND_API.G_FALSE
179 -- p_validation_level IN NUMBER Default FND_API.G_VALID_LEVEL_FULL
180 -- p_default IN VARCHAR2 Default FND_API.G_TRUE
181 -- p_module_type IN VARCHAR2 Default NULL.
182 --
183 -- Standard OUT Parameters :
184 -- x_return_status OUT VARCHAR2 Required
185 -- x_msg_count OUT NUMBER Required
186 -- x_msg_data OUT VARCHAR2 Required
187 --
188 -- Create_PO_Requisition Parameters:
189 -- p_osp_order_id IN NUMBER Required
190 -- The Id of the OSP Order for which to create the Purchase Requisition
191 -- p_osp_order_number IN NUMBER Required
192 -- The Number of the OSP Order for which to create the Purchase Requisition
193 -- x_batch_id OUT NUMBER Required
194 -- Contains the batch id if the concurrent program was launched successfuly.
195 -- x_request_id OUT NUMBER Required
196 -- Contains the concurrent request id if the concurrent program was launched successfuly.
197 --
198 --
199 -- Version :
200 -- Initial Version 1.0
201 --
202 -- End of Comments.
203
204 PROCEDURE Create_PO_Requisition
205 (
206 p_api_version IN NUMBER,
207 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
208 p_commit IN VARCHAR2 := FND_API.G_FALSE,
209 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
210 p_module_type IN VARCHAR2 := NULL,
211 p_osp_order_id IN NUMBER := NULL, -- Required if Number is not given
212 p_osp_order_number IN NUMBER := NULL, -- Required if Id is not given
213 x_batch_id OUT NOCOPY NUMBER,
214 x_request_id OUT NOCOPY NUMBER,
215 x_return_status OUT NOCOPY VARCHAR2,
216 x_msg_count OUT NOCOPY NUMBER,
217 x_msg_data OUT NOCOPY VARCHAR2) IS
218
219 l_api_version CONSTANT NUMBER := 1.0;
220 l_api_name CONSTANT VARCHAR2(30) := 'Create_PO_Requisition';
221 L_DEBUG_KEY CONSTANT VARCHAR2(150) := G_LOG_PREFIX || '.Create_PO_Requisition';
222
223 CURSOR l_osp_id_csr(p_osp_order_number IN NUMBER) IS
224 SELECT OSP_ORDER_ID
225 FROM AHL_OSP_ORDERS_B
226 WHERE OSP_ORDER_NUMBER = p_osp_order_number;
227
228 CURSOR l_osp_dtls_csr(p_osp_order_id IN NUMBER) IS
229 SELECT B.VENDOR_ID,
230 B.VENDOR_SITE_ID,
231 B.OPERATING_UNIT_ID,
232 B.PO_AGENT_ID,
233 B.PO_BATCH_ID,
234 TL.DESCRIPTION,
235 B.PO_INTERFACE_HEADER_ID,
236 B.VENDOR_CONTACT_ID,
237 B.STATUS_CODE
238 FROM AHL_OSP_ORDERS_B B, AHL_OSP_ORDERS_TL TL
239 WHERE B.OSP_ORDER_ID = p_osp_order_id
240 AND TL.OSP_ORDER_ID = B.OSP_ORDER_ID
241 AND TL.LANGUAGE = userenv('LANG');
242
243 CURSOR l_osp_line_dtls_csr(p_osp_order_id IN NUMBER) IS
244 SELECT OL.OSP_ORDER_LINE_ID,
245 OL.OBJECT_VERSION_NUMBER,
246 OL.OSP_LINE_NUMBER,
247 OL.SERVICE_ITEM_ID,
248 OL.SERVICE_ITEM_DESCRIPTION,
249 OL.QUANTITY,
250 OL.NEED_BY_DATE,
251 OL.SERVICE_ITEM_UOM_CODE,
252 OL.PO_LINE_TYPE_ID,
253 OL.INVENTORY_ORG_ID,
254 DECODE(OL.WORKORDER_ID, NULL, HAOU.LOCATION_ID, BOM.LOCATION_ID),
255 WO.WIP_ENTITY_ID,
256 WDJ.PROJECT_ID,
257 WDJ.TASK_ID
258 FROM AHL_OSP_ORDER_LINES OL, AHL_WORKORDERS WO, BOM_DEPARTMENTS BOM, HR_ALL_ORGANIZATION_UNITS HAOU, WIP_DISCRETE_JOBS WDJ
259 WHERE OL.OSP_ORDER_ID = p_osp_order_id AND
260 WO.WORKORDER_ID (+) = OL.WORKORDER_ID AND
261 WDJ.WIP_ENTITY_ID (+) = WO.WIP_ENTITY_ID AND
262 BOM.DEPARTMENT_ID (+) = WDJ.OWNING_DEPARTMENT AND
263 HAOU.ORGANIZATION_ID = OL.INVENTORY_ORG_ID
264 ORDER BY OL.OSP_LINE_NUMBER;
265
266 CURSOR get_return_to_org_csr(p_osp_line_id IN NUMBER) IS
267 SELECT oola.ship_from_org_id, HAOU.LOCATION_ID
268 FROM oe_order_lines_all oola, ahl_osp_order_lines aool, HR_ALL_ORGANIZATION_UNITS HAOU
269 WHERE oola.line_id = aool.oe_return_line_id and
270 HAOU.ORGANIZATION_ID = oola.ship_from_org_id and
271 aool.osp_order_line_id = p_osp_line_id;
272
273 l_po_req_header PO_Req_Header_Rec_Type;
274 l_po_req_line_tbl PO_Req_Line_Tbl_Type;
275 l_intf_hdr_id NUMBER;
276 l_batch_id NUMBER;
277 l_old_batch_id NUMBER := null;
278 l_old_intf_header_id NUMBER := null;
279 l_request_id NUMBER := 0;
280 l_temp_num NUMBER := 0;
281 l_temp_ret_org_id NUMBER;
282 l_temp_ret_org_loc_id NUMBER;
283 l_status VARCHAR2(30);
284
285 BEGIN
286
287 -- Standard start of API savepoint
288 SAVEPOINT Create_PO_Requisition_pvt;
289
290 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
291 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, L_DEBUG_KEY || '.begin', 'Entering Procedure');
292 END IF;
293
294 -- Standard call to check for call compatibility
295 IF NOT FND_API.Compatible_API_Call(l_api_version, p_api_version, l_api_name,
296 G_PKG_NAME) THEN
297 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
298 END IF;
299
300 -- Initialize message list if p_init_msg_list is set to TRUE
301 IF FND_API.To_Boolean(p_init_msg_list) THEN
302 FND_MSG_PUB.Initialize;
303 END IF;
304
305 -- Initialize API return status to success
306 x_return_status := FND_API.G_RET_STS_SUCCESS;
307
308 -- Start processing
309
310 -- Create the Header Rec
311 IF (p_osp_order_id IS NOT NULL) THEN
312 l_po_req_header.OSP_ORDER_ID := p_osp_order_id;
313 ELSE
314 -- Convert the Order number to Id
315 OPEN l_osp_id_csr(p_osp_order_number);
316 FETCH l_osp_id_csr INTO l_po_req_header.OSP_ORDER_ID;
317 CLOSE l_osp_id_csr;
318 END IF;
319
320 OPEN l_osp_dtls_csr(l_po_req_header.OSP_ORDER_ID);
321 FETCH l_osp_dtls_csr INTO l_po_req_header.VENDOR_ID,
322 l_po_req_header.VENDOR_SITE_ID,
323 l_po_req_header.OPERATING_UNIT_ID,
324 l_po_req_header.BUYER_ID,
325 l_old_batch_id, -- For Purging Interface table records
326 l_po_req_header.DESCRIPTION,
327 l_old_intf_header_id, -- For Purging Interface table records
328 l_po_req_header.VENDOR_CONTACT_ID,
329 l_status;
330 CLOSE l_osp_dtls_csr;
331
332 -- Validate Header
333 Validate_PO_Req_Header(l_po_req_header);
334 -- Create the Lines Table
335 OPEN l_osp_line_dtls_csr(p_osp_order_id);
336 LOOP
337 FETCH l_osp_line_dtls_csr INTO l_po_req_line_tbl(l_temp_num).OSP_LINE_ID,
338 l_po_req_line_tbl(l_temp_num).OBJECT_VERSION_NUMBER,
339 l_po_req_line_tbl(l_temp_num).LINE_NUMBER,
340 l_po_req_line_tbl(l_temp_num).ITEM_ID,
341 l_po_req_line_tbl(l_temp_num).ITEM_DESCRIPTION,
342 l_po_req_line_tbl(l_temp_num).QUANTITY,
343 l_po_req_line_tbl(l_temp_num).NEED_BY_DATE,
344 l_po_req_line_tbl(l_temp_num).UOM_CODE,
345 l_po_req_line_tbl(l_temp_num).PO_LINE_TYPE_ID,
346 l_po_req_line_tbl(l_temp_num).SHIP_TO_ORG_ID,
347 l_po_req_line_tbl(l_temp_num).SHIP_TO_LOC_ID,
348 l_po_req_line_tbl(l_temp_num).WIP_ENTITY_ID,
349 l_po_req_line_tbl(l_temp_num).PROJECT_ID,
350 l_po_req_line_tbl(l_temp_num).TASK_ID;
351
352 EXIT WHEN l_osp_line_dtls_csr%NOTFOUND;
353
354 OPEN get_return_to_org_csr(l_po_req_line_tbl(l_temp_num).OSP_LINE_ID);
355 FETCH get_return_to_org_csr INTO l_temp_ret_org_id, l_temp_ret_org_loc_id;
356 IF (get_return_to_org_csr%FOUND AND l_temp_ret_org_id IS NOT NULL) THEN
357 IF (l_temp_ret_org_id <> l_po_req_line_tbl(l_temp_num).SHIP_TO_ORG_ID) THEN
358 l_po_req_line_tbl(l_temp_num).SHIP_TO_ORG_ID := l_temp_ret_org_id;
359 -- Update the Ship To Location also from the Line's Inventory Org
360 -- if the Return To Org is different
361 l_po_req_line_tbl(l_temp_num).SHIP_TO_LOC_ID := l_temp_ret_org_loc_id;
362 END IF;
363 END IF;
364 CLOSE get_return_to_org_csr;
365
366 l_temp_num := l_temp_num + 1;
367 END LOOP;
368 CLOSE l_osp_line_dtls_csr;
369 l_po_req_line_tbl.DELETE(l_temp_num); -- Delete the last (null) record
370 -- Validate Lines
371 Validate_PO_Req_Lines(l_po_req_line_tbl, l_po_req_header.OSP_ORDER_ID);
372 -- Check Error Message stack.
373 x_msg_count := FND_MSG_PUB.count_msg;
374 IF x_msg_count > 0 THEN
375 RAISE FND_API.G_EXC_ERROR;
376 END IF;
377
378 -- Insert Rows into PO_REQUISITIONS_INTERFACE_ALL
379
380 -- Purge Error records from prior submission (if any)
381 IF (l_status = G_OSP_REQ_SUB_FAILED_STATUS) THEN
382 DELETE FROM po_interface_errors
383 WHERE INTERFACE_TRANSACTION_ID in
384 (SELECT transaction_id
385 FROM po_requisitions_interface_all
386 WHERE INTERFACE_SOURCE_CODE = AHL_GLOBAL.AHL_APP_SHORT_NAME
387 AND INTERFACE_SOURCE_LINE_ID = l_po_req_header.OSP_ORDER_ID);
388 DELETE FROM po_requisitions_interface_all
389 WHERE INTERFACE_SOURCE_CODE = AHL_GLOBAL.AHL_APP_SHORT_NAME
390 AND INTERFACE_SOURCE_LINE_ID = l_po_req_header.OSP_ORDER_ID;
391
392 ELSIF(l_status = G_OSP_SUB_FAILED_STATUS and l_old_intf_header_id IS NOT NULL) THEN
393 -- Earlier, User tried to create a PO (not a requisition) and that failed.
394 DELETE FROM PO_INTERFACE_ERRORS WHERE
395 INTERFACE_HEADER_ID = l_old_intf_header_id;
396 DELETE FROM PO_HEADERS_INTERFACE WHERE
397 INTERFACE_HEADER_ID = l_old_intf_header_id;
398 DELETE FROM PO_LINES_INTERFACE WHERE
399 INTERFACE_HEADER_ID = l_old_intf_header_id;
400 END IF;
401
402 Insert_Into_Req_Interface(p_po_req_hdr_rec => l_po_req_header,
403 p_po_req_line_tbl => l_po_req_line_tbl,
404 x_batch_id => l_batch_id);
405
406 -- Launch Concurrent Program to create PO Requisition
407 Call_Req_Import_Program(p_batch_id => l_batch_id,
408 p_osp_order_id => l_po_req_header.OSP_ORDER_ID,
409 x_request_id => l_request_id);
410 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
411 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, L_DEBUG_KEY, 'PO Req Concurrent Program Request Submitted. Request Id = ' || l_request_id);
412 END IF;
413 -- Check if request was submitted without error
414 IF (l_request_id = 0) THEN
415 -- Add Error Message generated by Concurrent Manager to Message List
416 FND_MSG_PUB.ADD;
417 IF (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
418 FND_LOG.MESSAGE(FND_LOG.LEVEL_UNEXPECTED, L_DEBUG_KEY, FALSE);
419 END IF;
420 RAISE FND_API.G_EXC_ERROR;
421 END IF;
422
423 -- Update OSP Table with batch id, request id
424 Update_OSP_Order(p_osp_order_id => p_osp_order_id,
425 p_batch_id => l_batch_id,
426 p_request_id => l_request_id);
427
428 -- Set Return parameters
429 x_batch_id := l_batch_id;
430 x_request_id := l_request_id;
431
432 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
433 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, L_DEBUG_KEY, 'Completed Processing. Checking for errors');
434 END IF;
435 -- Check Error Message stack.
436 x_msg_count := FND_MSG_PUB.count_msg;
437 IF x_msg_count > 0 THEN
438 RAISE FND_API.G_EXC_ERROR;
439 END IF;
440
441 -- Log this transaction in the Log Table
442 AHL_OSP_UTIL_PKG.Log_Transaction(p_trans_type_code => G_TXN_TYPE_PO_REQ_CREATION,
443 p_src_doc_id => p_osp_order_id,
444 p_src_doc_type_code => G_DOC_TYPE_OSP,
445 p_dest_doc_id => l_batch_id,
446 p_dest_doc_type_code => G_DOC_TYPE_PO_REQ);
447
448 -- Standard check of p_commit
449 IF FND_API.TO_BOOLEAN(p_commit) THEN
450 COMMIT WORK;
451 END IF;
452
453 -- Standard call to get message count and if count is 1, get message info
454 FND_MSG_PUB.Count_And_Get
455 ( p_count => x_msg_count,
456 p_data => x_msg_data,
457 p_encoded => fnd_api.g_false
458 );
459
460 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
461 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, L_DEBUG_KEY || '.end', 'Exiting Procedure');
462 END IF;
463
464 EXCEPTION
465 WHEN FND_API.G_EXC_ERROR THEN
466 ROLLBACK TO Create_PO_Requisition_pvt;
467 x_return_status := FND_API.G_RET_STS_ERROR;
468 FND_MSG_PUB.count_and_get( p_count => x_msg_count,
469 p_data => x_msg_data,
470 p_encoded => fnd_api.g_false);
471
472 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
473 ROLLBACK TO Create_PO_Requisition_pvt;
474 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
475 FND_MSG_PUB.count_and_get( p_count => x_msg_count,
476 p_data => x_msg_data,
477 p_encoded => fnd_api.g_false);
478
479 WHEN OTHERS THEN
480 ROLLBACK TO Create_PO_Requisition_pvt;
481 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
482 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
483 fnd_msg_pub.add_exc_msg(p_pkg_name => G_PKG_NAME,
484 p_procedure_name => 'Create_PO_Requisition',
485 p_error_text => SUBSTR(SQLERRM,1,240));
486 END IF;
487 FND_MSG_PUB.count_and_get( p_count => x_msg_count,
488 p_data => x_msg_data,
489 p_encoded => fnd_api.g_false);
490
491
492 END Create_PO_Requisition;
493
494 -- Start of Comments --
495 -- Procedure name : PO_Synch_All_Requisitions
496 -- Type : Private
497 -- Function : Synchronizes all OSPs based on the Requisition Status
498 -- 1. Handles successfully completed Requisition Submissions (Updates OSP tables)
499 -- 2. Handles failed Requisition Submissions (Updates OSP Status)
500 -- 3. Handles Cancelled Requisition Lines (Updates OSP Line status, deletes shipment lines)
501 -- 4. Handles Deleted Requisition Lines (Updates OSP Line status, deletes shipment lines)
502 -- Pre-reqs :
503 -- Parameters :
504 --
505 -- Standard IN Parameters :
506 -- p_api_version IN NUMBER Required
507 -- p_init_msg_list IN VARCHAR2 Default FND_API.G_FALSE
508 -- p_commit IN VARCHAR2 Default FND_API.G_FALSE
509 -- p_validation_level IN NUMBER Default FND_API.G_VALID_LEVEL_FULL
510 -- p_default IN VARCHAR2 Default FND_API.G_TRUE
511 -- p_module_type IN VARCHAR2 Default NULL.
512 --
513 -- Standard OUT Parameters :
514 -- x_return_status OUT VARCHAR2 Required
515 -- x_msg_count OUT NUMBER Required
516 -- x_msg_data OUT VARCHAR2 Required
517 --
518 -- PO_Synch_All_Requisitions parameters:
519 -- p_concurrent_flag IN VARCHAR2 Default N.
520 -- Writes debug Information to Concurrent Program's Log File if set to 'Y'
521 --
522 -- Version :
523 -- Initial Version 1.0
524 --
525 -- End of Comments.
526
527 PROCEDURE PO_Synch_All_Requisitions
528 (
529 p_api_version IN NUMBER,
530 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
531 p_commit IN VARCHAR2 := FND_API.G_FALSE,
532 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
533 p_default IN VARCHAR2 := FND_API.G_TRUE,
534 p_module_type IN VARCHAR2 := NULL,
535 p_concurrent_flag IN VARCHAR2 := 'N',
536 x_return_status OUT NOCOPY VARCHAR2,
537 x_msg_count OUT NOCOPY NUMBER,
538 x_msg_data OUT NOCOPY VARCHAR2) IS
539
540 l_api_version CONSTANT NUMBER := 1.0;
541 l_api_name CONSTANT VARCHAR2(30) := 'PO_Synch_All_Requisitions';
542 L_DEBUG_KEY CONSTANT VARCHAR2(150) := G_LOG_PREFIX || '.PO_Synch_All_Requisitions';
543
544 BEGIN
545 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
546 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, L_DEBUG_KEY || '.begin', 'Entering Procedure');
547 END IF;
548
549 -- No need of a Savepoint: Individual procedures commit or rollback
550 -- within themselves.
551
552 -- Standard call to check for call compatibility
553 IF NOT FND_API.Compatible_API_Call(l_api_version, p_api_version, l_api_name,
554 G_PKG_NAME) THEN
555 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
556 END IF;
557
558 -- Initialize message list if p_init_msg_list is set to TRUE
559 IF FND_API.To_Boolean(p_init_msg_list) THEN
560 FND_MSG_PUB.Initialize;
561 END IF;
562
563 -- Initialize API return status to success
564 x_return_status := FND_API.G_RET_STS_SUCCESS;
565
566 IF FND_API.to_boolean( p_default ) THEN
567 -- No special default settings required in this API
568 NULL;
569 END IF;
570
571 -- Start processing
572 IF (p_concurrent_flag = 'Y') THEN
573 fnd_file.put_line(fnd_file.log, 'Starting Requisition Synch process...');
574 END IF;
575
576 -- First make all associations (PO Req Header Id, PO Req Line Id, Status updates)
577 Associate_All_New_Reqs(p_api_version => 1.0,
578 p_commit => p_commit, --Commit this independent of other operations
579 x_return_status => x_return_status,
580 x_msg_count => x_msg_count,
581 x_msg_data => x_msg_data);
582 IF (p_concurrent_flag = 'Y') THEN
583 fnd_file.put_line(fnd_file.log, 'Completed Associating OSPs with POs');
584 fnd_file.put_line(fnd_file.log, 'Return Status = ' || x_return_status);
585 END IF;
586 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
587 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, L_DEBUG_KEY, 'Completed Associating OSPs with Requisitions, Return Status = ' || x_return_status);
588 END IF;
589
590 Handle_Deleted_Req_Headers(p_commit => p_commit, --Commit this independent of other operations
591 x_return_status => x_return_status);
592 IF (p_concurrent_flag = 'Y') THEN
593 fnd_file.put_line(fnd_file.log, 'Completed Handling Deleted PO Requisition Headers.');
594 fnd_file.put_line(fnd_file.log, 'Return Status = ' || x_return_status);
595 END IF;
596 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
597 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, L_DEBUG_KEY, 'Completed Handling Deleted PO Requisition Headers, Return Status = ' || x_return_status);
598 END IF;
599
600
601 -- Handle Canceled PO Requisitions
602 Handle_Cancelled_Req_Lines(p_commit => p_commit, --Commit this independent of other operations
603 x_return_status => x_return_status);
604 IF (p_concurrent_flag = 'Y') THEN
605 fnd_file.put_line(fnd_file.log, 'Completed Handling Cancelled PO Requisition Lines.');
606 fnd_file.put_line(fnd_file.log, 'Return Status = ' || x_return_status);
607 END IF;
608 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
609 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, L_DEBUG_KEY, 'Completed Handling Cancelled PO Requisition Lines, Return Status = ' || x_return_status);
610 END IF;
611
612 -- Handle Deleted PO Requisition Lines
613 Handle_Deleted_Req_Lines(p_commit => p_commit, --Commit this independent of other operations
614 x_return_status => x_return_status);
615 IF (p_concurrent_flag = 'Y') THEN
616 fnd_file.put_line(fnd_file.log, 'Completed Handling Deleted PO Requisition Lines.');
617 fnd_file.put_line(fnd_file.log, 'Return Status = ' || x_return_status);
618 END IF;
619 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
620 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, L_DEBUG_KEY, 'Completed Handling Deleted PO Requisition Lines, Return Status = ' || x_return_status);
621 END IF;
622
623 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
624 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, L_DEBUG_KEY, 'Completed Processing. Checking for errors.');
625 END IF;
626
627 IF (p_concurrent_flag = 'Y') THEN
628 fnd_file.put_line(fnd_file.log, 'Completed Requisition Synch Process. Checking for errors.');
629 END IF;
630 -- Check Error Message stack.
631 x_msg_count := FND_MSG_PUB.count_msg;
632 IF x_msg_count > 0 THEN
633 RAISE FND_API.G_EXC_ERROR; --Note that commit might already have taken place
634 END IF;
635
636 -- Standard check of p_commit
637 -- No need to commit: Individual procedures commit or rollback
638 -- within themselves.
639 -- IF FND_API.TO_BOOLEAN(p_commit) THEN
640 -- COMMIT WORK; --Note that commit might already have taken place
641 -- END IF;
642
643 -- Standard call to get message count and if count is 1, get message info
644 FND_MSG_PUB.Count_And_Get
645 ( p_count => x_msg_count,
646 p_data => x_msg_data,
647 p_encoded => fnd_api.g_false
648 );
649
650 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
651 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, L_DEBUG_KEY || '.end', 'Exiting Procedure');
652 END IF;
653
654 EXCEPTION
655 WHEN FND_API.G_EXC_ERROR THEN
656 -- No need to rollback: Individual procedures commit or rollback
657 -- within themselves.
658 -- ROLLBACK TO PO_Synch_All_OSPs_pvt; --Note that commit might already have taken place
659 x_return_status := FND_API.G_RET_STS_ERROR;
660 FND_MSG_PUB.count_and_get( p_count => x_msg_count,
661 p_data => x_msg_data,
662 p_encoded => fnd_api.g_false);
663 IF (p_concurrent_flag = 'Y') THEN
664 fnd_file.put_line(fnd_file.log, 'Caught Execution Exception: ' || x_msg_data);
665 END IF;
666 IF (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
667 FND_LOG.STRING(FND_LOG.LEVEL_UNEXPECTED, L_DEBUG_KEY, 'Caught Execution Exception: ' || x_msg_data);
668 END IF;
669
670 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
671 -- No need to rollback: Individual procedures commit or rollback
672 -- within themselves.
673
674 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
675 FND_MSG_PUB.count_and_get( p_count => x_msg_count,
676 p_data => x_msg_data,
677 p_encoded => fnd_api.g_false);
678 IF (p_concurrent_flag = 'Y') THEN
679 fnd_file.put_line(fnd_file.log, 'Caught Unexpected Exception: ' || x_msg_data);
680 END IF;
681 IF (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
682 FND_LOG.STRING(FND_LOG.LEVEL_UNEXPECTED, L_DEBUG_KEY, 'Caught Unexpected Exception: ' || x_msg_data);
683 END IF;
684
685 WHEN OTHERS THEN
686 -- No need to rollback: Individual procedures commit or rollback
687 -- within themselves.
688 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
689 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
690 fnd_msg_pub.add_exc_msg(p_pkg_name => G_PKG_NAME,
691 p_procedure_name => 'PO_Synch_All_Requisitions',
692 p_error_text => SUBSTR(SQLERRM,1,240));
693 END IF;
694 FND_MSG_PUB.count_and_get( p_count => x_msg_count,
695 p_data => x_msg_data,
696 p_encoded => fnd_api.g_false);
697 IF (p_concurrent_flag = 'Y') THEN
698 fnd_file.put_line(fnd_file.log, 'Caught Unknown Exception: ' || x_msg_data);
699 END IF;
700 IF (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
701 FND_LOG.STRING(FND_LOG.LEVEL_UNEXPECTED, L_DEBUG_KEY, 'Caught Unknown Exception: ' || x_msg_data);
702 END IF;
703
704 END PO_Synch_All_Requisitions;
705
706 ----------------------------------------
707
708 -- Start of Comments --
709 -- Procedure name : Associate_All_New_Reqs
710 -- Type : Private
711 -- Function : Updates AHL_OSP_ORDERS_B.PO_REQ_HEADER_ID and
712 -- AHL_OSP_ORDER_LINES.PO_REQ_LINE_ID with REQUISITION_HEADER_ID and
713 -- REQUISITION_LINE_ID respectively for all submitted OSP Orders (for Requisitions).
714 -- Pre-reqs :
715 -- Parameters :
716 --
717 -- Standard IN Parameters :
718 -- p_api_version IN NUMBER Required
719 -- p_init_msg_list IN VARCHAR2 Default FND_API.G_FALSE
720 -- p_commit IN VARCHAR2 Default FND_API.G_FALSE
721 -- p_validation_level IN NUMBER Default FND_API.G_VALID_LEVEL_FULL
722 -- p_default IN VARCHAR2 Default FND_API.G_TRUE
723 -- p_module_type IN VARCHAR2 Default NULL.
724 --
725 -- Standard OUT Parameters :
726 -- x_return_status OUT VARCHAR2 Required
727 -- x_msg_count OUT NUMBER Required
728 -- x_msg_data OUT VARCHAR2 Required
729 --
730 -- Version :
731 -- Initial Version 1.0
732 --
733 -- End of Comments.
734
735 PROCEDURE Associate_All_New_Reqs
736 (
737 p_api_version IN NUMBER,
738 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
739 p_commit IN VARCHAR2 := FND_API.G_FALSE,
740 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
741 p_default IN VARCHAR2 := FND_API.G_TRUE,
742 p_module_type IN VARCHAR2 := NULL,
743 x_return_status OUT NOCOPY VARCHAR2,
744 x_msg_count OUT NOCOPY NUMBER,
745 x_msg_data OUT NOCOPY VARCHAR2) IS
746
747 CURSOR l_get_osps_for_reqs_csr IS
748 SELECT OSP_ORDER_ID FROM AHL_OSP_ORDERS_B
749 WHERE STATUS_CODE = G_OSP_REQ_SUBMITTED_STATUS
750 AND PO_REQ_HEADER_ID IS NULL
751 AND PO_BATCH_ID IS NOT NULL
752 -- Added by jaramana on April 7, 2008 for bug 6609988
753 AND OPERATING_UNIT_ID = MO_GLOBAL.get_current_org_id();
754
755 l_api_version CONSTANT NUMBER := 1.0;
756 l_api_name CONSTANT VARCHAR2(30) := 'Associate_All_New_Reqs';
757 L_DEBUG_KEY CONSTANT VARCHAR2(150) := G_LOG_PREFIX || '.Associate_All_New_Reqs';
758 l_dummy VARCHAR2(1);
759 l_osp_order_id NUMBER;
760 l_po_req_header_id NUMBER;
761 l_temp_count NUMBER := 0;
762
763 BEGIN
764 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
765 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, L_DEBUG_KEY || '.begin', 'Entering Procedure');
766 END IF;
767
768 -- Standard start of API savepoint
769 SAVEPOINT Associate_All_New_Reqs_pvt;
770
771 -- Standard call to check for call compatibility
772 IF NOT FND_API.Compatible_API_Call(l_api_version, p_api_version, l_api_name,
773 G_PKG_NAME) THEN
774 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
775 END IF;
776
777 -- Initialize message list if p_init_msg_list is set to TRUE
778 IF FND_API.To_Boolean(p_init_msg_list) THEN
779 FND_MSG_PUB.Initialize;
780 END IF;
781
782 -- Initialize API return status to success
783 x_return_status := FND_API.G_RET_STS_SUCCESS;
784
785 IF FND_API.to_boolean( p_default ) THEN
786 -- No special default settings required in this API
787 NULL;
788 END IF;
789
790 -- Start processing
791 OPEN l_get_osps_for_reqs_csr;
792 LOOP
793 FETCH l_get_osps_for_reqs_csr INTO l_osp_order_id;
794 EXIT WHEN l_get_osps_for_reqs_csr%NOTFOUND;
795 Associate_New_Req(p_osp_order_id => l_osp_order_id,
796 x_po_req_header_id => l_po_req_header_id);
797 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
798 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, L_DEBUG_KEY, 'Associated OSP Order with Id ' || l_osp_order_id || ' to Requisition with Id ' || l_po_req_header_id);
799 END IF;
800 l_temp_count := l_temp_count + 1;
801 END LOOP;
802 CLOSE l_get_osps_for_reqs_csr;
803
804 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
805 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, L_DEBUG_KEY, 'Associated ' || l_temp_count || ' OSP Orders with Requisitions');
806 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, L_DEBUG_KEY, 'Completed Processing. Checking for errors.');
807 END IF;
808 -- Check Error Message stack.
809 x_msg_count := FND_MSG_PUB.count_msg;
810 IF x_msg_count > 0 THEN
811 RAISE FND_API.G_EXC_ERROR;
812 END IF;
813
814 -- Standard check of p_commit
815 IF FND_API.TO_BOOLEAN(p_commit) THEN
816 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
817 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, L_DEBUG_KEY, 'About to COMMIT work.');
818 END IF;
819 COMMIT WORK;
820 END IF;
821
822 -- Standard call to get message count and if count is 1, get message info
823 FND_MSG_PUB.Count_And_Get
824 ( p_count => x_msg_count,
825 p_data => x_msg_data,
826 p_encoded => fnd_api.g_false
827 );
828
829 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
830 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, L_DEBUG_KEY || '.end', 'Exiting Procedure');
831 END IF;
832
833 EXCEPTION
834 WHEN FND_API.G_EXC_ERROR THEN
835 ROLLBACK TO Associate_All_New_Reqs_pvt;
836 x_return_status := FND_API.G_RET_STS_ERROR;
837 FND_MSG_PUB.count_and_get(p_count => x_msg_count,
838 p_data => x_msg_data,
839 p_encoded => fnd_api.g_false);
840
841 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
842 ROLLBACK TO Associate_All_New_Reqs_pvt;
843 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
844 FND_MSG_PUB.count_and_get(p_count => x_msg_count,
845 p_data => x_msg_data,
846 p_encoded => fnd_api.g_false);
847
848 WHEN OTHERS THEN
849 ROLLBACK TO Associate_All_New_Reqs_pvt;
850 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
851 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
852 fnd_msg_pub.add_exc_msg(p_pkg_name => G_PKG_NAME,
853 p_procedure_name => 'Associate_All_New_Reqs',
854 p_error_text => SUBSTR(SQLERRM,1,240));
855 END IF;
856 FND_MSG_PUB.count_and_get(p_count => x_msg_count,
857 p_data => x_msg_data,
858 p_encoded => fnd_api.g_false);
859
860 END Associate_All_New_Reqs;
861
862 -------------------------------------------------------------------------
863 -- This Procedure inserts a record into the PO_REQUISITIONS_INTERFACE_ALL table
864 -------------------------------------------------------------------------
865 PROCEDURE Insert_Into_Req_Interface
866 (
867 p_po_req_hdr_rec IN PO_Req_Header_Rec_Type,
868 p_po_req_line_tbl IN PO_Req_Line_Tbl_Type,
869 x_batch_id OUT NOCOPY NUMBER
870 ) IS
871
872 l_org_id NUMBER := NULL;
873 l_price NUMBER := NULL;
874 l_category VARCHAR2(30) := NULL;
875 l_category_seg1 VARCHAR2(40) := NULL;
876 l_category_seg2 VARCHAR2(40) := NULL;
877 l_charge_acct_id NUMBER := NULL;
878
879 CURSOR get_prj_task_comp_date_csr (c_task_id IN NUMBER) IS
880 SELECT COMPLETION_DATE from pa_tasks
881 where task_id = c_task_id;
882
883 l_task_completion_date DATE;
884 l_expenditure_item_type pa_expenditure_types.expenditure_type%type;
885
886 L_DEBUG_KEY CONSTANT VARCHAR2(150) := G_LOG_PREFIX || '.Insert_Into_Req_Interface';
887
888 BEGIN
889
890 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
891 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, L_DEBUG_KEY || '.begin', 'Entering Procedure');
892 END IF;
893
894 -- Get the current Org to get Item Price
895 l_org_id := MO_GLOBAL.get_current_org_id();
896
897 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
898 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, L_DEBUG_KEY, 'p_po_req_line_tbl.count: '|| p_po_req_line_tbl.count);
899 END IF;
900
901 FOR i IN p_po_req_line_tbl.FIRST..p_po_req_line_tbl.LAST LOOP
902 IF (p_po_req_line_tbl(i).ITEM_ID IS NULL) THEN
903 -- One time items defaulting
904 l_price := G_DEFAULT_PRICE;
905 l_category_seg1 := G_DEFAULT_CATEGORY_SEG1;
906 l_category_seg2 := G_DEFAULT_CATEGORY_SEG2;
907 ELSE
908 -- Purchasing defaults the category from the item
909 l_category_seg1 := null;
910 l_category_seg2 := null;
911
912 /*
913 Item pricing information is also derived in the UNIT_PRICE and CURRENCY_UNIT_PRICE columns.
914 If no sourcing rules are found for the item, supplier sourcing fails and the UNIT_PRICE is
915 defaulted from the item master for supplier requisition lines
916 */
917 l_price := null;
918 END IF;
919
920 -- Insert row into PO_REQUISITIONS_INTERFACE_ALL
921 l_charge_acct_id := get_charge_account_id(p_po_req_line_tbl(i).SHIP_TO_ORG_ID, p_po_req_line_tbl(i).ITEM_ID);
922
923 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
924 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, L_DEBUG_KEY, 'l_charge_acct_id before inserting: '|| l_charge_acct_id);
925 END IF;
926
927 -- Project related fields to link the distributions to Projects
928 IF p_po_req_line_tbl(i).TASK_ID IS NOT NULL THEN
929 OPEN get_prj_task_comp_date_csr(p_po_req_line_tbl(i).TASK_ID);
930 FETCH get_prj_task_comp_date_csr INTO l_task_completion_date;
931 CLOSE get_prj_task_comp_date_csr;
932 ELSE
933 l_task_completion_date := NULL;
934 END IF;
935
936 l_expenditure_item_type := FND_PROFILE.VALUE('AHL_OSP_EXPENDITURE_TYPE');
937 IF(l_expenditure_item_type IS NULL) THEN
938 l_expenditure_item_type := 'Outside Processing';
939 END IF;
940
941 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
942 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'));
943 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, L_DEBUG_KEY, 'l_expenditure_item_type : '|| l_expenditure_item_type);
944 END IF;
945
946 INSERT INTO po_requisitions_interface_all
947 (
948 INTERFACE_SOURCE_CODE,
949 INTERFACE_SOURCE_LINE_ID,
950 REFERENCE_NUM,
951 LINE_TYPE_ID,
952 SOURCE_TYPE_CODE,
953 DESTINATION_TYPE_CODE,
954 AUTHORIZATION_STATUS,
955 PREPARER_ID,
956 ITEM_ID,
957 ITEM_DESCRIPTION,
958 QUANTITY,
959 CATEGORY_SEGMENT1,
960 CATEGORY_SEGMENT2,
961 UOM_CODE,
962 UNIT_PRICE,
963 NEED_BY_DATE,
964 DESTINATION_ORGANIZATION_ID,
965 DELIVER_TO_LOCATION_ID,
966 DELIVER_TO_REQUESTOR_ID,
967 SUGGESTED_BUYER_ID,
968 SUGGESTED_VENDOR_ID,
969 SUGGESTED_VENDOR_SITE_ID,
970 SUGGESTED_VENDOR_CONTACT_ID,
971 HEADER_DESCRIPTION,
972 --Project related fields to link the distributions to Projects
973 WIP_ENTITY_ID,
974 PROJECT_ID,
975 TASK_ID,
976 --Project related fields End
977 GROUP_CODE,
978 BATCH_ID,
979 ORG_ID,
980 PROGRAM_ID,
981 PROGRAM_APPLICATION_ID,
982 CHARGE_ACCOUNT_ID,
983 --Project related fields to link the distributions to Projects
984 PROJECT_ACCOUNTING_CONTEXT,
985 EXPENDITURE_TYPE,
986 EXPENDITURE_ORGANIZATION_ID,
987 EXPENDITURE_ITEM_DATE
988 --Project related fields End
989 )
990 VALUES
991 (
992 AHL_GLOBAL.AHL_APP_SHORT_NAME, --INTERFACE_SOURCE_CODE
993 p_po_req_hdr_rec.OSP_ORDER_ID, --INTERFACE_SOURCE_LINE_ID
994 p_po_req_line_tbl(i).OSP_LINE_ID, --REFERENCE_NUM
995 p_po_req_line_tbl(i).PO_LINE_TYPE_ID, --LINE_TYPE_ID
996 'VENDOR', --SOURCE_TYPE_CODE
997 'EXPENSE', --DESTINATION_TYPE_CODE
998 'INCOMPLETE', --AUTHORIZATION_STATUS
999 FND_GLOBAL.EMPLOYEE_ID, --PREPARER_ID (Should be logged in user)
1000 p_po_req_line_tbl(i).ITEM_ID, --ITEM_ID
1001 p_po_req_line_tbl(i).ITEM_DESCRIPTION, --ITEM_DESCRIPTION
1002 p_po_req_line_tbl(i).QUANTITY, --QUANTITY
1003 l_category_seg1, --CATEGORY_SEGMENT1
1004 l_category_seg2, --CATEGORY_SEGMENT2
1005 p_po_req_line_tbl(i).UOM_CODE, --UOM_CODE
1006 l_price, --UNIT_PRICE
1007 p_po_req_line_tbl(i).NEED_BY_DATE, --NEED_BY_DATE
1008 p_po_req_line_tbl(i).SHIP_TO_ORG_ID, --DESTINATION_ORGANIZATION_ID
1009 p_po_req_line_tbl(i).SHIP_TO_LOC_ID, --DELIVER_TO_LOCATION_ID
1010 FND_GLOBAL.EMPLOYEE_ID, --DELIVER_TO_REQUESTOR_ID
1011 p_po_req_hdr_rec.BUYER_ID, --SUGGESTED_BUYER_ID
1012 p_po_req_hdr_rec.VENDOR_ID, --SUGGESTED_VENDOR_ID
1013 p_po_req_hdr_rec.VENDOR_SITE_ID, --SUGGESTED_VENDOR_SITE_ID
1014 p_po_req_hdr_rec.VENDOR_CONTACT_ID, --SUGGESTED_VENDOR_CONTACT_ID
1015 SUBSTR(p_po_req_hdr_rec.DESCRIPTION, 1, 240), --HEADER_DESCRIPTION
1016
1017 p_po_req_line_tbl(i).WIP_ENTITY_ID, --WIP_ENTITY_ID
1018 p_po_req_line_tbl(i).PROJECT_ID, --PROJECT_ID
1019 p_po_req_line_tbl(i).TASK_ID, --TASK_ID
1020
1021 p_po_req_hdr_rec.OSP_ORDER_ID, --GROUP_CODE
1022 p_po_req_hdr_rec.OSP_ORDER_ID, --BATCH_ID
1023 p_po_req_hdr_rec.OPERATING_UNIT_ID, --ORG_ID
1024 AHL_GLOBAL.AHL_OSP_PROGRAM_ID, --PROGRAM_ID
1025 AHL_GLOBAL.AHL_APPLICATION_ID, --PROGRAM_APPLICATION_ID
1026 l_charge_acct_id, --CHARGE_ACCOUNT_ID
1027
1028 decode(p_po_req_line_tbl(i).PROJECT_ID, null, null, 'Y'), --PROJECT_ACCOUNTING_CONTEXT
1029 decode(p_po_req_line_tbl(i).PROJECT_ID, null, null, l_expenditure_item_type), --EXPENDITURE_TYPE
1030 decode(p_po_req_line_tbl(i).PROJECT_ID, null, null, p_po_req_line_tbl(i).SHIP_TO_ORG_ID), --EXPENDITURE_ORGANIZATION_ID
1031 l_task_completion_date --EXPENDITURE_ITEM_DATE
1032 );
1033
1034 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1035 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, L_DEBUG_KEY,
1036 ' Inserting into po_requisitions_interface_all: '||
1037 ' WIP_ENTITY_ID = ' || p_po_req_line_tbl(i).WIP_ENTITY_ID ||
1038 ', PROJECT_ID = ' || p_po_req_line_tbl(i).PROJECT_ID ||
1039 ', TASK_ID = ' || p_po_req_line_tbl(i).TASK_ID ||
1040 ', EXPENDITURE_TYPE = ' || l_expenditure_item_type ||
1041 ', EXPENDITURE_ORGANIZATION_ID = ' || p_po_req_line_tbl(i).SHIP_TO_ORG_ID ||
1042 ', EXPENDITURE_ITEM_DATE = ' || to_char(l_task_completion_date, 'DD-MON-YYYY HH24:MI:SS'));
1043 END IF;
1044
1045 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1046 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, L_DEBUG_KEY, 'Inserted One Record.');
1047 END IF;
1048
1049 END LOOP;
1050 x_batch_id := p_po_req_hdr_rec.OSP_ORDER_ID;
1051
1052 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1053 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, L_DEBUG_KEY || '.end', 'Exiting Procedure');
1054 END IF;
1055 END Insert_Into_Req_Interface;
1056
1057 ----------------------------------------
1058 -- This Procedure calls the Concurrent Program to
1059 -- Create a Purchasing Requisition
1060 ----------------------------------------
1061 PROCEDURE Call_Req_Import_Program(
1062 p_batch_id IN NUMBER,
1063 p_osp_order_id IN NUMBER,
1064 x_request_id OUT NOCOPY NUMBER) IS
1065
1066 L_DEBUG_KEY CONSTANT VARCHAR2(150) := G_LOG_PREFIX || '.Call_Req_Import_Program';
1067 l_curr_org_id NUMBER;
1068
1069 BEGIN
1070 -- Added by jaramana on Feb 7, 2008
1071 l_curr_org_id := MO_GLOBAL.get_current_org_id();
1072 FND_REQUEST.SET_ORG_ID(l_curr_org_id);
1073
1074 x_request_id := FND_REQUEST.SUBMIT_REQUEST(
1075 application => G_PO_APP_CODE,
1076 program => G_REQIMPORT_PROG_CODE,
1077 argument1 => AHL_GLOBAL.AHL_APP_SHORT_NAME, -- Origin of requisition (INTERFACE_SOURCE_CODE)
1078 argument2 => p_osp_order_id, -- ID of batch to be imported (BATCH_ID)
1079 argument3 => p_osp_order_id, -- Parameter to group reqs by (GROUP_BY)
1080 argument4 => NULL, -- Last Requisition Number
1081 argument5 => 'N', -- Speicfies if Multiple Distributions are to be created
1082 argument6 => 'N' -- Specifies if requisition approval has to be initiated
1083 );
1084
1085 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1086 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, L_DEBUG_KEY, 'Request Submitted. Request Id = ' || x_request_id);
1087 END IF;
1088 END Call_Req_Import_Program;
1089
1090 -----------------------------------------------------------------------
1091 -- This Procedure validates the OSP Order for PO Requisition Creation
1092 -----------------------------------------------------------------------
1093 PROCEDURE Validate_PO_Req_Header(
1094 p_po_req_header_rec IN PO_Req_Header_Rec_Type) IS
1095
1096 CURSOR l_validate_osp_csr(p_osp_order_id IN NUMBER) IS
1097 SELECT 'x' FROM AHL_OSP_ORDERS_B
1098 WHERE OSP_ORDER_ID = p_osp_order_id
1099 FOR UPDATE OF PO_BATCH_ID, PO_REQUEST_ID; -- Lock Row
1100
1101 CURSOR l_validate_supplier_csr(p_supplier_id IN NUMBER) IS
1102 SELECT 'x' FROM PO_VENDORS_VIEW
1103 WHERE VENDOR_ID = p_supplier_id
1104 AND ENABLED_FLAG = G_YES_FLAG
1105 AND NVL(VENDOR_START_DATE_ACTIVE, SYSDATE - 1) <= SYSDATE
1106 AND NVL(VENDOR_END_DATE_ACTIVE, SYSDATE + 1) > SYSDATE;
1107
1108 CURSOR l_validate_supp_site_csr(p_supp_site_id IN NUMBER,
1109 p_supp_id IN NUMBER) IS
1110 SELECT 'x' FROM PO_VENDOR_SITES
1111 WHERE VENDOR_SITE_ID = p_supp_site_id
1112 AND VENDOR_ID = p_supp_id
1113 AND NVL(INACTIVE_DATE, SYSDATE + 1) > SYSDATE
1114 AND NVL(RFQ_ONLY_SITE_FLAG, G_NO_FLAG) = G_NO_FLAG
1115 AND PURCHASING_SITE_FLAG = G_YES_FLAG;
1116 -- NOTE: Organization filtering is done by the PO_VENDOR_SITES view itself
1117
1118 CURSOR l_validate_vendor_contact_csr(p_vendor_contact_id IN NUMBER,
1119 p_supp_site_id IN NUMBER) IS
1120 SELECT 'x' FROM PO_VENDOR_CONTACTS
1121 WHERE VENDOR_CONTACT_ID = p_vendor_contact_id
1122 AND VENDOR_SITE_ID = p_supp_site_id;
1123 -- May have to check INACTIVE_DATE > SYSDATE also?
1124
1125 CURSOR l_validate_buyer_csr(p_buyer_id IN NUMBER) IS
1126 SELECT 'x' FROM PO_AGENTS_NAME_V
1127 WHERE BUYER_ID = p_buyer_id;
1128 -- NOTE: Effective Date filtering is done by the PO_AGENTS_NAME_V view itself
1129
1130 l_dummy VARCHAR2(1);
1131 l_temp_count NUMBER;
1132 l_org_id NUMBER := NULL;
1133 L_DEBUG_KEY CONSTANT VARCHAR2(150) := G_LOG_PREFIX || '.Validate_PO_Req_Header';
1134
1135 BEGIN
1136
1137 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1138 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, L_DEBUG_KEY || '.begin', 'Entering Procedure');
1139 END IF;
1140
1141 -- OSP Order Id
1142 IF (p_po_req_header_rec.OSP_ORDER_ID IS NULL OR p_po_req_header_rec.OSP_ORDER_ID = FND_API.G_MISS_NUM) THEN
1143 FND_MESSAGE.Set_Name('AHL', 'AHL_OSP_OSP_ID_NULL');
1144 FND_MSG_PUB.ADD;
1145 IF (FND_LOG.LEVEL_ERROR >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1146 FND_LOG.MESSAGE(FND_LOG.LEVEL_ERROR, L_DEBUG_KEY, FALSE);
1147 END IF;
1148 ELSE
1149 OPEN l_validate_osp_csr(p_po_req_header_rec.OSP_ORDER_ID);
1150 FETCH l_validate_osp_csr INTO l_dummy;
1151 IF (l_validate_osp_csr%NOTFOUND) THEN
1152 FND_MESSAGE.Set_Name('AHL', 'AHL_OSP_OSP_ID_INVALID');
1153 FND_MESSAGE.Set_Token('OSP_ID', p_po_req_header_rec.OSP_ORDER_ID);
1154 FND_MSG_PUB.ADD;
1155 IF (FND_LOG.LEVEL_ERROR >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1156 FND_LOG.MESSAGE(FND_LOG.LEVEL_ERROR, L_DEBUG_KEY, FALSE);
1157 END IF;
1158 END IF;
1159 CLOSE l_validate_osp_csr;
1160 END IF;
1161
1162 -- Supplier
1163 IF (p_po_req_header_rec.VENDOR_ID IS NULL OR p_po_req_header_rec.VENDOR_ID = FND_API.G_MISS_NUM) THEN
1164 FND_MESSAGE.Set_Name('AHL', 'AHL_OSP_SUPPLIER_ID_NULL');
1165 FND_MSG_PUB.ADD;
1166 IF (FND_LOG.LEVEL_ERROR >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1167 FND_LOG.MESSAGE(FND_LOG.LEVEL_ERROR, L_DEBUG_KEY, FALSE);
1168 END IF;
1169 ELSE
1170 OPEN l_validate_supplier_csr(p_po_req_header_rec.VENDOR_ID);
1171 FETCH l_validate_supplier_csr INTO l_dummy;
1172 IF (l_validate_supplier_csr%NOTFOUND) THEN
1173 FND_MESSAGE.Set_Name('AHL', 'AHL_OSP_SUPP_INVALID');
1174 FND_MESSAGE.Set_Token('SUPP_ID', p_po_req_header_rec.VENDOR_ID);
1175 FND_MSG_PUB.ADD;
1176 IF (FND_LOG.LEVEL_ERROR >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1177 FND_LOG.MESSAGE(FND_LOG.LEVEL_ERROR, L_DEBUG_KEY, FALSE);
1178 END IF;
1179 END IF;
1180 CLOSE l_validate_supplier_csr;
1181 END IF;
1182
1183 -- Supplier Site
1184 IF (p_po_req_header_rec.VENDOR_SITE_ID IS NULL OR p_po_req_header_rec.VENDOR_SITE_ID = FND_API.G_MISS_NUM) THEN
1185 FND_MESSAGE.Set_Name('AHL', 'AHL_OSP_SSITE_ID_NULL');
1186 FND_MSG_PUB.ADD;
1187 IF (FND_LOG.LEVEL_ERROR >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1188 FND_LOG.MESSAGE(FND_LOG.LEVEL_ERROR, L_DEBUG_KEY, FALSE);
1189 END IF;
1190 ELSE
1191 OPEN l_validate_supp_site_csr(p_po_req_header_rec.VENDOR_SITE_ID, p_po_req_header_rec.VENDOR_ID);
1192 FETCH l_validate_supp_site_csr INTO l_dummy;
1193 IF (l_validate_supp_site_csr%NOTFOUND) THEN
1194 FND_MESSAGE.Set_Name('AHL', 'AHL_OSP_SSITE_INVALID');
1195 FND_MESSAGE.Set_Token('SS_ID', p_po_req_header_rec.VENDOR_SITE_ID);
1196 FND_MSG_PUB.ADD;
1197 IF (FND_LOG.LEVEL_ERROR >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1198 FND_LOG.MESSAGE(FND_LOG.LEVEL_ERROR, L_DEBUG_KEY, FALSE);
1199 END IF;
1200 END IF;
1201 CLOSE l_validate_supp_site_csr;
1202 END IF;
1203
1204 -- Vendor Contact (Optional)
1205 IF (p_po_req_header_rec.VENDOR_CONTACT_ID IS NOT NULL AND p_po_req_header_rec.VENDOR_CONTACT_ID <> FND_API.G_MISS_NUM) THEN
1206 OPEN l_validate_vendor_contact_csr(p_po_req_header_rec.VENDOR_CONTACT_ID, p_po_req_header_rec.VENDOR_SITE_ID);
1207 FETCH l_validate_vendor_contact_csr INTO l_dummy;
1208 IF (l_validate_vendor_contact_csr%NOTFOUND) THEN
1209 FND_MESSAGE.Set_Name('AHL', 'AHL_OSP_VCONTACT_INVALID');
1210 FND_MESSAGE.Set_Token('V_CONTACT_ID', p_po_req_header_rec.VENDOR_CONTACT_ID);
1211 FND_MSG_PUB.ADD;
1212 IF (FND_LOG.LEVEL_ERROR >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1213 FND_LOG.MESSAGE(FND_LOG.LEVEL_ERROR, L_DEBUG_KEY, FALSE);
1214 END IF;
1215 END IF;
1216 CLOSE l_validate_vendor_contact_csr;
1217 END IF;
1218
1219 -- Buyer
1220 IF (p_po_req_header_rec.BUYER_ID IS NULL OR p_po_req_header_rec.BUYER_ID = FND_API.G_MISS_NUM) THEN
1221 FND_MESSAGE.Set_Name('AHL', 'AHL_OSP_BUYER_ID_NULL');
1222 FND_MSG_PUB.ADD;
1223 IF (FND_LOG.LEVEL_ERROR >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1224 FND_LOG.MESSAGE(FND_LOG.LEVEL_ERROR, L_DEBUG_KEY, FALSE);
1225 END IF;
1226 ELSE
1227 OPEN l_validate_buyer_csr(p_po_req_header_rec.BUYER_ID);
1228 FETCH l_validate_buyer_csr INTO l_dummy;
1229 IF (l_validate_buyer_csr%NOTFOUND) THEN
1230 FND_MESSAGE.Set_Name('AHL', 'AHL_OSP_BUYER_INVALID');
1231 FND_MESSAGE.Set_Token('BUYER_ID', p_po_req_header_rec.BUYER_ID);
1232 FND_MSG_PUB.ADD;
1233 IF (FND_LOG.LEVEL_ERROR >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1234 FND_LOG.MESSAGE(FND_LOG.LEVEL_ERROR, L_DEBUG_KEY, FALSE);
1235 END IF;
1236 END IF;
1237 CLOSE l_validate_buyer_csr;
1238 END IF;
1239
1240 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1241 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, L_DEBUG_KEY || '.end', 'Exiting Procedure');
1242 END IF;
1243
1244 END Validate_PO_Req_Header;
1245
1246 ----------------------------------------------------
1247 -- This Procedure validates the PO Requisition Lines
1248 ----------------------------------------------------
1249 PROCEDURE Validate_PO_Req_Lines(
1250 p_po_req_line_tbl IN PO_Req_Line_Tbl_Type,
1251 p_osp_order_id IN NUMBER) IS
1252
1253 CURSOR l_validate_item_csr(p_item_id IN NUMBER,
1254 p_org_id IN NUMBER) IS
1255 SELECT 'x' FROM MTL_SYSTEM_ITEMS_KFV
1256 WHERE INVENTORY_ITEM_ID = p_item_id
1257 AND ENABLED_FLAG = G_YES_FLAG
1258 AND PURCHASING_ENABLED_FLAG = G_YES_FLAG
1259 AND INVENTORY_ITEM_FLAG = G_NO_FLAG -- No Physical Items
1260 AND NVL(START_DATE_ACTIVE, SYSDATE - 1) <= SYSDATE
1261 AND NVL(END_DATE_ACTIVE, SYSDATE + 1) > SYSDATE
1262 AND ORGANIZATION_ID = p_org_id
1263 AND NVL(OUTSIDE_OPERATION_FLAG, G_NO_FLAG) = G_NO_FLAG;
1264
1265 CURSOR l_validate_line_type_csr(p_line_type_id IN NUMBER) IS
1266 SELECT 'x' FROM PO_LINE_TYPES
1267 WHERE ORDER_TYPE_LOOKUP_CODE = G_PO_LINE_TYPE_QUANTITY
1268 AND NVL(OUTSIDE_OPERATION_FLAG, G_NO_FLAG) = G_NO_FLAG
1269 AND LINE_TYPE_ID = p_line_type_id;
1270
1271 L_DEBUG_KEY CONSTANT VARCHAR2(150) := G_LOG_PREFIX || '.Validate_PO_Lines';
1272 l_org_id NUMBER := NULL;
1273 l_dummy VARCHAR2(1);
1274
1275 BEGIN
1276 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1277 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, L_DEBUG_KEY || '.begin', 'Entering Procedure');
1278 END IF;
1279
1280 -- Get the current Org
1281 l_org_id := MO_GLOBAL.get_current_org_id();
1282 IF (l_org_id IS NULL) THEN
1283 FND_MESSAGE.Set_Name('AHL', 'AHL_OSP_ORG_NOT_SET');
1284 FND_MSG_PUB.ADD;
1285 IF (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1286 FND_LOG.MESSAGE(FND_LOG.LEVEL_UNEXPECTED, L_DEBUG_KEY, FALSE);
1287 END IF;
1288 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1289 END IF;
1290
1291 -- Non zero count
1292 IF (p_po_req_line_tbl.COUNT = 0) THEN
1293 FND_MESSAGE.Set_Name('AHL', 'AHL_OSP_NO_PO_LINES');
1294 FND_MSG_PUB.ADD;
1295 IF (FND_LOG.LEVEL_ERROR >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1296 FND_LOG.MESSAGE(FND_LOG.LEVEL_ERROR, L_DEBUG_KEY, FALSE);
1297 END IF;
1298 RETURN; -- Cannot do any further validation
1299 END IF;
1300
1301 FOR i IN p_po_req_line_tbl.FIRST..p_po_req_line_tbl.LAST LOOP
1302 -- Item
1303 IF (p_po_req_line_tbl(i).ITEM_ID IS NOT NULL AND p_po_req_line_tbl(i).ITEM_ID <> FND_API.G_MISS_NUM) THEN
1304 -- Non One-time Item
1305 OPEN l_validate_item_csr(p_po_req_line_tbl(i).ITEM_ID, p_po_req_line_tbl(i).SHIP_TO_ORG_ID);
1306 FETCH l_validate_item_csr INTO l_dummy;
1307 IF (l_validate_item_csr%NOTFOUND) THEN
1308 FND_MESSAGE.Set_Name('AHL', 'AHL_OSP_ITEM_INVALID');
1309 FND_MESSAGE.Set_Token('ITEM', p_po_req_line_tbl(i).ITEM_ID);
1310 FND_MSG_PUB.ADD;
1311 IF (FND_LOG.LEVEL_ERROR >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1312 FND_LOG.MESSAGE(FND_LOG.LEVEL_ERROR, L_DEBUG_KEY, FALSE);
1313 END IF;
1314 END IF;
1315 CLOSE l_validate_item_csr;
1316 ELSE
1317 -- One-time Item: Description is mandatory
1318 IF (TRIM(p_po_req_line_tbl(i).ITEM_DESCRIPTION) IS NULL OR p_po_req_line_tbl(i).ITEM_DESCRIPTION = FND_API.G_MISS_CHAR) THEN
1319 FND_MESSAGE.Set_Name('AHL', 'AHL_OSP_IDESC_NULL');
1320 FND_MSG_PUB.ADD;
1321 IF (FND_LOG.LEVEL_ERROR >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1322 FND_LOG.MESSAGE(FND_LOG.LEVEL_ERROR, L_DEBUG_KEY, FALSE);
1323 END IF;
1324 END IF;
1325 -- One-time Item: UOM is mandatory
1326 IF (TRIM(p_po_req_line_tbl(i).UOM_CODE) IS NULL OR p_po_req_line_tbl(i).UOM_CODE = FND_API.G_MISS_CHAR) THEN
1327 FND_MESSAGE.Set_Name('AHL', 'AHL_OSP_UOM_CODE_NULL');
1328 FND_MSG_PUB.ADD;
1329 IF (FND_LOG.LEVEL_ERROR >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1330 FND_LOG.MESSAGE(FND_LOG.LEVEL_ERROR, L_DEBUG_KEY, FALSE);
1331 END IF;
1332 END IF;
1333 END IF;
1334
1335 -- Quantity
1336 IF (p_po_req_line_tbl(i).QUANTITY IS NULL OR p_po_req_line_tbl(i).QUANTITY = FND_API.G_MISS_NUM) THEN
1337 FND_MESSAGE.Set_Name('AHL', 'AHL_OSP_QUANTITY_NULL');
1338 FND_MSG_PUB.ADD;
1339 IF (FND_LOG.LEVEL_ERROR >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1340 FND_LOG.MESSAGE(FND_LOG.LEVEL_ERROR, L_DEBUG_KEY, FALSE);
1341 END IF;
1342 ELSE
1343 IF (p_po_req_line_tbl(i).QUANTITY <= 0) THEN
1344 FND_MESSAGE.Set_Name('AHL', 'AHL_OSP_QUANTITY_INVALID');
1345 FND_MESSAGE.Set_Token('QUANTITY', p_po_req_line_tbl(i).QUANTITY);
1346 FND_MSG_PUB.ADD;
1347 IF (FND_LOG.LEVEL_ERROR >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1348 FND_LOG.MESSAGE(FND_LOG.LEVEL_ERROR, L_DEBUG_KEY, FALSE);
1349 END IF;
1350 END IF;
1351 END IF;
1352
1353 -- Need By Date
1354 IF (p_po_req_line_tbl(i).NEED_BY_DATE IS NULL OR p_po_req_line_tbl(i).NEED_BY_DATE = FND_API.G_MISS_DATE) THEN
1355 FND_MESSAGE.Set_Name('AHL', 'AHL_OSP_NEED_BY_DATE_NULL');
1356 FND_MSG_PUB.ADD;
1357 IF (FND_LOG.LEVEL_ERROR >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1358 FND_LOG.MESSAGE(FND_LOG.LEVEL_ERROR, L_DEBUG_KEY, FALSE);
1359 END IF;
1360 ELSE
1361 IF (TRUNC(p_po_req_line_tbl(i).NEED_BY_DATE) < TRUNC(SYSDATE)) THEN
1362 FND_MESSAGE.Set_Name('AHL', 'AHL_OSP_NBDATE_INVALID');
1363 FND_MESSAGE.Set_Token('NBDATE', p_po_req_line_tbl(i).NEED_BY_DATE);
1364 FND_MSG_PUB.ADD;
1365 IF (FND_LOG.LEVEL_ERROR >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1366 FND_LOG.MESSAGE(FND_LOG.LEVEL_ERROR, L_DEBUG_KEY, FALSE);
1367 END IF;
1368 END IF;
1369 END IF;
1370
1371 -- Ship To Organization
1372 IF (p_po_req_line_tbl(i).SHIP_TO_ORG_ID IS NULL OR p_po_req_line_tbl(i).SHIP_TO_ORG_ID = FND_API.G_MISS_NUM) THEN
1373 FND_MESSAGE.Set_Name('AHL', 'AHL_OSP_SHIP_TO_ORG_NULL');
1374 FND_MSG_PUB.ADD;
1375 IF (FND_LOG.LEVEL_ERROR >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1376 FND_LOG.MESSAGE(FND_LOG.LEVEL_ERROR, L_DEBUG_KEY, FALSE);
1377 END IF;
1378 END IF;
1379
1380 -- Line Type
1381 IF (p_po_req_line_tbl(i).PO_LINE_TYPE_ID IS NULL OR p_po_req_line_tbl(i).PO_LINE_TYPE_ID = FND_API.G_MISS_NUM) THEN
1382 FND_MESSAGE.Set_Name('AHL', 'AHL_OSP_LN_TYPE_ID_NULL');
1383 FND_MSG_PUB.ADD;
1384 IF (FND_LOG.LEVEL_ERROR >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1385 FND_LOG.MESSAGE(FND_LOG.LEVEL_ERROR, L_DEBUG_KEY, FALSE);
1386 END IF;
1387 ELSE
1388 OPEN l_validate_line_type_csr(p_po_req_line_tbl(i).PO_LINE_TYPE_ID);
1389 FETCH l_validate_line_type_csr INTO l_dummy;
1390 IF (l_validate_line_type_csr%NOTFOUND) THEN
1391 FND_MESSAGE.Set_Name('AHL', 'AHL_OSP_LN_TYPE_ID_INVALID');
1392 FND_MESSAGE.Set_Token('LINE_TYPE_ID', p_po_req_line_tbl(i).PO_LINE_TYPE_ID);
1393 FND_MSG_PUB.ADD;
1394 IF (FND_LOG.LEVEL_ERROR >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1395 FND_LOG.MESSAGE(FND_LOG.LEVEL_ERROR, L_DEBUG_KEY, FALSE);
1396 END IF;
1397 END IF;
1398 CLOSE l_validate_line_type_csr;
1399 END IF;
1400
1401 END LOOP;
1402 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1403 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, L_DEBUG_KEY || '.end', 'Exiting Procedure');
1404 END IF;
1405 END Validate_PO_Req_Lines;
1406
1407 ----------------------------------------
1408 -- This Procedure updates a record of AHL_OSP_ORDERS_B using the table handler
1409 -- All updates to this table from this Package should go through this procedure only
1410 ----------------------------------------
1411 PROCEDURE Update_OSP_Order(
1412 p_osp_order_id IN NUMBER,
1413 p_po_req_header_id IN NUMBER := NULL,
1414 p_batch_id IN NUMBER := NULL,
1415 p_request_id IN NUMBER := NULL,
1416 p_status_code IN VARCHAR2 := NULL
1417 ) IS
1418
1419 CURSOR l_osp_dtls_csr(p_osp_order_id IN NUMBER) IS
1420 SELECT
1421 OBJECT_VERSION_NUMBER,
1422 OSP_ORDER_NUMBER,
1423 ORDER_TYPE_CODE,
1424 SINGLE_INSTANCE_FLAG,
1425 PO_HEADER_ID,
1426 OE_HEADER_ID,
1427 VENDOR_ID,
1428 VENDOR_SITE_ID,
1429 VENDOR_CONTACT_ID,
1430 CUSTOMER_ID,
1431 ORDER_DATE,
1432 CONTRACT_ID,
1433 CONTRACT_TERMS,
1434 OPERATING_UNIT_ID,
1435 PO_SYNCH_FLAG,
1436 STATUS_CODE,
1437 PO_BATCH_ID,
1438 PO_INTERFACE_HEADER_ID,
1439 PO_REQUEST_ID,
1440 PO_AGENT_ID,
1441 PO_REQ_HEADER_ID,
1442 ATTRIBUTE_CATEGORY,
1443 ATTRIBUTE1,
1444 ATTRIBUTE2,
1445 ATTRIBUTE3,
1446 ATTRIBUTE4,
1447 ATTRIBUTE5,
1448 ATTRIBUTE6,
1449 ATTRIBUTE7,
1450 ATTRIBUTE8,
1451 ATTRIBUTE9,
1452 ATTRIBUTE10,
1453 ATTRIBUTE11,
1454 ATTRIBUTE12,
1455 ATTRIBUTE13,
1456 ATTRIBUTE14,
1457 ATTRIBUTE15,
1458 DESCRIPTION
1459 FROM AHL_OSP_ORDERS_VL
1460 WHERE OSP_ORDER_ID = p_osp_order_id;
1461
1462 l_osp_dtls_rec l_osp_dtls_csr%ROWTYPE;
1463 L_DEBUG_KEY CONSTANT VARCHAR2(150) := G_LOG_PREFIX || '.Update_OSP_Order';
1464
1465 BEGIN
1466 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1467 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, L_DEBUG_KEY || '.begin', 'Entering Procedure');
1468 END IF;
1469 -- Retrieve the current record
1470 OPEN l_osp_dtls_csr(p_osp_order_id);
1471 FETCH l_osp_dtls_csr INTO l_osp_dtls_rec;
1472 IF (l_osp_dtls_csr%NOTFOUND) THEN
1473 FND_MESSAGE.Set_Name('AHL', 'AHL_OSP_OSP_ID_INVALID');
1474 FND_MESSAGE.Set_Token('OSP_ID', p_osp_order_id);
1475 FND_MSG_PUB.ADD;
1476 IF (FND_LOG.LEVEL_ERROR >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1477 FND_LOG.MESSAGE(FND_LOG.LEVEL_ERROR, L_DEBUG_KEY, FALSE);
1478 END IF;
1479 CLOSE l_osp_dtls_csr;
1480 RAISE FND_API.G_EXC_ERROR;
1481 END IF;
1482 CLOSE l_osp_dtls_csr;
1483
1484 -- Update non-null local variables into cursor variable
1485 IF (p_po_req_header_id IS NOT NULL) THEN
1486 l_osp_dtls_rec.PO_REQ_HEADER_ID := p_po_req_header_id;
1487 END IF;
1488 IF (p_batch_id IS NOT NULL) THEN
1489 l_osp_dtls_rec.PO_BATCH_ID := p_batch_id;
1490 END IF;
1491 IF (p_request_id IS NOT NULL) THEN
1492 l_osp_dtls_rec.PO_REQUEST_ID := p_request_id;
1493 END IF;
1494 IF (p_status_code IS NOT NULL) THEN
1495 l_osp_dtls_rec.STATUS_CODE := p_status_code;
1496 END IF;
1497
1498 -- Call Table Handler
1499 AHL_OSP_ORDERS_PKG.UPDATE_ROW(
1500 X_OSP_ORDER_ID => p_osp_order_id,
1501 X_OBJECT_VERSION_NUMBER => l_osp_dtls_rec.OBJECT_VERSION_NUMBER + 1, -- Updated
1502 X_OSP_ORDER_NUMBER => l_osp_dtls_rec.OSP_ORDER_NUMBER,
1503 X_ORDER_TYPE_CODE => l_osp_dtls_rec.ORDER_TYPE_CODE,
1504 X_SINGLE_INSTANCE_FLAG => l_osp_dtls_rec.SINGLE_INSTANCE_FLAG,
1505 X_PO_HEADER_ID => l_osp_dtls_rec.PO_HEADER_ID,
1506 X_OE_HEADER_ID => l_osp_dtls_rec.OE_HEADER_ID,
1507 X_VENDOR_ID => l_osp_dtls_rec.VENDOR_ID,
1508 X_VENDOR_SITE_ID => l_osp_dtls_rec.VENDOR_SITE_ID,
1509 X_VENDOR_CONTACT_ID => l_osp_dtls_rec.VENDOR_CONTACT_ID,
1510 X_CUSTOMER_ID => l_osp_dtls_rec.CUSTOMER_ID,
1511 X_ORDER_DATE => l_osp_dtls_rec.ORDER_DATE,
1512 X_CONTRACT_ID => l_osp_dtls_rec.CONTRACT_ID,
1513 X_CONTRACT_TERMS => l_osp_dtls_rec.CONTRACT_TERMS,
1514 X_OPERATING_UNIT_ID => l_osp_dtls_rec.OPERATING_UNIT_ID,
1515 X_PO_SYNCH_FLAG => l_osp_dtls_rec.PO_SYNCH_FLAG,
1516 X_STATUS_CODE => l_osp_dtls_rec.STATUS_CODE, -- Updated
1517 X_PO_BATCH_ID => l_osp_dtls_rec.PO_BATCH_ID, -- Updated
1518 X_PO_REQUEST_ID => l_osp_dtls_rec.PO_REQUEST_ID, -- Updated
1519 X_PO_INTERFACE_HEADER_ID => l_osp_dtls_rec.PO_INTERFACE_HEADER_ID,
1520 X_PO_REQ_HEADER_ID => l_osp_dtls_rec.PO_REQ_HEADER_ID, -- Updated
1521 X_PO_AGENT_ID => l_osp_dtls_rec.PO_AGENT_ID,
1522 X_ATTRIBUTE_CATEGORY => l_osp_dtls_rec.ATTRIBUTE_CATEGORY,
1523 X_ATTRIBUTE1 => l_osp_dtls_rec.ATTRIBUTE1,
1524 X_ATTRIBUTE2 => l_osp_dtls_rec.ATTRIBUTE2,
1525 X_ATTRIBUTE3 => l_osp_dtls_rec.ATTRIBUTE3,
1526 X_ATTRIBUTE4 => l_osp_dtls_rec.ATTRIBUTE4,
1527 X_ATTRIBUTE5 => l_osp_dtls_rec.ATTRIBUTE5,
1528 X_ATTRIBUTE6 => l_osp_dtls_rec.ATTRIBUTE6,
1529 X_ATTRIBUTE7 => l_osp_dtls_rec.ATTRIBUTE7,
1530 X_ATTRIBUTE8 => l_osp_dtls_rec.ATTRIBUTE8,
1531 X_ATTRIBUTE9 => l_osp_dtls_rec.ATTRIBUTE9,
1532 X_ATTRIBUTE10 => l_osp_dtls_rec.ATTRIBUTE10,
1533 X_ATTRIBUTE11 => l_osp_dtls_rec.ATTRIBUTE11,
1534 X_ATTRIBUTE12 => l_osp_dtls_rec.ATTRIBUTE12,
1535 X_ATTRIBUTE13 => l_osp_dtls_rec.ATTRIBUTE13,
1536 X_ATTRIBUTE14 => l_osp_dtls_rec.ATTRIBUTE14,
1537 X_ATTRIBUTE15 => l_osp_dtls_rec.ATTRIBUTE15,
1538 X_DESCRIPTION => l_osp_dtls_rec.DESCRIPTION,
1539 X_LAST_UPDATE_DATE => TRUNC(sysdate), -- Updated
1540 X_LAST_UPDATED_BY => fnd_global.user_id, -- Updated
1541 X_LAST_UPDATE_LOGIN => fnd_global.login_id); -- Updated
1542 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1543 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, L_DEBUG_KEY || '.end', 'Exiting Procedure');
1544 END IF;
1545 END Update_OSP_Order;
1546
1547 ----------------------------------------
1548 -- This Local Procedure updates OSP Tables with
1549 -- PO Information for one OSP Order
1550 ----------------------------------------
1551 PROCEDURE Associate_New_Req(
1552 p_osp_order_id IN NUMBER,
1553 x_po_req_header_id OUT NOCOPY NUMBER) IS
1554
1555 CURSOR l_get_po_req_hdr_csr(p_osp_order_id IN NUMBER) IS
1556 SELECT POREQ.REQUISITION_HEADER_ID
1557 FROM PO_REQUISITION_HEADERS_ALL POREQ, AHL_OSP_ORDERS_B OSP
1558 WHERE POREQ.INTERFACE_SOURCE_LINE_ID = p_osp_order_id AND
1559 OSP.OSP_ORDER_ID = p_osp_order_id AND
1560 OSP.OPERATING_UNIT_ID = POREQ.ORG_ID AND
1561 POREQ.INTERFACE_SOURCE_CODE = AHL_GLOBAL.AHL_APP_SHORT_NAME;
1562
1563 CURSOR l_get_osp_lines_csr(p_osp_order_id IN NUMBER) IS
1564 SELECT OSP_ORDER_LINE_ID FROM AHL_OSP_ORDER_LINES
1565 WHERE PO_REQ_LINE_ID IS NULL
1566 AND OSP_ORDER_ID = p_osp_order_id;
1567
1568 CURSOR l_get_po_req_line_csr(p_osp_order_line_id IN NUMBER, p_po_req_header_id IN NUMBER) IS
1569 SELECT REQUISITION_LINE_ID FROM PO_REQUISITION_LINES_ALL
1570 WHERE REFERENCE_NUM = p_osp_order_line_id AND
1571 REQUISITION_HEADER_ID = p_po_req_header_id;
1572
1573 CURSOR l_get_request_id_csr(p_osp_order_id IN NUMBER) IS
1574 SELECT PO_REQUEST_ID FROM AHL_OSP_ORDERS_B
1575 WHERE OSP_ORDER_ID = p_osp_order_id;
1576
1577 CURSOR check_req_completeness_csr(c_po_req_header_id IN NUMBER) IS
1578 select 1 from dual where
1579 (select count(*) from AHL_OSP_ORDER_LINES where OSP_ORDER_ID = p_osp_order_id) =
1580 (select count(*) from
1581 PO_REQUISITION_LINES_ALL REQL, AHL_OSP_ORDER_LINES OSPL
1582 where OSPL.OSP_ORDER_ID = p_osp_order_id
1583 AND REQL.REFERENCE_NUM = OSPL.osp_order_line_id
1584 AND REQL.REQUISITION_HEADER_ID = c_po_req_header_id);
1585
1586 L_DEBUG_KEY CONSTANT VARCHAR2(150) := G_LOG_PREFIX || '.Associate_New_Req';
1587 l_po_req_header_id NUMBER;
1588 l_osp_order_line_id NUMBER;
1589 l_po_req_line_id NUMBER;
1590 l_request_id NUMBER;
1591 l_phase VARCHAR2(100);
1592 l_status VARCHAR2(100);
1593 l_dev_phase VARCHAR2(100);
1594 l_dev_status VARCHAR2(100);
1595 l_message VARCHAR2(1000);
1596 l_retval BOOLEAN;
1597 l_temp_num NUMBER;
1598
1599 BEGIN
1600 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1601 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, L_DEBUG_KEY || '.begin', 'Entering Procedure');
1602 END IF;
1603
1604 OPEN l_get_po_req_hdr_csr(p_osp_order_id);
1605 FETCH l_get_po_req_hdr_csr INTO x_po_req_header_id;
1606 IF (l_get_po_req_hdr_csr%FOUND) THEN
1607 -- Ensure that the Requisition is created in its entirety
1608 OPEN check_req_completeness_csr(x_po_req_header_id);
1609 FETCH check_req_completeness_csr INTO l_temp_num;
1610 IF (check_req_completeness_csr%NOTFOUND) THEN
1611 -- The Requisition has been created only PARTIALLY: Flag as Failed
1612 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1613 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, L_DEBUG_KEY, 'Requisition Creation succeeded only PARTIALLY for OSP Order Id ' || p_osp_order_id ||
1614 ', Requisition Header Id: ' || x_po_req_header_id);
1615 END IF;
1616 Set_Req_Submission_Failed(p_osp_order_id);
1617 ELSE
1618 -- Update AHL_OSP_ORDERS_B's PO_REQ_HEADER_ID
1619 --dbms_output.put_line('About to Update AHL_OSP_ORDERS_B.PO_REQ_HEADER_ID with ' || x_po_req_header_id);
1620 Set_PO_Req_Header_Id(p_osp_order_id => p_osp_order_id,
1621 p_po_req_header_id => x_po_req_header_id);
1622
1623 --dbms_output.put_line('Updated po_req_header_id. Logging Transaction...');
1624 AHL_OSP_UTIL_PKG.Log_Transaction(p_trans_type_code => G_TXN_TYPE_PO_SYNCH,
1625 p_src_doc_id => p_osp_order_id,
1626 p_src_doc_type_code => G_DOC_TYPE_PO_REQ,
1627 p_dest_doc_id => x_po_req_header_id,
1628 p_dest_doc_type_code => G_DOC_TYPE_OSP);
1629
1630 -- Get PO Lines for all OSP Lines
1631 OPEN l_get_osp_lines_csr(p_osp_order_id);
1632 LOOP
1633 FETCH l_get_osp_lines_csr INTO l_osp_order_line_id;
1634 EXIT WHEN l_get_osp_lines_csr%NOTFOUND;
1635 OPEN l_get_po_req_line_csr(l_osp_order_line_id, x_po_req_header_id);
1636 FETCH l_get_po_req_line_csr INTO l_po_req_line_id;
1637 IF (l_get_po_req_line_csr%FOUND) THEN
1638 Update_Osp_Order_Lines(p_osp_order_line_id => l_osp_order_line_id,
1639 p_po_req_line_id => l_po_req_line_id);
1640 ELSE
1641 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1642 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 Req Line');
1643 END IF;
1644 END IF;
1645 CLOSE l_get_po_req_line_csr;
1646 END LOOP;
1647 CLOSE l_get_osp_lines_csr;
1648 END IF; -- check_req_completeness_csr FOUND or not
1649 CLOSE check_req_completeness_csr;
1650 ELSE
1651 -- Set Return PO Header Value to null
1652 x_po_req_header_id := null;
1653 -- Check if the Concurrent Program has completed
1654 OPEN l_get_request_id_csr(p_osp_order_id);
1655 FETCH l_get_request_id_csr INTO l_request_id;
1656 CLOSE l_get_request_id_csr;
1657 IF (l_request_id IS NOT NULL AND l_request_id <> 0) THEN
1658 --dbms_output.put_line('Getting Concurrent Program Status ');
1659 l_retval := FND_CONCURRENT.GET_REQUEST_STATUS(request_id => l_request_id,
1660 phase => l_phase,
1661 status => l_status,
1662 dev_phase => l_dev_phase,
1663 dev_status => l_dev_status,
1664 message => l_message);
1665 -- IF ((l_retval = TRUE) AND (l_dev_phase = 'COMPLETE') AND (l_dev_status <> 'NORMAL')) THEN
1666 -- Status can be NORMAL even if the Requisition Creation had failed.
1667 -- So setting status to REQ_SUBMISSION_FAILED if the Concurrent Program has completed
1668 -- but the Requisition Header is not set
1669 IF ((l_retval = TRUE) AND (l_dev_phase = 'COMPLETE')) THEN
1670 -- Abnormal Termination
1671 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1672 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);
1673 END IF;
1674 -- Set the Status of OSP Order to Submission Failed
1675 Set_Req_Submission_Failed(p_osp_order_id);
1676 END IF;
1677 END IF;
1678 END IF;
1679 CLOSE l_get_po_req_hdr_csr;
1680 --dbms_output.put_line('About to exit Associate_New_Req ');
1681 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1682 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, L_DEBUG_KEY || '.end', 'Exiting Procedure');
1683 END IF;
1684 END Associate_New_Req;
1685
1686
1687 ----------------------------------------
1688 -- This Procedure updates AHL_OSP_ORDERS_B's PO_REQ_HEADER_ID and sets STATUS_CODE to REQUISITION_CREATED
1689 ----------------------------------------
1690 PROCEDURE Set_PO_Req_Header_ID(
1691 p_osp_order_id IN NUMBER,
1692 p_po_req_header_id IN NUMBER) IS
1693 BEGIN
1694 Update_OSP_Order(p_osp_order_id => p_osp_order_id,
1695 p_po_req_header_id => p_po_req_header_id,
1696 p_status_code => G_OSP_REQ_CREATED_STATUS);
1697 END Set_PO_Req_Header_ID;
1698
1699 ----------------------------------------
1700 -- This Procedure updates the Osp Order Lines with the PO_REQ_LINE_ID
1701 ----------------------------------------
1702 PROCEDURE Update_Osp_Order_Lines(
1703 p_osp_order_line_id IN NUMBER,
1704 p_po_req_line_id IN NUMBER := NULL) IS
1705
1706 CURSOR l_osp_line_dtls_csr(p_osp_line_id IN NUMBER) IS
1707 SELECT
1708 OBJECT_VERSION_NUMBER,
1709 OSP_ORDER_ID,
1710 OSP_LINE_NUMBER,
1711 STATUS_CODE,
1712 PO_LINE_TYPE_ID,
1713 SERVICE_ITEM_ID,
1714 SERVICE_ITEM_DESCRIPTION,
1715 SERVICE_ITEM_UOM_CODE,
1716 NEED_BY_DATE,
1717 SHIP_BY_DATE,
1718 PO_LINE_ID,
1719 PO_REQ_LINE_ID,
1720 OE_SHIP_LINE_ID,
1721 OE_RETURN_LINE_ID,
1722 WORKORDER_ID,
1723 OPERATION_ID,
1724 QUANTITY,
1725 EXCHANGE_INSTANCE_ID,
1726 INVENTORY_ITEM_ID,
1727 INVENTORY_ORG_ID,
1728 INVENTORY_ITEM_UOM,
1729 INVENTORY_ITEM_QUANTITY,
1730 SUB_INVENTORY,
1731 LOT_NUMBER,
1732 SERIAL_NUMBER,
1733 ATTRIBUTE_CATEGORY,
1734 ATTRIBUTE1,
1735 ATTRIBUTE2,
1736 ATTRIBUTE3,
1737 ATTRIBUTE4,
1738 ATTRIBUTE5,
1739 ATTRIBUTE6,
1740 ATTRIBUTE7,
1741 ATTRIBUTE8,
1742 ATTRIBUTE9,
1743 ATTRIBUTE10,
1744 ATTRIBUTE11,
1745 ATTRIBUTE12,
1746 ATTRIBUTE13,
1747 ATTRIBUTE14,
1748 ATTRIBUTE15
1749 FROM AHL_OSP_ORDER_LINES
1750 WHERE OSP_ORDER_LINE_ID = p_osp_order_line_id;
1751
1752 l_osp_line_dtls_rec l_osp_line_dtls_csr%ROWTYPE;
1753 L_DEBUG_KEY CONSTANT VARCHAR2(150) := G_LOG_PREFIX || '.Update_Osp_Order_Lines';
1754
1755 BEGIN
1756 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1757 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, L_DEBUG_KEY || '.begin', 'Entering Procedure');
1758 END IF;
1759 -- Retrieve the current record
1760 OPEN l_osp_line_dtls_csr(p_osp_order_line_id);
1761 FETCH l_osp_line_dtls_csr INTO l_osp_line_dtls_rec;
1762 IF (l_osp_line_dtls_csr%NOTFOUND) THEN
1763 FND_MESSAGE.Set_Name('AHL', 'AHL_OSP_OSP_LINE_ID_INVALID');
1764 FND_MESSAGE.Set_Token('OSP_LINE_ID', p_osp_order_line_id);
1765 FND_MSG_PUB.ADD;
1766 IF (FND_LOG.LEVEL_ERROR >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1767 FND_LOG.MESSAGE(FND_LOG.LEVEL_ERROR, L_DEBUG_KEY, FALSE);
1768 END IF;
1769 CLOSE l_osp_line_dtls_csr;
1770 RAISE FND_API.G_EXC_ERROR;
1771 END IF;
1772 CLOSE l_osp_line_dtls_csr;
1773
1774 -- Update cursor variable's PO Req Line ID and PO intf transaction id
1775
1776 IF (p_po_req_line_id IS NOT NULL) THEN
1777 l_osp_line_dtls_rec.PO_REQ_LINE_ID := p_po_req_line_id;
1778 END IF;
1779
1780 -- Call Table Handler
1781 AHL_OSP_ORDER_LINES_PKG.UPDATE_ROW(
1782 P_OSP_ORDER_LINE_ID => p_osp_order_line_id,
1783 P_OBJECT_VERSION_NUMBER => l_osp_line_dtls_rec.OBJECT_VERSION_NUMBER + 1, -- Updated
1784 P_OSP_ORDER_ID => l_osp_line_dtls_rec.OSP_ORDER_ID,
1785 P_OSP_LINE_NUMBER => l_osp_line_dtls_rec.OSP_LINE_NUMBER,
1786 P_STATUS_CODE => l_osp_line_dtls_rec.STATUS_CODE,
1787 P_PO_LINE_TYPE_ID => l_osp_line_dtls_rec.PO_LINE_TYPE_ID,
1788 P_SERVICE_ITEM_ID => l_osp_line_dtls_rec.SERVICE_ITEM_ID,
1789 P_SERVICE_ITEM_DESCRIPTION => l_osp_line_dtls_rec.SERVICE_ITEM_DESCRIPTION,
1790 P_SERVICE_ITEM_UOM_CODE => l_osp_line_dtls_rec.SERVICE_ITEM_UOM_CODE,
1791 P_NEED_BY_DATE => l_osp_line_dtls_rec.NEED_BY_DATE,
1792 P_SHIP_BY_DATE => l_osp_line_dtls_rec.SHIP_BY_DATE,
1793 P_PO_LINE_ID => l_osp_line_dtls_rec.PO_LINE_ID,
1794 P_PO_REQ_LINE_ID => l_osp_line_dtls_rec.PO_REQ_LINE_ID, -- Updated
1795 P_OE_SHIP_LINE_ID => l_osp_line_dtls_rec.OE_SHIP_LINE_ID,
1796 P_OE_RETURN_LINE_ID => l_osp_line_dtls_rec.OE_RETURN_LINE_ID,
1797 P_WORKORDER_ID => l_osp_line_dtls_rec.WORKORDER_ID,
1798 P_OPERATION_ID => l_osp_line_dtls_rec.OPERATION_ID,
1799 P_QUANTITY => l_osp_line_dtls_rec.QUANTITY,
1800 P_EXCHANGE_INSTANCE_ID => l_osp_line_dtls_rec.EXCHANGE_INSTANCE_ID,
1801 P_INVENTORY_ITEM_ID => l_osp_line_dtls_rec.INVENTORY_ITEM_ID,
1802 P_INVENTORY_ORG_ID => l_osp_line_dtls_rec.INVENTORY_ORG_ID,
1803 P_INVENTORY_ITEM_UOM => l_osp_line_dtls_rec.INVENTORY_ITEM_UOM,
1804 P_INVENTORY_ITEM_QUANTITY => l_osp_line_dtls_rec.INVENTORY_ITEM_QUANTITY,
1805 P_SUB_INVENTORY => l_osp_line_dtls_rec.SUB_INVENTORY,
1806 P_LOT_NUMBER => l_osp_line_dtls_rec.LOT_NUMBER,
1807 P_SERIAL_NUMBER => l_osp_line_dtls_rec.SERIAL_NUMBER,
1808 P_ATTRIBUTE_CATEGORY => l_osp_line_dtls_rec.ATTRIBUTE_CATEGORY,
1809 P_ATTRIBUTE1 => l_osp_line_dtls_rec.ATTRIBUTE1,
1810 P_ATTRIBUTE2 => l_osp_line_dtls_rec.ATTRIBUTE2,
1811 P_ATTRIBUTE3 => l_osp_line_dtls_rec.ATTRIBUTE3,
1812 P_ATTRIBUTE4 => l_osp_line_dtls_rec.ATTRIBUTE4,
1813 P_ATTRIBUTE5 => l_osp_line_dtls_rec.ATTRIBUTE5,
1814 P_ATTRIBUTE6 => l_osp_line_dtls_rec.ATTRIBUTE6,
1815 P_ATTRIBUTE7 => l_osp_line_dtls_rec.ATTRIBUTE7,
1816 P_ATTRIBUTE8 => l_osp_line_dtls_rec.ATTRIBUTE8,
1817 P_ATTRIBUTE9 => l_osp_line_dtls_rec.ATTRIBUTE9,
1818 P_ATTRIBUTE10 => l_osp_line_dtls_rec.ATTRIBUTE10,
1819 P_ATTRIBUTE11 => l_osp_line_dtls_rec.ATTRIBUTE11,
1820 P_ATTRIBUTE12 => l_osp_line_dtls_rec.ATTRIBUTE12,
1821 P_ATTRIBUTE13 => l_osp_line_dtls_rec.ATTRIBUTE13,
1822 P_ATTRIBUTE14 => l_osp_line_dtls_rec.ATTRIBUTE14,
1823 P_ATTRIBUTE15 => l_osp_line_dtls_rec.ATTRIBUTE15,
1824 P_LAST_UPDATE_DATE => TRUNC(sysdate), -- Updated
1825 P_LAST_UPDATED_BY => fnd_global.user_id, -- Updated
1826 P_LAST_UPDATE_LOGIN => fnd_global.login_id); -- Updated
1827
1828 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1829 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, L_DEBUG_KEY || '.end', 'Exiting Procedure');
1830 END IF;
1831 END Update_Osp_Order_Lines;
1832
1833
1834 -- This Procedure updates AHL_OSP_ORDERS_B.STATUS_CODE to REQ_SUBMISSION_FAILED
1835 PROCEDURE Set_Req_Submission_Failed(
1836 p_osp_order_id IN NUMBER) IS
1837 BEGIN
1838 Update_OSP_Order(p_osp_order_id => p_osp_order_id,
1839 p_status_code => G_OSP_REQ_SUB_FAILED_STATUS);
1840 END Set_Req_Submission_Failed;
1841
1842 ----------------------------------------
1843 -- This Procedure handles Deleted PO Req Headers and is Part of PO Synchronization.
1844 -- This procedure commits its work if p_commit is set to true and
1845 -- if there were no errors during the execution of this procedure.
1846 -- It does not check the message list for performing the commit action.
1847 -- Functionality:
1848 -- After a PO Requisition has been created for an OSP Order, it is possible for the Requisition
1849 -- to be manually deleted (using Purchasing responsibility) before the Requisition is approved.
1850 -- Since this deletion will result in an OSP Order referring to a non-existent Requisition,
1851 -- we need to change the OSP order to bring it to a consistent state.
1852 -- This procedure basically looks for OSP Orders for which the Requisition has been deleted
1853 -- and resets some values and corrects the status of the order as well as the lines
1854 -- so that the OSP Order can be resubmitted and a different Requisition can be created.
1855 -- This procedure does a direct update of the AHL_OSP_ORDERS_B and the AHL_OSP_ORDER_LINES
1856 -- tables and does not call the process_osp_order API to avoid unwanted validations
1857 ----------------------------------------
1858 PROCEDURE Handle_Deleted_Req_Headers(
1859 p_commit IN VARCHAR2,
1860 x_return_status OUT NOCOPY VARCHAR2) IS
1861
1862 CURSOR get_req_deleted_osps_csr IS
1863 SELECT osp.osp_order_id,
1864 osp.object_version_number,
1865 osp.po_req_header_id,
1866 osp.status_code,
1867 osp.order_type_code
1868 FROM ahl_osp_orders_b osp
1869 WHERE osp.status_code = G_OSP_REQ_CREATED_STATUS AND
1870 osp.order_type_code in ('SERVICE', 'EXCHANGE') AND
1871 -- Added by jaramana on April 7, 2008 for bug 6609988
1872 osp.operating_unit_id = MO_GLOBAL.get_current_org_id() AND
1873 NOT EXISTS (SELECT 1 FROM po_requisition_headers_all where requisition_header_id = osp.po_req_header_id);
1874
1875 CURSOR get_osp_line_dtls_csr(c_osp_order_id IN NUMBER) IS
1876 SELECT ospl.osp_order_id,
1877 ospl.osp_order_line_id,
1878 ospl.object_version_number,
1879 ospl.status_code,
1880 ospl.po_req_line_id
1881 FROM ahl_osp_order_lines ospl
1882 WHERE ospl.osp_order_id = c_osp_order_id;
1883
1884 L_DEBUG_KEY CONSTANT VARCHAR2(150) := G_LOG_PREFIX || '.Handle_Deleted_Req_Headers';
1885 l_temp_num NUMBER := 0;
1886 l_osp_details_rec get_req_deleted_osps_csr%ROWTYPE;
1887 l_osp_line_details_rec get_osp_line_dtls_csr%ROWTYPE;
1888
1889 BEGIN
1890 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1891 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, L_DEBUG_KEY || '.begin', 'Entering Procedure');
1892 END IF;
1893 -- Standard start of API savepoint
1894 SAVEPOINT Handle_Deleted_Req_Headers_pvt;
1895
1896 -- Initialize API return status to success
1897 x_return_status := FND_API.G_RET_STS_SUCCESS;
1898 -- Get all OSP Orders for which the PO Header has been deleted
1899 OPEN get_req_deleted_osps_csr;
1900 LOOP
1901 FETCH get_req_deleted_osps_csr into l_osp_details_rec;
1902 EXIT WHEN get_req_deleted_osps_csr%NOTFOUND;
1903 l_temp_num := l_temp_num + 1;
1904 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1905 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, L_DEBUG_KEY, 'Processing PO Req Deletion for OSP Order ' || l_osp_details_rec.osp_order_id);
1906 END IF;
1907 -- Get the Line Details
1908 OPEN get_osp_line_dtls_csr(c_osp_order_id => l_osp_details_rec.osp_order_id);
1909 LOOP
1910 FETCH get_osp_line_dtls_csr into l_osp_line_details_rec;
1911 EXIT WHEN get_osp_line_dtls_csr%NOTFOUND;
1912 IF (l_osp_line_details_rec.status_code IS NULL) THEN
1913 IF (l_osp_line_details_rec.po_req_line_id IS NOT NULL) THEN
1914 -- Reset the value of PO_REQ_LINE_ID and increment OVN (status_code is already null)
1915 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1916 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, L_DEBUG_KEY, 'Updating OSP Line with Id ' || l_osp_line_details_rec.osp_order_line_id);
1917 END IF;
1918 update ahl_osp_order_lines
1919 set po_req_line_id = null,
1920 object_version_number = l_osp_line_details_rec.object_version_number + 1,
1921 last_update_date = TRUNC(sysdate),
1922 last_updated_by = fnd_global.user_id,
1923 last_update_login = fnd_global.login_id
1924 where osp_order_line_id = l_osp_line_details_rec.osp_order_line_id;
1925 END IF;
1926 ELSE
1927 -- Physically delete this line (REQ_DELETED, REQ_CANCELLED)
1928 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1929 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, L_DEBUG_KEY, 'Deleting OSP Line with Id ' || l_osp_line_details_rec.osp_order_line_id);
1930 END IF;
1931 DELETE FROM ahl_osp_order_lines
1932 WHERE osp_order_line_id = l_osp_line_details_rec.osp_order_line_id;
1933 END IF;
1934 END LOOP;
1935 CLOSE get_osp_line_dtls_csr;
1936 -- Now for the OSP Order Header, reset PO_REQ_HEADER_ID, PO_BATCH_ID, PO_REQUEST_ID
1937 -- set STATUS_CODE to "ENTERED" and increment OVN
1938 update ahl_osp_orders_b
1939 set po_req_header_id = null,
1940 po_batch_id = null,
1941 po_request_id = null,
1942 status_code = G_OSP_ENTERED_STATUS,
1943 object_version_number = l_osp_details_rec.object_version_number + 1,
1944 last_update_date = TRUNC(sysdate),
1945 last_updated_by = fnd_global.user_id,
1946 last_update_login = fnd_global.login_id
1947 where osp_order_id = l_osp_details_rec.osp_order_id;
1948 END LOOP;
1949 CLOSE get_req_deleted_osps_csr;
1950
1951 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1952 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, L_DEBUG_KEY, 'Processed PO Req Deletion for ' || l_temp_num || ' OSP Orders');
1953 END IF;
1954
1955 IF (FND_API.TO_BOOLEAN(p_commit)) THEN
1956 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1957 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, L_DEBUG_KEY, 'About to COMMIT work.');
1958 END IF;
1959 COMMIT WORK;
1960 END IF;
1961 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1962 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, L_DEBUG_KEY || '.end', 'Exiting Procedure');
1963 END IF;
1964
1965 EXCEPTION
1966 WHEN FND_API.G_EXC_ERROR THEN
1967 ROLLBACK TO Handle_Deleted_Req_Headers_pvt;
1968 x_return_status := FND_API.G_RET_STS_ERROR;
1969
1970 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1971 ROLLBACK TO Handle_Deleted_Req_Headers_pvt;
1972 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1973
1974 WHEN OTHERS THEN
1975 ROLLBACK TO Handle_Deleted_Req_Headers_pvt;
1976 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1977 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
1978 fnd_msg_pub.add_exc_msg(p_pkg_name => G_PKG_NAME,
1979 p_procedure_name => 'Handle_Deleted_Req_Headers',
1980 p_error_text => SUBSTR(SQLERRM,1,240));
1981 END IF;
1982 END Handle_Deleted_Req_Headers;
1983
1984 ----------------------------------------
1985 -- This Procedure handles cancelled Req Lines and is Part of PO Synchronization.
1986 -- This procedure commits its work if p_commit is set to true and
1987 -- if there were no errors during the execution of this procedure.
1988 -- It does not check the message list for performing the commit action
1989 ----------------------------------------
1990 PROCEDURE Handle_Cancelled_Req_Lines(
1991 p_commit IN VARCHAR2,
1992 x_return_status OUT NOCOPY VARCHAR2) IS
1993
1994 CURSOR l_get_cancelled_req_lines_csr IS
1995 SELECT REQL.REQUISITION_LINE_ID,
1996 OL.OSP_ORDER_LINE_ID,
1997 REQH.INTERFACE_SOURCE_LINE_ID,
1998 OL.OBJECT_VERSION_NUMBER,
1999 OSP.OBJECT_VERSION_NUMBER
2000 FROM PO_REQUISITION_LINES_ALL REQL,
2001 PO_REQUISITION_HEADERS_ALL REQH,
2002 AHL_OSP_ORDER_LINES OL,
2003 AHL_OSP_ORDERS_B OSP
2004 WHERE nvl(REQL.CANCEL_FLAG,'N') = 'Y' AND -- Canceled Req Line
2005 REQL.REQUISITION_HEADER_ID = REQH.REQUISITION_HEADER_ID AND
2006 REQH.INTERFACE_SOURCE_CODE = AHL_GLOBAL.AHL_APP_SHORT_NAME AND -- AHL Created Req
2007 REQH.INTERFACE_SOURCE_LINE_ID = OSP.OSP_ORDER_ID AND -- Related to the OSP Order
2008 OSP.OSP_ORDER_ID = OL.OSP_ORDER_ID AND
2009 -- Added by jaramana on April 7, 2008 for bug 6609988
2010 OSP.OPERATING_UNIT_ID = MO_GLOBAL.get_current_org_id() AND
2011 OL.PO_REQ_LINE_ID = REQL.REQUISITION_LINE_ID AND
2012 NVL(OL.STATUS_CODE, ' ') <> G_OL_REQ_CANCELLED_STATUS -- Not yet updated
2013 ORDER BY REQH.INTERFACE_SOURCE_LINE_ID; -- One OSP Order at a time
2014
2015 L_DEBUG_KEY CONSTANT VARCHAR2(150) := G_LOG_PREFIX || '.Handle_Cancelled_Req_Lines';
2016 l_osp_order_id NUMBER := -1;
2017 l_osp_order_line_id NUMBER;
2018 l_po_req_line_id NUMBER;
2019 l_prev_osp_order_id NUMBER := -1;
2020 l_table_index NUMBER := 0;
2021 l_return_status VARCHAR2(1);
2022 l_msg_count NUMBER;
2023 l_msg_data VARCHAR2(1000);
2024 l_osp_order_rec AHL_OSP_ORDERS_PVT.osp_order_rec_type;
2025 l_osp_order_lines_tbl AHL_OSP_ORDERS_PVT.osp_order_lines_tbl_type;
2026 l_commit_flag BOOLEAN := true;
2027 l_osp_obj_ver_num NUMBER;
2028 l_ol_obj_ver_num NUMBER;
2029
2030 BEGIN
2031 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2032 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, L_DEBUG_KEY || '.begin', 'Entering Procedure');
2033 END IF;
2034 -- Standard start of API savepoint
2035 SAVEPOINT Handle_Cancelled_Req_Lines_pvt;
2036
2037 -- Initialize API return status to success
2038 x_return_status := FND_API.G_RET_STS_SUCCESS;
2039
2040 OPEN l_get_cancelled_req_lines_csr;
2041 LOOP
2042 FETCH l_get_cancelled_req_lines_csr INTO l_po_req_line_id,
2043 l_osp_order_line_id,
2044 l_osp_order_id,
2045 l_ol_obj_ver_num,
2046 l_osp_obj_ver_num;
2047 EXIT WHEN l_get_cancelled_req_lines_csr%NOTFOUND;
2048 IF (l_osp_order_id <> l_prev_osp_order_id) THEN
2049 IF (l_prev_osp_order_id <> -1) THEN
2050 -- Cancel all OSP Lines pertaining to the previous OSP Order
2051 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2052 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, L_DEBUG_KEY, 'Cancelling OSP Line for Order ' || l_prev_osp_order_id);
2053 END IF;
2054 AHL_OSP_ORDERS_PVT.process_osp_order(p_api_version => 1.0,
2055 p_init_msg_list => FND_API.G_FALSE,
2056 p_commit => FND_API.G_FALSE,
2057 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
2058 p_x_osp_order_rec => l_osp_order_rec,
2059 p_x_osp_order_lines_tbl => l_osp_order_lines_tbl,
2060 x_return_status => l_return_status,
2061 x_msg_count => l_msg_count,
2062 x_msg_data => l_msg_data);
2063 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
2064 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2065 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, L_DEBUG_KEY, 'Error while Cancelling OSP Line for OSP Order ' || l_prev_osp_order_id);
2066 END IF;
2067 l_commit_flag := false;
2068 END IF;
2069 -- Delete table used by prior call
2070 l_osp_order_lines_tbl.DELETE;
2071 END IF;
2072 -- Update API Record with new OSP Order Id
2073 l_osp_order_rec.OSP_ORDER_ID := l_osp_order_id;
2074 l_osp_order_rec.OBJECT_VERSION_NUMBER := l_osp_obj_ver_num;
2075 l_prev_osp_order_id := l_osp_order_id;
2076 l_table_index := 0;
2077 END IF;
2078 -- Copy OSP Line Id into API's Line Table at l_table_index
2079 l_osp_order_lines_tbl(l_table_index).OSP_ORDER_LINE_ID := l_osp_order_line_id;
2080 -- Copy Line's Object Version Nnumber into API's Line Table at l_table_index
2081 l_osp_order_lines_tbl(l_table_index).OBJECT_VERSION_NUMBER := l_ol_obj_ver_num;
2082 -- Set OSP Line Status to G_OL_REQ_CANCELLED_STATUS in API's Line Table
2083 l_osp_order_lines_tbl(l_table_index).STATUS_CODE := G_OL_REQ_CANCELLED_STATUS;
2084 -- Set Operation to Update in the line rec
2085 l_osp_order_lines_tbl(l_table_index).OPERATION_FLAG := AHL_OSP_ORDERS_PVT.G_OP_UPDATE;
2086
2087 l_table_index := l_table_index + 1;
2088 END LOOP;
2089 CLOSE l_get_cancelled_req_lines_csr;
2090 IF (l_prev_osp_order_id <> -1) THEN
2091 -- Save the Last Cancellation
2092 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2093 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, L_DEBUG_KEY, 'Cancelling OSP Line for Order ' || l_prev_osp_order_id);
2094 END IF;
2095 AHL_OSP_ORDERS_PVT.process_osp_order(p_api_version => 1.0,
2096 p_init_msg_list => FND_API.G_FALSE,
2097 p_commit => FND_API.G_FALSE,
2098 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
2099 p_x_osp_order_rec => l_osp_order_rec,
2100 p_x_osp_order_lines_tbl => l_osp_order_lines_tbl,
2101 x_return_status => l_return_status,
2102 x_msg_count => l_msg_count,
2103 x_msg_data => l_msg_data);
2104 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
2105 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2106 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, L_DEBUG_KEY, 'Error while Cancelling OSP Line for OSP Order ' || l_prev_osp_order_id);
2107 END IF;
2108 l_commit_flag := false;
2109 END IF;
2110 END IF;
2111 IF (l_commit_flag = false) THEN
2112 RAISE FND_API.G_EXC_ERROR; -- Rollback and return error
2113 END IF;
2114 -- No errors in current procedure: Check only passed in flag
2115 IF (FND_API.TO_BOOLEAN(p_commit)) THEN
2116 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2117 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, L_DEBUG_KEY, 'About to COMMIT work.');
2118 END IF;
2119 COMMIT WORK;
2120 END IF;
2121 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2122 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, L_DEBUG_KEY || '.end', 'Exiting Procedure');
2123 END IF;
2124 EXCEPTION
2125 WHEN FND_API.G_EXC_ERROR THEN
2126 ROLLBACK TO Handle_Cancelled_Req_Lines_pvt;
2127 x_return_status := FND_API.G_RET_STS_ERROR;
2128
2129 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2130 ROLLBACK TO Handle_Cancelled_Req_Lines_pvt;
2131 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2132
2133 WHEN OTHERS THEN
2134 ROLLBACK TO Handle_Cancelled_Req_Lines_pvt;
2135 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2136 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
2137 fnd_msg_pub.add_exc_msg(p_pkg_name => G_PKG_NAME,
2138 p_procedure_name => 'Handle_Cancelled_Req_Lines',
2139 p_error_text => SUBSTR(SQLERRM,1,240));
2140 END IF;
2141
2142 END Handle_Cancelled_Req_Lines;
2143
2144 ----------------------------------------
2145 -- This Procedure handles deleted Req Lines and is Part of PO Synchronization.
2146 -- This procedure commits its work if p_commit is set to true and
2147 -- if there were no errors during the execution of this procedure.
2148 -- It does not check the message list for performing the commit action.
2149 ----------------------------------------
2150 PROCEDURE Handle_Deleted_Req_Lines(
2151 p_commit IN VARCHAR2,
2152 x_return_status OUT NOCOPY VARCHAR2) IS
2153
2154 CURSOR l_get_deleted_req_lines_csr IS
2155 SELECT OL.OSP_ORDER_ID, OL.OSP_ORDER_LINE_ID,
2156 OL.OBJECT_VERSION_NUMBER, OSP.OBJECT_VERSION_NUMBER
2157 FROM AHL_OSP_ORDER_LINES OL, AHL_OSP_ORDERS_B OSP
2158 WHERE OL.PO_REQ_LINE_ID IS NOT NULL AND -- PO Created
2159 NVL(OL.STATUS_CODE, ' ') <> G_OL_REQ_DELETED_STATUS AND -- Not yet updated
2160 OSP.OSP_ORDER_ID = OL.OSP_ORDER_ID AND
2161 -- Added by jaramana on April 7, 2008 for bug 6609988
2162 OSP.OPERATING_UNIT_ID = MO_GLOBAL.get_current_org_id() AND
2163 NOT EXISTS (SELECT REQUISITION_LINE_ID FROM PO_REQUISITION_LINES_ALL WHERE REQUISITION_LINE_ID = OL.PO_REQ_LINE_ID) -- Req Line Deleted
2164 ORDER BY OL.OSP_ORDER_ID; -- One OSP Order at a time
2165
2166 L_DEBUG_KEY CONSTANT VARCHAR2(150) := G_LOG_PREFIX || '.Handle_Deleted_Req_Lines';
2167 l_osp_order_id NUMBER := -1;
2168 l_osp_order_line_id NUMBER;
2169 l_prev_osp_order_id NUMBER := -1;
2170 l_table_index NUMBER := 0;
2171 l_return_status VARCHAR2(1);
2172 l_msg_count NUMBER;
2173 l_msg_data VARCHAR2(1000);
2174 l_osp_order_rec AHL_OSP_ORDERS_PVT.osp_order_rec_type;
2175 l_osp_order_lines_tbl AHL_OSP_ORDERS_PVT.osp_order_lines_tbl_type;
2176 l_commit_flag BOOLEAN := true;
2177 l_osp_obj_ver_num NUMBER;
2178 l_ol_obj_ver_num NUMBER;
2179
2180 BEGIN
2181 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2182 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, L_DEBUG_KEY || '.begin', 'Entering Procedure');
2183 END IF;
2184 -- Standard start of API savepoint
2185 SAVEPOINT Handle_Deleted_Req_Lines_pvt;
2186
2187 -- Initialize API return status to success
2188 x_return_status := FND_API.G_RET_STS_SUCCESS;
2189
2190 OPEN l_get_deleted_req_lines_csr;
2191 LOOP
2192 FETCH l_get_deleted_req_lines_csr INTO l_osp_order_id,
2193 l_osp_order_line_id,
2194 l_ol_obj_ver_num,
2195 l_osp_obj_ver_num;
2196 EXIT WHEN l_get_deleted_req_lines_csr%NOTFOUND;
2197 IF (l_osp_order_id <> l_prev_osp_order_id) THEN
2198 IF (l_prev_osp_order_id <> -1) THEN
2199 -- Req Delete all OSP Lines pertaining to the previous OSP Order
2200 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2201 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, L_DEBUG_KEY, 'Deleting OSP Line for Order ' || l_prev_osp_order_id);
2202 END IF;
2203 AHL_OSP_ORDERS_PVT.process_osp_order(p_api_version => 1.0,
2204 p_init_msg_list => FND_API.G_FALSE,
2205 p_commit => FND_API.G_FALSE,
2206 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
2207 p_x_osp_order_rec => l_osp_order_rec,
2208 p_x_osp_order_lines_tbl => l_osp_order_lines_tbl,
2209 x_return_status => l_return_status,
2210 x_msg_count => l_msg_count,
2211 x_msg_data => l_msg_data);
2212 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
2213 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2214 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, L_DEBUG_KEY, 'Error while Deleting OSP Line for OSP Order ' || l_prev_osp_order_id);
2215 END IF;
2216 l_commit_flag := false;
2217 END IF;
2218 -- Delete table used by prior call
2219 l_osp_order_lines_tbl.DELETE;
2220 END IF;
2221 -- Update API Record with new OSP Order Id
2222 l_osp_order_rec.OSP_ORDER_ID := l_osp_order_id;
2223 l_osp_order_rec.OBJECT_VERSION_NUMBER := l_osp_obj_ver_num;
2224 l_prev_osp_order_id := l_osp_order_id;
2225 l_table_index := 0;
2226 END IF;
2227 -- Copy OSP Line Id into API's Line Table at l_table_index
2228 l_osp_order_lines_tbl(l_table_index).OSP_ORDER_LINE_ID := l_osp_order_line_id;
2229 -- Copy Line's Object Version Nnumber into API's Line Table at l_table_index
2230 l_osp_order_lines_tbl(l_table_index).OBJECT_VERSION_NUMBER := l_ol_obj_ver_num;
2231 -- Set OSP Line Status to G_OL_REQ_DELETED_STATUS in API's Line Table
2232 l_osp_order_lines_tbl(l_table_index).STATUS_CODE := G_OL_REQ_DELETED_STATUS;
2233 -- Set Operation to Update in the line rec
2234 l_osp_order_lines_tbl(l_table_index).OPERATION_FLAG := AHL_OSP_ORDERS_PVT.G_OP_UPDATE;
2235 l_table_index := l_table_index + 1;
2236 END LOOP;
2237 CLOSE l_get_deleted_req_lines_csr;
2238 IF (l_prev_osp_order_id <> -1) THEN
2239 -- Save the Last Deletion
2240 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2241 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, L_DEBUG_KEY, 'Deleting OSP Line for Order ' || l_prev_osp_order_id);
2242 END IF;
2243 AHL_OSP_ORDERS_PVT.process_osp_order(p_api_version => 1.0,
2244 p_init_msg_list => FND_API.G_FALSE,
2245 p_commit => FND_API.G_FALSE,
2246 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
2247 p_x_osp_order_rec => l_osp_order_rec,
2248 p_x_osp_order_lines_tbl => l_osp_order_lines_tbl,
2249 x_return_status => l_return_status,
2250 x_msg_count => l_msg_count,
2251 x_msg_data => l_msg_data);
2252 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
2253 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2254 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, L_DEBUG_KEY, 'Error while Deleting OSP Line for OSP Order ' || l_prev_osp_order_id);
2255 END IF;
2256 l_commit_flag := false;
2257 END IF;
2258 END IF;
2259
2260 IF (l_commit_flag = false) THEN
2261 RAISE FND_API.G_EXC_ERROR; -- Rollback and return error
2262 END IF;
2263 -- No errors in current procedure: Check only passed in flag
2264 IF (FND_API.TO_BOOLEAN(p_commit)) THEN
2265 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2266 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, L_DEBUG_KEY, 'About to COMMIT work.');
2267 END IF;
2268 COMMIT WORK;
2269 END IF;
2270 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2271 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, L_DEBUG_KEY || '.end', 'Exiting Procedure');
2272 END IF;
2273 EXCEPTION
2274 WHEN FND_API.G_EXC_ERROR THEN
2275 ROLLBACK TO Handle_Deleted_Req_Lines_pvt;
2276 x_return_status := FND_API.G_RET_STS_ERROR;
2277
2278 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2279 ROLLBACK TO Handle_Deleted_Req_Lines_pvt;
2280 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2281
2282 WHEN OTHERS THEN
2283 ROLLBACK TO Handle_Deleted_Req_Lines_pvt;
2284 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2285 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
2286 fnd_msg_pub.add_exc_msg(p_pkg_name => G_PKG_NAME,
2287 p_procedure_name => 'Handle_Deleted_Req_Lines',
2288 p_error_text => SUBSTR(SQLERRM,1,240));
2289 END IF;
2290
2291 END Handle_Deleted_Req_Lines;
2292
2293 ----------------------------------------
2294 -- This function determines if the Requsition is closed/cancelled.
2295 ----------------------------------------
2296 FUNCTION Is_PO_Req_Closed(p_po_req_header_id IN NUMBER) RETURN VARCHAR2 IS
2297
2298 CURSOR l_is_req_closed_csr(p_po_req_header_id IN NUMBER) IS
2299 SELECT 1
2300 FROM po_requisition_headers_all poh
2301 WHERE poh.requisition_header_id = p_po_req_header_id
2302 AND (nvl(poh.closed_code, 'OPEN') IN ('CANCELLED', 'CLOSED','FINALLY CLOSED', 'REJECTED', 'RETURNED')
2303 OR
2304 nvl(poh.authorization_status, 'INCOMPLETE') = 'CANCELLED'
2305 );
2306
2307 --Verify that the passed PO_REQ_HEADER_ID is valid
2308 CURSOR l_val_req_hdr_id_csr(p_po_req_header_id IN NUMBER) IS
2309 SELECT poh.requisition_header_id
2310 FROM po_requisition_headers_all poh
2311 WHERE poh.requisition_header_id = p_po_req_header_id;
2312
2313
2314 L_DEBUG_KEY CONSTANT VARCHAR2(150) := G_LOG_PREFIX || '.Is_PO_Req_Closed';
2315 l_closed_status VARCHAR2(30);
2316 l_cancel_flag VARCHAR2(1);
2317 l_dummy NUMBER;
2318
2319
2320 BEGIN
2321 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2322 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, L_DEBUG_KEY, 'PO_REQ_HEADER_ID: '|| p_po_req_header_id);
2323 END IF;
2324
2325 IF (p_po_req_header_id IS NULL OR p_po_req_header_id = FND_API.G_MISS_NUM) THEN
2326 FND_MESSAGE.Set_Name('AHL', 'AHL_OSP_REQ_ID_NULL');
2327 FND_MSG_PUB.ADD;
2328 RETURN 'N';
2329 ELSE
2330 --validate the requisition_header_id. If it is not present, it may mean that user need to perform a PO Synch
2331 OPEN l_val_req_hdr_id_csr(p_po_req_header_id);
2332 FETCH l_val_req_hdr_id_csr INTO l_dummy;
2333 IF (l_val_req_hdr_id_csr%NOTFOUND) THEN
2334 FND_MESSAGE.Set_Name('AHL', 'AHL_OSP_REQ_HDR_ID_INV');
2335 FND_MESSAGE.Set_Token('REQ_HDR_ID', p_po_req_header_id);
2336 FND_MSG_PUB.ADD;
2337 CLOSE l_val_req_hdr_id_csr;
2338 RETURN 'N';
2339 END IF;
2340 CLOSE l_val_req_hdr_id_csr;
2341
2342 END IF;
2343
2344 OPEN l_is_req_closed_csr(p_po_req_header_id);
2345 FETCH l_is_req_closed_csr INTO l_dummy;
2346 IF (l_is_req_closed_csr%FOUND) THEN
2347 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2348 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, L_DEBUG_KEY, 'Requisition is closed');
2349 END IF;
2350 CLOSE l_is_req_closed_csr;
2351 RETURN 'Y';
2352 END IF;
2353 CLOSE l_is_req_closed_csr;
2354
2355 RETURN 'N';
2356
2357 END Is_PO_Req_Closed;
2358
2359 ----------------------------------------------
2360 -- This function derives the charge_account_id
2361 ----------------------------------------------
2362 FUNCTION get_charge_account_id(
2363 p_inv_org_id IN NUMBER,
2364 p_item_id IN NUMBER
2365 ) RETURN NUMBER IS
2366
2367 CURSOR get_exp_acct_item_csr(c_inv_org_id IN NUMBER, c_item_id IN NUMBER) IS
2368 SELECT expense_account
2369 FROM mtl_system_items_b
2370 WHERE organization_id = c_inv_org_id
2371 AND inventory_item_id = c_item_id;
2372
2373 CURSOR get_mtl_acct_org_csr(c_inv_org_id IN NUMBER) IS
2374 SELECT material_account
2375 FROM mtl_parameters
2376 WHERE organization_id = c_inv_org_id;
2377
2378 L_DEBUG_KEY CONSTANT VARCHAR2(150) := G_LOG_PREFIX || '.get_charge_account_id';
2379 l_charge_acct_id NUMBER := null;
2380 l_item_account_set BOOLEAN := FALSE;
2381
2382 BEGIN
2383 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2384 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, L_DEBUG_KEY, 'p_inv_org_id: '|| p_inv_org_id);
2385 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, L_DEBUG_KEY, 'p_item_id: '|| p_item_id);
2386 END IF;
2387 IF(p_item_id is NULL) THEN
2388 --It is a one-time item
2389 l_item_account_set := FALSE;
2390 ELSE
2391 --Item is present, retrieve the item's expense account.
2392 OPEN get_exp_acct_item_csr(p_inv_org_id, p_item_id);
2393 FETCH get_exp_acct_item_csr INTO l_charge_acct_id;
2394 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2395 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, L_DEBUG_KEY, 'l_charge_acct_id from item: '|| l_charge_acct_id);
2396 END IF;
2397 CLOSE get_exp_acct_item_csr;
2398 IF(l_charge_acct_id is not NULL) THEN
2399 l_item_account_set := TRUE;
2400 ELSE
2401 --Expense account not set at the Item Level.
2402 l_item_account_set := FALSE;
2403 END IF;
2404 END IF;
2405
2406 IF (l_item_account_set = FALSE) THEN
2407 --Retrieve the Org's Material Account Id
2408 OPEN get_mtl_acct_org_csr(p_inv_org_id);
2409 FETCH get_mtl_acct_org_csr INTO l_charge_acct_id;
2410 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2411 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, L_DEBUG_KEY, 'l_charge_acct_id from Org: '|| l_charge_acct_id);
2412 END IF;
2413 CLOSE get_mtl_acct_org_csr;
2414 END IF;
2415
2416 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2417 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, L_DEBUG_KEY, 'l_charge_acct_id: '|| l_charge_acct_id);
2418 END IF;
2419 RETURN l_charge_acct_id;
2420
2421 END get_charge_account_id;
2422
2423 END AHL_OSP_PO_REQ_PVT;
[Home] [Help]