1 Package Body OE_DROP_SHIP_GRP As
2 /* $Header: OEXUDSHB.pls 120.2.12000000.2 2007/05/14 20:57:01 prpathak 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
365 PROCEDURE Update_PO_Info (P_API_Version In Number,
362 Auto create process of Oracle Purchasing
363 -----------------------------------------------------------------*/
364
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;
491 WHERE Requisition_Line_ID = P_Req_Line_ID;
488 CURSOR L_Drop_Ship_Csr IS
489 SELECT Line_Id
490 FROM OE_Drop_Ship_Sources
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(
642
639 G_PKG_Name,
640 'Update_Drop_Ship_Links');
641 END IF;
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,
787 WHERE line_location_id = p_po_line_location_id
784 x_org_id
785 FROM oe_drop_ship_sources ds,
786 oe_order_lines_all oel
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
934 Get_Drop_Ship_Line_ids
931 OE_DEBUG_PUB.Add('Entering Get_Order_Line_Status...',1);
932 END IF;
933
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
1074 ,x_header_id => l_header_id
1071 ,p_mode => 1
1072 ,x_num_lines => l_num_lines
1073 ,x_line_id => l_line_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 user_item_description,
1186 cust_po_number,
1187 customer_line_number,
1188 customer_shipment_number,
1189 DECODE(p_mode, 0, null, RTRIM(line_number || '.'
1190 || shipment_number || '.'
1191 || option_number || '.'
1192 || component_number || '.'
1193 || service_number, '.')),
1194 DECODE(p_mode, 0, null, ordered_quantity),
1195 DECODE(p_mode, 0, null, shipped_quantity),
1196 DECODE(p_mode, 0, null, ordered_quantity2), -- INVCONV
1197 DECODE(p_mode, 0, null, shipped_quantity2), -- INVCONV
1198 DECODE(p_mode, 0, null, oelup.meaning)
1199 INTO l_ship_to_contact_id,
1200 l_deliver_to_contact_id,
1201 l_ship_to_org_id,
1202 l_deliver_to_org_id,
1203 l_sold_to_org_id,
1204 l_shipping_method_code,
1205 x_order_line_info_rec.shipping_instructions,
1206 x_order_line_info_rec.packing_instructions,
1207 l_inventory_item_id,
1208 l_item_identifier_type,
1209 l_ordered_item,
1210 x_order_line_info_rec.customer_product_description,
1214 x_order_line_info_rec.sales_order_line_number,
1211 x_order_line_info_rec.customer_po_number,
1212 x_order_line_info_rec.customer_po_line_number,
1213 x_order_line_info_rec.customer_po_shipment_number,
1215 x_order_line_info_rec.sales_order_line_ordered_qty,
1216 x_order_line_info_rec.sales_order_line_shipped_qty,
1217 x_order_line_info_rec.sales_order_line_ordered_qty2, -- INVCONV
1218 x_order_line_info_rec.sales_order_line_shipped_qty2, -- INVCONV
1219
1220 x_order_line_info_rec.sales_order_line_status
1221 FROM oe_order_lines_all oel, --oe_order_lines oel, -- Changes for BUG#6032405
1222 oe_lookups oelup
1223 WHERE line_id = l_line_id
1224 AND oelup.lookup_code = oel.flow_status_code
1225 AND oelup.lookup_type = 'LINE_FLOW_STATUS';
1226
1227 IF l_debug_level > 0 THEN
1228 oe_debug_pub.add('from oe_order_lines '
1229 || x_order_line_info_rec.customer_product_description
1230 || l_shipping_method_code, 3);
1231 END IF;
1232
1233 IF p_mode = 0 THEN -- do we need this??
1234 x_order_line_info_rec.sales_order_number := null;
1235 END IF;
1236
1237 END IF;
1238
1239 IF l_ship_to_org_id is NOT NULL THEN
1240 OE_Header_Util.Get_Customer_Details
1241 ( p_org_id => l_ship_to_org_id
1242 ,p_site_use_code => 'SHIP_TO'
1243 ,x_customer_name => x_order_line_info_rec.ship_to_customer_name
1244 ,x_customer_number => l_customer_number -- bug 4148163
1245 ,x_customer_id => l_customer_id -- bug 4148163
1246 ,x_location => x_order_line_info_rec.ship_to_customer_location
1247 ,x_address1 => l_address1
1248 ,x_address2 => l_address2
1249 ,x_address3 => l_address3
1250 ,x_address4 => l_address4
1251 ,x_city => l_city
1252 ,x_state => l_state
1253 ,x_zip => l_zip
1254 ,x_country => l_country);
1255
1256 IF l_debug_level > 0 THEN
1257 OE_DEBUG_PUB.Add
1258 ('got ship to ct name and location: '
1259 || x_order_line_info_rec.ship_to_customer_name || '-'
1260 || x_order_line_info_rec.ship_to_customer_location,5);
1261 END IF;
1262 END IF;
1263
1264 IF l_ship_to_contact_id is NOT NULL THEN
1265 x_order_line_info_rec.ship_to_contact_name :=
1266 OE_Id_To_Value.Ship_To_Contact
1267 (p_ship_to_contact_id => l_ship_to_contact_id);
1268 END IF;
1269
1270
1271 IF p_mode = 1 OR
1272 p_mode = 2 THEN
1273 IF l_debug_level > 0 THEN
1274 OE_DEBUG_PUB.Add
1275 ('sales order info: '
1276 || x_order_line_info_rec.sales_order_line_number || '-'
1277 || x_order_line_info_rec.sales_order_line_ordered_qty || '-'
1278 || x_order_line_info_rec.sales_order_line_shipped_qty || '-'
1279 || x_order_line_info_rec.sales_order_line_status || '-'
1280 || x_order_line_info_rec.sales_order_number,5);
1281 END IF;
1282 END IF;
1283
1284 IF p_mode = 0 OR
1285 p_mode = 2 THEN
1286
1287 IF l_ship_to_contact_id is NOT NULL THEN
1288
1289 OE_Id_To_Value.Get_Contact_Details
1290 ( p_contact_id => l_ship_to_contact_id
1291 ,x_contact_name => l_temp1 -- l_address1 bug 4148163
1292 ,x_phone_line_type => x_order_line_info_rec.ship_to_contact_fax
1293 ,x_phone_number => x_order_line_info_rec.ship_to_contact_phone
1294 ,x_email_address => x_order_line_info_rec.ship_to_contact_email);
1295
1296 IF x_order_line_info_rec.ship_to_contact_fax = 'FAX' THEN
1297 x_order_line_info_rec.ship_to_contact_fax
1298 := x_order_line_info_rec.ship_to_contact_phone;
1299 x_order_line_info_rec.ship_to_contact_phone := null;
1300 END IF;
1301
1302 IF l_debug_level > 0 THEN
1303 OE_DEBUG_PUB.Add
1304 ('got ship to contact details: '
1305 || x_order_line_info_rec.ship_to_contact_name || '-'
1306 || x_order_line_info_rec.ship_to_contact_fax || '-'
1307 || x_order_line_info_rec.ship_to_contact_phone || '-'
1308 || x_order_line_info_rec.ship_to_contact_email,5);
1309 END IF;
1310 END IF;
1311
1312 IF l_deliver_to_org_id is NOT NULL THEN
1313 OE_Header_Util.Get_Customer_Details
1314 ( p_org_id => l_deliver_to_org_id
1315 ,p_site_use_code => 'DELIVER_TO'
1316 ,x_customer_name => x_order_line_info_rec.deliver_to_customer_name
1317 ,x_customer_number => l_customer_number -- bug 4148163
1318 ,x_customer_id => l_customer_id -- bug 4148163
1319 ,x_location => x_order_line_info_rec.deliver_to_customer_location
1320 ,x_address1 => l_address1
1321 ,x_address2 => l_address2
1322 ,x_address3 => l_address3
1323 ,x_address4 => l_address4
1324 ,x_city => l_city
1325 ,x_state => l_state
1326 ,x_zip => l_zip
1327 ,x_country => l_country);
1328
1329 x_order_line_info_rec.deliver_to_customer_address :=
1330 l_address1 ||'@!!'|| l_address2 ||'@!!'|| l_address3 ||'@!!'||
1331 l_address4 ||'@!!'||
1332 l_city ||'@!!'|| l_state ||'@!!'|| l_zip ||'@!!'|| l_country;
1333
1337 x_order_line_info_rec.deliver_to_customer_address4 := l_address4;
1334 x_order_line_info_rec.deliver_to_customer_address1 := l_address1;
1335 x_order_line_info_rec.deliver_to_customer_address2 := l_address2;
1336 x_order_line_info_rec.deliver_to_customer_address3 := l_address3;
1338 x_order_line_info_rec.deliver_to_customer_city := l_city;
1339 x_order_line_info_rec.deliver_to_customer_state := l_state;
1340 x_order_line_info_rec.deliver_to_customer_zip := l_zip;
1341 x_order_line_info_rec.deliver_to_customer_country := l_country;
1342
1343 IF l_debug_level > 0 THEN
1344 OE_DEBUG_PUB.Add
1345 ('got deliver to ct details: '
1346 || x_order_line_info_rec.deliver_to_customer_name || '-'
1347 || x_order_line_info_rec.deliver_to_customer_location || '-'
1348 || x_order_line_info_rec.deliver_to_customer_address || '-', 5);
1349
1350 END IF;
1351 END IF;
1352
1353 IF l_deliver_to_contact_id is NOT NULL THEN
1354 x_order_line_info_rec.deliver_to_contact_name :=
1355 OE_Id_To_Value.Deliver_To_Contact
1356 (p_deliver_to_contact_id => l_deliver_to_contact_id);
1357
1358 OE_Id_To_Value.Get_Contact_Details
1359 ( p_contact_id => l_deliver_to_contact_id
1360 ,x_contact_name => l_temp1 -- bug 4148163
1361 ,x_phone_line_type => x_order_line_info_rec.deliver_to_contact_fax
1362 ,x_phone_number => x_order_line_info_rec.deliver_to_contact_phone
1363 ,x_email_address => x_order_line_info_rec.deliver_to_contact_email);
1364
1365 IF x_order_line_info_rec.deliver_to_contact_fax = 'FAX' THEN
1366 x_order_line_info_rec.deliver_to_contact_fax
1367 := x_order_line_info_rec.deliver_to_contact_phone;
1368 x_order_line_info_rec.deliver_to_contact_phone := null;
1369 END IF;
1370
1371 IF l_debug_level > 0 THEN
1372 OE_DEBUG_PUB.Add
1373 ('got deliver to contact details: '
1374 || x_order_line_info_rec.deliver_to_contact_name || '-'
1375 || x_order_line_info_rec.deliver_to_contact_fax || '-'
1376 || x_order_line_info_rec.deliver_to_contact_phone || '-'
1377 || x_order_line_info_rec.deliver_to_contact_email,5);
1378 END IF;
1379 END IF;
1380
1381 IF x_order_line_info_rec.customer_product_description is NULL THEN
1382 OE_Line_Util.Get_Item_Info
1383 ( p_item_identifier_type => l_item_identifier_type
1384 ,p_inventory_item_id => l_inventory_item_id
1385 ,p_ordered_item_id => l_ordered_item_id
1386 ,p_sold_to_org_id => l_sold_to_org_id
1387 ,p_ordered_item => l_ordered_item
1388 ,x_ordered_item => l_temp1 -- bug 4148163
1389 ,x_ordered_item_desc => x_order_line_info_rec.customer_product_description
1390 ,x_inventory_item => l_temp2 -- bug 4148163
1391 ,x_return_status => x_return_status
1392 ,x_msg_count => x_msg_count
1393 ,x_msg_data => x_msg_data);
1394 END IF;
1395
1396 IF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1397 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1398 ELSIF x_return_status = FND_API.G_RET_STS_ERROR THEN
1399 RAISE FND_API.G_EXC_ERROR;
1400 END IF;
1401
1402 IF l_shipping_method_code is NOT NULL THEN
1403 oe_debug_pub.add('here'|| l_shipping_method_code, 1);
1404
1405 SELECT ship_method_meaning
1406 INTO x_order_line_info_rec.shipping_method
1407 FROM wsh_carrier_services wshca
1408 WHERE SHIP_METHOD_CODE = l_shipping_method_code;
1409
1410 END IF;
1411
1412 IF l_debug_level > 0 THEN
1413 OE_DEBUG_PUB.Add
1414 ('got ct product and ship method: '
1415 || x_order_line_info_rec.customer_product_description || '-'
1416 || x_order_line_info_rec.shipping_method, 5);
1417 END IF;
1418 END IF; -- p_mode = 0 or 2
1419
1420 IF l_reset_policy THEN -- MOAC
1421 Mo_Global.Set_Policy_Context (p_access_mode => l_access_mode, p_org_id => l_current_org_id);
1422 END IF;
1423 /* commented for MOAC
1424 IF l_po_org_id <> l_org_id OR
1425 l_reset_context = 1 THEN
1426
1427 FND_GLOBAL.Apps_Initialize
1428 ( user_id => l_orig_user_id
1429 ,resp_id => l_orig_resp_id
1430 ,resp_appl_id => l_orig_resp_appl_id);
1431
1432 IF l_debug_level > 0 THEN
1433 OE_DEBUG_PUB.Add('context reset tp po org: ' || l_po_org_id,3);
1434 END IF;
1435
1436 l_reset_context := 0;
1437
1438 END IF; */
1439
1440 IF l_debug_level > 0 THEN
1441 OE_DEBUG_PUB.Add('Leaving Get_Order_Line_Info',1);
1442 END IF;
1443
1444 EXCEPTION
1445 WHEN FND_API.G_EXC_ERROR THEN
1446 x_return_status := FND_API.G_RET_STS_ERROR;
1447
1448 IF l_debug_level > 0 THEN
1449 OE_DEBUG_PUB.Add('error in Get_Order_Line_Info '||sqlerrm,1);
1450 END IF;
1451
1452 WHEN others THEN
1453
1454 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1455
1456 IF l_debug_level > 0 THEN
1457 OE_DEBUG_PUB.Add('error in Get_Order_Line_Info '||sqlerrm,1);
1458 END IF;
1459
1460 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1461 THEN
1465 OE_MSG_PUB.Count_And_Get (P_Count => x_msg_Count,
1462 OE_MSG_PUB.Add_Exc_Msg(G_PKG_Name, 'Get_Order_Line_Info');
1463 END IF;
1464
1466 P_Data => x_msg_Data);
1467 END Get_Order_Line_Info;
1468
1469
1470 /*-----------------------------------------------------
1471 Procedure : Purge_Drop_Ship_PO_Links
1472 Description :
1473 This procedure will be used by PO at the time of
1474 PO purge. OM will validate the order line status
1475 and then null out the PO links from the
1476 oe_drop_ship_sources table.
1477 -------------------------------------------------------*/
1478 Procedure Purge_Drop_Ship_PO_Links
1479 ( p_api_version IN NUMBER
1480 ,p_init_msg_list IN VARCHAR2
1481 ,p_commit IN VARCHAR2
1482 ,p_entity IN VARCHAR2
1483 ,p_entity_id_tbl IN PO_ENTITY_ID_TBL_TYPE
1484 ,x_msg_count OUT NOCOPY NUMBER
1485 ,x_msg_data OUT NOCOPY VARCHAR2
1486 ,x_return_status OUT NOCOPY VARCHAR2
1487 )
1488 IS
1489
1490 CURSOR c_dropship(cp_entity_id NUMBER) IS
1491 SELECT line_id
1492 FROM oe_drop_ship_sources
1493 WHERE (p_entity = 'PO_REQUISITION_HEADERS' AND
1494 requisition_header_id = cp_entity_id) OR
1495 (p_entity = 'PO_HEADERS' AND
1496 po_header_id = cp_entity_id)
1497 FOR UPDATE NOWAIT;
1498
1499 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
1500 I NUMBER := 1;
1501 l_line_id NUMBER;
1502
1503 BEGIN
1504
1505 IF l_debug_level > 0 THEN
1506 OE_DEBUG_PUB.Add('Entering Purge_Drop_Ship_PO_Links...',1);
1507 END IF;
1508
1509 x_return_status := FND_API.G_RET_STS_SUCCESS;
1510
1511 FOR I in 1..p_entity_id_tbl.COUNT LOOP
1512
1513 IF l_debug_level > 0 THEN
1514 OE_DEBUG_PUB.Add('Entity:'||p_entity,1);
1515 OE_DEBUG_PUB.Add('Entity ID :'||p_entity_id_tbl(I),1);
1516 END IF;
1517
1518 IF p_entity = 'PO_REQUISITION_HEADERS' OR
1519 p_entity = 'PO_HEADERS' THEN
1520
1521 OPEN c_dropship(p_entity_id_tbl(I));
1522 FETCH c_dropship INTO l_line_id;
1523
1524 IF l_debug_level > 0 THEN
1525 OE_DEBUG_PUB.Add('After Locking : '||l_line_id, 2);
1526 END IF;
1527 CLOSE c_dropship;
1528
1529 DELETE
1530 FROM oe_drop_ship_sources
1531 WHERE (p_entity = 'PO_REQUISITION_HEADERS' AND
1532 requisition_header_id = p_entity_id_tbl(I)) OR
1533 (p_entity = 'PO_HEADERS' AND
1534 po_header_id = p_entity_id_tbl(I));
1535
1536 IF l_debug_level > 0 THEN
1537 OE_DEBUG_PUB.Add('After Deleting: '||l_line_id, 2);
1538 END IF;
1539
1540 END IF;
1541
1542 END LOOP;
1543
1544 IF l_debug_level > 0 THEN
1545 OE_DEBUG_PUB.Add('Exiting Purge_Drop_Ship_PO_Links...',1);
1546 END IF;
1547
1548
1549 EXCEPTION
1550
1551 WHEN OTHERS THEN
1552
1553 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1554
1555 IF l_debug_level > 0 THEN
1556 OE_DEBUG_PUB.Add('UnExp Error in Purge_Drop_Ship_PO_Links...'||sqlerrm,4);
1557 END IF;
1558
1559 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1560 THEN
1561 OE_MSG_PUB.Add_Exc_Msg
1562 (G_PKG_NAME
1563 ,'Purge_Drop_Ship_PO_Links'
1564 );
1565 END IF;
1566
1567 OE_MSG_PUB.Count_And_Get (P_Count => x_msg_Count,
1568 P_Data => x_msg_Data);
1569 END Purge_Drop_Ship_PO_Links;
1570
1571 /*-----------------------------------------------------
1572 Procedure : Purge_Drop_Ship_PO_Validation
1573 Description :
1574 This procedure will be used by PO at the time of
1575 PO purge. OM will return validation status of each
1576 document passed whether it can be purged or not. The
1577 values will be Y or N. PO will pass entity as REQ or PO
1578 and a table of entity id's
1579 -------------------------------------------------------*/
1580 Procedure Purge_Drop_Ship_PO_Validation
1581 ( p_api_version IN NUMBER
1582 ,p_init_msg_list IN VARCHAR2
1583 ,p_commit IN VARCHAR2
1584 ,p_entity IN VARCHAR2
1585 ,p_entity_id_tbl IN PO_ENTITY_ID_TBL_TYPE
1586 ,x_purge_allowed_tbl OUT NOCOPY VAL_STATUS_TBL_TYPE
1587 ,x_msg_count OUT NOCOPY NUMBER
1588 ,x_msg_data OUT NOCOPY VARCHAR2
1589 ,x_return_status OUT NOCOPY VARCHAR2
1590 )
1591 IS
1592 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
1593 I NUMBER := 1;
1594 l_count NUMBER := 0;
1595
1596 BEGIN
1597
1598 IF l_debug_level > 0 THEN
1599 OE_DEBUG_PUB.Add('Entering Purge_Drop_Ship_PO_Validation...',1);
1600 END IF;
1601
1602 x_return_status := FND_API.G_RET_STS_SUCCESS;
1603 x_purge_allowed_tbl := VAL_STATUS_TBL_TYPE();
1604
1605 x_purge_allowed_tbl.extend(p_entity_id_tbl.COUNT);
1606
1607 FOR I in 1..p_entity_id_tbl.COUNT LOOP
1608
1609 IF l_debug_level > 0 THEN
1613
1610 OE_DEBUG_PUB.Add('Entity:'||p_entity,1);
1611 OE_DEBUG_PUB.Add('Entity ID :'||p_entity_id_tbl(I),1);
1612 END IF;
1614 IF p_entity = 'PO_REQUISITION_HEADERS' OR
1615 p_entity = 'PO_HEADERS' THEN
1616 -- fix for performance bug 3631271 begins
1617 IF p_entity = 'PO_REQUSITION_HEADERS'
1618 THEN
1619 SELECT count(*)
1620 INTO l_count
1621 FROM oe_drop_ship_sources ds,
1622 oe_order_lines_all l
1623 WHERE requisition_header_id = p_entity_id_tbl(I)
1624 AND l.line_id = ds.line_id
1625 AND l.header_id = ds.header_id
1626 AND nvl(l.open_flag,'Y') = 'Y'
1627 AND l.shipped_quantity is NULL;
1628 ELSE
1629 SELECT count(*)
1630 INTO l_count
1631 FROM oe_drop_ship_sources ds,
1632 oe_order_lines_all l
1633 WHERE po_header_id = p_entity_id_tbl(I)
1634 AND l.line_id = ds.line_id
1635 AND l.header_id = ds.header_id
1636 AND nvl(l.open_flag,'Y') = 'Y'
1637 AND l.shipped_quantity is NULL;
1638 END IF;
1639 -- fix for performance bug 3631271 ends
1640
1641 IF l_count > 0 THEN
1642
1643 IF l_debug_level > 0 THEN
1644 OE_DEBUG_PUB.Add('Line is Open :'||l_count);
1645 END IF;
1646
1647 x_purge_allowed_tbl(I) := 'N';
1648
1649 ELSE
1650 IF l_debug_level > 0 THEN
1651 OE_DEBUG_PUB.Add('Line is Closed:'||l_count);
1652 END IF;
1653
1654 x_purge_allowed_tbl(I) := 'Y';
1655
1656 END IF;
1657
1658 END IF;
1659
1660 END LOOP;
1661
1662 IF l_debug_level > 0 THEN
1663 OE_DEBUG_PUB.Add('Purge Table Count...'||x_purge_allowed_tbl.COUNT,1);
1664 OE_DEBUG_PUB.Add('Exiting Purge_Drop_Ship_PO_Validation...',1);
1665 END IF;
1666
1667 EXCEPTION
1668
1669 WHEN OTHERS THEN
1670
1671 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1672
1673 IF l_debug_level > 0 THEN
1674 OE_DEBUG_PUB.Add('UnExp Error in Purge_Drop_Ship_PO_Validation...'||sqlerrm,4);
1675 END IF;
1676
1677 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1678 THEN
1679 OE_MSG_PUB.Add_Exc_Msg
1680 (G_PKG_NAME
1681 ,'Purge_Drop_Ship_PO_Validation'
1682 );
1683 END IF;
1684
1685 OE_MSG_PUB.Count_And_Get (P_Count => x_msg_Count,
1686 P_Data => x_msg_Data);
1687
1688 END Purge_Drop_Ship_PO_Validation;
1689
1690 END OE_DROP_SHIP_GRP;
[Home] [Help]