1 PACKAGE BODY hr_update_utility AS
2 /* $Header: hruptutil.pkb 120.0 2005/05/31 23:59:15 appldev noship $ */
3
4 -- ----------------------------------------------------------------------------
5 -- |----------------------------< submitRequest >-----------------------------|
6 -- ----------------------------------------------------------------------------
7 --
8 procedure submitRequest
9 (p_app_shortname in varchar2
10 ,p_update_name in varchar2
11 ,p_validate_proc in varchar2
12 ,p_business_group_id in number default null
13 ,p_legislation_code in varchar2 default null
14 ,p_argument1 in varchar2 default chr(0)
15 ,p_argument2 in varchar2 default chr(0)
16 ,p_argument3 in varchar2 default chr(0)
17 ,p_argument4 in varchar2 default chr(0)
18 ,p_argument5 in varchar2 default chr(0)
19 ,p_argument6 in varchar2 default chr(0)
20 ,p_argument7 in varchar2 default chr(0)
21 ,p_argument8 in varchar2 default chr(0)
22 ,p_argument9 in varchar2 default chr(0)
23 ,p_argument10 in varchar2 default chr(0)
24 ,p_request_id out nocopy number) is
25
26 l_usr_id number;
27 l_resp_id number;
28 l_resp_appl_id number;
29
30 l_update_process varchar2(5);
31
32 l_request_id number := null;
33
34 l_sql_stmt varchar2(250);
35
36 l_upg_def_id pay_upgrade_definitions.upgrade_definition_id%type;
37 l_upg_lvl pay_upgrade_definitions.upgrade_level%type;
38 l_upg_mthd pay_upgrade_definitions.upgrade_method%type;
39
40 /*
41 ** Local variables for use by get_request_status
42 */
43 l_phase varchar2(100);
44 l_status varchar2(100);
45 l_dev_phase varchar2(20);
46 l_dev_status varchar2(20);
47 l_message varchar2(100);
48 l_return boolean;
49
50 cursor csr_get_resp_details is
51 select application_id, responsibility_id
52 from fnd_responsibility
53 where responsibility_key='SYSTEM_ADMINISTRATOR';
54
55 cursor csr_get_user_details is
56 select user_id
57 from fnd_user
58 where user_name = 'SYSADMIN';
59
60 cursor csr_get_update_details is
61 select upgrade_definition_id,
62 upgrade_level,
63 upgrade_method
64 from pay_upgrade_definitions
65 where short_name = p_update_name;
66
67 /*
68 ** This cursor is used to get the status of a GUP
69 ** based process. In this case the CP will be submitted multiple
70 ** times but any check to prevent duplicate submission needs to
71 ** look at the individual parameter values passed to determine whether
72 ** a duplicate process to the one about to be submitted is already
73 ** running. In this case the critical parameters are BG id(argument7)
74 ** and the upgrade name(argument12). Ideally this check should be
75 ** supported within the AOL layer but until such time....
76 */
77 cursor csr_get_conc_req_status is
78 select cr.phase_code dev_phase,
79 cr.status_code dev_status,
80 cr.request_id request_id
81 from fnd_concurrent_programs cp,
82 fnd_concurrent_requests cr,
83 fnd_application a
84 where a.application_short_name = p_app_shortname
85 and a.application_id = cp.application_id
86 and cp.concurrent_program_name = 'PAY_GEN_UPG'
87 and cp.concurrent_program_id = cr.concurrent_program_id
88 and cr.argument7 = p_business_group_id
89 and cr.argument12 = 'UPG_DEF_NAME='||p_update_name
90 order by cr.request_date desc;
91
92 begin
93
94 p_request_id := null;
95 /*
96 ** Execute the procedure to determine whether the conc request is
97 ** required.
98 */
99 l_sql_stmt := 'begin '||p_validate_proc||'( :a ); end;';
100 execute immediate l_sql_stmt using out l_update_process;
101
102 if l_update_process = 'TRUE' then
103 /*
104 ** The update is required so submit a request for the SYSADMIN user using
105 ** the System Administrator responsibility.
106 */
107
108 /* Get the required IDs...
109 */
110 open csr_get_user_details;
111 fetch csr_get_user_details into l_usr_id;
112 close csr_get_user_details;
113
114 open csr_get_resp_details;
115 fetch csr_get_resp_details into l_resp_appl_id, l_resp_id;
116 close csr_get_resp_details;
117
118 /* Get some details of the update being submitted to determine
119 ** the correct submission mechanism.
120 */
121 open csr_get_update_details;
122 fetch csr_get_update_details into l_upg_def_id, l_upg_lvl,
123 l_upg_mthd;
124 if csr_get_update_details%NOTFOUND then
125 hr_utility.set_message(800, 'PER_51775_UPD_NAME_NOT_FOUND');
126 hr_utility.raise_error;
127 end if;
128 close csr_get_update_details;
129
130 /* Initiate an APPS session as SYSADMIN
131 */
132 fnd_global.apps_initialize(user_id => l_usr_id,
133 resp_id => l_resp_id,
134 resp_appl_id => l_resp_appl_id);
135
136 if l_upg_mthd = 'SQLPLUS' then
137
138 /* The update_name passed holds the name of the concurrent program
139 ** to be submitted.
140 **
141 ** First look to see if a request for this CP is waiting to run. If so
142 ** then don't submit another one.
143 */
144 l_return := fnd_concurrent.get_request_status(
145 request_id => l_request_id,
146 appl_shortname => p_app_shortname,
147 program => p_update_name,
148 phase => l_phase,
149 status => l_status,
150 dev_phase => l_dev_phase,
151 dev_status => l_dev_status,
152 message => l_message);
153
154 /* The get_request_status returns FALSE if no request is found for the
155 ** specified program. Therefore submit a request.
156 ** The get_request_status returns TRUE if a request is found. The
157 ** dev_status holds the execution status of the request. Submit a new
158 ** request if there is no request for the CP waiting to run(dev_status of
159 ** PENDING.
160 **/
161
162
163 if l_return = FALSE or
164 (l_return = TRUE and (l_dev_phase <> 'PENDING' and
165 l_dev_phase <> 'RUNNING' )) then
166
167
168 /*
169 ** We need to submit the request and pass out the request ID for
170 ** reference.
171 */
172 l_request_id := fnd_request.submit_request(
173 application => p_app_shortname,
174 program => p_update_name,
175 argument1 => p_argument1,
176 argument2 => p_argument2,
177 argument3 => p_argument3,
178 argument4 => p_argument4,
179 argument5 => p_argument5,
180 argument6 => p_argument6,
181 argument7 => p_argument7,
182 argument8 => p_argument8,
183 argument9 => p_argument9,
184 argument10 => p_argument10);
185
186 p_request_id := l_request_id;
187
188 else
189
190 /*
191 ** Program either running or waiting to run so return request ID.
192 */
193 p_request_id := l_request_id;
194
195 end if;
196
197 else
198
199 /* The update is defined as a PYUGEN based update. Submit PYUGEN
200 ** passing the name of the update.
201 **
202 ** Check to see if a duplicate process is already running...
203 */
204 open csr_get_conc_req_status;
205 fetch csr_get_conc_req_status
206 into l_dev_phase, l_dev_status, l_request_id;
207
208 if csr_get_conc_req_status%NOTFOUND or
209 (csr_get_conc_req_status%FOUND and (l_dev_phase <> 'P' and
210 l_dev_phase <> 'R')) then
211
212 close csr_get_conc_req_status;
213
214 l_request_id := fnd_request.submit_request (
215 application => 'PER',
216 program => 'PAY_GEN_UPG',
217 argument1 => 'ARCHIVE', -- Process Name
218 argument2 => 'GENERIC_UPGRADE', -- Report Type
219 argument3 => 'DEFAULT', -- Rpt Qual
220 argument4 => fnd_date.date_to_canonical(sysdate), -- Start Date
221 argument5 => fnd_date.date_to_canonical(sysdate), -- End Date
222 argument6 => 'PROCESS', -- Rpt Category
223 argument7 => to_Char(p_business_group_id), -- Business Grp
224 argument8 => '', -- Mag File Nme
225 argument9 => '', -- Rep File Nme
226 argument10 => to_char(l_upg_def_id), -- ID
227 argument11 => p_update_name, -- Short Name
228 argument12 => 'UPG_DEF_NAME='||p_update_name -- Upgrade Name
229 );
230
231 p_request_id := l_request_id;
232
233 else
234
235 close csr_get_conc_req_status;
236
237 /*
238 ** Program either running or waiting to run so return request ID.
239 */
240 p_request_id := l_request_id;
241
242 end if;
243 end if; /* l_upg_mthd */
244
245 else
246
247 /* The update is not required for this customer. Set the status to
248 ** indicate this. This is acheived by first setting the status to
249 ** processing and then to complete due to validation within the GUP
250 ** infrastructure code.
251 ** Only do this if the process is not already at a Complete status.
252 */
253 if isUpdateComplete(p_app_shortname,
254 NULL,
255 p_business_group_id,
256 p_update_name) = 'FALSE' then
257
258 setUpdateProcessing(p_update_name,
259 p_business_group_id,
260 p_legislation_code);
261 setUpdateComplete(p_update_name,
262 p_business_group_id,
263 p_legislation_code);
264
265 end if;
266
267 end if;
268
269 end submitRequest;
270
271
272 -- ----------------------------------------------------------------------------
273 -- |--------------------------< isUpdateComplete >---------------------------|
274 -- ----------------------------------------------------------------------------
275 --
276 function isUpdateComplete
277 (p_app_shortname varchar2
278 ,p_function_name varchar2
279 ,p_business_group_id number
280 ,p_update_name varchar2) return varchar2 is
281
282 l_status varchar2(20);
283
284 begin
285
286 pay_core_utils.get_upgrade_status(
287 p_bus_grp_id => p_business_group_id,
288 p_short_name => p_update_name,
289 p_status => l_status,
290 p_raise_error => FALSE
291 );
292
293 if l_status = 'Y' then
294 /* The upgrade is complete if the last procedure returned a status of 'Y'
295 */
296 return 'TRUE';
297 else
298 return 'FALSE';
299 end if;
300
301 /*
302 exception
303 when no_data_found then
304 return 'FALSE';
305 */
306 end isUpdateComplete;
307
308 -- ----------------------------------------------------------------------------
309 -- |--------------------------< setUpdateProcessing >------------------------|
310 -- ----------------------------------------------------------------------------
311 --
312 procedure setUpdateProcessing
313 (p_update_name varchar2,
314 p_business_group_id number default null,
315 p_legislation_code varchar2 default null) is
316
317 cursor csr_get_update_details is
318 select upgrade_definition_id,
319 upgrade_level
320 from pay_upgrade_definitions
321 where short_name = p_update_name;
322
323 l_upg_def_id pay_upgrade_definitions.upgrade_definition_id%type;
324 l_upg_lvl pay_upgrade_definitions.upgrade_level%type;
325
326 begin
327
328
329 /* Get some details of the update being submitted to determine
330 ** the correct submission mechanism.
331 */
332 open csr_get_update_details;
333 fetch csr_get_update_details into l_upg_def_id, l_upg_lvl;
334 close csr_get_update_details;
335
336 pay_generic_upgrade.set_upgrade_status(
337 p_upg_def_id => l_upg_def_id,
338 p_upg_lvl => l_upg_lvl,
339 p_bus_grp => p_business_group_id,
340 p_leg_code => p_legislation_code,
341 p_status => 'P');
342
343 end setUpdateProcessing;
344
345 -- ----------------------------------------------------------------------------
346 -- |--------------------------< setUpdateComplete >--------------------------|
347 -- ----------------------------------------------------------------------------
348 --
349 procedure setUpdateComplete
350 (p_update_name varchar2,
351 p_business_group_id number default null,
352 p_legislation_code varchar2 default null) is
353
354 cursor csr_get_update_details is
355 select upgrade_definition_id,
356 upgrade_level
357 from pay_upgrade_definitions
358 where short_name = p_update_name;
359
360 l_upg_def_id pay_upgrade_definitions.upgrade_definition_id%type;
361 l_upg_lvl pay_upgrade_definitions.upgrade_level%type;
362
363 begin
364
365
366 /* Get some details of the update being submitted to determine
367 ** the correct submission mechanism.
368 */
369 open csr_get_update_details;
370 fetch csr_get_update_details into l_upg_def_id, l_upg_lvl;
371 close csr_get_update_details;
372
373 pay_generic_upgrade.set_upgrade_status(
374 p_upg_def_id => l_upg_def_id,
375 p_upg_lvl => l_upg_lvl,
376 p_bus_grp => p_business_group_id,
377 p_leg_code => p_legislation_code,
378 p_status => 'C');
379
380 end setUpdateComplete;
381
382 end hr_update_utility;
[Home] [Help]