1 PACKAGE FTE_FREIGHT_PRICING as
2 /* $Header: FTEFRPRS.pls 120.2 2005/11/16 14:38:37 mechawla noship $ */
3
4 -- Global Variables
5
6 g_package_name CONSTANT VARCHAR2(50) := 'FTE_FREIGHT_PRICING';
7
8 g_hash_base NUMBER := 1;
9 g_hash_size NUMBER := power(2, 25);
10
11 G_CONTAINER_BASIS NUMBER := 1;
12 G_VOLUME_BASIS NUMBER := 2;
13 G_WEIGHT_BASIS NUMBER := 3;
14
15 G_OBJECTIVE_HIGHEST VARCHAR2(30) := 'HIGHEST';
16 G_OBJECTIVE_LOWEST VARCHAR2(30) := 'LOWEST';
17
18 G_GROUPING_SHIPMENT VARCHAR2(30) := 'SHIPMENT';
19 G_GROUPING_CONTAINER VARCHAR2(30) := 'CONTAINER';
20
21 G_AGGREGATION_WITHIN VARCHAR2(30) := 'WITHIN';
22 G_AGGREGATION_ACROSS VARCHAR2(30) := 'ACROSS';
23
24 G_PATTERN_1 NUMBER := 1;
25 G_PATTERN_1_NAME VARCHAR2(30) := 'SC_CB';
26 G_PATTERN_2 NUMBER := 2;
27 G_PATTERN_2_NAME VARCHAR2(30) := 'SC_WB';
28 G_PATTERN_3 NUMBER := 3;
29 G_PATTERN_3_NAME VARCHAR2(30) := 'SC_VB';
30 G_PATTERN_4 NUMBER := 4;
31 G_PATTERN_4_NAME VARCHAR2(30) := 'MC_CB';
32 G_PATTERN_5 NUMBER := 5;
33 G_PATTERN_5_NAME VARCHAR2(30) := 'MC_WB';
34 G_PATTERN_6 NUMBER := 6;
35 G_PATTERN_6_NAME VARCHAR2(30) := 'MC_VB';
36 G_PATTERN_7 NUMBER := 7;
37 G_PATTERN_7_NAME VARCHAR2(30) := 'MC_MY';
38 G_PATTERN_8 NUMBER := 8;
39 G_PATTERN_8_NAME VARCHAR2(30) := 'MC_MN';
40 G_PATTERN_9 NUMBER := 9;
41 G_PATTERN_9_NAME VARCHAR2(30) := 'LI_WB';
42 G_PATTERN_10 NUMBER := 10;
43 G_PATTERN_10_NAME VARCHAR2(30) := 'LI_VB';
44
45 -- pricing events
46 G_LINE_EVENT_NUM NUMBER := 1;
47 G_CHARGE_EVENT_NUM NUMBER := 2;
48 G_LINE_EVENT_CODE VARCHAR2(30) := 'LINE';
49 G_CHARGE_EVENT_CODE VARCHAR2(30) := 'PRICE_LOAD'; -- should have a proper value (say FTE_CHARGE_EVENT)
50
51 TYPE shipment_line_rec_type IS RECORD
52 (delivery_detail_id NUMBER,
53 delivery_id NUMBER,
54 delivery_leg_id NUMBER,
55 reprice_required VARCHAR2(1),
56 parent_delivery_detail_id NUMBER,
57 customer_id NUMBER,
58 sold_to_contact_id NUMBER,
59 inventory_item_id NUMBER,
60 item_description VARCHAR2(250),
61 hazard_class_id NUMBER,
62 country_of_origin VARCHAR2(50),
63 classification VARCHAR2(30),
64 requested_quantity NUMBER,
65 requested_quantity_uom VARCHAR2(3),
66 master_container_item_id NUMBER,
67 detail_container_item_id NUMBER,
68 customer_item_id NUMBER,
69 net_weight NUMBER,
70 organization_id NUMBER,
71 container_flag VARCHAR2(1),
72 container_type_code VARCHAR2(30),
73 container_name VARCHAR2(30),
74 fill_percent NUMBER,
75 gross_weight NUMBER,
76 currency_code VARCHAR2(15),
77 freight_class_cat_id NUMBER,
78 commodity_code_cat_id NUMBER,
79 weight_uom_code VARCHAR2(3),
80 volume NUMBER,
81 volume_uom_code VARCHAR2(3),
82 tp_attribute_category VARCHAR2(240),
83 tp_attribute1 VARCHAR2(240),
84 tp_attribute2 VARCHAR2(240),
85 tp_attribute3 VARCHAR2(240),
86 tp_attribute4 VARCHAR2(240),
87 tp_attribute5 VARCHAR2(240),
88 tp_attribute6 VARCHAR2(240),
89 tp_attribute7 VARCHAR2(240),
90 tp_attribute8 VARCHAR2(240),
91 tp_attribute9 VARCHAR2(240),
92 tp_attribute10 VARCHAR2(240),
93 tp_attribute11 VARCHAR2(240),
94 tp_attribute12 VARCHAR2(240),
95 tp_attribute13 VARCHAR2(240),
96 tp_attribute14 VARCHAR2(240),
97 tp_attribute15 VARCHAR2(240),
98 attribute_category VARCHAR2(150),
99 attribute1 VARCHAR2(150),
100 attribute2 VARCHAR2(150),
101 attribute3 VARCHAR2(150),
102 attribute4 VARCHAR2(150),
103 attribute5 VARCHAR2(150),
104 attribute6 VARCHAR2(150),
105 attribute7 VARCHAR2(150),
106 attribute8 VARCHAR2(150),
107 attribute9 VARCHAR2(150),
108 attribute10 VARCHAR2(150),
109 attribute11 VARCHAR2(150),
110 attribute12 VARCHAR2(150),
111 attribute13 VARCHAR2(150),
112 attribute14 VARCHAR2(150),
113 attribute15 VARCHAR2(150),
114 source_type VARCHAR2(10), --new for om estimation
115 source_line_id NUMBER, --new for om estimation
116 source_header_id NUMBER, --new for om estimation
117 source_consolidation_id NUMBER, --new for om estimation
118 ship_date DATE, --new for om estimation
119 arrival_date DATE, --new for om estimation
120 comm_category_id NUMBER, -- new for FTE J FTE Estimation
121 assignment_type VARCHAR2(1) ,-- MDC TL
122 parent_delivery_id NUMBER,--MDC TL
123 parent_delivery_leg_id NUMBER--MDC TL
124 );
125
126 TYPE shipment_line_tab_type IS TABLE OF shipment_line_rec_type INDEX BY BINARY_INTEGER;
127
128 -- This is a global table which can be populated with delivery detail records from either
129 -- of the APIs shipment_price_compare or shipment_price_consolidate or shipment_price_calculate
130 -- This table should be indexed on delivery_detail_id for better performance
131
132 g_shipment_line_rows shipment_line_tab_type;
133
134 TYPE Freight_Cost_Temp_Rec_Type IS RECORD (
135 FREIGHT_COST_ID NUMBER
136 , FREIGHT_COST_TYPE_ID NUMBER
137 , UNIT_AMOUNT NUMBER
138 , CALCULATION_METHOD VARCHAR2(15)
139 , UOM VARCHAR2(15)
140 , QUANTITY NUMBER
141 , TOTAL_AMOUNT NUMBER
142 , CURRENCY_CODE VARCHAR2(15)
143 , CONVERSION_DATE DATE
144 , CONVERSION_RATE NUMBER
145 , CONVERSION_TYPE_CODE VARCHAR2(30)
146 , TRIP_ID NUMBER
147 , STOP_ID NUMBER
148 , DELIVERY_ID NUMBER
149 , DELIVERY_LEG_ID NUMBER
150 , DELIVERY_DETAIL_ID NUMBER
151 , ATTRIBUTE_CATEGORY VARCHAR2(150)
152 , ATTRIBUTE1 VARCHAR2(150)
153 , ATTRIBUTE2 VARCHAR2(150)
154 , ATTRIBUTE3 VARCHAR2(150)
155 , ATTRIBUTE4 VARCHAR2(150)
156 , ATTRIBUTE5 VARCHAR2(150)
157 , ATTRIBUTE6 VARCHAR2(150)
158 , ATTRIBUTE7 VARCHAR2(150)
159 , ATTRIBUTE8 VARCHAR2(150)
160 , ATTRIBUTE9 VARCHAR2(150)
161 , ATTRIBUTE10 VARCHAR2(150)
162 , ATTRIBUTE11 VARCHAR2(150)
163 , ATTRIBUTE12 VARCHAR2(150)
164 , ATTRIBUTE13 VARCHAR2(150)
165 , ATTRIBUTE14 VARCHAR2(150)
166 , ATTRIBUTE15 VARCHAR2(150)
167 , CREATION_DATE DATE
168 , CREATED_BY NUMBER
169 , LAST_UPDATE_DATE DATE
170 , LAST_UPDATED_BY NUMBER
171 , LAST_UPDATE_LOGIN NUMBER
172 , PROGRAM_APPLICATION_ID NUMBER
173 , PROGRAM_ID NUMBER
174 , PROGRAM_UPDATE_DATE DATE
175 , REQUEST_ID NUMBER
176 , LINE_TYPE_CODE VARCHAR2(30)
177 , PRICING_LIST_HEADER_ID NUMBER
178 , PRICING_LIST_LINE_ID NUMBER
179 , APPLIED_TO_CHARGE_ID NUMBER
180 , CHARGE_UNIT_VALUE NUMBER
181 , CHARGE_SOURCE_CODE VARCHAR2(30)
182 , ESTIMATED_FLAG VARCHAR2(1)
183 , COMPARISON_REQUEST_ID NUMBER
184 , LANE_ID NUMBER
185 , SCHEDULE_ID NUMBER
186 , MOVED_TO_MAIN_FLAG VARCHAR2(1)
187 , SERVICE_TYPE_CODE VARCHAR2(30)
188 , COMMODITY_CATEGORY_ID NUMBER
189 , BILLABLE_BASIS VARCHAR2 (30)
190 , BILLABLE_UOM VARCHAR2 (15)
191 , BILLABLE_QUANTITY NUMBER
192 , VEHICLE_TYPE_ID NUMBER
193 );
194
195 TYPE Freight_Cost_Temp_Tab_Type IS TABLE OF Freight_Cost_Temp_Rec_Type INDEX BY BINARY_INTEGER;
196
197 TYPE top_level_fc_rec_type IS RECORD
198 (delivery_detail_id NUMBER ,
199 delivery_leg_id NUMBER ,
200 line_type_code VARCHAR2(30),
201 freight_cost_type_id NUMBER ,
202 applied_to_charge_id NUMBER DEFAULT NULL, -- populated only for charges
203 currency_code VARCHAR2(30) ,
204 quantity NUMBER ,
205 uom VARCHAR2(30),
206 charge_unit_value NUMBER,
207 unit_amount NUMBER DEFAULT NULL,
208 total_amount NUMBER
209 );
210
211 TYPE top_level_fc_tab_type IS TABLE OF top_level_fc_rec_type INDEX BY BINARY_INTEGER;
212
213 TYPE rolledup_line_rec_type IS RECORD
214 (delivery_detail_id NUMBER,
215 item_id NUMBER,
216 category_id NUMBER,
217 rate_basis NUMBER,
218 container_id NUMBER,
219 master_container_id NUMBER,
220 line_quantity NUMBER,
221 line_uom VARCHAR2(30));
222
223 TYPE rolledup_line_tab_type IS TABLE OF rolledup_line_rec_type INDEX BY BINARY_INTEGER;
224
225 g_rolledup_lines rolledup_line_tab_type;
226
227 TYPE shpmnt_content_rec_type IS RECORD
228 (content_id NUMBER , -- Container/loose item id (DDetail id)
229 delivery_leg_id NUMBER,
230 container_flag VARCHAR2(1),
231 weight_uom VARCHAR2(30),
232 volume_uom VARCHAR2(30),
233 dim_uom mtl_system_items.dimension_uom_code%type,
234 gross_weight NUMBER,
235 volume NUMBER,
236 length NUMBER,
237 width NUMBER,
238 height NUMBER,
239 -- added for J+ Container_all
240 container_type_code VARCHAR2(30),
241
242 -- added for J+ Flat shipment rate
243 -- added for J+ LTL rating to include container weight
244 wdd_volume NUMBER,
245 wdd_volume_uom_code VARCHAR2(3),
246 wdd_net_weight NUMBER,
247 wdd_gross_weight NUMBER,
248 wdd_tare_weight NUMBER,
249 wdd_weight_uom_code VARCHAR2(3)
250 );
251
252 TYPE shpmnt_content_tab_type IS TABLE OF shpmnt_content_rec_type INDEX BY BINARY_INTEGER;
253
254 TYPE addl_services_rec_type IS RECORD
255 (service_line_index NUMBER ,
256 content_id NUMBER ,
257 freight_cost_type_code VARCHAR2(200),
258 freight_cost_type_id NUMBER
259 );
260
261 TYPE addl_services_tab_type IS TABLE OF addl_services_rec_type INDEX BY BINARY_INTEGER;
262
263 -- How do we store the shpmnt_content_rec_type - instance/QP line? relationship as it is many-to-many ?
264
265 TYPE top_level_pattern_rec_type IS RECORD
266 (pattern_index NUMBER , -- Do we need this ?
267 pattern_no NUMBER, -- Assign NUMBERs to 8 possible patterns
268 services_hash VARCHAR2(100),
269 content_id NUMBER , -- Container/loose item id (DDetail id)
270 instance_index NUMBER
271 );
272
273 TYPE top_level_pattern_tab_type IS TABLE OF top_level_pattern_rec_type INDEX BY BINARY_INTEGER;
274
275 TYPE pricing_dual_instance_rec_type IS RECORD
276 (instance_index NUMBER ,
277 pattern_no NUMBER, -- Assign NUMBERs to 9 possible patterns
278 -- Consolidation possible only for same patterns
279 services_hash VARCHAR2(100),
280 grouping_level VARCHAR2(60) ,
281 aggregation VARCHAR2(60) DEFAULT NULL,
282 objective VARCHAR2(60) DEFAULT NULL,
286
283 count_pattern NUMBER ,
284 loose_item_flag VARCHAR2(1) DEFAULT 'N' --new for loose item
285 );
287 TYPE pricing_dual_instance_tab_type IS TABLE OF pricing_dual_instance_rec_type INDEX BY BINARY_INTEGER;
288
289 TYPE pricing_engine_input_rec_type IS RECORD
290 (input_index NUMBER , -- Same as QP engine line_index ?
291 instance_index NUMBER , -- Origin pricing dual instance. Can be more than one input rec only in case of pricing objective consideration/percel hundredwt.
292 category_id NUMBER DEFAULT NULL, -- Populated for WITHIN
293 basis NUMBER DEFAULT NULL, -- Populated for ACROSS
294 loose_item_id NUMBER DEFAULT NULL, -- Populated for Loose Item
295 line_quantity NUMBER ,
296 line_uom VARCHAR2(60),
297 input_set_number NUMBER DEFAULT 1, -- indentifies an input set (for stuff like parcel hundred wt),
298
299 -- added for J+ Container_all
300 container_type_code VARCHAR2(30),
301 loose_item_flag VARCHAR2(1) DEFAULT 'N'
302 );
303
304 TYPE pricing_engine_input_tab_type IS TABLE OF pricing_engine_input_rec_type INDEX BY BINARY_INTEGER;
305
306 TYPE pricing_control_input_rec_type IS RECORD (
307 pricing_event_num NUMBER,
308 currency_code VARCHAR2(30),
309 lane_id NUMBER,
310 price_list_id NUMBER,
311 party_id NUMBER
312 );
313
314
315 TYPE pricing_engine_def_rec_type IS RECORD (
316 pricing_event_num NUMBER, --index
317 pricing_event_code VARCHAR2(30),
318 request_type_code VARCHAR2(30),
319 line_type_code VARCHAR2(30),
320 price_flag VARCHAR2(1)
321 );
322
323 TYPE pricing_engine_def_tab_type IS TABLE OF pricing_engine_def_rec_type INDEX BY BINARY_INTEGER;
324
325 TYPE fte_qual_rec_type IS RECORD
326 (supplier_id NUMBER ,
327 pricelist_id NUMBER
328 );
329
330 TYPE pricing_attribute_rec_type IS RECORD
331 (attribute_index NUMBER ,
332 input_index NUMBER , -- Origin QP engine input line index
333 attribute_name VARCHAR2(60) ,
334 attribute_value VARCHAR2(240)
335 );
336
337 TYPE pricing_attribute_tab_type IS TABLE OF pricing_attribute_rec_type INDEX BY BINARY_INTEGER;
338
339 TYPE Freight_Cost_Main_Tab_Type IS TABLE OF WSH_FREIGHT_COSTS_PVT.Freight_Cost_Rec_Type INDEX BY BINARY_INTEGER;
340
341 TYPE FtePricingInRecType is RECORD (
342 api_version_number NUMBER,
343 delivery_leg_id NUMBER DEFAULT NULL, -- Input only one of these two
344 segment_id NUMBER DEFAULT NULL -- WSH Trip id
345 );
346
347 TYPE effectivity_date_rec IS RECORD (
348 date_from DATE,
349 date_to DATE );
350
351 g_effectivity_dates effectivity_date_rec;
352
353 TYPE lane_info_rec_type IS RECORD -- Make these columns as %TYPE
354 (lane_id NUMBER ,
355 carrier_id NUMBER ,
356 pricelist_id NUMBER ,
357 mode_of_transportation_code VARCHAR2(30) ,
358 origin_id NUMBER ,
359 destination_id NUMBER ,
360 basis VARCHAR2(30) ,
361 commodity_catg_id NUMBER ,
362 service_type_code VARCHAR2(30),
363 classification_code VARCHAR2(10) -- To be added to fte_lanes
364 );
365
366 TYPE delivery_leg_rec_type IS RECORD
367 ( delivery_id NUMBER,
368 delivery_leg_id NUMBER,
369 delivery_name VARCHAR(4000)
370 );
371
372 TYPE delivery_trip_rec_type IS RECORD
373 ( delivery_id NUMBER,
374 trip_id NUMBER,
375 delivery_leg_id NUMBER,
376 delivery_name VARCHAR(4000)
377 );
378
379
380 TYPE DELIVERY_LEG_TAB_TYPE IS TABLE OF delivery_leg_rec_type INDEX BY BINARY_INTEGER;
381
382 TYPE DELIVERY_TRIP_TAB_TYPE IS TABLE OF delivery_trip_rec_type INDEX BY BINARY_INTEGER;
383
384 -- Utility APIs
385
386 FUNCTION is_consolidated (
387 p_segment_id IN NUMBER ) RETURN BOOLEAN;
388
389 FUNCTION get_segment_from_dleg (
390 p_delivery_leg_id IN NUMBER ) RETURN NUMBER;
394
391
392 FUNCTION get_delivery_from_dleg (
393 p_delivery_leg_id IN NUMBER ) RETURN NUMBER;
395 PROCEDURE flatten_shipment (
396 p_delivery_leg_id IN NUMBER DEFAULT NULL, -- Not required
397 x_first_level_rows OUT NOCOPY shpmnt_content_tab_type, -- Will get indexed on delivery_detail_id
398 x_return_status OUT NOCOPY VARCHAR2 ) ;
399
400
401 PROCEDURE shipment_pricing (
402 p_lane_id IN NUMBER DEFAULT NULL,
403 p_schedule_id IN NUMBER DEFAULT NULL,
404 p_segment_id IN NUMBER DEFAULT NULL,-- Input either Lane/schedule or the trip segment
405 p_service_type IN VARCHAR2 DEFAULT NULL, -- service type is required with lane/schedule
406 p_ship_date IN DATE DEFAULT sysdate, -- VVP (09/30/02)
407 p_arrival_date IN DATE DEFAULT sysdate, -- VVP (09/30/02)
408 --p_shpmnt_toplevel_rows IN OUT NOCOPY shpmnt_content_tab_type,
409 p_shpmnt_toplevel_rows IN shpmnt_content_tab_type, /* bug# 2501240 -VVP */
410 p_shpmnt_toplevel_charges IN addl_services_tab_type, -- Top level requested additional services
411 -- p_shpmnt_charges IN shpmnt_charges_tab_type, -- Not supported in Phase I
412 p_save_flag IN VARCHAR2, -- Whether to save to TEMP table or MAIN table
413 p_request_id IN NUMBER DEFAULT NULL, -- Required only in case of saving to TEMP table
414 p_currency_code IN VARCHAR2 DEFAULT NULL,
415 x_summary_lanesched_price OUT NOCOPY NUMBER, -- Only in case of 'T'
416 x_summary_lanesched_price_uom OUT NOCOPY VARCHAR2,
417 x_freight_cost_temp_price OUT NOCOPY Freight_Cost_Temp_Tab_Type,
418 x_freight_cost_temp_charge OUT NOCOPY Freight_Cost_Temp_Tab_Type,
419 x_return_status OUT NOCOPY VARCHAR2 ) ;
420
421 -- shipment_rating will be called by
422 -- WSH-LCSS (FTE_FREIGHT_RATING_DLVY_GRP.Rate_Delivery)
423 -- and OM-LCSS (FTE_FREIGHT_RATING_PUB.Get_Freight_Costs)
424 -- and OM-DisplayChoices (FTE_FREIGHT_RATING_PUB.Get_Services)
425 --
426 -- shipment_rating rate shipments in g_shipment_line_rows on p_lane_id and p_service_type
427 -- if p_mode_of_transport = 'TRUCK' it calls tl_shipment_pricing
428 -- otherwise it calls shipment_pricing
429 -- shipment_rating always returns rates in pl/sql table
430 --
431 PROCEDURE shipment_rating (
432 p_lane_id IN NUMBER,
433 p_service_type IN VARCHAR2,
434 p_mode_of_transport IN VARCHAR2,
435 p_ship_date IN DATE DEFAULT sysdate,
436 p_arrival_date IN DATE DEFAULT sysdate,
437 p_currency_code IN VARCHAR2 DEFAULT NULL,
438 x_summary_lanesched_price OUT NOCOPY NUMBER,
439 x_summary_lanesched_price_uom OUT NOCOPY VARCHAR2,
440 x_freight_cost_temp_price OUT NOCOPY Freight_Cost_Temp_Tab_Type,
441 x_freight_cost_temp_charge OUT NOCOPY Freight_Cost_Temp_Tab_Type,
442 x_return_status OUT NOCOPY VARCHAR2,
443 x_msg_count OUT NOCOPY NUMBER,
444 x_msg_data OUT NOCOPY VARCHAR2 );
445
446 -- FTE Public APIs to be called from Transportation events
447
448 -- This API will result in one qp output line per instance
449 -- It will delete other engine rows and associated engine output line details
450
451 PROCEDURE resolve_pricing_objective(
452 p_pricing_dual_instances IN pricing_dual_instance_tab_type,
453 x_pricing_engine_input IN OUT NOCOPY pricing_engine_input_tab_type,
454 x_qp_output_line_rows IN OUT NOCOPY QP_PREQ_GRP.LINE_TBL_TYPE,
455 x_qp_output_line_details IN OUT NOCOPY QP_PREQ_GRP.LINE_DETAIL_TBL_TYPE,-- Not required
456 x_return_status OUT NOCOPY VARCHAR2);
457
458 -- This API is called directly from DWB
459 -- Modified the signature for 12i. p_delivery_leg_list can contain list of Delivery Ids or Delivery Legs
460 -- Based on p_deliveries_list_type , it'll call different rating modules.
461 -- In R12 behavior of rerating will change as in case of a delivery/dleg , with more
462 -- delivery legs on the same trip we'll call rerating of the complete trip.
463 -- So in case of rerating Trip level rating will be called always instead of individual delivery leg
464
465 PROCEDURE rerate_shipment_online(
466 p_api_version IN NUMBER DEFAULT 1.0,
467 p_init_msg_list IN VARCHAR2 DEFAULT FND_API.G_FALSE,
468 p_commit IN VARCHAR2 DEFAULT FND_API.G_FALSE,
469 p_deliveries_list IN FTE_ID_TAB_TYPE,
470 p_delivery_name_list IN FTE_NAME_TAB_TYPE,
471 p_deliveries_list_type IN VARCHAR2 , -- This will have 'DEL' for Delivery IDs or 'DLEG' for Delivery Leg Ids.
472 x_success_list OUT NOCOPY FTE_ID_TAB_TYPE,
473 x_warning_list OUT NOCOPY FTE_ID_TAB_TYPE,
474 x_fail_list OUT NOCOPY FTE_ID_TAB_TYPE,
475 x_return_status OUT NOCOPY VARCHAR2,
476 x_msg_count OUT NOCOPY NUMBER,
477 x_msg_data OUT NOCOPY VARCHAR2 );
478
479 -- This API is called directly from the shipment repricing concurrent program
480 -- The input to it should be
481 -- either a FTE trip, segment, delivery or a delivery leg
482
483 PROCEDURE shipment_reprice (
487 p_segment_id IN NUMBER DEFAULT NULL,
484 errbuf OUT NOCOPY VARCHAR2,
485 retcode OUT NOCOPY VARCHAR2,
486 p_fte_trip_id IN NUMBER DEFAULT NULL, -- Input only ONE of the following FOUR
488 p_delivery_id IN NUMBER DEFAULT NULL,
489 p_delivery_leg_id IN NUMBER DEFAULT NULL );
490
491 -- This API is called by the shipment repricing concurrent program
492 -- The input to it should be
493 -- either a FTE trip, segment, delivery or a delivery leg
494 -- Calls shipment_price_consolidate API
495
496 PROCEDURE shipment_reprice2 (
497 p_init_prc_log IN VARCHAR2 DEFAULT 'Y',
498 p_fte_trip_id IN NUMBER DEFAULT NULL, -- Input only ONE of the following FOUR
499 p_segment_id IN NUMBER DEFAULT NULL,
500 p_delivery_id IN NUMBER DEFAULT NULL,
501 p_delivery_leg_id IN NUMBER DEFAULT NULL,
502 x_return_status OUT NOCOPY VARCHAR2 ) ;
503
504
505
506 -- This API is called by the shipment_reprice API or WSH online shipment repricing event
507 -- to consolidate shipments on a segment. The input to it should be
508 -- either a delivery leg or a segment
509 -- Calls shipment hierarchy flattening API
510
511 PROCEDURE shipment_price_consolidate (
512 p_init_msg_list IN VARCHAR2 DEFAULT fnd_api.g_true,--Whether to initialize global message table
513 p_in_attributes IN FtePricingInRecType,
514 x_return_status OUT NOCOPY VARCHAR2,
515 x_msg_count OUT NOCOPY NUMBER, -- Standard FND functionality
516 x_msg_data OUT NOCOPY VARCHAR2 ); -- Will return message text only if number of messages = 1
517
518 -- This API is mainly for internal FTE rating use
519 PROCEDURE shipment_price_consolidate (
520 p_delivery_leg_id IN NUMBER DEFAULT NULL, -- Gets either Dleg or wsh trip
521 p_segment_id IN NUMBER DEFAULT NULL,
522 p_check_reprice_flag IN VARCHAR2 DEFAULT 'N',
523 x_return_status OUT NOCOPY VARCHAR2 );
524
525 -- This API is called from the Multi-leg UI for saving chosen shipment price
526 -- The input to it should be either the chosen lane or schedule,
527 -- delivery leg and the comparison request id
528 -- bug : 2763791 : added p_service_type_code
529
530 PROCEDURE Move_fc_temp_to_main (
531 p_init_msg_list IN VARCHAR2 DEFAULT fnd_api.g_true,
532 p_init_prc_log IN VARCHAR2 DEFAULT 'Y',
533 p_request_id IN NUMBER, -- Comparison Request ID to move to main
534 p_delivery_leg_id IN NUMBER,
535 p_lane_id IN NUMBER DEFAULT NULL,
536 p_schedule_id IN NUMBER DEFAULT NULL,
537 p_service_type_code IN VARCHAR2 DEFAULT NULL,
538 x_return_status OUT NOCOPY VARCHAR2);
539
540
541 -- This API is called from FTE_TRIP_RATING_GRP for saving chosen shipment price for a non-TL trip
542 -- The input to it should be either the chosen lane or schedule,
543 -- trip and the comparison request id
544
545
546 PROCEDURE Move_fc_temp_to_main (
547 p_init_msg_list IN VARCHAR2 DEFAULT fnd_api.g_true,
548 p_init_prc_log IN VARCHAR2 DEFAULT 'Y',
549 p_request_id IN NUMBER, -- Comparison Request ID to move to main
550 p_trip_id IN NUMBER,
551 p_lane_id IN NUMBER DEFAULT NULL,
552 p_schedule_id IN NUMBER DEFAULT NULL,
553 p_service_type_code IN VARCHAR2 DEFAULT NULL,
554 x_return_status OUT NOCOPY VARCHAR2);
555
556
557 -- This API is called by Rate_Delivery of LCSS project
558 -- To move freight costs from pl/sql table to wsh_freight_costs
559
560 PROCEDURE Move_fc_temp_to_main (
561 p_delivery_leg_id IN NUMBER,
562 p_freight_cost_temp_price IN Freight_Cost_Temp_Tab_Type,
563 p_freight_cost_temp_charge IN Freight_Cost_Temp_Tab_Type,
564 x_return_status OUT NOCOPY VARCHAR2);
565
566 PROCEDURE Move_fc_temp_to_main (
567 p_freight_cost_temp IN Freight_Cost_Temp_Tab_Type,
568 x_return_status OUT NOCOPY VARCHAR2);
569
570 PROCEDURE delete_invalid_fc_recs (
571 p_segment_id IN NUMBER DEFAULT NULL,
572 p_delivery_leg_id IN NUMBER DEFAULT NULL,
573 x_return_status OUT NOCOPY VARCHAR2 );
577 p_delivery_leg_id IN NUMBER DEFAULT NULL,
574
575 PROCEDURE unmark_reprice_required (
576 p_segment_id IN NUMBER DEFAULT NULL,
578 x_return_status OUT NOCOPY VARCHAR2 );
579
580 -- This API is called from the Multi-leg UI when a price comparison request
581 -- for a delivery is not chosen to go forward with
582 -- The input to it should be the comparison request id
583
584 PROCEDURE delete_fc_temp (
585 p_init_msg_list IN VARCHAR2 DEFAULT fnd_api.g_true,
586 p_request_id IN NUMBER, -- Comparison Request ID to delete
587 x_return_status OUT NOCOPY VARCHAR2);
588
589 PROCEDURE print_qp_output_lines (
590 p_engine_output_line IN QP_PREQ_GRP.LINE_TBL_TYPE,
591 p_engine_output_detail IN QP_PREQ_GRP.LINE_DETAIL_TBL_TYPE,
592 p_return_status IN VARCHAR2 ,
593 x_return_status OUT NOCOPY VARCHAR2 );
594
595 PROCEDURE print_fc_temp_rows (
596 p_fc_temp_rows IN Freight_Cost_Temp_Tab_Type,
597 x_return_status OUT NOCOPY VARCHAR2 ) ;
598
599 PROCEDURE Create_Freight_Cost_Temp(
600 p_freight_cost_temp_info IN Freight_Cost_Temp_Rec_Type,
601 x_freight_cost_temp_id OUT NOCOPY NUMBER,
602 x_return_status OUT NOCOPY VARCHAR2);
603
604 --
605 -- API: SHIPMENT_PRICE_COMPARE_PVT
606 -- Internal api for price comparison for LTL and PARCEL
607 -- Can accept delivery or trip. Can generate its own comparison request id
608 -- or can be passed in.
609 -- Not to be called from outside rating
610 -- Does not initialize the log file
611 -- Introduced for pack J
612 --
613 -- Parameters :
614 -- p_delivery_id -> Input either delivery_id or trip_id (not both)
615 -- p_trip_id -> Input either delivery_id or trip_id (not both)
616 -- p_lane_id_tab -> table of lane ids
617 -- p_schedule_id_tab -> table of schedule ids
618 -- Note : p_lane_id_tab and p_schedule_id_tab can have overlapping indices
619 -- For this API both tables are assumed to be independent of each
620 -- other
621 -- p_dep_date -> departure date
622 -- p_arr_date -> arrival date
623 -- x_sum_lane_price_tab ->
624 -- x_sum_lane_price_curr_tab ->
625 -- x_sum_sched_price_tab ->
626 -- x_sum_sched_price_curr_tab ->
627 -- x_request_id -> Can generate its own id if not passed in
628 -- x_return_status -> return status
629 --
630
631 PROCEDURE shipment_price_compare_pvt (
632 p_delivery_id IN NUMBER DEFAULT NULL,
633 p_trip_id IN NUMBER DEFAULT NULL,
634 p_lane_id_tab IN WSH_UTIL_CORE.id_tab_type,
635 p_sched_id_tab IN WSH_UTIL_CORE.id_tab_type,
636 p_service_lane_tab IN WSH_UTIL_CORE.Column_Tab_Type,
637 p_service_sched_tab IN WSH_UTIL_CORE.Column_Tab_Type,
638 p_dep_date IN DATE DEFAULT sysdate,
639 p_arr_date IN DATE DEFAULT sysdate,
640 x_sum_lane_price_tab OUT NOCOPY WSH_UTIL_CORE.id_tab_type,
641 x_sum_lane_price_curr_tab OUT NOCOPY WSH_UTIL_CORE.Column_tab_type,
642 x_sum_sched_price_tab OUT NOCOPY WSH_UTIL_CORE.id_tab_type,
643 x_sum_sched_price_curr_tab OUT NOCOPY WSH_UTIL_CORE.Column_tab_type,
644 x_request_id IN OUT NOCOPY NUMBER,
645 x_return_status OUT NOCOPY VARCHAR2 );
646
647 -- Returns the freight_cost_id of the delivery leg summary record in WSH_FREIGHT_COSTS
648
649 FUNCTION get_fc_id_from_dleg (
650 p_delivery_leg_id IN NUMBER ) RETURN NUMBER;
651
652 -- Procedure to get total commodity weight (weight used by LTL) for FPA report
653 PROCEDURE FPA_total_commodity_weight(
654 p_init_msg_list IN VARCHAR2 DEFAULT fnd_api.g_true,
655 p_delivery_id IN NUMBER,
656 x_total_comm_wt OUT NOCOPY NUMBER,
657 x_wt_uom OUT NOCOPY VARCHAR2,
658 x_return_status OUT NOCOPY VARCHAR2,
659 x_msg_count OUT NOCOPY NUMBER,
660 x_msg_data OUT NOCOPY VARCHAR2 );
661
662 PROCEDURE get_currency_code (
663 p_carrier_id IN NUMBER,
664 x_currency_code OUT NOCOPY wsh_carriers.currency_code%TYPE,
665 x_return_status OUT NOCOPY VARCHAR2);
666
667 PROCEDURE get_fc_type_id (
668 p_line_type_code IN VARCHAR2,
669 p_charge_subtype_code IN VARCHAR2 DEFAULT NULL,
670 x_freight_cost_type_id OUT NOCOPY NUMBER,
671 x_return_status OUT NOCOPY VARCHAR2 );
672
673 PROCEDURE print_top_level_detail (
674 p_first_level_rows IN shpmnt_content_tab_type,
678 p_engine_rows IN pricing_engine_input_tab_type,
675 x_return_status OUT NOCOPY VARCHAR2 );
676
677 PROCEDURE print_engine_rows (
679 x_return_status OUT NOCOPY VARCHAR2 );
680
681 PROCEDURE print_rolledup_lines (
682 p_rolledup_lines IN rolledup_line_tab_type,
683 x_return_status OUT NOCOPY VARCHAR2 );
684
685
686 -- This API is called from the Multi-leg UI for price comparison across lanes/schedules
687 -- It stores frieght cost details in WSH_FREIGHT_COSTS_TEMP table for all the lanes
688 -- for display purpose.
689 -- It returns PL/SQL tables (dense) of summary price in the same sequence as the input
690 -- Calls shipment hierarchy flattening API
691
692
693 PROCEDURE shipment_price_compare (
694 p_init_msg_list IN VARCHAR2 DEFAULT fnd_api.g_true,
695 p_init_prc_log IN VARCHAR2 DEFAULT 'Y',
696 p_delivery_id IN NUMBER,
697 p_trip_id IN NUMBER,
698 p_lane_sched_id_tab IN FTE_ID_TAB_TYPE, -- lane_ids or schedule_ids
699 p_lane_sched_tab IN FTE_CODE_TAB_TYPE, -- 'L' or 'S' (Lane or Schedule)
700 p_mode_tab IN FTE_CODE_TAB_TYPE,
701 p_service_type_tab IN FTE_CODE_TAB_TYPE,
702 p_vehicle_type_tab IN FTE_ID_TAB_TYPE,
703 p_dep_date IN DATE DEFAULT sysdate,
704 p_arr_date IN DATE DEFAULT sysdate,
705 p_pickup_location_id IN NUMBER,
706 p_dropoff_location_id IN NUMBER,
707 x_lane_sched_id_tab OUT NOCOPY FTE_ID_TAB_TYPE, -- lane_ids or schedule_ids
708 x_lane_sched_tab OUT NOCOPY FTE_CODE_TAB_TYPE, -- 'L' or 'S' (Lane or Schedule)
709 x_vehicle_type_tab OUT NOCOPY FTE_ID_TAB_TYPE,--Vehicle Type ID
710 x_mode_tab OUT NOCOPY FTE_CODE_TAB_TYPE,
711 x_service_type_tab OUT NOCOPY FTE_CODE_TAB_TYPE,
712 x_sum_rate_tab OUT NOCOPY FTE_ID_TAB_TYPE,
713 x_sum_rate_curr_tab OUT NOCOPY FTE_CODE_TAB_TYPE,
714 x_request_id OUT NOCOPY NUMBER, -- One request ID per comparison request
715 x_return_status OUT NOCOPY VARCHAR2 ) ;
716
717
718
719
720 END FTE_FREIGHT_PRICING;
721
[Home] [Help]