1 PACKAGE pqp_gb_mileage_claim_pkg AUTHID CURRENT_USER AS
2 /* $Header: pqgbmgcm.pkh 120.0.12010000.2 2009/10/14 11:26:24 nchinnam ship $ */
3
4
5 TYPE r_purpose_det is Record ( category VARCHAR2(60)
6 ,value VARCHAr2(80)
7 );
8 TYPE t_purpose_det is Table of r_purpose_det
9 INDEX BY binary_integer;
10
11
12
13 PROCEDURE get_purpose_details (p_business_group_id IN NUMBER
14 ,p_assignment_id IN NUMBER
15 ,p_effective_date IN DATE
16 ,p_purpose IN VARCHAR2
17 ,p_ownership IN VARCHAR2
18 ,p_vehicle_type IN VARCHAR2
19 ,p_rate_type IN VARCHAR2
20 ,p_element_id OUT NOCOPY NUMBER
21 ,p_element_name OUT NOCOPY VARCHAR2
22 ,p_rate_table_id OUT NOCOPY VARCHAR2
23 ,p_rate_table OUT NOCOPY VARCHAR2
24 ,p_taxable OUT NOCOPY VARCHAR2
25 );
26
27
28 PROCEDURE insert_mileage_claim
29 ( p_effective_date IN DATE,
30 p_web_adi_identifier IN VARCHAR2 ,
31 p_info_id IN VARCHAR2 ,
32 p_time_stamp IN VARCHAR2 ,
33 p_assignment_id IN NUMBER,
34 p_business_group_id IN NUMBER,
35 p_ownership IN VARCHAR2 ,
36 p_usage_type IN VARCHAR2 ,
37 p_vehicle_type IN VARCHAR2,
38 p_start_date IN VARCHAR2 ,
39 p_end_date IN VARCHAR2 ,
40 p_claimed_mileage IN VARCHAR2 ,
41 p_actual_mileage IN VARCHAR2 default null,
42 p_registration_number IN VARCHAR2 default null,
43 p_engine_capacity IN VARCHAR2 default null,
44 p_fuel_type IN VARCHAR2 default null,
45 p_calculation_method IN VARCHAR2 default null,
46 p_user_rates_table IN VARCHAR2 default null,
47 p_fiscal_ratings IN VARCHAR2 default null,
48 p_PAYE_taxable IN VARCHAR2 default null,
49 p_no_of_passengers IN VARCHAR2 default null,
50 p_purpose IN VARCHAR2 default null,
51 p_data_source IN VARCHAR2 default 'I',
52 p_user_type IN VARCHAR2 default 'PUI',
53 p_mileage_claim_element IN OUT NOCOPY NUMBER ,
54 p_element_entry_id IN OUT NOCOPY NUMBER ,
55 p_element_entry_date IN OUT NOCOPY DATE
56 );
57
58
59 PROCEDURE update_mileage_claim
60 ( p_effective_date IN DATE,
61 p_assignment_id IN number,
62 p_business_group_id IN NUMBER,
63 p_ownership IN VARCHAR2 DEFAULT NULL,
64 p_usage_type IN VARCHAR2 DEFAULT NULL,
65 p_vehicle_type IN VARCHAR2 DEFAULT NULL,
66 p_start_date_o IN VARCHAR2 DEFAULT NULL,
67 p_start_date IN VARCHAR2 DEFAULT NULL,
68 p_end_date_o IN VARCHAR2 DEFAULT NULL,
69 p_end_date IN VARCHAR2 DEFAULT NULL,
70 p_claimed_mileage_o IN VARCHAR2 DEFAULT NULL,
71 p_claimed_mileage IN VARCHAR2 DEFAULT NULL,
72 p_actual_mileage_o IN VARCHAR2 DEFAULT NULL,
73 p_actual_mileage IN VARCHAR2 DEFAULT NULL,
74 p_registration_number IN VARCHAR2 DEFAULT NULL,
75 p_engine_capacity IN VARCHAR2 DEFAULT NULL,
76 p_fuel_type IN VARCHAR2 DEFAULT NULL,
77 p_calculation_method IN VARCHAR2 DEFAULT NULL,
78 p_user_rates_table IN VARCHAR2 DEFAULT NULL,
79 p_fiscal_ratings_o IN VARCHAR2 DEFAULT NULL,
80 p_fiscal_ratings IN VARCHAR2 DEFAULT NULL,
81 p_PAYE_taxable IN VARCHAR2 DEFAULT NULL,
82 p_no_of_passengers_o IN VARCHAR2 DEFAULT NULL,
83 p_no_of_passengers IN VARCHAR2 DEFAULT NULL,
84 p_purpose IN VARCHAR2 DEFAULT NULL ,
85 p_data_source IN VARCHAR2 DEFAULT 'I',
86 p_mileage_claim_element IN OUT NOCOPY NUMBER ,
87 p_element_entry_id IN OUT NOCOPY NUMBER ,
88 p_element_entry_date IN OUT NOCOPY DATE
89 );
90
91 PROCEDURE delete_mileage_claim
92 ( p_effective_date IN DATE,
93 p_assignment_id IN NUMBER,
94 p_mileage_claim_element IN NUMBER ,
95 p_element_entry_id IN NUMBER ,
96 p_element_entry_date IN DATE
97 );
98
99 --get element or rates from configuration
100 PROCEDURE get_config_info ( p_business_group_id IN NUMBER
101 ,p_ownership IN VARCHAR2
102 ,p_usage_type IN VARCHAR2
103 ,p_vehicle_type IN VARCHAR2
104 ,p_fuel_type IN VARCHAR2
105 ,p_sl_rates_type IN VARCHAR2
106 ,p_rates OUT NOCOPY NUMBER
107 ,p_element_id IN OUT NOCOPY NUMBER
108 );
109
110 --Check for Mandatory columns
111 FUNCTION chk_mndtry_fields ( p_effective_date IN DATE
112 ,p_assignment_id IN NUMBER
113 ,p_business_group_id IN NUMBER
114 ,p_ownership IN VARCHAR2
115 ,p_usage_type IN VARCHAR2
116 ,p_vehicle_type IN VARCHAR2
117 ,p_start_date IN VARCHAR2
118 ,p_end_date IN VARCHAR2
119 ,p_claimed_mileage IN VARCHAR2
120 ,p_actual_mileage IN VARCHAR2
121 ,p_registration_number IN VARCHAR2
122 ,p_engine_capacity IN VARCHAR2
123 ,p_fuel_type IN VARCHAR2
124 ,p_element_type_id IN NUMBER
125 ,p_data_source IN VARCHAR2
126 ,p_message OUT NOCOPY VARCHAR2
127 )
128
129 RETURN VARCHAR2;
130
131
132 --Check for eligibility
133
134 FUNCTION chk_eligibility ( p_effective_date IN DATE
135 ,p_assignment_id IN NUMBER
136 ,p_business_group_id IN NUMBER
137 ,p_ownership IN VARCHAR2
138 ,p_usage_type IN VARCHAR2
139 ,p_vehicle_type IN VARCHAR2
140 ,p_start_date IN VARCHAR2
141 ,p_end_date IN VARCHAR2
142 ,p_claimed_mileage IN VARCHAR2
143 ,p_actual_mileage IN VARCHAR2 default null
144 ,p_registration_number IN VARCHAR2 default null
145 ,p_data_source IN VARCHAR2 default 'I'
146 ,p_message OUT NOCOPY VARCHAR2
147 )
148 RETURN VARCHAR2;
149
150 ---Called from JDEV
151 --This is used to delete the cliam import recodes for infoID
152 --Procedure returns 'S' means sucessfully deleted
153 PROCEDURE delete_claim_import
154 ( p_info_id IN VARCHAR2
155 ,p_assignment_id IN NUMBER
156 ,p_business_group_id IN NUMBER
157 ,p_effective_date IN DATE
158 ,p_return_status OUT NOCOPY VARCHAR2
159 ) ;
160 -- Function get_code returns the code of the meaning passed
161 --
162 -- The Code depends on the value of the p_option parameter
163 -- p_option = 'R' -> p_field has the rates table name and
164 --it Returns the Rates table id
165 --
166 FUNCTION get_code
167 (p_option IN VARCHAR2
168 ,p_field IN VARCHAR2
169 ) RETURN VARCHAR2;
170
171 --
172 -- Function get_meaning returns the meaning string of the id passed
173 --
174 -- The Meaning depends on the value of the p_option parameter
175 -- p_option = 'R' -> p_field_id has the rates table id and it Returns the Rates table Name
176 -- p_option = 'E' -> p_field_id has the element type id and it Returns the Element Name
177 --
178 FUNCTION get_meaning
179 (p_option IN VARCHAR2
180 ,p_field_id IN NUMBER
181 ) RETURN VARCHAR2;
182
183
184 ---------
185 ---Get lookup meaning
186 FUNCTION get_lkp_meaning (p_lookup_code IN VARCHAR2,
187 p_lookup_type IN VARCHAR2
188 )
189 RETURN VARCHAR2;
190
191 --chk if vehicle is active during the claim period
192
193 FUNCTION chk_vehicle_active ( p_ownership IN VARCHAR2
194 ,p_usage_type IN VARCHAR2
195 ,p_assignment_id IN NUMBER
196 ,p_business_group_id IN NUMBER
197 ,p_start_date IN VARCHAR2
198 ,p_end_date IN VARCHAR2
199 ,p_registration_number IN VARCHAR2
200 ,p_message OUT NOCOPY VARCHAR2
201 )
202 RETURN NUMBER;
203
204 --Function used in query
205 FUNCTION get_meaning ( p_inp_type VARCHAR2
206 ,p_code VARCHAR2
207 )
208 RETURN VARCHAR2;
209
210 --Temporary function
211 FUNCTION get_total ( p_element_name IN VARCHAR2
212 ,p_assignment_action_id IN NUMBER
213 ,p_element_entry_id IN NUMBER
214 ,p_business_group_id IN NUMBER
215 )
216 RETURN NUMBER;
217
218 --Function to get balance for a view
219 FUNCTION get_amount ( p_element_name IN VARCHAR2
220 ,p_element_type_id IN NUMBER
221 ,p_effective_date IN DATE
222 ,p_assignment_id IN NUMBER
223 )
224 RETURN NUMBER;
225
226 ---Usage type proc
227
228 PROCEDURE insert_company_mileage_claim
229 ( p_effective_date IN DATE,
230 p_assignment_id IN NUMBER,
231 p_business_group_id IN NUMBER,
232 p_ownership IN VARCHAR2 ,
233 p_usage_type IN VARCHAR2 ,
234 p_vehicle_type IN VARCHAR2,
235 p_start_date IN VARCHAR2 ,
236 p_end_date IN VARCHAR2 ,
237 p_claimed_mileage IN VARCHAR2 ,
238 p_actual_mileage IN VARCHAR2 default null,
239 p_registration_number IN VARCHAR2 default null,
240 p_engine_capacity IN VARCHAR2 default null,
241 p_fuel_type IN VARCHAR2 default null,
242 p_calculation_method IN VARCHAR2 default null,
243 p_user_rates_table IN VARCHAR2 default null,
244 p_fiscal_ratings IN VARCHAR2 default null,
245 p_PAYE_taxable IN VARCHAR2 default null,
246 p_no_of_passengers IN VARCHAR2 default null,
247 p_purpose IN VARCHAR2 default null,
248 p_payroll_id IN NUMBER,
249 p_mileage_claim_element IN OUT NOCOPY NUMBER ,
250 p_element_entry_id IN OUT NOCOPY NUMBER ,
251 p_element_entry_date IN OUT NOCOPY DATE,
252 p_element_link_id IN NUMBER
253 );
254
255 PROCEDURE insert_private_mileage_claim
256 ( p_effective_date IN DATE,
257 p_assignment_id IN NUMBER,
258 p_business_group_id IN NUMBER,
259 p_ownership IN VARCHAR2 ,
260 p_usage_type IN VARCHAR2 ,
261 p_vehicle_type IN VARCHAR2,
262 p_start_date IN VARCHAR2 ,
263 p_end_date IN VARCHAR2 ,
264 p_claimed_mileage IN VARCHAR2 ,
265 p_actual_mileage IN VARCHAR2 default null,
266 p_registration_number IN VARCHAR2 default null,
267 p_engine_capacity IN VARCHAR2 default null,
268 p_fuel_type IN VARCHAR2 default null,
269 p_calculation_method IN VARCHAR2 default null,
270 p_user_rates_table IN VARCHAR2 default null,
271 p_fiscal_ratings IN VARCHAR2 default null,
272 p_PAYE_taxable IN VARCHAR2 default null,
273 p_no_of_passengers IN VARCHAR2 default null,
274 p_purpose IN VARCHAR2 default null,
275 p_payroll_id IN NUMBER,
276 p_mileage_claim_element IN OUT NOCOPY NUMBER ,
277 p_element_entry_id IN OUT NOCOPY NUMBER ,
278 p_element_entry_date IN OUT NOCOPY DATE,
279 p_element_link_id IN NUMBER
280 );
281
282
283 END;
[Home] [Help]