[Home] [Help]
PACKAGE BODY: APPS.FTE_FREIGHT_RATING_PUB
Source
1 PACKAGE BODY FTE_FREIGHT_RATING_PUB AS
2 /*$Header: FTEFRPBB.pls 120.3 2007/11/30 05:48:22 sankarun ship $ */
3
4 G_PKG_NAME CONSTANT VARCHAR2(30) := 'FTE_FREIGHT_RATING_PUB';
5
6 -- Private Package level Variables
7
8
9 TYPE rate_rec_type IS RECORD
10 (
11 price NUMBER, -- including discount
12 charge NUMBER, -- total charges
13 currency VARCHAR2(10),
14 qty NUMBER,
15 uom VARCHAR2(10)
16 );
17
18 TYPE rate_tab_type IS TABLE OF rate_rec_type INDEX BY BINARY_INTEGER;
19
20 CURSOR c_get_ship_method_code (c_carrier_id VARCHAR2, c_mode_of_trans VARCHAR2, c_service_level VARCHAR2, c_org_id NUMBER) IS
21 SELECT a.ship_method_code
22 FROM wsh_carrier_services a, wsh_org_carrier_services b
23 WHERE a.carrier_service_id = b.carrier_service_id
24 AND b.organization_id = c_org_id
25 AND b.enabled_flag = 'Y'
26 AND a.enabled_flag = 'Y'
27 AND a.mode_of_transport = c_mode_of_trans
28 AND a.service_level = c_service_level
29 AND a.carrier_id = c_carrier_id;
30
31 CURSOR c_get_carrier_freight_code (c_carrier_id VARCHAR2) IS
32 SELECT freight_code
33 FROM wsh_carriers
34 WHERE carrier_id = c_carrier_id;
35
36 CURSOR c_get_generic_carrier_flag (c_carrier_id VARCHAR2) IS
37 SELECT generic_flag
38 FROM wsh_carriers
39 WHERE carrier_id = c_carrier_id;
40
41 CURSOR c_get_generic_carrier_flag2 (c_ship_method_code VARCHAR2) IS
42 SELECT a.generic_flag
43 FROM wsh_carriers a, wsh_carrier_services b
44 WHERE a.carrier_id = b.carrier_id
45 AND b.ship_method_code = c_ship_method_code;
46
47 CURSOR c_carrier_services(c_shp_mthd_cd VARCHAR2)
48 IS
49 SELECT ship_method_code,carrier_id, service_level, mode_of_transport, ship_method_meaning
50 FROM wsh_carrier_services
51 WHERE ship_method_code = c_shp_mthd_cd;
52
53 PROCEDURE convert_amount(
54 p_from_currency IN VARCHAR2,
55 p_from_amount IN NUMBER,
56 p_conversion_type IN VARCHAR2,
57 p_to_currency IN VARCHAR2,
58 x_to_amount OUT NOCOPY NUMBER,
59 x_return_status OUT NOCOPY VARCHAR2)
60 IS
61 l_log_level NUMBER := fte_freight_pricing_util.G_DBG;
62 l_api_name VARCHAR2(50) := 'convert_amount';
63 l_conversion_type VARCHAR2(30);
64 BEGIN
65 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
66 FTE_FREIGHT_PRICING_UTIL.reset_dbg_vars;
67 FTE_FREIGHT_PRICING_UTIL.set_method(l_log_level,l_api_name);
68
69 fte_freight_pricing_util.print_msg(l_log_level,'p_from_currency= '||p_from_currency);
70 fte_freight_pricing_util.print_msg(l_log_level,'p_from_amount= '||p_from_amount);
71 fte_freight_pricing_util.print_msg(l_log_level,'p_conversion_type= '||p_conversion_type);
72 fte_freight_pricing_util.print_msg(l_log_level,'p_to_currency= '||p_to_currency);
73
74 IF p_conversion_type is null THEN
75 l_conversion_type := 'Corporate';
76 ELSE
77 l_conversion_type := p_conversion_type;
78 END IF;
79 fte_freight_pricing_util.print_msg(l_log_level,'l_conversion_type= '||l_conversion_type);
80
81 x_to_amount := GL_CURRENCY_API.convert_amount(
82 p_from_currency,
83 p_to_currency,
84 SYSDATE,
85 l_conversion_type,
86 p_from_amount
87 );
88
89 fte_freight_pricing_util.print_msg(l_log_level,'x_to_amount= '||x_to_amount);
90
91 FTE_FREIGHT_PRICING_UTIL.unset_method(l_log_level, l_api_name);
92
93 EXCEPTION
94 WHEN others THEN
95 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
96 FTE_FREIGHT_PRICING_UTIL.set_exception(l_api_name,FTE_FREIGHT_PRICING_UTIL.G_ERR,'g_others');
97 FTE_FREIGHT_PRICING_UTIL.print_msg(FTE_FREIGHT_PRICING_UTIL.G_ERR,'Unexpected Error : '||SQLCODE||' : '||SQLERRM);
98 FTE_FREIGHT_PRICING_UTIL.unset_method(l_log_level,l_api_name);
99
100 END convert_amount;
101
102 PROCEDURE print_rates_tab (
103 p_source_line_rates_tab IN FTE_PROCESS_REQUESTS.fte_source_line_rates_tab,
104 p_source_header_rates_tab IN FTE_PROCESS_REQUESTS.fte_source_header_rates_tab,
105 x_return_status OUT NOCOPY VARCHAR2)
106 IS
107 l_log_level NUMBER := fte_freight_pricing_util.G_DBG;
108 l_api_name VARCHAR2(50) := 'print_rates_tab';
109 i NUMBER;
110 BEGIN
111 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
112 FTE_FREIGHT_PRICING_UTIL.reset_dbg_vars;
113 FTE_FREIGHT_PRICING_UTIL.set_method(l_log_level,l_api_name);
114
115 fte_freight_pricing_util.print_msg(fte_freight_pricing_util.G_DBG,'-----------BEGIN Source Line Rates Tab -------------');
116
117 i := p_source_line_rates_tab.FIRST;
118 IF (i IS NOT NULL) THEN
119 LOOP
120 fte_freight_pricing_util.print_msg(fte_freight_pricing_util.G_DBG,'I = '||i);
121 fte_freight_pricing_util.print_msg(fte_freight_pricing_util.G_DBG,'source_line_id = '||p_source_line_rates_tab(i).source_line_id);
122 fte_freight_pricing_util.print_msg(fte_freight_pricing_util.G_DBG,'cost_type_id = '||p_source_line_rates_tab(i).cost_type_id);
123 fte_freight_pricing_util.print_msg(fte_freight_pricing_util.G_DBG,'line_type_code = '||p_source_line_rates_tab(i).line_type_code);
124 fte_freight_pricing_util.print_msg(fte_freight_pricing_util.G_DBG,'cost_type = '||p_source_line_rates_tab(i).cost_type);
125 fte_freight_pricing_util.print_msg(fte_freight_pricing_util.G_DBG,'cost_sub_type = '||p_source_line_rates_tab(i).cost_sub_type);
126 fte_freight_pricing_util.print_msg(fte_freight_pricing_util.G_DBG,'priced_quantity = '||p_source_line_rates_tab(i).priced_quantity);
127 fte_freight_pricing_util.print_msg(fte_freight_pricing_util.G_DBG,'priced_uom = '||p_source_line_rates_tab(i).priced_uom);
128 fte_freight_pricing_util.print_msg(fte_freight_pricing_util.G_DBG,'unit_price = '||p_source_line_rates_tab(i).unit_price);
129 fte_freight_pricing_util.print_msg(fte_freight_pricing_util.G_DBG,'base_price = '||p_source_line_rates_tab(i).base_price);
130 fte_freight_pricing_util.print_msg(fte_freight_pricing_util.G_DBG,'adjusted_unit_price = '||p_source_line_rates_tab(i).adjusted_unit_price);
131 fte_freight_pricing_util.print_msg(fte_freight_pricing_util.G_DBG,'adjusted_price = '||p_source_line_rates_tab(i).adjusted_price);
132 fte_freight_pricing_util.print_msg(fte_freight_pricing_util.G_DBG,'currency = '||p_source_line_rates_tab(i).currency);
133 fte_freight_pricing_util.print_msg(fte_freight_pricing_util.G_DBG,'consolidation_id = '||p_source_line_rates_tab(i).consolidation_id);
134 fte_freight_pricing_util.print_msg(fte_freight_pricing_util.G_DBG,'lane_id = '||p_source_line_rates_tab(i).lane_id);
135 fte_freight_pricing_util.print_msg(fte_freight_pricing_util.G_DBG,'carrier_id = '||p_source_line_rates_tab(i).carrier_id);
136 fte_freight_pricing_util.print_msg(fte_freight_pricing_util.G_DBG,'carrier_freight_code = '||p_source_line_rates_tab(i).carrier_freight_code);
137 fte_freight_pricing_util.print_msg(fte_freight_pricing_util.G_DBG,'service_level = '||p_source_line_rates_tab(i).service_level);
138 fte_freight_pricing_util.print_msg(fte_freight_pricing_util.G_DBG,'mode_of_transport = '||p_source_line_rates_tab(i).mode_of_transport);
139 fte_freight_pricing_util.print_msg(fte_freight_pricing_util.G_DBG,'ship_method_code = '||p_source_line_rates_tab(i).ship_method_code);
140 fte_freight_pricing_util.print_msg(fte_freight_pricing_util.G_DBG,'vehicle_type_id = '||p_source_line_rates_tab(i).vehicle_type_id);
141 fte_freight_pricing_util.print_msg(l_log_level,'------------------------');
142
143 EXIT WHEN (i >= p_source_line_rates_tab.LAST);
144 i := p_source_line_rates_tab.NEXT(i);
145 END LOOP;
146 END IF;
147
148 fte_freight_pricing_util.print_msg(fte_freight_pricing_util.G_DBG,'-----------BEGIN Source header Rates Tab -------------');
149
150 i := p_source_header_rates_tab.FIRST;
151 IF (i IS NOT NULL) THEN
152 LOOP
153 fte_freight_pricing_util.print_msg(l_log_level,'I = '||i);
154 fte_freight_pricing_util.print_msg(l_log_level,'consolidation_id = '||p_source_header_rates_tab(i).consolidation_id);
155 fte_freight_pricing_util.print_msg(l_log_level,'lane_id = '||p_source_header_rates_tab(i).lane_id);
156 fte_freight_pricing_util.print_msg(l_log_level,'carrier_id = '||p_source_header_rates_tab(i).carrier_id);
157 fte_freight_pricing_util.print_msg(l_log_level,'carrier_freight_code = '||p_source_header_rates_tab(i).carrier_freight_code);
158 fte_freight_pricing_util.print_msg(l_log_level,'service_level = '||p_source_header_rates_tab(i).service_level);
159 fte_freight_pricing_util.print_msg(l_log_level,'mode_of_transport = '||p_source_header_rates_tab(i).mode_of_transport);
160 fte_freight_pricing_util.print_msg(l_log_level,'ship_method_code = '||p_source_header_rates_tab(i).ship_method_code);
161 fte_freight_pricing_util.print_msg(l_log_level,'vehicle_type_id = '||p_source_header_rates_tab(i).vehicle_type_id);
162 fte_freight_pricing_util.print_msg(l_log_level,'cost_type_id = '||p_source_header_rates_tab(i).cost_type_id);
163 fte_freight_pricing_util.print_msg(l_log_level,'cost_type = '||p_source_header_rates_tab(i).cost_type);
164 fte_freight_pricing_util.print_msg(l_log_level,'price = '||p_source_header_rates_tab(i).price);
165 fte_freight_pricing_util.print_msg(l_log_level,'currency = '||p_source_header_rates_tab(i).currency);
166 fte_freight_pricing_util.print_msg(l_log_level,'transit_time = '||p_source_header_rates_tab(i).transit_time);
167 fte_freight_pricing_util.print_msg(l_log_level,'transit_time_uom = '||p_source_header_rates_tab(i).transit_time_uom);
168 fte_freight_pricing_util.print_msg(l_log_level,'first_line_index = '||p_source_header_rates_tab(i).first_line_index);
169 fte_freight_pricing_util.print_msg(l_log_level,'------------------------');
170
171 EXIT WHEN (i >= p_source_header_rates_tab.LAST);
172 i := p_source_header_rates_tab.NEXT(i);
173 END LOOP;
174 END IF;
175 fte_freight_pricing_util.print_msg(fte_freight_pricing_util.G_DBG,'---- END Source header Rates Tab ------- ');
176
177 FTE_FREIGHT_PRICING_UTIL.unset_method(l_log_level, l_api_name);
178
179 EXCEPTION
180 WHEN others THEN
181 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
182 FTE_FREIGHT_PRICING_UTIL.set_exception(l_api_name,FTE_FREIGHT_PRICING_UTIL.G_ERR,'g_others');
183 FTE_FREIGHT_PRICING_UTIL.print_msg(FTE_FREIGHT_PRICING_UTIL.G_ERR,'Unexpected Error : '||SQLCODE||' : '||SQLERRM);
184 FTE_FREIGHT_PRICING_UTIL.unset_method(l_log_level,l_api_name);
185
186 END print_rates_tab;
187
188 PROCEDURE print_matched_services (
189 p_matched_services IN lane_info_tab_type,
190 x_return_status OUT NOCOPY VARCHAR2)
191 IS
192 l_log_level NUMBER := fte_freight_pricing_util.G_DBG;
193 l_api_name VARCHAR2(50) := 'print_matched_services';
194 i NUMBER;
195 BEGIN
196 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
197
198 FTE_FREIGHT_PRICING_UTIL.reset_dbg_vars;
199 FTE_FREIGHT_PRICING_UTIL.set_method(l_log_level,l_api_name);
200
201 fte_freight_pricing_util.print_msg(l_log_level,'-----------BEGIN Matched Services Tab -------------');
202
203 i := p_matched_services.FIRST;
204 IF (i IS NOT NULL) THEN
205 LOOP
206 fte_freight_pricing_util.print_msg(l_log_level,'i = '||i);
207 fte_freight_pricing_util.print_msg(l_log_level,'lane_id = '||p_matched_services(i).lane_id);
208 fte_freight_pricing_util.print_msg(l_log_level,'carrier_id = '||p_matched_services(i).carrier_id);
209 fte_freight_pricing_util.print_msg(l_log_level,'carrier_freight_code = '||p_matched_services(i).carrier_freight_code);
210 fte_freight_pricing_util.print_msg(l_log_level,'mode_of_transport_code = '||p_matched_services(i).mode_of_transportation_code);
211 fte_freight_pricing_util.print_msg(l_log_level,'service_type_code = '||p_matched_services(i).service_type_code);
212 fte_freight_pricing_util.print_msg(l_log_level,'ship_method_code = '||p_matched_services(i).ship_method_code);
213 fte_freight_pricing_util.print_msg(l_log_level,'pricelist_id = '||p_matched_services(i).pricelist_id);
214 fte_freight_pricing_util.print_msg(l_log_level,'origin_id = '||p_matched_services(i).origin_id);
215 fte_freight_pricing_util.print_msg(l_log_level,'destination_id = '||p_matched_services(i).destination_id);
216 fte_freight_pricing_util.print_msg(l_log_level,'basis = '||p_matched_services(i).basis);
217 fte_freight_pricing_util.print_msg(l_log_level,'commodity_catg_id = '||p_matched_services(i).commodity_catg_id);
218 fte_freight_pricing_util.print_msg(l_log_level,'classification_code = '||p_matched_services(i).classification_code);
219 fte_freight_pricing_util.print_msg(l_log_level,'transit_time = '||p_matched_services(i).transit_time);
220 fte_freight_pricing_util.print_msg(l_log_level,'transit_time_uom = '||p_matched_services(i).transit_time_uom);
221 fte_freight_pricing_util.print_msg(l_log_level,'------------------------');
222
223 EXIT WHEN (i >= p_matched_services.LAST);
224 i := p_matched_services.NEXT(i);
225 END LOOP;
226 END IF;
227
228 FTE_FREIGHT_PRICING_UTIL.unset_method(l_log_level,l_api_name);
229
230 EXCEPTION
231 WHEN others THEN
232 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
233 FTE_FREIGHT_PRICING_UTIL.set_exception(l_api_name,FTE_FREIGHT_PRICING_UTIL.G_ERR,'g_others');
234 FTE_FREIGHT_PRICING_UTIL.print_msg(FTE_FREIGHT_PRICING_UTIL.G_ERR,'Unexpected Error : '||SQLCODE||' : '||SQLERRM);
235 FTE_FREIGHT_PRICING_UTIL.unset_method(l_log_level,l_api_name);
236 END print_matched_services;
237
238 PROCEDURE print_source_line_tab (
239 p_source_line_tab IN FTE_PROCESS_REQUESTS.fte_source_line_tab,
240 x_return_status OUT NOCOPY VARCHAR2)
241 IS
242 l_log_level NUMBER := fte_freight_pricing_util.G_DBG;
243 l_api_name VARCHAR2(50) := 'print_source_line_tab';
244 i NUMBER;
245 BEGIN
246 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
247
248 FTE_FREIGHT_PRICING_UTIL.reset_dbg_vars;
249 FTE_FREIGHT_PRICING_UTIL.set_method(l_log_level,l_api_name);
250
251 fte_freight_pricing_util.print_msg(l_log_level,'-----------BEGIN Source Line Tab -------------');
252
253 i := p_source_line_tab.FIRST;
254 IF (i IS NOT NULL) THEN
255 LOOP
256 fte_freight_pricing_util.print_msg(fte_freight_pricing_util.G_DBG,'i := '||i);
257
258 fte_freight_pricing_util.print_msg(fte_freight_pricing_util.G_DBG,'l_source_line_tab(i).source_type :='||p_source_line_tab(i).source_type);
259 fte_freight_pricing_util.print_msg(fte_freight_pricing_util.G_DBG,'l_source_line_tab(i).source_header_id :='||p_source_line_tab(i).source_header_id);
260 fte_freight_pricing_util.print_msg(fte_freight_pricing_util.G_DBG,'l_source_line_tab(i).source_line_id :='||p_source_line_tab(i).source_line_id);
261 fte_freight_pricing_util.print_msg(fte_freight_pricing_util.G_DBG,'l_source_line_tab(i).ship_from_org_id :='||p_source_line_tab(i).ship_from_org_id);
262 fte_freight_pricing_util.print_msg(fte_freight_pricing_util.G_DBG,'l_source_line_tab(i).ship_from_location_id :='||p_source_line_tab(i).ship_from_location_id);
263 fte_freight_pricing_util.print_msg(fte_freight_pricing_util.G_DBG,'l_source_line_tab(i).ship_to_site_id :='||p_source_line_tab(i).ship_to_site_id);
264 fte_freight_pricing_util.print_msg(fte_freight_pricing_util.G_DBG,'l_source_line_tab(i).ship_to_location_id :='||p_source_line_tab(i).ship_to_location_id);
265 fte_freight_pricing_util.print_msg(fte_freight_pricing_util.G_DBG,'l_source_line_tab(i).customer_id :='||p_source_line_tab(i).customer_id);
266 fte_freight_pricing_util.print_msg(fte_freight_pricing_util.G_DBG,'l_source_line_tab(i).inventory_item_id :='||p_source_line_tab(i).inventory_item_id);
267 fte_freight_pricing_util.print_msg(fte_freight_pricing_util.G_DBG,'l_source_line_tab(i).source_quantity :='||p_source_line_tab(i).source_quantity);
268 fte_freight_pricing_util.print_msg(fte_freight_pricing_util.G_DBG,'l_source_line_tab(i).source_quantity_uom :='||p_source_line_tab(i).source_quantity_uom);
269 fte_freight_pricing_util.print_msg(fte_freight_pricing_util.G_DBG,'l_source_line_tab(i).ship_date :='||p_source_line_tab(i).ship_date);
270 fte_freight_pricing_util.print_msg(fte_freight_pricing_util.G_DBG,'l_source_line_tab(i).arrival_date :='||p_source_line_tab(i).arrival_date);
271 fte_freight_pricing_util.print_msg(fte_freight_pricing_util.G_DBG,'l_source_line_tab(i).delivery_lead_time :='||p_source_line_tab(i).delivery_lead_time);
272 fte_freight_pricing_util.print_msg(fte_freight_pricing_util.G_DBG,'l_source_line_tab(i).scheduled_flag :='||p_source_line_tab(i).scheduled_flag);
273 fte_freight_pricing_util.print_msg(fte_freight_pricing_util.G_DBG,'l_source_line_tab(i).order_set_type :='||p_source_line_tab(i).order_set_type);
274 fte_freight_pricing_util.print_msg(fte_freight_pricing_util.G_DBG,'l_source_line_tab(i).order_set_id :='||p_source_line_tab(i).order_set_id);
275 fte_freight_pricing_util.print_msg(fte_freight_pricing_util.G_DBG,'l_source_line_tab(i).intmed_ship_to_site_id :='||p_source_line_tab(i).intmed_ship_to_site_id);
276 fte_freight_pricing_util.print_msg(fte_freight_pricing_util.G_DBG,'l_source_line_tab(i).intmed_ship_to_loc_id :='||p_source_line_tab(i).intmed_ship_to_loc_id);
277 fte_freight_pricing_util.print_msg(fte_freight_pricing_util.G_DBG,'l_source_line_tab(i).carrier_id :='||p_source_line_tab(i).carrier_id);
278 fte_freight_pricing_util.print_msg(fte_freight_pricing_util.G_DBG,'l_source_line_tab(i).ship_method_flag :='||p_source_line_tab(i).ship_method_flag);
279 fte_freight_pricing_util.print_msg(fte_freight_pricing_util.G_DBG,'l_source_line_tab(i).ship_method_code :='||p_source_line_tab(i).ship_method_code);
280 fte_freight_pricing_util.print_msg(fte_freight_pricing_util.G_DBG,'l_source_line_tab(i).freight_carrier_code :='||p_source_line_tab(i).freight_carrier_code);
281 fte_freight_pricing_util.print_msg(fte_freight_pricing_util.G_DBG,'l_source_line_tab(i).service_level :='||p_source_line_tab(i).service_level);
282 fte_freight_pricing_util.print_msg(fte_freight_pricing_util.G_DBG,'l_source_line_tab(i).mode_of_transport :='||p_source_line_tab(i).mode_of_transport);
283 fte_freight_pricing_util.print_msg(fte_freight_pricing_util.G_DBG,'l_source_line_tab(i).freight_terms :='||p_source_line_tab(i).freight_terms);
284 fte_freight_pricing_util.print_msg(fte_freight_pricing_util.G_DBG,'l_source_line_tab(i).fob_code :='||p_source_line_tab(i).fob_code);
285 fte_freight_pricing_util.print_msg(fte_freight_pricing_util.G_DBG,'l_source_line_tab(i).weight :='||p_source_line_tab(i).weight );
286 fte_freight_pricing_util.print_msg(fte_freight_pricing_util.G_DBG,'l_source_line_tab(i).weight_uom_code :='||p_source_line_tab(i).weight_uom_code);
287 fte_freight_pricing_util.print_msg(fte_freight_pricing_util.G_DBG,'l_source_line_tab(i).volume :='||p_source_line_tab(i).volume );
288 fte_freight_pricing_util.print_msg(fte_freight_pricing_util.G_DBG,'l_source_line_tab(i).volume_uom_code :='||p_source_line_tab(i).volume_uom_code);
289 fte_freight_pricing_util.print_msg(fte_freight_pricing_util.G_DBG,'l_source_line_tab(i).freight_rating_flag :='||p_source_line_tab(i).freight_rating_flag);
290 fte_freight_pricing_util.print_msg(fte_freight_pricing_util.G_DBG,'l_source_line_tab(i).freight_rate :='||p_source_line_tab(i).freight_rate);
291 fte_freight_pricing_util.print_msg(fte_freight_pricing_util.G_DBG,'l_source_line_tab(i).freight_rate_currency :='||p_source_line_tab(i).freight_rate_currency);
292 fte_freight_pricing_util.print_msg(fte_freight_pricing_util.G_DBG,'l_source_line_tab(i).status :='||p_source_line_tab(i).status );
293 fte_freight_pricing_util.print_msg(fte_freight_pricing_util.G_DBG,'l_source_line_tab(i).message_data :='||p_source_line_tab(i).message_data);
294 fte_freight_pricing_util.print_msg(fte_freight_pricing_util.G_DBG,'l_source_line_tab(i).consolidation_id :='||p_source_line_tab(i).consolidation_id);
295 fte_freight_pricing_util.print_msg(fte_freight_pricing_util.G_DBG,'l_source_line_tab(i).override_ship_method :='||p_source_line_tab(i).override_ship_method);
296 fte_freight_pricing_util.print_msg(fte_freight_pricing_util.G_DBG,'l_source_line_tab(i).currency :='||p_source_line_tab(i).currency);
297 fte_freight_pricing_util.print_msg(fte_freight_pricing_util.G_DBG,'l_source_line_tab(i).currency_conversion_type :='||p_source_line_tab(i).currency_conversion_type);
298
299
300 fte_freight_pricing_util.print_msg(fte_freight_pricing_util.G_DBG,'l_source_line_tab(i).origin_country :='||p_source_line_tab(i).origin_country);
301 fte_freight_pricing_util.print_msg(fte_freight_pricing_util.G_DBG,'l_source_line_tab(i).origin_state :='||p_source_line_tab(i).origin_state);
302 fte_freight_pricing_util.print_msg(fte_freight_pricing_util.G_DBG,'l_source_line_tab(i).origin_city :='||p_source_line_tab(i).origin_city);
303 fte_freight_pricing_util.print_msg(fte_freight_pricing_util.G_DBG,'l_source_line_tab(i).origin_zip :='||p_source_line_tab(i).origin_zip);
304
305 fte_freight_pricing_util.print_msg(fte_freight_pricing_util.G_DBG,'l_source_line_tab(i).destination_country :='||p_source_line_tab(i).destination_country);
306 fte_freight_pricing_util.print_msg(fte_freight_pricing_util.G_DBG,'l_source_line_tab(i).destination_state :='||p_source_line_tab(i).destination_state);
307 fte_freight_pricing_util.print_msg(fte_freight_pricing_util.G_DBG,'l_source_line_tab(i).destination_city :='||p_source_line_tab(i).destination_city);
308 fte_freight_pricing_util.print_msg(fte_freight_pricing_util.G_DBG,'l_source_line_tab(i).destination_zip :='||p_source_line_tab(i).destination_zip);
309
310 fte_freight_pricing_util.print_msg(fte_freight_pricing_util.G_DBG,'l_source_line_tab(i).distance :='||p_source_line_tab(i).distance);
311 fte_freight_pricing_util.print_msg(fte_freight_pricing_util.G_DBG,'l_source_line_tab(i).distance_uom :='||p_source_line_tab(i).distance_uom);
312 fte_freight_pricing_util.print_msg(fte_freight_pricing_util.G_DBG,'l_source_line_tab(i).vehicle_item_id :='||p_source_line_tab(i).vehicle_item_id);
313 fte_freight_pricing_util.print_msg(fte_freight_pricing_util.G_DBG,'l_source_line_tab(i).commodity_category_id :='||p_source_line_tab(i).commodity_category_id);
314
315
316 fte_freight_pricing_util.print_msg(l_log_level,'------------------------');
317
318 EXIT WHEN (i >= p_source_line_tab.LAST);
319 i := p_source_line_tab.NEXT(i);
320 END LOOP;
321 END IF;
322
323 FTE_FREIGHT_PRICING_UTIL.unset_method(l_log_level,l_api_name);
324
325 EXCEPTION
326 WHEN others THEN
327 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
328 FTE_FREIGHT_PRICING_UTIL.set_exception(l_api_name,FTE_FREIGHT_PRICING_UTIL.G_ERR,'g_others');
329 FTE_FREIGHT_PRICING_UTIL.print_msg(FTE_FREIGHT_PRICING_UTIL.G_ERR,'Unexpected Error : '||SQLCODE||' : '||SQLERRM);
330 FTE_FREIGHT_PRICING_UTIL.unset_method(l_log_level,l_api_name);
331 END print_source_line_tab;
332
333 PROCEDURE print_source_header_tab (
334 p_source_header_tab IN FTE_PROCESS_REQUESTS.fte_source_header_tab,
335 x_return_status OUT NOCOPY VARCHAR2)
336 IS
337 l_log_level NUMBER := fte_freight_pricing_util.G_DBG;
338 l_api_name VARCHAR2(50) := 'print_source_header_tab';
339 i NUMBER;
340 BEGIN
341 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
342
343 FTE_FREIGHT_PRICING_UTIL.reset_dbg_vars;
344 FTE_FREIGHT_PRICING_UTIL.set_method(l_log_level,l_api_name);
345
346 fte_freight_pricing_util.print_msg(fte_freight_pricing_util.G_DBG,'-----------BEGIN Source Header Tab -------------');
347
348 i := p_source_header_tab.FIRST;
349 IF (i IS NOT NULL) THEN
350 LOOP
351 fte_freight_pricing_util.print_msg(fte_freight_pricing_util.G_DBG,'i := '||i);
352
353 fte_freight_pricing_util.print_msg(fte_freight_pricing_util.G_DBG,'l_source_header_tab(i).consolidation_id :='||p_source_header_tab(i).consolidation_id);
354 fte_freight_pricing_util.print_msg(fte_freight_pricing_util.G_DBG,'l_source_header_tab(i).ship_from_org_id :='||p_source_header_tab(i).ship_from_org_id);
355 fte_freight_pricing_util.print_msg(fte_freight_pricing_util.G_DBG,'l_source_header_tab(i).ship_from_location_id :='||p_source_header_tab(i).ship_from_location_id);
356 fte_freight_pricing_util.print_msg(fte_freight_pricing_util.G_DBG,'l_source_header_tab(i).ship_to_location_id :='||p_source_header_tab(i).ship_to_location_id);
357 fte_freight_pricing_util.print_msg(fte_freight_pricing_util.G_DBG,'l_source_header_tab(i).ship_to_site_id :='||p_source_header_tab(i).ship_to_site_id);
358 fte_freight_pricing_util.print_msg(fte_freight_pricing_util.G_DBG,'l_source_header_tab(i).customer_id :='||p_source_header_tab(i).customer_id);
359 fte_freight_pricing_util.print_msg(fte_freight_pricing_util.G_DBG,'l_source_header_tab(i).ship_date :='||p_source_header_tab(i).ship_date);
360 fte_freight_pricing_util.print_msg(fte_freight_pricing_util.G_DBG,'l_source_header_tab(i).arrival_date :='||p_source_header_tab(i).arrival_date);
361 fte_freight_pricing_util.print_msg(fte_freight_pricing_util.G_DBG,'l_source_header_tab(i).delivery_lead_time :='||p_source_header_tab(i).delivery_lead_time);
362 fte_freight_pricing_util.print_msg(fte_freight_pricing_util.G_DBG,'l_source_header_tab(i).scheduled_flag :='||p_source_header_tab(i).scheduled_flag);
363 fte_freight_pricing_util.print_msg(fte_freight_pricing_util.G_DBG,'l_source_header_tab(i).total_weight :='||p_source_header_tab(i).total_weight);
364 fte_freight_pricing_util.print_msg(fte_freight_pricing_util.G_DBG,'l_source_header_tab(i).weight_uom_code :='||p_source_header_tab(i).weight_uom_code);
365 fte_freight_pricing_util.print_msg(fte_freight_pricing_util.G_DBG,'l_source_header_tab(i).total_volume :='||p_source_header_tab(i).total_volume);
366 fte_freight_pricing_util.print_msg(fte_freight_pricing_util.G_DBG,'l_source_header_tab(i).volume_uom_code :='||p_source_header_tab(i).volume_uom_code);
367 fte_freight_pricing_util.print_msg(fte_freight_pricing_util.G_DBG,'l_source_header_tab(i).ship_method_code :='||p_source_header_tab(i).ship_method_code);
368 fte_freight_pricing_util.print_msg(fte_freight_pricing_util.G_DBG,'l_source_header_tab(i).carrier_id :='||p_source_header_tab(i).carrier_id);
369 fte_freight_pricing_util.print_msg(fte_freight_pricing_util.G_DBG,'l_source_header_tab(i).service_level :='||p_source_header_tab(i).service_level);
370 fte_freight_pricing_util.print_msg(fte_freight_pricing_util.G_DBG,'l_source_header_tab(i).mode_of_transport :='||p_source_header_tab(i).mode_of_transport);
371 fte_freight_pricing_util.print_msg(fte_freight_pricing_util.G_DBG,'l_source_header_tab(i).freight_terms :='||p_source_header_tab(i).freight_terms);
372 fte_freight_pricing_util.print_msg(fte_freight_pricing_util.G_DBG,'l_source_header_tab(i).status :='||p_source_header_tab(i).status );
373 fte_freight_pricing_util.print_msg(fte_freight_pricing_util.G_DBG,'l_source_header_tab(i).message_data :='||p_source_header_tab(i).message_data);
374 fte_freight_pricing_util.print_msg(fte_freight_pricing_util.G_DBG,'l_source_header_tab(i).enforce_lead_time :='||p_source_header_tab(i).enforce_lead_time);
375 fte_freight_pricing_util.print_msg(fte_freight_pricing_util.G_DBG,'l_source_header_tab(i).currency :='||p_source_header_tab(i).currency);
376 fte_freight_pricing_util.print_msg(fte_freight_pricing_util.G_DBG,'l_source_header_tab(i).currency_conversion_type :='||p_source_header_tab(i).currency_conversion_type);
377
378 fte_freight_pricing_util.print_msg(fte_freight_pricing_util.G_DBG,'l_source_header_tab(i).origin_country :='||p_source_header_tab(i).origin_country);
379 fte_freight_pricing_util.print_msg(fte_freight_pricing_util.G_DBG,'l_source_header_tab(i).origin_state :='||p_source_header_tab(i).origin_state);
380 fte_freight_pricing_util.print_msg(fte_freight_pricing_util.G_DBG,'l_source_header_tab(i).origin_city :='||p_source_header_tab(i).origin_city);
381 fte_freight_pricing_util.print_msg(fte_freight_pricing_util.G_DBG,'l_source_header_tab(i).origin_zip :='||p_source_header_tab(i).origin_zip);
382
383
384 fte_freight_pricing_util.print_msg(fte_freight_pricing_util.G_DBG,'l_source_header_tab(i).destination_country :='||p_source_header_tab(i).destination_country);
385 fte_freight_pricing_util.print_msg(fte_freight_pricing_util.G_DBG,'l_source_header_tab(i).destination_state :='||p_source_header_tab(i).destination_state);
386 fte_freight_pricing_util.print_msg(fte_freight_pricing_util.G_DBG,'l_source_header_tab(i).destination_city :='||p_source_header_tab(i).destination_city);
387 fte_freight_pricing_util.print_msg(fte_freight_pricing_util.G_DBG,'l_source_header_tab(i).destination_zip :='||p_source_header_tab(i).destination_zip);
388
389 fte_freight_pricing_util.print_msg(fte_freight_pricing_util.G_DBG,'l_source_header_tab(i).distance :='||p_source_header_tab(i).distance);
390 fte_freight_pricing_util.print_msg(fte_freight_pricing_util.G_DBG,'l_source_header_tab(i).distance_uom :='||p_source_header_tab(i).distance_uom);
391 fte_freight_pricing_util.print_msg(fte_freight_pricing_util.G_DBG,'l_source_header_tab(i).vehicle_item_id :='||p_source_header_tab(i).vehicle_item_id);
392 fte_freight_pricing_util.print_msg(fte_freight_pricing_util.G_DBG,'l_source_header_tab(i).commodity_category_id :='||p_source_header_tab(i).commodity_category_id);
393
394
395 fte_freight_pricing_util.print_msg(l_log_level,'------------------------');
396
397
398 EXIT WHEN (i >= p_source_header_tab.LAST);
399 i := p_source_header_tab.NEXT(i);
400 END LOOP;
401 END IF;
402
403
404 FTE_FREIGHT_PRICING_UTIL.unset_method(l_log_level,l_api_name);
405
406 EXCEPTION
407 WHEN others THEN
408 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
409 FTE_FREIGHT_PRICING_UTIL.set_exception(l_api_name,FTE_FREIGHT_PRICING_UTIL.G_ERR,'g_others');
410 FTE_FREIGHT_PRICING_UTIL.print_msg(FTE_FREIGHT_PRICING_UTIL.G_ERR,'Unexpected Error : '||SQLCODE||' : '||SQLERRM);
411 FTE_FREIGHT_PRICING_UTIL.unset_method(l_log_level,l_api_name);
412
413 END print_source_header_tab;
414
415 PROCEDURE populate_rate (
416 p_source_header_rec IN FTE_PROCESS_REQUESTS.fte_source_header_rec,
417 p_service_rec IN lane_info_rec_type,
418 p_lane_rate IN NUMBER,
419 p_lane_rate_uom IN VARCHAR2,
420 p_fc_temp_price IN FTE_FREIGHT_PRICING.Freight_Cost_Temp_Tab_Type,
421 p_fc_temp_charge IN FTE_FREIGHT_PRICING.Freight_Cost_Temp_Tab_Type,
422 x_source_line_rates_tab IN OUT NOCOPY FTE_PROCESS_REQUESTS.fte_source_line_rates_tab,
423 x_source_header_rates_tab IN OUT NOCOPY FTE_PROCESS_REQUESTS.fte_source_header_rates_tab,
424 x_return_status OUT NOCOPY VARCHAR2)
425 IS
426 l_log_level NUMBER := FTE_FREIGHT_PRICING_UTIL.G_LOG;
427 l_api_name CONSTANT VARCHAR2(30) := 'POPULATE_RATE';
428 l_return_status VARCHAR2(1);
429
430 i NUMBER:=0;
431 j NUMBER:=0;
432 k NUMBER:=0;
433 ii NUMBER;
434 jj NUMBER;
435 l_found_group BOOLEAN;
436 l_need_move BOOLEAN;
437 l_converted_amount NUMBER;
438 l_target_currency VARCHAR2(10);
439 l_cost_type_code WSH_FREIGHT_COST_TYPES.freight_cost_type_code%TYPE;
440 l_sub_type_code WSH_FREIGHT_COST_TYPES.name%TYPE;
441
442 l_charge_tab rate_tab_type;
443 l_rate_tab rate_tab_type;
444 BEGIN
445 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
446
447 FTE_FREIGHT_PRICING_UTIL.reset_dbg_vars;
448 FTE_FREIGHT_PRICING_UTIL.set_method(l_log_level,l_api_name);
449
450 IF p_source_header_rec.currency is NULL THEN
451 l_target_currency := 'USD';
452 FTE_FREIGHT_PRICING_UTIL.print_msg(l_log_level,'source header currency is null, use USD');
453 ELSE
454 l_target_currency := p_source_header_rec.currency;
455 END IF;
456 FTE_FREIGHT_PRICING_UTIL.print_msg(l_log_level,'target currency is '||l_target_currency);
457
458 IF (p_lane_rate_uom <> l_target_currency) THEN
459 convert_amount(
460 p_from_currency =>p_lane_rate_uom,
461 p_from_amount =>p_lane_rate,
462 p_conversion_type =>p_source_header_rec.currency_conversion_type,
463 p_to_currency =>l_target_currency,
464 x_to_amount =>l_converted_amount,
465 x_return_status => l_return_status);
466
467 IF l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS THEN
468 FTE_FREIGHT_PRICING_UTIL.print_msg(l_log_level,'currency_conversion_failed');
469 raise FND_API.G_EXC_ERROR;
470 END IF;
471
472 ELSE
473 l_converted_amount := p_lane_rate;
474 END IF;
475 FTE_FREIGHT_PRICING_UTIL.print_msg(l_log_level,'convert p_lane_rate '||p_lane_rate||' to l_converted_amount='||l_converted_amount);
476
477 i := x_source_line_rates_tab.COUNT;
478 FTE_FREIGHT_PRICING_UTIL.print_msg(l_log_level,'number of existing source line rates records is '||i);
479
480 ii := x_source_header_rates_tab.COUNT;
481 FTE_FREIGHT_PRICING_UTIL.print_msg(l_log_level,'number of existing source header rates records is '||ii);
482
483 FTE_FREIGHT_PRICING_UTIL.print_msg(l_log_level,'in this sorted header_rates_tab finding the right position for the current header rate...');
484
485 l_found_group := false;
486 l_need_move := false;
487
488 IF ii > 0 THEN
489 jj:= x_source_header_rates_tab.FIRST;
490 LOOP
491 IF (x_source_header_rates_tab(jj).consolidation_id = p_source_header_rec.consolidation_id) THEN
492
493 l_found_group := true;
494
495 IF (l_converted_amount < x_source_header_rates_tab(jj).price) THEN
496 l_need_move := true;
497 EXIT; -- out of the loop
498 END IF;
499
500 ELSE -- not the same group
501 IF (l_found_group) THEN
502 l_need_move := true;
503 EXIT; -- out of the loop
504 END IF;
505 END IF;
506
507 IF (jj = x_source_header_rates_tab.LAST) THEN
508 jj := jj +1;
509 EXIT; -- out of the loop
510 ELSE
511 jj := x_source_header_rates_tab.NEXT(jj);
512 END IF;
513 END LOOP;
514 ELSE -- ii <= 0
515 jj := 1;
516 END IF; -- ii > 0
517
518 IF (l_need_move) THEN
519 FTE_FREIGHT_PRICING_UTIL.print_msg(l_log_level,'move source_header_rates_tab ('||jj||', '||x_source_header_rates_tab.LAST||' ) one records up.. ');
520 FOR ii IN REVERSE jj..x_source_header_rates_tab.LAST LOOP
521 FTE_FREIGHT_PRICING_UTIL.print_msg(l_log_level,'move ii='|| ii||' one record up...');
522 x_source_header_rates_tab(ii+1).consolidation_id := x_source_header_rates_tab(ii).consolidation_id;
523 x_source_header_rates_tab(ii+1).lane_id := x_source_header_rates_tab(ii).lane_id;
524 x_source_header_rates_tab(ii+1).carrier_id := x_source_header_rates_tab(ii).carrier_id;
525 x_source_header_rates_tab(ii+1).carrier_freight_code := x_source_header_rates_tab(ii).carrier_freight_code;
526 x_source_header_rates_tab(ii+1).service_level := x_source_header_rates_tab(ii).service_level;
527 x_source_header_rates_tab(ii+1).mode_of_transport := x_source_header_rates_tab(ii).mode_of_transport;
528 x_source_header_rates_tab(ii+1).ship_method_code := x_source_header_rates_tab(ii).ship_method_code;
529 x_source_header_rates_tab(ii+1).cost_type_id := x_source_header_rates_tab(ii).cost_type_id;
530 x_source_header_rates_tab(ii+1).cost_type := x_source_header_rates_tab(ii).cost_type;
531 x_source_header_rates_tab(ii+1).price := x_source_header_rates_tab(ii).price;
532 x_source_header_rates_tab(ii+1).currency := x_source_header_rates_tab(ii).currency;
533 x_source_header_rates_tab(ii+1).transit_time := x_source_header_rates_tab(ii).transit_time;
534 x_source_header_rates_tab(ii+1).transit_time_uom := x_source_header_rates_tab(ii).transit_time_uom;
535 x_source_header_rates_tab(ii+1).first_line_index := x_source_header_rates_tab(ii).first_line_index;
536 END LOOP;
537 END IF;
538
539 FTE_FREIGHT_PRICING_UTIL.print_msg(l_log_level,'populating jj '|| jj || ' with current header rates...');
540
541 x_source_header_rates_tab(jj).consolidation_id := p_source_header_rec.consolidation_id;
542 x_source_header_rates_tab(jj).lane_id := p_service_rec.lane_id;
543 x_source_header_rates_tab(jj).carrier_id := p_service_rec.carrier_id;
544 x_source_header_rates_tab(jj).carrier_freight_code := p_service_rec.carrier_freight_code;
545 x_source_header_rates_tab(jj).service_level := p_service_rec.service_type_code;
546 x_source_header_rates_tab(jj).mode_of_transport := p_service_rec.mode_of_transportation_code;
547 x_source_header_rates_tab(jj).ship_method_code := p_service_rec.ship_method_code;
548 x_source_header_rates_tab(jj).cost_type_id := null;
549 x_source_header_rates_tab(jj).cost_type := 'SUMMARY';
550 x_source_header_rates_tab(jj).price := l_converted_amount;
551 x_source_header_rates_tab(jj).currency := l_target_currency;
552 x_source_header_rates_tab(jj).transit_time := p_service_rec.transit_time;
553 x_source_header_rates_tab(jj).transit_time_uom := p_service_rec.transit_time_uom;
554 x_source_header_rates_tab(jj).first_line_index := i+1;
555
556 FTE_FREIGHT_PRICING_UTIL.print_msg(FTE_FREIGHT_PRICING_UTIL.G_DBG,'Loop 1 ');
557 j := p_fc_temp_price.FIRST;
558 IF (j IS NOT NULL) THEN
559 -- {
560 LOOP
561 -- {
562 IF (p_fc_temp_price(j).line_type_code = 'PRICE') THEN
563 -- {
564 -- store for later
565
566 IF (p_fc_temp_price(j).currency_code <> l_target_currency) THEN
567 convert_amount(
568 p_from_currency =>p_fc_temp_price(j).currency_code,
569 p_from_amount =>p_fc_temp_price(j).unit_amount,
570 p_conversion_type =>p_source_header_rec.currency_conversion_type,
571 p_to_currency =>l_target_currency,
572 x_to_amount =>l_converted_amount,
573 x_return_status => l_return_status);
574
575 IF l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS THEN
576 FTE_FREIGHT_PRICING_UTIL.print_msg(l_log_level,'currency_conversion_failed');
577 raise FND_API.G_EXC_ERROR;
578 END IF;
579
580 ELSE
581 l_converted_amount := p_fc_temp_price(j).unit_amount;
582 END IF;
583 FTE_FREIGHT_PRICING_UTIL.print_msg(l_log_level,'convert p_fc_temp_price(j).unit_amount '||p_fc_temp_price(j).unit_amount||' to l_converted_amount='||l_converted_amount);
584
585 l_rate_tab(p_fc_temp_price(j).delivery_detail_id).price := l_converted_amount ; -- including discount
586 l_rate_tab(p_fc_temp_price(j).delivery_detail_id).charge := 0;
587 l_rate_tab(p_fc_temp_price(j).delivery_detail_id).currency := l_target_currency ;
588 l_rate_tab(p_fc_temp_price(j).delivery_detail_id).qty := p_fc_temp_price(j).quantity ;
589 l_rate_tab(p_fc_temp_price(j).delivery_detail_id).uom := p_fc_temp_price(j).uom ;
590
591 i := i + 1;
592 x_source_line_rates_tab(i).source_line_id := p_fc_temp_price(j).delivery_detail_id;
593 x_source_line_rates_tab(i).cost_type_id := p_fc_temp_price(j).freight_cost_type_id;
594 x_source_line_rates_tab(i).line_type_code := p_fc_temp_price(j).line_type_code;
595
596 x_source_line_rates_tab(i).cost_type := 'FTEPRICE';
597 x_source_line_rates_tab(i).cost_sub_type := 'PRICE';
598
599 x_source_line_rates_tab(i).priced_quantity := p_fc_temp_price(j).quantity;
600 x_source_line_rates_tab(i).priced_uom := p_fc_temp_price(j).uom;
601 x_source_line_rates_tab(i).adjusted_unit_price := (l_converted_amount)/(p_fc_temp_price(j).quantity) ; -- adjusted unit price
602 x_source_line_rates_tab(i).adjusted_price := l_converted_amount ; -- adjusted unit price (including discount)
603 x_source_line_rates_tab(i).currency := l_target_currency;
604
605 IF (p_fc_temp_price(j).currency_code <> l_target_currency) THEN
606 convert_amount(
607 p_from_currency =>p_fc_temp_price(j).currency_code,
608 p_from_amount =>p_fc_temp_price(j).charge_unit_value,
609 p_conversion_type =>p_source_header_rec.currency_conversion_type,
610 p_to_currency =>l_target_currency,
611 x_to_amount =>l_converted_amount,
612 x_return_status => l_return_status);
613
614 IF l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS THEN
615 FTE_FREIGHT_PRICING_UTIL.print_msg(l_log_level,'currency_conversion_failed');
616 raise FND_API.G_EXC_ERROR;
617 END IF;
618
619 ELSE
620 l_converted_amount := p_fc_temp_price(j).charge_unit_value;
621 END IF;
622 FTE_FREIGHT_PRICING_UTIL.print_msg(l_log_level,'convert p_fc_temp_price(j).charge_unit_value '||p_fc_temp_price(j).charge_unit_value||' to l_converted_amount='||l_converted_amount);
623
624 x_source_line_rates_tab(i).unit_price := l_converted_amount;
625 x_source_line_rates_tab(i).base_price := l_converted_amount * p_fc_temp_price(j).quantity;
626
627 x_source_line_rates_tab(i).consolidation_id := p_source_header_rec.consolidation_id;
628 x_source_line_rates_tab(i).lane_id := p_service_rec.lane_id;
629 x_source_line_rates_tab(i).carrier_id := p_service_rec.carrier_id;
630 x_source_line_rates_tab(i).carrier_freight_code := p_service_rec.carrier_freight_code;
631 x_source_line_rates_tab(i).service_level := p_service_rec.service_type_code;
632 x_source_line_rates_tab(i).mode_of_transport := p_service_rec.mode_of_transportation_code;
633 x_source_line_rates_tab(i).ship_method_code := p_service_rec.ship_method_code;
634
635 -- }
636 END IF;
637
638 EXIT WHEN ( j >= p_fc_temp_price.LAST);
639 j := p_fc_temp_price.NEXT(j);
640 -- }
641 END LOOP;
642 -- }
643 END IF;
644
645
646 FTE_FREIGHT_PRICING_UTIL.print_msg(FTE_FREIGHT_PRICING_UTIL.G_DBG,'Loop 2 ');
647 -- sum up charges by source_line_id
648 j := p_fc_temp_charge.FIRST;
649 IF (j IS NOT NULL) THEN
650 LOOP
651 IF (p_fc_temp_charge(j).line_type_code = 'CHARGE') THEN
652
653 IF l_charge_tab.EXISTS(p_fc_temp_charge(j).delivery_detail_id) THEN
654 l_charge_tab(p_fc_temp_charge(j).delivery_detail_id).charge := l_charge_tab(p_fc_temp_charge(j).delivery_detail_id).charge + p_fc_temp_charge(j).total_amount;
655 ELSE
656 l_charge_tab(p_fc_temp_charge(j).delivery_detail_id).charge := p_fc_temp_charge(j).total_amount;
657 l_charge_tab(p_fc_temp_charge(j).delivery_detail_id).currency := p_fc_temp_charge(j).currency_code;
658 END IF;
659
660 END IF;
661 EXIT WHEN ( j = p_fc_temp_charge.LAST);
662 j := p_fc_temp_charge.NEXT(j);
663 END LOOP;
664 END IF;
665
666 FTE_FREIGHT_PRICING_UTIL.print_msg(FTE_FREIGHT_PRICING_UTIL.G_DBG,'Loop 3 ');
667 -- now for each value in l_charge_tab, create a source_line_rec for charges
668 k := l_charge_tab.FIRST;
669 IF (k IS NOT NULL) THEN
670 LOOP
671 i := i + 1;
672 x_source_line_rates_tab(i).source_line_id := k;
673 x_source_line_rates_tab(i).cost_type_id := NULL; -- fix this
674 x_source_line_rates_tab(i).line_type_code := 'CHARGE';
675 x_source_line_rates_tab(i).cost_type := 'FTECHARGE';
676 x_source_line_rates_tab(i).cost_sub_type := NULL;
677 x_source_line_rates_tab(i).priced_quantity := l_rate_tab(k).qty;
678 x_source_line_rates_tab(i).priced_uom := l_rate_tab(k).uom;
679
680 IF (l_charge_tab(k).currency <> l_target_currency) THEN
681 convert_amount(
682 p_from_currency =>l_charge_tab(k).currency,
683 p_from_amount =>l_charge_tab(k).charge,
684 p_conversion_type =>p_source_header_rec.currency_conversion_type,
685 p_to_currency =>l_target_currency,
686 x_to_amount =>l_converted_amount,
687 x_return_status => l_return_status);
688
689 IF l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS THEN
690 FTE_FREIGHT_PRICING_UTIL.print_msg(l_log_level,'currency_conversion_failed');
691 raise FND_API.G_EXC_ERROR;
692 END IF;
693
694 ELSE
695 l_converted_amount := l_charge_tab(k).charge;
696 END IF;
697 FTE_FREIGHT_PRICING_UTIL.print_msg(l_log_level,'convert l_charge_tab(k).charge '||l_charge_tab(k).charge||' to l_converted_amount='||l_converted_amount);
698
699 x_source_line_rates_tab(i).unit_price := l_converted_amount / l_rate_tab(k).qty;
700 x_source_line_rates_tab(i).base_price := l_converted_amount;
701 x_source_line_rates_tab(i).adjusted_unit_price := l_converted_amount / l_rate_tab(k).qty;
702 x_source_line_rates_tab(i).adjusted_price := l_converted_amount;
703 x_source_line_rates_tab(i).currency := l_target_currency;
704
705 x_source_line_rates_tab(i).consolidation_id := p_source_header_rec.consolidation_id;
706 x_source_line_rates_tab(i).lane_id := p_service_rec.lane_id;
707 x_source_line_rates_tab(i).carrier_id := p_service_rec.carrier_id;
708 x_source_line_rates_tab(i).carrier_freight_code := p_service_rec.carrier_freight_code;
709 x_source_line_rates_tab(i).service_level := p_service_rec.service_type_code;
710 x_source_line_rates_tab(i).mode_of_transport := p_service_rec.mode_of_transportation_code;
711 x_source_line_rates_tab(i).ship_method_code := p_service_rec.ship_method_code;
712
713 EXIT WHEN (k = l_charge_tab.LAST);
714 k := l_charge_tab.NEXT(k);
715 END LOOP;
716 END IF;
717
718 FTE_FREIGHT_PRICING_UTIL.unset_method(l_log_level,l_api_name);
719
720 EXCEPTION
721 WHEN FND_API.G_EXC_ERROR THEN
722 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
723 FTE_FREIGHT_PRICING_UTIL.set_exception(l_api_name,FTE_FREIGHT_PRICING_UTIL.G_ERR,'FND_API.G_EXC_ERROR');
724 FTE_FREIGHT_PRICING_UTIL.unset_method(l_log_level,l_api_name);
725 WHEN others THEN
726 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
727 FTE_FREIGHT_PRICING_UTIL.set_exception(l_api_name,FTE_FREIGHT_PRICING_UTIL.G_ERR,'g_others');
728 FTE_FREIGHT_PRICING_UTIL.print_msg(FTE_FREIGHT_PRICING_UTIL.G_ERR,'Unexpected Error : '||SQLCODE||' : '||SQLERRM);
729 FTE_FREIGHT_PRICING_UTIL.unset_method(l_log_level,l_api_name);
730 END populate_rate;
731
732 -- For FTE J Estimate Rates
733 -- No currency conversions
734 -- No sorting of rates
735
736 PROCEDURE populate_rate_2 (
737 p_source_header_rec IN FTE_PROCESS_REQUESTS.fte_source_header_rec,
738 p_service_rec IN lane_info_rec_type,
739 p_lane_rate IN NUMBER,
740 p_lane_rate_uom IN VARCHAR2,
741 p_fc_temp_price IN FTE_FREIGHT_PRICING.Freight_Cost_Temp_Tab_Type,
742 p_fc_temp_charge IN FTE_FREIGHT_PRICING.Freight_Cost_Temp_Tab_Type,
743 x_source_line_rates_tab IN OUT NOCOPY FTE_PROCESS_REQUESTS.fte_source_line_rates_tab,
744 x_source_header_rates_tab IN OUT NOCOPY FTE_PROCESS_REQUESTS.fte_source_header_rates_tab,
745 x_return_status OUT NOCOPY VARCHAR2)
746 IS
747 l_log_level NUMBER := FTE_FREIGHT_PRICING_UTIL.G_LOG;
748 l_api_name CONSTANT VARCHAR2(30) := 'POPULATE_RATE_2';
749 l_return_status VARCHAR2(1);
750
751 i NUMBER:=0;
752 j NUMBER:=0;
753 k NUMBER:=0;
754 ii NUMBER;
755 l_found_group BOOLEAN;
756 l_need_move BOOLEAN;
757 l_converted_amount NUMBER;
758 l_target_currency VARCHAR2(10);
759 l_cost_type_code WSH_FREIGHT_COST_TYPES.freight_cost_type_code%TYPE;
760 l_sub_type_code WSH_FREIGHT_COST_TYPES.name%TYPE;
761
762 l_charge_tab rate_tab_type;
763 l_rate_tab rate_tab_type;
764 BEGIN
765 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
766
767 FTE_FREIGHT_PRICING_UTIL.reset_dbg_vars;
768 FTE_FREIGHT_PRICING_UTIL.set_method(l_log_level,l_api_name);
769
770
771 i := x_source_line_rates_tab.COUNT;
772 FTE_FREIGHT_PRICING_UTIL.print_msg(l_log_level,'number of existing source line rates records is '||i);
773
774 ii := x_source_header_rates_tab.COUNT;
775 FTE_FREIGHT_PRICING_UTIL.print_msg(l_log_level,'number of existing source header rates records is '||ii);
776
777 ii := ii+1;
778 x_source_header_rates_tab(ii).consolidation_id := p_source_header_rec.consolidation_id;
779 x_source_header_rates_tab(ii).lane_id := p_service_rec.lane_id;
780 x_source_header_rates_tab(ii).carrier_id := p_service_rec.carrier_id;
781 x_source_header_rates_tab(ii).carrier_freight_code := p_service_rec.carrier_freight_code;
782 x_source_header_rates_tab(ii).service_level := p_service_rec.service_type_code;
783 x_source_header_rates_tab(ii).mode_of_transport := p_service_rec.mode_of_transportation_code;
784 x_source_header_rates_tab(ii).ship_method_code := p_service_rec.ship_method_code;
785 x_source_header_rates_tab(ii).cost_type_id := null;
786 x_source_header_rates_tab(ii).cost_type := 'SUMMARY';
787 x_source_header_rates_tab(ii).price := p_lane_rate;
788 x_source_header_rates_tab(ii).currency := p_lane_rate_uom;
789 x_source_header_rates_tab(ii).transit_time := p_service_rec.transit_time;
790 x_source_header_rates_tab(ii).transit_time_uom := p_service_rec.transit_time_uom;
791 x_source_header_rates_tab(ii).first_line_index := i+1;
792
793 FTE_FREIGHT_PRICING_UTIL.print_msg(FTE_FREIGHT_PRICING_UTIL.G_DBG,'Loop 1 ');
794 j := p_fc_temp_price.FIRST;
795 IF (j IS NOT NULL) THEN
796 -- {
797 LOOP
798 -- {
799 IF (p_fc_temp_price(j).line_type_code = 'PRICE') THEN
800 -- {
801 -- store for later
802 l_rate_tab(p_fc_temp_price(j).delivery_detail_id).price := p_fc_temp_price(j).unit_amount ; -- including discount
803 l_rate_tab(p_fc_temp_price(j).delivery_detail_id).charge := 0;
804 l_rate_tab(p_fc_temp_price(j).delivery_detail_id).currency := p_fc_temp_price(j).currency_code ;
805 l_rate_tab(p_fc_temp_price(j).delivery_detail_id).qty := p_fc_temp_price(j).quantity ;
806 l_rate_tab(p_fc_temp_price(j).delivery_detail_id).uom := p_fc_temp_price(j).uom ;
807
808 i := i + 1;
809 x_source_line_rates_tab(i).source_line_id := p_fc_temp_price(j).delivery_detail_id;
810 x_source_line_rates_tab(i).cost_type_id := p_fc_temp_price(j).freight_cost_type_id;
811 x_source_line_rates_tab(i).line_type_code := p_fc_temp_price(j).line_type_code;
812
813 x_source_line_rates_tab(i).cost_type := 'FTEPRICE';
814 x_source_line_rates_tab(i).cost_sub_type := 'PRICE';
815
816 x_source_line_rates_tab(i).priced_quantity := p_fc_temp_price(j).quantity;
817 x_source_line_rates_tab(i).priced_uom := p_fc_temp_price(j).uom;
818 x_source_line_rates_tab(i).adjusted_unit_price := (p_fc_temp_price(j).unit_amount)/(p_fc_temp_price(j).quantity) ; -- adjusted unit price
819 x_source_line_rates_tab(i).adjusted_price := p_fc_temp_price(j).unit_amount ; -- adjusted unit price (including discount)
820 x_source_line_rates_tab(i).currency := p_fc_temp_price(j).currency_code;
821
822 x_source_line_rates_tab(i).unit_price := p_fc_temp_price(j).charge_unit_value;
823 x_source_line_rates_tab(i).base_price := p_fc_temp_price(j).charge_unit_value * p_fc_temp_price(j).quantity;
824
825 x_source_line_rates_tab(i).consolidation_id := p_source_header_rec.consolidation_id;
826 x_source_line_rates_tab(i).lane_id := p_service_rec.lane_id;
827 x_source_line_rates_tab(i).carrier_id := p_service_rec.carrier_id;
828 x_source_line_rates_tab(i).carrier_freight_code := p_service_rec.carrier_freight_code;
829 x_source_line_rates_tab(i).service_level := p_service_rec.service_type_code;
830 x_source_line_rates_tab(i).mode_of_transport := p_service_rec.mode_of_transportation_code;
831 x_source_line_rates_tab(i).ship_method_code := p_service_rec.ship_method_code;
832
833 -- }
834 END IF;
835
836 EXIT WHEN ( j >= p_fc_temp_price.LAST);
837 j := p_fc_temp_price.NEXT(j);
838 -- }
839 END LOOP;
840 -- }
841 END IF;
842
843
844 FTE_FREIGHT_PRICING_UTIL.print_msg(FTE_FREIGHT_PRICING_UTIL.G_DBG,'Loop 2 ');
845 -- sum up charges by source_line_id
846 j := p_fc_temp_charge.FIRST;
847 IF (j IS NOT NULL) THEN
848 LOOP
849 IF (p_fc_temp_charge(j).line_type_code = 'CHARGE') THEN
850
851 IF l_charge_tab.EXISTS(p_fc_temp_charge(j).delivery_detail_id) THEN
852 l_charge_tab(p_fc_temp_charge(j).delivery_detail_id).charge := l_charge_tab(p_fc_temp_charge(j).delivery_detail_id).charge + p_fc_temp_charge(j).total_amount;
853 ELSE
854 l_charge_tab(p_fc_temp_charge(j).delivery_detail_id).charge := p_fc_temp_charge(j).total_amount;
855 l_charge_tab(p_fc_temp_charge(j).delivery_detail_id).currency := p_fc_temp_charge(j).currency_code;
856 END IF;
857
858 END IF;
859 EXIT WHEN ( j = p_fc_temp_charge.LAST);
860 j := p_fc_temp_charge.NEXT(j);
861 END LOOP;
862 END IF;
863
864 FTE_FREIGHT_PRICING_UTIL.print_msg(FTE_FREIGHT_PRICING_UTIL.G_DBG,'Loop 3 ');
865 -- now for each value in l_charge_tab, create a source_line_rec for charges
866 k := l_charge_tab.FIRST;
867 IF (k IS NOT NULL) THEN
868 LOOP
869 i := i + 1;
870 x_source_line_rates_tab(i).source_line_id := k;
871 x_source_line_rates_tab(i).cost_type_id := NULL; -- fix this
872 x_source_line_rates_tab(i).line_type_code := 'CHARGE';
873 x_source_line_rates_tab(i).cost_type := 'FTECHARGE';
874 x_source_line_rates_tab(i).cost_sub_type := NULL;
875 x_source_line_rates_tab(i).priced_quantity := l_rate_tab(k).qty;
876 x_source_line_rates_tab(i).priced_uom := l_rate_tab(k).uom;
877
878 x_source_line_rates_tab(i).unit_price := l_charge_tab(k).charge / l_rate_tab(k).qty;
879 x_source_line_rates_tab(i).base_price := l_charge_tab(k).charge;
880 x_source_line_rates_tab(i).adjusted_unit_price := l_charge_tab(k).charge / l_rate_tab(k).qty;
881 x_source_line_rates_tab(i).adjusted_price := l_charge_tab(k).charge;
882 x_source_line_rates_tab(i).currency := l_charge_tab(k).currency;
883
884 x_source_line_rates_tab(i).consolidation_id := p_source_header_rec.consolidation_id;
885 x_source_line_rates_tab(i).lane_id := p_service_rec.lane_id;
886 x_source_line_rates_tab(i).carrier_id := p_service_rec.carrier_id;
887 x_source_line_rates_tab(i).carrier_freight_code := p_service_rec.carrier_freight_code;
888 x_source_line_rates_tab(i).service_level := p_service_rec.service_type_code;
889 x_source_line_rates_tab(i).mode_of_transport := p_service_rec.mode_of_transportation_code;
890 x_source_line_rates_tab(i).ship_method_code := p_service_rec.ship_method_code;
891
892 EXIT WHEN (k = l_charge_tab.LAST);
893 k := l_charge_tab.NEXT(k);
894 END LOOP;
895 END IF;
896
897 FTE_FREIGHT_PRICING_UTIL.unset_method(l_log_level,l_api_name);
898
899 EXCEPTION
900 WHEN FND_API.G_EXC_ERROR THEN
901 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
902 FTE_FREIGHT_PRICING_UTIL.set_exception(l_api_name,FTE_FREIGHT_PRICING_UTIL.G_ERR,'FND_API.G_EXC_ERROR');
903 FTE_FREIGHT_PRICING_UTIL.unset_method(l_log_level,l_api_name);
904 WHEN others THEN
905 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
906 FTE_FREIGHT_PRICING_UTIL.set_exception(l_api_name,FTE_FREIGHT_PRICING_UTIL.G_ERR,'g_others');
907 FTE_FREIGHT_PRICING_UTIL.print_msg(FTE_FREIGHT_PRICING_UTIL.G_ERR,'Unexpected Error : '||SQLCODE||' : '||SQLERRM);
908 FTE_FREIGHT_PRICING_UTIL.unset_method(l_log_level,l_api_name);
909 END populate_rate_2;
910
911 PROCEDURE populate_shipment(
912 p_source_header_rec IN FTE_PROCESS_REQUESTS.fte_source_header_rec,
913 p_source_line_tab IN FTE_PROCESS_REQUESTS.fte_source_line_tab,
914 x_return_status OUT NOCOPY VARCHAR2)
915 IS
916 l_log_level NUMBER := FTE_FREIGHT_PRICING_UTIL.G_LOG;
917 l_api_name CONSTANT VARCHAR2(30) := 'POPULATE_SHIPMENT';
918 l_return_status VARCHAR2(1);
919 i NUMBER;
920 idx NUMBER;
921 BEGIN
922 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
923
924 FTE_FREIGHT_PRICING_UTIL.reset_dbg_vars;
925 FTE_FREIGHT_PRICING_UTIL.set_method(l_log_level,l_api_name);
926
927 fte_freight_pricing.g_shipment_line_rows.DELETE;
928
929 FOR i in p_source_line_tab.FIRST..p_source_line_tab.LAST LOOP
930 IF (p_source_line_tab(i).consolidation_id = p_source_header_rec.consolidation_id
931 AND nvl(p_source_line_tab(i).freight_rating_flag,'Y') = 'Y' ) THEN
932
933 idx := p_source_line_tab(i).source_line_id;
934 fte_freight_pricing.g_shipment_line_rows(idx).delivery_detail_id := p_source_line_tab(i).source_line_id;
935 fte_freight_pricing.g_shipment_line_rows(idx).delivery_id := p_source_line_tab(i).consolidation_id;
936 fte_freight_pricing.g_shipment_line_rows(idx).delivery_leg_id := NULL;
937 fte_freight_pricing.g_shipment_line_rows(idx).reprice_required := 'Y';
938 fte_freight_pricing.g_shipment_line_rows(idx).parent_delivery_detail_id := NULL;
939 fte_freight_pricing.g_shipment_line_rows(idx).customer_id := p_source_line_tab(i).customer_id;
940 fte_freight_pricing.g_shipment_line_rows(idx).sold_to_contact_id := NULL;
941 fte_freight_pricing.g_shipment_line_rows(idx).inventory_item_id := p_source_line_tab(i).inventory_item_id;
942 fte_freight_pricing.g_shipment_line_rows(idx).item_description := NULL;
943 fte_freight_pricing.g_shipment_line_rows(idx).hazard_class_id := NULL;
944 fte_freight_pricing.g_shipment_line_rows(idx).country_of_origin := NULL;
945 fte_freight_pricing.g_shipment_line_rows(idx).classification := NULL;
946 fte_freight_pricing.g_shipment_line_rows(idx).requested_quantity := p_source_line_tab(i).source_quantity;
947 fte_freight_pricing.g_shipment_line_rows(idx).requested_quantity_uom := p_source_line_tab(i).source_quantity_uom;
948 fte_freight_pricing.g_shipment_line_rows(idx).master_container_item_id := NULL;
949 fte_freight_pricing.g_shipment_line_rows(idx).detail_container_item_id := NULL;
950 fte_freight_pricing.g_shipment_line_rows(idx).customer_item_id := NULL;
951 fte_freight_pricing.g_shipment_line_rows(idx).net_weight := p_source_line_tab(i).weight;
952 fte_freight_pricing.g_shipment_line_rows(idx).organization_id := p_source_line_tab(i).ship_from_org_id;
953 fte_freight_pricing.g_shipment_line_rows(idx).container_flag := 'N';
954 fte_freight_pricing.g_shipment_line_rows(idx).container_type_code := NULL;
955 fte_freight_pricing.g_shipment_line_rows(idx).container_name := NULL;
956 fte_freight_pricing.g_shipment_line_rows(idx).fill_percent := NULL;
957 fte_freight_pricing.g_shipment_line_rows(idx).gross_weight := p_source_line_tab(i).weight;
958 fte_freight_pricing.g_shipment_line_rows(idx).currency_code := p_source_line_tab(i).freight_rate_currency;
959 fte_freight_pricing.g_shipment_line_rows(idx).freight_class_cat_id := NULL;
960 fte_freight_pricing.g_shipment_line_rows(idx).commodity_code_cat_id := NULL;
961 fte_freight_pricing.g_shipment_line_rows(idx).weight_uom_code := p_source_line_tab(i).weight_uom_code;
962 fte_freight_pricing.g_shipment_line_rows(idx).volume := p_source_line_tab(i).volume;
963 fte_freight_pricing.g_shipment_line_rows(idx).volume_uom_code := p_source_line_tab(i).volume_uom_code;
964 fte_freight_pricing.g_shipment_line_rows(idx).tp_attribute_category := NULL;
965 fte_freight_pricing.g_shipment_line_rows(idx).tp_attribute1 := NULL;
966 fte_freight_pricing.g_shipment_line_rows(idx).tp_attribute2 := NULL;
967 fte_freight_pricing.g_shipment_line_rows(idx).tp_attribute3 := NULL;
968 fte_freight_pricing.g_shipment_line_rows(idx).tp_attribute4 := NULL;
969 fte_freight_pricing.g_shipment_line_rows(idx).tp_attribute5 := NULL;
970 fte_freight_pricing.g_shipment_line_rows(idx).tp_attribute6 := NULL;
971 fte_freight_pricing.g_shipment_line_rows(idx).tp_attribute7 := NULL;
972 fte_freight_pricing.g_shipment_line_rows(idx).tp_attribute8 := NULL;
973 fte_freight_pricing.g_shipment_line_rows(idx).tp_attribute9 := NULL;
974 fte_freight_pricing.g_shipment_line_rows(idx).tp_attribute10 := NULL;
975 fte_freight_pricing.g_shipment_line_rows(idx).tp_attribute11 := NULL;
976 fte_freight_pricing.g_shipment_line_rows(idx).tp_attribute12 := NULL;
977 fte_freight_pricing.g_shipment_line_rows(idx).tp_attribute13 := NULL;
978 fte_freight_pricing.g_shipment_line_rows(idx).tp_attribute14 := NULL;
979 fte_freight_pricing.g_shipment_line_rows(idx).tp_attribute15 := NULL;
980 fte_freight_pricing.g_shipment_line_rows(idx).attribute_category := NULL;
981 fte_freight_pricing.g_shipment_line_rows(idx).attribute1 := NULL;
982 fte_freight_pricing.g_shipment_line_rows(idx).attribute2 := NULL;
983 fte_freight_pricing.g_shipment_line_rows(idx).attribute3 := NULL;
984 fte_freight_pricing.g_shipment_line_rows(idx).attribute4 := NULL;
985 fte_freight_pricing.g_shipment_line_rows(idx).attribute5 := NULL;
986 fte_freight_pricing.g_shipment_line_rows(idx).attribute6 := NULL;
987 fte_freight_pricing.g_shipment_line_rows(idx).attribute7 := NULL;
988 fte_freight_pricing.g_shipment_line_rows(idx).attribute8 := NULL;
989 fte_freight_pricing.g_shipment_line_rows(idx).attribute9 := NULL;
990 fte_freight_pricing.g_shipment_line_rows(idx).attribute10 := NULL;
991 fte_freight_pricing.g_shipment_line_rows(idx).attribute11 := NULL;
992 fte_freight_pricing.g_shipment_line_rows(idx).attribute12 := NULL;
993 fte_freight_pricing.g_shipment_line_rows(idx).attribute13 := NULL;
994 fte_freight_pricing.g_shipment_line_rows(idx).attribute14 := NULL;
995 fte_freight_pricing.g_shipment_line_rows(idx).attribute15 := NULL;
996 fte_freight_pricing.g_shipment_line_rows(idx).source_type := p_source_line_tab(i).source_type;
997 fte_freight_pricing.g_shipment_line_rows(idx).source_line_id := p_source_line_tab(i).source_line_id;
998 fte_freight_pricing.g_shipment_line_rows(idx).source_header_id := p_source_line_tab(i).source_header_id;
999 fte_freight_pricing.g_shipment_line_rows(idx).source_consolidation_id := p_source_line_tab(i).consolidation_id;
1000 fte_freight_pricing.g_shipment_line_rows(idx).ship_date := p_source_line_tab(i).ship_date;
1001 fte_freight_pricing.g_shipment_line_rows(idx).arrival_date := p_source_line_tab(i).arrival_date;
1002 -- FTE J rate estimate
1003 fte_freight_pricing.g_shipment_line_rows(idx).comm_category_id := p_source_line_tab(i).commodity_category_id;
1004
1005 END IF;
1006 END LOOP; -- p_source_line_tab
1007
1008 FTE_FREIGHT_PRICING_UTIL.unset_method(l_log_level,l_api_name);
1009
1010 EXCEPTION
1011 WHEN others THEN
1012 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
1013 FTE_FREIGHT_PRICING_UTIL.set_exception(l_api_name,FTE_FREIGHT_PRICING_UTIL.G_ERR,'g_others');
1014 FTE_FREIGHT_PRICING_UTIL.print_msg(FTE_FREIGHT_PRICING_UTIL.G_ERR,'Unexpected Error : '||SQLCODE||' : '||SQLERRM);
1015 FTE_FREIGHT_PRICING_UTIL.unset_method(l_log_level,l_api_name);
1016 END populate_shipment;
1017
1018 PROCEDURE Search_Services(
1019 p_source_header_rec IN FTE_PROCESS_REQUESTS.fte_source_header_rec,
1020 p_ignore_TL IN VARCHAR2 DEFAULT 'Y', -- FTE J estimate rate
1021 p_filter_shipmethod IN VARCHAR2 DEFAULT 'Y', -- FTE J estimate rate
1022 x_matched_services OUT NOCOPY lane_info_tab_type,
1023 x_return_status OUT NOCOPY VARCHAR2)
1024 IS
1025 l_log_level NUMBER := FTE_FREIGHT_PRICING_UTIL.G_LOG;
1026 l_api_name CONSTANT VARCHAR2(30) := 'SEARCH_SERVICES';
1027 l_return_status VARCHAR2(1);
1028 l_msg_count NUMBER := 0;
1029 l_msg_data VARCHAR2(32767);
1030 l_search_criteria fte_search_criteria_rec;
1031 l_lanes_tab fte_lane_tab;
1032 l_lane_rec fte_lane_rec;
1033 l_schedules_tab fte_schedule_tab;
1034 i NUMBER;
1035 j NUMBER;
1036 l_filter_lanes BOOLEAN;
1037 l_lead_time NUMBER;
1038 l_ship_method_code VARCHAR2(30);
1039 l_generic_carrier VARCHAR2(1);
1040 l_carrier_id NUMBER;
1041
1042 c_carr_srv_rec c_carrier_services%ROWTYPE;
1043 l_ship_method_meaning wsh_carrier_services.ship_method_meaning%type;
1044 l_mode_of_transport wsh_carrier_services.mode_of_transport%type;
1045 l_service_level wsh_carrier_services.service_level%type;
1046
1047 BEGIN
1048 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
1049
1050 FTE_FREIGHT_PRICING_UTIL.reset_dbg_vars;
1051 FTE_FREIGHT_PRICING_UTIL.set_method(l_log_level,l_api_name);
1052
1053 FTE_FREIGHT_PRICING_UTIL.print_msg(l_log_level,'p_ignore_TL='||p_ignore_TL);
1054 FTE_FREIGHT_PRICING_UTIL.print_msg(l_log_level,'p_filter_shipmethod='||p_filter_shipmethod);
1055
1056 l_ship_method_code := p_source_header_rec.ship_method_code;
1057 l_carrier_id := p_source_header_rec.carrier_id;
1058 l_mode_of_transport := p_source_header_rec.mode_of_transport;
1059 l_service_level := p_source_header_rec.service_level;
1060
1061 IF (l_ship_method_code is not null)
1062 AND (l_carrier_id is null
1063 OR l_mode_of_transport is null
1064 OR l_service_level is null) THEN
1065
1066 OPEN c_carrier_services(l_ship_method_code);
1067 FETCH c_carrier_services INTO c_carr_srv_rec;
1068 CLOSE c_carrier_services;
1069
1070 l_carrier_id := c_carr_srv_rec.carrier_id;
1071 l_mode_of_transport := c_carr_srv_rec.mode_of_transport;
1072 l_service_level := c_carr_srv_rec.service_level;
1073
1074 END IF;
1075
1076 FTE_FREIGHT_PRICING_UTIL.print_msg(l_log_level,'l_carrier_id='||l_carrier_id);
1077 FTE_FREIGHT_PRICING_UTIL.print_msg(l_log_level,'l_mode_of_transport'||l_mode_of_transport);
1078 FTE_FREIGHT_PRICING_UTIL.print_msg(l_log_level,'l_service_level='||l_service_level);
1079 FTE_FREIGHT_PRICING_UTIL.print_msg(l_log_level,'l_ship_method_code='||l_ship_method_code);
1080
1081 IF l_carrier_id is not null THEN
1082
1083 OPEN c_get_generic_carrier_flag(l_carrier_id);
1084 FETCH c_get_generic_carrier_flag INTO l_generic_carrier;
1085 CLOSE c_get_generic_carrier_flag;
1086
1087 IF (l_generic_carrier = 'Y') THEN
1088 l_carrier_id := null;
1089 END IF;
1090 END IF;
1091
1092 FTE_FREIGHT_PRICING_UTIL.print_msg(l_log_level,'populate l_search_criteria... ');
1093 FTE_FREIGHT_PRICING_UTIL.print_msg(l_log_level,'p_source_header_rec.ship_from_location_id='||p_source_header_rec.ship_from_location_id);
1094 FTE_FREIGHT_PRICING_UTIL.print_msg(l_log_level,'p_source_header_rec.ship_to_location_id='||p_source_header_rec.ship_to_location_id);
1095 FTE_FREIGHT_PRICING_UTIL.print_msg(l_log_level,'l_mode_of_transport='||l_mode_of_transport);
1096 FTE_FREIGHT_PRICING_UTIL.print_msg(l_log_level,'l_carrier_id='||l_carrier_id);
1097 FTE_FREIGHT_PRICING_UTIL.print_msg(l_log_level,'l_service_level='||l_service_level);
1098 FTE_FREIGHT_PRICING_UTIL.print_msg(l_log_level,'p_source_header_rec.ship_date='||p_source_header_rec.ship_date);
1099 FTE_FREIGHT_PRICING_UTIL.print_msg(l_log_level,'p_source_header_rec.arrival_date='||p_source_header_rec.arrival_date);
1100 FTE_FREIGHT_PRICING_UTIL.print_msg(l_log_level,'p_source_header_rec.ship_from_location_id='||p_source_header_rec.ship_from_location_id);
1101 FTE_FREIGHT_PRICING_UTIL.print_msg(l_log_level,'p_source_header_rec.ship_to_site_id='||p_source_header_rec.ship_to_site_id);
1102
1103 l_search_criteria := fte_search_criteria_rec(
1104 relax_flag => 'Y',
1105 origin_loc_id => p_source_header_rec.ship_from_location_id,
1106 destination_loc_id => p_source_header_rec.ship_to_location_id,
1107 -- FTE J rate estimate : begin --
1108 origin_country => p_source_header_rec.origin_country,
1109 origin_state => p_source_header_rec.origin_state,
1110 origin_city => p_source_header_rec.origin_city,
1111 origin_zip => p_source_header_rec.origin_zip,
1112 destination_country => p_source_header_rec.destination_country,
1113 destination_state => p_source_header_rec.destination_state,
1114 destination_city => p_source_header_rec.destination_city,
1115 destination_zip => p_source_header_rec.destination_zip,
1116 -- FTE J rate estimate : end --
1117 mode_of_transport => l_mode_of_transport,
1118 lane_number => null,
1119 carrier_id => l_carrier_id,
1120 carrier_name => null,
1121 commodity_catg_id => null,
1122 commodity => null,
1123 service_code => l_service_level,
1124 service => null,
1125 --Changes to fte_search_criteria_rec 11-Oct-2004(remove equipment_code,equipment,add tariff_name
1126 --equipment_code => null,
1127 --equipment => null,
1128 tariff_name => null,
1129
1130 schedule_only_flag => null,
1131 dep_date_from => p_source_header_rec.ship_date,
1132 dep_date_to => p_source_header_rec.ship_date,
1133 arr_date_from => p_source_header_rec.arrival_date,
1134 arr_date_to => p_source_header_rec.arrival_date,
1135 lane_ids_string => null,
1136 delivery_leg_id => null,
1137 exists_in_database => null,
1138 delivery_id => null,
1139 sequence_number => null,
1140 pick_up_stop_id => null,
1141 drop_off_stop_id => null,
1142 pickupstop_location_id => p_source_header_rec.ship_from_location_id,
1143 -- dropoffstop_location_id => null,
1144 dropoffstop_location_id => p_source_header_rec.ship_to_location_id, -- FTE J rate estimate
1145 ship_to_site_id => p_source_header_rec.ship_to_site_id,
1146 vehicle_id => null,
1147 --Changes made to fte_search_criteria_rec 19-FEB-2004
1148 effective_date => p_source_header_rec.ship_date,
1149 effective_date_type => '='
1150 );
1151
1152 FTE_FREIGHT_PRICING_UTIL.print_msg(l_log_level,'Calling FTE_LANE_SEARCH.Search_Lanes... ');
1153
1154 FTE_LANE_SEARCH.Search_Lanes(p_search_criteria => l_search_criteria,
1155 p_search_type => 'L',
1156 p_source_type => 'R',
1157 p_num_results => 999,
1158 x_lane_results => l_lanes_tab,
1159 x_schedule_results => l_schedules_tab,
1160 x_return_message => l_msg_data,
1161 x_return_status => l_return_status);
1162
1163 IF (l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS) and
1164 (l_return_status <> WSH_UTIL_CORE.G_RET_STS_WARNING) THEN
1165 FTE_FREIGHT_PRICING_UTIL.print_msg(l_log_level,'search_lane_failed');
1166 FTE_FREIGHT_PRICING_UTIL.print_msg(l_log_level,l_msg_data);
1167 raise FND_API.G_EXC_ERROR;
1168 END IF;
1169
1170 /*
1171 -- for testing
1172 FTE_FREIGHT_PRICING_UTIL.print_msg(l_log_level,'manually set up lane results... ');
1173
1174 l_lanes_tab := fte_lane_tab();
1175
1176 l_lane_rec := fte_lane_rec(
1177 lane_id => 1369,
1178 carrier_id => 15451,
1179 rate_chart_id => null,
1180 mode_of_transport => 'LTL',
1181 origin_id => null,
1182 destination_id => null,
1183 basis => null,
1184 commodity_catg_id => null,
1185 service_code => 'D2D',
1186 comm_fc_class_code => null,
1187 transit_time => 1,
1188 transit_time_uom => null,
1189 lane_number => null,
1190 equipment_code => null,
1191 schedules_flag_code => null,
1192 distance => null,
1193 distance_uom => null,
1194 carrier_name => null,
1195 mode_of_transport_code => null,
1196 commodity => null,
1197 equipment => null,
1198 service => null,
1199 schedules_flag => null,
1200 port_of_loading => null,
1201 port_of_discharge => null,
1202 rate_chart_name => null,
1203 owner_id => null,
1204 special_handling => null,
1205 addl_instr => null,
1206 commodity_flag => null,
1207 equipment_flag => null,
1208 service_flag => null,
1209 rate_chart_view_flag => null,
1210 effective_date => null,
1211 expiry_date => null,
1212 origin_region_type => null,
1213 dest_region_type => null
1214 );
1215
1216
1217 --IF p_source_header_rec.consolidation_id = 1 then
1218 --l_lane_rec.lane_id := 111;
1219 --end if;
1220
1221 l_lanes_tab.EXTEND;
1222 i := 1;
1223 l_lanes_tab(i) := l_lane_rec;
1224
1225 l_lane_rec := fte_lane_rec(
1226 lane_id => 1378,
1227 carrier_id => 15453,
1228 rate_chart_id => null,
1229 mode_of_transport => 'LTL',
1230 origin_id => null,
1231 destination_id => null,
1232 basis => null,
1233 commodity_catg_id => null,
1234 service_code => 'D2D',
1235 comm_fc_class_code => null,
1236 transit_time => 1,
1237 transit_time_uom => null,
1238 lane_number => null,
1239 equipment_code => null,
1240 schedules_flag_code => null,
1241 distance => null,
1242 distance_uom => null,
1243 carrier_name => null,
1244 mode_of_transport_code => null,
1245 commodity => null,
1246 equipment => null,
1247 service => null,
1248 schedules_flag => null,
1249 port_of_loading => null,
1250 port_of_discharge => null,
1251 rate_chart_name => null,
1252 owner_id => null,
1253 special_handling => null,
1254 addl_instr => null,
1255 commodity_flag => null,
1256 equipment_flag => null,
1257 service_flag => null,
1258 rate_chart_view_flag => null,
1259 effective_date => null,
1260 expiry_date => null,
1261 origin_region_type => null,
1262 dest_region_type => null
1263 );
1264
1265 --IF p_source_header_rec.consolidation_id = 1 then
1266 --l_lane_rec.lane_id := 111;
1267 --end if;
1268
1269 l_lanes_tab.EXTEND;
1270 i := 2;
1271 l_lanes_tab(i) := l_lane_rec;
1272
1273 l_lane_rec := fte_lane_rec(
1274 lane_id => 1379,
1275 carrier_id => 15453,
1276 rate_chart_id => null,
1277 mode_of_transport => 'LTL',
1278 origin_id => null,
1279 destination_id => null,
1280 basis => null,
1281 commodity_catg_id => null,
1282 service_code => 'D2D',
1283 comm_fc_class_code => null,
1284 transit_time => 10,
1285 transit_time_uom => null,
1286 lane_number => null,
1287 equipment_code => null,
1288 schedules_flag_code => null,
1289 distance => null,
1290 distance_uom => null,
1291 carrier_name => null,
1292 mode_of_transport_code => null,
1293 commodity => null,
1294 equipment => null,
1295 service => null,
1296 schedules_flag => null,
1297 port_of_loading => null,
1298 port_of_discharge => null,
1299 rate_chart_name => null,
1300 owner_id => null,
1301 special_handling => null,
1302 addl_instr => null,
1303 commodity_flag => null,
1304 equipment_flag => null,
1305 service_flag => null,
1306 rate_chart_view_flag => null,
1307 effective_date => null,
1308 expiry_date => null,
1309 origin_region_type => null,
1310 dest_region_type => null
1311 );
1312
1313 --IF p_source_header_rec.consolidation_id = 1 then
1314 --l_lane_rec.lane_id := 111;
1315 --end if;
1316
1317 l_lanes_tab.EXTEND;
1318 i := 3;
1319 l_lanes_tab(i) := l_lane_rec;
1320 */
1321
1322 FTE_FREIGHT_PRICING_UTIL.print_msg(l_log_level,'populating matched_services from search_lane results... ');
1323 FTE_FREIGHT_PRICING_UTIL.print_msg(l_log_level,'filtering lanes on following fields in source_header_rec:');
1324 FTE_FREIGHT_PRICING_UTIL.print_msg(l_log_level,'scheduled_flag='||p_source_header_rec.scheduled_flag);
1325 FTE_FREIGHT_PRICING_UTIL.print_msg(l_log_level,'delivery_lead_time='||p_source_header_rec.delivery_lead_time);
1326 FTE_FREIGHT_PRICING_UTIL.print_msg(l_log_level,'enforce_lead_time='||p_source_header_rec.enforce_lead_time);
1327 IF (p_source_header_rec.scheduled_flag = 'Y') and
1328 (p_source_header_rec.delivery_lead_time is not null) and
1329 (p_source_header_rec.delivery_lead_time > 0) and
1330 (p_source_header_rec.enforce_lead_time = 'Y') THEN
1331
1332 l_filter_lanes := true;
1333 l_lead_time := p_source_header_rec.delivery_lead_time;
1334 FTE_FREIGHT_PRICING_UTIL.print_msg(l_log_level,'filter lane results based on lead time: '||l_lead_time);
1335
1336 ELSE
1337 l_filter_lanes := false;
1338 FTE_FREIGHT_PRICING_UTIL.print_msg(l_log_level,'not filter lane results');
1339 END IF;
1340
1341 IF l_lanes_tab is not null AND l_lanes_tab.COUNT > 0 THEN
1342 j := 0;
1343 FOR i IN l_lanes_tab.FIRST..l_lanes_tab.LAST LOOP
1344 FTE_FREIGHT_PRICING_UTIL.print_msg(l_log_level,'i='||i);
1345 FTE_FREIGHT_PRICING_UTIL.print_msg(l_log_level,'found lane_id='||l_lanes_tab(i).lane_id);
1346 FTE_FREIGHT_PRICING_UTIL.print_msg(l_log_level,'transit time='||l_lanes_tab(i).transit_time);
1347
1348 IF ( l_lanes_tab(i).mode_of_transport_code = 'TRUCK'
1349 AND p_ignore_TL = 'Y' ) -- FTE J estimate rate
1350 THEN
1351 FTE_FREIGHT_PRICING_UTIL.print_msg(l_log_level,'ignore this TL lane...');
1352 ELSIF l_filter_lanes and (l_lanes_tab(i).transit_time is null) THEN
1353 FTE_FREIGHT_PRICING_UTIL.print_msg(l_log_level,'lane transit time is null, ignore');
1354 ELSIF l_filter_lanes and (l_lead_time < l_lanes_tab(i).transit_time) THEN
1355 FTE_FREIGHT_PRICING_UTIL.print_msg(l_log_level,'lane transit time is longer than lead time, ignore');
1356 ELSE
1357 FTE_FREIGHT_PRICING_UTIL.print_msg(l_log_level,'l_lanes_tab(i).carrier_id '||l_lanes_tab(i).carrier_id);
1358 FTE_FREIGHT_PRICING_UTIL.print_msg(l_log_level,'l_lanes_tab(i).mode_of_transport_code '||l_lanes_tab(i).mode_of_transport_code);
1359 FTE_FREIGHT_PRICING_UTIL.print_msg(l_log_level,'l_lanes_tab(i).service_code '||l_lanes_tab(i).service_code);
1360 FTE_FREIGHT_PRICING_UTIL.print_msg(l_log_level,'p_source_header_rec.ship_from_org_id '||p_source_header_rec.ship_from_org_id);
1361
1362 l_ship_method_code := null;
1363
1364 OPEN c_get_ship_method_code(l_lanes_tab(i).carrier_id,l_lanes_tab(i).mode_of_transport_code,l_lanes_tab(i).service_code, p_source_header_rec.ship_from_org_id);
1365 FETCH c_get_ship_method_code INTO l_ship_method_code;
1366 CLOSE c_get_ship_method_code;
1367
1368 FTE_FREIGHT_PRICING_UTIL.print_msg(l_log_level,'l_ship_method_code='||l_ship_method_code);
1369
1370 IF l_ship_method_code is null and p_filter_shipmethod = 'Y' THEN
1371 FTE_FREIGHT_PRICING_UTIL.print_msg(l_log_level,'ship method is null, ignore');
1372 ELSE
1373 j := j+1;
1374 x_matched_services(j).lane_id := l_lanes_tab(i).lane_id;
1375 x_matched_services(j).carrier_id := l_lanes_tab(i).carrier_id;
1376 x_matched_services(j).pricelist_id := l_lanes_tab(i).rate_chart_id;
1377 x_matched_services(j).mode_of_transportation_code := l_lanes_tab(i).mode_of_transport_code;
1378 x_matched_services(j).origin_id := l_lanes_tab(i).origin_id;
1379 x_matched_services(j).destination_id := l_lanes_tab(i).destination_id;
1380 x_matched_services(j).basis := l_lanes_tab(i).basis;
1381 x_matched_services(j).commodity_catg_id := l_lanes_tab(i).commodity_catg_id;
1382 x_matched_services(j).service_type_code := l_lanes_tab(i).service_code;
1383 x_matched_services(j).classification_code := l_lanes_tab(i).comm_fc_class_code;
1384 x_matched_services(j).transit_time := l_lanes_tab(i).transit_time;
1385 x_matched_services(j).transit_time_uom := l_lanes_tab(i).transit_time_uom;
1386
1387 x_matched_services(j).ship_method_code := l_ship_method_code;
1388
1389 l_ship_method_code := null;
1390
1391 OPEN c_get_carrier_freight_code(l_lanes_tab(i).carrier_id);
1392 FETCH c_get_carrier_freight_code INTO l_ship_method_code;
1393 CLOSE c_get_carrier_freight_code;
1394
1395 x_matched_services(j).carrier_freight_code:= l_ship_method_code;
1396 END IF;
1397 END IF;
1398 END LOOP; -- l_lanes_tab loop
1399
1400 END IF;
1401
1402 FTE_FREIGHT_PRICING_UTIL.unset_method(l_log_level,l_api_name);
1403
1404 EXCEPTION
1405 WHEN FND_API.G_EXC_ERROR THEN
1406 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
1407 FTE_FREIGHT_PRICING_UTIL.set_exception(l_api_name,FTE_FREIGHT_PRICING_UTIL.G_ERR,'FND_API.G_EXC_ERROR');
1408 FTE_FREIGHT_PRICING_UTIL.unset_method(l_log_level,l_api_name);
1409 WHEN others THEN
1410 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
1411 FTE_FREIGHT_PRICING_UTIL.set_exception(l_api_name,FTE_FREIGHT_PRICING_UTIL.G_ERR,'g_others');
1412 FTE_FREIGHT_PRICING_UTIL.print_msg(FTE_FREIGHT_PRICING_UTIL.G_ERR,'Unexpected Error : '||SQLCODE||' : '||SQLERRM);
1413 FTE_FREIGHT_PRICING_UTIL.unset_method(l_log_level,l_api_name);
1414
1415 END Search_Services;
1416
1417
1418 PROCEDURE Append_Rates(
1419 p_source_header_rates_tab IN FTE_PROCESS_REQUESTS.fte_source_header_rates_tab,
1420 p_source_line_rates_tab IN FTE_PROCESS_REQUESTS.fte_source_line_rates_tab,
1421 x_source_header_rates_tab IN OUT NOCOPY FTE_PROCESS_REQUESTS.fte_source_header_rates_tab,
1422 x_source_line_rates_tab IN OUT NOCOPY FTE_PROCESS_REQUESTS.fte_source_line_rates_tab,
1423 x_return_status OUT NOCOPY Varchar2)
1424
1425 IS
1426
1427
1428 i NUMBER;
1429 j NUMBER;
1430 l_first_line_index_offset NUMBER;
1431 l_first_line_index_tab dbms_utility.number_array;
1432 l_log_level NUMBER := FTE_FREIGHT_PRICING_UTIL.G_LOG;
1433 l_api_name CONSTANT VARCHAR2(30) := 'Append_Rates';
1434 l_return_status VARCHAR2(1);
1435
1436
1437 BEGIN
1438
1439
1440 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
1441
1442 FTE_FREIGHT_PRICING_UTIL.reset_dbg_vars;
1443 FTE_FREIGHT_PRICING_UTIL.set_method(l_log_level,l_api_name);
1444
1445
1446 --HEader Rates
1447
1448 j:=x_source_header_rates_tab.LAST;
1449 IF(j IS NULL)
1450 THEN
1451 j:=1;
1452 ELSE
1453 j:=j+1;
1454 END IF;
1455 i:=p_source_header_rates_tab.FIRST;
1456 WHILE(i IS NOT NULL)
1457 LOOP
1458 x_source_header_rates_tab(j):=p_source_header_rates_tab(i);
1459
1460 l_first_line_index_tab(x_source_header_rates_tab(j).first_line_index):=j;
1461
1462 j:=j+1;
1463 i:=p_source_header_rates_tab.NEXT(i);
1464 END LOOP;
1465
1466 --Line Rates
1467
1468 j:=x_source_line_rates_tab.LAST;
1469
1470 IF(j IS NULL)
1471 THEN
1472 j:=1;
1473 ELSE
1474 j:=j+1;
1475 END IF;
1476 i:=p_source_line_rates_tab.FIRST;
1477 WHILE(i IS NOT NULL)
1478 LOOP
1479 x_source_line_rates_tab(j):=p_source_line_rates_tab(i);
1480
1481 IF (l_first_line_index_tab.EXISTS(i))
1482 THEN
1483 IF (x_source_header_rates_tab.EXISTS(l_first_line_index_tab(i)))
1484 THEN
1485 --Adjust first line index
1486 x_source_header_rates_tab(l_first_line_index_tab(i)).first_line_index:=j;
1487
1488 END IF;
1489
1490 END IF;
1491
1492 j:=j+1;
1493 i:=p_source_line_rates_tab.NEXT(i);
1494 END LOOP;
1495
1496
1497
1498 FTE_FREIGHT_PRICING_UTIL.set_method(l_log_level,l_api_name);
1499
1500 EXCEPTION
1501
1502
1503
1504 WHEN others THEN
1505 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
1506 FTE_FREIGHT_PRICING_UTIL.set_exception(l_api_name,FTE_FREIGHT_PRICING_UTIL.G_ERR,'g_others');
1507 FTE_FREIGHT_PRICING_UTIL.print_msg(FTE_FREIGHT_PRICING_UTIL.G_ERR,'Unexpected Error : '||SQLCODE||' : '||SQLERRM);
1508 FTE_FREIGHT_PRICING_UTIL.unset_method(l_log_level,l_api_name);
1509
1510
1511 END Append_Rates;
1512
1513
1514
1515 PROCEDURE Sort_Source_Rates(
1516 P_LCSS_flag IN VARCHAR2,
1517 x_source_header_rates_tab IN OUT NOCOPY FTE_PROCESS_REQUESTS.fte_source_header_rates_tab,
1518 x_source_line_rates_tab IN OUT NOCOPY FTE_PROCESS_REQUESTS.fte_source_line_rates_tab,
1519 x_return_status OUT NOCOPY Varchar2)
1520 IS
1521
1522 l_values_tab FTE_TRIP_RATING_GRP.Sort_Value_Tab_Type;
1523 i NUMBER;
1524 j NUMBER;
1525 l_value_rec FTE_TRIP_RATING_GRP.Sort_Value_Rec_Type;
1526 l_value dbms_utility.number_array;
1527 l_sorted_index dbms_utility.number_array;
1528 l_source_header_rates_tab FTE_PROCESS_REQUESTS.fte_source_header_rates_tab;
1529
1530 l_log_level NUMBER := FTE_FREIGHT_PRICING_UTIL.G_LOG;
1531 l_api_name CONSTANT VARCHAR2(30) := 'Sort_Source_Rates';
1532 l_return_status VARCHAR2(1);
1533
1534
1535 BEGIN
1536
1537
1538 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
1539
1540 FTE_FREIGHT_PRICING_UTIL.reset_dbg_vars;
1541 FTE_FREIGHT_PRICING_UTIL.set_method(l_log_level,l_api_name);
1542
1543
1544 l_values_tab.DELETE;
1545
1546 i:=x_source_header_rates_tab.FIRST;
1547 WHILE(i IS NOT NULL)
1548 LOOP
1549 l_values_tab(i):=l_value_rec;
1550 l_values_tab(i).value(0):=x_source_header_rates_tab(i).price;
1551
1552 i:=x_source_header_rates_tab.NEXT(i);
1553 END LOOP;
1554
1555
1556 FTE_TRIP_RATING_GRP.Sort(
1557 p_values_tab=>l_values_tab,
1558 p_sort_type=>NULL,
1559 x_sorted_index=>l_sorted_index,
1560 x_return_status=>l_return_status);
1561
1562 IF l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS
1563 THEN
1564 IF l_return_status <> WSH_UTIL_CORE.G_RET_STS_WARNING
1565 THEN
1566 raise FTE_FREIGHT_PRICING_UTIL.g_sort_fail;
1567 END IF;
1568 END IF;
1569
1570 i:=l_sorted_index.FIRST;
1571 j:=x_source_header_rates_tab.FIRST;
1572 WHILE(i IS NOT NULL)
1573 LOOP
1574 l_source_header_rates_tab(j):=x_source_header_rates_tab(l_sorted_index(i));
1575 j:=j+1;
1576
1577 i:=l_sorted_index.NEXT(i);
1578 END LOOP;
1579
1580
1581 x_source_header_rates_tab:=l_source_header_rates_tab;
1582
1583 FTE_FREIGHT_PRICING_UTIL.set_method(l_log_level,l_api_name);
1584
1585 EXCEPTION
1586
1587 WHEN FTE_FREIGHT_PRICING_UTIL.g_sort_fail THEN
1588 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
1589 FTE_FREIGHT_PRICING_UTIL.set_exception(l_api_name,FTE_FREIGHT_PRICING_UTIL.G_ERR,'g_sort_fail');
1590 FTE_FREIGHT_PRICING_UTIL.unset_method(l_log_level,l_api_name);
1591
1592
1593 WHEN others THEN
1594 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
1595 FTE_FREIGHT_PRICING_UTIL.set_exception(l_api_name,FTE_FREIGHT_PRICING_UTIL.G_ERR,'g_others');
1596 FTE_FREIGHT_PRICING_UTIL.print_msg(FTE_FREIGHT_PRICING_UTIL.G_ERR,'Unexpected Error : '||SQLCODE||' : '||SQLERRM);
1597 FTE_FREIGHT_PRICING_UTIL.unset_method(l_log_level,l_api_name);
1598
1599
1600 END Sort_Source_Rates;
1601
1602
1603 PROCEDURE OM_TL_Rate(
1604 p_lane_info_tab IN FTE_FREIGHT_RATING_PUB.lane_info_tab_type,
1605 p_source_header_rec IN FTE_PROCESS_REQUESTS.fte_source_header_rec,
1606 p_source_lines_tab IN FTE_PROCESS_REQUESTS.fte_source_line_tab,
1607 p_LCSS_flag IN VARCHAR2,
1608 x_source_header_rates_tab IN OUT NOCOPY FTE_PROCESS_REQUESTS.fte_source_header_rates_tab,
1609 x_source_line_rates_tab IN OUT NOCOPY FTE_PROCESS_REQUESTS.fte_source_line_rates_tab,
1610 x_return_status OUT NOCOPY Varchar2)
1611 IS
1612
1613 i NUMBER;
1614 l_lane_rows dbms_utility.number_array;
1615 l_schedule_rows dbms_utility.number_array;
1616 l_log_level NUMBER := FTE_FREIGHT_PRICING_UTIL.G_LOG;
1617 l_api_name CONSTANT VARCHAR2(30) := 'OM_TL_Rate';
1618 l_return_status VARCHAR2(1);
1619
1620
1621 BEGIN
1622
1623
1624 FTE_FREIGHT_PRICING_UTIL.set_method(l_log_level,l_api_name);
1625
1626 i:=p_lane_info_tab.FIRST;
1627 WHILE(i IS NOT NULL)
1628 LOOP
1629 l_lane_rows(i):=p_lane_info_tab(i).lane_id;
1630 l_schedule_rows(i):=NULL;
1631
1632 i:=p_lane_info_tab.NEXT(i);
1633 END LOOP;
1634
1635 FTE_TL_RATING.TL_OM_RATING(
1636 p_lane_rows=>l_lane_rows,
1637 p_schedule_rows=>l_schedule_rows,
1638 p_lane_info_tab=>p_lane_info_tab,
1639 p_source_header_rec=>p_source_header_rec,
1640 p_source_lines_tab=>p_source_lines_tab,
1641 p_LCSS_flag=>p_LCSS_flag,
1642 x_source_header_rates_tab=>x_source_header_rates_tab,
1643 x_source_line_rates_tab=>x_source_line_rates_tab,
1644 x_return_status=>l_return_status);
1645
1646 IF l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS
1647 THEN
1648 IF l_return_status <> WSH_UTIL_CORE.G_RET_STS_WARNING
1649 THEN
1650 raise FTE_FREIGHT_PRICING_UTIL.g_tl_om_rating_fail;
1651 END IF;
1652 END IF;
1653
1654 FTE_FREIGHT_PRICING_UTIL.unset_method(l_log_level,l_api_name);
1655
1656 EXCEPTION
1657
1658 WHEN FTE_FREIGHT_PRICING_UTIL.g_tl_om_rating_fail THEN
1659 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
1660 FTE_FREIGHT_PRICING_UTIL.set_exception(l_api_name,FTE_FREIGHT_PRICING_UTIL.G_ERR,'g_tl_om_rating_fail');
1661 FTE_FREIGHT_PRICING_UTIL.unset_method(l_log_level,l_api_name);
1662
1663
1664 WHEN others THEN
1665 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
1666 FTE_FREIGHT_PRICING_UTIL.set_exception(l_api_name,FTE_FREIGHT_PRICING_UTIL.G_ERR,'g_others');
1667 FTE_FREIGHT_PRICING_UTIL.print_msg(FTE_FREIGHT_PRICING_UTIL.G_ERR,'Unexpected Error : '||SQLCODE||' : '||SQLERRM);
1668 FTE_FREIGHT_PRICING_UTIL.unset_method(l_log_level,l_api_name);
1669
1670
1671
1672 END OM_TL_Rate;
1673
1674 PROCEDURE Get_Services(
1675 p_source_line_tab IN OUT NOCOPY FTE_PROCESS_REQUESTS.fte_source_line_tab,
1676 p_source_header_tab IN OUT NOCOPY FTE_PROCESS_REQUESTS.fte_source_header_tab,
1677 x_source_line_rates_tab OUT NOCOPY FTE_PROCESS_REQUESTS.fte_source_line_rates_tab,
1678 x_source_header_rates_tab OUT NOCOPY FTE_PROCESS_REQUESTS.fte_source_header_rates_tab,
1679 x_return_status OUT NOCOPY VARCHAR2)
1680 IS
1681 l_log_level NUMBER := FTE_FREIGHT_PRICING_UTIL.G_LOG;
1682 l_api_name CONSTANT VARCHAR2(30) := 'GET_SERVICES';
1683 l_return_status VARCHAR2(1);
1684 l_msg_count NUMBER := 0;
1685 l_msg_data VARCHAR2(240);
1686 l_group_count NUMBER;
1687 l_fail_group_count NUMBER;
1688 l_matched_services lane_info_tab_type;
1689 l_all_lane_failed BOOLEAN;
1690 l_lane_rate NUMBER;
1691 l_lane_rate_uom VARCHAR2(10);
1692 l_lane_fct_price fte_freight_pricing.Freight_Cost_Temp_Tab_Type;
1693 l_lane_fct_charge fte_freight_pricing.Freight_Cost_Temp_Tab_Type;
1694 k NUMBER;
1695 l_lane_info_tab lane_info_tab_type;
1696
1697
1698
1699 BEGIN
1700
1701 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
1702
1703 FTE_FREIGHT_PRICING_UTIL.reset_dbg_vars;
1704 FTE_FREIGHT_PRICING_UTIL.set_method(l_log_level,l_api_name);
1705
1706 FTE_FREIGHT_PRICING_UTIL.print_msg(l_log_level,'looping through groups... ');
1707
1708 l_fail_group_count := 0;
1709 l_group_count := p_source_header_tab.COUNT;
1710
1711 FOR i in p_source_header_tab.FIRST..p_source_header_tab.LAST LOOP
1712
1713 FTE_FREIGHT_PRICING_UTIL.print_msg(l_log_level,'i = '||i);
1714 FTE_FREIGHT_PRICING_UTIL.print_msg(l_log_level,'consolidation_id = '||p_source_header_tab(i).consolidation_id);
1715 p_source_header_tab(i).status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
1716
1717 Search_Services(
1718 p_source_header_rec => p_source_header_tab(i),
1719 p_ignore_TL=>'N',
1720 x_matched_services => l_matched_services,
1721 x_return_status => l_return_status);
1722
1723 IF (l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS) and
1724 (l_return_status <> WSH_UTIL_CORE.G_RET_STS_WARNING) THEN
1725
1726 FTE_FREIGHT_PRICING_UTIL.print_msg(l_log_level,'search_services failed');
1727 p_source_header_tab(i).status := WSH_UTIL_CORE.G_RET_STS_ERROR;
1728 FND_MESSAGE.SET_NAME('FTE', 'FTE_PRC_SEARCH_SERVICES_FAIL');
1729 p_source_header_tab(i).message_data := FND_MESSAGE.GET;
1730 l_fail_group_count := l_fail_group_count + 1;
1731
1732 FND_MESSAGE.SET_NAME('FTE','FTE_PRC_RATE_OMDEL_FL');
1733 FND_MESSAGE.SET_TOKEN('CONSOLIDATION_ID',p_source_header_tab(i).consolidation_id);
1734 FND_MSG_PUB.ADD;
1735
1736 ELSE -- search services successful
1737
1738 IF (l_matched_services.COUNT < 1) THEN
1739
1740 FTE_FREIGHT_PRICING_UTIL.print_msg(l_log_level,'no services found');
1741 -- no services found
1742 p_source_header_tab(i).status := WSH_UTIL_CORE.G_RET_STS_ERROR;
1743 FND_MESSAGE.SET_NAME('FTE', 'FTE_PRC_NO_SERVICES_FOUND');
1744 p_source_header_tab(i).message_data := FND_MESSAGE.GET;
1745 l_fail_group_count := l_fail_group_count + 1;
1746
1747 FND_MESSAGE.SET_NAME('FTE','FTE_PRC_RATE_OMDEL_FL');
1748 FND_MESSAGE.SET_TOKEN('CONSOLIDATION_ID',p_source_header_tab(i).consolidation_id);
1749 FND_MSG_PUB.ADD;
1750
1751 ELSE -- found services
1752
1753 FTE_FREIGHT_PRICING_UTIL.print_msg(l_log_level,'found following services');
1754 print_matched_services(
1755 p_matched_services => l_matched_services,
1756 x_return_status => l_return_status);
1757
1758 IF (l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS) THEN
1759 FTE_FREIGHT_PRICING_UTIL.print_msg(l_log_level,'print_failed');
1760 raise FND_API.G_EXC_ERROR;
1761 END IF;
1762
1763 FTE_FREIGHT_PRICING_UTIL.print_msg(l_log_level,'populating shipment...');
1764 populate_shipment(
1765 p_source_header_rec => p_source_header_tab(i),
1766 p_source_line_tab => p_source_line_tab,
1767 x_return_status => l_return_status);
1768
1769 IF l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS THEN
1770
1771 FTE_FREIGHT_PRICING_UTIL.print_msg(l_log_level,'populate_shipment_failed');
1772 raise FND_API.G_EXC_ERROR;
1773
1774 END IF;
1775
1776 FTE_FREIGHT_PRICING_UTIL.print_msg(l_log_level,'rating on all the lanes...');
1777 l_all_lane_failed := true;
1778
1779 k:=1;
1780 l_lane_info_tab.DELETE;
1781
1782 FOR j in l_matched_services.FIRST..l_matched_services.LAST
1783 LOOP
1784
1785 FTE_FREIGHT_PRICING_UTIL.print_msg(l_log_level,'j = '||j);
1786 FTE_FREIGHT_PRICING_UTIL.print_msg(l_log_level,'rating on lane_id = '||l_matched_services(j).lane_id);
1787
1788
1789 IF((l_matched_services(j).mode_of_transportation_code IS NOT NULL)AND
1790 (l_matched_services(j).mode_of_transportation_code='TRUCK'))
1791 THEN
1792 l_lane_info_tab(k):=l_matched_services(j);
1793
1794
1795 k:=k+1;
1796
1797
1798 ELSE
1799
1800
1801
1802 -- rate the group on one lane
1803 fte_freight_pricing.shipment_rating (
1804 p_lane_id => l_matched_services(j).lane_id,
1805 p_service_type => l_matched_services(j).service_type_code,
1806 p_mode_of_transport => l_matched_services(j).mode_of_transportation_code,
1807 x_summary_lanesched_price => l_lane_rate,
1808 x_summary_lanesched_price_uom => l_lane_rate_uom,
1809 x_freight_cost_temp_price => l_lane_fct_price,
1810 x_freight_cost_temp_charge => l_lane_fct_charge,
1811 x_return_status => l_return_status,
1812 x_msg_count => l_msg_count,
1813 x_msg_data => l_msg_data );
1814
1815 IF l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS and
1816 l_return_status <> WSH_UTIL_CORE.G_RET_STS_WARNING THEN
1817 FTE_FREIGHT_PRICING_UTIL.print_msg(l_log_level,'shipment_rating failed');
1818 ELSIF l_lane_rate is null or l_lane_rate_uom is null THEN
1819 FTE_FREIGHT_PRICING_UTIL.print_msg(l_log_level,'l_lane_rate null');
1820 ELSIF l_lane_fct_price is null THEN
1821 FTE_FREIGHT_PRICING_UTIL.print_msg(l_log_level,'l_lane_fct_price null');
1822 ELSE
1823 FTE_FREIGHT_PRICING_UTIL.print_msg(l_log_level,'shipment_rating success');
1824 FTE_FREIGHT_PRICING_UTIL.print_msg(l_log_level,'l_lane_index='||j);
1825 FTE_FREIGHT_PRICING_UTIL.print_msg(l_log_level,'l_lane_rate='||l_lane_rate);
1826 FTE_FREIGHT_PRICING_UTIL.print_msg(l_log_level,'l_lane_rate_uom='||l_lane_rate_uom);
1827
1828 FTE_FREIGHT_PRICING_UTIL.print_msg(l_log_level,'populating line_rate and header_rate...');
1829 populate_rate (
1830 p_source_header_rec => p_source_header_tab(i),
1831 p_service_rec => l_matched_services(j),
1832 p_lane_rate => l_lane_rate,
1833 p_lane_rate_uom => l_lane_rate_uom,
1834 p_fc_temp_price => l_lane_fct_price,
1835 p_fc_temp_charge => l_lane_fct_charge,
1836 x_source_line_rates_tab => x_source_line_rates_tab,
1837 x_source_header_rates_tab => x_source_header_rates_tab,
1838 x_return_status => l_return_status);
1839
1840 IF l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS THEN
1841
1842 FTE_FREIGHT_PRICING_UTIL.print_msg(l_log_level,'populate_rate_failed');
1843 raise FND_API.G_EXC_ERROR;
1844
1845 END IF;
1846
1847 l_all_lane_failed := false;
1848
1849 print_rates_tab(
1850 p_source_line_rates_tab => x_source_line_rates_tab,
1851 p_source_header_rates_tab => x_source_header_rates_tab,
1852 x_return_status => l_return_status
1853 );
1854
1855 IF l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS THEN
1856
1857 FTE_FREIGHT_PRICING_UTIL.print_msg(l_log_level,'print_failed');
1858 raise FND_API.G_EXC_ERROR;
1859
1860 END IF;
1861
1862 END IF;
1863
1864 END IF;
1865
1866 END LOOP; -- l_matched_services loop
1867
1868
1869 OM_TL_Rate(
1870 p_lane_info_tab=>l_lane_info_tab,
1871 p_source_header_rec=>p_source_header_tab(i),
1872 p_source_lines_tab=>p_source_line_tab,
1873 p_LCSS_flag=>'N',
1874 x_source_header_rates_tab=>x_source_header_rates_tab,
1875 x_source_line_rates_tab=>x_source_line_rates_tab,
1876 x_return_status=>l_return_status);
1877
1878 IF l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS
1879 THEN
1880 IF l_return_status <> WSH_UTIL_CORE.G_RET_STS_WARNING
1881 THEN
1882 FTE_FREIGHT_PRICING_UTIL.print_msg(l_log_level,'OM_TL_Rate failed');
1883 raise FND_API.G_EXC_ERROR;
1884 END IF;
1885 END IF;
1886
1887 IF(x_source_header_rates_tab.COUNT > 0)
1888 THEN
1889
1890 l_all_lane_failed:=false;
1891
1892 END IF;
1893
1894
1895 Sort_Source_Rates(
1896 p_LCSS_flag=>'N',
1897 x_source_header_rates_tab=>x_source_header_rates_tab,
1898 x_source_line_rates_tab=>x_source_line_rates_tab,
1899 x_return_status=>l_return_status);
1900 IF l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS
1901 THEN
1902 IF l_return_status <> WSH_UTIL_CORE.G_RET_STS_WARNING
1903 THEN
1904 FTE_FREIGHT_PRICING_UTIL.print_msg(l_log_level,'Sort_Source_Rates failed');
1905 raise FND_API.G_EXC_ERROR;
1906 END IF;
1907 END IF;
1908
1909
1910
1911 IF (l_all_lane_failed) THEN
1912
1913 FTE_FREIGHT_PRICING_UTIL.print_msg(l_log_level,'no rates found');
1914 p_source_header_tab(i).status := WSH_UTIL_CORE.G_RET_STS_ERROR;
1915 FND_MESSAGE.SET_NAME('FTE', 'FTE_PRC_NO_RATES_FOUND');
1916 p_source_header_tab(i).message_data := FND_MESSAGE.GET;
1917 l_fail_group_count := l_fail_group_count + 1;
1918
1919 FND_MESSAGE.SET_NAME('FTE','FTE_PRC_RATE_OMDEL_FL');
1920 FND_MESSAGE.SET_TOKEN('CONSOLIDATION_ID',p_source_header_tab(i).consolidation_id);
1921 FND_MSG_PUB.ADD;
1922
1923 END IF;
1924
1925 END IF; -- found services
1926
1927 END IF; -- search services successful
1928
1929 END LOOP; -- p_source_header_tab loop
1930
1931 FTE_FREIGHT_PRICING_UTIL.print_msg(l_log_level,'l_group_count='||l_group_count);
1932 FTE_FREIGHT_PRICING_UTIL.print_msg(l_log_level,'l_fail_group_count='||l_fail_group_count);
1933
1934 IF (l_fail_group_count = l_group_count) THEN
1935 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
1936 ELSIF (l_fail_group_count = 0) THEN
1937 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
1938 ELSE
1939 x_return_status := WSH_UTIL_CORE.G_RET_STS_WARNING;
1940 FND_MESSAGE.SET_NAME('FTE','FTE_PRC_LOG_FILE_NAME');
1941 FND_MESSAGE.SET_TOKEN('LOGFILE',FTE_FREIGHT_PRICING_UTIL.get_log_file_name());
1942 FND_MSG_PUB.ADD;
1943 FND_MESSAGE.SET_NAME('FTE','FTE_PRC_RATE_MUL_OMDEL_SUC_W');
1944 FND_MSG_PUB.ADD;
1945 END IF;
1946
1947 FTE_FREIGHT_PRICING_UTIL.print_msg(l_log_level,'x_return_status='||x_return_status);
1948 FTE_FREIGHT_PRICING_UTIL.unset_method(l_log_level,l_api_name);
1949
1950 EXCEPTION
1951 WHEN FND_API.G_EXC_ERROR THEN
1952 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
1953 FTE_FREIGHT_PRICING_UTIL.set_exception(l_api_name,FTE_FREIGHT_PRICING_UTIL.G_ERR,'FND_API.G_EXC_ERROR');
1954 FTE_FREIGHT_PRICING_UTIL.unset_method(l_log_level,l_api_name);
1955 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1956 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
1957 FTE_FREIGHT_PRICING_UTIL.print_msg(FTE_FREIGHT_PRICING_UTIL.G_ERR,'Unexpected Error : '||SQLCODE||' : '||SQLERRM);
1958 FTE_FREIGHT_PRICING_UTIL.set_exception(l_api_name,FTE_FREIGHT_PRICING_UTIL.G_ERR,'FND_API.G_EXC_UNEXPECTED_ERROR');
1959 FTE_FREIGHT_PRICING_UTIL.unset_method(l_log_level,l_api_name);
1960 WHEN others THEN
1961 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
1962 FTE_FREIGHT_PRICING_UTIL.set_exception(l_api_name,FTE_FREIGHT_PRICING_UTIL.G_ERR,'g_others');
1963 FTE_FREIGHT_PRICING_UTIL.print_msg(FTE_FREIGHT_PRICING_UTIL.G_ERR,'Unexpected Error : '||SQLCODE||' : '||SQLERRM);
1964 FTE_FREIGHT_PRICING_UTIL.unset_method(l_log_level,l_api_name);
1965 END Get_Services;
1966
1967
1968 -- FTE J rate estimate -- internal API
1969
1970 PROCEDURE Get_FTE_Estimate_Rates(
1971 p_source_line_tab IN OUT NOCOPY FTE_PROCESS_REQUESTS.fte_source_line_tab,
1972 p_source_header_tab IN OUT NOCOPY FTE_PROCESS_REQUESTS.fte_source_header_tab,
1973 x_source_line_rates_tab OUT NOCOPY FTE_PROCESS_REQUESTS.fte_source_line_rates_tab,
1974 x_source_header_rates_tab OUT NOCOPY FTE_PROCESS_REQUESTS.fte_source_header_rates_tab,
1975 x_return_status OUT NOCOPY VARCHAR2)
1976 IS
1977 l_log_level NUMBER := FTE_FREIGHT_PRICING_UTIL.G_LOG;
1978 l_api_name CONSTANT VARCHAR2(30) := 'GET_FTE_ESTIMATE_RATES';
1979 l_return_status VARCHAR2(1);
1980 l_msg_count NUMBER := 0;
1981 l_msg_data VARCHAR2(240);
1982 l_group_count NUMBER;
1983 l_fail_group_count NUMBER;
1984 l_matched_services lane_info_tab_type;
1985 l_all_lane_failed BOOLEAN;
1986 l_lane_rate NUMBER;
1987 l_lane_rate_uom VARCHAR2(10);
1988 l_lane_fct_price fte_freight_pricing.Freight_Cost_Temp_Tab_Type;
1989 l_lane_fct_charge fte_freight_pricing.Freight_Cost_Temp_Tab_Type;
1990 l_lane_rows dbms_utility.number_array;
1991 l_schedule_rows dbms_utility.number_array;
1992 l_vehicle_rows dbms_utility.number_array;
1993 l_ref_rows dbms_utility.number_array;
1994 l_tl_base_rows dbms_utility.number_array;
1995 l_tl_chrg_rows dbms_utility.number_array;
1996 l_tl_curr_rows dbms_utility.name_array;
1997 i NUMBER;
1998 j NUMBER;
1999 k NUMBER;
2000 kk NUMBER;
2001 l_lane_out_rows dbms_utility.number_array;
2002 l_schedule_out_rows dbms_utility.number_array;
2003 l_vehicle_out_rows dbms_utility.number_array;
2004 --Bug 6625274 Added l_origin_id and l_destination_id variables to pass origin and destination to TL_FREIGHT_ESTIMATE
2005 l_origin_id NUMBER;
2006 l_destination_id NUMBER;
2007
2008
2009 BEGIN
2010
2011 -- Each groups (consolidation_id) stands for one mode_of_transport
2012 -- seach services for each group
2013 -- call mode specific rating api
2014 -- process and merge output
2015
2016 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
2017
2018 FTE_FREIGHT_PRICING_UTIL.reset_dbg_vars;
2019 FTE_FREIGHT_PRICING_UTIL.set_method(l_log_level,l_api_name);
2020
2021 FTE_FREIGHT_PRICING_UTIL.print_msg(l_log_level,'looping through groups... ');
2022
2023 l_fail_group_count := 0;
2024 l_group_count := p_source_header_tab.COUNT;
2025
2026 FOR i in p_source_header_tab.FIRST..p_source_header_tab.LAST LOOP
2027
2028 --Bug 6625274 Initialize Origin and Destination id to null each item loop iterates
2029 l_origin_id := null;
2030 l_destination_id := null;
2031
2032 FTE_FREIGHT_PRICING_UTIL.print_msg(l_log_level,'i = '||i);
2033 FTE_FREIGHT_PRICING_UTIL.print_msg(l_log_level,'consolidation_id = '||p_source_header_tab(i).consolidation_id);
2034 p_source_header_tab(i).status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
2035
2036 Search_Services(
2037 p_source_header_rec => p_source_header_tab(i),
2038 p_ignore_TL => 'N',
2039 p_filter_shipmethod => 'N',
2040 x_matched_services => l_matched_services,
2041 x_return_status => l_return_status);
2042
2043 IF (l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS) and
2044 (l_return_status <> WSH_UTIL_CORE.G_RET_STS_WARNING) THEN
2045
2046 FTE_FREIGHT_PRICING_UTIL.print_msg(l_log_level,'search_services failed');
2047 p_source_header_tab(i).status := WSH_UTIL_CORE.G_RET_STS_ERROR;
2048 FND_MESSAGE.SET_NAME('FTE', 'FTE_PRC_SEARCH_SERVICES_FAIL');
2049 p_source_header_tab(i).message_data := FND_MESSAGE.GET;
2050 l_fail_group_count := l_fail_group_count + 1;
2051
2052 FND_MESSAGE.SET_NAME('FTE','FTE_PRC_RATE_OMDEL_FL');
2053 FND_MESSAGE.SET_TOKEN('CONSOLIDATION_ID',p_source_header_tab(i).consolidation_id);
2054 FND_MSG_PUB.ADD;
2055
2056 ELSE -- search services successful
2057
2058 IF (l_matched_services.COUNT < 1) THEN
2059
2060 FTE_FREIGHT_PRICING_UTIL.print_msg(l_log_level,'no services found');
2061 -- no services found
2062 p_source_header_tab(i).status := WSH_UTIL_CORE.G_RET_STS_ERROR;
2063 FND_MESSAGE.SET_NAME('FTE', 'FTE_PRC_NO_SERVICES_FOUND');
2064 p_source_header_tab(i).message_data := FND_MESSAGE.GET;
2065 l_fail_group_count := l_fail_group_count + 1;
2066
2067 FND_MESSAGE.SET_NAME('FTE','FTE_PRC_RATE_OMDEL_FL');
2068 FND_MESSAGE.SET_TOKEN('CONSOLIDATION_ID',p_source_header_tab(i).consolidation_id);
2069 FND_MSG_PUB.ADD;
2070
2071 ELSE -- found services
2072
2073 FTE_FREIGHT_PRICING_UTIL.print_msg(l_log_level,'found following services');
2074 print_matched_services(
2075 p_matched_services => l_matched_services,
2076 x_return_status => l_return_status);
2077
2078 IF (l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS) THEN
2079 FTE_FREIGHT_PRICING_UTIL.print_msg(l_log_level,'print_failed');
2080 raise FND_API.G_EXC_ERROR;
2081 END IF;
2082 FTE_FREIGHT_PRICING_UTIL.print_msg(l_log_level,'>>>1');
2083 FTE_FREIGHT_PRICING_UTIL.print_msg(l_log_level,'p_source_header_tab(i).mode_of_transport='
2084 ||p_source_header_tab(i).mode_of_transport);
2085
2086 IF (p_source_header_tab(i).mode_of_transport = 'TRUCK') THEN
2087 -- Call TL API here
2088 -- add output of tl api to output tables
2089 FTE_FREIGHT_PRICING_UTIL.print_msg(l_log_level,'Doing TRUCK');
2090 FTE_FREIGHT_PRICING_UTIL.print_msg(l_log_level,'l_matched_services.COUNT='||l_matched_services.COUNT);
2091
2092 FOR j in l_matched_services.FIRST..l_matched_services.LAST LOOP
2093 l_lane_rows(j) := l_matched_services(j).lane_id;
2094 l_schedule_rows(j) := null;
2095
2096 --Bug 6625274 Storing origin and destination id for passing it to the procedure TL_FREIGHT_ESTIMATE
2097 IF (l_origin_id is null) THEN
2098 l_origin_id := l_matched_services(j).origin_id;
2099 END IF;
2100
2101 IF (l_destination_id is null) THEN
2102 l_destination_id := l_matched_services(j).destination_id;
2103 END IF;
2104 --End of Fix for Bug 6625274
2105
2106
2107 FTE_FREIGHT_PRICING_UTIL.print_msg(FTE_FREIGHT_PRICING_UTIL.G_DBG,'Calling vehicle API inventory item:'||p_source_header_tab(i).vehicle_item_id);
2108
2109 l_vehicle_rows(j):=FTE_VEHICLE_PKG.GET_VEHICLE_TYPE_ID(
2110 p_inventory_item_id=> p_source_header_tab(i).vehicle_item_id);
2111
2112 FTE_FREIGHT_PRICING_UTIL.print_msg(FTE_FREIGHT_PRICING_UTIL.G_DBG,'Vehicle API returned:'||l_vehicle_rows(j));
2113
2114 IF (l_vehicle_rows(j) = -1)
2115 THEN
2116 l_vehicle_rows(j):=NULL;
2117
2118 END IF;
2119
2120
2121 END LOOP;
2122
2123 FTE_FREIGHT_PRICING_UTIL.print_msg(l_log_level,'>>>2');
2124
2125 FTE_TL_RATING.Get_Vehicles_For_LaneSchedules(
2126 p_trip_id=>NULL,
2127 p_lane_rows=>l_lane_rows,
2128 p_schedule_rows=>l_schedule_rows,
2129 p_vehicle_rows=>l_vehicle_rows,
2130 x_vehicle_rows=>l_vehicle_out_rows,
2131 x_lane_rows=>l_lane_out_rows,
2132 x_schedule_rows=>l_schedule_out_rows,
2133 x_ref_rows=>l_ref_rows,
2134 x_return_status=>l_return_status);
2135
2136 IF l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS
2137 THEN
2138 IF l_return_status = WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR
2139 THEN
2140 raise FTE_FREIGHT_PRICING_UTIL.g_tl_veh_for_lane_sched_fail;
2141 END IF;
2142 END IF;
2143
2144
2145
2146 FTE_TL_RATING.TL_FREIGHT_ESTIMATE(
2147 p_lane_rows => l_lane_out_rows,
2148 p_schedule_rows => l_schedule_out_rows,
2149 p_vehicle_rows => l_vehicle_out_rows,
2150 p_pickup_location_id => p_source_header_tab(i).ship_from_location_id,
2151 p_dropoff_location_id => p_source_header_tab(i).ship_to_location_id,
2152 p_ship_date => p_source_header_tab(i).ship_date,
2153 p_delivery_date => p_source_header_tab(i).arrival_date,
2154 p_weight => p_source_header_tab(i).total_weight,
2155 p_weight_uom => p_source_header_tab(i).weight_uom_code,
2156 p_volume => p_source_header_tab(i).total_volume,
2157 p_volume_uom => p_source_header_tab(i).volume_uom_code,
2158 p_distance => p_source_header_tab(i).distance,
2159 p_distance_uom => p_source_header_tab(i).distance_uom,
2160 x_lane_sched_base_rows => l_tl_base_rows,
2161 x_lane_sched_acc_rows => l_tl_chrg_rows,
2162 x_lane_sched_curr_rows => l_tl_curr_rows ,
2163 x_return_status => l_return_status,
2164 --Bug 6625274 Passing Origin and Destination id to TL_FREIGHT_ESTIMATE procedure
2165 p_origin_id => l_origin_id,
2166 p_destination_id => l_destination_id);
2167
2168 IF l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS AND
2169 l_return_status <> WSH_UTIL_CORE.G_RET_STS_WARNING AND
2170 l_tl_base_rows.COUNT = 0 THEN
2171 FTE_FREIGHT_PRICING_UTIL.print_msg(l_log_level,'tl_freight_estimate failed');
2172 ELSE
2173 FTE_FREIGHT_PRICING_UTIL.print_msg(l_log_level,'tl_freight_estimate success');
2174
2175 k := x_source_line_rates_tab.COUNT;
2176 FTE_FREIGHT_PRICING_UTIL.print_msg(l_log_level,'number of existing source line rates records is '||k);
2177
2178 kk := x_source_header_rates_tab.COUNT;
2179 FTE_FREIGHT_PRICING_UTIL.print_msg(l_log_level,'number of existing source header rates records is '||kk);
2180
2181 --Sujith added 27-Oct-2003
2182 IF (l_tl_base_rows.COUNT > 0)
2183 THEN
2184
2185 FOR j IN l_tl_base_rows.FIRST..l_tl_base_rows.LAST LOOP
2186 IF (l_tl_base_rows(j) IS NOT NULL AND l_tl_chrg_rows(j) IS NOT NULL) THEN
2187
2188 kk := kk + 1;
2189 x_source_header_rates_tab(kk).consolidation_id := p_source_header_tab(i).consolidation_id;
2190 x_source_header_rates_tab(kk).lane_id := l_lane_out_rows(j);
2191 x_source_header_rates_tab(kk).carrier_freight_code := l_matched_services(l_ref_rows(j)).carrier_freight_code;
2192 x_source_header_rates_tab(kk).carrier_id := l_matched_services(l_ref_rows(j)).carrier_id;
2193 x_source_header_rates_tab(kk).service_level := l_matched_services(l_ref_rows(j)).service_type_code;
2194 x_source_header_rates_tab(kk).mode_of_transport := l_matched_services(l_ref_rows(j)).mode_of_transportation_code;
2195 x_source_header_rates_tab(kk).ship_method_code := l_matched_services(l_ref_rows(j)).ship_method_code;
2196 x_source_header_rates_tab(kk).cost_type_id := null;
2197 x_source_header_rates_tab(kk).cost_type := 'SUMMARY';
2198 x_source_header_rates_tab(kk).price := nvl(l_tl_base_rows(j),0) + nvl(l_tl_chrg_rows(j),0);
2199 x_source_header_rates_tab(kk).currency := l_tl_curr_rows(j);
2200 x_source_header_rates_tab(kk).transit_time := l_matched_services(l_ref_rows(j)).transit_time;
2201 x_source_header_rates_tab(kk).transit_time_uom := l_matched_services(l_ref_rows(j)).transit_time_uom;
2202 x_source_header_rates_tab(kk).vehicle_type_id:=l_vehicle_out_rows(j);
2203 x_source_header_rates_tab(kk).first_line_index := k+1;
2204
2205
2206 k := k + 1;
2207 x_source_line_rates_tab(k).cost_type_id := NULL;
2208 x_source_line_rates_tab(k).line_type_code := 'PRICE';
2209 x_source_line_rates_tab(k).cost_type := 'FTEPRICE';
2210 x_source_line_rates_tab(k).cost_sub_type := 'PRICE';
2211
2212 x_source_line_rates_tab(k).priced_quantity := null;
2213 x_source_line_rates_tab(k).priced_uom := null;
2214 x_source_line_rates_tab(k).adjusted_unit_price := null;
2215 x_source_line_rates_tab(k).adjusted_price := nvl(l_tl_base_rows(j),0);
2216 x_source_line_rates_tab(k).currency := l_tl_curr_rows(j);
2217
2218 x_source_line_rates_tab(k).unit_price := null;
2219 x_source_line_rates_tab(k).base_price := nvl(l_tl_base_rows(j),0);
2220
2221 x_source_line_rates_tab(k).consolidation_id := p_source_header_tab(i).consolidation_id;
2222 x_source_line_rates_tab(k).lane_id := l_matched_services(l_ref_rows(j)).lane_id;
2223 x_source_line_rates_tab(k).carrier_id := l_matched_services(l_ref_rows(j)).carrier_id;
2224 x_source_line_rates_tab(k).carrier_freight_code := l_matched_services(l_ref_rows(j)).carrier_freight_code;
2225 x_source_line_rates_tab(k).service_level := l_matched_services(l_ref_rows(j)).service_type_code;
2226 x_source_line_rates_tab(k).mode_of_transport := l_matched_services(l_ref_rows(j)).mode_of_transportation_code;
2227 x_source_line_rates_tab(k).ship_method_code := l_matched_services(l_ref_rows(j)).ship_method_code;
2228 x_source_line_rates_tab(k).vehicle_type_id := l_vehicle_out_rows(j);
2229
2230 k := k + 1;
2231 x_source_line_rates_tab(k).cost_type_id := NULL;
2232 x_source_line_rates_tab(k).line_type_code := 'CHARGE';
2233 x_source_line_rates_tab(k).cost_type := 'FTECHARGE';
2234 x_source_line_rates_tab(k).cost_sub_type := 'CHARGE';
2235
2236 x_source_line_rates_tab(k).priced_quantity := null;
2237 x_source_line_rates_tab(k).priced_uom := null;
2238 x_source_line_rates_tab(k).adjusted_unit_price := null;
2239 x_source_line_rates_tab(k).adjusted_price := nvl(l_tl_chrg_rows(j),0);
2240 x_source_line_rates_tab(k).currency := l_tl_curr_rows(j);
2241
2242 x_source_line_rates_tab(k).unit_price := null;
2243 x_source_line_rates_tab(k).base_price := nvl(l_tl_chrg_rows(j),0);
2244
2245 x_source_line_rates_tab(k).consolidation_id := p_source_header_tab(i).consolidation_id;
2246 x_source_line_rates_tab(k).lane_id := l_matched_services(l_ref_rows(j)).lane_id;
2247 x_source_line_rates_tab(k).carrier_id := l_matched_services(l_ref_rows(j)).carrier_id;
2248 x_source_line_rates_tab(k).carrier_freight_code := l_matched_services(l_ref_rows(j)).carrier_freight_code;
2249 x_source_line_rates_tab(k).service_level := l_matched_services(l_ref_rows(j)).service_type_code;
2250 x_source_line_rates_tab(k).mode_of_transport := l_matched_services(l_ref_rows(j)).mode_of_transportation_code;
2251 x_source_line_rates_tab(k).ship_method_code := l_matched_services(l_ref_rows(j)).ship_method_code;
2252 --Fixed typo was earlier l_vehicle_out_rows(l_ref_rows(j))
2253 x_source_line_rates_tab(k).vehicle_type_id := l_vehicle_out_rows(j);
2254
2255 END IF;
2256 END LOOP;
2257 END IF;
2258 END IF;
2259
2260 ELSE -- 'LTL' / 'PARCEL'
2261
2262 FTE_FREIGHT_PRICING_UTIL.print_msg(l_log_level,'populating shipment...');
2263 populate_shipment(
2264 p_source_header_rec => p_source_header_tab(i),
2265 p_source_line_tab => p_source_line_tab,
2266 x_return_status => l_return_status);
2267
2268 IF l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS THEN
2269
2270 FTE_FREIGHT_PRICING_UTIL.print_msg(l_log_level,'populate_shipment_failed');
2271 raise FND_API.G_EXC_ERROR;
2272
2273 END IF;
2274
2275 FTE_FREIGHT_PRICING_UTIL.print_msg(l_log_level,'rating on all the lanes...');
2276 l_all_lane_failed := true;
2277
2278 FOR j in l_matched_services.FIRST..l_matched_services.LAST LOOP
2279
2280 FTE_FREIGHT_PRICING_UTIL.print_msg(l_log_level,'j = '||j);
2281 FTE_FREIGHT_PRICING_UTIL.print_msg(l_log_level,'rating on lane_id = '||l_matched_services(j).lane_id);
2282 -- rate the group on one lane
2283 fte_freight_pricing.shipment_rating (
2284 p_lane_id => l_matched_services(j).lane_id,
2285 p_service_type => l_matched_services(j).service_type_code,
2286 p_mode_of_transport => l_matched_services(j).mode_of_transportation_code,
2287 x_summary_lanesched_price => l_lane_rate,
2288 x_summary_lanesched_price_uom => l_lane_rate_uom,
2289 x_freight_cost_temp_price => l_lane_fct_price,
2290 x_freight_cost_temp_charge => l_lane_fct_charge,
2291 x_return_status => l_return_status,
2292 x_msg_count => l_msg_count,
2293 x_msg_data => l_msg_data );
2294
2295 IF l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS and
2296 l_return_status <> WSH_UTIL_CORE.G_RET_STS_WARNING THEN
2297 FTE_FREIGHT_PRICING_UTIL.print_msg(l_log_level,'shipment_rating failed');
2298 ELSIF l_lane_rate is null or l_lane_rate_uom is null THEN
2299 FTE_FREIGHT_PRICING_UTIL.print_msg(l_log_level,'l_lane_rate null');
2300 ELSIF l_lane_fct_price is null THEN
2301 FTE_FREIGHT_PRICING_UTIL.print_msg(l_log_level,'l_lane_fct_price null');
2302 ELSE
2303 FTE_FREIGHT_PRICING_UTIL.print_msg(l_log_level,'shipment_rating success');
2304 FTE_FREIGHT_PRICING_UTIL.print_msg(l_log_level,'l_lane_index='||j);
2305 FTE_FREIGHT_PRICING_UTIL.print_msg(l_log_level,'l_lane_rate='||l_lane_rate);
2306 FTE_FREIGHT_PRICING_UTIL.print_msg(l_log_level,'l_lane_rate_uom='||l_lane_rate_uom);
2307
2308 FTE_FREIGHT_PRICING_UTIL.print_msg(l_log_level,'populating line_rate and header_rate...');
2309 populate_rate_2 (
2310 p_source_header_rec => p_source_header_tab(i),
2311 p_service_rec => l_matched_services(j),
2312 p_lane_rate => l_lane_rate,
2313 p_lane_rate_uom => l_lane_rate_uom,
2314 p_fc_temp_price => l_lane_fct_price,
2315 p_fc_temp_charge => l_lane_fct_charge,
2316 x_source_line_rates_tab => x_source_line_rates_tab,
2317 x_source_header_rates_tab => x_source_header_rates_tab,
2318 x_return_status => l_return_status);
2319
2320 IF l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS THEN
2321
2322 FTE_FREIGHT_PRICING_UTIL.print_msg(l_log_level,'populate_rate_failed');
2323 raise FND_API.G_EXC_ERROR;
2324
2325 END IF;
2326
2327 l_all_lane_failed := false;
2328
2329 print_rates_tab(
2330 p_source_line_rates_tab => x_source_line_rates_tab,
2331 p_source_header_rates_tab => x_source_header_rates_tab,
2332 x_return_status => l_return_status
2333 );
2334
2335 IF l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS THEN
2336
2337 FTE_FREIGHT_PRICING_UTIL.print_msg(l_log_level,'print_failed');
2338 raise FND_API.G_EXC_ERROR;
2339
2340 END IF;
2341
2342 END IF;
2343
2344 END LOOP; -- l_matched_services loop
2345
2346 IF (l_all_lane_failed) THEN
2347
2348 FTE_FREIGHT_PRICING_UTIL.print_msg(l_log_level,'no rates found');
2349 p_source_header_tab(i).status := WSH_UTIL_CORE.G_RET_STS_ERROR;
2350 FND_MESSAGE.SET_NAME('FTE', 'FTE_PRC_NO_RATES_FOUND');
2351 p_source_header_tab(i).message_data := FND_MESSAGE.GET;
2352 l_fail_group_count := l_fail_group_count + 1;
2353
2354 FND_MESSAGE.SET_NAME('FTE','FTE_PRC_RATE_OMDEL_FL');
2355 FND_MESSAGE.SET_TOKEN('CONSOLIDATION_ID',p_source_header_tab(i).consolidation_id);
2356 FND_MSG_PUB.ADD;
2357
2358 END IF;
2359
2360 END IF; -- 'TRUCK'
2361
2362 END IF; -- found services
2363
2364 END IF; -- search services successful
2365
2366 END LOOP; -- p_source_header_tab loop
2367
2368 FTE_FREIGHT_PRICING_UTIL.print_msg(l_log_level,'l_group_count='||l_group_count);
2369 FTE_FREIGHT_PRICING_UTIL.print_msg(l_log_level,'l_fail_group_count='||l_fail_group_count);
2370
2371 -- IF (l_fail_group_count = l_group_count) THEN
2372 -- x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
2373 -- ELSIF (l_fail_group_count = 0) THEN
2374 IF (l_fail_group_count = 0) THEN
2375 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
2376 ELSE
2377 x_return_status := WSH_UTIL_CORE.G_RET_STS_WARNING;
2378 FND_MESSAGE.SET_NAME('FTE','FTE_PRC_LOG_FILE_NAME');
2379 FND_MESSAGE.SET_TOKEN('LOGFILE',FTE_FREIGHT_PRICING_UTIL.get_log_file_name());
2380 FND_MSG_PUB.ADD;
2381 FND_MESSAGE.SET_NAME('FTE','FTE_PRC_RATE_MUL_OMDEL_SUC_W');
2382 FND_MSG_PUB.ADD;
2383 END IF;
2384
2385 FTE_FREIGHT_PRICING_UTIL.print_msg(l_log_level,'x_return_status='||x_return_status);
2386 FTE_FREIGHT_PRICING_UTIL.unset_method(l_log_level,l_api_name);
2387
2388 EXCEPTION
2389 WHEN FND_API.G_EXC_ERROR THEN
2390 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
2391 FTE_FREIGHT_PRICING_UTIL.set_exception(l_api_name,FTE_FREIGHT_PRICING_UTIL.G_ERR,'FND_API.G_EXC_ERROR');
2392 FTE_FREIGHT_PRICING_UTIL.unset_method(l_log_level,l_api_name);
2393 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2394 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
2395 FTE_FREIGHT_PRICING_UTIL.print_msg(FTE_FREIGHT_PRICING_UTIL.G_ERR,'Unexpected Error : '||SQLCODE||' : '||SQLERRM);
2396 FTE_FREIGHT_PRICING_UTIL.set_exception(l_api_name,FTE_FREIGHT_PRICING_UTIL.G_ERR,'FND_API.G_EXC_UNEXPECTED_ERROR');
2397 FTE_FREIGHT_PRICING_UTIL.unset_method(l_log_level,l_api_name);
2398 WHEN others THEN
2399 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
2400 FTE_FREIGHT_PRICING_UTIL.set_exception(l_api_name,FTE_FREIGHT_PRICING_UTIL.G_ERR,'g_others');
2401 FTE_FREIGHT_PRICING_UTIL.print_msg(FTE_FREIGHT_PRICING_UTIL.G_ERR,'Unexpected Error : '||SQLCODE||' : '||SQLERRM);
2402 FTE_FREIGHT_PRICING_UTIL.unset_method(l_log_level,l_api_name);
2403 END Get_FTE_Estimate_Rates;
2404
2405
2406
2407 PROCEDURE Get_Freight_Costs(
2408 p_api_version IN NUMBER DEFAULT 1.0,
2409 p_init_msg_list IN VARCHAR2 DEFAULT FND_API.G_FALSE,
2410 p_commit IN VARCHAR2 DEFAULT FND_API.G_FALSE,
2411 p_source_line_tab IN OUT NOCOPY FTE_PROCESS_REQUESTS.fte_source_line_tab,
2412 p_source_header_tab IN OUT NOCOPY FTE_PROCESS_REQUESTS.fte_source_header_tab,
2413 p_source_type IN VARCHAR2,
2414 p_action IN VARCHAR2,
2415 x_source_line_rates_tab OUT NOCOPY FTE_PROCESS_REQUESTS.fte_source_line_rates_tab,
2416 x_source_header_rates_tab OUT NOCOPY FTE_PROCESS_REQUESTS.fte_source_header_rates_tab,
2417 x_request_id OUT NOCOPY NUMBER,
2418 x_return_status OUT NOCOPY VARCHAR2,
2419 x_msg_count OUT NOCOPY NUMBER,
2420 x_msg_data OUT NOCOPY VARCHAR2)
2421 IS
2422
2423 CURSOR c_get_req_id IS
2424 SELECT fte_pricing_comp_request_s.nextval
2425 FROM sys.dual;
2426
2427 l_api_name CONSTANT VARCHAR2(30) := 'GET_FREIGHT_COSTS';
2428 l_api_version CONSTANT NUMBER := 1.0;
2429
2430 l_log_level NUMBER := FTE_FREIGHT_PRICING_UTIL.G_LOG;
2431 l_request_id NUMBER;
2432
2433 l_return_status VARCHAR2(1);
2434 l_msg_count NUMBER := 0;
2435 l_msg_data VARCHAR2(240);
2436
2437 i NUMBER;
2438 l_matched_services lane_info_tab_type;
2439
2440 l_group_count NUMBER;
2441 l_fail_group_count NUMBER;
2442
2443 l_lane_rate NUMBER;
2444 l_lane_rate_uom VARCHAR2(10);
2445 l_lane_fct_price fte_freight_pricing.Freight_Cost_Temp_Tab_Type;
2446 l_lane_fct_charge fte_freight_pricing.Freight_Cost_Temp_Tab_Type;
2447 l_lowest_lane_index NUMBER;
2448 l_lowest_lane_rate NUMBER;
2449 l_lowest_lane_rate_uom VARCHAR2(10);
2450 l_lowest_lane_fct_price fte_freight_pricing.Freight_Cost_Temp_Tab_Type;
2451 l_lowest_lane_fct_charge fte_freight_pricing.Freight_Cost_Temp_Tab_Type;
2452 l_all_lane_failed BOOLEAN;
2453 l_converted_amount NUMBER;
2454 k NUMBER;
2455 l_lane_info_tab lane_info_tab_type;
2456 l_tl_source_line_rates_tab FTE_PROCESS_REQUESTS.fte_source_line_rates_tab;
2457 l_tl_source_header_rates_tab FTE_PROCESS_REQUESTS.fte_source_header_rates_tab;
2458
2459
2460
2461 BEGIN
2462
2463 SAVEPOINT GET_FREIGHT_COSTS;
2464
2465 -- Standard call to check for call compatibility.
2466 IF NOT FND_API.Compatible_API_Call
2467 (
2468 l_api_version,
2469 p_api_version,
2470 l_api_name,
2471 G_PKG_NAME
2472 )
2473 THEN
2474 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2475 END IF;
2476
2477 -- Initialize message list if p_init_msg_list is set to TRUE.
2478 IF FND_API.to_Boolean( p_init_msg_list )
2479 THEN
2480 FND_MSG_PUB.initialize;
2481 END IF;
2482
2483 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
2484
2485 FTE_FREIGHT_PRICING_UTIL.initialize_logging( x_return_status => l_return_status );
2486
2487 IF (l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS) and
2488 (l_return_status <> WSH_UTIL_CORE.G_RET_STS_WARNING) THEN
2489 FTE_FREIGHT_PRICING_UTIL.print_msg(l_log_level,'init_log_failed');
2490 raise FND_API.G_EXC_ERROR;
2491 ELSE
2492 FTE_FREIGHT_PRICING_UTIL.print_msg(l_log_level,'Initialize Logging successful ');
2493 END IF;
2494
2495 FTE_FREIGHT_PRICING_UTIL.reset_dbg_vars;
2496 FTE_FREIGHT_PRICING_UTIL.set_method(l_log_level,l_api_name);
2497
2498 FTE_FREIGHT_PRICING_UTIL.print_msg(l_log_level,'p_api_version='||p_api_version);
2499 FTE_FREIGHT_PRICING_UTIL.print_msg(l_log_level,'p_init_msg_list='||p_init_msg_list);
2500 FTE_FREIGHT_PRICING_UTIL.print_msg(l_log_level,'p_commit='||p_commit);
2501 FTE_FREIGHT_PRICING_UTIL.print_msg(l_log_level,'p_source_type='||p_source_type);
2502 FTE_FREIGHT_PRICING_UTIL.print_msg(l_log_level,'p_action='||p_action);
2503
2504 FND_MSG_PUB.Count_And_Get
2505 (
2506 p_count => x_msg_count,
2507 p_data => x_msg_data,
2508 p_encoded => FND_API.G_FALSE
2509 );
2510 FTE_FREIGHT_PRICING_UTIL.print_msg(l_log_level,'x_msg_count='||x_msg_count);
2511 FTE_FREIGHT_PRICING_UTIL.print_msg(l_log_level,'x_msg_data='||x_msg_data);
2512
2513 IF (p_source_header_tab.COUNT < 1) THEN
2514
2515 FTE_FREIGHT_PRICING_UTIL.print_msg(l_log_level,'no_source_header');
2516 raise FND_API.G_EXC_ERROR;
2517
2518 END IF;
2519
2520 IF (p_source_line_tab.COUNT < 1) THEN
2521
2522 FTE_FREIGHT_PRICING_UTIL.print_msg(l_log_level,'no_source_line');
2523 raise FND_API.G_EXC_ERROR;
2524
2525 END IF;
2526
2527 print_source_line_tab(
2528 p_source_line_tab => p_source_line_tab,
2529 x_return_status => l_return_status);
2530
2531 IF l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS THEN
2532 FTE_FREIGHT_PRICING_UTIL.print_msg(l_log_level,'print_failed');
2533 raise FND_API.G_EXC_ERROR;
2534 END IF;
2535
2536 print_source_header_tab(
2537 p_source_header_tab => p_source_header_tab,
2538 x_return_status => l_return_status);
2539
2540 IF l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS THEN
2541 FTE_FREIGHT_PRICING_UTIL.print_msg(l_log_level,'print_failed');
2542 raise FND_API.G_EXC_ERROR;
2543 END IF;
2544
2545 -- Generate comparison request_id here and populate it into l_request_id
2546 OPEN c_get_req_id;
2547 FETCH c_get_req_id INTO l_request_id;
2548 CLOSE c_get_req_id;
2549 x_request_id := l_request_id;
2550 FTE_FREIGHT_PRICING_UTIL.print_msg(l_log_level,'*** Request Id :'||l_request_id);
2551
2552 FND_MSG_PUB.Count_And_Get
2553 (
2554 p_count => x_msg_count,
2555 p_data => x_msg_data,
2556 p_encoded => FND_API.G_FALSE
2557 );
2558 FTE_FREIGHT_PRICING_UTIL.print_msg(l_log_level,'x_msg_count='||x_msg_count);
2559 FTE_FREIGHT_PRICING_UTIL.print_msg(l_log_level,'x_msg_data='||x_msg_data);
2560
2561 IF (p_action = 'GET_RATE_CHOICE') THEN
2562
2563 FTE_FREIGHT_PRICING_UTIL.print_msg(l_log_level,'OM-DisplayChoices...');
2564
2565 Get_Services(
2566 p_source_line_tab => p_source_line_tab,
2567 p_source_header_tab => p_source_header_tab,
2568 x_source_line_rates_tab => x_source_line_rates_tab,
2569 x_source_header_rates_tab => x_source_header_rates_tab,
2570 x_return_status => l_return_status);
2571
2572 IF (l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS)
2573 and (l_return_status <> WSH_UTIL_CORE.G_RET_STS_WARNING) THEN
2574
2575 FTE_FREIGHT_PRICING_UTIL.print_msg(l_log_level,'get_services_failed');
2576 raise FND_API.G_EXC_ERROR;
2577 ELSE
2578 FND_MSG_PUB.Count_And_Get
2579 (
2580 p_count => x_msg_count,
2581 p_data => x_msg_data,
2582 p_encoded => FND_API.G_FALSE
2583 );
2584 FTE_FREIGHT_PRICING_UTIL.print_msg(l_log_level,'x_msg_count='||x_msg_count);
2585 FTE_FREIGHT_PRICING_UTIL.print_msg(l_log_level,'x_msg_data='||x_msg_data);
2586
2587 -- Standard check of p_commit.
2588 --
2589 IF FND_API.To_Boolean( p_commit )
2590 THEN
2591 COMMIT WORK;
2592 END IF;
2593 x_return_status := l_return_status;
2594 END IF;
2595
2596 ELSIF (p_action = 'GET_ESTIMATE_RATE') THEN
2597
2598 Get_FTE_Estimate_Rates(
2599 p_source_line_tab => p_source_line_tab,
2600 p_source_header_tab => p_source_header_tab,
2601 x_source_line_rates_tab => x_source_line_rates_tab,
2602 x_source_header_rates_tab => x_source_header_rates_tab,
2603 x_return_status => l_return_status);
2604
2605 IF (l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS)
2606 and (l_return_status <> WSH_UTIL_CORE.G_RET_STS_WARNING) THEN
2607 IF (l_return_status = WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR) THEN
2608 raise FND_API.G_EXC_UNEXPECTED_ERROR;
2609 ELSE
2610 FTE_FREIGHT_PRICING_UTIL.print_msg(l_log_level,'Get_FTE_Estimate_Rates failed');
2611 raise FND_API.G_EXC_ERROR;
2612 END IF;
2613 ELSE
2614 -- Standard check of p_commit.
2615 --
2616 IF FND_API.To_Boolean( p_commit )
2617 THEN
2618 COMMIT WORK;
2619 END IF;
2620 x_return_status := l_return_status;
2621 END IF;
2622
2623 ELSE -- p_action <> 'GET_RATE_CHOICE'
2624
2625 FTE_FREIGHT_PRICING_UTIL.print_msg(l_log_level,'OM-LCSS...');
2626
2627 l_fail_group_count := 0;
2628 l_group_count := p_source_header_tab.COUNT;
2629
2630 FOR i in p_source_header_tab.FIRST..p_source_header_tab.LAST LOOP
2631
2632 FTE_FREIGHT_PRICING_UTIL.print_msg(l_log_level,'consolidation_id = '||p_source_header_tab(i).consolidation_id);
2633 p_source_header_tab(i).status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
2634
2635 Search_Services(
2636 p_source_header_rec => p_source_header_tab(i),
2637 p_ignore_TL=>'N',
2638 x_matched_services => l_matched_services,
2639 x_return_status => l_return_status);
2640
2641 IF (l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS) and
2642 (l_return_status <> WSH_UTIL_CORE.G_RET_STS_WARNING) THEN
2643
2644 FTE_FREIGHT_PRICING_UTIL.print_msg(l_log_level,'search_services failed');
2645 p_source_header_tab(i).status := WSH_UTIL_CORE.G_RET_STS_ERROR;
2646 FND_MESSAGE.SET_NAME('FTE', 'FTE_PRC_SEARCH_SERVICES_FAIL');
2647 p_source_header_tab(i).message_data := FND_MESSAGE.GET;
2648 l_fail_group_count := l_fail_group_count + 1;
2649
2650 FND_MESSAGE.SET_NAME('FTE','FTE_PRC_RATE_OMDEL_FL');
2651 FND_MESSAGE.SET_TOKEN('CONSOLIDATION_ID',p_source_header_tab(i).consolidation_id);
2652 FND_MSG_PUB.ADD;
2653
2654 ELSE -- search services successful
2655
2656 IF (l_matched_services.COUNT < 1) THEN
2657
2658 FTE_FREIGHT_PRICING_UTIL.print_msg(l_log_level,'no services found');
2659 -- no services found
2660 p_source_header_tab(i).status := WSH_UTIL_CORE.G_RET_STS_ERROR;
2661 l_fail_group_count := l_fail_group_count + 1;
2662
2663 FND_MESSAGE.SET_NAME('FTE','FTE_PRC_RATE_OMDEL_FL');
2664 FND_MESSAGE.SET_TOKEN('CONSOLIDATION_ID',p_source_header_tab(i).consolidation_id);
2665 FND_MSG_PUB.ADD;
2666
2667 ELSE -- found services
2668
2669 FTE_FREIGHT_PRICING_UTIL.print_msg(l_log_level,'found following services');
2670 print_matched_services(
2671 p_matched_services => l_matched_services,
2672 x_return_status => l_return_status);
2673
2674 IF (l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS) THEN
2675 FTE_FREIGHT_PRICING_UTIL.print_msg(l_log_level,'print_failed');
2676 raise FND_API.G_EXC_ERROR;
2677 END IF;
2678
2679 FTE_FREIGHT_PRICING_UTIL.print_msg(l_log_level,'populating shipment...');
2680 populate_shipment(
2681 p_source_header_rec => p_source_header_tab(i),
2682 p_source_line_tab => p_source_line_tab,
2683 x_return_status => l_return_status);
2684
2685 IF l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS THEN
2686
2687 FTE_FREIGHT_PRICING_UTIL.print_msg(l_log_level,'populate_shipment_failed');
2688 raise FND_API.G_EXC_ERROR;
2689
2690 END IF;
2691
2692 FTE_FREIGHT_PRICING_UTIL.print_msg(l_log_level,'rating on all the lanes...');
2693
2694 k:=1;
2695 l_lane_info_tab.DELETE;
2696 l_all_lane_failed := true;
2697 l_lowest_lane_rate:=NULL;
2698 FOR j in l_matched_services.FIRST..l_matched_services.LAST
2699 LOOP
2700
2701 IF((l_matched_services(j).mode_of_transportation_code IS NOT NULL)AND
2702 (l_matched_services(j).mode_of_transportation_code='TRUCK'))
2703 THEN
2704 l_lane_info_tab(k):=l_matched_services(j);
2705
2706
2707 k:=k+1;
2708
2709
2710 ELSE
2711
2712
2713 FTE_FREIGHT_PRICING_UTIL.print_msg(l_log_level,'rating on lane_id = '||l_matched_services(j).lane_id);
2714 -- rate the group on one lane
2715 fte_freight_pricing.shipment_rating (
2716 p_lane_id => l_matched_services(j).lane_id,
2717 p_service_type => l_matched_services(j).service_type_code,
2718 p_mode_of_transport => l_matched_services(j).mode_of_transportation_code,
2719 x_summary_lanesched_price => l_lane_rate,
2720 x_summary_lanesched_price_uom => l_lane_rate_uom,
2721 x_freight_cost_temp_price => l_lane_fct_price,
2722 x_freight_cost_temp_charge => l_lane_fct_charge,
2723 x_return_status => l_return_status,
2724 x_msg_count => l_msg_count,
2725 x_msg_data => l_msg_data );
2726
2727 IF (l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS) and
2728 (l_return_status <> WSH_UTIL_CORE.G_RET_STS_WARNING) THEN
2729
2730 FTE_FREIGHT_PRICING_UTIL.print_msg(l_log_level,'shipment_rating failed');
2731
2732 ELSE -- rating successful
2733 FTE_FREIGHT_PRICING_UTIL.print_msg(l_log_level,'shipment_rating success');
2734 FTE_FREIGHT_PRICING_UTIL.print_msg(l_log_level,'l_lane_index='||j);
2735 FTE_FREIGHT_PRICING_UTIL.print_msg(l_log_level,'l_lane_rate='||l_lane_rate);
2736 FTE_FREIGHT_PRICING_UTIL.print_msg(l_log_level,'l_lane_rate_uom='||l_lane_rate_uom);
2737
2738 IF (l_all_lane_failed) THEN
2739
2740 l_lowest_lane_index := j;
2741 l_lowest_lane_rate := l_lane_rate;
2742 l_lowest_lane_rate_uom := l_lane_rate_uom;
2743 l_lowest_lane_fct_price := l_lane_fct_price;
2744 l_lowest_lane_fct_charge := l_lane_fct_charge;
2745 l_all_lane_failed := false;
2746
2747 ELSE
2748 --compare with current lowest cost lane;
2749
2750 IF (l_lowest_lane_rate_uom <> l_lane_rate_uom) THEN
2751 convert_amount(
2752 p_from_currency =>l_lane_rate_uom,
2753 p_from_amount =>l_lane_rate,
2754 p_conversion_type =>p_source_header_tab(i).currency_conversion_type,
2755 p_to_currency =>l_lowest_lane_rate_uom,
2756 x_to_amount =>l_converted_amount,
2757 x_return_status => l_return_status);
2758
2759 IF l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS THEN
2760 FTE_FREIGHT_PRICING_UTIL.print_msg(l_log_level,'currency_conversion_failed');
2761 raise FND_API.G_EXC_ERROR;
2762 END IF;
2763
2764 ELSE
2765 l_converted_amount := l_lane_rate;
2766 END IF;
2767 FTE_FREIGHT_PRICING_UTIL.print_msg(l_log_level,'l_converted_amount='||l_converted_amount);
2768
2769 IF (l_lowest_lane_rate > l_converted_amount) THEN
2770 l_lowest_lane_index := j;
2771 l_lowest_lane_rate := l_lane_rate;
2772 l_lowest_lane_rate_uom := l_lane_rate_uom;
2773 l_lowest_lane_fct_price := l_lane_fct_price;
2774 l_lowest_lane_fct_charge := l_lane_fct_charge;
2775 END IF;
2776 END IF;
2777
2778 FTE_FREIGHT_PRICING_UTIL.print_msg(l_log_level,'l_lowest_lane_index='||l_lowest_lane_index);
2779 FTE_FREIGHT_PRICING_UTIL.print_msg(l_log_level,'l_lowest_lane_rate='||l_lowest_lane_rate);
2780 FTE_FREIGHT_PRICING_UTIL.print_msg(l_log_level,'l_lowest_lane_rate_uom='||l_lowest_lane_rate_uom);
2781 END IF; -- rating successful
2782 END IF;
2783
2784 END LOOP; -- l_matched_services loop
2785
2786 l_tl_source_header_rates_tab.delete;
2787 l_tl_source_line_rates_tab.delete;
2788
2789 OM_TL_Rate(
2790 p_lane_info_tab=>l_lane_info_tab,
2791 p_source_header_rec=>p_source_header_tab(i),
2792 p_source_lines_tab=>p_source_line_tab,
2793 p_LCSS_flag=>'Y',
2794 x_source_header_rates_tab=>l_tl_source_header_rates_tab,
2795 x_source_line_rates_tab=>l_tl_source_line_rates_tab,
2796 x_return_status=>l_return_status);
2797
2798 IF l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS
2799 THEN
2800 IF l_return_status <> WSH_UTIL_CORE.G_RET_STS_WARNING
2801 THEN
2802 FTE_FREIGHT_PRICING_UTIL.print_msg(l_log_level,'OM_TL_Rate failed');
2803 raise FND_API.G_EXC_ERROR;
2804 END IF;
2805 END IF;
2806
2807
2808 IF(l_tl_source_header_rates_tab.COUNT > 0)
2809 THEN
2810
2811 l_all_lane_failed:=false;
2812
2813 END IF;
2814
2815
2816
2817 IF (l_all_lane_failed) THEN
2818
2819 FTE_FREIGHT_PRICING_UTIL.print_msg(l_log_level,'no rates found');
2820 p_source_header_tab(i).status := WSH_UTIL_CORE.G_RET_STS_ERROR;
2821 FND_MESSAGE.SET_NAME('FTE', 'FTE_PRC_NO_RATES_FOUND');
2822 p_source_header_tab(i).message_data := FND_MESSAGE.GET;
2823 l_fail_group_count := l_fail_group_count + 1;
2824
2825 FND_MESSAGE.SET_NAME('FTE','FTE_PRC_RATE_OMDEL_FL');
2826 FND_MESSAGE.SET_TOKEN('CONSOLIDATION_ID',p_source_header_tab(i).consolidation_id);
2827 FND_MSG_PUB.ADD;
2828
2829 ELSE -- found rate
2830
2831 -- Pick minimum of TL and non-TL all rates are in target currency
2832
2833 IF ((l_tl_source_header_rates_tab.COUNT=0)
2834 OR((l_lowest_lane_rate IS NOT NULL ) AND (l_lowest_lane_rate < l_tl_source_header_rates_tab(l_tl_source_header_rates_tab.FIRST).price)))
2835 THEN
2836
2837
2838 FTE_FREIGHT_PRICING_UTIL.print_msg(l_log_level,'found Lowest rate:');
2839 FTE_FREIGHT_PRICING_UTIL.print_msg(l_log_level,'l_lowest_lane_index='||l_lowest_lane_index);
2840 FTE_FREIGHT_PRICING_UTIL.print_msg(l_log_level,'l_lowest_lane_rate='||l_lowest_lane_rate);
2841 FTE_FREIGHT_PRICING_UTIL.print_msg(l_log_level,'l_lowest_lane_rate_uom='||l_lowest_lane_rate_uom);
2842
2843 FTE_FREIGHT_PRICING_UTIL.print_msg(l_log_level,'populating line_rate and header_rate...');
2844 populate_rate (
2845 p_source_header_rec => p_source_header_tab(i),
2846 p_service_rec => l_matched_services(l_lowest_lane_index),
2847 p_lane_rate => l_lowest_lane_rate,
2848 p_lane_rate_uom => l_lowest_lane_rate_uom,
2849 p_fc_temp_price => l_lowest_lane_fct_price,
2850 p_fc_temp_charge => l_lowest_lane_fct_charge,
2851 x_source_line_rates_tab => x_source_line_rates_tab,
2852 x_source_header_rates_tab => x_source_header_rates_tab,
2853 x_return_status => l_return_status);
2854
2855 IF l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS THEN
2856
2857 FTE_FREIGHT_PRICING_UTIL.print_msg(l_log_level,'populate_rate_failed');
2858 raise FND_API.G_EXC_ERROR;
2859
2860 END IF;
2861 ELSE
2862
2863 Append_Rates(
2864 p_source_header_rates_tab=>l_tl_source_header_rates_tab,
2865 p_source_line_rates_tab=>l_tl_source_line_rates_tab,
2866 x_source_header_rates_tab=>x_source_header_rates_tab,
2867 x_source_line_rates_tab=>x_source_line_rates_tab,
2868 x_return_status=>l_return_status);
2869
2870 IF l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS THEN
2871
2872 FTE_FREIGHT_PRICING_UTIL.print_msg(l_log_level,'Append_Rates');
2873 raise FND_API.G_EXC_ERROR;
2874
2875 END IF;
2876
2877 END IF;
2878
2879
2880
2881
2882 print_rates_tab(
2883 p_source_line_rates_tab => x_source_line_rates_tab,
2884 p_source_header_rates_tab => x_source_header_rates_tab,
2885 x_return_status => l_return_status);
2886
2887 IF l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS THEN
2888
2889 FTE_FREIGHT_PRICING_UTIL.print_msg(l_log_level,'print_failed');
2890 raise FND_API.G_EXC_ERROR;
2891
2892 END IF;
2893
2894 END IF; -- found rate
2895
2896 END IF; -- found services
2897
2898 END IF; -- search services successful
2899
2900 END LOOP; -- p_source_header_tab loop
2901
2902 FTE_FREIGHT_PRICING_UTIL.print_msg(l_log_level,'l_group_count='||l_group_count);
2903 FTE_FREIGHT_PRICING_UTIL.print_msg(l_log_level,'l_fail_group_count='||l_fail_group_count);
2904
2905 IF (l_fail_group_count = l_group_count) THEN
2906 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
2907 FND_MESSAGE.SET_NAME('FTE','FTE_PRC_LOG_FILE_NAME');
2908 FND_MESSAGE.SET_TOKEN('LOGFILE',FTE_FREIGHT_PRICING_UTIL.get_log_file_name());
2909 FND_MSG_PUB.ADD;
2910 FND_MESSAGE.SET_NAME('FTE','FTE_PRC_RATE_MUL_OMDEL_FL');
2911 FND_MSG_PUB.ADD;
2912 ELSIF (l_fail_group_count = 0) THEN
2913 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
2914 -- Standard check of p_commit.
2915 --
2916 IF FND_API.To_Boolean( p_commit )
2917 THEN
2918 COMMIT WORK;
2919 END IF;
2920 ELSE
2921 x_return_status := WSH_UTIL_CORE.G_RET_STS_WARNING;
2922 FND_MESSAGE.SET_NAME('FTE','FTE_PRC_LOG_FILE_NAME');
2923 FND_MESSAGE.SET_TOKEN('LOGFILE',FTE_FREIGHT_PRICING_UTIL.get_log_file_name());
2924 FND_MSG_PUB.ADD;
2925 FND_MESSAGE.SET_NAME('FTE','FTE_PRC_RATE_MUL_OMDEL_SUC_W');
2926 FND_MSG_PUB.ADD;
2927 -- Standard check of p_commit.
2928 --
2929 IF FND_API.To_Boolean( p_commit )
2930 THEN
2931 COMMIT WORK;
2932 END IF;
2933 END IF;
2934
2935 END IF; -- p_action <> 'GET_RATE_CHOICE'
2936
2937 FTE_FREIGHT_PRICING_UTIL.print_msg(l_log_level,'here is what we return to OM...');
2938 FTE_FREIGHT_PRICING_UTIL.print_msg(l_log_level,'x_return_status='||x_return_status);
2939
2940 print_rates_tab(
2941 p_source_line_rates_tab => x_source_line_rates_tab,
2942 p_source_header_rates_tab => x_source_header_rates_tab,
2943 x_return_status => l_return_status
2944 );
2945
2946 IF l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS THEN
2947
2948 FTE_FREIGHT_PRICING_UTIL.print_msg(l_log_level,'print_failed');
2949 raise FND_API.G_EXC_ERROR;
2950
2951 END IF;
2952
2953 print_source_header_tab(
2954 p_source_header_tab => p_source_header_tab,
2955 x_return_status => l_return_status);
2956
2957 IF l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS THEN
2958 FTE_FREIGHT_PRICING_UTIL.print_msg(l_log_level,'print_failed');
2959 raise FND_API.G_EXC_ERROR;
2960 END IF;
2961
2962 --
2963 --
2964 -- Standard call to get message count and if count is 1,get message info.
2965 --
2966 FND_MSG_PUB.Count_And_Get
2967 (
2968 p_count => x_msg_count,
2969 p_data => x_msg_data,
2970 p_encoded => FND_API.G_FALSE
2971 );
2972 FTE_FREIGHT_PRICING_UTIL.print_msg(l_log_level,'x_msg_count='||x_msg_count);
2973 FTE_FREIGHT_PRICING_UTIL.print_msg(l_log_level,'x_msg_data='||x_msg_data);
2974
2975 FTE_FREIGHT_PRICING_UTIL.unset_method(l_log_level,l_api_name);
2976 FTE_FREIGHT_PRICING_UTIL.close_logs;
2977
2978 EXCEPTION
2979 WHEN FND_API.G_EXC_ERROR THEN
2980 FND_MESSAGE.SET_NAME('FTE','FTE_PRC_LOG_FILE_NAME');
2981 FND_MESSAGE.SET_TOKEN('LOGFILE',FTE_FREIGHT_PRICING_UTIL.get_log_file_name());
2982 FND_MSG_PUB.ADD;
2983 FND_MESSAGE.SET_NAME('FTE','FTE_PRC_RATE_MUL_OMDEL_FL');
2984 FND_MSG_PUB.ADD;
2985 ROLLBACK TO GET_FREIGHT_COSTS;
2986 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
2987 FND_MSG_PUB.Count_And_Get
2988 (
2989 p_count => x_msg_count,
2990 p_data => x_msg_data,
2991 p_encoded => FND_API.G_FALSE
2992 );
2993 FTE_FREIGHT_PRICING_UTIL.set_exception(l_api_name,FTE_FREIGHT_PRICING_UTIL.G_ERR,'FND_API.G_EXC_ERROR');
2994 FTE_FREIGHT_PRICING_UTIL.unset_method(l_log_level,l_api_name);
2995 FTE_FREIGHT_PRICING_UTIL.close_logs;
2996 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2997 FND_MESSAGE.SET_NAME('FTE','FTE_PRC_LOG_FILE_NAME');
2998 FND_MESSAGE.SET_TOKEN('LOGFILE',FTE_FREIGHT_PRICING_UTIL.get_log_file_name());
2999 FND_MSG_PUB.ADD;
3000 FND_MESSAGE.SET_NAME('FTE','FTE_PRC_RATE_MUL_OMDEL_FL');
3001 FND_MSG_PUB.ADD;
3002 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
3003 FND_MSG_PUB.Count_And_Get
3004 (
3005 p_count => x_msg_count,
3006 p_data => x_msg_data,
3007 p_encoded => FND_API.G_FALSE
3008 );
3009 FTE_FREIGHT_PRICING_UTIL.print_msg(FTE_FREIGHT_PRICING_UTIL.G_ERR,'Unexpected Error : '||SQLCODE||' : '||SQLERRM);
3010 FTE_FREIGHT_PRICING_UTIL.set_exception(l_api_name,FTE_FREIGHT_PRICING_UTIL.G_ERR,'g_others');
3011 FTE_FREIGHT_PRICING_UTIL.unset_method(l_log_level,l_api_name);
3012 FTE_FREIGHT_PRICING_UTIL.close_logs;
3013 ROLLBACK TO GET_FREIGHT_COSTS;
3014 WHEN others THEN
3015 FND_MESSAGE.SET_NAME('FTE','FTE_PRC_LOG_FILE_NAME');
3016 FND_MESSAGE.SET_TOKEN('LOGFILE',FTE_FREIGHT_PRICING_UTIL.get_log_file_name());
3017 FND_MSG_PUB.ADD;
3018 FND_MESSAGE.SET_NAME('FTE','FTE_PRC_RATE_MUL_OMDEL_FL');
3019 FND_MSG_PUB.ADD;
3020 ROLLBACK TO GET_FREIGHT_COSTS;
3021 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
3022 FND_MSG_PUB.Count_And_Get
3023 (
3024 p_count => x_msg_count,
3025 p_data => x_msg_data,
3026 p_encoded => FND_API.G_FALSE
3027 );
3028 FTE_FREIGHT_PRICING_UTIL.print_msg(FTE_FREIGHT_PRICING_UTIL.G_ERR,'Unexpected Error : '||SQLCODE||' : '||SQLERRM);
3029 FTE_FREIGHT_PRICING_UTIL.set_exception(l_api_name,FTE_FREIGHT_PRICING_UTIL.G_ERR,'g_others');
3030 FTE_FREIGHT_PRICING_UTIL.unset_method(l_log_level,l_api_name);
3031 FTE_FREIGHT_PRICING_UTIL.close_logs;
3032 END Get_Freight_Costs;
3033
3034
3035 END FTE_FREIGHT_RATING_PUB;