[Home] [Help]
PACKAGE BODY: APPS.IES_SVY_DEPLOYMENT_PVT
Source
1 Package Body IES_SVY_DEPLOYMENT_PVT AS
2 /* $Header: iesdpypb.pls 120.1 2005/06/16 11:15:25 appldev $ */
3 /*==========================================================================+
4 | PROCEDURES. |
5 | Submit_Deployment. |
6 +==========================================================================*/
7
8
9 --global package variables.
10 g_sqlerrm varchar2(500);
11 g_sqlcode varchar2(500);
12
13 G_PKG_NAME CONSTANT VARCHAR2(30):='IES_SVY_DEPLOYMENT_PVT';
14 G_FILE_NAME CONSTANT VARCHAR2(12):='iesdpypb.pls';
15
16
17 ----------------------------------------------------------------------------------------------------------
18 -- Procedure
19 -- Submit_Deployment
20
21 -- PURPOSE
22 -- Submit Deployment to Fulfillemnt through Concurrent Manager at the specified_time.
23 --
24 -- PARAMETERS
25 -- deployment_id - survey deployment
26 -- list_entry_id - list entry identifier
27 -- template_id - fulfillment template identifier
28 -- NOTES
29 -- created rrsundar 05/03/2000
30 -- Modified vacharya 03/19/01 Need to provide value of p_commit as FND_API.G_TRUE when you call Submit_Deployment
31 -- the file has been modified to allow for PL/SQL Commits if certain conditions are met
32 ---------------------------------------------------------------------------------------------------------
33 Procedure Submit_Deployment
34 (
35 p_api_version IN NUMBER ,
36 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE ,
37 p_commit IN VARCHAR2 := FND_API.G_FALSE ,
38 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL ,
39 x_return_status OUT NOCOPY /* file.sql.39 change */ VARCHAR2 ,
40 x_msg_count OUT NOCOPY /* file.sql.39 change */ NUMBER ,
41 x_msg_data OUT NOCOPY /* file.sql.39 change */ VARCHAR2 ,
42 x_message OUT NOCOPY /* file.sql.39 change */ VARCHAR2 ,
43 p_user_id IN NUMBER ,
44 p_resp_id IN NUMBER ,
45 p_deployment_id IN NUMBER := NULL ,
46 p_list_entry_id IN NUMBER := NULL ,
47 p_template_id IN NUMBER := NULL ,
48 p_start_time IN DATE := NULL ,
49 p_reminder_type IN VARCHAR2 := NULL
50 ) IS
51
52 --PRAGMA AUTONOMOUS_TRANSACTION;
53
54 l_return_number NUMBER := NULL ;
55 l_api_version NUMBER := 1.0 ;
56 l_return_status VARCHAR2(1000) ;
57 l_msg_count NUMBER ;
58 l_msg_data VARCHAR2(1000) ;
59 l_Error_Msg VARCHAR2(2000) ;
60 l_ret_code NUMBER := NULL ;
61 l_err_buf VARCHAR2(80) := NULL ;
62 l_reminder_type VARCHAR2(10) :=NULL ;
63 l_reminder_flag BOOLEAN := FALSE;
64 l_no_of_reminder NUMBER ;
65 l_reminder_interval NUMBER ;
66 l_survey_reminder_id NUMBER ;
67 l_current_date DATE ;
68 l_deploy_date DATE ;
69 l_response_end_date DATE ;
70 l_reminder_hst_id NUMBER := NULL ;
71 l_reminder_template_id NUMBER ;
72 l_api_name VARCHAR2(30) := 'Submit_Deployment';
73 l_schedule_id NUMBER ;
74
75 CURSOR reminder_cur IS
76 SELECT NVL(SVR.NO_OF_REMINDERS,0) no_of_reminders
77 ,NVL(SVR.REMINDER_INTERVAL,0) reminder_interval
78 ,SVR.SURVEY_REMINDER_ID survey_reminder_id
79 ,SDP.RESPONSE_END_DATE response_end_date
80 ,SDP.REMINDER_TEMPLATE_ID reminder_template_id
81 FROM IES_SVY_REMINDERS_V SVR
82 ,IES_SVY_DEPLYMENTS_V SDP
83 WHERE SDP.SURVEY_DEPLOYMENT_ID = SVR.SURVEY_DEPLOYMENT_ID
84 AND SDP.SURVEY_DEPLOYMENT_ID = p_deployment_id;
85
86 BEGIN
87
88
89 -- Check API Compatability
90
91 IF NOT FND_API.Compatible_API_Call (
92 l_api_version,
93 p_api_version,
94 l_api_name,
95 G_PKG_NAME)
96 THEN
97 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
98 END IF;
99
100 -- Create a Savepoint for Rollback
101
102 SAVEPOINT Submit_Deployment;
103
104
105 -- Create Initial Summary records for deployment
106
107 IES_SVY_CREATE_INIT_RECORDS.CREATE_INITIAL_RECORDS(
108 p_deployment_id => p_deployment_id);
109
110
111 -- Populate Survey List Entries table
112
113 Populate_Survey_List_Entries
114 (p_api_version => 1.0,
115 p_init_msg_list => FND_API.G_TRUE,
116 p_commit => FND_API.G_FALSE,
117 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
118 x_return_status => l_return_status,
119 x_msg_count => l_msg_count,
120 x_msg_data => l_msg_data,
121 x_message => l_Error_Msg,
122 p_survey_deployment_id => p_deployment_id);
123
124 IF (l_return_status <> 'S') THEN
125 ROLLBACK TO Submit_Deployment;
126 x_return_status := l_return_status;
127 x_msg_data := l_msg_data;
128 x_msg_count := l_msg_count;
129 x_message := l_Error_Msg;
130 return;
131 END IF;
132
133 --Initialize message list if p_init_msg_list is TRUE.
134 IF FND_API.To_Boolean (p_init_msg_list) THEN
135 FND_MSG_PUB.initialize;
136 END IF;
137
138 -- Initialize API return status to success
139
140 x_return_status := FND_API.G_RET_STS_SUCCESS;
141 x_message := NULL;
142 x_msg_count := 0;
143 x_msg_data := NULL;
144
145 -- Check if Submit Deployment has been called for individual reminders
146
147 IF ((p_list_entry_id is not NULL) AND ( p_deployment_id is not NULL)) THEN
148 FM_Single_Request(
149 l_api_version ,
150 FND_API.G_FALSE,
151 FND_API.G_FALSE,
152 FND_API.G_VALID_LEVEL_FULL,
153 x_return_status,
154 x_msg_count,
155 x_msg_data,
156 x_message,
157 p_list_entry_id,
158 p_template_id,
159 p_deployment_id,
160 p_user_id);
161 -- If is not a single reminder but a "Deploy" request
162 ELSIF (( p_deployment_id is not NULL)AND (p_list_entry_id is NULL)) THEN
163 l_schedule_id := FND_REQUEST.SUBMIT_REQUEST(
164 application => 'IES',
165 program => 'SUBMIT_GROUP_REQUEST',
166 start_time => to_char(p_start_time,'DD-MON-YYYY HH24:MI'),
167 argument1 => p_api_version,
168 argument2 => p_init_msg_list,
169 argument3 => p_commit,
170 argument4 => p_validation_level,
171 argument5 => p_deployment_id,
172 argument6 => p_template_id,
173 argument7 => p_reminder_type,
174 argument8 => p_user_id,
175 argument9 => l_reminder_hst_id);
176 IF (l_schedule_id = 0) THEN
177 Update_Dep_Status(p_dep_id => p_deployment_id,
178 p_status => 'ERROR',
179 p_reminder_type => p_reminder_type,
180 p_update_flag => 'Y');
181 ELSE
182 Update_Dep_Status(p_dep_id => p_deployment_id,
183 p_status => 'PENDING',
184 p_reminder_type => p_reminder_type,
185 p_update_flag => 'Y');
186 UPDATE ies_svy_deplyments_all
187 SET concurrent_req_id = l_schedule_id
188 WHERE survey_deployment_id = p_deployment_id;
189 END IF;
190
191
192 -- Check if reminder needs to be set. Find out the dates on which reminder needs to be send and call
193 -- FND_REQUEST.SUBMIT_REQUEST for each request
194
195 SELECT response_end_date
196 INTO l_response_end_date
197 FROM ies_svy_deplyments_v
198 WHERE survey_deployment_id = p_deployment_id;
199
200 l_reminder_type := 'REMINDER';
201 l_reminder_flag := FALSE;
202
203 FOR reminder_rec in reminder_cur
204 LOOP
205 l_reminder_flag := TRUE;
206 l_deploy_date := p_start_time;
207 l_survey_reminder_id := reminder_rec.survey_reminder_id;
208 l_reminder_template_id := reminder_rec.reminder_template_id;
209 FOR loop_index IN 1 .. reminder_rec.no_of_reminders
210 LOOP
211 l_deploy_date := l_deploy_date + reminder_rec.reminder_interval;
212 SELECT IES_SVY_REMINDER_HST_S.nextval INTO l_reminder_hst_id FROM DUAL;
213 IF(l_deploy_date <= l_response_end_date) THEN
214 INSERT INTO ies_svy_reminder_hst_v
215 (survey_reminder_hst_id
216 ,object_version_number
217 ,created_by
218 ,creation_date
219 ,last_updated_by
220 ,last_update_date
221 ,last_update_login
222 ,survey_reminder_id
223 ,reminder_date)
224 VALUES
225 (l_reminder_hst_id
226 ,1
227 ,p_user_id
228 ,sysdate
229 ,p_user_id
230 ,sysdate
231 ,p_user_id
232 ,l_survey_reminder_id
233 ,sysdate
234 );
235 l_schedule_id := 0;
236 l_schedule_id := FND_REQUEST.SUBMIT_REQUEST(
237 application => 'IES',
238 program => 'SUBMIT_GROUP_REQUEST',
239 start_time => to_char(l_deploy_date,'DD-MON-YYYY HH24:MI'),
240 argument1 => p_api_version,
241 argument2 => p_init_msg_list,
242 argument3 => p_commit,
243 argument4 => p_validation_level,
244 argument5 => p_deployment_id,
245 argument6 => l_reminder_template_id,
246 argument7 => l_reminder_type,
247 argument8 => p_user_id,
248 argument9 => l_reminder_hst_id);
249 IF (l_schedule_id = 0) THEN
250 RAISE FND_API.G_EXC_ERROR;
251 ELSE
252 UPDATE ies_svy_reminder_hst_v
253 SET CONCURRENT_REQ_ID = l_schedule_id
254 WHERE SURVEY_REMINDER_HST_ID = l_reminder_hst_id;
255 END IF;
256 END IF;
257 END LOOP;
258 END LOOP;
259 END IF;
260
261 EXCEPTION
262 WHEN FND_API.G_EXC_ERROR THEN
263 ROLLBACK TO Submit_Deployment;
264 x_return_status := FND_API.G_RET_STS_ERROR;
265 x_message := SQLERRM;
266 FND_MSG_PUB.Count_And_Get
267 ( p_count => x_msg_count,
268 p_data => x_msg_data
269 );
270 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
271 ROLLBACK TO Submit_Deployment;
272 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
273 x_message := SQLERRM;
274 FND_MSG_PUB.Count_And_Get
275 ( p_count => x_msg_count ,
276 p_data => x_msg_data
277 );
278 WHEN OTHERS THEN
279 rollback to Submit_Deployment;
280 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
281 x_message := SQLERRM;
282 FND_MSG_PUB.Count_And_Get
283 (p_count => x_msg_count ,
284 p_data => x_msg_data
285 );
286 End Submit_Deployment;
287
288 ----------------------------------------------------------------------------------------------------------
289 -- Procedure
290 -- FM_Single_Request
291 -- PURPOSE Wrapper API to select the appropriate template details and submit a request to the
292 -- fulfillment engine.
293 --
294 --
295 -- PARAMETERS
296 -- NOTES
297 -- created kpandey 05/02/2000
298 ---------------------------------------------------------------------------------------------------------
299 PROCEDURE FM_Single_Request(
300 p_api_version IN NUMBER ,
301 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE ,
302 p_commit IN VARCHAR2 := FND_API.G_FALSE ,
303 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL ,
304 x_return_status OUT NOCOPY /* file.sql.39 change */ VARCHAR2 ,
305 x_msg_count OUT NOCOPY /* file.sql.39 change */ NUMBER ,
306 x_msg_data OUT NOCOPY /* file.sql.39 change */ VARCHAR2 ,
307 x_message OUT NOCOPY /* file.sql.39 change */ VARCHAR2 ,
308 p_list_entry_id IN NUMBER ,
309 p_template_id IN NUMBER ,
310 p_deployment_id IN NUMBER ,
311 p_user_id IN NUMBER
312 )
313
314 IS
315 l_api_version NUMBER := 1.0;
316 l_api_name CONSTANT VARCHAR2(30) := 'FM_Request_From_Survey';
317 l_commit VARCHAR2(5) := FND_API.G_FALSE;
318 l_full_name CONSTANT VARCHAR2(60) := G_PKG_NAME ||'.'|| l_api_name;
319 l_Error_Msg VARCHAR2(2000) ;
320 l_content_id VARCHAR2(30);
321 l_media_type VARCHAR2(30);
322 l_request_type VARCHAR2(20);
323 l_user_note VARCHAR2(1000);
324 l_document_type VARCHAR2(150);
325 l_template_id NUMBER;
326 l_party_id NUMBER;
327 l_user_id NUMBER;
328 l_server_id NUMBER;
329 l_request_id NUMBER;
330 l_subject VARCHAR2(100);
331 l_bind_var JTF_FM_REQUEST_GRP.G_VARCHAR_TBL_TYPE;
332 l_bind_var_type JTF_FM_REQUEST_GRP.G_VARCHAR_TBL_TYPE;
333 l_bind_val JTF_FM_REQUEST_GRP.G_VARCHAR_TBL_TYPE;
334
335 --
336 l_msg_count NUMBER;
337 l_msg_data VARCHAR2(1000);
338 l_return_status VARCHAR2(1000);
339 l_content_xml1 VARCHAR2(1000);
340 l_content_xml VARCHAR2(10000);
341 l_content_nm VARCHAR2(100);
342 l_email VARCHAR2(100);
343 l_printer VARCHAR2(100);
344 l_file_path VARCHAR2(100);
345 l_fax VARCHAR2(100);
346
347 l_content_no NUMBER;
348 l_content_type NUMBER;
349 l_content_type_text VARCHAR2(30);
350 v_count NUMBER;
351 l_survey_list_entry_id NUMBER;
352
353 CURSOR CCONTENT IS
354 SELECT CONTENT_NUMBER, CONTENT_TYPE_ID
355 FROM JTF_FM_TEMPLATE_CONTENTS, JTF_FM_AMV_ITEMS_VL
356 WHERE JTF_FM_TEMPLATE_CONTENTS.TEMPLATE_ID = p_template_id
357 AND JTF_FM_TEMPLATE_CONTENTS.CONTENT_NUMBER = JTF_FM_AMV_ITEMS_VL.ITEM_ID
358 AND JTF_FM_TEMPLATE_CONTENTS.F_DELETEDFLAG IS NULL;
359
360 CURSOR CEMAILADD IS
361 SELECT EMAIL_ADDRESS
362 FROM AMS_LIST_ENTRIES
363 WHERE LIST_ENTRY_ID = p_list_entry_id;
364
365 CURSOR CEMAILSUBJECTHEADING IS
366 SELECT EMAIL_SUBJECT_HEADING
367 FROM IES_SVY_DEPLYMENTS_ALL
368 WHERE SURVEY_DEPLOYMENT_ID = p_deployment_id;
369
370 BEGIN
371 SAVEPOINT FM_Request_From_Survey;
372
373 -- Check API Compatibility
374
375 IF NOT FND_API.Compatible_API_Call (
376 l_api_version,
377 p_api_version,
378 l_api_name,
379 G_PKG_NAME) THEN
380 x_message := 'API not compatible';
381 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
382 END IF;
383
384 --Initialize message list if p_init_msg_list is TRUE.
385 IF FND_API.To_Boolean (p_init_msg_list) THEN
386 FND_MSG_PUB.initialize;
387 END IF;
388
389 -- Debug Message
390 IF FND_MSG_PUB.Check_Msg_level (FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW) THEN
391 FND_MESSAGE.Set_Name('JTF','JTF_FM_API_DEBUG_MESSAGE');
392 FND_MESSAGE.Set_Token('ARG1',l_full_name||': Start');
393 FND_MSG_PUB.Add;
394 END IF;
395
396 -- Initialize API return status to success
397 x_return_status := FND_API.G_RET_STS_SUCCESS;
398
399
400 OPEN CEMAILADD;
401 FETCH CEMAILADD into l_email;
402 IF (CEMAILADD%NOTFOUND) THEN
403 CLOSE CEMAILADD;
404 l_Error_Msg := 'No e-mail address for this p_list_entry_id';
405 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
406 FND_MESSAGE.set_name('IES', 'IES_SVY_API_MISSING_EMAIL_ID');
407 FND_MSG_PUB.Add;
408 END IF;
409 x_message := l_Error_Msg;
410 RAISE FND_API.G_EXC_ERROR;
411 END IF;
412 CLOSE CEMAILADD;
413
414
415 OPEN CEMAILSUBJECTHEADING;
416 FETCH CEMAILSUBJECTHEADING into l_subject;
417 IF (CEMAILSUBJECTHEADING%NOTFOUND) THEN
418 CLOSE CEMAILSUBJECTHEADING;
419 l_Error_Msg := 'No e-mail subject heading for this p_deployment_id';
420 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
421 FND_MESSAGE.set_name('IES', 'IES_SVY_API_MISSING_EMAIL_ID');
422 FND_MSG_PUB.Add;
423 END IF;
424 x_message := l_Error_Msg;
425 RAISE FND_API.G_EXC_ERROR;
426 END IF;
427 CLOSE CEMAILSUBJECTHEADING;
428
429
430 l_bind_var(1) := 'party_id';
431 l_bind_val(1) := p_list_entry_id;
432 l_bind_var_type(1) := 'NUMBER';
433
434 l_bind_var(2) := 'deployment_id';
435 l_bind_val(2) := p_deployment_id;
436 l_bind_var_type(2) := 'NUMBER';
437
438 -- Start the fulfillment request. The output request_id must be passed
439 -- to all subsequent calls made for this request.
440
441 JTF_FM_REQUEST_GRP.Start_Request
442 (
443 p_api_version => l_api_version,
444 x_return_status => l_return_status,
445 x_msg_count => l_msg_count,
446 x_msg_data => l_msg_data,
447 x_request_id => l_request_id
448 );
449
450 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
451 RAISE FND_API.G_EXC_ERROR;
452 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
453 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
454 END IF;
455
456
457 -- Check if a template has contents (MD or collateral)
458 -- if it does then for each one, loop to genereate the content id, content_type_id type 20 is "QUERY"
459 -- and 10 is Collateral. Get content for each, conactenate and submit.
460
461 v_count :=0;
462 OPEN CCONTENT;
463 LOOP
464 FETCH CCONTENT into l_content_no, l_content_type;
465
466 EXIT WHEN CCONTENT%NOTFOUND OR CCONTENT%ROWCOUNT >2;
467 v_count := v_count + 1;
468
469 IF (l_content_type = 10) THEN
470 l_content_type_text := 'COLLATERAL';
471 ELSIF (l_content_type = 20) THEN
472 l_content_type_text := 'QUERY';
473 END IF;
474
475 l_content_id := l_content_no;
476 l_media_type := 'EMAIL';
477 l_request_type := l_content_type_text;
478 l_user_note := ' ';
479
480
481 -- This call gets the XML string for the content(Master Document) with
482 -- the parameters as defined above
483 JTF_FM_REQUEST_GRP.Get_Content_XML
484 (
485 p_api_version => l_api_version,
486 x_return_status => l_return_status,
487 x_msg_count => l_msg_count,
488 x_msg_data => l_msg_data,
489 p_content_id => l_content_id,
490 p_content_nm => l_content_nm,
491 p_document_type => l_document_type,
492 p_media_type => l_media_type,
493 p_printer => l_printer,
494 p_email => l_email,
495 p_file_path => l_file_path,
496 p_fax => l_fax,
497 p_user_note => l_user_note,
498 p_content_type => l_request_type,
499 p_bind_var => l_bind_var,
500 p_bind_val => l_bind_val,
501 p_bind_var_type => l_bind_var_type,
502 p_request_id => l_request_id,
503 x_content_xml => l_content_xml1);
504
505 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
506 RAISE FND_API.G_EXC_ERROR;
507 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
508 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
509 END IF;
510
511 -- The output XML string is stored in a local variable
512 l_content_xml := l_content_xml || l_content_xml1;
513
514
515 END LOOP;
516
517 IF(CCONTENT%NOTFOUND) AND ( v_count = 0)THEN
518 l_Error_Msg := 'Could not find content no in JTF_FM_TEMPLATE_CONTENTS';
519 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
520 FND_MESSAGE.set_name('JTF', 'IES_SVY_API_CONTENT_NOT_FOUND');
521 FND_MESSAGE.Set_Token('ARG1', to_char(l_content_no));
522 FND_MSG_PUB.Add;
523 END IF;
524 x_message := l_Error_Msg;
525 RAISE FND_API.G_EXC_ERROR;
526 END IF;
527
528 CLOSE CCONTENT;
529
530
531 -- Initialize Parameters for submitting the fulfillment request
532
533 l_user_id := p_user_id;
534 -- Submit the fulfillment request
535 JTF_FM_REQUEST_GRP.Submit_Request
536 (p_api_version => l_api_version,
537 p_commit => l_commit,
538 x_return_status => l_return_status,
539 x_msg_count => l_msg_count,
540 x_msg_data => l_msg_data,
541 p_subject => l_subject,
542 p_party_id => l_party_id,
543 p_user_id => l_user_id,
544 p_server_id => l_server_id,
545 p_queue_response => FND_API.G_TRUE,
546 p_content_xml => l_content_xml,
547 p_request_id => l_request_id
548 );
549
550 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
551 RAISE FND_API.G_EXC_ERROR;
552 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
553 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
554 END IF;
555
556
557 FND_MESSAGE.Set_Name('IES','IES_SVY_SUCCESS_REMINDER');
558 x_message := FND_MESSAGE.Get();
559 x_message := x_message || TO_CHAR(l_request_id);
560
561
562 --get the survey_list_entry_id to be inserted into ies_svy_entrs_remind_hst
563
564 SELECT survey_list_entry_id
565 INTO l_survey_list_entry_id
566 FROM ies_svy_list_entries
567 WHERE list_entry_id = p_list_entry_id
568 AND survey_deployment_id = p_deployment_id;
569
570
571 INSERT into ies_svy_entrs_remind_hst
572 (entry_reminder_hst_id
573 ,object_version_number
574 ,created_by
575 ,creation_date
576 ,last_updated_by
577 ,last_update_date
578 ,last_update_login
579 ,survey_list_entry_id
580 ,reminder_date
581 ,concurrent_req_id
582 ,fulfillment_req_id)
583 VALUES
584 (ies_svy_entrs_remind_hst_s.nextval
585 ,1
586 ,l_user_id
587 ,sysdate
588 ,l_user_id
589 ,sysdate
590 ,l_user_id
591 ,l_survey_list_entry_id
592 ,sysdate
593 ,null
594 ,l_request_id);
595
596 x_message := x_message || ' ,Entry inserted in ies_svy_entrs_remind_hst';
597 EXCEPTION
598 WHEN FND_API.G_EXC_ERROR THEN
599 ROLLBACK TO FM_Request_From_Survey;
600 x_return_status := FND_API.G_RET_STS_ERROR ;
601 x_message := x_message || ' ' ||SQLERRM;
602 -- write the error msg to log, if conc request is executing this code then
603 -- the log is viewable in concurrent manger log for that specific request
604 if(l_msg_count is not null) then
605 for l_index IN 1..l_msg_count loop
606 l_msg_data :=FND_MSG_PUB.Get(
607 p_msg_index => l_index,
608 p_encoded => FND_API.G_FALSE);
609 x_msg_data := l_msg_data;
610 end loop;
611 end if;
612 JTF_FM_REQUEST_GRP.Cancel_Request
613 (p_api_version => l_api_version,
614 p_commit => l_commit,
615 p_init_msg_list => FND_API.G_FALSE,
616 x_return_status => l_return_status,
617 x_msg_count => l_msg_count,
618 x_msg_data => l_msg_data,
619 p_request_id => l_request_id);
620 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
621 ROLLBACK TO FM_Request_From_Survey;
622 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
623 x_message := x_message || ' ' || SQLERRM;
624 -- write the error msg to log, if conc request is executing this code then
625 -- the log is viewable in concurrent manger log for that specific request
626 if(l_msg_count is not null) then
627 for l_index IN 1..l_msg_count loop
628 l_msg_data :=FND_MSG_PUB.Get(
629 p_msg_index => l_index,
630 p_encoded => FND_API.G_FALSE);
631 x_msg_data := l_msg_data;
632 end loop;
633 end if;
634
635 JTF_FM_REQUEST_GRP.Cancel_Request
636 (p_api_version => l_api_version,
637 p_commit => l_commit,
638 p_init_msg_list => FND_API.G_FALSE,
639 x_return_status => l_return_status,
640 x_msg_count => l_msg_count,
641 x_msg_data => l_msg_data,
642 p_request_id => l_request_id);
643 WHEN OTHERS THEN
644 -- rollback all the database actions
645 ROLLBACK to FM_Request_From_Survey;
646 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
647 x_message := x_message || ' ' || SQLERRM;
648 -- write the error msg to log, if conc request is executing this code then
649 -- the log is viewable in concurrent manger log for that specific request
650 if(l_msg_count is not null) then
651 for l_index IN 1..l_msg_count loop
652 l_msg_data :=FND_MSG_PUB.Get(
653 p_msg_index => l_index,
654 p_encoded => FND_API.G_FALSE);
655 x_msg_data := l_msg_data;
656 end loop;
657 end if;
658 -- cancel the request just sent
659 JTF_FM_REQUEST_GRP.Cancel_Request
660 (p_api_version => l_api_version,
661 p_commit => l_commit,
662 p_init_msg_list => FND_API.G_FALSE,
663 x_return_status => l_return_status,
664 x_msg_count => l_msg_count,
665 x_msg_data => l_msg_data,
666 p_request_id => l_request_id);
667
668 END FM_Single_Request;
669
670 ----------------------------------------------------------------------------------------------------------
671 -- Procedure
672 -- FM_Group_Request
673 -- PURPOSE Wrapper API to select the appropriate template details and submit a
674 -- group (mass e-mail invitation) request to fulfillment engine
675 --
676 --
677 -- PARAMETERS
678 -- NOTES
679 -- created kpandey 05/07/2000
680 ---------------------------------------------------------------------------------------------------------
681 PROCEDURE FM_Group_Request(
682 errbuf OUT NOCOPY /* file.sql.39 change */ VARCHAR2,
683 retcode OUT NOCOPY /* file.sql.39 change */ NUMBER,
684 p_api_version IN NUMBER,
685 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
686 p_commit IN VARCHAR2 := FND_API.G_FALSE,
687 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
688 p_deployment_id IN NUMBER,
689 p_template_id IN NUMBER,
690 p_reminder_type IN VARCHAR2,
691 p_user_id IN NUMBER,
692 p_reminder_hst_id IN NUMBER
693 -- no other out params are allowed here since its called from concurrent manager
694 -- x_return_status OUT NOCOPY /* file.sql.39 change */ VARCHAR2,
695 -- x_msg_count OUT NOCOPY /* file.sql.39 change */ NUMBER,
696 -- x_msg_data OUT NOCOPY /* file.sql.39 change */ VARCHAR2,
697 -- x_message OUT NOCOPY /* file.sql.39 change */ VARCHAR2
698 )
699
700 IS
701 x_return_status varchar2(100);
702 x_msg_count NUMBER :=0;
703 x_msg_data VARCHAR2(1000) := '';
704 x_message VARCHAR2(1000) := '';
705 l_api_version NUMBER := 1.0;
706 l_api_name CONSTANT VARCHAR2(30) := 'FM_Request_From_Survey';
707 l_commit VARCHAR2(5) := FND_API.G_FALSE;
708 l_full_name CONSTANT VARCHAR2(60) := G_PKG_NAME ||'.'|| l_api_name;
709 l_Error_Msg VARCHAR2(2000);
710
711 l_content_id VARCHAR2(30);
712 l_media_type VARCHAR2(30);
713 l_request_type VARCHAR2(20);
714 l_user_note VARCHAR2(1000);
715 l_document_type VARCHAR2(150);
716 l_template_id NUMBER;
717 l_party_id NUMBER;
718 l_user_id NUMBER;
719 l_server_id NUMBER;
720 l_request_id NUMBER;
721 l_subject VARCHAR2(100);
722 l_list_type VARCHAR2(100);
723 l_bind_var JTF_FM_REQUEST_GRP.G_VARCHAR_TBL_TYPE ;
724 l_bind_var_type JTF_FM_REQUEST_GRP.G_VARCHAR_TBL_TYPE ;
725 l_bind_val JTF_FM_REQUEST_GRP.G_VARCHAR_TBL_TYPE ;
726
727 --
728 l_msg_count NUMBER;
729 l_msg_data VARCHAR2(1000);
730 l_return_status VARCHAR2(1000);
731 l_content_xml1 VARCHAR2(1000);
732 l_content_xml VARCHAR2(10000);
733 l_content_nm VARCHAR2(100);
734
735 l_mass_party_id JTF_FM_REQUEST_GRP.G_NUMBER_TBL_TYPE;
736 l_mass_survey_list_entry_id JTF_FM_REQUEST_GRP.G_NUMBER_TBL_TYPE;
737 l_mass_email JTF_FM_REQUEST_GRP.G_VARCHAR_TBL_TYPE;
738 l_mass_printer JTF_FM_REQUEST_GRP.G_VARCHAR_TBL_TYPE;
739 l_mass_fax JTF_FM_REQUEST_GRP.G_VARCHAR_TBL_TYPE;
740 l_mass_path JTF_FM_REQUEST_GRP.G_VARCHAR_TBL_TYPE;
741 l_mass_party_name JTF_FM_REQUEST_GRP.G_VARCHAR_TBL_TYPE;
742 l_mass_bind_var JTF_FM_REQUEST_GRP.G_VARCHAR_TBL_TYPE;
743 l_mass_bind_var_type JTF_FM_REQUEST_GRP.G_VARCHAR_TBL_TYPE;
744 l_mass_bind_val JTF_FM_REQUEST_GRP.G_VARCHAR_TBL_TYPE;
745
746 l_content_no NUMBER;
747 l_content_type NUMBER;
748 l_content_type_text VARCHAR2(30);
749 v_count NUMBER;
750 l_list_header_id ies_svy_deplyments_all.list_header_id%type;
751 l_list_entry_id ams_list_entries.list_entry_id%type;
752
753 l_anonymous_code VARCHAR2(10) := 'NO';
754 l_max_responses_per_person NUMBER;
755 l_no_of_responses_recd NUMBER := 0;
756 l_survey_reminder_id NUMBER := 0;
757 l_query_id NUMBER := 0;
758
759 CURSOR CCONTENT IS
760 SELECT CONTENT_NUMBER, CONTENT_TYPE_ID
761 FROM JTF_FM_TEMPLATE_CONTENTS, JTF_FM_AMV_ITEMS_VL
762 WHERE JTF_FM_TEMPLATE_CONTENTS.TEMPLATE_ID = p_template_id
763 AND JTF_FM_TEMPLATE_CONTENTS.CONTENT_NUMBER = JTF_FM_AMV_ITEMS_VL.ITEM_ID
764 AND JTF_FM_TEMPLATE_CONTENTS.F_DELETEDFLAG IS NULL;
765
766 CURSOR CEMAILSUBJECTHEADING IS
767 SELECT EMAIL_SUBJECT_HEADING
768 FROM IES_SVY_DEPLYMENTS_ALL
769 WHERE SURVEY_DEPLOYMENT_ID = p_deployment_id;
770
771
772 BEGIN
773 SAVEPOINT FM_Request_From_Survey;
774 IF NOT FND_API.Compatible_API_Call (
775 l_api_version,
776 p_api_version,
777 l_api_name,
778 G_PKG_NAME)
779 THEN
780 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
781 END IF;
782
783 --Initialize message list if p_init_msg_list is TRUE.
784 IF FND_API.To_Boolean (p_init_msg_list) THEN
785 FND_MSG_PUB.initialize;
786 END IF;
787
788 -- Debug Message
789 IF FND_MSG_PUB.Check_Msg_level (FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW) THEN
790 FND_MESSAGE.Set_Name('JTF','JTF_FM_API_DEBUG_MESSAGE');
791 FND_MESSAGE.Set_Token('ARG1',l_full_name||': Start');
792 FND_MSG_PUB.Add;
793 END IF;
794
795 -- Initialize API return status to success
796 x_return_status := FND_API.G_RET_STS_SUCCESS;
797 IF (p_deployment_id IS NULL) THEN
798 l_Error_Msg := 'Must pass p_deployment_id parameter';
799 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
800 FND_MESSAGE.set_name('IES', 'IES_SVY_API_MISSING_DEPLOYMENT_ID');
801 FND_MSG_PUB.Add;
802 END IF;
803 x_message := l_Error_Msg;
804 RAISE FND_API.G_EXC_ERROR;
805 END IF;
806
807 IF (p_template_id IS NULL) THEN
808 l_Error_Msg := 'Must pass p_template_id parameter';
809 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
810 FND_MESSAGE.set_name('IES', 'IES_SVY_API_MISSING_TEMPLATE_ID');
811 FND_MSG_PUB.Add;
812 END IF;
813 x_message := l_Error_Msg;
814 RAISE FND_API.G_EXC_ERROR;
815 END IF;
816
817 if (p_reminder_type = 'REMINDER') then
818 SELECT REMINDER_EMAIL_SUBJECT
819 INTO l_subject
820 FROM ies_svy_reminders_v
821 WHERE survey_deployment_id = p_deployment_id;
822 else
823 OPEN CEMAILSUBJECTHEADING;
824 FETCH CEMAILSUBJECTHEADING into l_subject;
825 IF (CEMAILSUBJECTHEADING%NOTFOUND) THEN
826 CLOSE CEMAILSUBJECTHEADING;
827 l_Error_Msg := 'No e-mail subject heading for this p_deployment_id';
828 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
829 FND_MESSAGE.set_name('IES', 'IES_SVY_API_MISSING_EMAIL_ID');
830 FND_MSG_PUB.Add;
831 END IF;
832 x_message := l_Error_Msg;
833 RAISE FND_API.G_EXC_ERROR;
834 END IF;
835 CLOSE CEMAILSUBJECTHEADING;
836 end if;
837
838 -- Start the fulfillment request. The output request_id must be passed
839 -- to all subsequent calls made for this request.
840 JTF_FM_REQUEST_GRP.Start_Request
841 (
842 p_api_version => l_api_version,
843 x_return_status => l_return_status,
844 x_msg_count => l_msg_count,
845 x_msg_data => l_msg_data,
846 x_request_id => l_request_id
847 );
848
849 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
850 RAISE FND_API.G_EXC_ERROR;
851 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
852 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
853 END IF;
854
855
856 --Check if a template has contents (MD or collateral)
857 --if it does then for each one, loop to genereate the content id, content_type_id type 20 is "QUERY"
858 --and 10 is Collateral. Get content for each, conactenate and submit.
859
860 l_bind_var(1) := 'deployment_id';
861 l_bind_val(1) := p_deployment_id;
862 l_bind_var_type(1) := 'NUMBER';
863
864 v_count :=0;
865 OPEN CCONTENT;
866 LOOP
867 v_count := v_count + 1;
868 FETCH CCONTENT into l_content_no, l_content_type;
869 EXIT WHEN CCONTENT%NOTFOUND OR CCONTENT%ROWCOUNT >2;
870 v_count := v_count + 1;
871
872 IF (l_content_type = 10) THEN
873 l_content_type_text := 'COLLATERAL';
874 ELSIF (l_content_type = 20) THEN
875 l_content_type_text := 'QUERY';
876 END IF;
877
878 l_content_id := l_content_no;
879 l_media_type := 'EMAIL';
880 l_request_type := l_content_type_text;
881 l_user_note := ' ';
882
883 -- This call gets the XML string for the content(Master Document) with
884 -- the parameters as defined above
885 JTF_FM_REQUEST_GRP.Get_Content_XML
886 (
887 p_api_version => l_api_version,
888 x_return_status => l_return_status,
889 x_msg_count => l_msg_count,
890 x_msg_data => l_msg_data,
891 p_content_id => l_content_id,
892 p_content_nm => l_content_nm,
893 p_document_type => l_document_type,
894 p_media_type => l_media_type,
895 -- p_printer => l_printer,
896 -- p_email => l_email,
897 -- p_file_path => l_file_path,
898 -- p_fax => l_fax,
899 p_user_note => l_user_note,
900 p_content_type => l_request_type,
901 p_bind_var => l_bind_var,
902 p_bind_val => l_bind_val,
903 p_bind_var_type => l_bind_var_type,
904 p_request_id => l_request_id,
905 x_content_xml => l_content_xml1);
906
907 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
908 RAISE FND_API.G_EXC_ERROR;
909 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
910 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
911 END IF;
912
913 -- The output XML string is stored in a local variable
914 l_content_xml := l_content_xml || l_content_xml1;
915 END LOOP;
916
917 IF(CCONTENT%NOTFOUND) AND ( v_count = 0)THEN
918 l_Error_Msg := 'Could not find content no in JTF_FM_TEMPLATE_CONTENTS';
919 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
920 FND_MESSAGE.set_name('JTF', 'IES_SVY_API_CONTENT_NOT_FOUND');
921 FND_MESSAGE.Set_Token('ARG1', to_char(l_content_no));
922 FND_MSG_PUB.Add;
923 END IF;
924 x_message := 'Could not find content no in JTF_FM_TEMPLATE_CONTENTS';
925 RAISE FND_API.G_EXC_ERROR;
926 END IF;
927 CLOSE CCONTENT;
928 /*
929 SELECT QUERY_ID
930 INTO l_query_id
931 FROM JTF_FM_QUERY
932 WHERE QUERY_NAME = 'SURVEY_LIST_QUERY';
933
934 l_mass_bind_var(1) := 'list_header_id';
935 l_mass_bind_val(1) := l_list_header_id;
936 l_mass_bind_var_type(1) := 'NUMBER';
937 */
938 l_user_id := p_user_id;
939 -- Submit the fulfillment request
940
941 JTF_FM_REQUEST_GRP.Submit_Mass_Request
942 ( p_api_version =>l_api_version,
943 p_init_msg_list=> FND_API.G_TRUE,
944 p_commit =>FND_API.G_FALSE,
945 p_validation_level=>FND_API.G_VALID_LEVEL_FULL,
946 x_return_status =>l_return_status,
947 x_msg_count =>l_msg_count,
948 x_msg_data =>l_msg_data,
949 p_template_id =>l_template_id,
950 p_subject => l_subject,
951 p_user_id =>l_user_id,
952 p_source_code_id =>FND_API.G_MISS_NUM,
953 p_source_code => FND_API.G_MISS_CHAR,
954 p_object_type =>FND_API.G_MISS_CHAR,
955 p_object_id =>FND_API.G_MISS_NUM,
956 p_order_id =>FND_API.G_MISS_NUM,
957 p_doc_id => FND_API.G_MISS_NUM,
958 p_doc_ref => FND_API.G_MISS_CHAR,
959 p_view_nm =>FND_API.G_MISS_CHAR,
960 p_server_id =>FND_API.G_MISS_NUM,
961 p_queue_response =>FND_API.G_FALSE,
962 p_extended_header =>FND_API.G_MISS_CHAR,
963 p_content_xml =>l_content_xml,
964 p_request_id =>l_request_id,
965 p_per_user_history =>FND_API.G_TRUE,
966 p_mass_query_id =>0,
967 p_list_type => 'NO_LIST_TYPE');
968
969
970 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
971 RAISE FND_API.G_EXC_ERROR;
972 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
973 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
974 END IF;
975
976 -- if p_reminder_type is null then its from the deploy button so update
977 -- ies_svy_deplyments_all and populate the ies_svy_list_entries with the survey sent date else
978 -- a record should be inserted in ies_svy_reminder_hst
979
980 IF(p_reminder_type is null) THEN
981 -- Set Deployment Status to be Active
982 Update_Dep_Status(p_dep_id => p_deployment_id,
983 p_status => 'ACTIVE',
984 p_reminder_type => p_reminder_type,
985 p_update_flag => 'Y');
986
987 -- Set Survey Sent Date.
988 UPDATE ies_svy_list_entries
989 SET survey_sent_date = sysdate
990 WHERE survey_deployment_id = p_deployment_id;
991
992 -- Update FM Request ID
993 UPDATE ies_svy_deplyments_v
994 SET fulfillment_req_id = l_request_id
995 WHERE survey_deployment_id = p_deployment_id;
996
997 ELSIF(p_reminder_type = 'REMINDER') THEN
998 --else a record should be inserted in ies_svy_reminder_hst if its a reminder
999
1000 UPDATE ies_svy_reminder_hst_v
1001 SET fulfillment_req_id = l_request_id
1002 WHERE SURVEY_REMINDER_HST_ID = p_reminder_hst_id;
1003
1004 END IF;
1005
1006 EXCEPTION
1007 WHEN FND_API.G_EXC_ERROR THEN
1008 ROLLBACK TO FM_Request_From_Survey;
1009 x_return_status := FND_API.G_RET_STS_ERROR ;
1010 fnd_file.put_line(fnd_file.log, x_message);
1011 x_message := x_message || ' ' ||SQLERRM;
1012 -- write the error msg to log, if conc request is executing this code then
1013 -- the log is viewable in concurrent manger log for that specific request
1014 if(l_msg_count is not null) then
1015 for l_index IN 1..l_msg_count loop
1016 l_msg_data :=FND_MSG_PUB.Get(
1017 p_msg_index => l_index,
1018 p_encoded => FND_API.G_FALSE);
1019 x_msg_data := l_msg_data;
1020 fnd_file.put_line(fnd_file.log, l_msg_data);
1021 end loop;
1022 end if;
1023 fnd_file.put_line(fnd_file.log, SQLERRM);
1024 FND_MSG_PUB.Count_And_Get
1025 (p_count => x_msg_count ,
1026 p_data => x_msg_data
1027 );
1028 JTF_FM_REQUEST_GRP.Cancel_Request
1029 (p_api_version => l_api_version,
1030 p_commit => l_commit,
1031 p_init_msg_list => FND_API.G_FALSE,
1032 x_return_status => l_return_status,
1033 x_msg_count => l_msg_count,
1034 x_msg_data => l_msg_data,
1035 p_request_id => l_request_id);
1036 Update_Dep_Status(p_dep_id => p_deployment_id,
1037 p_status => 'ERROR',
1038 p_reminder_type => p_reminder_type,
1039 p_update_flag => 'Y');
1040 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1041 ROLLBACK TO FM_Request_From_Survey;
1042 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1043 fnd_file.put_line(fnd_file.log, x_message);
1044 x_message := x_message || ' ' ||SQLERRM;
1045 -- write the error msg to log, if conc request is executing this code then
1046 -- the log is viewable in concurrent manger log for that specific request
1047 if(l_msg_count is not null) then
1048 for l_index IN 1..l_msg_count loop
1049 l_msg_data :=FND_MSG_PUB.Get(
1050 p_msg_index => l_index,
1051 p_encoded => FND_API.G_FALSE);
1052 x_msg_data := l_msg_data;
1053 fnd_file.put_line(fnd_file.log, l_msg_data);
1054 end loop;
1055 end if;
1056 fnd_file.put_line(fnd_file.log, SQLERRM);
1057 JTF_FM_REQUEST_GRP.Cancel_Request
1058 (p_api_version => l_api_version,
1059 p_commit => l_commit,
1060 p_init_msg_list => FND_API.G_FALSE,
1061 x_return_status => l_return_status,
1062 x_msg_count => l_msg_count,
1063 x_msg_data => l_msg_data,
1064 p_request_id => l_request_id);
1065 Update_Dep_Status(p_dep_id => p_deployment_id,
1066 p_status => 'ERROR',
1067 p_reminder_type => p_reminder_type,
1068 p_update_flag => 'Y');
1069
1070 WHEN OTHERS THEN
1071 rollback to FM_Request_From_Survey;
1072 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1073 fnd_file.put_line(fnd_file.log, x_message);
1074 x_message := x_message || ' ' ||SQLERRM;
1075 -- write the error msg to log, if conc request is executing this code then
1076 -- the log is viewable in concurrent manger log for that specific request
1077 if(l_msg_count is not null) then
1078 for l_index IN 1..l_msg_count loop
1079 l_msg_data :=FND_MSG_PUB.Get(
1080 p_msg_index => l_index,
1081 p_encoded => FND_API.G_FALSE);
1082 x_msg_data := l_msg_data;
1083 fnd_file.put_line(fnd_file.log, l_msg_data);
1084 end loop;
1085 end if;
1086 fnd_file.put_line(fnd_file.log, SQLERRM);
1087
1088 -- cancel the request just sent
1089 JTF_FM_REQUEST_GRP.Cancel_Request
1090 (p_api_version => l_api_version,
1091 p_commit => l_commit,
1092 p_init_msg_list => FND_API.G_FALSE,
1093 x_return_status => l_return_status,
1094 x_msg_count => l_msg_count,
1095 x_msg_data => l_msg_data,
1096 p_request_id => l_request_id);
1097 Update_Dep_Status(p_dep_id => p_deployment_id,
1098 p_status => 'ERROR',
1099 p_reminder_type => p_reminder_type,
1100 p_update_flag => 'Y');
1101
1102 END FM_Group_Request;
1103
1104 ----------------------------------------------------------------------------------------------------------
1105 -- Procedure
1106 -- Populate_Survey_List_Entries
1107 -- PURPOSE Wrapper API to populate IES_SVY_LIST_ENTRIES based on the list_header_id and .
1108 -- deployment_id
1109 --
1110 -- PARAMETERS
1111 -- NOTES
1112 -- created kpandey 06/07/2000
1113 ---------------------------------------------------------------------------------------------------------
1114
1115 PROCEDURE Populate_Survey_List_Entries
1116 (
1117 p_api_version IN NUMBER ,
1118 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE ,
1119 p_commit IN VARCHAR2 := FND_API.G_FALSE ,
1120 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL ,
1121 x_return_status OUT NOCOPY /* file.sql.39 change */ VARCHAR2 ,
1122 x_msg_count OUT NOCOPY /* file.sql.39 change */ NUMBER ,
1123 x_msg_data OUT NOCOPY /* file.sql.39 change */ VARCHAR2 ,
1124 x_message OUT NOCOPY /* file.sql.39 change */ VARCHAR2 ,
1125 p_survey_deployment_id IN NUMBER
1126 )
1127
1128 IS
1129
1130 --PRAGMA AUTONOMOUS_TRANSACTION;
1131
1132 l_api_version NUMBER := 1.0 ;
1133 l_commit VARCHAR2(5) := FND_API.G_FALSE ;
1134 l_msg_count NUMBER ;
1135 l_msg_data VARCHAR2(1000) ;
1136 l_return_status VARCHAR2(1000) ;
1137 l_Error_Msg VARCHAR2(2000) ;
1138 l_random_number BINARY_INTEGER ;
1139 l_total_list_entries NUMBER := 0 ;
1140 l_list_header_id IES_SVY_DEPLYMENTS_ALL.LIST_HEADER_ID%TYPE ;
1141 l_list_entry_id AMS_LIST_ENTRIES.LIST_ENTRY_ID%TYPE ;
1142 l_seed NUMBER ;
1143 l_time_in_sec NUMBER ;
1144 l_api_name VARCHAR2(30) := 'Populate_Survey_List_Entries' ;
1145 l_count_value NUMBER ;
1146 l_full_name CONSTANT VARCHAR2(60) := G_PKG_NAME ||'.'|| l_api_name;
1147
1148 CURSOR CLISTENTRYID IS
1149 SELECT LIST_ENTRY_ID
1150 FROM AMS_LIST_ENTRIES
1151 WHERE LIST_HEADER_ID = l_list_header_id
1152 AND ENABLED_FLAG = 'Y';
1153 BEGIN
1154
1155 SAVEPOINT Populate_Survey_List_Entries;
1156 -- Submit the fulfillment request
1157 -- l_api_name := 'Populate_Survey_List_Entries';
1158 IF NOT FND_API.Compatible_API_Call (
1159 l_api_version,
1160 p_api_version,
1161 l_api_name,
1162 G_PKG_NAME)
1163 THEN
1164 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1165 END IF;
1166
1167 --Initialize message list if p_init_msg_list is TRUE.
1168 IF FND_API.To_Boolean (p_init_msg_list) THEN
1169 FND_MSG_PUB.initialize;
1170 END IF;
1171 -- Debug Message
1172 IF FND_MSG_PUB.Check_Msg_level (FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW) THEN
1173 FND_MESSAGE.Set_Name('JTF','JTF_FM_API_DEBUG_MESSAGE');
1174 FND_MESSAGE.Set_Token('ARG1',l_full_name||': Start');
1175 FND_MSG_PUB.Add;
1176 END IF;
1177 -- Initialize API return status to success
1178 x_return_status := FND_API.G_RET_STS_SUCCESS;
1179
1180 IF (p_survey_deployment_id IS NULL) THEN
1181 l_Error_Msg := 'Must pass p_survey_deployment_id parameter';
1182 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
1183 FND_MESSAGE.set_name('IES', 'IES__SVY_API_MISSING_DEPLOYMENT_ID');
1184 FND_MSG_PUB.Add;
1185 END IF;
1186 x_message := l_Error_Msg;
1187 RAISE FND_API.G_EXC_ERROR;
1188 END IF;
1189
1190 BEGIN
1191 SELECT LIST_HEADER_ID INTO l_list_header_id
1192 FROM IES_SVY_DEPLYMENTS_ALL
1193 WHERE SURVEY_DEPLOYMENT_ID = p_survey_deployment_id;
1194
1195 EXCEPTION
1196 WHEN NO_DATA_FOUND THEN
1197 l_Error_Msg := 'Deployment doesn''t have list_header_id';
1198 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
1199 FND_MESSAGE.set_name('IES', 'IES_SVY_API_MISSING_HEADER_ID');
1200 FND_MSG_PUB.Add;
1201 END IF;
1202 x_message := l_Error_Msg;
1203 RAISE FND_API.G_EXC_ERROR;
1204 END;
1205
1206 BEGIN
1207 SELECT COUNT(*) INTO l_total_list_entries
1208 FROM AMS_LIST_ENTRIES
1209 WHERE LIST_HEADER_ID = l_list_header_id;
1210
1211 EXCEPTION
1212 WHEN NO_DATA_FOUND THEN
1213 l_Error_Msg := 'AMS_LIST_ENTRIES doesn''t have list_header_id';
1214 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
1215 FND_MESSAGE.set_name('IES', 'IES_SVY_API_MISSING_HEADER_ID');
1216 FND_MSG_PUB.Add;
1217 END IF;
1218 x_message := l_Error_Msg;
1219 RAISE FND_API.G_EXC_ERROR;
1220 END;
1221
1222 IF l_total_list_entries > 0 THEN
1223 l_time_in_sec := dbms_utility.get_time();
1224 l_seed := MOD(l_time_in_sec,100000);
1225 --DBMS_OUTPUT.PUT_LINE('seedis '|| l_seed);
1226 dbms_random.initialize (l_seed);
1227
1228 -- To check if concurrent manager has entered values for particular deployment ID on prior failure.
1229 SELECT count(*) INTO l_count_value
1230 FROM IES_SVY_LIST_ENTRIES
1231 WHERE SURVEY_DEPLOYMENT_ID = p_survey_deployment_id;
1232
1233 IF l_count_value < 1 THEN
1234 OPEN CLISTENTRYID;
1235 LOOP
1236 FETCH CLISTENTRYID into l_list_entry_id;
1237 EXIT WHEN CLISTENTRYID%NOTFOUND;
1238 l_random_number := ABS(dbms_random.random);
1239 --DBMS_OUTPUT.PUT_LINE('Random no is '||l_random_number);
1240 --DBMS_OUTPUT.PUT_LINE('list_entry_id is '|| ABS(l_list_entry_id));
1241
1242 insert into ies_svy_list_entries
1243 (
1244 SURVEY_LIST_ENTRY_ID
1245 ,OBJECT_VERSION_NUMBER
1246 ,CREATED_BY
1247 ,CREATION_DATE
1248 ,LAST_UPDATED_BY
1249 ,LAST_UPDATE_DATE
1250 ,LAST_UPDATE_LOGIN
1251 ,SURVEY_DEPLOYMENT_ID
1252 ,LIST_ENTRY_ID
1253 ,RESPONDENT_ID
1254 )
1255 values
1256 ( ies_svy_list_entries_s.nextval
1257 ,1
1258 ,-1
1259 ,sysdate
1260 ,-1
1261 ,sysdate
1262 ,-1
1263 ,p_survey_deployment_id
1264 ,l_list_entry_id
1265 ,l_random_number
1266 );
1267
1268 END LOOP;
1269 CLOSE CLISTENTRYID;
1270 dbms_random.terminate;
1271
1272 END IF;
1273 ELSIF l_total_list_entries = 0 THEN
1274 -- vacharya: Hard Coded x_message 'IES_SVY_EMPTY_AMS_LIST' remember to enter it in FND_NEW_MESSAGE
1275 x_message := 'IES_SVY_EMPTY_AMS_LIST';
1276 RAISE FND_API.G_EXC_ERROR;
1277
1278 END IF;
1279
1280
1281 -- DBMS_OUTPUT.PUT_LINE('Return Status: '||l_return_status);
1282 -- DBMS_OUTPUT.PUT_LINE('Message_Count: '||l_msg_count);
1283 -- DBMS_OUTPUT.PUT_LINE('Message Data: '||l_msg_data);
1284
1285 EXCEPTION
1286 WHEN FND_API.G_EXC_ERROR THEN
1287
1288 ROLLBACK TO Populate_Survey_List_Entries;
1289 x_return_status := FND_API.G_RET_STS_ERROR ;
1290 --vacharya: Commented out until the message 'IES_SVY_EMPTY_AMS_LIST' is inserted in the FND_NEW_MESSAGES table
1291 x_message := x_message || SQLERRM;
1292 FND_MSG_PUB.Count_And_Get
1293 ( p_count => x_msg_count ,
1294 p_data => x_msg_data
1295 );
1296 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1297 ROLLBACK TO Populate_Survey_List_Entries;
1298 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1299 x_message := x_message || SQLERRM;
1300 FND_MSG_PUB.Count_And_Get
1301 ( p_count => x_msg_count ,
1302 p_data => x_msg_data
1303 );
1304 WHEN OTHERS THEN
1305 rollback to Populate_Survey_List_Entries;
1306 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1307 x_message := x_message || SQLERRM;
1308
1309 FND_MSG_PUB.Count_And_Get
1310 ( p_count => x_msg_count ,
1311 p_data => x_msg_data
1312 );
1313
1314 END Populate_Survey_List_Entries;
1315
1316
1317 Procedure Update_Dep_Status (p_dep_id NUMBER,
1318 p_status VARCHAR2,
1319 p_reminder_type VARCHAR2,
1320 p_update_flag VARCHAR2) IS
1321
1322 l_deployment_status_code VARCHAR2(30);
1323 l_dep_count_active NUMBER := 0;
1324 l_survey_cycle_id NUMBER := 0;
1325 l_survey_id NUMBER :=0;
1326
1327 BEGIN
1328 IF (p_reminder_type is null) THEN
1329
1330 SELECT DEPLOYMENT_STATUS_CODE, SURVEY_CYCLE_ID
1331 INTO l_deployment_status_code, l_survey_cycle_id
1332 FROM IES_SVY_DEPLYMENTS_V
1333 WHERE SURVEY_DEPLOYMENT_ID = p_dep_id;
1334
1335 SELECT count(*)
1336 INTO l_dep_count_active
1337 FROM IES_SVY_DEPLYMENTS_V
1338 WHERE SURVEY_CYCLE_ID = l_survey_cycle_id
1339 AND DEPLOYMENT_STATUS_CODE = 'ACTIVE';
1340
1341 if (p_update_flag = 'Y') then
1342 UPDATE IES_SVY_DEPLYMENTS_V
1343 SET DEPLOYMENT_STATUS_CODE = p_status
1344 where survey_deployment_id = p_dep_id;
1345 end if;
1346
1347 IF ((l_dep_count_active is null) OR (l_dep_count_active = 0)) THEN
1348 SELECT SURVEY_ID
1349 INTO l_survey_id
1350 FROM IES_SVY_CYCLES_V
1351 WHERE SURVEY_CYCLE_ID = l_survey_cycle_id;
1352
1353 IF ((p_status = 'PENDING') OR (p_status = 'ACTIVE')) THEN
1354 UPDATE IES_SVY_SURVEYS_V
1355 SET SURVEY_STATUS_CODE = 'ACTIVE'
1356 WHERE SURVEY_ID = l_survey_id;
1357 END IF;
1358 END IF;
1359 END IF;
1360
1361 END;
1362
1363 END IES_SVY_DEPLOYMENT_PVT;