1 PACKAGE BODY pay_nl_ww_adjustments AS
2 /* $Header: pynlsicp.pkb 120.3.12010000.2 2008/08/06 08:01:32 ubhat ship $ */
3
4 --------------------------------------------------------------
5 --Function for getting Basis Calculation Rule
6 --------------------------------------------------------------
7
8 FUNCTION Get_Basis_calc_Rule
9 ( p_source_text IN VARCHAR2,
10 p_source_text2 IN VARCHAR2,
11 p_date_earned IN DATE)
12 RETURN NUMBER IS
13
14 --Cursor for getting Basis Calc Rule
15
16 CURSOR csr_basis_calc_rule(c_si_type VARCHAR2, c_si_provider VARCHAR2, c_date DATE) IS
17
18 SELECT hoi.org_information5 basis_calc_rule
19 FROM hr_organization_information hoi
20 WHERE
21 Hoi.Organization_id = c_si_provider
22 AND hoi.org_information_context = 'NL_SIT'
23 AND hoi.org_information4 = c_si_type
24 AND c_date between fnd_date.canonical_to_date(hoi.org_information1) AND nvl(fnd_date.canonical_to_date(hoi.org_information2),hr_general.end_of_time);
25
26
27 l_basis_calc_rule csr_basis_calc_rule%ROWTYPE;
28
29 BEGIN
30 hr_utility.set_location('Entering Get_Basis_calc_Rule ',2300);
31
32 OPEN csr_basis_calc_rule(p_source_text,p_source_text2,p_date_earned);
33 FETCH csr_basis_calc_rule into l_basis_calc_rule;
34 CLOSE csr_basis_calc_rule;
35
36 RETURN to_number(l_basis_calc_rule.basis_calc_rule);
37
38 END;
39
40
41 --------------------------------------------------------------
42 --Function for getting Whether EE cont. is Gross or Net
43 --------------------------------------------------------------
44
45 FUNCTION Get_EE_Cont_Gross_Net
46 ( p_source_text IN VARCHAR2,
47 p_source_text2 IN VARCHAR2,
48 p_date_earned IN DATE)
49 RETURN VARCHAR2 IS
50
51 --Cursor for getting Whether EE cont. is Gross or Net
52
53 CURSOR csr_gross_net(c_si_type VARCHAR2, c_si_provider VARCHAR2, c_date DATE) IS
54
55 SELECT hoi.org_information14 gross_net
56 FROM hr_organization_information hoi
57 WHERE
58 Hoi.Organization_id = c_si_provider
59 AND hoi.org_information_context = 'NL_SIT'
60 AND hoi.org_information4 = c_si_type
61 AND c_date between fnd_date.canonical_to_date(hoi.org_information1) AND NVL(fnd_date.canonical_to_date(hoi.org_information2),hr_general.end_of_time);
62
63
64 l_gross_net csr_gross_net%ROWTYPE;
65
66 BEGIN
67
68 hr_utility.set_location('Entering Get_Basis_calc_Rule ',2370);
69
70 OPEN csr_gross_net(p_source_text,p_source_text2,p_date_earned);
71 FETCH csr_gross_net into l_gross_net;
72 CLOSE csr_gross_net;
73
74 RETURN l_gross_net.gross_net;
75
76 END;
77
78
79 --------------------------------------------------------------
80 -- Function for getting contribution percentages. Returns
81 -- SI Provider for next execution of Adjustment formula
82 --------------------------------------------------------------
83
84 FUNCTION Get_Adjustment_details (p_assignment_action_id IN NUMBER,
85 p_date_earned IN DATE,
86 p_source_text IN VARCHAR2,
87 p_source_text2 IN VARCHAR2,
88 p_age IN NUMBER,
89 p_ee_cont_perc IN OUT NOCOPY NUMBER,
90 p_er_cont_perc IN OUT NOCOPY NUMBER,
91 p_si_type_name OUT NOCOPY VARCHAR2)
92 RETURN VARCHAR2 IS
93
94 -- Cursor to return contribution_percentages
95 CURSOR csr_get_cont_perc(c_si_type VARCHAR2, c_si_provider VARCHAR2, c_date_earned DATE) IS
96 SELECT fnd_number.canonical_to_number(hoi.org_information6) ee_perc,
97 fnd_number.canonical_to_number(hoi.org_information7) er_perc,
98 fnd_number.canonical_to_number(hoi.org_information17) sr_ee_perc,
99 fnd_number.canonical_to_number(hoi.org_information18) sr_er_perc,
100 hoi.org_information3 si_type_name
101 FROM
102 hr_organization_information hoi
103 WHERE
104 hoi.org_information4 = c_si_type
105 AND hoi.organization_id = to_number(c_si_provider)
106 AND hoi.org_information_context = 'NL_SIT'
107 AND c_date_earned BETWEEN fnd_date.canonical_to_date(hoi.org_information1) AND nvl(fnd_date.canonical_to_date(hoi.org_information2),hr_general.end_of_time);
108 l_cont_perc csr_get_cont_perc%ROWTYPE;
109
110 l_delete VARCHAR2(15) := 'Y';
111 BEGIN
112
113 /*Version 115.1 change start*/
114
115 --hr_utility.trace_on(NULL,'ADJUSTMENT');
116
117 IF last_asg_action_id IS NULL THEN
118 last_asg_action_id := -1;
119 END IF;
120 IF last_asg_action_id <> p_assignment_action_id THEN
121 t1.delete;
122 last_asg_action_id := p_assignment_action_id;
123 END IF;
124 /* Version 115.1 change end */
125
126 hr_utility.set_location('Inside Get_Adjustment_details : NEntering',1800);
127
128 -- Populate PL/SQL table
129 populate_pl_sql_table(p_assignment_action_id, p_date_earned, p_source_text, p_source_text2) ;
130
131
132 hr_utility.set_location('Inside Get_Adjustment_details : after populate_pl_sql_table',1805);
133
134 BEGIN
135 hr_utility.set_location('Inside Get_Adjustment_details : p_source_text = '||p_source_text||' p_source_text2'||p_source_text2,1806);
136
137 OPEN csr_get_cont_perc(c_si_type => p_source_text,c_si_provider => p_source_text2,c_date_earned=> p_date_earned);
138 FETCH csr_get_cont_perc INTO l_cont_perc;
139
140 IF csr_get_cont_perc%FOUND THEN
141 hr_utility.set_location('Inside Get_Adjustment_details : Data found for cursor csr_get_cont_perc',1806);
142
143 IF p_age > pay_nl_general.get_global_value(p_date_earned, 'NL_SI_SENIOR_PERCENTAGE_AGE') THEN
144 p_ee_cont_perc := l_cont_perc.sr_ee_perc;
145 p_er_cont_perc := l_cont_perc.sr_er_perc;
146 p_si_type_name := l_cont_perc.si_type_name;
147 ELSE
148 p_ee_cont_perc := l_cont_perc.ee_perc;
149 p_er_cont_perc := l_cont_perc.er_perc;
150 p_si_type_name := l_cont_perc.si_type_name;
151 END IF;
152
153 ELSE
154 p_ee_cont_perc := 0;
155 p_er_cont_perc := 0;
156 p_si_type_name := ' ';
157 hr_utility.set_location('Inside Get_Adjustment_details : No data for cursor csr_get_cont_perc',1810);
158
159 END IF;
160
161 CLOSE csr_get_cont_perc;
162
163 -- Check out the next SIP for the current SI
164 FOR i in t1.FIRST..t1.LAST LOOP
165 hr_utility.set_location('Ins Get_Adjstment_s : T1 LOOP: SIP'||t1(i).si_provider_id||'SIT '||t1(i).si_type||' Flag ='||t1(i).processed_flag,1810);
166 IF t1(i).processed_flag = 'N' AND t1(i).si_provider_id <> p_source_text2 AND t1(i).si_type = p_source_text AND t1(i).asg_act_id = p_assignment_action_id THEN
167 hr_utility.set_location('Ins Get_Adjstment_s : T1 LOOP: SELECTED SIP'||t1(i).si_provider_id||'SIT '||p_source_text,1816);
168 t1(i).processed_flag := 'Y';
169 RETURN t1(i).si_provider_id;
170 END IF;
171 END LOOP;
172
173 -- Delete entries for the SI type after all SIPs are processed
174 FOR i in t1.FIRST..t1.LAST LOOP
175 IF t1(i).processed_flag = 'Y' AND t1(i).si_type = p_source_text AND t1(i).asg_act_id = p_assignment_action_id THEN
176 hr_utility.set_location('Deleted row'||' : SIP'||t1(i).si_provider_id||'SIT '||t1(i).si_type||' Flag ='||t1(i).processed_flag, 1878);
177 t1.delete(i);
178 END IF;
179 END LOOP;
180
181
182 RETURN '-1';
183
184 EXCEPTION
185 WHEN OTHERS THEN
186 hr_utility.set_location('Exception :' ||SQLERRM(SQLCODE),1899);
187 RAISE;
188 END;
189 END ;
190
191 --------------------------------------------------------------
192 -- Procedure to Populate PL/SQL table
193 --------------------------------------------------------------
194
195 PROCEDURE populate_pl_sql_table
196 (p_assignment_action_id IN NUMBER,
197 p_date_earned IN DATE,
198 p_si_type IN VARCHAR2,
199 p_si_provider IN VARCHAR2) IS
200
201 --Cursor for getting all SI providers for the given SI type for that person
202
203 CURSOR csr_get1(c_si_type VARCHAR2, c_assignment_action_id NUMBER) IS
204 SELECT distinct hr_nl_org_info.GET_SI_PROVIDER_INFO(paa.organization_id, c_si_type, paa.assignment_id) si_provider_id
205 FROM
206 per_all_assignments_f paa,
207 per_all_assignments_f paa1,
208 pay_assignment_actions pac
209 WHERE
210 pac.assignment_action_id=c_assignment_action_id
211 AND paa1.assignment_id = pac.assignment_id
212 AND paa1.person_id = paa.person_id
213 AND hr_nl_org_info.GET_SI_PROVIDER_INFO(paa.organization_id, c_si_type, paa.assignment_id) <> -1;
214
215 --Local Variables
216 i NUMBER := 1;
217 v_csr_get1 csr_get1%ROWTYPE;
218 populate_table VARCHAR2(10) := 'Y';
219 k NUMBER;
220
221 BEGIN
222
223 hr_utility.set_location('Inside pop_pl/sql si_type'||p_si_type||' sip'||p_si_provider,2350);
224
225 --Check whether present si_type, assignment_action_id combination has entry in PL/SQL table
226 IF t1.LAST IS NOT NULL THEN
227
228 FOR k IN t1.FIRST .. t1.LAST LOOP
229 IF t1(k).asg_act_id = p_assignment_action_id AND t1(k).si_type = p_si_type THEN
230 populate_table := 'N';
231 END IF;
232
233 END LOOP;
234 END IF;
235
236 BEGIN
237
238 i:= NVL(t1.LAST,0) +1;
239 IF populate_table = 'Y' THEN
240 FOR v_csr_get1
241 IN csr_get1(p_si_type, p_assignment_action_id)
242 LOOP
243 t1(i).si_provider_id := v_csr_get1.si_provider_id;
244 t1(i).si_type := p_si_type;
245 t1(i).asg_act_id := p_assignment_action_id;
246 IF t1(i).si_provider_id = p_si_provider THEN
247 t1(i).processed_flag := 'Y';
248 ELSE
249 t1(i).processed_flag := 'N';
250 END IF;
251 hr_utility.set_location('Inside v_csr_get1: t1(i).sip'||t1(i).si_provider_id||' SIT'||t1(i).si_type||' FLAG '||t1(i).processed_flag ,2355);
252 i := i+1;
253
254 END LOOP;
255 END IF;
256
257 hr_utility.set_location('End pop/pl/sql: SITP=' ||p_si_type||p_si_provider||'ACT_ID'||p_assignment_action_id||'T1.LAST='||NVL(T1.LAST,0),2379);
258
259 EXCEPTION
260 WHEN OTHERS THEN
261 hr_utility.set_location('Exception inside v_csr_get1:' ||SQLERRM(SQLCODE),2399);
262 RAISE;
263 END;
264 hr_utility.set_location('no Exception populate_pl_sql_table ',2398);
265 END;
266
267 FUNCTION get_si_prov_count
268 (p_assignment_id IN NUMBER,
269 p_assignment_action_id IN NUMBER)
270 RETURN NUMBER IS
271
272 l_si_prov_count NUMBER := 0;
273
274 BEGIN
275 select count(distinct(context_value)) into l_si_prov_count
276 from pay_action_contexts
277 where context_id IN (select context_id from ff_contexts
278 where context_name = 'SOURCE_TEXT2')
279 and assignment_action_id in (select paa.assignment_action_id from
280 per_all_assignments_f paaf,
281 pay_assignment_Actions paa,
282 pay_payroll_actions ppa,
283 per_time_periods ptp
284
285 where paaf.person_id = (select distinct(person_id) from per_all_assignments_f
286 where assignment_id = p_assignment_id)
287 and paaf.assignment_id = paa.assignment_id
288 and paa.payroll_action_id = ppa.payroll_action_id
289 -- and ppa.payroll_id = ptp.payroll_id
290 AND paa.action_status='C'
291 AND ppa.action_type in ('R','Q','V','B','I')
292 and paa.source_action_id is NOT NULL
293 -- and ptp.time_period_id = ppa.time_period_id
294 and ptp.time_period_id IN (select ppa1.time_period_id
295 from pay_payroll_actions ppa1, pay_assignment_Actions paa1
296 where paa1.assignment_action_id = p_assignment_action_id
297 and paa1.payroll_action_id = ppa1.payroll_action_id)
298 and ppa.date_earned between ptp.start_date and ptp.cut_off_date);
299 RETURN l_si_prov_count;
300 END get_si_prov_count;
301
302 END;
[Home] [Help]