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