[Home] [Help]
PACKAGE BODY: APPS.PAY_COSTING_DETAIL_REP_PKG
Source
1 PACKAGE BODY pay_costing_detail_rep_pkg AS
2 /* $Header: pycstrep.pkb 120.2 2006/05/02 13:56:03 ppanda noship $ */
3 --
4 /************************************************************
5 ** Local Package Variables
6 ************************************************************/
7 gv_title VARCHAR2(100);
8 --gv_title VARCHAR2(100) := ' Costing Detail Report';
9 gc_csv_delimiter VARCHAR2(1) := ',';
10 gc_csv_data_delimiter VARCHAR2(1) := '"';
11
12 gv_html_start_data VARCHAR2(5) := '<td>' ;
13 gv_html_end_data VARCHAR2(5) := '</td>' ;
14
15 gv_package_name VARCHAR2(50) := 'pay_costing_detail_rep_pkg';
16
17
18 /******************************************************************
19 ** Function Returns the formated input string based on the
20 ** Output format. If the format is CSV then the values are returned
21 ** seperated by comma (,). If the format is HTML then the returned
22 ** string as the HTML tags. The parameter p_bold only works for
23 ** the HTML format.
24 ******************************************************************/
25 FUNCTION formated_data_string
26 (p_input_string in varchar2
27 ,p_output_file_type in varchar2
28 ,p_bold in varchar2 default 'N'
29 )
30 RETURN VARCHAR2
31 IS
32
33 lv_format varchar2(1000);
34
35 BEGIN
36 hr_utility.set_location(gv_package_name || '.formated_data_string', 10);
37 if p_output_file_type = 'CSV' then
38 hr_utility.set_location(gv_package_name || '.formated_data_string', 20);
39 lv_format := gc_csv_data_delimiter || p_input_string ||
40 gc_csv_data_delimiter || gc_csv_delimiter;
41 elsif p_output_file_type = 'HTML' then
42 if p_input_string is null then
43 hr_utility.set_location(gv_package_name || '.formated_data_string', 30);
44 lv_format := gv_html_start_data || ' ' || gv_html_end_data;
45 else
46 if p_bold = 'Y' then
47 hr_utility.set_location(gv_package_name || '.formated_data_string', 40);
48 lv_format := gv_html_start_data || '<b> ' || p_input_string
49 || '</b>' || gv_html_end_data;
50 else
51 hr_utility.set_location(gv_package_name || '.formated_data_string', 50);
52 lv_format := gv_html_start_data || p_input_string || gv_html_end_data;
53 end if;
54 end if;
55 end if;
56
57 hr_utility.set_location(gv_package_name || '.formated_data_string', 60);
58 return lv_format;
59
60 END formated_data_string;
61
62
63 /************************************************************
64 ** Function returns the string with the HTML Header tags
65 ************************************************************/
66 FUNCTION formated_header_string
67 (p_input_string in varchar2
68 ,p_output_file_type in varchar2
69 )
70 RETURN VARCHAR2
71 IS
72 lv_format varchar2(1000);
73 BEGIN
74 hr_utility.set_location(gv_package_name || '.formated_header_string', 10);
75 if p_output_file_type = 'CSV' then
76 hr_utility.set_location(gv_package_name || '.formated_header_string', 20);
77 lv_format := p_input_string;
78 elsif p_output_file_type = 'HTML' then
79 hr_utility.set_location(gv_package_name || '.formated_header_string', 30);
80 lv_format := '<HTML><HEAD> <CENTER> <H1> <B>' || p_input_string ||
81 '</B></H1></CENTER></HEAD>';
82 end if;
83 hr_utility.set_location(gv_package_name || '.formated_header_string', 40);
84 return lv_format;
85 END formated_header_string;
86
87
88 /*****************************************************************
89 ** This procudure returns the Mandatory Static Labels and the
90 ** Other Additional Static columns. The other static columns are
91 ** printed after all the Element Information is printed for each
92 ** employee assignment.
93 ** The users can add hooks to this package to print more additional
94 ** data which they require for this report.
95 ** The package prints the user data from a PL/SQL table. The users
96 ** can insert data and the label in this PL/SQL table which will
97 ** be printed at the end of the report.
98 ** The PL/SQL table which needs to be populated is
99 ** LTR_ELEMENT_EXTRACT_DATA. This PL/SQL table is defined in the
100 ** Package pay_element_extract_data_pkg (pyelerpd.pkh/pkb).
101 *****************************************************************/
102 PROCEDURE formated_static_header(
103 p_output_file_type in varchar2
104 ,p_static_label1 out nocopy varchar2
105 ,p_static_label2 out nocopy varchar2
106 ,p_chk_ni_prt in varchar2 -- Bug 4142845
107 ,p_business_group_id in varchar2 -- Bug 2007614
108 )
109 IS
110
111 --Bug 2007614
112 cursor c_legislation_code is
113 select legislation_code
114 from per_business_groups
115 where business_group_id = p_business_group_id;
116
117 lv_legislation_code varchar2(150);-- Bug 2007614
118 lv_ssl_number varchar2(150); -- Bug 2007614
119 lv_format1 varchar2(32000);
120 lv_format2 varchar2(32000);
121
122 BEGIN
123
124 -- bug 3039073 replace hardcoded strings with translatable variables from
125 -- FND_COMMON_LOOKUPS table using the hr_general.decode_fnd_comm_lookup
126 -- function from the hrgenral.pkb package
127 -- Bug 2007614
128 open c_legislation_code ;
129 fetch c_legislation_code into lv_legislation_code;
130 /* Commented for Bug # 5192802
131 if lv_legislation_code = 'CA' then
132 lv_ssl_number := 'SIN';
133 else
134 lv_ssl_number := 'SSN';
135 end if;
136 */
137 /* This is added to fix Bug # 5179163 */
138 fnd_message.set_name('PER','HR_NATIONAL_ID_NUMBER_'||lv_legislation_code);
139 lv_ssl_number := fnd_message.get;
140 if lv_ssl_number IS NULL
141 then
142 lv_ssl_number := 'National Identifier';
143 end if;
144 hr_utility.trace('HR_NATIONAL_ID_NUMBER_'||lv_legislation_code ||' = ' || lv_ssl_number);
145
146
147 hr_utility.set_location(gv_package_name || '.formated_static_header', 10);
148 lv_format1 :=
149 formated_data_string (p_input_string =>
150 hr_general.decode_fnd_comm_lookup
151 ('PAYROLL_REPORTS', --lookup_type
152 'CONS_SET_NAME')--lookup_code
153 ,p_bold => 'Y'
154 ,p_output_file_type => p_output_file_type) ||
155 formated_data_string (p_input_string =>
156 hr_general.decode_fnd_comm_lookup
157 ('PAYROLL_REPORTS', --lookup_type
158 'PR_NAME')--lookup_code
159 ,p_bold => 'Y'
160 ,p_output_file_type => p_output_file_type) ||
161 formated_data_string (p_input_string =>
162 hr_general.decode_fnd_comm_lookup
163 ('PAYROLL_REPORTS', --lookup_type
164 'GRE')--lookup_code
165 ,p_bold => 'Y'
166 ,p_output_file_type => p_output_file_type) ||
167 formated_data_string (p_input_string =>
168 hr_general.decode_fnd_comm_lookup
169 ('PAYROLL_REPORTS', --lookup_type
170 'L_NAME')--lookup_code
171 ,p_bold => 'Y'
172 ,p_output_file_type => p_output_file_type) ||
173 formated_data_string (p_input_string =>
174 hr_general.decode_fnd_comm_lookup
175 ('PAYROLL_REPORTS', --lookup_type
176 'F_NAME')--lookup_code
177 ,p_bold => 'Y'
178 ,p_output_file_type => p_output_file_type) ||
179 formated_data_string (p_input_string =>
180 hr_general.decode_fnd_comm_lookup
181 ('PAYROLL_REPORTS', --lookup_type
182 'MI_NAME')--lookup_code
183 ,p_bold => 'Y'
184 ,p_output_file_type => p_output_file_type) ||
185 formated_data_string (p_input_string =>
186 hr_general.decode_fnd_comm_lookup
187 ('PAYROLL_REPORTS', --lookup_type
188 'EFF_DT')--lookup_code
189 ,p_bold => 'Y'
190 ,p_output_file_type => p_output_file_type) ||
191 formated_data_string (p_input_string =>
192 hr_general.decode_fnd_comm_lookup
193 ('PAYROLL_REPORTS', --lookup_type
194 'ELE_NAME')--lookup_code
195 ,p_bold => 'Y'
196 ,p_output_file_type => p_output_file_type) ||
197 formated_data_string (p_input_string =>
198 hr_general.decode_fnd_comm_lookup
199 ('PAYROLL_REPORTS', --lookup_type
200 'INP_VAL')--lookup_code
201 ,p_bold => 'Y'
202 ,p_output_file_type => p_output_file_type) ||
203 formated_data_string (p_input_string =>
204 hr_general.decode_fnd_comm_lookup
205 ('PAYROLL_REPORTS', --lookup_type
206 'UOM')--lookup_code
207 ,p_bold => 'Y'
208 ,p_output_file_type => p_output_file_type) ||
209 formated_data_string (p_input_string =>
210 hr_general.decode_fnd_comm_lookup
211 ('PAYROLL_REPORTS', --lookup_type
212 'COST_ALLOC_SEGM')--lookup_code
213 ,p_bold => 'Y'
214 ,p_output_file_type => p_output_file_type) ||
215 formated_data_string (p_input_string =>
216 hr_general.decode_fnd_comm_lookup
217 ('PAYROLL_REPORTS', --lookup_type
218 'CR_AMT')--lookup_code
219 ,p_bold => 'Y'
220 ,p_output_file_type => p_output_file_type) ||
221 formated_data_string (p_input_string =>
222 hr_general.decode_fnd_comm_lookup
223 ('PAYROLL_REPORTS', --lookup_type
224 'DR_AMT')--lookup_code
225 ,p_bold => 'Y'
226 ,p_output_file_type => p_output_file_type) ;
227
228
229 hr_utility.set_location(gv_package_name || '.formated_static_header', 20);
230 lv_format2 :=
231 formated_data_string (p_input_string =>
232 hr_general.decode_fnd_comm_lookup
233 ('PAYROLL_REPORTS', --lookup_type
234 'ORG_NAME')--lookup_code
235 ,p_bold => 'Y'
236 ,p_output_file_type => p_output_file_type) ||
237 formated_data_string (p_input_string =>
238 hr_general.decode_fnd_comm_lookup
239 ('PAYROLL_REPORTS', --lookup_type
240 'LOC_NAME')--lookup_code
241 ,p_bold => 'Y'
242 ,p_output_file_type => p_output_file_type) ;
243 /* Added by ssmukher for Bug 4142845 */
244 if p_chk_ni_prt = 'Y' then
245 lv_format2 := lv_format2 ||
246 formated_data_string (p_input_string =>
247 NVL(hr_general.decode_fnd_comm_lookup
248 ('PAYROLL_REPORTS', --lookup_type
249 lv_ssl_number),
250 lv_ssl_number)--lookup_code Bug 2007614
251 ,p_bold => 'Y'
252 ,p_output_file_type => p_output_file_type) ;
253 end if;
254
255 lv_format2 := lv_format2 ||
256 formated_data_string (p_input_string =>
257 hr_general.decode_fnd_comm_lookup
258 ('PAYROLL_REPORTS', --lookup_type
259 'EMP_NO')--lookup_code
260 ,p_bold => 'Y'
261 ,p_output_file_type => p_output_file_type) ||
262 formated_data_string (p_input_string =>
263 hr_general.decode_fnd_comm_lookup
264 ('PAYROLL_REPORTS', --lookup_type
265 'ASSIGN_NO')--lookup_code
266 ,p_bold => 'Y'
267 ,p_output_file_type => p_output_file_type)
268 ;
269
270 hr_utility.set_location(gv_package_name || '.formated_static_header', 30);
271
272 p_static_label1 := lv_format1;
273 p_static_label2 := lv_format2;
274 hr_utility.trace('Static Label1 = ' || lv_format1);
275 hr_utility.trace('Static Label2 = ' || lv_format2);
276 hr_utility.set_location(gv_package_name || '.formated_static_header', 40);
277
278 END formated_static_header;
279
280
281 /*****************************************************************
282 ** This procudure returns the Mandatory Static Labels and the
283 ** Other Additional Static columns. The other static columns are
284 ** printed after all the Element Information is printed for each
285 ** employee assignment.
286 ** The users can add hooks to this package to print more additional
287 ** data which they require for this report.
288 ** The package prints the user data from a PL/SQL table. The users
289 ** can insert data and the label in this PL/SQL table which will
290 ** be printed at the end of the report.
291 ** The PL/SQL table which needs to be populated is
292 ** LTR_ELEMENT_EXTRACT_DATA. This PL/SQL table is defined in the
293 ** Package pay_element_extract_data_pkg (pyelerpd.pkh/pkb).
294 *****************************************************************/
295 PROCEDURE formated_static_data (
296 p_consolidation_set_name in varchar2
297 ,p_payroll_name in varchar2
298 ,p_gre_name in varchar2
299 ,p_emp_last_name in varchar2
300 ,p_emp_first_name in varchar2
301 ,p_emp_middle_names in varchar2
302 ,p_action_effective_date in date
303 ,p_element_name in varchar2
304 ,p_input_value_name in varchar2
305 ,p_uom in varchar2 -- Bug 3072270
306 ,p_credit_amount in number
307 ,p_debit_amount in number
308 ,p_accrual_type in varchar2 --Bug 3179050
309 ,p_concatenated_segments in varchar2
310 ,p_org_name in varchar2
311 ,p_location_code in varchar2
312 ,p_emp_employee_number in varchar2
313 ,p_emp_national_identifier in varchar2
314 ,p_assignment_number in varchar2
315 ,p_chk_ni_prt in varchar2 --Bug 4142845
316 ,p_output_file_type in varchar2
317 ,p_static_data1 out nocopy varchar2
318 ,p_static_data2 out nocopy varchar2
319 )
320 IS
321
322 lv_format1 VARCHAR2(32000);
323 lv_format2 VARCHAR2(32000);
324
325 lv_action_effective_date varchar2(20);
326
327 BEGIN
328
329 hr_utility.set_location(gv_package_name || '.formated_static_data', 10);
330 lv_action_effective_date := to_char(p_action_effective_date, 'dd-MON-yyyy');
331 lv_format1 :=
332 formated_data_string (p_input_string => p_consolidation_set_name
333 ,p_output_file_type => p_output_file_type) ||
334 formated_data_string (p_input_string => p_payroll_name
335 ,p_output_file_type => p_output_file_type) ||
336 formated_data_string (p_input_string => p_gre_name
337 ,p_output_file_type => p_output_file_type) ||
338 formated_data_string (p_input_string => p_emp_last_name
339 ,p_output_file_type => p_output_file_type) ||
340 formated_data_string (p_input_string => p_emp_first_name
341 ,p_output_file_type => p_output_file_type) ||
342 formated_data_string (p_input_string => p_emp_middle_names
343 ,p_output_file_type => p_output_file_type) ||
344 formated_data_string (p_input_string => lv_action_effective_date
345 ,p_output_file_type => p_output_file_type) ||
346 formated_data_string (p_input_string => p_element_name
347 ,p_output_file_type => p_output_file_type) ||
348 formated_data_string (p_input_string => p_input_value_name
349 ,p_output_file_type => p_output_file_type) ||
350 formated_data_string (p_input_string => p_uom -- Bug 3072270
351 ,p_output_file_type => p_output_file_type) ||
352 formated_data_string (p_input_string => p_concatenated_segments
353 ,p_output_file_type => p_output_file_type) ||
354 formated_data_string (p_input_string => p_credit_amount
355 ,p_output_file_type => p_output_file_type) ||
356 formated_data_string (p_input_string => p_debit_amount
357 ,p_output_file_type => p_output_file_type) ||
358 formated_data_string (p_input_string => p_accrual_type
359 ,p_output_file_type => p_output_file_type)
360 ;
361
362 hr_utility.set_location(gv_package_name || '.formated_static_data', 20);
363
364 lv_format2 :=
365 formated_data_string (p_input_string => p_org_name
366 ,p_output_file_type => p_output_file_type) ||
367 formated_data_string (p_input_string => p_location_code
368 ,p_output_file_type => p_output_file_type);
369
370 /* Added by ssmukher for Bug 4142845 */
371 if p_chk_ni_prt = 'Y' then
372 lv_format2 := lv_format2 ||
373 formated_data_string (p_input_string => p_emp_national_identifier
374 ,p_output_file_type => p_output_file_type);
375 end if;
376
377 lv_format2 := lv_format2 ||
378 formated_data_string (p_input_string => p_emp_employee_number
379 ,p_output_file_type => p_output_file_type) ||
380 formated_data_string (p_input_string => p_assignment_number
381 ,p_output_file_type => p_output_file_type)
382 ;
383
384 hr_utility.set_location(gv_package_name || '.formated_static_data', 30);
385
386 p_static_data1 := lv_format1;
387 p_static_data2 := lv_format2;
388 hr_utility.trace('Static Data1 = ' || lv_format1);
389 hr_utility.trace('Static Data2 = ' || lv_format2);
390 hr_utility.set_location(gv_package_name || '.formated_static_data', 40);
391
392 END;
393
394 /******************************************************************
395 Function for returning the optional where clause for the cursor
396 c_asg_costing_details
397 Bug 3179050 To include Partial Period Accruals
398 ******************************************************************/
399
400 function get_optional_where_clause(cp_payroll_id in number
401 ,cp_consolidation_set_id in number
402 ,cp_tax_unit_id in number
403 ,cp_organization_id in number
404 ,cp_location_id in number
405 ,cp_person_id in number) return varchar2 is
406
407 dynamic_where_clause varchar2(10000);
408
409 begin
410
411 if cp_consolidation_set_id is not null then
412 dynamic_where_clause := ' and pcd.consolidation_set_id = '|| to_char(cp_consolidation_set_id);
413 end if;
414
415 if cp_payroll_id is not null then
416 dynamic_where_clause := dynamic_where_clause || ' and pcd.payroll_id = '|| to_char(cp_payroll_id);
417 end if;
418
419 if cp_tax_unit_id is not null then
420 dynamic_where_clause:= dynamic_where_clause || ' and pcd.tax_unit_id = ' || to_char(cp_tax_unit_id);
421 end if;
422
423 if cp_organization_id is not null then
424 dynamic_where_clause := dynamic_where_clause || ' and pcd.organization_id = ' || to_char(cp_organization_id);
425 end if;
426
427 if cp_location_id is not null then
428 dynamic_where_clause := dynamic_where_clause || ' and pcd.location_id = ' || to_char(cp_location_id);
429 end if;
430
431 if cp_person_id is not null then
432 dynamic_where_clause := dynamic_where_clause || ' and pcd.person_id = ' || to_char(cp_person_id);
433 end if;
434
435 return dynamic_where_clause;
436
437 end get_optional_where_clause;
438
439 /*****************************************************************
440 ** This is the main procedure which is called from the Concurrent
441 ** Request. All the paramaters are passed based on which it will
442 ** either print a CSV format or an HTML format file.
443 *****************************************************************/
444 PROCEDURE costing_extract
445 (errbuf out nocopy varchar2
446 ,retcode out nocopy number
447 ,p_business_group_id in number
448 ,p_start_date in varchar2
449 ,p_end_date in varchar2
450 ,p_selection_criteria in varchar2
451 ,p_is_ele_set in varchar2
452 ,p_element_set_id in number
453 ,p_is_ele_class in varchar2
454 ,p_element_classification_id in number
455 ,p_is_ele in varchar2
456 ,p_element_type_id in number
457 ,p_payroll_id in number
458 ,p_consolidation_set_id in number
459 ,p_tax_unit_id in number
460 ,p_organization_id in number
461 ,p_location_id in number
462 ,p_person_id in number
463 ,p_assignment_set_id in number
464 ,p_cost_type in varchar2 --Bug 3179050
465 ,p_output_file_type in varchar2
466 )
467 IS
468
469 /************************************************************
470 ** Added by ssmukher for Bug 4142845
471 ** Cursor to get the Legislation Code for the Business Group.
472 ************************************************************/
473 cursor c_leg_code(cp_business_group in number) is
474 select legislation_code
475 from per_business_groups
476 where business_group_id = cp_business_group;
477
478 /************************************************************
479 ** Added by ssmukher for Bug 4142845
480 ** Cursor to get the Legislation Rule info for printing the
481 ** National Identifier
482 ************************************************************/
483 cursor c_national_identifier(cp_legislation_code in varchar) is
484 select nvl(rule_mode,'Y')
485 from pay_legislative_field_info
486 where field_name = 'NATIONAL_IDENTIFIER_PRT'
487 and rule_type = 'DISPLAY'
488 and legislation_code = cp_legislation_code;
489
490
491 /************************************************************
492 ** Cursor to get the Costing flex which is setup at
493 ** Business Group.
494 ************************************************************/
495 cursor c_costing_flex_id (cp_business_group_id in number) is
496 select org_information7
497 from hr_organization_information hoi
498 where organization_id = cp_business_group_id
499 and org_information_context = 'Business Group Information';
500
501 /************************************************************
502 ** Cursor returns all the segments defined for the Costing
503 ** Flex which are enabled and displayed.
504 ************************************************************/
505 cursor c_costing_flex_segments (cp_id_flex_num in number) is
506 select segment_name, application_column_name
507 from fnd_id_flex_segments
508 where id_flex_code = 'COST'
509 and id_flex_num = cp_id_flex_num
510 and enabled_flag = 'Y'
511 and display_flag = 'Y'
512 order by segment_num;
513
514 /*************************************************************
515 ** Local Variables
516 *************************************************************/
517 lv_consolidation_set_name VARCHAR2(100);
518 lv_payroll_name VARCHAR2(100);
519 lv_gre_name VARCHAR2(240);
520 lv_org_name VARCHAR2(240);
521 lv_location_code VARCHAR2(100);
522 lv_emp_last_name VARCHAR2(150);
523 lv_emp_first_name VARCHAR2(150);
524 lv_emp_middle_names VARCHAR2(100);
525 lv_emp_employee_number VARCHAR2(100);
526 lv_assignment_number VARCHAR2(100);
527 lv_element_name VARCHAR2(100);
528 lv_input_value_name VARCHAR2(100);
529 lv_uom VARCHAR2(20);-- Bug 3072270
530 ln_credit_amount NUMBER;
531 ln_debit_amount NUMBER;
532
533 lv_emp_national_identifier VARCHAR2(100);
534 ld_effective_date DATE;
535 lv_concatenated_segments VARCHAR2(200);
536 lv_segment1 VARCHAR2(200);
537 lv_segment2 VARCHAR2(200);
538 lv_segment3 VARCHAR2(200);
539 lv_segment4 VARCHAR2(200);
540 lv_segment5 VARCHAR2(200);
541 lv_segment6 VARCHAR2(200);
542 lv_segment7 VARCHAR2(200);
543 lv_segment8 VARCHAR2(200);
544 lv_segment9 VARCHAR2(200);
545 lv_segment10 VARCHAR2(200);
546 lv_segment11 VARCHAR2(200);
547 lv_segment12 VARCHAR2(200);
548 lv_segment13 VARCHAR2(200);
549 lv_segment14 VARCHAR2(200);
550 lv_segment15 VARCHAR2(200);
551 lv_segment16 VARCHAR2(200);
552 lv_segment17 VARCHAR2(200);
553 lv_segment18 VARCHAR2(200);
554 lv_segment19 VARCHAR2(200);
555 lv_segment20 VARCHAR2(200);
556 lv_segment21 VARCHAR2(200);
557 lv_segment22 VARCHAR2(200);
558 lv_segment23 VARCHAR2(200);
559 lv_segment24 VARCHAR2(200);
560 lv_segment25 VARCHAR2(200);
561 lv_segment26 VARCHAR2(200);
562 lv_segment27 VARCHAR2(200);
563 lv_segment28 VARCHAR2(200);
564 lv_segment29 VARCHAR2(200);
565 lv_segment30 VARCHAR2(200);
566
567 ln_assignment_id NUMBER;
568 ln_costing_id_flex_num NUMBER;
569 lv_segment_name VARCHAR2(100);
570 lv_segment_value VARCHAR2(100);
571 lv_column_name VARCHAR2(100);
572
573 lv_header_label VARCHAR2(32000);
574 lv_header_label1 VARCHAR2(32000);
575 lv_header_label2 VARCHAR2(32000);
576
577 lv_data_row VARCHAR2(32000);
578 lv_data_row1 VARCHAR2(32000);
579 lv_data_row2 VARCHAR2(32000);
580
581 lv_chk_ni_prt VARCHAR2(30); --Bug 4142845
582 lv_legislation_code VARCHAR2(10);
583
584 ln_count NUMBER := 0;
585 lv_accrual_type varchar2(100); --Bug 3179050
586 lv_cost_mode varchar2(100); --Bug 3179050
587
588 ltr_costing_segment costing_tab;
589 lv_before_report_flag BOOLEAN;
590 TYPE cur_type is REF CURSOR; -- Bug 3179050
591 c_asg_costing_details cur_type; --Bug 3179050
592
593 c_query varchar2(5000); --for the cursor query (Bug 3179050)
594 c_clause1 varchar2(5000); --to store the optional where clause (Bug 3179050)
595
596 BEGIN
597
598 hr_utility.trace('Cost Type = ' || p_cost_type);
599 hr_utility.set_location(gv_package_name || '.costing_extract', 10);
600 hr_utility.trace('Start Date = ' || p_start_date);
601 hr_utility.trace('End Date = ' || p_end_date);
602 hr_utility.trace('Business Group ID = '|| p_business_group_id);
603 hr_utility.trace('Classification ID = '|| nvl(to_char(p_element_classification_id), 'NULL'));
604 hr_utility.trace('Element Set ID = ' || nvl(to_char(p_element_set_id), 'NULL'));
605 hr_utility.trace('Element Type ID = ' || nvl(to_char(p_element_type_id), 'NULL'));
606 hr_utility.trace('Person ID = ' || p_person_id);
607 hr_utility.trace('Location ID = ' || p_location_id);
608 hr_utility.trace('Organization ID = ' || p_organization_id);
609 hr_utility.trace('Tax Unit ID = ' || p_tax_unit_id);
610 hr_utility.trace('Payroll ID = ' || p_payroll_id);
611 hr_utility.trace('Consolidation ID = ' || p_consolidation_set_id);
612 hr_utility.trace('Asgn Set ID = ' || p_assignment_set_id);
613 hr_utility.set_location(gv_package_name || '.costing_extract', 20);
614
615 /* Added by ssmukher for Bug 4142845 */
616 open c_leg_code(p_business_group_id);
617 fetch c_leg_code into lv_legislation_code;
618 close c_leg_code;
619
620 open c_national_identifier( lv_legislation_code);
621 fetch c_national_identifier into lv_chk_ni_prt;
622 if c_national_identifier%notfound then
623 lv_chk_ni_prt := 'Y';
624 close c_national_identifier;
625 else
626 close c_national_identifier;
627 end if;
628
629 formated_static_header( p_output_file_type
630 ,lv_header_label1
631 ,lv_header_label2
632 ,lv_chk_ni_prt -- --Bug 4142845
633 ,p_business_group_id);
634
635 lv_header_label := lv_header_label1;
636
637 hr_utility.set_location(gv_package_name || '.costing_extract', 30);
638 lv_header_label := lv_header_label ||
639 formated_data_string (
640 p_input_string => 'Accrual Type'
641 ,p_bold => 'Y'
642 ,p_output_file_type => p_output_file_type);
643
644 open c_costing_flex_id (p_business_group_id);
645 fetch c_costing_flex_id into ln_costing_id_flex_num;
646 if c_costing_flex_id%found then
647 hr_utility.set_location(gv_package_name || '.costing_extract', 40);
648 open c_costing_flex_segments (ln_costing_id_flex_num);
649 loop
650 fetch c_costing_flex_segments into lv_segment_name, lv_column_name;
651 if c_costing_flex_segments%notfound then
652 exit;
653 end if;
654 lv_header_label := lv_header_label ||
655 formated_data_string (
656 p_input_string => lv_segment_name
657 ,p_bold => 'Y'
658 ,p_output_file_type => p_output_file_type);
659
660 ltr_costing_segment(ln_count).segment_label := lv_segment_name;
661 ltr_costing_segment(ln_count).column_name := lv_column_name;
662 ln_count := ln_count + 1;
663
664 end loop;
665 close c_costing_flex_segments;
666
667 end if;
668 close c_costing_flex_id;
669 hr_utility.set_location(gv_package_name || '.costing_extract', 50);
670
671 /****************************************************************
672 ** Concatnating the second Header Label which includes the
673 ** data set which has to be printed at the end of the report.
674 ****************************************************************/
675 lv_header_label := lv_header_label || lv_header_label2;
676
677 hr_utility.set_location(gv_package_name || '.costing_extract', 60);
678 hr_utility.trace('Static and Element Label = ' || lv_header_label);
679
680 gv_title := hr_general.decode_fnd_comm_lookup
681 ('PAYROLL_REPORTS',
682 'COSTING_REPORT_TITLE');
683
684 fnd_file.put_line(fnd_file.output, formated_header_string(
685 gv_title
686 ,p_output_file_type
687 ));
688
689 hr_utility.set_location(gv_package_name || '.costing_extract', 70);
690 /****************************************************************
691 ** Print the Header Information. If the format is HTML then open
692 ** the body and table before printing the header info, otherwise
693 ** just print the header information.
694 ****************************************************************/
695 if p_output_file_type ='HTML' then
696 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, '<body>');
697 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, '<table border=1>');
698 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, '<tr>');
699 end if;
700
701 fnd_file.put_line(fnd_file.output, lv_header_label);
702
703 if p_output_file_type ='HTML' then
704 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, '</tr>');
705 end if;
706
707 hr_utility.set_location(gv_package_name || '.costing_extract', 80);
708 /*****************************************************
709 ** Start of the Data Section of the Report
710 *****************************************************/
711 /*Bug 3179050 - changed cursor c_asg_costing_details to a REF CURSOR*/
712 c_clause1:=get_optional_where_clause(p_payroll_id,
713 p_consolidation_set_id,
714 p_tax_unit_id,
715 p_organization_id,
716 p_location_id,
717 p_person_id);
718 --3581378
719 if p_element_type_id is not null then
720 c_query :=
721 'select pcd.cost_type
722 ,pcd.consolidation_set_name
723 ,pcd.payroll_name
724 ,pcd.gre_name
725 ,pcd.organization_name
726 ,pcd.location_code
727 ,pcd.last_name
728 ,pcd.first_name
729 ,pcd.middle_names
730 ,pcd.employee_number
731 ,pcd.assignment_number
732 ,nvl(pcd.reporting_name,pcd.element_name)
733 ,pcd.input_value_name
734 ,pcd.uom
735 ,pcd.credit_amount
736 ,pcd.debit_amount
737 ,pcd.national_identifier
738 ,pcd.effective_date
739 ,pcd.concatenated_segments
740 ,pcd.assignment_id
741 ,pcd.segment1
742 ,pcd.segment2
743 ,pcd.segment3
744 ,pcd.segment4
745 ,pcd.segment5
746 ,pcd.segment6
747 ,pcd.segment7
748 ,pcd.segment8
749 ,pcd.segment9
750 ,pcd.segment10
751 ,pcd.segment11
752 ,pcd.segment12
753 ,pcd.segment13
754 ,pcd.segment14
755 ,pcd.segment15
756 ,pcd.segment16
757 ,pcd.segment17
758 ,pcd.segment18
759 ,pcd.segment19
760 ,pcd.segment20
761 ,pcd.segment21
762 ,pcd.segment22
763 ,pcd.segment23
764 ,pcd.segment24
765 ,pcd.segment25
766 ,pcd.segment26
767 ,pcd.segment27
768 ,pcd.segment28
769 ,pcd.segment29
770 ,pcd.segment30
771 from pay_costing_details_v pcd
772 where pcd.effective_date between :cp_start_date and :cp_end_date
773 ' || c_clause1 || '
774 and pcd.business_group_id = ' || NVL(p_business_group_id,0) || '
775 and (:cp_assignment_set_id is NULL
776 or (
777 :cp_assignment_set_id is not NULL
778 and :cp_assignment_set_id in
779 (select assignment_set_id
780 from hr_assignment_set_amendments hasa
781 where hasa.assignment_set_id = :cp_assignment_set_id
782 and pcd.assignment_id = hasa.assignment_id
783 )
784 )
785 )
786 and (:cp_element_type_id is null
787 or (:cp_element_type_id is not null
788 and pcd.element_type_id = :cp_element_type_id)
789 )
790 and ((:cp_cost_type = ''EST_MODE_COST''
791 and pcd.cost_type in (''COST_TMP'',''EST_COST''))
792 or
793 (:cp_cost_type = ''EST_MODE_ALL''
794 and pcd.cost_type in (''COST_TMP'',''EST_COST'',''EST_REVERSAL''))
795 or
796 (:cp_cost_type is null
797 and pcd.cost_type = ''COST_TMP'')
798 )
799 order by pcd.last_name, pcd.first_name,
800 pcd.middle_names, pcd.effective_date,pcd.cost_type';
801
802 elsif p_element_set_id is not null then
803 c_query :=
804 'select pcd.cost_type
805 ,pcd.consolidation_set_name
806 ,pcd.payroll_name
807 ,pcd.gre_name
808 ,pcd.organization_name
809 ,pcd.location_code
810 ,pcd.last_name
811 ,pcd.first_name
812 ,pcd.middle_names
813 ,pcd.employee_number
814 ,pcd.assignment_number
815 ,nvl(pcd.reporting_name,pcd.element_name)
816 ,pcd.input_value_name
817 ,pcd.uom
818 ,pcd.credit_amount
819 ,pcd.debit_amount
820 ,pcd.national_identifier
821 ,pcd.effective_date
822 ,pcd.concatenated_segments
823 ,pcd.assignment_id
824 ,pcd.segment1
825 ,pcd.segment2
826 ,pcd.segment3
827 ,pcd.segment4
828 ,pcd.segment5
829 ,pcd.segment6
830 ,pcd.segment7
831 ,pcd.segment8
832 ,pcd.segment9
833 ,pcd.segment10
834 ,pcd.segment11
835 ,pcd.segment12
836 ,pcd.segment13
837 ,pcd.segment14
838 ,pcd.segment15
839 ,pcd.segment16
840 ,pcd.segment17
841 ,pcd.segment18
842 ,pcd.segment19
843 ,pcd.segment20
844 ,pcd.segment21
845 ,pcd.segment22
846 ,pcd.segment23
847 ,pcd.segment24
848 ,pcd.segment25
849 ,pcd.segment26
850 ,pcd.segment27
851 ,pcd.segment28
852 ,pcd.segment29
853 ,pcd.segment30
854 from pay_costing_details_v pcd
855 where pcd.effective_date between :cp_start_date and :cp_end_date
856 ' || c_clause1 || '
857 and pcd.business_group_id = ' || NVL(p_business_group_id,0) || '
858 and (:cp_assignment_set_id is NULL
859 or ( :cp_assignment_set_id is not NULL
860 and :cp_assignment_set_id in
861 (select assignment_set_id
862 from hr_assignment_set_amendments hasa
863 where hasa.assignment_set_id = :cp_assignment_set_id
864 and pcd.assignment_id = hasa.assignment_id
865 )
866 )
867 )
868 and (:cp_element_set_id is null
869 or (:cp_element_set_id is not null
870 and exists
871 (select ''x'' from pay_element_type_rules petr
872 where petr.element_set_id = :cp_element_set_id
873 and petr.element_type_id = pcd.element_type_id
874 and petr.include_or_exclude = ''I''
875 union all
876 select ''x'' from pay_element_types_f pet1
877 where pet1.classification_id in
878 (select classification_id
879 from pay_ele_classification_rules
880 where element_set_id = :cp_element_set_id)
881 and pet1.element_type_id = pcd.element_type_id
882 minus
883 select ''x'' from pay_element_type_rules petr
884 where petr.element_set_id = :cp_element_set_id
885 and petr.element_type_id = pcd.element_type_id
886 and petr.include_or_exclude = ''E''
887 )
888 )
889 )
890 and ((:cp_cost_type = ''EST_MODE_COST''
891 and pcd.cost_type in (''COST_TMP'',''EST_COST''))
892 or
893 (:cp_cost_type = ''EST_MODE_ALL''
894 and pcd.cost_type in (''COST_TMP'',''EST_COST'',''EST_REVERSAL''))
895 or
896 (:cp_cost_type is null
897 and pcd.cost_type = ''COST_TMP'')
898 )
899 order by pcd.last_name, pcd.first_name,
900 pcd.middle_names, pcd.effective_date,pcd.cost_type';
901 elsif p_element_classification_id is not null then
902 c_query :=
903 'select pcd.cost_type
904 ,pcd.consolidation_set_name
905 ,pcd.payroll_name
906 ,pcd.gre_name
907 ,pcd.organization_name
908 ,pcd.location_code
909 ,pcd.last_name
910 ,pcd.first_name
911 ,pcd.middle_names
912 ,pcd.employee_number
913 ,pcd.assignment_number
914 ,nvl(pcd.reporting_name,pcd.element_name)
915 ,pcd.input_value_name
916 ,pcd.uom
917 ,pcd.credit_amount
918 ,pcd.debit_amount
919 ,pcd.national_identifier
920 ,pcd.effective_date
921 ,pcd.concatenated_segments
922 ,pcd.assignment_id
923 ,pcd.segment1
924 ,pcd.segment2
925 ,pcd.segment3
926 ,pcd.segment4
927 ,pcd.segment5
928 ,pcd.segment6
929 ,pcd.segment7
930 ,pcd.segment8
931 ,pcd.segment9
932 ,pcd.segment10
933 ,pcd.segment11
934 ,pcd.segment12
935 ,pcd.segment13
936 ,pcd.segment14
937 ,pcd.segment15
938 ,pcd.segment16
939 ,pcd.segment17
940 ,pcd.segment18
941 ,pcd.segment19
942 ,pcd.segment20
943 ,pcd.segment21
944 ,pcd.segment22
945 ,pcd.segment23
946 ,pcd.segment24
947 ,pcd.segment25
948 ,pcd.segment26
949 ,pcd.segment27
950 ,pcd.segment28
951 ,pcd.segment29
952 ,pcd.segment30
953 from pay_costing_details_v pcd
954 where pcd.effective_date between :cp_start_date and :cp_end_date
955 ' || c_clause1 || '
956 and pcd.business_group_id = ' || NVL(p_business_group_id,0) || '
957 and (:cp_assignment_set_id is NULL
958 or ( :cp_assignment_set_id is not NULL
959 and :cp_assignment_set_id in
960 (select assignment_set_id
961 from hr_assignment_set_amendments hasa
962 where hasa.assignment_set_id = :cp_assignment_set_id
963 and pcd.assignment_id = hasa.assignment_id
964 )
965 )
966 )
967 and (:cp_element_classification_id is null
968 or (:cp_element_classification_id is not null
969 and pcd.classification_id = :cp_element_classification_id)
970 )
971 and ((:cp_cost_type = ''EST_MODE_COST''
972 and pcd.cost_type in (''COST_TMP'',''EST_COST''))
973 or
974 (:cp_cost_type = ''EST_MODE_ALL''
975 and pcd.cost_type in (''COST_TMP'',''EST_COST'',''EST_REVERSAL''))
976 or
977 (:cp_cost_type is null
978 and pcd.cost_type = ''COST_TMP'')
979 )
980 order by pcd.last_name, pcd.first_name,
981 pcd.middle_names, pcd.effective_date,pcd.cost_type';
982 elsif (p_element_type_id is null)
983 and (p_element_set_id is null)
984 and (p_element_classification_id is null) then
985 c_query :=
986 'select pcd.cost_type
987 ,pcd.consolidation_set_name
988 ,pcd.payroll_name
989 ,pcd.gre_name
990 ,pcd.organization_name
991 ,pcd.location_code
992 ,pcd.last_name
993 ,pcd.first_name
994 ,pcd.middle_names
995 ,pcd.employee_number
996 ,pcd.assignment_number
997 ,nvl(pcd.reporting_name,pcd.element_name)
998 ,pcd.input_value_name
999 ,pcd.uom
1000 ,pcd.credit_amount
1001 ,pcd.debit_amount
1002 ,pcd.national_identifier
1003 ,pcd.effective_date
1004 ,pcd.concatenated_segments
1005 ,pcd.assignment_id
1006 ,pcd.segment1
1007 ,pcd.segment2
1008 ,pcd.segment3
1009 ,pcd.segment4
1010 ,pcd.segment5
1011 ,pcd.segment6
1012 ,pcd.segment7
1013 ,pcd.segment8
1014 ,pcd.segment9
1015 ,pcd.segment10
1016 ,pcd.segment11
1017 ,pcd.segment12
1018 ,pcd.segment13
1019 ,pcd.segment14
1020 ,pcd.segment15
1021 ,pcd.segment16
1022 ,pcd.segment17
1023 ,pcd.segment18
1024 ,pcd.segment19
1025 ,pcd.segment20
1026 ,pcd.segment21
1027 ,pcd.segment22
1028 ,pcd.segment23
1029 ,pcd.segment24
1030 ,pcd.segment25
1031 ,pcd.segment26
1032 ,pcd.segment27
1033 ,pcd.segment28
1034 ,pcd.segment29
1035 ,pcd.segment30
1036 from pay_costing_details_v pcd
1037 where pcd.effective_date between :cp_start_date and :cp_end_date
1038 ' || c_clause1 || '
1039 and pcd.business_group_id = ' || NVL(p_business_group_id,0) || '
1040 and (:cp_assignment_set_id is NULL
1041 or ( :cp_assignment_set_id is not NULL
1042 and :cp_assignment_set_id in
1043 (select assignment_set_id
1044 from hr_assignment_set_amendments hasa
1045 where hasa.assignment_set_id = :cp_assignment_set_id
1046 and pcd.assignment_id = hasa.assignment_id
1047 )
1048 )
1049 )
1050 and ((:cp_cost_type = ''EST_MODE_COST''
1051 and pcd.cost_type in (''COST_TMP'',''EST_COST''))
1052 or
1053 (:cp_cost_type = ''EST_MODE_ALL''
1054 and pcd.cost_type in (''COST_TMP'',''EST_COST'',''EST_REVERSAL''))
1055 or
1056 (:cp_cost_type is null
1057 and pcd.cost_type = ''COST_TMP'')
1058 )
1059 order by pcd.last_name, pcd.first_name,
1060 pcd.middle_names, pcd.effective_date,pcd.cost_type';
1061 end if;
1062
1063 if p_element_type_id is not null then
1064 OPEN c_asg_costing_details
1065 FOR c_query USING to_date(p_start_date, 'YYYY/MM/DD HH24:MI:SS')
1066 ,to_date(p_end_date, 'YYYY/MM/DD HH24:MI:SS')
1067 ,p_assignment_set_id,p_assignment_set_id
1068 ,p_assignment_set_id,p_assignment_set_id
1069 ,p_element_type_id,p_element_type_id,p_element_type_id
1070 ,p_cost_type,p_cost_type,p_cost_type;
1071 elsif p_element_set_id is not null then
1072 OPEN c_asg_costing_details
1073 FOR c_query USING to_date(p_start_date, 'YYYY/MM/DD HH24:MI:SS')
1074 ,to_date(p_end_date, 'YYYY/MM/DD HH24:MI:SS')
1075 ,p_assignment_set_id,p_assignment_set_id
1076 ,p_assignment_set_id,p_assignment_set_id
1077 ,p_element_set_id,p_element_set_id,p_element_set_id
1078 ,p_element_set_id,p_element_set_id
1079 ,p_cost_type,p_cost_type,p_cost_type;
1080 elsif p_element_classification_id is not null then
1081 OPEN c_asg_costing_details
1082 FOR c_query USING to_date(p_start_date, 'YYYY/MM/DD HH24:MI:SS')
1083 ,to_date(p_end_date, 'YYYY/MM/DD HH24:MI:SS')
1084 ,p_assignment_set_id,p_assignment_set_id
1085 ,p_assignment_set_id,p_assignment_set_id
1086 ,p_element_classification_id,p_element_classification_id
1087 ,p_element_classification_id
1088 ,p_cost_type,p_cost_type,p_cost_type;
1089 elsif (p_element_type_id is null)
1090 and (p_element_set_id is null)
1091 and (p_element_classification_id is null) then
1092
1093
1094 OPEN c_asg_costing_details
1095 FOR c_query USING to_date(p_start_date, 'YYYY/MM/DD HH24:MI:SS')
1096 ,to_date(p_end_date, 'YYYY/MM/DD HH24:MI:SS')
1097 ,p_assignment_set_id,p_assignment_set_id
1098 ,p_assignment_set_id,p_assignment_set_id
1099 ,p_cost_type,p_cost_type,p_cost_type;
1100 end if;
1101
1102 loop
1103 fetch c_asg_costing_details into
1104 lv_cost_mode
1105 ,lv_consolidation_set_name
1106 ,lv_payroll_name
1107 ,lv_gre_name
1108 ,lv_org_name
1109 ,lv_location_code
1110 ,lv_emp_last_name
1111 ,lv_emp_first_name
1112 ,lv_emp_middle_names
1113 ,lv_emp_employee_number
1114 ,lv_assignment_number
1115 ,lv_element_name
1116 ,lv_input_value_name
1117 ,lv_uom -- Bug 3072270
1118 ,ln_credit_amount
1119 ,ln_debit_amount
1120 ,lv_emp_national_identifier
1121 ,ld_effective_date
1122 ,lv_concatenated_segments
1123 ,ln_assignment_id
1124 ,lv_segment1
1125 ,lv_segment2
1126 ,lv_segment3
1127 ,lv_segment4
1128 ,lv_segment5
1129 ,lv_segment6
1130 ,lv_segment7
1131 ,lv_segment8
1132 ,lv_segment9
1133 ,lv_segment10
1134 ,lv_segment11
1135 ,lv_segment12
1136 ,lv_segment13
1137 ,lv_segment14
1138 ,lv_segment15
1139 ,lv_segment16
1140 ,lv_segment17
1141 ,lv_segment18
1142 ,lv_segment19
1143 ,lv_segment20
1144 ,lv_segment21
1145 ,lv_segment22
1146 ,lv_segment23
1147 ,lv_segment24
1148 ,lv_segment25
1149 ,lv_segment26
1150 ,lv_segment27
1151 ,lv_segment28
1152 ,lv_segment29
1153 ,lv_segment30
1154 ;
1155
1156 if c_asg_costing_details%notfound then
1157 hr_utility.set_location(gv_package_name || '.costing_extract', 90);
1158 exit;
1159 end if;
1160
1161 lv_accrual_type:=nvl(hr_general.decode_lookup('PAY_PAYRPCBR',lv_cost_mode),' ');
1162 if p_output_file_type='HTML' and lv_accrual_type = ' ' then
1163 lv_accrual_type:=' ';
1164 end if;
1165
1166 /************************************************************
1167 ** If Assignment Set is used, pick up only those employee
1168 ** assignments which are part of the Assignment Set - STATIC
1169 ** or DYNAMIC.
1170 ************************************************************/
1171 hr_utility.set_location(gv_package_name || '.costing_extract', 100);
1172 hr_utility.trace('Assignment ID = ' || ln_assignment_id);
1173
1174 if hr_assignment_set.assignment_in_set(
1175 p_assignment_set_id
1176 ,ln_assignment_id) = 'Y' then
1177
1178
1179 hr_utility.set_location(gv_package_name || '.costing_extract', 110);
1180
1181 formated_static_data(
1182 p_consolidation_set_name => lv_consolidation_set_name
1183 ,p_payroll_name => lv_payroll_name
1184 ,p_gre_name => lv_gre_name
1185 ,p_emp_last_name => lv_emp_last_name
1186 ,p_emp_first_name => lv_emp_first_name
1187 ,p_emp_middle_names => lv_emp_middle_names
1188 ,p_action_effective_date => ld_effective_date
1189 ,p_element_name => lv_element_name
1190 ,p_input_value_name => lv_input_value_name
1191 ,p_uom => lv_uom -- Bug 3072270
1192 ,p_credit_amount => ln_credit_amount
1193 ,p_debit_amount => ln_debit_amount
1194 ,p_accrual_type => lv_accrual_type
1195 ,p_concatenated_segments => lv_concatenated_segments
1196 ,p_org_name => lv_org_name
1197 ,p_location_code => lv_location_code
1198 ,p_emp_employee_number => lv_emp_employee_number
1199 ,p_emp_national_identifier => lv_emp_national_identifier
1200 ,p_assignment_number => lv_assignment_number
1201 ,p_chk_ni_prt => lv_chk_ni_prt --Bug 4142845 Added by ssmukher
1202 ,p_output_file_type => p_output_file_type
1203 ,p_static_data1 => lv_data_row1
1204 ,p_static_data2 => lv_data_row2);
1205
1206 lv_data_row := lv_data_row1;
1207 hr_utility.set_location(gv_package_name || '.costing_extract', 120);
1208
1209 for i in ltr_costing_segment.first .. ltr_costing_segment.last loop
1210 if ltr_costing_segment(i).column_name = 'SEGMENT1' then
1211 lv_segment_value := lv_segment1;
1212 elsif ltr_costing_segment(i).column_name = 'SEGMENT2' then
1213 lv_segment_value := lv_segment2;
1214 elsif ltr_costing_segment(i).column_name = 'SEGMENT3' then
1215 lv_segment_value := lv_segment3;
1216 elsif ltr_costing_segment(i).column_name = 'SEGMENT4' then
1217 lv_segment_value := lv_segment4;
1218 elsif ltr_costing_segment(i).column_name = 'SEGMENT5' then
1219 lv_segment_value := lv_segment5;
1220 elsif ltr_costing_segment(i).column_name = 'SEGMENT6' then
1221 lv_segment_value := lv_segment6;
1222 elsif ltr_costing_segment(i).column_name = 'SEGMENT7' then
1223 lv_segment_value := lv_segment7;
1224 elsif ltr_costing_segment(i).column_name = 'SEGMENT8' then
1225 lv_segment_value := lv_segment8;
1226 elsif ltr_costing_segment(i).column_name = 'SEGMENT9' then
1227 lv_segment_value := lv_segment9;
1228 elsif ltr_costing_segment(i).column_name = 'SEGMENT10' then
1229 lv_segment_value := lv_segment10;
1230 elsif ltr_costing_segment(i).column_name = 'SEGMENT11' then
1231 lv_segment_value := lv_segment11;
1232 elsif ltr_costing_segment(i).column_name = 'SEGMENT12' then
1233 lv_segment_value := lv_segment12;
1234 elsif ltr_costing_segment(i).column_name = 'SEGMENT13' then
1235 lv_segment_value := lv_segment13;
1236 elsif ltr_costing_segment(i).column_name = 'SEGMENT14' then
1237 lv_segment_value := lv_segment14;
1238 elsif ltr_costing_segment(i).column_name = 'SEGMENT15' then
1239 lv_segment_value := lv_segment15;
1240 elsif ltr_costing_segment(i).column_name = 'SEGMENT16' then
1241 lv_segment_value := lv_segment16;
1242 elsif ltr_costing_segment(i).column_name = 'SEGMENT17' then
1243 lv_segment_value := lv_segment17;
1244 elsif ltr_costing_segment(i).column_name = 'SEGMENT18' then
1245 lv_segment_value := lv_segment18;
1246 elsif ltr_costing_segment(i).column_name = 'SEGMENT19' then
1247 lv_segment_value := lv_segment19;
1248 elsif ltr_costing_segment(i).column_name = 'SEGMENT20' then
1249 lv_segment_value := lv_segment20;
1250 elsif ltr_costing_segment(i).column_name = 'SEGMENT21' then
1251 lv_segment_value := lv_segment21;
1252 elsif ltr_costing_segment(i).column_name = 'SEGMENT22' then
1253 lv_segment_value := lv_segment22;
1254 elsif ltr_costing_segment(i).column_name = 'SEGMENT23' then
1255 lv_segment_value := lv_segment23;
1256 elsif ltr_costing_segment(i).column_name = 'SEGMENT24' then
1257 lv_segment_value := lv_segment24;
1258 elsif ltr_costing_segment(i).column_name = 'SEGMENT25' then
1259 lv_segment_value := lv_segment25;
1260 elsif ltr_costing_segment(i).column_name = 'SEGMENT26' then
1261 lv_segment_value := lv_segment26;
1262 elsif ltr_costing_segment(i).column_name = 'SEGMENT27' then
1263 lv_segment_value := lv_segment27;
1264 elsif ltr_costing_segment(i).column_name = 'SEGMENT28' then
1265 lv_segment_value := lv_segment28;
1266 elsif ltr_costing_segment(i).column_name = 'SEGMENT29' then
1267 lv_segment_value := lv_segment29;
1268 elsif ltr_costing_segment(i).column_name = 'SEGMENT30' then
1269 lv_segment_value := lv_segment30;
1270 end if;
1271
1272 lv_data_row := lv_data_row ||
1273 formated_data_string (
1274 p_input_string => lv_segment_value
1275 ,p_output_file_type => p_output_file_type);
1276 end loop ;
1277 hr_utility.set_location(gv_package_name || '.costing_extract', 130);
1278
1279 /****************************************************************
1280 ** Concatnating the second Header Label which includes the
1281 ** data set which is printed at the end of the report.
1282 ****************************************************************/
1283 lv_data_row := lv_data_row || lv_data_row2;
1284 hr_utility.set_location(gv_package_name || '.costing_extract', 140);
1285
1286 if p_output_file_type ='HTML' then
1287 lv_data_row := '<tr>' || lv_data_row || '</tr>' ;
1288 end if;
1289
1290 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, lv_data_row);
1291
1292 end if; /********** End of Assignment Set ************************/
1293
1294 /*****************************************************************
1295 ** initialize Data varaibles
1296 *****************************************************************/
1297 lv_data_row := null;
1298 lv_data_row1 := null;
1299 lv_data_row2 := null;
1300 end loop;
1301 close c_asg_costing_details;
1302
1303 /*****************************************************
1304 ** Close of the Data Section of the Report
1305 *****************************************************/
1306
1307 if p_output_file_type ='HTML' then
1308 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, '</table></body></html>');
1309 end if;
1310 hr_utility.trace('Concurrent Request ID = ' || FND_GLOBAL.CONC_REQUEST_ID);
1311
1312 END costing_extract;
1313
1314 function get_costing_tax_unit_id(p_ACTION_TYPE pay_payroll_actions.action_type%TYPE,
1315 p_TAX_UNIT_ID pay_assignment_actions.TAX_UNIT_ID%TYPE,
1316 p_assignment_action_id pay_assignment_actions.assignment_action_id%TYPE,
1317 p_element_type_id pay_element_types_f.element_type_id%TYPE
1318 ) return number IS
1319 CURSOR c_tax_unit is
1320 select paa.tax_unit_id
1321 from pay_run_results prr,
1322 PAY_ASSIGNMENT_ACTIONS paa,
1323 pay_action_interlocks pai
1324 where paa.assignment_action_id = prr.assignment_action_id
1325 AND paa.assignment_action_id = pai.LOCKED_ACTION_ID
1326 and pai.locking_action_id = p_assignment_action_id
1327 and prr.element_type_id = p_element_type_id;
1328 L_TAX_UNIT_ID pay_assignment_actions.TAX_UNIT_ID%TYPE;
1329 BEGIN
1330 IF P_ACTION_TYPE = 'EC' THEN
1331 return P_TAX_UNIT_ID;
1332 ELSE
1333 OPEN c_tax_unit;
1334 FETCH c_tax_unit INTO l_tax_unit_id;
1335 CLOSE c_tax_unit;
1336 return l_tax_unit_id;
1337 END IF;
1338 END;
1339
1340 function get_costing_tax_unit_name(p_tax_unit_id HR_ORGANIZATION_UNITS.ORGANIZATION_ID%TYPE)
1341 return VARCHAR2 IS
1342 CURSOR c_tax_unit_name is
1343 select HOU_GRE.NAME
1344 from HR_ORGANIZATION_UNITS HOU_GRE
1345 where HOU_GRE.ORGANIZATION_ID = p_tax_unit_id;
1346 L_TAX_UNIT_NAME hr_organization_units.NAME%TYPE;
1347 BEGIN
1348 IF p_tax_unit_id IS NULL THEN
1349 return NULL;
1350 END IF;
1351 IF g_tax_unit_name.EXISTS(p_tax_unit_id) then
1352 l_tax_unit_name := g_tax_unit_name(p_tax_unit_id);
1353 return l_tax_unit_name;
1354 END IF;
1355 OPEN c_tax_unit_name;
1356 FETCH c_tax_unit_name INTO l_tax_unit_name;
1357 CLOSE c_tax_unit_name;
1358 g_tax_unit_name(p_tax_unit_id) := l_tax_unit_name;
1359 return l_tax_unit_name;
1360 END;
1361 --begin
1362 --hr_utility.trace_on(null, 'COSTING');
1363 end pay_costing_detail_rep_pkg;