[Home] [Help]
PACKAGE BODY: APPS.PA_PPR_OLAP_CONC_PROGRAM
Source
1 package body PA_ppr_olap_conc_program as
2 /* $Header: PAPPR06B.pls 120.0.12020000.8 2013/04/15 18:22:42 sachandr noship $ */
3
4 G_PKG_NAME CONSTANT VARCHAR2(30) := 'PA_PPR_CONCURRENT_PROGRAM';
5 g_extraction_type VARCHAR2(20);
6
7 procedure ppr_log (
8 p_msg IN VARCHAR2
9 ) as
10
11 begin
12 -- insert into sri_log values ( fnd_log_messages_s.nextval, p_msg || to_char(sysdate, 'DD-MON-YYYY HH24:MI:SS'));
13 -- DBMS_OUTPUT.PUT_LINE(p_msg);
14 --commit;
15 null;
16 end ppr_log;
17
18 procedure maintain_projects (
19 p_group_size IN NUMBER
20 ) as
21
22 l_count NUMBER;
23 l_olap_group NUMBER;
24 l_curr_olap_group_count NUMBER;
25 l_prj_count NUMBER;
26 l_loop NUMBER;
27 begin
28
29 BEGIN
30 select count(*), olap_group
31 into l_count, l_olap_group
32 from pa_projects_all
33 where olap_group is not null
34 group by olap_group
35 having count(*) < p_group_size;
36
37 EXCEPTION WHEN NO_DATA_FOUND THEN
38
39 l_olap_group := null;
40 END;
41
42 IF l_olap_group IS NOT NULL THEN
43 l_curr_olap_group_count := p_group_size - l_count;
44
45 update pa_projects_all
46 set olap_group = l_olap_group --,
47 --struct_change_flag = 'Y'
48 where olap_group is null
49 and rownum <= l_curr_olap_group_count;
50
51 END IF;
52
53 select count(*) into l_prj_count from pa_projects_all where olap_group is null;
54
55 select ceil(l_prj_count/p_group_size) into l_loop from dual;
56
57 FOR i in 1..l_loop LOOP
58 select PA_OLAP_GROUP_S.nextval into l_olap_group from dual;
59
60 update pa_projects_all
61 set olap_group = l_olap_group --,
62 -- struct_change_flag = 'Y'
63 where olap_group is null
64 and rownum <= p_group_size;
65
66 END LOOP;
67 -- update olap_task_id
68
69 end maintain_projects;
70
71 Procedure maintain_budgets (
72 p_group_size IN NUMBER) as
73
74 g_equip_resclass_code VARCHAR2(8) := 'EQUIP';
75 g_people_resclass_code VARCHAR2(8) := 'PEOPLE';
76
77 BEGIN
78 insert into pa_budget_versions_olap_tmp1
79 (project_id ,
80 budget_version_id ,
81 PROJFUNC_CURRENCY_CODE,
82 PROJECT_CURRENCY_CODE
83 )
84 select p.project_id,
85 b.budget_version_id,
86 p.PROJFUNC_CURRENCY_CODE,
87 p.PROJECT_CURRENCY_CODE
88 from pa_budget_versions b, pa_projects_all p, pa_pjt_proj_batch batch
89 where b.olap_change_flag = 'Y'
90 and b.project_id = p.project_id
91 and p.project_id = batch.project_id;
92
93 insert into pa_budget_versions_olap_tmp2
94 (curr_type,
95 project_id,
96 budget_version_id,
97 currency_code
98 )
99 select 'ALL', p.project_id, b.budget_version_id, p.PROJFUNC_CURRENCY_CODE
100 from pa_budget_versions_olap_tmp1 b, pa_projects_all p
101 where b.project_id = p.project_id
102 and p.PROJFUNC_CURRENCY_CODE = p.PROJECT_CURRENCY_CODE
103 UNION
104 select 'PFC', p.project_id, b.budget_version_id, p.PROJFUNC_CURRENCY_CODE
105 from pa_budget_versions_olap_tmp1 b, pa_projects_all p
106 where b.project_id = p.project_id
107 and p.PROJFUNC_CURRENCY_CODE <> p.PROJECT_CURRENCY_CODE
108 UNION
109 select 'PC', p.project_id, b.budget_version_id, p.PROJECT_CURRENCY_CODE
110 from pa_budget_versions_olap_tmp1 b, pa_projects_all p
111 where b.project_id = p.project_id
112 and p.PROJFUNC_CURRENCY_CODE <> p.PROJECT_CURRENCY_CODE;
113
114
115 INSERT INTO PJI_FM_EXTR_PLNVER4
116 (
117 WORKER_ID ,
118 PROJECT_ID ,
119 PLAN_VERSION_ID ,
120 WBS_STRUCT_VERSION_ID ,
121 RBS_STRUCT_VERSION_ID ,
122 PLAN_TYPE_CODE ,
123 PLAN_TYPE_ID ,
124 TIME_PHASED_TYPE_CODE ,
125 TIME_DANGLING_FLAG ,
126 RATE_DANGLING_FLAG ,
127 PROJECT_TYPE_CLASS ,
128 WP_FLAG ,
129 CURRENT_FLAG ,
130 ORIGINAL_FLAG ,
131 CURRENT_ORIGINAL_FLAG ,
132 BASELINED_FLAG ,
133 SECONDARY_RBS_FLAG ,
134 LP_FLAG
135 )
136 SELECT
137 1
138 , bv.project_id project_id
139 , bv.budget_version_id plan_version_id
140 , DECODE ( NVL(bv.wp_version_flag, 'N')
141 , 'Y', bv.project_structure_version_id
142 , PA_PROJECT_STRUCTURE_UTILS.GET_FIN_STRUC_VER_ID(bv.project_id) -- -9999 --
143 ) wbs_struct_version_id
144 , fpo.rbs_version_id rbs_struct_version_id
145 -- , to_char(fpo.fin_plan_type_id) plan_type_code
146 , DECODE (bv.version_type, 'COST' ,'C' , 'REVENUE', 'R', 'A') plan_type_code /* 4471527 */
147 , fpo.fin_plan_type_id plan_type_id
148 , DECODE(bv.version_type
149 , 'ALL', fpo.all_time_phased_code
150 , 'COST', fpo.cost_time_phased_code
151 , 'REVENUE', fpo.revenue_time_phased_code
152 ) time_phased_type_code
153 , NULL time_dangling_flag -- to be used for dangling check.
154 , NULL rate_dangling_flag -- to be used for dangling check.
155 , NULL PROJECT_TYPE_CLASS
156 , NVL(bv.wp_version_flag, 'N') is_wp_flag
157 , bv.current_flag current_flag
158 , bv.original_flag original_flag
159 , bv.current_original_flag current_original_flag
160 , DECODE(bv.baselined_date, NULL, 'N', 'Y') baselined_flag
161 , 'N' SECONDARY_RBS_FLAG
162 , DECODE( NVL(bv.wp_version_flag, 'N')
163 , 'Y'
164 , DECODE(bv.project_structure_version_id
165 , PA_PROJECT_STRUCTURE_UTILS.GET_LATEST_WP_VERSION( bv.project_id) -- 4682341
166 -- , PA_PROJECT_STRUCTURE_UTILS.GET_FIN_STRUC_VER_ID(bv.project_id)
167 , 'Y'
168 , 'N')
169 , 'N'
170 ) lp_flag
171 FROM
172 pa_budget_versions bv -- @pjdev115 bv
173 , pa_proj_fp_options fpo -- @pjdev115 fpo
174 , pa_projects_all ppa -- @pjdev115 ppa
175 , pa_budget_versions_olap_tmp1 b1
176 WHERE 1=1
177 AND ppa.project_id = bv.project_id
178 AND bv.budget_version_id = b1.budget_version_id
179 AND bv.version_type is not NULL -- COST, REVENUE, etc. Should not be null.
180 AND bv.fin_plan_type_id is not NULL -- Old budgets model data is not picked up with this condition.
181 AND fpo.project_id = bv.project_id
182 AND bv.fin_plan_type_id = fpo.fin_plan_type_id
183 AND bv.budget_version_id = fpo.fin_plan_version_id
184 AND bv.fin_plan_type_id <> 10 /* Added for bug 8708651*/
185 AND fpo.fin_plan_option_level_code = 'PLAN_VERSION' -- Other values are: plan type and project.
186 AND bv.version_type IN ( 'ALL' , 'COST' , 'REVENUE');
187
188 INSERT INTO pji_time_cal_period
189 (
190 CAL_PERIOD_ID,
191 CAL_QTR_ID,
192 CALENDAR_ID,
193 SEQUENCE,
194 NAME ,
195 START_DATE,
196 END_DATE,
197 CREATION_DATE,
198 LAST_UPDATE_DATE,
199 LAST_UPDATED_BY,
200 CREATED_BY,
201 LAST_UPDATE_LOGIN
202 )
203 SELECT -1 /*-x_max_project_id*/ cal_period_id
204 , -1 /*-x_max_project_id*/ cal_qtr_id
205 , -1 /*-x_max_project_id*/ calendar_id
206 , -1 /*-x_max_project_id*/ SEQUENCE
207 , 'XXX' name
208 , trunc(sysdate) start_date
209 , trunc(sysdate) end_date
210 , sysdate creation_date
211 , sysdate last_update_date
212 , -1 last_updated_by
213 , -1 created_by
214 , -1 last_update_login
215 FROM DUAL;
216
217 INSERT INTO pji_fp_aggr_pjp1
218 (
219 WORKER_ID,
220 PROJECT_ID
221 , PROJECT_ORG_ID
222 , PROJECT_ORGANIZATION_ID
223 , PROJECT_ELEMENT_ID
224 , TIME_ID
225 , PERIOD_TYPE_ID
226 , CALENDAR_TYPE
227 , RBS_AGGR_LEVEL
228 , WBS_ROLLUP_FLAG
229 , PRG_ROLLUP_FLAG
230 , CURR_RECORD_TYPE_ID
231 , CURRENCY_CODE
232 , RBS_ELEMENT_ID
233 , RBS_VERSION_ID
234 , PLAN_VERSION_ID
235 , PLAN_TYPE_ID
236 , RAW_COST
237 , BRDN_COST
238 , REVENUE
239 , BILL_RAW_COST
240 , BILL_BRDN_COST
241 , BILL_LABOR_RAW_COST
242 , BILL_LABOR_BRDN_COST
243 , BILL_LABOR_HRS
244 , EQUIPMENT_RAW_COST
245 , EQUIPMENT_BRDN_COST
246 , CAPITALIZABLE_RAW_COST
247 , CAPITALIZABLE_BRDN_COST
248 , LABOR_RAW_COST
249 , LABOR_BRDN_COST
250 , LABOR_HRS
251 , LABOR_REVENUE
252 , EQUIPMENT_HOURS
253 , BILLABLE_EQUIPMENT_HOURS
254 , SUP_INV_COMMITTED_COST
255 , PO_COMMITTED_COST
256 , PR_COMMITTED_COST
257 , OTH_COMMITTED_COST
258 , ACT_LABOR_HRS
259 , ACT_EQUIP_HRS
260 , ACT_LABOR_BRDN_COST
261 , ACT_EQUIP_BRDN_COST
262 , ACT_BRDN_COST
263 , ACT_RAW_COST
264 , ACT_REVENUE
265 , ACT_LABOR_RAW_COST
266 , ACT_EQUIP_RAW_COST
267 , ETC_LABOR_HRS
268 , ETC_EQUIP_HRS
269 , ETC_LABOR_BRDN_COST
270 , ETC_EQUIP_BRDN_COST
271 , ETC_BRDN_COST
272 , ETC_RAW_COST
273 , ETC_LABOR_RAW_COST
274 , ETC_EQUIP_RAW_COST
275 , CUSTOM1
276 , CUSTOM2
277 , CUSTOM3
278 , CUSTOM4
279 , CUSTOM5
280 , CUSTOM6
281 , CUSTOM7
282 , CUSTOM8
283 , CUSTOM9
284 , CUSTOM10
285 , CUSTOM11
286 , CUSTOM12
287 , CUSTOM13
288 , CUSTOM14
289 , CUSTOM15
290 , LINE_TYPE
291 , RATE_DANGLING_FLAG
292 , TIME_DANGLING_FLAG
293 -- , START_DATE
294 -- , END_DATE
295 , PRG_LEVEL
296 ,PLAN_TYPE_CODE
297 --, cbs_version_id
298 --, cbs_element_id
299 )
300 select
301 --p1.project_group ,
302 --p1.olap_group,
303 10,
304 b.project_id
305 , p1.org_id
306 , 1
307 --, ra.task_id,
308 , DECODE(ra.task_id, 0, -1, ra.task_id), --satya
309 decode(DECODE(b1.version_type
310 , 'ALL', fpo.all_time_phased_code
311 , 'COST', fpo.cost_time_phased_code
312 , 'REVENUE', fpo.revenue_time_phased_code
313 ), 'N'
314 , -1, cal_period_id
315 ) time_id,
316 decode(DECODE(b1.version_type
317 , 'ALL', fpo.all_time_phased_code
318 , 'COST', fpo.cost_time_phased_code
319 , 'REVENUE', fpo.revenue_time_phased_code
320 ),
321 'P', 32
322 , 'G', 32
323 , 'N', 2048
324 ) period_type_id,
325 decode(DECODE(b1.version_type
326 , 'ALL', fpo.all_time_phased_code
327 , 'COST', fpo.cost_time_phased_code
328 , 'REVENUE', fpo.revenue_time_phased_code
329 ),
330 'P', 'P'
331 , 'G', 'G'
332 , 'N', 'A'
333 ) calendar_type,
334 'L' RBS_AGGR_LEVEL
335 , 'N' WBS_ROLLUP_FLAG
336 , 'N' PRG_ROLLUP_FLAG
337 , 1 CURR_RECORD_TYPE -- curr code missing. --satya
338 , b.currency_code
339 , ra.RBS_ELEMENT_ID
340 , fpo.RBS_VERSION_ID
341 , b.BUDGET_VERSION_ID
342 , fpo.fin_plan_type_id
343 , decode(b.curr_type, 'ALL',bl.raw_cost,'PFC',bl.RAW_COST,'PC',bl.PROJECT_RAW_COST)
344 , decode(b.curr_type, 'ALL',bl.burdened_cost,'PFC',bl.burdened_cost,'PC',bl.PROJECT_BURDENED_COST)
345 , decode(b.curr_type, 'ALL',bl.revenue,'PFC',bl.revenue,'PC',bl.PROJECT_REVENUE),
346 DECODE ( ra.rate_based_flag, 'Y', decode(b.curr_type, 'ALL',bl.raw_cost,'PFC',bl.RAW_COST,'PC',bl.PROJECT_RAW_COST), 0 ) BILL_RAW_COST
347 , DECODE ( ra.rate_based_flag, 'Y', decode(b.curr_type, 'ALL',bl.burdened_cost,'PFC',bl.burdened_cost,'PC',bl.PROJECT_BURDENED_COST), 0 ) BILL_BRDN_COST
348 , DECODE ( ra.rate_based_flag || ra.resource_class_code, 'YPEOPLE', decode(b.curr_type, 'ALL',bl.raw_cost,'PFC',bl.RAW_COST,'PC',bl.PROJECT_RAW_COST), 0 ) BILL_LABOR_RAW_COST
349 , DECODE ( ra.rate_based_flag || ra.resource_class_code, 'YPEOPLE' , decode(b.curr_type, 'ALL',bl.burdened_cost,'PFC',bl.burdened_cost,'PC',bl.PROJECT_BURDENED_COST), 0 ) BILL_LABOR_BRDN_COST
350 , DECODE ( ra.rate_based_flag || ra.resource_class_code , 'YPEOPLE', DECODE ( ra.rate_based_flag , 'Y' , bl.quantity, 0 ) , 0 ) BILL_LABOR_HRS
351 , DECODE ( ra.resource_class_code, g_equip_resclass_code , decode(b.curr_type, 'ALL',bl.raw_cost,'PFC',bl.RAW_COST,'PC',bl.PROJECT_RAW_COST), 0 ) EQUIPMENT_RAW_COST
352 , DECODE ( ra.resource_class_code, g_equip_resclass_code , decode(b.curr_type, 'ALL',bl.burdened_cost,'PFC',bl.burdened_cost,'PC',bl.PROJECT_BURDENED_COST), 0 ) EQUIPMENT_BRDN_COST
353 , DECODE ( ra.rate_based_flag, 'Y', decode(b.curr_type, 'ALL',bl.raw_cost,'PFC',bl.RAW_COST,'PC',bl.PROJECT_RAW_COST), 0 ) CAPITALIZABLE_RAW_COST
354 , DECODE ( ra.rate_based_flag, 'Y', decode(b.curr_type, 'ALL',bl.burdened_cost,'PFC',bl.burdened_cost,'PC',bl.PROJECT_BURDENED_COST), 0 ) CAPITALIZABLE_BRDN_COST
355 , DECODE ( ra.resource_class_code, g_people_resclass_code, decode(b.curr_type, 'ALL',bl.raw_cost,'PFC',bl.RAW_COST,'PC',bl.PROJECT_RAW_COST), 0 ) LABOR_RAW_COST
356 , DECODE ( ra.resource_class_code, g_people_resclass_code, decode(b.curr_type, 'ALL',bl.burdened_cost,'PFC',bl.burdened_cost,'PC',bl.PROJECT_BURDENED_COST), 0 ) LABOR_BRDN_COST
357 , DECODE ( ra.resource_class_code, g_people_resclass_code,
358 DECODE ( ra.rate_based_flag, 'Y',bl.quantity,0), 0 ) labor_hrs -- bug 6039785
359 , DECODE ( ra.resource_class_code, g_people_resclass_code, decode(b.curr_type, 'ALL',bl.revenue,'PFC',bl.revenue,'PC',bl.PROJECT_REVENUE), 0 ) LABOR_REVENUE
360 , DECODE ( ra.resource_class_code, g_equip_resclass_code,
361 DECODE ( ra.rate_based_flag, 'Y',bl.quantity,0)) EQUIPMENT_HOURS -- bug 6039785
362 , DECODE ( ra.rate_based_flag || ra.resource_class_code, 'YEQUIPMENT',
363 DECODE ( ra.rate_based_flag , 'Y' , bl.quantity, 0 ) , 0 ) BILLABLE_EQUIPMENT_HOURS -- bug 6039785
364 , to_number(NULL) SUP_INV_COMMITTED_COST
365 , to_number(NULL) PO_COMMITTED_COST
366 , to_number(NULL) PR_COMMITTED_COST
367 , to_number(NULL) OTH_COMMITTED_COST
368 , DECODE ( ra.resource_class_code, g_people_resclass_code,
369 DECODE ( ra.rate_based_flag , 'Y',
370 DECODE ( ra.rate_based_flag, 'Y',bl.init_quantity,0), 0 ), 0 ) ACT_LABOR_HRS -- bug 6039785
371 , DECODE ( ra.resource_class_code, g_equip_resclass_code,
372 DECODE ( ra.rate_based_flag , 'Y',
373 DECODE ( ra.rate_based_flag, 'Y',bl.init_quantity,0), 0 ), 0 ) ACT_EQUIP_HOURS -- bug 6039785
374 , DECODE ( ra.resource_class_code, g_people_resclass_code, decode(b.curr_type, 'ALL',bl.init_burdened_cost,'PFC',bl.init_burdened_cost,'PC',bl.PROJECT_init_burdened_cost), 0 ) ACT_LABOR_BRDN_COST
375 , DECODE ( ra.resource_class_code, g_equip_resclass_code , decode(b.curr_type, 'ALL',bl.init_burdened_cost,'PFC',bl.init_burdened_cost,'PC',bl.PROJECT_init_burdened_cost), 0 ) ACT_EQUIPMENT_BRDN_COST
376 , decode(b.curr_type, 'ALL',bl.init_burdened_cost,'PFC',bl.init_burdened_cost,'PC',bl.PROJECT_init_burdened_cost) ACT_BRDN_COST
377 , decode(b.curr_type, 'ALL',bl.init_raw_cost,'PFC',bl.init_raw_cost,'PC',bl.PROJECT_init_raw_cost) ACT_RAW_COST
378 , decode(b.curr_type, 'ALL',bl.init_revenue,'PFC',bl.init_revenue,'PC',bl.PROJECT_init_revenue) ACT_REVENUE
379 , DECODE ( ra.resource_class_code, g_people_resclass_code, decode(b.curr_type, 'ALL',bl.init_raw_cost,'PFC',bl.init_raw_cost,'PC',bl.PROJECT_init_raw_cost), 0 ) ACT_LABOR_RAW_COST
380 , DECODE ( ra.resource_class_code, g_equip_resclass_code , decode(b.curr_type, 'ALL',bl.init_raw_cost,'PFC',bl.init_raw_cost,'PC',bl.PROJECT_init_raw_cost), 0 ) ACT_EQUIPMENT_RAW_COST
381 , DECODE ( ra.resource_class_code, g_people_resclass_code,
382 DECODE ( ra.rate_based_flag , 'Y',
383 DECODE ( ra.rate_based_flag, 'Y',bl.quantity - NVL(bl.init_quantity, 0),0), 0 ), 0 ) ETC_LABOR_HRS -- bug 6039785
384 , DECODE ( ra.resource_class_code, g_equip_resclass_code,
385 DECODE ( ra.rate_based_flag , 'Y',
386 DECODE ( ra.rate_based_flag, 'Y',bl.quantity - NVL(bl.init_quantity,0), 0 ), 0 ) ) ETC_EQUIP_HOURS -- bug 6039785
387 , DECODE ( ra.resource_class_code, g_people_resclass_code, decode(b.curr_type, 'ALL', bl.burdened_cost - NVL(bl.init_burdened_cost, 0),'PFC', bl.burdened_cost - NVL(bl.init_burdened_cost, 0),
388 'PC',bl.project_raw_cost - NVL(bl.project_init_raw_cost, 0), 0 )) ETC_LABOR_BRDN_COST
389 , DECODE ( ra.resource_class_code, g_equip_resclass_code, decode(b.curr_type, 'ALL', bl.burdened_cost - NVL(bl.init_burdened_cost, 0),'PFC', bl.burdened_cost - NVL(bl.init_burdened_cost, 0),
390 'PC',bl.project_raw_cost - NVL(bl.project_init_raw_cost, 0), 0 )) ETC_EQUIP_BRDN_COST
391 , decode(b.curr_type, 'ALL', bl.burdened_cost - NVL(bl.init_burdened_cost, 0),'PFC', bl.burdened_cost - NVL(bl.init_burdened_cost, 0),
392 'PC',bl.project_raw_cost - NVL(bl.project_init_raw_cost, 0), 0 ) ETC_BRDN_COST
393 , decode(b.curr_type, 'ALL', bl.raw_cost - NVL(bl.init_raw_cost, 0),'PFC', bl.raw_cost - NVL(bl.init_raw_cost, 0),'PC',bl.project_raw_cost - NVL(bl.project_init_raw_cost, 0), 0 ) ETC_RAW_COST
394 , DECODE ( ra.resource_class_code, g_people_resclass_code, decode(b.curr_type, 'ALL', bl.raw_cost - NVL(bl.init_raw_cost, 0),'PFC', bl.raw_cost - NVL(bl.init_raw_cost, 0),
395 'PC',bl.project_raw_cost - NVL(bl.project_init_raw_cost, 0), 0 ) ) ETC_LABOR_RAW_COST
396 , DECODE ( ra.resource_class_code, g_equip_resclass_code, decode(b.curr_type, 'ALL', bl.raw_cost - NVL(bl.init_raw_cost, 0),'PFC', bl.raw_cost - NVL(bl.init_raw_cost, 0),
397 'PC',bl.project_raw_cost - NVL(bl.project_init_raw_cost, 0), 0 ) ) ETC_EQUIP_RAW_COST
398 , to_number(NULL) CUSTOM1
399 , to_number(NULL) CUSTOM2
400 , to_number(NULL) CUSTOM3
401 , to_number(NULL) CUSTOM4
402 , to_number(NULL) CUSTOM5
403 , to_number(NULL) CUSTOM6
404 , to_number(NULL) CUSTOM7
405 , to_number(NULL) CUSTOM8
406 , to_number(NULL) CUSTOM9
407 , to_number(NULL) CUSTOM10
408 , to_number(NULL) CUSTOM11
409 , to_number(NULL) CUSTOM12
410 , to_number(NULL) CUSTOM13
411 , to_number(NULL) CUSTOM14
412 , to_number(NULL) CUSTOM15
413 , DECODE(DECODE(b1.version_type
414 , 'ALL', fpo.all_time_phased_code
415 , 'COST', fpo.cost_time_phased_code
416 , 'REVENUE', fpo.revenue_time_phased_code
417 ), 'P', 'OF', 'G', 'OF', 'N', 'NTR', 'X')
418 , NULL time_dangling_flag
419 , NULL rate_dangling_flag
420 -- , plr.start_date
421 -- , plr.end_date
422 , 1 prg_level
423 ,DECODE (b1.version_type, 'COST' ,'C' , 'REVENUE', 'R', 'A') plan_type_code
424 -- , nvl(p1.cbs_version_id,-1) cbs_version_id --satya
425 -- , nvl(ra.cbs_element_id,-1) cbs_element_id
426 from
427 pa_budget_versions_olap_tmp2 b ,
428 pa_budget_versions b1,
429 pa_resource_assignments ra,
430 pa_budget_lines bl,
431 pji_time_cal_period per,
432 pa_projects_all p1,
433 pa_proj_fp_options fpo,
434 PJI_ORG_EXTR_INFO inf
435 where
436 b.budget_version_id = b1.budget_version_id
437 and b.budget_version_id = ra.budget_version_id
438 and ra.resource_assignment_id = bl.resource_assignment_id
439 and p1.project_id = b.project_id
440 and b.project_id = fpo.project_id
441 and decode(DECODE(b1.version_type
442 , 'ALL', fpo.all_time_phased_code
443 , 'COST', fpo.cost_time_phased_code
444 , 'REVENUE', fpo.revenue_time_phased_code
445 ), 'P'
446 , inf.pa_calendar_id
447 , 'G'
448 , inf.gl_calendar_id, -b1.project_id
449 ) = per.calendar_id
450 AND nvl(bl.period_name,'XXX') = per.name
451 AND bl.budget_version_id = fpo.fin_plan_version_id
452 --AND fpo.time_phased_type_code IN ('P', 'G')
453 AND fpo.project_id = p1.project_id
454 AND p1.org_id = inf.org_id
455 AND b1.fin_plan_type_id = fpo.fin_plan_type_id
456 and fpo.fin_plan_type_id <> 10
457 AND b.budget_version_id = fpo.fin_plan_version_id
458 AND fpo.fin_plan_option_level_code = 'PLAN_VERSION' -- Other values are: plan type and project.
459 AND b1.version_type IN ( 'ALL' , 'COST' , 'REVENUE')
460 AND bl.budget_version_id = b.budget_version_id;
461
462
463 INSERT INTO pji_fp_aggr_pjp1
464 (
465 WORKER_ID,
466 PROJECT_ID
467 , PROJECT_ORG_ID
468 , PROJECT_ORGANIZATION_ID
469 , PROJECT_ELEMENT_ID
470 , TIME_ID
471 , PERIOD_TYPE_ID
472 , CALENDAR_TYPE
473 , RBS_AGGR_LEVEL
474 , WBS_ROLLUP_FLAG
475 , PRG_ROLLUP_FLAG
476 , CURR_RECORD_TYPE_ID
477 , CURRENCY_CODE
478 , RBS_ELEMENT_ID
479 , RBS_VERSION_ID
480 , PLAN_VERSION_ID
481 , PLAN_TYPE_ID
482 , RAW_COST
483 , BRDN_COST
484 , REVENUE
485 , BILL_RAW_COST
486 , BILL_BRDN_COST
487 , BILL_LABOR_RAW_COST
488 , BILL_LABOR_BRDN_COST
489 , BILL_LABOR_HRS
490 , EQUIPMENT_RAW_COST
491 , EQUIPMENT_BRDN_COST
492 , CAPITALIZABLE_RAW_COST
493 , CAPITALIZABLE_BRDN_COST
494 , LABOR_RAW_COST
495 , LABOR_BRDN_COST
496 , LABOR_HRS
497 , LABOR_REVENUE
498 , EQUIPMENT_HOURS
499 , BILLABLE_EQUIPMENT_HOURS
500 , SUP_INV_COMMITTED_COST
501 , PO_COMMITTED_COST
502 , PR_COMMITTED_COST
503 , OTH_COMMITTED_COST
504 , ACT_LABOR_HRS
505 , ACT_EQUIP_HRS
506 , ACT_LABOR_BRDN_COST
507 , ACT_EQUIP_BRDN_COST
508 , ACT_BRDN_COST
509 , ACT_RAW_COST
510 , ACT_REVENUE
511 , ACT_LABOR_RAW_COST
512 , ACT_EQUIP_RAW_COST
513 , ETC_LABOR_HRS
514 , ETC_EQUIP_HRS
515 , ETC_LABOR_BRDN_COST
516 , ETC_EQUIP_BRDN_COST
517 , ETC_BRDN_COST
518 , ETC_RAW_COST
519 , ETC_LABOR_RAW_COST
520 , ETC_EQUIP_RAW_COST
521 , CUSTOM1
522 , CUSTOM2
523 , CUSTOM3
524 , CUSTOM4
525 , CUSTOM5
526 , CUSTOM6
527 , CUSTOM7
528 , CUSTOM8
529 , CUSTOM9
530 , CUSTOM10
531 , CUSTOM11
532 , CUSTOM12
533 , CUSTOM13
534 , CUSTOM14
535 , CUSTOM15
536 , LINE_TYPE
537 , RATE_DANGLING_FLAG
538 , TIME_DANGLING_FLAG
539 -- , START_DATE
540 -- , END_DATE
541 , PRG_LEVEL
542 ,PLAN_TYPE_CODE
543 --, cbs_version_id
544 --, cbs_element_id
545 )
546 select
547 --p1.project_group ,
548 --p1.olap_group,
549 10,
550 b.project_id
551 , p1.org_id
552 , 1
553 --, ra.task_id,
554 , DECODE(ra.task_id, 0, -1, ra.task_id), --satya
555 decode(DECODE(b1.version_type
556 , 'ALL', fpo.all_time_phased_code
557 , 'COST', fpo.cost_time_phased_code
558 , 'REVENUE', fpo.revenue_time_phased_code
559 ), 'N'
560 , -1, cal_period_id
561 ) time_id,
562 decode(DECODE(b1.version_type
563 , 'ALL', fpo.all_time_phased_code
564 , 'COST', fpo.cost_time_phased_code
565 , 'REVENUE', fpo.revenue_time_phased_code
566 ),
567 'P', 32
568 , 'G', 32
569 , 'N', 2048
570 ) period_type_id,
571 decode(DECODE(b1.version_type
572 , 'ALL', fpo.all_time_phased_code
573 , 'COST', fpo.cost_time_phased_code
574 , 'REVENUE', fpo.revenue_time_phased_code
575 ),
576 'P', 'P'
577 , 'G', 'G'
578 , 'N', 'A'
579 ) calendar_type,
580 'L' RBS_AGGR_LEVEL
581 , 'N' WBS_ROLLUP_FLAG
582 , 'N' PRG_ROLLUP_FLAG
583 , 1 CURR_RECORD_TYPE -- curr code missing. --satya
584 , b.currency_code
585 , -1
586 , -1
587 , b.BUDGET_VERSION_ID
588 , fpo.fin_plan_type_id
589 , decode(b.curr_type, 'ALL',bl.raw_cost,'PFC',bl.RAW_COST,'PC',bl.PROJECT_RAW_COST)
590 , decode(b.curr_type, 'ALL',bl.burdened_cost,'PFC',bl.burdened_cost,'PC',bl.PROJECT_BURDENED_COST)
591 , decode(b.curr_type, 'ALL',bl.revenue,'PFC',bl.revenue,'PC',bl.PROJECT_REVENUE),
592 DECODE ( ra.rate_based_flag, 'Y', decode(b.curr_type, 'ALL',bl.raw_cost,'PFC',bl.RAW_COST,'PC',bl.PROJECT_RAW_COST), 0 ) BILL_RAW_COST
593 , DECODE ( ra.rate_based_flag, 'Y', decode(b.curr_type, 'ALL',bl.burdened_cost,'PFC',bl.burdened_cost,'PC',bl.PROJECT_BURDENED_COST), 0 ) BILL_BRDN_COST
594 , DECODE ( ra.rate_based_flag || ra.resource_class_code, 'YPEOPLE', decode(b.curr_type, 'ALL',bl.raw_cost,'PFC',bl.RAW_COST,'PC',bl.PROJECT_RAW_COST), 0 ) BILL_LABOR_RAW_COST
595 , DECODE ( ra.rate_based_flag || ra.resource_class_code, 'YPEOPLE' , decode(b.curr_type, 'ALL',bl.burdened_cost,'PFC',bl.burdened_cost,'PC',bl.PROJECT_BURDENED_COST), 0 ) BILL_LABOR_BRDN_COST
596 , DECODE ( ra.rate_based_flag || ra.resource_class_code , 'YPEOPLE', DECODE ( ra.rate_based_flag , 'Y' , bl.quantity, 0 ) , 0 ) BILL_LABOR_HRS
597 , DECODE ( ra.resource_class_code, g_equip_resclass_code , decode(b.curr_type, 'ALL',bl.raw_cost,'PFC',bl.RAW_COST,'PC',bl.PROJECT_RAW_COST), 0 ) EQUIPMENT_RAW_COST
598 , DECODE ( ra.resource_class_code, g_equip_resclass_code , decode(b.curr_type, 'ALL',bl.burdened_cost,'PFC',bl.burdened_cost,'PC',bl.PROJECT_BURDENED_COST), 0 ) EQUIPMENT_BRDN_COST
599 , DECODE ( ra.rate_based_flag, 'Y', decode(b.curr_type, 'ALL',bl.raw_cost,'PFC',bl.RAW_COST,'PC',bl.PROJECT_RAW_COST), 0 ) CAPITALIZABLE_RAW_COST
600 , DECODE ( ra.rate_based_flag, 'Y', decode(b.curr_type, 'ALL',bl.burdened_cost,'PFC',bl.burdened_cost,'PC',bl.PROJECT_BURDENED_COST), 0 ) CAPITALIZABLE_BRDN_COST
601 , DECODE ( ra.resource_class_code, g_people_resclass_code, decode(b.curr_type, 'ALL',bl.raw_cost,'PFC',bl.RAW_COST,'PC',bl.PROJECT_RAW_COST), 0 ) LABOR_RAW_COST
602 , DECODE ( ra.resource_class_code, g_people_resclass_code, decode(b.curr_type, 'ALL',bl.burdened_cost,'PFC',bl.burdened_cost,'PC',bl.PROJECT_BURDENED_COST), 0 ) LABOR_BRDN_COST
603 , DECODE ( ra.resource_class_code, g_people_resclass_code,
604 DECODE ( ra.rate_based_flag, 'Y',bl.quantity,0), 0 ) labor_hrs -- bug 6039785
605 , DECODE ( ra.resource_class_code, g_people_resclass_code, decode(b.curr_type, 'ALL',bl.revenue,'PFC',bl.revenue,'PC',bl.PROJECT_REVENUE), 0 ) LABOR_REVENUE
606 , DECODE ( ra.resource_class_code, g_equip_resclass_code,
607 DECODE ( ra.rate_based_flag, 'Y',bl.quantity,0)) EQUIPMENT_HOURS -- bug 6039785
608 , DECODE ( ra.rate_based_flag || ra.resource_class_code, 'YEQUIPMENT',
609 DECODE ( ra.rate_based_flag , 'Y' , bl.quantity, 0 ) , 0 ) BILLABLE_EQUIPMENT_HOURS -- bug 6039785
610 , to_number(NULL) SUP_INV_COMMITTED_COST
611 , to_number(NULL) PO_COMMITTED_COST
612 , to_number(NULL) PR_COMMITTED_COST
613 , to_number(NULL) OTH_COMMITTED_COST
614 , DECODE ( ra.resource_class_code, g_people_resclass_code,
615 DECODE ( ra.rate_based_flag , 'Y',
616 DECODE ( ra.rate_based_flag, 'Y',bl.init_quantity,0), 0 ), 0 ) ACT_LABOR_HRS -- bug 6039785
617 , DECODE ( ra.resource_class_code, g_equip_resclass_code,
618 DECODE ( ra.rate_based_flag , 'Y',
619 DECODE ( ra.rate_based_flag, 'Y',bl.init_quantity,0), 0 ), 0 ) ACT_EQUIP_HOURS -- bug 6039785
620 , DECODE ( ra.resource_class_code, g_people_resclass_code, decode(b.curr_type, 'ALL',bl.init_burdened_cost,'PFC',bl.init_burdened_cost,'PC',bl.PROJECT_init_burdened_cost), 0 ) ACT_LABOR_BRDN_COST
621 , DECODE ( ra.resource_class_code, g_equip_resclass_code , decode(b.curr_type, 'ALL',bl.init_burdened_cost,'PFC',bl.init_burdened_cost,'PC',bl.PROJECT_init_burdened_cost), 0 ) ACT_EQUIPMENT_BRDN_COST
622 , decode(b.curr_type, 'ALL',bl.init_burdened_cost,'PFC',bl.init_burdened_cost,'PC',bl.PROJECT_init_burdened_cost) ACT_BRDN_COST
623 , decode(b.curr_type, 'ALL',bl.init_raw_cost,'PFC',bl.init_raw_cost,'PC',bl.PROJECT_init_raw_cost) ACT_RAW_COST
624 , decode(b.curr_type, 'ALL',bl.init_revenue,'PFC',bl.init_revenue,'PC',bl.PROJECT_init_revenue) ACT_REVENUE
625 , DECODE ( ra.resource_class_code, g_people_resclass_code, decode(b.curr_type, 'ALL',bl.init_raw_cost,'PFC',bl.init_raw_cost,'PC',bl.PROJECT_init_raw_cost), 0 ) ACT_LABOR_RAW_COST
626 , DECODE ( ra.resource_class_code, g_equip_resclass_code , decode(b.curr_type, 'ALL',bl.init_raw_cost,'PFC',bl.init_raw_cost,'PC',bl.PROJECT_init_raw_cost), 0 ) ACT_EQUIPMENT_RAW_COST
627 , DECODE ( ra.resource_class_code, g_people_resclass_code,
628 DECODE ( ra.rate_based_flag , 'Y',
629 DECODE ( ra.rate_based_flag, 'Y',bl.quantity - NVL(bl.init_quantity, 0),0), 0 ), 0 ) ETC_LABOR_HRS -- bug 6039785
630 , DECODE ( ra.resource_class_code, g_equip_resclass_code,
631 DECODE ( ra.rate_based_flag , 'Y',
632 DECODE ( ra.rate_based_flag, 'Y',bl.quantity - NVL(bl.init_quantity,0), 0 ), 0 ) ) ETC_EQUIP_HOURS -- bug 6039785
633 , DECODE ( ra.resource_class_code, g_people_resclass_code, decode(b.curr_type, 'ALL', bl.burdened_cost - NVL(bl.init_burdened_cost, 0),'PFC', bl.burdened_cost - NVL(bl.init_burdened_cost, 0),
634 'PC',bl.project_raw_cost - NVL(bl.project_init_raw_cost, 0), 0 )) ETC_LABOR_BRDN_COST
635 , DECODE ( ra.resource_class_code, g_equip_resclass_code, decode(b.curr_type, 'ALL', bl.burdened_cost - NVL(bl.init_burdened_cost, 0),'PFC', bl.burdened_cost - NVL(bl.init_burdened_cost, 0),
636 'PC',bl.project_raw_cost - NVL(bl.project_init_raw_cost, 0), 0 )) ETC_EQUIP_BRDN_COST
637 , decode(b.curr_type, 'ALL', bl.burdened_cost - NVL(bl.init_burdened_cost, 0),'PFC', bl.burdened_cost - NVL(bl.init_burdened_cost, 0),
638 'PC',bl.project_raw_cost - NVL(bl.project_init_raw_cost, 0), 0 ) ETC_BRDN_COST
639 , decode(b.curr_type, 'ALL', bl.raw_cost - NVL(bl.init_raw_cost, 0),'PFC', bl.raw_cost - NVL(bl.init_raw_cost, 0),'PC',bl.project_raw_cost - NVL(bl.project_init_raw_cost, 0), 0 ) ETC_RAW_COST
640 , DECODE ( ra.resource_class_code, g_people_resclass_code, decode(b.curr_type, 'ALL', bl.raw_cost - NVL(bl.init_raw_cost, 0),'PFC', bl.raw_cost - NVL(bl.init_raw_cost, 0),
641 'PC',bl.project_raw_cost - NVL(bl.project_init_raw_cost, 0), 0 ) ) ETC_LABOR_RAW_COST
642 , DECODE ( ra.resource_class_code, g_equip_resclass_code, decode(b.curr_type, 'ALL', bl.raw_cost - NVL(bl.init_raw_cost, 0),'PFC', bl.raw_cost - NVL(bl.init_raw_cost, 0),
643 'PC',bl.project_raw_cost - NVL(bl.project_init_raw_cost, 0), 0 ) ) ETC_EQUIP_RAW_COST
644 , to_number(NULL) CUSTOM1
645 , to_number(NULL) CUSTOM2
646 , to_number(NULL) CUSTOM3
647 , to_number(NULL) CUSTOM4
648 , to_number(NULL) CUSTOM5
649 , to_number(NULL) CUSTOM6
650 , to_number(NULL) CUSTOM7
651 , to_number(NULL) CUSTOM8
652 , to_number(NULL) CUSTOM9
653 , to_number(NULL) CUSTOM10
654 , to_number(NULL) CUSTOM11
655 , to_number(NULL) CUSTOM12
656 , to_number(NULL) CUSTOM13
657 , to_number(NULL) CUSTOM14
658 , to_number(NULL) CUSTOM15
659 , DECODE(DECODE(b1.version_type
660 , 'ALL', fpo.all_time_phased_code
661 , 'COST', fpo.cost_time_phased_code
662 , 'REVENUE', fpo.revenue_time_phased_code
663 ), 'P', 'OF', 'G', 'OF', 'N', 'NTR', 'X')
664 , NULL time_dangling_flag
665 , NULL rate_dangling_flag
666 -- , plr.start_date
667 -- , plr.end_date
668 , 1 prg_level
669 ,DECODE (b1.version_type, 'COST' ,'C' , 'REVENUE', 'R', 'A') plan_type_code
670 -- , nvl(p1.cbs_version_id,-1) cbs_version_id --satya
671 -- , nvl(ra.cbs_element_id,-1) cbs_element_id
672 from
673 pa_budget_versions_olap_tmp2 b ,
674 pa_budget_versions b1,
675 pa_resource_assignments ra,
676 pa_budget_lines bl,
677 pji_time_cal_period per,
678 pa_projects_all p1,
679 pa_proj_fp_options fpo,
680 PJI_ORG_EXTR_INFO inf
681 where
682 b.budget_version_id = b1.budget_version_id
683 and b.budget_version_id = ra.budget_version_id
684 and ra.resource_assignment_id = bl.resource_assignment_id
685 and p1.project_id = b.project_id
686 and b.project_id = fpo.project_id
687 and decode(DECODE(b1.version_type
688 , 'ALL', fpo.all_time_phased_code
689 , 'COST', fpo.cost_time_phased_code
690 , 'REVENUE', fpo.revenue_time_phased_code
691 ), 'P'
692 , inf.pa_calendar_id
693 , 'G'
694 , inf.gl_calendar_id, -b1.project_id
695 ) = per.calendar_id
696 AND nvl(bl.period_name,'XXX') = per.name
697 AND bl.budget_version_id = fpo.fin_plan_version_id
698 --AND fpo.time_phased_type_code IN ('P', 'G')
699 AND fpo.project_id = p1.project_id
700 AND p1.org_id = inf.org_id
701 AND b1.fin_plan_type_id = fpo.fin_plan_type_id
702 and fpo.fin_plan_type_id <> 10
703 AND b.budget_version_id = fpo.fin_plan_version_id
704 AND fpo.fin_plan_option_level_code = 'PLAN_VERSION' -- Other values are: plan type and project.
705 AND b1.version_type IN ( 'ALL' , 'COST' , 'REVENUE')
706 AND bl.budget_version_id = b.budget_version_id;
707 PJI_FM_PLAN_MAINT_PVT.PRORATE_TO_ALL_CALENDARS;
708
709 insert into PA_OLAP_STAGE(
710 OLAP_GROUP ,
711 PROJECT_ID ,
712 PROJECT_ELEMENT_ID ,
713 TIME_ID ,
714 CALENDAR_TYPE ,
715 CURRENCY_CODE ,
716 RBS_ELEMENT_ID ,
717 RBS_VERSION_ID ,
718 PLAN_VERSION_ID ,
719 -- CBS_VERSION_ID,
720 -- CBS_ELEMENT_ID,
721 RAW_COST ,
722 BRDN_COST ,
723 REVENUE ,
724 BILL_RAW_COST ,
725 BILL_BRDN_COST ,
726 BILL_LABOR_RAW_COST ,
727 BILL_LABOR_BRDN_COST ,
728 BILL_LABOR_HRS ,
729 EQUIPMENT_RAW_COST ,
730 EQUIPMENT_BRDN_COST ,
731 CAPITALIZABLE_RAW_COST ,
732 CAPITALIZABLE_BRDN_COST ,
733 LABOR_RAW_COST ,
734 LABOR_BRDN_COST ,
735 LABOR_HRS ,
736 LABOR_REVENUE ,
737 EQUIPMENT_HOURS ,
738 BILLABLE_EQUIPMENT_HOURS ,
739 SUP_INV_COMMITTED_COST ,
740 PO_COMMITTED_COST ,
741 PR_COMMITTED_COST ,
742 OTH_COMMITTED_COST ,
743 CUSTOM1 ,
744 CUSTOM2 ,
745 CUSTOM3 ,
746 CUSTOM4 ,
747 CUSTOM5 ,
748 CUSTOM6 ,
749 CUSTOM7 ,
750 CUSTOM8 ,
751 CUSTOM9 ,
752 CUSTOM10 ,
753 CUSTOM11 ,
754 CUSTOM12 ,
755 CUSTOM13 ,
756 CUSTOM14 ,
757 CUSTOM15 ,
758 ACT_LABOR_HRS ,
759 ACT_EQUIP_HRS ,
760 ACT_LABOR_BRDN_COST ,
761 ACT_EQUIP_BRDN_COST ,
762 ACT_BRDN_COST ,
763 ACT_RAW_COST ,
764 ACT_REVENUE ,
765 ETC_LABOR_HRS ,
766 ETC_EQUIP_HRS ,
767 ETC_LABOR_BRDN_COST ,
768 ETC_EQUIP_BRDN_COST ,
769 ETC_BRDN_COST ,
770 ETC_RAW_COST ,
771 ACT_LABOR_RAW_COST ,
772 ACT_EQUIP_RAW_COST ,
773 ETC_LABOR_RAW_COST ,
774 ETC_EQUIP_RAW_COST ,
775 LAST_UPDATE_DATE ,
776 LAST_UPDATED_BY ,
777 CREATION_DATE ,
778 CREATED_BY ,
779 LAST_UPDATE_LOGIN
780
781 )
782 select
783 OLAP_GROUP ,
784 bv.PROJECT_ID ,
785 PROJECT_ELEMENT_ID ,
786 TIME_ID ,
787 CALENDAR_TYPE ,
788 CURRENCY_CODE ,
789 nvl(RBS_ELEMENT_ID,-1) , --satya2
790 nvl(RBS_VERSION_ID,-1) , --satya2
791 PLAN_VERSION_ID ,
792 -- nvl(p.CBS_VERSION_ID,-1), --satya2
793 -- nvl(bv.CBS_ELEMENT_ID, -1), --satya2
794 RAW_COST ,
795 BRDN_COST ,
796 REVENUE ,
797 BILL_RAW_COST ,
798 BILL_BRDN_COST ,
799 BILL_LABOR_RAW_COST ,
800 BILL_LABOR_BRDN_COST ,
801 BILL_LABOR_HRS ,
802 EQUIPMENT_RAW_COST ,
803 EQUIPMENT_BRDN_COST ,
804 CAPITALIZABLE_RAW_COST ,
805 CAPITALIZABLE_BRDN_COST ,
806 LABOR_RAW_COST ,
807 LABOR_BRDN_COST ,
808 LABOR_HRS ,
809 LABOR_REVENUE ,
810 EQUIPMENT_HOURS ,
811 BILLABLE_EQUIPMENT_HOURS ,
812 SUP_INV_COMMITTED_COST ,
813 PO_COMMITTED_COST ,
814 PR_COMMITTED_COST ,
815 OTH_COMMITTED_COST ,
816 CUSTOM1 ,
817 CUSTOM2 ,
818 CUSTOM3 ,
819 CUSTOM4 ,
820 CUSTOM5 ,
821 CUSTOM6 ,
822 CUSTOM7 ,
823 CUSTOM8 ,
824 CUSTOM9 ,
825 CUSTOM10 ,
826 CUSTOM11 ,
827 CUSTOM12 ,
828 CUSTOM13 ,
829 CUSTOM14 ,
830 CUSTOM15 ,
831 ACT_LABOR_HRS ,
832 ACT_EQUIP_HRS ,
833 ACT_LABOR_BRDN_COST ,
834 ACT_EQUIP_BRDN_COST ,
835 ACT_BRDN_COST ,
836 ACT_RAW_COST ,
837 ACT_REVENUE ,
838 ETC_LABOR_HRS ,
839 ETC_EQUIP_HRS ,
840 ETC_LABOR_BRDN_COST ,
841 ETC_EQUIP_BRDN_COST ,
842 ETC_BRDN_COST ,
843 ETC_RAW_COST ,
844 ACT_LABOR_RAW_COST ,
845 ACT_EQUIP_RAW_COST ,
846 ETC_LABOR_RAW_COST ,
847 ETC_EQUIP_RAW_COST ,
848 sysdate ,
849 -1 ,
850 sysdate ,
851 -1 ,
852 -1
853 from pji_fp_aggr_pjp1 bv, pa_projects_all p
854 where bv.project_id = p.project_id;
855
856 delete from pji_time_cal_period where cal_period_id = -1;
857
858 END maintain_budgets;
859
860 Procedure maintain_actuals_prj (
861 p_group_size IN NUMBER) as
862
863 BEGIN
864
865 INSERT INTO pa_olap_stage(
866 OLAP_GROUP ,
867 PROJECT_ID ,
868 PROJECT_ELEMENT_ID ,
869 TIME_ID ,
870 CALENDAR_TYPE ,
871 CURRENCY_CODE ,
872 RBS_ELEMENT_ID ,
873 RBS_VERSION_ID ,
874 PLAN_VERSION_ID ,
875 RAW_COST ,
876 BRDN_COST ,
877 REVENUE ,
878 BILL_RAW_COST ,
879 BILL_BRDN_COST ,
880 BILL_LABOR_RAW_COST ,
881 BILL_LABOR_BRDN_COST ,
882 BILL_LABOR_HRS ,
883 EQUIPMENT_RAW_COST ,
884 EQUIPMENT_BRDN_COST ,
885 CAPITALIZABLE_RAW_COST ,
886 CAPITALIZABLE_BRDN_COST ,
887 LABOR_RAW_COST ,
888 LABOR_BRDN_COST ,
889 LABOR_HRS ,
890 LABOR_REVENUE ,
891 EQUIPMENT_HOURS ,
892 BILLABLE_EQUIPMENT_HOURS ,
893 SUP_INV_COMMITTED_COST ,
894 PO_COMMITTED_COST ,
895 PR_COMMITTED_COST ,
896 OTH_COMMITTED_COST ,
897 CUSTOM1 ,
898 CUSTOM2 ,
899 CUSTOM3 ,
900 CUSTOM4 ,
901 CUSTOM5 ,
902 CUSTOM6 ,
903 CUSTOM7 ,
904 CUSTOM8 ,
905 CUSTOM9 ,
906 CUSTOM10 ,
907 CUSTOM11 ,
908 CUSTOM12 ,
909 CUSTOM13 ,
910 CUSTOM14 ,
911 CUSTOM15 ,
912 CBS_VERSION_ID ,
913 CBS_ELEMENT_ID ,
914 LAST_UPDATE_DATE ,
915 LAST_UPDATED_BY ,
916 CREATION_DATE ,
917 CREATED_BY ,
918 LAST_UPDATE_LOGIN
919 )
920 select
921 p.olap_group,
922 tmp1.PROJECT_ID,
923 tmp1.TASK_ID,
924 tmp1.RECVR_PERIOD_ID,
925 substrb(tmp1.RECVR_PERIOD_TYPE, 1,1),
926 p.project_currency_code,
927 -1, --satya2
928 -1,
929 -1,
930 tmp1.PRJ_RAW_COST,
931 tmp1.PRJ_BRDN_COST,
932 tmp1.PRJ_REVENUE,
933 decode(tmp1.PROJECT_TYPE_CLASS, 'B', tmp1.PRJ_BILL_RAW_COST, to_number(null)) BILL_RAW_COST,
934 decode(tmp1.PROJECT_TYPE_CLASS, 'B', tmp1.PRJ_BILL_BRDN_COST, to_number(null)) BILL_BRDN_COST,
935 decode(tmp1.PROJECT_TYPE_CLASS || '_' || tmp1.RESOURCE_CLASS_ID, 'B_1', tmp1.PRJ_BILL_RAW_COST, to_number(null)) BILL_LABOR_RAW_COST,
936 decode(tmp1.PROJECT_TYPE_CLASS || '_' || tmp1.RESOURCE_CLASS_ID, 'B_1', tmp1.PRJ_BILL_BRDN_COST, to_number(null)) BILL_LABOR_BRDN_COST,
937 decode(tmp1.PROJECT_TYPE_CLASS || '_' || tmp1.resource_class_id, 'B_1', tmp1.BILL_QUANTITY, to_number(null)) BILL_LABOR_HRS,
938 decode(tmp1.resource_class_id, '2', tmp1.PRJ_RAW_COST, to_number(null)) EQUIPMENT_RAW_COST,
939 decode(tmp1.resource_class_id, '2', tmp1.PRJ_BRDN_COST, to_number(null)) EQUIPMENT_BRDN_COST,
940 decode(tmp1.PROJECT_TYPE_CLASS, 'C', tmp1.PRJ_BILL_RAW_COST, to_number(null)) CAPITALIZABLE_RAW_COST,
941 decode(tmp1.PROJECT_TYPE_CLASS, 'C', tmp1.PRJ_BILL_BRDN_COST, to_number(null))CAPITALIZABLE_BRDN_COST,
942 decode(tmp1.resource_class_id, '1', tmp1.PRJ_RAW_COST, to_number(null)) LABOR_RAW_COST,
943 decode(tmp1.resource_class_id, '1', tmp1.PRJ_BRDN_COST, to_number(null)) LABOR_BRDN_COST,
944 decode(tmp1.resource_class_id, '1', tmp1.QUANTITY, to_number(null)) LABOR_HRS,
945 decode(tmp1.resource_class_id, '1', tmp1.PRJ_REVENUE, to_number(null)) LABOR_REVENUE,
946 decode(tmp1.resource_class_id, '2', tmp1.QUANTITY, to_number(null)) EQUIPMENT_HOURS,
947 decode(tmp1.resource_class_id, '2', tmp1.BILL_QUANTITY, to_number(null)) BILLABLE_EQUIPMENT_HOURS,
948 tmp1.PRJ_SUP_INV_COMMITTED_COST,
949 tmp1.PRJ_PO_COMMITTED_COST,
950 tmp1.PRJ_PR_COMMITTED_COST,
951 tmp1.PRJ_OTH_COMMITTED_COST,
952 0,
953 0,
954 0,
955 0,
956 0,
957 0,
958 0,
959 0,
960 0,
961 0,
962 0,
963 0,
964 0,
965 0,
966 0,
967 nvl(tmp1.CBS_VERSION_ID,-1), --satya2
968 nvl(tmp1.CBS_ELEMENT_ID,-1), --satya2
969 sysdate ,
970 -1 ,
971 sysdate ,
972 -1 ,
973 -1
974 from
975 (
976 select -- initial actuals data
977 bal.TXN_ACCUM_HEADER_ID,
978 bal.RESOURCE_CLASS_ID,
979 bal.PROJECT_ID,
980 bal.PROJECT_TYPE_CLASS,
981 nvl(bal.TASK_ID, -1) TASK_ID,
982 bal.RECVR_PERIOD_TYPE,
983 bal.RECVR_PERIOD_ID,
984 -1 RBS_VERSION_ID,
985 bal.TXN_CURRENCY_CODE,
986 bal.TXN_RAW_COST,
987 bal.TXN_BILL_RAW_COST,
988 bal.TXN_BRDN_COST,
989 bal.TXN_BILL_BRDN_COST,
990 bal.TXN_REVENUE,
991 to_number(null) TXN_SUP_INV_COMMITTED_COST,
992 to_number(null) TXN_PO_COMMITTED_COST,
993 to_number(null) TXN_PR_COMMITTED_COST,
994 to_number(null) TXN_OTH_COMMITTED_COST,
995 bal.PRJ_RAW_COST,
996 bal.PRJ_BILL_RAW_COST,
997 bal.PRJ_BRDN_COST,
998 bal.PRJ_BILL_BRDN_COST,
999 bal.PRJ_REVENUE,
1000 to_number(null) PRJ_SUP_INV_COMMITTED_COST,
1001 to_number(null) PRJ_PO_COMMITTED_COST,
1002 to_number(null) PRJ_PR_COMMITTED_COST,
1003 to_number(null) PRJ_OTH_COMMITTED_COST,
1004 bal.POU_RAW_COST,
1005 bal.POU_BILL_RAW_COST,
1006 bal.POU_BRDN_COST,
1007 bal.POU_BILL_BRDN_COST,
1008 bal.POU_REVENUE,
1009 to_number(null) POU_SUP_INV_COMMITTED_COST,
1010 to_number(null) POU_PO_COMMITTED_COST,
1011 to_number(null) POU_PR_COMMITTED_COST,
1012 to_number(null) POU_OTH_COMMITTED_COST,
1013 bal.EOU_RAW_COST,
1014 bal.EOU_BILL_RAW_COST,
1015 bal.EOU_BRDN_COST,
1016 bal.EOU_BILL_BRDN_COST,
1017 to_number(null) EOU_SUP_INV_COMMITTED_COST,
1018 to_number(null) EOU_PO_COMMITTED_COST,
1019 to_number(null) EOU_PR_COMMITTED_COST,
1020 to_number(null) EOU_OTH_COMMITTED_COST,
1021 bal.G1_RAW_COST,
1022 bal.G1_BILL_RAW_COST,
1023 bal.G1_BRDN_COST,
1024 bal.G1_BILL_BRDN_COST,
1025 bal.G1_REVENUE,
1026 to_number(null) G1_SUP_INV_COMMITTED_COST,
1027 to_number(null) G1_PO_COMMITTED_COST,
1028 to_number(null) G1_PR_COMMITTED_COST,
1029 to_number(null) G1_OTH_COMMITTED_COST,
1030 bal.G2_RAW_COST,
1031 bal.G2_BILL_RAW_COST,
1032 bal.G2_BRDN_COST,
1033 bal.G2_BILL_BRDN_COST,
1034 bal.G2_REVENUE,
1035 to_number(null) G2_SUP_INV_COMMITTED_COST,
1036 to_number(null) G2_PO_COMMITTED_COST,
1037 to_number(null) G2_PR_COMMITTED_COST,
1038 to_number(null) G2_OTH_COMMITTED_COST,
1039 bal.QUANTITY,
1040 bal.BILL_QUANTITY,
1041 bal.cbs_version_id,
1042 bal.cbs_element_id
1043 from
1044 PA_STAGE1_BATCH_OLAP map,
1045 PJI_FP_TXN_ACCUM bal,
1046 pa_projects_all pa,
1047 pa_pjt_proj_batch batch
1048 where
1049 g_extraction_type in ('FULL', 'PARTIAL') and
1050 -- decode(l_extraction_type,'FULL','Y','PARTIAL',decode(bitand(l_refresh_code,1),1,'Y','N'),'N') ='Y' and -- Bug#5099574
1051 -- l_plan_type_id IS NULL and -- Bug#5099574
1052 bal.PROJECT_ID = map.PROJECT_ID and
1053 bal.project_id = pa.project_id and
1054 pa.project_id = batch.project_id and
1055 pa.pjt_rollup_enabled_flag = 'Y'
1056 union all -- initial commitments data
1057 select
1058 bal.TXN_ACCUM_HEADER_ID,
1059 to_number(null) RESOURCE_CLASS_ID,
1060 bal.PROJECT_ID,
1061 to_char(null) PROJECT_TYPE_CLASS,
1062 nvl(bal.TASK_ID, -1) TASK_ID,
1063 bal.RECVR_PERIOD_TYPE,
1064 bal.RECVR_PERIOD_ID,
1065 -1 RBS_VERSION_ID,
1066 bal.TXN_CURRENCY_CODE,
1067 to_number(null) TXN_RAW_COST,
1068 to_number(null) TXN_BILL_RAW_COST,
1069 to_number(null) TXN_BRDN_COST,
1070 to_number(null) TXN_BILL_BRDN_COST,
1071 to_number(null) TXN_REVENUE,
1072 bal.TXN_SUP_INV_COMMITTED_COST,
1073 bal.TXN_PO_COMMITTED_COST,
1074 bal.TXN_PR_COMMITTED_COST,
1075 bal.TXN_OTH_COMMITTED_COST,
1076 to_number(null) PRJ_RAW_COST,
1077 to_number(null) PRJ_BILL_RAW_COST,
1078 to_number(null) PRJ_BRDN_COST,
1079 to_number(null) PRJ_BILL_BRDN_COST,
1080 to_number(null) PRJ_REVENUE,
1081 bal.PRJ_SUP_INV_COMMITTED_COST,
1082 bal.PRJ_PO_COMMITTED_COST,
1083 bal.PRJ_PR_COMMITTED_COST,
1084 bal.PRJ_OTH_COMMITTED_COST,
1085 to_number(null) POU_RAW_COST,
1086 to_number(null) POU_BILL_RAW_COST,
1087 to_number(null) POU_BRDN_COST,
1088 to_number(null) POU_BILL_BRDN_COST,
1089 to_number(null) POU_REVENUE,
1090 bal.POU_SUP_INV_COMMITTED_COST,
1091 bal.POU_PO_COMMITTED_COST,
1092 bal.POU_PR_COMMITTED_COST,
1093 bal.POU_OTH_COMMITTED_COST,
1094 to_number(null) EOU_RAW_COST,
1095 to_number(null) EOU_BILL_RAW_COST,
1096 to_number(null) EOU_BRDN_COST,
1097 to_number(null) EOU_BILL_BRDN_COST,
1098 bal.EOU_SUP_INV_COMMITTED_COST,
1099 bal.EOU_PO_COMMITTED_COST,
1100 bal.EOU_PR_COMMITTED_COST,
1101 bal.EOU_OTH_COMMITTED_COST,
1102 to_number(null) G1_RAW_COST,
1103 to_number(null) G1_BILL_RAW_COST,
1104 to_number(null) G1_BRDN_COST,
1105 to_number(null) G1_BILL_BRDN_COST,
1106 to_number(null) G1_REVENUE,
1107 bal.G1_SUP_INV_COMMITTED_COST,
1108 bal.G1_PO_COMMITTED_COST,
1109 bal.G1_PR_COMMITTED_COST,
1110 bal.G1_OTH_COMMITTED_COST,
1111 to_number(null) G2_RAW_COST,
1112 to_number(null) G2_BILL_RAW_COST,
1113 to_number(null) G2_BRDN_COST,
1114 to_number(null) G2_BILL_BRDN_COST,
1115 to_number(null) G2_REVENUE,
1116 bal.G2_SUP_INV_COMMITTED_COST,
1117 bal.G2_PO_COMMITTED_COST,
1118 bal.G2_PR_COMMITTED_COST,
1119 bal.G2_OTH_COMMITTED_COST,
1120 to_number(null) QUANTITY,
1121 to_number(null) BILL_QUANTITY,
1122 bal.cbs_version_id,
1123 bal.cbs_element_id
1124 from
1125 PA_STAGE1_BATCH_OLAP map,
1126 PJI_FP_TXN_ACCUM1 bal,
1127 pa_projects_all pa,
1128 pa_pjt_proj_batch batch
1129 where
1130 g_extraction_type in ('FULL', 'PARTIAL') and
1131 -- decode(l_extraction_type,'FULL','Y','PARTIAL',decode(bitand(l_refresh_code,1),1,'Y','N'),'N') ='Y' and -- Bug#5099574
1132 -- l_plan_type_id IS NULL and -- Bug#5099574
1133 bal.PROJECT_ID = map.PROJECT_ID and
1134 bal.project_id = pa.project_id and
1135 pa.project_id = batch.project_id and
1136 pa.pjt_rollup_enabled_flag = 'Y'
1137 union all -- incremental data
1138 select
1139 tmp7.TXN_ACCUM_HEADER_ID,
1140 tmp7.RESOURCE_CLASS_ID,
1141 tmp7.PROJECT_ID,
1142 tmp7.PROJECT_TYPE_CLASS,
1143 nvl(tmp7.TASK_ID, -1) TASK_ID,
1144 tmp7.RECVR_PERIOD_TYPE,
1145 tmp7.RECVR_PERIOD_ID,
1146 -1 RBS_VERSION_ID,
1147 tmp7.TXN_CURRENCY_CODE,
1148 tmp7.TXN_RAW_COST,
1149 tmp7.TXN_BILL_RAW_COST,
1150 tmp7.TXN_BRDN_COST,
1151 tmp7.TXN_BILL_BRDN_COST,
1152 tmp7.TXN_REVENUE,
1153 tmp7.TXN_SUP_INV_COMMITTED_COST,
1154 tmp7.TXN_PO_COMMITTED_COST,
1155 tmp7.TXN_PR_COMMITTED_COST,
1156 tmp7.TXN_OTH_COMMITTED_COST,
1157 tmp7.PRJ_RAW_COST,
1158 tmp7.PRJ_BILL_RAW_COST,
1159 tmp7.PRJ_BRDN_COST,
1160 tmp7.PRJ_BILL_BRDN_COST,
1161 tmp7.PRJ_REVENUE,
1162 decode(nvl(proj_batch.project_id, -1), -1, tmp7.PRJ_SUP_INV_COMMITTED_COST,0),
1163 decode(nvl(proj_batch.project_id, -1), -1, tmp7.PRJ_PO_COMMITTED_COST,0),
1164 decode(nvl(proj_batch.project_id, -1), -1, tmp7.PRJ_PR_COMMITTED_COST,0),
1165 decode(nvl(proj_batch.project_id, -1), -1, tmp7.PRJ_OTH_COMMITTED_COST,0),
1166 tmp7.POU_RAW_COST,
1167 tmp7.POU_BILL_RAW_COST,
1168 tmp7.POU_BRDN_COST,
1169 tmp7.POU_BILL_BRDN_COST,
1170 tmp7.POU_REVENUE,
1171 decode(nvl(proj_batch.project_id, -1), -1, tmp7.POU_SUP_INV_COMMITTED_COST,0),
1172 decode(nvl(proj_batch.project_id, -1), -1, tmp7.POU_PO_COMMITTED_COST,0),
1173 decode(nvl(proj_batch.project_id, -1), -1, tmp7.POU_PR_COMMITTED_COST,0),
1174 decode(nvl(proj_batch.project_id, -1), -1, tmp7.POU_OTH_COMMITTED_COST,0),
1175 tmp7.EOU_RAW_COST,
1176 tmp7.EOU_BILL_RAW_COST,
1177 tmp7.EOU_BRDN_COST,
1178 tmp7.EOU_BILL_BRDN_COST,
1179 decode(nvl(proj_batch.project_id, -1), -1, tmp7.EOU_SUP_INV_COMMITTED_COST,0),
1180 decode(nvl(proj_batch.project_id, -1), -1, tmp7.EOU_PO_COMMITTED_COST,0),
1181 decode(nvl(proj_batch.project_id, -1), -1, tmp7.EOU_PR_COMMITTED_COST,0),
1182 decode(nvl(proj_batch.project_id, -1), -1, tmp7.EOU_OTH_COMMITTED_COST,0),
1183 tmp7.G1_RAW_COST,
1184 tmp7.G1_BILL_RAW_COST,
1185 tmp7.G1_BRDN_COST,
1186 tmp7.G1_BILL_BRDN_COST,
1187 tmp7.G1_REVENUE,
1188 decode(nvl(proj_batch.project_id, -1), -1, tmp7.G1_SUP_INV_COMMITTED_COST,0),
1189 decode(nvl(proj_batch.project_id, -1), -1, tmp7.G1_PO_COMMITTED_COST,0),
1190 decode(nvl(proj_batch.project_id, -1), -1, tmp7.G1_PR_COMMITTED_COST,0),
1191 decode(nvl(proj_batch.project_id, -1), -1, tmp7.G1_OTH_COMMITTED_COST,0),
1192 tmp7.G2_RAW_COST,
1193 tmp7.G2_BILL_RAW_COST,
1194 tmp7.G2_BRDN_COST,
1195 tmp7.G2_BILL_BRDN_COST,
1196 tmp7.G2_REVENUE,
1197 decode(nvl(proj_batch.project_id, -1), -1, tmp7.G2_SUP_INV_COMMITTED_COST,0),
1198 decode(nvl(proj_batch.project_id, -1), -1, tmp7.G2_PO_COMMITTED_COST,0),
1199 decode(nvl(proj_batch.project_id, -1), -1, tmp7.G2_PR_COMMITTED_COST,0),
1200 decode(nvl(proj_batch.project_id, -1), -1, tmp7.G2_OTH_COMMITTED_COST,0),
1201 tmp7.QUANTITY,
1202 tmp7.BILL_QUANTITY,
1203 tmp7.cbs_version_id,
1204 tmp7.cbs_element_id
1205 from
1206 PA_FIN7_OLAP tmp7, PA_STAGE1_BATCH_OLAP proj_batch,
1207 pa_projects_all pa, pa_pjt_proj_batch batch
1208 where
1209 g_extraction_type = 'INCREMENTAL'
1210 and tmp7.project_id = proj_batch.project_id (+) and
1211 tmp7.project_id = pa.project_id and
1212 pa.project_id = batch.project_id and
1213 pa.pjt_rollup_enabled_flag = 'Y'
1214 union all
1215 select
1216 bal.TXN_ACCUM_HEADER_ID,
1217 bal.RESOURCE_CLASS_ID,
1218 bal.PROJECT_ID,
1219 bal.PROJECT_TYPE_CLASS,
1220 nvl(bal.TASK_ID, -1) TASK_ID,
1221 bal.RECVR_PERIOD_TYPE,
1222 bal.RECVR_PERIOD_ID,
1223 -1 RBS_VERSION_ID,
1224 bal.TXN_CURRENCY_CODE,
1225 bal.TXN_RAW_COST,
1226 bal.TXN_BILL_RAW_COST,
1227 bal.TXN_BRDN_COST,
1228 bal.TXN_BILL_BRDN_COST,
1229 bal.TXN_REVENUE,
1230 to_number(null) TXN_SUP_INV_COMMITTED_COST,
1231 to_number(null) TXN_PO_COMMITTED_COST,
1232 to_number(null) TXN_PR_COMMITTED_COST,
1233 to_number(null) TXN_OTH_COMMITTED_COST,
1234 bal.PRJ_RAW_COST,
1235 bal.PRJ_BILL_RAW_COST,
1236 bal.PRJ_BRDN_COST,
1237 bal.PRJ_BILL_BRDN_COST,
1238 bal.PRJ_REVENUE,
1239 to_number(null) PRJ_SUP_INV_COMMITTED_COST,
1240 to_number(null) PRJ_PO_COMMITTED_COST,
1241 to_number(null) PRJ_PR_COMMITTED_COST,
1242 to_number(null) PRJ_OTH_COMMITTED_COST,
1243 bal.POU_RAW_COST,
1244 bal.POU_BILL_RAW_COST,
1245 bal.POU_BRDN_COST,
1246 bal.POU_BILL_BRDN_COST,
1247 bal.POU_REVENUE,
1248 to_number(null) POU_SUP_INV_COMMITTED_COST,
1249 to_number(null) POU_PO_COMMITTED_COST,
1250 to_number(null) POU_PR_COMMITTED_COST,
1251 to_number(null) POU_OTH_COMMITTED_COST,
1252 bal.EOU_RAW_COST,
1253 bal.EOU_BILL_RAW_COST,
1254 bal.EOU_BRDN_COST,
1255 bal.EOU_BILL_BRDN_COST,
1256 to_number(null) EOU_SUP_INV_COMMITTED_COST,
1257 to_number(null) EOU_PO_COMMITTED_COST,
1258 to_number(null) EOU_PR_COMMITTED_COST,
1259 to_number(null) EOU_OTH_COMMITTED_COST,
1260 bal.G1_RAW_COST,
1261 bal.G1_BILL_RAW_COST,
1262 bal.G1_BRDN_COST,
1263 bal.G1_BILL_BRDN_COST,
1264 bal.G1_REVENUE,
1265 to_number(null) G1_SUP_INV_COMMITTED_COST,
1266 to_number(null) G1_PO_COMMITTED_COST,
1267 to_number(null) G1_PR_COMMITTED_COST,
1268 to_number(null) G1_OTH_COMMITTED_COST,
1269 bal.G2_RAW_COST,
1270 bal.G2_BILL_RAW_COST,
1271 bal.G2_BRDN_COST,
1272 bal.G2_BILL_BRDN_COST,
1273 bal.G2_REVENUE,
1274 to_number(null) G2_SUP_INV_COMMITTED_COST,
1275 to_number(null) G2_PO_COMMITTED_COST,
1276 to_number(null) G2_PR_COMMITTED_COST,
1277 to_number(null) G2_OTH_COMMITTED_COST,
1278 bal.QUANTITY,
1279 bal.BILL_QUANTITY,
1280 bal.cbs_version_id,
1281 bal.cbs_element_id
1282 from PJI_FP_TXN_ACCUM bal,
1283 PA_FIN7_OLAP fin7,
1284 pa_projects_all pa,
1285 pa_pjt_proj_batch batch
1286 where
1287 g_extraction_type = 'INCREMENTAL' and
1288 bal.project_id = fin7.project_id and
1289 bal.RECVR_PERIOD_TYPE = fin7.RECVR_PERIOD_TYPE and
1290 bal.RECVR_PERIOD_ID = fin7.RECVR_PERIOD_ID and
1291 bal.project_id = pa.project_id and
1292 pa.project_id = batch.project_id and
1293 pa.pjt_rollup_enabled_flag = 'Y'
1294 ) tmp1, pa_projects_all p
1295 where tmp1.project_id = p.project_id;
1296
1297 INSERT INTO pa_olap_stage(
1298 OLAP_GROUP ,
1299 PROJECT_ID ,
1300 PROJECT_ELEMENT_ID ,
1301 TIME_ID ,
1302 CALENDAR_TYPE ,
1303 CURRENCY_CODE ,
1304 RBS_ELEMENT_ID ,
1305 RBS_VERSION_ID ,
1306 PLAN_VERSION_ID ,
1307 RAW_COST ,
1308 BRDN_COST ,
1309 REVENUE ,
1310 BILL_RAW_COST ,
1311 BILL_BRDN_COST ,
1312 BILL_LABOR_RAW_COST ,
1313 BILL_LABOR_BRDN_COST ,
1314 BILL_LABOR_HRS ,
1315 EQUIPMENT_RAW_COST ,
1316 EQUIPMENT_BRDN_COST ,
1317 CAPITALIZABLE_RAW_COST ,
1318 CAPITALIZABLE_BRDN_COST ,
1319 LABOR_RAW_COST ,
1320 LABOR_BRDN_COST ,
1321 LABOR_HRS ,
1322 LABOR_REVENUE ,
1323 EQUIPMENT_HOURS ,
1324 BILLABLE_EQUIPMENT_HOURS ,
1325 SUP_INV_COMMITTED_COST ,
1326 PO_COMMITTED_COST ,
1327 PR_COMMITTED_COST ,
1328 OTH_COMMITTED_COST ,
1329 CUSTOM1 ,
1330 CUSTOM2 ,
1331 CUSTOM3 ,
1332 CUSTOM4 ,
1333 CUSTOM5 ,
1334 CUSTOM6 ,
1335 CUSTOM7 ,
1336 CUSTOM8 ,
1337 CUSTOM9 ,
1338 CUSTOM10 ,
1339 CUSTOM11 ,
1340 CUSTOM12 ,
1341 CUSTOM13 ,
1342 CUSTOM14 ,
1343 CUSTOM15 ,
1344 CBS_VERSION_ID ,
1345 CBS_ELEMENT_ID ,
1346 LAST_UPDATE_DATE ,
1347 LAST_UPDATED_BY ,
1348 CREATION_DATE ,
1349 CREATED_BY ,
1350 LAST_UPDATE_LOGIN
1351 )
1352 select
1353 p.olap_group,
1354 tmp1.PROJECT_ID,
1355 tmp1.TASK_ID,
1356 tmp1.RECVR_PERIOD_ID,
1357 substrb(tmp1.RECVR_PERIOD_TYPE, 1,1),
1358 p.project_currency_code,
1359 nvl(rbs.ELEMENT_ID,-1), --satya2
1360 tmp1.RBS_VERSION_ID,
1361 -1,
1362 tmp1.PRJ_RAW_COST,
1363 tmp1.PRJ_BRDN_COST,
1364 tmp1.PRJ_REVENUE,
1365 decode(tmp1.PROJECT_TYPE_CLASS, 'B', tmp1.PRJ_BILL_RAW_COST, to_number(null)) BILL_RAW_COST,
1366 decode(tmp1.PROJECT_TYPE_CLASS, 'B', tmp1.PRJ_BILL_BRDN_COST, to_number(null)) BILL_BRDN_COST,
1367 decode(tmp1.PROJECT_TYPE_CLASS || '_' || tmp1.RESOURCE_CLASS_ID, 'B_1', tmp1.PRJ_BILL_RAW_COST, to_number(null)) BILL_LABOR_RAW_COST,
1368 decode(tmp1.PROJECT_TYPE_CLASS || '_' || tmp1.RESOURCE_CLASS_ID, 'B_1', tmp1.PRJ_BILL_BRDN_COST, to_number(null)) BILL_LABOR_BRDN_COST,
1369 decode(tmp1.PROJECT_TYPE_CLASS || '_' || tmp1.resource_class_id, 'B_1', tmp1.BILL_QUANTITY, to_number(null)) BILL_LABOR_HRS,
1370 decode(tmp1.resource_class_id, '2', tmp1.PRJ_RAW_COST, to_number(null)) EQUIPMENT_RAW_COST,
1371 decode(tmp1.resource_class_id, '2', tmp1.PRJ_BRDN_COST, to_number(null)) EQUIPMENT_BRDN_COST,
1372 decode(tmp1.PROJECT_TYPE_CLASS, 'C', tmp1.PRJ_BILL_RAW_COST, to_number(null)) CAPITALIZABLE_RAW_COST,
1373 decode(tmp1.PROJECT_TYPE_CLASS, 'C', tmp1.PRJ_BILL_BRDN_COST, to_number(null))CAPITALIZABLE_BRDN_COST,
1374 decode(tmp1.resource_class_id, '1', tmp1.PRJ_RAW_COST, to_number(null)) LABOR_RAW_COST,
1375 decode(tmp1.resource_class_id, '1', tmp1.PRJ_BRDN_COST, to_number(null)) LABOR_BRDN_COST,
1376 decode(tmp1.resource_class_id, '1', tmp1.QUANTITY, to_number(null)) LABOR_HRS,
1377 decode(tmp1.resource_class_id, '1', tmp1.PRJ_REVENUE, to_number(null)) LABOR_REVENUE,
1378 decode(tmp1.resource_class_id, '2', tmp1.QUANTITY, to_number(null)) EQUIPMENT_HOURS,
1379 decode(tmp1.resource_class_id, '2', tmp1.BILL_QUANTITY, to_number(null)) BILLABLE_EQUIPMENT_HOURS,
1380 tmp1.PRJ_SUP_INV_COMMITTED_COST,
1381 tmp1.PRJ_PO_COMMITTED_COST,
1382 tmp1.PRJ_PR_COMMITTED_COST,
1383 tmp1.PRJ_OTH_COMMITTED_COST,
1384 0,
1385 0,
1386 0,
1387 0,
1388 0,
1389 0,
1390 0,
1391 0,
1392 0,
1393 0,
1394 0,
1395 0,
1396 0,
1397 0,
1398 0,
1399 nvl(tmp1.CBS_VERSION_ID,-1), --satya2
1400 nvl(tmp1.CBS_ELEMENT_ID,-1), --satya2
1401 sysdate ,
1402 -1 ,
1403 sysdate ,
1404 -1 ,
1405 -1
1406 from
1407 (
1408 select -- initial actuals data
1409 bal.TXN_ACCUM_HEADER_ID,
1410 bal.RESOURCE_CLASS_ID,
1411 bal.PROJECT_ID,
1412 bal.PROJECT_TYPE_CLASS,
1413 nvl(bal.TASK_ID, -1) TASK_ID,
1414 bal.RECVR_PERIOD_TYPE,
1415 bal.RECVR_PERIOD_ID,
1416 asg.RBS_VERSION_ID RBS_VERSION_ID,
1417 bal.TXN_CURRENCY_CODE,
1418 bal.TXN_RAW_COST,
1419 bal.TXN_BILL_RAW_COST,
1420 bal.TXN_BRDN_COST,
1421 bal.TXN_BILL_BRDN_COST,
1422 bal.TXN_REVENUE,
1423 to_number(null) TXN_SUP_INV_COMMITTED_COST,
1424 to_number(null) TXN_PO_COMMITTED_COST,
1425 to_number(null) TXN_PR_COMMITTED_COST,
1426 to_number(null) TXN_OTH_COMMITTED_COST,
1427 bal.PRJ_RAW_COST,
1428 bal.PRJ_BILL_RAW_COST,
1429 bal.PRJ_BRDN_COST,
1430 bal.PRJ_BILL_BRDN_COST,
1431 bal.PRJ_REVENUE,
1432 to_number(null) PRJ_SUP_INV_COMMITTED_COST,
1433 to_number(null) PRJ_PO_COMMITTED_COST,
1434 to_number(null) PRJ_PR_COMMITTED_COST,
1435 to_number(null) PRJ_OTH_COMMITTED_COST,
1436 bal.POU_RAW_COST,
1437 bal.POU_BILL_RAW_COST,
1438 bal.POU_BRDN_COST,
1439 bal.POU_BILL_BRDN_COST,
1440 bal.POU_REVENUE,
1441 to_number(null) POU_SUP_INV_COMMITTED_COST,
1442 to_number(null) POU_PO_COMMITTED_COST,
1443 to_number(null) POU_PR_COMMITTED_COST,
1444 to_number(null) POU_OTH_COMMITTED_COST,
1445 bal.EOU_RAW_COST,
1446 bal.EOU_BILL_RAW_COST,
1447 bal.EOU_BRDN_COST,
1448 bal.EOU_BILL_BRDN_COST,
1449 to_number(null) EOU_SUP_INV_COMMITTED_COST,
1450 to_number(null) EOU_PO_COMMITTED_COST,
1451 to_number(null) EOU_PR_COMMITTED_COST,
1452 to_number(null) EOU_OTH_COMMITTED_COST,
1453 bal.G1_RAW_COST,
1454 bal.G1_BILL_RAW_COST,
1455 bal.G1_BRDN_COST,
1456 bal.G1_BILL_BRDN_COST,
1457 bal.G1_REVENUE,
1458 to_number(null) G1_SUP_INV_COMMITTED_COST,
1459 to_number(null) G1_PO_COMMITTED_COST,
1460 to_number(null) G1_PR_COMMITTED_COST,
1461 to_number(null) G1_OTH_COMMITTED_COST,
1462 bal.G2_RAW_COST,
1463 bal.G2_BILL_RAW_COST,
1464 bal.G2_BRDN_COST,
1465 bal.G2_BILL_BRDN_COST,
1466 bal.G2_REVENUE,
1467 to_number(null) G2_SUP_INV_COMMITTED_COST,
1468 to_number(null) G2_PO_COMMITTED_COST,
1469 to_number(null) G2_PR_COMMITTED_COST,
1470 to_number(null) G2_OTH_COMMITTED_COST,
1471 bal.QUANTITY,
1472 bal.BILL_QUANTITY,
1473 bal.cbs_version_id,
1474 bal.cbs_element_id
1475 from
1476 PA_STAGE1_BATCH_OLAP map,
1477 PJI_FP_TXN_ACCUM bal,
1478 PA_RBS_PRJ_ASSIGNMENTS asg,
1479 pa_projects_all pa,
1480 pa_pjt_proj_batch batch
1481 where
1482 g_extraction_type in ('FULL', 'PARTIAL') and
1483 -- decode(l_extraction_type,'FULL','Y','PARTIAL',decode(bitand(l_refresh_code,1),1,'Y','N'),'N') ='Y' and -- Bug#5099574
1484 -- l_plan_type_id IS NULL and -- Bug#5099574
1485 bal.PROJECT_ID = map.PROJECT_ID and
1486 asg.PROJECT_ID = map.PROJECT_ID and
1487 asg.REPORTING_USAGE_FLAG = 'Y' and
1488 bal.project_id = pa.project_id and
1489 pa.project_id = batch.project_id and
1490 pa.pjt_rollup_enabled_flag = 'Y'
1491 union all -- initial commitments data
1492 select
1493 bal.TXN_ACCUM_HEADER_ID,
1494 to_number(null) RESOURCE_CLASS_ID,
1495 bal.PROJECT_ID,
1496 to_char(null) PROJECT_TYPE_CLASS,
1497 nvl(bal.TASK_ID, -1) TASK_ID,
1498 bal.RECVR_PERIOD_TYPE,
1499 bal.RECVR_PERIOD_ID,
1500 asg.RBS_VERSION_ID RBS_VERSION_ID,
1501 bal.TXN_CURRENCY_CODE,
1502 to_number(null) TXN_RAW_COST,
1503 to_number(null) TXN_BILL_RAW_COST,
1504 to_number(null) TXN_BRDN_COST,
1505 to_number(null) TXN_BILL_BRDN_COST,
1506 to_number(null) TXN_REVENUE,
1507 bal.TXN_SUP_INV_COMMITTED_COST,
1508 bal.TXN_PO_COMMITTED_COST,
1509 bal.TXN_PR_COMMITTED_COST,
1510 bal.TXN_OTH_COMMITTED_COST,
1511 to_number(null) PRJ_RAW_COST,
1512 to_number(null) PRJ_BILL_RAW_COST,
1513 to_number(null) PRJ_BRDN_COST,
1514 to_number(null) PRJ_BILL_BRDN_COST,
1515 to_number(null) PRJ_REVENUE,
1516 bal.PRJ_SUP_INV_COMMITTED_COST,
1517 bal.PRJ_PO_COMMITTED_COST,
1518 bal.PRJ_PR_COMMITTED_COST,
1519 bal.PRJ_OTH_COMMITTED_COST,
1520 to_number(null) POU_RAW_COST,
1521 to_number(null) POU_BILL_RAW_COST,
1522 to_number(null) POU_BRDN_COST,
1523 to_number(null) POU_BILL_BRDN_COST,
1524 to_number(null) POU_REVENUE,
1525 bal.POU_SUP_INV_COMMITTED_COST,
1526 bal.POU_PO_COMMITTED_COST,
1527 bal.POU_PR_COMMITTED_COST,
1528 bal.POU_OTH_COMMITTED_COST,
1529 to_number(null) EOU_RAW_COST,
1530 to_number(null) EOU_BILL_RAW_COST,
1531 to_number(null) EOU_BRDN_COST,
1532 to_number(null) EOU_BILL_BRDN_COST,
1533 bal.EOU_SUP_INV_COMMITTED_COST,
1534 bal.EOU_PO_COMMITTED_COST,
1535 bal.EOU_PR_COMMITTED_COST,
1536 bal.EOU_OTH_COMMITTED_COST,
1537 to_number(null) G1_RAW_COST,
1538 to_number(null) G1_BILL_RAW_COST,
1539 to_number(null) G1_BRDN_COST,
1540 to_number(null) G1_BILL_BRDN_COST,
1541 to_number(null) G1_REVENUE,
1542 bal.G1_SUP_INV_COMMITTED_COST,
1543 bal.G1_PO_COMMITTED_COST,
1544 bal.G1_PR_COMMITTED_COST,
1545 bal.G1_OTH_COMMITTED_COST,
1546 to_number(null) G2_RAW_COST,
1547 to_number(null) G2_BILL_RAW_COST,
1548 to_number(null) G2_BRDN_COST,
1549 to_number(null) G2_BILL_BRDN_COST,
1550 to_number(null) G2_REVENUE,
1551 bal.G2_SUP_INV_COMMITTED_COST,
1552 bal.G2_PO_COMMITTED_COST,
1553 bal.G2_PR_COMMITTED_COST,
1554 bal.G2_OTH_COMMITTED_COST,
1555 to_number(null) QUANTITY,
1556 to_number(null) BILL_QUANTITY,
1557 bal.cbs_version_id,
1558 bal.cbs_element_id
1559 from
1560 PA_STAGE1_BATCH_OLAP map,
1561 PJI_FP_TXN_ACCUM1 bal,
1562 PA_RBS_PRJ_ASSIGNMENTS asg,
1563 pa_projects_all pa,
1564 pa_pjt_proj_batch batch
1565 where
1566 g_extraction_type in ('FULL', 'PARTIAL') and
1567 -- decode(l_extraction_type,'FULL','Y','PARTIAL',decode(bitand(l_refresh_code,1),1,'Y','N'),'N') ='Y' and -- Bug#5099574
1568 -- l_plan_type_id IS NULL and -- Bug#5099574
1569 bal.PROJECT_ID = map.PROJECT_ID and
1570 asg.PROJECT_ID = map.PROJECT_ID and
1571 asg.REPORTING_USAGE_FLAG = 'Y' and
1572 bal.project_id = pa.project_id and
1573 pa.project_id = batch.project_id and
1574 pa.pjt_rollup_enabled_flag = 'Y'
1575 union all -- incremental data
1576 select
1577 tmp7.TXN_ACCUM_HEADER_ID,
1578 tmp7.RESOURCE_CLASS_ID,
1579 tmp7.PROJECT_ID,
1580 tmp7.PROJECT_TYPE_CLASS,
1581 nvl(tmp7.TASK_ID, -1) TASK_ID,
1582 tmp7.RECVR_PERIOD_TYPE,
1583 tmp7.RECVR_PERIOD_ID,
1584 asg.RBS_VERSION_ID RBS_VERSION_ID,
1585 tmp7.TXN_CURRENCY_CODE,
1586 tmp7.TXN_RAW_COST,
1587 tmp7.TXN_BILL_RAW_COST,
1588 tmp7.TXN_BRDN_COST,
1589 tmp7.TXN_BILL_BRDN_COST,
1590 tmp7.TXN_REVENUE,
1591 tmp7.TXN_SUP_INV_COMMITTED_COST,
1592 tmp7.TXN_PO_COMMITTED_COST,
1593 tmp7.TXN_PR_COMMITTED_COST,
1594 tmp7.TXN_OTH_COMMITTED_COST,
1595 tmp7.PRJ_RAW_COST,
1596 tmp7.PRJ_BILL_RAW_COST,
1597 tmp7.PRJ_BRDN_COST,
1598 tmp7.PRJ_BILL_BRDN_COST,
1599 tmp7.PRJ_REVENUE,
1600 decode(nvl(proj_batch.project_id, -1), -1, tmp7.PRJ_SUP_INV_COMMITTED_COST,0),
1601 decode(nvl(proj_batch.project_id, -1), -1, tmp7.PRJ_PO_COMMITTED_COST,0),
1602 decode(nvl(proj_batch.project_id, -1), -1, tmp7.PRJ_PR_COMMITTED_COST,0),
1603 decode(nvl(proj_batch.project_id, -1), -1, tmp7.PRJ_OTH_COMMITTED_COST,0),
1604 tmp7.POU_RAW_COST,
1605 tmp7.POU_BILL_RAW_COST,
1606 tmp7.POU_BRDN_COST,
1607 tmp7.POU_BILL_BRDN_COST,
1608 tmp7.POU_REVENUE,
1609 decode(nvl(proj_batch.project_id, -1), -1, tmp7.POU_SUP_INV_COMMITTED_COST,0),
1610 decode(nvl(proj_batch.project_id, -1), -1, tmp7.POU_PO_COMMITTED_COST,0),
1611 decode(nvl(proj_batch.project_id, -1), -1, tmp7.POU_PR_COMMITTED_COST,0),
1612 decode(nvl(proj_batch.project_id, -1), -1, tmp7.POU_OTH_COMMITTED_COST,0),
1613 tmp7.EOU_RAW_COST,
1614 tmp7.EOU_BILL_RAW_COST,
1615 tmp7.EOU_BRDN_COST,
1616 tmp7.EOU_BILL_BRDN_COST,
1617 decode(nvl(proj_batch.project_id, -1), -1, tmp7.EOU_SUP_INV_COMMITTED_COST,0),
1618 decode(nvl(proj_batch.project_id, -1), -1, tmp7.EOU_PO_COMMITTED_COST,0),
1619 decode(nvl(proj_batch.project_id, -1), -1, tmp7.EOU_PR_COMMITTED_COST,0),
1620 decode(nvl(proj_batch.project_id, -1), -1, tmp7.EOU_OTH_COMMITTED_COST,0),
1621 tmp7.G1_RAW_COST,
1622 tmp7.G1_BILL_RAW_COST,
1623 tmp7.G1_BRDN_COST,
1624 tmp7.G1_BILL_BRDN_COST,
1625 tmp7.G1_REVENUE,
1626 decode(nvl(proj_batch.project_id, -1), -1, tmp7.G1_SUP_INV_COMMITTED_COST,0),
1627 decode(nvl(proj_batch.project_id, -1), -1, tmp7.G1_PO_COMMITTED_COST,0),
1628 decode(nvl(proj_batch.project_id, -1), -1, tmp7.G1_PR_COMMITTED_COST,0),
1629 decode(nvl(proj_batch.project_id, -1), -1, tmp7.G1_OTH_COMMITTED_COST,0),
1630 tmp7.G2_RAW_COST,
1631 tmp7.G2_BILL_RAW_COST,
1632 tmp7.G2_BRDN_COST,
1633 tmp7.G2_BILL_BRDN_COST,
1634 tmp7.G2_REVENUE,
1635 decode(nvl(proj_batch.project_id, -1), -1, tmp7.G2_SUP_INV_COMMITTED_COST,0),
1636 decode(nvl(proj_batch.project_id, -1), -1, tmp7.G2_PO_COMMITTED_COST,0),
1637 decode(nvl(proj_batch.project_id, -1), -1, tmp7.G2_PR_COMMITTED_COST,0),
1638 decode(nvl(proj_batch.project_id, -1), -1, tmp7.G2_OTH_COMMITTED_COST,0),
1639 tmp7.QUANTITY,
1640 tmp7.BILL_QUANTITY,
1641 tmp7.cbs_version_id,
1642 tmp7.cbs_element_id
1643 from
1644 PA_FIN7_OLAP tmp7, PA_STAGE1_BATCH_OLAP proj_batch,
1645 PA_RBS_PRJ_ASSIGNMENTS asg, pa_projects_all pa, pa_pjt_proj_batch batch
1646 where
1647 g_extraction_type = 'INCREMENTAL'
1648 and tmp7.project_id = proj_batch.project_id (+) and
1649 asg.PROJECT_ID = tmp7.PROJECT_ID and
1650 asg.REPORTING_USAGE_FLAG = 'Y' and
1651 tmp7.project_id = pa.project_id and
1652 pa.project_id = batch.project_id and
1653 pa.pjt_rollup_enabled_flag = 'Y'
1654 union all
1655 select
1656 bal.TXN_ACCUM_HEADER_ID,
1657 bal.RESOURCE_CLASS_ID,
1658 bal.PROJECT_ID,
1659 bal.PROJECT_TYPE_CLASS,
1660 nvl(bal.TASK_ID, -1) TASK_ID,
1661 bal.RECVR_PERIOD_TYPE,
1662 bal.RECVR_PERIOD_ID,
1663 asg.RBS_VERSION_ID RBS_VERSION_ID,
1664 bal.TXN_CURRENCY_CODE,
1665 bal.TXN_RAW_COST,
1666 bal.TXN_BILL_RAW_COST,
1667 bal.TXN_BRDN_COST,
1668 bal.TXN_BILL_BRDN_COST,
1669 bal.TXN_REVENUE,
1670 to_number(null) TXN_SUP_INV_COMMITTED_COST,
1671 to_number(null) TXN_PO_COMMITTED_COST,
1672 to_number(null) TXN_PR_COMMITTED_COST,
1673 to_number(null) TXN_OTH_COMMITTED_COST,
1674 bal.PRJ_RAW_COST,
1675 bal.PRJ_BILL_RAW_COST,
1676 bal.PRJ_BRDN_COST,
1677 bal.PRJ_BILL_BRDN_COST,
1678 bal.PRJ_REVENUE,
1679 to_number(null) PRJ_SUP_INV_COMMITTED_COST,
1680 to_number(null) PRJ_PO_COMMITTED_COST,
1681 to_number(null) PRJ_PR_COMMITTED_COST,
1682 to_number(null) PRJ_OTH_COMMITTED_COST,
1683 bal.POU_RAW_COST,
1684 bal.POU_BILL_RAW_COST,
1685 bal.POU_BRDN_COST,
1686 bal.POU_BILL_BRDN_COST,
1687 bal.POU_REVENUE,
1688 to_number(null) POU_SUP_INV_COMMITTED_COST,
1689 to_number(null) POU_PO_COMMITTED_COST,
1690 to_number(null) POU_PR_COMMITTED_COST,
1691 to_number(null) POU_OTH_COMMITTED_COST,
1692 bal.EOU_RAW_COST,
1693 bal.EOU_BILL_RAW_COST,
1694 bal.EOU_BRDN_COST,
1695 bal.EOU_BILL_BRDN_COST,
1696 to_number(null) EOU_SUP_INV_COMMITTED_COST,
1697 to_number(null) EOU_PO_COMMITTED_COST,
1698 to_number(null) EOU_PR_COMMITTED_COST,
1699 to_number(null) EOU_OTH_COMMITTED_COST,
1700 bal.G1_RAW_COST,
1701 bal.G1_BILL_RAW_COST,
1702 bal.G1_BRDN_COST,
1703 bal.G1_BILL_BRDN_COST,
1704 bal.G1_REVENUE,
1705 to_number(null) G1_SUP_INV_COMMITTED_COST,
1706 to_number(null) G1_PO_COMMITTED_COST,
1707 to_number(null) G1_PR_COMMITTED_COST,
1708 to_number(null) G1_OTH_COMMITTED_COST,
1709 bal.G2_RAW_COST,
1710 bal.G2_BILL_RAW_COST,
1711 bal.G2_BRDN_COST,
1712 bal.G2_BILL_BRDN_COST,
1713 bal.G2_REVENUE,
1714 to_number(null) G2_SUP_INV_COMMITTED_COST,
1715 to_number(null) G2_PO_COMMITTED_COST,
1716 to_number(null) G2_PR_COMMITTED_COST,
1717 to_number(null) G2_OTH_COMMITTED_COST,
1718 bal.QUANTITY,
1719 bal.BILL_QUANTITY,
1720 bal.cbs_version_id,
1721 bal.cbs_element_id
1722 from PJI_FP_TXN_ACCUM bal,
1723 PA_RBS_PRJ_ASSIGNMENTS asg,
1724 PA_FIN7_OLAP fin7,
1725 pa_projects_all pa,
1726 pa_pjt_proj_batch batch
1727 where
1728 g_extraction_type = 'INCREMENTAL' and
1729 asg.PROJECT_ID = bal.PROJECT_ID and
1730 asg.REPORTING_USAGE_FLAG = 'Y' and
1731 bal.project_id = fin7.project_id and
1732 bal.RECVR_PERIOD_TYPE = fin7.RECVR_PERIOD_TYPE and
1733 bal.RECVR_PERIOD_ID = fin7.RECVR_PERIOD_ID and
1734 bal.project_id = pa.project_id and
1735 pa.project_id = batch.project_id and
1736 pa.pjt_rollup_enabled_flag = 'Y'
1737 ) tmp1,
1738 pa_rbs_txn_accum_map rbs, pa_projects_all p
1739 where tmp1.project_id = p.project_id and
1740 tmp1.txn_accum_header_id = rbs.txn_accum_header_id (+) and
1741 tmp1.rbs_version_id = rbs.struct_version_id(+)
1742 ;
1743 END maintain_actuals_prj;
1744
1745
1746 Procedure maintain_actuals_projfunc (
1747 p_group_size IN NUMBER) as
1748
1749
1750 BEGIN
1751
1752
1753 INSERT INTO pa_olap_stage(
1754 OLAP_GROUP ,
1755 PROJECT_ID ,
1756 PROJECT_ELEMENT_ID ,
1757 TIME_ID ,
1758 CALENDAR_TYPE ,
1759 CURRENCY_CODE ,
1760 RBS_ELEMENT_ID ,
1761 RBS_VERSION_ID ,
1762 PLAN_VERSION_ID ,
1763 RAW_COST ,
1764 BRDN_COST ,
1765 REVENUE ,
1766 BILL_RAW_COST ,
1767 BILL_BRDN_COST ,
1768 BILL_LABOR_RAW_COST ,
1769 BILL_LABOR_BRDN_COST ,
1770 BILL_LABOR_HRS ,
1771 EQUIPMENT_RAW_COST ,
1772 EQUIPMENT_BRDN_COST ,
1773 CAPITALIZABLE_RAW_COST ,
1774 CAPITALIZABLE_BRDN_COST ,
1775 LABOR_RAW_COST ,
1776 LABOR_BRDN_COST ,
1777 LABOR_HRS ,
1778 LABOR_REVENUE ,
1779 EQUIPMENT_HOURS ,
1780 BILLABLE_EQUIPMENT_HOURS ,
1781 SUP_INV_COMMITTED_COST ,
1782 PO_COMMITTED_COST ,
1783 PR_COMMITTED_COST ,
1784 OTH_COMMITTED_COST ,
1785 CUSTOM1 ,
1786 CUSTOM2 ,
1787 CUSTOM3 ,
1788 CUSTOM4 ,
1789 CUSTOM5 ,
1790 CUSTOM6 ,
1791 CUSTOM7 ,
1792 CUSTOM8 ,
1793 CUSTOM9 ,
1794 CUSTOM10 ,
1795 CUSTOM11 ,
1796 CUSTOM12 ,
1797 CUSTOM13 ,
1798 CUSTOM14 ,
1799 CUSTOM15 ,
1800 CBS_VERSION_ID ,
1801 CBS_ELEMENT_ID ,
1802 LAST_UPDATE_DATE ,
1803 LAST_UPDATED_BY ,
1804 CREATION_DATE ,
1805 CREATED_BY ,
1806 LAST_UPDATE_LOGIN
1807 )
1808 select
1809 p.olap_group,
1810 tmp1.PROJECT_ID,
1811 tmp1.TASK_ID,
1812 tmp1.RECVR_PERIOD_ID,
1813 substrb(tmp1.RECVR_PERIOD_TYPE, 1,1),
1814 p.projfunc_currency_code,
1815 -1, --satya2
1816 -1,
1817 -1,
1818 tmp1.PRJ_RAW_COST,
1819 tmp1.PRJ_BRDN_COST,
1820 tmp1.PRJ_REVENUE,
1821 decode(tmp1.PROJECT_TYPE_CLASS, 'B', tmp1.PRJ_BILL_RAW_COST, to_number(null)) BILL_RAW_COST,
1822 decode(tmp1.PROJECT_TYPE_CLASS, 'B', tmp1.PRJ_BILL_BRDN_COST, to_number(null)) BILL_BRDN_COST,
1823 decode(tmp1.PROJECT_TYPE_CLASS || '_' || tmp1.RESOURCE_CLASS_ID, 'B_1', tmp1.PRJ_BILL_RAW_COST, to_number(null)) BILL_LABOR_RAW_COST,
1824 decode(tmp1.PROJECT_TYPE_CLASS || '_' || tmp1.RESOURCE_CLASS_ID, 'B_1', tmp1.PRJ_BILL_BRDN_COST, to_number(null)) BILL_LABOR_BRDN_COST,
1825 decode(tmp1.PROJECT_TYPE_CLASS || '_' || tmp1.resource_class_id, 'B_1', tmp1.BILL_QUANTITY, to_number(null)) BILL_LABOR_HRS,
1826 decode(tmp1.resource_class_id, '2', tmp1.PRJ_RAW_COST, to_number(null)) EQUIPMENT_RAW_COST,
1827 decode(tmp1.resource_class_id, '2', tmp1.PRJ_BRDN_COST, to_number(null)) EQUIPMENT_BRDN_COST,
1828 decode(tmp1.PROJECT_TYPE_CLASS, 'C', tmp1.PRJ_BILL_RAW_COST, to_number(null)) CAPITALIZABLE_RAW_COST,
1829 decode(tmp1.PROJECT_TYPE_CLASS, 'C', tmp1.PRJ_BILL_BRDN_COST, to_number(null))CAPITALIZABLE_BRDN_COST,
1830 decode(tmp1.resource_class_id, '1', tmp1.PRJ_RAW_COST, to_number(null)) LABOR_RAW_COST,
1831 decode(tmp1.resource_class_id, '1', tmp1.PRJ_BRDN_COST, to_number(null)) LABOR_BRDN_COST,
1832 decode(tmp1.resource_class_id, '1', tmp1.QUANTITY, to_number(null)) LABOR_HRS,
1833 decode(tmp1.resource_class_id, '1', tmp1.PRJ_REVENUE, to_number(null)) LABOR_REVENUE,
1834 decode(tmp1.resource_class_id, '2', tmp1.QUANTITY, to_number(null)) EQUIPMENT_HOURS,
1835 decode(tmp1.resource_class_id, '2', tmp1.BILL_QUANTITY, to_number(null)) BILLABLE_EQUIPMENT_HOURS,
1836 tmp1.PRJ_SUP_INV_COMMITTED_COST,
1837 tmp1.PRJ_PO_COMMITTED_COST,
1838 tmp1.PRJ_PR_COMMITTED_COST,
1839 tmp1.PRJ_OTH_COMMITTED_COST,
1840 0,
1841 0,
1842 0,
1843 0,
1844 0,
1845 0,
1846 0,
1847 0,
1848 0,
1849 0,
1850 0,
1851 0,
1852 0,
1853 0,
1854 0,
1855 nvl(tmp1.CBS_VERSION_ID,-1), --satya2
1856 nvl(tmp1.CBS_ELEMENT_ID,-1), --satya2
1857 sysdate ,
1858 -1 ,
1859 sysdate ,
1860 -1 ,
1861 -1
1862 from
1863 (
1864 select -- initial actuals data
1865 bal.TXN_ACCUM_HEADER_ID,
1866 bal.RESOURCE_CLASS_ID,
1867 bal.PROJECT_ID,
1868 bal.PROJECT_TYPE_CLASS,
1869 nvl(bal.TASK_ID, -1) TASK_ID,
1870 bal.RECVR_PERIOD_TYPE,
1871 bal.RECVR_PERIOD_ID,
1872 -1 RBS_VERSION_ID,
1873 bal.TXN_CURRENCY_CODE,
1874 bal.TXN_RAW_COST,
1875 bal.TXN_BILL_RAW_COST,
1876 bal.TXN_BRDN_COST,
1877 bal.TXN_BILL_BRDN_COST,
1878 bal.TXN_REVENUE,
1879 to_number(null) TXN_SUP_INV_COMMITTED_COST,
1880 to_number(null) TXN_PO_COMMITTED_COST,
1881 to_number(null) TXN_PR_COMMITTED_COST,
1882 to_number(null) TXN_OTH_COMMITTED_COST,
1883 bal.PRJ_RAW_COST,
1884 bal.PRJ_BILL_RAW_COST,
1885 bal.PRJ_BRDN_COST,
1886 bal.PRJ_BILL_BRDN_COST,
1887 bal.PRJ_REVENUE,
1888 to_number(null) PRJ_SUP_INV_COMMITTED_COST,
1889 to_number(null) PRJ_PO_COMMITTED_COST,
1890 to_number(null) PRJ_PR_COMMITTED_COST,
1891 to_number(null) PRJ_OTH_COMMITTED_COST,
1892 bal.POU_RAW_COST,
1893 bal.POU_BILL_RAW_COST,
1894 bal.POU_BRDN_COST,
1895 bal.POU_BILL_BRDN_COST,
1896 bal.POU_REVENUE,
1897 to_number(null) POU_SUP_INV_COMMITTED_COST,
1898 to_number(null) POU_PO_COMMITTED_COST,
1899 to_number(null) POU_PR_COMMITTED_COST,
1900 to_number(null) POU_OTH_COMMITTED_COST,
1901 bal.EOU_RAW_COST,
1902 bal.EOU_BILL_RAW_COST,
1903 bal.EOU_BRDN_COST,
1904 bal.EOU_BILL_BRDN_COST,
1905 to_number(null) EOU_SUP_INV_COMMITTED_COST,
1906 to_number(null) EOU_PO_COMMITTED_COST,
1907 to_number(null) EOU_PR_COMMITTED_COST,
1908 to_number(null) EOU_OTH_COMMITTED_COST,
1909 bal.G1_RAW_COST,
1910 bal.G1_BILL_RAW_COST,
1911 bal.G1_BRDN_COST,
1912 bal.G1_BILL_BRDN_COST,
1913 bal.G1_REVENUE,
1914 to_number(null) G1_SUP_INV_COMMITTED_COST,
1915 to_number(null) G1_PO_COMMITTED_COST,
1916 to_number(null) G1_PR_COMMITTED_COST,
1917 to_number(null) G1_OTH_COMMITTED_COST,
1918 bal.G2_RAW_COST,
1919 bal.G2_BILL_RAW_COST,
1920 bal.G2_BRDN_COST,
1921 bal.G2_BILL_BRDN_COST,
1922 bal.G2_REVENUE,
1923 to_number(null) G2_SUP_INV_COMMITTED_COST,
1924 to_number(null) G2_PO_COMMITTED_COST,
1925 to_number(null) G2_PR_COMMITTED_COST,
1926 to_number(null) G2_OTH_COMMITTED_COST,
1927 bal.QUANTITY,
1928 bal.BILL_QUANTITY,
1929 bal.cbs_version_id,
1930 bal.cbs_element_id
1931 from
1932 PA_STAGE1_BATCH_OLAP map,
1933 PJI_FP_TXN_ACCUM bal,
1934 pa_projects_all pa,
1935 pa_pjt_proj_batch batch
1936 where
1937 g_extraction_type in ('FULL', 'PARTIAL') and
1938 -- decode(l_extraction_type,'FULL','Y','PARTIAL',decode(bitand(l_refresh_code,1),1,'Y','N'),'N') ='Y' and -- Bug#5099574
1939 -- l_plan_type_id IS NULL and -- Bug#5099574
1940 bal.PROJECT_ID = map.PROJECT_ID and
1941 bal.project_id = pa.project_id and
1942 pa.pjt_rollup_enabled_flag = 'Y' and
1943 pa.project_id = batch.project_id and
1944 pa.project_currency_code <> pa.projfunc_currency_code
1945 union all -- initial commitments data
1946 select
1947 bal.TXN_ACCUM_HEADER_ID,
1948 to_number(null) RESOURCE_CLASS_ID,
1949 bal.PROJECT_ID,
1950 to_char(null) PROJECT_TYPE_CLASS,
1951 nvl(bal.TASK_ID, -1) TASK_ID,
1952 bal.RECVR_PERIOD_TYPE,
1953 bal.RECVR_PERIOD_ID,
1954 -1 RBS_VERSION_ID,
1955 bal.TXN_CURRENCY_CODE,
1956 to_number(null) TXN_RAW_COST,
1957 to_number(null) TXN_BILL_RAW_COST,
1958 to_number(null) TXN_BRDN_COST,
1959 to_number(null) TXN_BILL_BRDN_COST,
1960 to_number(null) TXN_REVENUE,
1961 bal.TXN_SUP_INV_COMMITTED_COST,
1962 bal.TXN_PO_COMMITTED_COST,
1963 bal.TXN_PR_COMMITTED_COST,
1964 bal.TXN_OTH_COMMITTED_COST,
1965 to_number(null) PRJ_RAW_COST,
1966 to_number(null) PRJ_BILL_RAW_COST,
1967 to_number(null) PRJ_BRDN_COST,
1968 to_number(null) PRJ_BILL_BRDN_COST,
1969 to_number(null) PRJ_REVENUE,
1970 bal.PRJ_SUP_INV_COMMITTED_COST,
1971 bal.PRJ_PO_COMMITTED_COST,
1972 bal.PRJ_PR_COMMITTED_COST,
1973 bal.PRJ_OTH_COMMITTED_COST,
1974 to_number(null) POU_RAW_COST,
1975 to_number(null) POU_BILL_RAW_COST,
1976 to_number(null) POU_BRDN_COST,
1977 to_number(null) POU_BILL_BRDN_COST,
1978 to_number(null) POU_REVENUE,
1979 bal.POU_SUP_INV_COMMITTED_COST,
1980 bal.POU_PO_COMMITTED_COST,
1981 bal.POU_PR_COMMITTED_COST,
1982 bal.POU_OTH_COMMITTED_COST,
1983 to_number(null) EOU_RAW_COST,
1984 to_number(null) EOU_BILL_RAW_COST,
1985 to_number(null) EOU_BRDN_COST,
1986 to_number(null) EOU_BILL_BRDN_COST,
1987 bal.EOU_SUP_INV_COMMITTED_COST,
1988 bal.EOU_PO_COMMITTED_COST,
1989 bal.EOU_PR_COMMITTED_COST,
1990 bal.EOU_OTH_COMMITTED_COST,
1991 to_number(null) G1_RAW_COST,
1992 to_number(null) G1_BILL_RAW_COST,
1993 to_number(null) G1_BRDN_COST,
1994 to_number(null) G1_BILL_BRDN_COST,
1995 to_number(null) G1_REVENUE,
1996 bal.G1_SUP_INV_COMMITTED_COST,
1997 bal.G1_PO_COMMITTED_COST,
1998 bal.G1_PR_COMMITTED_COST,
1999 bal.G1_OTH_COMMITTED_COST,
2000 to_number(null) G2_RAW_COST,
2001 to_number(null) G2_BILL_RAW_COST,
2002 to_number(null) G2_BRDN_COST,
2003 to_number(null) G2_BILL_BRDN_COST,
2004 to_number(null) G2_REVENUE,
2005 bal.G2_SUP_INV_COMMITTED_COST,
2006 bal.G2_PO_COMMITTED_COST,
2007 bal.G2_PR_COMMITTED_COST,
2008 bal.G2_OTH_COMMITTED_COST,
2009 to_number(null) QUANTITY,
2010 to_number(null) BILL_QUANTITY,
2011 bal.cbs_version_id,
2012 bal.cbs_element_id
2013 from
2014 PA_STAGE1_BATCH_OLAP map,
2015 PJI_FP_TXN_ACCUM1 bal,
2016 pa_projects_all pa,
2017 pa_pjt_proj_batch batch
2018 where
2019 g_extraction_type in ('FULL', 'PARTIAL') and
2020 -- decode(l_extraction_type,'FULL','Y','PARTIAL',decode(bitand(l_refresh_code,1),1,'Y','N'),'N') ='Y' and -- Bug#5099574
2021 -- l_plan_type_id IS NULL and -- Bug#5099574
2022 bal.PROJECT_ID = map.PROJECT_ID and
2023 bal.project_id = pa.project_id and
2024 pa.project_id = batch.project_id and
2025 pa.pjt_rollup_enabled_flag = 'Y' and
2026 pa.project_currency_code <> pa.projfunc_currency_code
2027 union all -- incremental data
2028 select
2029 tmp7.TXN_ACCUM_HEADER_ID,
2030 tmp7.RESOURCE_CLASS_ID,
2031 tmp7.PROJECT_ID,
2032 tmp7.PROJECT_TYPE_CLASS,
2033 nvl(tmp7.TASK_ID, -1) TASK_ID,
2034 tmp7.RECVR_PERIOD_TYPE,
2035 tmp7.RECVR_PERIOD_ID,
2036 -1 RBS_VERSION_ID,
2037 tmp7.TXN_CURRENCY_CODE,
2038 tmp7.TXN_RAW_COST,
2039 tmp7.TXN_BILL_RAW_COST,
2040 tmp7.TXN_BRDN_COST,
2041 tmp7.TXN_BILL_BRDN_COST,
2042 tmp7.TXN_REVENUE,
2043 tmp7.TXN_SUP_INV_COMMITTED_COST,
2044 tmp7.TXN_PO_COMMITTED_COST,
2045 tmp7.TXN_PR_COMMITTED_COST,
2046 tmp7.TXN_OTH_COMMITTED_COST,
2047 tmp7.PRJ_RAW_COST,
2048 tmp7.PRJ_BILL_RAW_COST,
2049 tmp7.PRJ_BRDN_COST,
2050 tmp7.PRJ_BILL_BRDN_COST,
2051 tmp7.PRJ_REVENUE,
2052 decode(nvl(proj_batch.project_id, -1), -1, tmp7.PRJ_SUP_INV_COMMITTED_COST,0),
2053 decode(nvl(proj_batch.project_id, -1), -1, tmp7.PRJ_PO_COMMITTED_COST,0),
2054 decode(nvl(proj_batch.project_id, -1), -1, tmp7.PRJ_PR_COMMITTED_COST,0),
2055 decode(nvl(proj_batch.project_id, -1), -1, tmp7.PRJ_OTH_COMMITTED_COST,0),
2056 tmp7.POU_RAW_COST,
2057 tmp7.POU_BILL_RAW_COST,
2058 tmp7.POU_BRDN_COST,
2059 tmp7.POU_BILL_BRDN_COST,
2060 tmp7.POU_REVENUE,
2061 decode(nvl(proj_batch.project_id, -1), -1, tmp7.POU_SUP_INV_COMMITTED_COST,0),
2062 decode(nvl(proj_batch.project_id, -1), -1, tmp7.POU_PO_COMMITTED_COST,0),
2063 decode(nvl(proj_batch.project_id, -1), -1, tmp7.POU_PR_COMMITTED_COST,0),
2064 decode(nvl(proj_batch.project_id, -1), -1, tmp7.POU_OTH_COMMITTED_COST,0),
2065 tmp7.EOU_RAW_COST,
2066 tmp7.EOU_BILL_RAW_COST,
2067 tmp7.EOU_BRDN_COST,
2068 tmp7.EOU_BILL_BRDN_COST,
2069 decode(nvl(proj_batch.project_id, -1), -1, tmp7.EOU_SUP_INV_COMMITTED_COST,0),
2070 decode(nvl(proj_batch.project_id, -1), -1, tmp7.EOU_PO_COMMITTED_COST,0),
2071 decode(nvl(proj_batch.project_id, -1), -1, tmp7.EOU_PR_COMMITTED_COST,0),
2072 decode(nvl(proj_batch.project_id, -1), -1, tmp7.EOU_OTH_COMMITTED_COST,0),
2073 tmp7.G1_RAW_COST,
2074 tmp7.G1_BILL_RAW_COST,
2075 tmp7.G1_BRDN_COST,
2076 tmp7.G1_BILL_BRDN_COST,
2077 tmp7.G1_REVENUE,
2078 decode(nvl(proj_batch.project_id, -1), -1, tmp7.G1_SUP_INV_COMMITTED_COST,0),
2079 decode(nvl(proj_batch.project_id, -1), -1, tmp7.G1_PO_COMMITTED_COST,0),
2080 decode(nvl(proj_batch.project_id, -1), -1, tmp7.G1_PR_COMMITTED_COST,0),
2081 decode(nvl(proj_batch.project_id, -1), -1, tmp7.G1_OTH_COMMITTED_COST,0),
2082 tmp7.G2_RAW_COST,
2083 tmp7.G2_BILL_RAW_COST,
2084 tmp7.G2_BRDN_COST,
2085 tmp7.G2_BILL_BRDN_COST,
2086 tmp7.G2_REVENUE,
2087 decode(nvl(proj_batch.project_id, -1), -1, tmp7.G2_SUP_INV_COMMITTED_COST,0),
2088 decode(nvl(proj_batch.project_id, -1), -1, tmp7.G2_PO_COMMITTED_COST,0),
2089 decode(nvl(proj_batch.project_id, -1), -1, tmp7.G2_PR_COMMITTED_COST,0),
2090 decode(nvl(proj_batch.project_id, -1), -1, tmp7.G2_OTH_COMMITTED_COST,0),
2091 tmp7.QUANTITY,
2092 tmp7.BILL_QUANTITY,
2093 tmp7.cbs_version_id,
2094 tmp7.cbs_element_id
2095 from
2096 PA_FIN7_OLAP tmp7, PA_STAGE1_BATCH_OLAP proj_batch,
2097 pa_projects_all pa, pa_pjt_proj_batch batch
2098 where
2099 g_extraction_type = 'INCREMENTAL'
2100 and tmp7.project_id = proj_batch.project_id (+) and
2101 tmp7.project_id = pa.project_id and
2102 pa.project_id = batch.project_id and
2103 pa.pjt_rollup_enabled_flag = 'Y' and
2104 pa.project_currency_code <> pa.projfunc_currency_code
2105 union all
2106 select
2107 bal.TXN_ACCUM_HEADER_ID,
2108 bal.RESOURCE_CLASS_ID,
2109 bal.PROJECT_ID,
2110 bal.PROJECT_TYPE_CLASS,
2111 nvl(bal.TASK_ID, -1) TASK_ID,
2112 bal.RECVR_PERIOD_TYPE,
2113 bal.RECVR_PERIOD_ID,
2114 -1 RBS_VERSION_ID,
2115 bal.TXN_CURRENCY_CODE,
2116 bal.TXN_RAW_COST,
2117 bal.TXN_BILL_RAW_COST,
2118 bal.TXN_BRDN_COST,
2119 bal.TXN_BILL_BRDN_COST,
2120 bal.TXN_REVENUE,
2121 to_number(null) TXN_SUP_INV_COMMITTED_COST,
2122 to_number(null) TXN_PO_COMMITTED_COST,
2123 to_number(null) TXN_PR_COMMITTED_COST,
2124 to_number(null) TXN_OTH_COMMITTED_COST,
2125 bal.PRJ_RAW_COST,
2126 bal.PRJ_BILL_RAW_COST,
2127 bal.PRJ_BRDN_COST,
2128 bal.PRJ_BILL_BRDN_COST,
2129 bal.PRJ_REVENUE,
2130 to_number(null) PRJ_SUP_INV_COMMITTED_COST,
2131 to_number(null) PRJ_PO_COMMITTED_COST,
2132 to_number(null) PRJ_PR_COMMITTED_COST,
2133 to_number(null) PRJ_OTH_COMMITTED_COST,
2134 bal.POU_RAW_COST,
2135 bal.POU_BILL_RAW_COST,
2136 bal.POU_BRDN_COST,
2137 bal.POU_BILL_BRDN_COST,
2138 bal.POU_REVENUE,
2139 to_number(null) POU_SUP_INV_COMMITTED_COST,
2140 to_number(null) POU_PO_COMMITTED_COST,
2141 to_number(null) POU_PR_COMMITTED_COST,
2142 to_number(null) POU_OTH_COMMITTED_COST,
2143 bal.EOU_RAW_COST,
2144 bal.EOU_BILL_RAW_COST,
2145 bal.EOU_BRDN_COST,
2146 bal.EOU_BILL_BRDN_COST,
2147 to_number(null) EOU_SUP_INV_COMMITTED_COST,
2148 to_number(null) EOU_PO_COMMITTED_COST,
2149 to_number(null) EOU_PR_COMMITTED_COST,
2150 to_number(null) EOU_OTH_COMMITTED_COST,
2151 bal.G1_RAW_COST,
2152 bal.G1_BILL_RAW_COST,
2153 bal.G1_BRDN_COST,
2154 bal.G1_BILL_BRDN_COST,
2155 bal.G1_REVENUE,
2156 to_number(null) G1_SUP_INV_COMMITTED_COST,
2157 to_number(null) G1_PO_COMMITTED_COST,
2158 to_number(null) G1_PR_COMMITTED_COST,
2159 to_number(null) G1_OTH_COMMITTED_COST,
2160 bal.G2_RAW_COST,
2161 bal.G2_BILL_RAW_COST,
2162 bal.G2_BRDN_COST,
2163 bal.G2_BILL_BRDN_COST,
2164 bal.G2_REVENUE,
2165 to_number(null) G2_SUP_INV_COMMITTED_COST,
2166 to_number(null) G2_PO_COMMITTED_COST,
2167 to_number(null) G2_PR_COMMITTED_COST,
2168 to_number(null) G2_OTH_COMMITTED_COST,
2169 bal.QUANTITY,
2170 bal.BILL_QUANTITY,
2171 bal.cbs_version_id,
2172 bal.cbs_element_id
2173 from PJI_FP_TXN_ACCUM bal,
2174 PA_FIN7_OLAP fin7,
2175 pa_projects_all pa,
2176 pa_pjt_proj_batch batch
2177 where
2178 g_extraction_type = 'INCREMENTAL' and
2179 bal.project_id = fin7.project_id and
2180 bal.RECVR_PERIOD_TYPE = fin7.RECVR_PERIOD_TYPE and
2181 bal.RECVR_PERIOD_ID = fin7.RECVR_PERIOD_ID and
2182 bal.project_id = pa.project_id and
2183 pa.project_id = batch.project_id and
2184 pa.pjt_rollup_enabled_flag = 'Y' and
2185 pa.project_currency_code <> pa.projfunc_currency_code
2186 ) tmp1, pa_projects_all p
2187 where tmp1.project_id = p.project_id;
2188
2189 INSERT INTO pa_olap_stage(
2190 OLAP_GROUP ,
2191 PROJECT_ID ,
2192 PROJECT_ELEMENT_ID ,
2193 TIME_ID ,
2194 CALENDAR_TYPE ,
2195 CURRENCY_CODE ,
2196 RBS_ELEMENT_ID ,
2197 RBS_VERSION_ID ,
2198 PLAN_VERSION_ID ,
2199 RAW_COST ,
2200 BRDN_COST ,
2201 REVENUE ,
2202 BILL_RAW_COST ,
2203 BILL_BRDN_COST ,
2204 BILL_LABOR_RAW_COST ,
2205 BILL_LABOR_BRDN_COST ,
2206 BILL_LABOR_HRS ,
2207 EQUIPMENT_RAW_COST ,
2208 EQUIPMENT_BRDN_COST ,
2209 CAPITALIZABLE_RAW_COST ,
2210 CAPITALIZABLE_BRDN_COST ,
2211 LABOR_RAW_COST ,
2212 LABOR_BRDN_COST ,
2213 LABOR_HRS ,
2214 LABOR_REVENUE ,
2215 EQUIPMENT_HOURS ,
2216 BILLABLE_EQUIPMENT_HOURS ,
2217 SUP_INV_COMMITTED_COST ,
2218 PO_COMMITTED_COST ,
2219 PR_COMMITTED_COST ,
2220 OTH_COMMITTED_COST ,
2221 CUSTOM1 ,
2222 CUSTOM2 ,
2223 CUSTOM3 ,
2224 CUSTOM4 ,
2225 CUSTOM5 ,
2226 CUSTOM6 ,
2227 CUSTOM7 ,
2228 CUSTOM8 ,
2229 CUSTOM9 ,
2230 CUSTOM10 ,
2231 CUSTOM11 ,
2232 CUSTOM12 ,
2233 CUSTOM13 ,
2234 CUSTOM14 ,
2235 CUSTOM15 ,
2236 CBS_VERSION_ID ,
2237 CBS_ELEMENT_ID ,
2238 LAST_UPDATE_DATE ,
2239 LAST_UPDATED_BY ,
2240 CREATION_DATE ,
2241 CREATED_BY ,
2242 LAST_UPDATE_LOGIN
2243 )
2244 select
2245 p.olap_group,
2246 tmp1.PROJECT_ID,
2247 tmp1.TASK_ID,
2248 tmp1.RECVR_PERIOD_ID,
2249 substrb(tmp1.RECVR_PERIOD_TYPE, 1,1),
2250 p.PROJFUNC_CURRENCY_CODE,
2251 nvl(rbs.ELEMENT_ID,-1), --satya2
2252 nvl(tmp1.RBS_VERSION_ID, -1), --satya2
2253 -1,
2254 tmp1.POU_RAW_COST,
2255 tmp1.POU_BRDN_COST,
2256 tmp1.POU_REVENUE,
2257 decode(tmp1.PROJECT_TYPE_CLASS, 'B', tmp1.POU_BILL_RAW_COST, to_number(null)) BILL_RAW_COST,
2258 decode(tmp1.PROJECT_TYPE_CLASS, 'B', tmp1.POU_BILL_BRDN_COST, to_number(null)) BILL_BRDN_COST,
2259 decode(tmp1.PROJECT_TYPE_CLASS || '_' || tmp1.RESOURCE_CLASS_ID, 'B_1', tmp1.POU_BILL_RAW_COST, to_number(null)) BILL_LABOR_RAW_COST,
2260 decode(tmp1.PROJECT_TYPE_CLASS || '_' || tmp1.RESOURCE_CLASS_ID, 'B_1', tmp1.POU_BILL_BRDN_COST, to_number(null)) BILL_LABOR_BRDN_COST,
2261 decode(tmp1.PROJECT_TYPE_CLASS || '_' || tmp1.resource_class_id, 'B_1', tmp1.BILL_QUANTITY, to_number(null)) BILL_LABOR_HRS,
2262 decode(tmp1.resource_class_id, '2', tmp1.POU_RAW_COST, to_number(null)) EQUIPMENT_RAW_COST,
2263 decode(tmp1.resource_class_id, '2', tmp1.POU_BRDN_COST, to_number(null)) EQUIPMENT_BRDN_COST,
2264 decode(tmp1.PROJECT_TYPE_CLASS, 'C', tmp1.POU_BILL_RAW_COST, to_number(null)) CAPITALIZABLE_RAW_COST,
2265 decode(tmp1.PROJECT_TYPE_CLASS, 'C', tmp1.POU_BILL_BRDN_COST, to_number(null))CAPITALIZABLE_BRDN_COST,
2266 decode(tmp1.resource_class_id, '1', tmp1.POU_RAW_COST, to_number(null)) LABOR_RAW_COST,
2267 decode(tmp1.resource_class_id, '1', tmp1.POU_BRDN_COST, to_number(null)) LABOR_BRDN_COST,
2268 decode(tmp1.resource_class_id, '1', tmp1.QUANTITY, to_number(null)) LABOR_HRS,
2269 decode(tmp1.resource_class_id, '1', tmp1.POU_REVENUE, to_number(null)) LABOR_REVENUE,
2270 decode(tmp1.resource_class_id, '2', tmp1.QUANTITY, to_number(null)) EQUIPMENT_HOURS,
2271 decode(tmp1.resource_class_id, '2', tmp1.BILL_QUANTITY, to_number(null)) BILLABLE_EQUIPMENT_HOURS,
2272 tmp1.POU_SUP_INV_COMMITTED_COST,
2273 tmp1.POU_PO_COMMITTED_COST,
2274 tmp1.POU_PR_COMMITTED_COST,
2275 tmp1.POU_OTH_COMMITTED_COST,
2276 0,
2277 0,
2278 0,
2279 0,
2280 0,
2281 0,
2282 0,
2283 0,
2284 0,
2285 0,
2286 0,
2287 0,
2288 0,
2289 0,
2290 0,
2291 nvl(tmp1.CBS_VERSION_ID,-1), --satya2
2292 nvl(tmp1.CBS_ELEMENT_ID,-1), --satya2
2293 sysdate ,
2294 -1 ,
2295 sysdate ,
2296 -1 ,
2297 -1
2298
2299 from
2300 (
2301 select -- initial actuals data
2302 bal.TXN_ACCUM_HEADER_ID,
2303 bal.RESOURCE_CLASS_ID,
2304 bal.PROJECT_ID,
2305 bal.PROJECT_TYPE_CLASS,
2306 nvl(bal.TASK_ID, -1) TASK_ID,
2307 bal.RECVR_PERIOD_TYPE,
2308 bal.RECVR_PERIOD_ID,
2309 nvl(asg.RBS_VERSION_ID, -1) RBS_VERSION_ID,
2310 bal.TXN_CURRENCY_CODE,
2311 bal.TXN_RAW_COST,
2312 bal.TXN_BILL_RAW_COST,
2313 bal.TXN_BRDN_COST,
2314 bal.TXN_BILL_BRDN_COST,
2315 bal.TXN_REVENUE,
2316 to_number(null) TXN_SUP_INV_COMMITTED_COST,
2317 to_number(null) TXN_PO_COMMITTED_COST,
2318 to_number(null) TXN_PR_COMMITTED_COST,
2319 to_number(null) TXN_OTH_COMMITTED_COST,
2320 bal.PRJ_RAW_COST,
2321 bal.PRJ_BILL_RAW_COST,
2322 bal.PRJ_BRDN_COST,
2323 bal.PRJ_BILL_BRDN_COST,
2324 bal.PRJ_REVENUE,
2325 to_number(null) PRJ_SUP_INV_COMMITTED_COST,
2326 to_number(null) PRJ_PO_COMMITTED_COST,
2327 to_number(null) PRJ_PR_COMMITTED_COST,
2328 to_number(null) PRJ_OTH_COMMITTED_COST,
2329 bal.POU_RAW_COST,
2330 bal.POU_BILL_RAW_COST,
2331 bal.POU_BRDN_COST,
2332 bal.POU_BILL_BRDN_COST,
2333 bal.POU_REVENUE,
2334 to_number(null) POU_SUP_INV_COMMITTED_COST,
2335 to_number(null) POU_PO_COMMITTED_COST,
2336 to_number(null) POU_PR_COMMITTED_COST,
2337 to_number(null) POU_OTH_COMMITTED_COST,
2338 bal.EOU_RAW_COST,
2339 bal.EOU_BILL_RAW_COST,
2340 bal.EOU_BRDN_COST,
2341 bal.EOU_BILL_BRDN_COST,
2342 to_number(null) EOU_SUP_INV_COMMITTED_COST,
2343 to_number(null) EOU_PO_COMMITTED_COST,
2344 to_number(null) EOU_PR_COMMITTED_COST,
2345 to_number(null) EOU_OTH_COMMITTED_COST,
2346 bal.G1_RAW_COST,
2347 bal.G1_BILL_RAW_COST,
2348 bal.G1_BRDN_COST,
2349 bal.G1_BILL_BRDN_COST,
2350 bal.G1_REVENUE,
2351 to_number(null) G1_SUP_INV_COMMITTED_COST,
2352 to_number(null) G1_PO_COMMITTED_COST,
2353 to_number(null) G1_PR_COMMITTED_COST,
2354 to_number(null) G1_OTH_COMMITTED_COST,
2355 bal.G2_RAW_COST,
2356 bal.G2_BILL_RAW_COST,
2357 bal.G2_BRDN_COST,
2358 bal.G2_BILL_BRDN_COST,
2359 bal.G2_REVENUE,
2360 to_number(null) G2_SUP_INV_COMMITTED_COST,
2361 to_number(null) G2_PO_COMMITTED_COST,
2362 to_number(null) G2_PR_COMMITTED_COST,
2363 to_number(null) G2_OTH_COMMITTED_COST,
2364 bal.QUANTITY,
2365 bal.BILL_QUANTITY,
2366 bal.cbs_version_id,
2367 bal.cbs_element_id
2368 from
2369 PA_STAGE1_BATCH_OLAP map,
2370 PJI_FP_TXN_ACCUM bal,
2371 PA_RBS_PRJ_ASSIGNMENTS asg,
2372 pa_projects_all pa,
2373 pa_pjt_proj_batch batch
2374 where
2375 g_extraction_type in ('FULL', 'PARTIAL') and
2376 -- decode(l_extraction_type,'FULL','Y','PARTIAL',decode(bitand(l_refresh_code,1),1,'Y','N'),'N') ='Y' and -- Bug#5099574
2377 -- l_plan_type_id IS NULL and -- Bug#5099574
2378 bal.PROJECT_ID = map.PROJECT_ID and
2379 asg.PROJECT_ID = map.PROJECT_ID and
2380 asg.REPORTING_USAGE_FLAG = 'Y' and
2381 bal.project_id = pa.project_id and
2382 pa.project_id = batch.project_id and
2383 pa.pjt_rollup_enabled_flag = 'Y' and
2384 pa.project_currency_code <> pa.projfunc_currency_code
2385 union all -- initial commitments data
2386 select
2387 bal.TXN_ACCUM_HEADER_ID,
2388 to_number(null) RESOURCE_CLASS_ID,
2389 bal.PROJECT_ID,
2390 to_char(null) PROJECT_TYPE_CLASS,
2391 nvl(bal.TASK_ID, -1) TASK_ID,
2392 bal.RECVR_PERIOD_TYPE,
2393 bal.RECVR_PERIOD_ID,
2394 nvl(asg.RBS_VERSION_ID, -1) RBS_VERSION_ID,
2395 bal.TXN_CURRENCY_CODE,
2396 to_number(null) TXN_RAW_COST,
2397 to_number(null) TXN_BILL_RAW_COST,
2398 to_number(null) TXN_BRDN_COST,
2399 to_number(null) TXN_BILL_BRDN_COST,
2400 to_number(null) TXN_REVENUE,
2401 bal.TXN_SUP_INV_COMMITTED_COST,
2402 bal.TXN_PO_COMMITTED_COST,
2403 bal.TXN_PR_COMMITTED_COST,
2404 bal.TXN_OTH_COMMITTED_COST,
2405 to_number(null) PRJ_RAW_COST,
2406 to_number(null) PRJ_BILL_RAW_COST,
2407 to_number(null) PRJ_BRDN_COST,
2408 to_number(null) PRJ_BILL_BRDN_COST,
2409 to_number(null) PRJ_REVENUE,
2410 bal.PRJ_SUP_INV_COMMITTED_COST,
2411 bal.PRJ_PO_COMMITTED_COST,
2412 bal.PRJ_PR_COMMITTED_COST,
2413 bal.PRJ_OTH_COMMITTED_COST,
2414 to_number(null) POU_RAW_COST,
2415 to_number(null) POU_BILL_RAW_COST,
2416 to_number(null) POU_BRDN_COST,
2417 to_number(null) POU_BILL_BRDN_COST,
2418 to_number(null) POU_REVENUE,
2419 bal.POU_SUP_INV_COMMITTED_COST,
2420 bal.POU_PO_COMMITTED_COST,
2421 bal.POU_PR_COMMITTED_COST,
2422 bal.POU_OTH_COMMITTED_COST,
2423 to_number(null) EOU_RAW_COST,
2424 to_number(null) EOU_BILL_RAW_COST,
2425 to_number(null) EOU_BRDN_COST,
2426 to_number(null) EOU_BILL_BRDN_COST,
2427 bal.EOU_SUP_INV_COMMITTED_COST,
2428 bal.EOU_PO_COMMITTED_COST,
2429 bal.EOU_PR_COMMITTED_COST,
2430 bal.EOU_OTH_COMMITTED_COST,
2431 to_number(null) G1_RAW_COST,
2432 to_number(null) G1_BILL_RAW_COST,
2433 to_number(null) G1_BRDN_COST,
2434 to_number(null) G1_BILL_BRDN_COST,
2435 to_number(null) G1_REVENUE,
2436 bal.G1_SUP_INV_COMMITTED_COST,
2437 bal.G1_PO_COMMITTED_COST,
2438 bal.G1_PR_COMMITTED_COST,
2439 bal.G1_OTH_COMMITTED_COST,
2440 to_number(null) G2_RAW_COST,
2441 to_number(null) G2_BILL_RAW_COST,
2442 to_number(null) G2_BRDN_COST,
2443 to_number(null) G2_BILL_BRDN_COST,
2444 to_number(null) G2_REVENUE,
2445 bal.G2_SUP_INV_COMMITTED_COST,
2446 bal.G2_PO_COMMITTED_COST,
2447 bal.G2_PR_COMMITTED_COST,
2448 bal.G2_OTH_COMMITTED_COST,
2449 to_number(null) QUANTITY,
2450 to_number(null) BILL_QUANTITY,
2451 bal.cbs_version_id,
2452 bal.cbs_element_id
2453 from
2454 PA_STAGE1_BATCH_OLAP map,
2455 PJI_FP_TXN_ACCUM1 bal,
2456 PA_RBS_PRJ_ASSIGNMENTS asg,
2457 pa_projects_all pa,
2458 pa_pjt_proj_batch batch
2459 where
2460 g_extraction_type in ('FULL', 'PARTIAL') and
2461 -- decode(l_extraction_type,'FULL','Y','PARTIAL',decode(bitand(l_refresh_code,1),1,'Y','N'),'N') ='Y' and -- Bug#5099574
2462 -- l_plan_type_id IS NULL and -- Bug#5099574
2463 bal.PROJECT_ID = map.PROJECT_ID and
2464 asg.PROJECT_ID = map.PROJECT_ID and
2465 asg.REPORTING_USAGE_FLAG = 'Y' and
2466 bal.project_id = pa.project_id and
2467 pa.project_id = batch.project_id and
2468 pa.pjt_rollup_enabled_flag = 'Y' and
2469 pa.project_currency_code <> pa.projfunc_currency_code
2470 union all -- incremental data
2471 select
2472 tmp7.TXN_ACCUM_HEADER_ID,
2473 tmp7.RESOURCE_CLASS_ID,
2474 tmp7.PROJECT_ID,
2475 tmp7.PROJECT_TYPE_CLASS,
2476 nvl(tmp7.TASK_ID, -1) TASK_ID,
2477 tmp7.RECVR_PERIOD_TYPE,
2478 tmp7.RECVR_PERIOD_ID,
2479 nvl(asg.RBS_VERSION_ID, -1) RBS_VERSION_ID,
2480 tmp7.TXN_CURRENCY_CODE,
2481 tmp7.TXN_RAW_COST,
2482 tmp7.TXN_BILL_RAW_COST,
2483 tmp7.TXN_BRDN_COST,
2484 tmp7.TXN_BILL_BRDN_COST,
2485 tmp7.TXN_REVENUE,
2486 tmp7.TXN_SUP_INV_COMMITTED_COST,
2487 tmp7.TXN_PO_COMMITTED_COST,
2488 tmp7.TXN_PR_COMMITTED_COST,
2489 tmp7.TXN_OTH_COMMITTED_COST,
2490 tmp7.PRJ_RAW_COST,
2491 tmp7.PRJ_BILL_RAW_COST,
2492 tmp7.PRJ_BRDN_COST,
2493 tmp7.PRJ_BILL_BRDN_COST,
2494 tmp7.PRJ_REVENUE,
2495 decode(nvl(proj_batch.project_id, -1), -1, tmp7.PRJ_SUP_INV_COMMITTED_COST,0),
2496 decode(nvl(proj_batch.project_id, -1), -1, tmp7.PRJ_PO_COMMITTED_COST,0),
2497 decode(nvl(proj_batch.project_id, -1), -1, tmp7.PRJ_PR_COMMITTED_COST,0),
2498 decode(nvl(proj_batch.project_id, -1), -1, tmp7.PRJ_OTH_COMMITTED_COST,0),
2499 tmp7.POU_RAW_COST,
2500 tmp7.POU_BILL_RAW_COST,
2501 tmp7.POU_BRDN_COST,
2502 tmp7.POU_BILL_BRDN_COST,
2503 tmp7.POU_REVENUE,
2504 decode(nvl(proj_batch.project_id, -1), -1, tmp7.POU_SUP_INV_COMMITTED_COST,0),
2505 decode(nvl(proj_batch.project_id, -1), -1, tmp7.POU_PO_COMMITTED_COST,0),
2506 decode(nvl(proj_batch.project_id, -1), -1, tmp7.POU_PR_COMMITTED_COST,0),
2507 decode(nvl(proj_batch.project_id, -1), -1, tmp7.POU_OTH_COMMITTED_COST,0),
2508 tmp7.EOU_RAW_COST,
2509 tmp7.EOU_BILL_RAW_COST,
2510 tmp7.EOU_BRDN_COST,
2511 tmp7.EOU_BILL_BRDN_COST,
2512 decode(nvl(proj_batch.project_id, -1), -1, tmp7.EOU_SUP_INV_COMMITTED_COST,0),
2513 decode(nvl(proj_batch.project_id, -1), -1, tmp7.EOU_PO_COMMITTED_COST,0),
2514 decode(nvl(proj_batch.project_id, -1), -1, tmp7.EOU_PR_COMMITTED_COST,0),
2515 decode(nvl(proj_batch.project_id, -1), -1, tmp7.EOU_OTH_COMMITTED_COST,0),
2516 tmp7.G1_RAW_COST,
2517 tmp7.G1_BILL_RAW_COST,
2518 tmp7.G1_BRDN_COST,
2519 tmp7.G1_BILL_BRDN_COST,
2520 tmp7.G1_REVENUE,
2521 decode(nvl(proj_batch.project_id, -1), -1, tmp7.G1_SUP_INV_COMMITTED_COST,0),
2522 decode(nvl(proj_batch.project_id, -1), -1, tmp7.G1_PO_COMMITTED_COST,0),
2523 decode(nvl(proj_batch.project_id, -1), -1, tmp7.G1_PR_COMMITTED_COST,0),
2524 decode(nvl(proj_batch.project_id, -1), -1, tmp7.G1_OTH_COMMITTED_COST,0),
2525 tmp7.G2_RAW_COST,
2526 tmp7.G2_BILL_RAW_COST,
2527 tmp7.G2_BRDN_COST,
2528 tmp7.G2_BILL_BRDN_COST,
2529 tmp7.G2_REVENUE,
2530 decode(nvl(proj_batch.project_id, -1), -1, tmp7.G2_SUP_INV_COMMITTED_COST,0),
2531 decode(nvl(proj_batch.project_id, -1), -1, tmp7.G2_PO_COMMITTED_COST,0),
2532 decode(nvl(proj_batch.project_id, -1), -1, tmp7.G2_PR_COMMITTED_COST,0),
2533 decode(nvl(proj_batch.project_id, -1), -1, tmp7.G2_OTH_COMMITTED_COST,0),
2534 tmp7.QUANTITY,
2535 tmp7.BILL_QUANTITY,
2536 tmp7.cbs_version_id,
2537 tmp7.cbs_element_id
2538 from
2539 PA_FIN7_OLAP tmp7, PA_STAGE1_BATCH_OLAP proj_batch,
2540 PA_RBS_PRJ_ASSIGNMENTS asg, pa_projects_all pa, pa_pjt_proj_batch batch
2541 where
2542 g_extraction_type = 'INCREMENTAL'
2543 and tmp7.project_id = proj_batch.project_id (+) and
2544 asg.PROJECT_ID = tmp7.PROJECT_ID and
2545 asg.REPORTING_USAGE_FLAG = 'Y' and
2546 tmp7.project_id = pa.project_id and
2547 pa.project_id = batch.project_id and
2548 pa.pjt_rollup_enabled_flag = 'Y' and
2549 pa.project_currency_code <> pa.projfunc_currency_code
2550 union all
2551 select
2552 bal.TXN_ACCUM_HEADER_ID,
2553 bal.RESOURCE_CLASS_ID,
2554 bal.PROJECT_ID,
2555 bal.PROJECT_TYPE_CLASS,
2556 nvl(bal.TASK_ID, -1) TASK_ID,
2557 bal.RECVR_PERIOD_TYPE,
2558 bal.RECVR_PERIOD_ID,
2559 nvl(asg.RBS_VERSION_ID, -1) RBS_VERSION_ID,
2560 bal.TXN_CURRENCY_CODE,
2561 bal.TXN_RAW_COST,
2562 bal.TXN_BILL_RAW_COST,
2563 bal.TXN_BRDN_COST,
2564 bal.TXN_BILL_BRDN_COST,
2565 bal.TXN_REVENUE,
2566 to_number(null) TXN_SUP_INV_COMMITTED_COST,
2567 to_number(null) TXN_PO_COMMITTED_COST,
2568 to_number(null) TXN_PR_COMMITTED_COST,
2569 to_number(null) TXN_OTH_COMMITTED_COST,
2570 bal.PRJ_RAW_COST,
2571 bal.PRJ_BILL_RAW_COST,
2572 bal.PRJ_BRDN_COST,
2573 bal.PRJ_BILL_BRDN_COST,
2574 bal.PRJ_REVENUE,
2575 to_number(null) PRJ_SUP_INV_COMMITTED_COST,
2576 to_number(null) PRJ_PO_COMMITTED_COST,
2577 to_number(null) PRJ_PR_COMMITTED_COST,
2578 to_number(null) PRJ_OTH_COMMITTED_COST,
2579 bal.POU_RAW_COST,
2580 bal.POU_BILL_RAW_COST,
2581 bal.POU_BRDN_COST,
2582 bal.POU_BILL_BRDN_COST,
2583 bal.POU_REVENUE,
2584 to_number(null) POU_SUP_INV_COMMITTED_COST,
2585 to_number(null) POU_PO_COMMITTED_COST,
2586 to_number(null) POU_PR_COMMITTED_COST,
2587 to_number(null) POU_OTH_COMMITTED_COST,
2588 bal.EOU_RAW_COST,
2589 bal.EOU_BILL_RAW_COST,
2590 bal.EOU_BRDN_COST,
2591 bal.EOU_BILL_BRDN_COST,
2592 to_number(null) EOU_SUP_INV_COMMITTED_COST,
2593 to_number(null) EOU_PO_COMMITTED_COST,
2594 to_number(null) EOU_PR_COMMITTED_COST,
2595 to_number(null) EOU_OTH_COMMITTED_COST,
2596 bal.G1_RAW_COST,
2597 bal.G1_BILL_RAW_COST,
2598 bal.G1_BRDN_COST,
2599 bal.G1_BILL_BRDN_COST,
2600 bal.G1_REVENUE,
2601 to_number(null) G1_SUP_INV_COMMITTED_COST,
2602 to_number(null) G1_PO_COMMITTED_COST,
2603 to_number(null) G1_PR_COMMITTED_COST,
2604 to_number(null) G1_OTH_COMMITTED_COST,
2605 bal.G2_RAW_COST,
2606 bal.G2_BILL_RAW_COST,
2607 bal.G2_BRDN_COST,
2608 bal.G2_BILL_BRDN_COST,
2609 bal.G2_REVENUE,
2610 to_number(null) G2_SUP_INV_COMMITTED_COST,
2611 to_number(null) G2_PO_COMMITTED_COST,
2612 to_number(null) G2_PR_COMMITTED_COST,
2613 to_number(null) G2_OTH_COMMITTED_COST,
2614 bal.QUANTITY,
2615 bal.BILL_QUANTITY,
2616 bal.cbs_version_id,
2617 bal.cbs_element_id
2618 from PJI_FP_TXN_ACCUM bal,
2619 PA_RBS_PRJ_ASSIGNMENTS asg,
2620 PA_FIN7_OLAP fin7,
2621 pa_projects_all pa,
2622 pa_pjt_proj_batch batch
2623 where
2624 g_extraction_type = 'INCREMENTAL' and
2625 asg.PROJECT_ID = bal.PROJECT_ID and
2626 asg.REPORTING_USAGE_FLAG = 'Y' and
2627 bal.project_id = fin7.project_id and
2628 bal.RECVR_PERIOD_TYPE = fin7.RECVR_PERIOD_TYPE and
2629 bal.RECVR_PERIOD_ID = fin7.RECVR_PERIOD_ID and
2630 bal.project_id = pa.project_id and
2631 pa.project_id = batch.project_id and
2632 pa.pjt_rollup_enabled_flag = 'Y' and
2633 pa.project_currency_code <> pa.projfunc_currency_code
2634 ) tmp1,
2635 pa_rbs_txn_accum_map rbs, pa_projects_all p
2636 where tmp1.project_id = p.project_id and
2637 tmp1.txn_accum_header_id = rbs.txn_accum_header_id (+) and
2638 tmp1.rbs_version_id = rbs.struct_version_id(+)
2639 ;
2640
2641 END maintain_actuals_projfunc;
2642
2643 Procedure maintain_activity_data_prj (
2644 p_group_size IN NUMBER) as
2645
2646 BEGIN
2647
2648
2649 insert into pa_ac_olap_stage
2650 (OLAP_GROUP,
2651 PROJECT_ID ,
2652 PROJECT_ELEMENT_ID ,
2653 TIME_ID ,
2654 CALENDAR_TYPE ,
2655 CURRENCY_CODE ,
2656 REVENUE ,
2657 INITIAL_FUNDING_AMOUNT ,
2658 INITIAL_FUNDING_COUNT ,
2659 ADDITIONAL_FUNDING_AMOUNT ,
2660 ADDITIONAL_FUNDING_COUNT ,
2661 CANCELLED_FUNDING_AMOUNT ,
2662 CANCELLED_FUNDING_COUNT ,
2663 FUNDING_ADJUSTMENT_AMOUNT ,
2664 FUNDING_ADJUSTMENT_COUNT ,
2665 REVENUE_WRITEOFF ,
2666 AR_INVOICE_AMOUNT ,
2667 AR_INVOICE_COUNT ,
2668 AR_CASH_APPLIED_AMOUNT ,
2669 AR_INVOICE_WRITE_OFF_AMOUNT ,
2670 AR_INVOICE_WRITEOFF_COUNT ,
2671 AR_CREDIT_MEMO_AMOUNT ,
2672 AR_CREDIT_MEMO_COUNT ,
2673 UNBILLED_RECEIVABLES ,
2674 UNEARNED_REVENUE ,
2675 AR_UNAPPR_INVOICE_AMOUNT ,
2676 AR_UNAPPR_INVOICE_COUNT ,
2677 AR_APPR_INVOICE_AMOUNT ,
2678 AR_APPR_INVOICE_COUNT ,
2679 AR_AMOUNT_DUE ,
2680 AR_COUNT_DUE ,
2681 AR_AMOUNT_OVERDUE ,
2682 AR_COUNT_OVERDUE ,
2683 --DORMANT_BACKLOG_INACTIV ,
2684 --DORMANT_BACKLOG_START ,
2685 --LOST_BACKLOG ,
2686 --ACTIVE_BACKLOG ,
2687 --REVENUE_AT_RISK ,
2688 LAST_UPDATE_DATE ,
2689 LAST_UPDATED_BY ,
2690 CREATION_DATE ,
2691 CREATED_BY ,
2692 LAST_UPDATE_LOGIN ,
2693 CUSTOM1 ,
2694 CUSTOM2 ,
2695 CUSTOM3 ,
2696 CUSTOM4 ,
2697 CUSTOM5 ,
2698 CUSTOM6 ,
2699 CUSTOM7 ,
2700 CUSTOM8 ,
2701 CUSTOM9 ,
2702 CUSTOM10 ,
2703 CUSTOM11 ,
2704 CUSTOM12 ,
2705 CUSTOM13 ,
2706 CUSTOM14 ,
2707 CUSTOM15 )
2708 select /*+ ordered */
2709 prj.olap_group,
2710 src.PROJECT_ID,
2711 nvl(src.TASK_ID, -1) TASK_ID,
2712 src.PERIOD_ID,
2713 substrb(src.PERIOD_TYPE,1,1) CALENDAR_TYPE,
2714 prj.PROJECT_CURRENCY_CODE PRJ_CURRENCY_CODE,
2715 sum(src.PRJ_REVENUE) PRJ_REVENUE,
2716 -- sum(src.PRJ_FUNDING) PRJ_FUNDING,
2717 sum(src.PRJ_INITIAL_FUNDING_AMOUNT) PRJ_INITIAL_FUNDING_AMOUNT,
2718 sum(src.INITIAL_FUNDING_COUNT) INITIAL_FUNDING_COUNT,
2719 sum(src.PRJ_ADDITIONAL_FUNDING_AMOUNT)PRJ_ADDITIONAL_FUNDING_AMOUNT,
2720 sum(src.ADDITIONAL_FUNDING_COUNT) ADDITIONAL_FUNDING_COUNT,
2721 sum(src.PRJ_CANCELLED_FUNDING_AMOUNT) PRJ_CANCELLED_FUNDING_AMOUNT,
2722 sum(src.CANCELLED_FUNDING_COUNT) CANCELLED_FUNDING_COUNT,
2723 sum(src.PRJ_FUNDING_ADJUSTMENT_AMOUNT)PRJ_FUNDING_ADJUSTMENT_AMOUNT,
2724 sum(src.FUNDING_ADJUSTMENT_COUNT) FUNDING_ADJUSTMENT_COUNT,
2725 sum(src.PRJ_REVENUE_WRITEOFF) PRJ_REVENUE_WRITEOFF,
2726 sum(src.PRJ_AR_INVOICE_AMOUNT) PRJ_AR_INVOICE_AMOUNT,
2727 sum(src.AR_INVOICE_COUNT) AR_INVOICE_COUNT,
2728 --sum(src.PRJ_AR_CASH_APPLIED_AMOUNT) PRJ_AR_CASH_APPLIED_AMOUNT,
2729 sum(src.AR_CASH_APPLIED_COUNT) AR_CASH_APPLIED_COUNT,
2730 sum(src.PRJ_AR_INVOICE_WRITEOFF_AMOUNT)
2731 PRJ_AR_INVOICE_WRITEOFF_AMOUNT,
2732 sum(src.AR_INVOICE_WRITEOFF_COUNT) AR_INVOICE_WRITEOFF_COUNT,
2733 sum(src.PRJ_AR_CREDIT_MEMO_AMOUNT) PRJ_AR_CREDIT_MEMO_AMOUNT,
2734 sum(src.AR_CREDIT_MEMO_COUNT) AR_CREDIT_MEMO_COUNT,
2735 sum(src.PRJ_UNBILLED_RECEIVABLES) PRJ_UNBILLED_RECEIVABLES,
2736 sum(src.PRJ_UNEARNED_REVENUE) PRJ_UNEARNED_REVENUE,
2737 sum(src.PRJ_AR_UNAPPR_INVOICE_AMOUNT) PRJ_AR_UNAPPR_INVOICE_AMOUNT,
2738 sum(src.AR_UNAPPR_INVOICE_COUNT) AR_UNAPPR_INVOICE_COUNT,
2739 sum(src.PRJ_AR_APPR_INVOICE_AMOUNT) PRJ_AR_APPR_INVOICE_AMOUNT,
2740 sum(src.AR_APPR_INVOICE_COUNT) AR_APPR_INVOICE_COUNT,
2741 sum(src.PRJ_AR_AMOUNT_DUE) PRJ_AR_AMOUNT_DUE,
2742 sum(src.AR_COUNT_DUE) AR_COUNT_DUE,
2743 sum(src.PRJ_AR_AMOUNT_OVERDUE) PRJ_AR_AMOUNT_OVERDUE,
2744 sum(src.AR_COUNT_OVERDUE) AR_COUNT_OVERDUE,
2745 sysdate,
2746 -1,
2747 sysdate,
2748 -1,
2749 -1,
2750 0,
2751 0,
2752 0,
2753 0,
2754 0,
2755 0,
2756 0,
2757 0,
2758 0,
2759 0,
2760 0,
2761 0,
2762 0,
2763 0,
2764 0
2765 from
2766 PJI_FM_AGGR_ACT4 src,
2767 PA_PROJECTS_ALL prj,
2768 PJI_ORG_EXTR_INFO info,
2769 pa_pjt_proj_batch batch
2770 where
2771 src.PROJECT_ID = prj.PROJECT_ID and
2772 prj.ORG_ID = info.ORG_ID and
2773 prj.project_id = batch.project_id and
2774 prj.pjt_rollup_enabled_flag = 'Y'
2775 group by
2776 prj.olap_group,
2777 src.PROJECT_ID,
2778 src.PROJECT_ORG_ID,
2779 src.PROJECT_ORGANIZATION_ID,
2780 nvl(src.TASK_ID, -1),
2781 src.PERIOD_TYPE,
2782 src.PERIOD_ID,
2783 prj.PROJECT_CURRENCY_CODE;
2784
2785 END maintain_activity_data_prj;
2786
2787
2788 Procedure maintain_activity_data_pf (
2789 p_group_size IN NUMBER) as
2790
2791 BEGIN
2792
2793 insert into pa_ac_olap_stage
2794 (OLAP_GROUP,
2795 PROJECT_ID ,
2796 PROJECT_ELEMENT_ID ,
2797 TIME_ID ,
2798 CALENDAR_TYPE ,
2799 CURRENCY_CODE ,
2800 REVENUE ,
2801 INITIAL_FUNDING_AMOUNT ,
2802 INITIAL_FUNDING_COUNT ,
2803 ADDITIONAL_FUNDING_AMOUNT ,
2804 ADDITIONAL_FUNDING_COUNT ,
2805 CANCELLED_FUNDING_AMOUNT ,
2806 CANCELLED_FUNDING_COUNT ,
2807 FUNDING_ADJUSTMENT_AMOUNT ,
2808 FUNDING_ADJUSTMENT_COUNT ,
2809 REVENUE_WRITEOFF ,
2810 AR_INVOICE_AMOUNT ,
2811 AR_INVOICE_COUNT ,
2812 AR_CASH_APPLIED_AMOUNT ,
2813 AR_INVOICE_WRITE_OFF_AMOUNT ,
2814 AR_INVOICE_WRITEOFF_COUNT ,
2815 AR_CREDIT_MEMO_AMOUNT ,
2816 AR_CREDIT_MEMO_COUNT ,
2817 UNBILLED_RECEIVABLES ,
2818 UNEARNED_REVENUE ,
2819 AR_UNAPPR_INVOICE_AMOUNT ,
2820 AR_UNAPPR_INVOICE_COUNT ,
2821 AR_APPR_INVOICE_AMOUNT ,
2822 AR_APPR_INVOICE_COUNT ,
2823 AR_AMOUNT_DUE ,
2824 AR_COUNT_DUE ,
2825 AR_AMOUNT_OVERDUE ,
2826 AR_COUNT_OVERDUE ,
2827 --DORMANT_BACKLOG_INACTIV ,
2828 --DORMANT_BACKLOG_START ,
2829 --LOST_BACKLOG ,
2830 --ACTIVE_BACKLOG ,
2831 --REVENUE_AT_RISK ,
2832 LAST_UPDATE_DATE ,
2833 LAST_UPDATED_BY ,
2834 CREATION_DATE ,
2835 CREATED_BY ,
2836 LAST_UPDATE_LOGIN ,
2837 CUSTOM1 ,
2838 CUSTOM2 ,
2839 CUSTOM3 ,
2840 CUSTOM4 ,
2841 CUSTOM5 ,
2842 CUSTOM6 ,
2843 CUSTOM7 ,
2844 CUSTOM8 ,
2845 CUSTOM9 ,
2846 CUSTOM10 ,
2847 CUSTOM11 ,
2848 CUSTOM12 ,
2849 CUSTOM13 ,
2850 CUSTOM14 ,
2851 CUSTOM15 )
2852 select /*+ ordered */
2853 prj.olap_group,
2854 src.PROJECT_ID,
2855 nvl(src.TASK_ID, -1) TASK_ID,
2856 src.PERIOD_ID,
2857 substrb(src.PERIOD_TYPE,1,1) CALENDAR_TYPE,
2858 prj.PROJFUNC_CURRENCY_CODE PF_CURRENCY_CODE,
2859 sum(src.POU_REVENUE) POU_REVENUE,
2860 -- sum(src.POU_FUNDING) POU_FUNDING,
2861 sum(src.POU_INITIAL_FUNDING_AMOUNT) POU_INITIAL_FUNDING_AMOUNT,
2862 sum(src.INITIAL_FUNDING_COUNT) INITIAL_FUNDING_COUNT,
2863 sum(src.POU_ADDITIONAL_FUNDING_AMOUNT)POU_ADDITIONAL_FUNDING_AMOUNT,
2864 sum(src.ADDITIONAL_FUNDING_COUNT) ADDITIONAL_FUNDING_COUNT,
2865 sum(src.POU_CANCELLED_FUNDING_AMOUNT) POU_CANCELLED_FUNDING_AMOUNT,
2866 sum(src.CANCELLED_FUNDING_COUNT) CANCELLED_FUNDING_COUNT,
2867 sum(src.POU_FUNDING_ADJUSTMENT_AMOUNT)POU_FUNDING_ADJUSTMENT_AMOUNT,
2868 sum(src.FUNDING_ADJUSTMENT_COUNT) FUNDING_ADJUSTMENT_COUNT,
2869 sum(src.POU_REVENUE_WRITEOFF) POU_REVENUE_WRITEOFF,
2870 sum(src.POU_AR_INVOICE_AMOUNT) POU_AR_INVOICE_AMOUNT,
2871 sum(src.AR_INVOICE_COUNT) AR_INVOICE_COUNT,
2872 --sum(src.POU_AR_CASH_APPLIED_AMOUNT) POU_AR_CASH_APPLIED_AMOUNT,
2873 sum(src.AR_CASH_APPLIED_COUNT) AR_CASH_APPLIED_COUNT,
2874 sum(src.POU_AR_INVOICE_WRITEOFF_AMOUNT)
2875 POU_AR_INVOICE_WRITEOFF_AMOUNT,
2876 sum(src.AR_INVOICE_WRITEOFF_COUNT) AR_INVOICE_WRITEOFF_COUNT,
2877 sum(src.POU_AR_CREDIT_MEMO_AMOUNT) POU_AR_CREDIT_MEMO_AMOUNT,
2878 sum(src.AR_CREDIT_MEMO_COUNT) AR_CREDIT_MEMO_COUNT,
2879 sum(src.POU_UNBILLED_RECEIVABLES) POU_UNBILLED_RECEIVABLES,
2880 sum(src.POU_UNEARNED_REVENUE) POU_UNEARNED_REVENUE,
2881 sum(src.POU_AR_UNAPPR_INVOICE_AMOUNT) POU_AR_UNAPPR_INVOICE_AMOUNT,
2882 sum(src.AR_UNAPPR_INVOICE_COUNT) AR_UNAPPR_INVOICE_COUNT,
2883 sum(src.POU_AR_APPR_INVOICE_AMOUNT) POU_AR_APPR_INVOICE_AMOUNT,
2884 sum(src.AR_APPR_INVOICE_COUNT) AR_APPR_INVOICE_COUNT,
2885 sum(src.POU_AR_AMOUNT_DUE) POU_AR_AMOUNT_DUE,
2886 sum(src.AR_COUNT_DUE) AR_COUNT_DUE,
2887 sum(src.POU_AR_AMOUNT_OVERDUE) POU_AR_AMOUNT_OVERDUE,
2888 sum(src.AR_COUNT_OVERDUE) AR_COUNT_OVERDUE,
2889 sysdate,
2890 -1,
2891 sysdate,
2892 -1,
2893 -1,
2894 0,
2895 0,
2896 0,
2897 0,
2898 0,
2899 0,
2900 0,
2901 0,
2902 0,
2903 0,
2904 0,
2905 0,
2906 0,
2907 0,
2908 0
2909 from
2910 PJI_FM_AGGR_ACT4 src,
2911 PA_PROJECTS_ALL prj,
2912 PJI_ORG_EXTR_INFO info,
2913 pa_pjt_proj_batch batch
2914 where
2915 src.PROJECT_ID = prj.PROJECT_ID and
2916 prj.ORG_ID = info.ORG_ID and
2917 prj.project_id = batch.project_id and
2918 prj.pjt_rollup_enabled_flag = 'Y' and
2919 prj.project_currency_code <> prj.projfunc_currency_code
2920 group by
2921 prj.olap_group,
2922 src.PROJECT_ID,
2923 src.PROJECT_ORG_ID,
2924 src.PROJECT_ORGANIZATION_ID,
2925 nvl(src.TASK_ID, -1),
2926 src.PERIOD_TYPE,
2927 src.PERIOD_ID,
2928 prj.PROJFUNC_CURRENCY_CODE;
2929
2930 END maintain_activity_data_pf;
2931
2932
2933 Procedure summarize_olap (
2934 errbuf out nocopy varchar2,
2935 retcode out nocopy varchar2,
2936 p_run_mode in varchar2 default null,
2937 p_operating_unit in number default null,
2938 p_from_project in varchar2 default null,
2939 p_to_project in varchar2 default null,
2940 p_rbs_header_id in number default null,
2941 p_transaction_type in varchar2 default null
2942 )
2943 is
2944
2945 l_worker_id number ;
2946
2947
2948 l_return_status varchar2(255);
2949 l_msg_count number;
2950 l_msg_data varchar2(2000);
2951
2952 l_calling_mode varchar2(30);
2953 p_group_size NUMBER := 100; --satya
2954
2955 cursor c1 is
2956 select project_id
2957 from pa_projects_all
2958 where template_flag = 'N';
2959
2960 TYPE NUM_TBL_TYPE is TABLE of NUMBER index by binary_integer;
2961 p_project_id_tbl NUM_TBL_TYPE;
2962 Begin
2963
2964 if (p_run_mode = 'F') then
2965 g_extraction_type := 'FULL';
2966 elsif (p_run_mode = 'I' or
2967 p_run_mode = 'NO_PRELOAD') then
2968 g_extraction_type := 'INCREMENTAL';
2969 elsif (p_run_mode = 'P') then
2970 g_extraction_type := 'PARTIAL';
2971 elsif (p_run_mode = 'R') then
2972 g_extraction_type := 'RBS';
2973 g_rbs_version_id_arr.delete;
2974 g_rbs_version_id_index_arr.delete;
2975 g_rbs_version_id_rule_arr.delete;
2976 else
2977 commit;
2978 dbms_standard.raise_application_error(-20010, 'Invalid run type');
2979 end if;
2980
2981 open c1;
2982 loop
2983 fetch c1 bulk collect into p_project_id_tbl limit 10;
2984
2985 If p_project_id_tbl.count > 0 then
2986
2987 Forall i in p_project_id_tbl.first..p_project_id_tbl.last
2988
2989 insert into pa_pjt_proj_batch
2990 (
2991 WORKER_ID,
2992 PROJECT_ID,
2993 PJI_PROJECT_STATUS,
2994 EXTRACTION_TYPE,
2995 EXTRACTION_STATUS,
2996 PROJECT_TYPE,
2997 PROJECT_ORG_ID,
2998 PROJECT_ORGANIZATION_ID,
2999 PROJECT_TYPE_CLASS,
3000 PRJ_CURRENCY_CODE,
3001 PROJECT_ACTIVE_FLAG
3002 )
3003 values(1,
3004 p_project_id_tbl(i),
3005 null,
3006 null,
3007 NULL,
3008 null,
3009 null,
3010 NULL,
3011 NULL,
3012 null,
3013 'Y' );
3014
3015 p_project_id_tbl.delete;
3016
3017 maintain_projects(p_group_size);
3018 maintain_budgets(p_group_size);
3019 maintain_actuals_prj(p_group_size);
3020 maintain_actuals_projfunc(p_group_size);
3021 maintain_activity_data_prj(p_group_size);
3022 maintain_activity_data_pf(p_group_size);
3023
3024 delete from pa_pjt_proj_batch;
3025 exit when c1%notfound;
3026 Else
3027 Exit;
3028
3029 END IF;
3030
3031 end loop;
3032
3033 close c1;
3034
3035
3036 --delete from cube_rejected_records ;
3037 If g_extraction_type = 'FULL' THEN
3038
3039 begin
3040
3041 PA_olap_PVT.generate_nev('PA_TASK_OLAP_V');
3042 PA_olap_PVT.generate_nev('PA_RBS_VERSIONS_OLAP_V');
3043 PA_olap_PVT.generate_nev('PA_RBS_ELEMENTS_OLAP_V');
3044 PA_olap_PVT.generate_nev('PA_PROJECT_OLAP_V');
3045 PA_olap_PVT.generate_nev('PA_OLAP_STAGE_V');
3046 PA_olap_PVT.generate_nev('PA_CURRENCY_OLAP_V');
3047 PA_olap_PVT.generate_nev('PA_CBS_VERSIONS_OLAP_V');
3048 PA_olap_PVT.generate_nev('PA_CBS_ELEMENTS_OLAP_V');
3049 PA_olap_PVT.generate_nev('PA_CALENDAR_TYPE_OLAP_V');
3050 PA_olap_PVT.generate_nev('PA_CALENDAR_OLAP_V');
3051 PA_olap_PVT.generate_nev('PA_BUDGET_VER_OLAP_V');
3052 PA_olap_PVT.generate_nev('PA_AC_OLAP_STAGE_V');
3053
3054 commit;
3055
3056 PA_olap_PVT.refresh_dm;
3057
3058 commit;
3059
3060 PA_olap_PVT.generate_nev('PA_TASK_OLAP_V');
3061 PA_olap_PVT.generate_nev('PA_RBS_VERSIONS_OLAP_V');
3062 PA_olap_PVT.generate_nev('PA_RBS_ELEMENTS_OLAP_V');
3063 PA_olap_PVT.generate_nev('PA_PROJECT_OLAP_V');
3064 PA_olap_PVT.generate_nev('PA_OLAP_STAGE_V');
3065 PA_olap_PVT.generate_nev('PA_CURRENCY_OLAP_V');
3066 PA_olap_PVT.generate_nev('PA_CBS_VERSIONS_OLAP_V');
3067 PA_olap_PVT.generate_nev('PA_CBS_ELEMENTS_OLAP_V');
3068 PA_olap_PVT.generate_nev('PA_CALENDAR_TYPE_OLAP_V');
3069 PA_olap_PVT.generate_nev('PA_CALENDAR_OLAP_V');
3070 PA_olap_PVT.generate_nev('PA_BUDGET_VER_OLAP_V');
3071 PA_olap_PVT.generate_nev('PA_AC_OLAP_STAGE_V');
3072
3073 commit;
3074
3075 PA_olap_PVT.refresh_cube;
3076
3077 end ;
3078
3079
3080 ELSIF g_extraction_type = 'INCREMENTAL' THEN
3081
3082 begin
3083
3084 PA_olap_PVT.generate_nev('PA_TASK_OLAP_V');
3085 PA_olap_PVT.generate_nev('PA_RBS_VERSIONS_OLAP_V');
3086 PA_olap_PVT.generate_nev('PA_RBS_ELEMENTS_OLAP_V');
3087 PA_olap_PVT.generate_nev('PA_PROJECT_OLAP_V');
3088 PA_olap_PVT.generate_nev('PA_OLAP_STAGE_V');
3089 PA_olap_PVT.generate_nev('PA_CURRENCY_OLAP_V');
3090 PA_olap_PVT.generate_nev('PA_CBS_VERSIONS_OLAP_V');
3091 PA_olap_PVT.generate_nev('PA_CBS_ELEMENTS_OLAP_V');
3092 PA_olap_PVT.generate_nev('PA_CALENDAR_TYPE_OLAP_V');
3093 PA_olap_PVT.generate_nev('PA_CALENDAR_OLAP_V');
3094 PA_olap_PVT.generate_nev('PA_BUDGET_VER_OLAP_V');
3095 PA_olap_PVT.generate_nev('PA_AC_OLAP_STAGE_V');
3096
3097 commit;
3098
3099 PA_olap_PVT.refresh_dm;
3100
3101 commit;
3102
3103 PA_olap_PVT.generate_nev('PA_TASK_OLAP_V');
3104 PA_olap_PVT.generate_nev('PA_RBS_VERSIONS_OLAP_V');
3105 PA_olap_PVT.generate_nev('PA_RBS_ELEMENTS_OLAP_V');
3106 PA_olap_PVT.generate_nev('PA_PROJECT_OLAP_V');
3107 PA_olap_PVT.generate_nev('PA_OLAP_STAGE_V');
3108 PA_olap_PVT.generate_nev('PA_CURRENCY_OLAP_V');
3109 PA_olap_PVT.generate_nev('PA_CBS_VERSIONS_OLAP_V');
3110 PA_olap_PVT.generate_nev('PA_CBS_ELEMENTS_OLAP_V');
3111 PA_olap_PVT.generate_nev('PA_CALENDAR_TYPE_OLAP_V');
3112 PA_olap_PVT.generate_nev('PA_CALENDAR_OLAP_V');
3113 PA_olap_PVT.generate_nev('PA_BUDGET_VER_OLAP_V');
3114 PA_olap_PVT.generate_nev('PA_AC_OLAP_STAGE_V');
3115
3116 commit;
3117
3118 PA_olap_PVT.update_cube;
3119
3120 end ;
3121
3122 END IF;
3123 -- select * from CUBE_REJECTED_RECORDS; put it in fnd_log
3124
3125
3126 End ;
3127
3128
3129
3130 End PA_ppr_olap_conc_program;