[Home] [Help]
PACKAGE BODY: APPS.HRI_OPL_PERIOD_OF_WORK
Source
1 PACKAGE BODY HRI_OPL_PERIOD_OF_WORK AS
2 /* $Header: hriopow.pkb 120.2 2005/06/21 22:23:44 anmajumd noship $ */
3 --
4 -- Global variables
5 --
6 -- Record Type for populating table hri_cs_pow_band_ct
7 --
8 TYPE g_pow_band_record IS RECORD
9 (set_bucket_id NUMBER
10 ,set_bucket_custom_id NUMBER
11 ,set_short_name VARCHAR2(30)
12 ,set_uom VARCHAR2(10)
13 ,band_sequence NUMBER
14 ,band_range_low NUMBER
15 ,band_range_high NUMBER
16 ,wkth_wktyp_sk_fk VARCHAR2(240)
17 );
18 --
19 -- Table type for populating the table hri_cs_pow_band_ct
20 --
21 TYPE g_pow_band_tab_type IS TABLE OF g_pow_band_record INDEX BY BINARY_INTEGER;
22 --
23 -- Record type to hold pow cursor values
24 --
25 TYPE g_pow_bucket_record IS RECORD
26 (bucket_id NUMBER
27 ,bucket_custom_id NUMBER
28 ,short_name VARCHAR2(30)
29 ,range1_low NUMBER
30 ,range1_high NUMBER
31 ,range2_low NUMBER
32 ,range2_high NUMBER
33 ,range3_low NUMBER
34 ,range3_high NUMBER
35 ,range4_low NUMBER
36 ,range4_high NUMBER
37 ,range5_low NUMBER
38 ,range5_high NUMBER
39 ,range6_low NUMBER
40 ,range6_high NUMBER
41 ,range7_low NUMBER
42 ,range7_high NUMBER
43 ,range8_low NUMBER
44 ,range8_high NUMBER
45 ,range9_low NUMBER
46 ,range9_high NUMBER
47 ,range10_low NUMBER
48 ,range10_high NUMBER
49 ,uom VARCHAR2(10)
50 ,wkth_wktyp_sk_fk VARCHAR2(30)
51 );
52 --
53 g_counter NUMBER := 0;
54 --
55 -- -----------------------------------------------------------------------------
56 -- Inserts row into concurrent program log
57 -- -----------------------------------------------------------------------------
58 --
59 PROCEDURE output(p_text VARCHAR2) IS
60
61 BEGIN
62 --
63 HRI_BPL_CONC_LOG.output(p_text);
64 --
65 END output;
66
67 -- -----------------------------------------------------------------------------
68 -- Inserts row into concurrent program log if debugging is enabled
69 -- -----------------------------------------------------------------------------
70 --
71 PROCEDURE dbg(p_text VARCHAR2) IS
72
73 BEGIN
74 --
75 HRI_BPL_CONC_LOG.dbg(p_text);
76 --
77 END dbg;
78
79 --
80 -- ------------------------------------------------------------------------------
81 -- Checks if records are defined in the buckets and populates the record in
82 -- the table type
83 -- ------------------------------------------------------------------------------
84 --
85 PROCEDURE chk_and_populate_pow_records(p_pow_bucket_record IN g_pow_bucket_record
86 ,p_band_sequence IN NUMBER
87 ,p_range_low IN NUMBER
88 ,p_range_high IN NUMBER
89 ,p_populate_flag IN OUT NOCOPY VARCHAR2
90 ,p_pow_band_tab_type IN OUT NOCOPY g_pow_band_tab_type) IS
91
92 --
93 BEGIN
94 --
95 -- Flag when both low and high values are null
96 --
97 IF (p_range_low IS NULL) AND (p_range_high IS NULL) THEN
98 --
99 -- If the final value for the highest band is not null then make it null
100 -- This is done to ensure every record falls into a pow band
101 -- Set the warning flag
102 --
103 IF (p_pow_band_tab_type(g_counter).band_range_high IS NOT NULL) THEN
104 --
105 p_pow_band_tab_type(g_counter).band_range_high := NULL;
106 --
107 g_warning_flag := 'Y';
108 --
109 output('The high value for band' || to_char(g_counter) || ' of bucket ' || p_pow_band_tab_type(g_counter).set_short_name || ' is being set to');
110 output('null. This is done to ensure that all the records are inside a period of ');
111 output('work band.');
112 --
113 p_populate_flag := 'N';
114 --
115 END IF;
116 --
117 -- Populate only when values are present in the buckets
118 --
119 ELSIF (p_range_low IS NOT NULL OR p_range_high IS NOT NULL) THEN
120 --
121 g_counter := g_counter + 1;
122 --
123 p_pow_band_tab_type(g_counter).set_bucket_id := p_pow_bucket_record.bucket_id;
124 p_pow_band_tab_type(g_counter).set_bucket_custom_id := p_pow_bucket_record.bucket_custom_id;
125 p_pow_band_tab_type(g_counter).set_short_name := p_pow_bucket_record.short_name;
126 p_pow_band_tab_type(g_counter).set_uom := p_pow_bucket_record.uom;
127 p_pow_band_tab_type(g_counter).band_sequence := p_band_sequence;
128 p_pow_band_tab_type(g_counter).band_range_low := p_range_low;
129 p_pow_band_tab_type(g_counter).band_range_high := p_range_high;
130 p_pow_band_tab_type(g_counter).wkth_wktyp_sk_fk := p_pow_bucket_record.wkth_wktyp_sk_fk;
131 --
132 -- Flag whenever high value is set to NULL
133 --
134 IF p_range_high IS NULL THEN
135 --
136 p_populate_flag := 'N';
137 --
138 END IF;
139 --
140 END IF;
141 --
142 END chk_and_populate_pow_records;
143 --
144 -- ------------------------------------------------------------------------------
145 -- Collects the records in a table type
146 -- ------------------------------------------------------------------------------
147 --
148 PROCEDURE collect_records(p_pow_band_tab_type OUT NOCOPY g_pow_band_tab_type) IS
149 --
150 l_pow_bucket_record g_pow_bucket_record;
151 l_band_sequence NUMBER;
152 l_populate_flag VARCHAR2(1) := 'Y';
153 l_range_low VARCHAR2(100);
154 l_range_high VARCHAR2(100);
155 --
156 CURSOR pow_csr IS
157 SELECT
158 bkt.bucket_id bucket_id
159 ,bbc.id bucket_custom_id
160 ,bkt.short_name short_name
161 ,bbc.range1_low range1_low
162 ,bbc.range1_high range1_high
163 ,bbc.range2_low range2_low
164 ,bbc.range2_high range2_high
165 ,bbc.range3_low range3_low
166 ,bbc.range3_high range3_high
167 ,bbc.range4_low range4_low
168 ,bbc.range4_high range4_high
169 ,bbc.range5_low range5_low
170 ,bbc.range5_high range5_high
171 ,bbc.range6_low range6_low
172 ,bbc.range6_high range6_high
173 ,bbc.range7_low range7_low
174 ,bbc.range7_high range7_high
175 ,bbc.range8_low range8_low
176 ,bbc.range8_high range8_high
177 ,bbc.range9_low range9_low
178 ,bbc.range9_high range9_high
179 ,bbc.range10_low range10_low
180 ,bbc.range10_high range10_high
181 ,bkt.uom uom
182 ,CASE
183 WHEN bkt.short_name = 'HRI_DBI_LOW_BAND_CURRENT' THEN
184 'EMP'
185 WHEN bkt.short_name = 'HRI_DBI_POW_PLCMNT_BAND' THEN
186 'CWK'
187 ELSE
188 NULL
189 END wkth_wktyp_sk_fk
190 FROM bis_bucket bkt,
191 bis_bucket_customizations bbc
192 WHERE bkt.bucket_id = bbc.bucket_id
193 AND bkt.short_name IN ('HRI_DBI_LOW_BAND_CURRENT','HRI_DBI_POW_PLCMNT_BAND');
194
195 BEGIN
196 --
197 OPEN pow_csr;
198 --
199 LOOP
200 --
201 FETCH pow_csr INTO l_pow_bucket_record;
202 EXIT WHEN pow_csr%NOTFOUND;
203 --
204 -- Loop for 10 times (since there are 10 buckets in bis_bucket table)
205 --
206 FOR n IN 1..10 LOOP
207 --
208 -- Check if the table needs to be populated
209 --
210 IF (l_populate_flag = 'N') THEN
211 --
212 -- Set the populate flag to 'Y' for next iteration and then exit the
213 -- current loop
214 --
215 l_populate_flag := 'Y';
216 --
217 EXIT;
218 --
219 ELSIF (n = 1) THEN
220 --
221 l_band_sequence := 1;
222 l_range_low := l_pow_bucket_record.range1_low;
223 l_range_high := l_pow_bucket_record.range1_high;
224 --
225 ELSIF (n = 2) THEN
226 --
227 l_band_sequence := 2;
228 l_range_low := l_pow_bucket_record.range2_low;
229 l_range_high := l_pow_bucket_record.range2_high;
230 --
231 ELSIF (n = 3) THEN
232 --
233 l_band_sequence := 3;
234 l_range_low := l_pow_bucket_record.range3_low;
235 l_range_high := l_pow_bucket_record.range3_high;
236 --
237 ELSIF (n = 4) THEN
238 --
239 l_band_sequence := 4;
240 l_range_low := l_pow_bucket_record.range4_low;
241 l_range_high := l_pow_bucket_record.range4_high;
242 --
243 ELSIF (n = 5) THEN
244 --
245 l_band_sequence := 5;
246 l_range_low := l_pow_bucket_record.range5_low;
247 l_range_high := l_pow_bucket_record.range5_high;
248 --
249 ELSIF (n = 6) THEN
250 --
251 l_band_sequence := 6;
252 l_range_low := l_pow_bucket_record.range6_low;
253 l_range_high := l_pow_bucket_record.range6_high;
254 --
255 ELSIF (n = 7) THEN
256 --
257 l_band_sequence := 7;
258 l_range_low := l_pow_bucket_record.range7_low;
259 l_range_high := l_pow_bucket_record.range7_high;
260 --
261 ELSIF (n = 8) THEN
262 --
263 l_band_sequence := 8;
264 l_range_low := l_pow_bucket_record.range8_low;
265 l_range_high := l_pow_bucket_record.range8_high;
266 --
267 ELSIF (n = 9) THEN
268 --
269 l_band_sequence := 9;
270 l_range_low := l_pow_bucket_record.range9_low;
271 l_range_high := l_pow_bucket_record.range9_high;
272 --
273 ELSE
274 --
275 l_band_sequence := 10;
276 l_range_low := l_pow_bucket_record.range10_low;
277 l_range_high := l_pow_bucket_record.range10_high;
278 --
279 END IF;
280 --
281 chk_and_populate_pow_records(l_pow_bucket_record
282 ,l_band_sequence
283 ,l_range_low
284 ,l_range_high
285 ,l_populate_flag
286 ,p_pow_band_tab_type
287 );
288 --
289 END LOOP;
290 --
291 END LOOP;
292 --
293 END collect_records;
294
295 --
296 -- ----------------------------------------------------------------------------
297 -- Inserts the records in table hri_cs_pow_band_ct
298 -- ----------------------------------------------------------------------------
299 --
300 PROCEDURE insert_records(p_pow_band_tab_type IN g_pow_band_tab_type) IS
301 --
302 l_current_time DATE := SYSDATE;
303 l_user_id NUMBER := fnd_global.user_id;
304 --
305 BEGIN
306 --
307 FOR i in p_pow_band_tab_type.FIRST..p_pow_band_tab_type.LAST LOOP
308 --
309 INSERT INTO HRI_CS_POW_BAND_CT
310 (
311 pow_band_sk_pk
312 ,set_bucket_id
313 ,set_bucket_custom_id
314 ,set_short_name
315 ,set_uom
316 ,band_sequence
317 ,band_range_low
318 ,band_range_high
319 ,wkth_wktyp_sk_fk
320 ,created_by
321 ,creation_date
322 ,last_update_date
323 ,last_updated_by
324 ,last_update_login
325 )
326 VALUES
327 (hri_cs_pow_band_ct_s.nextval
328 ,p_pow_band_tab_type(i).set_bucket_id
329 ,p_pow_band_tab_type(i).set_bucket_custom_id
330 ,p_pow_band_tab_type(i).set_short_name
331 ,p_pow_band_tab_type(i).set_uom
332 ,p_pow_band_tab_type(i).band_sequence
333 ,p_pow_band_tab_type(i).band_range_low
334 ,p_pow_band_tab_type(i).band_range_high
335 ,p_pow_band_tab_type(i).wkth_wktyp_sk_fk
336 ,l_user_id
337 ,l_current_time
338 ,l_current_time
339 ,l_user_id
340 ,l_user_id
341 );
342 --
343 END LOOP;
344 --
345 END insert_records;
346 --
347 -- ------------------------------------------------------------------------------
348 -- Full refresh entry point
349 -- ------------------------------------------------------------------------------
350 --
351 PROCEDURE full_refresh IS
352 --
353 l_pow_band_tab_type g_pow_band_tab_type;
354 l_hri_schema VARCHAR2(300);
355 l_dummy1 VARCHAR2(2000);
356 l_dummy2 VARCHAR2(2000);
357 --
358 BEGIN
359 --
360 --
361 -- Truncate the table hri_cs_pow_band_ct
362 --
363 IF (fnd_installation.get_app_info('HRI',l_dummy1, l_dummy2, l_hri_schema)) THEN
364 --
365 EXECUTE IMMEDIATE 'TRUNCATE TABLE ' || l_hri_schema || '.HRI_CS_POW_BAND_CT';
366 --
367 END IF;
368 --
369
370 collect_records(l_pow_band_tab_type);
371 --
372 insert_records(l_pow_band_tab_type);
373 --
374 -- Commit changes
375 --
376 COMMIT;
377 --
378 END full_refresh;
379
380 --
381 END HRI_OPL_PERIOD_OF_WORK;