[Home] [Help]
PACKAGE BODY: APPS.PA_BUDGET_VERSIONS_PKG
Source
1 PACKAGE BODY PA_BUDGET_VERSIONS_PKG as
2 /* $Header: PAXBUBVB.pls 120.1.12020000.2 2012/07/19 10:02:34 admarath ship $ */
3
4
5 --Name: Insert_Row
6 --Type: Procedure
7 --
8 --Description:
9 --
10 --Notes:
11 -- For the FP dev effort, the decision was made to provide
12 -- very limited FP support. Just enough to keep new FP
13 -- queries from breaking.
14 --
15 -- This procedure does NOT create FP plans!
16 --
17 -- You must use a PA_FIN_PLAN_PUB api to insert plans.
18 --
19 --
20 --
21 --
22 --Called subprograms: None.
23 --
24 --
25 --
26 --History:
27 -- XX-XXX-XX who? - Created
28 --
29 -- 19-AUG-02 jwhite - Minor modifications for the new FP model:
30 -- 1) Added new FP columns, approved_cost/rev_plan_type_flags.
31 --
32 PROCEDURE Insert_Row(X_Rowid IN OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
33 X_Budget_Version_Id IN OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
34 X_Project_Id NUMBER,
35 X_Budget_Type_Code VARCHAR2,
36 X_Version_Number NUMBER,
37 X_Budget_Status_Code VARCHAR2,
38 X_Last_Update_Date DATE,
39 X_Last_Updated_By NUMBER,
40 X_Creation_Date DATE,
41 X_Created_By NUMBER,
42 X_Last_Update_Login NUMBER,
43 X_Current_Flag VARCHAR2,
44 X_Original_Flag VARCHAR2,
45 X_Current_Original_Flag VARCHAR2,
46 X_Resource_Accumulated_Flag VARCHAR2,
47 X_Resource_List_Id NUMBER,
48 X_Version_Name VARCHAR2,
49 X_Budget_Entry_Method_Code VARCHAR2,
50 X_Baselined_By_Person_Id NUMBER,
51 X_Baselined_Date DATE,
52 X_Change_Reason_Code VARCHAR2,
53 X_Labor_Quantity NUMBER,
54 X_Labor_Unit_Of_Measure VARCHAR2,
55 X_Raw_Cost NUMBER,
56 X_Burdened_Cost NUMBER,
57 X_Revenue NUMBER,
58 X_Description VARCHAR2,
59 X_Attribute_Category VARCHAR2,
60 X_Attribute1 VARCHAR2,
61 X_Attribute2 VARCHAR2,
62 X_Attribute3 VARCHAR2,
63 X_Attribute4 VARCHAR2,
64 X_Attribute5 VARCHAR2,
65 X_Attribute6 VARCHAR2,
66 X_Attribute7 VARCHAR2,
67 X_Attribute8 VARCHAR2,
68 X_Attribute9 VARCHAR2,
69 X_Attribute10 VARCHAR2,
70 X_Attribute11 VARCHAR2,
71 X_Attribute12 VARCHAR2,
72 X_Attribute13 VARCHAR2,
73 X_Attribute14 VARCHAR2,
74 X_Attribute15 VARCHAR2,
75 X_First_Budget_Period VARCHAR2,
76 X_Pm_Product_Code VARCHAR2 DEFAULT NULL,
77 X_Pm_Budget_Reference VARCHAR2 DEFAULT NULL,
78 X_wf_status_code VARCHAR2 DEFAULT NULL,
79 x_adw_notify_flag VARCHAR2 DEFAULT NULL,
80 x_prc_generated_flag VARCHAR2 DEFAULT NULL,
81 x_plan_run_date DATE DEFAULT NULL,
82 x_plan_processing_code VARCHAR2 DEFAULT NULL
83 )
84
85 IS
86
87
88 CURSOR C IS SELECT rowid FROM pa_budget_versions
89 WHERE budget_version_id = X_Budget_Version_Id;
90
91 CURSOR C2 IS SELECT pa_budget_versions_s.nextval FROM sys.dual;
92
93 nc_Rowid VARCHAR2(30);
94 nc_Budget_Version_Id NUMBER;
95
96
97 BEGIN
98 nc_Rowid := X_Rowid;
99 nc_Budget_Version_Id := X_Budget_Version_Id;
100
101 if (X_Budget_Version_Id is NULL) then
102 OPEN C2;
103 FETCH C2 INTO X_Budget_Version_Id;
104 CLOSE C2;
105 end if;
106
107 INSERT INTO pa_budget_versions(
108 budget_version_id,
109 project_id,
110 budget_type_code,
111 version_number,
112 budget_status_code,
113 last_update_date,
114 last_updated_by,
115 creation_date,
116 created_by,
117 last_update_login,
118 current_flag,
119 original_flag,
120 current_original_flag,
121 resource_accumulated_flag,
122 resource_list_id,
123 version_name,
124 budget_entry_method_code,
125 baselined_by_person_id,
126 baselined_date,
127 change_reason_code,
128 labor_quantity,
129 labor_unit_of_measure,
130 raw_cost,
131 burdened_cost,
132 revenue,
133 description,
134 attribute_category,
135 attribute1,
136 attribute2,
137 attribute3,
138 attribute4,
139 attribute5,
140 attribute6,
141 attribute7,
142 attribute8,
143 attribute9,
144 attribute10,
145 attribute11,
146 attribute12,
147 attribute13,
148 attribute14,
149 attribute15,
150 first_budget_period,
151 pm_product_code,
152 pm_budget_reference,
153 wf_status_code,
154 ADW_NOTIFY_FLAG,
155 PRC_GENERATED_FLAG,
156 PLAN_RUN_DATE,
157 PLAN_PROCESSING_CODE,
158 approved_cost_plan_type_flag,
159 approved_rev_plan_type_flag
160 ) VALUES (
161 X_Budget_Version_Id,
162 X_Project_Id,
163 X_Budget_Type_Code,
164 X_Version_Number,
165 X_Budget_Status_Code,
166 X_Last_Update_Date,
167 X_Last_Updated_By,
168 X_Creation_Date,
169 X_Created_By,
170 X_Last_Update_Login,
171 X_Current_Flag,
172 X_Original_Flag,
173 X_Current_Original_Flag,
174 X_Resource_Accumulated_Flag,
175 X_Resource_List_Id,
176 X_Version_Name,
177 X_Budget_Entry_Method_Code,
178 X_Baselined_By_Person_Id,
179 X_Baselined_Date,
180 X_Change_Reason_Code,
181 (X_Labor_Quantity),
182 X_Labor_Unit_Of_Measure,
183 pa_currency.round_currency_amt(X_Raw_Cost),
184 pa_currency.round_currency_amt(X_Burdened_Cost),
185 pa_currency.round_currency_amt(X_Revenue),
186 X_Description,
187 X_Attribute_Category,
188 X_Attribute1,
189 X_Attribute2,
190 X_Attribute3,
191 X_Attribute4,
192 X_Attribute5,
193 X_Attribute6,
194 X_Attribute7,
195 X_Attribute8,
196 X_Attribute9,
197 X_Attribute10,
198 X_Attribute11,
199 X_Attribute12,
200 X_Attribute13,
201 X_Attribute14,
202 X_Attribute15,
203 X_First_Budget_Period,
204 X_Pm_Product_Code ,
205 X_Pm_Budget_Reference,
206 X_WF_Status_Code,
207 X_ADW_NOTIFY_FLAG,
208 X_PRC_GENERATED_FLAG,
209 X_PLAN_RUN_DATE,
210 X_PLAN_PROCESSING_CODE,
211 decode(x_budget_type_code,'AC','Y','N'),
212 decode(x_budget_type_code,'AR','Y','N')
213 );
214
215 OPEN C;
216 FETCH C INTO X_Rowid;
217 if (C%NOTFOUND) then
218 CLOSE C;
219 Raise NO_DATA_FOUND;
220 end if;
221 CLOSE C;
222
223 EXCEPTION
224 WHEN OTHERS THEN
225 X_Rowid := nc_Rowid;
226 X_Budget_Version_Id := nc_Budget_Version_Id;
227 RAISE;
228
229 END Insert_Row;
230
231
232 PROCEDURE Lock_Row(X_Rowid VARCHAR2,
233
234 X_Budget_Version_Id NUMBER,
235 X_Project_Id NUMBER,
236 X_Budget_Type_Code VARCHAR2,
237 X_Version_Number NUMBER,
238 X_Budget_Status_Code VARCHAR2,
239 X_Current_Flag VARCHAR2,
240 X_Original_Flag VARCHAR2,
241 X_Current_Original_Flag VARCHAR2,
242 X_Resource_Accumulated_Flag VARCHAR2,
243 X_Resource_List_Id NUMBER,
244 X_Version_Name VARCHAR2,
245 X_Budget_Entry_Method_Code VARCHAR2,
246 X_Baselined_By_Person_Id NUMBER,
247 X_Baselined_Date DATE,
248 X_Change_Reason_Code VARCHAR2,
249 X_Labor_Quantity NUMBER,
250 X_Labor_Unit_Of_Measure VARCHAR2,
251 X_Raw_Cost NUMBER,
252 X_Burdened_Cost NUMBER,
253 X_Revenue NUMBER,
254 X_Description VARCHAR2,
255 X_Attribute_Category VARCHAR2,
256 X_Attribute1 VARCHAR2,
257 X_Attribute2 VARCHAR2,
258 X_Attribute3 VARCHAR2,
259 X_Attribute4 VARCHAR2,
260 X_Attribute5 VARCHAR2,
261 X_Attribute6 VARCHAR2,
262 X_Attribute7 VARCHAR2,
263 X_Attribute8 VARCHAR2,
264 X_Attribute9 VARCHAR2,
265 X_Attribute10 VARCHAR2,
266 X_Attribute11 VARCHAR2,
267 X_Attribute12 VARCHAR2,
268 X_Attribute13 VARCHAR2,
269 X_Attribute14 VARCHAR2,
270 X_Attribute15 VARCHAR2,
271 X_First_Budget_Period VARCHAR2
272 ) IS
273 CURSOR C IS
274 SELECT *
275 FROM pa_budget_versions
276 WHERE rowid = X_Rowid
277 FOR UPDATE of Budget_Version_Id NOWAIT;
278 Recinfo C%ROWTYPE;
279
280
281 BEGIN
282 OPEN C;
283 FETCH C INTO Recinfo;
284 if (C%NOTFOUND) then
285 CLOSE C;
286 FND_MESSAGE.Set_Name('FND', 'FORM_RECORD_DELETED');
287 APP_EXCEPTION.Raise_Exception;
288 end if;
289 CLOSE C;
290 if (
291
292 (Recinfo.budget_version_id = X_Budget_Version_Id)
293 AND (Recinfo.project_id = X_Project_Id)
294 AND (Recinfo.budget_type_code = X_Budget_Type_Code)
295 AND (Recinfo.version_number = X_Version_Number)
296 AND (Recinfo.budget_status_code = X_Budget_Status_Code)
297 AND (Recinfo.current_flag = X_Current_Flag)
298 AND (Recinfo.original_flag = X_Original_Flag)
302 AND ( (Recinfo.version_name = X_Version_Name)
299 AND (Recinfo.current_original_flag = X_Current_Original_Flag)
300 AND (Recinfo.resource_accumulated_flag = X_Resource_Accumulated_Flag)
301 AND (Recinfo.resource_list_id = X_Resource_List_Id)
303 OR ( (Recinfo.version_name IS NULL)
304 AND (X_Version_Name IS NULL)))
305 AND ( (Recinfo.budget_entry_method_code =
306 X_Budget_Entry_Method_Code)
307 OR ( (Recinfo.budget_entry_method_code IS NULL)
308 AND (X_Budget_Entry_Method_Code IS NULL)))
309 AND ( (Recinfo.baselined_by_person_id = X_Baselined_By_Person_Id)
310 OR ( (Recinfo.baselined_by_person_id IS NULL)
311 AND (X_Baselined_By_Person_Id IS NULL)))
312 AND ( (Recinfo.baselined_date = X_Baselined_Date)
313 OR ( (Recinfo.baselined_date IS NULL)
314 AND (X_Baselined_Date IS NULL)))
315 AND ( (Recinfo.change_reason_code = X_Change_Reason_Code)
316 OR ( (Recinfo.change_reason_code IS NULL)
317 AND (X_Change_Reason_Code IS NULL)))
318 AND ( (Recinfo.labor_quantity = X_Labor_Quantity)
319 OR ( (Recinfo.labor_quantity IS NULL)
320 AND (X_Labor_Quantity IS NULL)))
321 AND ( (Recinfo.labor_unit_of_measure = X_Labor_Unit_Of_Measure)
322 OR ( (Recinfo.labor_unit_of_measure IS NULL)
323 AND (X_Labor_Unit_Of_Measure IS NULL)))
324 AND ( (Recinfo.raw_cost = X_Raw_Cost)
325 OR ( (Recinfo.raw_cost IS NULL)
326 AND (X_Raw_Cost IS NULL)))
327 AND ( (Recinfo.burdened_cost = X_Burdened_Cost)
328 OR ( (Recinfo.burdened_cost IS NULL)
329 AND (X_Burdened_Cost IS NULL)))
330 AND ( (Recinfo.revenue = X_Revenue)
331 OR ( (Recinfo.revenue IS NULL)
332 AND (X_Revenue IS NULL)))
333 AND ( (Recinfo.description = X_Description)
334 OR ( (Recinfo.description IS NULL)
335 AND (X_Description IS NULL)))
336 AND ( (Recinfo.attribute_category = X_Attribute_Category)
337 OR ( (Recinfo.attribute_category IS NULL)
338 AND (X_Attribute_Category IS NULL)))
339 AND ( (Recinfo.attribute1 = X_Attribute1)
340 OR ( (Recinfo.attribute1 IS NULL)
341 AND (X_Attribute1 IS NULL)))
342 AND ( (Recinfo.attribute2 = X_Attribute2)
343 OR ( (Recinfo.attribute2 IS NULL)
344 AND (X_Attribute2 IS NULL)))
345 AND ( (Recinfo.attribute3 = X_Attribute3)
346 OR ( (Recinfo.attribute3 IS NULL)
347 AND (X_Attribute3 IS NULL)))
348 AND ( (Recinfo.attribute4 = X_Attribute4)
349 OR ( (Recinfo.attribute4 IS NULL)
350 AND (X_Attribute4 IS NULL)))
351 AND ( (Recinfo.attribute5 = X_Attribute5)
352 OR ( (Recinfo.attribute5 IS NULL)
353 AND (X_Attribute5 IS NULL)))
354 AND ( (Recinfo.attribute6 = X_Attribute6)
355 OR ( (Recinfo.attribute6 IS NULL)
356 AND (X_Attribute6 IS NULL)))
357 AND ( (Recinfo.attribute7 = X_Attribute7)
358 OR ( (Recinfo.attribute7 IS NULL)
359 AND (X_Attribute7 IS NULL)))
360 AND ( (Recinfo.attribute8 = X_Attribute8)
361 OR ( (Recinfo.attribute8 IS NULL)
362 AND (X_Attribute8 IS NULL)))
363 AND ( (Recinfo.attribute9 = X_Attribute9)
364 OR ( (Recinfo.attribute9 IS NULL)
365 AND (X_Attribute9 IS NULL)))
366 AND ( (Recinfo.attribute10 = X_Attribute10)
367 OR ( (Recinfo.attribute10 IS NULL)
368 AND (X_Attribute10 IS NULL)))
369 AND ( (Recinfo.attribute11 = X_Attribute11)
370 OR ( (Recinfo.attribute11 IS NULL)
371 AND (X_Attribute11 IS NULL)))
372 AND ( (Recinfo.attribute12 = X_Attribute12)
373 OR ( (Recinfo.attribute12 IS NULL)
374 AND (X_Attribute12 IS NULL)))
375 AND ( (Recinfo.attribute13 = X_Attribute13)
376 OR ( (Recinfo.attribute13 IS NULL)
377 AND (X_Attribute13 IS NULL)))
378 AND ( (Recinfo.attribute14 = X_Attribute14)
379 OR ( (Recinfo.attribute14 IS NULL)
380 AND (X_Attribute14 IS NULL)))
381 AND ( (Recinfo.attribute15 = X_Attribute15)
382 OR ( (Recinfo.attribute15 IS NULL)
383 AND (X_Attribute15 IS NULL)))
384 AND ( (Recinfo.First_Budget_period = X_First_Budget_period)
385 OR ( (Recinfo.First_Budget_period IS NULL)
386 AND (X_First_Budget_period IS NULL)))
387 ) then
388 return;
389 else
390 FND_MESSAGE.Set_Name('FND', 'FORM_RECORD_CHANGED');
391 APP_EXCEPTION.Raise_Exception;
392 end if;
393 END Lock_Row;
394
395
396 --Name: Update_Row
397 --Type: Procedure
398 --
399 --Description:
400 --
401 --Notes:
402 -- For the FP dev effort, the decision was made to provide
403 -- very limited FP support. Just enough to keep new FP
404 -- queries from breaking.
405 --
406 -- This procedure does NOT update FP plans!
407 --
408 -- You must use a PA_FIN_PLAN_PUB api to update plans.
409 --
410 --
411 --
412 --
416 --
413 --Called subprograms: None.
414 --
415 --
417 --History:
418 -- XX-XXX-XX who? - Created
419 --
420 -- 19-AUG-02 jwhite - Minor modifications for the new FP model:
421 -- 1) Added new FP columns, approved_cost/rev_plan_type_flags.
422 --
423
424 PROCEDURE Update_Row(X_Rowid VARCHAR2,
425
426 X_Budget_Version_Id NUMBER,
427 X_Project_Id NUMBER,
428 X_Budget_Type_Code VARCHAR2,
429 X_Version_Number NUMBER,
430 X_Budget_Status_Code VARCHAR2,
431 X_Last_Update_Date DATE,
432 X_Last_Updated_By NUMBER,
433 X_Last_Update_Login NUMBER,
434 X_Current_Flag VARCHAR2,
435 X_Original_Flag VARCHAR2,
436 X_Current_Original_Flag VARCHAR2,
437 X_Resource_Accumulated_Flag VARCHAR2,
438 X_Resource_List_Id NUMBER,
439 X_Version_Name VARCHAR2,
440 X_Budget_Entry_Method_Code VARCHAR2,
441 X_Baselined_By_Person_Id NUMBER,
442 X_Baselined_Date DATE,
443 X_Change_Reason_Code VARCHAR2,
444 X_Labor_Quantity NUMBER,
445 X_Labor_Unit_Of_Measure VARCHAR2,
446 X_Raw_Cost NUMBER,
447 X_Burdened_Cost NUMBER,
448 X_Revenue NUMBER,
449 X_Description VARCHAR2,
450 X_Attribute_Category VARCHAR2,
451 X_Attribute1 VARCHAR2,
452 X_Attribute2 VARCHAR2,
453 X_Attribute3 VARCHAR2,
454 X_Attribute4 VARCHAR2,
455 X_Attribute5 VARCHAR2,
456 X_Attribute6 VARCHAR2,
457 X_Attribute7 VARCHAR2,
458 X_Attribute8 VARCHAR2,
459 X_Attribute9 VARCHAR2,
460 X_Attribute10 VARCHAR2,
461 X_Attribute11 VARCHAR2,
462 X_Attribute12 VARCHAR2,
463 X_Attribute13 VARCHAR2,
464 X_Attribute14 VARCHAR2,
465 X_Attribute15 VARCHAR2,
466 X_First_Budget_period VARCHAR2,
467 X_WF_Status_Code VARCHAR2,
468 x_adw_notify_flag VARCHAR2 DEFAULT NULL,
469 x_prc_generated_flag VARCHAR2 DEFAULT NULL,
470 x_plan_run_date DATE DEFAULT NULL,
471 x_plan_processing_code VARCHAR2 DEFAULT NULL
472 ) IS
473 BEGIN
474 UPDATE pa_budget_versions
475 SET
476 budget_version_id = X_Budget_Version_Id,
477 project_id = X_Project_Id,
478 budget_type_code = X_Budget_Type_Code,
479 version_number = X_Version_Number,
480 budget_status_code = X_Budget_Status_Code,
481 last_update_date = X_Last_Update_Date,
482 last_updated_by = X_Last_Updated_By,
483 last_update_login = X_Last_Update_Login,
484 current_flag = X_Current_Flag,
485 original_flag = X_Original_Flag,
486 current_original_flag = X_Current_Original_Flag,
487 resource_accumulated_flag = X_Resource_Accumulated_Flag,
488 resource_list_id = X_Resource_List_Id,
489 version_name = X_Version_Name,
490 budget_entry_method_code = X_Budget_Entry_Method_Code,
491 baselined_by_person_id = X_Baselined_By_Person_Id,
492 baselined_date = X_Baselined_Date,
493 change_reason_code = X_Change_Reason_Code,
494 labor_quantity = (X_Labor_Quantity),
495 labor_unit_of_measure = X_Labor_Unit_Of_Measure,
496 raw_cost = pa_currency.round_currency_amt(X_Raw_Cost),
497 burdened_cost = pa_currency.round_currency_amt(X_Burdened_Cost),
498 revenue = pa_currency.round_currency_amt(X_Revenue),
499 description = X_Description,
500 attribute_category = X_Attribute_Category,
501 attribute1 = X_Attribute1,
502 attribute2 = X_Attribute2,
503 attribute3 = X_Attribute3,
504 attribute4 = X_Attribute4,
505 attribute5 = X_Attribute5,
506 attribute6 = X_Attribute6,
507 attribute7 = X_Attribute7,
508 attribute8 = X_Attribute8,
509 attribute9 = X_Attribute9,
510 attribute10 = X_Attribute10,
511 attribute11 = X_Attribute11,
512 attribute12 = X_Attribute12,
516 first_budget_period = X_First_Budget_Period,
513 attribute13 = X_Attribute13,
514 attribute14 = X_Attribute14,
515 attribute15 = X_Attribute15,
517 wf_status_code = X_WF_Status_Code,
518 ADW_NOTIFY_FLAG = DECODE(x_ADW_NOTIFY_FLAG, NULL, ADW_NOTIFY_FLAG, x_ADW_NOTIFY_FLAG),
519 PRC_GENERATED_FLAG = DECODE(x_PRC_GENERATED_FLAG, NULL, PRC_GENERATED_FLAG, x_PRC_GENERATED_FLAG),
520 PLAN_RUN_DATE = DECODE(x_PLAN_RUN_DATE, NULL, PLAN_RUN_DATE, x_PLAN_RUN_DATE),
521 PLAN_PROCESSING_CODE = DECODE(x_PLAN_PROCESSING_CODE, NULL, PLAN_PROCESSING_CODE, x_PLAN_PROCESSING_CODE),
522 approved_cost_plan_type_flag = decode(x_budget_type_code,'AC','Y','N'),
523 approved_rev_plan_type_flag = decode(x_budget_type_code,'AR','Y','N')
524 WHERE rowid = X_Rowid;
525
526 if (SQL%NOTFOUND) then
527 Raise NO_DATA_FOUND;
528 end if;
529 END Update_Row;
530
531 PROCEDURE Delete_Row(X_Rowid VARCHAR2) IS
532 BEGIN
533 DELETE FROM pa_budget_versions
534 WHERE rowid = X_Rowid;
535
536 if (SQL%NOTFOUND) then
537 Raise NO_DATA_FOUND;
538 end if;
539 END Delete_Row;
540
541
542 END PA_BUDGET_VERSIONS_PKG;