[Home] [Help]
PACKAGE BODY: APPS.AMS_FUND_UTILIZED_ALL_PKG
Source
1 package body AMS_FUND_UTILIZED_ALL_PKG as
2 /* $Header: amslfutb.pls 115.2 2002/07/15 22:12:04 mpande noship $ */
3 procedure INSERT_ROW (
4 X_ROWID IN OUT VARCHAR2,
5 P_UTILIZATION_ID IN NUMBER,
6 P_LAST_UPDATE_DATE IN DATE,
7 P_LAST_UPDATED_BY IN NUMBER,
8 P_LAST_UPDATE_LOGIN IN NUMBER,
9 P_CREATION_DATE IN DATE,
10 P_CREATED_BY IN NUMBER,
11 P_CREATED_FROM IN VARCHAR2,
12 P_REQUEST_ID IN NUMBER,
13 P_UTILIZATION_TYPE IN VARCHAR2,
14 P_FUND_ID IN NUMBER,
15 P_PLAN_TYPE IN VARCHAR2,
16 P_PLAN_ID IN NUMBER,
17 P_COMPONENT_TYPE IN VARCHAR2,
18 P_COMPONENT_ID IN NUMBER,
19 P_OBJECT_TYPE IN VARCHAR2,
20 P_OBJECT_ID IN NUMBER,
21 P_ORDER_ID IN NUMBER,
22 P_INVOICE_ID IN NUMBER,
23 P_AMOUNT IN NUMBER,
24 P_ACCTD_AMOUNT IN NUMBER,
25 P_CURRENCY_CODE IN VARCHAR2,
26 P_EXCHANGE_RATE_TYPE IN VARCHAR2,
27 P_EXCHANGE_RATE_DATE IN DATE,
28 P_EXCHANGE_RATE IN NUMBER,
29 P_ADJUSTMENT_TYPE IN VARCHAR2,
30 P_ADJUSTMENT_DATE IN DATE,
31 P_ADJUSTMENT_DESC IN VARCHAR2,
32 P_OBJECT_VERSION_NUMBER IN NUMBER,
33 P_ATTRIBUTE_CATEGORY IN VARCHAR2,
34 P_ATTRIBUTE1 IN VARCHAR2,
35 P_ATTRIBUTE2 IN VARCHAR2,
36 P_ATTRIBUTE3 IN VARCHAR2,
37 P_ATTRIBUTE4 IN VARCHAR2,
38 P_ATTRIBUTE5 IN VARCHAR2,
39 P_ATTRIBUTE6 IN VARCHAR2,
40 P_ATTRIBUTE7 IN VARCHAR2,
41 P_ATTRIBUTE8 IN VARCHAR2,
42 P_ATTRIBUTE9 IN VARCHAR2,
43 P_ATTRIBUTE10 IN VARCHAR2,
44 P_ATTRIBUTE11 IN VARCHAR2,
45 P_ATTRIBUTE12 IN VARCHAR2,
46 P_ATTRIBUTE13 IN VARCHAR2,
47 P_ATTRIBUTE14 IN VARCHAR2,
48 P_ATTRIBUTE15 IN VARCHAR2
49 ) is
50
51 cursor C is
52 select ROWID from OZF_FUNDS_UTILIZED_ALL_B
53 where UTILIZATION_ID = P_UTILIZATION_ID;
54
55 begin
56 insert into OZF_FUNDS_UTILIZED_ALL_B (
57 UTILIZATION_ID,
58 LAST_UPDATE_DATE,
59 LAST_UPDATED_BY,
60 LAST_UPDATE_LOGIN,
61 CREATION_DATE,
62 CREATED_BY,
63 CREATED_FROM,
64 REQUEST_ID,
65 UTILIZATION_TYPE,
66 FUND_ID,
67 PLAN_TYPE,
68 PLAN_ID,
69 COMPONENT_TYPE,
70 COMPONENT_ID,
71 OBJECT_TYPE,
72 OBJECT_ID,
73 ORDER_ID,
74 INVOICE_ID,
75 AMOUNT,
76 ACCTD_AMOUNT,
77 CURRENCY_CODE,
78 EXCHANGE_RATE_TYPE,
79 EXCHANGE_RATE_DATE,
80 EXCHANGE_RATE,
81 ADJUSTMENT_TYPE,
82 ADJUSTMENT_DATE,
83 OBJECT_VERSION_NUMBER,
84 ATTRIBUTE_CATEGORY,
85 ATTRIBUTE1,
86 ATTRIBUTE2,
87 ATTRIBUTE3,
88 ATTRIBUTE4,
89 ATTRIBUTE5,
90 ATTRIBUTE6,
91 ATTRIBUTE7,
92 ATTRIBUTE8,
93 ATTRIBUTE9,
94 ATTRIBUTE10,
95 ATTRIBUTE11,
96 ATTRIBUTE12,
97 ATTRIBUTE13,
98 ATTRIBUTE14,
99 ATTRIBUTE15
100 ) values (
101 P_UTILIZATION_ID,
102 P_LAST_UPDATE_DATE,
103 P_LAST_UPDATED_BY,
104 P_LAST_UPDATE_LOGIN,
105 P_CREATION_DATE,
106 P_CREATED_BY,
107 P_CREATED_FROM,
108 P_REQUEST_ID,
109 P_UTILIZATION_TYPE,
110 P_FUND_ID,
111 P_PLAN_TYPE,
112 P_PLAN_ID,
113 P_COMPONENT_TYPE,
114 P_COMPONENT_ID,
115 P_OBJECT_TYPE,
116 P_OBJECT_ID,
117 P_ORDER_ID,
118 P_INVOICE_ID,
119 P_AMOUNT,
120 P_ACCTD_AMOUNT,
121 P_CURRENCY_CODE,
122 P_EXCHANGE_RATE_TYPE,
123 P_EXCHANGE_RATE_DATE,
124 P_EXCHANGE_RATE,
125 P_ADJUSTMENT_TYPE,
126 P_ADJUSTMENT_DATE,
127 P_OBJECT_VERSION_NUMBER,
128 P_ATTRIBUTE_CATEGORY,
129 P_ATTRIBUTE1,
130 P_ATTRIBUTE2,
131 P_ATTRIBUTE3,
132 P_ATTRIBUTE4,
133 P_ATTRIBUTE5,
134 P_ATTRIBUTE6,
135 P_ATTRIBUTE7,
136 P_ATTRIBUTE8,
137 P_ATTRIBUTE9,
138 P_ATTRIBUTE10,
139 P_ATTRIBUTE11,
140 P_ATTRIBUTE12,
141 P_ATTRIBUTE13,
142 P_ATTRIBUTE14,
143 P_ATTRIBUTE15
144 );
145
146 insert into OZF_FUNDS_UTILIZED_ALL_TL (
147 UTILIZATION_ID,
148 LAST_UPDATE_DATE,
149 LAST_UPDATED_BY,
150 LAST_UPDATE_LOGIN,
151 CREATION_DATE,
152 CREATED_BY,
153 CREATED_FROM,
154 REQUEST_ID,
155 ADJUSTMENT_DESC,
156 LANGUAGE,
157 SOURCE_LANG
158 ) select
159 P_UTILIZATION_ID,
160 P_LAST_UPDATE_DATE,
161 P_LAST_UPDATED_BY,
162 P_LAST_UPDATE_LOGIN,
163 P_CREATION_DATE,
164 P_CREATED_BY,
165 P_CREATED_FROM,
166 P_REQUEST_ID,
167 P_ADJUSTMENT_DESC,
168 L.LANGUAGE_CODE,
169 userenv('LANG')
170 from FND_LANGUAGES L
171 where L.INSTALLED_FLAG in ('I', 'B')
172 and not exists(
173 select NULL
174 from OZF_FUNDS_UTILIZED_ALL_TL T
175 where T.UTILIZATION_ID = P_UTILIZATION_ID
176 and T.LANGUAGE = L.LANGUAGE_CODE
177 );
178
179 open c;
180 fetch c into X_ROWID;
181 if (c%notfound) then
182 close c;
183 raise no_data_found;
184 end if;
185 close c;
186
187 end INSERT_ROW;
188
189
190 procedure LOCK_ROW (
191 P_UTILIZATION_ID IN NUMBER,
192 P_CREATED_FROM IN VARCHAR2,
193 P_REQUEST_ID IN NUMBER,
194 P_UTILIZATION_TYPE IN VARCHAR2,
195 P_FUND_ID IN NUMBER,
196 P_PLAN_TYPE IN VARCHAR2,
197 P_PLAN_ID IN NUMBER,
198 P_COMPONENT_TYPE IN VARCHAR2,
199 P_COMPONENT_ID IN NUMBER,
200 P_OBJECT_TYPE IN VARCHAR2,
201 P_OBJECT_ID IN NUMBER,
202 P_ORDER_ID IN NUMBER,
203 P_INVOICE_ID IN NUMBER,
204 P_AMOUNT IN NUMBER,
205 P_ACCTD_AMOUNT IN NUMBER,
206 P_CURRENCY_CODE IN VARCHAR2,
207 P_EXCHANGE_RATE_TYPE IN VARCHAR2,
208 P_EXCHANGE_RATE_DATE IN DATE,
209 P_EXCHANGE_RATE IN NUMBER,
210 P_ADJUSTMENT_TYPE IN VARCHAR2,
211 P_ADJUSTMENT_DATE IN DATE,
212 P_ADJUSTMENT_DESC IN VARCHAR2,
213 P_OBJECT_VERSION_NUMBER IN NUMBER,
214 P_ATTRIBUTE_CATEGORY IN VARCHAR2,
215 P_ATTRIBUTE1 IN VARCHAR2,
216 P_ATTRIBUTE2 IN VARCHAR2,
217 P_ATTRIBUTE3 IN VARCHAR2,
218 P_ATTRIBUTE4 IN VARCHAR2,
219 P_ATTRIBUTE5 IN VARCHAR2,
220 P_ATTRIBUTE6 IN VARCHAR2,
221 P_ATTRIBUTE7 IN VARCHAR2,
222 P_ATTRIBUTE8 IN VARCHAR2,
223 P_ATTRIBUTE9 IN VARCHAR2,
224 P_ATTRIBUTE10 IN VARCHAR2,
225 P_ATTRIBUTE11 IN VARCHAR2,
226 P_ATTRIBUTE12 IN VARCHAR2,
227 P_ATTRIBUTE13 IN VARCHAR2,
228 P_ATTRIBUTE14 IN VARCHAR2,
229 P_ATTRIBUTE15 IN VARCHAR2
230 ) is
231 cursor c is
232 select CREATED_FROM,
233 REQUEST_ID,
234 UTILIZATION_TYPE,
235 FUND_ID,
236 PLAN_TYPE,
237 PLAN_ID,
238 COMPONENT_TYPE,
239 COMPONENT_ID,
240 OBJECT_TYPE,
241 OBJECT_ID,
242 ORDER_ID,
243 INVOICE_ID,
244 AMOUNT,
245 ACCTD_AMOUNT,
246 CURRENCY_CODE,
247 EXCHANGE_RATE_TYPE,
248 EXCHANGE_RATE_DATE,
249 EXCHANGE_RATE,
250 ADJUSTMENT_TYPE,
251 ADJUSTMENT_DATE,
252 OBJECT_VERSION_NUMBER,
253 ATTRIBUTE_CATEGORY,
254 ATTRIBUTE1,
255 ATTRIBUTE2,
256 ATTRIBUTE3,
257 ATTRIBUTE4,
258 ATTRIBUTE5,
259 ATTRIBUTE6,
260 ATTRIBUTE7,
261 ATTRIBUTE8,
262 ATTRIBUTE9,
263 ATTRIBUTE10,
264 ATTRIBUTE11,
265 ATTRIBUTE12,
266 ATTRIBUTE13,
267 ATTRIBUTE14,
268 ATTRIBUTE15
269 from OZF_FUNDS_UTILIZED_ALL_B
270 where UTILIZATION_ID = P_UTILIZATION_ID
271 for update of UTILIZATION_ID nowait;
272
273 recinfo c%rowtype;
274
275 cursor c1 is
276 select ADJUSTMENT_DESC,
277 decode(LANGUAGE, userenv('LANG'), 'Y', 'N') BASELANG
278 from OZF_FUNDS_UTILIZED_ALL_TL
279 where UTILIZATION_ID = P_UTILIZATION_ID
280 and userenv('LANG') in (LANGUAGE, SOURCE_LANG)
281 for update of UTILIZATION_ID nowait;
282
283 begin
284 open c;
285 fetch c into recinfo;
286 if (c%notfound) then
287 close c;
288 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
289 app_exception.raise_exception;
290 end if;
291 close c;
292 if ( ((recinfo.CREATED_FROM = P_CREATED_FROM)
293 OR ((recinfo.CREATED_FROM is null) AND (P_CREATED_FROM is null)))
294 AND ((recinfo.REQUEST_ID = P_REQUEST_ID)
295 OR ((recinfo.REQUEST_ID is null) AND (P_REQUEST_ID is null)))
296 AND ((recinfo.UTILIZATION_TYPE = p_UTILIZATION_TYPE)
297 OR ((recinfo.UTILIZATION_TYPE is null) AND (P_UTILIZATION_TYPE is null)))
298 AND (recinfo.FUND_ID = P_FUND_ID)
299 AND ((recinfo.PLAN_TYPE = P_PLAN_TYPE)
300 OR ((recinfo.PLAN_TYPE is null) AND (P_PLAN_TYPE is null)))
301 AND ((recinfo.PLAN_ID = P_PLAN_ID)
302 OR ((recinfo.PLAN_ID is null) AND (P_PLAN_ID is null)))
303 AND ((recinfo.COMPONENT_TYPE = P_COMPONENT_TYPE)
304 OR ((recinfo.COMPONENT_TYPE is null) AND (P_COMPONENT_TYPE is null)))
305 AND ((recinfo.COMPONENT_ID = P_COMPONENT_ID)
306 OR ((recinfo.COMPONENT_ID is null) AND (P_COMPONENT_ID is null)))
307 AND ((recinfo.OBJECT_TYPE = P_OBJECT_TYPE)
308 OR ((recinfo.OBJECT_TYPE is null) AND (P_OBJECT_TYPE is null)))
309 AND ((recinfo.OBJECT_ID = P_OBJECT_ID)
310 OR ((recinfo.OBJECT_ID is null) AND (P_OBJECT_ID is null)))
311 AND ((recinfo.ORDER_ID = P_ORDER_ID)
312 OR ((recinfo.ORDER_ID is null) AND (P_ORDER_ID is null)))
313 AND ((recinfo.INVOICE_ID = P_INVOICE_ID)
314 OR ((recinfo.INVOICE_ID is null) AND (P_INVOICE_ID is null)))
315 AND (recinfo.AMOUNT = P_AMOUNT)
316 AND ((recinfo.ACCTD_AMOUNT = P_ACCTD_AMOUNT)
317 OR ((recinfo.ACCTD_AMOUNT is null) AND (P_ACCTD_AMOUNT is null)))
318 AND ((recinfo.CURRENCY_CODE = P_CURRENCY_CODE)
319 OR ((recinfo.CURRENCY_CODE is null) AND (P_CURRENCY_CODE is null)))
320 AND ((recinfo.EXCHANGE_RATE_TYPE = P_EXCHANGE_RATE_TYPE)
321 OR ((recinfo.EXCHANGE_RATE_TYPE is null) AND (P_EXCHANGE_RATE_TYPE is null)))
322 AND ((recinfo.EXCHANGE_RATE_DATE = P_EXCHANGE_RATE_DATE)
323 OR ((recinfo.EXCHANGE_RATE_DATE is null) AND (P_EXCHANGE_RATE_DATE is null)))
324 AND ((recinfo.EXCHANGE_RATE = P_EXCHANGE_RATE)
325 OR ((recinfo.EXCHANGE_RATE is null) AND (P_EXCHANGE_RATE is null)))
326 AND ((recinfo.ADJUSTMENT_TYPE = P_ADJUSTMENT_TYPE)
327 OR ((recinfo.ADJUSTMENT_TYPE is null) AND (P_ADJUSTMENT_TYPE is null)))
328 AND ((recinfo.ADJUSTMENT_DATE = P_ADJUSTMENT_DATE)
329 OR ((recinfo.ADJUSTMENT_DATE is null) AND (P_ADJUSTMENT_DATE is null)))
330 AND ((recinfo.OBJECT_VERSION_NUMBER = P_OBJECT_VERSION_NUMBER)
331 OR ((recinfo.OBJECT_VERSION_NUMBER is null) AND (P_OBJECT_VERSION_NUMBER is null)))
335 OR ((recinfo.ATTRIBUTE1 is null) AND (P_ATTRIBUTE1 is null)))
332 AND ((recinfo.ATTRIBUTE_CATEGORY = P_ATTRIBUTE_CATEGORY)
333 OR ((recinfo.ATTRIBUTE_CATEGORY is null) AND (P_ATTRIBUTE_CATEGORY is null)))
334 AND ((recinfo.ATTRIBUTE1 = P_ATTRIBUTE1)
336 AND ((recinfo.ATTRIBUTE2 = P_ATTRIBUTE2)
337 OR ((recinfo.ATTRIBUTE2 is null) AND (P_ATTRIBUTE2 is null)))
338 AND ((recinfo.ATTRIBUTE3 = P_ATTRIBUTE3)
339 OR ((recinfo.ATTRIBUTE3 is null) AND (P_ATTRIBUTE3 is null)))
340 AND ((recinfo.ATTRIBUTE4 = P_ATTRIBUTE4)
341 OR ((recinfo.ATTRIBUTE4 is null) AND (P_ATTRIBUTE4 is null)))
342 AND ((recinfo.ATTRIBUTE5 = P_ATTRIBUTE5)
343 OR ((recinfo.ATTRIBUTE5 is null) AND (P_ATTRIBUTE5 is null)))
344 AND ((recinfo.ATTRIBUTE6 = P_ATTRIBUTE6)
345 OR ((recinfo.ATTRIBUTE6 is null) AND (P_ATTRIBUTE6 is null)))
346 AND ((recinfo.ATTRIBUTE7 = P_ATTRIBUTE7)
347 OR ((recinfo.ATTRIBUTE7 is null) AND (P_ATTRIBUTE7 is null)))
348 AND ((recinfo.ATTRIBUTE8 = P_ATTRIBUTE8)
349 OR ((recinfo.ATTRIBUTE8 is null) AND (P_ATTRIBUTE8 is null)))
350 AND ((recinfo.ATTRIBUTE9 = P_ATTRIBUTE9)
351 OR ((recinfo.ATTRIBUTE9 is null) AND (P_ATTRIBUTE9 is null)))
352 AND ((recinfo.ATTRIBUTE10 = P_ATTRIBUTE10)
353 OR ((recinfo.ATTRIBUTE10 is null) AND (P_ATTRIBUTE10 is null)))
354 AND ((recinfo.ATTRIBUTE11 = P_ATTRIBUTE11)
355 OR ((recinfo.ATTRIBUTE11 is null) AND (P_ATTRIBUTE11 is null)))
356 AND ((recinfo.ATTRIBUTE12 = P_ATTRIBUTE12)
357 OR ((recinfo.ATTRIBUTE12 is null) AND (P_ATTRIBUTE12 is null)))
358 AND ((recinfo.ATTRIBUTE13 = P_ATTRIBUTE13)
359 OR ((recinfo.ATTRIBUTE13 is null) AND (P_ATTRIBUTE13 is null)))
360 AND ((recinfo.ATTRIBUTE14 = P_ATTRIBUTE14)
361 OR ((recinfo.ATTRIBUTE14 is null) AND (P_ATTRIBUTE14 is null)))
362 AND ((recinfo.ATTRIBUTE15 = P_ATTRIBUTE15)
363 OR ((recinfo.ATTRIBUTE15 is null) AND (P_ATTRIBUTE15 is null)))
364 ) then
365 null;
366 else
367 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
368 app_exception.raise_exception;
369 end if;
370
371 for tlinfo in c1 loop
372 if (tlinfo.BASELANG = 'Y') then
373 if ( (tlinfo.ADJUSTMENT_DESC = P_ADJUSTMENT_DESC)
374 OR ((tlinfo.ADJUSTMENT_DESC is null) AND (P_ADJUSTMENT_DESC is null))
375 ) then
376 null;
377 else
378 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
379 app_exception.raise_exception;
380 end if;
381 end if;
382 end loop;
383 return;
384
385 end LOCK_ROW;
386
387
388 procedure UPDATE_ROW (
389 P_UTILIZATION_ID IN NUMBER,
390 P_LAST_UPDATE_DATE IN DATE,
391 P_LAST_UPDATED_BY IN NUMBER,
392 P_LAST_UPDATE_LOGIN IN NUMBER,
393 P_CREATED_FROM IN VARCHAR2,
394 P_REQUEST_ID IN NUMBER,
395 P_UTILIZATION_TYPE IN VARCHAR2,
396 P_FUND_ID IN NUMBER,
397 P_PLAN_TYPE IN VARCHAR2,
398 P_PLAN_ID IN NUMBER,
399 P_COMPONENT_TYPE IN VARCHAR2,
403 P_ORDER_ID IN NUMBER,
400 P_COMPONENT_ID IN NUMBER,
401 P_OBJECT_TYPE IN VARCHAR2,
402 P_OBJECT_ID IN NUMBER,
404 P_INVOICE_ID IN NUMBER,
405 P_AMOUNT IN NUMBER,
406 P_ACCTD_AMOUNT IN NUMBER,
407 P_CURRENCY_CODE IN VARCHAR2,
408 P_EXCHANGE_RATE_TYPE IN VARCHAR2,
409 P_EXCHANGE_RATE_DATE IN DATE,
410 P_EXCHANGE_RATE IN NUMBER,
411 P_ADJUSTMENT_TYPE IN VARCHAR2,
412 P_ADJUSTMENT_DATE IN DATE,
413 P_ADJUSTMENT_DESC IN VARCHAR2,
414 P_OBJECT_VERSION_NUMBER IN NUMBER,
415 P_ATTRIBUTE_CATEGORY IN VARCHAR2,
416 P_ATTRIBUTE1 IN VARCHAR2,
417 P_ATTRIBUTE2 IN VARCHAR2,
418 P_ATTRIBUTE3 IN VARCHAR2,
419 P_ATTRIBUTE4 IN VARCHAR2,
420 P_ATTRIBUTE5 IN VARCHAR2,
421 P_ATTRIBUTE6 IN VARCHAR2,
422 P_ATTRIBUTE7 IN VARCHAR2,
423 P_ATTRIBUTE8 IN VARCHAR2,
424 P_ATTRIBUTE9 IN VARCHAR2,
425 P_ATTRIBUTE10 IN VARCHAR2,
426 P_ATTRIBUTE11 IN VARCHAR2,
427 P_ATTRIBUTE12 IN VARCHAR2,
428 P_ATTRIBUTE13 IN VARCHAR2,
429 P_ATTRIBUTE14 IN VARCHAR2,
430 P_ATTRIBUTE15 IN VARCHAR2
431 ) is
432
433 begin
434 update OZF_FUNDS_UTILIZED_ALL_B set
435 LAST_UPDATE_DATE = p_LAST_UPDATE_DATE,
436 LAST_UPDATED_BY = P_LAST_UPDATED_BY,
437 LAST_UPDATE_LOGIN = P_LAST_UPDATE_LOGIN,
438 CREATED_FROM = P_CREATED_FROM,
439 REQUEST_ID = P_REQUEST_ID,
440 UTILIZATION_TYPE = P_UTILIZATION_TYPE,
441 FUND_ID = P_FUND_ID,
442 PLAN_TYPE = P_PLAN_TYPE,
443 PLAN_ID = P_PLAN_ID,
444 COMPONENT_TYPE = P_COMPONENT_TYPE,
445 COMPONENT_ID = P_COMPONENT_ID,
446 OBJECT_TYPE = P_OBJECT_TYPE,
447 OBJECT_ID = P_OBJECT_ID,
448 ORDER_ID = P_ORDER_ID,
449 INVOICE_ID = P_INVOICE_ID,
450 AMOUNT = P_AMOUNT,
451 ACCTD_AMOUNT = P_ACCTD_AMOUNT,
452 CURRENCY_CODE = P_CURRENCY_CODE,
453 EXCHANGE_RATE_TYPE = P_EXCHANGE_RATE_TYPE,
454 EXCHANGE_RATE_DATE = P_EXCHANGE_RATE_DATE,
455 EXCHANGE_RATE = P_EXCHANGE_RATE,
456 ADJUSTMENT_TYPE = P_ADJUSTMENT_TYPE,
457 ADJUSTMENT_DATE = P_ADJUSTMENT_DATE,
458 OBJECT_VERSION_NUMBER = P_OBJECT_VERSION_NUMBER,
459 ATTRIBUTE_CATEGORY = P_ATTRIBUTE_CATEGORY,
460 ATTRIBUTE1 = P_ATTRIBUTE1,
461 ATTRIBUTE2 = P_ATTRIBUTE2,
462 ATTRIBUTE3 = P_ATTRIBUTE3,
463 ATTRIBUTE4 = P_ATTRIBUTE4,
464 ATTRIBUTE5 = P_ATTRIBUTE5,
465 ATTRIBUTE6 = P_ATTRIBUTE6,
466 ATTRIBUTE7 = P_ATTRIBUTE7,
467 ATTRIBUTE8 = P_ATTRIBUTE8,
468 ATTRIBUTE9 = P_ATTRIBUTE9,
469 ATTRIBUTE10 = P_ATTRIBUTE10,
470 ATTRIBUTE11 = P_ATTRIBUTE11,
471 ATTRIBUTE12 = P_ATTRIBUTE12,
472 ATTRIBUTE13 = P_ATTRIBUTE13,
473 ATTRIBUTE14 = P_ATTRIBUTE14,
474 ATTRIBUTE15 = P_ATTRIBUTE15
475 where UTILIZATION_ID = P_UTILIZATION_ID;
476
477 if (sql%notfound) then
478 raise no_data_found;
479 end if;
480
481 update OZF_FUNDS_UTILIZED_ALL_TL set
482 LAST_UPDATE_DATE = P_LAST_UPDATE_DATE,
483 LAST_UPDATED_BY = P_LAST_UPDATED_BY,
484 LAST_UPDATE_LOGIN = P_LAST_UPDATE_LOGIN,
485 ADJUSTMENT_DESC = P_ADJUSTMENT_DESC,
486 SOURCE_LANG = userenv('LANG')
487 where UTILIZATION_ID = P_UTILIZATION_ID
488 and userenv('LANG') in (LANGUAGE, SOURCE_LANG);
489
490 if (sql%notfound) then
491 raise no_data_found;
492 end if;
493
497 procedure DELETE_ROW (
494 end UPDATE_ROW;
495
496
498 P_UTILIZATION_ID IN NUMBER
499 ) is
500 begin
501 delete from OZF_FUNDS_UTILIZED_ALL_TL
502 where UTILIZATION_ID = P_UTILIZATION_ID;
503
504 if (sql%notfound) then
505 raise no_data_found;
506 end if;
507
508 delete from OZF_FUNDS_UTILIZED_ALL_B
509 where UTILIZATION_ID = P_UTILIZATION_ID;
510
511 if (sql%notfound) then
512 raise no_data_found;
513 end if;
514
515 end DELETE_ROW;
516
517
518 procedure ADD_LANGUAGE
519 is
520 begin
521 delete from OZF_FUNDS_UTILIZED_ALL_TL T
522 where not exists
523 (select NULL
524 from OZF_FUNDS_UTILIZED_ALL_B B
525 where B.UTILIZATION_ID = T.UTILIZATION_ID
526 );
527
528 update OZF_FUNDS_UTILIZED_ALL_TL T
529 set ADJUSTMENT_DESC = (select B.ADJUSTMENT_DESC
530 from OZF_FUNDS_UTILIZED_ALL_TL B
531 where B.UTILIZATION_ID = T.UTILIZATION_ID
532 and B.LANGUAGE = T.SOURCE_LANG)
533 where ( T.UTILIZATION_ID,
534 T.LANGUAGE )
535 in (select SUBT.UTILIZATION_ID,
536 SUBT.LANGUAGE
537 from OZF_FUNDS_UTILIZED_ALL_TL SUBB, OZF_FUNDS_UTILIZED_ALL_TL SUBT
538 where SUBB.UTILIZATION_ID = SUBT.UTILIZATION_ID
539 and SUBB.LANGUAGE = SUBT.SOURCE_LANG
540 and (SUBB.ADJUSTMENT_DESC <> SUBT.ADJUSTMENT_DESC
541 or (SUBB.ADJUSTMENT_DESC is null and SUBT.ADJUSTMENT_DESC is not null)
542 or (SUBB.ADJUSTMENT_DESC is not null and SUBT.ADJUSTMENT_DESC is null)));
543
544 insert into OZF_FUNDS_UTILIZED_ALL_TL (
545 UTILIZATION_ID,
546 LAST_UPDATE_DATE,
547 LAST_UPDATED_BY,
548 LAST_UPDATE_LOGIN,
549 CREATION_DATE,
550 CREATED_BY,
551 CREATED_FROM,
552 REQUEST_ID,
553 PROGRAM_APPLICATION_ID,
554 PROGRAM_ID,
555 PROGRAM_UPDATE_DATE,
556 ADJUSTMENT_DESC,
557 LANGUAGE,
558 SOURCE_LANG
559 ) select
560 B.UTILIZATION_ID,
561 B.LAST_UPDATE_DATE,
562 B.LAST_UPDATED_BY,
563 B.LAST_UPDATE_LOGIN,
564 B.CREATION_DATE,
565 B.CREATED_BY,
566 B.CREATED_FROM,
567 B.REQUEST_ID,
568 B.PROGRAM_APPLICATION_ID,
569 B.PROGRAM_ID,
570 B.PROGRAM_UPDATE_DATE,
571 B.ADJUSTMENT_DESC,
572 L.LANGUAGE_CODE,
573 B.SOURCE_LANG
574 from OZF_FUNDS_UTILIZED_ALL_TL B, FND_LANGUAGES L
575 where L.INSTALLED_FLAG in ('I', 'B')
576 and B.LANGUAGE = userenv('LANG')
577 and not exists
578 (select NULL
579 from OZF_FUNDS_UTILIZED_ALL_TL T
580 where T.UTILIZATION_ID = B.UTILIZATION_ID
581 and T.LANGUAGE = L.LANGUAGE_CODE);
582
583 end ADD_LANGUAGE;
584
585
586 procedure TRANSLATE_ROW(
587 P_UTILIZATION_ID IN NUMBER,
588 P_ADJUSTMENT_DESC IN VARCHAR2,
589 P_OWNERS IN VARCHAR2
590 )
591 is
592 begin
593 update OZF_FUNDS_UTILIZED_ALL_TL set
594 ADJUSTMENT_DESC = nvl(P_ADJUSTMENT_DESC, ADJUSTMENT_DESC),
595 SOURCE_LANG = userenv('LANG'),
596 LAST_UPDATE_DATE = sysdate,
597 LAST_UPDATED_BY = decode(P_OWNERS, 'SEED', 1, 0),
598 LAST_UPDATE_LOGIN = 0
599 where UTILIZATION_ID = P_UTILIZATION_ID
600 and userenv('LANG') in (LANGUAGE, SOURCE_LANG);
601
602 end TRANSLATE_ROW;
603
604
605 procedure LOAD_ROW(
606 P_UTILIZATION_ID IN NUMBER,
607 P_CREATED_FROM IN VARCHAR2,
608 P_REQUEST_ID IN NUMBER,
609 P_UTILIZATION_TYPE IN VARCHAR2,
610 P_FUND_ID IN NUMBER,
611 P_PLAN_TYPE IN VARCHAR2,
612 P_PLAN_ID IN NUMBER,
613 P_COMPONENT_TYPE IN VARCHAR2,
614 P_COMPONENT_ID IN NUMBER,
615 P_OBJECT_TYPE IN VARCHAR2,
616 P_OBJECT_ID IN NUMBER,
617 P_ORDER_ID IN NUMBER,
618 P_INVOICE_ID IN NUMBER,
619 P_AMOUNT IN NUMBER,
620 P_ACCTD_AMOUNT IN NUMBER,
621 P_CURRENCY_CODE IN VARCHAR2,
622 P_EXCHANGE_RATE_TYPE IN VARCHAR2,
623 P_EXCHANGE_RATE_DATE IN DATE,
624 P_EXCHANGE_RATE IN NUMBER,
625 P_ADJUSTMENT_TYPE IN VARCHAR2,
626 P_ADJUSTMENT_DATE IN DATE,
627 P_ADJUSTMENT_DESC IN VARCHAR2,
628 P_OBJECT_VERSION_NUMBER IN NUMBER,
629 P_ATTRIBUTE_CATEGORY IN VARCHAR2,
630 P_ATTRIBUTE1 IN VARCHAR2,
631 P_ATTRIBUTE2 IN VARCHAR2,
632 P_ATTRIBUTE3 IN VARCHAR2,
633 P_ATTRIBUTE4 IN VARCHAR2,
634 P_ATTRIBUTE5 IN VARCHAR2,
635 P_ATTRIBUTE6 IN VARCHAR2,
636 P_ATTRIBUTE7 IN VARCHAR2,
637 P_ATTRIBUTE8 IN VARCHAR2,
638 P_ATTRIBUTE9 IN VARCHAR2,
639 P_ATTRIBUTE10 IN VARCHAR2,
640 P_ATTRIBUTE11 IN VARCHAR2,
641 P_ATTRIBUTE12 IN VARCHAR2,
642 P_ATTRIBUTE13 IN VARCHAR2,
643 P_ATTRIBUTE14 IN VARCHAR2,
644 P_ATTRIBUTE15 IN VARCHAR2,
645 P_OWNERS IN VARCHAR2
646 )
647 is
648
649 l_user_id number := 0;
650 l_version number;
651 l_utilization_id number;
652 l_dummy_char varchar2(1);
653 l_row_id varchar2(100);
654
655 cursor c_version is
659
656 select OBJECT_VERSION_NUMBER
657 from OZF_FUNDS_UTILIZED_ALL_B
658 where UTILIZATION_ID = P_UTILIZATION_ID;
660 cursor c_utilization_exists is
661 select 'x'
662 from OZF_FUNDS_UTILIZED_ALL_B
663 where UTILIZATION_ID = P_UTILIZATION_ID;
664
665 cursor c_utilization_id is
666 select OZF_FUNDS_UTILIZED_S.nextval
667 from dual;
668
669 begin
670
671 if P_OWNERS = 'SEED' then
672 l_user_id := 1;
673 end if;
674
675 open c_utilization_exists;
676 fetch c_utilization_exists into l_dummy_char;
677
678 if c_utilization_exists%notfound then
679 close c_utilization_exists;
680 if P_UTILIZATION_ID is not null then
681 l_utilization_id := P_UTILIZATION_ID;
682 else
683 open c_utilization_id;
684 fetch c_utilization_id into l_utilization_id;
685 close c_utilization_id;
686 end if;
687 l_version := 1;
688 AMS_FUND_UTILIZED_ALL_PKG.INSERT_ROW(
689 X_ROWID => l_row_id,
690 P_UTILIZATION_ID => l_utilization_id,
691 P_LAST_UPDATE_DATE => SYSDATE,
692 P_LAST_UPDATED_BY => l_user_id,
693 P_LAST_UPDATE_LOGIN => 0,
694 P_CREATION_DATE => SYSDATE,
695 P_CREATED_BY => l_user_id,
696 P_CREATED_FROM => P_CREATED_FROM,
697 P_REQUEST_ID => P_REQUEST_ID,
698 P_UTILIZATION_TYPE => P_UTILIZATION_TYPE,
699 P_FUND_ID => P_FUND_ID,
700 P_PLAN_TYPE => P_PLAN_TYPE,
701 P_PLAN_ID => P_PLAN_ID,
702 P_COMPONENT_TYPE => P_COMPONENT_TYPE,
703 P_COMPONENT_ID => P_COMPONENT_ID,
704 P_OBJECT_TYPE => P_OBJECT_TYPE,
705 P_OBJECT_ID => P_OBJECT_ID,
706 P_ORDER_ID => P_ORDER_ID,
707 P_INVOICE_ID => P_INVOICE_ID,
708 P_AMOUNT => P_AMOUNT,
709 P_ACCTD_AMOUNT => P_ACCTD_AMOUNT,
710 P_CURRENCY_CODE => P_CURRENCY_CODE,
711 P_EXCHANGE_RATE_TYPE => P_EXCHANGE_RATE_TYPE,
712 P_EXCHANGE_RATE_DATE => P_EXCHANGE_RATE_DATE,
713 P_EXCHANGE_RATE => P_EXCHANGE_RATE,
714 P_ADJUSTMENT_TYPE => P_ADJUSTMENT_TYPE,
715 P_ADJUSTMENT_DATE => P_ADJUSTMENT_DATE,
716 P_ADJUSTMENT_DESC => P_ADJUSTMENT_DESC,
717 P_OBJECT_VERSION_NUMBER => l_version,
718 P_ATTRIBUTE_CATEGORY => P_ATTRIBUTE_CATEGORY,
719 P_ATTRIBUTE1 => P_ATTRIBUTE1,
720 P_ATTRIBUTE2 => P_ATTRIBUTE2,
721 P_ATTRIBUTE3 => P_ATTRIBUTE3,
722 P_ATTRIBUTE4 => P_ATTRIBUTE4,
723 P_ATTRIBUTE5 => P_ATTRIBUTE5,
724 P_ATTRIBUTE6 => P_ATTRIBUTE6,
725 P_ATTRIBUTE7 => P_ATTRIBUTE7,
726 P_ATTRIBUTE8 => P_ATTRIBUTE8,
727 P_ATTRIBUTE9 => P_ATTRIBUTE9,
728 P_ATTRIBUTE10 => P_ATTRIBUTE10,
729 P_ATTRIBUTE11 => P_ATTRIBUTE11,
730 P_ATTRIBUTE12 => P_ATTRIBUTE12,
731 P_ATTRIBUTE13 => P_ATTRIBUTE13,
732 P_ATTRIBUTE14 => P_ATTRIBUTE14,
733 P_ATTRIBUTE15 => P_ATTRIBUTE15
734 );
735 else
736 close c_utilization_exists;
737 open c_version;
738 fetch c_version into l_version;
739 close c_version;
740 AMS_FUND_UTILIZED_ALL_PKG.UPDATE_ROW(
741 P_UTILIZATION_ID => l_utilization_id,
742 P_LAST_UPDATE_DATE => SYSDATE,
743 P_LAST_UPDATED_BY => l_user_id,
744 P_LAST_UPDATE_LOGIN => 0,
745 P_CREATED_FROM => P_CREATED_FROM,
746 P_REQUEST_ID => P_REQUEST_ID,
747 P_UTILIZATION_TYPE => P_UTILIZATION_TYPE,
748 P_FUND_ID => P_FUND_ID,
749 P_PLAN_TYPE => P_PLAN_TYPE,
750 P_PLAN_ID => P_PLAN_ID,
751 P_COMPONENT_TYPE => P_COMPONENT_TYPE,
752 P_COMPONENT_ID => P_COMPONENT_ID,
753 P_OBJECT_TYPE => P_OBJECT_TYPE,
754 P_OBJECT_ID => P_OBJECT_ID,
755 P_ORDER_ID => P_ORDER_ID,
756 P_INVOICE_ID => P_INVOICE_ID,
757 P_AMOUNT => P_AMOUNT,
758 P_ACCTD_AMOUNT => P_ACCTD_AMOUNT,
759 P_CURRENCY_CODE => P_CURRENCY_CODE,
760 P_EXCHANGE_RATE_TYPE => P_EXCHANGE_RATE_TYPE,
761 P_EXCHANGE_RATE_DATE => P_EXCHANGE_RATE_DATE,
762 P_EXCHANGE_RATE => P_EXCHANGE_RATE,
763 P_ADJUSTMENT_TYPE => P_ADJUSTMENT_TYPE,
764 P_ADJUSTMENT_DATE => P_ADJUSTMENT_DATE,
765 P_ADJUSTMENT_DESC => P_ADJUSTMENT_DESC,
766 P_OBJECT_VERSION_NUMBER => l_version + 1,
767 P_ATTRIBUTE_CATEGORY => P_ATTRIBUTE_CATEGORY,
768 P_ATTRIBUTE1 => P_ATTRIBUTE1,
769 P_ATTRIBUTE2 => P_ATTRIBUTE2,
770 P_ATTRIBUTE3 => P_ATTRIBUTE3,
771 P_ATTRIBUTE4 => P_ATTRIBUTE4,
772 P_ATTRIBUTE5 => P_ATTRIBUTE5,
773 P_ATTRIBUTE6 => P_ATTRIBUTE6,
774 P_ATTRIBUTE7 => P_ATTRIBUTE7,
775 P_ATTRIBUTE8 => P_ATTRIBUTE8,
776 P_ATTRIBUTE9 => P_ATTRIBUTE9,
777 P_ATTRIBUTE10 => P_ATTRIBUTE10,
778 P_ATTRIBUTE11 => P_ATTRIBUTE11,
779 P_ATTRIBUTE12 => P_ATTRIBUTE12,
780 P_ATTRIBUTE13 => P_ATTRIBUTE13,
781 P_ATTRIBUTE14 => P_ATTRIBUTE14,
782 P_ATTRIBUTE15 => P_ATTRIBUTE15
783 );
784 end if;
785
786 end LOAD_ROW;
787
788
789 end AMS_FUND_UTILIZED_ALL_PKG;