[Home] [Help]
PACKAGE BODY: APPS.FTE_LANE_PKG
Source
1 PACKAGE BODY FTE_LANE_PKG AS
2 /* $Header: FTELANEB.pls 120.5 2005/08/19 00:16:32 pkaliyam noship $ */
3 ------------------------------------------------------------------------- --
4 -- --
5 -- NAME: FTE_LANE_PKG --
6 -- TYPE: PACKAGE BODY --
7 -- FUNCTIONS: GET_NEXT_SCHEDULE_ID --
8 -- GET_NEXT_PRC_PARAMETER_ID --
9 -- GET_SCHEDULE --
10 -- GET_LANE_ID --
11 -- GET_NEXT_LANE_COMMODITY_ID --
12 -- GET_NEXT_LANE_SERVICE_ID --
13 -- GET_NEXT_LANE_ID --
14 -- VERIFY_OVERLAPPING_DATE --
15 -- FIND_TYPE --
16 -- CHECK_EXISTING_LOAD --
17 -- PROCEDURES: CHECK_LANES --
18 -- DELETE_ROW --
19 -- UPDATE_LANE_FLAGS --
20 -- INSERT_LANE_TABLES --
21 -- INSERT_SCHEDULES --
22 -- INSERT_PRC_PARAMETERS --
23 -- UPDATE_LANE_RATE_CHART --
24 -- UPDATE_PRC_PARAMETER --
25 ------------------------------------------------------------------------- --
26
27 G_PKG_NAME CONSTANT VARCHAR2(50) := 'FTE_LANE_PKG';
28
29 TYPE LANE_ID_TBL IS TABLE OF FTE_LANES.LANE_ID%TYPE INDEX BY BINARY_INTEGER;
30 TYPE LANE_NUMBER_TBL IS TABLE OF FTE_LANES.LANE_NUMBER%TYPE INDEX BY BINARY_INTEGER;
31 TYPE CARRIER_ID_TBL IS TABLE OF FTE_LANES.CARRIER_ID%TYPE INDEX BY BINARY_INTEGER;
32 TYPE ORIGIN_ID_TBL IS TABLE OF FTE_LANES.ORIGIN_ID%TYPE INDEX BY BINARY_INTEGER;
33 TYPE DESTINATION_ID_TBL IS TABLE OF FTE_LANES.DESTINATION_ID%TYPE INDEX BY BINARY_INTEGER;
34 TYPE MODE_OF_TRANS_TBL IS TABLE OF FTE_LANES.MODE_OF_TRANSPORTATION_CODE%TYPE INDEX BY BINARY_INTEGER;
35 TYPE TRANSIT_TIME_TBL IS TABLE OF FTE_LANES.TRANSIT_TIME%TYPE INDEX BY BINARY_INTEGER;
36 TYPE TRANSIT_TIME_UOM_TBL IS TABLE OF FTE_LANES.TRANSIT_TIME_UOM%TYPE INDEX BY BINARY_INTEGER;
37 TYPE SPECIAL_HANDLING_TBL IS TABLE OF FTE_LANES.SPECIAL_HANDLING%TYPE INDEX BY BINARY_INTEGER;
38 TYPE ADDITIONAL_INSTRUCTIONS_TBL IS TABLE OF FTE_LANES.ADDITIONAL_INSTRUCTIONS%TYPE INDEX BY BINARY_INTEGER;
39 TYPE COMM_FC_CLASS_TBL IS TABLE OF FTE_LANES.COMM_FC_CLASS_CODE%TYPE INDEX BY BINARY_INTEGER;
40 TYPE COMM_CATG_ID_TBL IS TABLE OF FTE_LANES.COMMODITY_CATG_ID%TYPE INDEX BY BINARY_INTEGER;
41 TYPE EQUIP_TYPE_CODE_TBL IS TABLE OF FTE_LANES.EQUIPMENT_TYPE_CODE%TYPE INDEX BY BINARY_INTEGER;
42 TYPE SERVICE_TYPE_CODE_TBL IS TABLE OF FTE_LANES.SERVICE_TYPE_CODE%TYPE INDEX BY BINARY_INTEGER;
43 TYPE DISTANCE_TBL IS TABLE OF FTE_LANES.DISTANCE%TYPE INDEX BY BINARY_INTEGER;
44 TYPE DISTANCE_UOM_TBL IS TABLE OF FTE_LANES.DISTANCE_UOM%TYPE INDEX BY BINARY_INTEGER;
45 TYPE PRICELIST_VIEW_FLAG_TBL IS TABLE OF FTE_LANES.PRICELIST_VIEW_FLAG%TYPE INDEX BY BINARY_INTEGER;
46 TYPE BASIS_TBL IS TABLE OF FTE_LANES.BASIS%TYPE INDEX BY BINARY_INTEGER;
47 TYPE EFFECTIVE_DATE_TBL IS TABLE OF FTE_LANES.EFFECTIVE_DATE%TYPE INDEX BY BINARY_INTEGER;
48 TYPE EXPIRY_DATE_TBL IS TABLE OF FTE_LANES.EXPIRY_DATE%TYPE INDEX BY BINARY_INTEGER;
49 TYPE EDITABLE_FLAG_TBL IS TABLE OF FTE_LANES.EDITABLE_FLAG%TYPE INDEX BY BINARY_INTEGER;
50 TYPE LANE_TYPE_TBL IS TABLE OF FTE_LANES.LANE_TYPE%TYPE INDEX BY BINARY_INTEGER;
51 TYPE TARIFF_NAME_TBL IS TABLE OF FTE_LANES.TARIFF_NAME%TYPE INDEX BY BINARY_INTEGER;
52 TYPE LIST_HEADER_ID_TBL IS TABLE OF FTE_LANE_RATE_CHARTS.LIST_HEADER_ID%TYPE INDEX BY BINARY_INTEGER;
53 TYPE START_DATE_ACTIVE_TBL IS TABLE OF FTE_LANE_RATE_CHARTS.START_DATE_ACTIVE%TYPE INDEX BY BINARY_INTEGER;
54 TYPE END_DATE_ACTIVE_TBL IS TABLE OF FTE_LANE_RATE_CHARTS.END_DATE_ACTIVE%TYPE INDEX BY BINARY_INTEGER;
55 TYPE LANE_COMMODITY_ID_TBL IS TABLE OF FTE_LANE_COMMODITIES.LANE_COMMODITY_ID%TYPE INDEX BY BINARY_INTEGER;
56 TYPE LANE_SERVICE_ID_TBL IS TABLE OF FTE_LANE_SERVICES.LANE_SERVICE_ID%TYPE INDEX BY BINARY_INTEGER;
57
58 TYPE VESSEL_NAME_TBL IS TABLE OF FTE_SCHEDULES.VESSEL_NAME%TYPE INDEX BY BINARY_INTEGER;
59 TYPE VESSEL_TYPE_TBL IS TABLE OF FTE_SCHEDULES.VESSEL_TYPE%TYPE INDEX BY BINARY_INTEGER;
60 TYPE VOYAGE_NUMBER_TBL IS TABLE OF FTE_SCHEDULES.VOYAGE_NUMBER%TYPE INDEX BY BINARY_INTEGER;
61 TYPE ARRIVAL_DATE_INDICATOR_TBL IS TABLE OF FTE_SCHEDULES.ARRIVAL_DATE_INDICATOR%TYPE INDEX BY BINARY_INTEGER;
62 TYPE SCH_TRANSIT_TIME_TBL IS TABLE OF FTE_SCHEDULES.TRANSIT_TIME%TYPE INDEX BY BINARY_INTEGER;
63 TYPE PORT_OF_LOADING_TBL IS TABLE OF FTE_SCHEDULES.PORT_OF_LOADING%TYPE INDEX BY BINARY_INTEGER;
64 TYPE PORT_OF_DISCHARGE_TBL IS TABLE OF FTE_SCHEDULES.PORT_OF_DISCHARGE%TYPE INDEX BY BINARY_INTEGER;
65 TYPE FREQUENCY_TYPE_TBL IS TABLE OF FTE_SCHEDULES.FREQUENCY_TYPE%TYPE INDEX BY BINARY_INTEGER;
66 TYPE FREQUENCY_TBL IS TABLE OF FTE_SCHEDULES.FREQUENCY%TYPE INDEX BY BINARY_INTEGER;
67 TYPE FREQUENCY_ARRIVAL_TBL IS TABLE OF FTE_SCHEDULES.FREQUENCY_ARRIVAL%TYPE INDEX BY BINARY_INTEGER;
68 TYPE DEPARTURE_TIME_TBL IS TABLE OF FTE_SCHEDULES.DEPARTURE_TIME%TYPE INDEX BY BINARY_INTEGER;
69 TYPE ARRIVAL_TIME_TBL IS TABLE OF FTE_SCHEDULES.ARRIVAL_TIME%TYPE INDEX BY BINARY_INTEGER;
70 TYPE DEPARTURE_DATE_TBL IS TABLE OF FTE_SCHEDULES.DEPARTURE_DATE%TYPE INDEX BY BINARY_INTEGER;
71 TYPE ARRIVAL_DATE_TBL IS TABLE OF FTE_SCHEDULES.ARRIVAL_DATE%TYPE INDEX BY BINARY_INTEGER;
72 TYPE SCH_EFFECTIVE_DATE_TBL IS TABLE OF FTE_SCHEDULES.EFFECTIVE_DATE%TYPE INDEX BY BINARY_INTEGER;
73 TYPE SCH_EXPIRY_DATE_TBL IS TABLE OF FTE_SCHEDULES.EXPIRY_DATE%TYPE INDEX BY BINARY_INTEGER;
74 TYPE SCH_TRANSIT_TIME_UOM_TBL IS TABLE OF FTE_SCHEDULES.TRANSIT_TIME_UOM%TYPE INDEX BY BINARY_INTEGER;
75 TYPE SCH_LANE_ID_TBL IS TABLE OF FTE_SCHEDULES.LANE_ID%TYPE INDEX BY BINARY_INTEGER;
76 TYPE SCHEDULES_ID_TBL IS TABLE OF FTE_SCHEDULES.SCHEDULES_ID%TYPE INDEX BY BINARY_INTEGER;
77 TYPE SCH_LANE_NUMBER_TBL IS TABLE OF FTE_SCHEDULES.LANE_NUMBER%TYPE INDEX BY BINARY_INTEGER;
78
79 TYPE VALUE_FROM_TBL IS TABLE OF FTE_PRC_PARAMETERS.VALUE_FROM%TYPE INDEX BY BINARY_INTEGER;
80 TYPE VALUE_TO_TBL IS TABLE OF FTE_PRC_PARAMETERS.VALUE_TO%TYPE INDEX BY BINARY_INTEGER;
81 TYPE UOM_CODE_TBL IS TABLE OF FTE_PRC_PARAMETERS.UOM_CODE%TYPE INDEX BY BINARY_INTEGER;
82 TYPE CURRENCY_CODE_TBL IS TABLE OF FTE_PRC_PARAMETERS.CURRENCY_CODE%TYPE INDEX BY BINARY_INTEGER;
83 TYPE PRC_LANE_ID_TBL IS TABLE OF FTE_PRC_PARAMETERS.LANE_ID%TYPE INDEX BY BINARY_INTEGER;
84 TYPE PARAMETER_INS_ID_TBL IS TABLE OF FTE_PRC_PARAMETERS.PARAMETER_INSTANCE_ID%TYPE INDEX BY BINARY_INTEGER;
85 TYPE PARAMETER_ID_TBL IS TABLE OF FTE_PRC_PARAMETERS.PARAMETER_ID%TYPE INDEX BY BINARY_INTEGER;
86
87 --------------------------------------------------------------------------
88 -- FUNCTION GET_NEXT_SCHEDULE_ID
89 --
90 -- Purpose: get the next schedule id based on the sequence
91 --
92 -- Returns schedule id, -1 if not found, -2 if other errors
93 --------------------------------------------------------------------------
94 FUNCTION GET_NEXT_SCHEDULE_ID RETURN NUMBER IS
95 l_id NUMBER;
96 BEGIN
97 SELECT fte_schedules_s.nextval
98 INTO l_id
99 FROM dual;
100 RETURN l_id;
101 EXCEPTION
102 WHEN NO_DATA_FOUND THEN
103 RETURN -1;
104 WHEN OTHERS THEN
105 RETURN -2;
106 END GET_NEXT_SCHEDULE_ID;
107
108 --------------------------------------------------------------------------
109 -- FUNCTION GET_NEXT_PRC_PARAMETER_ID
110 --
111 -- Purpose: get the next schedule id based on the sequence
112 --
113 -- Returns schedule id, -1 if not found, -2 if other errors
114 --------------------------------------------------------------------------
115 FUNCTION GET_NEXT_PRC_PARAMETER_ID RETURN NUMBER IS
116 l_id NUMBER;
117 BEGIN
118 SELECT fte_prc_parameters_s.nextval
119 INTO l_id
120 FROM dual;
121 RETURN l_id;
122 EXCEPTION
123 WHEN NO_DATA_FOUND THEN
124 RETURN -1;
125 WHEN OTHERS THEN
126 RETURN -2;
127 END GET_NEXT_PRC_PARAMETER_ID;
128
129 --------------------------------------------------------------------------
130 -- FUNCTION GET_SCHEDULE
131 --
132 -- Purpose: get the schedule id based on the lane id and voyage
133 --
134 -- IN parameters:
135 -- 1. p_lane_id: lane id to be searched
136 -- 2. p_voyage: voyage
137 --
138 -- Returns the schedule id. < 0 if errors
139 --------------------------------------------------------------------------
140 FUNCTION GET_SCHEDULE(p_lane_id IN NUMBER,
141 p_voyage IN VARCHAR2) RETURN NUMBER IS
142
143 l_id NUMBER;
144 BEGIN
145 SELECT schedules_id
146 INTO l_id
147 FROM fte_schedules
148 WHERE lane_id = p_lane_id
149 AND voyage_number = p_voyage
150 AND nvl(editable_flag,'Y') = 'Y';
151 RETURN l_id;
152 EXCEPTION
153 WHEN NO_DATA_FOUND THEN
154 RETURN -1;
155 WHEN OTHERS THEN
156 RETURN -2;
157 END GET_SCHEDULE;
158
159
160 ---------------------------------------------------------------------------
161 -- FUNCTION GET_LANE_ID
162 --
163 -- Purpose: get a lane id for the lane number and carrier id
164 --
165 -- IN parameters:
166 -- 1. p_lane_number: unique lane identification name
167 -- 2. p_carrier_id: carrier id
168 --
169 -- Returns a number, -1 for no lane id, else the lane id for the lane number and carrier
170 ---------------------------------------------------------------------------
171 FUNCTION GET_LANE_ID(p_lane_number IN VARCHAR2,
172 p_carrier_id IN NUMBER) RETURN NUMBER IS
173 l_lane_id NUMBER := -1;
174 BEGIN
175 SELECT lane_id
176 INTO l_lane_id
177 FROM fte_lanes
178 WHERE lane_number = p_lane_number
179 AND carrier_id = p_carrier_id
180 AND editable_flag <> 'D';
181 RETURN l_lane_id;
182 EXCEPTION
183 WHEN NO_DATA_FOUND THEN
184 RETURN -1;
185 WHEN OTHERS THEN
186 RETURN -2;
187 END GET_LANE_ID;
188
189 -------------------------------------------------------------------------------
190 --
191 -- PROCEDURE: Check_Lanes
192 -- Purpose: check if the rate chart has lanes attatched to it or not
193 --
194 -- IN Parameter:
195 -- 1. p_pricelist_id: list header id
196 --
197 -- OUT Parameters:
198 -- 1. x_status: status, -1 if no lanes attached, 2 otherwise
199 -- 2. x_error_msg: error message if any
200 --
201 -- Returns -1 if there are no lanes attached to the
202 -- rate chart, 2 otherwise.
203 -------------------------------------------------------------------------------
204 PROCEDURE Check_Lanes(p_pricelist_id IN NUMBER,
205 x_status OUT NOCOPY NUMBER,
206 x_error_msg OUT NOCOPY VARCHAR2) IS
207
208 CURSOR lane_number IS
209 SELECT l.lane_number
210 FROM fte_lanes l, fte_lane_rate_charts c
211 WHERE c.lane_id = l.lane_id
212 AND c.list_header_id = p_pricelist_id
213 AND l.editable_flag <> 'D';
214
215 l_lanes STRINGARRAY;
216 i NUMBER;
217
218 l_module_name CONSTANT VARCHAR2(100) := 'fte.plsql.' || G_PKG_NAME || '.CHECK_LANES';
219
220 BEGIN
221 FTE_UTIL_PKG.ENTER_DEBUG(l_module_name);
222
223 IF (FTE_BULKLOAD_PKG.g_debug_on) THEN
224 FTE_UTIL_PKG.WRITE_LOGFILE(l_module_name, 'List Header ID', p_pricelist_id);
225 END IF;
226
227 x_status := -1;
228
229 OPEN lane_number;
230 FETCH lane_number BULK COLLECT INTO l_lanes;
231 i := lane_number%ROWCOUNT;
232 CLOSE lane_number;
233
234 IF ( i > 0 ) THEN
235 x_error_msg := FTE_UTIL_PKG.GET_MSG('FTE_RC_ASSIGNED_TO_LN');
236 FOR j IN 1..l_lanes.COUNT LOOP
237 x_error_msg := x_error_msg ||' '|| FTE_UTIL_PKG.GET_MSG('FTE_LANE_NUMBER') || ' '|| l_lanes(j);
238 END LOOP;
239 x_status := 2;
240 FTE_UTIL_PKG.WRITE_OUTFILE(p_module_name => l_module_name,
241 p_msg => x_error_msg,
242 p_category => 'F');
243
244 END IF;
245
246 FTE_UTIL_PKG.EXIT_DEBUG(l_module_name);
247
248 EXCEPTION WHEN OTHERS THEN
249 IF (lane_number%ISOPEN) THEN
250 CLOSE lane_number;
251 END IF;
252 x_error_msg := sqlerrm;
253 FTE_UTIL_PKG.WRITE_OUTFILE(p_module_name => l_module_name,
254 p_msg => x_error_msg,
255 p_category => 'O');
256 FTE_UTIL_PKG.EXIT_DEBUG(l_module_name);
257 x_status := 2;
258 END Check_Lanes;
259
260 ---------------------------------------------------------------------------
261 -- FUNCTION GET_NEXT_LANE_COMMODITY_ID
262 --
263 -- Purpose: get the next squence number for commodity
264 --
265 -- Returns lane commodity sequence
266 ---------------------------------------------------------------------------
267 FUNCTION GET_NEXT_LANE_COMMODITY_ID RETURN NUMBER IS
268 l_id NUMBER := -1;
269 BEGIN
270 SELECT fte_lane_commodities_s.nextval
271 INTO l_id
272 FROM dual;
273 RETURN l_id;
274 EXCEPTION
275 WHEN NO_DATA_FOUND THEN
276 RETURN -1;
277 WHEN OTHERS THEN
278 RETURN -2;
279 END GET_NEXT_LANE_COMMODITY_ID;
280
281 ---------------------------------------------------------------------------
282 -- FUNCTION GET_NEXT_LANE_SERVICE_ID
283 --
284 -- Purpose: get the next squence number for service
285 --
286 -- Returns lane service squence
287 ---------------------------------------------------------------------------
288 FUNCTION GET_NEXT_LANE_SERVICE_ID RETURN NUMBER IS
289 l_id NUMBER := -1;
290 BEGIN
291 SELECT fte_lane_services_s.nextval
292 INTO l_id
293 FROM dual;
294 RETURN l_id;
295 EXCEPTION
296 WHEN NO_DATA_FOUND THEN
297 RETURN -1;
298 WHEN OTHERS THEN
299 RETURN -2;
300 END GET_NEXT_LANE_SERVICE_ID;
301
302 ---------------------------------------------------------------------------
303 -- FUNCTION GET_NEXT_LANE_ID
304 --
305 -- Purpose: get the next lane squence number
306 --
307 -- Returns lane squence
308 ---------------------------------------------------------------------------
309 FUNCTION GET_NEXT_LANE_ID RETURN NUMBER IS
310 l_id NUMBER := -1;
311 BEGIN
312 SELECT fte_lanes_s.nextval
313 INTO l_id
314 FROM dual;
315 RETURN l_id;
316 EXCEPTION
317 WHEN NO_DATA_FOUND THEN
318 RETURN -1;
319 WHEN OTHERS THEN
320 RETURN -2;
321 END GET_NEXT_LANE_ID;
322
323 ------------------------------------------------------------------
324 -- FUNCTION VERIFY_OVERLAPPING_DATE
325 --
326 -- Purpose: verify if the rate chart being added has any date conflict with the ones already attached
327 --
328 -- IN parameters:
329 -- 1. p_name: name of the rate chart
330 -- 2. p_lane_id: lane id
331 --
332 -- OUT parameters:
333 -- 1. x_status: error status, -1 if no error
334 -- 2. x_error_msg: error message if any
335 --
336 -- RETURN true if some other chart date overlap, false if no overlap
337 ------------------------------------------------------------------
338
342 x_error_msg OUT NOCOPY VARCHAR2) RETURN BOOLEAN IS
339 FUNCTION VERIFY_OVERLAPPING_DATE(p_name IN VARCHAR2,
340 p_lane_id IN NUMBER,
341 x_status OUT NOCOPY NUMBER,
343 l_module_name CONSTANT VARCHAR2(100) := 'fte.plsql.' || G_PKG_NAME || '.VERIFY_OVERLAPPING_DATE';
344 l_number_of_chart NUMBER;
345 l_start_date_active DATE;
346 l_end_date_active DATE;
347
348 BEGIN
349 FTE_UTIL_PKG.ENTER_DEBUG(l_module_name);
350
351 IF (FTE_BULKLOAD_PKG.g_debug_on) THEN
352 FTE_UTIL_PKG.WRITE_LOGFILE(l_module_name, 'Rate Chart Name', p_name);
353 FTE_UTIL_PKG.WRITE_LOGFILE(l_module_name, 'Lane ID', p_lane_id);
354 END IF;
355
356 x_status := -1;
357
358 BEGIN
359 SELECT lb.start_date_active, lb.end_date_active
360 INTO l_start_date_active, l_end_date_active
361 FROM qp_list_headers_tl ll, qp_list_headers_b lb
362 WHERE ll.list_header_id = lb.list_header_id
363 AND ll.name = p_name
364 AND ll.language = userenv('LANG');
365
366 IF (l_start_date_active IS NULL AND l_end_date_active IS NULL) THEN
367 SELECT count(list_header_id)
368 INTO l_number_of_chart
369 FROM fte_lane_rate_charts
370 WHERE lane_id = p_lane_id;
371
372 FTE_UTIL_PKG.EXIT_DEBUG(l_module_name);
373
374 IF (l_number_of_chart = 0) THEN
375 RETURN false;
376 ELSE
377 RETURN true;
378 END IF;
379 END IF;
380
381 IF (l_start_date_active IS NULL) THEN
382 SELECT count(list_header_id)
383 INTO l_number_of_chart
384 FROM fte_lane_rate_charts
385 WHERE lane_id = p_lane_id
386 AND ((nvl(start_date_active, l_end_date_active) <= l_end_date_active));
387 ELSIF (l_end_date_active IS NULL) THEN
388 SELECT count(list_header_id)
389 INTO l_number_of_chart
390 FROM fte_lane_rate_charts
391 WHERE lane_id = p_lane_id
392 AND ((nvl(end_date_active, l_start_date_active) >= l_start_date_active));
393 ELSE
394 SELECT count(list_header_id)
395 INTO l_number_of_chart
396 FROM fte_lane_rate_charts
397 WHERE lane_id = p_lane_id
398 AND ((nvl(start_date_active, l_start_date_active) <= l_start_date_active AND nvl(end_date_active, l_end_date_active) >= l_start_date_active)
399 OR (nvl(start_date_active, l_start_date_active) <= l_end_date_active AND nvl(end_date_active, l_end_date_active) >= l_end_date_active)
400 OR (nvl(end_date_active, l_start_date_active) >= l_start_date_active AND nvl(start_date_active, l_end_date_active) <= l_end_date_active));
401 END IF;
402 EXCEPTION
403 WHEN NO_DATA_FOUND THEN
404 FTE_UTIL_PKG.EXIT_DEBUG(l_module_name);
405 RETURN false;
406 WHEN OTHERS THEN
407 x_error_msg := sqlerrm;
408 FTE_UTIL_PKG.WRITE_OUTFILE(p_module_name => l_module_name,
409 p_msg => x_error_msg,
410 p_category => '0');
411 FTE_UTIL_PKG.EXIT_DEBUG(l_module_name);
412 x_status := 1;
413 RETURN FALSE;
414 END;
415
416 IF (FTE_BULKLOAD_PKG.g_debug_on) THEN
417 FTE_UTIL_PKG.WRITE_LOGFILE(l_module_name, 'Query result', l_number_of_chart);
418 END IF;
419
420 FTE_UTIL_PKG.EXIT_DEBUG(l_module_name);
421
422 IF (l_number_of_chart = 0) THEN
423 RETURN false;
424 ELSE
425 RETURN true;
426 END IF;
427 EXCEPTION
428 WHEN NO_DATA_FOUND THEN
429 FTE_UTIL_PKG.EXIT_DEBUG(l_module_name);
430 RETURN FALSE;
431 WHEN OTHERS THEN
432 x_error_msg := sqlerrm;
433 FTE_UTIL_PKG.WRITE_OUTFILE(p_module_name => l_module_name,
434 p_msg => x_error_msg,
435 p_category => '0');
436 FTE_UTIL_PKG.EXIT_DEBUG(l_module_name);
437 x_status := 1;
438 RETURN FALSE;
439 END VERIFY_OVERLAPPING_DATE;
440
441 ------------------------------------------------------------------
442 -- FUNCTION FIND_TYPE
443 --
444 -- Purpose: find if the lane service or lane commodity already have the service level or commodity
445 --
446 -- IN parameters:
447 -- 1. p_type: SERVICE_LEVEL or COMMODITY
448 -- 2. p_value: value to be found
449 -- 3. p_lane_id: lane id
450 --
451 -- OUT parameters:
452 -- 1. x_status: error status, -1 if no error
453 -- 2. x_error_msg: error message if any
454 --
455 -- RETURN true if type is found
456 ------------------------------------------------------------------
457
458 FUNCTION FIND_TYPE(p_type IN VARCHAR2,
459 p_value IN VARCHAR2,
460 p_lane_id IN NUMBER,
461 p_line_number IN NUMBER,
462 x_status OUT NOCOPY NUMBER,
463 x_error_msg OUT NOCOPY VARCHAR2) RETURN BOOLEAN IS
464 l_result VARCHAR2(10);
465 l_numfetch NUMBER;
466 l_module_name CONSTANT VARCHAR2(100) := 'fte.plsql.' || G_PKG_NAME || '.FIND_TYPE';
467 CURSOR GET_SERVICE (p_lane_id IN NUMBER, p_value IN VARCHAR2) IS
468 SELECT 'true'
469 FROM fte_lane_services
470 WHERE service_code = p_value
471 AND lane_id = p_lane_id;
472
473 CURSOR GET_COMMODITY (p_lane_id IN NUMBER, p_value IN VARCHAR2) IS
474 SELECT 'true'
475 FROM fte_lane_commodities
476 WHERE commodity_catg_id = TO_NUMBER(p_value)
477 AND lane_id = p_lane_id;
478
479 BEGIN
480 FTE_UTIL_PKG.ENTER_DEBUG(l_module_name);
481
482 IF (FTE_BULKLOAD_PKG.g_debug_on) THEN
486 END IF;
483 FTE_UTIL_PKG.WRITE_LOGFILE(l_module_name, 'Type', p_type);
484 FTE_UTIL_PKG.WRITE_LOGFILE(l_module_name, 'Value', p_value);
485 FTE_UTIL_PKG.WRITE_LOGFILE(l_module_name, 'Lane ID', p_lane_id);
487
488 x_status := -1;
489
490 IF (p_type = 'SERVICE_LEVEL') THEN
491 OPEN GET_SERVICE(p_lane_id, p_value);
492 FETCH GET_SERVICE INTO l_result;
493 l_numfetch := SQL%ROWCOUNT;
494 CLOSE GET_SERVICE;
495 ELSIF (p_type = 'COMMODITY') THEN
496 OPEN GET_COMMODITY(p_lane_id, p_value);
497 FETCH GET_COMMODITY INTO l_result;
498 l_numfetch := SQL%ROWCOUNT;
499 CLOSE GET_COMMODITY;
500 END IF;
501
502 FTE_UTIL_PKG.EXIT_DEBUG(l_module_name);
503
504 IF (l_numfetch = 0) THEN
505 RETURN false;
506 ELSE
507 RETURN true;
508 END IF;
509 EXCEPTION
510 WHEN OTHERS THEN
511 IF (GET_SERVICE%ISOPEN) THEN
512 CLOSE GET_SERVICE;
513 END IF;
514 IF (GET_COMMODITY%ISOPEN) THEN
515 CLOSE GET_COMMODITY;
516 END IF;
517
518 x_error_msg := sqlerrm;
519 FTE_UTIL_PKG.WRITE_OUTFILE(p_module_name => l_module_name,
520 p_msg => x_error_msg,
521 p_category => '0',
522 p_line_number => p_line_number);
523 FTE_UTIL_PKG.EXIT_DEBUG(l_module_name);
524 x_status := 1;
525 RETURN FALSE;
526 END FIND_TYPE;
527
528 ------------------------------------------------------------------------
529 -- FUNCTION CHECK_EXISTING_LOAD
530 --
531 -- Purpose: check existing entries in the fte_* tables
532 --
533 -- IN parameters:
534 -- 1. p_id: id to used for validating
535 -- 2. p_table: table to validate
536 -- 3. p_code: codes to use for matching
537 --
538 -- OUT parameters:
539 -- 1. x_status: status of the error -1 when no error
540 -- 2. x_error_msg: error msg if any errors
541 ------------------------------------------------------------------------
542 FUNCTION CHECK_EXISTING_LOAD( p_id IN NUMBER,
543 p_table IN VARCHAR2,
544 p_code IN VARCHAR2,
545 p_line_number IN NUMBER,
546 x_status OUT NOCOPY NUMBER,
547 x_error_msg OUT NOCOPY VARCHAR2) RETURN BOOLEAN IS
548 l_module_name CONSTANT VARCHAR2(100) := 'fte.plsql.' || G_PKG_NAME || '.CHECK_EXISTING_LOAD';
549 l_result NUMBER;
550 BEGIN
551
552 FTE_UTIL_PKG.ENTER_DEBUG(l_module_name);
553
554 IF (FTE_BULKLOAD_PKG.g_debug_on) THEN
555 FTE_UTIL_PKG.WRITE_LOGFILE(l_module_name, 'ID', p_id);
556 FTE_UTIL_PKG.WRITE_LOGFILE(l_module_name, 'Table', p_table);
557 FTE_UTIL_PKG.WRITE_LOGFILE(l_module_name, 'Code', p_code);
558 END IF;
559
560 x_status := -1;
561
562 IF (p_table = 'FTE_LANE_RATE_CHARTS') THEN
563 SELECT count(lane_id)
564 INTO l_result
565 FROM fte_lane_rate_charts
566 WHERE lane_id = p_id
567 AND list_header_id = TO_NUMBER(p_code);
568 ELSIF (p_table = 'FTE_LANE_COMMODITIES') THEN
569 SELECT count(lane_id)
570 INTO l_result
571 FROM fte_lane_commodities
572 WHERE lane_id = p_id
573 AND commodity_catg_id = TO_NUMBER(p_code);
574 ELSIF (p_table = 'FTE_LANE_SERVICES') THEN
575 SELECT count(lane_id)
576 INTO l_result
577 FROM fte_lane_services
578 WHERE lane_id = p_id
579 AND service_code = p_code;
580 END IF;
581
582 FTE_UTIL_PKG.EXIT_DEBUG(l_module_name);
583 IF (l_result > 0) THEN
584 RETURN TRUE;
585 ELSE
586 RETURN FALSE;
587 END IF;
588
589 EXCEPTION
590 WHEN NO_DATA_FOUND THEN
591 FTE_UTIL_PKG.EXIT_DEBUG(l_module_name);
592 RETURN FALSE;
593 WHEN OTHERS THEN
594 x_error_msg := sqlerrm;
595 FTE_UTIL_PKG.WRITE_OUTFILE(p_module_name => l_module_name,
596 p_msg => x_error_msg,
597 p_category => '0',
598 p_line_number => p_line_number);
599 x_status := 2;
600 FTE_UTIL_PKG.EXIT_DEBUG(l_module_name);
601 RETURN TRUE;
602 END CHECK_EXISTING_LOAD;
603
604 ------------------------------------------------------------------------
605 -- PROCEDURE DELETE_ROW
606 --
607 -- Purpose: delete a row in the fte_* tables
608 --
609 -- IN parameters:
610 -- 1. p_id: id to used for delete
611 -- 2. p_table: table to delete from
612 -- 3. p_code: codes to use for matching
613 --
614 -- OUT parameters:
615 -- 1. x_status: status of the error -1 when no error
616 -- 2. x_error_msg: error msg if any errors
617 ------------------------------------------------------------------------
618 PROCEDURE DELETE_ROW(p_id IN NUMBER,
619 p_table IN VARCHAR2,
620 p_code IN VARCHAR2,
621 p_line_number IN NUMBER,
622 x_status OUT NOCOPY NUMBER,
623 x_error_msg OUT NOCOPY VARCHAR2) IS
624 l_module_name CONSTANT VARCHAR2(100) := 'fte.plsql.' || G_PKG_NAME || '.DELETE_ROW';
625 BEGIN
626
627 FTE_UTIL_PKG.ENTER_DEBUG(l_module_name);
628
629 IF (FTE_BULKLOAD_PKG.g_debug_on) THEN
630 FTE_UTIL_PKG.WRITE_LOGFILE(l_module_name, 'ID', p_id);
631 FTE_UTIL_PKG.WRITE_LOGFILE(l_module_name, 'Table', p_table);
632 FTE_UTIL_PKG.WRITE_LOGFILE(l_module_name, 'Code', p_code);
633 END IF;
634
635 x_status := -1;
636
637 IF (p_table = 'FTE_LANE_RATE_CHARTS') THEN
641 ELSIF (p_table = 'FTE_LANE_COMMODITIES') THEN
638 DELETE from fte_lane_rate_charts
639 WHERE lane_id = p_id
640 AND list_header_id = TO_NUMBER(p_code);
642 DELETE from fte_lane_commodities
643 WHERE lane_id = p_id
644 AND commodity_catg_id = TO_NUMBER(p_code);
645 ELSIF (p_table = 'FTE_LANE_SERVICES') THEN
646 DELETE from fte_lane_services
647 WHERE lane_id = p_id
648 AND service_code = p_code;
649 ELSIF (p_table = 'FTE_LANES') THEN
650 UPDATE fte_lanes
651 SET editable_flag = 'D',
652 lane_number = p_id || '-DELETED by USER', -- might just leave the lane number as it
653 LAST_UPDATED_BY = FND_GLOBAL.USER_ID,
654 LAST_UPDATE_DATE = sysdate,
655 LAST_UPDATE_LOGIN = FND_GLOBAL.USER_ID
656 WHERE lane_id = p_id;
657 ELSIF (p_table = 'FTE_PRC_PARAMETERS') THEN
658 IF (p_code IS NULL) THEN
659 DELETE from fte_prc_parameters
660 WHERE parameter_instance_id = p_id;
661 ELSE
662 DELETE from fte_prc_parameters
663 WHERE lane_id = p_id
664 AND parameter_id = TO_NUMBER(p_code);
665 END IF;
666 ELSIF (p_table = 'FTE_SCHEDULES') THEN
667
668 UPDATE FTE_SCHEDULES
669 SET EDITABLE_FLAG = 'D',
670 LAST_UPDATED_BY = FND_GLOBAL.USER_ID,
671 LAST_UPDATE_DATE = sysdate,
672 LAST_UPDATE_LOGIN = FND_GLOBAL.USER_ID
673 WHERE SCHEDULES_ID = p_id
674 AND NVL(EDITABLE_FLAG,'Y') = 'Y';
675 ELSE
676 x_error_msg := FTE_UTIL_PKG.GET_MSG('FTE_INVALID_TABLE');
677 FTE_UTIL_PKG.WRITE_OUTFILE(p_module_name => l_module_name,
678 p_msg => x_error_msg,
679 p_category => 'O',
680 p_line_number => p_line_number);
681
682 x_status := 1;
683 FTE_UTIL_PKG.EXIT_DEBUG(l_module_name);
684 RETURN;
685 END IF;
686
687 FTE_UTIL_PKG.EXIT_DEBUG(l_module_name);
688 RETURN;
689 EXCEPTION
690 WHEN OTHERS THEN
691 x_error_msg := sqlerrm;
692 FTE_UTIL_PKG.WRITE_OUTFILE(p_module_name => l_module_name,
693 p_msg => x_error_msg,
694 p_category => '0',
695 p_line_number => p_line_number);
696 x_status := 2;
697 FTE_UTIL_PKG.EXIT_DEBUG(l_module_name);
698 RETURN;
699 END DELETE_ROW;
700
701 -------------------------------------------------------------------------
702 -- PROCEDURE UPDATE_LANE_FLAGS
703 --
704 -- Purpose: update the service detail, commodity detail, and schedules flag of the lane
705 --
706 -- IN parameters:
707 -- 1. p_type: type of the update
708 -- 2. p_lane_id: lane id to be updated
709 -- 3. p_value: value to set the flag to for schedule
710 --
711 -- OUT parameters:
712 -- 1. x_status: status of the error -1 when no error
713 -- 2. x_error_msg: error msg if any errors
714 -------------------------------------------------------------------------
715
716 PROCEDURE UPDATE_LANE_FLAGS(p_type IN VARCHAR2,
717 p_lane_id IN NUMBER,
718 p_value IN VARCHAR2 DEFAULT 'N',
719 x_status OUT NOCOPY NUMBER,
720 x_error_msg OUT NOCOPY VARCHAR2) IS
721
722 l_return STRINGARRAY := STRINGARRAY();
723 l_flag VARCHAR2(1) := 'N';
724 l_code VARCHAR2(100);
725 l_module_name CONSTANT VARCHAR2(100) := 'fte.plsql.' || G_PKG_NAME || '.UPDATE_LANE_FLAGS';
726 CURSOR Get_Commodity_Type (p_lane_id IN NUMBER)
727 IS
728 SELECT TO_CHAR(commodity_catg_id)
729 FROM fte_lane_commodities
730 WHERE lane_id = p_lane_id;
731
732 CURSOR Get_Service_Code (p_lane_id IN NUMBER)
733 IS
734 SELECT service_code
735 FROM fte_lane_services
736 WHERE lane_id = p_lane_id;
737
738 BEGIN
739
740 FTE_UTIL_PKG.ENTER_DEBUG(l_module_name);
741
742 IF (FTE_BULKLOAD_PKG.g_debug_on) THEN
743 FTE_UTIL_PKG.WRITE_LOGFILE(l_module_name, 'Type', p_type);
744 FTE_UTIL_PKG.WRITE_LOGFILE(l_module_name, 'Lane ID', p_lane_id);
745 FTE_UTIL_PKG.WRITE_LOGFILE(l_module_name, 'Value', p_value);
746 END IF;
747
748 x_status := -1;
749
750 IF (p_type = 'SERVICE_LEVEL') THEN
751 OPEN Get_Service_Code(p_lane_id => p_lane_id);
752 FETCH Get_Service_Code
753 BULK COLLECT INTO l_return;
754 CLOSE Get_Service_Code;
755 ELSIF (p_type = 'COMMODITY_TYPE') THEN
756 OPEN Get_Commodity_Type(p_lane_id => p_lane_id);
757 FETCH Get_Commodity_Type
758 BULK COLLECT INTO l_return;
759 CLOSE Get_Commodity_Type;
760 ELSIF (p_type <> 'SCHEDULE') THEN
761 x_error_msg := FTE_UTIL_PKG.GET_MSG('FTE_INVALID_TYPE');
762 FTE_UTIL_PKG.WRITE_OUTFILE(p_module_name => l_module_name,
763 p_msg => x_error_msg,
764 p_category => 'O');
765
766 x_status := 1;
767 FTE_UTIL_PKG.EXIT_DEBUG(l_module_name);
768 RETURN;
769 END IF;
770
771 IF (l_return.COUNT = 1) THEN
772 l_flag := 'Y';
773 l_code := l_return(1);
774 ELSIF (l_return.COUNT > 1) THEN
775 l_flag := 'Y';
776 END IF;
777
778 IF (p_type = 'SERVICE_LEVEL') THEN
779 UPDATE fte_lanes
780 SET service_type_code = l_code,
781 service_detail_flag = l_flag,
782 LAST_UPDATED_BY = FND_GLOBAL.USER_ID,
783 LAST_UPDATE_DATE = sysdate,
784 LAST_UPDATE_LOGIN = FND_GLOBAL.USER_ID
785 WHERE lane_id = p_lane_id;
786 ELSIF (p_type = 'COMMODITY_TYPE') THEN
787 UPDATE fte_lanes
788 SET commodity_catg_id = TO_NUMBER(l_code),
789 commodity_detail_flag = l_flag,
793 WHERE lane_id = p_lane_id;
790 LAST_UPDATED_BY = FND_GLOBAL.USER_ID,
791 LAST_UPDATE_DATE = sysdate,
792 LAST_UPDATE_LOGIN = FND_GLOBAL.USER_ID
794 ELSE -- schedule
795 UPDATE fte_lanes
796 SET schedules_flag = p_value,
797 LAST_UPDATED_BY = FND_GLOBAL.USER_ID,
798 LAST_UPDATE_DATE = sysdate,
799 LAST_UPDATE_LOGIN = FND_GLOBAL.USER_ID
800 WHERE lane_id = p_lane_id;
801 END IF;
802
803 FTE_UTIL_PKG.EXIT_DEBUG(l_module_name);
804 EXCEPTION
805 WHEN OTHERS THEN
806 IF (GET_SERVICE_CODE%ISOPEN) THEN
807 CLOSE GET_SERVICE_CODE;
808 END IF;
809 IF (GET_COMMODITY_TYPE%ISOPEN) THEN
810 CLOSE GET_COMMODITY_TYPE;
811 END IF;
812 x_error_msg := sqlerrm;
813 FTE_UTIL_PKG.WRITE_OUTFILE(p_module_name => l_module_name,
814 p_msg => x_error_msg,
815 p_category => 'O');
816 x_status := 1;
817 FTE_UTIL_PKG.EXIT_DEBUG(l_module_name);
818 RETURN;
819 END UPDATE_LANE_FLAGS;
820
821 -------------------------------------------------------------------------
822 -- PROCEDURE INSERT_SCHEDULES
823 --
824 -- Purpose: insert schedules
825 --
826 -- IN parameters:
827 -- 1. p_schedule_tbl: schedules pl/sql table
828 --
829 -- OUT parameters:
830 -- 1. x_status: status of the error -1 when no error
831 -- 2. x_error_msg: error msg if any errors
832 -------------------------------------------------------------------------
833
834 PROCEDURE INSERT_SCHEDULES(p_schedule_tbl IN OUT NOCOPY schedule_tbl,
835 x_status OUT NOCOPY NUMBER,
836 x_error_msg OUT NOCOPY VARCHAR2) IS
837
838 l_vessel_name VESSEL_NAME_TBL;
839 l_vessel_type VESSEL_TYPE_TBL;
840 l_voyage_number VOYAGE_NUMBER_TBL;
841 l_arrival_date_ind ARRIVAL_DATE_INDICATOR_TBL;
842 l_transit_time SCH_TRANSIT_TIME_TBL;
843 l_port_of_loading PORT_OF_LOADING_TBL;
844 l_port_of_discharge PORT_OF_DISCHARGE_TBL;
845 l_frequency_type FREQUENCY_TYPE_TBL;
846 l_frequency FREQUENCY_TBL;
847 l_frequency_arrival FREQUENCY_ARRIVAL_TBL;
848 l_departure_time DEPARTURE_TIME_TBL;
849 l_arrival_time ARRIVAL_TIME_TBL;
850 l_departure_date DEPARTURE_DATE_TBL;
851 l_arrival_date ARRIVAL_DATE_TBL;
852 l_effective_date SCH_EFFECTIVE_DATE_TBL;
853 l_expiry_date SCH_EXPIRY_DATE_TBL;
854 l_transit_time_uom SCH_TRANSIT_TIME_UOM_TBL;
855 l_lane_id SCH_LANE_ID_TBL;
856 l_schedules_id SCHEDULES_ID_TBL;
857 l_lane_number SCH_LANE_NUMBER_TBL;
858 l_module_name CONSTANT VARCHAR2(100) := 'fte.plsql.' || G_PKG_NAME || '.INSERT_SCHEDULES';
859
860 BEGIN
861
862 FTE_UTIL_PKG.ENTER_DEBUG(l_module_name);
863
864 IF (FTE_BULKLOAD_PKG.g_debug_on) THEN
865 FTE_UTIL_PKG.WRITE_LOGFILE(l_module_name, 'Number of Schedules', p_schedule_tbl.COUNT);
866 END IF;
867
868 x_status := -1;
869
870 IF (p_schedule_tbl.COUNT > 0) THEN
871 FOR i in p_schedule_tbl.FIRST..p_schedule_tbl.LAST LOOP
872 l_vessel_name(i) := p_schedule_tbl(i).vessel_name;
873 l_vessel_type(i) := p_schedule_tbl(i).vessel_type;
874 l_voyage_number(i) := p_schedule_tbl(i).voyage_number;
875 l_arrival_date_ind(i) := p_schedule_tbl(i).arrival_date_indicator;
876 l_transit_time(i) := p_schedule_tbl(i).transit_time;
877 l_port_of_loading(i) := p_schedule_tbl(i).port_of_loading;
878 l_port_of_discharge(i) := p_schedule_tbl(i).port_of_discharge;
879 l_frequency_type(i) := p_schedule_tbl(i).frequency_type;
880 l_frequency(i) := p_schedule_tbl(i).frequency;
881 l_frequency_arrival(i) := p_schedule_tbl(i).frequency_arrival;
882 l_departure_time(i) := p_schedule_tbl(i).departure_time;
883 l_arrival_time(i) := p_schedule_tbl(i).arrival_time;
884 l_departure_date(i) := p_schedule_tbl(i).departure_date;
885 l_arrival_date(i) := p_schedule_tbl(i).arrival_date;
886 l_effective_date(i) := p_schedule_tbl(i).effective_date;
887 l_expiry_date(i) := p_schedule_tbl(i).expiry_date;
888 l_transit_time_uom(i) := p_schedule_tbl(i).transit_time_uom;
889 l_lane_id(i) := p_schedule_tbl(i).lane_id;
890 l_schedules_id(i) := p_schedule_tbl(i).schedules_id;
891 l_lane_number(i) := p_schedule_tbl(i).lane_number;
892 END LOOP;
893
894 BEGIN
895 FORALL i in p_schedule_tbl.FIRST..p_schedule_tbl.LAST
896 INSERT INTO FTE_SCHEDULES(LANE_ID,
897 SCHEDULES_ID,
898 VESSEL_NAME,
899 VESSEL_TYPE,
900 VOYAGE_NUMBER,
901 ARRIVAL_DATE_INDICATOR,
902 TRANSIT_TIME,
903 PORT_OF_LOADING,
904 PORT_OF_DISCHARGE,
905 FREQUENCY_TYPE,
906 FREQUENCY,
907 FREQUENCY_ARRIVAL,
908 DEPARTURE_TIME,
909 ARRIVAL_TIME,
910 DEPARTURE_DATE,
911 ARRIVAL_DATE,
912 EFFECTIVE_DATE,
913 EXPIRY_DATE,
914 TRANSIT_TIME_UOM,
915 LANE_NUMBER,
916 CREATED_BY,
917 CREATION_DATE,
918 LAST_UPDATED_BY,
919 LAST_UPDATE_DATE,
920 LAST_UPDATE_LOGIN)
921 VALUES (l_lane_id(i),
922 l_schedules_id(i),
923 l_vessel_name(i),
924 l_vessel_type(i),
925 l_voyage_number(i),
926 l_arrival_date_ind(i),
927 l_transit_time(i),
928 l_port_of_loading(i),
929 l_port_of_discharge(i),
930 l_frequency_type(i),
931 l_frequency(i),
932 l_frequency_arrival(i),
936 l_arrival_date(i),
933 l_departure_time(i),
934 l_arrival_time(i),
935 l_departure_date(i),
937 l_effective_date(i),
938 l_expiry_date(i),
939 l_transit_time_uom(i),
940 l_lane_number(i),
941 FND_GLOBAL.USER_ID,
942 SYSDATE,
943 FND_GLOBAL.USER_ID,
944 SYSDATE,
945 FND_GLOBAL.USER_ID);
946
947 --+
948 -- For Generating Output file
949 --+
950
951 FOR i in l_voyage_number.FIRST..l_voyage_number.LAST LOOP
952
953 FTE_UTIL_PKG.WRITE_OUTFILE(p_module_name => l_module_name,
954 p_msg => l_voyage_number(i),
955 p_category => NULL);
956 END LOOP;
957
958 END;
959 END IF;
960 FTE_UTIL_PKG.EXIT_DEBUG(l_module_name);
961
962 EXCEPTION
963 WHEN OTHERS THEN
964 x_error_msg := sqlerrm;
965 FTE_UTIL_PKG.WRITE_OUTFILE(p_module_name => l_module_name,
966 p_msg => x_error_msg,
967 p_category => 'O');
968 x_status := 1;
969 FTE_UTIL_PKG.EXIT_DEBUG(l_module_name);
970 RETURN;
971 END INSERT_SCHEDULES;
972
973
974 -------------------------------------------------------------------------
975 -- PROCEDURE INSERT_LANE_TABLES
976 --
977 -- Purpose: insert all lane tables
978 --
979 -- IN parameters:
980 -- 1. p_lane_tbl: lane pl/sql table
981 -- 2. p_lane_rate_chart_tbl: lane rate chart pl/sql table
982 -- 3. p_lane_commodity_tbl: lane commodity pl/sql table
983 -- 4. p_lane_service_tbl: lane service pl/sql table
984 --
985 -- OUT parameters:
986 -- 1. x_status: status of the error -1 when no error
987 -- 2. x_error_msg: error msg if any errors
988 -------------------------------------------------------------------------
989
990 PROCEDURE INSERT_LANE_TABLES(p_lane_tbl IN OUT NOCOPY lane_tbl,
991 p_lane_rate_chart_tbl IN OUT NOCOPY lane_rate_chart_tbl,
992 p_lane_commodity_tbl IN OUT NOCOPY lane_commodity_tbl,
993 p_lane_service_tbl IN OUT NOCOPY lane_service_tbl,
994 x_status OUT NOCOPY NUMBER,
995 x_error_msg OUT NOCOPY VARCHAR2) IS
996
997 l_lane_id LANE_ID_TBL;
998 l_lane_number LANE_NUMBER_TBL;
999 l_carrier_id CARRIER_ID_TBL;
1000 l_origin_id ORIGIN_ID_TBL;
1001 l_dest_id DESTINATION_ID_TBL;
1002 l_mode_of_trans MODE_OF_TRANS_TBL;
1003 l_transit_time TRANSIT_TIME_TBL;
1004 l_transit_time_uom TRANSIT_TIME_UOM_TBL;
1005 l_special_handling SPECIAL_HANDLING_TBL;
1006 l_additional_instructions ADDITIONAL_INSTRUCTIONS_TBL;
1007 l_comm_fc_class_code COMM_FC_CLASS_TBL;
1008 l_comm_catg_id COMM_CATG_ID_TBL;
1009 l_equip_type_code EQUIP_TYPE_CODE_TBL;
1010 l_service_type_code SERVICE_TYPE_CODE_TBL;
1011 l_distance DISTANCE_TBL;
1012 l_distance_uom DISTANCE_UOM_TBL;
1013 l_pricelist_view_flag PRICELIST_VIEW_FLAG_TBL;
1014 l_basis BASIS_TBL;
1015 l_effective_date EFFECTIVE_DATE_TBL;
1016 l_expiry_date EXPIRY_DATE_TBL;
1017 l_editable_flag EDITABLE_FLAG_TBL;
1018 l_lane_type LANE_TYPE_TBL;
1019 l_tariff_name TARIFF_NAME_TBL;
1020
1021 l_lrc_lane_id LANE_ID_TBL;
1022 l_list_header_id LIST_HEADER_ID_TBL;
1023 l_start_date_active START_DATE_ACTIVE_TBL;
1024 l_end_date_active END_DATE_ACTIVE_TBL;
1025
1026 l_lc_lane_id LANE_ID_TBL;
1027 l_lane_commodity_id LANE_COMMODITY_ID_TBL;
1028 l_lc_basis BASIS_TBL;
1029 l_lc_comm_catg_id COMM_CATG_ID_TBL;
1030
1031 l_ls_lane_id LANE_ID_TBL;
1032 l_lane_service_id LANE_SERVICE_ID_TBL;
1033 l_service_code SERVICE_TYPE_CODE_TBL;
1034 l_count NUMBER;
1035 l_lane_id_number NUMBER;
1036 l_update BOOLEAN := FALSE;
1037 l_module_name CONSTANT VARCHAR2(100) := 'fte.plsql.' || G_PKG_NAME || '.INSERT_LANE_TABLES';
1038
1039 BEGIN
1040
1041 FTE_UTIL_PKG.ENTER_DEBUG(l_module_name);
1042
1043 IF (FTE_BULKLOAD_PKG.g_debug_on) THEN
1044 FTE_UTIL_PKG.WRITE_LOGFILE(l_module_name, 'Number of Lanes', p_lane_tbl.COUNT);
1045 FTE_UTIL_PKG.WRITE_LOGFILE(l_module_name, 'Number of Lane rate charts', p_lane_rate_chart_tbl.COUNT);
1046 FTE_UTIL_PKG.WRITE_LOGFILE(l_module_name, 'Number of Lane commodities', p_lane_commodity_tbl.COUNT);
1047 FTE_UTIL_PKG.WRITE_LOGFILE(l_module_name, 'Number of Lane services', p_lane_service_tbl.COUNT);
1048 END IF;
1049
1050 x_status := -1;
1051
1052 IF (p_lane_tbl.COUNT > 0) THEN
1053 FOR i in p_lane_tbl.FIRST..p_lane_tbl.LAST LOOP
1054 IF (p_lane_tbl(i).action = 'UPDATE') THEN
1055 l_update := TRUE;
1056 UPDATE FTE_LANES
1057 SET COMM_FC_CLASS_CODE = p_lane_tbl(i).comm_fc_class_code,
1058 ORIGIN_ID = nvl(p_lane_tbl(i).origin_id, ORIGIN_ID),
1059 DESTINATION_ID = nvl(p_lane_tbl(i).destination_id, DESTINATION_ID),
1060 LANE_TYPE = nvl(p_lane_tbl(i).lane_type, lane_type),
1061 PRICELIST_VIEW_FLAG = p_lane_tbl(i).pricelist_view_flag,
1062 BASIS = p_lane_tbl(i).basis,
1063 EFFECTIVE_DATE = p_lane_tbl(i).effective_date,
1064 EXPIRY_DATE = p_lane_tbl(i).expiry_date,
1065 LAST_UPDATED_BY = FND_GLOBAL.USER_ID,
1066 LAST_UPDATE_DATE = sysdate,
1067 LAST_UPDATE_LOGIN = FND_GLOBAL.USER_ID
1068 WHERE lane_id = p_lane_tbl(i).lane_id;
1069 p_lane_tbl.DELETE(i);
1070 ELSIF (p_lane_tbl(i).action = 'DELETE') THEN
1071 p_lane_tbl.DELETE(i);
1072 ELSE
1073 l_count := l_lane_id.COUNT+1;
1074 l_lane_id(l_count) := p_lane_tbl(i).lane_id;
1075 l_lane_number(l_count) := p_lane_tbl(i).lane_number;
1079 l_mode_of_trans(l_count) := p_lane_tbl(i).mode_of_transportation_code;
1076 l_carrier_id(l_count) := p_lane_tbl(i).carrier_id;
1077 l_origin_id(l_count) := p_lane_tbl(i).origin_id;
1078 l_dest_id(l_count) := p_lane_tbl(i).destination_id;
1080 l_transit_time(l_count) := p_lane_tbl(i).transit_time;
1081 l_transit_time_uom(l_count) := p_lane_tbl(i).transit_time_uom;
1082 l_special_handling(l_count) := p_lane_tbl(i).special_handling;
1083 l_additional_instructions(l_count) := p_lane_tbl(i).additional_instructions;
1084 l_comm_fc_class_code(l_count) := p_lane_tbl(i).comm_fc_class_code;
1085 l_comm_catg_id(l_count) := p_lane_tbl(i).commodity_catg_id;
1086 l_equip_type_code(l_count) := p_lane_tbl(i).equipment_type_code;
1087 l_service_type_code(l_count) := p_lane_tbl(i).service_type_code;
1088 l_distance(l_count) := p_lane_tbl(i).distance;
1089 l_distance_uom(l_count) := p_lane_tbl(i).distance_uom;
1090 l_pricelist_view_flag(l_count):= p_lane_tbl(i).pricelist_view_flag;
1091 l_basis(l_count) := p_lane_tbl(i).basis;
1092 l_effective_date(l_count) := p_lane_tbl(i).effective_date;
1093 l_expiry_date(l_count) := p_lane_tbl(i).expiry_date;
1094 l_editable_flag(l_count) := p_lane_tbl(i).editable_flag;
1095 l_lane_type(l_count) := p_lane_tbl(i).lane_type;
1096 l_tariff_name(l_count) := p_lane_tbl(i).tariff_name;
1097 END IF;
1098 END LOOP;
1099
1100 IF (NOT l_update) THEN
1101 BEGIN
1102 FORALL i in l_lane_id.FIRST..l_lane_id.LAST
1103 INSERT INTO FTE_LANES (LANE_ID,
1104 LANE_NUMBER,
1105 OWNER_ID,
1106 CARRIER_ID,
1107 ORIGIN_ID,
1108 DESTINATION_ID,
1109 MODE_OF_TRANSPORTATION_CODE,
1110 TRANSIT_TIME,
1111 TRANSIT_TIME_UOM,
1112 SPECIAL_HANDLING,
1113 ADDITIONAL_INSTRUCTIONS,
1114 COMMODITY_DETAIL_FLAG,
1115 EQUIPMENT_DETAIL_FLAG,
1116 SERVICE_DETAIL_FLAG,
1117 COMM_FC_CLASS_CODE,
1118 COMMODITY_CATG_ID,
1119 EQUIPMENT_TYPE_CODE,
1120 SERVICE_TYPE_CODE,
1121 DISTANCE,
1122 DISTANCE_UOM,
1123 SCHEDULES_FLAG,
1124 PRICELIST_VIEW_FLAG,
1125 BASIS,
1126 EFFECTIVE_DATE,
1127 EXPIRY_DATE,
1128 EDITABLE_FLAG,
1129 CREATED_BY,
1130 CREATION_DATE,
1131 LAST_UPDATED_BY,
1132 LAST_UPDATE_DATE,
1133 LAST_UPDATE_LOGIN,
1134 LANE_TYPE,
1135 TARIFF_NAME)
1136 VALUES (l_lane_id(i),
1137 l_lane_number(i),
1138 -1,
1139 l_carrier_id(i),
1140 l_origin_id(i),
1141 l_dest_id(i),
1142 l_mode_of_trans(i),
1143 l_transit_time(i),
1144 l_transit_time_uom(i),
1145 l_special_handling(i),
1146 l_additional_instructions(i),
1147 'N',
1148 'N',
1149 'N',
1150 l_comm_fc_class_code(i),
1151 l_comm_catg_id(i),
1152 l_equip_type_code(i),
1153 l_service_type_code(i),
1154 l_distance(i),
1155 l_distance_uom(i),
1156 'N',
1157 l_pricelist_view_flag(i),
1158 l_basis(i),
1159 l_effective_date(i),
1160 l_expiry_date(i),
1161 l_editable_flag(i),
1162 FND_GLOBAL.USER_ID,
1163 SYSDATE,
1164 FND_GLOBAL.USER_ID,
1165 SYSDATE,
1166 FND_GLOBAL.USER_ID,
1167 l_lane_type(i),
1168 l_tariff_name(i));
1169 EXCEPTION
1170 WHEN OTHERS THEN
1171 x_error_msg := sqlerrm;
1172 FTE_UTIL_PKG.WRITE_LOGFILE(l_module_name, '[Inserting lanes]');
1173 FTE_UTIL_PKG.WRITE_OUTFILE(p_module_name => l_module_name,
1174 p_msg => x_error_msg,
1175 p_category => 'O');
1176 x_status := 1;
1177 FTE_UTIL_PKG.EXIT_DEBUG(l_module_name);
1178 p_lane_tbl.DELETE;
1179 p_lane_rate_chart_tbl.DELETE;
1180 p_lane_commodity_tbl.DELETE;
1181 p_lane_service_tbl.DELETE;
1182 RETURN;
1183 END; -- FINISH INSERTING lanes
1184
1185 --+
1186 -- For Generating Output file
1187 --+
1188 FTE_UTIL_PKG.WRITE_OUTFILE(p_module_name => l_module_name,
1189 p_msg_name => 'FTE_LANES_LOADED',
1190 p_category => NULL);
1191
1192 FOR i in l_lane_number.FIRST..l_lane_number.LAST LOOP
1193
1194 FTE_UTIL_PKG.WRITE_OUTFILE(p_module_name => l_module_name,
1195 p_msg => l_lane_number(i),
1196 p_category => NULL);
1197
1198 END LOOP;
1199
1200 END IF;
1201 END IF;
1202
1203 IF (p_lane_rate_chart_tbl.COUNT > 0) THEN
1204 FOR i in p_lane_rate_chart_tbl.FIRST..p_lane_rate_chart_tbl.LAST LOOP
1205 l_lrc_lane_id(i) := p_lane_rate_chart_tbl(i).lane_id;
1206 l_list_header_id(i) := p_lane_rate_chart_tbl(i).list_header_id;
1207 l_end_date_active(i) := p_lane_rate_chart_tbl(i).end_date_active;
1208 l_start_date_active(i) := p_lane_rate_chart_tbl(i).start_date_active;
1209 END LOOP;
1210
1211 BEGIN
1212 FORALL i in p_lane_rate_chart_tbl.FIRST..p_lane_rate_chart_tbl.LAST
1216 START_DATE_ACTIVE,
1213 INSERT INTO FTE_LANE_RATE_CHARTS (LANE_ID,
1214 LIST_HEADER_ID,
1215 END_DATE_ACTIVE,
1217 CREATED_BY,
1218 CREATION_DATE,
1219 LAST_UPDATED_BY,
1220 LAST_UPDATE_DATE,
1221 LAST_UPDATE_LOGIN)
1222 VALUES (l_lrc_lane_id(i),
1223 l_list_header_id(i),
1224 l_end_date_active(i),
1225 l_start_date_active(i),
1226 FND_GLOBAL.USER_ID,
1227 SYSDATE,
1228 FND_GLOBAL.USER_ID,
1229 SYSDATE,
1230 FND_GLOBAL.USER_ID);
1231 --+
1232 -- Remove the hold from fte_lanes
1233 -- for these rate charts.
1234 --+
1235 IF (FTE_BULKLOAD_PKG.g_debug_on) THEN
1236 FTE_UTIL_PKG.WRITE_LOGFILE(l_module_name, 'Updating lane_type of fte_lanes... ');
1237 END IF;
1238 FORALL i in l_lrc_lane_id.FIRST..l_lrc_lane_id.LAST
1239 UPDATE fte_lanes
1240 SET lane_type = NULL,
1241 LAST_UPDATED_BY = FND_GLOBAL.USER_ID,
1242 LAST_UPDATE_DATE = SYSDATE,
1243 LAST_UPDATE_LOGIN = FND_GLOBAL.USER_ID
1244 WHERE lane_id = l_lrc_lane_id(i);
1245
1246 EXCEPTION
1247 WHEN DUP_VAL_ON_INDEX THEN
1248 x_status := -1;
1249 WHEN OTHERS THEN
1250 x_error_msg := sqlerrm;
1251 FTE_UTIL_PKG.WRITE_LOGFILE(l_module_name, '[Inserting lane rate charts]');
1252 FTE_UTIL_PKG.WRITE_OUTFILE(p_module_name => l_module_name,
1253 p_msg => x_error_msg,
1254 p_category => 'O');
1255 FTE_UTIL_PKG.EXIT_DEBUG(l_module_name);
1256 p_lane_tbl.DELETE;
1257 p_lane_rate_chart_tbl.DELETE;
1258 p_lane_commodity_tbl.DELETE;
1259 p_lane_service_tbl.DELETE;
1260 x_status := 1;
1261 RETURN;
1262 END; --FINISH INSERTING lane rate charts
1263 END IF;
1264
1265 IF (p_lane_commodity_tbl.COUNT > 0) THEN
1266 FOR i in p_lane_commodity_tbl.FIRST..p_lane_commodity_tbl.LAST LOOP
1267 l_lc_lane_id(i) := p_lane_commodity_tbl(i).lane_id;
1268 l_lane_commodity_id(i) := p_lane_commodity_tbl(i).lane_commodity_id;
1269 l_lc_basis(i) := p_lane_commodity_tbl(i).basis;
1270 l_lc_comm_catg_id(i) := p_lane_commodity_tbl(i).commodity_catg_id;
1271 END LOOP;
1272
1273 BEGIN
1274 FORALL i in p_lane_commodity_tbl.FIRST..p_lane_commodity_tbl.LAST
1275 INSERT INTO FTE_LANE_COMMODITIES (LANE_ID,
1276 LANE_COMMODITY_ID,
1277 BASIS,
1278 COMMODITY_CATG_ID,
1279 CREATED_BY,
1280 CREATION_DATE,
1281 LAST_UPDATED_BY,
1282 LAST_UPDATE_DATE,
1283 LAST_UPDATE_LOGIN)
1284 VALUES (l_lc_lane_id(i),
1285 l_lane_commodity_id(i),
1286 l_lc_basis(i),
1287 l_lc_comm_catg_id(i),
1288 FND_GLOBAL.USER_ID,
1289 SYSDATE,
1290 FND_GLOBAL.USER_ID,
1291 SYSDATE,
1292 FND_GLOBAL.USER_ID);
1293 EXCEPTION
1294 WHEN DUP_VAL_ON_INDEX THEN
1295 x_status := -1;
1296 WHEN OTHERS THEN
1297 x_error_msg := sqlerrm;
1298 FTE_UTIL_PKG.WRITE_LOGFILE(l_module_name, '[Inserting lane commodities]');
1299 FTE_UTIL_PKG.WRITE_OUTFILE(p_module_name => l_module_name,
1300 p_msg => x_error_msg,
1301 p_category => 'O');
1302 FTE_UTIL_PKG.EXIT_DEBUG(l_module_name);
1303 p_lane_tbl.DELETE;
1304 p_lane_rate_chart_tbl.DELETE;
1305 p_lane_commodity_tbl.DELETE;
1306 p_lane_service_tbl.DELETE;
1307 x_status := 1;
1308 RETURN;
1309 END; --FINISH INSERTING lane commodities
1310 END IF;
1311
1312 IF (p_lane_service_tbl.COUNT > 0) THEN
1313 FOR i in p_lane_service_tbl.FIRST..p_lane_service_tbl.LAST LOOP
1314 l_ls_lane_id(i) := p_lane_service_tbl(i).lane_id;
1315 l_lane_service_id(i) := p_lane_service_tbl(i).lane_service_id;
1316 l_service_code(i) := p_lane_service_tbl(i).service_code;
1317 END LOOP;
1318
1319 BEGIN
1320 FORALL i in p_lane_service_tbl.FIRST..p_lane_service_tbl.LAST
1321 INSERT INTO FTE_LANE_SERVICES (LANE_ID,
1322 LANE_SERVICE_ID,
1323 SERVICE_CODE,
1324 CREATED_BY,
1325 CREATION_DATE,
1326 LAST_UPDATED_BY,
1327 LAST_UPDATE_DATE,
1328 LAST_UPDATE_LOGIN)
1329 VALUES (l_ls_lane_id(i),
1330 l_lane_service_id(i),
1331 l_service_code(i),
1332 FND_GLOBAL.USER_ID,
1333 SYSDATE,
1334 FND_GLOBAL.USER_ID,
1335 SYSDATE,
1336 FND_GLOBAL.USER_ID);
1337 EXCEPTION
1338 WHEN DUP_VAL_ON_INDEX THEN
1339 x_status := -1;
1340 WHEN OTHERS THEN
1341 x_error_msg := sqlerrm;
1342 FTE_UTIL_PKG.WRITE_LOGFILE(l_module_name, '[Inserting lane services]');
1343 FTE_UTIL_PKG.WRITE_OUTFILE(p_module_name => l_module_name,
1344 p_msg => x_error_msg,
1345 p_category => 'O');
1346 FTE_UTIL_PKG.EXIT_DEBUG(l_module_name);
1347 p_lane_tbl.DELETE;
1348 p_lane_rate_chart_tbl.DELETE;
1349 p_lane_commodity_tbl.DELETE;
1350 p_lane_service_tbl.DELETE;
1351 x_status := 1;
1352 RETURN;
1353 END; --FINISH INSERTING lane services
1354 END IF;
1355
1359 UPDATE_LANE_FLAGS(p_type => 'SERVICE_LEVEL',
1356 IF (p_lane_tbl.COUNT > 0) THEN
1357 FOR i in p_lane_tbl.FIRST..p_lane_tbl.LAST LOOP
1358 -- update the service detail flag after add
1360 p_lane_id => p_lane_tbl(i).lane_id,
1361 x_status => x_status,
1362 x_error_msg => x_error_msg);
1363 -- update the commodity detail flag after add
1364 UPDATE_LANE_FLAGS(p_type => 'COMMODITY_TYPE',
1365 p_lane_id => p_lane_tbl(i).lane_id,
1366 x_status => x_status,
1367 x_error_msg => x_error_msg);
1368 END LOOP;
1369 END IF;
1370
1371 p_lane_tbl.DELETE;
1372 p_lane_rate_chart_tbl.DELETE;
1373 p_lane_commodity_tbl.DELETE;
1374 p_lane_service_tbl.DELETE;
1375
1376 FTE_UTIL_PKG.EXIT_DEBUG(l_module_name);
1377 EXCEPTION
1378 WHEN OTHERS THEN
1379 x_error_msg := sqlerrm;
1380 FTE_UTIL_PKG.WRITE_OUTFILE(p_module_name => l_module_name,
1381 p_msg => x_error_msg,
1382 p_category => 'O');
1383 FTE_UTIL_PKG.EXIT_DEBUG(l_module_name);
1384 x_status := 1;
1385 p_lane_tbl.DELETE;
1386 p_lane_rate_chart_tbl.DELETE;
1387 p_lane_commodity_tbl.DELETE;
1388 p_lane_service_tbl.DELETE;
1389 RETURN;
1390 END INSERT_LANE_TABLES;
1391
1392 -------------------------------------------------------------------------
1393 -- PROCEDURE INSERT_PRC_PARAMETERS
1394 --
1395 -- Purpose: insert the prc parameter row
1396 --
1397 -- IN parameters:
1398 -- 1. p_prc_parameter_tbl: pricing parameter pl/sql table
1399 --
1400 -- OUT parameters:
1401 -- 1. x_status: status of the error -1 when no error
1402 -- 2. x_error_msg: error msg if any errors
1403 -------------------------------------------------------------------------
1404
1405 PROCEDURE INSERT_PRC_PARAMETERS(p_prc_parameter_tbl IN OUT NOCOPY prc_parameter_tbl,
1406 x_status OUT NOCOPY NUMBER,
1407 x_error_msg OUT NOCOPY VARCHAR2) IS
1408
1409 l_value_from VALUE_FROM_TBL;
1410 l_value_to VALUE_TO_TBL;
1411 l_uom_code UOM_CODE_TBL;
1412 l_currency_code CURRENCY_CODE_TBL;
1413 l_parameter_instance_id PARAMETER_INS_ID_TBL;
1414 l_lane_id PRC_LANE_ID_TBL;
1415 l_parameter_id PARAMETER_ID_TBL;
1416
1417
1418 l_module_name CONSTANT VARCHAR2(100) := 'fte.plsql.' || G_PKG_NAME || '.INSERT_PRC_PARAMETERS';
1419
1420 BEGIN
1421
1422 FTE_UTIL_PKG.ENTER_DEBUG(l_module_name);
1423
1424 IF (FTE_BULKLOAD_PKG.g_debug_on) THEN
1425 FTE_UTIL_PKG.WRITE_LOGFILE(l_module_name, 'Number of pricing parameters', p_prc_parameter_tbl.COUNT);
1426 END IF;
1427
1428 x_status := -1;
1429
1430 IF (p_prc_parameter_tbl.COUNT > 0) THEN
1431 FOR i in p_prc_parameter_tbl.FIRST..p_prc_parameter_tbl.LAST LOOP
1432 l_value_from(i) := p_prc_parameter_tbl(i).value_from;
1433 l_value_to(i) := p_prc_parameter_tbl(i).value_to;
1434 l_uom_code(i) := p_prc_parameter_tbl(i).uom_code;
1435 l_currency_code(i) := p_prc_parameter_tbl(i).currency_code;
1436 l_parameter_instance_id(i) := p_prc_parameter_tbl(i).parameter_instance_id;
1437 l_lane_id(i) := p_prc_parameter_tbl(i).lane_id;
1438 l_parameter_id(i) := p_prc_parameter_tbl(i).parameter_id;
1439 END LOOP;
1440
1441 BEGIN
1442 FORALL i in p_prc_parameter_tbl.FIRST..p_prc_parameter_tbl.LAST
1443 INSERT INTO FTE_PRC_PARAMETERS (LANE_ID,
1444 PARAMETER_ID,
1445 PARAMETER_INSTANCE_ID,
1446 VALUE_FROM,
1447 VALUE_TO,
1448 UOM_CODE,
1449 CURRENCY_CODE,
1450 CREATED_BY,
1451 CREATION_DATE,
1452 LAST_UPDATED_BY,
1453 LAST_UPDATE_DATE,
1454 LAST_UPDATE_LOGIN)
1455 VALUES (l_lane_id(i),
1456 l_parameter_id(i),
1457 l_parameter_instance_id(i),
1458 l_value_from(i),
1459 l_value_to(i),
1460 l_uom_code(i),
1461 l_currency_code(i),
1462 FND_GLOBAL.USER_ID,
1463 SYSDATE,
1464 FND_GLOBAL.USER_ID,
1465 SYSDATE,
1466 FND_GLOBAL.USER_ID);
1467
1468 END;
1469 END IF;
1470 FTE_UTIL_PKG.EXIT_DEBUG(l_module_name);
1471 EXCEPTION
1472 WHEN OTHERS THEN
1473 x_error_msg := sqlerrm;
1474 FTE_UTIL_PKG.WRITE_OUTFILE(p_module_name => l_module_name,
1475 p_msg => x_error_msg,
1476 p_category => 'O');
1477 FTE_UTIL_PKG.EXIT_DEBUG(l_module_name);
1478 x_status := 1;
1479 RETURN;
1480 END INSERT_PRC_PARAMETERS;
1481
1482 -------------------------------------------------------------------------
1483 -- PROCEDURE UPDATE_LANE_RATE_CHART
1484 --
1485 -- Purpose: update lane rate chart's dates
1486 --
1487 -- IN parameters:
1488 -- 1. p_list_header_id: the rate chart to update in fte_lane_rate_charts
1489 -- 2. p_start_date: start date
1490 -- 3. p_end_date: end date
1491 --
1492 -- OUT parameters:
1493 -- 1. x_status: status of the error -1 when no error
1494 -- 2. x_error_msg: error msg if any errors
1495 -------------------------------------------------------------------------
1496
1497 PROCEDURE UPDATE_LANE_RATE_CHART (p_list_header_id IN NUMBER,
1498 p_start_date IN DATE,
1499 p_end_date IN DATE,
1500 x_status OUT NOCOPY NUMBER,
1501 x_error_msg OUT NOCOPY VARCHAR2)IS
1502 l_module_name CONSTANT VARCHAR2(100) := 'fte.plsql.' || G_PKG_NAME || '.UPDATE_LANE_RATE_CHART';
1503 BEGIN
1504 FTE_UTIL_PKG.ENTER_DEBUG(l_module_name);
1505
1506 IF (FTE_BULKLOAD_PKG.g_debug_on) THEN
1507 FTE_UTIL_PKG.WRITE_LOGFILE(l_module_name, 'List Header ID', p_list_header_id);
1508 FTE_UTIL_PKG.WRITE_LOGFILE(l_module_name, 'Start Date', p_start_date);
1509 FTE_UTIL_PKG.WRITE_LOGFILE(l_module_name, 'End Date', p_end_date);
1510 END IF;
1511
1512 x_status := -1;
1513
1514 UPDATE fte_lane_rate_charts
1515 SET START_DATE_ACTIVE = p_start_date,
1516 END_DATE_ACTIVE = p_end_date,
1517 LAST_UPDATED_BY = FND_GLOBAL.USER_ID,
1518 LAST_UPDATE_DATE = sysdate,
1519 LAST_UPDATE_LOGIN = FND_GLOBAL.USER_ID
1520 WHERE LIST_HEADER_ID = p_list_header_id;
1521
1522 FTE_UTIL_PKG.EXIT_DEBUG(l_module_name);
1523 EXCEPTION
1524 WHEN OTHERS THEN
1525 x_error_msg := sqlerrm;
1526 FTE_UTIL_PKG.WRITE_OUTFILE(p_module_name => l_module_name,
1527 p_msg => x_error_msg,
1528 p_category => 'O');
1529 FTE_UTIL_PKG.EXIT_DEBUG(l_module_name);
1530 x_status := 1;
1531 RETURN;
1532 END UPDATE_LANE_RATE_CHART;
1533
1534 -------------------------------------------------------------------------
1535 -- PROCEDURE UPDATE_PRC_PARAMETER
1536 --
1537 -- Purpose: update pricing parameter line
1538 --
1539 -- IN parameters:
1540 -- 1. p_prc_parameter_tbl: pricing parameter pl/sql table
1541 --
1542 -- OUT parameters:
1543 -- 1. x_status: status of the error -1 when no error
1544 -- 2. x_error_msg: error msg if any errors
1545 -------------------------------------------------------------------------
1546
1547 PROCEDURE UPDATE_PRC_PARAMETER( p_prc_parameter_tbl IN prc_parameter_tbl,
1548 x_status OUT NOCOPY NUMBER,
1549 x_error_msg OUT NOCOPY VARCHAR2)IS
1550
1551 l_value_from fte_prc_parameters.value_from%TYPE;
1552 l_value_to fte_prc_parameters.value_to%TYPE;
1553 l_uom_code fte_prc_parameters.uom_code%TYPE;
1554 l_currency_code fte_prc_parameters.currency_code%TYPE;
1555 l_parameter_instance_id fte_prc_parameters.parameter_instance_id%TYPE;
1556 l_module_name CONSTANT VARCHAR2(100) := 'fte.plsql.' || G_PKG_NAME || '.UPDATE_PRC_PARAMETERS';
1557
1558 BEGIN
1559
1560 FTE_UTIL_PKG.ENTER_DEBUG(l_module_name);
1561
1562 l_value_from := p_prc_parameter_tbl(p_prc_parameter_tbl.COUNT).value_from;
1563 l_value_to := p_prc_parameter_tbl(p_prc_parameter_tbl.COUNT).value_to;
1564 l_uom_code := p_prc_parameter_tbl(p_prc_parameter_tbl.COUNT).uom_code;
1565 l_currency_code := p_prc_parameter_tbl(p_prc_parameter_tbl.COUNT).currency_code;
1566 l_parameter_instance_id := p_prc_parameter_tbl(p_prc_parameter_tbl.COUNT).parameter_instance_id;
1567
1568 IF (FTE_BULKLOAD_PKG.g_debug_on) THEN
1569 FTE_UTIL_PKG.WRITE_LOGFILE(l_module_name, 'Value from', l_value_from);
1570 FTE_UTIL_PKG.WRITE_LOGFILE(l_module_name, 'Value to', l_value_to);
1571 FTE_UTIL_PKG.WRITE_LOGFILE(l_module_name, 'Uom code', l_uom_code);
1572 FTE_UTIL_PKG.WRITE_LOGFILE(l_module_name, 'Currecy code', l_currency_code);
1573 FTE_UTIL_PKG.WRITE_LOGFILE(l_module_name, 'Parameter code', l_parameter_instance_id);
1574 END IF;
1575
1576 x_status := -1;
1577
1578 UPDATE fte_prc_parameters
1579 SET value_from = l_value_from,
1580 value_to = l_value_to,
1581 uom_code = l_uom_code,
1582 currency_code = l_currency_code,
1583 LAST_UPDATED_BY = FND_GLOBAL.USER_ID,
1584 LAST_UPDATE_DATE = sysdate,
1585 LAST_UPDATE_LOGIN = FND_GLOBAL.USER_ID
1586 WHERE parameter_instance_id = l_parameter_instance_id;
1587
1588 FTE_UTIL_PKG.EXIT_DEBUG(l_module_name);
1589
1590 EXCEPTION
1591 WHEN OTHERS THEN
1592 x_error_msg := sqlerrm;
1593 FTE_UTIL_PKG.WRITE_OUTFILE(p_module_name => l_module_name,
1594 p_msg => x_error_msg,
1595 p_category => 'O');
1596 FTE_UTIL_PKG.EXIT_DEBUG(l_module_name);
1597 x_status := 1;
1598 RETURN;
1599 END UPDATE_PRC_PARAMETER;
1600
1601 END FTE_LANE_PKG;