[Home] [Help]
PACKAGE BODY: APPS.ZPB_DC_OBJECTS_PVT
Source
1 PACKAGE BODY ZPB_DC_OBJECTS_PVT AS
2 /* $Header: ZPBDCGTB.pls 120.10 2008/01/24 09:57:46 maniskum ship $ */
3
4 G_PKG_NAME CONSTANT VARCHAR2(30):= 'ZPB_DC_OBJECTS_PVT';
5
6
7 /*---------------------------Private Procedure-----------------------------*/
8 /*=========================================================================+
9 | PROCEDURE Populate_Distributors
10 |
11 | DESCRIPTION
12 | Procedure gets the list of data owners by calling the distribution
13 | list api.
14 +========================================================================*/
15
16 PROCEDURE Get_User_Id_Clob(
17 p_api_version IN NUMBER,
18 p_init_msg_list IN VARCHAR2,
19 p_commit IN VARCHAR2,
20 p_validation_level IN NUMBER,
21 x_return_status OUT NOCOPY VARCHAR2,
22 x_msg_count OUT NOCOPY NUMBER,
23 x_msg_data OUT NOCOPY VARCHAR2,
24 --
25 p_object_id IN NUMBER,
26 p_object_user_id IN NUMBER,
27 p_recipient_type IN VARCHAR2,
28 p_resp_key IN VARCHAR2,
29 x_user_id_clob OUT NOCOPY CLOB
30 ) IS
31
32 PRAGMA autonomous_transaction;
33
34 l_api_name CONSTANT VARCHAR2(30) := 'Get_User_Id_Clob';
35 l_api_version CONSTANT NUMBER := 1.0;
36 l_business_area_id ZPB_BUSINESS_AREAS.BUSINESS_AREA_ID%type;
37 l_template_id NUMBER;
38 l_master_object_id NUMBER;
39
40 BEGIN
41
42 SAVEPOINT Get_User_Id_Clob_Pvt ;
43
44 IF NOT FND_API.Compatible_API_Call ( l_api_version,
45 p_api_version,
46 l_api_name,
47 G_PKG_NAME )
48 THEN
49 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
50 END IF;
51
52
53 IF FND_API.to_Boolean ( p_init_msg_list ) THEN
54 FND_MSG_PUB.initialize ;
55 END IF;
56
57 x_return_status := FND_API.G_RET_STS_SUCCESS ;
58
59 --
60 -- Get the Business Area ID for the object in question
61 --
62 select B.BUSINESS_AREA_ID, A.TEMPLATE_ID
63 into l_business_area_id , l_template_id
64 from ZPB_DC_OBJECTS A,
65 ZPB_ANALYSIS_CYCLES B
66 where A.OBJECT_ID = p_object_id
67 and A.AC_INSTANCE_ID = B.ANALYSIS_CYCLE_ID;
68
69 select OBJECT_ID into l_master_object_id
70 from ZPB_DC_OBJECTS
71 where TEMPLATE_ID = l_template_id
72 and OBJECT_TYPE = 'M';
73
74
75 -- Initialize the parameters
76
77 -- API Body
78
79 ZPB_AW.INITIALIZE_USER(p_api_version => 1.0,
80 p_init_msg_list => FND_API.G_FALSE,
81 p_validation_level => p_validation_level,
82 x_return_status => x_return_status,
83 x_msg_count => x_msg_count,
84 x_msg_data => x_msg_data,
85 p_user => p_object_user_id,
86 p_business_area_id => l_business_area_id,
87 p_attach_readwrite => FND_API.G_FALSE);
88
89
90 ZPB_DATA_COLLECTION_UTIL_PVT.get_dc_owners(
91 p_object_id => l_master_object_id,
92 p_user_id => p_object_user_id,
93 p_query_type => p_recipient_type,
94 p_api_version => p_api_version,
95 p_init_msg_list => p_init_msg_list,
96 p_commit => p_commit,
97 p_validation_level => p_validation_level,
98 x_owner_list => x_user_id_clob,
99 x_return_status => x_return_status,
100 x_msg_count => x_msg_count,
101 x_msg_data => x_msg_data);
102
103 ZPB_AW.clean_workspace (
104 p_api_version => p_api_version,
105 p_init_msg_list => p_init_msg_list,
106 p_validation_level => p_validation_level,
107 x_return_status => x_return_status,
108 x_msg_count => x_msg_count,
109 x_msg_data => x_msg_data);
110
111 FND_MSG_PUB.Count_And_Get ( p_count => x_msg_count,
112 p_data => x_msg_data );
113 EXCEPTION
114
115 WHEN FND_API.G_EXC_ERROR THEN
116
117 ROLLBACK TO Get_User_Id_Clob_Pvt ;
118 x_return_status := FND_API.G_RET_STS_ERROR;
119 FND_MSG_PUB.Count_And_Get ( p_count => x_msg_count,
120 p_data => x_msg_data );
121
122 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
123
124 ROLLBACK TO Get_User_Id_Clob_Pvt ;
125 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
126 FND_MSG_PUB.Count_And_Get ( p_count => x_msg_count,
127 p_data => x_msg_data );
128
129 WHEN OTHERS THEN
130
131 ROLLBACK TO Get_User_Id_Clob_Pvt ;
132 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
133
134 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
135 FND_MSG_PUB.Add_Exc_Msg ( G_PKG_NAME,
136 l_api_name);
137 END if;
138
139 FND_MSG_PUB.Count_And_Get ( p_count => x_msg_count,
140 p_data => x_msg_data );
141
142 END Get_User_Id_Clob;
143
144
145 /*=========================================================================+
146 | PROCEDURE Populate_Distributors
147 |
148 | DESCRIPTION
149 | Procedure populates the zpb_dc_distributors table to keep
150 | track of the distributors for a specific worksheet.
151 |
152 +=========================================================================*/
153
154 PROCEDURE Populate_Distributors
155 (
156 p_object_id IN NUMBER ,
157 p_distributor_user_id IN NUMBER ,
158 p_approver_type IN VARCHAR2
159 ) IS
160 BEGIN
161 INSERT INTO ZPB_DC_DISTRIBUTORS(
162 OBJECT_ID,
163 DISTRIBUTOR_USER_ID,
164 DISTRIBUTION_DATE,
165 APPROVER_TYPE,
166 LAST_UPDATE_LOGIN,
167 LAST_UPDATED_BY,
168 LAST_UPDATE_DATE,
169 CREATED_BY,
170 CREATION_DATE
171 )
172 VALUES(
173 p_object_id,
174 p_distributor_user_id,
175 SYSDATE,
176 p_approver_type,
177 fnd_global.LOGIN_ID,
178 fnd_global.user_id,
179 SYSDATE,
180 fnd_global.user_id,
181 SYSDATE
182 );
183 END Populate_Distributors;
184
185
186 /*=========================================================================+
187 | PROCEDURE Populate_Approvers
188 |
189 | DESCRIPTION
190 | Procedure populates the zpb_dc_approvers table to keep
191 | track of the approvers for a specific worksheet.
192 |
193 +=========================================================================*/
194
195 PROCEDURE Populate_Approvers(
196 p_api_version IN NUMBER,
197 p_init_msg_list IN VARCHAR2,
198 p_commit IN VARCHAR2,
199 p_validation_level IN NUMBER,
200 x_return_status OUT NOCOPY VARCHAR2,
201 x_msg_count OUT NOCOPY NUMBER,
202 x_msg_data OUT NOCOPY VARCHAR2,
203 --
204 p_object_id IN NUMBER,
205 p_approver_user_id IN NUMBER,
206 p_approval_date IN DATE)
207
208 IS
209
210 l_api_name CONSTANT VARCHAR2(30) := 'Populate_Approvers';
211 l_api_version CONSTANT NUMBER := 1.0;
212
213 BEGIN
214
215 SAVEPOINT Populate_Approvers ;
216
217 IF NOT FND_API.Compatible_API_Call ( l_api_version,
218 p_api_version,
219 l_api_name,
220 G_PKG_NAME )
221 THEN
222 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
223 END IF;
224
225
226 IF FND_API.to_Boolean ( p_init_msg_list ) THEN
227 FND_MSG_PUB.initialize ;
228 END IF;
229
230 x_return_status := FND_API.G_RET_STS_SUCCESS ;
231
232 -- Initialize the parameters
233
234 -- API Body
235 INSERT INTO zpb_dc_approvers
236 (OBJECT_ID,
237 APPROVER_USER_ID,
238 APPROVAL_DATE,
239 CREATED_BY,
240 CREATION_DATE,
241 LAST_UPDATE_LOGIN,
242 LAST_UPDATED_BY,
243 LAST_UPDATE_DATE)
244 VALUES
245 (p_object_id,
246 p_approver_user_id,
247 p_approval_date,
248 fnd_global.user_id,
249 SYSDATE,
250 fnd_global.LOGIN_ID,
251 fnd_global.user_id,
252 SYSDATE);
253
254 FND_MSG_PUB.Count_And_Get ( p_count => x_msg_count,
255 p_data => x_msg_data );
256 EXCEPTION
257
258 WHEN FND_API.G_EXC_ERROR THEN
259
260 ROLLBACK TO Populate_Approvers ;
261 x_return_status := FND_API.G_RET_STS_ERROR;
262 FND_MSG_PUB.Count_And_Get ( p_count => x_msg_count,
263 p_data => x_msg_data );
264
265 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
266
267 ROLLBACK TO Populate_Approvers ;
268 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
269 FND_MSG_PUB.Count_And_Get ( p_count => x_msg_count,
270 p_data => x_msg_data );
271
272 WHEN OTHERS THEN
273
274 ROLLBACK TO Populate_Approvers ;
275 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
276
277 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
278 FND_MSG_PUB.Add_Exc_Msg ( G_PKG_NAME,
279 l_api_name);
280 END if;
281
282 FND_MSG_PUB.Count_And_Get ( p_count => x_msg_count,
283 p_data => x_msg_data );
284
285 END Populate_Approvers;
286
287 /*=========================================================================+
288 | PROCEDURE Override_Customization
289 |
290 | DESCRIPTION
291 | Procedure overrides the users Worksheet with the Master record
292 |
293 +=========================================================================*/
294
295 PROCEDURE Override_Customization(
296 p_overwrite_cust IN VARCHAR2,
297 p_template_id IN NUMBER
298 )
299 IS
300 CURSOR dist_template_csr IS
301 SELECT ANALYSIS_CYCLE_ID ,
302 AC_INSTANCE_ID ,
303 GENERATE_TEMPLATE_TASK_ID,
304 OBJECT_USER_ID ,
305 AC_TEMPLATE_ID ,
306 TEMPLATE_NAME ,
307 DATAENTRY_OBJ_PATH ,
308 DATAENTRY_OBJ_NAME ,
309 TARGET_OBJ_PATH ,
310 TARGET_OBJ_NAME ,
311 INSTRUCTION_TEXT_ID ,
312 FREEZE_FLAG ,
313 DISTRIBUTION_METHOD ,
314 DISTRIBUTION_DIMENSION ,
315 DISTRIBUTION_HIERARCHY ,
316 DESCRIPTION ,
317 DEADLINE_DATE ,
318 APPROVAL_REQUIRED_FLAG ,
319 ENABLE_TARGET_FLAG ,
320 CREATE_INSTANCE_MEASURES_FLAG
321 FROM ZPB_DC_OBJECTS
322 WHERE TEMPLATE_ID = p_template_id
323 AND OBJECT_TYPE = 'M';
324 BEGIN
325 -- Populate the layout and properties to all ws for this template
326 FOR l_dist_template_row_rec IN dist_template_csr
327 LOOP
328 IF (p_overwrite_cust = 'OVERWRITE') THEN
329 UPDATE ZPB_DC_OBJECTS
330 SET TEMPLATE_NAME = l_dist_template_row_rec.template_name,
331 DISTRIBUTION_DATE = SYSDATE,
332 DATAENTRY_OBJ_PATH = l_dist_template_row_rec.dataentry_obj_path,
333 DATAENTRY_OBJ_NAME = l_dist_template_row_rec.dataentry_obj_name,
334 TARGET_OBJ_PATH = l_dist_template_row_rec.target_obj_path,
335 TARGET_OBJ_NAME = l_dist_template_row_rec.target_obj_name,
336 PERSONAL_DATA_QUERY_FLAG = 'N',
337 PERSONAL_TARGET_QUERY_FLAG = 'N',
338 CREATE_SOLVE_PROGRAM_FLAG = 'Y',
339 -- template properties
340 DEADLINE_DATE = l_dist_template_row_rec.deadline_date,
341 APPROVER_TYPE = 'DISTRIBUTOR',
342 DESCRIPTION = l_dist_template_row_rec.description,
343 -- WHO columns
344 LAST_UPDATE_DATE = SYSDATE,
345 LAST_UPDATED_BY = fnd_global.user_id,
346 LAST_UPDATE_LOGIN = fnd_global.LOGIN_ID
347 WHERE TEMPLATE_ID = p_template_id
348 AND OBJECT_TYPE in ('W','C');
349 END IF;
350 END LOOP;
351
352 END Override_Customization;
353
354 /*=========================================================================+
355 | PROCEDURE Distribute
356 |
357 | DESCRIPTION
358 | Procedure creates a new worksheet for each user on the
359 | distribution user list or users in the parameter table.
360 |
361 +=========================================================================*/
362 PROCEDURE Distribute
363 (
364 p_object_id IN NUMBER ,
365 p_object_type IN VARCHAR2,
366 p_template_id IN NUMBER ,
367 p_ac_template_id IN NUMBER ,
368 p_analysis_cycle_id IN NUMBER ,
369 p_ac_instance_id IN NUMBER ,
370 p_generate_template_task_id IN NUMBER ,
371 p_object_user_id IN NUMBER ,
372 p_distributor_user_id IN NUMBER,
373 p_template_name IN VARCHAR2 ,
374 p_dataentry_obj_path IN VARCHAR2 ,
375 p_dataentry_obj_name IN VARCHAR2 ,
376 p_target_obj_path IN VARCHAR2 ,
377 p_target_obj_name IN VARCHAR2 ,
378 p_deadline_date IN DATE ,
379 p_instruction_text_id IN NUMBER,
380 p_freeze_flag IN VARCHAR2 ,
381 p_distribution_method IN VARCHAR2 ,
382 p_distribution_dimension IN VARCHAR2 ,
383 p_distribution_hierarchy IN VARCHAR2 ,
384 p_description IN VARCHAR2 ,
385 p_approval_required_flag IN VARCHAR2 ,
386 p_enable_target_flag IN VARCHAR2 ,
387 p_create_inst_mea_flag IN VARCHAR2 ,
388 p_per_data_query_flag IN VARCHAR2 ,
389 p_per_target_query_flag IN VARCHAR2 ,
390 p_approver_type IN VARCHAR2 ,
391 p_overwrite_custm IN VARCHAR2 ,
392 p_overwrite_ws_data IN VARCHAR2 ,
393 p_insert_type IN VARCHAR2,
394 p_distribute_type IN VARCHAR2,
395 p_currency_flag IN VARCHAR2,
396 p_view_type IN VARCHAR2,
397 p_business_area_id IN NUMBER,
398 p_multiple_submissions_flag IN VARCHAR2
399 )
400 IS
401 l_template_type zpb_dc_objects.object_type%TYPE;
402 l_status zpb_dc_objects.status%TYPE;
403 l_approver_type zpb_dc_objects.approver_type%TYPE;
404 l_instance_flag VARCHAR2(20);
405 l_copy_target_data_flag VARCHAR2(1);
406
407 --PRAGMA autonomous_transaction;
408
409 BEGIN
410
411
412
413 -- These are for worksheets --
414 l_template_type := 'W';
415 l_approver_type := 'DISTRIBUTOR'; -- no multiple distributor/approvers
416 l_status := 'DISTRIBUTION_PENDING';
417
418 -- Update 'M' template with changes in 'E' for manual dist --
419 IF (p_distribute_type = 'MANUAL' and p_object_type = 'E') THEN
420
421 UPDATE ZPB_DC_OBJECTS
422 SET TEMPLATE_NAME = p_template_name,
423 DESCRIPTION = p_description,
424 DATAENTRY_OBJ_PATH = p_dataentry_obj_path,
425 -- These 2 lines are commented out because they are already updated
426 -- before distribution wf starts
427 --DATAENTRY_OBJ_NAME = p_dataentry_obj_name,
428 TARGET_OBJ_PATH = p_target_obj_path,
429 --TARGET_OBJ_NAME = p_target_obj_name,
430 INSTRUCTION_TEXT_ID = p_instruction_text_id,
431 DEADLINE_DATE = p_deadline_date,
432 FREEZE_FLAG = p_freeze_flag,
433 APPROVAL_REQUIRED_FLAG = p_approval_required_flag,
434 DISTRIBUTION_DIMENSION = p_distribution_dimension,
435 DISTRIBUTION_METHOD = p_distribution_method,
436 DISTRIBUTION_HIERARCHY = p_distribution_hierarchy,
437 APPROVER_TYPE = p_approver_type,
438 ENABLE_TARGET_FLAG = p_enable_target_flag,
439 CREATE_INSTANCE_MEASURES_FLAG = p_create_inst_mea_flag,
440 MULTIPLE_SUBMISSIONS_FLAG = p_multiple_submissions_flag,
441 LAST_UPDATED_BY = fnd_global.USER_ID,
442 LAST_UPDATE_DATE = SYSDATE,
443 LAST_UPDATE_LOGIN = fnd_global.LOGIN_ID
444 WHERE TEMPLATE_ID = p_template_id
445 AND OBJECT_TYPE = 'M';
446 END IF;
447
448 IF (p_object_type <> 'W') THEN
449 -- Ater the distribution, the status of templates changes --
450 UPDATE ZPB_DC_OBJECTS
451 SET STATUS = 'DISTRIBUTED',
452 DISTRIBUTION_DATE = SYSDATE,
453 LAST_UPDATED_BY = fnd_global.USER_ID,
454 LAST_UPDATE_DATE = SYSDATE,
455 LAST_UPDATE_LOGIN = fnd_global.LOGIN_ID
456 WHERE TEMPLATE_ID = p_template_id
457 AND OBJECT_TYPE in ('M','E');
458 END IF;
459
460
461 -- if overwrite, reload the data from template --
462 IF (p_overwrite_ws_data = 'OVERWRITE') THEN
463 l_instance_flag := 'Y';
464 ELSE
465 l_instance_flag := 'N';
466 END IF;
467 FOR c1 in (SELECT 1 from zpb_dc_objects WHERE object_id = p_object_id
468 and create_instance_measures_flag = 'Y')
469 LOOP
470 l_instance_flag := 'Y';
471 END LOOP;
472
473 -- l_copy_target_data_flag gets populated --
474 IF (p_enable_target_flag = 'Y') THEN
475 l_copy_target_data_flag := 'Y';
476 ELSE
477 l_copy_target_data_flag := 'N';
478 END IF;
479
480 IF p_insert_type = 'Insert' THEN
481 -- Populate Distributors tables --
482
483 Populate_Distributors(
484 p_object_id => p_object_id,
485 p_distributor_user_id => p_distributor_user_id,
486 p_approver_type => l_approver_type);
487 INSERT INTO ZPB_DC_OBJECTS (
488 OBJECT_ID ,
489 TEMPLATE_ID ,
490 AC_TEMPLATE_ID ,
491 ANALYSIS_CYCLE_ID ,
492 AC_INSTANCE_ID ,
493 GENERATE_TEMPLATE_TASK_ID,
494 OBJECT_USER_ID ,
495 DISTRIBUTOR_USER_ID ,
496 DISTRIBUTION_DATE ,
497 OBJECT_TYPE ,
498 TEMPLATE_NAME ,
499 DATAENTRY_OBJ_PATH ,
500 DATAENTRY_OBJ_NAME ,
501 TARGET_OBJ_PATH ,
502 TARGET_OBJ_NAME ,
503 STATUS ,
504 DEADLINE_DATE ,
505 INSTRUCTION_TEXT_ID ,
506 FREEZE_FLAG ,
507 DISTRIBUTION_METHOD ,
508 DISTRIBUTION_DIMENSION ,
509 DISTRIBUTION_HIERARCHY ,
510 DESCRIPTION ,
511 APPROVAL_REQUIRED_FLAG ,
512 ENABLE_TARGET_FLAG ,
513 APPROVER_TYPE ,
514 COPY_INSTANCE_DATA_FLAG ,
515 COPY_TARGET_DATA_FLAG ,
516 CREATE_INSTANCE_MEASURES_FLAG,
517 PERSONAL_DATA_QUERY_FLAG,
518 PERSONAL_TARGET_QUERY_FLAG,
519 CREATE_SOLVE_PROGRAM_FLAG,
520 COPY_SOURCE_TYPE_FLAG,
521 LAST_UPDATE_DATE ,
522 LAST_UPDATED_BY ,
523 LAST_UPDATE_LOGIN ,
524 CREATION_DATE ,
525 CREATED_BY,
526 CURRENCY_FLAG,
527 VIEW_TYPE,
528 BUSINESS_AREA_ID,
529 MULTIPLE_SUBMISSIONS_FLAG)
530 VALUES (
531 p_object_id ,
532 p_template_id ,
533 p_ac_template_id ,
534 p_analysis_cycle_id ,
535 p_ac_instance_id ,
536 p_generate_template_task_id,
537 p_object_user_id ,
538 p_distributor_user_id ,
539 SYSDATE ,
540 l_template_type ,
541 p_template_name ,
542 p_dataentry_obj_path ,
543 p_dataentry_obj_name ,
544 p_target_obj_path ,
545 p_target_obj_name ,
546 l_status ,
547 p_deadline_date ,
548 p_instruction_text_id ,
549 p_freeze_flag ,
550 p_distribution_method ,
551 p_distribution_dimension ,
552 p_distribution_hierarchy ,
553 p_description ,
554 p_approval_required_flag,
555 p_enable_target_flag ,
556 l_approver_type ,
557 'Y' ,
558 l_copy_target_data_flag,
559 'Y',
560 'N',
561 'N',
562 'Y',
563 'Y',
564 SYSDATE ,
565 fnd_global.user_id ,
566 fnd_global.LOGIN_ID,
567 SYSDATE ,
568 fnd_global.user_id,
569 p_currency_flag,
570 p_view_type,
571 p_business_area_id,
572 p_multiple_submissions_flag);
573 ELSE -- update
574 -- Update distributors table
575 UPDATE ZPB_DC_DISTRIBUTORS
576 SET DISTRIBUTION_DATE = SYSDATE,
577 APPROVER_TYPE = l_approver_type,
578 LAST_UPDATE_LOGIN = fnd_global.LOGIN_ID,
579 LAST_UPDATED_BY = fnd_global.user_id,
580 LAST_UPDATE_DATE = SYSDATE,
581 CREATED_BY = fnd_global.user_id,
582 CREATION_DATE = SYSDATE
583 WHERE object_id = p_object_id;
584 UPDATE ZPB_DC_OBJECTS
585 SET DISTRIBUTOR_USER_ID = p_distributor_user_id,
586 DISTRIBUTION_DATE = SYSDATE,
587 -- data overwrite or not
588 DEADLINE_DATE = p_deadline_date,
589 STATUS = l_status,
590 FREEZE_FLAG = 'N',
591 COPY_INSTANCE_DATA_FLAG = l_instance_flag,
592 --WHO columns
593 LAST_UPDATE_DATE = SYSDATE,
594 LAST_UPDATED_BY = fnd_global.user_id,
595 LAST_UPDATE_LOGIN = fnd_global.LOGIN_ID
596 WHERE object_id = p_object_id;
597
598 END IF; -- insert or update
599
600 --COMMIT WORK;
601 exception
602 when others then
603 fnd_file.PUT_LINE(FND_FILE.LOG, sqlcode);
604 END Distribute;
605
606 /*------------------- End Private Procedures ---------------*/
607
608
609 /*=========================================================================+
610 | PROCEDURE Generate_Template_CP
611 |
612 | DESCRIPTION
613 | Procedure calls Generate_Template procedure and pass in necessary
614 | parameters.
615 |
616 +=========================================================================*/
617 PROCEDURE Generate_Template_CP
618 (
619 errbuf OUT NOCOPY VARCHAR2,
620 retcode OUT NOCOPY VARCHAR2,
621 --
622 p_task_id IN NUMBER,
623 p_ac_id IN NUMBER,
624 p_instance_id IN NUMBER)
625 IS
626 --
627 l_api_name CONSTANT VARCHAR2(30) := 'Generate_Template_CP';
628 l_api_version CONSTANT NUMBER := 1.0 ;
629 --
630 l_error_api_name VARCHAR2(2000);
631 l_return_status VARCHAR2(1) ;
632 l_msg_count NUMBER ;
633 l_msg_data VARCHAR2(2000) ;
634 l_msg_index_out NUMBER;
635 --
636
637 BEGIN
638
639 Generate_Template(
640 p_api_version => 1.0,
641 p_init_msg_list => FND_API.G_TRUE,
642 p_commit => FND_API.G_FALSE,
643 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
644 x_return_status => l_return_status,
645 x_msg_count => l_msg_count,
646 x_msg_data => l_msg_data,
647 --
648 p_task_id => p_task_id,
649 p_ac_id => p_ac_id,
650 p_instance_id => p_instance_id);
651
652 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
653 raise FND_API.G_EXC_ERROR;
654 END IF;
655
656 retcode := '0';
657 COMMIT;
658 RETURN;
659
660 EXCEPTION
661
662 WHEN FND_API.G_EXC_ERROR THEN
663 retcode := '2' ;
664 errbuf:=substr(sqlerrm, 1, 255);
665
666 WHEN OTHERS THEN
667 retcode := '2' ;
668 errbuf:=substr(sqlerrm, 1, 255);
669 END Generate_Template_CP ;
670
671
672 /*=========================================================================+
673 | PROCEDURE Generate_Template
674 |
675 | DESCRIPTION
676 | Procedure retrieves parameters defined in Generate Template Task,
677 | then creates 3 records in ZPB_DC_OBJECTS - one as the
678 | read-only master version, one as an editable template v
679 | for controller to edit layout and other template properties and data
680 | and one for controller to edit worksheet data and target plus layout.
681 | Procedure also creates an empty record for instruction text as well.
682 |
683 +=========================================================================*/
684
685 PROCEDURE Generate_Template(
686 p_api_version IN NUMBER,
687 p_init_msg_list IN VARCHAR2,
688 p_commit IN VARCHAR2,
689 p_validation_level IN NUMBER,
690 x_return_status OUT NOCOPY VARCHAR2,
691 x_msg_count OUT NOCOPY NUMBER,
692 x_msg_data OUT NOCOPY VARCHAR2,
693 --
694 p_task_id IN NUMBER,
695 p_ac_id IN NUMBER,
696 p_instance_id IN NUMBER)
697 IS
698 l_api_name CONSTANT VARCHAR2(30) := 'Generate_Template';
699 l_api_version CONSTANT NUMBER := 1.0;
700 l_return_status VARCHAR2(1);
701 l_msg_count NUMBER;
702 l_msg_data VARCHAR2(2000);
703
704 l_param_name zpb_task_parameters.name%TYPE;
705 l_param_value zpb_task_parameters.value%TYPE;
706 l_instance_name zpb_analysis_cycle_instances.instance_description%TYPE;
707 l_template_id zpb_dc_objects.template_id%TYPE;
708 l_ac_template_id zpb_dc_objects.ac_template_id%TYPE;
709 l_object_m_id zpb_dc_objects.object_id%TYPE;
710 l_object_e_id zpb_dc_objects.object_id%TYPE;
711 l_object_c_id zpb_dc_objects.object_id%TYPE;
712 l_template_name zpb_dc_objects.template_name%TYPE;
713 l_dataentry_path zpb_dc_objects.dataentry_obj_path%TYPE;
714 l_dataentry_name zpb_dc_objects.dataentry_obj_name%TYPE;
715 l_target_path zpb_dc_objects.target_obj_path%TYPE;
716 l_target_name zpb_dc_objects.target_obj_name%TYPE;
717 l_use_last_reviewed zpb_dc_objects.use_last_reviewed%TYPE;
718 l_dist_dim zpb_dc_objects.distribution_dimension%TYPE;
719 l_dist_hier zpb_dc_objects.distribution_hierarchy%TYPE;
720 l_dist_method zpb_dc_objects.distribution_method%TYPE;
721 l_approval_req_flag zpb_dc_objects.approval_required_flag%TYPE;
722 l_wait_review_flag zpb_dc_objects.wait_for_review_flag%TYPE;
723 l_review_complete_flag zpb_dc_objects.review_complete_flag%TYPE;
724 l_enable_target_flag zpb_dc_objects.enable_target_flag%TYPE;
725 l_copy_target_data_flag zpb_dc_objects.copy_target_data_flag%TYPE;
726 l_status zpb_dc_objects.status%TYPE;
727 l_short_instr_text zpb_dc_instruction_text.short_text%TYPE;
728 l_viewType zpb_dc_objects.view_type%TYPE;
729 l_currency_flag zpb_dc_objects.currency_flag%TYPE;
730 l_bus_area_id zpb_dc_objects.business_area_id%TYPE;
731 l_multiple_submissions_flag zpb_dc_objects.multiple_submissions_flag%TYPE;
732
733 l_ac_count NUMBER;
734 l_view_value VARCHAR2(100);
735 l_currency_param_id NUMBER;
736 l_entered NUMBER;
737 l_have_entered_currency NUMBER;
738 l_last_ac_id NUMBER;
739 l_count_changed_dim NUMBER;
740 l_master_exists NUMBER;
741
742
743 CURSOR l_task_params_csr IS
744 SELECT name,
745 value
746 FROM zpb_task_parameters
747 WHERE task_id = p_task_id;
748
749 CURSOR l_ac_param_val_cursor IS
750 SELECT value FROM ZPB_AC_PARAM_VALUES
751 WHERE ANALYSIS_CYCLE_ID = p_ac_id
752 AND PARAM_ID = l_currency_param_id;
753
754 CURSOR c_bus_area IS
755 SELECT business_area_id FROM zpb_analysis_cycles
756 WHERE ANALYSIS_CYCLE_ID = p_ac_id;
757
758 --PRAGMA autonomous_transaction;
759
760 BEGIN
761
762 SAVEPOINT Generate_Template_Pvt ;
763
764 IF NOT FND_API.Compatible_API_Call ( l_api_version,
765 p_api_version,
766 l_api_name,
767 G_PKG_NAME )
768 THEN
769 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
770 END IF;
771
772
773 IF FND_API.to_Boolean ( p_init_msg_list ) THEN
774 FND_MSG_PUB.initialize ;
775 END IF;
776
777 x_return_status := FND_API.G_RET_STS_SUCCESS ;
778
779 -- Initialize the parameters
780
781 -- API Body
782
783 SELECT ZPB_TEMPLATE_WS_ID_SEQ.nextval
784 INTO l_object_m_id
785 FROM dual;
786 --Template id should be from object_id of the M record
787 l_template_id := l_object_m_id;
788
789 SELECT ZPB_TEMPLATE_WS_ID_SEQ.nextval
790 INTO l_object_e_id
791 FROM dual;
792
793 SELECT ZPB_TEMPLATE_WS_ID_SEQ.nextval
794 INTO l_object_c_id
795 FROM dual;
796
797 -- Get the instance name to append to template name
798 SELECT instance_description
799 INTO l_instance_name
800 FROM zpb_analysis_cycle_instances
801 WHERE instance_ac_id = p_instance_id;
802
803 SELECT tag INTO l_currency_param_id
804 FROM fnd_lookup_values_vl
805 WHERE LOOKUP_CODE = 'BUSINESS_PROCESS_CURRENCY'
806 and LOOKUP_TYPE = 'ZPB_PARAMS';
807
808 SELECT tag INTO l_entered
809 FROM fnd_lookup_values_vl
810 WHERE LOOKUP_CODE = 'LOAD_ENTERED'
811 and LOOKUP_TYPE = 'ZPB_PARAMS';
812
813 l_currency_flag := 'N';
814 l_viewType := 'BASE';
815
816 open c_bus_area;
817 fetch c_bus_area into l_bus_area_id;
818 close c_bus_area;
819
820 open l_ac_param_val_cursor;
821 fetch l_ac_param_val_cursor into l_view_value;
822
823 if l_ac_param_val_cursor %FOUND then
824 l_currency_flag := 'Y';
825
826 SELECT count(*) into l_have_entered_currency FROM ZPB_AC_PARAM_VALUES
827 WHERE ANALYSIS_CYCLE_ID = p_ac_id
828 AND PARAM_ID = l_entered AND value = 'Y';
829
830 if l_have_entered_currency > 0 then
831 l_viewType := 'ENTERED';
832 end if;
833
834 close l_ac_param_val_cursor;
835 end if;
836
837
838 -- Get parameters from Generate Template Process
839 FOR l_task_params_row_rec IN l_task_params_csr
840 LOOP
841 l_param_name := l_task_params_row_rec.name;
842 l_param_value := l_task_params_row_rec.value;
843
844 /* Template id should be from object_id of the M record
845 this template id should be ac_template_id */
846 IF l_param_name='TEMPLATE_ID' THEN
847 l_ac_template_id := l_param_value;
848 ELSIF l_param_name='TEMPLATE_NAME' THEN
849 l_template_name := l_param_value;
850 IF (l_instance_name is not null) THEN
851 l_template_name := l_template_name ||': '||l_instance_name;
852 END IF;
853 ELSIF l_param_name='TEMPLATE_LAYOUT' THEN
854 IF l_param_value='DEFAULT_LAYOUT' THEN
855 l_use_last_reviewed:='N';
856 ELSE
857 l_use_last_reviewed:='Y';
858 END IF;
859 ELSIF l_param_name='TEMPLATE_DISTRIBUTE_DIMENSION' THEN
860 l_dist_dim := l_param_value;
861 ELSIF l_param_name='TEMPLATE_DISTRIBUTE_HIERARCHY' THEN
862 l_dist_hier := l_param_value;
863 ELSIF l_param_name='TEMPLATE_DISTRIBUTION_METHOD' THEN
864 l_dist_method := l_param_value;
865 ELSIF l_param_name='TEMPLATE_DATAENTRY_OBJ_PATH' THEN
866 l_dataentry_path := l_param_value;
867 ELSIF l_param_name='TEMPLATE_TARGET_OBJ_PATH' THEN
868 l_target_path := l_param_value;
869 ELSIF l_param_name='TEMPLATE_TARGET_OBJ_NAME' THEN
870 l_target_name := l_param_value;
871 ELSIF l_param_name='TEMPLATE_DATAENTRY_OBJ_NAME' THEN
872 l_dataentry_name := l_param_value;
873 ELSIF l_param_name='TEMPLATE_WAIT_FOR_REVIEW' THEN
874 l_wait_review_flag := l_param_value;
875 IF l_wait_review_flag = 'Y' THEN
876 l_review_complete_flag := 'N';
877 l_status := 'REVIEW_PENDING';
878 ELSE
879 l_review_complete_flag := 'Y';
880 l_status := 'REVIEW_COMPLETED';
881 END IF;
882 ELSIF l_param_name='TEMPLATE_APPROVAL_REQUIRED' THEN
883 l_approval_req_flag := l_param_value;
884 IF (l_dist_method = 'CASCADE_DISTRIBUTION') THEN
885 l_approval_req_flag := 'Y';
886 END IF;
887 ELSIF l_param_name = 'TEMPLATE_ENABLE_TARGET' THEN
888 l_enable_target_flag := l_param_value;
889 IF (l_enable_target_flag = 'Y') THEN
890 l_copy_target_data_flag := 'Y';
891 ELSE
892 l_copy_target_data_flag := 'N';
893 END IF;
894 ELSIF l_param_name = 'TEMPLATE_ALLOW_MULTIPLE_SUBS' THEN
895 l_multiple_submissions_flag := l_param_value;
896 END IF;
897
898 END LOOP;
899
900 IF (l_use_last_reviewed = 'Y') THEN
901 /* Only when a bp already has a template does 'user last
902 reviewed' hold true */
903 /* SELECT count(*)
904 INTO l_ac_count
905 FROM zpb_dc_objects
906 WHERE analysis_cycle_id = p_ac_id
907 AND ac_template_id = l_ac_template_id;*/
908
909 /* IF (l_ac_count <> 0) THEN
910 SELECT max(ac_instance_id)
911 INTO l_last_ac_id
912 FROM zpb_dc_objects
913 WHERE analysis_cycle_id = p_ac_id
914 AND ac_template_id = l_ac_template_id;*/
915
916 /* IF (l_ac_count <> 0) THEN*/
917 SELECT max(a2.analysis_cycle_id)
918 INTO l_last_ac_id
919 FROM zpb_analysis_cycles a1, zpb_analysis_cycles a2
920 WHERE a1.analysis_cycle_id = p_ac_id
921 AND a2.status_code = 'ENABLE_TASK_OLD'
922 AND a1.current_instance_id = a2.current_instance_id
923 AND a2.analysis_cycle_id <> p_ac_id;
924
925 SELECT count(*)
926 INTO l_count_changed_dim
927 FROM (
928 (SELECT dimension_name
929 FROM zpb_cycle_model_dimensions
930 WHERE analysis_cycle_id = l_last_ac_id
931 MINUS
932 SELECT dimension_name
933 FROM zpb_cycle_model_dimensions
934 WHERE analysis_cycle_id = p_ac_id
935 )
936 UNION
937 (SELECT dimension_name
938 FROM zpb_cycle_model_dimensions
939 WHERE analysis_cycle_id = p_ac_id
940 MINUS
941 SELECT dimension_name
942 FROM zpb_cycle_model_dimensions
943 WHERE analysis_cycle_id = l_last_ac_id)
944 );
945
946 /* IF (l_count_changed_dim = 0)THEN
947 SELECT dataentry_obj_name, target_obj_name
948 INTO l_dataentry_name, l_target_name
949 FROM zpb_dc_objects
950 WHERE object_type = 'M'
951 AND ac_template_id = l_ac_template_id
952 AND ac_instance_id IN
953 (SELECT max(ac_instance_id)
954 FROM zpb_dc_objects
955 WHERE analysis_cycle_id = p_ac_id
956 AND ac_template_id = l_ac_template_id);
957 END IF;
958 END IF;
959 END IF;
960 */
961
962 IF (l_count_changed_dim = 0)THEN
963 -- ensure there is a master records to copy from
964 SELECT count(*) INTO l_master_exists
965 FROM zpb_dc_objects
966 WHERE object_type = 'M'
967 AND ac_template_id = l_ac_template_id
968 AND analysis_cycle_id = l_last_ac_id;
969
970 IF (l_master_exists > 0)THEN
971 SELECT
972 dataentry_obj_path,dataentry_obj_name,target_obj_path,target_obj_name
973 INTO l_dataentry_path,l_dataentry_name,l_target_path,l_target_name
974 FROM zpb_dc_objects
975 WHERE object_type = 'M'
976 AND ac_template_id = l_ac_template_id
977 AND analysis_cycle_id = l_last_ac_id;
978 END IF;
979 END IF;
980 /* END IF;*/
981 END IF;
982
983
984 -- Get the short text from fnd messages
985 -- FND_MESSAGE.SET_NAME('ZPB', 'ZPB_DC_INSTR_TEXT_MSG');
986 -- l_short_instr_text := FND_MESSAGE.GET;
987
988 l_short_instr_text := '';
989 /* Insert a record for the Master Template,
990 this template is updated when distribute and submit, the changes from E template*/
991 INSERT INTO ZPB_DC_OBJECTS (
992 OBJECT_ID,
993 TEMPLATE_ID,
994 AC_TEMPLATE_ID,
995 ANALYSIS_CYCLE_ID,
996 AC_INSTANCE_ID,
997 GENERATE_TEMPLATE_TASK_ID,
998 OBJECT_USER_ID,
999 DISTRIBUTOR_USER_ID,
1000 OBJECT_TYPE,
1001 STATUS,
1002 TEMPLATE_NAME,
1003 USE_LAST_REVIEWED,
1004 DATAENTRY_OBJ_PATH,
1005 DATAENTRY_OBJ_NAME,
1006 TARGET_OBJ_PATH,
1007 TARGET_OBJ_NAME,
1008 APPROVAL_REQUIRED_FLAG,
1009 DISTRIBUTION_DIMENSION,
1010 DISTRIBUTION_METHOD,
1011 DISTRIBUTION_HIERARCHY,
1012 INSTRUCTION_TEXT_ID,
1013 WAIT_FOR_REVIEW_FLAG,
1014 FREEZE_FLAG,
1015 REVIEW_COMPLETE_FLAG,
1016 ENABLE_TARGET_FLAG,
1017 CREATE_INSTANCE_MEASURES_FLAG,
1018 COPY_INSTANCE_DATA_FLAG,
1019 COPY_TARGET_DATA_FLAG,
1020 PERSONAL_DATA_QUERY_FLAG,
1021 PERSONAL_TARGET_QUERY_FLAG,
1022 CREATE_SOLVE_PROGRAM_FLAG,
1023 COPY_SOURCE_TYPE_FLAG,
1024 LAST_UPDATE_DATE,
1025 LAST_UPDATED_BY,
1026 CREATION_DATE,
1027 CREATED_BY,
1028 LAST_UPDATE_LOGIN,
1029 CURRENCY_FLAG,
1030 VIEW_TYPE,
1031 BUSINESS_AREA_ID,
1032 MULTIPLE_SUBMISSIONS_FLAG)
1033 VALUES (
1034 l_object_m_id,
1035 l_template_id,
1036 l_ac_template_id,
1037 p_ac_id,
1038 p_instance_id,
1039 p_task_id,
1040 fnd_global.user_id,
1041 -100,
1042 'M',
1043 l_status,
1044 l_template_name,
1045 l_use_last_reviewed,
1046 l_dataentry_path,
1047 l_dataentry_name,
1048 l_target_path,
1049 l_target_name,
1050 l_approval_req_flag,
1051 l_dist_dim,
1052 l_dist_method,
1053 l_dist_hier,
1054 l_object_m_id,
1055 l_wait_review_flag,
1056 'N',
1057 l_review_complete_flag,
1058 l_enable_target_flag,
1059 'Y',
1060 'Y',
1061 l_copy_target_data_flag,
1062 'N',
1063 'N',
1064 'Y',
1065 'Y',
1066 SYSDATE,
1067 fnd_global.user_id,
1068 SYSDATE,
1069 fnd_global.user_id,
1070 fnd_global.LOGIN_ID,
1071 l_currency_flag,
1072 l_viewType,
1073 l_bus_area_id,
1074 l_multiple_submissions_flag);
1075
1076 -- Insert an empty record in instruction text M template
1077 INSERT INTO ZPB_DC_INSTRUCTION_TEXT (
1078 INSTRUCTION_TEXT_ID ,
1079 LONG_TEXT ,
1080 SHORT_TEXT ,
1081 LAST_UPDATE_LOGIN ,
1082 LAST_UPDATE_DATE ,
1083 LAST_UPDATED_BY ,
1084 CREATION_DATE ,
1085 CREATED_BY) Values (
1086 l_object_m_id ,
1087 '' ,
1088 l_short_instr_text ,
1089 fnd_global.LOGIN_ID ,
1090 SYSDATE ,
1091 fnd_global.user_id ,
1092 SYSDATE ,
1093 fnd_global.user_id
1094 );
1095
1096 /* Generate an editable version of the template.
1097 controller make changes then distribute. so the changes be copied
1098 to M template when distribute*/
1099 INSERT INTO ZPB_DC_OBJECTS (
1100 OBJECT_ID,
1101 TEMPLATE_ID,
1102 AC_TEMPLATE_ID,
1103 ANALYSIS_CYCLE_ID,
1104 AC_INSTANCE_ID,
1105 GENERATE_TEMPLATE_TASK_ID,
1106 OBJECT_USER_ID,
1107 DISTRIBUTOR_USER_ID,
1108 OBJECT_TYPE,
1109 STATUS,
1110 TEMPLATE_NAME,
1111 USE_LAST_REVIEWED,
1112 DATAENTRY_OBJ_PATH,
1113 DATAENTRY_OBJ_NAME,
1114 TARGET_OBJ_PATH,
1115 TARGET_OBJ_NAME,
1116 APPROVAL_REQUIRED_FLAG,
1117 DISTRIBUTION_DIMENSION,
1118 DISTRIBUTION_METHOD,
1119 DISTRIBUTION_HIERARCHY,
1120 INSTRUCTION_TEXT_ID,
1121 WAIT_FOR_REVIEW_FLAG,
1122 FREEZE_FLAG,
1123 REVIEW_COMPLETE_FLAG,
1124 ENABLE_TARGET_FLAG,
1125 CREATE_INSTANCE_MEASURES_FLAG,
1126 COPY_INSTANCE_DATA_FLAG,
1127 COPY_TARGET_DATA_FLAG,
1128 PERSONAL_DATA_QUERY_FLAG,
1129 PERSONAL_TARGET_QUERY_FLAG,
1130 CREATE_SOLVE_PROGRAM_FLAG,
1131 COPY_SOURCE_TYPE_FLAG,
1132 LAST_UPDATE_DATE,
1133 LAST_UPDATED_BY,
1134 CREATION_DATE,
1135 CREATED_BY,
1136 LAST_UPDATE_LOGIN,
1137 CURRENCY_FLAG,
1138 VIEW_TYPE,
1139 BUSINESS_AREA_ID,
1140 MULTIPLE_SUBMISSIONS_FLAG)
1141 VALUES (
1142 l_object_e_id,
1143 l_template_id,
1144 l_ac_template_id,
1145 p_ac_id,
1146 p_instance_id,
1147 p_task_id,
1148 fnd_global.user_id,
1149 -100,
1150 'E',
1151 l_status,
1152 l_template_name,
1153 l_use_last_reviewed,
1154 l_dataentry_path,
1155 l_dataentry_name,
1156 l_target_path,
1157 l_target_name,
1158 l_approval_req_flag,
1159 l_dist_dim,
1160 l_dist_method,
1161 l_dist_hier,
1162 l_object_e_id,
1163 l_wait_review_flag,
1164 'N',
1165 l_review_complete_flag,
1166 l_enable_target_flag,
1167 'Y',
1168 'Y',
1169 l_copy_target_data_flag,
1170 'N',
1171 'N',
1172 'Y',
1173 'Y',
1174 SYSDATE,
1175 fnd_global.user_id,
1176 SYSDATE,
1177 fnd_global.user_id,
1178 fnd_global.LOGIN_ID,
1179 l_currency_flag,
1180 l_viewType,
1181 l_bus_area_id,
1182 l_multiple_submissions_flag);
1183
1184
1185 -- Insert an empty record in instruction text for editable template
1186 INSERT INTO ZPB_DC_INSTRUCTION_TEXT (
1187 INSTRUCTION_TEXT_ID ,
1188 LONG_TEXT ,
1189 SHORT_TEXT ,
1190 LAST_UPDATE_LOGIN ,
1191 LAST_UPDATE_DATE ,
1192 LAST_UPDATED_BY ,
1193 CREATION_DATE ,
1194 CREATED_BY) Values (
1195 l_object_e_id ,
1196 '' ,
1197 l_short_instr_text ,
1198 fnd_global.LOGIN_ID ,
1199 SYSDATE ,
1200 fnd_global.user_id ,
1201 SYSDATE ,
1202 fnd_global.user_id
1203 );
1204
1205
1206 /* Generate a controller worksheet, need to set measures flag
1207 for him to work on his own ws */
1208 INSERT INTO ZPB_DC_OBJECTS (
1209 OBJECT_ID,
1210 TEMPLATE_ID,
1211 AC_TEMPLATE_ID,
1212 ANALYSIS_CYCLE_ID,
1213 AC_INSTANCE_ID,
1214 GENERATE_TEMPLATE_TASK_ID,
1215 OBJECT_USER_ID,
1216 DISTRIBUTOR_USER_ID,
1217 OBJECT_TYPE,
1218 TEMPLATE_NAME,
1219 STATUS,
1220 USE_LAST_REVIEWED,
1221 DATAENTRY_OBJ_PATH,
1222 DATAENTRY_OBJ_NAME,
1223 TARGET_OBJ_PATH,
1224 TARGET_OBJ_NAME,
1225 APPROVAL_REQUIRED_FLAG,
1226 DISTRIBUTION_DIMENSION,
1227 DISTRIBUTION_METHOD,
1228 DISTRIBUTION_HIERARCHY,
1229 INSTRUCTION_TEXT_ID,
1230 WAIT_FOR_REVIEW_FLAG,
1231 FREEZE_FLAG,
1232 REVIEW_COMPLETE_FLAG,
1233 ENABLE_TARGET_FLAG,
1234 CREATE_INSTANCE_MEASURES_FLAG,
1235 COPY_INSTANCE_DATA_FLAG,
1236 COPY_TARGET_DATA_FLAG,
1237 PERSONAL_DATA_QUERY_FLAG,
1238 PERSONAL_TARGET_QUERY_FLAG,
1239 CREATE_SOLVE_PROGRAM_FLAG,
1240 COPY_SOURCE_TYPE_FLAG,
1241 LAST_UPDATE_DATE,
1242 LAST_UPDATED_BY,
1243 CREATION_DATE,
1244 CREATED_BY,
1245 LAST_UPDATE_LOGIN,
1246 CURRENCY_FLAG,
1247 VIEW_TYPE,
1248 BUSINESS_AREA_ID,
1249 MULTIPLE_SUBMISSIONS_FLAG)
1250 VALUES (
1251 l_object_c_id,
1252 l_template_id,
1253 l_ac_template_id,
1254 p_ac_id,
1255 p_instance_id,
1256 p_task_id,
1257 fnd_global.user_id,
1258 -100,
1259 'C',
1260 l_template_name,
1261 'DISTRIBUTION_PENDING',
1262 l_use_last_reviewed,
1263 l_dataentry_path,
1264 l_dataentry_name,
1265 l_target_path,
1266 l_target_name,
1267 l_approval_req_flag,
1268 l_dist_dim,
1269 l_dist_method,
1270 l_dist_hier,
1271 l_object_m_id,
1272 l_wait_review_flag,
1273 'N',
1274 l_review_complete_flag,
1275 l_enable_target_flag,
1276 'Y',
1277 'Y',
1278 l_copy_target_data_flag,
1279 'N',
1280 'N',
1281 'Y',
1282 'Y',
1283 SYSDATE,
1284 fnd_global.user_id,
1285 SYSDATE,
1286 fnd_global.user_id,
1287 fnd_global.LOGIN_ID,
1288 l_currency_flag,
1289 l_viewType,
1290 l_bus_area_id,
1291 l_multiple_submissions_flag);
1292
1293 FND_MSG_PUB.Count_And_Get ( p_count => x_msg_count,
1294 p_data => x_msg_data );
1295 --COMMIT WORK;
1296
1297 EXCEPTION
1298
1299 WHEN FND_API.G_EXC_ERROR THEN
1300
1301 ROLLBACK TO Generate_Template_Pvt ;
1302 x_return_status := FND_API.G_RET_STS_ERROR;
1303 FND_MSG_PUB.Count_And_Get ( p_count => x_msg_count,
1304 p_data => x_msg_data );
1305
1306 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1307
1308 ROLLBACK TO Generate_Template_Pvt ;
1309 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1310 FND_MSG_PUB.Count_And_Get ( p_count => x_msg_count,
1311 p_data => x_msg_data );
1312 WHEN OTHERS THEN
1313
1314 ROLLBACK TO Generate_Template_Pvt ;
1315 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1316
1317 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
1318 FND_MSG_PUB.Add_Exc_Msg ( G_PKG_NAME,
1319 l_api_name);
1320 END if;
1321
1322 FND_MSG_PUB.Count_And_Get ( p_count => x_msg_count,
1323 p_data => x_msg_data );
1324
1325
1326 END Generate_Template;
1327
1328 /*=========================================================================+
1329 | PROCEDURE Auto_Distribute_CP
1330 |
1331 | DESCRIPTION
1332 | Procedure calls Auto_Distribute procedure and pass in necessary
1333 | parameters.
1334 |
1335 +=========================================================================*/
1336 PROCEDURE Auto_Distribute_CP
1337 (
1338 errbuf OUT NOCOPY VARCHAR2,
1339 retcode OUT NOCOPY VARCHAR2,
1340 --
1341 p_task_id IN NUMBER,
1342 p_template_id IN NUMBER)
1343 IS
1344 --
1345 l_api_name CONSTANT VARCHAR2(30) := 'Auto_Distribute_CP';
1346 l_api_version CONSTANT NUMBER := 1.0 ;
1347 --
1348 l_error_api_name VARCHAR2(2000);
1349 l_return_status VARCHAR2(1) ;
1350 l_msg_count NUMBER ;
1351 l_msg_data VARCHAR2(2000) ;
1352 l_msg_index_out NUMBER;
1353 --
1354 BEGIN
1355 --
1356 Auto_Distribute(
1357 p_api_version => 1.0,
1358 p_init_msg_list => FND_API.G_TRUE,
1359 p_commit => FND_API.G_FALSE,
1360 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
1361 x_return_status => l_return_status,
1362 x_msg_count => l_msg_count,
1363 x_msg_data => l_msg_data,
1364 --
1365 p_task_id => p_task_id,
1366 p_template_id => p_template_id);
1367
1368 if l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1369 raise FND_API.G_EXC_ERROR;
1370 end if;
1371
1372 retcode := '0';
1373 COMMIT;
1374 RETURN;
1375
1376 EXCEPTION
1377
1378 WHEN FND_API.G_EXC_ERROR THEN
1379 retcode := '2' ;
1380 errbuf:=substr(sqlerrm, 1, 255);
1381
1382 WHEN OTHERS THEN
1383 retcode := '2' ;
1384 errbuf:=substr(sqlerrm, 1, 255);
1385 END Auto_Distribute_CP ;
1386
1387
1388 /*=========================================================================+
1389 | PROCEDURE Auto_Distribute
1390 |
1391 |
1392 | DESCRIPTION
1393 | Procedure creates a new worksheet for each user
1394 | specified on the distribute template task ui. It copies the
1395 | Master template to the user's worksheet folder.
1396 |
1397 |
1398 +=========================================================================*/
1399
1400 PROCEDURE Auto_Distribute(
1401 p_api_version IN NUMBER,
1402 p_init_msg_list IN VARCHAR2,
1403 p_commit IN VARCHAR2,
1404 p_validation_level IN NUMBER,
1405 x_return_status OUT NOCOPY VARCHAR2,
1406 x_msg_count OUT NOCOPY NUMBER,
1407 x_msg_data OUT NOCOPY VARCHAR2,
1408 --
1409 p_task_id IN NUMBER,
1410 p_template_id IN NUMBER
1411 )
1412 IS
1413 l_api_name CONSTANT VARCHAR2(30) := 'Auto_Distribute';
1414 l_api_version CONSTANT NUMBER := 1.0;
1415 l_return_status VARCHAR2(1);
1416 l_msg_count NUMBER;
1417 l_msg_data VARCHAR2(2000);
1418
1419 l_object_id NUMBER;
1420 l_object_user_id NUMBER;
1421 l_distributor_user_id NUMBER;
1422 l_param_name zpb_task_parameters.name%TYPE;
1423 l_param_value zpb_task_parameters.value%TYPE;
1424 l_recipient_type zpb_task_parameters.value%TYPE;
1425 l_approver_type zpb_dc_objects.approver_type%TYPE;
1426 l_object_type zpb_dc_objects.object_type%TYPE;
1427 l_wait_for_review VARCHAR2(1);
1428 l_review_complete_flag VARCHAR2(1);
1429 l_deadline_date DATE;
1430 l_deadline_type VARCHAR2(10);
1431 l_deadline_duration NUMBER;
1432 l_amt INTEGER;
1433 l_buffer VARCHAR2(30);
1434 l_user_id_clob CLOB;
1435 l_lob_length BINARY_INTEGER;
1436 l_ind BINARY_INTEGER;
1437
1438 l_pattern VARCHAR2(1);
1439 l_position BINARY_INTEGER;
1440 l_pattern_position BINARY_INTEGER;
1441
1442 l_user_id NUMBER;
1443 l_user zpb_task_parameters.value%TYPE;
1444 l_insert_type VARCHAR2(10);
1445 l_count NUMBER;
1446 l_resp_key fnd_responsibility.responsibility_key%TYPE;
1447
1448 -- Get the all the parameter values for the task id
1449 CURSOR task_params_csr IS
1450 SELECT name, value
1451 FROM zpb_task_parameters
1452 WHERE task_id = p_task_id;
1453
1454 --Get all the info of this template id and of type M
1455 CURSOR dist_template_csr IS
1456 SELECT ANALYSIS_CYCLE_ID ,
1457 AC_INSTANCE_ID ,
1458 GENERATE_TEMPLATE_TASK_ID,
1459 OBJECT_USER_ID ,
1460 DISTRIBUTOR_USER_ID ,
1461 AC_TEMPLATE_ID ,
1462 TEMPLATE_NAME ,
1463 DATAENTRY_OBJ_PATH ,
1464 DATAENTRY_OBJ_NAME ,
1465 TARGET_OBJ_PATH ,
1466 TARGET_OBJ_NAME ,
1467 INSTRUCTION_TEXT_ID ,
1468 FREEZE_FLAG ,
1469 DISTRIBUTION_METHOD ,
1470 DISTRIBUTION_DIMENSION ,
1471 DISTRIBUTION_HIERARCHY ,
1472 DESCRIPTION ,
1473 APPROVAL_REQUIRED_FLAG ,
1474 ENABLE_TARGET_FLAG ,
1475 CREATE_INSTANCE_MEASURES_FLAG,
1476 PERSONAL_DATA_QUERY_FLAG,
1477 PERSONAL_TARGET_QUERY_FLAG,
1478 CURRENCY_FLAG,
1479 VIEW_TYPE,
1480 BUSINESS_AREA_ID,
1481 MULTIPLE_SUBMISSIONS_FLAG
1482 FROM ZPB_DC_OBJECTS
1483 WHERE TEMPLATE_ID = p_template_id
1484 AND OBJECT_TYPE = 'M';
1485
1486 -- Get all the specified users from the task param table
1487 CURSOR specific_user_csr IS
1488 SELECT value
1489 FROM zpb_task_parameters
1490 WHERE task_id = p_task_id
1491 AND name = 'DISTRIBUTION_SPECIFIED_USERS';
1492
1493 -- Check whether a specific user was distributed to
1494 CURSOR check_exist_csr IS
1495 SELECT count(*)
1496 FROM ZPB_DC_OBJECTS
1497 WHERE TEMPLATE_ID = p_template_id
1498 AND OBJECT_USER_ID = l_user_id;
1499
1500 BEGIN
1501
1502 SAVEPOINT Auto_Distribute_Pvt ;
1503
1504 IF NOT FND_API.Compatible_API_Call ( l_api_version,
1505 p_api_version,
1506 l_api_name,
1507 G_PKG_NAME )
1508 THEN
1509 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
1510 END IF;
1511
1512
1513 IF FND_API.to_Boolean ( p_init_msg_list ) THEN
1514 FND_MSG_PUB.initialize ;
1515 END IF;
1516
1517 x_return_status := FND_API.G_RET_STS_SUCCESS ;
1518
1519 -- Initialize the parameters
1520 l_amt := 255;
1521 l_ind := 1;
1522 l_pattern := ',';
1523 l_position := 1;
1524 l_pattern_position := 1;
1525 l_count := 0;
1526 l_object_type := 'E';
1527
1528 -- API Body
1529
1530 -- Get parameters from Distribute Template Task definition
1531 FOR l_task_params_row_rec IN task_params_csr
1532 LOOP
1533 l_param_name := l_task_params_row_rec.name;
1534 l_param_value := l_task_params_row_rec.value;
1535
1536 IF l_param_name='DISTRIBUTION_APPROVER_TYPE' THEN
1537 l_approver_type := l_param_value;
1538 END IF;
1539
1540 IF l_param_name='DISTRIBUTION_RECIPIENT_TYPE' THEN
1541 l_recipient_type := l_param_value;
1542 IF (l_recipient_type = 'ALL_TOP_LEVEL_OWNERS') THEN
1543 l_recipient_type := 'ALL_TOP_LVL';
1544 ELSIF (l_recipient_type = 'ALL_DATA_OWNERS') THEN
1545 l_recipient_type := 'ALL';
1546 END IF;
1547 END IF;
1548
1549 IF l_param_name='DISTRIBUTION_DEADLINE_TYPE' THEN
1550 l_deadline_type := l_param_value;
1551 END IF;
1552
1553 IF l_param_name='DISTRIBUTION_DEADLINE_DURATION' THEN
1554 l_deadline_duration := l_param_value;
1555 END IF;
1556
1557 IF l_deadline_type = 'DAYS' THEN
1558 l_deadline_date := sysdate + l_deadline_duration;
1559 ELSIF l_deadline_type = 'WEEKS' THEN
1560 l_deadline_date := sysdate + l_deadline_duration*7;
1561 ELSIF l_deadline_type = 'MONTHS' THEN
1562 l_deadline_date := ADD_MONTHS(sysdate,l_deadline_duration);
1563 ELSIF l_deadline_type = 'YEARS' THEN
1564 l_deadline_date := ADD_MONTHS(sysdate,l_deadline_duration*12);
1565 END IF;
1566 END LOOP;
1567
1568 -- Call the following api to get the clob of user ids comma separated
1569 SELECT object_id, object_user_id, wait_for_review_flag, review_complete_flag
1570 INTO l_object_id, l_object_user_id, l_wait_for_review, l_review_complete_flag
1571 FROM zpb_dc_objects
1572 WHERE template_id = p_template_id
1573 AND object_type = 'M';
1574
1575 /* Only the controller can distribute ws automatically
1576 Here we use the defaule manager resp to get the user ids */
1577 l_resp_key := 'ZPB_CONTROLLER_RESP';
1578
1579 -- The insertion of the records
1580 IF l_recipient_type <> 'SPECIFIC_USERS' THEN
1581
1582 get_user_id_clob(
1583 p_api_version => p_api_version,
1584 p_init_msg_list => p_init_msg_list,
1585 p_commit => p_commit,
1586 p_validation_level => p_validation_level,
1587 x_return_status => x_return_status,
1588 x_msg_count => x_msg_count,
1589 x_msg_data => x_msg_data,
1590 --
1591 p_object_id => l_object_id,
1592 p_object_user_id => l_object_user_id,
1593 p_recipient_type => l_recipient_type,
1594 p_resp_key => l_resp_key,
1595 x_user_id_clob => l_user_id_clob);
1596
1597 l_position:= 1;
1598 l_lob_length := dbms_lob.getlength(l_user_id_clob);
1599
1600 IF l_lob_length > 0 THEN
1601 LOOP
1602 l_pattern_position :=
1603 DBMS_LOB.INSTR(l_user_id_clob, l_pattern, l_position, 1);
1604
1605 IF l_pattern_position = 0 THEN
1606 l_amt := l_lob_length - l_position + 1;
1607 ELSE
1608 l_amt := l_pattern_position - l_position;
1609 END IF;
1610
1611 dbms_lob.read (l_user_id_clob, l_amt, l_position, l_buffer);
1612
1613 l_user_id := to_number(l_buffer);
1614 -- Check duplicates
1615 OPEN check_exist_csr;
1616 FETCH check_exist_csr INTO l_count;
1617 CLOSE check_exist_csr;
1618
1619 IF (l_count = 0 ) THEN -- do the insert, otherwise ignore
1620 l_insert_type := 'Insert';
1621
1622 -- Object id is unique - ws id
1623 SELECT ZPB_TEMPLATE_WS_ID_SEQ.nextval
1624 INTO l_object_id
1625 FROM dual;
1626
1627 -- Retrieving the master template row
1628 FOR l_dist_template_row_rec IN dist_template_csr
1629 LOOP -- only one record
1630
1631 /* The dist id is -100 (shared aw). If review is Y, pa opens the ws
1632 (load data from share aw) before the distributee does. Otherwise,
1633 distributee gets data from shared to avoid blank ws */
1634 l_distributor_user_id := l_dist_template_row_rec.distributor_user_id;
1635 IF (l_wait_for_review = 'Y' AND l_review_complete_flag = 'Y') THEN
1636 l_distributor_user_id := l_dist_template_row_rec.object_user_id;
1637 END IF;
1638
1639 Distribute
1640 (
1641 p_object_id => l_object_id,
1642 p_object_type => l_object_type, -- 'E'
1643 p_template_id => p_template_id,
1644 p_ac_template_id => l_dist_template_row_rec.ac_template_id,
1645 p_analysis_cycle_id => l_dist_template_row_rec.analysis_cycle_id,
1646 p_ac_instance_id => l_dist_template_row_rec.ac_instance_id,
1647 p_generate_template_task_id => l_dist_template_row_rec.generate_template_task_id,
1648 p_object_user_id => l_user_id,
1649 p_distributor_user_id => l_distributor_user_id,
1650 p_template_name => l_dist_template_row_rec.template_name,
1651 p_dataentry_obj_path => l_dist_template_row_rec.dataentry_obj_path,
1652 p_dataentry_obj_name => l_dist_template_row_rec.dataentry_obj_name,
1653 p_target_obj_path => l_dist_template_row_rec.target_obj_path,
1654 p_target_obj_name => l_dist_template_row_rec.target_obj_name,
1655 p_deadline_date => l_deadline_date,
1656 p_instruction_text_id => l_dist_template_row_rec.instruction_text_id,
1657 p_freeze_flag => l_dist_template_row_rec.freeze_flag,
1658 p_distribution_method => l_dist_template_row_rec.distribution_method,
1659 p_distribution_dimension => l_dist_template_row_rec.distribution_dimension,
1660 p_distribution_hierarchy => l_dist_template_row_rec.distribution_hierarchy,
1661 p_description => l_dist_template_row_rec.description,
1662 p_approval_required_flag => l_dist_template_row_rec.approval_required_flag,
1663 p_enable_target_flag => l_dist_template_row_rec.enable_target_flag,
1664 p_create_inst_mea_flag => l_dist_template_row_rec.create_instance_measures_flag,
1665 p_per_data_query_flag => l_dist_template_row_rec.personal_data_query_flag,
1666 p_per_target_query_flag => l_dist_template_row_rec.personal_target_query_flag,
1667 p_approver_type => l_approver_type,
1668 p_overwrite_custm => NULL,
1669 p_overwrite_ws_data => NULL,
1670 p_insert_type => l_insert_type,
1671 p_distribute_type => 'AUTO',
1672 p_currency_flag => l_dist_template_row_rec.currency_flag,
1673 p_view_type => l_dist_template_row_rec.view_type,
1674 p_business_area_id => l_dist_template_row_rec.business_area_id,
1675 p_multiple_submissions_flag => l_dist_template_row_rec.multiple_submissions_flag
1676 );
1677 END LOOP; -- Retrieving the master template row
1678 END IF; -- check duplicates
1679
1680 -- get clob
1681 l_position := l_pattern_position+1 ;
1682 IF l_pattern_position = 0 THEN
1683 EXIT;
1684 END IF;
1685
1686 END LOOP; -- clob loop
1687 END IF; -- clob length limit
1688
1689 ELSE -- recipients are specified
1690
1691 OPEN specific_user_csr;
1692 -- Loop through the parameter table to get the user id
1693 LOOP
1694 FETCH specific_user_csr INTO l_user;
1695 EXIT WHEN specific_user_csr%NOTFOUND;
1696
1697 -- Get the user id by user name
1698 SELECT user_id INTO l_user_id
1699 FROM fnd_user
1700 WHERE user_name = upper(l_user);
1701
1702 -- Check duplicates
1703 OPEN check_exist_csr;
1704 FETCH check_exist_csr INTO l_count;
1705 CLOSE check_exist_csr;
1706
1707 IF (l_count = 0 ) THEN -- do the insert, otherwise ignore
1708
1709 -- Object id is unique = ws id
1710 SELECT ZPB_TEMPLATE_WS_ID_SEQ.nextval
1711 INTO l_object_id
1712 FROM dual;
1713
1714 l_insert_type := 'Insert';
1715
1716 -- Retrieving the master template row
1717 FOR l_dist_template_row_rec IN dist_template_csr
1718 LOOP -- only one record
1719
1720 l_distributor_user_id := l_dist_template_row_rec.distributor_user_id;
1721 IF (l_wait_for_review = 'Y' AND l_review_complete_flag = 'Y') THEN
1722 l_distributor_user_id := l_dist_template_row_rec.object_user_id;
1723 END IF;
1724
1725 Distribute
1726 (
1727 p_object_id => l_object_id,
1728 p_object_type => l_object_type,
1729 p_template_id => p_template_id,
1730 p_ac_template_id => l_dist_template_row_rec.ac_template_id,
1731 p_analysis_cycle_id => l_dist_template_row_rec.analysis_cycle_id,
1732 p_ac_instance_id => l_dist_template_row_rec.ac_instance_id,
1733 p_generate_template_task_id => l_dist_template_row_rec.generate_template_task_id,
1734 p_object_user_id => l_user_id,
1735 p_distributor_user_id => l_distributor_user_id,
1736 p_template_name => l_dist_template_row_rec.template_name,
1737 p_dataentry_obj_path => l_dist_template_row_rec.dataentry_obj_path,
1738 p_dataentry_obj_name => l_dist_template_row_rec.dataentry_obj_name,
1739 p_target_obj_path => l_dist_template_row_rec.target_obj_path,
1740 p_target_obj_name => l_dist_template_row_rec.target_obj_name,
1741 p_deadline_date => l_deadline_date,
1742 p_instruction_text_id => l_dist_template_row_rec.instruction_text_id,
1743 p_freeze_flag => l_dist_template_row_rec.freeze_flag,
1744 p_distribution_method => l_dist_template_row_rec.distribution_method,
1745 p_distribution_dimension => l_dist_template_row_rec.distribution_dimension,
1746 p_distribution_hierarchy => l_dist_template_row_rec.distribution_hierarchy,
1747 p_description => l_dist_template_row_rec.description,
1748 p_approval_required_flag => l_dist_template_row_rec.approval_required_flag,
1749 p_enable_target_flag => l_dist_template_row_rec.enable_target_flag,
1750 p_create_inst_mea_flag => l_dist_template_row_rec.create_instance_measures_flag,
1751 p_per_data_query_flag => l_dist_template_row_rec.personal_data_query_flag,
1752 p_per_target_query_flag => l_dist_template_row_rec.personal_target_query_flag,
1753 p_approver_type => l_approver_type,
1754 p_overwrite_custm => NULL,
1755 p_overwrite_ws_data => NULL,
1756 p_insert_type => l_insert_type,
1757 p_distribute_type => 'AUTO',
1758 p_currency_flag => l_dist_template_row_rec.currency_flag,
1759 p_view_type => l_dist_template_row_rec.view_type,
1760 p_business_area_id => l_dist_template_row_rec.business_area_id,
1761 p_multiple_submissions_flag => l_dist_template_row_rec.multiple_submissions_flag
1762 );
1763 END LOOP; -- Retrieving the master template row
1764 END IF; -- Check duplicates
1765
1766 END LOOP; -- loop through task parameter table
1767 END IF; -- Specified user or not
1768
1769 FND_MSG_PUB.Count_And_Get ( p_count => x_msg_count,
1770 p_data => x_msg_data );
1771
1772 EXCEPTION
1773
1774 WHEN FND_API.G_EXC_ERROR THEN
1775
1776 ROLLBACK TO Auto_Distribute_Pvt ;
1777 x_return_status := FND_API.G_RET_STS_ERROR;
1778 FND_MSG_PUB.Count_And_Get ( p_count => x_msg_count,
1779 p_data => x_msg_data );
1780
1781 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1782
1783 ROLLBACK TO Auto_Distribute_Pvt ;
1784 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1785 FND_MSG_PUB.Count_And_Get ( p_count => x_msg_count,
1786 p_data => x_msg_data );
1787
1788 WHEN OTHERS THEN
1789
1790 ROLLBACK TO Auto_Distribute_Pvt ;
1791 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1792
1793 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
1794 FND_MSG_PUB.Add_Exc_Msg ( G_PKG_NAME,
1795 l_api_name);
1796 END if;
1797
1798 FND_MSG_PUB.Count_And_Get ( p_count => x_msg_count,
1799 p_data => x_msg_data );
1800
1801
1802 END Auto_Distribute;
1803
1804
1805 /*=========================================================================+
1806 | PROCEDURE Manual_Distribute_CP
1807 |
1808 | DESCRIPTION
1809 | Procedure calls Manual_Distributeprocedure and pass in necessary
1810 | parameters.
1811 |
1812 +=========================================================================*/
1813 PROCEDURE Manual_Distribute_CP
1814 (
1815 errbuf OUT NOCOPY VARCHAR2,
1816 retcode OUT NOCOPY VARCHAR2,
1817 --
1818 p_object_id IN number,
1819 p_recipient_type IN varchar2,
1820 p_dist_list_id IN number,
1821 p_approver_type IN varchar2,
1822 p_deadline_date IN varchar2,
1823 p_overwrite_cust IN varchar2,
1824 p_overwrite_data IN varchar2)
1825 IS
1826 --
1827 l_api_name CONSTANT VARCHAR2(30) := 'Manual_Distribute_CP';
1828 l_api_version CONSTANT NUMBER := 1.0 ;
1829 --
1830 l_error_api_name VARCHAR2(2000);
1831 l_return_status VARCHAR2(1) ;
1832 l_msg_count NUMBER ;
1833 l_msg_data VARCHAR2(2000) ;
1834 l_msg_index_out NUMBER;
1835 --
1836
1837 BEGIN
1838
1839 Manual_Distribute(
1840 p_api_version => 1.0,
1841 p_init_msg_list => FND_API.G_TRUE,
1842 p_commit => FND_API.G_FALSE,
1843 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
1844 x_return_status => l_return_status,
1845 x_msg_count => l_msg_count,
1846 x_msg_data => l_msg_data,
1847 --
1848 p_object_id => p_object_id,
1849 p_recipient_type => p_recipient_type,
1850 p_dist_list_id => p_dist_list_id,
1851 p_approver_type => p_approver_type,
1852 p_deadline_date => FND_DATE.CANONICAL_TO_DATE(p_deadline_date),
1853 p_overwrite_cust => p_overwrite_cust,
1854 p_overwrite_data => p_overwrite_data);
1855
1856 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1857 raise FND_API.G_EXC_ERROR;
1858 END IF;
1859
1860 retcode := '0';
1861 COMMIT;
1862 RETURN;
1863
1864 EXCEPTION
1865
1866 WHEN FND_API.G_EXC_ERROR THEN
1867 retcode := '2' ;
1868 errbuf:=substr(sqlerrm, 1, 255);
1869
1870 WHEN OTHERS THEN
1871 retcode := '2' ;
1872 errbuf:=substr(sqlerrm, 1, 255);
1873 END Manual_Distribute_CP ;
1874
1875
1876
1877 /*=========================================================================+
1878 | PROCEDURE Manual_Distribute
1879 |
1880 |
1881 | DESCRIPTION
1882 | Procedure updates the Master template copies the changes,
1883 | if any, that the user makes
1884 | creates a new worksheet for each user on the
1885 | distribution user list.
1886 +=========================================================================*/
1887
1888 PROCEDURE Manual_Distribute(
1889 p_api_version IN NUMBER,
1890 p_init_msg_list IN VARCHAR2,
1891 p_commit IN VARCHAR2,
1892 p_validation_level IN NUMBER,
1893 x_return_status OUT NOCOPY VARCHAR2,
1894 x_msg_count OUT NOCOPY NUMBER,
1895 x_msg_data OUT NOCOPY VARCHAR2,
1896 --
1897 p_object_id IN number,
1898 p_recipient_type IN varchar2,
1899 p_dist_list_id IN number,
1900 p_approver_type IN varchar2,
1901 p_deadline_date IN date,
1902 p_overwrite_cust IN varchar2,
1903 p_overwrite_data IN varchar2)
1904 IS
1905 l_api_name CONSTANT VARCHAR2(30) := 'Manual_Distribute';
1906 l_api_version CONSTANT NUMBER := 1.0;
1907 l_return_status VARCHAR2(1);
1908 l_msg_count NUMBER;
1909 l_msg_data VARCHAR2(2000);
1910
1911 l_template_id NUMBER;
1912 l_object_id NUMBER;
1913 l_object_user_id NUMBER;
1914 l_deadline_date DATE;
1915 l_object_type zpb_dc_objects.object_type%TYPE;
1916 l_template_name zpb_dc_objects.template_name%TYPE;
1917 l_data_obj_name zpb_dc_objects.dataentry_obj_name%TYPE;
1918 l_target_obj_name zpb_dc_objects.target_obj_name%TYPE;
1919 l_data_obj_path zpb_dc_objects.dataentry_obj_path%TYPE;
1920 l_target_obj_path zpb_dc_objects.target_obj_path%TYPE;
1921 l_multiple_submissions_flag zpb_dc_objects.multiple_submissions_flag%TYPE;
1922 l_user_id NUMBER;
1923 l_user zpb_task_parameters.value%TYPE;
1924 l_insert_type VARCHAR2(10);
1925 l_distribute_type VARCHAR2(10);
1926 l_count NUMBER;
1927 l_raise_count NUMBER;
1928
1929 l_amt INTEGER;
1930 l_buffer VARCHAR2(30);
1931 l_user_id_clob CLOB;
1932 x_user_id_clob CLOB;
1933 l_lob_length BINARY_INTEGER;
1934 l_ind BINARY_INTEGER;
1935
1936 l_pattern VARCHAR2(1);
1937 l_position BINARY_INTEGER;
1938 l_pattern_position BINARY_INTEGER;
1939 l_resp_key fnd_responsibility.responsibility_key%TYPE;
1940
1941 -- Get template id and more
1942 CURSOR template_info_csr IS
1943 SELECT template_id,
1944 object_user_id,
1945 object_type
1946 FROM zpb_dc_objects
1947 WHERE object_id = p_object_id;
1948
1949 -- Analyst distributes his ws in cascade distribution
1950 CURSOR dist_worksheet_csr IS
1951 SELECT ANALYSIS_CYCLE_ID ,
1952 AC_INSTANCE_ID ,
1953 GENERATE_TEMPLATE_TASK_ID,
1954 OBJECT_USER_ID ,
1955 AC_TEMPLATE_ID ,
1956 TEMPLATE_NAME ,
1957 DATAENTRY_OBJ_PATH ,
1958 DATAENTRY_OBJ_NAME ,
1959 TARGET_OBJ_PATH ,
1960 TARGET_OBJ_NAME ,
1961 INSTRUCTION_TEXT_ID ,
1962 FREEZE_FLAG ,
1963 DISTRIBUTION_METHOD ,
1964 DISTRIBUTION_DIMENSION ,
1965 DISTRIBUTION_HIERARCHY ,
1966 DESCRIPTION ,
1967 DEADLINE_DATE ,
1968 APPROVAL_REQUIRED_FLAG ,
1969 ENABLE_TARGET_FLAG ,
1970 CREATE_INSTANCE_MEASURES_FLAG,
1971 CURRENCY_FLAG,
1972 VIEW_TYPE,
1973 BUSINESS_AREA_ID,
1974 MULTIPLE_SUBMISSIONS_FLAG
1975 FROM ZPB_DC_OBJECTS
1976 WHERE TEMPLATE_ID = l_template_id
1977 AND OBJECT_TYPE = 'M';
1978
1979 -- Get all the specified users from the task param table
1980 CURSOR specific_user_csr IS
1981 SELECT user_name
1982 FROM zpb_dc_distribution_list_items
1983 WHERE distribution_list_id = p_dist_list_id;
1984
1985 -- Check whether a specific user was distributed to
1986 CURSOR check_exist_csr IS
1987 SELECT count(*)
1988 FROM ZPB_DC_OBJECTS
1989 WHERE TEMPLATE_ID = l_template_id
1990 AND OBJECT_USER_ID = l_user_id;
1991
1992 BEGIN
1993 SAVEPOINT Manual_Distribute_Pvt ;
1994
1995 IF NOT FND_API.Compatible_API_Call ( l_api_version,
1996 p_api_version,
1997 l_api_name,
1998 G_PKG_NAME )
1999 THEN
2000
2001 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
2002 END IF;
2003
2004 IF FND_API.to_Boolean ( p_init_msg_list ) THEN
2005 FND_MSG_PUB.initialize ;
2006 END IF;
2007
2008 x_return_status := FND_API.G_RET_STS_SUCCESS ;
2009
2010 -- Initialize the parameters
2011 l_resp_key := null;
2012 l_count := 0;
2013 l_raise_count := 0;
2014 l_amt := 255;
2015 l_ind := 1;
2016 l_pattern := ',';
2017 l_position := 1;
2018 l_pattern_position := 1;
2019
2020 -- API Body
2021
2022 OPEN template_info_csr;
2023 FETCH template_info_csr
2024 INTO l_template_id, l_object_user_id, l_object_type;
2025 CLOSE template_info_csr;
2026 -- Populate resp key
2027 l_resp_key := 'ZPB_CONTROLLER_RESP';
2028
2029 /* M needs a separate query object in the repostory
2030 so that the changes won't automatically populated to distributed ws */
2031 --l_data_obj_name := 'TEMPL_DATA_'||l_template_id||'_MASTER';
2032 --l_target_obj_name := 'TEMPL_TARGET_'||l_template_id||'_MASTER';
2033 --code moved to ui java file
2034
2035 --override the users Worksheet with the Master copy('M' record)
2036 Override_Customization(
2037 p_overwrite_cust => p_overwrite_cust,
2038 p_template_id => l_template_id );
2039 --Populate the object names from M record for BPO distribution
2040 FOR master_name_rec IN dist_worksheet_csr
2041 LOOP
2042 l_data_obj_name := master_name_rec.dataentry_obj_name;
2043 l_data_obj_path := master_name_rec.dataentry_obj_path;
2044 l_target_obj_name := master_name_rec.target_obj_name;
2045 l_target_obj_path := master_name_rec.target_obj_path;
2046 END LOOP;
2047
2048 -- The insert/update the records
2049 IF p_recipient_type <> 'SPECIFIC_USERS' THEN
2050 get_user_id_clob(
2051 p_api_version => l_api_version,
2052 p_init_msg_list => p_init_msg_list,
2053 p_commit => p_commit,
2054 p_validation_level => p_validation_level,
2055 x_return_status => x_return_status,
2056 x_msg_count => x_msg_count,
2057 x_msg_data => x_msg_data,
2058 --
2059 p_object_id => p_object_id,
2060 p_object_user_id => l_object_user_id,
2061 p_recipient_type => p_recipient_type,
2062 p_resp_key => l_resp_key,
2063 x_user_id_clob => l_user_id_clob);
2064
2065 l_position:= 1;
2066 l_lob_length := dbms_lob.getlength(l_user_id_clob);
2067
2068 IF l_lob_length > 0 THEN
2069 LOOP
2070 l_pattern_position :=
2071 DBMS_LOB.INSTR(l_user_id_clob, l_pattern, l_position, 1);
2072
2073 IF l_pattern_position = 0 THEN
2074 l_amt := l_lob_length - l_position + 1;
2075 ELSE
2076 l_amt := l_pattern_position - l_position;
2077 END IF;
2078
2079 dbms_lob.read (l_user_id_clob, l_amt, l_position, l_buffer);
2080
2081 l_user_id := to_number(l_buffer);
2082
2083 -- Check whether the distribution exists already
2084 OPEN check_exist_csr;
2085 FETCH check_exist_csr INTO l_count;
2086 CLOSE check_exist_csr;
2087
2088 IF (l_count = 0) THEN -- first time dist
2089 l_insert_type := 'Insert';
2090 -- Object id is unique - ws id
2091 SELECT ZPB_TEMPLATE_WS_ID_SEQ.nextval
2092 INTO l_object_id
2093 FROM dual;
2094
2095 ELSIF (l_count > 0) THEN -- distributed before
2096 l_insert_type := 'Update';
2097 SELECT object_id
2098 INTO l_object_id
2099 FROM ZPB_DC_OBJECTS
2100 WHERE TEMPLATE_ID = l_template_id
2101 AND OBJECT_USER_ID = l_user_id;
2102 END IF; -- count = or > 0
2103
2104 FOR l_dist_worksheet_row_rec IN dist_worksheet_csr
2105 LOOP
2106 IF (p_deadline_date is null) THEN
2107 l_deadline_date := l_dist_worksheet_row_rec.deadline_date;
2108 ELSE
2109 l_deadline_date := p_deadline_date;
2110 END IF;
2111 Distribute
2112 (
2113 p_object_id => l_object_id,
2114 p_object_type => l_object_type,
2115 p_template_id => l_template_id,
2116 p_ac_template_id => l_dist_worksheet_row_rec.ac_template_id,
2117 p_analysis_cycle_id => l_dist_worksheet_row_rec.analysis_cycle_id,
2118 p_ac_instance_id => l_dist_worksheet_row_rec.ac_instance_id,
2119 p_generate_template_task_id => l_dist_worksheet_row_rec.generate_template_task_id,
2120 p_object_user_id => l_user_id,
2121 p_distributor_user_id => l_object_user_id,
2122 p_template_name => l_dist_worksheet_row_rec.template_name,
2123 p_dataentry_obj_path => l_dist_worksheet_row_rec.dataentry_obj_path,
2124 p_dataentry_obj_name => l_dist_worksheet_row_rec.dataentry_obj_name,
2125 p_target_obj_path => l_dist_worksheet_row_rec.target_obj_path,
2126 p_target_obj_name => l_dist_worksheet_row_rec.target_obj_name,
2127 p_deadline_date => l_deadline_date,
2128 p_instruction_text_id => l_dist_worksheet_row_rec.instruction_text_id,
2129 p_freeze_flag => l_dist_worksheet_row_rec.freeze_flag,
2130 p_distribution_method => l_dist_worksheet_row_rec.distribution_method,
2131 p_distribution_dimension => l_dist_worksheet_row_rec.distribution_dimension,
2132 p_distribution_hierarchy => l_dist_worksheet_row_rec.distribution_hierarchy,
2133 p_description => l_dist_worksheet_row_rec.description,
2134 p_approval_required_flag => l_dist_worksheet_row_rec.approval_required_flag,
2135 p_enable_target_flag => l_dist_worksheet_row_rec.enable_target_flag,
2136 p_create_inst_mea_flag => l_dist_worksheet_row_rec.create_instance_measures_flag,
2137 p_per_data_query_flag => 'N',
2138 p_per_target_query_flag => 'N',
2139 p_approver_type => p_approver_type,
2140 p_overwrite_custm => p_overwrite_cust,
2141 p_overwrite_ws_data => p_overwrite_data,
2142 p_insert_type => l_insert_type,
2143 p_distribute_type => 'MANUAL',
2144 p_currency_flag => l_dist_worksheet_row_rec.currency_flag,
2145 p_view_type => l_dist_worksheet_row_rec.view_type,
2146 p_business_area_id => l_dist_worksheet_row_rec.business_area_id,
2147 p_multiple_submissions_flag => l_dist_worksheet_row_rec.multiple_submissions_flag
2148 );
2149 END LOOP;
2150
2151 -- get clob next user
2152 l_position := l_pattern_position+1 ;
2153 IF l_pattern_position = 0 THEN
2154 EXIT;
2155 END IF;
2156
2157 END LOOP; -- clob loop
2158 END IF; -- clob length limit
2159
2160 ELSE -- recipients are specified
2161 OPEN specific_user_csr;
2162 -- Loop through the parameter table to get the user id
2163 LOOP
2164 FETCH specific_user_csr INTO l_user;
2165 EXIT WHEN specific_user_csr%NOTFOUND;
2166
2167 -- Get the user id by user name
2168 SELECT user_id INTO l_user_id
2169 FROM fnd_user
2170 WHERE user_name = upper(l_user);
2171
2172 -- Check whether the distribution exists already
2173 OPEN check_exist_csr;
2174 FETCH check_exist_csr INTO l_count;
2175 CLOSE check_exist_csr;
2176
2177 -- Check whether the distribution exists already
2178 IF (l_count = 0) THEN
2179 l_insert_type := 'Insert';
2180 -- Object id is unique = ws id
2181 SELECT ZPB_TEMPLATE_WS_ID_SEQ.nextval
2182 INTO l_object_id
2183 FROM dual;
2184
2185 ELSIF (l_count > 0) THEN
2186 l_insert_type := 'Update';
2187 SELECT object_id
2188 INTO l_object_id
2189 FROM ZPB_DC_OBJECTS
2190 WHERE TEMPLATE_ID = l_template_id
2191 AND OBJECT_USER_ID = l_user_id;
2192
2193 END IF; -- l_count
2194 FOR l_dist_worksheet_row_rec IN dist_worksheet_csr
2195 LOOP
2196 IF (p_deadline_date is null) THEN
2197 l_deadline_date := l_dist_worksheet_row_rec.deadline_date;
2198 ELSE
2199 l_deadline_date := p_deadline_date;
2200 END IF;
2201 Distribute
2202 (
2203 p_object_id => l_object_id,
2204 p_object_type => l_object_type,
2205 p_template_id => l_template_id,
2206 p_ac_template_id => l_dist_worksheet_row_rec.ac_template_id,
2207 p_analysis_cycle_id => l_dist_worksheet_row_rec.analysis_cycle_id,
2208 p_ac_instance_id => l_dist_worksheet_row_rec.ac_instance_id,
2209 p_generate_template_task_id => l_dist_worksheet_row_rec.generate_template_task_id,
2210 p_object_user_id => l_user_id,
2211 p_distributor_user_id => l_object_user_id,
2212 p_template_name => l_dist_worksheet_row_rec.template_name,
2213 p_dataentry_obj_path => l_dist_worksheet_row_rec.dataentry_obj_path,
2214 p_dataentry_obj_name => l_dist_worksheet_row_rec.dataentry_obj_name,
2215 p_target_obj_path => l_dist_worksheet_row_rec.target_obj_path,
2216 p_target_obj_name => l_dist_worksheet_row_rec.target_obj_name,
2217 p_deadline_date => l_deadline_date,
2218 p_instruction_text_id => l_dist_worksheet_row_rec.instruction_text_id,
2219 p_freeze_flag => l_dist_worksheet_row_rec.freeze_flag,
2220 p_distribution_method => l_dist_worksheet_row_rec.distribution_method,
2221 p_distribution_dimension => l_dist_worksheet_row_rec.distribution_dimension,
2222 p_distribution_hierarchy => l_dist_worksheet_row_rec.distribution_hierarchy,
2223 p_description => l_dist_worksheet_row_rec.description,
2224 p_approval_required_flag => l_dist_worksheet_row_rec.approval_required_flag,
2225 p_enable_target_flag => l_dist_worksheet_row_rec.enable_target_flag,
2226 p_create_inst_mea_flag => l_dist_worksheet_row_rec.create_instance_measures_flag,
2227 p_per_data_query_flag => 'N',
2228 p_per_target_query_flag => 'N',
2229 p_approver_type => p_approver_type,
2230 p_overwrite_custm => p_overwrite_cust,
2231 p_overwrite_ws_data => p_overwrite_data,
2232 p_insert_type => l_insert_type,
2233 p_distribute_type => 'MANUAL',
2234 p_currency_flag => l_dist_worksheet_row_rec.currency_flag,
2235 p_view_type => l_dist_worksheet_row_rec.view_type,
2236 p_business_area_id => l_dist_worksheet_row_rec.business_area_id,
2237 p_multiple_submissions_flag => l_dist_worksheet_row_rec.multiple_submissions_flag
2238 );
2239 END LOOP;
2240
2241 END LOOP; -- loop through specific users
2242 END IF; -- recipient is specified or not
2243 FND_MSG_PUB.Count_And_Get ( p_count => x_msg_count,
2244 p_data => x_msg_data );
2245 EXCEPTION
2246
2247 WHEN FND_API.G_EXC_ERROR THEN
2248
2249 ROLLBACK TO Manual_Distribute_Pvt ;
2250 x_return_status := FND_API.G_RET_STS_ERROR;
2251 FND_MSG_PUB.Count_And_Get ( p_count => x_msg_count,
2252 p_data => x_msg_data );
2253
2254 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2255
2256 ROLLBACK TO Manual_Distribute_Pvt ;
2257 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2258 FND_MSG_PUB.Count_And_Get ( p_count => x_msg_count,
2259 p_data => x_msg_data );
2260
2261 WHEN OTHERS THEN
2262
2263 ROLLBACK TO Manual_Distribute_Pvt ;
2264 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2265
2266 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
2267 FND_MSG_PUB.Add_Exc_Msg ( G_PKG_NAME,
2268 l_api_name);
2269 END if;
2270
2271 FND_MSG_PUB.Count_And_Get ( p_count => x_msg_count,
2272 p_data => x_msg_data );
2273
2274
2275 END Manual_Distribute;
2276
2277 /*=========================================================================+
2278 | PROCEDURE Set_Template_Recipient
2279 |
2280 |
2281 | DESCRIPTION
2282 | This procedure sets the notification recipients for the template
2283 | distribution.
2284 |
2285 |
2286 +=========================================================================*/
2287
2288 PROCEDURE Set_Template_Recipient(
2289 p_api_version IN NUMBER,
2290 p_init_msg_list IN VARCHAR2,
2291 p_commit IN VARCHAR2,
2292 p_validation_level IN NUMBER,
2293 x_return_status OUT NOCOPY VARCHAR2,
2294 x_msg_count OUT NOCOPY NUMBER,
2295 x_msg_data OUT NOCOPY VARCHAR2,
2296 --
2297 p_template_id IN NUMBER,
2298 x_role_name OUT NOCOPY VARCHAR2)
2299 IS
2300
2301 l_api_name CONSTANT VARCHAR2(30) := 'Set_Template_Recipient';
2302 l_api_version CONSTANT NUMBER := 1.0;
2303 l_return_status VARCHAR2(1);
2304 l_msg_count NUMBER;
2305 l_msg_data VARCHAR2(2000);
2306
2307 l_object_user_id NUMBER;
2308 l_exp_days NUMBER;
2309 l_charDate VARCHAR2(20);
2310 l_rolename VARCHAR2(320);
2311
2312 CURSOR template_user_csr IS
2313 SELECT fnd.user_name
2314 FROM zpb_dc_objects obj, fnd_user fnd
2315 WHERE obj.template_id = p_template_id
2316 AND obj.object_user_id = fnd.user_id
2317 AND obj.object_type in ('W');
2318 --
2319 BEGIN
2320
2321 SAVEPOINT Set_Template_Recipient ;
2322
2323 IF NOT FND_API.Compatible_API_Call ( l_api_version,
2324 p_api_version,
2325 l_api_name,
2326 G_PKG_NAME )
2327 THEN
2328 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
2329 END IF;
2330
2331
2332 IF FND_API.to_Boolean ( p_init_msg_list ) THEN
2333 FND_MSG_PUB.initialize ;
2334 END IF;
2335
2336 x_return_status := FND_API.G_RET_STS_SUCCESS ;
2337
2338 -- Initialize the parameters
2339 l_exp_days := 7;
2340
2341 -- API Body
2342
2343 -- Create the role
2344 l_charDate := to_char(sysdate, 'J-SSSSS');
2345 l_rolename := 'ZPB_DC_TMPL_USER'|| to_char(p_template_id) || '-' || l_charDate;
2346 zpb_wf_ntf.SetRole(l_rolename, l_exp_days);
2347
2348 FND_FILE.Put_Line ( FND_FILE.LOG, 'set_template_recipient - l_rolename=' || l_rolename ) ;
2349 FOR template_user_rec IN template_user_csr
2350 LOOP
2351 FND_FILE.Put_Line ( FND_FILE.LOG, 'set_template_recipient - template_user_rec.user_name=' || template_user_rec.user_name ) ;
2352 l_rolename := zpb_wf_ntf.update_Role_with_Shadows(l_rolename, template_user_rec.user_name);
2353 FND_FILE.Put_Line ( FND_FILE.LOG, 'set_template_recipient - after call to update_Role_with_Shadows') ;
2354 END LOOP;
2355 x_role_name := l_rolename;
2356 FND_MSG_PUB.Count_And_Get ( p_count => x_msg_count,
2357 p_data => x_msg_data );
2358 EXCEPTION
2359
2360 WHEN FND_API.G_EXC_ERROR THEN
2361
2362 ROLLBACK TO Set_Template_Recipient ;
2363 x_return_status := FND_API.G_RET_STS_ERROR;
2364 FND_MSG_PUB.Count_And_Get ( p_count => x_msg_count,
2365 p_data => x_msg_data );
2366
2367 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2368
2369 ROLLBACK TO Set_Template_Recipient ;
2370 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2371 FND_MSG_PUB.Count_And_Get ( p_count => x_msg_count,
2372 p_data => x_msg_data );
2373
2374 WHEN OTHERS THEN
2375
2376 ROLLBACK TO Set_Template_Recipient ;
2377 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2378
2379 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
2380 FND_MSG_PUB.Add_Exc_Msg ( G_PKG_NAME,
2381 l_api_name);
2382 END if;
2383
2384 FND_MSG_PUB.Count_And_Get ( p_count => x_msg_count,
2385 p_data => x_msg_data );
2386
2387
2388 END Set_Template_Recipient;
2389
2390
2391
2392 /*=========================================================================+
2393 | PROCEDURE Set_Ws_Recipient
2394 |
2395 |
2396 | DESCRIPTION
2397 | This procedure sets the notification users for worksheet distribution.
2398 |
2399 +=========================================================================*/
2400 PROCEDURE Set_Ws_Recipient(
2401 p_api_version IN NUMBER,
2402 p_init_msg_list IN VARCHAR2,
2403 p_commit IN VARCHAR2,
2404 p_validation_level IN NUMBER,
2405 x_return_status OUT NOCOPY VARCHAR2,
2406 x_msg_count OUT NOCOPY NUMBER,
2407 x_msg_data OUT NOCOPY VARCHAR2,
2408 --
2409 p_task_id IN NUMBER,
2410 p_template_id IN NUMBER,
2411 p_dist_list_id IN NUMBER,
2412 p_object_id IN NUMBER,
2413 p_recipient_type IN VARCHAR2,
2414 x_role_name OUT NOCOPY VARCHAR2,
2415 x_resultout OUT NOCOPY VARCHAR2 )
2416 IS
2417 l_api_name CONSTANT VARCHAR2(30) := 'Set_Ws_Recipient';
2418
2419 l_param_value zpb_task_parameters.value%TYPE;
2420 l_object_type zpb_dc_objects.object_type%TYPE;
2421 l_exp_days NUMBER;
2422 l_user VARCHAR2(4000);
2423 l_upper_user VARCHAR2(100);
2424 l_charDate VARCHAR2(20);
2425 l_rolename VARCHAR2(320);
2426 l_dist_list_id NUMBER := 0;
2427 l_recipient_type VARCHAR2(30);
2428 l_object_user_id NUMBER;
2429 l_template_id NUMBER;
2430 l_object_id NUMBER;
2431 l_resp_key fnd_responsibility.responsibility_key%TYPE;
2432 --
2433 l_amt INTEGER;
2434 l_buffer VARCHAR2(30);
2435 l_user_id_clob CLOB;
2436 l_lob_length BINARY_INTEGER;
2437 l_ind BINARY_INTEGER;
2438
2439 l_pattern VARCHAR2(1);
2440 l_role_has_users VARCHAR2(1);
2441 l_position BINARY_INTEGER;
2442 l_pattern_position BINARY_INTEGER;
2443
2444 aw_user_list zpb_num_tbl_type;
2445
2446 -- Auto distribution
2447 CURSOR param_type_csr IS
2448 SELECT value
2449 FROM zpb_task_parameters
2450 WHERE task_id = p_task_id
2451 AND name = 'DISTRIBUTION_RECIPIENT_TYPE';
2452
2453 -- Auto distribution
2454 CURSOR specific_user_csr IS
2455 SELECT value
2456 FROM zpb_task_parameters
2457 WHERE task_id = p_task_id
2458 AND name = 'DISTRIBUTION_SPECIFIED_USERS';
2459
2460 -- Manual distribution
2461 CURSOR dist_list_csr IS
2462 SELECT user_name
2463 FROM zpb_dc_distribution_list_items
2464 WHERE distribution_list_id = p_dist_list_id;
2465
2466 BEGIN
2467 ZPB_LOG.WRITE(G_PKG_NAME||'.'||l_api_name, 'BEGIN');
2468 ZPB_LOG.WRITE(G_PKG_NAME||'.'||l_api_name, 'IN Parameter:'
2469 || ' p_init_msg_list=' || p_init_msg_list
2470 || ' p_commit=' || p_commit
2471 || ' p_task_id=' || p_task_id
2472 || ' p_template_id=' || p_template_id
2473 || ' p_dist_list_id=' || p_dist_list_id
2474 || ' p_object_id=' || p_object_id
2475 || ' p_recipient_type=' || p_recipient_type);
2476
2477 -- Initialize the parameters
2478 l_exp_days := 7;
2479 l_dist_list_id := 0;
2480 l_amt := 255;
2481 l_ind := 1;
2482 l_pattern := ',';
2483 l_position := 1;
2484 l_pattern_position := 1;
2485
2486 -- API Body
2487
2488 IF (p_task_id is not null) THEN -- Auto distribution
2489
2490 ZPB_LOG.WRITE(G_PKG_NAME||'.'||l_api_name, 'Auto Distribution');
2491 -- Set the resp key
2492 l_resp_key := 'ZPB_CONTROLLER_RESP';
2493
2494 -- Get the recipient type
2495 OPEN param_type_csr;
2496 FETCH param_type_csr INTO l_param_value;
2497 CLOSE param_type_csr;
2498
2499 -- Create the role
2500 l_charDate := to_char(sysdate, 'J-SSSSS');
2501 l_rolename := 'ZPB_DC_AUTO'|| to_char(p_task_id) || '-' || l_charDate;
2502 zpb_wf_ntf.SetRole(l_rolename, l_exp_days);
2503 l_role_has_users := 'N';
2504
2505 SELECT object_id, object_user_id
2506 INTO l_object_id, l_object_user_id
2507 FROM zpb_dc_objects
2508 WHERE template_id = p_template_id
2509 AND object_type = 'M';
2510
2511 IF l_param_value = 'SPECIFIC_USERS' THEN
2512 OPEN specific_user_csr;
2513 LOOP
2514 FETCH specific_user_csr INTO l_user;
2515 EXIT WHEN specific_user_csr%NOTFOUND;
2516 l_rolename := zpb_wf_ntf.update_Role_with_Shadows(l_rolename, l_user );
2517 l_role_has_users := 'Y';
2518 END LOOP;
2519 CLOSE specific_user_csr;
2520
2521
2522 ELSE -- other than specific users -- auto
2523 -- Get the clob of user ids
2524 get_user_id_clob(
2525 p_api_version => p_api_version,
2526 p_init_msg_list => p_init_msg_list,
2527 p_commit => p_commit,
2528 p_validation_level => p_validation_level,
2529 x_return_status => x_return_status,
2530 x_msg_count => x_msg_count,
2531 x_msg_data => x_msg_data,
2532 --
2533 p_object_id => l_object_id,
2534 p_object_user_id => l_object_user_id,
2535 p_recipient_type => l_param_value,
2536 p_resp_key => l_resp_key,
2537 x_user_id_clob => l_user_id_clob);
2538
2539 l_ind := 1;
2540 l_position:= 1;
2541 l_lob_length := dbms_lob.getlength(l_user_id_clob);
2542
2543 -- Parse the clob and put the ids in a tab type object
2544 IF l_lob_length > 0 THEN
2545 aw_user_list := zpb_num_tbl_type(0);
2546 END IF;
2547
2548 IF l_lob_length > 0 THEN
2549 LOOP
2550 l_pattern_position := DBMS_LOB.INSTR(l_user_id_clob, l_pattern, l_position, 1);
2551
2552 IF l_pattern_position = 0 THEN
2553 l_amt := l_lob_length - l_position+1;
2554 ELSE
2555 l_amt := l_pattern_position - l_position;
2556 END IF;
2557
2558 dbms_lob.read (l_user_id_clob, l_amt, l_position, l_buffer);
2559 aw_user_list.extend;
2560 aw_user_list(l_ind):= to_number(l_buffer);
2561 l_ind:= l_ind+1;
2562
2563 l_position := l_pattern_position+1 ;
2564 IF l_pattern_position = 0 THEN
2565 EXIT;
2566 END IF;
2567
2568 END LOOP;
2569 END IF; -- parse and store the ids
2570
2571 -- Join the fnd user to get the user names
2572 FOR fnd_rec IN
2573 (select column_value , fndu.user_name user_name
2574 from
2575 table( cast(aw_user_list as zpb_num_tbl_type)) aw_users,
2576 fnd_user fndu
2577 where fndu.user_id = aw_users.column_value
2578 )
2579 LOOP
2580 l_rolename := zpb_wf_ntf.update_Role_with_Shadows(l_rolename, fnd_rec.user_name);
2581 l_role_has_users := 'Y';
2582 END LOOP;
2583
2584 END IF; --recipients types
2585
2586 ELSE -- Manual distribution
2587
2588 ZPB_LOG.WRITE(G_PKG_NAME||'.'||l_api_name, 'Manual Distribution');
2589
2590 -- Populate resp key based on object type and object id/user id
2591 SELECT object_type, object_id, object_user_id
2592 INTO l_object_type, l_object_id, l_object_user_id
2593 FROM zpb_dc_objects
2594 WHERE object_id = p_object_id;
2595
2596 IF (l_object_type = 'E') THEN
2597 l_resp_key := 'ZPB_CONTROLLER_RESP';
2598 ELSE
2599 l_resp_key := 'ZPB_ANALYST_RESP';
2600 END IF;
2601
2602 -- Create the role
2603 l_charDate := to_char(sysdate, 'J-SSSSS');
2604 l_rolename := 'ZPB_DC_MAN'|| to_char(p_dist_list_id) || '-' || l_charDate;
2605 ZPB_LOG.WRITE(G_PKG_NAME||'.'||l_api_name, ' l_rolename='||l_rolename);
2606
2607 zpb_wf_ntf.SetRole(l_rolename, l_exp_days);
2608
2609 l_role_has_users := 'N';
2610
2611 IF (p_recipient_type ='SPECIFIC_USERS') THEN
2612
2613 ZPB_LOG.WRITE(G_PKG_NAME||'.'||l_api_name, 'SPECIFIC_USERS');
2614
2615 OPEN dist_list_csr;
2616 LOOP
2617 FETCH dist_list_csr INTO l_user;
2618 EXIT WHEN dist_list_csr%NOTFOUND;
2619 l_rolename := zpb_wf_ntf.update_Role_with_Shadows(l_rolename, l_user);
2620 l_role_has_users := 'Y';
2621 END LOOP;
2622 CLOSE dist_list_csr;
2623
2624 ELSE -- Other than specific users -manual
2625
2626 ZPB_LOG.WRITE(G_PKG_NAME||'.'||l_api_name, 'ALL DATA OWNERS');
2627
2628 -- Get the clob of user ids
2629 get_user_id_clob(
2630 p_api_version => p_api_version,
2631 p_init_msg_list => p_init_msg_list,
2632 p_commit => p_commit,
2633 p_validation_level => p_validation_level,
2634 x_return_status => x_return_status,
2635 x_msg_count => x_msg_count,
2636 x_msg_data => x_msg_data,
2637 --
2638 p_object_id => l_object_id,
2639 p_object_user_id => l_object_user_id,
2640 p_recipient_type => p_recipient_type,
2641 p_resp_key => l_resp_key,
2642 x_user_id_clob => l_user_id_clob);
2643
2644 -- Parse the clob and put the ids in a tab type object
2645 l_ind := 1;
2646 l_position:= 1;
2647 l_lob_length := dbms_lob.getlength(l_user_id_clob);
2648
2649 IF l_lob_length > 0 THEN
2650 aw_user_list := zpb_num_tbl_type(0);
2651 END IF;
2652
2653 IF l_lob_length > 0 THEN
2654 LOOP
2655 l_pattern_position := DBMS_LOB.INSTR(l_user_id_clob, l_pattern, l_position, 1);
2656
2657 IF l_pattern_position = 0 THEN
2658 l_amt := l_lob_length - l_position+1;
2659 ELSE
2660 l_amt := l_pattern_position - l_position;
2661 END IF;
2662
2663 dbms_lob.read (l_user_id_clob, l_amt, l_position, l_buffer);
2664 aw_user_list.extend;
2665 aw_user_list(l_ind):= to_number(l_buffer);
2666 l_ind:= l_ind+1;
2667
2668 l_position := l_pattern_position+1 ;
2669 IF l_pattern_position = 0 THEN
2670 EXIT;
2671 END IF;
2672
2673 END LOOP;
2674 END IF; -- parse and store the ids
2675
2676 FOR fnd_rec IN
2677 (select column_value , fndu.user_name user_name
2678 from
2679 table( cast(aw_user_list as zpb_num_tbl_type)) aw_users,
2680 fnd_user fndu
2681 where fndu.user_id = aw_users.column_value
2682 )
2683 LOOP
2684 l_rolename := zpb_wf_ntf.update_Role_with_Shadows(l_rolename, fnd_rec.user_name);
2685 l_role_has_users := 'Y';
2686 END LOOP;
2687
2688 END IF; --recipients types
2689 END IF; -- Auto or manual
2690
2691 -- Return role_name only if it has any Users.
2692 IF (l_role_has_users = 'Y') THEN
2693 x_resultout := 'COMPLETE:Y';
2694 x_role_name := l_rolename;
2695 ELSE
2696 x_resultout := 'COMPLETE:N';
2697 END IF;
2698
2699 ZPB_LOG.WRITE(G_PKG_NAME||'.'||l_api_name, 'END. Out Parameters:'
2700 || ' x_resultout='||x_resultout
2701 || ' x_role_name='||x_role_name);
2702
2703 EXCEPTION
2704 WHEN OTHERS THEN
2705 ZPB_LOG.WRITE(G_PKG_NAME||'.'||l_api_name, 'EXCEPTION');
2706 raise;
2707
2708 END Set_Ws_Recipient;
2709
2710
2711 /*=========================================================================+
2712 | PROCEDURE Complete_Review
2713 |
2714 |
2715 | DESCRIPTION
2716 | This procedure changes the complete_review_flag to 'Y' when
2717 | user finishes review and clicks on Finish Reciew button from the UI.
2718 |
2719 +=========================================================================*/
2720 PROCEDURE Complete_Review(
2721 p_api_version IN NUMBER,
2722 p_init_msg_list IN VARCHAR2,
2723 p_commit IN VARCHAR2,
2724 p_validation_level IN NUMBER,
2725 x_return_status OUT NOCOPY VARCHAR2,
2726 x_msg_count OUT NOCOPY NUMBER,
2727 x_msg_data OUT NOCOPY VARCHAR2,
2728 --
2729 p_template_id IN NUMBER)
2730 IS
2731 l_api_name CONSTANT VARCHAR2(30) := 'Complete_Review';
2732 l_api_version CONSTANT NUMBER := 1.0;
2733 l_return_status VARCHAR2(1);
2734 l_msg_count NUMBER;
2735 l_msg_data VARCHAR2(2000);
2736
2737 l_data_obj_name zpb_dc_objects.dataentry_obj_name%TYPE;
2738 l_target_obj_name zpb_dc_objects.target_obj_name%TYPE;
2739
2740
2741 BEGIN
2742
2743 SAVEPOINT Complete_Review_Pvt ;
2744
2745 IF NOT FND_API.Compatible_API_Call ( l_api_version,
2746 p_api_version,
2747 l_api_name,
2748 G_PKG_NAME )
2749 THEN
2750 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
2751 END IF;
2752
2753
2754 IF FND_API.to_Boolean ( p_init_msg_list ) THEN
2755 FND_MSG_PUB.initialize ;
2756 END IF;
2757
2758 x_return_status := FND_API.G_RET_STS_SUCCESS ;
2759
2760 -- Initialize the parameters
2761
2762 -- API Body
2763
2764 -- M needs a separate object in the repostory
2765 --l_data_obj_name := 'TEMPL_DATA_'||p_template_id||'_MASTER';
2766 --l_target_obj_name := 'TEMPL_TARGET_'||p_template_id||'_MASTER';
2767 -- logic moved to ui java file --
2768
2769 -- Update C with 'M' record
2770 FOR e_rec IN
2771 (SELECT TEMPLATE_NAME ,
2772 DATAENTRY_OBJ_PATH ,
2773 DATAENTRY_OBJ_NAME ,
2774 TARGET_OBJ_PATH ,
2775 TARGET_OBJ_NAME ,
2776 INSTRUCTION_TEXT_ID ,
2777 DESCRIPTION ,
2778 DEADLINE_DATE
2779 FROM ZPB_DC_OBJECTS
2780 WHERE TEMPLATE_ID = p_template_id
2781 AND OBJECT_TYPE = 'M')
2782 LOOP
2783 UPDATE ZPB_DC_OBJECTS
2784 SET TEMPLATE_NAME = e_rec.template_name,
2785 DESCRIPTION = e_rec.description,
2786 DATAENTRY_OBJ_PATH = e_rec.dataentry_obj_path,
2787 DATAENTRY_OBJ_NAME = e_rec.dataentry_obj_name,
2788 TARGET_OBJ_PATH = e_rec.target_obj_path,
2789 TARGET_OBJ_NAME = e_rec.target_obj_name,
2790 PERSONAL_DATA_QUERY_FLAG = 'N',
2791 PERSONAL_TARGET_QUERY_FLAG = 'N',
2792 CREATE_SOLVE_PROGRAM_FLAG = 'Y',
2793 INSTRUCTION_TEXT_ID = e_rec.instruction_text_id,
2794 DEADLINE_DATE = e_rec.deadline_date,
2795 LAST_UPDATED_BY = fnd_global.USER_ID,
2796 LAST_UPDATE_DATE = SYSDATE,
2797 LAST_UPDATE_LOGIN = fnd_global.LOGIN_ID
2798 WHERE TEMPLATE_ID = p_template_id
2799 AND OBJECT_TYPE = 'C';
2800 END LOOP;
2801
2802 UPDATE ZPB_DC_OBJECTS
2803 SET REVIEW_COMPLETE_FLAG = 'Y',
2804 STATUS = 'REVIEW_COMPLETED',
2805 LAST_UPDATED_BY = fnd_global.USER_ID,
2806 LAST_UPDATE_DATE = SYSDATE,
2807 LAST_UPDATE_LOGIN = fnd_global.LOGIN_ID
2808 WHERE TEMPLATE_ID = p_template_id
2809 AND OBJECT_TYPE in ('E','M');
2810
2811 FND_MSG_PUB.Count_And_Get ( p_count => x_msg_count,
2812 p_data => x_msg_data );
2813 COMMIT WORK;
2814 EXCEPTION
2815
2816 WHEN FND_API.G_EXC_ERROR THEN
2817
2818 ROLLBACK TO Complete_Review_Pvt ;
2819 x_return_status := FND_API.G_RET_STS_ERROR;
2820 FND_MSG_PUB.Count_And_Get ( p_count => x_msg_count,
2821 p_data => x_msg_data );
2822
2823 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2824
2825 ROLLBACK TO Complete_Review_Pvt ;
2826 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2827 FND_MSG_PUB.Count_And_Get ( p_count => x_msg_count,
2828 p_data => x_msg_data );
2829
2830 WHEN OTHERS THEN
2831
2832 ROLLBACK TO Complete_Review_Pvt ;
2833 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2834
2835 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
2836 FND_MSG_PUB.Add_Exc_Msg ( G_PKG_NAME,
2837 l_api_name);
2838 END if;
2839
2840 FND_MSG_PUB.Count_And_Get ( p_count => x_msg_count,
2841 p_data => x_msg_data );
2842
2843
2844 END Complete_Review;
2845
2846
2847
2848 /*=========================================================================+
2849 | PROCEDURE Delete_Template
2850 |
2851 |
2852 | DESCRIPTION
2853 | This procedure is called by zpbac.plb. When the analysis cycle gets deleted
2854 | the template gets deleted too.
2855 |
2856 +=========================================================================*/
2857 PROCEDURE Delete_Template(
2858 p_api_version IN NUMBER,
2859 p_init_msg_list IN VARCHAR2,
2860 p_commit IN VARCHAR2,
2861 p_validation_level IN NUMBER,
2862 x_return_status OUT NOCOPY VARCHAR2,
2863 x_msg_count OUT NOCOPY NUMBER,
2864 x_msg_data OUT NOCOPY VARCHAR2,
2865 --
2866 p_analysis_cycle_instance_id IN NUMBER)
2867 IS
2868 l_api_name CONSTANT VARCHAR2(30) := 'Delete_Template';
2869 l_api_version CONSTANT NUMBER := 1.0;
2870 l_return_status VARCHAR2(1);
2871 l_msg_count NUMBER;
2872 l_msg_data VARCHAR2(2000);
2873
2874 BEGIN
2875
2876 SAVEPOINT Delete_Template_Pvt ;
2877
2878 IF NOT FND_API.Compatible_API_Call ( l_api_version,
2879 p_api_version,
2880 l_api_name,
2881 G_PKG_NAME )
2882 THEN
2883 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
2884 END IF;
2885
2886
2887 IF FND_API.to_Boolean ( p_init_msg_list ) THEN
2888 FND_MSG_PUB.initialize ;
2889 END IF;
2890
2891 x_return_status := FND_API.G_RET_STS_SUCCESS ;
2892
2893 -- Initialize the parameters
2894
2895 -- API Body
2896
2897 UPDATE zpb_dc_objects
2898 SET delete_instance_measures_flag = 'Y'
2899 WHERE ac_instance_id = p_analysis_cycle_instance_id;
2900
2901 IF FND_API.To_Boolean ( p_commit ) THEN
2902 COMMIT WORK;
2903 END iF;
2904
2905 FND_MSG_PUB.Count_And_Get ( p_count => x_msg_count,
2906 p_data => x_msg_data );
2907 EXCEPTION
2908
2909 WHEN FND_API.G_EXC_ERROR THEN
2910
2911 ROLLBACK TO Delete_Template_Pvt ;
2912 x_return_status := FND_API.G_RET_STS_ERROR;
2913 FND_MSG_PUB.Count_And_Get ( p_count => x_msg_count,
2914 p_data => x_msg_data );
2915
2916 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2917
2918 ROLLBACK TO Delete_Template_Pvt ;
2919 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2920 FND_MSG_PUB.Count_And_Get ( p_count => x_msg_count,
2921 p_data => x_msg_data );
2922
2923 WHEN OTHERS THEN
2924
2925 ROLLBACK TO Delete_Template_Pvt ;
2926 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2927
2928 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
2929 FND_MSG_PUB.Add_Exc_Msg ( G_PKG_NAME,
2930 l_api_name);
2931 END if;
2932
2933 FND_MSG_PUB.Count_And_Get ( p_count => x_msg_count,
2934 p_data => x_msg_data );
2935
2936 END Delete_Template;
2937
2938 /*=========================================================================+
2939 | PROCEDURE Set_Submit_Ntf_Recipients
2940 |
2941 |
2942 | DESCRIPTION
2943 | This procedure will set the recipients for the submission activity.
2944 |
2945 +=========================================================================*/
2946 PROCEDURE Set_Submit_Ntf_Recipients(
2947 p_api_version IN NUMBER,
2948 p_init_msg_list IN VARCHAR2,
2949 p_commit IN VARCHAR2,
2950 p_validation_level IN NUMBER,
2951 x_return_status OUT NOCOPY VARCHAR2,
2952 x_msg_count OUT NOCOPY NUMBER,
2953 x_msg_data OUT NOCOPY VARCHAR2,
2954 --
2955 p_object_id IN NUMBER,
2956 x_role_name OUT NOCOPY VARCHAR2)
2957 IS
2958 l_api_name CONSTANT VARCHAR2(30) := 'Set_Submit_Ntf_Recipients';
2959 l_api_version CONSTANT NUMBER := 1.0;
2960 l_return_status VARCHAR2(1);
2961 l_msg_count NUMBER;
2962 l_msg_data VARCHAR2(2000);
2963
2964 l_exp_days NUMBER;
2965 l_user VARCHAR2(4000);
2966 l_charDate VARCHAR2(20);
2967 l_rolename VARCHAR2(320);
2968 l_recipient_type VARCHAR2(30);
2969 l_object_user_id NUMBER;
2970 l_resp_key fnd_responsibility.responsibility_key%TYPE;
2971 --
2972 l_amt INTEGER;
2973 l_buffer VARCHAR2(30);
2974 l_user_id_clob CLOB;
2975 l_lob_length BINARY_INTEGER;
2976 l_ind BINARY_INTEGER;
2977
2978 l_pattern VARCHAR2(1);
2979 l_position BINARY_INTEGER;
2980 l_pattern_position BINARY_INTEGER;
2981 aw_user_list zpb_num_tbl_type;
2982
2983 BEGIN
2984
2985 SAVEPOINT Set_Submit_Ntf_Recipients;
2986
2987 IF NOT FND_API.Compatible_API_Call ( l_api_version,
2988 p_api_version,
2989 l_api_name,
2990 G_PKG_NAME )
2991 THEN
2992 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
2993 END IF;
2994
2995
2996 IF FND_API.to_Boolean ( p_init_msg_list ) THEN
2997 FND_MSG_PUB.initialize ;
2998 END IF;
2999
3000 x_return_status := FND_API.G_RET_STS_SUCCESS ;
3001
3002 -- Initialize the parameters
3003 l_exp_days := 7;
3004 l_amt := 255;
3005 l_ind := 1;
3006 l_pattern := ',';
3007 l_position := 1;
3008 l_pattern_position := 1;
3009
3010 -- API Body
3011
3012 SELECT object_user_id
3013 INTO l_object_user_id
3014 FROM zpb_dc_objects
3015 WHERE object_id = p_object_id;
3016
3017 l_recipient_type := 'ALL_RPT';
3018
3019 -- Create the role
3020 l_charDate := to_char(sysdate, 'J-SSSSS');
3021 l_rolename := 'ZPB_DC_SUB'|| to_char(p_object_id) || '-' || l_charDate;
3022 zpb_wf_ntf.SetRole(l_rolename, l_exp_days);
3023
3024 get_user_id_clob(
3025 p_api_version => p_api_version,
3026 p_init_msg_list => p_init_msg_list,
3027 p_commit => p_commit,
3028 p_validation_level => p_validation_level,
3029 x_return_status => x_return_status,
3030 x_msg_count => x_msg_count,
3031 x_msg_data => x_msg_data,
3032 --
3033 p_object_id => p_object_id,
3034 p_object_user_id => l_object_user_id,
3035 p_recipient_type => l_recipient_type,
3036 p_resp_key => l_resp_key,
3037 x_user_id_clob => l_user_id_clob);
3038
3039 -- Parse the clob and put the ids in a tab type object
3040 l_ind := 1;
3041 l_position:= 1;
3042 l_lob_length := dbms_lob.getlength(l_user_id_clob);
3043
3044 IF (l_lob_length > 0) THEN
3045 aw_user_list := zpb_num_tbl_type(0);
3046 END IF ;
3047
3048 IF l_lob_length > 0 THEN
3049 LOOP
3050 l_pattern_position := DBMS_LOB.INSTR(l_user_id_clob, l_pattern, l_position, 1);
3051
3052 IF l_pattern_position = 0 THEN
3053 l_amt := l_lob_length - l_position+1;
3054 ELSE
3055 l_amt := l_pattern_position - l_position;
3056 END IF;
3057
3058 dbms_lob.read (l_user_id_clob, l_amt, l_position, l_buffer);
3059 aw_user_list.extend;
3060 aw_user_list(l_ind):= to_number(l_buffer);
3061 l_ind:= l_ind+1;
3062
3063 l_position := l_pattern_position+1 ;
3064 IF l_pattern_position = 0 THEN
3065 EXIT;
3066 END IF;
3067
3068 END LOOP;
3069 END IF; -- parse and store the ids
3070
3071 -- Join the fnd user to get the user names
3072 FOR fnd_rec IN
3073 (select column_value , fndu.user_name user_name
3074 from
3075 table( cast(aw_user_list as zpb_num_tbl_type)) aw_users,
3076 fnd_user fndu
3077 where fndu.user_id = aw_users.column_value
3078 )
3079 LOOP
3080 ZPB_UTIL_PVT.AddUsersToAdHocRole(l_rolename, fnd_rec.user_name);
3081 END LOOP;
3082 x_role_name := l_rolename;
3083
3084 FND_MSG_PUB.Count_And_Get ( p_count => x_msg_count,
3085 p_data => x_msg_data );
3086 EXCEPTION
3087
3088 WHEN FND_API.G_EXC_ERROR THEN
3089
3090 ROLLBACK TO Set_Submit_Ntf_Recipients ;
3091 x_return_status := FND_API.G_RET_STS_ERROR;
3092 FND_MSG_PUB.Count_And_Get ( p_count => x_msg_count,
3093 p_data => x_msg_data );
3094
3095 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
3096
3097 ROLLBACK TO Set_Submit_Ntf_Recipients;
3098 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3099 FND_MSG_PUB.Count_And_Get ( p_count => x_msg_count,
3100 p_data => x_msg_data );
3101
3102 WHEN OTHERS THEN
3103
3104 ROLLBACK TO Set_Submit_Ntf_Recipients ;
3105 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3106
3107 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
3108 FND_MSG_PUB.Add_Exc_Msg ( G_PKG_NAME,
3109 l_api_name);
3110 END if;
3111
3112 FND_MSG_PUB.Count_And_Get ( p_count => x_msg_count,
3113 p_data => x_msg_data );
3114
3115 END Set_Submit_Ntf_Recipients;
3116
3117 /*=========================================================================+
3118 | PROCEDURE Set_Source_Type
3119 |
3120 |
3121 | DESCRIPTION
3122 | This procedure will be called by backend api to change the
3123 | copy_source_type_flag to 'Y' at redistribution.
3124 |
3125 +=========================================================================*/
3126
3127 PROCEDURE Set_Source_Type(
3128 p_api_version IN NUMBER,
3129 p_init_msg_list IN VARCHAR2,
3130 p_commit IN VARCHAR2,
3131 p_validation_level IN NUMBER,
3132 x_return_status OUT NOCOPY VARCHAR2,
3133 x_msg_count OUT NOCOPY NUMBER,
3134 x_msg_data OUT NOCOPY VARCHAR2,
3135 --
3136 p_ac_instance_id IN NUMBER)
3137
3138 IS
3139
3140 l_api_name CONSTANT VARCHAR2(30) := 'Set_Source_Type';
3141 l_api_version CONSTANT NUMBER := 1.0;
3142
3143 BEGIN
3144
3145 SAVEPOINT Set_Source_Type;
3146
3147 IF NOT FND_API.Compatible_API_Call ( l_api_version,
3148 p_api_version,
3149 l_api_name,
3150 G_PKG_NAME )
3151 THEN
3152 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
3153 END IF;
3154
3155
3156 IF FND_API.to_Boolean ( p_init_msg_list ) THEN
3157 FND_MSG_PUB.initialize ;
3158 END IF;
3159
3160 x_return_status := FND_API.G_RET_STS_SUCCESS ;
3161
3162 -- Initialize the parameters
3163
3164 -- API Body
3165 UPDATE zpb_dc_objects
3166 SET copy_source_type_flag = 'Y',
3167 create_solve_program_flag = 'Y',
3168 LAST_UPDATED_BY = fnd_global.USER_ID,
3169 LAST_UPDATE_DATE = SYSDATE,
3170 LAST_UPDATE_LOGIN = fnd_global.LOGIN_ID
3171 WHERE ac_instance_id = p_ac_instance_id;
3172
3173 FND_MSG_PUB.Count_And_Get ( p_count => x_msg_count,
3174 p_data => x_msg_data );
3175 EXCEPTION
3176
3177 WHEN FND_API.G_EXC_ERROR THEN
3178
3179 ROLLBACK TO Set_Source_Type ;
3180 x_return_status := FND_API.G_RET_STS_ERROR;
3181 FND_MSG_PUB.Count_And_Get ( p_count => x_msg_count,
3182 p_data => x_msg_data );
3183
3184 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
3185
3186 ROLLBACK TO Set_Source_Type ;
3187 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3188 FND_MSG_PUB.Count_And_Get ( p_count => x_msg_count,
3189 p_data => x_msg_data );
3190
3191 WHEN OTHERS THEN
3192
3193 ROLLBACK TO Set_Source_Type ;
3194 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3195
3196 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
3197 FND_MSG_PUB.Add_Exc_Msg ( G_PKG_NAME,
3198 l_api_name);
3199 END if;
3200
3201 FND_MSG_PUB.Count_And_Get ( p_count => x_msg_count,
3202 p_data => x_msg_data );
3203
3204 END Set_Source_Type;
3205
3206
3207 PROCEDURE Update_Template_View_Type(
3208 p_template_id IN NUMBER,
3209 p_view_type IN VARCHAR2,
3210 p_result_out OUT NOCOPY VARCHAR2)
3211
3212 IS
3213
3214 BEGIN
3215
3216 SAVEPOINT Update_Template_View_Type;
3217
3218 UPDATE ZPB_DC_OBJECTS SET view_type = p_view_type,
3219 create_solve_program_flag = 'Y' where
3220 template_id = p_template_id and (object_type = 'M' or object_type = 'E' or object_type = 'C');
3221
3222 p_result_out := 'S';
3223
3224 EXCEPTION
3225
3226 WHEN OTHERS THEN
3227 ROLLBACK TO Update_Template_View_Type;
3228 p_result_out := 'E';
3229
3230 END Update_Template_View_Type;
3231
3232
3233 PROCEDURE Update_Worksheet_View_Type(
3234 p_template_id IN NUMBER,
3235 p_object_id IN NUMBER,
3236 p_view_type IN VARCHAR2,
3237 p_result_out OUT NOCOPY VARCHAR2)
3238
3239 IS
3240 s_object_type VARCHAR2(1);
3241
3242 BEGIN
3243
3244 SAVEPOINT Update_Worksheet_View_Type;
3245
3246 SELECT object_type INTO s_object_type FROM zpb_dc_objects where
3247 object_id = p_object_id;
3248
3249 --Check if it is a Controller's Worksheet, If it is then we need to update
3250 --the records of type 'M' and 'E' also
3251 IF s_object_type = 'C' THEN
3252 Update_Template_View_Type(p_template_id,p_view_type,p_result_out);
3253 ELSE
3254 UPDATE zpb_dc_objects SET view_type = p_view_type,
3255 create_solve_program_flag = 'Y' where
3256 template_id = p_template_id and object_id = p_object_id;
3257 END IF;
3258
3259 p_result_out := 'S';
3260
3261 EXCEPTION
3262
3263 WHEN OTHERS THEN
3264 ROLLBACK TO Update_Worksheet_View_Type;
3265 p_result_out := 'E';
3266
3267 END Update_Worksheet_View_Type;
3268
3269 END ZPB_DC_OBJECTS_PVT;