1 PACKAGE BODY hri_bpl_pyugen_wrapper AS
2 /* $Header: hribpgw.pkb 115.4 2004/05/18 07:04:47 vsethi noship $ */
3 --
4 -- -------------------------------------------------------------------------
5 -- PACKAGE OVERVIEW
6 -- -------------------------------------------------------------------------
7 --
8 -- Purpose
9 -- -------
10 -- This package was created to detect if shared HR is installed for
11 -- processes that require PYUGEN. If shared HR is detected, then either
12 -- execute a shared HR process or put an appropriate unsupported
13 -- functionality message in the log and end.
14 -- Processes can be forced to run in Shared HR Mode by setting the value of
15 -- profile "HRI:DBI Force Foundation HR Processes" to Yes.
16 --
17 -- Requirements
18 -- ~~~~~~~~~~~~
19 -- A concurrent process (e.g. HRI_OPL_WMV_WRPR) that executes the executable
20 -- HRI_BPL_PYUGEN_WRAPPER passing in the parameters:
21 -- + p_collect_from_date
22 -- + p_collect_to_date
23 -- + p_full_refresh
24 -- + p_attribute1
25 -- + p_attribute2
26 -- It IS necessary to pass these parameters into this package from the
27 -- concurrent process that calls it. However they can be set to NULL,
28 -- and are not essential for this package to work
29 --
30 -- Foundation HR Processing
31 -- ~~~~~~~~~~~~~~~~~~~~~~~~
32 -- If shared HR is detected or if the profile value for "HRI:DBI Force
33 -- Foundation HR Processes" is set to Yes, the following steps will be followed:
34 --
35 -- 1. Try to find a concurrent that has the same name as p_collection_name
36 -- concatenated with '_SHRDHR' (p_collection_name||'_SHRDHR') (For
37 -- example HRI_MB_WMV_SHRDHR).
38 -- 2. If the concurrent process is not detected assume this is the intended
39 -- behaviour and end without error, put an appropriate message in the
40 -- log.
41 -- 3. If the concurrent process is detected then execute it passing in the
42 -- parameters passed into this process when it was called.
43 --
44 -- The called process should contain the following procedure to handle
45 -- default processing when shared HR is not present:
46 --
47 -- PROCEDURE shared_hrms_dflt_prcss
48 -- (
49 -- errbuf OUT NOCOPY VARCHAR2
50 -- ,retcode OUT NOCOPY NUMBER
51 -- ,p_collect_from_date IN VARCHAR2 DEFAULT NULL -- Optional Param
52 -- ,p_collect_to_date IN VARCHAR2 DEFAULT NULL -- Optional Param
53 -- ,p_full_refresh IN VARCHAR2 DEFAULT NULL -- Optional Param
54 -- ,p_attribute1 IN VARCHAR2 DEFAULT NULL -- Optional Param
55 -- ,p_attribute2 IN VARCHAR2 DEFAULT NULL -- Optional Param
56 -- );
57 --
58 -- 4. Submit the concurrent process p_collection_name||'_SHRDHR', and wait
59 -- for it to complete.
60 --
61 -- Full HR Processing
62 -- ~~~~~~~~~~~~~~~~~~
63 -- If full HR is detected, the following steps will be followed:
64 -- 3/ Execute the pre-seeded concurrent process 'HRI_PYUGEN_WRAPPER',
65 -- passing in the parameters:
66 -- + p_collect_from_date
67 -- + p_collect_to_date
68 -- + p_full_refresh
69 -- + p_attribute1
70 -- + p_attribute2
71 -- It is necessary to pass these parameters into this package from the
72 -- concurrent process that calls it. However they can be set to NULL,
73 -- and are not essential for this package to work. They only need to be
74 -- set to something useful if the PYUGEN process that we are
75 -- going to call needs them.
76 --
77 -- -------------------------------------------------------------------------
78 --
79 -- GLOBAL CONSTANTS
80 --
81 c_OUTPUT_LINE_LENGTH CONSTANT NUMBER := 255;
82 --
83 -- Global constants used by PEM (Payroll Events Model)
84 --
85 c_application CONSTANT VARCHAR2(30) := 'HRI';
86 c_report_type CONSTANT VARCHAR2(30) := 'HISTORIC_SUMMARY';
87 c_process_name CONSTANT VARCHAR2(30) := 'ARCHIVE';
88 c_report_category CONSTANT VARCHAR2(30) := 'PROCESS';
89 c_magnetic_file_name CONSTANT VARCHAR2(30) := TO_CHAR(NULL);
90 c_report_file_name CONSTANT VARCHAR2(30) := TO_CHAR(NULL);
91 c_cncrrnt_prcss_name CONSTANT VARCHAR2(30) := 'HRI_PYUGEN_WRAPPER';
92 --
93 -- -------------------------------------------------------------------------
94 -- PRIVATE GLOBALS
95 --
96 -- Debug and logging globals
97 --
98 g_debugging BOOLEAN := FALSE;
99 g_concurrent_logging BOOLEAN := FALSE;
100 --
101 -- ----------------------------------------------------------------------------
102 -- set_debugging
103 -- Switches debugging messages on or off.
104 -- ============================================================================
105 -- Setting to on will mean extra debugging information will be generated when
106 -- the process is run.
107 --
108 PROCEDURE set_debugging(p_on IN BOOLEAN) IS
109 BEGIN
110 --
111 g_debugging := p_on;
112 --
113 END set_debugging;
114 --
115 -- ----------------------------------------------------------------------------
116 -- set_concurrent_logging
117 -- Turns concurrent logging on or off
118 -- ============================================================================
119 -- This procedure sets the global g_concurrent_logging to
120 -- the value passed in. If set log messages will be output
121 -- through fnd_file.put_line.
122 --
123 PROCEDURE set_concurrent_logging(p_on IN BOOLEAN) IS
124 BEGIN
125 --
126 g_concurrent_logging := p_on;
127 --
128 END set_concurrent_logging;
129 --
130 -- ----------------------------------------------------------------------------
131 -- msg
132 -- logs a message, either using fnd_file, or hr_utility.trace
133 -- ============================================================================
134 --
135 PROCEDURE msg(p_text IN VARCHAR2)
136 IS
137 l_pos NUMBER := 1;
138 l_txt VARCHAR2(255);
139 BEGIN
140 --
141 IF g_concurrent_logging
142 THEN
143 --
144 -- Chop up p_text string into 250 char chunks as we are
145 -- writing to the concurrent manager log file.
146 --
147 LOOP
148 --
149 l_txt := SUBSTR(p_text,l_pos,c_OUTPUT_LINE_LENGTH);
150 --
151 fnd_file.put_line(fnd_file.LOG,l_txt);
152 --
153 l_pos := l_pos + c_OUTPUT_LINE_LENGTH;
154 --
155 EXIT WHEN l_pos > LENGTH(p_text);
156 --
157 END LOOP;
158 --
159 ELSE
160 --
161 -- Use HR trace
162 --
163 hr_utility.trace(p_text);
164 --
165 END IF;
166 --
167 END msg;
168 --
169 -- ----------------------------------------------------------------------------
170 -- dbg
171 -- Decides whether to log the passed in message
172 -- ============================================================================
173 -- Depending on whether debug mode is set, decides whether to log the passed
174 -- in message
175 --
176 PROCEDURE dbg(p_text IN VARCHAR2)
177 IS
178 --
179 BEGIN
180 --
181 -- dbms_output.put_line(p_text);
182 -- dbms_output.put_line(sqlerrm);
183 IF g_debugging THEN
184 --
185 msg(p_text);
186 --
187 END IF;
188 --
189 END dbg;
190 --
191 -- ----------------------------------------------------------------------------
192 -- check_cncrrnt_prcss_exists
193 -- Checks whether a given concurrent process exists.
194 -- ============================================================================
195 --
196 FUNCTION check_cncrrnt_prcss_exists(p_process_name IN VARCHAR2)
197 RETURN BOOLEAN IS
198 --
199 CURSOR c_cncrrnt_prcss(p_process_name IN VARCHAR2) IS
200 SELECT 'x'
201 FROM Fnd_Concurrent_Programs FCP
202 WHERE Concurrent_Program_Name = Upper (p_process_name)
203 AND application_id = 453; -- HRI
204 --
205 l_result VARCHAR2(240);
206 --
207 BEGIN
208 --
209 dbg('Checking concurrent process '||p_process_name||' exists.');
210 --
211 -- Check the concurrent process exists
212 --
213 OPEN c_cncrrnt_prcss(p_process_name);
214 FETCH c_cncrrnt_prcss INTO l_result;
215 IF c_cncrrnt_prcss%NOTFOUND
216 THEN
217 --
218 dbg('Concurrent Process "'||p_process_name||'" not found.');
219 --
220 CLOSE c_cncrrnt_prcss;
221 --
222 -- Return FALSE to indicate that the concurrent process does not exist.
223 --
224 RETURN FALSE;
225 --
226 ELSE
227 --
228 dbg('Concurrent Process "'||p_process_name||'" found.');
229 --
230 CLOSE c_cncrrnt_prcss;
231 --
232 -- Return TRUE to indicate that the concurrent process exists.
233 --
234 RETURN TRUE;
235 --
236 END IF;
237 --
238 EXCEPTION
239 --
240 WHEN OTHERS
241 THEN
242 --
243 dbg('Exception raised in check_cncrrnt_prcss_exists ..');
244 RAISE;
245 --
246 END check_cncrrnt_prcss_exists;
247 --
248 -- ----------------------------------------------------------------------------
249 -- process_request
250 -- This is the main process in this package.
251 -- ============================================================================
252 -- This process will either call a PYUGEN concurrent process, a default
253 -- single threaded shared HR concurrent process (for the process we are
254 -- trying to execute, or if no process exists exit cleanly putting a suitable
255 -- message in the log.
256 --
257 PROCEDURE process_request
258 (--
259 -- p_collection_name:
260 -- The meta data name of the the PYUGEN process to be executed.
261 --
262 p_collection_name IN VARCHAR2
263 --
264 -- p_business_group_id:
265 -- The value for this parameter will be set by the concurrent manager
266 -- using default type 'Profile', and default value 'PER_BUSINESS_GROUP_ID'
267 --
268 ,p_business_group_id IN VARCHAR2
269 --
270 -- p_collect_from_date:
271 -- The date to start collection from. This is not mandatory, and can be
272 -- defaulted to NULL.
273 --
274 ,p_collect_from_date IN VARCHAR2 DEFAULT TO_CHAR(NULL)
275 --
276 -- p_collect_to_date:
277 -- The date to run collection to. This is not mandatory, and can be
278 -- defaulted to NULL.
279 --
280 ,p_collect_to_date IN VARCHAR2 DEFAULT TO_CHAR(NULL)
281 --
282 -- p_full_refresh:
283 -- Whether to run full refresh or not. This is not mandatory, and can be
284 -- defaulted to NULL.
285 --
286 ,p_full_refresh IN VARCHAR2 DEFAULT TO_CHAR(NULL)
287 --
288 -- p_attribute1:
289 -- Spare attribute field 1. This is not mandatory, and can be
290 -- defaulted to NULL.
291 --
292 ,p_attribute1 IN VARCHAR2 DEFAULT TO_CHAR(NULL)
293 --
294 -- p_attribute2:
295 -- Spare attribute field 1. This is not mandatory, and can be
296 -- defaulted to NULL.
297 --
298 ,p_attribute2 IN VARCHAR2 DEFAULT TO_CHAR(NULL)
299 )
300 IS
301 --
302 -- Used to store the return value of fnd_conc_global.request_data. If
303 -- it is non null then this indicates that the process has returned
304 -- from a paused state.
305 --
306 l_request_data VARCHAR2(240);
307 --
308 -- Store the request id of the sub process launched to run PYUGEN.
309 --
310 l_request_id NUMBER;
311 --
312 -- Used later to generate the concurrent process name that we attempt to
313 -- launch.
314 --
315 l_cncrrnt_prcss_name VARCHAR2(30);
316 --
317 -- Local variables used to store details of successfully completed
318 -- sub processes.
319 --
323 l_dev_status VARCHAR2(240); -- Set to NORMAL if the sub process ended
320 l_phase VARCHAR2(240); -- Dummy output variable that is ignored.
321 l_status VARCHAR2(240); -- Dummy output variable that is ignored.
322 l_dev_phase VARCHAR2(240); -- Dummy output variable that is ignored.
324 -- successfully.
325 l_message VARCHAR2(240); -- Dummy output variable that is ignored.
326 l_success BOOLEAN;
327 --
328 l_hr_installed VARCHAR2(30); -- Stores HR installed or not
329 l_frc_shrd_hr_prfl_val VARCHAR2(30); -- Variable to store value for
330 -- Profile HRI:DBI Force Foundation HR Processes
331 --
332 BEGIN
333 --
334 -- Call fnd_conc_global.request_data, to see if this program is re-entering
335 -- after being paused, while the PYUGEN master process completes.
336 --
337 l_request_data := fnd_conc_global.request_data;
338 --
339 -- NOTE!!! THE FOLLOWING CODE WITHIN THE CONDITION:
340 -- 'IF l_request_data IS NOT NULL', is only run after re-entering the
341 -- package when sub processes have completed.
342 -- See the section titled 'INITIAL FLOW THROUGH', for the code that
343 -- is run on intial execution.
344 --
345 -- If the process is re-entering at the end of being paused awaiting PYUGEN
346 -- completion, then we have nothing left to do other than end.
347 --
348 IF l_request_data IS NOT NULL
349 THEN
350 --
351 msg('Re-starting '||p_collection_name||'.');
352 --
353 -- Get the request_id of the sub process previously executed so that we
354 -- can check it's status.
355 --
356 l_request_id := TO_NUMBER(l_request_data);
357 --
358 -- Check whether the sub process finished successfully.
359 --
360 l_success := fnd_concurrent.get_request_status
361 (
362 request_id => l_request_id
363 ,appl_shortname => NULL
364 ,program => NULL
365 ,phase => l_phase
366 ,status => l_status
367 ,dev_phase => l_dev_phase
368 ,dev_status => l_dev_status
369 ,message => l_message
370 );
371 --
372 -- Set Varchar2 equivalent (l_success_chr) of l_success
373 --
374 IF l_success
375 THEN
376 --
377 -- Debug info
378 --
379 dbg('Sub process finished with status '||l_dev_status||'.');
380 --
381 -- If l_dev_status 'NORMAL', then that means the sub process was
382 -- successful.
383 --
384 IF l_dev_status <> 'NORMAL'
385 THEN
386 --
387 -- The sub process failed so raise an exception
388 --
389 msg('The sub process failed. Raising an exception.');
390 --
391 RAISE sub_process_failed;
392 --
393 ELSE
394 --
395 -- The sub process completed successfully so end.
396 --
397 msg('All processes have ended successfully.');
398 --
399 RETURN;
400 --
401 END IF;
402 --
403 ELSE
404 --
405 -- Details of the sub process can not be found for some reason, so
406 -- raise an exception.
407 --
408 msg('An un-expected error has occurred');
409 --
410 RAISE sub_process_not_found;
411 --
412 END IF;
413 --
414 END IF; -- End process re-entered logic.
415 --
416 -- INITIAL FLOW THROUGH
417 -- --------------------
418 -- This is the initial flow through this process for this executaion.
419 -- If it had not been l_request_data would not have been NULL, and we would
420 -- have exited.
421 --
422 --
423 -- If we are in shared HR or if profile HRI:DBI Force Foundation HR Processes has been set
424 -- then call the shared HR process (if it exists) or put a suitable message in
425 -- the log and end with success.
426 --
427 --
428 msg('Starting '||p_collection_name||'.');
429 --
430 l_frc_shrd_hr_prfl_val := NVL(fnd_profile.value('HRI_DBI_FORCE_SHARED_HR'),'N');
431 l_hr_installed := hr_general.chk_product_installed(800);
432 --
433 IF l_hr_installed = 'FALSE'
434 OR l_frc_shrd_hr_prfl_val = 'Y'
435 THEN
436 --
437 -- Insert the appropriate message in the log file
438 --
439 IF l_hr_installed = 'FALSE' THEN
440 --
441 msg('Foundation HR detected ...');
442 --
443 ELSIF l_frc_shrd_hr_prfl_val = 'Y' THEN
444 --
445 msg('Profile HRI:DBI Force Foundation HR Processes has been set. Forcing the Foundation HR version of the process');
446 --
447 END IF;
448 --
449 -- Generate concurrent process name to use for shared HR.
450 --
451 l_cncrrnt_prcss_name := p_collection_name||'_SHRDHR';
452 --
453 -- Check if the concurrent process we think we need to run exists
454 --
455 IF check_cncrrnt_prcss_exists(l_cncrrnt_prcss_name) = FALSE
456 THEN
457 --
458 -- Output a message saying that this process is not supported in
459 -- shared HR, and end.
460 -- --
461 msg ('The process '||l_cncrrnt_prcss_name||
462 ' is not supported in HR Foundation.');
463 --
464 RETURN;
465 --
466 ELSE
467 --
468 -- execute simplified collection process for shared HRMS based on the
469 -- the PYUGEN process being executed.
470 --
471 msg('Requesting single threaded foundation HR collection process.');
472 --
473 l_request_id :=
474 fnd_request.submit_request
475 (
476 application => c_application
477 ,program => l_cncrrnt_prcss_name -- Name of concurrent process
478 ,sub_request => TRUE -- Indicates that the request should be
479 -- executed as a sub process.
480 ,argument1 => p_collect_from_date -- Optional Parameter defaulted
481 -- to NULL
482 ,argument2 => p_collect_to_date -- Optional Parameter defaulted
483 -- to NULL
484 ,argument3 => p_full_refresh -- Optional Parameter defaulted
485 -- to NULL
486 ,argument4 => p_attribute1 -- Optional Parameter defaulted to NULL
487 ,argument5 => p_attribute2 -- Optional Parameter defaulted to NULL
488 );
489 --
490 dbg('Request Submitted for single threaded foundation HR collection '||
491 'process.');
492 --
493 dbg('Telling concurrent manage to wait for sub processes to complete.');
494 --
495 -- Tell the process to pause awaiting sub process completion.
496 --
497 fnd_conc_global.set_req_globals(conc_status => 'PAUSED',
498 request_data=> TO_CHAR(l_request_id));
499 --
500 dbg('Waiting for sub processes to complete.');
501 --
502 RETURN;
503 --
504 END IF;
505 --
506 -- IF the following ELSE condition is entered then we have a full HRMS
507 -- installation, so we can call the standard refresh process that uses
508 -- PYUGEN.
509 --
510 ELSE
511 --
512 dbg('Full HR Installation detected ...');
513 --
514 msg('Requesting multi threaded full HR collection process.');
515 --
516 -- Call PYUGEN passing through the parameters that process_request was
517 -- originally called with.
518 --
519 l_request_id :=
520 fnd_request.submit_request
521 (
522 application => c_application
523 ,program => c_cncrrnt_prcss_name -- Name of concurrent process
524 ,sub_request => TRUE -- Indicates that the request should be
525 -- executed as a sub process.
526 ,argument1 => c_process_name -- The type of PYUGEN process. Fixed
527 -- to 'ARCHIVE'.
528 ,argument2 => c_report_type -- PYUGEN Report type. Fixed to 'C'.
529 ,argument3 => p_collection_name -- The seeded name of the PYUGEN
530 -- process we have created.
531 ,argument4 => p_collect_from_date -- PYUGEN Fixed parameter
532 ,argument5 => p_collect_to_date -- PYUGEN Fixed parameter
533 ,argument6 => c_report_category -- PYUGEN Fixed parameter
534 -- 'PROCESS'
535 ,argument7 => p_business_group_id -- PYUGEN parameter, based on
536 -- profile option
537 -- PER_BUSINESS_GROUP_ID
538 ,argument8 => c_magnetic_file_name -- PYUGEN Fixed parameter
539 -- (IGNORED)
540 ,argument9 => c_report_file_name -- PYUGEN Fixed parameter
541 -- (IGNORED)
542 ,argument10 => p_full_refresh -- Optional Parameter defaulted
543 -- to NULL
544 ,argument11 => p_attribute1 -- Optional Parameter defaulted
545 -- to NULL
546 ,argument12 => p_attribute2 -- Optional Parameter defaulted
547 -- to NULL
548 );
549 --
550 dbg('Request Submitted for multi threaded full HR collection '||
551 'process.');
552 --
553 dbg('Telling concurrent manager to wait for sub processes to complete.');
554 --
555 --
556 -- Tell the process to pause awaiting sub process completion.
557 --
558 fnd_conc_global.set_req_globals(conc_status => 'PAUSED',
559 request_data=> TO_CHAR(l_request_id));
560 --
561 RETURN;
562 --
563 END IF; -- hr_general.chk_product_installed(800) = 'FALSE'
564 --
565 EXCEPTION
566 --
567 WHEN OTHERS
568 THEN
569 --
570 msg('Exception found in HRI PYUGEN Wrapper process ..');
571 dbg(SQLERRM);
572 --
573 RAISE;
574 --
575 END process_request;
576 --
577 -- ----------------------------------------------------------------------------
578 -- process_request
579 -- ============================================================================
580 -- Overloaded version of process_request to be called from the concurrent
581 -- manager.
582 --
583 PROCEDURE process_request
584 (errbuf OUT NOCOPY VARCHAR2
585 ,retcode OUT NOCOPY NUMBER
586 ,p_collection_name IN VARCHAR2
587 ,p_business_group_id IN VARCHAR2
588 ,p_collect_from_date IN VARCHAR2 DEFAULT TO_CHAR(NULL)
589 ,p_collect_to_date IN VARCHAR2 DEFAULT TO_CHAR(NULL)
590 ,p_full_refresh IN VARCHAR2 DEFAULT TO_CHAR(NULL)
591 ,p_attribute1 IN VARCHAR2 DEFAULT TO_CHAR(NULL)
592 ,p_attribute2 IN VARCHAR2 DEFAULT TO_CHAR(NULL)
593 )
594 IS
595 --
596 BEGIN
597 --
598 -- Set concurrent logging on.
599 --
600 set_concurrent_logging(TRUE);
601 --
602 process_request
603 (p_collection_name => p_collection_name
604 ,p_business_group_id => p_business_group_id
605 ,p_collect_from_date => p_collect_from_date
609 ,p_attribute2 => p_attribute2
606 ,p_collect_to_date => p_collect_to_date
607 ,p_full_refresh => p_full_refresh
608 ,p_attribute1 => p_attribute1
610 );
611 --
612 EXCEPTION
613 WHEN OTHERS
614 THEN
615 --
616 errbuf := SQLERRM;
617 retcode := SQLCODE;
618 RAISE;
619 --
620 --
621 END process_request;
622 --
623 END hri_bpl_pyugen_wrapper;
[Home] [Help]