[Home] [Help]
PACKAGE BODY: APPS.AME_ACTION_TYPES_API
Source
1 PACKAGE BODY AME_ACTION_TYPES_API AS
2 /* $Header: ameacapi.pkb 120.6 2005/10/14 04:10:32 ubhat noship $ */
3 procedure OWNER_TO_WHO (
4 X_OWNER in VARCHAR2,
5 X_CREATED_BY out nocopy NUMBER,
6 X_LAST_UPDATED_BY out nocopy NUMBER,
7 X_LAST_UPDATE_LOGIN out nocopy NUMBER
8 ) is
9 begin
10 X_CREATED_BY := AME_SEED_UTILITY.OWNER_AS_INTEGER(X_OWNER);
11 X_LAST_UPDATED_BY := AME_SEED_UTILITY.OWNER_AS_INTEGER(X_OWNER);
12 X_LAST_UPDATE_LOGIN := 0;
13 end OWNER_TO_WHO;
14
15 procedure KEY_TO_IDS (
16 X_ACTION_TYPE_NAME in VARCHAR2,
17 X_ACTION_TYPE_ROWID out nocopy VARCHAR2,
18 X_ACTION_TYPE_ID out nocopy NUMBER,
19 X_CURRENT_OWNER out nocopy NUMBER,
20 X_CURRENT_LAST_UPDATE_DATE out nocopy VARCHAR2,
21 X_CURRENT_OVN out nocopy NUMBER
22 ) is
23 cursor CSR_GET_CURRENT_ACTION_TYPE
24 (
25 X_ATTRIBUTE_NAME in VARCHAR2
26 ) is
27 select ROWID, ACTION_TYPE_ID,
28 LAST_UPDATED_BY,
29 to_char(LAST_UPDATE_DATE,'YYYY/MM/DD HH24:MI:SS'),
30 nvl(OBJECT_VERSION_NUMBER,1)
31 from AME_ACTION_TYPES
32 where NAME = X_ACTION_TYPE_NAME
33 and sysdate between START_DATE
34 and nvl(END_DATE - (1/86400), sysdate);
35 begin
36 X_CURRENT_OVN := 1;
37 open CSR_GET_CURRENT_ACTION_TYPE (
38 X_ACTION_TYPE_NAME
39 );
40 fetch CSR_GET_CURRENT_ACTION_TYPE into X_ACTION_TYPE_ROWID, X_ACTION_TYPE_ID,
41 X_CURRENT_OWNER, X_CURRENT_LAST_UPDATE_DATE, X_CURRENT_OVN;
42 if (CSR_GET_CURRENT_ACTION_TYPE%notfound) then
43 X_ACTION_TYPE_ID := null;
44 end if;
45 close CSR_GET_CURRENT_ACTION_TYPE;
46 end KEY_TO_IDS;
47 function DO_UPDATE_INSERT(X_OWNER in NUMBER,
48 X_CURRENT_OWNER in NUMBER,
49 X_LAST_UPDATE_DATE in VARCHAR2,
50 X_CURRENT_LAST_UPDATE_DATE in VARCHAR2,
51 X_CUSTOM_MODE in VARCHAR2 default null)
52 return boolean as
53 begin
54 return AME_SEED_UTILITY.MERGE_ROW_TEST
55 (X_OWNER => X_OWNER
56 ,X_CURRENT_OWNER => X_CURRENT_OWNER
57 ,X_LAST_UPDATE_DATE => X_LAST_UPDATE_DATE
58 ,X_CURRENT_LAST_UPDATE_DATE => X_CURRENT_LAST_UPDATE_DATE
59 ,X_CUSTOM_MODE => X_CUSTOM_MODE
60 );
61 end DO_UPDATE_INSERT;
62 function DO_TL_UPDATE_INSERT(X_OWNER in NUMBER,
63 X_CURRENT_OWNER in NUMBER,
64 X_LAST_UPDATE_DATE in VARCHAR2,
65 X_CURRENT_LAST_UPDATE_DATE in VARCHAR2,
66 X_CURRENT_CREATED_BY in VARCHAR2,
67 X_CUSTOM_MODE in VARCHAR2 default null)
68 return boolean as
69 begin
70 if X_CUSTOM_MODE = 'FORCE' then
71 return true;
72 end if;
73 if AME_SEED_UTILITY.IS_SEED_USER(X_CURRENT_CREATED_BY) then
74 return true;
75 else
76 return AME_SEED_UTILITY.TL_MERGE_ROW_TEST
77 (X_OWNER => X_OWNER
78 ,X_CURRENT_OWNER => X_CURRENT_OWNER
79 ,X_LAST_UPDATE_DATE => X_LAST_UPDATE_DATE
80 ,X_CURRENT_LAST_UPDATE_DATE => X_CURRENT_LAST_UPDATE_DATE
81 ,X_CUSTOM_MODE => X_CUSTOM_MODE
82 );
83 end if;
84 return(false);
85 end DO_TL_UPDATE_INSERT;
86 procedure INSERT_ROW (
87 X_ACTION_TYPE_ID in NUMBER,
88 X_NAME in VARCHAR2,
89 X_PROCEDURE_NAME in VARCHAR2,
90 X_DYNAMIC_DESCRIPTION in VARCHAR2,
91 X_DESCRIPTION_QUERY in VARCHAR2,
92 X_CREATED_BY in NUMBER,
93 X_CREATION_DATE in DATE,
94 X_LAST_UPDATED_BY in NUMBER,
95 X_LAST_UPDATE_DATE in DATE,
96 X_LAST_UPDATE_LOGIN in NUMBER,
97 X_START_DATE in DATE,
98 X_DESCRIPTION in VARCHAR2,
99 X_OBJECT_VERSION_NUMBER in NUMBER)
100 is
101
102 begin
103
104 insert into AME_ACTION_TYPES
105 (
106 ACTION_TYPE_ID,
107 NAME,
108 PROCEDURE_NAME,
109 DYNAMIC_DESCRIPTION,
110 DESCRIPTION_QUERY,
111 CREATED_BY,
112 CREATION_DATE,
113 LAST_UPDATED_BY,
114 LAST_UPDATE_DATE,
115 LAST_UPDATE_LOGIN,
116 START_DATE,
117 END_DATE,
118 DESCRIPTION,
119 OBJECT_VERSION_NUMBER
120 ) values (
121 X_ACTION_TYPE_ID,
122 X_NAME,
123 X_PROCEDURE_NAME,
124 X_DYNAMIC_DESCRIPTION,
125 X_DESCRIPTION_QUERY,
126 X_CREATED_BY,
127 X_CREATION_DATE,
128 X_LAST_UPDATED_BY,
129 X_LAST_UPDATE_DATE,
130 X_LAST_UPDATE_LOGIN,
131 X_START_DATE,
132 AME_SEED_UTILITY.GET_DEFAULT_END_DATE,
133 X_DESCRIPTION,
134 X_OBJECT_VERSION_NUMBER);
135
136 end INSERT_ROW;
137
138 procedure INSERT_TL_ROW (
139 X_ACTION_TYPE_ID in NUMBER,
140 X_USER_ACTION_TYPE_NAME in VARCHAR2,
141 X_DESCRIPTION in VARCHAR2,
142 X_CREATED_BY in NUMBER,
143 X_CREATION_DATE in DATE,
144 X_LAST_UPDATED_BY in NUMBER,
145 X_LAST_UPDATE_DATE in DATE,
146 X_LAST_UPDATE_LOGIN in NUMBER) is
147 begin
148 if not AME_SEED_UTILITY.MLS_ENABLED then
149 return;
150 end if;
151 insert into AME_ACTION_TYPES_TL
152 (ACTION_TYPE_ID
153 ,USER_ACTION_TYPE_NAME
154 ,DESCRIPTION
155 ,CREATED_BY
156 ,CREATION_DATE
157 ,LAST_UPDATED_BY
158 ,LAST_UPDATE_DATE
159 ,LAST_UPDATE_LOGIN
160 ,LANGUAGE
161 ,SOURCE_LANG
162 ) select X_ACTION_TYPE_ID,
163 X_USER_ACTION_TYPE_NAME,
164 X_DESCRIPTION,
165 X_CREATED_BY,
166 X_CREATION_DATE,
167 X_LAST_UPDATED_BY,
168 X_LAST_UPDATE_DATE,
169 X_LAST_UPDATE_LOGIN,
170 L.LANGUAGE_CODE,
171 userenv('LANG')
172 from FND_LANGUAGES L
173 where L.INSTALLED_FLAG in ('I', 'B')
174 and not exists (select null
175 from AME_ACTION_TYPES_TL T
176 where T.ACTION_TYPE_ID = X_ACTION_TYPE_ID
177 and T.LANGUAGE = L.LANGUAGE_CODE);
178 END INSERT_TL_ROW;
179
180 procedure UPDATE_TL_ROW (
181 X_ACTION_TYPE_ID in NUMBER,
182 X_USER_ACTION_TYPE_NAME in VARCHAR2,
183 X_DESCRIPTION in VARCHAR2,
184 X_CREATED_BY in NUMBER,
185 X_CREATION_DATE in DATE,
186 X_LAST_UPDATED_BY in NUMBER,
187 X_LAST_UPDATE_DATE in DATE,
188 X_LAST_UPDATE_LOGIN in NUMBER,
189 X_CUSTOM_MODE in VARCHAR2) is
190 X_CURRENT_OWNER NUMBER;
191 X_CURRENT_LAST_UPDATE_DATE DATE;
192 begin
193 if not AME_SEED_UTILITY.MLS_ENABLED then
194 return;
195 end if;
196 select LAST_UPDATED_BY,
197 LAST_UPDATE_DATE
198 into X_CURRENT_OWNER,
199 X_CURRENT_LAST_UPDATE_DATE
200 FROM AME_ACTION_TYPES_TL
201 WHERE ACTION_TYPE_ID = X_ACTION_TYPE_ID
202 AND LANGUAGE = USERENV('LANG');
203
204 if DO_UPDATE_INSERT
205 (X_LAST_UPDATED_BY
206 ,X_CURRENT_OWNER
207 ,AME_SEED_UTILITY.DATE_AS_STRING(X_LAST_UPDATE_DATE)
208 ,AME_SEED_UTILITY.DATE_AS_STRING(X_CURRENT_LAST_UPDATE_DATE)
209 ,X_CUSTOM_MODE) then
210 update AME_ACTION_TYPES_TL
211 set USER_ACTION_TYPE_NAME = nvl(X_USER_ACTION_TYPE_NAME,USER_ACTION_TYPE_NAME),
212 DESCRIPTION = nvl(X_DESCRIPTION,DESCRIPTION),
213 SOURCE_LANG = userenv('LANG'),
214 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
215 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
216 LAST_UPDATE_LOGIN = 0
217 where ACTION_TYPE_ID = X_ACTION_TYPE_ID
218 and userenv('LANG') in (LANGUAGE,SOURCE_LANG);
219 end if;
220 exception
221 when no_data_found then
222 null;
223 end UPDATE_TL_ROW;
224
225 procedure UPDATE_ROW (
226 X_ACTION_TYPE_ROWID in VARCHAR2,
227 X_END_DATE in DATE)
228 is
229 begin
230 update AME_ACTION_TYPES set
231 END_DATE = X_END_DATE
232 where ROWID = X_ACTION_TYPE_ROWID;
233 end UPDATE_ROW;
234
235 procedure FORCE_UPDATE_ROW (
236 X_ROWID in VARCHAR2,
237 X_NAME in VARCHAR2,
238 X_PROCEDURE_NAME in VARCHAR2,
239 X_DYNAMIC_DESCRIPTION in VARCHAR2,
240 X_DESCRIPTION_QUERY in VARCHAR2,
241 X_DESCRIPTION in VARCHAR2,
242 X_CREATED_BY in NUMBER,
243 X_CREATION_DATE in DATE,
244 X_LAST_UPDATED_BY in NUMBER,
245 X_LAST_UPDATE_DATE in DATE,
246 X_LAST_UPDATE_LOGIN in NUMBER,
247 X_START_DATE in DATE,
248 X_END_DATE in DATE,
249 X_OBJECT_VERSION_NUMBER in NUMBER
250 ) is
251 begin
252 update AME_ACTION_TYPES
253 set NAME = X_NAME,
254 PROCEDURE_NAME = X_PROCEDURE_NAME,
255 DYNAMIC_DESCRIPTION = X_DYNAMIC_DESCRIPTION,
256 DESCRIPTION_QUERY = X_DESCRIPTION_QUERY,
257 DESCRIPTION = X_DESCRIPTION,
258 CREATED_BY = X_CREATED_BY,
259 CREATION_DATE = X_CREATION_DATE,
260 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
261 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
262 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN,
263 START_DATE = X_START_DATE,
264 END_DATE = X_END_DATE,
265 OBJECT_VERSION_NUMBER = X_OBJECT_VERSION_NUMBER
266 where ROWID = X_ROWID;
267 end FORCE_UPDATE_ROW;
268
269 procedure DELETE_ROW (
270 X_ACTION_TYPE_ID in NUMBER
271 ) is
272 begin
273 if AME_SEED_UTILITY.MLS_ENABLED then
274 delete from AME_ACTION_TYPES_TL
275 where ACTION_TYPE_ID = X_ACTION_TYPE_ID;
276 end if;
277 delete from AME_ACTION_TYPES
278 where ACTION_TYPE_ID = X_ACTION_TYPE_ID;
279
280 if (sql%notfound) then
281 raise no_data_found;
282 end if;
283 end DELETE_ROW;
284
285
286 procedure LOAD_ROW (
287 X_ACTION_TYPE_NAME in VARCHAR2,
288 X_USER_ACTION_TYPE_NAME in VARCHAR2,
289 X_PROCEDURE_NAME in VARCHAR2,
290 X_DESCRIPTION in VARCHAR2,
291 X_DYNAMIC_DESCRIPTION in VARCHAR2,
292 X_DESCRIPTION_QUERY in VARCHAR2,
293 X_OWNER in VARCHAR2,
294 X_LAST_UPDATE_DATE in VARCHAR2,
295 X_CUSTOM_MODE in VARCHAR2
296 )
297 is
298 X_ACTION_TYPE_ROWID ROWID;
299 X_ACTION_TYPE_ID NUMBER;
300 X_CREATED_BY NUMBER;
301 X_CURRENT_LAST_UPDATE_DATE VARCHAR2(19);
302 X_CURRENT_OWNER NUMBER;
303 X_LAST_UPDATED_BY NUMBER;
304 X_LAST_UPDATE_LOGIN NUMBER;
305 X_CURRENT_OVN NUMBER;
306 begin
307 -- retrieve information for the current row
308 KEY_TO_IDS (
309 X_ACTION_TYPE_NAME,
310 X_ACTION_TYPE_ROWID,
311 X_ACTION_TYPE_ID,
312 X_CURRENT_OWNER,
313 X_CURRENT_LAST_UPDATE_DATE,
314 X_CURRENT_OVN
315 );
316 -- obtain who column details
317 OWNER_TO_WHO (
318 X_OWNER,
319 X_CREATED_BY,
320 X_LAST_UPDATED_BY,
321 X_LAST_UPDATE_LOGIN
322 );
323 begin
324 -- the current row was not found insert a new row
325 if X_ACTION_TYPE_ID is null then
326 select ame_action_types_s.nextval into X_ACTION_TYPE_ID from dual;
327 INSERT_ROW (
328 X_ACTION_TYPE_ID,
329 X_ACTION_TYPE_NAME,
330 X_PROCEDURE_NAME,
331 X_DYNAMIC_DESCRIPTION,
332 X_DESCRIPTION_QUERY,
333 X_CREATED_BY,
334 to_date(X_LAST_UPDATE_DATE,'YYYY/MM/DD HH24:MI:SS'),
335 X_LAST_UPDATED_BY,
339 X_DESCRIPTION,
336 to_date(X_LAST_UPDATE_DATE,'YYYY/MM/DD HH24:MI:SS'),
337 X_LAST_UPDATE_LOGIN,
338 to_date(X_LAST_UPDATE_DATE,'YYYY/MM/DD HH24:MI:SS'),
340 1);
341
342 INSERT_TL_ROW
343 (X_ACTION_TYPE_ID,
344 nvl(X_USER_ACTION_TYPE_NAME,X_ACTION_TYPE_NAME),
345 X_DESCRIPTION,
346 X_CREATED_BY,
347 to_date(X_LAST_UPDATE_DATE,'YYYY/MM/DD HH24:MI:SS'),
348 X_LAST_UPDATED_BY,
349 to_date(X_LAST_UPDATE_DATE,'YYYY/MM/DD HH24:MI:SS'),
350 X_LAST_UPDATE_LOGIN
351 );
352
353 -- the current row was found end date the current row
354 -- insert a row with the same action type id
355 else
356 if X_CUSTOM_MODE = 'FORCE' then
357 FORCE_UPDATE_ROW (
358 X_ACTION_TYPE_ROWID,
359 X_ACTION_TYPE_NAME,
360 X_PROCEDURE_NAME,
361 X_DYNAMIC_DESCRIPTION,
362 X_DESCRIPTION_QUERY,
363 X_DESCRIPTION,
364 X_CREATED_BY,
365 to_date(X_LAST_UPDATE_DATE,'YYYY/MM/DD HH24:MI:SS'),
366 X_LAST_UPDATED_BY,
367 to_date(X_LAST_UPDATE_DATE,'YYYY/MM/DD HH24:MI:SS'),
368 X_LAST_UPDATE_LOGIN,
369 to_date(X_LAST_UPDATE_DATE,'YYYY/MM/DD HH24:MI:SS'),
370 AME_SEED_UTILITY.GET_DEFAULT_END_DATE,
371 X_CURRENT_OVN + 1);
372 UPDATE_TL_ROW
373 (X_ACTION_TYPE_ID,
374 nvl(X_USER_ACTION_TYPE_NAME,X_ACTION_TYPE_NAME),
375 X_DESCRIPTION,
376 X_CREATED_BY,
377 to_date(X_LAST_UPDATE_DATE,'YYYY/MM/DD HH24:MI:SS'),
378 X_LAST_UPDATED_BY,
379 to_date(X_LAST_UPDATE_DATE,'YYYY/MM/DD HH24:MI:SS'),
380 X_LAST_UPDATE_LOGIN,
381 X_CUSTOM_MODE
382 );
383 else
384 if DO_UPDATE_INSERT
385 (AME_SEED_UTILITY.OWNER_AS_INTEGER(X_OWNER),
386 X_CURRENT_OWNER,
387 X_LAST_UPDATE_DATE,
388 X_CURRENT_LAST_UPDATE_DATE) then
389 UPDATE_ROW (
390 X_ACTION_TYPE_ROWID,
391 to_date(X_LAST_UPDATE_DATE,'YYYY/MM/DD HH24:MI:SS')-(1/86400));
392 INSERT_ROW (
393 X_ACTION_TYPE_ID,
394 X_ACTION_TYPE_NAME,
395 X_PROCEDURE_NAME,
396 X_DYNAMIC_DESCRIPTION,
397 X_DESCRIPTION_QUERY,
398 X_CREATED_BY,
399 to_date(X_LAST_UPDATE_DATE,'YYYY/MM/DD HH24:MI:SS'),
400 X_LAST_UPDATED_BY,
401 to_date(X_LAST_UPDATE_DATE,'YYYY/MM/DD HH24:MI:SS'),
402 X_LAST_UPDATE_LOGIN,
403 to_date(X_LAST_UPDATE_DATE,'YYYY/MM/DD HH24:MI:SS'),
404 X_DESCRIPTION,
405 X_CURRENT_OVN + 1);
406 UPDATE_TL_ROW
407 (X_ACTION_TYPE_ID,
408 nvl(X_USER_ACTION_TYPE_NAME,X_ACTION_TYPE_NAME),
409 X_DESCRIPTION,
410 X_CREATED_BY,
411 to_date(X_LAST_UPDATE_DATE,'YYYY/MM/DD HH24:MI:SS'),
412 X_LAST_UPDATED_BY,
413 to_date(X_LAST_UPDATE_DATE,'YYYY/MM/DD HH24:MI:SS'),
414 X_LAST_UPDATE_LOGIN,
415 X_CUSTOM_MODE
416 );
417 end if;
418 end if;
419 end if;
420 end;
421 exception
422 when others then
423 ame_util.runtimeException('ame_action_types_api',
424 'load_row',
425 sqlcode,
426 sqlerrm);
427 raise;
428 end LOAD_ROW;
429
430 procedure LOAD_ROW (
431 X_ACTION_TYPE_NAME in VARCHAR2,
432 X_PROCEDURE_NAME in VARCHAR2,
433 X_DESCRIPTION in VARCHAR2,
434 X_OWNER in VARCHAR2,
435 X_LAST_UPDATE_DATE in VARCHAR2
436 )
437 is
438 begin
439 null;
440 end LOAD_ROW;
441
442 procedure TRANSLATE_ROW
443 (X_ACTION_TYPE_NAME in varchar2
444 ,X_USER_ACTION_TYPE_NAME in varchar2
445 ,X_DESCRIPTION in varchar2
446 ,X_OWNER in varchar2
447 ,X_LAST_UPDATE_DATE in varchar2
448 ,X_CUSTOM_MODE in varchar2
449 ) as
450 X_CURRENT_OWNER number;
451 X_CURRENT_LAST_UPDATE_DATE varchar2(20);
452 X_CURRENT_CREATED_BY varchar2(100);
453 X_ACTION_TYPE_ID number;
454 begin
455 if not AME_SEED_UTILITY.MLS_ENABLED then
456 return;
457 end if;
458 begin
459 select AATTL.LAST_UPDATED_BY,
460 AME_SEED_UTILITY.DATE_AS_STRING(AATTL.LAST_UPDATE_DATE),
461 AME_SEED_UTILITY.OWNER_AS_STRING(AATTL.CREATED_BY),
462 AAT.ACTION_TYPE_ID
463 into X_CURRENT_OWNER,
464 X_CURRENT_LAST_UPDATE_DATE,
465 X_CURRENT_CREATED_BY,
466 X_ACTION_TYPE_ID
467 from AME_ACTION_TYPES_TL AATTL,
468 AME_ACTION_TYPES AAT
469 where AAT.NAME = X_ACTION_TYPE_NAME
470 and AAT.ACTION_TYPE_ID = AATTL.ACTION_TYPE_ID
471 and sysdate between AAT.START_DATE and nvl(AAT.END_DATE - (1/86400),sysdate)
472 and AATTL.ACTION_TYPE_ID = AAT.ACTION_TYPE_ID
473 and AATTL.LANGUAGE = userenv('LANG');
474 if DO_TL_UPDATE_INSERT
475 (X_OWNER => AME_SEED_UTILITY.OWNER_AS_INTEGER(X_OWNER),
476 X_CURRENT_OWNER => X_CURRENT_OWNER,
477 X_LAST_UPDATE_DATE => X_LAST_UPDATE_DATE,
478 X_CURRENT_LAST_UPDATE_DATE => X_CURRENT_LAST_UPDATE_DATE,
479 X_CURRENT_CREATED_BY => X_CURRENT_CREATED_BY,
480 X_CUSTOM_MODE => X_CUSTOM_MODE) then
481 update AME_ACTION_TYPES_TL AATTL
485 LAST_UPDATE_DATE = AME_SEED_UTILITY.DATE_AS_DATE(X_LAST_UPDATE_DATE),
482 set USER_ACTION_TYPE_NAME = nvl(X_USER_ACTION_TYPE_NAME,USER_ACTION_TYPE_NAME),
483 DESCRIPTION = nvl(X_DESCRIPTION,DESCRIPTION),
484 SOURCE_LANG = userenv('LANG'),
486 LAST_UPDATED_BY = AME_SEED_UTILITY.OWNER_AS_INTEGER(X_OWNER),
487 LAST_UPDATE_LOGIN = 0
488 where AATTL.ACTION_TYPE_ID = X_ACTION_TYPE_ID
489 and userenv('LANG') in (AATTL.LANGUAGE,AATTL.SOURCE_LANG);
490 end if;
491 exception
492 when no_data_found then
493 null;
494 end;
495 end TRANSLATE_ROW;
496
497 end AME_ACTION_TYPES_API;