PACKAGE BODY: APPS.IES_SVY_DPYLMENT_STATUS_PVT

Source

1 PACKAGE BODY IES_SVY_DPYLMENT_STATUS_PVT AS
2 /* $Header: iesdpstb.pls 120.1 2005/06/16 11:14:29 appldev  $ */
3 ----------------------------------------------------------------------------------------------------------
4 -- Procedure
5 --   Submit_Deployment
6 
7 -- PURPOSE
8 --   Submit Deployment to Concurrent Manager at the specified_time.
9 --
10 -- PARAMETERS
11 
12 -- NOTES
13 -- created rrsundar 05/03/2000
14 ---------------------------------------------------------------------------------------------------------
15 Procedure  Update_Deployment_Status
16 (
17     ERRBUF				 OUT NOCOPY /* file.sql.39 change */ VARCHAR2,
18     RETCODE 				 OUT NOCOPY /* file.sql.39 change */ BINARY_INTEGER
19 ) IS
20   l_error_msg         	   VARCHAR2(2000);
21   l_ret_code                NUMBER              := NULL ;
22   l_dep_count                NUMBER              := 0;
23   l_cycle_count                NUMBER              := 0;
24   l_err_buf                 VARCHAR2(80)        := NULL ;
25 
26   CURSOR dep_c IS
27   SELECT survey_deployment_id, isdv.survey_cycle_id , survey_id
28   FROM ies_svy_deplyments_v isdv, ies_svy_cycles_v iscv
29   WHERE response_end_date < sysdate
30   and deployment_status_code = 'ACTIVE'
31   and isdv.survey_cycle_id = iscv.survey_cycle_id
32   FOR UPDATE of deployment_status_code;
33 
34 begin
35 	SAVEPOINT update_deployment_status;
36 
37 	for c1_rec in dep_c loop
38 		update ies_svy_deplyments_v
39 		set deployment_status_code = 'CLOSED'
40 		where survey_deployment_id = c1_rec.survey_deployment_id;
41 
42 		SELECT count(*)
43 		INTO   l_dep_count
44 		FROM   ies_svy_deplyments_v
45 		WHERE  survey_cycle_id = c1_rec.survey_cycle_id
46 		AND    deployment_status_code = 'ACTIVE';
47 
48 	     if (l_dep_count = 0)  then
49 			UPDATE ies_svy_cycles_v
50 			SET cycle_status_code = 'OPEN'
51 			WHERE survey_cycle_id = c1_rec.survey_cycle_id;
52 		end if;
53 
54 		select count(*)
55 		INTO l_cycle_count
56 		FROM ies_svy_cycles_v
57 		WHERE survey_id = c1_rec.survey_id
58 		AND cycle_status_code = 'ACTIVE';
59 
60 		if (l_cycle_count = 0) then
61 			UPDATE ies_svy_surveys_v
62 			SET survey_status_code = 'OPEN'
63 			WHERE survey_id = c1_rec.survey_id;
64 		end if;
65 
66 	end loop;
67 
68 exception
69 		when others  then
70 			FND_MESSAGE.SET_NAME('IES', 'IES_SVY_UPDATE_DEPLOY_STATUS');
71 		     l_error_msg := FND_MESSAGE.GET;
72 			fnd_file.put_line(fnd_file.log, l_error_msg);
73 			ERRBUF := l_error_msg;
74 			RETCODE := -1;
75 			rollback to update_deployment_status;
76 end Update_Deployment_Status;
77 end IES_SVY_DPYLMENT_STATUS_PVT;