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