[Home] [Help]
PACKAGE BODY: APPS.OZF_FUND_UTILIZED_ALL_PKG
Source
1 package body OZF_FUND_UTILIZED_ALL_PKG as
2 /* $Header: ozflfutb.pls 120.1 2008/03/28 06:27:03 bkunjan noship $ */
3 procedure INSERT_ROW (
4 X_ROWID IN OUT NOCOPY 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)))
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)
335 OR ((recinfo.ATTRIBUTE1 is null) AND (P_ATTRIBUTE1 is null)))
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,
398 P_PLAN_ID IN NUMBER,
395 P_UTILIZATION_TYPE IN VARCHAR2,
396 P_FUND_ID IN NUMBER,
397 P_PLAN_TYPE IN VARCHAR2,
399 P_COMPONENT_TYPE IN VARCHAR2,
400 P_COMPONENT_ID IN NUMBER,
401 P_OBJECT_TYPE IN VARCHAR2,
402 P_OBJECT_ID IN NUMBER,
403 P_ORDER_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
494 end UPDATE_ROW;
495
496
497 procedure DELETE_ROW (
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
540 and (SUBB.ADJUSTMENT_DESC <> SUBT.ADJUSTMENT_DESC
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
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 ORG_ID
560 ) select
561 B.UTILIZATION_ID,
562 B.LAST_UPDATE_DATE,
563 B.LAST_UPDATED_BY,
564 B.LAST_UPDATE_LOGIN,
565 B.CREATION_DATE,
566 B.CREATED_BY,
567 B.CREATED_FROM,
568 B.REQUEST_ID,
569 B.PROGRAM_APPLICATION_ID,
570 B.PROGRAM_ID,
571 B.PROGRAM_UPDATE_DATE,
572 B.ADJUSTMENT_DESC,
573 L.LANGUAGE_CODE,
574 B.SOURCE_LANG,
575 B.ORG_ID
576 from OZF_FUNDS_UTILIZED_ALL_TL B, FND_LANGUAGES L
577 where L.INSTALLED_FLAG in ('I', 'B')
578 and B.LANGUAGE = userenv('LANG')
579 and not exists
580 (select NULL
581 from OZF_FUNDS_UTILIZED_ALL_TL T
582 where T.UTILIZATION_ID = B.UTILIZATION_ID
583 and T.LANGUAGE = L.LANGUAGE_CODE);
584
585 end ADD_LANGUAGE;
586
587
588 procedure TRANSLATE_ROW(
589 P_UTILIZATION_ID IN NUMBER,
590 P_ADJUSTMENT_DESC IN VARCHAR2,
591 P_OWNERS IN VARCHAR2
592 )
593 is
594 begin
595 update OZF_FUNDS_UTILIZED_ALL_TL set
596 ADJUSTMENT_DESC = nvl(P_ADJUSTMENT_DESC, ADJUSTMENT_DESC),
597 SOURCE_LANG = userenv('LANG'),
598 LAST_UPDATE_DATE = sysdate,
599 LAST_UPDATED_BY = decode(P_OWNERS, 'SEED', 1, 0),
600 LAST_UPDATE_LOGIN = 0
601 where UTILIZATION_ID = P_UTILIZATION_ID
602 and userenv('LANG') in (LANGUAGE, SOURCE_LANG);
603
604 end TRANSLATE_ROW;
605
606
607 procedure LOAD_ROW(
608 P_UTILIZATION_ID IN NUMBER,
609 P_CREATED_FROM IN VARCHAR2,
610 P_REQUEST_ID IN NUMBER,
611 P_UTILIZATION_TYPE IN VARCHAR2,
612 P_FUND_ID IN NUMBER,
613 P_PLAN_TYPE IN VARCHAR2,
614 P_PLAN_ID IN NUMBER,
615 P_COMPONENT_TYPE IN VARCHAR2,
616 P_COMPONENT_ID IN NUMBER,
617 P_OBJECT_TYPE IN VARCHAR2,
618 P_OBJECT_ID IN NUMBER,
619 P_ORDER_ID IN NUMBER,
620 P_INVOICE_ID IN NUMBER,
621 P_AMOUNT IN NUMBER,
622 P_ACCTD_AMOUNT IN NUMBER,
623 P_CURRENCY_CODE IN VARCHAR2,
624 P_EXCHANGE_RATE_TYPE IN VARCHAR2,
625 P_EXCHANGE_RATE_DATE IN DATE,
626 P_EXCHANGE_RATE IN NUMBER,
627 P_ADJUSTMENT_TYPE IN VARCHAR2,
628 P_ADJUSTMENT_DATE IN DATE,
629 P_ADJUSTMENT_DESC IN VARCHAR2,
630 P_OBJECT_VERSION_NUMBER IN NUMBER,
631 P_ATTRIBUTE_CATEGORY IN VARCHAR2,
632 P_ATTRIBUTE1 IN VARCHAR2,
633 P_ATTRIBUTE2 IN VARCHAR2,
634 P_ATTRIBUTE3 IN VARCHAR2,
635 P_ATTRIBUTE4 IN VARCHAR2,
636 P_ATTRIBUTE5 IN VARCHAR2,
637 P_ATTRIBUTE6 IN VARCHAR2,
638 P_ATTRIBUTE7 IN VARCHAR2,
639 P_ATTRIBUTE8 IN VARCHAR2,
640 P_ATTRIBUTE9 IN VARCHAR2,
641 P_ATTRIBUTE10 IN VARCHAR2,
642 P_ATTRIBUTE11 IN VARCHAR2,
643 P_ATTRIBUTE12 IN VARCHAR2,
644 P_ATTRIBUTE13 IN VARCHAR2,
645 P_ATTRIBUTE14 IN VARCHAR2,
646 P_ATTRIBUTE15 IN VARCHAR2,
647 P_OWNERS IN VARCHAR2
648 )
649 is
650
651 l_user_id number := 0;
652 l_version number;
653 l_utilization_id number;
654 l_dummy_char varchar2(1);
655 l_row_id varchar2(100);
656
657 cursor c_version is
658 select OBJECT_VERSION_NUMBER
659 from OZF_FUNDS_UTILIZED_ALL_B
660 where UTILIZATION_ID = P_UTILIZATION_ID;
661
662 cursor c_utilization_exists is
663 select 'x'
664 from OZF_FUNDS_UTILIZED_ALL_B
665 where UTILIZATION_ID = P_UTILIZATION_ID;
666
667 cursor c_utilization_id is
668 select OZF_FUNDS_UTILIZED_S.nextval
669 from dual;
670
671 begin
672
673 if P_OWNERS = 'SEED' then
674 l_user_id := 1;
675 end if;
676
677 open c_utilization_exists;
678 fetch c_utilization_exists into l_dummy_char;
679
680 if c_utilization_exists%notfound then
681 close c_utilization_exists;
682 if P_UTILIZATION_ID is not null then
683 l_utilization_id := P_UTILIZATION_ID;
687 close c_utilization_id;
684 else
685 open c_utilization_id;
686 fetch c_utilization_id into l_utilization_id;
688 end if;
689 l_version := 1;
690 OZF_FUND_UTILIZED_ALL_PKG.INSERT_ROW(
691 X_ROWID => l_row_id,
692 P_UTILIZATION_ID => l_utilization_id,
693 P_LAST_UPDATE_DATE => SYSDATE,
694 P_LAST_UPDATED_BY => l_user_id,
695 P_LAST_UPDATE_LOGIN => 0,
696 P_CREATION_DATE => SYSDATE,
697 P_CREATED_BY => l_user_id,
698 P_CREATED_FROM => P_CREATED_FROM,
699 P_REQUEST_ID => P_REQUEST_ID,
700 P_UTILIZATION_TYPE => P_UTILIZATION_TYPE,
701 P_FUND_ID => P_FUND_ID,
702 P_PLAN_TYPE => P_PLAN_TYPE,
703 P_PLAN_ID => P_PLAN_ID,
704 P_COMPONENT_TYPE => P_COMPONENT_TYPE,
705 P_COMPONENT_ID => P_COMPONENT_ID,
706 P_OBJECT_TYPE => P_OBJECT_TYPE,
707 P_OBJECT_ID => P_OBJECT_ID,
708 P_ORDER_ID => P_ORDER_ID,
709 P_INVOICE_ID => P_INVOICE_ID,
710 P_AMOUNT => P_AMOUNT,
711 P_ACCTD_AMOUNT => P_ACCTD_AMOUNT,
712 P_CURRENCY_CODE => P_CURRENCY_CODE,
713 P_EXCHANGE_RATE_TYPE => P_EXCHANGE_RATE_TYPE,
714 P_EXCHANGE_RATE_DATE => P_EXCHANGE_RATE_DATE,
715 P_EXCHANGE_RATE => P_EXCHANGE_RATE,
716 P_ADJUSTMENT_TYPE => P_ADJUSTMENT_TYPE,
717 P_ADJUSTMENT_DATE => P_ADJUSTMENT_DATE,
718 P_ADJUSTMENT_DESC => P_ADJUSTMENT_DESC,
719 P_OBJECT_VERSION_NUMBER => l_version,
720 P_ATTRIBUTE_CATEGORY => P_ATTRIBUTE_CATEGORY,
721 P_ATTRIBUTE1 => P_ATTRIBUTE1,
722 P_ATTRIBUTE2 => P_ATTRIBUTE2,
723 P_ATTRIBUTE3 => P_ATTRIBUTE3,
724 P_ATTRIBUTE4 => P_ATTRIBUTE4,
725 P_ATTRIBUTE5 => P_ATTRIBUTE5,
726 P_ATTRIBUTE6 => P_ATTRIBUTE6,
727 P_ATTRIBUTE7 => P_ATTRIBUTE7,
728 P_ATTRIBUTE8 => P_ATTRIBUTE8,
729 P_ATTRIBUTE9 => P_ATTRIBUTE9,
730 P_ATTRIBUTE10 => P_ATTRIBUTE10,
731 P_ATTRIBUTE11 => P_ATTRIBUTE11,
732 P_ATTRIBUTE12 => P_ATTRIBUTE12,
733 P_ATTRIBUTE13 => P_ATTRIBUTE13,
734 P_ATTRIBUTE14 => P_ATTRIBUTE14,
735 P_ATTRIBUTE15 => P_ATTRIBUTE15
736 );
737 else
738 close c_utilization_exists;
739 open c_version;
740 fetch c_version into l_version;
741 close c_version;
742 OZF_FUND_UTILIZED_ALL_PKG.UPDATE_ROW(
743 P_UTILIZATION_ID => l_utilization_id,
744 P_LAST_UPDATE_DATE => SYSDATE,
745 P_LAST_UPDATED_BY => l_user_id,
746 P_LAST_UPDATE_LOGIN => 0,
747 P_CREATED_FROM => P_CREATED_FROM,
748 P_REQUEST_ID => P_REQUEST_ID,
749 P_UTILIZATION_TYPE => P_UTILIZATION_TYPE,
750 P_FUND_ID => P_FUND_ID,
751 P_PLAN_TYPE => P_PLAN_TYPE,
752 P_PLAN_ID => P_PLAN_ID,
753 P_COMPONENT_TYPE => P_COMPONENT_TYPE,
754 P_COMPONENT_ID => P_COMPONENT_ID,
755 P_OBJECT_TYPE => P_OBJECT_TYPE,
756 P_OBJECT_ID => P_OBJECT_ID,
757 P_ORDER_ID => P_ORDER_ID,
758 P_INVOICE_ID => P_INVOICE_ID,
759 P_AMOUNT => P_AMOUNT,
760 P_ACCTD_AMOUNT => P_ACCTD_AMOUNT,
761 P_CURRENCY_CODE => P_CURRENCY_CODE,
762 P_EXCHANGE_RATE_TYPE => P_EXCHANGE_RATE_TYPE,
763 P_EXCHANGE_RATE_DATE => P_EXCHANGE_RATE_DATE,
764 P_EXCHANGE_RATE => P_EXCHANGE_RATE,
765 P_ADJUSTMENT_TYPE => P_ADJUSTMENT_TYPE,
766 P_ADJUSTMENT_DATE => P_ADJUSTMENT_DATE,
767 P_ADJUSTMENT_DESC => P_ADJUSTMENT_DESC,
768 P_OBJECT_VERSION_NUMBER => l_version + 1,
769 P_ATTRIBUTE_CATEGORY => P_ATTRIBUTE_CATEGORY,
770 P_ATTRIBUTE1 => P_ATTRIBUTE1,
771 P_ATTRIBUTE2 => P_ATTRIBUTE2,
772 P_ATTRIBUTE3 => P_ATTRIBUTE3,
773 P_ATTRIBUTE4 => P_ATTRIBUTE4,
774 P_ATTRIBUTE5 => P_ATTRIBUTE5,
775 P_ATTRIBUTE6 => P_ATTRIBUTE6,
776 P_ATTRIBUTE7 => P_ATTRIBUTE7,
777 P_ATTRIBUTE8 => P_ATTRIBUTE8,
778 P_ATTRIBUTE9 => P_ATTRIBUTE9,
779 P_ATTRIBUTE10 => P_ATTRIBUTE10,
780 P_ATTRIBUTE11 => P_ATTRIBUTE11,
781 P_ATTRIBUTE12 => P_ATTRIBUTE12,
782 P_ATTRIBUTE13 => P_ATTRIBUTE13,
783 P_ATTRIBUTE14 => P_ATTRIBUTE14,
784 P_ATTRIBUTE15 => P_ATTRIBUTE15
785 );
786 end if;
787
788 end LOAD_ROW;
789
790
791 end OZF_FUND_UTILIZED_ALL_PKG;