[Home] [Help]
PACKAGE BODY: APPS.OE_PURCHASE_RELEASE_PVT
Source
1 PACKAGE BODY OE_Purchase_Release_PVT AS
2 /* $Header: OEXVDSPB.pls 120.14.12010000.3 2008/10/21 06:30:20 spothula ship $ */
3
4 -- Global constant holding the package name
5
6 G_PKG_NAME CONSTANT VARCHAR2(30) := 'OE_Purchase_Release_PVT';
7
8
9 /*-----------------------------------------------------------------
10 PROCEDURE : OELOGO
11 DESCRIPTION: Writes Values to the concurrent program log file
12 -----------------------------------------------------------------*/
13 Procedure OELOGO(p_drop_ship_line_rec IN Drop_Ship_Line_Rec_Type,
14 p_mode IN VARCHAR2)
15 IS
16 --
17 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
18 --
19 BEGIN
20 IF (p_mode = G_MODE_CONCURRENT) THEN
21 fnd_file.put_line(FND_FILE.LOG, 'Line ID = '|| p_drop_ship_line_rec.line_id);
22 fnd_file.put_line(FND_FILE.LOG, 'Header ID = '|| p_drop_ship_line_rec.header_id);
23 fnd_file.put_line(FND_FILE.LOG, 'Source Type Code' || p_drop_ship_line_rec.source_type_code);
24 fnd_file.put_line(FND_FILE.LOG, 'Project Id' || p_drop_ship_line_rec.project_id);
25 fnd_file.put_line(FND_FILE.LOG, 'Task Id ' || p_drop_ship_line_rec.task_id);
26 fnd_file.put_line(FND_FILE.LOG, 'End Item Unit Number ' || p_drop_ship_line_rec.end_item_unit_number);
27 fnd_file.put_line(FND_FILE.LOG, 'Employee Id' || p_drop_ship_line_rec.employee_id);
28 fnd_file.put_line(FND_FILE.LOG, 'Inventory_Item_Id' || p_drop_ship_line_rec.inventory_item_id);
29 fnd_file.put_line(FND_FILE.LOG, 'Charge Account Id ' || p_drop_ship_line_rec.charge_account_id);
30 END IF;
31 END;
32 /*-----------------------------------------------------------------
33 PROCEDURE : Purchase_Release
34 DESCRIPTION: For the each record that is passed to this procedure,
35 it will do the following:
36 1. Check for holds on the record
37 2. If not on holds, check for valid location
38 3. If location is valid, check for valid user
39 4. If user is valid, insert into PO_REQUISITION_INTERFACE and
40 OE_DROP_SHIP_SOURCES tables
41 This program will be called as a concurrent program or from the
42 workflow.
43 -----------------------------------------------------------------*/
44
45 Procedure Purchase_Release
46 ( p_api_version_number IN NUMBER
47 , p_drop_ship_tbl IN Drop_Ship_Tbl_Type
48 , p_mode IN VARCHAR2 := G_MODE_ONLINE
49 , x_drop_ship_tbl OUT NOCOPY Drop_Ship_Tbl_Type
50
51 , x_return_status OUT NOCOPY VARCHAR2
52
53 , x_msg_count OUT NOCOPY NUMBER
54
55 , x_msg_data OUT NOCOPY VARCHAR2
56
57 )
58 IS
59
60 l_api_version_number CONSTANT NUMBER := 1.0;
61 l_api_name CONSTANT VARCHAR2(30):= 'Purchase Release';
62 l_drop_ship_line_rec Drop_Ship_Line_Rec_Type;
63 l_drop_ship_tbl Drop_Ship_Tbl_Type;
64 l_x_drop_ship_tbl Drop_Ship_Tbl_Type;
65 l_drop_ship_source_id NUMBER;
66 l_invoke_verify_payment VARCHAR2(1) := 'Y';
67 l_return_status VARCHAR2(1);
68 l_result Varchar2(30);
69 l_count NUMBER;
70 l_msg_count NUMBER;
71 l_msg_data VARCHAR2(2000) := NULL;
72 l_user_id NUMBER;
73 l_resp_id NUMBER;
74 l_application_id NUMBER;
75 l_org_id NUMBER;
76 l_org_id2 NUMBER;
77 l_login_id NUMBER;
78 l_request_id NUMBER;
79 l_program_id NUMBER;
80 l_old_header_id NUMBER;
81 l_payment_type VARCHAR2(50);
82 l_item_type_code VARCHAR2(30);
83 l_ato_line_id NUMBER;
84 -- For Process Order
85 l_line_rec OE_ORDER_PUB.line_rec_type;
86 l_old_line_tbl OE_ORDER_PUB.line_tbl_type;
87 l_line_tbl OE_ORDER_PUB.line_tbl_type;
88 l_control_rec OE_GLOBALS.control_rec_type;
89
90 l_valid_employee VARCHAR2(25);
91 l_temp_employee_Id NUMBER; -- Temp ID got from profile option
92 l_order_source_id NUMBER;
93 l_source_document_type_id NUMBER;
94 item_purchase_enabled VARCHAR2(1); -- Fix for bug2097383
95 v_tmp NUMBER;
96 v_request_date DATE;
97 l_shippable_flag VARCHAR2(1);
98 l_ou_id NUMBER; -- R12.MOAC
99 l_exists varchar2(1); -- added for bug 7433481
100
101 Purchase_Release_Incomplete EXCEPTION;
102
103 Cursor C_Check_Employee_ID (x_employee_id NUMBER) IS
104 SELECT 'Yes '
105 FROM hr_employees_current_v
106 WHERE employee_id = x_employee_id;
107 Cursor C_Payment_Type(x_header_id NUMBER) IS
108 SELECT payment_type_code
109 FROM oe_order_headers
110 WHERE header_id = x_header_id;
111 Cursor Req_Date (v_line_id NUMBER) is
112 SELECT REQUEST_DATE
113 FROM OE_ORDER_LINES
114 WHERE LINE_ID = v_line_id;
115
116 --
117 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
118 --
119 BEGIN
120 IF l_debug_level > 0 THEN
121 oe_debug_pub.add('Entering Purchase Release' , 1 ) ;
122 END IF;
123
124 IF NOT FND_API.Compatible_API_Call
125 ( l_api_version_number
126 , p_api_version_number
127 , l_api_name
128 , G_PKG_NAME
129 )
130 THEN
131 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
132 END IF;
133
134 l_drop_ship_tbl := p_drop_ship_tbl;
135
136 -- l_user_id := FND_GLOBAL.USER_ID; -- Commented for bug 6118936
137 l_login_id := FND_GLOBAL.LOGIN_ID;
138 l_request_id := FND_GLOBAL.CONC_REQUEST_ID;
139 -- l_application_id := FND_GLOBAL.RESP_APPL_ID; -- Commented for bug 6118936
140 l_program_id := FND_GLOBAL.CONC_PROGRAM_ID;
141 -- l_resp_id := FND_GLOBAL.RESP_ID; -- Commented for bug 6118936
142 -- This change is required since we are dropping the profile OE_ORGANIZATION -- _ID. Change made by Esha.
143 l_org_id2 := FND_PROFILE.VALUE('OE_ORGANIZATION_ID');
144 l_org_id := OE_Sys_Parameters.VALUE('MASTER_ORGANIZATION_ID');
145
146 IF l_debug_level > 0 THEN
147 oe_debug_pub.add('User id => ' ||l_user_id,1);
148 oe_debug_pub.add('Responsibility id => ' ||l_resp_id,1);
149 oe_debug_pub.add('Application id => ' ||l_application_id,1);
150 oe_debug_pub.add('Organization id => ' ||l_org_id,1);
151 END IF;
152
153 l_old_header_id := 0;
154
155 FOR I IN 1..l_drop_ship_tbl.COUNT LOOP
156 BEGIN
157
158 IF l_debug_level > 0 THEN
159 oe_debug_pub.add('Processing Line ID : '||l_drop_ship_tbl(i).line_id,1);
160 END IF;
161 l_drop_ship_line_rec := l_drop_ship_tbl(I);
162
163
164 /* Added for the bug 6118936 */
165
166 l_user_id := wf_engine.GetItemAttrNumber( OE_GLOBALS.G_WFI_LIN , to_char(l_drop_ship_line_rec.line_id) , 'USER_ID');
167 l_resp_id := wf_engine.GetItemAttrNumber( OE_GLOBALS.G_WFI_LIN , to_char(l_drop_ship_line_rec.line_id) , 'RESPONSIBILITY_ID');
168 l_application_id := wf_engine.GetItemAttrNumber( OE_GLOBALS.G_WFI_LIN , to_char(l_drop_ship_line_rec.line_id) , 'APPLICATION_ID');
169
170
171 IF l_debug_level > 0 THEN
172 oe_debug_pub.add('User id => ' ||l_user_id,1);
173 oe_debug_pub.add('Responsibility id => ' ||l_resp_id,1);
174 oe_debug_pub.add('Application id => ' ||l_application_id,1);
175 oe_debug_pub.add('Organization id => ' ||l_org_id,1);
176 END IF;
177
178 /* End of bug 6118936 */
179
180
181 IF FND_PROFILE.VALUE('ONT_INCLUDED_ITEM_FREEZE_METHOD') = OE_GLOBALS.G_IIFM_PICK_RELEASE THEN
182 IF l_debug_level > 0 THEN
183 oe_debug_pub.add('may need to freeze inc items',5);
184 END IF;
185
186 SELECT item_type_code, ato_line_id
187 INTO l_item_type_code, l_ato_line_id
188 FROM oe_order_lines
189 WHERE line_id = l_drop_ship_line_rec.line_id;
190
191 IF (l_item_type_code = 'MODEL' OR
192 l_item_type_code = 'KIT' OR
193 l_item_type_code = 'CLASS') AND
194 l_ato_line_id is NULL THEN
195
196 l_return_status := OE_Config_Util.Freeze_Included_Items (l_drop_ship_line_rec.line_id);
197
198 IF l_debug_level > 0 THEN
199 oe_debug_pub.add('Freeze ret status '||l_return_status,5);
200 END IF;
201
202 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
203 RAISE Purchase_Release_Incomplete;
204 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
205 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
206 END IF;
207 END IF;
208
209 END IF;
210
211 IF l_drop_ship_line_rec.header_id <> l_old_header_id THEN
212 l_invoke_verify_payment := 'Y';
213 l_old_header_id := l_drop_ship_line_rec.header_id;
214 END IF;
215 OELOGO(p_drop_ship_line_rec => l_drop_ship_line_rec,
216 p_mode => p_mode);
217 IF l_debug_level > 0 THEN
218 oe_debug_pub.add('Source Type Code => '||l_drop_ship_line_rec.source_type_code,5);
219 END IF;
220 IF (l_drop_ship_line_rec.item_type_code <> OE_GLOBALS.G_ITEM_SERVICE) THEN
221 OPEN C_Payment_Type(l_drop_ship_line_rec.header_id);
222 FETCH C_Payment_Type INTO l_payment_type;
223 CLOSE C_Payment_Type;
224
225 --IF l_payment_type = 'CREDIT_CARD' THEN
226 IF l_debug_level > 0 THEN
227 oe_debug_pub.add('before calling verify payment ',1);
228 END IF;
229 IF l_invoke_verify_payment = 'Y' THEN
230 IF l_debug_level > 0 THEN
231 oe_debug_pub.add('Before calling verify payment for header '||to_char(l_drop_ship_line_rec.header_id),1);
232 END IF;
233
234 OE_VERIFY_PAYMENT_PUB.VERIFY_PAYMENT(
235 p_header_id => l_drop_ship_line_rec.header_id,
236 p_calling_action => 'PICKING',
237 p_delayed_request => NULL,
238 p_return_status => l_return_status,
239 p_msg_count => l_msg_count,
240 p_msg_data => l_msg_data);
241
242 l_invoke_verify_payment := 'N';
243
244 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) then
245 IF l_debug_level > 0 THEN
246 oe_debug_pub.add('Exception: verify payment returns failure',1);
247 END IF;
248 OE_MSG_PUB.Add_Text(p_message_text => l_msg_data);--bug4683857
249 OE_MSG_PUB.Save_API_Messages; --bug4683857
250 IF l_return_status = FND_API.G_RET_STS_ERROR then
251 RAISE Purchase_Release_Incomplete;
252 ELSE
253 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
254 END IF;
255 END IF;
256 END IF;
257 --END IF; -- for 2412678
258
259 IF l_debug_level > 0 THEN
260 oe_debug_pub.add('After the call to verify payment ',5);
261 END IF;
262
263 /* Check if there are holds on this line or its header,
264 If yes, bypass this line */
265
266 IF l_debug_level > 0 THEN
267 oe_debug_pub.add('Calling check holds including activity based holds',5);
268 END IF;
269 OE_Holds_PUB.Check_Holds
270 ( p_api_version => 1.0
271 ,p_init_msg_list => FND_API.G_FALSE
272 ,p_commit => FND_API.G_FALSE
273 ,p_validation_level => FND_API.G_VALID_LEVEL_FULL
274 ,x_return_status => l_return_status
275 ,x_msg_count => l_msg_count
276 ,x_msg_data => l_msg_data
277 ,p_line_id => l_drop_ship_line_rec.line_id
278 ,p_hold_id => NULL
279 ,p_entity_code => NULL
280 ,p_entity_id => NULL
281 ,p_wf_item => 'OEOL'
282 ,p_wf_activity => 'PUR_REL_THE_LINE'
283 ,x_result_out => l_result);
284
285 IF l_debug_level > 0 THEN
286 oe_debug_pub.add('After calling check holds return status => '||l_return_status,1);
287 END IF;
288 IF l_debug_level > 0 THEN
289 oe_debug_pub.add('Return result => '||l_result,1);
290 END IF;
291
292 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) then
293
294 OE_MSG_PUB.Add_Text(p_message_text => l_msg_data);--bug4683857
295 OE_MSG_PUB.Save_API_Messages; --bug4683857
296 IF l_return_status = FND_API.G_RET_STS_ERROR then
297 RAISE Purchase_Release_Incomplete;
298 ELSE
299 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
300 END IF;
301 END IF;
302
303 IF (l_result = FND_API.G_TRUE) then
304 FND_MESSAGE.SET_NAME('ONT','OE_II_HOLD_FOUND');
305 OE_MSG_PUB.Add;
306 l_drop_ship_line_rec.result := OE_Purchase_Release_PVT.G_RES_ONHOLD;
307 l_drop_ship_line_rec.return_status := FND_API.G_RET_STS_ERROR;
308 RAISE Purchase_Release_Incomplete;
309 END IF;
310 END IF;/* If line is not a service line */
311
312 IF l_drop_ship_line_rec.deliver_to_location_id is null THEN
313 FND_MESSAGE.SET_NAME('ONT','OE_DS_NO_LOC_LINK');
314 OE_MSG_PUB.Add;
315 l_return_status := FND_API.G_RET_STS_ERROR;
316 IF l_debug_level > 0 THEN
317 oe_debug_pub.add('Exception, Deliver to Location is not setup correctly',1);
318 END IF;
319 RAISE Purchase_Release_Incomplete;
320 END IF;
321
322 IF l_debug_level > 0 THEN
323 oe_debug_pub.add('Deliver to location => ' || l_drop_ship_line_rec.deliver_to_location_id,1);
324 END IF;
325
326 -- Check to see if the user who entered the order is a valid employee
327
328 IF l_debug_level > 0 THEN
329 oe_debug_pub.add('Employee id check '||l_drop_ship_line_rec.employee_id,1);
330 END IF;
331 OPEN C_Check_Employee_ID (l_drop_ship_line_rec.employee_id);
332 FETCH C_Check_Employee_ID INTO l_valid_employee;
333 CLOSE C_Check_Employee_ID;
334
335 IF l_debug_level > 0 THEN
336 oe_debug_pub.add('Is this a valid employee ? '||l_valid_employee,1);
337 END IF;
338
339 IF l_valid_employee is null THEN
340
341 IF l_debug_level > 0 THEN
342 oe_debug_pub.add('Invalid employee id. checking whether this is self-service order..',5);
343 END IF;
344 --R12.MOAC
345 SELECT nvl(ORDER_SOURCE_ID,0),nvl(SOURCE_DOCUMENT_TYPE_ID,0),org_id
346 INTO l_order_source_id,l_source_document_type_id,l_ou_id
347 FROM OE_ORDER_HEADERS_ALL
348 WHERE HEADER_ID = l_drop_ship_line_rec.header_id;
349
350 IF l_debug_level > 0 THEN
351 oe_debug_pub.add('Order source id for this order is '||to_char ( l_order_source_id ) , 5 ) ;
352 oe_debug_pub.add('Source document type id for this order is '||to_char ( l_source_document_type_id ) , 5 ) ;
353 END IF;
354 IF l_order_source_id between 11 and 19 OR
355 l_source_document_type_id between 11 and 19 THEN -- 11 and 19 for self service orders
356 --R12.MOAC Start
357 -- Call to Oe_Sys_Parameters.Value as profile moved to system parameter
358 /*
359 -- changed call from fnd_profile.value to oe_profile.value to retrieve profile in created_by context
360 l_temp_employee_id := oe_profile.value(p_header_id => null,
361 p_line_id => l_drop_ship_line_rec.line_id,
362 p_profile_option_name => 'ONT_EMP_ID_FOR_SS_ORDERS');
363 */
364 l_temp_employee_id := Oe_Sys_Parameters.Value('ONT_EMP_ID_FOR_SS_ORDERS',l_ou_id);
365 --R12.MOAC End
366 IF l_debug_level > 0 THEN
367 oe_debug_pub.add('Assigning employee id to this order as -> '||to_char ( l_temp_employee_id ),5);
368 END IF;
369 l_drop_ship_line_rec.employee_id := l_temp_employee_id;
370 OPEN C_Check_Employee_ID (l_temp_employee_id);
371 FETCH C_Check_Employee_ID INTO l_valid_employee;
372 CLOSE C_Check_Employee_ID;
373 END IF;
374 ELSE
375 IF l_debug_level > 0 THEN
376 oe_debug_pub.add('Employee id set for this order '||to_char ( l_drop_ship_line_rec.employee_id ),5);
377 END IF;
378 END IF;
379
380 IF l_valid_employee is null THEN
381 IF l_debug_level > 0 THEN
382 oe_debug_pub.add('No records found for this employee id',1);
383 END IF;
384 FND_MESSAGE.SET_NAME('ONT','OE_DS_NOT_VALID_EMP');
385 OE_MSG_PUB.Add;
386 RAISE Purchase_Release_Incomplete;
387 END IF;
388
389 -- Fix for the bug2097383 and 2201362
390 SELECT PURCHASING_ENABLED_FLAG
391 INTO item_purchase_enabled
392 FROM MTL_SYSTEM_ITEMS
393 WHERE INVENTORY_ITEM_ID = l_drop_ship_line_rec.inventory_item_id
394 AND ORGANIZATION_ID = l_drop_ship_line_rec.ship_from_org_id;
395
396 IF l_debug_level > 0 THEN
397 OE_DEBUG_PUB.add('Item Purchase Enabled : '||item_purchase_enabled,1);
398 END IF;
399
400 IF nvl(item_purchase_enabled,'N') <> 'Y' THEN
401 FND_MESSAGE.SET_NAME('PO','PO_RI_INVALID_ITEM_SRC_VEND_P');
402 OE_MSG_PUB.Add;
403 IF l_debug_level > 0 THEN
404 OE_DEBUG_PUB.add('Exception, item is not purchasing enabled',1);
405 END IF;
406 RAISE Purchase_Release_Incomplete;
407 END IF;
408
409 -- Fix for #2003381
410
411 IF l_drop_ship_line_rec.schedule_ship_date is null THEN
412
413 OPEN REQ_DATE (l_drop_ship_line_rec.line_id);
414 FETCH REQ_DATE INTO v_request_date;
415 CLOSE REQ_DATE;
416
417 if v_request_date is null then
418 -- raise error, request date cannot be null #2003381.
419 FND_MESSAGE.SET_NAME('ONT','OE_REQUEST_DATE_FOR_PR_REQD');
420 OE_MSG_PUB.Add;
421 IF l_debug_level > 0 THEN
422 OE_DEBUG_PUB.add('Exception, Reqeust Date is either null or not valid',1);
423 END IF;
424 RAISE Purchase_Release_Incomplete;
425 end if;
426 END IF;
427
428 IF l_debug_level > 0 THEN
429 oe_debug_pub.add('Source type code ' || l_drop_ship_line_rec.source_type_code , 1 ) ;
430 oe_debug_pub.add('Item type code ' || l_drop_ship_line_rec.item_type_code , 1 ) ;
431 END IF;
432
433
434 IF NOT OE_GLOBALS.Equal(l_drop_ship_line_rec.source_type_code, OE_GLOBALS.G_SOURCE_EXTERNAL) THEN
435 IF l_debug_level > 0 THEN
436 oe_debug_pub.add('Wrong source type '|| l_drop_ship_line_rec.line_id,3);
437 END IF;
438 l_drop_ship_line_rec.result := G_RES_NOT_APPLICABLE;
439 l_drop_ship_line_rec.return_status := FND_API.G_RET_STS_SUCCESS;
440 goto end_of_for_loop;
441 ELSE
442 SELECT shippable_flag
443 INTO l_shippable_flag
444 FROM oe_order_lines
445 WHERE line_id = l_drop_ship_line_rec.line_id;
446
447 IF nvl(l_shippable_flag, 'N') = 'N' THEN
448 IF l_debug_level > 0 THEN
449 oe_debug_pub.add('Non shippable!! '|| l_drop_ship_line_rec.line_id,3);
450 END IF;
451 l_drop_ship_line_rec.result := G_RES_NOT_APPLICABLE;
452 l_drop_ship_line_rec.return_status := FND_API.G_RET_STS_SUCCESS;
453 goto end_of_for_loop;
454 END IF;
455
456 --Fix for bug#2678070-Begin
457
458 IF l_drop_ship_line_rec.schedule_ship_date is null THEN
459 IF l_debug_level > 0 THEN
460 oe_debug_pub.add('Updating sch ship date from request date',1);
461 END IF;
462
463 -- Turning off Perform Scheduling Flag Before calling
464 -- this procedure since this procedure is calling Process Order
465 -- which in turn will call scheduling if this flag is not turned off.
466
467 -- Bug# 4189838 Start
468 OE_SCHEDULE_UTIL.OESCH_PERFORM_SCHEDULING := 'N';
469 -- Bug# 4189838 End
470
471 OE_LINE_UTIL.Query_Row(p_line_id => l_drop_ship_line_rec.line_id
472 ,x_line_rec => l_line_rec);
473
474 l_old_line_tbl(1) := l_line_rec;
475
476 l_line_rec.schedule_ship_date := l_line_rec.request_date;
477
478 l_line_rec.operation := OE_GLOBALS.G_OPR_UPDATE;
479 l_line_tbl(1) := l_line_rec;
480
481 /* Start Audit Trail */
482 l_line_tbl(1).change_reason := 'SYSTEM';
483 /* End Audit Trail */
484
485 l_control_rec.controlled_operation := TRUE;
486 l_control_rec.default_attributes := TRUE;
487 l_control_rec.change_attributes := TRUE;
488 l_control_rec.validate_entity := TRUE;
489 l_control_rec.write_to_DB := TRUE;
490 l_control_rec.check_security := TRUE;
491 l_control_rec.process_entity := OE_GLOBALS.G_ENTITY_LINE;
492 l_control_rec.process := TRUE;
493
494 -- Instruct API to retain its caches
495
496 l_control_rec.clear_api_cache := FALSE;
497 l_control_rec.clear_api_requests := FALSE;
498
499 IF l_debug_level > 0 THEN
500 oe_debug_pub.add('Now calling Process Order from OEXVDSPB',1);
501 END IF;
502
503 OE_ORDER_PVT.Lines
504 (p_validation_level => FND_API.G_VALID_LEVEL_NONE
505 ,p_control_rec => l_control_rec
506 ,p_x_line_tbl => l_line_tbl
507 ,p_x_old_line_tbl => l_old_line_tbl
508 ,x_return_status => l_return_status);
509
510 IF l_debug_level > 0 THEN
511 oe_debug_pub.add('After calling process lines' , 1 ) ;
512 oe_debug_pub.add('Return Status ' || l_return_status , 1 ) ;
513 END IF;
514
515 -- Bug# 4189838 Start
516 OE_SCHEDULE_UTIL.OESCH_PERFORM_SCHEDULING := 'Y';
517 -- Bug# 4189838 End
518
519 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
520 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
521 ELSIF l_return_status = FND_API.G_RET_STS_ERROR THEN
522 RAISE FND_API.G_EXC_ERROR;
523 END IF;
524
525 IF l_debug_level > 0 THEN
526 OE_DEBUG_PUB.add('Calling Process Request and Notify',1);
527 END IF;
528
529 OE_ORDER_PVT.Process_Requests_And_notify
530 ( p_process_requests => l_control_rec.process
531 ,p_notify => TRUE
532 ,x_return_status => l_return_status
533 ,p_line_tbl => l_line_tbl
534 ,p_old_line_tbl => l_old_line_tbl);
535
536 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
537 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
538 ELSIF l_return_status = FND_API.G_RET_STS_ERROR THEN
539 RAISE FND_API.G_EXC_ERROR;
540 END IF;
541
542 END IF; /* If schedule_ship_date is null */
543 --Fix for bug#2678070-End
544
545 begin
546 select 'Y' -- added begin block for bug 7433481
547 into l_exists
548 from oe_drop_ship_sources
549 where line_id=l_drop_ship_line_rec.line_id;
550 exception
551 when no_data_found then
552 l_exists:='N';
553 when too_many_rows then
554 l_exists:='Y';
555 end;
556 oe_debug_pub.add('Record already exists in drop ship table,Y OR N ? '||l_exists);
557 if l_exists='N' then --added for bug 7433481
558
559 SELECT oe_drop_ship_source_s.nextval
560 INTO l_drop_ship_source_id
561 FROM dual;
562
563 /* insert into po_requisition_interface table */
564
565 IF l_debug_level > 0 THEN
566 oe_debug_pub.add('Before inserting into Requisitions Interface Table' , 1 ) ;
567 END IF;
568
569 Insert_Into_Po_Req_Interface
570 (p_drop_ship_line_rec => l_drop_ship_line_rec
571 ,x_return_status => l_return_status
572 ,p_user_id => l_user_id
573 ,p_resp_id => l_resp_id
574 ,p_application_id => l_application_id
575 ,p_org_id => l_org_id
576 ,p_login_id => l_login_id
577 ,p_drop_ship_source_id => l_drop_ship_source_id
578 );
579
580 IF l_debug_level > 0 THEN
581 oe_debug_pub.add('After inserting ',1);
582 END IF;
583
584 IF l_return_status <> FND_API.G_RET_STS_SUCCESS then
585 IF l_return_status = FND_API.G_RET_STS_ERROR then
586 RAISE FND_API.G_EXC_ERROR;
587 ELSE
588 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
589 END IF;
590 END IF;
591
592 /* insert into oe_drop_ship_sources table */
593
594 IF l_debug_level > 0 THEN
595 oe_debug_pub.add('Inserting Dropship Source Record',1);
596 END IF;
597
598 Insert_Drop_Ship_Source
599 ( p_drop_ship_line_rec => l_drop_ship_line_rec
600 ,x_return_status => l_return_status
601 ,p_user_id => l_user_id
602 ,p_resp_id => l_resp_id
603 ,p_application_id => l_application_id
604 ,p_org_id => l_org_id
605 ,p_login_id => l_login_id
606 ,p_drop_ship_source_id => l_drop_ship_source_id
607 );
608
609 IF l_debug_level > 0 THEN
610 oe_debug_pub.add('Inserted into Dropship Source Record', 1);
611 END IF;
612
613 IF l_return_status <> FND_API.G_RET_STS_SUCCESS then
614 IF l_return_status = FND_API.G_RET_STS_ERROR then
615 RAISE FND_API.G_EXC_ERROR;
616 ELSE
617 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
618 END IF;
619 END IF;
620 end if; --added for bug 7433481
621 END IF;
622
623 l_drop_ship_line_rec.return_status := FND_API.G_RET_STS_SUCCESS;
624 l_drop_ship_line_rec.result := OE_Purchase_Release_PVT.G_RES_COMPLETE;
625
626 EXCEPTION
627 WHEN Purchase_Release_Incomplete THEN
628 IF l_debug_level > 0 THEN
629 oe_debug_pub.add('Exception Purchase Release Incomplete',1);
630 oe_debug_pub.add('Purchase Release activity is incomplete for line ID : '||l_drop_ship_line_rec.line_id,1);
631 END IF;
632 l_drop_ship_line_rec.return_status := FND_API.G_RET_STS_ERROR;
633 IF l_drop_ship_line_rec.result <> OE_Purchase_Release_PVT.G_RES_ONHOLD THEN
634 l_drop_ship_line_rec.result := OE_Purchase_Release_PVT.G_RES_INCOMPLETE;
635 END IF;
636 --OE_MSG_PUB.Add_Exc_Msg (G_PKG_NAME ,'Purchase_Release'); bug 4683857
637 WHEN FND_API.G_EXC_ERROR THEN
638 l_drop_ship_line_rec.return_status := FND_API.G_RET_STS_UNEXP_ERROR;
639 -- Changes for Bug - 2352589
640 IF l_drop_ship_line_rec.result <> OE_Purchase_Release_PVT.G_RES_ONHOLD THEN
641 l_drop_ship_line_rec.result := OE_Purchase_Release_PVT.G_RES_INCOMPLETE;
642 END IF;
643 OE_MSG_PUB.Add_Exc_Msg (G_PKG_NAME ,'Purchase_Release');
644 IF l_debug_level > 0 THEN
645 oe_debug_pub.add('AN EXPECTED ERROR RAISED..'||SQLERRM , 1 ) ;
646 END IF;
647 RAISE FND_API.G_EXC_ERROR; -- bug 4683857
648 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
649 OE_MSG_PUB.Add_Exc_Msg (G_PKG_NAME ,'Purchase_Release');
650 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
651 IF l_debug_level > 0 THEN
652 oe_debug_pub.add('AN UNEXPECTED ERROR RAISED..'||SQLERRM , 1 ) ;
653 END IF;
654 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
655 WHEN OTHERS THEN
656 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
657 OE_MSG_PUB.Add_Exc_Msg ( G_PKG_NAME , 'Purchase_Release');
658 END IF;
659 IF l_debug_level > 0 THEN
660 oe_debug_pub.add('OTHER ERROR RAISED..'||SQLERRM , 1 ) ;
661 END IF;
662 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
663 END;
664 <<end_of_for_loop>>
665 l_x_drop_ship_tbl(I) := l_drop_ship_line_rec;
666 l_old_header_id := l_drop_ship_line_rec.header_id;
667 END LOOP;
668 x_drop_ship_tbl := l_x_drop_ship_tbl;
669 IF l_debug_level > 0 THEN
670 oe_debug_pub.add('Exit Purchase Release for line ID : '||l_drop_ship_line_rec.line_id , 1 ) ;
671 END IF;
672
673 EXCEPTION
674
675 WHEN FND_API.G_EXC_ERROR THEN
676 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
677 OE_MSG_PUB.Add_Exc_Msg (G_PKG_NAME, 'Purchase_Release');
678 END IF; --bug 5030428
679 --OE_MSG_PUB.reset_msg_context('Purchase_Release'); 5030428
680 RAISE;
681
682 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
683 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
684 OE_MSG_PUB.Add_Exc_Msg (G_PKG_NAME, 'Purchase_Release');
685 END IF; --bug 5030428
686 --OE_MSG_PUB.reset_msg_context('Purchase_Release'); 5030428
687 RAISE;
688
689 WHEN OTHERS THEN
690 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
691 OE_MSG_PUB.Add_Exc_Msg (G_PKG_NAME, 'Purchase_Release');
692 END IF;
693 --OE_MSG_PUB.reset_msg_context('Purchase_Release'); 5030428
694 END Purchase_Release;
695
696 /*-----------------------------------------------------------------
697 PROCEDURE : Insert_Into_Po_Req_Interface
698 DESCRIPTION:
699 -----------------------------------------------------------------*/
700
701 Procedure Insert_Into_Po_Req_Interface
702 (p_drop_ship_line_rec IN Drop_Ship_Line_Rec_Type
703 ,x_return_status OUT NOCOPY VARCHAR2
704
705 ,p_user_id IN NUMBER
706 ,p_resp_id IN NUMBER
707 ,p_application_id IN NUMBER
708 ,p_org_id IN NUMBER
709 ,p_login_id IN NUMBER
710 ,p_drop_ship_source_id IN NUMBER
711 )
712 IS
713 l_destination_type_code VARCHAR2(25) ;
714 l_authorization_status VARCHAR2(25) := 'APPROVED';
715 l_project_accounting_context VARCHAR2(30) := null;
716 l_schedule_ship_date DATE := null;
717 l_stock_enabled_flag VARCHAR2(1); /* 1835314 */
718 l_source_code VARCHAR2(30); /*2058542 */
719 l_prof_value NUMBER; --Fix for bug#2172019
720 l_item_revision VARCHAR2(3);
721 l_revision_control_code NUMBER;
722 l_ou_org_id NUMBER;
723
724 --
725 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
726 --
727 BEGIN
728
729 IF l_debug_level > 0 THEN
730 oe_debug_pub.add( 'BEFORE INSERTING RECORDS INTO PO REQUISITIONS INTERFACE ' , 1 ) ;
731 END IF;
732
733 IF p_drop_ship_line_rec.project_id is not null AND
734 p_drop_ship_line_rec.project_id <> FND_API.G_MISS_NUM AND
735 p_drop_ship_line_rec.project_id <> -1 THEN
736 l_project_accounting_context := 'Y';
737 ELSE
738 l_project_accounting_context := null;
739 END IF;
740
741 IF p_drop_ship_line_rec.schedule_ship_date is null THEN
742 l_schedule_ship_date := p_drop_ship_line_rec.request_date;
743 ELSE
744 l_schedule_ship_date := p_drop_ship_line_rec.schedule_ship_date;
745 END IF;
746
747 BEGIN
748 -- SQL Performance ID 14882612
749 -- Replaced org_organization_definitions with inv_organization_info_v which
750 -- includes the validation that the organization is inventory enabled.
751 -- Please see bug 3742393 for details.
752 -- The table FND_PRODUCT_GROUPs (in inv_organization_info_v) always has one row and hence
753 -- FTS on this table can be ignored.
754
755 SELECT msi.stock_enabled_flag ,revision_qty_control_code
756 INTO l_stock_enabled_flag, l_revision_control_code
757 FROM mtl_system_items msi,inv_organization_info_v org
758 WHERE msi.inventory_item_id = p_drop_ship_line_rec.inventory_item_id
759 AND org.organization_id = msi.organization_id
760 AND org.organization_id = p_drop_ship_line_rec.ship_from_org_id;
761
762 IF l_stock_enabled_flag = 'N' THEN
763 l_destination_type_code := 'EXPENSE';
764 ELSE
765 l_destination_type_code := 'INVENTORY';
766 END IF;
767
768 EXCEPTION WHEN NO_DATA_FOUND THEN
769 l_destination_type_code := 'INVENTORY';
770 l_revision_control_code := 1;
771 END;
772
773 -- added for bug 2201362
774 if nvl(l_revision_control_code,1)=2 then
775 IF NVL(FND_PROFILE.VALUE('INV_PURCHASING_BY_REVISION'),2) = 1 THEN
776 BEGIN
777 select MAX(revision)
778 into l_item_revision
779 from mtl_item_revisions mir,
780 mtl_system_items mti
781 where mir.inventory_item_id = mti.inventory_item_id
782 and mir.organization_id = mti.organization_id
783 and mti.inventory_item_id = p_drop_ship_line_rec.inventory_item_id
784 and mti.organization_id = p_drop_ship_line_rec.ship_from_org_id
785 and mti.REVISION_QTY_CONTROL_CODE =2 /* Means item is under revision control */
786 and mir.effectivity_date < SYSDATE+1
787 and mir.effectivity_date =
788 ( select MAX(mir1.effectivity_date)
789 from mtl_item_revisions mir1
790 where mir1.inventory_item_id = mir.inventory_item_id
791 and mir1.organization_id = mir.organization_id
792 and mir1.effectivity_date < SYSDATE+1
793 );
794
795 EXCEPTION
796 WHEN NO_DATA_FOUND THEN
797 l_item_revision := NULL;
798 WHEN OTHERS THEN
799 IF l_debug_level > 0 THEN
800 oe_debug_pub.add( 'ERROR WHILE RETRIEVING ITEM REVISION INFO '||SQLERRM , 1 ) ;
801 END IF;
802 l_item_revision := NULL;
803 END;
804 END IF;
805 END IF;
806
807 IF l_debug_level > 0 THEN
808 oe_debug_pub.add( '------------INSERTING VALUES: ----------' , 1 ) ;
809 oe_debug_pub.add( 'DESTINATION_ORGANIZATION_ID: '||P_DROP_SHIP_LINE_REC.SHIP_FROM_ORG_ID , 1 ) ;
810 oe_debug_pub.add( 'DELIVER_TO_LOCATION_ID : '||P_DROP_SHIP_LINE_REC.DELIVER_TO_LOCATION_ID , 1 ) ;
811 oe_debug_pub.add( 'DELIVER_TO_REQUESTOR_ID : '||P_DROP_SHIP_LINE_REC.EMPLOYEE_ID , 1 ) ;
812 oe_debug_pub.add( 'NEED_BY_DATE : '||L_SCHEDULE_SHIP_DATE , 1 ) ;
813 oe_debug_pub.add( 'LAST_UPDATED_BY : '||P_USER_ID , 1 ) ;
814 oe_debug_pub.add( 'LAST_UPDATE_LOGIN : '||P_LOGIN_ID , 1 ) ;
815 oe_debug_pub.add( 'DESTINATION_TYPE_CODE : '||L_DESTINATION_TYPE_CODE , 1 ) ;
816 oe_debug_pub.add( 'QUANTITY : '||P_DROP_SHIP_LINE_REC.OPEN_QUANTITY , 1 ) ;
817 oe_debug_pub.add( 'AUTHORIZATION_STATUS : '||L_AUTHORIZATION_STATUS , 1 ) ;
818 oe_debug_pub.add( 'PREPARER_ID : '||P_DROP_SHIP_LINE_REC.EMPLOYEE_ID , 1 ) ;
819 oe_debug_pub.add( 'ITEM_ID : '||P_DROP_SHIP_LINE_REC.INVENTORY_ITEM_ID , 1 ) ;
820 oe_debug_pub.add( 'STOCK ENABLED FLAG : '||L_STOCK_ENABLED_FLAG , 1 ) ;
821 oe_debug_pub.add( 'CHARGE_ACCOUNT_ID : '||P_DROP_SHIP_LINE_REC.CHARGE_ACCOUNT_ID , 1 ) ;
822 oe_debug_pub.add( 'INTERFACE_SOURCE_LINE_ID : '||P_DROP_SHIP_SOURCE_ID , 1 ) ;
823 oe_debug_pub.add( 'PROJECT_ID : '||P_DROP_SHIP_LINE_REC.PROJECT_ID , 1 ) ;
824 oe_debug_pub.add( 'TASK_ID : '||P_DROP_SHIP_LINE_REC.TASK_ID , 1 ) ;
825 oe_debug_pub.add( 'END_ITEM_UNIT_NUMBER : '||P_DROP_SHIP_LINE_REC.END_ITEM_UNIT_NUMBER , 1 ) ;
826 oe_debug_pub.add( 'PROJECT_ACCOUNTING_CONTEXT : '||L_PROJECT_ACCOUNTING_CONTEXT , 1 ) ;
827 oe_debug_pub.add( 'UNIT LIST PRICE : '||TO_CHAR ( P_DROP_SHIP_LINE_REC.UNIT_LIST_PRICE ) , 1 ) ;
828 oe_debug_pub.add( 'DESTINATION TYPE CODE : '||L_DESTINATION_TYPE_CODE , 1 ) ;
829 oe_debug_pub.add( 'ITEM REVISION : '||L_ITEM_REVISION , 1 ) ;
830 END IF;
831
832 IF l_debug_level > 0 THEN
833 oe_debug_pub.add( '----------------------' , 1 ) ;
834 END IF;
835
836 -- Fix for bug2058542
837 l_source_code :=fnd_profile.value('ONT_SOURCE_CODE');
838 l_prof_value :=fnd_profile.value('ONT_POPULATE_BUYER'); --Fix for bug#2172019
839
840 IF PO_CODE_RELEASE_GRP.Current_Release >= PO_CODE_RELEASE_GRP.PRC_11i_Family_Pack_J THEN
841 -- SQL Performance ID 14882656
842 -- Replaced org_organization_definitions with inv_organization_info_v which
843 -- includes the validation that the organization is inventory enabled.
844 -- Please see bug 3742393 for details.
845 -- The table FND_PRODUCT_GROUPs (in inv_organization_info_v) always has one row and hence
846 -- FTS on this table can be ignored.
847
848 SELECT operating_unit
849 INTO l_ou_org_id
850 FROM inv_organization_info_v
851 WHERE organization_id = p_drop_ship_line_rec.ship_from_org_id;
852
853 END IF;
854
855
856 INSERT INTO po_requisitions_interface_all
857 (interface_source_code,
858 destination_organization_id,
859 deliver_to_location_id,
860 deliver_to_requestor_id,
861 need_by_date,
862 last_updated_by,
863 last_update_date,
864 last_update_login,
865 creation_date,
866 created_by,
867 destination_type_code,
868 quantity,
869 uom_code,
870 secondary_quantity, -- OPM
871 secondary_uom_code, -- OPM
872 preferred_grade, -- OPM
873 authorization_status,
874 preparer_id,
875 item_id,
876 charge_account_id,
877 accrual_account_id, -- OPM
878 interface_source_line_id,
879 source_type_code,
880 unit_price,
881 project_id,
882 task_id,
883 end_item_unit_number,
884 project_accounting_context,
885 item_revision,
886 suggested_buyer_id, -- Fix for bug 2122969
887 item_description,
888 org_id)
889 VALUES
890 (
891 l_source_code,
892 p_drop_ship_line_rec.ship_from_org_id,
893 p_drop_ship_line_rec.deliver_to_location_id,
894 p_drop_ship_line_rec.employee_id,
895 l_schedule_ship_date,
896 p_user_id,
897 SYSDATE,
898 p_login_id,
899 SYSDATE,
900 p_user_id,
901 l_destination_type_code,
902 p_drop_ship_line_rec.open_quantity,
903 p_drop_ship_line_rec.uom_code,
904 p_drop_ship_line_rec.open_quantity2, -- OPM
905 p_drop_ship_line_rec.uom2_code, -- OPM
906 p_drop_ship_line_rec.preferred_grade, -- OPM
907 l_authorization_status,
908 p_drop_ship_line_rec.employee_id,
909 p_drop_ship_line_rec.inventory_item_id,
910 p_drop_ship_line_rec.charge_account_id,
911 p_drop_ship_line_rec.accrual_account_id, -- OPM
912 p_drop_ship_source_id,
913 'VENDOR',
914 NULL,
915 decode(p_drop_ship_line_rec.project_id, -1, NULL, p_drop_ship_line_rec.project_id),
916 decode(p_drop_ship_line_rec.task_id, -1, NULL, p_drop_ship_line_rec.task_id),
917 decode(p_drop_ship_line_rec.end_item_unit_number, '-1', NULL, p_drop_ship_line_rec.end_item_unit_number),
918 l_project_accounting_context,
919 l_item_revision,
920 decode(nvl(l_prof_value,0),1,p_drop_ship_line_rec.employee_id,NULL), -- Modified Fix for bug 2122969 through bug 2172019
921 p_drop_ship_line_rec.item_description,
922 l_ou_org_id);
923
924 IF l_debug_level > 0 THEN
925 oe_debug_pub.add( 'END OF INSERT_INTO_PO_REQ_INTERFACE' , 1 ) ;
926 END IF;
927 x_return_status := FND_API.G_RET_STS_SUCCESS;
928
929 EXCEPTION
930
931 WHEN OTHERS THEN
932
933 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
934 THEN
935 OE_MSG_PUB.Add_Exc_Msg
936 ( G_PKG_NAME
937 , 'Insert_Into_Po_Req_Interface'
938 );
939 END IF;
940
941 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
942
943 END Insert_Into_Po_Req_Interface;
944
945 /*-----------------------------------------------------------------
946 PROCEDURE : Insert_Drop_Ship_Source
947 DESCRIPTION:
948 -----------------------------------------------------------------*/
949
950 Procedure Insert_Drop_Ship_Source
951 (p_drop_ship_line_rec IN Drop_Ship_Line_Rec_Type
952 ,x_return_status OUT NOCOPY VARCHAR2
953
954 ,p_user_id IN NUMBER
955 ,p_resp_id IN NUMBER
956 ,p_application_id IN NUMBER
957 ,p_org_id IN NUMBER
958 ,p_login_id IN NUMBER
959 ,p_drop_ship_source_id IN NUMBER
960 )
961 IS
962 --
963 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
964 --
965 BEGIN
966
967 IF l_debug_level > 0 THEN
968 oe_debug_pub.add( 'START OF INSERT_DROP_SHIP_SOURCE' , 1 ) ;
969 END IF;
970
971
972 INSERT INTO oe_drop_ship_sources
973 (drop_ship_source_id,
974 header_id,
975 line_id,
976 destination_organization_id,
977 last_updated_by,
978 last_update_date,
979 last_update_login,
980 creation_date,
981 created_by,
982 org_id
983 )
984 VALUES
985 (p_drop_ship_source_id,
986 p_drop_ship_line_rec.header_id,
987 p_drop_ship_line_rec.line_id,
988 p_drop_ship_line_rec.ship_from_org_id,
989 p_user_id,
990 SYSDATE,
991 p_login_id,
992 SYSDATE,
993 p_user_id,
994 p_org_id
995 );
996
997 IF l_debug_level > 0 THEN
998 oe_debug_pub.add( 'END OF INSERT_DROP_SHIP_SOURCE' , 1 ) ;
999 END IF;
1000
1001 x_return_status := FND_API.G_RET_STS_SUCCESS;
1002
1003 EXCEPTION
1004
1005 WHEN OTHERS THEN
1006
1007 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1008 THEN
1009 OE_MSG_PUB.Add_Exc_Msg
1010 ( G_PKG_NAME
1011 , 'Insert_Drop_Ship_Source'
1012 );
1013 END IF;
1014
1015 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1016 END Insert_Drop_Ship_Source;
1017
1018 /*-----------------------------------------------------------------
1019 PROCEDURE : Associate_address
1020 DESCRIPTION:
1021 -----------------------------------------------------------------*/
1022
1023 Procedure Associate_address(p_drop_ship_line_rec IN Drop_Ship_Line_Rec_Type
1024 ,x_drop_ship_line_rec OUT NOCOPY Drop_Ship_Line_Rec_Type
1025
1026 ,x_return_status OUT NOCOPY VARCHAR2)
1027
1028 IS
1029 l_drop_ship_line_rec Drop_Ship_Line_Rec_Type;
1030 --
1031 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
1032 --
1033 BEGIN
1034 l_drop_ship_line_rec := p_drop_ship_line_rec;
1035
1036 x_drop_ship_line_rec := l_drop_ship_line_rec;
1037 IF l_drop_ship_line_rec.deliver_to_location_id = -1 THEN
1038 x_return_status := FND_API.G_RET_STS_ERROR;
1039 FND_MESSAGE.SET_NAME('ONT','OE_DS_NO_LOC_LINK');
1040 OE_MSG_PUB.Add;
1041 ELSE
1042 x_return_status := FND_API.G_RET_STS_SUCCESS;
1043 END IF;
1044 END Associate_address;
1045
1046 /*-----------------------------------------------------------------
1047 PROCEDURE : Get_Eligible_lines
1048 DESCRIPTION:
1049 -----------------------------------------------------------------*/
1050
1051 Procedure Get_Eligible_lines
1052 (p_line_id IN NUMBER
1053 ,x_drop_ship_tbl OUT NOCOPY Drop_Ship_Tbl_Type
1054
1055 ,x_return_status OUT NOCOPY VARCHAR2)
1056
1057 IS
1058 CURSOR eligible_lines_cursor(p_line_id IN NUMBER) IS
1059 SELECT /* MOAC_SQL_CHANGE */ sl.item_type_code, 'STANDARD',
1060 sh.order_number, sl.line_number,
1061 sl.header_id, sl.line_id, sl.ship_from_org_id,
1062 nvl(sl.project_id, -1), nvl(sl.task_id, -1),
1063 nvl(sl.end_item_unit_number,'-1'),fu.user_name,
1064 nvl(fu.employee_id, -99), sl.request_date,
1065 sl.schedule_ship_date,
1066 sl.ordered_quantity,
1067 sl.ordered_quantity2, -- OPM
1068 sl.ordered_quantity_uom2, -- OPM
1069 sl.preferred_grade, -- OPM
1070 sl.inventory_item_id,
1071 sl.source_type_code, decode(msi.inventory_asset_flag,
1072 'Y', mp.material_account, nvl(msi.expense_account,
1073 mp.expense_account)), nvl(pla.location_id, -1)
1074 FROM po_location_associations pla, oe_order_lines_all sl,
1075 mtl_parameters mp, fnd_user fu, mtl_system_items msi,
1076 oe_order_headers sh
1077 WHERE sl.header_id = sh.header_id
1078 AND sl.line_id = p_line_id
1079 AND fu.user_id = sh.created_by
1080 AND sl.source_type_code is not null
1081 AND sl.ship_from_org_id is not null
1082 AND sl.inventory_item_id = msi.inventory_item_id
1083 AND sl.ship_from_org_id = msi.organization_id
1084 AND mp.organization_id = msi.organization_id
1085 AND sl.ship_to_org_id = pla.site_use_id(+)
1086 AND sl.source_type_code = OE_GLOBALS.G_SOURCE_EXTERNAL;
1087
1088 l_drop_ship_line_rec Drop_Ship_Line_Rec_Type;
1089 l_drop_ship_tbl Drop_Ship_Tbl_Type;
1090 l_line_count NUMBER;
1091 l_line_id NUMBER;
1092 l_header_id NUMBER;
1093 l_order_type_name VARCHAR2(240);
1094 l_order_number NUMBER;
1095 l_line_number NUMBER;
1096 l_item_type_code VARCHAR2(30);
1097 l_inventory_item_id NUMBER;
1098 l_open_quantity NUMBER;
1099 l_project_id NUMBER;
1100 l_task_id NUMBER;
1101 l_end_item_unit_number VARCHAR2(30);
1102 l_user_name VARCHAR2(100); -- bug 4189862
1103 l_employee_id NUMBER;
1104 l_request_date DATE;
1105 l_schedule_ship_date DATE;
1106 l_source_type_code VARCHAR2(30);
1107 l_charge_account_id NUMBER;
1108 l_deliver_to_location_id NUMBER;
1109 l_ship_from_org_id NUMBER;
1110 /* OPM variables */
1111 l_open_quantity2 NUMBER;
1112 l_uom2_code VARCHAR2(25);
1113 l_preferred_grade VARCHAR2(150);
1114
1115 --
1116 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
1117 --
1118 BEGIN
1119 IF l_debug_level > 0 THEN
1120 oe_debug_pub.add( 'ENTERING GET_ELIGIBLE_LINES' , 1 ) ;
1121 END IF;
1122 l_line_id := p_line_id;
1123 l_line_count := 0;
1124 OPEN eligible_lines_cursor(p_line_id => l_line_id);
1125 LOOP
1126 FETCH eligible_lines_cursor INTO
1127 l_item_type_code,l_order_type_name, l_order_number, l_line_number,
1128 l_header_id, l_line_id, l_ship_from_org_id,l_project_id, l_task_id,
1129 l_end_item_unit_number,l_user_name, l_employee_id,
1130 l_request_date,l_schedule_ship_date,
1131 l_open_quantity, l_open_quantity2, l_uom2_code, l_preferred_grade,
1132 l_inventory_item_id, l_source_type_code,
1133 l_charge_account_id, l_deliver_to_location_id;
1134 EXIT WHEN eligible_lines_cursor%NOTFOUND;
1135
1136 l_line_count := l_line_count + 1;
1137 l_drop_ship_line_rec.header_id := l_header_id;
1138 l_drop_ship_line_rec.order_type_name := l_order_type_name;
1139 l_drop_ship_line_rec.order_number := l_order_number;
1140 l_drop_ship_line_rec.line_number := l_line_number;
1141 l_drop_ship_line_rec.line_id := l_line_id;
1142 l_drop_ship_line_rec.ship_from_org_id := l_ship_from_org_id;
1143 l_drop_ship_line_rec.item_type_code := l_item_type_code;
1144 l_drop_ship_line_rec.inventory_item_id := l_inventory_item_id;
1145 l_drop_ship_line_rec.open_quantity := l_open_quantity;
1146 l_drop_ship_line_rec.open_quantity2 := l_open_quantity2; -- OPM
1147 l_drop_ship_line_rec.uom2_code := l_uom2_code; -- OPM
1148 l_drop_ship_line_rec.preferred_grade := l_preferred_grade; -- OPM
1149 l_drop_ship_line_rec.project_id := l_project_id;
1150 l_drop_ship_line_rec.task_id := l_task_id;
1151 l_drop_ship_line_rec.end_item_unit_number := l_end_item_unit_number;
1152 l_drop_ship_line_rec.user_name := l_user_name;
1153 l_drop_ship_line_rec.employee_id := l_employee_id;
1154 l_drop_ship_line_rec.request_date := l_request_date;
1155 l_drop_ship_line_rec.schedule_ship_date := l_schedule_ship_date;
1156 l_drop_ship_line_rec.source_type_code := l_source_type_code;
1157 l_drop_ship_line_rec.charge_account_id := l_charge_account_id;
1158 l_drop_ship_line_rec.deliver_to_location_id := l_deliver_to_location_id;
1159
1160 l_drop_ship_tbl(l_line_count) := l_drop_ship_line_rec;
1161 END LOOP;
1162 CLOSE eligible_lines_cursor;
1163 x_drop_ship_tbl := l_drop_ship_tbl;
1164 IF l_debug_level > 0 THEN
1165 oe_debug_pub.add( 'EXITING GET_ELIGIBLE_LINES' , 1 ) ;
1166 END IF;
1167
1168 EXCEPTION
1169
1170 WHEN OTHERS THEN
1171
1172 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1173 THEN
1174 OE_MSG_PUB.Add_Exc_Msg
1175 ( G_PKG_NAME
1176 , 'Get_Eligible_lines'
1177 );
1178 END IF;
1179
1180 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1181
1182 END Get_Eligible_lines;
1183
1184 FUNCTION Get_Shipto_Location_Id
1185 (p_site_use_id IN NUMBER
1186 )
1187 RETURN NUMBER
1188 IS
1189 l_ship_to_location_id NUMBER;
1190 BEGIN
1191
1192 SELECT loc.location_id
1193 INTO l_ship_to_location_id
1194 FROM hz_cust_site_uses_all site_uses,
1195 hz_cust_acct_sites_all acct_site,
1196 hz_party_sites party_site,
1197 hz_locations loc
1198 WHERE site_uses.cust_acct_site_id = acct_site.cust_acct_site_id
1199 AND acct_site.party_site_id = party_site.party_site_id
1200 AND loc.location_id = party_site.location_id
1201 AND site_uses.site_use_code = 'SHIP_TO'
1202 AND site_uses.site_use_id = p_site_use_id;
1203
1204 RETURN l_ship_to_location_id;
1205
1206 EXCEPTION
1207 WHEN NO_DATA_FOUND THEN
1208 RETURN NULL;
1209 END Get_Shipto_Location_Id;
1210
1211
1212 Procedure Process_DropShip_CMS_Requests
1213 (p_request_tbl IN OUT NOCOPY OE_ORDER_PUB.Request_Tbl_Type
1214 ,x_return_status OUT NOCOPY VARCHAR2
1215 )
1216 IS
1217 I NUMBER;
1218 l_return_status VARCHAR2(3);
1219 l_count NUMBER := 1;
1220 l_can_count NUMBER := 1;
1221
1222 l_req_header_id PO_TBL_NUMBER := PO_TBL_NUMBER();
1223 l_req_line_id PO_TBL_NUMBER := PO_TBL_NUMBER();
1224 l_po_header_id PO_TBL_NUMBER := PO_TBL_NUMBER();
1225 l_po_release_id PO_TBL_NUMBER := PO_TBL_NUMBER();
1226 l_po_line_id PO_TBL_NUMBER := PO_TBL_NUMBER();
1227 l_po_line_location_id PO_TBL_NUMBER := PO_TBL_NUMBER();
1228
1229 l_can_req_header_id PO_TBL_NUMBER := PO_TBL_NUMBER();
1230 l_can_req_line_id PO_TBL_NUMBER := PO_TBL_NUMBER();
1231 l_can_po_header_id PO_TBL_NUMBER := PO_TBL_NUMBER();
1232 l_can_po_release_id PO_TBL_NUMBER := PO_TBL_NUMBER();
1233 l_can_po_line_id PO_TBL_NUMBER := PO_TBL_NUMBER();
1234 l_can_po_line_location_id PO_TBL_NUMBER := PO_TBL_NUMBER();
1235
1236 l_quantity PO_TBL_NUMBER := PO_TBL_NUMBER();
1237 l_secondary_quantity PO_TBL_NUMBER := PO_TBL_NUMBER();
1238 l_need_by_date PO_TBL_DATE := PO_TBL_DATE();
1239 l_ship_to_location PO_TBL_NUMBER := PO_TBL_NUMBER();
1240 l_sales_order_updated_date PO_TBL_DATE := PO_TBL_DATE();
1241 l_grade po_tbl_varchar240 := po_tbl_varchar240(); -- INVCONV
1242
1243 l_ds_quantity NUMBER;
1244 l_ds_ordered_quantity_uom VARCHAR2(3);
1245 l_ds_ordered_quantity2 NUMBER;
1246 l_ds_ordered_quantity_uom2 VARCHAR2(3);
1247 l_ds_preferred_grade VARCHAR2(150);
1248 l_ds_schedule_ship_date DATE;
1249
1250 l_ds_old_ship_to_location_id NUMBER;
1251 l_ds_new_ship_to_location_id NUMBER;
1252 l_ds_ship_to_location_id NUMBER;
1253
1254 l_requisition_header_id NUMBER;
1255 l_pur_header_id NUMBER;
1256 l_requisition_line_id NUMBER;
1257 l_pur_line_id NUMBER;
1258 l_pur_release_id NUMBER;
1259 l_line_loc_id NUMBER;
1260 l_drop_ship_id NUMBER;
1261
1262
1263 l_req_created VARCHAR2(1);
1264 l_po_created VARCHAR2(1);
1265 l_changed_flag VARCHAR2(1);
1266 l_msg_data VARCHAR2(2000);
1267 l_error_msg VARCHAR2(2000);
1268 l_msg_count NUMBER;
1269 l_msg_index NUMBER;
1270 --l_po_status VARCHAR2(4100);
1271 l_process_flag VARCHAR2(30);
1272 l_line_num VARCHAR2(30);
1273
1274 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
1275 l_source_code VARCHAR2(30) := FND_PROFILE.Value('ONT_SOURCE_CODE');
1276 --bug 4411054
1277 l_po_status_rec PO_STATUS_REC_TYPE;
1278 l_autorization_status VARCHAR2(30);
1279
1280 BEGIN
1281
1282 IF l_debug_level > 0 THEN
1283 OE_DEBUG_PUB.Add('Entering Process_DropShip_CMS_Requests...', 2);
1284 OE_DEBUG_PUB.Add('No of Records:'|| p_request_tbl.count , 2);
1285 END IF;
1286
1287 I := p_request_tbl.FIRST;
1288
1289 WHILE I IS NOT NULL LOOP
1290
1291 IF p_request_tbl(I).request_type = 'DROPSHIP_CMS' THEN
1292
1293 -- Initialize all the Variables.
1294 l_req_created := 'N';
1295 l_po_created := 'N';
1296 l_changed_flag := 'N';
1297 l_ds_quantity := NULL;
1298 l_ds_ordered_quantity_uom := NULL;
1299 l_ds_ordered_quantity2 := NULL;
1300 l_ds_ordered_quantity_uom2 := NULL;
1301 l_ds_preferred_grade := NULL;
1302 l_ds_schedule_ship_date := NULL;
1303 l_ds_old_ship_to_location_id := NULL;
1304 l_ds_new_ship_to_location_id := NULL;
1305 l_ds_ship_to_location_id := NULL;
1306
1307 OE_DEBUG_PUB.Add('After Initializing Local Variable', 2);
1308
1309 SELECT requisition_header_id,po_header_id,
1310 requisition_line_id,po_line_id,
1311 line_location_id,po_release_id,drop_ship_source_id
1312 INTO l_requisition_header_id,l_pur_header_id,
1313 l_requisition_line_id,l_pur_line_id,
1314 l_line_loc_id,l_pur_release_id,l_drop_ship_id
1315 FROM oe_drop_ship_sources
1316 WHERE line_id = p_request_tbl(I).entity_id;
1317
1318 IF l_requisition_header_id IS NOT NULL THEN
1319 l_req_created := 'Y';
1320 END IF;
1321
1322 IF l_pur_header_id IS NOT NULL THEN
1323 l_po_created := 'Y';
1324 END IF;
1325
1326 IF l_debug_level > 0 THEN
1327 OE_DEBUG_PUB.Add('Line Id:'||p_request_tbl(I).entity_id,2);
1328 OE_DEBUG_PUB.Add('Req Created: '||l_req_created,2);
1329 OE_DEBUG_PUB.Add('PO Created: '||l_po_created,2);
1330 END IF;
1331
1332 IF l_req_created = 'Y' THEN
1333 -- 3579735
1334 oe_debug_pub.add(' Extending req hdr ', 1);
1335 l_req_header_id.extend;
1336 l_req_line_id.extend;
1337 l_po_header_id.extend;
1338 l_po_release_id.extend;
1339 l_po_line_id.extend;
1340 l_po_line_location_id.extend;
1341 l_quantity.extend;
1342 l_secondary_quantity.extend;
1343 l_grade.extend; -- bug 4639066 , missed by INVCONV
1344 l_need_by_date.extend;
1345 l_ship_to_location.extend;
1346 l_sales_order_updated_date.extend;
1347
1348 --l_req_header_id.extend(l_count);
1349 --l_req_line_id.extend(l_count);
1350 --l_po_header_id.extend(l_count);
1351 --l_po_release_id.extend(l_count);
1352 --l_po_line_id.extend(l_count);
1353 --l_po_line_location_id.extend(l_count);
1354 --l_quantity.extend(l_count);
1355 --l_secondary_quantity.extend(l_count);
1356 --l_need_by_date.extend(l_count);
1357 --l_ship_to_location.extend(l_count);
1358 --l_sales_order_updated_date.extend(l_count);
1359
1360 /* Commented for bug 4639066, because EXTEND would add a new NULL element
1361 l_req_header_id(l_count) := NULL;
1362 l_req_line_id(l_count) := NULL;
1363 l_po_header_id(l_count) := NULL;
1364 l_po_release_id(l_count) := NULL;
1365 l_po_line_id(l_count) := NULL;
1366 l_po_line_location_id(l_count) := NULL;
1367 l_quantity(l_count) := NULL;
1368 l_secondary_quantity(l_count) := NULL;
1369 l_grade(l_count) := NULL; -- INVCONV
1370 l_need_by_date(l_count) := NULL;
1371 l_ship_to_location(l_count) := NULL;
1372 l_sales_order_updated_date(l_count) := NULL;
1373 */
1374 END IF;
1375
1376 IF l_debug_level > 0 THEN
1377 OE_DEBUG_PUB.Add('Compare the Old and New CMS Parameters...', 2);
1378 OE_DEBUG_PUB.Add('Operation Performed:'||p_request_tbl(I).param15, 2);
1379 END IF;
1380
1381 IF p_request_tbl(I).param15 = 'UPDATE' THEN
1382
1383 IF NOT OE_GLOBALS.EQUAL(p_request_tbl(I).param1
1384 ,p_request_tbl(I).param8) THEN
1385
1386 IF l_req_created = 'N' THEN
1387 l_ds_quantity := p_request_tbl(I).param8;
1388 ELSE
1389 l_req_header_id(l_count) := l_requisition_header_id;
1390 l_req_line_id(l_count) := l_requisition_line_id;
1391 l_po_header_id(l_count) := l_pur_header_id;
1392 l_po_line_id(l_count) := l_pur_line_id;
1393 l_po_release_id(l_count) := l_pur_release_id;
1394 l_po_line_location_id(l_count) := l_line_loc_id;
1395 l_quantity(l_count) := p_request_tbl(I).param8;
1396 l_changed_flag := 'Y';
1397 END IF;
1398
1399
1400 IF l_debug_level > 0 THEN
1401 OE_DEBUG_PUB.Add('----Ordered Quantity Changed----', 2);
1402 OE_DEBUG_PUB.Add('Old :'||p_request_tbl(I).param1||
1403 ' New :'||p_request_tbl(I).param8,1);
1404 OE_DEBUG_PUB.Add('--------------------------------', 2);
1405 END IF;
1406
1407 END IF;
1408
1409
1410 IF NOT OE_GLOBALS.EQUAL(p_request_tbl(I).param2
1411 ,p_request_tbl(I).param9) THEN
1412 IF l_req_created = 'N' THEN
1413 l_ds_ordered_quantity_uom := p_request_tbl(I).param9;
1414 END IF;
1415
1416 IF l_debug_level > 0 THEN
1417 OE_DEBUG_PUB.Add('----Ordered Quantity UOM Changed----', 2);
1418 OE_DEBUG_PUB.Add('Old:'||p_request_tbl(I).param2||
1419 'New:'||p_request_tbl(I).param9,1);
1420 OE_DEBUG_PUB.Add('--------------------------------', 2);
1421 END IF;
1422
1423
1424 END IF;
1425
1426 IF NOT OE_GLOBALS.EQUAL(p_request_tbl(I).param3
1427 ,p_request_tbl(I).param10) THEN
1428
1429 l_ds_new_ship_to_location_id := Get_Shipto_Location_id(p_request_tbl(I).param10);
1430 l_ds_old_ship_to_location_id := Get_Shipto_Location_id(p_request_tbl(I).param3);
1431
1432 IF NOT OE_GLOBALS.EQUAL(l_ds_new_ship_to_location_id,
1433 l_ds_old_ship_to_location_id) THEN
1434
1435 IF l_req_created = 'N' THEN
1436 l_ds_ship_to_location_id := l_ds_new_ship_to_location_id;
1437 ELSE
1438 l_req_header_id(l_count) := l_requisition_header_id;
1439 l_req_line_id(l_count) := l_requisition_line_id;
1440 l_po_header_id(l_count) := l_pur_header_id;
1441 l_po_line_id(l_count) := l_pur_line_id;
1442 l_po_release_id(l_count) := l_pur_release_id;
1443 l_po_line_location_id(l_count) := l_line_loc_id;
1444 l_ship_to_location(l_count) := l_ds_new_ship_to_location_id;
1445 l_changed_flag := 'Y';
1446
1447 END IF;
1448
1449 END IF;
1450
1451
1452 IF l_debug_level > 0 THEN
1453 OE_DEBUG_PUB.Add('----Ship To Location Changed----', 2);
1454 OE_DEBUG_PUB.Add('Old:'||l_ds_old_ship_to_location_id||
1455 ' New:'||l_ds_new_ship_to_location_id,1);
1456 OE_DEBUG_PUB.Add('--------------------------------', 2);
1457 END IF;
1458
1459 END IF;
1460
1461
1462
1463 IF NOT OE_GLOBALS.EQUAL(p_request_tbl(I).param4
1464 ,p_request_tbl(I).param11) THEN
1465
1466 IF l_req_created = 'N' THEN
1467 l_ds_ordered_quantity2 := p_request_tbl(I).param11;
1468 ELSE
1469 l_req_header_id(l_count) := l_requisition_header_id;
1470 l_req_line_id(l_count) := l_requisition_line_id;
1471 l_po_header_id(l_count) := l_pur_header_id;
1472 l_po_line_id(l_count) := l_pur_line_id;
1473 l_po_release_id(l_count) := l_pur_release_id;
1474 l_po_line_location_id(l_count) := l_line_loc_id;
1475 l_secondary_quantity(l_count) := p_request_tbl(I).param11;
1476 l_changed_flag := 'Y';
1477 END IF;
1478
1479
1480 IF l_debug_level > 0 THEN
1481 OE_DEBUG_PUB.Add('----Secondary Quantity Changed----', 2);
1482 OE_DEBUG_PUB.Add('Old:'||p_request_tbl(I).param4||
1483 ' New:'||p_request_tbl(I).param11,1);
1484 OE_DEBUG_PUB.Add('--------------------------------', 2);
1485 END IF;
1486
1487 END IF;
1488
1489
1490 IF NOT OE_GLOBALS.EQUAL(p_request_tbl(I).param5
1491 ,p_request_tbl(I).param12) THEN
1492
1493 IF l_req_created = 'N' THEN
1494 l_ds_ordered_quantity_uom2 := p_request_tbl(I).param12;
1495 END IF;
1496
1497 IF l_debug_level > 0 THEN
1498 OE_DEBUG_PUB.Add('----Secondary Quantity UOM Changed----', 2);
1499 OE_DEBUG_PUB.Add('Old:'||p_request_tbl(I).param5||
1500 'New:'||p_request_tbl(I).param12,1);
1501 OE_DEBUG_PUB.Add('--------------------------------------', 2);
1502 END IF;
1503
1504 END IF;
1505
1506 IF NOT OE_GLOBALS.EQUAL(p_request_tbl(I).param6
1507 ,p_request_tbl(I).param13) THEN
1508
1509 IF l_req_created = 'N' THEN
1510 l_ds_preferred_grade := p_request_tbl(I).param13;
1511 END IF;
1512
1513 IF l_debug_level > 0 THEN
1514 OE_DEBUG_PUB.Add('----Preferred Grade Changed----', 2);
1515 OE_DEBUG_PUB.Add('Old:'||p_request_tbl(I).param6||
1516 'New:'||p_request_tbl(I).param13,1);
1517 OE_DEBUG_PUB.Add('--------------------------------', 2);
1518 END IF;
1519
1520
1521 END IF;
1522
1523
1524 /*****Begin changes for bug#6918700*********/
1525
1526 -- IF NOT OE_GLOBALS.EQUAL(p_request_tbl(I).param7 ,p_request_tbl(I).param14) THEN
1527
1528 IF NOT OE_GLOBALS.EQUAL(p_request_tbl(I).date_param1 ,p_request_tbl(I).date_param2) THEN
1529 /*****End changes for bug#6918700*********/
1530
1531 IF l_req_created = 'N' THEN
1532 /*****Begin changes for bug#6918700*********/
1533 -- l_ds_schedule_ship_date := p_request_tbl(I).param14;
1534 l_ds_schedule_ship_date := p_request_tbl(I).date_param2;
1535 /*****End changes for bug#6918700*********/
1536 ELSE
1537 l_req_header_id(l_count) := l_requisition_header_id;
1538 l_req_line_id(l_count) := l_requisition_line_id;
1539 l_po_header_id(l_count) := l_pur_header_id;
1540 l_po_line_id(l_count) := l_pur_line_id;
1541 l_po_release_id(l_count) := l_pur_release_id;
1542 l_po_line_location_id(l_count) := l_line_loc_id;
1543 /*****Begin changes for bug#6918700*********/
1544 -- l_need_by_date(l_count) := p_request_tbl(I).param14;
1545 l_need_by_date(l_count) := p_request_tbl(I).date_param2;
1546 /*****End changes for bug#6918700*********/
1547 l_changed_flag := 'Y';
1548
1549 END IF;
1550
1551 IF l_debug_level > 0 THEN
1552 OE_DEBUG_PUB.Add('----Schedule Ship Date Changed----', 2);
1553 /*****Begin changes for bug#6918700*********/
1554 /* OE_DEBUG_PUB.Add('Old:'||p_request_tbl(I).param7||
1555 'New:'||p_request_tbl(I).param14,1);
1556 */
1557 OE_DEBUG_PUB.Add('Old:'||p_request_tbl(I).date_param1||
1558 'New:'||p_request_tbl(I).date_param2,1);
1559 /*****End changes for bug#6918700*********/
1560 OE_DEBUG_PUB.Add('--------------------------------', 2);
1561 END IF;
1562
1563 END IF;
1564
1565
1566 IF p_request_tbl(I).param16 = 'Y' AND
1567 l_req_created = 'Y' and l_po_created = 'Y' THEN
1568
1569 -- comment out for bug 4411054
1570 /*l_po_status := UPPER(PO_HEADERS_SV3.Get_PO_Status
1571 (x_po_header_id => l_pur_header_id
1572 ));
1573
1574 IF l_debug_level > 0 THEN
1575 OE_DEBUG_PUB.Add('Check PO Status : '|| l_po_status, 2);
1576 END IF;
1577 */
1578 PO_DOCUMENT_CHECKS_GRP.PO_STATUS_CHECK
1579 (p_api_version => 1.0
1580 , p_header_id => l_pur_header_id
1581 , p_release_id => l_pur_release_id --bug 5328526
1582 , p_mode => 'GET_STATUS'
1583 , x_po_status_rec => l_po_status_rec
1584 , x_return_status => l_return_status);
1585
1586 IF(l_return_status = FND_API.G_RET_STS_SUCCESS) THEN
1587 l_autorization_status := l_po_status_rec.authorization_status(1);
1588
1589 IF l_debug_level > 0 THEN
1590 OE_DEBUG_PUB.Add('Sucess call from PO_DOCUMENT_CHECKS_GRP.po_status_check',2);
1591 OE_DEBUG_PUB.Add('Check PO Status : '|| l_autorization_status, 2);
1592 END IF;
1593 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1594 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1595 ELSIF l_return_status = FND_API.G_RET_STS_ERROR THEN
1596 RAISE FND_API.G_EXC_ERROR;
1597 END IF;
1598
1599 --IF (INSTR(nvl(l_po_status,'z'), 'APPROVED') <> 0 ) THEN
1600 IF(nvl(l_autorization_status,'z')= 'APPROVED') THEN
1601 l_req_header_id(l_count) := l_requisition_header_id;
1602 l_req_line_id(l_count) := l_requisition_line_id;
1603 l_po_header_id(l_count) := l_pur_header_id;
1604 l_po_line_id(l_count) := l_pur_line_id;
1605 l_po_release_id(l_count) := l_pur_release_id;
1606 l_po_line_location_id(l_count) := l_line_loc_id;
1607 l_sales_order_updated_date(l_count) := sysdate;
1608 l_changed_flag := 'Y';
1609
1610 IF l_debug_level > 0 THEN
1611 OE_DEBUG_PUB.Add('----Ref Data Elem Changed----', 2);
1612 OE_DEBUG_PUB.Add('Req:'||l_req_created||'PO:'||l_po_created||
1613 'Release:'||l_pur_release_id, 2);
1614 OE_DEBUG_PUB.Add('--------------------------------', 2);
1615 END IF;
1616
1617 END IF;
1618
1619 END IF;
1620
1621
1622
1623 IF l_req_created = 'N' THEN
1624
1625 -- Lock the Interface record
1626
1627 SELECT process_flag
1628 INTO l_process_flag
1629 FROM po_requisitions_interface_all
1630 WHERE interface_source_line_id = l_drop_ship_id
1631 AND interface_source_code = l_source_code;
1632
1633 IF l_debug_level > 0 THEN
1634 OE_DEBUG_PUB.Add('After Querying: '||l_drop_ship_id, 2);
1635 END IF;
1636
1637 SELECT RTRIM(line_number || '.' ||
1638 shipment_number || '.' ||
1639 option_number || '.' ||
1640 component_number || '.' ||
1641 service_number, '.')
1642 INTO l_line_num
1643 FROM oe_order_lines_all
1644 WHERE line_id = p_request_tbl(I).entity_id;
1645
1646 IF l_process_flag is NULL THEN
1647
1648 -- Update the Interface Record
1649 -- Analyzed for SQL performance ID 14882834, No changes required
1650 -- as we do not have any more where condition criteria
1651 UPDATE po_requisitions_interface_all
1652 SET quantity = nvl(l_ds_quantity,quantity),
1653 uom_code = nvl(l_ds_ordered_quantity_uom,
1654 uom_code),
1655 secondary_quantity = nvl(l_ds_ordered_quantity2,
1656 secondary_quantity),
1657 secondary_uom_code = nvl(l_ds_ordered_quantity_uom2,
1658 secondary_uom_code),
1659 preferred_grade = nvl(l_ds_preferred_grade,
1660 preferred_grade),
1661 need_by_date = nvl(l_ds_schedule_ship_date,
1662 need_by_date),
1663 deliver_to_location_id = nvl(l_ds_ship_to_location_id,
1664 deliver_to_location_id)
1665 WHERE interface_source_line_id = l_drop_ship_id
1666 AND interface_source_code = l_source_code;
1667
1668 IF l_debug_level > 0 THEN
1669 OE_DEBUG_PUB.Add('After Updating PO_Requisitions_Interface_All..', 2);
1670 END IF;
1671
1672 ELSIF l_process_flag = 'ERROR' THEN
1673 FND_MESSAGE.SET_NAME('ONT','ONT_DS_LINE_IN_ERROR');
1674 FND_MESSAGE.SET_TOKEN('LINE_NUM',l_line_num);
1675 OE_MSG_PUB.Add;
1676 ELSIF l_process_flag is NOT NULL THEN
1677 FND_MESSAGE.SET_NAME('ONT','ONT_DS_LINE_IN_PROCESS');
1678 FND_MESSAGE.SET_TOKEN('LINE_NUM',l_line_num);
1679 OE_MSG_PUB.Add;
1680 RAISE FND_API.G_EXC_ERROR;
1681 END IF;
1682
1683 END IF;
1684
1685 IF l_changed_flag = 'Y' THEN
1686 l_count := l_count + 1;
1687 END IF;
1688
1689
1690 END IF; -- Update
1691
1692 IF p_request_tbl(I).param15 = 'CANCEL' THEN
1693
1694 oe_debug_pub.add('1 extending can req hdr ', 1);
1695 -- 3579735
1696 --l_can_req_header_id.extend(l_can_count);
1697 --l_can_req_line_id.extend(l_can_count);
1698 --l_can_po_header_id.extend(l_can_count);
1699 --l_can_po_release_id.extend(l_can_count);
1700 --l_can_po_line_id.extend(l_can_count);
1701 --l_can_po_line_location_id.extend(l_can_count);
1702
1703 l_can_req_header_id.extend;
1704 l_can_req_line_id.extend;
1705 l_can_po_header_id.extend;
1706 l_can_po_release_id.extend;
1707 l_can_po_line_id.extend;
1708 l_can_po_line_location_id.extend;
1709
1710 IF l_req_created = 'N' THEN
1711
1712 BEGIN
1713 SELECT process_flag
1714 INTO l_process_flag
1715 FROM po_requisitions_interface_all
1716 WHERE interface_source_line_id = l_drop_ship_id
1717 AND interface_source_code = l_source_code ;
1718 EXCEPTION
1719 WHEN NO_DATA_FOUND THEN
1720 -- #5262032, no data found when the req interface records deleted
1721 IF l_debug_level > 0 THEN
1722 OE_DEBUG_PUB.add('No po req interface records for this line',1);
1723 END IF;
1724 END;
1725
1726
1727 IF l_debug_level > 0 THEN
1728 OE_DEBUG_PUB.Add('After Querying,dropship_id: '||l_drop_ship_id||' ,process_flag:'||l_process_flag, 2);
1729 END IF;
1730
1731 Begin
1732 SELECT RTRIM(line_number || '.' ||
1733 shipment_number || '.' ||
1734 option_number || '.' ||
1735 component_number || '.' ||
1736 service_number, '.')
1737 INTO l_line_num
1738 FROM oe_order_lines_all
1739 WHERE line_id = p_request_tbl(I).entity_id;
1740 Exception
1741 When Others Then
1742 /* Bug 4922019: Changes done to ATO configuration resutls in
1743 deletion of the config line. Hence the above select would fail.
1744 If line has been deleted, then its OK to have the line number NULL */
1745 Null;
1746 End;
1747
1748 IF l_process_flag is NULL THEN
1749 -- Analysed for SQL Performance ID 14882874, No changes required
1750 -- as we do not have any more where condition criteria
1751 DELETE FROM po_requisitions_interface_all
1752 WHERE interface_source_line_id = l_drop_ship_id
1753 AND interface_source_code = l_source_code;
1754
1755 IF l_debug_level > 0 THEN
1756 OE_DEBUG_PUB.Add('After Deleting: '||l_drop_ship_id, 2);
1757 END IF;
1758
1759 ELSIF l_process_flag = 'ERROR' THEN
1760 FND_MESSAGE.SET_NAME('ONT','ONT_DS_LINE_IN_ERROR');
1761 FND_MESSAGE.SET_TOKEN('LINE_NUM',l_line_num);
1762 OE_MSG_PUB.Add;
1763 ELSIF l_process_flag is NOT NULL THEN
1764 FND_MESSAGE.SET_NAME('ONT','ONT_DS_LINE_IN_PROCESS');
1765 FND_MESSAGE.SET_TOKEN('LINE_NUM',l_line_num);
1766 OE_MSG_PUB.Add;
1767 RAISE FND_API.G_EXC_ERROR;
1768 END IF;
1769
1770 ELSE
1771 oe_debug_pub.add('assigning values to cancel records', 1);
1772 l_can_req_header_id(l_can_count) := l_requisition_header_id;
1773 l_can_req_line_id(l_can_count) := l_requisition_line_id;
1774 l_can_po_header_id(l_can_count) := l_pur_header_id;
1775 l_can_po_line_id(l_can_count) := l_pur_line_id;
1776 l_can_po_release_id(l_can_count) := l_pur_release_id;
1777 l_can_po_line_location_id(l_can_count) := l_line_loc_id;
1778 l_can_count := l_can_count + 1;
1779 END IF;
1780
1781
1782 END IF;
1783
1784 ELSE
1785 oe_debug_pub.add('not a dropship request ', 1);
1786 END IF; -- Dropship CMS
1787
1788
1789 I := p_request_tbl.NEXT(I);
1790
1791 END LOOP;
1792
1793 IF l_count > 1 THEN
1794
1795 IF l_debug_level > 0 THEN
1796 OE_DEBUG_PUB.Add('Before Calling Update_Req_PO...',2) ;
1797 OE_DEBUG_PUB.Add('No of Req Records PO: '||to_char(l_count -1),2) ;
1798 END IF;
1799 --
1800
1801 PO_OM_INTEGRATION_GRP.Update_Req_PO
1802 (p_api_version => 1.0
1803 ,p_req_header_id => l_req_header_id
1804 ,p_req_line_id => l_req_line_id
1805 ,p_po_header_id => l_po_header_id
1806 ,p_po_release_id => l_po_release_id
1807 ,p_po_line_id => l_po_line_id
1808 ,p_po_line_location_id => l_po_line_location_id
1809 ,p_quantity => l_quantity
1810 ,p_secondary_quantity => l_secondary_quantity
1811 ,p_need_by_date => l_need_by_date
1812 ,p_ship_to_location_id => l_ship_to_location
1813 ,p_sales_order_update_date => l_sales_order_updated_date
1814 ,p_preferred_grade => l_grade -- INVCONV
1815 ,x_return_status => l_return_status
1816 ,x_msg_count => l_msg_count
1817 ,x_msg_data => l_msg_data
1818 );
1819
1820 IF l_debug_level > 0 THEN
1821 OE_DEBUG_PUB.Add(' After Calling update_req_po...'||l_return_status,2) ;
1822 OE_DEBUG_PUB.Add(' Message Count:'||l_msg_count,2) ;
1823 OE_DEBUG_PUB.Add(' Message Data:'||l_msg_data,2) ;
1824 END IF;
1825
1826 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1827 OE_MSG_PUB.Transfer_Msg_Stack;
1828 l_msg_count:=OE_MSG_PUB.COUNT_MSG;
1829 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1830 ELSIF l_return_status = FND_API.G_RET_STS_ERROR THEN
1831
1832 OE_MSG_PUB.Transfer_Msg_Stack;
1833 l_msg_count:=OE_MSG_PUB.COUNT_MSG;
1834 FOR I in 1..l_msg_count LOOP
1835 l_msg_data := OE_MSG_PUB.Get(I,'F');
1836 IF l_debug_level > 0 THEN
1837 oe_debug_pub.add('Messages from Update PO;'||l_msg_data,1 ) ;
1838 END IF;
1839 END LOOP;
1840
1841 RAISE FND_API.G_EXC_ERROR;
1842 END IF;
1843
1844 END IF;
1845
1846 IF l_can_count > 1 THEN
1847
1848 IF l_debug_level > 0 THEN
1849 OE_DEBUG_PUB.Add('Before Calling Cancel_Req_PO...',2) ;
1850 OE_DEBUG_PUB.Add
1851 ('No of Records for PO Cancellation...'||l_can_count ,2) ;
1852 END IF;
1853
1854 FOR I in l_can_req_header_id.FIRST..l_can_req_header_id.LAST
1855 LOOP
1856 oe_debug_pub.add('req hdr '|| l_can_req_header_id(I), 1);
1857
1858 -- added for bug 3946163
1859 -- The p_po_line_id shall be passed as NULL for all the lines that are
1860 -- attached to a blanket purchase agreement
1861 IF l_can_po_release_id(I) is NOT NULL THEN
1862 l_can_po_line_id(I) := NULL;
1863 END IF;
1864 END LOOP;
1865
1866 PO_OM_INTEGRATION_GRP.Cancel_Req_PO
1867 (p_api_version => 1.0
1868 ,p_req_header_id => l_can_req_header_id
1869 ,p_req_line_id => l_can_req_line_id
1870 ,p_po_header_id => l_can_po_header_id
1871 ,p_po_release_id => l_can_po_release_id
1872 ,p_po_line_id => l_can_po_line_id
1873 ,p_po_line_location_id => l_can_po_line_location_id
1874 ,x_return_status => l_return_status
1875 ,x_msg_data => l_msg_data
1876 ,x_msg_count => l_msg_count
1877 );
1878
1879
1880 IF l_debug_level > 0 THEN
1881 OE_DEBUG_PUB.Add('After Calling Cancel_Req_PO: '||l_return_status,2) ;
1882 OE_DEBUG_PUB.Add(' Message Count:'||l_msg_count,2) ;
1883 OE_DEBUG_PUB.Add(' Message Data:'||l_msg_data,2) ;
1884 END IF;
1885
1886 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1887 OE_MSG_PUB.Transfer_Msg_Stack;
1888 l_msg_count:=OE_MSG_PUB.COUNT_MSG;
1889 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1890 ELSIF l_return_status = FND_API.G_RET_STS_ERROR THEN
1891
1892 OE_MSG_PUB.Transfer_Msg_Stack;
1893 l_msg_count:=OE_MSG_PUB.COUNT_MSG;
1894 FOR I in 1..l_msg_count LOOP
1895 l_msg_data := OE_MSG_PUB.Get(I,'F');
1896 IF l_debug_level > 0 THEN
1897 oe_debug_pub.add('Messages from Cancel PO;'||l_msg_data,1 ) ;
1898 END IF;
1899 END LOOP;
1900
1901 IF l_debug_level > 0 THEN
1902 OE_DEBUG_PUB.Add('Errors from Cancel_req_po: '||l_msg_data,2) ;
1903 END IF;
1904
1905 -- bug 5358405
1906 FND_MESSAGE.set_name('ONT','OE_CANCEL_FAILED');
1907 OE_MSG_PUB.Add;
1908
1909 RAISE FND_API.G_EXC_ERROR;
1910 END IF;
1911
1912 END IF;
1913
1914
1915 -- Error during updates, mark the requests as NOT processed
1916
1917 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1918
1919 I := p_request_tbl.FIRST;
1920 WHILE I IS NOT NULL LOOP
1921 IF p_request_tbl(I).request_type = 'DROPSHIP_CMS' THEN
1922 p_request_tbl(I).processed := 'N';
1923 END IF;
1924 I := p_request_tbl.NEXT(I);
1925 END LOOP;
1926 END IF;
1927
1928 IF l_debug_level > 0 then
1929 OE_DEBUG_PUB.Add('Exiting Process_DropShip_CMS_Requests..:'||x_return_status);
1930 END IF;
1931
1932 x_return_status := l_return_status;
1933
1934 EXCEPTION
1935 WHEN FND_API.G_EXC_ERROR THEN
1936 IF l_debug_level > 0 THEN
1937 OE_DEBUG_PUB.Add('Expected Error in Process_DropShip_CMS_Requests...',4);
1938 END IF;
1939
1940 x_return_status := FND_API.G_RET_STS_ERROR;
1941 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1942 IF l_debug_level > 0 THEN
1943 OE_DEBUG_PUB.Add('UnExpected Error in Process_DropShip_CMS_Requests...'||sqlerrm,4);
1944 END IF;
1945
1946 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1947 WHEN OTHERS THEN
1948 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1949
1950 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1951 THEN
1952 OE_MSG_PUB.Add_Exc_Msg
1953 (G_PKG_NAME
1954 ,'Process_DropShip_CMS_Requests'
1955 );
1956 END IF;
1957
1958 END Process_DropShip_CMS_Requests;
1959
1960 END OE_Purchase_Release_PVT;