[Home] [Help]
PACKAGE BODY: APPS.PAY_IE_HIST_ARCH_PKG
Source
1 PACKAGE BODY PAY_IE_HIST_ARCH_PKG AS
2 /* $Header: pyiepupg.pkb 120.1 2009/08/21 11:50:46 vijranga noship $ */
3
4 g_package CONSTANT VARCHAR2(30) := 'PAY_IE_HIST_ARCH_PKG.';
5
6
7 /**------------------------------------------------------------
8 ** Name : get_parameters
9 ** Purpose : This procedure returns the value of the given parameter name.
10 **----------------------------------------------------------**/
11
12 procedure get_parameters(p_payroll_action_id in number,
13 p_token_name in varchar2,
17 p_token char) is
14 p_token_value out NOCOPY varchar2) is
15
16 cursor csr_parameter_info(p_pact_id number,
18 select pay_core_utils.get_parameter(p_token,legislative_parameters)
19 from pay_payroll_actions
20 where payroll_action_id = p_pact_id;
21
22 l_token_value varchar2(50) := null;
23 l_proc VARCHAR2(50) := g_package || 'get_parameters';
24
25 begin
26
27 hr_utility.set_location('Entering ' || l_proc,10);
28 hr_utility.set_location('p_token_name: ' || p_token_name,20);
29
30 open csr_parameter_info(p_payroll_action_id,p_token_name);
31 fetch csr_parameter_info into l_token_value;
32 close csr_parameter_info;
33
34 p_token_value := l_token_value;
35 hr_utility.set_location('l_token_value: ' || l_token_value,20);
36 hr_utility.set_location('Leaving ' || l_proc,30);
37
38 exception
39 when OTHERS
40 then
41 hr_utility.set_location('Exception occured in get_parameters: '||SQLERRM,30);
42 raise;
43 end get_parameters;
44
45 /**------------------------------------------------------------
46 ** Name : range_cursor
47 ** Purpose : This procedure returns an SQL statement to select all the
48 ** people that may be eligible for payslip reports.
49 ** The archiver uses this cursor to split the people into chunks
50 ** for parallel processing.
51 **----------------------------------------------------------**/
52 procedure range_cursor (pactid in number, sqlstr out nocopy varchar2) is
53
54 l_payroll_id number := null;
55 l_proc CONSTANT VARCHAR2(50):= g_package||'range_cursor';
56
57 begin
58
59 hr_utility.set_location('Entering ' || l_proc,10);
60
61 pay_ie_hist_arch_pkg.get_parameters (p_payroll_action_id => pactid
62 , p_token_name => 'PAYROLL_NAME'
63 , p_token_value => l_payroll_id);
64
65 hr_utility.set_location('l_payroll_id = ' || l_payroll_id,20);
66
67 if l_payroll_id is null then
68 -- Use full cursor not restricting by payroll
69 hr_utility.trace('Range Cursor Not using Payroll Restriction');
70 sqlstr := 'SELECT DISTINCT person_id
71 FROM per_people_f ppf,
72 pay_payroll_actions ppa
73 WHERE ppa.payroll_action_id = :payroll_action_id
74 AND ppa.business_group_id +0= ppf.business_group_id
75 ORDER BY ppf.person_id';
76 else
77 -- The Payroll ID was used as parameter, so restrict by this
78 hr_utility.trace('Range Cursor using Payroll Restriction');
79 sqlstr := 'SELECT DISTINCT ppf.person_id
80 FROM per_all_people_f ppf,
81 pay_payroll_actions ppa,
82 per_all_assignments_f paaf
83 WHERE ppa.payroll_action_id = :payroll_action_id
84 AND ppf.business_group_id +0 = ppa.business_group_id
85 AND paaf.person_id = ppf.person_id
86 AND paaf.payroll_id = '|| to_char(l_payroll_id)||
87 ' ORDER BY ppf.person_id';
88 end if;
89
90 hr_utility.set_location('Leaving ' || l_proc,30);
91 exception
92 when OTHERS
93 then
94 sqlstr := 'select 1 '||
95 '/* ERROR - Employer Details Fetch failed with: '||
96 sqlerrm(sqlcode)||' */ '||
97 'from dual where to_char(:payroll_action_id) = dummy';
98 hr_utility.set_location('Exception occured in range_cursor: '||SQLERRM,30);
99 raise;
100 end range_cursor;
101
102 /**------------------------------------------------------------
103 ** Name : action_creation
104 ** Purpose : This procedure fetches archived assignment_action_id's
105 ** from archive table and creates a new temp assignment_action_id
106 ** for each of them. This new temp id along with the archived id
107 ** will be inserted into pay_temp_object_actions.
108 **----------------------------------------------------------**/
109 procedure action_creation(pactid in number,
110 stperson in number,
111 endperson in number,
112 chunk in number) is
113
114 cursor get_arch_action_det (cp_payroll_id number,
115 cp_bg_id number,
116 cp_start_date varchar2,
117 cp_end_date varchar2 ) is
118 select distinct paa.assignment_action_id,
119 paaf.assignment_id,
120 ppa.effective_date
121 from per_all_assignments_f paaf,
122 pay_payroll_actions ppa,
123 pay_assignment_actions paa,
124 pay_action_interlocks lck,
125 pay_payroll_actions ppa1,
126 pay_assignment_actions paa1
127 where paa.SOURCE_ACTION_ID is null
128 and paaf.person_id between stperson and endperson
129 and ppa.report_type = 'IEPS'
130 and ppa.action_type ='X'
131 and paa.assignment_id = paaf.assignment_id
132 and paaf.business_group_id = cp_bg_id
133 and ppa.payroll_action_id = paa.payroll_action_id
134 and ppa.start_date between fnd_date.canonical_to_date(cp_start_date)
135 and fnd_date.canonical_to_date(cp_end_date)
136 and ppa.report_qualifier = 'IE'
137 and ppa.report_category = 'ARCHIVE'
138 and ppa.business_group_id = paaf.business_group_id
139 and paa.action_status = 'C'
140 and lck.locking_action_id = paa.assignment_action_id
141 and lck.locked_action_id = paa1.assignment_action_id
142 and ppa1.payroll_action_id = paa1.payroll_action_id
143 and ppa1.action_status = 'C'
144 and ppa1.payroll_id = nvl(cp_payroll_id,ppa1.payroll_id)
145 and ppa1.business_group_id = ppa.business_group_id
146 and ppa1.action_type IN ('U','P')
147 and not exists (
148 select 1
149 from pay_action_information pai
150 where pai.action_context_id = paa.assignment_action_id
151 and pai.action_context_type = 'AAP'
152 and pai.assignment_id = paa.assignment_id
153 and pai.action_information_category = 'EMEA ELEMENT INFO'
154 and pai.action_information3 in ('E','D')) ;
155
156 l_start_date varchar2(30);
157 l_end_date varchar2(30);
158 l_payroll_id number := null;
159 l_bg_id number := null;
160 lockingactid varchar2(100);
161 l_proc VARCHAR2(50) := g_package||'action_creation';
162
163 begin
164 hr_utility.set_location('Entering ' || l_proc,10);
165
166 get_parameters(pactid,'PAYROLL_NAME',l_payroll_id);
167 get_parameters(pactid,'P_START_DATE',l_start_date);
168 get_parameters(pactid,'P_END_DATE',l_end_date);
169
170 select business_group_id
171 into l_bg_id
172 from pay_payroll_actions
173 where payroll_action_id = pactid;
174
175 hr_utility.set_location('l_payroll_id :' ||l_payroll_id ,20);
176 hr_utility.set_location('l_start_date :' ||l_start_date ,20);
177 hr_utility.set_location('l_end_date :' ||l_end_date ,20);
178 hr_utility.set_location('l_bg_id :' ||l_bg_id ,20);
179 hr_utility.set_location('stperson :' ||stperson ,20);
180 hr_utility.set_location('endperson :' ||endperson ,20);
181
182 for asgrec in get_arch_action_det(l_payroll_id,l_bg_id,l_start_date,l_end_date)
183 loop
184
185 select pay_assignment_actions_s.nextval
186 into lockingactid
187 from dual;
188
189 hr_nonrun_asact.insact
190 (
191 lockingactid => lockingactid,
192 assignid => asgrec.assignment_id,
193 pactid => pactid,
194 chunk => chunk,
195 object_type => 'ASG',
196 object_id => asgrec.assignment_action_id
197 );
198 end loop;
199 hr_utility.set_location('Leaving ' || l_proc,30);
200
201 exception
202 when OTHERS
203 then
204 hr_utility.set_location('Exception occured in action_creation: '||SQLERRM,30);
205 raise;
206 end action_creation;
207
208 procedure archive_init(p_payroll_action_id in number) is
209 l_proc VARCHAR2(50) := g_package || 'archinit';
210
211 begin
212 hr_utility.set_location('Entering ' || l_proc,10);
213 null;
214 hr_utility.set_location('Leaving ' || l_proc,20);
215 end archive_init;
216
217 /**------------------------------------------------------------
218 ** Name : archive_historic_data
219 ** Purpose : This procedure gets the archived assignment_action_id from
220 ** pay_temp_object_actions and passes it to the
221 ** get_pay_deduct_element_info procedure to archive payments and
222 ** deductions data.
223 **----------------------------------------------------------**/
224
225 procedure archive_historic_data (p_assactid in number,
226 p_effective_date in date) IS
227
228 cursor get_archive_asg_id is
229 select ptoa.object_id assignment_action_id
230 from pay_temp_object_actions ptoa
231 where object_action_id = p_assactid;
232
233 l_assignment_action_id pay_assignment_actions.assignment_action_id%type;
234 l_proc VARCHAR2(50) := g_package || 'archive_historic_data';
235
236 begin
237 hr_utility.set_location('Entering ' || l_proc,10);
238 hr_utility.set_location('Current assignment_action_id - p_assactid: '
239 || p_assactid, 10);
240
241 --getting the archived assignment_action_id
242 open get_archive_asg_id;
243 fetch get_archive_asg_id into l_assignment_action_id;
244 close get_archive_asg_id;
245
246 hr_utility.set_location('Archived assignment_action_id - l_assignment_action_id: '
247 || l_assignment_action_id, 20);
248
249 --Calling archive procedure to archive payments and deductions details for
250 --this assignment_action_id
251 pay_ie_legislative_archive.get_pay_deduct_element_info(l_assignment_action_id);
252 hr_utility.set_location('Leaving ' || l_proc,40);
253
254 exception
255 when OTHERS
256 then
257 hr_utility.set_location('Exception occured in archive_historic_data: '||SQLERRM,30);
258 raise;
259 end archive_historic_data;
260
261 END pay_ie_hist_arch_pkg;