[Home] [Help]
PACKAGE BODY: APPS.HRI_OPL_SPRTN_RSNS
Source
1 PACKAGE BODY hri_opl_sprtn_rsns AS
2 /* $Header: hriosprn.pkb 120.1 2005/07/28 02:20:53 anmajumd noship $ */
3 --
4 -- Bug 4105868: Collection Diagnostics
5 --
6 g_msg_sub_group VARCHAR2(400) := '';
7 --
8 -- -------------------------------------------------------------------------
9 -- Inserts row into concurrent program log when the g_conc_request_id has
10 -- been set to TRUE, otherwise does nothing
11 -- -------------------------------------------------------------------------
12 --
13 PROCEDURE output(p_text VARCHAR2) IS
14 --
15 BEGIN
16 --
17 -- Bug 4105868: Collection Diagnostics
18 --
19 HRI_BPL_CONC_LOG.output(p_text);
20 --
21 END output;
22 --
23 -- -------------------------------------------------------------------------
24 -- Inserts row into concurrent program log if debugging is enabled
25 -- -------------------------------------------------------------------------
26 --
27 PROCEDURE dbg(p_text VARCHAR2) IS
28 --
29 BEGIN
30 --
31 -- Bug 4105868: Collection Diagnostics
32 --
33 HRI_BPL_CONC_LOG.dbg(p_text);
34 --
35 END dbg;
36 --
37 -- -------------------------------------------------------------------------
38 -- Load Separations Reason Table
39 -- -------------------------------------------------------------------------
40 --
41 PROCEDURE load_sprtn_rsns_tab
42 IS
43 --
44 l_formula_id NUMBER; -- Fast Formula Id of HR_MOVE_TYPE
45 l_term_type VARCHAR2(30); -- Holds I(nvoluntary) or V(oluntary)
46 --
47 -- Cursor selects all separation reasons, and outer joins to the
48 -- involuntary/voluntary categorization table to determine whether
49 -- an insert or an update is needed
50 -- 2448895 - Added the NA_EDW row to ensure that a separation
51 -- category is obtained for the NULL separation reason from the
52 -- fast formula
53 --
54 CURSOR leaving_reasons_csr IS
55 SELECT hrl.lookup_code lookup_code,
56 DECODE(spr.reason, null, 'N', 'Y') exists_flag
57 FROM hr_standard_lookups hrl,
58 hri_inv_sprtn_rsns spr
59 WHERE hrl.lookup_code = spr.reason (+)
60 AND hrl.lookup_type = 'LEAV_REAS'
61 UNION ALL
62 SELECT 'NA_EDW' lookup_code,
63 'N' exists_flag
64 FROM dual
65 WHERE NOT EXISTS
66 (SELECT -1
67 FROM hri_inv_sprtn_rsns spr
68 WHERE spr.reason = 'NA_EDW')
69 UNION ALL
70 SELECT 'NA_EDW' lookup_code,
71 'Y' exists_flag
72 FROM dual
73 WHERE EXISTS
74 (SELECT -1
75 FROM hri_inv_sprtn_rsns spr
76 WHERE spr.reason = 'NA_EDW');
77 --
78 BEGIN
79 --
80 dbg('inside load_sprtn_rsns_tab');
81 --
82 -- Get formula id of fast formula to use
83 --
84 l_formula_id := hr_person_flex_logic.GetTermTypeFormula
85 ( p_business_group_id => 0 );
86 --
87 dbg('insert/update records in hri_inv_sprtn_rsns');
88 --
89 -- Loop through all the leaving reasons defined
90 --
91 FOR v_leaving_reason IN leaving_reasons_csr LOOP
92 --
93 -- Run fast formula for current leaving reason
94 --
95 l_term_type := HR_PERSON_FLEX_LOGIC.GetTermType
96 ( p_term_formula_id => l_formula_id,
97 p_leaving_reason => v_leaving_reason.lookup_code,
98 p_session_date => SYSDATE );
99 --
100 dbg('code = '||v_leaving_reason.lookup_code||' , term_type = '||l_term_type);
101 --
102 -- If leaving reason not already in the categorization table
103 --
104 IF (v_leaving_reason.exists_flag = 'N') THEN
105 --
106 -- Insert the details into the table
107 --
108 INSERT
109 INTO hri_inv_sprtn_rsns
110 (reason
111 ,termination_type
112 ,update_allowed_flag
113 )
114 VALUES (v_leaving_reason.lookup_code
115 ,l_term_type
116 ,'N'
117 );
118 --
119 ELSE
120 --
121 -- Update the reason
122 --
123 UPDATE hri_inv_sprtn_rsns
124 SET termination_type = l_term_type
125 WHERE reason = v_leaving_reason.lookup_code;
126 --
127 END IF;
128 --
129 END LOOP;
130 --
131 COMMIT;
132 --
133 dbg('exiting load_sprtn_rsns_tab');
134 --
135 -- Bug 4105868: Collection Diagnostics
136 --
137 EXCEPTION
138 --
139 WHEN OTHERS THEN
140 --
141 g_msg_sub_group := NVL(g_msg_sub_group, 'LOAD_SPRTN_RSNS_TAB');
142 --
143 RAISE;
144 --
145 END load_sprtn_rsns_tab;
146 --
147 -- This procedure populates the separation reasons table in shared HRMS installations
148 --
149 PROCEDURE load_sprtn_rsns_shared_hrms
150
151 IS
152
153 l_sysdate Date;
154 l_user number;
155 l_sql_stmt VARCHAR2(2000);
156 l_dummy1 VARCHAR2(2000);
157 l_dummy2 VARCHAR2(2000);
158 l_schema VARCHAR2(2000);
159 --
160 BEGIN
161 --
162 dbg('inside load_sprtn_rsns_shared_hrms');
163 --
164 --
165 -- Get HRI schema name - get_app_info populates l_schema
166 --
167 IF fnd_installation.get_app_info('HRI',l_dummy1,l_dummy2,l_schema) THEN
168 --
169 null;
170 --
171 END IF;
172 --
173 -- Truncate the table
174 --
175 l_sql_stmt := 'TRUNCATE TABLE ' || l_schema || '.HRI_INV_SPRTN_RSNS';
176 --
177 EXECUTE IMMEDIATE(l_sql_stmt);
178 --
179 -- Assigning sysdate and user for who columns
180 --
181 l_sysdate := sysdate;
182 l_user := fnd_global.user_id;
183 --
184 -- Insert into the table
185 --
186 INSERT /*+ APPEND */ INTO hri_inv_sprtn_rsns
187 ( reason
188 , termination_type
189 , update_allowed_flag
190 , last_update_date
191 , last_updated_by
192 , last_update_login
193 , created_by
194 , creation_date
195 )
196 SELECT
197 lookup_code
198 , 'V'
199 , 'N'
200 , l_sysdate
201 , l_user
202 , l_user
203 , l_user
204 , l_sysdate
205 FROM hr_standard_lookups
206 WHERE lookup_type = 'LEAV_REAS'
207 UNION ALL
208 SELECT
209 'NA_EDW' lookup_code
210 , 'V'
211 , 'N'
212 , l_sysdate
213 , l_user
214 , l_user
215 , l_user
216 , l_sysdate
217 FROM
218 dual;
219 --
220 COMMIT;
221 --
222 dbg('exiting load_sprtn_rsns_shared_hrms');
223 --
224 END load_sprtn_rsns_shared_hrms;
225 --
226 --
227 -- ----------------------------------------------------------------------------
228 -- Entry point to be called from the concurrent manager
229 -- ----------------------------------------------------------------------------
230 --
231 PROCEDURE populate_sprtn_rsns(Errbuf in out nocopy Varchar2,
232 Retcode in out nocopy Varchar2)
233 IS
234 --
235 -- Bug 4105868: Collection Diagnostics
236 --
237 l_message fnd_new_messages.message_text%type;
238 --
239 --
240 l_foundation_hr_flag VARCHAR2(1);
241 --
242 BEGIN
243 --
244 dbg('inside populate_sprtn_rsns');
245 --
246 --
247 -- If full HR is not installed, then set the flag to 'Y'
248 --
249 IF hr_general.chk_product_installed(800) = 'FALSE' THEN
250 --
251 l_foundation_hr_flag := 'Y';
252 --
253 --
254 -- If the profile HRI_DBI_FORCE_SHARED_HR has been set then
255 -- set the flag to 'Y'
256 --
257 ELSIF NVL(fnd_profile.value('HRI_DBI_FORCE_SHARED_HR'),'N') ='Y' THEN
258 --
259 l_foundation_hr_flag := 'Y';
260 --
261 ELSE
262 --
263 l_foundation_hr_flag := 'N';
264 --
265 END IF;
266 --
267 -- Bug 4105868: Collection Diagnostics
268 --
269 hri_bpl_conc_log.record_process_start('HRI_INV_SPRTN_RSNS');
270 --
271 -- If the flag for foundation has been set then run the load in shared mode,
272 -- else run in normal mode
273 --
274 IF l_foundation_hr_flag = 'Y' THEN
275 --
276 load_sprtn_rsns_shared_hrms;
277 --
278 ELSE
279 --
280 load_sprtn_rsns_tab;
281 --
282 END IF;
283 --
284 -- Bug 4105868: Collection Diagnostics
285 --
286 hri_bpl_conc_log.log_process_end(
287 p_status => TRUE,
288 p_period_from => hr_general.start_of_time,
289 p_period_to => hr_general.end_of_time);
290 --
291 dbg('exiting populate_sprtn_rsns');
292 --
293 EXCEPTION
294 WHEN OTHERS THEN
295 --
296 l_message := nvl(fnd_message.get,SQLERRM);
297 --
298 output(l_message);
299 --
300 errbuf := sqlerrm;
301 retcode := sqlcode;
302 --
303 -- Bug 4105868: Collection Diagnostics
304 --
305 g_msg_sub_group := NVL(g_msg_sub_group, 'POPULATE_SPRTN_RSNS');
306 --
307 hri_bpl_conc_log.log_process_info
308 (p_msg_type => 'ERROR'
309 ,p_note => l_message
310 ,p_package_name => 'HRI_OPL_SPRTN_RSNS'
311 ,p_msg_sub_group => g_msg_sub_group
312 ,p_sql_err_code => SQLCODE
313 ,p_msg_group => 'SEP_RSN');
314 --
315 hri_bpl_conc_log.log_process_end
316 (p_status => FALSE
317 ,p_period_from => hr_general.start_of_time
318 ,p_period_to => hr_general.end_of_time);
319 --
320 RAISE;
321 --
322 END populate_sprtn_rsns;
323 --
324 END hri_opl_sprtn_rsns;