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