[Home] [Help]
PACKAGE BODY: APPS.PAY_IE_PYMT_SUMMARY_RPT_PKG
Source
1 PACKAGE BODY pay_ie_pymt_summary_rpt_pkg AS
2 /* $Header: pyiepysm.pkb 120.2 2006/03/01 03:47:34 rbhardwa noship $ */
3
4 -------------------------------------------------------------------------------
5 -- WRITETOCLOB
6 --------------------------------------------------------------------------------
7 PROCEDURE writetoclob (p_xfdf_string OUT NOCOPY CLOB)
8 IS
9 l_str1 VARCHAR2 (1000);
10 l_str2 VARCHAR2 (20);
11 l_str3 VARCHAR2 (20);
12 l_str4 VARCHAR2 (20);
13 l_str5 VARCHAR2 (20);
14 l_str6 VARCHAR2 (30);
15 l_str7 VARCHAR2 (1000);
16 l_str8 VARCHAR2 (240);
17 l_str9 VARCHAR2 (240);
18 BEGIN
19 l_str1 :=
20 '<?xml version="1.0" encoding="UTF-8"?> <PAYMENT_SUMMARY_REPORT>';
21 l_str2 := '<';
22 l_str3 := '>';
23 l_str4 := '</';
24 l_str5 := '>';
25 l_str6 := '</PAYMENT_SUMMARY_REPORT>';
26 l_str7 :=
27 '<?xml version="1.0" encoding="UTF-8"?>
28 <PAYMENT_SUMMARY_REPORT></PAYMENT_SUMMARY_REPORT>';
29 DBMS_LOB.createtemporary (p_xfdf_string, FALSE, DBMS_LOB.CALL);
30 DBMS_LOB.OPEN (p_xfdf_string, DBMS_LOB.lob_readwrite);
31 hr_utility.set_location ('TableCnt' || TO_CHAR (vxmltable.COUNT), 13);
32
33 IF vxmltable.COUNT > 0
34 THEN
35 DBMS_LOB.writeappend (p_xfdf_string, LENGTH (l_str1), l_str1);
36
37 FOR ctr_table IN vxmltable.FIRST .. vxmltable.LAST
38 LOOP
39 hr_utility.set_location ( vxmltable (ctr_table).tagname
40 || ' '
41 || vxmltable (ctr_table).tagvalue,
42 15
43 );
44 l_str8 := vxmltable (ctr_table).tagname;
45 l_str9 := vxmltable (ctr_table).tagvalue;
46
47 IF ( l_str9 IS NOT NULL
48 AND l_str8 NOT LIKE '/%'
49 AND SUBSTR (l_str8, 1, 2) <> 'G_'
50 )
51 THEN
52 DBMS_LOB.writeappend (p_xfdf_string, LENGTH (l_str2), l_str2);
53 DBMS_LOB.writeappend (p_xfdf_string, LENGTH (l_str8), l_str8);
54 DBMS_LOB.writeappend (p_xfdf_string, LENGTH (l_str3), l_str3);
55 DBMS_LOB.writeappend (p_xfdf_string, LENGTH (l_str9), l_str9);
56 DBMS_LOB.writeappend (p_xfdf_string, LENGTH (l_str4), l_str4);
57 DBMS_LOB.writeappend (p_xfdf_string, LENGTH (l_str8), l_str8);
58 DBMS_LOB.writeappend (p_xfdf_string, LENGTH (l_str5), l_str5);
59 ELSIF l_str8 LIKE '/%' OR SUBSTR (l_str8, 1, 2) = 'G_'
60 THEN
61 DBMS_LOB.writeappend (p_xfdf_string, LENGTH (l_str2), l_str2);
62 DBMS_LOB.writeappend (p_xfdf_string, LENGTH (l_str8), l_str8);
63 DBMS_LOB.writeappend (p_xfdf_string, LENGTH (l_str3), l_str3);
64 ELSE
65 NULL;
66 END IF;
67 END LOOP;
68
69 DBMS_LOB.writeappend (p_xfdf_string, LENGTH (l_str6), l_str6);
70 ELSE
71 DBMS_LOB.writeappend (p_xfdf_string, LENGTH (l_str7), l_str7);
72 END IF;
73 /* INSERT INTO tmp
74 * VALUES (p_xfdf_string);*/
75 END writetoclob;
76
77 --
78 --------------------------------------------------------------------------------
79 -- POPULATE_PYMT_SUMMARY_REPORT
80 --------------------------------------------------------------------------------
81 PROCEDURE populate_pymt_summary_rep (
82 p_bg_id IN NUMBER,
83 p_payroll_id IN NUMBER,
84 p_period_id IN NUMBER,
85 p_consolidation_set_id IN NUMBER,
86 p_template_name IN VARCHAR2,
87 p_xml OUT NOCOPY CLOB
88 )
89 IS
90 l_time_period per_time_periods.period_name%TYPE;
91 l_payroll_name pay_payrolls_f.payroll_name%TYPE;
92 l_bg_name VARCHAR2 (240);
93 l_consolidation_set_name
94 pay_consolidation_sets.consolidation_set_name%TYPE;
95 l_report_date VARCHAR2 (12);
96
97 CURSOR csr_payroll_name
98 IS
99 SELECT DISTINCT payroll_name
100 FROM pay_payrolls_f
101 WHERE payroll_id = p_payroll_id;
102
103 --
104 CURSOR csr_time_period
105 IS
106 SELECT period_name
107 FROM per_time_periods
108 WHERE time_period_id = p_period_id;
109
110 --
111 CURSOR csr_consolidation_set_name
112 IS
113 SELECT consolidation_set_name
114 FROM pay_consolidation_sets
115 WHERE consolidation_set_id = p_consolidation_set_id;
116
117 --
118 CURSOR csr_get_pymt_data
119 IS
120 SELECT dummy_break,
121 payment_method_type,
122 payment_method_type || ' Totals' payment_method_type_totals,
123 NAME,
124 source_sort_code,
125 bank_name,
126 bank_branch,
127 account_number,
128 account_name,
129 amount,
130 total_assignments_paid,
131 SUM (amount) OVER (PARTITION BY payment_method_type)
132 AS amount_per_type,
133 SUM (amount) OVER (PARTITION BY dummy_break)
134 AS total_amount_paid,
135 SUM (total_assignments_paid) OVER (PARTITION BY
136 payment_method_type)
137 AS asg_paid_per_type,
138 SUM (total_assignments_paid) OVER (PARTITION BY dummy_break)
139 AS total_asg_paid
140 FROM (SELECT 'X' dummy_break,
141 SUBSTR (ppttl.payment_type_name, 1, 14)
142 payment_method_type,
143 popmftl.org_payment_method_name NAME,
144 SUBSTR (pea.segment1, 1, 6) source_sort_code,
145 hr_general.decode_lookup ('HR_IE_BANK',
146 pea.segment2) bank_name,
147 SUBSTR (pea.segment3, 1, 35) bank_branch,
148 SUBSTR (pea.segment4, 1, 8) account_number,
149 SUBSTR (pea.segment5, 1, 18) account_name,
150 SUM (TO_NUMBER (ppp.VALUE)) amount,
151 COUNT (ppp.VALUE) total_assignments_paid
152 FROM pay_payroll_actions ppa,
153 pay_assignment_actions paa,
154 pay_pre_payments ppp,
155 pay_org_payment_methods_f_tl popmftl,
156 pay_org_payment_methods_f popmf,
157 pay_payment_types_tl ppttl,
158 pay_payment_types ppt,
159 pay_external_accounts pea,
160 per_time_periods ptp
161 WHERE ppt.payment_type_id = ppttl.payment_type_id
162 AND ppttl.LANGUAGE = USERENV ('LANG')
163 AND popmf.org_payment_method_id =
164 popmftl.org_payment_method_id
165 AND popmftl.LANGUAGE = USERENV ('LANG')
166 AND ppa.payroll_action_id = paa.payroll_action_id
167 AND ( p_consolidation_set_id IS NULL
168 OR ppa.consolidation_set_id =
169 p_consolidation_set_id
170 )
171 AND ppa.action_type IN ('U', 'P')
172 AND ppa.action_status = 'C'
173 AND ppa.payroll_id = p_payroll_id
174 AND ptp.payroll_id = ppa.payroll_id -- Bug 5070091 Offset payroll Change
175 -- Commented for Time Period Change
176
177 --AND ptp.time_period_id = p_period_id
178 /* AND ppa.effective_date BETWEEN ptp.start_date
179 AND ptp.regular_payment_date
180 */
181 AND ppa.effective_date BETWEEN popmf.effective_start_date
182 AND popmf.effective_end_date
183 AND paa.assignment_action_id = ppp.assignment_action_id
184 AND ppp.org_payment_method_id =
185 popmf.org_payment_method_id
186 AND popmf.payment_type_id = ppt.payment_type_id
187 AND popmf.external_account_id = pea.external_account_id
188 AND exists ( SELECT NULL -- Bug 5070091 Offset payroll Change
189 FROM pay_assignment_actions paa_run,
190 pay_action_interlocks pai_run,
191 pay_payroll_actions ppa_run
192 WHERE ppa_run.payroll_id = p_payroll_id
193 AND ptp.time_period_id = p_period_id
194 AND ppa_run.date_earned between ptp.start_date and ptp.end_date
195 AND ppa_run.action_type in ('R','Q')
196 AND ppa_run.payroll_action_id = paa_run.payroll_action_id
197 AND paa_run.assignment_action_id = pai_run.locked_action_id
198 AND pai_run.locking_action_id = paa.assignment_action_id
199 )
200 GROUP BY ppttl.payment_type_name,
201 popmftl.org_payment_method_name,
202 pea.segment1,
203 pea.segment2,
204 pea.segment3,
205 pea.segment4,
206 pea.segment5,
207 ppa.consolidation_set_id,
208 ppa.effective_date,
209 ptp.start_date,
210 ptp.end_date,
211 popmf.effective_start_date,
212 popmf.effective_end_date);
213
214 --
215 /* Moved entire query to exists clause so that it comes out from sub query as soon as it finds a
216 record with no Payments (5042843) */
217 CURSOR csr_get_warning_not_all_pay
218 IS
219 SELECT '*** Warning: Not all payroll runs have been paid in this
220 payroll period ***'
221 text_not_all_payroll_runs_paid
222 FROM DUAL
223 WHERE EXISTS ( SELECT NULL
224 FROM pay_payroll_actions ppa,
225 pay_assignment_actions paa,
226 per_time_periods ptp -- Bug 5070091 Offset payroll change
227 WHERE paa.payroll_action_id = ppa.payroll_action_id
228 AND ppa.action_status = 'C'
229 AND ppa.action_type IN ('Q', 'R')
230 AND ppa.payroll_id = p_payroll_id
231 AND ptp.payroll_id = ppa.payroll_id -- Bug 5070091 Offset payroll change
232 AND ppa.date_earned between ptp.start_date and ptp.end_date
233 --AND ppa.time_period_id = p_period_id
234 AND NOT EXISTS (
235 SELECT 1
236 FROM pay_action_interlocks pai,
237 pay_assignment_actions paa1,
238 pay_payroll_actions ppa1
239 WHERE pai.locked_action_id = paa.assignment_action_id
240 AND pai.locking_action_id = paa1.assignment_action_id
241 AND paa1.payroll_action_id = ppa1.payroll_action_id
242 AND ppa1.action_type IN ('U', 'P')
243 AND ppa1.action_status = 'C'
244 )
245 );
246
247 --
248 CURSOR csr_get_warning_prev_pay
249 IS
250 SELECT '*** Warning: These Amount totals include payments from previous
251 payroll period(s) ***'
252 payments_from_previous_periods
253 FROM /* per_time_periods ptp, */
254 pay_payroll_actions ppa,
255 pay_assignment_actions paa,
256 pay_pre_payments ppp
257 WHERE paa.payroll_action_id = ppa.payroll_action_id
258 AND ( p_consolidation_set_id IS NULL
259 OR ppa.consolidation_set_id = p_consolidation_set_id
260 )
261 AND ppa.action_status = 'C'
262 AND ppa.payroll_id = p_payroll_id
263 --AND ptp.time_period_id = p_period_id
264 --AND ppa.effective_date BETWEEN ptp.start_date AND ptp.end_date
265 AND ppa.action_type IN ('U', 'P')
266 AND ppp.assignment_action_id = paa.assignment_action_id
267 AND EXISTS (
268 SELECT 1
269 FROM pay_action_interlocks pai,
270 pay_assignment_actions paa1,
271 pay_payroll_actions ppa1,
272 per_time_periods ptp1
273 WHERE pai.locking_action_id = paa.assignment_action_id
274 AND pai.locked_action_id = paa1.assignment_action_id
275 AND ppa1.payroll_action_id = paa1.payroll_action_id
276 AND ppa1.action_type IN ('Q', 'R')
277 AND ppa1.action_status = 'C'
278 AND ppa1.payroll_id = ptp1.payroll_id --Bug 5070091 Offset payroll change
279 AND ppa1.date_earned between ptp1.start_date and ptp1.end_date
280 AND ptp1.time_period_id <> p_period_id)
281 --AND ppa1.time_period_id <> ptp.time_period_id);
282 AND EXISTS (
283 SELECT 1
287 per_time_periods ptp2
284 FROM pay_action_interlocks pai2,
285 pay_assignment_actions paa2,
286 pay_payroll_actions ppa2,
288 WHERE pai2.locking_action_id = paa.assignment_action_id
289 AND pai2.locked_action_id = paa2.assignment_action_id
290 AND ppa2.payroll_action_id = paa2.payroll_action_id
291 AND ppa2.action_type IN ('Q', 'R')
292 AND ppa2.action_status = 'C'
293 AND ppa2.payroll_id = ptp2.payroll_id
294 AND ppa2.date_earned between ptp2.start_date and ptp2.end_date
295 AND ptp2.time_period_id = p_period_id
296 );
297
298
299 --
300 l_pymt_type pay_payment_types_tl.payment_type_name%TYPE;
301 l_master_rec VARCHAR2 (1);
302 BEGIN
303 hr_utility.set_location ('Input Parameters', 01);
304 hr_utility.set_location ('p_bg_id ' || p_bg_id, 01);
305 hr_utility.set_location ('p_period_id ' || p_period_id, 01);
306 hr_utility.set_location ('p_payroll_id ' || p_payroll_id, 01);
307 hr_utility.set_location ( 'p_consolidation_set_id'
308 || p_consolidation_set_id,
309 01
310 );
311 l_bg_name :=
312 RTRIM (SUBSTRB (hr_reports.get_business_group (p_bg_id), 1, 240));
313 OPEN csr_payroll_name;
314 FETCH csr_payroll_name
315 INTO l_payroll_name;
316 CLOSE csr_payroll_name;
317 OPEN csr_time_period;
318 FETCH csr_time_period
319 INTO l_time_period;
320 CLOSE csr_time_period;
321
322 IF (p_consolidation_set_id IS NOT NULL)
323 THEN
324 OPEN csr_consolidation_set_name;
325 FETCH csr_consolidation_set_name
326 INTO l_consolidation_set_name;
327 CLOSE csr_consolidation_set_name;
328 END IF;
329
330 SELECT fnd_date.date_to_displaydate (SYSDATE)
331 INTO l_report_date
332 FROM DUAL;
333 hr_utility.set_location ('Header Table Creation', 10);
334 vxmltable.DELETE;
335 vctr := 1;
336 vxmltable (vctr).tagname := 'G_HEADER';
337 vxmltable (vctr).tagvalue := ' ';
338
339 IF (l_bg_name IS NOT NULL)
340 THEN
341 vctr := vctr + 1;
342 vxmltable (vctr).tagname := 'BUSINESS_GROUP';
343 vxmltable (vctr).tagvalue := l_bg_name;
344 END IF;
345
346 IF (l_payroll_name IS NOT NULL)
347 THEN
348 vctr := vctr + 1;
349 vxmltable (vctr).tagname := 'PAYROLL_NAME';
350 vxmltable (vctr).tagvalue := l_payroll_name;
351 END IF;
352
353 IF (l_time_period IS NOT NULL)
354 THEN
355 vctr := vctr + 1;
356 vxmltable (vctr).tagname := 'TIME_PERIOD';
357 vxmltable (vctr).tagvalue := l_time_period;
358 END IF;
359
360 IF (l_consolidation_set_name IS NOT NULL)
361 THEN
362 vctr := vctr + 1;
363 vxmltable (vctr).tagname := 'CONSOLIDATION_SET_NAME';
364 vxmltable (vctr).tagvalue := l_consolidation_set_name;
365 END IF;
366
367 vctr := vctr + 1;
368 vxmltable (vctr).tagname := 'REPORT_DATE';
369 vxmltable (vctr).tagvalue := l_report_date;
370 vctr := vctr + 1;
371 vxmltable (vctr).tagname := '/G_HEADER';
372 vxmltable (vctr).tagvalue := ' ';
373 l_pymt_type := NULL;
374 l_master_rec := NULL;
375
376 FOR c_pymt_record IN csr_get_pymt_data
377 LOOP
378 IF (l_master_rec <> c_pymt_record.dummy_break OR l_master_rec IS NULL
379 )
380 THEN
381 vctr := vctr + 1;
382 vxmltable (vctr).tagname := 'G_PAYMENT_MASTER_RECORD';
383 vxmltable (vctr).tagvalue := ' ';
384 vctr := vctr + 1;
385 vxmltable (vctr).tagname := 'TOTAL_AMOUNT_PAID';
386 vxmltable (vctr).tagvalue := (c_pymt_record.total_amount_paid);
387 vctr := vctr + 1;
388 vxmltable (vctr).tagname := 'TOTAL_ASG_PAID';
389 vxmltable (vctr).tagvalue := (c_pymt_record.total_asg_paid);
390 l_master_rec := c_pymt_record.dummy_break;
391 END IF;
392
393 IF ( l_pymt_type <> c_pymt_record.payment_method_type
394 OR l_pymt_type IS NULL
395 )
396 THEN
397 IF (l_pymt_type IS NOT NULL)
398 THEN
399 vctr := vctr + 1;
400 vxmltable (vctr).tagname := '/G_PYMT_TYPE_RECORD';
401 vxmltable (vctr).tagvalue := ' ';
402 END IF;
403
404 vctr := vctr + 1;
405 vxmltable (vctr).tagname := 'G_PYMT_TYPE_RECORD';
406 vxmltable (vctr).tagvalue := ' ';
407 vctr := vctr + 1;
408 vxmltable (vctr).tagname := 'PAYMENT_METHOD_TYPE_TOTALS';
409 vxmltable (vctr).tagvalue :=
410 (c_pymt_record.payment_method_type_totals
411 );
412 vctr := vctr + 1;
413 vxmltable (vctr).tagname := 'AMOUNT_PAID_PER_TYPE';
414 vxmltable (vctr).tagvalue := (c_pymt_record.amount_per_type);
415 vctr := vctr + 1;
416 vxmltable (vctr).tagname := 'ASG_PAID_PER_TYPE';
417 vxmltable (vctr).tagvalue := (c_pymt_record.asg_paid_per_type);
418 l_pymt_type := c_pymt_record.payment_method_type;
419 END IF;
420
421 vctr := vctr + 1;
425 vxmltable (vctr).tagname := 'PAYMENT_METHOD_TYPE';
422 vxmltable (vctr).tagname := 'G_PYMT_RECORD';
423 vxmltable (vctr).tagvalue := ' ';
424 vctr := vctr + 1;
426 vxmltable (vctr).tagvalue := (c_pymt_record.payment_method_type);
427 vctr := vctr + 1;
428 vxmltable (vctr).tagname := 'PAYMENT_METHOD_NAME';
429 vxmltable (vctr).tagvalue := (c_pymt_record.NAME);
430 vctr := vctr + 1;
431 vxmltable (vctr).tagname := 'SOURCE_SORT_CODE';
432 vxmltable (vctr).tagvalue := (c_pymt_record.source_sort_code);
433 vctr := vctr + 1;
434 vxmltable (vctr).tagname := 'BANK_NAME';
435 vxmltable (vctr).tagvalue := (c_pymt_record.bank_name);
436 vctr := vctr + 1;
437 vxmltable (vctr).tagname := 'BANK_BRANCH';
438 vxmltable (vctr).tagvalue := (c_pymt_record.bank_branch);
439 vctr := vctr + 1;
440 vxmltable (vctr).tagname := 'ACCOUNT_NUMBER';
441 vxmltable (vctr).tagvalue := (c_pymt_record.account_number);
442 vctr := vctr + 1;
443 vxmltable (vctr).tagname := 'ACCOUNT_NAME';
444 vxmltable (vctr).tagvalue := (c_pymt_record.account_name);
445 vctr := vctr + 1;
446 vxmltable (vctr).tagname := 'AMOUNT';
447 vxmltable (vctr).tagvalue := (c_pymt_record.amount);
448 vctr := vctr + 1;
449 vxmltable (vctr).tagname := 'TOTAL_ASSIGNMENTS_PAID';
450 vxmltable (vctr).tagvalue := (c_pymt_record.total_assignments_paid);
451 vctr := vctr + 1;
452 vxmltable (vctr).tagname := '/G_PYMT_RECORD';
453 vxmltable (vctr).tagvalue := ' ';
454 hr_utility.set_location ('TableCnt' || TO_CHAR (vxmltable.COUNT), 18);
455 END LOOP;
456
457 IF (l_master_rec IS NOT NULL)
458 THEN
459 IF (l_pymt_type IS NOT NULL)
460 THEN
461 vctr := vctr + 1;
462 vxmltable (vctr).tagname := '/G_PYMT_TYPE_RECORD';
463 vxmltable (vctr).tagvalue := ' ';
464 END IF;
465
466 vctr := vctr + 1;
467 vxmltable (vctr).tagname := '/G_PAYMENT_MASTER_RECORD';
468 vxmltable (vctr).tagvalue := ' ';
469 ELSE /*since l_master_rec is null no data found */
470 vctr := vctr + 1;
471 vxmltable (vctr).tagname := 'NO_DATA_FOUND';
472 vxmltable (vctr).tagvalue := '----- No Data Found -----';
473 END IF;
474
475 FOR c_warning IN csr_get_warning_not_all_pay
476 LOOP
477 vctr := vctr + 1;
478 vxmltable (vctr).tagname := 'NOT_ALL_PAY_WARNING';
479 vxmltable (vctr).tagvalue :=
480 c_warning.text_not_all_payroll_runs_paid;
481 EXIT;
482 END LOOP;
483
484 FOR c_warning IN csr_get_warning_prev_pay
485 LOOP
486 vctr := vctr + 1;
487 vxmltable (vctr).tagname := 'PREV_PAY_WARNING';
488 vxmltable (vctr).tagvalue :=
489 c_warning.payments_from_previous_periods;
490 EXIT;
491 END LOOP;
492
493 hr_utility.set_location ('TableCnt' || TO_CHAR (vxmltable.COUNT), 13);
494 writetoclob (p_xml);
495 END populate_pymt_summary_rep;
496 END pay_ie_pymt_summary_rpt_pkg;