1 PACKAGE BODY rcv_table_functions AS
2 /* $Header: RCVTBFNB.pls 120.11.12020000.2 2012/07/10 09:28:38 ptkumar ship $*/
3 g_debug_flag CONSTANT VARCHAR2(1) := asn_debug.is_debug_on; -- Bug 9152790
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 p_receipt_source_code IN rcv_shipment_headers.receipt_source_code%TYPE,
168 no_data_found_is_error IN BOOLEAN
169 )
170 RETURN rcv_shipment_headers%ROWTYPE IS
171 BEGIN
172 IF (p_shipment_num IS NULL) THEN
173 RETURN NULL;
174 END IF;
175
176 IF (p_shipment_num = g_rsh_row.shipment_num)
177 AND ( NVL(p_ship_to_org_id, g_rsh_row.ship_to_org_id) = g_rsh_row.ship_to_org_id
178 OR g_rsh_row.ship_to_org_id IS NULL)
179 AND ( NVL(p_vendor_id, g_rsh_row.vendor_id) = g_rsh_row.vendor_id
180 OR g_rsh_row.vendor_id IS NULL)
181 AND ( NVL(p_vendor_site_id, g_rsh_row.vendor_site_id) = g_rsh_row.vendor_site_id
182 OR g_rsh_row.vendor_site_id IS NULL)
183 AND ( TRUNC(NVL(p_shipped_date, g_rsh_row.shipped_date)) = TRUNC(g_rsh_row.shipped_date)
184 OR g_rsh_row.shipped_date IS NULL)
185 AND ( p_receipt_source_code = g_rsh_row.receipt_source_code) THEN
186 RETURN g_rsh_row;
187 END IF;
188
189 SELECT *
190 INTO g_rsh_row
191 FROM rcv_shipment_headers
192 WHERE shipment_num = p_shipment_num
193 AND ( vendor_site_id = NVL(p_vendor_site_id, vendor_site_id)
194 OR vendor_site_id IS NULL)
195 AND ( vendor_id = NVL(p_vendor_id, vendor_id)
196 OR vendor_id IS NULL)
197 AND ship_to_org_id = NVL(p_ship_to_org_id, ship_to_org_id)
198 AND shipped_date >= ADD_MONTHS(NVL(p_shipped_date, SYSDATE), -12)
199 AND receipt_source_code=p_receipt_source_code;
200
201 RETURN g_rsh_row;
202 EXCEPTION
203 WHEN NO_DATA_FOUND THEN
204 IF (no_data_found_is_error = TRUE) THEN
205 invalid_value(p_shipment_num, 'SHIPMENT_NUM');
206 ELSE
207 RETURN NULL;
208 END IF;
209 WHEN TOO_MANY_ROWS THEN
210 IF (g_debug_flag = 'Y') THEN
211 asn_debug.put_line('too many rows in get_rsh_row_from_num(' || p_shipment_num || ',' || p_vendor_id || ');');
212 END IF;
213
214 RETURN NULL;
215 END get_rsh_row_from_num;
216
217 FUNCTION get_rsh_row_from_id(
218 p_shipment_header_id IN rcv_shipment_headers.shipment_header_id%TYPE
219 )
220 RETURN rcv_shipment_headers%ROWTYPE IS
221 BEGIN
222 IF (p_shipment_header_id IS NULL) THEN
223 RETURN NULL;
224 END IF;
225
226 IF (p_shipment_header_id = g_rsh_row.shipment_header_id) THEN
227 RETURN g_rsh_row;
228 END IF;
229
230 SELECT *
231 INTO g_rsh_row
232 FROM rcv_shipment_headers
233 WHERE shipment_header_id = p_shipment_header_id;
234
235 RETURN g_rsh_row;
236 EXCEPTION
237 WHEN OTHERS THEN
238 invalid_value(p_shipment_header_id, 'SHIPMENT_HEADER_ID');
239 END get_rsh_row_from_id;
240
241 /*******/
242 /* RSL */
243 /*******/
244 FUNCTION get_rsl_row_from_num(
245 p_line_num rcv_shipment_lines.line_num%TYPE,
246 p_shipment_header_id rcv_shipment_lines.shipment_header_id%TYPE
247 )
248 RETURN rcv_shipment_lines%ROWTYPE IS
249 BEGIN
250 IF ( p_line_num IS NULL
251 OR p_shipment_header_id IS NULL) THEN
252 RETURN NULL;
253 END IF;
254
255 IF ( p_line_num = g_rsl_row.line_num
256 AND p_shipment_header_id = g_rsl_row.shipment_header_id) THEN
257 RETURN g_rsl_row;
258 END IF;
259
260 SELECT *
261 INTO g_rsl_row
262 FROM rcv_shipment_lines
263 WHERE line_num = p_line_num
264 AND shipment_header_id = p_shipment_header_id;
265
266 RETURN g_rsl_row;
267 EXCEPTION
268 WHEN NO_DATA_FOUND THEN
269 invalid_value(p_line_num, 'LINE_NUM');
270 WHEN TOO_MANY_ROWS THEN
271 IF (g_debug_flag = 'Y') THEN
272 asn_debug.put_line('too many rows in get_rsl_row_from_num(' || p_line_num || ',' || p_shipment_header_id || ');');
273 END IF;
274
275 RETURN NULL;
276 END get_rsl_row_from_num;
277
278 FUNCTION get_rsl_row_from_id(
279 p_shipment_line_id IN rcv_shipment_lines.shipment_line_id%TYPE
280 )
281 RETURN rcv_shipment_lines%ROWTYPE IS
282 BEGIN
283 IF (p_shipment_line_id IS NULL) THEN
284 RETURN NULL;
285 END IF;
286
287 IF (p_shipment_line_id = g_rsl_row.shipment_line_id) THEN
288 RETURN g_rsl_row;
289 END IF;
290
291 SELECT *
292 INTO g_rsl_row
293 FROM rcv_shipment_lines
294 WHERE shipment_line_id = p_shipment_line_id;
295
296 RETURN g_rsl_row;
297 EXCEPTION
298 WHEN OTHERS THEN
299 invalid_value(p_shipment_line_id, 'SHIPMENT_LINE_ID');
300 END get_rsl_row_from_id;
301
302 PROCEDURE update_rsl_row(
303 p_rsl_row IN rcv_shipment_lines%ROWTYPE
304 ) IS
305 BEGIN
306 asn_debug.put_line('Updating RSL SHIPMENT_LINE_ID=' || p_rsl_row.shipment_line_id);
307
308 UPDATE rcv_shipment_lines
309 SET ROW = p_rsl_row
310 WHERE shipment_line_id = p_rsl_row.shipment_line_id;
311
312 g_rsl_row := p_rsl_row;
313 END;
314
315 /*******/
316 /* poh */
317 /*******/
318 FUNCTION get_poh_row_from_num(
319 p_po_num po_headers_all.segment1%TYPE,
320 p_org_id po_headers_all.org_id%TYPE
321 )
322 RETURN po_headers_all%ROWTYPE IS
323 BEGIN
324 IF (p_po_num IS NULL) THEN
325 RETURN NULL;
326 END IF;
327
328 IF (p_po_num = g_poh_row.segment1) THEN
329 RETURN g_poh_row;
330 END IF;
331
332 --first check if there is a singular match in the current OU
333 SELECT *
334 INTO g_poh_row
335 FROM po_headers_all
336 WHERE po_header_id IN(SELECT po_header_id
337 FROM po_headers
338 WHERE segment1 = p_po_num
339 -- Bug 13720644 Begin: type_lookup_code for Planned Purchase Order is 'PLANNED'
340 --AND type_lookup_code IN('STANDARD', 'BLANKET', 'SCHEDULED')
341 AND type_lookup_code IN('STANDARD', 'BLANKET', 'PLANNED')
342 --Bug 13720644 End
343 AND org_id = NVL(p_org_id, org_id));
344
345 RETURN g_poh_row;
346 EXCEPTION
347 WHEN NO_DATA_FOUND THEN --check if this exists in a different OU
348 BEGIN
349 SELECT *
350 INTO g_poh_row
351 FROM po_headers_all
352 WHERE segment1 = p_po_num
353 -- Bug 13720644: type_lookup_code for Planned Purchase Order is 'PLANNED'
354 --AND type_lookup_code IN('STANDARD', 'BLANKET', 'SCHEDULED')
355 AND type_lookup_code IN('STANDARD', 'BLANKET', 'PLANNED')
356 --Bug 13720644 End
357 AND org_id = NVL(p_org_id, org_id);
358
359 RETURN g_poh_row;
360 EXCEPTION
361 WHEN NO_DATA_FOUND THEN
362 invalid_value(p_po_num, 'PO_HEADER_NUM');
363 WHEN TOO_MANY_ROWS THEN
364 IF (g_debug_flag = 'Y') THEN
365 asn_debug.put_line('too many rows in get_poh_row_from_num(' || p_po_num || ',' || p_org_id || ');');
366 END IF;
367
368 RETURN NULL;
369 END;
370 WHEN TOO_MANY_ROWS THEN
371 IF (g_default_org_id IS NOT NULL) THEN
372 BEGIN
373 SELECT *
374 INTO g_poh_row
375 FROM po_headers_all
376 WHERE segment1 = p_po_num
377 -- Bug 13720644: type_lookup_code for Planned Purchase Order is 'PLANNED'
378 --AND type_lookup_code IN('STANDARD', 'BLANKET', 'SCHEDULED')
379 AND type_lookup_code IN('STANDARD', 'BLANKET', 'PLANNED')
380 --Bug 13720644 End
381 AND org_id = g_default_org_id;
382
383 RETURN g_poh_row;
384 EXCEPTION
385 WHEN OTHERS THEN
386 NULL;
387 END;
388 END IF;
389
390 IF (g_debug_flag = 'Y') THEN
391 asn_debug.put_line('too many rows in get_poh_row_from_num(' || p_po_num || ',' || p_org_id || ');');
392 END IF;
393
394 RETURN NULL;
395 END get_poh_row_from_num;
396
397 FUNCTION get_poh_row_from_id(
398 p_header_id IN po_headers_all.po_header_id%TYPE
399 )
400 RETURN po_headers_all%ROWTYPE IS
401 BEGIN
402 IF (p_header_id IS NULL) THEN
403 RETURN NULL;
404 END IF;
405
406 IF (p_header_id = g_poh_row.po_header_id) THEN
407 RETURN g_poh_row;
408 END IF;
409
410 SELECT *
411 INTO g_poh_row
412 FROM po_headers_all
413 WHERE po_header_id = p_header_id;
414
415 RETURN g_poh_row;
416 EXCEPTION
417 WHEN OTHERS THEN
418 invalid_value(p_header_id, 'PO_HEADER_ID');
419 END get_poh_row_from_id;
420
421 /*******/
422 /* pol */
423 /*******/
424 FUNCTION get_pol_row_from_num(
425 p_line_num po_lines_all.line_num%TYPE,
426 p_header_id po_lines_all.po_header_id%TYPE,
427 p_item_description po_lines_all.item_description%TYPE,
428 p_vendor_product_num po_lines_all.vendor_product_num%TYPE,
429 p_item_id po_lines_all.item_id%TYPE
430 )
431 RETURN po_lines_all%ROWTYPE IS
432 x_line_num po_lines_all.line_num%TYPE;
433 x_item_id po_lines_all.item_id%TYPE;
434 x_item_description po_lines_all.item_description%TYPE;
435 x_vendor_product_num po_lines_all.vendor_product_num%TYPE;
436 --Bug 7645326 Added the item_id to find the po line num along with the existing
437 -- input parameters and modified the statement accordingly.
438 BEGIN
439 IF ( ( p_line_num IS NULL
440 AND p_item_id IS NULL
441 AND p_item_description IS NULL
442 AND p_vendor_product_num IS NULL)
443 OR p_header_id IS NULL) THEN
444 RETURN NULL;
445 END IF;
446
447 IF ( p_line_num = g_pol_row.line_num
448 AND p_header_id = g_pol_row.po_header_id) THEN
449 RETURN g_pol_row;
450 END IF;
451
452 IF (p_line_num IS NOT NULL) THEN
453 x_line_num := p_line_num;
454 x_item_id :=NULL;
455 x_item_description := NULL;
456 x_vendor_product_num := NULL;
457 ELSIF (p_item_id IS NOT NULL ) THEN
458 x_item_id :=p_item_id;
459 x_line_num :=NULL;
460 x_item_description := NULL;
461 x_vendor_product_num := NULL;
462 ELSIF(p_item_description IS NOT NULL) THEN
463 x_line_num := NULL;
464 x_item_id := NULL;
465 x_item_description := p_item_description;
466 x_vendor_product_num := NULL;
467 ELSIF(p_vendor_product_num IS NOT NULL) THEN
468 x_line_num := NULL;
469 x_item_id := NULL;
470 x_item_description := NULL;
471 x_vendor_product_num := p_vendor_product_num;
472 ELSE
473 x_line_num := 1;
474 x_item_id := NULL;
475 x_item_description := NULL;
476 x_vendor_product_num := NULL;
477 END IF;
478
479 --first check if there is a singular match in the current OU
480 SELECT *
481 INTO g_pol_row
482 FROM po_lines_all
483 WHERE po_line_id IN(SELECT po_line_id
484 FROM po_lines
485 WHERE po_header_id = p_header_id
486 AND ( line_num = x_line_num
487 OR x_line_num IS NULL)
488 AND ( item_id = x_item_id
489 OR x_item_id IS NULL )
490 AND ( item_description = x_item_description
491 OR x_item_description IS NULL)
492 AND ( vendor_product_num = x_vendor_product_num
493 OR x_vendor_product_num IS NULL));
494
495 RETURN g_pol_row;
496 EXCEPTION
497 WHEN NO_DATA_FOUND THEN --check if this exists in a different OU
498 BEGIN
499 SELECT *
500 INTO g_pol_row
501 FROM po_lines_all
502 WHERE po_header_id = p_header_id
503 AND ( line_num = x_line_num
504 OR x_line_num IS NULL)
505 AND ( item_id = x_item_id
506 OR x_item_id IS NULL )
507 AND ( item_description = x_item_description
508 OR x_item_description IS NULL)
509 AND ( vendor_product_num = x_vendor_product_num
510 OR x_vendor_product_num IS NULL);
511
512 RETURN g_pol_row;
513 EXCEPTION
514 WHEN NO_DATA_FOUND THEN
515 invalid_value(p_line_num, 'PO_LINE_NUM');
516 WHEN TOO_MANY_ROWS THEN
517 IF (g_debug_flag = 'Y') THEN
518 asn_debug.put_line('too many rows in get_pol_row_from_num(' || p_line_num || ',' || p_header_id || ');');
519 END IF;
520
521 /*Bug 12618848 Do not error out the RTI if it fetches more than one PO line.
522 In preprocessor, it will be decided*/
523 --RETURN NULL;
524 g_pol_row.po_line_id := -99;
525 RETURN g_pol_row;
526 /*End of Bug 12618848 */
527 END;
528 WHEN TOO_MANY_ROWS THEN
529 IF (g_debug_flag = 'Y') THEN
530 asn_debug.put_line('too many rows in get_pol_row_from_num(' || p_line_num || ',' || p_header_id || ');');
531 END IF;
532
533 /*Bug 12618848 Do not error out the RTI if it fetches more than one PO line.
534 In preprocessor, it will be decided*/
535 --RETURN NULL;
536 g_pol_row.po_line_id := -99;
537 RETURN g_pol_row;
538 /*End of Bug 12618848 */
539
540 END get_pol_row_from_num;
541
542 FUNCTION get_pol_row_from_id(
543 p_line_id IN po_lines_all.po_line_id%TYPE
544 )
545 RETURN po_lines_all%ROWTYPE IS
546 BEGIN
547 IF (p_line_id IS NULL) THEN
548 RETURN NULL;
549 END IF;
550
551 IF (p_line_id = g_pol_row.po_line_id) THEN
552 RETURN g_pol_row;
553 END IF;
554
555 SELECT *
556 INTO g_pol_row
557 FROM po_lines_all
558 WHERE po_line_id = p_line_id;
559
560 RETURN g_pol_row;
561 EXCEPTION
562 WHEN OTHERS THEN
563 invalid_value(p_line_id, 'PO_LINE_ID');
564 END get_pol_row_from_id;
565
566 /*******/
567 /* pll */
568 /*******/
569 FUNCTION get_pll_row_from_num(
570 p_shipment_num po_line_locations_all.shipment_num%TYPE,
571 p_line_id po_line_locations_all.po_line_id%TYPE
572 )
573 RETURN po_line_locations_all%ROWTYPE IS
574 BEGIN
575 IF ( p_shipment_num IS NULL
576 OR p_line_id IS NULL) THEN
577 RETURN NULL;
578 END IF;
579
580 IF ( p_shipment_num = g_pll_row.shipment_num
581 AND p_line_id = g_pll_row.po_line_id) THEN
582 RETURN g_pll_row;
583 END IF;
584
585 --first check if there is a singular match in the current OU
586 SELECT *
587 INTO g_pll_row
588 FROM po_line_locations_all
589 WHERE line_location_id IN(SELECT line_location_id
590 FROM po_line_locations
591 WHERE shipment_num = p_shipment_num
592 AND po_line_id = p_line_id);
593
594 RETURN g_pll_row;
595 EXCEPTION
596 WHEN NO_DATA_FOUND THEN --check if this exists in a different OU
597 BEGIN
598 SELECT *
599 INTO g_pll_row
600 FROM po_line_locations_all
601 WHERE shipment_num = p_shipment_num
602 AND po_line_id = p_line_id;
603
604 RETURN g_pll_row;
605 EXCEPTION
606 WHEN NO_DATA_FOUND THEN
607 invalid_value(p_shipment_num, 'PO_LINE_LOCATION_NUM');
608 WHEN TOO_MANY_ROWS THEN
609 IF (g_debug_flag = 'Y') THEN
610 asn_debug.put_line('too many rows in get_pll_row_from_num(' || p_shipment_num || ',' || p_line_id || ');');
611 END IF;
612
613 g_pll_row := NULL; -- bug#12568219 null out g_pll_row otherwise it will be populated with value even for TOO_MANY_ROWS exception.
614
615 RETURN NULL;
616 END;
617 WHEN TOO_MANY_ROWS THEN
618 IF (g_debug_flag = 'Y') THEN
619 asn_debug.put_line('too many rows in get_pll_row_from_num(' || p_shipment_num || ',' || p_line_id || ');');
620 END IF;
621
622 g_pll_row := NULL; -- bug#12568219 null out g_pll_row otherwise it will be populated with value even for TOO_MANY_ROWS exception.
623
624 RETURN NULL;
625 END get_pll_row_from_num;
626
627 FUNCTION get_pll_row_from_id(
628 p_line_location_id IN po_line_locations_all.line_location_id%TYPE
629 )
630 RETURN po_line_locations_all%ROWTYPE IS
631 BEGIN
632 IF (p_line_location_id IS NULL) THEN
633 RETURN NULL;
634 END IF;
635
636 IF (p_line_location_id = g_pll_row.line_location_id) THEN
637 RETURN g_pll_row;
638 END IF;
639
640 SELECT *
641 INTO g_pll_row
642 FROM po_line_locations_all
643 WHERE line_location_id = p_line_location_id;
644
645 RETURN g_pll_row;
646 EXCEPTION
647 WHEN OTHERS THEN
648 invalid_value(p_line_location_id, 'PO_LINE_LOCATION_ID');
649 END get_pll_row_from_id;
650
651 /*******/
652 /* POD */
653 /*******/
654 FUNCTION get_pod_row_from_num(
655 p_distribution_num po_distributions_all.distribution_num%TYPE,
656 p_line_location_id po_distributions_all.line_location_id%TYPE
657 )
658 RETURN po_distributions_all%ROWTYPE IS
659 BEGIN
660 IF ( p_distribution_num IS NULL
661 OR p_line_location_id IS NULL) THEN
662 RETURN NULL;
663 END IF;
664
665 IF ( p_distribution_num = g_pod_row.distribution_num
666 AND p_line_location_id = g_pod_row.line_location_id) THEN
667 RETURN g_pod_row;
668 END IF;
669
670 --first check if there is a singular match in the current OU
671 SELECT *
672 INTO g_pod_row
673 FROM po_distributions_all
674 WHERE po_distribution_id IN(SELECT po_distribution_id
675 FROM po_distributions
676 WHERE distribution_num = p_distribution_num
677 AND line_location_id = p_line_location_id);
678
679 RETURN g_pod_row;
680 EXCEPTION
681 WHEN NO_DATA_FOUND THEN --check if this exists in a different OU
682 BEGIN
683 SELECT *
684 INTO g_pod_row
685 FROM po_distributions_all
686 WHERE distribution_num = p_distribution_num
687 AND line_location_id = p_line_location_id;
688
689 RETURN g_pod_row;
690 EXCEPTION
691 WHEN NO_DATA_FOUND THEN
692 invalid_value(p_distribution_num, 'PO_DISTRIBUTION_NUM');
693 WHEN TOO_MANY_ROWS THEN
694 IF (g_debug_flag = 'Y') THEN
695 asn_debug.put_line('too many rows in get_pod_row_from_num(' || p_distribution_num || ',' || p_line_location_id || ');');
696 END IF;
697
698 RETURN NULL;
699 END;
700 WHEN TOO_MANY_ROWS THEN
701 IF (g_debug_flag = 'Y') THEN
702 asn_debug.put_line('too many rows in get_pod_row_from_num(' || p_distribution_num || ',' || p_line_location_id || ');');
703 END IF;
704
705 RETURN NULL;
706 END get_pod_row_from_num;
707
708 FUNCTION get_pod_row_from_id(
709 p_distribution_id IN po_distributions_all.po_distribution_id%TYPE
710 )
711 RETURN po_distributions_all%ROWTYPE IS
712 BEGIN
713 IF (p_distribution_id IS NULL) THEN
714 RETURN NULL;
715 END IF;
716
717 IF (p_distribution_id = g_pod_row.po_distribution_id) THEN
718 RETURN g_pod_row;
719 END IF;
720
721 SELECT *
722 INTO g_pod_row
723 FROM po_distributions_all
724 WHERE po_distribution_id = p_distribution_id;
725
726 RETURN g_pod_row;
727 EXCEPTION
728 WHEN OTHERS THEN
729 invalid_value(p_distribution_id, 'PO_DISTRIBUTION_ID');
730 END get_pod_row_from_id;
731
732 /*******/
733 /* oeh */
734 /*******/
735 FUNCTION get_oeh_row_from_num(
736 p_order_number oe_order_headers_all.order_number%TYPE,
737 p_order_type_id oe_order_headers_all.order_type_id%TYPE,
738 p_version_number oe_order_headers_all.version_number%TYPE,
739 p_org_id oe_order_headers_all.org_id%TYPE
740 )
741 RETURN oe_order_headers_all%ROWTYPE IS
742 BEGIN
743 IF (p_order_number IS NULL) THEN
744 RETURN NULL;
745 END IF;
746
747 IF ( p_order_number = g_oeh_row.order_number
748 AND ( p_order_type_id IS NULL
749 OR p_order_type_id = g_oeh_row.order_type_id)
750 AND ( p_version_number IS NULL
751 OR p_version_number = g_oeh_row.version_number)) THEN
752 RETURN g_oeh_row;
753 END IF;
754
755 --first check if there is a singular match in the current OU
756 /* WDK: NOTE - THIS QUERY IS NOT UNIQUE!!! REQUIRES ORDER_TYPE_ID and VERSION_NUMBER.
757 WE CAN FIX ONLY IF WE ADD THESE COLUMNS */
758 SELECT *
759 INTO g_oeh_row
760 FROM oe_order_headers_all
761 WHERE header_id IN(SELECT header_id
762 FROM oe_order_headers
763 WHERE order_number = p_order_number
764 AND order_type_id = NVL(p_order_type_id, order_type_id)
765 AND version_number = NVL(p_version_number, version_number)
766 AND org_id = NVL(p_org_id, org_id))
767 AND ROWNUM = 1
768 ORDER BY version_number DESC;
769
770 RETURN g_oeh_row;
771 EXCEPTION
772 WHEN NO_DATA_FOUND THEN --check if this exists in a different OU
773 BEGIN
774 SELECT *
775 INTO g_oeh_row
776 FROM oe_order_headers_all
777 WHERE order_number = p_order_number
778 AND order_type_id = NVL(p_order_type_id, order_type_id)
779 AND version_number = NVL(p_version_number, version_number)
780 AND org_id = NVL(p_org_id, org_id)
781 AND ROWNUM = 1
782 ORDER BY version_number DESC;
783
784 RETURN g_oeh_row;
785 EXCEPTION
786 WHEN NO_DATA_FOUND THEN
787 invalid_value(p_order_number, 'OE_ORDER_HEADER_NUM');
788 WHEN TOO_MANY_ROWS THEN
789 IF (g_debug_flag = 'Y') THEN
790 asn_debug.put_line('too many rows in get_oeh_row_from_num(' || p_order_number || ',' || p_order_type_id || ');');
791 END IF;
792
793 RETURN NULL;
794 END;
795 WHEN TOO_MANY_ROWS THEN
796 IF (g_default_org_id IS NOT NULL) THEN
797 BEGIN
798 SELECT *
799 INTO g_oeh_row
800 FROM oe_order_headers_all
801 WHERE order_number = p_order_number
802 AND order_type_id = NVL(p_order_type_id, order_type_id)
803 AND version_number = NVL(p_version_number, version_number)
804 AND org_id = g_default_org_id
805 AND ROWNUM = 1
806 ORDER BY version_number DESC;
807
808 RETURN g_oeh_row;
809 EXCEPTION
810 WHEN OTHERS THEN
811 NULL;
812 END;
813 END IF;
814
815 IF (g_debug_flag = 'Y') THEN
816 asn_debug.put_line('too many rows in get_oeh_row_from_num(' || p_order_number || ',' || p_order_type_id || ');');
817 END IF;
818
819 RETURN NULL;
820 END get_oeh_row_from_num;
821
822 FUNCTION get_oeh_row_from_id(
823 p_header_id IN oe_order_headers_all.header_id%TYPE
824 )
825 RETURN oe_order_headers_all%ROWTYPE IS
826 BEGIN
827 IF (p_header_id IS NULL) THEN
828 RETURN NULL;
829 END IF;
830
831 IF (p_header_id = g_oeh_row.header_id) THEN
832 RETURN g_oeh_row;
833 END IF;
834
835 SELECT *
836 INTO g_oeh_row
837 FROM oe_order_headers_all
838 WHERE header_id = p_header_id;
839
840 RETURN g_oeh_row;
841 EXCEPTION
842 WHEN OTHERS THEN
843 invalid_value(p_header_id, 'OE_ORDER_HEADER_ID');
844 END get_oeh_row_from_id;
845
846 /*******/
847 /* oel */
848 /*******/
849 FUNCTION get_oel_row_from_num(
850 p_line_number oe_order_lines_all.line_number%TYPE,
851 p_header_id oe_order_lines_all.header_id%TYPE
852 )
853 RETURN oe_order_lines_all%ROWTYPE IS
854 BEGIN
855 IF ( p_line_number IS NULL
856 OR p_header_id IS NULL) THEN
857 RETURN NULL;
858 END IF;
859
860 IF ( p_line_number = g_oel_row.line_number
861 AND p_header_id = g_oel_row.header_id) THEN
862 RETURN g_oel_row;
863 END IF;
864
865 --first check if there is a singular match in the current OU
866 SELECT *
867 INTO g_oel_row
868 FROM oe_order_lines_all
869 WHERE line_id IN(SELECT line_id
870 FROM oe_order_lines
871 WHERE line_number = p_line_number
872 AND header_id = p_header_id
873 AND flow_status_code = 'AWAITING_RETURN');
874
875 RETURN g_oel_row;
876 EXCEPTION
877 WHEN NO_DATA_FOUND THEN --check if this exists in a different OU
878 BEGIN
879 SELECT *
880 INTO g_oel_row
881 FROM oe_order_lines_all
882 WHERE line_number = p_line_number
883 AND header_id = p_header_id
884 AND flow_status_code = 'AWAITING_RETURN';
885
886 RETURN g_oel_row;
887 EXCEPTION
888 WHEN NO_DATA_FOUND THEN
889 invalid_value(p_line_number, 'OE_ORDER_LINE_NUM');
890 WHEN TOO_MANY_ROWS THEN
891 IF (g_debug_flag = 'Y') THEN
892 asn_debug.put_line('too many rows in get_oel_row_from_num(' || p_line_number || ',' || p_header_id || ');');
893 END IF;
894
895 RETURN NULL;
896 END;
897 WHEN TOO_MANY_ROWS THEN
898 IF (g_debug_flag = 'Y') THEN
899 asn_debug.put_line('too many rows in get_oel_row_from_num(' || p_line_number || ',' || p_header_id || ');');
900 END IF;
901
902 RETURN NULL;
903 END get_oel_row_from_num;
904
905 FUNCTION get_oel_row_from_id(
906 p_line_id IN oe_order_lines_all.line_id%TYPE
907 )
908 RETURN oe_order_lines_all%ROWTYPE IS
909 BEGIN
910 IF (p_line_id IS NULL) THEN
911 RETURN NULL;
912 END IF;
913
914 IF (p_line_id = g_oel_row.line_id) THEN
915 RETURN g_oel_row;
916 END IF;
917
918 SELECT *
919 INTO g_oel_row
920 FROM oe_order_lines_all
921 WHERE line_id = p_line_id;
922
923 RETURN g_oel_row;
924 EXCEPTION
925 WHEN OTHERS THEN
926 invalid_value(p_line_id, 'OE_ORDER_LINE_ID');
927 END get_oel_row_from_id;
928
929 FUNCTION get_prl_row_from_id(
930 p_requisition_line_id IN po_requisition_lines_all.requisition_line_id%TYPE
931 )
932 RETURN po_requisition_lines_all%ROWTYPE IS
933 BEGIN
934 IF (p_requisition_line_id IS NULL) THEN
935 RETURN NULL;
936 END IF;
937
938 IF (p_requisition_line_id = g_prl_row.requisition_line_id) THEN
939 RETURN g_prl_row;
940 END IF;
941
942 SELECT *
943 INTO g_prl_row
944 FROM po_requisition_lines_all
945 WHERE requisition_line_id = p_requisition_line_id;
946
947 RETURN g_prl_row;
948 EXCEPTION
949 WHEN OTHERS THEN
950 invalid_value(p_requisition_line_id, 'REQUISITION_LINE_ID');
951 END;
952
953 FUNCTION get_prd_row_from_id(
954 p_req_distribution_id IN po_req_distributions_all.distribution_id%TYPE
955 )
956 RETURN po_req_distributions_all%ROWTYPE IS
957 BEGIN
958 IF (p_req_distribution_id IS NULL) THEN
959 RETURN NULL;
960 END IF;
961
962 IF (p_req_distribution_id = g_prd_row.distribution_id) THEN
963 RETURN g_prd_row;
964 END IF;
965
966 SELECT *
967 INTO g_prd_row
968 FROM po_req_distributions_all
969 WHERE distribution_id = p_req_distribution_id;
970
971 RETURN g_prd_row;
972 EXCEPTION
973 WHEN OTHERS THEN
974 invalid_value(p_req_distribution_id, 'REQ_DISTRIBUTION_ID');
975 END;
976
977 FUNCTION get_msi_row_from_num
978 ( p_item_id IN mtl_system_items.inventory_item_id%TYPE
979 , p_org_id IN mtl_system_items.organization_id%TYPE
980 )
981 RETURN mtl_system_items%ROWTYPE IS
982 BEGIN
983 IF ( p_item_id IS NULL OR p_org_id IS NULL ) THEN
984 RETURN NULL;
985 END IF;
986
987 IF p_item_id = g_msi_row.inventory_item_id AND p_org_id = g_msi_row.organization_id THEN
988 RETURN g_msi_row;
989 END IF;
990
991 SELECT *
992 INTO g_msi_row
993 FROM mtl_system_items
994 WHERE inventory_item_id = p_item_id
995 AND organization_id = p_org_id;
996
997 RETURN g_msi_row;
998 EXCEPTION
999 WHEN OTHERS THEN
1000 invalid_value('(' || p_item_id || ', ' || p_org_id || ')', '(ITEM_ID,TO_ORGANIZATION_ID)');
1001 END get_msi_row_from_num;
1002
1003 FUNCTION get_mic_row_from_num
1004 ( p_item_id IN mtl_item_categories.inventory_item_id%TYPE
1005 , p_org_id IN mtl_item_categories.organization_id%TYPE
1006 )
1007 RETURN mtl_item_categories%ROWTYPE IS
1008 BEGIN
1009 IF ( p_item_id IS NULL OR p_org_id IS NULL ) THEN
1010 RETURN NULL;
1011 END IF;
1012
1013 IF p_item_id = g_mic_row.inventory_item_id AND p_org_id = g_mic_row.organization_id THEN
1014 RETURN g_mic_row;
1015 END IF;
1016
1017 SELECT *
1018 INTO g_mic_row
1019 FROM mtl_item_categories
1020 WHERE inventory_item_id = p_item_id
1021 AND organization_id = p_org_id;
1022
1023 RETURN g_mic_row;
1024 EXCEPTION
1025 WHEN OTHERS THEN
1026 invalid_value(p_item_id, 'ITEM_ID');
1027 END get_mic_row_from_num;
1028
1029 FUNCTION get_mp_row_from_org( p_org_id IN mtl_parameters.organization_id%TYPE )
1030 RETURN mtl_parameters%ROWTYPE IS
1031 BEGIN
1032 IF ( p_org_id IS NULL ) THEN
1033 RETURN NULL;
1034 END IF;
1035
1036 IF p_org_id = g_mp_row.organization_id THEN
1037 RETURN g_mp_row;
1038 END IF;
1039
1040 SELECT *
1041 INTO g_mp_row
1042 FROM mtl_parameters
1043 WHERE organization_id = p_org_id
1044 AND rownum = 1;
1045
1046 RETURN g_mp_row;
1047 EXCEPTION
1048 WHEN OTHERS THEN
1049 invalid_value(p_org_id, 'ORG_ID');
1050 END get_mp_row_from_org;
1051
1052 FUNCTION get_rp_row_from_org( p_org_id IN rcv_parameters.organization_id%TYPE )
1053 RETURN rcv_parameters%ROWTYPE IS
1054 BEGIN
1055 IF ( p_org_id IS NULL ) THEN
1056 RETURN NULL;
1057 END IF;
1058
1059 IF p_org_id = g_rp_row.organization_id THEN
1060 RETURN g_rp_row;
1061 END IF;
1062
1063 SELECT *
1064 INTO g_rp_row
1065 FROM rcv_parameters
1066 WHERE organization_id = p_org_id
1067 AND rownum = 1;
1068
1069 RETURN g_rp_row;
1070 EXCEPTION
1071 WHEN OTHERS THEN
1072 invalid_value(p_org_id, 'ORG_ID');
1073 END get_rp_row_from_org;
1074
1075 FUNCTION get_fc_row_from_code( p_currency_code IN fnd_currencies.currency_code%TYPE )
1076 RETURN fnd_currencies%ROWTYPE IS
1077 BEGIN
1078 IF ( p_currency_code IS NULL ) THEN
1079 RETURN NULL;
1080 END IF;
1081
1082 IF p_currency_code = g_fc_row.currency_code THEN
1083 RETURN g_fc_row;
1084 END IF;
1085
1086 SELECT *
1087 INTO g_fc_row
1088 FROM fnd_currencies
1089 WHERE currency_code = p_currency_code;
1090
1091 RETURN g_fc_row;
1092 EXCEPTION
1093 WHEN OTHERS THEN
1094 invalid_value(p_currency_code, 'CURRENCY_CODE');
1095 END get_fc_row_from_code;
1096
1097 FUNCTION get_fsp_row
1098 RETURN financials_system_parameters%ROWTYPE IS
1099 BEGIN
1100 IF g_fsp_row.set_of_books_id IS NULL THEN
1101 SELECT *
1102 INTO g_fsp_row
1103 FROM financials_system_parameters;
1104 END IF;
1105
1106 RETURN g_fsp_row;
1107 END get_fsp_row;
1108
1109 FUNCTION get_pvs_row_from_id( p_vendor_site_id IN po_vendor_sites_all.vendor_site_id%TYPE )
1110 RETURN po_vendor_sites_all%ROWTYPE IS
1111 BEGIN
1112 IF p_vendor_site_id IS NULL THEN
1113 RETURN NULL;
1114 END IF;
1115
1116 /* Bug 11834044
1117 * In Online receiving mode, when the supplier site setup was changed
1118 * for the Pay On code value, the change is not reflected during receiving
1119 * when the receipt is against the same supplier/supplier site. Instead the
1120 * previously cached value was being used which is incorrect. So commenting
1121 * the logic that is caching, now we get the Pay On code value from
1122 * supplier site setup every time.
1123 */
1124
1125 --IF g_pvs_row.vendor_site_id = p_vendor_site_id THEN
1126 -- RETURN g_pvs_row;
1127 --END IF;
1128
1129 SELECT *
1130 INTO g_pvs_row
1131 FROM po_vendor_sites_all
1132 WHERE vendor_site_id = p_vendor_site_id;
1133
1134 RETURN g_pvs_row;
1135 END get_pvs_row_from_id;
1136
1137 FUNCTION get_muom_row_from_name( p_unit_of_measure IN mtl_units_of_measure.unit_of_measure%TYPE )
1138 RETURN mtl_units_of_measure%ROWTYPE IS
1139 BEGIN
1140 IF p_unit_of_measure IS NULL THEN
1141 RETURN NULL;
1142 END IF;
1143
1144 IF g_muom_row.unit_of_measure = p_unit_of_measure THEN
1145 RETURN g_muom_row;
1146 END IF;
1147
1148 SELECT *
1149 INTO g_muom_row
1150 FROM mtl_units_of_measure
1151 WHERE unit_of_measure = p_unit_of_measure;
1152
1153 RETURN g_muom_row;
1154 END get_muom_row_from_name;
1155
1156 FUNCTION get_po_lookup
1157 ( p_lookup_type IN po_lookup_codes.lookup_type%TYPE
1158 , p_lookup_code IN po_lookup_codes.lookup_code%TYPE
1159 ) RETURN po_lookup_codes.displayed_field%TYPE IS
1160 l_key VARCHAR2(80) := p_lookup_type || '-' || p_lookup_code;
1161 BEGIN
1162 IF NOT g_po_lookups.EXISTS(l_key) THEN
1163 SELECT displayed_field
1164 INTO g_po_lookups(l_key)
1165 FROM po_lookup_codes
1166 WHERE lookup_type = p_lookup_type
1167 AND lookup_code = p_lookup_code
1168 AND enabled_flag = 'Y';
1169 END IF;
1170
1171 RETURN g_po_lookups(l_key);
1172 EXCEPTION
1173 WHEN OTHERS THEN
1174 RETURN NULL;
1175 END get_po_lookup;
1176
1177 FUNCTION is_lcm_shipment (p_po_line_location_id IN NUMBER )
1178 RETURN VARCHAR2 IS
1179 BEGIN
1180 IF ( nvl(g_pll_id,-99) <> p_po_line_location_id) THEN
1181
1182 SELECT nvl(lcm_flag,'N')
1183 INTO g_lcm_flag
1184 FROM po_line_locations_all
1185 WHERE line_location_id = p_po_line_location_id;
1186
1187 g_pll_id := p_po_line_location_id;
1188 END IF;
1189
1190 RETURN g_lcm_flag;
1191 EXCEPTION
1192 WHEN OTHERS THEN
1193 RETURN 'N';
1194 END is_lcm_shipment;
1195
1196 FUNCTION is_lcm_org ( p_organization_id IN NUMBER )
1197 RETURN VARCHAR2 IS
1198 BEGIN
1199 IF ( nvl(g_mp_org_id,-99) <> p_organization_id) THEN
1200 SELECT nvl(lcm_enabled_flag,'N')
1201 INTO g_lcm_enabled_flag
1202 FROM mtl_parameters
1203 WHERE organization_id = p_organization_id;
1204
1205 g_mp_org_id := p_organization_id;
1206 END IF;
1207
1208 RETURN g_lcm_enabled_flag;
1209 EXCEPTION
1210 WHEN OTHERS THEN
1211 RETURN 'N';
1212 END is_lcm_org;
1213
1214 FUNCTION is_pre_rcv_org ( p_organization_id IN NUMBER )
1215 RETURN VARCHAR2 IS
1216 BEGIN
1217 IF ( nvl(g_rp_org_id,-99) <> p_organization_id) THEN
1218 SELECT nvl(pre_receive,'N')
1219 INTO g_pre_rcv_flag
1220 FROM rcv_parameters
1221 WHERE organization_id = p_organization_id;
1222
1223 g_rp_org_id := p_organization_id;
1224 END IF;
1225
1226 RETURN g_pre_rcv_flag;
1227 EXCEPTION
1228 WHEN OTHERS THEN
1229 RETURN 'N';
1230 END is_pre_rcv_org;
1231
1232 END rcv_table_functions;
[Home] [Help]