[Home] [Help]
PACKAGE BODY: APPS.FTE_SERVICES_UI_WRAPPER
Source
1 PACKAGE BODY FTE_SERVICES_UI_WRAPPER AS
2 /* $Header: FTEUIWPB.pls 120.7 2005/08/22 03:49:50 pkaliyam noship $ */
3 -------------------------------------------------------------------------- --
4 -- --
5 -- NAME: FTE_SERVICES_UI_WRAPPER --
6 -- TYPE: PACKAGE BODY --
7 -- DESCRIPTION: Contains wrapper procedures for UI loader --
8 -- --
9 -------------------------------------------------------------------------- --
10
11 G_PKG_NAME CONSTANT VARCHAR2(50) := 'FTE_SERVICES_UI_WRAPPER';
12
13 --------------------------------------------------------
14 -- PROCEDURE RATE_CHART_WRAPPER
15 --
16 -- Purpose: convert UI data into pl/sql tables and insert into the database
17 --
18 -- IN parameters:
19 -- 1. p_header_table: header info table
20 -- 2. p_line_table: line info table
21 -- 3. p_break_table: break info table
22 -- 4. p_chart_type: the chart type (RC or MOD)
23 --
24 -- OUT parameters:
25 -- 1. x_status: status, -1 means no error
26 -- 2. x_error_msg: error message if any
27 --------------------------------------------------------
28 PROCEDURE RATE_CHART_WRAPPER( p_header_table IN rate_chart_header_table,
29 p_line_table IN rate_chart_line_table,
30 p_break_table IN rate_chart_break_table,
31 p_chart_type IN VARCHAR2,
32 x_status OUT NOCOPY NUMBER,
33 x_error_msg OUT NOCOPY VARCHAR2) IS
34
35 l_block_header FTE_BULKLOAD_PKG.block_header_tbl;
36 l_block_data FTE_BULKLOAD_PKG.block_data_tbl;
37 l_aj_block_data FTE_BULKLOAD_PKG.block_data_tbl;
38 l_line_block_data FTE_BULKLOAD_PKG.block_data_tbl;
39 l_break_block_data FTE_BULKLOAD_PKG.block_data_tbl;
40 l_offset NUMBER := 0;
41 l_count NUMBER := 0;
42 l_break_count NUMBER := 0;
43 l_line_number NUMBER := 0;
44 l_context VARCHAR2(20) := 'LOGISTICS';
45
46 BEGIN
47
48 x_status := -1;
49
50 IF ( WSH_DEBUG_SV.is_debug_enabled ) THEN
51 FTE_UTIL_PKG.Init_Debug(1);
52 END IF;
53
54 FTE_RATE_CHART_LOADER.INIT_QP_TABLES;
55 FTE_RATE_CHART_PKG.RESET_ALL;
56
57 FOR i IN p_header_table.FIRST..p_header_table.LAST LOOP
58
59 l_block_data(1)('ACTION') := 'UPDATE';
60 l_block_data(1)('CARRIER_ID') := p_header_table(i).carrier_id;
61 l_block_data(1)('CURRENCY') := p_header_table(i).currency_code;
62 l_block_data(1)('START_DATE') := to_char(p_header_table(i).start_date_active, FTE_BULKLOAD_PKG.G_DATE_FORMAT);
63 l_block_data(1)('END_DATE') := to_char(p_header_table(i).end_date_active, FTE_BULKLOAD_PKG.G_DATE_FORMAT);
64 l_block_data(1)('DESCRIPTION') := p_header_table(i).description;
65 l_block_data(1)('REPLACED_RATE_CHART') := '';
66 l_block_data(1)('LIST_HEADER_ID') := p_header_table(i).list_header_id;
67
68 IF ('RC' = p_chart_type) THEN
69 l_block_data(1)('RATE_CHART_NAME') := p_header_table(i).chart_name;
70 ELSE
71 l_block_data(1)('CHARGES_DISCOUNTS_NAME') := p_header_table(i).chart_name;
72 END IF;
73
74 g_list_header_id := p_header_table(i).list_header_id;
75
76 IF ('RC' = p_chart_type) THEN
77 FTE_RATE_CHART_LOADER.PROCESS_RATE_CHART(p_block_header => l_block_header,
78 p_block_data => l_block_data,
79 p_line_number => NULL,
80 p_validate_column => FALSE,
81 p_validate => FALSE,
82 x_status => x_status,
83 x_error_msg => x_error_msg);
84 ELSE
85 FTE_RATE_CHART_LOADER.PROCESS_CHARGES_DISCOUNTS(p_block_header => l_block_header,
86 p_block_data => l_block_data,
87 p_line_number => NULL,
88 p_validate_column => FALSE,
89 p_validate => FALSE,
90 x_status => x_status,
91 x_error_msg => x_error_msg);
92 END IF;
93
94 IF (x_status <> -1) THEN
95 FTE_RATE_CHART_PKG.RESET_ALL;
96 FTE_RATE_CHART_LOADER.INIT_QP_TABLES;
97 RETURN;
98 END IF;
99
100 FOR j IN p_line_table.FIRST..p_line_table.LAST LOOP
101 l_count := l_count+1;
102 l_line_number := l_count + l_break_count;
103 l_line_block_data(j)('ACTION') := 'ADD';
104 l_line_block_data(j)('LINE_NUMBER') := l_line_number;
105 l_line_block_data(j)('DESCRIPTION') := p_line_table(j).description;
106 l_line_block_data(j)('UOM') := p_line_table(j).rate_basis_uom;
107
108 IF ('RC' = p_chart_type) THEN
109 l_line_block_data(j)('RATE') := p_line_table(j).rate;
110 l_line_block_data(j)('RATE_BREAK_TYPE') := p_line_table(j).break_type;
111 l_line_block_data(j)('VOLUME_TYPE') := 'TOTAL_QUANTITY';
112 l_line_block_data(j)('RATE_TYPE') := p_line_table(j).rate_type;
113 ELSE
114 l_line_block_data(j)('TYPE') := p_line_table(j).type;
115 l_line_block_data(j)('SUBTYPE') := p_line_table(j).subtype;
116 IF (p_line_table(j).rate_type = 'PER_UOM') THEN
117 l_line_block_data(j)('RATE_PER_UOM') := p_line_table(j).rate;
118 ELSIF (p_line_table(j).rate_type = '%') THEN
119 l_line_block_data(j)('PERCENTAGE') := p_line_table(j).rate;
120 ELSE
121 l_line_block_data(j)('FIXED_RATE') := p_line_table(j).rate;
122 END IF;
123 END IF;
124
125 IF ('RC' = p_chart_type) THEN
126 FTE_RATE_CHART_LOADER.PROCESS_RATE_LINE (p_block_header => l_block_header,
127 p_block_data => l_line_block_data,
128 p_line_number => NULL,
129 p_validate_column => FALSE,
130 p_validate => FALSE,
131 x_status => x_status,
132 x_error_msg => x_error_msg);
133 ELSE
134 FTE_RATE_CHART_LOADER.PROCESS_CHARGES_DISCOUNTS_LINE (p_block_header => l_block_header,
135 p_block_data => l_line_block_data,
136 p_line_number => NULL,
137 p_validate_column => FALSE,
138 p_validate => FALSE,
139 x_status => x_status,
140 x_error_msg => x_error_msg);
141 END IF;
142
143 IF (x_status <> -1) THEN
144 FTE_RATE_CHART_PKG.RESET_ALL;
145 FTE_RATE_CHART_LOADER.INIT_QP_TABLES;
146 RETURN;
147 END IF;
148
149 IF (p_line_table(j).break_type IS NOT NULL) THEN
150 l_break_block_data.DELETE;
151 FOR k IN p_break_table.FIRST..p_break_table.LAST LOOP
152 IF (p_break_table(k).break_header_index = p_line_table(j).line_num) THEN
153 l_break_count := l_break_count + 1;
154 l_break_block_data(l_break_block_data.COUNT+1)('ACTION') := 'ADD';
155 l_break_block_data(l_break_block_data.COUNT)('LINE_NUMBER') := l_count+l_break_count;
156 l_break_block_data(l_break_block_data.COUNT)('LOWER_LIMIT') := p_break_table(k).lower;
157 l_break_block_data(l_break_block_data.COUNT)('UPPER_LIMIT') := p_break_table(k).upper;
158 l_break_block_data(l_break_block_data.COUNT)('RATE') := p_break_table(k).rate;
159 l_break_block_data(l_break_block_data.COUNT)('RATE_TYPE') := p_break_table(k).rate_type;
160 END IF;
161 END LOOP;
162 FTE_RATE_CHART_LOADER.PROCESS_RATE_BREAK(p_block_header => l_block_header,
163 p_block_data => l_break_block_data,
164 p_line_number => NULL,
165 p_validate_column => FALSE,
166 p_validate => FALSE,
167 x_status => x_status,
168 x_error_msg => x_error_msg);
169 IF (x_status <> -1) THEN
170 FTE_RATE_CHART_PKG.RESET_ALL;
171 FTE_RATE_CHART_LOADER.INIT_QP_TABLES;
172 RETURN;
173 END IF;
174
175 END IF;
176
177 IF (p_line_table(j).origin_id IS NOT NULL) THEN
178 FTE_RATE_CHART_LOADER.ADD_ATTRIBUTE(p_pricing_attribute => 'ORIGIN_ZONE',
179 p_attr_value_from => p_line_table(j).origin_id,
180 p_line_number => l_line_number,
181 x_status => x_status,
182 x_error_msg => x_error_msg);
183 END IF;
184
185 IF (x_status <> -1) THEN
186 FTE_RATE_CHART_PKG.RESET_ALL;
187 FTE_RATE_CHART_LOADER.INIT_QP_TABLES;
188 RETURN;
189 END IF;
190
191 IF (p_line_table(j).dest_id IS NOT NULL) THEN
192 FTE_RATE_CHART_LOADER.ADD_ATTRIBUTE(p_pricing_attribute => 'DESTINATION_ZONE',
193 p_attr_value_from => p_line_table(j).dest_id,
194 p_line_number => l_line_number,
195 x_status => x_status,
196 x_error_msg => x_error_msg);
197 END IF;
198
199 IF (x_status <> -1) THEN
200 FTE_RATE_CHART_PKG.RESET_ALL;
201 FTE_RATE_CHART_LOADER.INIT_QP_TABLES;
202 RETURN;
203 END IF;
204
205 IF (p_line_table(j).catg_id IS NOT NULL) THEN
206 FTE_RATE_CHART_LOADER.ADD_ATTRIBUTE(p_pricing_attribute => 'COMMODITY',
207 p_attr_value_from => p_line_table(j).catg_id,
208 p_line_number => l_line_number,
209 x_status => x_status,
210 x_error_msg => x_error_msg);
211 END IF;
212
213 IF (x_status <> -1) THEN
214 FTE_RATE_CHART_PKG.RESET_ALL;
215 FTE_RATE_CHART_LOADER.INIT_QP_TABLES;
216 RETURN;
217 END IF;
218
219 IF (p_line_table(j).service_code IS NOT NULL) THEN
220 FTE_RATE_CHART_LOADER.ADD_ATTRIBUTE(p_pricing_attribute => 'SERVICE_LEVEL',
221 p_attr_value_from => p_line_table(j).service_code,
222 p_line_number => l_line_number,
223 x_status => x_status,
224 x_error_msg => x_error_msg);
225 END IF;
226
227 IF (x_status <> -1) THEN
228 FTE_RATE_CHART_PKG.RESET_ALL;
229 FTE_RATE_CHART_LOADER.INIT_QP_TABLES;
230 RETURN;
231 END IF;
232
233 IF (p_line_table(j).multi_flag IS NOT NULL) THEN
234 FTE_RATE_CHART_LOADER.ADD_ATTRIBUTE(p_pricing_attribute => 'PARCEL_MULTIPIECE_FLAG',
235 p_attr_value_from => p_line_table(j).multi_flag,
236 p_line_number => l_line_number,
237 x_status => x_status,
238 x_error_msg => x_error_msg);
239 END IF;
240
241 IF (x_status <> -1) THEN
242 FTE_RATE_CHART_PKG.RESET_ALL;
243 FTE_RATE_CHART_LOADER.INIT_QP_TABLES;
244 RETURN;
245 END IF;
246
247 l_line_block_data.DELETE;
248 l_break_block_data.DELETE;
249 END LOOP;
250
251 l_block_data.DELETE;
252 END LOOP;
253
254 FTE_RATE_CHART_LOADER.SUBMIT_QP_PROCESS(
255 x_status => x_status,
256 x_error_msg => x_error_msg);
257
258 FTE_RATE_CHART_PKG.RESET_ALL;
259 FTE_RATE_CHART_LOADER.INIT_QP_TABLES;
260
261 IF (x_status = -1) THEN
262 commit;
263 ELSE
264 rollback;
265 END IF;
266
267 FTE_RATE_CHART_PKG.RESET_ALL;
268 FTE_RATE_CHART_LOADER.INIT_QP_TABLES;
269 END RATE_CHART_WRAPPER;
270
271
272 --------------------------------------------------------
273 -- PROCEDURE TL_SURCHARGE_WRAPPER
274 --
275 -- Purpose: convert UI data into pl/sql tables and insert into the database
276 --
277 -- IN parameters:
278 -- 1. p_header_table: header info table
279 -- 2. p_tl_line_table: line info table
280 -- 3. p_break_table: break info table
281 --
282 -- OUT parameters:
283 -- 1. x_status: status, -1 means no error
284 -- 2. x_error_msg: error message if any
285 --------------------------------------------------------
286 PROCEDURE TL_SURCHARGE_WRAPPER( p_header_table IN rate_chart_header_table,
287 p_tl_line_table IN tl_line_table,
288 p_break_table IN rate_chart_break_table,
289 p_action IN VARCHAR2,
290 x_status OUT NOCOPY NUMBER,
291 x_error_msg OUT NOCOPY VARCHAR2) IS
292
293 l_block_header FTE_BULKLOAD_PKG.block_header_tbl;
294 l_block_data FTE_BULKLOAD_PKG.block_data_tbl;
295
296 BEGIN
297 x_status := -1;
298 IF ( WSH_DEBUG_SV.is_debug_enabled ) THEN
299 FTE_UTIL_PKG.Init_Debug(1);
300 END IF;
301
302 l_block_data(1)('ACTION') := p_action;
303 l_block_data(1)('TYPE') := 'C';
304 l_block_data(1)('CARRIER_ID') := p_header_table(p_header_table.FIRST).carrier_id;
305 l_block_data(1)('SERVICE_CODE') := p_header_table(p_header_table.FIRST).service_level;
306 l_block_data(1)('CURRENCY') := p_header_table(p_header_table.FIRST).currency_code;
307 l_block_data(1)('START_DATE') := to_char(p_header_table(p_header_table.FIRST).start_date_active, FTE_BULKLOAD_PKG.G_DATE_FORMAT);
308 l_block_data(1)('END_DATE') := to_char(p_header_table(p_header_table.FIRST).end_date_active, FTE_BULKLOAD_PKG.G_DATE_FORMAT);
309
310 FOR i IN p_tl_line_table.FIRST..p_tl_line_table.LAST LOOP
311 IF (p_tl_line_table(i).type = 'C_ORIGIN_SURCHRG') THEN
312 l_block_data(l_block_data.COUNT+1)('ACTION') := 'ADD';
313 l_block_data(l_block_data.COUNT)('TYPE') := 'O';
314 l_block_data(l_block_data.COUNT)('REGION_CODE') := p_tl_line_table(i).region_code;
315 l_block_data(l_block_data.COUNT)('SURCHARGES') := p_tl_line_table(i).charge;
316 ELSIF (p_tl_line_table(i).type = 'C_DESTINATION_SURCHRG') THEN
317 l_block_data(l_block_data.COUNT+1)('ACTION') := 'ADD';
318 l_block_data(l_block_data.COUNT)('TYPE') := 'D';
319 l_block_data(l_block_data.COUNT)('REGION_CODE') := p_tl_line_table(i).region_code;
320 l_block_data(l_block_data.COUNT)('SURCHARGES') := p_tl_line_table(i).charge;
321 ELSIF (p_tl_line_table(i).type = 'C_FUEL_CHRG') THEN
322 l_block_data(l_block_data.COUNT+1)('ACTION') := 'ADD';
323 l_block_data(l_block_data.COUNT)('TYPE') := 'F';
324 l_block_data(l_block_data.COUNT)('SURCHARGES') := p_tl_line_table(i).charge;
325 ELSIF (p_tl_line_table(i).type = 'C_STOP_OFF_CHRG') THEN
326 l_block_data(1)('NUMBER_OF_FREE_STOPS') := p_tl_line_table(i).free_stops;
327 l_block_data(1)('FIRST_ADD_STOP_OFF_CHARGES') := p_tl_line_table(i).first_stop;
328 l_block_data(1)('SECOND_ADD_STOP_OFF_CHARGES') := p_tl_line_table(i).second_stop;
329 l_block_data(1)('THIRD_ADD_STOP_OFF_CHARGES') := p_tl_line_table(i).third_stop;
330 l_block_data(1)('FOURTH_ADD_STOP_OFF_CHARGES') := p_tl_line_table(i).fourth_stop;
331 l_block_data(1)('FIFTH_ADD_STOP_OFF_CHARGES') := p_tl_line_table(i).fifth_stop;
332 l_block_data(1)('ADDITIONAL_STOP_CHARGES') := p_tl_line_table(i).add_stops;
333 ELSIF (p_tl_line_table(i).type = 'C_OUT_OF_ROUTE_CHRG') THEN
334 l_block_data(1)('OUT_OF_ROUTE_CHARGES') := p_tl_line_table(i).charge;
335 l_block_data(1)('OUT_OF_ROUTE_CHARGE_BASIS_UOM'):= p_tl_line_table(i).basis_uom_code;
336 ELSIF (p_tl_line_table(i).type = 'C_HANDLING_WEIGHT_CHRG') THEN
337 l_block_data(1)('HANDLING_CHARGES') := p_tl_line_table(i).charge;
338 l_block_data(1)('MINIMUM_HANDLING_CHARGES') := p_tl_line_table(i).min_charge;
339 l_block_data(1)('BASIS_FOR_HANDLING_LOADING_UNLOADING_CHARGES') := p_tl_line_table(i).basis;
340 l_block_data(1)('UOM_FOR_HANDLING_LOADING_UNLOADING_CHARGE_BASIS') := p_tl_line_table(i).basis_uom_code;
341 ELSIF (p_tl_line_table(i).type = 'C_LOADING_WEIGHT_CHRG') THEN
342 l_block_data(1)('LOADING_CHARGES') := p_tl_line_table(i).charge;
343 l_block_data(1)('MINIMUM_LOADING_CHARGES') := p_tl_line_table(i).min_charge;
344 l_block_data(1)('BASIS_FOR_HANDLING_LOADING_UNLOADING_CHARGES') := p_tl_line_table(i).basis;
345 l_block_data(1)('UOM_FOR_HANDLING_LOADING_UNLOADING_CHARGE_BASIS') := p_tl_line_table(i).basis_uom_code;
346 ELSIF (p_tl_line_table(i).type = 'C_AST_LOADING_WEIGHT_CHRG') THEN
347 l_block_data(1)('ASSISTED_LOADING_CHARGES') := p_tl_line_table(i).charge;
348 l_block_data(1)('MINIMUM_ASSISTED_LOADING_CHARGES') := p_tl_line_table(i).min_charge;
349 l_block_data(1)('BASIS_FOR_HANDLING_LOADING_UNLOADING_CHARGES') := p_tl_line_table(i).basis;
350 l_block_data(1)('UOM_FOR_HANDLING_LOADING_UNLOADING_CHARGE_BASIS') := p_tl_line_table(i).basis_uom_code;
351 ELSIF (p_tl_line_table(i).type = 'C_UNLOADING_WEIGHT_CHRG') THEN
352 l_block_data(1)('UNLOADING_CHARGES') := p_tl_line_table(i).charge;
353 l_block_data(1)('MINIMUM_UNLOADING_CHARGES') := p_tl_line_table(i).min_charge;
354 l_block_data(1)('BASIS_FOR_HANDLING_LOADING_UNLOADING_CHARGES') := p_tl_line_table(i).basis;
355 l_block_data(1)('UOM_FOR_HANDLING_LOADING_UNLOADING_CHARGE_BASIS') := p_tl_line_table(i).basis_uom_code;
356 ELSIF (p_tl_line_table(i).type = 'C_AST_UNLOADING_WEIGHT_CHRG') THEN
357 l_block_data(1)('ASSISTED_UNLOADING_CHARGES') := p_tl_line_table(i).charge;
358 l_block_data(1)('MINIMUM_ASSISTED_UNLOADING_CHARGES') := p_tl_line_table(i).min_charge;
359 l_block_data(1)('BASIS_FOR_HANDLING_LOADING_UNLOADING_CHARGES') := p_tl_line_table(i).basis;
360 l_block_data(1)('UOM_FOR_HANDLING_LOADING_UNLOADING_CHARGE_BASIS') := p_tl_line_table(i).basis_uom_code;
361 ELSIF (p_tl_line_table(i).type = 'C_CONTINUOUS_MOVE_DISCOUNT') THEN
362 l_block_data(1)('CONTINUOUS_MOVE_DISCOUNT_PERCENTAGE') := p_tl_line_table(i).charge;
363 ELSIF (p_tl_line_table(i).type = 'C_WEEKDAY_LAYOVER_CHRG') THEN
364 l_block_data(1)('WEEKDAY_LAYOVER_CHARGES') := p_tl_line_table(i).charge;
365 ELSIF (p_tl_line_table(i).type = 'C_WEEKEND_LAYOVER_CHRG') THEN
366 FOR j IN p_break_table.FIRST..p_break_table.LAST LOOP
367 l_block_data(l_block_data.COUNT+1)('ACTION') := 'ADD';
368 l_block_data(l_block_data.COUNT)('TYPE') := 'B';
369 l_block_data(l_block_data.COUNT)('DISTANCE_UOM_FOR_WEEKEND_LAYOVER_CHARGES') := p_tl_line_table(i).basis_uom_code;
370 l_block_data(l_block_data.COUNT)('WEEKEND_LAYOVER_DISTANCE_BREAK') := p_break_table(j).upper;
371 l_block_data(l_block_data.COUNT)('CHARGES') := p_break_table(j).rate;
372 END LOOP;
373 END IF;
374
375 END LOOP;
376
377 FTE_TL_LOADER.PROCESS_TL_SURCHARGES(p_block_header => l_block_header,
378 p_block_data => l_block_data,
379 p_line_number => NULL,
380 p_doValidate => FALSE,
381 x_status => x_status,
382 x_error_msg => x_error_msg);
383
384 l_block_data.DELETE;
385
386 FTE_TL_LOADER.RESET_ALL;
387
388 IF (x_status = -1) THEN
389 commit;
390 ELSE
391 rollback;
392 END IF;
393
394 END TL_SURCHARGE_WRAPPER;
395
396
397 --------------------------------------------------------
398 -- PROCEDURE EDIT_TL_SERVICES
399 --
400 -- Purpose: convert UI data into pl/sql tables and insert into the database
401 --
402 -- IN parameters:
403 -- 1. p_init_msg_list:
404 -- 2. p_transaction_type:
405 -- 3. p_lane_table: lane table info
406 -- 4. p_rate_chart_header_table: rate chart header info
407 -- 5. p_rate_chart_line_table: rate chart line info
408 --
409 -- OUT parameters:
410 -- 1. x_status:
411 -- 2. x_error_msg:
412 --------------------------------------------------------
413 PROCEDURE Edit_TL_Services(P_INIT_MSG_LIST IN VARCHAR2,
414 P_TRANSACTION_TYPE IN VARCHAR2,
415 P_LANE_TABLE IN lane_table,
416 P_RATE_CHART_HEADER_TABLE IN rate_chart_header_table,
417 P_RATE_CHART_LINE_TABLE IN rate_chart_line_table,
418 X_STATUS OUT NOCOPY NUMBER,
419 X_ERROR_MSG OUT NOCOPY VARCHAR2) IS
420
421 l_lane_tbl FTE_LANE_PKG.lane_tbl;
422 l_lane_rate_chart_tbl FTE_LANE_PKG.lane_rate_chart_tbl;
423 l_lane_commodity_tbl FTE_LANE_PKG.lane_commodity_tbl;
424 l_lane_service_tbl FTE_LANE_PKG.lane_service_tbl;
425
426 l_lane_id NUMBER;
427
428 l_block_header FTE_BULKLOAD_PKG.block_header_tbl;
429 l_rate_line_data FTE_BULKLOAD_PKG.block_data_tbl;
430 l_postal_code_from VARCHAR2(30) := NULL;
431 l_postal_code_to VARCHAR2(30) := NULL;
432 l_postal_code_from_num NUMBER := NULL;
433 l_postal_code_to_num NUMBER := NULL;
434
435 l_action VARCHAR2(10);
436
437 BEGIN
438 x_status := -1;
439
440 IF ( WSH_DEBUG_SV.is_debug_enabled ) THEN
441 FTE_UTIL_PKG.Init_Debug(1);
442 END IF;
443
444 FOR i IN P_LANE_TABLE.FIRST..P_LANE_TABLE.LAST LOOP
445
446 IF (p_lane_table(i).LANE_ID IS NOT NULL) THEN
447 l_lane_id := p_lane_table(i).LANE_ID;
448 ELSE
449 l_lane_id := FTE_LANE_PKG.GET_NEXT_LANE_ID;
450 END IF;
451
452 IF (P_TRANSACTION_TYPE = 'CREATE') THEN
453 l_lane_tbl(i).action := 'ADD';
454
455 l_action := 'ADD';
456
457 ELSE
458 l_action := 'UPDATE';
459 l_lane_tbl(i).action := 'UPDATE';
460
461 END IF;
462
463 l_lane_tbl(i).lane_id := l_lane_id;
464 l_lane_tbl(i).carrier_id := P_LANE_TABLE(i).carrier_id;
465 l_lane_tbl(i).origin_id := P_LANE_TABLE(i).origin_id;
466 l_lane_tbl(i).destination_id := P_LANE_TABLE(i).destination_id;
467 l_lane_tbl(i).effective_date := P_LANE_TABLE(i).START_DATE_ACTIVE;
468 l_lane_tbl(i).expiry_date := P_LANE_TABLE(i).END_DATE_ACTIVE;
469 l_lane_tbl(i).pricelist_view_flag := 'Y';
470 l_lane_tbl(i).editable_flag := 'N';
471 l_lane_tbl(i).lane_number := P_LANE_TABLE(i).SERVICE_NUMBER;
472 l_lane_tbl(i).mode_of_transportation_code := P_LANE_TABLE(i).TRANSPORT_MODE;
473 l_lane_tbl(i).service_type_code := P_LANE_TABLE(i).service_type_code;
474 l_lane_tbl(i).container_all_flag := FALSE;
475 l_lane_tbl(i).basis_flag := FALSE;
476 l_lane_tbl(i).distance := null;
477 l_lane_tbl(i).distance_uom := null;
478 l_lane_tbl(i).transit_time := null;
479 l_lane_tbl(i).transit_time_uom := null;
480 l_lane_tbl(i).basis := null;
481 l_lane_tbl(i).comm_fc_class_code := null;
482 l_lane_tbl(i).additional_instructions := null;
483 l_lane_tbl(i).special_handling := null;
484 l_lane_tbl(i).tariff_name := null;
485 l_lane_tbl(i).commodity_catg_id := null;
486 l_lane_tbl(i).equipment_type_code := null;
487 l_lane_tbl(i).line_number := null;
488 IF (p_transaction_type = 'CREATE') THEN
489 l_lane_tbl(i).lane_type := 'HOLD_'||upper(P_RATE_CHART_HEADER_TABLE(p_rate_chart_header_table.FIRST).CHART_NAME);
490
491 -- Initialize l_lane_service_tbl
492 l_lane_service_tbl(i).lane_id := l_lane_id;
493 l_lane_service_tbl(i).service_code := P_LANE_TABLE(i).SERVICE_TYPE_CODE;
494 l_lane_service_tbl(i).lane_service_id := FTE_LANE_PKG.get_next_lane_service_id;
495
496 END IF;
497
498 BEGIN
499 SELECT postal_code_from, postal_code_to
500 INTO l_postal_code_from, l_postal_code_to
501 FROM wsh_regions_tl
502 WHERE region_id = l_lane_tbl(i).origin_id
503 AND language = USERENV('LANG');
504 EXCEPTION
505 WHEN OTHERS THEN
506 l_lane_tbl.DELETE;
507 l_lane_rate_chart_tbl.DELETE;
508 l_lane_commodity_tbl.DELETE;
509 l_lane_service_tbl.DELETE;
510 x_status := 2;
511 x_error_msg := FTE_UTIL_PKG.GET_MSG(P_Name => 'FTE_CAT_REGION_UNKNOWN',
512 p_tokens => STRINGARRAY('REGION_NAME'),
513 p_values => STRINGARRAY(l_postal_code_from || ' ' ||
514 l_postal_code_to));
515
516 RETURN;
517 END;
518
519 --inserting the region in wsh_zone_regions
520 IF (FTE_REGION_ZONE_LOADER.INSERT_PARTY_REGION(p_region_id => l_lane_tbl(i).origin_id,
521 p_parent_region_id => l_lane_tbl(i).origin_id,
522 p_supplier_id => -1,
523 p_validate_flag => TRUE,
524 p_postal_code_from => l_postal_code_from_num,
525 p_postal_code_to => l_postal_code_to_num) = -1) THEN
526
527 l_lane_tbl.DELETE;
528 l_lane_rate_chart_tbl.DELETE;
529 l_lane_commodity_tbl.DELETE;
530 l_lane_service_tbl.DELETE;
531 x_status := 2;
532 x_error_msg := FTE_UTIL_PKG.GET_MSG(P_Name => 'FTE_CAT_REGION_UNKNOWN',
533 p_tokens => STRINGARRAY('REGION_NAME'),
534 p_values => STRINGARRAY(l_postal_code_from || ' ' ||
535 l_postal_code_to));
536
537
538 RETURN;
539 END IF;
540
541 BEGIN
542 SELECT postal_code_from, postal_code_to
543 INTO l_postal_code_from, l_postal_code_to
544 FROM wsh_regions_tl
545 WHERE region_id = l_lane_tbl(i).destination_id
546 AND language = USERENV('LANG');
547 EXCEPTION
548 WHEN OTHERS THEN
549 l_lane_tbl.DELETE;
550 l_lane_rate_chart_tbl.DELETE;
551 l_lane_commodity_tbl.DELETE;
552 l_lane_service_tbl.DELETE;
553 x_status := 2;
554 x_error_msg := FTE_UTIL_PKG.GET_MSG(P_Name => 'FTE_CAT_REGION_UNKNOWN',
555 p_tokens => STRINGARRAY('REGION_NAME'),
556 p_values => STRINGARRAY(l_postal_code_from || ' ' ||
557 l_postal_code_to));
558 RETURN;
559 END;
560
561 --inserting the region in wsh_zone_regions
562 IF (FTE_REGION_ZONE_LOADER.INSERT_PARTY_REGION(p_region_id => l_lane_tbl(i).destination_id,
563 p_parent_region_id => l_lane_tbl(i).destination_id,
564 p_supplier_id => -1,
565 p_validate_flag => TRUE,
566 p_postal_code_from => l_postal_code_from_num,
567 p_postal_code_to => l_postal_code_to_num) = -1) THEN
568
569 l_lane_tbl.DELETE;
570 l_lane_rate_chart_tbl.DELETE;
571 l_lane_commodity_tbl.DELETE;
572 l_lane_service_tbl.DELETE;
573 x_status := 2;
574 x_error_msg := FTE_UTIL_PKG.GET_MSG(P_Name => 'FTE_CAT_REGION_UNKNOWN',
575 p_tokens => STRINGARRAY('REGION_NAME'),
576 p_values => STRINGARRAY(l_postal_code_from || ' ' ||
577 l_postal_code_to));
578 RETURN;
579 END IF;
580
581 END LOOP;
582
583 FTE_LANE_PKG.insert_lane_tables(p_lane_tbl => l_lane_tbl,
584 p_lane_rate_chart_tbl => l_lane_rate_chart_tbl,
585 p_lane_commodity_tbl => l_lane_commodity_tbl,
586 p_lane_service_tbl => l_lane_service_tbl,
587 x_status => x_status,
588 x_error_msg => x_error_msg);
589
590
591 IF (x_status <> -1) THEN
592 l_lane_tbl.DELETE;
593 l_lane_rate_chart_tbl.DELETE;
594 l_lane_commodity_tbl.DELETE;
595 l_lane_service_tbl.DELETE;
596 RETURN;
597 END IF;
598
599 -- rate chart processing
600 IF (p_rate_chart_line_table.COUNT > 0) THEN
601 FOR I IN P_RATE_CHART_LINE_TABLE.FIRST..P_RATE_CHART_LINE_TABLE.LAST LOOP
602 l_rate_line_data(l_rate_line_data.COUNT+1)('ACTION') := l_action;
603 l_rate_line_data(l_rate_line_data.COUNT)('CARRIER_ID') := P_RATE_CHART_HEADER_TABLE(p_rate_chart_header_table.FIRST).CARRIER_ID;
604 l_rate_line_data(l_rate_line_data.COUNT)('RATE_CHART_NAME') := P_RATE_CHART_HEADER_TABLE(p_rate_chart_header_table.FIRST).CHART_NAME;
605 l_rate_line_data(l_rate_line_data.COUNT)('CURRENCY') := P_RATE_CHART_HEADER_TABLE(p_rate_chart_header_table.FIRST).CURRENCY_CODE;
606 l_rate_line_data(l_rate_line_data.COUNT)('RATE_BASIS') := P_RATE_CHART_LINE_TABLE(I).RATE_BASIS;
607 l_rate_line_data(l_rate_line_data.COUNT)('RATE_BASIS_UOM_CODE') := P_RATE_CHART_LINE_TABLE(I).RATE_BASIS_UOM;
608 l_rate_line_data(l_rate_line_data.COUNT)('DISTANCE_TYPE') := P_RATE_CHART_LINE_TABLE(I).DIST_TYPE;
609 l_rate_line_data(l_rate_line_data.COUNT)('SERVICE_CODE') := P_RATE_CHART_HEADER_TABLE(p_rate_chart_header_table.FIRST).SERVICE_LEVEL;
610 l_rate_line_data(l_rate_line_data.COUNT)('VEHICLE_CODE') := P_RATE_CHART_LINE_TABLE(I).VEHICLE_TYPE;
611 l_rate_line_data(l_rate_line_data.COUNT)('RATE') := P_RATE_CHART_LINE_TABLE(I).RATE;
612 l_rate_line_data(l_rate_line_data.COUNT)('MINIMUM_CHARGE') := P_RATE_CHART_LINE_TABLE(I).MIN_CHARGE;
613 l_rate_line_data(l_rate_line_data.COUNT)('START_DATE') := to_char(P_RATE_CHART_LINE_TABLE(I).START_DATE, FTE_BULKLOAD_PKG.G_DATE_FORMAT);
614 l_rate_line_data(l_rate_line_data.COUNT)('END_DATE') := to_char(P_RATE_CHART_LINE_TABLE(I).END_DATE, FTE_BULKLOAD_PKG.G_DATE_FORMAT);
615
616 END LOOP;
617
618 FTE_TL_LOADER.PROCESS_TL_BASE_RATES(p_block_header => l_block_header,
619 p_block_data => l_rate_line_data,
620 p_line_number => null,
621 p_doValidate => FALSE,
622 x_status => x_status,
623 x_error_msg => x_error_msg);
624 END IF;
625
626 FTE_TL_LOADER.RESET_ALL;
627 l_lane_tbl.DELETE;
628 l_lane_rate_chart_tbl.DELETE;
629 l_lane_commodity_tbl.DELETE;
630 l_lane_service_tbl.DELETE;
631
632 l_rate_line_data.DELETE;
633
634 IF (x_status = -1) THEN
635 commit;
636 ELSE
637 rollback;
638 END IF;
639
640 END Edit_TL_Services;
641
642 END FTE_SERVICES_UI_WRAPPER;
643