[Home] [Help]
PACKAGE BODY: APPS.PAY_COSTING_SUMMARY_REP_PKG
Source
1 PACKAGE BODY pay_costing_summary_rep_pkg AS
2 /* $Header: pyrpcsrp.pkb 120.3.12010000.4 2009/05/05 04:55:23 ankagarw ship $ */
3
4 /************************************************************
5 ** Local Package Variables
6 ************************************************************/
7 gv_title VARCHAR2(100):= 'Costing Summary Report';
8 gc_csv_delimiter VARCHAR2(1) := ',';
9 gc_csv_data_delimiter VARCHAR2(1) := '"';
10 gv_html_start_data VARCHAR2(5) := '<td>' ;
11 gv_html_END_data VARCHAR2(5) := '</td>' ;
12
13 gv_package_name VARCHAR2(50) := 'pay_costing_summary_rep_pkg';
14 gv_title1 VARCHAR2(100);
15 gv_title2 VARCHAR2(100);
16
17
18 /************************************************************
19 ** Procedure: formated_title_page
20 **
21 ** Purpose : This function displays the title part of the
22 ** report that shows the Concurrent program
23 ** parameters passed.
24 ************************************************************/
25
26 PROCEDURE formated_title_page(
27 p_output_file_type in VARCHAR2
28 ,p_business_group in VARCHAR2
29 ,p_start_date in date
30 ,p_end_date in date
31 ,p_costing in VARCHAR2
32 ,p_payroll_name in VARCHAR2
33 ,p_consolidation_set_name in VARCHAR2
34 ,p_gre_name in VARCHAR2
35 ,p_include_accruals in VARCHAR2
36 ,p_sort_order1 in VARCHAR2
37 ,p_sort_order2 in VARCHAR2
38 )
39 IS
40 lv_payroll_name varchar2(240);
41 lv_consolidation_set_name varchar2(240);
42 lv_gre_name VARCHAR2(240);
43 lv_include_accruals VARCHAR2(240);
44 lv_sort_order1 VARCHAR2(240);
45 lv_sort_order2 VARCHAR2(240);
46 lv_start_date VARCHAR2(20) := to_char(p_start_date,'DD-MON-YYYY'); --Bug 3305391
47 lv_end_date VARCHAR2(20) := to_char(p_end_date,'DD-MON-YYYY'); --Bug 3305391
48
49 BEGIN
50 IF p_output_file_type ='HTML' THEN
51 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, '<body>');
52 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, '<table border=0 align=CENTER>');
53 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, '<tr>');
54 IF p_payroll_name IS NULL THEN
55 lv_payroll_name:=' ';
56 ELSE lv_payroll_name:= p_payroll_name;
57 END IF;
58 IF p_consolidation_set_name IS NULL THEN
59 lv_consolidation_set_name:=' ';
60 ELSE lv_consolidation_set_name:=p_consolidation_set_name;
61 END IF;
62 IF p_gre_name IS NULL THEN
63 lv_gre_name:=' ';
64 ELSE
65 lv_gre_name:=p_gre_name;
66 END IF;
67 IF p_include_accruals IS NULL THEN
68 lv_include_accruals:=' ';
69 ELSE lv_include_accruals:=p_include_accruals;
70 END IF;
71 IF p_sort_order1 IS NULL THEN
72 lv_sort_order1:=' ';
73 ELSE lv_sort_order1:=p_sort_order1;
74 END IF;
75 IF p_sort_order2 IS NULL THEN
76 lv_sort_order2:=' ';
77 ELSE lv_sort_order2:=p_sort_order2;
78 END IF;
79 END IF;
80 hr_utility.set_location(gv_package_name || '.formated_title_page.',10);
81 FND_FILE.PUT_LINE(fnd_file.output,pay_us_payroll_utils.formated_data_string(
82 p_input_string=>'Business Group: '
83 ,p_output_file_type=>p_output_file_type
84 ,p_bold=>'N')||
85 pay_us_payroll_utils.formated_data_string(
86 p_input_string=>p_business_group
87 ,p_output_file_type=>p_output_file_type
88 ,p_bold=>'N'));
89
90 IF p_output_file_type ='HTML' THEN
91 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, '</tr><tr>');
92 END IF;
93
94 FND_FILE.PUT_LINE(fnd_file.output,pay_us_payroll_utils.formated_data_string(
95 p_input_string=>'Costing Effective Date Begin: '
96 ,p_output_file_type=>p_output_file_type
97 ,p_bold=>'N')||
98 pay_us_payroll_utils.formated_data_string(
99 p_input_string=>lv_start_date
100 ,p_output_file_type=>p_output_file_type
101 ,p_bold=>'N'));
102
103
104 IF p_output_file_type ='HTML' THEN
105 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, '</tr><tr>');
106 END IF;
107
108 FND_FILE.PUT_LINE(fnd_file.output,pay_us_payroll_utils.formated_data_string(
109 p_input_string=>'Costing Effective Date End: '
110 ,p_output_file_type=>p_output_file_type
111 ,p_bold=>'N')||
112 pay_us_payroll_utils.formated_data_string(
113 p_input_string=>lv_end_date
114 ,p_output_file_type=>p_output_file_type
115 ,p_bold=>'N'));
116
117
118 IF p_output_file_type ='HTML' THEN
119 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, '</tr>');
120 END IF;
121
122 IF p_costing IS NOT NULL THEN
123 FND_FILE.PUT_LINE(fnd_file.output,pay_us_payroll_utils.formated_data_string(
124 p_input_string=>'Costing Process: '
125 ,p_output_file_type=>p_output_file_type
126 ,p_bold=>'N')||
127 pay_us_payroll_utils.formated_data_string(
128 p_input_string=>lv_start_date ||'('||p_costing||')'
129 ,p_output_file_type=>p_output_file_type
130 ,p_bold=>'N'));
131 ELSE
132 FND_FILE.PUT_LINE(fnd_file.output,pay_us_payroll_utils.formated_data_string(
133 p_input_string=>'Costing Process: '
134 ,p_output_file_type=>p_output_file_type
135 ,p_bold=>'N'));
136 END IF;
137
138
139
140 IF p_output_file_type ='HTML' THEN
141 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, '</tr><tr>');
142 END IF;
143
144 FND_FILE.PUT_LINE(fnd_file.output,pay_us_payroll_utils.formated_data_string(
145 p_input_string=>'Payroll Name: '
146 ,p_output_file_type=>p_output_file_type
147 ,p_bold=>'N')||
148 pay_us_payroll_utils.formated_data_string(
149 p_input_string=>lv_payroll_name
150 ,p_output_file_type=>p_output_file_type
151 ,p_bold=>'N'));
152
153
154 IF p_output_file_type ='HTML' THEN
155 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, '</tr><tr>');
156 END IF;
157
158 FND_FILE.PUT_LINE(fnd_file.output,pay_us_payroll_utils.formated_data_string(
159 p_input_string=>'Consolidation Set: '
160 ,p_output_file_type=>p_output_file_type
161 ,p_bold=>'N')||
162 pay_us_payroll_utils.formated_data_string(
163 p_input_string=>lv_consolidation_set_name
164 ,p_output_file_type=>p_output_file_type
165 ,p_bold=>'N'));
166
167
168 IF p_output_file_type ='HTML' THEN
169 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, '</tr><tr>');
170 END IF;
171
172 FND_FILE.PUT_LINE(fnd_file.output,pay_us_payroll_utils.formated_data_string(
173 p_input_string=>'GRE: '
174 ,p_output_file_type=>p_output_file_type
175 ,p_bold=>'N')||
176 pay_us_payroll_utils.formated_data_string(
177 p_input_string=>lv_gre_name
178 ,p_output_file_type=>p_output_file_type
179 ,p_bold=>'N'));
180
181
182 IF p_output_file_type ='HTML' THEN
183 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, '</tr><tr>');
184 END IF;
185
186 FND_FILE.PUT_LINE(fnd_file.output,pay_us_payroll_utils.formated_data_string(
187 p_input_string=>'Include Accruals: '
188 ,p_output_file_type=>p_output_file_type
189 ,p_bold=>'N')||
190 pay_us_payroll_utils.formated_data_string(
191 p_input_string=>lv_include_accruals
192 ,p_output_file_type=>p_output_file_type
193 ,p_bold=>'N'));
194
195
196 IF p_output_file_type ='HTML' THEN
197 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, '</tr><tr>');
198 END IF;
199
200 FND_FILE.PUT_LINE(fnd_file.output,pay_us_payroll_utils.formated_data_string(
201 p_input_string=>'Sort Option One: '
202 ,p_output_file_type=>p_output_file_type
203 ,p_bold=>'N')||
204 pay_us_payroll_utils.formated_data_string(
205 p_input_string=>lv_sort_order1
206 ,p_output_file_type=>p_output_file_type
207 ,p_bold=>'N'));
208
209
210 IF p_output_file_type ='HTML' THEN
211 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, '</tr><tr>');
212 END IF;
213
214 FND_FILE.PUT_LINE(fnd_file.output,pay_us_payroll_utils.formated_data_string(
215 p_input_string=>'Sort Option Two: '
216 ,p_output_file_type=>p_output_file_type
217 ,p_bold=>'N')||
218 pay_us_payroll_utils.formated_data_string(
219 p_input_string=>lv_sort_order2
220 ,p_output_file_type=>p_output_file_type
221 ,p_bold=>'N'));
222
223
224 IF p_output_file_type ='HTML' THEN
225 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, '</tr></table><br></br>');
226 END IF;
227 hr_utility.set_location(gv_package_name || '.formated_title_page.',20);
228
229 END formated_title_page;
230
231 /************************************************************
232 ** Procedure: formated_static_header
233 ** Returns : Concatenated Title strings for the first table
234 ** that contains costing info
235 ** Purpose : This procedure is used to get the concatenated
236 ** title information for the first table of costing
237 ** info.
238 ************************************************************/
239
240 PROCEDURE formated_static_header(
241 p_output_file_type in VARCHAR2
242 ,p_static_label1 out nocopy VARCHAR2
243 ,p_static_label2 out nocopy VARCHAR2
244 )
245 IS
246
247 lv_format1 VARCHAR2(32000);
248 lv_format2 VARCHAR2(32000);
249
250 BEGIN
251 hr_utility.set_location(gv_package_name || '.formated_static_header.',10);
252 lv_format1:= pay_us_payroll_utils.formated_data_string(p_input_string=>'Payroll'
253 ,p_output_file_type=>p_output_file_type
254 ,p_bold=>'Y')||
255 pay_us_payroll_utils.formated_data_string(p_input_string=>'GRE'
256 ,p_output_file_type=>p_output_file_type
257 ,p_bold=>'Y')||
258 pay_us_payroll_utils.formated_data_string(p_input_string=>'Input Value Name'
259 ,p_output_file_type=>p_output_file_type
260 ,p_bold=>'Y')||
261 pay_us_payroll_utils.formated_data_string(p_input_string=>'Unit Of Measure'
262 ,p_output_file_type=>p_output_file_type
263 ,p_bold=>'Y');
264
265
266 lv_format2:= pay_us_payroll_utils.formated_data_string(p_input_string=>'Credit Amount'
267 ,p_output_file_type=>p_output_file_type
268 ,p_bold=>'Y')||
269 pay_us_payroll_utils.formated_data_string(p_input_string=>'Debit Amount'
270 ,p_output_file_type=>p_output_file_type
271 ,p_bold=>'Y')||
272 pay_us_payroll_utils.formated_data_string(p_input_string=>'Accrual Type'
273 ,p_output_file_type=>p_output_file_type
274 ,p_bold=>'Y');
275
276 p_static_label1 := lv_format1;
277 p_static_label2 := lv_format2;
278 hr_utility.trace('Static Label1 = ' || lv_format1);
279 hr_utility.trace('Static Label2 = ' || lv_format2);
280 hr_utility.set_location(gv_package_name || '.formated_static_header', 20);
281
282 END formated_static_header;
283
284 /************************************************************
285 ** Procedure: formated_totals_header
286 ** Returns : Concatenated Title strings for the second table
287 ** that contains cost center wise costing totals
288 ** for each payroll or GRE
289 ** Purpose : This procedure is used to get the concatenated
290 ** title information for the second table that
291 ** contains costing totals for each payroll or GRE
292 ************************************************************/
293
294 PROCEDURE formated_totals_header(p_sort_order1 in VARCHAR2
295 ,p_output_file_type in VARCHAR2
296 ,p_static_label1 out nocopy VARCHAR2
297 ,p_static_label2 out nocopy VARCHAR2
298 )
299 IS
300
301 lv_format1 VARCHAR2(32000);
302 lv_format2 VARCHAR2(32000);
303 lv_gre_or_payroll VARCHAR2(240);
304
305 BEGIN
306
307 hr_utility.set_location(gv_package_name || '.formated_totals_header', 20);
308 IF upper(p_sort_order1)='PAYROLL NAME' THEN
309 lv_gre_or_payroll:='Payroll Name';
310 ELSE lv_gre_or_payroll:='GRE Name';
311 END IF;
312
313
314 lv_format1:= pay_us_payroll_utils.formated_data_string(p_input_string=>lv_gre_or_payroll
315 ,p_output_file_type=>p_output_file_type
316 ,p_bold=>'Y')||
317 pay_us_payroll_utils.formated_data_string(p_input_string=>'Unit of Measure'
318 ,p_output_file_type=>p_output_file_type
319 ,p_bold=>'Y');
320
321
322 lv_format2:= pay_us_payroll_utils.formated_data_string(p_input_string=>'Credit Amount'
323 ,p_output_file_type=>p_output_file_type
324 ,p_bold=>'Y')||
325 pay_us_payroll_utils.formated_data_string(p_input_string=>'Debit Amount'
326 ,p_output_file_type=>p_output_file_type
327 ,p_bold=>'Y');
328
329 p_static_label1 := lv_format1;
330 p_static_label2 := lv_format2;
331 hr_utility.trace('Static Label1 = ' || lv_format1);
332 hr_utility.trace('Static Label2 = ' || lv_format2);
333 hr_utility.set_location(gv_package_name || '.formated_totals_header', 20);
334
335 END formated_totals_header;
336
337 /************************************************************
338 ** Procedure: formated_cons_totals_header1
339 ** Returns : Concatenated Title strings for the third table
340 ** that contains consolidated costing totals for
341 ** the selected payroll or GRE
342 ** Purpose : This procedure is used to get the concatenated
343 ** title information for the third table that
344 ** contains consolidated costing totals for the
345 ** selected payroll or GRE
346 ************************************************************/
347
348 PROCEDURE formated_cons_totals_header1(p_sort_order1 in VARCHAR2
349 ,p_output_file_type in VARCHAR2
350 ,p_static_label out nocopy VARCHAR2
351 )
352 IS
353
354 lv_format VARCHAR2(32000);
355 lv_gre_or_payroll VARCHAR2(240);
356
357 BEGIN
358 hr_utility.set_location(gv_package_name || '.formated_cons_totals_header1', 10);
359 IF upper(p_sort_order1)='PAYROLL NAME' THEN
360 lv_gre_or_payroll:='Payroll Name';
361 ELSE lv_gre_or_payroll:='GRE Name';
362 END IF;
363
364 lv_format:= pay_us_payroll_utils.formated_data_string(p_input_string=>lv_gre_or_payroll
365 ,p_output_file_type=>p_output_file_type
366 ,p_bold=>'Y')||
367 pay_us_payroll_utils.formated_data_string(p_input_string=>'Credit Amount'
368 ,p_output_file_type=>p_output_file_type
369 ,p_bold=>'Y')||
370 pay_us_payroll_utils.formated_data_string(p_input_string=>'Debit Amount'
371 ,p_output_file_type=>p_output_file_type
372 ,p_bold=>'Y')||
373 pay_us_payroll_utils.formated_data_string(p_input_string=>'Unit of Measure'
374 ,p_output_file_type=>p_output_file_type
375 ,p_bold=>'Y');
376
377 p_static_label := lv_format;
378 hr_utility.trace('Static Label = ' || lv_format);
379 hr_utility.set_location(gv_package_name || '.formated_cons_total_header1', 20);
380
381 END formated_cons_totals_header1;
382
383 /************************************************************
384 ** Procedure: formated_cons_totals_header2
385 ** Returns : Concatenated Title strings for the last table
386 ** that contains consolidated report totals
387 ** Purpose : This procedure is used to get the concatenated
388 ** title information for the last table that
389 ** contains consolidated report totals
390 ************************************************************/
391
392
393 PROCEDURE formated_cons_totals_header2(
394 p_output_file_type in VARCHAR2
395 ,p_static_label out nocopy VARCHAR2
396 )
397 IS
398
399 lv_format VARCHAR2(32000);
400
401 BEGIN
402 hr_utility.set_location(gv_package_name || '.formated_cons_totals_header2', 10);
403 lv_format:= pay_us_payroll_utils.formated_data_string(p_input_string=>'Credit Amount'
404 ,p_output_file_type=>p_output_file_type
405 ,p_bold=>'Y')||
406 pay_us_payroll_utils.formated_data_string(p_input_string=>'Debit Amount'
407 ,p_output_file_type=>p_output_file_type
408 ,p_bold=>'Y')||
409 pay_us_payroll_utils.formated_data_string(p_input_string=>'Unit of Measure'
410 ,p_output_file_type=>p_output_file_type
411 ,p_bold=>'Y') ;
412
413
414 p_static_label := lv_format;
415 hr_utility.trace('Static Label = ' || lv_format);
416 hr_utility.set_location(gv_package_name || '.formated_cons_total_header2', 20);
417
418 END formated_cons_totals_header2;
419
420 /************************************************************
421 ** Procedure: formated_grand_totals_header
422 ** Returns : Concatenated Title strings for the fourth table
423 ** that contains cost center wise grand totals for
424 ** the report
425 ** Purpose : This procedure is used to get the concatenated
426 ** title information for the fourth table that
427 ** contains report totals
428 ************************************************************/
429
430 PROCEDURE formated_grand_totals_header(
431 p_output_file_type in VARCHAR2
432 ,p_static_label1 out nocopy VARCHAR2
433 )
434 IS
435
436 lv_format VARCHAR2(32000);
437
438
439 BEGIN
440
441 hr_utility.set_location(gv_package_name || '.formated_grand_totals_header', 10);
442 lv_format:= pay_us_payroll_utils.formated_data_string(p_input_string=>'Unit of Measure'
443 ,p_output_file_type=>p_output_file_type
444 ,p_bold=>'Y')||
445 pay_us_payroll_utils.formated_data_string(p_input_string=>'Credit Amount'
446 ,p_output_file_type=>p_output_file_type
447 ,p_bold=>'Y')||
448 pay_us_payroll_utils.formated_data_string(p_input_string=>'Debit Amount'
449 ,p_output_file_type=>p_output_file_type
450 ,p_bold=>'Y');
451
452 p_static_label1 := lv_format;
453 hr_utility.trace('Static Label1 = ' || lv_format);
454 hr_utility.set_location(gv_package_name || '.formated_grand_totals_header', 20);
455
456 END formated_grand_totals_header;
457
458 /************************************************************
459 ** Procedure: formated_data_row
460 ** Returns : Concatenated data values for the first table
461 ** that contains costing info
462 ** Purpose : This procedure is used to get the concatenated
463 ** data values for the first table of costing
464 ** info.
465 ************************************************************/
466
467 PROCEDURE formated_data_row (
468 p_payroll_name in VARCHAR2
469 ,p_gre_name in VARCHAR2
470 ,p_input_value_name in VARCHAR2
471 ,p_uom in VARCHAR2
472 ,p_credit_amount in NUMBER
473 ,p_debit_amount in NUMBER
474 ,p_accrual_type in VARCHAR2
475 ,p_output_file_type in VARCHAR2
476 ,p_static_data1 out nocopy VARCHAR2
477 ,p_static_data2 out nocopy VARCHAR2
478 )
479 IS
480
481 lv_format1 VARCHAR2(32000);
482 lv_format2 VARCHAR2(32000);
483
484
485 BEGIN
486
487 hr_utility.set_location(gv_package_name || '.formated_data_row', 10);
488
489 lv_format1 :=
490 pay_us_payroll_utils.formated_data_string (p_input_string=>p_payroll_name
491 ,p_output_file_type=>p_output_file_type
492 ,p_bold=>'N'
493 )||
494 pay_us_payroll_utils.formated_data_string (p_input_string=>p_gre_name
495 ,p_output_file_type=>p_output_file_type
496 ,p_bold=>'N'
497 )||
498 pay_us_payroll_utils.formated_data_string (p_input_string=>p_input_value_name
499 ,p_output_file_type=>p_output_file_type
500 ,p_bold=>'N'
501 )||
502 pay_us_payroll_utils.formated_data_string (p_input_string=>p_uom
503 ,p_output_file_type=>p_output_file_type
504 ,p_bold=>'N'
505 );
506
507
508 lv_format2 :=
509 pay_us_payroll_utils.formated_data_string (p_input_string=>p_credit_amount
510 ,p_output_file_type=>p_output_file_type
511 ,p_bold=>'N')||
512 pay_us_payroll_utils.formated_data_string (p_input_string=>p_debit_amount
513 ,p_output_file_type=>p_output_file_type
514 ,p_bold=>'N')||
515 pay_us_payroll_utils.formated_data_string (p_input_string=>p_accrual_type
516 ,p_output_file_type=>p_output_file_type
517 ,p_bold=>'N'
518 );
519
520 hr_utility.set_location(gv_package_name || '.formated_data_row', 20);
521
522 p_static_data1 := lv_format1;
523 p_static_data2 := lv_format2;
524 hr_utility.trace('Static Data1 = ' || lv_format1);
525 hr_utility.trace('Static Data2 = ' || lv_format2);
526 hr_utility.set_location(gv_package_name || '.formated_static_data', 30);
527
528 END formated_data_row;
529
530 /************************************************************
531 ** Procedure: formated_totals
532 ** Returns : Concatenated data values for the second table
533 ** that contains cost center wise costing totals
534 ** for each payroll or GRE
535 ** Purpose : This procedure is used to get the concatenated
536 ** data values for the second table that
537 ** contains costing totals for each payroll or GRE
538 ************************************************************/
539
540 PROCEDURE formated_totals(p_gre_or_payroll in VARCHAR2
541 ,p_uom in VARCHAR2
542 ,p_credit_amount in NUMBER
543 ,p_debit_amount in NUMBER
544 ,p_output_file_type in VARCHAR2
545 ,p_static_data1 out nocopy VARCHAR2
546 ,p_static_data2 out nocopy VARCHAR2
547 ) IS
548 lv_format1 VARCHAR2(32000);
549 lv_format2 VARCHAR2(32000);
550
551 BEGIN
552 hr_utility.set_location(gv_package_name || '.formated_totals', 10);
553 lv_format1:=
554 pay_us_payroll_utils.formated_data_string (p_input_string=>p_gre_or_payroll
555 ,p_output_file_type=>p_output_file_type
556 ,p_bold=>'N'
557 )||
558 pay_us_payroll_utils.formated_data_string (p_input_string=>p_uom
559 ,p_output_file_type=>p_output_file_type
560 ,p_bold=>'N'
561 );
562 lv_format2:=
563 pay_us_payroll_utils.formated_data_string(p_input_string=>p_credit_amount
564 ,p_output_file_type=>p_output_file_type
565 ,p_bold=>'N'
566 )||
567 pay_us_payroll_utils.formated_data_string(p_input_string=>p_debit_amount
568 ,p_output_file_type=>p_output_file_type
569 ,p_bold=>'N'
570 );
571 p_static_data1 := lv_format1;
572 p_static_data2 := lv_format2;
573 hr_utility.trace('Static Data1 = ' || lv_format1);
574 hr_utility.trace('Static Data2 = ' || lv_format2);
575 hr_utility.set_location(gv_package_name || '.formated_totals', 20);
576
577 END formated_totals;
578
579 /************************************************************
580 ** Procedure: formated_cons_totals1
581 ** Returns : Concatenated data values for the third table
582 ** that contains consolidated costing totals for
583 ** the selected payroll or GRE
584 ** Purpose : This procedure is used to get the concatenated
585 ** data values for the third table that
586 ** contains consolidated costing totals for the
587 ** selected payroll or GRE
588 ************************************************************/
589
590 PROCEDURE formated_cons_totals1(p_gre_or_payroll in VARCHAR2
591 ,p_uom in VARCHAR2
592 ,p_credit_amount in NUMBER
593 ,p_debit_amount in NUMBER
594 ,p_output_file_type in VARCHAR2
595 ,p_static_data out nocopy VARCHAR2
596 ) IS
597 lv_format VARCHAR2(32000);
598
599
600 BEGIN
601 hr_utility.set_location(gv_package_name || '.formated_cons_totals1', 10);
602 lv_format:=
603 pay_us_payroll_utils.formated_data_string (p_input_string=>p_gre_or_payroll
604 ,p_output_file_type=>p_output_file_type
605 ,p_bold=>'N'
606 )||
607
608 pay_us_payroll_utils.formated_data_string(p_input_string=>p_credit_amount
609 ,p_output_file_type=>p_output_file_type
610 ,p_bold=>'N'
611 )||
612 pay_us_payroll_utils.formated_data_string(p_input_string=>p_debit_amount
613 ,p_output_file_type=>p_output_file_type
614 ,p_bold=>'N'
615 )||
616 pay_us_payroll_utils.formated_data_string (p_input_string=>p_uom
617 ,p_output_file_type=>p_output_file_type
618 ,p_bold=>'N'
619 );
620 p_static_data := lv_format;
621 hr_utility.trace('Static Data = ' || lv_format);
622 hr_utility.set_location(gv_package_name || '.formated_cons_totals1', 10);
623
624 END formated_cons_totals1;
625
626 /************************************************************
627 ** Procedure: formated_cons_totals2
628 ** Returns : Concatenated data values for the last table
629 ** that contains consolidated report totals
630 ** Purpose : This procedure is used to get the concatenated
631 ** data values for the last table that
632 ** contains consolidated report totals
633 ************************************************************/
634
635 PROCEDURE formated_cons_totals2( p_uom in VARCHAR2
636 ,p_credit_amount in NUMBER
637 ,p_debit_amount in NUMBER
638 ,p_output_file_type in VARCHAR2
639 ,p_static_data out nocopy VARCHAR2
640 ) IS
641 lv_format VARCHAR2(32000);
642
643
644 BEGIN
645 hr_utility.set_location(gv_package_name || '.formated_cons_totals2', 10);
646 lv_format:= pay_us_payroll_utils.formated_data_string(p_input_string=>p_credit_amount
647 ,p_output_file_type=>p_output_file_type
648 ,p_bold=>'N'
649 )||
650 pay_us_payroll_utils.formated_data_string(p_input_string=>p_debit_amount
651 ,p_output_file_type=>p_output_file_type
652 ,p_bold=>'N'
653 )||
654 pay_us_payroll_utils.formated_data_string (p_input_string=>p_uom
655 ,p_output_file_type=>p_output_file_type
656 ,p_bold=>'N'
657 );
658 p_static_data := lv_format;
659 hr_utility.trace('Static Data = ' || lv_format);
660 hr_utility.set_location(gv_package_name || '.formated_cons_totals2', 20);
661
662 END formated_cons_totals2;
663
664 /************************************************************
665 ** Procedure: formated_grand_totals
666 ** Returns : Concatenated data values for the fourth table
667 ** that contains cost center wise grand totals for
668 ** the report
669 ** Purpose : This procedure is used to get the concatenated
670 ** data values for the fourth table that
671 ** contains report totals
672 ************************************************************/
673
674 PROCEDURE formated_grand_totals(p_uom in VARCHAR2
675 ,p_credit_amount in NUMBER
676 ,p_debit_amount in NUMBER
677 ,p_output_file_type in VARCHAR2
678 ,p_static_data1 out nocopy VARCHAR2
679 ) IS
680 lv_format VARCHAR2(32000);
681
682 BEGIN
683 hr_utility.set_location(gv_package_name || '.formated_grand_totals', 10);
684 lv_format:=
685 pay_us_payroll_utils.formated_data_string(p_input_string=>p_uom
686 ,p_output_file_type=>p_output_file_type
687 ,p_bold=>'N'
688 )||
689 pay_us_payroll_utils.formated_data_string(p_input_string=>p_credit_amount
690 ,p_output_file_type=>p_output_file_type
691 ,p_bold=>'N'
692 )||
693 pay_us_payroll_utils.formated_data_string(p_input_string=>p_debit_amount
694 ,p_output_file_type=>p_output_file_type
695 ,p_bold=>'N'
696 );
697 p_static_data1 := lv_format;
698 hr_utility.trace('Static Data1 = ' || lv_format);
699 hr_utility.set_location(gv_package_name || '.formated_grand_totals', 20);
700
701 END formated_grand_totals;
702
703 /******************************************************************
704 Function for returning the optional where clause for the cursor
705 c_asg_costing_details
706 Bug 3946996
707 ******************************************************************/
708
709 function get_optional_where_clause(cp_payroll_id in number
710 ,cp_consolidation_set_id in number
711 ,cp_tax_unit_id in number
712 ,cp_costing_process_flag VARCHAR2
713 ,cp_costing in VARCHAR2
714 ,cp_cost_type in VARCHAR2)
715 return varchar2 is
716
717 dynamic_where_clause varchar2(10000);
718
719 begin
720
721 if cp_consolidation_set_id is not null then
722 dynamic_where_clause := ' and pcd.consolidation_set_id = '|| to_char(cp_consolidation_set_id);
723 end if;
724
725 if cp_payroll_id is not null then
726 dynamic_where_clause := dynamic_where_clause || ' and pcd.payroll_id = '|| to_char(cp_payroll_id);
727 end if;
728
729 if cp_tax_unit_id is not null then
730 dynamic_where_clause:= dynamic_where_clause || ' and pcd.tax_unit_id = ' || to_char(cp_tax_unit_id);
731 end if;
732
733 if cp_costing_process_flag ='Y' then
734 dynamic_where_clause := dynamic_where_clause || ' and pcd.payroll_action_id = ' || cp_costing;
735 end if;
736
737 if cp_cost_type is null then
738 dynamic_where_clause := dynamic_where_clause || ' and pcd.cost_type = ''COST_TMP''' ;
739 elsif cp_cost_type = 'EST_MODE_COST' then
740 dynamic_where_clause := dynamic_where_clause || ' and pcd.cost_type in (''COST_TMP'',''EST_COST'') ';
741 elsif cp_cost_type = 'EST_MODE_ALL' then
742 dynamic_where_clause := dynamic_where_clause || ' and pcd.cost_type in (''COST_TMP'',''EST_COST'',''EST_REVERSAL'') ';
743 end if;
744
745 return dynamic_where_clause;
746
747 end get_optional_where_clause;
748
749
750 /********************************* End function Bug 3946996 ***************************/
751
752 /************************************************************
753 ** Procedure: costing_summary
754 **
755 ** Purpose : This procedure is the one that is called from
756 ** the concurrent program
757 ************************************************************/
758
759 PROCEDURE costing_summary (
760 errbuf out nocopy VARCHAR2
761 ,retcode out nocopy NUMBER
762 ,p_business_group_id in NUMBER
763 ,p_start_date in VARCHAR2
764 ,p_dummy_start in VARCHAR2
765 ,p_END_date in VARCHAR2
766 ,p_costing in VARCHAR2
767 ,p_dummy_END in VARCHAR2
768 ,p_payroll_id in NUMBER
769 ,p_consolidation_set_id in NUMBER
770 ,p_tax_unit_id in NUMBER
771 ,p_cost_type in VARCHAR2
772 ,p_sort_order1 in VARCHAR2
773 ,p_sort_order2 in VARCHAR2
774 ,p_output_file_type in VARCHAR2
775 ) IS
776
777 TYPE cur_type is REF CURSOR; -- Bug 3946996
778 c_asg_costing_details cur_type; --Bug 3946996
779
780 c_query varchar2(5000); --for the cursor query (Bug 3946996)
781 c_clause1 varchar2(5000); --to store the optional where clause (Bug 3946996)
782
783 /**********************************************************
784 CURSOR to get the Business group name
785 ************************************************************/
786 CURSOR c_get_organization_name (cp_organization_id in NUMBER) IS
787 SELECT name
788 FROM hr_organization_units
789 WHERE organization_id=cp_organization_id;
790
791 /***********************************************************
792 CURSORs to get payroll,consolidation set names
793 ***********************************************************/
794 CURSOR c_get_payroll_name (cp_payroll_id in NUMBER) IS
795 SELECT payroll_name
796 FROM pay_payrolls_f
797 WHERE payroll_id = cp_payroll_id;
798
799 CURSOR c_get_consolidation_set_name (cp_consolidation_set_id in NUMBER) IS
800 SELECT consolidation_set_name
801 FROM pay_consolidation_sets
802 WHERE consolidation_set_id=cp_consolidation_set_id;
803
804 /***********************************************************
805 CURSOR to get effective date for a payroll action id
806 ************************************************************/
807
808 CURSOR c_get_effective_date(cp_payroll_action_id in NUMBER) IS
809 SELECT effective_date
810 FROM pay_payroll_actions
811 WHERE payroll_action_id=cp_payroll_action_id;
812
813 CURSOR c_get_accruals(cp_cost_type in VARCHAR2) IS
814 SELECT nvl(hr_general.decode_lookup('PAY_PAYRPCBR',cp_cost_type),' ')
815 FROM dual;
816
817
818 /************************************************************
819 ** Cursor to get the Costing flex which IS setup at
820 ** Business Group.
821 ************************************************************/
822 CURSOR c_costing_flex_id (cp_business_group_id in NUMBER) IS
823 SELECT org_information7
824 FROM hr_organization_information hoi
825 WHERE organization_id = cp_business_group_id
826 and org_information_context = 'Business Group Information';
827
828 /************************************************************
829 ** Cursor returns all the segments defined for the Costing
830 ** Flex which are enabled and displayed.
831 ************************************************************/
832 CURSOR c_costing_flex_segments (cp_id_flex_num in NUMBER) IS
833 SELECT segment_name, application_column_name
834 FROM fnd_id_flex_segments
835 WHERE id_flex_code = 'COST'
836 and id_flex_num = cp_id_flex_num
837 and enabled_flag = 'Y'
838 and display_flag = 'Y'
839 ORDER BY segment_num;
840
841 CURSOR c_get_session_id IS
842 SELECT userenv('sessionid')
843 FROM dual;
844
845 /************************************************************
846 ** Cursor returns payroll/gre totals
847 ************************************************************/
848 CURSOR c_costing_summary_rpt_details (cp_session_id in NUMBER
849 ,cp_business_group_id in NUMBER
850 ,cp_csr in VARCHAR2
851 ,cp_sort_order1 in VARCHAR2
852 ,cp_sort_order2 in VARCHAR2) IS
853 SELECT decode(upper(cp_sort_order1),'PAYROLL NAME',attribute32
854 ,gre_name)
855 ,attribute34 --UOM
856 ,sum(value1)
857 ,sum(value2)
858 ,attribute1
859 ,attribute2
860 ,attribute3
861 ,attribute4
862 ,attribute5
863 ,attribute6
864 ,attribute7
865 ,attribute8
866 ,attribute9
867 ,attribute10
868 ,attribute11
869 ,attribute12
870 ,attribute13
871 ,attribute14
872 ,attribute15
873 ,attribute16
874 ,attribute17
875 ,attribute18
876 ,attribute19
877 ,attribute20
878 ,attribute21
879 ,attribute22
880 ,attribute23
881 ,attribute24
882 ,attribute25
883 ,attribute26
884 ,attribute27
885 ,attribute28
886 ,attribute29
887 ,attribute30
888 FROM pay_us_rpt_totals
889 WHERE business_group_id=cp_business_group_id
890 and attribute31=cp_csr
891 and session_id=cp_session_id
892 GROUP BY decode(upper(cp_sort_order1), 'PAYROLL NAME',attribute32,
893 gre_name)
894 ,attribute1
895 ,attribute2
896 ,attribute3
897 ,attribute4
898 ,attribute5
899 ,attribute6
900 ,attribute7
901 ,attribute8
902 ,attribute9
903 ,attribute10
904 ,attribute11
905 ,attribute12
906 ,attribute13
907 ,attribute14
908 ,attribute15
909 ,attribute16
910 ,attribute17
911 ,attribute18
912 ,attribute19
913 ,attribute20
914 ,attribute21
915 ,attribute22
916 ,attribute23
917 ,attribute24
918 ,attribute25
919 ,attribute26
920 ,attribute27
921 ,attribute28
922 ,attribute29
923 ,attribute30
924 ,attribute34
925 order by
926 attribute1
927 ,attribute2
928 ,attribute3
929 ,attribute4
930 ,attribute5
931 ,attribute6
932 ,attribute7
933 ,attribute8
934 ,attribute9
935 ,attribute10
936 ,attribute11
937 ,attribute12
938 ,attribute13
939 ,attribute14
940 ,attribute15
941 ,attribute16
942 ,attribute17
943 ,attribute18
944 ,attribute19
945 ,attribute20
946 ,attribute21
947 ,attribute22
948 ,attribute23
949 ,attribute24
950 ,attribute25
951 ,attribute26
952 ,attribute27
953 ,attribute28
954 ,attribute29
955 ,attribute30
956 ,attribute34
957 ;
958
959 /************************************************************
960 ** Cursor returns grand totals
961
962 ************************************************************/
963
964
965 CURSOR c_costing_grand_totals (cp_session_id in NUMBER
966 ,cp_business_group_id in NUMBER
967 ,cp_csr in VARCHAR2
968 ) IS
969 SELECT attribute34 --UOM
970 ,sum(value1)
971 ,sum(value2)
972 ,attribute1
973 ,attribute2
974 ,attribute3
975 ,attribute4
976 ,attribute5
977 ,attribute6
978 ,attribute7
979 ,attribute8
980 ,attribute9
981 ,attribute10
982 ,attribute11
983 ,attribute12
984 ,attribute13
985 ,attribute14
986 ,attribute15
987 ,attribute16
988 ,attribute17
989 ,attribute18
990 ,attribute19
991 ,attribute20
992 ,attribute21
993 ,attribute22
994 ,attribute23
995 ,attribute24
996 ,attribute25
997 ,attribute26
998 ,attribute27
999 ,attribute28
1000 ,attribute29
1001 ,attribute30
1002 FROM pay_us_rpt_totals
1003 WHERE business_group_id=cp_business_group_id
1004 AND attribute31=cp_csr
1005 AND session_id=cp_session_id
1006 GROUP BY
1007 attribute1
1008 ,attribute2
1009 ,attribute3
1010 ,attribute4
1011 ,attribute5
1012 ,attribute6
1013 ,attribute7
1014 ,attribute8
1015 ,attribute9
1016 ,attribute10
1017 ,attribute11
1018 ,attribute12
1019 ,attribute13
1020 ,attribute14
1021 ,attribute15
1022 ,attribute16
1023 ,attribute17
1024 ,attribute18
1025 ,attribute19
1026 ,attribute20
1027 ,attribute21
1028 ,attribute22
1029 ,attribute23
1030 ,attribute24
1031 ,attribute25
1032 ,attribute26
1033 ,attribute27
1034 ,attribute28
1035 ,attribute29
1036 ,attribute30
1037 ,attribute34
1038 order by
1039 attribute1
1040 ,attribute2
1041 ,attribute3
1042 ,attribute4
1043 ,attribute5
1044 ,attribute6
1045 ,attribute7
1046 ,attribute8
1047 ,attribute9
1048 ,attribute10
1049 ,attribute11
1050 ,attribute12
1051 ,attribute13
1052 ,attribute14
1053 ,attribute15
1054 ,attribute16
1055 ,attribute17
1056 ,attribute18
1057 ,attribute19
1058 ,attribute20
1059 ,attribute21
1060 ,attribute22
1061 ,attribute23
1062 ,attribute24
1063 ,attribute25
1064 ,attribute26
1065 ,attribute27
1066 ,attribute28
1067 ,attribute29
1068 ,attribute30
1069 ,attribute34
1070 ;
1071
1072
1073 /**************************************************************
1074 Cursor to get GRE/Payroll totals
1075 *************************************************************/
1076 CURSOR c_get_gre_or_payroll_totals(cp_session_id in NUMBER
1077 ,cp_business_group_id in NUMBER
1078 ,cp_total_flag in VARCHAR2
1079 ,cp_sort_order1 in VARCHAR2
1080 ) IS
1081 SELECT decode(upper(cp_sort_order1), 'PAYROLL NAME',attribute32,
1082 gre_name)
1083 ,attribute34 --UOM
1084 ,sum(value1)
1085 ,sum(value2)
1086 FROM pay_us_rpt_totals
1087 WHERE session_id=cp_session_id
1088 AND business_group_id=cp_business_group_id
1089 AND attribute31=cp_total_flag
1090 GROUP BY decode(upper(cp_sort_order1), 'PAYROLL NAME',attribute32,
1091 gre_name)
1092 ,attribute34;
1093
1094 /**************************************************************
1095 CURSOR to get report total
1096 **************************************************************/
1097 CURSOR c_get_report_totals (cp_session_id in NUMBER
1098 ,cp_business_group_id in NUMBER
1099 ,cp_total_flag in VARCHAR2
1100 ) IS
1101 SELECT attribute34 --UOM
1102 ,sum(value1)
1103 ,sum(value2)
1104 FROM pay_us_rpt_totals
1105 WHERE session_id=cp_session_id
1106 AND business_group_id=cp_business_group_id
1107 AND attribute31=cp_total_flag
1108 GROUP BY attribute34;
1109
1110
1111
1112 /*************************************************************
1113 ** Local Variables
1114 *************************************************************/
1115 lv_consolidation_set_name VARCHAR2(100);
1116 lv_business_group_name VARCHAR2(100);
1117 lv_payroll_name VARCHAR2(100);
1118 lv_gre_name VARCHAR2(240);
1119 lv_input_value_name VARCHAR2(100);
1120 lv_uom VARCHAR2(100);
1121 ln_credit_amount NUMBER;
1122 ln_debit_amount NUMBER;
1123 lv_effective_date DATE;
1124 lv_concatenated_segments VARCHAR2(200);
1125 lv_segment1 VARCHAR2(200);
1126 lv_segment2 VARCHAR2(200);
1127 lv_segment3 VARCHAR2(200);
1128 lv_segment4 VARCHAR2(200);
1129 lv_segment5 VARCHAR2(200);
1130 lv_segment6 VARCHAR2(200);
1131 lv_segment7 VARCHAR2(200);
1132 lv_segment8 VARCHAR2(200);
1133 lv_segment9 VARCHAR2(200);
1134 lv_segment10 VARCHAR2(200);
1135 lv_segment11 VARCHAR2(200);
1136 lv_segment12 VARCHAR2(200);
1137 lv_segment13 VARCHAR2(200);
1138 lv_segment14 VARCHAR2(200);
1139 lv_segment15 VARCHAR2(200);
1140 lv_segment16 VARCHAR2(200);
1141 lv_segment17 VARCHAR2(200);
1142 lv_segment18 VARCHAR2(200);
1143 lv_segment19 VARCHAR2(200);
1144 lv_segment20 VARCHAR2(200);
1145 lv_segment21 VARCHAR2(200);
1146 lv_segment22 VARCHAR2(200);
1147 lv_segment23 VARCHAR2(200);
1148 lv_segment24 VARCHAR2(200);
1149 lv_segment25 VARCHAR2(200);
1150 lv_segment26 VARCHAR2(200);
1151 lv_segment27 VARCHAR2(200);
1152 lv_segment28 VARCHAR2(200);
1153 lv_segment29 VARCHAR2(200);
1154 lv_segment30 VARCHAR2(200);
1155 ln_costing_id_flex_num NUMBER;
1156 lv_segment_name VARCHAR2(100);
1157 lv_segment_value VARCHAR2(100);
1158 lv_column_name VARCHAR2(100);
1159
1160 lv_header_label VARCHAR2(32000);
1161 lv_header_label1 VARCHAR2(32000);
1162 lv_header_label2 VARCHAR2(32000);
1163 lv_cost_flex_header VARCHAR2(32000):= NULL;
1164
1165 lv_data_row VARCHAR2(32000);
1166 lv_data_row1 VARCHAR2(32000);
1167 lv_data_row2 VARCHAR2(32000);
1168
1169 ln_count NUMBER := 0;
1170 lv_accrual_type VARCHAR2(100);
1171 lv_cost_mode VARCHAR2(100);
1172
1173 ltr_costing_segment costing_tab;
1174
1175 lv_gre_or_payroll VARCHAR2(240);
1176 lv_session_id NUMBER;
1177 lv_credit_sum NUMBER;
1178 lv_debit_sum NUMBER;
1179 lv_total_heading VARCHAR2(240);
1180
1181 lv_start_date date;
1182 lv_END_date date;
1183 lv_costing_process_flag VARCHAR2(1) := 'N';
1184 lv_include_accruals VARCHAR2(100);
1185
1186 /*sackumar testing*/
1187 i_sackumar number;
1188 BEGIN
1189
1190 hr_utility.set_location(gv_package_name || '.costing_summary', 10);
1191 hr_utility.trace('Start Date = ' || p_start_date);
1192 hr_utility.trace('End Date = ' || p_END_date);
1193 hr_utility.trace('Business Group ID = '|| p_business_group_id);
1194 hr_utility.trace('Costing Process = ' || p_costing);
1195 hr_utility.trace('Payroll ID = ' || p_payroll_id);
1196 hr_utility.trace('Consolidation Set ID = ' || p_consolidation_set_id);
1197 hr_utility.trace('Tax unit ID = ' || p_tax_unit_id);
1198 hr_utility.trace('Cost Type = ' || p_cost_type);
1199 hr_utility.trace('Sort Order 1 = ' || p_sort_order1);
1200 hr_utility.trace('Sort Order 2 = ' || p_sort_order2);
1201 hr_utility.trace('Output File Type = ' || p_output_file_type);
1202
1203 formated_static_header(p_output_file_type,
1204 lv_header_label1,
1205 lv_header_label2);
1206 hr_utility.trace('Header Label 1 = ' || lv_header_label1);
1207 hr_utility.trace('Header Label 2 = ' || lv_header_label2);
1208
1209 lv_header_label:=lv_header_label1;
1210 OPEN c_costing_flex_id (p_business_group_id);
1211 FETCH c_costing_flex_id into ln_costing_id_flex_num;
1212 IF c_costing_flex_id%found THEN
1213 hr_utility.set_location(gv_package_name || '.costing_summary', 20);
1214 OPEN c_costing_flex_segments (ln_costing_id_flex_num);
1215 LOOP
1216 FETCH c_costing_flex_segments into lv_segment_name, lv_column_name;
1217 IF c_costing_flex_segments%notfound THEN
1218 exit;
1219 END IF;
1220 lv_header_label := lv_header_label ||
1221 pay_us_payroll_utils.formated_data_string (p_input_string=>lv_segment_name
1222 ,p_bold=>'Y'
1223 ,p_output_file_type=>p_output_file_type);
1224 lv_cost_flex_header:= lv_cost_flex_header ||
1225 pay_us_payroll_utils.formated_data_string (p_input_string=>lv_segment_name
1226 ,p_bold=>'Y'
1227 ,p_output_file_type=>p_output_file_type);
1228 ltr_costing_segment(ln_count).segment_label := lv_segment_name;
1229 ltr_costing_segment(ln_count).column_name := lv_column_name;
1230 ln_count := ln_count + 1;
1231 END LOOP;
1232 CLOSE c_costing_flex_segments;
1233
1234 /*sackumar testing */
1235 i_sackumar :=0;
1236 hr_utility.trace('data from cursor c_costing_flex_segments stored in ltr_costing_segment PL/SQL table');
1237 for i_sackumar in ltr_costing_segment.first .. ltr_costing_segment.last LOOP
1238 hr_utility.trace(ltr_costing_segment(i_sackumar).segment_label||'='||ltr_costing_segment(i_sackumar).column_name);
1239 end loop;
1240 hr_utility.trace('ends data from cursor c_costing_flex_segments ');
1241 /*end of sac kumar testing */
1242
1243 END IF;
1244 CLOSE c_costing_flex_id;
1245 lv_header_label:=lv_header_label||lv_header_label2;
1246
1247 FND_FILE.PUT_LINE(fnd_file.output,pay_us_payroll_utils.formated_header_string(
1248 gv_title
1249 ,p_output_file_type
1250 ));
1251 OPEN c_get_organization_name(p_business_group_id);
1252 FETCH c_get_organization_name into lv_business_group_name;
1253 CLOSE c_get_organization_name;
1254
1255 OPEN c_get_organization_name(p_tax_unit_id);
1256 FETCH c_get_organization_name into lv_gre_name;
1257 CLOSE c_get_organization_name;
1258
1259 OPEN c_get_payroll_name(p_payroll_id);
1260 FETCH c_get_payroll_name into lv_payroll_name;
1261 CLOSE c_get_payroll_name;
1262
1263 OPEN c_get_consolidation_set_name(p_consolidation_set_id);
1264 FETCH c_get_consolidation_set_name into lv_consolidation_set_name;
1265 CLOSE c_get_consolidation_set_name;
1266
1267 hr_utility.set_location(gv_package_name || '.costing_summary', 30);
1268
1269 lv_include_accruals:= nvl(hr_general.decode_lookup('PAY_PAYRPCBR',p_cost_type),' ');
1270 IF p_output_file_type='HTML' AND lv_include_accruals = ' ' THEN
1271 lv_include_accruals:=' ';
1272 END IF;
1273
1274 IF p_costing IS not NULL THEN
1275 hr_utility.trace('to_NUMBER(p_costing)='||to_NUMBER(p_costing));
1276 OPEN c_get_effective_date(to_NUMBER(p_costing));
1277 FETCH c_get_effective_date into lv_start_date;
1278 CLOSE c_get_effective_date;
1279 lv_end_date := lv_start_date;
1280 lv_costing_process_flag:='Y';
1281 ELSE
1282 lv_start_date :=to_date(p_start_date, 'YYYY/MM/DD HH24:MI:SS');
1283 lv_end_date :=to_date(p_end_date, 'YYYY/MM/DD HH24:MI:SS');
1284 lv_costing_process_flag:='N';
1285 END IF;
1286
1287 hr_utility.set_location(gv_package_name || '.costing_summary', 40);
1288
1289 formated_title_page(p_output_file_type=> p_output_file_type
1290 ,p_business_group => lv_business_group_name
1291 ,p_start_date =>lv_start_date
1292 ,p_END_date =>lv_END_date
1293 ,p_costing =>p_costing
1294 ,p_payroll_name =>lv_payroll_name
1295 ,p_consolidation_set_name=>lv_consolidation_set_name
1296 ,p_gre_name =>lv_gre_name
1297 ,p_include_accruals=>lv_include_accruals
1298 ,p_sort_order1 =>p_sort_order1
1299 ,p_sort_order2 =>p_sort_order2
1300 );
1301
1302 hr_utility.set_location(gv_package_name || '.costing_summary', 50);
1303
1304 /****************************************************************
1305 ** Print the Header Information. If the format IS HTML THEN OPEN
1306 ** the body and table before printing the header info, otherwISe
1307 ** just print the header information.
1308 ****************************************************************/
1309 IF p_output_file_type ='HTML' THEN
1310 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, '<table border=1 align=CENTER>');
1311 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, '<tr>');
1312 END IF;
1313
1314 FND_FILE.PUT_LINE(fnd_file.output, lv_header_label);
1315
1316 IF p_output_file_type ='HTML' THEN
1317 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, '</tr>');
1318 END IF;
1319
1320 OPEN c_get_session_id;
1321 FETCH c_get_session_id into lv_session_id;
1322 CLOSE c_get_session_id;
1323 hr_utility.trace('Sort Option 1 = ' || p_sort_order1);
1324 hr_utility.trace('Sort Option 2 = ' || p_sort_order2);
1325
1326 /************* changed to ref cursor 3946996 */
1327
1328 c_clause1 :=get_optional_where_clause(p_payroll_id,
1329 p_consolidation_set_id,
1330 p_tax_unit_id,
1331 lv_costing_process_flag,
1332 p_costing,
1333 p_cost_type);
1334
1335 hr_utility.trace('c_clause1 = ' || c_clause1);
1336
1337 c_query := 'SELECT
1338 pcd.payroll_name
1339 ,pcd.gre_name
1340 ,pcd.input_value_name
1341 ,pcd.uom
1342 ,sum(pcd.credit_amount)
1343 ,sum(pcd.debit_amount)
1344 ,pcd.cost_type
1345 ,pcd.concatenated_segments
1346 ,pcd.segment1
1347 ,pcd.segment2
1348 ,pcd.segment3
1349 ,pcd.segment4
1350 ,pcd.segment5
1351 ,pcd.segment6
1352 ,pcd.segment7
1353 ,pcd.segment8
1354 ,pcd.segment9
1355 ,pcd.segment10
1356 ,pcd.segment11
1357 ,pcd.segment12
1358 ,pcd.segment13
1359 ,pcd.segment14
1360 ,pcd.segment15
1361 ,pcd.segment16
1362 ,pcd.segment17
1363 ,pcd.segment18
1364 ,pcd.segment19
1365 ,pcd.segment20
1366 ,pcd.segment21
1367 ,pcd.segment22
1368 ,pcd.segment23
1369 ,pcd.segment24
1370 ,pcd.segment25
1371 ,pcd.segment26
1372 ,pcd.segment27
1373 ,pcd.segment28
1374 ,pcd.segment29
1375 ,pcd.segment30
1376 FROM pay_costing_details_v pcd
1377 WHERE
1378 pcd.effective_date between :cp_start_date and :cp_end_date
1379 ' || c_clause1 || '
1380 and pcd.business_group_id = :cp_business_group_id
1381 GROUP BY pcd.payroll_name,pcd.gre_name
1382 ,pcd.input_value_name
1383 ,pcd.uom,pcd.cost_type
1384 ,pcd.concatenated_segments
1385 ,pcd.segment1
1386 ,pcd.segment2
1387 ,pcd.segment3
1388 ,pcd.segment4
1389 ,pcd.segment5
1390 ,pcd.segment6
1391 ,pcd.segment7
1392 ,pcd.segment8
1393 ,pcd.segment9
1394 ,pcd.segment10
1395 ,pcd.segment11
1396 ,pcd.segment12
1397 ,pcd.segment13
1398 ,pcd.segment14
1399 ,pcd.segment15
1400 ,pcd.segment16
1401 ,pcd.segment17
1402 ,pcd.segment18
1403 ,pcd.segment19
1404 ,pcd.segment20
1405 ,pcd.segment21
1406 ,pcd.segment22
1407 ,pcd.segment23
1408 ,pcd.segment24
1409 ,pcd.segment25
1410 ,pcd.segment26
1411 ,pcd.segment27
1412 ,pcd.segment28
1413 ,pcd.segment29
1414 ,pcd.segment30
1415 ORDER BY pcd.cost_type
1416 ,decode (upper(:cp_sort_order1), ''PAYROLL NAME'', pcd.payroll_name,
1417 pcd.gre_name)
1418 ,decode(upper(:cp_sort_order2), ''GRE'', pcd.gre_name,''PAYROLL NAME'',
1419 pcd.payroll_name,''X'')
1420 ,pcd.segment1
1421 ,pcd.segment2
1422 ,pcd.segment3
1423 ,pcd.segment4
1424 ,pcd.segment5
1425 ,pcd.segment6
1426 ,pcd.segment7
1427 ,pcd.segment8
1428 ,pcd.segment9
1429 ,pcd.segment10
1430 ,pcd.segment11
1431 ,pcd.segment12
1432 ,pcd.segment13
1433 ,pcd.segment14
1434 ,pcd.segment15
1435 ,pcd.segment16
1436 ,pcd.segment17
1437 ,pcd.segment18
1438 ,pcd.segment19
1439 ,pcd.segment20
1440 ,pcd.segment21
1441 ,pcd.segment22
1442 ,pcd.segment23
1443 ,pcd.segment24
1444 ,pcd.segment25
1445 ,pcd.segment26
1446 ,pcd.segment27
1447 ,pcd.segment28
1448 ,pcd.segment29
1449 ,pcd.segment30';
1450
1451
1452 OPEN c_asg_costing_details
1453 FOR c_query USING lv_start_date
1454 ,lv_end_date
1455 ,p_business_group_id
1456 ,p_sort_order1
1457 ,p_sort_order2;
1458
1459 hr_utility.trace('Start Date for Query = '||lv_start_date);
1460 hr_utility.trace('End Date for Query = '||lv_end_date);
1461 hr_utility.trace('Bussiness Group for Query = '||p_business_group_id);
1462 hr_utility.trace('Short Order 1 for Query = '||p_sort_order1);
1463 hr_utility.trace('Short Order 2 for Query = '||p_sort_order2);
1464
1465 LOOP
1466 FETCH c_asg_costing_details into
1467 lv_payroll_name
1468 ,lv_gre_name
1469 ,lv_input_value_name
1470 ,lv_uom
1471 ,ln_credit_amount
1472 ,ln_debit_amount
1473 ,lv_cost_mode
1474 ,lv_concatenated_segments
1475 ,lv_segment1
1476 ,lv_segment2
1477 ,lv_segment3
1478 ,lv_segment4
1479 ,lv_segment5
1480 ,lv_segment6
1481 ,lv_segment7
1482 ,lv_segment8
1483 ,lv_segment9
1484 ,lv_segment10
1485 ,lv_segment11
1486 ,lv_segment12
1487 ,lv_segment13
1488 ,lv_segment14
1489 ,lv_segment15
1490 ,lv_segment16
1491 ,lv_segment17
1492 ,lv_segment18
1493 ,lv_segment19
1494 ,lv_segment20
1495 ,lv_segment21
1496 ,lv_segment22
1497 ,lv_segment23
1498 ,lv_segment24
1499 ,lv_segment25
1500 ,lv_segment26
1501 ,lv_segment27
1502 ,lv_segment28
1503 ,lv_segment29
1504 ,lv_segment30;
1505
1506 IF c_asg_costing_details%notfound THEN
1507 hr_utility.set_location(gv_package_name || '.costing_summary', 60);
1508 exit;
1509 END IF;
1510
1511 hr_utility.trace('Record No (After Main Query) - '||c_asg_costing_details%rowcount);
1512
1513 lv_accrual_type:=nvl(hr_general.decode_lookup('PAY_PAYRPCBR',lv_cost_mode),' ');
1514 IF p_output_file_type='HTML' AND lv_accrual_type = ' ' THEN
1515 lv_accrual_type:=' ';
1516 END IF;
1517
1518 /*insert into pay_us_rpt_totals*/
1519 /*sackumar : this data is used in the report for geting the other section
1520 here atrributes1 to 30 used to store the values of the Segments 1 to 30
1521 and Attribute31 = 'CSR'
1522 Attribute32 = Payroll Name
1523 Attribute33 = Concatenated Segments Value
1524 Attribute34 = UOM
1525 */
1526 insert into pay_us_rpt_totals(session_id,business_group_id,gre_name,value1 ,value2 ,attribute1,attribute2
1527 ,attribute3,attribute4,attribute5,attribute6,attribute7,attribute8
1528 ,attribute9,attribute10,attribute11,attribute12,attribute13
1529 ,attribute14,attribute15,attribute16,attribute17,attribute18
1530 ,attribute19,attribute20,attribute21,attribute22,attribute23
1531 ,attribute24,attribute25,attribute26,attribute27,attribute28
1532 ,attribute29,attribute30,attribute31,attribute32,attribute33
1533 ,attribute34) values
1534 (lv_session_id -- session ID is passed
1535 ,p_business_group_id
1536 ,lv_gre_name
1537 ,ln_credit_amount
1538 ,ln_debit_amount
1539 ,lv_segment1
1540 ,lv_segment2
1541 ,lv_segment3
1542 ,lv_segment4
1543 ,lv_segment5
1544 ,lv_segment6
1545 ,lv_segment7
1546 ,lv_segment8
1547 ,lv_segment9
1548 ,lv_segment10
1549 ,lv_segment11
1550 ,lv_segment12
1551 ,lv_segment13
1552 ,lv_segment14
1553 ,lv_segment15
1554 ,lv_segment16
1555 ,lv_segment17
1556 ,lv_segment18
1557 ,lv_segment19
1558 ,lv_segment20
1559 ,lv_segment21
1560 ,lv_segment22
1561 ,lv_segment23
1562 ,lv_segment24
1563 ,lv_segment25
1564 ,lv_segment26
1565 ,lv_segment27
1566 ,lv_segment28
1567 ,lv_segment29
1568 ,lv_segment30
1569 ,'CSR' --attribute31 -- denotes that the record is for Costing Summary Report
1570 ,lv_payroll_name --attribute32
1571 ,lv_concatenated_segments --attribute33
1572 ,lv_uom --attribute34
1573 );
1574
1575 hr_utility.set_location(gv_package_name || '.costing_summary', 70);
1576
1577
1578 formated_data_row(p_payroll_name => lv_payroll_name
1579 ,p_gre_name => lv_gre_name
1580 ,p_input_value_name => lv_input_value_name
1581 ,p_uom => lv_uom
1582 ,p_credit_amount => ln_credit_amount
1583 ,p_debit_amount => ln_debit_amount
1584 ,p_accrual_type => lv_accrual_type
1585 ,p_output_file_type=> p_output_file_type
1586 ,p_static_data1 => lv_data_row1
1587 ,p_static_data2 => lv_data_row2
1588 ) ;
1589 hr_utility.set_location(gv_package_name || '.costing_summary', 80);
1590 hr_utility.trace('lv_data_row1 = ' || lv_data_row1);
1591 hr_utility.trace('lv_data_row2 = ' || lv_data_row2);
1592
1593
1594 lv_data_row:= lv_data_row1;
1595
1596 for i in ltr_costing_segment.first .. ltr_costing_segment.last LOOP
1597 IF ltr_costing_segment(i).column_name = 'SEGMENT1' THEN
1598 lv_segment_value := lv_segment1;
1599 elsIF ltr_costing_segment(i).column_name = 'SEGMENT2' THEN
1600 lv_segment_value := lv_segment2;
1601 elsIF ltr_costing_segment(i).column_name = 'SEGMENT3' THEN
1602 lv_segment_value := lv_segment3;
1603 elsIF ltr_costing_segment(i).column_name = 'SEGMENT4' THEN
1604 lv_segment_value := lv_segment4;
1605 elsIF ltr_costing_segment(i).column_name = 'SEGMENT5' THEN
1606 lv_segment_value := lv_segment5;
1607 elsIF ltr_costing_segment(i).column_name = 'SEGMENT6' THEN
1608 lv_segment_value := lv_segment6;
1609 elsIF ltr_costing_segment(i).column_name = 'SEGMENT7' THEN
1610 lv_segment_value := lv_segment7;
1611 elsIF ltr_costing_segment(i).column_name = 'SEGMENT8' THEN
1612 lv_segment_value := lv_segment8;
1613 elsIF ltr_costing_segment(i).column_name = 'SEGMENT9' THEN
1614 lv_segment_value := lv_segment9;
1615 elsIF ltr_costing_segment(i).column_name = 'SEGMENT10' THEN
1616 lv_segment_value := lv_segment10;
1617 elsIF ltr_costing_segment(i).column_name = 'SEGMENT11' THEN
1618 lv_segment_value := lv_segment11;
1619 elsIF ltr_costing_segment(i).column_name = 'SEGMENT12' THEN
1620 lv_segment_value := lv_segment12;
1621 elsIF ltr_costing_segment(i).column_name = 'SEGMENT13' THEN
1622 lv_segment_value := lv_segment13;
1623 elsIF ltr_costing_segment(i).column_name = 'SEGMENT14' THEN
1624 lv_segment_value := lv_segment14;
1625 elsIF ltr_costing_segment(i).column_name = 'SEGMENT15' THEN
1626 lv_segment_value := lv_segment15;
1627 elsIF ltr_costing_segment(i).column_name = 'SEGMENT16' THEN
1628 lv_segment_value := lv_segment16;
1629 elsIF ltr_costing_segment(i).column_name = 'SEGMENT17' THEN
1630 lv_segment_value := lv_segment17;
1631 elsIF ltr_costing_segment(i).column_name = 'SEGMENT18' THEN
1632 lv_segment_value := lv_segment18;
1633 elsIF ltr_costing_segment(i).column_name = 'SEGMENT19' THEN
1634 lv_segment_value := lv_segment19;
1635 elsIF ltr_costing_segment(i).column_name = 'SEGMENT20' THEN
1636 lv_segment_value := lv_segment20;
1637 elsIF ltr_costing_segment(i).column_name = 'SEGMENT21' THEN
1638 lv_segment_value := lv_segment21;
1639 elsIF ltr_costing_segment(i).column_name = 'SEGMENT22' THEN
1640 lv_segment_value := lv_segment22;
1641 elsIF ltr_costing_segment(i).column_name = 'SEGMENT23' THEN
1642 lv_segment_value := lv_segment23;
1643 elsIF ltr_costing_segment(i).column_name = 'SEGMENT24' THEN
1644 lv_segment_value := lv_segment24;
1645 elsIF ltr_costing_segment(i).column_name = 'SEGMENT25' THEN
1646 lv_segment_value := lv_segment25;
1647 elsIF ltr_costing_segment(i).column_name = 'SEGMENT26' THEN
1648 lv_segment_value := lv_segment26;
1649 elsIF ltr_costing_segment(i).column_name = 'SEGMENT27' THEN
1650 lv_segment_value := lv_segment27;
1651 elsIF ltr_costing_segment(i).column_name = 'SEGMENT28' THEN
1652 lv_segment_value := lv_segment28;
1653 elsIF ltr_costing_segment(i).column_name = 'SEGMENT29' THEN
1654 lv_segment_value := lv_segment29;
1655 elsIF ltr_costing_segment(i).column_name = 'SEGMENT30' THEN
1656 lv_segment_value := lv_segment30;
1657 END IF;
1658
1659 lv_data_row := lv_data_row ||
1660 pay_us_payroll_utils.formated_data_string (p_input_string=>lv_segment_value
1661 ,p_output_file_type=>p_output_file_type
1662 ,p_bold=>'N'
1663 );
1664
1665 END LOOP ;
1666
1667 lv_data_row:=lv_data_row||lv_data_row2;
1668
1669 hr_utility.trace('lv_data_row = ' || lv_data_row);
1670
1671 IF p_output_file_type ='HTML' THEN
1672 lv_data_row := '<tr>' || lv_data_row || '</tr>' ;
1673 END IF;
1674
1675 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, lv_data_row);
1676
1677 /*****************************************************************
1678 ** initialize Data varaibles
1679 *****************************************************************/
1680 lv_data_row := null;
1681 lv_data_row1 := null;
1682 lv_data_row2 := null;
1683
1684 END LOOP;
1685 IF p_output_file_type='HTML' THEN
1686 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, '</table>');
1687 END IF;
1688 CLOSE c_asg_costing_details;
1689
1690 /*display totals by sort order 1*/
1691 IF p_sort_order1='Payroll Name' THEN
1692 gv_title1:='Costing Summary Report - Payroll Totals';
1693 gv_title2:='Costing Summary Report - GRE Totals';
1694 lv_total_heading:= 'Payroll Totals';
1695 ELSE
1696 gv_title1:='Costing Summary Report - GRE Totals';
1697 gv_title2:='Costing Summary Report - Payroll Totals';
1698 lv_total_heading:= 'GRE Totals';
1699 END IF;
1700
1701 FND_FILE.PUT_LINE(fnd_file.output,pay_us_payroll_utils.formated_header_string(
1702 gv_title1
1703 ,p_output_file_type
1704 ));
1705 IF p_output_file_type ='HTML' THEN
1706
1707 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, '<table border=1 align=CENTER>');
1708 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, '<tr>');
1709 END IF;
1710
1711 formated_totals_header(p_sort_order1,p_output_file_type,lv_header_label1,lv_header_label2);
1712 lv_header_label1:=lv_header_label1 || lv_cost_flex_header;
1713 lv_header_label2:=lv_header_label1 || lv_header_label2;
1714
1715 hr_utility.set_location(gv_package_name || '.costing_summary', 90);
1716
1717 FND_FILE.PUT_LINE(fnd_file.output, lv_header_label2);
1718
1719
1720 OPEN c_costing_summary_rpt_details (lv_session_id
1721 ,p_business_group_id
1722 ,'CSR'
1723 ,p_sort_order1
1724 ,p_sort_order2
1725 );
1726 LOOP
1727 FETCH c_costing_summary_rpt_details into
1728 lv_gre_or_payroll
1729 ,lv_uom
1730 ,ln_credit_amount
1731 ,ln_debit_amount
1732 ,lv_segment1
1733 ,lv_segment2
1734 ,lv_segment3
1735 ,lv_segment4
1736 ,lv_segment5
1737 ,lv_segment6
1738 ,lv_segment7
1739 ,lv_segment8
1740 ,lv_segment9
1741 ,lv_segment10
1742 ,lv_segment11
1743 ,lv_segment12
1744 ,lv_segment13
1745 ,lv_segment14
1746 ,lv_segment15
1747 ,lv_segment16
1748 ,lv_segment17
1749 ,lv_segment18
1750 ,lv_segment19
1751 ,lv_segment20
1752 ,lv_segment21
1753 ,lv_segment22
1754 ,lv_segment23
1755 ,lv_segment24
1756 ,lv_segment25
1757 ,lv_segment26
1758 ,lv_segment27
1759 ,lv_segment28
1760 ,lv_segment29
1761 ,lv_segment30;
1762 IF c_costing_summary_rpt_details%notfound THEN
1763 hr_utility.set_location(gv_package_name || '.costing_summary', 100);
1764 exit;
1765 END IF;
1766
1767 formated_totals(p_gre_or_payroll => lv_gre_or_payroll
1768 ,p_uom => lv_uom
1769 ,p_credit_amount => ln_credit_amount
1770 ,p_debit_amount => ln_debit_amount
1771 ,p_output_file_type => p_output_file_type
1772 ,p_static_data1 => lv_data_row1
1773 ,p_static_data2 => lv_data_row2
1774 );
1775
1776 lv_data_row:= lv_data_row1;
1777
1778 for i in ltr_costing_segment.first .. ltr_costing_segment.last LOOP
1779 IF ltr_costing_segment(i).column_name = 'SEGMENT1' THEN
1780 lv_segment_value := lv_segment1;
1781 elsIF ltr_costing_segment(i).column_name = 'SEGMENT2' THEN
1782 lv_segment_value := lv_segment2;
1783 elsIF ltr_costing_segment(i).column_name = 'SEGMENT3' THEN
1784 lv_segment_value := lv_segment3;
1785 elsIF ltr_costing_segment(i).column_name = 'SEGMENT4' THEN
1786 lv_segment_value := lv_segment4;
1787 elsIF ltr_costing_segment(i).column_name = 'SEGMENT5' THEN
1788 lv_segment_value := lv_segment5;
1789 elsIF ltr_costing_segment(i).column_name = 'SEGMENT6' THEN
1790 lv_segment_value := lv_segment6;
1791 elsIF ltr_costing_segment(i).column_name = 'SEGMENT7' THEN
1792 lv_segment_value := lv_segment7;
1793 elsIF ltr_costing_segment(i).column_name = 'SEGMENT8' THEN
1794 lv_segment_value := lv_segment8;
1795 elsIF ltr_costing_segment(i).column_name = 'SEGMENT9' THEN
1796 lv_segment_value := lv_segment9;
1797 elsIF ltr_costing_segment(i).column_name = 'SEGMENT10' THEN
1798 lv_segment_value := lv_segment10;
1799 elsIF ltr_costing_segment(i).column_name = 'SEGMENT11' THEN
1800 lv_segment_value := lv_segment11;
1801 elsIF ltr_costing_segment(i).column_name = 'SEGMENT12' THEN
1802 lv_segment_value := lv_segment12;
1803 elsIF ltr_costing_segment(i).column_name = 'SEGMENT13' THEN
1804 lv_segment_value := lv_segment13;
1805 elsIF ltr_costing_segment(i).column_name = 'SEGMENT14' THEN
1806 lv_segment_value := lv_segment14;
1807 elsIF ltr_costing_segment(i).column_name = 'SEGMENT15' THEN
1808 lv_segment_value := lv_segment15;
1809 elsIF ltr_costing_segment(i).column_name = 'SEGMENT16' THEN
1810 lv_segment_value := lv_segment16;
1811 elsIF ltr_costing_segment(i).column_name = 'SEGMENT17' THEN
1812 lv_segment_value := lv_segment17;
1813 elsIF ltr_costing_segment(i).column_name = 'SEGMENT18' THEN
1814 lv_segment_value := lv_segment18;
1815 elsIF ltr_costing_segment(i).column_name = 'SEGMENT19' THEN
1816 lv_segment_value := lv_segment19;
1817 elsIF ltr_costing_segment(i).column_name = 'SEGMENT20' THEN
1818 lv_segment_value := lv_segment20;
1819 elsIF ltr_costing_segment(i).column_name = 'SEGMENT21' THEN
1820 lv_segment_value := lv_segment21;
1821 elsIF ltr_costing_segment(i).column_name = 'SEGMENT22' THEN
1822 lv_segment_value := lv_segment22;
1823 elsIF ltr_costing_segment(i).column_name = 'SEGMENT23' THEN
1824 lv_segment_value := lv_segment23;
1825 elsIF ltr_costing_segment(i).column_name = 'SEGMENT24' THEN
1826 lv_segment_value := lv_segment24;
1827 elsIF ltr_costing_segment(i).column_name = 'SEGMENT25' THEN
1828 lv_segment_value := lv_segment25;
1829 elsIF ltr_costing_segment(i).column_name = 'SEGMENT26' THEN
1830 lv_segment_value := lv_segment26;
1831 elsIF ltr_costing_segment(i).column_name = 'SEGMENT27' THEN
1832 lv_segment_value := lv_segment27;
1833 elsIF ltr_costing_segment(i).column_name = 'SEGMENT28' THEN
1834 lv_segment_value := lv_segment28;
1835 elsIF ltr_costing_segment(i).column_name = 'SEGMENT29' THEN
1836 lv_segment_value := lv_segment29;
1837 elsIF ltr_costing_segment(i).column_name = 'SEGMENT30' THEN
1838 lv_segment_value := lv_segment30;
1839 END IF;
1840
1841 lv_data_row := lv_data_row ||
1842 pay_us_payroll_utils.formated_data_string (p_input_string=>lv_segment_value
1843 ,p_output_file_type=>p_output_file_type
1844 ,p_bold=>'N'
1845 );
1846
1847 END LOOP ;
1848
1849 lv_data_row:=lv_data_row||lv_data_row2;
1850
1851 hr_utility.trace('lv_data_row = ' || lv_data_row);
1852
1853 IF p_output_file_type ='HTML' THEN
1854 lv_data_row := '<tr>' || lv_data_row || '</tr>' ;
1855 END IF;
1856
1857 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, lv_data_row);
1858
1859 /*****************************************************************
1860 ** initialize Data varaibles
1861 *****************************************************************/
1862 lv_data_row := null;
1863 lv_data_row1 := null;
1864 lv_data_row2 := null;
1865
1866 END LOOP;
1867 CLOSE c_costing_summary_rpt_details;
1868 IF p_output_file_type='HTML' THEN
1869 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, '</table>');
1870 END IF;
1871
1872 /*Display GRE/Payroll Totals*/
1873
1874 IF p_output_file_type='HTML' THEN
1875 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, '<br><center><b>' || lv_total_heading || '</b></center></br>');
1876 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, '<table border=1 align=CENTER>');
1877 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, '<tr>');
1878 ELSE
1879 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, pay_us_payroll_utils.formated_data_string(
1880 p_input_string=>lv_total_heading
1881 ,p_output_file_type=>p_output_file_type
1882 ,p_bold=>'N'));
1883 END IF;
1884
1885
1886 formated_cons_totals_header1(p_sort_order1,p_output_file_type,lv_header_label);
1887 hr_utility.set_location(gv_package_name || '.costing_summary', 110);
1888
1889 FND_FILE.PUT_LINE(fnd_file.output, lv_header_label);
1890
1891 OPEN c_get_gre_or_payroll_totals (lv_session_id
1892 ,p_business_group_id
1893 ,'CSR'
1894 ,p_sort_order1
1895 );
1896 LOOP
1897
1898 FETCH c_get_gre_or_payroll_totals into lv_gre_or_payroll
1899 ,lv_uom
1900 ,ln_credit_amount
1901 ,ln_debit_amount;
1902 IF c_get_gre_or_payroll_totals%notfound THEN
1903 hr_utility.set_location(gv_package_name || '.costing_summary', 90);
1904 exit;
1905 END IF;
1906
1907 formated_cons_totals1(p_gre_or_payroll => lv_gre_or_payroll
1908 ,p_uom => lv_uom
1909 ,p_credit_amount => ln_credit_amount
1910 ,p_debit_amount => ln_debit_amount
1911 ,p_output_file_type => p_output_file_type
1912 ,p_static_data => lv_data_row
1913 );
1914
1915 hr_utility.trace('lv_data_row = ' || lv_data_row);
1916 IF p_output_file_type ='HTML' THEN
1917 lv_data_row := '<tr>' || lv_data_row || '</tr>' ;
1918 END IF;
1919
1920 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, lv_data_row);
1921
1922 /*****************************************************************
1923 ** initialize Data varaibles
1924 *****************************************************************/
1925 lv_data_row := null;
1926
1927
1928 END LOOP;
1929 CLOSE c_get_gre_or_payroll_totals;
1930
1931 IF p_output_file_type='HTML' THEN
1932 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, '</table>');
1933 END IF;
1934
1935 FND_FILE.PUT_LINE(fnd_file.output,pay_us_payroll_utils.formated_header_string(
1936 'Costing Summary Report - Grand Totals'
1937 ,p_output_file_type
1938 ));
1939 IF p_output_file_type ='HTML' THEN
1940
1941 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, '<table border=1 align=CENTER>');
1942 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, '<tr>');
1943 END IF;
1944
1945 formated_grand_totals_header(p_output_file_type,lv_header_label1);
1946 lv_header_label1:=lv_cost_flex_header || lv_header_label1;
1947 FND_FILE.PUT_LINE(fnd_file.output, lv_header_label1);
1948
1949
1950 OPEN c_costing_grand_totals (lv_session_id
1951 ,p_business_group_id
1952 ,'CSR'
1953 );
1954 LOOP
1955 FETCH c_costing_grand_totals into lv_uom
1956 ,ln_credit_amount
1957 ,ln_debit_amount
1958 ,lv_segment1
1959 ,lv_segment2
1960 ,lv_segment3
1961 ,lv_segment4
1962 ,lv_segment5
1963 ,lv_segment6
1964 ,lv_segment7
1965 ,lv_segment8
1966 ,lv_segment9
1967 ,lv_segment10
1968 ,lv_segment11
1969 ,lv_segment12
1970 ,lv_segment13
1971 ,lv_segment14
1972 ,lv_segment15
1973 ,lv_segment16
1974 ,lv_segment17
1975 ,lv_segment18
1976 ,lv_segment19
1977 ,lv_segment20
1978 ,lv_segment21
1979 ,lv_segment22
1980 ,lv_segment23
1981 ,lv_segment24
1982 ,lv_segment25
1983 ,lv_segment26
1984 ,lv_segment27
1985 ,lv_segment28
1986 ,lv_segment29
1987 ,lv_segment30;
1988
1989 lv_credit_sum:= lv_credit_sum + ln_credit_amount;
1990 lv_debit_sum:= lv_debit_sum + ln_debit_amount;
1991
1992 IF c_costing_grand_totals%notfound THEN
1993 hr_utility.set_location(gv_package_name || '.costing_summary', 120);
1994 exit;
1995 END IF;
1996
1997 formated_grand_totals(p_uom => lv_uom
1998 ,p_credit_amount => ln_credit_amount
1999 ,p_debit_amount => ln_debit_amount
2000 ,p_output_file_type => p_output_file_type
2001 ,p_static_data1 => lv_data_row1
2002 );
2003
2004 lv_data_row:=null;
2005
2006 for i in ltr_costing_segment.first .. ltr_costing_segment.last LOOP
2007 IF ltr_costing_segment(i).column_name = 'SEGMENT1' THEN
2008 lv_segment_value := lv_segment1;
2009 elsIF ltr_costing_segment(i).column_name = 'SEGMENT2' THEN
2010 lv_segment_value := lv_segment2;
2011 elsIF ltr_costing_segment(i).column_name = 'SEGMENT3' THEN
2012 lv_segment_value := lv_segment3;
2013 elsIF ltr_costing_segment(i).column_name = 'SEGMENT4' THEN
2014 lv_segment_value := lv_segment4;
2015 elsIF ltr_costing_segment(i).column_name = 'SEGMENT5' THEN
2016 lv_segment_value := lv_segment5;
2017 elsIF ltr_costing_segment(i).column_name = 'SEGMENT6' THEN
2018 lv_segment_value := lv_segment6;
2019 elsIF ltr_costing_segment(i).column_name = 'SEGMENT7' THEN
2020 lv_segment_value := lv_segment7;
2021 elsIF ltr_costing_segment(i).column_name = 'SEGMENT8' THEN
2022 lv_segment_value := lv_segment8;
2023 elsIF ltr_costing_segment(i).column_name = 'SEGMENT9' THEN
2024 lv_segment_value := lv_segment9;
2025 elsIF ltr_costing_segment(i).column_name = 'SEGMENT10' THEN
2026 lv_segment_value := lv_segment10;
2027 elsIF ltr_costing_segment(i).column_name = 'SEGMENT11' THEN
2028 lv_segment_value := lv_segment11;
2029 elsIF ltr_costing_segment(i).column_name = 'SEGMENT12' THEN
2030 lv_segment_value := lv_segment12;
2031 elsIF ltr_costing_segment(i).column_name = 'SEGMENT13' THEN
2032 lv_segment_value := lv_segment13;
2033 elsIF ltr_costing_segment(i).column_name = 'SEGMENT14' THEN
2034 lv_segment_value := lv_segment14;
2035 elsIF ltr_costing_segment(i).column_name = 'SEGMENT15' THEN
2036 lv_segment_value := lv_segment15;
2037 elsIF ltr_costing_segment(i).column_name = 'SEGMENT16' THEN
2038 lv_segment_value := lv_segment16;
2039 elsIF ltr_costing_segment(i).column_name = 'SEGMENT17' THEN
2040 lv_segment_value := lv_segment17;
2041 elsIF ltr_costing_segment(i).column_name = 'SEGMENT18' THEN
2042 lv_segment_value := lv_segment18;
2043 elsIF ltr_costing_segment(i).column_name = 'SEGMENT19' THEN
2044 lv_segment_value := lv_segment19;
2045 elsIF ltr_costing_segment(i).column_name = 'SEGMENT20' THEN
2046 lv_segment_value := lv_segment20;
2047 elsIF ltr_costing_segment(i).column_name = 'SEGMENT21' THEN
2048 lv_segment_value := lv_segment21;
2049 elsIF ltr_costing_segment(i).column_name = 'SEGMENT22' THEN
2050 lv_segment_value := lv_segment22;
2051 elsIF ltr_costing_segment(i).column_name = 'SEGMENT23' THEN
2052 lv_segment_value := lv_segment23;
2053 elsIF ltr_costing_segment(i).column_name = 'SEGMENT24' THEN
2054 lv_segment_value := lv_segment24;
2055 elsIF ltr_costing_segment(i).column_name = 'SEGMENT25' THEN
2056 lv_segment_value := lv_segment25;
2057 elsIF ltr_costing_segment(i).column_name = 'SEGMENT26' THEN
2058 lv_segment_value := lv_segment26;
2059 elsIF ltr_costing_segment(i).column_name = 'SEGMENT27' THEN
2060 lv_segment_value := lv_segment27;
2061 elsIF ltr_costing_segment(i).column_name = 'SEGMENT28' THEN
2062 lv_segment_value := lv_segment28;
2063 elsIF ltr_costing_segment(i).column_name = 'SEGMENT29' THEN
2064 lv_segment_value := lv_segment29;
2065 elsIF ltr_costing_segment(i).column_name = 'SEGMENT30' THEN
2066 lv_segment_value := lv_segment30;
2067 END IF;
2068
2069 lv_data_row := lv_data_row ||
2070 pay_us_payroll_utils.formated_data_string (p_input_string=>lv_segment_value
2071 ,p_output_file_type=>p_output_file_type
2072 ,p_bold=>'N'
2073 );
2074
2075 END LOOP ;
2076
2077 lv_data_row:=lv_data_row||lv_data_row1;
2078 hr_utility.trace('lv_data_row = ' || lv_data_row);
2079 IF p_output_file_type ='HTML' THEN
2080 lv_data_row := '<tr>' || lv_data_row || '</tr>' ;
2081 END IF;
2082
2083 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, lv_data_row);
2084
2085 /*****************************************************************
2086 ** initialize Data varaibles
2087 *****************************************************************/
2088 lv_data_row := null;
2089 lv_data_row1 := null;
2090 lv_data_row2 := null;
2091 END LOOP;
2092 CLOSE c_costing_grand_totals;
2093 IF p_output_file_type='HTML' THEN
2094 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, '</table>');
2095 END IF;
2096
2097 /* display report totals*/
2098 IF p_output_file_type='HTML' THEN
2099 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, '<br><center><b>' || 'Report Totals' || '</b></center></br>');
2100 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, '<table border=1 align=CENTER>');
2101 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, '<tr>');
2102 ELSE
2103 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, pay_us_payroll_utils.formated_data_string(
2104 p_input_string=>'Report Totals'
2105 ,p_output_file_type=>p_output_file_type
2106 ,p_bold=>'N'));
2107 END IF;
2108
2109 formated_cons_totals_header2(p_output_file_type,lv_header_label);
2110 hr_utility.set_location(gv_package_name || '.costing_summary', 140);
2111
2112 FND_FILE.PUT_LINE(fnd_file.output, lv_header_label);
2113
2114
2115 OPEN c_get_report_totals(lv_session_id,p_business_group_id,'CSR');
2116 LOOP
2117 FETCH c_get_report_totals into
2118 lv_uom
2119 ,lv_credit_sum
2120 ,lv_debit_sum;
2121 IF c_get_report_totals%notfound THEN
2122 hr_utility.set_location(gv_package_name || '.costing_summary', 150);
2123 exit;
2124 END IF;
2125
2126 formated_cons_totals2 (p_uom => lv_uom
2127 ,p_credit_amount => lv_credit_sum
2128 ,p_debit_amount => lv_debit_sum
2129 ,p_output_file_type => p_output_file_type
2130 ,p_static_data => lv_data_row
2131 );
2132
2133 hr_utility.trace(lv_data_row);
2134
2135 IF p_output_file_type ='HTML' THEN
2136 lv_data_row := '<tr>' || lv_data_row || '</tr>' ;
2137 END IF;
2138
2139 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, lv_data_row);
2140
2141 /****************************************************************
2142 ** initialize Data varaibles
2143 *****************************************************************/
2144 lv_data_row := null;
2145 END LOOP;
2146 CLOSE c_get_report_totals;
2147
2148 DELETE FROM pay_us_rpt_totals where attribute31='CSR';
2149 hr_utility.trace('Concurrent Request ID = ' || FND_GLOBAL.CONC_REQUEST_ID);
2150 END costing_summary;
2151 --begin
2152 --hr_utility.trace_on(null, 'COSTING');
2153 END pay_costing_summary_rep_pkg;