1 PACKAGE BODY OE_ORDER_IMPORT_INTEROP_PUB AS
2 /* $Header: OEXPIMIB.pls 120.4.12010000.2 2008/08/04 15:00:43 amallik ship $ */
3
4 /*
5 ---------------------------------------------------------------
6 -- Start of Comments
7 -- API name OE_ORDER_IMPORT_INTEROP_PUB
8 -- Type Public
9 -- Purpose To support PO's existing functionality.
10 -- Function
11 -- Pre-reqs
12 -- Parameters
13 -- Version Current version = 1.0
14 -- Initial version = 1.0
15 --
16 -- Notes: These APIs first check if old OE is installed or new OE.
17 -- If new OE (called ONT) is installed then we use the fixed value
18 -- for p_order_source_id (= 10 since this is the value fixed for
19 -- internal orders in OE_ORDER_SOURCES table) otherwise we use the
20 -- p_order_source_id passed by the caller.
21 --
22 -- End of Comments
23 ------------------------------------------------------------------
24 */
25
26 /* ------------------------------------------------------------------
27 Function: Get_Open_Qty
28 ------------------------------------------------------------------
29 This accepts order source id, original system document reference and
30 original system line reference and returns the total open quantity.
31 ------------------------------------------------------------------
32 */
33 FUNCTION Get_Open_Qty (
34 p_order_source_id IN NUMBER
35 ,p_orig_sys_document_ref IN VARCHAR2
36 ,p_orig_sys_line_ref IN VARCHAR2
37 )
38 RETURN NUMBER
39 IS
40 x_open_qty NUMBER;
41 l_orig_sys_document_ref VARCHAR2(50);
42 l_orig_sys_line_ref VARCHAR2(50);
43 -- Fix for bug 2469894
44 l_header_id NUMBER;
45 --
46 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
47 --
48 -- Fix for bug 3217280
49 l_org_id NUMBER;
50 BEGIN
51
52 IF OE_INSTALL.Get_Active_Product = 'ONT' THEN
53
54 /* bsadri performance fixes for bug 1807599 so that the
55 indexes on oe_order_headers and oe_order_lines are
56 used again
57 */
58
59 SELECT segment1
60 INTO l_orig_sys_document_ref
61 FROM po_requisition_headers_all
62 WHERE requisition_header_id = to_number(p_orig_sys_document_ref);
63
64 -- Fix for bug 3217280 = Added table org_organization_definitions to get org_id
65 SELECT rl.LINE_NUM, nvl(org.operating_unit,-1)
66 INTO l_orig_sys_line_ref, l_org_id
67 FROM po_requisition_lines_all rl,
68 org_organization_definitions org
69 WHERE rl.requisition_line_id = to_number(p_orig_sys_line_ref)
70 AND rl.requisition_header_id = to_number(p_orig_sys_document_ref)
71 AND rl.source_organization_id = org.organization_id
72 AND rownum = 1;
73
74
75 -- Fix for bug 2469894,2641565
76 -- { Start
77 SELECT h.header_id
78 INTO l_header_id
79 FROM oe_order_headers_all h
80 WHERE h.order_source_id = nvl(p_order_source_id,10)
81 AND h.orig_sys_document_ref = l_orig_sys_document_ref
82 AND h.source_document_id = to_number(p_orig_sys_document_ref)
83 AND NVL(h.org_id,-1) = l_org_id;
84
85 Select sum(nvl(l.ordered_quantity, 0))
86 INTO x_open_qty
87 FROM oe_order_lines_all l
88 WHERE l.header_id = l_header_id
89 AND l.source_document_id = to_number(p_orig_sys_document_ref)
90 AND l.source_document_line_id = to_number(p_orig_sys_line_ref)
91 AND l.shipped_quantity IS NULL
92 AND l.shippable_flag = 'Y'
93 AND nvl(l.cancelled_flag, 'N') = 'N'
94 AND NVL(l.org_id,-1) = l_org_id
95 GROUP BY l.source_document_line_id;
96
97 -- End of bug 2469894,2641565 }
98 ELSIF OE_INSTALL.Get_Active_Product = 'OE' THEN
99 SELECT nvl(l.ordered_quantity, 0)
100 - nvl(l.shipped_quantity,0)
101 - nvl(l.cancelled_quantity,0)
102 INTO x_open_qty
103 FROM so_headers h, so_lines l
104 WHERE h.original_system_source_code = p_order_source_id
105 AND h.original_system_reference = p_orig_sys_document_ref
106 AND h.header_id = l.header_id
107 AND l.original_system_line_reference = p_orig_sys_line_ref
108 AND nvl(l.open_flag,'N') = 'Y';
109
110 END IF;
111
112 RETURN x_open_qty;
113
114 EXCEPTION
115 WHEN OTHERS THEN RETURN('');
116
117 END Get_Open_Qty;
118
119
120 /* ------------------------------------------------------------------
121 Function: Get_Shipped_Qty
122 ------------------------------------------------------------------
123 This accepts order source id, original system document reference and
124 original system line reference and returns the total shipped quantity.
125 ------------------------------------------------------------------
126 */
127 FUNCTION Get_Shipped_Qty (
128 p_order_source_id IN NUMBER
129 ,p_orig_sys_document_ref IN VARCHAR2
130 ,p_orig_sys_line_ref IN VARCHAR2
131 )
132 RETURN NUMBER
133 IS
134 x_shipped_qty NUMBER;
135 l_orig_sys_document_ref VARCHAR2(50);
136 l_orig_sys_line_ref VARCHAR2(50);
137 -- Fix for bug 2469894
138 l_header_id NUMBER;
139 --
140 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
141 --
142 -- Fix for bug 3217280
143 l_org_id NUMBER;
144 BEGIN
145
146 IF OE_INSTALL.Get_Active_Product = 'ONT' THEN
147 /* bsadri performance fixes for bug 1807599 so that the
148 indexes on oe_order_headers and oe_order_lines are
149 used again
150 */
151
152 SELECT segment1
153 INTO l_orig_sys_document_ref
154 FROM po_requisition_headers_all
155 WHERE requisition_header_id = to_number(p_orig_sys_document_ref);
156
157 -- Fix for bug 3217280 = Added table org_organization_definitions to get org_id
158 SELECT rl.LINE_NUM, nvl(org.operating_unit,-1)
159 INTO l_orig_sys_line_ref, l_org_id
160 FROM po_requisition_lines_all rl,
161 org_organization_definitions org
162 WHERE rl.requisition_line_id = to_number(p_orig_sys_line_ref)
163 AND rl.requisition_header_id = to_number(p_orig_sys_document_ref)
164 AND rl.source_organization_id = org.organization_id
165 AND rownum = 1;
166
167 -- Fix for bug 2469894
168 -- { Start
169 SELECT h.header_id
170 INTO l_header_id
171 FROM oe_order_headers_all h
172 WHERE h.order_source_id = nvl(p_order_source_id,10)
173 AND h.orig_sys_document_ref = l_orig_sys_document_ref
174 AND h.source_document_id = to_number(p_orig_sys_document_ref)
175 AND h.org_id = l_org_id;
176
177 Select sum(nvl(l.shipped_quantity,0))
178 INTO x_shipped_qty
179 FROM oe_order_lines_all l
180 WHERE l.header_id = l_header_id
181 AND l.source_document_id = to_number(p_orig_sys_document_ref)
182 AND l.source_document_line_id = to_number(p_orig_sys_line_ref)
183 AND l.org_id = l_org_id
184 GROUP BY l.source_document_line_id;
185
186 -- End of bug 2469894 }
187
188 /* Commented as part of above fix
189 SELECT nvl(l.shipped_quantity,0)
190 INTO x_shipped_qty
191 -- Following is changed to _all tables because of change in PO to multi-org
192 FROM oe_order_headers_all h, oe_order_lines_all l
193 -- Following is changed to _all tables because of change in PO to multi-org
194 WHERE h.order_source_id = nvl(p_order_source_id,10)
195 --AND h.source_document_id = p_orig_sys_document_ref
196 AND h.orig_sys_document_ref = l_orig_sys_document_ref
197 -- aksingh adding this for internal order multi-org change duplicate issue
198 -- Bug 1794206 fix
199 AND h.source_document_id = p_orig_sys_document_ref
200 AND h.header_id = l.header_id
201 --AND l.source_document_line_id = p_orig_sys_line_ref;
202 AND l.orig_sys_line_ref = l_orig_sys_line_ref;
203 */
204
205 ELSIF OE_INSTALL.Get_Active_Product = 'OE' THEN
206 SELECT nvl(l.shipped_quantity,0)
207 INTO x_shipped_qty
208 FROM so_headers h, so_lines l
209 WHERE h.original_system_source_code = p_order_source_id
210 AND h.original_system_reference = p_orig_sys_document_ref
211 AND h.header_id = l.header_id
212 AND l.original_system_line_reference = p_orig_sys_line_ref;
213
214 END IF;
215
216 RETURN x_shipped_qty;
217
218 EXCEPTION
219 WHEN OTHERS THEN RETURN('');
220
221 END Get_Shipped_Qty;
222
223
224 /* ------------------------------------------------------------------
225 Function: Get_Cancelled_Qty
226 ------------------------------------------------------------------
227 This accepts order source id, original system document reference and
228 original system line reference and returns the total cancelled quantity.
229 ------------------------------------------------------------------
230 */
231 FUNCTION Get_Cancelled_Qty (
232 p_order_source_id IN NUMBER
233 ,p_orig_sys_document_ref IN VARCHAR2
234 ,p_orig_sys_line_ref IN VARCHAR2
235
236 )
237 RETURN NUMBER
238 IS
239 x_cancelled_qty NUMBER;
240 l_orig_sys_document_ref VARCHAR2(50);
241 l_orig_sys_line_ref VARCHAR2(50);
242 -- Fix for bug 2469894
243 l_header_id NUMBER;
244 l_sum_of_quantity NUMBER;
245 l_diff_ship_cancel NUMBER;
246
247 -- Fix for bug 3217280
248 l_org_id NUMBER;
249 Cursor Cancelled_Qty_Cur Is
250 Select sum(l.cancelled_quantity) cancelled_quantity,
251 sum( l.shipped_quantity) shipped_quantity,
252 sum(l.ordered_quantity) ordered_quantity
253 FROM oe_order_lines_all l
254 WHERE l.header_id = l_header_id
255 AND l.source_document_id = to_number(p_orig_sys_document_ref)
256 AND l.source_document_line_id = to_number(p_orig_sys_line_ref)
257 AND l.org_id = l_org_id
258 GROUP BY l.source_document_line_id;
259
260
261 --
262 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
263 --
264 BEGIN
265
266 IF OE_INSTALL.Get_Active_Product = 'ONT' THEN
267 /* bsadri performance fixes for bug 1807599 so that the
268 indexes on oe_order_headers and oe_order_lines are
269 used again
270 */
271
272 SELECT segment1
273 INTO l_orig_sys_document_ref
274 FROM po_requisition_headers_all
275 WHERE requisition_header_id = to_number(p_orig_sys_document_ref);
276
277 -- Fix for bug 3217280 = Added table org_organization_definitions to get org_id
278 SELECT rl.LINE_NUM, nvl(org.operating_unit,-1)
279 INTO l_orig_sys_line_ref, l_org_id
280 FROM po_requisition_lines_all rl,
281 org_organization_definitions org
282 WHERE rl.requisition_line_id = to_number(p_orig_sys_line_ref)
283 AND rl.requisition_header_id = to_number(p_orig_sys_document_ref)
284 AND rl.source_organization_id = org.organization_id
285 AND rownum = 1;
286
287 -- Fix for bug 2469894,2641565
288 -- { Start
289 SELECT h.header_id
290 INTO l_header_id
291 FROM oe_order_headers_all h
292 WHERE h.order_source_id = nvl(p_order_source_id,10)
293 AND h.orig_sys_document_ref = l_orig_sys_document_ref
294 AND h.source_document_id = to_number(p_orig_sys_document_ref)
295 AND h.org_id = l_org_id;
296
297 l_sum_of_quantity := 0;
298
299 For Cancelled_Qty_Rec In Cancelled_Qty_Cur Loop
300 If Cancelled_Qty_Rec.Cancelled_Quantity Is Not Null Then
301 l_sum_of_quantity := l_sum_of_quantity +
302 Cancelled_Qty_Rec.Cancelled_Quantity;
303 End If;
304
305 If Cancelled_Qty_Rec.Shipped_Quantity > 0 Then
306 l_diff_ship_cancel := Cancelled_Qty_Rec.Ordered_Quantity -
307 Cancelled_Qty_Rec.Shipped_Quantity;
308 If l_diff_ship_cancel > 0 Then
309 l_sum_of_quantity := l_sum_of_quantity + l_diff_ship_cancel;
310 End If;
311 End If;
312 End Loop;
313
314 x_cancelled_qty := l_sum_of_quantity;
315 -- End of bug 2469894,2641565 }
316
317 ELSIF OE_INSTALL.Get_Active_Product = 'OE' THEN
318 SELECT l.cancelled_quantity
319 INTO x_cancelled_qty
320 FROM so_headers h, so_lines l
321 WHERE h.original_system_source_code = p_order_source_id
322 AND h.original_system_reference = p_orig_sys_document_ref
323 AND h.header_id = l.header_id
324 AND l.original_system_line_reference = p_orig_sys_line_ref;
325
326 END IF;
327
328 RETURN x_cancelled_qty;
329
330 EXCEPTION
331 WHEN OTHERS THEN RETURN('');
332
333 END Get_Cancelled_Qty;
334
335
336 /* ------------------------------------------------------------------
337 Function: Get_Order_Number
338 ------------------------------------------------------------------
339 This accepts Order Source Id, Original System Reference and
340 Original System Line Reference and returns the corresponding
341 Order Number.
342 ------------------------------------------------------------------
343 */
344 FUNCTION Get_Order_Number (
345 p_order_source_id IN NUMBER
346 ,p_orig_sys_document_ref IN VARCHAR2
347 ,p_orig_sys_line_ref IN VARCHAR2
348 )
349 RETURN NUMBER
350 IS
351 x_order_number NUMBER;
352 l_orig_sys_document_ref VARCHAR2(50);
353 l_orig_sys_line_ref VARCHAR2(50);
354 --
355 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
356 --
357 -- Fix for bug 3217280
358 l_org_id NUMBER;
359 BEGIN
360
361 IF OE_INSTALL.Get_Active_Product = 'ONT' THEN
362 /* bsadri performance fixes for bug 1807599 so that the
363 indexes on oe_order_headers and oe_order_lines are
364 used again
365 */
366
367 SELECT segment1
368 INTO l_orig_sys_document_ref
369 FROM po_requisition_headers_all
370 WHERE requisition_header_id = to_number(p_orig_sys_document_ref);
371
372 -- Fix for bug 3217280 = Added table org_organization_definitions to get org_id
373 SELECT rl.LINE_NUM, nvl(org.operating_unit,-1)
374 INTO l_orig_sys_line_ref, l_org_id
375 FROM po_requisition_lines_all rl,
376 org_organization_definitions org
377 WHERE rl.requisition_line_id = to_number(p_orig_sys_line_ref)
378 AND rl.requisition_header_id = to_number(p_orig_sys_document_ref)
379 AND rl.source_organization_id = org.organization_id
380 AND rownum = 1;
381
382 -- Fix 7031428, added distinct for split lines from partial shipment
383
384 SELECT distinct h.order_number
385 INTO x_order_number
386 FROM oe_order_headers_all h,oe_order_lines_all l
387 WHERE h.order_source_id = nvl(p_order_source_id,10)
388 AND h.orig_sys_document_ref = l_orig_sys_document_ref
389 AND h.source_document_id = to_number(p_orig_sys_document_ref)
390 AND l.orig_sys_document_ref = l_orig_sys_document_ref
391 AND l.orig_sys_line_ref = l_orig_sys_line_ref
392 AND h.source_document_id = l.source_document_id
393 AND nvl(h.org_id, -1) = l_org_id;
394
395 ELSIF OE_INSTALL.Get_Active_Product = 'OE' THEN
396 SELECT h.order_number
397 INTO x_order_number
398 FROM so_headers h, so_lines l
399 WHERE h.original_system_source_code = p_order_source_id
400 AND h.original_system_reference = p_orig_sys_document_ref
401 AND h.header_id = l.header_id
402 AND l.original_system_line_reference = p_orig_sys_line_ref;
403
404 END IF;
405
406 RETURN x_order_number;
407
408 EXCEPTION
409 WHEN OTHERS THEN RETURN('');
410
411 END Get_Order_Number;
412
413
414 /* ------------------------------------------------------------------
415 Function: Get_Header_Id
416 ------------------------------------------------------------------
417 This accepts a Requisition Line Id and returns the corresponding
418 Order Header Id.
419
420 p_type='S' will get it from so_headers/oe_order_headers table and
421 ='D' will get it from so_drop_ship_sources/oe_drop_ship_sources
422 ------------------------------------------------------------------
423 */
424 -- aksingh question ask whether they are sending id in ref column ????? 11/28
425 FUNCTION Get_Header_Id (
426 p_order_source_id IN NUMBER
427 ,p_orig_sys_document_ref IN VARCHAR2
428 ,p_requisition_header_id IN NUMBER
429 ,p_type IN VARCHAR2
430 ,p_requisition_line_id IN NUMBER
431 )
432 RETURN NUMBER
433 IS
434 x_header_id NUMBER;
435 --
436 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
437 --
438 -- Fix for bug 3217280
439 l_org_id NUMBER;
440 l_orig_sys_line_ref VARCHAR2(50);
441 BEGIN
442
443 IF OE_INSTALL.Get_Active_Product = 'ONT' THEN
444 IF p_type = 'H' THEN
445 /* bsadri performance fixes for bug 1807599 so that the
446 indexes on oe_order_headers and oe_order_lines are
447 used again
448 */
449
450 -- Fix for bug 3217280 = Added table org_organization_definitions to get org_id
451 SELECT rl.LINE_NUM, nvl(org.operating_unit,-1)
452 INTO l_orig_sys_line_ref, l_org_id
453 FROM po_requisition_lines_all rl,
454 org_organization_definitions org
455 WHERE rl.requisition_line_id = p_requisition_line_id
456 AND rl.requisition_header_id = p_requisition_header_id
457 AND rl.source_organization_id = org.organization_id
458 AND rownum = 1;
459
460 SELECT h.header_id
461 INTO x_header_id
462 FROM oe_order_headers_all h
463 WHERE h.order_source_id = nvl(p_order_source_id,10)
464 AND h.orig_sys_document_ref = p_orig_sys_document_ref
465 AND h.source_document_id = p_requisition_header_id
466 AND nvl(h.org_id,-1) = l_org_id;--added nvl for bug5394855
467 ELSIF p_type = 'D' THEN
468 SELECT max(d.header_id)
469 INTO x_header_id
470 FROM oe_drop_ship_sources d
471 WHERE d.requisition_header_id = p_requisition_header_id
472 AND d.requisition_line_id =
473 nvl(p_requisition_line_id, d.requisition_line_id);
474 END IF;
475
476 ELSIF OE_INSTALL.Get_Active_Product = 'OE' THEN
477 IF p_type = 'H' THEN
478 SELECT h.header_id
479 INTO x_header_id
480 FROM so_headers h
481 WHERE h.original_system_source_code = p_order_source_id
482 AND h.original_system_reference = p_orig_sys_document_ref;
483
484 ELSIF p_type = 'D' THEN
485 SELECT max(d.header_id)
486 INTO x_header_id
487 FROM so_drop_ship_sources d
488 WHERE d.requisition_header_id = p_requisition_header_id;
489 END IF;
490
491 END IF;
492
493 RETURN x_header_id;
494
495 EXCEPTION
496 WHEN OTHERS THEN RETURN('');
497
498 END Get_Header_Id;
499
500
501 /* ------------------------------------------------------------------
502 Function: Get_Req_Header_Id
503 ------------------------------------------------------------------
504 This accepts a Order Header Id and returns the corresponding
505 Requisition Header Id.
506
507 p_type='S' will get it from so_headers/oe_order_headers table and
508 ='D' will get it from so_drop_ship_sources/oe_drop_ship_sources
509 ------------------------------------------------------------------
510 */
511 FUNCTION Get_Req_Header_Id (
512 p_header_id IN NUMBER
513 ,p_type IN VARCHAR2
514 )
515 RETURN NUMBER IS
516 x_req_header_id NUMBER;
517 --
518 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
519 --
520 BEGIN
521
522 IF OE_INSTALL.Get_Active_Product = 'ONT' THEN
523 IF p_type = 'H' THEN /* From oe_order_headers */
524 -- Following change is made to support the multi-org PO functionality
525 -- SELECT r.requisition_header_id
526 -- INTO x_req_header_id
527 -- FROM oe_order_headers h, po_requisition_headers r
528 -- WHERE h.header_id = p_header_id
529 -- AND h.orig_sys_document_ref = r.segment1;
530
531 SELECT source_document_id
532 INTO x_req_header_id
533 FROM oe_order_headers_all
534 WHERE header_id = p_header_id;
535 ELSIF p_type = 'D' THEN /* From oe_drop_ship_sources */
536 SELECT max(d.requisition_header_id)
537 INTO x_req_header_id
538 FROM oe_drop_ship_sources d
539 WHERE d.header_id = p_header_id;
540 END IF;
541 ELSIF OE_INSTALL.Get_Active_Product = 'OE' THEN
542 IF p_type = 'H' THEN /* From oe_order_headers */
543 SELECT r.requisition_header_id
544 INTO x_req_header_id
545 FROM so_headers h, po_requisition_headers r
546 WHERE h.header_id = p_header_id
547 AND h.original_system_reference = r.segment1;
548 ELSIF p_type = 'D' THEN /* From oe_drop_ship_sources */
549 SELECT d.requisition_header_id
550 INTO x_req_header_id
551 FROM so_drop_ship_sources d
552 WHERE d.header_id = p_header_id;
553 END IF;
554 END IF;
555 RETURN x_req_header_id;
556
557 EXCEPTION
558 WHEN OTHERS THEN RETURN('');
559
560 END Get_Req_Header_Id;
561
562
563 ------------------------------------------------------------------
564 -- Procedure: Get_Line_Id
565 ------------------------------------------------------------------
566 -- This accepts a Requisition Line Id and Line Num for the requisition
567 -- and return order's line id.
568 ------------------------------------------------------------------
569 -- Fix for bug 2520049
570 -- { Start
571 PROCEDURE Get_Line_Id (
572 p_order_source_id IN NUMBER
573 ,p_orig_sys_document_ref IN VARCHAR2
574 ,p_requisition_header_id IN NUMBER
575 ,p_line_num IN VARCHAR2
576 ,p_requisition_line_id IN NUMBER
577 ,x_line_id_tbl OUT NOCOPY /* file.sql.39 change */ LineId_Tbl_Type
578 ,x_return_status OUT NOCOPY /* file.sql.39 change */ VARCHAR2
579 )
580 IS
581 l_header_id NUMBER;
582 l_line_id NUMBER;
583 l_line_id_count NUMBER := 0;
584 Cursor Line_Id_Cur Is
585 Select line_id
586 FROM oe_order_lines_all l
587 WHERE l.header_id = l_header_id
588 AND l.source_document_id = p_requisition_header_id
589 AND l.source_document_line_id = p_requisition_line_id
590 AND l.order_source_id = p_order_source_id
591 AND l.shipped_quantity IS NOT NULL;
592 --
593 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
594 --
595 BEGIN
596
597 oe_debug_pub.add('Entering OEXPIMIB.Get_Line_Id');
598
599 x_return_status := fnd_api.g_ret_sts_success;
600 SELECT h.header_id
601 INTO l_header_id
602 FROM oe_order_headers_all h
603 WHERE h.order_source_id = p_order_source_id
604 AND h.orig_sys_document_ref = p_orig_sys_document_ref
605 AND h.source_document_id = p_requisition_header_id;
606
607 Open Line_Id_Cur;
608 Loop
609 Fetch Line_Id_Cur Into l_line_id;
610 Exit When Line_Id_Cur%NotFound;
611
612 l_line_id_count := l_line_id_count + 1;
613 x_line_id_tbl(l_line_id_count).line_id := l_line_id;
614 End Loop;
615
616
617 EXCEPTION
618 WHEN OTHERS THEN
619 oe_debug_pub.add('Unexpected error: '||sqlerrm);
620 If oe_msg_pub.check_msg_level(oe_msg_pub.g_msg_lvl_unexp_error) Then
621 x_return_status := fnd_api.g_ret_sts_unexp_error;
622 oe_msg_pub.add_exc_Msg (g_pkg_name, 'oe_order_import_interop_pub');
623 END IF;
624
625 END Get_Line_Id;
626 -- End }
627
628
629 /*Bug2770121*/
630 /* ------------------------------------------------------------------
631 Procedure: Get_Requisition_Header_Ids
632 ------------------------------------------------------------------
633 This accepts a Order Header Id and returns the corresponding
634 Requisition Header Ids associated with the Drop ship Header id.
635 ------------------------------------------------------------------
636 */
637 Procedure Get_Requisition_Header_Ids (
638 p_header_id IN NUMBER
639 ,x_req_header_id_tbl OUT NOCOPY /* file.sql.39 change */ HeaderId_Tbl_Type
640 )
641 IS
642 l_header_id NUMBER;
643 l_header_id_count NUMBER := 0;
644 -- Addded not null condition for the bug 3688591
645 CURSOR header_id_cur is
646 SELECT distinct d.requisition_header_id
647 FROM oe_drop_ship_sources d
648 WHERE d.header_id = p_header_id
649 And d.requisition_header_id is not null;
650 --
651 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
652 --
653 BEGIN
654 IF l_debug_level > 0 THEN
655 oe_debug_pub.add('Entering OEXPIMIB.Get_Requisition_header_ids');
656 END IF;
657
658 Open header_Id_Cur;
659 Loop
660 Fetch header_Id_Cur Into l_header_id;
661 Exit When header_Id_Cur%NotFound;
662
663 l_header_id_count := l_header_id_count + 1;
664 x_req_header_id_tbl(l_header_id_count).header_id := l_header_id;
665 End Loop;
666
667 EXCEPTION
668 WHEN OTHERS THEN
669 IF l_debug_level > 0 THEN
670 oe_debug_pub.add('Unexpected error: '||sqlerrm);
671 END IF;
672 If oe_msg_pub.check_msg_level(oe_msg_pub.g_msg_lvl_unexp_error) Then
673 oe_msg_pub.add_exc_Msg (g_pkg_name, 'oe_order_import_interop_pub');
674 END IF;
675
676 END Get_Requisition_Header_Ids;
677
678 END OE_ORDER_IMPORT_INTEROP_PUB;
[Home] [Help]