[Home] [Help]
PACKAGE BODY: APPS.HR_CAGR_FF_PKG
Source
1 PACKAGE BODY hr_cagr_ff_pkg AS
2 /* $Header: hrcagrff.pkb 115.6 2002/12/09 14:45:49 hjonnala noship $ */
3 --
4 -- Package Variables
5 --
6 g_package varchar2(33) := 'hr_cagr_ff_pkg.';
7
8 /*
9 Name : hr_cagr_ff_pkg (BODY)
10 */
11 --
12 -- ------------------- cagr_entitlement_ff --------------------
13 --
14 --
15 procedure cagr_entitlement_ff
16 ( p_formula_id IN NUMBER,
17 p_effective_date IN DATE,
18 p_assignment_id IN NUMBER,
19 p_category_name IN VARCHAR2,
20 p_out_rec OUT NOCOPY hr_cagr_ff_pkg.cagr_FF_record) IS
21 --
22 l_proc varchar2(72) := g_package||'cagr_entitlement_ff';
23 --
24 l_formula_id NUMBER;
25 l_effective_start_date DATE;
26 l_effective_end_date DATE;
27 l_inputs ff_exec.inputs_t;
28 l_outputs ff_exec.outputs_t;
29 l_business_group_id NUMBER;
30 l_person_id NUMBER;
31 l_organization_id NUMBER;
32 l_payroll_id NUMBER;
33 l_tax_unit_id NUMBER;
34 l_formula_name VARCHAR2(30);
35
36 cursor csr_assignment is
37 select paf.business_group_id
38 , paf.organization_id
39 , paf.payroll_id
40 , paf.person_id
41 , scl.segment1
42 from per_all_assignments_f paf,
43 hr_soft_coding_keyflex scl
44 where paf.assignment_id = p_assignment_id
45 and p_effective_date between paf.effective_start_date
46 and paf.effective_end_date
47 and paf.soft_coding_keyflex_id = scl.soft_coding_keyflex_id(+);
48
49 cursor csr_formula is
50 select ff.formula_name
51 from ff_formulas_f ff
52 where ff.formula_id = p_formula_id
53 and p_effective_date between ff.effective_start_date
54 and ff.effective_end_date;
55
56 --
57 Begin
58 --
59 hr_utility.set_location('Entering:'|| l_proc, 10);
60 --
61 -- validate arguments prior to calling FF
62 --
63 -- check national identifier is not null
64 --
65 if p_assignment_id is null then
66 hr_utility.set_message(800,'HR_XXXXX_ASSIGNMENT_ID_NULL');
67 hr_utility.raise_error;
68 end if;
69 --
70 open csr_formula;
71
72 fetch csr_formula into l_formula_name;
73
74 close csr_formula;
75 --
76 hr_utility.set_location(l_proc, 20);
77 --
78 open csr_assignment;
79
80 fetch csr_assignment into
81 l_business_group_id
82 , l_organization_id
83 , l_payroll_id
84 , l_person_id
85 , l_tax_unit_id;
86 --
87 close csr_assignment;
88
89 --
90 hr_utility.set_location('l_business_group_id = '||to_char(l_business_group_id),11);
91 hr_utility.set_location('l_organization_id = '||to_char(l_organization_id),11);
92 hr_utility.set_location('l_payroll_id = '||to_char(l_payroll_id),11);
93 hr_utility.set_location('l_person_id = '||to_char(l_person_id),11);
94 --
95 per_cagr_utility_pkg.put_log(' Information : Formula '||l_formula_name);
96 per_cagr_utility_pkg.put_log(' Information : Business Group ID = '||l_business_group_id);
97 per_cagr_utility_pkg.put_log(' Information : Organization ID = '||l_organization_id);
98 per_cagr_utility_pkg.put_log(' Information : Payroll ID = '||l_payroll_id);
99 per_cagr_utility_pkg.put_log(' Information : Person ID = '||l_person_id);
100 per_cagr_utility_pkg.put_log(' Information : Tax Unit ID = '||l_tax_unit_id);
101
102 ff_exec.init_formula(p_formula_id,p_effective_date,l_inputs,l_outputs);
103 --
104 hr_utility.set_location('Inputs='||ff_exec.input_count,6);
105 hr_utility.set_location('Contexts='||ff_exec.context_count,6);
106 hr_utility.set_location('Outputs='||ff_exec.output_count,6);
107
108 if ff_exec.input_count > 0 then
109 per_cagr_utility_pkg.put_log('ERROR : CAGR formula has unexpected Input values defined.');
110 end if;
111 -- if ff_exec.output_count < 8 then
112 -- per_cagr_utility_pkg.put_log('ERROR : CAGR formula has less than 8 Output values defined.');
113 -- end if;
114 -- if ff_exec.output_count > 8 then
115 -- per_cagr_utility_pkg.put_log('ERROR : CAGR formula has more than 8 Output values defined.');
116 -- end if;
117 -- hr_utility.set_location('FF',12);
118 --
119
120 for l_in_cnt in l_inputs.first..l_inputs.last
121 loop
122 if l_inputs(l_in_cnt).name='ASSIGNMENT_ID' then
123 l_inputs(l_in_cnt).value := p_assignment_id;
124 -- per_cagr_utility_pkg.put_log('Information : Context - Assignment ID');
125 hr_utility.set_location('ASSIGNMENT_ID',13);
126 elsif l_inputs(l_in_cnt).name='DATE_EARNED' then
127 l_inputs(l_in_cnt).value := fnd_date.date_to_canonical(p_effective_date);
128 -- per_cagr_utility_pkg.put_log('Information : Context - DATE_EARNED');
129 hr_utility.set_location('DATE_EARNED',14);
130 elsif l_inputs(l_in_cnt).name='PERSON_ID' then
131 l_inputs(l_in_cnt).value := l_person_id;
132 -- per_cagr_utility_pkg.put_log('Information : Context - PERSON_ID');
133 hr_utility.set_location('PERSON_ID',15);
134 elsif l_inputs(l_in_cnt).name='ORGANIZATION_ID' then
135 l_inputs(l_in_cnt).value := l_organization_id;
136 -- per_cagr_utility_pkg.put_log('Information : Context - ORGANIZATION_ID');
137 hr_utility.set_location('ORGANIZATION_ID',16);
138 elsif l_inputs(l_in_cnt).name='BUSINESS_GROUP_ID' then
139 l_inputs(l_in_cnt).value := l_business_group_id;
140 -- per_cagr_utility_pkg.put_log('Information : Context - BUSINESS_GROUP_ID');
141 hr_utility.set_location('BUSINESS_GROUP_ID',17);
142 elsif l_inputs(l_in_cnt).name='PAYROLL_ID' then
143 l_inputs(l_in_cnt).value := l_payroll_id;
144 -- per_cagr_utility_pkg.put_log('Information : Context - PAYROLL_ID');
145 hr_utility.set_location('PAYROLL_ID',18);
146 elsif l_inputs(l_in_cnt).name='TAX_UNIT_ID' then
147 l_inputs(l_in_cnt).value := l_tax_unit_id;
148 -- per_cagr_utility_pkg.put_log('Information : Context - TAX_UNIT_ID');
149 hr_utility.set_location('TAX_UNIT_ID',19);
150 end if;
151 -- per_cagr_utility_pkg.put_log('Information : End of Context Section ');
152 end loop;
153 --
154 hr_utility.set_location(l_proc, 15);
155 --
156 --
157 -- per_cagr_utility_pkg.put_log('Before Start of Run Formula ');
158 ff_exec.run_formula
159 (P_INPUTS => l_inputs
160 ,P_OUTPUTS => l_outputs
161 ,P_USE_DBI_CACHE=> FALSE);
162 --
163 -- per_cagr_utility_pkg.put_log('Start of Run Formula ');
164 for l_out_cnt in
165 l_outputs.first..l_outputs.last
166 loop
167 if l_outputs(l_out_cnt).name='VALUE' then
168 p_out_rec.value := l_outputs(l_out_cnt).value;
169 -- per_cagr_utility_pkg.put_log('Information : Output - Value');
170 elsif
171 l_outputs(l_out_cnt).name='RANGE_FROM' then
172 p_out_rec.range_from := l_outputs(l_out_cnt).value;
173 -- per_cagr_utility_pkg.put_log('Information : Output - Range From');
174 elsif
175 l_outputs(l_out_cnt).name='RANGE_TO' then
176 p_out_rec.range_to := l_outputs(l_out_cnt).value;
177 -- per_cagr_utility_pkg.put_log('Information : Output - Range To');
178 elsif
179 l_outputs(l_out_cnt).name='PARENT_SPINE_ID' then
180 p_out_rec.parent_spine_id := l_outputs(l_out_cnt).value;
181 -- per_cagr_utility_pkg.put_log('Information : Output - Parent Spine ID');
182 elsif
183 l_outputs(l_out_cnt).name='STEP_ID' then
184 p_out_rec.step_id := l_outputs(l_out_cnt).value;
185 -- per_cagr_utility_pkg.put_log('Information : Output - Step ID');
186 elsif
187 l_outputs(l_out_cnt).name='FROM_STEP_ID' then
188 p_out_rec.from_step_id := l_outputs(l_out_cnt).value;
189 -- per_cagr_utility_pkg.put_log('Information : Output - From Step ID');
190 elsif
191 l_outputs(l_out_cnt).name='TO_STEP_ID' then
192 p_out_rec.to_step_id := l_outputs(l_out_cnt).value;
193 -- per_cagr_utility_pkg.put_log('Information : Output - To Step ID');
194 elsif
195 l_outputs(l_out_cnt).name='GRADE_SPINE_ID' then
196 p_out_rec.grade_spine_id := l_outputs(l_out_cnt).value;
197 -- per_cagr_utility_pkg.put_log('Information : Output - Grade Spine ID');
198 end if;
199 -- per_cagr_utility_pkg.put_log('Information : End of Output Section');
200 end loop;
201 --
202 --
203
204 if p_category_name = 'PYS' then
205
206 -- if
207 -- (p_out_rec.parent_spine_id is null OR
208 -- p_out_rec.step_id is null OR
209 -- p_out_rec.grade_spine_id is null) THEN
210 -- hr_utility.set_location('Mandatory Outputs missing for Category '||p_category_name,100);
211 -- per_cagr_utility_pkg.put_log('Warning : Mandatory Outputs missing for Category '||p_category_name);
212 -- end if;
213 if
214 (p_out_rec.value is not null OR
215 p_out_rec.range_from is not null OR
216 p_out_rec.range_to is not null) THEN
217 hr_utility.set_location('Invalid Outputs Returned for Category '||p_category_name,200);
218 per_cagr_utility_pkg.put_log('Warning : Invalid Outputs Returned for Category '||p_category_name);
219 end if;
220 elsif p_category_name in ('ASG','PAY','ABS') then
221 -- if
222 -- p_out_rec.value is null then
223 -- hr_utility.set_location('Mandatory Output missing for Category '||p_category_name,300);
224 -- per_cagr_utility_pkg.put_log('Warning : Mandatory Output missing for Category '||p_category_name);
225 -- end if;
226 if
227 (p_out_rec.parent_spine_id is not null OR
228 p_out_rec.step_id is not null OR
229 p_out_rec.from_step_id is not null OR
230 p_out_rec.to_step_id is not null or
231 p_out_rec.grade_spine_id is not null) THEN
232 hr_utility.set_location('Invalid Outputs Returned for Category '||p_category_name,400);
233 per_cagr_utility_pkg.put_log('Warning : Invalid Outputs Returned for Category '||p_category_name);
234 end if;
235 else
236 hr_utility.set_location('Incorrect Category Name = '||p_category_name,500);
237 per_cagr_utility_pkg.put_log('Warning : Category is incorrect');
238 end if;
239
240 hr_utility.set_location('Leaving:'|| l_proc, 22);
241 --
242 per_cagr_utility_pkg.put_log(' Information : End of Fast Formula Evaluation.');
243 EXCEPTION
244 when OTHERS then
245 p_out_rec := null;
246 hr_utility.set_location('Formula Not Compiled',600);
247 -- per_cagr_utility_pkg.put_log('Error : Formula '||l_formula_name||' has not been compiled or is invalid : '||sqlerrm,1);
248 per_cagr_utility_pkg.put_log('Error : Formula '||l_formula_name);
249 per_cagr_utility_pkg.put_log('Error : '||sqlerrm);
250 --
251 end cagr_entitlement_ff;
252
253
254 end hr_cagr_ff_pkg;