[Home] [Help]
PACKAGE BODY: APPS.PA_RP_UTIL
Source
1 PACKAGE BODY Pa_Rp_Util AS
2 /* $Header: PARPUTILB.pls 120.7 2007/02/26 16:44:31 pschandr noship $ */
3 g_debug_mode VARCHAR2(1) := NVL(Fnd_Profile.value('PA_DEBUG_MODE'),'N');
4 g_proc NUMBER :=5;
5
6 PROCEDURE Assign_Job(p_main_request_id NUMBER
7 , p_worker_request_id NUMBER
8 , p_previous_succeed VARCHAR
9 , x_job_assigned OUT NOCOPY VARCHAR2
10 , x_bursting_values OUT NOCOPY SYSTEM.PA_VARCHAR2_240_TBL_TYPE
11 , x_return_status IN OUT NOCOPY VARCHAR2
12 , x_msg_count IN OUT NOCOPY NUMBER
13 , x_msg_data IN OUT NOCOPY VARCHAR2) IS
14 BEGIN
15 IF g_debug_mode = 'Y' THEN
16 Pji_Utils.WRITE2LOG( 'PA_RP_UTIL.Assign_Job: beginning', TRUE , g_proc);
17 END IF;
18
19 IF x_return_status IS NULL THEN
20 x_msg_count := 0;
21 x_return_status := Fnd_Api.G_RET_STS_SUCCESS;
22 END IF;
23
24
25 UPDATE pa_rp_job_assignments
26 SET status_flag = p_previous_succeed
27 WHERE status_flag = 'P'
28 AND main_request_id = p_main_request_id
29 AND worker_request_id = p_worker_request_id;
30
31 x_bursting_values := SYSTEM.PA_VARCHAR2_240_TBL_TYPE();
32 x_bursting_values.extend(3);
33
34 UPDATE pa_rp_job_assignments
35 SET status_flag ='P', worker_request_id = p_worker_request_id
36 WHERE status_flag = 'C' AND ROWNUM=1 AND main_request_id = p_main_request_id
37 RETURN bursting_value_1, bursting_value_2, bursting_value_3
38 INTO x_bursting_values(1), x_bursting_values(2), x_bursting_values(3);
39
40 IF (SQL%rowcount <> 0) THEN
41 x_job_assigned := 'Y';
42 ELSE
43 x_job_assigned := 'N';
44 END IF;
45
46 COMMIT;
47
48 IF g_debug_mode = 'Y' THEN
49 Pji_Utils.WRITE2LOG( 'PA_RP_UTIL.Assign_Job: finishing', TRUE , g_proc);
50 END IF;
51
52 EXCEPTION
53 WHEN OTHERS THEN
54 Add_Message(p_app_short_name=> 'PA',p_msg_name=> 'PA_RP_GENERIC_MSG',p_msg_type=>Pa_Rp_Util.G_RET_STS_ERROR,p_token1=>'PROC_NAME',p_token1_value=>'PA_RP_UTIL.Assign_Job');
55 RAISE;
56 END Assign_Job;
57
58 PROCEDURE Is_DT_Trimmed (p_rp_id NUMBER
59 , p_app_short_name VARCHAR2
60 , x_is_dt_trimmed OUT NOCOPY VARCHAR2
61 , x_return_status IN OUT NOCOPY VARCHAR2
62 , x_msg_count IN OUT NOCOPY NUMBER
63 , x_msg_data IN OUT NOCOPY VARCHAR2) IS
64 l_count NUMBER;
65 BEGIN
66 IF g_debug_mode = 'Y' THEN
67 Pji_Utils.WRITE2LOG( 'PA_RP_UTIL.Is_DT_Trimmed: beginning', TRUE , g_proc);
68 END IF;
69
70 IF x_return_status IS NULL THEN
71 x_msg_count := 0;
72 x_return_status := Fnd_Api.G_RET_STS_SUCCESS;
73 END IF;
74
75 x_is_dt_trimmed := 'N';
76
77 SELECT COUNT(*)
78 INTO l_count
79 FROM
80 PA_RP_Definitions_B rp, PA_RP_Template_Lists templates, XDO_LOBS xdo
81 WHERE rp.rp_Id = p_rp_id
82 AND rp.rp_Id = templates.rp_Id
83 AND templates.template_code = xdo.LOB_CODE
84 AND xdo.application_short_name = p_app_short_name
85 AND xdo.LOB_TYPE = 'TEMPLATE'
86 AND (rp.dt_process_date is null or rp.dt_process_date < xdo.last_update_date OR templates.dt_process_flag='N')
87 AND ROWNUM = 1;
88
89 IF l_count = 0 THEN
90 SELECT COUNT(*)
91 INTO l_count
92 FROM
93 PA_RP_Definitions_B rp, PA_RP_TYPES_b TYPES, XDO_LOBS xdo
94 WHERE rp.rp_Id = p_rp_id
95 AND rp.rp_type_Id = types.rp_type_Id
96 AND types.seeded_dt_code = xdo.LOB_CODE
97 AND xdo.application_short_name = p_app_short_name
98 AND xdo.LOB_TYPE = 'DATA_TEMPLATE'
99 AND rp.dt_process_date < xdo.last_update_date
100 AND ROWNUM = 1;
101
102 IF l_count = 0 THEN
103 x_is_dt_trimmed := 'Y';
104 END IF;
105 END IF;
106
107 IF g_debug_mode = 'Y' THEN
108 Pji_Utils.WRITE2LOG( 'PA_RP_UTIL.Is_DT_Trimmed: finishing', TRUE , g_proc);
109 END IF;
110
111 EXCEPTION
112 WHEN OTHERS THEN
113 Add_Message(p_app_short_name=> 'PA',p_msg_name=> 'PA_RP_GENERIC_MSG',p_msg_type=>Pa_Rp_Util.G_RET_STS_ERROR,p_token1=>'PROC_NAME',p_token1_value=>'PA_RP_UTIL.Assign_Job');
114 RAISE;
115 END Is_DT_Trimmed;
116
117 PROCEDURE Save_Trimmed_DT (p_rp_id NUMBER
118 , x_trimmed_dt OUT NOCOPY BLOB
119 , x_return_status IN OUT NOCOPY VARCHAR2
120 , x_msg_count IN OUT NOCOPY NUMBER
121 , x_msg_data IN OUT NOCOPY VARCHAR2) IS
122
123 BEGIN
124 IF g_debug_mode = 'Y' THEN
125 Pji_Utils.WRITE2LOG( 'PA_RP_UTIL.Is_DT_Trimmed: beginning', TRUE , g_proc);
126 END IF;
127
128 IF x_return_status IS NULL THEN
129 x_msg_count := 0;
130 x_return_status := Fnd_Api.G_RET_STS_SUCCESS;
131 END IF;
132
133 UPDATE PA_RP_DEFINITIONS_B
134 SET dt_process_date = SYSDATE
135 WHERE rp_id = p_rp_id;
136
137 SELECT RP_FILE_DATA
138 INTO x_trimmed_dt
139 FROM PA_RP_LOBS lobs
140 WHERE lobs.rp_id = p_rp_id
141 AND lobs.lob_type = 'DT'
142 FOR UPDATE;
143
144 UPDATE pa_rp_template_lists
145 SET DT_Process_flag = 'Y'
146 WHERE rp_id = p_rp_id;
147 /*
148 SELECT trimmed_dt
149 INTO x_trimmed_dt
150 FROM PA_RP_DEFINITIONS_B
151 WHERE rp_id = p_rp_id
152 FOR UPDATE;
153 */
154 IF g_debug_mode = 'Y' THEN
155 Pji_Utils.WRITE2LOG( 'PA_RP_UTIL.Is_DT_Trimmed: finishing', TRUE , g_proc);
156 END IF;
157
158 EXCEPTION
159 WHEN OTHERS THEN
160 Add_Message(p_app_short_name=> 'PA',p_msg_name=> 'PA_RP_GENERIC_MSG',p_msg_type=>Pa_Rp_Util.G_RET_STS_ERROR,p_token1=>'PROC_NAME',p_token1_value=>'PA_RP_UTIL.Assign_Job');
161 ROLLBACK;
162 RAISE;
163 END Save_Trimmed_DT;
164
165
166 PROCEDURE Add_Message (p_app_short_name VARCHAR2
167 , p_msg_name VARCHAR2
168 , p_msg_type VARCHAR2
169 , p_token1 VARCHAR2 DEFAULT NULL
170 , p_token1_value VARCHAR2 DEFAULT NULL
171 , p_token2 VARCHAR2 DEFAULT NULL
172 , p_token2_value VARCHAR2 DEFAULT NULL
173 , p_token3 VARCHAR2 DEFAULT NULL
174 , p_token3_value VARCHAR2 DEFAULT NULL
175 , p_token4 VARCHAR2 DEFAULT NULL
176 , p_token4_value VARCHAR2 DEFAULT NULL
177 , p_token5 VARCHAR2 DEFAULT NULL
178 , p_token5_value VARCHAR2 DEFAULT NULL
179 )
180 IS
181 BEGIN
182 Fnd_Message.set_name(p_app_short_name, p_msg_name);
183 -- Fnd_Msg_Pub.ADD;
184 IF p_token1 IS NOT NULL THEN
185 Fnd_Message.set_token(p_token1, p_token1_value);
186 END IF;
187
188 IF p_token2 IS NOT NULL THEN
189 Fnd_Message.set_token(p_token2, p_token2_value);
190 END IF;
191
192 IF p_token3 IS NOT NULL THEN
193 Fnd_Message.set_token(p_token3, p_token3_value);
194 END IF;
195
196 IF p_token4 IS NOT NULL THEN
197 Fnd_Message.set_token(p_token4, p_token4_value);
198 END IF;
199
200 IF p_token5 IS NOT NULL THEN
201 Fnd_Message.set_token(p_token5, p_token5_value);
202 END IF;
203 Fnd_Msg_Pub.add_detail(p_message_type=>p_msg_type);
204 EXCEPTION
205 WHEN OTHERS THEN
206 Fnd_Message.set_name('PA','PA_RP_GENERIC_MSG');
207 Fnd_Message.set_token('PROC_NAME','PA_RP_UTILS.Add_Message');
208 END Add_Message;
209
210
211 PROCEDURE Start_Workers (p_request_id NUMBER
212 ,p_rp_id NUMBER
213 , x_worker_request_ids OUT NOCOPY SYSTEM.PA_NUM_TBL_TYPE
214 , x_return_status IN OUT NOCOPY VARCHAR
215 , x_msg_count IN OUT NOCOPY NUMBER
216 , x_msg_data IN OUT NOCOPY VARCHAR2) IS
217 l_worker_number NUMBER;
218 l_i NUMBER;
219 BEGIN
220 IF g_debug_mode = 'Y' THEN
221 Pji_Utils.WRITE2LOG( 'PA_RP_UTIL.Start_Workers: beginning', TRUE , g_proc);
222 END IF;
223
224 IF x_return_status IS NULL THEN
225 x_msg_count := 0;
226 x_return_status := Fnd_Api.G_RET_STS_SUCCESS;
227 END IF;
228
229
230 l_worker_number := TO_NUMBER(FND_PROFILE.VALUE('PA_RP_WORKER_QUANTITY')); -- this should be read from profile option
231
232 x_worker_request_ids := SYSTEM.PA_NUM_TBL_TYPE();
233 x_worker_request_ids.extend(l_worker_number);
234
235 FOR l_i IN 1..l_worker_number LOOP
236 x_worker_request_ids(l_i):=Fnd_Request.submit_request(application => 'PA'
237 , program =>'PARPWORKER'
238 , argument1 => p_request_id
239 , argument2 => p_rp_id);
240 END LOOP;
241
242
243 IF g_debug_mode = 'Y' THEN
244 Pji_Utils.WRITE2LOG( 'PA_RP_UTIL.Start_Workers: finishing', TRUE , g_proc);
245 END IF;
246
247 EXCEPTION
248 WHEN OTHERS THEN
249 Add_Message(p_app_short_name=> 'PA',p_msg_name=> 'PA_RP_GENERIC_MSG',p_msg_type=>Pa_Rp_Util.G_RET_STS_ERROR,p_token1=>'PROC_NAME',p_token1_value=>'PA_RP_UTIL.Start_Workers');
250 RAISE;
251 END Start_Workers;
252
253
254 PROCEDURE Check_Workers (p_main_request_id NUMBER
255 , p_worker_request_ids SYSTEM.PA_NUM_TBL_TYPE
256 , x_conc_prog_status OUT NOCOPY NUMBER -- 0 normal 1 warning 2 error
257 , x_return_status IN OUT NOCOPY VARCHAR
258 , x_msg_count IN OUT NOCOPY NUMBER
259 , x_msg_data IN OUT NOCOPY VARCHAR2)IS
260 l_finish VARCHAR(1);
261 l_i NUMBER;
262 l_phase VARCHAR(80);
263 l_status VARCHAR(80);
264 l_dev_phase VARCHAR(80);
265 l_dev_status VARCHAR(80);
266 l_message VARCHAR(2000);
267 l_request_check BOOLEAN;
268 l_request_id NUMBER;
269 BEGIN
270 IF g_debug_mode = 'Y' THEN
271 Pji_Utils.WRITE2LOG( 'PA_RP_UTIL.Check_Workers: beginning', TRUE , g_proc);
272 END IF;
273
274 IF x_return_status IS NULL THEN
275 x_msg_count := 0;
276 x_return_status := Fnd_Api.G_RET_STS_SUCCESS;
277 END IF;
278 /* When the main program call this procedure, it should already done with its own processing
279 work, which means there is no job left to be done, so the job assignment table should
280 only contain finished job and job under processing*/
281
282 -- error> warning > normal; waiting> normal; complete and pending status only need to check once.
283 -- initialize x_conc_prog_stauts = success,
284 -- for every worker request id check the status of the worker conc prog
285 -- check whether all jobs under this request in P status
286 -- (still under processing by looking at the table) -- l_finish = Y/N
287 -- if the worker is running or the worker is inactive but job is not done, then wait
288 -- when the waiting is complete, decide what the conc prog status should be
289
290 x_conc_prog_status := 0;
291
292 FOR l_i IN 1..p_worker_request_ids.LAST LOOP
293
294 l_request_id := p_worker_request_ids(l_i);
295 l_dev_phase := 'RUNNING';
296
297 -- Pa_Debug.log_message(p_message => 'Checking worker status for request:'|| l_request_id);
298 WHILE (l_dev_phase = 'RUNNING') OR ((l_finish = 'N') AND (l_dev_phase = 'INACTIVE') AND (l_dev_status <>'DISABLED')) LOOP
299 l_request_check:= Fnd_Concurrent.get_request_status(request_id => l_request_id
300 ,phase => l_phase
301 ,status => l_status
302 ,dev_phase => l_dev_phase
303 ,dev_status => l_dev_status
304 ,message => l_message);
305
306 SELECT DECODE(COUNT(*),0,'Y','N')
307 INTO l_finish
308 FROM pa_rp_job_assignments
309 WHERE main_request_id = p_main_request_id
310 AND worker_request_id = l_request_id
311 AND status_flag = 'P';
312
313 IF (l_dev_phase = 'RUNNING') OR ((l_finish = 'N') AND (l_dev_phase = 'INACTIVE') AND (l_dev_status <>'DISABLED')) THEN
314 Pa_Debug.log_message(p_message => 'Waiting for request:'|| l_request_id);
315 DBMS_LOCK.SLEEP(1);
316 END IF;
317 END LOOP;
318
319
320 IF (l_dev_phase = 'PENDING') OR (l_dev_phase = 'INACTIVE' AND l_finish='Y') THEN
321 l_request_check := Fnd_Concurrent.CANCEL_REQUEST(l_request_id,l_message);
322 END IF;
323
324 IF x_conc_prog_status <2 THEN
325 IF (l_dev_phase = 'COMPLETE') THEN
326 IF (l_finish = 'N') OR (l_dev_status = 'ERROR') THEN
327 x_conc_prog_status :=2;
328 ELSIF (l_dev_status = 'WARNING') AND (x_conc_prog_status <1) THEN
329 x_conc_prog_status :=1;
330 END IF;
331 ELSIF (l_dev_phase = 'INACTVE') AND (l_dev_status='DISABLED') AND (l_finish = 'N') THEN
332 x_conc_prog_status :=2;
333 END IF;
334 END IF;
335 END LOOP;
336
337 /* Move the job assignment record into history table */
338 INSERT INTO pa_rp_job_assignments_history
339 (ASSIGNMENT_ID, MAIN_REQUEST_ID, WORKER_REQUEST_ID, STATUS_FLAG, RP_ID, BURSTING_VALUE_1, BURSTING_VALUE_2, BURSTING_VALUE_3, CREATION_DATE, LAST_UPDATE_DATE, LAST_UPDATED_BY, CREATED_BY, LAST_UPDATE_LOGIN)
340 SELECT ASSIGNMENT_ID, MAIN_REQUEST_ID, WORKER_REQUEST_ID, STATUS_FLAG, RP_ID, BURSTING_VALUE_1, BURSTING_VALUE_2, BURSTING_VALUE_3, CREATION_DATE, LAST_UPDATE_DATE, LAST_UPDATED_BY, CREATED_BY, LAST_UPDATE_LOGIN FROM pa_rp_job_assignments
341 WHERE main_request_id = p_main_request_id;
342
343 DELETE FROM pa_rp_job_assignments
344 WHERE main_request_id = p_main_request_id;
345
346 IF g_debug_mode = 'Y' THEN
347 Pji_Utils.WRITE2LOG( 'PA_RP_UTIL.Check_Workers: finishing', TRUE , g_proc);
348 END IF;
349
350 EXCEPTION
351 WHEN OTHERS THEN
352 Add_Message(p_app_short_name=> 'PA',p_msg_name=> 'PA_RP_GENERIC_MSG',p_msg_type=>Pa_Rp_Util.G_RET_STS_ERROR,p_token1=>'PROC_NAME',p_token1_value=>'PA_RP_UTIL.Check_Workers');
353 RAISE;
354 END Check_Workers;
355
356
357 /*
358 PROCEDURE Save_RP_Definition(p_rp_definition pa_rp_definition_all) IS
359 l_rp_id NUMBER;
360 l_dist_list_id NUMBER;
361 l_dist_list_items pa_rp_dist_list_items_tbl;
362 l_dist_list_item_id NUMBER;
363 BEGIN
364 l_rp_id := p_rp_definition.rp_id;
365 l_dist_list_id := p_rp_definition.dist_list_id;
366 l_dist_list_items := p_rp_definition.dist_list_items;
367 IF l_rp_id IS NULL THEN
368
369 --Insert RP_DEFINITION_First
370 l_rp_id := TO_NUMBER(SYSDATE,'j');
371 Pa_Distribution_Lists_Pkg.INSERT_ROW
372 (
373 P_LIST_ID => l_dist_list_id,
374 P_NAME => 'Reportin pack distribution list',
375 P_DESCRIPTION => l_rp_id,
376 P_RECORD_VERSION_NUMBER => NULL,
377 P_CREATED_BY => Fnd_Global.user_id,
378 P_CREATION_DATE => SYSDATE,
379 P_LAST_UPDATED_BY => Fnd_Global.user_id,
380 P_LAST_UPDATE_DATE => SYSDATE,
381 P_LAST_UPDATE_LOGIN => Fnd_Global.user_id
382 );
383
384
385 Pa_Object_Dist_Lists_Pkg.INSERT_ROW
386 (
387 P_LIST_ID => l_dist_list_id,
388 P_OBJECT_TYPE => 'PA_RP_LIST',
389 P_OBJECT_ID => l_rp_id,
390 P_RECORD_VERSION_NUMBER => NULL,
391 P_CREATED_BY => Fnd_Global.user_id,
392 P_CREATION_DATE => SYSDATE,
393 P_LAST_UPDATED_BY => Fnd_Global.user_id,
394 P_LAST_UPDATE_DATE => SYSDATE,
395 P_LAST_UPDATE_LOGIN => Fnd_Global.user_id
396 );
397 END IF;
398
399 IF l_dist_list_items IS NOT NULL THEN
400
401 FOR i IN l_dist_list_items.FIRST..l_dist_list_items.LAST
402 LOOP
403
404 IF l_dist_list_items(i).list_item_id IS NOT NULL THEN
405
406
407 Pa_Dist_List_Items_Pkg.Update_Row
408 (
409 P_LIST_ITEM_ID => l_dist_list_items(i).list_item_id,
410 P_LIST_ID => l_dist_list_id,
411 P_RECIPIENT_TYPE => l_dist_list_items(i).recipient_type,
412 P_RECIPIENT_ID => l_dist_list_items(i).recipient_id,
413 P_ACCESS_LEVEL => NULL,
414 P_MENU_ID => NULL,
415 P_EMAIL => l_dist_list_items(i).email_exists,
416 P_RECORD_VERSION_NUMBER => NULL,
417 P_LAST_UPDATED_BY => Fnd_Global.user_id,
418 P_LAST_UPDATE_DATE => SYSDATE,
419 P_LAST_UPDATE_LOGIN => Fnd_Global.user_id
420 );
421
422 ELSE
423
424 -- call insert , set listItemId
425 --DBMS_OUTPUT.put_line('... before insert in Pa_Dist_List_Items_Update_Row...insert row..');
426
427 Pa_Dist_List_Items_Pkg.INSERT_ROW
428 (
429 P_LIST_ITEM_ID => l_dist_list_item_id,
430 P_LIST_ID => l_dist_list_id,
431 P_RECIPIENT_TYPE => l_dist_list_items(i).recipient_type,
432 P_RECIPIENT_ID => l_dist_list_items(i).recipient_id,
433 P_ACCESS_LEVEL => NULL,
434 P_MENU_ID => NULL,
435 P_EMAIL => l_dist_list_items(i).email_exists,
436 P_RECORD_VERSION_NUMBER => NULL,
437 P_LAST_UPDATED_BY => Fnd_Global.user_id,
438 P_LAST_UPDATE_DATE => SYSDATE,
439 P_LAST_UPDATE_LOGIN => Fnd_Global.user_id,
440 P_CREATED_BY => Fnd_Global.user_id,
441 P_CREATION_DATE => SYSDATE
442 );
443
444
445 END IF;
446
447 END LOOP;
448
449 END IF;
450
451 END;
452 */
453
454 PROCEDURE Save_Params (p_main_request_id NUMBER
455 , p_rp_id NUMBER
456 , p_param_names SYSTEM.PA_VARCHAR2_240_TBL_TYPE
457 , p_param_values SYSTEM.PA_VARCHAR2_240_TBL_TYPE
458 , x_return_status IN OUT NOCOPY VARCHAR
459 , x_msg_count IN OUT NOCOPY NUMBER
460 , x_msg_data IN OUT NOCOPY VARCHAR2) IS
461 l_i NUMBER;
462 BEGIN
463 IF g_debug_mode = 'Y' THEN
464 Pji_Utils.WRITE2LOG( 'PA_RP_UTIL.Save_Params: beginning', TRUE , g_proc);
465 END IF;
466
467 IF x_return_status IS NULL THEN
468 x_msg_count := 0;
469 x_return_status := Fnd_Api.G_RET_STS_SUCCESS;
470 END IF;
471
472 FORALL l_i IN p_param_names.first..p_param_names.last
473 INSERT INTO PA_RP_CONC_PARAMS(REQUEST_ID
474 , RP_ID
475 , PARAM_NAME
476 , PARAM_VALUE
477 ,LAST_UPDATE_DATE
478 , LAST_UPDATED_BY
479 , CREATION_DATE
480 , CREATED_BY
481 , LAST_UPDATE_LOGIN)
482 VALUES(p_main_request_id
483 , p_rp_id
484 , p_param_names(l_i)
485 , p_param_values(l_i)
486 , SYSDATE()
487 , Fnd_Global.user_id
488 , SYSDATE()
489 , Fnd_Global.user_id
490 , Fnd_Global.login_id);
491
492
493
494 IF g_debug_mode = 'Y' THEN
495 Pji_Utils.WRITE2LOG( 'PA_RP_UTIL.Save_Params: finishing', TRUE , g_proc);
496 END IF;
497
498 EXCEPTION
499 WHEN OTHERS THEN
500 Add_Message(p_app_short_name=> 'PA',p_msg_name=> 'PA_RP_GENERIC_MSG',p_msg_type=>Pa_Rp_Util.G_RET_STS_ERROR,p_token1=>'PROC_NAME',p_token1_value=>'PA_RP_UTIL.Save_Params');
501 RAISE;
502 END Save_Params;
503
504
505 PROCEDURE Get_Email_Addresses (p_rp_id NUMBER
506 , p_project_id NUMBER
507 , x_email_addresses OUT NOCOPY VARCHAR2
508 , x_return_status IN OUT NOCOPY VARCHAR2
509 , x_msg_count IN OUT NOCOPY NUMBER
510 , x_msg_data IN OUT NOCOPY VARCHAR2)
511 IS
512 l_email_list VARCHAR(4000);
513
514 CURSOR c_project_role_emails IS
515 SELECT DISTINCT(p.email_address)
516 FROM pa_object_dist_lists o,
517 pa_dist_list_items i,
518 pa_project_parties_v p,
519 fnd_user u
520 WHERE o.object_id = p_rp_id
521 AND o.list_id = i.list_id
522 AND i.recipient_type = 'PROJECT_ROLE'
523 AND p.project_role_id = i.recipient_id
524 AND p.object_type = 'PA_PROJECTS'
525 AND p.object_id = p_project_id
526 AND u.user_name=p.user_name
527 AND (TRUNC(SYSDATE) BETWEEN TRUNC(u.start_date) AND NVL(TRUNC(u.end_date),SYSDATE))
528 AND (TRUNC(SYSDATE) BETWEEN TRUNC(p.start_date_active) AND NVL(TRUNC(p.end_date_active),SYSDATE));
529
530 BEGIN
531 IF g_debug_mode = 'Y' THEN
532 Pji_Utils.WRITE2LOG( 'PA_RP_UTIL.Get_Email_Addresses: beginning', TRUE , g_proc);
533 END IF;
534
535 IF x_return_status IS NULL THEN
536 x_msg_count := 0;
537 x_return_status := Fnd_Api.G_RET_STS_SUCCESS;
538 END IF;
539
540
541 FOR l_email IN c_project_role_emails LOOP
542 IF (trim(l_email.email_address) IS NOT NULL) THEN
543 l_email_list := l_email_list || l_email.email_address || ',';
544 END IF;
545 END LOOP;
546
547 x_email_addresses := l_email_list;
548 IF g_debug_mode = 'Y' THEN
549 Pji_Utils.WRITE2LOG( 'PA_RP_UTIL.Get_Email_Addresses: finishing', TRUE , g_proc);
550 END IF;
551
552 EXCEPTION
553 WHEN OTHERS THEN
554 Add_Message(p_app_short_name=> 'PA',p_msg_name=> 'PA_RP_GENERIC_MSG',p_msg_type=>Pa_Rp_Util.G_RET_STS_ERROR,p_token1=>'PROC_NAME',p_token1_value=>'PA_RP_UTIL.Get_Email_Addresses');
555 RAISE;
556 END Get_Email_Addresses;
557
558 PROCEDURE Derive_Proj_Params (p_project_id NUMBER
559 , p_calendar_type VARCHAR2
560 , p_currency_type VARCHAR2
561 , p_cstbudget2_plan_type_id NUMBER
562 , p_revbudget2_plan_type_id NUMBER
563 , p_report_period VARCHAR2
564 , p_spec_period_name VARCHAR2
565 , x_wbs_version_id OUT NOCOPY NUMBER
566 , x_wbs_element_id OUT NOCOPY NUMBER
567 , x_rbs_version_id OUT NOCOPY NUMBER
568 , x_rbs_element_id OUT NOCOPY NUMBER
569 , x_calendar_id OUT NOCOPY NUMBER
570 , x_report_date OUT NOCOPY NUMBER
571 , x_period_name OUT NOCOPY VARCHAR2
572 , x_period_id OUT NOCOPY NUMBER
573 , x_actual_version_id OUT NOCOPY NUMBER
574 , x_cstforecast_version_id OUT NOCOPY NUMBER
575 , x_cstbudget_version_id OUT NOCOPY NUMBER
576 , x_cstbudget2_version_id OUT NOCOPY NUMBER
577 , x_revforecast_version_id OUT NOCOPY NUMBER
578 , x_revbudget_version_id OUT NOCOPY NUMBER
579 , x_revbudget2_version_id OUT NOCOPY NUMBER
580 , x_orig_cstbudget_version_id OUT NOCOPY NUMBER
581 , x_orig_cstbudget2_version_id OUT NOCOPY NUMBER
582 , x_orig_revbudget_version_id OUT NOCOPY NUMBER
583 , x_orig_revbudget2_version_id OUT NOCOPY NUMBER
584 , x_prior_cstforecast_version_id OUT NOCOPY NUMBER
585 , x_prior_revforecast_version_id OUT NOCOPY NUMBER
586 , x_cstforecast_plan_type_id OUT NOCOPY NUMBER
587 , x_cstbudget_plan_type_id OUT NOCOPY NUMBER
588 , x_revforecast_plan_type_id OUT NOCOPY NUMBER
589 , x_revbudget_plan_type_id OUT NOCOPY NUMBER
590 , x_currency_record_type_id OUT NOCOPY NUMBER
591 , x_Currency_Code OUT NOCOPY VARCHAR2
592 , x_period_start_date OUT NOCOPY NUMBER
593 , x_period_end_date OUT NOCOPY NUMBER
594 , x_project_type OUT NOCOPY VARCHAR2
595 , x_return_status IN OUT NOCOPY VARCHAR2
596 , x_msg_count IN OUT NOCOPY NUMBER
597 , x_msg_data IN OUT NOCOPY VARCHAR2)
598 IS
599 l_temp_id NUMBER;
600 l_i NUMBER;
601 l_plan_version_ids SYSTEM.PA_NUM_TBL_TYPE;
602 BEGIN
603 IF g_debug_mode = 'Y' THEN
604 Pji_Utils.WRITE2LOG( 'PA_RP_UTIL.Derive_Proj_Params: beginning', TRUE , g_proc);
605 END IF;
606
607 IF x_return_status IS NULL THEN
608 x_msg_count := 0;
609 x_return_status := Fnd_Api.G_RET_STS_SUCCESS;
610 END IF;
611
612 SELECT
613 MAX(DECODE(ppfo.approved_cost_plan_type_flag, 'Y', fin_plan_type_id, -99)) pApprCostBudgetPTId,
614 MAX(DECODE(ppfo.approved_rev_plan_type_flag, 'Y', fin_plan_type_id, -99)) pApprRevBudgetPTId,
615 MAX(DECODE(ppfo.primary_cost_forecast_flag, 'Y', fin_plan_type_id, -99)) pPrimCostFcstPTId,
616 MAX(DECODE(ppfo.primary_rev_forecast_flag, 'Y', fin_plan_type_id, -99)) pPrimRevFcstPTId
617 INTO
618 x_cstbudget_plan_type_id,
619 x_revbudget_plan_type_id,
620 x_cstforecast_plan_type_id,
621 x_revforecast_plan_type_id
622 FROM pa_proj_fp_options ppfo
623 WHERE 1=1
624 AND ppfo.project_id = p_project_id
625 AND ppfo.fin_plan_option_level_code = 'PLAN_TYPE'
626 AND 'Y' IN (ppfo.approved_cost_plan_type_flag
627 , ppfo.approved_cost_plan_type_flag
628 , ppfo.primary_cost_forecast_flag
629 , ppfo.primary_rev_forecast_flag);
630 /*
631 Pji_Rep_Util.Derive_Default_Plan_Type_Ids(p_project_id
632 , x_cstforecast_plan_type_id
633 , x_cstbudget_plan_type_id
634 , l_temp_id
635 , x_revforecast_plan_type_id
636 , x_revbudget_plan_type_id
637 , l_temp_id
638 , x_return_status, x_msg_count, x_msg_data);
639 */
640 x_actual_version_id := -1;
641
642 SELECT
643 MAX(DECODE(pbv.current_flag, 'Y', DECODE(x_cstbudget_plan_type_id, pbv.fin_plan_type_id, DECODE(version_type,'REVENUE',-99,pbv.budget_version_id), -99), -99)) pApprCostBudgetCurrPVId,
644 MAX(DECODE(pbv.current_original_flag, 'Y', DECODE(x_cstbudget_plan_type_id, pbv.fin_plan_type_id, DECODE(version_type,'REVENUE',-99,pbv.budget_version_id), -99), -99)) pApprCostBudgetOrigPVId,
645 MAX(DECODE(pbv.current_flag, 'Y', DECODE(x_revbudget_plan_type_id , pbv.fin_plan_type_id, DECODE(version_type,'COST',-99,pbv.budget_version_id), -99), -99)) pApprRevBudgetCurrPVId,
646 MAX(DECODE(pbv.current_original_flag, 'Y', DECODE(x_revbudget_plan_type_id , pbv.fin_plan_type_id, DECODE(version_type,'COST',-99,pbv.budget_version_id), -99), -99)) pApprRevBudgetOrigPVId,
647 MAX(DECODE(pbv.current_flag, 'Y', DECODE(x_cstforecast_plan_type_id, pbv.fin_plan_type_id, DECODE(version_type,'REVENUE',-99,pbv.budget_version_id), -99), -99)) pPrimCostFcstCurrPVId,
648 MAX(DECODE(pbv.current_flag, 'Y', DECODE(x_revforecast_plan_type_id, pbv.fin_plan_type_id, DECODE(version_type,'COST',-99,pbv.budget_version_id), -99), -99)) pPrimRevFcstCurrPVId,
649 MAX(DECODE(pbv.current_flag, 'Y', DECODE(p_cstbudget2_plan_type_id, pbv.fin_plan_type_id, DECODE(version_type,'REVENUE',-99,pbv.budget_version_id), -99), -99)) pCostCurrPVId,
650 MAX(DECODE(pbv.current_original_flag, 'Y', DECODE(p_cstbudget2_plan_type_id, pbv.fin_plan_type_id, DECODE(version_type,'REVENUE',-99,pbv.budget_version_id), -99), -99)) pCostOrigPVId,
651 MAX(DECODE(pbv.current_flag, 'Y', DECODE(p_revbudget2_plan_type_id, pbv.fin_plan_type_id, DECODE(version_type,'COST',-99,pbv.budget_version_id), -99), -99)) pRevCurrPVId,
652 MAX(DECODE(pbv.current_original_flag, 'Y', DECODE(p_revbudget2_plan_type_id, pbv.fin_plan_type_id, DECODE(version_type,'COST',-99,pbv.budget_version_id), -99), -99)) pRevOrigPVId
653 INTO
654 x_cstbudget_version_id,
655 x_orig_cstbudget_version_id,
656 x_revbudget_version_id,
657 x_orig_revbudget_version_id,
658 x_cstforecast_version_id,
659 x_revforecast_version_id,
660 x_cstbudget2_version_id,
661 x_orig_cstbudget2_version_id,
662 x_revbudget2_version_id,
663 x_orig_revbudget2_version_id
664 FROM pa_budget_versions pbv
665 WHERE 1=1
666 AND pbv.project_id = p_project_id
667 AND pbv.fin_plan_type_id IN ( x_cstbudget_plan_type_id
668 , x_revbudget_plan_type_id
669 , x_cstforecast_plan_type_id
670 , x_revforecast_plan_type_id
671 , p_cstbudget2_plan_type_id
672 , p_revbudget2_plan_type_id)
673 AND 'Y' IN (pbv.current_flag, pbv.current_original_flag)
674 AND pbv.version_type IS NOT NULL;
675
676 x_prior_cstforecast_version_id := Pa_Planning_Element_Utils.get_prior_forecast_version_id(x_cstforecast_version_id,p_project_id);
677 x_prior_revforecast_version_id := Pa_Planning_Element_Utils.get_prior_forecast_version_id(x_revforecast_version_id,p_project_id);
678
679 /*
680
681
682 Pji_Rep_Util.Derive_Plan_Version_Ids(p_project_id
683 , x_cstforecast_plan_type_id
684 , x_cstbudget_plan_type_id
685 , p_cstbudget2_plan_type_id
686 , x_revforecast_plan_type_id
687 , x_revbudget_plan_type_id
688 , p_revbudget2_plan_type_id
689 , x_cstforecast_version_id
690 , x_cstbudget_version_id
691 , x_cstbudget2_version_id
692 , x_revforecast_version_id
693 , x_revbudget_version_id
694 , x_revbudget2_version_id
695 , x_orig_cstbudget_version_id
696 , x_orig_cstbudget2_version_id
697 , x_orig_revbudget_version_id
698 , x_orig_revbudget2_version_id
699 , x_prior_cstforecast_version_id
700 , x_prior_revforecast_version_id
701 , x_return_status, x_msg_count, x_msg_data);
702
703 l_plan_version_ids := SYSTEM.PA_NUM_TBL_TYPE(
704 -1
705 , x_cstforecast_version_id
706 , x_cstbudget_version_id
707 , x_cstbudget2_version_id
708 , x_revforecast_version_id
709 , x_revbudget_version_id
710 , x_revbudget2_version_id
711 , x_orig_cstbudget_version_id
712 , x_orig_cstbudget2_version_id
713 , x_orig_revbudget_version_id
714 , x_orig_revbudget2_version_id
715 , x_prior_cstforecast_version_id
716 , x_prior_revforecast_version_id);
717 */
718
719 x_wbs_element_id := Pa_Project_Structure_Utils.GET_FIN_STRUCTURE_ID(p_project_id);
720 x_wbs_version_id := Pa_Project_Structure_Utils.GET_FIN_STRUC_VER_ID(p_project_id);
721 /*
722 l_i := 1;
723 WHILE l_i <= l_plan_version_ids.COUNT AND x_wbs_version_id IS NULL LOOP
724 IF l_plan_version_ids(l_i) IS NOT NULL THEN
725 Pji_Rep_Util.Derive_Default_WBS_Parameters(p_project_id
726 , l_plan_version_ids(l_i)
727 , x_WBS_Version_ID, x_WBS_Element_Id
728 , x_return_status, x_msg_count, x_msg_data);
729 END IF;
730 l_i := l_i+1;
731 END LOOP;
732
733 */
734 IF x_wbs_version_id IS NULL THEN
735 x_wbs_version_id := -1;
736 x_wbs_element_id := -1;
737 END IF;
738
739 Pji_Rep_Util.Derive_Perf_RBS_Parameters(p_project_id
740 , l_temp_id
741 , 'N'
742 , x_RBS_Version_ID, x_RBS_Element_Id
743 , x_return_status, x_msg_count, x_msg_data);
744
745
746 Pji_Rep_Util.Derive_Project_Type(p_project_id
747 , x_project_type
748 , x_return_status
749 , x_msg_count
750 , x_msg_data);
751
752
753 Derive_Currency_Info(p_project_id, p_currency_type
754 , x_currency_record_type_id, x_currency_code
755 , x_return_status, x_msg_count, x_msg_data);
756
757 Derive_Calendar_Info(p_project_id
758 , p_report_period
759 , p_calendar_type
760 , p_spec_period_name
761 , x_calendar_id
762 , x_report_date
763 , x_period_name
764 , x_period_id
765 , x_period_start_date
766 , x_period_end_date
767 , x_return_status
768 , x_msg_count
769 , x_msg_data
770 );
771
772 IF g_debug_mode = 'Y' THEN
773 Pji_Utils.WRITE2LOG( 'PA_RP_UTIL.Derive_Proj_Params: finishing', TRUE , g_proc);
774 END IF;
775
776 EXCEPTION
777 WHEN OTHERS THEN
778 Add_Message(p_app_short_name=> 'PA',p_msg_name=> 'PA_RP_GENERIC_MSG',p_msg_type=>Pa_Rp_Util.G_RET_STS_ERROR,p_token1=>'PROC_NAME',p_token1_value=>'PA_RP_UTIL.Derive_Proj_Params');
779 RAISE;
780 END Derive_Proj_Params;
781
782
783 PROCEDURE Derive_Currency_Info(
784 p_project_id NUMBER
785 , p_currency_type VARCHAR2
786 , x_currency_record_type OUT NOCOPY NUMBER
787 , x_currency_code OUT NOCOPY VARCHAR2
788 , x_return_status IN OUT NOCOPY VARCHAR2
789 , x_msg_count IN OUT NOCOPY NUMBER
790 , x_msg_data IN OUT NOCOPY VARCHAR2) IS
791 l_proj_currency VARCHAR2(20);
792 l_projfunc_currency VARCHAR2(20);
793 BEGIN
794
795 IF g_debug_mode = 'Y' THEN
796 Pji_Utils.WRITE2LOG( 'derive_currency_info: beginning', TRUE , g_proc);
797 END IF;
798
799 IF x_return_status IS NULL THEN
800 x_msg_count := 0;
801 x_return_status := Fnd_Api.G_RET_STS_SUCCESS;
802 END IF;
803
804 SELECT project_currency_code, projfunc_currency_code
805 INTO l_proj_currency,l_projfunc_currency
806 FROM pa_projects_all
807 WHERE project_id = p_project_id;
808
809 IF p_currency_type = 'PC' THEN
810 x_currency_record_type := 8;
811 x_currency_code := l_proj_currency;
812 ELSE /* Default as project functional currency */
813 x_currency_record_type :=4;
814 x_currency_code := l_projfunc_currency;
815 END IF;
816
817
818 IF g_debug_mode = 'Y' THEN
819 Pji_Utils.WRITE2LOG( 'derive_currency_info: finishing', TRUE , g_proc);
820 END IF;
821
822 EXCEPTION
823 WHEN OTHERS THEN
824 x_msg_count := x_msg_count + 1;
825 x_return_status := Fnd_Api.G_RET_STS_ERROR;
826 Add_Message(p_app_short_name=> 'PJI',p_msg_name=> 'PJI_REP_GENERIC_MSG',p_msg_type=>Pji_Rep_Util.G_RET_STS_ERROR,p_token1=>'PROC_NAME',p_token1_value=>'Pji_RP_Util.Derive_Currency_Info');
827 RAISE;
828 END Derive_Currency_Info;
829
830
831
832 PROCEDURE Derive_Calendar_Info(p_project_id NUMBER
833 , p_report_period VARCHAR2
834 , p_calendar_type VARCHAR2
835 , p_spec_period_name VARCHAR2
836 , x_calendar_id OUT NOCOPY NUMBER
837 , x_report_date OUT NOCOPY NUMBER
838 , x_period_name OUT NOCOPY VARCHAR2
839 , x_period_id OUT NOCOPY NUMBER
840 , x_start_date OUT NOCOPY NUMBER
841 , x_end_date OUT NOCOPY NUMBER
842 , x_return_status IN OUT NOCOPY VARCHAR2
843 , x_msg_count IN OUT NOCOPY NUMBER
844 , x_msg_data IN OUT NOCOPY VARCHAR2
845 )
846 IS
847 l_active_rep VARCHAR2(30);
848 l_report_date DATE;
849 l_start_date DATE;
850 l_end_date DATE;
851 l_gl_calendar_id NUMBER;
852 l_pa_calendar_id NUMBER;
853 l_application_id NUMBER;
854 BEGIN
855 Pa_Debug.init_err_stack('PA_RP_UTIL.Derive_Report_Period');
856 IF g_debug_mode = 'Y' THEN
857 Pji_Utils.WRITE2LOG( 'Derive_Report_Period: begining', TRUE , g_proc);
858 END IF;
859
860 IF p_calendar_type = 'E' THEN
861 x_calendar_id := -1;
862 ELSE
863 SELECT info.gl_calendar_id, info.pa_calendar_id
864 INTO l_gl_calendar_id, l_pa_calendar_id
865 FROM pji_org_extr_info info, pa_projects_all proj
866 WHERE info.org_id = proj.org_id
867 AND proj.project_id = p_project_id;
868
869 IF p_calendar_type = 'G' THEN
870 x_calendar_id := l_gl_calendar_id;
871 l_application_id := 101;
872 ELSE
873 x_calendar_id := l_pa_calendar_id;
874 l_application_id := 275;
875 END IF;
876 END IF;
877
878 l_active_rep := p_report_period;
879 IF p_report_period IS NULL THEN
880 l_active_rep := 'CURRENT';
881 END IF;
882
883 IF p_calendar_type = 'E' THEN
884 IF l_active_rep IN ('CURRENT','PRIOR') THEN
885 SELECT start_date
886 INTO l_report_date
887 FROM pji_time_ent_period_v
888 WHERE TRUNC(SYSDATE) BETWEEN start_date AND end_date;
889 END IF;
890
891 IF l_active_rep = 'PRIOR' THEN
892 SELECT MAX(start_date)
893 INTO l_report_date
894 FROM pji_time_ent_period_v
895 WHERE end_date <l_report_date;
896 END IF;
897
898 SELECT name, ent_period_id, start_date, end_date
899 INTO x_period_name, x_period_id, l_start_date, l_end_date
900 FROM pji_time_ent_period_v
901 WHERE l_report_date BETWEEN start_date AND end_date;
902
903 ELSE
904 IF l_active_rep ='FIRST_OPEN' THEN
905 SELECT MIN(TIM.start_date) first_open
906 INTO l_report_date
907 FROM
908 pji_time_cal_period_v TIM
909 , gl_period_statuses glps
910 , pa_implementations paimp
911 WHERE 1=1
912 AND TIM.calendar_id = x_calendar_id
913 AND paimp.set_of_books_id = glps.set_of_books_id
914 AND glps.application_id = l_application_id
915 AND glps.period_name = TIM.NAME
916 AND closing_status = 'O';
917 ELSIF l_active_rep = 'LAST_OPEN' THEN
918 SELECT MAX(TIM.start_date) last_open
919 INTO l_report_date
920 FROM
921 pji_time_cal_period_v TIM
922 , gl_period_statuses glps
923 , pa_implementations paimp
924 WHERE 1=1
925 AND TIM.calendar_id = x_calendar_id
926 AND paimp.set_of_books_id = glps.set_of_books_id
927 AND glps.application_id = 275
928 AND glps.period_name = TIM.NAME
929 AND closing_status = 'O';
930 ELSIF l_active_rep = 'LAST_CLOSED' THEN
931 SELECT MAX(TIM.start_date) last_closed
932 INTO l_report_date
933 FROM
934 pji_time_cal_period_v TIM
935 , gl_period_statuses glps
936 , pa_implementations paimp
937 WHERE 1=1
938 AND TIM.calendar_id = x_calendar_id
939 AND paimp.set_of_books_id = glps.set_of_books_id
940 AND glps.application_id = l_application_id
941 AND glps.period_name = TIM.NAME
942 AND closing_status = 'C';
943 ELSIF l_active_rep IN ('CURRENT','PRIOR') THEN
944 SELECT start_date
945 INTO l_report_date
946 FROM pji_time_cal_period_v
947 WHERE TRUNC(SYSDATE) BETWEEN start_date
948 AND end_date
949 AND calendar_id = x_calendar_id;
950 END IF;
951
952 IF l_active_rep = 'PRIOR' THEN
953 SELECT MAX(start_date)
954 INTO l_report_date
955 FROM pji_time_cal_period_v
956 WHERE end_date < l_report_date
957 AND calendar_id = x_calendar_id;
958 END IF;
959
960 if l_active_rep = 'SPECIFIC' then
961 SELECT name, cal_period_id, start_date, end_date, start_date
962 INTO x_period_name, x_period_id, l_start_date, l_end_date, l_report_date
963 FROM pji_time_cal_period_v
964 WHERE name = p_spec_period_name
965 AND calendar_id = x_calendar_id;
966 else
967 SELECT name, cal_period_id, start_date, end_date
968 INTO x_period_name, x_period_id, l_start_date, l_end_date
969 FROM pji_time_cal_period_v
970 WHERE l_report_date BETWEEN start_date AND end_date
971 AND calendar_id = x_calendar_id;
972 end if;
973
974 END IF;
975
976 x_report_date := TO_CHAR(l_report_date,'j');
977 x_start_date := TO_CHAR(l_start_date,'j');
978 x_end_date := TO_CHAR(l_end_date, 'j');
979
980 IF g_debug_mode = 'Y' THEN
981 Pji_Utils.WRITE2LOG( 'Derive_Report_Period: finishing', TRUE , g_proc);
982 END IF;
983
984
985 EXCEPTION
986 WHEN NO_DATA_FOUND THEN
987 x_msg_count := x_msg_count + 1;
988 x_return_status := Fnd_Api.G_RET_STS_ERROR;
989 Add_Message(p_app_short_name=> 'PJI',p_msg_name=> 'PJI_REP_SYSTEM_ERROR', p_msg_type=>Pji_Rep_Util.G_RET_STS_ERROR,p_token1=>'TOKEN_NAME',p_token1_value=>'CURRENT PERIOD');
990 x_report_date :=2;
991 WHEN OTHERS THEN
992 x_msg_count := x_msg_count + 1;
993 x_return_status := Fnd_Api.G_RET_STS_ERROR;
994 Add_Message(p_app_short_name=> 'PJI',p_msg_name=> 'PJI_REP_GENERIC_MSG',p_msg_type=>Pji_Rep_Util.G_RET_STS_ERROR,p_token1=>'PROC_NAME',p_token1_value=>'Pji_Rep_Util.Derive_Report_Period');
995 RAISE;
996 END Derive_Calendar_Info;
997
998 FUNCTION Get_Percent_Complete
999 ( p_project_id NUMBER
1000 , p_wbs_version_id NUMBER
1001 , p_wbs_element_id NUMBER
1002 , p_report_date_julian NUMBER
1003 , p_calendar_type VARCHAR2 DEFAULT 'E'
1004 , p_calendar_id NUMBER DEFAULT -1
1005 ) RETURN NUMBER IS
1006 PRAGMA AUTONOMOUS_TRANSACTION;
1007 l_percent_complete NUMBER;
1008 l_return_status VARCHAR2(255);
1009 l_msg_count NUMBER;
1010 l_msg_data VARCHAR2(255);
1011 BEGIN
1012 Pji_Rep_Util.Derive_Percent_Complete
1013 ( p_project_id
1014 , p_wbs_version_id
1015 , p_wbs_element_id
1016 , 'Y'
1017 , p_report_date_julian
1018 , 'FINANCIAL'
1019 , p_calendar_type
1020 , p_calendar_id
1021 , 'N'
1022 , l_percent_complete
1023 , l_return_status
1024 , l_msg_count
1025 , l_msg_data
1026 );
1027 --This will ensure there is no error for calling a DML statement from SQL
1028 COMMIT;
1029 RETURN l_percent_complete/100;
1030 END;
1031
1032
1033 FUNCTION Get_Task_Proj_Number
1034 ( p_project_id NUMBER
1035 , p_proj_elem_id NUMBER
1036 ) RETURN VARCHAR IS
1037 l_number VARCHAR(80);
1038 BEGIN
1039
1040 SELECT
1041 MAX(DECODE(ppe.object_type, 'PA_TASKS', ppe.element_number, ppa.segment1)) task_number
1042 INTO
1043 l_number
1044 FROM pa_proj_elements ppe, pa_projects_all ppa
1045 WHERE ppe.project_id = p_project_id
1046 AND ppa.project_id = ppe.project_id
1047 AND ppe.proj_element_id = p_proj_elem_id;
1048
1049 RETURN l_number;
1050 END;
1051
1052 END Pa_Rp_Util;