[Home] [Help]
PACKAGE BODY: APPS.PAY_AU_TAX_INFO_PKG
Source
1 package body pay_au_tax_info_pkg as
2 /* $Header: pyautinf.pkb 120.2.12010000.1 2008/10/28 01:53:40 keyazawa noship $ */
3 --
4 c_package constant varchar2(31) := 'pay_au_tax_info_pkg.';
5 --
6 c_legislation_code per_business_groups_perf.legislation_code%type := 'AU';
7 --
8 c_tax_info_elm constant pay_element_types_f.element_name%type := 'Tax Information';
9 --
10 c_iv_upper varchar2(1) := 'N';
11 --
12 type t_input_value_id_rec is record(
13 input_value_id pay_input_values_f.input_value_id%type,
14 name pay_input_values_f.name%type);
15 type t_input_value_id_tbl is table of t_input_value_id_rec index by binary_integer;
16 --
17 g_input_value_id_tbl t_input_value_id_tbl;
18 g_tax_info_elm_id number;
19 --
20 g_debug boolean := hr_utility.debug_enabled;
21 --
22 -- -------------------------------------------------------------------------
23 -- get_element_type_id
24 -- -------------------------------------------------------------------------
25 function get_element_type_id(
26 p_element_name in varchar2,
27 p_element_type_id in number)
28 return number
29 is
30 --
31 cursor csr_element_type_id
32 is
33 select element_type_id
34 from pay_element_types_f
35 where element_name = p_element_name
36 and legislation_code = c_legislation_code;
37 --
38 l_element_type_id number := p_element_type_id;
39 --
40 begin
41 --
42 if l_element_type_id is null then
43 --
44 open csr_element_type_id;
45 fetch csr_element_type_id into l_element_type_id;
46 close csr_element_type_id;
47 --
48 end if;
49 --
50 return l_element_type_id;
51 end get_element_type_id;
52 --
53 -- -------------------------------------------------------------------------
54 -- get_input_value_id
55 -- -------------------------------------------------------------------------
56 function get_input_value_id(
57 p_element_type_id in number,
58 p_input_value_name in varchar2,
59 p_input_value_id in number)
60 return number
61 is
62 --
63 cursor csr_input_value_id
64 is
65 select input_value_id,
66 decode(c_iv_upper,'Y',upper(name),name) iv_name
67 from pay_input_values_f
68 where element_type_id = p_element_type_id
69 and legislation_code = c_legislation_code;
70 --
71 l_iv_tbl_name pay_input_values_f.name%type;
72 l_iv_tbl_id pay_input_values_f.input_value_id%type;
73 --
74 l_iv_tbl_cnt number := 0;
75 l_input_value_id number := p_input_value_id;
76 --
77 begin
78 --
79 if l_input_value_id is null then
80 --
81 if g_input_value_id_tbl.count = 0 then
82 --
83 open csr_input_value_id;
84 loop
85 --
86 fetch csr_input_value_id into
87 l_iv_tbl_id,
88 l_iv_tbl_name;
89 exit when csr_input_value_id%notfound;
90 --
91 g_input_value_id_tbl(l_iv_tbl_cnt).name := l_iv_tbl_name;
92 g_input_value_id_tbl(l_iv_tbl_cnt).input_value_id := l_iv_tbl_id;
93 --
94 l_iv_tbl_cnt := l_iv_tbl_cnt + 1;
95 --
96 end loop;
97 close csr_input_value_id;
98 --
99 end if;
100 --
101 for i in 0..g_input_value_id_tbl.count loop
102 --
103 if g_input_value_id_tbl(i).name = p_input_value_name then
104 --
105 l_input_value_id := g_input_value_id_tbl(i).input_value_id;
106 exit;
107 --
108 end if;
109 --
110 end loop;
111 --
112 end if;
113 --
114 return l_input_value_id;
115 end get_input_value_id;
116 --
117 -- -------------------------------------------------------------------------
118 -- set_eev_upd_mode
119 -- -------------------------------------------------------------------------
120 procedure set_eev_upd_mode(
121 p_assignment_id in number,
122 p_session_date in date,
123 p_scl_upd_mode in varchar2,
124 p_scl_upd_esd in date,
125 p_eev_upd_esd in date,
126 p_update_mode out nocopy varchar2,
127 p_effective_date out nocopy date,
128 p_warning out nocopy varchar2)
129 is
130 --
131 l_proc varchar2(80) := c_package||'set_eev_upd_mode';
132 --
133 l_update_mode varchar2(60);
134 l_effective_date date;
135 l_warning fnd_new_messages.message_name%type;
136 --
137 l_tax_info_fut_ee_id number;
138 --
139 cursor csr_tax_info_fut_ee
140 is
141 select /*+ ORDERED
142 USE_NL(PA,PEL,PEE)
143 INDEX(PA PER_ASSIGNMENTS_F_PK)
144 INDEX(PEL PAY_ELEMENT_LINKS_F_N7)
145 INDEX(PEE PAY_ELEMENT_ENTRIES_F_N51) */
146 pee.element_entry_id
147 from per_all_assignments_f pa,
148 pay_element_links_f pel,
149 pay_element_entries_f pee
150 where pa.assignment_id = p_assignment_id
151 and l_effective_date
152 between pa.effective_start_date and pa.effective_end_date
153 and pel.element_type_id = g_tax_info_elm_id
154 and l_effective_date
155 between pel.effective_start_date and pel.effective_end_date
156 and pel.business_group_id + 0 = pa.business_group_id
157 and pee.assignment_id = pa.assignment_id
158 and pee.element_link_id = pel.element_link_id
159 and pee.effective_start_date > l_effective_date
160 and pee.effective_end_date > l_effective_date;
161 --
162 begin
163 --
164 if g_debug then
165 hr_utility.set_location(l_proc,0);
166 end if;
167 --
168 g_tax_info_elm_id := get_element_type_id(c_tax_info_elm, g_tax_info_elm_id);
169 --
170 if g_debug then
171 hr_utility.set_location(l_proc,10);
172 hr_utility.trace('g_tax_info_elm_id : '||to_char(g_tax_info_elm_id));
173 end if;
174 --
175 if p_scl_upd_mode = hr_api.g_correction then
176 --
177 l_effective_date := p_scl_upd_esd;
178 --
179 else
180 --
181 l_effective_date := p_session_date;
182 --
183 end if;
184 --
185 if g_debug then
186 hr_utility.set_location(l_proc,20);
187 hr_utility.trace('l_effective_date : '||to_char(l_effective_date,'YYYY/MM/DD'));
188 end if;
189 --
190 open csr_tax_info_fut_ee;
191 fetch csr_tax_info_fut_ee into l_tax_info_fut_ee_id;
192 close csr_tax_info_fut_ee;
193 --
194 if g_debug then
195 hr_utility.set_location(l_proc,30);
196 hr_utility.trace('l_tax_info_fut_ee_id : '||to_char(l_tax_info_fut_ee_id));
197 end if;
198 --
199 -- no support alternative hr_api.g_update_override and hr_api.g_correction option
200 -- because double asking update option on assignment window (for assignment change and eev change)
201 -- will be complicated use, which window is for assignment change or for eev change.
202 --
203 if l_tax_info_fut_ee_id is not null then
204 --
205 if trunc(l_effective_date,'DD') = trunc(p_eev_upd_esd,'DD') then
206 --
207 l_update_mode := hr_api.g_correction;
208 l_warning := 'HR_AU_TAX_SCALE_SYNC_WNG';
209 --
210 else
211 --
212 if p_scl_upd_mode = hr_api.g_update_override then
213 --
214 -- no support hr_api.g_update_override
215 -- (use hr_api.g_update_change_insert instead, plus message)
216 --
217 --l_update_mode := hr_api.g_update_override;
218 --
219 l_update_mode := hr_api.g_update_change_insert;
220 l_warning := 'HR_AU_TAX_SCALE_SYNC_WNG';
221 --
222 else
223 --
224 -- show warning in case hr_api.g_update_change_insert is set.
225 -- no support hr_api.g_update_override
226 --
227 -- create history to sync with scl DateTrack
228 -- no support hr_api.g_correction by customer option
229 --
230 l_update_mode := hr_api.g_update_change_insert;
231 l_warning := 'HR_AU_TAX_SCALE_SYNC_WNG';
232 --
233 end if;
234 --
235 end if;
236 --
237 else
238 --
239 if trunc(l_effective_date,'DD') = trunc(p_eev_upd_esd,'DD') then
240 --
241 l_update_mode := hr_api.g_correction;
242 --
243 else
244 --
245 -- create history to sync with scl DateTrack
246 -- no support hr_api.g_correction by customer option
247 --
248 l_update_mode := hr_api.g_update;
249 --
250 end if;
251 --
252 end if;
253 --
254 if g_debug then
255 hr_utility.set_location(l_proc,40);
256 hr_utility.trace('l_update_mode : '||l_update_mode);
257 hr_utility.trace('l_warning : '||l_warning);
258 end if;
259 --
260 p_update_mode := l_update_mode;
261 p_effective_date := l_effective_date;
262 p_warning := l_warning;
263 --
264 if g_debug then
265 hr_utility.set_location(l_proc,1000);
266 end if;
267 --
268 end set_eev_upd_mode;
269 --
270 end pay_au_tax_info_pkg;