1 PACKAGE PAY_NO_EMP_CONT AS
2 /* $Header: pynoempcont.pkh 120.5 2006/05/26 07:03:48 pgopal noship $ */
3
4 ------------------------------- Definig Globals and initializing ----------------------------------------------------
5
6 /* Bug No. : 4474602 and 4463136 */
7
8 g_error_flag BOOLEAN := FALSE;
9
10 g_error_check BOOLEAN := FALSE;
11
12
13 --------------------------------Defining record Types------------------------------------------------
14
15 -- record type for Main table for Legal Employer and Local Unit
16
17 TYPE g_tab_main_rectype IS
18 RECORD ( legal_employer_id NUMBER
19 ,local_unit_id NUMBER
20 ,zone NUMBER
21 ,under_62 VARCHAR2(10)
22 ,exemption_limit_used NUMBER
23 ,run_base NUMBER
24 ,run_contribution NUMBER );
25
26
27 -- record type for Calculation table for Legal Employer and Local Unit
28
29 TYPE g_tab_calc_rectype IS
30 RECORD ( zone NUMBER
31 ,under_62 varchar2(10)
32 ,under_limit varchar2(10)
33 ,status varchar2(200)
34 ,bimonth_base NUMBER
35 ,run_base NUMBER
36 ,bimonth_contribution NUMBER
37 ,bimonth_contribution_todate NUMBER
38 ,run_contribution NUMBER );
39
40
41
42 ---------------------------------Defining Table Types-----------------------------------------------
43
44
45 -- table type for Main table for Legal Employer and Local Unit
46
47 TYPE g_tab_main_tabtype IS TABLE OF g_tab_main_rectype
48 INDEX BY BINARY_INTEGER;
49
50
51 -- table type for Calculation table for Legal Employer and Local Unit
52
53 TYPE g_tab_calc_tabtype IS TABLE OF g_tab_calc_rectype
54 INDEX BY BINARY_INTEGER;
55
56 ------------------new table types
57
58 -- table type for unique LU
59 TYPE g_lu_tabtype IS TABLE OF NUMBER (20)
60 INDEX BY BINARY_INTEGER;
61
62 -- table type for unique LU
63 -- changing type from Number to Varchar for Jurisdiction code (text) / TM
64
65 TYPE g_mu_tabtype IS TABLE OF VARCHAR2 (20)
66 INDEX BY BINARY_INTEGER;
67
68
69
70
71 ---------------------------------Defining Global PL/SQL Table -----------------------------------------------
72
73 g_tab_main PAY_NO_EMP_CONT.g_tab_main_tabtype;
74
75 --------------------------------Cursor Definitions ----------------------------------------------------------
76
77 -- cursor to get Status and Report Separately from Local Unit
78 cursor get_lu_details(l_local_unit_id NUMBER) IS
79 select ORG_INFORMATION4 status , ORG_INFORMATION5 report_sep
80 from HR_ORGANIZATION_INFORMATION
81 where ORGANIZATION_ID = l_local_unit_id
82 and org_information_context = 'NO_LOCAL_UNIT_DETAILS';
83
84
85
86 -- Cursor to get the payroll_action_id
87 /* cursor csr_payroll_action_id (p_payroll_id pay_all_payrolls_f.PAYROLL_ID%type ,p_date_earned DATE ) is
88 select ppa.PAYROLL_ACTION_ID
89 from pay_payroll_actions ppa
90 ,pay_run_types_f prt
91 where ppa.PAYROLL_ID = p_payroll_id
92 and prt.LEGISLATION_CODE = 'NO'
93 and prt.RUN_TYPE_NAME <> 'Employer Contributions'
94 and prt.RUN_TYPE_ID = ppa.RUN_TYPE_ID
95 and p_date_earned between prt.EFFECTIVE_START_DATE and prt.EFFECTIVE_END_DATE
96 and ppa.effective_date between trunc(Add_months(p_date_earned,MOD(TO_NUMBER(TO_CHAR(p_date_earned,'MM')),2)-1),'MM')
97 and p_date_earned ; */
98
99 --Bug Fix 4465274, modified the cursor
100 /*
101 cursor csr_payroll_action_id (p_date_earned DATE ) is
102 select ppa.PAYROLL_ACTION_ID
103 from pay_payroll_actions ppa
104 ,pay_run_types_f prt
105 where prt.LEGISLATION_CODE = 'NO'
106 and prt.RUN_TYPE_NAME <> 'Employer Contributions'
107 and prt.RUN_TYPE_ID = ppa.RUN_TYPE_ID
108 and p_date_earned between prt.EFFECTIVE_START_DATE and prt.EFFECTIVE_END_DATE
109 and ppa.effective_date between trunc(Add_months(p_date_earned,MOD(TO_NUMBER(TO_CHAR(p_date_earned,'MM')),2)-1),'MM')
110 and last_day(Add_months(p_date_earned,MOD(TO_NUMBER(TO_CHAR(p_date_earned,'MM')),2)));
111 */
112
113 --Bug Fix 5042861, modified the cursor for performance fix to remove TABLE ACCESS FULL (SQL ID:16539845)
114
115 cursor csr_payroll_action_id (p_date_earned DATE ) is
116 select ppa.PAYROLL_ACTION_ID
117 from pay_payroll_actions ppa
118 ,pay_run_types_f prt
119 where ppa.RUN_TYPE_ID = prt.RUN_TYPE_ID
120 and prt.LEGISLATION_CODE = 'NO'
121 and prt.RUN_TYPE_NAME IN ('Main','Standard','Process Alone','Pay Separately')
122 and p_date_earned between prt.EFFECTIVE_START_DATE and prt.EFFECTIVE_END_DATE
123 and ppa.effective_date between trunc(Add_months(p_date_earned,MOD(TO_NUMBER(TO_CHAR(p_date_earned,'MM')),2)-1),'MM')
124 and last_day(Add_months(p_date_earned,MOD(TO_NUMBER(TO_CHAR(p_date_earned,'MM')),2)));
125
126
127
128 -- Cursor to get the assignment_action_id and assignment_id
129 cursor csr_assignment_id (p_tax_unit_id pay_assignment_actions.TAX_UNIT_ID%TYPE
130 ,l_payroll_action_id pay_payroll_actions.PAYROLL_ACTION_ID%type) is
131 select ASSIGNMENT_ID
132 ,ASSIGNMENT_ACTION_ID
133 from pay_assignment_actions
134 where PAYROLL_ACTION_ID = l_payroll_action_id
135 and TAX_UNIT_ID = p_tax_unit_id;
136
137 -- Cursor to get the Local Unit and Tax Municipality
138
139 /*
140 cursor csr_lu_mu (l_assignment_id per_all_assignments_f.ASSIGNMENT_ID%type
141 ,l_assignment_action_id pay_assignment_actions.ASSIGNMENT_ACTION_ID %type
142 ,p_date_earned DATE ) is
143 select scl.segment2 local_unit_id
144 ,rr.jurisdiction_code tax_mun_id
145 from per_all_assignments_f asg
146 ,hr_soft_coding_keyflex scl
147 ,pay_run_results rr
148 ,pay_element_types_f pet
149 where asg.assignment_id = l_assignment_id
150 and p_date_earned between asg.EFFECTIVE_START_DATE and asg.EFFECTIVE_END_DATE
151 and asg.SOFT_CODING_KEYFLEX_ID = scl.SOFT_CODING_KEYFLEX_ID
152 and pet.element_name = 'Tax Deduction Base'
153 and pet.LEGISLATION_CODE = 'NO'
154 and p_date_earned between pet.EFFECTIVE_START_DATE and pet.EFFECTIVE_END_DATE
155 and pet.ELEMENT_TYPE_ID = rr.ELEMENT_TYPE_ID
156 and rr.ASSIGNMENT_ACTION_ID = l_assignment_action_id ;
157 */
158
159 /* Bug Fix: 5042861 - Modified cursor csr_lu_mu for performance fix to remove MERGE JOIN CARTESIAN*/
160 cursor csr_lu_mu (l_assignment_id per_all_assignments_f.ASSIGNMENT_ID%type
161 ,l_assignment_action_id pay_assignment_actions.ASSIGNMENT_ACTION_ID %type
162 ,p_date_earned DATE
163 ,l_ele_type_id NUMBER ) is
164 SELECT scl.segment2 local_unit_id
165 ,rr.jurisdiction_code tax_mun_id
166 FROM pay_assignment_actions assact
167 ,per_all_assignments_f asg
168 ,hr_soft_coding_keyflex scl
169 ,pay_run_results rr
170 WHERE assact.assignment_action_id = l_assignment_action_id
171 AND assact.assignment_id = asg.assignment_id
172 AND asg.assignment_id = l_assignment_id
173 AND p_date_earned BETWEEN asg.EFFECTIVE_START_DATE AND asg.EFFECTIVE_END_DATE
174 AND asg.SOFT_CODING_KEYFLEX_ID = scl.SOFT_CODING_KEYFLEX_ID
175 AND assact.assignment_action_id = rr.assignment_action_id
176 AND rr.ELEMENT_TYPE_ID = l_ele_type_id
177 AND rr.ASSIGNMENT_ACTION_ID = l_assignment_action_id ;
178
179
180 /* Bug No. : 4474602 and 4463136 */
181
182 -- cursor to get the Local Units (LU) and Legal Employers (LE) of the current assignments
183
184 cursor csr_get_lu_le
185 (p_payroll_action_id pay_payroll_actions.PAYROLL_ACTION_ID%type
186 ,p_date_earned DATE ) is
187
188 SELECT scl.segment2 loc_unit , assact.tax_unit_id leg_emp
189 FROM pay_assignment_actions assact
190 ,pay_run_types_f prt
191 ,per_all_assignments_f asg
192 ,hr_soft_coding_keyflex scl
193 WHERE assact.PAYROLL_ACTION_ID = p_payroll_action_id
194 and prt.LEGISLATION_CODE = 'NO'
195 and prt.RUN_TYPE_NAME = 'Employer Contributions'
196 and nvl(assact.RUN_TYPE_ID,-99) <> prt.RUN_TYPE_ID
197 and p_date_earned between prt.EFFECTIVE_START_DATE and prt.EFFECTIVE_END_DATE
198 and asg.assignment_id = assact.assignment_id
199 and asg.soft_coding_keyflex_id = scl.soft_coding_keyflex_id
200 group by scl.segment2 ,assact.tax_unit_id ;
201
202 /* Bug No. : 4474602 and 4463136 */
203
204 -- cursor to get Name of Organisation (LE or LU) from org_id
205
206 CURSOR csr_org_name (p_org_id HR_ORGANIZATION_UNITS.ORGANIZATION_ID%TYPE ,
207 p_bus_group_id HR_ORGANIZATION_UNITS.BUSINESS_GROUP_ID%TYPE ) IS
208 SELECT NAME
209 FROM HR_ORGANIZATION_UNITS
210 WHERE ORGANIZATION_ID = p_org_id
211 AND BUSINESS_GROUP_ID = p_bus_group_id ;
212
213 /* Bug No. : 4474602 and 4463136 */
214
215 -- cursor to get STATUS from Legal Employer
216 cursor get_le_status(p_tax_unit_id pay_assignment_actions.TAX_UNIT_ID%TYPE) IS
217 select ORG_INFORMATION3
218 from HR_ORGANIZATION_INFORMATION
219 where ORGANIZATION_ID = p_tax_unit_id
220 and org_information_context = 'NO_LEGAL_EMPLOYER_DETAILS';
221
222 /* Bug No. : 4474602 and 4463136 */
223
224 -- cursor to get the Exemption Limit of Legal Employer or Local Unit
225 cursor csr_get_exemption_limit (p_org_id NUMBER , p_date_earned DATE) is
226 select nvl(to_number(ORG_INFORMATION1),0)
227 from hr_organization_information
228 where ORGANIZATION_ID = p_org_id
229 and ORG_INFORMATION_CONTEXT = 'NO_NI_EXEMPTION_LIMIT'
230 and p_date_earned between fnd_date.canonical_to_date(ORG_INFORMATION2) and fnd_date.canonical_to_date(ORG_INFORMATION3);
231
232
233
234 ----------------------------------Function declarations----------------------------------------------
235
236
237 -- Function to calculate the Employer Contributions
238
239 FUNCTION GET_EMPLOYER_DEDUCTION
240 (p_tax_unit_id IN NUMBER
241 ,p_local_unit_id IN NUMBER
242 ,p_jurisdiction_code IN VARCHAR2
243 ,p_payroll_id IN NUMBER
244 ,p_payroll_action_id IN NUMBER
245 ,p_date_earned IN DATE
246 ,p_asg_act_id IN NUMBER
247 ,p_bus_group_id IN NUMBER
248 ,p_under_age_high_rate IN NUMBER
249 ,p_over_age_high_rate IN NUMBER
250 ,p_run_base OUT NOCOPY NUMBER
251 ,p_run_contribution OUT NOCOPY NUMBER
252 ,p_curr_exemption_limit_used OUT NOCOPY NUMBER) RETURN NUMBER ;
253
254
255 -- Function to get defined balance id
256
257 FUNCTION get_defined_balance_id
258 (p_balance_name IN VARCHAR2
259 ,p_dbi_suffix IN VARCHAR2 ) RETURN NUMBER ;
260
261
262 FUNCTION populate_tables
263 (p_tax_unit_id IN NUMBER
264 ,p_payroll_id IN NUMBER
265 ,p_date_earned IN DATE
266 ,g_lu_tab IN OUT NOCOPY PAY_NO_EMP_CONT.g_lu_tabtype
267 ,g_mu_tab IN OUT NOCOPY PAY_NO_EMP_CONT.g_mu_tabtype ) RETURN NUMBER ;
268
269
270 FUNCTION check_under_62
271 (p_assignment_action_id IN pay_assignment_actions.ASSIGNMENT_ACTION_ID%type
272 ,p_date_earned IN DATE) RETURN VARCHAR2 ;
273
274
275 -- function to get the lookup meaning
276 FUNCTION get_lookup_meaning (p_lookup_type IN varchar2,p_lookup_code IN varchar2) RETURN VARCHAR2 ;
277
278 -- Function to look up the corresponding cell number in he table g_tab_calc
279 FUNCTION lookup_cell
280 (g_tab_calc IN PAY_NO_EMP_CONT.g_tab_calc_tabtype
281 ,l_under_62 IN VARCHAR2
282 ,l_zone IN NUMBER ) RETURN NUMBER ;
283
284
285 -- NEW function for main calculation
286
287 FUNCTION ec_main_calculation
288
289 (g_tab_calc IN OUT NOCOPY PAY_NO_EMP_CONT.g_tab_calc_tabtype
290 ,g_tab_main IN OUT NOCOPY PAY_NO_EMP_CONT.g_tab_main_tabtype
291 ,p_tax_unit_id IN NUMBER
292 ,p_local_unit_id IN NUMBER
293 ,p_exemption_limit_used IN NUMBER
294 ,p_org_status IN VARCHAR2
295 ,p_bus_group_id IN NUMBER
296 ,p_date_earned IN DATE
297 ,p_under_age_high_rate IN NUMBER
298 ,p_over_age_high_rate IN NUMBER
299 ,l_curr_zone IN NUMBER
300 ,l_curr_under_62 IN VARCHAR2 ) RETURN NUMBER ;
301
302
303 -- function to get the ec rate
304
305 FUNCTION get_ec_rate
306
307 (p_under_62 IN VARCHAR2
308 ,p_zone IN NUMBER
309 ,p_under_limit IN VARCHAR2
310 ,p_org_status IN VARCHAR2
311 ,p_bus_group_id IN NUMBER
312 ,p_date_earned IN DATE
313 ,p_under_age_high_rate IN NUMBER
314 ,p_over_age_high_rate IN NUMBER ) RETURN NUMBER ;
315
316
317 -- function to display table values of g_tab_calc
318
319 FUNCTION display_table_calc
320 (g_tab_calc IN PAY_NO_EMP_CONT.g_tab_calc_tabtype ) RETURN NUMBER ;
321
322 -- changing the function to fetch the avg ni base rate for months and end of year averaging
323 -- function to get the average NI Base Rate Value
324
325 FUNCTION avg_ni_base_rate (p_date_earned IN DATE , p_bus_grp_id NUMBER ) RETURN NUMBER ;
326
327
328 -- Function to look up the corresponding cell number in he table g_tab_main
329
330 FUNCTION main_lookup_cell
331 (g_tab_main IN PAY_NO_EMP_CONT.g_tab_main_tabtype
332 ,start_main_index IN NUMBER
333 ,l_under_62 IN VARCHAR2
334 ,l_zone IN NUMBER ) RETURN NUMBER ;
335
336
337 /* Bug No. : 4474602 and 4463136 */
338
339 -- Function to check if any exemption limit error exists
340
341 FUNCTION chk_exemption_limit_err
342 (p_date_earned IN DATE
343 ,p_bus_grp_id IN NUMBER
344 ,p_payroll_action_id IN NUMBER ) RETURN NUMBER ;
345
346
347 -- function to get the employer contribution rate
348 FUNCTION get_emp_contr_rate
349
350 (p_bus_group_id IN NUMBER,
351 p_tax_unit_id IN NUMBER,
352 p_local_unit_id IN NUMBER,
353 p_jurisdiction_code IN VARCHAR2,
354 p_date_earned IN DATE,
355 p_asg_act_id IN NUMBER,
356 p_under_age_high_rate IN NUMBER,
357 p_over_age_high_rate IN NUMBER,
358 p_under_limit IN VARCHAR2) RETURN NUMBER;
359
360
361 --------------------------------------------------------------------------------
362
363
364
365 END PAY_NO_EMP_CONT ;
[Home] [Help]