[Home] [Help]
PACKAGE BODY: APPS.PAY_GB_HIST_ARCH_PKG
Source
1 PACKAGE BODY PAY_GB_HIST_ARCH_PKG AS
2 /* $Header: pygbpupg.pkb 120.1 2010/05/14 07:02:17 dwkrishn 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_gb_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_assignment_id (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
134 and endperson
135 and ppa.report_type = 'UKPS'
136 and ppa.action_type ='X'
137 and paa.assignment_id = paaf.assignment_id
138 and paaf.business_group_id = cp_bg_id
139 and ppa.payroll_action_id = paa.payroll_action_id
140 and ppa.start_date between fnd_date.canonical_to_date(cp_start_date)
141 and fnd_date.canonical_to_date(cp_end_date)
142 and ppa.report_qualifier = 'GB'
143 and ppa.report_category = 'ARCHIVE'
144 and ppa.business_group_id = paaf.business_group_id
145 and paa.action_status = 'C'
146 and lck.locking_action_id = paa.assignment_action_id
147 and lck.locked_action_id = paa1.assignment_action_id
148 and ppa1.payroll_action_id = paa1.payroll_action_id
149 and ppa1.action_status = 'C'
150 and ppa1.payroll_id = nvl(cp_payroll_id,ppa1.payroll_id)
151 and ppa1.business_group_id = ppa.business_group_id
152 and ppa1.action_type IN ('U','P')
153 and not exists (
154 select 1
155 from pay_action_information pai
156 where pai.action_context_id = paa.assignment_action_id
157 and pai.action_context_type = 'AAP'
158 and pai.assignment_id = paa.assignment_id
159 and pai.action_information_category = 'GB ELEMENT PAYSLIP INFO')
160 ;
161
162 l_start_date varchar2(30);
163 l_end_date varchar2(30);
164 l_payroll_id number := null;
165 l_bg_id number := null;
166 lockingactid varchar2(100);
167 begin
168 hr_utility.trace('Entering action_creation' );
169
170 get_parameters(pactid,'PAYROLL_NAME',l_payroll_id);
171 get_parameters(pactid,'P_START_DATE',l_start_date);
172 get_parameters(pactid,'P_END_DATE',l_end_date);
173
174 select business_group_id
175 into l_bg_id
176 from pay_payroll_actions
177 where payroll_action_id = pactid;
178
179 hr_utility.set_location('l_payroll_id :' ||l_payroll_id ,10);
180 hr_utility.set_location('l_start_date :' ||l_start_date ,10);
181 hr_utility.set_location('l_end_date :' ||l_end_date ,10);
182 hr_utility.set_location('l_bg_id :' ||l_bg_id ,10);
183 hr_utility.set_location('stperson :' ||stperson ,10);
184 hr_utility.set_location('endperson :' ||endperson ,10);
185
186 for asgrec in get_assignment_id(l_payroll_id,l_bg_id,l_start_date,l_end_date)
187 loop
188 select pay_assignment_actions_s.nextval
189 into lockingactid
190 from dual;
191
192 hr_nonrun_asact.insact
193 (
194 lockingactid => lockingactid,
195 assignid => asgrec.assignment_id,
196 pactid => pactid,
197 chunk => chunk,
198 object_type => 'ASG',
199 object_id => asgrec.assignment_action_id
200 );
201 end loop;
202 hr_utility.trace('Leaving action_creation' );
203 exception
204 when OTHERS
205 then
206 hr_utility.set_location('Exception occured in action_creation: '||SQLERRM,30);
207 raise;
208 end action_creation;
209
210 procedure archinit(p_payroll_action_id in number) is
211 begin
212 hr_utility.trace('Entering archinit');
213 null;
214 hr_utility.trace('Leaving archinit');
215 end archinit;
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 begin
235
236 hr_utility.trace('Entering archive_historic_data');
237 hr_utility.set_location('Current assignment_action_id - p_assactid: '
238 || p_assactid, 10);
239
240 --getting the archived assignment_action_id
241
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_GB_PAYSLIP_ARCHIVE.get_pay_deduct_element_info(l_assignment_action_id);
252
253 hr_utility.trace('Leaving archive_historic_data');
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_gb_hist_arch_pkg;