1 package body ENG_CHANGE_ACTIONS_PKG as
2 /* $Header: ENGUCAMB.pls 120.3 2005/12/22 04:23:17 lkasturi noship $ */
3
4 procedure INSERT_ROW (
5 X_ROWID in out NOCOPY VARCHAR2,
6 X_ACTION_ID in NUMBER,
7 X_ORIGINAL_SYSTEM_REFERENCE in VARCHAR2,
8 X_WORKFLOW_ITEM_KEY in VARCHAR2,
9 X_REQUEST_ID in NUMBER,
10 X_STATUS_CODE in NUMBER,
11 X_PRIORITY_CODE in VARCHAR2,
12 X_ASSIGNEE_ID in NUMBER,
13 X_RESPONSE_BY_DATE in DATE,
14 X_PARTY_ID_LIST in VARCHAR2,
15 X_PARENT_STATUS_CODE in NUMBER,
16 X_WORKFLOW_ITEM_TYPE in VARCHAR2,
17 X_ROUTE_ID in NUMBER,
18 X_PARENT_ACTION_ID in NUMBER,
19 X_ACTION_TYPE in VARCHAR2,
20 X_OBJECT_NAME in VARCHAR2,
21 X_OBJECT_ID1 in NUMBER,
22 X_OBJECT_ID2 in NUMBER,
23 X_OBJECT_ID3 in NUMBER,
24 X_OBJECT_ID4 in NUMBER,
25 X_OBJECT_ID5 in NUMBER,
26 X_DESCRIPTION in VARCHAR2,
27 X_PROGRAM_ID in NUMBER,
28 X_PROGRAM_APPLICATION_ID in NUMBER,
29 X_PROGRAM_UPDATE_DATE in DATE,
30 X_CREATION_DATE in DATE,
31 X_CREATED_BY in NUMBER,
32 X_LAST_UPDATE_DATE in DATE,
33 X_LAST_UPDATED_BY in NUMBER,
34 X_LAST_UPDATE_LOGIN in NUMBER,
35 X_IMPLEMENTATION_REQ_ID in NUMBER DEFAULT NULL,
36 X_LOCAL_ORGANIZATION_ID in NUMBER DEFAULT NULL -- Bug 4704384
37 ) is
38 cursor C is select ROWID from ENG_CHANGE_ACTIONS
39 where ACTION_ID = X_ACTION_ID;
40 begin
41 insert into ENG_CHANGE_ACTIONS (
42 ORIGINAL_SYSTEM_REFERENCE,
43 WORKFLOW_ITEM_KEY,
44 REQUEST_ID,
45 STATUS_CODE,
46 PRIORITY_CODE,
47 ASSIGNEE_ID,
48 RESPONSE_BY_DATE,
49 PARTY_ID_LIST,
50 PARENT_STATUS_CODE,
51 WORKFLOW_ITEM_TYPE,
52 ROUTE_ID,
53 PARENT_ACTION_ID,
54 ACTION_ID,
55 ACTION_TYPE,
56 OBJECT_NAME,
57 OBJECT_ID1,
58 OBJECT_ID2,
59 OBJECT_ID3,
60 OBJECT_ID4,
61 OBJECT_ID5,
62 PROGRAM_ID,
63 PROGRAM_APPLICATION_ID,
64 PROGRAM_UPDATE_DATE,
65 CREATION_DATE,
66 CREATED_BY,
67 LAST_UPDATE_DATE,
68 LAST_UPDATED_BY,
69 LAST_UPDATE_LOGIN,
70 IMPLEMENTATION_REQ_ID,
71 LOCAL_ORGANIZATION_ID -- Bug 4704384
72 ) values (
73 X_ORIGINAL_SYSTEM_REFERENCE,
74 X_WORKFLOW_ITEM_KEY,
75 X_REQUEST_ID,
76 X_STATUS_CODE,
77 X_PRIORITY_CODE,
78 X_ASSIGNEE_ID,
79 X_RESPONSE_BY_DATE,
80 X_PARTY_ID_LIST,
81 X_PARENT_STATUS_CODE,
82 X_WORKFLOW_ITEM_TYPE,
83 X_ROUTE_ID,
84 X_PARENT_ACTION_ID,
85 X_ACTION_ID,
86 X_ACTION_TYPE,
87 X_OBJECT_NAME,
88 X_OBJECT_ID1,
89 X_OBJECT_ID2,
90 X_OBJECT_ID3,
91 X_OBJECT_ID4,
92 X_OBJECT_ID5,
93 X_PROGRAM_ID,
94 X_PROGRAM_APPLICATION_ID,
95 X_PROGRAM_UPDATE_DATE,
96 X_CREATION_DATE,
97 X_CREATED_BY,
98 X_LAST_UPDATE_DATE,
99 X_LAST_UPDATED_BY,
100 X_LAST_UPDATE_LOGIN,
101 X_IMPLEMENTATION_REQ_ID,
102 X_LOCAL_ORGANIZATION_ID -- Bug 4704384
103 );
104
105 insert into ENG_CHANGE_ACTIONS_TL (
106 LAST_UPDATED_BY,
107 LAST_UPDATE_LOGIN,
108 LAST_UPDATE_DATE,
109 CREATED_BY,
110 DESCRIPTION,
111 CREATION_DATE,
112 ACTION_ID,
113 LANGUAGE,
114 SOURCE_LANG
115 ) select
116 X_LAST_UPDATED_BY,
117 X_LAST_UPDATE_LOGIN,
118 X_LAST_UPDATE_DATE,
119 X_CREATED_BY,
120 X_DESCRIPTION,
121 X_CREATION_DATE,
122 X_ACTION_ID,
123 L.LANGUAGE_CODE,
124 userenv('LANG')
125 from FND_LANGUAGES L
126 where L.INSTALLED_FLAG in ('I', 'B')
127 and not exists
128 (select NULL
129 from ENG_CHANGE_ACTIONS_TL T
130 where T.ACTION_ID = X_ACTION_ID
131 and T.LANGUAGE = L.LANGUAGE_CODE);
132
133 open c;
134 fetch c into X_ROWID;
135 if (c%notfound) then
136 close c;
137 raise no_data_found;
138 end if;
139 close c;
140
141 BEGIN
142
143 ENG_CHANGE_TEXT_UTIL.Insert_Update_Change ( p_change_id => X_OBJECT_ID1 );
144
145 EXCEPTION
146 WHEN others THEN
147 NULL;
148 END;
149
150 end INSERT_ROW;
151
152
153 procedure LOCK_ROW (
154 X_ACTION_ID in NUMBER,
155 X_ORIGINAL_SYSTEM_REFERENCE in VARCHAR2,
156 X_WORKFLOW_ITEM_KEY in VARCHAR2,
157 X_REQUEST_ID in NUMBER,
158 X_STATUS_CODE in NUMBER,
159 X_PRIORITY_CODE in VARCHAR2,
160 X_ASSIGNEE_ID in NUMBER,
161 X_RESPONSE_BY_DATE in DATE,
162 X_PARTY_ID_LIST in VARCHAR2,
163 X_PARENT_STATUS_CODE in NUMBER,
164 X_WORKFLOW_ITEM_TYPE in VARCHAR2,
165 X_ROUTE_ID in NUMBER,
166 X_PARENT_ACTION_ID in NUMBER,
167 X_ACTION_TYPE in VARCHAR2,
168 X_OBJECT_NAME in VARCHAR2,
169 X_OBJECT_ID1 in NUMBER,
170 X_OBJECT_ID2 in NUMBER,
171 X_OBJECT_ID3 in NUMBER,
172 X_OBJECT_ID4 in NUMBER,
173 X_OBJECT_ID5 in NUMBER,
174 X_DESCRIPTION in VARCHAR2,
175 X_PROGRAM_ID in NUMBER,
176 X_PROGRAM_APPLICATION_ID in NUMBER,
177 X_PROGRAM_UPDATE_DATE in DATE,
178 X_IMPLEMENTATION_REQ_ID in NUMBER,
179 X_LOCAL_ORGANIZATION_ID in NUMBER DEFAULT NULL -- Bug 4704384
180
181 ) is
182 cursor c is select
183 ORIGINAL_SYSTEM_REFERENCE,
184 WORKFLOW_ITEM_KEY,
185 REQUEST_ID,
186 STATUS_CODE,
187 PRIORITY_CODE,
188 ASSIGNEE_ID,
189 RESPONSE_BY_DATE,
190 PARTY_ID_LIST,
191 PARENT_STATUS_CODE,
192 WORKFLOW_ITEM_TYPE,
193 ROUTE_ID,
194 PARENT_ACTION_ID,
195 ACTION_TYPE,
196 OBJECT_NAME,
197 OBJECT_ID1,
198 OBJECT_ID2,
199 OBJECT_ID3,
200 OBJECT_ID4,
201 OBJECT_ID5,
202 PROGRAM_ID,
203 PROGRAM_APPLICATION_ID,
204 PROGRAM_UPDATE_DATE,
205 IMPLEMENTATION_REQ_ID,
206 LOCAL_ORGANIZATION_ID
207 from ENG_CHANGE_ACTIONS
208 where ACTION_ID = X_ACTION_ID
209 for update of ACTION_ID nowait;
210 recinfo c%rowtype;
211
212 cursor c1 is select
213 DESCRIPTION,
214 decode(LANGUAGE, userenv('LANG'), 'Y', 'N') BASELANG
215 from ENG_CHANGE_ACTIONS_TL
216 where ACTION_ID = X_ACTION_ID
217 and userenv('LANG') in (LANGUAGE, SOURCE_LANG)
218 for update of ACTION_ID nowait;
219 begin
220 open c;
221 fetch c into recinfo;
222 if (c%notfound) then
223 close c;
224 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
225 app_exception.raise_exception;
226 end if;
227 close c;
228 if ( ((recinfo.ORIGINAL_SYSTEM_REFERENCE = X_ORIGINAL_SYSTEM_REFERENCE)
229 OR ((recinfo.ORIGINAL_SYSTEM_REFERENCE is null) AND (X_ORIGINAL_SYSTEM_REFERENCE is null)))
230 AND ((recinfo.WORKFLOW_ITEM_KEY = X_WORKFLOW_ITEM_KEY)
231 OR ((recinfo.WORKFLOW_ITEM_KEY is null) AND (X_WORKFLOW_ITEM_KEY is null)))
232 AND ((recinfo.REQUEST_ID = X_REQUEST_ID)
233 OR ((recinfo.REQUEST_ID is null) AND (X_REQUEST_ID is null)))
234 AND ((recinfo.STATUS_CODE = X_STATUS_CODE)
235 OR ((recinfo.STATUS_CODE is null) AND (X_STATUS_CODE is null)))
236 AND ((recinfo.PRIORITY_CODE = X_PRIORITY_CODE)
237 OR ((recinfo.PRIORITY_CODE is null) AND (X_PRIORITY_CODE is null)))
238 AND ((recinfo.ASSIGNEE_ID = X_ASSIGNEE_ID)
239 OR ((recinfo.ASSIGNEE_ID is null) AND (X_ASSIGNEE_ID is null)))
240 AND ((recinfo.RESPONSE_BY_DATE = X_RESPONSE_BY_DATE)
241 OR ((recinfo.RESPONSE_BY_DATE is null) AND (X_RESPONSE_BY_DATE is null)))
242 AND ((recinfo.PARTY_ID_LIST = X_PARTY_ID_LIST)
243 OR ((recinfo.PARTY_ID_LIST is null) AND (X_PARTY_ID_LIST is null)))
244 AND ((recinfo.PARENT_STATUS_CODE = X_PARENT_STATUS_CODE)
245 OR ((recinfo.PARENT_STATUS_CODE is null) AND (X_PARENT_STATUS_CODE is null)))
246 AND ((recinfo.WORKFLOW_ITEM_TYPE = X_WORKFLOW_ITEM_TYPE)
247 OR ((recinfo.WORKFLOW_ITEM_TYPE is null) AND (X_WORKFLOW_ITEM_TYPE is null)))
248 AND ((recinfo.ROUTE_ID = X_ROUTE_ID)
249 OR ((recinfo.ROUTE_ID is null) AND (X_ROUTE_ID is null)))
250 AND ((recinfo.PARENT_ACTION_ID = X_PARENT_ACTION_ID)
251 OR ((recinfo.PARENT_ACTION_ID is null) AND (X_PARENT_ACTION_ID is null)))
252 AND (recinfo.ACTION_TYPE = X_ACTION_TYPE)
253 AND (recinfo.OBJECT_NAME = X_OBJECT_NAME)
254 AND (recinfo.OBJECT_ID1 = X_OBJECT_ID1)
255 AND ((recinfo.OBJECT_ID2 = X_OBJECT_ID2)
256 OR ((recinfo.OBJECT_ID2 is null) AND (X_OBJECT_ID2 is null)))
257 AND ((recinfo.OBJECT_ID3 = X_OBJECT_ID3)
258 OR ((recinfo.OBJECT_ID3 is null) AND (X_OBJECT_ID3 is null)))
259 AND ((recinfo.OBJECT_ID4 = X_OBJECT_ID4)
260 OR ((recinfo.OBJECT_ID4 is null) AND (X_OBJECT_ID4 is null)))
261 AND ((recinfo.OBJECT_ID5 = X_OBJECT_ID5)
262 OR ((recinfo.OBJECT_ID5 is null) AND (X_OBJECT_ID5 is null)))
263 AND ((recinfo.PROGRAM_ID = X_PROGRAM_ID)
264 OR ((recinfo.PROGRAM_ID is null) AND (X_PROGRAM_ID is null)))
265 AND ((recinfo.PROGRAM_APPLICATION_ID = X_PROGRAM_APPLICATION_ID)
266 OR ((recinfo.PROGRAM_APPLICATION_ID is null) AND (X_PROGRAM_APPLICATION_ID is null)))
267 AND ((recinfo.PROGRAM_UPDATE_DATE = X_PROGRAM_UPDATE_DATE)
268 OR ((recinfo.PROGRAM_UPDATE_DATE is null) AND (X_PROGRAM_UPDATE_DATE is null)))
269 AND ((recinfo.IMPLEMENTATION_REQ_ID = X_IMPLEMENTATION_REQ_ID)
270 OR ((recinfo.IMPLEMENTATION_REQ_ID is null) AND (X_IMPLEMENTATION_REQ_ID is null)))
271 -- Bug 4704384
272 AND ((recinfo.LOCAL_ORGANIZATION_ID = X_LOCAL_ORGANIZATION_ID)
273 OR ((recinfo.LOCAL_ORGANIZATION_ID is null) AND (X_LOCAL_ORGANIZATION_ID is null)))
274
275 ) then
276 null;
277 else
278 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
279 app_exception.raise_exception;
280 end if;
281
282 for tlinfo in c1 loop
283 if (tlinfo.BASELANG = 'Y') then
284 if ( ((tlinfo.DESCRIPTION = X_DESCRIPTION)
285 OR ((tlinfo.DESCRIPTION is null) AND (X_DESCRIPTION is null)))
286 ) then
287 null;
288 else
289 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
290 app_exception.raise_exception;
291 end if;
292 end if;
293 end loop;
294 return;
295 end LOCK_ROW;
296
297 procedure UPDATE_ROW (
298 X_ACTION_ID in NUMBER,
299 X_ORIGINAL_SYSTEM_REFERENCE in VARCHAR2,
300 X_WORKFLOW_ITEM_KEY in VARCHAR2,
301 X_REQUEST_ID in NUMBER,
302 X_STATUS_CODE in NUMBER,
303 X_PRIORITY_CODE in VARCHAR2,
304 X_ASSIGNEE_ID in NUMBER,
305 X_RESPONSE_BY_DATE in DATE,
306 X_PARTY_ID_LIST in VARCHAR2,
307 X_PARENT_STATUS_CODE in NUMBER,
308 X_WORKFLOW_ITEM_TYPE in VARCHAR2,
309 X_ROUTE_ID in NUMBER,
310 X_PARENT_ACTION_ID in NUMBER,
311 X_ACTION_TYPE in VARCHAR2,
312 X_OBJECT_NAME in VARCHAR2,
313 X_OBJECT_ID1 in NUMBER,
314 X_OBJECT_ID2 in NUMBER,
315 X_OBJECT_ID3 in NUMBER,
316 X_OBJECT_ID4 in NUMBER,
317 X_OBJECT_ID5 in NUMBER,
318 X_DESCRIPTION in VARCHAR2,
319 X_PROGRAM_ID in NUMBER,
320 X_PROGRAM_APPLICATION_ID in NUMBER,
321 X_PROGRAM_UPDATE_DATE in DATE,
322 X_LAST_UPDATE_DATE in DATE,
323 X_LAST_UPDATED_BY in NUMBER,
324 X_LAST_UPDATE_LOGIN in NUMBER,
325 X_IMPLEMENTATION_REQ_ID in NUMBER,
326 X_LOCAL_ORGANIZATION_ID in NUMBER DEFAULT NULL -- Bug 4704384
327
328 ) is
329 begin
330 update ENG_CHANGE_ACTIONS set
331 ORIGINAL_SYSTEM_REFERENCE = X_ORIGINAL_SYSTEM_REFERENCE,
332 WORKFLOW_ITEM_KEY = X_WORKFLOW_ITEM_KEY,
333 REQUEST_ID = X_REQUEST_ID,
334 STATUS_CODE = X_STATUS_CODE,
335 PRIORITY_CODE = X_PRIORITY_CODE,
336 ASSIGNEE_ID = X_ASSIGNEE_ID,
337 RESPONSE_BY_DATE = X_RESPONSE_BY_DATE,
338 PARTY_ID_LIST = X_PARTY_ID_LIST,
339 PARENT_STATUS_CODE = X_PARENT_STATUS_CODE,
340 WORKFLOW_ITEM_TYPE = X_WORKFLOW_ITEM_TYPE,
341 ROUTE_ID = X_ROUTE_ID,
342 PARENT_ACTION_ID = X_PARENT_ACTION_ID,
343 ACTION_TYPE = X_ACTION_TYPE,
344 OBJECT_NAME = X_OBJECT_NAME,
345 OBJECT_ID1 = X_OBJECT_ID1,
346 OBJECT_ID2 = X_OBJECT_ID2,
347 OBJECT_ID3 = X_OBJECT_ID3,
348 OBJECT_ID4 = X_OBJECT_ID4,
349 OBJECT_ID5 = X_OBJECT_ID5,
350 PROGRAM_ID = X_PROGRAM_ID,
351 PROGRAM_APPLICATION_ID = X_PROGRAM_APPLICATION_ID,
352 PROGRAM_UPDATE_DATE = X_PROGRAM_UPDATE_DATE,
353 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
354 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
355 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN,
356 IMPLEMENTATION_REQ_ID = X_IMPLEMENTATION_REQ_ID,
357 LOCAL_ORGANIZATION_ID = X_LOCAL_ORGANIZATION_ID -- Bug 4704384
358 where ACTION_ID = X_ACTION_ID;
359
360 if (sql%notfound) then
361 raise no_data_found;
362 end if;
363
364 update ENG_CHANGE_ACTIONS_TL set
365 DESCRIPTION = X_DESCRIPTION,
366 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
367 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
368 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN,
369 SOURCE_LANG = userenv('LANG')
370 where ACTION_ID = X_ACTION_ID
371 and userenv('LANG') in (LANGUAGE, SOURCE_LANG);
372
373 if (sql%notfound) then
374 raise no_data_found;
375 end if;
376 end UPDATE_ROW;
377
378 procedure DELETE_ROW (
379 X_ACTION_ID in NUMBER
380 ) is
381 begin
382 delete from ENG_CHANGE_ACTIONS_TL
383 where ACTION_ID = X_ACTION_ID;
384
385 if (sql%notfound) then
386 raise no_data_found;
387 end if;
388
389 delete from ENG_CHANGE_ACTIONS
390 where ACTION_ID = X_ACTION_ID;
391
392 if (sql%notfound) then
393 raise no_data_found;
394 end if;
395 end DELETE_ROW;
396
397 procedure ADD_LANGUAGE
398 is
399 begin
400 delete from ENG_CHANGE_ACTIONS_TL T
401 where not exists
402 (select NULL
403 from ENG_CHANGE_ACTIONS B
404 where B.ACTION_ID = T.ACTION_ID
405 );
406
407 update ENG_CHANGE_ACTIONS_TL T set (
408 DESCRIPTION
409 ) = (select
410 B.DESCRIPTION
411 from ENG_CHANGE_ACTIONS_TL B
412 where B.ACTION_ID = T.ACTION_ID
413 and B.LANGUAGE = T.SOURCE_LANG)
414 where (
415 T.ACTION_ID,
416 T.LANGUAGE
417 ) in (select
418 SUBT.ACTION_ID,
419 SUBT.LANGUAGE
420 from ENG_CHANGE_ACTIONS_TL SUBB, ENG_CHANGE_ACTIONS_TL SUBT
421 where SUBB.ACTION_ID = SUBT.ACTION_ID
422 and SUBB.LANGUAGE = SUBT.SOURCE_LANG
423 and (SUBB.DESCRIPTION <> SUBT.DESCRIPTION
424 or (SUBB.DESCRIPTION is null and SUBT.DESCRIPTION is not null)
425 or (SUBB.DESCRIPTION is not null and SUBT.DESCRIPTION is null)
426 ));
427
428 insert into ENG_CHANGE_ACTIONS_TL (
429 LAST_UPDATED_BY,
430 LAST_UPDATE_LOGIN,
431 LAST_UPDATE_DATE,
432 CREATED_BY,
433 DESCRIPTION,
434 CREATION_DATE,
435 ACTION_ID,
436 LANGUAGE,
437 SOURCE_LANG
438 ) select
439 B.LAST_UPDATED_BY,
440 B.LAST_UPDATE_LOGIN,
441 B.LAST_UPDATE_DATE,
442 B.CREATED_BY,
443 B.DESCRIPTION,
444 B.CREATION_DATE,
445 B.ACTION_ID,
446 L.LANGUAGE_CODE,
447 B.SOURCE_LANG
448 from ENG_CHANGE_ACTIONS_TL B, FND_LANGUAGES L
449 where L.INSTALLED_FLAG in ('I', 'B')
450 and B.LANGUAGE = userenv('LANG')
451 and not exists
452 (select NULL
453 from ENG_CHANGE_ACTIONS_TL T
454 where T.ACTION_ID = B.ACTION_ID
455 and T.LANGUAGE = L.LANGUAGE_CODE);
456 end ADD_LANGUAGE;
457
458
459 end ENG_CHANGE_ACTIONS_PKG;
[Home] [Help]