[Home] [Help]
PACKAGE BODY: APPS.AME_ITEM_CLASSES_API
Source
1 PACKAGE BODY AME_ITEM_CLASSES_API AS
2 /* $Header: ameicapi.pkb 120.11 2006/09/21 15:09:00 pvelugul 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_ITEM_CLASS_NAME in VARCHAR2,
17 X_ITEM_CLASS_ID out nocopy NUMBER,
18 X_ITEM_CLASS_ROWID out nocopy VARCHAR2,
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_ITEM_CLASS
24 (
25 X_ITEM_CLASS_NAME in VARCHAR2
26 ) is
27 select ROWID,
28 ITEM_CLASS_ID,
29 LAST_UPDATED_BY,
30 to_char(LAST_UPDATE_DATE,'YYYY/MM/DD HH24:MI:SS'),
31 nvl(OBJECT_VERSION_NUMBER,1)
32 from AME_ITEM_CLASSES
33 where NAME = X_ITEM_CLASS_NAME
34 and sysdate between START_DATE
35 and nvl(END_DATE - (1/86400), sysdate);
36 cursor CSR_MAX_ITEM_CLASS_ID is
37 select nvl(max(ITEM_CLASS_ID),4)
38 from AME_ITEM_CLASSES
39 where ITEM_CLASS_ID > 4;
40 L_ITEM_CLASS_ID NUMBER;
41 begin
42 X_CURRENT_OVN := 1;
43 open CSR_GET_CURRENT_ITEM_CLASS (
44 X_ITEM_CLASS_NAME
45 );
46 fetch CSR_GET_CURRENT_ITEM_CLASS into X_ITEM_CLASS_ROWID,
47 L_ITEM_CLASS_ID,
48 X_CURRENT_OWNER,
49 X_CURRENT_LAST_UPDATE_DATE,
50 X_CURRENT_OVN;
51 if (CSR_GET_CURRENT_ITEM_CLASS%notfound) then
52 X_ITEM_CLASS_ROWID := null;
53 select decode(X_ITEM_CLASS_NAME
54 ,'header',1
55 ,'line item',2
56 ,'cost center',3
57 ,'project code',4
58 ,ame_item_classes_s.nextVal)
59 into X_ITEM_CLASS_ID
60 from dual;
61 else
62 X_ITEM_CLASS_ID := L_ITEM_CLASS_ID;
63 end if;
64 close CSR_GET_CURRENT_ITEM_CLASS;
65 end KEY_TO_IDS;
66 function DO_UPDATE_INSERT(X_OWNER in NUMBER,
67 X_CURRENT_OWNER in NUMBER,
68 X_LAST_UPDATE_DATE in VARCHAR2,
69 X_CURRENT_LAST_UPDATE_DATE in VARCHAR2,
70 X_CUSTOM_MODE in VARCHAR2 default null)
71 return boolean as
72 begin
73 return AME_SEED_UTILITY.MERGE_ROW_TEST
74 (X_OWNER => X_OWNER
75 ,X_CURRENT_OWNER => X_CURRENT_OWNER
76 ,X_LAST_UPDATE_DATE => X_LAST_UPDATE_DATE
77 ,X_CURRENT_LAST_UPDATE_DATE => X_CURRENT_LAST_UPDATE_DATE
78 ,X_CUSTOM_MODE => X_CUSTOM_MODE
79 );
80 end DO_UPDATE_INSERT;
81 function DO_TL_UPDATE_INSERT(X_OWNER in NUMBER,
82 X_CURRENT_OWNER in NUMBER,
83 X_LAST_UPDATE_DATE in VARCHAR2,
84 X_CURRENT_LAST_UPDATE_DATE in VARCHAR2,
85 X_CREATED_BY in VARCHAR2,
86 X_CUSTOM_MODE in VARCHAR2 default null)
87 return boolean as
88 begin
89 if X_CUSTOM_MODE = 'FORCE' then
90 return true;
91 end if;
92 if AME_SEED_UTILITY.IS_SEED_USER(X_CREATED_BY) then
93 return true;
94 else
95 return AME_SEED_UTILITY.TL_MERGE_ROW_TEST
96 (X_OWNER => X_OWNER
97 ,X_CURRENT_OWNER => X_CURRENT_OWNER
98 ,X_LAST_UPDATE_DATE => X_LAST_UPDATE_DATE
99 ,X_CURRENT_LAST_UPDATE_DATE => X_CURRENT_LAST_UPDATE_DATE
100 ,X_CUSTOM_MODE => X_CUSTOM_MODE
101 );
102 end if;
103 return(false);
104 end DO_TL_UPDATE_INSERT;
105 procedure INSERT_ROW (
106 X_ITEM_CLASS_ID in NUMBER,
107 X_ITEM_CLASS_NAME in VARCHAR2,
108 X_CREATED_BY in NUMBER,
109 X_CREATION_DATE in DATE,
110 X_LAST_UPDATED_BY in NUMBER,
111 X_LAST_UPDATE_DATE in DATE,
112 X_LAST_UPDATE_LOGIN in NUMBER,
113 X_START_DATE in DATE,
114 X_OBJECT_VERSION_NUMBER in NUMBER)
115 is
116 begin
117 insert into AME_ITEM_CLASSES
118 (
119 ITEM_CLASS_ID,
120 NAME,
121 CREATED_BY,
122 CREATION_DATE,
123 LAST_UPDATED_BY,
124 LAST_UPDATE_DATE,
125 LAST_UPDATE_LOGIN,
126 START_DATE,
127 END_DATE,
128 OBJECT_VERSION_NUMBER)
129 values (
130 X_ITEM_CLASS_ID,
131 X_ITEM_CLASS_NAME,
132 X_CREATED_BY,
133 X_CREATION_DATE,
134 X_LAST_UPDATED_BY,
135 X_LAST_UPDATE_DATE,
136 X_LAST_UPDATE_LOGIN,
137 X_START_DATE,
138 AME_SEED_UTILITY.GET_DEFAULT_END_DATE,
139 X_OBJECT_VERSION_NUMBER);
140 end INSERT_ROW;
141
142 procedure INSERT_TL_ROW
143 (X_ITEM_CLASS_ID IN NUMBER
144 ,X_USER_ITEM_CLASS_NAME IN VARCHAR2
145 ,X_CREATED_BY IN NUMBER
146 ,X_CREATION_DATE IN DATE
147 ,X_LAST_UPDATED_BY IN NUMBER
148 ,X_LAST_UPDATE_DATE IN DATE
149 ,X_LAST_UPDATE_LOGIN IN NUMBER
150 ) AS
151 BEGIN
152 if not AME_SEED_UTILITY.MLS_ENABLED then
153 return;
154 end if;
155
156 insert into AME_ITEM_CLASSES_TL
157 (ITEM_CLASS_ID
158 ,USER_ITEM_CLASS_NAME
159 ,CREATED_BY
160 ,CREATION_DATE
161 ,LAST_UPDATED_BY
162 ,LAST_UPDATE_DATE
163 ,LAST_UPDATE_LOGIN
164 ,LANGUAGE
165 ,SOURCE_LANG
166 ) select X_ITEM_CLASS_ID,
167 X_USER_ITEM_CLASS_NAME,
168 X_CREATED_BY,
169 X_CREATION_DATE,
170 X_LAST_UPDATED_BY,
171 X_LAST_UPDATE_DATE,
172 X_LAST_UPDATE_LOGIN,
173 L.LANGUAGE_CODE,
174 userenv('LANG')
175 from FND_LANGUAGES L
176 where L.INSTALLED_FLAG in ('I', 'B')
177 and not exists (select null
178 from AME_ITEM_CLASSES_TL T
179 where T.ITEM_CLASS_ID = X_ITEM_CLASS_ID
180 and T.LANGUAGE = L.LANGUAGE_CODE);
181 END INSERT_TL_ROW;
182
183 procedure UPDATE_TL_ROW (
184 X_ITEM_CLASS_ID in NUMBER,
185 X_USER_ITEM_CLASS_NAME in VARCHAR2,
186 X_CREATED_BY in NUMBER,
187 X_CREATION_DATE in DATE,
188 X_LAST_UPDATED_BY in NUMBER,
189 X_LAST_UPDATE_DATE in DATE,
190 X_LAST_UPDATE_LOGIN in NUMBER,
191 X_CUSTOM_MODE in VARCHAR2) is
192 X_CURRENT_OWNER NUMBER;
193 X_CURRENT_LAST_UPDATE_DATE DATE;
194 begin
195 if not AME_SEED_UTILITY.MLS_ENABLED then
196 return;
197 end if;
198
199 select LAST_UPDATED_BY,
200 LAST_UPDATE_DATE
201 into X_CURRENT_OWNER,
202 X_CURRENT_LAST_UPDATE_DATE
203 FROM AME_ITEM_CLASSES_TL
204 WHERE ITEM_CLASS_ID = X_ITEM_CLASS_ID
205 AND LANGUAGE = USERENV('LANG');
206
207 if DO_UPDATE_INSERT
208 (X_LAST_UPDATED_BY
209 ,X_CURRENT_OWNER
210 ,AME_SEED_UTILITY.DATE_AS_STRING(X_LAST_UPDATE_DATE)
211 ,AME_SEED_UTILITY.DATE_AS_STRING(X_CURRENT_LAST_UPDATE_DATE)
212 ,X_CUSTOM_MODE) then
213 update AME_ITEM_CLASSES_TL
214 set USER_ITEM_CLASS_NAME = nvl(X_USER_ITEM_CLASS_NAME,USER_ITEM_CLASS_NAME),
215 SOURCE_LANG = userenv('LANG'),
216 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
217 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
218 LAST_UPDATE_LOGIN = 0
219 where ITEM_CLASS_ID = X_ITEM_CLASS_ID
220 and userenv('LANG') in (LANGUAGE,SOURCE_LANG);
221 end if;
222 exception
223 when no_data_found then
224 null;
225 end UPDATE_TL_ROW;
226
227 procedure FORCE_UPDATE_ROW (
228 X_ROWID in VARCHAR2,
229 X_ITEM_CLASS_NAME in VARCHAR2,
230 X_CREATED_BY in NUMBER,
231 X_CREATION_DATE in DATE,
232 X_LAST_UPDATED_BY in NUMBER,
233 X_LAST_UPDATE_DATE in DATE,
234 X_LAST_UPDATE_LOGIN in NUMBER,
235 X_START_DATE in DATE,
236 X_END_DATE in DATE,
237 X_OBJECT_VERSION_NUMBER in NUMBER
238 ) is
239 begin
240 update AME_ITEM_CLASSES
241 set NAME = X_ITEM_CLASS_NAME,
242 CREATED_BY = X_CREATED_BY,
243 CREATION_DATE = X_CREATION_DATE,
244 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
245 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
246 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN,
247 START_DATE = X_START_DATE,
248 END_DATE = X_END_DATE,
249 OBJECT_VERSION_NUMBER = X_OBJECT_VERSION_NUMBER
250 where ROWID = X_ROWID;
251 end FORCE_UPDATE_ROW;
252
253 procedure UPDATE_ROW (
254 X_ITEM_CLASS_ROWID in VARCHAR2,
255 X_END_DATE in DATE)
256 is
257 begin
258 update AME_ITEM_CLASSES set
259 END_DATE = X_END_DATE
260 where ROWID = X_ITEM_CLASS_ROWID;
261 end UPDATE_ROW;
262 procedure DELETE_ROW (
263 X_ITEM_CLASS_ID in NUMBER
264 ) is
265 begin
266 if AME_SEED_UTILITY.MLS_ENABLED then
267 delete from AME_ITEM_CLASSES_TL
268 where ITEM_CLASS_ID = X_ITEM_CLASS_ID;
269 end if;
270 delete from AME_ITEM_CLASSES
271 where ITEM_CLASS_ID = X_ITEM_CLASS_ID;
272 if (sql%notfound) then
273 raise no_data_found;
274 end if;
275 end DELETE_ROW;
276 procedure LOAD_ROW (
277 X_ITEM_CLASS_NAME in VARCHAR2,
278 X_USER_ITEM_CLASS_NAME in VARCHAR2,
279 X_ITEM_CLASS_ID in VARCHAR2,
280 X_OWNER in VARCHAR2,
281 X_LAST_UPDATE_DATE in VARCHAR2,
282 X_CUSTOM_MODE in VARCHAR2
283 )
284 is
285 X_CREATED_BY NUMBER;
286 X_CURRENT_LAST_UPDATE_DATE VARCHAR2(19);
287 X_CURRENT_OWNER NUMBER;
288 X_ITEM_CLASS_COUNT NUMBER;
289 X_ITEM_CLASS_ROWID ROWID;
290 X_LAST_UPDATED_BY NUMBER;
291 X_LAST_UPDATE_LOGIN NUMBER;
292 X_CURRENT_OVN NUMBER;
293 L_ITEM_CLASS_ID NUMBER;
294 X_LOCK_HANDLE varchar2(500);
295 X_RETURN_VALUE number;
296 begin
297 -- retrieve information for the current row
298 DBMS_LOCK.ALLOCATE_UNIQUE
299 (LOCKNAME =>'AME_ITEM_CLASSES.'||X_ITEM_CLASS_NAME
300 ,LOCKHANDLE => X_LOCK_HANDLE
301 );
302 X_RETURN_VALUE := DBMS_LOCK.REQUEST
303 (LOCKHANDLE => X_LOCK_HANDLE
304 ,TIMEOUT => 0
305 ,RELEASE_ON_COMMIT => true);
306 if X_RETURN_VALUE = 0 then
307 -- retrieve information for the current row
308 KEY_TO_IDS (
309 X_ITEM_CLASS_NAME,
310 L_ITEM_CLASS_ID,
311 X_ITEM_CLASS_ROWID,
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_ITEM_CLASS_ROWID is null then
326 INSERT_ROW (
327 L_ITEM_CLASS_ID,
328 X_ITEM_CLASS_NAME,
329 X_CREATED_BY,
330 to_date(X_LAST_UPDATE_DATE,'YYYY/MM/DD HH24:MI:SS'),
331 X_LAST_UPDATED_BY,
332 to_date(X_LAST_UPDATE_DATE,'YYYY/MM/DD HH24:MI:SS'),
333 X_LAST_UPDATE_LOGIN,
334 to_date(X_LAST_UPDATE_DATE,'YYYY/MM/DD HH24:MI:SS'),
335 1);
336 INSERT_TL_ROW
337 (L_ITEM_CLASS_ID,
338 nvl(X_USER_ITEM_CLASS_NAME,X_ITEM_CLASS_NAME),
339 X_CREATED_BY,
340 to_date(X_LAST_UPDATE_DATE,'YYYY/MM/DD HH24:MI:SS'),
341 X_LAST_UPDATED_BY,
342 to_date(X_LAST_UPDATE_DATE,'YYYY/MM/DD HH24:MI:SS'),
343 X_LAST_UPDATE_LOGIN
344 );
345 -- the current row was found end date the current row
346 -- insert a row with the same action type id
347 else
348 if X_CUSTOM_MODE = 'FORCE' then
349 FORCE_UPDATE_ROW
350 (X_ITEM_CLASS_ROWID,
351 X_ITEM_CLASS_NAME,
352 X_CREATED_BY,
353 to_date(X_LAST_UPDATE_DATE,'YYYY/MM/DD HH24:MI:SS'),
354 X_LAST_UPDATED_BY,
358 AME_SEED_UTILITY.GET_DEFAULT_END_DATE,
355 to_date(X_LAST_UPDATE_DATE,'YYYY/MM/DD HH24:MI:SS'),
356 X_LAST_UPDATE_LOGIN,
357 to_date(X_LAST_UPDATE_DATE,'YYYY/MM/DD HH24:MI:SS'),
359 X_CURRENT_OVN + 1
360 );
361 UPDATE_TL_ROW
362 (L_ITEM_CLASS_ID,
363 nvl(X_USER_ITEM_CLASS_NAME,X_ITEM_CLASS_NAME),
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 X_CUSTOM_MODE
370 );
371 else
372 if DO_UPDATE_INSERT
373 (AME_SEED_UTILITY.OWNER_AS_INTEGER(X_OWNER),
374 X_CURRENT_OWNER,
375 X_LAST_UPDATE_DATE,
376 X_CURRENT_LAST_UPDATE_DATE) then
377 UPDATE_ROW (
378 X_ITEM_CLASS_ROWID,
379 to_date(X_LAST_UPDATE_DATE,'YYYY/MM/DD HH24:MI:SS')-(1/86400));
380 INSERT_ROW (
381 L_ITEM_CLASS_ID,
382 X_ITEM_CLASS_NAME,
383 X_CREATED_BY,
384 to_date(X_LAST_UPDATE_DATE,'YYYY/MM/DD HH24:MI:SS'),
385 X_LAST_UPDATED_BY,
386 to_date(X_LAST_UPDATE_DATE,'YYYY/MM/DD HH24:MI:SS'),
387 X_LAST_UPDATE_LOGIN,
388 to_date(X_LAST_UPDATE_DATE,'YYYY/MM/DD HH24:MI:SS'),
389 X_CURRENT_OVN + 1);
390 UPDATE_TL_ROW
391 (L_ITEM_CLASS_ID,
392 nvl(X_USER_ITEM_CLASS_NAME,X_ITEM_CLASS_NAME),
393 X_CREATED_BY,
394 to_date(X_LAST_UPDATE_DATE,'YYYY/MM/DD HH24:MI:SS'),
395 X_LAST_UPDATED_BY,
396 to_date(X_LAST_UPDATE_DATE,'YYYY/MM/DD HH24:MI:SS'),
397 X_LAST_UPDATE_LOGIN,
398 X_CUSTOM_MODE
399 );
400 end if;
401 end if;
402 end if;
403 end;
404 end if;
405 exception
406 when others then
407 ame_util.runtimeException('ame_item_classes_api',
408 'load_row',
409 sqlcode,
410 sqlerrm);
411 raise;
412 end LOAD_ROW;
413
414 procedure TRANSLATE_ROW
415 (X_ITEM_CLASS_NAME in varchar2
416 ,X_USER_ITEM_CLASS_NAME in varchar2
417 ,X_OWNER in varchar2
418 ,X_LAST_UPDATE_DATE in varchar2
419 ,X_CUSTOM_MODE in varchar2
420 ) as
421 L_ITEM_CLASS_ID integer;
422 X_CURRENT_OWNER NUMBER;
423 X_CURRENT_LAST_UPDATE_DATE varchar2(20);
424 X_CREATED_BY varchar2(100);
425 begin
426 if not AME_SEED_UTILITY.MLS_ENABLED then
427 return;
428 end if;
429
430 begin
431 select AICTL.LAST_UPDATED_BY,
432 AME_SEED_UTILITY.DATE_AS_STRING(AICTL.LAST_UPDATE_DATE),
433 AME_SEED_UTILITY.OWNER_AS_STRING(AICTL.CREATED_BY),
434 AICTL.ITEM_CLASS_ID
435 into X_CURRENT_OWNER,
436 X_CURRENT_LAST_UPDATE_DATE,
437 X_CREATED_BY,
438 L_ITEM_CLASS_ID
439 from AME_ITEM_CLASSES_TL AICTL,
440 AME_ITEM_CLASSES AIC
441 where AICTL.ITEM_CLASS_ID = AIC.ITEM_CLASS_ID
442 and AIC.NAME = X_ITEM_CLASS_NAME
443 and sysdate between AIC.START_DATE and nvl(AIC.END_DATE - (1/86400),sysdate)
444 and AICTL.LANGUAGE = userenv('LANG');
445
446 if DO_TL_UPDATE_INSERT
447 (X_OWNER => AME_SEED_UTILITY.OWNER_AS_INTEGER(X_OWNER),
448 X_CURRENT_OWNER => X_CURRENT_OWNER,
449 X_LAST_UPDATE_DATE => X_LAST_UPDATE_DATE,
450 X_CURRENT_LAST_UPDATE_DATE => X_CURRENT_LAST_UPDATE_DATE,
451 X_CREATED_BY => X_CREATED_BY,
452 X_CUSTOM_MODE => X_CUSTOM_MODE) then
453 update AME_ITEM_CLASSES_TL AICTL
454 set USER_ITEM_CLASS_NAME = nvl(X_USER_ITEM_CLASS_NAME,AICTL.USER_ITEM_CLASS_NAME),
455 SOURCE_LANG = userenv('LANG'),
456 LAST_UPDATE_DATE = AME_SEED_UTILITY.DATE_AS_DATE(X_LAST_UPDATE_DATE),
457 LAST_UPDATED_BY = AME_SEED_UTILITY.OWNER_AS_INTEGER(X_OWNER),
458 LAST_UPDATE_LOGIN = 0
459 where AICTL.ITEM_CLASS_ID = L_ITEM_CLASS_ID
460 and userenv('LANG') in (AICTL.LANGUAGE,AICTL.SOURCE_LANG);
461 END IF;
462 exception
463 when no_data_found then
464 null;
465 end;
466 end TRANSLATE_ROW;
467 END AME_ITEM_CLASSES_API;