1 PACKAGE pay_us_employee_payslip_web
2 /* $Header: pyusempw.pkh 120.12 2010/11/16 22:39:34 rnestor noship $ */
3 /*
4 ******************************************************************
5 * *
6 * Copyright (C) 1993 Oracle Corporation. *
7 * All rights reserved. *
8 * *
9 * This material has been provided pursuant to an agreement *
10 * containing restrictions on its use. The material AUTHID CURRENT_USER is also *
11 * protected by copyright law. No part of this material may *
12 * be copied or distributed, transmitted or transcribed, in *
13 * any form or by any means, electronic, mechanical, magnetic, *
14 * manual, or otherwise, or disclosed to third parties without *
15 * the express written permission of Oracle Corporation, *
16 * 500 Oracle Parkway, Redwood City, CA, 94065. *
17 * *
18 ******************************************************************
19
20 Name : pay_us_employee_payslip_web
21
22
23 Description : Package contains functions and procedures used
24 by te Online Payslip Views.
25
26 Uses :
27
28 Change List
29 -----------
30 Date Name Vers Bug No Description
31 ---- ---- ---- ------ -----------
32 24-Jun-2010 mikarthi 115.26 9745300 Added function get_display_list for Arabic Hijrah
33 calendar support in Choose Payslip list
34 22-Jun-2010 vaisriva 115.25 9804074 Updated query for the cursor get_jp_choose_payslip
35 for Payslip Performance Improvement
39 to pay_perf_payslip_action_info_v
36 15-Jun-2010 vaisriva 115.24 9804074 Added code specific to the JP legislation for
37 Payslip Performance Improvement
38 21-Feb-2010 mikarthi 115.23 9555144 changed the view in cursor get_choose_payslip
40 21-Feb-2010 mikarthi 115.22 9394861 New cursor check_emp_personal_payment
41 21-Feb-2010 mikarthi 115.21 9394861 NOCOPY hint added
42 21-Feb-2010 mikarthi 115.20 9394861 Payslip Perf Enhancement
43 a) New function check_us_emp_personal_payment
44 b) New Function check_emp_personal_payment
45 05-MAR-2008 sudedas 115.17 6739242 New Function get_netpaydistr_segment
46 Added.
47 08-SEP-2005 ppanda 115.16 For Enhanced multi jurisdiction taxation
48 a new function added to derive the
49 full jurisdiction name (State, County, City)
50 using the existing function get_jurisdiction_nmame
51 05-MAY-2005 ahanda 115.15 4246280 Changed Payslip code to check for
52 View Payslip offset before showing
53 Payslip for an employee.
54 Added overloaded function -
55 check_emp_personal_payment with
56 parameter of p_time_period_id
57 19-JAN-2005 sodhingr 115.14 4132132 Changed the function get_term_info
58 05-NOV-2003 pganguly 115.13 Added two pkg variables.
59 19-JUL-2003 ahanda 115.12 Added function format_to_date.
60 14-NOV-2002 tclewis 115.11 Changed order of parameters in
61 get_check_number now AA_ID, PP_ID.
62 27-SEP-2002 sodhingr 115.9 Added check for GSCC compliance.
63 16-JUN-2002 sodhingr 115.8 Added a new function get_term_info
64 to check
65 the terminated employee based
66 on the legislation_field_info
67 13-MAY-2002 pganguly 115.7 2363857 Added a new function
68 get_legislation_code.
69 21-MAR-2002 ekim 115.6 Changed get_doc_eit function.
70 24-JAN-2002 dgarg 115.4 Added get_jurisdiction_name
71 function
72 05-OCT-2001 ekim 115.3 Added get_doc_eit function.
73 21-SEP-2001 ekim 115.2 Added get_format_value function.
74 17-SEP-2001 asasthan 115.1 Added get_check_number for payslip
75 08-FEB-2000 ahanda 115.0 Removed all reference to PRAGMA
76
77 ***********************************************************************
78 ** Removed All reference to PRAGMA for 11i.
79 ***********************************************************************
80
81 01-FEB-2000 ahanda 110.2 Removed element_name parameter from
82 School dsts function.
83 01-FEB-2000 ahanda 110.1 Added function to get School Dst Name.
84 01-JUL-1999 ahanda 110.0 Created.
85
86 *******************************************************************/
87 AS
88
89 -- Global Variable for the Package
90 --
91 g_currency_code varchar2(10) := null;
92 g_legislation_code varchar2(2) := null;
93 g_legislation_rule varchar2(30) := null;
94
95 TYPE eit_rec is RECORD
96 ( t_level varchar2(150),
97 t_id number,
98 t_online varchar2(1),
99 t_print varchar2(1));
100
101 TYPE eit_table is TABLE OF eit_rec INDEX BY BINARY_INTEGER;
102
103 eit_tab eit_table;
104
105 FUNCTION get_doc_eit (p_doc_type in varchar,
106 p_mode in varchar,
107 p_level in varchar,
108 p_id in number,
109 p_effective_date date)
110
111 RETURN varchar2;
112
113
114 FUNCTION get_format_value (p_business_group_id in number,
115 p_value in number)
116 return varchar2;
117
118
119 FUNCTION check_emp_personal_payment
120 (p_assignment_id number,
121 p_payroll_id number,
122 p_time_period_id number,
123 p_assignment_action_id number,
124 p_effective_date date)
125 return varchar2;
126
127 -- Added for Testing
128 --
129 FUNCTION check_emp_personal_payment
130 (p_assignment_id number,
131 p_payroll_id number,
132 p_time_period_id number,
133 p_assignment_action_id number,
134 p_effective_date date,
135 p_payment_category varchar2,
136 p_legislation_code varchar2)
137 return varchar2;
138
139
140 FUNCTION check_emp_personal_payment
141 (p_assignment_id number,
142 p_payroll_id number,
143 p_assignment_action_id number,
144 p_effective_date date)
145 return varchar2;
146
147
148 FUNCTION get_proposed_emp_salary (p_assignment_id in number,
149 p_pay_basis_id in number,
150 p_pay_bases_name in varchar2,
151 p_period_start_date in date,
152 p_period_end_date in date)
153 return varchar2;
154
155
156 FUNCTION get_emp_annualization_factor
157 (p_pay_basis_id in number,
158 p_period_type in varchar2,
159 p_pay_bases_name in varchar2,
160 p_assignment_id in number,
161 p_period_end_date in date)
162 return number;
163
164
165 FUNCTION get_asgn_annual_hours (p_assignment_id in number,
166 p_period_end_date in date)
167 return number;
168
169
170 FUNCTION get_school_dsts_name (p_jurisdiction_code in varchar2)
171 return varchar2;
172
173 FUNCTION get_check_number(p_pre_payment_assact in number
174 ,p_pre_payment_id in number)
175 return varchar2;
176
177
178 FUNCTION get_jurisdiction_name( p_jurisdiction_code in varchar2)
179 return varchar2;
180
181 FUNCTION get_legislation_code( p_business_group_id in number)
182 return varchar2;
183
184 FUNCTION get_term_info (p_business_group_id in number,
185 p_person_id in number,
186 p_action_context_id number)
187 /* for bug 4132132
188 p_effective_start_date date,
189 p_effective_end_date date) */
190 RETURN varchar2;
191
192 FUNCTION get_meaning_payslip_label(p_leg_code in VARCHAR2,
193 p_lookup_code in VARCHAR2)
194 RETURN VARCHAR2;
195
196 FUNCTION format_to_date(p_char_date in varchar2)
197 RETURN date;
198
199 FUNCTION get_full_jurisdiction_name( p_jurisdiction_code in varchar2)
200 return varchar2;
201
202 -- This Function has been Added for Bug# 6739242
203 -- For CA OT Enhancement pay_action_information started getting
204 -- Populated with Account Details that is NOT needed to be displayed
205 -- In Self Service Payslip
206 FUNCTION get_netpaydistr_segment(p_business_grp_id IN NUMBER
207 ,p_org_pay_meth_id IN NUMBER)
208 RETURN VARCHAR2;
209
210
211 /* Start of changes for 9394861
212 */
213 g_job_label VARCHAR2(50):= '';
214 g_check_label VARCHAR2(50):= '';
215
216 FUNCTION check_us_emp_personal_payment( p_assignment_id NUMBER
217 , p_payroll_id NUMBER
218 , p_time_period_id NUMBER
219 , p_assignment_action_id NUMBER
220 , p_effective_date DATE
221 )
222 RETURN VARCHAR2;
223
224
225 PROCEDURE check_emp_personal_payment( p_effective_date VARCHAR2,
226 p_enable_term VARCHAR2,
227 p_business_group_id VARCHAR2,
228 p_person_id VARCHAR2,
229 p_first_call VARCHAR2 default 'N',
230 p_last_fetch OUT NOCOPY VARCHAR2,
231 pay_ret_table OUT NOCOPY pay_payslip_list_table);
232
233 cursor get_choose_payslip(v_person_id VARCHAR2, v_eff_date VARCHAR2) IS
234 SELECT
235 to_char(action_context_id) action_context_id,
236 trunc(effective_date) effective_date,
237 payroll_id,
238 time_period_id,
239 assignment_id,
240 action_information14,
241 check_count
242 FROM
243 pay_perf_payslip_action_info_v
244 WHERE person_id = to_number(v_person_id)
245 AND effective_date >= to_date(v_eff_date,'YYYY/MM/DD')
246 order by effective_date desc;
247
248 /* End of changes for 9394861
249 */
250
251 FUNCTION get_display_list (p_date DATE,
252 p_legislation_code VARCHAR2,
253 p_legislation_code_1 VARCHAR2,
254 p_action_information14 VARCHAR2,
255 p_check_count NUMBER)
256 return VARCHAR2;
257
258 -- Bug 9804074: Added new cursor for JP Legislation to fetch the values for
259 -- the field 'Choose a Payslip'
260 CURSOR get_jp_choose_payslip(v_person_id VARCHAR2, v_eff_date VARCHAR2) IS
261 SELECT
262 to_char(pai.action_context_id) action_context_id,
263 trunc(pai.effective_date) effective_date,
264 paf.payroll_id,
265 paf.assignment_id,
266 pai.action_information2
267 FROM
268 pay_action_information pai,
269 per_all_assignments_f paf
270 WHERE paf.person_id = to_number(v_person_id)
271 AND paf.assignment_id = pai.assignment_id
272 AND pai.action_information_category = 'EMPLOYEE PAYMENT INFORMATION'
273 AND pai.effective_date BETWEEN paf.effective_start_date AND paf.effective_end_date
274 AND pai.effective_date >= to_date(v_eff_date,'YYYY/MM/DD')
275 AND EXISTS (
276 SELECT
277 'x'
278 FROM
279 pay_assignment_actions paa2
280 WHERE nvl(paa2.tax_unit_id,-9999) = nvl(pai.tax_unit_id,-9999)
281 AND paa2.assignment_action_id = pai.action_context_id
282 AND paa2.assignment_id = pai.assignment_id )
283 ORDER BY pai.effective_date desc,
284 pai.action_context_id;
285 --
286 end pay_us_employee_payslip_web;
[Home] [Help]