[Home] [Help]
PACKAGE BODY: APPS.PO_LOCATIONS_S
Source
1 PACKAGE BODY PO_LOCATIONS_S AS
2 /* $Header: POXCOL2B.pls 120.2 2005/09/15 05:11:11 asista noship $*/
3
4 -- Read the profile option that enables/disables the debug log
5 g_asn_debug VARCHAR2(1) := NVL(FND_PROFILE.VALUE('PO_RVCTP_ENABLE_TRACE'),'N');
6
7 /* create client package body */
8 /*
9 PACKAGE BODY PO_LOCATIONS_S IS
10 */
11
12 /* local procedure - added for hz_location changes : bug 1942696 */
13 PROCEDURE validate_hz_loc_info(p_loc_record IN OUT NOCOPY RCV_SHIPMENT_OBJECT_SV.Location_id_record_type);
14
15 /*===========================================================================
16
17 FUNCTION NAME: get_ship_to_location()
18
19 ===========================================================================*/
20 FUNCTION get_ship_to_location(X_deliver_to_loc_id IN NUMBER,
21 X_ship_to_loc_id IN OUT NOCOPY NUMBER) return BOOLEAN IS
22
23 X_progress varchar2(3) := NULL;
24 X_location_id_v number := NULL;
25
26 BEGIN
27
28 X_progress := '010';
29
30 /* Select the ship-to location associated with a given
31 ** deliver-to location.
32 */
33
34 begin
35 SELECT ship_to_location_id
36 INTO X_ship_to_loc_id
37 FROM hr_locations_all
38 WHERE location_id = X_deliver_to_loc_id;
39
40 exception
41 --bug 1942696 hr_location changes to reflect the new view
42 when no_data_found then
43 SELECT location_id
44 INTO X_ship_to_loc_id
45 FROM hz_locations
46 WHERE location_id = X_deliver_to_loc_id;
47 end;
48
49 /* Check to see if this location is still valid. If so, return TRUE.
50 ** If not, return FALSE.
51 */
52
53 begin
54 SELECT location_id
55 INTO X_location_id_v
56 FROM hr_locations_all
57 WHERE location_id = X_ship_to_loc_id
58 AND sysdate < nvl(inactive_date, sysdate + 1);
59 exception
60 when no_data_found then
61 --bug 1942696 hr_location changes to reflect the new view
62 SELECT location_id
63 INTO X_location_id_v
64 FROM hz_locations
65 WHERE location_id = X_ship_to_loc_id
66 AND sysdate < nvl(address_expiration_date, sysdate + 1);
67 end;
68
69 return (TRUE);
70
71 EXCEPTION
72
73 when no_data_found then
74 return (FALSE);
75 when others then
76 po_message_s.sql_error('get_ship_to_location',X_progress,sqlcode);
77 raise;
78
79 END get_ship_to_location;
80
81 /*===========================================================================
82
83 FUNCTION NAME: val_location(...)
84
85 ===========================================================================*/
86
87 FUNCTION val_location
88 (
89 x_location_id IN NUMBER,
90 x_destination_type IN VARCHAR2,
91 x_organization_id IN NUMBER
92 )
93 RETURN NUMBER IS
94
95 x_progress VARCHAR2(3) := NULL;
96 x_status VARCHAR2(20) := NULL;
97
98 BEGIN
99 x_progress := '000';
100
101 IF x_location_id IS NULL THEN
102 IF x_destination_type = 'RECEIVING' THEN
103 RETURN 2; /* missing receive to */
104 ELSE
105 RETURN 3; /* missing deliver to */
106 END IF;
107 END IF;
108
109 begin
110 SELECT 'location_ok'
111 INTO x_status
112 FROM HR_LOCATIONS_ALL
113 WHERE LOCATION_ID = x_location_id
114 AND NVL(INVENTORY_ORGANIZATION_ID, x_organization_id) = x_organization_id
115 AND NVL(INACTIVE_DATE, SYSDATE+1) > SYSDATE;
116
117 exception
118 when no_data_found then
119 --bug 1942696 hr_location changes to reflect the new view
120 SELECT 'location_ok'
121 INTO x_status
122 FROM HZ_LOCATIONS
123 WHERE LOCATION_ID = x_location_id
124 AND NVL(ADDRESS_EXPIRATION_DATE, SYSDATE+1) > SYSDATE;
125 end;
126
127 IF x_status = 'location_ok' THEN
128 RETURN 0;
129 ELSE
130 RETURN 1;
131 END IF;
132
133 EXCEPTION
134 WHEN NO_DATA_FOUND THEN
135 RETURN 4; /* Require this Staate in Release Shipment */
136 WHEN OTHERS THEN
137 po_message_s.sql_error('val_location', x_progress, sqlcode);
138 RAISE;
139
140
141 END val_location;
142
143 /*===========================================================================
144
145 PROCEDURE NAME: get_loc_attributes(...)
146
147 ===========================================================================*/
148
149 procedure get_loc_attributes ( X_temp_loc_id IN number, X_loc_dsp IN OUT NOCOPY varchar2,
150 X_org_id IN OUT NOCOPY number) IS
151 X_progress varchar2(3) := '';
152
153 begin
154 X_progress := '010';
155 X_org_id := NULL;
156 X_loc_dsp := NULL;
157
158
159 select location_code,
160 inventory_organization_id
161 into X_loc_dsp,
162 X_org_id
163 from hr_locations
164 where location_id = X_temp_loc_id;
165
166 exception
167 when no_data_found then
168 begin
169 --- As part of hr_location changes bug# 2393886
170 select (substrb(rtrim(address1)||'-'||rtrim(city),1,20)) location_code ,
171 null
172 into X_loc_dsp,
173 x_org_id
174 from hz_locations
175 where location_id = X_temp_loc_id;
176 exception
177 when no_data_found then
178 X_loc_dsp := '';
179 X_org_id := '';
180 end;
181 when too_many_rows then
182 X_loc_dsp := '';
183 X_org_id := '';
184 when others then
185 po_message_s.sql_error('get_loc_attributes',X_progress,sqlcode);
186 raise;
187 end get_loc_attributes;
188
189 /*===========================================================================
190
191 PROCEDURE NAME: get_tax_name(...)
192
193 ===========================================================================*/
194 procedure get_tax_name ( X_location_id IN NUMBER,
195 X_org_id IN NUMBER,
196 X_tax_name IN OUT NOCOPY VARCHAR2) IS
197
198 X_Progress varchar2(3) := '';
199
200 begin
201
202 X_Progress := '010';
203 --
204 -- <R12 eTax Integration>
205 -- This procedure is no longer used
206 --
207 exception
208 when no_data_found then
209 null; /* Not an error */
210 when others then
211 po_message_s.sql_error('get_tax_name',X_progress,sqlcode);
212 raise;
213
214 end get_tax_name;
215
216
217
218 /*===========================================================================
219
220 PROCEDURE NAME: get_loc_org (...)
221
222 ===========================================================================*/
223
224 procedure get_loc_org ( X_location_id IN NUMBER,
225 X_sob_id IN NUMBER,
226 X_org_id IN OUT NOCOPY NUMBER,
227 X_org_code IN OUT NOCOPY VARCHAR2,
228 X_org_name IN OUT NOCOPY VARCHAR2) IS
229
230 x_progress varchar2(3) := '';
231
232 begin
233
234 x_progress := '010';
235 x_org_id := NULL;
236 x_org_name := NULL;
237 x_org_code := NULL;
238
239 SELECT hrl.inventory_organization_id
240 INTO x_org_id
241 FROM hr_locations hrl,
242 org_organization_definitions ood
243 WHERE ood.organization_id = hrl.inventory_organization_id
244 AND ood.set_of_books_id = x_sob_id
245 AND hrl.location_id = x_location_id;
246
247 x_progress := '020';
248
249 SELECT ood.organization_name,
250 ood.organization_code
251 INTO x_org_name,
252 X_org_code
253 FROM org_organization_definitions ood
254 WHERE ood.organization_id = x_org_id;
255
256 exception
257 when no_data_found then
258 x_org_id := null;
259 x_org_name := null;
260 when others then
261 po_message_s.sql_error('get_loc_org', x_progress, sqlcode);
262 raise;
263
264 end get_loc_org;
265
266 /*===========================================================================
267
268 PROCEDURE NAME: val_if_inventory_destination
269
270 ===========================================================================*/
271
272 /*
273 ** Check to see if any of the distributions are of type inventory
274 */
275 FUNCTION val_if_inventory_destination (X_line_location_id IN NUMBER,
276 X_shipment_line_id IN NUMBER)
277 RETURN BOOLEAN IS
278
279 X_number_of_inv_dest NUMBER := 0;
280 X_progress VARCHAR2(4) := '000';
281
282 BEGIN
283
284 X_progress := '600';
285 /*
286 ** Check to see which id is set to know which table to check for
287 ** inventory destination_type_code
288 */
289 IF (X_line_location_id IS NOT NULL) THEN
290
291 X_progress := '610';
292
293 SELECT count(1)
294 INTO X_number_of_inv_dest
295 FROM po_distributions pd
296 WHERE pd.line_location_id = X_line_location_id
297 AND pd.destination_type_code = 'INVENTORY';
298
299 ELSE
300
301 X_progress := '620';
302
303 SELECT count(1)
304 INTO X_number_of_inv_dest
305 FROM rcv_shipment_lines rsl
306 WHERE rsl.shipment_line_id = X_shipment_line_id
307 AND rsl.destination_type_code = 'INVENTORY';
308 END IF;
309
310 IF (X_number_of_inv_dest > 0) THEN
311 RETURN TRUE;
312 ELSE
313 RETURN FALSE;
314 END IF;
315
316 EXCEPTION
317 WHEN OTHERS THEN
318 po_message_s.sql_error('val_if_inventory_destination', x_progress, sqlcode);
319 RAISE;
320
321 END val_if_inventory_destination;
322
323
324
325 /*===========================================================================
326
327 PROCEDURE NAME: get_location_code (...)
328
329 ===========================================================================*/
330
331 procedure get_location_code (x_location_id IN NUMBER,
332 x_location_code IN OUT NOCOPY VARCHAR2) IS
333
334 x_progress varchar2(3) := '';
335
336 begin
337
338 x_progress := '010';
339
340
341 SELECT hrl.location_code
342 INTO x_location_code
343 FROM hr_locations hrl
344 WHERE hrl.location_id = x_location_id;
345
346
347 exception
348 when no_data_found then
349 -- part of hr_location changes bug# 2393886
350 po_shipments_sv2.get_drop_ship_cust_locations
351 (x_location_id , x_location_code );
352
353 when others then
354 po_message_s.sql_error('get_location_code', x_progress, sqlcode);
355 raise;
356
357 end get_location_code;
358
359
360 --=============================================================================
361 -- FUNCTION : get_location_code <2699040>
362 -- TYPE : Private
363 --
364 -- PRE-REQS : -
365 -- MODIFIES : -
366 --
367 -- DESCRIPTION : Gets the location_code for the input location_id.
368 --
369 -- PARAMETERS : location_id
370 --
371 -- RETURNS : location_code for the input location_id
372 -- (NULL for invalid location_id's)
373 --
374 -- EXCEPTIONS : -
375 --=============================================================================
376 FUNCTION get_location_code
377 (
378 p_location_id IN HR_LOCATIONS.location_id%TYPE
379 )
380 RETURN HR_LOCATIONS.location_code%TYPE
381 IS
382 x_location_code HR_LOCATIONS.location_code%TYPE;
383 BEGIN
384
385 get_location_code( p_location_id, x_location_code );
386
387 return (x_location_code);
388
389 EXCEPTION
390
391 WHEN OTHERS THEN
392 return (NULL);
393
394 END get_location_code;
395
396
397 /*============================================================================
398 ** FUNCTION : val_ship_to_site_in_org
399 **===========================================================================*/
400
401 FUNCTION val_ship_to_site_in_org
402 ( X_location_id IN NUMBER,
403 X_organization_id IN NUMBER
404 )
405 RETURN BOOLEAN IS
406
407 X_Progress varchar2(3) := '';
408 X_valid_loc varchar2(1);
409
410 begin
411
412 X_Progress := '010';
413
414 if X_location_id is not null then
415
416 begin
417 SELECT 'Y'
418 INTO X_valid_loc
419 FROM HR_LOCATIONS_ALL
420 WHERE LOCATION_ID = x_location_id
421 AND NVL(SHIP_TO_SITE_FLAG,'N') = 'Y'
422 AND NVL(INVENTORY_ORGANIZATION_ID, X_organization_id) = X_organization_id
423 AND NVL(INACTIVE_DATE, SYSDATE+1) > SYSDATE;
424 exception
425 when no_data_found then
426 --bug 1942696 hr_location changes to reflect the new view
427 SELECT 'Y'
428 INTO X_valid_loc
429 FROM HZ_LOCATIONS
430 WHERE LOCATION_ID = x_location_id
431 AND NVL(ADDRESS_EXPIRATION_DATE, SYSDATE+1) > SYSDATE;
432 end;
433
434 return(TRUE);
435
436 else
437
438 return(FALSE);
439
440 end if;
441
442 exception
443
444 when no_data_found then
445 return(FALSE);
446 when others then
447 po_message_s.sql_error('val_ship_to_site_in_org', X_progress, sqlcode);
448 raise;
449
450
451 end val_ship_to_site_in_org;
452
453
454 /*============================================================================
455 ** FUNCTION : val_receipt_site_in_org
456 **===========================================================================*/
457
458 FUNCTION val_receipt_site_in_org
459 ( X_location_id IN NUMBER,
460 X_organization_id IN NUMBER
461 )
462 RETURN BOOLEAN IS
463
464 X_Progress varchar2(3) := '';
465 X_valid_loc varchar2(1);
466
467 begin
468
469 X_Progress := '010';
470
471 if X_location_id is not null then
472
473 begin
474 SELECT 'Y'
475 INTO X_valid_loc
476 FROM HR_LOCATIONS_ALL
477 WHERE LOCATION_ID = x_location_id
478 AND NVL(RECEIVING_SITE_FLAG,'N') = 'Y'
479 AND NVL(INVENTORY_ORGANIZATION_ID, X_organization_id) = X_organization_id
480 AND NVL(INACTIVE_DATE, SYSDATE+1) > SYSDATE;
481 exception
482 when no_data_found then
483 --bug 1942696 hr_location changes to reflect the new view
484 SELECT 'Y'
485 INTO X_valid_loc
486 FROM HZ_LOCATIONS
487 WHERE LOCATION_ID = x_location_id
488 AND NVL(ADDRESS_EXPIRATION_DATE, SYSDATE+1) > SYSDATE;
489 end;
490
491 return(TRUE);
492
493 else
494
498
495 return(FALSE);
496
497 end if;
499 exception
500
501 when no_data_found then
502 return(FALSE);
503 when others then
504 po_message_s.sql_error('val_receipt_site_in_org', X_progress, sqlcode);
505 raise;
506
507
508 end val_receipt_site_in_org;
509
510
511 /*===========================================================================
512
513 PROCEDURE NAME: derive_location_info()
514
515 ===========================================================================*/
516
517 PROCEDURE derive_location_info (
518 p_loc_record IN OUT NOCOPY RCV_SHIPMENT_OBJECT_SV.Location_id_record_type) IS
519
520 cid INTEGER;
521 rows_processed INTEGER;
522 sql_str VARCHAR2(2000);
523
524 loc_code_null BOOLEAN := TRUE;
525 loc_id_null BOOLEAN := TRUE;
526
527 BEGIN
528 sql_str := 'SELECT location_code, location_id FROM hr_locations WHERE ';
529
530 IF p_loc_record.location_code IS NULL and
531 p_loc_record.location_id IS NULL THEN
532 p_loc_record.error_record.error_status := 'W';
533 RETURN;
534
535 END IF;
536
537 IF p_loc_record.location_code IS NOT NULL and
538 p_loc_record.location_id IS NOT NULL THEN
539
540 p_loc_record.error_record.error_status := 'S';
541 RETURN;
542
543 END IF;
544
545 IF p_loc_record.location_code IS NOT NULL THEN
546
547 sql_str := sql_str || ' location_code = :v_loc_code and';
548 loc_code_null := FALSE;
549
550 END IF;
551
552 IF p_loc_record.location_id IS NOT NULL THEN
553 sql_str := sql_str || ' location_id = :v_loc_id and';
554 loc_id_null := FALSE;
555
556 END IF;
557
558 sql_str := substr(sql_str,1,length(sql_str)-3);
559 /* dbms_output.put_line(substr(sql_str,1,255));
560 dbms_output.put_line(substr(sql_str,256,255));
561 dbms_output.put_line(substr(sql_str,513,255)); */
562
563 cid := dbms_sql.open_cursor;
564
565 dbms_sql.parse(cid, sql_str , dbms_sql.native);
566
567 dbms_sql.define_column(cid,1,p_loc_record.location_code,25);
568 dbms_sql.define_column(cid,2,p_loc_record.location_id);
569
570 IF not loc_code_null THEN
571
572 dbms_sql.bind_variable(cid,'v_loc_code',p_loc_record.location_code);
573
574 END IF;
575
576 IF NOT loc_id_null THEN
577
578 dbms_sql.bind_variable(cid,'v_loc_id',p_loc_record.location_id);
579
580 END IF;
581
582 rows_processed := dbms_sql.execute_and_fetch(cid);
583
584 IF rows_processed = 1 THEN
585 IF loc_code_null THEN
586 dbms_sql.column_value(cid,1,p_loc_record.location_code);
587 END IF;
588
589 IF loc_id_null THEN
590 dbms_sql.column_value(cid,2,p_loc_record.location_id);
591 END IF;
592
593 p_loc_record.error_record.error_status := 'S';
594
595 ELSIF rows_processed = 0 and p_loc_record.location_id IS NOT NULL THEN
596 -- part of hr_location changes new bug 2393886
597
598 sql_str := 'SELECT (substrb(rtrim(address1)||' || '''-'''||
599 '||rtrim(city),1,20)) location_code, location_id '||
600 'FROM hz_locations WHERE ';
601
602 -- IF p_loc_record.location_id IS NOT NULL THEN
603 sql_str := sql_str || 'location_id = :v_loc_id ';
604 loc_id_null := FALSE;
605 -- END IF;
606 sql_str := substr(sql_str,1,length(sql_str));
607
608
609 cid := dbms_sql.open_cursor;
610
611 dbms_sql.parse(cid, sql_str , dbms_sql.native);
612 dbms_sql.define_column(cid,1,p_loc_record.location_code,25);
613 dbms_sql.define_column(cid,2,p_loc_record.location_id);
614 /*
615 IF not loc_code_null THEN
616 dbms_sql.bind_variable(cid,'v_loc_code',p_loc_record.location_code);
617 END IF;
618 */
619 IF NOT loc_id_null THEN
620 dbms_sql.bind_variable(cid,'v_loc_id',p_loc_record.location_id);
621 END IF;
622
623 rows_processed := dbms_sql.execute_and_fetch(cid);
624
625 IF rows_processed = 1 THEN
626
627 IF loc_code_null THEN
628 dbms_sql.column_value(cid,1,p_loc_record.location_code);
629 END IF;
630
631 /* IF loc_id_null THEN
632 dbms_sql.column_value(cid,2,p_loc_record.location_id);
633 END IF;
634 */
635 p_loc_record.error_record.error_status := 'S';
636
637
638 ELSIF rows_processed = 0 THEN
639 p_loc_record.error_record.error_status := 'W';
640
641 ELSE
642 p_loc_record.error_record.error_status := 'W';
646 p_loc_record.error_record.error_status := 'W';
643 END IF;
644 -- part of new bug 2393886
645 ELSE
647
648 END IF;
649
650 IF dbms_sql.is_open(cid) THEN
651 dbms_sql.close_cursor(cid);
652 END IF;
653
654 EXCEPTION
655 WHEN others THEN
656
657 IF dbms_sql.is_open(cid) THEN
658 dbms_sql.close_cursor(cid);
659 END IF;
660
661 p_loc_record.error_record.error_status := 'U';
662 p_loc_record.error_record.error_message := sqlerrm;
663
664 END derive_location_info;
665
666 /*===========================================================================
667
668 PROCEDURE NAME: validate_location_info()
669
670 ===========================================================================*/
671
672 PROCEDURE validate_location_info (
673 p_loc_record IN OUT NOCOPY rcv_shipment_object_sv.Location_id_record_type) IS
674
675 X_cid INTEGER;
676 X_rows_processed INTEGER;
677 X_sql_str VARCHAR2(2000);
678
679 X_loc_code_null BOOLEAN := TRUE;
680 X_loc_id_null BOOLEAN := TRUE;
681
682 X_inactive_date DATE;
683 X_receiving_site_flag VARCHAR2(1);
684 X_inventory_organization_id NUMBER;
685
686 X_sysdate DATE := sysdate;
687
688 BEGIN
689
690 X_sql_str := 'select inactive_date,receiving_site_flag,inventory_organization_id from hr_locations where ';
691
692 IF p_loc_record.location_code IS NULL and
693 p_loc_record.location_id IS NULL THEN
694
695 p_loc_record.error_record.error_status := 'E';
696 p_loc_record.error_record.error_message := 'All Blanks';
697 RETURN;
698
699 END IF;
700
701 IF p_loc_record.location_code IS NOT NULL THEN
702
703 X_sql_str := X_sql_str || ' location_code = :v_loc_code and';
704 X_loc_code_null := FALSE;
705
706 END IF;
707
708 IF p_loc_record.location_id IS NOT NULL THEN
709 X_sql_str := X_sql_str || ' location_id = :v_loc_id and';
710 X_loc_id_null := FALSE;
711
712 END IF;
713
714 X_sql_str := substr(X_sql_str,1,length(X_sql_str)-3);
715
716 /* dbms_output.put_line(substr(X_sql_str,1,255));
717 dbms_output.put_line(substr(X_sql_str,256,255));
718 dbms_output.put_line(substr(X_sql_str,513,255)); */
719
720 X_cid := dbms_sql.open_cursor;
721
722 dbms_sql.parse(X_cid, X_sql_str , dbms_sql.native);
723
724 dbms_sql.define_column(X_cid,1,X_inactive_date);
725 dbms_sql.define_column(X_cid,2,X_receiving_site_flag,1);
726 dbms_sql.define_column(X_cid,3,X_inventory_organization_id);
727
728 IF NOT X_loc_code_null THEN
729
730 dbms_sql.bind_variable(X_cid,'v_loc_code',p_loc_record.location_code);
731
732 END IF;
733
734 IF NOT X_loc_id_null THEN
735
736 dbms_sql.bind_variable(X_cid,'v_loc_id',p_loc_record.location_id);
737
738 END IF;
739
740 X_rows_processed := dbms_sql.execute_and_fetch(X_cid);
741
742 IF X_rows_processed = 1 THEN
743
744 dbms_sql.column_value(X_cid,1,X_inactive_date);
745 dbms_sql.column_value(X_cid,2,X_receiving_site_flag);
746 dbms_sql.column_value(X_cid,3,X_inventory_organization_id);
747
748 /* Check whether specified location is active */
749
750 IF nvl(X_inactive_date,X_sysdate + 1) < X_sysdate THEN
751
752 p_loc_record.error_record.error_status := 'E';
753 p_loc_record.error_record.error_message := 'LOC_DISABLED';
754
755 IF dbms_sql.is_open(X_cid) THEN
756 dbms_sql.close_cursor(X_cid);
757 END IF;
758
759 RETURN;
760
761 END IF;
762
763
764 /* Check whether location is receiving location */
765
766 IF nvl(X_receiving_site_flag,'Y') = 'N' THEN
767
768 p_loc_record.error_record.error_status := 'E';
769 p_loc_record.error_record.error_message := 'LOC_NOT_RCV_SITE';
770
771 IF dbms_sql.is_open(X_cid) THEN
772 dbms_sql.close_cursor(X_cid);
773 END IF;
774
775 RETURN;
776
777 END IF;
778
779 /* Check whether the location is within the ship_to_organization */
780 /* Bug 989583 :
781 the location may not be connected to any inventory org in some cases
782 Therefore changing the nvl to equate it to p_loc_record.organization_id in such cases
783 */
784
785 IF nvl(X_inventory_organization_id,p_loc_record.organization_id) <> p_loc_record.organization_id THEN
786
787 p_loc_record.error_record.error_status := 'E';
788 p_loc_record.error_record.error_message := 'LOC_NOT_IN_ORG';
789
790 IF dbms_sql.is_open(X_cid) THEN
791 dbms_sql.close_cursor(X_cid);
792 END IF;
793
794 RETURN;
795
796 END IF;
797
798 p_loc_record.error_record.error_status := 'S';
802
799 p_loc_record.error_record.error_message := NULL;
800
801 ELSIF X_rows_processed = 0 THEN
803 -- fix as part of 2393886
804 -- p_loc_record.error_record.error_status := 'E';
805 p_loc_record.error_record.error_message := 'LOC_ID';
806
807 IF dbms_sql.is_open(X_cid) THEN
808 dbms_sql.close_cursor(X_cid);
809 END IF;
810 /* validate from hz_locations */
811 IF p_loc_record.location_code IS NULL and
812 p_loc_record.location_id IS NOT NULL THEN
813 validate_hz_loc_info(p_loc_record);
814 ELSE
815 RETURN;
816 END IF;
817
818 ELSE
819
820 p_loc_record.error_record.error_status := 'E';
821 p_loc_record.error_record.error_message := 'TOOMANYROWS';
822 IF dbms_sql.is_open(X_cid) THEN
823 dbms_sql.close_cursor(X_cid);
824 END IF;
825
826 RETURN;
827
828 END IF;
829
830 IF dbms_sql.is_open(X_cid) THEN
831 dbms_sql.close_cursor(X_cid);
832 END IF;
833
834 EXCEPTION
835
836 WHEN others THEN
837 IF dbms_sql.is_open(X_cid) THEN
838 dbms_sql.close_cursor(X_cid);
839 END IF;
840
841 p_loc_record.error_record.error_status := 'U';
842 p_loc_record.error_record.error_message := sqlerrm;
843
844 END validate_location_info;
845
846 /*===========================================================================
847
848 PROCEDURE NAME: validate_hz_loc_info()
849
850 ===========================================================================*/
851
852 PROCEDURE validate_hz_loc_info (
853 p_loc_record IN OUT NOCOPY rcv_shipment_object_sv.Location_id_record_type) IS
854
855 X_cid INTEGER;
856 X_rows_processed INTEGER;
857 X_sql_str VARCHAR2(2000);
858
859 X_loc_code_null BOOLEAN := TRUE;
860 X_loc_id_null BOOLEAN := TRUE;
861
862 X_add_exp_date DATE;
863
864 X_sysdate DATE := sysdate;
865
866 BEGIN
867
868 X_sql_str := 'select address_expiration_date from hz_locations where ';
869
870
871 IF p_loc_record.location_id IS NOT NULL THEN
872
873 X_sql_str := X_sql_str || ' location_id = :v_loc_id and';
874 X_loc_id_null := FALSE;
875
876 END IF;
877
878 X_sql_str := substr(X_sql_str,1,length(X_sql_str)-3);
879
880 X_cid := dbms_sql.open_cursor;
881
882 dbms_sql.parse(X_cid, X_sql_str , dbms_sql.native);
883
884 dbms_sql.define_column(X_cid,1,X_add_exp_date);
885
886
887 IF NOT X_loc_id_null THEN
888
889 dbms_sql.bind_variable(X_cid,'v_loc_id',p_loc_record.location_id);
890
891 END IF;
892
893 X_rows_processed := dbms_sql.execute_and_fetch(X_cid);
894
895
896 IF X_rows_processed = 1 THEN
897
898 dbms_sql.column_value(X_cid,1,X_add_exp_date);
899
900 /* Check whether specified location is active */
901
902 IF nvl(X_add_exp_date,X_sysdate + 1) < X_sysdate THEN
903
904 p_loc_record.error_record.error_status := 'E';
905 p_loc_record.error_record.error_message := 'LOC_DISABLED';
906
907 IF dbms_sql.is_open(X_cid) THEN
908 dbms_sql.close_cursor(X_cid);
909 END IF;
910
911 RETURN;
912
913 END IF;
914
915 p_loc_record.error_record.error_status := 'S';
916 p_loc_record.error_record.error_message := NULL;
917
918 ELSIF X_rows_processed = 0 THEN
919
920 p_loc_record.error_record.error_status := 'E';
921 p_loc_record.error_record.error_message := 'LOC_ID';
922
923 IF dbms_sql.is_open(X_cid) THEN
924 dbms_sql.close_cursor(X_cid);
925 END IF;
926
927 RETURN;
928
929 ELSE
930
931 p_loc_record.error_record.error_status := 'E';
932 p_loc_record.error_record.error_message := 'TOOMANYROWS';
933
934 IF dbms_sql.is_open(X_cid) THEN
935 dbms_sql.close_cursor(X_cid);
936 END IF;
937
938 RETURN;
939
940 END IF;
941
942 IF dbms_sql.is_open(X_cid) THEN
943 dbms_sql.close_cursor(X_cid);
944 END IF;
945
946 EXCEPTION
947
948 WHEN others THEN
949
950 IF dbms_sql.is_open(X_cid) THEN
951 dbms_sql.close_cursor(X_cid);
952 END IF;
953
954 p_loc_record.error_record.error_status := 'U';
955 p_loc_record.error_record.error_message := sqlerrm;
956
957 END validate_hz_loc_info;
958
959 /*===========================================================================
960
961 PROCEDURE NAME: validate_tax_info(...)
962
963 ===========================================================================*/
964
965 PROCEDURE validate_tax_info(
966 p_tax_rec IN OUT NOCOPY RCV_SHIPMENT_HEADER_SV.TaxRecType) IS
967
971 X_Progress := '010';
968 X_Progress varchar2(3);
969 BEGIN
970
972 --
973 -- <R12 eTax Integration>
974 -- This procedure is no longer used
975 --
976
977 EXCEPTION
978 WHEN others THEN
979 po_message_s.sql_error('validate_tax_info',X_progress,sqlcode);
980 raise;
981
982 END validate_tax_info;
983
984
985
986 /*===========================================================================
987
988 PROCEDURE NAME: po_predel_validation(...)
989 DESCRIPTION: This procedure is used primarily by the HR Location form
990 (PERWSLOC) to validate any locations that can be deleted
991 from the database. It checks for any location that is
992 currently in use in the PO, RCV, CHV base tables
993
994 ==========================================================================*/
995 PROCEDURE PO_PREDEL_VALIDATION (p_location_id IN NUMBER) IS
996
997 v_delete_allowed VARCHAR2(1);
998 l_msg VARCHAR2(30);
999
1000 BEGIN
1001
1002 hr_utility.set_location('PO_LOCATIONS_S.PO_PREDEL_VALIDATION', 1);
1003
1004 BEGIN
1005
1006 -- we will do an exhaustive search in all the base tables with some
1007 -- sort of location column. if the tables have a location_id =
1008 -- p_location_id, then the outer select will raise a NO_DATA_FOUND
1009 -- exception. otherwise, the select clause finishes with no error,
1010 -- the validation will pass, and the procedure will exit normally.
1011 -- we do NOT return anything from this procedure.
1012
1013 l_msg := 'PO_LOC_AGENTS';
1014 SELECT 'Y'
1015 INTO v_delete_allowed
1016 FROM sys.dual
1017 WHERE NOT EXISTS (
1018 SELECT null
1019 FROM PO_AGENTS
1020 WHERE location_id = p_location_id
1021 );
1022
1023 l_msg := 'PO_LOC_CONTROL_RULES';
1024 SELECT 'Y'
1025 INTO v_delete_allowed
1026 FROM sys.dual
1027 WHERE NOT EXISTS (
1028 SELECT null
1029 FROM PO_CONTROL_RULES
1030 WHERE location_id = p_location_id
1031 );
1032
1033 l_msg := 'PO_LOC_DISTRIBUTIONS_ALL';
1034 SELECT 'Y'
1035 INTO v_delete_allowed
1036 FROM sys.dual
1037 WHERE NOT EXISTS (
1038 SELECT null
1039 FROM PO_DISTRIBUTIONS_ALL
1040 WHERE deliver_to_location_id = p_location_id
1041 );
1042
1043 l_msg := 'PO_LOC_DISTRIBUTIONS_ARCHIVE';
1044 SELECT 'Y'
1045 INTO v_delete_allowed
1046 FROM sys.dual
1047 WHERE NOT EXISTS (
1048 SELECT null
1049 FROM PO_DISTRIBUTIONS_ARCHIVE_ALL
1050 WHERE deliver_to_location_id = p_location_id
1051 );
1052
1053 l_msg := 'PO_LOC_DISTRIBUTIONS_INTERFACE';
1054 SELECT 'Y'
1055 INTO v_delete_allowed
1056 FROM sys.dual
1057 WHERE NOT EXISTS (
1058 SELECT null
1059 FROM PO_DISTRIBUTIONS_INTERFACE
1060 WHERE deliver_to_location_id = p_location_id
1061 );
1062
1063 l_msg := 'PO_LOC_HEADERS_ALL';
1064 SELECT 'Y'
1065 INTO v_delete_allowed
1066 FROM sys.dual
1067 WHERE NOT EXISTS (
1068 SELECT null
1069 FROM PO_HEADERS_ALL
1070 WHERE ship_to_location_id = p_location_id
1071 OR bill_to_location_id = p_location_id
1072 );
1073
1074 l_msg := 'PO_LOC_HEADERS_ARCHIVE_ALL';
1075 SELECT 'Y'
1076 INTO v_delete_allowed
1077 FROM sys.dual
1078 WHERE NOT EXISTS (
1079 SELECT null
1080 FROM PO_HEADERS_ARCHIVE_ALL
1081 WHERE ship_to_location_id = p_location_id
1082 OR bill_to_location_id = p_location_id
1083 );
1084
1085 l_msg := 'PO_LOC_HEADERS_INTERFACE';
1086 SELECT 'Y'
1087 INTO v_delete_allowed
1088 FROM sys.dual
1089 WHERE NOT EXISTS (
1090 SELECT null
1091 FROM PO_HEADERS_INTERFACE
1092 WHERE ship_to_location_id = p_location_id
1093 OR bill_to_location_id = p_location_id
1094 );
1095
1096 l_msg := 'PO_LOC_LINES_INTERFACE';
1097 SELECT 'Y'
1098 INTO v_delete_allowed
1099 FROM sys.dual
1100 WHERE NOT EXISTS (
1101 SELECT null
1102 FROM PO_LINES_INTERFACE
1106 l_msg := 'PO_LOC_LINE_LOCATIONS_ALL';
1103 WHERE ship_to_location_id = p_location_id
1104 );
1105
1107 SELECT 'Y'
1108 INTO v_delete_allowed
1109 FROM sys.dual
1110 WHERE NOT EXISTS (
1111 SELECT null
1112 FROM PO_LINE_LOCATIONS_ALL
1113 WHERE ship_to_location_id = p_location_id
1114 );
1115
1116 l_msg := 'PO_LOC_LINE_LOCATIONS_ARCHIVE';
1117 SELECT 'Y'
1118 INTO v_delete_allowed
1119 FROM sys.dual
1120 WHERE NOT EXISTS (
1121 SELECT null
1122 FROM PO_LINE_LOCATIONS_ARCHIVE_ALL
1123 WHERE ship_to_location_id = p_location_id
1124 );
1125
1126 l_msg := 'PO_LOC_LOCATION_ASSOCIATIONS';
1127 SELECT 'Y'
1128 INTO v_delete_allowed
1129 FROM sys.dual
1130 WHERE NOT EXISTS (
1131 SELECT null
1132 FROM PO_LOCATION_ASSOCIATIONS
1133 WHERE location_id = p_location_id
1134 );
1135
1136 l_msg := 'PO_LOC_REQUISITIONS_INTERFACE';
1137 SELECT 'Y'
1138 INTO v_delete_allowed
1139 FROM sys.dual
1140 WHERE NOT EXISTS (
1141 SELECT null
1142 FROM PO_REQUISITIONS_INTERFACE_ALL
1143 WHERE deliver_to_location_id = p_location_id
1144 );
1145
1146 l_msg := 'PO_LOC_REQUISITION_LINES_ALL';
1147 SELECT 'Y'
1148 INTO v_delete_allowed
1149 FROM sys.dual
1150 WHERE NOT EXISTS (
1151 SELECT null
1152 FROM PO_REQUISITION_LINES_ALL
1153 WHERE deliver_to_location_id = p_location_id
1154 );
1155 -- Bug# 4546121:ship_to_location_id and bill_to_location_id have been nulled out
1156 -- on po_vendors. This validation is not viable any longer.
1157 /*
1158 l_msg := 'PO_LOC_VENDORS';
1159 SELECT 'Y'
1160 INTO v_delete_allowed
1161 FROM sys.dual
1162 WHERE NOT EXISTS (
1163 SELECT null
1164 FROM PO_VENDORS
1165 WHERE ship_to_location_id = p_location_id
1166 OR bill_to_location_id = p_location_id
1167 );
1168 */
1169
1170 l_msg := 'PO_LOC_VENDOR_SITES_ALL';
1171 SELECT 'Y'
1172 INTO v_delete_allowed
1173 FROM sys.dual
1174 WHERE NOT EXISTS (
1175 SELECT null
1176 FROM PO_VENDOR_SITES_ALL
1177 WHERE ship_to_location_id = p_location_id
1178 OR bill_to_location_id = p_location_id
1179 );
1180
1181 l_msg := 'RCV_LOC_HEADERS_INTERFACE';
1182 SELECT 'Y'
1183 INTO v_delete_allowed
1184 FROM sys.dual
1185 WHERE NOT EXISTS (
1186 SELECT null
1187 FROM RCV_HEADERS_INTERFACE
1188 WHERE location_id = p_location_id
1189 );
1190
1191 l_msg := 'RCV_LOC_SHIPMENT_HEADERS';
1192 SELECT 'Y'
1193 INTO v_delete_allowed
1194 FROM sys.dual
1195 WHERE NOT EXISTS (
1196 SELECT null
1197 FROM RCV_SHIPMENT_HEADERS
1198 WHERE ship_to_location_id = p_location_id
1199 );
1200
1201 l_msg := 'RCV_LOC_SHIPMENT_LINES';
1202 SELECT 'Y'
1203 INTO v_delete_allowed
1204 FROM sys.dual
1205 WHERE NOT EXISTS (
1206 SELECT null
1207 FROM RCV_SHIPMENT_LINES
1208 WHERE ship_to_location_id = p_location_id
1209 OR deliver_to_location_id = p_location_id
1210 );
1211
1212 l_msg := 'RCV_LOC_SUPPLY';
1213 SELECT 'Y'
1214 INTO v_delete_allowed
1215 FROM sys.dual
1216 WHERE NOT EXISTS (
1217 SELECT null
1218 FROM RCV_SUPPLY
1219 WHERE location_id = p_location_id
1220 );
1221
1222 l_msg := 'RCV_LOC_TRANSACTIONS';
1223 SELECT 'Y'
1224 INTO v_delete_allowed
1225 FROM sys.dual
1226 WHERE NOT EXISTS (
1227 SELECT null
1228 FROM RCV_TRANSACTIONS
1229 WHERE deliver_to_location_id = p_location_id
1230 OR location_id = p_location_id
1231 );
1232
1233 l_msg := 'RCV_LOC_TRANSACTIONS_INTERFACE';
1234 SELECT 'Y'
1235 INTO v_delete_allowed
1236 FROM sys.dual
1237 WHERE NOT EXISTS (
1238 SELECT null
1239 FROM RCV_TRANSACTIONS_INTERFACE
1240 WHERE ship_to_location_id = p_location_id
1241 OR deliver_to_location_id = p_location_id
1242 OR location_id = p_location_id
1243 );
1244
1245
1246 EXCEPTION
1247
1248 WHEN NO_DATA_FOUND THEN
1249 -- this means that the location already exists in our base tables.
1250 -- set an error message and raise an error to disallow the
1251 -- deletion of the location.
1252 hr_utility.set_message(201, l_msg);
1253 hr_utility.raise_error;
1254
1255 END;
1256
1257 END PO_PREDEL_VALIDATION;
1258
1259
1260 END PO_LOCATIONS_S;