[Home] [Help]
PACKAGE BODY: APPS.JTF_LOC_AREAS_PKG
Source
1 package body JTF_LOC_AREAS_PKG as
2 /* $Header: jtflloab.pls 120.2 2005/08/18 23:07:42 stopiwal ship $ */
3 procedure INSERT_ROW (
4 X_ROWID IN OUT NOCOPY /* file.sql.39 change */ VARCHAR2,
5 X_LOCATION_AREA_ID in NUMBER,
6 X_OBJECT_VERSION_NUMBER in NUMBER,
7 X_PARENT_LOCATION_AREA_ID in NUMBER,
8 X_REQUEST_ID in NUMBER,
9 X_PROGRAM_APPLICATION_ID in NUMBER,
10 X_PROGRAM_ID in NUMBER,
11 X_PROGRAM_UPDATE_DATE in DATE,
12 X_ORIG_SYSTEM_REF in VARCHAR2,
13 X_ORIG_SYSTEM_ID in NUMBER,
14 X_LOCATION_TYPE_CODE in VARCHAR2,
15 X_START_DATE_ACTIVE in DATE,
16 X_END_DATE_ACTIVE in DATE,
17 X_LOCATION_AREA_CODE in VARCHAR2,
18 X_LOCATION_AREA_NAME in VARCHAR2,
19 X_LOCATION_AREA_DESCRIPTION in VARCHAR2,
20 X_CREATION_DATE in DATE,
21 X_CREATED_BY in NUMBER,
22 X_LAST_UPDATE_DATE in DATE,
23 X_LAST_UPDATED_BY in NUMBER,
24 X_LAST_UPDATE_LOGIN in NUMBER
25 ) is
26 cursor C is select ROWID from JTF_LOC_AREAS_B
27 where LOCATION_AREA_ID = X_LOCATION_AREA_ID
28 ;
29 begin
30 insert into JTF_LOC_AREAS_B (
31 LOCATION_AREA_ID,
32 OBJECT_VERSION_NUMBER,
33 PARENT_LOCATION_AREA_ID,
34 REQUEST_ID,
35 PROGRAM_APPLICATION_ID,
36 PROGRAM_ID,
37 PROGRAM_UPDATE_DATE,
38 ORIG_SYSTEM_REF,
39 ORIG_SYSTEM_ID,
40 LOCATION_TYPE_CODE,
41 START_DATE_ACTIVE,
42 END_DATE_ACTIVE,
43 LOCATION_AREA_CODE,
44 CREATION_DATE,
45 CREATED_BY,
46 LAST_UPDATE_DATE,
47 LAST_UPDATED_BY,
48 LAST_UPDATE_LOGIN
49 ) values (
50 X_LOCATION_AREA_ID,
51 X_OBJECT_VERSION_NUMBER,
52 X_PARENT_LOCATION_AREA_ID,
53 X_REQUEST_ID,
54 X_PROGRAM_APPLICATION_ID,
55 X_PROGRAM_ID,
56 X_PROGRAM_UPDATE_DATE,
57 X_ORIG_SYSTEM_REF,
58 X_ORIG_SYSTEM_ID,
59 X_LOCATION_TYPE_CODE,
60 X_START_DATE_ACTIVE,
61 X_END_DATE_ACTIVE,
62 X_LOCATION_AREA_CODE,
63 X_CREATION_DATE,
64 X_CREATED_BY,
65 X_LAST_UPDATE_DATE,
66 X_LAST_UPDATED_BY,
67 X_LAST_UPDATE_LOGIN
68 );
69
70 insert into JTF_LOC_AREAS_TL (
71 LOCATION_AREA_ID,
72 LAST_UPDATE_DATE,
73 LAST_UPDATED_BY,
74 CREATION_DATE,
75 CREATED_BY,
76 LAST_UPDATE_LOGIN,
77 LOCATION_AREA_NAME,
78 LOCATION_AREA_DESCRIPTION,
79 LANGUAGE,
80 SOURCE_LANG
81 ) select
82 X_LOCATION_AREA_ID,
83 X_LAST_UPDATE_DATE,
84 X_LAST_UPDATED_BY,
85 X_CREATION_DATE,
86 X_CREATED_BY,
87 X_LAST_UPDATE_LOGIN,
88 X_LOCATION_AREA_NAME,
89 X_LOCATION_AREA_DESCRIPTION,
90 L.LANGUAGE_CODE,
91 userenv('LANG')
92 from FND_LANGUAGES L
93 where L.INSTALLED_FLAG in ('I', 'B')
94 and not exists
95 (select NULL
96 from JTF_LOC_AREAS_TL T
97 where T.LOCATION_AREA_ID = X_LOCATION_AREA_ID
98 and T.LANGUAGE = L.LANGUAGE_CODE);
99
100 open c;
101 fetch c into X_ROWID;
102 if (c%notfound) then
103 close c;
104 raise no_data_found;
105 end if;
106 close c;
107
108 end INSERT_ROW;
109
110 procedure UPDATE_ROW (
111 X_LOCATION_AREA_ID in NUMBER,
112 X_OBJECT_VERSION_NUMBER in NUMBER,
113 X_PARENT_LOCATION_AREA_ID in NUMBER,
114 X_REQUEST_ID in NUMBER,
115 X_PROGRAM_APPLICATION_ID in NUMBER,
116 X_PROGRAM_ID in NUMBER,
117 X_PROGRAM_UPDATE_DATE in DATE,
118 X_ORIG_SYSTEM_REF in VARCHAR2,
119 X_ORIG_SYSTEM_ID in NUMBER,
120 X_LOCATION_TYPE_CODE in VARCHAR2,
121 X_START_DATE_ACTIVE in DATE,
122 X_END_DATE_ACTIVE in DATE,
123 X_LOCATION_AREA_CODE in VARCHAR2,
124 X_LOCATION_AREA_NAME in VARCHAR2,
125 X_LOCATION_AREA_DESCRIPTION in VARCHAR2,
126 X_LAST_UPDATE_DATE in DATE,
127 X_LAST_UPDATED_BY in NUMBER,
128 X_LAST_UPDATE_LOGIN in NUMBER
129 ) is
130 begin
131 update JTF_LOC_AREAS_B set
132 OBJECT_VERSION_NUMBER = X_OBJECT_VERSION_NUMBER,
133 PARENT_LOCATION_AREA_ID = X_PARENT_LOCATION_AREA_ID,
134 REQUEST_ID = X_REQUEST_ID,
135 PROGRAM_APPLICATION_ID = X_PROGRAM_APPLICATION_ID,
136 PROGRAM_ID = X_PROGRAM_ID,
137 PROGRAM_UPDATE_DATE = X_PROGRAM_UPDATE_DATE,
138 ORIG_SYSTEM_REF = X_ORIG_SYSTEM_REF,
139 ORIG_SYSTEM_ID = X_ORIG_SYSTEM_ID,
140 LOCATION_TYPE_CODE = X_LOCATION_TYPE_CODE,
141 START_DATE_ACTIVE = X_START_DATE_ACTIVE,
142 END_DATE_ACTIVE = X_END_DATE_ACTIVE,
143 LOCATION_AREA_CODE = X_LOCATION_AREA_CODE,
144 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
145 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
146 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN
147 where LOCATION_AREA_ID = X_LOCATION_AREA_ID;
148
149 if (sql%notfound) then
150 raise no_data_found;
151 end if;
152
153 update JTF_LOC_AREAS_TL set
154 LOCATION_AREA_NAME = X_LOCATION_AREA_NAME,
155 LOCATION_AREA_DESCRIPTION = X_LOCATION_AREA_DESCRIPTION,
156 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
157 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
158 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN,
159 SOURCE_LANG = userenv('LANG')
160 where LOCATION_AREA_ID = X_LOCATION_AREA_ID
161 and userenv('LANG') in (LANGUAGE, SOURCE_LANG);
162
163 if (sql%notfound) then
164 raise no_data_found;
165 end if;
166 end UPDATE_ROW;
167
168
169 procedure UPDATE_ROW (
170 X_LOCATION_AREA_ID in NUMBER,
171 X_OBJECT_VERSION_NUMBER in NUMBER,
172 X_PARENT_LOCATION_AREA_ID in NUMBER,
173 X_REQUEST_ID in NUMBER,
174 X_PROGRAM_APPLICATION_ID in NUMBER,
175 X_PROGRAM_ID in NUMBER,
176 X_PROGRAM_UPDATE_DATE in DATE,
177 X_ORIG_SYSTEM_REF in VARCHAR2,
178 X_ORIG_SYSTEM_ID in NUMBER,
179 X_LOCATION_TYPE_CODE in VARCHAR2,
180 X_START_DATE_ACTIVE in DATE,
181 X_END_DATE_ACTIVE in DATE,
182 X_LOCATION_AREA_CODE in VARCHAR2,
183 X_LOCATION_AREA_NAME in VARCHAR2,
184 X_LOCATION_AREA_DESCRIPTION in VARCHAR2,
185 X_LAST_UPDATE_DATE in DATE,
186 X_LAST_UPDATED_BY in NUMBER,
187 X_LAST_UPDATE_LOGIN in NUMBER,
188 X_RETURN_STATUS OUT NOCOPY /* file.sql.39 change */ varchar2,
189 X_MSG_COUNT OUT NOCOPY /* file.sql.39 change */ number,
190 X_MSG_DATA OUT NOCOPY /* file.sql.39 change */ varchar2
191 ) is
192 CURSOR child_end_date IS
193 SELECT end_Date_active
194 FROM jtf_loc_areas_b
195 WHERE parent_location_area_id = x_location_area_id;
196
197 Cursor locationName is
198 select location_Area_name
199 from jtf_loc_Areas_vl
200 where location_Area_id = x_location_Area_id;
201
202 l_loc_area_name varchar2(240);
203 begin
204
205 X_Return_Status := FND_API.G_RET_STS_SUCCESS;
206
207 if x_end_date_active is not NULL then
208 for i in child_end_date loop
209 if i.end_date_Active is null or i.end_Date_Active > x_end_Date_active then
210 x_return_Status := FND_API.G_RET_STS_ERROR;
211
212 -- get the location name
213 Open locationName;
214 Fetch locationName into l_loc_area_name;
215 if (locationName%notfound) then
216 raise no_data_found;
217 end if;
218 Close locationName;
219 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
220 FND_MESSAGE.Set_Name('JTF', 'JTF_DELETE_LOC_AREA');
221 FND_MESSAGE.Set_Token('AREA', l_loc_area_name, FALSE);
222 FND_MSG_PUB.ADD;
223 END IF;
224 end if;
225 exit;
226 end loop;
227
228 FND_MSG_PUB.Count_And_Get(
229 p_count => x_msg_count,
230 p_data => x_msg_data);
231 end if;
232
233 if X_Return_Status = FND_API.G_RET_STS_SUCCESS then
234
235 UPDATE_ROW (
236 X_LOCATION_AREA_ID,
237 X_OBJECT_VERSION_NUMBER ,
238 X_PARENT_LOCATION_AREA_ID ,
239 X_REQUEST_ID ,
240 X_PROGRAM_APPLICATION_ID,
241 X_PROGRAM_ID ,
242 X_PROGRAM_UPDATE_DATE ,
243 X_ORIG_SYSTEM_REF ,
244 X_ORIG_SYSTEM_ID ,
245 X_LOCATION_TYPE_CODE ,
246 X_START_DATE_ACTIVE ,
247 X_END_DATE_ACTIVE ,
248 X_LOCATION_AREA_CODE ,
249 X_LOCATION_AREA_NAME ,
250 X_LOCATION_AREA_DESCRIPTION ,
251 X_LAST_UPDATE_DATE ,
252 X_LAST_UPDATED_BY ,
253 X_LAST_UPDATE_LOGIN);
254 end if;
255 end UPDATE_ROW;
256
257
258
259 procedure DELETE_ROW (
260 X_LOCATION_AREA_ID in NUMBER
261 ) is
262 begin
263 delete from JTF_LOC_AREAS_TL
264 where LOCATION_AREA_ID = X_LOCATION_AREA_ID;
265
266 if (sql%notfound) then
267 raise no_data_found;
268 end if;
269
270 delete from JTF_LOC_AREAS_B
271 where LOCATION_AREA_ID = X_LOCATION_AREA_ID;
272
273 if (sql%notfound) then
274 raise no_data_found;
275 end if;
276 end DELETE_ROW;
277
278 procedure ADD_LANGUAGE
279 is
280 begin
281 delete from JTF_LOC_AREAS_TL T
282 where not exists
283 (select NULL
284 from JTF_LOC_AREAS_B B
285 where B.LOCATION_AREA_ID = T.LOCATION_AREA_ID
286 );
287
288 update JTF_LOC_AREAS_TL T set (
289 LOCATION_AREA_NAME,
290 LOCATION_AREA_DESCRIPTION
291 ) = (select
292 B.LOCATION_AREA_NAME,
293 B.LOCATION_AREA_DESCRIPTION
294 from JTF_LOC_AREAS_TL B
295 where B.LOCATION_AREA_ID = T.LOCATION_AREA_ID
296 and B.LANGUAGE = T.SOURCE_LANG)
297 where (
298 T.LOCATION_AREA_ID,
299 T.LANGUAGE
300 ) in (select
301 SUBT.LOCATION_AREA_ID,
302 SUBT.LANGUAGE
303 from JTF_LOC_AREAS_TL SUBB, JTF_LOC_AREAS_TL SUBT
304 where SUBB.LOCATION_AREA_ID = SUBT.LOCATION_AREA_ID
305 and SUBB.LANGUAGE = SUBT.SOURCE_LANG
306 and (SUBB.LOCATION_AREA_NAME <> SUBT.LOCATION_AREA_NAME
307 or SUBB.LOCATION_AREA_DESCRIPTION <> SUBT.LOCATION_AREA_DESCRIPTION
308 or (SUBB.LOCATION_AREA_DESCRIPTION is null and SUBT.LOCATION_AREA_DESCRIPTION is not null)
309 or (SUBB.LOCATION_AREA_DESCRIPTION is not null and SUBT.LOCATION_AREA_DESCRIPTION is null)
310 ));
311
312 insert into JTF_LOC_AREAS_TL (
313 LOCATION_AREA_ID,
314 LAST_UPDATE_DATE,
315 LAST_UPDATED_BY,
316 CREATION_DATE,
317 CREATED_BY,
318 LAST_UPDATE_LOGIN,
319 LOCATION_AREA_NAME,
320 LOCATION_AREA_DESCRIPTION,
321 LANGUAGE,
322 SOURCE_LANG
323 ) select
324 B.LOCATION_AREA_ID,
325 B.LAST_UPDATE_DATE,
326 B.LAST_UPDATED_BY,
327 B.CREATION_DATE,
328 B.CREATED_BY,
329 B.LAST_UPDATE_LOGIN,
330 B.LOCATION_AREA_NAME,
331 B.LOCATION_AREA_DESCRIPTION,
332 L.LANGUAGE_CODE,
333 B.SOURCE_LANG
334 from JTF_LOC_AREAS_TL B, FND_LANGUAGES L
335 where L.INSTALLED_FLAG in ('I', 'B')
336 and B.LANGUAGE = userenv('LANG')
337 and not exists
338 (select NULL
339 from JTF_LOC_AREAS_TL T
340 where T.LOCATION_AREA_ID = B.LOCATION_AREA_ID
341 and T.LANGUAGE = L.LANGUAGE_CODE);
342 end ADD_LANGUAGE;
343
344 procedure TRANSLATE_ROW(
345 X_LOCATION_AREA_ID in NUMBER
346 , X_LOCATION_AREA_NAME in VARCHAR2
347 , X_LOCATION_AREA_DESCRIPTION in VARCHAR2
348 , X_OWNER IN VARCHAR2
349 ) is
350 begin
351 update JTF_LOC_AREAS_TL set
352 location_area_name = nvl(x_location_area_name, location_area_name),
353 location_area_description = nvl(x_location_area_description, location_area_description),
354 source_lang = userenv('LANG'),
355 last_update_date = sysdate,
356 last_updated_by = decode(x_owner, 'SEED', 1, 0),
357 last_update_login = 0
358 where location_area_id = x_location_area_id
359 and userenv('LANG') in (language, source_lang);
360 end TRANSLATE_ROW;
361
362 procedure LOAD_ROW(
363 X_LOCATION_AREA_ID in NUMBER,
364 X_PARENT_LOCATION_AREA_ID in NUMBER,
365 X_REQUEST_ID in NUMBER,
366 X_PROGRAM_APPLICATION_ID in NUMBER,
367 X_PROGRAM_ID in NUMBER,
368 X_PROGRAM_UPDATE_DATE in DATE,
369 X_ORIG_SYSTEM_REF in VARCHAR2,
370 X_ORIG_SYSTEM_ID in NUMBER,
371 X_LOCATION_TYPE_CODE in VARCHAR2,
372 X_START_DATE_ACTIVE in DATE,
373 X_END_DATE_ACTIVE in DATE,
374 X_LOCATION_AREA_CODE in VARCHAR2,
375 X_LOCATION_AREA_NAME in VARCHAR2,
376 X_LOCATION_AREA_DESCRIPTION in VARCHAR2,
377 X_OWNER in VARCHAR2
378 ) is
379
380 l_user_id number := 0;
381 l_obj_verno number;
382 l_dummy_char varchar2(1);
383 l_row_id varchar2(100);
384 l_area_id number;
385
386 cursor c_obj_verno is
387 select object_version_number
388 from JTF_LOC_AREAS_B
389 where location_area_id = X_LOCATION_AREA_ID;
390
391 cursor c_chk_area_exists is
392 select 'x'
393 from JTF_LOC_AREAS_B
394 where location_area_id = X_LOCATION_AREA_ID;
395
396 cursor c_get_areaid is
397 select JTF_LOC_AREAS_B_S.nextval
398 from dual;
399
400 BEGIN
401
402 if X_OWNER = 'SEED' then
403 l_user_id := 1;
404 end if;
405
406 open c_chk_area_exists;
407 fetch c_chk_area_exists into l_dummy_char;
408 if c_chk_area_exists%notfound
409 then
410 close c_chk_area_exists;
411 if X_LOCATION_AREA_ID is null
412 then
413 open c_get_areaid;
414 fetch c_get_areaid into l_area_id;
415 close c_get_areaid;
416 else
417 l_area_id := X_LOCATION_AREA_ID;
418 end if;
419 l_obj_verno := 1;
420 JTF_LOC_AREAS_PKG.INSERT_ROW(
421 X_ROWID => l_row_id,
422 X_LOCATION_AREA_ID => l_area_id,
423 X_OBJECT_VERSION_NUMBER => l_obj_verno,
424 X_PARENT_LOCATION_AREA_ID => X_PARENT_LOCATION_AREA_ID,
425 X_REQUEST_ID => X_REQUEST_ID,
426 X_PROGRAM_APPLICATION_ID => X_PROGRAM_APPLICATION_ID,
427 X_PROGRAM_ID => X_PROGRAM_ID,
428 X_PROGRAM_UPDATE_DATE => X_PROGRAM_UPDATE_DATE,
429 X_ORIG_SYSTEM_REF => X_ORIG_SYSTEM_REF,
430 X_ORIG_SYSTEM_ID => X_ORIG_SYSTEM_ID,
431 X_LOCATION_TYPE_CODE => X_LOCATION_TYPE_CODE,
432 X_START_DATE_ACTIVE => X_START_DATE_ACTIVE,
433 X_END_DATE_ACTIVE => X_END_DATE_ACTIVE,
434 X_LOCATION_AREA_CODE => X_LOCATION_AREA_CODE,
435 X_LOCATION_AREA_NAME => X_LOCATION_AREA_NAME,
436 X_LOCATION_AREA_DESCRIPTION => X_LOCATION_AREA_DESCRIPTION,
437 X_CREATION_DATE => SYSDATE,
438 X_CREATED_BY => l_user_id,
439 X_LAST_UPDATE_DATE => SYSDATE,
440 X_LAST_UPDATED_BY => l_user_id,
441 X_LAST_UPDATE_LOGIN => 0
442 );
443 else
444 close c_chk_area_exists;
445 open c_obj_verno;
446 fetch c_obj_verno into l_obj_verno;
447 close c_obj_verno;
448 JTF_LOC_AREAS_PKG.UPDATE_ROW(
449 X_LOCATION_AREA_ID => X_LOCATION_AREA_ID,
450 X_OBJECT_VERSION_NUMBER => l_obj_verno + 1,
451 X_PARENT_LOCATION_AREA_ID => X_PARENT_LOCATION_AREA_ID,
452 X_REQUEST_ID => X_REQUEST_ID,
453 X_PROGRAM_APPLICATION_ID => X_PROGRAM_APPLICATION_ID,
454 X_PROGRAM_ID => X_PROGRAM_ID,
455 X_PROGRAM_UPDATE_DATE => X_PROGRAM_UPDATE_DATE,
456 X_ORIG_SYSTEM_REF => X_ORIG_SYSTEM_REF,
457 X_ORIG_SYSTEM_ID => X_ORIG_SYSTEM_ID,
458 X_LOCATION_TYPE_CODE => X_LOCATION_TYPE_CODE,
459 X_START_DATE_ACTIVE => X_START_DATE_ACTIVE,
460 X_END_DATE_ACTIVE => X_END_DATE_ACTIVE,
461 X_LOCATION_AREA_CODE => X_LOCATION_AREA_CODE,
462 X_LOCATION_AREA_NAME => X_LOCATION_AREA_NAME,
463 X_LOCATION_AREA_DESCRIPTION => X_LOCATION_AREA_DESCRIPTION,
464 X_LAST_UPDATE_DATE => SYSDATE,
465 X_LAST_UPDATED_BY => l_user_id,
466 X_LAST_UPDATE_LOGIN => 0
467 );
468 end if;
469 END LOAD_ROW;
470
471 end JTF_LOC_AREAS_PKG;