1 package body pay_au_payment_summary_magtape as
2 /* $Header: pyaupsm.pkb 120.4.12010000.2 2008/08/06 06:50:42 ubhat ship $*/
3 -------------------------------------------------------------------------+
4
5 -- g_debug boolean := hr_utility.debug_enabled; --Bug3132178
6 g_debug boolean := TRUE; --Bug3132178
7
8
9 procedure range_code
10 (p_payroll_action_id in pay_payroll_actions.payroll_action_id%type,
11 p_sql out NOCOPY varchar2) is
12 begin
13 -- hr_utility.trace_on(null, 'magtape');
14 hr_utility.set_location('Start of range_code',1);
15 /*Bug2920725 Corrected base tables to support security model*/
16
17 p_sql := ' select distinct p.person_id' ||
18 ' from per_people_f p,' ||
19 ' pay_payroll_actions pa ' ||
20 ' where pa.payroll_action_id = :payroll_action_id' ||
21 ' and p.business_group_id = pa.business_group_id' ||
22 ' order by p.person_id';
23
24 IF g_debug THEN
25 hr_utility.set_location('End of range_code',2);
26 END IF;
27 end range_code;
28
29 -----------------------------------------------------------------------+
30 -- This procedure is used to further restrict the Assignment Action
31 -- Creation. It calls the procedure that actually inserts the Assignment
32 -- Actions.
33 -----------------------------------------------------------------------+
34
35 procedure assignment_action_code
36 (p_payroll_action_id in pay_payroll_actions.payroll_action_id%type,
37 p_start_person_id in per_all_people_f.person_id%type,
38 p_end_person_id in per_all_people_f.person_id%type,
39 p_chunk in number) is
40
41 v_next_action_id pay_assignment_actions.assignment_action_id%type;
42 v_run_action_id pay_assignment_actions.assignment_action_id%type;
43 p_assignment_id pay_assignment_Actions.assignment_id%type;
44 ps_report_id pay_assignment_actions.assignment_action_id%type;
45 l_payment_summary_type varchar2(5) := 'O'; /* bug 6630375 */
46
47 ------start of Bug3132178-----------------------------------------------------
48 l_testing_flag varchar2(5):='N';
49 l_archive_payroll_action pay_payroll_actions.payroll_action_id%type;
50
51
52 CURSOR get_parameters
53 IS
54 SELECT pay_core_utils.get_parameter('IS_TESTING',ppa.legislative_parameters),
55 pay_core_utils.get_parameter('ARCHIVE_PAYROLL_ACTION',ppa.legislative_parameters),
56 pay_core_utils.get_parameter('PAYMENT_SUMMARY_TYPE',ppa.legislative_parameters) /* bug 6630375 */
57 FROM pay_payroll_actions ppa
58 WHERE ppa.payroll_Action_id = p_payroll_action_id;
59
60
61 CURSOR process_assignments_val
62 (c_payroll_action_id in pay_payroll_actions.payroll_action_id%type,
63 c_start_person_id in per_all_people_f.person_id%type,
64 c_end_person_id in per_all_people_f.person_id%type)
65 IS
66 SELECT DISTINCT 'ASSIGNMENT_ACTION_ID=C',
67 paa.assignment_action_id ps_report_id,
68 paa.assignment_id
69 FROM pay_assignment_actions paa,
70 per_assignments_f a
71 WHERE paa.payroll_action_id = c_payroll_action_id
72 AND paa.action_status = 'C'
73 AND a.assignment_id = paa.assignment_id
74 AND pay_au_payment_summary.get_archive_value('X_REPORTING_FLAG',paa.assignment_action_id)='YES'
75 AND pay_au_payment_summary.get_archive_value('X_CURR_TERM_0_BAL_FLAG',paa.assignment_action_id)='NO' /* Added for bug 5257622 */
76 AND a.person_id BETWEEN c_start_person_id AND c_end_person_id ;
77
78 --------End of Bug3132178--------------------------------------------------
79
80
81 /*Bug2920725 Corrected base tables to support security model*/
82 CURSOR process_assignments
83 (c_payroll_action_id in pay_payroll_actions.payroll_action_id%type,
84 c_start_person_id in per_all_people_f.person_id%type,
85 c_end_person_id in per_all_people_f.person_id%type) is
86 SELECT /*+ INDEX (apac PAY_ASSIGNMENT_ACTIONS_N50)
87 INDEX (ppac PAY_ASSIGNMENT_ACTIONS_PK)
88 INDEX(mpa PAY_PAYROLL_ACTIONS_PK)
89 INDEX(ppai PAY_ACTION_INTERLOCKS_FK2)
90 INDEX (p PER_PEOPLE_F_PK)
91 INDEX(a PER_ASSIGNMENTS_F_PK) */
92 DISTINCT 'ASSIGNMENT_ACTION_ID=C',
93 ppac.assignment_action_id ps_report_id,
94 ppac.assignment_id
95 FROM pay_payroll_actions mpa,
96 per_people_f p,
97 per_assignments_f a,
98 pay_payroll_actions apa,
99 pay_assignment_actions apac,
100 pay_payroll_actions ppa,
101 pay_assignment_actions ppac,
102 pay_action_interlocks ppai
103 WHERE mpa.payroll_action_id =c_payroll_action_id
104 AND p.person_id = a.person_id
105 AND p.person_id BETWEEN c_start_person_id AND c_end_person_id
106 AND p.business_group_id = mpa.business_group_id
107 AND apa.payroll_action_id = apac.payroll_action_id
108 AND ppa.payroll_action_id = ppac.payroll_action_id
109 AND apac.assignment_action_id = ppai.locked_action_id
110 AND ppac.assignment_action_id = ppai.locking_action_id
111 and apa.action_status = 'C'
112 AND ppa.action_status = 'C'
113 AND apa.payroll_action_id = pay_core_utils.get_parameter('ARCHIVE_ID',ppa.legislative_parameters)
114 AND a.assignment_id = apac.assignment_id
115 AND a.assignment_id = ppac.assignment_id
116 AND apa.report_type ='AU_PAYMENT_SUMMARY'
117 AND ppa.report_type = 'AU_PAYMENT_SUMMARY_REPORT'
118 AND pay_core_utils.get_parameter('BUSINESS_GROUP_ID',apa.legislative_parameters)=
119 pay_core_utils.get_parameter('BUSINESS_GROUP_ID',mpa.legislative_parameters)
120 AND pay_core_utils.get_parameter('REGISTERED_EMPLOYER',apa.legislative_parameters)=
121 pay_core_utils.get_parameter('REGISTERED_EMPLOYER',mpa.legislative_parameters)
122 AND pay_core_utils.get_parameter('FINANCIAL_YEAR',apa.legislative_parameters)=
123 pay_core_utils.get_parameter('FINANCIAL_YEAR',mpa.legislative_parameters)
124 AND NOT EXISTS /* 5471093 */
125 (SELECT /*+ ORDERED */ locked_action_id
126 FROM pay_action_interlocks pail,
127 pay_assignment_actions paa1,
128 pay_payroll_actions paas
129 WHERE paas.action_type='X'
130 and paas.action_status='C'
131 AND paas.report_type='AU_PS_DATA_FILE'
132 AND paa1.payroll_action_id = paas.payroll_action_id
133 AND pail.locking_action_id = paa1.assignment_action_id
134 AND pail.locked_action_id = ppac.assignment_action_id);
135
136
137 CURSOR process_assignments_val_amend /* bug 6630375 */
138 (c_payroll_action_id in pay_payroll_actions.payroll_action_id%type,
139 c_start_person_id in per_all_people_f.person_id%type,
140 c_end_person_id in per_all_people_f.person_id%type)
141 IS
142 SELECT DISTINCT 'ASSIGNMENT_ACTION_ID=C',
143 paa.assignment_action_id ps_report_id,
144 paa.assignment_id
145 FROM pay_assignment_actions paa,
146 per_assignments_f a,
147 pay_payroll_actions ppa
148 WHERE ppa.payroll_action_id = c_payroll_action_id
149 AND ppa.payroll_action_id = paa.payroll_action_id
150 AND ppa.action_status = 'C'
151 AND a.assignment_id = paa.assignment_id
152 AND pay_au_payment_summary.get_archive_value('X_REPORTING_FLAG',paa.assignment_action_id)='YES'
153 AND pay_au_payment_summary.get_archive_value('X_CURR_TERM_0_BAL_FLAG',paa.assignment_action_id)='NO'
154 AND pay_au_payment_summary.get_archive_value('X_PAYMENT_SUMMARY_TYPE',paa.assignment_action_id)='A'
155 AND ppa.report_type = 'AU_PAY_SUMM_AMEND'
156 AND a.person_id BETWEEN c_start_person_id AND c_end_person_id ;
157
158
159 CURSOR process_assignments_amend /* bug 6630375 */
160 (c_payroll_action_id in pay_payroll_actions.payroll_action_id%type,
161 c_start_person_id in per_all_people_f.person_id%type,
162 c_end_person_id in per_all_people_f.person_id%type) is
163 SELECT /*+ INDEX (apac PAY_ASSIGNMENT_ACTIONS_N50)
164 INDEX (ppac PAY_ASSIGNMENT_ACTIONS_PK)
165 INDEX(mpa PAY_PAYROLL_ACTIONS_PK)
166 INDEX(ppai PAY_ACTION_INTERLOCKS_FK2)
167 INDEX (p PER_PEOPLE_F_PK)
168 INDEX(a PER_ASSIGNMENTS_F_PK) */
169 DISTINCT 'ASSIGNMENT_ACTION_ID=C',
170 ppac.assignment_action_id ps_report_id,
171 ppac.assignment_id
172 FROM pay_payroll_actions mpa,
173 per_people_f p,
174 per_assignments_f a,
175 pay_payroll_actions apa,
176 pay_assignment_actions apac,
177 pay_payroll_actions ppa,
178 pay_assignment_actions ppac,
179 pay_action_interlocks ppai
180 WHERE mpa.payroll_action_id =c_payroll_action_id
181 AND p.person_id = a.person_id
182 AND p.person_id BETWEEN c_start_person_id AND c_end_person_id
183 AND p.business_group_id = mpa.business_group_id
184 AND apa.payroll_action_id = apac.payroll_action_id
185 AND ppa.payroll_action_id = ppac.payroll_action_id
186 AND apac.assignment_action_id = ppai.locked_action_id
187 AND ppac.assignment_action_id = ppai.locking_action_id
188 and apa.action_status = 'C'
189 AND ppa.action_status = 'C'
190 AND apa.payroll_action_id = pay_core_utils.get_parameter('ARCHIVE_ID',ppa.legislative_parameters)
191 AND a.assignment_id = apac.assignment_id
192 AND a.assignment_id = ppac.assignment_id
193 AND apa.report_type ='AU_PAY_SUMM_AMEND'
194 AND ppa.report_type = 'AU_PAYMENT_SUMMARY_REPORT'
195 AND pay_core_utils.get_parameter('BUSINESS_GROUP_ID',apa.legislative_parameters)=
196 pay_core_utils.get_parameter('BUSINESS_GROUP_ID',mpa.legislative_parameters)
197 AND pay_core_utils.get_parameter('REGISTERED_EMPLOYER',apa.legislative_parameters)=
198 pay_core_utils.get_parameter('REGISTERED_EMPLOYER',mpa.legislative_parameters)
199 AND pay_core_utils.get_parameter('FINANCIAL_YEAR',apa.legislative_parameters)=
200 pay_core_utils.get_parameter('FINANCIAL_YEAR',mpa.legislative_parameters)
201 AND pay_au_payment_summary.get_archive_value('X_PAYMENT_SUMMARY_TYPE', apac.assignment_action_id)='A'
202 AND NOT EXISTS /* 5471093 */
203 (SELECT /*+ ORDERED */ locked_action_id
204 FROM pay_action_interlocks pail,
205 pay_assignment_actions paa1,
206 pay_payroll_actions paas
207 WHERE paas.action_type='X'
208 and paas.action_status='C'
209 AND paas.report_type='AU_PS_DATA_FILE'
210 AND paa1.payroll_action_id = paas.payroll_action_id
211 AND pail.locking_action_id = paa1.assignment_action_id
212 AND pail.locked_action_id = ppac.assignment_action_id);
213
214 CURSOR next_action_id IS
215 SELECT pay_assignment_actions_s.NEXTVAL
216 FROM dual;
217
218 BEGIN
219
220 IF g_debug THEN
221 hr_utility.set_location('Start of assignment_action_code',3);
222 END IF;
223 --------start of Bug3132178-----------------------------------------------------
224 OPEN get_parameters;
225 FETCH get_parameters INTO l_testing_flag,l_archive_payroll_action,l_payment_summary_type;
226 CLOSE get_parameters;
227
228
229 IF l_testing_flag = 'Y' THEN -- In this case fetch the assignments processed by archival process
230 IF l_payment_summary_type = 'O' THEN
231 FOR process_rec IN process_assignments_val (l_archive_payroll_action,
232 p_start_person_id,
233 p_end_person_id)
234 LOOP
235 EXIT WHEN process_assignments_val%NOTFOUND;
236 OPEN next_action_id;
237 FETCH next_action_id INTO v_next_action_id;
238 CLOSE next_action_id;
239 hr_nonrun_asact.insact(v_next_action_id,
240 process_rec.assignment_id,
241 p_payroll_action_id,
242 p_chunk,
243 NULL);
244 IF g_debug THEN
245 hr_utility.set_location('After calling hr_nonrun_asact.insint',14);
246 END IF;
247 END LOOP;
248 ELSE /* bug 6630375 */
249 FOR process_rec IN process_assignments_val_amend (l_archive_payroll_action,
250 p_start_person_id,
251 p_end_person_id)
252 LOOP
253 EXIT WHEN process_assignments_val_amend%NOTFOUND;
254 OPEN next_action_id;
255 FETCH next_action_id INTO v_next_action_id;
256 CLOSE next_action_id;
257 hr_nonrun_asact.insact(v_next_action_id,
258 process_rec.assignment_id,
259 p_payroll_action_id,
260 p_chunk,
261 NULL);
262 IF g_debug THEN
263 hr_utility.set_location('After calling hr_nonrun_asact.insint',14);
264 END IF;
265 END LOOP;
266 END IF;
267 ------End of Bug3132178-----------------------------------------------------
268 ELSE -- In this case fetch the assignments locked by Self-Printed Process.
269 IF l_payment_summary_type = 'O' THEN
270 FOR process_rec IN process_assignments (p_payroll_action_id,
271 p_start_person_id,
272 p_end_person_id)
273 LOOP
274 EXIT WHEN process_assignments%NOTFOUND;
275 OPEN next_action_id;
276 FETCH next_action_id INTO v_next_action_id;
277 CLOSE next_action_id;
278 hr_nonrun_asact.insact(v_next_action_id,
279 process_rec.assignment_id,
280 p_payroll_action_id,
281 p_chunk,
282 NULL);
283 IF g_debug THEN
284 hr_utility.set_location('Before calling hr_nonrun_asact.insint',14);
285 hr_utility.set_location('locking action' || v_next_action_id, 15);
286 hr_utility.set_location('locked action' || process_rec.ps_report_id, 16);
287 END IF;
288 hr_nonrun_asact.insint(v_next_action_id, -- locking action id
289 process_rec.ps_report_id); -- locked action id
290
291 IF g_debug THEN
292 hr_utility.set_location('After calling hr_nonrun_asact.insint',14);
293 END IF;
294 END LOOP;
295 ELSE /* bug 6630375 */
296 FOR process_rec IN process_assignments_amend (p_payroll_action_id,
297 p_start_person_id,
298 p_end_person_id)
299 LOOP
300 EXIT WHEN process_assignments_amend%NOTFOUND;
301 OPEN next_action_id;
302 FETCH next_action_id INTO v_next_action_id;
303 CLOSE next_action_id;
304 hr_nonrun_asact.insact(v_next_action_id,
305 process_rec.assignment_id,
306 p_payroll_action_id,
307 p_chunk,
308 NULL);
309 IF g_debug THEN
310 hr_utility.set_location('Before calling hr_nonrun_asact.insint',14);
311 hr_utility.set_location('locking action' || v_next_action_id, 15);
312 hr_utility.set_location('locked action' || process_rec.ps_report_id, 16);
313 END IF;
314 hr_nonrun_asact.insint(v_next_action_id, -- locking action id
315 process_rec.ps_report_id); -- locked action id
316
317 IF g_debug THEN
318 hr_utility.set_location('After calling hr_nonrun_asact.insint',14);
319 END IF;
320 END LOOP;
321 END IF;
322 END IF;
323 IF g_debug THEN
324 hr_utility.set_location('End of assignment_action_code',5);
325 END IF;
326 END assignment_action_code;
327
328
329 -----------------------------------------------------------------------+
330 -- This is used by legislation groups to set global contexts that are
331 -- required for the lifetime of the archiving process. This is null
332 -- because there are no setup requirements, but a procedure needs to
333 -- exist in pay_report_format_mappings_f, otherwise the archiver will
334 -- assume that no archival of data is required.
335 ------------------------------------------------------------------------+
336
337 procedure initialization_code
338 (p_payroll_action_id in pay_payroll_actions.payroll_action_id%type) is
339 begin
340 IF g_debug THEN
341 hr_utility.set_location('Start of initialization_code',6);
342 END IF;
343 null;
344 IF g_debug THEN
345 hr_utility.set_location('End of initialization_code',7);
346 END IF;
347 end initialization_code;
348
349
350 -------------------------------------------------------------------------+
351 -- Used to actually perform the archival of data. We are not archiving
352 -- any data here, so this is null.
353 ------------------------------------------------------------------------+
354 procedure archive_code
355 (p_payroll_action_id in pay_assignment_actions.payroll_action_id%type,
356 p_effective_date in date)
357 is
358
359 begin
360 IF g_debug THEN
361 hr_utility.set_location('Start of archive_code',8);
362 END IF;
363 null;
364 IF g_debug THEN
365 hr_utility.set_location('End of archive_code',9);
366 END IF;
367 end archive_code;
368 ---------------------------------------------------------------------------+
369
370 End pay_au_payment_summary_magtape;
[Home] [Help]