1 PACKAGE BODY rcv_table_functions AS
2 /* $Header: RCVTBFNB.pls 120.4.12010000.3 2008/12/26 07:28:26 ksivasa ship $*/
3 g_debug_flag CONSTANT VARCHAR2(1) := NVL(fnd_profile.VALUE('RCV_DEBUG_MODE'), 'N');
4 g_rhi_row rcv_headers_interface%ROWTYPE;
5 g_rti_row rcv_transactions_interface%ROWTYPE;
6 g_rt_row rcv_transactions%ROWTYPE;
7 g_rsh_row rcv_shipment_headers%ROWTYPE;
8 g_rsl_row rcv_shipment_lines%ROWTYPE;
9 g_poh_row po_headers_all%ROWTYPE;
10 g_pol_row po_lines_all%ROWTYPE;
11 g_pll_row po_line_locations_all%ROWTYPE;
12 g_pod_row po_distributions_all%ROWTYPE;
13 g_oeh_row oe_order_headers_all%ROWTYPE;
14 g_oel_row oe_order_lines_all%ROWTYPE;
15 g_prl_row po_requisition_lines_all%ROWTYPE;
16 g_prd_row po_req_distributions_all%ROWTYPE;
17 g_msi_row mtl_system_items%ROWTYPE;
18 g_mic_row mtl_item_categories%ROWTYPE;
19 g_mp_row mtl_parameters%ROWTYPE;
20 g_rp_row rcv_parameters%ROWTYPE;
21 g_fc_row fnd_currencies%ROWTYPE;
22 g_sob_row gl_sets_of_books%ROWTYPE;
23 g_fsp_row financials_system_parameters%ROWTYPE;
24 g_fspa_row financials_system_params_all%ROWTYPE;
25 g_pvs_row po_vendor_sites_all%ROWTYPE;
26 g_muom_row mtl_units_of_measure%ROWTYPE;
27
28 g_po_lookups lookup_table_type;
29
30 /* Bug 5246147: Removed the following function definitions,
31 get_fspa_row_from_org() and
32 get_sob_row_from_id() */
33
34 PROCEDURE invalid_value(
35 p_value IN VARCHAR2,
36 p_column IN VARCHAR2
37 ) IS
38 BEGIN
39 rcv_error_pkg.set_error_message('RCV_INVALID_ROI_VALUE_NE');
40 rcv_error_pkg.set_token('COLUMN', p_column);
41 rcv_error_pkg.set_token('ROI_VALUE', p_value);
42 g_error_column := p_column;
43 asn_debug.put_line('Invalid value ' || p_value || ' for ' || p_column || ' in RCV_TABLE_FUNCTIONS');
44 RAISE e_fatal_error;
45 END invalid_value;
46
47 /* NOTE: All functions except IS_ORG_ID_IN_OU are operating unit (OU) agnostic */
48 /* It is the caller's responsibility to check if the OU is correct and the org_id is in the OU */
49
50 /*******/
51 /* RHI */
52 /*******/
53 FUNCTION get_rhi_row_from_id(
54 p_header_interface_id IN rcv_headers_interface.header_interface_id%TYPE
55 )
56 RETURN rcv_headers_interface%ROWTYPE IS
57 BEGIN
58 IF (p_header_interface_id IS NULL) THEN
59 RETURN NULL;
60 END IF;
61
62 IF (p_header_interface_id = g_rhi_row.header_interface_id) THEN
63 RETURN g_rhi_row;
64 END IF;
65
66 SELECT *
67 INTO g_rhi_row
68 FROM rcv_headers_interface
69 WHERE header_interface_id = p_header_interface_id;
70
71 RETURN g_rhi_row;
72 EXCEPTION
73 WHEN OTHERS THEN
74 invalid_value(p_header_interface_id, 'HEADER_INTERFACE_ID');
75 END get_rhi_row_from_id;
76
77 PROCEDURE update_rhi_row(
78 p_rhi_row IN rcv_headers_interface%ROWTYPE
79 ) IS
80 BEGIN
81 asn_debug.put_line('Updating RHI HEADER_INTERFACE_ID=' || p_rhi_row.header_interface_id);
82
83 UPDATE rcv_headers_interface
84 SET ROW = p_rhi_row
85 WHERE header_interface_id = p_rhi_row.header_interface_id;
86
87 g_rhi_row := p_rhi_row;
88 END;
89
90 /*******/
91 /* RTI */
92 /*******/
93 FUNCTION get_rti_row_from_id(
94 p_interface_transaction_id IN rcv_transactions_interface.interface_transaction_id%TYPE
95 )
96 RETURN rcv_transactions_interface%ROWTYPE IS
97 BEGIN
98 IF (p_interface_transaction_id IS NULL) THEN
99 RETURN NULL;
100 END IF;
101
102 IF (p_interface_transaction_id = g_rti_row.interface_transaction_id) THEN
103 RETURN g_rti_row;
104 END IF;
105
106 SELECT *
107 INTO g_rti_row
108 FROM rcv_transactions_interface
109 WHERE interface_transaction_id = p_interface_transaction_id;
110
111 RETURN g_rti_row;
112 EXCEPTION
113 WHEN OTHERS THEN
114 invalid_value(p_interface_transaction_id, 'INTERFACE_TRANSACTION_ID');
115 END;
116
117 PROCEDURE update_rti_row(
118 p_rti_row IN rcv_transactions_interface%ROWTYPE
119 ) IS
120 BEGIN
121 asn_debug.put_line('Updating RTI INTERFACE_TRANSACTION_ID=' || p_rti_row.interface_transaction_id);
122
123 UPDATE rcv_transactions_interface
124 SET ROW = p_rti_row
125 WHERE interface_transaction_id = p_rti_row.interface_transaction_id
126 AND processing_status_code <> 'ERROR'; --BUG: 5598140
127
128 g_rti_row := p_rti_row;
129 END;
130
131 /*******/
132 /* RT */
133 /*******/
134 FUNCTION get_rt_row_from_id(
135 p_transaction_id IN rcv_transactions.transaction_id%TYPE
136 )
137 RETURN rcv_transactions%ROWTYPE IS
138 BEGIN
139 IF (p_transaction_id IS NULL) THEN
140 RETURN NULL;
141 END IF;
142
143 IF (p_transaction_id = g_rt_row.transaction_id) THEN
144 RETURN g_rt_row;
145 END IF;
146
147 SELECT *
148 INTO g_rt_row
149 FROM rcv_transactions
150 WHERE transaction_id = p_transaction_id;
151
152 RETURN g_rt_row;
153 EXCEPTION
154 WHEN OTHERS THEN
155 invalid_value(p_transaction_id, 'TRANSACTION_ID');
156 END;
157
158 /*******/
159 /* RSH */
160 /*******/
161 FUNCTION get_rsh_row_from_num(
162 p_shipment_num IN rcv_shipment_headers.shipment_num%TYPE,
163 p_vendor_id IN rcv_shipment_headers.vendor_id%TYPE,
164 p_vendor_site_id IN rcv_shipment_headers.vendor_site_id%TYPE,
165 p_ship_to_org_id IN rcv_shipment_headers.ship_to_org_id%TYPE,
166 p_shipped_date IN rcv_shipment_headers.shipped_date%TYPE,
167 no_data_found_is_error IN BOOLEAN
168 )
169 RETURN rcv_shipment_headers%ROWTYPE IS
170 BEGIN
171 IF (p_shipment_num IS NULL) THEN
172 RETURN NULL;
173 END IF;
174
175 IF (p_shipment_num = g_rsh_row.shipment_num)
176 AND ( NVL(p_ship_to_org_id, g_rsh_row.ship_to_org_id) = g_rsh_row.ship_to_org_id
177 OR g_rsh_row.ship_to_org_id IS NULL)
178 AND ( NVL(p_vendor_id, g_rsh_row.vendor_id) = g_rsh_row.vendor_id
179 OR g_rsh_row.vendor_id IS NULL)
180 AND ( NVL(p_vendor_site_id, g_rsh_row.vendor_site_id) = g_rsh_row.vendor_site_id
181 OR g_rsh_row.vendor_site_id IS NULL)
182 AND ( TRUNC(NVL(p_shipped_date, g_rsh_row.shipped_date)) = TRUNC(g_rsh_row.shipped_date)
183 OR g_rsh_row.shipped_date IS NULL) THEN
184 RETURN g_rsh_row;
185 END IF;
186
187 SELECT *
188 INTO g_rsh_row
189 FROM rcv_shipment_headers
190 WHERE shipment_num = p_shipment_num
191 AND ( vendor_site_id = NVL(p_vendor_site_id, vendor_site_id)
192 OR vendor_site_id IS NULL)
193 AND ( vendor_id = NVL(p_vendor_id, vendor_id)
194 OR vendor_id IS NULL)
195 AND ship_to_org_id = NVL(p_ship_to_org_id, ship_to_org_id)
196 AND shipped_date >= ADD_MONTHS(NVL(p_shipped_date, SYSDATE), -12);
197
198 RETURN g_rsh_row;
199 EXCEPTION
200 WHEN NO_DATA_FOUND THEN
201 IF (no_data_found_is_error = TRUE) THEN
202 invalid_value(p_shipment_num, 'SHIPMENT_NUM');
203 ELSE
204 RETURN NULL;
205 END IF;
206 WHEN TOO_MANY_ROWS THEN
207 IF (g_debug_flag = 'Y') THEN
208 asn_debug.put_line('too many rows in get_rsh_row_from_num(' || p_shipment_num || ',' || p_vendor_id || ');');
209 END IF;
210
211 RETURN NULL;
212 END get_rsh_row_from_num;
213
214 FUNCTION get_rsh_row_from_id(
215 p_shipment_header_id IN rcv_shipment_headers.shipment_header_id%TYPE
216 )
217 RETURN rcv_shipment_headers%ROWTYPE IS
218 BEGIN
219 IF (p_shipment_header_id IS NULL) THEN
220 RETURN NULL;
221 END IF;
222
223 IF (p_shipment_header_id = g_rsh_row.shipment_header_id) THEN
224 RETURN g_rsh_row;
225 END IF;
226
227 SELECT *
228 INTO g_rsh_row
229 FROM rcv_shipment_headers
230 WHERE shipment_header_id = p_shipment_header_id;
231
232 RETURN g_rsh_row;
233 EXCEPTION
234 WHEN OTHERS THEN
235 invalid_value(p_shipment_header_id, 'SHIPMENT_HEADER_ID');
236 END get_rsh_row_from_id;
237
238 /*******/
239 /* RSL */
240 /*******/
241 FUNCTION get_rsl_row_from_num(
242 p_line_num rcv_shipment_lines.line_num%TYPE,
243 p_shipment_header_id rcv_shipment_lines.shipment_header_id%TYPE
244 )
245 RETURN rcv_shipment_lines%ROWTYPE IS
246 BEGIN
247 IF ( p_line_num IS NULL
248 OR p_shipment_header_id IS NULL) THEN
249 RETURN NULL;
250 END IF;
251
252 IF ( p_line_num = g_rsl_row.line_num
253 AND p_shipment_header_id = g_rsl_row.shipment_header_id) THEN
254 RETURN g_rsl_row;
255 END IF;
256
257 SELECT *
258 INTO g_rsl_row
259 FROM rcv_shipment_lines
260 WHERE line_num = p_line_num
261 AND shipment_header_id = p_shipment_header_id;
262
263 RETURN g_rsl_row;
264 EXCEPTION
265 WHEN NO_DATA_FOUND THEN
266 invalid_value(p_line_num, 'LINE_NUM');
267 WHEN TOO_MANY_ROWS THEN
268 IF (g_debug_flag = 'Y') THEN
269 asn_debug.put_line('too many rows in get_rsl_row_from_num(' || p_line_num || ',' || p_shipment_header_id || ');');
270 END IF;
271
272 RETURN NULL;
273 END get_rsl_row_from_num;
274
275 FUNCTION get_rsl_row_from_id(
276 p_shipment_line_id IN rcv_shipment_lines.shipment_line_id%TYPE
277 )
278 RETURN rcv_shipment_lines%ROWTYPE IS
279 BEGIN
280 IF (p_shipment_line_id IS NULL) THEN
281 RETURN NULL;
282 END IF;
283
284 IF (p_shipment_line_id = g_rsl_row.shipment_line_id) THEN
285 RETURN g_rsl_row;
286 END IF;
287
288 SELECT *
289 INTO g_rsl_row
290 FROM rcv_shipment_lines
291 WHERE shipment_line_id = p_shipment_line_id;
292
293 RETURN g_rsl_row;
294 EXCEPTION
295 WHEN OTHERS THEN
296 invalid_value(p_shipment_line_id, 'SHIPMENT_LINE_ID');
297 END get_rsl_row_from_id;
298
299 PROCEDURE update_rsl_row(
300 p_rsl_row IN rcv_shipment_lines%ROWTYPE
301 ) IS
302 BEGIN
303 asn_debug.put_line('Updating RSL SHIPMENT_LINE_ID=' || p_rsl_row.shipment_line_id);
304
305 UPDATE rcv_shipment_lines
306 SET ROW = p_rsl_row
307 WHERE shipment_line_id = p_rsl_row.shipment_line_id;
308
309 g_rsl_row := p_rsl_row;
310 END;
311
312 /*******/
313 /* poh */
314 /*******/
315 FUNCTION get_poh_row_from_num(
316 p_po_num po_headers_all.segment1%TYPE,
317 p_org_id po_headers_all.org_id%TYPE
318 )
319 RETURN po_headers_all%ROWTYPE IS
320 BEGIN
321 IF (p_po_num IS NULL) THEN
322 RETURN NULL;
323 END IF;
324
325 IF (p_po_num = g_poh_row.segment1) THEN
326 RETURN g_poh_row;
327 END IF;
328
329 --first check if there is a singular match in the current OU
330 SELECT *
331 INTO g_poh_row
332 FROM po_headers_all
333 WHERE po_header_id IN(SELECT po_header_id
334 FROM po_headers
335 WHERE segment1 = p_po_num
336 AND type_lookup_code IN('STANDARD', 'BLANKET', 'SCHEDULED')
337 AND org_id = NVL(p_org_id, org_id));
338
339 RETURN g_poh_row;
340 EXCEPTION
341 WHEN NO_DATA_FOUND THEN --check if this exists in a different OU
342 BEGIN
343 SELECT *
344 INTO g_poh_row
345 FROM po_headers_all
346 WHERE segment1 = p_po_num
347 AND type_lookup_code IN('STANDARD', 'BLANKET', 'SCHEDULED')
348 AND org_id = NVL(p_org_id, org_id);
349
350 RETURN g_poh_row;
351 EXCEPTION
352 WHEN NO_DATA_FOUND THEN
353 invalid_value(p_po_num, 'PO_HEADER_NUM');
354 WHEN TOO_MANY_ROWS THEN
355 IF (g_debug_flag = 'Y') THEN
356 asn_debug.put_line('too many rows in get_poh_row_from_num(' || p_po_num || ',' || p_org_id || ');');
357 END IF;
358
359 RETURN NULL;
360 END;
361 WHEN TOO_MANY_ROWS THEN
362 IF (g_default_org_id IS NOT NULL) THEN
363 BEGIN
364 SELECT *
365 INTO g_poh_row
366 FROM po_headers_all
367 WHERE segment1 = p_po_num
368 AND type_lookup_code IN('STANDARD', 'BLANKET', 'SCHEDULED')
369 AND org_id = g_default_org_id;
370
371 RETURN g_poh_row;
372 EXCEPTION
373 WHEN OTHERS THEN
374 NULL;
375 END;
376 END IF;
377
378 IF (g_debug_flag = 'Y') THEN
379 asn_debug.put_line('too many rows in get_poh_row_from_num(' || p_po_num || ',' || p_org_id || ');');
380 END IF;
381
382 RETURN NULL;
383 END get_poh_row_from_num;
384
385 FUNCTION get_poh_row_from_id(
386 p_header_id IN po_headers_all.po_header_id%TYPE
387 )
388 RETURN po_headers_all%ROWTYPE IS
389 BEGIN
390 IF (p_header_id IS NULL) THEN
391 RETURN NULL;
392 END IF;
393
394 IF (p_header_id = g_poh_row.po_header_id) THEN
395 RETURN g_poh_row;
396 END IF;
397
398 SELECT *
399 INTO g_poh_row
400 FROM po_headers_all
401 WHERE po_header_id = p_header_id;
402
403 RETURN g_poh_row;
404 EXCEPTION
405 WHEN OTHERS THEN
406 invalid_value(p_header_id, 'PO_HEADER_ID');
407 END get_poh_row_from_id;
408
409 /*******/
410 /* pol */
411 /*******/
412 FUNCTION get_pol_row_from_num(
413 p_line_num po_lines_all.line_num%TYPE,
414 p_header_id po_lines_all.po_header_id%TYPE,
415 p_item_description po_lines_all.item_description%TYPE,
416 p_vendor_product_num po_lines_all.vendor_product_num%TYPE,
417 p_item_id po_lines_all.item_id%TYPE
418 )
419 RETURN po_lines_all%ROWTYPE IS
420 x_line_num po_lines_all.line_num%TYPE;
421 x_item_id po_lines_all.item_id%TYPE;
422 x_item_description po_lines_all.item_description%TYPE;
423 x_vendor_product_num po_lines_all.vendor_product_num%TYPE;
424 --Bug 7645326 Added the item_id to find the po line num along with the existing
425 -- input parameters and modified the statement accordingly.
426 BEGIN
427 IF ( ( p_line_num IS NULL
428 AND p_item_id IS NULL
429 AND p_item_description IS NULL
430 AND p_vendor_product_num IS NULL)
431 OR p_header_id IS NULL) THEN
432 RETURN NULL;
433 END IF;
434
435 IF ( p_line_num = g_pol_row.line_num
436 AND p_header_id = g_pol_row.po_header_id) THEN
437 RETURN g_pol_row;
438 END IF;
439
440 IF (p_line_num IS NOT NULL) THEN
441 x_line_num := p_line_num;
442 x_item_id :=NULL;
443 x_item_description := NULL;
444 x_vendor_product_num := NULL;
445 ELSIF (p_item_id IS NOT NULL ) THEN
446 x_item_id :=p_item_id;
447 x_line_num :=NULL;
448 x_item_description := NULL;
449 x_vendor_product_num := NULL;
450 ELSIF(p_item_description IS NOT NULL) THEN
451 x_line_num := NULL;
452 x_item_id := NULL;
453 x_item_description := p_item_description;
454 x_vendor_product_num := NULL;
455 ELSIF(p_vendor_product_num IS NOT NULL) THEN
456 x_line_num := NULL;
457 x_item_id := NULL;
458 x_item_description := NULL;
459 x_vendor_product_num := p_vendor_product_num;
460 ELSE
461 x_line_num := 1;
462 x_item_id := NULL;
463 x_item_description := NULL;
464 x_vendor_product_num := NULL;
465 END IF;
466
467 --first check if there is a singular match in the current OU
468 SELECT *
469 INTO g_pol_row
470 FROM po_lines_all
471 WHERE po_line_id IN(SELECT po_line_id
472 FROM po_lines
473 WHERE po_header_id = p_header_id
474 AND ( line_num = x_line_num
475 OR x_line_num IS NULL)
476 AND ( item_id = x_item_id
477 OR x_item_id IS NULL )
478 AND ( item_description = x_item_description
479 OR x_item_description IS NULL)
480 AND ( vendor_product_num = x_vendor_product_num
481 OR x_vendor_product_num IS NULL));
482
483 RETURN g_pol_row;
484 EXCEPTION
485 WHEN NO_DATA_FOUND THEN --check if this exists in a different OU
486 BEGIN
487 SELECT *
488 INTO g_pol_row
489 FROM po_lines_all
490 WHERE po_header_id = p_header_id
491 AND ( line_num = x_line_num
492 OR x_line_num IS NULL)
493 AND ( item_id = x_item_id
494 OR x_item_id IS NULL )
495 AND ( item_description = x_item_description
496 OR x_item_description IS NULL)
497 AND ( vendor_product_num = x_vendor_product_num
498 OR x_vendor_product_num IS NULL);
499
500 RETURN g_pol_row;
501 EXCEPTION
502 WHEN NO_DATA_FOUND THEN
503 invalid_value(p_line_num, 'PO_LINE_NUM');
504 WHEN TOO_MANY_ROWS THEN
505 IF (g_debug_flag = 'Y') THEN
506 asn_debug.put_line('too many rows in get_pol_row_from_num(' || p_line_num || ',' || p_header_id || ');');
507 END IF;
508
509 RETURN NULL;
510 END;
511 WHEN TOO_MANY_ROWS THEN
512 IF (g_debug_flag = 'Y') THEN
513 asn_debug.put_line('too many rows in get_pol_row_from_num(' || p_line_num || ',' || p_header_id || ');');
514 END IF;
515
516 RETURN NULL;
517 END get_pol_row_from_num;
518
519 FUNCTION get_pol_row_from_id(
520 p_line_id IN po_lines_all.po_line_id%TYPE
521 )
522 RETURN po_lines_all%ROWTYPE IS
523 BEGIN
524 IF (p_line_id IS NULL) THEN
525 RETURN NULL;
526 END IF;
527
528 IF (p_line_id = g_pol_row.po_line_id) THEN
529 RETURN g_pol_row;
530 END IF;
531
532 SELECT *
533 INTO g_pol_row
534 FROM po_lines_all
535 WHERE po_line_id = p_line_id;
536
537 RETURN g_pol_row;
538 EXCEPTION
539 WHEN OTHERS THEN
540 invalid_value(p_line_id, 'PO_LINE_ID');
541 END get_pol_row_from_id;
542
543 /*******/
544 /* pll */
545 /*******/
546 FUNCTION get_pll_row_from_num(
547 p_shipment_num po_line_locations_all.shipment_num%TYPE,
548 p_line_id po_line_locations_all.po_line_id%TYPE
549 )
550 RETURN po_line_locations_all%ROWTYPE IS
551 BEGIN
552 IF ( p_shipment_num IS NULL
553 OR p_line_id IS NULL) THEN
554 RETURN NULL;
555 END IF;
556
557 IF ( p_shipment_num = g_pll_row.shipment_num
558 AND p_line_id = g_pll_row.po_line_id) THEN
559 RETURN g_pll_row;
560 END IF;
561
562 --first check if there is a singular match in the current OU
563 SELECT *
564 INTO g_pll_row
565 FROM po_line_locations_all
566 WHERE line_location_id IN(SELECT line_location_id
567 FROM po_line_locations
568 WHERE shipment_num = p_shipment_num
569 AND po_line_id = p_line_id);
570
571 RETURN g_pll_row;
572 EXCEPTION
573 WHEN NO_DATA_FOUND THEN --check if this exists in a different OU
574 BEGIN
575 SELECT *
576 INTO g_pll_row
577 FROM po_line_locations_all
578 WHERE shipment_num = p_shipment_num
579 AND po_line_id = p_line_id;
580
581 RETURN g_pll_row;
582 EXCEPTION
583 WHEN NO_DATA_FOUND THEN
584 invalid_value(p_shipment_num, 'PO_LINE_LOCATION_NUM');
585 WHEN TOO_MANY_ROWS THEN
586 IF (g_debug_flag = 'Y') THEN
587 asn_debug.put_line('too many rows in get_pll_row_from_num(' || p_shipment_num || ',' || p_line_id || ');');
588 END IF;
589
590 RETURN NULL;
591 END;
592 WHEN TOO_MANY_ROWS THEN
593 IF (g_debug_flag = 'Y') THEN
594 asn_debug.put_line('too many rows in get_pll_row_from_num(' || p_shipment_num || ',' || p_line_id || ');');
595 END IF;
596
597 RETURN NULL;
598 END get_pll_row_from_num;
599
600 FUNCTION get_pll_row_from_id(
601 p_line_location_id IN po_line_locations_all.line_location_id%TYPE
602 )
603 RETURN po_line_locations_all%ROWTYPE IS
604 BEGIN
605 IF (p_line_location_id IS NULL) THEN
606 RETURN NULL;
607 END IF;
608
609 IF (p_line_location_id = g_pll_row.line_location_id) THEN
610 RETURN g_pll_row;
611 END IF;
612
613 SELECT *
614 INTO g_pll_row
615 FROM po_line_locations_all
616 WHERE line_location_id = p_line_location_id;
617
618 RETURN g_pll_row;
619 EXCEPTION
620 WHEN OTHERS THEN
621 invalid_value(p_line_location_id, 'PO_LINE_LOCATION_ID');
622 END get_pll_row_from_id;
623
624 /*******/
625 /* POD */
626 /*******/
627 FUNCTION get_pod_row_from_num(
628 p_distribution_num po_distributions_all.distribution_num%TYPE,
629 p_line_location_id po_distributions_all.line_location_id%TYPE
630 )
631 RETURN po_distributions_all%ROWTYPE IS
632 BEGIN
633 IF ( p_distribution_num IS NULL
634 OR p_line_location_id IS NULL) THEN
635 RETURN NULL;
636 END IF;
637
638 IF ( p_distribution_num = g_pod_row.distribution_num
639 AND p_line_location_id = g_pod_row.line_location_id) THEN
640 RETURN g_pod_row;
641 END IF;
642
643 --first check if there is a singular match in the current OU
644 SELECT *
645 INTO g_pod_row
646 FROM po_distributions_all
647 WHERE po_distribution_id IN(SELECT po_distribution_id
648 FROM po_distributions
649 WHERE distribution_num = p_distribution_num
650 AND line_location_id = p_line_location_id);
651
652 RETURN g_pod_row;
653 EXCEPTION
654 WHEN NO_DATA_FOUND THEN --check if this exists in a different OU
655 BEGIN
656 SELECT *
657 INTO g_pod_row
658 FROM po_distributions_all
659 WHERE distribution_num = p_distribution_num
660 AND line_location_id = p_line_location_id;
661
662 RETURN g_pod_row;
663 EXCEPTION
664 WHEN NO_DATA_FOUND THEN
665 invalid_value(p_distribution_num, 'PO_DISTRIBUTION_NUM');
666 WHEN TOO_MANY_ROWS THEN
667 IF (g_debug_flag = 'Y') THEN
668 asn_debug.put_line('too many rows in get_pod_row_from_num(' || p_distribution_num || ',' || p_line_location_id || ');');
669 END IF;
670
671 RETURN NULL;
672 END;
673 WHEN TOO_MANY_ROWS THEN
674 IF (g_debug_flag = 'Y') THEN
675 asn_debug.put_line('too many rows in get_pod_row_from_num(' || p_distribution_num || ',' || p_line_location_id || ');');
676 END IF;
677
678 RETURN NULL;
679 END get_pod_row_from_num;
680
681 FUNCTION get_pod_row_from_id(
682 p_distribution_id IN po_distributions_all.po_distribution_id%TYPE
683 )
684 RETURN po_distributions_all%ROWTYPE IS
685 BEGIN
686 IF (p_distribution_id IS NULL) THEN
687 RETURN NULL;
688 END IF;
689
690 IF (p_distribution_id = g_pod_row.po_distribution_id) THEN
691 RETURN g_pod_row;
692 END IF;
693
694 SELECT *
695 INTO g_pod_row
696 FROM po_distributions_all
697 WHERE po_distribution_id = p_distribution_id;
698
699 RETURN g_pod_row;
700 EXCEPTION
701 WHEN OTHERS THEN
702 invalid_value(p_distribution_id, 'PO_DISTRIBUTION_ID');
703 END get_pod_row_from_id;
704
705 /*******/
706 /* oeh */
707 /*******/
708 FUNCTION get_oeh_row_from_num(
709 p_order_number oe_order_headers_all.order_number%TYPE,
710 p_order_type_id oe_order_headers_all.order_type_id%TYPE,
711 p_version_number oe_order_headers_all.version_number%TYPE,
712 p_org_id oe_order_headers_all.org_id%TYPE
713 )
714 RETURN oe_order_headers_all%ROWTYPE IS
715 BEGIN
716 IF (p_order_number IS NULL) THEN
717 RETURN NULL;
718 END IF;
719
720 IF ( p_order_number = g_oeh_row.order_number
721 AND ( p_order_type_id IS NULL
722 OR p_order_type_id = g_oeh_row.order_type_id)
723 AND ( p_version_number IS NULL
724 OR p_version_number = g_oeh_row.version_number)) THEN
725 RETURN g_oeh_row;
726 END IF;
727
728 --first check if there is a singular match in the current OU
729 /* WDK: NOTE - THIS QUERY IS NOT UNIQUE!!! REQUIRES ORDER_TYPE_ID and VERSION_NUMBER.
730 WE CAN FIX ONLY IF WE ADD THESE COLUMNS */
731 SELECT *
732 INTO g_oeh_row
733 FROM oe_order_headers_all
734 WHERE header_id IN(SELECT header_id
735 FROM oe_order_headers
736 WHERE order_number = p_order_number
737 AND order_type_id = NVL(p_order_type_id, order_type_id)
738 AND version_number = NVL(p_version_number, version_number)
739 AND org_id = NVL(p_org_id, org_id))
740 AND ROWNUM = 1
741 ORDER BY version_number DESC;
742
743 RETURN g_oeh_row;
744 EXCEPTION
745 WHEN NO_DATA_FOUND THEN --check if this exists in a different OU
746 BEGIN
747 SELECT *
748 INTO g_oeh_row
749 FROM oe_order_headers_all
750 WHERE order_number = p_order_number
751 AND order_type_id = NVL(p_order_type_id, order_type_id)
752 AND version_number = NVL(p_version_number, version_number)
753 AND org_id = NVL(p_org_id, org_id)
754 AND ROWNUM = 1
755 ORDER BY version_number DESC;
756
757 RETURN g_oeh_row;
758 EXCEPTION
759 WHEN NO_DATA_FOUND THEN
760 invalid_value(p_order_number, 'OE_ORDER_HEADER_NUM');
761 WHEN TOO_MANY_ROWS THEN
762 IF (g_debug_flag = 'Y') THEN
763 asn_debug.put_line('too many rows in get_oeh_row_from_num(' || p_order_number || ',' || p_order_type_id || ');');
764 END IF;
765
766 RETURN NULL;
767 END;
768 WHEN TOO_MANY_ROWS THEN
769 IF (g_default_org_id IS NOT NULL) THEN
770 BEGIN
771 SELECT *
772 INTO g_oeh_row
773 FROM oe_order_headers_all
774 WHERE order_number = p_order_number
775 AND order_type_id = NVL(p_order_type_id, order_type_id)
776 AND version_number = NVL(p_version_number, version_number)
777 AND org_id = g_default_org_id
778 AND ROWNUM = 1
779 ORDER BY version_number DESC;
780
781 RETURN g_oeh_row;
782 EXCEPTION
783 WHEN OTHERS THEN
784 NULL;
785 END;
786 END IF;
787
788 IF (g_debug_flag = 'Y') THEN
789 asn_debug.put_line('too many rows in get_oeh_row_from_num(' || p_order_number || ',' || p_order_type_id || ');');
790 END IF;
791
792 RETURN NULL;
793 END get_oeh_row_from_num;
794
795 FUNCTION get_oeh_row_from_id(
796 p_header_id IN oe_order_headers_all.header_id%TYPE
797 )
798 RETURN oe_order_headers_all%ROWTYPE IS
799 BEGIN
800 IF (p_header_id IS NULL) THEN
801 RETURN NULL;
802 END IF;
803
804 IF (p_header_id = g_oeh_row.header_id) THEN
805 RETURN g_oeh_row;
806 END IF;
807
808 SELECT *
809 INTO g_oeh_row
810 FROM oe_order_headers_all
811 WHERE header_id = p_header_id;
812
813 RETURN g_oeh_row;
814 EXCEPTION
815 WHEN OTHERS THEN
816 invalid_value(p_header_id, 'OE_ORDER_HEADER_ID');
817 END get_oeh_row_from_id;
818
819 /*******/
820 /* oel */
821 /*******/
822 FUNCTION get_oel_row_from_num(
823 p_line_number oe_order_lines_all.line_number%TYPE,
824 p_header_id oe_order_lines_all.header_id%TYPE
825 )
826 RETURN oe_order_lines_all%ROWTYPE IS
827 BEGIN
828 IF ( p_line_number IS NULL
829 OR p_header_id IS NULL) THEN
830 RETURN NULL;
831 END IF;
832
833 IF ( p_line_number = g_oel_row.line_number
834 AND p_header_id = g_oel_row.header_id) THEN
835 RETURN g_oel_row;
836 END IF;
837
838 --first check if there is a singular match in the current OU
839 SELECT *
840 INTO g_oel_row
841 FROM oe_order_lines_all
842 WHERE line_id IN(SELECT line_id
843 FROM oe_order_lines
844 WHERE line_number = p_line_number
845 AND header_id = p_header_id
846 AND flow_status_code = 'AWAITING_RETURN');
847
848 RETURN g_oel_row;
849 EXCEPTION
850 WHEN NO_DATA_FOUND THEN --check if this exists in a different OU
851 BEGIN
852 SELECT *
853 INTO g_oel_row
854 FROM oe_order_lines_all
855 WHERE line_number = p_line_number
856 AND header_id = p_header_id
857 AND flow_status_code = 'AWAITING_RETURN';
858
859 RETURN g_oel_row;
860 EXCEPTION
861 WHEN NO_DATA_FOUND THEN
862 invalid_value(p_line_number, 'OE_ORDER_LINE_NUM');
863 WHEN TOO_MANY_ROWS THEN
864 IF (g_debug_flag = 'Y') THEN
865 asn_debug.put_line('too many rows in get_oel_row_from_num(' || p_line_number || ',' || p_header_id || ');');
866 END IF;
867
868 RETURN NULL;
869 END;
870 WHEN TOO_MANY_ROWS THEN
871 IF (g_debug_flag = 'Y') THEN
872 asn_debug.put_line('too many rows in get_oel_row_from_num(' || p_line_number || ',' || p_header_id || ');');
873 END IF;
874
875 RETURN NULL;
876 END get_oel_row_from_num;
877
878 FUNCTION get_oel_row_from_id(
879 p_line_id IN oe_order_lines_all.line_id%TYPE
880 )
881 RETURN oe_order_lines_all%ROWTYPE IS
882 BEGIN
883 IF (p_line_id IS NULL) THEN
884 RETURN NULL;
885 END IF;
886
887 IF (p_line_id = g_oel_row.line_id) THEN
888 RETURN g_oel_row;
889 END IF;
890
891 SELECT *
892 INTO g_oel_row
893 FROM oe_order_lines_all
894 WHERE line_id = p_line_id;
895
896 RETURN g_oel_row;
897 EXCEPTION
898 WHEN OTHERS THEN
899 invalid_value(p_line_id, 'OE_ORDER_LINE_ID');
900 END get_oel_row_from_id;
901
902 FUNCTION get_prl_row_from_id(
903 p_requisition_line_id IN po_requisition_lines_all.requisition_line_id%TYPE
904 )
905 RETURN po_requisition_lines_all%ROWTYPE IS
906 BEGIN
907 IF (p_requisition_line_id IS NULL) THEN
908 RETURN NULL;
909 END IF;
910
911 IF (p_requisition_line_id = g_prl_row.requisition_line_id) THEN
912 RETURN g_prl_row;
913 END IF;
914
915 SELECT *
916 INTO g_prl_row
917 FROM po_requisition_lines_all
918 WHERE requisition_line_id = p_requisition_line_id;
919
920 RETURN g_prl_row;
921 EXCEPTION
922 WHEN OTHERS THEN
923 invalid_value(p_requisition_line_id, 'REQUISITION_LINE_ID');
924 END;
925
926 FUNCTION get_prd_row_from_id(
927 p_req_distribution_id IN po_req_distributions_all.distribution_id%TYPE
928 )
929 RETURN po_req_distributions_all%ROWTYPE IS
930 BEGIN
931 IF (p_req_distribution_id IS NULL) THEN
932 RETURN NULL;
933 END IF;
934
935 IF (p_req_distribution_id = g_prd_row.distribution_id) THEN
936 RETURN g_prd_row;
937 END IF;
938
939 SELECT *
940 INTO g_prd_row
941 FROM po_req_distributions_all
942 WHERE distribution_id = p_req_distribution_id;
943
944 RETURN g_prd_row;
945 EXCEPTION
946 WHEN OTHERS THEN
947 invalid_value(p_req_distribution_id, 'REQ_DISTRIBUTION_ID');
948 END;
949
950 FUNCTION get_msi_row_from_num
951 ( p_item_id IN mtl_system_items.inventory_item_id%TYPE
952 , p_org_id IN mtl_system_items.organization_id%TYPE
953 )
954 RETURN mtl_system_items%ROWTYPE IS
955 BEGIN
956 IF ( p_item_id IS NULL OR p_org_id IS NULL ) THEN
957 RETURN NULL;
958 END IF;
959
960 IF p_item_id = g_msi_row.inventory_item_id AND p_org_id = g_msi_row.organization_id THEN
961 RETURN g_msi_row;
962 END IF;
963
964 SELECT *
965 INTO g_msi_row
966 FROM mtl_system_items
967 WHERE inventory_item_id = p_item_id
968 AND organization_id = p_org_id;
969
970 RETURN g_msi_row;
971 EXCEPTION
972 WHEN OTHERS THEN
973 invalid_value('(' || p_item_id || ', ' || p_org_id || ')', '(ITEM_ID,TO_ORGANIZATION_ID)');
974 END get_msi_row_from_num;
975
976 FUNCTION get_mic_row_from_num
977 ( p_item_id IN mtl_item_categories.inventory_item_id%TYPE
978 , p_org_id IN mtl_item_categories.organization_id%TYPE
979 )
980 RETURN mtl_item_categories%ROWTYPE IS
981 BEGIN
982 IF ( p_item_id IS NULL OR p_org_id IS NULL ) THEN
983 RETURN NULL;
984 END IF;
985
986 IF p_item_id = g_mic_row.inventory_item_id AND p_org_id = g_mic_row.organization_id THEN
987 RETURN g_mic_row;
988 END IF;
989
990 SELECT *
991 INTO g_mic_row
992 FROM mtl_item_categories
993 WHERE inventory_item_id = p_item_id
994 AND organization_id = p_org_id;
995
996 RETURN g_mic_row;
997 EXCEPTION
998 WHEN OTHERS THEN
999 invalid_value(p_item_id, 'ITEM_ID');
1000 END get_mic_row_from_num;
1001
1002 FUNCTION get_mp_row_from_org( p_org_id IN mtl_parameters.organization_id%TYPE )
1003 RETURN mtl_parameters%ROWTYPE IS
1004 BEGIN
1005 IF ( p_org_id IS NULL ) THEN
1006 RETURN NULL;
1007 END IF;
1008
1009 IF p_org_id = g_mp_row.organization_id THEN
1010 RETURN g_mp_row;
1011 END IF;
1012
1013 SELECT *
1014 INTO g_mp_row
1015 FROM mtl_parameters
1016 WHERE organization_id = p_org_id
1017 AND rownum = 1;
1018
1019 RETURN g_mp_row;
1020 EXCEPTION
1021 WHEN OTHERS THEN
1022 invalid_value(p_org_id, 'ORG_ID');
1023 END get_mp_row_from_org;
1024
1025 FUNCTION get_rp_row_from_org( p_org_id IN rcv_parameters.organization_id%TYPE )
1026 RETURN rcv_parameters%ROWTYPE IS
1027 BEGIN
1028 IF ( p_org_id IS NULL ) THEN
1029 RETURN NULL;
1030 END IF;
1031
1032 IF p_org_id = g_rp_row.organization_id THEN
1033 RETURN g_rp_row;
1034 END IF;
1035
1036 SELECT *
1037 INTO g_rp_row
1038 FROM rcv_parameters
1039 WHERE organization_id = p_org_id
1040 AND rownum = 1;
1041
1042 RETURN g_rp_row;
1043 EXCEPTION
1044 WHEN OTHERS THEN
1045 invalid_value(p_org_id, 'ORG_ID');
1046 END get_rp_row_from_org;
1047
1048 FUNCTION get_fc_row_from_code( p_currency_code IN fnd_currencies.currency_code%TYPE )
1049 RETURN fnd_currencies%ROWTYPE IS
1050 BEGIN
1051 IF ( p_currency_code IS NULL ) THEN
1052 RETURN NULL;
1053 END IF;
1054
1055 IF p_currency_code = g_fc_row.currency_code THEN
1056 RETURN g_fc_row;
1057 END IF;
1058
1059 SELECT *
1060 INTO g_fc_row
1061 FROM fnd_currencies
1062 WHERE currency_code = p_currency_code;
1063
1064 RETURN g_fc_row;
1065 EXCEPTION
1066 WHEN OTHERS THEN
1067 invalid_value(p_currency_code, 'CURRENCY_CODE');
1068 END get_fc_row_from_code;
1069
1070 FUNCTION get_fsp_row
1071 RETURN financials_system_parameters%ROWTYPE IS
1072 BEGIN
1073 IF g_fsp_row.set_of_books_id IS NULL THEN
1074 SELECT *
1075 INTO g_fsp_row
1076 FROM financials_system_parameters;
1077 END IF;
1078
1079 RETURN g_fsp_row;
1080 END get_fsp_row;
1081
1082 FUNCTION get_pvs_row_from_id( p_vendor_site_id IN po_vendor_sites_all.vendor_site_id%TYPE )
1083 RETURN po_vendor_sites_all%ROWTYPE IS
1084 BEGIN
1085 IF p_vendor_site_id IS NULL THEN
1086 RETURN NULL;
1087 END IF;
1088
1089 IF g_pvs_row.vendor_site_id = p_vendor_site_id THEN
1090 RETURN g_pvs_row;
1091 END IF;
1092
1093 SELECT *
1094 INTO g_pvs_row
1095 FROM po_vendor_sites_all
1096 WHERE vendor_site_id = p_vendor_site_id;
1097
1098 RETURN g_pvs_row;
1099 END get_pvs_row_from_id;
1100
1101 FUNCTION get_muom_row_from_name( p_unit_of_measure IN mtl_units_of_measure.unit_of_measure%TYPE )
1102 RETURN mtl_units_of_measure%ROWTYPE IS
1103 BEGIN
1104 IF p_unit_of_measure IS NULL THEN
1105 RETURN NULL;
1106 END IF;
1107
1108 IF g_muom_row.unit_of_measure = p_unit_of_measure THEN
1109 RETURN g_muom_row;
1110 END IF;
1111
1112 SELECT *
1113 INTO g_muom_row
1114 FROM mtl_units_of_measure
1115 WHERE unit_of_measure = p_unit_of_measure;
1116
1117 RETURN g_muom_row;
1118 END get_muom_row_from_name;
1119
1120 FUNCTION get_po_lookup
1121 ( p_lookup_type IN po_lookup_codes.lookup_type%TYPE
1122 , p_lookup_code IN po_lookup_codes.lookup_code%TYPE
1123 ) RETURN po_lookup_codes.displayed_field%TYPE IS
1124 l_key VARCHAR2(80) := p_lookup_type || '-' || p_lookup_code;
1125 BEGIN
1126 IF NOT g_po_lookups.EXISTS(l_key) THEN
1127 SELECT displayed_field
1128 INTO g_po_lookups(l_key)
1129 FROM po_lookup_codes
1130 WHERE lookup_type = p_lookup_type
1131 AND lookup_code = p_lookup_code
1132 AND enabled_flag = 'Y';
1133 END IF;
1134
1135 RETURN g_po_lookups(l_key);
1136 EXCEPTION
1137 WHEN OTHERS THEN
1138 RETURN NULL;
1139 END get_po_lookup;
1140
1141 FUNCTION is_lcm_shipment (p_po_line_location_id IN NUMBER )
1142 RETURN VARCHAR2 IS
1143 BEGIN
1144 IF ( nvl(g_pll_id,-99) <> p_po_line_location_id) THEN
1145
1146 SELECT nvl(lcm_flag,'N')
1147 INTO g_lcm_flag
1148 FROM po_line_locations_all
1149 WHERE line_location_id = p_po_line_location_id;
1150
1151 g_pll_id := p_po_line_location_id;
1152 END IF;
1153
1154 RETURN g_lcm_flag;
1155 EXCEPTION
1156 WHEN OTHERS THEN
1157 RETURN 'N';
1158 END is_lcm_shipment;
1159
1160 FUNCTION is_lcm_org ( p_organization_id IN NUMBER )
1161 RETURN VARCHAR2 IS
1162 BEGIN
1163 IF ( nvl(g_mp_org_id,-99) <> p_organization_id) THEN
1164 SELECT nvl(lcm_enabled_flag,'N')
1165 INTO g_lcm_enabled_flag
1166 FROM mtl_parameters
1167 WHERE organization_id = p_organization_id;
1168
1169 g_mp_org_id := p_organization_id;
1170 END IF;
1171
1172 RETURN g_lcm_enabled_flag;
1173 EXCEPTION
1174 WHEN OTHERS THEN
1175 RETURN 'N';
1176 END is_lcm_org;
1177
1178 FUNCTION is_pre_rcv_org ( p_organization_id IN NUMBER )
1179 RETURN VARCHAR2 IS
1180 BEGIN
1181 IF ( nvl(g_rp_org_id,-99) <> p_organization_id) THEN
1182 SELECT nvl(pre_receive,'N')
1183 INTO g_pre_rcv_flag
1184 FROM rcv_parameters
1185 WHERE organization_id = p_organization_id;
1186
1187 g_rp_org_id := p_organization_id;
1188 END IF;
1189
1190 RETURN g_pre_rcv_flag;
1191 EXCEPTION
1192 WHEN OTHERS THEN
1193 RETURN 'N';
1194 END is_pre_rcv_org;
1195
1196 END rcv_table_functions;
[Home] [Help]