1 PACKAGE BODY PAY_PL_SOE as
2 /* $Header: pyplsoep.pkb 120.0 2005/10/14 03:53:12 mseshadr noship $ */
3
4 l_sql long;
5 g_debug boolean := hr_utility.debug_enabled;
6
7 function employee(p_assignment_action_id in number)
8 return long is
9 begin
10
11
12 l_sql := 'select papf.full_name COL01,
13 papf.employee_number COL02, /* Employee Number */
14 pay_pl_utility.pay_pl_nip_format(papf.PER_INFORMATION1) COL03, /* NIP */
15 pap.name COL04, /* Position */
16 papf.national_identifier COL05, /* PESEL */
17 hr_general.decode_lookup(''PL_CONTRACT_CATEGORY'',haaf.segment3) COL07, /* Contract Category */
18 hr_general.decode_lookup(decode(haaf.segment3,''NORMAL'',''PL_CONTRACT_TYPE_NORMAL'',''CIVIL'',
19 ''PL_CONTRACT_TYPE_CIVIL''),haaf.segment4) COL08, /* Contract Type */
20 hou.name COL09, /* Organization Name */
21 hr_general.decode_lookup(''NATIONALITY'',papf.nationality) COL11, /* Nationality */
22 hr_general.decode_lookup(''PL_CITIZENSHIP'',papf.per_information8) COL12 /* Citizenship */
23 from per_all_people_f papf,
24 per_all_assignments_f paaf,
25 hr_soft_coding_keyflex haaf,
26 hr_organization_units hou,
27 per_all_positions pap
28 where paaf.payroll_id = :payroll_id and
29 :effective_date between papf.effective_start_date and papf.effective_end_date and
30 :effective_date between paaf.effective_start_date and paaf.effective_end_date and
31 paaf.assignment_id = :assignment_id and
32 paaf.business_group_id = :business_group_id and
33 papf.business_group_id = :business_group_id and
34 papf.person_id = paaf.person_id and
35 haaf.SOFT_CODING_KEYFLEX_ID = paaf.soft_coding_keyflex_id and
36 hou.organization_id = paaf.organization_id and
37 pap.position_id (+) = paaf.position_id';
38
39
40 return l_sql;
41 end employee;
42
43 -- This function is used in the Tax Information region of the SOE
44 function tax_information(p_assignment_action_id in number)
45 return long is
46 begin
47
48 l_sql := 'select payroll.payroll_name COL01, /* Payroll Name */
49 hou1.name COL02, /* Tax Office */
50 hr_general.decode_lookup(''PL_OLDAGE_PENSION_RIGHTS'',papf.per_information4) COL03, /* Old Age/Pension Rights */
51 case to_char(trunc(trunc(months_between(:effective_date,papf.date_of_birth)/12)/16))
52 when ''0'' then hr_general.decode_lookup(''PL_DISABILITY_CATEGORY'',4)
53 else hr_general.decode_lookup(''PL_DISABILITY_CATEGORY'',nvl(pdf.dis_category,0)) end COL04, /*Disability Code */
54 hr_general.decode_lookup(decode(paye.contract_category,''CIVIL'',''PL_CIVIL_RATE_OF_TAX''
55 ,''PL_NORMAL_RATE_OF_TAX''),paye.rate_of_tax) COL05, /* Rate of Tax */
56 hr_general.decode_lookup(''PL_INCOME_REDUCTION'',paye.income_reduction) COL06, /* Income Reduction */
57 hr_general.decode_lookup(''PL_TAX_REDUCTION'',paye.tax_reduction) COL07, /* Tax Reduction */
58 hr_general.decode_lookup(''YES_NO'',paye.tax_calc_with_spouse_child) COL08, /* Tax Calculation with Spouse/Child */
59 sii.emp_social_security_info COL09 /* SII Code */
60 from pay_all_payrolls_f payroll,
61 hr_organization_units hou1,
62 per_all_people_f papf,
63 per_all_assignments_f paaf,
64 (select max(CATEGORY) dis_category
65 from per_disabilities_f
66 where person_id =
67 (select person_id from per_all_assignments_f where assignment_id = :ASSIGNMENT_ID and rownum = 1)
68 and :effective_date between effective_start_date and effective_end_date )pdf,
69 pay_pl_sii_details_f sii,
70 per_assignment_status_types past,
71 pay_pl_paye_details_f paye,
72 hr_soft_coding_keyflex keyflex
73 where payroll.payroll_id = :payroll_id and
74 paaf.assignment_id = :assignment_id and
75 paaf.person_id = papf.person_id and
76 :effective_date between papf.effective_start_date and papf.effective_end_date and
77 :effective_date between paaf.effective_start_date and paaf.effective_end_date and
78 papf.per_information6 = hou1.organization_id and
79 keyflex.soft_coding_keyflex_id = paaf.soft_coding_keyflex_id and
80 decode(keyflex.segment3,
81 ''CIVIL'',:assignment_id,
82 decode(past.per_system_status,''TERM_ASSIGN'',:assignment_id,papf.person_id)) = sii.per_or_asg_id and
83 :effective_date between sii.effective_start_date and sii.effective_end_date and
84 decode(keyflex.segment3,
85 ''CIVIL'',:assignment_id,
86 decode(past.per_system_status,''TERM_ASSIGN'',:assignment_id,papf.person_id)) = paye.per_or_asg_id and
87 :effective_date between paye.effective_start_date and paye.effective_end_date and
88 sii.business_group_id = :business_group_id and
89 paye.business_group_id = :business_group_id and
90 past.assignment_status_type_id = paaf.assignment_status_type_id';
91
92 return l_sql;
93
94 end tax_information;
95
96 /* ---------------------------------------------------------------------
97 Function : getElements
98 Usage: This is the function used in Earnings/Deductions region.
99 This function checks the value present in the profile PAY: PL Statement of Earnings Display Zero
100 and displays elements with a zero Pay Value if the profile is set to 'Yes'.
101 If the profile is set to 'No' then elements with a zero Pay Value are not displayed.
102 ------------------------------------------------------------------------ */
103 function getElements(p_assignment_action_id number
104 ,p_element_set_name varchar2) return long is
105 begin
106 --
107 --
108 if g_debug then
109 hr_utility.set_location('Entering pay_soe_glb.getElements', 10);
110 end if;
111 --
112 if fnd_profile.value('PAY_PL_SOE_ELEMENTS_DISPLAY') = 'Y' then
113
114 l_sql :=
115 'select /*+ ORDERED */ nvl(ettl.reporting_name,et.element_type_id) COL01
116 , nvl(ettl.reporting_name,ettl.element_name) COL02
117 , to_char(sum(FND_NUMBER.CANONICAL_TO_NUMBER(rrv.result_value)),fnd_currency.get_format_mask(:G_CURRENCY_CODE,40)) COL16
118 , decode(count(*),1,''1'',''2'') COL17 -- destination indicator
119 , decode(count(*),1,max(rr.run_result_id),max(et.element_type_id)) COL18
120 from pay_assignment_actions aa
121 , pay_run_results rr
122 , pay_run_result_values rrv
123 , pay_input_values_f iv
124 , pay_input_values_f_tl ivtl
125 , pay_element_types_f et
126 , pay_element_types_f_tl ettl
127 , pay_element_set_members esm
128 , pay_element_sets es
129 where aa.assignment_action_id :action_clause
130 and aa.assignment_action_id = rr.assignment_action_id
131 and rr.status in (''P'',''PA'')
132 and rr.run_result_id = rrv.run_result_id
133 and rr.element_type_id = et.element_type_id
134 and :effective_date between
135 et.effective_start_date and et.effective_end_date
136 and et.element_type_id = ettl.element_type_id
137 and rrv.input_value_id = iv.input_value_id
138 and iv.name = ''Pay Value''
139 and :effective_date between
140 iv.effective_start_date and iv.effective_end_date
141 and iv.input_value_id = ivtl.input_value_id
142 and ettl.language = userenv(''LANG'')
143 and ivtl.language = userenv(''LANG'')
144 and et.element_type_id = esm.element_type_id
145 and esm.element_set_id = es.element_set_id
146 and ( es.BUSINESS_GROUP_ID IS NULL
147 OR es.BUSINESS_GROUP_ID = :business_group_id )
148 AND ( es.LEGISLATION_CODE IS NULL
149 OR es.LEGISLATION_CODE = '':legislation_code'' )
150 and es.element_set_name = '''|| p_element_set_name ||'''
151 group by nvl(ettl.reporting_name,ettl.element_name)
152 , ettl.reporting_name
153 ,nvl(ettl.reporting_name,et.element_type_id)
154 order by nvl(ettl.reporting_name,ettl.element_name)';
155 --
156 elsif fnd_profile.value('PAY_PL_SOE_ELEMENTS_DISPLAY') = 'N' then
157
158 l_sql :=
159 'select /*+ ORDERED */ nvl(ettl.reporting_name,et.element_type_id) COL01
160 , nvl(ettl.reporting_name,ettl.element_name) COL02
161 , to_char(sum(FND_NUMBER.CANONICAL_TO_NUMBER(rrv.result_value)),fnd_currency.get_format_mask(:G_CURRENCY_CODE,40)) COL16
162 , decode(count(*),1,''1'',''2'') COL17 -- destination indicator
163 , decode(count(*),1,max(rr.run_result_id),max(et.element_type_id)) COL18
164 from pay_assignment_actions aa
165 , pay_run_results rr
166 , pay_run_result_values rrv
167 , pay_input_values_f iv
168 , pay_input_values_f_tl ivtl
169 , pay_element_types_f et
170 , pay_element_types_f_tl ettl
171 , pay_element_set_members esm
172 , pay_element_sets es
173 where aa.assignment_action_id :action_clause
174 and aa.assignment_action_id = rr.assignment_action_id
175 and rr.status in (''P'',''PA'')
176 and rr.run_result_id = rrv.run_result_id
177 and rr.element_type_id = et.element_type_id
178 and :effective_date between
179 et.effective_start_date and et.effective_end_date
180 and et.element_type_id = ettl.element_type_id
181 and rrv.input_value_id = iv.input_value_id
182 and iv.name = ''Pay Value''
183 and :effective_date between
184 iv.effective_start_date and iv.effective_end_date
185 and iv.input_value_id = ivtl.input_value_id
186 and ettl.language = userenv(''LANG'')
187 and ivtl.language = userenv(''LANG'')
188 and et.element_type_id = esm.element_type_id
189 and esm.element_set_id = es.element_set_id
190 and ( es.BUSINESS_GROUP_ID IS NULL
191 OR es.BUSINESS_GROUP_ID = :business_group_id )
192 AND ( es.LEGISLATION_CODE IS NULL
193 OR es.LEGISLATION_CODE = '':legislation_code'' )
194 and es.element_set_name = '''|| p_element_set_name ||'''
195 and rrv.result_value <> ''0''
196 group by nvl(ettl.reporting_name,ettl.element_name)
197 , ettl.reporting_name
198 ,nvl(ettl.reporting_name,et.element_type_id)
199 order by nvl(ettl.reporting_name,ettl.element_name)';
200 --
201
202
203 end if;
204 --
205 if g_debug then
206 hr_utility.set_location('Leaving pay_soe_glb.getElements', 20);
207 end if;
208 --
209 return l_sql;
210 --
211 end getElements;
212 --
213
214
215 /* ---------------------------------------------------------------------
216 Function : Elements1
217 Usage: This function is called from the Earnings region (Region5 of SOE)
218 ------------------------------------------------------------------------ */
219 function Elements1(p_assignment_action_id number) return long is
220 begin
221 hr_utility.trace('Entering elements1');
222 return getElements(p_assignment_action_id
223 ,pay_soe_util.getConfig('ELEMENTS1'));
224 hr_utility.trace('Leaving Elements1');
225 end Elements1;
226
227 /* ---------------------------------------------------------------------
228 Function : SetParameters
229 Usage: This function is called from the Deductions region (Region6 of SOE)
230 ------------------------------------------------------------------------ */
231 function Elements2(p_assignment_action_id number) return long is
232 begin
233 return getElements(p_assignment_action_id
234 ,pay_soe_util.getConfig('ELEMENTS2'));
235 end Elements2;
236 --
237 end pay_pl_soe;
[Home] [Help]