[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.4.12020000.2 2013/02/25 05:01:49 pparate ship $ */
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(10000); --for the cursor query (Bug 3179050) -- Increased size to 10000 (Bug 12726004)
594 c_clause1 varchar2(10000); --to store the optional where clause (Bug 3179050) -- Increased size to 10000 (Bug 12726004)
595
596 BEGIN
597
598 /* Bug 16375305: irrespective of profile value for icx numeric characters session
599 value is forced set to ',.'. Resetting value of this session parameter based
600 on value found in profile option */
601 IF FND_PROFILE.VALUE('ICX_NUMERIC_CHARACTERS') = ',.' THEN
602 execute immediate ('alter session set nls_numeric_characters ='',.''');
603 ELSE
604 execute immediate ('alter session set nls_numeric_characters =''.,''');
605 END IF;
606
607 hr_utility.trace('Cost Type = ' || p_cost_type);
608 hr_utility.set_location(gv_package_name || '.costing_extract', 10);
609 hr_utility.trace('Start Date = ' || p_start_date);
610 hr_utility.trace('End Date = ' || p_end_date);
611 hr_utility.trace('Business Group ID = '|| p_business_group_id);
612 hr_utility.trace('Classification ID = '|| nvl(to_char(p_element_classification_id), 'NULL'));
613 hr_utility.trace('Element Set ID = ' || nvl(to_char(p_element_set_id), 'NULL'));
614 hr_utility.trace('Element Type ID = ' || nvl(to_char(p_element_type_id), 'NULL'));
615 hr_utility.trace('Person ID = ' || p_person_id);
616 hr_utility.trace('Location ID = ' || p_location_id);
617 hr_utility.trace('Organization ID = ' || p_organization_id);
618 hr_utility.trace('Tax Unit ID = ' || p_tax_unit_id);
619 hr_utility.trace('Payroll ID = ' || p_payroll_id);
620 hr_utility.trace('Consolidation ID = ' || p_consolidation_set_id);
621 hr_utility.trace('Asgn Set ID = ' || p_assignment_set_id);
622 hr_utility.set_location(gv_package_name || '.costing_extract', 20);
623
624 /* Added by ssmukher for Bug 4142845 */
625 open c_leg_code(p_business_group_id);
626 fetch c_leg_code into lv_legislation_code;
627 close c_leg_code;
628
629 open c_national_identifier( lv_legislation_code);
630 fetch c_national_identifier into lv_chk_ni_prt;
631 if c_national_identifier%notfound then
632 lv_chk_ni_prt := 'Y';
633 close c_national_identifier;
634 else
635 close c_national_identifier;
636 end if;
637
638 formated_static_header( p_output_file_type
639 ,lv_header_label1
640 ,lv_header_label2
641 ,lv_chk_ni_prt -- --Bug 4142845
642 ,p_business_group_id);
643
644 lv_header_label := lv_header_label1;
645
646 hr_utility.set_location(gv_package_name || '.costing_extract', 30);
647 lv_header_label := lv_header_label ||
648 formated_data_string (
649 p_input_string => 'Accrual Type'
650 ,p_bold => 'Y'
651 ,p_output_file_type => p_output_file_type);
652
653 open c_costing_flex_id (p_business_group_id);
654 fetch c_costing_flex_id into ln_costing_id_flex_num;
655 if c_costing_flex_id%found then
656 hr_utility.set_location(gv_package_name || '.costing_extract', 40);
657 open c_costing_flex_segments (ln_costing_id_flex_num);
658 loop
659 fetch c_costing_flex_segments into lv_segment_name, lv_column_name;
660 if c_costing_flex_segments%notfound then
661 exit;
662 end if;
663 lv_header_label := lv_header_label ||
664 formated_data_string (
665 p_input_string => lv_segment_name
666 ,p_bold => 'Y'
667 ,p_output_file_type => p_output_file_type);
668
669 ltr_costing_segment(ln_count).segment_label := lv_segment_name;
670 ltr_costing_segment(ln_count).column_name := lv_column_name;
671 ln_count := ln_count + 1;
672
673 end loop;
674 close c_costing_flex_segments;
675
676 end if;
677 close c_costing_flex_id;
678 hr_utility.set_location(gv_package_name || '.costing_extract', 50);
679
680 /****************************************************************
681 ** Concatnating the second Header Label which includes the
682 ** data set which has to be printed at the end of the report.
683 ****************************************************************/
684 lv_header_label := lv_header_label || lv_header_label2;
685
686 hr_utility.set_location(gv_package_name || '.costing_extract', 60);
687 hr_utility.trace('Static and Element Label = ' || lv_header_label);
688
689 gv_title := hr_general.decode_fnd_comm_lookup
690 ('PAYROLL_REPORTS',
691 'COSTING_REPORT_TITLE');
692
693 fnd_file.put_line(fnd_file.output, formated_header_string(
694 gv_title
695 ,p_output_file_type
696 ));
697
698 hr_utility.set_location(gv_package_name || '.costing_extract', 70);
699 /****************************************************************
700 ** Print the Header Information. If the format is HTML then open
701 ** the body and table before printing the header info, otherwise
702 ** just print the header information.
703 ****************************************************************/
704 if p_output_file_type ='HTML' then
705 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, '<body>');
706 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, '<table border=1>');
707 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, '<tr>');
708 end if;
709
710 fnd_file.put_line(fnd_file.output, lv_header_label);
711
712 if p_output_file_type ='HTML' then
713 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, '</tr>');
714 end if;
715
716 hr_utility.set_location(gv_package_name || '.costing_extract', 80);
717 /*****************************************************
718 ** Start of the Data Section of the Report
719 *****************************************************/
720 /*Bug 3179050 - changed cursor c_asg_costing_details to a REF CURSOR*/
721 c_clause1:=get_optional_where_clause(p_payroll_id,
722 p_consolidation_set_id,
723 p_tax_unit_id,
724 p_organization_id,
725 p_location_id,
726 p_person_id);
727 --3581378
728 if p_element_type_id is not null then
729 c_query :=
730 'select pcd.cost_type
731 ,pcd.consolidation_set_name
732 ,pcd.payroll_name
733 ,pcd.gre_name
734 ,pcd.organization_name
735 ,pcd.location_code
736 ,pcd.last_name
737 ,pcd.first_name
738 ,pcd.middle_names
739 ,pcd.employee_number
740 ,pcd.assignment_number
741 ,nvl(pcd.reporting_name,pcd.element_name)
742 ,pcd.input_value_name
743 ,pcd.uom
744 ,pcd.credit_amount
745 ,pcd.debit_amount
746 ,pcd.national_identifier
747 ,pcd.effective_date
748 ,pcd.concatenated_segments
749 ,pcd.assignment_id
750 ,pcd.segment1
751 ,pcd.segment2
752 ,pcd.segment3
753 ,pcd.segment4
754 ,pcd.segment5
755 ,pcd.segment6
756 ,pcd.segment7
757 ,pcd.segment8
758 ,pcd.segment9
759 ,pcd.segment10
760 ,pcd.segment11
761 ,pcd.segment12
762 ,pcd.segment13
763 ,pcd.segment14
764 ,pcd.segment15
765 ,pcd.segment16
766 ,pcd.segment17
767 ,pcd.segment18
768 ,pcd.segment19
769 ,pcd.segment20
770 ,pcd.segment21
771 ,pcd.segment22
772 ,pcd.segment23
773 ,pcd.segment24
774 ,pcd.segment25
775 ,pcd.segment26
776 ,pcd.segment27
777 ,pcd.segment28
778 ,pcd.segment29
779 ,pcd.segment30
780 from pay_costing_details_v pcd
781 where pcd.effective_date between :cp_start_date and :cp_end_date
782 ' || c_clause1 || '
783 and pcd.business_group_id = ' || NVL(p_business_group_id,0) || '
784 and (:cp_assignment_set_id is NULL
785 or ( :cp_assignment_set_id is not NULL
786 and exists (SELECT 1
787 FROM hr_assignment_sets aset
788 WHERE aset.assignment_set_id = :cp_assignment_set_id
789 and nvl(aset.payroll_id,pcd.payroll_id) = pcd.payroll_id
790 and (not exists
791 (select 1
792 from hr_assignment_set_amendments hasa
793 where hasa.assignment_set_id = aset.assignment_set_id
794 and hasa.include_or_exclude = ''I'')
795 or exists (select 1
796 from hr_assignment_set_amendments hasa
797 where hasa.assignment_set_id = aset.assignment_set_id
798 and hasa.assignment_id = pcd.assignment_id
799 and hasa.include_or_exclude = ''I''))
800 and not exists (select 1
801 from hr_assignment_set_amendments hasa
802 where hasa.assignment_set_id = aset.assignment_set_id
803 and hasa.assignment_id = pcd.assignment_id
804 and hasa.include_or_exclude = ''E''))
805 )
806 )
807 and (:cp_element_type_id is null
808 or (:cp_element_type_id is not null
809 and pcd.element_type_id = :cp_element_type_id)
810 )
811 and ((:cp_cost_type = ''EST_MODE_COST''
812 and pcd.cost_type in (''COST_TMP'',''EST_COST''))
813 or
814 (:cp_cost_type = ''EST_MODE_ALL''
815 and pcd.cost_type in (''COST_TMP'',''EST_COST'',''EST_REVERSAL''))
816 or
817 (:cp_cost_type is null
818 and pcd.cost_type = ''COST_TMP'')
819 )
820 order by pcd.last_name, pcd.first_name,
821 pcd.middle_names, pcd.effective_date,pcd.cost_type';
822
823 elsif p_element_set_id is not null then
824 c_query :=
825 'select pcd.cost_type
826 ,pcd.consolidation_set_name
827 ,pcd.payroll_name
828 ,pcd.gre_name
829 ,pcd.organization_name
830 ,pcd.location_code
831 ,pcd.last_name
832 ,pcd.first_name
833 ,pcd.middle_names
834 ,pcd.employee_number
835 ,pcd.assignment_number
836 ,nvl(pcd.reporting_name,pcd.element_name)
837 ,pcd.input_value_name
838 ,pcd.uom
839 ,pcd.credit_amount
840 ,pcd.debit_amount
841 ,pcd.national_identifier
842 ,pcd.effective_date
843 ,pcd.concatenated_segments
844 ,pcd.assignment_id
845 ,pcd.segment1
846 ,pcd.segment2
847 ,pcd.segment3
848 ,pcd.segment4
849 ,pcd.segment5
850 ,pcd.segment6
851 ,pcd.segment7
852 ,pcd.segment8
853 ,pcd.segment9
854 ,pcd.segment10
855 ,pcd.segment11
856 ,pcd.segment12
857 ,pcd.segment13
858 ,pcd.segment14
859 ,pcd.segment15
860 ,pcd.segment16
861 ,pcd.segment17
862 ,pcd.segment18
863 ,pcd.segment19
864 ,pcd.segment20
865 ,pcd.segment21
866 ,pcd.segment22
867 ,pcd.segment23
868 ,pcd.segment24
869 ,pcd.segment25
870 ,pcd.segment26
871 ,pcd.segment27
872 ,pcd.segment28
873 ,pcd.segment29
874 ,pcd.segment30
875 from pay_costing_details_v pcd
876 where pcd.effective_date between :cp_start_date and :cp_end_date
877 ' || c_clause1 || '
878 and pcd.business_group_id = ' || NVL(p_business_group_id,0) || '
879 and (:cp_assignment_set_id is NULL
880 or ( :cp_assignment_set_id is not NULL
881 and exists (SELECT 1
882 FROM hr_assignment_sets aset
883 WHERE aset.assignment_set_id = :cp_assignment_set_id
884 and nvl(aset.payroll_id,pcd.payroll_id) = pcd.payroll_id
885 and (not exists
886 (select 1
887 from hr_assignment_set_amendments hasa
888 where hasa.assignment_set_id = aset.assignment_set_id
889 and hasa.include_or_exclude = ''I'')
890 or exists (select 1
891 from hr_assignment_set_amendments hasa
892 where hasa.assignment_set_id = aset.assignment_set_id
893 and hasa.assignment_id = pcd.assignment_id
894 and hasa.include_or_exclude = ''I''))
895 and not exists (select 1
896 from hr_assignment_set_amendments hasa
897 where hasa.assignment_set_id = aset.assignment_set_id
898 and hasa.assignment_id = pcd.assignment_id
899 and hasa.include_or_exclude = ''E''))
900 )
901 )
902 and (:cp_element_set_id is null
903 or (:cp_element_set_id is not null
904 and exists
905 (select ''x'' from pay_element_type_rules petr
906 where petr.element_set_id = :cp_element_set_id
907 and petr.element_type_id = pcd.element_type_id
908 and petr.include_or_exclude = ''I''
909 union all
910 select ''x'' from pay_element_types_f pet1
911 where pet1.classification_id in
912 (select classification_id
913 from pay_ele_classification_rules
914 where element_set_id = :cp_element_set_id)
915 and pet1.element_type_id = pcd.element_type_id
916 minus
917 select ''x'' from pay_element_type_rules petr
918 where petr.element_set_id = :cp_element_set_id
919 and petr.element_type_id = pcd.element_type_id
920 and petr.include_or_exclude = ''E''
921 )
922 )
923 )
924 and ((:cp_cost_type = ''EST_MODE_COST''
925 and pcd.cost_type in (''COST_TMP'',''EST_COST''))
926 or
927 (:cp_cost_type = ''EST_MODE_ALL''
928 and pcd.cost_type in (''COST_TMP'',''EST_COST'',''EST_REVERSAL''))
929 or
930 (:cp_cost_type is null
931 and pcd.cost_type = ''COST_TMP'')
932 )
933 order by pcd.last_name, pcd.first_name,
934 pcd.middle_names, pcd.effective_date,pcd.cost_type';
935 elsif p_element_classification_id is not null then
936 c_query :=
937 'select pcd.cost_type
938 ,pcd.consolidation_set_name
939 ,pcd.payroll_name
940 ,pcd.gre_name
941 ,pcd.organization_name
942 ,pcd.location_code
943 ,pcd.last_name
944 ,pcd.first_name
945 ,pcd.middle_names
946 ,pcd.employee_number
947 ,pcd.assignment_number
948 ,nvl(pcd.reporting_name,pcd.element_name)
949 ,pcd.input_value_name
950 ,pcd.uom
951 ,pcd.credit_amount
952 ,pcd.debit_amount
953 ,pcd.national_identifier
954 ,pcd.effective_date
955 ,pcd.concatenated_segments
956 ,pcd.assignment_id
957 ,pcd.segment1
958 ,pcd.segment2
959 ,pcd.segment3
960 ,pcd.segment4
961 ,pcd.segment5
962 ,pcd.segment6
963 ,pcd.segment7
964 ,pcd.segment8
965 ,pcd.segment9
966 ,pcd.segment10
967 ,pcd.segment11
968 ,pcd.segment12
969 ,pcd.segment13
970 ,pcd.segment14
971 ,pcd.segment15
972 ,pcd.segment16
973 ,pcd.segment17
974 ,pcd.segment18
975 ,pcd.segment19
976 ,pcd.segment20
977 ,pcd.segment21
978 ,pcd.segment22
979 ,pcd.segment23
980 ,pcd.segment24
981 ,pcd.segment25
982 ,pcd.segment26
983 ,pcd.segment27
984 ,pcd.segment28
985 ,pcd.segment29
986 ,pcd.segment30
987 from pay_costing_details_v pcd
988 where pcd.effective_date between :cp_start_date and :cp_end_date
989 ' || c_clause1 || '
990 and pcd.business_group_id = ' || NVL(p_business_group_id,0) || '
991 and (:cp_assignment_set_id is NULL
992 or ( :cp_assignment_set_id is not NULL
993 and exists (SELECT 1
994 FROM hr_assignment_sets aset
995 WHERE aset.assignment_set_id = :cp_assignment_set_id
996 and nvl(aset.payroll_id,pcd.payroll_id) = pcd.payroll_id
997 and (not exists
998 (select 1
999 from hr_assignment_set_amendments hasa
1000 where hasa.assignment_set_id = aset.assignment_set_id
1001 and hasa.include_or_exclude = ''I'')
1002 or exists (select 1
1003 from hr_assignment_set_amendments hasa
1004 where hasa.assignment_set_id = aset.assignment_set_id
1005 and hasa.assignment_id = pcd.assignment_id
1006 and hasa.include_or_exclude = ''I''))
1007 and not exists (select 1
1008 from hr_assignment_set_amendments hasa
1009 where hasa.assignment_set_id = aset.assignment_set_id
1010 and hasa.assignment_id = pcd.assignment_id
1011 and hasa.include_or_exclude = ''E''))
1012 )
1013 )
1014 and (:cp_element_classification_id is null
1015 or (:cp_element_classification_id is not null
1016 and pcd.classification_id = :cp_element_classification_id)
1017 )
1018 and ((:cp_cost_type = ''EST_MODE_COST''
1019 and pcd.cost_type in (''COST_TMP'',''EST_COST''))
1020 or
1021 (:cp_cost_type = ''EST_MODE_ALL''
1022 and pcd.cost_type in (''COST_TMP'',''EST_COST'',''EST_REVERSAL''))
1023 or
1024 (:cp_cost_type is null
1025 and pcd.cost_type = ''COST_TMP'')
1026 )
1027 order by pcd.last_name, pcd.first_name,
1028 pcd.middle_names, pcd.effective_date,pcd.cost_type';
1029 elsif (p_element_type_id is null)
1030 and (p_element_set_id is null)
1031 and (p_element_classification_id is null) then
1032 c_query :=
1033 'select pcd.cost_type
1034 ,pcd.consolidation_set_name
1035 ,pcd.payroll_name
1036 ,pcd.gre_name
1037 ,pcd.organization_name
1038 ,pcd.location_code
1039 ,pcd.last_name
1040 ,pcd.first_name
1041 ,pcd.middle_names
1042 ,pcd.employee_number
1043 ,pcd.assignment_number
1044 ,nvl(pcd.reporting_name,pcd.element_name)
1045 ,pcd.input_value_name
1046 ,pcd.uom
1047 ,pcd.credit_amount
1048 ,pcd.debit_amount
1049 ,pcd.national_identifier
1050 ,pcd.effective_date
1051 ,pcd.concatenated_segments
1052 ,pcd.assignment_id
1053 ,pcd.segment1
1054 ,pcd.segment2
1055 ,pcd.segment3
1056 ,pcd.segment4
1057 ,pcd.segment5
1058 ,pcd.segment6
1059 ,pcd.segment7
1060 ,pcd.segment8
1061 ,pcd.segment9
1062 ,pcd.segment10
1063 ,pcd.segment11
1064 ,pcd.segment12
1065 ,pcd.segment13
1066 ,pcd.segment14
1067 ,pcd.segment15
1068 ,pcd.segment16
1069 ,pcd.segment17
1070 ,pcd.segment18
1071 ,pcd.segment19
1072 ,pcd.segment20
1073 ,pcd.segment21
1074 ,pcd.segment22
1075 ,pcd.segment23
1076 ,pcd.segment24
1077 ,pcd.segment25
1078 ,pcd.segment26
1079 ,pcd.segment27
1080 ,pcd.segment28
1081 ,pcd.segment29
1082 ,pcd.segment30
1083 from pay_costing_details_v pcd
1084 where pcd.effective_date between :cp_start_date and :cp_end_date
1085 ' || c_clause1 || '
1086 and pcd.business_group_id = ' || NVL(p_business_group_id,0) || '
1087 and (:cp_assignment_set_id is NULL
1088 or ( :cp_assignment_set_id is not NULL
1089 and exists (SELECT 1
1090 FROM hr_assignment_sets aset
1091 WHERE aset.assignment_set_id = :cp_assignment_set_id
1092 and nvl(aset.payroll_id,pcd.payroll_id) = pcd.payroll_id
1093 and (not exists
1094 (select 1
1095 from hr_assignment_set_amendments hasa
1096 where hasa.assignment_set_id = aset.assignment_set_id
1097 and hasa.include_or_exclude = ''I'')
1098 or exists (select 1
1099 from hr_assignment_set_amendments hasa
1100 where hasa.assignment_set_id = aset.assignment_set_id
1101 and hasa.assignment_id = pcd.assignment_id
1102 and hasa.include_or_exclude = ''I''))
1103 and not exists (select 1
1104 from hr_assignment_set_amendments hasa
1105 where hasa.assignment_set_id = aset.assignment_set_id
1106 and hasa.assignment_id = pcd.assignment_id
1107 and hasa.include_or_exclude = ''E''))
1108 )
1109 )
1110 and ((:cp_cost_type = ''EST_MODE_COST''
1111 and pcd.cost_type in (''COST_TMP'',''EST_COST''))
1112 or
1113 (:cp_cost_type = ''EST_MODE_ALL''
1114 and pcd.cost_type in (''COST_TMP'',''EST_COST'',''EST_REVERSAL''))
1115 or
1116 (:cp_cost_type is null
1117 and pcd.cost_type = ''COST_TMP'')
1118 )
1119 order by pcd.last_name, pcd.first_name,
1120 pcd.middle_names, pcd.effective_date,pcd.cost_type';
1121 end if;
1122
1123 if p_element_type_id is not null then
1124 OPEN c_asg_costing_details
1125 FOR c_query USING to_date(p_start_date, 'YYYY/MM/DD HH24:MI:SS')
1126 ,to_date(p_end_date, 'YYYY/MM/DD HH24:MI:SS')
1127 ,p_assignment_set_id,p_assignment_set_id
1128 ,p_assignment_set_id
1129 --,p_assignment_set_id
1130 ,p_element_type_id,p_element_type_id,p_element_type_id
1131 ,p_cost_type,p_cost_type,p_cost_type;
1132 elsif p_element_set_id is not null then
1133 OPEN c_asg_costing_details
1134 FOR c_query USING to_date(p_start_date, 'YYYY/MM/DD HH24:MI:SS')
1135 ,to_date(p_end_date, 'YYYY/MM/DD HH24:MI:SS')
1136 ,p_assignment_set_id,p_assignment_set_id
1137 ,p_assignment_set_id
1138 --,p_assignment_set_id
1139 ,p_element_set_id,p_element_set_id,p_element_set_id
1140 ,p_element_set_id,p_element_set_id
1141 ,p_cost_type,p_cost_type,p_cost_type;
1142 elsif p_element_classification_id is not null then
1143 OPEN c_asg_costing_details
1144 FOR c_query USING to_date(p_start_date, 'YYYY/MM/DD HH24:MI:SS')
1145 ,to_date(p_end_date, 'YYYY/MM/DD HH24:MI:SS')
1146 ,p_assignment_set_id,p_assignment_set_id
1147 ,p_assignment_set_id
1148 --,p_assignment_set_id
1149 ,p_element_classification_id,p_element_classification_id
1150 ,p_element_classification_id
1151 ,p_cost_type,p_cost_type,p_cost_type;
1152 elsif (p_element_type_id is null)
1153 and (p_element_set_id is null)
1154 and (p_element_classification_id is null) then
1155
1156
1157 OPEN c_asg_costing_details
1158 FOR c_query USING to_date(p_start_date, 'YYYY/MM/DD HH24:MI:SS')
1159 ,to_date(p_end_date, 'YYYY/MM/DD HH24:MI:SS')
1160 ,p_assignment_set_id,p_assignment_set_id
1161 ,p_assignment_set_id
1162 --,p_assignment_set_id
1163 ,p_cost_type,p_cost_type,p_cost_type;
1164 end if;
1165
1166 loop
1167 fetch c_asg_costing_details into
1168 lv_cost_mode
1169 ,lv_consolidation_set_name
1170 ,lv_payroll_name
1171 ,lv_gre_name
1172 ,lv_org_name
1173 ,lv_location_code
1174 ,lv_emp_last_name
1175 ,lv_emp_first_name
1176 ,lv_emp_middle_names
1177 ,lv_emp_employee_number
1178 ,lv_assignment_number
1179 ,lv_element_name
1180 ,lv_input_value_name
1181 ,lv_uom -- Bug 3072270
1182 ,ln_credit_amount
1183 ,ln_debit_amount
1184 ,lv_emp_national_identifier
1185 ,ld_effective_date
1186 ,lv_concatenated_segments
1187 ,ln_assignment_id
1188 ,lv_segment1
1189 ,lv_segment2
1190 ,lv_segment3
1191 ,lv_segment4
1192 ,lv_segment5
1193 ,lv_segment6
1194 ,lv_segment7
1195 ,lv_segment8
1196 ,lv_segment9
1197 ,lv_segment10
1198 ,lv_segment11
1199 ,lv_segment12
1200 ,lv_segment13
1201 ,lv_segment14
1202 ,lv_segment15
1203 ,lv_segment16
1204 ,lv_segment17
1205 ,lv_segment18
1206 ,lv_segment19
1207 ,lv_segment20
1208 ,lv_segment21
1209 ,lv_segment22
1210 ,lv_segment23
1211 ,lv_segment24
1212 ,lv_segment25
1213 ,lv_segment26
1214 ,lv_segment27
1215 ,lv_segment28
1216 ,lv_segment29
1217 ,lv_segment30
1218 ;
1219
1220 if c_asg_costing_details%notfound then
1221 hr_utility.set_location(gv_package_name || '.costing_extract', 90);
1222 exit;
1223 end if;
1224
1225 lv_accrual_type:=nvl(hr_general.decode_lookup('PAY_PAYRPCBR',lv_cost_mode),' ');
1226 if p_output_file_type='HTML' and lv_accrual_type = ' ' then
1227 lv_accrual_type:=' ';
1228 end if;
1229
1230 /************************************************************
1231 ** If Assignment Set is used, pick up only those employee
1232 ** assignments which are part of the Assignment Set - STATIC
1233 ** or DYNAMIC.
1234 ************************************************************/
1235 hr_utility.set_location(gv_package_name || '.costing_extract', 100);
1236 hr_utility.trace('Assignment ID = ' || ln_assignment_id);
1237
1238 /* for bug 9569291
1239 if hr_assignment_set.assignment_in_set(
1240 p_assignment_set_id
1241 ,ln_assignment_id) = 'Y' then
1242 */
1243
1244 hr_utility.set_location(gv_package_name || '.costing_extract', 110);
1245
1246 formated_static_data(
1247 p_consolidation_set_name => lv_consolidation_set_name
1248 ,p_payroll_name => lv_payroll_name
1249 ,p_gre_name => lv_gre_name
1250 ,p_emp_last_name => lv_emp_last_name
1251 ,p_emp_first_name => lv_emp_first_name
1252 ,p_emp_middle_names => lv_emp_middle_names
1253 ,p_action_effective_date => ld_effective_date
1254 ,p_element_name => lv_element_name
1255 ,p_input_value_name => lv_input_value_name
1256 ,p_uom => lv_uom -- Bug 3072270
1257 ,p_credit_amount => ln_credit_amount
1258 ,p_debit_amount => ln_debit_amount
1259 ,p_accrual_type => lv_accrual_type
1260 ,p_concatenated_segments => lv_concatenated_segments
1261 ,p_org_name => lv_org_name
1262 ,p_location_code => lv_location_code
1263 ,p_emp_employee_number => lv_emp_employee_number
1264 ,p_emp_national_identifier => lv_emp_national_identifier
1265 ,p_assignment_number => lv_assignment_number
1266 ,p_chk_ni_prt => lv_chk_ni_prt --Bug 4142845 Added by ssmukher
1267 ,p_output_file_type => p_output_file_type
1268 ,p_static_data1 => lv_data_row1
1269 ,p_static_data2 => lv_data_row2);
1270
1271 lv_data_row := lv_data_row1;
1272 hr_utility.set_location(gv_package_name || '.costing_extract', 120);
1273
1274 for i in ltr_costing_segment.first .. ltr_costing_segment.last loop
1275 if ltr_costing_segment(i).column_name = 'SEGMENT1' then
1276 lv_segment_value := lv_segment1;
1277 elsif ltr_costing_segment(i).column_name = 'SEGMENT2' then
1278 lv_segment_value := lv_segment2;
1279 elsif ltr_costing_segment(i).column_name = 'SEGMENT3' then
1280 lv_segment_value := lv_segment3;
1281 elsif ltr_costing_segment(i).column_name = 'SEGMENT4' then
1282 lv_segment_value := lv_segment4;
1283 elsif ltr_costing_segment(i).column_name = 'SEGMENT5' then
1284 lv_segment_value := lv_segment5;
1285 elsif ltr_costing_segment(i).column_name = 'SEGMENT6' then
1286 lv_segment_value := lv_segment6;
1287 elsif ltr_costing_segment(i).column_name = 'SEGMENT7' then
1288 lv_segment_value := lv_segment7;
1289 elsif ltr_costing_segment(i).column_name = 'SEGMENT8' then
1290 lv_segment_value := lv_segment8;
1291 elsif ltr_costing_segment(i).column_name = 'SEGMENT9' then
1292 lv_segment_value := lv_segment9;
1293 elsif ltr_costing_segment(i).column_name = 'SEGMENT10' then
1294 lv_segment_value := lv_segment10;
1295 elsif ltr_costing_segment(i).column_name = 'SEGMENT11' then
1296 lv_segment_value := lv_segment11;
1297 elsif ltr_costing_segment(i).column_name = 'SEGMENT12' then
1298 lv_segment_value := lv_segment12;
1299 elsif ltr_costing_segment(i).column_name = 'SEGMENT13' then
1300 lv_segment_value := lv_segment13;
1301 elsif ltr_costing_segment(i).column_name = 'SEGMENT14' then
1302 lv_segment_value := lv_segment14;
1303 elsif ltr_costing_segment(i).column_name = 'SEGMENT15' then
1304 lv_segment_value := lv_segment15;
1305 elsif ltr_costing_segment(i).column_name = 'SEGMENT16' then
1306 lv_segment_value := lv_segment16;
1307 elsif ltr_costing_segment(i).column_name = 'SEGMENT17' then
1308 lv_segment_value := lv_segment17;
1309 elsif ltr_costing_segment(i).column_name = 'SEGMENT18' then
1310 lv_segment_value := lv_segment18;
1311 elsif ltr_costing_segment(i).column_name = 'SEGMENT19' then
1312 lv_segment_value := lv_segment19;
1313 elsif ltr_costing_segment(i).column_name = 'SEGMENT20' then
1314 lv_segment_value := lv_segment20;
1315 elsif ltr_costing_segment(i).column_name = 'SEGMENT21' then
1316 lv_segment_value := lv_segment21;
1317 elsif ltr_costing_segment(i).column_name = 'SEGMENT22' then
1318 lv_segment_value := lv_segment22;
1319 elsif ltr_costing_segment(i).column_name = 'SEGMENT23' then
1320 lv_segment_value := lv_segment23;
1321 elsif ltr_costing_segment(i).column_name = 'SEGMENT24' then
1322 lv_segment_value := lv_segment24;
1323 elsif ltr_costing_segment(i).column_name = 'SEGMENT25' then
1324 lv_segment_value := lv_segment25;
1325 elsif ltr_costing_segment(i).column_name = 'SEGMENT26' then
1326 lv_segment_value := lv_segment26;
1327 elsif ltr_costing_segment(i).column_name = 'SEGMENT27' then
1328 lv_segment_value := lv_segment27;
1329 elsif ltr_costing_segment(i).column_name = 'SEGMENT28' then
1330 lv_segment_value := lv_segment28;
1331 elsif ltr_costing_segment(i).column_name = 'SEGMENT29' then
1332 lv_segment_value := lv_segment29;
1333 elsif ltr_costing_segment(i).column_name = 'SEGMENT30' then
1334 lv_segment_value := lv_segment30;
1335 end if;
1336
1337 lv_data_row := lv_data_row ||
1338 formated_data_string (
1339 p_input_string => lv_segment_value
1340 ,p_output_file_type => p_output_file_type);
1341 end loop ;
1342 hr_utility.set_location(gv_package_name || '.costing_extract', 130);
1343
1344 /****************************************************************
1345 ** Concatnating the second Header Label which includes the
1346 ** data set which is printed at the end of the report.
1347 ****************************************************************/
1348 lv_data_row := lv_data_row || lv_data_row2;
1349 hr_utility.set_location(gv_package_name || '.costing_extract', 140);
1350
1351 if p_output_file_type ='HTML' then
1352 lv_data_row := '<tr>' || lv_data_row || '</tr>' ;
1353 end if;
1354
1355 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, lv_data_row);
1356
1357 -- end if; /********** End of Assignment Set ************************/
1358
1359 /*****************************************************************
1360 ** initialize Data varaibles
1361 *****************************************************************/
1362 lv_data_row := null;
1363 lv_data_row1 := null;
1364 lv_data_row2 := null;
1365 end loop;
1366 close c_asg_costing_details;
1367
1368 /*****************************************************
1369 ** Close of the Data Section of the Report
1370 *****************************************************/
1371
1372 if p_output_file_type ='HTML' then
1373 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, '</table></body></html>');
1374 end if;
1375 hr_utility.trace('Concurrent Request ID = ' || FND_GLOBAL.CONC_REQUEST_ID);
1376
1377 END costing_extract;
1378
1379 function get_costing_tax_unit_id(p_ACTION_TYPE pay_payroll_actions.action_type%TYPE,
1380 p_TAX_UNIT_ID pay_assignment_actions.TAX_UNIT_ID%TYPE,
1381 p_assignment_action_id pay_assignment_actions.assignment_action_id%TYPE,
1382 p_element_type_id pay_element_types_f.element_type_id%TYPE
1383 ) return number IS
1384 CURSOR c_tax_unit is
1385 select paa.tax_unit_id
1386 from pay_run_results prr,
1387 PAY_ASSIGNMENT_ACTIONS paa,
1388 pay_action_interlocks pai
1389 where paa.assignment_action_id = prr.assignment_action_id
1390 AND paa.assignment_action_id = pai.LOCKED_ACTION_ID
1391 and pai.locking_action_id = p_assignment_action_id
1392 and prr.element_type_id = p_element_type_id;
1393 L_TAX_UNIT_ID pay_assignment_actions.TAX_UNIT_ID%TYPE;
1394 BEGIN
1395 IF P_ACTION_TYPE = 'EC' THEN
1396 return P_TAX_UNIT_ID;
1397 ELSE
1398 OPEN c_tax_unit;
1399 FETCH c_tax_unit INTO l_tax_unit_id;
1400 CLOSE c_tax_unit;
1401 return l_tax_unit_id;
1402 END IF;
1403 END;
1404
1405 function get_costing_tax_unit_name(p_tax_unit_id HR_ORGANIZATION_UNITS.ORGANIZATION_ID%TYPE)
1406 return VARCHAR2 IS
1407 CURSOR c_tax_unit_name is
1408 select HOU_GRE.NAME
1409 from HR_ORGANIZATION_UNITS HOU_GRE
1410 where HOU_GRE.ORGANIZATION_ID = p_tax_unit_id;
1411 L_TAX_UNIT_NAME hr_organization_units.NAME%TYPE;
1412 BEGIN
1413 IF p_tax_unit_id IS NULL THEN
1414 return NULL;
1415 END IF;
1416 IF g_tax_unit_name.EXISTS(p_tax_unit_id) then
1417 l_tax_unit_name := g_tax_unit_name(p_tax_unit_id);
1418 return l_tax_unit_name;
1419 END IF;
1420 OPEN c_tax_unit_name;
1421 FETCH c_tax_unit_name INTO l_tax_unit_name;
1422 CLOSE c_tax_unit_name;
1423 g_tax_unit_name(p_tax_unit_id) := l_tax_unit_name;
1424 return l_tax_unit_name;
1425 END;
1426 --begin
1427 --hr_utility.trace_on(null, 'COSTING');
1428 end pay_costing_detail_rep_pkg;