eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.
PACKAGE BODY: APPS.PAY_AU_PAYMENT_SUMMARY_AMEND

Source


1 PACKAGE BODY pay_au_payment_summary_amend AS
2 /* $Header: pyaupsam.pkb 120.0.12010000.1 2008/10/13 13:34:31 avenkatk noship $*/
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 *** --------------------------------------------------------------------------+
16 */
17 
18 g_debug             boolean;
19 g_business_group_id number;
20 g_package           constant varchar2(30) := 'pay_au_payment_summary_amend';
21 g_legislation_code  constant varchar2(2)  := 'AU';
22 
23 
24 TYPE char_tab_type IS TABLE OF ff_user_entities.user_entity_name%TYPE;
25 
26 /* The following global tables store the User entity type of each DB Item */
27 
28 g_payg_db_items char_tab_type;
29 g_etp1_db_items char_tab_type;
30 g_etp2_db_items char_tab_type;
31 g_etp3_db_items char_tab_type;
32 g_etp4_db_items char_tab_type;
33 g_etp_cmn_db_items char_tab_type;
34 
35 
36 /* The following variables hold the slotted DB Items */
37 
38 l_cmn_tab_new archive_db_tab;
39 l_payg_tab_new archive_db_tab;
40 l_etp_cmn_tab_new archive_db_tab;
41 l_etp_1_tab_new archive_db_tab;
42 l_etp_2_tab_new archive_db_tab;
43 l_etp_3_tab_new archive_db_tab;
44 l_etp_4_tab_new archive_db_tab;
45 l_amend_types_new archive_db_tab;
46 
47 
48 
49 /*
50 --------------------------------------------------------------------
51     Name  : range_code
52     Type  : Procedure
53     Access: Public
54     Description: This procedure returns a sql string to
55                  select a range of assignments eligible for archival.
56   --------------------------------------------------------------------
57 */
58 
59 PROCEDURE range_code
60         (p_payroll_action_id   IN pay_payroll_actions.payroll_action_id%TYPE,
61          p_sql                 OUT NOCOPY VARCHAR2)
62 IS
63 BEGIN
64 
65 g_debug := hr_utility.debug_enabled;
66 
67 IF g_debug
68 THEN
69         hr_utility.set_location('Start of range_code    ',1);
70 END IF;
71 
72 p_sql   := ' select distinct p.person_id'                                       ||
73              ' from   per_people_f p,'                                        ||
74                     ' pay_payroll_actions pa'                                     ||
75              ' where  pa.payroll_action_id = :payroll_action_id'                  ||
76              ' and    p.business_group_id = pa.business_group_id'                 ||
77              ' order by p.person_id';
78 
79 IF g_debug
80 THEN
81         hr_utility.set_location('End of range_code',2);
82 END IF;
83 END range_code;
84 
85 
86 /*
87 --------------------------------------------------------------------
88     Name  : initialization_code
89     Type  : Procedure
90     Access: Public
91     Description:  This procedure initializes global variables required
92                   by Archive. The g_payment_summary_type parameters
93                   is set to 'A'
94   --------------------------------------------------------------------
95 */
96 
97 PROCEDURE initialization_code
98         (p_payroll_action_id  IN pay_payroll_actions.payroll_action_id%TYPE)
99 IS
100 
101 l_procedure VARCHAR2(80);
102 
103 BEGIN
104 
105 g_debug := hr_utility.debug_enabled;
106 IF g_debug
107 THEN
108     l_procedure     := g_package||'.initialization_code_amend';
109     hr_utility.set_location('In Procedure   '||l_procedure,1000);
110     END IF;
111 
112     pay_au_payment_summary.initialization_code(p_payroll_action_id);
113     pay_au_payment_summary.g_payment_summary_type   := 'A'; /*Reset the Payment Summary Type Variable */
114     populate_user_entity_types;                             /* Initialize the DB Item Types */
115 
116 IF g_debug
117 THEN
118     hr_utility.set_location('Leaving Procedure   '||l_procedure,1000);
119 END IF;
120 
121 EXCEPTION
122 WHEN others THEN
123 IF g_debug THEN
124     hr_utility.set_location('Error in initialization_code',1000);
125 END IF;
126 raise;
127 END initialization_code;
128 
129 
130 /*
131 --------------------------------------------------------------------
132 Name  : assignment_action_code
133 Type  : Procedure
134 Access: Public
135 Description:This procedure further restricts the assignment_id's
136             returned by range_code.
137             The procedure uses the Assignment ID or Assignment Set ID
138             parameter and restricts assignments to be archived
139             it then calls hr_nonrun.insact to create an assignment action id
140   --------------------------------------------------------------------
141 */
142 
143 PROCEDURE assignment_action_code
144     (p_payroll_action_id  IN pay_payroll_actions.payroll_action_id%TYPE,
145      p_start_person_id    IN per_all_people_f.person_id%TYPE,
146      p_end_person_id      IN per_all_people_f.person_id%TYPE,
147      p_chunk              IN NUMBER)
148 IS
149 
150 v_next_action_id  pay_assignment_actions.assignment_action_id%type;
151 
152 v_lst_year_start       date ;
153 v_fbt_year_start       date ;
154 v_lst_fbt_year_start   date ;
155 v_fbt_year_end         date ;
156 v_fin_year_start       date ;
157 v_fin_year_end         date ;
158 v_assignment_id        varchar2(50);
159 v_registered_employer  varchar2(50);
160 v_financial_year       varchar2(50);
161 v_payroll_id           varchar2(50);
162 v_employee_type        varchar2(1);
163 v_asg_id               number;
164 v_reg_emp              number;
165 l_lst_yr_term          varchar(10);
166 
167 v_assignment_set_id    VARCHAR2(50);
168 
169 l_procedure            VARCHAR2(80);
170 
171 CURSOR get_params(c_payroll_action_id  per_all_assignments_f.assignment_id%TYPE)
172 IS
173 SELECT   to_date('01-07-'||substr(pay_core_utils.get_parameter('FINANCIAL_YEAR',legislative_parameters),1,4),'DD-MM-YYYY') Financial_year_start
174         ,to_date('30-06-'||substr(pay_core_utils.get_parameter('FINANCIAL_YEAR',legislative_parameters),6,4),'DD-MM-YYYY') Financial_year_end
175         ,to_date('01-04-'||substr(pay_core_utils.get_parameter('FINANCIAL_YEAR',legislative_parameters),1,4),'DD-MM-YYYY') FBT_year_start
176         ,to_date('30-06-'||substr(pay_core_utils.get_parameter('FINANCIAL_YEAR',legislative_parameters),1,4),'DD-MM-YYYY') FBT_year_end
177         ,decode(pay_core_utils.get_parameter('EMPLOYEE_TYPE',legislative_parameters),'C','Y','T','N','B','%')   Employee_type
178         ,pay_core_utils.get_parameter('REGISTERED_EMPLOYER',legislative_parameters)                             Registered_Employer
179         ,pay_core_utils.get_parameter('FINANCIAL_YEAR',legislative_parameters)                                  Financial_year
180         ,pay_core_utils.get_parameter('ASSIGNMENT_ID',legislative_parameters)               Assignment_id
181         ,decode(pay_core_utils.get_parameter('PAYROLL',legislative_parameters),NULL,'%',pay_core_utils.get_parameter('PAYROLL',legislative_parameters)) payroll_id
182         ,pay_core_utils.get_parameter('LST_YR_TERM',legislative_parameters)              lst_yr_term    /*3661230*/
183         ,pay_core_utils.get_parameter('BUSINESS_GROUP_ID',legislative_parameters)               Business_group_id
184         ,pay_core_utils.get_parameter('ASSIGNMENT_SET_ID',legislative_parameters)               assignment_set_id
185 FROM  pay_payroll_actions
186 WHERE payroll_action_id = c_payroll_Action_id;
187 
188 CURSOR process_assignments_only(c_payroll_action_id  IN pay_payroll_actions.payroll_action_id%TYPE
189                            ,c_start_person_id    IN per_all_people_f.person_id%TYPE
190                            ,c_end_person_id      IN per_all_people_f.person_id%TYPE
191                            ,c_assignment_id      IN per_all_assignments_f.assignment_id%TYPE
192                            ,c_financial_year     IN VARCHAR2
193                            ,c_tax_unit_id        IN pay_assignment_actions.tax_unit_iD%TYPE)
194 IS
195 SELECT DISTINCT paf.assignment_id
196 FROM   per_assignments_f paf
197       ,per_people_f      ppf
198       ,pay_payroll_actions ppa
199 WHERE  ppa.payroll_action_id = c_payroll_action_id
200 AND    ppf.person_id  BETWEEN c_start_person_id AND c_end_person_id
201 AND    ppf.person_id         = paf.person_id
202 AND    paf.assignment_id     = c_assignment_id
203 AND    paf.business_group_id = ppa.business_group_id
204 AND    EXISTS
205         ( /* Check if a Datafile is run for this year */
206            SELECT '1'
207            FROM  pay_payroll_actions ppa1
208                 ,pay_assignment_actions paa1
209            WHERE ppa1.payroll_action_id = paa1.payroll_action_id
210            AND   ppa1.report_type       = 'AU_PS_DATA_FILE'
211            AND   ppa1.report_qualifier  = 'AU'
212            AND   ppa1.report_category   = 'REPORT'
213            AND   paa1.assignment_id     =  paf.assignment_id
214            AND   pay_core_utils.get_parameter('FINANCIAL_YEAR',ppa1.legislative_parameters) = c_financial_year
215            AND   pay_core_utils.get_parameter('REGISTERED_EMPLOYER',ppa1.legislative_parameters) = c_tax_unit_id
216            )
217 AND    NOT EXISTS
218         ( /* Check if a locked Amended Payment Summary does not exist for this year */
219         SELECT '1'
220         FROM   pay_payroll_actions ppa2
221               ,pay_assignment_actions paa2
222               ,pay_action_interlocks pai
223         WHERE   ppa2.payroll_action_id = paa2.payroll_action_id
224           AND   ppa2.report_type       = 'AU_PAY_SUMM_AMEND'
225           AND   ppa2.report_qualifier  = 'AU'
226           AND   ppa2.report_category   = 'REPORT'
227           AND   pay_core_utils.get_parameter('FINANCIAL_YEAR',ppa2.legislative_parameters) = c_financial_year
228           AND   pay_core_utils.get_parameter('REGISTERED_EMPLOYER',ppa2.legislative_parameters) = c_tax_unit_id
229           AND   paa2.assignment_id      = paf.assignment_id
230           AND   pai.locked_action_id   = paa2.assignment_action_id
231         );
232 
233 
234 CURSOR process_assignments(c_payroll_action_id  IN pay_payroll_actions.payroll_action_id%TYPE
235                            ,c_start_person_id    IN per_all_people_f.person_id%TYPE
236                            ,c_end_person_id      IN per_all_people_f.person_id%TYPE
237                            ,c_assignment_set_id  IN NUMBER
238                            ,c_financial_year     IN VARCHAR2
239                            ,c_tax_unit_id        IN pay_assignment_actions.tax_unit_iD%TYPE)
240 IS
241 SELECT DISTINCT paf.assignment_id
242 FROM   per_assignments_f paf
243       ,per_people_f      ppf
244       ,pay_payroll_actions ppa
245       ,hr_assignment_set_amendments  has
246 WHERE  ppa.payroll_action_id = c_payroll_action_id
247 AND    ppf.person_id  BETWEEN c_start_person_id AND c_end_person_id
248 AND    ppf.person_id         = paf.person_id
249 AND    paf.assignment_id     = has.assignment_id
250 AND    has.assignment_set_id  = c_assignment_set_id
251 AND    upper(has.include_or_exclude) = 'I'
252 AND    paf.business_group_id = ppa.business_group_id
253 AND    EXISTS
254         ( /* Check if a Datafile is run for this year */
255            SELECT '1'
256            FROM  pay_payroll_actions ppa1
257                 ,pay_assignment_actions paa1
258            WHERE ppa1.payroll_action_id = paa1.payroll_action_id
259            AND   ppa1.report_type       = 'AU_PS_DATA_FILE'
260            AND   ppa1.report_qualifier  = 'AU'
261            AND   ppa1.report_category   = 'REPORT'
262            AND   paa1.assignment_id     =  paf.assignment_id
263            AND   pay_core_utils.get_parameter('FINANCIAL_YEAR',ppa1.legislative_parameters) = c_financial_year
264            AND   pay_core_utils.get_parameter('REGISTERED_EMPLOYER',ppa1.legislative_parameters) = c_tax_unit_id
265            )
266 AND    NOT EXISTS
267         ( /* Check if a locked Amended Payment Summary does not exist for this year */
268         SELECT '1'
269         FROM   pay_payroll_actions ppa2
270               ,pay_assignment_actions paa2
271               ,pay_action_interlocks pai
272         WHERE   ppa2.payroll_action_id = paa2.payroll_action_id
273           AND   ppa2.report_type       = 'AU_PAY_SUMM_AMEND'
274           AND   ppa2.report_qualifier  = 'AU'
275           AND   ppa2.report_category   = 'REPORT'
276           AND   pay_core_utils.get_parameter('FINANCIAL_YEAR',ppa2.legislative_parameters) = c_financial_year
277           AND   pay_core_utils.get_parameter('REGISTERED_EMPLOYER',ppa2.legislative_parameters) = c_tax_unit_id
278           AND   paa2.assignment_id      = paf.assignment_id
279           AND   pai.locked_action_id    = paa2.assignment_action_id
280         );
281 
282 CURSOR   next_action_id
283 IS
284 SELECT pay_assignment_actions_s.nextval
285 FROM  dual;
286 
287 BEGIN
288 
289 g_debug := hr_utility.debug_enabled;
290 
291 IF g_debug
292 THEN
293     l_procedure     := g_package||'.assignment_action_coded';
294     hr_utility.set_location('In Procedure   '||l_procedure,1020);
295 END IF;
296 
297 /* Get the paramters for archival process */
298 OPEN   get_params(p_payroll_action_id);
299 FETCH  get_params
300 INTO      v_fin_year_start
301          ,v_fin_year_end
302          ,v_fbt_year_start
303          ,v_fbt_year_end
304          ,v_employee_type
305          ,v_registered_employer
306          ,v_financial_year
307          ,v_assignment_id
308          ,v_payroll_id
309          ,l_lst_yr_term
310          ,g_business_group_id
311          ,v_assignment_set_id;
312 CLOSE get_params;
313 
314 v_reg_emp := to_number(v_registered_employer);
315 
316 IF g_debug
317 THEN
318     hr_utility.set_location('p_payroll_action_id       '||p_payroll_action_id,1030);
319     hr_utility.set_location('p_start_person_id         '||p_start_person_id,1030);
320     hr_utility.set_location('p_end_person_id           '||p_end_person_id,1030);
321     hr_utility.set_location('v_assignment_set_id       '||to_number(v_assignment_set_id),1030);
322     hr_utility.set_location('v_financial_year          '||v_financial_year,1030);
323     hr_utility.set_location('v_assignment_id           '||v_assignment_id,1030);
324     hr_utility.set_location('v_reg_emp                 '||v_reg_emp,1030);
325 END IF;
326 
327 IF v_assignment_id IS NOT NULL
328 THEN
329         FOR csr_rec IN process_assignments_only(p_payroll_action_id
330                                                ,p_start_person_id
331                                                ,p_end_person_id
332                                                ,to_number(v_assignment_id)
333                                                ,v_financial_year
334                                                ,v_reg_emp)
335         LOOP
336                 OPEN next_action_id;
337                 FETCH next_action_id INTO v_next_action_id;
338                 CLOSE next_action_id;
339 
340                 hr_nonrun_asact.insact(v_next_action_id,
341                                        csr_rec.assignment_id,
342                                        p_payroll_action_id,
343                                        p_chunk,
344                                        NULL);
345 
346         END LOOP;
347 ELSIF v_assignment_set_id IS NOT NULL
348 THEN
349 
350         FOR csr_rec IN process_assignments(p_payroll_action_id
351                                           ,p_start_person_id
352                                           ,p_end_person_id
353                                           ,to_number(v_assignment_set_id)
354                                           ,v_financial_year
355                                           ,v_reg_emp)
356         LOOP
357                 OPEN next_action_id;
358                 FETCH next_action_id INTO v_next_action_id;
359                 CLOSE next_action_id;
360 
361                 hr_nonrun_asact.insact(v_next_action_id,
362                                        csr_rec.assignment_id,
363                                        p_payroll_action_id,
364                                        p_chunk,
365                                        NULL);
366         END LOOP;
367 
368 END IF;
369 
370 IF g_debug THEN
371     hr_utility.set_location('Leaving  '||l_procedure,1040);
372 END IF;
373 
374 EXCEPTION
375 WHEN others THEN
376 IF g_debug THEN
377     hr_utility.set_location('Error raised in assignment_action_code_amend procedure ',1050);
378 END IF;
379 raise;
380 END assignment_action_code;
381 
382 
383 /*
384 --------------------------------------------------------------------
385 Name  : populate_user_entity_types
386 Type  : Procedure
387 Access: Public
388 Description:This procedure populates the Global PL/SQL table with
389             the User Entity type of all shipped DB items.
390             PAYG        - PAYG Record
391             ETP_CMN     - Common data reported in all ETP records
392             ETP1        - Transtional (Y), Part of Prev Term (Y) ETP Record
393             ETP2        - Transtional (Y), Part of Prev Term (N) ETP Record
394             ETP3        - Transtional (N), Part of Prev Term (Y) ETP Record
395             ETP4        - Transtional (N), Part of Prev Term (N) ETP Record
396 --------------------------------------------------------------------
397 */
398 
399 
400 PROCEDURE populate_user_entity_types
401 IS
402 
403 CURSOR csr_payg_items
404 IS
405 SELECT user_entity_name
406 FROM  ff_user_entities
407 WHERE legislation_code = 'AU'
408 AND (  user_entity_name LIKE 'X_ALLOWANCE%'
409         OR user_entity_name LIKE 'X_EMPLOYEE%DATE%'
410         OR user_entity_name LIKE 'X_UNION%'
411         OR user_entity_name LIKE 'X_%ASG_YTD'
412         OR user_entity_name IN ('X_EMPLOYEE_TAX_FILE_NUMBER')
413         )
414 AND user_entity_name NOT LIKE 'X_%83%_ASG_YTD'
415 AND user_entity_name NOT LIKE 'X_%TRANS%_ASG_YTD'
416 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');
417 
418 
419 CURSOR csr_etp1_items
420 IS
421 SELECT user_entity_name
422 FROM  ff_user_entities
423 WHERE legislation_code = 'AU'
424 AND    user_entity_name IN
425 ( 'X_ETP_DED_TRANS_PPTERM_ASG_YTD','X_INV_PAY_TRANS_PPTERM_ASG_YTD'
426  ,'X_POST_JUN_83_TAXED_TRANS_PPTERM_ASG_YTD','X_PRE_JUL_83_COMP_TRANS_PPTERM_ASG_YTD');
427 
428 CURSOR csr_etp2_items
429 IS
430 SELECT user_entity_name
431 FROM  ff_user_entities
432 WHERE legislation_code = 'AU'
433 AND    user_entity_name IN
434 ( 'X_ETP_DED_TRANS_NOT_PPTERM_ASG_YTD','X_INV_PAY_TRANS_NOT_PPTERM_ASG_YTD'
435  ,'X_POST_JUN_83_TAXED_TRANS_NOT_PPTERM_ASG_YTD','X_PRE_JUL_83_COMP_TRANS_NOT_PPTERM_ASG_YTD');
436 
437 CURSOR csr_etp3_items
438 IS
439 SELECT user_entity_name
440 FROM  ff_user_entities
441 WHERE legislation_code = 'AU'
442 AND    user_entity_name IN
443 ( 'X_ETP_DED_NOT_TRANS_PPTERM_ASG_YTD','X_INV_PAY_NOT_TRANS_PPTERM_ASG_YTD'
444 ,'X_POST_JUN_83_TAXED_NOT_TRANS_PPTERM_ASG_YTD','X_PRE_JUL_83_COMP_NOT_TRANS_PPTERM_ASG_YTD');
445 
446 CURSOR csr_etp4_items
447 IS
448 SELECT user_entity_name
449 FROM  ff_user_entities
450 WHERE legislation_code = 'AU'
451 AND    user_entity_name IN
452 ( 'X_ETP_DED_NOT_TRANS_NOT_PPTERM_ASG_YTD','X_INV_PAY_NOT_TRANS_NOT_PPTERM_ASG_YTD'
453  ,'X_POST_JUN_83_TAXED_NOT_TRANS_NOT_PPTERM_ASG_YTD','X_PRE_JUL_83_COMP_NOT_TRANS_NOT_PPTERM_ASG_YTD');
454 
455 CURSOR csr_etp_cmn_items
456 IS
457 SELECT user_entity_name
458 FROM  ff_user_entities
459 WHERE legislation_code = 'AU'
460 AND (  user_entity_name LIKE 'X_ETP%DATE%'
461       OR user_entity_name LIKE 'X_DAYS%'
462       OR user_entity_name IN ('X_ETP_TAX_FILE_NUMBER')
463        );
464 
465 l_procedure     VARCHAR2(200);
466 
467 BEGIN
468 
469         g_debug := hr_utility.debug_enabled;
470         IF g_debug
471         THEN
472                 l_procedure     := g_package||'.populate_user_entity_types';
473                 hr_utility.set_location('Entering Procedure     '||l_procedure,2400);
474         END IF;
475 
476         OPEN csr_payg_items;
477         FETCH csr_payg_items BULK COLLECT INTO g_payg_db_items;
478         CLOSE csr_payg_items;
479 
480         OPEN  csr_etp_cmn_items;
481         FETCH csr_etp_cmn_items BULK COLLECT INTO g_etp_cmn_db_items;
482         CLOSE csr_etp_cmn_items ;
483 
484         OPEN  csr_etp1_items;
485         FETCH csr_etp1_items BULK COLLECT INTO g_etp1_db_items;
486         CLOSE csr_etp1_items ;
487 
488         OPEN  csr_etp2_items;
489         FETCH csr_etp2_items BULK COLLECT INTO g_etp2_db_items;
490         CLOSE csr_etp2_items ;
491 
492         OPEN  csr_etp3_items;
493         FETCH csr_etp3_items BULK COLLECT INTO g_etp3_db_items;
494         CLOSE csr_etp3_items ;
495 
496         OPEN  csr_etp4_items;
497         FETCH csr_etp4_items BULK COLLECT INTO g_etp4_db_items;
498         CLOSE csr_etp4_items ;
499 
500         IF g_debug
501         THEN
502                 hr_utility.set_location('Leaving Procedure     '||l_procedure,2420);
503         END IF;
504 END populate_user_entity_types;
505 
506 /*
507 --------------------------------------------------------------------
508 Name  : check_user_entity_type
509 Type  : Function
510 Access: Public
511 Description:This procedure takes a User Entity Name and returns the
512             Data file record which corresponds to the ITEM.
513             Values returned,
514             PAYG        - PAYG Record
515             ETP_CMN     - Common data reported in all ETP records
516             ETP1        - Transtional (Y), Part of Prev Term (Y) ETP Record
517             ETP2        - Transtional (Y), Part of Prev Term (N) ETP Record
518             ETP3        - Transtional (N), Part of Prev Term (Y) ETP Record
519             ETP4        - Transtional (N), Part of Prev Term (N) ETP Record
520             ETP_CMN_BAL - ETP Balances - not used anymore now
521             AMEND       - Amend PS Flag Items
522             CMN         - Rest of the Items (Default Value returned)
523   --------------------------------------------------------------------
524 */
525 
526 FUNCTION check_user_entity_type(p_user_entity_name IN ff_user_entities.user_entity_name%TYPE)
527 RETURN VARCHAR2
528 IS
529 
530 
531 l_return_value  VARCHAR2(20);
532 l_procedure     VARCHAR2(80);
533 
534 l_entity_id     ff_user_entities.user_entity_id%TYPE;
535 l_found         BOOLEAN;
536 
537 BEGIN
538 
539 g_debug := hr_utility.debug_enabled;
540 
541 IF g_debug
542 THEN
543         l_procedure     := g_package||'.check_user_entity_type';
544         hr_utility.set_location('Entering Procedure     '||l_procedure,2500);
545         hr_utility.set_location('p_user_entity_name     '||p_user_entity_name,2510);
546 END IF;
547 
548 IF p_user_entity_name IN ('X_PAYG_PAYMENT_SUMMARY_TYPE','X_PAYMENT_SUMMARY_TYPE','X_ETP1_PAYMENT_SUMMARY_TYPE'
549                           ,'X_ETP2_PAYMENT_SUMMARY_TYPE','X_ETP3_PAYMENT_SUMMARY_TYPE','X_ETP4_PAYMENT_SUMMARY_TYPE')
550 THEN
551         l_return_value  := 'AMEND';
552 
553 ELSIF p_user_entity_name IN ('X_LUMP_SUM_C_PAYMENTS_ASG_YTD','X_LUMP_SUM_C_DEDUCTIONS_ASG_YTD',
554                              'X_INVALIDITY_PAYMENTS_ASG_YTD')
555 THEN
556         l_return_value  := 'ETP_CMN_BAL';
557 ELSE
558 
559        /* ETP Items can have values
560                    1. ETP1 (YY)
561                    2. ETP2 (YN)
562                    3. ETP3 (NY)
563                    4. ETP4 (NN)
564                    5. ETP_CMN   (Rest of the Common Items)
565         */
566 
567         l_found         := FALSE;
568         IF (l_found = FALSE AND g_payg_db_items.COUNT > 0)
569         THEN
570                 FOR i IN g_payg_db_items.FIRST..g_payg_db_items.LAST
571                 LOOP
572                         IF (g_payg_db_items(i) = p_user_entity_name)
573                         THEN
574                                 l_found         := TRUE;
575                                 l_return_value  := 'PAYG';
576                         END IF;
577                 END LOOP;
578         END IF;
579 
580         IF (l_found = FALSE AND g_etp_cmn_db_items.COUNT > 0)
581         THEN
582                 FOR i IN g_etp_cmn_db_items.FIRST..g_etp_cmn_db_items.LAST
583                 LOOP
584                         IF (g_etp_cmn_db_items(i) = p_user_entity_name)
585                         THEN
586                                 l_found         := TRUE;
587                                 l_return_value  := 'ETP_CMN';
588                         END IF;
589                 END LOOP;
590         END IF;
591 
592         IF (l_found = FALSE AND g_etp1_db_items.COUNT > 0)
593         THEN
594                 FOR i IN g_etp1_db_items.FIRST..g_etp1_db_items.LAST
595                 LOOP
596                         IF (g_etp1_db_items(i) = p_user_entity_name)
597                         THEN
598                                 l_found         := TRUE;
599                                 l_return_value  := 'ETP1';
600                         END IF;
601                 END LOOP;
602         END IF;
603 
604         IF (l_found = FALSE AND g_etp2_db_items.COUNT > 0)
605         THEN
606                 FOR i IN g_etp2_db_items.FIRST..g_etp2_db_items.LAST
607                 LOOP
608                         IF (g_etp2_db_items(i) = p_user_entity_name)
609                         THEN
610                                 l_found         := TRUE;
611                                 l_return_value  := 'ETP2';
612                         END IF;
613                 END LOOP;
614         END IF;
615 
616 
617         IF (l_found = FALSE AND g_etp3_db_items.COUNT > 0)
618         THEN
619                 FOR i IN g_etp3_db_items.FIRST..g_etp3_db_items.LAST
620                 LOOP
621                         IF (g_etp3_db_items(i) = p_user_entity_name)
622                         THEN
623                                 l_found         := TRUE;
624                                 l_return_value  := 'ETP3';
625                         END IF;
626                 END LOOP;
627         END IF;
628 
629         IF (l_found = FALSE AND g_etp4_db_items.COUNT > 0)
630         THEN
631                 FOR i IN g_etp4_db_items.FIRST..g_etp4_db_items.LAST
632                 LOOP
633                         IF (g_etp4_db_items(i) = p_user_entity_name)
634                         THEN
635                                 l_found         := TRUE;
636                                 l_return_value  := 'ETP4';
637                         END IF;
638                 END LOOP;
639         END IF;
640 
641         l_return_value := NVL(l_return_value,'CMN');
642 
643 END IF;
644 
645         IF g_debug THEN
646                 hr_utility.set_location('Return Value           '||l_return_value,2520);
647                 hr_utility.set_location('Leaving Procedure      '||l_procedure,2530);
648         END IF;
649 
650         RETURN NVL(l_return_value,'CMN');
651 
652 END check_user_entity_type;
653 
654 
655 /*
656 --------------------------------------------------------------------
657 Name  : compare_user_entity_value
658 Type  : Function
659 Access: Private
660 Description:This procedure takes a User entity name and two values
661             and compares the same.
662             The following values are returned,
663                     Y - Value Matches
664                     N - Values Don't Match
665 --------------------------------------------------------------------
666 */
667 
668 FUNCTION compare_user_entity_value
669         (p_user_entity_name IN ff_user_entities.user_entity_name%TYPE
670         ,p_value1           IN ff_archive_items.value%TYPE
671         ,p_value2           IN ff_archive_items.value%TYPE
672         ,p_data_type         IN ff_database_items.data_type%TYPE)
673 RETURN VARCHAR2
674 IS
675 
676 l_procedure     VARCHAR2(80);
677 l_return_flag   VARCHAR2(5);
678 
679 BEGIN
680 
681 IF g_debug
682 THEN
683         l_procedure     := g_package||'.compare_user_entity_value';
684         hr_utility.set_location('Entering Function      '||l_procedure,2600);
685         hr_utility.set_location('p_user_entity_name     '||p_user_entity_name,2610);
686         hr_utility.set_location('p_value1               '||p_value1,2620);
687         hr_utility.set_location('p_value2               '||p_value2,2620);
688         hr_utility.set_location('p_data_type            '||p_data_type,2620);
689 END IF;
690 
691 l_return_flag   := 'Y'; /* Default - Values Match */
692 
693         IF p_data_type  = 'N'
694         THEN
695                 IF trunc(to_number(p_value1)) <> trunc(to_number(p_value2))
696                 THEN
697                         l_return_flag   := 'N';
698                 END IF;
699         ELSIF p_data_type  = 'D'
700         THEN
701                 IF fnd_date.canonical_to_date(p_value1) <> fnd_date.canonical_to_date(p_value2)
702                 THEN
703                         l_return_flag   := 'N';
704                 END IF;
705         ELSE
706                 IF trim(p_value1) <> trim(p_value2)
707                 THEN
708                         l_return_flag   := 'N';
709                 END IF;
710         END IF;
711 
712 IF g_debug
713 THEN
714         hr_utility.set_location('l_return_flag          '||l_return_flag,2640);
715         hr_utility.set_location('Leaving Function       '||l_procedure,2650);
716 END IF;
717 
718 RETURN l_return_flag;
719 
720 END compare_user_entity_value;
721 
722 
723 /*
724 --------------------------------------------------------------------
725 Name  : find_new_missing_items
726 Type  : Procedure
727 Access: Private
728 Description:This procedure is called when the count of items
729             for old and new archive do not match.
730             This Procedure identifies the missing item from New
731             Archive and sets the appropriate Amend PS Flag
732   --------------------------------------------------------------------
733 */
734 
735 PROCEDURE find_new_missing_items
736         (p_archive_action_id    IN pay_assignment_actions.assignment_action_id%TYPE
737         ,p_old_count            IN NUMBER
738         ,p_all_tab_new          IN archive_db_tab
739         ,p_new_count            IN NUMBER)
740 IS
741 
742 CURSOR get_archived_user_entities
743         (c_archive_action_id pay_assignment_actions.assignment_action_id%TYPE)
744 IS
745 SELECT  fue.user_entity_name
746 FROM    ff_archive_items fae,
747         ff_user_entities fue
748 WHERE  fae.context1 = c_archive_action_id
749 AND   fue.user_entity_id = fae.user_entity_id
750 AND   (
751         fue.user_entity_name    LIKE 'X_ALLOWANCE%'
752         OR fue.user_entity_name LIKE 'X_EMPLOYEE%DATE%'
753         OR fue.user_entity_name LIKE 'X_UNION%'
754         OR fue.user_entity_name LIKE 'X_%ASG_YTD'
755         OR  fue.user_entity_name IN( 'X_SORT_EMPLOYEE_TYPE','X_EMPLOYEE_TAX_FILE_NUMBER','X_ETP_TAX_FILE_NUMBER'
756                                 ,'X_ETP_DED_TRANS_PPTERM_ASG_YTD','X_INV_PAY_TRANS_PPTERM_ASG_YTD'
757                                 ,'X_POST_JUN_83_TAXED_TRANS_PPTERM_ASG_YTD','X_PRE_JUL_83_COMP_TRANS_PPTERM_ASG_YTD'
758                                 ,'X_ETP_DED_TRANS_NOT_PPTERM_ASG_YTD','X_INV_PAY_TRANS_NOT_PPTERM_ASG_YTD'
759                                 ,'X_POST_JUN_83_TAXED_TRANS_NOT_PPTERM_ASG_YTD','X_PRE_JUL_83_COMP_TRANS_NOT_PPTERM_ASG_YTD'
760                                 ,'X_ETP_DED_NOT_TRANS_PPTERM_ASG_YTD','X_INV_PAY_NOT_TRANS_PPTERM_ASG_YTD'
761                                 ,'X_POST_JUN_83_TAXED_NOT_TRANS_PPTERM_ASG_YTD','X_PRE_JUL_83_COMP_NOT_TRANS_PPTERM_ASG_YTD'
762                                 ,'X_ETP_DED_NOT_TRANS_NOT_PPTERM_ASG_YTD','X_INV_PAY_NOT_TRANS_NOT_PPTERM_ASG_YTD'
763                                 ,'X_POST_JUN_83_TAXED_NOT_TRANS_NOT_PPTERM_ASG_YTD','X_PRE_JUL_83_COMP_NOT_TRANS_NOT_PPTERM_ASG_YTD' )
764         OR fue.user_entity_name LIKE 'X_ETP%DATE%'
765         OR fue.user_entity_name LIKE 'X_DAYS%'
766         )
767 AND     fue.user_entity_name  NOT IN ('X_PAYMENT_SUMMARY_TYPE','X_PAYG_PAYMENT_SUMMARY_TYPE','X_ETP1_PAYMENT_SUMMARY_TYPE'
768                                  ,'X_ETP2_PAYMENT_SUMMARY_TYPE','X_ETP3_PAYMENT_SUMMARY_TYPE','X_ETP4_PAYMENT_SUMMARY_TYPE'
769                                  ,'X_LUMP_SUM_C_PAYMENTS_ASG_YTD','X_LUMP_SUM_C_DEDUCTIONS_ASG_YTD','X_INVALIDITY_PAYMENTS_ASG_YTD'
770                                  ,'X_PRE_JUL_83_COMPONENT_ASG_YTD','X_POST_JUN_83_UNTAXED_ASG_YTD','X_POST_JUN_83_TAXED_ASG_YTD')
771 AND     fue.legislation_code     = 'AU';
772 
773 l_procedure     VARCHAR2(100);
774 l_diff_count    NUMBER;
775 l_found         BOOLEAN;
776 l_item_type     VARCHAR2(20);
777 
778 
779 BEGIN
780 g_debug := hr_utility.debug_enabled;
781 
782 IF g_debug
783 THEN
784         l_procedure     := g_package||'.find_new_missing_items';
785         hr_utility.set_location('Entering Procedure     '||l_procedure,3500);
786 END IF;
787 
788 l_diff_count    := p_old_count - p_new_count;
789 
790 /*    Logic Used
791       (A) Fetch all items from Original Archive for Standard and ETP pages
792       (B) If this item is missing in New Archive PL/SQL table, set the Amended PS Flag accordingly
793       (C) We will look only for items of type PAYG,ETP_CMN,ETP1, ETP2,ETP3,ETP4 - Relevant Numeric and Date Types
794       (D) CMN - we are not interested if these items are missing
795       (E) ETP_CMN_BAL and AMEND items are archived for all Employees - so will be ignored
796 */
797 
798 FOR csr_rec IN get_archived_user_entities(p_archive_action_id)
799 LOOP
800         IF l_diff_count = 0
801         THEN
802                 exit;
803         END IF;
804 
805         l_found := FALSE;
806         IF (p_all_tab_new.COUNT > 0)
807         THEN
808                 FOR i IN p_all_tab_new.FIRST..p_all_tab_new.LAST
809                 LOOP
810                         IF p_all_tab_new(i).db_item_name = csr_rec.user_entity_name
811                         THEN
812                                 l_found := TRUE;
813                                 exit;
814                         END IF;
815                 END LOOP;
816         END IF;
817 
818         IF (l_found = FALSE)
819         THEN
820                 /* DB Item missing in New Archive.
821                    Set the Amend Flags */
822                 IF  g_debug
823                 THEN
824                         hr_utility.set_location('Missing Item Found     '||csr_rec.user_entity_name,3510);
825                 END IF;
826                 l_item_type := check_user_entity_type(csr_rec.user_entity_name);
827                 IF l_item_type = 'PAYG'
828                 THEN
829                        l_amend_types_new(1).db_item_value := 'A';
830                 ELSIF l_item_type = 'ETP_CMN'
831                 THEN
832                        l_amend_types_new(2).db_item_value := 'A';
833                        l_amend_types_new(3).db_item_value := 'A';
834                        l_amend_types_new(4).db_item_value := 'A';
835                        l_amend_types_new(5).db_item_value := 'A';
836                 ELSIF l_item_type = 'ETP1'
837                 THEN
838                        l_amend_types_new(2).db_item_value := 'A';
839                 ELSIF l_item_type = 'ETP2'
840                 THEN
841                        l_amend_types_new(3).db_item_value := 'A';
842                 ELSIF l_item_type = 'ETP3'
843                 THEN
844                        l_amend_types_new(4).db_item_value := 'A';
845 
846                 ELSIF l_item_type = 'ETP4'
847                 THEN
848                        l_amend_types_new(5).db_item_value := 'A';
849                 END IF;
850                 l_diff_count := l_diff_count - 1;
851         END IF;
852 END LOOP;
853 
854 IF g_debug
855 THEN
856         hr_utility.set_location('Payment Summary Flags  ',3520);
857     IF (l_amend_types_new.COUNT > 0 )
858     THEN
859         FOR i IN l_amend_types_new.FIRST..l_amend_types_new.LAST
860         LOOP
861                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);
862         END LOOP;
863         hr_utility.set_location('Leaving Procedure     '||l_procedure,3540);
864     END IF;
865 END IF;
866 
867 END find_new_missing_items;
868 
869 
870 
871 /*
872 --------------------------------------------------------------------
873 Name  : slot_items_build_archive_list
874 Type  : Procedure
875 Access: Private
876 Description:This private procedure does the actual comparison and
877             slotting in multiple PL/SQL tables - one for each datafile type.
878             It takes each item in Archive Pl/SQL table - finds the
879             data file record, compares with the Original Archive value
880             and sets the Amended PS Flag PL/sql table accordingly.
881   --------------------------------------------------------------------
882 */
883 
884 PROCEDURE  slot_items_build_archive_list
885         (p_archive_action_id    IN pay_assignment_actions.assignment_action_id%TYPE
886         ,p_all_tab_new          IN archive_db_tab)
887 IS
888 
889 CURSOR csr_get_value(c_user_entity_name VARCHAR2,
890                      c_assignment_action_id pay_assignment_actions.assignment_action_id%TYPE)
891 IS
892 SELECT  fai.value
893        ,fdi.data_type
894 FROM    ff_archive_items fai,
895         ff_user_entities fue,
896         ff_database_items fdi
897 WHERE fai.context1         = c_assignment_action_id
898 AND   fai.user_entity_id   = fue.user_entity_id
899 AND   fdi.user_entity_id   = fue.user_entity_id
900 AND   fue.user_entity_name = c_user_entity_name;
901 
902 i_index NUMBER;
903 l_procedure     VARCHAR2(80);
904 
905 l_item_type     VARCHAR2(20);
906 l_old_value     ff_archive_items.value%TYPE;
907 l_data_type     ff_database_items.data_type%TYPE;
908 
909 l_compare_flag  VARCHAR2(2);
910 l_etp_cmn_flag  VARCHAR2(2);
911 
912 
913 BEGIN
914 
915 g_debug := hr_utility.debug_enabled;
916 
917 IF g_debug
918 THEN
919         l_procedure     := g_package||'.slot_items_build_archive_list';
920         hr_utility.set_location('Entering Procedure     '||l_procedure,3700);
921 END IF;
922 
923 
924 l_etp_cmn_flag := 'O'; /* Initialize ETP Common Change Flag to O */
925 
926 IF ( p_all_tab_new.COUNT > 0 )
927 THEN
928     FOR i IN p_all_tab_new.FIRST..p_all_tab_new.LAST
929     LOOP
930 
931     l_compare_flag := 'Y';
932 
933     l_item_type     := check_user_entity_type(p_all_tab_new(i).db_item_name);
934 
935     IF l_item_type ='CMN'
936     THEN
937             /* Only Old Values */
938             OPEN csr_get_value(p_all_tab_new(i).db_item_name
939                               ,p_archive_action_id);
940             FETCH csr_get_value INTO l_old_value,l_data_type;
941             IF   csr_get_value%NOTFOUND
942             THEN
943                     l_old_value     := NULL;
944                     l_data_type     := NULL;
945             END IF;
946             CLOSE csr_get_value;
947 
948             i_index := NVL(l_cmn_tab_new.LAST,-1) + 1;
949 
950             l_cmn_tab_new(i_index).db_item_name   := p_all_tab_new(i).db_item_name;
951             l_cmn_tab_new(i_index).db_item_value  := l_old_value;
952 
953     ELSIF l_item_type ='PAYG'
954     THEN
955             IF l_amend_types_new(1).db_item_value  <> 'A'
956             THEN
957 
958                     OPEN csr_get_value(p_all_tab_new(i).db_item_name
959                                       ,p_archive_action_id);
960                     FETCH csr_get_value INTO l_old_value,l_data_type;
961                     IF   csr_get_value%NOTFOUND
962                     THEN
963                             l_amend_types_new(1).db_item_value := 'A';
964                             l_old_value     := NULL;
965                             l_data_type     := NULL;
966                     ELSE
967                             /* Compare Old and New Values
968                                Set the Amended Payment Summary Flag accordingly
969                             */
970 
971                             l_compare_flag := compare_user_entity_value
972                                                     (p_all_tab_new(i).db_item_name
973                                                     ,p_all_tab_new(i).db_item_value
974                                                     ,l_old_value
975                                                     ,l_data_type);
976 
977                             IF l_compare_flag = 'N'
978                             THEN
979                                     l_amend_types_new(1).db_item_value := 'A';
980                             END IF;
981                     END IF;
982                     CLOSE csr_get_value ;
983 
984                     i_index := NVL(l_payg_tab_new.LAST,-1) + 1;
985 
986                     l_payg_tab_new(i_index).db_item_name   := p_all_tab_new(i).db_item_name;
987                     l_payg_tab_new(i_index).db_item_value  := p_all_tab_new(i).db_item_value;
988 
989             ELSE
990                     /* Amended Payment Summary - No need to compare
991                     */
992 
993                     i_index := NVL(l_payg_tab_new.LAST,-1) + 1;
994 
995                     l_payg_tab_new(i_index).db_item_name   := p_all_tab_new(i).db_item_name;
996                     l_payg_tab_new(i_index).db_item_value  := p_all_tab_new(i).db_item_value;
997 
998             END IF;
999     ELSIF l_item_type IN ('ETP_CMN','ETP_CMN_BAL')
1000     THEN
1001             IF ( l_item_type = 'ETP_CMN' AND l_etp_cmn_flag  <> 'A')
1002             THEN
1003 
1004                     OPEN csr_get_value(p_all_tab_new(i).db_item_name
1005                                       ,p_archive_action_id);
1006                     FETCH csr_get_value INTO l_old_value,l_data_type;
1007                     IF   csr_get_value%NOTFOUND
1008                     THEN
1009                             l_etp_cmn_flag  := 'A';
1010                             l_amend_types_new(2).db_item_value := 'A';
1011                             l_amend_types_new(3).db_item_value := 'A';
1012                             l_amend_types_new(4).db_item_value := 'A';
1013                             l_amend_types_new(5).db_item_value := 'A';
1014                             l_old_value     := NULL;
1015                             l_data_type     := NULL;
1016                     ELSE
1017                             /* Compare Old and New Values
1018                                Set the Amended Payment Summary Flag accordingly
1019                             */
1020 
1021                             l_compare_flag := compare_user_entity_value
1022                                                     (p_all_tab_new(i).db_item_name
1023                                                     ,p_all_tab_new(i).db_item_value
1024                                                     ,l_old_value
1025                                                     ,l_data_type);
1026 
1027                             IF l_compare_flag = 'N'
1028                             THEN
1029                                 l_etp_cmn_flag  := 'A';
1030                                 l_amend_types_new(2).db_item_value := 'A';
1031                                 l_amend_types_new(3).db_item_value := 'A';
1032                                 l_amend_types_new(4).db_item_value := 'A';
1033                                 l_amend_types_new(5).db_item_value := 'A';
1034                             END IF;
1035                     END IF;
1036                     CLOSE csr_get_value ;
1037 
1038                     i_index := NVL(l_etp_cmn_tab_new.LAST,-1) + 1;
1039 
1040                     l_etp_cmn_tab_new(i_index).db_item_name   := p_all_tab_new(i).db_item_name;
1041                     l_etp_cmn_tab_new(i_index).db_item_value  := p_all_tab_new(i).db_item_value;
1042 
1043             ELSE
1044                     /* Amended Payment Summary - No need to compare
1045                        ETP Balances - will be adjusted in ETP1-4 Sections. Always copy the new Value
1046                     */
1047 
1048                     i_index := NVL(l_etp_cmn_tab_new.LAST,-1) + 1;
1049 
1050                     l_etp_cmn_tab_new(i_index).db_item_name   := p_all_tab_new(i).db_item_name;
1051                     l_etp_cmn_tab_new(i_index).db_item_value  := p_all_tab_new(i).db_item_value;
1052 
1053             END IF;
1054     ELSIF l_item_type ='ETP1'
1055     THEN
1056             IF l_amend_types_new(2).db_item_value  <> 'A'
1057             THEN
1058 
1059                     OPEN csr_get_value(p_all_tab_new(i).db_item_name
1060                                       ,p_archive_action_id);
1061                     FETCH csr_get_value INTO l_old_value,l_data_type;
1062                     IF   csr_get_value%NOTFOUND
1063                     THEN
1064                             l_amend_types_new(2).db_item_value := 'A';
1065                             l_old_value     := NULL;
1066                             l_data_type     := NULL;
1067                     ELSE
1068                             /* Compare Old and New Values
1069                                Set the Amended Payment Summary Flag accordingly
1070                             */
1071 
1072                             l_compare_flag := compare_user_entity_value
1073                                                     (p_all_tab_new(i).db_item_name
1074                                                     ,p_all_tab_new(i).db_item_value
1075                                                     ,l_old_value
1076                                                     ,l_data_type);
1077 
1078                             IF l_compare_flag = 'N'
1079                             THEN
1080                             l_amend_types_new(2).db_item_value := 'A';
1081                             END IF;
1082                     END IF;
1083                     CLOSE csr_get_value ;
1084 
1085                     i_index := NVL(l_etp_1_tab_new.LAST,-1) + 1;
1086 
1087                     l_etp_1_tab_new(i_index).db_item_name   := p_all_tab_new(i).db_item_name;
1088                     l_etp_1_tab_new(i_index).db_item_value  := p_all_tab_new(i).db_item_value;
1089 
1090             ELSE
1091                     /* Amended Payment Summary - No need to compare
1092                     */
1093 
1094                     i_index := NVL(l_etp_1_tab_new.LAST,-1) + 1;
1095 
1096                     l_etp_1_tab_new(i_index).db_item_name   := p_all_tab_new(i).db_item_name;
1097                     l_etp_1_tab_new(i_index).db_item_value  := p_all_tab_new(i).db_item_value;
1098             END IF;
1099 
1100     ELSIF l_item_type ='ETP2'
1101     THEN
1102             IF l_amend_types_new(3).db_item_value  <> 'A'
1103             THEN
1104 
1105                     OPEN csr_get_value(p_all_tab_new(i).db_item_name
1106                                       ,p_archive_action_id);
1107                     FETCH csr_get_value INTO l_old_value,l_data_type;
1108                     IF   csr_get_value%NOTFOUND
1109                     THEN
1110                             l_amend_types_new(3).db_item_value := 'A';
1111                             l_old_value     := NULL;
1112                             l_data_type     := NULL;
1113                     ELSE
1114                             /* Compare Old and New Values
1115                                Set the Amended Payment Summary Flag accordingly
1116                             */
1117 
1118                             l_compare_flag := compare_user_entity_value
1119                                                     (p_all_tab_new(i).db_item_name
1120                                                     ,p_all_tab_new(i).db_item_value
1121                                                     ,l_old_value
1122                                                     ,l_data_type);
1123 
1124                             IF l_compare_flag = 'N'
1125                             THEN
1126                             l_amend_types_new(3).db_item_value := 'A';
1127                             END IF;
1128                     END IF;
1129                     CLOSE csr_get_value ;
1130 
1131                     i_index := NVL(l_etp_2_tab_new.LAST,-1) + 1;
1132 
1133                     l_etp_2_tab_new(i_index).db_item_name   := p_all_tab_new(i).db_item_name;
1134                     l_etp_2_tab_new(i_index).db_item_value  := p_all_tab_new(i).db_item_value;
1135 
1136             ELSE
1137                     /* Amended Payment Summary - No need to compare
1138                     */
1139 
1140                     i_index := NVL(l_etp_2_tab_new.LAST,-1) + 1;
1141 
1142                     l_etp_2_tab_new(i_index).db_item_name   := p_all_tab_new(i).db_item_name;
1143                     l_etp_2_tab_new(i_index).db_item_value  := p_all_tab_new(i).db_item_value;
1144             END IF;
1145 
1146     ELSIF l_item_type ='ETP3'
1147     THEN
1148             IF l_amend_types_new(4).db_item_value  <> 'A'
1149             THEN
1150 
1151                     OPEN csr_get_value(p_all_tab_new(i).db_item_name
1152                                       ,p_archive_action_id);
1153                     FETCH csr_get_value INTO l_old_value,l_data_type;
1154                     IF   csr_get_value%NOTFOUND
1155                     THEN
1156                             l_amend_types_new(4).db_item_value := 'A';
1157                             l_old_value     := NULL;
1158                             l_data_type     := NULL;
1159                     ELSE
1160                             /* Compare Old and New Values
1161                                Set the Amended Payment Summary Flag accordingly
1162                             */
1163 
1164                             l_compare_flag := compare_user_entity_value
1165                                                     (p_all_tab_new(i).db_item_name
1166                                                     ,p_all_tab_new(i).db_item_value
1167                                                     ,l_old_value
1168                                                     ,l_data_type);
1169 
1170                             IF l_compare_flag = 'N'
1171                             THEN
1172                             l_amend_types_new(4).db_item_value := 'A';
1173                             END IF;
1174                     END IF;
1175                     CLOSE csr_get_value ;
1176 
1177                     i_index := NVL(l_etp_3_tab_new.LAST,-1) + 1;
1178 
1179                     l_etp_3_tab_new(i_index).db_item_name   := p_all_tab_new(i).db_item_name;
1180                     l_etp_3_tab_new(i_index).db_item_value  := p_all_tab_new(i).db_item_value;
1181 
1182             ELSE
1183                     /* Amended Payment Summary - No need to compare
1184                     */
1185 
1186                     i_index := NVL(l_etp_3_tab_new.LAST,-1) + 1;
1187 
1188                     l_etp_3_tab_new(i_index).db_item_name   := p_all_tab_new(i).db_item_name;
1189                     l_etp_3_tab_new(i_index).db_item_value  := p_all_tab_new(i).db_item_value;
1190             END IF;
1191 
1192     ELSIF l_item_type ='ETP4'
1193     THEN
1194             IF l_amend_types_new(5).db_item_value  <> 'A'
1195             THEN
1196 
1197                     OPEN csr_get_value(p_all_tab_new(i).db_item_name
1198                                       ,p_archive_action_id);
1199                     FETCH csr_get_value INTO l_old_value,l_data_type;
1200                     IF   csr_get_value%NOTFOUND
1201                     THEN
1202                             l_amend_types_new(5).db_item_value := 'A';
1203                             l_old_value     := NULL;
1204                             l_data_type     := NULL;
1205                     ELSE
1206                             /* Compare Old and New Values
1207                                Set the Amended Payment Summary Flag accordingly
1208                             */
1209 
1210                             l_compare_flag := compare_user_entity_value
1211                                                     (p_all_tab_new(i).db_item_name
1212                                                     ,p_all_tab_new(i).db_item_value
1213                                                     ,l_old_value
1214                                                     ,l_data_type);
1215 
1216                             IF l_compare_flag = 'N'
1217                             THEN
1218                             l_amend_types_new(5).db_item_value := 'A';
1219                             END IF;
1220                     END IF;
1221                     CLOSE csr_get_value ;
1222 
1223                     i_index := NVL(l_etp_4_tab_new.LAST,-1) + 1;
1224 
1225                     l_etp_4_tab_new(i_index).db_item_name   := p_all_tab_new(i).db_item_name;
1226                     l_etp_4_tab_new(i_index).db_item_value  := p_all_tab_new(i).db_item_value;
1227 
1228             ELSE
1229                     /* Amended Payment Summary - No need to compare
1230                     */
1231                     i_index := NVL(l_etp_4_tab_new.LAST,-1) + 1;
1232 
1233                     l_etp_4_tab_new(i_index).db_item_name   := p_all_tab_new(i).db_item_name;
1234                     l_etp_4_tab_new(i_index).db_item_value  := p_all_tab_new(i).db_item_value;
1235             END IF;
1236     END IF;
1237 
1238     END LOOP;
1239 END IF;
1240 
1241 /* Reset the Value of DB Item X_PAYMENT_SUMMARY_TYPE if No individual record has changed
1242 */
1243 
1244 IF   ( l_amend_types_new(1).db_item_value ='O'
1245         AND l_amend_types_new(2).db_item_value ='O'
1246         AND l_amend_types_new(3).db_item_value ='O'
1247         AND l_amend_types_new(4).db_item_value ='O'
1248         AND l_amend_types_new(5).db_item_value ='O')
1249 THEN
1250         l_amend_types_new(0).db_item_value :='O';
1251 END IF;
1252 
1253 IF g_debug
1254 THEN
1255     IF ( l_cmn_tab_new.COUNT > 0)
1256     THEN
1257         hr_utility.set_location('              COMMON ITEMS                            ',3710);
1258 
1259         FOR i IN l_cmn_tab_new.FIRST..l_cmn_tab_new.LAST
1260         LOOP
1261                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);
1262         END LOOP;
1263     END IF;
1264 
1265     IF ( l_payg_tab_new.COUNT > 0)
1266     THEN
1267         hr_utility.set_location('              STANDARD ITEMS                            ',3720);
1268 
1269         FOR i IN l_payg_tab_new.FIRST..l_payg_tab_new.LAST
1270         LOOP
1271                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);
1272         END LOOP;
1273     END IF;
1274 
1275 
1276     IF ( l_etp_cmn_tab_new.COUNT > 0)
1277     THEN
1278         hr_utility.set_location('              ETP COMMON ITEMS                            ',3730);
1279 
1280         FOR i IN l_etp_cmn_tab_new.FIRST..l_etp_cmn_tab_new.LAST
1281         LOOP
1282                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);
1283         END LOOP;
1284     END IF;
1285 
1286     IF (l_etp_1_tab_new.COUNT > 0)
1287     THEN
1288         hr_utility.set_location('              ETP 1 ITEMS                            ',3740);
1289 
1290         FOR i IN l_etp_1_tab_new.FIRST..l_etp_1_tab_new.LAST
1291         LOOP
1292                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);
1293         END LOOP;
1294     END IF;
1295 
1296     IF (l_etp_2_tab_new.COUNT > 0)
1297     THEN
1298         hr_utility.set_location('              ETP 2 ITEMS                            ',3750);
1299 
1300         FOR i IN l_etp_2_tab_new.FIRST..l_etp_2_tab_new.LAST
1301         LOOP
1302                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);
1303         END LOOP;
1304     END IF;
1305 
1306     IF (l_etp_3_tab_new.COUNT > 0)
1307     THEN
1308         hr_utility.set_location('              ETP 3 ITEMS                            ',3760);
1309 
1310         FOR i IN l_etp_3_tab_new.FIRST..l_etp_3_tab_new.LAST
1311         LOOP
1312                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);
1313         END LOOP;
1314     END IF;
1315 
1316     IF ( l_etp_4_tab_new.COUNT > 0)
1317     THEN
1318         hr_utility.set_location('              ETP 4 ITEMS                            ',3770);
1319 
1320         FOR i IN l_etp_4_tab_new.FIRST..l_etp_4_tab_new.LAST
1321         LOOP
1322                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);
1323         END LOOP;
1324     END IF;
1325 
1326 
1327     IF (l_amend_types_new.COUNT > 0)
1328     THEN
1329         hr_utility.set_location('              AMEND TYPE ITEMS                            ',3780);
1330 
1331         FOR i IN l_amend_types_new.FIRST..l_amend_types_new.LAST
1332         LOOP
1333                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,' '),3780);
1334         END LOOP;
1335     END IF;
1336 
1337         hr_utility.set_location('Leaving Procedure     '||l_procedure,3800);
1338 END IF;
1339 
1340 
1341 END slot_items_build_archive_list;
1342 
1343 
1344 /*
1345 --------------------------------------------------------------------
1346 Name  : archive_db_items_tab
1347 Type  : Procedure
1348 Access: Private
1349 Description:This procedure archives the contents of the
1350             user entity value PL/SQL table
1351 --------------------------------------------------------------------
1352 */
1353 
1354 PROCEDURE archive_db_items_tab(
1355          p_assignment_action_id  IN pay_assignment_actions.assignment_action_id%TYPE
1356         ,p_db_item_tab           IN archive_db_tab
1357         )
1358 IS
1359 
1360 CURSOR  get_user_entity_id(c_user_entity_name IN VARCHAR2)
1361 IS
1362 SELECT fue.user_entity_id
1363       ,dbi.data_type
1364 FROM  ff_user_entities  fue
1365      ,ff_database_items dbi
1366 WHERE user_entity_name     = c_user_entity_name
1367 AND   fue.user_entity_id   = dbi.user_entity_id
1368 AND   fue.legislation_code = 'AU';
1369 
1370 l_procedure             VARCHAR2(80);
1371 l_user_entity_id        ff_user_entities.user_entity_id%TYPE;
1372 l_archive_item_id       ff_archive_items.archive_item_id%TYPE;
1373 l_object_version_number ff_archive_items.object_version_number%type;
1374 l_some_warning          boolean;
1375 
1376 e_ue_missing            EXCEPTION;
1377 
1378 BEGIN
1379 
1380 g_debug := hr_utility.debug_enabled;
1381 IF g_debug
1382 THEN
1383         l_procedure     := g_package||'.archive_db_items_tab';
1384         hr_utility.set_location('Entering Procedure     '||l_procedure,4200);
1385 END IF;
1386 
1387 IF (p_db_item_tab.COUNT > 0)
1388 THEN
1389         FOR i IN p_db_item_tab.FIRST..p_db_item_tab.LAST
1390         LOOP
1391                 IF g_debug
1392                 THEN
1393                         hr_utility.set_location('p_db_item_tab.name     '||p_db_item_tab(i).db_item_name,4210);
1394                         hr_utility.set_location('p_db_item_tab.value    '||p_db_item_tab(i).db_item_value,4220);
1395                 END IF;
1396 
1397                 FOR csr_ue_rec IN get_user_entity_id(p_db_item_tab(i).db_item_name)
1398                 LOOP
1399                         l_archive_item_id       := NULL;
1400                         l_object_version_number := NULL;
1401                         l_some_warning          := NULL;
1402 
1403                         ff_archive_api.create_archive_item
1404                          (p_validate              => false
1405                          ,p_archive_item_id       => l_archive_item_id
1406                          ,p_user_entity_id        => csr_ue_rec.user_entity_id
1407                          ,p_archive_value         => p_db_item_tab(i).db_item_value
1408                          ,p_archive_type          => 'AAP'
1409                          ,p_action_id             => p_assignment_action_id
1410                          ,p_legislation_code      => 'AU'
1411                          ,p_object_version_number => l_object_version_number
1412                          ,p_context_name1         => 'ASSIGNMENT_ACTION_ID'
1413                          ,p_context1              => p_assignment_action_id
1414                          ,p_some_warning          => l_some_warning);
1415 
1416                         IF g_debug
1417                         THEN
1418                                 hr_utility.set_location('l_archive_item_id      '||l_archive_item_id,4230);
1419                         END IF;
1420                 END LOOP;
1421 
1422         END LOOP;
1423 END IF;
1424 IF g_debug
1425 THEN
1426         hr_utility.set_location('Leaving Procedure      '||l_procedure,4250);
1427 END IF;
1428 
1429 END archive_db_items_tab;
1430 
1431 
1432 /*
1433 --------------------------------------------------------------------
1434 Name  : modify_and_archive_code
1435 Type  : Procedure
1436 Access: Public
1437 Description:This procedure is called from Archive code of Payment Summary
1438             with a PL/SQL table holding all DB items and values
1439             This procedure slots the DB items according to record
1440             in datafile and populates different PL/SQL tables.
1441             Data is archived in this procedure based on Amend PS
1442             flags.
1443   --------------------------------------------------------------------
1444 */
1445 PROCEDURE modify_and_archive_code
1446         (p_assignment_action_id  IN pay_assignment_actions.assignment_action_id%TYPE
1447         ,p_effective_date        IN DATE
1448         ,p_all_tab_new           IN archive_db_tab)
1449 IS
1450 
1451 l_procedure     VARCHAR2(80);
1452 
1453 CURSOR get_orig_archive_id
1454         (c_assignmenr_id pay_assignment_actions.assignment_id%TYPE
1455         ,c_fin_year      VARCHAR2
1456         ,c_tax_unit_id  pay_assignment_actions.tax_unit_id%TYPE
1457         )
1458 IS
1459 SELECT selfplock.locked_action_id
1460 FROM     pay_assignment_actions mpaa
1461         ,pay_payroll_actions    mppa
1462         ,pay_action_interlocks  mplock
1463         ,pay_action_interlocks  selfplock
1464 WHERE   mpaa.assignment_id      = c_assignmenr_id
1465 AND   mpaa.payroll_action_id    = mppa.payroll_action_id
1466 AND   mppa.report_type          = 'AU_PS_DATA_FILE'
1467 AND   mppa.report_qualifier     = 'AU'
1468 AND   mppa.report_category      = 'REPORT'
1469 AND   pay_core_utils.get_parameter('FINANCIAL_YEAR',mppa.legislative_parameters) = c_fin_year
1470 AND   pay_core_utils.get_parameter('REGISTERED_EMPLOYER',mppa.legislative_parameters) = c_tax_unit_id
1471 AND   mplock.locking_action_id  = mpaa.assignment_action_id
1472 AND   mplock.locked_action_id   = selfplock.locking_action_id;
1473 
1474 
1475 CURSOR c_action(c_assignment_action_id NUMBER) IS
1476 SELECT pay_core_utils.get_parameter('BUSINESS_GROUP_ID',ppa.legislative_parameters)
1477 ,      pay_core_utils.get_parameter('REGISTERED_EMPLOYER',ppa.legislative_parameters)
1478 ,      pay_core_utils.get_parameter('EMPLOYEE_TYPE',ppa.legislative_parameters)
1479 ,      ppa.payroll_action_id
1480 ,      paa.assignment_id
1481 ,      to_date('01-07-'|| substr(pay_core_utils.get_parameter('FINANCIAL_YEAR',ppa.legislative_parameters),1,4),'DD-MM-YYYY')
1482 ,      to_date('30-06-'|| substr(pay_core_utils.get_parameter('FINANCIAL_YEAR',ppa.legislative_parameters),6,4),'DD-MM-YYYY')
1483 ,      pay_core_utils.get_parameter('LST_YR_TERM',ppa.legislative_parameters)
1484 ,      pay_core_utils.get_parameter('FINANCIAL_YEAR',ppa.legislative_parameters)
1485 FROM   pay_assignment_actions     paa
1486 ,      pay_payroll_actions        ppa
1487 WHERE  paa.assignment_action_id   = c_assignment_action_id
1488 AND    ppa.payroll_action_id      = paa.payroll_action_id ;
1489 
1490 CURSOR get_context_id(c_context_name ff_contexts.context_name%TYPE)
1491 IS
1492 SELECT fc.context_id
1493 FROM   ff_contexts fc
1494 WHERE  fc.context_name = c_context_name;
1495 
1496 CURSOR get_archive_item_count(c_archive_action_id pay_assignment_actions.assignment_action_id%TYPE
1497                              ,c_context_id        ff_contexts.context_id%TYPE)
1498 IS
1499 SELECT COUNT(*)
1500 FROM    ff_archive_items fai,
1501         ff_user_entities fue,
1502         ff_archive_item_contexts faic
1503 WHERE fai.context1 = c_archive_action_id
1504 AND   fue.user_entity_id = fai.user_entity_id
1505 AND   fai.archive_item_id = faic.archive_item_id
1506 AND   faic.context_id = c_context_id
1507 AND   fue.user_entity_name NOT IN ('X_PAYMENT_SUMMARY_TYPE'
1508                                   ,'X_PAYG_PAYMENT_SUMMARY_TYPE'
1509                                   ,'X_ETP1_PAYMENT_SUMMARY_TYPE'
1510                                   ,'X_ETP2_PAYMENT_SUMMARY_TYPE'
1511                                   ,'X_ETP3_PAYMENT_SUMMARY_TYPE'
1512                                   ,'X_ETP4_PAYMENT_SUMMARY_TYPE');
1513 
1514 l_assignment_id               pay_assignment_actions.assignment_id%TYPE;
1515 l_business_group_id           pay_payroll_actions.business_group_id%TYPE ;
1516 l_registered_employer         hr_organization_units.organization_id%TYPE;
1517 l_payroll_action_id           pay_payroll_actions.payroll_action_id%TYPE ;
1518 l_year_start                  pay_payroll_Actions.effective_date%TYPE;
1519 l_year_end                    pay_payroll_actions.effective_date%TYPE;
1520 l_employee_type               per_all_people_f.current_Employee_Flag%TYPE;
1521 l_lst_yr_term                 varchar2(10);
1522 l_fin_year                    VARCHAR2(20);
1523 l_archive_action_id           pay_assignment_actions.assignment_action_id%TYPE;
1524 
1525 l_eit_value                   VARCHAR2(10);
1526 
1527 l_new_count                   NUMBER;
1528 l_old_count                   NUMBER;
1529 l_context_id                  ff_contexts.context_id%TYPE;
1530 
1531 BEGIN
1532 g_debug := hr_utility.debug_enabled;
1533 
1534 IF g_debug
1535 THEN
1536         l_procedure  := g_package||'.modify_and_archive_code';
1537         hr_utility.set_location('Entering Procedure  '||l_procedure, 3000);
1538 END IF;
1539 
1540 /* Print All the DB Items Values got from Archive */
1541 IF g_debug
1542 THEN
1543     IF (p_all_tab_new.COUNT > 0)
1544     THEN
1545         FOR i IN p_all_tab_new.FIRST..p_all_tab_new.LAST
1546         LOOP
1547             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);
1548         END LOOP;
1549     END IF;
1550 END IF;
1551 
1552 OPEN c_action(p_assignment_action_id);
1553 FETCH c_action INTO  l_business_group_id
1554                     ,l_registered_employer
1555                     ,l_employee_type
1556                     ,l_payroll_action_id
1557                     ,l_assignment_id
1558                     ,l_year_start
1559                     ,l_year_end
1560                     ,l_lst_yr_term
1561                     ,l_fin_year;
1562 CLOSE c_action;
1563 
1564 
1565 OPEN get_orig_archive_id(l_assignment_id
1566                         ,l_fin_year
1567                         ,l_registered_employer);
1568 FETCH get_orig_archive_id INTO l_archive_action_id;
1569 CLOSE get_orig_archive_id;
1570 
1571 IF g_debug
1572 THEN
1573     hr_utility.set_location('l_business_group_id    '||l_business_group_id,3020);
1574     hr_utility.set_location('l_registered_employer  '||l_registered_employer,3020);
1575     hr_utility.set_location('l_employee_type        '||l_employee_type,3020);
1576     hr_utility.set_location('l_payroll_action_id    '||l_payroll_action_id,3020);
1577     hr_utility.set_location('l_assignment_id        '||l_assignment_id,3020);
1578     hr_utility.set_location('l_year_start           '||l_year_start,3020);
1579     hr_utility.set_location('l_year_end             '||l_year_end,3020);
1580     hr_utility.set_location('l_lst_yr_term          '||l_lst_yr_term,3020);
1581     hr_utility.set_location('l_fin_year             '||l_fin_year,3020);
1582     hr_utility.set_location('l_archive_action_id    '||l_archive_action_id,3020);
1583 END IF;
1584 
1585 
1586 /* Now you have all the archive items - slot them according to Datafile record
1587    Initialize the PL/SQL tables to NULL
1588 */
1589 
1590 l_cmn_tab_new.DELETE;
1591 l_payg_tab_new.DELETE;
1592 l_etp_cmn_tab_new.DELETE;
1593 l_etp_1_tab_new.DELETE;
1594 l_etp_2_tab_new.DELETE;
1595 l_etp_3_tab_new.DELETE;
1596 l_etp_4_tab_new.DELETE;
1597 l_amend_types_new.DELETE;
1598 
1599 /* Initialize all Amended Payment Summary Flags,
1600           Index     Meaning    Value
1601             0.      Common      A
1602             1       Standard    O
1603             2       ETP1        O
1604             3       ETP2        O
1605             4       ETP3        O
1606             5       ETP4        O
1607 */
1608 
1609     l_amend_types_new(0).db_item_name       := 'X_PAYMENT_SUMMARY_TYPE';
1610     l_amend_types_new(0).db_item_value      := 'A';
1611 
1612     l_amend_types_new(1).db_item_name       := 'X_PAYG_PAYMENT_SUMMARY_TYPE';
1613     l_amend_types_new(1).db_item_value      := 'O';
1614 
1615     l_amend_types_new(2).db_item_name       := 'X_ETP1_PAYMENT_SUMMARY_TYPE';
1616     l_amend_types_new(2).db_item_value      := 'O';
1617 
1618     l_amend_types_new(3).db_item_name       := 'X_ETP2_PAYMENT_SUMMARY_TYPE';
1619     l_amend_types_new(3).db_item_value      := 'O';
1620 
1621     l_amend_types_new(4).db_item_name       := 'X_ETP3_PAYMENT_SUMMARY_TYPE';
1622     l_amend_types_new(4).db_item_value      := 'O';
1623 
1624     l_amend_types_new(5).db_item_name       := 'X_ETP4_PAYMENT_SUMMARY_TYPE';
1625     l_amend_types_new(5).db_item_value      := 'O';
1626 
1627 
1628 /*      Check count and set flags if some items are missing in New Run
1629 */
1630 
1631     l_new_count     := NVL(p_all_tab_new.LAST,-1) + 1;
1632 
1633     OPEN get_context_id('ASSIGNMENT_ACTION_ID');
1634     FETCH get_context_id INTO l_context_id;
1635     CLOSE get_context_id;
1636 
1637     OPEN  get_archive_item_count(l_archive_action_id,l_context_id);
1638     FETCH get_archive_item_count INTO l_old_count;
1639     CLOSE get_archive_item_count ;
1640 
1641     IF g_debug THEN
1642         hr_utility.set_location('Old Archive Count      '||l_old_count,3030);
1643         hr_utility.set_location('New Archive Count      '||l_new_count,3030);
1644     END IF;
1645 
1646     IF l_old_count > l_new_count
1647     THEN
1648     /* Some Items Missing from New Archive - Find and Set the Amend Flags appropriately
1649     */
1650         find_new_missing_items(l_archive_action_id
1651                               ,l_old_count
1652                               ,p_all_tab_new
1653                               ,l_new_count);
1654     END IF;
1655 
1656     slot_items_build_archive_list(l_archive_action_id
1657                                  ,p_all_tab_new);
1658 
1659     /*  1. Archive all Common Information - Old
1660         2. Archive all Standard Information - Old/New based on EIT
1661         3. Archive all ETP Information - Old/New based on EIT
1662         4. Archive Amended Payment Summary Flags
1663     */
1664 
1665         archive_db_items_tab
1666                 (p_assignment_action_id  => p_assignment_action_id
1667                 ,p_db_item_tab           => l_cmn_tab_new);
1668 
1669         archive_db_items_tab
1670                 (p_assignment_action_id  => p_assignment_action_id
1671                 ,p_db_item_tab           => l_payg_tab_new);
1672 
1673         archive_db_items_tab
1674                 (p_assignment_action_id  => p_assignment_action_id
1675                 ,p_db_item_tab           => l_etp_cmn_tab_new);
1676 
1677         archive_db_items_tab
1678                 (p_assignment_action_id  => p_assignment_action_id
1679                 ,p_db_item_tab           => l_etp_1_tab_new);
1680 
1681         archive_db_items_tab
1682                 (p_assignment_action_id  => p_assignment_action_id
1683                 ,p_db_item_tab           => l_etp_2_tab_new);
1684 
1685         archive_db_items_tab
1686                 (p_assignment_action_id  => p_assignment_action_id
1687                 ,p_db_item_tab           => l_etp_3_tab_new);
1688 
1689         archive_db_items_tab
1690                 (p_assignment_action_id  => p_assignment_action_id
1691                 ,p_db_item_tab           => l_etp_4_tab_new);
1692 
1693         archive_db_items_tab
1694                 (p_assignment_action_id  => p_assignment_action_id
1695                 ,p_db_item_tab           => l_amend_types_new);
1696 
1697 IF g_debug
1698 THEN
1699         hr_utility.set_location('Leaving Procedure  '||l_procedure, 3000);
1700 END IF;
1701 
1702 END modify_and_archive_code;
1703 
1704 PROCEDURE spawn_data_file
1705         (p_payroll_action_id IN pay_payroll_actions.payroll_action_id%TYPE)
1706 IS
1707 
1708 l_payroll_action_id    pay_payroll_actions.payroll_action_id%TYPE;
1709 l_business_group_id     NUMBER;
1710 l_start_date            DATE;
1711 l_end_date              DATE;
1712 l_effective_date        DATE;
1713 l_legal_employer        NUMBER;
1714 l_financial_year_code   VARCHAR2(10);
1715 l_test_efile            VARCHAR2(10);
1716 l_financial_year        VARCHAR2(10);
1717 l_legislative_param     VARCHAR2(200);
1718 l_procedure             VARCHAR2(80);
1719 ps_request_id           NUMBER;
1720   --------------------------------------------------------------------+
1721   -- Cursor      : csr_params
1722   -- Description : Fetches User Parameters from Legislative_paramters
1723   --               column.
1724   --------------------------------------------------------------------+
1725 
1726 CURSOR csr_magtape_params(c_payroll_action_id  pay_payroll_actions.payroll_action_id%TYPE)
1727 IS
1728 SELECT  pay_core_utils.get_parameter('TEST_EFILE',legislative_parameters)        TEST_EFILE,
1729         pay_core_utils.get_parameter('BUSINESS_GROUP_ID',legislative_parameters)        BUSINESS_GROUP_ID,
1730         pay_core_utils.get_parameter('FINANCIAL_YEAR',legislative_parameters)  FINANCIAL_YEAR,
1731         pay_core_utils.get_parameter('REGISTERED_EMPLOYER',legislative_parameters)    REGISTERED_EMPLOYER,
1732         to_date(pay_core_utils.get_parameter('START_DATE',legislative_parameters),'YYYY/MM/DD') start_date,
1733         to_date(pay_core_utils.get_parameter('END_DATE',legislative_parameters),'YYYY/MM/DD')   end_date,
1734         to_date(pay_core_utils.get_parameter('EFFECTIVE_DATE',legislative_parameters),'YYYY/MM/DD')   EFFECTIVE_DATE
1735 FROM    pay_payroll_actions ppa
1736 WHERE   ppa.payroll_action_id  =  c_payroll_action_id;
1737 
1738 
1739 CURSOR csr_lookup_code (c_financial_year VARCHAR2)
1740 IS
1741 SELECT LOOKUP_CODE
1742 FROM HR_LOOKUPS
1743 WHERE lookup_type   = 'AU_PS_FINANCIAL_YEAR'
1744 AND enabled_flag    = 'Y'
1745 AND meaning         = c_financial_year;
1746 
1747 BEGIN
1748 
1749 g_debug := hr_utility.debug_enabled;
1750 
1751 IF g_debug
1752 THEN
1753     l_procedure     := g_package||'.spawn_data_file';
1754     hr_utility.set_location('Entering package       '||l_procedure,4500);
1755 END IF;
1756 
1757 ps_request_id :=-1;
1758 l_TEST_EFILE :='N';
1759 
1760 OPEN  csr_magtape_params(p_payroll_action_id);
1761 FETCH csr_magtape_params
1762 INTO    l_test_efile,
1763         l_business_group_id,
1764         l_financial_year,
1765         l_legal_employer,
1766         l_start_date,
1767         l_end_date,
1768         l_effective_date;
1769 CLOSE csr_magtape_params;
1770 
1771 IF l_TEST_EFILE = 'Y'
1772 THEN
1773        OPEN  csr_lookup_code(l_financial_year);
1774        FETCH csr_lookup_code
1775        INTO  l_financial_year_code;
1776        CLOSE csr_lookup_code;
1777 
1778     l_legislative_param := 'BUSINESS_GROUP_ID='      || l_business_group_id         ||' '
1779                 || 'FINANCIAL_YEAR='         || l_FINANCIAL_YEAR            ||' '
1780                 || 'REGISTERED_EMPLOYER='    || l_legal_employer            ||' '
1781                 || 'IS_TESTING='             || 'Y'                         ||' '
1782                 || 'ARCHIVE_PAYROLL_ACTION=' || to_char(p_payroll_action_id)||' '
1783                 || 'END_DATE='               || to_char(l_end_date,'YYYY/MM/DD HH:MI:SS')||' '
1784                 || 'PAYMENT_SUMMARY_TYPE=A';
1785 
1786      ps_request_id := fnd_request.submit_request
1787      ('PAY',
1788       'PYAUPSDF',
1789       null,
1790       null,
1791       false,
1792       'ARCHIVE',
1793       'AU_PS_DATA_FILE_VAL',                 -- Report_format of magtape process
1794       'AU',
1795       to_char(l_start_date,'YYYY/MM/DD HH:MI:SS'),
1796       to_char(l_EFFECTIVE_DATE,'YYYY/MM/DD HH:MI:SS'),
1797       'REPORT',
1798       l_business_group_id,
1799       null,
1800       null,
1801       l_legal_employer,
1802       l_FINANCIAL_YEAR_code,
1803       'END_DATE='||to_char(l_end_date,'YYYY/MM/DD HH:MI:SS'),
1804       'Y',                                   -- IS_TESTING Parameter
1805       'A',
1806       'AU_PAY_SUMM_AMEND',
1807       to_number(p_payroll_action_id),        -- Archive_PAyroll_Action
1808       l_legislative_param                    -- Legislative parameters
1809      );
1810 
1811 END IF;
1812 
1813 IF g_debug
1814 THEN
1815     hr_utility.set_location('Leaving procedure          '||l_procedure,4540);
1816 END IF;
1817 
1818 END spawn_data_file;
1819 
1820 END pay_au_payment_summary_amend;