1 package body PAY_NL_RULES as
2 /* $Header: pynlrule.pkb 120.3.12000000.3 2007/07/26 04:58:45 grchandr noship $ */
3 --
4 g_package varchar2(30) := 'pay_nl_rules';
5
6 -- +********************************************************************+
7 -- | PUBLIC FUNCTIONS |
8 -- +********************************************************************+
9 --
10 ---------------------------------------------------------------------------
11 -- Procedure: get_override_ctx_value
12 -- Procedure returns current system effective date in canonical format
13 ---------------------------------------------------------------------------
14 PROCEDURE get_override_ctx_value (p_retro_asg_action_id IN Number,
15 p_run_asg_action_id IN Number,
16 p_element_type_id IN Number,
17 p_retro_component_id IN Number,
18 p_override_date IN OUT NOCOPY varchar2)
19
20 IS
21
22 Cursor c_date(p_retro_asg_action_id Number)
23 is
24 select fnd_date.DATE_TO_CANONICAL(ppa.effective_date)
25 from pay_payroll_actions ppa,
26 pay_assignment_actions paa
27 where paa.assignment_action_id=p_retro_asg_action_id
28 and ppa.payroll_action_id=paa.payroll_action_id ;
29
30 l_override_date varchar2(30);
31
32
33
34
35 Begin
36
37 Open c_date(p_retro_asg_action_id);
38 Fetch c_date into l_override_date ;
39 Close c_date;
40
41 p_override_date:=l_override_date;
42
43 End get_override_ctx_value;
44
45 PROCEDURE get_retro_component_id (p_ee_id IN Number,
46 p_element_type_id IN Number default -1,
47 p_retro_component_id IN OUT NOCOPY Number)
48
49 is
50 cursor c_standard_component(cp_element_type_id in number)
51 is
52 select prc.retro_component_id
53 from
54 pay_retro_components prc,
55 pay_retro_component_usages prcu
56 where
57 prcu.creator_id=cp_element_type_id
58 and prc.retro_component_id=prcu.retro_component_id
59 and prc.legislation_code='NL'
60 and prc.component_name in ('Correction','Adjustment')
61 and prcu.default_component = 'Y';
62
63 cursor c_replacement_component(cp_element_type_id in number)
64 is
65 select prc.retro_component_id
66 from
67 pay_retro_components prc,
68 pay_retro_component_usages prcu
69 where
70 prcu.creator_id=cp_element_type_id
71 and prc.retro_component_id=prcu.retro_component_id
72 and prc.legislation_code='NL'
73 and prc.component_name='Replacement';
74
75 Cursor c_retro_method(p_business_group_id in number)
76 is
77 Select hoi.org_information2
78 from hr_organization_information hoi
79 where hoi.org_information_context = 'NL_BG_INFO'
80 and hoi.organization_id = p_business_group_id;
81
82
83 cursor c_bg_id(p_ee_id in number)
84 is
85 select distinct paa.business_group_id
86 from per_all_assignments_f paa
87 ,pay_element_entries_f pee
88 where pee.element_entry_id=p_ee_id
89 and pee.assignment_id=paa.assignment_id;
90
91
92 l_return_date date;
93 l_period_date date;
94 l_retro_method varchar2(10);
95 l_business_group_id number;
96
97 Begin
98
99 Open c_bg_id(p_ee_id);
100 Fetch c_bg_id into l_business_group_id;
101 Close c_bg_id;
102
103 Open c_retro_method(l_business_group_id);
104 Fetch c_retro_method into l_retro_method;
105 Close c_retro_method;
106
107 if (l_retro_method ='Y') /* or if the BG wants to override the replacement method to standard*/
108 then
109
110 OPEN c_standard_component(p_element_type_id);
111 FETCH c_standard_component into p_retro_component_id;
112 CLOSE c_standard_component;
113
114 Else
115
116 OPEN c_replacement_component(p_element_type_id);
117 FETCH c_replacement_component into p_retro_component_id;
118 CLOSE c_replacement_component;
119
120
121 end if;
122
123 end get_retro_component_id;
124
125
126 ---------------------------------------------------------------------------
127 -- Procedure : get_asg_process_group
128 -- This procedure gives the process group name for an assignment
129 ---------------------------------------------------------------------------
130 Procedure get_asg_process_group(p_assignment_id IN Number,
131 p_effective_start_date IN Date,
132 p_effective_end_date IN Date,
133 p_process_group_name OUT NOCOPY Varchar2) IS
134
135 CURSOR csr_asg_process_group IS
136 SELECT DECODE(pay_nl_rules.get_object_group_type
137 ,'EMPLOYER'
138 ,prl_information1
139 ,substr(pap.payroll_name,1,30))
140 FROM per_all_assignments_f paa
141 ,pay_all_payrolls_f pap
142 WHERE paa.assignment_id = p_assignment_id
143 AND paa.payroll_id = pap.payroll_id
144 AND paa.effective_start_date <= p_effective_end_date
145 AND paa.effective_end_date >= p_effective_start_date
146 AND pap.effective_start_date <= p_effective_end_date
147 AND pap.effective_end_date >= p_effective_start_date
148 ORDER BY paa.effective_end_date DESC ,pap.effective_end_date DESC;
149 --
150 Begin
151 --
152 open csr_asg_process_group;
153 fetch csr_asg_process_group into p_process_group_name;
154 close csr_asg_process_group;
155 --
156 End get_asg_process_group ;
157
158 ---------------------------------------------------------------------------
159 -- Procedure : get_main_tax_unit_id
160 -- This procedure gives the tax unit id for an assignment
161 ---------------------------------------------------------------------------
162 PROCEDURE get_main_tax_unit_id(p_assignment_id IN NUMBER,
163 p_effective_date IN DATE,
164 p_tax_unit_id IN OUT NOCOPY NUMBER) IS
165 --
166 CURSOR cur_tax_unit_id IS
167 select NVL(asg.establishment_id,prl_information1) from
168 per_all_assignments_f asg,
169 pay_all_payrolls_f pap
170 where assignment_id = p_assignment_id
171 and asg.payroll_id = pap.payroll_id
172 and p_effective_date between asg.effective_start_date and asg.effective_end_date
173 and p_effective_date between pap.effective_start_date and pap.effective_end_date;
174 --
175 BEGIN
176 --
177 open cur_tax_unit_id;
178 fetch cur_tax_unit_id into p_tax_unit_id;
179 close cur_tax_unit_id;
180 --
181 END get_main_tax_unit_id;
182 --
183 ---------------------------------------------------------------------------
184 -- Function : get_object_group_type
185 -- This Function returns the type, the object group is based on
186 -- 'PAYROLL' if based on payroll_id
187 -- 'EMPLOYER' if based on HR Organization
188 ---------------------------------------------------------------------------
189 FUNCTION get_object_group_type RETURN VARCHAR2 IS
190 --
191 CURSOR get_type IS
192 SELECT org_information6
193 FROM hr_organization_information
194 WHERE organization_id = fnd_profile.value('PER_BUSINESS_GROUP_ID')
195 AND org_information_context = 'NL_BG_INFO';
196 --
197 l_type VARCHAR2(20);
198 --
199 BEGIN
200 --
201 OPEN get_type;
202 FETCH get_type INTO l_type;
203 CLOSE get_type;
204 --
205 RETURN NVL(l_type,'PAYROLL');
206 --
207 --RETURN 'PAYROLL' ;
208 --RETURN 'EMPLOYER' ;
209 END get_object_group_type;
210
211 end PAY_NL_RULES;
[Home] [Help]