[Home] [Help]
PACKAGE BODY: APPS.PAY_JP_DEF_ARCHIVE
Source
1 package body pay_jp_def_archive as
2 /* $Header: pyjpdefc.pkb 120.4 2010/11/01 13:04:25 keyazawa ship $ */
3 --
4 -- Constants
5 --
6 c_package constant varchar2(31) := 'pay_jp_def_archive.';
7 c_organization_iv_name constant pay_input_values_f.name%type := 'COM_ITX_INFO_WITHHOLD_AGENT_ENTRY_VALUE';
8 c_tax_type_iv_name constant pay_input_values_f.name%type := 'COM_ITX_INFO_ITX_TYPE_ENTRY_VALUE';
9 --
10 -- Global Variables
11 --
12 g_payroll_action_id number;
13 g_business_group_id number;
14 g_effective_date date;
15 g_legislation_code per_business_groups_perf.legislation_code%type;
16 g_payroll_id number;
17 g_organization_id number;
18 g_process_assignments_flag varchar2(1);
19 g_archive_default_flag varchar2(1);
20 --
21 -- Procedures for ARCHIVE process
22 --
23 -- |-------------------------------------------------------------------|
24 -- |----------------------< initialization_code >----------------------|
25 -- |-------------------------------------------------------------------|
26 procedure initialization_code(p_payroll_action_id in number)
27 is
28 --
29 c_proc constant varchar2(61) := c_package || 'initialization_code';
30 --
31 cursor csr
32 is
33 select ppa.business_group_id,
34 ppa.effective_date,
35 ppa.legislative_parameters,
36 bg.legislation_code
37 from per_business_groups_perf bg,
38 pay_payroll_actions ppa
39 where ppa.payroll_action_id = p_payroll_action_id
40 and bg.business_group_id = ppa.business_group_id;
41 --
42 l_rec csr%rowtype;
43 --
44 begin
45 --
46 hr_utility.set_location('Entering : ' || c_proc, 10);
47 --
48 if g_payroll_action_id is null
49 or g_payroll_action_id <> p_payroll_action_id then
50 --
51 hr_utility.trace('cache not available');
52 --
53 open csr;
54 fetch csr into l_rec;
55 if csr%notfound then
56 close csr;
57 fnd_message.set_name('PAY', 'PAY_34985_INVALID_PAY_ACTION');
58 fnd_message.raise_error;
59 end if;
60 close csr;
61 --
62 g_payroll_action_id := p_payroll_action_id;
63 g_effective_date := l_rec.effective_date;
64 g_business_group_id := l_rec.business_group_id;
65 g_legislation_code := l_rec.legislation_code;
66 g_payroll_id := fnd_number.canonical_to_number(pay_core_utils.get_parameter('PAYROLL_ID', l_rec.legislative_parameters));
67 g_organization_id := fnd_number.canonical_to_number(pay_core_utils.get_parameter('ORGANIZATION_ID', l_rec.legislative_parameters));
68 g_process_assignments_flag := pay_core_utils.get_parameter('PROCESS_ASSIGNMENTS_FLAG', l_rec.legislative_parameters);
69 g_archive_default_flag := pay_core_utils.get_parameter('ARCHIVE_DEFAULT_FLAG',l_rec.legislative_parameters);
70 --
71 end if;
72 --
73 hr_utility.trace('payroll_action_id : ' || g_payroll_action_id);
74 hr_utility.trace('business_group_id : ' || g_business_group_id);
75 hr_utility.trace('effective_date : ' || g_effective_date);
76 hr_utility.trace('legislation_code : ' || g_legislation_code);
77 hr_utility.trace('payroll_id : ' || g_payroll_id);
78 hr_utility.trace('organization_id : ' || g_organization_id);
79 hr_utility.trace('process_assignments_flag : ' || g_process_assignments_flag);
80 hr_utility.trace('archive_default_flag : '||g_archive_default_flag);
81 --
82 hr_utility.set_location('Leaving : ' || c_proc, 20);
83 --
84 end initialization_code;
85 -- |-------------------------------------------------------------------|
86 -- |-------------------------< archive_pact >--------------------------|
87 -- |-------------------------------------------------------------------|
88 procedure archive_pact(p_payroll_action_id in number)
89 is
90 c_proc constant varchar2(61) := c_package || 'archive_pact';
91 l_exists varchar2(1);
92 l_action_information_id number;
93 l_object_version_number number;
94 cursor csr_exists is
95 select 'Y'
96 from pay_jp_def_pact_v
97 where payroll_action_id = p_payroll_action_id;
98 cursor csr_org is
99 select hoi2.org_information1 tax_office_name,
100 hoi1.org_information1 salary_payer_name,
101 hoi1.org_information6 || hoi1.org_information7 || hoi1.org_information8 salary_payer_address
102 from hr_organization_information hoi2,
103 hr_organization_information hoi1,
104 hr_all_organization_units hou
105 where hou.organization_id = g_organization_id
106 and hoi1.organization_id(+) = hou.organization_id
107 and hoi1.org_information_context(+) = 'JP_TAX_SWOT_INFO'
108 and hoi2.organization_id(+) = hou.organization_id
109 and hoi2.org_information_context(+) = 'JP_ITAX_WITHHELD_INFO';
110 l_org_rec csr_org%rowtype;
111 begin
112 hr_utility.set_location('Entering : ' || c_proc, 10);
113 --
114 -- Archive only if PA level is not archived.
115 -- This is to avoid the issue of Mark for Retry which does not kick range_code.
116 --
117 open csr_exists;
118 fetch csr_exists into l_exists;
119 if csr_exists%notfound then
120 open csr_org;
121 fetch csr_org into l_org_rec;
122 if csr_org%notfound then
123 fnd_message.set_name('PAY', 'PAY_JP_INVALID_SWOT');
124 fnd_message.raise_error;
125 end if;
126 close csr_org;
127 --
128 hr_utility.trace('tax_office_name : ' || l_org_rec.tax_office_name);
129 hr_utility.trace('salary_payer_name : ' || l_org_rec.salary_payer_name);
130 hr_utility.trace('salary_payer_address : ' || l_org_rec.salary_payer_address);
131 --
132 pay_jp_def_api.create_pact(
133 P_VALIDATE => false,
134 P_PAYROLL_ACTION_ID => p_payroll_action_id,
135 P_EFFECTIVE_DATE => g_effective_date,
136 P_PAYROLL_ID => g_payroll_id,
137 P_ORGANIZATION_ID => g_organization_id,
138 P_SUBMISSION_PERIOD_STATUS => 'C',
139 P_SUBMISSION_START_DATE => null,
140 P_SUBMISSION_END_DATE => null,
141 P_TAX_OFFICE_NAME => l_org_rec.tax_office_name,
142 P_SALARY_PAYER_NAME => l_org_rec.salary_payer_name,
143 P_SALARY_PAYER_ADDRESS => l_org_rec.salary_payer_address,
144 P_ACTION_INFORMATION_ID => l_action_information_id,
145 P_OBJECT_VERSION_NUMBER => l_object_version_number);
146 end if;
147 close csr_exists;
148 --
149 hr_utility.set_location('Leaving : ' || c_proc, 20);
150 end archive_pact;
151 -- |-------------------------------------------------------------------|
152 -- |--------------------------< range_code >---------------------------|
153 -- |-------------------------------------------------------------------|
154 procedure range_code(
155 p_payroll_action_id in number,
156 p_sqlstr out nocopy varchar2)
157 is
158 c_proc constant varchar2(61) := c_package || 'range_code';
159 begin
160 hr_utility.set_location('Entering : ' || c_proc, 10);
161 --
162 initialization_code(p_payroll_action_id);
163 --
164 archive_pact(p_payroll_action_id);
165 --
166 -- When "process assignments flag" is set to no, no assignment actions are created.
167 --
168 if g_process_assignments_flag = 'N' then
169 p_sqlstr := 'select 1 from dual where :payroll_action_id < 0';
170 else
171 p_sqlstr :=
172 'select distinct per.person_id
173 from per_all_people_f per,
174 pay_payroll_actions ppa
175 where ppa.payroll_action_id = :payroll_action_id
176 and ppa.business_group_id + 0 = per.business_group_id
177 order by per.person_id';
178 end if;
179 --
180 hr_utility.set_location('Leaving : ' || c_proc, 20);
181 end range_code;
182 -- |-------------------------------------------------------------------|
183 -- |--------------------< assignment_action_code >---------------------|
184 -- |-------------------------------------------------------------------|
185 procedure assignment_action_code(
186 p_payroll_action_id in number,
187 p_start_person_id in number,
188 p_end_person_id in number,
189 p_chunk_number in number)
190 is
191 c_proc constant varchar2(61) := c_package || 'assignment_action_code';
192 l_tax_type pay_element_entry_values_f.screen_entry_value%type;
193 l_organization_id number;
194 l_assignment_action_id number;
195 --
196 cursor csr_asg is
197 select asg.assignment_id
198 from per_all_assignments_f asg,
199 per_periods_of_service pds
200 where pds.person_id
201 between p_start_person_id and p_end_person_id
202 and pds.business_group_id + 0 = g_business_group_id
203 and g_effective_date
204 between pds.date_start and nvl(pds.final_process_date, g_effective_date)
205 and asg.period_of_service_id = pds.period_of_service_id
206 and asg.primary_flag = 'Y' /*Added by JSAJJA, as per Bug 8435426*/
207 and g_effective_date
208 between asg.effective_start_date and asg.effective_end_date
209 and asg.payroll_id + 0 = g_payroll_id;
210 begin
211 hr_utility.set_location('Entering : ' || c_proc, 10);
212 --
213 -- Reset global variable in case of multiple threads.
214 initialization_code(p_payroll_action_id);
215 --
216 for l_asg_rec in csr_asg loop
217 pay_balance_pkg.set_context('DATE_EARNED', fnd_date.date_to_canonical(g_effective_date));
218 pay_balance_pkg.set_context('ASSIGNMENT_ID', fnd_number.number_to_canonical(l_asg_rec.assignment_id));
219 l_organization_id := pay_balance_pkg.run_db_item(c_organization_iv_name, g_business_group_id, g_legislation_code);
220 --
221 if l_organization_id = g_organization_id then
222 l_tax_type := pay_balance_pkg.run_db_item(c_tax_type_iv_name, g_business_group_id, g_legislation_code);
223 if l_tax_type in ('M_KOU', 'D_KOU') then
224 hr_utility.trace('assignment_id : ' || l_asg_rec.assignment_id);
225 --
226 select pay_assignment_actions_s.nextval
227 into l_assignment_action_id
228 from dual;
229 --
230 hr_nonrun_asact.insact(
231 lockingactid => l_assignment_action_id,
232 assignid => l_asg_rec.assignment_id,
233 pactid => p_payroll_action_id,
234 chunk => p_chunk_number,
235 greid => null);
236 end if;
237 end if;
238 end loop;
239 --
240 hr_utility.set_location('Leaving : ' || c_proc, 20);
241 end assignment_action_code;
242 --
243 -- |-------------------------------------------------------------------|
244 -- |------------------------< archive_assact >-------------------------|
245 -- |-------------------------------------------------------------------|
246 procedure archive_assact(
247 p_assignment_action_id in number,
248 p_effective_date in date)
249 is
250 c_proc constant varchar2(61) := c_package || 'archive_assact';
251 l_assignment_id number;
252 l_tax_type pay_element_entry_values_f.screen_entry_value%type;
253 l_action_information_id number;
254 l_object_version_number number;
255 begin
256 hr_utility.set_location('Entering : ' || c_proc, 10);
257 --
258 select assignment_id
259 into l_assignment_id
260 from pay_assignment_actions
261 where assignment_action_id = p_assignment_action_id;
262 --
263 -- Derive Tax Type input value
264 --
265 pay_balance_pkg.set_context('DATE_EARNED', fnd_date.date_to_canonical(p_effective_date));
266 pay_balance_pkg.set_context('ASSIGNMENT_ID', fnd_number.number_to_canonical(l_assignment_id));
267 l_tax_type := pay_balance_pkg.run_db_item(c_tax_type_iv_name, g_business_group_id, g_legislation_code);
268 --
269 if l_tax_type not in ('M_KOU', 'D_KOU') then
270 fnd_message.set_name('PAY', 'PAY_JP_INVALID_TAX_TYPE');
271 fnd_message.raise_error;
272 else
273 pay_jp_def_api.create_assact(
274 P_VALIDATE => false,
275 P_ASSIGNMENT_ACTION_ID => p_assignment_action_id,
276 P_EFFECTIVE_DATE => p_effective_date,
277 P_ASSIGNMENT_ID => l_assignment_id,
278 P_TAX_TYPE => l_tax_type,
279 P_TRANSACTION_STATUS => 'U',
280 P_FINALIZED_DATE => null,
281 P_FINALIZED_BY => null,
282 P_USER_COMMENTS => null,
283 P_ADMIN_COMMENTS => null,
284 P_TRANSFER_STATUS => 'U',
285 P_EXPIRY_DATE => null,
286 P_ACTION_INFORMATION_ID => l_action_information_id,
287 P_OBJECT_VERSION_NUMBER => l_object_version_number);
288 --
289 if g_archive_default_flag = 'Y' then
290 --
291 if pay_jp_def_ss.g_business_group_id is null
292 or pay_jp_def_ss.g_business_group_id <> g_business_group_id then
293 --
294 pay_jp_def_ss.g_business_group_id := g_business_group_id;
295 --
296 end if;
297 --
298 pay_jp_def_ss.archive_default(p_assignment_action_id,l_assignment_id,p_effective_date);
299 --
300 pay_jp_def_api.update_assact(
301 p_validate => false,
302 p_action_information_id => l_action_information_id,
303 p_object_version_number => l_object_version_number,
304 p_transaction_status => 'N');
305 --
306 end if;
307 --
308 end if;
309 --
310 hr_utility.set_location('Leaving : ' || c_proc, 20);
311 end archive_assact;
312 -- |-------------------------------------------------------------------|
313 -- |---------------------< deinitialization_code >---------------------|
314 -- |-------------------------------------------------------------------|
315 procedure deinitialization_code(p_payroll_action_id in number)
316 is
317 c_proc constant varchar2(61) := c_package || 'deinitialization_code';
318 begin
319 hr_utility.set_location('Entering : ' || c_proc, 10);
320 --
321 -- invoke in case of mark for retry.
322 initialization_code(p_payroll_action_id);
323 --
324 archive_pact(p_payroll_action_id);
325 --
326 hr_utility.set_location('Leaving : ' || c_proc, 20);
327 end deinitialization_code;
328 --
329 end pay_jp_def_archive;