[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 2006/09/13 17:18:38 sgottipa noship $ */
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 hr_utility.trace('payroll_action_id : ' || g_payroll_action_id);
83 hr_utility.trace('business_group_id : ' || g_business_group_id);
84 hr_utility.trace('start_date : ' || g_start_date);
85 hr_utility.trace('effective_date : ' || g_effective_date);
86 hr_utility.trace('payroll_id : ' || g_payroll_id);
87 hr_utility.trace('consolidation_set_id : ' || g_consolidation_set_id);
88 --
89 hr_utility.set_location('Leaving: ' || c_proc, 20);
90 end initialization_code;
91 -- |-------------------------------------------------------------------|
92 -- |--------------------------< range_code >---------------------------|
93 -- |-------------------------------------------------------------------|
94 procedure range_code(
95 p_payroll_action_id in pay_payroll_actions.payroll_action_id%TYPE,
96 p_sqlstr out nocopy varchar2)
97 is
98 c_proc constant varchar2(61) := c_package || 'range_code';
99 begin
100 hr_utility.set_location('Entering: ' || c_proc, 10);
101 --
102 -- This needs to be called for the case of single-threaded.
103 --
104 initialization_code(p_payroll_action_id);
105 --
106 p_sqlstr :=
107 'select distinct per.person_id
108 from per_all_people_f per,
109 pay_payroll_actions ppa
110 where ppa.payroll_action_id = :payroll_action_id
111 and ppa.business_group_id + 0 = per.business_group_id
112 order by per.person_id';
113 --
114 hr_utility.set_location('Leaving: ' || c_proc, 20);
115 end range_code;
116 -- |-------------------------------------------------------------------|
117 -- |--------------------< assignment_action_code >---------------------|
118 -- |-------------------------------------------------------------------|
119 procedure assignment_action_code(
120 p_payroll_action_id in pay_payroll_actions.payroll_action_id%TYPE,
121 p_start_person_id in number,
122 p_end_person_id in number,
123 p_chunk_number in pay_assignment_actions.chunk_number%TYPE)
124 is
125 c_proc constant varchar2(61) := c_package || 'assignment_action_code';
126 l_locking_action_id number;
127 l_assignment_id number;
128 --
129 cursor csr_assact(
130 p_business_group_id pay_payroll_actions.business_group_id%TYPE,
131 p_consolidation_set_id pay_payroll_actions.consolidation_set_id%TYPE,
132 p_start_date pay_payroll_actions.effective_date%TYPE,
133 p_effective_date pay_payroll_actions.effective_date%TYPE,
134 p_payroll_id pay_payroll_actions.payroll_id%TYPE) is
135
136 select /* Removed the hint as per Bug# 4767118 */
137 paa.assignment_id,
138 paa.assignment_action_id
139 from (
140 select /* Removed the hint as per Bug# 4767118 */
141 distinct asg.assignment_id
142 from per_periods_of_service pds,
143 per_all_assignments_f asg
144 where pds.person_id
145 between p_start_person_id and p_end_person_id
146 and pds.business_group_id + 0 = p_business_group_id
147 and asg.period_of_service_id = pds.period_of_service_id
148 ) v,
149 pay_assignment_actions paa,
150 pay_payroll_actions ppa
151 where paa.assignment_id = v.assignment_id
152 and paa.action_status = 'C'
153 and ppa.payroll_action_id = paa.payroll_action_id
154 and (ppa.consolidation_set_id = p_consolidation_set_id or p_consolidation_set_id is null)
155 and ppa.effective_date
156 between nvl(p_start_date, ppa.effective_date) and p_effective_date
157 and (ppa.payroll_id = p_payroll_id or p_payroll_id is null)
158 and ppa.action_type in ('R', 'Q', 'B', 'I')
159 and not exists(
160 select /*+ ORDERED
161 USE_NL(PAAA PPAA)
162 INDEX(XPAI PAY_ACTION_INTERLOCKS_FK2)
163 INDEX(XPAA PAY_ASSIGNMENT_ACTIONS_PK)
164 INDEX(XPPA PAY_PAYROLL_ACTIONS_PK) */
165 null
166 from pay_action_interlocks xpai,
167 pay_assignment_actions xpaa,
168 pay_payroll_actions xppa
169 where xpai.locked_action_id = paa.assignment_action_id
170 and xpaa.assignment_action_id = xpai.locking_action_id
171 and xppa.payroll_action_id = xpaa.payroll_action_id
172 and xppa.action_type = 'X'
173 and xppa.report_type = 'PRT'
174 and xppa.report_qualifier = 'JP')
175 and not exists(
176 select null
177 from pay_action_information pai
178 where (pai.action_information_category='JP_PRE_TAX_1')
179 and pai.action_context_type='AAP'
180 and pai.action_information1=paa.assignment_action_id
181 and pai.assignment_id=paa.assignment_id)
182 order by paa.assignment_id
183 for update of paa.assignment_action_id nowait;
184 begin
185 hr_utility.set_location('Entering: ' || c_proc, 10);
186 --
187 -- This needs to be called for the case of single-threaded.
188 --
189 initialization_code(p_payroll_action_id);
190 --
191 for l_assact_rec in csr_assact( g_business_group_id,
192 g_consolidation_set_id,
193 g_start_date,
194 g_effective_date,
195 g_payroll_id) loop
196
197 hr_utility.trace('assignment_id : ' || l_assact_rec.assignment_id);
198 --
199 select pay_assignment_actions_s.nextval
200 into l_locking_action_id
201 from dual;
202 --
203 hr_utility.trace('archive assignment_action_id : ' || l_locking_action_id);
204 --
205 hr_nonrun_asact.insact(
206 lockingactid => l_locking_action_id,
207 assignid => l_assact_rec.assignment_id,
208 pactid => p_payroll_action_id,
209 chunk => p_chunk_number,
210 greid => null);
211 --
212 -- Create action interlock
213 --
214 hr_utility.trace('run assignment_action_id : ' || l_assact_rec.assignment_action_id);
215 hr_nonrun_asact.insint(
216 lockingactid => l_locking_action_id,
217 lockedactid => l_assact_rec.assignment_action_id);
218
219 --
220 l_assignment_id := l_assact_rec.assignment_id;
221 end loop;
222 --
223 hr_utility.set_location('Leaving: ' || c_proc, 20);
224 end assignment_action_code;
225 -- |-------------------------------------------------------------------|
226 -- |-------------------------< archive_code >--------------------------|
227 -- |-------------------------------------------------------------------|
228 procedure archive_code(
229 p_assignment_action_id in pay_assignment_actions.assignment_action_id%TYPE,
230 p_effective_date in pay_payroll_actions.effective_date%TYPE)
231 is
232 c_proc constant varchar2(61) := c_package || 'archive_code';
233 l_errbuf varchar2(2000);
234 l_retcode varchar2(10);
235 --
236 cursor csr is
237 select locked_action_id
238 from pay_action_interlocks
239 where locking_action_id = p_assignment_action_id;
240 begin
241 hr_utility.set_location('Entering: ' || c_proc, 10);
242 hr_utility.trace('locking_action_id : ' || p_assignment_action_id);
243 --
244 for l_rec in csr loop
245 hr_utility.trace('locked_action_id : ' || l_rec.locked_action_id);
246 --
247 pay_jp_pre_tax_pkg.run_assact(
248 p_errbuf => l_errbuf,
249 p_retcode => l_retcode,
250 p_locked_assignment_action_id => l_rec.locked_action_id,
251 p_locking_assignment_action_id => p_assignment_action_id);
252 --
253 if nvl(l_retcode, '0') <> '0' then
254
255 hr_utility.trace('Error: ' || l_errbuf);
256 fnd_message.set_encoded(l_errbuf);
257 fnd_message.raise_error;
258
259 end if;
260
261 end loop;
262 --
263 hr_utility.set_location('Leaving: ' || c_proc, 20);
264 end archive_code;
265 -- |-------------------------------------------------------------------|
266 -- |---------------------< deinitialization_code >---------------------|
267 -- |-------------------------------------------------------------------|
268 procedure deinitialization_code(p_payroll_action_id in pay_payroll_actions.payroll_action_id%TYPE)
269 is
270 c_proc constant varchar2(61) := c_package || 'deinitialization_code';
271 l_dummy varchar2(1);
272 cursor csr_remove_actions is
273 select 'Y'
274 from dual
275 where not exists(
276 select null
277 from pay_assignment_actions
278 where payroll_action_id = p_payroll_action_id
279 and action_status <> 'C');
280 begin
281 hr_utility.set_location('Entering: ' || c_proc, 10);
282 --
283 -- If all assignment actions are completed without error, delete all assignment actions.
284 --
285
286 open csr_remove_actions;
287 fetch csr_remove_actions into l_dummy;
288 if csr_remove_actions%found then
289 hr_utility.trace('Removing all assignment actions in interlocks table...');
290 remove_interlocks(p_payroll_action_id);
291 -- pay_archive.remove_report_actions(p_payroll_action_id);
292 hr_utility.trace('Removed all assignment actions in interlocks table');
293 end if;
294 close csr_remove_actions;
295
296 --
297 hr_utility.set_location('Leaving: ' || c_proc, 20);
298 end deinitialization_code;
299 --
300 END PAY_JP_PRE_TAX_ARCHIVE;