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