1 PACKAGE body PA_ACCUM_UTILS AS
2 /* $Header: PAACUTIB.pls 120.2 2010/10/01 15:11:33 kmaddi ship $ */
3
4 -- Proj_level_record - This verifies for the existence of the Project level
5 -- record (Task id = 0 and Resource list member id = 0)
6 -- If available, returns the Project_Accum_id else
7 -- creates a record in PA_PROJECT_ACCUM_HEADERS and
8 -- returns the Project_Accum_Id
9
10
11
12
13
14 Procedure Proj_level_record (x_project_id In Number,
15 x_current_pa_period In Varchar2,
16 x_current_gl_period In Varchar2,
17 x_impl_Option In Varchar2,
18 x_accum_id Out NOCOPY Number, --File.Sql.39 bug 4440895
19 x_prev_accum_period Out NOCOPY Varchar2, --File.Sql.39 bug 4440895
20 x_err_stack In Out NOCOPY Varchar2, --File.Sql.39 bug 4440895
21 x_err_stage In Out NOCOPY Varchar2, --File.Sql.39 bug 4440895
22 x_err_code In Out NOCOPY Number ) IS --File.Sql.39 bug 4440895
23
24
25 V_accum_id Number := 0;
26 V_prev_accum_period Varchar2(30);
27 v_current_period Varchar2(30);
28 V_Old_Stack Varchar2(630);
29 P_DEBUG_MODE varchar2(1) := NVL(FND_PROFILE.value('PA_DEBUG_MODE'), 'N'); /* Added Debug Profile Option variable initialization for bug#2674619 */
30 Begin
31 V_Old_Stack := x_err_stack;
32 x_err_stack :=
33 x_err_stack||'->PA_ACCUM_UTILS.Proj_level_record';
34
35 IF P_DEBUG_MODE = 'Y' THEN /* Added Debug Profile Option Check for bug#2674619 */
36 pa_debug.debug('Proj_level_record: ' || x_err_stack);
37 END IF;
38
39 -- Check whether Implementation option is PA or GL , based on which the
40 -- current period is set
41 If x_impl_option = 'PA' then
42 v_current_period := X_current_pa_period;
43 Else
44 v_current_period := X_current_gl_period;
45 End If;
46
47 -- Select the Project level record. The project level record has
48 -- Task id, resource list id ,resource id and resource list member id = 0
49 SELECT Project_Accum_Id,
50 Accum_Period
51 INTO
52 V_Accum_id,
53 V_prev_accum_period
54 FROM
55 PA_PROJECT_ACCUM_HEADERS
56 WHERE Project_id = X_project_id
57 AND Task_id = 0
58 AND Resource_List_id = 0
59 AND Resource_List_member_id = 0
60 AND Resource_id = 0 ;
61
62 X_accum_id := V_Accum_id;
63 X_Prev_Accum_period := v_prev_accum_period;
64
65 -- Restore the old x_err_stack;
66
67 x_err_stack := V_Old_Stack;
68
69 EXCEPTION
70 -- If there is no Project level record, then create the same.
71
72 WHEN NO_DATA_FOUND THEN
73
74 IF P_DEBUG_MODE = 'Y' THEN /* Added Debug Profile Option Check for bug#2674619 */
75 pa_debug.debug('Proj_level_record: ' || 'Creating Project Level Header Record');
76 END IF;
77
78 SELECT PA_PROJECT_ACCUM_HEADERS_S.Nextval
79 INTO V_accum_id
80 FROM Dual;
81 INSERT INTO PA_PROJECT_ACCUM_HEADERS
82 (PROJECT_ACCUM_ID,
83 PROJECT_ID,
84 TASK_ID,
85 ACCUM_PERIOD,
86 RESOURCE_ID,
87 RESOURCE_LIST_ID,
88 RESOURCE_LIST_MEMBER_ID,
89 RESOURCE_LIST_ASSIGNMENT_ID,
90 LAST_UPDATE_DATE,
91 LAST_UPDATED_BY,
92 REQUEST_ID,
93 CREATION_DATE,
94 CREATED_BY,
95 LAST_UPDATE_LOGIN )
96 VALUES (V_Accum_id,
97 X_project_id,
98 0,
99 v_current_period,
100 0,
101 0,
102 0,
103 0,
104 trunc(sysdate),
105 pa_proj_accum_main.x_last_updated_by,
106 pa_proj_accum_main.x_request_id,
107 trunc(sysdate),
108 pa_proj_accum_main.x_created_by,
109 pa_proj_accum_main.x_last_update_login );
110 -- Create Actuals record for the Project level record
111
112 IF P_DEBUG_MODE = 'Y' THEN /* Added Debug Profile Option Check for bug#2674619 */
113 pa_debug.debug('Proj_level_record: ' || 'Creating Project Level Header record for Actuals');
114 END IF;
115
116 INSERT INTO PA_PROJECT_ACCUM_ACTUALS (
117 PROJECT_ACCUM_ID,RAW_COST_ITD,RAW_COST_YTD,RAW_COST_PP,RAW_COST_PTD,
118 BILLABLE_RAW_COST_ITD,BILLABLE_RAW_COST_YTD,BILLABLE_RAW_COST_PP,
119 BILLABLE_RAW_COST_PTD,BURDENED_COST_ITD,BURDENED_COST_YTD,
120 BURDENED_COST_PP,BURDENED_COST_PTD,BILLABLE_BURDENED_COST_ITD,
121 BILLABLE_BURDENED_COST_YTD,BILLABLE_BURDENED_COST_PP,
122 BILLABLE_BURDENED_COST_PTD,QUANTITY_ITD,QUANTITY_YTD,QUANTITY_PP,
123 QUANTITY_PTD,LABOR_HOURS_ITD,LABOR_HOURS_YTD,LABOR_HOURS_PP,
124 LABOR_HOURS_PTD,BILLABLE_QUANTITY_ITD,BILLABLE_QUANTITY_YTD,
125 BILLABLE_QUANTITY_PP,BILLABLE_QUANTITY_PTD,
126 BILLABLE_LABOR_HOURS_ITD,BILLABLE_LABOR_HOURS_YTD,
127 BILLABLE_LABOR_HOURS_PP,BILLABLE_LABOR_HOURS_PTD,REVENUE_ITD,
128 REVENUE_YTD,REVENUE_PP,REVENUE_PTD,TXN_UNIT_OF_MEASURE,
129 REQUEST_ID,LAST_UPDATED_BY,LAST_UPDATE_DATE,CREATION_DATE,CREATED_BY,
130 LAST_UPDATE_LOGIN) VALUES
131 (V_Accum_id,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,
132 0,0,0,0,0,0,0,0,0,0,0,0,Null,pa_proj_accum_main.x_request_id,pa_proj_accum_main.x_last_updated_by,Trunc(sysdate),
133 Trunc(Sysdate),pa_proj_accum_main.x_created_by,pa_proj_accum_main.x_last_update_login);
134
135 -- Create commitments record for the Project level record
136
137 IF P_DEBUG_MODE = 'Y' THEN /* Added Debug Profile Option Check for bug#2674619 */
138 pa_debug.debug('Proj_level_record: ' || 'Creating Project Level Header record for Commitments');
139 END IF;
140
141 INSERT INTO PA_PROJECT_ACCUM_COMMITMENTS (
142 PROJECT_ACCUM_ID,CMT_RAW_COST_ITD,CMT_RAW_COST_YTD,CMT_RAW_COST_PP,
143 CMT_RAW_COST_PTD,CMT_BURDENED_COST_ITD,CMT_BURDENED_COST_YTD,
144 CMT_BURDENED_COST_PP,CMT_BURDENED_COST_PTD,
145 CMT_QUANTITY_ITD,CMT_QUANTITY_YTD,CMT_QUANTITY_PP,CMT_QUANTITY_PTD,
146 REQUEST_ID,LAST_UPDATED_BY,LAST_UPDATE_DATE,CREATION_DATE,CREATED_BY,
147 LAST_UPDATE_LOGIN ) VALUES
148 (V_Accum_Id,0,0,0,0,0,0,0,0,0,0,0,0,pa_proj_accum_main.x_request_id,pa_proj_accum_main.x_last_updated_by,Trunc(Sysdate),
149 Trunc(Sysdate), pa_proj_accum_main.x_created_by,pa_proj_accum_main.x_last_update_login);
150 x_Accum_id := V_Accum_id;
151 x_prev_accum_period := Null;
152 -- Restore the old x_err_stack;
153 x_err_stack := V_Old_Stack;
154 When Others Then
155 x_err_code := SQLCODE;
156 RAISE ;
157 End proj_level_record;
158
159 -- Get_Impl_Option - This returns the Accumulation option as specified
160 -- in PA_IMPLEMENTATIONS table . Returns whether
161 -- accumulation is maintained by PA_PERIOD or GL_PERIOD
162
163 Procedure Get_Impl_Option (x_impl_option Out NOCOPY Varchar2, --File.Sql.39 bug 4440895
164 x_err_stack In Out NOCOPY Varchar2, --File.Sql.39 bug 4440895
165 x_err_stage In Out NOCOPY Varchar2, --File.Sql.39 bug 4440895
166 x_err_code In Out NOCOPY Number ) IS --File.Sql.39 bug 4440895
167
168
169 V_Old_Stack Varchar2(630);
173 x_err_stack :=
170 P_DEBUG_MODE varchar2(1) := NVL(FND_PROFILE.value('PA_DEBUG_MODE'), 'N'); /* Added Debug Profile Option variable initialization for bug#2674619 */
171 Begin
172 V_Old_Stack := x_err_stack;
174 x_err_stack||'->PA_ACCUM_UTILS.Get_Impl_Option';
175
176 IF P_DEBUG_MODE = 'Y' THEN /* Added Debug Profile Option Check for bug#2674619 */
177 pa_debug.debug('Get_Impl_Option: ' || x_err_stack);
178 END IF;
179
180 SELECT accumulation_period_type
181 INTO x_impl_option
182 FROM pa_implementations;
183
184 -- Restore the old x_err_stack;
185 x_err_stack := V_Old_Stack;
186
187 Exception
188 When Others then
189 x_err_code := SQLCODE;
190 RAISE ;
191 End get_impl_option;
192
193 -- Get_Current_period_Info - This returns all relevant details pertaining
194 -- to the current pa period
195
196 Procedure Get_Current_period_Info (x_Current_Pa_Period Out NOCOPY Varchar2, --File.Sql.39 bug 4440895
197 x_Current_gl_period Out NOCOPY Varchar2, --File.Sql.39 bug 4440895
198 x_current_pa_start_date Out NOCOPY Date, --File.Sql.39 bug 4440895
199 x_current_pa_end_date Out NOCOPY Date, --File.Sql.39 bug 4440895
200 x_current_gl_start_date Out NOCOPY Date, --File.Sql.39 bug 4440895
201 x_current_gl_end_date Out NOCOPY Date, --File.Sql.39 bug 4440895
202 x_current_year Out NOCOPY Number, --File.Sql.39 bug 4440895
203 x_err_stack In Out NOCOPY Varchar2, --File.Sql.39 bug 4440895
204 x_err_stage In Out NOCOPY Varchar2, --File.Sql.39 bug 4440895
205 x_err_code In Out NOCOPY Number ) IS --File.Sql.39 bug 4440895
206
207 V_Old_Stack Varchar2(630);
208 P_DEBUG_MODE varchar2(1) := NVL(FND_PROFILE.value('PA_DEBUG_MODE'), 'N'); /* Added Debug Profile Option variable initialization for bug#2674619 */
209 Begin
210 V_Old_Stack := x_err_stack;
211 x_err_stack :=
212 x_err_stack||'->PA_ACCUM_UTILS.Get_Current_period_Info';
213
214 IF P_DEBUG_MODE = 'Y' THEN /* Added Debug Profile Option Check for bug#2674619 */
215 pa_debug.debug('Get_Current_period_Info: ' || x_err_stack);
216 END IF;
217 /* Commented out for the bug#2634995
218 SELECT
219 period_name,
220 gl_period_name,
221 pa_start_date,
222 pa_end_date,
223 gl_start_date,
224 gl_end_date,
225 period_year
226 INTO
227 x_current_pa_period,
228 x_current_gl_period,
229 x_current_pa_start_date,
230 x_current_pa_end_date,
231 x_current_gl_start_date,
232 x_current_gl_end_date,
233 x_current_year
234 FROM pa_periods_v
235 WHERE
236 current_pa_period_flag = 'Y';
237 */
238
239 /* Replaced the query from pa_periods_v with the view definition Bug #2634995*/
240 SELECT pap.period_name,
241 pap.gl_period_name,
242 pap.start_date,
243 pap.end_date,
244 glp.start_date,
245 glp.end_date,
246 glp.period_year
247 INTO
248 x_current_pa_period,
249 x_current_gl_period,
250 x_current_pa_start_date,
251 x_current_pa_end_date,
252 x_current_gl_start_date,
253 x_current_gl_end_date,
254 x_current_year
255 FROM PA_PERIODS PAP, GL_PERIOD_STATUSES GLP,
256 PA_IMPLEMENTATIONS PAIMP, PA_LOOKUPS PAL
257 WHERE PAP.GL_PERIOD_NAME = GLP.PERIOD_NAME
258 AND GLP.SET_OF_BOOKS_ID = PAIMP.SET_OF_BOOKS_ID
259 AND GLP.APPLICATION_ID = Pa_Period_Process_Pkg.Application_id
260 AND GLP.ADJUSTMENT_PERIOD_FLAG = 'N'
261 AND PAL.LOOKUP_TYPE = 'CLOSING STATUS'
262 AND PAL.LOOKUP_CODE = PAP.STATUS
263 AND PAP.current_pa_period_flag = 'Y';
264
265 -- Restore the old x_err_stack;
266 x_err_stack := V_Old_Stack;
267
268 Exception
269 WHEN NO_DATA_FOUND THEN
270 IF P_DEBUG_MODE = 'Y' THEN /* Added Debug Profile Option Check for bug#2674619 */
271 pa_debug.debug('Get_Current_period_Info: ' || '****REPORTING PERIOD NOT SET*****',pa_debug.DEBUG_LEVEL_EXCEPTION);
272 pa_debug.debug('Get_Current_period_Info: ' || '****SET REPORTING PERIOD AND RE-RUN PROCESS *****',pa_debug.DEBUG_LEVEL_EXCEPTION);
273 END IF;
274 x_err_code := SQLCODE;
275 RAISE;
276 WHEN OTHERS THEN
277 x_err_code := SQLCODE;
278 RAISE;
279 End Get_Current_period_Info;
280
281 -- Get_pa_period_info - This returns all details pertaining to the
282 -- following
283 -- Current Pa period,Previous pa period, current
284 -- gl period , previous gl period, year pertaining
285 -- to the previously accumulated period
286
287 Procedure Get_pa_period_Info (x_impl_opt In Varchar2,
288 x_prev_accum_period in Varchar2,
289 x_current_Pa_Period In Varchar2,
290 x_current_gl_period In Varchar2,
291 x_current_pa_start_date In Date,
292 x_current_pa_end_date In Date,
296 x_prev_gl_period Out NOCOPY Varchar2, --File.Sql.39 bug 4440895
293 x_current_gl_start_date In Date,
294 x_current_gl_end_date In Date,
295 x_prev_pa_period Out NOCOPY Varchar2, --File.Sql.39 bug 4440895
297 x_prev_pa_year Out NOCOPY Number, --File.Sql.39 bug 4440895
298 x_prev_gl_year Out NOCOPY Number, --File.Sql.39 bug 4440895
299 x_prev_accum_year Out NOCOPY number, --File.Sql.39 bug 4440895
300 x_prev_pa_start_date Out NOCOPY Date, --File.Sql.39 bug 4440895
301 x_prev_pa_end_date Out NOCOPY Date, --File.Sql.39 bug 4440895
302 x_prev_gl_start_date Out NOCOPY Date, --File.Sql.39 bug 4440895
303 x_prev_gl_end_date Out NOCOPY Date, --File.Sql.39 bug 4440895
304 x_prev_accum_start_date In Out NOCOPY Date, --File.Sql.39 bug 4440895
305 x_prev_accum_end_date Out NOCOPY Date, --File.Sql.39 bug 4440895
306 x_prev_prev_accum_period Out NOCOPY Varchar2, --File.Sql.39 bug 4440895
307 x_accum_period_type_changed IN OUT NOCOPY BOOLEAN, --File.Sql.39 bug 4440895
308 x_err_stack In Out NOCOPY Varchar2, --File.Sql.39 bug 4440895
309 x_err_stage In Out NOCOPY Varchar2, --File.Sql.39 bug 4440895
310 x_err_code In Out NOCOPY Number ) IS --File.Sql.39 bug 4440895
311
312 V_Old_Stack Varchar2(630);
313 P_DEBUG_MODE varchar2(1) := NVL(FND_PROFILE.value('PA_DEBUG_MODE'), 'N'); /* Added Debug Profile Option variable initialization for bug#2674619 */
314 BEGIN
315 V_Old_Stack := x_err_stack;
316 x_err_stack :=
317 x_err_stack||'->PA_ACCUM_UTILS.Get_pa_period_Info';
318 x_accum_period_type_changed := FALSE;
319
320 -- Select the details pertaining to the previous pa period.
321
322 IF P_DEBUG_MODE = 'Y' THEN /* Added Debug Profile Option Check for bug#2674619 */
323 pa_debug.debug('Get_pa_period_Info: ' || x_err_stack);
324 END IF;
325
326 <<prev_pa_period>>
327 BEGIN
328 SELECT
329 PERIOD_NAME,
330 PERIOD_YEAR,
331 PA_START_DATE,
332 PA_END_DATE
333 INTO
334 x_prev_pa_period,
335 x_prev_pa_year,
336 x_prev_pa_start_date,
337 x_prev_pa_end_date
338 FROM
339 PA_PERIODS_V
340 WHERE pa_start_date =
341 (SELECT max(start_date)
342 FROM
343 pa_periods
344 WHERE start_date < x_current_pa_start_date);
345
346 EXCEPTION
347 WHEN NO_DATA_FOUND THEN
348 -- The current pa_period is the first period defined
349 x_prev_pa_period := NULL;
350 x_prev_pa_year := NULL;
351 x_prev_pa_start_date := NULL;
352 x_prev_pa_end_date := NULL;
353
354 WHEN OTHERS THEN
355 x_err_code := SQLCODE;
356 RAISE;
357 END prev_pa_period;
358
359 -- Select the details pertaining to the previous gl period.
360
361 <<prev_gl_period>>
362 BEGIN
363
364 SELECT
365 DISTINCT gl_period_name,
366 period_year,
367 gl_start_date,
368 gl_end_date
369 INTO
370 x_prev_gl_period,
371 x_prev_gl_year,
372 x_prev_gl_start_date,
373 x_prev_gl_end_date
374 FROM
375 pa_periods_v
376 WHERE
377 gl_start_date =
378 (SELECT max(gl_start_date)
379 FROM pa_periods_v
380 WHERE
381 gl_start_date < x_current_gl_start_date);
382
383 EXCEPTION
384 WHEN NO_DATA_FOUND THEN
385 -- current gl_period is the first period defined
386 x_prev_gl_period := NULL;
387 x_prev_gl_year := NULL;
388 x_prev_gl_start_date := NULL;
389 x_prev_gl_end_date := NULL;
390 WHEN OTHERS THEN
391 x_err_code := SQLCODE;
392 RAISE;
393 END prev_gl_period;
394
395 -- If the project had been previously accumulated, then get the details
396 -- pertaining to the previously accumulated period
397
398 If x_prev_accum_period is not Null Then
399 BEGIN
400
401 If x_impl_opt = 'PA' Then
402 Select PERIOD_YEAR,PA_START_DATE,PA_END_DATE
403 into x_prev_accum_year,x_prev_accum_start_date,
404 x_prev_accum_end_date from
405 PA_PERIODS_V WHERE Period_name = x_prev_accum_period;
406 Elsif
407 x_impl_opt = 'GL' Then
408 Select Distinct PERIOD_YEAR,GL_START_DATE,GL_END_DATE
409 into x_prev_accum_year,x_prev_accum_start_date,
410 x_prev_accum_end_date from
411 PA_PERIODS_V WHERE Gl_Period_name = x_prev_accum_period;
412 End If;
413
414 EXCEPTION
415 WHEN NO_DATA_FOUND THEN
416 -- Accumulation period type must have changed
417 -- Bug #572031
418 x_accum_period_type_changed := TRUE;
419 END;
420
424 <<prev_prev_accum_period>>
421 IF (x_accum_period_type_changed = FALSE) THEN
422
423 -- Now get x_prev_prev_accum_period
425 BEGIN
426 SELECT DISTINCT
427 DECODE(x_impl_opt,'PA',PERIOD_NAME,'GL',GL_PERIOD_NAME,PERIOD_NAME)
428 INTO x_prev_prev_accum_period
429 FROM
430 pa_periods_v
431 WHERE
432 DECODE(x_impl_opt,
433 'PA',pa_start_date,'GL',gl_start_date,pa_start_date) =
434 (SELECT max(DECODE(
435 x_impl_opt,'PA',pa_start_date,
436 'GL',gl_start_date,pa_start_date))
437 FROM pa_periods_v
438 WHERE
439 DECODE(x_impl_opt,
440 'PA',pa_start_date,'GL',gl_start_date,pa_start_date)
441 < x_prev_accum_start_date);
442
443 EXCEPTION
444 WHEN NO_DATA_FOUND THEN
445 x_prev_prev_accum_period := NULL;
446 WHEN OTHERS THEN
447 x_err_code := SQLCODE;
448 RAISE;
449 END prev_prev_accum_period;
450 END IF; -- (x_accum_period_type_changed = FALSE)
451 End If;
452 -- Restore the old x_err_stack;
453 x_err_stack := V_Old_Stack;
454 Exception
455 When Others Then
456 x_err_code := SQLCODE;
457 RAISE ;
458 End Get_pa_period_Info;
459
460 -- Get_period_year_info - This returns the start date
461 -- of the current period year
462
463 Procedure Get_period_year_info (x_current_gl_period In Varchar2,
464 x_period_yr_start_date Out NOCOPY Date, --File.Sql.39 bug 4440895
465 x_err_stack In Out NOCOPY Varchar2, --File.Sql.39 bug 4440895
466 x_err_stage In Out NOCOPY Varchar2, --File.Sql.39 bug 4440895
467 x_err_code In Out NOCOPY Number ) IS --File.Sql.39 bug 4440895
468
469 V_Old_Stack Varchar2(630);
470 P_DEBUG_MODE varchar2(1) := NVL(FND_PROFILE.value('PA_DEBUG_MODE'), 'N'); /* Added Debug Profile Option variable initialization for bug#2674619 */
471 BEGIN
472 V_Old_Stack := x_err_stack;
473 x_err_stack :=
474 x_err_stack||'->PA_ACCUM_UTILS.Get_period_year_Info';
475
476 IF P_DEBUG_MODE = 'Y' THEN /* Added Debug Profile Option Check for bug#2674619 */
477 pa_debug.debug('Get_period_year_info: ' || x_err_stack);
478 END IF;
479
480 -- Get the period year start date
481
482 SELECT
483 DISTINCT YEAR_START_DATE
484 INTO
485 x_period_yr_start_date
486 FROM
487 GL_PERIOD_STATUSES gps, pa_implementations imp
488 WHERE gps.application_id = pa_period_process_pkg.application_id
489 and gps.set_of_books_id = imp.set_of_books_id
490 and gps.period_name = x_current_gl_period;
491
492 EXCEPTION
493 WHEN NO_DATA_FOUND THEN
494 x_period_yr_start_date := NULL;
495
496 WHEN OTHERS THEN
497 x_err_code := SQLCODE;
498 RAISE;
499 End Get_period_year_info;
500
501 Procedure Check_Actuals_Details ( x_project_id In Number,
502 x_task_id In Number,
503 x_resource_list_member_id In Number,
504 x_recs_processed Out NOCOPY Number, --File.Sql.39 bug 4440895
505 x_err_stack In Out NOCOPY Varchar2, --File.Sql.39 bug 4440895
506 x_err_stage In Out NOCOPY Varchar2, --File.Sql.39 bug 4440895
507 x_err_code In Out NOCOPY Number ) IS --File.Sql.39 bug 4440895
508
509
510 -- Check_Actuals_Details - For the given Project,Task and Resource
511 -- combination in the PA_PROJECT_ACCUM_HEADERS table,
512 -- checks for detail records in
513 -- PA_PROJECT_ACCUM_ACTUALS table. It is possible
514 -- that the Headers table might have a record
515 -- but no corresponding detail record. This procedure
516 -- creates the detail records for all the tasks in
517 -- the hierarchy
518
519 V_Accum_id Number := 0;
520 V_recs_processed Number := 0;
521 V_Task_Array task_id_tabtype;
522 V_Task_id Number := 0;
523 V_Noof_Tasks Number := 0;
524 V_err_code Number := 0;
525 V_Old_Stack Varchar2(630);
526
527 -- This cursor gets the Accum_id for the given Project,Task and Resource
528 -- combination which has a record in PA_PROJECT_ACCUM_HEADERS but no
529 -- corresponding record in PA_PROJECT_ACCUM_ACTUALS.
530
531 CURSOR Get_Accum_Id_Cur is
532 Select Project_accum_id
533 FROM
534 PA_PROJECT_ACCUM_HEADERS PAH
535 WHERE Project_id = x_project_id
536 and TASK_ID = V_task_id
537 and RESOURCE_LIST_MEMBER_ID = x_resource_list_member_id
538 and not exists
539 (Select Project_accum_id
540 from
541 PA_PROJECT_ACCUM_ACTUALS paa
542 where paa.project_accum_id = pah.project_accum_id);
543
544 Begin
545 V_Old_Stack := x_err_stack;
546 x_err_stack :=
547 x_err_stack||'->PA_ACCUM_UTILS.Check_Actuals_Details';
548 V_task_id := X_Task_id;
549 Open Get_Accum_Id_Cur;
550 Fetch Get_Accum_Id_Cur Into V_Accum_id;
551 -- If we get such a record in Headers, then we insert one record in
555 Insert into PA_PROJECT_ACCUM_ACTUALS (
552 -- the Actuals Detail table
553
554 If Get_Accum_Id_Cur%FOUND Then
556 PROJECT_ACCUM_ID,RAW_COST_ITD,RAW_COST_YTD,RAW_COST_PP,RAW_COST_PTD,
557 BILLABLE_RAW_COST_ITD,BILLABLE_RAW_COST_YTD,BILLABLE_RAW_COST_PP,
558 BILLABLE_RAW_COST_PTD,BURDENED_COST_ITD,BURDENED_COST_YTD,
559 BURDENED_COST_PP,BURDENED_COST_PTD,BILLABLE_BURDENED_COST_ITD,
560 BILLABLE_BURDENED_COST_YTD,BILLABLE_BURDENED_COST_PP,
561 BILLABLE_BURDENED_COST_PTD,QUANTITY_ITD,QUANTITY_YTD,QUANTITY_PP,
562 QUANTITY_PTD,LABOR_HOURS_ITD,LABOR_HOURS_YTD,LABOR_HOURS_PP,
563 LABOR_HOURS_PTD,BILLABLE_QUANTITY_ITD,BILLABLE_QUANTITY_YTD,
564 BILLABLE_QUANTITY_PP,BILLABLE_QUANTITY_PTD,
565 BILLABLE_LABOR_HOURS_ITD,BILLABLE_LABOR_HOURS_YTD,
566 BILLABLE_LABOR_HOURS_PP,BILLABLE_LABOR_HOURS_PTD,REVENUE_ITD,
567 REVENUE_YTD,REVENUE_PP,REVENUE_PTD,TXN_UNIT_OF_MEASURE,
568 REQUEST_ID,LAST_UPDATED_BY,LAST_UPDATE_DATE,CREATION_DATE,CREATED_BY,
569 LAST_UPDATE_LOGIN) Values
570 (V_Accum_id,0,0,0,0,
571 0,0,0,
572 0,0,0,
573 0,0,0,
574 0,0,0,
575 0,0,0,0,0,0,0,0,
576 0,0,0,0,0,0,0,
577 0,0,0,0,
578 0,Null,pa_proj_accum_main.x_request_id,pa_proj_accum_main.x_last_updated_by,Trunc(sysdate),
579 Trunc(Sysdate),pa_proj_accum_main.x_created_by,pa_proj_accum_main.x_last_update_login);
580 V_recs_processed := 1;
581 End If;
582 Close Get_Accum_Id_Cur;
583 V_noof_Tasks := 0;
584
585 --Now get all the higher task ids for the current task
586 --(if the task id <> 0,
587 --since we may be passing the task id as 0 for the Project-resource
588 --level records)
589
590 If ( x_Task_id <> 0 ) Then
591 Get_existing_higher_tasks (x_project_id,
592 X_task_id,
593 X_resource_list_member_id,
594 V_task_array,
595 V_noof_tasks,
596 x_err_stack,
597 x_err_stage,
598 x_err_code);
599
600 -- Insert the appropriate records in the Actuals table for all higher
601 -- tasks, if they have not been created.
602
603 IF v_noof_tasks > 0 then
604 FOR i in 1..v_noof_tasks LOOP
605 Insert into PA_PROJECT_ACCUM_ACTUALS (
606 PROJECT_ACCUM_ID,RAW_COST_ITD,RAW_COST_YTD,RAW_COST_PP,RAW_COST_PTD,
607 BILLABLE_RAW_COST_ITD,BILLABLE_RAW_COST_YTD,BILLABLE_RAW_COST_PP,
608 BILLABLE_RAW_COST_PTD,BURDENED_COST_ITD,BURDENED_COST_YTD,
609 BURDENED_COST_PP,BURDENED_COST_PTD,BILLABLE_BURDENED_COST_ITD,
610 BILLABLE_BURDENED_COST_YTD,BILLABLE_BURDENED_COST_PP,
611 BILLABLE_BURDENED_COST_PTD,QUANTITY_ITD,QUANTITY_YTD,QUANTITY_PP,
612 QUANTITY_PTD,LABOR_HOURS_ITD,LABOR_HOURS_YTD,LABOR_HOURS_PP,
613 LABOR_HOURS_PTD,BILLABLE_QUANTITY_ITD,BILLABLE_QUANTITY_YTD,
614 BILLABLE_QUANTITY_PP,BILLABLE_QUANTITY_PTD,
615 BILLABLE_LABOR_HOURS_ITD,BILLABLE_LABOR_HOURS_YTD,
616 BILLABLE_LABOR_HOURS_PP,BILLABLE_LABOR_HOURS_PTD,REVENUE_ITD,
617 REVENUE_YTD,REVENUE_PP,REVENUE_PTD,TXN_UNIT_OF_MEASURE,
618 REQUEST_ID,LAST_UPDATED_BY,LAST_UPDATE_DATE,CREATION_DATE,CREATED_BY,
619 LAST_UPDATE_LOGIN)
620 Select PAH.PROJECT_ACCUM_ID,0,0,0,0,0,0,0,0,0,0,0,0,0,
621 0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,
622 0,Null,pa_proj_accum_main.x_request_id,pa_proj_accum_main.x_last_updated_by,Trunc(sysdate),
623 Trunc(Sysdate),pa_proj_accum_main.x_created_by,pa_proj_accum_main.x_last_update_login
624 from
625 PA_PROJECT_ACCUM_HEADERS PAH
626 Where Project_Id = x_project_id
627 and Task_id = v_task_array(i)
628 and Resource_list_member_id = x_Resource_list_member_id
629 and Not Exists
630 (Select 'x'
631 from
632 PA_PROJECT_ACCUM_ACTUALS PAA
633 Where
634 PAH.PROJECT_ACCUM_ID = PAA.PROJECT_ACCUM_ID);
635 v_recs_processed := V_recs_processed + 1;
636 END LOOP; -- (i in 1..v_noof_tasks LOOP )
637 End If; -- (v_noof_tasks > 0)
638 End If; -- (v_recs_processed = 1 and x_Task_id <> 0)
639 x_recs_processed := v_recs_processed;
640
641 -- Restore the old x_err_stack;
642
643 x_err_stack := V_Old_Stack;
644 Exception
645 When Others Then
646 x_err_code := SQLCODE;
647 RAISE ;
648 End Check_Actuals_Details;
649
650 Procedure Check_Cmt_Details ( x_project_id In Number,
651 x_task_id In Number,
652 x_resource_list_member_id In Number,
653 x_recs_processed Out NOCOPY Number, --File.Sql.39 bug 4440895
654 x_err_stack In Out NOCOPY Varchar2, --File.Sql.39 bug 4440895
655 x_err_stage In Out NOCOPY Varchar2, --File.Sql.39 bug 4440895
656 x_err_code In Out NOCOPY Number ) IS --File.Sql.39 bug 4440895
657
658
659 -- Check_Cmt_Details - For the given Project,Task and Resource
660 -- combination in the PA_PROJECT_ACCUM_HEADERS table,
661 -- checks for detail records in
662 -- PA_PROJECT_ACCUM_COMMITMENTS table. It is possible
663 -- that the Headers table might have a record
667
664 -- but no corresponding detail record. This procedure
665 -- creates the detail records for all the tasks in
666 -- the hierarchy
668 V_Accum_id Number := 0;
669 V_recs_processed Number := 0;
670 V_Task_Array task_id_tabtype;
671 V_Task_id Number := 0;
672 V_Noof_Tasks Number := 0;
673 V_err_code Number := 0;
674 V_Old_Stack Varchar2(630);
675
676 -- This cursor gets the Accum_id for the given Project,Task and Resource
677 -- combination which has a record in PA_PROJECT_ACCUM_HEADERS but no
678 -- corresponding record in PA_PROJECT_ACCUM_COMMITMENTS
679
680 CURSOR Get_Accum_Id_Cur is
681 Select Project_accum_id
682 from
683 PA_PROJECT_ACCUM_HEADERS PAH
684 Where Project_id = x_project_id
685 and TASK_ID = V_task_id
686 and RESOURCE_LIST_MEMBER_ID = x_resource_list_member_id
687 and not exists
688 (Select Project_accum_id
689 from
690 PA_PROJECT_ACCUM_COMMITMENTS pac
691 where pac.project_accum_id = pah.project_accum_id);
692
693 Begin
694 V_Old_Stack := x_err_stack;
695 x_err_stack :=
696 x_err_stack||'->PA_ACCUM_UTILS.Check_Cmt_Details';
697 V_task_id := X_Task_id;
698
699 Open Get_Accum_Id_Cur;
700 Fetch Get_Accum_Id_Cur Into V_Accum_id;
701
702 -- If we get such a record in Headers, then we insert one record in
703 -- the Commitments Detail table
704
705 If Get_Accum_Id_Cur%FOUND Then
706 Insert into PA_PROJECT_ACCUM_COMMITMENTS (
707 PROJECT_ACCUM_ID,CMT_RAW_COST_ITD,CMT_RAW_COST_YTD,CMT_RAW_COST_PP,
708 CMT_RAW_COST_PTD,
709 CMT_BURDENED_COST_ITD,CMT_BURDENED_COST_YTD,
710 CMT_BURDENED_COST_PP,CMT_BURDENED_COST_PTD,
711 CMT_QUANTITY_ITD,CMT_QUANTITY_YTD,
712 CMT_QUANTITY_PP,CMT_QUANTITY_PTD,
713 CMT_UNIT_OF_MEASURE,
714 REQUEST_ID,LAST_UPDATED_BY,LAST_UPDATE_DATE,CREATION_DATE,CREATED_BY,
715 LAST_UPDATE_LOGIN) Values
716 (v_Accum_id,0,0,0,0,
717 0,0,0,0,
718 0,0,0,0,
719 Null,pa_proj_accum_main.x_request_id,pa_proj_accum_main.x_last_updated_by,Trunc(sysdate),
720 Trunc(Sysdate),pa_proj_accum_main.x_created_by,pa_proj_accum_main.x_last_update_login);
721 v_recs_processed := 1;
722 End If;
723 Close Get_Accum_Id_Cur;
724 V_noof_Tasks := 0;
725
726 --Now get all the higher task ids for the current task
727 --(if the task id <> 0,
728 --since we may be passing the task id as 0 for the Project-resource
729 --level records)
730
731 If ( x_Task_id <> 0 ) Then
732 Get_existing_higher_tasks (x_project_id,
733 X_task_id,
734 X_resource_list_member_id,
735 V_task_array,
736 V_noof_tasks,
737 x_err_stack,
738 x_err_stage,
739 x_err_code);
740
741
742 -- Insert the appropriate records in the Commitments table for all higher
743 -- tasks, if they have not been created.
744
745 If v_noof_tasks > 0 then
746 FOR i in 1..v_noof_tasks LOOP
747 Insert into PA_PROJECT_ACCUM_COMMITMENTS (
748 PROJECT_ACCUM_ID,CMT_RAW_COST_ITD,CMT_RAW_COST_YTD,CMT_RAW_COST_PP,
749 CMT_RAW_COST_PTD,
750 CMT_BURDENED_COST_ITD,CMT_BURDENED_COST_YTD,
751 CMT_BURDENED_COST_PP,CMT_BURDENED_COST_PTD,
752 CMT_QUANTITY_ITD,CMT_QUANTITY_YTD,
753 CMT_QUANTITY_PP,CMT_QUANTITY_PTD,
754 CMT_UNIT_OF_MEASURE,
755 REQUEST_ID,LAST_UPDATED_BY,LAST_UPDATE_DATE,CREATION_DATE,CREATED_BY,
756 LAST_UPDATE_LOGIN)
757 Select PROJECT_ACCUM_ID,0,0,0,0,0,0,0,0,0,0,0,0,
758 Null,pa_proj_accum_main.x_request_id,pa_proj_accum_main.x_last_updated_by,Trunc(sysdate),
759 Trunc(Sysdate),pa_proj_accum_main.x_created_by,pa_proj_accum_main.x_last_update_login
760 from PA_PROJECT_ACCUM_HEADERS PAH
761 Where Project_Id = x_project_id and Task_id = v_task_array(i) and
762 Resource_list_member_id = x_Resource_list_member_id and
763 Not Exists (Select 'x' from PA_PROJECT_ACCUM_COMMITMENTS PAC Where
764 PAH.PROJECT_ACCUM_ID = PAC.PROJECT_ACCUM_ID);
765 v_recs_processed := V_recs_processed + 1;
766 END LOOP;
767 End If;
768 End If;
769 x_recs_processed := v_recs_processed;
770
771 -- Restore the old x_err_stack;
772
773 x_err_stack := V_Old_Stack;
774 Exception
775 When Others Then
776 x_err_code := SQLCODE;
777 RAISE ;
778 End Check_Cmt_Details;
779
780 Procedure Check_Budget_Details ( x_project_id In Number,
781 x_task_id In Number,
782 x_resource_list_member_id In Number,
783 x_Budget_type_code In Varchar2,
784 x_recs_processed Out NOCOPY Number, --File.Sql.39 bug 4440895
785 x_err_stack In Out NOCOPY Varchar2, --File.Sql.39 bug 4440895
786 x_err_stage In Out NOCOPY Varchar2, --File.Sql.39 bug 4440895
787 x_err_code In Out NOCOPY Number ) IS --File.Sql.39 bug 4440895
788
789 -- Check_Budget_Details - For the given Project,Task and Resource
793 -- that the Headers table might have a record
790 -- combination in the PA_PROJECT_ACCUM_HEADERS table,
791 -- checks for detail records in
792 -- PA_PROJECT_ACCUM_COMMITMENTS table. It is possible
794 -- but no corresponding detail record. This procedure
795 -- creates the detail records for all the tasks in
796 -- the hierarchy
797
798 V_Accum_id Number := 0;
799 V_recs_processed Number := 0;
800 V_Task_Array task_id_tabtype;
801 V_Task_id Number := 0;
802 V_Noof_Tasks Number := 0;
803 V_err_code Number := 0;
804 V_Old_Stack Varchar2(630);
805
806 -- This cursor gets the Accum_id for the given Project,Task and Resource
807 -- combination which has a record in PA_PROJECT_ACCUM_HEADERS but no
808 -- corresponding record in PA_PROJECT_ACCUM_BUDGETS
809
810 CURSOR Get_Accum_Id_Cur IS
811 SELECT
812 Project_accum_id
813 FROM
814 PA_PROJECT_ACCUM_HEADERS PAH
815 WHERE Project_id = x_project_id
816 and TASK_ID = v_task_id
817 and RESOURCE_LIST_MEMBER_ID = x_resource_list_member_id
818 and not exists
819 (Select Project_accum_id
820 from
821 PA_PROJECT_ACCUM_BUDGETS pab
822 where pab.project_accum_id = pah.project_accum_id
823 and pab.Budget_Type_Code = x_Budget_Type_Code);
824
825 Begin
826 V_Old_Stack := x_err_stack;
827 x_err_stack :=
828 x_err_stack||'->PA_ACCUM_UTILS.Check_Budget_Details';
829 V_task_id := X_Task_id;
830 Open Get_Accum_Id_Cur;
831 Fetch Get_Accum_Id_Cur Into V_Accum_id;
832
833 -- If we get such a record in Headers, then we insert one record in
834 -- the Budgets Detail table
835
836 If Get_Accum_Id_Cur%FOUND Then
837 Insert into PA_PROJECT_ACCUM_BUDGETS (
838 PROJECT_ACCUM_ID,BUDGET_TYPE_CODE,BASE_RAW_COST_ITD,BASE_RAW_COST_YTD,
839 BASE_RAW_COST_PP, BASE_RAW_COST_PTD,
840 BASE_BURDENED_COST_ITD,BASE_BURDENED_COST_YTD,
841 BASE_BURDENED_COST_PP,BASE_BURDENED_COST_PTD,
842 ORIG_RAW_COST_ITD,ORIG_RAW_COST_YTD,
843 ORIG_RAW_COST_PP, ORIG_RAW_COST_PTD,
844 ORIG_BURDENED_COST_ITD,ORIG_BURDENED_COST_YTD,
845 ORIG_BURDENED_COST_PP,ORIG_BURDENED_COST_PTD,
846 BASE_QUANTITY_ITD,BASE_QUANTITY_YTD,BASE_QUANTITY_PP,
847 BASE_QUANTITY_PTD,
848 ORIG_QUANTITY_ITD,ORIG_QUANTITY_YTD,ORIG_QUANTITY_PP,
849 ORIG_QUANTITY_PTD,
850 BASE_LABOR_HOURS_ITD,BASE_LABOR_HOURS_YTD,BASE_LABOR_HOURS_PP,
851 BASE_LABOR_HOURS_PTD,
852 ORIG_LABOR_HOURS_ITD,ORIG_LABOR_HOURS_YTD,ORIG_LABOR_HOURS_PP,
853 ORIG_LABOR_HOURS_PTD,
854 BASE_REVENUE_ITD,BASE_REVENUE_YTD,BASE_REVENUE_PP,BASE_REVENUE_PTD,
855 ORIG_REVENUE_ITD,ORIG_REVENUE_YTD,ORIG_REVENUE_PP,ORIG_REVENUE_PTD,
856 BASE_UNIT_OF_MEASURE,ORIG_UNIT_OF_MEASURE,
857 BASE_RAW_COST_TOT,BASE_BURDENED_COST_TOT,ORIG_RAW_COST_TOT,
858 ORIG_BURDENED_COST_TOT,BASE_REVENUE_TOT,ORIG_REVENUE_TOT,
859 BASE_LABOR_HOURS_TOT,ORIG_LABOR_HOURS_TOT,BASE_QUANTITY_TOT,
860 ORIG_QUANTITY_TOT,
861 REQUEST_ID,LAST_UPDATED_BY,LAST_UPDATE_DATE,CREATION_DATE,CREATED_BY,
862 LAST_UPDATE_LOGIN) Values
863 (V_Accum_id,x_budget_type_code,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,
864 0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,NULL,NULL,0,0,0,0,0,0,0,0,0,0,
865 pa_proj_accum_main.x_request_id,pa_proj_accum_main.x_last_updated_by,Trunc(Sysdate),Trunc(Sysdate),pa_proj_accum_main.x_created_by,pa_proj_accum_main.x_last_update_login);
866 v_recs_processed := 1;
867 End If;
868 Close Get_Accum_Id_Cur;
869 V_noof_Tasks := 0;
870
871 --Now get all the higher task ids for the current task
872 --(if the task id <> 0,
873 --since we may be passing the task id as 0 for the Project-resource
874 --level records)
875
876 If ( x_Task_id <> 0 ) Then
877 Get_existing_higher_tasks (x_project_id,
878 X_task_id,
879 X_resource_list_member_id,
880 V_task_array,
881 V_noof_tasks,
882 x_err_stack,
883 x_err_stage,
884 x_err_code);
885
886
887 -- Insert the appropriate records in the Budgets table for all higher
888 -- tasks, if they have not been created.
889
890 If v_noof_tasks > 0 then
891 FOR i in 1..v_noof_tasks LOOP
892 Insert into PA_PROJECT_ACCUM_BUDGETS (
893 PROJECT_ACCUM_ID,BUDGET_TYPE_CODE,BASE_RAW_COST_ITD,BASE_RAW_COST_YTD,
894 BASE_RAW_COST_PP, BASE_RAW_COST_PTD,
895 BASE_BURDENED_COST_ITD,BASE_BURDENED_COST_YTD,
896 BASE_BURDENED_COST_PP,BASE_BURDENED_COST_PTD,
897 ORIG_RAW_COST_ITD,ORIG_RAW_COST_YTD,
898 ORIG_RAW_COST_PP, ORIG_RAW_COST_PTD,
899 ORIG_BURDENED_COST_ITD,ORIG_BURDENED_COST_YTD,
900 ORIG_BURDENED_COST_PP,ORIG_BURDENED_COST_PTD,
901 BASE_QUANTITY_ITD,BASE_QUANTITY_YTD,BASE_QUANTITY_PP,
902 BASE_QUANTITY_PTD,
903 ORIG_QUANTITY_ITD,ORIG_QUANTITY_YTD,ORIG_QUANTITY_PP,
904 ORIG_QUANTITY_PTD,
905 BASE_LABOR_HOURS_ITD,BASE_LABOR_HOURS_YTD,BASE_LABOR_HOURS_PP,
906 BASE_LABOR_HOURS_PTD,
907 ORIG_LABOR_HOURS_ITD,ORIG_LABOR_HOURS_YTD,ORIG_LABOR_HOURS_PP,
908 ORIG_LABOR_HOURS_PTD,
912 BASE_RAW_COST_TOT,BASE_BURDENED_COST_TOT,ORIG_RAW_COST_TOT,
909 BASE_REVENUE_ITD,BASE_REVENUE_YTD,BASE_REVENUE_PP,BASE_REVENUE_PTD,
910 ORIG_REVENUE_ITD,ORIG_REVENUE_YTD,ORIG_REVENUE_PP,ORIG_REVENUE_PTD,
911 BASE_UNIT_OF_MEASURE,ORIG_UNIT_OF_MEASURE,
913 ORIG_BURDENED_COST_TOT,BASE_REVENUE_TOT,ORIG_REVENUE_TOT,
914 BASE_LABOR_HOURS_TOT,ORIG_LABOR_HOURS_TOT,BASE_QUANTITY_TOT,
915 ORIG_QUANTITY_TOT,
916 REQUEST_ID,LAST_UPDATED_BY,LAST_UPDATE_DATE,CREATION_DATE,CREATED_BY,
917 LAST_UPDATE_LOGIN)
918 Select PROJECT_ACCUM_ID,x_budget_type_code,0,0,0,0,0,0,0,0,0,0,
919 0,0,0,0,0,0,0,0,0,0,
920 0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,NULL,NULL,0,0,0,0,0,0,0,0,0,0,
921 pa_proj_accum_main.x_request_id,pa_proj_accum_main.x_last_updated_by,Trunc(Sysdate),Trunc(Sysdate),pa_proj_accum_main.x_created_by,pa_proj_accum_main.x_last_update_login
922 from PA_PROJECT_ACCUM_HEADERS PAH
923 Where Project_Id = x_project_id and Task_id = v_task_array(i) and
924 Resource_list_member_id = x_Resource_list_member_id and
925 Not Exists (Select 'x' from PA_PROJECT_ACCUM_BUDGETS PAB Where
926 PAH.PROJECT_ACCUM_ID = PAB.PROJECT_ACCUM_ID
927 AND PAB.budget_type_code = x_budget_type_code);
928 v_recs_processed := V_recs_processed + 1;
929 END LOOP; -- i in 1..v_noof_tasks LOOP
930 End If; -- v_noof_tasks > 0
931 End If; -- (v_recs_processed = 1 and x_Task_id <> 0 )
932 x_recs_processed := v_recs_processed;
933
934 -- Restore the old x_err_stack;
935
936 x_err_stack := V_Old_Stack;
937 Exception
938 When Others Then
939 x_err_code := SQLCODE;
940 RAISE ;
941
942 End Check_Budget_Details;
943
944 Procedure Get_Config_Option (X_project_id In Number,
945 x_Accum_category_code In Varchar2,
946 x_Accum_column_code In Varchar2,
947 x_Accum_Flag Out NOCOPY Varchar2, --File.Sql.39 bug 4440895
948 x_err_code In Out NOCOPY Number, --File.Sql.39 bug 4440895
949 x_err_stage In Out NOCOPY Varchar2, --File.Sql.39 bug 4440895
950 x_err_stack In Out NOCOPY Varchar2 ) Is --File.Sql.39 bug 4440895
951
952
953 -- Get_Config_Option - For the given Accumulation Category
954 -- checks whether the given column is configured
955 -- for Accumulation. The Accum_flag 'Y' or 'N'
956 -- determines whether the said column is to be
957 -- accumulated or not
958
959 v_project_type Varchar2(30);
960 v_project_type_class_code Varchar2(30);
961
962 -- This Cursor fetches the Project_Type_Class_Code from PA_PROJECT_TYPES
963 -- based on the given Project's project_type
964
965 CURSOR Get_Project_type_class_cur is
966 Select Pt.project_type_class_code
967 from
968 pa_project_types Pt , pa_projects P
969 where P.project_id = x_project_id
970 and P.project_type = Pt.project_type ;
971
972 -- This Cursor fetches the Accum flag for the given Accum category,column
973 -- and Project Type class code
974
975 CURSOR Get_Accum_Flag_cur is
976 Select Accum_Flag
977 from
978 pa_accum_columns
979 where Project_Type_Class_code = v_project_type_class_code
980 and Accum_Category_Code = x_Accum_category_code
981 and Accum_Column_Code = x_Accum_column_code ;
982
983 V_old_stack Varchar2(630);
984 P_DEBUG_MODE varchar2(1) := NVL(FND_PROFILE.value('PA_DEBUG_MODE'), 'N'); /* Added Debug Profile Option variable initialization for bug#2674619 */
985 Begin
986 x_err_code := 0;
987 x_Accum_flag := NULL;
988 v_old_stack := x_err_stack;
989 x_err_stack := x_err_stack || '->PA_ACCUM_UTILS.Get_config_Option ';
990 x_err_stage := ' Select Project_Type_class_code ';
991
992 IF P_DEBUG_MODE = 'Y' THEN /* Added Debug Profile Option Check for bug#2674619 */
993 pa_debug.debug('Get_Config_Option: ' || x_err_stack);
994 END IF;
995
996 Open Get_Project_type_class_cur;
997 Fetch Get_Project_type_class_cur into v_project_type_class_code;
998
999 -- If we get NO_DATA_FOUND then raise Exception
1000
1001 If Get_Project_type_class_cur%NOTFOUND Then
1002 Close Get_Project_type_class_cur;
1003 RAISE NO_DATA_FOUND;
1004 End If;
1005
1006 Close Get_Project_type_class_cur;
1007 x_err_stage := ' Select Accum_Flag ';
1008 Open Get_Accum_Flag_cur;
1009 Fetch Get_Accum_Flag_cur into x_accum_flag;
1010
1011 -- If we get NO_DATA_FOUND then return the flag as 'N';
1012 -- It is possible that some columns may not be found in PA_ACCUM_COLUMNS
1013 -- if Project Costing is installed
1014
1015 If Get_Accum_Flag_cur%NOTFOUND Then
1016 x_accum_flag := 'N';
1017 End If;
1018 Close Get_Accum_Flag_cur;
1019 x_err_code := 0;
1020 x_err_stack := v_old_stack;
1021
1022 Exception
1023 When NO_DATA_FOUND Then
1024 x_err_code := SQLCODE;
1025 RAISE;
1026
1027 When Others Then
1028 x_err_code := SQLCODE;
1029 RAISE;
1030 End Get_Config_Option;
1031
1032 Procedure Get_existing_higher_tasks (x_project_id in Number,
1033 X_task_id in Number,
1034 X_resource_list_member_id In Number,
1038 x_err_stage In Out NOCOPY Varchar2, --File.Sql.39 bug 4440895
1035 x_task_array Out NOCOPY task_id_tabtype, --File.Sql.39 bug 4440895
1036 x_noof_tasks Out NOCOPY number, --File.Sql.39 bug 4440895
1037 x_err_stack In Out NOCOPY Varchar2, --File.Sql.39 bug 4440895
1039 x_err_code In Out NOCOPY Number ) IS --File.Sql.39 bug 4440895
1040
1041 -- Get_existing_higher_tasks - For the given task, returns all the higher level
1042 -- tasks which are available in
1043 -- PA_PROJECT_ACCUM_HEADERS .
1044
1045 Cursor Tasks_Cur is
1046 Select task_id
1047 from
1048 pa_tasks pt
1049 where project_id = x_project_id
1050 and task_id <> x_task_id
1051 and exists
1052 (select 'x'
1053 from
1054 pa_project_accum_headers pah
1055 where pah.project_id = x_project_id
1056 and pah.task_id = pt.task_id
1057 and pah.resource_list_member_id = x_resource_list_member_id)
1058 start with task_id = x_task_id
1059 connect by prior parent_task_id = task_id;
1060
1061 v_noof_tasks Number := 0;
1062
1063 Task_Rec Tasks_Cur%ROWTYPE;
1064
1065 V_Old_Stack Varchar2(630);
1066 Begin
1067 V_Old_Stack := x_err_stack;
1068 x_err_stack :=
1069 x_err_stack||'->PA_ACCUM_UTILS.Get_existing_higher_tasks';
1070 For Task_Rec IN Tasks_Cur LOOP
1071 v_noof_tasks := v_noof_tasks + 1;
1072 x_task_array(v_noof_tasks) := Task_Rec.Task_id;
1073 END LOOP;
1074 x_noof_tasks := v_noof_tasks;
1075
1076 -- Restore the old x_err_stack;
1077
1078 x_err_stack := V_Old_Stack;
1079 Exception
1080 When Others Then
1081 x_err_code := SQLCODE;
1082 RAISE ;
1083 end Get_existing_higher_tasks;
1084
1085 -- update_proj_accum_header :
1086 -- This procedure updates the accum period, once the accumulation is successful
1087 Procedure update_proj_accum_header (x_project_accum_id IN Number,
1088 x_accum_period IN Varchar2,
1089 x_err_stack IN OUT NOCOPY Varchar2, --File.Sql.39 bug 4440895
1090 x_err_stage IN OUT NOCOPY Varchar2, --File.Sql.39 bug 4440895
1091 x_err_code IN OUT NOCOPY Number ) IS --File.Sql.39 bug 4440895
1092
1093 V_old_stack Varchar2(630);
1094 P_DEBUG_MODE varchar2(1) := NVL(FND_PROFILE.value('PA_DEBUG_MODE'), 'N'); /* Added Debug Profile Option variable initialization for bug#2674619 */
1095
1096 Begin
1097 V_Old_Stack := x_err_stack;
1098 x_err_code := 0;
1099 x_err_stack :=
1100 x_err_stack||'->PA_ACCUM_UTILS.update_proj_accum_header';
1101
1102 IF P_DEBUG_MODE = 'Y' THEN /* Added Debug Profile Option Check for bug#2674619 */
1103 pa_debug.debug('update_proj_accum_header: ' || x_err_stack);
1104 END IF;
1105
1106 -- update the accum_period to current period
1107
1108 UPDATE pa_project_accum_headers
1109 SET
1110 accum_period = x_accum_period,
1111 request_id = pa_proj_accum_main.x_request_id,
1112 last_updated_by = pa_proj_accum_main.x_last_updated_by,
1113 last_update_date = TRUNC(SYSDATE),
1114 last_update_login = pa_proj_accum_main.x_last_update_login
1115 WHERE project_accum_id = x_project_accum_id;
1116
1117 -- Restore the old x_err_stack;
1118
1119 x_err_stack := v_old_stack;
1120
1121 EXCEPTION
1122 WHEN OTHERS THEN
1123 x_err_code := SQLCODE;
1124 RAISE ;
1125 End update_proj_accum_header;
1126
1127 -- update_proj_accum_header :
1128 -- This procedure updates the tasks_restructured_flag
1129 Procedure update_tasks_restructured_flag (x_project_accum_id IN Number,
1130 x_tasks_restructured_flag IN Varchar2,
1131 x_err_stack IN OUT NOCOPY Varchar2, --File.Sql.39 bug 4440895
1132 x_err_stage IN OUT NOCOPY Varchar2, --File.Sql.39 bug 4440895
1133 x_err_code IN OUT NOCOPY Number ) IS --File.Sql.39 bug 4440895
1134
1135 V_old_stack Varchar2(630);
1136 P_DEBUG_MODE varchar2(1) := NVL(FND_PROFILE.value('PA_DEBUG_MODE'), 'N'); /* Added Debug Profile Option variable initialization for bug#2674619 */
1137 Begin
1138 V_Old_Stack := x_err_stack;
1139 x_err_code := 0;
1140 x_err_stack :=
1141 x_err_stack||'->PA_ACCUM_UTILS.update_tasks_restructured_flag';
1142
1143 IF P_DEBUG_MODE = 'Y' THEN /* Added Debug Profile Option Check for bug#2674619 */
1144 pa_debug.debug('update_tasks_restructured_flag: ' || x_err_stack);
1145 END IF;
1146
1147 -- update the accum_period to current period
1148
1149 UPDATE pa_project_accum_headers
1150 SET
1151 tasks_restructured_flag = x_tasks_restructured_flag,
1152 request_id = pa_proj_accum_main.x_request_id,
1153 last_updated_by = pa_proj_accum_main.x_last_updated_by,
1154 last_update_date = TRUNC(SYSDATE),
1155 last_update_login = pa_proj_accum_main.x_last_update_login
1156 WHERE project_accum_id = x_project_accum_id;
1157
1158 -- Restore the old x_err_stack;
1159
1160 x_err_stack := v_old_stack;
1161
1162 EXCEPTION
1166 End update_tasks_restructured_flag;
1163 WHEN OTHERS THEN
1164 x_err_code := SQLCODE;
1165 RAISE ;
1167
1168 -- Check proj accum header :
1169 -- This procedure updates the tasks_restructured_flag
1170 Procedure check_tasks_restructured_flag (x_project_accum_id IN Number,
1171 x_tasks_restructured_flag IN OUT NOCOPY Varchar2, --File.Sql.39 bug 4440895
1172 x_err_stack IN OUT NOCOPY Varchar2, --File.Sql.39 bug 4440895
1173 x_err_stage IN OUT NOCOPY Varchar2, --File.Sql.39 bug 4440895
1174 x_err_code IN OUT NOCOPY Number ) IS --File.Sql.39 bug 4440895
1175
1176 V_old_stack Varchar2(630);
1177 P_DEBUG_MODE varchar2(1) := NVL(FND_PROFILE.value('PA_DEBUG_MODE'), 'N'); /* Added Debug Profile Option variable initialization for bug#2674619 */
1178 Begin
1179 V_Old_Stack := x_err_stack;
1180 x_err_code := 0;
1181 x_err_stack :=
1182 x_err_stack||'->PA_ACCUM_UTILS.check_tasks_restructured_flag';
1183
1184 IF P_DEBUG_MODE = 'Y' THEN /* Added Debug Profile Option Check for bug#2674619 */
1185 pa_debug.debug('check_tasks_restructured_flag: ' || x_err_stack);
1186 END IF;
1187
1188 -- Select the accum_period to current period
1189 SELECT
1190 NVL(tasks_restructured_flag,'N')
1191 INTO
1192 x_tasks_restructured_flag
1193 FROM
1194 pa_project_accum_headers
1195 WHERE project_accum_id = x_project_accum_id;
1196
1197 -- Restore the old x_err_stack;
1198
1199 x_err_stack := v_old_stack;
1200
1201 EXCEPTION
1202 WHEN OTHERS THEN
1203 x_err_code := SQLCODE;
1204 RAISE ;
1205 End check_tasks_restructured_flag;
1206
1207 --Name: Get_First_Accum_Period
1208 --Type: Procedure
1209 --Description: This procedure fetches attributes
1210 -- for the first pa_txn_accum period
1211 -- for a project and resource list.
1212 --
1213 --Called subprograms: none
1214 --
1215 --History:
1216 -- 01-FEB-01 jwhite Bug 1614284: Performance Fix for
1217 -- CURSOR selresaccums:
1218 -- 1) Added the following join
1219 -- AND PRAD.PROJECT_ID = PTA.PROJECT_ID
1220 -- 2) decomposed pa_periods_v; removed pa_lookup
1221 -- join.
1222 -- 24-JUL-02 rravipat Bug 2331201: Extended the procedure to use it for
1223 -- Financial Planning.Included fnctionality forthe case
1224 -- x_amount_type = 'A' which corresponds to a finplan
1225 -- having cost and rev togther.
1226 -- 13-MAY-03 gjain Bug 2922974: Split the cursor selresaccums into
1227 -- two cursors selresaccums_g and selresaccums_p
1228 -- Also revamped the entire code logic within this procedure
1229
1230 PROCEDURE get_first_accum_period
1231 (x_project_id IN NUMBER,
1232 x_resource_list_id IN NUMBER DEFAULT NULL,
1233 x_amount_type IN VARCHAR2 DEFAULT 'C',
1234 x_period_type IN VARCHAR2 DEFAULT 'P',
1235 x_period_name IN OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
1236 x_period_start_date IN OUT NOCOPY DATE, --File.Sql.39 bug 4440895
1237 x_err_code IN OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
1238 x_err_stage IN OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
1239 x_err_stack IN OUT NOCOPY VARCHAR2) --File.Sql.39 bug 4440895
1240
1241 IS
1242 /* Commented for bug 2922974
1243 CURSOR selresaccums IS
1244 SELECT DISTINCT
1245 PTA.PA_PERIOD pa_period
1246 ,PAP.START_DATE pa_start_date
1247 ,PAP.END_DATE pa_end_date
1248 ,PTA.GL_PERIOD gl_period
1249 ,GLP.START_DATE gl_start_date
1250 ,GLP.END_DATE gl_end_date
1251 ,PTA.TOT_RAW_COST tot_raw_cost
1252 ,PTA.TOT_QUANTITY tot_quantity
1253 ,PTA.TOT_REVENUE tot_revenue
1254 FROM PA_TXN_ACCUM PTA
1255 , PA_RESOURCE_ACCUM_DETAILS PRAD
1256 , PA_PERIODS PAP
1257 , GL_PERIOD_STATUSES GLP
1258 , PA_IMPLEMENTATIONS PAIMP
1259 WHERE PRAD.PROJECT_ID = x_project_id
1260 AND PRAD.RESOURCE_LIST_ID = x_resource_list_id
1261 AND PRAD.TXN_ACCUM_ID = PTA.TXN_ACCUM_ID
1262 AND PRAD.PROJECT_ID = PTA.PROJECT_ID
1263 AND PTA.PA_PERIOD = PAP.PERIOD_NAME
1264 AND PAP.GL_PERIOD_NAME = GLP.PERIOD_NAME
1265 AND GLP.SET_OF_BOOKS_ID = PAIMP.SET_OF_BOOKS_ID
1266 AND GLP.APPLICATION_ID = pa_period_process_pkg.application_id
1267 AND GLP.ADJUSTMENT_PERIOD_FLAG = 'N'
1268 ORDER BY DECODE(x_period_type,'P',PAP.START_DATE,'G',GLP.START_DATE,PAP.START_DATE);
1269 */
1270
1271 /* Addition for bug 2922974 starts */
1272 CURSOR selresaccums_g IS
1273 SELECT DISTINCT
1274 PTA.GL_PERIOD gl_period
1275 ,GLP.START_DATE gl_start_date
1276 ,GLP.END_DATE gl_end_date
1277 ,PTA.TOT_RAW_COST tot_raw_cost
1278 ,PTA.TOT_BURDENED_COST tot_burd_cost --Bug 10029571
1279 ,PTA.TOT_QUANTITY tot_quantity
1280 ,PTA.TOT_REVENUE tot_revenue
1281 FROM PA_TXN_ACCUM PTA
1282 , PA_RESOURCE_ACCUM_DETAILS PRAD
1283 , GL_PERIOD_STATUSES GLP
1284 , PA_IMPLEMENTATIONS PAIMP
1285 WHERE PRAD.PROJECT_ID = x_project_id
1289 AND PTA.GL_PERIOD = GLP.PERIOD_NAME
1286 AND PRAD.RESOURCE_LIST_ID = x_resource_list_id
1287 AND PRAD.TXN_ACCUM_ID = PTA.TXN_ACCUM_ID
1288 AND PRAD.PROJECT_ID = PTA.PROJECT_ID
1290 AND GLP.SET_OF_BOOKS_ID = PAIMP.SET_OF_BOOKS_ID
1291 AND GLP.APPLICATION_ID = pa_period_process_pkg.application_id
1292 AND GLP.ADJUSTMENT_PERIOD_FLAG = 'N'
1293 ORDER BY GLP.START_DATE;
1294
1295 CURSOR selresaccums_p IS
1296 SELECT DISTINCT
1297 PTA.PA_PERIOD pa_period
1298 ,PAP.START_DATE pa_start_date
1299 ,PAP.END_DATE pa_end_date
1300 ,PTA.TOT_RAW_COST tot_raw_cost
1301 ,PTA.TOT_BURDENED_COST tot_burd_cost --Bug 10029571
1302 ,PTA.TOT_QUANTITY tot_quantity
1303 ,PTA.TOT_REVENUE tot_revenue
1304 FROM PA_TXN_ACCUM PTA
1305 , PA_RESOURCE_ACCUM_DETAILS PRAD
1306 , PA_PERIODS PAP
1307 , GL_PERIOD_STATUSES GLP
1308 , PA_IMPLEMENTATIONS PAIMP
1309 WHERE PRAD.PROJECT_ID = x_project_id
1310 AND PRAD.RESOURCE_LIST_ID = x_resource_list_id
1311 AND PRAD.TXN_ACCUM_ID = PTA.TXN_ACCUM_ID
1312 AND PRAD.PROJECT_ID = PTA.PROJECT_ID
1313 AND PTA.PA_PERIOD = PAP.PERIOD_NAME
1314 AND PAP.GL_PERIOD_NAME = GLP.PERIOD_NAME
1315 AND GLP.SET_OF_BOOKS_ID = PAIMP.SET_OF_BOOKS_ID
1316 AND GLP.APPLICATION_ID = pa_period_process_pkg.application_id
1317 AND GLP.ADJUSTMENT_PERIOD_FLAG = 'N'
1318 ORDER BY PAP.START_DATE;
1319
1320 gresaccumrec selresaccums_g%ROWTYPE;
1321 presaccumrec selresaccums_p%ROWTYPE;
1322 /* Addition for bug 2922974 ends */
1323
1324 /* resaccumrec selresaccums%ROWTYPE; commented for bug 2922974 */
1325
1326 BEGIN
1327 x_err_code := 0;
1328 x_err_stage := 'Getting the Project First Accumlation Period';
1329
1330 x_period_name := NULL;
1331 x_period_start_date := NULL;
1332
1333 /* commented for bug 2922974
1334 FOR resaccumrec IN selresaccums LOOP
1335
1336 IF (x_amount_type = 'C') THEN
1337 IF (resaccumrec.tot_raw_cost <> 0 OR resaccumrec.tot_quantity <> 0) THEN
1338 IF (x_period_type = 'P') THEN
1339 x_period_name := resaccumrec.pa_period;
1340 x_period_start_date := resaccumrec.pa_start_date;
1341 ELSIF (x_period_type = 'G') THEN
1342 x_period_name := resaccumrec.gl_period;
1343 x_period_start_date := resaccumrec.gl_start_date;
1344 END IF;
1345 EXIT; -- Exit the loop immediately, since the cursor has a sort order
1346 END IF;
1347 ELSIF (x_amount_type = 'R') THEN
1348 IF (resaccumrec.tot_revenue <> 0 ) THEN
1349 IF (x_period_type = 'P') THEN
1350 x_period_name := resaccumrec.pa_period;
1351 x_period_start_date := resaccumrec.pa_start_date;
1352 ELSIF (x_period_type = 'G') THEN
1353 x_period_name := resaccumrec.gl_period;
1354 x_period_start_date := resaccumrec.gl_start_date;
1355 END IF;
1356 EXIT; -- Exit the loop immediately, since the cursor has a sort order
1357 END IF;
1358 --Start of changes Bug: 2331201 For Financial Planning
1359 --This enahancement is being done to include the case where financial plan
1360 --is cost and revenue together in financial planning
1361 ELSIF (x_amount_type = 'A') THEN
1362
1363 IF (resaccumrec.tot_raw_cost <> 0 OR resaccumrec.tot_quantity <> 0
1364 OR resaccumrec.tot_revenue <> 0) THEN
1365 IF (x_period_type = 'P') THEN
1366 x_period_name := resaccumrec.pa_period;
1367 x_period_start_date := resaccumrec.pa_start_date;
1368 ELSIF (x_period_type = 'G') THEN
1369 x_period_name := resaccumrec.gl_period;
1370 x_period_start_date := resaccumrec.gl_start_date;
1371 END IF;
1372 EXIT; -- Exit the loop immediately, since the cursor has a sort order
1373 END IF;
1374 --End of changes Bug: 2331201 For Financial Planning
1375 END IF; -- IF (x_amount_type = 'C') THEN
1376 END LOOP; -- FOR resaccumrec IN selresaccums LOOP
1377 */
1378
1379 /* addition for bug 2922974 starts */
1380 If x_period_type = 'P' then
1381 FOR presaccumrec IN selresaccums_p
1382 LOOP
1383 IF (x_amount_type = 'C') THEN
1384 IF (presaccumrec.tot_raw_cost <> 0 OR presaccumrec.tot_quantity <> 0
1385 OR presaccumrec.tot_burd_cost <> 0) THEN --Bug 10029571
1386 x_period_name := presaccumrec.pa_period;
1387 x_period_start_date := presaccumrec.pa_start_date;
1388 EXIT; -- Exit the loop immediately, since the cursor has a sort order
1389 END IF;
1390 ELSIF (x_amount_type = 'R') THEN
1391 IF (presaccumrec.tot_revenue <> 0 ) THEN
1392 x_period_name := presaccumrec.pa_period;
1393 x_period_start_date := presaccumrec.pa_start_date;
1394 EXIT; -- Exit the loop immediately, since the cursor has a sort order
1395 END IF;
1396 ELSIF (x_amount_type = 'A') THEN
1397 IF (presaccumrec.tot_raw_cost <> 0 OR presaccumrec.tot_quantity <> 0
1398 OR presaccumrec.tot_revenue <> 0 OR presaccumrec.tot_burd_cost <> 0) THEN --Bug 10029571
1399 x_period_name := presaccumrec.pa_period;
1400 x_period_start_date := presaccumrec.pa_start_date;
1401 EXIT; -- Exit the loop immediately, since the cursor has a sort order
1402 END IF;
1403 END IF;
1404 END LOOP;
1405 elsif x_period_type = 'G' then
1406 FOR gresaccumrec IN selresaccums_g
1407 LOOP
1408 IF (x_amount_type = 'C') THEN
1409 IF (gresaccumrec.tot_raw_cost <> 0 OR gresaccumrec.tot_quantity <> 0
1413 EXIT; -- Exit the loop immediately, since the cursor has a sort order
1410 OR gresaccumrec.tot_burd_cost <> 0 ) THEN --Bug 10029571
1411 x_period_name := gresaccumrec.gl_period;
1412 x_period_start_date := gresaccumrec.gl_start_date;
1414 END IF;
1415 ELSIF (x_amount_type = 'R') THEN
1416 IF (gresaccumrec.tot_revenue <> 0 ) THEN
1417 x_period_name := gresaccumrec.gl_period;
1418 x_period_start_date := gresaccumrec.gl_start_date;
1419 EXIT; -- Exit the loop immediately, since the cursor has a sort order
1420 END IF;
1421 ELSIF (x_amount_type = 'A') THEN
1422 IF (gresaccumrec.tot_raw_cost <> 0 OR gresaccumrec.tot_quantity <> 0
1423 OR gresaccumrec.tot_revenue <> 0 OR gresaccumrec.tot_burd_cost <> 0 ) THEN --Bug 10029571
1424 x_period_name := gresaccumrec.gl_period;
1425 x_period_start_date := gresaccumrec.gl_start_date;
1426 EXIT; -- Exit the loop immediately, since the cursor has a sort order
1427 END IF;
1428 END IF;
1429 END LOOP;
1430 end if;
1431 /* addition for bug 2922974 ends */
1432
1433 EXCEPTION
1434 WHEN OTHERS THEN
1435 x_err_code := SQLCODE;
1436 RAISE;
1437 END get_first_accum_period;
1438
1439 PROCEDURE set_check_reporting_end_date
1440 ( x_period_name IN VARCHAR2)
1441 IS
1442 lcl_end_date date;
1443 BEGIN
1444 -- Return the end date of the passed period and set the global variable
1445 select decode( pai.accumulation_period_type, 'PA', pav.pa_end_date,
1446 'GL', pav.gl_end_date )
1447 into lcl_end_date
1448 from PA_PERIODS_V pav, pa_implementations pai
1449 where period_name = x_period_name;
1450
1451 pa_accum_utils.g_check_reporting_end_date := lcl_end_date;
1452
1453 END set_check_reporting_end_date;
1454
1455 FUNCTION get_check_reporting_end_date
1456 return date
1457 IS
1458 lcl_end_date date;
1459 BEGIN
1460
1461 -- Check the global variable for a reporting period. If none is set,
1462 -- return the current reporting period
1463 IF pa_accum_utils.g_check_reporting_end_date is null
1464 THEN
1465 /* Bug 2634995 begins */
1466 --Replaced the pa_periods_v with the view definition
1467 -- select decode( pai.accumulation_period_type, 'PA', pav.pa_end_date,
1468 -- 'GL', pav.gl_end_date )
1469 -- into lcl_end_date
1470 -- from PA_PERIODS_V pav, pa_implementations pai
1471 -- where current_pa_period_flag = 'Y';
1472 select decode(paimp.accumulation_period_type, 'PA', pap.end_date,
1473 'GL', glp.end_date)
1474 into lcl_end_date
1475 FROM PA_PERIODS PAP, GL_PERIOD_STATUSES GLP,
1476 PA_IMPLEMENTATIONS PAIMP, PA_LOOKUPS PAL
1477 WHERE PAP.GL_PERIOD_NAME = GLP.PERIOD_NAME
1478 AND GLP.SET_OF_BOOKS_ID = PAIMP.SET_OF_BOOKS_ID
1479 AND GLP.APPLICATION_ID = Pa_Period_Process_Pkg.Application_id
1480 AND GLP.ADJUSTMENT_PERIOD_FLAG = 'N'
1481 AND PAL.LOOKUP_TYPE = 'CLOSING STATUS'
1482 AND PAL.LOOKUP_CODE = PAP.STATUS
1483 AND PAP.current_pa_period_flag = 'Y' ;
1484 /* Bug 2634995 ends */
1485
1486 return lcl_end_date;
1487 ELSE
1488 return pa_accum_utils.g_check_reporting_end_date;
1489 END IF;
1490
1491 return pa_accum_utils.g_check_reporting_end_date;
1492 END get_check_reporting_end_date;
1493
1494
1495 Procedure Set_current_period_Info IS
1496 P_DEBUG_MODE varchar2(1) := NVL(FND_PROFILE.value('PA_DEBUG_MODE'), 'N'); /* Added Debug Profile Option variable initialization for bug#2674619 */
1497 Begin
1498 IF P_DEBUG_MODE = 'Y' THEN /* Added Debug Profile Option Check for bug#2674619 */
1499 pa_debug.debug('pa_accum_utils.set_current_period_info');
1500 END IF;
1501
1502 SELECT
1503 period_name,
1504 gl_period_name
1505 INTO
1506 g_current_pa_period,
1507 g_current_gl_period
1508 FROM pa_periods
1509 WHERE
1510 current_pa_period_flag = 'Y';
1511
1512 Exception
1513 WHEN NO_DATA_FOUND THEN
1514 IF P_DEBUG_MODE = 'Y' THEN /* Added Debug Profile Option Check for bug#2674619 */
1515 pa_debug.debug('Data not found to set g_current_pa_period and g_current
1516 _gl_period',PA_DEBUG.DEBUG_LEVEL_EXCEPTION);
1517 END IF;
1518 RAISE;
1519 WHEN OTHERS THEN
1520 RAISE;
1521 End Set_current_period_info;
1522
1523 FUNCTION Get_current_pa_period
1524 return varchar2
1525 IS
1526 l_current_pa_period varchar2(20);
1527 BEGIN
1528
1529
1530 -- Return the value in global variable g_current_pa_period,if it is set.
1531 -- If g_current_pa_period is not set,fetch the current pa period from
1532 -- the database
1533
1534 IF pa_accum_utils.g_current_pa_period is null
1535 THEN
1536 SELECT
1537 period_name
1538 INTO
1539 l_current_pa_period
1540 FROM pa_periods
1541 WHERE
1542 current_pa_period_flag = 'Y';
1543 ELSE
1544 l_current_pa_period := pa_accum_utils.g_current_pa_period;
1545 END IF;
1546
1547 return l_current_pa_period;
1548
1549 Exception
1550 WHEN OTHERS THEN
1551 RAISE;
1552
1556 return varchar2
1553 END Get_current_pa_period;
1554
1555 FUNCTION Get_current_gl_period
1557 IS
1558 l_current_gl_period varchar2(15);
1559 BEGIN
1560
1561 -- Return the value in global variable g_current_gl_period,if it is set.
1562 -- If g_current_gl_period is not set,fetch the current gl period from
1563 -- the database
1564
1565 IF pa_accum_utils.g_current_gl_period is null
1566 THEN
1567 SELECT
1568 gl_period_name
1569 INTO
1570 l_current_gl_period
1571 FROM pa_periods
1572 WHERE
1573 current_pa_period_flag = 'Y';
1574 ELSE
1575 l_current_gl_period := pa_accum_utils.g_current_gl_period;
1576 END IF;
1577
1578 return l_current_gl_period;
1579
1580 Exception
1581 WHEN OTHERS THEN
1582 RAISE;
1583
1584 END Get_current_gl_period;
1585
1586 -- Function Get_spread_amount_val
1587 -- Budget amount will have to be spread across the various time periods,
1588 -- i.e., current period (for PTD), previous period for (PP) and
1589 -- current year (for YTD)
1590 -- This function returns the spread_amount value (tmp_amt_returned) of
1591 -- the amount passed to it (x_amt_to_be_spread) as a parameter.
1592 -- i.e., x_amt_to_be_spread can be either raw_cost or burdened_cost or
1593 -- revenue or quantity or labor_quantity.
1594 -- x_which_date_flag parameter can either be 'C' or 'P' or 'Y' or 'I'.
1595 -- These stand for Current period, Prior period, current Year,
1596 -- Inception to date.
1597
1598 FUNCTION Get_spread_amount_val
1599 (x_from_date IN DATE,
1600 x_to_date IN DATE,
1601 x_amt_to_be_spread IN NUMBER,
1602 x_which_date_flag IN VARCHAR2)
1603
1604 RETURN NUMBER
1605 IS
1606 tmp_amt_returned NUMBER := 0;
1607
1608 BEGIN
1609
1610 IF x_which_date_flag = 'C' THEN
1611
1612 -- PTD
1613 -- Budget End Date >= Period Start date and Budget Start Date
1614 -- <= Period End Date
1615
1616 IF x_to_date >= PA_PROJ_ACCUM_MAIN.x_current_start_date AND
1617 x_from_date <= PA_PROJ_ACCUM_MAIN.x_current_end_date THEN
1618
1619 tmp_amt_returned := PA_MISC.spread_amount('L', x_from_date, x_to_date,
1620 PA_PROJ_ACCUM_MAIN.x_current_start_date,
1621 PA_PROJ_ACCUM_MAIN.x_current_end_date, x_amt_to_be_spread);
1622
1623 END IF;
1624
1625 ELSIF x_which_date_flag = 'P' THEN
1626
1627 -- PP
1628 -- Budget End Date >= Period Start date and Budget Start Date
1629 -- <= Period End Date
1630
1631 IF x_to_date >= PA_PROJ_ACCUM_MAIN.x_prev_start_date AND
1632 x_from_date <= PA_PROJ_ACCUM_MAIN.x_prev_end_date THEN
1633
1634 tmp_amt_returned := PA_MISC.spread_amount('L', x_from_date, x_to_date,
1635 PA_PROJ_ACCUM_MAIN.x_prev_start_date,
1636 PA_PROJ_ACCUM_MAIN.x_prev_end_date, x_amt_to_be_spread);
1637
1638 END IF;
1639
1640 ELSIF x_which_date_flag = 'Y' THEN
1641
1642 -- YTD
1643 -- NOT (Budget End Date < Year Start Date OR Budget Start Date > Year End Date)
1644
1645 IF NOT (x_to_date < PA_PROJ_ACCUM_MAIN.x_period_yr_start_date OR
1646 x_from_date > PA_PROJ_ACCUM_MAIN.x_period_yr_end_date) THEN
1647
1648 tmp_amt_returned := PA_MISC.spread_amount('L', x_from_date, x_to_date,
1649 PA_PROJ_ACCUM_MAIN.x_period_yr_start_date,
1650 PA_PROJ_ACCUM_MAIN.x_period_yr_end_date, x_amt_to_be_spread);
1651
1652 END IF;
1653
1654 ELSIF x_which_date_flag = 'I' THEN
1655
1656 -- ITD
1657 -- NOT (Budget Start Date > Period End Date AND Budget End Date >
1658 -- Period Start Date)
1659
1660 IF NOT (x_from_date > PA_PROJ_ACCUM_MAIN.x_current_end_date) THEN
1661
1662 tmp_amt_returned := PA_MISC.spread_amount('L', x_from_date, x_to_date,
1663 x_from_date, PA_PROJ_ACCUM_MAIN.x_current_end_date, x_amt_to_be_spread);
1664
1665 END IF;
1666
1667 END IF;
1668
1669 RETURN tmp_amt_returned ;
1670
1671 Exception
1672 WHEN OTHERS THEN
1673 RAISE;
1674
1675 END Get_spread_amount_val;
1676
1677 -- /*--------------------------------------------------------*/
1678 -- Three new functions added as a part of Project Allocation
1679 -- Summarization Enhancement changes
1680 -- /*--------------------------------------------------------*/
1681
1682 Function Get_Grouping_Id
1683 Return Number
1684 Is
1685 Group_id Number := NULL;
1686
1687 Begin
1688 If pa_accum_utils.G_grouping_id Is Not Null Then
1689 Group_id := pa_accum_utils.G_grouping_id;
1690 End If;
1691
1692 Return Group_id;
1693
1694 Exception
1695 When No_Data_Found Then
1696 Return Group_id;
1697 When Others Then
1698 Raise;
1699
1700 End Get_Grouping_Id;
1701
1702
1703 Function Get_Context_Info
1704 Return Varchar2
1705 Is
1706 Summ_context Varchar2(25) := NULL;
1707
1708 Begin
1709 If pa_accum_utils.G_context Is Not Null Then
1710 Summ_context := pa_accum_utils.G_context;
1711 End If;
1712
1713 Return Summ_context;
1714
1715 Exception
1716 When No_Data_Found Then
1717 Return Summ_context;
1718 When Others Then
1719 Raise;
1723
1720
1721 End Get_Context_Info;
1722
1724 Function Get_Project_Info
1725 (x_From_Or_To IN VARCHAR2)
1726 Return Varchar2
1727 Is
1728 Proj_num Varchar2(25) := NULL;
1729 Begin
1730
1731 If x_From_Or_To = 'F' Then -- From which project
1732 If pa_accum_utils.G_start_proj Is Not Null Then
1733 Proj_num := pa_accum_utils.G_start_proj;
1734 End If;
1735 Else -- Till which project
1736 If pa_accum_utils.G_end_proj Is Not Null Then
1737 Proj_num := pa_accum_utils.G_end_proj;
1738 End If;
1739 End If;
1740
1741 Return Proj_num;
1742
1743 Exception
1744 When No_Data_Found Then
1745 Return Proj_num;
1746 When Others Then
1747 Raise;
1748
1749 End Get_Project_Info;
1750
1751 -- /*--------------------------------------------------------*/
1752 -- End of Project Allocation Summarization Enhancement changes
1753 -- /*--------------------------------------------------------*/
1754
1755 -- /*--------------------------------------------------------*/
1756 -- Start of code added for performance issue 3653978
1757 -- /*--------------------------------------------------------*/
1758
1759 Procedure Get_pa_period_Info1 (x_impl_opt In Varchar2,
1760 x_current_pa_start_date In Date,
1761 x_current_gl_start_date In Date,
1762 x_prev_pa_period Out NOCOPY Varchar2, --File.Sql.39 bug 4440895
1763 x_prev_gl_period Out NOCOPY Varchar2, --File.Sql.39 bug 4440895
1764 x_prev_pa_year Out NOCOPY Number, --File.Sql.39 bug 4440895
1765 x_prev_gl_year Out NOCOPY Number, --File.Sql.39 bug 4440895
1766 x_prev_pa_start_date Out NOCOPY Date, --File.Sql.39 bug 4440895
1767 x_prev_pa_end_date Out NOCOPY Date, --File.Sql.39 bug 4440895
1768 x_prev_gl_start_date Out NOCOPY Date, --File.Sql.39 bug 4440895
1769 x_prev_gl_end_date Out NOCOPY Date, --File.Sql.39 bug 4440895
1770 x_err_stack In Out NOCOPY Varchar2, --File.Sql.39 bug 4440895
1771 x_err_stage In Out NOCOPY Varchar2, --File.Sql.39 bug 4440895
1772 x_err_code In Out NOCOPY Number ) IS --File.Sql.39 bug 4440895
1773
1774 V_Old_Stack Varchar2(630);
1775 P_DEBUG_MODE varchar2(1) := NVL(FND_PROFILE.value('PA_DEBUG_MODE'), 'N');
1776 BEGIN
1777 V_Old_Stack := x_err_stack;
1778 x_err_stack :=
1779 x_err_stack||'->PA_ACCUM_UTILS.Get_pa_period_Info1';
1780
1781 -- Select the details pertaining to the previous pa period.
1782
1783 IF P_DEBUG_MODE = 'Y' THEN
1784 pa_debug.debug('Get_pa_period_Info1: ' || x_err_stack);
1785 END IF;
1786
1787 <<prev_pa_period>>
1788 BEGIN
1789 SELECT
1790 PERIOD_NAME,
1791 PERIOD_YEAR,
1792 PA_START_DATE,
1793 PA_END_DATE
1794 INTO
1795 x_prev_pa_period,
1796 x_prev_pa_year,
1797 x_prev_pa_start_date,
1798 x_prev_pa_end_date
1799 FROM
1800 PA_PERIODS_V
1801 WHERE pa_start_date =
1802 (SELECT max(start_date)
1803 FROM
1804 pa_periods
1805 WHERE start_date < x_current_pa_start_date);
1806
1807 EXCEPTION
1808 WHEN NO_DATA_FOUND THEN
1809 -- The current pa_period is the first period defined
1810 x_prev_pa_period := NULL;
1811 x_prev_pa_year := NULL;
1812 x_prev_pa_start_date := NULL;
1813 x_prev_pa_end_date := NULL;
1814
1815 WHEN OTHERS THEN
1816 x_err_code := SQLCODE;
1817 RAISE;
1818 END prev_pa_period;
1819
1820 -- Select the details pertaining to the previous gl period.
1821
1822 <<prev_gl_period>>
1823 BEGIN
1824
1825 SELECT
1826 DISTINCT gl_period_name,
1827 period_year,
1828 gl_start_date,
1829 gl_end_date
1830 INTO
1831 x_prev_gl_period,
1832 x_prev_gl_year,
1833 x_prev_gl_start_date,
1834 x_prev_gl_end_date
1835 FROM
1836 pa_periods_v
1837 WHERE
1838 gl_start_date =
1839 (SELECT max(gl_start_date)
1840 FROM pa_periods_v
1841 WHERE
1842 gl_start_date < x_current_gl_start_date);
1843
1844 EXCEPTION
1845 WHEN NO_DATA_FOUND THEN
1846 -- current gl_period is the first period defined
1847 x_prev_gl_period := NULL;
1848 x_prev_gl_year := NULL;
1849 x_prev_gl_start_date := NULL;
1850 x_prev_gl_end_date := NULL;
1851 WHEN OTHERS THEN
1852 x_err_code := SQLCODE;
1853 RAISE;
1854 END prev_gl_period;
1855
1856 -- Restore the old x_err_stack;
1857 x_err_stack := V_Old_Stack;
1858 Exception
1859 When Others Then
1860 x_err_code := SQLCODE;
1861 RAISE ;
1862 End Get_pa_period_Info1;
1863
1864
1865 Procedure Get_pa_period_Info2 (x_impl_opt In Varchar2,
1866 x_prev_accum_period in Varchar2,
1867 x_prev_accum_year Out NOCOPY number, --File.Sql.39 bug 4440895
1871 x_accum_period_type_changed IN OUT NOCOPY BOOLEAN, --File.Sql.39 bug 4440895
1868 x_prev_accum_start_date In Out NOCOPY Date, --File.Sql.39 bug 4440895
1869 x_prev_accum_end_date Out NOCOPY Date, --File.Sql.39 bug 4440895
1870 x_prev_prev_accum_period Out NOCOPY Varchar2, --File.Sql.39 bug 4440895
1872 x_err_stack In Out NOCOPY Varchar2, --File.Sql.39 bug 4440895
1873 x_err_stage In Out NOCOPY Varchar2, --File.Sql.39 bug 4440895
1874 x_err_code In Out NOCOPY Number ) IS --File.Sql.39 bug 4440895
1875
1876 V_Old_Stack Varchar2(630);
1877 P_DEBUG_MODE varchar2(1) := NVL(FND_PROFILE.value('PA_DEBUG_MODE'), 'N');
1878 BEGIN
1879 V_Old_Stack := x_err_stack;
1880 x_err_stack :=
1881 x_err_stack||'->PA_ACCUM_UTILS.Get_pa_period_Info2';
1882 x_accum_period_type_changed := FALSE;
1883
1884 IF P_DEBUG_MODE = 'Y' THEN
1885 pa_debug.debug('Get_pa_period_Info2: ' || x_err_stack);
1886 END IF;
1887
1888 BEGIN
1889
1890 If x_impl_opt = 'PA' Then
1891 Select PERIOD_YEAR,PA_START_DATE,PA_END_DATE
1892 into x_prev_accum_year,x_prev_accum_start_date,
1893 x_prev_accum_end_date from
1894 PA_PERIODS_V WHERE Period_name = x_prev_accum_period;
1895 Elsif
1896 x_impl_opt = 'GL' Then
1897 Select Distinct PERIOD_YEAR,GL_START_DATE,GL_END_DATE
1898 into x_prev_accum_year,x_prev_accum_start_date,
1899 x_prev_accum_end_date from
1900 PA_PERIODS_V WHERE Gl_Period_name = x_prev_accum_period;
1901 End If;
1902
1903 EXCEPTION
1904 WHEN NO_DATA_FOUND THEN
1905 -- Accumulation period type must have changed
1906 -- Bug #572031
1907 x_accum_period_type_changed := TRUE;
1908 END;
1909
1910 IF (x_accum_period_type_changed = FALSE) THEN
1911
1912 -- Now get x_prev_prev_accum_period
1913 <<prev_prev_accum_period>>
1914 BEGIN
1915 If x_impl_opt = 'PA' Then
1916 SELECT DISTINCT PERIOD_NAME
1917 INTO x_prev_prev_accum_period
1918 FROM
1919 pa_periods_v
1920 WHERE
1921 pa_start_date =
1922 (SELECT max(pa_start_date)
1923 FROM pa_periods_v
1924 WHERE pa_start_date < x_prev_accum_start_date);
1925 Elsif
1926 x_impl_opt = 'GL' Then
1927 SELECT DISTINCT GL_PERIOD_NAME
1928 INTO x_prev_prev_accum_period
1929 FROM
1930 pa_periods_v
1931 WHERE gl_start_date =
1932 (SELECT max(gl_start_date)
1933 FROM pa_periods_v
1934 WHERE gl_start_date < x_prev_accum_start_date);
1935 End If;
1936
1937 EXCEPTION
1938 WHEN NO_DATA_FOUND THEN
1939 x_prev_prev_accum_period := NULL;
1940 WHEN OTHERS THEN
1941 x_err_code := SQLCODE;
1942 RAISE;
1943 END prev_prev_accum_period;
1944 END IF; -- (x_accum_period_type_changed = FALSE)
1945
1946 -- Restore the old x_err_stack;
1947 x_err_stack := V_Old_Stack;
1948 Exception
1949 When Others Then
1950 x_err_code := SQLCODE;
1951 RAISE ;
1952 End Get_pa_period_Info2;
1953
1954
1955 -- /*--------------------------------------------------------*/
1956 -- End of code added for performance issue 3653978
1957 -- /*--------------------------------------------------------*/
1958
1959 END pa_accum_utils;
[Home] [Help]