[Home] [Help]
PACKAGE BODY: APPS.PA_PPR_CONCURRENT_PROGRAM
Source
1 package body PA_ppr_concurrent_program as
2 /* $Header: PAPPR01B.pls 120.0.12020000.2 2013/03/10 06:56:48 sachandr noship $ */
3
4 G_PKG_NAME CONSTANT VARCHAR2(30) := 'PA_PPR_CONCURRENT_PROGRAM';
5
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 merge_into_fp_txn_accum (
19 p_project_id IN NUMBER
20 ) as
21
22 begin
23 MERGE INTO PJI_FP_TXN_ACCUM accum
24 USING
25 ( SELECT
26 TXN_ACCUM_HEADER_ID,
27 RESOURCE_CLASS_ID,
28 PROJECT_ID,
29 PROJECT_ORG_ID,
30 PROJECT_ORGANIZATION_ID,
31 PROJECT_TYPE_CLASS,
32 TASK_ID,
33 ASSIGNMENT_ID,
34 NAMED_ROLE,
35 RECVR_PERIOD_TYPE,
36 RECVR_PERIOD_ID,
37 TXN_CURRENCY_CODE,
38 TXN_RAW_COST,
39 TXN_BILL_RAW_COST,
40 TXN_BRDN_COST,
41 TXN_BILL_BRDN_COST,
42 TXN_REVENUE,
43 PRJ_RAW_COST,
44 PRJ_BILL_RAW_COST,
45 PRJ_BRDN_COST,
46 PRJ_BILL_BRDN_COST,
47 PRJ_REVENUE,
48 POU_RAW_COST,
49 POU_BILL_RAW_COST,
50 POU_BRDN_COST,
51 POU_BILL_BRDN_COST,
52 POU_REVENUE,
53 EOU_RAW_COST,
54 EOU_BILL_RAW_COST,
55 EOU_BRDN_COST,
56 EOU_BILL_BRDN_COST,
57 G1_RAW_COST,
58 G1_BILL_RAW_COST,
59 G1_BRDN_COST,
60 G1_BILL_BRDN_COST,
61 G1_REVENUE,
62 G2_RAW_COST,
63 G2_BILL_RAW_COST,
64 G2_BRDN_COST,
65 G2_BILL_BRDN_COST,
66 G2_REVENUE,
67 QUANTITY,
68 BILL_QUANTITY,
69 sysdate LAST_UPDATE_DATE,
70 -1 LAST_UPDATED_BY,
71 sysdate CREATION_DATE,
72 -1 CREATED_BY,
73 -1 LAST_UPDATE_LOGIN
74 FROM PJI_FM_AGGR_FIN7
75 WHERE project_id = p_project_id ) FIN7
76 ON
77 (
78
79 FIN7.TXN_ACCUM_HEADER_ID = accum.TXN_ACCUM_HEADER_ID and
80 FIN7.RESOURCE_CLASS_ID = accum.RESOURCE_CLASS_ID and
81 FIN7.PROJECT_ID = accum.PROJECT_ID and
82 FIN7.PROJECT_ORG_ID = accum.PROJECT_ORG_ID and
83 FIN7.PROJECT_ORGANIZATION_ID = accum.PROJECT_ORGANIZATION_ID and
84 FIN7.TASK_ID = accum.TASK_ID and
85 FIN7.ASSIGNMENT_ID = accum.ASSIGNMENT_ID and
86 FIN7.NAMED_ROLE = accum.NAMED_ROLE and
87 FIN7.RECVR_PERIOD_TYPE = accum.RECVR_PERIOD_TYPE and
88 FIN7.RECVR_PERIOD_ID = accum.RECVR_PERIOD_ID and
89 FIN7.TXN_CURRENCY_CODE = accum.TXN_CURRENCY_CODE
90 )
91 WHEN MATCHED THEN
92 UPDATE
93 SET
94 accum.TXN_RAW_COST = nvl(accum.TXN_RAW_COST,0) + nvl(FIN7.TXN_RAW_COST,0)
95 ,accum.TXN_BILL_RAW_COST = nvl(accum.TXN_BILL_RAW_COST,0) + nvl(FIN7.TXN_BILL_RAW_COST,0)
96 ,accum.TXN_BRDN_COST = nvl(accum.TXN_BRDN_COST,0) + nvl(FIN7.TXN_BRDN_COST,0)
97 ,accum.TXN_BILL_BRDN_COST = nvl(accum.TXN_BILL_BRDN_COST,0) + nvl(FIN7.TXN_BILL_BRDN_COST,0)
98 ,accum.TXN_REVENUE = nvl(accum.TXN_REVENUE,0) + nvl(FIN7.TXN_REVENUE,0)
99 ,accum.PRJ_RAW_COST = nvl(accum.PRJ_RAW_COST,0) + nvl(FIN7.PRJ_RAW_COST,0)
100 ,accum.PRJ_BILL_RAW_COST = nvl(accum.PRJ_BILL_RAW_COST,0) + nvl(FIN7.PRJ_BILL_RAW_COST,0)
101 ,accum.PRJ_BRDN_COST = nvl(accum.PRJ_BRDN_COST,0) + nvl(FIN7.PRJ_BRDN_COST,0)
102 ,accum.PRJ_BILL_BRDN_COST = nvl(accum.PRJ_BILL_BRDN_COST,0) + nvl(FIN7.PRJ_BILL_BRDN_COST,0)
103 ,accum.PRJ_REVENUE = nvl(accum.PRJ_REVENUE,0) + nvl(FIN7.PRJ_REVENUE,0)
104 ,accum.POU_RAW_COST = nvl(accum.POU_RAW_COST,0) + nvl(FIN7.POU_RAW_COST,0)
105 ,accum.POU_BILL_RAW_COST = nvl(accum.POU_BILL_RAW_COST,0) + nvl(FIN7.POU_BILL_RAW_COST,0)
106 ,accum.POU_BRDN_COST = nvl(accum.POU_BRDN_COST,0) + nvl(FIN7.POU_BRDN_COST,0)
107 ,accum.POU_BILL_BRDN_COST = nvl(accum.POU_BILL_BRDN_COST,0) + nvl(FIN7.POU_BILL_BRDN_COST,0)
108 ,accum.POU_REVENUE = nvl(accum.POU_REVENUE,0) + nvl(FIN7.POU_REVENUE,0)
109 /* ,accum.POU_RAW_COST = nvl(accum.POU_RAW_COST,0) + nvl(FIN7.POU_RAW_COST,0)
110 ,accum.POU_BILL_RAW_COST = nvl(accum.POU_BILL_RAW_COST,0) + nvl(FIN7.POU_BILL_RAW_COST,0)
111 ,accum.POU_BRDN_COST = nvl(accum.POU_BRDN_COST,0) + nvl(FIN7.POU_BRDN_COST,0)
112 ,accum.POU_BILL_BRDN_COST = nvl(accum.POU_BILL_BRDN_COST,0) + nvl(FIN7.POU_BILL_BRDN_COST,0)
113 ,accum.POU_REVENUE = nvl(accum.POU_REVENUE,0) + nvl(FIN7.POU_REVENUE,0) */ -- Satya
114 ,accum.EOU_RAW_COST = nvl(accum.EOU_RAW_COST,0) + nvl(FIN7.EOU_RAW_COST,0)
115 ,accum.EOU_BILL_RAW_COST = nvl(accum.EOU_BILL_RAW_COST,0) + nvl(FIN7.EOU_BILL_RAW_COST,0)
116 ,accum.EOU_BRDN_COST = nvl(accum.EOU_BRDN_COST,0) + nvl(FIN7.EOU_BRDN_COST,0)
117 ,accum.EOU_BILL_BRDN_COST = nvl(accum.EOU_BILL_BRDN_COST,0) + nvl(FIN7.EOU_BILL_BRDN_COST,0)
118 ,accum.G1_RAW_COST = nvl(accum.G1_RAW_COST,0) + nvl(FIN7.G1_RAW_COST,0)
119 ,accum.G1_BILL_RAW_COST = nvl(accum.G1_BILL_RAW_COST,0) + nvl(FIN7.G1_BILL_RAW_COST,0)
120 ,accum.G1_BRDN_COST = nvl(accum.G1_BRDN_COST,0) + nvl(FIN7.G1_BRDN_COST,0)
121 ,accum.G1_BILL_BRDN_COST = nvl(accum.G1_BILL_BRDN_COST,0) + nvl(FIN7.G1_BILL_BRDN_COST,0)
122 ,accum.G1_REVENUE = nvl(accum.G1_REVENUE,0) + nvl(FIN7.G1_REVENUE,0)
123 ,accum.G2_RAW_COST = nvl(accum.G2_RAW_COST,0) + nvl(FIN7.G2_RAW_COST,0)
124 ,accum.G2_BILL_RAW_COST = nvl(accum.G2_BILL_RAW_COST,0) + nvl(FIN7.G2_BILL_RAW_COST,0)
125 ,accum.G2_BRDN_COST = nvl(accum.G2_BRDN_COST,0) + nvl(FIN7.G2_BRDN_COST,0)
126 ,accum.G2_BILL_BRDN_COST = nvl(accum.G2_BILL_BRDN_COST,0) + nvl(FIN7.G2_BILL_BRDN_COST,0)
127 ,accum.G2_REVENUE = nvl(accum.G2_REVENUE,0) + nvl(FIN7.G2_REVENUE,0)
128 ,accum.QUANTITY = nvl(accum.QUANTITY,0) + nvl(FIN7.QUANTITY,0)
129 ,accum.BILL_QUANTITY = nvl(accum.BILL_QUANTITY,0) + nvl(FIN7.BILL_QUANTITY,0)
130 ,accum.LAST_UPDATE_DATE = FIN7.LAST_UPDATE_DATE
131 ,accum.LAST_UPDATED_BY = FIN7.LAST_UPDATED_BY
132 ,accum.LAST_UPDATE_LOGIN = FIN7.LAST_UPDATE_LOGIN
133 WHEN NOT MATCHED THEN
134 INSERT (
135 TXN_ACCUM_HEADER_ID,
136 RESOURCE_CLASS_ID,
137 PROJECT_ID,
138 PROJECT_ORG_ID,
139 PROJECT_ORGANIZATION_ID,
140 PROJECT_TYPE_CLASS,
141 TASK_ID,
142 ASSIGNMENT_ID,
143 NAMED_ROLE,
144 RECVR_PERIOD_TYPE,
145 RECVR_PERIOD_ID,
146 TXN_CURRENCY_CODE,
147 TXN_RAW_COST,
148 TXN_BILL_RAW_COST,
149 TXN_BRDN_COST,
150 TXN_BILL_BRDN_COST,
151 TXN_REVENUE,
152 PRJ_RAW_COST,
153 PRJ_BILL_RAW_COST,
154 PRJ_BRDN_COST,
155 PRJ_BILL_BRDN_COST,
156 PRJ_REVENUE,
157 POU_RAW_COST,
158 POU_BILL_RAW_COST,
159 POU_BRDN_COST,
160 POU_BILL_BRDN_COST,
161 POU_REVENUE,
162 EOU_RAW_COST,
163 EOU_BILL_RAW_COST,
164 EOU_BRDN_COST,
165 EOU_BILL_BRDN_COST,
166 G1_RAW_COST,
167 G1_BILL_RAW_COST,
168 G1_BRDN_COST,
169 G1_BILL_BRDN_COST,
170 G1_REVENUE,
171 G2_RAW_COST,
172 G2_BILL_RAW_COST,
173 G2_BRDN_COST,
174 G2_BILL_BRDN_COST,
175 G2_REVENUE,
176 QUANTITY,
177 BILL_QUANTITY,
178 LAST_UPDATE_DATE,
179 LAST_UPDATED_BY,
180 CREATION_DATE,
181 CREATED_BY,
182 LAST_UPDATE_LOGIN
183 )
184 values
185 (
186 FIN7.TXN_ACCUM_HEADER_ID,
187 FIN7.RESOURCE_CLASS_ID,
188 FIN7.PROJECT_ID,
189 FIN7.PROJECT_ORG_ID,
190 FIN7.PROJECT_ORGANIZATION_ID,
191 FIN7.PROJECT_TYPE_CLASS,
192 FIN7.TASK_ID,
193 FIN7.ASSIGNMENT_ID,
194 FIN7.NAMED_ROLE,
195 FIN7.RECVR_PERIOD_TYPE,
196 FIN7.RECVR_PERIOD_ID,
197 FIN7.TXN_CURRENCY_CODE,
198 FIN7.TXN_RAW_COST,
199 FIN7.TXN_BILL_RAW_COST,
200 FIN7.TXN_BRDN_COST,
201 FIN7.TXN_BILL_BRDN_COST,
202 FIN7.TXN_REVENUE,
203 FIN7.PRJ_RAW_COST,
204 FIN7.PRJ_BILL_RAW_COST,
205 FIN7.PRJ_BRDN_COST,
206 FIN7.PRJ_BILL_BRDN_COST,
207 FIN7.PRJ_REVENUE,
208 FIN7.POU_RAW_COST,
209 FIN7.POU_BILL_RAW_COST,
210 FIN7.POU_BRDN_COST,
211 FIN7.POU_BILL_BRDN_COST,
212 FIN7.POU_REVENUE,
213 FIN7.EOU_RAW_COST,
214 FIN7.EOU_BILL_RAW_COST,
215 FIN7.EOU_BRDN_COST,
216 FIN7.EOU_BILL_BRDN_COST,
217 FIN7.G1_RAW_COST,
218 FIN7.G1_BILL_RAW_COST,
219 FIN7.G1_BRDN_COST,
220 FIN7.G1_BILL_BRDN_COST,
221 FIN7.G1_REVENUE,
222 FIN7.G2_RAW_COST,
223 FIN7.G2_BILL_RAW_COST,
224 FIN7.G2_BRDN_COST,
225 FIN7.G2_BILL_BRDN_COST,
226 FIN7.G2_REVENUE,
227 FIN7.QUANTITY,
228 FIN7.BILL_QUANTITY,
229 FIN7.LAST_UPDATE_DATE,
230 FIN7.LAST_UPDATED_BY,
231 FIN7.CREATION_DATE,
232 FIN7.CREATED_BY,
233 FIN7.LAST_UPDATE_LOGIN );
234
235
236
237 delete
238 from PJI_FP_TXN_ACCUM1 bal
239 where bal.PROJECT_ID = p_project_id;
240
241
242 insert /*+ append parallel(bal_i) */ into PJI_FP_TXN_ACCUM1 bal_i
243 (
244 TXN_ACCUM_HEADER_ID,
245 PROJECT_ID,
246 PROJECT_ORG_ID,
247 PROJECT_ORGANIZATION_ID,
248 TASK_ID,
249 RECVR_PERIOD_TYPE,
250 RECVR_PERIOD_ID,
251 TXN_CURRENCY_CODE,
252 TXN_SUP_INV_COMMITTED_COST,
253 TXN_PO_COMMITTED_COST,
254 TXN_PR_COMMITTED_COST,
255 TXN_OTH_COMMITTED_COST,
256 PRJ_SUP_INV_COMMITTED_COST,
257 PRJ_PO_COMMITTED_COST,
258 PRJ_PR_COMMITTED_COST,
259 PRJ_OTH_COMMITTED_COST,
260 POU_SUP_INV_COMMITTED_COST,
261 POU_PO_COMMITTED_COST,
262 POU_PR_COMMITTED_COST,
263 POU_OTH_COMMITTED_COST,
264 EOU_SUP_INV_COMMITTED_COST,
265 EOU_PO_COMMITTED_COST,
266 EOU_PR_COMMITTED_COST,
267 EOU_OTH_COMMITTED_COST,
268 G1_SUP_INV_COMMITTED_COST,
269 G1_PO_COMMITTED_COST,
270 G1_PR_COMMITTED_COST,
271 G1_OTH_COMMITTED_COST,
272 G2_SUP_INV_COMMITTED_COST,
273 G2_PO_COMMITTED_COST,
274 G2_PR_COMMITTED_COST,
275 G2_OTH_COMMITTED_COST,
276 LAST_UPDATE_DATE,
277 LAST_UPDATED_BY,
278 CREATION_DATE,
279 CREATED_BY,
280 LAST_UPDATE_LOGIN
281 )
282 select
283 tmp7.TXN_ACCUM_HEADER_ID,
284 tmp7.PROJECT_ID,
285 tmp7.PROJECT_ORG_ID,
286 tmp7.PROJECT_ORGANIZATION_ID,
287 tmp7.TASK_ID,
288 tmp7.RECVR_PERIOD_TYPE,
289 tmp7.RECVR_PERIOD_ID,
290 tmp7.TXN_CURRENCY_CODE,
291 sum(tmp7.TXN_SUP_INV_COMMITTED_COST) TXN_SUP_INV_COMMITTED_COST,
292 sum(tmp7.TXN_PO_COMMITTED_COST) TXN_PO_COMMITTED_COST,
293 sum(tmp7.TXN_PR_COMMITTED_COST) TXN_PR_COMMITTED_COST,
294 sum(tmp7.TXN_OTH_COMMITTED_COST) TXN_OTH_COMMITTED_COST,
295 sum(tmp7.PRJ_SUP_INV_COMMITTED_COST) PRJ_SUP_INV_COMMITTED_COST,
296 sum(tmp7.PRJ_PO_COMMITTED_COST) PRJ_PO_COMMITTED_COST,
297 sum(tmp7.PRJ_PR_COMMITTED_COST) PRJ_PR_COMMITTED_COST,
298 sum(tmp7.PRJ_OTH_COMMITTED_COST) PRJ_OTH_COMMITTED_COST,
299 sum(tmp7.POU_SUP_INV_COMMITTED_COST) POU_SUP_INV_COMMITTED_COST,
300 sum(tmp7.POU_PO_COMMITTED_COST) POU_PO_COMMITTED_COST,
301 sum(tmp7.POU_PR_COMMITTED_COST) POU_PR_COMMITTED_COST,
302 sum(tmp7.POU_OTH_COMMITTED_COST) POU_OTH_COMMITTED_COST,
303 sum(tmp7.EOU_SUP_INV_COMMITTED_COST) EOU_SUP_INV_COMMITTED_COST,
304 sum(tmp7.EOU_PO_COMMITTED_COST) EOU_PO_COMMITTED_COST,
305 sum(tmp7.EOU_PR_COMMITTED_COST) EOU_PR_COMMITTED_COST,
306 sum(tmp7.EOU_OTH_COMMITTED_COST) EOU_OTH_COMMITTED_COST,
307 sum(tmp7.G1_SUP_INV_COMMITTED_COST) G1_SUP_INV_COMMITTED_COST,
308 sum(tmp7.G1_PO_COMMITTED_COST) G1_PO_COMMITTED_COST,
309 sum(tmp7.G1_PR_COMMITTED_COST) G1_PR_COMMITTED_COST,
310 sum(tmp7.G1_OTH_COMMITTED_COST) G1_OTH_COMMITTED_COST,
311 sum(tmp7.G2_SUP_INV_COMMITTED_COST) G2_SUP_INV_COMMITTED_COST,
312 sum(tmp7.G2_PO_COMMITTED_COST) G2_PO_COMMITTED_COST,
313 sum(tmp7.G2_PR_COMMITTED_COST) G2_PR_COMMITTED_COST,
314 sum(tmp7.G2_OTH_COMMITTED_COST) G2_OTH_COMMITTED_COST,
315 /* l_last_update_date LAST_UPDATE_DATE,
316 l_last_updated_by LAST_UPDATED_BY,
317 l_creation_date CREATION_DATE,
318 l_created_by CREATED_BY,
319 l_last_update_login LAST_UPDATE_LOGIN */
320 sysdate LAST_UPDATE_DATE,
321 -1 LAST_UPDATED_BY,
322 sysdate CREATION_DATE,
323 -1 CREATED_BY,
324 -1 LAST_UPDATE_LOGIN
325 from
326 PJI_FM_AGGR_FIN7 tmp7
327 where
328 tmp7.PROJECT_ID = p_project_id
329 group by
330 tmp7.TXN_ACCUM_HEADER_ID,
331 tmp7.PROJECT_ID,
332 tmp7.PROJECT_ORG_ID,
333 tmp7.PROJECT_ORGANIZATION_ID,
334 tmp7.TASK_ID,
335 tmp7.RECVR_PERIOD_TYPE,
336 tmp7.RECVR_PERIOD_ID,
337 tmp7.TXN_CURRENCY_CODE;
338
339 delete from PJI_FM_AGGR_FIN7
340 where project_id = p_project_id;
341 end merge_into_fp_txn_accum;
342
343
344 Procedure summarize (
345 errbuf out nocopy varchar2,
346 retcode out nocopy varchar2,
347 p_run_mode in varchar2,
348 p_operating_unit in number default null,
349 p_from_project in varchar2 default null,
350 p_to_project in varchar2 default null,
351 p_rbs_header_id in number default null,
352 p_transaction_type in varchar2 default null
353 )
354 is
355
356 l_worker_id number ;
357
358
359 l_return_status varchar2(255);
360 l_msg_count number;
361 l_msg_data varchar2(2000);
362
363 l_calling_mode varchar2(30);
364
365 Begin
366
367 if (p_run_mode = 'F') then
368 l_calling_mode := 'FULL';
369 elsif (p_run_mode = 'I' or
370 p_run_mode = 'NO_PRELOAD') then
371 l_calling_mode := 'INCREMENTAL';
372 elsif (p_run_mode = 'P') then
373 l_calling_mode := 'PARTIAL';
374 elsif (p_run_mode = 'R') then
375 l_calling_mode := 'RBS';
376 g_rbs_version_id_arr.delete;
377 g_rbs_version_id_index_arr.delete;
378 g_rbs_version_id_rule_arr.delete;
379 else
380 commit;
381 dbms_standard.raise_application_error(-20010, 'Invalid run type');
382 end if;
383
384 l_worker_id := FND_GLOBAL.CONC_REQUEST_ID; -- concurrent_Request_id
385
386 ppr_log('1');
387 If l_calling_mode in ( 'INCREMENTAL' , 'FULL' ) then
388
389
390 insert into pa_pjt_proj_batch
391 (
392 WORKER_ID,
393 PROJECT_ID,
394 PJI_PROJECT_STATUS,
395 EXTRACTION_TYPE,
396 EXTRACTION_STATUS,
397 PROJECT_TYPE,
398 PROJECT_ORG_ID,
399 PROJECT_ORGANIZATION_ID,
400 PROJECT_TYPE_CLASS,
401 PRJ_CURRENCY_CODE,
402 PROJECT_ACTIVE_FLAG
403 )
404 select
405 l_worker_id,
406 prj.PROJECT_ID,
407 null PJI_PROJECT_STATUS,
408 null EXTRACTION_TYPE,
409 NULL,
410 prj.PROJECT_TYPE,
411 prj.ORG_ID PROJECT_ORG_ID,
412 NULL,
413 NULL,
414 prj.PROJECT_CURRENCY_CODE,
415 'Y' PROJECT_ACTIVE_FLAG
416 from
417 PA_PROJECTS_ALL prj
418 where
419 prj.ORG_ID = nvl(p_operating_unit, prj.ORG_ID ) and
420 prj.SEGMENT1 between nvl(p_from_project, prj.SEGMENT1) and
421 nvl(p_to_project, prj.SEGMENT1) AND
422 PA_PROJECT_UTILS.CHECK_PRJ_STUS_ACTION_ALLOWED
423 (prj.PROJECT_STATUS_CODE, 'STATUS_REPORTING') = 'Y'
424 and prj.pjt_rollup_enabled_flag = 'Y' ;
425
426 end if;
427
428 If l_calling_mode = 'RBS' then
429
430 ppr_log('2');
431
432 insert into pa_pjt_events_02
433 (
434 WORKER_ID,
435 LOG_ROWID,
436 EVENT_TYPE,
437 EVENT_ID,
438 EVENT_OBJECT,
439 OPERATION_TYPE,
440 STATUS,
441 ATTRIBUTE_CATEGORY,
442 ATTRIBUTE1,
443 ATTRIBUTE2,
444 ATTRIBUTE3,
445 ATTRIBUTE4,
446 ATTRIBUTE5,
447 ATTRIBUTE6,
448 ATTRIBUTE7,
449 ATTRIBUTE8,
450 ATTRIBUTE9,
451 ATTRIBUTE10,
452 ATTRIBUTE11,
453 ATTRIBUTE12,
454 ATTRIBUTE13,
455 ATTRIBUTE14,
456 ATTRIBUTE15,
457 ATTRIBUTE16,
458 ATTRIBUTE17,
459 ATTRIBUTE18,
460 ATTRIBUTE19,
461 ATTRIBUTE20,
462 LAST_UPDATE_DATE,
463 LAST_UPDATED_BY,
464 CREATION_DATE,
465 CREATED_BY,
466 LAST_UPDATE_LOGIN
467
468 )
469 select /*+ index(log PA_PJI_PROJ_EVENTS_LOG_N1) */
470 l_worker_id ,
471 log.rowid LOG_ROWID,
472 log.EVENT_TYPE,
473 log.EVENT_ID,
474 log.EVENT_OBJECT,
475 log.OPERATION_TYPE,
476 log.STATUS,
477 log.ATTRIBUTE_CATEGORY,
478 log.ATTRIBUTE1,
479 nvl(log.ATTRIBUTE2, log.EVENT_OBJECT) ATTRIBUTE2,
480 log.ATTRIBUTE3,
481 log.ATTRIBUTE4,
482 log.ATTRIBUTE5,
483 log.ATTRIBUTE6,
484 log.ATTRIBUTE7,
485 log.ATTRIBUTE8,
486 log.ATTRIBUTE9,
487 log.ATTRIBUTE10,
488 log.ATTRIBUTE11,
489 log.ATTRIBUTE12,
490 log.ATTRIBUTE13,
491 log.ATTRIBUTE14,
492 log.ATTRIBUTE15,
493 'Y' ATTRIBUTE16, -- project event flag
494 log.ATTRIBUTE17, -- chain identifier
495 log.ATTRIBUTE18, -- push chain flag
496 prj.project_id, -- project id
497 log.ATTRIBUTE20,
498 sysdate LAST_UPDATE_DATE,
499 -1 LAST_UPDATED_BY,
500 sysdate CREATION_DATE,
501 -1 CREATED_BY,
502 -1 LAST_UPDATE_LOGIN
503 from
504 PA_PJI_PROJ_EVENTS_LOG log ,
505 PA_RBS_PRJ_ASSIGNMENTS rbs_asg,
506 PA_PROJECTS_ALL prj
507 where
508 log.EVENT_TYPE in ('RBS_PUSH', 'RBS_DELETE') and
509 rbs_asg.RBS_VERSION_ID in (log.EVENT_OBJECT, log.ATTRIBUTE2) and
510 rbs_asg.RBS_HEADER_ID = p_rbs_header_id and
511 rbs_asg.PROJECT_ID = prj.PROJECT_ID and
512 prj.pjt_rollup_enabled_flag = 'Y' and
513 log.pjt_rollup_flag = 'N'
514 UNION ALL
515 select /*+ index(log PA_PJI_PROJ_EVENTS_LOG_N1) */
516 l_worker_id ,
517 log.rowid LOG_ROWID,
518 log.EVENT_TYPE,
519 log.EVENT_ID,
520 log.EVENT_OBJECT,
521 log.OPERATION_TYPE,
522 log.STATUS,
523 log.ATTRIBUTE_CATEGORY,
524 log.ATTRIBUTE1,
525 nvl(log.ATTRIBUTE2, log.EVENT_OBJECT) ATTRIBUTE2,
526 log.ATTRIBUTE3,
527 log.ATTRIBUTE4,
528 log.ATTRIBUTE5,
529 log.ATTRIBUTE6,
530 log.ATTRIBUTE7,
531 log.ATTRIBUTE8,
532 log.ATTRIBUTE9,
533 log.ATTRIBUTE10,
534 log.ATTRIBUTE11,
535 log.ATTRIBUTE12,
536 log.ATTRIBUTE13,
537 log.ATTRIBUTE14,
538 log.ATTRIBUTE15,
539 'Y' ATTRIBUTE16, -- project event flag
540 log.ATTRIBUTE17, -- chain identifier
541 log.ATTRIBUTE18, -- push chain flag
542 prj.project_id, -- project id
543 log.ATTRIBUTE20 ,
544 sysdate LAST_UPDATE_DATE,
545 -1 LAST_UPDATED_BY,
546 sysdate CREATION_DATE,
547 -1 CREATED_BY,
548 -1 LAST_UPDATE_LOGIN
549 from
550 pa_pjt_events_02 log ,
551 PA_RBS_PRJ_ASSIGNMENTS rbs_asg,
552 PA_PROJECTS_ALL prj
553 where
554 log.EVENT_TYPE in ('RBS_PUSH', 'RBS_DELETE') and
555 rbs_asg.RBS_VERSION_ID in (log.EVENT_OBJECT, log.ATTRIBUTE2) and
556 rbs_asg.RBS_HEADER_ID = p_rbs_header_id and
557 rbs_asg.PROJECT_ID = prj.PROJECT_ID and
558 prj.pjt_rollup_enabled_flag = 'Y' and
559 log.ATTRIBUTE16 = 'N' ;
560
561 ppr_log('events02 '|| SQL%rowcount);
562 begin
563
564 update PA_PJI_PROJ_EVENTS_LOG log
565 set pjt_rollup_flag = 'Y'
566 where log.EVENT_TYPE in ('RBS_PUSH', 'RBS_DELETE')
567 and exists ( select 'x' from
568 PA_RBS_PRJ_ASSIGNMENTS rbs_asg,
569 PA_PROJECTS_ALL prj
570 where
571 rbs_asg.RBS_VERSION_ID in (log.EVENT_OBJECT, log.ATTRIBUTE2) and
572 rbs_asg.RBS_HEADER_ID = p_rbs_header_id and
573 rbs_asg.PROJECT_ID = prj.PROJECT_ID and
574 prj.pjt_rollup_enabled_flag = 'Y' )
575 and log.pjt_rollup_flag = 'N' ;
576
577 delete from pa_pjt_events_02 log
578 where log.EVENT_TYPE in ('RBS_PUSH', 'RBS_DELETE')
579 and log.ATTRIBUTE16 = 'N' ;
580
581
582
583 exception
584 when no_data_found then
585 null;
586 end ;
587
588 insert into pa_pjt_proj_batch
589 (
590 WORKER_ID,
591 PROJECT_ID,
592 PJI_PROJECT_STATUS,
593 EXTRACTION_TYPE,
594 EXTRACTION_STATUS,
595 PROJECT_TYPE,
596 PROJECT_ORG_ID,
597 PROJECT_ORGANIZATION_ID,
598 PROJECT_TYPE_CLASS,
599 PRJ_CURRENCY_CODE,
600 PROJECT_ACTIVE_FLAG
601 )
602 select /*+ ordered
603 index(log, PA_PJI_PROJ_EVENTS_LOG_N1)
604 index(rbs_asg, PA_RBS_PRJ_ASSIGNMENTS_N1) */
605 distinct
606 l_worker_id,
607 rbs_asg.PROJECT_ID,
608 null PJI_PROJECT_STATUS,
609 null EXTRACTION_TYPE,
610 'R' EXTRACTION_STATUS,
611 prj.PROJECT_TYPE,
612 prj.ORG_ID PROJECT_ORG_ID,
613 prj.CARRYING_OUT_ORGANIZATION_ID PROJECT_ORGANIZATION_ID,
614 decode(pt.PROJECT_TYPE_CLASS_CODE,
615 'CAPITAL', 'C',
616 'CONTRACT', 'B',
617 'INDIRECT', 'I') PROJECT_TYPE_CLASS,
618 prj.PROJECT_CURRENCY_CODE,
619 'Y' PROJECT_ACTIVE_FLAG
620 from
621 pa_pjt_events_02 log,
622 PA_RBS_PRJ_ASSIGNMENTS rbs_asg,
623 PA_PROJECTS_ALL prj,
624 PA_PROJECT_TYPES_ALL pt
625 where
626 log.EVENT_TYPE in ('RBS_PUSH', 'RBS_DELETE') and
627 rbs_asg.RBS_VERSION_ID in (log.EVENT_OBJECT, log.ATTRIBUTE2) and
628 rbs_asg.RBS_HEADER_ID = p_rbs_header_id and
629 rbs_asg.PROJECT_ID = prj.PROJECT_ID and
630 prj.PROJECT_TYPE = pt.PROJECT_TYPE
631 and prj.pjt_rollup_enabled_flag = 'Y' ;
632
633 ppr_log('count '||SQL%rowcount);
634
635 end if;
636
637 -- insert into pa_pjt_proj_batch.
638
639 commit;
640
641
642 PJI_TIME_C.LOAD(null, null, l_return_status, l_msg_count, l_msg_data); --PJI_PJP_SUM_ROLLUP.POPULATE_TIME_DIMENSION(l_worker_id);
643
644 PJI_PJP_EXTRACTION_UTILS.POPULATE_ORG_EXTR_INFO;
645
646 run_process(l_worker_id , l_calling_mode );
647
648 End ;
649
650
651 procedure run_process ( p_worker_id number , p_calling_mode varchar2 )
652 is
653
654 cursor c1 ( c_worker_id number ) is
655 select project_id , log.event_type event_type, to_number(log.event_object) rbs_version_id
656 from pa_pjt_proj_batch b1 , pa_pjt_events_02 log --satya -- delete old_rbs_version from fact -- delete from pa_pjt_events and pa_pjt_events_02
657 where p_calling_mode = 'RBS'
658 and b1.worker_id = c_worker_id
659 and b1.project_id = log.ATTRIBUTE19
660 and log.event_type = 'RBS_PUSH'
661 union all
662 select project_id , 'AAA' event_type, -1 rbs_version_id
663 from pa_pjt_proj_batch b1
664 where p_calling_mode IN ( 'FULL','INCREMENTAL' )
665 and b1.worker_id = c_worker_id ;
666
667 cursor c_rbs ( p_project_id number ) is
668 select distinct assign.rbs_version_id , nvl(log.event_type,'AAA') event_type
669 from PA_RBS_PRJ_ASSIGNMENTS ASSIGN, pa_rbs_versions_b rbsv, pa_pjt_events log
670 where ASSIGN.project_id = p_project_id
671 and ASSIGN.reporting_usage_flag = 'Y'
672 AND ASSIGN.rbs_version_id = rbsv.rbs_version_id
673 AND rbsv.current_reporting_flag = 'Y'
674 AND log.attribute1 (+)= ASSIGN.project_id
675 AND log.event_type (+)= 'RBS_ASSOC'
676 AND log.event_object (+) = assign.rbs_version_id;
677 /*
678 union all
679 select prj.rbs_version_id , log.event_type event_type
680 from PA_RBS_PRJ_ASSIGNMENTS prj, pa_pjt_events log, pa_rbs_versions_b rbsv
681 where prj.project_id = p_project_id
682 and log.event_type = 'RBS_ASSOC'
683 and log.attribute1 = prj.project_id
684 and reporting_usage_flag = 'Y'
685 AND prj.rbs_version_id = rbsv.rbs_version_id
686 AND rbsv.current_reporting_flag = 'Y'
687 order by 2;
688 */
689
690 cursor c_rbs_plan ( c_project_id number , c_rbs_version_id number ) is
691 select
692 bv.budget_version_id , fpo.rbs_version_id
693 from
694 pa_budget_versions bv ,
695 pa_proj_fp_options fpo
696 where
697 bv.project_id = fpo.project_id
698 AND bv.fin_plan_type_id is not NULL -- Old budgets model data is not picked up with this condition.
699 AND fpo.project_id = c_project_id
700 AND bv.fin_plan_type_id = fpo.fin_plan_type_id
701 AND bv.budget_version_id = fpo.fin_plan_version_id
702 AND fpo.fin_plan_option_level_code = 'PLAN_VERSION' -- Other values are: plan type and project.
703 AND bv.version_type IN ( 'ALL' , 'COST' , 'REVENUE') -- Eg of other version type is ORG_FORECAST.
704 AND fpo.rbs_version_id = c_rbs_version_id ;
705
706
707 cursor c_plan_full ( c_project_id number ) is
708 select
709 bv.budget_version_id , nvl(fpo.rbs_version_id ,-1) rbs_version_id
710 from
711 pa_budget_versions bv ,
712 pa_proj_fp_options fpo
713 where
714 bv.project_id = fpo.project_id
715 AND bv.fin_plan_type_id is not NULL -- Old budgets model data is not picked up with this condition.
716 AND fpo.project_id = c_project_id
717 AND bv.fin_plan_type_id = fpo.fin_plan_type_id
718 AND bv.budget_version_id = fpo.fin_plan_version_id
719 AND fpo.fin_plan_option_level_code = 'PLAN_VERSION' -- Other values are: plan type and project.
720 AND bv.version_type IN ( 'ALL' , 'COST' , 'REVENUE')
721 order by fpo.rbs_version_id ;-- Eg of other version type is ORG_FORECAST.
722
723
724
725 l_project_id_tbl system.pa_num_tbl_type;
726
727 l_return_status varchar2(255);
728 l_msg_count number;
729 l_msg_data varchar2(2000);
730
731
732 t_calling_mode varchar2(30);
733
734 l_extraction_type_wp varchar2(30);
735
736 l_ret_msg_data varchar2(2000);
737 l_success varchar2(1) := 'Y';
738
739 begin
740
741 ppr_log('3');
742 if ( p_calling_mode = 'RBS' ) then
743 process_rbs_denorm;
744 end if;
745
746 ppr_log('4');
747
748 SELECT DECODE(p_calling_mode, 'INCREMENTAL', 'INCREMENTAL', 'FULL', 'INITIAL', 'FULL')
749 INTO l_extraction_type_wp
750 FROM DUAL;
751
752 l_project_id_tbl := system.pa_num_tbl_type();
753
754 for c1_rec in c1(p_worker_id) loop
755
756 ppr_log('5');
757 create_events (
758 p_worker_id => p_worker_id, --satya
759 p_project_id => c1_rec.project_id ,
760 p_calling_mode => p_calling_mode );
761
762 if ( p_calling_mode = 'RBS' ) then
763
764 process_rbs_changes (
765 p_worker_id => p_worker_id ,
766 p_project_id => c1_rec.project_id ,
767 p_calling_mode => p_calling_mode
768 );
769
770 end if;
771
772 ppr_log('6');
773 l_project_id_tbl.delete;
774
775 l_project_id_tbl.extend(1);
776 l_project_id_tbl(1) := c1_rec.project_id ;
777
778 PA_RBS_ASGMT_PVT.ASSIGN_NEW_VERSION(c1_rec.rbs_version_id, --satya
779 l_project_id_tbl,
780 l_return_status);
781
782 create_mapping_rules (
783 p_worker_id => p_worker_id ,
784 p_project_id => c1_rec.project_id ,
785 p_calling_mode => p_calling_mode
786 ) ;
787
788
789 --- progress calculations
790
791
792 l_project_id_tbl.delete;
793
794 l_project_id_tbl.extend(1);
795 l_project_id_tbl(1) := c1_rec.project_id ;
796
797
798 savepoint S_PROGRESS_ROLLUP;
799
800 POPULATE_FIN8 (p_worker_id => p_worker_id ,
801 p_project_id => c1_rec.project_id,
802 p_calling_mode => p_calling_mode );
803
804 PA_PROGRESS_PUB.GET_SUMMARIZED_ACTUALS(p_project_id_list => l_project_id_tbl,
805 p_extraction_type => l_extraction_type_wp,
806 p_plan_res_level => 'Y',
807 x_return_status => l_return_status,
808 x_msg_count => l_msg_count,
809 x_msg_data => l_msg_data);
810
811
812 if (l_return_status <> FND_API.G_RET_STS_SUCCESS) then
813 FND_MESSAGE.SET_NAME('PA', l_msg_data);
814 l_ret_msg_data := FND_MESSAGE.GET;
815 rollback to S_PROGRESS_ROLLUP;
816 l_success := 'Y';
817 end if;
818
819
820 -- Incremental or full WBS refresh
821
822
823 PA_ppr_rollup_actual_PVT.actual_rollup(
824 p_commit => 'N'
825 ,p_calling_mode => p_calling_mode
826 ,p_fact_slice => 'WBS'
827 ,p_debug_mode => 'N'
828 ,p_project_id => c1_rec.project_id
829 ,p_budget_version_id => -1
830 ,p_rbs_version_id => -1
831 ,p_cbs_version_id => -1
832 ,p_proj_element_id => -1
833 ,p_rbs_element_id => -1
834 ,p_cbs_element_id => -1
835 ,x_return_status => l_return_status
836 ,x_msg_count => l_msg_count
837 ,x_msg_data => l_msg_data
838 ) ;
839
840 --- RBS refresh for INCREMENTAL and FULL mode
841
842 if ( p_calling_mode in ('INCREMENTAL','FULL' ) ) then
843
844 for c_rbs_rec in c_rbs(c1_rec.project_id) loop
845
846 map_rbs_headers (
847 p_worker_id => p_worker_id ,
848 p_project_id => c1_rec.project_id ,
849 p_rbs_version_id => c_rbs_rec.rbs_version_id ,
850 p_event_type => c_rbs_rec.event_type,
851 p_calling_mode => p_calling_mode
852 ) ;
853
854
855 if ( c1_rec.event_type = 'AAA' ) then
856
857 t_calling_mode := p_calling_mode;
858
859 elsif ( c1_rec.event_type = 'RBS_ASSOC' ) then
860
861 t_calling_mode := 'FULL';
862
863 end if;
864
865
866 PA_ppr_rollup_actual_PVT.actual_rollup(
867 p_commit => 'N'
868 ,p_calling_mode => t_calling_mode
869 ,p_fact_slice => 'RBS'
870 ,p_debug_mode => 'N'
871 ,p_project_id => c1_rec.project_id
872 ,p_budget_version_id => -1
873 ,p_rbs_version_id => c_rbs_rec.rbs_version_id
874 ,p_cbs_version_id => -1
875 ,p_proj_element_id => -1
876 ,p_rbs_element_id => -1
877 ,p_cbs_element_id => -1
878 ,x_return_status => l_return_status
879 ,x_msg_count => l_msg_count
880 ,x_msg_data => l_msg_data
881 ) ;
882
883 end loop;
884
885 end if;
886
887 ppr_log('7');
888 if ( p_calling_mode = 'RBS' ) then
889
890 map_rbs_headers (
891 p_worker_id => p_worker_id ,
892 p_project_id => c1_rec.project_id ,
893 p_rbs_version_id => c1_rec.rbs_version_id ,
894 p_event_type => c1_rec.event_type ,
895 p_calling_mode => p_calling_mode
896 ) ;
897
898 PA_ppr_rollup_actual_PVT.actual_rollup(
899 p_commit => 'N'
900 ,p_calling_mode => 'FULL'
901 ,p_fact_slice => 'RBS'
902 ,p_debug_mode => 'N'
903 ,p_project_id => c1_rec.project_id
904 ,p_budget_version_id => -1
905 ,p_rbs_version_id => c1_rec.rbs_version_id
906 ,p_cbs_version_id => -1
907 ,p_proj_element_id => -1
908 ,p_rbs_element_id => -1
909 ,p_cbs_element_id => -1
910 ,x_return_status => l_return_status
911 ,x_msg_count => l_msg_count
912 ,x_msg_data => l_msg_data
913 ) ;
914
915 end if;
916
917
918 if ( p_calling_mode = 'RBS' ) then
919
920 for c_rbs_plan_rec in c_rbs_plan(c1_rec.project_id , c1_rec.rbs_version_id ) loop
921
922 PA_ppr_rollup_PVT.plan_rollup(
923 p_commit => 'N'
924 ,p_calling_mode => 'CREATE'
925 ,p_fact_slice => 'RBS'
926 ,p_debug_mode => 'N'
927 ,p_project_id => c1_rec.project_id
928 ,p_budget_version_id => c_rbs_plan_rec.budget_version_id
929 ,p_rbs_version_id => c1_rec.rbs_version_id
930 ,p_cbs_version_id => -1
931 ,p_proj_element_id => -1
932 ,p_rbs_element_id => -1
933 ,p_cbs_element_id => -1
934 ,x_return_status => l_return_status
935 ,x_msg_count => l_msg_count
936 ,x_msg_data => l_msg_data
937 ) ;
938
939 end loop;
940
941 end if;
942
943
944 if ( p_calling_mode = 'FULL' ) then
945
946 for c_plan_full_rec in c_plan_full(c1_rec.project_id ) loop
947
948 if ( c_plan_full_rec.rbs_version_id > 0 ) then
949
950 PA_ppr_rollup_PVT.plan_rollup(
951 p_commit => 'N'
952 ,p_calling_mode => 'CREATE'
953 ,p_fact_slice => 'RBS'
954 ,p_debug_mode => 'N'
955 ,p_project_id => c1_rec.project_id
956 ,p_budget_version_id => c_plan_full_rec.budget_version_id
957 ,p_rbs_version_id => c_plan_full_rec.rbs_version_id
958 ,p_cbs_version_id => -1
959 ,p_proj_element_id => -1
960 ,p_rbs_element_id => -1
961 ,p_cbs_element_id => -1
962 ,x_return_status => l_return_status
963 ,x_msg_count => l_msg_count
964 ,x_msg_data => l_msg_data
965 ) ;
966
967 end if;
968
969 PA_ppr_rollup_PVT.plan_rollup(
970 p_commit => 'N'
971 ,p_calling_mode => 'CREATE'
972 ,p_fact_slice => 'WBS'
973 ,p_debug_mode => 'N'
974 ,p_project_id => c1_rec.project_id
975 ,p_budget_version_id => c_plan_full_rec.budget_version_id
976 ,p_rbs_version_id => -1
977 ,p_cbs_version_id => -1
978 ,p_proj_element_id => -1
979 ,p_rbs_element_id => -1
980 ,p_cbs_element_id => -1
981 ,x_return_status => l_return_status
982 ,x_msg_count => l_msg_count
983 ,x_msg_data => l_msg_data
984 ) ;
985
986 end loop;
987
988 end if;
989
990
991 PA_PROGRESS_PUB.GET_SUMMARIZED_ACTUALS(p_project_id_list => l_project_id_tbl,
992 p_extraction_type => l_extraction_type_wp,
993 p_plan_res_level => 'N',
994 x_return_status => l_return_status,
995 x_msg_count => l_msg_count,
996 x_msg_data => l_msg_data);
997
998
999 if (l_return_status <> FND_API.G_RET_STS_SUCCESS) then
1000 FND_MESSAGE.SET_NAME('PA', l_msg_data);
1001 l_ret_msg_data := FND_MESSAGE.GET;
1002 rollback to S_PROGRESS_ROLLUP;
1003 l_success := 'Y';
1004 end if;
1005
1006
1007 merge_into_fp_txn_accum(c1_rec.project_id);
1008
1009
1010 end loop;
1011
1012 end ;
1013
1014
1015 procedure process_rbs_denorm
1016 is
1017
1018 cursor c1 is
1019 SELECT object_id ,
1020 SYS_CONNECT_BY_PATH(object_id, '/') connect_path
1021 FROM pa_ppr_obj_tmp r
1022 START WITH r.parent_object_id is null CONNECT BY
1023 PRIOR r.object_id = r.parent_object_id ;
1024
1025 l_object_id_arr PA_PLSQL_DATATYPES.NumTabTyp;
1026 l_connect_path_arr PA_PLSQL_DATATYPES.Char240TabTyp;
1027
1028
1029 begin
1030
1031 For i in 1..g_rbs_version_id_arr.count loop
1032
1033 delete from pa_ppr_obj_tmp2;
1034
1035 insert into pa_ppr_obj_tmp2 ( object_id , parent_object_id )
1036 select rbs_element_id , nvl(parent_element_id,0)
1037 from pa_rbs_elements
1038 where rbs_version_id = g_rbs_version_id_arr(i) ;
1039
1040
1041
1042 OPEN c1;
1043
1044 FETCH c1
1045 BULK COLLECT INTO l_object_id_arr, l_connect_path_arr ;
1046
1047 CLOSE c1;
1048
1049 FORALL k IN 1..l_object_id_arr.COUNT
1050 UPDATE pa_rbs_elements
1051 SET connect_path = l_connect_path_arr(k)
1052 WHERE rbs_element_id = l_object_id_arr(k)
1053 and rbs_version_id = g_rbs_version_id_arr(i);
1054
1055 commit;
1056
1057 end loop;
1058
1059 exception
1060 when others then
1061 raise;
1062 end;
1063
1064
1065 procedure POPULATE_FIN8 (p_worker_id number ,
1066 p_project_id number ,
1067 p_calling_mode varchar2 )
1068 IS
1069
1070 BEGIN
1071
1072 update pa_pjt_proj_batch
1073 set PJI_PROJECT_STATUS = 'Y'
1074 where WORKER_ID = p_worker_id and
1075 PROJECT_ID in (select PROJECT_ID
1076 from PA_PROJECTS_ALL
1077 where STRUCTURE_SHARING_CODE = 'SHARE_FULL');
1078
1079
1080 IF ( p_calling_mode='INCREMENTAL' ) THEN
1081
1082 INSERT INTO pji_fm_aggr_fin8
1083 (
1084 WORKER_ID , RECORD_TYPE , TXN_ACCUM_HEADER_ID ,
1085 RESOURCE_CLASS_ID , PROJECT_ID , PROJECT_ORG_ID ,
1086 PROJECT_ORGANIZATION_ID , PROJECT_TYPE_CLASS , TASK_ID ,
1087 RECVR_PERIOD_TYPE , RECVR_PERIOD_ID , TXN_CURRENCY_CODE ,
1088 TXN_REVENUE , TXN_RAW_COST , TXN_BRDN_COST ,
1089 TXN_BILL_RAW_COST , TXN_BILL_BRDN_COST , TXN_SUP_INV_COMMITTED_COST ,
1090 TXN_PO_COMMITTED_COST , TXN_PR_COMMITTED_COST , TXN_OTH_COMMITTED_COST ,
1091 PRJ_REVENUE , PRJ_RAW_COST , PRJ_BRDN_COST ,
1092 PRJ_BILL_RAW_COST , PRJ_BILL_BRDN_COST , PRJ_REVENUE_WRITEOFF ,
1093 PRJ_SUP_INV_COMMITTED_COST , PRJ_PO_COMMITTED_COST , PRJ_PR_COMMITTED_COST ,
1094 PRJ_OTH_COMMITTED_COST , POU_REVENUE , POU_RAW_COST ,
1095 POU_BRDN_COST , POU_BILL_RAW_COST , POU_BILL_BRDN_COST ,
1096 POU_REVENUE_WRITEOFF , POU_SUP_INV_COMMITTED_COST , POU_PO_COMMITTED_COST ,
1097 POU_PR_COMMITTED_COST , POU_OTH_COMMITTED_COST , EOU_REVENUE ,
1098 EOU_RAW_COST , EOU_BRDN_COST , EOU_BILL_RAW_COST ,
1099 EOU_BILL_BRDN_COST , EOU_SUP_INV_COMMITTED_COST , EOU_PO_COMMITTED_COST ,
1100 EOU_PR_COMMITTED_COST , EOU_OTH_COMMITTED_COST , QUANTITY ,
1101 BILL_QUANTITY , G1_REVENUE , G1_RAW_COST ,
1102 G1_BRDN_COST , G1_BILL_RAW_COST , G1_BILL_BRDN_COST ,
1103 G1_REVENUE_WRITEOFF , G1_SUP_INV_COMMITTED_COST , G1_PO_COMMITTED_COST ,
1104 G1_PR_COMMITTED_COST , G1_OTH_COMMITTED_COST , G2_REVENUE ,
1105 G2_RAW_COST , G2_BRDN_COST , G2_BILL_RAW_COST ,
1106 G2_BILL_BRDN_COST , G2_REVENUE_WRITEOFF , G2_SUP_INV_COMMITTED_COST ,
1107 G2_PO_COMMITTED_COST , G2_PR_COMMITTED_COST , G2_OTH_COMMITTED_COST ,
1108 ASSIGNMENT_ID, NAMED_ROLE --Bug#4590810
1109 )
1110 SELECT
1111 tmp.WORKER_ID , RECORD_TYPE , TXN_ACCUM_HEADER_ID ,
1112 RESOURCE_CLASS_ID , tmp.PROJECT_ID , tmp.PROJECT_ORG_ID ,
1113 tmp.PROJECT_ORGANIZATION_ID , tmp.PROJECT_TYPE_CLASS , TASK_ID ,
1114 RECVR_PERIOD_TYPE , RECVR_PERIOD_ID , TXN_CURRENCY_CODE ,
1115 TXN_REVENUE , TXN_RAW_COST , TXN_BRDN_COST ,
1116 TXN_BILL_RAW_COST , TXN_BILL_BRDN_COST , TXN_SUP_INV_COMMITTED_COST ,
1117 TXN_PO_COMMITTED_COST , TXN_PR_COMMITTED_COST , TXN_OTH_COMMITTED_COST ,
1118 PRJ_REVENUE , PRJ_RAW_COST , PRJ_BRDN_COST ,
1119 PRJ_BILL_RAW_COST , PRJ_BILL_BRDN_COST , PRJ_REVENUE_WRITEOFF ,
1120 PRJ_SUP_INV_COMMITTED_COST , PRJ_PO_COMMITTED_COST , PRJ_PR_COMMITTED_COST ,
1121 PRJ_OTH_COMMITTED_COST , POU_REVENUE , POU_RAW_COST ,
1122 POU_BRDN_COST , POU_BILL_RAW_COST , POU_BILL_BRDN_COST ,
1123 POU_REVENUE_WRITEOFF , POU_SUP_INV_COMMITTED_COST , POU_PO_COMMITTED_COST ,
1124 POU_PR_COMMITTED_COST , POU_OTH_COMMITTED_COST , EOU_REVENUE ,
1125 EOU_RAW_COST , EOU_BRDN_COST , EOU_BILL_RAW_COST ,
1126 EOU_BILL_BRDN_COST , EOU_SUP_INV_COMMITTED_COST , EOU_PO_COMMITTED_COST ,
1127 EOU_PR_COMMITTED_COST , EOU_OTH_COMMITTED_COST , QUANTITY ,
1128 BILL_QUANTITY , G1_REVENUE , G1_RAW_COST ,
1129 G1_BRDN_COST , G1_BILL_RAW_COST , G1_BILL_BRDN_COST ,
1130 G1_REVENUE_WRITEOFF , G1_SUP_INV_COMMITTED_COST , G1_PO_COMMITTED_COST ,
1131 G1_PR_COMMITTED_COST , G1_OTH_COMMITTED_COST , G2_REVENUE ,
1132 G2_RAW_COST , G2_BRDN_COST , G2_BILL_RAW_COST ,
1133 G2_BILL_BRDN_COST , G2_REVENUE_WRITEOFF , G2_SUP_INV_COMMITTED_COST ,
1134 G2_PO_COMMITTED_COST , G2_PR_COMMITTED_COST , G2_OTH_COMMITTED_COST ,
1135 ASSIGNMENT_ID , NAMED_ROLE --Bug#4590810
1136 FROM pji_fm_aggr_fin7 tmp
1137 ,pa_proj_fp_options ppfo
1138 WHERE
1139 tmp.PROJECT_ID= p_project_id AND
1140 ppfo.PROJECT_ID=tmp.PROJECT_ID AND
1141 SUBSTR(tmp.RECVR_PERIOD_TYPE,1,1) = DECODE (ppfo.COST_TIME_PHASED_CODE,'N','G',ppfo.COST_TIME_PHASED_CODE) AND
1142 ppfo.FIN_PLAN_TYPE_ID = (
1143 SELECT fin_plan_type_id
1144 FROM pa_fin_plan_types_b
1145 WHERE use_for_workplan_flag = 'Y'
1146 ) AND
1147 ppfo.FIN_PLAN_OPTION_LEVEL_CODE = 'PLAN_TYPE';
1148
1149 ELSE
1150
1151 INSERT INTO pji_fm_aggr_fin8
1152 (
1153 TXN_ACCUM_HEADER_ID , RESOURCE_CLASS_ID , PROJECT_ID ,
1154 PROJECT_ORG_ID , PROJECT_ORGANIZATION_ID , PROJECT_TYPE_CLASS ,
1155 TASK_ID , RECVR_PERIOD_TYPE , RECVR_PERIOD_ID ,
1156 TXN_CURRENCY_CODE , TXN_REVENUE , TXN_RAW_COST ,
1157 TXN_BRDN_COST , TXN_BILL_RAW_COST , TXN_BILL_BRDN_COST ,
1158 PRJ_REVENUE , PRJ_RAW_COST , PRJ_BRDN_COST ,
1159 PRJ_BILL_RAW_COST , PRJ_BILL_BRDN_COST , POU_REVENUE ,
1160 POU_RAW_COST , POU_BRDN_COST , POU_BILL_RAW_COST ,
1161 POU_BILL_BRDN_COST , EOU_RAW_COST , EOU_BRDN_COST ,
1162 EOU_BILL_RAW_COST , EOU_BILL_BRDN_COST , QUANTITY ,
1163 BILL_QUANTITY , G1_REVENUE , G1_RAW_COST ,
1164 G1_BRDN_COST , G1_BILL_RAW_COST , G1_BILL_BRDN_COST ,
1165 G2_REVENUE , G2_RAW_COST , G2_BRDN_COST ,
1166 G2_BILL_RAW_COST , G2_BILL_BRDN_COST , ASSIGNMENT_ID ,
1167 WORKER_ID , RECORD_TYPE, NAMED_ROLE --Bug#4590810
1168 )
1169 SELECT
1170 TXN_ACCUM_HEADER_ID , RESOURCE_CLASS_ID , tmp.PROJECT_ID ,
1171 tmp.PROJECT_ORG_ID , tmp.PROJECT_ORGANIZATION_ID , tmp.PROJECT_TYPE_CLASS ,
1172 TASK_ID , RECVR_PERIOD_TYPE , RECVR_PERIOD_ID ,
1173 TXN_CURRENCY_CODE , TXN_REVENUE , TXN_RAW_COST ,
1174 TXN_BRDN_COST , TXN_BILL_RAW_COST , TXN_BILL_BRDN_COST ,
1175 PRJ_REVENUE , PRJ_RAW_COST , PRJ_BRDN_COST ,
1176 PRJ_BILL_RAW_COST , PRJ_BILL_BRDN_COST , POU_REVENUE ,
1177 POU_RAW_COST , POU_BRDN_COST , POU_BILL_RAW_COST ,
1178 POU_BILL_BRDN_COST , EOU_RAW_COST , EOU_BRDN_COST ,
1179 EOU_BILL_RAW_COST , EOU_BILL_BRDN_COST , QUANTITY ,
1180 BILL_QUANTITY , G1_REVENUE , G1_RAW_COST ,
1181 G1_BRDN_COST , G1_BILL_RAW_COST , G1_BILL_BRDN_COST ,
1182 G2_REVENUE , G2_RAW_COST , G2_BRDN_COST ,
1183 G2_BILL_RAW_COST , G2_BILL_BRDN_COST , ASSIGNMENT_ID ,
1184 p_worker_id , 'A' , NAMED_ROLE --Bug#4590810
1185 FROM pji_fp_txn_accum tmp
1186 ,pa_proj_fp_options ppfo
1187 WHERE
1188 tmp.PROJECT_ID= p_project_id AND
1189 ppfo.PROJECT_ID=tmp.PROJECT_ID AND
1190 SUBSTR(tmp.RECVR_PERIOD_TYPE,1,1) = DECODE (ppfo.COST_TIME_PHASED_CODE,'N','G',ppfo.COST_TIME_PHASED_CODE) AND
1191 ppfo.FIN_PLAN_TYPE_ID = (
1192 SELECT fin_plan_type_id
1193 FROM pa_fin_plan_types_b
1194 WHERE use_for_workplan_flag = 'Y'
1195 ) AND
1196 ppfo.FIN_PLAN_OPTION_LEVEL_CODE = 'PLAN_TYPE';
1197
1198
1199 END IF;
1200
1201
1202 END ;
1203
1204
1205
1206 procedure map_rbs_headers (
1207 p_worker_id number ,
1208 p_project_id number ,
1209 p_rbs_version_id number,
1210 p_event_type varchar2 ,
1211 p_calling_mode varchar2
1212 ) as
1213
1214 l_return_status varchar2(255);
1215 l_msg_count number;
1216 l_msg_data varchar2(2000);
1217
1218 begin
1219
1220
1221
1222 PA_RBS_MAPPING.MAP_RBS_ACTUALS_new(p_worker_id => p_worker_id,
1223 p_calling_mode => p_calling_mode,
1224 p_project_id => p_project_id ,
1225 p_rbs_version_id => p_rbs_version_id ,
1226 p_event_type => p_event_type ,
1227 x_return_status => l_return_status,
1228 x_msg_count => l_msg_count,
1229 x_msg_data => l_msg_data ) ;
1230
1231 end ;
1232
1233 procedure create_mapping_rules (
1234 p_worker_id number ,
1235 p_project_id number,
1236 p_calling_mode varchar2
1237 ) as
1238
1239
1240 cursor required_rbs (p_worker_id in number) is
1241 select
1242 distinct
1243 asg.RBS_VERSION_ID
1244 from
1245 pa_pjt_proj_batch map,
1246 PA_RBS_PRJ_ASSIGNMENTS asg
1247 where
1248 map.WORKER_ID = p_worker_id and
1249 asg.PROJECT_ID = map.PROJECT_ID and
1250 asg.REPORTING_USAGE_FLAG = 'Y'
1251 and map.project_id = p_project_id ;
1252
1253 l_process varchar2(30);
1254 l_extraction_type varchar2(30);
1255
1256 l_return_status varchar2(255);
1257 l_msg_count number;
1258 l_msg_data varchar2(2000);
1259
1260 l_id number;
1261
1262 begin
1263
1264
1265 for c in required_rbs(p_worker_id) loop
1266
1267 l_id := c.RBS_VERSION_ID;
1268
1269 if g_rbs_version_id_rule_arr.exists(l_id) then
1270
1271 null;
1272
1273 else
1274
1275 g_rbs_version_id_rule_arr(l_id) := 0 ;
1276
1277 begin
1278 PA_RBS_MAPPING.CREATE_MAPPING_RULES(c.RBS_VERSION_ID,
1279 l_return_status,
1280 l_msg_count,
1281 l_msg_data);
1282 exception when others then
1283 PJI_UTILS.WRITE2LOG('CREATE_MAPPING_RULES:' ||
1284 c.RBS_VERSION_ID || ' : ' || SQLERRM);
1285 end;
1286
1287 end if;
1288
1289 end loop;
1290
1291
1292 end CREATE_MAPPING_RULES;
1293
1294 procedure process_rbs_changes (
1295 p_worker_id number ,
1296 p_project_id number ,
1297 p_calling_mode varchar2
1298 ) as
1299
1300
1301 cursor rbs_events (p_worker_id in number) is
1302 select
1303 distinct
1304 ATTRIBUTE20 RBS_HEADER_ID,
1305 ATTRIBUTE2 OLD_RBS_VERSION_ID,
1306 EVENT_OBJECT NEW_RBS_VERSION_ID
1307 from
1308 pa_pjt_events
1309 where
1310 WORKER_ID = p_worker_id and
1311 EVENT_TYPE = 'RBS_PUSH' AND
1312 attribute19 = p_project_id;
1313
1314 l_process varchar2(30);
1315 l_extraction_type varchar2(30);
1316
1317 x_return_status varchar2(255);
1318 x_msg_count number;
1319 x_msg_data varchar2(2000);
1320
1321 p_rbs_header_id number;
1322 p_new_rbs_version_id number;
1323 p_old_rbs_version_id number;
1324
1325
1326 begin
1327
1328 ppr_log('process_rbs_changes1');
1329 for c in rbs_events(p_worker_id) loop
1330 ppr_log('process_rbs_changes2 '||c.NEW_RBS_VERSION_ID);
1331 ppr_log('process_rbs_changes2 '||c.OLD_RBS_VERSION_ID);
1332 p_rbs_header_id := c.RBS_HEADER_ID;
1333 p_new_rbs_version_id := c.NEW_RBS_VERSION_ID;
1334 p_old_rbs_version_id := c.OLD_RBS_VERSION_ID;
1335
1336 PA_ppr_rollup_PVT.rbs_denorm(c.NEW_RBS_VERSION_ID);
1337 --Initialize return status
1338 x_return_status := FND_API.G_RET_STS_SUCCESS;
1339
1340 --Call to RBS handler API
1341 PA_RBS_VERSIONS_PVT.SET_REPORTING_FLAG (
1342 p_rbs_version_id => p_new_rbs_version_id,
1343 x_return_status => x_return_status );
1344
1345 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1346 pa_debug.log_message ('Error in API PA_RBS_VERSIONS_PVT.SET_REPORTING_FLAG' || SQLERRM);
1347 --RETURN;
1348 END IF;
1349
1350 --Call to Allocations handler API
1351 PA_ALLOC_UTILS.ASSOCIATE_RBS_TO_ALLOC_RULE(
1352 p_rbs_header_id => p_rbs_header_id,
1353 p_rbs_version_id => p_new_rbs_version_id,
1354 x_return_status => x_return_status,
1355 x_error_code => x_msg_data );
1356
1357 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1358 pa_debug.log_message ('Error in API PA_ALLOC_UTILS.ASSOCIATE_RBS_TO_ALLOC_RULE' || SQLERRM);
1359 --RETURN;
1360 END IF;
1361
1362 --Call to Budgeting and Forecasting handler API
1363
1364
1365 -- SRI
1366
1367 /*insert into PJI_PJP_PROJ_BATCH_MAP
1368 (
1369 WORKER_ID,
1370 PROJECT_ID,
1371 PJI_PROJECT_STATUS,
1372 EXTRACTION_TYPE,
1373 EXTRACTION_STATUS,
1374 PROJECT_TYPE,
1375 PROJECT_ORG_ID,
1376 PROJECT_ORGANIZATION_ID,
1377 PROJECT_TYPE_CLASS,
1378 PRJ_CURRENCY_CODE,
1379 PROJECT_ACTIVE_FLAG
1380 )
1381 select
1382 1,
1383 PROJECT_ID,
1384 PJI_PROJECT_STATUS,
1385 EXTRACTION_TYPE,
1386 EXTRACTION_STATUS,
1387 PROJECT_TYPE,
1388 PROJECT_ORG_ID,
1389 PROJECT_ORGANIZATION_ID,
1390 PROJECT_TYPE_CLASS,
1391 PRJ_CURRENCY_CODE,
1392 PROJECT_ACTIVE_FLAG
1393 from pa_pjt_proj_batch
1394 where worker_id = p_worker_id and
1395 project_id = p_project_id;
1396
1397 PJI_PJP_EXTRACTION_UTILS.SET_WORKER_ID(p_worker_id);
1398
1399 PJI_PROCESS_UTIL.SET_PROCESS_PARAMETER
1400 (PJI_PJP_SUM_MAIN.g_process || to_char(p_worker_id), 'RBS_HEADER_ID', nvl(p_rbs_header_id, -1)); */
1401
1402 -- populate pji_pjp_proj_batch_map
1403 -- set the following variables
1404 -- l_worker_id := PJI_PJP_EXTRACTION_UTILS.GET_WORKER_ID;
1405 -- l_process := PJI_PJP_SUM_MAIN.g_process || to_char(l_worker_id);
1406 -- l_rbs_header_id := PJI_PROCESS_UTIL.GET_PROCESS_PARAMETER(l_process,'RBS_HEADER_ID');
1407
1408 PA_ppr_concurrent_program.g_pjt_rollup_flag := 'Y';
1409 PA_ppr_concurrent_program.g_project_id := p_project_id;
1410 PA_ppr_concurrent_program.g_rbs_header_id := p_rbs_header_id;
1411
1412 PA_RLMI_RBS_MAP_PUB.PUSH_RBS_VERSION (
1413 p_old_rbs_version_id => p_old_rbs_version_id,
1414 p_new_rbs_version_id => p_new_rbs_version_id,
1415 x_return_status => x_return_status,
1416 x_msg_count => x_msg_count,
1417 x_msg_data => x_msg_data );
1418
1419 PA_ppr_concurrent_program.g_pjt_rollup_flag := 'N';
1420 PA_ppr_concurrent_program.g_project_id := null;
1421 PA_ppr_concurrent_program.g_rbs_header_id := null ;
1422
1423
1424 -- DELETE FROM pji_pjp_proj_batch_map
1425
1426 /* delete from PJI_PJP_PROJ_BATCH_MAP
1427 where worker_id = p_worker_id and
1428 project_id = p_project_id; */
1429
1430 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1431 pa_debug.log_message ('Error in API PA_RLMI_RBS_MAP_PUB.PUSH_RBS_VERSION' || SQLERRM);
1432 --RETURN;
1433 END IF;
1434
1435
1436 end loop;
1437
1438 end process_rbs_changes;
1439
1440 procedure create_events (
1441 p_worker_id number, -- satya
1442 p_project_id number ,
1443 p_calling_mode varchar2
1444 ) as
1445
1446 cursor events (p_worker_id in number) is
1447 select /*+ ordered index(log PA_PJI_PROJ_EVENTS_LOG_N1) use_hash(log) */ --Bug 7516507
1448 distinct
1449 log.ROWID LOG_ROWID,
1450 log.EVENT_TYPE,
1451 log.EVENT_ID,
1452 log.EVENT_OBJECT,
1453 log.OPERATION_TYPE,
1454 log.STATUS,
1455 log.ATTRIBUTE_CATEGORY,
1456 log.ATTRIBUTE1,
1457 to_char(decode(log.EVENT_TYPE,
1458 'RBS_PRG', ver.RBS_HEADER_ID,
1459 'RBS_ASSOC', ver.RBS_HEADER_ID,
1460 log.ATTRIBUTE2)) ATTRIBUTE2,
1461 log.ATTRIBUTE3,
1462 log.ATTRIBUTE4,
1463 log.ATTRIBUTE5,
1464 log.ATTRIBUTE6,
1465 log.ATTRIBUTE7,
1466 log.ATTRIBUTE8,
1467 log.ATTRIBUTE9,
1468 log.ATTRIBUTE10,
1469 log.ATTRIBUTE11,
1470 log.ATTRIBUTE12,
1471 log.ATTRIBUTE13,
1472 log.ATTRIBUTE14,
1473 log.ATTRIBUTE15,
1474 log.ATTRIBUTE16,
1475 log.ATTRIBUTE17,
1476 log.ATTRIBUTE18,
1477 log.ATTRIBUTE19,
1478 log.ATTRIBUTE20
1479 from
1480 PA_PJI_PROJ_EVENTS_LOG log,
1481 PA_RBS_VERSIONS_B ver
1482 where
1483 -- map.WORKER_ID = p_worker_id and
1484 log.EVENT_TYPE in (-- 'WBS_CHANGE', -- disable bulk processing
1485 -- 'WBS_PUBLISH', -- of WBS events
1486 'RBS_ASSOC'
1487 ---- ,'RBS_PRG' -- OLAP OPEN ITEM : to support programs with new model. Need to resolve this.
1488 ) and
1489 log.ATTRIBUTE1 = p_project_id and
1490 log.EVENT_OBJECT = ver.RBS_VERSION_ID (+) and
1491 p_calling_mode = 'INCREMENTAL'
1492 union all
1493 select /*+ ordered index(log
1494 PA_PJI_PROJ_EVENTS_LOG_N1) use_hash(log) */ --Bug 7516507
1495 distinct
1496 log.ROWID LOG_ROWID,
1497 log.EVENT_TYPE,
1498 log.EVENT_ID,
1499 log.EVENT_OBJECT,
1500 log.OPERATION_TYPE,
1501 log.STATUS,
1502 log.ATTRIBUTE_CATEGORY,
1503 log.ATTRIBUTE1,
1504 log.ATTRIBUTE2,
1505 log.ATTRIBUTE3,
1506 log.ATTRIBUTE4,
1507 log.ATTRIBUTE5,
1508 log.ATTRIBUTE6,
1509 log.ATTRIBUTE7,
1510 log.ATTRIBUTE8,
1511 log.ATTRIBUTE9,
1512 log.ATTRIBUTE10,
1513 log.ATTRIBUTE11,
1514 log.ATTRIBUTE12,
1515 log.ATTRIBUTE13,
1516 log.ATTRIBUTE14,
1517 log.ATTRIBUTE15,
1518 log.ATTRIBUTE16,
1519 log.ATTRIBUTE17,
1520 log.ATTRIBUTE18,
1521 log.ATTRIBUTE19,
1522 log.ATTRIBUTE20
1523 from
1524 -- pa_pjt_proj_batch map,
1525 PA_PJI_PROJ_EVENTS_LOG log
1526 where
1527 1 = 2 AND --- OLAP OPEN ITEM : to support programs with new model. Need to resolve this,
1528 -- map.WORKER_ID = p_worker_id and
1529 log.EVENT_TYPE = 'PRG_CHANGE' and
1530 log.EVENT_OBJECT = -1 and
1531 log.ATTRIBUTE1 = p_project_id;
1532
1533
1534
1535 cursor pjt_events_02 is
1536 select /*+ index(log PA_PJI_PROJ_EVENTS_LOG_N1) */
1537 distinct
1538 log.ROWID LOG_ROWID,
1539 log.EVENT_TYPE,
1540 log.EVENT_ID,
1541 log.EVENT_OBJECT,
1542 log.OPERATION_TYPE,
1543 log.STATUS,
1544 log.ATTRIBUTE_CATEGORY,
1545 log.ATTRIBUTE1,
1546 nvl(log.ATTRIBUTE2, log.EVENT_OBJECT) ATTRIBUTE2,
1547 log.ATTRIBUTE3,
1548 log.ATTRIBUTE4,
1549 log.ATTRIBUTE5,
1550 log.ATTRIBUTE6,
1551 log.ATTRIBUTE7,
1552 log.ATTRIBUTE8,
1553 log.ATTRIBUTE9,
1554 log.ATTRIBUTE10,
1555 log.ATTRIBUTE11,
1556 log.ATTRIBUTE12,
1557 log.ATTRIBUTE13,
1558 log.ATTRIBUTE14,
1559 log.ATTRIBUTE15,
1560 'N' ATTRIBUTE16, -- project event flag
1561 log.ATTRIBUTE17, -- chain identifier
1562 log.ATTRIBUTE18, -- push chain flag
1563 log.ATTRIBUTE19, -- project id
1564 ver.RBS_HEADER_ID ATTRIBUTE20 -- rbs header
1565 from
1566 pa_pjt_events_02 log,
1567 (
1568 select
1569 distinct
1570 asg.RBS_HEADER_ID,
1571 asg.RBS_VERSION_ID
1572 from
1573 -- pa_pjt_proj_batch map,
1574 PA_RBS_PRJ_ASSIGNMENTS asg
1575 where
1576 -- map.WORKER_ID = p_worker_id and
1577 asg.PROJECT_ID = p_project_id
1578 ) ver
1579 where
1580 log.EVENT_TYPE IN ( 'RBS_PUSH' , 'RBS_DELETE' ) and
1581 ver.RBS_VERSION_ID in (log.EVENT_OBJECT, log.ATTRIBUTE2) AND
1582 log.attribute19 = p_project_id ;
1583
1584 i number;
1585
1586 begin
1587
1588 ppr_log('In create event');
1589 if ( p_calling_mode = 'INCREMENTAL' ) then
1590
1591 for c in events(p_worker_id) loop -- satya (removed p_project_id and added p_worker_id
1592
1593 insert into pa_pjt_events
1594 (
1595 WORKER_ID,
1596 LOG_ROWID,
1597 EVENT_TYPE,
1598 EVENT_ID,
1599 EVENT_OBJECT,
1600 OPERATION_TYPE,
1601 STATUS,
1602 ATTRIBUTE_CATEGORY,
1603 ATTRIBUTE1,
1604 ATTRIBUTE2,
1605 ATTRIBUTE3,
1606 ATTRIBUTE4,
1607 ATTRIBUTE5,
1608 ATTRIBUTE6,
1609 ATTRIBUTE7,
1610 ATTRIBUTE8,
1611 ATTRIBUTE9,
1612 ATTRIBUTE10,
1613 ATTRIBUTE11,
1614 ATTRIBUTE12,
1615 ATTRIBUTE13,
1616 ATTRIBUTE14,
1617 ATTRIBUTE15,
1618 ATTRIBUTE16,
1619 ATTRIBUTE17,
1620 ATTRIBUTE18,
1621 ATTRIBUTE19,
1622 ATTRIBUTE20,
1623 LAST_UPDATE_DATE,
1624 LAST_UPDATED_BY,
1625 CREATION_DATE,
1626 CREATED_BY,
1627 LAST_UPDATE_LOGIN
1628 )
1629 values
1630 (
1631 p_worker_id, -- satya
1632 c.LOG_ROWID,
1633 c.EVENT_TYPE,
1634 c.EVENT_ID,
1635 c.EVENT_OBJECT,
1636 c.OPERATION_TYPE,
1637 c.STATUS,
1638 c.ATTRIBUTE_CATEGORY,
1639 c.ATTRIBUTE1,
1640 c.ATTRIBUTE2,
1641 c.ATTRIBUTE3,
1642 c.ATTRIBUTE4,
1643 c.ATTRIBUTE5,
1644 c.ATTRIBUTE6,
1645 c.ATTRIBUTE7,
1646 c.ATTRIBUTE8,
1647 c.ATTRIBUTE9,
1648 c.ATTRIBUTE10,
1649 c.ATTRIBUTE11,
1650 c.ATTRIBUTE12,
1651 c.ATTRIBUTE13,
1652 c.ATTRIBUTE14,
1653 c.ATTRIBUTE15,
1654 c.ATTRIBUTE16,
1655 c.ATTRIBUTE17,
1656 c.ATTRIBUTE18,
1657 c.ATTRIBUTE19,
1658 c.ATTRIBUTE20,
1659 sysdate ,
1660 -1 ,
1661 sysdate ,
1662 -1 ,
1663 -1
1664 );
1665
1666 delete
1667 from PA_PJI_PROJ_EVENTS_LOG a
1668 where ROWID = c.LOG_ROWID;
1669
1670 end loop;
1671
1672 elsif ( p_calling_mode = 'RBS' ) then
1673
1674 i:= 0;
1675 for c in pjt_events_02 loop
1676
1677 insert into pa_pjt_events
1678 (
1679 WORKER_ID,
1680 LOG_ROWID,
1681 EVENT_TYPE,
1682 EVENT_ID,
1683 EVENT_OBJECT,
1684 OPERATION_TYPE,
1685 STATUS,
1686 ATTRIBUTE_CATEGORY,
1687 ATTRIBUTE1,
1688 ATTRIBUTE2,
1689 ATTRIBUTE3,
1690 ATTRIBUTE4,
1691 ATTRIBUTE5,
1692 ATTRIBUTE6,
1693 ATTRIBUTE7,
1694 ATTRIBUTE8,
1695 ATTRIBUTE9,
1696 ATTRIBUTE10,
1697 ATTRIBUTE11,
1698 ATTRIBUTE12,
1699 ATTRIBUTE13,
1700 ATTRIBUTE14,
1701 ATTRIBUTE15,
1702 ATTRIBUTE16,
1703 ATTRIBUTE17,
1704 ATTRIBUTE18,
1705 ATTRIBUTE19,
1706 ATTRIBUTE20,
1707 LAST_UPDATE_DATE,
1708 LAST_UPDATED_BY,
1709 CREATION_DATE,
1710 CREATED_BY,
1711 LAST_UPDATE_LOGIN
1712 )
1713 values
1714 (
1715 p_worker_id, --satya
1716 c.LOG_ROWID,
1717 c.EVENT_TYPE,
1718 c.EVENT_ID,
1719 c.EVENT_OBJECT,
1720 c.OPERATION_TYPE,
1721 c.STATUS,
1722 c.ATTRIBUTE_CATEGORY,
1723 c.ATTRIBUTE1,
1724 c.ATTRIBUTE2,
1725 c.ATTRIBUTE3,
1726 c.ATTRIBUTE4,
1727 c.ATTRIBUTE5,
1728 c.ATTRIBUTE6,
1729 c.ATTRIBUTE7,
1730 c.ATTRIBUTE8,
1731 c.ATTRIBUTE9,
1732 c.ATTRIBUTE10,
1733 c.ATTRIBUTE11,
1734 c.ATTRIBUTE12,
1735 c.ATTRIBUTE13,
1736 c.ATTRIBUTE14,
1737 c.ATTRIBUTE15,
1738 c.ATTRIBUTE16,
1739 c.ATTRIBUTE17,
1740 c.ATTRIBUTE18,
1741 c.ATTRIBUTE19,
1742 c.ATTRIBUTE20,
1743 sysdate ,
1744 -1 ,
1745 sysdate ,
1746 -1 ,
1747 -1
1748 );
1749
1750 ppr_log('event '|| SQL%rowcount);
1751 if ( g_rbs_version_id_index_arr.exists(c.EVENT_OBJECT) ) then
1752 null;
1753 else
1754 i := i + 1;
1755 g_rbs_version_id_arr(i) := c.EVENT_OBJECT;
1756 g_rbs_version_id_index_arr(c.EVENT_OBJECT) := 0 ;
1757
1758 end if;
1759 delete
1760 from pa_pjt_events_02 a
1761 where ROWID = c.LOG_ROWID;
1762
1763 end loop;
1764
1765 end if;
1766
1767 commit;
1768
1769 end create_events ;
1770
1771
1772
1773 End PA_ppr_concurrent_program;