1 PACKAGE pa_location_utils AS
2 -- $Header: PALOUTLS.pls 120.1 2005/08/19 16:35:50 mwasowic noship $
3
4 ----------------------------------------------------------------------------------
5 -- Start of comments
6 --
7 -- API Name : pa_location_utils.Check_Country_Name_Or_Code
8 --
9 -- Type : Public
10 --
11 -- Pre-reqs : None
12 --
13 -- Function : This procedure does the the following
14 -- 1>. If a country name is passed it derrives the country code.
15 -- 2>. If a country code is passed, the code is validated based on
16 -- the check id flag.
17 --
18 -- If a valid country is not found an invalid country error code
19 -- is returned. Also is there are more that one records retrieved
20 -- then it will flag an error of ambigous country.
21 --
22 --
23 -- Parameters :
24 -- p_country_code IN VARCHAR2
25 -- p_country_name IN VARCHAR2
26 -- p_check_id_flag IN VARCHAR2
27 -- x_country_code OUT VARCHAR2
28 -- x_return_status OUT VARCHAR2
29 -- x_error_message_code OUT VARCHAR2
30
31 -- Version : Initial version 115.0
32 --
33 -- End of comments
34 ----------------------------------------------------------------------------------
35
36 PROCEDURE Check_Country_Name_Or_Code
37 ( p_country_code IN VARCHAR2
38 ,p_country_name IN VARCHAR2
39 ,p_check_id_flag IN VARCHAR2 default 'A'
40 ,x_country_code OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
41 ,x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
42 ,x_error_message_code OUT NOCOPY VARCHAR2); --File.Sql.39 bug 4440895
43
44 ----------------------------------------------------------------------------------
45 -- Start of comments
46 --
47 -- API Name : pa_location_utils.Get_ORG_Location_Details
48 --
49 -- Type : Public
50 --
51 -- Pre-reqs : None
52 --
53 -- Procedure : This procedure accepts the organization id and returns the following
54 -- 1>. Country name
55 -- 2>. City
56 -- 3>. Region/State
57 -- 4>. Country Code
58 --
59 -- It is used to get the location of an organization.
60 --
61 -- Parameters :
62 -- p_organization_id IN NUMBER
63 -- x_country_name OUT VARCHAR2
64 -- x_city OUT VARCHAR2
65 -- x_region OUT VARCHAR2
66 -- x_country_code OUT VARCHAR2
67 -- x_return_status OUT VARCHAR2
68 -- x_error_message_code OUT VARCHAR2
69
70 -- Version : Initial version 115.0
71 --
72 -- End of comments
73 ----------------------------------------------------------------------------------
74 PROCEDURE Get_ORG_Location_Details
75 ( p_organization_id IN NUMBER
76 ,x_country_name OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
77 ,x_city OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
78 ,x_region OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
79 ,x_country_code OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
80 ,x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
81 ,x_error_message_code OUT NOCOPY VARCHAR2); --File.Sql.39 bug 4440895
82
83 ----------------------------------------------------------------------------------
84 -- Start of comments
85 --
86 -- API Name : pa_location_utils.Get_EMP_Location_Details
87 --
88 -- Type : Public
89 --
90 -- Pre-reqs : None
91 --
92 -- Procedure : This procedure accepts the person id and returns the following
93 -- 1>. Country name
94 -- 2>. City
95 -- 3>. Region/State
96 -- 4>. Country Code
97 --
98 -- It is used to get the primary location of an person.
99 --
100 --
101 --
102 -- Parameters :
103 -- p_person_id IN NUMBER
104 -- x_country_name OUT VARCHAR2
105 -- x_city OUT VARCHAR2
106 -- x_region OUT VARCHAR2
107 -- x_country_code OUT VARCHAR2
108 -- x_return_status OUT VARCHAR2
109 -- x_error_message_code OUT VARCHAR2
110
111 -- Version : Initial version 115.0
112 --
113 -- End of comments
114 ----------------------------------------------------------------------------------
115 PROCEDURE Get_EMP_Location_Details
116 ( p_person_id IN NUMBER
117 ,p_assign_date IN DATE
118 ,x_country_name OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
119 ,x_city OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
120 ,x_region OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
121 ,x_country_code OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
122 ,x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
123 ,x_error_message_code OUT NOCOPY VARCHAR2); --File.Sql.39 bug 4440895
124
125 ----------------------------------------------------------------------------------
126 -- Start of comments
127 --
128 -- API Name : pa_location_utils.Get_PA_Location_Details
129 --
130 -- Type : Public
131 --
132 -- Pre-reqs : None
133 --
134 -- Procedure : This procedure accepts the location id and returns the following
135 -- 1>. Country name
136 -- 2>. City
137 -- 3>. Region/State
138 -- 4>. Country Code
139 --
140 -- It is used to get the project location.
141 --
142 --
143 --
144 -- Parameters :
145 -- p_location_id IN NUMBER
146 -- x_country_name OUT VARCHAR2
147 -- x_city OUT VARCHAR2
148 -- x_region OUT VARCHAR2
149 -- x_country_code OUT VARCHAR2
150 -- x_return_status OUT VARCHAR2
151 -- x_error_message_code OUT VARCHAR2
152
153 -- Version : Initial version 11.0
154 --
155 -- End of comments
156 ----------------------------------------------------------------------------------
157 PROCEDURE Get_PA_Location_Details
158 ( p_location_id IN NUMBER
159 ,x_country_name OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
160 ,x_city OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
161 ,x_region OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
162 ,x_country_code OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
163 ,x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
164 ,x_error_message_code OUT NOCOPY VARCHAR2); --File.Sql.39 bug 4440895
165
166 ----------------------------------------------------------------------------------
167 -- Start of comments
168 --
169 -- API Name : pa_location_utils.Check_Location_Exists
170 --
171 -- Type : Public
172 --
173 -- Pre-reqs : None
174 --
175 -- Procedure : This procedure is used to check if a valid project location
176 -- for that combination of country, city and region.
177 --
178 --
179 --
180 -- Parameters :
181 -- p_country_code IN VARCHAR2
182 -- p_city IN VARCHAR2
183 -- p_region IN VARCHAR2
184 -- x_location_id OUT NUMBER
185 -- x_return_status OUT VARCHAR2
186
187 -- Version : Initial version 115.0
188 --
189 -- End of comments
190 ----------------------------------------------------------------------------------
191 PROCEDURE Check_Location_Exists( p_country_code IN VARCHAR2
192 ,p_city IN VARCHAR2
193 ,p_region IN VARCHAR2
194 ,x_location_id OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
195 ,x_return_status OUT NOCOPY VARCHAR2); --File.Sql.39 bug 4440895
196
197 ----------------------------------------------------------------------------------
198 -- Start of comments
199 --
200 -- API Name : pa_location_utils.Get_Location
201 --
202 -- Type : Public
203 --
204 -- Pre-reqs : None
205 --
206 -- Procedure : This procedure accepts country code, city and region and input
207 -- parameters, checks if the location exists and returns the location
208 -- id if the location exists. If the location does not exist a new
209 -- location is created with the country, city and region and the new
210 -- location id is returned.
211 --
212 --
213 -- Parameters :
214 -- p_country_code IN VARCHAR2
215 -- p_city IN VARCHAR2
216 -- p_region IN VARCHAR2
217 -- x_location_id OUT NUMBER
218 -- x_return_status OUT VARCHAR2
219
220 -- Version : Initial version 115.0
221 --
222 -- End of comments
223 ----------------------------------------------------------------------------------
224 PROCEDURE Get_Location ( p_country_code IN VARCHAR2
225 ,p_city IN VARCHAR2
226 ,p_region IN VARCHAR2
227 ,x_location_id OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
228 ,x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
229 ,x_error_message_code OUT NOCOPY VARCHAR2); --File.Sql.39 bug 4440895
230
231
232 ----------------------------------------------------------------------------------
233 -- Start of comments
234 --
235 -- API Name : pa_location_utils.Get_Country_Code_Name
236 --
237 -- Procedure : This procedure returns country code and count name
238 -- from location id.
239 --
240 -- Parameters :
241 -- p_location IN NUMBER
242 -- x_country_code IN VARCHAR2
243 -- x_country_name IN VARCHAR2
244 --
245 -- End of comments
246 -------------------------------------------------------------------------------
247 Procedure Get_Country_Code_Name(p_location_id IN NUMBER,
248 x_country_code OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
249 x_country_name OUT NOCOPY VARCHAR2); --File.Sql.39 bug 4440895
250
251 end pa_location_utils ;
[Home] [Help]