1 PACKAGE BODY HRI_OPL_JOB_JOB_ROLE AS
2 /* $Header: hriojbrl.pkb 120.5 2006/10/11 15:29:59 jtitmas noship $ */
3 --
4 /******************************************************************************/
5 /* This package populates the job role table. Customers can specify the job */
6 /* role associated with a job through a fast formula with following details */
7 /* Name : HRI_MAP_JOB_JOB_ROLE */
8 /* Fast formula Type: Quickpaint */
9 /* Business group : Global fast formula defined in the setup business group */
10 /* Inputs : JOB_FAMILY_CODE, JOB_FUNCTION_CODE */
11 /* Output : JOB_ROLE_CODE */
12 /******************************************************************************/
13 --
14 --
15 -- HRI schema name
16 --
17 g_hri_schema VARCHAR2(240);
18 --
19 -- -------------------------------------------------------------------------
20 -- Inserts row into concurrent program log when the g_conc_request_flag has
21 -- been set to TRUE, otherwise does nothing
22 -- -------------------------------------------------------------------------
23 --
24 PROCEDURE output(p_text VARCHAR2) IS
25 --
26 BEGIN
27 --
28 -- Bug 4105868: Global to store msg_sub_group
29 --
30 HRI_BPL_CONC_LOG.output(p_text);
31 --
32 END output;
33 --
34 -- -------------------------------------------------------------------------
35 -- Inserts row into concurrent program log if debugging is enabled
36 -- -------------------------------------------------------------------------
37 --
38 PROCEDURE dbg(p_text VARCHAR2) IS
39 --
40 BEGIN
41 --
42 -- Bug 4105868: Collection Diagnostics
43 --
44 HRI_BPL_CONC_LOG.dbg(p_text);
45 --
46 END dbg;
47 --
48 --
49 -- -----------------------------------------------------------------------------
50 -- Collects job and the role associated with the job
51 -- -----------------------------------------------------------------------------
52 --
53 PROCEDURE collect_job_job_roles IS
54 --
55 l_current_time DATE := SYSDATE;
56 l_user_id NUMBER := fnd_global.user_id;
57 l_dummy1 VARCHAR2(2000);
58 l_dummy2 VARCHAR2(2000);
59 --
60 BEGIN
61 --
62 output('Fully refreshing the Job Role table.');
63 --
64 -- Truncate the table
65 --
66 IF (fnd_installation.get_app_info('HRI',l_dummy1, l_dummy2, g_hri_schema)) THEN
67 --
68 EXECUTE IMMEDIATE 'TRUNCATE TABLE ' || g_hri_schema || '.HRI_CS_JOB_JOB_ROLE_CT';
69 --
70 END IF;
71 --
72 dbg('Inserting into hri_cs_job_job_role_ct.');
73 --
74 INSERT INTO HRI_CS_JOB_JOB_ROLE_CT
75 (job_id
76 ,job_role_code
77 ,primary_role_for_job_flag
78 ,created_by
79 ,creation_date
80 ,last_update_date
81 ,last_updated_by
82 ,last_update_login)
83 SELECT
84 job_id
85 --
86 -- Job Role is determined through a fast formula
87 --
88 ,job_role_code
89 --
90 -- Currently only primary job role is implemented
91 --
92 ,'Y'
93 --
94 -- WHO columns
95 --
96 ,l_user_id
97 ,l_current_time
98 ,l_current_time
99 ,l_user_id
100 ,l_user_id
101 FROM hri_cs_job_job_role_v;
102 --
103 dbg('Inserted '||sql%rowcount||' records.');
104 --
105 output('Finished Fully refreshing the Job Role table.');
106 --
107 EXCEPTION
108 --
109 WHEN OTHERS THEN
110 --
111 output('Error occurred in procedure collect_job_job_roles.');
112 --
113 RAISE;
114 --
115 --
116 END collect_job_job_roles;
117 --
118 -- -------------------------------------------------------------------------------
119 -- Incremental refresh
120 -- -------------------------------------------------------------------------------
121 --
122 PROCEDURE update_job_job_roles IS
123 --
124 l_current_time DATE := SYSDATE;
125 l_user_id NUMBER := fnd_global.user_id;
126 l_end_date DATE := SYSDATE;
127 --
128 -- PL/SQL table of updated job records
129 --
130 TYPE l_number_tab_type IS TABLE OF hri_cs_job_job_role_ct.job_id%TYPE;
131 l_upd_job_ids L_NUMBER_TAB_TYPE;
132 --
133 BEGIN
134 --
135 output('Incremetally refreshing the Job Role table.');
136 --
137 -- Insert the new job ids and the corresponding job roles
138 --
139 dbg('Incrementaly inserting into hri_cs_job_job_role_ct.');
140 --
141 INSERT INTO hri_cs_job_job_role_ct
142 (job_id
143 ,job_role_code
144 ,primary_role_for_job_flag
145 ,created_by
146 ,creation_date
147 ,last_update_date
148 ,last_updated_by
149 ,last_update_login)
150 SELECT
151 job_id
152 --
153 -- Job Role is determined through a fast formula
154 --
155 ,job_role_code
156 --
157 -- Currently only primary job role is implemented
158 --
159 ,'Y'
160 --
161 -- WHO columns
162 --
163 ,l_user_id
164 ,l_current_time
165 ,l_current_time
166 ,l_user_id
167 ,l_user_id
168 FROM hri_cs_job_job_role_v jbrlv
169 WHERE NOT EXISTS
170 (SELECT null
171 FROM hri_cs_job_job_role_ct jbrl
172 WHERE jbrlv.job_id = jbrl.job_id);
173 --
174 -- Delete the non - existant job ids
175 --
176 dbg('Inserted '||sql%rowcount||' records.');
177 --
178 dbg('Incrementaly deleting from hri_cs_job_job_role_ct.');
179 --
180 DELETE FROM hri_cs_job_job_role_ct jbrl
181 WHERE NOT EXISTS
182 (SELECT null
183 FROM hri_cs_job_job_role_v jbrlv
184 WHERE jbrlv.job_id = jbrl.job_id);
185 --
186 dbg('Deleted '||sql%rowcount||' records.');
187 --
188 -- Update the records for which there was a change in job family/job function
189 --
190 dbg('Incrementaly updating hri_cs_job_job_role_ct.');
191 --
192 UPDATE hri_cs_job_job_role_ct jbrl
193 SET (jbrl.job_role_code
194 ,primary_role_for_job_flag) =
195 (SELECT
196 jbrlv.job_role_code
197 ,primary_role_for_job_flag
198 FROM hri_cs_job_job_role_v jbrlv
199 WHERE jbrlv.job_id = jbrl.job_id)
200 WHERE EXISTS
201 (SELECT NULL
202 FROM hri_cs_job_job_role_v jbrlv
203 WHERE jbrlv.job_id = jbrl.job_id
204 AND (
205 (jbrlv.job_role_code <> jbrl.job_role_code)
206 OR
207 (jbrlv.primary_role_for_job_flag <> jbrl.primary_role_for_job_flag)
208 )
209 )
210 RETURNING jbrl.job_id BULK COLLECT INTO l_upd_job_ids;
211 --
212 dbg('Updated '||sql%rowcount||' records.');
213 --
214 -- If the job role details of any of the existing records is changed then
215 -- the corresponding changes should be refelected in the assingment delta table also
216 -- So insert the JOB_ID of the updated records into the assingment delta table
217 -- so that the changes can be made to the assignment delta table by the incr process
218 --
219 IF (l_upd_job_ids.LAST > 0 AND
220 fnd_profile.value('HRI_IMPL_DBI') = 'Y') THEN
221 --
222 dbg('Populating event queue HRI_EQ_ASG_SUP_WRFC.');
223 --
224 BEGIN
225 --
226 FORALL i IN 1..l_upd_job_ids.LAST SAVE EXCEPTIONS
227 --
228 INSERT INTO HRI_EQ_ASG_SUP_WRFC
229 (SOURCE_TYPE,
230 SOURCE_ID)
231 VALUES
232 ('PRIMARY_JOB_ROLE',
233 l_upd_job_ids(i));
234 --
235 --
236 EXCEPTION
237 --
238 WHEN OTHERS THEN
239 --
240 dbg(sql%bulk_exceptions.count|| ' role records already exists in the event queue ');
241 --
242 END;
243 --
244 END IF;
245 --
246 output('Finished incremetally refreshing the Job Role table.');
247 --
248 EXCEPTION
249 --
250 WHEN OTHERS THEN
251 --
252 dbg('Error encountered in update_job_job_roles.');
253 --
254 RAISE;
255 --
256 END update_job_job_roles;
257 --
258 -- -----------------------------------------------------------------------
259 -- Full Refresh
260 -- -----------------------------------------------------------------------
261 --
262 PROCEDURE full_refresh IS
263 --
264 BEGIN
265 --
266 collect_job_job_roles;
267 --
268 -- Commit changes
269 --
270 COMMIT;
271 --
272 END full_refresh;
273 --
274 -- ------------------------------------------------------------------------
275 -- Incremental Refresh
276 -- ------------------------------------------------------------------------
277 --
278 PROCEDURE incr_refresh IS
279 --
280 BEGIN
281 --
282 update_job_job_roles;
283 --
284 -- Commit changes
285 --
286 COMMIT;
287 --
288 END incr_refresh;
289 --
290 END HRI_OPL_JOB_JOB_ROLE;
[Home] [Help]