1 PACKAGE FTE_LANE_PKG AS
2 /* $Header: FTELANES.pls 120.1 2005/06/28 22:48:37 pkaliyam noship $ */
3
4 TYPE lane_rec IS RECORD (
5 action VARCHAR2(10),
6 carrier_id fte_lanes.carrier_id%TYPE,
7 lane_id fte_lanes.lane_id%TYPE,
8 origin_id fte_lanes.origin_id%TYPE,
9 destination_id fte_lanes.destination_id%TYPE,
10 distance fte_lanes.distance%TYPE,
11 distance_uom fte_lanes.distance_uom%TYPE,
12 transit_time fte_lanes.transit_time%TYPE,
13 transit_time_uom fte_lanes.transit_time_uom%TYPE,
14 basis fte_lanes.basis%TYPE,
15 comm_fc_class_code fte_lanes.comm_fc_class_code%TYPE,
16 effective_date fte_lanes.effective_date%TYPE,
17 expiry_date fte_lanes.expiry_date%TYPE,
18 pricelist_view_flag fte_lanes.pricelist_view_flag%TYPE,
19 editable_flag fte_lanes.editable_flag%TYPE,
20 lane_number fte_lanes.lane_number%TYPE,
21 mode_of_transportation_code fte_lanes.mode_of_transportation_code%TYPE,
22 additional_instructions fte_lanes.additional_instructions%TYPE,
23 special_handling fte_lanes.special_handling%TYPE,
24 lane_type fte_lanes.lane_type%TYPE,
25 tariff_name fte_lanes.tariff_name%TYPE,
26 service_type_code fte_lanes.service_type_code%TYPE,
27 commodity_catg_id fte_lanes.commodity_catg_id%TYPE,
28 equipment_type_code fte_lanes.equipment_type_code%TYPE,
29 container_all_flag BOOLEAN,
30 basis_flag BOOLEAN,
31 line_number NUMBER);
32
33 TYPE lane_tbl IS TABLE OF
34 lane_rec
35 INDEX BY BINARY_INTEGER;
36
37 TYPE lane_rate_chart_rec IS RECORD (
38 lane_id fte_lane_rate_charts.lane_id%TYPE,
39 list_header_id fte_lane_rate_charts.list_header_id%TYPE,
40 start_date_active fte_lane_rate_charts.start_date_active%TYPE,
41 end_date_active fte_lane_rate_charts.end_date_active%TYPE);
42
43 TYPE lane_rate_chart_tbl IS TABLE OF
44 lane_rate_chart_rec
45 INDEX BY BINARY_INTEGER;
46
47 TYPE lane_commodity_rec IS RECORD (
48 commodity_catg_id fte_lane_commodities.commodity_catg_id%TYPE,
49 basis fte_lane_commodities.basis%TYPE,
50 lane_id fte_lane_commodities.lane_id%TYPE,
51 lane_commodity_id fte_lane_commodities.lane_commodity_id%TYPE,
52 basis_flag BOOLEAN);
53
54 TYPE lane_commodity_tbl IS TABLE OF
55 lane_commodity_rec
56 INDEX BY BINARY_INTEGER;
57
58 TYPE lane_service_rec IS RECORD (
59 service_code fte_lane_services.service_code%TYPE,
60 lane_id fte_lane_services.lane_id%TYPE,
61 lane_service_id fte_lane_services.lane_service_id%TYPE);
62
63 TYPE lane_service_tbl IS TABLE OF
64 lane_service_rec
65 INDEX BY BINARY_INTEGER;
66
67 TYPE schedule_rec IS RECORD (
68 vessel_name fte_schedules.vessel_name%TYPE,
69 vessel_type fte_schedules.vessel_type%TYPE,
70 voyage_number fte_schedules.voyage_number%TYPE,
71 arrival_date_indicator fte_schedules.arrival_date_indicator%TYPE,
72 transit_time fte_schedules.transit_time%TYPE,
73 port_of_loading fte_schedules.port_of_loading%TYPE,
74 port_of_discharge fte_schedules.port_of_discharge%TYPE,
75 frequency_type fte_schedules.frequency_type%TYPE,
76 frequency fte_schedules.frequency%TYPE,
77 frequency_arrival fte_schedules.frequency_arrival%TYPE,
78 departure_time fte_schedules.departure_time%TYPE,
79 arrival_time fte_schedules.arrival_time%TYPE,
80 departure_date fte_schedules.departure_date%TYPE,
81 arrival_date fte_schedules.arrival_date%TYPE,
82 effective_date fte_schedules.effective_date%TYPE,
83 expiry_date fte_schedules.expiry_date%TYPE,
84 transit_time_uom fte_schedules.transit_time_uom%TYPE,
85 lane_id fte_schedules.lane_id%TYPE,
86 schedules_id fte_schedules.schedules_id%TYPE,
87 lane_number fte_schedules.lane_number%TYPE);
88
89 TYPE schedule_tbl IS TABLE OF
90 fte_schedules%ROWTYPE
91 INDEX BY BINARY_INTEGER;
92
93 TYPE prc_parameter_rec IS RECORD (
94 value_from fte_prc_parameters.value_from%TYPE,
95 value_to fte_prc_parameters.value_to%TYPE,
96 uom_code fte_prc_parameters.uom_code%TYPE,
97 currency_code fte_prc_parameters.currency_code%TYPE,
98 parameter_instance_id fte_prc_parameters.parameter_instance_id%TYPE,
99 lane_id fte_prc_parameters.lane_id%TYPE,
100 parameter_id fte_prc_parameters.parameter_id%TYPE);
101
102 TYPE prc_parameter_tbl IS TABLE OF
103 prc_parameter_rec
104 INDEX BY BINARY_INTEGER;
105
106 ---------------------------------------------------------------------------
107 -- FUNCTION GET_LANE_ID
108 --
109 -- Purpose: get a lane id for the lane number and carrier id
110 --
111 -- IN parameters:
112 -- 1. p_lane_number: unique lane identification name
113 -- 2. p_carrier_id: carrier id
114 --
115 -- Returns a number, -1 for no lane id, else the lane id for the lane number and carrier
116 ---------------------------------------------------------------------------
117 FUNCTION GET_LANE_ID (p_lane_number IN VARCHAR2,
118 p_carrier_id IN NUMBER) RETURN NUMBER;
119
120 ---------------------------------------------------------------------------
121 -- FUNCTION GET_NEXT_LANE_ID
122 --
123 -- Purpose: get the next lane squence number
124 --
125 -- Returns lane squence
126 ---------------------------------------------------------------------------
127 FUNCTION GET_NEXT_LANE_ID RETURN NUMBER;
128
129 ---------------------------------------------------------------------------
130 -- FUNCTION GET_NEXT_LANE_COMMODITY_ID
131 --
132 -- Purpose: get the next squence number for commodity
133 --
134 -- Returns lane commodity sequence
135 ---------------------------------------------------------------------------
136 FUNCTION GET_NEXT_LANE_COMMODITY_ID RETURN NUMBER;
137
138 ---------------------------------------------------------------------------
139 -- FUNCTION GET_NEXT_LANE_SERVICE_ID
140 --
141 -- Purpose: get the next squence number for service
142 --
143 -- Returns lane service squence
144 ---------------------------------------------------------------------------
145 FUNCTION GET_NEXT_LANE_SERVICE_ID RETURN NUMBER;
146
147 --------------------------------------------------------------------------
148 -- FUNCTION GET_NEXT_SCHEDULE_ID
149 --
150 -- Purpose: get the next schedule id based on the sequence
151 --
152 -- Returns schedule id, -1 if not found, -2 if other errors
153 --------------------------------------------------------------------------
154 FUNCTION GET_NEXT_SCHEDULE_ID RETURN NUMBER;
155
156 --------------------------------------------------------------------------
157 -- FUNCTION GET_NEXT_PRC_PARAMETER_ID
158 --
159 -- Purpose: get the next schedule id based on the sequence
160 --
161 -- Returns schedule id, -1 if not found, -2 if other errors
162 --------------------------------------------------------------------------
163 FUNCTION GET_NEXT_PRC_PARAMETER_ID RETURN NUMBER;
164
165 --------------------------------------------------------------------------
166 -- FUNCTION GET_SCHEDULE
167 --
168 -- Purpose: get the schedule id based on the lane id and voyage
169 --
170 -- IN parameters:
171 -- 1. p_lane_id: lane id to be searched
172 -- 2. p_voyage: voyage
173 --
174 -- Returns the schedule id. < 0 if errors
175 --------------------------------------------------------------------------
176 FUNCTION GET_SCHEDULE(p_lane_id IN NUMBER,
177 p_voyage IN VARCHAR2) RETURN NUMBER;
178
179 ------------------------------------------------------------------------
180 -- PROCEDURE DELETE_ROW
181 --
182 -- Purpose: delete a row in the fte_* tables
183 --
184 -- IN parameters:
185 -- 1. p_id: id to used for delete
186 -- 2. p_table: table to delete from
187 -- 3. p_code: codes to use for matching
188 --
189 -- OUT parameters:
190 -- 1. x_status: status of the error -1 when no error
191 -- 2. x_error_msg: error msg if any errors
192 ------------------------------------------------------------------------
193 PROCEDURE DELETE_ROW(p_id IN NUMBER,
194 p_table IN VARCHAR2,
195 p_code IN VARCHAR2,
196 p_line_number IN NUMBER,
197 x_status OUT NOCOPY NUMBER,
198 x_error_msg OUT NOCOPY VARCHAR2);
199
200 -------------------------------------------------------------------------
201 -- PROCEDURE UPDATE_LANE_FLAGS
202 --
203 -- Purpose: update the service detail, commodity detail, and schedules flag of the lane
204 --
205 -- IN parameters:
206 -- 1. p_type: type of the update
207 -- 2. p_lane_id: lane id to be updated
208 -- 3. p_value: value to set the flag to for schedule
209 --
210 -- OUT parameters:
211 -- 1. x_status: status of the error -1 when no error
212 -- 2. x_error_msg: error msg if any errors
213 -------------------------------------------------------------------------
214 PROCEDURE UPDATE_LANE_FLAGS(p_type IN VARCHAR2,
215 p_lane_id IN NUMBER,
216 p_value IN VARCHAR2 DEFAULT 'N',
217 x_status OUT NOCOPY NUMBER,
218 x_error_msg OUT NOCOPY VARCHAR2);
219
220 -------------------------------------------------------------------------
221 -- PROCEDURE UPDATE_LANE_RATE_CHART
222 --
223 -- Purpose: update lane rate chart's dates
224 --
225 -- IN parameters:
226 -- 1. p_list_header_id: the rate chart to update in fte_lane_rate_charts
227 -- 2. p_start_date: start date
228 -- 3. p_end_date: end date
229 --
230 -- OUT parameters:
231 -- 1. x_status: status of the error -1 when no error
232 -- 2. x_error_msg: error msg if any errors
233 -------------------------------------------------------------------------
234 PROCEDURE UPDATE_LANE_RATE_CHART (p_list_header_id IN NUMBER,
235 p_start_date IN DATE,
236 p_end_date IN DATE,
237 x_status OUT NOCOPY NUMBER,
238 x_error_msg OUT NOCOPY VARCHAR2);
239
240
241 ------------------------------------------------------------------
242 -- FUNCTION VERIFY_OVERLAPPING_DATE
243 --
244 -- Purpose: verify if the rate chart being added has any date conflict with the ones already attached
245 --
246 -- IN parameters:
247 -- 1. p_name: name of the rate chart
248 -- 2. p_lane_id: lane id
249 --
250 -- OUT parameters:
251 -- 1. x_status: error status, -1 if no error
252 -- 2. x_error_msg: error message if any
253 --
254 -- RETURN true if some other chart date overlap, false if no overlap
255 ------------------------------------------------------------------
256 FUNCTION VERIFY_OVERLAPPING_DATE(p_name IN VARCHAR2,
257 p_lane_id IN NUMBER,
258 x_status OUT NOCOPY NUMBER,
259 x_error_msg OUT NOCOPY VARCHAR2) RETURN BOOLEAN;
260
261 ------------------------------------------------------------------
262 -- FUNCTION FIND_TYPE
263 --
264 -- Purpose: find if the lane service or lane commodity already have the service level or commodity
265 --
266 -- IN parameters:
267 -- 1. p_type: SERVICE_LEVEL or COMMODITY
268 -- 2. p_value: value to be found
269 -- 3. p_lane_id: lane id
270 --
271 -- OUT parameters:
272 -- 1. x_status: error status, -1 if no error
273 -- 2. x_error_msg: error message if any
274 --
275 -- RETURN true if type is found
276 ------------------------------------------------------------------
277 FUNCTION FIND_TYPE(p_type IN VARCHAR2,
278 p_value IN VARCHAR2,
279 p_lane_id IN NUMBER,
280 p_line_number IN NUMBER,
281 x_status OUT NOCOPY NUMBER,
282 x_error_msg OUT NOCOPY VARCHAR2) RETURN BOOLEAN;
283
284 -------------------------------------------------------------------------
285 -- PROCEDURE INSERT_LANE_TABLES
286 --
287 -- Purpose: insert all lane tables
288 --
289 -- IN parameters:
290 -- 1. p_lane_tbl: lane pl/sql table
291 -- 2. p_lane_rate_chart_tbl: lane rate chart pl/sql table
292 -- 3. p_lane_commodity_tbl: lane commodity pl/sql table
293 -- 4. p_lane_service_tbl: lane service pl/sql table
294 --
295 -- OUT parameters:
296 -- 1. x_status: status of the error -1 when no error
297 -- 2. x_error_msg: error msg if any errors
298 -------------------------------------------------------------------------
299 PROCEDURE INSERT_LANE_TABLES(p_lane_tbl IN OUT NOCOPY lane_tbl,
300 p_lane_rate_chart_tbl IN OUT NOCOPY lane_rate_chart_tbl,
301 p_lane_commodity_tbl IN OUT NOCOPY lane_commodity_tbl,
302 p_lane_service_tbl IN OUT NOCOPY lane_service_tbl,
303 x_status OUT NOCOPY NUMBER,
304 x_error_msg OUT NOCOPY VARCHAR2);
305
306 -------------------------------------------------------------------------
307 -- PROCEDURE INSERT_SCHEDULES
308 --
309 -- Purpose: insert schedules
310 --
311 -- IN parameters:
312 -- 1. p_schedule_tbl: schedules pl/sql table
313 --
314 -- OUT parameters:
315 -- 1. x_status: status of the error -1 when no error
316 -- 2. x_error_msg: error msg if any errors
317 -------------------------------------------------------------------------
318 PROCEDURE INSERT_SCHEDULES(p_schedule_tbl IN OUT NOCOPY schedule_tbl,
319 x_status OUT NOCOPY NUMBER,
320 x_error_msg OUT NOCOPY VARCHAR2);
321
322 -------------------------------------------------------------------------------
323 --
324 -- PROCEDURE: Check_Lanes
325 -- Purpose: check if the rate chart has lanes attatched to it or not
326 --
327 -- IN Parameter:
328 -- 1. p_pricelist_id: list header id
329 --
330 -- OUT Parameters:
331 -- 1. x_status: status, -1 if no lanes attached, 2 otherwise
332 -- 2. x_error_msg: error message if any
333 --
334 -- Returns -1 if there are no lanes attached to the
335 -- rate chart, 2 otherwise.
336 -------------------------------------------------------------------------------
337 PROCEDURE Check_Lanes(p_pricelist_id IN NUMBER,
338 x_status OUT NOCOPY NUMBER,
339 x_error_msg OUT NOCOPY VARCHAR2);
340
341 -------------------------------------------------------------------------
342 -- PROCEDURE INSERT_PRC_PARAMETERS
343 --
344 -- Purpose: insert the prc parameter row
345 --
346 -- IN parameters:
347 -- 1. p_prc_parameter_tbl: pricing parameter pl/sql table
348 --
349 -- OUT parameters:
350 -- 1. x_status: status of the error -1 when no error
351 -- 2. x_error_msg: error msg if any errors
352 -------------------------------------------------------------------------
353 PROCEDURE INSERT_PRC_PARAMETERS(p_prc_parameter_tbl IN OUT NOCOPY prc_parameter_tbl,
354 x_status OUT NOCOPY NUMBER,
355 x_error_msg OUT NOCOPY VARCHAR2);
356
357 -------------------------------------------------------------------------
358 -- PROCEDURE UPDATE_PRC_PARAMETER
359 --
360 -- Purpose: update pricing parameter line
361 --
362 -- IN parameters:
363 -- 1. p_prc_parameter_tbl: pricing parameter pl/sql table
364 --
365 -- OUT parameters:
366 -- 1. x_status: status of the error -1 when no error
367 -- 2. x_error_msg: error msg if any errors
368 -------------------------------------------------------------------------
369 PROCEDURE UPDATE_PRC_PARAMETER( p_prc_parameter_tbl IN prc_parameter_tbl,
370 x_status OUT NOCOPY NUMBER,
371 x_error_msg OUT NOCOPY VARCHAR2);
372
373 ------------------------------------------------------------------------
374 -- FUNCTION CHECK_EXISTING_LOAD
375 --
376 -- Purpose: check existing entries in the fte_* tables
377 --
378 -- IN parameters:
379 -- 1. p_id: id to used for validating
380 -- 2. p_table: table to validate
381 -- 3. p_code: codes to use for matching
382 --
383 -- OUT parameters:
384 -- 1. x_status: status of the error -1 when no error
385 -- 2. x_error_msg: error msg if any errors
386 ------------------------------------------------------------------------
387 FUNCTION CHECK_EXISTING_LOAD( p_id IN NUMBER,
388 p_table IN VARCHAR2,
389 p_code IN VARCHAR2,
390 p_line_number IN NUMBER,
391 x_status OUT NOCOPY NUMBER,
392 x_error_msg OUT NOCOPY VARCHAR2) RETURN BOOLEAN;
393
394 END FTE_LANE_PKG;
[Home] [Help]