[Home] [Help]
PACKAGE BODY: APPS.PA_ACCUM_API
Source
1 PACKAGE BODY PA_ACCUM_API AS
2 /* $Header: PAAAPIB.pls 120.2 2005/08/19 16:13:17 mwasowic ship $ */
3
4 PROCEDURE get_period_date_range
5 (x_period_type IN VARCHAR2 DEFAULT 'P',
6 x_from_period_name IN VARCHAR2 DEFAULT NULL,
7 x_to_period_name IN VARCHAR2 DEFAULT NULL,
8 x_start_date IN OUT NOCOPY DATE, --File.Sql.39 bug 4440895
9 x_end_date IN OUT NOCOPY DATE, --File.Sql.39 bug 4440895
10 x_err_stage IN OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
11 x_err_code IN OUT NOCOPY NUMBER) --File.Sql.39 bug 4440895
12 IS
13 BEGIN
14 x_err_code := 0;
15 x_err_stage := 'Getting the period date range';
16
17 x_start_date := NULL;
18 x_end_date := NULL;
19
20 -- Get the period start date and end date
21
22 IF ( x_period_type = 'G' ) THEN
23 -- Get the dates from GL_PERIOD_STATUSES table
24 /*
25 SELECT MIN(sp.start_date)
26 INTO x_start_date
27 FROM gl_period_statuses sp, pa_implementations imp
28 WHERE
29 sp.period_name = NVL(x_from_period_name,sp.period_name)
30 AND sp.set_of_books_id = imp.set_of_books_id
31 AND sp.application_id = 101
32 AND sp.adjustment_period_flag = 'N';
33
34 SELECT MAX(ep.end_date)
35 INTO x_end_date
36 FROM gl_period_statuses ep, pa_implementations imp
37 WHERE
38 ep.period_name = NVL(x_to_period_name,ep.period_name)
39 AND ep.set_of_books_id = imp.set_of_books_id
40 AND ep.application_id = 101
41 AND ep.adjustment_period_flag = 'N';
42 */
43
44 SELECT MIN(sp.start_date)
45 INTO x_start_date
46 FROM gl_period_statuses sp, pa_implementations imp
47 WHERE
48 sp.period_name =x_from_period_name
49 AND sp.set_of_books_id = imp.set_of_books_id
50 AND sp.application_id = pa_period_process_pkg.application_id
51 AND sp.adjustment_period_flag = 'N';
52
53 if x_start_date is null then
54
55 SELECT MIN(sp.start_date)
56 INTO x_start_date
57 FROM gl_period_statuses sp, pa_implementations imp
58 WHERE sp.set_of_books_id = imp.set_of_books_id
59 AND sp.application_id = pa_period_process_pkg.application_id
60 AND sp.adjustment_period_flag = 'N';
61
62 end if;
63
64
65 SELECT MAX(ep.end_date)
66 INTO x_end_date
67 FROM gl_period_statuses ep, pa_implementations imp
68 WHERE
69 ep.period_name = x_to_period_name
70 AND ep.set_of_books_id = imp.set_of_books_id
71 AND ep.application_id = pa_period_process_pkg.application_id
72 AND ep.adjustment_period_flag = 'N';
73
74 if x_end_date is null then
75
76 SELECT MAX(ep.end_date)
77 INTO x_end_date
78 FROM gl_period_statuses ep, pa_implementations imp
79 WHERE ep.set_of_books_id = imp.set_of_books_id
80 AND ep.application_id = pa_period_process_pkg.application_id
81 AND ep.adjustment_period_flag = 'N';
82
83 end if;
84
85 ELSE
86
87 -- Get the dates from PA_PERIODS table
88
89 SELECT MIN(sp.start_date)
90 INTO x_start_date
91 FROM pa_periods sp
92 WHERE sp.period_name = x_from_period_name;
93
94 IF ( x_start_date IS NULL ) THEN
95 SELECT MIN(sp.start_date)
96 INTO x_start_date
97 FROM pa_periods sp;
98 END IF;
99
100 SELECT MAX(ep.end_date)
101 INTO x_end_date
102 FROM pa_periods ep
103 WHERE ep.period_name = x_to_period_name;
104
105 IF (x_end_date IS NULL ) THEN
106 SELECT MAX(ep.end_date)
107 INTO x_end_date
108 FROM pa_periods ep;
109 END IF;
110
111 /*
112 SELECT MIN(sp.start_date)
113 INTO x_start_date
114 FROM pa_periods sp
115 WHERE
116 sp.period_name = NVL(x_from_period_name,sp.period_name);
117
118 SELECT MAX(ep.end_date)
119 INTO x_end_date
120 FROM pa_periods ep
121 WHERE
122 ep.period_name = NVL(x_to_period_name,ep.period_name);
123 */
124
125 END IF;
126
127 EXCEPTION
128 WHEN OTHERS THEN
129 x_err_code := SQLCODE;
130 RAISE;
131 END get_period_date_range;
132
133 -- Actuals accumulation API
134
135 PROCEDURE get_proj_txn_accum
136 (x_project_id IN NUMBER,
137 x_task_id IN NUMBER DEFAULT NULL,
138 x_period_type IN VARCHAR2 DEFAULT 'P',
139 x_from_period_name IN VARCHAR2 DEFAULT NULL,
140 x_prd_start_date IN DATE DEFAULT NULL,
141 x_prd_end_date IN DATE DEFAULT NULL,
142 x_revenue IN OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
143 x_raw_cost IN OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
144 x_burdened_cost IN OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
145 x_quantity IN OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
146 x_labor_hours IN OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
147 x_billable_raw_cost IN OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
148 x_billable_burdened_cost IN OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
149 x_billable_quantity IN OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
150 x_billable_labor_hours IN OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
151 x_cmt_raw_cost IN OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
152 x_cmt_burdened_cost IN OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
153 x_unit_of_measure IN OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
154 x_err_stage IN OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
155 x_err_code IN OUT NOCOPY NUMBER) --File.Sql.39 bug 4440895
156 IS
157 /* Done changes for bug 1631100.
158 1. Replaced pa_periods with pa_periods_all with join with pa_implementations.
159 2. Removed nvl for start and end date so that index can be used
160 */
161
162 -- Modified for bug 4390421
163 -- CURSOR seltxnaccums_p(x_prd_start_date IN DATE, x_prd_end_date IN DATE) IS
164 CURSOR seltxnaccums_p IS
165 SELECT
166 tot_revenue,
167 tot_raw_cost,
168 tot_burdened_cost,
169 tot_quantity,
170 tot_labor_hours,
171 tot_billable_raw_cost,
172 tot_billable_burdened_cost,
173 tot_billable_quantity,
174 tot_billable_labor_hours,
175 tot_cmt_raw_cost,
176 tot_cmt_burdened_cost,
177 unit_of_measure
178 FROM
179 pa_txn_accum pta /*, commented for bug 4390421
180 pa_periods_all pp,
181 pa_implementations imp */
182 WHERE
183 x_period_type = 'P'
184 AND pta.project_id = x_project_id
185 AND (
186 (x_task_id IS NULL) --- project level numbers
187 OR
188 (pta.task_id IN
189 (SELECT
190 task_id
191 FROM
192 pa_tasks
193 CONNECT BY PRIOR task_id = parent_task_id
194 START WITH task_id = x_task_id
195 )
196 )
197 )
198 AND EXISTS
199 ( SELECT 'Yes' FROM PA_TXN_ACCUM_DETAILS PTAD
200 WHERE
201 PTA.TXN_ACCUM_ID = PTAD.TXN_ACCUM_ID
202 )
203 AND pta.pa_period = x_from_period_name ;
204 /* Commented for bug 4390421
205 AND nvl(imp.org_id,-1) = nvl(pp.org_id,-1)
206 AND pp.period_name = pta.pa_period
207 AND pp.start_date BETWEEN
208 x_prd_start_date AND -- Bug 1631100 Removed nvl
209 x_prd_end_date;
210 */
214 */
211 /* Made changes for bug 1631100. For performance improvement added pa_periods table so that
212 index on project_id , pa_period gets effectively used.
213 For consistency purpose removed nvl for start date/end date. Refer bugdb for more details
215
216 -- Modified for bug 4390421
217 -- CURSOR seltxnaccums_g(x_prd_start_date IN DATE, x_prd_end_date IN DATE) IS
218 CURSOR seltxnaccums_g IS
219 SELECT
220 tot_revenue,
221 tot_raw_cost,
222 tot_burdened_cost,
223 tot_quantity,
224 tot_labor_hours,
225 tot_billable_raw_cost,
226 tot_billable_burdened_cost,
227 tot_billable_quantity,
228 tot_billable_labor_hours,
229 tot_cmt_raw_cost,
230 tot_cmt_burdened_cost,
231 unit_of_measure
232 FROM
233 /* commented for bug 4390421
234 pa_implementations imp,
235 gl_period_statuses glp,
236 pa_periods_all pp, Commented for bug 2922974 Added for bug 1631100 performance tuning */
237 pa_txn_accum pta
238 WHERE
239 x_period_type = 'G'
240 AND pta.project_id = x_project_id
241 AND (
242 (x_task_id IS NULL) --- project level numbers
243 OR
244 (pta.task_id IN
245 (SELECT
246 task_id
247 FROM
248 pa_tasks
249 CONNECT BY PRIOR task_id = parent_task_id
250 START WITH task_id = x_task_id
251 )
252 )
253 )
254 AND EXISTS
255 ( SELECT 'Yes' FROM PA_TXN_ACCUM_DETAILS PTAD
256 WHERE
257 PTA.TXN_ACCUM_ID = PTAD.TXN_ACCUM_ID
258 )
259 AND pta.gl_period = x_from_period_name ; -- Added for bug 4390421
260 /* AND pp.gl_period_name = glp.period_name Commented for bug 2922974 Added for bug 1631100 performance tuning
261 AND pp.period_name = pta.pa_period Commented for bug 2922974 Added for bug 1631100 performance tuning
262 AND nvl(pp.org_id, -1) = nvl(imp.org_id, -1) Commented for bug 2922974 Added for bug 1631100 performance tuning
263 -- Commented for bug 4390421
264 AND glp.period_name = pta.gl_period
265 AND glp.set_of_books_id = imp.set_of_books_id
266 AND glp.application_id = pa_period_process_pkg.application_id
267 AND glp.adjustment_period_flag = 'N'
268 AND glp.start_date BETWEEN
269 x_prd_start_date AND Bug 1631100 removed nvl
270 x_prd_end_date;
271 */
272 txnaccumrec_p seltxnaccums_p%ROWTYPE;
273 txnaccumrec_g seltxnaccums_g%ROWTYPE;
274 is_uom_unique BOOLEAN;
275
276 -- Added for bug 4390421
277 P_DEBUG_MODE varchar2(1) :=NVL(FND_PROFILE.VALUE('PA_DEBUG_MODE'),'N');
278
279 BEGIN
280 x_err_code := 0;
281 x_err_stage := 'Getting the Project Txn Accumlation';
282
283 -- Added for bug 4390421
284 If p_debug_mode = 'Y' and pa_budget_core1.g_calling_mode = 'CONCURRENT REQUEST' then
285 fnd_file.put_line(1,x_err_stage);
286 End if;
287 -- all of the accumlation numbers are initialized in the calling
288 -- procedure
289 is_uom_unique := TRUE;
290
291
292 IF x_period_type = 'G' THEN
293
294 -- commented for bug 4390421
295 -- FOR txnaccumrec_g IN seltxnaccums_g (x_prd_start_date, x_prd_end_date) LOOP
296 FOR txnaccumrec_g IN seltxnaccums_g LOOP
297 x_revenue := x_revenue +
298 NVL(txnaccumrec_g.TOT_REVENUE,0) ;
299 x_raw_cost := x_raw_cost +
300 NVL(txnaccumrec_g.TOT_RAW_COST,0);
301 x_burdened_cost := x_burdened_cost +
302 NVL(txnaccumrec_g.TOT_BURDENED_COST,0);
303 x_quantity := x_quantity +
304 NVL(txnaccumrec_g.TOT_QUANTITY,0);
305 x_labor_hours := x_labor_hours +
306 NVL(txnaccumrec_g.TOT_LABOR_HOURS,0);
307 x_billable_raw_cost := x_billable_raw_cost +
308 NVL(txnaccumrec_g.TOT_BILLABLE_RAW_COST,0);
309 x_billable_burdened_cost := x_billable_burdened_cost +
310 NVL(txnaccumrec_g.TOT_BILLABLE_BURDENED_COST,0);
311 x_billable_quantity := x_billable_quantity +
312 NVL(txnaccumrec_g.TOT_BILLABLE_QUANTITY,0);
313 x_billable_labor_hours := x_billable_labor_hours +
314 NVL(txnaccumrec_g.TOT_BILLABLE_LABOR_HOURS,0);
315 x_cmt_raw_cost := x_cmt_raw_cost + NVL(txnaccumrec_g.TOT_CMT_RAW_COST,0) ;
316 x_cmt_burdened_cost := x_cmt_burdened_cost +
317 NVL(txnaccumrec_g.TOT_CMT_BURDENED_COST,0);
318
319 -- Process UOM
320 -- We will return UOM only if all the txn has the same UOM
321
322 IF ( is_uom_unique AND txnaccumrec_g.unit_of_measure IS NOT NULL) THEN
323 IF ( x_unit_of_measure IS NULL ) THEN
324 x_unit_of_measure := txnaccumrec_g.unit_of_measure;
325 ELSIF ( x_unit_of_measure <> txnaccumrec_g.unit_of_measure) THEN
326 is_uom_unique := FALSE;
327 x_unit_of_measure := NULL;
328 END IF;
329 END IF;
330
331 END LOOP;
332
333 END IF; /* End of x_period_type = 'G' */
334
335 IF x_period_type = 'P' THEN
336
337 -- commented for bug 4390421
338 -- FOR txnaccumrec_p IN seltxnaccums_p (x_prd_start_date, x_prd_end_date) LOOP
339 FOR txnaccumrec_p IN seltxnaccums_p LOOP
340 x_revenue := x_revenue +
341 NVL(txnaccumrec_p.TOT_REVENUE,0) ;
345 NVL(txnaccumrec_p.TOT_BURDENED_COST,0);
342 x_raw_cost := x_raw_cost +
343 NVL(txnaccumrec_p.TOT_RAW_COST,0);
344 x_burdened_cost := x_burdened_cost +
346 x_quantity := x_quantity +
347 NVL(txnaccumrec_p.TOT_QUANTITY,0);
348 x_labor_hours := x_labor_hours +
349 NVL(txnaccumrec_p.TOT_LABOR_HOURS,0);
350 x_billable_raw_cost := x_billable_raw_cost +
351 NVL(txnaccumrec_p.TOT_BILLABLE_RAW_COST,0);
352 x_billable_burdened_cost := x_billable_burdened_cost +
353 NVL(txnaccumrec_p.TOT_BILLABLE_BURDENED_COST,0);
354 x_billable_quantity := x_billable_quantity +
355 NVL(txnaccumrec_p.TOT_BILLABLE_QUANTITY,0);
356 x_billable_labor_hours := x_billable_labor_hours +
357 NVL(txnaccumrec_p.TOT_BILLABLE_LABOR_HOURS,0);
358 x_cmt_raw_cost := x_cmt_raw_cost + NVL(txnaccumrec_p.TOT_CMT_RAW_COST,0) ;
359 x_cmt_burdened_cost := x_cmt_burdened_cost +
360 NVL(txnaccumrec_p.TOT_CMT_BURDENED_COST,0);
361
362 -- Process UOM
363 -- We will return UOM only if all the txn has the same UOM
364
365 IF ( is_uom_unique AND txnaccumrec_p.unit_of_measure IS NOT NULL) THEN
366 IF ( x_unit_of_measure IS NULL ) THEN
367 x_unit_of_measure := txnaccumrec_p.unit_of_measure;
368 ELSIF ( x_unit_of_measure <> txnaccumrec_p.unit_of_measure) THEN
369 is_uom_unique := FALSE;
370 x_unit_of_measure := NULL;
371 END IF;
372 END IF;
373
374 END LOOP;
375
376 END IF; /* End of x_period_type = 'P' */
377
378 EXCEPTION
379 WHEN NO_DATA_FOUND THEN
380 NULL;
381 WHEN OTHERS THEN
382 x_err_code := SQLCODE;
383 RAISE;
384 END get_proj_txn_accum;
385
386 PROCEDURE get_proj_res_accum
387 (x_project_id IN NUMBER,
388 x_task_id IN NUMBER DEFAULT NULL,
389 x_resource_list_member_id IN NUMBER DEFAULT NULL,
390 x_period_type IN VARCHAR2 DEFAULT 'P',
391 x_from_period_name IN VARCHAR2 DEFAULT NULL,
392 x_prd_start_date IN DATE DEFAULT NULL,
393 x_prd_end_date IN DATE DEFAULT NULL,
394 x_revenue IN OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
395 x_raw_cost IN OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
396 x_burdened_cost IN OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
397 x_quantity IN OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
398 x_labor_hours IN OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
399 x_billable_raw_cost IN OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
400 x_billable_burdened_cost IN OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
401 x_billable_quantity IN OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
402 x_billable_labor_hours IN OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
403 x_cmt_raw_cost IN OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
404 x_cmt_burdened_cost IN OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
405 x_unit_of_measure IN OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
406 x_err_stage IN OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
407 x_err_code IN OUT NOCOPY NUMBER) --File.Sql.39 bug 4440895
408 IS
409 /* Modified for performance. Bug 1631100
410 Moved join for project_id and task_id to the pa_txn_accum table and joining to pa_resource
411 accum_details thru txn_accum_id.
412 replaced pa_periods with pa_periods_all. Removed nvl for start date and end date.
413 Now index on project_id and pa_period will be very effectively used on pa_txn_accum
414 */
415
416 -- Commented for bug 4390421
417 -- CURSOR selresaccums_p(x_prd_start_date IN DATE, x_prd_end_date IN DATE) IS
418 CURSOR selresaccums_p IS
419 SELECT
420 PTA.TOT_REVENUE,
421 PTA.TOT_RAW_COST,
422 PTA.TOT_BURDENED_COST,
423 PTA.TOT_QUANTITY,
424 PTA.TOT_LABOR_HOURS,
425 PTA.TOT_BILLABLE_RAW_COST,
426 PTA.TOT_BILLABLE_BURDENED_COST,
427 PTA.TOT_BILLABLE_QUANTITY,
428 PTA.TOT_BILLABLE_LABOR_HOURS,
429 PTA.TOT_CMT_RAW_COST,
430 PTA.TOT_CMT_BURDENED_COST,
431 PTA.UNIT_OF_MEASURE
432 FROM
433 PA_TXN_ACCUM PTA /*, Commented for bug 4390421
434 pa_periods_all pp,
435 pa_implementations imp */
436 WHERE PTA.PROJECT_ID = X_PROJECT_ID
437 AND (
438 (x_task_id IS NULL) --- project level numbers
439 OR
440 (PTA.TASK_ID IN
441 (SELECT
442 task_id
443 FROM
444 pa_tasks
445 CONNECT BY PRIOR task_id = parent_task_id
446 START WITH task_id = x_task_id
447 )
448 )
449 )
450 AND EXISTS
451 ( SELECT 'Yes'
452 FROM PA_RESOURCE_ACCUM_DETAILS PRAD
453 WHERE PRAD.TXN_ACCUM_ID = PTA.TXN_ACCUM_ID
454 AND PRAD.RESOURCE_LIST_MEMBER_ID IN
455 -- Modified for bug 4390421
456 ( -- Fetch both 2nd level and group level resource list member
457 SELECT PRLM.RESOURCE_LIST_MEMBER_ID
458 FROM PA_RESOURCE_LIST_MEMBERS PRLM
459 WHERE (prlm.resource_list_member_id = X_RESOURCE_LIST_MEMBER_ID
460 or
461 PRLM.PARENT_MEMBER_ID = X_RESOURCE_LIST_MEMBER_ID )
465 PRLM.RESOURCE_LIST_MEMBER_ID
462 /* Commented for bug 4390421
463 (
464 SELECT -- 2nd level resource list members
466 FROM
467 PA_RESOURCE_LIST_MEMBERS PRLM
468 WHERE
469 PRLM.PARENT_MEMBER_ID = X_RESOURCE_LIST_MEMBER_ID
470 UNION
471 SELECT -- Group level Resource list member
472 X_RESOURCE_LIST_MEMBER_ID
473 FROM
474 SYS.DUAL */
475 )
476 )
477 AND EXISTS
478 ( SELECT 'Yes' FROM PA_TXN_ACCUM_DETAILS PTAD
479 WHERE
480 PTA.TXN_ACCUM_ID = PTAD.TXN_ACCUM_ID
481 )
482 AND x_period_type = 'P'
483 AND pta.pa_period = x_from_period_name; -- Added for bug 4390421
484 /* Commented for bug 4390421
485 AND nvl(imp.org_id,-1) = nvl(pp.org_id,-1)
486 AND pp.period_name = pta.pa_period
487 AND pp.start_date BETWEEN
488 x_prd_start_date AND
489 x_prd_end_date;
490 */
491
492 /* Modified for performance. Bug 1631100
493 Moved join for project_id and task_id to the pa_txn_accum table and joining to pa_resource
494 accum_details thru txn_accum_id.
495 Added pa_periods_all table to the from clause so that index on project_id, pa_period can
496 be used.
497 removed nvl from start date/end date for consistencey purpose.
498 */
499
500 -- Commented for bug 4390421
501 -- CURSOR selresaccums_g(x_prd_start_date IN DATE, x_prd_end_date IN DATE) IS
502 CURSOR selresaccums_g IS
503 SELECT
504 PTA.TOT_REVENUE,
505 PTA.TOT_RAW_COST,
506 PTA.TOT_BURDENED_COST,
507 PTA.TOT_QUANTITY,
508 PTA.TOT_LABOR_HOURS,
509 PTA.TOT_BILLABLE_RAW_COST,
510 PTA.TOT_BILLABLE_BURDENED_COST,
511 PTA.TOT_BILLABLE_QUANTITY,
512 PTA.TOT_BILLABLE_LABOR_HOURS,
513 PTA.TOT_CMT_RAW_COST,
514 PTA.TOT_CMT_BURDENED_COST,
515 PTA.UNIT_OF_MEASURE
516 FROM
517 /* Commented for bug 4390421
518 pa_implementations imp,
519 gl_period_statuses glp, */
520 PA_TXN_ACCUM PTA
521 /* PA_PERIODS_ALL PP commented for bug 2922974 */
522 WHERE PTA.PROJECT_ID = X_PROJECT_ID
523 AND (
524 (x_task_id IS NULL) --- project level numbers
525 OR
526 (PTA.TASK_ID IN
527 (SELECT
528 task_id
529 FROM
530 pa_tasks
531 CONNECT BY PRIOR task_id = parent_task_id
532 START WITH task_id = x_task_id
533 )
534 )
535 )
536 AND EXISTS (SELECT 'Yes'
537 FROM PA_RESOURCE_ACCUM_DETAILS PRAD
538 WHERE PRAD.TXN_ACCUM_ID = PTA.TXN_ACCUM_ID
539 AND PRAD.RESOURCE_LIST_MEMBER_ID IN
540 -- Modified for bug 4390421
541 ( -- Fetch both 2nd level and group level resource list member
542 SELECT PRLM.RESOURCE_LIST_MEMBER_ID
543 FROM PA_RESOURCE_LIST_MEMBERS PRLM
544 WHERE (prlm.resource_list_member_id = X_RESOURCE_LIST_MEMBER_ID
545 or
546 PRLM.PARENT_MEMBER_ID = X_RESOURCE_LIST_MEMBER_ID )
547 /*
548 (
549 SELECT -- 2nd level resource list members
550 PRLM.RESOURCE_LIST_MEMBER_ID
551 FROM
552 PA_RESOURCE_LIST_MEMBERS PRLM
553 WHERE
554 PRLM.PARENT_MEMBER_ID = X_RESOURCE_LIST_MEMBER_ID
555 UNION
556 SELECT -- Group level Resource list member
557 X_RESOURCE_LIST_MEMBER_ID
558 FROM
559 SYS.DUAL */
560 )
561 )
562 AND EXISTS
563 ( SELECT 'Yes' FROM PA_TXN_ACCUM_DETAILS PTAD
564 WHERE
565 PTA.TXN_ACCUM_ID = PTAD.TXN_ACCUM_ID
566 )
567 /* AND pp.gl_period_name = glp.period_name commented for bug 2922974 Added for bug 1631100 performance tuning
568 AND pp.period_name = pta.pa_period commented for bug 2922974 Added for bug 1631100 performance tuning
569 AND nvl(pp.org_id, -1) = nvl(imp.org_id, -1) commented for bug 2922974 Added for bug 1631100 performance tuning */
570 AND x_period_type = 'G'
571 AND pta.gl_period = x_from_period_name;
572 /* Commented for bug 4390421
573 AND glp.set_of_books_id = imp.set_of_books_id
574 AND glp.application_id = pa_period_process_pkg.application_id
575 AND glp.adjustment_period_flag = 'N'
576 AND glp.start_date BETWEEN
577 x_prd_start_date AND
578 x_prd_end_date; */
579
580 resaccumrec_p selresaccums_p%ROWTYPE;
581 resaccumrec_g selresaccums_g%ROWTYPE;
582 is_uom_unique BOOLEAN;
583
584 -- Added for bug 4390421
585 P_DEBUG_MODE varchar2(1) :=NVL(FND_PROFILE.VALUE('PA_DEBUG_MODE'),'N');
586
587 BEGIN
588 x_err_code := 0;
589 x_err_stage := 'Getting the Project Res Accumlation';
590
591 -- Added for bug 4390421
592 If p_debug_mode = 'Y' and pa_budget_core1.g_calling_mode = 'CONCURRENT REQUEST' then
593 fnd_file.put_line(1,x_err_stage);
594 End if;
595
596 -- all of the accumlation numbers are initialized in the calling
597 -- procedure
598 is_uom_unique := TRUE;
599
603 -- FOR resaccumrec_g IN selresaccums_g (x_prd_start_date, x_prd_end_date) LOOP
600 IF x_period_type = 'G' THEN
601
602 -- Commented for bug 4390421
604 FOR resaccumrec_g IN selresaccums_g LOOP
605
606 x_revenue := x_revenue +
607 NVL(resaccumrec_g.TOT_REVENUE,0);
608 x_raw_cost := x_raw_cost +
609 NVL(resaccumrec_g.TOT_RAW_COST,0);
610 x_burdened_cost := x_burdened_cost +
611 NVL(resaccumrec_g.TOT_BURDENED_COST,0);
612 x_quantity := x_quantity +
613 NVL(resaccumrec_g.TOT_QUANTITY,0);
614 x_labor_hours := x_labor_hours +
615 NVL(resaccumrec_g.TOT_LABOR_HOURS,0);
616 x_billable_raw_cost := x_billable_raw_cost +
617 NVL(resaccumrec_g.TOT_BILLABLE_RAW_COST,0);
618 x_billable_burdened_cost := x_billable_burdened_cost +
619 NVL(resaccumrec_g.TOT_BILLABLE_BURDENED_COST,0);
620 x_billable_quantity := x_billable_quantity +
621 NVL(resaccumrec_g.TOT_BILLABLE_QUANTITY,0);
622 x_billable_labor_hours := x_billable_labor_hours +
623 NVL(resaccumrec_g.TOT_BILLABLE_LABOR_HOURS,0);
624 x_cmt_raw_cost := x_cmt_raw_cost + NVL(resaccumrec_g.TOT_CMT_RAW_COST,0) ;
625 x_cmt_burdened_cost := x_cmt_burdened_cost +
626 NVL(resaccumrec_g.TOT_CMT_BURDENED_COST,0);
627 -- Process UOM
628 -- We will return UOM only if all the txn has the same UOM
629
630 IF ( is_uom_unique AND resaccumrec_g.unit_of_measure IS NOT NULL) THEN
631 IF ( x_unit_of_measure IS NULL ) THEN
632 x_unit_of_measure := resaccumrec_g.unit_of_measure;
633 ELSIF ( x_unit_of_measure <> resaccumrec_g.unit_of_measure) THEN
634 is_uom_unique := FALSE;
635 x_unit_of_measure := NULL;
636 END IF;
637 END IF;
638
639 END LOOP;
640
641 END IF; /* End of x_period_type = 'G' */
642
643
644 IF x_period_type = 'P' THEN
645
646 -- Commented for bug 4390421
647 -- FOR resaccumrec_p IN selresaccums_p (x_prd_start_date, x_prd_end_date) LOOP
648 FOR resaccumrec_p IN selresaccums_p LOOP
649 x_revenue := x_revenue +
650 NVL(resaccumrec_p.TOT_REVENUE,0);
651 x_raw_cost := x_raw_cost +
652 NVL(resaccumrec_p.TOT_RAW_COST,0);
653 x_burdened_cost := x_burdened_cost +
654 NVL(resaccumrec_p.TOT_BURDENED_COST,0);
655 x_quantity := x_quantity +
656 NVL(resaccumrec_p.TOT_QUANTITY,0);
657 x_labor_hours := x_labor_hours +
658 NVL(resaccumrec_p.TOT_LABOR_HOURS,0);
659 x_billable_raw_cost := x_billable_raw_cost +
660 NVL(resaccumrec_p.TOT_BILLABLE_RAW_COST,0);
661 x_billable_burdened_cost := x_billable_burdened_cost +
662 NVL(resaccumrec_p.TOT_BILLABLE_BURDENED_COST,0);
663 x_billable_quantity := x_billable_quantity +
664 NVL(resaccumrec_p.TOT_BILLABLE_QUANTITY,0);
665 x_billable_labor_hours := x_billable_labor_hours +
666 NVL(resaccumrec_p.TOT_BILLABLE_LABOR_HOURS,0);
667 x_cmt_raw_cost := x_cmt_raw_cost + NVL(resaccumrec_p.TOT_CMT_RAW_COST,0) ;
668 x_cmt_burdened_cost := x_cmt_burdened_cost +
669 NVL(resaccumrec_p.TOT_CMT_BURDENED_COST,0);
670 -- Process UOM
671 -- We will return UOM only if all the txn has the same UOM
672
673 IF ( is_uom_unique AND resaccumrec_p.unit_of_measure IS NOT NULL) THEN
674 IF ( x_unit_of_measure IS NULL ) THEN
675 x_unit_of_measure := resaccumrec_p.unit_of_measure;
676 ELSIF ( x_unit_of_measure <> resaccumrec_p.unit_of_measure) THEN
677 is_uom_unique := FALSE;
678 x_unit_of_measure := NULL;
679 END IF;
680 END IF;
681
682 END LOOP;
683
684 END IF; /* End of x_period_type = 'P' */
685
686 EXCEPTION
687 WHEN NO_DATA_FOUND THEN
688 NULL;
689 WHEN OTHERS THEN
690 x_err_code := SQLCODE;
691 RAISE;
692 END get_proj_res_accum;
693
694
695 PROCEDURE get_proj_accum_actuals
696 (x_project_id IN NUMBER,
697 x_task_id IN NUMBER DEFAULT NULL,
698 x_resource_list_member_id IN NUMBER DEFAULT NULL,
699 x_period_type IN VARCHAR2 DEFAULT 'P',
700 x_from_period_name IN VARCHAR2 DEFAULT NULL,
701 x_prd_start_date IN DATE DEFAULT NULL,
702 x_prd_end_date IN DATE DEFAULT NULL,
703 x_revenue IN OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
704 x_raw_cost IN OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
705 x_burdened_cost IN OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
706 x_quantity IN OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
707 x_labor_hours IN OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
708 x_billable_raw_cost IN OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
709 x_billable_burdened_cost IN OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
710 x_billable_quantity IN OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
711 x_billable_labor_hours IN OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
712 x_cmt_raw_cost IN OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
713 x_cmt_burdened_cost IN OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
714 x_unit_of_measure IN OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
715 x_err_stage IN OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
716 x_err_code IN OUT NOCOPY NUMBER) --File.Sql.39 bug 4440895
717 IS
718 -- Added for bug 4390421
719 P_DEBUG_MODE varchar2(1) :=NVL(FND_PROFILE.VALUE('PA_DEBUG_MODE'),'N');
723 x_err_stage := 'Getting the Project Accumlation';
720
721 BEGIN
722 x_err_code := 0;
724
725 -- Added for bug 4390421
726 If p_debug_mode = 'Y' and pa_budget_core1.g_calling_mode = 'CONCURRENT REQUEST' then
727 fnd_file.put_line(1,x_err_stage);
728 End if;
729
730 x_revenue := 0;
731 x_raw_cost := 0;
732 x_burdened_cost := 0;
733 x_quantity := 0;
734 x_labor_hours := 0;
735 x_billable_raw_cost := 0;
736 x_billable_burdened_cost := 0;
737 x_billable_quantity := 0;
738 x_billable_labor_hours := 0;
739 x_cmt_raw_cost := 0;
740 x_cmt_burdened_cost := 0;
741 x_unit_of_measure := NULL;
742
743 IF ( x_resource_list_member_id IS NULL ) THEN
744 -- Call the txn accum
745 get_proj_txn_accum
746 (x_project_id,
747 x_task_id,
748 x_period_type,
749 x_from_period_name,
750 x_prd_start_date,
751 x_prd_end_date,
752 x_revenue,
753 x_raw_cost,
754 x_burdened_cost,
755 x_quantity,
756 x_labor_hours,
757 x_billable_raw_cost,
758 x_billable_burdened_cost,
759 x_billable_quantity,
760 x_billable_labor_hours,
761 x_cmt_raw_cost,
762 x_cmt_burdened_cost,
763 x_unit_of_measure,
764 x_err_stage,
765 x_err_code);
766 ELSE
767 -- Call the resource accum
768 get_proj_res_accum
769 (x_project_id,
770 x_task_id,
771 x_resource_list_member_id,
772 x_period_type,
773 x_from_period_name,
774 x_prd_start_date,
775 x_prd_end_date,
776 x_revenue,
777 x_raw_cost,
778 x_burdened_cost,
779 x_quantity,
780 x_labor_hours,
781 x_billable_raw_cost,
782 x_billable_burdened_cost,
783 x_billable_quantity,
784 x_billable_labor_hours,
785 x_cmt_raw_cost,
786 x_cmt_burdened_cost,
787 x_unit_of_measure,
788 x_err_stage,
789 x_err_code);
790 END IF;
791
792 EXCEPTION
793 WHEN NO_DATA_FOUND THEN
794 NULL;
795 WHEN OTHERS THEN
796 x_err_code := SQLCODE;
797 RAISE;
798 END get_proj_accum_actuals;
799
800 PROCEDURE get_proj_accum_budgets
801 (x_project_id IN NUMBER,
802 x_task_id IN NUMBER DEFAULT NULL,
803 x_resource_list_member_id IN NUMBER DEFAULT NULL,
804 x_period_type IN VARCHAR2 DEFAULT 'P',
805 x_from_period_name IN VARCHAR2 DEFAULT NULL,
806 x_to_period_name IN VARCHAR2 DEFAULT NULL,
807 x_budget_type_code IN VARCHAR2 DEFAULT NULL,
808 x_base_raw_cost IN OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
809 x_base_burdened_cost IN OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
810 x_base_revenue IN OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
811 x_base_quantity IN OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
812 x_base_labor_quantity IN OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
813 x_unit_of_measure IN OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
814 x_orig_raw_cost IN OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
815 x_orig_burdened_cost IN OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
816 x_orig_revenue IN OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
817 x_orig_quantity IN OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
818 x_orig_labor_quantity IN OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
819 x_err_stage IN OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
820 x_err_code IN OUT NOCOPY NUMBER) --File.Sql.39 bug 4440895
821 IS
822
823 --- Transaction Cursor (Project- and Task-Level Amounts)
824 CURSOR seltxnbudget(x_start_date IN DATE, x_end_date IN DATE) IS
825 SELECT
826 bpv.base_raw_cost,
827 bpv.base_burdened_cost,
828 bpv.base_revenue,
829 bpv.base_quantity,
830 bpv.base_labor_quantity,
831 bpv.unit_of_measure,
832 bpv.orig_raw_cost,
833 bpv.orig_burdened_cost,
834 bpv.orig_revenue,
835 bpv.orig_quantity,
836 bpv.orig_labor_quantity
837 FROM
838 pa_budget_by_pa_period_v bpv,
839 pa_periods pp
840 WHERE
841 bpv.project_id = x_project_id
842 AND (
843 (x_task_id IS NULL) --- project level numbers
844 OR
845 (bpv.task_id IN
846 (SELECT
847 t.task_id
848 FROM
849 pa_tasks t
850 CONNECT BY PRIOR t.task_id = t.parent_task_id
851 START WITH t.task_id = x_task_id
852 )
853 )
854 )
855 AND x_period_type = 'P'
856 AND pp.period_name = bpv.pa_period
857 AND pp.start_date BETWEEN
858 NVL(x_start_date,pp.start_date) AND NVL(x_end_date,pp.end_date)
859 AND bpv.budget_type_code = NVL(x_budget_type_code,bpv.budget_type_code)
860 UNION ALL
861 SELECT
862 bpv.base_raw_cost,
863 bpv.base_burdened_cost,
864 bpv.base_revenue,
865 bpv.base_quantity,
866 bpv.base_labor_quantity,
867 bpv.unit_of_measure,
868 bpv.orig_raw_cost,
869 bpv.orig_burdened_cost,
873 FROM
870 bpv.orig_revenue,
871 bpv.orig_quantity,
872 bpv.orig_labor_quantity
874 pa_budget_by_pa_period_v bpv,
875 gl_period_statuses glp,
876 pa_implementations imp
877 WHERE
878 bpv.project_id = x_project_id
879 AND (
880 (x_task_id IS NULL) --- project level numbers
881 OR
882 (bpv.task_id IN
883 (SELECT
884 t.task_id
885 FROM
886 pa_tasks t
887 CONNECT BY PRIOR t.task_id = t.parent_task_id
888 START WITH t.task_id = x_task_id
889 )
890 )
891 )
892 AND x_period_type = 'G'
893 AND glp.period_name = bpv.gl_period_name
894 AND glp.set_of_books_id = imp.set_of_books_id
895 AND glp.application_id = pa_period_process_pkg.application_id
896 AND glp.adjustment_period_flag = 'N'
897 AND glp.start_date BETWEEN
898 NVL(x_start_date,glp.start_date) AND NVL(x_end_date,glp.end_date)
899 AND bpv.budget_type_code = NVL(x_budget_type_code,bpv.budget_type_code) ;
900
901 txnbudgetrec seltxnbudget%ROWTYPE;
902 is_txn_uom_unique BOOLEAN;
903
904
905 --- Resource Cursor (Project-, Task- and Resource-Level Amounts)
906 CURSOR selresbudget(x_start_date IN DATE, x_end_date IN DATE) IS
907 SELECT
908 bpv.base_raw_cost,
909 bpv.base_burdened_cost,
910 bpv.base_revenue,
911 bpv.base_quantity,
912 bpv.base_labor_quantity,
913 bpv.unit_of_measure,
914 bpv.orig_raw_cost,
915 bpv.orig_burdened_cost,
916 bpv.orig_revenue,
917 bpv.orig_quantity,
918 bpv.orig_labor_quantity
919 FROM
920 pa_budget_by_pa_period_v bpv,
921 pa_periods pp
922 WHERE
923 bpv.project_id = x_project_id
924 AND (
925 (x_task_id IS NULL) --- project level numbers
926 OR
927 (bpv.task_id IN
928 (SELECT
929 t.task_id
930 FROM
931 pa_tasks t
932 CONNECT BY PRIOR t.task_id = t.parent_task_id
933 START WITH t.task_id = x_task_id)
934 )
935 )
936 AND bpv.resource_list_member_id IN
937 (
938 SELECT -- 2nd level resource list members
939 rlm.resource_list_member_id
940 FROM
941 pa_resource_list_members rlm
942 WHERE
943 rlm.parent_member_id = x_resource_list_member_id
944 UNION
945 SELECT -- Group level Resource list member
946 x_resource_list_member_id
947 FROM
948 SYS.DUAL
949 )
950 AND x_period_type = 'P'
951 AND pp.period_name = bpv.pa_period
952 AND pp.start_date BETWEEN
953 NVL(x_start_date,pp.start_date) AND NVL(x_end_date,pp.end_date)
954 AND bpv.budget_type_code = NVL(x_budget_type_code,bpv.budget_type_code)
955 UNION ALL
956 SELECT
957 bpv.base_raw_cost,
958 bpv.base_burdened_cost,
959 bpv.base_revenue,
960 bpv.base_quantity,
961 bpv.base_labor_quantity,
962 bpv.unit_of_measure,
963 bpv.orig_raw_cost,
964 bpv.orig_burdened_cost,
965 bpv.orig_revenue,
966 bpv.orig_quantity,
967 bpv.orig_labor_quantity
968 FROM
969 pa_budget_by_pa_period_v bpv,
970 gl_period_statuses glp,
971 pa_implementations imp
972 WHERE
973 bpv.project_id = x_project_id
974 AND (
975 (x_task_id IS NULL) --- project level numbers
976 OR
977 (bpv.task_id IN
978 (SELECT
979 t.task_id
980 FROM
981 pa_tasks t
982 CONNECT BY PRIOR t.task_id = t.parent_task_id
983 START WITH t.task_id = x_task_id)
984 )
985 )
986 AND bpv.resource_list_member_id IN
987 (
988 SELECT -- 2nd level resource list members
989 rlm.resource_list_member_id
990 FROM
991 pa_resource_list_members rlm
992 WHERE
993 rlm.parent_member_id = x_resource_list_member_id
994 UNION
995 SELECT -- Group level Resource list member
996 x_resource_list_member_id
997 FROM
998 SYS.DUAL
999 )
1000 AND x_period_type = 'G'
1001 AND glp.period_name = bpv.gl_period_name
1002 AND glp.set_of_books_id = imp.set_of_books_id
1003 AND glp.application_id = pa_period_process_pkg.application_id
1004 AND glp.adjustment_period_flag = 'N'
1005 AND glp.start_date BETWEEN
1006 NVL(x_start_date,glp.start_date) AND NVL(x_end_date,glp.end_date)
1007 AND bpv.budget_type_code = NVL(x_budget_type_code,bpv.budget_type_code) ;
1008
1009 resbudgetrec selresbudget%ROWTYPE;
1010 is_res_uom_unique BOOLEAN;
1011 x_start_date DATE;
1012 x_end_date DATE;
1013
1014
1015 BEGIN
1016 x_err_code := 0;
1017 x_err_stage := 'Getting the Project Accumulation Budgets';
1018
1019 x_base_raw_cost := 0;
1020 x_base_burdened_cost := 0;
1021 x_base_revenue := 0;
1022 x_base_quantity := 0;
1023 x_base_labor_quantity := 0;
1024 x_unit_of_measure := NULL;
1025 x_orig_raw_cost := 0;
1026 x_orig_burdened_cost := 0;
1027 x_orig_revenue := 0;
1028 x_orig_quantity := 0;
1029 x_orig_labor_quantity := 0;
1030
1031 -- Get period start and end date
1032
1033 get_period_date_range
1034 (x_period_type,
1035 x_from_period_name,
1036 x_to_period_name,
1037 x_start_date,
1038 x_end_date,
1039 x_err_stage,
1040 x_err_code);
1041
1042 IF ( x_resource_list_member_id IS NULL ) THEN
1043 -- Process Transaction Cursor
1044 x_err_stage := 'Getting the Project Txn Accumulation Budgets';
1045 is_txn_uom_unique := TRUE;
1046
1047 FOR txnbudgetrec IN seltxnbudget(x_start_date, x_end_date) LOOP
1048
1049 x_base_raw_cost := x_base_raw_cost +
1050 NVL(txnbudgetrec.BASE_RAW_COST, 0);
1051
1052 x_base_burdened_cost := x_base_burdened_cost +
1053 NVL(txnbudgetrec.BASE_BURDENED_COST, 0);
1054
1055 x_base_revenue := x_base_revenue +
1056 NVL(txnbudgetrec.BASE_REVENUE, 0);
1057
1058 x_base_quantity := x_base_quantity +
1059 NVL(txnbudgetrec.BASE_QUANTITY, 0);
1060
1061 x_base_labor_quantity := x_base_labor_quantity +
1062 NVL(txnbudgetrec.BASE_LABOR_QUANTITY, 0);
1063
1064 x_orig_raw_cost := x_orig_raw_cost +
1065 NVL(txnbudgetrec.ORIG_RAW_COST, 0);
1066
1067 x_orig_burdened_cost := x_orig_burdened_cost +
1068 NVL(txnbudgetrec.ORIG_BURDENED_COST, 0);
1069
1070 x_orig_revenue := x_orig_revenue +
1071 NVL(txnbudgetrec.ORIG_REVENUE, 0);
1072
1073 x_orig_quantity := x_orig_quantity +
1074 NVL(txnbudgetrec.ORIG_QUANTITY, 0);
1075
1076 x_orig_labor_quantity := x_orig_labor_quantity +
1077 NVL(txnbudgetrec.ORIG_LABOR_QUANTITY, 0);
1078
1079 -- Process UOM
1080 -- We will return UOM only if all the txn has the same UOM
1081
1082 IF ( is_txn_uom_unique AND txnbudgetrec.unit_of_measure IS NOT NULL) THEN
1083 IF ( x_unit_of_measure IS NULL ) THEN
1084 x_unit_of_measure := txnbudgetrec.unit_of_measure;
1085 ELSIF ( x_unit_of_measure <> txnbudgetrec.unit_of_measure) THEN
1086 is_txn_uom_unique := FALSE;
1087 x_unit_of_measure := NULL;
1088 END IF;
1089 END IF;
1090
1091
1092 END LOOP;
1093
1094 ELSE
1095 -- Process Resource Cursor
1096 x_err_stage := 'Getting the Project Res Accumulation Budgets';
1097 is_res_uom_unique := TRUE;
1098
1099 FOR resbudgetrec IN selresbudget(x_start_date, x_end_date) LOOP
1100
1101 x_base_raw_cost := x_base_raw_cost +
1102 NVL(resbudgetrec.BASE_RAW_COST, 0);
1103
1104 x_base_burdened_cost := x_base_burdened_cost +
1105 NVL(resbudgetrec.BASE_BURDENED_COST, 0);
1106
1107 x_base_revenue := x_base_revenue +
1108 NVL(resbudgetrec.BASE_REVENUE, 0);
1109
1110 x_base_quantity := x_base_quantity +
1111 NVL(resbudgetrec.BASE_QUANTITY, 0);
1112
1113 x_base_labor_quantity := x_base_labor_quantity +
1114 NVL(resbudgetrec.BASE_LABOR_QUANTITY, 0);
1115
1116 x_orig_raw_cost := x_orig_raw_cost +
1117 NVL(resbudgetrec.ORIG_RAW_COST, 0);
1118
1119 x_orig_burdened_cost := x_orig_burdened_cost +
1120 NVL(resbudgetrec.ORIG_BURDENED_COST, 0);
1121
1122 x_orig_revenue := x_orig_revenue +
1123 NVL(resbudgetrec.ORIG_REVENUE, 0);
1124
1125 x_orig_quantity := x_orig_quantity +
1126 NVL(resbudgetrec.ORIG_QUANTITY, 0);
1127
1128 x_orig_labor_quantity := x_orig_labor_quantity +
1129 NVL(resbudgetrec.ORIG_LABOR_QUANTITY, 0);
1130
1131 -- Process UOM
1132 -- We will return UOM only if all the res has the same UOM
1133
1134 IF ( is_res_uom_unique AND resbudgetrec.unit_of_measure IS NOT NULL) THEN
1135 IF ( x_unit_of_measure IS NULL ) THEN
1136 x_unit_of_measure := resbudgetrec.unit_of_measure;
1137 ELSIF ( x_unit_of_measure <> resbudgetrec.unit_of_measure) THEN
1138 is_res_uom_unique := FALSE;
1139 x_unit_of_measure := NULL;
1140 END IF;
1141 END IF;
1142
1143
1144 END LOOP;
1145
1146 END IF;
1147
1148
1149 EXCEPTION
1150 WHEN NO_DATA_FOUND THEN
1151 NULL;
1152 WHEN OTHERS THEN
1153 x_err_code := SQLCODE;
1154 RAISE;
1155
1156 END get_proj_accum_budgets;
1157
1158
1159 END PA_ACCUM_API;