1 PACKAGE FTE_VALIDATION_PKG AUTHID CURRENT_USER AS
2 /* $Header: FTEVALDS.pls 120.2 2005/06/28 23:50:23 pkaliyam noship $ */
3
4
5 /***********************************************************************************/
6
7 TL_BASE_RATES CONSTANT STRINGARRAY := STRINGARRAY('ACTION','CARRIER_NAME',
8 'RATE_CHART_NAME','CURRENCY','RATE_BASIS','RATE_BASIS_UOM','DISTANCE_TYPE',
9 'SERVICE_LEVEL', 'VEHICLE_TYPE', 'RATE', 'MINIMUM_CHARGE', 'START_DATE', 'END_DATE');
10
11 SERVICE CONSTANT STRINGARRAY := STRINGARRAY(
12 'ACTION','TYPE','CARRIER_NAME',
13 'SERVICE_NUMBER','MODE_OF_TRANSPORTATION','ORIGIN_COUNTRY','ORIGIN_STATE',
14 'ORIGIN_CITY','ORIGIN_POSTAL_CODE_FROM','ORIGIN_POSTAL_CODE_TO',
15 'ORIGIN_ZONE','DESTINATION_COUNTRY','DESTINATION_STATE',
16 'DESTINATION_CITY','DESTINATION_POSTAL_CODE_FROM',
17 'DESTINATION_POSTAL_CODE_TO','DESTINATION_ZONE','COMMODITY_CLASS',
18 'SERVICE_LEVEL','COMMODITY_TYPE','BASIS',
19 'RATE_CHART_NAME','RATE_CHART_VIEW_FLAG','PORT_OF_LOADING',
20 'PORT_OF_DISCHARGE','DISTANCE','DISTANCE_UOM','TRANSIT_TIME',
21 'TRANSIT_TIME_UOM','SPECIAL_HANDLING','ADDITIONAL_INSTRUCTIONS',
22 'START_DATE','END_DATE');
23
24 SERVICE_RATING_SETUP CONSTANT STRINGARRAY := STRINGARRAY(
25 'ACTION','CARRIER_NAME','SERVICE_NUMBER','TYPE','SUBTYPE','NAME',
26 'VALUE_FROM','VALUE_TO','UOM','CURRENCY');
27
28 SCHEDULE CONSTANT STRINGARRAY := STRINGARRAY(
29 'ACTION','CARRIER_NAME','SERVICE_NUMBER','VESSEL_TYPE','VESSEL_NAME',
30 'VOYAGE_NUMBER','DEPARTURE_DATE','DEPARTURE_TIME','ARRIVAL_DATE',
31 'ARRIVAL_TIME','ARRIVAL_DATE_INDICATOR','FREQUENCY_TYPE','FREQUENCY',
32 'TRANSIT_TIME','TRANSIT_TIME_UOM','PORT_OF_LOADING','PORT_OF_DISCHARGE',
33 'START_DATE','END_DATE');
34
35 RATE_CHART CONSTANT STRINGARRAY := STRINGARRAY(
36 'ACTION','CARRIER_NAME','RATE_CHART_NAME','CURRENCY','START_DATE',
37 'END_DATE','DESCRIPTION','REPLACED_RATE_CHART');
38
39 RATE_LINE CONSTANT STRINGARRAY := STRINGARRAY(
40 'ACTION','LINE_NUMBER','DESCRIPTION','RATE','UOM','RATE_BREAK_TYPE',
41 'VOLUME_TYPE','RATE_TYPE');
42
43 RATE_BREAK CONSTANT STRINGARRAY := STRINGARRAY(
44 'ACTION','LINE_NUMBER','LOWER_LIMIT','UPPER_LIMIT','RATE', 'RATE_TYPE');
45
46 RATING_ATTRIBUTE CONSTANT STRINGARRAY := STRINGARRAY(
47 'ACTION','LINE_NUMBER','ATTRIBUTE','ATTRIBUTE_VALUE');
48
49 CHARGES_DISCOUNTS CONSTANT STRINGARRAY := STRINGARRAY(
50 'ACTION','CARRIER_NAME','CHARGES_DISCOUNTS_NAME','CURRENCY',
51 'START_DATE','END_DATE','DESCRIPTION');
52
53 ADJUSTED_RATE_CHART CONSTANT STRINGARRAY := STRINGARRAY(
54 'ACTION','RATE_CHART_NAME', 'TARIFF_NAME');
55
56 CHARGES_DISCOUNTS_LINE CONSTANT STRINGARRAY := STRINGARRAY(
57 'ACTION','LINE_NUMBER','DESCRIPTION','TYPE','SUBTYPE','UOM',
58 'RATE_PER_UOM','PERCENTAGE','FIXED_RATE');
59
60 RATING_ZONE_CHART CONSTANT STRINGARRAY := STRINGARRAY (
61 'NAME_PREFIX','MODE_OF_TRANSPORTATION','CARRIER_NAME',
62 'RATE_CHART_PREFIX', 'RATE_CHART_VIEW_FLAG', 'START_DATE', 'END_DATE');
63
64 RATING_SETUP CONSTANT STRINGARRAY := STRINGARRAY(
65 'SERVICE_LEVEL','TYPE','SUBTYPE','NAME','VALUE_FROM','VALUE_TO','UOM',
66 'CURRENCY');
67
68 ORIGIN CONSTANT STRINGARRAY := STRINGARRAY(
69 'POSTAL_CODE_RANGE','COUNTRY','STATE','CITY');
70
71 REGION CONSTANT STRINGARRAY := STRINGARRAY (
72 'ACTION','COUNTRY','COUNTRY_CODE','STATE','STATE_CODE','CITY',
73 'CITY_CODE','POSTAL_CODE_FROM','POSTAL_CODE_TO');
74
75 ZONE CONSTANT STRINGARRAY := STRINGARRAY(
76 'ACTION','ZONE_NAME','COUNTRY','STATE','CITY','POSTAL_CODE_FROM',
77 'POSTAL_CODE_TO');
78
79 TL_SERVICES CONSTANT STRINGARRAY := STRINGARRAY(
80 'ACTION','CARRIER_NAME','SERVICE_NUMBER', 'ORIGIN_COUNTRY','ORIGIN_STATE',
81 'ORIGIN_CITY','ORIGIN_POSTAL_CODE_FROM','ORIGIN_POSTAL_CODE_TO',
82 'ORIGIN_ZONE','DESTINATION_COUNTRY','DESTINATION_STATE',
83 'DESTINATION_CITY','DESTINATION_POSTAL_CODE_FROM',
84 'DESTINATION_POSTAL_CODE_TO','DESTINATION_ZONE','SERVICE_START_DATE',
85 'SERVICE_END_DATE','RATE_CHART_NAME','SERVICE_LEVEL');
86
87 TL_SURCHARGES CONSTANT STRINGARRAY := STRINGARRAY(
88 'ACTION','TYPE','CARRIER_NAME','SERVICE_LEVEL','CURRENCY','START_DATE','END_DATE',
89 'NUMBER_OF_FREE_STOPS','FIRST_ADD_STOP_OFF_CHARGES','SECOND_ADD_STOP_OFF_CHARGES',
90 'THIRD_ADD_STOP_OFF_CHARGES','FOURTH_ADD_STOP_OFF_CHARGES','FIFTH_ADD_STOP_OFF_CHARGES',
91 'ADDITIONAL_STOP_CHARGES','OUT_OF_ROUTE_CHARGES','OUT_OF_ROUTE_CHARGE_BASIS_UOM',
92 'BASIS_FOR_HANDLING_LOADING_UNLOADING_CHARGES','UOM_FOR_HANDLING_LOADING_UNLOADING_CHARGE_BASIS',
93 'HANDLING_CHARGES','MINIMUM_HANDLING_CHARGES','LOADING_CHARGES','MINIMUM_LOADING_CHARGES',
94 'ASSISTED_LOADING_CHARGES','MINIMUM_ASSISTED_LOADING_CHARGES','UNLOADING_CHARGES',
95 'MINIMUM_UNLOADING_CHARGES','ASSISTED_UNLOADING_CHARGES','MINIMUM_ASSISTED_UNLOADING_CHARGES',
96 'WEEKDAY_LAYOVER_CHARGES','DISTANCE_UOM_FOR_WEEKEND_LAYOVER_CHARGES',
97 'WEEKEND_LAYOVER_DISTANCE_BREAK', 'CHARGES','CONTINUOUS_MOVE_DISCOUNT_PERCENTAGE',
98 'COUNTRY','STATE','CITY','POSTAL_CODE_FROM','POSTAL_CODE_TO','SURCHARGES');
99
100 FACILITY_CHARGES CONSTANT STRINGARRAY := STRINGARRAY(
101 'ACTION','FACILITY_RATE_CHART_NAME','START_DATE','END_DATE','CURRENCY',
102 'BASIS_FOR_HANDLING_LOADING_UNLOADING_CHARGES','UOM_FOR_HANDLING_LOADING_UNLOADING_CHARGE_BASIS',
103 'HANDLING_CHARGES', 'MINIMUM_HANDLING_CHARGES','LOADING_CHARGES','MINIMUM_LOADING_CHARGES',
104 'UNLOADING_CHARGES', 'MINIMUM_UNLOADING_CHARGES','ASSISTED_LOADING_CHARGES',
105 'MINIMUM_ASSISTED_LOADING_CHARGES', 'ASSISTED_UNLOADING_CHARGES',
106 'MINIMUM_ASSISTED_UNLOADING_CHARGES');
107
108 g_debug_set BOOLEAN := TRUE;
109 g_debug_on BOOLEAN := TRUE;
110
111 ----------------------------------------------------------------------------
112 -- PROCEDURE VALIDATE_COLUMNS
113 --
114 -- Purpose: check if the columns read is valid
115 --
116 -- IN parameters:
117 -- 1. p_keys: columns STRINGARRAY
118 -- 2. p_type: type of the block
119 -- 3. p_line_number: line number
120 --
121 -- OUT parameters:
122 -- 1. x_status: status, -1 if no error(1 wrong number, 2 wrong column, 3 no such type)
123 -- 2. x_error_msg: error message if status <> -1
124 ----------------------------------------------------------------------------
125 PROCEDURE VALIDATE_COLUMNS (p_keys IN FTE_BULKLOAD_PKG.block_header_tbl,
126 p_type IN VARCHAR2,
127 p_line_number IN NUMBER,
128 x_status OUT NOCOPY NUMBER,
129 x_error_msg OUT NOCOPY VARCHAR2);
130
131 ----------------------------------------------------------------------------
132 -- PROCEDURE VALIDATE_RATING_ZONE_CHART
133 --
134 -- Purpose: does validation for one line in rating zone chart block
135 --
136 -- IN parameters:
137 -- 1. p_values: pl/sql table of the rating zone chart line
138 -- 2. p_line_number: line number of current line
139 --
140 -- OUT parameters:
141 -- 1. p_chart_info: a STRINGARRAY that contains name_prefix, mode_of_trans, carrier_name,
142 -- carrier_id, price_prefix, view_flag, start_date, and end_date
143 -- 2. x_status: status of the processing, -1 means no error
144 -- 3. x_error_msg: error message if any.
145 ----------------------------------------------------------------------------
146 PROCEDURE VALIDATE_RATING_ZONE_CHART(p_values IN FTE_BULKLOAD_PKG.data_values_tbl,
147 p_line_number IN NUMBER,
148 p_chart_info OUT NOCOPY STRINGARRAY,
149 x_status OUT NOCOPY NUMBER,
150 x_error_msg OUT NOCOPY VARCHAR2);
151
152 ----------------------------------------------------------------------------
153 -- PROCEDURE VALIDATE_RATING_SETUP
154 --
155 -- Purpose: does validation for one line in rating setup block
156 --
157 -- IN parameters:
158 -- 1. p_values: pl/sql table of rating setup line
159 -- 2. p_line_number: line number of current line
160 --
161 -- OUT parameters:
162 -- 1. p_setup_info: table of service information
163 -- 2. p_last_service_type: last line's service type
164 -- 3. x_status: status of the processing, -1 means no error
165 -- 4. x_error_msg: error message if any.
166 ----------------------------------------------------------------------------
167 PROCEDURE VALIDATE_RATING_SETUP(p_values IN FTE_BULKLOAD_PKG.data_values_tbl,
168 p_line_number IN NUMBER,
169 p_setup_info IN OUT NOCOPY FTE_BULKLOAD_PKG.array_tbl,
170 p_last_service_type IN OUT NOCOPY VARCHAR2,
171 x_status OUT NOCOPY NUMBER,
172 x_error_msg OUT NOCOPY VARCHAR2);
173
174 ----------------------------------------------------------------------------
175 -- PROCEDURE VALIDATE_ORIGIN
176 --
177 -- Purpose: does validation for one line in origin block
178 --
179 -- IN parameters:
180 -- 1. p_values: pl/sql table of origin line
181 -- 2. p_line_number: line number of current line
182 --
183 -- OUT parameters:
184 -- 1. p_origin: STRINGARRAY of origin_postal, origin_country, origin_state, and origin_city
185 -- 2. x_status: status of the processing, -1 means no error
186 -- 3. x_error_msg: error message if any.
187 ----------------------------------------------------------------------------
188 PROCEDURE VALIDATE_ORIGIN(p_values IN FTE_BULKLOAD_PKG.data_values_tbl,
189 p_line_number IN NUMBER,
190 p_origin OUT NOCOPY STRINGARRAY,
191 x_status OUT NOCOPY NUMBER,
192 x_error_msg OUT NOCOPY VARCHAR2);
193
194 ----------------------------------------------------------------------------
195 -- PROCEDURE VALIDATE_DESTINATION
196 --
197 -- Purpose: does validation for one line in destination block
198 --
199 -- IN parameters:
200 -- 1. p_values: pl/sql table of destination line
201 -- 2. p_line_number: line number of current line
202 -- 3. p_price_prefix: price prefix
203 -- 4. p_carrier_id: carrier id
204 -- 5. p_origin_zone: pl/sql table of origin zone info
205 -- 6. p_service_count: number of service columns
206 -- 7. p_services: service column names
207 --
208 -- OUT parameters:
209 -- 1. p_dest: STRINGARRAY of dest_postal, dest_country, dest_state, and dest_city
210 -- 2. x_status: status of the processing, -1 means no error
211 -- 3. x_error_msg: error message if any.
212 ----------------------------------------------------------------------------
213 PROCEDURE VALIDATE_DESTINATION(p_values IN OUT NOCOPY FTE_BULKLOAD_PKG.data_values_tbl,
214 p_line_number IN NUMBER,
215 p_price_prefix IN VARCHAR2,
216 p_carrier_id IN NUMBER,
217 p_origin_zone IN FTE_BULKLOAD_PKG.data_values_tbl,
218 p_service_count IN OUT NOCOPY NUMBER,
219 p_services IN OUT NOCOPY FTE_PARCEL_LOADER.service_array,
220 p_dest OUT NOCOPY STRINGARRAY,
221 x_status OUT NOCOPY NUMBER,
222 x_error_msg OUT NOCOPY VARCHAR2);
223
224 ----------------------------------------------------------------------------
225 -- PROCEDURE VALIDATE_SERVICE
226 --
227 -- Purpose: does validation for a line in service block
228 --
229 -- IN parameters:
230 -- 1. p_values: pl/sql table of the service line
231 -- 2. p_line_number: line number of current line
232 --
233 -- OUT parameters:
234 -- 1. p_type: type value of the line
235 -- 2. p_action: action value of the line
236 -- 3. p_lane_tbl: pl/sql table for lane
237 -- 4. p_lane_rate_chart_tbl: pl/sql table for lane rate chart
238 -- 5. p_lane_service_tbl: pl/sql table for lane service
239 -- 6. p_lane_commodity_tbl: pl/sql table for lane commodity
240 -- 7. x_status: status of the processing, -1 means no error
241 -- 8. x_error_msg: error message if any.
242 ----------------------------------------------------------------------------
243 PROCEDURE VALIDATE_SERVICE(p_values IN FTE_BULKLOAD_PKG.data_values_tbl,
244 p_line_number IN NUMBER,
245 p_type OUT NOCOPY VARCHAR2,
246 p_action OUT NOCOPY VARCHAR2,
247 p_lane_tbl IN OUT NOCOPY FTE_LANE_PKG.lane_tbl,
248 p_lane_rate_chart_tbl IN OUT NOCOPY FTE_LANE_PKG.lane_rate_chart_tbl,
249 p_lane_service_tbl IN OUT NOCOPY FTE_LANE_PKG.lane_service_tbl,
250 p_lane_commodity_tbl IN OUT NOCOPY FTE_LANE_PKG.lane_commodity_tbl,
251 x_status OUT NOCOPY NUMBER,
252 x_error_msg OUT NOCOPY VARCHAR2);
253
254 ----------------------------------------------------------------------------
255 -- PROCEDURE VALIDATE_SERVICE_RATING_SETUP
256 --
257 -- Purpose: does validation for one line in service rating setup block
258 --
259 -- IN parameters:
260 -- 1. p_values: pl/sql table for the service rating setup line
261 -- 2. p_line_number: line number of current line
262 -- 3. p_pre_lane_number: previous lane's line number
263 --
264 -- OUT parameters:
265 -- 1. p_prc_parameter_tbl: pl/sql table for pricing parameter
266 -- 2. p_deficit_wt: deficit weight parameter
267 -- 3. p_lane_function: lane function
268 -- 4. p_lane_number: lane number
269 -- 5. p_action: action value of the line
270 -- 6. x_status: status of the processing, -1 means no error
271 -- 7. x_error_msg: error message if any.
272 ----------------------------------------------------------------------------
273 PROCEDURE VALIDATE_SERVICE_RATING_SETUP(p_values IN FTE_BULKLOAD_PKG.data_values_tbl,
274 p_line_number IN NUMBER,
275 p_pre_lane_number IN VARCHAR2,
276 p_prc_parameter_tbl IN OUT NOCOPY FTE_LANE_PKG.prc_parameter_tbl,
277 p_deficit_wt IN OUT NOCOPY BOOLEAN,
278 p_lane_function IN OUT NOCOPY VARCHAR2,
279 p_lane_number OUT NOCOPY VARCHAR2,
280 p_action OUT NOCOPY VARCHAR2,
281 x_status OUT NOCOPY NUMBER,
282 x_error_msg OUT NOCOPY VARCHAR2);
283
284 ----------------------------------------------------------------------------
285 -- PROCEDURE VALIDATE_SCHEDULE
286 --
287 -- Purpose: does validation for one line in schedule block
288 --
289 -- IN parameters:
290 -- 1. p_values: pl/sql table for schedule line
291 -- 2. p_line_number: line number of current line
292 --
293 -- OUT parameters:
294 -- 1. p_schedule_tbl: pl/sql table for schedules
295 -- 2. p_action: action value of the line
296 -- 3. x_status: status of the processing, -1 means no error
297 -- 4. x_error_msg: error message if any.
298 ----------------------------------------------------------------------------
299 PROCEDURE VALIDATE_SCHEDULE(p_values IN FTE_BULKLOAD_PKG.data_values_tbl,
300 p_line_number IN NUMBER,
301 p_schedule_tbl IN OUT NOCOPY FTE_LANE_PKG.schedule_tbl,
302 p_action OUT NOCOPY VARCHAR2,
303 x_status OUT NOCOPY NUMBER,
304 x_error_msg OUT NOCOPY VARCHAR2);
305
306 -----------------------------------------------------------------------------
307 -- PROCEDURE: VALIDATE_RATE_CHART
308 --
309 -- Purpose: Validate the data corresponding to this Line Header, and
310 -- store the data in temporary tables for later insertion into
311 -- p_list_header_tbl
312 --
313 -- IN Parameters
314 -- 1. p_values: pl/sql table for the rate chart line
315 -- 2. p_line_number: line number
316 -- 3. p_validate: boolean for validating data within VALIDATION procedure, default true
317 -- 4. p_process_id process id for the loading, default null and assigned later
318 --
319 -- Out Parameters
320 -- 1. p_qp_list_header_tbl: pl/sql table for the list header information
321 -- 2. p_qp_qualifier_tbl: pl/sql table for the qualifiers
322 -- 3. p_action: action of the line
323 -- 4. p_carrier_id: carrier id
324 -- 4. x_status: status of the processing, -1 means no error
325 -- 5. x_error_msg: error message if any.
326 -----------------------------------------------------------------------------
327 PROCEDURE VALIDATE_RATE_CHART(p_values IN FTE_BULKLOAD_PKG.data_values_tbl,
328 p_line_number IN NUMBER,
329 p_validate IN BOOLEAN DEFAULT TRUE,
330 p_process_id IN NUMBER default null,
331 p_qp_list_header_tbl IN OUT NOCOPY FTE_RATE_CHART_PKG.qp_list_header_tbl,
332 p_qp_qualifier_tbl IN OUT NOCOPY FTE_RATE_CHART_PKG.qp_qualifier_tbl,
333 p_action OUT NOCOPY VARCHAR2,
334 p_carrier_id OUT NOCOPY NUMBER,
335 x_status OUT NOCOPY NUMBER,
336 x_error_msg OUT NOCOPY VARCHAR2);
337
338 -----------------------------------------------------------------------------
339 -- PROCEDURE: VALIDATE_RATE_LINE
340 --
341 -- Purpose: Validate the data corresponding to this Rate Chart Line, and
342 -- store the data in temporary tables for later insertion into
343 -- QP_INTERFACE_LIST_LINES and QP_INTERFACE_LIST_PRICING_ATTRIBS.
344 --
345 -- IN Parameters
346 -- 1. p_values: pl/sql table for the rate line line
347 -- 2. p_line_number: line number
348 -- 3. p_validate: boolean for validating data within VALIDATION procedure, default true
349 --
350 -- Out Parameters
351 -- 1. p_qp_list_line_tbl: pl/sql table for the list line information
352 -- 2. p_qp_pricing_attrib_tbl: pl/sql table for the attributes
353 -- 3. x_status: status of the processing, -1 means no error
354 -- 4. x_error_msg: error message if any.
355 -----------------------------------------------------------------------------
356 PROCEDURE VALIDATE_RATE_LINE(p_values IN FTE_BULKLOAD_PKG.data_values_tbl,
357 p_line_number IN NUMBER,
358 p_validate IN BOOLEAN DEFAULT TRUE,
359 p_qp_list_line_tbl IN OUT NOCOPY FTE_RATE_CHART_PKG.qp_list_line_tbl,
360 p_qp_pricing_attrib_tbl IN OUT NOCOPY FTE_RATE_CHART_PKG.qp_pricing_attrib_tbl,
361 x_status OUT NOCOPY NUMBER,
362 x_error_msg OUT NOCOPY VARCHAR2);
363
364 -----------------------------------------------------------------------------
365 -- PROCEDURE: VALIDATE_RATE_BREAK
366 --
367 -- Purpose: Validate the data corresponding to this price break, and store
368 -- the data in temporary tables for later insertion into
369 -- QP_INTERFACE_LIST_LINES and QP_INTERFACE_PRICING_ATTRIBS.
370 --
371 -- IN Parameters
372 -- 1. p_values: pl/sql table for the rate break line
373 -- 2. p_line_number: line number
374 -- 3. p_validate: boolean for validating data within VALIDATION procedure, default true
375 --
376 -- Out Parameters
377 -- 1. p_qp_list_line_tbl: pl/sql table for the list line information
378 -- 2. p_qp_pricing_attrib_tbl: pl/sql table for the attributes
379 -- 3. x_status: status of the processing, -1 means no error
380 -- 4. x_error_msg: error message if any.
381 -----------------------------------------------------------------------------
382 PROCEDURE VALIDATE_RATE_BREAK(p_values IN FTE_BULKLOAD_PKG.data_values_tbl,
383 p_line_number IN NUMBER,
384 p_validate IN BOOLEAN DEFAULT TRUE,
385 p_qp_list_line_tbl IN OUT NOCOPY FTE_RATE_CHART_PKG.qp_list_line_tbl,
386 p_qp_pricing_attrib_tbl IN OUT NOCOPY FTE_RATE_CHART_PKG.qp_pricing_attrib_tbl,
387 x_status OUT NOCOPY NUMBER,
388 x_error_msg OUT NOCOPY VARCHAR2);
389
390 -----------------------------------------------------------------------------
391 -- PROCEDURE: VALIDATE_RATING_ATTRIBUTE
392 --
393 -- Purpose: Validate the data corresponding to this pricing attribute, and
394 -- store the data in temporary tables for later insertion into
395 -- QP_INTERFACE_PRICING_ATTRIBS.
396 --
397 -- IN Parameters
398 -- 1. p_values: pl/sql table for rating attribute line
399 -- 2. p_line_number: line number
400 --
401 -- Out Parameters
402 -- 1. p_qp_pricing_attrib_tbl: pl/sql table for the attributes
403 -- 2. x_status: status of the processing, -1 means no error
404 -- 3. x_error_msg: error message if any.
405 -----------------------------------------------------------------------------
406 PROCEDURE VALIDATE_RATING_ATTRIBUTE(p_values IN FTE_BULKLOAD_PKG.data_values_tbl,
407 p_line_number IN NUMBER,
408 p_qp_pricing_attrib_tbl IN OUT NOCOPY FTE_RATE_CHART_PKG.qp_pricing_attrib_tbl,
409 x_status OUT NOCOPY NUMBER,
410 x_error_msg OUT NOCOPY VARCHAR2);
411
412 -----------------------------------------------------------------------------
413 -- PROCEDURE: VALIDATE_ADJUSTED_RATE_CHART
414 --
415 -- Purpose: Validate the data corresponding to this Qualifier, and
416 -- store the data in temporary tables for later insertion into
417 -- QP_INTERFACE_QUALIFIERS.
418 --
419 -- IN Parameters
420 -- 1. p_values: pl/sql table for adjusted rate chart line
421 -- 2. p_line_number: line number
422 -- 3. p_carrier_id: carrier id from the header
423 --
424 -- Out Parameters
425 -- 1. p_qp_qualifier_tbl: pl/sql table for the attributes
426 -- 2. x_status: status of the processing, -1 means no error
427 -- 3. x_error_msg: error message if any.
428 -----------------------------------------------------------------------------
429 PROCEDURE VALIDATE_ADJUSTED_RATE_CHART(p_values IN FTE_BULKLOAD_PKG.data_values_tbl,
430 p_line_number IN NUMBER,
431 p_carrier_id IN NUMBER,
432 p_qp_qualifier_tbl IN OUT NOCOPY FTE_RATE_CHART_PKG.qp_qualifier_tbl,
433 x_status OUT NOCOPY NUMBER,
434 x_error_msg OUT NOCOPY VARCHAR2);
435
436 -----------------------------------------------------------------------------
437 -- PROCEDURE: VALIDATE_QUALIFIER
438 --
439 -- Purpose: Add a qualifier to the qp_interface_qualifiers table for the
440 -- a rate chart.
441 --
442 -- IN Parameters
443 -- 1. p_values: pl/sql table for the qualifier line
444 -- 2. p_line_number: line number
445 --
446 -- Out Parameters
447 -- 1. p_qp_qualifier_tbl: pl/sql table for the attributes
448 -- 2. x_status: status of the processing, -1 means no error
449 -- 3. x_error_msg: error message if any.
450 -----------------------------------------------------------------------------
451 PROCEDURE VALIDATE_QUALIFIER(p_values IN FTE_BULKLOAD_PKG.data_values_tbl,
452 p_line_number IN NUMBER,
453 p_qp_qualifier_tbl IN OUT NOCOPY FTE_RATE_CHART_PKG.qp_qualifier_tbl,
454 x_status OUT NOCOPY NUMBER,
455 x_error_msg OUT NOCOPY VARCHAR2);
456
457 -----------------------------------------------------------------------------
458 -- PROCEDURE: ADD_ATTRIBUTE
459 --
460 -- Purpose: Store a pricing attribute in the temporary attribute tables for
461 -- later insertion into QP_INTERFACE_PRICING_ATTRIBS.
462 --
463 -- IN Parameters
464 -- 1. p_pricing_attribute: The pricing attribute
465 -- 2. p_attr_value_from: The value of the pricing attribute
466 -- 3. p_attr_value_to:
467 -- 4. p_line_number: The line number for this pricing attribute.
468 -- 5. p_comp_operator: comparison operator
469 -- 6. p_qp_pricing_attrib_tbl: pl/sql table for pricing attributes
470 --
471 -- Out Parameters
472 -- 1. x_status: status, -1 when no errors
473 -- 2. x_error_msg: error message if any
474 -----------------------------------------------------------------------------
475 PROCEDURE ADD_ATTRIBUTE(p_pricing_attribute IN VARCHAR2,
476 p_attr_value_from IN VARCHAR2,
477 p_attr_value_to IN VARCHAR2,
478 p_line_number IN VARCHAR2,
479 p_context IN VARCHAR2,
480 p_comp_operator IN VARCHAR2,
481 p_qp_pricing_attrib_tbl IN OUT NOCOPY FTE_RATE_CHART_PKG.qp_pricing_attrib_tbl,
482 x_status OUT NOCOPY NUMBER,
483 x_error_msg OUT NOCOPY VARCHAR2);
484
485 -----------------------------------------------------------------------------
486 -- FUNCTION Validate_Service_Level
487 --
488 -- Purpose Validate that the carrier with id <p_carrier_id> has been set
489 -- up to handle the service level <p_service_level>
490 --
491 -- IN Parameters
492 -- 1. p_carrier_id: The carrier id
493 -- 2. p_carrier_name: carrier name
494 -- 3. p_service_level: service level to validate
495 -- 4. p_line_number: line number
496 --
497 -- OUT Parameters
498 -- 1. x_status: error status, -1 if no errors
499 -- 2. x_error_msg: error message if any
500 --
501 -- Returns:
502 -- service code
503 -----------------------------------------------------------------------------
504 FUNCTION VALIDATE_SERVICE_LEVEL(p_carrier_id IN NUMBER,
505 p_carrier_name IN VARCHAR2,
506 p_service_level IN VARCHAR2,
507 p_line_number IN NUMBER,
508 x_status OUT NOCOPY NUMBER,
509 x_error_msg OUT NOCOPY VARCHAR2) RETURN VARCHAR2;
510
511 ----------------------------------------------------------------------------
512 -- PROCEDURE VALIDATE_ACTION
513 --
514 -- Purpose: check if the action is valid
515 --
516 -- IN parameters:
517 -- 1. p_action: action value
518 -- 2. p_type: block type
519 -- 3. p_line_number: line number
520 --
521 -- OUT parameters:
522 -- 1. x_status: status, -1 if no error
523 -- 2. x_error_msg: error message if status <> -1
524 ----------------------------------------------------------------------------
525 PROCEDURE VALIDATE_ACTION(p_action IN OUT NOCOPY VARCHAR2,
526 p_type IN VARCHAR2,
527 p_line_number IN NUMBER,
528 x_status OUT NOCOPY NUMBER,
529 x_error_msg OUT NOCOPY VARCHAR2);
530
531
532 ----------------------------------------------------------------------------
533 -- PROCEDURE VALIDATE_TL_SERVICE
534 --
535 -- Purpose: does validation for a line in tl service block
536 --
537 -- IN parameters:
538 -- 1. p_values: FTE_BULKLOAD_PKG.data_values_tbl
539 -- 2. p_line_number: line number of current line
540 --
541 -- OUT parameters:
542 -- 1. p_type: type value of the line
543 -- 2. p_action: action value of the line
544 -- 3. x_status: status of the processing, -1 means no error
545 -- 4. x_error_msg: error message if any.
546 ----------------------------------------------------------------------------
547 PROCEDURE VALIDATE_TL_SERVICE(p_values IN FTE_BULKLOAD_PKG.data_values_tbl,
548 p_line_number IN NUMBER,
549 p_type OUT NOCOPY VARCHAR2,
550 p_action OUT NOCOPY VARCHAR2,
551 p_lane_tbl IN OUT NOCOPY FTE_LANE_PKG.lane_tbl,
552 p_lane_service_tbl IN OUT NOCOPY FTE_LANE_PKG.lane_service_tbl,
553 p_lane_rate_chart_tbl IN OUT NOCOPY FTE_LANE_PKG.lane_rate_chart_tbl,
554 x_status OUT NOCOPY NUMBER,
555 x_error_msg OUT NOCOPY VARCHAR2);
556
557 ----------------------------------------------------------------------------
558 -- PROCEDURE VALIDATE_LANE_NUMBER
559 --
560 -- Purpose: check if the lane number is valid
561 --
562 -- IN parameters:
563 -- 1. p_lane_number: lane number value
564 -- 2. p_carrier_id: carrier id
565 -- 3. p_line_number: line number
566 --
567 -- OUT parameters:
568 -- 1. p_lane_id: lane id of the lane number if exists in the table
569 -- 2. x_status: status, -1 if no error
570 -- 3. x_error_msg: error message if status <> -1
571 ----------------------------------------------------------------------------
572 PROCEDURE VALIDATE_LANE_NUMBER(p_lane_number IN VARCHAR2,
573 p_carrier_id IN NUMBER,
574 p_line_number IN NUMBER,
575 p_lane_id OUT NOCOPY NUMBER,
576 x_status OUT NOCOPY NUMBER,
577 x_error_msg OUT NOCOPY VARCHAR2);
578
579 --_______________________________________________________________________________________--
580 --
581 -- PROCEDURE VALIDATE_ZONE
582 --
583 -- Purpose: does validation for one line in zone block
584 --
585 -- IN parameters:
586 -- 1. p_columns: STRINGARRAY of the column names
587 -- 2. p_values: STRINGARRAY of the line values
588 -- 3. p_line_number: line number of current line
589 -- 4. p_region_type: region type for the zone (10, 11 etc)
590 --
591 -- OUT parameters:
592 -- 1. p_action: action value of the line
593 -- 2. p_zone_name: zone name value of the line
594 -- 3. p_country: country value of the line
595 -- 4. p_zone_id: zone id, not -1 if zone exists
596 -- 5. p_region_rec: record to store region info
597 -- 6. p_region_id: region id, not -1 if already exists
598 -- 7. x_status: status of the processing, -1 means no error
599 -- 8. x_error_msg: error message if any.
600 --_______________________________________________________________________________________--
601
602 PROCEDURE VALIDATE_ZONE(p_values IN FTE_BULKLOAD_PKG.data_values_tbl,
603 p_line_number IN NUMBER,
604 p_region_type IN VARCHAR2,
605 p_action OUT NOCOPY VARCHAR2,
606 p_zone_name OUT NOCOPY VARCHAR2,
607 p_country OUT NOCOPY VARCHAR2,
608 p_zone_id OUT NOCOPY NUMBER,
609 p_region_rec OUT NOCOPY WSH_REGIONS_SEARCH_PKG.region_rec,
610 p_region_id OUT NOCOPY NUMBER,
611 x_status OUT NOCOPY NUMBER,
612 x_error_msg OUT NOCOPY VARCHAR2);
613 END FTE_VALIDATION_PKG;
[Home] [Help]