[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.0 2005/05/29 07:53:31 appldev noship $ */
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)||
119 '('||p_costing||')';
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))||
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))||
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',attribute2
302 ,gre_name)
303 ,attribute24 --UOM
304 ,sum(value1)
305 ,sum(value2)
306 ,attribute3
307 ,attribute4
308 ,attribute5
309 ,attribute6
310 ,attribute7
311 ,attribute8
312 ,attribute9
313 ,attribute10
314 ,attribute11
315 ,attribute12
316 ,attribute13
317 ,attribute14
318 ,attribute15
319 ,attribute16
320 ,attribute17
321 ,attribute18
325 ,attribute22
322 ,attribute19
323 ,attribute20
324 ,attribute21
326 FROM pay_us_rpt_totals
327 WHERE business_group_id=cp_business_group_id
328 AND cp_csr='CSR'
329 AND session_id=cp_session_id
330 GROUP BY decode(upper(cp_sort_order1), 'PAYROLL NAME',attribute2,
331 gre_name)
332 ,attribute3
333 ,attribute4
334 ,attribute5
335 ,attribute6
336 ,attribute7
337 ,attribute8
338 ,attribute9
339 ,attribute10
340 ,attribute11
341 ,attribute12
342 ,attribute13
343 ,attribute14
344 ,attribute15
345 ,attribute16
346 ,attribute17
347 ,attribute18
348 ,attribute19
349 ,attribute20
350 ,attribute21
351 ,attribute22
352 ,attribute24
353 ;
354 --
355 /************************************************************
356 ** Cursor returns grand totals **
357 ************************************************************/
358 --
359 CURSOR c_costing_grand_totals (cp_session_id IN NUMBER
360 ,cp_business_group_id IN NUMBER
361 ,cp_csr IN VARCHAR2
362 ) IS
363 SELECT attribute24
364 ,sum(value1)
365 ,sum(value2)
366 ,attribute3
367 ,attribute4
368 ,attribute5
369 ,attribute6
370 ,attribute7
371 ,attribute8
372 ,attribute9
373 ,attribute10
374 ,attribute11
375 ,attribute12
376 ,attribute13
377 ,attribute14
378 ,attribute15
379 ,attribute16
380 ,attribute17
381 ,attribute18
382 ,attribute19
383 ,attribute20
384 ,attribute21
385 ,attribute22
386 FROM pay_us_rpt_totals
387 WHERE business_group_id=cp_business_group_id
388 AND cp_csr='CSR'
389 AND session_id=cp_session_id
390 GROUP BY
391 attribute3
392 ,attribute4
393 ,attribute5
394 ,attribute6
395 ,attribute7
396 ,attribute8
397 ,attribute9
398 ,attribute10
399 ,attribute11
400 ,attribute12
401 ,attribute13
402 ,attribute14
403 ,attribute15
404 ,attribute16
405 ,attribute17
406 ,attribute18
407 ,attribute19
408 ,attribute20
409 ,attribute21
410 ,attribute22
411 ,attribute24
412 ;
413
414
415 /**************************************************************
416 **Cursor to get GRE/Payroll totals **
417 ***************************************************************/
418 --
419 CURSOR c_get_gre_or_payroll_totals(cp_session_id IN NUMBER
420 ,cp_business_group_id IN NUMBER
421 ,cp_total_flag IN VARCHAR2
422 ,cp_sort_order1 IN VARCHAR2
423 ) IS
424 SELECT decode(upper(cp_sort_order1), 'PAYROLL NAME',attribute2,
425 gre_name)
426 ,attribute24 --UOM
427 ,sum(value1)
428 ,sum(value2)
429 FROM pay_us_rpt_totals
430 WHERE session_id=cp_session_id
431 AND business_group_id=cp_business_group_id
432 AND attribute1=cp_total_flag
433 GROUP BY decode(upper(cp_sort_order1), 'PAYROLL NAME',attribute2,
434 gre_name)
435 ,attribute24;
436 --
437 /**************************************************************
438 **CURSOR to get report total **
439 ***************************************************************/
440 CURSOR c_get_report_totals (cp_session_id IN NUMBER
441 ,cp_business_group_id IN NUMBER
442 ,cp_total_flag IN VARCHAR2
443 ) IS
444 SELECT attribute24 --UOM
445 ,SUM(value1)
446 ,SUM(value2)
447 FROM pay_us_rpt_totals
448 WHERE session_id=cp_session_id
449 AND business_group_id=cp_business_group_id
450 AND attribute1=cp_total_flag
451 GROUP BY attribute24;
452 --
453 /*************************************************************
454 ** Local Variables **
458 lv_payroll_name VARCHAR2(100);
455 **************************************************************/
456 lv_consolidation_set_name VARCHAR2(100);
457 lv_business_group_name VARCHAR2(100);
459 lv_gre_name VARCHAR2(240);
460 lv_input_value_name VARCHAR2(100);
461 lv_uom VARCHAR2(100);
462 ln_credit_amount NUMBER;
463 ln_debit_amount NUMBER;
464 lv_effective_date DATE;
465 lv_concatenated_segments VARCHAR2(200);
466 lv_segment1 VARCHAR2(200);
467 lv_segment2 VARCHAR2(200);
468 lv_segment3 VARCHAR2(200);
469 lv_segment4 VARCHAR2(200);
470 lv_segment5 VARCHAR2(200);
471 lv_segment6 VARCHAR2(200);
472 lv_segment7 VARCHAR2(200);
473 lv_segment8 VARCHAR2(200);
474 lv_segment9 VARCHAR2(200);
475 lv_segment10 VARCHAR2(200);
476 lv_segment11 VARCHAR2(200);
477 lv_segment12 VARCHAR2(200);
478 lv_segment13 VARCHAR2(200);
479 lv_segment14 VARCHAR2(200);
480 lv_segment15 VARCHAR2(200);
481 lv_segment16 VARCHAR2(200);
482 lv_segment17 VARCHAR2(200);
483 lv_segment18 VARCHAR2(200);
484 lv_segment19 VARCHAR2(200);
485 lv_segment20 VARCHAR2(200);
486
487 lv_segment_value VARCHAR2(100);
488 lv_column_name VARCHAR2(100);
489
490 lv_accrual_type VARCHAR2(100);
491 lv_cost_mode VARCHAR2(100);
492
493 lv_gre_or_payroll VARCHAR2(240);
494 lv_session_id NUMBER;
495 lv_credit_sum NUMBER;
496 lv_debit_sum NUMBER;
497
498 lv_start_date DATE;
499 lv_END_date DATE;
500 lv_costing_process_flag VARCHAR2(1);
501 lv_include_accruals VARCHAR2(100);
502
503 l_tag VARCHAR2(2000);
504 l_count NUMBER;
505
506 lv_tagged_parameters VARCHAR2(2000);
507 lv_tableHeadings VARCHAR2(1000);
508 lv_total_heading VARCHAR2(240);
509 lv_csr_heading VARCHAR2(240);
510
511 l_nodata_flag BOOLEAN DEFAULT TRUE;
512 l_rec_count NUMBER DEFAULT 0;
513
514 --
515 BEGIN
516 --
517 gv_package_name :='pay_costing_summary_x_rep_pkg';
518 hr_utility.set_location(gv_package_name || '.costing_summary', 10);
519 hr_utility.trace('Start Date = ' || p_start_date);
520 hr_utility.trace('End Date = ' || p_END_date);
521 hr_utility.trace('Business Group ID = '|| p_business_group_id);
522 hr_utility.trace('Costing Process = ' || p_costing);
523 hr_utility.trace('Payroll ID = ' || p_payroll_id);
524 hr_utility.trace('Consolidation Set ID = ' || p_consolidation_set_id);
525 hr_utility.trace('Tax unit ID = ' || p_tax_unit_id);
526 hr_utility.trace('Cost Type = ' || p_cost_type);
527 hr_utility.trace('Sort Order 1 = ' || p_sort_order1);
528 hr_utility.trace('Sort Order 2 = ' || p_sort_order2);
529
530 lv_costing_process_flag := 'N';
531 --Clearing the PL/SQL table of package pay_prl_xml_utils
532 pay_prl_xml_utils.gXMLTable.DELETE;
533 --
534 fnd_file.put_line(fnd_file.log,'Creating the XML...');
535
536 --Creating a CLOB and opening the CLOB.
537 DBMS_LOB.CREATETEMPORARY(g_xml_data,FALSE,DBMS_LOB.CALL);
538 DBMS_LOB.OPEN(g_xml_data,dbms_lob.lob_readwrite);
539 --
540 l_tag :='<?xml version="1.0" encoding="UTF-8"?>';
541 DBMS_LOB.WRITEAPPEND(g_xml_data, length(l_tag), l_tag);
542 --
543 l_tag := '<PAYRPCSR>';
544 DBMS_LOB.WRITEAPPEND(g_xml_data, length(l_tag), l_tag);
545 fnd_file.put_line(fnd_file.log,'Started...');
546 --
547 --Fill the Global PL/SQL table with the CostAllocation Keyflex's
548 --enabled segments and their corresponding values.
549 getNFillCostFlexSegments(p_business_group_id);
550 --
551 OPEN c_get_organization_name(p_business_group_id);
552 FETCH c_get_organization_name INTO lv_business_group_name;
553 CLOSE c_get_organization_name;
554
555 OPEN c_get_organization_name(p_tax_unit_id);
556 FETCH c_get_organization_name INTO lv_gre_name;
557 CLOSE c_get_organization_name;
558
559 OPEN c_get_payroll_name(p_payroll_id);
560 FETCH c_get_payroll_name INTO lv_payroll_name;
561 CLOSE c_get_payroll_name;
562
563 OPEN c_get_consolidation_set_name(p_consolidation_set_id);
564 FETCH c_get_consolidation_set_name INTO lv_consolidation_set_name;
565 CLOSE c_get_consolidation_set_name;
566
567 hr_utility.set_location(gv_package_name || '.costing_summary', 30);
568
569 lv_include_accruals:= nvl(hr_general.decode_lookup('PAY_PAYRPCBR',
570 p_cost_type),' ');
571
572 IF p_costing IS NOT NULL THEN
573 OPEN c_get_effective_date(TO_NUMBER(p_costing));
574 FETCH c_get_effective_date INTO lv_start_date;
575 CLOSE c_get_effective_date;
576 lv_END_date := lv_start_date;
577 lv_costing_process_flag:='Y';
578 hr_utility.trace('lv_start_date'|| lv_start_date);
579 ELSE
580 lv_start_date:=to_date(p_start_date, 'YYYY/MM/DD HH24:MI:SS');
584
581 lv_END_date:=to_date(p_END_date, 'YYYY/MM/DD HH24:MI:SS');
582 lv_costing_process_flag:='N';
583 END IF;
585 lv_tagged_parameters := getTaggedParameters(
586 p_business_group => lv_business_group_name
587 ,p_start_date =>lv_start_date
588 ,p_end_date =>lv_END_date
589 ,p_costing =>p_costing
590 ,p_payroll_name =>lv_payroll_name
591 ,p_consolidation_set_name
592 =>lv_consolidation_set_name
593 ,p_gre_name =>lv_gre_name
594 ,p_include_accruals=>lv_include_accruals
595 ,p_sort_order1 =>p_sort_order1
596 ,p_sort_order2 =>p_sort_order2
597 ,p_template_name =>p_template_name
598 );
599 /*Finding the headings of the tables*/
600 IF p_sort_order1='Payroll Name' THEN
601 lv_csr_heading :='Costing Summary Report - Payroll Totals';
602 lv_total_heading := 'Payroll Totals';
603 ELSE
604 lv_csr_heading :='Costing Summary Report - GRE Totals';
605 lv_total_heading := 'GRE Totals';
606 END IF;
607
608 lv_tableHeadings := pay_prl_xml_utils.getTag('CSR_GRE_OR_PAYROLL_HEADING',
609 lv_csr_heading)||
610 pay_prl_xml_utils.getTag('GRE_OR_PAYROLL_TOTAL_HEADING',
611 lv_total_heading);
612 lv_tagged_parameters := lv_tagged_parameters || lv_tableHeadings;
613
614 /***********************************************************************
615 * The following code is for populating the CLOB with the data of the *
616 * Costing details of payrolls. *
617 ***********************************************************************/
618 --
619 l_tag := '<LIST_G_ASG_COSTING_DETAILS>';
620 DBMS_LOB.WRITEAPPEND(g_xml_data, length(l_tag), l_tag);
621 --
622 OPEN c_get_session_id;
623 FETCH c_get_session_id INTO lv_session_id;
624 CLOSE c_get_session_id;
625 --
626 -- Get the WHERE CLAUSE depending upon the parameters provided.
627 c_clause1:=get_optional_where_clause(p_payroll_id,
628 p_consolidation_set_id,
629 p_tax_unit_id,
630 lv_costing_process_flag,
631 p_costing,
632 p_cost_type);
633
634 --Construct the Query depending on the WHERE CLAUSE.
635 c_query := 'SELECT
636 pcd.payroll_name
637 ,pcd.gre_name
638 ,pcd.input_value_name
639 ,pcd.uom
640 ,sum(pcd.credit_amount)
641 ,sum(pcd.debit_amount)
642 ,pcd.cost_type
643 ,pcd.concatenated_segments
644 ,pcd.segment1
645 ,pcd.segment2
646 ,pcd.segment3
647 ,pcd.segment4
648 ,pcd.segment5
649 ,pcd.segment6
650 ,pcd.segment7
651 ,pcd.segment8
652 ,pcd.segment9
653 ,pcd.segment10
654 ,pcd.segment11
655 ,pcd.segment12
656 ,pcd.segment13
657 ,pcd.segment14
658 ,pcd.segment15
659 ,pcd.segment16
660 ,pcd.segment17
661 ,pcd.segment18
662 ,pcd.segment19
663 ,pcd.segment20
664 FROM pay_costing_details_v pcd
665 WHERE
666 pcd.effective_date between :cp_start_date AND :cp_end_date
667 ' || c_clause1 || '
668 AND pcd.business_group_id = :cp_business_group_id
669 GROUP BY pcd.payroll_name,pcd.gre_name
670 ,pcd.input_value_name
671 ,pcd.uom,pcd.cost_type
672 ,pcd.concatenated_segments
673 ,pcd.segment1
674 ,pcd.segment2
675 ,pcd.segment3
676 ,pcd.segment4
677 ,pcd.segment5
678 ,pcd.segment6
679 ,pcd.segment7
680 ,pcd.segment8
681 ,pcd.segment9
682 ,pcd.segment10
683 ,pcd.segment11
684 ,pcd.segment12
685 ,pcd.segment13
686 ,pcd.segment14
687 ,pcd.segment15
688 ,pcd.segment16
689 ,pcd.segment17
690 ,pcd.segment18
691 ,pcd.segment19
692 ,pcd.segment20
693 ORDER BY pcd.cost_type
694 ,decode (upper(:cp_sort_order1), ''PAYROLL NAME'',
695 pcd.payroll_name,
696 pcd.gre_name)
697 ,decode(upper(:cp_sort_order2), ''GRE'', pcd.gre_name,
698 ''PAYROLL NAME'',
699 pcd.payroll_name,''X'')';
700 hr_utility.trace('Query is : '||c_query );
701 --
702 --Opening the REFCURSOR for getting and populating into the XML variable.
703 OPEN c_asg_costing_details
704 FOR c_query USING TO_DATE(NVL(p_start_date,'0001/01/01 00:00:00'), 'YYYY/MM/DD HH24:MI:SS')
705 ,TO_DATE(nvl(p_end_date,'4712/12/31 00:00:00'), 'YYYY/MM/DD HH24:MI:SS')
706 ,p_business_group_id
707 ,p_sort_order1
708 ,p_sort_order2;
709 LOOP
710 FETCH c_asg_costing_details INTO
711 lv_payroll_name
712 ,lv_gre_name
713 ,lv_input_value_name
714 ,lv_uom
715 ,ln_credit_amount
716 ,ln_debit_amount
717 ,lv_cost_mode
718 ,lv_concatenated_segments
719 ,lv_segment1
720 ,lv_segment2
721 ,lv_segment3
722 ,lv_segment4
723 ,lv_segment5
727 ,lv_segment9
724 ,lv_segment6
725 ,lv_segment7
726 ,lv_segment8
728 ,lv_segment10
729 ,lv_segment11
730 ,lv_segment12
731 ,lv_segment13
732 ,lv_segment14
733 ,lv_segment15
734 ,lv_segment16
735 ,lv_segment17
736 ,lv_segment18
737 ,lv_segment19
738 ,lv_segment20 ;
739 IF c_asg_costing_details%NOTFOUND THEN
740 hr_utility.set_location(gv_package_name || '.costing_summary', 60);
741
742 --If no data is returned from the cursor then need to populate the
743 --XML data with no values.
744 IF(l_nodata_flag = TRUE) THEN
745 lv_tagged_parameters := lv_tagged_parameters||
746 pay_prl_xml_utils.getTag(
747 'C_G_ASG_COSTING_DETAILS_NODATA',
748 '1');
749 l_tag := '<G_ASG_COSTING_DETAILS>';
750
751 l_tag := l_tag||
752 pay_prl_xml_utils.getTag('PAYROLL_NAME',NULL)||
753 pay_prl_xml_utils.getTag('GRE_NAME', NULL)||
754 pay_prl_xml_utils.getTag('INPUT_VALUE_NAME',
755 NULL)||
756 pay_prl_xml_utils.getTag('UOM',NULL)||
757 pay_prl_xml_utils.getTag('CREDIT_AMOUNT',NULL)||
758 pay_prl_xml_utils.getTag('DEBIT_AMOUNT',NULL)||
759 pay_prl_xml_utils.getTag('ACCRUAL_TYPE', NULL);
760
761 --Need to provide the Heading of the columns eventhough no data
762 --found.So here it's going to fill the Column heading data.
763 FOR i IN gtr_costing_segment.first .. gtr_costing_segment.last LOOP
764 l_tag:= l_tag||'<G_ASG_COSTING_DETAILS_SEGMENT>'||
765 pay_prl_xml_utils.getTag('SEGMENT',INITCAP(
766 gtr_costing_segment(i).segment_label))||
767 pay_prl_xml_utils.getTag('VALUE',NULL)||
768 '</G_ASG_COSTING_DETAILS_SEGMENT>';
769 END LOOP;
770 l_tag :=l_tag||'</G_ASG_COSTING_DETAILS>';
771 DBMS_LOB.WRITEAPPEND(g_xml_data, length(l_tag), l_tag);
772 END IF;
773 EXIT;
774 END IF;
775 --
776 --If no data is there, then need to fill one NodataFound Flag
777 -- in the XML output.
778 l_nodata_flag := FALSE;
779 IF(l_rec_count = 0) THEN
780 lv_tagged_parameters := lv_tagged_parameters||
781 pay_prl_xml_utils.getTag(
782 'C_G_ASG_COSTING_DETAILS_NODATA',
783 '0');
784 END IF;
785 l_rec_count := l_rec_count + 1;
786 lv_accrual_type:=nvl(hr_general.decode_lookup('PAY_PAYRPCBR',lv_cost_mode),' ');
787 /*insert into pay_us_rpt_totals*/
788 insert INTO pay_us_rpt_totals(session_id,business_group_id,gre_name,value1,
789 value2 ,attribute1,attribute2,attribute3,
790 attribute4,attribute5,attribute6,attribute7,
791 attribute8,attribute9,attribute10,
792 attribute11,attribute12,attribute13,
793 attribute14,attribute15,attribute16,
794 attribute17,attribute18,attribute19,
795 attribute20,attribute21,attribute22,
796 attribute23,attribute24) values
797 (lv_session_id -- session ID is passed
798 ,p_business_group_id
799 ,lv_gre_name
800 ,ln_credit_amount
801 ,ln_debit_amount
802 ,'CSR' -- denotes that the record is for Costing Summary Report
803 ,lv_payroll_name
804 ,lv_segment1
805 ,lv_segment2
806 ,lv_segment3
807 ,lv_segment4
808 ,lv_segment5
809 ,lv_segment6
810 ,lv_segment7
811 ,lv_segment8
812 ,lv_segment9
813 ,lv_segment10
814 ,lv_segment11
815 ,lv_segment12
816 ,lv_segment13
817 ,lv_segment14
818 ,lv_segment15
819 ,lv_segment16
820 ,lv_segment17
821 ,lv_segment18
822 ,lv_segment19
823 ,lv_segment20
824 ,lv_concatenated_segments
825 ,lv_uom);
826
827 hr_utility.set_location(gv_package_name || '.costing_summary', 70);
828
829 --Filling the data along with the XML tags into the local XML
830 --data variable.
831 l_tag := '<G_ASG_COSTING_DETAILS>';
832
833 l_tag := l_tag||
834 pay_prl_xml_utils.getTag('PAYROLL_NAME',lv_payroll_name)||
835 pay_prl_xml_utils.getTag('GRE_NAME', lv_gre_name)||
836 pay_prl_xml_utils.getTag('INPUT_VALUE_NAME',
837 lv_input_value_name)||
838 pay_prl_xml_utils.getTag('UOM',lv_uom)||
839 pay_prl_xml_utils.getTag('CREDIT_AMOUNT',ln_credit_amount)||
840 pay_prl_xml_utils.getTag('DEBIT_AMOUNT',ln_debit_amount)||
841 pay_prl_xml_utils.getTag('ACCRUAL_TYPE', lv_accrual_type);
842
843 DBMS_LOB.WRITEAPPEND(g_xml_data, length(l_tag), l_tag);
844
845 l_count := 1;
846
847 --Filling the Cost Allocation KFF Values into the XML data Variable.
848 FOR i IN gtr_costing_segment.first .. gtr_costing_segment.last LOOP
852 lv_segment_value := lv_segment2;
849 IF gtr_costing_segment(i).column_name = 'SEGMENT1' THEN
850 lv_segment_value := lv_segment1;
851 elsIF gtr_costing_segment(i).column_name = 'SEGMENT2' THEN
853 elsIF gtr_costing_segment(i).column_name = 'SEGMENT3' THEN
854 lv_segment_value := lv_segment3;
855 elsIF gtr_costing_segment(i).column_name = 'SEGMENT4' THEN
856 lv_segment_value := lv_segment4;
857 elsIF gtr_costing_segment(i).column_name = 'SEGMENT5' THEN
858 lv_segment_value := lv_segment5;
859 elsIF gtr_costing_segment(i).column_name = 'SEGMENT6' THEN
860 lv_segment_value := lv_segment6;
861 elsIF gtr_costing_segment(i).column_name = 'SEGMENT7' THEN
862 lv_segment_value := lv_segment7;
863 elsIF gtr_costing_segment(i).column_name = 'SEGMENT8' THEN
864 lv_segment_value := lv_segment8;
865 elsIF gtr_costing_segment(i).column_name = 'SEGMENT9' THEN
866 lv_segment_value := lv_segment9;
867 elsIF gtr_costing_segment(i).column_name = 'SEGMENT10' THEN
868 lv_segment_value := lv_segment10;
869 elsIF gtr_costing_segment(i).column_name = 'SEGMENT11' THEN
870 lv_segment_value := lv_segment11;
871 elsIF gtr_costing_segment(i).column_name = 'SEGMENT12' THEN
872 lv_segment_value := lv_segment12;
873 elsIF gtr_costing_segment(i).column_name = 'SEGMENT13' THEN
874 lv_segment_value := lv_segment13;
875 elsIF gtr_costing_segment(i).column_name = 'SEGMENT14' THEN
876 lv_segment_value := lv_segment14;
877 elsIF gtr_costing_segment(i).column_name = 'SEGMENT15' THEN
878 lv_segment_value := lv_segment15;
879 elsIF gtr_costing_segment(i).column_name = 'SEGMENT16' THEN
880 lv_segment_value := lv_segment16;
881 elsIF gtr_costing_segment(i).column_name = 'SEGMENT17' THEN
882 lv_segment_value := lv_segment17;
883 elsIF gtr_costing_segment(i).column_name = 'SEGMENT18' THEN
884 lv_segment_value := lv_segment18;
885 elsIF gtr_costing_segment(i).column_name = 'SEGMENT19' THEN
886 lv_segment_value := lv_segment19;
887 elsIF gtr_costing_segment(i).column_name = 'SEGMENT20' THEN
888 lv_segment_value := lv_segment20;
889 END IF;
890
891 pay_prl_xml_utils.gXMLTable(l_count).Name := INITCAP(
892 gtr_costing_segment(i).segment_label);
893 pay_prl_xml_utils.gXMLTable(l_count).Value := lv_segment_value;
894
895 l_count := l_count + 1;
896
897 END LOOP ;
898
899 pay_prl_xml_utils.twoColumnar(p_type => 'G_ASG_COSTING_DETAILS_SEGMENT'
900 ,p_data => pay_prl_xml_utils.gXMLTable
901 ,p_count => l_count
902 ,p_xml_data => g_xml_data);
903 pay_prl_xml_utils.gXMLTable.delete;
904
905 l_tag := '</G_ASG_COSTING_DETAILS>';
906
907 --Appending the XML data into the Global XML data variable.
908 DBMS_LOB.WRITEAPPEND(g_xml_data, length(l_tag), l_tag);
909
910 END LOOP;
911 CLOSE c_asg_costing_details;
912
913 l_tag := '</LIST_G_ASG_COSTING_DETAILS>';
914 DBMS_LOB.WRITEAPPEND(g_xml_data, length(l_tag), l_tag);
915
916 /********************************************************************
917 * This code is for populating the XML with the Costing Summary *
918 * Report for GRE/Payroll Depends on the parameter. *
919 ********************************************************************/
920
921 pay_prl_xml_utils.gXMLTable.delete;
922
923 l_nodata_flag := TRUE;
924 l_rec_count := 0;
925
926 l_tag := '<LIST_G_CSR_GRE_OR_PRL_TOTAL>';
927 DBMS_LOB.WRITEAPPEND(g_xml_data, length(l_tag), l_tag);
928
929
930 --Opening the Cursor for the data of the Second table
931 --i.e., Costing Summary Details Table
932 OPEN c_costing_summary_rpt_details (lv_session_id
933 ,p_business_group_id
934 ,'CSR'
935 ,p_sort_order1
936 ,p_sort_order2
937 );
938 LOOP
939 FETCH c_costing_summary_rpt_details INTO
940 lv_gre_or_payroll
941 ,lv_uom
942 ,ln_credit_amount
943 ,ln_debit_amount
944 ,lv_segment1
945 ,lv_segment2
946 ,lv_segment3
947 ,lv_segment4
948 ,lv_segment5
949 ,lv_segment6
950 ,lv_segment7
951 ,lv_segment8
952 ,lv_segment9
953 ,lv_segment10
954 ,lv_segment11
955 ,lv_segment12
956 ,lv_segment13
957 ,lv_segment14
958 ,lv_segment15
959 ,lv_segment16
960 ,lv_segment17
961 ,lv_segment18
965 hr_utility.set_location(gv_package_name || '.costing_summary', 100);
962 ,lv_segment19
963 ,lv_segment20;
964 IF c_costing_summary_rpt_details % NOTFOUND THEN
966
967 --If no data is returned from the cursor then need to populate the
968 --XML data with no values.
969 IF(l_nodata_flag = TRUE) THEN
970
971 lv_tagged_parameters := lv_tagged_parameters||
972 pay_prl_xml_utils.getTag(
973 'C_G_CSR_GRE_OR_PRL_TOTAL_NODATA',
974 '1');
975 l_tag := '<G_CSR_GRE_OR_PRL_TOTAL>';
976 l_tag := l_tag||
977 pay_prl_xml_utils.getTag('GRE_OR_PAYROLL',NULL)||
978 pay_prl_xml_utils.getTag('UOM',NULL)||
979 pay_prl_xml_utils.getTag('CREDIT_AMOUNT',NULL)||
980 pay_prl_xml_utils.getTag('DEBIT_AMOUNT',NULL);
981
982 --Need to provide the Heading of the columns eventhough no data
983 --found.So here it's going to fill the Column heading data.
984 FOR i IN gtr_costing_segment.first .. gtr_costing_segment.last LOOP
985 l_tag := l_tag ||'<G_CSR_GRE_OR_PRL_TOTAL_SEGMENT>'||
986 pay_prl_xml_utils.getTag('SEGMENT',INITCAP(
987 gtr_costing_segment(i).segment_label))||
988 pay_prl_xml_utils.getTag('VALUE',NULL)||
989 '</G_CSR_GRE_OR_PRL_TOTAL_SEGMENT>';
990 END LOOP;
991
992 l_tag :=l_tag ||'</G_CSR_GRE_OR_PRL_TOTAL>';
993
994 DBMS_LOB.WRITEAPPEND(g_xml_data, length(l_tag), l_tag);
995 END IF;
996
997 EXIT;
998 END IF;
999
1000 --If no data is there, then need to fill one NodataFound Flag
1001 -- in the XML output.
1002 l_nodata_flag := FALSE;
1003 IF(l_rec_count = 0)THEN
1004 lv_tagged_parameters := lv_tagged_parameters||
1005 pay_prl_xml_utils.getTag(
1006 'C_G_CSR_GRE_OR_PRL_TOTAL_NODATA',
1007 '0');
1008 END IF;
1009 l_rec_count := l_rec_count + 1;
1010
1011 --Filling the data along with the XML tags into the local XML
1012 --data variable.
1013 l_tag := '<G_CSR_GRE_OR_PRL_TOTAL>';
1014
1015 l_tag := l_tag||
1016 pay_prl_xml_utils.getTag('GRE_OR_PAYROLL',lv_gre_or_payroll)||
1017 pay_prl_xml_utils.getTag('UOM',lv_uom)||
1018 pay_prl_xml_utils.getTag('CREDIT_AMOUNT',ln_credit_amount)||
1019 pay_prl_xml_utils.getTag('DEBIT_AMOUNT',ln_debit_amount);
1020
1021 DBMS_LOB.WRITEAPPEND(g_xml_data, length(l_tag), l_tag);
1022
1023 l_count := 1;
1024 --Filling the Cost Allocation KFF Values into the XML data Variable.
1025 FOR i IN gtr_costing_segment.first .. gtr_costing_segment.last LOOP
1026 IF gtr_costing_segment(i).column_name = 'SEGMENT1' THEN
1027 lv_segment_value := lv_segment1;
1028 elsIF gtr_costing_segment(i).column_name = 'SEGMENT2' THEN
1029 lv_segment_value := lv_segment2;
1030 elsIF gtr_costing_segment(i).column_name = 'SEGMENT3' THEN
1031 lv_segment_value := lv_segment3;
1032 elsIF gtr_costing_segment(i).column_name = 'SEGMENT4' THEN
1033 lv_segment_value := lv_segment4;
1034 elsIF gtr_costing_segment(i).column_name = 'SEGMENT5' THEN
1035 lv_segment_value := lv_segment5;
1036 elsIF gtr_costing_segment(i).column_name = 'SEGMENT6' THEN
1037 lv_segment_value := lv_segment6;
1038 elsIF gtr_costing_segment(i).column_name = 'SEGMENT7' THEN
1039 lv_segment_value := lv_segment7;
1040 elsIF gtr_costing_segment(i).column_name = 'SEGMENT8' THEN
1041 lv_segment_value := lv_segment8;
1042 elsIF gtr_costing_segment(i).column_name = 'SEGMENT9' THEN
1043 lv_segment_value := lv_segment9;
1044 elsIF gtr_costing_segment(i).column_name = 'SEGMENT10' THEN
1045 lv_segment_value := lv_segment10;
1046 elsIF gtr_costing_segment(i).column_name = 'SEGMENT11' THEN
1047 lv_segment_value := lv_segment11;
1048 elsIF gtr_costing_segment(i).column_name = 'SEGMENT12' THEN
1049 lv_segment_value := lv_segment12;
1050 elsIF gtr_costing_segment(i).column_name = 'SEGMENT13' THEN
1051 lv_segment_value := lv_segment13;
1052 elsIF gtr_costing_segment(i).column_name = 'SEGMENT14' THEN
1053 lv_segment_value := lv_segment14;
1054 elsIF gtr_costing_segment(i).column_name = 'SEGMENT15' THEN
1055 lv_segment_value := lv_segment15;
1056 elsIF gtr_costing_segment(i).column_name = 'SEGMENT16' THEN
1057 lv_segment_value := lv_segment16;
1058 elsIF gtr_costing_segment(i).column_name = 'SEGMENT17' THEN
1059 lv_segment_value := lv_segment17;
1060 elsIF gtr_costing_segment(i).column_name = 'SEGMENT18' THEN
1061 lv_segment_value := lv_segment18;
1062 elsIF gtr_costing_segment(i).column_name = 'SEGMENT19' THEN
1063 lv_segment_value := lv_segment19;
1064 elsIF gtr_costing_segment(i).column_name = 'SEGMENT20' THEN
1065 lv_segment_value := lv_segment20;
1066 END IF;
1067
1068 pay_prl_xml_utils.gXMLTable(l_count).Name := INITCAP(
1069 gtr_costing_segment(i).segment_label);
1070 pay_prl_xml_utils.gXMLTable(l_count).Value := lv_segment_value;
1071
1072 l_count := l_count + 1;
1073
1074 END LOOP ;
1075
1079 ,p_xml_data => g_xml_data);
1076 pay_prl_xml_utils.twoColumnar(p_type => 'G_CSR_GRE_OR_PRL_TOTAL_SEGMENT'
1077 ,p_data => pay_prl_xml_utils.gXMLTable
1078 ,p_count => l_count
1080 pay_prl_xml_utils.gXMLTable.delete;
1081
1082 l_tag := '</G_CSR_GRE_OR_PRL_TOTAL>';
1083
1084 --Appending the XML data into the Global XML data variable.
1085 DBMS_LOB.WRITEAPPEND(g_xml_data, length(l_tag), l_tag);
1086
1087 END LOOP;
1088 CLOSE c_costing_summary_rpt_details;
1089
1090 l_tag := '</LIST_G_CSR_GRE_OR_PRL_TOTAL>';
1091 DBMS_LOB.WRITEAPPEND(g_xml_data, length(l_tag), l_tag);
1092
1093 /********************************************************************
1094 ** This code is for populating the XML with the Costing Summary **
1095 ** Report for GRE/Payroll totals Depends on the parameter. **
1096 ********************************************************************/
1097
1098 pay_prl_xml_utils.gXMLTable.delete;
1099
1100 l_nodata_flag := TRUE;
1101 l_rec_count := 0;
1102 l_tag := '<LIST_G_GRE_OR_PRL_TOTAL>';
1103 DBMS_LOB.WRITEAPPEND(g_xml_data, length(l_tag), l_tag);
1104
1105 --Opening the Cursor for the data of the Third table
1106 --i.e., GRE/Payroll Totals Table
1107 OPEN c_get_gre_or_payroll_totals (lv_session_id
1108 ,p_business_group_id
1109 ,'CSR'
1110 ,p_sort_order1
1111 );
1112 LOOP
1113
1114 FETCH c_get_gre_or_payroll_totals INTO lv_gre_or_payroll
1115 ,lv_uom
1116 ,ln_credit_amount
1117 ,ln_debit_amount;
1118 IF c_get_gre_or_payroll_totals%NOTFOUND THEN
1119 hr_utility.set_location(gv_package_name ||
1120 '.costing_summary', 90);
1121
1122 --If no data is returned from the cursor then need to populate the
1123 --XML data with no values.
1124 IF(l_nodata_flag=TRUE) THEN
1125 lv_tagged_parameters := lv_tagged_parameters||
1126 pay_prl_xml_utils.getTag(
1127 'C_G_GRE_OR_PRL_TOTAL_NODATA',
1128 '1');
1129 l_tag := '<G_GRE_OR_PRL_TOTAL>';
1130 l_tag := l_tag||
1131 pay_prl_xml_utils.getTag('GRE_OR_PAYROLL',NULL)||
1132 pay_prl_xml_utils.getTag('CREDIT_AMOUNT',NULL)||
1133 pay_prl_xml_utils.getTag('DEBIT_AMOUNT',NULL)||
1134 pay_prl_xml_utils.getTag('UOM',NULL)||
1135 '</G_GRE_OR_PRL_TOTAL>';
1136 DBMS_LOB.WRITEAPPEND(g_xml_data, length(l_tag), l_tag);
1137 END IF;
1138 EXIT;
1139 END IF;
1140
1141 --If no data is there, then need to fill one NodataFound Flag
1142 -- in the XML output.
1143 l_nodata_flag := FALSE;
1144 IF(l_rec_count = 0)THEN
1145 lv_tagged_parameters := lv_tagged_parameters||
1146 pay_prl_xml_utils.getTag(
1147 'C_G_GRE_OR_PRL_TOTAL_NODATA',
1148 '0');
1149 END IF;
1150 l_rec_count := l_rec_count + 1;
1151 l_count := 1;
1152 pay_prl_xml_utils.gXMLTable(l_count).Name := 'GRE_OR_PAYROLL';
1153 pay_prl_xml_utils.gXMLTable(l_count).Value := lv_gre_or_payroll;
1154 l_count := l_count + 1;
1155
1156 pay_prl_xml_utils.gXMLTable(l_count).Name := 'CREDIT_AMOUNT';
1157 pay_prl_xml_utils.gXMLTable(l_count).Value := ln_credit_amount;
1158 l_count := l_count + 1;
1159
1160 pay_prl_xml_utils.gXMLTable(l_count).Name := 'DEBIT_AMOUNT';
1161 pay_prl_xml_utils.gXMLTable(l_count).Value := ln_debit_amount;
1162 l_count := l_count + 1;
1163
1164 pay_prl_xml_utils.gXMLTable(l_count).Name := 'UOM';
1165 pay_prl_xml_utils.gXMLTable(l_count).Value := lv_uom;
1166
1167 --Appending the XML data into the Global XML data variable by
1168 --invoking the multiColumnar in the pay_prl_xml_utils package.
1169 pay_prl_xml_utils.multiColumnar(
1170 'G_GRE_OR_PRL_TOTAL',
1171 pay_prl_xml_utils.gXMLTable,
1172 l_count,
1173 g_xml_data);
1174 pay_prl_xml_utils.gXMLTable.delete;
1175
1176 END LOOP;
1177 CLOSE c_get_gre_or_payroll_totals;
1178
1179 l_tag := '</LIST_G_GRE_OR_PRL_TOTAL>';
1180 DBMS_LOB.WRITEAPPEND(g_xml_data, length(l_tag), l_tag);
1181
1182 /********************************************************************
1183 * This code is for populating the XML with the Costing Summary *
1184 * Report for Grand Totals. *
1185 ********************************************************************/
1186
1187 pay_prl_xml_utils.gXMLTable.delete;
1188
1189 l_nodata_flag := TRUE;
1190 l_rec_count := 0;
1191 l_tag := '<LIST_G_CSR_GRAND_TOTAL>';
1192 DBMS_LOB.WRITEAPPEND(g_xml_data, length(l_tag), l_tag);
1193
1194 --Opening the Cursor for the data of the Fourth table
1195 --i.e., Costing Summary Report - Grand Totals Table
1196 OPEN c_costing_grand_totals (lv_session_id
1197 ,p_business_group_id
1198 ,'CSR'
1199 );
1200 LOOP
1201 FETCH c_costing_grand_totals INTO lv_uom
1202 ,ln_credit_amount
1203 ,ln_debit_amount
1204 ,lv_segment1
1205 ,lv_segment2
1206 ,lv_segment3
1207 ,lv_segment4
1208 ,lv_segment5
1212 ,lv_segment9
1209 ,lv_segment6
1210 ,lv_segment7
1211 ,lv_segment8
1213 ,lv_segment10
1214 ,lv_segment11
1215 ,lv_segment12
1216 ,lv_segment13
1217 ,lv_segment14
1218 ,lv_segment15
1219 ,lv_segment16
1220 ,lv_segment17
1221 ,lv_segment18
1222 ,lv_segment19
1223 ,lv_segment20;
1224
1225 lv_credit_sum:= lv_credit_sum + ln_credit_amount;
1226 lv_debit_sum:= lv_debit_sum + ln_debit_amount;
1227
1228 IF c_costing_grand_totals%NOTFOUND THEN
1229 hr_utility.set_location(gv_package_name ||
1230 '.costing_summary', 120);
1231
1232 IF (l_nodata_flag = TRUE)THEN
1233 lv_tagged_parameters := lv_tagged_parameters||
1234 pay_prl_xml_utils.getTag(
1235 'C_G_CSR_GRAND_TOTAL_NODATA',
1236 '1');
1237 l_tag := '<G_CSR_GRAND_TOTAL>';
1238
1239 l_tag := l_tag||
1240 pay_prl_xml_utils.getTag('UOM',NULL)||
1241 pay_prl_xml_utils.getTag('CREDIT_AMOUNT',NULL)||
1242 pay_prl_xml_utils.getTag('DEBIT_AMOUNT',NULL);
1243
1244 FOR i IN gtr_costing_segment.first .. gtr_costing_segment.last LOOP
1245 l_tag := l_tag ||'<G_CSR_GRAND_TOTAL_SEGMENT>'||
1246 pay_prl_xml_utils.getTag('SEGMENT',INITCAP(
1247 gtr_costing_segment(i).segment_label))||
1248 pay_prl_xml_utils.getTag('VALUE',NULL)||
1249 '</G_CSR_GRAND_TOTAL_SEGMENT>';
1250 END LOOP;
1251 l_tag := l_tag ||'</G_CSR_GRAND_TOTAL>';
1252 DBMS_LOB.WRITEAPPEND(g_xml_data, length(l_tag), l_tag);
1253 END IF;
1254 EXIT;
1255 END IF;
1256
1257 l_nodata_flag := FALSE;
1258 IF(l_rec_count = 0) THEN
1259 lv_tagged_parameters := lv_tagged_parameters||
1260 pay_prl_xml_utils.getTag(
1261 'C_G_CSR_GRAND_TOTAL_NODATA',
1262 '0');
1263 END IF;
1264 l_rec_count := l_rec_count + 1;
1265 l_tag := '<G_CSR_GRAND_TOTAL>';
1266
1267 l_tag := l_tag||
1268 pay_prl_xml_utils.getTag('UOM',lv_uom)||
1269 pay_prl_xml_utils.getTag('CREDIT_AMOUNT',ln_credit_amount)||
1270 pay_prl_xml_utils.getTag('DEBIT_AMOUNT',ln_debit_amount);
1271
1272 DBMS_LOB.WRITEAPPEND(g_xml_data, length(l_tag), l_tag);
1273
1274 l_count := 1;
1275 FOR i IN gtr_costing_segment.first .. gtr_costing_segment.last LOOP
1276 IF gtr_costing_segment(i).column_name = 'SEGMENT1' THEN
1277 lv_segment_value := lv_segment1;
1278 elsIF gtr_costing_segment(i).column_name = 'SEGMENT2' THEN
1279 lv_segment_value := lv_segment2;
1280 elsIF gtr_costing_segment(i).column_name = 'SEGMENT3' THEN
1281 lv_segment_value := lv_segment3;
1282 elsIF gtr_costing_segment(i).column_name = 'SEGMENT4' THEN
1283 lv_segment_value := lv_segment4;
1284 elsIF gtr_costing_segment(i).column_name = 'SEGMENT5' THEN
1285 lv_segment_value := lv_segment5;
1286 elsIF gtr_costing_segment(i).column_name = 'SEGMENT6' THEN
1287 lv_segment_value := lv_segment6;
1288 elsIF gtr_costing_segment(i).column_name = 'SEGMENT7' THEN
1289 lv_segment_value := lv_segment7;
1290 elsIF gtr_costing_segment(i).column_name = 'SEGMENT8' THEN
1291 lv_segment_value := lv_segment8;
1292 elsIF gtr_costing_segment(i).column_name = 'SEGMENT9' THEN
1293 lv_segment_value := lv_segment9;
1294 elsIF gtr_costing_segment(i).column_name = 'SEGMENT10' THEN
1295 lv_segment_value := lv_segment10;
1296 elsIF gtr_costing_segment(i).column_name = 'SEGMENT11' THEN
1297 lv_segment_value := lv_segment11;
1298 elsIF gtr_costing_segment(i).column_name = 'SEGMENT12' THEN
1299 lv_segment_value := lv_segment12;
1300 elsIF gtr_costing_segment(i).column_name = 'SEGMENT13' THEN
1301 lv_segment_value := lv_segment13;
1302 elsIF gtr_costing_segment(i).column_name = 'SEGMENT14' THEN
1303 lv_segment_value := lv_segment14;
1304 elsIF gtr_costing_segment(i).column_name = 'SEGMENT15' THEN
1305 lv_segment_value := lv_segment15;
1306 elsIF gtr_costing_segment(i).column_name = 'SEGMENT16' THEN
1307 lv_segment_value := lv_segment16;
1308 elsIF gtr_costing_segment(i).column_name = 'SEGMENT17' THEN
1309 lv_segment_value := lv_segment17;
1310 elsIF gtr_costing_segment(i).column_name = 'SEGMENT18' THEN
1311 lv_segment_value := lv_segment18;
1312 elsIF gtr_costing_segment(i).column_name = 'SEGMENT19' THEN
1313 lv_segment_value := lv_segment19;
1314 elsIF gtr_costing_segment(i).column_name = 'SEGMENT20' THEN
1315 lv_segment_value := lv_segment20;
1316 END IF;
1317
1318 pay_prl_xml_utils.gXMLTable(l_count).Name := INITCAP(
1319 gtr_costing_segment(i).segment_label);
1323
1320 pay_prl_xml_utils.gXMLTable(l_count).Value := lv_segment_value;
1321
1322 l_count := l_count + 1;
1324 END LOOP ;
1325
1326 pay_prl_xml_utils.twoColumnar(p_type => 'G_CSR_GRAND_TOTAL_SEGMENT'
1327 ,p_data => pay_prl_xml_utils.gXMLTable
1328 ,p_count => l_count
1329 ,p_xml_data => g_xml_data);
1330
1331 pay_prl_xml_utils.gXMLTable.delete;
1332
1333 l_tag := '</G_CSR_GRAND_TOTAL>';
1334 DBMS_LOB.WRITEAPPEND(g_xml_data, length(l_tag), l_tag);
1335
1336 END LOOP;
1337 CLOSE c_costing_grand_totals;
1338
1339 l_tag := '</LIST_G_CSR_GRAND_TOTAL>';
1340 DBMS_LOB.WRITEAPPEND(g_xml_data, length(l_tag), l_tag);
1341
1342 /********************************************************************
1343 * This code is for populating the XML with the Costing Summary *
1344 * Report for Report Totals. *
1345 ********************************************************************/
1346
1347 pay_prl_xml_utils.gXMLTable.delete;
1348 l_rec_count := 0;
1349
1350 l_tag := '<LIST_G_REPORT_TOTAL>';
1351 DBMS_LOB.WRITEAPPEND(g_xml_data, length(l_tag), l_tag);
1352
1353 hr_utility.trace('Session id : '||lv_session_id);
1354 OPEN c_get_report_totals(lv_session_id,p_business_group_id,'CSR');
1355 LOOP
1356 FETCH c_get_report_totals INTO
1357 lv_uom
1358 ,lv_credit_sum
1359 ,lv_debit_sum;
1360
1361 IF c_get_report_totals%NOTFOUND THEN
1362 hr_utility.set_location(gv_package_name || '.costing_summary', 150);
1363 IF (l_nodata_flag = TRUE) THEN
1364 lv_tagged_parameters := lv_tagged_parameters||
1365 pay_prl_xml_utils.getTag(
1366 'C_G_REPORT_TOTAL_NODATA',
1367 '1');
1368 l_tag := '<G_REPORT_TOTAL>';
1369 l_tag := l_tag||
1370 pay_prl_xml_utils.getTag('CREDIT_AMOUNT',NULL)||
1371 pay_prl_xml_utils.getTag('DEBIT_AMOUNT',NULL)||
1372 pay_prl_xml_utils.getTag('UOM',NULL)||
1373 '</G_REPORT_TOTAL>';
1374
1375 DBMS_LOB.WRITEAPPEND(g_xml_data, length(l_tag), l_tag);
1376 END IF;
1377 EXIT;
1378 END IF;
1379
1380 l_nodata_flag := FALSE;
1381
1382 IF(l_rec_count = 0) THEN
1383 lv_tagged_parameters := lv_tagged_parameters||
1384 pay_prl_xml_utils.getTag(
1385 'C_G_REPORT_TOTAL_NODATA',
1386 '0');
1387 END IF;
1388 l_rec_count := l_rec_count + 1;
1389
1390 l_count := 1;
1391 pay_prl_xml_utils.gXMLTable(l_count).Name := 'CREDIT_AMOUNT';
1392 pay_prl_xml_utils.gXMLTable(l_count).Value := lv_credit_sum;
1393 l_count := l_count + 1;
1394
1395 pay_prl_xml_utils.gXMLTable(l_count).Name := 'DEBIT_AMOUNT';
1396 pay_prl_xml_utils.gXMLTable(l_count).Value := lv_debit_sum;
1397 l_count := l_count + 1;
1398
1399 pay_prl_xml_utils.gXMLTable(l_count).Name := 'UOM';
1400 pay_prl_xml_utils.gXMLTable(l_count).Value := lv_uom;
1401
1402 pay_prl_xml_utils.multiColumnar(
1403 'G_REPORT_TOTAL',
1404 pay_prl_xml_utils.gXMLTable,
1405 l_count,
1406 g_xml_data);
1407 pay_prl_xml_utils.gXMLTable.delete;
1408
1409 END LOOP;
1410 CLOSE c_get_report_totals;
1411
1412 l_tag := '</LIST_G_REPORT_TOTAL>';
1413 DBMS_LOB.WRITEAPPEND(g_xml_data, length(l_tag), l_tag);
1414
1415 DBMS_LOB.WRITEAPPEND(g_xml_data, length(lv_tagged_parameters),
1416 lv_tagged_parameters);
1417
1418 l_tag := '</PAYRPCSR>';
1419 DBMS_LOB.WRITEAPPEND(g_xml_data, length(l_tag), l_tag);
1420
1421 DELETE FROM pay_us_rpt_totals WHERE attribute1='CSR';
1422
1423 p_xml := g_xml_data;
1424
1425
1426 --
1427 END costing_summary;
1428 --
1429 --
1430 END pay_costing_summary_x_rep_pkg;