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