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