[Home] [Help]
PACKAGE BODY: APPS.PAY_JP_PRE_TAX_ARCHIVE
Source
1 PACKAGE BODY PAY_JP_PRE_TAX_ARCHIVE AS
2 /* $Header: pyjppretaxarch.pkb 120.3.12020000.4 2012/08/06 11:24:31 mdubasi ship $ */
3 --
4 -- Constants
5 --
6 c_package constant varchar2(31) := 'pay_jp_pre_tax_archive.';
7 --
8 -- Global Variables
9 --
10 g_payroll_action_id pay_payroll_actions.payroll_action_id%TYPE;
11 g_business_group_id pay_payroll_actions.business_group_id%TYPE;
12 g_start_date pay_payroll_actions.effective_date%TYPE;
13 g_effective_date pay_payroll_actions.effective_date%TYPE;
14 g_payroll_id pay_payroll_actions.payroll_id%TYPE;
15 g_consolidation_set_id pay_payroll_actions.consolidation_set_id%TYPE;
16 g_legislation_code per_business_groups.legislation_code%TYPE;
17 --
18 -- |-------------------------------------------------------------------|
19 -- |-----------------------< remove_interlocks >-----------------------|
20 -- |-------------------------------------------------------------------|
21 procedure remove_interlocks(p_payroll_action_id in pay_payroll_actions.payroll_action_id%type)
22 is
23 --
24 begin
25 --
26 delete pay_action_interlocks
27 where locking_action_id in (
28 select assignment_action_id
29 from pay_assignment_actions
30 where payroll_action_id = p_payroll_action_id);
31 --
32 end remove_interlocks;
33 --
34 -- Procedures for ARCHIVE process
35 --
36 -- |-------------------------------------------------------------------|
37 -- |----------------------< initialization_code >----------------------|
38 -- |-------------------------------------------------------------------|
39 procedure initialization_code(p_payroll_action_id in pay_payroll_actions.payroll_action_id%TYPE)
40 is
41 c_proc constant varchar2(61) := c_package || 'initialization_code';
42 cursor csr is
43 select business_group_id,
44 start_date,
45 effective_date,
46 legislative_parameters
47 from pay_payroll_actions
48 where payroll_action_id = p_payroll_action_id;
49 l_rec csr%rowtype;
50 begin
51 hr_utility.set_location('Entering: ' || c_proc, 10);
52 --
53 if g_payroll_action_id is null
54 or g_payroll_action_id <> p_payroll_action_id then
55 hr_utility.trace('cache not available');
56 --
57 open csr;
58 fetch csr into l_rec;
59 if csr%notfound then
60 close csr;
61 fnd_message.set_name('PAY', 'PAY_34985_INVALID_PAY_ACTION');
62 fnd_message.raise_error;
63 end if;
64 close csr;
65 --
66 g_payroll_action_id := p_payroll_action_id;
67 g_business_group_id := l_rec.business_group_id;
68
69 g_legislation_code := hr_jp_id_pkg.legislation_code(g_business_group_id);
70 if (g_legislation_code is NULL) then
71 fnd_message.set_name(800,'HR_51255_PYP_INVALID_BUS_GROUP');
72 fnd_message.raise_error;
73 end if;
74
75 g_start_date := l_rec.start_date;
76 g_effective_date := l_rec.effective_date;
77 g_payroll_id := fnd_number.canonical_to_number(pay_core_utils.get_parameter('PAYROLL_ID', l_rec.legislative_parameters));
78 g_consolidation_set_id := fnd_number.canonical_to_number(pay_core_utils.get_parameter('CONSOLIDATION_SET_ID', l_rec.legislative_parameters));
79 --
80 end if;
81 --
82
83 /* Bug 13974619 */
84 if g_payroll_id IS NOT NULL then
85 update pay_payroll_actions
86 set payroll_id=g_payroll_id
87 where payroll_action_id=g_payroll_action_id;
88 end if;
89
90 --
91 hr_utility.trace('payroll_action_id : ' || g_payroll_action_id);
92 hr_utility.trace('business_group_id : ' || g_business_group_id);
93 hr_utility.trace('start_date : ' || g_start_date);
94 hr_utility.trace('effective_date : ' || g_effective_date);
95 hr_utility.trace('payroll_id : ' || g_payroll_id);
96 hr_utility.trace('consolidation_set_id : ' || g_consolidation_set_id);
97 --
98 hr_utility.set_location('Leaving: ' || c_proc, 20);
99 end initialization_code;
100 -- |-------------------------------------------------------------------|
101 -- |--------------------------< range_code >---------------------------|
102 -- |-------------------------------------------------------------------|
103 procedure range_code(
104 p_payroll_action_id in pay_payroll_actions.payroll_action_id%TYPE,
105 p_sqlstr out nocopy varchar2)
106 is
107 c_proc constant varchar2(61) := c_package || 'range_code';
108 begin
109 hr_utility.set_location('Entering: ' || c_proc, 10);
110 --
111 -- This needs to be called for the case of single-threaded.
112 --
113 initialization_code(p_payroll_action_id);
114 --
115 p_sqlstr :=
116 'select distinct per.person_id
117 from per_all_people_f per,
118 pay_payroll_actions ppa
119 where ppa.payroll_action_id = :payroll_action_id
120 and ppa.business_group_id + 0 = per.business_group_id
121 order by per.person_id';
122 --
126 -- |--------------------< assignment_action_code >---------------------|
123 hr_utility.set_location('Leaving: ' || c_proc, 20);
124 end range_code;
125 -- |-------------------------------------------------------------------|
127 -- |-------------------------------------------------------------------|
128 procedure assignment_action_code(
129 p_payroll_action_id in pay_payroll_actions.payroll_action_id%TYPE,
130 p_start_person_id in number,
131 p_end_person_id in number,
132 p_chunk_number in pay_assignment_actions.chunk_number%TYPE)
133 is
134 c_proc constant varchar2(61) := c_package || 'assignment_action_code';
135 l_locking_action_id number;
136 l_assignment_id number;
137 --
138 cursor csr_assact(
139 p_business_group_id pay_payroll_actions.business_group_id%TYPE,
140 p_consolidation_set_id pay_payroll_actions.consolidation_set_id%TYPE,
141 p_start_date pay_payroll_actions.effective_date%TYPE,
142 p_effective_date pay_payroll_actions.effective_date%TYPE,
143 p_payroll_id pay_payroll_actions.payroll_id%TYPE) is
144
145 select /* Removed the hint as per Bug# 4767118 */
146 paa.assignment_id,
147 paa.assignment_action_id
148 from (
149 select /* Removed the hint as per Bug# 4767118 */
150 distinct asg.assignment_id
151 from per_periods_of_service pds,
152 per_all_assignments_f asg
153 where pds.person_id
154 between p_start_person_id and p_end_person_id
155 and pds.business_group_id + 0 = p_business_group_id
156 and asg.period_of_service_id = pds.period_of_service_id
157 ) v,
158 pay_assignment_actions paa,
159 pay_payroll_actions ppa
160 where paa.assignment_id = v.assignment_id
161 and paa.action_status = 'C'
162 and ppa.payroll_action_id = paa.payroll_action_id
163 and (ppa.consolidation_set_id = p_consolidation_set_id or p_consolidation_set_id is null)
164 and ppa.effective_date
165 between nvl(p_start_date, ppa.effective_date) and p_effective_date
166 and (ppa.payroll_id = p_payroll_id or p_payroll_id is null)
167 and ppa.action_type in ('R', 'Q', 'B', 'I')
168 and not exists(
169 select /*+ ORDERED
170 USE_NL(PAAA PPAA)
171 INDEX(XPAI PAY_ACTION_INTERLOCKS_FK2)
172 INDEX(XPAA PAY_ASSIGNMENT_ACTIONS_PK)
173 INDEX(XPPA PAY_PAYROLL_ACTIONS_PK) */
174 null
175 from pay_action_interlocks xpai,
176 pay_assignment_actions xpaa,
177 pay_payroll_actions xppa
178 where xpai.locked_action_id = paa.assignment_action_id
179 and xpaa.assignment_action_id = xpai.locking_action_id
180 and xppa.payroll_action_id = xpaa.payroll_action_id
181 and xppa.action_type = 'X'
182 and xppa.report_type = 'PRT'
183 and xppa.report_qualifier = 'JP')
184 and not exists(
185 select null
186 from pay_action_information pai
187 where (pai.action_information_category='JP_PRE_TAX_1')
188 and pai.action_context_type='AAP'
189 and pai.action_information1=paa.assignment_action_id
190 and pai.assignment_id=paa.assignment_id)
191 order by paa.assignment_id
192 for update of paa.assignment_action_id nowait;
193 begin
194 hr_utility.set_location('Entering: ' || c_proc, 10);
195 --
196 -- This needs to be called for the case of single-threaded.
197 --
198 initialization_code(p_payroll_action_id);
199 --
200 for l_assact_rec in csr_assact( g_business_group_id,
201 g_consolidation_set_id,
202 g_start_date,
203 g_effective_date,
204 g_payroll_id) loop
205
206 hr_utility.trace('assignment_id : ' || l_assact_rec.assignment_id);
207 --
208 select pay_assignment_actions_s.nextval
209 into l_locking_action_id
210 from dual;
211 --
212 hr_utility.trace('archive assignment_action_id : ' || l_locking_action_id);
213 --
214 hr_nonrun_asact.insact(
215 lockingactid => l_locking_action_id,
216 assignid => l_assact_rec.assignment_id,
217 pactid => p_payroll_action_id,
218 chunk => p_chunk_number,
219 greid => null);
220 --
221 -- Create action interlock
222 --
223 hr_utility.trace('run assignment_action_id : ' || l_assact_rec.assignment_action_id);
224 hr_nonrun_asact.insint(
225 lockingactid => l_locking_action_id,
226 lockedactid => l_assact_rec.assignment_action_id);
227
228 --
229 l_assignment_id := l_assact_rec.assignment_id;
230 end loop;
231 --
232 hr_utility.set_location('Leaving: ' || c_proc, 20);
233 end assignment_action_code;
234 -- |-------------------------------------------------------------------|
235 -- |-------------------------< archive_code >--------------------------|
236 -- |-------------------------------------------------------------------|
237 procedure archive_code(
238 p_assignment_action_id in pay_assignment_actions.assignment_action_id%TYPE,
239 p_effective_date in pay_payroll_actions.effective_date%TYPE)
240 is
241 c_proc constant varchar2(61) := c_package || 'archive_code';
242 l_errbuf varchar2(2000);
243 l_retcode varchar2(10);
244 error_message boolean;
245 --
246 cursor csr is
247 select locked_action_id
248 from pay_action_interlocks
249 where locking_action_id = p_assignment_action_id;
250 begin
251 hr_utility.set_location('Entering: ' || c_proc, 10);
252 hr_utility.trace('locking_action_id : ' || p_assignment_action_id);
253 --
254 for l_rec in csr loop
255 hr_utility.trace('locked_action_id : ' || l_rec.locked_action_id);
256 --
257 pay_jp_pre_tax_pkg.run_assact(
258 p_errbuf => l_errbuf,
259 p_retcode => l_retcode,
260 p_locked_assignment_action_id => l_rec.locked_action_id,
261 p_locking_assignment_action_id => p_assignment_action_id);
262 --
263 if nvl(l_retcode, '0') <> '0' then
264
265 hr_utility.trace('Error: ' || l_errbuf);
266 error_message := FND_CONCURRENT.SET_COMPLETION_STATUS('WARNING','JP Pre tax Archive Process completed with validation warning(s).');
267 fnd_message.set_encoded(l_errbuf);
268 fnd_message.raise_error;
269
270 end if;
271
272 end loop;
273 --
274 hr_utility.set_location('Leaving: ' || c_proc, 20);
275 end archive_code;
276 -- |-------------------------------------------------------------------|
277 -- |---------------------< deinitialization_code >---------------------|
278 -- |-------------------------------------------------------------------|
279 procedure deinitialization_code(p_payroll_action_id in pay_payroll_actions.payroll_action_id%TYPE)
280 is
281 c_proc constant varchar2(61) := c_package || 'deinitialization_code';
282 l_dummy varchar2(1);
283 cursor csr_remove_actions is
284 select 'Y'
285 from dual
286 where not exists(
287 select null
288 from pay_assignment_actions
289 where payroll_action_id = p_payroll_action_id
290 and action_status <> 'C');
291 begin
292 hr_utility.set_location('Entering: ' || c_proc, 10);
293 --
294 -- 14208197 - delete all assignment interlock actions.
295 --
296
297 hr_utility.trace('Removing all assignment actions in interlocks table...');
298 remove_interlocks(p_payroll_action_id);
299 hr_utility.trace('Removed all assignment actions in interlocks table');
300 --
301 hr_utility.set_location('Leaving: ' || c_proc, 20);
302 end deinitialization_code;
303 --
304 END PAY_JP_PRE_TAX_ARCHIVE;