[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 2006/11/06 23:06:57 asasthan noship $ */
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
342 ** Purpose : This procedure is used to get the concatenated
339 ** Returns : Concatenated Title strings for the third table
340 ** that contains consolidated costing totals for
341 ** the selected payroll or GRE
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'
449 ,p_output_file_type=>p_output_file_type
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'
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
555 ,p_output_file_type=>p_output_file_type
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
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
654 pay_us_payroll_utils.formated_data_string (p_input_string=>p_uom
651 ,p_output_file_type=>p_output_file_type
652 ,p_bold=>'N'
653 )||
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
765 ,p_END_date in VARCHAR2
762 ,p_business_group_id in NUMBER
763 ,p_start_date in VARCHAR2
764 ,p_dummy_start 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
874 ,attribute17
871 ,attribute14
872 ,attribute15
873 ,attribute16
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 ;
926
927 /************************************************************
928 ** Cursor returns grand totals
929
930 ************************************************************/
931
932
933 CURSOR c_costing_grand_totals (cp_session_id in NUMBER
934 ,cp_business_group_id in NUMBER
935 ,cp_csr in VARCHAR2
936 ) IS
937 SELECT attribute34 --UOM
938 ,sum(value1)
939 ,sum(value2)
940 ,attribute1
941 ,attribute2
942 ,attribute3
943 ,attribute4
944 ,attribute5
945 ,attribute6
946 ,attribute7
947 ,attribute8
948 ,attribute9
949 ,attribute10
950 ,attribute11
951 ,attribute12
952 ,attribute13
953 ,attribute14
954 ,attribute15
955 ,attribute16
956 ,attribute17
957 ,attribute18
958 ,attribute19
959 ,attribute20
960 ,attribute21
961 ,attribute22
962 ,attribute23
963 ,attribute24
964 ,attribute25
965 ,attribute26
966 ,attribute27
967 ,attribute28
968 ,attribute29
969 ,attribute30
970 FROM pay_us_rpt_totals
971 WHERE business_group_id=cp_business_group_id
972 AND attribute31=cp_csr
973 AND session_id=cp_session_id
974 GROUP BY
975 attribute1
976 ,attribute2
977 ,attribute3
978 ,attribute4
979 ,attribute5
980 ,attribute6
981 ,attribute7
982 ,attribute8
983 ,attribute9
984 ,attribute10
985 ,attribute11
986 ,attribute12
987 ,attribute13
988 ,attribute14
989 ,attribute15
990 ,attribute16
991 ,attribute17
992 ,attribute18
993 ,attribute19
994 ,attribute20
995 ,attribute21
996 ,attribute22
997 ,attribute23
998 ,attribute24
999 ,attribute25
1000 ,attribute26
1001 ,attribute27
1002 ,attribute28
1003 ,attribute29
1004 ,attribute30
1005 ,attribute34
1009 Cursor to get GRE/Payroll totals
1006 ;
1007
1008 /**************************************************************
1010 *************************************************************/
1011 CURSOR c_get_gre_or_payroll_totals(cp_session_id in NUMBER
1012 ,cp_business_group_id in NUMBER
1013 ,cp_total_flag in VARCHAR2
1014 ,cp_sort_order1 in VARCHAR2
1015 ) IS
1016 SELECT decode(upper(cp_sort_order1), 'PAYROLL NAME',attribute32,
1017 gre_name)
1018 ,attribute34 --UOM
1019 ,sum(value1)
1020 ,sum(value2)
1021 FROM pay_us_rpt_totals
1022 WHERE session_id=cp_session_id
1023 AND business_group_id=cp_business_group_id
1024 AND attribute31=cp_total_flag
1025 GROUP BY decode(upper(cp_sort_order1), 'PAYROLL NAME',attribute32,
1026 gre_name)
1027 ,attribute34;
1028
1029 /**************************************************************
1030 CURSOR to get report total
1031 **************************************************************/
1032 CURSOR c_get_report_totals (cp_session_id in NUMBER
1033 ,cp_business_group_id in NUMBER
1034 ,cp_total_flag in VARCHAR2
1035 ) IS
1036 SELECT attribute34 --UOM
1037 ,sum(value1)
1038 ,sum(value2)
1039 FROM pay_us_rpt_totals
1040 WHERE session_id=cp_session_id
1041 AND business_group_id=cp_business_group_id
1042 AND attribute31=cp_total_flag
1043 GROUP BY attribute34;
1044
1045
1046
1047 /*************************************************************
1048 ** Local Variables
1049 *************************************************************/
1050 lv_consolidation_set_name VARCHAR2(100);
1051 lv_business_group_name VARCHAR2(100);
1052 lv_payroll_name VARCHAR2(100);
1053 lv_gre_name VARCHAR2(240);
1054 lv_input_value_name VARCHAR2(100);
1055 lv_uom VARCHAR2(100);
1056 ln_credit_amount NUMBER;
1057 ln_debit_amount NUMBER;
1058 lv_effective_date DATE;
1059 lv_concatenated_segments VARCHAR2(200);
1060 lv_segment1 VARCHAR2(200);
1061 lv_segment2 VARCHAR2(200);
1062 lv_segment3 VARCHAR2(200);
1063 lv_segment4 VARCHAR2(200);
1064 lv_segment5 VARCHAR2(200);
1065 lv_segment6 VARCHAR2(200);
1066 lv_segment7 VARCHAR2(200);
1067 lv_segment8 VARCHAR2(200);
1068 lv_segment9 VARCHAR2(200);
1069 lv_segment10 VARCHAR2(200);
1070 lv_segment11 VARCHAR2(200);
1071 lv_segment12 VARCHAR2(200);
1072 lv_segment13 VARCHAR2(200);
1073 lv_segment14 VARCHAR2(200);
1074 lv_segment15 VARCHAR2(200);
1075 lv_segment16 VARCHAR2(200);
1076 lv_segment17 VARCHAR2(200);
1077 lv_segment18 VARCHAR2(200);
1078 lv_segment19 VARCHAR2(200);
1079 lv_segment20 VARCHAR2(200);
1080 lv_segment21 VARCHAR2(200);
1081 lv_segment22 VARCHAR2(200);
1082 lv_segment23 VARCHAR2(200);
1083 lv_segment24 VARCHAR2(200);
1084 lv_segment25 VARCHAR2(200);
1085 lv_segment26 VARCHAR2(200);
1086 lv_segment27 VARCHAR2(200);
1087 lv_segment28 VARCHAR2(200);
1088 lv_segment29 VARCHAR2(200);
1089 lv_segment30 VARCHAR2(200);
1090 ln_costing_id_flex_num NUMBER;
1091 lv_segment_name VARCHAR2(100);
1092 lv_segment_value VARCHAR2(100);
1093 lv_column_name VARCHAR2(100);
1094
1095 lv_header_label VARCHAR2(32000);
1096 lv_header_label1 VARCHAR2(32000);
1097 lv_header_label2 VARCHAR2(32000);
1098 lv_cost_flex_header VARCHAR2(32000):= NULL;
1099
1100 lv_data_row VARCHAR2(32000);
1101 lv_data_row1 VARCHAR2(32000);
1102 lv_data_row2 VARCHAR2(32000);
1103
1104 ln_count NUMBER := 0;
1105 lv_accrual_type VARCHAR2(100);
1106 lv_cost_mode VARCHAR2(100);
1107
1108 ltr_costing_segment costing_tab;
1109
1110 lv_gre_or_payroll VARCHAR2(240);
1111 lv_session_id NUMBER;
1112 lv_credit_sum NUMBER;
1113 lv_debit_sum NUMBER;
1117 lv_END_date date;
1114 lv_total_heading VARCHAR2(240);
1115
1116 lv_start_date date;
1118 lv_costing_process_flag VARCHAR2(1) := 'N';
1119 lv_include_accruals VARCHAR2(100);
1120
1121 /*sackumar testing*/
1122 i_sackumar number;
1123 BEGIN
1124
1125 hr_utility.set_location(gv_package_name || '.costing_summary', 10);
1126 hr_utility.trace('Start Date = ' || p_start_date);
1127 hr_utility.trace('End Date = ' || p_END_date);
1128 hr_utility.trace('Business Group ID = '|| p_business_group_id);
1129 hr_utility.trace('Costing Process = ' || p_costing);
1130 hr_utility.trace('Payroll ID = ' || p_payroll_id);
1131 hr_utility.trace('Consolidation Set ID = ' || p_consolidation_set_id);
1132 hr_utility.trace('Tax unit ID = ' || p_tax_unit_id);
1133 hr_utility.trace('Cost Type = ' || p_cost_type);
1134 hr_utility.trace('Sort Order 1 = ' || p_sort_order1);
1135 hr_utility.trace('Sort Order 2 = ' || p_sort_order2);
1136 hr_utility.trace('Output File Type = ' || p_output_file_type);
1137
1138 formated_static_header(p_output_file_type,
1139 lv_header_label1,
1140 lv_header_label2);
1141 hr_utility.trace('Header Label 1 = ' || lv_header_label1);
1142 hr_utility.trace('Header Label 2 = ' || lv_header_label2);
1143
1144 lv_header_label:=lv_header_label1;
1145 OPEN c_costing_flex_id (p_business_group_id);
1146 FETCH c_costing_flex_id into ln_costing_id_flex_num;
1147 IF c_costing_flex_id%found THEN
1148 hr_utility.set_location(gv_package_name || '.costing_summary', 20);
1149 OPEN c_costing_flex_segments (ln_costing_id_flex_num);
1150 LOOP
1151 FETCH c_costing_flex_segments into lv_segment_name, lv_column_name;
1152 IF c_costing_flex_segments%notfound THEN
1153 exit;
1154 END IF;
1155 lv_header_label := lv_header_label ||
1156 pay_us_payroll_utils.formated_data_string (p_input_string=>lv_segment_name
1157 ,p_bold=>'Y'
1158 ,p_output_file_type=>p_output_file_type);
1159 lv_cost_flex_header:= lv_cost_flex_header ||
1160 pay_us_payroll_utils.formated_data_string (p_input_string=>lv_segment_name
1161 ,p_bold=>'Y'
1162 ,p_output_file_type=>p_output_file_type);
1163 ltr_costing_segment(ln_count).segment_label := lv_segment_name;
1164 ltr_costing_segment(ln_count).column_name := lv_column_name;
1165 ln_count := ln_count + 1;
1166 END LOOP;
1167 CLOSE c_costing_flex_segments;
1168
1169 /*sackumar testing */
1170 i_sackumar :=0;
1171 hr_utility.trace('data from cursor c_costing_flex_segments stored in ltr_costing_segment PL/SQL table');
1172 for i_sackumar in ltr_costing_segment.first .. ltr_costing_segment.last LOOP
1173 hr_utility.trace(ltr_costing_segment(i_sackumar).segment_label||'='||ltr_costing_segment(i_sackumar).column_name);
1174 end loop;
1175 hr_utility.trace('ends data from cursor c_costing_flex_segments ');
1176 /*end of sac kumar testing */
1177
1178 END IF;
1179 CLOSE c_costing_flex_id;
1180 lv_header_label:=lv_header_label||lv_header_label2;
1181
1182 FND_FILE.PUT_LINE(fnd_file.output,pay_us_payroll_utils.formated_header_string(
1183 gv_title
1184 ,p_output_file_type
1185 ));
1186 OPEN c_get_organization_name(p_business_group_id);
1187 FETCH c_get_organization_name into lv_business_group_name;
1188 CLOSE c_get_organization_name;
1189
1190 OPEN c_get_organization_name(p_tax_unit_id);
1191 FETCH c_get_organization_name into lv_gre_name;
1192 CLOSE c_get_organization_name;
1193
1194 OPEN c_get_payroll_name(p_payroll_id);
1195 FETCH c_get_payroll_name into lv_payroll_name;
1196 CLOSE c_get_payroll_name;
1197
1198 OPEN c_get_consolidation_set_name(p_consolidation_set_id);
1199 FETCH c_get_consolidation_set_name into lv_consolidation_set_name;
1200 CLOSE c_get_consolidation_set_name;
1201
1202 hr_utility.set_location(gv_package_name || '.costing_summary', 30);
1203
1204 lv_include_accruals:= nvl(hr_general.decode_lookup('PAY_PAYRPCBR',p_cost_type),' ');
1205 IF p_output_file_type='HTML' AND lv_include_accruals = ' ' THEN
1206 lv_include_accruals:=' ';
1207 END IF;
1208
1209 IF p_costing IS not NULL THEN
1210 hr_utility.trace('to_NUMBER(p_costing)='||to_NUMBER(p_costing));
1211 OPEN c_get_effective_date(to_NUMBER(p_costing));
1212 FETCH c_get_effective_date into lv_start_date;
1213 CLOSE c_get_effective_date;
1214 lv_end_date := lv_start_date;
1215 lv_costing_process_flag:='Y';
1216 ELSE
1217 lv_start_date :=to_date(p_start_date, 'YYYY/MM/DD HH24:MI:SS');
1218 lv_end_date :=to_date(p_end_date, 'YYYY/MM/DD HH24:MI:SS');
1219 lv_costing_process_flag:='N';
1220 END IF;
1221
1222 hr_utility.set_location(gv_package_name || '.costing_summary', 40);
1223
1227 ,p_END_date =>lv_END_date
1224 formated_title_page(p_output_file_type=> p_output_file_type
1225 ,p_business_group => lv_business_group_name
1226 ,p_start_date =>lv_start_date
1228 ,p_costing =>p_costing
1229 ,p_payroll_name =>lv_payroll_name
1230 ,p_consolidation_set_name=>lv_consolidation_set_name
1231 ,p_gre_name =>lv_gre_name
1232 ,p_include_accruals=>lv_include_accruals
1233 ,p_sort_order1 =>p_sort_order1
1234 ,p_sort_order2 =>p_sort_order2
1235 );
1236
1237 hr_utility.set_location(gv_package_name || '.costing_summary', 50);
1238
1239 /****************************************************************
1240 ** Print the Header Information. If the format IS HTML THEN OPEN
1241 ** the body and table before printing the header info, otherwISe
1242 ** just print the header information.
1243 ****************************************************************/
1244 IF p_output_file_type ='HTML' THEN
1245 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, '<table border=1 align=CENTER>');
1246 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, '<tr>');
1247 END IF;
1248
1249 FND_FILE.PUT_LINE(fnd_file.output, lv_header_label);
1250
1251 IF p_output_file_type ='HTML' THEN
1252 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, '</tr>');
1253 END IF;
1254
1255 OPEN c_get_session_id;
1256 FETCH c_get_session_id into lv_session_id;
1257 CLOSE c_get_session_id;
1258 hr_utility.trace('Sort Option 1 = ' || p_sort_order1);
1259 hr_utility.trace('Sort Option 2 = ' || p_sort_order2);
1260
1261 /************* changed to ref cursor 3946996 */
1262
1263 c_clause1 :=get_optional_where_clause(p_payroll_id,
1264 p_consolidation_set_id,
1265 p_tax_unit_id,
1266 lv_costing_process_flag,
1267 p_costing,
1268 p_cost_type);
1269
1270 hr_utility.trace('c_clause1 = ' || c_clause1);
1271
1272 c_query := 'SELECT
1273 pcd.payroll_name
1274 ,pcd.gre_name
1275 ,pcd.input_value_name
1276 ,pcd.uom
1277 ,sum(pcd.credit_amount)
1278 ,sum(pcd.debit_amount)
1279 ,pcd.cost_type
1280 ,pcd.concatenated_segments
1281 ,pcd.segment1
1282 ,pcd.segment2
1283 ,pcd.segment3
1284 ,pcd.segment4
1285 ,pcd.segment5
1286 ,pcd.segment6
1287 ,pcd.segment7
1288 ,pcd.segment8
1289 ,pcd.segment9
1290 ,pcd.segment10
1291 ,pcd.segment11
1292 ,pcd.segment12
1293 ,pcd.segment13
1294 ,pcd.segment14
1295 ,pcd.segment15
1296 ,pcd.segment16
1297 ,pcd.segment17
1298 ,pcd.segment18
1299 ,pcd.segment19
1300 ,pcd.segment20
1301 ,pcd.segment21
1302 ,pcd.segment22
1303 ,pcd.segment23
1304 ,pcd.segment24
1305 ,pcd.segment25
1306 ,pcd.segment26
1307 ,pcd.segment27
1308 ,pcd.segment28
1309 ,pcd.segment29
1310 ,pcd.segment30
1311 FROM pay_costing_details_v pcd
1312 WHERE
1313 pcd.effective_date between :cp_start_date and :cp_end_date
1314 ' || c_clause1 || '
1315 and pcd.business_group_id = :cp_business_group_id
1316 GROUP BY pcd.payroll_name,pcd.gre_name
1317 ,pcd.input_value_name
1318 ,pcd.uom,pcd.cost_type
1319 ,pcd.concatenated_segments
1320 ,pcd.segment1
1321 ,pcd.segment2
1322 ,pcd.segment3
1323 ,pcd.segment4
1324 ,pcd.segment5
1325 ,pcd.segment6
1326 ,pcd.segment7
1327 ,pcd.segment8
1328 ,pcd.segment9
1329 ,pcd.segment10
1330 ,pcd.segment11
1331 ,pcd.segment12
1332 ,pcd.segment13
1333 ,pcd.segment14
1334 ,pcd.segment15
1335 ,pcd.segment16
1336 ,pcd.segment17
1337 ,pcd.segment18
1338 ,pcd.segment19
1339 ,pcd.segment20
1340 ,pcd.segment21
1341 ,pcd.segment22
1342 ,pcd.segment23
1343 ,pcd.segment24
1344 ,pcd.segment25
1345 ,pcd.segment26
1346 ,pcd.segment27
1347 ,pcd.segment28
1348 ,pcd.segment29
1349 ,pcd.segment30
1350 ORDER BY pcd.cost_type
1351 ,decode (upper(:cp_sort_order1), ''PAYROLL NAME'', pcd.payroll_name,
1352 pcd.gre_name)
1353 ,decode(upper(:cp_sort_order2), ''GRE'', pcd.gre_name,''PAYROLL NAME'',
1354 pcd.payroll_name,''X'')';
1355
1356
1357 OPEN c_asg_costing_details
1358 FOR c_query USING lv_start_date
1359 ,lv_end_date
1360 ,p_business_group_id
1361 ,p_sort_order1
1362 ,p_sort_order2;
1363
1364 hr_utility.trace('Start Date for Query = '||lv_start_date);
1365 hr_utility.trace('End Date for Query = '||lv_end_date);
1366 hr_utility.trace('Bussiness Group for Query = '||p_business_group_id);
1367 hr_utility.trace('Short Order 1 for Query = '||p_sort_order1);
1368 hr_utility.trace('Short Order 2 for Query = '||p_sort_order2);
1372 lv_payroll_name
1369
1370 LOOP
1371 FETCH c_asg_costing_details into
1373 ,lv_gre_name
1374 ,lv_input_value_name
1375 ,lv_uom
1376 ,ln_credit_amount
1377 ,ln_debit_amount
1378 ,lv_cost_mode
1379 ,lv_concatenated_segments
1380 ,lv_segment1
1381 ,lv_segment2
1382 ,lv_segment3
1383 ,lv_segment4
1384 ,lv_segment5
1385 ,lv_segment6
1386 ,lv_segment7
1387 ,lv_segment8
1388 ,lv_segment9
1389 ,lv_segment10
1390 ,lv_segment11
1391 ,lv_segment12
1392 ,lv_segment13
1393 ,lv_segment14
1394 ,lv_segment15
1395 ,lv_segment16
1396 ,lv_segment17
1397 ,lv_segment18
1398 ,lv_segment19
1399 ,lv_segment20
1400 ,lv_segment21
1401 ,lv_segment22
1402 ,lv_segment23
1403 ,lv_segment24
1404 ,lv_segment25
1405 ,lv_segment26
1406 ,lv_segment27
1407 ,lv_segment28
1408 ,lv_segment29
1409 ,lv_segment30;
1410
1411 IF c_asg_costing_details%notfound THEN
1412 hr_utility.set_location(gv_package_name || '.costing_summary', 60);
1413 exit;
1414 END IF;
1415
1416 hr_utility.trace('Record No (After Main Query) - '||c_asg_costing_details%rowcount);
1417
1418 lv_accrual_type:=nvl(hr_general.decode_lookup('PAY_PAYRPCBR',lv_cost_mode),' ');
1419 IF p_output_file_type='HTML' AND lv_accrual_type = ' ' THEN
1420 lv_accrual_type:=' ';
1421 END IF;
1422
1423 /*insert into pay_us_rpt_totals*/
1424 /*sackumar : this data is used in the report for geting the other section
1425 here atrributes1 to 30 used to store the values of the Segments 1 to 30
1426 and Attribute31 = 'CSR'
1427 Attribute32 = Payroll Name
1428 Attribute33 = Concatenated Segments Value
1429 Attribute34 = UOM
1430 */
1431 insert into pay_us_rpt_totals(session_id,business_group_id,gre_name,value1 ,value2 ,attribute1,attribute2
1432 ,attribute3,attribute4,attribute5,attribute6,attribute7,attribute8
1433 ,attribute9,attribute10,attribute11,attribute12,attribute13
1434 ,attribute14,attribute15,attribute16,attribute17,attribute18
1435 ,attribute19,attribute20,attribute21,attribute22,attribute23
1436 ,attribute24,attribute25,attribute26,attribute27,attribute28
1437 ,attribute29,attribute30,attribute31,attribute32,attribute33
1438 ,attribute34) values
1439 (lv_session_id -- session ID is passed
1440 ,p_business_group_id
1441 ,lv_gre_name
1442 ,ln_credit_amount
1443 ,ln_debit_amount
1444 ,lv_segment1
1445 ,lv_segment2
1446 ,lv_segment3
1447 ,lv_segment4
1448 ,lv_segment5
1449 ,lv_segment6
1450 ,lv_segment7
1451 ,lv_segment8
1452 ,lv_segment9
1453 ,lv_segment10
1454 ,lv_segment11
1455 ,lv_segment12
1456 ,lv_segment13
1457 ,lv_segment14
1458 ,lv_segment15
1459 ,lv_segment16
1460 ,lv_segment17
1461 ,lv_segment18
1462 ,lv_segment19
1463 ,lv_segment20
1464 ,lv_segment21
1465 ,lv_segment22
1466 ,lv_segment23
1467 ,lv_segment24
1468 ,lv_segment25
1469 ,lv_segment26
1470 ,lv_segment27
1471 ,lv_segment28
1472 ,lv_segment29
1473 ,lv_segment30
1474 ,'CSR' --attribute31 -- denotes that the record is for Costing Summary Report
1475 ,lv_payroll_name --attribute32
1476 ,lv_concatenated_segments --attribute33
1477 ,lv_uom --attribute34
1478 );
1479
1480 hr_utility.set_location(gv_package_name || '.costing_summary', 70);
1481
1482
1483 formated_data_row(p_payroll_name => lv_payroll_name
1484 ,p_gre_name => lv_gre_name
1485 ,p_input_value_name => lv_input_value_name
1486 ,p_uom => lv_uom
1490 ,p_output_file_type=> p_output_file_type
1487 ,p_credit_amount => ln_credit_amount
1488 ,p_debit_amount => ln_debit_amount
1489 ,p_accrual_type => lv_accrual_type
1491 ,p_static_data1 => lv_data_row1
1492 ,p_static_data2 => lv_data_row2
1493 ) ;
1494 hr_utility.set_location(gv_package_name || '.costing_summary', 80);
1495 hr_utility.trace('lv_data_row1 = ' || lv_data_row1);
1496 hr_utility.trace('lv_data_row2 = ' || lv_data_row2);
1497
1498
1499 lv_data_row:= lv_data_row1;
1500
1501 for i in ltr_costing_segment.first .. ltr_costing_segment.last LOOP
1502 IF ltr_costing_segment(i).column_name = 'SEGMENT1' THEN
1503 lv_segment_value := lv_segment1;
1504 elsIF ltr_costing_segment(i).column_name = 'SEGMENT2' THEN
1505 lv_segment_value := lv_segment2;
1506 elsIF ltr_costing_segment(i).column_name = 'SEGMENT3' THEN
1507 lv_segment_value := lv_segment3;
1508 elsIF ltr_costing_segment(i).column_name = 'SEGMENT4' THEN
1509 lv_segment_value := lv_segment4;
1510 elsIF ltr_costing_segment(i).column_name = 'SEGMENT5' THEN
1511 lv_segment_value := lv_segment5;
1512 elsIF ltr_costing_segment(i).column_name = 'SEGMENT6' THEN
1513 lv_segment_value := lv_segment6;
1514 elsIF ltr_costing_segment(i).column_name = 'SEGMENT7' THEN
1515 lv_segment_value := lv_segment7;
1516 elsIF ltr_costing_segment(i).column_name = 'SEGMENT8' THEN
1517 lv_segment_value := lv_segment8;
1518 elsIF ltr_costing_segment(i).column_name = 'SEGMENT9' THEN
1519 lv_segment_value := lv_segment9;
1520 elsIF ltr_costing_segment(i).column_name = 'SEGMENT10' THEN
1521 lv_segment_value := lv_segment10;
1522 elsIF ltr_costing_segment(i).column_name = 'SEGMENT11' THEN
1523 lv_segment_value := lv_segment11;
1524 elsIF ltr_costing_segment(i).column_name = 'SEGMENT12' THEN
1525 lv_segment_value := lv_segment12;
1526 elsIF ltr_costing_segment(i).column_name = 'SEGMENT13' THEN
1527 lv_segment_value := lv_segment13;
1528 elsIF ltr_costing_segment(i).column_name = 'SEGMENT14' THEN
1529 lv_segment_value := lv_segment14;
1530 elsIF ltr_costing_segment(i).column_name = 'SEGMENT15' THEN
1531 lv_segment_value := lv_segment15;
1532 elsIF ltr_costing_segment(i).column_name = 'SEGMENT16' THEN
1533 lv_segment_value := lv_segment16;
1534 elsIF ltr_costing_segment(i).column_name = 'SEGMENT17' THEN
1535 lv_segment_value := lv_segment17;
1536 elsIF ltr_costing_segment(i).column_name = 'SEGMENT18' THEN
1537 lv_segment_value := lv_segment18;
1538 elsIF ltr_costing_segment(i).column_name = 'SEGMENT19' THEN
1539 lv_segment_value := lv_segment19;
1540 elsIF ltr_costing_segment(i).column_name = 'SEGMENT20' THEN
1541 lv_segment_value := lv_segment20;
1542 elsIF ltr_costing_segment(i).column_name = 'SEGMENT21' THEN
1543 lv_segment_value := lv_segment21;
1544 elsIF ltr_costing_segment(i).column_name = 'SEGMENT22' THEN
1545 lv_segment_value := lv_segment22;
1546 elsIF ltr_costing_segment(i).column_name = 'SEGMENT23' THEN
1547 lv_segment_value := lv_segment23;
1548 elsIF ltr_costing_segment(i).column_name = 'SEGMENT24' THEN
1549 lv_segment_value := lv_segment24;
1550 elsIF ltr_costing_segment(i).column_name = 'SEGMENT25' THEN
1551 lv_segment_value := lv_segment25;
1552 elsIF ltr_costing_segment(i).column_name = 'SEGMENT26' THEN
1553 lv_segment_value := lv_segment26;
1554 elsIF ltr_costing_segment(i).column_name = 'SEGMENT27' THEN
1555 lv_segment_value := lv_segment27;
1556 elsIF ltr_costing_segment(i).column_name = 'SEGMENT28' THEN
1557 lv_segment_value := lv_segment28;
1558 elsIF ltr_costing_segment(i).column_name = 'SEGMENT29' THEN
1559 lv_segment_value := lv_segment29;
1560 elsIF ltr_costing_segment(i).column_name = 'SEGMENT30' THEN
1561 lv_segment_value := lv_segment30;
1562 END IF;
1563
1564 lv_data_row := lv_data_row ||
1565 pay_us_payroll_utils.formated_data_string (p_input_string=>lv_segment_value
1566 ,p_output_file_type=>p_output_file_type
1567 ,p_bold=>'N'
1568 );
1569
1570 END LOOP ;
1571
1572 lv_data_row:=lv_data_row||lv_data_row2;
1573
1574 hr_utility.trace('lv_data_row = ' || lv_data_row);
1575
1576 IF p_output_file_type ='HTML' THEN
1577 lv_data_row := '<tr>' || lv_data_row || '</tr>' ;
1578 END IF;
1579
1580 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, lv_data_row);
1581
1585 lv_data_row := null;
1582 /*****************************************************************
1583 ** initialize Data varaibles
1584 *****************************************************************/
1586 lv_data_row1 := null;
1587 lv_data_row2 := null;
1588
1589 END LOOP;
1590 IF p_output_file_type='HTML' THEN
1591 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, '</table>');
1592 END IF;
1593 CLOSE c_asg_costing_details;
1594
1595 /*display totals by sort order 1*/
1596 IF p_sort_order1='Payroll Name' THEN
1597 gv_title1:='Costing Summary Report - Payroll Totals';
1598 gv_title2:='Costing Summary Report - GRE Totals';
1599 lv_total_heading:= 'Payroll Totals';
1600 ELSE
1601 gv_title1:='Costing Summary Report - GRE Totals';
1602 gv_title2:='Costing Summary Report - Payroll Totals';
1603 lv_total_heading:= 'GRE Totals';
1604 END IF;
1605
1606 FND_FILE.PUT_LINE(fnd_file.output,pay_us_payroll_utils.formated_header_string(
1607 gv_title1
1608 ,p_output_file_type
1609 ));
1610 IF p_output_file_type ='HTML' THEN
1611
1612 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, '<table border=1 align=CENTER>');
1613 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, '<tr>');
1614 END IF;
1615
1616 formated_totals_header(p_sort_order1,p_output_file_type,lv_header_label1,lv_header_label2);
1617 lv_header_label1:=lv_header_label1 || lv_cost_flex_header;
1618 lv_header_label2:=lv_header_label1 || lv_header_label2;
1619
1620 hr_utility.set_location(gv_package_name || '.costing_summary', 90);
1621
1622 FND_FILE.PUT_LINE(fnd_file.output, lv_header_label2);
1623
1624
1625 OPEN c_costing_summary_rpt_details (lv_session_id
1626 ,p_business_group_id
1627 ,'CSR'
1628 ,p_sort_order1
1629 ,p_sort_order2
1630 );
1631 LOOP
1632 FETCH c_costing_summary_rpt_details into
1633 lv_gre_or_payroll
1634 ,lv_uom
1635 ,ln_credit_amount
1636 ,ln_debit_amount
1637 ,lv_segment1
1638 ,lv_segment2
1639 ,lv_segment3
1640 ,lv_segment4
1641 ,lv_segment5
1642 ,lv_segment6
1643 ,lv_segment7
1644 ,lv_segment8
1645 ,lv_segment9
1646 ,lv_segment10
1647 ,lv_segment11
1648 ,lv_segment12
1649 ,lv_segment13
1650 ,lv_segment14
1651 ,lv_segment15
1652 ,lv_segment16
1653 ,lv_segment17
1654 ,lv_segment18
1655 ,lv_segment19
1656 ,lv_segment20
1657 ,lv_segment21
1658 ,lv_segment22
1659 ,lv_segment23
1660 ,lv_segment24
1661 ,lv_segment25
1662 ,lv_segment26
1663 ,lv_segment27
1664 ,lv_segment28
1665 ,lv_segment29
1666 ,lv_segment30;
1667 IF c_costing_summary_rpt_details%notfound THEN
1668 hr_utility.set_location(gv_package_name || '.costing_summary', 100);
1669 exit;
1670 END IF;
1671
1672 formated_totals(p_gre_or_payroll => lv_gre_or_payroll
1673 ,p_uom => lv_uom
1674 ,p_credit_amount => ln_credit_amount
1675 ,p_debit_amount => ln_debit_amount
1676 ,p_output_file_type => p_output_file_type
1677 ,p_static_data1 => lv_data_row1
1678 ,p_static_data2 => lv_data_row2
1679 );
1680
1681 lv_data_row:= lv_data_row1;
1682
1683 for i in ltr_costing_segment.first .. ltr_costing_segment.last LOOP
1684 IF ltr_costing_segment(i).column_name = 'SEGMENT1' THEN
1685 lv_segment_value := lv_segment1;
1686 elsIF ltr_costing_segment(i).column_name = 'SEGMENT2' THEN
1687 lv_segment_value := lv_segment2;
1688 elsIF ltr_costing_segment(i).column_name = 'SEGMENT3' THEN
1689 lv_segment_value := lv_segment3;
1690 elsIF ltr_costing_segment(i).column_name = 'SEGMENT4' THEN
1691 lv_segment_value := lv_segment4;
1692 elsIF ltr_costing_segment(i).column_name = 'SEGMENT5' THEN
1693 lv_segment_value := lv_segment5;
1697 lv_segment_value := lv_segment7;
1694 elsIF ltr_costing_segment(i).column_name = 'SEGMENT6' THEN
1695 lv_segment_value := lv_segment6;
1696 elsIF ltr_costing_segment(i).column_name = 'SEGMENT7' THEN
1698 elsIF ltr_costing_segment(i).column_name = 'SEGMENT8' THEN
1699 lv_segment_value := lv_segment8;
1700 elsIF ltr_costing_segment(i).column_name = 'SEGMENT9' THEN
1701 lv_segment_value := lv_segment9;
1702 elsIF ltr_costing_segment(i).column_name = 'SEGMENT10' THEN
1703 lv_segment_value := lv_segment10;
1704 elsIF ltr_costing_segment(i).column_name = 'SEGMENT11' THEN
1705 lv_segment_value := lv_segment11;
1706 elsIF ltr_costing_segment(i).column_name = 'SEGMENT12' THEN
1707 lv_segment_value := lv_segment12;
1708 elsIF ltr_costing_segment(i).column_name = 'SEGMENT13' THEN
1709 lv_segment_value := lv_segment13;
1710 elsIF ltr_costing_segment(i).column_name = 'SEGMENT14' THEN
1711 lv_segment_value := lv_segment14;
1712 elsIF ltr_costing_segment(i).column_name = 'SEGMENT15' THEN
1713 lv_segment_value := lv_segment15;
1714 elsIF ltr_costing_segment(i).column_name = 'SEGMENT16' THEN
1715 lv_segment_value := lv_segment16;
1716 elsIF ltr_costing_segment(i).column_name = 'SEGMENT17' THEN
1717 lv_segment_value := lv_segment17;
1718 elsIF ltr_costing_segment(i).column_name = 'SEGMENT18' THEN
1719 lv_segment_value := lv_segment18;
1720 elsIF ltr_costing_segment(i).column_name = 'SEGMENT19' THEN
1721 lv_segment_value := lv_segment19;
1722 elsIF ltr_costing_segment(i).column_name = 'SEGMENT20' THEN
1723 lv_segment_value := lv_segment20;
1724 elsIF ltr_costing_segment(i).column_name = 'SEGMENT21' THEN
1725 lv_segment_value := lv_segment21;
1726 elsIF ltr_costing_segment(i).column_name = 'SEGMENT22' THEN
1727 lv_segment_value := lv_segment22;
1728 elsIF ltr_costing_segment(i).column_name = 'SEGMENT23' THEN
1729 lv_segment_value := lv_segment23;
1730 elsIF ltr_costing_segment(i).column_name = 'SEGMENT24' THEN
1731 lv_segment_value := lv_segment24;
1732 elsIF ltr_costing_segment(i).column_name = 'SEGMENT25' THEN
1733 lv_segment_value := lv_segment25;
1734 elsIF ltr_costing_segment(i).column_name = 'SEGMENT26' THEN
1735 lv_segment_value := lv_segment26;
1736 elsIF ltr_costing_segment(i).column_name = 'SEGMENT27' THEN
1737 lv_segment_value := lv_segment27;
1738 elsIF ltr_costing_segment(i).column_name = 'SEGMENT28' THEN
1739 lv_segment_value := lv_segment28;
1740 elsIF ltr_costing_segment(i).column_name = 'SEGMENT29' THEN
1741 lv_segment_value := lv_segment29;
1742 elsIF ltr_costing_segment(i).column_name = 'SEGMENT30' THEN
1743 lv_segment_value := lv_segment30;
1744 END IF;
1745
1746 lv_data_row := lv_data_row ||
1747 pay_us_payroll_utils.formated_data_string (p_input_string=>lv_segment_value
1748 ,p_output_file_type=>p_output_file_type
1749 ,p_bold=>'N'
1750 );
1751
1752 END LOOP ;
1753
1754 lv_data_row:=lv_data_row||lv_data_row2;
1755
1756 hr_utility.trace('lv_data_row = ' || lv_data_row);
1757
1758 IF p_output_file_type ='HTML' THEN
1759 lv_data_row := '<tr>' || lv_data_row || '</tr>' ;
1760 END IF;
1761
1762 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, lv_data_row);
1763
1764 /*****************************************************************
1765 ** initialize Data varaibles
1766 *****************************************************************/
1767 lv_data_row := null;
1768 lv_data_row1 := null;
1769 lv_data_row2 := null;
1770
1771 END LOOP;
1772 CLOSE c_costing_summary_rpt_details;
1773 IF p_output_file_type='HTML' THEN
1774 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, '</table>');
1775 END IF;
1776
1777 /*Display GRE/Payroll Totals*/
1778
1779 IF p_output_file_type='HTML' THEN
1780 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, '<br><center><b>' || lv_total_heading || '</b></center></br>');
1781 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, '<table border=1 align=CENTER>');
1782 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, '<tr>');
1783 ELSE
1784 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, pay_us_payroll_utils.formated_data_string(
1785 p_input_string=>lv_total_heading
1786 ,p_output_file_type=>p_output_file_type
1787 ,p_bold=>'N'));
1788 END IF;
1789
1790
1791 formated_cons_totals_header1(p_sort_order1,p_output_file_type,lv_header_label);
1792 hr_utility.set_location(gv_package_name || '.costing_summary', 110);
1793
1794 FND_FILE.PUT_LINE(fnd_file.output, lv_header_label);
1795
1796 OPEN c_get_gre_or_payroll_totals (lv_session_id
1797 ,p_business_group_id
1798 ,'CSR'
1799 ,p_sort_order1
1803 FETCH c_get_gre_or_payroll_totals into lv_gre_or_payroll
1800 );
1801 LOOP
1802
1804 ,lv_uom
1805 ,ln_credit_amount
1806 ,ln_debit_amount;
1807 IF c_get_gre_or_payroll_totals%notfound THEN
1808 hr_utility.set_location(gv_package_name || '.costing_summary', 90);
1809 exit;
1810 END IF;
1811
1812 formated_cons_totals1(p_gre_or_payroll => lv_gre_or_payroll
1813 ,p_uom => lv_uom
1814 ,p_credit_amount => ln_credit_amount
1815 ,p_debit_amount => ln_debit_amount
1816 ,p_output_file_type => p_output_file_type
1817 ,p_static_data => lv_data_row
1818 );
1819
1820 hr_utility.trace('lv_data_row = ' || lv_data_row);
1821 IF p_output_file_type ='HTML' THEN
1822 lv_data_row := '<tr>' || lv_data_row || '</tr>' ;
1823 END IF;
1824
1825 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, lv_data_row);
1826
1827 /*****************************************************************
1828 ** initialize Data varaibles
1829 *****************************************************************/
1830 lv_data_row := null;
1831
1832
1833 END LOOP;
1834 CLOSE c_get_gre_or_payroll_totals;
1835
1836 IF p_output_file_type='HTML' THEN
1837 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, '</table>');
1838 END IF;
1839
1840 FND_FILE.PUT_LINE(fnd_file.output,pay_us_payroll_utils.formated_header_string(
1841 'Costing Summary Report - Grand Totals'
1842 ,p_output_file_type
1843 ));
1844 IF p_output_file_type ='HTML' THEN
1845
1846 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, '<table border=1 align=CENTER>');
1847 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, '<tr>');
1848 END IF;
1849
1850 formated_grand_totals_header(p_output_file_type,lv_header_label1);
1851 lv_header_label1:=lv_cost_flex_header || lv_header_label1;
1852 FND_FILE.PUT_LINE(fnd_file.output, lv_header_label1);
1853
1854
1855 OPEN c_costing_grand_totals (lv_session_id
1856 ,p_business_group_id
1857 ,'CSR'
1858 );
1859 LOOP
1860 FETCH c_costing_grand_totals into lv_uom
1861 ,ln_credit_amount
1862 ,ln_debit_amount
1863 ,lv_segment1
1864 ,lv_segment2
1865 ,lv_segment3
1866 ,lv_segment4
1867 ,lv_segment5
1868 ,lv_segment6
1869 ,lv_segment7
1870 ,lv_segment8
1871 ,lv_segment9
1872 ,lv_segment10
1873 ,lv_segment11
1874 ,lv_segment12
1875 ,lv_segment13
1876 ,lv_segment14
1877 ,lv_segment15
1878 ,lv_segment16
1879 ,lv_segment17
1880 ,lv_segment18
1881 ,lv_segment19
1882 ,lv_segment20
1883 ,lv_segment21
1884 ,lv_segment22
1885 ,lv_segment23
1886 ,lv_segment24
1887 ,lv_segment25
1888 ,lv_segment26
1889 ,lv_segment27
1890 ,lv_segment28
1891 ,lv_segment29
1892 ,lv_segment30;
1893
1894 lv_credit_sum:= lv_credit_sum + ln_credit_amount;
1895 lv_debit_sum:= lv_debit_sum + ln_debit_amount;
1896
1897 IF c_costing_grand_totals%notfound THEN
1898 hr_utility.set_location(gv_package_name || '.costing_summary', 120);
1899 exit;
1900 END IF;
1901
1902 formated_grand_totals(p_uom => lv_uom
1903 ,p_credit_amount => ln_credit_amount
1904 ,p_debit_amount => ln_debit_amount
1905 ,p_output_file_type => p_output_file_type
1906 ,p_static_data1 => lv_data_row1
1907 );
1908
1909 lv_data_row:=null;
1910
1911 for i in ltr_costing_segment.first .. ltr_costing_segment.last LOOP
1912 IF ltr_costing_segment(i).column_name = 'SEGMENT1' THEN
1913 lv_segment_value := lv_segment1;
1917 lv_segment_value := lv_segment3;
1914 elsIF ltr_costing_segment(i).column_name = 'SEGMENT2' THEN
1915 lv_segment_value := lv_segment2;
1916 elsIF ltr_costing_segment(i).column_name = 'SEGMENT3' THEN
1918 elsIF ltr_costing_segment(i).column_name = 'SEGMENT4' THEN
1919 lv_segment_value := lv_segment4;
1920 elsIF ltr_costing_segment(i).column_name = 'SEGMENT5' THEN
1921 lv_segment_value := lv_segment5;
1922 elsIF ltr_costing_segment(i).column_name = 'SEGMENT6' THEN
1923 lv_segment_value := lv_segment6;
1924 elsIF ltr_costing_segment(i).column_name = 'SEGMENT7' THEN
1925 lv_segment_value := lv_segment7;
1926 elsIF ltr_costing_segment(i).column_name = 'SEGMENT8' THEN
1927 lv_segment_value := lv_segment8;
1928 elsIF ltr_costing_segment(i).column_name = 'SEGMENT9' THEN
1929 lv_segment_value := lv_segment9;
1930 elsIF ltr_costing_segment(i).column_name = 'SEGMENT10' THEN
1931 lv_segment_value := lv_segment10;
1932 elsIF ltr_costing_segment(i).column_name = 'SEGMENT11' THEN
1933 lv_segment_value := lv_segment11;
1934 elsIF ltr_costing_segment(i).column_name = 'SEGMENT12' THEN
1935 lv_segment_value := lv_segment12;
1936 elsIF ltr_costing_segment(i).column_name = 'SEGMENT13' THEN
1937 lv_segment_value := lv_segment13;
1938 elsIF ltr_costing_segment(i).column_name = 'SEGMENT14' THEN
1939 lv_segment_value := lv_segment14;
1940 elsIF ltr_costing_segment(i).column_name = 'SEGMENT15' THEN
1941 lv_segment_value := lv_segment15;
1942 elsIF ltr_costing_segment(i).column_name = 'SEGMENT16' THEN
1943 lv_segment_value := lv_segment16;
1944 elsIF ltr_costing_segment(i).column_name = 'SEGMENT17' THEN
1945 lv_segment_value := lv_segment17;
1946 elsIF ltr_costing_segment(i).column_name = 'SEGMENT18' THEN
1947 lv_segment_value := lv_segment18;
1948 elsIF ltr_costing_segment(i).column_name = 'SEGMENT19' THEN
1949 lv_segment_value := lv_segment19;
1950 elsIF ltr_costing_segment(i).column_name = 'SEGMENT20' THEN
1951 lv_segment_value := lv_segment20;
1952 elsIF ltr_costing_segment(i).column_name = 'SEGMENT21' THEN
1953 lv_segment_value := lv_segment21;
1954 elsIF ltr_costing_segment(i).column_name = 'SEGMENT22' THEN
1955 lv_segment_value := lv_segment22;
1956 elsIF ltr_costing_segment(i).column_name = 'SEGMENT23' THEN
1957 lv_segment_value := lv_segment23;
1958 elsIF ltr_costing_segment(i).column_name = 'SEGMENT24' THEN
1959 lv_segment_value := lv_segment24;
1960 elsIF ltr_costing_segment(i).column_name = 'SEGMENT25' THEN
1961 lv_segment_value := lv_segment25;
1962 elsIF ltr_costing_segment(i).column_name = 'SEGMENT26' THEN
1963 lv_segment_value := lv_segment26;
1964 elsIF ltr_costing_segment(i).column_name = 'SEGMENT27' THEN
1965 lv_segment_value := lv_segment27;
1966 elsIF ltr_costing_segment(i).column_name = 'SEGMENT28' THEN
1967 lv_segment_value := lv_segment28;
1968 elsIF ltr_costing_segment(i).column_name = 'SEGMENT29' THEN
1969 lv_segment_value := lv_segment29;
1970 elsIF ltr_costing_segment(i).column_name = 'SEGMENT30' THEN
1971 lv_segment_value := lv_segment30;
1972 END IF;
1973
1974 lv_data_row := lv_data_row ||
1975 pay_us_payroll_utils.formated_data_string (p_input_string=>lv_segment_value
1976 ,p_output_file_type=>p_output_file_type
1977 ,p_bold=>'N'
1978 );
1979
1980 END LOOP ;
1981
1982 lv_data_row:=lv_data_row||lv_data_row1;
1983 hr_utility.trace('lv_data_row = ' || lv_data_row);
1984 IF p_output_file_type ='HTML' THEN
1985 lv_data_row := '<tr>' || lv_data_row || '</tr>' ;
1986 END IF;
1987
1988 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, lv_data_row);
1989
1990 /*****************************************************************
1991 ** initialize Data varaibles
1992 *****************************************************************/
1993 lv_data_row := null;
1994 lv_data_row1 := null;
1995 lv_data_row2 := null;
1996 END LOOP;
1997 CLOSE c_costing_grand_totals;
1998 IF p_output_file_type='HTML' THEN
1999 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, '</table>');
2000 END IF;
2001
2002 /* display report totals*/
2003 IF p_output_file_type='HTML' THEN
2004 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, '<br><center><b>' || 'Report Totals' || '</b></center></br>');
2005 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, '<table border=1 align=CENTER>');
2006 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, '<tr>');
2007 ELSE
2008 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, pay_us_payroll_utils.formated_data_string(
2009 p_input_string=>'Report Totals'
2010 ,p_output_file_type=>p_output_file_type
2011 ,p_bold=>'N'));
2012 END IF;
2013
2014 formated_cons_totals_header2(p_output_file_type,lv_header_label);
2015 hr_utility.set_location(gv_package_name || '.costing_summary', 140);
2016
2017 FND_FILE.PUT_LINE(fnd_file.output, lv_header_label);
2018
2019
2020 OPEN c_get_report_totals(lv_session_id,p_business_group_id,'CSR');
2021 LOOP
2022 FETCH c_get_report_totals into
2023 lv_uom
2024 ,lv_credit_sum
2025 ,lv_debit_sum;
2026 IF c_get_report_totals%notfound THEN
2027 hr_utility.set_location(gv_package_name || '.costing_summary', 150);
2028 exit;
2029 END IF;
2030
2031 formated_cons_totals2 (p_uom => lv_uom
2032 ,p_credit_amount => lv_credit_sum
2033 ,p_debit_amount => lv_debit_sum
2034 ,p_output_file_type => p_output_file_type
2035 ,p_static_data => lv_data_row
2036 );
2037
2038 hr_utility.trace(lv_data_row);
2039
2040 IF p_output_file_type ='HTML' THEN
2041 lv_data_row := '<tr>' || lv_data_row || '</tr>' ;
2042 END IF;
2043
2044 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, lv_data_row);
2045
2046 /****************************************************************
2047 ** initialize Data varaibles
2048 *****************************************************************/
2049 lv_data_row := null;
2050 END LOOP;
2051 CLOSE c_get_report_totals;
2052
2053 DELETE FROM pay_us_rpt_totals where attribute31='CSR';
2054 hr_utility.trace('Concurrent Request ID = ' || FND_GLOBAL.CONC_REQUEST_ID);
2055 END costing_summary;
2056 --begin
2057 --hr_utility.trace_on(null, 'COSTING');
2058 END pay_costing_summary_rep_pkg;