1 PACKAGE BODY PAY_HRMS_ACCESS_PKG AS
2 /* $Header: pyhraces.pkb 120.0 2005/05/29 05:40 appldev noship $ */
3
4 PROCEDURE Disable_Enable_HRMS (
5 ERRBUF out nocopy VARCHAR2
6 ,RETCODE out nocopy VARCHAR2
7 ) as
8
9 --
10 -- cursor to check the status of 'STATUS' and 'ACTION' fields in
11 -- hr_legialation_installations
12
13 cursor csr_legislation_status is
14 Select
15 LEGISLATION_CODE -- which is set by datainstall utility.
16 from HR_LEGISLATION_INSTALLATIONS
17 where STATUS is null and ACTION is not null
18 or STATUS = 'I' and ACTION is not null;
19
20 --
21 -- cursor to check whether the fnd_install_processes table is created or not.
22 -- The table FND_INSTALL_PROCESSES is created when the adpatch utility starts
23 -- running the .drv file. So checking for its existence.
24
25 cursor csr_fnd_install_processes(p_fnd_schema in varchar2) is
26 Select
27 OBJECT_NAME
28 from ALL_OBJECTS
29 where OBJECT_NAME = 'FND_INSTALL_PROCESSES'
30 and OBJECT_TYPE ='TABLE'
31 and OWNER = p_fnd_schema;
32
33 cursor csr_get_sleep_time is
34 select
35 PARAMETER_VALUE
36 from PAY_ACTION_PARAMETERS
37 where PARAMETER_NAME = 'HRLEGDEL_SLEEP';
38 --
39
40 l_object_name all_objects.object_name%type;
41 l_flag_fnd_install_processes boolean;
42
43 l_flag_stop_process boolean; -- flag to represent the 'STATUS' and 'ACTION field
44 -- status
45
46 l_flag_install_process boolean; -- flag to represent the running state of
47 -- hrglobal.drv from 'R' state in
48 -- fnd_install_processes.
49
50 l_stmt_fnd_install_data varchar2(200);
51 l_cnt_fnd_install_data number;
52 l_legislation hr_legislation_installations.legislation_code%type;
53
54 l_sleep_time PAY_ACTION_PARAMETERS.PARAMETER_VALUE%type;
55
56 l_flag_offline boolean;
57 l_flag_online boolean;
58
59 --
60
61 NO_SUCH_TABLE EXCEPTION;
62 PRAGMA EXCEPTION_INIT (NO_SUCH_TABLE, -942);
63
64 INVALID_SYNONYM_INVALID EXCEPTION;
65 PRAGMA EXCEPTION_INIT (INVALID_SYNONYM_INVALID, -980);
66
67 l_result boolean;
68 l_prod_status varchar2(1);
69 l_industry varchar2(1);
70 l_fnd_schema varchar2(30);
71 --
72
73 begin
74
75 ERRBUF := null;
76 RETCODE := null;
77
78 -- initializing the flag = true as taking into consideration that
79 -- this will be called from SQL script only within the hrglobal.drv.
80 -- So by then the FND_INSTALL_PROCESSES will be created and also
81 -- the 'STATUS' and 'ACTION' flag will be set.
82 l_flag_stop_process := true;
83 l_flag_install_process := true;
84 l_flag_offline := true;
85 l_flag_online := false;
86 l_sleep_time := null;
87 l_stmt_fnd_install_data := 'select count(worker_id) from fnd_install_processes where status =''F''';
88
89 --
90 -- get fnd schema name
91 l_result := fnd_installation.get_app_info ('FND', l_prod_status, l_industry, l_fnd_schema );
92 --
93 -- opening the csr_fnd_install_processes cursor to check whether the
94 -- hrglobal.drv has started or not.
95 open csr_fnd_install_processes(l_fnd_schema);
96 fetch csr_fnd_install_processes into l_object_name;
97 if( csr_fnd_install_processes%found) then
98 l_flag_fnd_install_processes := true;
99 else
100 l_flag_fnd_install_processes := false;
101 end if;
102 close csr_fnd_install_processes;
103 --
104 open csr_get_sleep_time;
105 fetch csr_get_sleep_time into l_sleep_time;
106 if( csr_get_sleep_time%notfound) then
107 l_sleep_time := '10';
108 end if;
109 close csr_get_sleep_time;
110 --
111 while (l_flag_stop_process and l_flag_fnd_install_processes) loop
112
113 --
114 -- opening the cursor that check the status of 'STATUS' and 'ACTION'
115 -- fields in hr_legialation_installations.
116 open csr_legislation_status;
117 fetch csr_legislation_status into l_legislation;
118 if (csr_legislation_status%found) then
119 l_flag_stop_process := true;
120 else
121 l_flag_stop_process := false;
122 end if;
123 close csr_legislation_status;
124 --
125 open csr_fnd_install_processes(l_fnd_schema);
126 fetch csr_fnd_install_processes into l_object_name;
127 if (csr_fnd_install_processes%found) then
128 -- checking the status of worker threads.
129 --
130 EXECUTE IMMEDIATE l_stmt_fnd_install_data into l_cnt_fnd_install_data;
131 --
132 if (l_cnt_fnd_install_data = 0) then
133 l_flag_install_process := true;
134 else
135 l_flag_install_process := false;
136 end if;
137 else
138 l_flag_install_process := false;
139 end if;
140 close csr_fnd_install_processes;
141 --
142 if l_flag_stop_process and l_flag_install_process then
143 if NOT l_flag_offline then
144 FND_FORM_FUNCTIONS_PKG.SET_FUNCTION_MODE('PER_%', 'OFFLINE');
145 FND_FORM_FUNCTIONS_PKG.SET_FUNCTION_MODE('PAY_%', 'OFFLINE');
146 l_flag_offline := true;
147 l_flag_online := false;
148 end if;
149 else
150 if NOT l_flag_online then
151 FND_FORM_FUNCTIONS_PKG.SET_FUNCTION_MODE('PER_%', 'NONE');
152 FND_FORM_FUNCTIONS_PKG.SET_FUNCTION_MODE('PAY_%', 'NONE');
153 l_flag_online := true;
154 l_flag_offline := false;
155 end if;
156 end if;
157 --
158 -- opening the fnd_install_processes cursor to check whether the
159 -- hrglobal.drv has started or not.
160 open csr_fnd_install_processes(l_fnd_schema);
161 fetch csr_fnd_install_processes into l_object_name;
162 if( csr_fnd_install_processes%found) then
163 l_flag_fnd_install_processes := true;
164 else
165 l_flag_fnd_install_processes := false;
166 end if;
167 close csr_fnd_install_processes;
168 --
169 commit;
170 DBMS_LOCK.SLEEP(l_sleep_time);
171 end loop; -- end of while loop.
172
173 --
174
175 EXCEPTION
176 WHEN NO_SUCH_TABLE OR INVALID_SYNONYM_INVALID THEN
177 FND_FORM_FUNCTIONS_PKG.SET_FUNCTION_MODE('PER_%', 'NONE');
178 FND_FORM_FUNCTIONS_PKG.SET_FUNCTION_MODE('PAY_%', 'NONE');
179 commit;
180 WHEN OTHERS THEN
181 ERRBUF := sqlerrm;
182 RETCODE := sqlcode;
183 --
184 end Disable_Enable_HRMS;
185
186 END PAY_HRMS_ACCESS_PKG;
187