1 Package Body OE_DROP_SHIP_GRP As
2 /* $Header: OEXUDSHB.pls 120.4 2011/12/07 11:22:32 lnammina ship $ */
3 --
4 -- The following are global variables.
5 --
6 --
7 G_PKG_NAME CONSTANT VARCHAR2(30) := 'OE_DROP_SHIP_GRP';
8 --
9 G_OE_SOURCE_CODE Varchar2(25);
10 G_DROP_SHIP_SOURCE_ID Number;
11 G_HEADER_ID Number;
12 G_LINE_ID Number;
13 G_ORG_ID Number;
14 G_DESTINATION_ORGANIZATION_ID Number;
15 G_REQUISITION_HEADER_ID Number;
16 G_REQUISITION_LINE_ID Number;
17 G_PO_HEADER_ID Number;
18 G_PO_LINE_ID Number;
19
20 /*-----------------------------------------------------------------
21 FUNCTION:Valid_Drop_Ship_Source_ID
22 DESCRIPTION: Function to validate the existence of a drop ship source
23 id. When PO calls this call back, a drop ship source id has
24 to exist. There can be duplicates on this source id if PO
25 splits a requisition or a PO.
26 -----------------------------------------------------------------*/
27 Function Valid_Drop_Ship_Source_ID
28 (P_Drop_Ship_Source_ID In Number)
29 Return Boolean
30 IS
31 Cursor L_Drop_Ship_Source_ID_Csr Is
32 Select Count(*)
33 From OE_DROP_SHIP_SOURCES
34 Where Drop_Ship_Source_ID = P_Drop_Ship_Source_ID ;
35 --
36 L_Drop_Ship_Source_Count Number(4);
37
38 --
39 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
40 --
41 Begin
42
43 Open L_Drop_Ship_Source_ID_Csr ;
44 Fetch L_Drop_Ship_Source_ID_Csr Into L_Drop_Ship_Source_Count ;
45 IF L_Drop_Ship_Source_Count > 0 Then
46 Return True;
47 ELSE
48 Return False;
49 End If;
50
51 END Valid_Drop_Ship_Source_ID;
52
53 /*-----------------------------------------------------------------
54 PROCEDURE : Insert_OE_Drop_Ship_Source
55 DESCRIPTION:
56 -----------------------------------------------------------------*/
57
58 PROCEDURE Insert_OE_Drop_Ship_Sources
59 (P_Drop_Ship_Source_ID In Number,
60 P_Header_ID In Number,
61 P_Line_ID In Number,
62 P_Org_ID In Number,
63 P_Destination_Organization_ID In Number,
64 P_Requisition_Header_ID In Number,
65 P_Requisition_Line_ID In Number,
66 P_PO_Header_ID In Number,
67 P_PO_Line_ID In Number,
68 P_Line_Location_ID In Number, P_PO_Release_ID In Number Default Null)
69 IS
70 --
71 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
72 --
73 BEGIN
74
75 Insert Into OE_Drop_Ship_Sources
76 (
77 Drop_Ship_Source_ID,
78 Header_ID,
79 Line_ID,
80 Org_ID,
81 Destination_Organization_ID,
82 Requisition_Header_ID,
83 Requisition_Line_ID,
84 PO_Header_ID,
85 PO_Line_ID,
86 Line_Location_ID,
87 PO_Release_ID,
88 Creation_Date,
89 Created_By,
90 Last_Update_Date,
91 Last_Updated_By
92 )
93 Values
94 (
95 P_Drop_Ship_Source_ID,
96 P_Header_ID,
97 P_Line_ID,
98 P_Org_ID,
99 P_Destination_Organization_ID,
100 P_Requisition_Header_ID,
101 P_Requisition_Line_ID,
102 P_PO_Header_ID,
103 P_PO_Line_ID,
104 P_Line_Location_ID,
105 P_PO_Release_ID,
106 Trunc(Sysdate),
107 Nvl(To_Number(FND_PROFILE.VALUE('USER_ID')),-1),
108 Trunc(Sysdate),
109 Nvl(To_Number(FND_PROFILE.VALUE('USER_ID')),-1)
110 );
111
112 End Insert_OE_Drop_Ship_Sources;
113
114 /*-----------------------------------------------------------------
115 PROCEDURE : Update_Req_Info
116 DESCRIPTION: Update_Req_Info is an OE procedure that is called by
117 Oracle Purchasing to update requisition information for
118 a drop shipped line. This procedure is called in the
119 Requisition Import (ReqImport) process of Oracle Purchasing
120 -----------------------------------------------------------------*/
121
122 PROCEDURE Update_Req_Info
123 (P_API_Version In Number,
124 P_Return_Status out nocopy Varchar2,
125
126 P_Msg_Count out nocopy Number,
127
128 P_MSG_Data out nocopy Varchar2,
129
130 P_Interface_Source_Code In Varchar2,
131 P_Interface_Source_Line_ID In Number,
132 P_Requisition_Header_ID In Number,
133 P_Requisition_Line_ID In Number)
134 IS
135
136 L_API_Name Constant Varchar2(30) := 'UPDATE_REQ_INFO';
137 L_API_Version Constant Number := 1.0;
138 L_SQLCODE Number;
139 L_SQLERRM Varchar2(2000);
140
141 Cursor L_OE_Drop_Ship_Source_CSR (P_Interface_Source_Line_ID In Number)Is
142 SELECT Drop_Ship_Source_ID,
143 Header_ID,
144 Line_ID,
145 Org_ID,
146 Destination_Organization_ID,
147 Requisition_Header_ID,
148 Requisition_Line_ID,
149 PO_Header_ID,
150 PO_Line_ID
151 FROM OE_Drop_Ship_Sources
152 WHERE Drop_Ship_source_ID = P_Interface_Source_Line_ID
153 FOR UPDATE OF Requisition_Header_ID NOWAIT; -- bug 4503620
154
155 --
156 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
157 --
158 BEGIN
159
160 SavePoint Update_Req_Info_GRP;
161
162 IF Not FND_API.Compatible_API_Call
163 (L_API_Version,
164 P_API_Version,
165 L_API_Name,
166 G_PKG_Name)
167 THEN
168 Raise FND_API.G_EXC_UNEXPECTED_ERROR;
169 END IF;
170
171 -- Returns OE's profile value for SO_SOURCE_CODE
172 FND_PROFILE.Get('ONT_SOURCE_CODE',G_OE_SOURCE_CODE);
173
174 IF P_Interface_Source_Code = G_OE_Source_Code THEN
175 Null;
176 ELSE -- Interface source code is not an OE interface source code
177 P_Return_Status := FND_API.G_RET_STS_SUCCESS;
178 Return;
179 END IF;
180
181 IF Valid_Drop_Ship_Source_Id(P_INTERFACE_SOURCE_LINE_ID) Then
182 Null;
183 ELSE -- The drop ship source ID does not exist. Serious Error!
184 Rollback to Update_Req_INfo_GRP;
185 P_Return_Status := FND_API.G_RET_STS_ERROR;
186 Return;
187 End If;
188 --
189
190 OPEN L_OE_Drop_Ship_Source_Csr(P_INTERFACE_SOURCE_LINE_ID);
191 FETCH L_OE_Drop_Ship_Source_Csr
192 INTO G_DROP_SHIP_SOURCE_ID,
193 G_HEADER_ID,
194 G_LINE_ID,
195 G_ORG_ID,
196 G_DESTINATION_ORGANIZATION_ID,
197 G_REQUISITION_HEADER_ID,
198 G_REQUISITION_LINE_ID,
199 G_PO_HEADER_ID,
200 G_PO_LINE_ID;
201
202 IF L_OE_Drop_Ship_Source_Csr%Found THEN
203 IF G_Requisition_Header_ID Is Null THEN
204 -- Requisition being updated for the first time
205
206 UPDATE OE_Drop_Ship_Sources
207 SET Requisition_Header_ID = P_Requisition_Header_ID,
208 Requisition_LIne_ID = P_Requisition_Line_ID,
209 Last_Update_Date = Trunc(Sysdate),
210 Last_Updated_By = Nvl(To_Number(FND_PROFILE.VALUE
211 ('USER_ID')),-1)
212 WHERE Current of L_OE_Drop_Ship_Source_Csr;
213
214 ELSE
215 -- Another requisition for same order line.
216 Insert_OE_Drop_Ship_Sources
217 (P_Drop_Ship_Source_ID => G_Drop_Ship_Source_ID,
218 P_Header_ID => G_Header_ID,
219 P_Line_ID => G_Line_ID,
220 P_Org_ID => G_Org_ID,
221 P_Destination_Organization_ID =>
222 G_Destination_Organization_ID,
223 P_Requisition_Header_ID =>
224 P_Requisition_Header_ID,
225 P_Requisition_LIne_ID => P_Requisition_Line_ID,
226 P_PO_Header_ID => Null,
227 P_PO_Line_ID => Null,
228 P_Line_Location_ID => Null);
229 END IF;
230 END IF;
231 CLOSE L_OE_Drop_Ship_Source_Csr;
232 p_return_status := FND_API.G_RET_STS_SUCCESS;
233 EXCEPTION
234 WHEN APP_EXCEPTIONS.RECORD_LOCK_EXCEPTION THEN -- bug 4503620
235 Rollback to Update_Req_Info_Grp;
236 IF l_debug_level > 0 THEN
237 oe_debug_pub.add('OEXUDSHB.pls:Update_Req_Info- unable to lock the line',1);
238 END IF;
239 fnd_message.set_name('ONT', 'OE_LINE_LOCKED');
240 -- msg not added in OE as the msg ctx is not set
241 P_Msg_Data := FND_MESSAGE.GET;
242 P_Msg_Count := 1;
243 p_return_status := FND_API.G_RET_STS_ERROR;
244
245 WHEN OTHERS THEN
246 Rollback to Update_Req_Info_Grp;
247 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
248 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
249 THEN
250 OE_MSG_PUB.Add_Exc_Msg(G_PKG_Name,L_API_Name);
251 END IF;
252 OE_MSG_PUB.Count_And_Get (P_Count => P_MSG_Count,
253 P_Data => P_MSG_Data);
254 END Update_Req_Info;
255
256
257 /*-----------------------------------------------------------------
258 PROCEDURE : Update_All_Reqs_In_Process
259 DESCRIPTION: Update_All_Reqs_In_Process is an OE procedure that is
260 called by Oracle Purchasing to update requisition
261 information for a drop shipped line. This procedure is
262 called in the Requisition Import (ReqImport) process of
263 Oracle Purchasing.
264 -----------------------------------------------------------------*/
265
266 PROCEDURE Update_All_Reqs_In_Process
267 (P_API_Version In Number,
268 P_Return_Status out nocopy Varchar2,
269
270 P_Msg_Count out nocopy Number,
271
272 P_MSG_Data out nocopy Varchar2,
273
274 P_Requisition_Header_ID In Number,
275 P_Request_Id In Number,
276 P_Process_Flag In Varchar2)
277 IS
278 L_API_Name Constant Varchar2(30)
279 := 'UPDATE_ALL_REQS_IN_PROCESS';
280 L_API_Version Constant Number := 1.0;
281 L_Interface_Source_Code Varchar2(25);
282 L_Interface_Source_Line_Id Number;
283 L_Requisition_Header_Id Number;
284 L_Requisition_Line_Id Number;
285 L_Return_Status Varchar2(1);
286 L_Msg_Count Number;
287 L_MSG_Data Varchar2(1000);
288
289 CURSOR L_PO_Req_Interface_CSR Is
290 SELECT Interface_Source_Code,Interface_source_line_id,
291 requisition_header_id,requisition_line_id
292 FROM po_requisitions_interface
293 WHERE requisition_header_id = P_Requisition_Header_ID
294 AND process_flag = P_Process_Flag
295 AND request_id = P_Request_Id;
296 --
297 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
298 --
299 BEGIN
300
301 SAVEPOINT UPDATE_ALL_REQS_IN_PROCESS;
302
303 IF Not FND_API.Compatible_API_Call (L_API_Version,
304 P_API_Version,
305 L_API_Name,
306 G_PKG_Name)
307 THEN
308 Raise FND_API.G_EXC_UNEXPECTED_ERROR;
309 END IF;
310
311 OPEN L_PO_Req_Interface_CSR;
312 LOOP
313 FETCH L_PO_Req_Interface_CSR Into L_Interface_Source_Code,
314 L_Interface_Source_Line_Id,
315 L_Requisition_Header_Id,
316 L_Requisition_Line_Id;
317 IF L_PO_Req_Interface_CSR%NotFound THEN
318 p_return_status := FND_API.G_RET_STS_SUCCESS;
319 EXIT;
320 END IF;
321
322 Update_Req_Info(1.0,
323 L_Return_Status,
324 L_Msg_Count,
325 L_MSG_Data,
326 L_Interface_Source_Code,
327 L_Interface_Source_Line_ID,
328 L_Requisition_Header_ID,
329 L_Requisition_Line_ID);
330
331 p_return_status := l_Return_Status ;
332
333 IF l_return_Status = FND_API.G_RET_STS_SUCCESS THEN
334 Null;
335 ELSE
336 CLOSE L_PO_Req_Interface_CSR;
337 EXIT;
338 END IF;
339
340 END LOOP;
341
342 CLOSE L_PO_Req_Interface_CSR;
343
344 EXCEPTION
345
346 WHEN OTHERS THEN
347 ROLLBACK TO UPDATE_ALL_REQS_IN_PROCESS;
348 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
349 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
350 THEN
351 OE_MSG_PUB.Add_Exc_Msg(G_PKG_Name,L_API_Name);
352 END IF;
353 OE_MSG_PUB.Count_And_Get (P_Count => P_MSG_Count,
354 P_Data => P_MSG_Data);
355 END Update_All_Reqs_In_Process;
356
357 /*-----------------------------------------------------------------
358 PROCEDURE : Update_PO_Info
359 DESCRIPTION: Update_PO_Info is an OE procedure that is called by
360 Oracle Purchasing to update purchase order information
361 for a drop shipped line. This procedure is called in the
362 Auto create process of Oracle Purchasing
363 -----------------------------------------------------------------*/
364
365 PROCEDURE Update_PO_Info (P_API_Version In Number,
366 P_Return_Status out nocopy Varchar2,
367
368 P_Msg_Count out nocopy Number,
369
370 P_MSG_Data out nocopy Varchar2,
371
372 P_Req_Header_ID In Number,
373 P_Req_Line_ID In Number,
374 P_PO_Header_Id In Number,
375 P_PO_Line_Id In Number,
376 P_Line_Location_ID In Number,
377 P_PO_Release_ID In Number Default Null)
378 IS
379
380 L_API_Name Constant Varchar2(30) := 'UPDATE_PO_INFO';
381 L_API_Version Constant Number := 1.0;
382
383 Cursor L_OE_Drop_Ship_Source_CSR (P_Req_Line_ID In Number,
384 P_Req_Header_Id In Number)
385 IS SELECT Drop_Ship_Source_ID,
386 Header_ID,
387 Line_ID,
388 Org_ID,
389 Destination_Organization_ID,
390 Requisition_Header_ID,
391 Requisition_Line_ID,
392 PO_Header_ID,
393 PO_Line_ID
394 FROM OE_Drop_Ship_Sources
395 WHERE Requisition_Line_ID = P_Req_Line_ID
396 AND Requisition_Header_ID = P_Req_Header_ID
397 FOR Update of PO_Header_ID NOWAIT; -- bug 4503620
398
399 --
400 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
401 --
402 BEGIN
403
404 SavePoint Update_PO_Info_GRP;
405 IF Not FND_API.Compatible_API_Call (L_API_Version,
406 P_API_Version,
407 L_API_Name,
408 G_PKG_Name)
409 THEN
410 Raise FND_API.G_EXC_UNEXPECTED_ERROR;
411 END IF;
412 --
413 OPEN L_OE_Drop_Ship_SOurce_Csr(P_REQ_LINE_ID,P_REQ_HEADER_ID);
414 FETCH L_OE_Drop_Ship_Source_Csr Into G_Drop_Ship_Source_ID,
415 G_Header_ID,
416 G_Line_ID,
417 G_Org_ID,
418 G_Destination_ORganization_ID,
419 G_Requisition_Header_ID,
420 G_Requisition_Line_ID,
421 G_PO_Header_ID,
422 G_PO_Line_ID;
423 IF L_OE_Drop_Ship_Source_CSR%Found THEN
424 -- P_Req_Line_Id Is a drop ship line id
425 IF G_Requisition_Header_ID IS NOT NULL
426 And G_PO_Header_ID IS NULL THEN
427 -- PO being updated for the first time
428
429 Update OE_Drop_Ship_Sources
430 SET PO_Header_ID = P_PO_Header_ID,
431 PO_Line_ID = P_PO_Line_ID,
432 Line_Location_ID = P_Line_Location_ID,
433 PO_Release_ID = Nvl(P_PO_Release_Id,PO_Release_ID),
434 Last_Update_Date = Trunc(Sysdate),
435 Last_Updated_By = Nvl(To_Number(FND_PROFILE.VALUE('USER_ID')),-1)
436 WHERE Current of L_OE_Drop_Ship_Source_Csr;
437 ELSE -- Another PO for same order line.
438 Insert_OE_Drop_Ship_Sources
439 (P_Drop_Ship_Source_ID => G_Drop_Ship_Source_ID,
440 P_Header_ID => G_Header_ID,
441 P_Line_ID => G_Line_ID,
442 P_Org_ID => G_Org_ID,
443 P_Destination_Organization_ID => G_Destination_Organization_ID,
444 P_Requisition_Header_ID => G_Requisition_Header_ID,
445 P_Requisition_Line_ID => G_Requisition_Line_ID,
446 P_PO_Header_ID => P_PO_Header_ID,
447 P_PO_Line_ID => P_PO_Line_ID,
448 P_Line_Location_ID => P_Line_Location_ID,
449 P_PO_Release_Id => P_PO_Release_ID);
450 END IF;
451 END IF;
452 CLOSE L_OE_Drop_Ship_Source_Csr;
453 P_Return_Status := FND_API.G_RET_STS_SUCCESS;
454
455 EXCEPTION
456 WHEN APP_EXCEPTIONS.RECORD_LOCK_EXCEPTION THEN -- bug 4503620
457 Rollback to Update_PO_Info_GRP;
458 IF l_debug_level > 0 THEN
459 oe_debug_pub.add('OEXUDSHB.pls:Update_PO_Info-unable to lock the line',1);
460 END IF;
461 fnd_message.set_name('ONT', 'OE_LINE_LOCKED');
462 -- msg not added in OE as the msg ctx is not set
463 P_Msg_Data := FND_MESSAGE.GET;
464 P_Msg_Count := 1;
465 p_return_status := FND_API.G_RET_STS_ERROR;
466
467 WHEN OTHERS THEN
468 ROLLBACK TO Update_PO_Info_GRP;
469 P_Return_Status := FND_API.G_RET_STS_UNEXP_ERROR;
470 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
471 THEN
472 OE_MSG_PUB.Add_Exc_Msg(G_PKG_Name,L_API_Name);
473 END IF;
474 OE_MSG_PUB.Count_And_Get (P_Count => P_MSG_Count,
475 P_Data => P_MSG_Data);
476 END Update_PO_Info;
477 --
478
479 /*-----------------------------------------------------------------
480 PROCEDURE : Req_Line_Is_Drop_Ship
481 DESCRIPTION:
482 -----------------------------------------------------------------*/
483 Function Req_Line_Is_Drop_Ship(P_Req_Line_Id In Number)
484 RETURN NUMBER
485 IS
486
487 L_Line_id Number;
488 CURSOR L_Drop_Ship_Csr IS
489 SELECT Line_Id
490 FROM OE_Drop_Ship_Sources
491 WHERE Requisition_Line_ID = P_Req_Line_ID;
492
493 --
494 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
495 --
496 BEGIN
497 OPEN L_Drop_Ship_Csr;
498 FETCH L_Drop_Ship_Csr Into L_Line_Id;
499
500 IF L_Drop_Ship_Csr%NotFound THEN
501 -- Req Line Id is not a "drop Ship" Req
502 CLOSE L_Drop_Ship_Csr;
503 RETURN NULL;
504 ELSE
505 -- Req Line Id is a "drop Ship" Req
506 CLOSE L_Drop_Ship_Csr;
507 RETURN L_Line_Id;
508 END IF;
509 END Req_Line_Is_Drop_Ship;
510
511 --
512 /*-----------------------------------------------------------------
513 PROCEDURE : PO_Line_Location_Is_Drop_Ship
514 DESCRIPTION:
515 -----------------------------------------------------------------*/
516 Function PO_Line_Location_Is_Drop_Ship(P_PO_Line_Location_Id In Number)
517 RETURN Number
518 IS
519 L_Line_Id Number;
520 Cursor L_Drop_Ship_Csr Is
521 SELECT Line_Id
522 FROM OE_Drop_Ship_Sources
523 WHERE Line_Location_Id = P_PO_Line_Location_ID;
524 --
525 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
526 --
527 BEGIN
528 OPEN L_Drop_Ship_Csr;
529
530 FETCH L_Drop_Ship_Csr Into L_Line_Id;
531
532 IF L_Drop_Ship_Csr%NotFound THEN
533 -- PO Line Location Id is not a "drop Ship" Line location
534 CLOSE L_Drop_Ship_Csr;
535 RETURN Null;
536 ELSE
537 -- PO Line Location Id is a "drop Ship" Line Location
538 CLOSE L_Drop_Ship_Csr;
539 RETURN L_Line_Id;
540 END IF;
541
542 END PO_Line_Location_Is_Drop_Ship;
543
544 /*--------------------------------------------------
545 Procedure : Update_Drop_Ship_Links
546 Description : This procedure will be called by PO
547 in the case of supplier initiated PO cancellation.
548 This will update line in oe dropship sources
549 with new requistion id.
550 ----------------------------------------------------*/
551
552 Procedure Update_Drop_Ship_Links
553 ( p_api_version IN NUMBER
554 ,p_po_header_id IN NUMBER
555 ,p_po_line_id IN NUMBER
556 ,p_po_line_location_id IN NUMBER
557 ,p_po_release_id IN NUMBER
558 ,p_new_req_hdr_id IN NUMBER
559 ,p_new_req_line_id IN NUMBER
560 ,x_msg_data OUT NOCOPY VARCHAR2
561 ,x_msg_count OUT NOCOPY NUMBER
562 ,x_return_status OUT NOCOPY VARCHAR2
563 )
564 IS
565 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
566 l_line_id NUMBER;
567 l_header_id NUMBER;
568 l_org_id NUMBER;
569 l_num_lines NUMBER;
570
571 BEGIN
572
573 x_return_status := FND_API.G_RET_STS_SUCCESS;
574
575 SAVEPOINT Update_Drop_Ship_Links_GRP;
576
577 IF l_debug_level > 0 THEN
578 OE_DEBUG_PUB.Add('Entering Update_Drop_Ship_Links...',1);
579 END IF;
580
581 Get_Drop_Ship_Line_ids
582 ( p_po_header_id => p_po_header_id
583 ,p_po_line_id => p_po_line_id
584 ,p_po_line_location_id => p_po_line_location_id
585 ,p_po_release_id => p_po_release_id
586 ,x_num_lines => l_num_lines
587 ,x_line_id => l_line_id
588 ,x_header_id => l_header_id
589 ,x_org_id => l_org_id
590 );
591
592 IF p_new_req_hdr_id IS NOT NULL THEN
593
594 UPDATE oe_drop_ship_sources
595 SET requisition_header_id = p_new_req_hdr_id,
596 requisition_line_id = p_new_req_line_id,
597 po_header_id = NULL,
598 po_line_id = NULL,
599 po_release_id = NULL,
600 line_location_id = NULL,
601 last_update_date = Trunc(Sysdate),
602 last_updated_by = NVL(To_Number(FND_PROFILE.VALUE('USER_ID')),-1),
603 last_update_login = NVL(To_Number(FND_PROFILE.VALUE('USER_ID')),-1)
604 WHERE line_id = l_line_id
605 AND header_id = l_header_id;
606
607 IF l_debug_level > 0 THEN
608 OE_DEBUG_PUB.Add('Updated the Drop Ship Links...',1);
609 END IF;
610
611 END IF;
612
613 IF l_debug_level > 0 THEN
614 OE_DEBUG_PUB.Add('Exiting Update_Drop_Ship_Links...',1);
615 END IF;
616
617 EXCEPTION
618
619 WHEN NO_DATA_FOUND THEN
620
621 IF l_debug_level > 0 THEN
622 OE_DEBUG_PUB.Add('No DAta Found in Update_Drop_Ship_Links...',1);
623 END IF;
624
625 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
626
627 ROLLBACK TO Update_Drop_Ship_Links;
628
629
630 WHEN OTHERS THEN
631
632 ROLLBACK TO Update_Drop_Ship_Links;
633
634 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
635
636 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
637 THEN
638 OE_MSG_PUB.Add_Exc_Msg(
639 G_PKG_Name,
640 'Update_Drop_Ship_Links');
641 END IF;
642
643 OE_MSG_PUB.Count_And_Get (p_Count => x_msg_count,
644 p_Data => x_msg_data);
645 END Update_Drop_Ship_Links;
646
647 /*--------------------------------------------------
648 Function : Is_Receipt_For_Drop_Ship
649 Description : This Procedure will called by Inventory.
650 This will check whether the receipt is for a dropship
651 order or not.
652 ---------------------------------------------------*/
653
654 Function Is_Receipt_For_Drop_Ship
655 ( p_rcv_transaction_id IN NUMBER
656 )RETURN BOOLEAN
657 IS
658 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
659 l_line_id NUMBER;
660 BEGIN
661
662 IF l_debug_level > 0 THEN
663 OE_DEBUG_PUB.Add('Entering Is_Receipt_For_Drop_Ship...'||
664 p_rcv_transaction_id,1);
665 END IF;
666
667 SELECT ol.line_id
668 INTO l_line_id
669 FROM oe_order_lines_all ol,
670 oe_drop_ship_sources od,
671 rcv_transactions rt
672 WHERE ol.line_id = od.line_id
673 AND ol.source_type_code = 'EXTERNAL'
674 AND od.po_header_id = rt.po_header_id
675 AND od.po_line_id = rt.po_line_id
676 AND od.line_location_id = rt.po_line_location_id
677 AND rt.transaction_id = p_rcv_transaction_id
678 AND ROWNUM = 1;
679
680 RETURN TRUE;
681
682 IF l_debug_level > 0 THEN
683 OE_DEBUG_PUB.Add('Exiting Is_Receipt_For_Drop_Ship...',1);
684 END IF;
685
686 EXCEPTION
687 WHEN NO_DATA_FOUND THEN
688 IF l_debug_level > 0 THEN
689 OE_DEBUG_PUB.Add('No Data Found in Is_Receipt_For_Drop_Ship...',1);
690 END IF;
691 RETURN FALSE;
692 END Is_Receipt_For_Drop_Ship;
693
694 /*-----------------------------------------------------
695 Procedure : Get_Drop_Ship_Line_ids
696 Description : This Procedure will be called from PO and
697 iSupplier and will return header id,line id and release id for
698 a given po or req. As Line Location id is unique according
699 to PO the po header join conditions are removed.
700
701 Added p_mode to address 3210977 and 3251580.
702
703 p_mode :
704 null : The api returns open unshipped order
705 line associated with this po shipment.
706 1 : The api returns open unshipped order
707 line associated with this po shipment.
708 If there are no unshipped lines, and there
709 is only one closed line, it will return that line.
710 If there are more than one shipped lines, it will return
711 -99 in the x_line_id parameter.x_header_id, x_org_id
712 will have correct value.
713 2 : This means the requirement is to send the line
714 associated with the p_rcv_transaction_id parameter.
715 Refer bug 3251580.
716 -------------------------------------------------------*/
717 Procedure Get_Drop_Ship_Line_ids
718 ( p_po_header_id IN NUMBER
719 , p_po_line_id IN NUMBER
720 , p_po_line_location_id IN NUMBER
721 , p_po_release_id IN NUMBER
722 , p_mode IN NUMBER := null
723 , p_rcv_transaction_id IN NUMBER := null
724 , x_num_lines OUT NOCOPY /* file.sql.39 change */ NUMBER
725 , x_line_id OUT NOCOPY NUMBER
726 , x_header_id OUT NOCOPY NUMBER
727 , x_org_id OUT NOCOPY NUMBER
728 )
729
730 IS
731 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
732 BEGIN
733
734 IF l_debug_level > 0 THEN
735 OE_DEBUG_PUB.Add('Entering Get_Drop_Ship_Line_ids...' || p_mode,1);
736 END IF;
737
738 -- Check for Open Lines and Not Shipped Yet Lines
739
740 IF p_mode is NULL OR
741 p_mode = 1 THEN
742
743 BEGIN
744 SELECT oel.line_id,
745 oel.header_id,
746 oel.org_id
747 INTO x_line_id,
748 x_header_id,
749 x_org_id
750 FROM oe_drop_ship_sources ds,
751 oe_order_lines_all oel
752 WHERE line_location_id = p_po_line_location_id
753 AND oel.line_id = ds.line_id
754 AND oel.header_id = ds.header_id
755 AND nvl(oel.open_flag,'Y') = 'Y'
756 AND oel.shipped_quantity is NULL;
757
758 EXCEPTION
759
760 WHEN NO_DATA_FOUND THEN
761
762 IF l_debug_level > 0 THEN
763 OE_DEBUG_PUB.Add('no open order line', 5);
764 END IF;
765
766 IF p_mode is NULL THEN
767
768 IF l_debug_level > 0 THEN
769 OE_DEBUG_PUB.Add('No Data Found first sql', 2);
770 END IF;
771
772 x_line_id := NULL;
773 x_header_id := NULL;
774 x_org_id := NULL;
775
776 ELSE
777
778 BEGIN
779 SELECT oel.line_id,
780 oel.header_id,
781 oel.org_id
782 INTO x_line_id,
783 x_header_id,
784 x_org_id
785 FROM oe_drop_ship_sources ds,
786 oe_order_lines_all oel
787 WHERE line_location_id = p_po_line_location_id
788 AND oel.line_id = ds.line_id
789 AND oel.header_id = ds.header_id;
790
791 IF l_debug_level > 0 THEN
792 OE_DEBUG_PUB.Add('line found now '|| x_line_id, 2);
793 END IF;
794
795 EXCEPTION
796
797 WHEN TOO_MANY_ROWS THEN
798
799 IF l_debug_level > 0 THEN
800 OE_DEBUG_PUB.Add('multiple receipts ', 2);
801 END IF;
802 x_line_id := -99;
803
804 SELECT oel.header_id,
805 oel.org_id
806 INTO x_header_id,
807 x_org_id
808 FROM oe_drop_ship_sources ds,
809 oe_order_lines_all oel
810 WHERE line_location_id = p_po_line_location_id
811 AND oel.line_id = ds.line_id
812 AND oel.header_id = ds.header_id
813 AND rownum = 1;
814
815 WHEN OTHERS THEN
816 IF l_debug_level > 0 THEN
817 OE_DEBUG_PUB.Add('sql error * '|| sqlerrm, 2);
818 END IF;
819 RAISE;
820 END;
821
822 END IF; -- if p_mode is null within no_data_found
823 END;
824
825 ELSIF p_mode = 2 THEN
826
827 IF l_debug_level > 0 THEN
828 OE_DEBUG_PUB.Add('rcv txn id '|| p_rcv_transaction_id, 2);
829 END IF;
830
831 SELECT MAX(OL.line_id)
832 INTO x_line_id
833 FROM oe_order_lines_all OL,
834 oe_drop_ship_sources OD,
835 rcv_transactions RT
836 WHERE OL.line_id = OD.line_id
837 AND OL.source_type_code = 'EXTERNAL'
838 AND OD.po_header_id = RT.po_header_id
839 AND OD.po_line_id = RT.po_line_id
840 AND OD.line_location_id = RT.po_line_location_id
841 AND RT.transaction_id = p_rcv_transaction_id
842 AND OL.shipped_quantity is NOT NULL;
843
844 x_header_id := NULL;
845 x_org_id := NULL;
846
847 END IF; -- if p_mode
848
849 SELECT count(*)
850 INTO x_num_lines
851 FROM oe_drop_ship_sources ds,
852 oe_order_lines_all oel
853 WHERE line_location_id = p_po_line_location_id
854 AND oel.line_id = ds.line_id
855 AND oel.header_id = ds.header_id;
856
857 IF l_debug_level > 0 THEN
858 OE_DEBUG_PUB.Add('Exiting Get_Drop_Ship_Line_ids...'||x_num_lines,1);
859 END IF;
860
861 EXCEPTION
862
863 WHEN NO_DATA_FOUND THEN
864 IF l_debug_level > 0 THEN
865 OE_DEBUG_PUB.Add('No Data Found in Get_Drop_Ship_Line_ids', 2);
866 END IF;
867 x_num_lines := NULL;
868 x_line_id := NULL;
869 x_header_id := NULL;
870 x_org_id := NULL;
871
872 WHEN TOO_MANY_ROWS THEN
873 IF l_debug_level > 0 THEN
874 OE_DEBUG_PUB.Add('Too Many Rows in Get_Drop_Ship_Line_ids', 2);
875 END IF;
876
877 RAISE;
878
879 WHEN OTHERS THEN
880
881 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
882 THEN
883 OE_MSG_PUB.Add_Exc_Msg
884 ( G_PKG_NAME,
885 'Get_Drop_Ship_Line_ids');
886 END IF;
887
888 RAISE;
889
890 END Get_Drop_Ship_Line_ids;
891
892
893
894 /*-----------------------------------------------------
895 Procedure : Get_Order_Line_Status
896 Description :
897 This procedure will be used by PO to validate supplier
898 initiated changes to PO from iSupplier portal.
899 Enter PO form to validate changes to PO attributes
900 e.g. need by date
901 PO receiving API at the time of receipt + delivery.
902 OM receiving API at the time of drop ship receipt
903 to fail receipt when order line is fulfilled.
904 ------------------------------------------------------*/
905
906 Procedure Get_Order_Line_Status
907 (p_api_version IN NUMBER
908 ,p_po_header_id IN NUMBER
909 ,p_po_line_id IN NUMBER
910 ,p_po_line_location_id IN NUMBER
911 ,p_po_release_id IN NUMBER
912 ,p_mode IN NUMBER
913 ,x_updatable_flag OUT NOCOPY VARCHAR2
914 ,x_on_hold OUT NOCOPY VARCHAR2
915 ,x_order_line_status OUT NOCOPY NUMBER
916 ,x_msg_data OUT NOCOPY VARCHAR2
917 ,x_msg_count OUT NOCOPY NUMBER
918 ,x_return_status OUT NOCOPY VARCHAR2
919 )
920 IS
921 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
922 l_line_id NUMBER;
923 l_header_id NUMBER;
924 l_org_id NUMBER;
925 l_num_lines NUMBER;
926 BEGIN
927
928 x_return_status := FND_API.G_RET_STS_SUCCESS;
929
930 IF l_debug_level > 0 THEN
931 OE_DEBUG_PUB.Add('Entering Get_Order_Line_Status...',1);
932 END IF;
933
934 Get_Drop_Ship_Line_ids
935 ( p_po_header_id => p_po_header_id
936 ,p_po_line_id => p_po_line_id
937 ,p_po_line_location_id => p_po_line_location_id
938 ,p_po_release_id => p_po_release_id
939 ,x_num_lines => l_num_lines
940 ,x_line_id => l_line_id
941 ,x_header_id => l_header_id
942 ,x_org_id => l_org_id
943 );
944
945 IF p_mode = 0 THEN
946
947 IF l_line_id is NOT NULL THEN
948
949 x_updatable_flag := 'Y';
950
951 ELSE
952
953 x_updatable_flag := 'N';
954
955 END IF;
956
957 END IF;
958
959 x_on_hold := NULL;
960 x_order_line_status := NULL;
961
962 IF l_debug_level > 0 THEN
963 OE_DEBUG_PUB.Add('Exiting Get_Order_Line_Status...',1);
964 END IF;
965
966 EXCEPTION
967
968 WHEN NO_DATA_FOUND THEN
969
970 IF l_debug_level > 0 THEN
971 OE_DEBUG_PUB.Add('No DAta Found in Update_Drop_Ship_Links...',1);
972 END IF;
973
974 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
975
976 ROLLBACK TO Update_Drop_Ship_Links;
977
978 WHEN OTHERS THEN
979
980 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
981 THEN
982 OE_MSG_PUB.Add_Exc_Msg
983 ( G_PKG_NAME,
984 'Get_Order_Line_Status');
985 END IF;
986
987 END Get_Order_Line_Status;
988
989 /*-----------------------------------------------------
990 Procedure : Get_Order_Line_Info
991 Description :
992 This procedure will be used by PO to get addition
993 so that they can obtain additionla information about
994 sales order line. This info will be used such taht,
995
996 buyers can see the Sales Order Line details in the new
997 Drop Ship tab that will be added to the Shipments
998 Block of both the Enter PO Form and Enter Releases Form.
999
1000
1001 All the data elements necessary for the fulfillment
1002 of a Drop Ship Line will be sent to the end Suppliers.
1003
1004
1005 -------------------------------------------------------*/
1006
1007 PROCEDURE Get_Order_Line_Info
1008 ( p_api_version IN NUMBER
1009 ,p_po_header_id IN NUMBER
1010 ,p_po_line_id IN NUMBER
1011 ,p_po_line_location_id IN NUMBER
1012 ,p_po_release_id IN NUMBER
1013 ,p_mode IN NUMBER
1014 ,x_order_line_info_rec OUT NOCOPY Order_Line_Info_Rec_Type
1015 ,x_msg_data OUT NOCOPY VARCHAR2
1016 ,x_msg_count OUT NOCOPY NUMBER
1017 ,x_return_status OUT NOCOPY VARCHAR2)
1018 IS
1019 l_line_id NUMBER;
1020 l_header_id NUMBER;
1021 l_org_id NUMBER;
1022 l_num_lines NUMBER;
1023 l_ship_to_contact_id NUMBER;
1024 l_deliver_to_contact_id NUMBER;
1025 l_ship_to_org_id NUMBER;
1026 l_deliver_to_org_id NUMBER;
1027 l_sold_to_org_id NUMBER;
1028 l_inventory_item_id NUMBER;
1029 l_ordered_item_id NUMBER;
1030 l_item_identifier_type VARCHAR2(30);-- bug 4148163
1031 l_ordered_item VARCHAR2(2000);
1032 l_orig_user_id NUMBER;
1033 l_orig_resp_id NUMBER;
1034 l_orig_resp_appl_id NUMBER;
1035 l_address1 VARCHAR2(240); -- bug 4148163
1036 l_address2 VARCHAR2(240);
1037 l_address3 VARCHAR2(240); -- bug 4148163
1038 l_address4 VARCHAR2(240); -- bug 4148163
1039 l_city VARCHAR2(60); -- bug 4148163
1040 l_state VARCHAR2(60); -- bug 4148163
1041 l_zip VARCHAR2(60); -- bug 4148163
1042 l_country VARCHAR2(60); -- bug 4148163
1043 l_shipping_method_code VARCHAR2(30);
1044 --l_reset_context NUMBER := 0; no more required after MOAC
1045 l_customer_number VARCHAR2(30); -- bug 4148163
1046 l_customer_id NUMBER; -- bug 4148163
1047 l_po_org_id NUMBER; -- bug 4148163
1048 l_temp1 VARCHAR2(2000); -- bug 4148163
1049 l_temp2 VARCHAR2(2000); -- bug 4148163
1050 -- MOAC
1051 l_access_mode VARCHAR2(1);
1052 l_current_org_id NUMBER;
1053 l_reset_policy BOOLEAN;
1054 --
1055 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
1056 --
1057 BEGIN
1058 IF l_debug_level > 0 THEN
1059 OE_DEBUG_PUB.Add('Entering Get_Order_Line_Info '||p_mode,1);
1060 END IF;
1061
1062 x_return_status := FND_API.G_RET_STS_SUCCESS;
1063 l_access_mode := mo_global.Get_access_mode; -- MOAC
1064 l_current_org_id := mo_global.get_current_org_id();
1065
1066 Get_Drop_Ship_Line_ids
1067 ( p_po_header_id => p_po_header_id
1068 ,p_po_line_id => p_po_line_id
1069 ,p_po_line_location_id => p_po_line_location_id
1070 ,p_po_release_id => p_po_release_id
1071 ,p_mode => 1
1072 ,x_num_lines => l_num_lines
1073 ,x_line_id => l_line_id
1074 ,x_header_id => l_header_id
1075 ,x_org_id => l_org_id);
1076
1077 IF l_debug_level > 0 THEN
1078 OE_DEBUG_PUB.Add('line_id '|| l_line_id || 'om org ' || l_org_id,3);
1079 END IF;
1080
1081 IF l_line_id is NULL THEN
1082 IF l_debug_level > 0 THEN
1083 OE_DEBUG_PUB.Add
1084 ('no line for this po_location_id '|| p_po_line_location_id,3);
1085 END IF;
1086 RAISE FND_API.G_EXC_ERROR;
1087 END IF;
1088
1089 SELECT org_id
1090 INTO l_po_org_id -- l_num_lines 4148163
1091 FROM po_line_locations_all
1092 WHERE line_location_id = p_po_line_location_id;
1093
1094 IF l_debug_level > 0 THEN
1095 OE_DEBUG_PUB.Add(' po org '|| l_po_org_id, 5);
1096 END IF;
1097
1098 IF l_po_org_id <> l_org_id THEN
1099 -- MOAC
1100 Mo_Global.Set_Policy_Context (p_access_mode =>'S', p_org_id => l_org_id);
1101 l_reset_policy := TRUE;
1102 ELSE
1103 IF nvl(l_current_org_id,-99) <> l_org_id THEN
1104 Mo_Global.Set_Policy_Context (p_access_mode => 'S', p_org_id => l_org_id);
1105 l_reset_policy := TRUE;
1106 END IF;
1107 END IF;
1108
1109 /* Commented for MOAC R12 project
1110 OE_ORDER_CONTEXT_GRP.Set_Created_By_Context
1111 ( p_header_id => NULL
1112 ,p_line_id => l_line_id
1113 ,x_orig_user_id => l_orig_user_id
1114 ,x_orig_resp_id => l_orig_resp_id
1115 ,x_orig_resp_appl_id => l_orig_resp_appl_id
1116 ,x_return_status => x_return_status
1117 ,x_msg_count => x_msg_count
1118 ,x_msg_data => x_msg_data );
1119
1120 IF l_debug_level > 0 THEN
1121 OE_DEBUG_PUB.Add('context switched ' || x_return_status,3);
1122 END IF;
1123
1124 IF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1125 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1126 ELSIF x_return_status = FND_API.G_RET_STS_ERROR THEN
1127 RAISE FND_API.G_EXC_ERROR;
1128 END IF;
1129 END IF;
1130
1131 IF NVL(to_number(FND_GLOBAL.USER_ID),-1) = -1 OR
1132 NVL(to_number(FND_GLOBAL.RESP_ID),-1) = -1 OR
1133 NVL(to_number(FND_GLOBAL.RESP_APPL_ID),-1) = -1 THEN
1134
1135 OE_ORDER_CONTEXT_GRP.Set_Created_By_Context
1136 ( p_header_id => NULL
1137 ,p_line_id => l_line_id
1138 ,x_orig_user_id => l_orig_user_id
1139 ,x_orig_resp_id => l_orig_resp_id
1140 ,x_orig_resp_appl_id => l_orig_resp_appl_id
1141 ,x_return_status => x_return_status
1142 ,x_msg_count => x_msg_count
1143 ,x_msg_data => x_msg_data );
1144
1145 IF l_debug_level > 0 THEN
1146 OE_DEBUG_PUB.Add('context switched for no context' || x_return_status,3);
1147 END IF;
1148
1149 IF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1150 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1151 ELSIF x_return_status = FND_API.G_RET_STS_ERROR THEN
1152 RAISE FND_API.G_EXC_ERROR;
1153 END IF;
1154
1155 l_reset_context := 1;
1156
1157 END IF; */
1158
1159 SELECT order_number
1160 INTO x_order_line_info_rec.sales_order_number
1161 FROM oe_order_headers_all --oe_order_headers --Changes for BUG#6032405
1162 WHERE header_id = l_header_id;
1163
1164 IF l_line_id = -99 THEN
1165
1166 IF l_debug_level > 0 THEN
1167 OE_DEBUG_PUB.Add
1168 ('order number '|| x_order_line_info_rec.sales_order_number,3);
1169 END IF;
1170 RETURN;
1171
1172 ELSE
1173
1174 SELECT ship_to_contact_id,
1175 deliver_to_contact_id,
1176 ship_to_org_id,
1177 deliver_to_org_id,
1178 sold_to_org_id,
1179 shipping_method_code,
1180 shipping_instructions,
1181 packing_instructions,
1182 inventory_item_id,
1183 item_identifier_type,
1184 ordered_item,
1185 ordered_item_id, -- Bug# 13100856
1186 user_item_description,
1187 cust_po_number,
1188 customer_line_number,
1189 customer_shipment_number,
1190 DECODE(p_mode, 0, null, RTRIM(line_number || '.'
1191 || shipment_number || '.'
1192 || option_number || '.'
1193 || component_number || '.'
1194 || service_number, '.')),
1195 DECODE(p_mode, 0, null, ordered_quantity),
1196 DECODE(p_mode, 0, null, shipped_quantity),
1197 DECODE(p_mode, 0, null, ordered_quantity2), -- INVCONV
1198 DECODE(p_mode, 0, null, shipped_quantity2), -- INVCONV
1199 DECODE(p_mode, 0, null, oelup.meaning)
1200 INTO l_ship_to_contact_id,
1201 l_deliver_to_contact_id,
1202 l_ship_to_org_id,
1203 l_deliver_to_org_id,
1204 l_sold_to_org_id,
1205 l_shipping_method_code,
1206 x_order_line_info_rec.shipping_instructions,
1207 x_order_line_info_rec.packing_instructions,
1208 l_inventory_item_id,
1209 l_item_identifier_type,
1210 l_ordered_item,
1211 l_ordered_item_id, -- Bug# 13100856
1212 x_order_line_info_rec.customer_product_description,
1213 x_order_line_info_rec.customer_po_number,
1214 x_order_line_info_rec.customer_po_line_number,
1215 x_order_line_info_rec.customer_po_shipment_number,
1216 x_order_line_info_rec.sales_order_line_number,
1217 x_order_line_info_rec.sales_order_line_ordered_qty,
1218 x_order_line_info_rec.sales_order_line_shipped_qty,
1219 x_order_line_info_rec.sales_order_line_ordered_qty2, -- INVCONV
1220 x_order_line_info_rec.sales_order_line_shipped_qty2, -- INVCONV
1221
1222 x_order_line_info_rec.sales_order_line_status
1223 FROM oe_order_lines_all oel, --oe_order_lines oel, -- Changes for BUG#6032405
1224 oe_lookups oelup
1225 WHERE line_id = l_line_id
1226 AND oelup.lookup_code = oel.flow_status_code
1227 AND oelup.lookup_type = 'LINE_FLOW_STATUS';
1228
1229 IF l_debug_level > 0 THEN
1230 oe_debug_pub.add('from oe_order_lines '
1231 || x_order_line_info_rec.customer_product_description
1232 || l_shipping_method_code, 3);
1233 END IF;
1234
1235 IF p_mode = 0 THEN -- do we need this??
1236 x_order_line_info_rec.sales_order_number := null;
1237 END IF;
1238
1239 END IF;
1240
1241 IF l_ship_to_org_id is NOT NULL THEN
1242 OE_Header_Util.Get_Customer_Details
1243 ( p_org_id => l_ship_to_org_id
1244 ,p_site_use_code => 'SHIP_TO'
1245 ,x_customer_name => x_order_line_info_rec.ship_to_customer_name
1246 ,x_customer_number => l_customer_number -- bug 4148163
1247 ,x_customer_id => l_customer_id -- bug 4148163
1248 ,x_location => x_order_line_info_rec.ship_to_customer_location
1249 ,x_address1 => l_address1
1250 ,x_address2 => l_address2
1251 ,x_address3 => l_address3
1252 ,x_address4 => l_address4
1253 ,x_city => l_city
1254 ,x_state => l_state
1255 ,x_zip => l_zip
1256 ,x_country => l_country);
1257
1258 IF l_debug_level > 0 THEN
1259 OE_DEBUG_PUB.Add
1260 ('got ship to ct name and location: '
1261 || x_order_line_info_rec.ship_to_customer_name || '-'
1262 || x_order_line_info_rec.ship_to_customer_location,5);
1263 END IF;
1264 END IF;
1265
1266 IF l_ship_to_contact_id is NOT NULL THEN
1267 x_order_line_info_rec.ship_to_contact_name :=
1268 OE_Id_To_Value.Ship_To_Contact
1269 (p_ship_to_contact_id => l_ship_to_contact_id);
1270 END IF;
1271
1272
1273 IF p_mode = 1 OR
1274 p_mode = 2 THEN
1275 IF l_debug_level > 0 THEN
1276 OE_DEBUG_PUB.Add
1277 ('sales order info: '
1278 || x_order_line_info_rec.sales_order_line_number || '-'
1279 || x_order_line_info_rec.sales_order_line_ordered_qty || '-'
1280 || x_order_line_info_rec.sales_order_line_shipped_qty || '-'
1281 || x_order_line_info_rec.sales_order_line_status || '-'
1282 || x_order_line_info_rec.sales_order_number,5);
1283 END IF;
1284 END IF;
1285
1286 IF p_mode = 0 OR
1287 p_mode = 2 THEN
1288
1289 IF l_ship_to_contact_id is NOT NULL THEN
1290
1291 OE_Id_To_Value.Get_Contact_Details
1292 ( p_contact_id => l_ship_to_contact_id
1293 ,x_contact_name => l_temp1 -- l_address1 bug 4148163
1294 ,x_phone_line_type => x_order_line_info_rec.ship_to_contact_fax
1295 ,x_phone_number => x_order_line_info_rec.ship_to_contact_phone
1296 ,x_email_address => x_order_line_info_rec.ship_to_contact_email);
1297
1298 IF x_order_line_info_rec.ship_to_contact_fax = 'FAX' THEN
1299 x_order_line_info_rec.ship_to_contact_fax
1300 := x_order_line_info_rec.ship_to_contact_phone;
1301 x_order_line_info_rec.ship_to_contact_phone := null;
1302 END IF;
1303
1304 IF l_debug_level > 0 THEN
1305 OE_DEBUG_PUB.Add
1306 ('got ship to contact details: '
1307 || x_order_line_info_rec.ship_to_contact_name || '-'
1308 || x_order_line_info_rec.ship_to_contact_fax || '-'
1309 || x_order_line_info_rec.ship_to_contact_phone || '-'
1310 || x_order_line_info_rec.ship_to_contact_email,5);
1311 END IF;
1312 END IF;
1313
1314 IF l_deliver_to_org_id is NOT NULL THEN
1315 OE_Header_Util.Get_Customer_Details
1316 ( p_org_id => l_deliver_to_org_id
1317 ,p_site_use_code => 'DELIVER_TO'
1318 ,x_customer_name => x_order_line_info_rec.deliver_to_customer_name
1319 ,x_customer_number => l_customer_number -- bug 4148163
1320 ,x_customer_id => l_customer_id -- bug 4148163
1321 ,x_location => x_order_line_info_rec.deliver_to_customer_location
1322 ,x_address1 => l_address1
1323 ,x_address2 => l_address2
1324 ,x_address3 => l_address3
1325 ,x_address4 => l_address4
1326 ,x_city => l_city
1327 ,x_state => l_state
1328 ,x_zip => l_zip
1329 ,x_country => l_country);
1330
1331 x_order_line_info_rec.deliver_to_customer_address :=
1332 l_address1 ||'@!!'|| l_address2 ||'@!!'|| l_address3 ||'@!!'||
1333 l_address4 ||'@!!'||
1334 l_city ||'@!!'|| l_state ||'@!!'|| l_zip ||'@!!'|| l_country;
1335
1336 x_order_line_info_rec.deliver_to_customer_address1 := l_address1;
1337 x_order_line_info_rec.deliver_to_customer_address2 := l_address2;
1338 x_order_line_info_rec.deliver_to_customer_address3 := l_address3;
1339 x_order_line_info_rec.deliver_to_customer_address4 := l_address4;
1340 x_order_line_info_rec.deliver_to_customer_city := l_city;
1341 x_order_line_info_rec.deliver_to_customer_state := l_state;
1342 x_order_line_info_rec.deliver_to_customer_zip := l_zip;
1343 x_order_line_info_rec.deliver_to_customer_country := l_country;
1344
1345 IF l_debug_level > 0 THEN
1346 OE_DEBUG_PUB.Add
1347 ('got deliver to ct details: '
1348 || x_order_line_info_rec.deliver_to_customer_name || '-'
1349 || x_order_line_info_rec.deliver_to_customer_location || '-'
1350 || x_order_line_info_rec.deliver_to_customer_address || '-', 5);
1351
1352 END IF;
1353 END IF;
1354
1355 IF l_deliver_to_contact_id is NOT NULL THEN
1356 x_order_line_info_rec.deliver_to_contact_name :=
1357 OE_Id_To_Value.Deliver_To_Contact
1358 (p_deliver_to_contact_id => l_deliver_to_contact_id);
1359
1360 OE_Id_To_Value.Get_Contact_Details
1361 ( p_contact_id => l_deliver_to_contact_id
1362 ,x_contact_name => l_temp1 -- bug 4148163
1363 ,x_phone_line_type => x_order_line_info_rec.deliver_to_contact_fax
1364 ,x_phone_number => x_order_line_info_rec.deliver_to_contact_phone
1365 ,x_email_address => x_order_line_info_rec.deliver_to_contact_email);
1366
1367 IF x_order_line_info_rec.deliver_to_contact_fax = 'FAX' THEN
1368 x_order_line_info_rec.deliver_to_contact_fax
1369 := x_order_line_info_rec.deliver_to_contact_phone;
1370 x_order_line_info_rec.deliver_to_contact_phone := null;
1371 END IF;
1372
1373 IF l_debug_level > 0 THEN
1374 OE_DEBUG_PUB.Add
1375 ('got deliver to contact details: '
1376 || x_order_line_info_rec.deliver_to_contact_name || '-'
1377 || x_order_line_info_rec.deliver_to_contact_fax || '-'
1378 || x_order_line_info_rec.deliver_to_contact_phone || '-'
1379 || x_order_line_info_rec.deliver_to_contact_email,5);
1380 END IF;
1381 END IF;
1382
1383 IF x_order_line_info_rec.customer_product_description is NULL THEN
1384 OE_Line_Util.Get_Item_Info
1385 ( p_item_identifier_type => l_item_identifier_type
1386 ,p_inventory_item_id => l_inventory_item_id
1387 ,p_ordered_item_id => l_ordered_item_id
1388 ,p_sold_to_org_id => l_sold_to_org_id
1389 ,p_ordered_item => l_ordered_item
1390 ,x_ordered_item => l_temp1 -- bug 4148163
1391 ,x_ordered_item_desc => x_order_line_info_rec.customer_product_description
1392 ,x_inventory_item => l_temp2 -- bug 4148163
1393 ,x_return_status => x_return_status
1394 ,x_msg_count => x_msg_count
1395 ,x_msg_data => x_msg_data);
1396 END IF;
1397
1398 IF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1399 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1400 ELSIF x_return_status = FND_API.G_RET_STS_ERROR THEN
1401 RAISE FND_API.G_EXC_ERROR;
1402 END IF;
1403
1404 IF l_shipping_method_code is NOT NULL THEN
1405 oe_debug_pub.add('here'|| l_shipping_method_code, 1);
1406
1407 SELECT ship_method_meaning
1408 INTO x_order_line_info_rec.shipping_method
1409 FROM wsh_carrier_services wshca
1410 WHERE SHIP_METHOD_CODE = l_shipping_method_code;
1411
1412 END IF;
1413
1414 IF l_debug_level > 0 THEN
1415 OE_DEBUG_PUB.Add
1416 ('got ct product and ship method: '
1417 || x_order_line_info_rec.customer_product_description || '-'
1418 || x_order_line_info_rec.shipping_method, 5);
1419 END IF;
1420 END IF; -- p_mode = 0 or 2
1421
1422 IF l_reset_policy THEN -- MOAC
1423 Mo_Global.Set_Policy_Context (p_access_mode => l_access_mode, p_org_id => l_current_org_id);
1424 END IF;
1425 /* commented for MOAC
1426 IF l_po_org_id <> l_org_id OR
1427 l_reset_context = 1 THEN
1428
1429 FND_GLOBAL.Apps_Initialize
1430 ( user_id => l_orig_user_id
1431 ,resp_id => l_orig_resp_id
1432 ,resp_appl_id => l_orig_resp_appl_id);
1433
1434 IF l_debug_level > 0 THEN
1435 OE_DEBUG_PUB.Add('context reset tp po org: ' || l_po_org_id,3);
1436 END IF;
1437
1438 l_reset_context := 0;
1439
1440 END IF; */
1441
1442 IF l_debug_level > 0 THEN
1443 OE_DEBUG_PUB.Add('Leaving Get_Order_Line_Info',1);
1444 END IF;
1445
1446 EXCEPTION
1447 WHEN FND_API.G_EXC_ERROR THEN
1448 x_return_status := FND_API.G_RET_STS_ERROR;
1449
1450 IF l_debug_level > 0 THEN
1451 OE_DEBUG_PUB.Add('error in Get_Order_Line_Info '||sqlerrm,1);
1452 END IF;
1453
1454 WHEN others THEN
1455
1456 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1457
1458 IF l_debug_level > 0 THEN
1459 OE_DEBUG_PUB.Add('error in Get_Order_Line_Info '||sqlerrm,1);
1460 END IF;
1461
1462 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1463 THEN
1464 OE_MSG_PUB.Add_Exc_Msg(G_PKG_Name, 'Get_Order_Line_Info');
1465 END IF;
1466
1467 OE_MSG_PUB.Count_And_Get (P_Count => x_msg_Count,
1468 P_Data => x_msg_Data);
1469 END Get_Order_Line_Info;
1470
1471
1472 /*-----------------------------------------------------
1473 Procedure : Purge_Drop_Ship_PO_Links
1474 Description :
1475 This procedure will be used by PO at the time of
1476 PO purge. OM will validate the order line status
1477 and then null out the PO links from the
1478 oe_drop_ship_sources table.
1479 -------------------------------------------------------*/
1480 Procedure Purge_Drop_Ship_PO_Links
1481 ( p_api_version IN NUMBER
1482 ,p_init_msg_list IN VARCHAR2
1483 ,p_commit IN VARCHAR2
1484 ,p_entity IN VARCHAR2
1485 ,p_entity_id_tbl IN PO_ENTITY_ID_TBL_TYPE
1486 ,x_msg_count OUT NOCOPY NUMBER
1487 ,x_msg_data OUT NOCOPY VARCHAR2
1488 ,x_return_status OUT NOCOPY VARCHAR2
1489 )
1490 IS
1491
1492 CURSOR c_dropship(cp_entity_id NUMBER) IS
1493 SELECT line_id
1494 FROM oe_drop_ship_sources
1495 WHERE (p_entity = 'PO_REQUISITION_HEADERS' AND
1496 requisition_header_id = cp_entity_id) OR
1497 (p_entity = 'PO_HEADERS' AND
1498 po_header_id = cp_entity_id)
1499 FOR UPDATE NOWAIT;
1500
1501 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
1502 I NUMBER := 1;
1503 l_line_id NUMBER;
1504
1505 BEGIN
1506
1507 IF l_debug_level > 0 THEN
1508 OE_DEBUG_PUB.Add('Entering Purge_Drop_Ship_PO_Links...',1);
1509 END IF;
1510
1511 x_return_status := FND_API.G_RET_STS_SUCCESS;
1512
1513 FOR I in 1..p_entity_id_tbl.COUNT LOOP
1514
1515 IF l_debug_level > 0 THEN
1516 OE_DEBUG_PUB.Add('Entity:'||p_entity,1);
1517 OE_DEBUG_PUB.Add('Entity ID :'||p_entity_id_tbl(I),1);
1518 END IF;
1519
1520 IF p_entity = 'PO_REQUISITION_HEADERS' OR
1521 p_entity = 'PO_HEADERS' THEN
1522
1523 OPEN c_dropship(p_entity_id_tbl(I));
1524 FETCH c_dropship INTO l_line_id;
1525
1526 IF l_debug_level > 0 THEN
1527 OE_DEBUG_PUB.Add('After Locking : '||l_line_id, 2);
1528 END IF;
1529 CLOSE c_dropship;
1530
1531 DELETE
1532 FROM oe_drop_ship_sources
1533 WHERE (p_entity = 'PO_REQUISITION_HEADERS' AND
1534 requisition_header_id = p_entity_id_tbl(I)) OR
1535 (p_entity = 'PO_HEADERS' AND
1536 po_header_id = p_entity_id_tbl(I));
1537
1538 IF l_debug_level > 0 THEN
1539 OE_DEBUG_PUB.Add('After Deleting: '||l_line_id, 2);
1540 END IF;
1541
1542 END IF;
1543
1544 END LOOP;
1545
1546 IF l_debug_level > 0 THEN
1547 OE_DEBUG_PUB.Add('Exiting Purge_Drop_Ship_PO_Links...',1);
1548 END IF;
1549
1550
1551 EXCEPTION
1552
1553 WHEN OTHERS THEN
1554
1555 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1556
1557 IF l_debug_level > 0 THEN
1558 OE_DEBUG_PUB.Add('UnExp Error in Purge_Drop_Ship_PO_Links...'||sqlerrm,4);
1559 END IF;
1560
1561 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1562 THEN
1563 OE_MSG_PUB.Add_Exc_Msg
1564 (G_PKG_NAME
1565 ,'Purge_Drop_Ship_PO_Links'
1566 );
1567 END IF;
1568
1569 OE_MSG_PUB.Count_And_Get (P_Count => x_msg_Count,
1570 P_Data => x_msg_Data);
1571 END Purge_Drop_Ship_PO_Links;
1572
1573 /*-----------------------------------------------------
1574 Procedure : Purge_Drop_Ship_PO_Validation
1575 Description :
1576 This procedure will be used by PO at the time of
1577 PO purge. OM will return validation status of each
1578 document passed whether it can be purged or not. The
1579 values will be Y or N. PO will pass entity as REQ or PO
1580 and a table of entity id's
1581 -------------------------------------------------------*/
1582 Procedure Purge_Drop_Ship_PO_Validation
1583 ( p_api_version IN NUMBER
1584 ,p_init_msg_list IN VARCHAR2
1585 ,p_commit IN VARCHAR2
1586 ,p_entity IN VARCHAR2
1587 ,p_entity_id_tbl IN PO_ENTITY_ID_TBL_TYPE
1588 ,x_purge_allowed_tbl OUT NOCOPY VAL_STATUS_TBL_TYPE
1589 ,x_msg_count OUT NOCOPY NUMBER
1590 ,x_msg_data OUT NOCOPY VARCHAR2
1591 ,x_return_status OUT NOCOPY VARCHAR2
1592 )
1593 IS
1594 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
1595 I NUMBER := 1;
1596 l_count NUMBER := 0;
1597
1598 BEGIN
1599
1600 IF l_debug_level > 0 THEN
1601 OE_DEBUG_PUB.Add('Entering Purge_Drop_Ship_PO_Validation...',1);
1602 END IF;
1603
1604 x_return_status := FND_API.G_RET_STS_SUCCESS;
1605 x_purge_allowed_tbl := VAL_STATUS_TBL_TYPE();
1606
1607 x_purge_allowed_tbl.extend(p_entity_id_tbl.COUNT);
1608
1609 FOR I in 1..p_entity_id_tbl.COUNT LOOP
1610
1611 IF l_debug_level > 0 THEN
1612 OE_DEBUG_PUB.Add('Entity:'||p_entity,1);
1613 OE_DEBUG_PUB.Add('Entity ID :'||p_entity_id_tbl(I),1);
1614 END IF;
1615
1616 IF p_entity = 'PO_REQUISITION_HEADERS' OR
1617 p_entity = 'PO_HEADERS' THEN
1618 -- fix for performance bug 3631271 begins
1619 IF p_entity = 'PO_REQUSITION_HEADERS'
1620 THEN
1621 SELECT count(*)
1622 INTO l_count
1623 FROM oe_drop_ship_sources ds,
1624 oe_order_lines_all l
1625 WHERE requisition_header_id = p_entity_id_tbl(I)
1626 AND l.line_id = ds.line_id
1627 AND l.header_id = ds.header_id
1628 AND nvl(l.open_flag,'Y') = 'Y'
1629 AND l.shipped_quantity is NULL;
1630 ELSE
1631 SELECT count(*)
1632 INTO l_count
1633 FROM oe_drop_ship_sources ds,
1634 oe_order_lines_all l
1635 WHERE po_header_id = p_entity_id_tbl(I)
1636 AND l.line_id = ds.line_id
1637 AND l.header_id = ds.header_id
1638 AND nvl(l.open_flag,'Y') = 'Y'
1639 AND l.shipped_quantity is NULL;
1640 END IF;
1641 -- fix for performance bug 3631271 ends
1642
1643 IF l_count > 0 THEN
1644
1645 IF l_debug_level > 0 THEN
1646 OE_DEBUG_PUB.Add('Line is Open :'||l_count);
1647 END IF;
1648
1649 x_purge_allowed_tbl(I) := 'N';
1650
1651 ELSE
1652 IF l_debug_level > 0 THEN
1653 OE_DEBUG_PUB.Add('Line is Closed:'||l_count);
1654 END IF;
1655
1656 x_purge_allowed_tbl(I) := 'Y';
1657
1658 END IF;
1659
1660 END IF;
1661
1662 END LOOP;
1663
1664 IF l_debug_level > 0 THEN
1665 OE_DEBUG_PUB.Add('Purge Table Count...'||x_purge_allowed_tbl.COUNT,1);
1666 OE_DEBUG_PUB.Add('Exiting Purge_Drop_Ship_PO_Validation...',1);
1667 END IF;
1668
1669 EXCEPTION
1670
1671 WHEN OTHERS THEN
1672
1673 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1674
1675 IF l_debug_level > 0 THEN
1676 OE_DEBUG_PUB.Add('UnExp Error in Purge_Drop_Ship_PO_Validation...'||sqlerrm,4);
1677 END IF;
1678
1679 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1680 THEN
1681 OE_MSG_PUB.Add_Exc_Msg
1682 (G_PKG_NAME
1683 ,'Purge_Drop_Ship_PO_Validation'
1684 );
1685 END IF;
1686
1687 OE_MSG_PUB.Count_And_Get (P_Count => x_msg_Count,
1688 P_Data => x_msg_Data);
1689
1690 END Purge_Drop_Ship_PO_Validation;
1691
1692 END OE_DROP_SHIP_GRP;
[Home] [Help]