[Home] [Help]
PACKAGE BODY: APPS.PAY_AU_PAYMENT_SUMMARY_AMEND
Source
1 PACKAGE BODY pay_au_payment_summary_amend AS
2 /* $Header: pyaupsam.pkb 120.8.12020000.3 2013/02/04 05:37:51 skshin ship $*/
3 /*
4 *** -------------------------------------------------------------------------+
5 *** Program: pay_au_payment_summary_amend (Package Body)
6 *** Description: Various procedures and functions to assist Amended
7 *** Payment Summary archival process and report
8 ***
9 *** Change History
10 *** Date Changed By Version Description of Change
11 *** -------- ---------- ------- --------------------------------------------+
12 *** 08-Jan-08 avenkatk 115.0 6470581 Initial Version
13 *** 22-Jan-08 avenkatk 115.2 6470581 Changes made as per review comments
14 *** 23-Jan-08 avenkatk 115.3 6470581 Resolved GSCC Errors
15 *** 13-May-09 pmatamsr 115.4 8315198 Modified cursors csr_payg_items ,csr_etp_cmn_items
16 *** and get_archived_user_entities to include X_ETP_DEATH_BENEFIT_TFN
17 *** and X_LUMP_SUM_A_PAYMENT_TYPE DB items as part of Payment Summary
18 *** changes.
19 *** 11-Dec-09 dduvvuri 115.5 9113084 Added RANGE_PERSON_ID for Amended Payment Summary Archive.
20 *** 01-Jun-10 pmatamsr 115.6 9764142 Modified cursor csr_etp_cmn_items to include X_SORT_EMPLOYEE_TYPE item.
21 *** 28-Jun-10 pmatamsr 115.7 9817894 Modified function check_user_entity_type to return X_PRE_JUL_83_COMPONENT_ASG_YTD
22 *** X_POST_JUN_83_TAXED_ASG_YTD and X_POST_JUN_83_UNTAXED_ASG_YTD items under ETP_CMN_BAL data record.
23 *** 23-Jun-10 skshin 115.9 9147430 Added Foreign Worker sections
24 *** 09-May-11 skshin 115.10 9817894 ETP person details added to csr_etp_cmn_items cursor
25 *** 07-Jun-11 keyazawa 115.11 12605912 modified cursor range_process_assignments for improving performance
26 *** 07-Dec-12 skshin 115.12 14703826 Modifed to compare new ETP balances
27 *** 04-Feb-13 skshin 115.13 14621185 Modified to support multiple Amended Archive runs on 12.1 onwards.
28 *** --------------------------------------------------------------------------+
29 */
30
31 g_debug boolean;
32 g_business_group_id number;
33 g_package constant varchar2(30) := 'pay_au_payment_summary_amend';
34 g_legislation_code constant varchar2(2) := 'AU';
35
36
37 TYPE char_tab_type IS TABLE OF ff_user_entities.user_entity_name%TYPE;
38
39 /* The following global tables store the User entity type of each DB Item */
40
41 g_payg_db_items char_tab_type;
42 g_etp1_db_items char_tab_type;
43 g_etp2_db_items char_tab_type;
44 g_etp3_db_items char_tab_type;
45 g_etp4_db_items char_tab_type;
46 g_etp_cmn_db_items char_tab_type;
47 g_fw1_payg_db_items char_tab_type; /*bug9147430*/
48 g_fw2_payg_db_items char_tab_type; /*bug9147430*/
49
50 /* The following variables hold the slotted DB Items */
51
52 l_cmn_tab_new archive_db_tab;
53 l_payg_tab_new archive_db_tab;
54 l_etp_cmn_tab_new archive_db_tab;
55 l_etp_1_tab_new archive_db_tab;
56 l_etp_2_tab_new archive_db_tab;
57 l_etp_3_tab_new archive_db_tab;
58 l_etp_4_tab_new archive_db_tab;
59 l_amend_types_new archive_db_tab;
60 l_fw1_payg_tab_new archive_db_tab; /*bug9147430*/
61 l_fw2_payg_tab_new archive_db_tab; /*bug9147430*/
62
63
64 /*
65 --------------------------------------------------------------------
66 Name : range_code
67 Type : Procedure
68 Access: Public
69 Description: This procedure returns a sql string to
70 select a range of assignments eligible for archival.
71 --------------------------------------------------------------------
72 */
73
74 PROCEDURE range_code
75 (p_payroll_action_id IN pay_payroll_actions.payroll_action_id%TYPE,
76 p_sql OUT NOCOPY VARCHAR2)
77 IS
78 BEGIN
79
80 g_debug := hr_utility.debug_enabled;
81
82 IF g_debug
83 THEN
84 hr_utility.set_location('Start of range_code ',1);
85 END IF;
86
87 p_sql := ' select distinct p.person_id' ||
88 ' from per_people_f p,' ||
89 ' pay_payroll_actions pa' ||
90 ' where pa.payroll_action_id = :payroll_action_id' ||
91 ' and p.business_group_id = pa.business_group_id' ||
92 ' order by p.person_id';
93
94 IF g_debug
95 THEN
96 hr_utility.set_location('End of range_code',2);
97 END IF;
98 END range_code;
99
100 /*
101 Bug 9113084 - Added Function range_person_on
102 --------------------------------------------------------------------
103 Name : range_person_on
104 Type : Function
105 Access: Private
106 Description: Checks if RANGE_PERSON_ID is enabled for
107 Archive process.
108 --------------------------------------------------------------------
109 */
110 FUNCTION range_person_on
111 RETURN BOOLEAN
112 IS
113
114 CURSOR csr_action_parameter is
115 select parameter_value
116 from pay_action_parameters
117 where parameter_name = 'RANGE_PERSON_ID';
118
119 CURSOR csr_range_format_param is
120 select par.parameter_value
121 from pay_report_format_parameters par,
122 pay_report_format_mappings_f map
123 where map.report_format_mapping_id = par.report_format_mapping_id
124 and map.report_type = 'AU_PAY_SUMM_AMEND'
125 and map.report_format = 'AU_PAY_SUMM_AMEND'
126 and map.report_qualifier = 'AU'
127 and par.parameter_name = 'RANGE_PERSON_ID';
128
129 l_return boolean;
130 l_action_param_val varchar2(30);
131 l_report_param_val varchar2(30);
132
133 BEGIN
134
135 g_debug := hr_utility.debug_enabled;
136
137 BEGIN
138
139 open csr_action_parameter;
140 fetch csr_action_parameter into l_action_param_val;
141 close csr_action_parameter;
142
143 open csr_range_format_param;
144 fetch csr_range_format_param into l_report_param_val;
145 close csr_range_format_param;
146
147 EXCEPTION WHEN NO_DATA_FOUND THEN
148 l_return := FALSE;
149 END;
150 --
151 IF l_action_param_val = 'Y' AND l_report_param_val = 'Y' THEN
152 l_return := TRUE;
153 IF g_debug THEN
154 hr_utility.set_location('Range Person = True',1);
155 END IF;
156 ELSE
157 l_return := FALSE;
158 END IF;
159 --
160 RETURN l_return;
161 --
162 END range_person_on;
163
164 /*
165 --------------------------------------------------------------------
166 Name : initialization_code
167 Type : Procedure
168 Access: Public
169 Description: This procedure initializes global variables required
170 by Archive. The g_payment_summary_type parameters
171 is set to 'A'
172 --------------------------------------------------------------------
173 */
174
175 PROCEDURE initialization_code
176 (p_payroll_action_id IN pay_payroll_actions.payroll_action_id%TYPE)
177 IS
178
179 l_procedure VARCHAR2(80);
180
181 BEGIN
182
183 g_debug := hr_utility.debug_enabled;
184 IF g_debug
185 THEN
186 l_procedure := g_package||'.initialization_code_amend';
187 hr_utility.set_location('In Procedure '||l_procedure,1000);
188 END IF;
189
190 pay_au_payment_summary.initialization_code(p_payroll_action_id);
191 pay_au_payment_summary.g_payment_summary_type := 'A'; /*Reset the Payment Summary Type Variable */
192 populate_user_entity_types; /* Initialize the DB Item Types */
193
194 IF g_debug
195 THEN
196 hr_utility.set_location('Leaving Procedure '||l_procedure,1000);
197 END IF;
198
199 EXCEPTION
200 WHEN others THEN
201 IF g_debug THEN
202 hr_utility.set_location('Error in initialization_code',1000);
203 END IF;
204 raise;
205 END initialization_code;
206
207
208 /*
209 --------------------------------------------------------------------
210 Name : assignment_action_code
211 Type : Procedure
212 Access: Public
213 Description:This procedure further restricts the assignment_id's
214 returned by range_code.
215 The procedure uses the Assignment ID or Assignment Set ID
216 parameter and restricts assignments to be archived
217 it then calls hr_nonrun.insact to create an assignment action id
218 --------------------------------------------------------------------
219 */
220
221 PROCEDURE assignment_action_code
222 (p_payroll_action_id IN pay_payroll_actions.payroll_action_id%TYPE,
223 p_start_person_id IN per_all_people_f.person_id%TYPE,
224 p_end_person_id IN per_all_people_f.person_id%TYPE,
225 p_chunk IN NUMBER)
226 IS
227
228 v_next_action_id pay_assignment_actions.assignment_action_id%type;
229
230 v_lst_year_start date ;
231 v_fbt_year_start date ;
232 v_lst_fbt_year_start date ;
233 v_fbt_year_end date ;
234 v_fin_year_start date ;
235 v_fin_year_end date ;
236 v_assignment_id varchar2(50);
237 v_registered_employer varchar2(50);
238 v_financial_year varchar2(50);
239 v_payroll_id varchar2(50);
240 v_employee_type varchar2(1);
241 v_asg_id number;
242 v_reg_emp number;
243 l_lst_yr_term varchar(10);
244
245 v_assignment_set_id VARCHAR2(50);
246 v_multiple_flag VARCHAR2(50);
247
248 l_procedure VARCHAR2(80);
249
250 CURSOR get_params(c_payroll_action_id per_all_assignments_f.assignment_id%TYPE)
251 IS
252 SELECT to_date('01-07-'||substr(pay_core_utils.get_parameter('FINANCIAL_YEAR',legislative_parameters),1,4),'DD-MM-YYYY') Financial_year_start
253 ,to_date('30-06-'||substr(pay_core_utils.get_parameter('FINANCIAL_YEAR',legislative_parameters),6,4),'DD-MM-YYYY') Financial_year_end
254 ,to_date('01-04-'||substr(pay_core_utils.get_parameter('FINANCIAL_YEAR',legislative_parameters),1,4),'DD-MM-YYYY') FBT_year_start
255 ,to_date('30-06-'||substr(pay_core_utils.get_parameter('FINANCIAL_YEAR',legislative_parameters),1,4),'DD-MM-YYYY') FBT_year_end
256 ,decode(pay_core_utils.get_parameter('EMPLOYEE_TYPE',legislative_parameters),'C','Y','T','N','B','%') Employee_type
257 ,pay_core_utils.get_parameter('REGISTERED_EMPLOYER',legislative_parameters) Registered_Employer
258 ,pay_core_utils.get_parameter('FINANCIAL_YEAR',legislative_parameters) Financial_year
259 ,pay_core_utils.get_parameter('ASSIGNMENT_ID',legislative_parameters) Assignment_id
260 ,decode(pay_core_utils.get_parameter('PAYROLL',legislative_parameters),NULL,'%',pay_core_utils.get_parameter('PAYROLL',legislative_parameters)) payroll_id
261 ,pay_core_utils.get_parameter('LST_YR_TERM',legislative_parameters) lst_yr_term /*3661230*/
262 ,pay_core_utils.get_parameter('BUSINESS_GROUP_ID',legislative_parameters) Business_group_id
263 ,pay_core_utils.get_parameter('ASSIGNMENT_SET_ID',legislative_parameters) assignment_set_id
264 ,pay_core_utils.get_parameter('MULTIPLE_AMENDED',legislative_parameters) multiple_amended /*14621185*/
265 FROM pay_payroll_actions
266 WHERE payroll_action_id = c_payroll_Action_id;
267
268 CURSOR process_assignments_only(c_payroll_action_id IN pay_payroll_actions.payroll_action_id%TYPE
269 ,c_start_person_id IN per_all_people_f.person_id%TYPE
270 ,c_end_person_id IN per_all_people_f.person_id%TYPE
271 ,c_assignment_id IN per_all_assignments_f.assignment_id%TYPE
272 ,c_financial_year IN VARCHAR2
273 ,c_tax_unit_id IN pay_assignment_actions.tax_unit_iD%TYPE)
274 IS
275 SELECT DISTINCT paf.assignment_id
276 FROM per_assignments_f paf
277 ,per_people_f ppf
278 ,pay_payroll_actions ppa
279 WHERE ppa.payroll_action_id = c_payroll_action_id
280 AND ppf.person_id BETWEEN c_start_person_id AND c_end_person_id
281 AND ppf.person_id = paf.person_id
282 AND paf.assignment_id = c_assignment_id
283 AND paf.business_group_id = ppa.business_group_id
284 AND EXISTS
285 ( /* Check if a Datafile is run for this year */
286 SELECT '1'
287 FROM pay_payroll_actions ppa1
288 ,pay_assignment_actions paa1
289 WHERE ppa1.payroll_action_id = paa1.payroll_action_id
290 AND ppa1.report_type = 'AU_PS_DATA_FILE'
291 AND ppa1.report_qualifier = 'AU'
292 AND ppa1.report_category = 'REPORT'
293 AND paa1.assignment_id = paf.assignment_id
294 AND pay_core_utils.get_parameter('FINANCIAL_YEAR',ppa1.legislative_parameters) = c_financial_year
295 AND pay_core_utils.get_parameter('REGISTERED_EMPLOYER',ppa1.legislative_parameters) = c_tax_unit_id
296 )
297 AND NOT EXISTS
298 ( /* Check if a locked Amended Payment Summary does not exist for this year */
299 SELECT '1'
300 FROM pay_payroll_actions ppa2
301 ,pay_assignment_actions paa2
302 ,pay_action_interlocks pai
303 WHERE ppa2.payroll_action_id = paa2.payroll_action_id
304 AND ppa2.report_type = 'AU_PAY_SUMM_AMEND'
305 AND ppa2.report_qualifier = 'AU'
306 AND ppa2.report_category = 'REPORT'
307 AND pay_core_utils.get_parameter('FINANCIAL_YEAR',ppa2.legislative_parameters) = c_financial_year
308 AND pay_core_utils.get_parameter('REGISTERED_EMPLOYER',ppa2.legislative_parameters) = c_tax_unit_id
309 AND paa2.assignment_id = paf.assignment_id
310 AND pai.locked_action_id = paa2.assignment_action_id
311 );
312
313 /* 14621185 - new cursor for multiple amended without checking the previous AU_PAY_SUMM_AMEND */
314 CURSOR multi_assignments_only(c_payroll_action_id IN pay_payroll_actions.payroll_action_id%TYPE
315 ,c_start_person_id IN per_all_people_f.person_id%TYPE
316 ,c_end_person_id IN per_all_people_f.person_id%TYPE
317 ,c_assignment_id IN per_all_assignments_f.assignment_id%TYPE
318 ,c_financial_year IN VARCHAR2
319 ,c_tax_unit_id IN pay_assignment_actions.tax_unit_iD%TYPE)
320 IS
321 SELECT DISTINCT paf.assignment_id
322 FROM per_assignments_f paf
323 ,per_people_f ppf
324 ,pay_payroll_actions ppa
325 WHERE ppa.payroll_action_id = c_payroll_action_id
326 AND ppf.person_id BETWEEN c_start_person_id AND c_end_person_id
327 AND ppf.person_id = paf.person_id
328 AND paf.assignment_id = c_assignment_id
329 AND paf.business_group_id = ppa.business_group_id
330 AND EXISTS
331 ( /* Check if a Datafile is run for this year */
332 SELECT '1'
333 FROM pay_payroll_actions ppa1
334 ,pay_assignment_actions paa1
335 WHERE ppa1.payroll_action_id = paa1.payroll_action_id
336 AND ppa1.report_type = 'AU_PS_DATA_FILE'
337 AND ppa1.report_qualifier = 'AU'
338 AND ppa1.report_category = 'REPORT'
339 AND paa1.assignment_id = paf.assignment_id
340 AND pay_core_utils.get_parameter('FINANCIAL_YEAR',ppa1.legislative_parameters) = c_financial_year
341 AND pay_core_utils.get_parameter('REGISTERED_EMPLOYER',ppa1.legislative_parameters) = c_tax_unit_id
342 )
343 ;
344
345 CURSOR process_assignments(c_payroll_action_id IN pay_payroll_actions.payroll_action_id%TYPE
346 ,c_start_person_id IN per_all_people_f.person_id%TYPE
347 ,c_end_person_id IN per_all_people_f.person_id%TYPE
348 ,c_assignment_set_id IN NUMBER
349 ,c_financial_year IN VARCHAR2
350 ,c_tax_unit_id IN pay_assignment_actions.tax_unit_iD%TYPE)
351 IS
352 SELECT DISTINCT paf.assignment_id
353 FROM per_assignments_f paf
354 ,per_people_f ppf
355 ,pay_payroll_actions ppa
356 ,hr_assignment_set_amendments has
357 WHERE ppa.payroll_action_id = c_payroll_action_id
358 AND ppf.person_id BETWEEN c_start_person_id AND c_end_person_id
359 AND ppf.person_id = paf.person_id
360 AND paf.assignment_id = has.assignment_id
361 AND has.assignment_set_id = c_assignment_set_id
362 AND upper(has.include_or_exclude) = 'I'
363 AND paf.business_group_id = ppa.business_group_id
364 AND EXISTS
365 ( /* Check if a Datafile is run for this year */
366 SELECT '1'
367 FROM pay_payroll_actions ppa1
368 ,pay_assignment_actions paa1
369 WHERE ppa1.payroll_action_id = paa1.payroll_action_id
370 AND ppa1.report_type = 'AU_PS_DATA_FILE'
371 AND ppa1.report_qualifier = 'AU'
372 AND ppa1.report_category = 'REPORT'
373 AND paa1.assignment_id = paf.assignment_id
374 AND pay_core_utils.get_parameter('FINANCIAL_YEAR',ppa1.legislative_parameters) = c_financial_year
375 AND pay_core_utils.get_parameter('REGISTERED_EMPLOYER',ppa1.legislative_parameters) = c_tax_unit_id
376 )
377 AND NOT EXISTS
378 ( /* Check if a locked Amended Payment Summary does not exist for this year */
379 SELECT '1'
380 FROM pay_payroll_actions ppa2
381 ,pay_assignment_actions paa2
382 ,pay_action_interlocks pai
383 WHERE ppa2.payroll_action_id = paa2.payroll_action_id
384 AND ppa2.report_type = 'AU_PAY_SUMM_AMEND'
385 AND ppa2.report_qualifier = 'AU'
386 AND ppa2.report_category = 'REPORT'
387 AND pay_core_utils.get_parameter('FINANCIAL_YEAR',ppa2.legislative_parameters) = c_financial_year
388 AND pay_core_utils.get_parameter('REGISTERED_EMPLOYER',ppa2.legislative_parameters) = c_tax_unit_id
389 AND paa2.assignment_id = paf.assignment_id
390 AND pai.locked_action_id = paa2.assignment_action_id
391 );
392
393 /* 14621185 - new cursor for multiple amended without checking the previous AU_PAY_SUMM_AMEND */
394 CURSOR multi_assignments(c_payroll_action_id IN pay_payroll_actions.payroll_action_id%TYPE
395 ,c_start_person_id IN per_all_people_f.person_id%TYPE
396 ,c_end_person_id IN per_all_people_f.person_id%TYPE
397 ,c_assignment_set_id IN NUMBER
398 ,c_financial_year IN VARCHAR2
399 ,c_tax_unit_id IN pay_assignment_actions.tax_unit_iD%TYPE)
400 IS
401 SELECT DISTINCT paf.assignment_id
402 FROM per_assignments_f paf
403 ,per_people_f ppf
404 ,pay_payroll_actions ppa
405 ,hr_assignment_set_amendments has
406 WHERE ppa.payroll_action_id = c_payroll_action_id
407 AND ppf.person_id BETWEEN c_start_person_id AND c_end_person_id
408 AND ppf.person_id = paf.person_id
409 AND paf.assignment_id = has.assignment_id
410 AND has.assignment_set_id = c_assignment_set_id
411 AND upper(has.include_or_exclude) = 'I'
412 AND paf.business_group_id = ppa.business_group_id
413 AND EXISTS
414 ( /* Check if a Datafile is run for this year */
415 SELECT '1'
416 FROM pay_payroll_actions ppa1
417 ,pay_assignment_actions paa1
418 WHERE ppa1.payroll_action_id = paa1.payroll_action_id
419 AND ppa1.report_type = 'AU_PS_DATA_FILE'
420 AND ppa1.report_qualifier = 'AU'
421 AND ppa1.report_category = 'REPORT'
422 AND paa1.assignment_id = paf.assignment_id
423 AND pay_core_utils.get_parameter('FINANCIAL_YEAR',ppa1.legislative_parameters) = c_financial_year
424 AND pay_core_utils.get_parameter('REGISTERED_EMPLOYER',ppa1.legislative_parameters) = c_tax_unit_id
425 )
426 ;
427
428 /* 9113084 - Added range person cursor for the above CURSOR process_assignments */
429 /* 9113084 - Cursor fetches the assignments for Amended Payment Summary Archive when RANGE_PERSON_ID is enabled */
430 CURSOR range_process_assignments(c_payroll_action_id IN pay_payroll_actions.payroll_action_id%TYPE
431 , c_chunk IN NUMBER
432 ,c_assignment_set_id IN NUMBER
433 ,c_financial_year IN VARCHAR2
434 ,c_tax_unit_id IN pay_assignment_actions.tax_unit_iD%TYPE)
435 IS
436 SELECT /*+ ORDERED
437 USE_NL(PPA, PPR, PAF, HAS)
438 INDEX(PPA PAY_PAYROLL_ACTIONS_PK)
439 INDEX(PPR PAY_POPULATION_RANGES_N4)
440 INDEX(PAF PER_ASSIGNMENTS_F_N12)
441 INDEX(HAS HR_ASSIGNMENT_SET_AMENDMEN_PK) */
442 paf.assignment_id
443 FROM pay_payroll_actions ppa,
444 pay_population_ranges ppr,
445 per_assignments_f paf,
446 hr_assignment_set_amendments has
447 WHERE ppa.payroll_action_id = c_payroll_action_id
448 AND ppr.payroll_action_id = ppa.payroll_action_id
449 AND ppr.chunk_number = c_chunk
450 AND paf.person_id = ppr.person_id
451 AND paf.business_group_id = ppa.business_group_id + 0
452 AND has.assignment_id = paf.assignment_id
453 AND has.assignment_set_id = c_assignment_set_id
454 AND upper(has.include_or_exclude) = 'I'
455 and not exists(
456 select null
457 from per_all_assignments_f pa2
458 where pa2.assignment_id = has.assignment_id
459 and pa2.effective_start_date > paf.effective_start_date)
460 AND EXISTS (
461 /* Check if a Datafile is run for this year */
462 SELECT /*+ ORDERED
463 USE_NL(PPA1, PAA1)
464 INDEX(PPA1 PAY_PAYROLL_ACTIONS_N52)
465 INDEX(PAA1 PAY_ASSIGNMENT_ACTIONS_N51) */
466 '1'
467 FROM pay_payroll_actions ppa1,
468 pay_assignment_actions paa1
469 WHERE ppa1.report_type = 'AU_PS_DATA_FILE'
470 AND ppa1.report_qualifier = 'AU'
471 AND ppa1.report_category = 'REPORT'
472 AND pay_core_utils.get_parameter('FINANCIAL_YEAR',ppa1.legislative_parameters) = c_financial_year
473 AND pay_core_utils.get_parameter('REGISTERED_EMPLOYER',ppa1.legislative_parameters) = c_tax_unit_id
474 AND paa1.payroll_action_id = nvl(ppa1.payroll_action_id,ppa.payroll_action_id)
475 AND paa1.assignment_id = has.assignment_id)
476 AND NOT EXISTS (
477 /* Check if a locked Amended Payment Summary does not exist for this year */
478 SELECT /*+ ORDERED
479 USE_NL(PPA2, PAA2, PAI)
480 INDEX(PPA2 PAY_PAYROLL_ACTIONS_N52)
481 INDEX(PAA2 PAY_ASSIGNMENT_ACTIONS_N51)
482 INDEX(PAI PAY_ACTION_INTERLOCKS_FK2) */
483 '1'
484 FROM pay_payroll_actions ppa2,
485 pay_assignment_actions paa2,
486 pay_action_interlocks pai
487 WHERE ppa2.report_type = 'AU_PAY_SUMM_AMEND'
488 AND ppa2.report_qualifier = 'AU'
489 AND ppa2.report_category = 'REPORT'
490 AND pay_core_utils.get_parameter('FINANCIAL_YEAR',ppa2.legislative_parameters) = c_financial_year
491 AND pay_core_utils.get_parameter('REGISTERED_EMPLOYER',ppa2.legislative_parameters) = c_tax_unit_id
492 AND paa2.payroll_action_id = nvl(ppa2.payroll_action_id,ppa.payroll_action_id)
493 AND paa2.assignment_id = has.assignment_id
494 AND pai.locked_action_id = paa2.assignment_action_id);
495
496 /* 14621185 - new cursor for multiple amended without checking the previous AU_PAY_SUMM_AMEND */
497 CURSOR multi_range_assignments(c_payroll_action_id IN pay_payroll_actions.payroll_action_id%TYPE
498 , c_chunk IN NUMBER
499 ,c_assignment_set_id IN NUMBER
500 ,c_financial_year IN VARCHAR2
501 ,c_tax_unit_id IN pay_assignment_actions.tax_unit_iD%TYPE)
502 IS
503 SELECT /*+ ORDERED
504 USE_NL(PPA, PPR, PAF, HAS)
505 INDEX(PPA PAY_PAYROLL_ACTIONS_PK)
506 INDEX(PPR PAY_POPULATION_RANGES_N4)
507 INDEX(PAF PER_ASSIGNMENTS_F_N12)
508 INDEX(HAS HR_ASSIGNMENT_SET_AMENDMEN_PK) */
509 paf.assignment_id
510 FROM pay_payroll_actions ppa,
511 pay_population_ranges ppr,
512 per_assignments_f paf,
513 hr_assignment_set_amendments has
514 WHERE ppa.payroll_action_id = c_payroll_action_id
515 AND ppr.payroll_action_id = ppa.payroll_action_id
516 AND ppr.chunk_number = c_chunk
517 AND paf.person_id = ppr.person_id
518 AND paf.business_group_id = ppa.business_group_id + 0
519 AND has.assignment_id = paf.assignment_id
520 AND has.assignment_set_id = c_assignment_set_id
521 AND upper(has.include_or_exclude) = 'I'
522 and not exists(
523 select null
524 from per_all_assignments_f pa2
525 where pa2.assignment_id = has.assignment_id
526 and pa2.effective_start_date > paf.effective_start_date)
527 AND EXISTS (
528 /* Check if a Datafile is run for this year */
529 SELECT /*+ ORDERED
530 USE_NL(PPA1, PAA1)
531 INDEX(PPA1 PAY_PAYROLL_ACTIONS_N52)
532 INDEX(PAA1 PAY_ASSIGNMENT_ACTIONS_N51) */
533 '1'
534 FROM pay_payroll_actions ppa1,
535 pay_assignment_actions paa1
536 WHERE ppa1.report_type = 'AU_PS_DATA_FILE'
537 AND ppa1.report_qualifier = 'AU'
538 AND ppa1.report_category = 'REPORT'
539 AND pay_core_utils.get_parameter('FINANCIAL_YEAR',ppa1.legislative_parameters) = c_financial_year
540 AND pay_core_utils.get_parameter('REGISTERED_EMPLOYER',ppa1.legislative_parameters) = c_tax_unit_id
541 AND paa1.payroll_action_id = nvl(ppa1.payroll_action_id,ppa.payroll_action_id)
542 AND paa1.assignment_id = has.assignment_id)
543 ;
544
545 CURSOR next_action_id
546 IS
547 SELECT pay_assignment_actions_s.nextval
548 FROM dual;
549
550 BEGIN
551
552 g_debug := hr_utility.debug_enabled;
553
554 IF g_debug
555 THEN
556 l_procedure := g_package||'.assignment_action_coded';
557 hr_utility.set_location('In Procedure '||l_procedure,1020);
558 END IF;
559
560 /* Get the paramters for archival process */
561 OPEN get_params(p_payroll_action_id);
562 FETCH get_params
563 INTO v_fin_year_start
564 ,v_fin_year_end
565 ,v_fbt_year_start
566 ,v_fbt_year_end
567 ,v_employee_type
568 ,v_registered_employer
569 ,v_financial_year
570 ,v_assignment_id
571 ,v_payroll_id
572 ,l_lst_yr_term
573 ,g_business_group_id
574 ,v_assignment_set_id
575 ,v_multiple_flag;
576 CLOSE get_params;
577
578 v_reg_emp := to_number(v_registered_employer);
579 v_multiple_flag := nvl(v_multiple_flag, 'N'); /* 14621185 */
580
581 IF g_debug
582 THEN
583 hr_utility.set_location('p_payroll_action_id '||p_payroll_action_id,1030);
584 hr_utility.set_location('p_start_person_id '||p_start_person_id,1030);
585 hr_utility.set_location('p_end_person_id '||p_end_person_id,1030);
586 hr_utility.set_location('v_assignment_set_id '||to_number(v_assignment_set_id),1030);
587 hr_utility.set_location('v_financial_year '||v_financial_year,1030);
588 hr_utility.set_location('v_assignment_id '||v_assignment_id,1030);
589 hr_utility.set_location('v_reg_emp '||v_reg_emp,1030);
590 hr_utility.set_location('v_multiple_flag '||v_multiple_flag,1030);
591 END IF;
592
593 IF v_multiple_flag = 'Y' THEN /* 14621185 - 12.1 release onwards only */
594 IF v_assignment_id IS NOT NULL
595 THEN
596 FOR csr_rec IN multi_assignments_only(p_payroll_action_id
597 ,p_start_person_id
598 ,p_end_person_id
599 ,to_number(v_assignment_id)
600 ,v_financial_year
601 ,v_reg_emp)
602 LOOP
603 OPEN next_action_id;
604 FETCH next_action_id INTO v_next_action_id;
605 CLOSE next_action_id;
606
607 hr_nonrun_asact.insact(v_next_action_id,
608 csr_rec.assignment_id,
609 p_payroll_action_id,
610 p_chunk,
611 NULL);
612
613 END LOOP;
614 ELSIF v_assignment_set_id IS NOT NULL
615 THEN
616
617 IF range_person_on THEN /* 9113084 - Use new Range Person Cursor if Range Person is enabled */
618 IF g_debug THEN
619 hr_utility.set_location('Using Range Person Cursor for fetching assignments ', 5);
620 END IF;
621 FOR csr_rec IN multi_range_assignments(p_payroll_action_id
622 ,p_chunk
623 ,to_number(v_assignment_set_id)
624 ,v_financial_year
625 ,v_reg_emp)
626 LOOP
627 OPEN next_action_id;
628 FETCH next_action_id INTO v_next_action_id;
629 CLOSE next_action_id;
630
631 hr_nonrun_asact.insact(v_next_action_id,
632 csr_rec.assignment_id,
633 p_payroll_action_id,
634 p_chunk,
635 NULL);
636 END LOOP;
637 ELSE /* 9113084 - Old Logic to be used when Range Person is disabled */
638
639 FOR csr_rec IN multi_assignments(p_payroll_action_id
640 ,p_start_person_id
641 ,p_end_person_id
642 ,to_number(v_assignment_set_id)
643 ,v_financial_year
644 ,v_reg_emp)
645 LOOP
646 OPEN next_action_id;
647 FETCH next_action_id INTO v_next_action_id;
648 CLOSE next_action_id;
649
650 hr_nonrun_asact.insact(v_next_action_id,
651 csr_rec.assignment_id,
652 p_payroll_action_id,
653 p_chunk,
654 NULL);
655 END LOOP;
656 END IF;
657
658 END IF;
659 ELSE
660 IF v_assignment_id IS NOT NULL
661 THEN
662 FOR csr_rec IN process_assignments_only(p_payroll_action_id
663 ,p_start_person_id
664 ,p_end_person_id
665 ,to_number(v_assignment_id)
666 ,v_financial_year
667 ,v_reg_emp)
668 LOOP
669 OPEN next_action_id;
670 FETCH next_action_id INTO v_next_action_id;
671 CLOSE next_action_id;
672
673 hr_nonrun_asact.insact(v_next_action_id,
674 csr_rec.assignment_id,
675 p_payroll_action_id,
676 p_chunk,
677 NULL);
678
679 END LOOP;
680 ELSIF v_assignment_set_id IS NOT NULL
681 THEN
682
683 IF range_person_on THEN /* 9113084 - Use new Range Person Cursor if Range Person is enabled */
684 IF g_debug THEN
685 hr_utility.set_location('Using Range Person Cursor for fetching assignments ', 15);
686 END IF;
687 FOR csr_rec IN range_process_assignments(p_payroll_action_id
688 ,p_chunk
689 ,to_number(v_assignment_set_id)
690 ,v_financial_year
691 ,v_reg_emp)
692 LOOP
693 OPEN next_action_id;
694 FETCH next_action_id INTO v_next_action_id;
695 CLOSE next_action_id;
696
697 hr_nonrun_asact.insact(v_next_action_id,
698 csr_rec.assignment_id,
699 p_payroll_action_id,
700 p_chunk,
701 NULL);
702 END LOOP;
703 ELSE /* 9113084 - Old Logic to be used when Range Person is disabled */
704
705 FOR csr_rec IN process_assignments(p_payroll_action_id
706 ,p_start_person_id
707 ,p_end_person_id
708 ,to_number(v_assignment_set_id)
709 ,v_financial_year
710 ,v_reg_emp)
711 LOOP
712 OPEN next_action_id;
713 FETCH next_action_id INTO v_next_action_id;
714 CLOSE next_action_id;
715
716 hr_nonrun_asact.insact(v_next_action_id,
717 csr_rec.assignment_id,
718 p_payroll_action_id,
719 p_chunk,
720 NULL);
721 END LOOP;
722 END IF;
723
724 END IF;
725 END IF;
726
727
728 IF g_debug THEN
729 hr_utility.set_location('Leaving '||l_procedure,1040);
730 END IF;
731
732 EXCEPTION
733 WHEN others THEN
734 IF g_debug THEN
735 hr_utility.set_location('Error raised in assignment_action_code_amend procedure ',1050);
736 END IF;
737 raise;
738 END assignment_action_code;
739
740
741 /*
742 --------------------------------------------------------------------
743 Name : populate_user_entity_types
744 Type : Procedure
745 Access: Public
746 Description:This procedure populates the Global PL/SQL table with
747 the User Entity type of all shipped DB items.
748 PAYG - PAYG Record
749 ETP_CMN - Common data reported in all ETP records
750 ETP1 - Transtional (Y), Part of Prev Term (Y) ETP Record
751 ETP2 - Transtional (Y), Part of Prev Term (N) ETP Record
752 ETP3 - Transtional (N), Part of Prev Term (Y) ETP Record
753 ETP4 - Transtional (N), Part of Prev Term (N) ETP Record
754 --------------------------------------------------------------------
755 */
756
757 /*Bug 8315198 - Modified cursor csr_payg_items to include X_LUMP_SUM_A_PAYMENT_TYPE DB item for Amended archive process*/
758
759 PROCEDURE populate_user_entity_types
760 IS
761
762 CURSOR csr_payg_items
763 IS
764 SELECT user_entity_name
765 FROM ff_user_entities
766 WHERE legislation_code = 'AU'
767 AND ( user_entity_name LIKE 'X_ALLOWANCE%'
768 OR user_entity_name LIKE 'X_EMPLOYEE%DATE%'
769 OR user_entity_name LIKE 'X_UNION%'
770 OR user_entity_name LIKE 'X_%ASG_YTD'
771 OR user_entity_name IN ('X_EMPLOYEE_TAX_FILE_NUMBER')
772 OR user_entity_name IN ('X_LUMP_SUM_A_PAYMENT_TYPE')
773 )
774 AND user_entity_name NOT LIKE 'X_%83%_ASG_YTD'
775 AND user_entity_name NOT LIKE 'X_%TRANS%_ASG_YTD'
776 AND user_entity_name NOT LIKE 'X_%EXCL%_ASG_YTD' /*bug 14703826*/
777 AND user_entity_name NOT IN ('X_LUMP_SUM_C_PAYMENTS_ASG_YTD','X_LUMP_SUM_C_DEDUCTIONS_ASG_YTD','X_INVALIDITY_PAYMENTS_ASG_YTD')
778 AND user_entity_name NOT LIKE 'X_FW%';
779
780 /* start bug 14703826 - Modifed to check new ETP balances */
781 CURSOR csr_etp1_items
782 IS
783 SELECT user_entity_name
784 FROM ff_user_entities
785 WHERE legislation_code = 'AU'
786 AND ( user_entity_name IN ( 'X_ETP_DED_EXCL_ASG_YTD','X_INV_PAY_EXCL_ASG_YTD'
787 ,'X_POST_JUN_83_TAXED_EXCL_ASG_YTD','X_PRE_JUL_83_COMP_EXCL_ASG_YTD')
788 OR user_entity_name LIKE 'X%EXCL%' )
789 AND user_entity_name NOT LIKE 'X%NON_EXCL%'
790 AND user_entity_name NOT LIKE 'X%EXCL_PP%'
791 ;
792
793 CURSOR csr_etp2_items
794 IS
795 SELECT user_entity_name
796 FROM ff_user_entities
797 WHERE legislation_code = 'AU'
798 AND ( user_entity_name IN ( 'X_ETP_DED_EXCL_PP_ASG_YTD','X_INV_PAY_EXCL_PP_ASG_YTD'
799 ,'X_POST_JUN_83_TAXED_EXCL_PP_ASG_YTD','X_PRE_JUL_83_COMP_EXCL_PP_ASG_YTD')
800 OR user_entity_name LIKE 'X%EXCL_PP%')
801 AND user_entity_name NOT LIKE 'X%NON_EXCL%'
802 ;
803
804 CURSOR csr_etp3_items
805 IS
806 SELECT user_entity_name
807 FROM ff_user_entities
808 WHERE legislation_code = 'AU'
809 AND ( user_entity_name IN ( 'X_ETP_DED_NON_EXCL_ASG_YTD',
810 'X_POST_JUN_83_TAXED_NON_EXCL_ASG_YTD','X_PRE_JUL_83_COMP_NON_EXCL_ASG_YTD')
811 OR user_entity_name LIKE 'X%\_NE\_%' escape '\')
812 AND user_entity_name NOT LIKE 'X%\_NE\_PP%' escape '\'
813 ;
814
815 CURSOR csr_etp4_items
816 IS
817 SELECT user_entity_name
818 FROM ff_user_entities
819 WHERE legislation_code = 'AU'
820 AND ( user_entity_name IN ( 'X_ETP_DED_NOT_TRANS_NOT_PPTERM_ASG_YTD'
821 ,'X_POST_JUN_83_TAXED_NON_EXCL_PP_ASG_YTD','X_PRE_JUL_83_COMP_NON_EXCL_PP_ASG_YTD')
822 OR user_entity_name LIKE 'X%\_NE\_PP%' escape '\' )
823 ;
824
825 /* end bug 14703826 */
826
827 /*Bug 8315198 - Modified cursor csr_etp_cmn_items to include X_ETP_DEATH_BENEFIT_TFN DB item for Amended archive process*/
828 /*Bug 9764142 - Added user entity X_SORT_EMPLOYEE_TYPE to cursor such that the item is considered during Amended Archive process */
829
830 CURSOR csr_etp_cmn_items
831 IS
832 SELECT user_entity_name
833 FROM ff_user_entities
834 WHERE legislation_code = 'AU'
835 AND ( user_entity_name LIKE 'X_ETP%DATE'
836 OR user_entity_name LIKE 'X_DAYS%'
837 OR user_entity_name IN ('X_ETP_TAX_FILE_NUMBER')
838 OR user_entity_name IN ('X_ETP_DEATH_BENEFIT_TFN')
839 OR user_entity_name IN ('X_SORT_EMPLOYEE_TYPE')
840 OR user_entity_name LIKE ('X_ETP_EMPLOYEE%') -- bug9817894
841 );
842
843 /*bug9147430 Added new cursors for FW */
844 CURSOR csr_fw1_payg_items
845 IS
846 SELECT user_entity_name
847 FROM ff_user_entities
848 WHERE legislation_code = 'AU'
849 AND user_entity_name LIKE 'X_FW1%';
850
851 CURSOR csr_fw2_payg_items
852 IS
853 SELECT user_entity_name
854 FROM ff_user_entities
855 WHERE legislation_code = 'AU'
856 AND user_entity_name LIKE 'X_FW2%';
857
858
859 l_procedure VARCHAR2(200);
860
861 BEGIN
862
863 g_debug := hr_utility.debug_enabled;
864 IF g_debug
865 THEN
866 l_procedure := g_package||'.populate_user_entity_types';
867 hr_utility.set_location('Entering Procedure '||l_procedure,2400);
868 END IF;
869
870 OPEN csr_payg_items;
871 FETCH csr_payg_items BULK COLLECT INTO g_payg_db_items;
872 CLOSE csr_payg_items;
873
874 OPEN csr_etp_cmn_items;
875 FETCH csr_etp_cmn_items BULK COLLECT INTO g_etp_cmn_db_items;
876 CLOSE csr_etp_cmn_items ;
877
878 OPEN csr_etp1_items;
879 FETCH csr_etp1_items BULK COLLECT INTO g_etp1_db_items;
880 CLOSE csr_etp1_items ;
881
882 OPEN csr_etp2_items;
883 FETCH csr_etp2_items BULK COLLECT INTO g_etp2_db_items;
884 CLOSE csr_etp2_items ;
885
886 OPEN csr_etp3_items;
887 FETCH csr_etp3_items BULK COLLECT INTO g_etp3_db_items;
888 CLOSE csr_etp3_items ;
889
890 OPEN csr_etp4_items;
891 FETCH csr_etp4_items BULK COLLECT INTO g_etp4_db_items;
892 CLOSE csr_etp4_items ;
893
894 OPEN csr_fw1_payg_items; /*bug9147430*/
895 FETCH csr_fw1_payg_items BULK COLLECT INTO g_fw1_payg_db_items;
896 CLOSE csr_fw1_payg_items;
897
898 OPEN csr_fw2_payg_items; /*bug9147430*/
899 FETCH csr_fw2_payg_items BULK COLLECT INTO g_fw2_payg_db_items;
900 CLOSE csr_fw2_payg_items;
901
902 IF g_debug
903 THEN
904 hr_utility.set_location('Leaving Procedure '||l_procedure,2420);
905 END IF;
906 END populate_user_entity_types;
907
908 /*
909 --------------------------------------------------------------------
910 Name : check_user_entity_type
911 Type : Function
912 Access: Public
913 Description:This procedure takes a User Entity Name and returns the
914 Data file record which corresponds to the ITEM.
915 Values returned,
916 PAYG - PAYG Record
917 ETP_CMN - Common data reported in all ETP records
918 ETP1 - Transtional (Y), Part of Prev Term (Y) ETP Record
919 ETP2 - Transtional (Y), Part of Prev Term (N) ETP Record
920 ETP3 - Transtional (N), Part of Prev Term (Y) ETP Record
921 ETP4 - Transtional (N), Part of Prev Term (N) ETP Record
922 ETP_CMN_BAL - ETP Balances - not used anymore now
923 AMEND - Amend PS Flag Items
924 CMN - Rest of the Items (Default Value returned)
925 --------------------------------------------------------------------
926 */
927
928 /* Bug 9817894 - Modifed function such that X_PRE_JUL_83_COMPONENT_ASG_YTD,X_POST_JUN_83_TAXED_ASG_YTD,X_POST_JUN_83_UNTAXED_ASG_YTD
929 user entities will be returned under ETP_CMN_BAL data file record */
930
931 FUNCTION check_user_entity_type(p_user_entity_name IN ff_user_entities.user_entity_name%TYPE)
932 RETURN VARCHAR2
933 IS
934
935
936 l_return_value VARCHAR2(20);
937 l_procedure VARCHAR2(80);
938
939 l_entity_id ff_user_entities.user_entity_id%TYPE;
940 l_found BOOLEAN;
941
942 BEGIN
943
944 g_debug := hr_utility.debug_enabled;
945
946 IF g_debug
947 THEN
948 l_procedure := g_package||'.check_user_entity_type';
949 hr_utility.set_location('Entering Procedure '||l_procedure,2500);
950 hr_utility.set_location('p_user_entity_name '||p_user_entity_name,2510);
951 END IF;
952
953 IF p_user_entity_name IN ('X_PAYG_PAYMENT_SUMMARY_TYPE','X_PAYMENT_SUMMARY_TYPE','X_ETP1_PAYMENT_SUMMARY_TYPE'
954 ,'X_ETP2_PAYMENT_SUMMARY_TYPE','X_ETP3_PAYMENT_SUMMARY_TYPE','X_ETP4_PAYMENT_SUMMARY_TYPE'
955 ,'X_FW1_PAYMENT_SUMMARY_TYPE','X_FW2_PAYMENT_SUMMARY_TYPE') /*bug9147430*/
956 THEN
957 l_return_value := 'AMEND';
958
959 ELSIF p_user_entity_name IN ('X_LUMP_SUM_C_PAYMENTS_ASG_YTD','X_LUMP_SUM_C_DEDUCTIONS_ASG_YTD',
960 'X_INVALIDITY_PAYMENTS_ASG_YTD','X_PRE_JUL_83_COMPONENT_ASG_YTD','X_POST_JUN_83_TAXED_ASG_YTD','X_POST_JUN_83_UNTAXED_ASG_YTD')
961 THEN
962 l_return_value := 'ETP_CMN_BAL';
963 ELSE
964
965 /* ETP Items can have values
966 1. ETP1 (YY)
967 2. ETP2 (YN)
968 3. ETP3 (NY)
969 4. ETP4 (NN)
970 5. ETP_CMN (Rest of the Common Items)
971 */
972
973 l_found := FALSE;
974 IF (l_found = FALSE AND g_payg_db_items.COUNT > 0)
975 THEN
976 FOR i IN g_payg_db_items.FIRST..g_payg_db_items.LAST
977 LOOP
978 IF (g_payg_db_items(i) = p_user_entity_name)
979 THEN
980 l_found := TRUE;
981 l_return_value := 'PAYG';
982 END IF;
983 END LOOP;
984 END IF;
985
986 IF (l_found = FALSE AND g_etp_cmn_db_items.COUNT > 0)
987 THEN
988 FOR i IN g_etp_cmn_db_items.FIRST..g_etp_cmn_db_items.LAST
989 LOOP
990 IF (g_etp_cmn_db_items(i) = p_user_entity_name)
991 THEN
992 l_found := TRUE;
993 l_return_value := 'ETP_CMN';
994 END IF;
995 END LOOP;
996 END IF;
997
998 IF (l_found = FALSE AND g_etp1_db_items.COUNT > 0)
999 THEN
1000 FOR i IN g_etp1_db_items.FIRST..g_etp1_db_items.LAST
1001 LOOP
1002 IF (g_etp1_db_items(i) = p_user_entity_name)
1003 THEN
1004 l_found := TRUE;
1005 l_return_value := 'ETP1';
1006 END IF;
1007 END LOOP;
1008 END IF;
1009
1010 IF (l_found = FALSE AND g_etp2_db_items.COUNT > 0)
1011 THEN
1012 FOR i IN g_etp2_db_items.FIRST..g_etp2_db_items.LAST
1013 LOOP
1014 IF (g_etp2_db_items(i) = p_user_entity_name)
1015 THEN
1016 l_found := TRUE;
1017 l_return_value := 'ETP2';
1018 END IF;
1019 END LOOP;
1020 END IF;
1021
1022
1023 IF (l_found = FALSE AND g_etp3_db_items.COUNT > 0)
1024 THEN
1025 FOR i IN g_etp3_db_items.FIRST..g_etp3_db_items.LAST
1026 LOOP
1027 IF (g_etp3_db_items(i) = p_user_entity_name)
1028 THEN
1029 l_found := TRUE;
1030 l_return_value := 'ETP3';
1031 END IF;
1032 END LOOP;
1033 END IF;
1034
1035 IF (l_found = FALSE AND g_etp4_db_items.COUNT > 0)
1036 THEN
1037 FOR i IN g_etp4_db_items.FIRST..g_etp4_db_items.LAST
1038 LOOP
1039 IF (g_etp4_db_items(i) = p_user_entity_name)
1040 THEN
1041 l_found := TRUE;
1042 l_return_value := 'ETP4';
1043 END IF;
1044 END LOOP;
1045 END IF;
1046
1047 IF (l_found = FALSE AND g_fw1_payg_db_items.COUNT > 0) /*bug9147430*/
1048 THEN
1049 FOR i IN g_fw1_payg_db_items.FIRST..g_fw1_payg_db_items.LAST
1050 LOOP
1051 IF (g_fw1_payg_db_items(i) = p_user_entity_name)
1052 THEN
1053 l_found := TRUE;
1054 l_return_value := 'FW1_PAYG';
1055 END IF;
1056 END LOOP;
1057 END IF;
1058
1059 IF (l_found = FALSE AND g_fw2_payg_db_items.COUNT > 0) /*bug9147430*/
1060 THEN
1061 FOR i IN g_fw2_payg_db_items.FIRST..g_fw2_payg_db_items.LAST
1062 LOOP
1063 IF (g_fw2_payg_db_items(i) = p_user_entity_name)
1064 THEN
1065 l_found := TRUE;
1066 l_return_value := 'FW2_PAYG';
1067 END IF;
1068 END LOOP;
1069 END IF;
1070
1071 l_return_value := NVL(l_return_value,'CMN');
1072
1073 END IF;
1074
1075 IF g_debug THEN
1076 hr_utility.set_location('Return Value '||l_return_value,2520);
1077 hr_utility.set_location('Leaving Procedure '||l_procedure,2530);
1078 END IF;
1079
1080 RETURN NVL(l_return_value,'CMN');
1081
1082 END check_user_entity_type;
1083
1084
1085 /*
1086 --------------------------------------------------------------------
1087 Name : compare_user_entity_value
1088 Type : Function
1089 Access: Private
1090 Description:This procedure takes a User entity name and two values
1091 and compares the same.
1092 The following values are returned,
1093 Y - Value Matches
1094 N - Values Don't Match
1095 --------------------------------------------------------------------
1096 */
1097
1098 FUNCTION compare_user_entity_value
1099 (p_user_entity_name IN ff_user_entities.user_entity_name%TYPE
1100 ,p_value1 IN ff_archive_items.value%TYPE
1101 ,p_value2 IN ff_archive_items.value%TYPE
1102 ,p_data_type IN ff_database_items.data_type%TYPE)
1103 RETURN VARCHAR2
1104 IS
1105
1106 l_procedure VARCHAR2(80);
1107 l_return_flag VARCHAR2(5);
1108
1109 BEGIN
1110
1111 IF g_debug
1112 THEN
1113 l_procedure := g_package||'.compare_user_entity_value';
1114 hr_utility.set_location('Entering Function '||l_procedure,2600);
1115 hr_utility.set_location('p_user_entity_name '||p_user_entity_name,2610);
1116 hr_utility.set_location('p_value1 '||p_value1,2620);
1117 hr_utility.set_location('p_value2 '||p_value2,2620);
1118 hr_utility.set_location('p_data_type '||p_data_type,2620);
1119 END IF;
1120
1121 l_return_flag := 'Y'; /* Default - Values Match */
1122
1123 IF p_data_type = 'N'
1124 THEN
1125 IF trunc(to_number(p_value1)) <> trunc(to_number(p_value2))
1126 THEN
1127 l_return_flag := 'N';
1128 END IF;
1129 ELSIF p_data_type = 'D'
1130 THEN
1131 IF fnd_date.canonical_to_date(p_value1) <> fnd_date.canonical_to_date(p_value2)
1132 THEN
1133 l_return_flag := 'N';
1134 END IF;
1135 ELSE
1136 IF trim(p_value1) <> trim(p_value2)
1137 THEN
1138 l_return_flag := 'N';
1139 END IF;
1140 END IF;
1141
1142 IF g_debug
1143 THEN
1144 hr_utility.set_location('l_return_flag '||l_return_flag,2640);
1145 hr_utility.set_location('Leaving Function '||l_procedure,2650);
1146 END IF;
1147
1148 RETURN l_return_flag;
1149
1150 END compare_user_entity_value;
1151
1152
1153 /*
1154 --------------------------------------------------------------------
1155 Name : find_new_missing_items
1156 Type : Procedure
1157 Access: Private
1158 Description:This procedure is called when the count of items
1159 for old and new archive do not match.
1160 This Procedure identifies the missing item from New
1161 Archive and sets the appropriate Amend PS Flag
1162 --------------------------------------------------------------------
1163 */
1164
1165 PROCEDURE find_new_missing_items
1166 (p_archive_action_id IN pay_assignment_actions.assignment_action_id%TYPE
1167 ,p_old_count IN NUMBER
1168 ,p_all_tab_new IN archive_db_tab
1169 ,p_new_count IN NUMBER)
1170 IS
1171
1172 /* Bug 8315198 - Modified cursor to include X_ETP_DEATH_BENEFIT_TFN and X_LUMP_SUM_A_PAYMENT_TYPE DB items */
1173 CURSOR get_archived_user_entities
1174 (c_archive_action_id pay_assignment_actions.assignment_action_id%TYPE)
1175 IS
1176 SELECT fue.user_entity_name
1177 FROM ff_archive_items fae,
1178 ff_user_entities fue
1179 WHERE fae.context1 = c_archive_action_id
1180 AND fue.user_entity_id = fae.user_entity_id
1181 AND (
1182 fue.user_entity_name LIKE 'X_ALLOWANCE%'
1183 OR fue.user_entity_name LIKE 'X_EMPLOYEE%DATE%'
1184 OR fue.user_entity_name LIKE 'X_UNION%'
1185 OR fue.user_entity_name LIKE 'X_%ASG_YTD'
1186 OR fue.user_entity_name IN( 'X_SORT_EMPLOYEE_TYPE','X_EMPLOYEE_TAX_FILE_NUMBER','X_ETP_TAX_FILE_NUMBER'
1187 ,'X_ETP_DEATH_BENEFIT_TFN','X_LUMP_SUM_A_PAYMENT_TYPE')
1188 OR fue.user_entity_name LIKE 'X_%EXCL%' /*bug 14703826*/
1189 OR fue.user_entity_name LIKE 'X_%NE%'
1190 OR fue.user_entity_name LIKE 'X_ETP%DATE%'
1191 OR fue.user_entity_name LIKE 'X_DAYS%'
1192 OR fue.user_entity_name LIKE 'X_FW%' /*bug9147430*/
1193 )
1194 AND fue.user_entity_name NOT IN ('X_PAYMENT_SUMMARY_TYPE','X_PAYG_PAYMENT_SUMMARY_TYPE','X_ETP1_PAYMENT_SUMMARY_TYPE'
1195 ,'X_ETP2_PAYMENT_SUMMARY_TYPE','X_ETP3_PAYMENT_SUMMARY_TYPE','X_ETP4_PAYMENT_SUMMARY_TYPE'
1196 ,'X_LUMP_SUM_C_PAYMENTS_ASG_YTD','X_LUMP_SUM_C_DEDUCTIONS_ASG_YTD','X_INVALIDITY_PAYMENTS_ASG_YTD'
1197 ,'X_PRE_JUL_83_COMPONENT_ASG_YTD','X_POST_JUN_83_UNTAXED_ASG_YTD','X_POST_JUN_83_TAXED_ASG_YTD'
1198 ,'X_FW_PAYG_TYPE','X_FW1_PAYMENT_SUMMARY_TYPE','X_FW2_PAYMENT_SUMMARY_TYPE') /*bug9147430*/
1199 AND fue.legislation_code = 'AU';
1200
1201 l_procedure VARCHAR2(100);
1202 l_diff_count NUMBER;
1203 l_found BOOLEAN;
1204 l_item_type VARCHAR2(20);
1205
1206
1207 BEGIN
1208 g_debug := hr_utility.debug_enabled;
1209
1210 IF g_debug
1211 THEN
1212 l_procedure := g_package||'.find_new_missing_items';
1213 hr_utility.set_location('Entering Procedure '||l_procedure,3500);
1214 END IF;
1215
1216 l_diff_count := p_old_count - p_new_count;
1217
1218 /* Logic Used
1219 (A) Fetch all items from Original Archive for Standard and ETP pages
1220 (B) If this item is missing in New Archive PL/SQL table, set the Amended PS Flag accordingly
1221 (C) We will look only for items of type PAYG,ETP_CMN,ETP1, ETP2,ETP3,ETP4 - Relevant Numeric and Date Types
1222 (D) CMN - we are not interested if these items are missing
1223 (E) ETP_CMN_BAL and AMEND items are archived for all Employees - so will be ignored
1224 */
1225
1226 FOR csr_rec IN get_archived_user_entities(p_archive_action_id)
1227 LOOP
1228 IF l_diff_count = 0
1229 THEN
1230 exit;
1231 END IF;
1232
1233 l_found := FALSE;
1234 IF (p_all_tab_new.COUNT > 0)
1235 THEN
1236 FOR i IN p_all_tab_new.FIRST..p_all_tab_new.LAST
1237 LOOP
1238 IF p_all_tab_new(i).db_item_name = csr_rec.user_entity_name
1239 THEN
1240 l_found := TRUE;
1241 exit;
1242 END IF;
1243 END LOOP;
1244 END IF;
1245
1246 IF (l_found = FALSE)
1247 THEN
1248 /* DB Item missing in New Archive.
1249 Set the Amend Flags */
1250 IF g_debug
1251 THEN
1252 hr_utility.set_location('Missing Item Found '||csr_rec.user_entity_name,3510);
1253 END IF;
1254 l_item_type := check_user_entity_type(csr_rec.user_entity_name);
1255 IF l_item_type = 'PAYG'
1256 THEN
1257 l_amend_types_new(1).db_item_value := 'A';
1258 ELSIF l_item_type = 'ETP_CMN'
1259 THEN
1260 l_amend_types_new(2).db_item_value := 'A';
1261 l_amend_types_new(3).db_item_value := 'A';
1262 l_amend_types_new(4).db_item_value := 'A';
1263 l_amend_types_new(5).db_item_value := 'A';
1264 ELSIF l_item_type = 'ETP1'
1265 THEN
1266 l_amend_types_new(2).db_item_value := 'A';
1267 ELSIF l_item_type = 'ETP2'
1268 THEN
1269 l_amend_types_new(3).db_item_value := 'A';
1270 ELSIF l_item_type = 'ETP3'
1271 THEN
1272 l_amend_types_new(4).db_item_value := 'A';
1273 ELSIF l_item_type = 'ETP4'
1274 THEN
1275 l_amend_types_new(5).db_item_value := 'A';
1276 ELSIF l_item_type = 'FW1_PAYG' /*bug9147430*/
1277 THEN
1278 l_amend_types_new(6).db_item_value := 'A';
1279 ELSIF l_item_type = 'FW2_PAYG' /*bug9147430*/
1280 THEN
1281 l_amend_types_new(7).db_item_value := 'A';
1282 END IF;
1283 l_diff_count := l_diff_count - 1;
1284 END IF;
1285 END LOOP;
1286
1287 IF g_debug
1288 THEN
1289 hr_utility.set_location('Payment Summary Flags ',3520);
1290 IF (l_amend_types_new.COUNT > 0 )
1291 THEN
1292 FOR i IN l_amend_types_new.FIRST..l_amend_types_new.LAST
1293 LOOP
1294 hr_utility.set_location(rpad(i,5,' ')||rpad(substr(l_amend_types_new(i).db_item_name,1,50),50,' ')||rpad(l_amend_types_new(i).db_item_value,30,' '),3530);
1295 END LOOP;
1296 hr_utility.set_location('Leaving Procedure '||l_procedure,3540);
1297 END IF;
1298 END IF;
1299
1300 END find_new_missing_items;
1301
1302
1303
1304 /*
1305 --------------------------------------------------------------------
1306 Name : slot_items_build_archive_list
1307 Type : Procedure
1308 Access: Private
1309 Description:This private procedure does the actual comparison and
1310 slotting in multiple PL/SQL tables - one for each datafile type.
1311 It takes each item in Archive Pl/SQL table - finds the
1312 data file record, compares with the Original Archive value
1313 and sets the Amended PS Flag PL/sql table accordingly.
1314 --------------------------------------------------------------------
1315 */
1316
1317 PROCEDURE slot_items_build_archive_list
1318 (p_archive_action_id IN pay_assignment_actions.assignment_action_id%TYPE
1319 ,p_all_tab_new IN archive_db_tab)
1320 IS
1321
1322 CURSOR csr_get_value(c_user_entity_name VARCHAR2,
1323 c_assignment_action_id pay_assignment_actions.assignment_action_id%TYPE)
1324 IS
1325 SELECT fai.value
1326 ,fdi.data_type
1327 FROM ff_archive_items fai,
1328 ff_user_entities fue,
1329 ff_database_items fdi
1330 WHERE fai.context1 = c_assignment_action_id
1331 AND fai.user_entity_id = fue.user_entity_id
1332 AND fdi.user_entity_id = fue.user_entity_id
1333 AND fue.user_entity_name = c_user_entity_name;
1334
1335 i_index NUMBER;
1336 l_procedure VARCHAR2(80);
1337
1338 l_item_type VARCHAR2(20);
1339 l_old_value ff_archive_items.value%TYPE;
1340 l_data_type ff_database_items.data_type%TYPE;
1341
1342 l_compare_flag VARCHAR2(2);
1343 l_etp_cmn_flag VARCHAR2(2);
1344
1345 /*bug9147430*/
1346 CURSOR csr_data_type(c_user_entity_name VARCHAR2)
1347 IS
1348 SELECT fdi.data_type
1349 FROM ff_user_entities fue,
1350 ff_database_items fdi
1351 WHERE fdi.user_entity_id = fue.user_entity_id
1352 AND fue.user_entity_name = c_user_entity_name;
1353
1354 l_payg_new_sum ff_archive_items.value%TYPE := 0;
1355 l_payg_old_sum ff_archive_items.value%TYPE := 0;
1356 l_fw1_new_sum ff_archive_items.value%TYPE := 0;
1357 l_fw1_old_sum ff_archive_items.value%TYPE :=0;
1358 l_fw1_new_sub_sum ff_archive_items.value%TYPE :=0;
1359 l_fw1_old_sub_sum ff_archive_items.value%TYPE :=0;
1360 l_fw2_new_sum ff_archive_items.value%TYPE := 0;
1361 l_fw2_old_sum ff_archive_items.value%TYPE :=0;
1362 l_fw2_new_sub_sum ff_archive_items.value%TYPE :=0;
1363 l_fw2_old_sub_sum ff_archive_items.value%TYPE :=0;
1364 l_fw_payg_exists varchar2(1);
1365 l_counter number;
1366
1367 BEGIN
1368
1369 g_debug := hr_utility.debug_enabled;
1370
1371 IF g_debug
1372 THEN
1373 l_procedure := g_package||'.slot_items_build_archive_list';
1374 hr_utility.set_location('Entering Procedure '||l_procedure,3700);
1375 END IF;
1376
1377
1378 l_etp_cmn_flag := 'O'; /* Initialize ETP Common Change Flag to O */
1379
1380 IF ( p_all_tab_new.COUNT > 0 )
1381 THEN
1382 FOR i IN p_all_tab_new.FIRST..p_all_tab_new.LAST
1383 LOOP
1384
1385 l_compare_flag := 'Y';
1386
1387 l_item_type := check_user_entity_type(p_all_tab_new(i).db_item_name);
1388
1389 IF l_item_type ='CMN'
1390 THEN
1391 /* Only Old Values */
1392 OPEN csr_get_value(p_all_tab_new(i).db_item_name
1393 ,p_archive_action_id);
1394 FETCH csr_get_value INTO l_old_value,l_data_type;
1395 IF csr_get_value%NOTFOUND
1396 THEN
1397 l_old_value := NULL;
1398 l_data_type := NULL;
1399 END IF;
1400 CLOSE csr_get_value;
1401
1402 i_index := NVL(l_cmn_tab_new.LAST,-1) + 1;
1403
1404 l_cmn_tab_new(i_index).db_item_name := p_all_tab_new(i).db_item_name;
1405 l_cmn_tab_new(i_index).db_item_value := l_old_value;
1406
1407 ELSIF l_item_type ='PAYG'
1408 THEN
1409 IF l_amend_types_new(1).db_item_value <> 'A'
1410 THEN
1411
1412 OPEN csr_get_value(p_all_tab_new(i).db_item_name
1413 ,p_archive_action_id);
1414 FETCH csr_get_value INTO l_old_value,l_data_type;
1415 IF csr_get_value%NOTFOUND
1416 THEN
1417 l_amend_types_new(1).db_item_value := 'A';
1418 l_old_value := NULL;
1419 l_data_type := NULL;
1420 ELSE
1421 /* Compare Old and New Values
1422 Set the Amended Payment Summary Flag accordingly
1423 */
1424
1425 l_compare_flag := compare_user_entity_value
1426 (p_all_tab_new(i).db_item_name
1427 ,p_all_tab_new(i).db_item_value
1428 ,l_old_value
1429 ,l_data_type);
1430
1431 IF l_compare_flag = 'N'
1432 THEN
1433 l_amend_types_new(1).db_item_value := 'A';
1434 END IF;
1435 END IF;
1436 CLOSE csr_get_value ;
1437
1438 i_index := NVL(l_payg_tab_new.LAST,-1) + 1;
1439
1440 l_payg_tab_new(i_index).db_item_name := p_all_tab_new(i).db_item_name;
1441 l_payg_tab_new(i_index).db_item_value := p_all_tab_new(i).db_item_value;
1442
1443 ELSE
1444 /* Amended Payment Summary - No need to compare
1445 */
1446
1447 i_index := NVL(l_payg_tab_new.LAST,-1) + 1;
1448
1449 l_payg_tab_new(i_index).db_item_name := p_all_tab_new(i).db_item_name;
1450 l_payg_tab_new(i_index).db_item_value := p_all_tab_new(i).db_item_value;
1451
1452 END IF;
1453 ELSIF l_item_type IN ('ETP_CMN','ETP_CMN_BAL')
1454 THEN
1455 IF ( l_item_type = 'ETP_CMN' AND l_etp_cmn_flag <> 'A')
1456 THEN
1457
1458 OPEN csr_get_value(p_all_tab_new(i).db_item_name
1459 ,p_archive_action_id);
1460 FETCH csr_get_value INTO l_old_value,l_data_type;
1461 IF csr_get_value%NOTFOUND
1462 THEN
1463 l_etp_cmn_flag := 'A';
1464 l_amend_types_new(2).db_item_value := 'A';
1465 l_amend_types_new(3).db_item_value := 'A';
1466 l_amend_types_new(4).db_item_value := 'A';
1467 l_amend_types_new(5).db_item_value := 'A';
1468 l_old_value := NULL;
1469 l_data_type := NULL;
1470 ELSE
1471 /* Compare Old and New Values
1472 Set the Amended Payment Summary Flag accordingly
1473 */
1474
1475 l_compare_flag := compare_user_entity_value
1476 (p_all_tab_new(i).db_item_name
1477 ,p_all_tab_new(i).db_item_value
1478 ,l_old_value
1479 ,l_data_type);
1480
1481 IF l_compare_flag = 'N'
1482 THEN
1483 l_etp_cmn_flag := 'A';
1484 l_amend_types_new(2).db_item_value := 'A';
1485 l_amend_types_new(3).db_item_value := 'A';
1486 l_amend_types_new(4).db_item_value := 'A';
1487 l_amend_types_new(5).db_item_value := 'A';
1488 END IF;
1489 END IF;
1490 CLOSE csr_get_value ;
1491
1492 i_index := NVL(l_etp_cmn_tab_new.LAST,-1) + 1;
1493
1494 l_etp_cmn_tab_new(i_index).db_item_name := p_all_tab_new(i).db_item_name;
1495 l_etp_cmn_tab_new(i_index).db_item_value := p_all_tab_new(i).db_item_value;
1496
1497 ELSE
1498 /* Amended Payment Summary - No need to compare
1499 ETP Balances - will be adjusted in ETP1-4 Sections. Always copy the new Value
1500 */
1501
1502 i_index := NVL(l_etp_cmn_tab_new.LAST,-1) + 1;
1503
1504 l_etp_cmn_tab_new(i_index).db_item_name := p_all_tab_new(i).db_item_name;
1505 l_etp_cmn_tab_new(i_index).db_item_value := p_all_tab_new(i).db_item_value;
1506
1507 END IF;
1508 ELSIF l_item_type ='ETP1'
1509 THEN
1510 IF l_amend_types_new(2).db_item_value <> 'A'
1511 THEN
1512
1513 OPEN csr_get_value(p_all_tab_new(i).db_item_name
1514 ,p_archive_action_id);
1515 FETCH csr_get_value INTO l_old_value,l_data_type;
1516 IF csr_get_value%NOTFOUND
1517 THEN
1518 l_amend_types_new(2).db_item_value := 'A';
1519 l_old_value := NULL;
1520 l_data_type := NULL;
1521 ELSE
1522 /* Compare Old and New Values
1523 Set the Amended Payment Summary Flag accordingly
1524 */
1525
1526 l_compare_flag := compare_user_entity_value
1527 (p_all_tab_new(i).db_item_name
1528 ,p_all_tab_new(i).db_item_value
1529 ,l_old_value
1530 ,l_data_type);
1531
1532 IF l_compare_flag = 'N'
1533 THEN
1534 l_amend_types_new(2).db_item_value := 'A';
1535 END IF;
1536 END IF;
1537 CLOSE csr_get_value ;
1538
1539 i_index := NVL(l_etp_1_tab_new.LAST,-1) + 1;
1540
1541 l_etp_1_tab_new(i_index).db_item_name := p_all_tab_new(i).db_item_name;
1542 l_etp_1_tab_new(i_index).db_item_value := p_all_tab_new(i).db_item_value;
1543
1544 ELSE
1545 /* Amended Payment Summary - No need to compare
1546 */
1547
1548 i_index := NVL(l_etp_1_tab_new.LAST,-1) + 1;
1549
1550 l_etp_1_tab_new(i_index).db_item_name := p_all_tab_new(i).db_item_name;
1551 l_etp_1_tab_new(i_index).db_item_value := p_all_tab_new(i).db_item_value;
1552 END IF;
1553
1554 ELSIF l_item_type ='ETP2'
1555 THEN
1556 IF l_amend_types_new(3).db_item_value <> 'A'
1557 THEN
1558
1559 OPEN csr_get_value(p_all_tab_new(i).db_item_name
1560 ,p_archive_action_id);
1561 FETCH csr_get_value INTO l_old_value,l_data_type;
1562 IF csr_get_value%NOTFOUND
1563 THEN
1564 l_amend_types_new(3).db_item_value := 'A';
1565 l_old_value := NULL;
1566 l_data_type := NULL;
1567 ELSE
1568 /* Compare Old and New Values
1569 Set the Amended Payment Summary Flag accordingly
1570 */
1571
1572 l_compare_flag := compare_user_entity_value
1573 (p_all_tab_new(i).db_item_name
1574 ,p_all_tab_new(i).db_item_value
1575 ,l_old_value
1576 ,l_data_type);
1577
1578 IF l_compare_flag = 'N'
1579 THEN
1580 l_amend_types_new(3).db_item_value := 'A';
1581 END IF;
1582 END IF;
1583 CLOSE csr_get_value ;
1584
1585 i_index := NVL(l_etp_2_tab_new.LAST,-1) + 1;
1586
1587 l_etp_2_tab_new(i_index).db_item_name := p_all_tab_new(i).db_item_name;
1588 l_etp_2_tab_new(i_index).db_item_value := p_all_tab_new(i).db_item_value;
1589
1590 ELSE
1591 /* Amended Payment Summary - No need to compare
1592 */
1593
1594 i_index := NVL(l_etp_2_tab_new.LAST,-1) + 1;
1595
1596 l_etp_2_tab_new(i_index).db_item_name := p_all_tab_new(i).db_item_name;
1597 l_etp_2_tab_new(i_index).db_item_value := p_all_tab_new(i).db_item_value;
1598 END IF;
1599
1600 ELSIF l_item_type ='ETP3'
1601 THEN
1602 IF l_amend_types_new(4).db_item_value <> 'A'
1603 THEN
1604
1605 OPEN csr_get_value(p_all_tab_new(i).db_item_name
1606 ,p_archive_action_id);
1607 FETCH csr_get_value INTO l_old_value,l_data_type;
1608 IF csr_get_value%NOTFOUND
1609 THEN
1610 l_amend_types_new(4).db_item_value := 'A';
1611 l_old_value := NULL;
1612 l_data_type := NULL;
1613 ELSE
1614 /* Compare Old and New Values
1615 Set the Amended Payment Summary Flag accordingly
1616 */
1617
1618 l_compare_flag := compare_user_entity_value
1619 (p_all_tab_new(i).db_item_name
1620 ,p_all_tab_new(i).db_item_value
1621 ,l_old_value
1622 ,l_data_type);
1623
1624 IF l_compare_flag = 'N'
1625 THEN
1626 l_amend_types_new(4).db_item_value := 'A';
1627 END IF;
1628 END IF;
1629 CLOSE csr_get_value ;
1630
1631 i_index := NVL(l_etp_3_tab_new.LAST,-1) + 1;
1632
1633 l_etp_3_tab_new(i_index).db_item_name := p_all_tab_new(i).db_item_name;
1634 l_etp_3_tab_new(i_index).db_item_value := p_all_tab_new(i).db_item_value;
1635
1636 ELSE
1637 /* Amended Payment Summary - No need to compare
1638 */
1639
1640 i_index := NVL(l_etp_3_tab_new.LAST,-1) + 1;
1641
1642 l_etp_3_tab_new(i_index).db_item_name := p_all_tab_new(i).db_item_name;
1643 l_etp_3_tab_new(i_index).db_item_value := p_all_tab_new(i).db_item_value;
1644 END IF;
1645
1646 ELSIF l_item_type ='ETP4'
1647 THEN
1648 IF l_amend_types_new(5).db_item_value <> 'A'
1649 THEN
1650
1651 OPEN csr_get_value(p_all_tab_new(i).db_item_name
1652 ,p_archive_action_id);
1653 FETCH csr_get_value INTO l_old_value,l_data_type;
1654 IF csr_get_value%NOTFOUND
1655 THEN
1656 l_amend_types_new(5).db_item_value := 'A';
1657 l_old_value := NULL;
1658 l_data_type := NULL;
1659 ELSE
1660 /* Compare Old and New Values
1661 Set the Amended Payment Summary Flag accordingly
1662 */
1663
1664 l_compare_flag := compare_user_entity_value
1665 (p_all_tab_new(i).db_item_name
1666 ,p_all_tab_new(i).db_item_value
1667 ,l_old_value
1668 ,l_data_type);
1669
1670 IF l_compare_flag = 'N'
1671 THEN
1672 l_amend_types_new(5).db_item_value := 'A';
1673 END IF;
1674 END IF;
1675 CLOSE csr_get_value ;
1676
1677 i_index := NVL(l_etp_4_tab_new.LAST,-1) + 1;
1678
1679 l_etp_4_tab_new(i_index).db_item_name := p_all_tab_new(i).db_item_name;
1680 l_etp_4_tab_new(i_index).db_item_value := p_all_tab_new(i).db_item_value;
1681
1682 ELSE
1683 /* Amended Payment Summary - No need to compare
1684 */
1685 i_index := NVL(l_etp_4_tab_new.LAST,-1) + 1;
1686
1687 l_etp_4_tab_new(i_index).db_item_name := p_all_tab_new(i).db_item_name;
1688 l_etp_4_tab_new(i_index).db_item_value := p_all_tab_new(i).db_item_value;
1689 END IF;
1690
1691 ELSIF l_item_type ='FW1_PAYG' /*bug9147430*/
1692 THEN
1693 IF l_amend_types_new(6).db_item_value <> 'A'
1694 THEN
1695
1696 OPEN csr_get_value(p_all_tab_new(i).db_item_name
1697 ,p_archive_action_id);
1698 FETCH csr_get_value INTO l_old_value,l_data_type;
1699 IF csr_get_value%NOTFOUND
1700 THEN
1701 l_amend_types_new(6).db_item_value := 'A';
1702 l_old_value := NULL;
1703 l_data_type := NULL;
1704 ELSE
1705 /* Compare Old and New Values
1706 Set the Amended Payment Summary Flag accordingly
1707 */
1708
1709 l_compare_flag := compare_user_entity_value
1710 (p_all_tab_new(i).db_item_name
1711 ,p_all_tab_new(i).db_item_value
1712 ,l_old_value
1713 ,l_data_type);
1714
1715 IF l_compare_flag = 'N'
1716 THEN
1717 l_amend_types_new(6).db_item_value := 'A';
1718 END IF;
1719 END IF;
1720 CLOSE csr_get_value ;
1721
1722 i_index := NVL(l_fw1_payg_tab_new.LAST,-1) + 1;
1723
1724 l_fw1_payg_tab_new(i_index).db_item_name := p_all_tab_new(i).db_item_name;
1725 l_fw1_payg_tab_new(i_index).db_item_value := p_all_tab_new(i).db_item_value;
1726
1727 ELSE
1728 /* Amended Payment Summary - No need to compare
1729 */
1730
1731 i_index := NVL(l_fw1_payg_tab_new.LAST,-1) + 1;
1732
1733 l_fw1_payg_tab_new(i_index).db_item_name := p_all_tab_new(i).db_item_name;
1734 l_fw1_payg_tab_new(i_index).db_item_value := p_all_tab_new(i).db_item_value;
1735
1736 END IF;
1737
1738 ELSIF l_item_type ='FW2_PAYG' /*bug9147430*/
1739 THEN
1740
1741 IF l_amend_types_new(7).db_item_value <> 'A'
1742 THEN
1743
1744 OPEN csr_get_value(p_all_tab_new(i).db_item_name
1745 ,p_archive_action_id);
1746 FETCH csr_get_value INTO l_old_value,l_data_type;
1747 IF csr_get_value%NOTFOUND
1748 THEN
1749 l_amend_types_new(7).db_item_value := 'A';
1750 l_old_value := NULL;
1751 l_data_type := NULL;
1752 ELSE
1753 /* Compare Old and New Values
1754 Set the Amended Payment Summary Flag accordingly
1755 */
1756
1757 l_compare_flag := compare_user_entity_value
1758 (p_all_tab_new(i).db_item_name
1759 ,p_all_tab_new(i).db_item_value
1760 ,l_old_value
1761 ,l_data_type);
1762
1763 IF l_compare_flag = 'N'
1764 THEN
1765 l_amend_types_new(7).db_item_value := 'A';
1766 END IF;
1767 END IF;
1768 CLOSE csr_get_value ;
1769
1770 i_index := NVL(l_fw2_payg_tab_new.LAST,-1) + 1;
1771
1772 l_fw2_payg_tab_new(i_index).db_item_name := p_all_tab_new(i).db_item_name;
1773 l_fw2_payg_tab_new(i_index).db_item_value := p_all_tab_new(i).db_item_value;
1774
1775 ELSE
1776 /* Amended Payment Summary - No need to compare
1777 */
1778
1779 i_index := NVL(l_fw2_payg_tab_new.LAST,-1) + 1;
1780
1781 l_fw2_payg_tab_new(i_index).db_item_name := p_all_tab_new(i).db_item_name;
1782 l_fw2_payg_tab_new(i_index).db_item_value := p_all_tab_new(i).db_item_value;
1783
1784 END IF;
1785
1786 END IF;
1787
1788 IF p_all_tab_new(i).db_item_name = 'X_FW_PAYG_TYPE' and (p_all_tab_new(i).db_item_value = 'PF' )THEN
1789 l_fw_payg_exists := 'Y';
1790 END IF;
1791
1792 END LOOP;
1793 END IF;
1794
1795 /*bug9147430 - Checking sum of gross and fw balances to determine which payment summary type is amended */
1796 IF l_fw_payg_exists = 'Y' THEN
1797
1798 l_counter := l_payg_tab_new.FIRST;
1799 IF ( l_payg_tab_new.COUNT > 0 ) THEN
1800 FOR l_counter IN l_payg_tab_new.FIRST..l_payg_tab_new.LAST LOOP
1801 IF l_payg_tab_new.exists(l_counter) THEN
1802 OPEN csr_get_value(l_payg_tab_new(l_counter).db_item_name
1803 ,p_archive_action_id);
1804 FETCH csr_get_value INTO l_old_value,l_data_type;
1805 IF csr_get_value%NOTFOUND THEN
1806 l_old_value := 0;
1807 open csr_data_type(l_payg_tab_new(l_counter).db_item_name);
1808 fetch csr_data_type into l_data_type;
1809 close csr_data_type;
1810 END IF;
1811 CLOSE csr_get_value ;
1812
1813 if l_data_type = 'N' then
1814 l_payg_old_sum := l_payg_old_sum + nvl(l_old_value,0);
1815 l_payg_new_sum := l_payg_new_sum + nvl(l_payg_tab_new(l_counter).db_item_value,0);
1816 end if;
1817 END IF;
1818 END LOOP;
1819 END IF;
1820
1821 l_counter := l_fw1_payg_tab_new.FIRST;
1822 IF ( l_fw1_payg_tab_new.COUNT > 0 ) THEN
1823 FOR l_counter IN l_fw1_payg_tab_new.FIRST..l_fw1_payg_tab_new.LAST LOOP
1824 IF l_fw1_payg_tab_new.exists(l_counter) THEN
1825 IF (l_fw1_payg_tab_new(l_counter).db_item_name = 'X_FW1_FOREIGN_TAX_PAID' or
1826 l_fw1_payg_tab_new(l_counter).db_item_name = 'X_FW1_ALLOWANCE_1_ASG_YTD' or
1827 l_fw1_payg_tab_new(l_counter).db_item_name = 'X_FW1_ALLOWANCE_2_ASG_YTD' or
1828 l_fw1_payg_tab_new(l_counter).db_item_name = 'X_FW1_ALLOWANCE_3_ASG_YTD' or
1829 l_fw1_payg_tab_new(l_counter).db_item_name = 'X_FW1_ALLOWANCE_4_ASG_YTD' or
1830 l_fw1_payg_tab_new(l_counter).db_item_name = 'X_FW1_UNION_FEES') THEN
1831 null;
1832 ELSE
1833 OPEN csr_get_value(l_fw1_payg_tab_new(l_counter).db_item_name
1834 ,p_archive_action_id);
1835 FETCH csr_get_value INTO l_old_value,l_data_type;
1836 IF csr_get_value%NOTFOUND THEN
1837 l_old_value := 0;
1838 open csr_data_type(l_fw1_payg_tab_new(l_counter).db_item_name);
1839 fetch csr_data_type into l_data_type;
1840 close csr_data_type;
1841 END IF;
1842 CLOSE csr_get_value ;
1843
1844 if l_data_type = 'N' then
1845 l_fw1_old_sum := l_fw1_old_sum + nvl(l_old_value,0);
1846 l_fw1_new_sum := l_fw1_new_sum + nvl(l_fw1_payg_tab_new(l_counter).db_item_value,0);
1847 IF l_fw1_payg_tab_new(l_counter).db_item_name = 'X_FW1_FRINGE_BENEFITS' or
1848 l_fw1_payg_tab_new(l_counter).db_item_name = 'X_FW1_RPT_EMPLOYER_SUPERANN_CONTR' or
1849 l_fw1_payg_tab_new(l_counter).db_item_name = 'X_FW1_LUMP_SUM_D_PAYMENTS' THEN
1850 l_fw1_old_sub_sum := l_fw1_old_sub_sum + nvl(l_old_value,0);
1851 l_fw1_new_sub_sum := l_fw1_new_sub_sum + nvl(l_fw1_payg_tab_new(l_counter).db_item_value,0);
1852 END IF;
1853 end if;
1854 END IF;
1855 END IF;
1856 END LOOP;
1857 END IF;
1858
1859 l_counter := l_fw2_payg_tab_new.FIRST;
1860 IF ( l_fw2_payg_tab_new.COUNT > 0 ) THEN
1861 FOR l_counter IN l_fw2_payg_tab_new.FIRST..l_fw2_payg_tab_new.LAST LOOP
1862 IF l_fw2_payg_tab_new.exists(l_counter) THEN
1863 IF (l_fw1_payg_tab_new(l_counter).db_item_name = 'X_FW2_FOREIGN_TAX_PAID' or
1864 l_fw1_payg_tab_new(l_counter).db_item_name = 'X_FW2_ALLOWANCE_1_ASG_YTD' or
1865 l_fw1_payg_tab_new(l_counter).db_item_name = 'X_FW2_ALLOWANCE_2_ASG_YTD' or
1866 l_fw1_payg_tab_new(l_counter).db_item_name = 'X_FW2_ALLOWANCE_3_ASG_YTD' or
1867 l_fw1_payg_tab_new(l_counter).db_item_name = 'X_FW2_ALLOWANCE_4_ASG_YTD' or
1868 l_fw1_payg_tab_new(l_counter).db_item_name = 'X_FW2_UNION_FEES') THEN
1869 null;
1870 ELSE
1871 OPEN csr_get_value(l_fw2_payg_tab_new(l_counter).db_item_name
1872 ,p_archive_action_id);
1873 FETCH csr_get_value INTO l_old_value,l_data_type;
1874 IF csr_get_value%NOTFOUND THEN
1875 l_old_value := 0;
1876 open csr_data_type(l_fw2_payg_tab_new(l_counter).db_item_name);
1877 fetch csr_data_type into l_data_type;
1878 close csr_data_type;
1879 END IF;
1880 CLOSE csr_get_value ;
1881
1882 if l_data_type = 'N' then
1883 l_fw2_old_sum := l_fw2_old_sum + nvl(l_old_value,0);
1884 l_fw2_new_sum := l_fw2_new_sum + nvl(l_fw2_payg_tab_new(l_counter).db_item_value,0);
1885 IF l_fw2_payg_tab_new(l_counter).db_item_name = 'X_FW2_FRINGE_BENEFITS' or
1886 l_fw2_payg_tab_new(l_counter).db_item_name = 'X_FW2_RPT_EMPLOYER_SUPERANN_CONTR' or
1887 l_fw2_payg_tab_new(l_counter).db_item_name = 'X_FW2_LUMP_SUM_D_PAYMENTS' THEN
1888 l_fw2_old_sub_sum := l_fw2_old_sub_sum + nvl(l_old_value,0);
1889 l_fw2_new_sub_sum := l_fw2_new_sub_sum + nvl(l_fw2_payg_tab_new(l_counter).db_item_value,0);
1890 END IF;
1891 end if;
1892 END IF;
1893 END IF;
1894 END LOOP;
1895 END IF;
1896
1897 IF (l_payg_new_sum - l_payg_old_sum) - ((l_fw1_new_sum - l_fw1_old_sum) + (l_fw2_new_sum - l_fw2_old_sum)) = 0 THEN -- FW changes only
1898
1899 IF l_fw1_new_sub_sum - l_fw1_old_sub_sum <> 0 or
1900 l_fw2_new_sub_sum - l_fw2_old_sub_sum <> 0 THEN
1901 l_amend_types_new(1).db_item_value :='A';
1902
1903 IF (l_fw1_new_sum - l_fw1_old_sum) - (l_fw1_new_sub_sum - l_fw1_old_sub_sum) = 0 THEN
1904 l_amend_types_new(6).db_item_value :='O';
1905 END IF;
1906 IF (l_fw2_new_sum - l_fw2_old_sum) - (l_fw2_new_sub_sum - l_fw2_old_sub_sum) = 0 THEN
1907 l_amend_types_new(7).db_item_value :='O';
1908 END IF;
1909
1910 ELSE
1911 l_amend_types_new(1).db_item_value :='O';
1912 END IF;
1913
1914 END IF;
1915
1916 if g_debug then
1917 hr_utility.trace('PAYG/FW change comparision -');
1918 hr_utility.trace('l_payg_new_sum : '||l_payg_new_sum);
1919 hr_utility.trace('l_payg_old_sum : '||l_payg_old_sum);
1920 hr_utility.trace('l_fw1_new_sum : '||l_fw1_new_sum);
1921 hr_utility.trace('l_fw1_old_sum : '||l_fw1_old_sum);
1922 hr_utility.trace('l_fw1_new_sub_sum : '||l_fw1_new_sub_sum);
1923 hr_utility.trace('l_fw1_old_sub_sum : '||l_fw1_old_sub_sum);
1924 hr_utility.trace('l_fw2_new_sum : '||l_fw2_new_sum);
1925 hr_utility.trace('l_fw2_old_sum : '||l_fw2_old_sum);
1926 hr_utility.trace('l_fw2_new_sub_sum : '||l_fw2_new_sub_sum);
1927 hr_utility.trace('l_fw2_old_sub_sum : '||l_fw2_old_sub_sum);
1928 end if;
1929
1930 END IF;
1931
1932
1933 /* Reset the Value of DB Item X_PAYMENT_SUMMARY_TYPE if No individual record has changed
1934 */
1935
1936 IF ( l_amend_types_new(1).db_item_value ='O'
1937 AND l_amend_types_new(2).db_item_value ='O'
1938 AND l_amend_types_new(3).db_item_value ='O'
1939 AND l_amend_types_new(4).db_item_value ='O'
1940 AND l_amend_types_new(5).db_item_value ='O'
1941 AND l_amend_types_new(6).db_item_value ='O' /*bug9147430*/
1942 AND l_amend_types_new(7).db_item_value ='O') /*bug9147430*/
1943 THEN
1944 l_amend_types_new(0).db_item_value :='O';
1945 END IF;
1946
1947
1948 IF g_debug
1949 THEN
1950 IF ( l_cmn_tab_new.COUNT > 0)
1951 THEN
1952 hr_utility.set_location(' COMMON ITEMS ',3710);
1953
1954 FOR i IN l_cmn_tab_new.FIRST..l_cmn_tab_new.LAST
1955 LOOP
1956 hr_utility.set_location(rpad(i,5,' ')||rpad(substr(l_cmn_tab_new(i).db_item_name,1,50),50,' ')||rpad(l_cmn_tab_new(i).db_item_value,30,' '),3710);
1957 END LOOP;
1958 END IF;
1959
1960 IF ( l_payg_tab_new.COUNT > 0)
1961 THEN
1962 hr_utility.set_location(' STANDARD ITEMS ',3720);
1963
1964 FOR i IN l_payg_tab_new.FIRST..l_payg_tab_new.LAST
1965 LOOP
1966 hr_utility.set_location(rpad(i,5,' ')||rpad(substr(l_payg_tab_new(i).db_item_name,1,50),50,' ')||rpad(l_payg_tab_new(i).db_item_value,30,' '),3720);
1967 END LOOP;
1968 END IF;
1969
1970
1971 IF ( l_etp_cmn_tab_new.COUNT > 0)
1972 THEN
1973 hr_utility.set_location(' ETP COMMON ITEMS ',3730);
1974
1975 FOR i IN l_etp_cmn_tab_new.FIRST..l_etp_cmn_tab_new.LAST
1976 LOOP
1977 hr_utility.set_location(rpad(i,5,' ')||rpad(substr(l_etp_cmn_tab_new(i).db_item_name,1,50),50,' ')||rpad(l_etp_cmn_tab_new(i).db_item_value,30,' '),3730);
1978 END LOOP;
1979 END IF;
1980
1981 IF (l_etp_1_tab_new.COUNT > 0)
1982 THEN
1983 hr_utility.set_location(' ETP 1 ITEMS ',3740);
1984
1985 FOR i IN l_etp_1_tab_new.FIRST..l_etp_1_tab_new.LAST
1986 LOOP
1987 hr_utility.set_location(rpad(i,5,' ')||rpad(substr(l_etp_1_tab_new(i).db_item_name,1,50),50,' ')||rpad(l_etp_1_tab_new(i).db_item_value,30,' '),3740);
1988 END LOOP;
1989 END IF;
1990
1991 IF (l_etp_2_tab_new.COUNT > 0)
1992 THEN
1993 hr_utility.set_location(' ETP 2 ITEMS ',3750);
1994
1995 FOR i IN l_etp_2_tab_new.FIRST..l_etp_2_tab_new.LAST
1996 LOOP
1997 hr_utility.set_location(rpad(i,5,' ')||rpad(substr(l_etp_2_tab_new(i).db_item_name,1,50),50,' ')||rpad(l_etp_2_tab_new(i).db_item_value,30,' '),3750);
1998 END LOOP;
1999 END IF;
2000
2001 IF (l_etp_3_tab_new.COUNT > 0)
2002 THEN
2003 hr_utility.set_location(' ETP 3 ITEMS ',3760);
2004
2005 FOR i IN l_etp_3_tab_new.FIRST..l_etp_3_tab_new.LAST
2006 LOOP
2007 hr_utility.set_location(rpad(i,5,' ')||rpad(substr(l_etp_3_tab_new(i).db_item_name,1,50),50,' ')||rpad(l_etp_3_tab_new(i).db_item_value,30,' '),3760);
2008 END LOOP;
2009 END IF;
2010
2011 IF ( l_etp_4_tab_new.COUNT > 0)
2012 THEN
2013 hr_utility.set_location(' ETP 4 ITEMS ',3770);
2014
2015 FOR i IN l_etp_4_tab_new.FIRST..l_etp_4_tab_new.LAST
2016 LOOP
2017 hr_utility.set_location(rpad(i,5,' ')||rpad(substr(l_etp_4_tab_new(i).db_item_name,1,50),50,' ')||rpad(l_etp_4_tab_new(i).db_item_value,30,' '),3770);
2018 END LOOP;
2019 END IF;
2020
2021 IF ( l_fw1_payg_tab_new.COUNT > 0)
2022 THEN
2023 hr_utility.set_location(' FW1 PAYG ITEMS ',3770);
2024
2025 FOR i IN l_fw1_payg_tab_new.FIRST..l_fw1_payg_tab_new.LAST
2026 LOOP
2027 hr_utility.set_location(rpad(i,5,' ')||rpad(substr(l_fw1_payg_tab_new(i).db_item_name,1,50),50,' ')||rpad(l_fw1_payg_tab_new(i).db_item_value,30,' '),3780);
2028 END LOOP;
2029 END IF;
2030
2031 IF ( l_fw2_payg_tab_new.COUNT > 0)
2032 THEN
2033 hr_utility.set_location(' FW2 PAYG ITEMS ',3770);
2034
2035 FOR i IN l_fw2_payg_tab_new.FIRST..l_fw2_payg_tab_new.LAST
2036 LOOP
2037 hr_utility.set_location(rpad(i,5,' ')||rpad(substr(l_fw2_payg_tab_new(i).db_item_name,1,50),50,' ')||rpad(l_fw2_payg_tab_new(i).db_item_value,30,' '),3780);
2038 END LOOP;
2039 END IF;
2040
2041 IF (l_amend_types_new.COUNT > 0)
2042 THEN
2043 hr_utility.set_location(' AMEND TYPE ITEMS ',3780);
2044
2045 FOR i IN l_amend_types_new.FIRST..l_amend_types_new.LAST
2046 LOOP
2047 hr_utility.set_location(rpad(i,5,' ')||rpad(substr(l_amend_types_new(i).db_item_name,1,50),50,' ')||rpad(l_amend_types_new(i).db_item_value,30,' '),3790);
2048 END LOOP;
2049 END IF;
2050
2051 hr_utility.set_location('Leaving Procedure '||l_procedure,3800);
2052 END IF;
2053
2054
2055 END slot_items_build_archive_list;
2056
2057
2058 /*
2059 --------------------------------------------------------------------
2060 Name : archive_db_items_tab
2061 Type : Procedure
2062 Access: Private
2063 Description:This procedure archives the contents of the
2064 user entity value PL/SQL table
2065 --------------------------------------------------------------------
2066 */
2067
2068 PROCEDURE archive_db_items_tab(
2069 p_assignment_action_id IN pay_assignment_actions.assignment_action_id%TYPE
2070 ,p_db_item_tab IN archive_db_tab
2071 )
2072 IS
2073
2074 CURSOR get_user_entity_id(c_user_entity_name IN VARCHAR2)
2075 IS
2076 SELECT fue.user_entity_id
2077 ,dbi.data_type
2078 FROM ff_user_entities fue
2079 ,ff_database_items dbi
2080 WHERE user_entity_name = c_user_entity_name
2081 AND fue.user_entity_id = dbi.user_entity_id
2082 AND fue.legislation_code = 'AU';
2083
2084 l_procedure VARCHAR2(80);
2085 l_user_entity_id ff_user_entities.user_entity_id%TYPE;
2086 l_archive_item_id ff_archive_items.archive_item_id%TYPE;
2087 l_object_version_number ff_archive_items.object_version_number%type;
2088 l_some_warning boolean;
2089
2090 e_ue_missing EXCEPTION;
2091
2092 BEGIN
2093
2094 g_debug := hr_utility.debug_enabled;
2095 IF g_debug
2096 THEN
2097 l_procedure := g_package||'.archive_db_items_tab';
2098 hr_utility.set_location('Entering Procedure '||l_procedure,4200);
2099 END IF;
2100
2101 IF (p_db_item_tab.COUNT > 0)
2102 THEN
2103 FOR i IN p_db_item_tab.FIRST..p_db_item_tab.LAST
2104 LOOP
2105 IF g_debug
2106 THEN
2107 hr_utility.set_location('p_db_item_tab.name '||p_db_item_tab(i).db_item_name,4210);
2108 hr_utility.set_location('p_db_item_tab.value '||p_db_item_tab(i).db_item_value,4220);
2109 END IF;
2110
2111 FOR csr_ue_rec IN get_user_entity_id(p_db_item_tab(i).db_item_name)
2112 LOOP
2113 l_archive_item_id := NULL;
2114 l_object_version_number := NULL;
2115 l_some_warning := NULL;
2116
2117 ff_archive_api.create_archive_item
2118 (p_validate => false
2119 ,p_archive_item_id => l_archive_item_id
2120 ,p_user_entity_id => csr_ue_rec.user_entity_id
2121 ,p_archive_value => p_db_item_tab(i).db_item_value
2122 ,p_archive_type => 'AAP'
2123 ,p_action_id => p_assignment_action_id
2124 ,p_legislation_code => 'AU'
2125 ,p_object_version_number => l_object_version_number
2126 ,p_context_name1 => 'ASSIGNMENT_ACTION_ID'
2127 ,p_context1 => p_assignment_action_id
2128 ,p_some_warning => l_some_warning);
2129
2130 IF g_debug
2131 THEN
2132 hr_utility.set_location('l_archive_item_id '||l_archive_item_id,4230);
2133 END IF;
2134 END LOOP;
2135
2136 END LOOP;
2137 END IF;
2138 IF g_debug
2139 THEN
2140 hr_utility.set_location('Leaving Procedure '||l_procedure,4250);
2141 END IF;
2142
2143 END archive_db_items_tab;
2144
2145
2146 /*
2147 --------------------------------------------------------------------
2148 Name : modify_and_archive_code
2149 Type : Procedure
2150 Access: Public
2151 Description:This procedure is called from Archive code of Payment Summary
2152 with a PL/SQL table holding all DB items and values
2153 This procedure slots the DB items according to record
2154 in datafile and populates different PL/SQL tables.
2155 Data is archived in this procedure based on Amend PS
2156 flags.
2157 --------------------------------------------------------------------
2158 */
2159 PROCEDURE modify_and_archive_code
2160 (p_assignment_action_id IN pay_assignment_actions.assignment_action_id%TYPE
2161 ,p_effective_date IN DATE
2162 ,p_all_tab_new IN archive_db_tab)
2163 IS
2164
2165 l_procedure VARCHAR2(80);
2166
2167 CURSOR get_orig_archive_id
2168 (c_assignmenr_id pay_assignment_actions.assignment_id%TYPE
2169 ,c_fin_year VARCHAR2
2170 ,c_tax_unit_id pay_assignment_actions.tax_unit_id%TYPE
2171 )
2172 IS
2173 SELECT selfplock.locked_action_id
2174 FROM pay_assignment_actions mpaa
2175 ,pay_payroll_actions mppa
2176 ,pay_action_interlocks mplock
2177 ,pay_action_interlocks selfplock
2178 WHERE mpaa.assignment_id = c_assignmenr_id
2179 AND mpaa.payroll_action_id = mppa.payroll_action_id
2180 AND mppa.report_type = 'AU_PS_DATA_FILE'
2181 AND mppa.report_qualifier = 'AU'
2182 AND mppa.report_category = 'REPORT'
2183 AND pay_core_utils.get_parameter('FINANCIAL_YEAR',mppa.legislative_parameters) = c_fin_year
2184 AND pay_core_utils.get_parameter('REGISTERED_EMPLOYER',mppa.legislative_parameters) = c_tax_unit_id
2185 AND mplock.locking_action_id = mpaa.assignment_action_id
2186 AND mplock.locked_action_id = selfplock.locking_action_id
2187 ORDER BY selfplock.locked_action_id desc; /* bug 14621185 - picking up the latest archive */
2188
2189
2190 CURSOR c_action(c_assignment_action_id NUMBER) IS
2191 SELECT pay_core_utils.get_parameter('BUSINESS_GROUP_ID',ppa.legislative_parameters)
2192 , pay_core_utils.get_parameter('REGISTERED_EMPLOYER',ppa.legislative_parameters)
2193 , pay_core_utils.get_parameter('EMPLOYEE_TYPE',ppa.legislative_parameters)
2194 , ppa.payroll_action_id
2195 , paa.assignment_id
2196 , to_date('01-07-'|| substr(pay_core_utils.get_parameter('FINANCIAL_YEAR',ppa.legislative_parameters),1,4),'DD-MM-YYYY')
2197 , to_date('30-06-'|| substr(pay_core_utils.get_parameter('FINANCIAL_YEAR',ppa.legislative_parameters),6,4),'DD-MM-YYYY')
2198 , pay_core_utils.get_parameter('LST_YR_TERM',ppa.legislative_parameters)
2199 , pay_core_utils.get_parameter('FINANCIAL_YEAR',ppa.legislative_parameters)
2200 FROM pay_assignment_actions paa
2201 , pay_payroll_actions ppa
2202 WHERE paa.assignment_action_id = c_assignment_action_id
2203 AND ppa.payroll_action_id = paa.payroll_action_id ;
2204
2205 CURSOR get_context_id(c_context_name ff_contexts.context_name%TYPE)
2206 IS
2207 SELECT fc.context_id
2208 FROM ff_contexts fc
2209 WHERE fc.context_name = c_context_name;
2210
2211 CURSOR get_archive_item_count(c_archive_action_id pay_assignment_actions.assignment_action_id%TYPE
2212 ,c_context_id ff_contexts.context_id%TYPE)
2213 IS
2214 SELECT COUNT(*)
2215 FROM ff_archive_items fai,
2216 ff_user_entities fue,
2217 ff_archive_item_contexts faic
2218 WHERE fai.context1 = c_archive_action_id
2219 AND fue.user_entity_id = fai.user_entity_id
2220 AND fai.archive_item_id = faic.archive_item_id
2221 AND faic.context_id = c_context_id
2222 AND fue.user_entity_name NOT IN ('X_PAYMENT_SUMMARY_TYPE'
2223 ,'X_PAYG_PAYMENT_SUMMARY_TYPE'
2224 ,'X_ETP1_PAYMENT_SUMMARY_TYPE'
2225 ,'X_ETP2_PAYMENT_SUMMARY_TYPE'
2226 ,'X_ETP3_PAYMENT_SUMMARY_TYPE'
2227 ,'X_ETP4_PAYMENT_SUMMARY_TYPE'
2228 ,'X_FW1_PAYMENT_SUMMARY_TYPE' /*bug9147430*/
2229 ,'X_FW2_PAYMENT_SUMMARY_TYPE'); /*bug9147430*/
2230
2231 l_assignment_id pay_assignment_actions.assignment_id%TYPE;
2232 l_business_group_id pay_payroll_actions.business_group_id%TYPE ;
2233 l_registered_employer hr_organization_units.organization_id%TYPE;
2234 l_payroll_action_id pay_payroll_actions.payroll_action_id%TYPE ;
2235 l_year_start pay_payroll_Actions.effective_date%TYPE;
2236 l_year_end pay_payroll_actions.effective_date%TYPE;
2237 l_employee_type per_all_people_f.current_Employee_Flag%TYPE;
2238 l_lst_yr_term varchar2(10);
2239 l_fin_year VARCHAR2(20);
2240 l_archive_action_id pay_assignment_actions.assignment_action_id%TYPE;
2241
2242 l_eit_value VARCHAR2(10);
2243
2244 l_new_count NUMBER;
2245 l_old_count NUMBER;
2246 l_context_id ff_contexts.context_id%TYPE;
2247
2248 BEGIN
2249 g_debug := hr_utility.debug_enabled;
2250
2251 IF g_debug
2252 THEN
2253 l_procedure := g_package||'.modify_and_archive_code';
2254 hr_utility.set_location('Entering Procedure '||l_procedure, 3000);
2255 END IF;
2256
2257 /* Print All the DB Items Values got from Archive */
2258 IF g_debug
2259 THEN
2260 IF (p_all_tab_new.COUNT > 0)
2261 THEN
2262 FOR i IN p_all_tab_new.FIRST..p_all_tab_new.LAST
2263 LOOP
2264 hr_utility.set_location(rpad(i,5,' ')||rpad(p_all_tab_new(i).db_item_name,50,' ')||rpad(p_all_tab_new(i).db_item_value,30,' '),3010);
2265 END LOOP;
2266 END IF;
2267 END IF;
2268
2269 OPEN c_action(p_assignment_action_id);
2270 FETCH c_action INTO l_business_group_id
2271 ,l_registered_employer
2272 ,l_employee_type
2273 ,l_payroll_action_id
2274 ,l_assignment_id
2275 ,l_year_start
2276 ,l_year_end
2277 ,l_lst_yr_term
2278 ,l_fin_year;
2279 CLOSE c_action;
2280
2281
2282 OPEN get_orig_archive_id(l_assignment_id
2283 ,l_fin_year
2284 ,l_registered_employer);
2285 FETCH get_orig_archive_id INTO l_archive_action_id;
2286 CLOSE get_orig_archive_id;
2287
2288 IF g_debug
2289 THEN
2290 hr_utility.set_location('l_business_group_id '||l_business_group_id,3020);
2291 hr_utility.set_location('l_registered_employer '||l_registered_employer,3020);
2292 hr_utility.set_location('l_employee_type '||l_employee_type,3020);
2293 hr_utility.set_location('l_payroll_action_id '||l_payroll_action_id,3020);
2294 hr_utility.set_location('l_assignment_id '||l_assignment_id,3020);
2295 hr_utility.set_location('l_year_start '||l_year_start,3020);
2296 hr_utility.set_location('l_year_end '||l_year_end,3020);
2297 hr_utility.set_location('l_lst_yr_term '||l_lst_yr_term,3020);
2298 hr_utility.set_location('l_fin_year '||l_fin_year,3020);
2299 hr_utility.set_location('l_archive_action_id '||l_archive_action_id,3020);
2300 END IF;
2301
2302
2303 /* Now you have all the archive items - slot them according to Datafile record
2304 Initialize the PL/SQL tables to NULL
2305 */
2306
2307 l_cmn_tab_new.DELETE;
2308 l_payg_tab_new.DELETE;
2309 l_etp_cmn_tab_new.DELETE;
2310 l_etp_1_tab_new.DELETE;
2311 l_etp_2_tab_new.DELETE;
2312 l_etp_3_tab_new.DELETE;
2313 l_etp_4_tab_new.DELETE;
2314 l_amend_types_new.DELETE;
2315 l_fw1_payg_tab_new.DELETE; /*bug9147430*/
2316 l_fw2_payg_tab_new.DELETE; /*bug9147430*/
2317
2318 /* Initialize all Amended Payment Summary Flags,
2319 Index Meaning Value
2320 0. Common A
2321 1 Standard O
2322 2 ETP1 O
2323 3 ETP2 O
2324 4 ETP3 O
2325 5 ETP4 O
2326 */
2327
2328 l_amend_types_new(0).db_item_name := 'X_PAYMENT_SUMMARY_TYPE';
2329 l_amend_types_new(0).db_item_value := 'A';
2330
2331 l_amend_types_new(1).db_item_name := 'X_PAYG_PAYMENT_SUMMARY_TYPE';
2332 l_amend_types_new(1).db_item_value := 'O';
2333
2334 l_amend_types_new(2).db_item_name := 'X_ETP1_PAYMENT_SUMMARY_TYPE';
2335 l_amend_types_new(2).db_item_value := 'O';
2336
2337 l_amend_types_new(3).db_item_name := 'X_ETP2_PAYMENT_SUMMARY_TYPE';
2338 l_amend_types_new(3).db_item_value := 'O';
2339
2340 l_amend_types_new(4).db_item_name := 'X_ETP3_PAYMENT_SUMMARY_TYPE';
2341 l_amend_types_new(4).db_item_value := 'O';
2342
2343 l_amend_types_new(5).db_item_name := 'X_ETP4_PAYMENT_SUMMARY_TYPE';
2344 l_amend_types_new(5).db_item_value := 'O';
2345
2346 l_amend_types_new(6).db_item_name := 'X_FW1_PAYMENT_SUMMARY_TYPE'; /*bug9147430*/
2347 l_amend_types_new(6).db_item_value := 'O';
2348
2349 l_amend_types_new(7).db_item_name := 'X_FW2_PAYMENT_SUMMARY_TYPE'; /*bug9147430*/
2350 l_amend_types_new(7).db_item_value := 'O';
2351
2352 /* Check count and set flags if some items are missing in New Run
2353 */
2354
2355 l_new_count := NVL(p_all_tab_new.LAST,-1) + 1;
2356
2357 OPEN get_context_id('ASSIGNMENT_ACTION_ID');
2358 FETCH get_context_id INTO l_context_id;
2359 CLOSE get_context_id;
2360
2361 OPEN get_archive_item_count(l_archive_action_id,l_context_id);
2362 FETCH get_archive_item_count INTO l_old_count;
2363 CLOSE get_archive_item_count ;
2364
2365 IF g_debug THEN
2366 hr_utility.set_location('Old Archive Count '||l_old_count,3030);
2367 hr_utility.set_location('New Archive Count '||l_new_count,3030);
2368 END IF;
2369
2370 IF l_old_count > l_new_count
2371 THEN
2372 /* Some Items Missing from New Archive - Find and Set the Amend Flags appropriately
2373 */
2374 find_new_missing_items(l_archive_action_id
2375 ,l_old_count
2376 ,p_all_tab_new
2377 ,l_new_count);
2378 END IF;
2379
2380 slot_items_build_archive_list(l_archive_action_id
2381 ,p_all_tab_new);
2382
2383 /* 1. Archive all Common Information - Old
2384 2. Archive all Standard Information - Old/New based on EIT
2385 3. Archive all ETP Information - Old/New based on EIT
2386 4. Archive Amended Payment Summary Flags
2387 */
2388
2389 archive_db_items_tab
2390 (p_assignment_action_id => p_assignment_action_id
2391 ,p_db_item_tab => l_cmn_tab_new);
2392
2393 archive_db_items_tab
2394 (p_assignment_action_id => p_assignment_action_id
2395 ,p_db_item_tab => l_payg_tab_new);
2396
2397 archive_db_items_tab
2398 (p_assignment_action_id => p_assignment_action_id
2399 ,p_db_item_tab => l_etp_cmn_tab_new);
2400
2401 archive_db_items_tab
2402 (p_assignment_action_id => p_assignment_action_id
2403 ,p_db_item_tab => l_etp_1_tab_new);
2404
2405 archive_db_items_tab
2406 (p_assignment_action_id => p_assignment_action_id
2407 ,p_db_item_tab => l_etp_2_tab_new);
2408
2409 archive_db_items_tab
2410 (p_assignment_action_id => p_assignment_action_id
2411 ,p_db_item_tab => l_etp_3_tab_new);
2412
2413 archive_db_items_tab
2414 (p_assignment_action_id => p_assignment_action_id
2415 ,p_db_item_tab => l_etp_4_tab_new);
2416
2417 archive_db_items_tab /*bug9147430*/
2418 (p_assignment_action_id => p_assignment_action_id
2419 ,p_db_item_tab => l_fw1_payg_tab_new);
2420
2421 archive_db_items_tab /*bug9147430*/
2422 (p_assignment_action_id => p_assignment_action_id
2423 ,p_db_item_tab => l_fw2_payg_tab_new);
2424
2425 archive_db_items_tab
2426 (p_assignment_action_id => p_assignment_action_id
2427 ,p_db_item_tab => l_amend_types_new);
2428
2429 IF g_debug
2430 THEN
2431 hr_utility.set_location('Leaving Procedure '||l_procedure, 3000);
2432 END IF;
2433
2434 END modify_and_archive_code;
2435
2436 PROCEDURE spawn_data_file
2437 (p_payroll_action_id IN pay_payroll_actions.payroll_action_id%TYPE)
2438 IS
2439
2440 l_payroll_action_id pay_payroll_actions.payroll_action_id%TYPE;
2441 l_business_group_id NUMBER;
2442 l_start_date DATE;
2443 l_end_date DATE;
2444 l_effective_date DATE;
2445 l_legal_employer NUMBER;
2446 l_financial_year_code VARCHAR2(10);
2447 l_test_efile VARCHAR2(10);
2448 l_financial_year VARCHAR2(10);
2449 l_legislative_param VARCHAR2(200);
2450 l_procedure VARCHAR2(80);
2451 ps_request_id NUMBER;
2452 --------------------------------------------------------------------+
2453 -- Cursor : csr_params
2454 -- Description : Fetches User Parameters from Legislative_paramters
2455 -- column.
2456 --------------------------------------------------------------------+
2457
2458 CURSOR csr_magtape_params(c_payroll_action_id pay_payroll_actions.payroll_action_id%TYPE)
2459 IS
2460 SELECT pay_core_utils.get_parameter('TEST_EFILE',legislative_parameters) TEST_EFILE,
2461 pay_core_utils.get_parameter('BUSINESS_GROUP_ID',legislative_parameters) BUSINESS_GROUP_ID,
2462 pay_core_utils.get_parameter('FINANCIAL_YEAR',legislative_parameters) FINANCIAL_YEAR,
2463 pay_core_utils.get_parameter('REGISTERED_EMPLOYER',legislative_parameters) REGISTERED_EMPLOYER,
2464 to_date(pay_core_utils.get_parameter('START_DATE',legislative_parameters),'YYYY/MM/DD') start_date,
2465 to_date(pay_core_utils.get_parameter('END_DATE',legislative_parameters),'YYYY/MM/DD') end_date,
2466 to_date(pay_core_utils.get_parameter('EFFECTIVE_DATE',legislative_parameters),'YYYY/MM/DD') EFFECTIVE_DATE
2467 FROM pay_payroll_actions ppa
2468 WHERE ppa.payroll_action_id = c_payroll_action_id;
2469
2470
2471 CURSOR csr_lookup_code (c_financial_year VARCHAR2)
2472 IS
2473 SELECT LOOKUP_CODE
2474 FROM HR_LOOKUPS
2475 WHERE lookup_type = 'AU_PS_FINANCIAL_YEAR'
2476 AND enabled_flag = 'Y'
2477 AND meaning = c_financial_year;
2478
2479 BEGIN
2480
2481 g_debug := hr_utility.debug_enabled;
2482
2483 IF g_debug
2484 THEN
2485 l_procedure := g_package||'.spawn_data_file';
2486 hr_utility.set_location('Entering package '||l_procedure,4500);
2487 END IF;
2488
2489 ps_request_id :=-1;
2490 l_TEST_EFILE :='N';
2491
2492 OPEN csr_magtape_params(p_payroll_action_id);
2493 FETCH csr_magtape_params
2494 INTO l_test_efile,
2495 l_business_group_id,
2496 l_financial_year,
2497 l_legal_employer,
2498 l_start_date,
2499 l_end_date,
2500 l_effective_date;
2501 CLOSE csr_magtape_params;
2502
2503 IF l_TEST_EFILE = 'Y'
2504 THEN
2505 OPEN csr_lookup_code(l_financial_year);
2506 FETCH csr_lookup_code
2507 INTO l_financial_year_code;
2508 CLOSE csr_lookup_code;
2509
2510 l_legislative_param := 'BUSINESS_GROUP_ID=' || l_business_group_id ||' '
2511 || 'FINANCIAL_YEAR=' || l_FINANCIAL_YEAR ||' '
2512 || 'REGISTERED_EMPLOYER=' || l_legal_employer ||' '
2513 || 'IS_TESTING=' || 'Y' ||' '
2514 || 'ARCHIVE_PAYROLL_ACTION=' || to_char(p_payroll_action_id)||' '
2515 || 'END_DATE=' || to_char(l_end_date,'YYYY/MM/DD HH:MI:SS')||' '
2516 || 'PAYMENT_SUMMARY_TYPE=A';
2517
2518 ps_request_id := fnd_request.submit_request
2519 ('PAY',
2520 'PYAUPSDF',
2521 null,
2522 null,
2523 false,
2524 'ARCHIVE',
2525 'AU_PS_DATA_FILE_VAL', -- Report_format of magtape process
2526 'AU',
2527 to_char(l_start_date,'YYYY/MM/DD HH:MI:SS'),
2528 to_char(l_EFFECTIVE_DATE,'YYYY/MM/DD HH:MI:SS'),
2529 'REPORT',
2530 l_business_group_id,
2531 null,
2532 null,
2533 l_legal_employer,
2534 l_FINANCIAL_YEAR_code,
2535 'END_DATE='||to_char(l_end_date,'YYYY/MM/DD HH:MI:SS'),
2536 'Y', -- IS_TESTING Parameter
2537 'A',
2538 'AU_PAY_SUMM_AMEND',
2539 to_number(p_payroll_action_id), -- Archive_PAyroll_Action
2540 l_legislative_param -- Legislative parameters
2541 );
2542
2543 END IF;
2544
2545 IF g_debug
2546 THEN
2547 hr_utility.set_location('Leaving procedure '||l_procedure,4540);
2548 END IF;
2549
2550 END spawn_data_file;
2551
2552 END pay_au_payment_summary_amend;