1 PACKAGE BODY pay_no_soe AS
2 /* $Header: pynosoe.pkb 120.0.12000000.1 2007/05/20 09:29:05 rlingama noship $ */
3 --
4 --
5 l_sql long;
6 g_debug boolean := hr_utility.debug_enabled;
7
8
9 /* ---------------------------------------------------------------------
10 Function : Elements1 (For the Earnings Section on the Norway SOE)
11
12 Text
13 ------------------------------------------------------------------------ */
14 function Elements1(p_assignment_action_id number) return long is
15 begin
16 hr_utility.trace('Entering elements1');
17 return getElements(p_assignment_action_id
18 ,pay_soe_util.getConfig('ELEMENTS1'));
19 hr_utility.trace('Leaving Elements1');
20 end Elements1;
21 --
22
23 /* ---------------------------------------------------------------------
24 Function : getElements
25
26 Text
27 ------------------------------------------------------------------------ */
28 function getElements(p_assignment_action_id number
29 ,p_element_set_name varchar2) return long is
30 begin
31 --
32 --
33 if g_debug then
34 hr_utility.set_location('Entering pay_no_soe.getElements', 10);
35 end if;
36 --
37 --
38 if p_element_set_name is null then
39 l_sql := null;
40 else
41 --
42
43 -- the sql statement below has been modfied for Norway SOE from the global SOE sql
44 -- added join with table pay_element_classifications to fetch the classification name
45 -- if the element classification is 'Earnings Adjustment', a negative value of rrv.result_value is taken
46 -- else the rrv.result_value is taken as it is
47
48 l_sql :=
49 'select /*+ ORDERED */ nvl(ettl.reporting_name,et.element_type_id) COL01
50 , nvl(ettl.reporting_name,ettl.element_name) COL02
51 , to_char(sum(decode(ec.classification_name,
52 ''Earnings Adjustment''
53 ,( 0 - FND_NUMBER.CANONICAL_TO_NUMBER(rrv.result_value))
54 ,FND_NUMBER.CANONICAL_TO_NUMBER(rrv.result_value))
55 ),fnd_currency.get_format_mask(:G_CURRENCY_CODE,40)) COL16
56 , decode(count(*),1,''1'',''2'') COL17 -- destination indicator
57 , decode(count(*),1,max(rr.run_result_id),max(et.element_type_id)) COL18
58 from pay_assignment_actions aa
59 , pay_run_results rr
60 , pay_run_result_values rrv
61 , pay_input_values_f iv
62 , pay_input_values_f_tl ivtl
63 , pay_element_types_f et
64 , pay_element_types_f_tl ettl
65 , pay_element_classifications ec
66 where aa.assignment_action_id :action_clause
67 and aa.assignment_action_id = rr.assignment_action_id
68 and rr.status in (''P'',''PA'')
69 and rr.run_result_id = rrv.run_result_id
70 and rr.element_type_id = et.element_type_id
71 and :effective_date between
72 et.effective_start_date and et.effective_end_date
73 and et.element_type_id = ettl.element_type_id
74 and rrv.input_value_id = iv.input_value_id
75 and iv.name = ''Pay Value''
76 and :effective_date between
77 iv.effective_start_date and iv.effective_end_date
78 and iv.input_value_id = ivtl.input_value_id
79 and ettl.language = userenv(''LANG'')
80 and ivtl.language = userenv(''LANG'')
81 and iv.element_type_id = et.element_type_id
82 and et.CLASSIFICATION_ID = ec.CLASSIFICATION_ID
83 and exists (select 1
84 from pay_element_set_members esm
85 ,pay_element_sets es
86 where et.element_type_id = esm.element_type_id
87 and iv.element_type_id = et.element_type_id
88 and esm.element_set_id = es.element_set_id
89 and ( es.BUSINESS_GROUP_ID IS NULL
90 OR es.BUSINESS_GROUP_ID = :business_group_id )
91 AND ( es.LEGISLATION_CODE IS NULL
92 OR es.LEGISLATION_CODE = '':legislation_code'' )
93 and es.element_set_name = '''|| p_element_set_name ||''' )
94 group by nvl(ettl.reporting_name,ettl.element_name)
95 , ettl.reporting_name
96 ,nvl(ettl.reporting_name,et.element_type_id)
97 order by nvl(ettl.reporting_name,ettl.element_name)';
98 --
99 end if;
100 --
101
102 --
103 if g_debug then
104 hr_utility.set_location('Leaving pay_no_soe.getElements', 20);
105 end if;
106 --
107 return l_sql;
108 --
109 end getElements;
110 --
111
112
113 -- End of the Package
114
115 END pay_no_soe;
[Home] [Help]