[Home] [Help]
PACKAGE BODY: APPS.FTE_RATE_CHART_LOADER
Source
1 PACKAGE BODY FTE_RATE_CHART_LOADER AS
2 /* $Header: FTERCLDB.pls 120.0 2005/06/28 02:27:32 pkaliyam noship $ */
3 -------------------------------------------------------------------------- --
4 -- --
5 -- NAME: FTE_RATE_CHART_LOADER --
6 -- TYPE: PACKAGE BODY --
7 -- DESCRIPTION: Contains Rate Chart Validations for Bulk Loader purposes --
8 -- --
9 -------------------------------------------------------------------------- --
10
11 G_PKG_NAME CONSTANT VARCHAR2(50) := 'FTE_RATE_CHART_LOADER';
12 g_qp_list_header_tbl FTE_RATE_CHART_PKG.qp_list_header_tbl;
13 g_qp_list_line_tbl FTE_RATE_CHART_PKG.qp_list_line_tbl;
14 g_qp_qualifier_tbl FTE_RATE_CHART_PKG.qp_qualifier_tbl;
15 g_qp_pricing_attrib_tbl FTE_RATE_CHART_PKG.qp_pricing_attrib_tbl;
16 g_carrier_id NUMBER;
17 g_list_header_deleted BOOLEAN := false;
18
19 ----------------------------------------------------------------------------
20 -- PROCEDURE PROCESS_DATA
21 --
22 -- Purpose: Call appropriate process function according to the type.
23 --
24 -- IN parameters:
25 -- 1. p_type: type of the block (Rating zone chart, rating setup, orign, destination)
26 -- 2. p_table: pl/sql table of STRINGARRAY containing the block information
27 -- 3. p_line_number: line number for the beginning of the block
28 --
29 -- OUT parameters:
30 -- 1. x_status: status of the processing, -1 means no error
31 -- 2. x_error_msg: error message if any.
32 ----------------------------------------------------------------------------
33 PROCEDURE PROCESS_DATA (p_type IN VARCHAR2,
34 p_block_header IN FTE_BULKLOAD_PKG.block_header_tbl,
35 p_block_data IN FTE_BULKLOAD_PKG.block_data_tbl,
36 p_line_number IN NUMBER,
37 x_status OUT NOCOPY NUMBER,
38 x_error_msg OUT NOCOPY VARCHAR2) IS
39 l_module_name CONSTANT VARCHAR2(100) := 'fte.plsql.' || G_PKG_NAME || '.PROCESS_DATA';
40 BEGIN
41 FTE_UTIL_PKG.ENTER_DEBUG(l_module_name);
42
43 x_status := -1;
44
45 IF (p_type = 'RATE_CHART') THEN
46 PROCESS_RATE_CHART(p_block_header => p_block_header,
47 p_block_data => p_block_data,
48 p_line_number => p_line_number,
49 x_status => x_status,
50 x_error_msg => x_error_msg);
51 ELSIF (p_type = 'RATE_LINE') THEN
52 PROCESS_RATE_LINE(p_block_header => p_block_header,
53 p_block_data => p_block_data,
54 p_line_number => p_line_number,
55 x_status => x_status,
56 x_error_msg => x_error_msg);
57 ELSIF (p_type = 'RATE_BREAK') THEN
58 PROCESS_RATE_BREAK(p_block_header => p_block_header,
59 p_block_data => p_block_data,
60 p_line_number => p_line_number,
61 x_status => x_status,
62 x_error_msg => x_error_msg);
63 ELSIF (p_type = 'RATING_ATTRIBUTE') THEN
64 PROCESS_RATING_ATTRIBUTE(p_block_header => p_block_header,
65 p_block_data => p_block_data,
66 p_line_number => p_line_number,
67 x_status => x_status,
68 x_error_msg => x_error_msg);
69 ELSIF (p_type = 'CHARGES_DISCOUNTS') THEN
70 PROCESS_CHARGES_DISCOUNTS(p_block_header => p_block_header,
71 p_block_data => p_block_data,
72 p_line_number => p_line_number,
73 x_status => x_status,
74 x_error_msg => x_error_msg);
75 ELSIF (p_type = 'CHARGES_DISCOUNTS_LINE') THEN
76 PROCESS_CHARGES_DISCOUNTS_LINE(p_block_header => p_block_header,
77 p_block_data => p_block_data,
78 p_line_number => p_line_number,
79 x_status => x_status,
80 x_error_msg => x_error_msg);
81 ELSIF (p_type = 'ADJUSTED_RATE_CHART') THEN
82 PROCESS_ADJUSTED_RATE_CHART(p_block_header => p_block_header,
83 p_block_data => p_block_data,
84 p_line_number => p_line_number,
85 x_status => x_status,
86 x_error_msg => x_error_msg);
87 END IF;
88 FTE_UTIL_PKG.EXIT_DEBUG(l_module_name);
89 EXCEPTION
90 WHEN OTHERS THEN
91 FTE_UTIL_PKG.WRITE_OUTFILE(p_module_name => l_module_name,
92 p_msg => sqlerrm,
93 p_category => 'O');
94 FTE_UTIL_PKG.EXIT_DEBUG(l_module_name);
95 x_status := 1;
96 END PROCESS_DATA;
97
98 ----------------------------------------------------------------------------
99 -- PROCEDURE PROCESS_RATE_CHART
100 --
101 -- Purpose: process the lines in p_table for rate chart header
102 --
103 -- IN parameters:
104 -- 1. p_block_header: pl/sql table of STRINGARRAY containing the block information
105 -- 2. p_block_data: pl/sql table of the data
106 -- 3. p_line_number: line number for the beginning of the block
107 -- 4. p_validate_column: boolean for calling VALIDATE_COLUMNS, default true
108 -- 5. p_validate: boolean for validating data within VALIDATION procedure, default true
109 --
110 -- OUT parameters:
111 -- 1. x_status: status of the processing, -1 means no error
112 -- 2. x_error_msg: error message if any.
113 ----------------------------------------------------------------------------
114
115 PROCEDURE PROCESS_RATE_CHART(p_block_header IN FTE_BULKLOAD_PKG.block_header_tbl,
116 p_block_data IN FTE_BULKLOAD_PKG.block_data_tbl,
117 p_line_number IN NUMBER,
118 p_validate_column IN BOOLEAN DEFAULT TRUE,
119 p_validate IN BOOLEAN DEFAULT TRUE,
120 p_process_id IN NUMBER DEFAULT NULL,
121 x_status OUT NOCOPY NUMBER,
122 x_error_msg OUT NOCOPY VARCHAR2) IS
123
124 l_values FTE_BULKLOAD_PKG.data_values_tbl;
125 l_action VARCHAR2(20);
126 l_carrier_id NUMBER;
127 l_module_name CONSTANT VARCHAR2(100) := 'fte.plsql.' || G_PKG_NAME || '.PROCESS_RATE_CHART';
128 BEGIN
129 FTE_UTIL_PKG.ENTER_DEBUG(l_module_name);
130
131 x_status := -1;
132
133 IF (p_validate_column) THEN
134 --verify the column name
135 FTE_VALIDATION_PKG.VALIDATE_COLUMNS(p_keys => p_block_header,
136 p_type => 'RATE_CHART',
137 p_line_number => p_line_number+1,
138 x_status => x_status,
139 x_error_msg => x_error_msg);
140 IF (x_status <> -1) THEN
141 FTE_UTIL_PKG.EXIT_DEBUG(l_module_name);
142 RETURN;
143 END IF;
144 END IF;
145
146 --now the body of the block
147 FOR i IN p_block_data.FIRST..p_block_data.LAST LOOP
148 l_values := p_block_data(i);
149 FTE_VALIDATION_PKG.VALIDATE_RATE_CHART(p_values => l_values,
150 p_line_number => p_line_number+i+1,
151 p_qp_list_header_tbl => g_qp_list_header_tbl,
152 p_qp_qualifier_tbl => g_qp_qualifier_tbl,
153 p_action => l_action,
154 p_carrier_id => l_carrier_id,
155 p_validate => p_validate,
156 p_process_id => p_process_id,
157 x_status => x_status,
158 x_error_msg => x_error_msg);
159
160 IF (x_status <> -1) THEN
161 FTE_UTIL_PKG.EXIT_DEBUG(l_module_name);
162 RETURN;
163 END IF;
164
165 g_carrier_id := l_carrier_id;
166
167 IF (l_action = 'DELETE') THEN
168 FTE_RATE_CHART_PKG.DELETE_FROM_QP(p_list_header_id => g_qp_list_header_tbl(g_qp_list_header_tbl.COUNT).list_header_id,
169 p_name => g_qp_list_header_tbl(g_qp_list_header_tbl.COUNT).name,
170 p_action => l_action,
171 p_line_number => p_line_number+i+1,
172 x_error_msg => x_error_msg,
173 x_status => x_status );
174
175 g_list_header_deleted := true;
176 ELSIF (l_action = 'UPDATE') THEN
177
178 FTE_RATE_CHART_PKG.DELETE_FROM_QP(p_list_header_id => g_qp_list_header_tbl(g_qp_list_header_tbl.COUNT).list_header_id,
179 p_name => g_qp_list_header_tbl(g_qp_list_header_tbl.COUNT).name,
180 p_action => l_action,
181 p_line_number => p_line_number+i+1,
182 x_error_msg => x_error_msg,
183 x_status => x_status );
184
185
186 FTE_LANE_PKG.UPDATE_LANE_RATE_CHART(p_list_header_id => g_qp_list_header_tbl(g_qp_list_header_tbl.COUNT).list_header_id,
187 p_start_date => g_qp_list_header_tbl(g_qp_list_header_tbl.COUNT).start_date_active,
188 p_end_date => g_qp_list_header_tbl(g_qp_list_header_tbl.COUNT).end_date_active,
189 x_status => x_status,
190 x_error_msg => x_error_msg);
191
192 END IF;
193
194 IF (x_status <> -1) THEN
195 FTE_UTIL_PKG.EXIT_DEBUG(l_module_name);
196 RETURN;
197 END IF;
198
199 END LOOP;
200 FTE_UTIL_PKG.EXIT_DEBUG(l_module_name);
201
202 EXCEPTION
203 WHEN OTHERS THEN
204 FTE_UTIL_PKG.WRITE_OUTFILE(p_module_name => l_module_name,
205 p_msg => sqlerrm,
206 p_category => 'O');
207 FTE_UTIL_PKG.EXIT_DEBUG(l_module_name);
208 x_status := 1;
209 RETURN;
210 END PROCESS_RATE_CHART;
211
212 ----------------------------------------------------------------------------
213 -- PROCEDURE PROCESS_RATE_LINE
214 --
215 -- Purpose: process the lines in p_table for rate chart line
216 --
217 -- IN parameters:
218 -- 1. p_block_header: pl/sql table of STRINGARRAY containing the block information
219 -- 2. p_block_data: pl/sql table of the data
220 -- 3. p_line_number: line number for the beginning of the block
221 -- 4. p_validate_column: boolean for calling VALIDATE_COLUMNS, default true
222 -- 5. p_validate: boolean for validating data within VALIDATION procedure, default true
223 --
224 -- OUT parameters:
225 -- 1. x_status: status of the processing, -1 means no error
226 -- 2. x_error_msg: error message if any.
227 ----------------------------------------------------------------------------
228
229 PROCEDURE PROCESS_RATE_LINE (p_block_header IN FTE_BULKLOAD_PKG.block_header_tbl,
230 p_block_data IN FTE_BULKLOAD_PKG.block_data_tbl,
231 p_line_number IN NUMBER,
232 p_validate_column IN BOOLEAN DEFAULT TRUE,
233 p_validate IN BOOLEAN DEFAULT TRUE,
234 x_status OUT NOCOPY NUMBER,
235 x_error_msg OUT NOCOPY VARCHAR2) IS
236
237 l_values FTE_BULKLOAD_PKG.data_values_tbl;
238 l_module_name CONSTANT VARCHAR2(100) := 'fte.plsql.' || G_PKG_NAME || '.PROCESS_RATE_LINE';
239 BEGIN
240 FTE_UTIL_PKG.ENTER_DEBUG(l_module_name);
241
242 x_status := -1;
243
244 IF (g_list_header_deleted) THEN
245 FTE_UTIL_PKG.EXIT_DEBUG(l_module_name);
246 RETURN;
247 END IF;
248
249 IF (p_validate_column) THEN
250 --verify the column name
251 FTE_VALIDATION_PKG.VALIDATE_COLUMNS(p_keys => p_block_header,
252 p_type => 'RATE_LINE',
253 p_line_number => p_line_number+1,
254 x_status => x_status,
255 x_error_msg => x_error_msg);
256 IF (x_status <> -1) THEN
257 FTE_UTIL_PKG.EXIT_DEBUG(l_module_name);
258 RETURN;
259 END IF;
260 END IF;
261
262 --now the body of the block
263 FOR i IN p_block_data.FIRST..p_block_data.LAST LOOP
264 l_values := p_block_data(i);
265
266 FTE_VALIDATION_PKG.VALIDATE_RATE_LINE(p_values => l_values,
267 p_line_number => p_line_number+i+1,
268 p_qp_list_line_tbl => g_qp_list_line_tbl,
269 p_qp_pricing_attrib_tbl => g_qp_pricing_attrib_tbl,
270 p_validate => p_validate,
271 x_status => x_status,
272 x_error_msg => x_error_msg);
273
274 IF (x_status <> -1) THEN
275 FTE_UTIL_PKG.EXIT_DEBUG(l_module_name);
276 RETURN;
277 END IF;
278
279 END LOOP;
280 FTE_UTIL_PKG.EXIT_DEBUG(l_module_name);
281
282 EXCEPTION
283 WHEN OTHERS THEN
284 FTE_UTIL_PKG.WRITE_OUTFILE(p_module_name => l_module_name,
285 p_msg => sqlerrm,
286 p_category => 'O');
287 FTE_UTIL_PKG.EXIT_DEBUG(l_module_name);
288 x_status := 1;
289 RETURN;
290 END PROCESS_RATE_LINE;
291
292 ----------------------------------------------------------------------------
293 -- PROCEDURE PROCESS_RATE_BREAK
294 --
295 -- Purpose: process the lines in p_table for rate chart break
296 --
297 -- IN parameters:
298 -- 1. p_block_header: pl/sql table of STRINGARRAY containing the block information
299 -- 2. p_block_data: pl/sql table of the data
300 -- 3. p_line_number: line number for the beginning of the block
301 -- 4. p_validate_column: boolean for calling VALIDATE_COLUMNS, default true
302 -- 5. p_validate: boolean for validating data within VALIDATION procedure, default true
303 --
304 -- OUT parameters:
305 -- 1. x_status: status of the processing, -1 means no error
306 -- 2. x_error_msg: error message if any.
307 ----------------------------------------------------------------------------
308
309 PROCEDURE PROCESS_RATE_BREAK(p_block_header IN FTE_BULKLOAD_PKG.block_header_tbl,
310 p_block_data IN FTE_BULKLOAD_PKG.block_data_tbl,
311 p_line_number IN NUMBER,
312 p_validate_column IN BOOLEAN DEFAULT TRUE,
313 p_validate IN BOOLEAN DEFAULT TRUE,
314 x_status OUT NOCOPY NUMBER,
315 x_error_msg OUT NOCOPY VARCHAR2) IS
316
317 l_values FTE_BULKLOAD_PKG.data_values_tbl;
318 l_module_name CONSTANT VARCHAR2(100) := 'fte.plsql.' || G_PKG_NAME || '.PROCESS_RATE_BREAK';
319 BEGIN
320 FTE_UTIL_PKG.ENTER_DEBUG(l_module_name);
321
322 x_status := -1;
323
324 IF (g_list_header_deleted) THEN
325 FTE_UTIL_PKG.EXIT_DEBUG(l_module_name);
326 RETURN;
327 END IF;
328
329 IF (p_validate_column) THEN
330 --verify the column name
331
332 FTE_VALIDATION_PKG.VALIDATE_COLUMNS(p_keys => p_block_header,
333 p_type => 'RATE_BREAK',
334 p_line_number => p_line_number+1,
335 x_status => x_status,
336 x_error_msg => x_error_msg);
337 IF (x_status <> -1) THEN
338 FTE_UTIL_PKG.EXIT_DEBUG(l_module_name);
339 RETURN;
340 END IF;
341 END IF;
342
343 --now the body of the block
344 FOR i IN p_block_data.FIRST..p_block_data.LAST LOOP
345 l_values := p_block_data(i);
346
347 FTE_VALIDATION_PKG.VALIDATE_RATE_BREAK(p_values => l_values,
348 p_line_number => p_line_number+i+1,
349 p_qp_list_line_tbl => g_qp_list_line_tbl,
350 p_qp_pricing_attrib_tbl => g_qp_pricing_attrib_tbl,
351 p_validate => p_validate,
352 x_status => x_status,
353 x_error_msg => x_error_msg);
354
355 IF (x_status <> -1) THEN
356 FTE_UTIL_PKG.EXIT_DEBUG(l_module_name);
357 RETURN;
358 END IF;
359
360 END LOOP;
361 FTE_UTIL_PKG.EXIT_DEBUG(l_module_name);
362
363 EXCEPTION
364 WHEN OTHERS THEN
365 FTE_UTIL_PKG.WRITE_OUTFILE(p_module_name => l_module_name,
366 p_msg => sqlerrm,
367 p_category => 'O');
368 FTE_UTIL_PKG.EXIT_DEBUG(l_module_name);
369 x_status := 1;
370 RETURN;
371 END PROCESS_RATE_BREAK;
372
373 ----------------------------------------------------------------------------
374 -- PROCEDURE PROCESS_RATING_ATTRIBUTE
375 --
376 -- Purpose: process the lines in p_table for rate chart line attribute
377 --
378 -- IN parameters:
379 -- 1. p_block_header: pl/sql table of STRINGARRAY containing the block information
380 -- 2. p_block_data: pl/sql table of the data
381 -- 3. p_line_number: line number for the beginning of the block
382 -- 4. p_validate_column: boolean for calling VALIDATE_COLUMNS, default true
383 --
384 -- OUT parameters:
385 -- 1. x_status: status of the processing, -1 means no error
386 -- 2. x_error_msg: error message if any.
387 ----------------------------------------------------------------------------
388
389 PROCEDURE PROCESS_RATING_ATTRIBUTE(p_block_header IN FTE_BULKLOAD_PKG.block_header_tbl,
390 p_block_data IN FTE_BULKLOAD_PKG.block_data_tbl,
391 p_line_number IN NUMBER,
392 p_validate_column IN BOOLEAN DEFAULT TRUE,
393 x_status OUT NOCOPY NUMBER,
394 x_error_msg OUT NOCOPY VARCHAR2) IS
395
396 l_values FTE_BULKLOAD_PKG.data_values_tbl;
397 l_module_name CONSTANT VARCHAR2(100) := 'fte.plsql.' || G_PKG_NAME || '.PROCESS_RATING_ATTRIBUTE';
398 BEGIN
399 FTE_UTIL_PKG.ENTER_DEBUG(l_module_name);
400
401 x_status := -1;
402
403 IF (g_list_header_deleted) THEN
404 FTE_UTIL_PKG.EXIT_DEBUG(l_module_name);
405 RETURN;
406 END IF;
407
408 IF (p_validate_column) THEN
409 --verify the column name
410 FTE_VALIDATION_PKG.VALIDATE_COLUMNS(p_keys => p_block_header,
411 p_type => 'RATING_ATTRIBUTE',
412 p_line_number => p_line_number+1,
413 x_status => x_status,
414 x_error_msg => x_error_msg);
415 IF (x_status <> -1) THEN
416 FTE_UTIL_PKG.EXIT_DEBUG(l_module_name);
417 RETURN;
418 END IF;
419 END IF;
420
421 --now the body of the block
422 FOR i IN p_block_data.FIRST..p_block_data.LAST LOOP
423 l_values := p_block_data(i);
424
425 FTE_VALIDATION_PKG.VALIDATE_RATING_ATTRIBUTE(p_values => l_values,
426 p_line_number => p_line_number+i+1,
427 p_qp_pricing_attrib_tbl => g_qp_pricing_attrib_tbl,
428 x_status => x_status,
429 x_error_msg => x_error_msg);
430
431 IF (x_status <> -1) THEN
432 FTE_UTIL_PKG.EXIT_DEBUG(l_module_name);
433 RETURN;
434 END IF;
435
436 END LOOP;
437 FTE_UTIL_PKG.EXIT_DEBUG(l_module_name);
438
439 EXCEPTION
440 WHEN OTHERS THEN
441 FTE_UTIL_PKG.WRITE_OUTFILE(p_module_name => l_module_name,
442 p_msg => sqlerrm,
443 p_category => 'O');
444 FTE_UTIL_PKG.EXIT_DEBUG(l_module_name);
445 x_status := 1;
446 RETURN;
447 END PROCESS_RATING_ATTRIBUTE;
448
449 ----------------------------------------------------------------------------
450 -- PROCEDURE PROCESS_CHARGES_DISCOUNTS
451 --
452 -- Purpose: process the lines in p_table for charges and discounts header
453 --
454 -- IN parameters:
455 -- 1. p_block_header: pl/sql table of STRINGARRAY containing the block information
456 -- 2. p_block_data: pl/sql table of the data
457 -- 3. p_line_number: line number for the beginning of the block
458 -- 4. p_validate_column: boolean for calling VALIDATE_COLUMNS, default true
459 -- 5. p_validate: boolean for validating data within VALIDATION procedure, default true
460 --
461 -- OUT parameters:
462 -- 1. x_status: status of the processing, -1 means no error
463 -- 2. x_error_msg: error message if any.
464 ----------------------------------------------------------------------------
465
466 PROCEDURE PROCESS_CHARGES_DISCOUNTS(p_block_header IN FTE_BULKLOAD_PKG.block_header_tbl,
467 p_block_data IN FTE_BULKLOAD_PKG.block_data_tbl,
468 p_line_number IN NUMBER,
469 p_validate_column IN BOOLEAN DEFAULT TRUE,
470 p_validate IN BOOLEAN DEFAULT TRUE,
471 x_status OUT NOCOPY NUMBER,
472 x_error_msg OUT NOCOPY VARCHAR2) IS
473
474 l_values FTE_BULKLOAD_PKG.data_values_tbl;
475 l_action VARCHAR2(20);
476 l_carrier_id NUMBER;
477
478 l_module_name CONSTANT VARCHAR2(100) := 'fte.plsql.' || G_PKG_NAME || '.PROCESS_CHARGES_DISCOUNTS';
479 BEGIN
480 FTE_UTIL_PKG.ENTER_DEBUG(l_module_name);
481
482 x_status := -1;
483
484 IF (p_validate_column) THEN
485 --verify the column name
486 FTE_VALIDATION_PKG.VALIDATE_COLUMNS(p_keys => p_block_header,
487 p_type => 'CHARGES_DISCOUNTS',
488 p_line_number => p_line_number+1,
489 x_status => x_status,
490 x_error_msg => x_error_msg);
491 IF (x_status <> -1) THEN
492 FTE_UTIL_PKG.EXIT_DEBUG(l_module_name);
493 RETURN;
494 END IF;
495 END IF;
496
497 --now the body of the block
498 FOR i IN p_block_data.FIRST..p_block_data.LAST LOOP
499 l_values := p_block_data(i);
500
501 FTE_VALIDATION_PKG.VALIDATE_RATE_CHART(p_values => l_values,
502 p_line_number => p_line_number+i+1,
503 p_qp_list_header_tbl => g_qp_list_header_tbl,
504 p_qp_qualifier_tbl => g_qp_qualifier_tbl,
505 p_action => l_action,
506 p_carrier_id => l_carrier_id,
507 p_validate => p_validate,
508 x_status => x_status,
509 x_error_msg => x_error_msg);
510
511 IF (x_status <> -1) THEN
512 FTE_UTIL_PKG.EXIT_DEBUG(l_module_name);
513 RETURN;
514 END IF;
515 g_carrier_id := l_carrier_id;
516
517 IF (l_action = 'DELETE') THEN
518 FTE_RATE_CHART_PKG.DELETE_FROM_QP(p_list_header_id => g_qp_list_header_tbl(g_qp_list_header_tbl.COUNT).list_header_id,
519 p_name => g_qp_list_header_tbl(g_qp_list_header_tbl.COUNT).name,
520 p_action => l_action,
521 p_line_number => p_line_number+i+1,
522 x_error_msg => x_error_msg,
523 x_status => x_status );
524
525 g_list_header_deleted := true;
526 ELSIF (l_action = 'UPDATE') THEN
527
528 FTE_RATE_CHART_PKG.DELETE_FROM_QP(p_list_header_id => g_qp_list_header_tbl(g_qp_list_header_tbl.COUNT).list_header_id,
529 p_name => g_qp_list_header_tbl(g_qp_list_header_tbl.COUNT).name,
530 p_action => l_action,
531 p_line_number => p_line_number+i+1,
532 x_error_msg => x_error_msg,
533 x_status => x_status );
534 END IF;
535
536 IF (x_status <> -1) THEN
537 FTE_UTIL_PKG.EXIT_DEBUG(l_module_name);
538 RETURN;
539 END IF;
540
541 END LOOP;
542
543 FTE_UTIL_PKG.EXIT_DEBUG(l_module_name);
544
545 EXCEPTION
546 WHEN OTHERS THEN
547 FTE_UTIL_PKG.WRITE_OUTFILE(p_module_name => l_module_name,
548 p_msg => sqlerrm,
549 p_category => 'O');
550 FTE_UTIL_PKG.EXIT_DEBUG(l_module_name);
551 x_status := 1;
552 RETURN;
553 END PROCESS_CHARGES_DISCOUNTS;
554
555 ----------------------------------------------------------------------------
556 -- PROCEDURE PROCESS_CHARGES_DISCOUNTS_LINE
557 --
558 -- Purpose: process the lines in p_table for charges and discounts line
559 --
560 -- IN parameters:
561 -- 1. p_block_header: pl/sql table of STRINGARRAY containing the block information
562 -- 2. p_block_data: pl/sql table of the data
563 -- 3. p_line_number: line number for the beginning of the block
564 -- 4. p_validate_column: boolean for calling VALIDATE_COLUMNS, default true
565 -- 5. p_validate: boolean for validating data within VALIDATION procedure, default true
566 --
567 -- OUT parameters:
568 -- 1. x_status: status of the processing, -1 means no error
569 -- 2. x_error_msg: error message if any.
570 ----------------------------------------------------------------------------
571
572 PROCEDURE PROCESS_CHARGES_DISCOUNTS_LINE (p_block_header IN FTE_BULKLOAD_PKG.block_header_tbl,
573 p_block_data IN FTE_BULKLOAD_PKG.block_data_tbl,
574 p_line_number IN NUMBER,
575 p_validate_column IN BOOLEAN DEFAULT TRUE,
576 p_validate IN BOOLEAN DEFAULT TRUE,
577 x_status OUT NOCOPY NUMBER,
578 x_error_msg OUT NOCOPY VARCHAR2) IS
579
580 l_values FTE_BULKLOAD_PKG.data_values_tbl;
581 l_module_name CONSTANT VARCHAR2(100) := 'fte.plsql.' || G_PKG_NAME || '.PROCESS_CHARGES_DISCOUNTS_LINE';
582 BEGIN
583 FTE_UTIL_PKG.ENTER_DEBUG(l_module_name);
584
585 x_status := -1;
586
587
588 IF (g_list_header_deleted) THEN
589 FTE_UTIL_PKG.EXIT_DEBUG(l_module_name);
590 RETURN;
591 END IF;
592
593 --verify the column name
594 IF (p_validate_column) THEN
595 FTE_VALIDATION_PKG.VALIDATE_COLUMNS(p_keys => p_block_header,
596 p_type => 'CHARGES_DISCOUNTS_LINE',
597 p_line_number => p_line_number+1,
598 x_status => x_status,
599 x_error_msg => x_error_msg);
600 IF (x_status <> -1) THEN
601 FTE_UTIL_PKG.EXIT_DEBUG(l_module_name);
602 RETURN;
603 END IF;
604 END IF;
605
606 --now the body of the block
607 FOR i IN p_block_data.FIRST..p_block_data.LAST LOOP
608 l_values := p_block_data(i);
609
610 FTE_VALIDATION_PKG.VALIDATE_RATE_LINE(p_values => l_values,
611 p_line_number => p_line_number+i+1,
612 p_qp_list_line_tbl => g_qp_list_line_tbl,
613 p_qp_pricing_attrib_tbl => g_qp_pricing_attrib_tbl,
614 p_validate => p_validate,
615 x_status => x_status,
616 x_error_msg => x_error_msg);
617
618 IF (x_status <> -1) THEN
619 FTE_UTIL_PKG.EXIT_DEBUG(l_module_name);
620 RETURN;
621 END IF;
622
623 END LOOP;
624 FTE_UTIL_PKG.EXIT_DEBUG(l_module_name);
625
626 EXCEPTION
627 WHEN OTHERS THEN
628 FTE_UTIL_PKG.WRITE_OUTFILE(p_module_name => l_module_name,
629 p_msg => sqlerrm,
630 p_category => 'O');
631 FTE_UTIL_PKG.EXIT_DEBUG(l_module_name);
632 x_status := 1;
633 RETURN;
634 END PROCESS_CHARGES_DISCOUNTS_LINE;
635
636 ----------------------------------------------------------------------------
637 -- PROCEDURE PROCESS_ADJUSTED_RATE_CHART
638 --
639 -- Purpose: process the lines in p_table for charges and discounts' rate chart
640 --
641 -- IN parameters:
642 -- 1. p_block_header: pl/sql table of STRINGARRAY containing the block information
643 -- 2. p_block_data: pl/sql table of the data
644 -- 3. p_line_number: line number for the beginning of the block
645 --
646 -- OUT parameters:
647 -- 1. x_status: status of the processing, -1 means no error
648 -- 2. x_error_msg: error message if any.
649 ----------------------------------------------------------------------------
650
651 PROCEDURE PROCESS_ADJUSTED_RATE_CHART (p_block_header IN FTE_BULKLOAD_PKG.block_header_tbl,
652 p_block_data IN FTE_BULKLOAD_PKG.block_data_tbl,
653 p_line_number IN NUMBER,
654 x_status OUT NOCOPY NUMBER,
655 x_error_msg OUT NOCOPY VARCHAR2) IS
656
657 l_values FTE_BULKLOAD_PKG.data_values_tbl;
658 l_module_name CONSTANT VARCHAR2(100) := 'fte.plsql.' || G_PKG_NAME || '.PROCESS_ADJUSTED_RATE_CHART';
659 BEGIN
660 FTE_UTIL_PKG.ENTER_DEBUG(l_module_name);
661
662 x_status := -1;
663
664
665 IF (g_list_header_deleted) THEN
666 FTE_UTIL_PKG.EXIT_DEBUG(l_module_name);
667 RETURN;
668 END IF;
669
670 --verify the column name
671 FTE_VALIDATION_PKG.VALIDATE_COLUMNS(p_keys => p_block_header,
672 p_type => 'ADJUSTED_RATE_CHART',
673 p_line_number => p_line_number+1,
674 x_status => x_status,
675 x_error_msg => x_error_msg);
676 IF (x_status <> -1) THEN
677 FTE_UTIL_PKG.EXIT_DEBUG(l_module_name);
678 RETURN;
679 END IF;
680
681 --now the body of the block
682 FOR i IN p_block_data.FIRST..p_block_data.LAST LOOP
683 l_values := p_block_data(i);
684
685 FTE_VALIDATION_PKG.VALIDATE_ADJUSTED_RATE_CHART(p_values => l_values,
686 p_line_number => p_line_number+i+1,
687 p_carrier_id => g_carrier_id,
688 p_qp_qualifier_tbl => g_qp_qualifier_tbl,
689 x_status => x_status,
690 x_error_msg => x_error_msg);
691
692 IF (x_status <> -1) THEN
693 FTE_UTIL_PKG.EXIT_DEBUG(l_module_name);
694 RETURN;
695 END IF;
696
697 END LOOP;
698 FTE_UTIL_PKG.EXIT_DEBUG(l_module_name);
699
700 EXCEPTION
701 WHEN OTHERS THEN
702 FTE_UTIL_PKG.WRITE_OUTFILE(p_module_name => l_module_name,
703 p_msg => sqlerrm,
704 p_category => 'O');
705 FTE_UTIL_PKG.EXIT_DEBUG(l_module_name);
706 x_status := 1;
707 RETURN;
708 END PROCESS_ADJUSTED_RATE_CHART;
709
710 ----------------------------------------------------------------------------
711 -- PROCEDURE PROCESS_QUALIFIER
712 --
713 -- Purpose: process the qualifiers for TL Rate Chart
714 --
715 -- IN parameters:
716 -- 1. p_block_header: pl/sql table of STRINGARRAY containing the block information
717 -- 2. p_block_data: pl/sql table of the data
718 -- 3. p_line_number: line number for the beginning of the block
719 --
720 -- OUT parameters:
721 -- 1. x_status: status of the processing, -1 means no error
722 -- 2. x_error_msg: error message if any.
723 ----------------------------------------------------------------------------
724
725 PROCEDURE PROCESS_QUALIFIER(p_block_header IN FTE_BULKLOAD_PKG.block_header_tbl,
726 p_block_data IN FTE_BULKLOAD_PKG.block_data_tbl,
727 p_line_number IN NUMBER,
728 x_status OUT NOCOPY NUMBER,
729 x_error_msg OUT NOCOPY VARCHAR2) IS
730 l_values FTE_BULKLOAD_PKG.data_values_tbl;
731 l_module_name CONSTANT VARCHAR2(100) := 'fte.plsql.' || G_PKG_NAME || '.PROCESS_QUALIFIER';
732 BEGIN
733 FTE_UTIL_PKG.ENTER_DEBUG(l_module_name);
734
735 x_status := -1;
736
737 --now the body of the block
738 FOR i IN p_block_data.FIRST..p_block_data.LAST LOOP
739 l_values := p_block_data(i);
740
741 FTE_VALIDATION_PKG.VALIDATE_QUALIFIER(p_values => l_values,
742 p_line_number => p_line_number+i+1,
743 p_qp_qualifier_tbl => g_qp_qualifier_tbl,
744 x_status => x_status,
745 x_error_msg => x_error_msg);
746
747 IF (x_status <> -1) THEN
748 FTE_UTIL_PKG.EXIT_DEBUG(l_module_name);
749 RETURN;
750 END IF;
751 END LOOP;
752 FTE_UTIL_PKG.EXIT_DEBUG(l_module_name);
753 EXCEPTION
754 WHEN OTHERS THEN
755 FTE_UTIL_PKG.WRITE_OUTFILE(p_module_name => l_module_name,
756 p_msg => sqlerrm,
757 p_category => 'O');
758 FTE_UTIL_PKG.EXIT_DEBUG(l_module_name);
759 x_status := 1;
760 RETURN;
761 END PROCESS_QUALIFIER;
762
763 ----------------------------------------------------------------------------
764 -- PROCEDURE ADD_ATTRIBUTE
765 --
766 -- Purpose: process the attributes from the UI by directly adding it to pricing attrib tbl
767 --
768 -- IN parameters:
769 -- 1. p_pricing_attribute: type of the attribute
770 -- 2. p_attr_value_from: value of the attribute
771 -- 3. p_linenum: line number
772 --
773 -- OUT parameters:
774 -- 1. x_status: status of the processing, -1 means no error
775 -- 2. x_error_msg: error message if any.
776 ----------------------------------------------------------------------------
777
778 PROCEDURE ADD_ATTRIBUTE(p_pricing_attribute IN VARCHAR2,
779 p_attr_value_from IN VARCHAR2,
780 p_line_number IN NUMBER,
781 x_status OUT NOCOPY NUMBER,
782 x_error_msg OUT NOCOPY VARCHAR2) IS
783 l_module_name CONSTANT VARCHAR2(100) := 'fte.plsql.' || G_PKG_NAME || '.ADD_ATTRIBUTE';
784 BEGIN
785 FTE_UTIL_PKG.ENTER_DEBUG(l_module_name);
786
787 x_status := -1;
788
789 FTE_VALIDATION_PKG.ADD_ATTRIBUTE(p_pricing_attribute => p_pricing_attribute,
790 p_attr_value_from => p_attr_value_from,
791 p_attr_value_to => NULL,
792 p_line_number => p_line_number,
793 p_context => 'LOGISTICS',
794 p_comp_operator => NULL,
795 p_qp_pricing_attrib_tbl => g_qp_pricing_attrib_tbl,
796 x_status => x_status,
797 x_error_msg => x_error_msg);
798 FTE_UTIL_PKG.EXIT_DEBUG(l_module_name);
799 EXCEPTION
800 WHEN OTHERS THEN
801 FTE_UTIL_PKG.WRITE_OUTFILE(p_module_name => l_module_name,
802 p_msg => sqlerrm,
803 p_category => 'O');
804 FTE_UTIL_PKG.EXIT_DEBUG(l_module_name);
805 x_status := 1;
806 RETURN;
807 END ADD_ATTRIBUTE;
808
809 ----------------------------------------------------------------------
810 -- PROCEDURE SUBMIT_QP_PROCESS
811 --
812 -- Purpose: insert all pl/sql tables into QP_INTERFACE_* tables
813 ----------------------------------------------------------------------
814
815 PROCEDURE SUBMIT_QP_PROCESS(p_qp_call IN BOOLEAN DEFAULT TRUE,
816 x_status OUT NOCOPY NUMBER,
817 x_error_msg OUT NOCOPY VARCHAR2) IS
818 l_module_name CONSTANT VARCHAR2(100) := 'fte.plsql.' || G_PKG_NAME || '.SUBMIT_QP_PROCESS';
819 BEGIN
820 FTE_UTIL_PKG.ENTER_DEBUG(l_module_name);
821
822 x_status := -1;
823
824 IF (g_list_header_deleted) THEN
825 FTE_UTIL_PKG.EXIT_DEBUG(l_module_name);
826 RETURN;
827 END IF;
828
829 -- have to check if the lanes that has no commodity have to have basis
830 FTE_RATE_CHART_PKG.INSERT_QP_INTERFACE_TABLES(p_qp_list_header_tbl => g_qp_list_header_tbl,
831 p_qp_list_line_tbl => g_qp_list_line_tbl,
832 p_qp_qualifier_tbl => g_qp_qualifier_tbl,
833 p_qp_pricing_attrib_tbl => g_qp_pricing_attrib_tbl,
834 p_qp_call => p_qp_call,
835 x_status => x_status,
836 x_error_msg => x_error_msg);
837 FTE_UTIL_PKG.EXIT_DEBUG(l_module_name);
838
839 EXCEPTION
840 WHEN OTHERS THEN
841 FTE_UTIL_PKG.WRITE_OUTFILE(p_module_name => l_module_name,
842 p_msg => sqlerrm,
843 p_category => 'O');
844 FTE_UTIL_PKG.EXIT_DEBUG(l_module_name);
845 x_status := 1;
846 RETURN;
847
848 END SUBMIT_QP_PROCESS;
849
850 ----------------------------------------------------------------------
851 -- PROCEDURE INIT_QP_TABLES
852 --
853 -- Purpose: initialize all pl/sql tables
854 ----------------------------------------------------------------------
855
856 PROCEDURE INIT_QP_TABLES IS
857 BEGIN
858
859 g_qp_list_header_tbl.DELETE;
860 g_qp_list_line_tbl.DELETE;
861 g_qp_qualifier_tbl.DELETE;
862 g_qp_pricing_attrib_tbl.DELETE;
863
864 END INIT_QP_TABLES;
865
866
867 END FTE_RATE_CHART_LOADER;