1 PACKAGE body PA_ACCUM_UTILS AS
2 /* $Header: PAACUTIB.pls 120.1 2005/08/19 16:15:02 mwasowic noship $ */
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);
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;
173 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;
277 x_err_code := SQLCODE;
274 x_err_code := SQLCODE;
275 RAISE;
276 WHEN OTHERS THEN
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,
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
296 x_prev_gl_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
401 If x_impl_opt = 'PA' Then
398 If x_prev_accum_period is not Null Then
399 BEGIN
400
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
421 IF (x_accum_period_type_changed = FALSE) THEN
422
423 -- Now get x_prev_prev_accum_period
424 <<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
520 V_recs_processed Number := 0;
517 -- the hierarchy
518
519 V_Accum_id 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
552 -- the Actuals Detail table
553
554 If Get_Accum_Id_Cur%FOUND Then
555 Insert into PA_PROJECT_ACCUM_ACTUALS (
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);
638 End If; -- (v_recs_processed = 1 and x_Task_id <> 0)
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)
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
664 -- but no corresponding detail record. This procedure
665 -- creates the detail records for all the tasks in
666 -- the hierarchy
667
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,
761 Where Project_Id = x_project_id and Task_id = v_task_array(i) and
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
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
790 -- combination in the PA_PROJECT_ACCUM_HEADERS table,
791 -- checks for detail records in
792 -- PA_PROJECT_ACCUM_COMMITMENTS table. It is possible
793 -- that the Headers table might have a record
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;
872 --(if the task id <> 0,
869 V_noof_Tasks := 0;
870
871 --Now get all the higher task ids for the current task
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,
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,
912 BASE_RAW_COST_TOT,BASE_BURDENED_COST_TOT,ORIG_RAW_COST_TOT,
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);
987 x_Accum_flag := NULL;
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;
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,
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
1038 x_err_stage 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
1118
1115 WHERE project_accum_id = x_project_accum_id;
1116
1117 -- Restore the old x_err_stack;
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
1163 WHEN OTHERS THEN
1164 x_err_code := SQLCODE;
1165 RAISE ;
1166 End update_tasks_restructured_flag;
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,
1236 x_period_start_date IN OUT NOCOPY DATE, --File.Sql.39 bug 4440895
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
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_QUANTITY tot_quantity
1279 ,PTA.TOT_REVENUE tot_revenue
1280 FROM PA_TXN_ACCUM PTA
1281 , PA_RESOURCE_ACCUM_DETAILS PRAD
1282 , GL_PERIOD_STATUSES GLP
1283 , PA_IMPLEMENTATIONS PAIMP
1284 WHERE PRAD.PROJECT_ID = x_project_id
1285 AND PRAD.RESOURCE_LIST_ID = x_resource_list_id
1286 AND PRAD.TXN_ACCUM_ID = PTA.TXN_ACCUM_ID
1287 AND PRAD.PROJECT_ID = PTA.PROJECT_ID
1288 AND PTA.GL_PERIOD = GLP.PERIOD_NAME
1289 AND GLP.SET_OF_BOOKS_ID = PAIMP.SET_OF_BOOKS_ID
1290 AND GLP.APPLICATION_ID = pa_period_process_pkg.application_id
1291 AND GLP.ADJUSTMENT_PERIOD_FLAG = 'N'
1292 ORDER BY GLP.START_DATE;
1293
1294 CURSOR selresaccums_p IS
1295 SELECT DISTINCT
1296 PTA.PA_PERIOD pa_period
1297 ,PAP.START_DATE pa_start_date
1298 ,PAP.END_DATE pa_end_date
1299 ,PTA.TOT_RAW_COST tot_raw_cost
1300 ,PTA.TOT_QUANTITY tot_quantity
1301 ,PTA.TOT_REVENUE tot_revenue
1302 FROM PA_TXN_ACCUM PTA
1303 , PA_RESOURCE_ACCUM_DETAILS PRAD
1304 , PA_PERIODS PAP
1305 , GL_PERIOD_STATUSES GLP
1306 , PA_IMPLEMENTATIONS PAIMP
1307 WHERE PRAD.PROJECT_ID = x_project_id
1308 AND PRAD.RESOURCE_LIST_ID = x_resource_list_id
1309 AND PRAD.TXN_ACCUM_ID = PTA.TXN_ACCUM_ID
1310 AND PRAD.PROJECT_ID = PTA.PROJECT_ID
1311 AND PTA.PA_PERIOD = PAP.PERIOD_NAME
1312 AND PAP.GL_PERIOD_NAME = GLP.PERIOD_NAME
1313 AND GLP.SET_OF_BOOKS_ID = PAIMP.SET_OF_BOOKS_ID
1314 AND GLP.APPLICATION_ID = pa_period_process_pkg.application_id
1315 AND GLP.ADJUSTMENT_PERIOD_FLAG = 'N'
1316 ORDER BY PAP.START_DATE;
1317
1318 gresaccumrec selresaccums_g%ROWTYPE;
1319 presaccumrec selresaccums_p%ROWTYPE;
1320 /* Addition for bug 2922974 ends */
1321
1322 /* resaccumrec selresaccums%ROWTYPE; commented for bug 2922974 */
1323
1324 BEGIN
1325 x_err_code := 0;
1326 x_err_stage := 'Getting the Project First Accumlation Period';
1327
1328 x_period_name := NULL;
1329 x_period_start_date := NULL;
1330
1331 /* commented for bug 2922974
1332 FOR resaccumrec IN selresaccums LOOP
1333
1334 IF (x_amount_type = 'C') THEN
1335 IF (resaccumrec.tot_raw_cost <> 0 OR resaccumrec.tot_quantity <> 0) THEN
1336 IF (x_period_type = 'P') THEN
1337 x_period_name := resaccumrec.pa_period;
1338 x_period_start_date := resaccumrec.pa_start_date;
1339 ELSIF (x_period_type = 'G') THEN
1340 x_period_name := resaccumrec.gl_period;
1341 x_period_start_date := resaccumrec.gl_start_date;
1342 END IF;
1343 EXIT; -- Exit the loop immediately, since the cursor has a sort order
1344 END IF;
1345 ELSIF (x_amount_type = 'R') THEN
1346 IF (resaccumrec.tot_revenue <> 0 ) THEN
1347 IF (x_period_type = 'P') THEN
1348 x_period_name := resaccumrec.pa_period;
1349 x_period_start_date := resaccumrec.pa_start_date;
1350 ELSIF (x_period_type = 'G') THEN
1351 x_period_name := resaccumrec.gl_period;
1352 x_period_start_date := resaccumrec.gl_start_date;
1353 END IF;
1354 EXIT; -- Exit the loop immediately, since the cursor has a sort order
1355 END IF;
1359 ELSIF (x_amount_type = 'A') THEN
1356 --Start of changes Bug: 2331201 For Financial Planning
1357 --This enahancement is being done to include the case where financial plan
1358 --is cost and revenue together in financial planning
1360
1361 IF (resaccumrec.tot_raw_cost <> 0 OR resaccumrec.tot_quantity <> 0
1362 OR resaccumrec.tot_revenue <> 0) THEN
1363 IF (x_period_type = 'P') THEN
1364 x_period_name := resaccumrec.pa_period;
1365 x_period_start_date := resaccumrec.pa_start_date;
1366 ELSIF (x_period_type = 'G') THEN
1367 x_period_name := resaccumrec.gl_period;
1368 x_period_start_date := resaccumrec.gl_start_date;
1369 END IF;
1370 EXIT; -- Exit the loop immediately, since the cursor has a sort order
1371 END IF;
1372 --End of changes Bug: 2331201 For Financial Planning
1373 END IF; -- IF (x_amount_type = 'C') THEN
1374 END LOOP; -- FOR resaccumrec IN selresaccums LOOP
1375 */
1376
1377 /* addition for bug 2922974 starts */
1378 If x_period_type = 'P' then
1379 FOR presaccumrec IN selresaccums_p
1380 LOOP
1381 IF (x_amount_type = 'C') THEN
1382 IF (presaccumrec.tot_raw_cost <> 0 OR presaccumrec.tot_quantity <> 0) THEN
1383 x_period_name := presaccumrec.pa_period;
1384 x_period_start_date := presaccumrec.pa_start_date;
1385 EXIT; -- Exit the loop immediately, since the cursor has a sort order
1386 END IF;
1387 ELSIF (x_amount_type = 'R') THEN
1388 IF (presaccumrec.tot_revenue <> 0 ) THEN
1389 x_period_name := presaccumrec.pa_period;
1390 x_period_start_date := presaccumrec.pa_start_date;
1391 EXIT; -- Exit the loop immediately, since the cursor has a sort order
1392 END IF;
1393 ELSIF (x_amount_type = 'A') THEN
1394 IF (presaccumrec.tot_raw_cost <> 0 OR presaccumrec.tot_quantity <> 0
1395 OR presaccumrec.tot_revenue <> 0) THEN
1396 x_period_name := presaccumrec.pa_period;
1397 x_period_start_date := presaccumrec.pa_start_date;
1398 EXIT; -- Exit the loop immediately, since the cursor has a sort order
1399 END IF;
1400 END IF;
1401 END LOOP;
1402 elsif x_period_type = 'G' then
1403 FOR gresaccumrec IN selresaccums_g
1404 LOOP
1405 IF (x_amount_type = 'C') THEN
1406 IF (gresaccumrec.tot_raw_cost <> 0 OR gresaccumrec.tot_quantity <> 0) THEN
1407 x_period_name := gresaccumrec.gl_period;
1408 x_period_start_date := gresaccumrec.gl_start_date;
1409 EXIT; -- Exit the loop immediately, since the cursor has a sort order
1410 END IF;
1411 ELSIF (x_amount_type = 'R') THEN
1412 IF (gresaccumrec.tot_revenue <> 0 ) THEN
1413 x_period_name := gresaccumrec.gl_period;
1414 x_period_start_date := gresaccumrec.gl_start_date;
1415 EXIT; -- Exit the loop immediately, since the cursor has a sort order
1416 END IF;
1417 ELSIF (x_amount_type = 'A') THEN
1418 IF (gresaccumrec.tot_raw_cost <> 0 OR gresaccumrec.tot_quantity <> 0
1419 OR gresaccumrec.tot_revenue <> 0) THEN
1420 x_period_name := gresaccumrec.gl_period;
1421 x_period_start_date := gresaccumrec.gl_start_date;
1422 EXIT; -- Exit the loop immediately, since the cursor has a sort order
1423 END IF;
1424 END IF;
1425 END LOOP;
1426 end if;
1427 /* addition for bug 2922974 ends */
1428
1429 EXCEPTION
1430 WHEN OTHERS THEN
1431 x_err_code := SQLCODE;
1432 RAISE;
1433 END get_first_accum_period;
1434
1435 PROCEDURE set_check_reporting_end_date
1436 ( x_period_name IN VARCHAR2)
1437 IS
1438 lcl_end_date date;
1439 BEGIN
1440 -- Return the end date of the passed period and set the global variable
1441 select decode( pai.accumulation_period_type, 'PA', pav.pa_end_date,
1442 'GL', pav.gl_end_date )
1443 into lcl_end_date
1444 from PA_PERIODS_V pav, pa_implementations pai
1445 where period_name = x_period_name;
1446
1447 pa_accum_utils.g_check_reporting_end_date := lcl_end_date;
1448
1449 END set_check_reporting_end_date;
1450
1451 FUNCTION get_check_reporting_end_date
1452 return date
1453 IS
1454 lcl_end_date date;
1455 BEGIN
1456
1457 -- Check the global variable for a reporting period. If none is set,
1458 -- return the current reporting period
1459 IF pa_accum_utils.g_check_reporting_end_date is null
1460 THEN
1461 /* Bug 2634995 begins */
1462 --Replaced the pa_periods_v with the view definition
1463 -- select decode( pai.accumulation_period_type, 'PA', pav.pa_end_date,
1464 -- 'GL', pav.gl_end_date )
1465 -- into lcl_end_date
1466 -- from PA_PERIODS_V pav, pa_implementations pai
1467 -- where current_pa_period_flag = 'Y';
1468 select decode(paimp.accumulation_period_type, 'PA', pap.end_date,
1469 'GL', glp.end_date)
1470 into lcl_end_date
1471 FROM PA_PERIODS PAP, GL_PERIOD_STATUSES GLP,
1472 PA_IMPLEMENTATIONS PAIMP, PA_LOOKUPS PAL
1473 WHERE PAP.GL_PERIOD_NAME = GLP.PERIOD_NAME
1477 AND PAL.LOOKUP_TYPE = 'CLOSING STATUS'
1474 AND GLP.SET_OF_BOOKS_ID = PAIMP.SET_OF_BOOKS_ID
1475 AND GLP.APPLICATION_ID = Pa_Period_Process_Pkg.Application_id
1476 AND GLP.ADJUSTMENT_PERIOD_FLAG = 'N'
1478 AND PAL.LOOKUP_CODE = PAP.STATUS
1479 AND PAP.current_pa_period_flag = 'Y' ;
1480 /* Bug 2634995 ends */
1481
1482 return lcl_end_date;
1483 ELSE
1484 return pa_accum_utils.g_check_reporting_end_date;
1485 END IF;
1486
1487 return pa_accum_utils.g_check_reporting_end_date;
1488 END get_check_reporting_end_date;
1489
1490
1491 Procedure Set_current_period_Info IS
1492 P_DEBUG_MODE varchar2(1) := NVL(FND_PROFILE.value('PA_DEBUG_MODE'), 'N'); /* Added Debug Profile Option variable initialization for bug#2674619 */
1493 Begin
1494 IF P_DEBUG_MODE = 'Y' THEN /* Added Debug Profile Option Check for bug#2674619 */
1495 pa_debug.debug('pa_accum_utils.set_current_period_info');
1496 END IF;
1497
1498 SELECT
1499 period_name,
1500 gl_period_name
1501 INTO
1502 g_current_pa_period,
1503 g_current_gl_period
1504 FROM pa_periods
1505 WHERE
1506 current_pa_period_flag = 'Y';
1507
1508 Exception
1509 WHEN NO_DATA_FOUND THEN
1510 IF P_DEBUG_MODE = 'Y' THEN /* Added Debug Profile Option Check for bug#2674619 */
1511 pa_debug.debug('Data not found to set g_current_pa_period and g_current
1512 _gl_period',PA_DEBUG.DEBUG_LEVEL_EXCEPTION);
1513 END IF;
1514 RAISE;
1515 WHEN OTHERS THEN
1516 RAISE;
1517 End Set_current_period_info;
1518
1519 FUNCTION Get_current_pa_period
1520 return varchar2
1521 IS
1522 l_current_pa_period varchar2(20);
1523 BEGIN
1524
1525
1526 -- Return the value in global variable g_current_pa_period,if it is set.
1527 -- If g_current_pa_period is not set,fetch the current pa period from
1528 -- the database
1529
1530 IF pa_accum_utils.g_current_pa_period is null
1531 THEN
1532 SELECT
1533 period_name
1534 INTO
1535 l_current_pa_period
1536 FROM pa_periods
1537 WHERE
1538 current_pa_period_flag = 'Y';
1539 ELSE
1540 l_current_pa_period := pa_accum_utils.g_current_pa_period;
1541 END IF;
1542
1543 return l_current_pa_period;
1544
1545 Exception
1546 WHEN OTHERS THEN
1547 RAISE;
1548
1549 END Get_current_pa_period;
1550
1551 FUNCTION Get_current_gl_period
1552 return varchar2
1553 IS
1554 l_current_gl_period varchar2(15);
1555 BEGIN
1556
1557 -- Return the value in global variable g_current_gl_period,if it is set.
1558 -- If g_current_gl_period is not set,fetch the current gl period from
1559 -- the database
1560
1561 IF pa_accum_utils.g_current_gl_period is null
1562 THEN
1563 SELECT
1564 gl_period_name
1565 INTO
1566 l_current_gl_period
1567 FROM pa_periods
1568 WHERE
1569 current_pa_period_flag = 'Y';
1570 ELSE
1571 l_current_gl_period := pa_accum_utils.g_current_gl_period;
1572 END IF;
1573
1574 return l_current_gl_period;
1575
1576 Exception
1577 WHEN OTHERS THEN
1578 RAISE;
1579
1580 END Get_current_gl_period;
1581
1582 -- Function Get_spread_amount_val
1583 -- Budget amount will have to be spread across the various time periods,
1584 -- i.e., current period (for PTD), previous period for (PP) and
1585 -- current year (for YTD)
1586 -- This function returns the spread_amount value (tmp_amt_returned) of
1587 -- the amount passed to it (x_amt_to_be_spread) as a parameter.
1588 -- i.e., x_amt_to_be_spread can be either raw_cost or burdened_cost or
1589 -- revenue or quantity or labor_quantity.
1590 -- x_which_date_flag parameter can either be 'C' or 'P' or 'Y' or 'I'.
1591 -- These stand for Current period, Prior period, current Year,
1592 -- Inception to date.
1593
1594 FUNCTION Get_spread_amount_val
1595 (x_from_date IN DATE,
1596 x_to_date IN DATE,
1597 x_amt_to_be_spread IN NUMBER,
1598 x_which_date_flag IN VARCHAR2)
1599
1600 RETURN NUMBER
1601 IS
1602 tmp_amt_returned NUMBER := 0;
1603
1604 BEGIN
1605
1606 IF x_which_date_flag = 'C' THEN
1607
1608 -- PTD
1609 -- Budget End Date >= Period Start date and Budget Start Date
1610 -- <= Period End Date
1611
1612 IF x_to_date >= PA_PROJ_ACCUM_MAIN.x_current_start_date AND
1613 x_from_date <= PA_PROJ_ACCUM_MAIN.x_current_end_date THEN
1614
1615 tmp_amt_returned := PA_MISC.spread_amount('L', x_from_date, x_to_date,
1616 PA_PROJ_ACCUM_MAIN.x_current_start_date,
1617 PA_PROJ_ACCUM_MAIN.x_current_end_date, x_amt_to_be_spread);
1618
1619 END IF;
1620
1621 ELSIF x_which_date_flag = 'P' THEN
1622
1623 -- PP
1624 -- Budget End Date >= Period Start date and Budget Start Date
1625 -- <= Period End Date
1626
1630 tmp_amt_returned := PA_MISC.spread_amount('L', x_from_date, x_to_date,
1627 IF x_to_date >= PA_PROJ_ACCUM_MAIN.x_prev_start_date AND
1628 x_from_date <= PA_PROJ_ACCUM_MAIN.x_prev_end_date THEN
1629
1631 PA_PROJ_ACCUM_MAIN.x_prev_start_date,
1632 PA_PROJ_ACCUM_MAIN.x_prev_end_date, x_amt_to_be_spread);
1633
1634 END IF;
1635
1636 ELSIF x_which_date_flag = 'Y' THEN
1637
1638 -- YTD
1639 -- NOT (Budget End Date < Year Start Date OR Budget Start Date > Year End Date)
1640
1641 IF NOT (x_to_date < PA_PROJ_ACCUM_MAIN.x_period_yr_start_date OR
1642 x_from_date > PA_PROJ_ACCUM_MAIN.x_period_yr_end_date) THEN
1643
1644 tmp_amt_returned := PA_MISC.spread_amount('L', x_from_date, x_to_date,
1645 PA_PROJ_ACCUM_MAIN.x_period_yr_start_date,
1646 PA_PROJ_ACCUM_MAIN.x_period_yr_end_date, x_amt_to_be_spread);
1647
1648 END IF;
1649
1650 ELSIF x_which_date_flag = 'I' THEN
1651
1652 -- ITD
1653 -- NOT (Budget Start Date > Period End Date AND Budget End Date >
1654 -- Period Start Date)
1655
1656 IF NOT (x_from_date > PA_PROJ_ACCUM_MAIN.x_current_end_date) THEN
1657
1658 tmp_amt_returned := PA_MISC.spread_amount('L', x_from_date, x_to_date,
1659 x_from_date, PA_PROJ_ACCUM_MAIN.x_current_end_date, x_amt_to_be_spread);
1660
1661 END IF;
1662
1663 END IF;
1664
1665 RETURN tmp_amt_returned ;
1666
1667 Exception
1668 WHEN OTHERS THEN
1669 RAISE;
1670
1671 END Get_spread_amount_val;
1672
1673 -- /*--------------------------------------------------------*/
1674 -- Three new functions added as a part of Project Allocation
1675 -- Summarization Enhancement changes
1676 -- /*--------------------------------------------------------*/
1677
1678 Function Get_Grouping_Id
1679 Return Number
1680 Is
1681 Group_id Number := NULL;
1682
1683 Begin
1684 If pa_accum_utils.G_grouping_id Is Not Null Then
1685 Group_id := pa_accum_utils.G_grouping_id;
1686 End If;
1687
1688 Return Group_id;
1689
1690 Exception
1691 When No_Data_Found Then
1692 Return Group_id;
1693 When Others Then
1694 Raise;
1695
1696 End Get_Grouping_Id;
1697
1698
1699 Function Get_Context_Info
1700 Return Varchar2
1701 Is
1702 Summ_context Varchar2(25) := NULL;
1703
1704 Begin
1705 If pa_accum_utils.G_context Is Not Null Then
1706 Summ_context := pa_accum_utils.G_context;
1707 End If;
1708
1709 Return Summ_context;
1710
1711 Exception
1712 When No_Data_Found Then
1713 Return Summ_context;
1714 When Others Then
1715 Raise;
1716
1717 End Get_Context_Info;
1718
1719
1720 Function Get_Project_Info
1721 (x_From_Or_To IN VARCHAR2)
1722 Return Varchar2
1723 Is
1724 Proj_num Varchar2(25) := NULL;
1725 Begin
1726
1727 If x_From_Or_To = 'F' Then -- From which project
1728 If pa_accum_utils.G_start_proj Is Not Null Then
1729 Proj_num := pa_accum_utils.G_start_proj;
1730 End If;
1731 Else -- Till which project
1732 If pa_accum_utils.G_end_proj Is Not Null Then
1733 Proj_num := pa_accum_utils.G_end_proj;
1734 End If;
1735 End If;
1736
1737 Return Proj_num;
1738
1739 Exception
1740 When No_Data_Found Then
1741 Return Proj_num;
1742 When Others Then
1743 Raise;
1744
1745 End Get_Project_Info;
1746
1747 -- /*--------------------------------------------------------*/
1748 -- End of Project Allocation Summarization Enhancement changes
1749 -- /*--------------------------------------------------------*/
1750
1751 -- /*--------------------------------------------------------*/
1752 -- Start of code added for performance issue 3653978
1753 -- /*--------------------------------------------------------*/
1754
1755 Procedure Get_pa_period_Info1 (x_impl_opt In Varchar2,
1756 x_current_pa_start_date In Date,
1757 x_current_gl_start_date In Date,
1758 x_prev_pa_period Out NOCOPY Varchar2, --File.Sql.39 bug 4440895
1759 x_prev_gl_period Out NOCOPY Varchar2, --File.Sql.39 bug 4440895
1760 x_prev_pa_year Out NOCOPY Number, --File.Sql.39 bug 4440895
1761 x_prev_gl_year Out NOCOPY Number, --File.Sql.39 bug 4440895
1762 x_prev_pa_start_date Out NOCOPY Date, --File.Sql.39 bug 4440895
1763 x_prev_pa_end_date Out NOCOPY Date, --File.Sql.39 bug 4440895
1764 x_prev_gl_start_date Out NOCOPY Date, --File.Sql.39 bug 4440895
1765 x_prev_gl_end_date Out NOCOPY Date, --File.Sql.39 bug 4440895
1766 x_err_stack In Out NOCOPY Varchar2, --File.Sql.39 bug 4440895
1767 x_err_stage In Out NOCOPY Varchar2, --File.Sql.39 bug 4440895
1768 x_err_code In Out NOCOPY Number ) IS --File.Sql.39 bug 4440895
1769
1770 V_Old_Stack Varchar2(630);
1774 x_err_stack :=
1771 P_DEBUG_MODE varchar2(1) := NVL(FND_PROFILE.value('PA_DEBUG_MODE'), 'N');
1772 BEGIN
1773 V_Old_Stack := x_err_stack;
1775 x_err_stack||'->PA_ACCUM_UTILS.Get_pa_period_Info1';
1776
1777 -- Select the details pertaining to the previous pa period.
1778
1779 IF P_DEBUG_MODE = 'Y' THEN
1780 pa_debug.debug('Get_pa_period_Info1: ' || x_err_stack);
1781 END IF;
1782
1783 <<prev_pa_period>>
1784 BEGIN
1785 SELECT
1786 PERIOD_NAME,
1787 PERIOD_YEAR,
1788 PA_START_DATE,
1789 PA_END_DATE
1790 INTO
1791 x_prev_pa_period,
1792 x_prev_pa_year,
1793 x_prev_pa_start_date,
1794 x_prev_pa_end_date
1795 FROM
1796 PA_PERIODS_V
1797 WHERE pa_start_date =
1798 (SELECT max(start_date)
1799 FROM
1800 pa_periods
1801 WHERE start_date < x_current_pa_start_date);
1802
1803 EXCEPTION
1804 WHEN NO_DATA_FOUND THEN
1805 -- The current pa_period is the first period defined
1806 x_prev_pa_period := NULL;
1807 x_prev_pa_year := NULL;
1808 x_prev_pa_start_date := NULL;
1809 x_prev_pa_end_date := NULL;
1810
1811 WHEN OTHERS THEN
1812 x_err_code := SQLCODE;
1813 RAISE;
1814 END prev_pa_period;
1815
1816 -- Select the details pertaining to the previous gl period.
1817
1818 <<prev_gl_period>>
1819 BEGIN
1820
1821 SELECT
1822 DISTINCT gl_period_name,
1823 period_year,
1824 gl_start_date,
1825 gl_end_date
1826 INTO
1827 x_prev_gl_period,
1828 x_prev_gl_year,
1829 x_prev_gl_start_date,
1830 x_prev_gl_end_date
1831 FROM
1832 pa_periods_v
1833 WHERE
1834 gl_start_date =
1835 (SELECT max(gl_start_date)
1836 FROM pa_periods_v
1837 WHERE
1838 gl_start_date < x_current_gl_start_date);
1839
1840 EXCEPTION
1841 WHEN NO_DATA_FOUND THEN
1842 -- current gl_period is the first period defined
1843 x_prev_gl_period := NULL;
1844 x_prev_gl_year := NULL;
1845 x_prev_gl_start_date := NULL;
1846 x_prev_gl_end_date := NULL;
1847 WHEN OTHERS THEN
1848 x_err_code := SQLCODE;
1849 RAISE;
1850 END prev_gl_period;
1851
1852 -- Restore the old x_err_stack;
1853 x_err_stack := V_Old_Stack;
1854 Exception
1855 When Others Then
1856 x_err_code := SQLCODE;
1857 RAISE ;
1858 End Get_pa_period_Info1;
1859
1860
1861 Procedure Get_pa_period_Info2 (x_impl_opt In Varchar2,
1862 x_prev_accum_period in Varchar2,
1863 x_prev_accum_year Out NOCOPY number, --File.Sql.39 bug 4440895
1864 x_prev_accum_start_date In Out NOCOPY Date, --File.Sql.39 bug 4440895
1865 x_prev_accum_end_date Out NOCOPY Date, --File.Sql.39 bug 4440895
1866 x_prev_prev_accum_period Out NOCOPY Varchar2, --File.Sql.39 bug 4440895
1867 x_accum_period_type_changed IN OUT NOCOPY BOOLEAN, --File.Sql.39 bug 4440895
1868 x_err_stack In Out NOCOPY Varchar2, --File.Sql.39 bug 4440895
1869 x_err_stage In Out NOCOPY Varchar2, --File.Sql.39 bug 4440895
1870 x_err_code In Out NOCOPY Number ) IS --File.Sql.39 bug 4440895
1871
1872 V_Old_Stack Varchar2(630);
1873 P_DEBUG_MODE varchar2(1) := NVL(FND_PROFILE.value('PA_DEBUG_MODE'), 'N');
1874 BEGIN
1875 V_Old_Stack := x_err_stack;
1876 x_err_stack :=
1877 x_err_stack||'->PA_ACCUM_UTILS.Get_pa_period_Info2';
1878 x_accum_period_type_changed := FALSE;
1879
1880 IF P_DEBUG_MODE = 'Y' THEN
1881 pa_debug.debug('Get_pa_period_Info2: ' || x_err_stack);
1882 END IF;
1883
1884 BEGIN
1885
1886 If x_impl_opt = 'PA' Then
1887 Select PERIOD_YEAR,PA_START_DATE,PA_END_DATE
1888 into x_prev_accum_year,x_prev_accum_start_date,
1889 x_prev_accum_end_date from
1890 PA_PERIODS_V WHERE Period_name = x_prev_accum_period;
1891 Elsif
1892 x_impl_opt = 'GL' Then
1893 Select Distinct PERIOD_YEAR,GL_START_DATE,GL_END_DATE
1894 into x_prev_accum_year,x_prev_accum_start_date,
1895 x_prev_accum_end_date from
1896 PA_PERIODS_V WHERE Gl_Period_name = x_prev_accum_period;
1897 End If;
1898
1899 EXCEPTION
1900 WHEN NO_DATA_FOUND THEN
1901 -- Accumulation period type must have changed
1902 -- Bug #572031
1903 x_accum_period_type_changed := TRUE;
1904 END;
1905
1906 IF (x_accum_period_type_changed = FALSE) THEN
1907
1908 -- Now get x_prev_prev_accum_period
1909 <<prev_prev_accum_period>>
1910 BEGIN
1911 If x_impl_opt = 'PA' Then
1912 SELECT DISTINCT PERIOD_NAME
1913 INTO x_prev_prev_accum_period
1914 FROM
1915 pa_periods_v
1916 WHERE
1917 pa_start_date =
1918 (SELECT max(pa_start_date)
1919 FROM pa_periods_v
1920 WHERE pa_start_date < x_prev_accum_start_date);
1921 Elsif
1922 x_impl_opt = 'GL' Then
1923 SELECT DISTINCT GL_PERIOD_NAME
1924 INTO x_prev_prev_accum_period
1925 FROM
1926 pa_periods_v
1927 WHERE gl_start_date =
1928 (SELECT max(gl_start_date)
1929 FROM pa_periods_v
1930 WHERE gl_start_date < x_prev_accum_start_date);
1931 End If;
1932
1933 EXCEPTION
1934 WHEN NO_DATA_FOUND THEN
1935 x_prev_prev_accum_period := NULL;
1936 WHEN OTHERS THEN
1937 x_err_code := SQLCODE;
1938 RAISE;
1939 END prev_prev_accum_period;
1940 END IF; -- (x_accum_period_type_changed = FALSE)
1941
1942 -- Restore the old x_err_stack;
1943 x_err_stack := V_Old_Stack;
1944 Exception
1945 When Others Then
1946 x_err_code := SQLCODE;
1947 RAISE ;
1948 End Get_pa_period_Info2;
1949
1950
1951 -- /*--------------------------------------------------------*/
1952 -- End of code added for performance issue 3653978
1953 -- /*--------------------------------------------------------*/
1954
1955 END pa_accum_utils;
[Home] [Help]