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