[Home] [Help]
PACKAGE BODY: APPS.PAY_US_ARCHIVE_RULES
Source
1 PACKAGE BODY PAY_US_ARCHIVE_RULES AS
2 /* $Header: payusarcyema.pkb 120.1 2007/01/23 09:28:33 ydevi noship $ */
3 /******************************************************************************
4
5 ******************************************************************
6 * *
7 * Copyright (C) 1996 Oracle Corporation. *
8 * All rights reserved. *
9 * *
10 * This material has been provided pursuant to an agreement *
11 * containing restrictions on its use. The material is also *
12 * protected by copyright law. No part of this material may *
13 * be copied or distributed, transmitted or transcribed, in *
14 * any form or by any means, electronic, mechanical, magnetic, *
15 * manual, or otherwise, or disCLOSEd to third parties without *
16 * the express written permission of Oracle Corporation, *
17 * 500 Oracle Parkway, Redwood City, CA, 94065. *
18 * *
19 ******************************************************************
20 --
21 Name : PAY_US_ARCHIVE_RULES
22 Description : This package contains the rules for archiving
23 for missing assignment report specific to US legislation
24 --
25 Change List
26 -----------
27 Date Name Vers Bug Description
28 ----------- ---------- ----- -------- ----------------------------------
29 24-Oct-2005 rdhingra 115.0 Created
30 19-JAN-2007 ydevi 115.1 4886285 adding the condition for
31 GRE in range_cursor
32 and action creation
33 adding the procedure archive_code.
34 added procedure archive_code
35
36 ******************************************************************************/
37 gv_package_name VARCHAR2(50) := 'pay_us_archive_rules.';
38
39
40 ----------------------------------- range_cursor ------------------------------
41 procedure range_cursor (pactid in number, sqlstr out nocopy varchar2) is
42
43 l_proc VARCHAR2(240);
44 l_gre_id VARCHAR2(50);
45 lv_legislative_param VARCHAR2(240);
46 begin
47
48 l_proc := gv_package_name||'range_cursor';
49 hr_utility.set_location(l_proc, 10);
50 hr_utility.trace('PACTID = '||to_char(pactid));
51 hr_utility.trace('PACTID = '||to_char(pactid));
52 select ppa.legislative_parameters
53 into lv_legislative_param
54 from pay_payroll_actions ppa
55 where payroll_action_id = pactid;
56 l_gre_id := pay_core_utils.get_parameter('TRANSFER_GRE',lv_legislative_param);
57 sqlstr := 'AND PAA.tax_unit_id = '||to_number(l_gre_id);
58
59 hr_utility.set_location(l_proc, 20);
60 hr_utility.trace(sqlstr);
61
62 exception when others then
63 hr_utility.set_location(l_proc, 30);
64 end range_cursor;
65
66 ---------------------------------- action_creation ----------------------------------
67 --
68 PROCEDURE action_creation(pactid IN NUMBER,
69 stperson IN NUMBER,
70 endperson IN NUMBER,
71 chunk IN NUMBER,
72 sqlstr OUT NOCOPY VARCHAR2) IS
73
74 l_proc VARCHAR2(240);
75 ld_effective_date DATE;
76 ld_year_end DATE;
77 ln_tax_unit_id NUMBER;
78 BEGIN
79 l_proc := gv_package_name||'range_cursor';
80 hr_utility.set_location(l_proc, 10);
81
82 hr_utility.trace('PACTID = '||to_char(pactid));
83
84 SELECT effective_date,
85 pay_core_utils.get_parameter('TRANSFER_GRE',legislative_parameters)
86 INTO ld_effective_date,
87 ln_tax_unit_id
88 FROM pay_payroll_actions
89 WHERE payroll_action_id = pactid;
90
91 ld_year_end := add_months(trunc(ld_effective_date, 'Y'),12) -1;
92 hr_utility.trace('year end '|| to_char(ld_year_end,'dd-mm-yyyy'));
93 sqlstr := ' AND PAA.tax_unit_id = '||to_number(ln_tax_unit_id);
94 sqlstr := sqlstr ||' AND not exists (SELECT 1
95 FROM pay_payroll_actions ppa,
96 pay_assignment_actions paa
97 WHERE ppa.report_type = ''YREND''
98 AND ppa.action_status = ''C''
99 AND ppa.effective_date = '''|| ld_year_end ||'''
100 AND pay_core_utils.get_parameter(
101 ''TRANSFER_GRE'',
102 legislative_parameters) = '|| ln_tax_unit_id ||'
103 AND ppa.payroll_action_id = paa.payroll_action_id
104 AND paa.action_status = ''C''
105 AND ppa.business_group_id = paf.business_group_id
106 AND paa.serial_number = TO_CHAR(paf.person_id))';
107
108 hr_utility.set_location(l_proc, 20);
109 hr_utility.trace(sqlstr);
110
111 EXCEPTION WHEN OTHERS THEN
112 hr_utility.set_location(l_proc, 30);
113 END action_creation;
114
115 PROCEDURE archive_code (pactid IN NUMBER,
116 sqlstr OUT NOCOPY VARCHAR2) IS
117
118 l_gre_id VARCHAR2(50);
119 lv_legislative_param VARCHAR2(240);
120 l_proc VARCHAR2(240);
121
122 BEGIN
123 l_proc := gv_package_name||'archive_code';
124 hr_utility.set_location(l_proc, 10);
125
126 hr_utility.trace('PACTID = '||to_char(pactid));
127
128 select ppa.legislative_parameters
129 into lv_legislative_param
130 from pay_payroll_actions ppa
131 where payroll_action_id = pactid;
132
133 l_gre_id := pay_core_utils.get_parameter('TRANSFER_GRE',lv_legislative_param);
134 sqlstr := 'AND PAA.tax_unit_id = '||to_number(l_gre_id);
135 hr_utility.set_location(l_proc, 20);
136 hr_utility.trace(sqlstr);
137
138 EXCEPTION WHEN OTHERS THEN
139 hr_utility.set_location(l_proc, 30);
140 END archive_code;
141
142 --begin
143 --hr_utility.trace_on(null,'YREND_YEMA');
144
145 END PAY_US_ARCHIVE_RULES;