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