[Home] [Help]
PACKAGE BODY: APPS.PQH_EMPLOYMENT_CATEGORY
Source
1 PACKAGE BODY pqh_employment_category AS
2 /* $Header: pqhuseeo.pkb 115.7 2003/11/17 11:39:33 nsanghal noship $*/
3 --
4 -- ----------------------------------------------------------------------------
5 -- | Private Global Definitions |
6 -- ----------------------------------------------------------------------------
7 --
8 g_package varchar2(33) := ' pqh_employment_category.'; -- Global package name
9 --
10 --
11 -- ----------------------------------------------------------------------------
12 -- |-------------------------< fetch_empl_categories >------------------------|
13 -- ----------------------------------------------------------------------------
14 PROCEDURE fetch_empl_categories (
15 p_business_group_id in number,
16 p_full_time_regular out nocopy varchar2,
17 p_full_time_temp out nocopy varchar2,
18 p_part_time_regular out nocopy varchar2,
19 p_part_time_temp out nocopy varchar2 ) IS
20 --
21 l_proc varchar2(72) := g_package||'fetch_empl_categories';
22 --
23 l_effective_date date := TRUNC(SYSDATE);
24 l_outputs ff_exec.outputs_t;
25 l_inputs ff_exec.inputs_t;
26 l_formula_id number;
27 --
28 --
29 cursor c_frml is
30 select formula_id
31 from ff_formulas_f
32 where formula_name = 'PQH_EMPLOYMENT_CATEGORY'
33 and business_group_id = p_business_group_id
34 and l_effective_date between effective_start_date and effective_end_date;
35 --
36 Begin
37 --
38 hr_utility.set_location('Entering:'||l_proc, 5);
39 --
40 --
41 open c_frml;
42 fetch c_frml into l_formula_id;
43 close c_frml;
44 --
45 -- ------------------------------------------------
46 -- Set the defaults if the formula is not defined.
47 -- ------------------------------------------------
48 --
49
50 if l_formula_id is null then
51 p_full_time_regular := '''FR''';
52 p_full_time_temp := '''FT''';
53 p_part_time_regular := '''PR''';
54 p_part_time_temp := '''PT''';
55 --
56 else
57 --
58 begin
59 --
60 insert into fnd_sessions (
61 session_id, effective_date)
62 values (userenv('sessionid'), l_effective_date);
63 --
64 exception
65 --
66 when dup_val_on_index then
67 null;
68 end;
69 --
70 --
71
72 ff_exec.init_formula(l_formula_id, l_effective_date, l_inputs, l_outputs);
73 ff_exec.run_formula(l_inputs, l_outputs);
74 --
75 for l_out_cnt in l_outputs.first..l_outputs.last loop
76 if l_outputs(l_out_cnt).name = 'FULL_TIME_REGULARS' then
77 p_full_time_regular := ''''||l_outputs(l_out_cnt).value||'''';
78 elsif l_outputs(l_out_cnt).name = 'FULL_TIME_TEMPS' then
79 p_full_time_temp := ''''||l_outputs(l_out_cnt).value||'''';
80 elsif l_outputs(l_out_cnt).name = 'PART_TIME_REGULARS' then
81 p_part_time_regular := ''''||l_outputs(l_out_cnt).value||'''';
82 elsif l_outputs(l_out_cnt).name = 'PART_TIME_TEMPS' then
83 p_part_time_temp := ''''||l_outputs(l_out_cnt).value||'''';
84 end if;
85 end loop;
86 end if;
87 --
88 hr_utility.set_location(' Leaving:'||l_proc, 10);
89 --
90 exception when others then
91 p_full_time_regular := null;
92 p_full_time_temp := null;
93 p_part_time_regular := null;
94 p_part_time_temp := null;
95 raise;
96 End;
97 --
98 --
99 -- ----------------------------------------------------------------------------
100 -- |------------------------< identify_empl_categories >----------------------|
101 -- ----------------------------------------------------------------------------
102 FUNCTION identify_empl_category (
103 p_empl_category in varchar2,
104 p_full_time_regular in varchar2,
105 p_full_time_temp in varchar2,
106 p_part_time_regular in varchar2,
107 p_part_time_temp in varchar2 ) RETURN varchar2 IS
108 --
109 l_proc varchar2(72) := g_package||'identify_empl_category';
110 l_delim varchar2(3) := ',';
111 l_fr varchar2(10000) := replace(replace(p_full_time_regular,'''',null),' ',null);
112 l_ft varchar2(10000) := replace(replace(p_full_time_temp,'''',null),' ',null);
113 l_pr varchar2(10000) := replace(replace(p_part_time_regular,'''',null),' ',null);
114 l_pt varchar2(10000) := replace(replace(p_part_time_temp,'''',null),' ',null);
115 --
116 l_empl_cat varchar2(100) := l_delim||p_empl_category||l_delim;
117 --
118 BEGIN
119 --
120 hr_utility.set_location('Entering:'||l_proc, 5);
121 --
122 l_fr := replace( replace(l_delim||l_fr||l_delim,', ',l_delim), ' ,', l_delim);
123 l_ft := replace( replace(l_delim||l_ft||l_delim,', ',l_delim), ' ,', l_delim);
124 l_pr := replace( replace(l_delim||l_pr||l_delim,', ',l_delim), ' ,', l_delim);
125 l_pt := replace( replace(l_delim||l_pt||l_delim,', ',l_delim), ' ,', l_delim);
126
127 if instr(l_fr, l_empl_cat) > 0 then
128 return 'FR';
129 elsif instr(l_ft, l_empl_cat) > 0 then
130 return 'FT';
131 elsif instr(l_pr, l_empl_cat) > 0 then
132 return 'PR';
133 elsif instr(l_pt, l_empl_cat) > 0 then
134 return 'PT';
135 else
136 return 'XX';
137 end if;
138
139 --
140 hr_utility.set_location(' Leaving:'||l_proc, 10);
141 --
142 END;
143 --
144 --
145 FUNCTION get_duration_in_months (p_duration IN NUMBER,
146 p_duration_units IN VARCHAR2,
147 p_business_group_id IN NUMBER,
148 p_ref_date IN DATE default sysdate)
149 RETURN NUMBER IS
150 l_ref_date DATE := trunc(NVL(p_ref_date,sysdate));
151 l_ret_months NUMBER;
152 l_conv_factor NUMBER;
153 CURSOR csr_conv_factor IS
154 SELECT information1
155 FROM per_shared_types
156 WHERE lookup_type = 'QUALIFYING_UNITS'
157 AND business_group_id = p_business_group_id
158 AND system_type_cd = p_duration_units
159 UNION ALL
160 SELECT information1
161 FROM per_shared_types
162 WHERE lookup_type = 'QUALIFYING_UNITS'
163 AND business_group_id IS NULL
164 AND system_type_cd = p_duration_units;
165 BEGIN
166 -- return the duration as it is if the units are Months
167 if p_duration_units = 'M' THEN
168 RETURN p_duration;
169 end if;
170 --
171 --get the conversion factor for the Units
172 OPEN csr_conv_factor;
173 FETCH csr_conv_factor INTO l_conv_factor;
174 CLOSE csr_conv_factor;
175 -- if the conversion factor is set, multiply the duration with CF to get the
176 -- equivalent months else use default conversion for the known units
177 IF l_conv_factor IS NOT NULL THEN
178 l_ret_months := p_duration*l_conv_factor;
179 ELSE
180 IF p_duration_units = 'D' THEN
181 l_ret_months := Months_Between(l_ref_date+p_duration,l_ref_date);
182 ELSIF p_duration_units = 'Y' THEN
183 l_ret_months := p_duration*12;
184 ELSIF p_duration_units = 'W' THEN
185 l_ret_months := Months_between(l_ref_date+(p_duration*7),l_ref_date );
186 ELSE
187 l_ret_months := 0;
188 END IF;
189 END IF;
190 RETURN l_ret_months;
191 END;
192 FUNCTION get_service_start_date(p_period_of_service_id IN NUMBER) RETURN DATE IS
193 l_start_date DATE;
194 CURSOR csr_pos_date_start IS
195 SELECT date_start
196 FROM per_periods_of_service
197 WHERE period_of_service_id = p_period_of_service_id;
198 BEGIN
199 OPEN csr_pos_date_start;
200 FETCH csr_pos_date_start INTO l_start_date;
201 CLOSE csr_pos_date_start;
202 RETURN l_start_date;
203 END;
204 END pqh_employment_category;