[Home] [Help]
PACKAGE BODY: APPS.PA_SUMMARIZE_FORECAST_UTIL_PVT
Source
1 PACKAGE BODY PA_SUMMARIZE_FORECAST_UTIL_PVT
2 -- $Header: PARRFCVB.pls 120.0 2005/05/29 23:19:58 appldev noship $
3 AS
4
5 -- Assign the glboal variables to the local variable
6 -- This aviods repeated calls to the global package
7
8 -- Balance Type
9 l_balance_type VARCHAR2(15) := PA_REP_UTIL_GLOB.G_BAL_TYPE_C.G_FORECAST_C;
10
11 -- Input Parameters.
12 l_fc_start_date DATE := PA_REP_UTIL_GLOB.G_input_parameters.G_fc_start_date;
13 l_fc_end_date DATE := PA_REP_UTIL_GLOB.G_input_parameters.G_fc_end_date;
14
15 -- Utilization Option details
16 l_forecast_thru_date pa_utilization_options.forecast_thru_date%TYPE := PA_REP_UTIL_GLOB.G_util_option_details.G_forecast_thru_date;
17 l_pa_period_flag pa_utilization_options.pa_period_flag%TYPE := PA_REP_UTIL_GLOB.G_util_option_details.G_pa_period_flag;
18 l_gl_period_flag pa_utilization_options.gl_period_flag%TYPE := PA_REP_UTIL_GLOB.G_util_option_details.G_gl_period_flag;
19 l_ge_period_flag pa_utilization_options.global_exp_period_flag%TYPE := PA_REP_UTIL_GLOB.G_util_option_details.G_ge_period_flag;
20
21 -- Implementation options
22 l_org_id pa_implementations.org_id%TYPE := PA_REP_UTIL_GLOB.G_implementation_details.G_org_id;
23
24 -- Profile Options
25 l_global_week_start_day PLS_INTEGER := PA_REP_UTIL_GLOB.G_global_week_start_day;
26 l_fetch_size PLS_INTEGER := PA_REP_UTIL_GLOB.G_util_fetch_size;
27
28 -- Period Information
29 -- l_period_set_name gl_sets_of_books.period_set_name%TYPE := PA_REP_UTIL_GLOB.G_implementation_details.G_period_set_name;
30 l_gl_period_set_name gl_sets_of_books.period_set_name%TYPE := PA_REP_UTIL_GLOB.G_implementation_details.G_gl_period_set_name; -- bug 3434019
31 l_pa_period_set_name gl_sets_of_books.period_set_name%TYPE := PA_REP_UTIL_GLOB.G_implementation_details.G_pa_period_set_name; -- bug 3434019
32 l_pa_period_type pa_implementations.pa_period_type%TYPE := PA_REP_UTIL_GLOB.G_implementation_details.G_pa_period_type;
33 l_gl_period_type gl_sets_of_books.accounted_period_type%TYPE := PA_REP_UTIL_GLOB.G_implementation_details.G_gl_period_type;
34
35 -- Who Columns
36 l_created_by NUMBER(15) := PA_REP_UTIL_GLOB.G_who_columns.G_created_by;
37 l_last_updated_by NUMBER(15) := PA_REP_UTIL_GLOB.G_who_columns.G_last_updated_by;
38 l_request_id NUMBER(15) := PA_REP_UTIL_GLOB.G_who_columns.G_request_id;
39 l_program_id NUMBER(15) := PA_REP_UTIL_GLOB.G_who_columns.G_program_id;
40 l_program_application_id NUMBER(15) := PA_REP_UTIL_GLOB.G_who_columns.G_program_application_id;
41 l_creation_date DATE := PA_REP_UTIL_GLOB.G_who_columns.G_creation_date;
42 l_last_update_date DATE := PA_REP_UTIL_GLOB.G_who_columns.G_last_update_date;
43 l_program_update_date DATE := PA_REP_UTIL_GLOB.G_who_columns.G_last_update_date;
44
45 l_debug_mode VARCHAR2(2) := PA_REP_UTIL_GLOB.G_input_parameters.G_debug_mode;
46 l_quantity_id pa_amount_types_b.amount_type_id%TYPE := PA_REP_UTIL_GLOB. G_amt_type_details.G_quantity_id;
47 l_org_rollup_method VARCHAR2(1) := PA_REP_UTIL_GLOB.G_input_parameters.G_org_rollup_method;
48
49 -------------------------------------------------------
50 P_DEBUG_MODE varchar2(1) := NVL(FND_PROFILE.value('PA_DEBUG_MODE'), 'N'); /* Added Debug Profile Option variable initialization for bug#2674619 */
51
52 PROCEDURE insert_fct_into_tmp_table
53 IS
54 BEGIN
55 PA_DEBUG.Set_Curr_Function( p_function => 'insert_fct_into_tmp_table');
56 IF P_DEBUG_MODE = 'Y' THEN /* Added Debug Profile Option Check for bug#2674619 */
57 PA_DEBUG.g_err_stage := '205: Before inserting recs into Temp Table';
58 PA_DEBUG.Log_Message( p_message => PA_DEBUG.g_err_stage);
59 END IF;
60 --
61 -- Coded as a performance fix by mpuvathi
62 -- Starts here
63 --
64
65 INSERT
66 INTO pa_rep_util_summ0_tmp
67 ( row_id
68 , parent_row_id
69 , expenditure_organization_id
70 , person_id
71 , assignment_id
72 , work_type_id
73 , org_util_category_id
74 , res_util_category_id
75 , expenditure_type
76 , expenditure_type_class
77 , pa_period_name
78 , pa_period_num
79 , pa_period_year
80 , pa_quarter_number
81 , gl_period_name
82 , gl_period_num
83 , gl_period_year
84 , gl_quarter_number
85 , global_exp_period_end_date
86 , global_exp_year
87 , global_exp_month_number
88 , total_hours
89 , total_prov_hours
90 , total_wghted_hours_people
91 , total_wghted_hours_org
92 , prov_wghted_hours_people
93 , prov_wghted_hours_org
94 , reduce_capacity
95 , delete_flag )
96 SELECT
97 row_id
98 , parent_row_id
99 , expenditure_organization_id
100 , person_id
101 , assignment_id
102 , work_type_id
103 , org_util_category_id
104 , res_util_category_id
105 , expenditure_type
106 , expenditure_type_class
107 , pa_period_name
108 , pa_period_num
109 , pa_period_year
110 , pa_quarter_number
111 , gl_period_name
112 , gl_period_num
113 , gl_period_year
114 , gl_quarter_number
115 , global_exp_period_end_date
116 , global_exp_year
117 , global_exp_month_number
118 , total_hours
119 , total_prov_hours
120 , total_wghted_hours_people
121 , total_wghted_hours_org
122 , prov_wghted_hours_people
123 , prov_wghted_hours_org
124 , reduce_capacity
125 , delete_flag
126 FROM pa_rep_util_summ00_tmp
127 WHERE rownum <= l_fetch_size;
128
129 --
130 -- Coded as a performance fix by mpuvathi
131 -- Ends here
132 --
133
134 -- PA_DEBUG.g_err_stage := '210: After inserting recs into Temp Table';
135 -- PA_DEBUG.Log_Message( p_message => PA_DEBUG.g_err_stage);
136 PA_DEBUG.Reset_Curr_Function;
137 EXCEPTION
138 WHEN OTHERS THEN
139 RAISE;
140 END insert_fct_into_tmp_table;
141 -------------------------------------------------------
142
143 PROCEDURE Summarize_Forecast_Util
144 IS
145 l_fid_rowid_tab PA_PLSQL_DATATYPES.RowidTabTyp;
146 l_process_method VARCHAR2(1);
147 l_records_inserted PLS_INTEGER;
148 l_records_updated PLS_INTEGER;
149 l_capacity_summarized NUMBER :=0;
150 l_records_inserted1 PLS_INTEGER;
151 BEGIN
152 -- Initialize the Error Stack
153
154
155 PA_DEBUG.Set_Curr_Function( p_function => 'Summarize_Forecast_Util');
156
157 IF (l_pa_period_flag = 'N') AND (l_gl_period_flag = 'N') AND (l_ge_period_flag = 'N') THEN
158 -- Reset the error stack when returning to the calling program
159 PA_DEBUG.Reset_Curr_Function;
160 RETURN;
161 END IF;
162
163 IF P_DEBUG_MODE = 'Y' THEN /* Added Debug Profile Option Check for bug#2674619 */
164 PA_DEBUG.G_Err_Stage := '50 : L_FC_START_DATE ' || l_fc_start_date;
165 PA_DEBUG.Log_Message(p_message => PA_DEBUG.G_Err_Stage);
166 PA_DEBUG.G_Err_Stage := '100 : L_FC_END_DATE ' || l_fc_end_date;
167 PA_DEBUG.Log_Message(p_message => PA_DEBUG.G_Err_Stage);
168 PA_DEBUG.G_Err_Stage := '150 : After checking the flags ';
169 PA_DEBUG.Log_Message(p_message => PA_DEBUG.G_Err_Stage);
170 PA_DEBUG.G_Err_Stage := '200 : L_PA_PERIOD_FLAG ' || l_pa_period_flag;
171 PA_DEBUG.Log_Message(p_message => PA_DEBUG.G_Err_Stage);
172 PA_DEBUG.G_Err_Stage := '250 : L_GL_PERIOD_FLAG ' || l_gl_period_flag;
173 PA_DEBUG.Log_Message(p_message => PA_DEBUG.G_Err_Stage);
174 PA_DEBUG.G_Err_Stage := '300 : L_GE_PERIOD_FLAG ' || l_ge_period_flag;
175 PA_DEBUG.Log_Message(p_message => PA_DEBUG.G_Err_Stage);
176 END IF;
177
178 /*
179 * Calling a procedure for populating the initial temporary workspace
180 * Starts Here
181 */
182 IF (l_pa_period_flag = 'Y') THEN
183 IF (l_gl_period_flag = 'Y') THEN
184 IF (l_ge_period_flag = 'Y') THEN
185 IF P_DEBUG_MODE = 'Y' THEN /* Added Debug Profile Option Check for bug#2674619 */
186 PA_DEBUG.G_Err_Stage := '350 : Calling PAGLGE';
187 PA_DEBUG.Log_Message(p_message => PA_DEBUG.G_Err_Stage);
188 END IF;
189 Insert_Fcst_Into_Tmp_PAGLGE;
190 ELSE
191 IF P_DEBUG_MODE = 'Y' THEN /* Added Debug Profile Option Check for bug#2674619 */
192 PA_DEBUG.G_Err_Stage := '400 : Calling PAGL';
193 PA_DEBUG.Log_Message(p_message => PA_DEBUG.G_Err_Stage);
194 END IF;
195 Insert_Fcst_Into_Tmp_PAGL;
196 END IF;
197 ELSIF (l_ge_period_flag = 'Y') THEN
198 IF P_DEBUG_MODE = 'Y' THEN /* Added Debug Profile Option Check for bug#2674619 */
199 PA_DEBUG.G_Err_Stage := '450 : Calling PAGE';
200 PA_DEBUG.Log_Message(p_message => PA_DEBUG.G_Err_Stage);
201 END IF;
202 Insert_Fcst_Into_Tmp_PAGE;
203 ELSE
204 IF P_DEBUG_MODE = 'Y' THEN /* Added Debug Profile Option Check for bug#2674619 */
205 PA_DEBUG.G_Err_Stage := '500 : Calling PA';
206 PA_DEBUG.Log_Message(p_message => PA_DEBUG.G_Err_Stage);
207 END IF;
208 Insert_Fcst_Into_Tmp_PA;
209 END IF;
210 ELSIF (l_gl_period_flag = 'Y') THEN
211 IF (l_ge_period_flag = 'Y') THEN
212 IF P_DEBUG_MODE = 'Y' THEN /* Added Debug Profile Option Check for bug#2674619 */
213 PA_DEBUG.G_Err_Stage := '550 : Calling GLGE';
214 PA_DEBUG.Log_Message(p_message => PA_DEBUG.G_Err_Stage);
215 END IF;
216 Insert_Fcst_Into_Tmp_GLGE;
217 ELSE
218 IF P_DEBUG_MODE = 'Y' THEN /* Added Debug Profile Option Check for bug#2674619 */
219 PA_DEBUG.G_Err_Stage := '600 : Calling GL';
220 PA_DEBUG.Log_Message(p_message => PA_DEBUG.G_Err_Stage);
221 END IF;
222 Insert_Fcst_Into_Tmp_GL;
223 END IF;
224 ELSE
225 IF P_DEBUG_MODE = 'Y' THEN /* Added Debug Profile Option Check for bug#2674619 */
226 PA_DEBUG.G_Err_Stage := '650 : Calling GE';
227 PA_DEBUG.Log_Message(p_message => PA_DEBUG.G_Err_Stage);
228 END IF;
229 Insert_Fcst_Into_Tmp_GE;
230 END IF;
231 IF P_DEBUG_MODE = 'Y' THEN /* Added Debug Profile Option Check for bug#2674619 */
232 PA_DEBUG.G_Err_Stage := '700 : After Calling the INSERT_PROC_[PA][GL][GE]';
233 PA_DEBUG.Log_Message(p_message => PA_DEBUG.G_Err_Stage);
234 END IF;
235
236 /*
237 * Calling a procedure for populating the temporary workspace
238 * Ends Here
239 */
240 -- Main Un-conditional Loop. Exits if No more records to process (SQL%ROWCOUNT = 0)
241 LOOP
242
243 -- Check if ANY records have been inserted into the temporary table.
244 /*
245 * Transfer the data from the initial staging area to the current
246 * processing set.
247 */
248
249 insert_fct_into_tmp_table;
250 -- If NO records are inserted, getout of the loop.
251 l_records_inserted := SQL%ROWCOUNT;
252 IF P_DEBUG_MODE = 'Y' THEN /* Added Debug Profile Option Check for bug#2674619 */
253 PA_DEBUG.G_Err_Stage := '750 : Records Inserted in Temp tab : ' || l_records_inserted;
254 PA_DEBUG.Log_Message(p_message => pa_debug.G_Err_Stage);
255 PA_DEBUG.G_Err_Stage := '753 : l_capacity_summarized: ' || l_capacity_summarized;
256 PA_DEBUG.Log_Message(p_message => pa_debug.G_Err_Stage);
257 END IF;
258
259 IF (l_records_inserted = 0 AND l_capacity_summarized= 1) THEN
260 IF P_DEBUG_MODE = 'Y' THEN /* Added Debug Profile Option Check for bug#2674619 */
261 PA_DEBUG.G_Err_Stage := '757 : EXITING since l_records_inserted = 0 AND l_capacity_summarized= 1';
262 PA_DEBUG.Log_Message(p_message => pa_debug.G_Err_Stage);
263 END IF;
264 EXIT;
265 END IF;
266
267 -- Update Forecast Item Detail with util_summarized_code = 'S' for the
268 -- rowids available in the temp0 table.
269 -- The local rowid plsql table collects all the rowids
270 -- for which update was successful.
271
272 UPDATE pa_forecast_item_details A
273 SET util_summarized_code = 'S'
274 WHERE util_summarized_code = 'N'
275 AND exists ( SELECT row_id
276 FROM pa_rep_util_summ0_tmp B
277 WHERE A.ROWID = B.ROW_ID -- bug 3132246
278 )
279 RETURNING ROWID BULK COLLECT INTO l_fid_rowid_tab;
280
281 l_records_updated := SQL%ROWCOUNT;
282 IF P_DEBUG_MODE = 'Y' THEN /* Added Debug Profile Option Check for bug#2674619 */
283 PA_DEBUG.G_Err_Stage := '800 : Records Updated in PA_FORECAST_ITEMS_DETAILS : '|| l_records_updated;
284 PA_DEBUG.Log_Message(p_message => PA_DEBUG.G_Err_Stage);
285 END IF;
286
287 -- Check if the all the Forecast_Item_Detail records corresponding to the
288 -- those in temp0 have been updated with util_summarized_code = 'S'.
289 -- If YES, process_method = 'A' (ALL)
290 -- id not process_method = 'F'(FILTER - will be based on the PA_REP_UTIL_SUMM0_TMP.delete_flag).
291 -- The delete flag in pa_rep_util_summ0_tmp is initialized
292 -- to 'Y' when first inserted. But if update of util_summarized_code = 'S'
293 -- is successful, for those records, delete_flag of their corresponding records in temp table
294 -- are updated to 'N' - meaning that, those records SHOULD be processed and hence
295 -- to be considered as NOT deleted.
296
297 l_process_method := 'A';
298
299
300 IF (l_records_updated < l_records_inserted AND l_fid_rowid_tab.COUNT > 0) THEN /* added second condition 2084888 */
301 l_process_method := 'F';
302 FORALL i IN l_fid_rowid_tab.FIRST .. l_fid_rowid_tab.LAST
303 UPDATE pa_rep_util_summ0_tmp tmp
304 SET tmp.delete_flag = 'N'
305 WHERE tmp.row_id = l_fid_rowid_tab(i);
306 END IF;
307
308 -- Delete the rowid plsql table.
309 l_fid_rowid_tab.DELETE;
310 IF P_DEBUG_MODE = 'Y' THEN /* Added Debug Profile Option Check for bug#2674619 */
311 PA_DEBUG.G_Err_Stage := '850 : Process Method : ' || l_process_method;
312 PA_DEBUG.Log_Message(p_message => PA_DEBUG.G_Err_Stage);
313 PA_DEBUG.G_Err_Stage := '900 : Before calling PA_REP_UTILS_SUMM_PKG';
314 PA_DEBUG.Log_Message(p_message => PA_DEBUG.G_Err_Stage);
315 END IF;
316
317 --
318 -- Call the package which processes the data.
319 PA_REP_UTILS_SUMM_PKG.populate_summ_entity( p_balance_type_code => l_balance_type
320 ,p_process_method => l_process_method );
321
322
323 l_capacity_summarized := 1;
324 IF P_DEBUG_MODE = 'Y' THEN /* Added Debug Profile Option Check for bug#2674619 */
325 PA_DEBUG.G_Err_Stage := '950 : After calling PA_REP_UTILS_SUMM_PKG';
326 PA_DEBUG.Log_Message(p_message => PA_DEBUG.G_Err_Stage);
327 END IF;
328
329 -- If the process_method is 'F' (Filter):
330 -- 1. Delete those records from pa_forecast_item_details which have been processed
331 -- i.e. summarised code is 'S' and temp table delete flag is 'N'
332 -- 2. Update the util_summarized_code to NULL for those records which have been processed
333 -- 3. Delete pa_forecat_items (master) records for which processed detail records have been
334 -- deleted and no further detail record exists
335 --
336 -- If process_method is 'A' (All):
337 -- 1. Delete those records from pa_forecast_item_details which have been processed and for
338 -- for which a record exsists in temp table. i.e. summarised code is 'S'
339 -- and temp table delete flag is 'Y'
340 -- 2. Update the util_summarized_code to NULL for those records which have been processed
341 --
342 -- 3. Delete pa_forecat_items (master) records for which processed detail records have been
343 -- deleted and no further detail record exists
344
345
346 IF l_process_method = 'F' THEN
347 IF P_DEBUG_MODE = 'Y' THEN /* Added Debug Profile Option Check for bug#2674619 */
348 /* Commented for Bug 2984871
349 PA_DEBUG.G_Err_Stage := '1000 : Records Deleted from PA_FORECAST_ITEM_DETAILS: ' || to_char(SQL%ROWCOUNT);
350 PA_DEBUG.Log_Message(p_message => pa_debug.G_Err_Stage);
351 PA_DEBUG.G_Err_Stage := '1050 : After deleting from PA_FORECAST_ITEM_DETAILS for process method ' || l_process_method ;
352 PA_DEBUG.Log_Message(p_message => PA_DEBUG.G_Err_Stage);*/
353 /*Code Changes for Bug No.2984871 start */
354 PA_DEBUG.G_Err_Stage := '1050 : process method = ' || l_process_method ;
355 PA_DEBUG.Log_Message(p_message => PA_DEBUG.G_Err_Stage);
356 /*Code Changes for Bug No.2984871 end */
357 END IF;
358 --
359 UPDATE pa_forecast_item_details
360 SET util_summarized_code = NULL
361 ,last_update_date = l_last_update_date
362 ,last_updated_by = l_last_updated_by
363 ,request_id = l_request_id
364 ,program_application_id = l_program_application_id
365 ,program_id = l_program_id
366 ,program_update_date = l_program_update_date
367 WHERE util_summarized_code = 'S' -- Do we require this?
368 AND ROWID IN (SELECT row_id
369 FROM pa_rep_util_summ0_tmp
370 WHERE delete_flag = 'N'
371 );
372 IF P_DEBUG_MODE = 'Y' THEN /* Added Debug Profile Option Check for bug#2674619 */
373 PA_DEBUG.G_Err_Stage := '1100 : Records Updated in PA_FORECAST_ITEM_DETAILS: ' || to_char(SQL%ROWCOUNT);
374 PA_DEBUG.Log_Message(p_message => pa_debug.G_Err_Stage);
375 PA_DEBUG.G_Err_Stage := '1150 : After Updating PA_FORECAST_ITEM_DETAILS for process method ' || l_process_method ;
376 PA_DEBUG.Log_Message(p_message => PA_DEBUG.G_Err_Stage);
377 END IF;
378 --
379 /*
380 ** Bug 2263074
381 ** The delete statement for pa_forecast_items is removed.
382 */
383 --
384 ELSIF l_process_method = 'A' THEN
385 IF P_DEBUG_MODE = 'Y' THEN /* Added Debug Profile Option Check for bug#2674619 */
386 /* Commented for Bug 2984871
387 PA_DEBUG.G_Err_Stage := '1300 : Records Deleted from PA_FORECAST_ITEM_DETAILS: ' || to_char(SQL%ROWCOUNT);
388 PA_DEBUG.Log_Message(p_message => pa_debug.G_Err_Stage);
389 PA_DEBUG.G_Err_Stage := '1350 : After deleting from PA_FORECAST_ITEM_DETAILS for process method ' || l_process_method ;
390 PA_DEBUG.Log_Message(p_message => PA_DEBUG.G_Err_Stage);*/
391
392 /*Code Changes for Bug No.2984871 start */
393 PA_DEBUG.G_Err_Stage := '1350 : process method = ' || l_process_method ;
394 PA_DEBUG.Log_Message(p_message => PA_DEBUG.G_Err_Stage);
395 /*Code Changes for Bug No.2984871 end */
396 END IF;
397 --
398 UPDATE pa_forecast_item_details
399 SET util_summarized_code = NULL
400 ,last_update_date = l_last_update_date
401 ,last_updated_by = l_last_updated_by
402 ,request_id = l_request_id
403 ,program_application_id = l_program_application_id
404 ,program_id = l_program_id
405 ,program_update_date = l_program_update_date
406 WHERE util_summarized_code = 'S' -- Do we require this?
407 AND ROWID IN (SELECT row_id
408 FROM pa_rep_util_summ0_tmp
409 );
410 IF P_DEBUG_MODE = 'Y' THEN /* Added Debug Profile Option Check for bug#2674619 */
411 PA_DEBUG.G_Err_Stage := '1400 : Records Updated in PA_FORECAST_ITEM_DETAILS: ' || to_char(SQL%ROWCOUNT);
412 PA_DEBUG.Log_Message(p_message => pa_debug.G_Err_Stage);
413 PA_DEBUG.G_Err_Stage := '1450 : After Updating PA_FORECAST_ITEM_DETAILS for process method ' || l_process_method ;
414 PA_DEBUG.Log_Message(p_message => PA_DEBUG.G_Err_Stage);
415 END IF;
416 --
417 /*
418 ** Bug 2263074
419 ** The delete statement for pa_forecast_items is removed.
420 */
421 --
422 END IF;
423 -- mpuvathi
424 delete from pa_rep_util_summ00_tmp
425 where row_id in (select row_id from pa_rep_util_summ0_tmp)
426 ;
427 -- mpuvathi
428 COMMIT;
429 END LOOP;
430
431
432 -- IF Refresh organization rollup is enabled, populate the PA_rep_util_summ_tmp
433 IF (l_org_rollup_method = 'R') THEN
434 PA_SUMMARIZE_ORG_ROLLUP_PVT.refresh_org_hierarchy_rollup( p_balance_type_code => l_balance_type);
435 END IF;
436
437 IF P_DEBUG_MODE = 'Y' THEN /* Added Debug Profile Option Check for bug#2674619 */
438 PA_DEBUG.G_Err_Stage := '1600 : After calling Organization Utilization Forecast Refresh Rollup';
439 PA_DEBUG.Log_Message(p_message => PA_DEBUG.G_Err_Stage);
440 END IF;
441
442 --
443 -- Update pa_utilization_options with the dates for which Balances exist.
444 --
445 /* Bug 1628557
446 * Put the code for update outside of the if loop so that the thru date
447 * is updated with the end date of the current run (it would no longer
448 * reflect the furthest out date till which summarization was ever run,
449 * as was the case earlier)
450 * IF NVL(l_forecast_thru_date, l_fc_end_date -1) < l_fc_end_date THEN
451 * code for update
452 * END IF;
453 */
454 UPDATE pa_utilization_options_all
455 SET forecast_thru_date = l_fc_end_date
456 , forecast_last_run_date = sysdate
457 WHERE NVL(org_id, -99) = l_org_id;
458
459 /* Bug 2177424
460 * The delete logic is modified to delete all the forecast items
461 * which are processed (util_summarized_code is null).
462 */
463 /*
464 ** Bug 2263074
465 ** The delete statement for pa_forecast_item_details is removed.
466 */
467
468 COMMIT;
469 IF P_DEBUG_MODE = 'Y' THEN /* Added Debug Profile Option Check for bug#2674619 */
470 PA_DEBUG.G_Err_Stage := '1650 : After updating PA_UTILIZATION_OPTIONS_ALL';
471 PA_DEBUG.Log_Message(p_message => PA_DEBUG.G_Err_Stage);
472 PA_DEBUG.g_err_stage := '1660: Exiting the Package PA_SUMMARIZE_FORECAST_UTIL';
473 END IF;
474 -- Reset the error stack when returning to the calling program
475 PA_DEBUG.Reset_Curr_Function;
476
477 EXCEPTION
478 WHEN OTHERS THEN
479 IF P_DEBUG_MODE = 'Y' THEN /* Added Debug Profile Option Check for bug#2674619 */
480 PA_DEBUG.Log_Message(p_message => PA_DEBUG.G_Err_Stack);
481 PA_DEBUG.Log_Message(p_message => SQLERRM);
482 END IF;
483 RAISE;
484
485 END Summarize_Forecast_Util;
486
487
488 --------------------------------------------
489 -- Procedure Insert_Fcst_Into_Tmp_PA
490 --------------------------------------------
491
492 PROCEDURE Insert_Fcst_Into_Tmp_PA
493 IS
494 BEGIN
495 -- Set the error stack
496 PA_DEBUG.Set_Curr_Function( p_function => 'Insert_Fcst_Into_Tmp_PA');
497 IF P_DEBUG_MODE = 'Y' THEN /* Added Debug Profile Option Check for bug#2674619 */
498 PA_DEBUG.G_Err_Stage := '2000 : Before Inserting Forecast data into PA_REP_UTIL_SUMM0_TMP for PA';
499 END IF;
500 INSERT INTO pa_rep_util_summ00_tmp
501 ( row_id
502 ,parent_row_id
503 ,expenditure_organization_id
504 ,person_id
505 ,assignment_id
506 ,work_type_id
507 ,org_util_category_id
508 ,res_util_category_id
509 ,expenditure_type
510 ,expenditure_type_class
511 ,pa_period_name
512 ,pa_period_num
513 ,pa_period_year
514 ,pa_quarter_number
515 ,gl_period_name
516 ,gl_period_num
517 ,gl_period_year
518 ,gl_quarter_number
519 ,global_exp_period_end_date
520 ,global_exp_year
521 ,global_exp_month_number
522 ,total_hours
523 ,total_prov_hours
524 ,total_wghted_hours_people
525 ,total_wghted_hours_org
526 ,prov_wghted_hours_people
527 ,prov_wghted_hours_org
528 ,reduce_capacity
529 ,delete_flag
530 )
531 SELECT fid.rowid row_id
532 ,fi.rowid parent_row_id
533 ,fi.expenditure_organization_id
534 ,fi.person_id
535 ,fi.assignment_id
536 ,fid.work_type_id
537 ,fid.org_util_category_id
538 ,fid.resource_util_category_id
539 ,fi.expenditure_type
540 ,fi.expenditure_type_class
541 ,fi.pvdr_pa_period_name pa_period_name
542 ,(pp.period_year * 10000) + pp.period_num pa_period_num
543 ,pp.period_year pa_period_year
544 ,pp.quarter_num pa_quarter_number
545 ,NULL gl_period_name
546 ,NULL gl_period_num
547 ,NULL gl_period_year
548 ,NULL gl_quarter_number
549 ,NULL global_exp_period_end_date
550 ,NULL global_exp_year
551 ,NULL global_exp_month_number
552 ,NVL(fid.item_quantity, 0) total_hours
553 ,NVL(fid.item_quantity, 0) * decode(fid.provisional_flag, 'Y', 1, 0) total_prov_hours
554 ,NVL(fid.resource_util_weighted, 0) total_wghted_hours_people
555 ,NVL(fid.org_util_weighted, 0) total_wghted_hours_org
556 ,NVL(fid.resource_util_weighted, 0) * decode(fid.provisional_flag, 'Y', 1, 0) prov_wghted_hours_people
557 ,NVL(fid.org_util_weighted, 0) * decode(fid.provisional_flag, 'Y', 1, 0) prov_wghted_hours_org
558 ,DECODE(fid.reduce_capacity_flag, 'Y', 1, 'N', 0) * NVL(fid.item_quantity, 0) reduce_capacity
559 ,'Y' delete_flag
560 FROM pa_forecast_items fi
561 ,pa_forecast_item_details fid
562 ,gl_periods pp
563 WHERE fi.forecast_item_id = fid.forecast_item_id
564 AND fi.expenditure_org_id = l_org_id
565 AND fid.expenditure_org_id = l_org_id
566 AND fi.forecast_item_type IN ('A', 'U')
567 AND fid.util_summarized_code = 'N'
568 --AND fid.person_billable_flag = 'Y'
569 AND fid.amount_type_id = l_quantity_id
570 -- AND pp.period_set_name = l_period_set_name
571 AND pp.period_set_name = l_pa_period_set_name -- bug 3434019
572 AND pp.period_type = l_pa_period_type
573 --AND fi.pvdr_period_set_name = pp.period_set_name /* commented for bug 3488229 */
574 AND fi.pvdr_pa_period_name = pp.period_name
575 AND fi.item_date BETWEEN l_fc_start_date AND TRUNC(l_fc_end_date)+0.99999; /* BUG# 3118592 */
576
577 commit;
578 IF P_DEBUG_MODE = 'Y' THEN /* Added Debug Profile Option Check for bug#2674619 */
579 PA_DEBUG.Log_Message(p_message => PA_DEBUG.G_Err_Stage);
580 PA_DEBUG.G_Err_Stage := '2050 : After Inserting Forecast data into PA_REP_UTIL_SUMM0_TMP for PA';
581 PA_DEBUG.Log_Message(p_message => PA_DEBUG.G_Err_Stage);
582 END IF;
583
584 -- Reset the error stack
585 PA_DEBUG.Reset_Curr_Function;
586
587 EXCEPTION
588 WHEN OTHERS THEN
589 RAISE;
590 END Insert_Fcst_Into_Tmp_PA;
591
592
593 --------------------------------------------
594 -- Procedure Insert_Fcst_Into_Tmp_GL
595 --------------------------------------------
596
597 PROCEDURE Insert_Fcst_Into_Tmp_GL
598 IS
599 BEGIN
600 -- Set the error satack
601 PA_DEBUG.Set_Curr_Function( p_function => 'Insert_Fcst_Into_Tmp_GL');
602 IF P_DEBUG_MODE = 'Y' THEN /* Added Debug Profile Option Check for bug#2674619 */
603 PA_DEBUG.G_Err_Stage := '2100 : Before Inserting Forecast data into PA_REP_UTIL_SUMM0_TMP for GL';
604 PA_DEBUG.Log_Message(p_message => PA_DEBUG.G_Err_Stage);
605 END IF;
606
607 INSERT INTO pa_rep_util_summ00_tmp
608 ( row_id
609 ,parent_row_id
610 ,expenditure_organization_id
611 ,person_id
612 ,assignment_id
613 ,work_type_id
614 ,org_util_category_id
615 ,res_util_category_id
616 ,expenditure_type
617 ,expenditure_type_class
618 ,pa_period_name
619 ,pa_period_num
620 ,pa_period_year
621 ,pa_quarter_number
622 ,gl_period_name
623 ,gl_period_num
624 ,gl_period_year
625 ,gl_quarter_number
626 ,global_exp_period_end_date
627 ,global_exp_year
628 ,global_exp_month_number
629 ,total_hours
630 ,total_prov_hours
631 ,total_wghted_hours_people
632 ,total_wghted_hours_org
633 ,prov_wghted_hours_people
634 ,prov_wghted_hours_org
635 ,reduce_capacity
636 ,delete_flag
637 )
638 SELECT fid.rowid row_id
639 ,fi.rowid parent_row_id
640 ,fi.expenditure_organization_id
641 ,fi.person_id
642 ,fi.assignment_id
643 ,fid.work_type_id
644 ,fid.org_util_category_id
645 ,fid.resource_util_category_id
646 ,fi.expenditure_type
647 ,fi.expenditure_type_class
648 ,NULL pa_period_name
649 ,NULL pa_period_num
650 ,NULL pa_period_year
651 ,NULL pa_quarter_number
652 ,fi.pvdr_gl_period_name gl_period_name
653 ,(gp.period_year * 10000) + gp.period_num gl_period_num
654 ,gp.period_year gl_period_year
655 ,gp.quarter_num gl_quarter_number
656 ,NULL global_exp_period_end_date
657 ,NULL global_exp_year
658 ,NULL global_exp_month_number
659 ,NVL(fid.item_quantity, 0) total_hours
660 ,NVL(fid.item_quantity, 0) * decode(fid.provisional_flag, 'Y', 1, 0) total_prov_hours
661 ,NVL(fid.resource_util_weighted, 0) total_wghted_hours_people
662 ,NVL(fid.org_util_weighted, 0) total_wghted_hours_org
663 ,NVL(fid.resource_util_weighted, 0) * decode(fid.provisional_flag, 'Y', 1, 0) prov_wghted_hours_people
664 ,NVL(fid.org_util_weighted, 0) * decode(fid.provisional_flag, 'Y', 1, 0) prov_wghted_hours_org
665 ,DECODE(fid.reduce_capacity_flag, 'Y', 1, 'N', 0) * NVL(fid.item_quantity, 0) reduce_capacity
666 ,'Y' delete_flag
667 FROM pa_forecast_items fi
668 ,pa_forecast_item_details fid
669 ,gl_periods gp
670 WHERE fi.forecast_item_id = fid.forecast_item_id
671 AND fi.expenditure_org_id = l_org_id
672 AND fid.expenditure_org_id = l_org_id
673 AND fi.forecast_item_type IN ('A', 'U')
674 AND fid.util_summarized_code = 'N'
675 --AND fid.person_billable_flag = 'Y'
676 AND fid.amount_type_id = l_quantity_id
677 -- AND gp.period_set_name = l_period_set_name
678 AND gp.period_set_name = l_gl_period_set_name -- bug 3322360
679 AND gp.period_type = l_gl_period_type
680 --AND fi.pvdr_period_set_name = gp.period_set_name /* commented for bug 3488229 */
681 AND fi.pvdr_gl_period_name = gp.period_name
682 AND fi.item_date BETWEEN l_fc_start_date AND TRUNC(l_fc_end_date)+0.99999 ; /* BUG# 3118592 */
683
684
685 IF P_DEBUG_MODE = 'Y' THEN /* Added Debug Profile Option Check for bug#2674619 */
686 PA_DEBUG.G_Err_Stage := '2150 : After Inserting Forecast data into PA_REP_UTIL_SUMM0_TMP for GL';
687 PA_DEBUG.Log_Message(p_message => PA_DEBUG.G_Err_Stage);
688 END IF;
689
690 -- Reset the error stack
691 PA_DEBUG.Reset_Curr_Function;
692
693 EXCEPTION
694 WHEN OTHERS THEN
695 RAISE;
696 END Insert_Fcst_Into_Tmp_GL;
697
698
699 --------------------------------------------
700 -- Procedure Insert_Fcst_Into_Tmp_GE
701 --------------------------------------------
702
703 PROCEDURE Insert_Fcst_Into_Tmp_GE
704 IS
705 BEGIN
706 -- Set the error satack
707 PA_DEBUG.Set_Curr_Function( p_function => 'Insert_Fcst_Into_Tmp_GE');
708
709 IF P_DEBUG_MODE = 'Y' THEN /* Added Debug Profile Option Check for bug#2674619 */
710 PA_DEBUG.G_Err_Stage := '2200 : Before Inserting Forecast data into PA_REP_UTIL_SUMM0_TMP for GE';
711 PA_DEBUG.Log_Message(p_message => PA_DEBUG.G_Err_Stage);
712 END IF;
713
714 INSERT INTO pa_rep_util_summ00_tmp
715 ( row_id
716 ,parent_row_id
717 ,expenditure_organization_id
718 ,person_id
719 ,assignment_id
720 ,work_type_id
721 ,org_util_category_id
722 ,res_util_category_id
723 ,expenditure_type
724 ,expenditure_type_class
725 ,pa_period_name
726 ,pa_period_num
727 ,pa_period_year
728 ,pa_quarter_number
729 ,gl_period_name
730 ,gl_period_num
731 ,gl_period_year
732 ,gl_quarter_number
733 ,global_exp_period_end_date
734 ,global_exp_year
735 ,global_exp_month_number
736 ,total_hours
737 ,total_prov_hours
738 ,total_wghted_hours_people
739 ,total_wghted_hours_org
740 ,prov_wghted_hours_people
741 ,prov_wghted_hours_org
742 ,reduce_capacity
743 ,delete_flag
744 )
745 SELECT fid.rowid row_id
746 ,fi.rowid parent_row_id
747 ,fi.expenditure_organization_id
748 ,fi.person_id
749 ,fi.assignment_id
750 ,fid.work_type_id
751 ,fid.org_util_category_id
752 ,fid.resource_util_category_id
753 ,fi.expenditure_type
754 ,fi.expenditure_type_class
755 ,NULL pa_period_name
756 ,NULL pa_period_num
757 ,NULL pa_period_year
758 ,NULL pa_quarter_number
759 ,NULL gl_period_name
760 ,NULL gl_period_num
761 ,NULL gl_period_year
762 ,NULL gl_quarter_number
763 ,trunc(fi.global_exp_period_end_date) global_exp_period_end_date
764 ,to_number(to_char(fi.global_exp_period_end_date, 'YYYY')) global_exp_year
765 ,to_number(to_char(fi.global_exp_period_end_date, 'MM')) global_exp_month_number
766 ,NVL(fid.item_quantity, 0) total_hours
767 ,NVL(fid.item_quantity, 0) * decode(fid.provisional_flag, 'Y', 1, 0) total_prov_hours
768 ,NVL(fid.resource_util_weighted, 0) total_wghted_hours_people
769 ,NVL(fid.org_util_weighted, 0) total_wghted_hours_org
770 ,NVL(fid.resource_util_weighted, 0) * decode(fid.provisional_flag, 'Y', 1, 0) prov_wghted_hours_people
771 ,NVL(fid.org_util_weighted, 0) * decode(fid.provisional_flag, 'Y', 1, 0) prov_wghted_hours_org
772 ,DECODE(fid.reduce_capacity_flag, 'Y', 1, 'N', 0) * NVL(fid.item_quantity, 0) reduce_capacity
773 ,'Y' delete_flag
774 FROM pa_forecast_items fi
775 ,pa_forecast_item_details fid
776 WHERE fi.forecast_item_id = fid.forecast_item_id
777 AND fi.expenditure_org_id = l_org_id
778 AND fid.expenditure_org_id = l_org_id
779 AND fi.forecast_item_type IN ('A', 'U')
780 AND fid.util_summarized_code = 'N'
781 --AND fid.person_billable_flag = 'Y'
782 AND fid.amount_type_id = l_quantity_id
783 AND fi.item_date BETWEEN l_fc_start_date AND TRUNC(l_fc_end_date)+0.99999; /* BUG# 3118592 */
784
785 IF P_DEBUG_MODE = 'Y' THEN /* Added Debug Profile Option Check for bug#2674619 */
786 PA_DEBUG.G_Err_Stage := '2250 : After Inserting Forecast data into PA_REP_UTIL_SUMM0_TMP for GE';
787 PA_DEBUG.Log_Message(p_message => PA_DEBUG.G_Err_Stage);
788 END IF;
789
790 -- Reset the error stack
791 PA_DEBUG.Reset_Curr_Function;
792
793 EXCEPTION
794 WHEN OTHERS THEN
795 RAISE;
796 END Insert_Fcst_Into_Tmp_GE;
797
798
799 --------------------------------------------
800 -- Procedure Insert_Fcst_Into_Tmp_PAGL
801 --------------------------------------------
802
803 PROCEDURE Insert_Fcst_Into_Tmp_PAGL
804 IS
805 BEGIN
806 -- Set the error satack
807 PA_DEBUG.Set_Curr_Function( p_function => 'Insert_Fcst_Into_Tmp_PAGL');
808
809 IF P_DEBUG_MODE = 'Y' THEN /* Added Debug Profile Option Check for bug#2674619 */
810 PA_DEBUG.G_Err_Stage := '2300 : Before Inserting Forecast data into PA_REP_UTIL_SUMM0_TMP for PA and GL';
811 PA_DEBUG.Log_Message(p_message => PA_DEBUG.G_Err_Stage);
812 END IF;
813
814 INSERT INTO pa_rep_util_summ00_tmp
815 ( row_id
816 ,parent_row_id
817 ,expenditure_organization_id
818 ,person_id
819 ,assignment_id
820 ,work_type_id
821 ,org_util_category_id
822 ,res_util_category_id
823 ,expenditure_type
824 ,expenditure_type_class
825 ,pa_period_name
826 ,pa_period_num
827 ,pa_period_year
828 ,pa_quarter_number
829 ,gl_period_name
830 ,gl_period_num
831 ,gl_period_year
832 ,gl_quarter_number
833 ,global_exp_period_end_date
834 ,global_exp_year
835 ,global_exp_month_number
836 ,total_hours
837 ,total_prov_hours
838 ,total_wghted_hours_people
839 ,total_wghted_hours_org
840 ,prov_wghted_hours_people
841 ,prov_wghted_hours_org
842 ,reduce_capacity
843 ,delete_flag
844 )
845 SELECT fid.rowid row_id
846 ,fi.rowid parent_row_id
847 ,fi.expenditure_organization_id
848 ,fi.person_id
849 ,fi.assignment_id
850 ,fid.work_type_id
851 ,fid.org_util_category_id
852 ,fid.resource_util_category_id
853 ,fi.expenditure_type
854 ,fi.expenditure_type_class
855 ,fi.pvdr_pa_period_name pa_period_name
856 ,(pp.period_year * 10000) + pp.period_num pa_period_num
857 ,pp.period_year pa_period_year
858 ,pp.quarter_num pa_quarter_number
859 ,fi.pvdr_gl_period_name gl_period_name
860 ,(gp.period_year * 10000) + gp.period_num gl_period_num
861 ,gp.period_year gl_period_year
862 ,gp.quarter_num gl_quarter_number
863 ,NULL global_exp_period_end_date
864 ,NULL global_exp_year
865 ,NULL global_exp_month_number
866 ,NVL(fid.item_quantity, 0) total_hours
867 ,NVL(fid.item_quantity, 0) * decode(fid.provisional_flag, 'Y', 1, 0) total_prov_hours
868 ,NVL(fid.resource_util_weighted, 0) total_wghted_hours_people
869 ,NVL(fid.org_util_weighted, 0) total_wghted_hours_org
870 ,NVL(fid.resource_util_weighted, 0) * decode(fid.provisional_flag, 'Y', 1, 0) prov_wghted_hours_people
871 ,NVL(fid.org_util_weighted, 0) * decode(fid.provisional_flag, 'Y', 1, 0) prov_wghted_hours_org
872 ,DECODE(fid.reduce_capacity_flag, 'Y', 1, 'N', 0) * NVL(fid.item_quantity, 0) reduce_capacity
873 ,'Y' delete_flag
874 FROM pa_forecast_items fi
875 ,pa_forecast_item_details fid
876 ,gl_periods pp
877 ,gl_periods gp
878 WHERE fi.forecast_item_id = fid.forecast_item_id
879 AND fi.expenditure_org_id = l_org_id
880 AND fid.expenditure_org_id = l_org_id
881 AND fi.forecast_item_type IN ('A', 'U')
882 AND fid.util_summarized_code = 'N'
883 --AND fid.person_billable_flag = 'Y'
884 AND fid.amount_type_id = l_quantity_id
885 -- AND pp.period_set_name = l_period_set_name
886 AND pp.period_set_name = l_pa_period_set_name -- bug 3434019
887 AND pp.period_type = l_pa_period_type
888 --AND fi.pvdr_period_set_name = pp.period_set_name /* commented for bug 3488229 */
889 AND fi.pvdr_pa_period_name = pp.period_name
890 -- AND gp.period_set_name = l_period_set_name
891 AND gp.period_set_name = l_gl_period_set_name -- bug 3434019
892 AND gp.period_type = l_gl_period_type
893 --AND fi.pvdr_period_set_name = gp.period_set_name /* commented for bug 3488229 */
894 AND fi.pvdr_gl_period_name = gp.period_name
895 AND fi.item_date BETWEEN l_fc_start_date AND TRUNC(l_fc_end_date)+0.99999 ; /* BUG# 3118592 */
896 IF P_DEBUG_MODE = 'Y' THEN /* Added Debug Profile Option Check for bug#2674619 */
897 PA_DEBUG.G_Err_Stage := '2350 : After Inserting Forecast data into PA_REP_UTIL_SUMM0_TMP for PA and GL';
898 PA_DEBUG.Log_Message(p_message => PA_DEBUG.G_Err_Stage);
899 END IF;
900
901 -- Reset the error stack
902 PA_DEBUG.Reset_Curr_Function;
903
904 EXCEPTION
905 WHEN OTHERS THEN
906 RAISE;
907 END Insert_Fcst_Into_Tmp_PAGL;
908
909
910 --------------------------------------------
911 -- Procedure Insert_Fcst_Into_Tmp_PAGE
912 --------------------------------------------
913
914 PROCEDURE Insert_Fcst_Into_Tmp_PAGE
915 IS
916 BEGIN
917 -- Set the error satack
918 PA_DEBUG.Set_Curr_Function( p_function => 'Insert_Fcst_Into_Tmp_PAGE');
919
920 IF P_DEBUG_MODE = 'Y' THEN /* Added Debug Profile Option Check for bug#2674619 */
921 PA_DEBUG.G_Err_Stage := '2400 : Before Inserting Forecast data into PA_REP_UTIL_SUMM0_TMP for PA and GE';
922 PA_DEBUG.Log_Message(p_message => PA_DEBUG.G_Err_Stage);
923 END IF;
924
925 INSERT INTO pa_rep_util_summ00_tmp
926 ( row_id
927 ,parent_row_id
928 ,expenditure_organization_id
929 ,person_id
930 ,assignment_id
931 ,work_type_id
932 ,org_util_category_id
933 ,res_util_category_id
934 ,expenditure_type
935 ,expenditure_type_class
936 ,pa_period_name
937 ,pa_period_num
938 ,pa_period_year
939 ,pa_quarter_number
940 ,gl_period_name
941 ,gl_period_num
942 ,gl_period_year
943 ,gl_quarter_number
944 ,global_exp_period_end_date
945 ,global_exp_year
946 ,global_exp_month_number
947 ,total_hours
948 ,total_prov_hours
949 ,total_wghted_hours_people
950 ,total_wghted_hours_org
951 ,prov_wghted_hours_people
952 ,prov_wghted_hours_org
953 ,reduce_capacity
954 ,delete_flag
955 )
956 SELECT fid.rowid row_id
957 ,fi.rowid parent_row_id
958 ,fi.expenditure_organization_id
959 ,fi.person_id
960 ,fi.assignment_id
961 ,fid.work_type_id
962 ,fid.org_util_category_id
963 ,fid.resource_util_category_id
964 ,fi.expenditure_type
965 ,fi.expenditure_type_class
966 ,fi.pvdr_pa_period_name pa_period_name
967 ,(pp.period_year * 10000) + pp.period_num pa_period_num
968 ,pp.period_year pa_period_year
969 ,pp.quarter_num pa_quarter_number
970 ,NULL gl_period_name
971 ,NULL gl_period_num
972 ,NULL gl_period_year
973 ,NULL gl_quarter_number
974 ,trunc(fi.global_exp_period_end_date) global_exp_period_end_date
975 ,to_number(to_char(fi.global_exp_period_end_date, 'YYYY')) global_exp_year
976 ,to_number(to_char(fi.global_exp_period_end_date, 'MM')) global_exp_month_number
977 ,NVL(fid.item_quantity, 0) total_hours
978 ,NVL(fid.item_quantity, 0) * decode(fid.provisional_flag, 'Y', 1, 0) total_prov_hours
979 ,NVL(fid.resource_util_weighted, 0) total_wghted_hours_people
980 ,NVL(fid.org_util_weighted, 0) total_wghted_hours_org
981 ,NVL(fid.resource_util_weighted, 0) * decode(fid.provisional_flag, 'Y', 1, 0) prov_wghted_hours_people
982 ,NVL(fid.org_util_weighted, 0) * decode(fid.provisional_flag, 'Y', 1, 0) prov_wghted_hours_org
983 ,DECODE(fid.reduce_capacity_flag, 'Y', 1, 'N', 0) * NVL(fid.item_quantity, 0) reduce_capacity
984 ,'Y' delete_flag
985 FROM pa_forecast_items fi
986 ,pa_forecast_item_details fid
987 ,gl_periods pp
988 WHERE fi.forecast_item_id = fid.forecast_item_id
989 AND fi.expenditure_org_id = l_org_id
990 AND fid.expenditure_org_id = l_org_id
991 AND fi.forecast_item_type IN ('A', 'U')
992 AND fid.util_summarized_code = 'N'
993 --AND fid.person_billable_flag = 'Y'
994 AND fid.amount_type_id = l_quantity_id
995 -- AND pp.period_set_name = l_period_set_name
996 AND pp.period_set_name = l_pa_period_set_name -- bug 3434019
997 AND pp.period_type = l_pa_period_type
998 --AND fi.pvdr_period_set_name = pp.period_set_name /* commented for bug 3488229 */
999 AND fi.pvdr_pa_period_name = pp.period_name
1000 AND fi.item_date BETWEEN l_fc_start_date AND TRUNC(l_fc_end_date)+0.99999 ; /* BUG# 3118592 */
1001 IF P_DEBUG_MODE = 'Y' THEN /* Added Debug Profile Option Check for bug#2674619 */
1002 PA_DEBUG.G_Err_Stage := '2450 : After Inserting Forecast data into PA_REP_UTIL_SUMM0_TMP for PA and GE';
1003 PA_DEBUG.Log_Message(p_message => PA_DEBUG.G_Err_Stage);
1004 END IF;
1005
1006 -- Reset the error stack
1007 PA_DEBUG.Reset_Curr_Function;
1008
1009 EXCEPTION
1010 WHEN OTHERS THEN
1011 RAISE;
1012 END Insert_Fcst_Into_Tmp_PAGE;
1013
1014
1015 --------------------------------------------
1016 -- Procedure Insert_Fcst_Into_Tmp_GLGE
1017 --------------------------------------------
1018
1019 PROCEDURE Insert_Fcst_Into_Tmp_GLGE
1020 IS
1021 BEGIN
1022 -- Set the error satack
1023 PA_DEBUG.Set_Curr_Function( p_function => 'Insert_Fcst_Into_Tmp_GLGE');
1024
1025 IF P_DEBUG_MODE = 'Y' THEN /* Added Debug Profile Option Check for bug#2674619 */
1026 PA_DEBUG.G_Err_Stage := '2500 : Before Inserting Forecast data into PA_REP_UTIL_SUMM0_TMP for GL and GE';
1027 PA_DEBUG.Log_Message(p_message => PA_DEBUG.G_Err_Stage);
1028 END IF;
1029
1030 INSERT INTO pa_rep_util_summ00_tmp
1031 ( row_id
1032 ,parent_row_id
1033 ,expenditure_organization_id
1034 ,person_id
1035 ,assignment_id
1036 ,work_type_id
1037 ,org_util_category_id
1038 ,res_util_category_id
1039 ,expenditure_type
1040 ,expenditure_type_class
1041 ,pa_period_name
1042 ,pa_period_num
1043 ,pa_period_year
1044 ,pa_quarter_number
1045 ,gl_period_name
1046 ,gl_period_num
1047 ,gl_period_year
1048 ,gl_quarter_number
1049 ,global_exp_period_end_date
1050 ,global_exp_year
1051 ,global_exp_month_number
1052 ,total_hours
1053 ,total_prov_hours
1054 ,total_wghted_hours_people
1055 ,total_wghted_hours_org
1056 ,prov_wghted_hours_people
1057 ,prov_wghted_hours_org
1058 ,reduce_capacity
1059 ,delete_flag
1060 )
1061 SELECT fid.rowid row_id
1062 ,fi.rowid parent_row_id
1063 ,fi.expenditure_organization_id
1064 ,fi.person_id
1065 ,fi.assignment_id
1066 ,fid.work_type_id
1067 ,fid.org_util_category_id
1068 ,fid.resource_util_category_id
1069 ,fi.expenditure_type
1070 ,fi.expenditure_type_class
1071 ,NULL pa_period_name
1072 ,NULL pa_period_num
1073 ,NULL pa_period_year
1074 ,NULL pa_quarter_number
1075 ,fi.pvdr_gl_period_name gl_period_name
1076 ,(gp.period_year * 10000) + gp.period_num gl_period_num
1077 ,gp.period_year gl_period_year
1078 ,gp.quarter_num gl_quarter_number
1079 ,trunc(fi.global_exp_period_end_date) global_exp_period_end_date
1080 ,to_number(to_char(fi.global_exp_period_end_date, 'YYYY')) global_exp_year
1081 ,to_number(to_char(fi.global_exp_period_end_date, 'MM')) global_exp_month_number
1082 ,NVL(fid.item_quantity, 0) total_hours
1083 ,NVL(fid.item_quantity, 0) * decode(fid.provisional_flag, 'Y', 1, 0) total_prov_hours
1084 ,NVL(fid.resource_util_weighted, 0) total_wghted_hours_people
1085 ,NVL(fid.org_util_weighted, 0) total_wghted_hours_org
1086 ,NVL(fid.resource_util_weighted, 0) * decode(fid.provisional_flag, 'Y', 1, 0) prov_wghted_hours_people
1087 ,NVL(fid.org_util_weighted, 0) * decode(fid.provisional_flag, 'Y', 1, 0) prov_wghted_hours_org
1088 ,DECODE(fid.reduce_capacity_flag, 'Y', 1, 'N', 0) * NVL(fid.item_quantity, 0) reduce_capacity
1089 ,'Y' delete_flag
1090 FROM pa_forecast_items fi
1091 ,pa_forecast_item_details fid
1092 ,gl_periods gp
1093 WHERE fi.forecast_item_id = fid.forecast_item_id
1094 AND fi.expenditure_org_id = l_org_id
1095 AND fid.expenditure_org_id = l_org_id
1096 AND fi.forecast_item_type IN ('A', 'U')
1097 AND fid.util_summarized_code = 'N'
1098 --AND fid.person_billable_flag = 'Y'
1099 AND fid.amount_type_id = l_quantity_id
1100 -- AND gp.period_set_name = l_period_set_name
1101 AND gp.period_set_name = l_gl_period_set_name -- bug 3434019
1102 AND gp.period_type = l_gl_period_type
1103 --AND fi.pvdr_period_set_name = gp.period_set_name /* commented for bug 3488229 */
1104 AND fi.pvdr_gl_period_name = gp.period_name
1105 AND fi.item_date BETWEEN l_fc_start_date AND TRUNC(l_fc_end_date)+0.99999; /* BUG# 3118592 */
1106 IF P_DEBUG_MODE = 'Y' THEN /* Added Debug Profile Option Check for bug#2674619 */
1107 PA_DEBUG.G_Err_Stage := '2550 : After Inserting Forecast data into PA_REP_UTIL_SUMM0_TMP for GL and GE';
1108 PA_DEBUG.Log_Message(p_message => PA_DEBUG.G_Err_Stage);
1109 END IF;
1110
1111 -- Reset the error stack
1112 PA_DEBUG.Reset_Curr_Function;
1113
1114 EXCEPTION
1115 WHEN OTHERS THEN
1116 RAISE;
1117 END Insert_Fcst_Into_Tmp_GLGE;
1118
1119
1120 --------------------------------------------
1121 -- Procedure Insert_Fcst_Into_Tmp_PAGLGE
1122 --------------------------------------------
1123
1124 PROCEDURE Insert_Fcst_Into_Tmp_PAGLGE
1125 IS
1126 BEGIN
1127 -- Set the error satack
1128 PA_DEBUG.Set_Curr_Function( p_function => 'Insert_Fcst_Into_Tmp_PAGLGE');
1129 IF P_DEBUG_MODE = 'Y' THEN /* Added Debug Profile Option Check for bug#2674619 */
1130 PA_DEBUG.G_Err_Stage := '2600 : Before Inserting Forecast data into PA_REP_UTIL_SUMM0_TMP for PA, GL and GE';
1131 PA_DEBUG.Log_Message(p_message => PA_DEBUG.G_Err_Stage);
1132 END IF;
1133
1134 INSERT INTO pa_rep_util_summ00_tmp
1135 ( row_id
1136 ,parent_row_id
1137 ,expenditure_organization_id
1138 ,person_id
1139 ,assignment_id
1140 ,work_type_id
1141 ,org_util_category_id
1142 ,res_util_category_id
1143 ,expenditure_type
1144 ,expenditure_type_class
1145 ,pa_period_name
1146 ,pa_period_num
1147 ,pa_period_year
1148 ,pa_quarter_number
1149 ,gl_period_name
1150 ,gl_period_num
1151 ,gl_period_year
1152 ,gl_quarter_number
1153 ,global_exp_period_end_date
1154 ,global_exp_year
1155 ,global_exp_month_number
1156 ,total_hours
1157 ,total_prov_hours
1158 ,total_wghted_hours_people
1159 ,total_wghted_hours_org
1160 ,prov_wghted_hours_people
1161 ,prov_wghted_hours_org
1162 ,reduce_capacity
1163 ,delete_flag
1164 )
1165 SELECT fid.rowid row_id
1166 ,fi.rowid parent_row_id
1167 ,fi.expenditure_organization_id
1168 ,fi.person_id
1169 ,fi.assignment_id
1170 ,fid.work_type_id
1171 ,fid.org_util_category_id
1172 ,fid.resource_util_category_id
1173 ,fi.expenditure_type
1174 ,fi.expenditure_type_class
1175 ,fi.pvdr_pa_period_name pa_period_name
1176 ,(pp.period_year * 10000) + pp.period_num pa_period_num
1177 ,pp.period_year pa_period_year
1178 ,pp.quarter_num pa_quarter_number
1179 ,fi.pvdr_gl_period_name gl_period_name
1180 ,(gp.period_year * 10000) + gp.period_num gl_period_num
1181 ,gp.period_year gl_period_year
1182 ,gp.quarter_num gl_quarter_number
1183 ,trunc(fi.global_exp_period_end_date) global_exp_period_end_date
1184 ,to_number(to_char(fi.global_exp_period_end_date, 'YYYY')) global_exp_year
1185 ,to_number(to_char(fi.global_exp_period_end_date, 'MM')) global_exp_month_number
1186 ,NVL(fid.item_quantity, 0) total_hours
1187 ,NVL(fid.item_quantity, 0) * decode(fid.provisional_flag, 'Y', 1, 0) total_prov_hours
1188 ,NVL(fid.resource_util_weighted, 0) total_wghted_hours_people
1189 ,NVL(fid.org_util_weighted, 0) total_wghted_hours_org
1190 ,NVL(fid.resource_util_weighted, 0) * decode(fid.provisional_flag, 'Y', 1, 0) prov_wghted_hours_people
1191 ,NVL(fid.org_util_weighted, 0) * decode(fid.provisional_flag, 'Y', 1, 0) prov_wghted_hours_org
1192 ,DECODE(fid.reduce_capacity_flag, 'Y', 1, 'N', 0) * NVL(fid.item_quantity, 0) reduce_capacity
1193 ,'Y' delete_flag
1194 FROM pa_forecast_items fi
1195 ,pa_forecast_item_details fid
1196 ,gl_periods pp
1197 ,gl_periods gp
1198 WHERE fi.forecast_item_id = fid.forecast_item_id
1199 AND fi.expenditure_org_id = l_org_id
1200 AND fid.expenditure_org_id = l_org_id
1201 AND fi.forecast_item_type IN ('A', 'U')
1202 AND fid.util_summarized_code = 'N'
1203 --AND fid.person_billable_flag = 'Y'
1204 AND fid.amount_type_id = l_quantity_id
1205 -- AND pp.period_set_name = l_period_set_name
1206 AND pp.period_set_name = l_pa_period_set_name -- bug 3434019
1207 AND pp.period_type = l_pa_period_type
1208 --AND fi.pvdr_period_set_name = pp.period_set_name /* commented for bug 3488229 */
1209 AND fi.pvdr_pa_period_name = pp.period_name
1210 -- AND gp.period_set_name = l_period_set_name
1211 AND gp.period_set_name = l_gl_period_set_name -- bug 3434019
1212 AND gp.period_type = l_gl_period_type
1213 --AND fi.pvdr_period_set_name = gp.period_set_name /* commented for bug 3488229 */
1214 AND fi.pvdr_gl_period_name = gp.period_name
1215 AND fi.item_date BETWEEN l_fc_start_date AND TRUNC(l_fc_end_date)+0.99999 ; /* BUG# 3118592 */
1216
1217 IF P_DEBUG_MODE = 'Y' THEN /* Added Debug Profile Option Check for bug#2674619 */
1218 PA_DEBUG.G_Err_Stage := '2650 : After Inserting Forecast data into PA_REP_UTIL_SUMM0_TMP for PA, GL and GE';
1219 PA_DEBUG.Log_Message(p_message => PA_DEBUG.G_Err_Stage);
1220 END IF;
1221
1222 -- Reset the error stack
1223 PA_DEBUG.Reset_Curr_Function;
1224
1225 EXCEPTION
1226 WHEN OTHERS THEN
1227 RAISE;
1228 END Insert_Fcst_Into_Tmp_PAGLGE;
1229
1230
1231 END PA_SUMMARIZE_FORECAST_UTIL_PVT;