1 package pay_sg_iras_archive as
2 /* $Header: pysgirar.pkh 120.5.12010000.1 2008/07/27 23:41:21 appldev 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_basis_start in date,
138 p_basis_end in date );
139 --
140 procedure archive_people_flex
141 ( p_assignment_action_id in pay_assignment_actions.assignment_action_id%type,
142 p_person_id in per_all_people_f.person_id%type,
143 p_basis_start in date,
144 p_basis_end in date );
145 --
146 procedure archive_person_eits
147 ( p_assignment_action_id in pay_assignment_actions.assignment_action_id%type,
148 p_person_id in per_all_people_f.person_id%type,
149 p_basis_start in date,
150 p_basis_end in date );
151
152 --------------------------------------------------------------------------
153 -- Bug 5435088, Added for payroll date
154 --------------------------------------------------------------------------
155 procedure archive_payroll_date
156 ( p_assignment_action_id in pay_assignment_actions.assignment_action_id%type,
157 p_business_group_id in hr_organization_units.business_group_id%type,
158 p_legal_entity_id in hr_organization_units.organization_id%type,
159 p_person_id in per_all_people_f.person_id%type,
160 p_basis_year in varchar2);
161 --
162 --
163 --------------------------------------------------------------------------
164 -- Bug #4688761, added legal_entity_id
165 -- Bug 4890964, added assignment_id
166 --------------------------------------------------------------------------
167 procedure archive_assignment_eits
168 ( p_assignment_action_id in pay_assignment_actions.assignment_action_id%type,
169 p_person_id in per_all_people_f.person_id%type,
170 p_assignment_id in per_all_assignments_f.assignment_id%type,
171 p_legal_entity_id in hr_organization_units.organization_id%type,
172 p_basis_start in date,
173 p_basis_end in date );
174 --
175 --------------------------------------------------------------------------
176 -- Bug #5078454
177 --------------------------------------------------------------------------
178 procedure archive_ass_bonus_date_eits
179 ( p_assignment_action_id in pay_assignment_actions.assignment_action_id%type,
180 p_person_id in per_all_people_f.person_id%type,
181 p_assignment_id in per_all_assignments_f.assignment_id%type,
182 p_legal_entity_id in hr_organization_units.organization_id%type,
183 p_basis_start in date,
184 p_basis_end in date );
185 --
186 --------------------------------------------------------------------------
187 -- Bug 5435088
188 --------------------------------------------------------------------------
189 procedure archive_ass_payment_method
190 ( p_assignment_action_id in pay_assignment_actions.assignment_action_id%type,
191 p_person_id in per_all_people_f.person_id%type,
192 p_assignment_id in per_all_assignments_f.assignment_id%type,
193 p_legal_entity_id in hr_organization_units.organization_id%type,
194 p_basis_start in date,
195 p_basis_end in date);
196 --
197 -------------------------------------------------------------------------
198 -- Bug #4688761, added legal_entity_id
199 -------------------------------------------------------------------------
200 procedure archive_os_assignment
201 ( p_assignment_action_id in pay_assignment_actions.assignment_action_id%type,
202 p_person_id in per_all_people_f.person_id%type,
203 p_legal_entity_id in hr_organization_units.organization_id%type,
204 p_basis_start in date,
205 p_basis_end in date );
206 --
207 --------------------------------------------------------------------------
208 -- Bug 4890964, added assignment_id
209 --------------------------------------------------------------------------
210 procedure archive_job_designation
211 ( p_assignment_action_id in pay_assignment_actions.assignment_action_id%type,
212 p_person_id in per_all_people_f.person_id%type,
213 p_assignment_id in per_all_assignments_f.assignment_id%type,
214 p_legal_entity_id in hr_organization_units.organization_id%type,
215 p_basis_start in date,
216 p_basis_end in date,
217 p_er_designation_type in hr_organization_information.org_information17%type,
218 p_er_position_seg_type in hr_organization_information.org_information18%type );
219 --
220 ---------------------------------------------------------------------------
221 -- Bug #4314453, added legal_entity_id
222 ---------------------------------------------------------------------------
223 procedure archive_shares_details
224 ( p_assignment_action_id in pay_assignment_actions.assignment_action_id%type,
225 p_person_id in per_all_people_f.person_id%type,
226 p_tax_unit_id in ff_archive_item_contexts.context%type,
227 p_basis_start in date,
228 p_basis_end in date );
229 --
230 procedure archive_ir8s_c_details
231 ( p_assignment_action_id in pay_assignment_actions.assignment_action_id%type,
232 p_person_id in per_all_people_f.person_id%type,
233 p_tax_unit_id in ff_archive_item_contexts.context%type,
234 p_business_group_id in per_assignments_f.business_group_id%type,
235 p_basis_start in date,
236 p_basis_end in date );
237 --
238 procedure archive_ir8s_c_detail_moas
239 ( p_assignment_action_id in pay_assignment_actions.assignment_action_id%type,
240 p_1_person_id in per_all_people_f.person_id%type,
241 p_person_id in per_all_people_f.person_id%type,
242 p_tax_unit_id in ff_archive_item_contexts.context%type,
243 p_business_group_id in per_assignments_f.business_group_id%type,
244 p_basis_start in date,
245 p_basis_end in date );
246 --
247 procedure archive_item
248 ( p_user_entity_name in ff_user_entities.user_entity_name%type,
249 p_assignment_action_id in pay_assignment_actions.assignment_action_id%type,
250 p_archive_value in ff_archive_items.value%type );
251 --
252 procedure archive_item_2
253 ( p_user_entity_name in ff_user_entities.user_entity_name%type,
254 p_assignment_action_id in pay_assignment_actions.assignment_action_id%type,
255 p_archive_value in ff_archive_items.value%type,
256 p_context_value2 in ff_archive_item_contexts.context%type );
257 --
258 procedure archive_item_3
259 ( p_user_entity_name in ff_user_entities.user_entity_name%type,
260 p_assignment_action_id in pay_assignment_actions.assignment_action_id%type,
261 p_archive_value in ff_archive_items.value%type,
262 p_context_value2 in ff_archive_item_contexts.context%type,
263 p_context_value3 in ff_archive_item_contexts.context%type );
264 -------------------------------------------------------------------------------
265 -- Bug 3435334 - New function introduced for implementing duplicate person logic
266 -- with pre-processor
267 -------------------------------------------------------------------------------
268 function employee_if_latest
269 ( p_national_identifier in varchar2,
270 p_person_id in per_all_people_f.person_id%type,
271 p_setup_action_id in pay_payroll_actions.payroll_action_id%type,
272 p_report_type in varchar2 ) return boolean ;
273 --
274 ----------------------------------------------------------------------------
275 -- Bug 4688761 - New function to check if the person_id has been archived once
276 ----------------------------------------------------------------------------
277 function person_if_archived (p_person_id in per_all_people_f.person_id%type) return boolean;
278 --
279 ----------------------------------------------------------------------------
280 -- Bug 4890964 - The function to get the LE if it is with the latest primary
281 -- assignment for multi-LEs.
282 ----------------------------------------------------------------------------
283 function pri_if_latest
284 ( p_person_id in per_all_people_f.person_id%type
285 , p_tax_unit_id in ff_archive_item_contexts.context%type
286 , p_basis_start in date
287 , p_basis_end in date) return boolean;
288 --
289 ----------------------------------------------------------------------------
290 -- Bug 4890964 - The function to get the assignment with LE if its the
291 -- latest primary assignment
292 ----------------------------------------------------------------------------
293 function pri_LE_if_latest
294 ( p_person_id in per_all_people_f.person_id%type
295 , p_tax_unit_id in ff_archive_item_contexts.context%type
296 , p_basis_start in date
297 , p_basis_end in date) return number;
298 --
299 ----------------------------------------------------------------------------
300 -- Bug 4890964 - The function to get the assignment with LE if its the
301 -- latest assignment without primay flag defined.
302 ----------------------------------------------------------------------------
303 function id_LE_if_latest
304 ( p_person_id in per_all_people_f.person_id%type
305 , p_tax_unit_id in ff_archive_item_contexts.context%type
306 , p_basis_start in date
307 , p_basis_end in date) return number;
308 --
309 ---------------------------------------------------------------------------
310 -- Bug 5435088 - The function to check if a value is numeric
311 ---------------------------------------------------------------------------
312 function check_is_number (p_value in varchar2) return boolean;
313
314 ---------------------------------------------------------------------------
315 -- Bug 5435088 - The function to check if the payer id is valid
316 ---------------------------------------------------------------------------
317 function check_payee_id (p_ee_income_tax_number in varchar2,
318 p_payee_id_type in varchar2) return char;
319
320 ---------------------------------------------------------------------------
321 -- Bug 5435088 - The function to get country code
322 ---------------------------------------------------------------------------
323 function get_country_code (p_country in varchar2) return varchar2;
324
325
326 ---------------------------------------------------------------------------
327 -- Bug 5435088 - The function to check if the payee id is valid
328 ---------------------------------------------------------------------------
329 function check_payer_id (p_er_income_tax_number in varchar2,
330 p_er_payer_id in varchar2) return char;
331
332
333 ---------------------------------------------------------------------
334 -- PUBLIC cursors used to retrieve data and pass it to IRAS formulae.
335 -- Passes parameters to SG_ARCHIVE_HEADER formula
336 -- Just need 1 context for Org Info
337 ---------------------------------------------------------------------
338 cursor archive_header is
339 select 'ASSIGNMENT_ACTION_ID=C',
340 pac.assignment_action_id,
341 'BASIS_YEAR=P',
342 pay_core_utils.get_parameter('BASIS_YEAR', ppa.legislative_parameters) basis_year,
343 'CREATION_DATE=P',
344 to_char(sysdate,'YYYYMMDD') creation_date
345 from pay_payroll_actions ppa,
346 pay_assignment_actions pac,
347 ff_archive_items ffi,
348 ff_database_items fdi
349 where ppa.payroll_action_id = pay_magtape_generic.get_parameter_value('TRANSFER_PAYROLL_ACTION_ID')
350 and ppa.payroll_action_id = pac.payroll_action_id
351 and pac.action_status = 'C'
352 and ffi.context1 = pac.assignment_action_id /* Added joins for bug:3232300 */
353 and fdi.user_name = 'X_SG_LEGAL_ENTITY_SG_LEGAL_ENTITY_NAME'
354 and ffi.user_entity_id = fdi.user_entity_id
355 and rownum=1;
356 ---------------------------------------------------------------------
357 -- Passes parameters to SG_ARCHIVE_DETAILS formula
358 ---------------------------------------------------------------------
359 cursor archive_details is
360 select distinct
361 'ASSIGNMENT_ACTION_ID=C',
362 pac.assignment_action_id,
363 'TAX_UNIT_ID=C',
364 to_number(ac2.context) tax_unit_id,
365 'BASIS_YEAR=P',
366 pay_core_utils.get_parameter('BASIS_YEAR', ppa.legislative_parameters) basis_year,
367 'P_ASSIGNMENT_ACTION_ID=P',
368 to_char(pac.assignment_action_id),
369 'PERSON_ID=P',
370 to_char(paa.person_id) person_id,
371 'ASSIGNMENT_NUMBER=P',
372 paa.assignment_number assignment_number
373 from per_all_assignments_f paa,
374 pay_payroll_actions ppa,
375 pay_assignment_actions pac,
376 ff_archive_items fai,
377 ff_archive_item_contexts ac2
378 where ppa.payroll_action_id = pay_magtape_generic.get_parameter_value('TRANSFER_PAYROLL_ACTION_ID')
379 and pac.action_status = 'C'
380 and ppa.payroll_action_id = pac.payroll_action_id
381 and paa.assignment_id = pac.assignment_id
382 and ppa.business_group_id = pay_magtape_generic.get_parameter_value('BUSINESS_GROUP_ID')
383 and paa.business_group_id = ppa.business_group_id
384 and fai.context1 = pac.assignment_action_id
385 and fai.archive_item_id = ac2.archive_item_id and ac2.sequence_no = 2
386 order by paa.person_id;
387 ---------------------------------------------------------------------
388 -- Passes parameters to SG_ARCHIVE_DETAILS formula
389 ---------------------------------------------------------------------
390 cursor archive_org_details is
391 select distinct
392 'TAX_UNIT_ID=C',
393 to_number(ac2.context) tax_unit_id,
394 'BASIS_YEAR=P',
395 pay_core_utils.get_parameter('BASIS_YEAR', ppa.legislative_parameters) basis_year
396 from per_all_assignments_f paa,
397 pay_payroll_actions ppa,
398 pay_assignment_actions pac,
399 ff_archive_items fai,
400 ff_archive_item_contexts ac2
401 where ppa.payroll_action_id = pay_magtape_generic.get_parameter_value('TRANSFER_PAYROLL_ACTION_ID')
402 and pac.action_status = 'C'
403 and ppa.payroll_action_id = pac.payroll_action_id
404 and paa.assignment_id = pac.assignment_id
405 and ppa.business_group_id = pay_magtape_generic.get_parameter_value('BUSINESS_GROUP_ID')
406 AND paa.business_group_id = ppa.business_group_id
407 and fai.context1 = pac.assignment_action_id
408 and fai.archive_item_id = ac2.archive_item_id and ac2.sequence_no = 2
409 order by paa.person_id;
410 ---------------------------------------------------------------------
411 end pay_sg_iras_archive;
[Home] [Help]