[Home] [Help]
PACKAGE BODY: APPS.OKE_FUNDINGSOURCE_PVT
Source
1 package body OKE_FundingSource_PVT as
2 /* $Header: OKEVFDSB.pls 115.11 2003/10/07 00:48:42 alaw ship $ */
3
4
5 PROCEDURE Insert_Row(X_Rowid IN OUT NOCOPY VARCHAR2,
6 X_Funding_Source_Id NUMBER,
7 X_Pool_Party_Id NUMBER,
8 X_K_Party_Id NUMBER,
9 X_Object_Type VARCHAR2,
10 X_Object_Id NUMBER,
11 X_Agreement_Number VARCHAR2,
12 X_Currency_Code VARCHAR2,
13 X_Amount NUMBER,
14 X_Initial_Amount NUMBER,
15 X_Previous_Amount NUMBER,
16 X_Funding_Status VARCHAR2,
17 X_Hard_Limit NUMBER,
18 X_Revenue_Hard_Limit NUMBER,
19 X_Agreement_Org_Id NUMBER,
20 X_K_Conversion_Type VARCHAR2,
21 X_K_Conversion_Date DATE,
22 X_K_Conversion_Rate NUMBER,
23 X_Start_Date_Active DATE,
24 X_End_Date_Active DATE,
25 X_Last_Update_Date DATE,
26 X_Last_Updated_By NUMBER,
27 X_Creation_Date DATE,
28 X_Created_By NUMBER,
29 X_Last_Update_Login NUMBER,
30 X_Attribute_Category VARCHAR2,
31 X_Attribute1 VARCHAR2,
32 X_Attribute2 VARCHAR2,
33 X_Attribute3 VARCHAR2,
34 X_Attribute4 VARCHAR2,
35 X_Attribute5 VARCHAR2,
36 X_Attribute6 VARCHAR2,
37 X_Attribute7 VARCHAR2,
38 X_Attribute8 VARCHAR2,
39 X_Attribute9 VARCHAR2,
40 X_Attribute10 VARCHAR2,
41 X_Attribute11 VARCHAR2,
42 X_Attribute12 VARCHAR2,
43 X_Attribute13 VARCHAR2,
44 X_Attribute14 VARCHAR2,
45 X_Attribute15 VARCHAR2,
46 X_PA_Attribute_Category VARCHAR2,
47 X_PA_Attribute1 VARCHAR2,
48 X_PA_Attribute2 VARCHAR2,
49 X_PA_Attribute3 VARCHAR2,
50 X_PA_Attribute4 VARCHAR2,
51 X_PA_Attribute5 VARCHAR2,
52 X_PA_Attribute6 VARCHAR2,
53 X_PA_Attribute7 VARCHAR2,
54 X_PA_Attribute8 VARCHAR2,
55 X_PA_Attribute9 VARCHAR2,
56 X_PA_Attribute10 VARCHAR2
57 ) is
58
59 cursor C is
60 select rowid
61 from OKE_K_FUNDING_SOURCES
62 where funding_source_id = X_Funding_Source_Id;
63
64 begin
65 --oke_debug.debug('entering funding source insert_row');
66 insert into OKE_K_FUNDING_SOURCES(
67 funding_source_id,
68 pool_party_id,
69 k_party_id,
70 object_type,
71 object_id,
72 agreement_number,
73 amount,
74 initial_amount,
75 previous_amount,
76 funding_status,
77 hard_limit,
78 revenue_hard_limit,
79 agreement_org_id,
80 currency_code,
81 k_conversion_type,
82 k_conversion_date,
83 k_conversion_rate,
84 start_date_active,
85 end_date_active,
86 creation_date,
87 created_by,
88 last_updated_by,
89 last_update_date,
90 last_update_login,
91 attribute_category,
92 attribute1,
93 attribute2,
94 attribute3,
95 attribute4,
96 attribute5,
97 attribute6,
98 attribute7,
99 attribute8,
100 attribute9,
101 attribute10,
102 attribute11,
103 attribute12,
104 attribute13,
105 attribute14,
106 attribute15,
107 pa_attribute_category,
108 pa_attribute1,
109 pa_attribute2,
110 pa_attribute3,
111 pa_attribute4,
112 pa_attribute5,
113 pa_attribute6,
114 pa_attribute7,
115 pa_attribute8,
116 pa_attribute9,
117 pa_attribute10
118 ) VALUES (
119 X_Funding_Source_Id,
120 X_Pool_Party_Id,
121 X_K_Party_Id,
122 X_Object_Type,
123 X_Object_Id,
124 X_Agreement_Number,
125 X_Amount,
126 X_Initial_Amount,
127 X_Previous_Amount,
128 X_Funding_Status,
129 X_Hard_Limit,
130 X_Revenue_Hard_Limit,
131 X_Agreement_Org_ID,
132 X_Currency_Code,
133 X_K_Conversion_Type,
134 X_K_Conversion_Date,
135 X_K_Conversion_Rate,
136 X_Start_Date_Active,
137 X_End_Date_Active,
138 X_Creation_Date,
139 X_Created_By,
140 X_Last_Updated_By,
141 X_Last_Update_Date,
142 X_Last_Update_Login,
143 X_Attribute_Category,
144 X_Attribute1,
145 X_Attribute2,
146 X_Attribute3,
147 X_Attribute4,
148 X_Attribute5,
149 X_Attribute6,
150 X_Attribute7,
151 X_Attribute8,
152 X_Attribute9,
153 X_Attribute10,
154 X_Attribute11,
155 X_Attribute12,
156 X_Attribute13,
157 X_Attribute14,
158 X_Attribute15,
159 X_PA_Attribute_Category,
160 X_PA_Attribute1,
161 X_PA_Attribute2,
162 X_PA_Attribute3,
163 X_PA_Attribute4,
164 X_PA_Attribute5,
165 X_PA_Attribute6,
166 X_PA_Attribute7,
167 X_PA_Attribute8,
168 X_PA_Attribute9,
169 X_PA_Attribute10
170 );
171
172 open c;
173 fetch c into X_Rowid;
174 if (c%notfound) then
175 close c;
176 raise no_data_found;
177 end if;
178 close c;
179 --oke_debug.debug('finishing insert_row');
180 if (x_pool_party_id is not null) then
181 --oke_debug.debug('inside the update oke_pool_parties');
182
183 update oke_pool_parties
184 set available_amount = (available_amount - X_amount)
185 where pool_party_id = X_Pool_Party_Id;
186
187 if (sql%notfound) then
188 --oke_debug.debug('encounter no date found for update pool party');
189 raise no_data_found;
190 end if;
191
192 end if;
193
194 end Insert_Row;
195
196 PROCEDURE Lock_Row(X_Funding_Source_Id NUMBER,
197 X_Pool_Party_Id NUMBER,
198 X_K_Party_Id NUMBER,
199 X_Object_Type VARCHAR2,
200 X_Object_Id NUMBER,
201 X_Agreement_Number VARCHAR2,
202 X_Currency_Code VARCHAR2,
203 X_Amount NUMBER,
204 X_Initial_Amount NUMBER,
205 X_Previous_Amount NUMBER,
206 X_Funding_Status VARCHAR2,
207 X_Hard_Limit NUMBER,
208 X_Revenue_Hard_Limit NUMBER,
209 X_Agreement_Org_Id NUMBER,
210 X_K_Conversion_Type VARCHAR2,
211 X_K_Conversion_Date DATE,
212 X_K_Conversion_Rate NUMBER,
213 X_Start_Date_Active DATE,
214 X_End_Date_Active DATE,
215 X_Attribute_Category VARCHAR2,
216 X_Attribute1 VARCHAR2,
217 X_Attribute2 VARCHAR2,
218 X_Attribute3 VARCHAR2,
219 X_Attribute4 VARCHAR2,
220 X_Attribute5 VARCHAR2,
221 X_Attribute6 VARCHAR2,
222 X_Attribute7 VARCHAR2,
223 X_Attribute8 VARCHAR2,
224 X_Attribute9 VARCHAR2,
225 X_Attribute10 VARCHAR2,
226 X_Attribute11 VARCHAR2,
227 X_Attribute12 VARCHAR2,
228 X_Attribute13 VARCHAR2,
229 X_Attribute14 VARCHAR2,
230 X_Attribute15 VARCHAR2,
231 X_PA_Attribute_Category VARCHAR2,
232 X_PA_Attribute1 VARCHAR2,
233 X_PA_Attribute2 VARCHAR2,
234 X_PA_Attribute3 VARCHAR2,
235 X_PA_Attribute4 VARCHAR2,
236 X_PA_Attribute5 VARCHAR2,
237 X_PA_Attribute6 VARCHAR2,
238 X_PA_Attribute7 VARCHAR2,
239 X_PA_Attribute8 VARCHAR2,
240 X_PA_Attribute9 VARCHAR2,
241 X_PA_Attribute10 VARCHAR2
242 ) is
243
244 cursor c is
245 select funding_source_id,
246 pool_party_id,
247 k_party_id,
248 object_type,
249 object_id,
250 agreement_number,
251 currency_code,
252 amount,
253 initial_amount,
254 previous_amount,
255 funding_status,
256 hard_limit,
257 revenue_hard_limit,
258 agreement_org_id,
259 k_conversion_type,
260 k_conversion_rate,
261 k_conversion_date,
262 start_date_active,
263 end_date_active,
264 attribute_category,
265 attribute1,
266 attribute2,
267 attribute3,
268 attribute4,
269 attribute5,
270 attribute6,
271 attribute7,
272 attribute8,
273 attribute9,
274 attribute10,
275 attribute11,
276 attribute12,
277 attribute13,
278 attribute14,
279 attribute15,
280 pa_attribute_category,
281 pa_attribute1,
282 pa_attribute2,
283 pa_attribute3,
284 pa_attribute4,
285 pa_attribute5,
286 pa_attribute6,
287 pa_attribute7,
288 pa_attribute8,
289 pa_attribute9,
290 pa_attribute10
291 from OKE_K_FUNDING_SOURCES
292 where funding_source_id = X_Funding_Source_Id
293 for update of funding_source_id nowait;
294
295 recinfo c%rowtype;
296
297 begin
298
299 open c;
300 fetch c into recinfo;
301 if (c%notfound) then
302 close c;
303 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
304 app_exception.raise_exception;
305 end if;
306 close c;
307
308 if ( ((recinfo.hard_limit = X_Hard_Limit)
309 OR ((recinfo.hard_limit is null) AND (X_Hard_Limit is null)))
310 AND ((recinfo.revenue_hard_limit = X_Revenue_Hard_Limit)
311 OR ((recinfo.revenue_hard_limit is null) AND (X_Revenue_Hard_Limit is null)))
312 AND ((recinfo.agreement_org_id = X_Agreement_Org_ID)
313 OR ((recinfo.agreement_org_id is null) AND (X_Agreement_Org_ID is null)))
314 AND ((rtrim(recinfo.k_conversion_type) = rtrim(X_K_Conversion_Type))
315 OR ((recinfo.k_conversion_type is null) AND (X_K_Conversion_Type is null)))
316 AND ((rtrim(recinfo.k_conversion_date) = rtrim(X_K_Conversion_Date))
317 OR ((recinfo.k_conversion_date is null) AND (X_K_Conversion_Date is null)))
318 AND ((recinfo.k_conversion_rate = X_K_Conversion_Rate)
319 OR ((recinfo.k_conversion_rate is null) AND (X_K_Conversion_Rate is null)))
320 AND ((rtrim(recinfo.end_date_active) = rtrim(X_End_Date_Active))
321 OR ((recinfo.end_date_active is null) AND (X_End_Date_Active is null)))
322 AND ((rtrim(recinfo.start_date_active) = rtrim(X_Start_Date_Active))
323 OR ((recinfo.start_date_active is null) AND (X_Start_Date_Active is null)))
324 AND ((rtrim(recinfo.funding_status) = rtrim(X_Funding_Status))
325 OR ((recinfo.funding_status is null) AND (X_Funding_Status is null)))
326 AND ((recinfo.pool_party_id = X_Pool_Party_Id)
327 OR ((recinfo.pool_party_id is null) AND (X_Pool_Party_Id is null)))
328 AND (recinfo.initial_amount = X_Initial_Amount)
329 AND (recinfo.previous_amount = X_Previous_Amount)
330 AND (rtrim(recinfo.object_type) = rtrim(X_Object_Type))
331 AND (rtrim(recinfo.currency_code) = rtrim(X_Currency_Code))
332 AND (recinfo.funding_source_id = X_Funding_Source_Id)
333 AND (recinfo.object_id = X_Object_Id)
334 AND (recinfo.amount = X_Amount)
335 AND (recinfo.k_party_id = X_K_Party_Id)
336 AND (recinfo.funding_source_id = X_Funding_Source_Id)
337 AND ((rtrim(recinfo.attribute_category) = rtrim(X_Attribute_Category))
338 OR ((recinfo.attribute_category is null) AND (X_Attribute_Category is null)))
339 AND ((rtrim(recinfo.attribute1) = rtrim(X_Attribute1))
340 OR ((recinfo.attribute1 is null) AND (X_Attribute1 is null)))
341 AND ((rtrim(recinfo.attribute2) = rtrim(X_Attribute2))
342 OR ((recinfo.attribute2 is null) AND (X_Attribute2 is null)))
343 AND ((rtrim(recinfo.attribute3) = rtrim(X_Attribute3))
344 OR ((recinfo.attribute3 is null) AND (X_Attribute3 is null)))
345 AND ((rtrim(recinfo.attribute4) = rtrim(X_Attribute4))
346 OR ((recinfo.attribute4 is null) AND (X_Attribute4 is null)))
347 AND ((rtrim(recinfo.attribute5) = rtrim(X_Attribute5))
348 OR ((recinfo.attribute5 is null) AND (X_Attribute5 is null)))
349 AND ((rtrim(recinfo.attribute6) = rtrim(X_Attribute6))
350 OR ((recinfo.attribute6 is null) AND (X_Attribute6 is null)))
351 AND ((rtrim(recinfo.attribute7) = rtrim(X_Attribute7))
352 OR ((recinfo.attribute7 is null) AND (X_Attribute7 is null)))
353 AND ((rtrim(recinfo.attribute8) = rtrim(X_Attribute8))
354 OR ((recinfo.attribute8 is null) AND (X_Attribute8 is null)))
355 AND ((rtrim(recinfo.attribute9) = rtrim(X_Attribute9))
356 OR ((recinfo.attribute9 is null) AND (X_Attribute9 is null)))
357 AND ((rtrim(recinfo.attribute10) = rtrim(X_Attribute10))
358 OR ((recinfo.attribute10 is null) AND (X_Attribute10 is null)))
359 AND ((rtrim(recinfo.attribute11) = rtrim(X_Attribute11))
360 OR ((recinfo.attribute11 is null) AND (X_Attribute11 is null)))
361 AND ((rtrim(recinfo.attribute12) = rtrim(X_Attribute12))
362 OR ((recinfo.attribute12 is null) AND (X_Attribute12 is null)))
363 AND ((rtrim(recinfo.attribute13) = rtrim(X_Attribute13))
364 OR ((recinfo.attribute13 is null) AND (X_Attribute13 is null)))
368 OR ((recinfo.attribute15 is null) AND (X_Attribute15 is null)))
365 AND ((rtrim(recinfo.attribute14) = rtrim(X_Attribute14))
366 OR ((recinfo.attribute14 is null) AND (X_Attribute14 is null)))
367 AND ((rtrim(recinfo.attribute15) = rtrim(X_Attribute15))
369 AND ((rtrim(recinfo.pa_attribute_category) = rtrim(X_PA_Attribute_Category))
370 OR ((recinfo.pa_attribute_category is null) AND (X_PA_Attribute_Category is null)))
371 AND ((rtrim(recinfo.pa_attribute1) = rtrim(X_PA_Attribute1))
372 OR ((recinfo.pa_attribute1 is null) AND (X_PA_Attribute1 is null)))
373 AND ((rtrim(recinfo.pa_attribute2) = rtrim(X_PA_Attribute2))
374 OR ((recinfo.pa_attribute2 is null) AND (X_PA_Attribute2 is null)))
375 AND ((rtrim(recinfo.pa_attribute3) = rtrim(X_PA_Attribute3))
376 OR ((recinfo.pa_attribute3 is null) AND (X_PA_Attribute3 is null)))
377 AND ((rtrim(recinfo.pa_attribute4) = rtrim(X_PA_Attribute4))
378 OR ((recinfo.pa_attribute4 is null) AND (X_PA_Attribute4 is null)))
379 AND ((rtrim(recinfo.pa_attribute5) = rtrim(X_PA_Attribute5))
380 OR ((recinfo.pa_attribute5 is null) AND (X_PA_Attribute5 is null)))
381 AND ((rtrim(recinfo.pa_attribute6) = rtrim(X_PA_Attribute6))
382 OR ((recinfo.pa_attribute6 is null) AND (X_PA_Attribute6 is null)))
383 AND ((rtrim(recinfo.pa_attribute7) = rtrim(X_PA_Attribute7))
384 OR ((recinfo.pa_attribute7 is null) AND (X_PA_Attribute7 is null)))
385 AND ((rtrim(recinfo.pa_attribute8) = rtrim(X_PA_Attribute8))
386 OR ((recinfo.pa_attribute8 is null) AND (X_PA_Attribute8 is null)))
387 AND ((rtrim(recinfo.pa_attribute9) = rtrim(X_PA_Attribute9))
388 OR ((recinfo.pa_attribute9 is null) AND (X_PA_Attribute9 is null)))
389 AND ((rtrim(recinfo.pa_attribute10) = rtrim(X_PA_Attribute10))
390 OR ((recinfo.pa_attribute10 is null) AND (X_PA_Attribute10 is null)))
391 ) then
392 null;
393 else
394 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
395 app_exception.raise_exception;
396 end if;
397
398 end Lock_Row;
399
400 PROCEDURE Update_Row(X_Funding_Source_Id NUMBER,
401 X_Pool_Party_Id NUMBER,
402 X_K_Party_Id NUMBER,
403 X_Amount NUMBER,
404 X_Previous_Amount NUMBER,
405 X_Funding_Status VARCHAR2,
406 X_Agreement_Number VARCHAR2,
407 X_Hard_Limit NUMBER,
408 X_Revenue_Hard_Limit NUMBER,
409 X_Agreement_Org_ID NUMBER,
410 X_K_Conversion_Type VARCHAR2,
411 X_K_Conversion_Date DATE,
412 X_K_Conversion_Rate NUMBER,
413 X_Start_Date_Active DATE,
414 X_End_Date_Active DATE,
415 X_Last_Update_Date DATE,
416 X_Last_Updated_By NUMBER,
417 X_Last_Update_Login NUMBER,
418 X_Attribute_Category VARCHAR2,
419 X_Attribute1 VARCHAR2,
420 X_Attribute2 VARCHAR2,
421 X_Attribute3 VARCHAR2,
422 X_Attribute4 VARCHAR2,
423 X_Attribute5 VARCHAR2,
424 X_Attribute6 VARCHAR2,
425 X_Attribute7 VARCHAR2,
426 X_Attribute8 VARCHAR2,
427 X_Attribute9 VARCHAR2,
428 X_Attribute10 VARCHAR2,
429 X_Attribute11 VARCHAR2,
430 X_Attribute12 VARCHAR2,
431 X_Attribute13 VARCHAR2,
432 X_Attribute14 VARCHAR2,
433 X_Attribute15 VARCHAR2,
434 X_PA_Attribute_Category VARCHAR2,
435 X_PA_Attribute1 VARCHAR2,
436 X_PA_Attribute2 VARCHAR2,
437 X_PA_Attribute3 VARCHAR2,
438 X_PA_Attribute4 VARCHAR2,
439 X_PA_Attribute5 VARCHAR2,
440 X_PA_Attribute6 VARCHAR2,
441 X_PA_Attribute7 VARCHAR2,
442 X_PA_Attribute8 VARCHAR2,
443 X_PA_Attribute9 VARCHAR2,
444 X_PA_Attribute10 VARCHAR2
445 ) is
446
447 cursor c_source is
448 select nvl(sum(amount), 0)
449 from oke_k_funding_sources
450 where pool_party_id = x_pool_party_id;
451
452 l_amount number;
453
454 begin
455
456 update OKE_K_FUNDING_SOURCES
457 set
458 pool_party_id = X_Pool_Party_Id,
459 k_party_id = X_K_Party_Id,
460 amount = X_Amount,
461 previous_amount = X_Previous_Amount,
465 revenue_hard_limit = X_Revenue_Hard_Limit,
462 funding_status = X_Funding_Status,
463 agreement_number = X_Agreement_Number,
464 hard_limit = X_Hard_Limit,
466 agreement_org_id = X_Agreement_Org_ID,
467 k_conversion_type = X_K_Conversion_Type,
468 k_conversion_date = X_K_Conversion_Date,
469 k_conversion_rate = X_K_Conversion_Rate,
470 start_date_active = X_Start_Date_Active,
471 end_date_active = X_End_Date_Active,
472 last_update_date = X_Last_Update_Date,
473 last_updated_by = X_Last_Updated_By,
474 last_update_login = X_Last_Update_Login,
475 attribute_Category = X_Attribute_Category,
476 attribute1 = X_Attribute1,
477 attribute2 = X_Attribute2,
478 attribute3 = X_Attribute3,
479 attribute4 = X_Attribute4,
480 attribute5 = X_Attribute5,
481 attribute6 = X_Attribute6,
482 attribute7 = X_Attribute7,
483 attribute8 = X_Attribute8,
484 attribute9 = X_Attribute9,
485 attribute10 = X_Attribute10,
486 attribute11 = X_Attribute11,
487 attribute12 = X_Attribute12,
488 attribute13 = X_Attribute13,
489 attribute14 = X_Attribute14,
490 attribute15 = X_Attribute15,
491 pa_attribute_category = X_PA_Attribute_Category,
492 pa_attribute1 = X_PA_Attribute1,
493 pa_attribute2 = X_PA_Attribute2,
494 pa_attribute3 = X_PA_Attribute3,
495 pa_attribute4 = X_PA_Attribute4,
496 pa_attribute5 = X_PA_Attribute5,
497 pa_attribute6 = X_PA_Attribute6,
498 pa_attribute7 = X_PA_Attribute7,
499 pa_attribute8 = X_PA_Attribute8,
500 pa_attribute9 = X_PA_Attribute9,
501 pa_attribute10 = X_PA_Attribute10
502 where funding_source_id = X_Funding_Source_Id;
503
504 if (sql%notfound) then
505 raise no_data_found;
506 end if;
507
508 if (x_pool_party_id is not null) then
509
510 open c_source;
511 fetch c_source into l_amount;
512
513 if (c_source%notfound) then
514 close c_source;
515 raise no_data_found;
516 end if;
517
518 close c_source;
519
520 update oke_pool_parties
521 set available_amount = (amount - l_amount)
522 where pool_party_id = X_Pool_Party_Id;
523
524 if (sql%notfound) then
525 raise no_data_found;
526 end if;
527
528 end if;
529
530 end Update_Row;
531
532 PROCEDURE Delete_Row(X_Rowid VARCHAR2,
533 X_Pool_Party_Id NUMBER ) IS
534 cursor c_pool is
535 select *
536 from oke_pool_parties
537 where pool_party_id = x_pool_party_id
538 for update of pool_party_id nowait;
539
540 cursor c_source is
541 select nvl(amount, 0)
542 from oke_k_funding_sources
543 where rowid = x_rowid;
544
545 l_source_amount number;
546 l_pool_party_row c_pool%ROWTYPE;
547
548 BEGIN
549
550 OPEN c_source;
551 FETCH c_source into l_source_amount;
552 CLOSE c_source;
553
554 DELETE FROM OKE_K_FUNDING_SOURCES
555 WHERE rowid = X_Rowid;
556
557 if (SQL%NOTFOUND) then
558 Raise NO_DATA_FOUND;
559 end if;
560
561 if (x_pool_party_id is not null) then
562
563 OPEN c_pool;
564 FETCH c_pool into l_pool_party_row;
565
566 if (c_pool%notfound) then
567 close c_pool;
568 raise no_data_found;
569 end if;
570
571 close c_pool;
572
573 UPDATE OKE_POOL_PARTIES
574 SET available_amount = l_pool_party_row.available_amount + l_source_amount
575 WHERE pool_party_id = l_pool_party_row.pool_party_id;
576
577 end if;
578
579 EXCEPTION
580 WHEN OTHERS THEN
581 raise;
582
583 END Delete_Row;
584
585 END OKE_FundingSource_PVT;