4 l_package_name varchar2(50);
1 package body pay_sg_iras_amend_setup as
2 /* $Header: pysgiras.pkb 120.0.12010000.4 2009/12/15 03:41:09 jalin noship $ */
3 g_debug boolean;
5 -----------------------------------------------------------------------------
6 -- The SELECT statement in this procedure returns the Person Ids for
7 -- Assignments that require the archive process to create an Assignment
8 -- Action.
9 -- Core Payroll recommends the select has minimal restrictions.
13 , p_sql out nocopy varchar2 )
10 -----------------------------------------------------------------------------
11 procedure range_code
12 ( p_payroll_action_id in pay_payroll_actions.payroll_action_id%type
14 is
15 begin
16 if g_debug then
17 hr_utility.trace(l_package_name||'range_code - Start');
18 end if;
19 --
20 p_sql := 'select distinct person_id ' ||
21 'from per_people_f ppf, ' ||
22 'pay_payroll_actions ppa ' ||
23 'where ppa.payroll_action_id = :payroll_action_id ' ||
24 'and ppa.business_group_id = ppf.business_group_id ' ||
25 'order by ppf.person_id';
26 --
27 if g_debug then
28 hr_utility.trace(l_package_name||'range_code - End');
29 end if;
30 end range_code;
31 ----------------------------------------------------------------------------
32 -- This procedure is used to restrict the Assignment Action Creation.
33 -- It calls the procedure that actually inserts the Assignment Actions.
34 -- The cursor selects the assignments that have had any payroll
35 -- processing for the Legal Entity within the Basis Year.
36 -- The person must not have the "IR21 Run Date" set, as this means they
37 -- have received this form, and therefore must not appear in the
38 -- archive/magtape.
39 -- The person must not have had any Magtape File produced for the same
40 -- Business Group, Legal Entity and Basis Year. If they want to
41 -- re-archive a person, they must ROLLBACK the magtape first, or use the
42 -- standard Re-try, Rollback payroll process.
43 ----------------------------------------------------------------------------
44 procedure assignment_action_code
45 ( p_payroll_action_id in pay_payroll_actions.payroll_action_id%type
46 , p_start_person_id in per_all_people_f.person_id%type
47 , p_end_person_id in per_all_people_f.person_id%type
48 , p_chunk in number )
49 is
50 v_next_action_id pay_assignment_actions.assignment_action_id%type;
51 v_effective_date date;
52 v_business_group_id number;
53 v_basis_start date;
54 v_basis_end date;
55 v_legal_entity_id number;
56 v_assignment_set_id number;
57 v_person_id number;
58 v_basis_year number;
59 -------------------------------------------
60 -- Record of Assignments
61 -------------------------------------------
62 type t_assignment_list is table of per_all_assignments_f.assignment_id%type;
63 asglist t_assignment_list;
64 ----------------------------------------------------------------------------
65 -- Legislative Parameters for Run
66 ----------------------------------------------------------------------------
67 cursor get_params( c_payroll_action_id in pay_payroll_actions.payroll_action_id%type )
68 is
69 select nvl(to_date(to_char(to_date(pay_core_utils.get_parameter('EFFECTIVE_DATE', legislative_parameters),
70 'YYYY/MM/DD'),'DD-MM-YYYY'),'DD-MM-YYYY'),
71 to_date('31-12-'||pay_core_utils.get_parameter('BASIS_YEAR',legislative_parameters),'DD-MM-YYYY')),
72 pay_core_utils.get_parameter('BUSINESS_GROUP_ID',legislative_parameters),
73 to_date('01-01-'||pay_core_utils.get_parameter('BASIS_YEAR',legislative_parameters),'DD-MM-YYYY'),
74 to_date('31-12-'|| pay_core_utils.get_parameter('BASIS_YEAR',legislative_parameters),'DD-MM-YYYY'),
75 pay_core_utils.get_parameter('LEGAL_ENTITY_ID',legislative_parameters),
76 pay_core_utils.get_parameter('ASSIGNMENT_SET_ID',legislative_parameters),
77 pay_core_utils.get_parameter('PERSON_ID',legislative_parameters),
78 pay_core_utils.get_parameter('BASIS_YEAR',legislative_parameters)
79 from pay_payroll_actions
80 where payroll_action_id =c_payroll_Action_id;
81 ----------------------------------------------------------------------------
82 -- Cursor Next Assignment Action
83 ----------------------------------------------------------------------------
84 cursor next_action_id is
85 select pay_assignment_actions_s.nextval
86 from dual;
87 ----------------------------------------------------------------------------
88 -- Filters Assignments to be processed.
89 ----------------------------------------------------------------------------
90 cursor process_assignments
91 ( c_payroll_action_id in pay_payroll_actions.payroll_action_id%type,
92 c_start_person_id in per_all_people_f.person_id%type,
93 c_end_person_id in per_all_people_f.person_id%type )
94 is
95 select distinct a.assignment_id
96 from per_assignments_f a,
97 pay_payroll_actions pa
98 where pa.payroll_action_id = c_payroll_action_id
99 and a.person_id between c_start_person_id and c_end_person_id
100 and a.business_group_id = pa.business_group_id
101 and ( a.effective_start_date <= v_basis_end and a.effective_end_date>= v_basis_start)
102 and decode(v_assignment_set_id,null,'Y',
103 decode(hr_assignment_set.ASSIGNMENT_IN_SET(v_assignment_set_id,a.assignment_id),'Y','Y','N')) = 'Y'
104 and a.person_id = nvl(v_person_id,a.person_id)
105 ----------------------------------------------------------------------
106 -- Do not select the person if they have had an IR21 Form produced
110 from per_people_extra_info pei
107 ----------------------------------------------------------------------
108 and not exists
109 ( select null
111 where pei.person_id = a.person_id
112 and pei.pei_information1 is not null
113 and pei.information_type = 'HR_IR21_PROCESSING_DATES_SG'
114 )
115 ----------------------------------------------------------------------
116 -- Select the person if they have had any Magtape File produced for the same
117 -- Business Group, Legal Entity and Basis Year
118 -- We'll do this because unless they have submitted an original, no need of an archive
119 ----------------------------------------------------------------------
120 and exists
121 ( select null
122 from per_assignments_f paf,
123 pay_assignment_actions mcl,
124 pay_payroll_actions mpl
125 where paf.assignment_id = a.assignment_id
126 and paf.assignment_id = mcl.assignment_id
127 and mpl.payroll_action_id = mcl.payroll_action_id
128 and mpl.business_group_id = pa.business_group_id
129 and mpl.effective_date between v_basis_start and v_basis_end
130 and pay_core_utils.get_parameter('LEGAL_ENTITY_ID',mpl.legislative_parameters) = v_legal_entity_id
131 and mpl.report_type in ( 'SG_A8B','SG_IR8A','SG_IR8S','SG_A8A' )
132 and mpl.action_type = 'X'
133 and mcl.action_status = 'C'
134 group by paf.assignment_id
135 )
136 --------------------------------------------------------------------------------------------------------------------
137 -- Select an assignment if any payroll runs exist for assignment in processing
138 -- year and legal entity.
139 --------------------------------------------------------------------------------------------------------------------
140 and exists
141 ( select null
142 from per_assignments_f paf,
143 pay_assignment_actions pac,
144 pay_payroll_actions ppa
145 where paf.assignment_id = a.assignment_id
146 and paf.assignment_id = pac.assignment_id
147 and pac.tax_unit_id = v_legal_entity_id
148 and ppa.payroll_action_id = pac.payroll_action_id
149 and ppa.action_type in ('R','B','I','Q','V')
150 and pac.action_status = 'C'
151 and ppa.business_group_id = v_business_group_id
152 and ppa.effective_date between v_basis_start and v_basis_end
153 group by paf.assignment_id
154 );
155 ----------------------------------------------------------------------
156 begin
157 if g_debug then
158 hr_utility.trace(l_package_name||'assignment_action_code - Start');
159 end if;
160 --
161 open get_params(p_payroll_action_id);
162 fetch get_params
163 into v_effective_date,
164 v_business_group_id,
165 v_basis_start,
166 v_basis_end,
167 v_legal_entity_id,
168 v_assignment_set_id,
169 v_person_id,
170 v_basis_year;
171 close get_params;
172 --
173 open process_assignments( p_payroll_action_id,
174 p_start_person_id,
175 p_end_person_id ) ;
176 fetch process_assignments bulk collect into asglist;
177 close process_assignments;
178 --
179 for i in 1..asglist.count
180 loop
181 if asglist.exists(i) then
182 open next_action_id;
183 fetch next_action_id into v_next_action_id;
184 close next_action_id;
185 --
186 if g_debug then
187 hr_utility.trace(l_package_name||'assignment_action_code - Before hr_nonrun_asact.insact');
188 end if;
189 --
190 hr_nonrun_asact.insact( v_next_action_id
191 , asglist(i)
192 , p_payroll_action_id
193 , p_chunk
194 , null
195 );
196 --
197 if g_debug then
198 hr_utility.trace(l_package_name||'assignment_action_code - After hr_nonrun_asact.insact');
199 end if;
200 end if;
201 end loop;
202 --
203 if g_debug then
204 hr_utility.trace(l_package_name||'assignment_action_code - End');
205 end if;
206 end assignment_action_code;
207 ------------------------------------------------------------------------
208 -- Archives Person details for the processing Assignment
209 ------------------------------------------------------------------------
210 procedure archive_code
211 ( p_assignment_action_id in pay_assignment_actions.assignment_action_id%type
212 , p_effective_date in date )
213 is
214 v_national_identifier per_all_people_f.national_identifier%type;
215 v_start_date varchar2(50); /* Bug# 3910804 */
216 v_person_id per_all_people_f.person_id%type;
217 v_assignment_id per_all_assignments_f.assignment_id%type;
218 v_setup_action pay_assignment_actions.payroll_action_id%type;
219 ------------------------------------------------------------------------
220 -- Get Person Details
221 -- Bug 3910804 - Selected canonical format of person start date
225 is
222 ------------------------------------------------------------------------
223 cursor get_details
224 ( c_assignment_action_id pay_assignment_actions.assignment_action_id%type )
226 select nvl(pap.national_identifier,per_information12),
227 fnd_date.date_to_canonical(pap.start_date), /* Bug# 3910804 */
228 paa.person_id,
229 pac.assignment_id
230 from pay_assignment_actions pac,
231 per_assignments_f paa,
232 per_people_f pap
233 where pac.assignment_action_id = c_assignment_action_id
234 and paa.assignment_id = pac.assignment_id
235 and paa.person_id = pap.person_id;
236 --
237 begin
238 if g_debug then
239 hr_utility.trace(l_package_name||'Archive_Code - Start');
240 end if;
241 -------------------------------------------------------------------
242 -- Get setup action id
243 -------------------------------------------------------------------
244 select payroll_action_id
245 into v_setup_action
246 from pay_assignment_actions
247 where assignment_action_id = p_assignment_action_id ;
248 --
249 open get_details ( p_assignment_action_id );
250 fetch get_details into v_national_identifier,
251 v_start_date,
252 v_person_id,
253 v_assignment_id;
254 if get_details%found then
255 close get_details;
256 --
257 if g_debug then
258 hr_utility.trace(l_package_name||'Archive_Code - Before pai insert');
259 end if;
260 --------------------------------------------------------------
261 --
262 --
263 --------------------------------------------------------------
264 insert into pay_action_information
265 ( action_information_id,
266 action_context_id,
267 action_context_type,
268 action_information_category,
269 action_information1,
270 action_information2,
271 action_information3,
272 assignment_id )
273 values
274 ( pay_action_information_s.nextval,
275 v_setup_action,
276 'AAP',
277 'SG_IRAS_AMEND_SETUP',
278 v_national_identifier,
279 v_person_id,
280 v_start_date,
281 v_assignment_id );
282 --
283 if g_debug then
284 hr_utility.trace(l_package_name||'Archive_Code - After pai insert');
285 end if;
286 else
287 if g_debug then
288 hr_utility.trace(l_package_name||'Archive_Code - Person record not found');
289 end if;
290 --
291 close get_details;
292 end if;
293 --
294 if g_debug then
295 hr_utility.trace(l_package_name||'Archive_Code - End');
296 end if;
297 end archive_code;
298 --------------------------------------------------------------
299 -- Initiates IRAS Line Archive Processor
300 --------------------------------------------------------------
301 procedure deinit_code
302 ( p_payroll_action_id in pay_payroll_actions.payroll_action_id%type )
303 is
304 l_rep_req_id number;
305 v_magnetic_file_name varchar2(50);
306 v_le number;
307 v_basis number;
308 v_bg_id number;
309 v_ass_id number;
310 v_per_id number;
311 v_action_parameter_group varchar2(50);
312
313 --
314 cursor csr_iras_action_details is
315 select pay_core_utils.get_parameter('BUSINESS_GROUP_ID',legislative_parameters),
316 pay_core_utils.get_parameter('LEGAL_ENTITY_ID',legislative_parameters),
317 pay_core_utils.get_parameter('BASIS_YEAR',legislative_parameters),
318 pay_core_utils.get_parameter('MAGNETIC_FILE_NAME',legislative_parameters),
319 pay_core_utils.get_parameter('ACTION_PARAMETER_GROUP',legislative_parameters),
320 pay_core_utils.get_parameter('ASSIGNMENT_SET_ID',legislative_parameters),
321 pay_core_utils.get_parameter('PERSON_ID',legislative_parameters)
322 from pay_payroll_actions
323 where payroll_action_id = p_payroll_action_id;
324 begin
325 if g_debug then
326 hr_utility.trace(l_package_name||'Deinit_Code - Start');
327 end if;
328 --
329 open csr_iras_action_details;
330 fetch csr_iras_action_details into
331 v_bg_id,
332 v_le,
333 v_basis,
334 v_magnetic_file_name,
335 v_action_parameter_group,
336 v_ass_id,
337 v_per_id;
338
339 close csr_iras_action_details;
340 --
341 if g_debug then
342 hr_utility.trace(l_package_name||'Deinit_Code - Before initiating archive');
343 end if;
344 --
345
346 l_rep_req_id := FND_REQUEST.SUBMIT_REQUEST (
347 APPLICATION => 'PAY',
348 PROGRAM => 'PYSGIRAA',
349 ARGUMENT1 => 'ARCHIVE',
350 ARGUMENT2 => 'SG_IRAS_AMEND_ARCHIVE',
351 ARGUMENT3 => 'SG',
352 ARGUMENT4 => null,
353 ARGUMENT5 => null,
354 ARGUMENT6 => 'REPORT',
355 ARGUMENT7 => v_bg_id,
356 ARGUMENT8 => v_magnetic_file_name,
357 ARGUMENT9 => null,
358 ARGUMENT10 => v_action_parameter_group,
359 ARGUMENT11 => v_le,
360 ARGUMENT12 => v_basis,
361 ARGUMENT13 => v_ass_id,
362 ARGUMENT14 => v_per_id,
363 ARGUMENT15 => 'START_DATE='||to_char(v_basis)||'/01/01 00:00:00',
364 ARGUMENT16 => 'END_DATE='||to_char(v_basis)||'/12/31 00:00:00',
365 ARGUMENT17=>'BUSINESS_GROUP_ID='||v_bg_id||' LEGAL_ENTITY_ID='||v_le||' SETUP_ACTION_ID='||p_payroll_action_id||' ASSIGNMENT_SET_ID='||v_ass_id||' PERSON_ID='||v_per_id||' BASIS_YEAR='||v_basis||' '||'END_DATE='||to_char(v_basis)||'/12/31 00:00:00');
366
367 if g_debug then
368 hr_utility.trace(l_package_name||'Deinit_Code - After initiating archive');
369 hr_utility.trace(l_package_name||'Deinit_Code - End');
370 end if;
371 end deinit_code;
372 begin
373 l_package_name := 'pay_sg_iras_amend_setup.';
374 g_debug := hr_utility.debug_enabled;
375 end pay_sg_iras_amend_setup;
[Home] [Help]