[Home] [Help]
PACKAGE BODY: APPS.PJM_SEIBAN_PKG
Source
1 package body pjm_seiban_pkg as
2 /* $Header: PJMSEBNB.pls 120.1 2005/11/23 14:16:44 yliou noship $ */
3
4 --
5 -- Procedure Name : project_number_dup
6 --
7 -- Checks for the project_number if it already exists in PJM_PROJECTS_ALL_V
8 --
9 PROCEDURE project_number_dup
10 ( X_project_number IN VARCHAR2
11 , X_dup_number_flag OUT NOCOPY VARCHAR2
12 ) IS
13 BEGIN
14 X_dup_number_flag := check_dup_project_num( X_project_number , null );
15 END project_number_dup;
16
17
18 --
19 -- Procedure Name : project_name_dup
20 --
21 -- Checks for the project_name if it already exists in PJM_PROJECTS_ALL_V
22 --
23 PROCEDURE project_name_dup
24 ( X_project_name IN VARCHAR2
25 , X_dup_name_flag OUT NOCOPY VARCHAR2
26 ) IS
27 BEGIN
28 X_dup_name_flag := check_dup_project_name( X_project_name , null );
29 END project_name_dup;
30
31
32 FUNCTION check_dup_project_num
33 ( X_project_number IN VARCHAR2
34 , X_project_id IN NUMBER
35 ) RETURN VARCHAR2
36 IS
37
38 CURSOR c IS
39 select project_id
40 from pa_projects_all
41 where segment1 = X_project_number
42 and ( X_project_id is null or project_id <> X_project_id )
43 union all
44 select project_id
45 from pjm_seiban_numbers
46 where project_number = X_project_number
47 and ( X_project_id is null or project_id <> X_project_id );
48 crec c%rowtype;
49
50 BEGIN
51
52 if ( X_project_number is null ) then
53 return('N');
54 end if;
55
56 open c;
57 fetch c into crec;
58 if c%notfound then
59 close c;
60 return('N');
61 else
62 close c;
63 return('Y');
64 end if;
65
66 EXCEPTION
67 WHEN OTHERS THEN
68 return('E');
69 END check_dup_project_num;
70
71
72 FUNCTION check_dup_project_name
73 ( X_project_name IN VARCHAR2
74 , X_project_id IN NUMBER
75 ) RETURN VARCHAR2
76 IS
77
78 CURSOR c IS
79 select project_id
80 from pa_projects_all
81 where name = X_project_name
82 and ( X_project_id is null or project_id <> X_project_id )
83 union all
84 select project_id
85 from pjm_seiban_numbers
86 where project_name = X_project_name
87 and ( X_project_id is null or project_id <> X_project_id );
88 crec c%rowtype;
89
90 BEGIN
91
92 if ( X_project_name is null ) then
93 return('N');
94 end if;
95
96 open c;
97 fetch c into crec;
98 if c%notfound then
99 close c;
100 return('N');
101 else
102 close c;
103 return('Y');
104 end if;
105
106 EXCEPTION
107 WHEN OTHERS THEN
108 return('E');
109 END check_dup_project_name;
110
111
112 --
113 -- Private procedure to get messages from AMG
114 --
115 FUNCTION get_messages
116 ( X_msg_count IN NUMBER
117 , X_msg_data IN VARCHAR2
118 ) RETURN VARCHAR2 IS
119
120 msgtxt varchar2(2000);
121 msgbuf varchar2(32000);
122 msgidxout number;
123
124 BEGIN
125
126 msgbuf := null;
127 for i in 1..X_msg_count loop
128 pa_interface_utils_pub.get_messages
129 ( p_msg_index => i
130 , p_msg_count => X_msg_count
131 , p_msg_data => X_msg_data
132 , p_data => msgtxt
133 , p_msg_index_out => msgidxout
134 );
135 if ( msgbuf is null ) then
136 msgbuf := msgtxt;
137 else
138 msgbuf := msgbuf || fnd_global.newline || msgtxt;
139 end if;
140 end loop;
141 return( msgbuf );
142
143 EXCEPTION
144 WHEN OTHERS THEN
145 return( msgbuf );
146 END get_messages;
147
148
149 --
150 -- Create_amg_project procedure can be used to create a project in
151 -- Oracle Projects. This uses AMG's API named create_project.
152 --
153 -- This procedure accepts the following parameters:
154 --
155 -- Project_created_from source (template) project_id
156 -- Project_number target project_number
157 -- Project_name target project_name
158 -- start_date start date for the new project
159 -- end_date end date for the new project
160 -- Submit_Workflow 'Y' or 'N'
161 -- Project_id ID of the target project
162 -- Return_status status of the project creation.
163 --
164 PROCEDURE create_amg_project
165 ( X_project_created_from IN NUMBER
166 , X_project_number IN VARCHAR2
167 , X_project_name IN VARCHAR2
168 , X_start_date IN DATE
169 , X_end_date IN DATE
170 , X_submit_workflow IN VARCHAR2
171 , X_project_id OUT NOCOPY NUMBER
172 , X_return_status OUT NOCOPY VARCHAR2
173 ) IS
174
175 l_api_version_number NUMBER := 1.0;
176 l_commit VARCHAR2(1) := 'F';
177 l_pm_product_code VARCHAR2(10):= 'PJM';
178 l_init_msg_list VARCHAR2(1);
179 l_msg_count NUMBER;
180 l_msg_data VARCHAR2(2000);
181 l_errbuf VARCHAR2(20000);
182 l_return_status VARCHAR2(1);
183 l_Submit_Workflow VARCHAR2(1);
184 API_ERROR EXCEPTION;
185 --
186 -- Variables needed for project specific parameters
187 --
188 l_project_in_rec pa_project_pub.project_in_rec_type;
189 l_project_out_rec pa_project_pub.project_out_rec_type;
190 l_key_member_tbl pa_project_pub.project_role_tbl_type;
191 l_class_category_tbl pa_project_pub.class_category_tbl_type;
192 l_tasks_out pa_project_pub.task_out_tbl_type;
193 l_tasks_in pa_project_pub.task_in_tbl_type;
194 -- bug 4731449
195 l_resp_id NUMBER := 0;
196 l_user_id NUMBER := 0;
197 l_resp_appl_id NUMBER := 0;
198 l_org_id NUMBER := 0;
199
200 BEGIN
201
202 l_project_in_rec.pm_project_reference := X_project_number;
203 l_project_in_rec.project_name := X_project_name;
204 l_project_in_rec.created_from_project_id := X_project_created_from;
205 l_project_in_rec.description := X_project_name;
206 l_project_in_rec.public_sector_flag := 'N';
207 l_project_in_rec.start_date := X_start_date;
208 l_project_in_rec.completion_date := X_end_date;
209
210 -- bug 4731449
211 l_resp_id := FND_GLOBAL.Resp_id;
212 l_user_id := FND_GLOBAL.User_id;
213 select org_id into l_org_id
214 from pa_projects_all
215 where project_id = X_project_created_from;
216 select application_id into l_resp_appl_id
217 from fnd_responsibility where responsibility_id = l_resp_id;
218
219 PA_INTERFACE_UTILS_PUB.set_global_info
220 ( p_api_version_number => l_api_version_number
221 , p_responsibility_id => l_resp_id
222 , p_user_id => l_user_id
223 , p_resp_appl_id => l_resp_appl_id
224 , p_operating_unit_id => l_org_id
225 , p_msg_count => l_msg_count
226 , p_msg_data => l_msg_data
227 , p_return_status => l_return_status
228 );
229
230 X_return_status := l_return_status;
231 if l_return_status <> 'S' then
232 raise API_ERROR;
233 else
234 pa_project_pub.create_project
235 ( p_api_version_number => l_api_version_number
236 , p_commit => l_commit
237 , p_init_msg_list => l_init_msg_list
238 , p_msg_count => l_msg_count
239 , p_msg_data => l_msg_data
240 , p_return_status => l_return_status
241 , p_workflow_started => l_Submit_Workflow
242 , p_pm_product_code => l_pm_product_code
243 , p_project_in => l_project_in_rec
244 , p_project_out => l_project_out_rec
245 , p_key_members => l_key_member_tbl
246 , p_class_categories => l_class_category_tbl
247 , p_tasks_in => l_tasks_in
248 , p_tasks_out => l_tasks_out
249 );
250
251 X_project_id := l_project_out_rec.pa_project_id;
252 X_return_status := l_return_status;
253 if l_return_status <> 'S' then
254 raise API_ERROR;
255 end if;
256 end if;
257
258 EXCEPTION
259 WHEN API_ERROR THEN
260 l_errbuf := get_messages( X_msg_count => l_msg_count , X_msg_data => l_msg_data );
261 fnd_message.set_name('PJM','SEIB-AMG PROJECT ERROR');
262 fnd_message.set_token('DETAIL', l_errbuf);
263
264 WHEN OTHERS THEN
265 l_errbuf := get_messages( X_msg_count => l_msg_count , X_msg_data => l_msg_data );
266 fnd_message.set_name('PJM','SEIB-AMG PROJECT ERROR');
267 fnd_message.set_token('DETAIL', l_errbuf);
268
269 END create_amg_project;
270
271
272 --
273 -- Procedure Name : create_amg_task
274 --
275 -- Create_amg_task procedure can be used to create a task in
276 -- Oracle projects. This uses AMG's API named add_task.
277 --
278 -- This procedure accepts the following parameters:
279 --
280 -- Project_id project_id of project under which the task
281 -- needs to be created
282 -- Project_number Corresponding project_number for the above
283 -- project
284 -- Task_number Task number for the task to be created
285 -- Task_id ID of the task that has been created
286 -- Return_status status of the Task creation
287 --
288 procedure create_amg_task
289 ( X_project_id IN NUMBER
290 , X_project_number IN VARCHAR2
291 , X_task_number IN VARCHAR2
292 , X_task_id OUT NOCOPY NUMBER
293 , X_return_status OUT NOCOPY VARCHAR2
294 ) IS
295
296 l_api_version_number NUMBER := 1.0;
297 l_commit VARCHAR2(1) := 'F';
298 l_return_status VARCHAR2(1);
299 l_init_msg_list VARCHAR2(1);
300 l_msg_count NUMBER;
301 l_msg_data VARCHAR2(2000);
302 l_errbuf VARCHAR2(20000);
303 --
304 -- Variables needed for project specific parameters
305 --
306 l_pm_product_code VARCHAR2(10);
307 l_pa_project_id_out NUMBER(15);
308 l_pa_project_number_out VARCHAR2(25);
309 l_task_id NUMBER(15);
310 API_ERROR EXCEPTION;
311
312 BEGIN
313
314 l_pm_product_code := 'PJM';
315
316 --
317 -- Temporarily setting the Cross Project responsibility to Yes to
318 -- bypass PA security check
319 --
320 fnd_profile.put('PA_SUPER_PROJECT' , 'Y');
321
322 pa_project_pub.add_task
323 ( p_api_version_number => l_api_version_number
324 , p_commit => l_commit
325 , p_init_msg_list => l_init_msg_list
326 , p_msg_count => l_msg_count
327 , p_msg_data => l_msg_data
328 , p_return_status => l_return_status
329 , p_pm_product_code => l_pm_product_code
330 , p_pm_project_reference => X_project_number
331 , p_pa_project_id => X_project_id
332 , p_pm_task_reference => X_task_number
333 , p_pa_task_number => X_task_number
334 , p_task_name => X_task_number
335 , p_task_description => X_task_number
336 , p_pa_project_id_out => l_pa_project_id_out
337 , p_pa_project_number_out => l_pa_project_number_out
338 , p_task_id => l_task_id
339 );
340
341 X_task_id := l_task_id;
342 X_return_status := l_return_status;
343
344 if l_return_status <> 'S' then
345 Raise API_ERROR;
346 end if;
347
348 EXCEPTION
349 WHEN API_ERROR THEN
350 l_errbuf := get_messages( X_msg_count => l_msg_count , X_msg_data => l_msg_data );
351 fnd_message.set_name('PJM','SEIB-AMG TASK ERROR');
352 fnd_message.set_token('DETAIL', l_errbuf);
353
354 WHEN OTHERS THEN
355 l_errbuf := get_messages( X_msg_count => l_msg_count , X_msg_data => l_msg_data );
356 fnd_message.set_name('PJM','SEIB-AMG TASK ERROR');
357 fnd_message.set_token('DETAIL', l_errbuf);
358
359 END create_amg_task;
360
361
362 PROCEDURE Conc_Create
363 ( ERRBUF OUT NOCOPY VARCHAR2
364 , RETCODE OUT NOCOPY NUMBER
365 , X_Create_or_Add IN NUMBER
366 , X_Project_Template IN NUMBER
367 , X_Project_Number IN VARCHAR2
368 , X_Project_Name IN VARCHAR2
369 , X_start_date IN VARCHAR2
370 , X_end_date IN VARCHAR2
371 , X_submit_workflow IN VARCHAR2
372 , X_Project_ID IN NUMBER
373 , X_Prefix IN VARCHAR2
374 , X_Suffix IN VARCHAR2
375 , X_From_Task IN NUMBER
376 , X_To_Task IN NUMBER
377 , X_Increment_By IN NUMBER
378 , X_numeric_width IN NUMBER
379 ) IS
380
381 L_Project_ID NUMBER := NULL;
382 L_Project_Num VARCHAR2(25) := NULL;
383 L_Task_ID NUMBER := NULL;
384 L_Task_Num VARCHAR2(20) := NULL;
385 L_Return_Status VARCHAR2(1) := NULL;
386 i NUMBER;
387
388 CREATE_NEW_PROJECT CONSTANT NUMBER := 1;
389 ADD_TO_NEW_PROJECT CONSTANT NUMBER := 2;
390 Proj_Creation_Error EXCEPTION;
391 Task_Creation_Error EXCEPTION;
392
393 --
394 -- The following variables are used for Project Approval Workflow
395 --
396 l_err_code number;
397 l_err_stage varchar2(80);
398 l_err_stack varchar2(630);
399 l_wf_status_code varchar2(30);
400
401 CURSOR get_wf_status (C_project_id IN NUMBER) IS
402 SELECT wf_status_code
403 FROM pa_projects
404 WHERE project_id = C_project_id;
405
406 BEGIN
407
408 if ( X_Create_or_Add = CREATE_NEW_PROJECT ) then
409 --
410 -- If the project number is a duplicate then raise error.
411 --
412 project_number_dup(X_project_number, L_Return_Status);
413
414 if ( L_Return_Status = 'Y' ) then
415 fnd_message.set_name('PJM', 'FORM-DUPLICATE PROJECT NUM');
416 pjm_conc.put_line( fnd_message.get );
417 Raise Proj_Creation_Error;
418 end if;
419
420 --
421 -- If the project name is a duplicate then raise error
422 --
423 project_name_dup(X_project_name, L_Return_Status);
424
425 if ( L_Return_Status = 'Y' ) then
426 fnd_message.set_name('PJM', 'FORM-DUPLICATE PROJECT NAME');
427 pjm_conc.put_line( fnd_message.get );
428 Raise Proj_Creation_Error;
429 end if;
430
431 Create_AMG_Project( X_Project_Template
432 , X_Project_Number
433 , X_Project_Name
434 , fnd_date.canonical_to_date(X_start_date)
435 , fnd_date.canonical_to_date(X_end_date)
436 , X_submit_workflow
437 , L_Project_ID
438 , L_Return_Status );
439
440 pjm_conc.put_line('Project_Number = ' || X_Project_Number);
441 pjm_conc.put_line('Project_Name = ' || X_Project_Name);
442 pjm_conc.put_line('Project_ID = ' || to_char(L_Project_ID));
443 pjm_conc.put_line('AMG return_status = ' || L_Return_Status);
444 pjm_conc.new_line(1);
445
446 if ( L_Return_Status <> 'S' ) then
447 pjm_conc.put_line( fnd_message.get );
448 Raise Proj_Creation_Error;
449 end if;
450
451 --
452 -- The project number is needed for task creation as the
453 -- project number is used as the AMG reference to the
454 -- project
455 --
456 L_Project_Num := X_Project_Number;
457
458 commit;
459
460 else
461 --
462 -- User wants to add to existing project
463 --
464 L_Project_ID := X_Project_ID;
465
466 SELECT segment1
467 INTO L_Project_Num
468 FROM PA_Projects
469 WHERE Project_ID = L_Project_ID;
470
471 end if;
472
473 --
474 -- Task Creation
475 --
476 i := X_From_Task;
477
478 WHILE ( i<= X_To_Task ) LOOP
479 if ( X_numeric_width is not null ) then
480 --
481 -- User wants zero padding
482 --
483 L_Task_Num := rtrim(X_Prefix) ||
484 lpad(to_char(i), X_numeric_width, '0') ||
485 rtrim(X_suffix);
486 else
487 --
488 -- User does not want zero padding
489 --
490 L_Task_Num := rtrim(X_Prefix) ||
491 to_char(i) ||
492 rtrim(X_suffix);
493 end if;
494
495 --
496 -- Check for duplicates
497 --
498 BEGIN
499 SELECT task_id
500 INTO L_Task_ID
501 FROM PA_Tasks
502 WHERE Project_ID = L_Project_ID
503 AND ( Task_Number = L_Task_Num
504 OR Task_Name = L_Task_num )
505 AND rownum = 1;
506
507 EXCEPTION
508 WHEN NO_DATA_FOUND THEN
509 L_Task_ID := NULL;
510 WHEN OTHERS THEN
511 pjm_conc.put_line( sqlerrm );
512 Raise Task_Creation_Error;
513 END;
514
515 if ( L_Task_ID is null ) then
516
517 Create_AMG_Task( L_Project_ID
518 , L_Project_Num
519 , L_Task_Num
520 , L_Task_ID
521 , L_Return_Status );
522
523 pjm_conc.put_line(
524 'Task_Number = ' || L_Task_Num || ' ' ||
525 'Task_ID = ' || to_char(L_Task_ID) || ' ' ||
526 'AMG return_status = ' || L_Return_Status);
527
528 if ( L_Return_Status <> 'S' ) then
529 pjm_conc.new_line(1);
530 pjm_conc.put_line( fnd_message.get );
531 Raise Task_Creation_Error;
532 end if;
533
534 commit;
535
536 i := i + X_Increment_By;
537
538 end if;
539
540 END LOOP;
541
542 if ( X_submit_workflow = 'Y' ) then
543
544 OPEN get_wf_status ( l_project_id );
545 FETCH get_wf_status INTO l_wf_status_code;
546 CLOSE get_wf_status;
547
548 if ( l_wf_status_code <> 'IN_ROUTE' ) then
549 --
550 -- Workflow has not been submitted
551 --
552 PA_PROJECT_WF.start_project_wf
553 ( l_project_id
554 , l_err_stack
555 , l_err_stage
556 , l_err_code );
557
558 if ( l_err_code <> 0 ) then
559 fnd_message.set_name('PA', l_err_stage);
560 errbuf := fnd_message.get;
561 pjm_conc.put_line( errbuf );
562 commit;
563 retcode := PJM_CONC.G_conc_warning;
564 end if;
565
566 commit;
567
568 end if; /* l_wf_status_code */
569
570 end if; /* X_submit_workflow */
571
572 retcode := PJM_CONC.G_conc_success;
573
574 EXCEPTION
575 WHEN Proj_Creation_Error THEN
576 fnd_message.set_name('PJM', 'SEIB-PROJ CREATION FAILED');
577 errbuf := fnd_message.get;
578 pjm_conc.put_line( errbuf );
579 retcode := PJM_CONC.G_conc_failure;
580
581 WHEN Task_Creation_Error THEN
582 fnd_message.set_name('PJM', 'SEIB-TASK CREATION FAILED');
583 errbuf := fnd_message.get;
584 pjm_conc.put_line( errbuf );
585 retcode := PJM_CONC.G_conc_failure;
586
587 WHEN OTHERS THEN
588 errbuf := sqlerrm;
589 pjm_conc.put_line( sqlerrm );
590 retcode := PJM_CONC.G_conc_failure;
591
592 END Conc_Create;
593
594 END pjm_seiban_pkg;