1 PACKAGE BODY hz_location_profiles_pkg AS
2 /*$Header: ARHLOCPB.pls 115.1 2003/08/14 00:22:41 acng noship $ */
3
4 PROCEDURE insert_row (
5 x_location_profile_id IN OUT NOCOPY NUMBER,
6 x_location_id IN NUMBER,
7 x_actual_content_source IN VARCHAR2,
8 x_effective_start_date IN DATE,
9 x_effective_end_date IN DATE,
10 x_validation_sst_flag IN VARCHAR2,
11 x_validation_status_code IN VARCHAR2,
12 x_date_validated IN DATE,
13 x_address1 IN VARCHAR2,
14 x_address2 IN VARCHAR2,
15 x_address3 IN VARCHAR2,
16 x_address4 IN VARCHAR2,
17 x_city IN VARCHAR2,
18 x_postal_code IN VARCHAR2,
19 x_prov_state_admin_code IN VARCHAR2,
20 x_county IN VARCHAR2,
21 x_country IN VARCHAR2,
22 x_object_version_number IN NUMBER
23 ) IS
24
25 l_success VARCHAR2(1) := 'N';
26 l_primary_key_passed BOOLEAN := FALSE;
27
28 BEGIN
29
30 -- The following lines are used to take care of the situation
31 -- when content_source_type is not USER_ENTERED, because of
32 -- policy funcation, when we do unique validation, we cannot
33 -- see all of the records. Thus, we have to double check here
34 -- and raise corresponding exception. We donot need to do anything
35 -- for those tables without polity functions.
36
37 IF x_location_profile_id IS NOT NULL AND
38 x_location_profile_id <> fnd_api.g_miss_num
39 THEN
40 l_primary_key_passed := TRUE;
41 END IF;
42
43 WHILE l_success = 'N' LOOP
44 BEGIN
45 INSERT INTO hz_location_profiles (
46 location_profile_id,
47 location_id,
48 actual_content_source,
49 effective_start_date,
50 effective_end_date,
51 validation_sst_flag,
52 validation_status_code,
53 date_validated,
54 address1,
55 address2,
56 address3,
57 address4,
58 city,
59 postal_code,
60 prov_state_admin_code,
61 county,
62 country,
63 last_update_date,
64 last_updated_by,
65 creation_date,
66 created_by,
67 last_update_login,
68 object_version_number
69 )
70 VALUES (
71 DECODE(x_location_profile_id,
72 fnd_api.g_miss_num, hz_location_profiles_s.NEXTVAL,
73 NULL, hz_location_profiles_s.NEXTVAL,
74 x_location_profile_id),
75 DECODE(x_location_id, fnd_api.g_miss_num, NULL, x_location_id),
76 DECODE(x_actual_content_source, fnd_api.g_miss_char, NULL, x_actual_content_source),
77 DECODE(x_effective_start_date, fnd_api.g_miss_date, TO_DATE(NULL), x_effective_start_date),
78 DECODE(x_effective_end_date, fnd_api.g_miss_date, TO_DATE(NULL), x_effective_end_date),
79 DECODE(x_validation_sst_flag, fnd_api.g_miss_char, NULL, x_validation_sst_flag),
80 DECODE(x_validation_status_code, fnd_api.g_miss_char, NULL, x_validation_status_code),
81 DECODE(x_date_validated, fnd_api.g_miss_date, TO_DATE(NULL), x_date_validated),
82 DECODE(x_address1, fnd_api.g_miss_char, NULL, x_address1),
83 DECODE(x_address2, fnd_api.g_miss_char, NULL, x_address2),
84 DECODE(x_address3, fnd_api.g_miss_char, NULL, x_address3),
85 DECODE(x_address4, fnd_api.g_miss_char, NULL, x_address4),
86 DECODE(x_city, fnd_api.g_miss_char, NULL, x_city),
87 DECODE(x_postal_code, fnd_api.g_miss_char, NULL, x_postal_code),
88 DECODE(x_prov_state_admin_code, fnd_api.g_miss_char, NULL, x_prov_state_admin_code),
89 DECODE(x_county, fnd_api.g_miss_char, NULL, x_county),
90 DECODE(x_country, fnd_api.g_miss_char, NULL, x_country),
91 hz_utility_v2pub.last_update_date,
92 hz_utility_v2pub.last_updated_by,
93 hz_utility_v2pub.creation_date,
94 hz_utility_v2pub.created_by,
95 hz_utility_v2pub.last_update_login,
96 DECODE(x_object_version_number, fnd_api.g_miss_num, NULL, x_object_version_number)
97 ) RETURNING
98 location_profile_id
99 INTO
100 x_location_profile_id;
101
102 l_success := 'Y';
103
104 EXCEPTION
105 WHEN DUP_VAL_ON_INDEX THEN
106 IF INSTRB(SQLERRM, 'HZ_LOCATION_PROFILES_U1') <> 0 OR
107 INSTRB(SQLERRM, 'HZ_LOCATION_PROFILES_PK') <> 0
108 THEN
109 IF l_primary_key_passed THEN
110 fnd_message.set_name('AR', 'HZ_API_DUPLICATE_COLUMN');
111 fnd_message.set_token('COLUMN', 'location_profile_id');
112 fnd_msg_pub.add;
113 RAISE fnd_api.g_exc_error;
114 END IF;
115
116 DECLARE
117 l_temp_profile_id NUMBER;
118 l_max_profile_id NUMBER;
119 BEGIN
120 l_temp_profile_id := 0;
121 SELECT max(LOCATION_PROFILE_ID) INTO l_max_profile_id
122 FROM HZ_LOCATION_PROFILES;
123 WHILE l_temp_profile_id <= l_max_profile_id LOOP
124 SELECT HZ_LOCATION_PROFILES_S.NEXTVAL
125 INTO l_temp_profile_id FROM dual;
126 END LOOP;
127 END;
128
129 ELSE
130 RAISE;
131 END IF;
132 END;
133 END LOOP;
134 END insert_row;
135
136 PROCEDURE update_row (
137 x_rowid IN OUT NOCOPY VARCHAR2,
138 x_location_profile_id IN NUMBER,
139 x_location_id IN NUMBER,
140 x_actual_content_source IN VARCHAR2,
141 x_effective_start_date IN DATE,
142 x_effective_end_date IN DATE,
143 x_validation_sst_flag IN VARCHAR2,
144 x_validation_status_code IN VARCHAR2,
145 x_date_validated IN DATE,
146 x_address1 IN VARCHAR2,
147 x_address2 IN VARCHAR2,
148 x_address3 IN VARCHAR2,
149 x_address4 IN VARCHAR2,
150 x_city IN VARCHAR2,
151 x_postal_code IN VARCHAR2,
152 x_prov_state_admin_code IN VARCHAR2,
153 x_county IN VARCHAR2,
154 x_country IN VARCHAR2,
155 x_object_version_number IN NUMBER
156 ) IS
157 BEGIN
158 UPDATE hz_location_profiles
159 SET location_profile_id = DECODE(x_location_profile_id,
160 NULL, location_profile_id,
161 fnd_api.g_miss_num, NULL,
162 x_location_profile_id),
163 location_id = DECODE(x_location_id,
164 NULL, location_id,
165 fnd_api.g_miss_num, NULL,
166 x_location_id),
167 actual_content_source = DECODE(x_actual_content_source,
168 NULL, actual_content_source,
169 fnd_api.g_miss_char, NULL,
170 x_actual_content_source),
171 effective_start_date = DECODE(x_effective_start_date,
172 NULL, effective_start_date,
173 fnd_api.g_miss_date, NULL,
174 x_effective_start_date),
175 effective_end_date = DECODE(x_effective_end_date,
176 NULL, effective_end_date,
177 fnd_api.g_miss_date, NULL,
178 x_effective_end_date),
179 validation_sst_flag = DECODE(x_validation_sst_flag,
180 NULL, validation_sst_flag,
181 fnd_api.g_miss_char, NULL,
182 x_validation_sst_flag),
183 validation_status_code = DECODE(x_validation_status_code,
184 NULL, validation_status_code,
185 fnd_api.g_miss_char, NULL,
186 x_validation_status_code),
187 date_validated = DECODE(x_date_validated,
188 NULL, date_validated,
189 fnd_api.g_miss_date, NULL,
190 x_date_validated),
191 address1 = DECODE(x_address1,
192 NULL, address1,
193 fnd_api.g_miss_char, NULL,
194 x_address1),
195 address2 = DECODE(x_address2,
196 NULL, address2,
197 fnd_api.g_miss_char, NULL,
198 x_address2),
199 address3 = DECODE(x_address3,
200 NULL, address3,
201 fnd_api.g_miss_char, NULL,
202 x_address3),
203 address4 = DECODE(x_address4,
204 NULL, address4,
205 fnd_api.g_miss_char, NULL,
206 x_address4),
207 city = DECODE(x_city,
208 NULL, city,
209 fnd_api.g_miss_char, NULL,
210 x_city),
211 postal_code = DECODE(x_postal_code,
212 NULL, postal_code,
213 fnd_api.g_miss_char, NULL,
214 x_postal_code),
215 prov_state_admin_code = DECODE(x_prov_state_admin_code,
216 NULL, prov_state_admin_code,
217 fnd_api.g_miss_char, NULL,
218 x_prov_state_admin_code),
219 county = DECODE(x_county,
220 NULL, county,
221 fnd_api.g_miss_char, NULL,
222 x_county),
223 country = DECODE(x_country,
224 NULL, country,
225 fnd_api.g_miss_char, NULL,
226 x_country),
227 last_update_date = hz_utility_v2pub.last_update_date,
228 last_updated_by = hz_utility_v2pub.last_updated_by,
229 creation_date = creation_date,
230 created_by = created_by,
231 last_update_login = hz_utility_v2pub.last_update_login,
232 object_version_number = DECODE(x_object_version_number,
233 NULL, object_version_number,
234 fnd_api.g_miss_num, NULL,
235 x_object_version_number)
236 WHERE ROWID = x_rowid;
237
238 IF (SQL%NOTFOUND) THEN
239 RAISE NO_DATA_FOUND;
240 END IF;
241 END update_row;
242
243
244 PROCEDURE lock_row (
245 x_rowid IN OUT NOCOPY VARCHAR2,
246 x_location_profile_id IN NUMBER,
247 x_location_id IN NUMBER,
248 x_actual_content_source IN VARCHAR2,
249 x_effective_start_date IN DATE,
250 x_effective_end_date IN DATE,
251 x_validation_sst_flag IN VARCHAR2,
252 x_validation_status_code IN VARCHAR2,
253 x_date_validated IN DATE,
254 x_address1 IN VARCHAR2,
255 x_address2 IN VARCHAR2,
256 x_address3 IN VARCHAR2,
257 x_address4 IN VARCHAR2,
258 x_city IN VARCHAR2,
259 x_postal_code IN VARCHAR2,
260 x_prov_state_admin_code IN VARCHAR2,
261 x_county IN VARCHAR2,
262 x_country IN VARCHAR2,
263 x_last_update_date IN DATE,
264 x_last_updated_by IN NUMBER,
265 x_creation_date IN DATE,
266 x_created_by IN NUMBER,
267 x_last_update_login IN NUMBER,
268 x_object_version_number IN NUMBER
269 ) IS
270
271 CURSOR c IS
272 SELECT *
273 FROM hz_location_profiles
274 WHERE ROWID = x_rowid
275 FOR UPDATE NOWAIT;
276
277 recinfo c%ROWTYPE;
278 BEGIN
279 OPEN c;
280 FETCH c INTO recinfo;
281 IF (C%NOTFOUND) THEN
282 CLOSE c;
283 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
284 app_exception.raise_exception;
285 END IF;
286 CLOSE c;
287
288 IF (((recinfo.location_profile_id = x_location_profile_id)
289 OR ((recinfo.location_profile_id IS NULL)
290 AND (x_location_profile_id IS NULL)))
291 AND ((recinfo.location_id = x_location_id)
292 OR ((recinfo.location_id IS NULL)
293 AND (x_location_id IS NULL)))
294 AND ((recinfo.actual_content_source = x_actual_content_source)
295 OR ((recinfo.actual_content_source IS NULL)
296 AND (x_actual_content_source IS NULL)))
297 AND ((recinfo.effective_start_date = x_effective_start_date)
298 OR ((recinfo.effective_start_date IS NULL)
299 AND (x_effective_start_date IS NULL)))
300 AND ((recinfo.effective_end_date = x_effective_end_date)
301 OR ((recinfo.effective_end_date IS NULL)
302 AND (x_effective_end_date IS NULL)))
303 AND ((recinfo.validation_sst_flag = x_validation_sst_flag)
304 OR ((recinfo.validation_sst_flag IS NULL)
305 AND (x_validation_sst_flag IS NULL)))
306 AND ((recinfo.validation_status_code = x_validation_status_code)
307 OR ((recinfo.validation_status_code IS NULL)
308 AND (x_validation_status_code IS NULL)))
309 AND ((recinfo.date_validated = x_date_validated)
310 OR ((recinfo.date_validated IS NULL)
311 AND (x_date_validated IS NULL)))
312 AND ((recinfo.address1 = x_address1)
313 OR ((recinfo.address1 IS NULL)
314 AND (x_address1 IS NULL)))
315 AND ((recinfo.address2 = x_address2)
316 OR ((recinfo.address2 IS NULL)
317 AND (x_address2 IS NULL)))
318 AND ((recinfo.address3 = x_address3)
319 OR ((recinfo.address3 IS NULL)
320 AND (x_address3 IS NULL)))
321 AND ((recinfo.address4 = x_address4)
322 OR ((recinfo.address4 IS NULL)
323 AND (x_address4 IS NULL)))
324 AND ((recinfo.city = x_city)
325 OR ((recinfo.city IS NULL)
326 AND (x_city IS NULL)))
327 AND ((recinfo.postal_code = x_postal_code)
328 OR ((recinfo.postal_code IS NULL)
329 AND (x_postal_code IS NULL)))
330 AND ((recinfo.prov_state_admin_code = x_prov_state_admin_code)
331 OR ((recinfo.prov_state_admin_code IS NULL)
332 AND (x_prov_state_admin_code IS NULL)))
333 AND ((recinfo.county = x_county)
334 OR ((recinfo.county IS NULL)
335 AND (x_county IS NULL)))
336 AND ((recinfo.country = x_country)
337 OR ((recinfo.country IS NULL)
338 AND (x_country IS NULL)))
339 AND ((recinfo.last_update_date = x_last_update_date)
340 OR ((recinfo.last_update_date IS NULL)
341 AND (x_last_update_date IS NULL)))
342 AND ((recinfo.last_updated_by = x_last_updated_by)
343 OR ((recinfo.last_updated_by IS NULL)
344 AND (x_last_updated_by IS NULL)))
345 AND ((recinfo.creation_date = x_creation_date)
346 OR ((recinfo.creation_date IS NULL)
347 AND (x_creation_date IS NULL)))
348 AND ((recinfo.created_by = x_created_by)
349 OR ((recinfo.created_by IS NULL)
350 AND (x_created_by IS NULL)))
351 AND ((recinfo.last_update_login = x_last_update_login)
352 OR ((recinfo.last_update_login IS NULL)
353 AND (x_last_update_login IS NULL)))
354 AND ((recinfo.object_version_number = x_object_version_number)
355 OR ((recinfo.object_version_number IS NULL)
356 AND (x_object_version_number IS NULL)))
357 )
358 THEN
359 RETURN;
360 ELSE
361 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
362 app_exception.raise_exception;
363 END IF;
364 END lock_row;
365
366
367 PROCEDURE delete_row (x_location_profile_id IN NUMBER) IS
368 BEGIN
369 DELETE FROM hz_location_profiles
370 WHERE location_profile_id = x_location_profile_id;
371
372 IF (SQL%NOTFOUND) THEN
373 RAISE NO_DATA_FOUND;
374 END IF;
375 END delete_row;
376
377 END hz_location_profiles_pkg;
[Home] [Help]