1 PACKAGE FTE_FREIGHT_PRICING_SPECIAL as
2 /* $Header: FTEFRPSS.pls 120.1 2005/07/15 12:13:52 mechawla noship $ */
3
4 -- Global Variables
5
6 g_package_name CONSTANT VARCHAR2(100) := 'FTE_FREIGHT_PRICING_SPECIAL';
7
8 -- parameters rec
9 TYPE lane_parameter_rec_type IS RECORD (
10 parameter_instance_id fte_prc_parameters.parameter_instance_id%TYPE,
11 lane_id fte_prc_parameters.parameter_instance_id%TYPE,
12 lane_function fte_prc_parameter_defaults.lane_function%TYPE,
13 parameter_sub_type fte_prc_parameter_defaults.parameter_sub_type%TYPE,
14 parameter_name fte_prc_parameter_defaults.parameter_name%TYPE,
15 value_from fte_prc_parameters.value_from%TYPE,
16 value_to fte_prc_parameters.value_to%TYPE,
17 uom_class fte_prc_parameters.uom_class%TYPE, --- do we need this?
18 uom_code fte_prc_parameters.uom_code%TYPE,
19 currency_code fte_prc_parameters.currency_code%TYPE
20 );
21
22 -- use this table to cache parameters for a lane
23 TYPE lane_parameter_tab_type IS TABLE OF lane_parameter_rec_type INDEX BY BINARY_INTEGER;
24 g_lane_parameters lane_parameter_tab_type;
25
26
27 TYPE special_process_flags_rec_type IS RECORD (
28 lane_id NUMBER,
29 lane_function VARCHAR2(30), -- can we make this a number?
30 dim_wt_flag VARCHAR2(1),
31 minimum_charge_flag VARCHAR2(1),
32 parcel_hundredwt_flag VARCHAR2(1),
33 flat_containerwt_flag VARCHAR2(1),
34 deficit_wt_flag VARCHAR2(1));
35
36 TYPE lane_rule_rec_type IS RECORD
37 ( lane_function VARCHAR2(40),
38 pattern_name VARCHAR2(40),
39 grouping_level VARCHAR2(40),
40 commodity_aggregation VARCHAR2(40),
41 pricing_objective VARCHAR2(40));
42
43 TYPE lane_rule_tab_type IS TABLE OF lane_rule_rec_type INDEX BY BINARY_INTEGER;
44
45 g_special_flags special_process_flags_rec_type;
46 g_lane_rules_tab lane_rule_tab_type;
47
48 -- Addded for 12i To Support dimensional weights at Carrier/CVarrier Service level
49 -- if not defined at Lane level
50 TYPE carrier_dim_weight_rec_type IS RECORD
51 ( dim_factor NUMBER,
52 dim_weight_uom mtl_system_items.dimension_uom_code%type,
53 dim_volume_uom VARCHAR2(30),
54 dim_dimension_uom VARCHAR2(30),
55 dim_min_volume VARCHAR2(30)
56 );
57
58
59 PROCEDURE initialize(p_lane_id IN NUMBER,
60 x_lane_function OUT NOCOPY VARCHAR2,
61 x_return_status OUT NOCOPY VARCHAR2);
62
63
64 PROCEDURE apply_dimensional_weight (
65 p_lane_id IN NUMBER,
66 p_carrier_id IN NUMBER,
67 p_service_code IN VARCHAR2,
68 p_top_level_rec IN OUT NOCOPY fte_freight_pricing.shpmnt_content_rec_type,
69 p_rolledup_rows IN OUT NOCOPY fte_freight_pricing.rolledup_line_tab_type,
70 x_return_status OUT NOCOPY VARCHAR2 );
71
72 FUNCTION isLTL RETURN VARCHAR2;
73
74 FUNCTION isParcel RETURN VARCHAR2;
75
76 PROCEDURE apply_min_charge (p_event_num IN NUMBER,
77 p_set_num IN NUMBER DEFAULT 1,
78 p_comp_with_price IN NUMBER DEFAULT NULL,
79 x_charge_applied OUT NOCOPY VARCHAR2, -- Y/N
80 x_return_status OUT NOCOPY VARCHAR2);
81
82 PROCEDURE process_LTL (
83 p_pricing_control_rec IN fte_freight_pricing.pricing_control_input_rec_type,
84 p_top_level_rows IN fte_freight_pricing.shpmnt_content_tab_type,
85 p_pricing_engine_rows IN OUT NOCOPY fte_freight_pricing.pricing_engine_input_tab_type,
86 p_pricing_dual_instances IN fte_freight_pricing.pricing_dual_instance_tab_type,
87 p_pattern_rows IN fte_freight_pricing.top_level_pattern_tab_type,
88 p_pricing_attribute_rows IN OUT NOCOPY fte_freight_pricing.pricing_attribute_tab_type,
89 --p_pricing_qualifier IN fte_qual_rec_type,
90 --x_qp_output_line_rows IN OUT NOCOPY QP_PREQ_GRP.LINE_TBL_TYPE,
91 --x_qp_output_detail_rows IN OUT NOCOPY QP_PREQ_GRP.LINE_DETAIL_TBL_TYPE,
92 x_qp_output_line_rows OUT NOCOPY QP_PREQ_GRP.LINE_TBL_TYPE,
93 x_qp_output_detail_rows OUT NOCOPY QP_PREQ_GRP.LINE_DETAIL_TBL_TYPE,
94 x_return_status OUT NOCOPY VARCHAR2 );
95
96 -- This is called for Parcel
97 PROCEDURE process_Parcel (
98 p_pricing_control_rec IN fte_freight_pricing.pricing_control_input_rec_type,
99 p_top_level_rows IN fte_freight_pricing.shpmnt_content_tab_type,
100 p_pricing_engine_rows IN OUT NOCOPY fte_freight_pricing.pricing_engine_input_tab_type,
101 p_pricing_dual_instances IN fte_freight_pricing.pricing_dual_instance_tab_type,
102 p_pattern_rows IN fte_freight_pricing.top_level_pattern_tab_type,
103 p_pricing_attribute_rows IN OUT NOCOPY fte_freight_pricing.pricing_attribute_tab_type,
104 --p_pricing_qualifier IN fte_qual_rec_type,
105 --x_qp_output_line_rows IN OUT NOCOPY QP_PREQ_GRP.LINE_TBL_TYPE,
106 --x_qp_output_detail_rows IN OUT NOCOPY QP_PREQ_GRP.LINE_DETAIL_TBL_TYPE,
107 x_qp_output_line_rows OUT NOCOPY QP_PREQ_GRP.LINE_TBL_TYPE,
108 x_qp_output_detail_rows OUT NOCOPY QP_PREQ_GRP.LINE_DETAIL_TBL_TYPE,
109 x_return_status OUT NOCOPY VARCHAR2 );
110
111
112 PROCEDURE process_others (
113 p_pricing_control_rec IN fte_freight_pricing.pricing_control_input_rec_type,
114 p_top_level_rows IN fte_freight_pricing.shpmnt_content_tab_type,
115 p_pricing_engine_rows IN OUT NOCOPY fte_freight_pricing.pricing_engine_input_tab_type,
116 p_pricing_dual_instances IN fte_freight_pricing.pricing_dual_instance_tab_type,
117 p_pattern_rows IN fte_freight_pricing.top_level_pattern_tab_type,
118 p_pricing_attribute_rows IN OUT NOCOPY fte_freight_pricing.pricing_attribute_tab_type,
119 --p_pricing_qualifier IN fte_qual_rec_type,
120 --x_qp_output_line_rows IN OUT NOCOPY QP_PREQ_GRP.LINE_TBL_TYPE,
121 --x_qp_output_detail_rows IN OUT NOCOPY QP_PREQ_GRP.LINE_DETAIL_TBL_TYPE,
122 x_qp_output_line_rows OUT NOCOPY QP_PREQ_GRP.LINE_TBL_TYPE,
123 x_qp_output_detail_rows OUT NOCOPY QP_PREQ_GRP.LINE_DETAIL_TBL_TYPE,
124 x_return_status OUT NOCOPY VARCHAR2 );
125
126 -- Is called by the main code after searching for patterns and creating standard engine rows and attributes
127 -- Checks process flags.
128 -- Depending upon the mix of conditions it calls other internal procedures to process the input lines.
129 -- If no special conditions apply, control is returned to the main code line.
130
131 PROCEDURE process_special_conditions(
132 p_pricing_control_rec IN fte_freight_pricing.pricing_control_input_rec_type,
133 p_top_level_rows IN fte_freight_pricing.shpmnt_content_tab_type,
134 p_pattern_rows IN fte_freight_pricing.top_level_pattern_tab_type,
135 p_pricing_dual_instances IN fte_freight_pricing.pricing_dual_instance_tab_type,
136 x_pricing_engine_rows IN OUT NOCOPY fte_freight_pricing.pricing_engine_input_tab_type,
137 x_pricing_attribute_rows IN OUT NOCOPY fte_freight_pricing.pricing_attribute_tab_type,
138 --x_qp_output_line_rows IN OUT NOCOPY QP_PREQ_GRP.LINE_TBL_TYPE,
139 --x_qp_output_detail_rows IN OUT NOCOPY QP_PREQ_GRP.LINE_DETAIL_TBL_TYPE,
140 x_qp_output_line_rows OUT NOCOPY QP_PREQ_GRP.LINE_TBL_TYPE,
141 x_qp_output_detail_rows OUT NOCOPY QP_PREQ_GRP.LINE_DETAIL_TBL_TYPE,
142 x_return_status OUT NOCOPY VARCHAR2 );
143
144 -- J+ enhancement for container_all rate basis
145 -- this procedure is called by shipment_pricing to rate container_all basis
146 PROCEDURE rate_container_all(
147 p_lane_info IN fte_freight_pricing.lane_info_rec_type,
148 p_top_level_rows IN fte_freight_pricing.shpmnt_content_tab_type,
149 p_save_flag IN VARCHAR2,
150 p_currency_code IN VARCHAR2 ,
151 x_freight_cost_main_price OUT NOCOPY fte_freight_pricing.Freight_Cost_Main_Tab_Type,
152 x_freight_cost_temp_price OUT NOCOPY fte_freight_pricing.Freight_Cost_Temp_Tab_Type,
153 x_freight_cost_main_charge OUT NOCOPY fte_freight_pricing.Freight_Cost_Main_Tab_Type,
154 x_freight_cost_temp_charge OUT NOCOPY fte_freight_pricing.Freight_Cost_Temp_Tab_Type,
155 x_fc_main_update_rows OUT NOCOPY fte_freight_pricing.Freight_Cost_Main_Tab_Type,
156 x_summary_lanesched_price OUT NOCOPY NUMBER,
157 x_summary_lanesched_price_uom OUT NOCOPY VARCHAR2,
158 x_return_status OUT NOCOPY VARCHAR2 );
159
160 -- J+ enhancement for LTL rating to include container weight
161 -- this procedure is called by process_shipment_patterns to distribute LTL container weight to
162 -- pricing_engine_rows and g_rolledup_lines
163 PROCEDURE distribute_LTL_container_wt(
164 p_top_level_rows IN fte_freight_pricing.shpmnt_content_tab_type,
165 x_pricing_engine_rows IN OUT NOCOPY fte_freight_pricing.pricing_engine_input_tab_type,
166 x_return_status OUT NOCOPY VARCHAR2 ) ;
167
168 -- J+ enhancement for shipment flat rating
169 -- this procedure is called by shipment_pricing to handle shipment flat rating
170 PROCEDURE process_shipment_flatrate(
171 p_lane_info IN fte_freight_pricing.lane_info_rec_type,
172 p_top_level_rows IN fte_freight_pricing.shpmnt_content_tab_type,
173 p_save_flag IN VARCHAR2,
174 p_currency_code IN VARCHAR2,
175 x_freight_cost_main_price OUT NOCOPY fte_freight_pricing.Freight_Cost_Main_Tab_Type,
176 x_freight_cost_temp_price OUT NOCOPY fte_freight_pricing.Freight_Cost_Temp_Tab_Type,
177 x_freight_cost_main_charge OUT NOCOPY fte_freight_pricing.Freight_Cost_Main_Tab_Type,
178 x_freight_cost_temp_charge OUT NOCOPY fte_freight_pricing.Freight_Cost_Temp_Tab_Type,
179 x_fc_main_update_rows OUT NOCOPY fte_freight_pricing.Freight_Cost_Main_Tab_Type,
180 x_summary_lanesched_price OUT NOCOPY NUMBER,
181 x_summary_lanesched_price_uom OUT NOCOPY VARCHAR2,
182 x_return_status OUT NOCOPY VARCHAR2 );
183
184 -- 12i Enhancement for loading Dimensional weight parameters at Carrier/Carrier Service
185 -- level.. called by apply_dimensional_weight
186 PROCEDURE load_carrier_dim_weight_params(
187 p_lane_id IN NUMBER,
188 p_carrier_id IN NUMBER,
189 p_service_code IN VARCHAR2,
190 x_carrier_dim_weight_rec OUT NOCOPY carrier_dim_weight_rec_type,
191 x_return_status OUT NOCOPY VARCHAR2 );
192
193 --- debugging utils ---
194 -- exceptions
195 G_NO_PARAMS_FOUND EXCEPTION; -- if parameter table is empty or required param is not available
196 G_INVALID_PARAM_VAL EXCEPTION; -- parameter has an invalid value
197
198
199 END FTE_FREIGHT_PRICING_SPECIAL;
[Home] [Help]