PACKAGE BODY: APPS.PO_LOCATIONS_SV2

Source

1 PACKAGE BODY PO_LOCATIONS_SV2 as
2 /* $Header: POXCOL1B.pls 120.2.12010000.2 2009/05/04 06:25:43 rohbansa ship $*/
3 
4 /*=============================  PO_LOCATIONS_SV2 ===========================*/
5 
6 /*===========================================================================
7 
8   FUNCTION NAME:	val_location()
9 
10 ===========================================================================*/
11 function val_location(x_location_id        in number,
12 		      x_destination_org_id in number,
13 		      x_ship_to       	   in varchar2,
14 		      x_receiving	   in varchar2,
15 		      x_val_internal	   in varchar2,
16                       x_source_org_id      in number default null )
17 return boolean is
18 
19   x_progress       varchar2(3) := NULL;
20   x_location_count number      := 0;
21 
22 begin
23 
24   x_progress := '010';
25 
26   -- Bug 5028505, Added validation to check if customer location
27   -- association exist in the source Organization Operating Unit
28  if ( x_source_org_id      is not null) then
29   SELECT count(1)
30   INTO   x_location_count
31   FROM   hr_locations_all hl
32   WHERE  hl.location_id = x_location_id
33   AND    sysdate < nvl(hl.inactive_date, sysdate + 1)
34   AND    nvl(hl.ship_to_site_flag, 'N') = decode(x_ship_to, 'Y', x_ship_to,
35 					         nvl(hl.ship_to_site_flag, 'N'))
36   AND	 nvl(hl.receiving_site_flag, 'N') = decode(x_receiving, 'Y', x_receiving,
37 						   nvl(hl.receiving_site_flag, 'N'))
38   AND    nvl(hl.inventory_organization_id, x_destination_org_id) =
39 					   x_destination_org_id
40   AND    (nvl(x_val_internal, 'N') = 'N'
41           OR
42           x_location_id in
43   	  (SELECT pla.location_id
44    	   FROM   po_location_associations_all pla,
45                   org_organization_definitions org       -- Bug 5028505
46 	   WHERE  pla.location_id = x_location_id
47            AND    org.organization_id= x_source_org_id   -- Bug 5028505
48            AND    org.operating_unit = pla.org_id ));     -- Bug 5028505
49 else
50  SELECT count(1)
51  	   INTO   x_location_count
52  	   FROM   hr_locations_all hl
53  	   WHERE  hl.location_id = x_location_id
54  	   AND    sysdate < nvl(hl.inactive_date, sysdate + 1)
55  	   AND    nvl(hl.ship_to_site_flag, 'N') = decode(x_ship_to, 'Y', x_ship_to,
56  	                                                  nvl(hl.ship_to_site_flag, 'N'))
57  	   AND    nvl(hl.receiving_site_flag, 'N') = decode(x_receiving, 'Y', x_receiving,
58  	                                                    nvl(hl.receiving_site_flag, 'N'))
59  	   AND    nvl(hl.inventory_organization_id, x_destination_org_id) =
60  	                                            x_destination_org_id
61  	   AND    (nvl(x_val_internal, 'N') = 'N'
62  	           OR
63  	           x_location_id in
64  	           (SELECT pla.location_id
65  	            FROM   po_location_associations pla
66  	            WHERE  pla.location_id = x_location_id));
67 
68  end if;
69 
70   x_progress := '020';
71   -- bug 1942696 hr_location changes to reflect the new view
72   if (x_location_count = 0) then
73     SELECT count(1)
74     INTO   x_location_count
75     FROM   hz_locations hz
76     WHERE  hz.location_id = x_location_id
77     AND    sysdate < nvl(hz.address_expiration_date, sysdate + 1);
78   end if;
79 
80   x_progress := '030';
81   if (x_location_count = 1) then
82     return (TRUE);
83   else
84     return (FALSE);
85   end if;
86 
87 exception
88 
89   when others then
90     po_message_s.sql_error('val_location', x_progress, sqlcode);
91     raise;
92 
93 end val_location;
94 
95 END PO_LOCATIONS_SV2;