[Home] [Help]
PACKAGE BODY: APPS.JTF_IH_ACTION_ITEMS_SEED_PVT
Source
1 package body JTF_IH_ACTION_ITEMS_SEED_PVT as
2 /* $Header: JTFIHAIB.pls 115.1 2000/02/15 12:25:10 pkm ship $ */
3 procedure INSERT_ROW (
4 X_ROWID in out VARCHAR2,
5 X_ACTION_ITEM_ID in NUMBER,
6 X_OBJECT_VERSION_NUMBER in NUMBER,
7 X_ACTION_ITEM in VARCHAR2,
8 X_SHORT_DESCRIPTION in VARCHAR2,
9 X_CREATION_DATE in DATE,
10 X_CREATED_BY in NUMBER,
11 X_LAST_UPDATE_DATE in DATE,
12 X_LAST_UPDATED_BY in NUMBER,
13 X_LAST_UPDATE_LOGIN in NUMBER
14 ) is
15 cursor C is select ROWID from JTF_IH_ACTION_ITEMS_B
16 where ACTION_ITEM_ID = X_ACTION_ITEM_ID
17 ;
18 begin
19 insert into JTF_IH_ACTION_ITEMS_B (
20 ACTION_ITEM_ID,
21 OBJECT_VERSION_NUMBER,
22 CREATION_DATE,
23 CREATED_BY,
24 LAST_UPDATE_DATE,
25 LAST_UPDATED_BY,
26 LAST_UPDATE_LOGIN
27 ) values (
28 X_ACTION_ITEM_ID,
29 X_OBJECT_VERSION_NUMBER,
30 X_CREATION_DATE,
31 X_CREATED_BY,
32 X_LAST_UPDATE_DATE,
33 X_LAST_UPDATED_BY,
34 X_LAST_UPDATE_LOGIN
35 );
36
37 insert into JTF_IH_ACTION_ITEMS_TL (
38 ACTION_ITEM_ID,
39 OBJECT_VERSION_NUMBER,
40 CREATED_BY,
41 CREATION_DATE,
42 LAST_UPDATED_BY,
43 LAST_UPDATE_DATE,
44 LAST_UPDATE_LOGIN,
45 ACTION_ITEM,
46 SHORT_DESCRIPTION,
47 LANGUAGE,
48 SOURCE_LANG
49 ) select
50 X_ACTION_ITEM_ID,
51 X_OBJECT_VERSION_NUMBER,
52 X_CREATED_BY,
53 X_CREATION_DATE,
54 X_LAST_UPDATED_BY,
55 X_LAST_UPDATE_DATE,
56 X_LAST_UPDATE_LOGIN,
57 X_ACTION_ITEM,
58 X_SHORT_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_IH_ACTION_ITEMS_TL T
66 where T.ACTION_ITEM_ID = X_ACTION_ITEM_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 LOCK_ROW (
80 X_ACTION_ITEM_ID in NUMBER,
81 X_OBJECT_VERSION_NUMBER in NUMBER,
82 X_ACTION_ITEM in VARCHAR2,
83 X_SHORT_DESCRIPTION in VARCHAR2
84 ) is
85 cursor c is select
86 OBJECT_VERSION_NUMBER
87 from JTF_IH_ACTION_ITEMS_B
88 where ACTION_ITEM_ID = X_ACTION_ITEM_ID
89 for update of ACTION_ITEM_ID nowait;
90 recinfo c%rowtype;
91
92 cursor c1 is select
93 ACTION_ITEM,
94 SHORT_DESCRIPTION,
95 decode(LANGUAGE, userenv('LANG'), 'Y', 'N') BASELANG
96 from JTF_IH_ACTION_ITEMS_TL
97 where ACTION_ITEM_ID = X_ACTION_ITEM_ID
98 and userenv('LANG') in (LANGUAGE, SOURCE_LANG)
99 for update of ACTION_ITEM_ID nowait;
100 begin
101 open c;
102 fetch c into recinfo;
103 if (c%notfound) then
104 close c;
105 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
106 app_exception.raise_exception;
107 end if;
108 close c;
109 if ( ((recinfo.OBJECT_VERSION_NUMBER = X_OBJECT_VERSION_NUMBER)
110 OR ((recinfo.OBJECT_VERSION_NUMBER is null) AND (X_OBJECT_VERSION_NUMBER is null)))
111 ) then
112 null;
113 else
114 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
115 app_exception.raise_exception;
116 end if;
117
118 for tlinfo in c1 loop
119 if (tlinfo.BASELANG = 'Y') then
120 if ( (tlinfo.ACTION_ITEM = X_ACTION_ITEM)
121 AND (tlinfo.SHORT_DESCRIPTION = X_SHORT_DESCRIPTION)
122 ) then
123 null;
124 else
125 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
126 app_exception.raise_exception;
127 end if;
128 end if;
129 end loop;
130 return;
131 end LOCK_ROW;
132
133 procedure UPDATE_ROW (
134 X_ACTION_ITEM_ID in NUMBER,
135 X_OBJECT_VERSION_NUMBER in NUMBER,
136 X_ACTION_ITEM in VARCHAR2,
137 X_SHORT_DESCRIPTION in VARCHAR2,
138 X_LAST_UPDATE_DATE in DATE,
139 X_LAST_UPDATED_BY in NUMBER,
140 X_LAST_UPDATE_LOGIN in NUMBER
141 ) is
142 begin
143 update JTF_IH_ACTION_ITEMS_B set
144 OBJECT_VERSION_NUMBER = X_OBJECT_VERSION_NUMBER,
145 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
146 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
147 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN
148 where ACTION_ITEM_ID = X_ACTION_ITEM_ID;
149
150 if (sql%notfound) then
151 raise no_data_found;
152 end if;
153
154 update JTF_IH_ACTION_ITEMS_TL set
155 ACTION_ITEM = X_ACTION_ITEM,
156 SHORT_DESCRIPTION = X_SHORT_DESCRIPTION,
157 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
158 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
159 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN,
160 SOURCE_LANG = userenv('LANG')
161 where ACTION_ITEM_ID = X_ACTION_ITEM_ID
162 and userenv('LANG') in (LANGUAGE, SOURCE_LANG);
163
164 if (sql%notfound) then
165 raise no_data_found;
166 end if;
167 end UPDATE_ROW;
168
169 procedure DELETE_ROW (
170 X_ACTION_ITEM_ID in NUMBER
171 ) is
172 begin
173 delete from JTF_IH_ACTION_ITEMS_TL
174 where ACTION_ITEM_ID = X_ACTION_ITEM_ID;
175
176 if (sql%notfound) then
177 raise no_data_found;
178 end if;
179
180 delete from JTF_IH_ACTION_ITEMS_B
181 where ACTION_ITEM_ID = X_ACTION_ITEM_ID;
182
183 if (sql%notfound) then
184 raise no_data_found;
185 end if;
186 end DELETE_ROW;
187
188 procedure ADD_LANGUAGE
189 is
190 begin
191 delete from JTF_IH_ACTION_ITEMS_TL T
192 where not exists
193 (select NULL
194 from JTF_IH_ACTION_ITEMS_B B
195 where B.ACTION_ITEM_ID = T.ACTION_ITEM_ID
196 );
197
198 update JTF_IH_ACTION_ITEMS_TL T set (
199 ACTION_ITEM,
200 SHORT_DESCRIPTION
201 ) = (select
202 B.ACTION_ITEM,
203 B.SHORT_DESCRIPTION
204 from JTF_IH_ACTION_ITEMS_TL B
205 where B.ACTION_ITEM_ID = T.ACTION_ITEM_ID
206 and B.LANGUAGE = T.SOURCE_LANG)
207 where (
208 T.ACTION_ITEM_ID,
209 T.LANGUAGE
210 ) in (select
211 SUBT.ACTION_ITEM_ID,
212 SUBT.LANGUAGE
213 from JTF_IH_ACTION_ITEMS_TL SUBB, JTF_IH_ACTION_ITEMS_TL SUBT
214 where SUBB.ACTION_ITEM_ID = SUBT.ACTION_ITEM_ID
215 and SUBB.LANGUAGE = SUBT.SOURCE_LANG
216 and (SUBB.ACTION_ITEM <> SUBT.ACTION_ITEM
217 or SUBB.SHORT_DESCRIPTION <> SUBT.SHORT_DESCRIPTION
218 ));
219
220 insert into JTF_IH_ACTION_ITEMS_TL (
221 ACTION_ITEM_ID,
222 OBJECT_VERSION_NUMBER,
223 CREATED_BY,
224 CREATION_DATE,
225 LAST_UPDATED_BY,
226 LAST_UPDATE_DATE,
227 LAST_UPDATE_LOGIN,
228 ACTION_ITEM,
229 SHORT_DESCRIPTION,
230 LANGUAGE,
231 SOURCE_LANG
232 ) select
233 B.ACTION_ITEM_ID,
234 B.OBJECT_VERSION_NUMBER,
235 B.CREATED_BY,
236 B.CREATION_DATE,
237 B.LAST_UPDATED_BY,
238 B.LAST_UPDATE_DATE,
239 B.LAST_UPDATE_LOGIN,
240 B.ACTION_ITEM,
241 B.SHORT_DESCRIPTION,
242 L.LANGUAGE_CODE,
243 B.SOURCE_LANG
244 from JTF_IH_ACTION_ITEMS_TL B, FND_LANGUAGES L
245 where L.INSTALLED_FLAG in ('I', 'B')
246 and B.LANGUAGE = userenv('LANG')
247 and not exists
248 (select NULL
249 from JTF_IH_ACTION_ITEMS_TL T
250 where T.ACTION_ITEM_ID = B.ACTION_ITEM_ID
251 and T.LANGUAGE = L.LANGUAGE_CODE);
252 end ADD_LANGUAGE;
253 procedure LOAD_ROW (
254 X_ACTION_ITEM_ID in NUMBER,
255 X_ACTION_ITEM in VARCHAR2,
256 X_OBJECT_VERSION_NUMBER in NUMBER,
257 X_SHORT_DESCRIPTION in VARCHAR2,
258 X_OWNER IN VARCHAR2
259 ) IS
260 begin
261 declare
262 user_id NUMBER := 0;
263 row_id VARCHAR2(64);
264 l_api_version NUMBER := 1.0;
265 l_return_status VARCHAR2(1);
266 l_msg_count NUMBER;
267 l_msg_data VARCHAR2(100);
268 l_init_msg_list VARCHAR2(1) := 'F';
269 l_commit VARCHAR2(1) := 'F';
270 l_validation_level NUMBER := 100;
271 l_action_item_id NUMBER;
272 l_object_version_number NUMBER;
273 l_action_item VARCHAR2(80);
274 l_short_description VARCHAR2(240);
275 l_last_update_date DATE;
276 l_last_updated_by NUMBER;
277 l_last_update_login NUMBER;
278 l_creation_date DATE;
279 l_created_by NUMBER;
280
281 begin
282 if (x_owner = 'SEED') then
283 user_id := -1;
284 end if;
285 l_action_item_id := X_ACTION_ITEM_ID;
286 l_object_version_number := 1;
287 l_action_item := X_ACTION_ITEM;
288 l_short_description := X_SHORT_DESCRIPTION;
289 l_last_update_date := sysdate;
290 l_last_updated_by := user_id;
291 l_last_update_login := 0;
292
293 UPDATE_ROW(
294 X_ACTION_ITEM_ID => l_action_item_id,
295 X_OBJECT_VERSION_NUMBER => l_object_version_number,
296 X_ACTION_ITEM => l_action_item,
297 X_SHORT_DESCRIPTION => l_short_description,
298 X_LAST_UPDATE_DATE => l_last_update_date,
299 X_LAST_UPDATED_BY => l_last_updated_by,
300 X_LAST_UPDATE_LOGIN => l_last_update_login);
301 EXCEPTION
302 when no_data_found then
303 l_creation_date := sysdate;
304 l_created_by := user_id;
305 INSERT_ROW(
306 row_id,
307 X_ACTION_ITEM_ID => l_action_item_id,
308 X_OBJECT_VERSION_NUMBER => l_object_version_number,
309 X_ACTION_ITEM => l_action_item,
310 X_SHORT_DESCRIPTION => l_short_description,
311 X_CREATION_DATE => l_creation_date,
312 X_CREATED_BY => l_created_by,
313 X_LAST_UPDATE_DATE => l_last_update_date,
314 X_LAST_UPDATED_BY => l_last_updated_by,
315 X_LAST_UPDATE_LOGIN => l_last_update_login);
316 end;
317 end LOAD_ROW;
318 procedure TRANSLATE_ROW (
319 X_ACTION_ITEM_ID in NUMBER,
320 X_ACTION_ITEM in VARCHAR2,
321 X_SHORT_DESCRIPTION in VARCHAR2,
322 X_OWNER IN VARCHAR2) is
323 begin
324 UPDATE jtf_ih_action_items_tl SET
325 action_item_id = X_ACTION_ITEM_ID,
326 action_item = X_ACTION_ITEM,
327 short_description = X_SHORT_DESCRIPTION,
328 last_update_date = sysdate,
329 last_updated_by = decode(X_OWNER, 'SEED', 1, 0),
330 last_update_login = 0,
331 source_lang = userenv('LANG')
332 WHERE userenv('LANG') in (LANGUAGE, SOURCE_LANG) AND
333 action_item_id = X_ACTION_ITEM_ID;
334 end TRANSLATE_ROW;
335 end JTF_IH_ACTION_ITEMS_SEED_PVT;