1 PACKAGE pqp_car_mileage_functions AS
2 --REM $Header: pqgbcmfn.pkh 120.2.12010000.1 2008/07/28 11:10:57 appldev ship $
3
4
5 TYPE r_ses_date is RECORD (asg_id per_assignments_f.assignment_id%TYPE,
6 start_index number(3),start_date date,
7 end_index number(3),end_date date);
8 TYPE t_ses_date IS TABLE OF r_ses_date INDEX BY BINARY_INTEGER;
9 l_ses_date t_ses_date;
10
11 ses_start number;
12 ses_end number;
13
14 --Function get_legislation_code
15 ----------------------------------------------------------------------------
16
17 FUNCTION get_legislation_code (p_business_group_id IN NUMBER)
18 RETURN VARCHAR2;
19 -----------------------------------------------------------------------------
20 -- PQP_GET_RANGE
21 -----------------------------------------------------------------------------
22 FUNCTION pqp_get_range( p_assignment_id IN NUMBER
23 ,p_business_group_id IN NUMBER
24 ,p_payroll_action_id IN NUMBER
25 ,p_table_name IN VARCHAR2
26 ,p_row_or_column IN VARCHAR2
27 ,p_value IN NUMBER
28 ,p_claim_date IN DATE
29 ,p_low_value OUT NOCOPY NUMBER
30 ,p_high_value OUT NOCOPY NUMBER)
31 RETURN NUMBER;
32
33 ----------------------------------------------------------------------------
34 --FUNCTION get_config_info
35 ---------------------------------------------------------------------------
36 FUNCTION get_config_info (p_business_group_id IN NUMBER
37 ,p_info_type IN VARCHAR2
38 )
39 RETURN VARCHAR2;
40
41 -----------------------------------------------------------------------------
42 -- PQP_GET_ATTR_VAL
43 -----------------------------------------------------------------------------
44 FUNCTION pqp_get_attr_val( p_assignment_id IN NUMBER
45 ,p_business_group_id IN NUMBER
46 ,p_payroll_action_id IN NUMBER
47 ,p_car_type IN VARCHAR2
48 ,p_cc OUT NOCOPY NUMBER
49 ,p_rates_table OUT NOCOPY VARCHAR2
50 ,p_calc_method OUT NOCOPY VARCHAR2
51 ,p_error_msg OUT NOCOPY VARCHAR2
52 ,p_claim_date IN DATE
53 ,p_fuel_type OUT NOCOPY VARCHAR2
54 ,p_veh_reg IN VARCHAR2 DEFAULT NULL)
55 RETURN NUMBER;
56
57 -----------------------------------------------------------------------------
58 -- PQP_GET_PERIOD
59 -----------------------------------------------------------------------------
60 FUNCTION pqp_get_period( p_assignment_id IN NUMBER
61 ,p_business_group_id IN NUMBER
62 ,p_payroll_id IN NUMBER
63 ,p_payroll_action_id IN NUMBER
64 ,p_claim_date IN DATE
65 ,p_period_num OUT NOCOPY NUMBER)
66 RETURN NUMBER;
67
68 -----------------------------------------------------------------------------
69 -- PQP_GET_VEH_CC
70 -----------------------------------------------------------------------------
71 FUNCTION pqp_get_veh_cc( p_assignment_id IN NUMBER
72 ,p_business_group_id IN NUMBER
73 ,p_reg_num IN VARCHAR2)
74 RETURN NUMBER;
75
76 -----------------------------------------------------------------------------
77 -- PQP_GET_YEAR
78 -----------------------------------------------------------------------------
79 FUNCTION pqp_get_year( p_assignment_id IN NUMBER
80 ,p_business_group_id IN NUMBER
81 ,p_payroll_action_id IN NUMBER
82 ,p_claim_date IN DATE)
83 RETURN VARCHAR2;
84
85 -----------------------------------------------------------------------------
86 -- PQP_MULTIPLE_ASG
87 -----------------------------------------------------------------------------
88 FUNCTION pqp_multiple_asg( p_assignment_id IN NUMBER
89 ,p_business_group_id IN NUMBER
90 ,p_payroll_action_id IN NUMBER)
91 RETURN VARCHAR2;
92
93 -----------------------------------------------------------------------------
94 -- PQP_GET_TABLE_VALUE
95 -----------------------------------------------------------------------------
96 FUNCTION pqp_get_table_value ( p_bus_group_id IN NUMBER
97 ,p_payroll_action_id IN NUMBER
98 ,p_table_name IN VARCHAR2
99 ,p_col_name IN VARCHAR2
100 ,p_row_value IN VARCHAR2
101 ,p_effective_date IN DATE DEFAULT NULL
102 ,p_error_msg OUT NOCOPY VARCHAR2)
103 RETURN VARCHAR2;
104
105 -----------------------------------------------------------------------------
106 -- PQP_CHECK_RATES_TABLE
107 -----------------------------------------------------------------------------
108 FUNCTION pqp_check_rates_table(p_business_group_id IN NUMBER
109 ,p_table_name IN VARCHAR2)
110 RETURN VARCHAR2;
111
112 -----------------------------------------------------------------------------
113 -- PQP_VALIDATE_DATE
114 -----------------------------------------------------------------------------
115 FUNCTION pqp_validate_date(p_date_earned IN DATE
116 ,p_claim_end_date IN DATE)
117 RETURN VARCHAR2;
118
119 ------------------------------------------------------------------------
120
121 --Function Max_Limit_Calc
122 ------------------------------------------------------------------------
123 FUNCTION Max_limit_calc ( p_assignment_id IN NUMBER
124 ,p_bg_id IN NUMBER
125 ,p_payroll_action_id IN NUMBER
126 ,p_prorated_mileage IN NUMBER
127 ,p_cc IN NUMBER
128 ,p_claim_date IN date
129 ,p_total_period IN NUMBER
130 ,p_cl_period IN NUMBER
131 ,p_rates_table IN VARCHAR2
132 )
133 RETURN VARCHAR2;
134
135
136
137
138 --------------------------------------------------------------------------
139 --Function PQP_PRORATE_CALC returns prorated Amt
140 --------------------------------------------------------------------------
141 FUNCTION pqp_prorate_calc( p_assignment_id IN NUMBER
142 ,p_bg_id IN NUMBER
143 ,p_payroll_action_id IN NUMBER
144 ,p_prorated_mileage IN NUMBER
145 ,p_cc IN NUMBER
146 ,p_claim_date IN DATE
147 ,p_total_period IN NUMBER
148 ,p_cl_period IN NUMBER
149 ,p_rates_table IN VARCHAR2
150 ,p_lower_pro_mileage IN NUMBER
151 ,p_end_date IN OUT NOCOPY VARCHAR2)
152 RETURN NUMBER;
153
154
155 --------------------------------------------------------------------------
156 --Procedure PQP_GET_TAXNI_RATES Calculates Taxable and NIC liabilities
157 --------------------------------------------------------------------------
158
159 FUNCTION pqp_get_taxni_rates ( p_assignment_id IN NUMBER
160 ,p_business_group_id IN NUMBER
161 ,p_payroll_action_id IN NUMBER
162 ,p_itd_ac_miles IN NUMBER
163 ,p_actual_mileage IN NUMBER
164 ,p_total_actual_mileage IN NUMBER
165 ,p_ele_iram_itd IN NUMBER
166 ,p_cc IN NUMBER
167 ,p_claim_end_date IN DATE
168 ,p_two_wheeler_type IN VARCHAR2
169 ,p_wheeler_type IN VARCHAR2
170 ,p_table_name IN VARCHAR2
171 ,p_ele_iram_amt OUT NOCOPY NUMBER
172 ,p_error_mesg OUT NOCOPY VARCHAR2)
173
174 RETURN NUMBER;
175
176
177 --------------------------------------------------------------------------
178 --Procedure PQP_GET_ADDLPASG_RATE Calculates Additional Passengers Rates
179 --------------------------------------------------------------------------
180 FUNCTION PQP_GET_ADDLPASG_RATE (p_business_group_id IN NUMBER
181 ,p_payroll_action_id IN NUMBER
182 ,p_vehicle_type IN VARCHAR2
183 ,p_claimed_mileage IN NUMBER
184 ,p_itd_miles IN NUMBER
185 ,p_total_passengers IN NUMBER
186 ,p_total_pasg_itd_val IN NUMBER
187 ,p_cc IN NUMBER
188 ,p_rates_table IN VARCHAR2
189 ,p_claim_end_date IN DATE
190 ,p_tax_free_amt OUT NOCOPY NUMBER
191 ,p_ni_amt OUT NOCOPY NUMBER
192 ,p_tax_amt OUT NOCOPY NUMBER
193 ,p_err_msg OUT NOCOPY VARCHAR2)
194
195 RETURN NUMBER ;
196
197 --------------------------------------------------------------------------
198 --Function PQP_GET_DATE_PAID gets date paid based on payroll action id
199 --------------------------------------------------------------------------
200 FUNCTION pqp_get_date_paid ( p_payroll_action_id IN NUMBER)
201 RETURN DATE;
202
203 ------------------------------------------------------------------------
204 --Function pqp_get_passenger_rate
205 -----------------------------------------------------------------------
206 FUNCTION pqp_get_passenger_rate ( p_business_group_id IN NUMBER
207 ,p_payroll_action_id IN NUMBER
208 ,p_vehicle_type IN VARCHAR2
209 ,p_claimed_mileage IN NUMBER
210 ,p_cl_itd_miles IN NUMBER
211 ,p_actual_mileage IN NUMBER
212 ,p_ac_itd_miles IN NUMBER
213 ,p_total_passengers IN NUMBER
214 ,p_total_pasg_itd_val IN NUMBER
215 ,p_cc IN NUMBER
216 ,p_rates_table IN VARCHAR2
217 ,p_claim_end_date IN DATE
218 ,p_tax_free_amt OUT NOCOPY NUMBER
219 ,p_ni_amt OUT NOCOPY NUMBER
220 ,p_tax_amt OUT NOCOPY NUMBER
221 ,p_err_msg OUT NOCOPY VARCHAR2)
222
223 RETURN number;
224
225 ---------------------------------------------------------------
226 --FUNCTION pqp_get_ele_endate checks if element entry is
227 --end dated.
228 ---------------------------------------------------------------
229 FUNCTION pqp_get_ele_endate ( p_assignment_id IN NUMBER
230 ,p_business_group_id IN NUMBER
231 ,p_payroll_action_id IN NUMBER
232 ,p_element_entry_id IN NUMBER
233 )
234 RETURN VARCHAR2;
235 ---------------------------------------------------------------
236 ---------------------------------------------------------------
237 --FUNCTION pqp_is_emp_term checks if employee is terminated.
238 ---------------------------------------------------------------
239
240 FUNCTION pqp_is_emp_term ( p_assignment_id IN NUMBER
241 ,p_business_group_id IN NUMBER
242 ,p_payroll_action_id IN NUMBER
243 ,p_date_earned IN DATE
244 )
245 RETURN VARCHAR2;
246 -----------------------------------------------------------------
247
248 -----------------------------------------------------------------
249 --FUNCTION get_rates_table
250 ----------------------------------------------------------------
251 FUNCTION get_rates_table (p_business_group_id IN NUMBER
252 ,p_lookup_type IN VARCHAR2
253 ,p_additional_passenger IN NUMBER
254 )
255 RETURN VARCHAR2;
256 ------------------------------------------------------------------
257 --FUNCTION get_vehicle_type
258 -----------------------------------------------------------------
259 FUNCTION get_vehicle_type (p_business_group_id IN NUMBER
260 ,p_element_type_id IN NUMBER
261 ,p_payroll_action_id IN NUMBER
262 )
263 RETURN VARCHAR2;
264
265 ----------------------------------------------------------------
266
267 ---------------------------------------------------------------
268 FUNCTION is_miles_nonreimbursed
269 ( p_assignment_id IN NUMBER
270 ,p_business_group_id IN NUMBER
271 ,p_payroll_action_id IN NUMBER
272 ,p_element_type_id IN NUMBER
273 ,p_date_earned IN DATE
274 ,p_to_date IN DATE
275 )
276 RETURN VARCHAR2;
277 END pqp_car_mileage_functions;