1 PACKAGE BODY PA_PROJECT_FUNDINGS_PKG as
2 /* $Header: PAINPFDB.pls 120.1 2005/08/05 00:13:12 rgandhi noship $ */
3
4 PROCEDURE Insert_Row(
5 X_Rowid IN OUT NOCOPY VARCHAR2, /*File.sql.39*/
6 X_Project_Funding_Id IN OUT NOCOPY NUMBER, /*File.sql.39*/
7 X_Last_Update_Date IN DATE,
8 X_Last_Updated_By IN NUMBER,
9 X_Creation_Date IN DATE,
10 X_Created_By IN NUMBER,
11 X_Last_Update_Login IN NUMBER,
12 X_Agreement_Id IN NUMBER,
13 X_Project_Id IN NUMBER,
14 X_Task_Id IN NUMBER,
15 X_Budget_Type_Code IN VARCHAR2,
16 X_Allocated_Amount IN NUMBER,
17 X_Date_Allocated IN DATE,
18 X_Attribute_Category IN VARCHAR2,
19 X_Control_Item_ID IN NUMBER DEFAULT NULL, /* Added for FP_M changes */
20 X_Attribute1 IN VARCHAR2,
21 X_Attribute2 IN VARCHAR2,
22 X_Attribute3 IN VARCHAR2,
23 X_Attribute4 IN VARCHAR2,
24 X_Attribute5 IN VARCHAR2,
25 X_Attribute6 IN VARCHAR2,
26 X_Attribute7 IN VARCHAR2,
27 X_Attribute8 IN VARCHAR2,
28 X_Attribute9 IN VARCHAR2,
29 X_Attribute10 IN VARCHAR2,
30 X_pm_funding_reference IN VARCHAR2,
31 X_pm_product_code IN VARCHAR2,
32 x_funding_currency_code IN VARCHAR2,
33 x_project_currency_code IN VARCHAR2,
34 x_project_rate_type IN VARCHAR2,
35 x_project_rate_date IN DATE,
36 x_project_exchange_rate IN NUMBER,
37 x_project_allocated_amount IN NUMBER,
38 x_projfunc_currency_code IN VARCHAR2,
39 x_projfunc_rate_type IN VARCHAR2,
40 x_projfunc_rate_date IN DATE,
41 x_projfunc_exchange_rate IN NUMBER,
42 x_projfunc_allocated_amount IN NUMBER,
43 x_invproc_currency_code IN VARCHAR2,
44 x_invproc_rate_type IN VARCHAR2,
45 x_invproc_rate_date IN DATE,
46 x_invproc_exchange_rate IN NUMBER,
47 x_invproc_allocated_amount IN NUMBER,
48 x_revproc_currency_code IN VARCHAR2,
49 x_revproc_rate_type IN VARCHAR2,
50 x_revproc_rate_date IN DATE,
51 x_revproc_exchange_rate IN NUMBER,
52 x_revproc_allocated_amount IN NUMBER,
53 x_funding_category IN VARCHAR2, /* For Bug 2244796 */
54 x_revaluation_through_date IN DATE DEFAULT NULL,
55 x_revaluation_rate_date IN DATE DEFAULT NULL,
56 x_reval_projfunc_rate_type IN VARCHAR2 DEFAULT NULL,
57 x_reval_invproc_rate_type IN VARCHAR2 DEFAULT NULL,
58 x_revaluation_projfunc_rate IN NUMBER DEFAULT NULL,
59 x_revaluation_invproc_rate IN NUMBER DEFAULT NULL,
60 x_funding_inv_applied_amount IN NUMBER DEFAULT NULL,
61 x_funding_inv_due_amount IN NUMBER DEFAULT NULL,
62 x_funding_backlog_amount IN NUMBER DEFAULT NULL,
63 x_projfunc_realized_gains_amt IN NUMBER DEFAULT NULL,
64 x_projfunc_realized_losses_amt IN NUMBER DEFAULT NULL,
65 x_projfunc_inv_applied_amount IN NUMBER DEFAULT NULL,
66 x_projfunc_inv_due_amount IN NUMBER DEFAULT NULL,
67 x_projfunc_backlog_amount IN NUMBER DEFAULT NULL,
68 x_non_updateable_flag IN VARCHAR2 DEFAULT NULL,
69 x_invproc_backlog_amount IN NUMBER DEFAULT NULL,
70 x_funding_reval_amount IN NUMBER DEFAULT NULL,
71 x_projfunc_reval_amount IN NUMBER DEFAULT NULL,
72 x_projfunc_revalued_amount IN NUMBER DEFAULT NULL,
73 x_invproc_reval_amount IN NUMBER DEFAULT NULL,
74 x_invproc_revalued_amount IN NUMBER DEFAULT NULL,
75 x_funding_revaluation_factor IN NUMBER DEFAULT NULL,
76 x_request_id IN NUMBER DEFAULT NULL,
77 x_program_application_id IN NUMBER DEFAULT NULL,
78 x_program_id IN NUMBER DEFAULT NULL,
79 x_program_update_date IN DATE DEFAULT NULL
80 ) IS
81
82 CURSOR C IS SELECT rowid FROM PA_PROJECT_FUNDINGS
83 WHERE project_funding_id = X_Project_Funding_Id;
84 CURSOR C2 IS SELECT pa_project_fundings_s.nextval FROM sys.dual;
85
86 l_Project_Funding_Id NUMBER := X_Project_Funding_Id; /*File.sql.39*/
87 BEGIN
88 if (X_Project_Funding_Id is NULL OR X_Project_Funding_Id = PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM) then /* Added second condition for bug 3452865 */
89 OPEN C2;
90 FETCH C2 INTO X_Project_Funding_Id;
91 CLOSE C2;
92 end if;
93
94 INSERT INTO PA_PROJECT_FUNDINGS(
95 project_funding_id,
96 last_update_date,
97 last_updated_by,
98 creation_date,
99 created_by,
100 last_update_login,
101 agreement_id,
102 project_id,
103 task_id,
104 budget_type_code,
105 allocated_amount,
106 date_allocated,
107 attribute_category,
108 attribute1,
109 attribute2,
110 attribute3,
111 attribute4,
112 attribute5,
113 attribute6,
114 attribute7,
115 attribute8,
116 attribute9,
117 attribute10,
118 pm_funding_reference,
119 pm_product_code,
120 funding_currency_code,
121 project_currency_code,
122 project_rate_type,
123 project_rate_date,
124 project_exchange_rate,
125 project_allocated_amount,
126 projfunc_currency_code,
127 projfunc_rate_type,
128 projfunc_rate_date,
129 projfunc_exchange_rate,
130 projfunc_allocated_amount,
131 invproc_currency_code,
132 invproc_rate_type,
133 invproc_rate_date,
134 invproc_exchange_rate,
135 invproc_allocated_amount,
136 revproc_currency_code,
137 revproc_rate_type,
138 revproc_rate_date,
139 revproc_exchange_rate,
140 revproc_allocated_amount,
141 funding_category, /* For Bug 2244796 */
142 revaluation_through_date,
143 revaluation_rate_date,
144 revaluation_projfunc_rate_type,
145 revaluation_invproc_rate_type,
146 revaluation_projfunc_rate,
147 revaluation_invproc_rate,
148 funding_inv_applied_amount,
149 funding_inv_due_amount,
150 funding_backlog_amount,
151 projfunc_realized_gains_amt,
152 projfunc_realized_losses_amt,
153 projfunc_inv_applied_amount,
154 projfunc_inv_due_amount,
155 projfunc_backlog_amount,
156 non_updateable_flag,
157 invproc_backlog_amount,
158 funding_reval_amount,
159 projfunc_reval_amount,
160 projfunc_revalued_amount,
161 invproc_reval_amount,
162 invproc_revalued_amount,
163 funding_revaluation_factor,
164 request_id,
165 program_application_id,
166 program_id,
167 program_update_date,
168 CI_ID
169 )
170 VALUES (
171 X_Project_Funding_Id,
172 X_Last_Update_Date,
173 X_Last_Updated_By,
174 X_Creation_Date,
175 X_Created_By,
176 X_Last_Update_Login,
177 X_Agreement_Id,
178 X_Project_Id,
179 X_Task_Id,
180 X_Budget_Type_Code,
181 X_Allocated_Amount,
182 X_Date_Allocated,
183 X_Attribute_Category,
184 X_Attribute1,
185 X_Attribute2,
186 X_Attribute3,
187 X_Attribute4,
188 X_Attribute5,
189 X_Attribute6,
190 X_Attribute7,
191 X_Attribute8,
192 X_Attribute9,
193 X_Attribute10,
194 X_pm_funding_reference,
195 X_pm_product_code,
196 x_funding_currency_code,
197 x_project_currency_code,
198 x_project_rate_type,
199 x_project_rate_date,
200 x_project_exchange_rate,
201 x_project_allocated_amount,
202 x_projfunc_currency_code,
203 x_projfunc_rate_type,
204 x_projfunc_rate_date,
205 x_projfunc_exchange_rate,
206 x_projfunc_allocated_amount,
207 x_invproc_currency_code,
208 x_invproc_rate_type,
209 x_invproc_rate_date,
210 x_invproc_exchange_rate,
211 x_invproc_allocated_amount,
212 x_revproc_currency_code,
213 x_revproc_rate_type,
214 x_revproc_rate_date,
215 x_revproc_exchange_rate,
216 x_revproc_allocated_amount,
217 X_funding_category, /* For Bug 2244796 */
218 x_revaluation_through_date,
219 x_revaluation_rate_date,
220 x_reval_projfunc_rate_type,
221 x_reval_invproc_rate_type,
222 x_revaluation_projfunc_rate,
223 x_revaluation_invproc_rate,
224 x_funding_inv_applied_amount,
225 x_funding_inv_due_amount,
226 x_funding_backlog_amount,
227 x_projfunc_realized_gains_amt,
228 x_projfunc_realized_losses_amt,
229 x_projfunc_inv_applied_amount,
230 x_projfunc_inv_due_amount,
231 x_projfunc_backlog_amount,
232 x_non_updateable_flag,
233 x_invproc_backlog_amount,
234 x_funding_reval_amount,
235 x_projfunc_reval_amount,
236 x_projfunc_revalued_amount,
237 x_invproc_reval_amount,
238 x_invproc_revalued_amount,
239 x_funding_revaluation_factor,
240 x_request_id,
241 x_program_application_id,
242 x_program_id,
243 x_program_update_date,
244 X_Control_Item_ID /* Added for FP_M changes */
245 );
246
247 OPEN C;
248 FETCH C INTO X_Rowid;
249 if (C%NOTFOUND) then
250 CLOSE C;
251 Raise NO_DATA_FOUND;
252 end if;
253 CLOSE C;
254
255 /*Added Exception for file.sql.39*/
256 EXCEPTION
257 WHEN OTHERS THEN
258 X_Rowid :=NULL;
259 X_Project_Funding_Id := l_Project_Funding_Id;
260 raise;
261 END Insert_Row;
262
263
264 PROCEDURE Lock_Row(
265 X_Rowid IN VARCHAR2,
266 X_Project_Funding_Id IN NUMBER,
267 X_Agreement_Id IN NUMBER,
268 X_Project_Id IN NUMBER,
269 X_Task_Id IN NUMBER,
270 X_Budget_Type_Code IN VARCHAR2,
271 X_Allocated_Amount IN NUMBER,
272 X_Date_Allocated IN DATE,
273 X_Attribute_Category IN VARCHAR2,
274 X_Attribute1 IN VARCHAR2,
275 X_Attribute2 IN VARCHAR2,
276 X_Attribute3 IN VARCHAR2,
277 X_Attribute4 IN VARCHAR2,
278 X_Attribute5 IN VARCHAR2,
279 X_Attribute6 IN VARCHAR2,
280 X_Attribute7 IN VARCHAR2,
281 X_Attribute8 IN VARCHAR2,
282 X_Attribute9 IN VARCHAR2,
283 X_Attribute10 IN VARCHAR2,
284 X_pm_funding_reference IN VARCHAR2,
285 X_pm_product_code IN VARCHAR2,
286 x_funding_currency_code IN VARCHAR2,
287 x_project_currency_code IN VARCHAR2,
288 x_project_rate_type IN VARCHAR2,
289 x_project_rate_date IN DATE,
290 x_project_exchange_rate IN NUMBER,
291 x_project_allocated_amount IN NUMBER,
292 x_projfunc_currency_code IN VARCHAR2,
293 x_projfunc_rate_type IN VARCHAR2,
294 x_projfunc_rate_date IN DATE,
295 x_projfunc_exchange_rate IN NUMBER,
296 x_projfunc_allocated_amount IN NUMBER,
297 X_funding_category IN VARCHAR2 /* For Bug 2244796 */
298 ) IS
299 CURSOR C IS
300 SELECT *
301 FROM PA_PROJECT_FUNDINGS
302 WHERE rowid = X_Rowid
303 FOR UPDATE of Project_Funding_Id NOWAIT;
304 Recinfo C%ROWTYPE;
305
306 BEGIN
307 OPEN C;
308 FETCH C INTO Recinfo;
309 if (C%NOTFOUND) then
310 CLOSE C;
311 FND_MESSAGE.Set_Name('FND', 'FORM_RECORD_DELETED');
312 APP_EXCEPTION.Raise_Exception;
313 end if;
314 CLOSE C;
315 if (
316
317 (Recinfo.project_funding_id = X_Project_Funding_Id)
318 AND (Recinfo.agreement_id = X_Agreement_Id)
319 AND (Recinfo.project_id = X_Project_Id)
320 AND ( (Recinfo.task_id = X_Task_Id)
321 OR ( (Recinfo.task_id IS NULL)
322 AND (X_Task_Id IS NULL)))
323 AND (Recinfo.budget_type_code = X_Budget_Type_Code)
324 AND (Recinfo.allocated_amount = X_Allocated_Amount)
325 AND (trunc(Recinfo.date_allocated) = trunc(X_Date_Allocated))
326 AND ( (Recinfo.attribute_category = X_Attribute_Category)
327 OR ( (Recinfo.attribute_category IS NULL)
328 AND (X_Attribute_Category IS NULL)))
329 AND ( (Recinfo.attribute1 = X_Attribute1)
330 OR ( (Recinfo.attribute1 IS NULL)
331 AND (X_Attribute1 IS NULL)))
332 AND ( (Recinfo.attribute2 = X_Attribute2)
333 OR ( (Recinfo.attribute2 IS NULL)
334 AND (X_Attribute2 IS NULL)))
335 AND ( (Recinfo.attribute3 = X_Attribute3)
336 OR ( (Recinfo.attribute3 IS NULL)
337 AND (X_Attribute3 IS NULL)))
338 AND ( (Recinfo.attribute4 = X_Attribute4)
339 OR ( (Recinfo.attribute4 IS NULL)
340 AND (X_Attribute4 IS NULL)))
341 AND ( (Recinfo.attribute5 = X_Attribute5)
342 OR ( (Recinfo.attribute5 IS NULL)
343 AND (X_Attribute5 IS NULL)))
344 AND ( (Recinfo.attribute6 = X_Attribute6)
345 OR ( (Recinfo.attribute6 IS NULL)
346 AND (X_Attribute6 IS NULL)))
347 AND ( (Recinfo.attribute7 = X_Attribute7)
348 OR ( (Recinfo.attribute7 IS NULL)
349 AND (X_Attribute7 IS NULL)))
350 AND ( (Recinfo.attribute8 = X_Attribute8)
351 OR ( (Recinfo.attribute8 IS NULL)
352 AND (X_Attribute8 IS NULL)))
353 AND ( (Recinfo.attribute9 = X_Attribute9)
354 OR ( (Recinfo.attribute9 IS NULL)
355 AND (X_Attribute9 IS NULL)))
356 AND ( (Recinfo.attribute10 = X_Attribute10)
357 OR ( (Recinfo.attribute10 IS NULL)
358 AND (X_Attribute10 IS NULL)))
359 AND ( (Recinfo.pm_funding_reference = X_pm_funding_reference)
360 OR ( (Recinfo.pm_funding_reference IS NULL)
361 AND (X_pm_funding_reference IS NULL)))
362 AND ( (Recinfo.pm_product_code = X_pm_product_code)
363 OR ( (Recinfo.pm_product_code IS NULL)
364 AND (X_pm_product_code IS NULL)))
365 AND ( (Recinfo.funding_currency_code = X_funding_currency_code)
366 OR ( (Recinfo.funding_currency_code IS NULL)
367 AND (X_funding_currency_code IS NULL)))
368 AND ( (Recinfo.project_currency_code = X_project_currency_code)
369 OR ( (Recinfo.project_currency_code IS NULL)
370 AND (X_project_currency_code IS NULL)))
371 AND ( (Recinfo.project_rate_type = X_project_rate_type)
372 OR ( (Recinfo.project_rate_type IS NULL)
373 AND (X_project_rate_type IS NULL)))
374 AND ( (trunc(Recinfo.project_rate_date) = trunc(X_project_rate_date))
375 OR ( (trunc(Recinfo.project_rate_date) IS NULL)
376 AND (trunc(X_project_rate_date) IS NULL)))
377 AND ( (Recinfo.project_exchange_rate = X_project_exchange_rate)
378 OR ( (Recinfo.project_exchange_rate IS NULL)
379 AND (X_project_exchange_rate IS NULL)))
380 AND ( (Recinfo.project_allocated_amount =
381 X_project_allocated_amount)
382 OR ( (Recinfo.project_allocated_amount IS NULL)
383 AND (X_project_allocated_amount IS NULL)))
384 AND ( (Recinfo.projfunc_currency_code = X_projfunc_currency_code)
385 OR ( (Recinfo.projfunc_currency_code IS NULL)
386 AND (X_projfunc_currency_code IS NULL)))
387 AND ( (Recinfo.projfunc_rate_type = X_projfunc_rate_type)
388 OR ( (Recinfo.projfunc_rate_type IS NULL)
389 AND (X_projfunc_rate_type IS NULL)))
390 AND ( (trunc(Recinfo.projfunc_rate_date) = trunc(X_projfunc_rate_date))
391 OR ( (trunc(Recinfo.projfunc_rate_date) IS NULL)
392 AND (trunc(X_projfunc_rate_date) IS NULL)))
393 AND ( (Recinfo.projfunc_exchange_rate = X_projfunc_exchange_rate)
394 OR ( (Recinfo.projfunc_exchange_rate IS NULL)
395 AND (X_projfunc_exchange_rate IS NULL)))
396 AND ( (Recinfo.projfunc_allocated_amount =
397 X_projfunc_allocated_amount)
398 OR ( (Recinfo.projfunc_allocated_amount IS NULL)
399 AND (X_projfunc_allocated_amount IS NULL)))
400 AND ( (Recinfo.funding_category = X_funding_category) /* For Bug 2244796 */
401 OR ( (Recinfo.funding_category IS NULL)
402 AND (X_funding_category IS NULL)))
403 ) then
404 return;
405 else
406 FND_MESSAGE.Set_Name('FND', 'FORM_RECORD_CHANGED');
407 APP_EXCEPTION.Raise_Exception;
408 end if;
409 END Lock_Row;
410
411
412
413 PROCEDURE Update_Row(
414 X_Rowid IN VARCHAR2,
415 X_Project_Funding_Id IN NUMBER,
416 X_Last_Update_Date IN DATE,
417 X_Last_Updated_By IN NUMBER,
418 X_Last_Update_Login IN NUMBER,
419 X_Agreement_Id IN NUMBER,
420 X_Project_Id IN NUMBER,
421 X_Task_Id IN NUMBER,
422 X_Budget_Type_Code IN VARCHAR2,
423 X_Allocated_Amount IN NUMBER,
424 X_Date_Allocated IN DATE,
425 X_Attribute_Category IN VARCHAR2,
426 X_Attribute1 IN VARCHAR2,
427 X_Attribute2 IN VARCHAR2,
428 X_Attribute3 IN VARCHAR2,
429 X_Attribute4 IN VARCHAR2,
430 X_Attribute5 IN VARCHAR2,
431 X_Attribute6 IN VARCHAR2,
432 X_Attribute7 IN VARCHAR2,
433 X_Attribute8 IN VARCHAR2,
434 X_Attribute9 IN VARCHAR2,
435 X_Attribute10 IN VARCHAR2,
436 X_pm_funding_reference IN VARCHAR2,
437 X_pm_product_code IN VARCHAR2,
438 x_funding_currency_code IN VARCHAR2,
439 x_project_currency_code IN VARCHAR2,
440 x_project_rate_type IN VARCHAR2,
441 x_project_rate_date IN DATE,
442 x_project_exchange_rate IN NUMBER,
443 x_project_allocated_amount IN NUMBER,
444 x_projfunc_currency_code IN VARCHAR2,
445 x_projfunc_rate_type IN VARCHAR2,
446 x_projfunc_rate_date IN DATE,
447 x_projfunc_exchange_rate IN NUMBER,
448 x_projfunc_allocated_amount IN NUMBER,
449 x_invproc_currency_code IN VARCHAR2,
450 x_invproc_rate_type IN VARCHAR2,
451 x_invproc_rate_date IN DATE,
452 x_invproc_exchange_rate IN NUMBER,
453 x_invproc_allocated_amount IN NUMBER,
454 x_revproc_currency_code IN VARCHAR2,
455 x_revproc_rate_type IN VARCHAR2,
456 x_revproc_rate_date IN DATE,
457 x_revproc_exchange_rate IN NUMBER,
458 x_revproc_allocated_amount IN NUMBER,
459 X_funding_category IN VARCHAR2 /* For Bug 2244796 */
460 ) IS
461 BEGIN
462 UPDATE PA_PROJECT_FUNDINGS
463 SET
464 project_funding_id = X_Project_Funding_Id,
465 last_update_date = X_Last_Update_Date,
466 last_updated_by = X_Last_Updated_By,
467 last_update_login = X_Last_Update_Login,
468 agreement_id = X_Agreement_Id,
469 project_id = X_Project_Id,
470 task_id = X_Task_Id,
471 budget_type_code = X_Budget_Type_Code,
472 allocated_amount = X_Allocated_Amount,
473 date_allocated = X_Date_Allocated,
474 attribute_category = X_Attribute_Category,
475 attribute1 = X_Attribute1,
476 attribute2 = X_Attribute2,
477 attribute3 = X_Attribute3,
478 attribute4 = X_Attribute4,
479 attribute5 = X_Attribute5,
480 attribute6 = X_Attribute6,
481 attribute7 = X_Attribute7,
482 attribute8 = X_Attribute8,
483 attribute9 = X_Attribute9,
484 attribute10 = X_Attribute10,
485 pm_funding_reference = X_pm_funding_reference,
486 pm_product_code = X_pm_product_code,
487 funding_currency_code = x_funding_currency_code,
488 project_currency_code = x_project_currency_code,
489 project_rate_type = x_project_rate_type,
490 project_rate_date = x_project_rate_date,
491 project_exchange_rate = x_project_exchange_rate,
492 project_allocated_amount = x_project_allocated_amount,
493 projfunc_currency_code = x_projfunc_currency_code,
494 projfunc_rate_type = x_projfunc_rate_type,
495 projfunc_rate_date = x_projfunc_rate_date,
496 projfunc_exchange_rate = x_projfunc_exchange_rate,
497 projfunc_allocated_amount = x_projfunc_allocated_amount,
498 invproc_currency_code = x_invproc_currency_code,
499 invproc_rate_type = x_invproc_rate_type,
500 invproc_rate_date = x_invproc_rate_date,
501 invproc_exchange_rate = x_invproc_exchange_rate,
502 invproc_allocated_amount = x_invproc_allocated_amount,
503 revproc_currency_code = x_revproc_currency_code,
504 revproc_rate_type = x_revproc_rate_type,
505 revproc_rate_date = x_revproc_rate_date,
506 revproc_exchange_rate = x_revproc_exchange_rate,
507 revproc_allocated_amount = x_revproc_allocated_amount,
508 funding_category = X_funding_category /* For Bug2244796 */
509 WHERE rowid = X_Rowid;
510
511 if (SQL%NOTFOUND) then
512 Raise NO_DATA_FOUND;
513 end if;
514 END Update_Row;
515
516 PROCEDURE Delete_Row(X_Rowid VARCHAR2) IS
517 BEGIN
518 DELETE FROM PA_PROJECT_FUNDINGS
519 WHERE rowid = X_Rowid;
520
521 if (SQL%NOTFOUND) then
522 Raise NO_DATA_FOUND;
523 end if;
524 END Delete_Row;
525
526
527 END PA_PROJECT_FUNDINGS_PKG;
[Home] [Help]