1 package pay_sg_iras_archive AUTHID CURRENT_USER as
2 /* $Header: pysgirar.pkh 120.5.12020000.2 2012/07/06 10:22:00 jalin ship $ */
3 level_cnt number;
4
5 --
6 type t_balanceid_store_rec is record
7 ( user_entity_id ff_user_entities.user_entity_id%type,
8 defined_balance_id pay_defined_balances.defined_balance_id%type );
9 --
10 type t_ytd_balanceid_store_tab is table of t_balanceid_store_rec index by binary_integer;
11 t_ytd_balanceid_store t_ytd_balanceid_store_tab;
12 t_ytd_a8a_balanceid_store t_ytd_balanceid_store_tab;
13 --
14 type t_month_balanceid_store_tab is table of t_balanceid_store_rec index by binary_integer;
15 t_month_balanceid_store t_month_balanceid_store_tab;
16 --
17 ---------------------------------------------------------------------
18 -- 3435334 Record of person_ids with same national identifier
19 ---------------------------------------------------------------------
20 type t_person_id_tab is table of per_all_people_f.person_id%type index by binary_integer;
21 g_person_id_tab t_person_id_tab;
22 --
23 type t_rehire_same_person_record is record
24 (
25 person_id per_all_people_f.person_id%type
26 );
27 --------------------------------------------------------------------
28 -- Bug 4688761 Record of person_ids with archived person_id
29 --------------------------------------------------------------------
30 type t_archived_person_rec is record
31 (
32 person_id per_all_people_f.person_id%type
33 );
34 type t_archived_person_tab is table of t_archived_person_rec index by binary_integer;
35 t_archived_person t_archived_person_tab;
36
37 -------------------------------------------------------------------
38 -- 3956870 Defined to store the balance status details
39 -------------------------------------------------------------------
40 type balance_status_store_rec is record
41 ( business_group_id pay_balance_validation.business_group_id%type,
42 defined_balance_id pay_balance_validation.defined_balance_id%type,
43 run_balance_status pay_balance_validation.run_balance_status%type
44 );
45 --
46 type t_balance_status_store_tab is table of balance_status_store_rec index by binary_integer;
47 t_bal_stat_rec t_balance_status_store_tab;
48
49 --------------------------------------------------------------------
50 -- These are PUBLIC procedures are required by the Archive process.
51 -- Their names are stored in PAY_REPORT_FORMAT_MAPPINGS_F so that
52 -- the archive process knows what code to execute for each step of
53 -- the archive.
54 --------------------------------------------------------------------
55 procedure range_code
56 ( p_payroll_action_id in pay_payroll_actions.payroll_action_id%type,
57 p_sql out nocopy varchar2 );
58 --
59 procedure assignment_action_code
60 ( p_payroll_action_id in pay_payroll_actions.payroll_action_id%type,
61 p_start_person_id in per_all_people_f.person_id%type,
62 p_end_person_id in per_all_people_f.person_id%type,
63 p_chunk in number );
64 --
65 procedure initialization_code
66 ( p_payroll_action_id in pay_payroll_actions.payroll_action_id%type );
67 --
68 procedure archive_code
69 ( p_assignment_action_id in pay_assignment_actions.assignment_action_id%type,
70 p_effective_date in date );
71 ----------------------------------------------------------------------------
72 -- Bug: 3118540 Procedures called from SRS 'IR8S Ad Hoc Printed Archive'
73 ----------------------------------------------------------------------------
74 procedure assignment_action_code_adhoc
75 ( p_payroll_action_id in pay_payroll_actions.payroll_action_id%type,
76 p_start_person_id in per_all_people_f.person_id%type,
77 p_end_person_id in per_all_people_f.person_id%type,
78 p_chunk in number );
79 --
80 procedure initialization_code_adhoc
81 ( p_payroll_action_id in pay_payroll_actions.payroll_action_id%type );
82 --
83 Function get_archive_value
84 ( p_user_entity_name in ff_user_entities.user_entity_name%type,
85 p_assignment_action_id in pay_assignment_actions.assignment_action_id%type ) return varchar2;
86 --
87 procedure deinit_code
88 ( p_payroll_action_id in pay_payroll_actions.payroll_action_id%type );
89 ----------------------------------------------------------------------------
90 -- These are PRIVATE procedures that required for the Archive
91 -- process.
92 --------------------------------------------------------------------
93 procedure archive_balances
94 ( p_assignment_action_id in pay_assignment_actions.assignment_action_id%type,
95 p_person_id in per_all_people_f.person_id%type,
96 p_business_group_id in hr_organization_units.business_group_id%type,
97 p_tax_unit_id in ff_archive_item_contexts.context%type,
98 p_basis_year in varchar2 );
99 --
100 procedure archive_balance_dates
101 ( p_person_id in per_all_people_f.person_id%TYPE,
102 p_basis_year in varchar2,
103 p_business_group_id in hr_organization_units.business_group_id%type,
104 p_assignment_action_id in pay_assignment_actions.assignment_action_id%type,
105 p_run_ass_action_id in pay_assignment_actions.assignment_action_id%type,
106 p_tax_unit_id in pay_assignment_actions.tax_unit_id%type );
107 --
108 procedure archive_org_info
109 ( p_assignment_action_id in pay_assignment_actions.assignment_action_id%type,
110 p_business_group_id in hr_organization_units.business_group_id%type,
111 p_legal_entity_id in hr_organization_units.organization_id%type,
112 p_person_id in per_all_people_f.person_id%type,
113 p_basis_start in date,
114 p_basis_end in date );
115 --
116 procedure archive_person_details
117 ( p_assignment_action_id in pay_assignment_actions.assignment_action_id%type,
118 p_person_id in per_all_people_f.person_id%type,
119 p_basis_start in date,
120 p_basis_end in date );
121 --
122 procedure archive_person_addresses
123 ( p_assignment_action_id in pay_assignment_actions.assignment_action_id%type,
124 p_person_id in per_all_people_f.person_id%type,
125 p_basis_start in date,
126 p_basis_end in date );
127 --
128 procedure archive_person_cq_addresses
129 ( p_assignment_action_id in pay_assignment_actions.assignment_action_id%type,
130 p_person_id in per_all_people_f.person_id%type,
131 p_basis_start in date,
132 p_basis_end in date );
133 --
134 procedure archive_emp_details
135 ( p_assignment_action_id in pay_assignment_actions.assignment_action_id%type,
136 p_person_id in per_all_people_f.person_id%type,
137 -- p_assignment_id in per_all_assignments_f.assignment_id%type,
138 p_legal_entity_id in hr_organization_units.organization_id%type, /* Bug 13786754*/
139 p_basis_start in date,
140 p_basis_end in date );
141 --
142 procedure archive_people_flex
143 ( p_assignment_action_id in pay_assignment_actions.assignment_action_id%type,
144 p_person_id in per_all_people_f.person_id%type,
145 p_basis_start in date,
146 p_basis_end in date );
147 --
148 procedure archive_person_eits
149 ( p_assignment_action_id in pay_assignment_actions.assignment_action_id%type,
150 p_person_id in per_all_people_f.person_id%type,
151 p_basis_start in date,
152 p_basis_end in date );
153
154 --------------------------------------------------------------------------
155 -- Bug 5435088, Added for payroll date
156 --------------------------------------------------------------------------
157 procedure archive_payroll_date
158 ( p_assignment_action_id in pay_assignment_actions.assignment_action_id%type,
159 p_business_group_id in hr_organization_units.business_group_id%type,
160 p_legal_entity_id in hr_organization_units.organization_id%type,
161 p_person_id in per_all_people_f.person_id%type,
162 p_basis_year in varchar2);
163 --
164 --
165 --------------------------------------------------------------------------
166 -- Bug #4688761, added legal_entity_id
167 -- Bug 4890964, added assignment_id
168 --------------------------------------------------------------------------
169 procedure archive_assignment_eits
170 ( p_assignment_action_id in pay_assignment_actions.assignment_action_id%type,
171 p_person_id in per_all_people_f.person_id%type,
172 p_assignment_id in per_all_assignments_f.assignment_id%type,
173 p_legal_entity_id in hr_organization_units.organization_id%type,
174 p_basis_start in date,
175 p_basis_end in date );
176 --
177 --------------------------------------------------------------------------
178 -- Bug #5078454
179 --------------------------------------------------------------------------
180 procedure archive_ass_bonus_date_eits
181 ( p_assignment_action_id in pay_assignment_actions.assignment_action_id%type,
182 p_person_id in per_all_people_f.person_id%type,
183 p_assignment_id in per_all_assignments_f.assignment_id%type,
184 p_legal_entity_id in hr_organization_units.organization_id%type,
185 p_basis_start in date,
186 p_basis_end in date );
187 --
188 --------------------------------------------------------------------------
189 -- Bug 5435088
190 --------------------------------------------------------------------------
191 procedure archive_ass_payment_method
192 ( p_assignment_action_id in pay_assignment_actions.assignment_action_id%type,
193 p_person_id in per_all_people_f.person_id%type,
194 p_assignment_id in per_all_assignments_f.assignment_id%type,
195 p_legal_entity_id in hr_organization_units.organization_id%type,
196 p_basis_start in date,
197 p_basis_end in date);
198 --
199 -------------------------------------------------------------------------
200 -- Bug #4688761, added legal_entity_id
201 -------------------------------------------------------------------------
202 procedure archive_os_assignment
203 ( p_assignment_action_id in pay_assignment_actions.assignment_action_id%type,
204 p_person_id in per_all_people_f.person_id%type,
205 p_legal_entity_id in hr_organization_units.organization_id%type,
206 p_basis_start in date,
207 p_basis_end in date );
208 --
209 --------------------------------------------------------------------------
210 -- Bug 4890964, added assignment_id
211 --------------------------------------------------------------------------
212 procedure archive_job_designation
213 ( p_assignment_action_id in pay_assignment_actions.assignment_action_id%type,
214 p_person_id in per_all_people_f.person_id%type,
215 p_assignment_id in per_all_assignments_f.assignment_id%type,
216 p_legal_entity_id in hr_organization_units.organization_id%type,
217 p_basis_start in date,
218 p_basis_end in date,
219 p_er_designation_type in hr_organization_information.org_information17%type,
220 p_er_position_seg_type in hr_organization_information.org_information18%type );
221 --
222 ---------------------------------------------------------------------------
223 -- Bug #4314453, added legal_entity_id
224 ---------------------------------------------------------------------------
225 procedure archive_shares_details
226 ( p_assignment_action_id in pay_assignment_actions.assignment_action_id%type,
227 p_person_id in per_all_people_f.person_id%type,
228 p_tax_unit_id in ff_archive_item_contexts.context%type,
229 p_basis_start in date,
230 p_basis_end in date );
231 --
232 procedure archive_ir8s_c_details
233 ( p_assignment_action_id in pay_assignment_actions.assignment_action_id%type,
234 p_person_id in per_all_people_f.person_id%type,
235 p_tax_unit_id in ff_archive_item_contexts.context%type,
236 p_business_group_id in per_assignments_f.business_group_id%type,
237 p_basis_start in date,
238 p_basis_end in date );
239 --
240 procedure archive_ir8s_c_detail_moas
241 ( p_assignment_action_id in pay_assignment_actions.assignment_action_id%type,
242 p_1_person_id in per_all_people_f.person_id%type,
243 p_person_id in per_all_people_f.person_id%type,
244 p_tax_unit_id in ff_archive_item_contexts.context%type,
245 p_business_group_id in per_assignments_f.business_group_id%type,
246 p_basis_start in date,
247 p_basis_end in date );
248 --
249 procedure archive_item
250 ( p_user_entity_name in ff_user_entities.user_entity_name%type,
251 p_assignment_action_id in pay_assignment_actions.assignment_action_id%type,
252 p_archive_value in ff_archive_items.value%type );
253 --
254 procedure archive_item_2
255 ( p_user_entity_name in ff_user_entities.user_entity_name%type,
256 p_assignment_action_id in pay_assignment_actions.assignment_action_id%type,
257 p_archive_value in ff_archive_items.value%type,
258 p_context_value2 in ff_archive_item_contexts.context%type );
259 --
260 procedure archive_item_3
261 ( p_user_entity_name in ff_user_entities.user_entity_name%type,
262 p_assignment_action_id in pay_assignment_actions.assignment_action_id%type,
263 p_archive_value in ff_archive_items.value%type,
264 p_context_value2 in ff_archive_item_contexts.context%type,
265 p_context_value3 in ff_archive_item_contexts.context%type );
266 -------------------------------------------------------------------------------
267 -- Bug 3435334 - New function introduced for implementing duplicate person logic
268 -- with pre-processor
269 -------------------------------------------------------------------------------
270 function employee_if_latest
271 ( p_national_identifier in varchar2,
272 p_person_id in per_all_people_f.person_id%type,
273 p_setup_action_id in pay_payroll_actions.payroll_action_id%type,
274 p_report_type in varchar2 ) return boolean ;
275 --
276 ----------------------------------------------------------------------------
277 -- Bug 4688761 - New function to check if the person_id has been archived once
278 ----------------------------------------------------------------------------
279 function person_if_archived (p_person_id in per_all_people_f.person_id%type) return boolean;
280 --
281 ----------------------------------------------------------------------------
282 -- Bug 4890964 - The function to get the LE if it is with the latest primary
283 -- assignment for multi-LEs.
284 ----------------------------------------------------------------------------
285 function pri_if_latest
286 ( p_person_id in per_all_people_f.person_id%type
287 , p_tax_unit_id in ff_archive_item_contexts.context%type
288 , p_basis_start in date
289 , p_basis_end in date) return boolean;
290 --
291 ----------------------------------------------------------------------------
292 -- Bug 4890964 - The function to get the assignment with LE if its the
293 -- latest primary assignment
294 ----------------------------------------------------------------------------
295 function pri_LE_if_latest
296 ( p_person_id in per_all_people_f.person_id%type
297 , p_tax_unit_id in ff_archive_item_contexts.context%type
298 , p_basis_start in date
299 , p_basis_end in date) return number;
300 --
301 ----------------------------------------------------------------------------
302 -- Bug 4890964 - The function to get the assignment with LE if its the
303 -- latest assignment without primay flag defined.
304 ----------------------------------------------------------------------------
305 function id_LE_if_latest
306 ( p_person_id in per_all_people_f.person_id%type
307 , p_tax_unit_id in ff_archive_item_contexts.context%type
308 , p_basis_start in date
309 , p_basis_end in date) return number;
310 --
311 ---------------------------------------------------------------------------
312 -- Bug 5435088 - The function to check if a value is numeric
313 ---------------------------------------------------------------------------
314 function check_is_number (p_value in varchar2) return boolean;
315
316 ---------------------------------------------------------------------------
317 -- Bug 5435088 - The function to check if the payer id is valid
318 ---------------------------------------------------------------------------
319 function check_payee_id (p_ee_income_tax_number in varchar2,
320 p_payee_id_type in varchar2) return char;
321
322 ---------------------------------------------------------------------------
323 -- Bug 5435088 - The function to get country code
324 ---------------------------------------------------------------------------
325 function get_country_code (p_country in varchar2) return varchar2;
326
327
328 ---------------------------------------------------------------------------
329 -- Bug 5435088 - The function to check if the payee id is valid
330 ---------------------------------------------------------------------------
331 function check_payer_id (p_er_income_tax_number in varchar2,
332 p_er_payer_id in varchar2) return char;
333
334
335 ---------------------------------------------------------------------
336 -- PUBLIC cursors used to retrieve data and pass it to IRAS formulae.
337 -- Passes parameters to SG_ARCHIVE_HEADER formula
338 -- Just need 1 context for Org Info
339 ---------------------------------------------------------------------
340 cursor archive_header is
341 select 'ASSIGNMENT_ACTION_ID=C',
342 pac.assignment_action_id,
343 'BASIS_YEAR=P',
344 pay_core_utils.get_parameter('BASIS_YEAR', ppa.legislative_parameters) basis_year,
345 'CREATION_DATE=P',
346 to_char(sysdate,'YYYYMMDD') creation_date
347 from pay_payroll_actions ppa,
348 pay_assignment_actions pac,
349 ff_archive_items ffi,
350 ff_database_items fdi
351 where ppa.payroll_action_id = pay_magtape_generic.get_parameter_value('TRANSFER_PAYROLL_ACTION_ID')
352 and ppa.payroll_action_id = pac.payroll_action_id
353 and pac.action_status = 'C'
354 and ffi.context1 = pac.assignment_action_id /* Added joins for bug:3232300 */
355 and fdi.user_name = 'X_SG_LEGAL_ENTITY_SG_LEGAL_ENTITY_NAME'
356 and ffi.user_entity_id = fdi.user_entity_id
357 and rownum=1;
358 ---------------------------------------------------------------------
359 -- Passes parameters to SG_ARCHIVE_DETAILS formula
360 ---------------------------------------------------------------------
361 cursor archive_details is
362 select distinct
363 'ASSIGNMENT_ACTION_ID=C',
364 pac.assignment_action_id,
365 'TAX_UNIT_ID=C',
366 to_number(ac2.context) tax_unit_id,
367 'BASIS_YEAR=P',
368 pay_core_utils.get_parameter('BASIS_YEAR', ppa.legislative_parameters) basis_year,
369 'P_ASSIGNMENT_ACTION_ID=P',
370 to_char(pac.assignment_action_id),
371 'PERSON_ID=P',
372 to_char(paa.person_id) person_id,
373 'ASSIGNMENT_NUMBER=P',
374 paa.assignment_number assignment_number
375 from per_all_assignments_f paa,
376 pay_payroll_actions ppa,
377 pay_assignment_actions pac,
378 ff_archive_items fai,
379 ff_archive_item_contexts ac2
380 where ppa.payroll_action_id = pay_magtape_generic.get_parameter_value('TRANSFER_PAYROLL_ACTION_ID')
381 and pac.action_status = 'C'
382 and ppa.payroll_action_id = pac.payroll_action_id
383 and paa.assignment_id = pac.assignment_id
384 and ppa.business_group_id = pay_magtape_generic.get_parameter_value('BUSINESS_GROUP_ID')
385 and paa.business_group_id = ppa.business_group_id
386 and fai.context1 = pac.assignment_action_id
387 and fai.archive_item_id = ac2.archive_item_id and ac2.sequence_no = 2
388 order by paa.person_id;
389 ---------------------------------------------------------------------
390 -- Passes parameters to SG_ARCHIVE_DETAILS formula
391 ---------------------------------------------------------------------
392 cursor archive_org_details is
393 select distinct
394 'TAX_UNIT_ID=C',
395 to_number(ac2.context) tax_unit_id,
396 'BASIS_YEAR=P',
397 pay_core_utils.get_parameter('BASIS_YEAR', ppa.legislative_parameters) basis_year
398 from per_all_assignments_f paa,
399 pay_payroll_actions ppa,
400 pay_assignment_actions pac,
401 ff_archive_items fai,
402 ff_archive_item_contexts ac2
403 where ppa.payroll_action_id = pay_magtape_generic.get_parameter_value('TRANSFER_PAYROLL_ACTION_ID')
404 and pac.action_status = 'C'
405 and ppa.payroll_action_id = pac.payroll_action_id
406 and paa.assignment_id = pac.assignment_id
407 and ppa.business_group_id = pay_magtape_generic.get_parameter_value('BUSINESS_GROUP_ID')
408 AND paa.business_group_id = ppa.business_group_id
409 and fai.context1 = pac.assignment_action_id
410 and fai.archive_item_id = ac2.archive_item_id and ac2.sequence_no = 2
411 order by paa.person_id;
412 ---------------------------------------------------------------------
413 end pay_sg_iras_archive;
[Home] [Help]