[Home] [Help]
PACKAGE BODY: APPS.PAY_COSTING_SUMMARY_X_REP_PKG
Source
1 PACKAGE BODY pay_costing_summary_x_rep_pkg AS
2 /* $Header: pyprxcsr.pkb 120.3 2011/05/17 13:12:06 nchinnam ship $ */
3
4 /*************************************************************************
5 * Local Package Variables *
6 *************************************************************************/
7 gv_package_name VARCHAR2(50);
8 gtr_costing_segment costing_tab;
9 g_xml_data CLOB; --For storing the XML output data.
10
11 /******************************************************************
12 * Function for returning the optional where clause for the *
13 * cursor c_asg_costing_details *
14 *******************************************************************/
15
16 FUNCTION get_optional_where_clause(cp_payroll_id IN NUMBER
17 ,cp_consolidation_set_id IN NUMBER
18 ,cp_tax_unit_id IN NUMBER
19 ,cp_costing_process_flag VARCHAR2
20 ,cp_costing IN VARCHAR2
21 ,cp_cost_type IN VARCHAR2)
22 RETURN VARCHAR2 IS
23
24 dynamic_where_clause VARCHAR2(10000);
25
26 BEGIN
27
28 IF cp_consolidation_set_id IS NOT NULL THEN
29 dynamic_where_clause := ' AND pcd.consolidation_set_id = '||
30 to_char(cp_consolidation_set_id);
31 END IF;
32
33 IF cp_payroll_id IS NOT NULL THEN
34 dynamic_where_clause := dynamic_where_clause ||
35 ' AND pcd.payroll_id = '||
36 to_char(cp_payroll_id);
37 END IF;
38
39 IF cp_tax_unit_id IS NOT NULL THEN
40 dynamic_where_clause:= dynamic_where_clause ||
41 ' AND pcd.tax_unit_id = ' ||
42 to_char(cp_tax_unit_id);
43 END IF;
44
45 IF cp_costing_process_flag ='Y' THEN
46 dynamic_where_clause := dynamic_where_clause ||
47 ' AND pcd.payroll_action_id = ' ||
48 cp_costing;
49 END IF;
50
51 IF cp_cost_type IS NULL THEN
52 dynamic_where_clause := dynamic_where_clause ||
53 ' AND pcd.cost_type = ''COST_TMP''' ;
54 ELSIF cp_cost_type = 'EST_MODE_COST' THEN
55 dynamic_where_clause := dynamic_where_clause ||
56 ' AND pcd.cost_type IN (''COST_TMP'',
57 ''EST_COST'') ';
58 ELSIF cp_cost_type = 'EST_MODE_ALL' THEN
59 dynamic_where_clause := dynamic_where_clause ||
60 ' AND pcd.cost_type IN (''COST_TMP'',
61 ''EST_COST'',
62 ''EST_REVERSAL'') ';
63 END IF;
64
65 RETURN dynamic_where_clause;
66
67 END get_optional_where_clause;
68 --
69 /*******************************************************************
70 **This Function is used to get the template name from the template**
71 **code provided by the user. **
72 *******************************************************************/
73
74 FUNCTION GET_TEMPLATE_NAME(P_App_Short_Name VARCHAR2
75 ,P_Template_Code VARCHAR2) RETURN VARCHAR2 IS
76
77 l_template_name xdo_templates_tl.TEMPLATE_NAME%type;
78 BEGIN
79
80 l_template_name := 'Not Defined';
81 SELECT TEMPLATE_NAME
82 INTO l_template_name
83 FROM XDO_TEMPLATES_TL
84 WHERE APPLICATION_SHORT_NAME= P_App_Short_Name
85 AND TEMPLATE_CODE= P_Template_Code
86 AND LANGUAGE=userenv('LANG');
87
88 RETURN l_template_name;
89
90 EXCEPTION
91 WHEN NO_DATA_FOUND THEN
92 RETURN l_template_name;
93 END GET_TEMPLATE_NAME;
94 --
95 /********************************************************************
96 ** This Function is used to return the string of parameters **
97 ** with the tags. **
98 ********************************************************************/
99
100 FUNCTION getTaggedParameters(p_business_group VARCHAR2
101 ,p_start_date DATE
102 ,p_end_date DATE
103 ,p_costing VARCHAR2
104 ,p_payroll_name VARCHAR2
105 ,p_consolidation_set_name VARCHAR2
106 ,p_gre_name VARCHAR2
107 ,p_include_accruals VARCHAR2
108 ,p_sort_order1 VARCHAR2
109 ,p_sort_order2 VARCHAR2
110 ,p_template_name VARCHAR2
111 )RETURN VARCHAR2 IS
112 --
113 l_costing VARCHAR2(300) DEFAULT NULL;
114 --
115 BEGIN
116 --
117 IF(p_costing IS NOT NULL) THEN
118 l_costing := fnd_date.date_to_displaydate( p_start_date,calendar_aware=>fnd_date.calendar_aware_alt)||
119 '('||p_costing||')'; -- change as per bug 11830805
120 END IF;
121
122 RETURN pay_prl_xml_utils.getTag(p_tag_Name => 'C_BUSINESS_GROUP'
123 ,p_tag_value => p_business_group)||
124 pay_prl_xml_utils.getTag(p_tag_Name => 'C_STARTDATE_ICX'
125 ,p_tag_value => fnd_date.date_to_displaydate(
126 p_start_date,calendar_aware=>fnd_date.calendar_aware_alt))|| -- change as per bug 11830805
127 pay_prl_xml_utils.getTag(p_tag_Name => 'C_ENDDATE_ICX'
128 ,p_tag_value => fnd_date.date_to_displaydate(
129 p_end_date,calendar_aware=>fnd_date.calendar_aware_alt))|| -- change as per bug 11830805
130 pay_prl_xml_utils.getTag(p_tag_Name => 'C_COSTING'
131 ,p_tag_value => l_costing)||
132 pay_prl_xml_utils.getTag(p_tag_Name => 'C_PAYROLL_NAME'
133 ,p_tag_value => p_payroll_name)||
134 pay_prl_xml_utils.getTag(p_tag_Name => 'C_CONSOLIDATION_SET'
135 ,p_tag_value => p_consolidation_set_name)||
136 pay_prl_xml_utils.getTag(p_tag_Name => 'C_GRE_NAME'
137 ,p_tag_value => p_gre_name)||
138 pay_prl_xml_utils.getTag(p_tag_Name => 'C_INCLUDE_ACCRUALS'
139 ,p_tag_value => p_include_accruals)||
140 pay_prl_xml_utils.getTag(p_tag_Name => 'C_SORT_ORDER1'
141 ,p_tag_value => p_sort_order1)||
142 pay_prl_xml_utils.getTag(p_tag_Name => 'C_SORT_ORDER2'
143 ,p_tag_value => p_sort_order2)||
144 pay_prl_xml_utils.getTag(p_tag_name => 'C_TEMPLATE_NAME'
145 ,p_tag_value => get_template_name(
146 p_app_short_name => 'PAY'
147 ,p_template_code =>
148 p_template_name));
149
150 --
151 END getTaggedParameters;
152 --
153 --
154 /********************************************************************
155 ** This Procedure is used to fill the global table variable **
156 ** gtr_costing_segment with the Cost Allocation Keyflex's enabled **
157 ** segments and thier respective values. **
158 ********************************************************************/
159 --
160 PROCEDURE getNFillCostFlexSegments(p_business_group_id NUMBER)
161 IS
162 --
163 /***********************************************************
164 ** Cursor to get the Costing flex which IS setup at **
165 ** Business Group. **
166 ************************************************************/
167 --
168 CURSOR c_costing_flex_id (cp_business_group_id IN NUMBER) IS
169 SELECT org_information7
170 FROM hr_organization_information hoi
171 WHERE organization_id = cp_business_group_id
172 AND org_information_context = 'Business Group Information';
173 --
174 /************************************************************
175 ** Cursor returns all the segments defined for the Costing **
176 ** Flex which are enabled and displayed. **
177 *************************************************************/
178 CURSOR c_costing_flex_segments (cp_id_flex_num IN NUMBER) IS
179 SELECT segment_name, application_column_name
180 FROM fnd_id_flex_segments
181 WHERE id_flex_code = 'COST'
182 AND id_flex_num = cp_id_flex_num
183 AND enabled_flag = 'Y'
184 AND display_flag = 'Y'
185 ORDER BY segment_num;
186
187 ln_costing_id_flex_num NUMBER;
188 lv_segment_name VARCHAR2(100);
189 lv_column_name VARCHAR2(100);
190 ln_count NUMBER DEFAULT 0;
191
192 --
193 BEGIN
194 --
195 OPEN c_costing_flex_id (p_business_group_id);
196 FETCH c_costing_flex_id INTO ln_costing_id_flex_num;
197 IF c_costing_flex_id%found THEN
198 hr_utility.set_location(gv_package_name || '.costing_summary', 20);
199 OPEN c_costing_flex_segments (ln_costing_id_flex_num);
200 LOOP
201 FETCH c_costing_flex_segments INTO lv_segment_name, lv_column_name;
202 IF c_costing_flex_segments%NOTFOUND THEN
203 exit;
204 END IF;
205 gtr_costing_segment(ln_count).segment_label := lv_segment_name;
206 gtr_costing_segment(ln_count).column_name := lv_column_name;
207 ln_count := ln_count + 1;
208 END LOOP;
209 CLOSE c_costing_flex_segments;
210 END IF;
211 CLOSE c_costing_flex_id;
212 --
213 END getNFillCostFlexSegments;
214 --
215 --
216 /***************************************************************
217 ** Procedure: costing_summary **
218 ** **
219 ** Purpose : This procedure is the one that is called from **
220 ** the concurrent program.It's going to populate **
221 ** the report output in XML format into the **
222 ** global variable and then to the out variable. **
223 ****************************************************************/
224
225 PROCEDURE costing_summary (p_xml OUT NOCOPY CLOB
226 ,p_business_group_id IN NUMBER
227 ,p_start_date IN VARCHAR2
228 ,p_dummy_start IN VARCHAR2
229 ,p_end_date IN VARCHAR2
230 ,p_costing IN VARCHAR2
231 ,p_dummy_costing IN VARCHAR2
232 ,p_payroll_id IN NUMBER
233 ,p_consolidation_set_id IN NUMBER
234 ,p_tax_unit_id IN NUMBER
235 ,p_cost_type IN VARCHAR2
236 ,p_sort_order1 IN VARCHAR2
237 ,p_sort_order2 IN VARCHAR2
238 ,p_template_name IN VARCHAR2
239 ) IS
240 --
241 --
242 TYPE cur_type IS REF CURSOR;
243 c_asg_costing_details cur_type;
244 c_query VARCHAR2(5000);
245 c_clause1 VARCHAR2(5000);
246 --
247 /**********************************************************
248 ** CURSOR to get the Business group name **
249 ***********************************************************/
250 --
251 CURSOR c_get_organization_name (cp_organization_id IN NUMBER) IS
252 SELECT name
253 FROM hr_organization_units
254 WHERE organization_id=cp_organization_id;
255 --
256 /***********************************************************
257 ** CURSORs to get payroll,consolidation set names **
258 ***********************************************************/
259 --
260 CURSOR c_get_payroll_name (cp_payroll_id IN NUMBER) IS
261 SELECT payroll_name
262 FROM pay_payrolls_f
263 WHERE payroll_id = cp_payroll_id;
264 --
265 CURSOR c_get_consolidation_set_name (cp_consolidation_set_id IN NUMBER) IS
266 SELECT consolidation_set_name
267 FROM pay_consolidation_sets
268 WHERE consolidation_set_id=cp_consolidation_set_id;
269 --
270 /***********************************************************
271 ** CURSOR to get effective date for a payroll action id **
272 ************************************************************/
273 --
274 CURSOR c_get_effective_date(cp_payroll_action_id IN NUMBER) IS
275 SELECT effective_date
276 FROM pay_payroll_actions
277 WHERE payroll_action_id=cp_payroll_action_id;
278 --
279 CURSOR c_get_accruals(cp_cost_type IN VARCHAR2) IS
280 SELECT nvl(hr_general.decode_lookup('PAY_PAYRPCBR',cp_cost_type),' ')
281 FROM dual;
282 --
283
284 /************************************************************
285 ** Cursor returns the session id **
286 *************************************************************/
287 --
288 CURSOR c_get_session_id IS
289 SELECT userenv('sessionid')
290 FROM dual;
291
292 /************************************************************
293 ** Cursor returns payroll/gre totals **
294 ************************************************************/
295 --
296 CURSOR c_costing_summary_rpt_details (cp_session_id IN NUMBER
297 ,cp_business_group_id IN NUMBER
298 ,cp_csr IN VARCHAR2
299 ,cp_sort_order1 IN VARCHAR2
300 ,cp_sort_order2 IN VARCHAR2) IS
301 SELECT decode(upper(cp_sort_order1),'PAYROLL NAME',attribute32
302 ,gre_name)
303 ,attribute34 --UOM
304 ,sum(value1)
305 ,sum(value2)
306 ,attribute1
310 ,attribute5
307 ,attribute2
308 ,attribute3
309 ,attribute4
311 ,attribute6
312 ,attribute7
313 ,attribute8
314 ,attribute9
315 ,attribute10
316 ,attribute11
317 ,attribute12
318 ,attribute13
319 ,attribute14
320 ,attribute15
321 ,attribute16
322 ,attribute17
323 ,attribute18
324 ,attribute19
325 ,attribute20
326 ,attribute21
327 ,attribute22
328 ,attribute23
329 ,attribute24
330 ,attribute25
331 ,attribute26
332 ,attribute27
333 ,attribute28
334 ,attribute29
335 ,attribute30
336 FROM pay_us_rpt_totals
337 WHERE business_group_id=cp_business_group_id
338 and attribute31=cp_csr
339 and session_id=cp_session_id
340 GROUP BY decode(upper(cp_sort_order1), 'PAYROLL NAME',attribute32,
341 gre_name)
342 ,attribute1
343 ,attribute2
344 ,attribute3
345 ,attribute4
346 ,attribute5
347 ,attribute6
348 ,attribute7
349 ,attribute8
350 ,attribute9
351 ,attribute10
352 ,attribute11
353 ,attribute12
354 ,attribute13
355 ,attribute14
356 ,attribute15
357 ,attribute16
358 ,attribute17
359 ,attribute18
360 ,attribute19
361 ,attribute20
362 ,attribute21
363 ,attribute22
364 ,attribute23
365 ,attribute24
366 ,attribute25
367 ,attribute26
368 ,attribute27
369 ,attribute28
370 ,attribute29
371 ,attribute30
372 ,attribute34
373 order by
374 attribute1
375 ,attribute2
376 ,attribute3
377 ,attribute4
378 ,attribute5
379 ,attribute6
380 ,attribute7
381 ,attribute8
382 ,attribute9
383 ,attribute10
384 ,attribute11
385 ,attribute12
386 ,attribute13
387 ,attribute14
388 ,attribute15
389 ,attribute16
390 ,attribute17
391 ,attribute18
392 ,attribute19
393 ,attribute20
394 ,attribute21
395 ,attribute22
396 ,attribute23
397 ,attribute24
398 ,attribute25
399 ,attribute26
400 ,attribute27
401 ,attribute28
402 ,attribute29
403 ,attribute30
404 ,attribute34
405 ;
406 --
407 /************************************************************
408 ** Cursor returns grand totals **
409 ************************************************************/
410 --
411 CURSOR c_costing_grand_totals (cp_session_id IN NUMBER
412 ,cp_business_group_id IN NUMBER
413 ,cp_csr IN VARCHAR2
414 ) IS
415 SELECT attribute34 --UOM
416 ,sum(value1)
417 ,sum(value2)
418 ,attribute1
419 ,attribute2
420 ,attribute3
421 ,attribute4
422 ,attribute5
423 ,attribute6
424 ,attribute7
425 ,attribute8
426 ,attribute9
427 ,attribute10
428 ,attribute11
429 ,attribute12
430 ,attribute13
431 ,attribute14
432 ,attribute15
433 ,attribute16
434 ,attribute17
435 ,attribute18
436 ,attribute19
437 ,attribute20
438 ,attribute21
439 ,attribute22
440 ,attribute23
441 ,attribute24
442 ,attribute25
443 ,attribute26
444 ,attribute27
445 ,attribute28
446 ,attribute29
447 ,attribute30
448 FROM pay_us_rpt_totals
449 WHERE business_group_id=cp_business_group_id
450 AND attribute31=cp_csr
451 AND session_id=cp_session_id
452 GROUP BY
453 attribute1
454 ,attribute2
455 ,attribute3
456 ,attribute4
457 ,attribute5
458 ,attribute6
459 ,attribute7
460 ,attribute8
461 ,attribute9
462 ,attribute10
463 ,attribute11
464 ,attribute12
465 ,attribute13
466 ,attribute14
467 ,attribute15
468 ,attribute16
469 ,attribute17
470 ,attribute18
471 ,attribute19
472 ,attribute20
473 ,attribute21
474 ,attribute22
475 ,attribute23
476 ,attribute24
477 ,attribute25
478 ,attribute26
479 ,attribute27
480 ,attribute28
481 ,attribute29
482 ,attribute30
483 ,attribute34
484 order by
485 attribute1
486 ,attribute2
487 ,attribute3
488 ,attribute4
489 ,attribute5
490 ,attribute6
491 ,attribute7
492 ,attribute8
493 ,attribute9
494 ,attribute10
495 ,attribute11
496 ,attribute12
497 ,attribute13
498 ,attribute14
499 ,attribute15
500 ,attribute16
501 ,attribute17
502 ,attribute18
503 ,attribute19
504 ,attribute20
505 ,attribute21
506 ,attribute22
507 ,attribute23
508 ,attribute24
509 ,attribute25
510 ,attribute26
511 ,attribute27
512 ,attribute28
513 ,attribute29
514 ,attribute30
515 ,attribute34
516 ;
517
518
519 /**************************************************************
520 **Cursor to get GRE/Payroll totals **
521 ***************************************************************/
522 --
523 CURSOR c_get_gre_or_payroll_totals(cp_session_id IN NUMBER
524 ,cp_business_group_id IN NUMBER
525 ,cp_total_flag IN VARCHAR2
526 ,cp_sort_order1 IN VARCHAR2
527 ) IS
528 SELECT decode(upper(cp_sort_order1), 'PAYROLL NAME',attribute32,
529 gre_name)
530 ,attribute34 --UOM
531 ,sum(value1)
532 ,sum(value2)
533 FROM pay_us_rpt_totals
534 WHERE session_id=cp_session_id
535 AND business_group_id=cp_business_group_id
536 AND attribute31=cp_total_flag
537 GROUP BY decode(upper(cp_sort_order1), 'PAYROLL NAME',attribute32,
538 gre_name)
539 ,attribute34;
540 --
541 /**************************************************************
542 **CURSOR to get report total **
543 ***************************************************************/
544 CURSOR c_get_report_totals (cp_session_id IN NUMBER
545 ,cp_business_group_id IN NUMBER
546 ,cp_total_flag IN VARCHAR2
547 ) IS
548 SELECT attribute34 --UOM
549 ,sum(value1)
550 ,sum(value2)
551 FROM pay_us_rpt_totals
552 WHERE session_id=cp_session_id
553 AND business_group_id=cp_business_group_id
554 AND attribute31=cp_total_flag
555 GROUP BY attribute34;
556 --
557 /*************************************************************
558 ** Local Variables **
559 **************************************************************/
560 lv_consolidation_set_name VARCHAR2(100);
561 lv_business_group_name VARCHAR2(100);
562 lv_payroll_name VARCHAR2(100);
563 lv_gre_name VARCHAR2(240);
564 lv_input_value_name VARCHAR2(100);
565 lv_uom VARCHAR2(100);
566 ln_credit_amount NUMBER;
567 ln_debit_amount NUMBER;
568 lv_effective_date DATE;
569 lv_concatenated_segments VARCHAR2(200);
570 lv_segment1 VARCHAR2(200);
571 lv_segment2 VARCHAR2(200);
572 lv_segment3 VARCHAR2(200);
573 lv_segment4 VARCHAR2(200);
574 lv_segment5 VARCHAR2(200);
575 lv_segment6 VARCHAR2(200);
576 lv_segment7 VARCHAR2(200);
577 lv_segment8 VARCHAR2(200);
578 lv_segment9 VARCHAR2(200);
579 lv_segment10 VARCHAR2(200);
580 lv_segment11 VARCHAR2(200);
581 lv_segment12 VARCHAR2(200);
582 lv_segment13 VARCHAR2(200);
583 lv_segment14 VARCHAR2(200);
584 lv_segment15 VARCHAR2(200);
585 lv_segment16 VARCHAR2(200);
586 lv_segment17 VARCHAR2(200);
587 lv_segment18 VARCHAR2(200);
588 lv_segment19 VARCHAR2(200);
589 lv_segment20 VARCHAR2(200);
590 lv_segment21 VARCHAR2(200);
591 lv_segment22 VARCHAR2(200);
592 lv_segment23 VARCHAR2(200);
593 lv_segment24 VARCHAR2(200);
594 lv_segment25 VARCHAR2(200);
595 lv_segment26 VARCHAR2(200);
596 lv_segment27 VARCHAR2(200);
597 lv_segment28 VARCHAR2(200);
598 lv_segment29 VARCHAR2(200);
599 lv_segment30 VARCHAR2(200);
600
601 lv_segment_value VARCHAR2(100);
602 lv_column_name VARCHAR2(100);
603
604 lv_accrual_type VARCHAR2(100);
605 lv_cost_mode VARCHAR2(100);
606
607 lv_gre_or_payroll VARCHAR2(240);
608 lv_session_id NUMBER;
609 lv_credit_sum NUMBER;
610 lv_debit_sum NUMBER;
611
612 lv_start_date DATE;
613 lv_END_date DATE;
614 lv_costing_process_flag VARCHAR2(1);
615 lv_include_accruals VARCHAR2(100);
616
617 l_tag VARCHAR2(2000);
618 l_count NUMBER;
619
620 lv_tagged_parameters VARCHAR2(2000);
621 lv_tableHeadings VARCHAR2(1000);
622 lv_total_heading VARCHAR2(240);
623 lv_csr_heading VARCHAR2(240);
624
625 l_nodata_flag BOOLEAN DEFAULT TRUE;
626 l_rec_count NUMBER DEFAULT 0;
627
628 --
629 BEGIN
630 --
631 gv_package_name :='pay_costing_summary_x_rep_pkg';
632 hr_utility.set_location(gv_package_name || '.costing_summary', 10);
633 hr_utility.trace('Start Date = ' || p_start_date);
634 hr_utility.trace('End Date = ' || p_END_date);
635 hr_utility.trace('Business Group ID = '|| p_business_group_id);
636 hr_utility.trace('Costing Process = ' || p_costing);
637 hr_utility.trace('Payroll ID = ' || p_payroll_id);
638 hr_utility.trace('Consolidation Set ID = ' || p_consolidation_set_id);
639 hr_utility.trace('Tax unit ID = ' || p_tax_unit_id);
640 hr_utility.trace('Cost Type = ' || p_cost_type);
641 hr_utility.trace('Sort Order 1 = ' || p_sort_order1);
642 hr_utility.trace('Sort Order 2 = ' || p_sort_order2);
643
644 lv_costing_process_flag := 'N';
645 --Clearing the PL/SQL table of package pay_prl_xml_utils
646 pay_prl_xml_utils.gXMLTable.DELETE;
647 --
648 fnd_file.put_line(fnd_file.log,'Creating the XML...');
649
650 --Creating a CLOB and opening the CLOB.
651 DBMS_LOB.CREATETEMPORARY(g_xml_data,FALSE,DBMS_LOB.CALL);
652 DBMS_LOB.OPEN(g_xml_data,dbms_lob.lob_readwrite);
653 --
654 l_tag :='<?xml version="1.0" encoding="UTF-8"?>';
655 DBMS_LOB.WRITEAPPEND(g_xml_data, length(l_tag), l_tag);
656 --
657 l_tag := '<PAYRPCSR>';
658 DBMS_LOB.WRITEAPPEND(g_xml_data, length(l_tag), l_tag);
659 fnd_file.put_line(fnd_file.log,'Started...');
660 --
661 --Fill the Global PL/SQL table with the CostAllocation Keyflex's
662 --enabled segments and their corresponding values.
663 getNFillCostFlexSegments(p_business_group_id);
664 --
665 OPEN c_get_organization_name(p_business_group_id);
666 FETCH c_get_organization_name INTO lv_business_group_name;
667 CLOSE c_get_organization_name;
668
669 OPEN c_get_organization_name(p_tax_unit_id);
670 FETCH c_get_organization_name INTO lv_gre_name;
671 CLOSE c_get_organization_name;
672
673 OPEN c_get_payroll_name(p_payroll_id);
674 FETCH c_get_payroll_name INTO lv_payroll_name;
675 CLOSE c_get_payroll_name;
676
677 OPEN c_get_consolidation_set_name(p_consolidation_set_id);
678 FETCH c_get_consolidation_set_name INTO lv_consolidation_set_name;
679 CLOSE c_get_consolidation_set_name;
680
681 hr_utility.set_location(gv_package_name || '.costing_summary', 30);
682
683 lv_include_accruals:= nvl(hr_general.decode_lookup('PAY_PAYRPCBR',
684 p_cost_type),' ');
685
686 IF p_costing IS NOT NULL THEN
687 OPEN c_get_effective_date(TO_NUMBER(p_costing));
688 FETCH c_get_effective_date INTO lv_start_date;
689 CLOSE c_get_effective_date;
690 lv_END_date := lv_start_date;
691 lv_costing_process_flag:='Y';
692 hr_utility.trace('lv_start_date'|| lv_start_date);
693 ELSE
694 lv_start_date:=to_date(p_start_date, 'YYYY/MM/DD HH24:MI:SS');
695 lv_END_date:=to_date(p_END_date, 'YYYY/MM/DD HH24:MI:SS');
696 lv_costing_process_flag:='N';
697 END IF;
698
699 lv_tagged_parameters := getTaggedParameters(
700 p_business_group => lv_business_group_name
701 ,p_start_date =>lv_start_date
702 ,p_end_date =>lv_END_date
703 ,p_costing =>p_costing
704 ,p_payroll_name =>lv_payroll_name
705 ,p_consolidation_set_name
706 =>lv_consolidation_set_name
707 ,p_gre_name =>lv_gre_name
708 ,p_include_accruals=>lv_include_accruals
709 ,p_sort_order1 =>p_sort_order1
710 ,p_sort_order2 =>p_sort_order2
711 ,p_template_name =>p_template_name
712 );
713 /*Finding the headings of the tables*/
714 IF p_sort_order1='Payroll Name' THEN
715 lv_csr_heading :='Costing Summary Report - Payroll Totals';
716 lv_total_heading := 'Payroll Totals';
717 ELSE
718 lv_csr_heading :='Costing Summary Report - GRE Totals';
719 lv_total_heading := 'GRE Totals';
720 END IF;
721
722 lv_tableHeadings := pay_prl_xml_utils.getTag('CSR_GRE_OR_PAYROLL_HEADING',
723 lv_csr_heading)||
724 pay_prl_xml_utils.getTag('GRE_OR_PAYROLL_TOTAL_HEADING',
725 lv_total_heading);
726 lv_tagged_parameters := lv_tagged_parameters || lv_tableHeadings;
727
728 /***********************************************************************
729 * The following code is for populating the CLOB with the data of the *
730 * Costing details of payrolls. *
731 ***********************************************************************/
732 --
733 l_tag := '<LIST_G_ASG_COSTING_DETAILS>';
734 DBMS_LOB.WRITEAPPEND(g_xml_data, length(l_tag), l_tag);
735 --
736 OPEN c_get_session_id;
737 FETCH c_get_session_id INTO lv_session_id;
738 CLOSE c_get_session_id;
739 --
740 -- Get the WHERE CLAUSE depending upon the parameters provided.
741 c_clause1:=get_optional_where_clause(p_payroll_id,
742 p_consolidation_set_id,
743 p_tax_unit_id,
744 lv_costing_process_flag,
745 p_costing,
746 p_cost_type);
747
748 --Construct the Query depending on the WHERE CLAUSE.
749 c_query := 'SELECT
750 pcd.payroll_name
751 ,pcd.gre_name
752 ,pcd.input_value_name
753 ,pcd.uom
754 ,sum(pcd.credit_amount)
755 ,sum(pcd.debit_amount)
756 ,pcd.cost_type
757 ,pcd.concatenated_segments
758 ,pcd.segment1
759 ,pcd.segment2
760 ,pcd.segment3
761 ,pcd.segment4
762 ,pcd.segment5
763 ,pcd.segment6
764 ,pcd.segment7
765 ,pcd.segment8
766 ,pcd.segment9
767 ,pcd.segment10
768 ,pcd.segment11
769 ,pcd.segment12
770 ,pcd.segment13
771 ,pcd.segment14
772 ,pcd.segment15
773 ,pcd.segment16
774 ,pcd.segment17
775 ,pcd.segment18
776 ,pcd.segment19
777 ,pcd.segment20
778 ,pcd.segment21
779 ,pcd.segment22
780 ,pcd.segment23
781 ,pcd.segment24
782 ,pcd.segment25
783 ,pcd.segment26
784 ,pcd.segment27
785 ,pcd.segment28
786 ,pcd.segment29
787 ,pcd.segment30
788 FROM pay_costing_details_v pcd
789 WHERE
790 pcd.effective_date between :cp_start_date and :cp_end_date
791 ' || c_clause1 || '
792 and pcd.business_group_id = :cp_business_group_id
793 GROUP BY pcd.payroll_name,pcd.gre_name
794 ,pcd.input_value_name
795 ,pcd.uom,pcd.cost_type
796 ,pcd.concatenated_segments
797 ,pcd.segment1
798 ,pcd.segment2
799 ,pcd.segment3
800 ,pcd.segment4
801 ,pcd.segment5
802 ,pcd.segment6
803 ,pcd.segment7
804 ,pcd.segment8
805 ,pcd.segment9
806 ,pcd.segment10
807 ,pcd.segment11
808 ,pcd.segment12
809 ,pcd.segment13
810 ,pcd.segment14
811 ,pcd.segment15
812 ,pcd.segment16
813 ,pcd.segment17
814 ,pcd.segment18
815 ,pcd.segment19
816 ,pcd.segment20
817 ,pcd.segment21
818 ,pcd.segment22
819 ,pcd.segment23
820 ,pcd.segment24
821 ,pcd.segment25
822 ,pcd.segment26
823 ,pcd.segment27
824 ,pcd.segment28
825 ,pcd.segment29
826 ,pcd.segment30
827 ORDER BY pcd.cost_type
828 ,decode (upper(:cp_sort_order1), ''PAYROLL NAME'', pcd.payroll_name,
829 pcd.gre_name)
830 ,decode(upper(:cp_sort_order2), ''GRE'', pcd.gre_name,''PAYROLL NAME'',
831 pcd.payroll_name,''X'')
832 ,pcd.segment1
833 ,pcd.segment2
834 ,pcd.segment3
835 ,pcd.segment4
836 ,pcd.segment5
837 ,pcd.segment6
838 ,pcd.segment7
839 ,pcd.segment8
840 ,pcd.segment9
841 ,pcd.segment10
842 ,pcd.segment11
843 ,pcd.segment12
844 ,pcd.segment13
845 ,pcd.segment14
846 ,pcd.segment15
847 ,pcd.segment16
848 ,pcd.segment17
849 ,pcd.segment18
850 ,pcd.segment19
851 ,pcd.segment20
852 ,pcd.segment21
853 ,pcd.segment22
854 ,pcd.segment23
855 ,pcd.segment24
856 ,pcd.segment25
857 ,pcd.segment26
858 ,pcd.segment27
859 ,pcd.segment28
860 ,pcd.segment29
861 ,pcd.segment30';
862 hr_utility.trace('Query is : '||c_query );
863 --
864 --Opening the REFCURSOR for getting and populating into the XML variable.
865 OPEN c_asg_costing_details
866 FOR c_query USING TO_DATE(NVL(p_start_date,'0001/01/01 00:00:00'), 'YYYY/MM/DD HH24:MI:SS')
867 ,TO_DATE(nvl(p_end_date,'4712/12/31 00:00:00'), 'YYYY/MM/DD HH24:MI:SS')
868 ,p_business_group_id
869 ,p_sort_order1
870 ,p_sort_order2;
871 LOOP
872 FETCH c_asg_costing_details into
873 lv_payroll_name
874 ,lv_gre_name
875 ,lv_input_value_name
876 ,lv_uom
877 ,ln_credit_amount
878 ,ln_debit_amount
879 ,lv_cost_mode
880 ,lv_concatenated_segments
881 ,lv_segment1
882 ,lv_segment2
883 ,lv_segment3
884 ,lv_segment4
885 ,lv_segment5
886 ,lv_segment6
887 ,lv_segment7
888 ,lv_segment8
889 ,lv_segment9
890 ,lv_segment10
891 ,lv_segment11
892 ,lv_segment12
893 ,lv_segment13
894 ,lv_segment14
895 ,lv_segment15
896 ,lv_segment16
897 ,lv_segment17
898 ,lv_segment18
899 ,lv_segment19
900 ,lv_segment20
901 ,lv_segment21
902 ,lv_segment22
903 ,lv_segment23
904 ,lv_segment24
905 ,lv_segment25
906 ,lv_segment26
907 ,lv_segment27
908 ,lv_segment28
909 ,lv_segment29
910 ,lv_segment30;
911
912 IF c_asg_costing_details%NOTFOUND THEN
913 hr_utility.set_location(gv_package_name || '.costing_summary', 60);
914
915 --If no data is returned from the cursor then need to populate the
916 --XML data with no values.
917 IF(l_nodata_flag = TRUE) THEN
918 lv_tagged_parameters := lv_tagged_parameters||
919 pay_prl_xml_utils.getTag(
920 'C_G_ASG_COSTING_DETAILS_NODATA',
921 '1');
922 l_tag := '<G_ASG_COSTING_DETAILS>';
923
924 l_tag := l_tag||
925 pay_prl_xml_utils.getTag('PAYROLL_NAME',NULL)||
926 pay_prl_xml_utils.getTag('GRE_NAME', NULL)||
927 pay_prl_xml_utils.getTag('INPUT_VALUE_NAME',
928 NULL)||
929 pay_prl_xml_utils.getTag('UOM',NULL)||
930 pay_prl_xml_utils.getTag('CREDIT_AMOUNT',NULL)||
931 pay_prl_xml_utils.getTag('DEBIT_AMOUNT',NULL)||
932 pay_prl_xml_utils.getTag('ACCRUAL_TYPE', NULL);
933
934 --Need to provide the Heading of the columns eventhough no data
935 --found.So here it's going to fill the Column heading data.
936 FOR i IN gtr_costing_segment.first .. gtr_costing_segment.last LOOP
937 l_tag:= l_tag||'<G_ASG_COSTING_DETAILS_SEGMENT>'||
938 pay_prl_xml_utils.getTag('SEGMENT',INITCAP(
939 gtr_costing_segment(i).segment_label))||
940 pay_prl_xml_utils.getTag('VALUE',NULL)||
941 '</G_ASG_COSTING_DETAILS_SEGMENT>';
942 END LOOP;
943 l_tag :=l_tag||'</G_ASG_COSTING_DETAILS>';
944 DBMS_LOB.WRITEAPPEND(g_xml_data, length(l_tag), l_tag);
945 END IF;
946 EXIT;
947 END IF;
948 --
949 --If no data is there, then need to fill one NodataFound Flag
950 -- in the XML output.
951 l_nodata_flag := FALSE;
952 IF(l_rec_count = 0) THEN
953 lv_tagged_parameters := lv_tagged_parameters||
954 pay_prl_xml_utils.getTag(
955 'C_G_ASG_COSTING_DETAILS_NODATA',
956 '0');
957 END IF;
958 l_rec_count := l_rec_count + 1;
959 lv_accrual_type:=nvl(hr_general.decode_lookup('PAY_PAYRPCBR',lv_cost_mode),' ');
960 /*insert into pay_us_rpt_totals*/
961 insert into pay_us_rpt_totals(session_id,business_group_id,gre_name,value1 ,value2 ,attribute1,attribute2
962 ,attribute3,attribute4,attribute5,attribute6,attribute7,attribute8
963 ,attribute9,attribute10,attribute11,attribute12,attribute13
964 ,attribute14,attribute15,attribute16,attribute17,attribute18
965 ,attribute19,attribute20,attribute21,attribute22,attribute23
966 ,attribute24,attribute25,attribute26,attribute27,attribute28
967 ,attribute29,attribute30,attribute31,attribute32,attribute33
968 ,attribute34) values
969 (lv_session_id -- session ID is passed
970 ,p_business_group_id
971 ,lv_gre_name
972 ,ln_credit_amount
973 ,ln_debit_amount
974 ,lv_segment1
975 ,lv_segment2
976 ,lv_segment3
977 ,lv_segment4
978 ,lv_segment5
979 ,lv_segment6
980 ,lv_segment7
981 ,lv_segment8
982 ,lv_segment9
983 ,lv_segment10
984 ,lv_segment11
985 ,lv_segment12
986 ,lv_segment13
987 ,lv_segment14
988 ,lv_segment15
989 ,lv_segment16
990 ,lv_segment17
991 ,lv_segment18
992 ,lv_segment19
993 ,lv_segment20
994 ,lv_segment21
995 ,lv_segment22
996 ,lv_segment23
997 ,lv_segment24
998 ,lv_segment25
999 ,lv_segment26
1000 ,lv_segment27
1001 ,lv_segment28
1002 ,lv_segment29
1003 ,lv_segment30
1004 ,'CSR' --attribute31 -- denotes that the record is for Costing Summary Report
1005 ,lv_payroll_name --attribute32
1006 ,lv_concatenated_segments --attribute33
1007 ,lv_uom --attribute34
1008 );
1009
1010
1011 hr_utility.set_location(gv_package_name || '.costing_summary', 70);
1012
1013 --Filling the data along with the XML tags into the local XML
1014 --data variable.
1015 l_tag := '<G_ASG_COSTING_DETAILS>';
1016
1017 l_tag := l_tag||
1018 pay_prl_xml_utils.getTag('PAYROLL_NAME',lv_payroll_name)||
1019 pay_prl_xml_utils.getTag('GRE_NAME', lv_gre_name)||
1020 pay_prl_xml_utils.getTag('INPUT_VALUE_NAME',
1021 lv_input_value_name)||
1022 pay_prl_xml_utils.getTag('UOM',lv_uom)||
1023 pay_prl_xml_utils.getTag('CREDIT_AMOUNT',ln_credit_amount)||
1024 pay_prl_xml_utils.getTag('DEBIT_AMOUNT',ln_debit_amount)||
1025 pay_prl_xml_utils.getTag('ACCRUAL_TYPE', lv_accrual_type);
1026
1027 DBMS_LOB.WRITEAPPEND(g_xml_data, length(l_tag), l_tag);
1028
1029 l_count := 1;
1030
1031 --Filling the Cost Allocation KFF Values into the XML data Variable.
1032 FOR i IN gtr_costing_segment.first .. gtr_costing_segment.last LOOP
1033 IF gtr_costing_segment(i).column_name = 'SEGMENT1' THEN
1034 lv_segment_value := lv_segment1;
1035 elsIF gtr_costing_segment(i).column_name = 'SEGMENT2' THEN
1036 lv_segment_value := lv_segment2;
1037 elsIF gtr_costing_segment(i).column_name = 'SEGMENT3' THEN
1038 lv_segment_value := lv_segment3;
1039 elsIF gtr_costing_segment(i).column_name = 'SEGMENT4' THEN
1040 lv_segment_value := lv_segment4;
1041 elsIF gtr_costing_segment(i).column_name = 'SEGMENT5' THEN
1042 lv_segment_value := lv_segment5;
1043 elsIF gtr_costing_segment(i).column_name = 'SEGMENT6' THEN
1044 lv_segment_value := lv_segment6;
1045 elsIF gtr_costing_segment(i).column_name = 'SEGMENT7' THEN
1046 lv_segment_value := lv_segment7;
1047 elsIF gtr_costing_segment(i).column_name = 'SEGMENT8' THEN
1048 lv_segment_value := lv_segment8;
1049 elsIF gtr_costing_segment(i).column_name = 'SEGMENT9' THEN
1050 lv_segment_value := lv_segment9;
1051 elsIF gtr_costing_segment(i).column_name = 'SEGMENT10' THEN
1052 lv_segment_value := lv_segment10;
1053 elsIF gtr_costing_segment(i).column_name = 'SEGMENT11' THEN
1054 lv_segment_value := lv_segment11;
1055 elsIF gtr_costing_segment(i).column_name = 'SEGMENT12' THEN
1056 lv_segment_value := lv_segment12;
1057 elsIF gtr_costing_segment(i).column_name = 'SEGMENT13' THEN
1058 lv_segment_value := lv_segment13;
1059 elsIF gtr_costing_segment(i).column_name = 'SEGMENT14' THEN
1060 lv_segment_value := lv_segment14;
1061 elsIF gtr_costing_segment(i).column_name = 'SEGMENT15' THEN
1062 lv_segment_value := lv_segment15;
1063 elsIF gtr_costing_segment(i).column_name = 'SEGMENT16' THEN
1064 lv_segment_value := lv_segment16;
1065 elsIF gtr_costing_segment(i).column_name = 'SEGMENT17' THEN
1066 lv_segment_value := lv_segment17;
1067 elsIF gtr_costing_segment(i).column_name = 'SEGMENT18' THEN
1068 lv_segment_value := lv_segment18;
1069 elsIF gtr_costing_segment(i).column_name = 'SEGMENT19' THEN
1070 lv_segment_value := lv_segment19;
1071 elsIF gtr_costing_segment(i).column_name = 'SEGMENT20' THEN
1072 lv_segment_value := lv_segment20;
1073 elsIF gtr_costing_segment(i).column_name = 'SEGMENT21' THEN
1074 lv_segment_value := lv_segment21;
1075 elsIF gtr_costing_segment(i).column_name = 'SEGMENT22' THEN
1076 lv_segment_value := lv_segment22;
1077 elsIF gtr_costing_segment(i).column_name = 'SEGMENT23' THEN
1078 lv_segment_value := lv_segment23;
1079 elsIF gtr_costing_segment(i).column_name = 'SEGMENT24' THEN
1080 lv_segment_value := lv_segment24;
1081 elsIF gtr_costing_segment(i).column_name = 'SEGMENT25' THEN
1082 lv_segment_value := lv_segment25;
1083 elsIF gtr_costing_segment(i).column_name = 'SEGMENT26' THEN
1084 lv_segment_value := lv_segment26;
1085 elsIF gtr_costing_segment(i).column_name = 'SEGMENT27' THEN
1086 lv_segment_value := lv_segment27;
1087 elsIF gtr_costing_segment(i).column_name = 'SEGMENT28' THEN
1088 lv_segment_value := lv_segment28;
1089 elsIF gtr_costing_segment(i).column_name = 'SEGMENT29' THEN
1090 lv_segment_value := lv_segment29;
1091 elsIF gtr_costing_segment(i).column_name = 'SEGMENT30' THEN
1092 lv_segment_value := lv_segment30;
1093 END IF;
1094
1095 pay_prl_xml_utils.gXMLTable(l_count).Name := INITCAP(
1096 gtr_costing_segment(i).segment_label);
1097 pay_prl_xml_utils.gXMLTable(l_count).Value := lv_segment_value;
1098
1099 l_count := l_count + 1;
1100
1101 END LOOP ;
1102
1103 pay_prl_xml_utils.twoColumnar(p_type => 'G_ASG_COSTING_DETAILS_SEGMENT'
1104 ,p_data => pay_prl_xml_utils.gXMLTable
1105 ,p_count => l_count
1106 ,p_xml_data => g_xml_data);
1107 pay_prl_xml_utils.gXMLTable.delete;
1108
1109 l_tag := '</G_ASG_COSTING_DETAILS>';
1110
1111 --Appending the XML data into the Global XML data variable.
1112 DBMS_LOB.WRITEAPPEND(g_xml_data, length(l_tag), l_tag);
1113
1114 END LOOP;
1115 CLOSE c_asg_costing_details;
1116
1117 l_tag := '</LIST_G_ASG_COSTING_DETAILS>';
1118 DBMS_LOB.WRITEAPPEND(g_xml_data, length(l_tag), l_tag);
1119
1120 /********************************************************************
1121 * This code is for populating the XML with the Costing Summary *
1122 * Report for GRE/Payroll Depends on the parameter. *
1123 ********************************************************************/
1124
1125 pay_prl_xml_utils.gXMLTable.delete;
1126
1127 l_nodata_flag := TRUE;
1128 l_rec_count := 0;
1129
1130 l_tag := '<LIST_G_CSR_GRE_OR_PRL_TOTAL>';
1131 DBMS_LOB.WRITEAPPEND(g_xml_data, length(l_tag), l_tag);
1132
1133
1134 --Opening the Cursor for the data of the Second table
1135 --i.e., Costing Summary Details Table
1136 OPEN c_costing_summary_rpt_details (lv_session_id
1137 ,p_business_group_id
1138 ,'CSR'
1139 ,p_sort_order1
1140 ,p_sort_order2
1141 );
1142 LOOP
1143 FETCH c_costing_summary_rpt_details into
1144 lv_gre_or_payroll
1145 ,lv_uom
1146 ,ln_credit_amount
1147 ,ln_debit_amount
1148 ,lv_segment1
1149 ,lv_segment2
1150 ,lv_segment3
1151 ,lv_segment4
1152 ,lv_segment5
1153 ,lv_segment6
1154 ,lv_segment7
1158 ,lv_segment11
1155 ,lv_segment8
1156 ,lv_segment9
1157 ,lv_segment10
1159 ,lv_segment12
1160 ,lv_segment13
1161 ,lv_segment14
1162 ,lv_segment15
1163 ,lv_segment16
1164 ,lv_segment17
1165 ,lv_segment18
1166 ,lv_segment19
1167 ,lv_segment20
1168 ,lv_segment21
1169 ,lv_segment22
1170 ,lv_segment23
1171 ,lv_segment24
1172 ,lv_segment25
1173 ,lv_segment26
1174 ,lv_segment27
1175 ,lv_segment28
1176 ,lv_segment29
1177 ,lv_segment30;
1178 IF c_costing_summary_rpt_details % NOTFOUND THEN
1179 hr_utility.set_location(gv_package_name || '.costing_summary', 100);
1180
1181 --If no data is returned from the cursor then need to populate the
1182 --XML data with no values.
1183 IF(l_nodata_flag = TRUE) THEN
1184
1185 lv_tagged_parameters := lv_tagged_parameters||
1186 pay_prl_xml_utils.getTag(
1187 'C_G_CSR_GRE_OR_PRL_TOTAL_NODATA',
1188 '1');
1189 l_tag := '<G_CSR_GRE_OR_PRL_TOTAL>';
1190 l_tag := l_tag||
1191 pay_prl_xml_utils.getTag('GRE_OR_PAYROLL',NULL)||
1192 pay_prl_xml_utils.getTag('UOM',NULL)||
1193 pay_prl_xml_utils.getTag('CREDIT_AMOUNT',NULL)||
1194 pay_prl_xml_utils.getTag('DEBIT_AMOUNT',NULL);
1195
1196 --Need to provide the Heading of the columns eventhough no data
1197 --found.So here it's going to fill the Column heading data.
1198 FOR i IN gtr_costing_segment.first .. gtr_costing_segment.last LOOP
1199 l_tag := l_tag ||'<G_CSR_GRE_OR_PRL_TOTAL_SEGMENT>'||
1200 pay_prl_xml_utils.getTag('SEGMENT',INITCAP(
1201 gtr_costing_segment(i).segment_label))||
1202 pay_prl_xml_utils.getTag('VALUE',NULL)||
1203 '</G_CSR_GRE_OR_PRL_TOTAL_SEGMENT>';
1204 END LOOP;
1205
1206 l_tag :=l_tag ||'</G_CSR_GRE_OR_PRL_TOTAL>';
1207
1208 DBMS_LOB.WRITEAPPEND(g_xml_data, length(l_tag), l_tag);
1209 END IF;
1210
1211 EXIT;
1212 END IF;
1213
1214 --If no data is there, then need to fill one NodataFound Flag
1215 -- in the XML output.
1216 l_nodata_flag := FALSE;
1217 IF(l_rec_count = 0)THEN
1218 lv_tagged_parameters := lv_tagged_parameters||
1219 pay_prl_xml_utils.getTag(
1220 'C_G_CSR_GRE_OR_PRL_TOTAL_NODATA',
1221 '0');
1222 END IF;
1223 l_rec_count := l_rec_count + 1;
1224
1225 --Filling the data along with the XML tags into the local XML
1226 --data variable.
1227 l_tag := '<G_CSR_GRE_OR_PRL_TOTAL>';
1228
1229 l_tag := l_tag||
1230 pay_prl_xml_utils.getTag('GRE_OR_PAYROLL',lv_gre_or_payroll)||
1231 pay_prl_xml_utils.getTag('UOM',lv_uom)||
1232 pay_prl_xml_utils.getTag('CREDIT_AMOUNT',ln_credit_amount)||
1233 pay_prl_xml_utils.getTag('DEBIT_AMOUNT',ln_debit_amount);
1234
1235 DBMS_LOB.WRITEAPPEND(g_xml_data, length(l_tag), l_tag);
1236
1237 l_count := 1;
1238 --Filling the Cost Allocation KFF Values into the XML data Variable.
1239 FOR i IN gtr_costing_segment.first .. gtr_costing_segment.last LOOP
1240 IF gtr_costing_segment(i).column_name = 'SEGMENT1' THEN
1241 lv_segment_value := lv_segment1;
1242 elsIF gtr_costing_segment(i).column_name = 'SEGMENT2' THEN
1243 lv_segment_value := lv_segment2;
1244 elsIF gtr_costing_segment(i).column_name = 'SEGMENT3' THEN
1245 lv_segment_value := lv_segment3;
1246 elsIF gtr_costing_segment(i).column_name = 'SEGMENT4' THEN
1247 lv_segment_value := lv_segment4;
1248 elsIF gtr_costing_segment(i).column_name = 'SEGMENT5' THEN
1249 lv_segment_value := lv_segment5;
1250 elsIF gtr_costing_segment(i).column_name = 'SEGMENT6' THEN
1251 lv_segment_value := lv_segment6;
1252 elsIF gtr_costing_segment(i).column_name = 'SEGMENT7' THEN
1253 lv_segment_value := lv_segment7;
1254 elsIF gtr_costing_segment(i).column_name = 'SEGMENT8' THEN
1255 lv_segment_value := lv_segment8;
1256 elsIF gtr_costing_segment(i).column_name = 'SEGMENT9' THEN
1257 lv_segment_value := lv_segment9;
1258 elsIF gtr_costing_segment(i).column_name = 'SEGMENT10' THEN
1259 lv_segment_value := lv_segment10;
1260 elsIF gtr_costing_segment(i).column_name = 'SEGMENT11' THEN
1261 lv_segment_value := lv_segment11;
1262 elsIF gtr_costing_segment(i).column_name = 'SEGMENT12' THEN
1263 lv_segment_value := lv_segment12;
1264 elsIF gtr_costing_segment(i).column_name = 'SEGMENT13' THEN
1265 lv_segment_value := lv_segment13;
1266 elsIF gtr_costing_segment(i).column_name = 'SEGMENT14' THEN
1267 lv_segment_value := lv_segment14;
1268 elsIF gtr_costing_segment(i).column_name = 'SEGMENT15' THEN
1269 lv_segment_value := lv_segment15;
1270 elsIF gtr_costing_segment(i).column_name = 'SEGMENT16' THEN
1271 lv_segment_value := lv_segment16;
1272 elsIF gtr_costing_segment(i).column_name = 'SEGMENT17' THEN
1273 lv_segment_value := lv_segment17;
1274 elsIF gtr_costing_segment(i).column_name = 'SEGMENT18' THEN
1275 lv_segment_value := lv_segment18;
1276 elsIF gtr_costing_segment(i).column_name = 'SEGMENT19' THEN
1277 lv_segment_value := lv_segment19;
1278 elsIF gtr_costing_segment(i).column_name = 'SEGMENT20' THEN
1279 lv_segment_value := lv_segment20;
1280 elsIF gtr_costing_segment(i).column_name = 'SEGMENT21' THEN
1281 lv_segment_value := lv_segment21;
1282 elsIF gtr_costing_segment(i).column_name = 'SEGMENT22' THEN
1283 lv_segment_value := lv_segment22;
1284 elsIF gtr_costing_segment(i).column_name = 'SEGMENT23' THEN
1285 lv_segment_value := lv_segment23;
1286 elsIF gtr_costing_segment(i).column_name = 'SEGMENT24' THEN
1287 lv_segment_value := lv_segment24;
1288 elsIF gtr_costing_segment(i).column_name = 'SEGMENT25' THEN
1289 lv_segment_value := lv_segment25;
1290 elsIF gtr_costing_segment(i).column_name = 'SEGMENT26' THEN
1291 lv_segment_value := lv_segment26;
1292 elsIF gtr_costing_segment(i).column_name = 'SEGMENT27' THEN
1293 lv_segment_value := lv_segment27;
1294 elsIF gtr_costing_segment(i).column_name = 'SEGMENT28' THEN
1295 lv_segment_value := lv_segment28;
1296 elsIF gtr_costing_segment(i).column_name = 'SEGMENT29' THEN
1297 lv_segment_value := lv_segment29;
1298 elsIF gtr_costing_segment(i).column_name = 'SEGMENT30' THEN
1299 lv_segment_value := lv_segment30;
1300 END IF;
1301
1302 pay_prl_xml_utils.gXMLTable(l_count).Name := INITCAP(
1303 gtr_costing_segment(i).segment_label);
1304 pay_prl_xml_utils.gXMLTable(l_count).Value := lv_segment_value;
1305
1306 l_count := l_count + 1;
1307
1308 END LOOP ;
1309
1310 pay_prl_xml_utils.twoColumnar(p_type => 'G_CSR_GRE_OR_PRL_TOTAL_SEGMENT'
1311 ,p_data => pay_prl_xml_utils.gXMLTable
1312 ,p_count => l_count
1313 ,p_xml_data => g_xml_data);
1314 pay_prl_xml_utils.gXMLTable.delete;
1315
1316 l_tag := '</G_CSR_GRE_OR_PRL_TOTAL>';
1317
1318 --Appending the XML data into the Global XML data variable.
1319 DBMS_LOB.WRITEAPPEND(g_xml_data, length(l_tag), l_tag);
1320
1321 END LOOP;
1322 CLOSE c_costing_summary_rpt_details;
1323
1324 l_tag := '</LIST_G_CSR_GRE_OR_PRL_TOTAL>';
1325 DBMS_LOB.WRITEAPPEND(g_xml_data, length(l_tag), l_tag);
1326
1327 /********************************************************************
1328 ** This code is for populating the XML with the Costing Summary **
1329 ** Report for GRE/Payroll totals Depends on the parameter. **
1330 ********************************************************************/
1331
1332 pay_prl_xml_utils.gXMLTable.delete;
1333
1334 l_nodata_flag := TRUE;
1335 l_rec_count := 0;
1336 l_tag := '<LIST_G_GRE_OR_PRL_TOTAL>';
1337 DBMS_LOB.WRITEAPPEND(g_xml_data, length(l_tag), l_tag);
1338
1339 --Opening the Cursor for the data of the Third table
1340 --i.e., GRE/Payroll Totals Table
1341 OPEN c_get_gre_or_payroll_totals (lv_session_id
1342 ,p_business_group_id
1343 ,'CSR'
1344 ,p_sort_order1
1345 );
1346 LOOP
1347
1348 FETCH c_get_gre_or_payroll_totals INTO lv_gre_or_payroll
1349 ,lv_uom
1350 ,ln_credit_amount
1351 ,ln_debit_amount;
1352 IF c_get_gre_or_payroll_totals%NOTFOUND THEN
1353 hr_utility.set_location(gv_package_name ||
1354 '.costing_summary', 90);
1355
1356 --If no data is returned from the cursor then need to populate the
1357 --XML data with no values.
1358 IF(l_nodata_flag=TRUE) THEN
1359 lv_tagged_parameters := lv_tagged_parameters||
1360 pay_prl_xml_utils.getTag(
1361 'C_G_GRE_OR_PRL_TOTAL_NODATA',
1362 '1');
1363 l_tag := '<G_GRE_OR_PRL_TOTAL>';
1364 l_tag := l_tag||
1365 pay_prl_xml_utils.getTag('GRE_OR_PAYROLL',NULL)||
1366 pay_prl_xml_utils.getTag('CREDIT_AMOUNT',NULL)||
1367 pay_prl_xml_utils.getTag('DEBIT_AMOUNT',NULL)||
1368 pay_prl_xml_utils.getTag('UOM',NULL)||
1369 '</G_GRE_OR_PRL_TOTAL>';
1370 DBMS_LOB.WRITEAPPEND(g_xml_data, length(l_tag), l_tag);
1371 END IF;
1372 EXIT;
1373 END IF;
1374
1375 --If no data is there, then need to fill one NodataFound Flag
1376 -- in the XML output.
1377 l_nodata_flag := FALSE;
1378 IF(l_rec_count = 0)THEN
1379 lv_tagged_parameters := lv_tagged_parameters||
1380 pay_prl_xml_utils.getTag(
1381 'C_G_GRE_OR_PRL_TOTAL_NODATA',
1382 '0');
1383 END IF;
1384 l_rec_count := l_rec_count + 1;
1385 l_count := 1;
1386 pay_prl_xml_utils.gXMLTable(l_count).Name := 'GRE_OR_PAYROLL';
1387 pay_prl_xml_utils.gXMLTable(l_count).Value := lv_gre_or_payroll;
1388 l_count := l_count + 1;
1389
1390 pay_prl_xml_utils.gXMLTable(l_count).Name := 'CREDIT_AMOUNT';
1391 pay_prl_xml_utils.gXMLTable(l_count).Value := ln_credit_amount;
1392 l_count := l_count + 1;
1393
1394 pay_prl_xml_utils.gXMLTable(l_count).Name := 'DEBIT_AMOUNT';
1395 pay_prl_xml_utils.gXMLTable(l_count).Value := ln_debit_amount;
1396 l_count := l_count + 1;
1397
1398 pay_prl_xml_utils.gXMLTable(l_count).Name := 'UOM';
1399 pay_prl_xml_utils.gXMLTable(l_count).Value := lv_uom;
1400
1401 --Appending the XML data into the Global XML data variable by
1402 --invoking the multiColumnar in the pay_prl_xml_utils package.
1403 pay_prl_xml_utils.multiColumnar(
1404 'G_GRE_OR_PRL_TOTAL',
1405 pay_prl_xml_utils.gXMLTable,
1406 l_count,
1407 g_xml_data);
1408 pay_prl_xml_utils.gXMLTable.delete;
1409
1410 END LOOP;
1411 CLOSE c_get_gre_or_payroll_totals;
1412
1413 l_tag := '</LIST_G_GRE_OR_PRL_TOTAL>';
1414 DBMS_LOB.WRITEAPPEND(g_xml_data, length(l_tag), l_tag);
1415
1416 /********************************************************************
1417 * This code is for populating the XML with the Costing Summary *
1418 * Report for Grand Totals. *
1419 ********************************************************************/
1420
1421 pay_prl_xml_utils.gXMLTable.delete;
1422
1423 l_nodata_flag := TRUE;
1424 l_rec_count := 0;
1425 l_tag := '<LIST_G_CSR_GRAND_TOTAL>';
1426 DBMS_LOB.WRITEAPPEND(g_xml_data, length(l_tag), l_tag);
1427
1428 --Opening the Cursor for the data of the Fourth table
1429 --i.e., Costing Summary Report - Grand Totals Table
1430 OPEN c_costing_grand_totals (lv_session_id
1431 ,p_business_group_id
1432 ,'CSR'
1433 );
1434 LOOP
1435 FETCH c_costing_grand_totals into lv_uom
1436 ,ln_credit_amount
1437 ,ln_debit_amount
1438 ,lv_segment1
1439 ,lv_segment2
1440 ,lv_segment3
1441 ,lv_segment4
1442 ,lv_segment5
1443 ,lv_segment6
1444 ,lv_segment7
1445 ,lv_segment8
1446 ,lv_segment9
1447 ,lv_segment10
1448 ,lv_segment11
1449 ,lv_segment12
1450 ,lv_segment13
1451 ,lv_segment14
1452 ,lv_segment15
1453 ,lv_segment16
1454 ,lv_segment17
1455 ,lv_segment18
1456 ,lv_segment19
1457 ,lv_segment20
1461 ,lv_segment24
1458 ,lv_segment21
1459 ,lv_segment22
1460 ,lv_segment23
1462 ,lv_segment25
1463 ,lv_segment26
1464 ,lv_segment27
1465 ,lv_segment28
1466 ,lv_segment29
1467 ,lv_segment30;
1468
1469 lv_credit_sum:= lv_credit_sum + ln_credit_amount;
1470 lv_debit_sum:= lv_debit_sum + ln_debit_amount;
1471
1472 IF c_costing_grand_totals%NOTFOUND THEN
1473 hr_utility.set_location(gv_package_name ||
1474 '.costing_summary', 120);
1475
1476 IF (l_nodata_flag = TRUE)THEN
1477 lv_tagged_parameters := lv_tagged_parameters||
1478 pay_prl_xml_utils.getTag(
1479 'C_G_CSR_GRAND_TOTAL_NODATA',
1480 '1');
1481 l_tag := '<G_CSR_GRAND_TOTAL>';
1482
1483 l_tag := l_tag||
1484 pay_prl_xml_utils.getTag('UOM',NULL)||
1485 pay_prl_xml_utils.getTag('CREDIT_AMOUNT',NULL)||
1486 pay_prl_xml_utils.getTag('DEBIT_AMOUNT',NULL);
1487
1488 FOR i IN gtr_costing_segment.first .. gtr_costing_segment.last LOOP
1489 l_tag := l_tag ||'<G_CSR_GRAND_TOTAL_SEGMENT>'||
1490 pay_prl_xml_utils.getTag('SEGMENT',INITCAP(
1491 gtr_costing_segment(i).segment_label))||
1492 pay_prl_xml_utils.getTag('VALUE',NULL)||
1493 '</G_CSR_GRAND_TOTAL_SEGMENT>';
1494 END LOOP;
1495 l_tag := l_tag ||'</G_CSR_GRAND_TOTAL>';
1496 DBMS_LOB.WRITEAPPEND(g_xml_data, length(l_tag), l_tag);
1497 END IF;
1498 EXIT;
1499 END IF;
1500
1501 l_nodata_flag := FALSE;
1502 IF(l_rec_count = 0) THEN
1503 lv_tagged_parameters := lv_tagged_parameters||
1504 pay_prl_xml_utils.getTag(
1505 'C_G_CSR_GRAND_TOTAL_NODATA',
1506 '0');
1507 END IF;
1508 l_rec_count := l_rec_count + 1;
1509 l_tag := '<G_CSR_GRAND_TOTAL>';
1510
1511 l_tag := l_tag||
1512 pay_prl_xml_utils.getTag('UOM',lv_uom)||
1513 pay_prl_xml_utils.getTag('CREDIT_AMOUNT',ln_credit_amount)||
1514 pay_prl_xml_utils.getTag('DEBIT_AMOUNT',ln_debit_amount);
1515
1516 DBMS_LOB.WRITEAPPEND(g_xml_data, length(l_tag), l_tag);
1517
1518 l_count := 1;
1519 FOR i IN gtr_costing_segment.first .. gtr_costing_segment.last LOOP
1520 IF gtr_costing_segment(i).column_name = 'SEGMENT1' THEN
1521 lv_segment_value := lv_segment1;
1522 elsIF gtr_costing_segment(i).column_name = 'SEGMENT2' THEN
1523 lv_segment_value := lv_segment2;
1524 elsIF gtr_costing_segment(i).column_name = 'SEGMENT3' THEN
1525 lv_segment_value := lv_segment3;
1526 elsIF gtr_costing_segment(i).column_name = 'SEGMENT4' THEN
1527 lv_segment_value := lv_segment4;
1528 elsIF gtr_costing_segment(i).column_name = 'SEGMENT5' THEN
1529 lv_segment_value := lv_segment5;
1530 elsIF gtr_costing_segment(i).column_name = 'SEGMENT6' THEN
1531 lv_segment_value := lv_segment6;
1532 elsIF gtr_costing_segment(i).column_name = 'SEGMENT7' THEN
1533 lv_segment_value := lv_segment7;
1534 elsIF gtr_costing_segment(i).column_name = 'SEGMENT8' THEN
1535 lv_segment_value := lv_segment8;
1536 elsIF gtr_costing_segment(i).column_name = 'SEGMENT9' THEN
1537 lv_segment_value := lv_segment9;
1538 elsIF gtr_costing_segment(i).column_name = 'SEGMENT10' THEN
1539 lv_segment_value := lv_segment10;
1540 elsIF gtr_costing_segment(i).column_name = 'SEGMENT11' THEN
1541 lv_segment_value := lv_segment11;
1542 elsIF gtr_costing_segment(i).column_name = 'SEGMENT12' THEN
1543 lv_segment_value := lv_segment12;
1544 elsIF gtr_costing_segment(i).column_name = 'SEGMENT13' THEN
1545 lv_segment_value := lv_segment13;
1546 elsIF gtr_costing_segment(i).column_name = 'SEGMENT14' THEN
1547 lv_segment_value := lv_segment14;
1548 elsIF gtr_costing_segment(i).column_name = 'SEGMENT15' THEN
1549 lv_segment_value := lv_segment15;
1550 elsIF gtr_costing_segment(i).column_name = 'SEGMENT16' THEN
1551 lv_segment_value := lv_segment16;
1552 elsIF gtr_costing_segment(i).column_name = 'SEGMENT17' THEN
1553 lv_segment_value := lv_segment17;
1554 elsIF gtr_costing_segment(i).column_name = 'SEGMENT18' THEN
1555 lv_segment_value := lv_segment18;
1556 elsIF gtr_costing_segment(i).column_name = 'SEGMENT19' THEN
1557 lv_segment_value := lv_segment19;
1558 elsIF gtr_costing_segment(i).column_name = 'SEGMENT20' THEN
1559 lv_segment_value := lv_segment20;
1560 elsIF gtr_costing_segment(i).column_name = 'SEGMENT21' THEN
1561 lv_segment_value := lv_segment21;
1562 elsIF gtr_costing_segment(i).column_name = 'SEGMENT22' THEN
1563 lv_segment_value := lv_segment22;
1564 elsIF gtr_costing_segment(i).column_name = 'SEGMENT23' THEN
1565 lv_segment_value := lv_segment23;
1566 elsIF gtr_costing_segment(i).column_name = 'SEGMENT24' THEN
1567 lv_segment_value := lv_segment24;
1568 elsIF gtr_costing_segment(i).column_name = 'SEGMENT25' THEN
1569 lv_segment_value := lv_segment25;
1570 elsIF gtr_costing_segment(i).column_name = 'SEGMENT26' THEN
1571 lv_segment_value := lv_segment26;
1572 elsIF gtr_costing_segment(i).column_name = 'SEGMENT27' THEN
1573 lv_segment_value := lv_segment27;
1574 elsIF gtr_costing_segment(i).column_name = 'SEGMENT28' THEN
1575 lv_segment_value := lv_segment28;
1576 elsIF gtr_costing_segment(i).column_name = 'SEGMENT29' THEN
1577 lv_segment_value := lv_segment29;
1578 elsIF gtr_costing_segment(i).column_name = 'SEGMENT30' THEN
1579 lv_segment_value := lv_segment30;
1580 END IF;
1581
1582 pay_prl_xml_utils.gXMLTable(l_count).Name := INITCAP(
1583 gtr_costing_segment(i).segment_label);
1584 pay_prl_xml_utils.gXMLTable(l_count).Value := lv_segment_value;
1585
1586 l_count := l_count + 1;
1587
1588 END LOOP ;
1589
1590 pay_prl_xml_utils.twoColumnar(p_type => 'G_CSR_GRAND_TOTAL_SEGMENT'
1591 ,p_data => pay_prl_xml_utils.gXMLTable
1592 ,p_count => l_count
1593 ,p_xml_data => g_xml_data);
1594
1595 pay_prl_xml_utils.gXMLTable.delete;
1596
1597 l_tag := '</G_CSR_GRAND_TOTAL>';
1598 DBMS_LOB.WRITEAPPEND(g_xml_data, length(l_tag), l_tag);
1599
1600 END LOOP;
1601 CLOSE c_costing_grand_totals;
1602
1603 l_tag := '</LIST_G_CSR_GRAND_TOTAL>';
1604 DBMS_LOB.WRITEAPPEND(g_xml_data, length(l_tag), l_tag);
1605
1606 /********************************************************************
1607 * This code is for populating the XML with the Costing Summary *
1608 * Report for Report Totals. *
1609 ********************************************************************/
1610
1611 pay_prl_xml_utils.gXMLTable.delete;
1612 l_rec_count := 0;
1613
1614 l_tag := '<LIST_G_REPORT_TOTAL>';
1615 DBMS_LOB.WRITEAPPEND(g_xml_data, length(l_tag), l_tag);
1616
1617 hr_utility.trace('Session id : '||lv_session_id);
1618 OPEN c_get_report_totals(lv_session_id,p_business_group_id,'CSR');
1619 LOOP
1620 FETCH c_get_report_totals INTO
1621 lv_uom
1622 ,lv_credit_sum
1623 ,lv_debit_sum;
1624
1625 IF c_get_report_totals%NOTFOUND THEN
1626 hr_utility.set_location(gv_package_name || '.costing_summary', 150);
1627 IF (l_nodata_flag = TRUE) THEN
1628 lv_tagged_parameters := lv_tagged_parameters||
1629 pay_prl_xml_utils.getTag(
1630 'C_G_REPORT_TOTAL_NODATA',
1631 '1');
1632 l_tag := '<G_REPORT_TOTAL>';
1633 l_tag := l_tag||
1634 pay_prl_xml_utils.getTag('CREDIT_AMOUNT',NULL)||
1635 pay_prl_xml_utils.getTag('DEBIT_AMOUNT',NULL)||
1636 pay_prl_xml_utils.getTag('UOM',NULL)||
1637 '</G_REPORT_TOTAL>';
1638
1639 DBMS_LOB.WRITEAPPEND(g_xml_data, length(l_tag), l_tag);
1640 END IF;
1641 EXIT;
1642 END IF;
1643
1644 l_nodata_flag := FALSE;
1645
1646 IF(l_rec_count = 0) THEN
1647 lv_tagged_parameters := lv_tagged_parameters||
1648 pay_prl_xml_utils.getTag(
1649 'C_G_REPORT_TOTAL_NODATA',
1650 '0');
1651 END IF;
1652 l_rec_count := l_rec_count + 1;
1653
1654 l_count := 1;
1655 pay_prl_xml_utils.gXMLTable(l_count).Name := 'CREDIT_AMOUNT';
1656 pay_prl_xml_utils.gXMLTable(l_count).Value := lv_credit_sum;
1657 l_count := l_count + 1;
1658
1659 pay_prl_xml_utils.gXMLTable(l_count).Name := 'DEBIT_AMOUNT';
1660 pay_prl_xml_utils.gXMLTable(l_count).Value := lv_debit_sum;
1661 l_count := l_count + 1;
1662
1663 pay_prl_xml_utils.gXMLTable(l_count).Name := 'UOM';
1664 pay_prl_xml_utils.gXMLTable(l_count).Value := lv_uom;
1665
1666 pay_prl_xml_utils.multiColumnar(
1667 'G_REPORT_TOTAL',
1668 pay_prl_xml_utils.gXMLTable,
1669 l_count,
1670 g_xml_data);
1671 pay_prl_xml_utils.gXMLTable.delete;
1672
1673 END LOOP;
1674 CLOSE c_get_report_totals;
1675
1676 l_tag := '</LIST_G_REPORT_TOTAL>';
1677 DBMS_LOB.WRITEAPPEND(g_xml_data, length(l_tag), l_tag);
1678
1679 DBMS_LOB.WRITEAPPEND(g_xml_data, length(lv_tagged_parameters),
1680 lv_tagged_parameters);
1681
1682 l_tag := '</PAYRPCSR>';
1683 DBMS_LOB.WRITEAPPEND(g_xml_data, length(l_tag), l_tag);
1684
1685 DELETE FROM pay_us_rpt_totals where attribute31='CSR';
1686
1687 p_xml := g_xml_data;
1688
1689
1690 --
1691 END costing_summary;
1692 --
1693 --
1694 END pay_costing_summary_x_rep_pkg;