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