[Home] [Help]
PACKAGE BODY: APPS.JTF_LOC_TYPES_PKG
Source
1 package body JTF_LOC_TYPES_PKG as
2 /* $Header: jtfllotb.pls 120.2 2005/08/18 23:07:59 stopiwal ship $ */
3 procedure INSERT_ROW (
4 X_ROWID IN OUT NOCOPY /* file.sql.39 change */ VARCHAR2,
5 X_LOCATION_TYPE_ID in NUMBER,
6 X_OBJECT_VERSION_NUMBER in NUMBER,
7 X_LOCATION_TYPE_CODE in VARCHAR2,
8 X_LOCATION_TYPE_NAME in VARCHAR2,
9 X_LOCATION_TYPE_DESCRIPTION in VARCHAR2,
10 X_CREATION_DATE in DATE,
11 X_CREATED_BY in NUMBER,
12 X_LAST_UPDATE_DATE in DATE,
13 X_LAST_UPDATED_BY in NUMBER,
14 X_LAST_UPDATE_LOGIN in NUMBER) is
15 cursor C is select ROWID from JTF_LOC_TYPES_B
16 where LOCATION_TYPE_ID = X_LOCATION_TYPE_ID
17 ;
18 begin
19 insert into JTF_LOC_TYPES_B (
20 LOCATION_TYPE_ID,
21 OBJECT_VERSION_NUMBER,
22 LOCATION_TYPE_CODE,
23 CREATION_DATE,
24 CREATED_BY,
25 LAST_UPDATE_DATE,
26 LAST_UPDATED_BY,
27 LAST_UPDATE_LOGIN
28 ) values (
29 X_LOCATION_TYPE_ID,
30 X_OBJECT_VERSION_NUMBER,
31 X_LOCATION_TYPE_CODE,
32 X_CREATION_DATE,
33 X_CREATED_BY,
34 X_LAST_UPDATE_DATE,
35 X_LAST_UPDATED_BY,
36 X_LAST_UPDATE_LOGIN
37 );
38
39 insert into JTF_LOC_TYPES_TL (
40 LOCATION_TYPE_ID,
41 LAST_UPDATE_DATE,
42 LAST_UPDATED_BY,
43 CREATION_DATE,
44 CREATED_BY,
45 LAST_UPDATE_LOGIN,
46 LOCATION_TYPE_NAME,
47 LOCATION_TYPE_DESCRIPTION,
48 LANGUAGE,
49 SOURCE_LANG
50 ) select
51 X_LOCATION_TYPE_ID,
52 X_LAST_UPDATE_DATE,
53 X_LAST_UPDATED_BY,
54 X_CREATION_DATE,
55 X_CREATED_BY,
56 X_LAST_UPDATE_LOGIN,
57 X_LOCATION_TYPE_NAME,
58 X_LOCATION_TYPE_DESCRIPTION,
59 L.LANGUAGE_CODE,
60 userenv('LANG')
61 from FND_LANGUAGES L
62 where L.INSTALLED_FLAG in ('I', 'B')
63 and not exists
64 (select NULL
65 from JTF_LOC_TYPES_TL T
66 where T.LOCATION_TYPE_ID = X_LOCATION_TYPE_ID
67 and T.LANGUAGE = L.LANGUAGE_CODE);
68
69 open c;
70 fetch c into X_ROWID;
71 if (c%notfound) then
72 close c;
73 raise no_data_found;
74 end if;
75 close c;
76
77 end INSERT_ROW;
78
79 procedure UPDATE_ROW (
80 X_LOCATION_TYPE_ID in NUMBER,
81 X_OBJECT_VERSION_NUMBER in NUMBER,
82 X_LOCATION_TYPE_CODE in VARCHAR2,
83 X_LOCATION_TYPE_NAME in VARCHAR2,
84 X_LOCATION_TYPE_DESCRIPTION in VARCHAR2,
85 X_LAST_UPDATE_DATE in DATE,
86 X_LAST_UPDATED_BY in NUMBER,
87 X_LAST_UPDATE_LOGIN in NUMBER
88 ) is
89 begin
90 update JTF_LOC_TYPES_B set
91 OBJECT_VERSION_NUMBER = X_OBJECT_VERSION_NUMBER,
92 LOCATION_TYPE_CODE = X_LOCATION_TYPE_CODE,
93 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
94 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
95 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN
96 where LOCATION_TYPE_ID = X_LOCATION_TYPE_ID;
97
98 if (sql%notfound) then
99 raise no_data_found;
100 end if;
101
102 update JTF_LOC_TYPES_TL set
103 LOCATION_TYPE_NAME = X_LOCATION_TYPE_NAME,
104 LOCATION_TYPE_DESCRIPTION = X_LOCATION_TYPE_DESCRIPTION,
105 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
106 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
107 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN,
108 SOURCE_LANG = userenv('LANG')
109 where LOCATION_TYPE_ID = X_LOCATION_TYPE_ID
110 and userenv('LANG') in (LANGUAGE, SOURCE_LANG);
111
112 if (sql%notfound) then
113 raise no_data_found;
114 end if;
115 end UPDATE_ROW;
116
117
118 procedure ADD_LANGUAGE
119 is
120 begin
121 delete from JTF_LOC_TYPES_TL T
122 where not exists
123 (select NULL
124 from JTF_LOC_TYPES_B B
125 where B.LOCATION_TYPE_ID = T.LOCATION_TYPE_ID
126 );
127
128 update JTF_LOC_TYPES_TL T set (
129 LOCATION_TYPE_NAME,
130 LOCATION_TYPE_DESCRIPTION
131 ) = (select
132 B.LOCATION_TYPE_NAME,
133 B.LOCATION_TYPE_DESCRIPTION
134 from JTF_LOC_TYPES_TL B
135 where B.LOCATION_TYPE_ID = T.LOCATION_TYPE_ID
136 and B.LANGUAGE = T.SOURCE_LANG)
137 where (
138 T.LOCATION_TYPE_ID,
139 T.LANGUAGE
140 ) in (select
141 SUBT.LOCATION_TYPE_ID,
142 SUBT.LANGUAGE
143 from JTF_LOC_TYPES_TL SUBB, JTF_LOC_TYPES_TL SUBT
144 where SUBB.LOCATION_TYPE_ID = SUBT.LOCATION_TYPE_ID
145 and SUBB.LANGUAGE = SUBT.SOURCE_LANG
146 and (SUBB.LOCATION_TYPE_NAME <> SUBT.LOCATION_TYPE_NAME
147 or SUBB.LOCATION_TYPE_DESCRIPTION <> SUBT.LOCATION_TYPE_DESCRIPTION
148 or (SUBB.LOCATION_TYPE_DESCRIPTION is null and SUBT.LOCATION_TYPE_DESCRIPTION is not null)
149 or (SUBB.LOCATION_TYPE_DESCRIPTION is not null and SUBT.LOCATION_TYPE_DESCRIPTION is null)
150 ));
151
152 insert into JTF_LOC_TYPES_TL (
153 LOCATION_TYPE_ID,
154 LAST_UPDATE_DATE,
155 LAST_UPDATED_BY,
156 CREATION_DATE,
157 CREATED_BY,
158 LAST_UPDATE_LOGIN,
159 LOCATION_TYPE_NAME,
160 LOCATION_TYPE_DESCRIPTION,
161 LANGUAGE,
162 SOURCE_LANG
163 ) select
164 B.LOCATION_TYPE_ID,
165 B.LAST_UPDATE_DATE,
166 B.LAST_UPDATED_BY,
167 B.CREATION_DATE,
168 B.CREATED_BY,
169 B.LAST_UPDATE_LOGIN,
170 B.LOCATION_TYPE_NAME,
171 B.LOCATION_TYPE_DESCRIPTION,
172 L.LANGUAGE_CODE,
173 B.SOURCE_LANG
174 from JTF_LOC_TYPES_TL B, FND_LANGUAGES L
175 where L.INSTALLED_FLAG in ('I', 'B')
176 and B.LANGUAGE = userenv('LANG')
177 and not exists
178 (select NULL
179 from JTF_LOC_TYPES_TL T
180 where T.LOCATION_TYPE_ID = B.LOCATION_TYPE_ID
181 and T.LANGUAGE = L.LANGUAGE_CODE);
182 end ADD_LANGUAGE;
183
184 procedure TRANSLATE_ROW(
185 X_LOCATION_TYPE_ID IN NUMBER
186 , X_LOCATION_TYPE_NAME IN VARCHAR2
187 , X_LOCATION_TYPE_DESCRIPTION IN VARCHAR2
188 , X_OWNER IN VARCHAR2
189 ) is
190 begin
191 update JTF_LOC_TYPES_TL set
192 location_type_name = nvl(x_location_type_name, location_type_name),
193 location_type_description = nvl(X_LOCATION_TYPE_DESCRIPTION, location_type_description),
194 source_lang = userenv('LANG'),
195 last_update_date = sysdate,
196 last_updated_by = decode(x_owner, 'SEED', 1, 0),
197 last_update_login = 0
198 where location_type_id = x_location_type_id
199 and userenv('LANG') in (language, source_lang);
200 end TRANSLATE_ROW;
201
202 procedure LOAD_ROW(
203 X_LOCATION_TYPE_ID in NUMBER,
204 X_LOCATION_TYPE_CODE in VARCHAR2,
205 X_LOCATION_TYPE_NAME in VARCHAR2,
206 X_LOCATION_TYPE_DESCRIPTION in VARCHAR2,
207 X_OWNER in VARCHAR2
208 ) is
209
210 l_user_id number := 0;
211 l_obj_verno number;
212 l_dummy_char varchar2(1);
213 l_row_id varchar2(100);
214 l_type_id number;
215
216 cursor c_obj_verno is
217 select object_version_number
218 from JTF_LOC_TYPES_B
219 where LOCATION_TYPE_ID = X_LOCATION_TYPE_ID;
220
221 cursor c_chk_type_exists is
222 select 'x'
223 from JTF_LOC_TYPES_B
224 where LOCATION_TYPE_ID = X_LOCATION_TYPE_ID;
225
226 cursor c_get_typeid is
227 select JTF_LOC_TYPES_B_S.nextval
228 from dual;
229
230 BEGIN
231
232 if X_OWNER = 'SEED' then
233 l_user_id := 1;
234 end if;
235
236 open c_chk_type_exists;
237 fetch c_chk_type_exists into l_dummy_char;
238 if c_chk_type_exists%notfound
239 then
240 close c_chk_type_exists;
241 if x_location_type_id is null
242 then
243 open c_get_typeid;
244 fetch c_get_typeid into l_type_id;
245 close c_get_typeid;
246 else
247 l_type_id := X_LOCATION_TYPE_ID;
248 end if;
249 l_obj_verno := 1;
250 JTF_LOC_TYPES_PKG.INSERT_ROW(
251 X_ROWID => l_row_id,
252 X_LOCATION_TYPE_ID => l_type_id,
253 X_OBJECT_VERSION_NUMBER => l_obj_verno,
254 X_LOCATION_TYPE_CODE => X_LOCATION_TYPE_CODE,
255 X_LOCATION_TYPE_NAME => X_LOCATION_TYPE_NAME,
256 X_LOCATION_TYPE_DESCRIPTION => X_LOCATION_TYPE_DESCRIPTION,
257 X_CREATION_DATE => SYSDATE,
258 X_CREATED_BY => l_user_id,
259 X_LAST_UPDATE_DATE => SYSDATE,
260 X_LAST_UPDATED_BY => l_user_id,
261 X_LAST_UPDATE_LOGIN => 0
262 );
263 else
264 close c_chk_type_exists;
265 open c_obj_verno;
266 fetch c_obj_verno into l_obj_verno;
267 close c_obj_verno;
268 JTF_LOC_TYPES_PKG.UPDATE_ROW(
269 X_LOCATION_TYPE_ID => X_LOCATION_TYPE_ID,
270 X_OBJECT_VERSION_NUMBER => l_obj_verno + 1,
271 X_LOCATION_TYPE_CODE => X_LOCATION_TYPE_CODE,
272 X_LOCATION_TYPE_NAME => X_LOCATION_TYPE_NAME,
273 X_LOCATION_TYPE_DESCRIPTION => X_LOCATION_TYPE_DESCRIPTION,
274 X_LAST_UPDATE_DATE => SYSDATE,
275 X_LAST_UPDATED_BY => l_user_id,
276 X_LAST_UPDATE_LOGIN => 0
277 );
278 end if;
279 END LOAD_ROW;
280
281 end JTF_LOC_TYPES_PKG;