[Home] [Help]
PACKAGE BODY: APPS.WSH_FC_INTERFACE_PKG
Source
1 PACKAGE BODY WSH_FC_INTERFACE_PKG AS
2 /* $Header: WSHFCIFB.pls 120.4 2006/08/12 00:34:46 wrudge noship $ */
3 -- ----------------------------------------------------------------------------------------
4 -- Package Name: WSH_FC_INTERFACE_PKG
5 --
6 -- Use ':set tabstop=3' in vi command line to see the proper alignment
7 -- Name: Calculate_Association_Level_Cost
8 --
9 -- Goal: Calcuate distributed cost amount from association level
10 -- to delivery detail level according to cost factor
11 -- Parameter: p_Association_Level : Level the freight cost associated with
12 -- p_Association_Entity_Id : Depending on association level, this
13 -- parameter could be delivery_id,
14 -- container_instance_id..
15 --
16 -- ----------------------------------------------------------------------------------------
17
18 g_container_relationship ContainerRelationshipTabType;
19
20 -- -----------------------------------------------------------------------------------------
21 -- This procedure is get cost relevant info of the delivery detail line
22 -- assigned to a entitiy. The parameter list contains entity level which could be
23 -- TRIP, STOP, DELIVERY , DETAIL, CONTAINER. This is procedure will search all the details
24 -- within that level. Also, this procedure will copy the entity id which is above the
25 -- searched level to the output table x_Relevant_Info_Tab.
26 -- For example, p_level= 'DELIVERY', p_delivery_id = 12345, and p_stop_id = 5555.
27 -- This procedure will find all the delivery details assigned to delivery 12345, and copy
28 -- stop id 5555 to each record of the stop_id column.
29 -- private procedure, primarily used by get_XXX_level_breakdown so it get the info from the
30 -- PL/SQL table instead of doing a data fetch.
31 -- ------------------------------------------------------------------------------------------
32 --
33 G_PKG_NAME CONSTANT VARCHAR2(50) := 'WSH_FC_INTERFACE_PKG';
34 --
35 PROCEDURE Get_Relevant_Info(
36 p_level IN VARCHAR2,
37 p_container_id IN NUMBER DEFAULT NULL,
38 p_delivery_id IN NUMBER DEFAULT NULL,
39 p_stop_id IN NUMBER DEFAULT NULL,
40 p_trip_id IN NUMBER DEFAULT NULL,
41 x_Relevant_Info_Tab IN OUT NOCOPY RelavantInfoTabType,
42 x_return_status OUT NOCOPY VARCHAR2 )
43 IS
44
45 CURSOR c_content_details( c_container_id NUMBER) IS
46 SELECT wdd.delivery_detail_id,
47 -- bug 3935583
48 wdd.inventory_item_id,
49 wdd.requested_quantity,
50 wdd.shipped_quantity,
51 wdd.requested_quantity_uom,
52 wdd.net_weight,
53 wdd.weight_uom_code,
54 wdd.volume,
55 wdd.volume_uom_code,
56 wdd.container_flag
57 FROM wsh_delivery_details wdd, wsh_delivery_assignments_v wda
58 WHERE wda.parent_delivery_detail_id = c_container_id and
59 wda.delivery_detail_id = wdd.delivery_detail_id and
60 wdd.oe_interfaced_flag = 'N' and
61 wdd.released_status = 'C' and
62 NVL(wdd.shipped_quantity , 0) > 0 ;
63
64 CURSOR c_delivery_details( c_delivery_id NUMBER) IS
65 SELECT dd.delivery_detail_id,
66 -- bug 3935583
67 dd.inventory_item_id,
68 dd.requested_quantity,
69 dd.shipped_quantity,
70 dd.requested_quantity_uom,
71 dd.net_weight,
72 dd.weight_uom_code,
73 dd.volume,
74 dd.volume_uom_code
75 FROM wsh_delivery_details dd,
76 wsh_delivery_assignments_v da,
77 wsh_new_deliveries nd,
78 oe_order_lines_all ol
79 WHERE dd.delivery_detail_id = da.delivery_detail_id AND
80 da.delivery_id = nd.delivery_id AND
81 da.delivery_id IS NOT NULL AND
82 ol.line_id = dd.source_line_id AND
83 dd.source_code = 'OE' and
84 nd.delivery_id = c_delivery_id AND
85 dd.container_flag = 'N' AND
86 dd.oe_interfaced_flag = 'N' and
87 dd.released_status = 'C' and
88 NVL(dd.shipped_quantity, 0) > 0;
89
90
91 CURSOR c_pickup_deliveries (c_stop_id NUMBER) IS
92 SELECT dg.delivery_id
93 FROM wsh_delivery_legs dg,
94 wsh_new_deliveries dl,
95 wsh_trip_stops st
96 WHERE st.stop_id = dg.pick_up_stop_id AND
97 st.stop_id = c_stop_id AND
98 st.stop_location_id = dl.initial_pickup_location_id AND
99 dg.delivery_id = dl.delivery_id;
100
101 CURSOR c_trip_stops (c_trip_id NUMBER) IS
102 SELECT stop_id
103 FROM wsh_trip_stops
104 WHERE trip_id = c_trip_id
105 AND nvl(shipments_type_flag,'O') IN ('O','M');
106
107 l_detail_info c_delivery_details%ROWTYPE;
108 l_content_detail_info c_content_details%ROWTYPE;
109 l_counter NUMBER := 0;
110 l_delivery_id NUMBER := 0;
111 l_stop_id NUMBER := 0;
112 l_return_status VARCHAR2(1) := WSH_UTIL_CORE.G_RET_STS_SUCCESS ;
113 l_container_id NUMBER := 0;
114 l_parent_container_id NUMBER := 0;
115 l_find_parent VARCHAR2(1) := 'F';
116 i NUMBER := 0;
117
118 WSH_FC_INFO_ERR EXCEPTION;
119
120 --
121 l_debug_on BOOLEAN;
122 --
123 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'GET_RELEVANT_INFO';
124 --
125 BEGIN
126 --
127 -- Debug Statements
128 --
129 --
130 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
131 --
132 IF l_debug_on IS NULL
133 THEN
134 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
135 END IF;
136 --
137 IF l_debug_on THEN
138 WSH_DEBUG_SV.push(l_module_name);
139 --
140 WSH_DEBUG_SV.log(l_module_name,'P_LEVEL',P_LEVEL);
141 WSH_DEBUG_SV.log(l_module_name,'P_CONTAINER_ID',P_CONTAINER_ID);
142 WSH_DEBUG_SV.log(l_module_name,'P_DELIVERY_ID',P_DELIVERY_ID);
143 WSH_DEBUG_SV.log(l_module_name,'P_STOP_ID',P_STOP_ID);
144 WSH_DEBUG_SV.log(l_module_name,'P_TRIP_ID',P_TRIP_ID);
145 END IF;
146 --
147 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
148 --
149 -- Debug Statements
150 --
151 IF l_debug_on THEN
152 WSH_DEBUG_SV.logmsg(l_module_name, 'BEGINNING OF GET_RELEVANT_INFO , LEVEL: ' || P_LEVEL || ' CONTAINER ID: ' || P_CONTAINER_ID || ' DELIVERY ID: ' || P_DELIVERY_ID || ' STOP ID: ' || P_STOP_ID || ' TRIP ID: ' || P_TRIP_ID );
153 END IF;
154 --
155 l_counter := x_Relevant_Info_Tab.count;
156
157 IF p_level = 'CONTAINER' THEN
158 -- --------------------------------------------------------------
159 -- Example: container C1 contains D and container C2;
160 -- container C2 contains C and container C3;
161 -- container C3 contains A and B
162 -- The output in x_Relevant_Info_Tab will be
163 -- delivery_detail_id container_id
164 -- --------------------- ---------------
165 -- D C1
166 -- C C2
167 -- C C1
168 -- A C3
169 -- A C2
170 -- A C1
171 -- B C3
172 -- B C2
173 -- B C1
174 -- Later to calculat the prorated cost for the container C1,
175 -- the code search for the records with container_id = C1, it finds
176 -- D, C, A, B; for cost associated with container C2, it finds
177 -- C, A, B; for cost associated with container C3, it finds
178 -- A and B
179 -- ---------------------------------------------------------------
180
181 OPEN c_content_details(p_container_id);
182 LOOP
183 FETCH c_content_details INTO l_content_detail_info;
184 EXIT WHEN c_content_details%NOTFOUND;
185 IF l_content_detail_info.container_flag ='N' THEN
186 l_counter := l_counter + 1;
187 x_Relevant_Info_Tab(l_counter).delivery_detail_id := l_content_detail_info.delivery_detail_id;
188 x_Relevant_Info_Tab(l_counter).container_id := p_container_id;
189 x_Relevant_Info_Tab(l_counter).delivery_id := p_delivery_id;
190 x_Relevant_Info_Tab(l_counter).stop_id := p_stop_id;
191 x_Relevant_Info_Tab(l_counter).trip_id := p_trip_id;
192 -- bug 3935583
193 x_Relevant_Info_Tab(l_counter).inventory_item_id := l_content_detail_info.inventory_item_id;
194 x_Relevant_Info_Tab(l_counter).requested_quantity := l_content_detail_info.requested_quantity;
195 x_Relevant_Info_Tab(l_counter).shipped_quantity := l_content_detail_info.shipped_quantity;
196 x_Relevant_Info_Tab(l_counter).requested_quantity_uom := l_content_detail_info.requested_quantity_uom;
197 x_Relevant_Info_Tab(l_counter).net_weight := l_content_detail_info.net_weight;
198 x_Relevant_Info_Tab(l_counter).weight_uom_code := l_content_detail_info.weight_uom_code;
199 x_Relevant_Info_Tab(l_counter).volume := l_content_detail_info.volume;
200 x_Relevant_Info_Tab(l_counter).volume_uom_code := l_content_detail_info.volume_uom_code;
201 --
202 -- Debug Statements
203 --
204 IF l_debug_on THEN
205 WSH_DEBUG_SV.logmsg(l_module_name, 'X_RELEVANT_INFO_TAB ( '|| L_COUNTER || ' ) .DELIVERY_DETAIL_ID: ' || X_RELEVANT_INFO_TAB ( L_COUNTER ) .DELIVERY_DETAIL_ID );
206 WSH_DEBUG_SV.logmsg(l_module_name, 'X_RELEVANT_INFO_TAB ( '|| L_COUNTER || ' ) .CONTAINER_ID: ' ||X_RELEVANT_INFO_TAB ( L_COUNTER ) .CONTAINER_ID );
207 WSH_DEBUG_SV.logmsg(l_module_name, 'X_RELEVANT_INFO_TAB ( '|| L_COUNTER || ' ) .DELIVERY_ID: ' ||X_RELEVANT_INFO_TAB ( L_COUNTER ) .DELIVERY_ID );
208 WSH_DEBUG_SV.logmsg(l_module_name, 'X_RELEVANT_INFO_TAB ( '|| L_COUNTER || ' ) .STOP_ID: ' || X_RELEVANT_INFO_TAB ( L_COUNTER ) .STOP_ID );
209 WSH_DEBUG_SV.logmsg(l_module_name, 'X_RELEVANT_INFO_TAB ( '|| L_COUNTER || ' ) .TRIP_ID: ' || X_RELEVANT_INFO_TAB ( L_COUNTER ) .TRIP_ID );
210 -- bug 3935583
211 WSH_DEBUG_SV.logmsg(l_module_name, 'X_RELEVANT_INFO_TAB ( '|| L_COUNTER || ' ) .INVENTORY_ITEM_ID: ' || X_RELEVANT_INFO_TAB ( L_COUNTER ) .INVENTORY_ITEM_ID );
212 WSH_DEBUG_SV.logmsg(l_module_name, 'X_RELEVANT_INFO_TAB ( '|| L_COUNTER || ' ) .REQUESTED_QUANTITY: ' ||X_RELEVANT_INFO_TAB ( L_COUNTER ) .REQUESTED_QUANTITY );
213 WSH_DEBUG_SV.logmsg(l_module_name, 'X_RELEVANT_INFO_TAB ( '|| L_COUNTER || ' ) .SHIPPED_QUANTITY: ' ||X_RELEVANT_INFO_TAB ( L_COUNTER ) .SHIPPED_QUANTITY );
214 WSH_DEBUG_SV.logmsg(l_module_name, 'X_RELEVANT_INFO_TAB ( '|| L_COUNTER || ' ) .REQUESTED_QUANTITY_UOM: ' ||X_RELEVANT_INFO_TAB ( L_COUNTER ) .REQUESTED_QUANTITY_UOM );
215 WSH_DEBUG_SV.logmsg(l_module_name, 'X_RELEVANT_INFO_TAB ( '|| L_COUNTER || ' ) .NET_WEIGHT: ' || X_RELEVANT_INFO_TAB ( L_COUNTER ) .NET_WEIGHT );
216 WSH_DEBUG_SV.logmsg(l_module_name, 'X_RELEVANT_INFO_TAB ( '|| L_COUNTER || ' ) .WEIGHT_UOM_CODE : ' || X_RELEVANT_INFO_TAB ( L_COUNTER ) .WEIGHT_UOM_CODE );
217 WSH_DEBUG_SV.logmsg(l_module_name, 'X_RELEVANT_INFO_TAB ( '|| L_COUNTER || ' ) .VOLUME: ' ||X_RELEVANT_INFO_TAB ( L_COUNTER ) .VOLUME );
218 WSH_DEBUG_SV.logmsg(l_module_name, 'X_RELEVANT_INFO_TAB ( '|| L_COUNTER || ' ) .VOLUME_UOM_CODE : ' ||X_RELEVANT_INFO_TAB ( L_COUNTER ) .VOLUME_UOM_CODE );
219 END IF;
220 --
221
222 -- -------------------------------------------------------------------------------
223 -- recursively checking the container relationship table, add same entry for
224 -- each parent container
225 -- -------------------------------------------------------------------------------
226 l_container_id := p_container_id;
227 LOOP
228 l_find_parent := 'F';
229 FOR i in 1 .. g_container_relationship.count LOOP
230 IF g_container_relationship(i).container_id = l_container_id THEN
231 l_parent_container_id := g_container_relationship(i).parent_container_id;
232 l_find_parent := 'T';
233 EXIT;
234 END IF;
235 END LOOP;
236 IF l_find_parent = 'T' THEN
237 -- copy the record again with container_id := l_parent_container_id;
238 l_counter := l_counter + 1;
239 x_Relevant_Info_Tab(l_counter).delivery_detail_id := l_content_detail_info.delivery_detail_id;
240 x_Relevant_Info_Tab(l_counter).container_id := l_parent_container_id;
241 x_Relevant_Info_Tab(l_counter).delivery_id := p_delivery_id;
242 x_Relevant_Info_Tab(l_counter).stop_id := p_stop_id;
243 x_Relevant_Info_Tab(l_counter).trip_id := p_trip_id;
244 -- bug 3935583
245 x_Relevant_Info_Tab(l_counter).inventory_item_id := l_content_detail_info.inventory_item_id;
246 x_Relevant_Info_Tab(l_counter).requested_quantity := l_content_detail_info.requested_quantity;
247 x_Relevant_Info_Tab(l_counter).shipped_quantity := l_content_detail_info.shipped_quantity;
248 x_Relevant_Info_Tab(l_counter).requested_quantity_uom := l_content_detail_info.requested_quantity_uom;
249 x_Relevant_Info_Tab(l_counter).net_weight := l_content_detail_info.net_weight;
250 x_Relevant_Info_Tab(l_counter).weight_uom_code := l_content_detail_info.weight_uom_code;
251 x_Relevant_Info_Tab(l_counter).volume := l_content_detail_info.volume;
252 x_Relevant_Info_Tab(l_counter).volume_uom_code := l_content_detail_info.volume_uom_code;
253 --
254 -- Debug Statements
255 --
256 IF l_debug_on THEN
257 WSH_DEBUG_SV.logmsg(l_module_name, 'X_RELEVANT_INFO_TAB ( '|| L_COUNTER || ' ) .DELIVERY_DETAIL_ID: ' || X_RELEVANT_INFO_TAB ( L_COUNTER ) .DELIVERY_DETAIL_ID );
258 WSH_DEBUG_SV.logmsg(l_module_name, 'X_RELEVANT_INFO_TAB ( '|| L_COUNTER || ' ) .CONTAINER_ID: ' ||X_RELEVANT_INFO_TAB ( L_COUNTER ) .CONTAINER_ID );
259 WSH_DEBUG_SV.logmsg(l_module_name, 'X_RELEVANT_INFO_TAB ( '|| L_COUNTER || ' ) .DELIVERY_ID: ' ||X_RELEVANT_INFO_TAB ( L_COUNTER ) .DELIVERY_ID );
260 WSH_DEBUG_SV.logmsg(l_module_name, 'X_RELEVANT_INFO_TAB ( '|| L_COUNTER || ' ) .STOP_ID: ' || X_RELEVANT_INFO_TAB ( L_COUNTER ) .STOP_ID );
261 WSH_DEBUG_SV.logmsg(l_module_name, 'X_RELEVANT_INFO_TAB ( '|| L_COUNTER || ' ) .TRIP_ID: ' || X_RELEVANT_INFO_TAB ( L_COUNTER ) .TRIP_ID );
262 -- bug 3935583
263 WSH_DEBUG_SV.logmsg(l_module_name, 'X_RELEVANT_INFO_TAB ( '|| L_COUNTER || ' ) .INVENTORY_ITEM_ID: ' || X_RELEVANT_INFO_TAB ( L_COUNTER ) .INVENTORY_ITEM_ID );
264 WSH_DEBUG_SV.logmsg(l_module_name, 'X_RELEVANT_INFO_TAB ( '|| L_COUNTER || ' ) .REQUESTED_QUANTITY: ' ||X_RELEVANT_INFO_TAB ( L_COUNTER ) .REQUESTED_QUANTITY );
265 WSH_DEBUG_SV.logmsg(l_module_name, 'X_RELEVANT_INFO_TAB ( '|| L_COUNTER || ' ) .SHIPPED_QUANTITY: ' ||X_RELEVANT_INFO_TAB ( L_COUNTER ) .SHIPPED_QUANTITY );
266 WSH_DEBUG_SV.logmsg(l_module_name, 'X_RELEVANT_INFO_TAB ( '|| L_COUNTER || ' ) .REQUESTED_QUANTITY_UOM: ' ||X_RELEVANT_INFO_TAB ( L_COUNTER ) .REQUESTED_QUANTITY_UOM );
267 WSH_DEBUG_SV.logmsg(l_module_name, 'X_RELEVANT_INFO_TAB ( '|| L_COUNTER || ' ) .NET_WEIGHT: ' || X_RELEVANT_INFO_TAB ( L_COUNTER ) .NET_WEIGHT );
268 WSH_DEBUG_SV.logmsg(l_module_name, 'X_RELEVANT_INFO_TAB ( '|| L_COUNTER || ' ) .WEIGHT_UOM_CODE : ' || X_RELEVANT_INFO_TAB ( L_COUNTER ) .WEIGHT_UOM_CODE );
269 WSH_DEBUG_SV.logmsg(l_module_name, 'X_RELEVANT_INFO_TAB ( '|| L_COUNTER || ' ) .VOLUME: ' ||X_RELEVANT_INFO_TAB ( L_COUNTER ) .VOLUME );
270 WSH_DEBUG_SV.logmsg(l_module_name, 'X_RELEVANT_INFO_TAB ( '|| L_COUNTER || ' ) .VOLUME_UOM_CODE : ' ||X_RELEVANT_INFO_TAB ( L_COUNTER ) .VOLUME_UOM_CODE );
271 END IF;
272 --
273 l_container_id := l_parent_container_id;
274 l_parent_container_id := 0;
275 ELSE
276 EXIT;
277 END IF;
278 END LOOP;
279
280 ELSE
281 -- container has sub-container
282 i := g_container_relationship.count + 1;
283 g_container_relationship(i).container_id := l_content_detail_info.delivery_detail_id;
284 g_container_relationship(i).parent_container_id := p_container_id;
285 Get_Relevant_Info(
286 p_level => 'CONTAINER',
287 p_container_id => l_content_detail_info.delivery_detail_id,
288 p_delivery_id => p_delivery_id,
289 p_stop_id => p_stop_id,
290 p_trip_id => p_trip_id,
291 x_Relevant_Info_Tab => x_Relevant_Info_Tab,
292 x_return_status => l_return_status );
293 END IF;
294
295 END LOOP;
296 CLOSE c_content_details;
297
298
299 ELSIF p_level = 'DELIVERY' THEN
300 OPEN c_delivery_details(p_delivery_id);
301 LOOP
302 FETCH c_delivery_details INTO l_detail_info;
303 EXIT WHEN c_delivery_details%NOTFOUND;
304 l_counter := l_counter + 1;
305 x_Relevant_Info_Tab(l_counter).delivery_detail_id := l_detail_info.delivery_detail_id;
306 x_Relevant_Info_Tab(l_counter).delivery_id := p_delivery_id;
307 x_Relevant_Info_Tab(l_counter).stop_id := p_stop_id;
308 x_Relevant_Info_Tab(l_counter).trip_id := p_trip_id;
309 -- bug 3935583
310 x_Relevant_Info_Tab(l_counter).inventory_item_id := l_detail_info.inventory_item_id;
311 x_Relevant_Info_Tab(l_counter).requested_quantity := l_detail_info.requested_quantity;
312 x_Relevant_Info_Tab(l_counter).shipped_quantity := l_detail_info.shipped_quantity;
313 x_Relevant_Info_Tab(l_counter).requested_quantity_uom := l_detail_info.requested_quantity_uom;
314 x_Relevant_Info_Tab(l_counter).net_weight := l_detail_info.net_weight;
315 x_Relevant_Info_Tab(l_counter).weight_uom_code := l_detail_info.weight_uom_code;
316 x_Relevant_Info_Tab(l_counter).volume := l_detail_info.volume;
317 x_Relevant_Info_Tab(l_counter).volume_uom_code := l_detail_info.volume_uom_code;
318 --
319 -- Debug Statements
320 --
321 IF l_debug_on THEN
322 WSH_DEBUG_SV.logmsg(l_module_name, 'X_RELEVANT_INFO_TAB ( '|| L_COUNTER || ' ) .DELIVERY_DETAIL_ID: ' || X_RELEVANT_INFO_TAB ( L_COUNTER ) .DELIVERY_DETAIL_ID );
323 WSH_DEBUG_SV.logmsg(l_module_name, 'X_RELEVANT_INFO_TAB ( '|| L_COUNTER || ' ) .CONTAINER_ID: ' ||X_RELEVANT_INFO_TAB ( L_COUNTER ) .CONTAINER_ID );
324 WSH_DEBUG_SV.logmsg(l_module_name, 'X_RELEVANT_INFO_TAB ( '|| L_COUNTER || ' ) .DELIVERY_ID: ' ||X_RELEVANT_INFO_TAB ( L_COUNTER ) .DELIVERY_ID );
325 WSH_DEBUG_SV.logmsg(l_module_name, 'X_RELEVANT_INFO_TAB ( '|| L_COUNTER || ' ) .STOP_ID: ' || X_RELEVANT_INFO_TAB ( L_COUNTER ) .STOP_ID );
326 -- bug 3935583
327 WSH_DEBUG_SV.logmsg(l_module_name, 'X_RELEVANT_INFO_TAB ( '|| L_COUNTER || ' ) .INVENTORY_ITEM_ID: ' ||X_RELEVANT_INFO_TAB ( L_COUNTER ) .INVENTORY_ITEM_ID);
328 WSH_DEBUG_SV.logmsg(l_module_name, 'X_RELEVANT_INFO_TAB ( '|| L_COUNTER || ' ) .REQUESTED_QUANTITY: ' ||X_RELEVANT_INFO_TAB ( L_COUNTER ) .REQUESTED_QUANTITY );
329 WSH_DEBUG_SV.logmsg(l_module_name, 'X_RELEVANT_INFO_TAB ( '|| L_COUNTER || ' ) .SHIPPED_QUANTITY: ' ||X_RELEVANT_INFO_TAB ( L_COUNTER ) .SHIPPED_QUANTITY );
330 WSH_DEBUG_SV.logmsg(l_module_name, 'X_RELEVANT_INFO_TAB ( '|| L_COUNTER || ' ) .REQUESTED_QUANTITY_UOM: ' ||X_RELEVANT_INFO_TAB ( L_COUNTER ) .REQUESTED_QUANTITY_UOM );
331 WSH_DEBUG_SV.logmsg(l_module_name, 'X_RELEVANT_INFO_TAB ( '|| L_COUNTER || ' ) .NET_WEIGHT: ' || X_RELEVANT_INFO_TAB ( L_COUNTER ) .NET_WEIGHT );
332 WSH_DEBUG_SV.logmsg(l_module_name, 'X_RELEVANT_INFO_TAB ( '|| L_COUNTER || ' ) .WEIGHT_UOM_CODE : ' || X_RELEVANT_INFO_TAB ( L_COUNTER ) .WEIGHT_UOM_CODE );
333 WSH_DEBUG_SV.logmsg(l_module_name, 'X_RELEVANT_INFO_TAB ( '|| L_COUNTER || ' ) .VOLUME: ' ||X_RELEVANT_INFO_TAB ( L_COUNTER ) .VOLUME );
334 WSH_DEBUG_SV.logmsg(l_module_name, 'X_RELEVANT_INFO_TAB ( '|| L_COUNTER || ' ) .VOLUME_UOM_CODE : ' ||X_RELEVANT_INFO_TAB ( L_COUNTER ) .VOLUME_UOM_CODE );
335 END IF;
336 --
337 END LOOP;
338 CLOSE c_delivery_details;
339
340 ELSIF p_level = 'STOP' THEN
341 OPEN c_pickup_deliveries(p_stop_id);
342 LOOP
343 FETCH c_pickup_deliveries INTO l_delivery_id;
344 EXIT WHEN c_pickup_deliveries%NOTFOUND;
345 Get_Relevant_Info(
346 p_level => 'DELIVERY',
347 p_delivery_id => l_delivery_id,
348 p_stop_id => p_stop_id,
349 p_trip_id => p_trip_id,
350 x_Relevant_Info_Tab => x_Relevant_Info_Tab,
351 x_return_status => l_return_status );
352 IF l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS THEN
353 RAISE WSH_FC_INFO_ERR;
354 END IF;
355 END LOOP;
356 CLOSE c_pickup_deliveries;
357
358 ELSIF p_level = 'TRIP' THEN
359 OPEN c_trip_stops(p_trip_id);
360 LOOP
361 FETCH c_trip_stops INTO l_stop_id;
362 EXIT WHEN c_trip_stops%NOTFOUND;
363 Get_Relevant_Info(
364 p_level => 'STOP',
365 p_stop_id => l_stop_id,
366 p_trip_id => p_trip_id,
367 x_Relevant_Info_Tab => x_Relevant_Info_Tab,
368 x_return_status => l_return_status );
369 IF l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS THEN
370 RAISE WSH_FC_INFO_ERR;
371 END IF;
372 END LOOP;
373 CLOSE c_trip_stops;
374 END IF;
375
376 --
377 -- Debug Statements
378 --
379 IF l_debug_on THEN
380 WSH_DEBUG_SV.logmsg(l_module_name, 'END OF GET_RELEVANT_INFO , LEVEL: ' || P_LEVEL || ' CONTAINER ID: ' || P_CONTAINER_ID || ' DELIVERY ID: ' || P_DELIVERY_ID || ' STOP ID: ' || P_STOP_ID || ' TRIP ID: ' || P_TRIP_ID );
381 WSH_DEBUG_SV.pop(l_module_name);
382 END IF;
383 --
384
385 EXCEPTION
386
387 WHEN WSH_FC_INFO_ERR THEN
388 --
389 -- Debug Statements
390 --
391 IF l_debug_on THEN
392 WSH_DEBUG_SV.logmsg(l_module_name, 'GET RELEVANT_INFO FAILED' );
393 END IF;
394 --
395 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
396
397 --
398 -- Debug Statements
399 --
400 IF l_debug_on THEN
401 WSH_DEBUG_SV.logmsg(l_module_name,'WSH_FC_INFO_ERR exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
402 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:WSH_FC_INFO_ERR');
403 END IF;
404 --
405 WHEN others THEN
406 WSH_UTIL_CORE.Default_Handler('WSH_FC_INTERFACE.Get_Relevant_Info');
407 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
408
409 --
410 -- Debug Statements
411 --
412 IF l_debug_on THEN
413 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
414 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
415 END IF;
416 --
417 END Get_Relevant_Info;
418
419
420 -- -----------------------------------------------------------------------------------------
421 -- PROCEDURE NAME: Get_Container_Level_Breakdown
422 -- This function returns the total number of units in UOM specified in the container
423 -- and lists the number of units for each delivery detail in the container
424 -- Calculate_container_level_cost calls this function to get the breakdown, then calculate
425 -- prorated cost propotionally according to the x_Cost_Break_Down table
426 -- ------------------------------------------------------------------------------------------
427 FUNCTION Get_Container_Level_Breakdown(
428 p_Container_id IN NUMBER
429 , p_Cost_Factor IN VARCHAR2 DEFAULT NULL
430 , p_Relevant_Info_Tab IN RelavantInfoTabType
431 , x_Cost_Breakdown IN OUT NOCOPY CostBreakdownTabType
432 , x_return_status IN OUT NOCOPY VARCHAR2
433 ) RETURN NUMBER
434 IS
435
436
437 l_container_quantity NUMBER := 0;
438 l_standard_uom VARCHAR2(3) := NULL;
439 l_quantity_in_st_uom NUMBER := 0;
440 l_counter NUMBER := 0;
441 l_cost_factor VARCHAR2(8) := NULL;
442 i NUMBER := 0;
443 l_weight_as_cost_factor VARCHAR2(1) := 'T';
444 l_volume_as_cost_factor VARCHAR2(1) := 'T';
445 l_shipped_quantity_all_zero VARCHAR2(1) := 'T';
446
447
448 WSH_NULL_UOM EXCEPTION;
449
450 --
451 l_debug_on BOOLEAN;
452 --
453 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'GET_CONTAINER_LEVEL_BREAKDOWN';
454 --
455 BEGIN
456 --
457 -- Debug Statements
458 --
459 --
460 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
461 --
462 IF l_debug_on IS NULL
463 THEN
464 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
465 END IF;
466 --
467 IF l_debug_on THEN
468 WSH_DEBUG_SV.push(l_module_name);
469 --
470 WSH_DEBUG_SV.log(l_module_name,'P_CONTAINER_ID',P_CONTAINER_ID);
471 WSH_DEBUG_SV.log(l_module_name,'P_COST_FACTOR',P_COST_FACTOR);
472 WSH_DEBUG_SV.log(l_module_name,'X_RETURN_STATUS',X_RETURN_STATUS);
473 END IF;
474 --
475 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
476 --
477 -- Debug Statements
478 --
479 IF l_debug_on THEN
480 WSH_DEBUG_SV.logmsg(l_module_name, 'BEGINNING OF GET_CONTAINER_LEVEL_BREAKDOWN: ' || P_CONTAINER_ID );
481 END IF;
482 --
483 -- assume all the relevant info about all delivery details belongs the delivery
484 -- have been populated in the table
485 -- first, loop through the table, check # of delivery_deatils with non zero quantity and
486 -- decide cost factor
487
488 IF p_Cost_Factor is NULL THEN
489 FOR i in 1 .. p_Relevant_Info_Tab.count LOOP
490 IF p_Relevant_Info_Tab(i).container_id = p_container_id THEN
491 IF p_Relevant_Info_Tab(i).shipped_quantity <> 0 THEN
492 l_shipped_quantity_all_zero := 'F';
493 IF p_Relevant_Info_Tab(i).net_weight is NULL OR
494 p_Relevant_Info_Tab(i).net_weight = 0 THEN
495 l_weight_as_cost_factor := 'F';
496 END IF;
497 IF p_Relevant_Info_Tab(i).volume is NULL OR
498 p_Relevant_Info_Tab(i).volume = 0 THEN
499 l_volume_as_cost_factor := 'F';
500 END IF;
501 END IF;
502 END IF;
503 END LOOP;
504 IF l_weight_as_cost_factor = 'T' THEN
505 l_cost_factor := 'WEIGHT';
506 ELSIF l_volume_as_cost_factor = 'T' THEN
507 l_cost_factor := 'VOLUME';
508 ELSE
509 l_cost_factor := 'QUANTITY';
510 END IF;
511 ELSE
512 l_Cost_Factor := p_Cost_Factor;
513 END IF;
514
515 IF l_shipped_quantity_all_zero = 'T' THEN
516 --
517 -- Debug Statements
518 --
519 IF l_debug_on THEN
520 WSH_DEBUG_SV.pop(l_module_name);
521 END IF;
522 --
523 return 0;
524 END IF;
525
526 l_counter := x_Cost_Breakdown.count;
527 --
528 -- Debug Statements
529 --
530 IF l_debug_on THEN
531 WSH_DEBUG_SV.logmsg(l_module_name, 'X_COST_BREAKDOWN HAS ' || L_COUNTER || ' RECORDS' );
532 END IF;
533 --
534
535 IF (l_cost_factor = 'WEIGHT') THEN
536 l_standard_uom := NULL;
537 FOR i in 1 .. p_Relevant_Info_Tab.count LOOP
538 IF p_Relevant_Info_Tab(i).container_id = p_container_id AND
539 p_Relevant_Info_Tab(i).shipped_quantity <> 0 THEN
540 IF l_standard_uom is NULL THEN
541 l_standard_uom := p_Relevant_Info_Tab(i).weight_uom_code;
542 END IF;
543 -- need to convert weight to same uom to do calculation
544 --
545 -- Debug Statements
546 --
547 IF l_debug_on THEN
548 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_WV_UTILS.CONVERT_UOM',WSH_DEBUG_SV.C_PROC_LEVEL);
549 END IF;
550 --
551
552 l_quantity_in_st_uom := WSH_WV_UTILS.convert_uom(
553 from_uom => p_Relevant_Info_Tab(i).weight_uom_code,
554 to_uom => l_standard_uom,
555 quantity => p_Relevant_Info_Tab(i).net_weight,
556 item_id => p_Relevant_Info_Tab(i).inventory_item_id);
557
558 l_counter := l_counter + 1;
559 x_Cost_Breakdown(l_counter).delivery_detail_id := p_Relevant_Info_Tab(i).delivery_detail_id;
560 x_Cost_Breakdown(l_counter).container_id := p_Relevant_Info_Tab(i).container_id;
561 x_Cost_Breakdown(l_counter).delivery_id := p_Relevant_Info_Tab(i).delivery_id;
562 x_Cost_Breakdown(l_counter).stop_id := p_Relevant_Info_Tab(i).stop_id;
563 x_Cost_Breakdown(l_counter).quantity := l_quantity_in_st_uom;
564 x_Cost_Breakdown(l_counter).uom := l_standard_uom;
565 --
566 -- Debug Statements
567 --
568 IF l_debug_on THEN
569 WSH_DEBUG_SV.logmsg(l_module_name, 'X_COST_BREAKDOWN ( ' || L_COUNTER ||' ) .DELIVERY_DETAIL_ID: '|| X_COST_BREAKDOWN ( L_COUNTER ) .DELIVERY_DETAIL_ID );
570 WSH_DEBUG_SV.logmsg(l_module_name, 'X_COST_BREAKDOWN ( ' || L_COUNTER ||' ) .DELIVERY_ID: '|| X_COST_BREAKDOWN ( L_COUNTER ) .DELIVERY_ID );
571 WSH_DEBUG_SV.logmsg(l_module_name, 'X_COST_BREAKDOWN ( ' || L_COUNTER ||' ) .STOP_ID: '|| X_COST_BREAKDOWN ( L_COUNTER ) .STOP_ID );
572 WSH_DEBUG_SV.logmsg(l_module_name, 'X_COST_BREAKDOWN ( ' || L_COUNTER ||' ) .TRIP_ID: '|| X_COST_BREAKDOWN ( L_COUNTER ) .TRIP_ID );
573 WSH_DEBUG_SV.logmsg(l_module_name, 'X_COST_BREAKDOWN ( ' || L_COUNTER ||' ) .QUANTITY: '|| X_COST_BREAKDOWN ( L_COUNTER ) .QUANTITY );
574 WSH_DEBUG_SV.logmsg(l_module_name, 'X_COST_BREAKDOWN ( ' || L_COUNTER ||' ) .UOM: '|| X_COST_BREAKDOWN ( L_COUNTER ) .UOM );
575 END IF;
576 --
577 l_container_quantity := l_container_quantity + l_quantity_in_st_uom;
578
579 END IF;
580 END LOOP;
581 ELSIF (l_cost_factor = 'VOLUME') THEN
582 l_standard_uom := NULL;
583 FOR i in 1 .. p_Relevant_Info_Tab.count LOOP
584 IF p_Relevant_Info_Tab(i).container_id = p_container_id AND
585 p_Relevant_Info_Tab(i).shipped_quantity <> 0 THEN
586 IF l_standard_uom is NULL THEN
587 l_standard_uom := p_Relevant_Info_Tab(i).volume_uom_code;
588 END IF;
589 -- need to convert weight to same uom to do calculation
590 --
591 -- Debug Statements
592 --
593 IF l_debug_on THEN
594 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_WV_UTILS.CONVERT_UOM',WSH_DEBUG_SV.C_PROC_LEVEL);
595 END IF;
596 --
597 l_quantity_in_st_uom := WSH_WV_UTILS.convert_uom(
598 from_uom => p_Relevant_Info_Tab(i).volume_uom_code,
599 to_uom => l_standard_uom,
600 quantity => p_Relevant_Info_Tab(i).volume,
601 item_id => p_Relevant_Info_Tab(i).inventory_item_id);
602
603 l_counter := l_counter + 1;
604 x_Cost_Breakdown(l_counter).delivery_detail_id := p_Relevant_Info_Tab(i).delivery_detail_id;
605 x_Cost_Breakdown(l_counter).container_id := p_Relevant_Info_Tab(i).container_id;
606 x_Cost_Breakdown(l_counter).delivery_id := p_Relevant_Info_Tab(i).delivery_id;
607 x_Cost_Breakdown(l_counter).stop_id := p_Relevant_Info_Tab(i).stop_id;
608 x_Cost_Breakdown(l_counter).quantity := l_quantity_in_st_uom;
609 x_Cost_Breakdown(l_counter).uom := l_standard_uom;
610 --
611 -- Debug Statements
612 --
613 IF l_debug_on THEN
614 WSH_DEBUG_SV.logmsg(l_module_name, 'X_COST_BREAKDOWN ( ' || L_COUNTER ||' ) .DELIVERY_DETAIL_ID: '|| X_COST_BREAKDOWN ( L_COUNTER ) .DELIVERY_DETAIL_ID );
615 WSH_DEBUG_SV.logmsg(l_module_name, 'X_COST_BREAKDOWN ( ' || L_COUNTER ||' ) .DELIVERY_ID: '|| X_COST_BREAKDOWN ( L_COUNTER ) .DELIVERY_ID );
616 WSH_DEBUG_SV.logmsg(l_module_name, 'X_COST_BREAKDOWN ( ' || L_COUNTER ||' ) .STOP_ID: '|| X_COST_BREAKDOWN ( L_COUNTER ) .STOP_ID );
617 WSH_DEBUG_SV.logmsg(l_module_name, 'X_COST_BREAKDOWN ( ' || L_COUNTER ||' ) .TRIP_ID: '|| X_COST_BREAKDOWN ( L_COUNTER ) .TRIP_ID );
618 WSH_DEBUG_SV.logmsg(l_module_name, 'X_COST_BREAKDOWN ( ' || L_COUNTER ||' ) .QUANTITY: '|| X_COST_BREAKDOWN ( L_COUNTER ) .QUANTITY );
619 WSH_DEBUG_SV.logmsg(l_module_name, 'X_COST_BREAKDOWN ( ' || L_COUNTER ||' ) .UOM: '|| X_COST_BREAKDOWN ( L_COUNTER ) .UOM );
620 END IF;
621 --
622 l_container_quantity := l_container_quantity + l_quantity_in_st_uom;
623 END IF;
624 END LOOP;
625 ELSE
626 l_standard_uom := NULL;
627 FOR i in 1 .. p_Relevant_Info_Tab.count LOOP
628 IF p_Relevant_Info_Tab(i).container_id = p_container_id AND
629 p_Relevant_Info_Tab(i).shipped_quantity <> 0 THEN
630 -- shipped quantity
631 IF l_standard_uom is NULL THEN
632 l_standard_uom := p_Relevant_Info_Tab(i).requested_quantity_uom;
633 END IF;
634 -- need to convert weight to same uom to do calculation
635 --
636 -- Debug Statements
637 --
638 IF l_debug_on THEN
639 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_WV_UTILS.CONVERT_UOM',WSH_DEBUG_SV.C_PROC_LEVEL);
640 END IF;
641 -- 3935583, add item_id parameter
642 l_quantity_in_st_uom := WSH_WV_UTILS.convert_uom(
643 from_uom => p_Relevant_Info_Tab(i).requested_quantity_uom,
644 to_uom => l_standard_uom,
645 quantity => p_Relevant_Info_Tab(i).shipped_quantity,
646 item_id => p_Relevant_Info_Tab(i).inventory_item_id);
647
648 l_counter := l_counter + 1;
649 x_Cost_Breakdown(l_counter).delivery_detail_id := p_Relevant_Info_Tab(i).delivery_detail_id;
650 x_Cost_Breakdown(l_counter).container_id := p_Relevant_Info_Tab(i).container_id;
651 x_Cost_Breakdown(l_counter).delivery_id := p_Relevant_Info_Tab(i).delivery_id;
652 x_Cost_Breakdown(l_counter).stop_id := p_Relevant_Info_Tab(i).stop_id;
653 x_Cost_Breakdown(l_counter).quantity := l_quantity_in_st_uom;
654 x_Cost_Breakdown(l_counter).uom := l_standard_uom;
655 --
656 -- Debug Statements
657 --
658 IF l_debug_on THEN
659 WSH_DEBUG_SV.logmsg(l_module_name, 'X_COST_BREAKDOWN ( ' || L_COUNTER ||' ) .DELIVERY_DETAIL_ID: '|| X_COST_BREAKDOWN ( L_COUNTER ) .DELIVERY_DETAIL_ID );
660 WSH_DEBUG_SV.logmsg(l_module_name, 'X_COST_BREAKDOWN ( ' || L_COUNTER ||' ) .DELIVERY_ID: '|| X_COST_BREAKDOWN ( L_COUNTER ) .DELIVERY_ID );
661 WSH_DEBUG_SV.logmsg(l_module_name, 'X_COST_BREAKDOWN ( ' || L_COUNTER ||' ) .STOP_ID: '|| X_COST_BREAKDOWN ( L_COUNTER ) .STOP_ID );
662 WSH_DEBUG_SV.logmsg(l_module_name, 'X_COST_BREAKDOWN ( ' || L_COUNTER ||' ) .TRIP_ID: '|| X_COST_BREAKDOWN ( L_COUNTER ) .TRIP_ID );
663 WSH_DEBUG_SV.logmsg(l_module_name, 'X_COST_BREAKDOWN ( ' || L_COUNTER ||' ) .QUANTITY: '|| X_COST_BREAKDOWN ( L_COUNTER ) .QUANTITY );
664 WSH_DEBUG_SV.logmsg(l_module_name, 'X_COST_BREAKDOWN ( ' || L_COUNTER ||' ) .UOM: '|| X_COST_BREAKDOWN ( L_COUNTER ) .UOM );
665 END IF;
666 --
667 l_container_quantity := l_container_quantity + l_quantity_in_st_uom;
668 END IF;
669 END LOOP;
670 END IF; -- End of if cost factor is quantity
671
672 --
673 -- Debug Statements
674 --
675 IF l_debug_on THEN
676 WSH_DEBUG_SV.logmsg(l_module_name, 'END OF GET_CONTAINER_LEVEL_BREAKDOWN' );
677 WSH_DEBUG_SV.pop(l_module_name);
678 END IF;
679 --
680 return l_container_quantity;
681
682 EXCEPTION
683
684 WHEN WSH_NULL_UOM THEN
685 --
686 -- Debug Statements
687 --
688 IF l_debug_on THEN
689 WSH_DEBUG_SV.logmsg(l_module_name, 'UOM CODE IS NULL' );
690 END IF;
691 --
692 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
693
694 --
695 -- Debug Statements
696 --
697 IF l_debug_on THEN
698 WSH_DEBUG_SV.logmsg(l_module_name,'WSH_NULL_UOM exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
699 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:WSH_NULL_UOM');
700 END IF;
701 --
702 WHEN others THEN
703 WSH_UTIL_CORE.Default_Handler('WSH_FC_INTERFACE.Get_Container_Level_Breakdown');
704 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
705
706 --
707 -- Debug Statements
708 --
709 IF l_debug_on THEN
710 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
711 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
712 END IF;
713 --
714 END Get_Container_Level_Breakdown;
715
716
717
718 -- -----------------------------------------------------------------------------------------
719 -- PROCEDURE NAME: Get_Delivery_Level_Breakdown
720 -- This function returns the total number of units in the UOM specified in the delivery
721 -- and lists the number of units for each delivery detail in the delivery
722 -- Calculate_delivery_level_cost calls this function to get the breakdown, then calculate
723 -- prorated cost propotionally according to the x_Cost_Break_Down table
724 -- ------------------------------------------------------------------------------------------
725 FUNCTION Get_Delivery_Level_Breakdown(
726 p_Delivery_id IN NUMBER
727 , p_Cost_Factor IN VARCHAR2 DEFAULT NULL
728 , p_Relevant_Info_Tab IN RelavantInfoTabType
729 , x_Cost_Breakdown IN OUT NOCOPY CostBreakdownTabType
730 , x_return_status IN OUT NOCOPY VARCHAR2
731 ) RETURN NUMBER
732 IS
733
734 l_delivery_quantity NUMBER := 0;
735 l_standard_uom VARCHAR2(3) := NULL;
736 l_quantity_in_st_uom NUMBER := 0;
737 l_counter NUMBER := 0;
738 l_cost_factor VARCHAR2(8) := NULL;
739 i NUMBER := 0;
740 l_weight_as_cost_factor VARCHAR2(1) := 'T';
741 l_volume_as_cost_factor VARCHAR2(1) := 'T';
742 l_shipped_quantity_all_zero VARCHAR2(1) := 'T';
743
744 WSH_NULL_UOM EXCEPTION;
745
746 --
747 l_debug_on BOOLEAN;
748 --
749 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'GET_DELIVERY_LEVEL_BREAKDOWN';
750 --
751 BEGIN
752 --
753 -- Debug Statements
754 --
755 --
756 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
757 --
758 IF l_debug_on IS NULL
759 THEN
760 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
761 END IF;
762 --
763 IF l_debug_on THEN
764 WSH_DEBUG_SV.push(l_module_name);
765 --
766 WSH_DEBUG_SV.log(l_module_name,'P_DELIVERY_ID',P_DELIVERY_ID);
767 WSH_DEBUG_SV.log(l_module_name,'P_COST_FACTOR',P_COST_FACTOR);
768 WSH_DEBUG_SV.log(l_module_name,'X_RETURN_STATUS',X_RETURN_STATUS);
769 END IF;
770 --
771 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
772 --
773 -- Debug Statements
774 --
775 IF l_debug_on THEN
776 WSH_DEBUG_SV.logmsg(l_module_name, 'BEGINNING OF GET_DELIVERY_LEVEL_BREAKDOWN: ' || P_DELIVERY_ID );
777 END IF;
778 --
779 -- assume all the relevant info about all delivery details belongs the delivery
780 -- have been populated in the table
781 -- first, loop through the table, check # of delivery_deatils with non zero quantity and
782
783 -- decide cost factor
784 IF p_Cost_Factor is NULL THEN
785 FOR i in 1 .. p_Relevant_Info_Tab.count LOOP
786 IF p_Relevant_Info_Tab(i).delivery_id = p_delivery_id THEN
787 IF p_Relevant_Info_Tab(i).shipped_quantity <> 0 THEN
788 l_shipped_quantity_all_zero := 'F';
789 IF p_Relevant_Info_Tab(i).net_weight is NULL OR
790 p_Relevant_Info_Tab(i).net_weight = 0 THEN
791 l_weight_as_cost_factor := 'F';
792 END IF;
793 IF p_Relevant_Info_Tab(i).volume is NULL OR
794 p_Relevant_Info_Tab(i).volume = 0 THEN
795 l_volume_as_cost_factor := 'F';
796 END IF;
797 END IF;
798 END IF;
799 END LOOP;
800 IF l_weight_as_cost_factor = 'T' THEN
801 l_cost_factor := 'WEIGHT';
802 ELSIF l_volume_as_cost_factor = 'T' THEN
803 l_cost_factor := 'VOLUME';
804 ELSE
805 l_cost_factor := 'QUANTITY';
806 END IF;
807 ELSE
808 l_Cost_Factor := p_Cost_Factor;
809 END IF;
810
811 IF l_shipped_quantity_all_zero = 'T' THEN
812 --
813 -- Debug Statements
814 --
815 IF l_debug_on THEN
816 WSH_DEBUG_SV.logmsg(l_module_name, 'SHIPPED_QUANTITY_ALL_ZERO IS TRUE' );
817 WSH_DEBUG_SV.logmsg(l_module_name, 'EXIT OUT OF GET_DELIVERY_LEVEL_BREAKDOWN' );
818 WSH_DEBUG_SV.pop(l_module_name);
819 END IF;
820 return 0;
821 END IF;
822
823 l_counter := x_Cost_Breakdown.count;
824 --
825 -- Debug Statements
826 --
827 IF l_debug_on THEN
828 WSH_DEBUG_SV.logmsg(l_module_name, 'COST FACTOR: ' || L_COST_FACTOR );
829 WSH_DEBUG_SV.logmsg(l_module_name, 'X_COST_BREAKDOWN HAS ' || L_COUNTER || ' RECORDS' );
830 END IF;
831 --
832
833 IF (l_cost_factor = 'WEIGHT') THEN
834 l_standard_uom := NULL;
835 FOR i in 1 .. p_Relevant_Info_Tab.count LOOP
836 IF p_Relevant_Info_Tab(i).delivery_id = p_delivery_id AND
837 p_Relevant_Info_Tab(i).shipped_quantity <> 0 THEN
838 IF l_standard_uom is NULL THEN
839 l_standard_uom := p_Relevant_Info_Tab(i).weight_uom_code;
840 END IF;
841 -- need to convert weight to same uom to do calculation
842 --
843 -- Debug Statements
844 --
845 IF l_debug_on THEN
846 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_WV_UTILS.CONVERT_UOM',WSH_DEBUG_SV.C_PROC_LEVEL);
847 END IF;
848 -- bug 3935583 , passed value to item_id parameter
849 l_quantity_in_st_uom := WSH_WV_UTILS.convert_uom(
850 from_uom => p_Relevant_Info_Tab(i).weight_uom_code,
851 to_uom => l_standard_uom,
852 quantity => p_Relevant_Info_Tab(i).net_weight,
853 item_id => p_Relevant_Info_Tab(i).inventory_item_id);
854
855 l_counter := l_counter + 1;
856 x_Cost_Breakdown(l_counter).delivery_detail_id := p_Relevant_Info_Tab(i).delivery_detail_id;
857 x_Cost_Breakdown(l_counter).delivery_id := p_Relevant_Info_Tab(i).delivery_id;
858 x_Cost_Breakdown(l_counter).stop_id := p_Relevant_Info_Tab(i).stop_id;
859 x_Cost_Breakdown(l_counter).quantity := l_quantity_in_st_uom;
860 x_Cost_Breakdown(l_counter).uom := l_standard_uom;
861 --
862 -- Debug Statements
863 --
864 IF l_debug_on THEN
865 WSH_DEBUG_SV.logmsg(l_module_name, 'X_COST_BREAKDOWN ( ' || L_COUNTER ||' ) .DELIVERY_DETAIL_ID: '|| X_COST_BREAKDOWN ( L_COUNTER ) .DELIVERY_DETAIL_ID );
866 WSH_DEBUG_SV.logmsg(l_module_name, 'X_COST_BREAKDOWN ( ' || L_COUNTER ||' ) .DELIVERY_ID: '|| X_COST_BREAKDOWN ( L_COUNTER ) .DELIVERY_ID );
867 WSH_DEBUG_SV.logmsg(l_module_name, 'X_COST_BREAKDOWN ( ' || L_COUNTER ||' ) .STOP_ID: '|| X_COST_BREAKDOWN ( L_COUNTER ) .STOP_ID );
868 WSH_DEBUG_SV.logmsg(l_module_name, 'X_COST_BREAKDOWN ( ' || L_COUNTER ||' ) .TRIP_ID: '|| X_COST_BREAKDOWN ( L_COUNTER ) .TRIP_ID );
869 WSH_DEBUG_SV.logmsg(l_module_name, 'X_COST_BREAKDOWN ( ' || L_COUNTER ||' ) .QUANTITY: '|| X_COST_BREAKDOWN ( L_COUNTER ) .QUANTITY );
870 WSH_DEBUG_SV.logmsg(l_module_name, 'X_COST_BREAKDOWN ( ' || L_COUNTER ||' ) .UOM: '|| X_COST_BREAKDOWN ( L_COUNTER ) .UOM );
871 END IF;
872 --
873 l_delivery_quantity := l_delivery_quantity + l_quantity_in_st_uom;
874
875 END IF;
876 END LOOP;
877 ELSIF (l_cost_factor = 'VOLUME') THEN
878 l_standard_uom := NULL;
879 FOR i in 1 .. p_Relevant_Info_Tab.count LOOP
880 IF p_Relevant_Info_Tab(i).delivery_id = p_delivery_id AND
881 p_Relevant_Info_Tab(i).shipped_quantity <> 0 THEN
882 IF l_standard_uom is NULL THEN
883 l_standard_uom := p_Relevant_Info_Tab(i).volume_uom_code;
884 END IF;
885 -- need to convert weight to same uom to do calculation
886 --
887 -- Debug Statements
888 --
889 IF l_debug_on THEN
890 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_WV_UTILS.CONVERT_UOM',WSH_DEBUG_SV.C_PROC_LEVEL);
891 END IF;
892 -- bug 3935583 , passed value to item_id parameter
893 l_quantity_in_st_uom := WSH_WV_UTILS.convert_uom(
894 from_uom => p_Relevant_Info_Tab(i).volume_uom_code,
895 to_uom => l_standard_uom,
896 quantity => p_Relevant_Info_Tab(i).volume,
897 item_id => p_Relevant_Info_Tab(i).inventory_item_id);
898 l_counter := l_counter + 1;
899 x_Cost_Breakdown(l_counter).delivery_detail_id := p_Relevant_Info_Tab(i).delivery_detail_id;
900 x_Cost_Breakdown(l_counter).delivery_id := p_Relevant_Info_Tab(i).delivery_id;
901 x_Cost_Breakdown(l_counter).stop_id := p_Relevant_Info_Tab(i).stop_id;
902 x_Cost_Breakdown(l_counter).quantity := l_quantity_in_st_uom;
903 x_Cost_Breakdown(l_counter).uom := l_standard_uom;
904 --
905 -- Debug Statements
906 --
907 IF l_debug_on THEN
908 WSH_DEBUG_SV.logmsg(l_module_name, 'X_COST_BREAKDOWN ( ' || L_COUNTER ||' ) .DELIVERY_DETAIL_ID: '|| X_COST_BREAKDOWN ( L_COUNTER ) .DELIVERY_DETAIL_ID );
909 WSH_DEBUG_SV.logmsg(l_module_name, 'X_COST_BREAKDOWN ( ' || L_COUNTER ||' ) .DELIVERY_ID: '|| X_COST_BREAKDOWN ( L_COUNTER ) .DELIVERY_ID );
910 WSH_DEBUG_SV.logmsg(l_module_name, 'X_COST_BREAKDOWN ( ' || L_COUNTER ||' ) .STOP_ID: '|| X_COST_BREAKDOWN ( L_COUNTER ) .STOP_ID );
911 WSH_DEBUG_SV.logmsg(l_module_name, 'X_COST_BREAKDOWN ( ' || L_COUNTER ||' ) .TRIP_ID: '|| X_COST_BREAKDOWN ( L_COUNTER ) .TRIP_ID );
912 WSH_DEBUG_SV.logmsg(l_module_name, 'X_COST_BREAKDOWN ( ' || L_COUNTER ||' ) .QUANTITY: '|| X_COST_BREAKDOWN ( L_COUNTER ) .QUANTITY );
913 WSH_DEBUG_SV.logmsg(l_module_name, 'X_COST_BREAKDOWN ( ' || L_COUNTER ||' ) .UOM: '|| X_COST_BREAKDOWN ( L_COUNTER ) .UOM );
914 END IF;
915
916 l_delivery_quantity := l_delivery_quantity + l_quantity_in_st_uom;
917 END IF;
918 END LOOP;
919 ELSE
920 l_standard_uom := NULL;
921 FOR i in 1 .. p_Relevant_Info_Tab.count LOOP
922 IF p_Relevant_Info_Tab(i).delivery_id = p_delivery_id AND
923 p_Relevant_Info_Tab(i).shipped_quantity <> 0 THEN
924 -- shipped quantity
925 IF l_standard_uom is NULL THEN
926 l_standard_uom := p_Relevant_Info_Tab(i).requested_quantity_uom;
927 END IF;
928 -- need to convert weight to same uom to do calculation
929 --
930 -- Debug Statements
931 --
932 IF l_debug_on THEN
933 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_WV_UTILS.CONVERT_UOM',WSH_DEBUG_SV.C_PROC_LEVEL);
934 END IF;
935 -- bug 3935583, passed value to item_id
936 l_quantity_in_st_uom := WSH_WV_UTILS.convert_uom(
937 from_uom => p_Relevant_Info_Tab(i).requested_quantity_uom,
938 to_uom => l_standard_uom,
939 quantity => p_Relevant_Info_Tab(i).shipped_quantity,
940 item_id => p_Relevant_Info_Tab(i).inventory_item_id);
941 l_counter := l_counter + 1;
942 x_Cost_Breakdown(l_counter).delivery_detail_id := p_Relevant_Info_Tab(i).delivery_detail_id;
943 x_Cost_Breakdown(l_counter).delivery_id := p_Relevant_Info_Tab(i).delivery_id;
944 x_Cost_Breakdown(l_counter).stop_id := p_Relevant_Info_Tab(i).stop_id;
945 x_Cost_Breakdown(l_counter).quantity := l_quantity_in_st_uom;
946 x_Cost_Breakdown(l_counter).uom := l_standard_uom;
947 --
948 -- Debug Statements
949 --
950 IF l_debug_on THEN
951 WSH_DEBUG_SV.logmsg(l_module_name, 'X_COST_BREAKDOWN ( ' || L_COUNTER ||' ) .DELIVERY_DETAIL_ID: '|| X_COST_BREAKDOWN ( L_COUNTER ) .DELIVERY_DETAIL_ID );
952 WSH_DEBUG_SV.logmsg(l_module_name, 'X_COST_BREAKDOWN ( ' || L_COUNTER ||' ) .DELIVERY_ID: '|| X_COST_BREAKDOWN ( L_COUNTER ) .DELIVERY_ID );
953 WSH_DEBUG_SV.logmsg(l_module_name, 'X_COST_BREAKDOWN ( ' || L_COUNTER ||' ) .STOP_ID: '|| X_COST_BREAKDOWN ( L_COUNTER ) .STOP_ID );
954 WSH_DEBUG_SV.logmsg(l_module_name, 'X_COST_BREAKDOWN ( ' || L_COUNTER ||' ) .TRIP_ID: '|| X_COST_BREAKDOWN ( L_COUNTER ) .TRIP_ID );
955 WSH_DEBUG_SV.logmsg(l_module_name, 'X_COST_BREAKDOWN ( ' || L_COUNTER ||' ) .QUANTITY: '|| X_COST_BREAKDOWN ( L_COUNTER ) .QUANTITY );
956 WSH_DEBUG_SV.logmsg(l_module_name, 'X_COST_BREAKDOWN ( ' || L_COUNTER ||' ) .UOM: '|| X_COST_BREAKDOWN ( L_COUNTER ) .UOM );
957 END IF;
958 --
959 l_delivery_quantity := l_delivery_quantity + l_quantity_in_st_uom;
960 END IF;
961 END LOOP;
962 END IF; -- End of if cost factor is quantity
963
964 --
965 -- Debug Statements
966 --
967 IF l_debug_on THEN
968 WSH_DEBUG_SV.logmsg(l_module_name, 'END OF GET_DELIVERY_LEVEL_BREAKDOWN' );
969 WSH_DEBUG_SV.pop(l_module_name);
970 END IF;
971 --
972 return l_delivery_quantity;
973
974 EXCEPTION
975
976 WHEN WSH_NULL_UOM THEN
977 --
978 -- Debug Statements
979 --
980 IF l_debug_on THEN
981 WSH_DEBUG_SV.logmsg(l_module_name, 'UOM CODE IS NULL' );
982 END IF;
983 --
984 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
985
986 --
987 -- Debug Statements
988 --
989 IF l_debug_on THEN
990 WSH_DEBUG_SV.logmsg(l_module_name,'WSH_NULL_UOM exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
991 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:WSH_NULL_UOM');
992 END IF;
993 --
994 WHEN others THEN
995 WSH_UTIL_CORE.Default_Handler('WSH_FC_INTERFACE.Get_Delivery_Level_Breakdown');
996 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
997
998 --
999 -- Debug Statements
1000 --
1001 IF l_debug_on THEN
1002 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
1003 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
1004 END IF;
1005 --
1006 END Get_Delivery_Level_Breakdown;
1007
1008 -- -----------------------------------------------------------------------------------------
1009 -- PROCEDURE NAME: Get_Stop_Level_Breakdown
1010 -- This function returns the total number of units in the UOM specified in the stop
1011 -- and lists the number of units for each delivery detail in the stop
1012 -- Calculate_Stop_level_cost calls this function to get the breakdown, then calculate
1013 -- prorated cost propotionally according to the x_Cost_Break_Down table
1014 -- ------------------------------------------------------------------------------------------
1015 FUNCTION Get_Stop_Level_Breakdown(
1016 p_Stop_id IN NUMBER
1017 , p_Cost_Factor IN VARCHAR2 DEFAULT NULL
1018 , p_Relevant_Info_Tab IN RelavantInfoTabType
1019 , x_Cost_Breakdown IN OUT NOCOPY CostBreakdownTabType
1020 , x_return_status OUT NOCOPY VARCHAR2
1021 ) RETURN NUMBER
1022 IS
1023
1024 l_stop_quantity NUMBER := 0;
1025 l_standard_uom VARCHAR2(3) := NULL;
1026 l_quantity_in_st_uom NUMBER := 0;
1027 l_counter NUMBER := 0;
1028 l_cost_factor VARCHAR2(8) := NULL;
1029 i NUMBER := 0;
1030 l_weight_as_cost_factor VARCHAR2(1) := 'T';
1031 l_volume_as_cost_factor VARCHAR2(1) := 'T';
1032 l_shipped_quantity_all_zero VARCHAR2(1) := 'T';
1033
1034
1035 WSH_NULL_UOM EXCEPTION;
1036
1037
1038 --
1039 l_debug_on BOOLEAN;
1040 --
1041 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'GET_STOP_LEVEL_BREAKDOWN';
1042 --
1043 BEGIN
1044 --
1045 -- Debug Statements
1046 --
1047 --
1048 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
1049 --
1050 IF l_debug_on IS NULL
1051 THEN
1052 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
1053 END IF;
1054 --
1055 IF l_debug_on THEN
1056 WSH_DEBUG_SV.push(l_module_name);
1057 --
1058 WSH_DEBUG_SV.log(l_module_name,'P_STOP_ID',P_STOP_ID);
1059 WSH_DEBUG_SV.log(l_module_name,'P_COST_FACTOR',P_COST_FACTOR);
1060 WSH_DEBUG_SV.log(l_module_name,'P_RELEVANT_INFO_TAB.COUNT',P_RELEVANT_INFO_TAB.count);
1061 END IF;
1062 --
1063 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
1064 --
1065 -- Debug Statements
1066 --
1067 IF l_debug_on THEN
1068 WSH_DEBUG_SV.logmsg(l_module_name, 'BEGINNING OF GET_STOP_LEVEL BREAKDOWN: ' || P_STOP_ID );
1069 END IF;
1070 --
1071 l_counter := x_Cost_Breakdown.count;
1072
1073
1074 -- decide cost factor
1075 IF p_Cost_Factor is NULL THEN
1076 FOR i in 1 .. p_Relevant_Info_Tab.count LOOP
1077 IF p_Relevant_Info_Tab(i).stop_id = p_stop_id THEN
1078 IF p_Relevant_Info_Tab(i).shipped_quantity <> 0 THEN
1079 l_shipped_quantity_all_zero := 'F';
1080 IF p_Relevant_Info_Tab(i).net_weight is NULL OR
1081 p_Relevant_Info_Tab(i).net_weight = 0 THEN
1082 l_weight_as_cost_factor := 'F';
1083 END IF;
1084 IF p_Relevant_Info_Tab(i).volume is NULL OR
1085 p_Relevant_Info_Tab(i).volume = 0 THEN
1086 l_volume_as_cost_factor := 'F';
1087 END IF;
1088 END IF;
1089 END IF;
1090 END LOOP;
1091 IF l_weight_as_cost_factor = 'T' THEN
1092 l_cost_factor := 'WEIGHT';
1093 ELSIF l_volume_as_cost_factor = 'T' THEN
1094 l_cost_factor := 'VOLUME';
1095 ELSE
1096 l_cost_factor := 'QUANTITY';
1097 END IF;
1098 ELSE
1099 l_Cost_Factor := p_Cost_Factor;
1100 END IF;
1101
1102 IF l_shipped_quantity_all_zero = 'T' THEN
1103 --
1104 -- Debug Statements
1105 --
1106 IF l_debug_on THEN
1107 WSH_DEBUG_SV.pop(l_module_name);
1108 END IF;
1109 --
1110 return 0;
1111 END IF;
1112
1113 l_counter := x_Cost_Breakdown.count;
1114 --
1115 -- Debug Statements
1116 --
1117 IF l_debug_on THEN
1118 WSH_DEBUG_SV.logmsg(l_module_name, 'X_COST_BREAKDOWN HAS ' || L_COUNTER || ' RECORDS' );
1119 END IF;
1120 --
1121
1122 IF (l_cost_factor = 'WEIGHT') THEN
1123 l_standard_uom := NULL;
1124 FOR i in 1 .. p_Relevant_Info_Tab.count LOOP
1125 IF p_Relevant_Info_Tab(i).stop_id = p_stop_id AND
1126 p_Relevant_Info_Tab(i).shipped_quantity <> 0 THEN
1127 IF l_standard_uom is NULL THEN
1128 l_standard_uom := p_Relevant_Info_Tab(i).weight_uom_code;
1129 END IF;
1130 -- need to convert weight to same uom to do calculation
1131 --
1132 -- Debug Statements
1133 --
1134 IF l_debug_on THEN
1135 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_WV_UTILS.CONVERT_UOM',WSH_DEBUG_SV.C_PROC_LEVEL);
1136 END IF;
1137 --
1138 -- bug 3935583, passed value to item_id
1139 l_quantity_in_st_uom := WSH_WV_UTILS.convert_uom(
1140 from_uom => p_Relevant_Info_Tab(i).weight_uom_code,
1141 to_uom => l_standard_uom,
1142 quantity => p_Relevant_Info_Tab(i).net_weight,
1143 item_id => p_Relevant_Info_Tab(i).inventory_item_id);
1144 l_counter := l_counter + 1;
1145 x_Cost_Breakdown(l_counter).delivery_detail_id := p_Relevant_Info_Tab(i).delivery_detail_id;
1146 x_Cost_Breakdown(l_counter).delivery_id := p_Relevant_Info_Tab(i).delivery_id;
1147 x_Cost_Breakdown(l_counter).stop_id := p_Relevant_Info_Tab(i).stop_id;
1148 x_Cost_Breakdown(l_counter).quantity := l_quantity_in_st_uom;
1149 x_Cost_Breakdown(l_counter).uom := l_standard_uom;
1150 --
1151 -- Debug Statements
1152 --
1153 IF l_debug_on THEN
1154 WSH_DEBUG_SV.logmsg(l_module_name, 'X_COST_BREAKDOWN ( ' || L_COUNTER ||' ) .DELIVERY_DETAIL_ID: '|| X_COST_BREAKDOWN ( L_COUNTER ) .DELIVERY_DETAIL_ID );
1155 WSH_DEBUG_SV.logmsg(l_module_name, 'X_COST_BREAKDOWN ( ' || L_COUNTER ||' ) .DELIVERY_ID: '|| X_COST_BREAKDOWN ( L_COUNTER ) .DELIVERY_ID );
1156 WSH_DEBUG_SV.logmsg(l_module_name, 'X_COST_BREAKDOWN ( ' || L_COUNTER ||' ) .STOP_ID: '|| X_COST_BREAKDOWN ( L_COUNTER ) .STOP_ID );
1157 WSH_DEBUG_SV.logmsg(l_module_name, 'X_COST_BREAKDOWN ( ' || L_COUNTER ||' ) .TRIP_ID: '|| X_COST_BREAKDOWN ( L_COUNTER ) .TRIP_ID );
1158 WSH_DEBUG_SV.logmsg(l_module_name, 'X_COST_BREAKDOWN ( ' || L_COUNTER ||' ) .QUANTITY: '|| X_COST_BREAKDOWN ( L_COUNTER ) .QUANTITY );
1159 WSH_DEBUG_SV.logmsg(l_module_name, 'X_COST_BREAKDOWN ( ' || L_COUNTER ||' ) .UOM: '|| X_COST_BREAKDOWN ( L_COUNTER ) .UOM );
1160 END IF;
1161 --
1162 l_stop_quantity := l_stop_quantity + l_quantity_in_st_uom;
1163
1164 END IF;
1165 END LOOP;
1166 ELSIF (l_cost_factor = 'VOLUME') THEN
1167 l_standard_uom := NULL;
1168 FOR i in 1 .. p_Relevant_Info_Tab.count LOOP
1169 IF p_Relevant_Info_Tab(i).stop_id = p_stop_id AND
1170 p_Relevant_Info_Tab(i).shipped_quantity <> 0 THEN
1171 IF l_standard_uom is NULL THEN
1172 l_standard_uom := p_Relevant_Info_Tab(i).volume_uom_code;
1173 END IF;
1174 -- need to convert weight to same uom to do calculation
1175 --
1176 -- Debug Statements
1177 --
1178 IF l_debug_on THEN
1179 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_WV_UTILS.CONVERT_UOM',WSH_DEBUG_SV.C_PROC_LEVEL);
1180 END IF;
1181 -- 3935583 , passed value to item_id
1182 l_quantity_in_st_uom := WSH_WV_UTILS.convert_uom(
1183 from_uom => p_Relevant_Info_Tab(i).volume_uom_code,
1184 to_uom => l_standard_uom,
1185 quantity => p_Relevant_Info_Tab(i).volume,
1186 item_id => p_Relevant_Info_Tab(i).inventory_item_id);
1187 l_counter := l_counter + 1;
1188 x_Cost_Breakdown(l_counter).delivery_detail_id := p_Relevant_Info_Tab(i).delivery_detail_id;
1189 x_Cost_Breakdown(l_counter).delivery_id := p_Relevant_Info_Tab(i).delivery_id;
1190 x_Cost_Breakdown(l_counter).stop_id := p_Relevant_Info_Tab(i).stop_id;
1191 x_Cost_Breakdown(l_counter).quantity := l_quantity_in_st_uom;
1192 x_Cost_Breakdown(l_counter).uom := l_standard_uom;
1193 --
1194 -- Debug Statements
1195 --
1196 IF l_debug_on THEN
1197 WSH_DEBUG_SV.logmsg(l_module_name, 'X_COST_BREAKDOWN ( ' || L_COUNTER ||' ) .DELIVERY_DETAIL_ID: '|| X_COST_BREAKDOWN ( L_COUNTER ) .DELIVERY_DETAIL_ID );
1198 WSH_DEBUG_SV.logmsg(l_module_name, 'X_COST_BREAKDOWN ( ' || L_COUNTER ||' ) .DELIVERY_ID: '|| X_COST_BREAKDOWN ( L_COUNTER ) .DELIVERY_ID );
1199 WSH_DEBUG_SV.logmsg(l_module_name, 'X_COST_BREAKDOWN ( ' || L_COUNTER ||' ) .STOP_ID: '|| X_COST_BREAKDOWN ( L_COUNTER ) .STOP_ID );
1200 WSH_DEBUG_SV.logmsg(l_module_name, 'X_COST_BREAKDOWN ( ' || L_COUNTER ||' ) .TRIP_ID: '|| X_COST_BREAKDOWN ( L_COUNTER ) .TRIP_ID );
1201 WSH_DEBUG_SV.logmsg(l_module_name, 'X_COST_BREAKDOWN ( ' || L_COUNTER ||' ) .QUANTITY: '|| X_COST_BREAKDOWN ( L_COUNTER ) .QUANTITY );
1202 WSH_DEBUG_SV.logmsg(l_module_name, 'X_COST_BREAKDOWN ( ' || L_COUNTER ||' ) .UOM: '|| X_COST_BREAKDOWN ( L_COUNTER ) .UOM );
1203 END IF;
1204 --
1205 l_stop_quantity := l_stop_quantity + l_quantity_in_st_uom;
1206 END IF;
1207 END LOOP;
1208 ELSE
1209 l_standard_uom := NULL;
1210 FOR i in 1 .. p_Relevant_Info_Tab.count LOOP
1211 IF p_Relevant_Info_Tab(i).stop_id = p_stop_id AND
1212 p_Relevant_Info_Tab(i).shipped_quantity <> 0 THEN
1213 -- shipped quantity
1214 IF l_standard_uom is NULL THEN
1215 l_standard_uom := p_Relevant_Info_Tab(i).requested_quantity_uom;
1216 END IF;
1217 -- need to convert weight to same uom to do calculation
1218 --
1219 -- Debug Statements
1220 --
1221 IF l_debug_on THEN
1222 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_WV_UTILS.CONVERT_UOM',WSH_DEBUG_SV.C_PROC_LEVEL);
1223 END IF;
1224 --
1225 l_quantity_in_st_uom := WSH_WV_UTILS.convert_uom(
1226 from_uom => p_Relevant_Info_Tab(i).requested_quantity_uom,
1227 to_uom => l_standard_uom,
1228 quantity => p_Relevant_Info_Tab(i).shipped_quantity,
1229 item_id => p_Relevant_Info_Tab(i).inventory_item_id);
1230 l_counter := l_counter + 1;
1231 x_Cost_Breakdown(l_counter).delivery_detail_id := p_Relevant_Info_Tab(i).delivery_detail_id;
1232 x_Cost_Breakdown(l_counter).delivery_id := p_Relevant_Info_Tab(i).delivery_id;
1233 x_Cost_Breakdown(l_counter).stop_id := p_Relevant_Info_Tab(i).stop_id;
1234 x_Cost_Breakdown(l_counter).quantity := l_quantity_in_st_uom;
1235 x_Cost_Breakdown(l_counter).uom := l_standard_uom;
1236 --
1237 -- Debug Statements
1238 --
1239 IF l_debug_on THEN
1240 WSH_DEBUG_SV.logmsg(l_module_name, 'X_COST_BREAKDOWN ( ' || L_COUNTER ||' ) .DELIVERY_DETAIL_ID: '|| X_COST_BREAKDOWN ( L_COUNTER ) .DELIVERY_DETAIL_ID );
1241 WSH_DEBUG_SV.logmsg(l_module_name, 'X_COST_BREAKDOWN ( ' || L_COUNTER ||' ) .DELIVERY_ID: '|| X_COST_BREAKDOWN ( L_COUNTER ) .DELIVERY_ID );
1242 WSH_DEBUG_SV.logmsg(l_module_name, 'X_COST_BREAKDOWN ( ' || L_COUNTER ||' ) .STOP_ID: '|| X_COST_BREAKDOWN ( L_COUNTER ) .STOP_ID );
1243 WSH_DEBUG_SV.logmsg(l_module_name, 'X_COST_BREAKDOWN ( ' || L_COUNTER ||' ) .TRIP_ID: '|| X_COST_BREAKDOWN ( L_COUNTER ) .TRIP_ID );
1244 WSH_DEBUG_SV.logmsg(l_module_name, 'X_COST_BREAKDOWN ( ' || L_COUNTER ||' ) .QUANTITY: '|| X_COST_BREAKDOWN ( L_COUNTER ) .QUANTITY );
1245 WSH_DEBUG_SV.logmsg(l_module_name, 'X_COST_BREAKDOWN ( ' || L_COUNTER ||' ) .UOM: '|| X_COST_BREAKDOWN ( L_COUNTER ) .UOM );
1246 END IF;
1247 --
1248 l_stop_quantity := l_stop_quantity + l_quantity_in_st_uom;
1249 END IF;
1250 END LOOP;
1251 END IF; -- End of if cost factor is quantity
1252
1253 --
1254 -- Debug Statements
1255 --
1256 IF l_debug_on THEN
1257 WSH_DEBUG_SV.log(l_module_name,'x_cost_breakdown.count',x_cost_breakdown.count);
1258 WSH_DEBUG_SV.log(l_module_name,'STOP QUANTITY',l_stop_quantity);
1259 WSH_DEBUG_SV.logmsg(l_module_name, 'END OF GET_STOP_LEVEL_BREAKDOWN' );
1260 WSH_DEBUG_SV.pop(l_module_name);
1261 END IF;
1262 --
1263 return l_stop_quantity;
1264
1265 EXCEPTION
1266
1267 WHEN WSH_NULL_UOM THEN
1268 --
1269 -- Debug Statements
1270 --
1271 IF l_debug_on THEN
1272 WSH_DEBUG_SV.logmsg(l_module_name, 'UOM CODE IS NULL' );
1273 END IF;
1274 --
1275 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
1276
1277 --
1278 -- Debug Statements
1279 --
1280 IF l_debug_on THEN
1281 WSH_DEBUG_SV.logmsg(l_module_name,'WSH_NULL_UOM exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
1282 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:WSH_NULL_UOM');
1283 END IF;
1284 --
1285 WHEN others THEN
1286 WSH_UTIL_CORE.Default_Handler('WSH_FC_INTERFACE.Get_Stop_Level_Breakdown');
1287 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
1288
1289 --
1290 -- Debug Statements
1291 --
1292 IF l_debug_on THEN
1293 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
1294 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
1295 END IF;
1296 --
1297 END Get_Stop_Level_Breakdown;
1298
1299 -- -----------------------------------------------------------------------------------------
1300 -- PROCEDURE NAME: Calculate_Container_Level_Cost
1301 -- ------------------------------------------------------------------------------------------
1302
1303 PROCEDURE Calculate_Container_Level_Cost(
1304 p_Container_id IN NUMBER
1305 , p_Freight_Cost_Type_Code IN VARCHAR2
1306 , p_Freight_Cost_Id IN NUMBER
1307 , p_Freight_Cost_Amount IN NUMBER
1308 , p_From_Currency_code IN VARCHAR2
1309 , p_Conversion_Type_Code IN WSH_FREIGHT_COSTS.CONVERSION_TYPE_CODE%TYPE
1310 , p_Conversion_Rate IN WSH_FREIGHT_COSTS.CONVERSION_RATE%TYPE
1311 , p_Relevant_Info_Tab IN RelavantInfoTabType
1312 , x_Prorated_Freight_Cost IN OUT NOCOPY ProratedCostTabType
1313 , x_return_status OUT NOCOPY VARCHAR2
1314 )
1315 IS
1316
1317 l_distributed_cost NUMBER := 0;
1318 l_round_distributed_cost NUMBER := 0;
1319 l_rest_amount NUMBER := 0;
1320 l_counter NUMBER := 0;
1321 l_cost_brk_count NUMBER := 0;
1322 l_prorated_cost_count NUMBER := 0;
1323 l_container_quantity NUMBER := 0;
1324 l_Cost_Breakdown CostBreakdownTabType;
1325 l_return_status VARCHAR2(10) := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
1326
1327
1328 WSH_ROUND_AMOUNT_ERROR EXCEPTION;
1329 WSH_GET_BREAKDOWN_ERR EXCEPTION;
1330
1331 --
1332 l_debug_on BOOLEAN;
1333 --
1334 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'CALCULATE_CONTAINER_LEVEL_COST';
1335 --
1336 BEGIN
1337 --
1338 -- Debug Statements
1339 --
1340 --
1341 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
1342 --
1343 IF l_debug_on IS NULL
1344 THEN
1345 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
1346 END IF;
1347 --
1348 IF l_debug_on THEN
1349 WSH_DEBUG_SV.push(l_module_name);
1350 --
1351 WSH_DEBUG_SV.log(l_module_name,'P_CONTAINER_ID',P_CONTAINER_ID);
1352 WSH_DEBUG_SV.log(l_module_name,'P_FREIGHT_COST_TYPE_CODE',P_FREIGHT_COST_TYPE_CODE);
1353 WSH_DEBUG_SV.log(l_module_name,'P_FREIGHT_COST_ID',P_FREIGHT_COST_ID);
1354 WSH_DEBUG_SV.log(l_module_name,'P_FREIGHT_COST_AMOUNT',P_FREIGHT_COST_AMOUNT);
1355 WSH_DEBUG_SV.log(l_module_name,'P_FROM_CURRENCY_CODE',P_FROM_CURRENCY_CODE);
1356
1357 WSH_DEBUG_SV.log(l_module_name,'P_RELEVANT_INFO_TAB.COUNT',P_RELEVANT_INFO_TAB.count);
1358
1359 END IF;
1360 --
1361 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
1362 --
1363 -- Debug Statements
1364 --
1365 IF l_debug_on THEN
1366 WSH_DEBUG_SV.logmsg(l_module_name, 'BEGINNING OF CALCULATE_CONTAINER_LEVEL COST: '|| P_CONTAINER_ID );
1367 END IF;
1368 --
1369 -- get the cost factor across the whole container
1370
1371 l_container_quantity := Get_Container_Level_Breakdown(
1372 p_container_id => p_container_id,
1373 p_Relevant_Info_Tab => p_Relevant_Info_Tab,
1374 x_Cost_Breakdown => l_Cost_Breakdown,
1375 x_return_status => l_return_status
1376 );
1377
1378
1379 IF l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS THEN
1380 RAISE WSH_GET_BREAKDOWN_ERR;
1381 END IF;
1382
1383
1384 l_cost_brk_count := l_Cost_Breakdown.count;
1385 l_prorated_cost_count := x_Prorated_Freight_Cost.count;
1386 l_rest_amount := p_Freight_Cost_Amount;
1387 IF l_cost_brk_count > 0 THEN
1388 FOR l_counter IN 1..(l_cost_brk_count -1) LOOP
1389 l_prorated_cost_count := l_prorated_cost_count +1;
1390 l_distributed_cost := p_Freight_Cost_Amount * ( l_Cost_Breakdown(l_counter).quantity / l_container_quantity);
1391 Round_Cost_Amount(l_distributed_cost, p_From_Currency_Code, l_round_distributed_cost, l_return_status);
1392 IF l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS THEN
1393 RAISE WSH_ROUND_AMOUNT_ERROR;
1394 END IF;
1395 x_Prorated_Freight_Cost(l_prorated_cost_count).delivery_detail_id := l_Cost_Breakdown(l_counter).delivery_detail_id;
1396 x_Prorated_Freight_Cost(l_prorated_cost_count).freight_cost_type_code := p_freight_cost_type_code;
1397 x_Prorated_Freight_Cost(l_prorated_cost_count).freight_cost_id := p_freight_cost_id;
1398 x_Prorated_Freight_Cost(l_prorated_cost_count).amount := l_round_distributed_cost;
1399 x_Prorated_Freight_Cost(l_prorated_cost_count).currency_code := p_from_currency_code;
1400 x_Prorated_Freight_Cost(l_prorated_cost_count).conversion_type_code := p_conversion_type_code;
1401 x_Prorated_Freight_Cost(l_prorated_cost_count).conversion_rate:= p_conversion_rate;
1402
1403 --
1404 -- Debug Statements
1405 --
1406 IF l_debug_on THEN
1407 WSH_DEBUG_SV.logmsg(l_module_name, 'X_PRORATED_FREIGHT_COST ( ' || L_PRORATED_COST_COUNT ||' ) .DELIVERY_DETAIL_ID: '||X_PRORATED_FREIGHT_COST ( L_PRORATED_COST_COUNT ).DELIVERY_DETAIL_ID );
1408 WSH_DEBUG_SV.logmsg(l_module_name, 'X_PRORATED_FREIGHT_COST ( ' || L_PRORATED_COST_COUNT ||' ) .FREIGHT_COST_TYPE_CODE: '||X_PRORATED_FREIGHT_COST ( L_PRORATED_COST_COUNT ).FREIGHT_COST_TYPE_CODE );
1409 WSH_DEBUG_SV.logmsg(l_module_name, 'X_PRORATED_FREIGHT_COST ( ' || L_PRORATED_COST_COUNT ||' ) .FREIGHT_COST_ID: '||X_PRORATED_FREIGHT_COST ( L_PRORATED_COST_COUNT ).FREIGHT_COST_ID );
1410 WSH_DEBUG_SV.logmsg(l_module_name, 'X_PRORATED_FREIGHT_COST ( ' || L_PRORATED_COST_COUNT ||' ) .AMOUNT: '||X_PRORATED_FREIGHT_COST ( L_PRORATED_COST_COUNT ).AMOUNT );
1411 WSH_DEBUG_SV.logmsg(l_module_name, 'X_PRORATED_FREIGHT_COST ( ' || L_PRORATED_COST_COUNT ||' ) .CURRENCY_CODE: '||X_PRORATED_FREIGHT_COST ( L_PRORATED_COST_COUNT).CURRENCY_CODE );
1412 WSH_DEBUG_SV.logmsg(l_module_name, 'X_PRORATED_FREIGHT_COST ( ' || L_PRORATED_COST_COUNT ||' ) .CONVERSION_TYPE_CODE: '||X_PRORATED_FREIGHT_COST ( L_PRORATED_COST_COUNT ).CONVERSION_TYPE_CODE );
1413 WSH_DEBUG_SV.logmsg(l_module_name, 'X_PRORATED_FREIGHT_COST ( ' || L_PRORATED_COST_COUNT ||' ) .CONVERSION_RATE: '||X_PRORATED_FREIGHT_COST ( L_PRORATED_COST_COUNT ).CONVERSION_RATE );
1414 END IF;
1415 --
1416 l_rest_amount := l_rest_amount - l_round_distributed_cost;
1417 END LOOP;
1418
1419 -- last record
1420 l_prorated_cost_count := l_prorated_cost_count +1;
1421 l_distributed_cost := l_rest_amount;
1422 Round_Cost_Amount(l_distributed_cost, p_From_Currency_Code, l_round_distributed_cost, l_return_status);
1423 IF l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS THEN
1424 RAISE WSH_ROUND_AMOUNT_ERROR;
1425 END IF;
1426 x_Prorated_Freight_Cost(l_prorated_cost_count).delivery_detail_id := l_Cost_Breakdown(l_cost_brk_count).delivery_detail_id;
1427 x_Prorated_Freight_Cost(l_prorated_cost_count).freight_cost_type_code := p_freight_cost_type_code;
1428 x_Prorated_Freight_Cost(l_prorated_cost_count).freight_cost_id := p_freight_cost_id;
1429 x_Prorated_Freight_Cost(l_prorated_cost_count).amount := l_round_distributed_cost;
1430 x_Prorated_Freight_Cost(l_prorated_cost_count).currency_code := p_from_currency_code;
1431 x_Prorated_Freight_Cost(l_prorated_cost_count).conversion_type_code := p_conversion_type_code;
1432 x_Prorated_Freight_Cost(l_prorated_cost_count).conversion_rate:= p_conversion_rate;
1433 --
1434 -- Debug Statements
1435 --
1436 IF l_debug_on THEN
1437 WSH_DEBUG_SV.logmsg(l_module_name, 'X_PRORATED_FREIGHT_COST ( ' || L_PRORATED_COST_COUNT ||' ) .DELIVERY_DETAIL_ID: '||X_PRORATED_FREIGHT_COST ( L_PRORATED_COST_COUNT ) .DELIVERY_DETAIL_ID );
1438 WSH_DEBUG_SV.logmsg(l_module_name, 'X_PRORATED_FREIGHT_COST ( ' || L_PRORATED_COST_COUNT ||' ) .FREIGHT_COST_TYPE_ID: '||X_PRORATED_FREIGHT_COST ( L_PRORATED_COST_COUNT ) .FREIGHT_COST_TYPE_CODE );
1439 WSH_DEBUG_SV.logmsg(l_module_name, 'X_PRORATED_FREIGHT_COST ( ' || L_PRORATED_COST_COUNT ||' ) .FREIGHT_COST_ID: '||X_PRORATED_FREIGHT_COST ( L_PRORATED_COST_COUNT ) .FREIGHT_COST_ID );
1440 WSH_DEBUG_SV.logmsg(l_module_name, 'X_PRORATED_FREIGHT_COST ( ' || L_PRORATED_COST_COUNT ||' ) .AMOUNT: '||X_PRORATED_FREIGHT_COST ( L_PRORATED_COST_COUNT ) .AMOUNT );
1441 WSH_DEBUG_SV.logmsg(l_module_name, 'X_PRORATED_FREIGHT_COST ( ' || L_PRORATED_COST_COUNT ||' ) .CURRENCY_CODE: '||X_PRORATED_FREIGHT_COST ( L_PRORATED_COST_COUNT ) .CURRENCY_CODE );
1442 WSH_DEBUG_SV.logmsg(l_module_name, 'X_PRORATED_FREIGHT_COST ( ' || L_PRORATED_COST_COUNT ||' ) .CONVERSION_TYPE_CODE: '||X_PRORATED_FREIGHT_COST ( L_PRORATED_COST_COUNT ) .CONVERSION_TYPE_CODE );
1443 WSH_DEBUG_SV.logmsg(l_module_name, 'X_PRORATED_FREIGHT_COST ( ' || L_PRORATED_COST_COUNT ||' ) .CONVERSION_RATE: '||X_PRORATED_FREIGHT_COST ( L_PRORATED_COST_COUNT ) .CONVERSION_RATE );
1444
1445 END IF;
1446 --
1447 END IF;
1448
1449 --
1450 -- Debug Statements
1451 --
1452 IF l_debug_on THEN
1453 WSH_DEBUG_SV.logmsg(l_module_name, 'END OF CALCULATE_CONTAINER_LEVEL_COST' );
1454 WSH_DEBUG_SV.pop(l_module_name);
1455 END IF;
1456 --
1457
1458 EXCEPTION
1459
1460 WHEN WSH_GET_BREAKDOWN_ERR THEN
1461 --
1462 -- Debug Statements
1463 --
1464 IF l_debug_on THEN
1465 WSH_DEBUG_SV.logmsg(l_module_name, 'GET COST BREAKDOWN ERROR AT CONTAINER ' || P_CONTAINER_ID );
1466 END IF;
1467 --
1468 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
1469
1470 --
1471 -- Debug Statements
1472 --
1473 IF l_debug_on THEN
1474 WSH_DEBUG_SV.logmsg(l_module_name,'WSH_GET_BREAKDOWN_ERR exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
1475 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:WSH_GET_BREAKDOWN_ERR');
1476 END IF;
1477 --
1478 WHEN WSH_ROUND_AMOUNT_ERROR THEN
1479 --
1480 -- Debug Statements
1481 --
1482 IF l_debug_on THEN
1483 WSH_DEBUG_SV.logmsg(l_module_name, 'GET ROUND AMOUNT ERROR AT CONTAINER ' || P_CONTAINER_ID );
1484 END IF;
1485 --
1486 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
1487
1488 --
1489 -- Debug Statements
1490 --
1491 IF l_debug_on THEN
1492 WSH_DEBUG_SV.logmsg(l_module_name,'WSH_ROUND_AMOUNT_ERROR exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
1493 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:WSH_ROUND_AMOUNT_ERROR');
1494 END IF;
1495 --
1496 WHEN others THEN
1497 WSH_UTIL_CORE.Default_Handler('WSH_FC_INTERFACE.Calculate_Container_Level_Cost');
1498 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
1499
1500 --
1501 -- Debug Statements
1502 --
1503 IF l_debug_on THEN
1504 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
1505 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
1506 END IF;
1507 --
1508 END Calculate_Container_Level_Cost;
1509
1510
1511 -- -----------------------------------------------------------------------------------------
1512 -- PROCEDURE NAME: Calculate_Detail_Level_Cost
1513 -- ------------------------------------------------------------------------------------------
1514
1515
1516 PROCEDURE Calculate_Detail_Level_Cost(
1517 p_Delivery_Detail_Id IN NUMBER
1518 , p_Freight_Cost_Type_Code IN VARCHAR2
1519 , p_Freight_cost_id IN NUMBER
1520 , p_Freight_Cost_Amount IN NUMBER
1521 , p_From_Currency_code IN VARCHAR2
1522 , p_Conversion_Type_Code IN WSH_FREIGHT_COSTS.CONVERSION_TYPE_CODE%TYPE
1523 , p_Conversion_Rate IN WSH_FREIGHT_COSTS.CONVERSION_RATE%TYPE
1524 , p_Relevant_Info_Tab IN RelavantInfoTabType
1525 , x_Prorated_Freight_Cost IN OUT NOCOPY ProratedCostTabType
1526 , x_return_status OUT NOCOPY VARCHAR2
1527 )
1528 IS
1529
1530 l_next_table_id NUMBER;
1531 l_number_container NUMBER;
1532 l_container_flag VARCHAR2(1);
1533 l_return_status VARCHAR2(30);
1534
1535
1536 --
1537 l_debug_on BOOLEAN;
1538 --
1539 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'CALCULATE_DETAIL_LEVEL_COST';
1540 --
1541 BEGIN
1542
1543 --
1544 -- Debug Statements
1545 --
1546 --
1547 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
1548 --
1549 IF l_debug_on IS NULL
1550 THEN
1551 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
1552 END IF;
1553 --
1554 IF l_debug_on THEN
1555 WSH_DEBUG_SV.push(l_module_name);
1556 --
1557 WSH_DEBUG_SV.log(l_module_name,'P_DELIVERY_DETAIL_ID',P_DELIVERY_DETAIL_ID);
1558 WSH_DEBUG_SV.log(l_module_name,'P_FREIGHT_COST_TYPE_CODE',P_FREIGHT_COST_TYPE_CODE);
1559 WSH_DEBUG_SV.log(l_module_name,'P_FREIGHT_COST_ID',P_FREIGHT_COST_ID);
1560 WSH_DEBUG_SV.log(l_module_name,'P_FREIGHT_COST_AMOUNT',P_FREIGHT_COST_AMOUNT);
1561 WSH_DEBUG_SV.log(l_module_name,'P_FROM_CURRENCY_CODE',P_FROM_CURRENCY_CODE);
1562 WSH_DEBUG_SV.log(l_module_name,'P_FROM_CURRENCY_CODE',P_CONVERSION_TYPE_CODE);
1563 WSH_DEBUG_SV.log(l_module_name,'P_FROM_CURRENCY_CODE',P_CONVERSION_RATE);
1564
1565 WSH_DEBUG_SV.log(l_module_name,'P_RELEVANT_INFO_TAB.COUNT',P_RELEVANT_INFO_TAB.count);
1566
1567 END IF;
1568 --
1569 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
1570 l_next_table_id := x_Prorated_Freight_Cost.COUNT + 1;
1571 x_Prorated_Freight_Cost(l_next_table_id).delivery_detail_id := p_delivery_detail_id;
1572 x_Prorated_Freight_Cost(l_next_table_id).freight_cost_type_code := p_freight_cost_type_code;
1573 x_prorated_Freight_Cost(l_next_table_id).freight_cost_id := p_freight_cost_id;
1574 x_prorated_Freight_Cost(l_next_table_id).amount := p_Freight_Cost_Amount;
1575 x_prorated_Freight_Cost(l_next_table_id).currency_code := p_From_Currency_code;
1576 x_prorated_Freight_Cost(l_next_table_id).conversion_type_code := p_conversion_type_code;
1577 x_prorated_Freight_Cost(l_next_table_id).conversion_rate := p_conversion_rate;
1578
1579 --
1580 -- Debug Statements
1581 --
1582 IF l_debug_on THEN
1583 WSH_DEBUG_SV.logmsg(l_module_name, 'X_PRORATED_FREIGHT_COST ( ' || L_NEXT_TABLE_ID ||' ) .DELIVERY_DETAIL_ID: '||X_PRORATED_FREIGHT_COST ( L_NEXT_TABLE_ID ) .DELIVERY_DETAIL_ID );
1584 WSH_DEBUG_SV.logmsg(l_module_name, 'X_PRORATED_FREIGHT_COST ( ' || L_NEXT_TABLE_ID ||' ) .FREIGHT_COST_TYPE_CODE: '||X_PRORATED_FREIGHT_COST ( L_NEXT_TABLE_ID ) .FREIGHT_COST_TYPE_CODE );
1585 WSH_DEBUG_SV.logmsg(l_module_name, 'X_PRORATED_FREIGHT_COST ( ' || L_NEXT_TABLE_ID ||' ) .FREIGHT_COST_ID: '||X_PRORATED_FREIGHT_COST ( L_NEXT_TABLE_ID ) .FREIGHT_COST_ID );
1586 WSH_DEBUG_SV.logmsg(l_module_name, 'X_PRORATED_FREIGHT_COST ( ' || L_NEXT_TABLE_ID ||' ) .AMOUNT: '||X_PRORATED_FREIGHT_COST ( L_NEXT_TABLE_ID ) .AMOUNT );
1587 WSH_DEBUG_SV.logmsg(l_module_name, 'X_PRORATED_FREIGHT_COST ( ' || L_NEXT_TABLE_ID ||' ) .CURRENCY_CODE: '||X_PRORATED_FREIGHT_COST ( L_NEXT_TABLE_ID ) .CURRENCY_CODE );
1588 WSH_DEBUG_SV.logmsg(l_module_name, 'X_PRORATED_FREIGHT_COST ( ' || L_NEXT_TABLE_ID ||' ) .CONVERSION_TYPE_CODE: '||X_PRORATED_FREIGHT_COST ( L_NEXT_TABLE_ID ) .CONVERSION_TYPE_CODE );
1589 WSH_DEBUG_SV.logmsg(l_module_name, 'X_PRORATED_FREIGHT_COST ( ' || L_NEXT_TABLE_ID ||' ) .CONVERSION_RATE: '||X_PRORATED_FREIGHT_COST ( L_NEXT_TABLE_ID ) .CONVERSION_RATE );
1590
1591 WSH_DEBUG_SV.pop(l_module_name);
1592 END IF;
1593 --
1594 Exception
1595 WHEN others THEN
1596 WSH_UTIL_CORE.Default_Handler('WSH_FC_INTERFACE.Calculate_Detail_Level_Cost');
1597 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
1598 --
1599 -- Debug Statements
1600 --
1601 IF l_debug_on THEN
1602 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
1603 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
1604 END IF;
1605 --
1606 END Calculate_Detail_Level_Cost;
1607
1608 -- -----------------------------------------------------------------------
1609 -- API name: Calculate_Delivery_Level_Cost
1610 -- Type: private
1611 -- Function: need to add a cost_factor parameter, this procedure may be called in
1612 -- calculating the trip or trip stop level cost, the cost_factor is pre-decided in the
1613 -- trip or trip stop level before it pass to calculate_delivery_level_cost
1614 -- Parameters: added optional p_cost_factor
1615 -- Notes: please use :set tabstop=3 to view this file in vi to get
1616 -- proper alignment
1617 --
1618 -- ------------------------------------------------------------------------
1619
1620 PROCEDURE Calculate_Delivery_Level_Cost(
1621 p_Delivery_Id IN NUMBER
1622 , p_Freight_Cost_Type_Code IN VARCHAR2
1623 , p_Freight_Cost_Id IN NUMBER
1624 , p_Freight_Cost_Amount IN NUMBER
1625 , p_From_Currency_Code IN VARCHAR2
1626 , p_Conversion_Type_Code IN WSH_FREIGHT_COSTS.CONVERSION_TYPE_CODE%TYPE
1627 , p_Conversion_Rate IN WSH_FREIGHT_COSTS.CONVERSION_RATE%TYPE
1628 , p_Relevant_Info_Tab IN RelavantInfoTabType
1629 , x_Prorated_Freight_Cost IN OUT NOCOPY ProratedCostTabType
1630 , x_return_status OUT NOCOPY VARCHAR2
1631 )
1632 IS
1633
1634 l_distributed_cost NUMBER := 0;
1635 l_round_distributed_cost NUMBER := 0;
1636 l_rest_amount NUMBER := 0;
1637 l_counter NUMBER := 0;
1638 l_cost_brk_count NUMBER := 0;
1639 l_prorated_cost_count NUMBER := 0;
1640 l_delivery_quantity NUMBER := 0;
1641 l_Cost_Breakdown CostBreakdownTabType;
1642 l_return_status VARCHAR2(10) := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
1643
1644 WSH_ROUND_AMOUNT_ERROR EXCEPTION;
1645 WSH_GET_BREAKDOWN_ERR EXCEPTION;
1646
1647
1648 --
1649 l_debug_on BOOLEAN;
1650 --
1651 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'CALCULATE_DELIVERY_LEVEL_COST';
1652 --
1653 BEGIN
1654 --
1655 -- Debug Statements
1656 --
1657 --
1658 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
1659 --
1660 IF l_debug_on IS NULL
1661 THEN
1662 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
1663 END IF;
1664 --
1665 IF l_debug_on THEN
1666 WSH_DEBUG_SV.push(l_module_name);
1667 --
1668 WSH_DEBUG_SV.log(l_module_name,'P_DELIVERY_ID',P_DELIVERY_ID);
1669 WSH_DEBUG_SV.log(l_module_name,'P_FREIGHT_COST_TYPE_CODE',P_FREIGHT_COST_TYPE_CODE);
1670 WSH_DEBUG_SV.log(l_module_name,'P_FREIGHT_COST_ID',P_FREIGHT_COST_ID);
1671 WSH_DEBUG_SV.log(l_module_name,'P_FREIGHT_COST_AMOUNT',P_FREIGHT_COST_AMOUNT);
1672 WSH_DEBUG_SV.log(l_module_name,'P_FROM_CURRENCY_CODE',P_FROM_CURRENCY_CODE);
1673 WSH_DEBUG_SV.log(l_module_name,'P_CONVERSION_TYPE_CODE',P_CONVERSION_TYPE_CODE);
1674 WSH_DEBUG_SV.log(l_module_name,'P_CONVERSION_RATE',P_CONVERSION_RATE);
1675
1676 WSH_DEBUG_SV.log(l_module_name,'P_RELEVANT_INFO_TAB.COUNT',P_RELEVANT_INFO_TAB.count);
1677
1678 END IF;
1679 --
1680 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
1681 --
1682 -- Debug Statements
1683 --
1684 IF l_debug_on THEN
1685 WSH_DEBUG_SV.logmsg(l_module_name, 'BEGINNING OF CALCULATE_DELIVERY_LEVEL_COST , DELIVERY_ID: '|| P_DELIVERY_ID || ' FREIGHT_COST_ID: ' || P_FREIGHT_COST_ID );
1686 END IF;
1687 --
1688 -- get the cost factor across the whole stop
1689 -- needs to pass the delivery id as well
1690
1691 l_delivery_quantity := Get_Delivery_Level_Breakdown(
1692 p_delivery_id => p_delivery_id,
1693 p_Relevant_Info_Tab => p_Relevant_Info_Tab,
1694 x_Cost_Breakdown => l_Cost_Breakdown,
1695 x_return_status => l_return_status
1696 );
1697
1698
1699 IF l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS THEN
1700 RAISE WSH_GET_BREAKDOWN_ERR;
1701 END IF;
1702
1703 l_cost_brk_count := l_Cost_Breakdown.count;
1704 l_prorated_cost_count := x_Prorated_Freight_Cost.count;
1705 l_rest_amount := p_Freight_Cost_Amount;
1706 IF l_cost_brk_count > 0 THEN
1707 FOR l_counter IN 1..(l_cost_brk_count -1) LOOP
1708 l_prorated_cost_count := l_prorated_cost_count +1;
1709 l_distributed_cost := p_Freight_Cost_Amount * ( l_Cost_Breakdown(l_counter).quantity / l_delivery_quantity);
1710 Round_Cost_Amount(l_distributed_cost, p_From_Currency_Code, l_round_distributed_cost, l_return_status);
1711 IF l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS THEN
1712 RAISE WSH_ROUND_AMOUNT_ERROR;
1713 END IF;
1714 x_Prorated_Freight_Cost(l_prorated_cost_count).delivery_detail_id := l_Cost_Breakdown(l_counter).delivery_detail_id;
1715 x_Prorated_Freight_Cost(l_prorated_cost_count).freight_cost_type_code := p_freight_cost_type_code;
1716 x_Prorated_Freight_Cost(l_prorated_cost_count).freight_cost_id := p_freight_cost_id;
1717 x_Prorated_Freight_Cost(l_prorated_cost_count).amount := l_round_distributed_cost;
1718 x_Prorated_Freight_Cost(l_prorated_cost_count).currency_code := p_from_currency_code;
1719 x_Prorated_Freight_Cost(l_prorated_cost_count).conversion_type_code := p_conversion_type_code;
1720 x_Prorated_Freight_Cost(l_prorated_cost_count).conversion_rate := p_conversion_rate;
1721 l_rest_amount := l_rest_amount - l_round_distributed_cost;
1722 --
1723 -- Debug Statements
1724 --
1725 IF l_debug_on THEN
1726 WSH_DEBUG_SV.logmsg(l_module_name, 'X_PRORATED_FREIGHT_COST ( ' || L_PRORATED_COST_COUNT ||' ) .DELIVERY_DETAIL_ID: '||X_PRORATED_FREIGHT_COST ( L_PRORATED_COST_COUNT ) .DELIVERY_DETAIL_ID );
1727 WSH_DEBUG_SV.logmsg(l_module_name, 'X_PRORATED_FREIGHT_COST ( ' || L_PRORATED_COST_COUNT ||' ) .FREIGHT_COST_TYPE_CODE: '||X_PRORATED_FREIGHT_COST ( L_PRORATED_COST_COUNT ) .FREIGHT_COST_TYPE_CODE );
1728 WSH_DEBUG_SV.logmsg(l_module_name, 'X_PRORATED_FREIGHT_COST ( ' || L_PRORATED_COST_COUNT ||' ) .FREIGHT_COST_ID: '||X_PRORATED_FREIGHT_COST ( L_PRORATED_COST_COUNT ) .FREIGHT_COST_ID );
1729 WSH_DEBUG_SV.logmsg(l_module_name, 'X_PRORATED_FREIGHT_COST ( ' || L_PRORATED_COST_COUNT ||' ) .AMOUNT: '||X_PRORATED_FREIGHT_COST ( L_PRORATED_COST_COUNT ) .AMOUNT );
1730 WSH_DEBUG_SV.logmsg(l_module_name, 'X_PRORATED_FREIGHT_COST ( ' || L_PRORATED_COST_COUNT ||' ) .CURRENCY_CODE: '||X_PRORATED_FREIGHT_COST ( L_PRORATED_COST_COUNT ) .CURRENCY_CODE );
1731 WSH_DEBUG_SV.logmsg(l_module_name, 'X_PRORATED_FREIGHT_COST ( ' || L_PRORATED_COST_COUNT ||' ) .CONVERSION_TYPE_CODE: '||X_PRORATED_FREIGHT_COST ( L_PRORATED_COST_COUNT ) .CONVERSION_TYPE_CODE );
1732 WSH_DEBUG_SV.logmsg(l_module_name, 'X_PRORATED_FREIGHT_COST ( ' || L_PRORATED_COST_COUNT ||' ) .CONVERSION_RATE: '||X_PRORATED_FREIGHT_COST ( L_PRORATED_COST_COUNT ) .CONVERSION_RATE );
1733 END IF;
1734 --
1735 END LOOP;
1736 -- last record
1737 l_prorated_cost_count := l_prorated_cost_count +1;
1738 l_distributed_cost := l_rest_amount;
1739 Round_Cost_Amount(l_distributed_cost, p_From_Currency_Code, l_round_distributed_cost, l_return_status);
1740 IF l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS THEN
1741 RAISE WSH_ROUND_AMOUNT_ERROR;
1742 END IF;
1743 x_Prorated_Freight_Cost(l_prorated_cost_count).delivery_detail_id := l_Cost_Breakdown(l_cost_brk_count).delivery_detail_id;
1744 x_Prorated_Freight_Cost(l_prorated_cost_count).freight_cost_type_code := p_freight_cost_type_code;
1745 x_Prorated_Freight_Cost(l_prorated_cost_count).freight_cost_id := p_freight_cost_id;
1746 x_Prorated_Freight_Cost(l_prorated_cost_count).amount := l_round_distributed_cost;
1747 x_Prorated_Freight_Cost(l_prorated_cost_count).currency_code := p_from_currency_code;
1748 x_Prorated_Freight_Cost(l_prorated_cost_count).conversion_type_code := p_conversion_type_code;
1749 x_Prorated_Freight_Cost(l_prorated_cost_count).conversion_rate := p_conversion_rate;
1750 --
1751 -- Debug Statements
1752 --
1753 IF l_debug_on THEN
1754 WSH_DEBUG_SV.logmsg(l_module_name, 'X_PRORATED_FREIGHT_COST ( ' || L_PRORATED_COST_COUNT ||' ) .DELIVERY_DETAIL_ID: '||X_PRORATED_FREIGHT_COST ( L_PRORATED_COST_COUNT ) .DELIVERY_DETAIL_ID );
1755 WSH_DEBUG_SV.logmsg(l_module_name, 'X_PRORATED_FREIGHT_COST ( ' || L_PRORATED_COST_COUNT ||' ) .FREIGHT_COST_TYPE_CODE: '||X_PRORATED_FREIGHT_COST ( L_PRORATED_COST_COUNT ) .FREIGHT_COST_TYPE_CODE );
1756 WSH_DEBUG_SV.logmsg(l_module_name, 'X_PRORATED_FREIGHT_COST ( ' || L_PRORATED_COST_COUNT ||' ) .FREIGHT_COST_ID: '||X_PRORATED_FREIGHT_COST ( L_PRORATED_COST_COUNT ) .FREIGHT_COST_ID );
1757 WSH_DEBUG_SV.logmsg(l_module_name, 'X_PRORATED_FREIGHT_COST ( ' || L_PRORATED_COST_COUNT ||' ) .AMOUNT: '||X_PRORATED_FREIGHT_COST ( L_PRORATED_COST_COUNT ) .AMOUNT );
1758 WSH_DEBUG_SV.logmsg(l_module_name, 'X_PRORATED_FREIGHT_COST ( ' || L_PRORATED_COST_COUNT ||' ) .CURRENCY_CODE: '||X_PRORATED_FREIGHT_COST ( L_PRORATED_COST_COUNT ) .CURRENCY_CODE );
1759 WSH_DEBUG_SV.logmsg(l_module_name, 'X_PRORATED_FREIGHT_COST ( ' || L_PRORATED_COST_COUNT ||' ) .CONVERSION_TYPE_CODE: '||X_PRORATED_FREIGHT_COST ( L_PRORATED_COST_COUNT ) .CONVERSION_TYPE_CODE );
1760 WSH_DEBUG_SV.logmsg(l_module_name, 'X_PRORATED_FREIGHT_COST ( ' || L_PRORATED_COST_COUNT ||' ) .CONVERSION_RATE: '||X_PRORATED_FREIGHT_COST ( L_PRORATED_COST_COUNT ) .CONVERSION_RATE );
1761 END IF;
1762 --
1763
1764 END IF;
1765 --
1766 -- Debug Statements
1767 --
1768 IF l_debug_on THEN
1769 WSH_DEBUG_SV.logmsg(l_module_name, 'END OF CALCULATE_DELIVERY_LEVEL_COST' );
1770 WSH_DEBUG_SV.pop(l_module_name);
1771 END IF;
1772 --
1773 EXCEPTION
1774
1775 WHEN WSH_GET_BREAKDOWN_ERR THEN
1776 --
1777 -- Debug Statements
1778 --
1779 IF l_debug_on THEN
1780 WSH_DEBUG_SV.logmsg(l_module_name, 'GET COST BREAKDOWN ERROR AT DELIVERY ' || P_DELIVERY_ID );
1781 END IF;
1782 --
1783 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
1784
1785 --
1786 -- Debug Statements
1787 --
1788 IF l_debug_on THEN
1789 WSH_DEBUG_SV.logmsg(l_module_name,'WSH_GET_BREAKDOWN_ERR exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
1790 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:WSH_GET_BREAKDOWN_ERR');
1791 END IF;
1792 --
1793 WHEN WSH_ROUND_AMOUNT_ERROR THEN
1794 --
1795 -- Debug Statements
1796 --
1797 IF l_debug_on THEN
1798 WSH_DEBUG_SV.logmsg(l_module_name, 'GET ROUND AMOUNT ERROR AT DELIVERY' || P_DELIVERY_ID );
1799 END IF;
1800 --
1801 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
1802
1803 --
1804 -- Debug Statements
1805 --
1806 IF l_debug_on THEN
1807 WSH_DEBUG_SV.logmsg(l_module_name,'WSH_ROUND_AMOUNT_ERROR exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
1808 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:WSH_ROUND_AMOUNT_ERROR');
1809 END IF;
1810 --
1811 WHEN others THEN
1812 WSH_UTIL_CORE.Default_Handler('WSH_FC_INTERFACE.Calculate_Delivery_Level_Cost');
1813 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
1814
1815 --
1816 -- Debug Statements
1817 --
1818 IF l_debug_on THEN
1819 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
1820 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
1821 END IF;
1822 --
1823 END Calculate_Delivery_Level_Cost;
1824
1825 -- -----------------------------------------------------------------------
1826 -- API name: Calculate_Stop_Level_Cost
1827 -- Type: private
1828 -- Function:
1829 -- Parameters:
1830 -- Notes: please use :set tabstop=3 to view this file in vi to get
1831 -- proper alignment
1832 --
1833 -- ------------------------------------------------------------------------
1834 PROCEDURE Calculate_Stop_Level_Cost(
1835 p_Stop_Id IN NUMBER
1836 , p_Freight_Cost_Type_Code IN VARCHAR2
1837 , p_Freight_Cost_Id IN NUMBER
1838 , p_Freight_Cost_Amount IN NUMBER
1839 , p_From_Currency_Code IN VARCHAR2
1840 , p_Conversion_Type_Code IN WSH_FREIGHT_COSTS.CONVERSION_TYPE_CODE%TYPE
1841 , p_Conversion_Rate IN WSH_FREIGHT_COSTS.CONVERSION_RATE%TYPE
1842 , p_Relevant_Info_Tab IN RelavantInfoTabType
1843 , x_Prorated_Freight_Cost IN OUT NOCOPY ProratedCostTabType
1844 , x_return_status OUT NOCOPY VARCHAR2
1845 )
1846 IS
1847
1848 l_distributed_cost NUMBER := 0;
1849 l_round_distributed_cost NUMBER := 0;
1850 l_rest_amount NUMBER := 0;
1851 l_counter NUMBER := 0;
1852 l_cost_brk_count NUMBER := 0;
1853 l_prorated_cost_count NUMBER := 0;
1854 l_stop_quantity NUMBER := 0;
1855 l_Cost_Breakdown CostBreakdownTabType;
1856 l_return_status VARCHAR2(10) := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
1857
1858 WSH_ROUND_AMOUNT_ERROR EXCEPTION;
1859 WSH_GET_BREAKDOWN_ERR EXCEPTION;
1860
1861
1862
1863 --
1864 l_debug_on BOOLEAN;
1865 --
1866 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'CALCULATE_STOP_LEVEL_COST';
1867 --
1868 BEGIN
1869 --
1870 -- Debug Statements
1871 --
1872 --
1873 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
1874 --
1875 IF l_debug_on IS NULL
1876 THEN
1877 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
1878 END IF;
1879 --
1880 IF l_debug_on THEN
1881 WSH_DEBUG_SV.push(l_module_name);
1882 --
1883 WSH_DEBUG_SV.log(l_module_name,'P_STOP_ID',P_STOP_ID);
1884 WSH_DEBUG_SV.log(l_module_name,'P_FREIGHT_COST_TYPE_CODE',P_FREIGHT_COST_TYPE_CODE);
1885 WSH_DEBUG_SV.log(l_module_name,'P_FREIGHT_COST_ID',P_FREIGHT_COST_ID);
1886 WSH_DEBUG_SV.log(l_module_name,'P_FREIGHT_COST_AMOUNT',P_FREIGHT_COST_AMOUNT);
1887 WSH_DEBUG_SV.log(l_module_name,'P_FROM_CURRENCY_CODE',P_FROM_CURRENCY_CODE);
1888 WSH_DEBUG_SV.log(l_module_name,'P_CONVERSION_TYPE_CODE',P_CONVERSION_TYPE_CODE);
1889 WSH_DEBUG_SV.log(l_module_name,'P_CONVERSION_RATE',P_CONVERSION_RATE);
1890
1891 WSH_DEBUG_SV.log(l_module_name,'P_RELEVANT_INFO_TAB.COUNT',P_RELEVANT_INFO_TAB.count);
1892
1893 /* WSH_DEBUG_SV.log(l_module_name,'P_RELEVANT_INFO_TAB.delivery_detail_id',P_RELEVANT_INFO_TAB.delivery_detail_id);
1894 WSH_DEBUG_SV.log(l_module_name,'P_RELEVANT_INFO_TAB.container_id',P_RELEVANT_INFO_TAB.container_id);
1895 WSH_DEBUG_SV.log(l_module_name,'P_RELEVANT_INFO_TAB.delivery_id',P_RELEVANT_INFO_TAB.delivery_id);
1896 WSH_DEBUG_SV.log(l_module_name,'P_RELEVANT_INFO_TAB.stop_id',P_RELEVANT_INFO_TAB.stop_id);
1897 WSH_DEBUG_SV.log(l_module_name,'P_RELEVANT_INFO_TAB.trip_id',P_RELEVANT_INFO_TAB.trip_id);
1898 WSH_DEBUG_SV.log(l_module_name,'P_RELEVANT_INFO_TAB.requested_quantity',P_RELEVANT_INFO_TAB.requested_quantity);
1899 WSH_DEBUG_SV.log(l_module_name,'P_RELEVANT_INFO_TAB.shipped_quantity',P_RELEVANT_INFO_TAB.shipped_quantity);
1900 WSH_DEBUG_SV.log(l_module_name,'P_RELEVANT_INFO_TAB.requested_quantity_Uom',P_RELEVANT_INFO_TAB.requested_quantity_uom);
1901 WSH_DEBUG_SV.log(l_module_name,'P_RELEVANT_INFO_TAB.net_weight',P_RELEVANT_INFO_TAB.net_weight);
1902 WSH_DEBUG_SV.log(l_module_name,'P_RELEVANT_INFO_TAB.weight_uom_code',P_RELEVANT_INFO_TAB.weight_uom_code);
1903 WSH_DEBUG_SV.log(l_module_name,'P_RELEVANT_INFO_TAB.volume',P_RELEVANT_INFO_TAB.volume);
1904 WSH_DEBUG_SV.log(l_module_name,'P_RELEVANT_INFO_TAB.volume_uom_code',P_RELEVANT_INFO_TAB.volume_uom_code);
1905 */
1906 END IF;
1907 --
1908 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
1909 --
1910 -- Debug Statements
1911 --
1912 IF l_debug_on THEN
1913 WSH_DEBUG_SV.logmsg(l_module_name, 'BEGINNING OF CALCULATE_STOP_LEVEL COST: '|| P_STOP_ID || ' FREIGHT COST ID: '|| P_FREIGHT_COST_ID );
1914 END IF;
1915 --
1916 -- get the cost factor across the whole stop
1917 -- needs to pass the stop id as well
1918
1919 l_stop_quantity := Get_Stop_Level_Breakdown(
1920 p_Stop_id => p_stop_id,
1921 p_Relevant_Info_Tab => p_Relevant_Info_Tab,
1922 x_Cost_Breakdown => l_Cost_Breakdown,
1923 x_return_status => l_return_status
1924 );
1925
1926
1927 IF l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS THEN
1928 RAISE WSH_GET_BREAKDOWN_ERR;
1929 END IF;
1930
1931 l_cost_brk_count := l_Cost_Breakdown.count;
1932 l_prorated_cost_count := x_Prorated_Freight_Cost.count;
1933 l_rest_amount := p_Freight_Cost_Amount;
1934 IF l_cost_brk_count > 0 THEN
1935 FOR l_counter IN 1..(l_cost_brk_count -1) LOOP
1936 l_prorated_cost_count := l_prorated_cost_count +1;
1937 l_distributed_cost := p_Freight_Cost_Amount * ( l_Cost_Breakdown(l_counter).quantity / l_stop_quantity);
1938 Round_Cost_Amount(l_distributed_cost, p_From_Currency_Code, l_round_distributed_cost, l_return_status);
1939 IF l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS THEN
1940 RAISE WSH_ROUND_AMOUNT_ERROR;
1941 END IF;
1942 x_Prorated_Freight_Cost(l_prorated_cost_count).delivery_detail_id := l_Cost_Breakdown(l_counter).delivery_detail_id;
1943 x_Prorated_Freight_Cost(l_prorated_cost_count).freight_cost_type_code := p_freight_cost_type_code;
1944 x_Prorated_Freight_Cost(l_prorated_cost_count).freight_cost_id := p_freight_cost_id;
1945 x_Prorated_Freight_Cost(l_prorated_cost_count).amount := l_round_distributed_cost;
1946 x_Prorated_Freight_Cost(l_prorated_cost_count).currency_code := p_from_currency_code;
1947 x_Prorated_Freight_Cost(l_prorated_cost_count).conversion_type_code := p_conversion_type_code;
1948 x_Prorated_Freight_Cost(l_prorated_cost_count).conversion_rate := p_conversion_rate;
1949
1950 l_rest_amount := l_rest_amount - l_round_distributed_cost;
1951 --
1952 -- Debug Statements
1953 --
1954 IF l_debug_on THEN
1955 WSH_DEBUG_SV.logmsg(l_module_name, 'X_PRORATED_FREIGHT_COST ( ' || L_PRORATED_COST_COUNT ||' ) .DELIVERY_DETAIL_ID: '||X_PRORATED_FREIGHT_COST ( L_PRORATED_COST_COUNT ) .DELIVERY_DETAIL_ID );
1956 WSH_DEBUG_SV.logmsg(l_module_name, 'X_PRORATED_FREIGHT_COST ( ' || L_PRORATED_COST_COUNT ||' ) .FREIGHT_COST_TYPE_CODE: '||X_PRORATED_FREIGHT_COST ( L_PRORATED_COST_COUNT ) .FREIGHT_COST_TYPE_CODE );
1957 WSH_DEBUG_SV.logmsg(l_module_name, 'X_PRORATED_FREIGHT_COST ( ' || L_PRORATED_COST_COUNT ||' ) .FREIGHT_COST_ID: '||X_PRORATED_FREIGHT_COST ( L_PRORATED_COST_COUNT ) .FREIGHT_COST_ID );
1958 WSH_DEBUG_SV.logmsg(l_module_name, 'X_PRORATED_FREIGHT_COST ( ' || L_PRORATED_COST_COUNT ||' ) .AMOUNT: '||X_PRORATED_FREIGHT_COST ( L_PRORATED_COST_COUNT ) .AMOUNT );
1959 WSH_DEBUG_SV.logmsg(l_module_name, 'X_PRORATED_FREIGHT_COST ( ' || L_PRORATED_COST_COUNT ||' ) .CURRENCY_CODE: '||X_PRORATED_FREIGHT_COST ( L_PRORATED_COST_COUNT ) .CURRENCY_CODE );
1960 WSH_DEBUG_SV.logmsg(l_module_name, 'X_PRORATED_FREIGHT_COST ( ' || L_PRORATED_COST_COUNT ||' ) .CONVERSION_TYPE_CODE: '||X_PRORATED_FREIGHT_COST ( L_PRORATED_COST_COUNT ) .CONVERSION_TYPE_CODE );
1961 WSH_DEBUG_SV.logmsg(l_module_name, 'X_PRORATED_FREIGHT_COST ( ' || L_PRORATED_COST_COUNT ||' ) .CONVERSION_RATE: '||X_PRORATED_FREIGHT_COST ( L_PRORATED_COST_COUNT ) .CONVERSION_RATE );
1962 END IF;
1963 --
1964
1965 END LOOP;
1966 -- last record
1967 l_prorated_cost_count := l_prorated_cost_count +1;
1968 l_distributed_cost := l_rest_amount;
1969 Round_Cost_Amount(l_distributed_cost, p_From_Currency_Code, l_round_distributed_cost, l_return_status);
1970 IF l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS THEN
1971 RAISE WSH_ROUND_AMOUNT_ERROR;
1972 END IF;
1973 x_Prorated_Freight_Cost(l_prorated_cost_count).delivery_detail_id := l_Cost_Breakdown(l_cost_brk_count).delivery_detail_id;
1974 x_Prorated_Freight_Cost(l_prorated_cost_count).freight_cost_type_code := p_freight_cost_type_code;
1975 x_Prorated_Freight_Cost(l_prorated_cost_count).freight_cost_id := p_freight_cost_id;
1976 x_Prorated_Freight_Cost(l_prorated_cost_count).amount := l_round_distributed_cost;
1977 x_Prorated_Freight_Cost(l_prorated_cost_count).currency_code := p_from_currency_code;
1978 x_Prorated_Freight_Cost(l_prorated_cost_count).conversion_type_code := p_conversion_type_code;
1979 x_Prorated_Freight_Cost(l_prorated_cost_count).conversion_rate := p_conversion_rate;
1980 --
1981 -- Debug Statements
1982 --
1983 IF l_debug_on THEN
1984 WSH_DEBUG_SV.logmsg(l_module_name, 'X_PRORATED_FREIGHT_COST ( ' || L_PRORATED_COST_COUNT ||' ) .DELIVERY_DETAIL_ID: '||X_PRORATED_FREIGHT_COST ( L_PRORATED_COST_COUNT ) .DELIVERY_DETAIL_ID );
1985 WSH_DEBUG_SV.logmsg(l_module_name, 'X_PRORATED_FREIGHT_COST ( ' || L_PRORATED_COST_COUNT ||' ) .FREIGHT_COST_TYPE_CODE: '||X_PRORATED_FREIGHT_COST ( L_PRORATED_COST_COUNT ) .FREIGHT_COST_TYPE_CODE );
1986 WSH_DEBUG_SV.logmsg(l_module_name, 'X_PRORATED_FREIGHT_COST ( ' || L_PRORATED_COST_COUNT ||' ) .FREIGHT_COST_ID: '||X_PRORATED_FREIGHT_COST ( L_PRORATED_COST_COUNT ) .FREIGHT_COST_ID );
1987 WSH_DEBUG_SV.logmsg(l_module_name, 'X_PRORATED_FREIGHT_COST ( ' || L_PRORATED_COST_COUNT ||' ) .AMOUNT: '||X_PRORATED_FREIGHT_COST ( L_PRORATED_COST_COUNT ) .AMOUNT );
1988 WSH_DEBUG_SV.logmsg(l_module_name, 'X_PRORATED_FREIGHT_COST ( ' || L_PRORATED_COST_COUNT ||' ) .CURRENCY_CODE: '||X_PRORATED_FREIGHT_COST ( L_PRORATED_COST_COUNT ) .CURRENCY_CODE );
1989 WSH_DEBUG_SV.logmsg(l_module_name, 'X_PRORATED_FREIGHT_COST ( ' || L_PRORATED_COST_COUNT ||' ) .CONVERSION_TYPE_CODE: '||X_PRORATED_FREIGHT_COST ( L_PRORATED_COST_COUNT ) .CONVERSION_TYPE_CODE );
1990 WSH_DEBUG_SV.logmsg(l_module_name, 'X_PRORATED_FREIGHT_COST ( ' || L_PRORATED_COST_COUNT ||' ) .CONVERSION_RATE: '||X_PRORATED_FREIGHT_COST ( L_PRORATED_COST_COUNT ) .CONVERSION_RATE );
1991 END IF;
1992 --
1993 END IF;
1994 --
1995 -- Debug Statements
1996 --
1997 IF l_debug_on THEN
1998 WSH_DEBUG_SV.logmsg(l_module_name, 'END OF CALCULATE_STOP_LEVEL_COST' );
1999 WSH_DEBUG_SV.pop(l_module_name);
2000 END IF;
2001 --
2002 EXCEPTION
2003
2004 WHEN WSH_GET_BREAKDOWN_ERR THEN
2005 --
2006 -- Debug Statements
2007 --
2008 IF l_debug_on THEN
2009 WSH_DEBUG_SV.logmsg(l_module_name, 'GET COST BREAKDOWN ERROR AT STOP ' || P_STOP_ID );
2010 END IF;
2011 --
2012 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
2013
2014 --
2015 -- Debug Statements
2016 --
2017 IF l_debug_on THEN
2018 WSH_DEBUG_SV.logmsg(l_module_name,'WSH_GET_BREAKDOWN_ERR exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
2019 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:WSH_GET_BREAKDOWN_ERR');
2020 END IF;
2021 --
2022 WHEN WSH_ROUND_AMOUNT_ERROR THEN
2023 --
2024 -- Debug Statements
2025 --
2026 IF l_debug_on THEN
2027 WSH_DEBUG_SV.logmsg(l_module_name, 'GET ROUND AMOUNT ERROR AT STOP' || P_STOP_ID );
2028 END IF;
2029 --
2030 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
2031
2032 --
2033 -- Debug Statements
2034 --
2035 IF l_debug_on THEN
2036 WSH_DEBUG_SV.logmsg(l_module_name,'WSH_ROUND_AMOUNT_ERROR exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
2037 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:WSH_ROUND_AMOUNT_ERROR');
2038 END IF;
2039 --
2040 WHEN others THEN
2041 WSH_UTIL_CORE.Default_Handler('WSH_FC_INTERFACE.Calculate_Stop_Level_Cost');
2042 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
2043
2044 --
2045 -- Debug Statements
2046 --
2047 IF l_debug_on THEN
2048 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
2049 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
2050 END IF;
2051 --
2052 END Calculate_Stop_Level_Cost;
2053
2054
2055 -- -----------------------------------------------------------------------
2056 -- PROCEDURE NAME: Calculate_Trip_Level_Cost
2057 -- This procedure evenly distributes the trip level cost to all the
2058 -- stops having pickup deliveries.
2059 -- For freight cost at all other levels, the cost
2060 -- is distributed according to weight , volume, or shipped quantity propotionally.
2061 -- ------------------------------------------------------------------------
2062 PROCEDURE Calculate_Trip_Level_Cost(
2063 p_Trip_Id IN NUMBER
2064 , p_Stop_ID IN NUMBER DEFAULT NULL
2065 , p_Delivery_ID IN NUMBER DEFAULT NULL
2066 , p_Freight_Cost_Type_Code IN VARCHAR2
2067 , p_Freight_Cost_Id IN NUMBER
2068 , p_Freight_Cost_Amount IN NUMBER
2069 , p_From_Currency_Code IN VARCHAR2
2070 , p_Conversion_Type_Code IN WSH_FREIGHT_COSTS.CONVERSION_TYPE_CODE%TYPE
2071 , p_Conversion_Rate IN WSH_FREIGHT_COSTS.CONVERSION_RATE%TYPE
2072 , p_Relevant_Info_Tab IN RelavantInfoTabType
2073 , x_Prorated_Freight_Cost IN OUT NOCOPY ProratedCostTabType
2074 , x_return_status OUT NOCOPY VARCHAR2
2075 )
2076 IS
2077
2078 CURSOR C_Sharing_Stops IS
2079 SELECT distinct st.stop_id
2080 FROM wsh_trip_stops st,
2081 wsh_new_deliveries nd,
2082 wsh_delivery_legs dl,
2083 wsh_delivery_assignments_v da,
2084 wsh_delivery_details dd
2085 WHERE
2086 st.trip_id = p_trip_id and
2087 st.stop_id = dl.pick_up_stop_id and
2088 dl.delivery_id = nd.delivery_id and
2089 st.stop_location_id = nd.initial_pickup_location_id and
2090 da.delivery_detail_id = dd.delivery_detail_id and
2091 dd.shipped_quantity > 0 and
2092 dd.container_flag = 'N' and
2093 da.delivery_id IS NOT NULL and
2094 da.delivery_id = nd.delivery_id and
2095 nvl(dd.line_direction,'O') IN ('O','IO');
2096
2097 l_number_of_stops NUMBER := 0;
2098 i NUMBER := 0;
2099 l_distributed_cost NUMBER := 0;
2100 l_stop_id NUMBER := 0;
2101 l_cost_per_stop NUMBER := 0;
2102 l_round_cost_per_stop NUMBER := 0;
2103 l_remained_cost NUMBER := 0;
2104 l_Cost_Breakdown CostBreakdownTabType;
2105 l_return_status VARCHAR2(10) := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
2106
2107
2108 WSH_ROUND_AMOUNT_ERROR EXCEPTION;
2109
2110
2111 --
2112 l_debug_on BOOLEAN;
2113 --
2114 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'CALCULATE_TRIP_LEVEL_COST';
2115 --
2116 BEGIN
2117
2118 --
2119 -- Debug Statements
2120 --
2121 --
2122 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
2123 --
2124 IF l_debug_on IS NULL
2125 THEN
2126 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
2127 END IF;
2128 --
2129 IF l_debug_on THEN
2130 WSH_DEBUG_SV.push(l_module_name);
2131 --
2132 WSH_DEBUG_SV.log(l_module_name,'P_TRIP_ID',P_TRIP_ID);
2133 WSH_DEBUG_SV.log(l_module_name,'P_STOP_ID',P_STOP_ID);
2134 WSH_DEBUG_SV.log(l_module_name,'P_DELIVERY_ID',P_DELIVERY_ID);
2135 WSH_DEBUG_SV.log(l_module_name,'P_FREIGHT_COST_TYPE_CODE',P_FREIGHT_COST_TYPE_CODE);
2136 WSH_DEBUG_SV.log(l_module_name,'P_FREIGHT_COST_ID',P_FREIGHT_COST_ID);
2137 WSH_DEBUG_SV.log(l_module_name,'P_FREIGHT_COST_AMOUNT',P_FREIGHT_COST_AMOUNT);
2138 WSH_DEBUG_SV.log(l_module_name,'P_FROM_CURRENCY_CODE',P_FROM_CURRENCY_CODE);
2139 WSH_DEBUG_SV.log(l_module_name,'P_CONVERSION_TYPE_CODE',P_CONVERSION_TYPE_CODE);
2140 WSH_DEBUG_SV.log(l_module_name,'P_CONVERSION_RATE',P_CONVERSION_RATE);
2141
2142
2143 WSH_DEBUG_SV.log(l_module_name,'P_RELEVANT_INFO_TAB.COUNT',P_RELEVANT_INFO_TAB.count);
2144 END IF;
2145 --
2146 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
2147 --
2148 -- Debug Statements
2149 --
2150 IF l_debug_on THEN
2151 WSH_DEBUG_SV.logmsg(l_module_name, 'BEGINNING OF CALCULATE TRIP LEVEL COST:' || P_TRIP_ID || ' FREIGHT COST ID: '|| P_FREIGHT_COST_ID );
2152 END IF;
2153 --
2154 /* get number of stops with pickup deliveries in this trip */
2155 select count(distinct st.stop_id) into l_number_of_stops
2156 from wsh_trip_stops st,
2157 wsh_new_deliveries nd,
2158 wsh_delivery_legs dl,
2159 wsh_delivery_assignments_v da,
2160 wsh_delivery_details dd
2161 where
2162 st.trip_id = p_trip_id and
2163 st.stop_id = dl.pick_up_stop_id and
2164 dl.delivery_id = nd.delivery_id and
2165 st.stop_location_id = nd.initial_pickup_location_id and
2166 da.delivery_detail_id = dd.delivery_detail_id and
2167 dd.shipped_quantity > 0 and
2168 dd.container_flag = 'N' and
2169 da.delivery_id IS NOT NULL and
2170 da.delivery_id = nd.delivery_id and
2171 nvl(dd.line_direction,'O') IN ('O','IO');
2172
2173 --
2174 -- Debug Statements
2175 --
2176 IF l_debug_on THEN
2177 WSH_DEBUG_SV.logmsg(l_module_name, 'NUMBER OF STOPS WITH FREIGHT COST IN TRIP '||P_TRIP_ID || ' IS: ' || L_NUMBER_OF_STOPS );
2178 END IF;
2179 --
2180 IF l_number_of_stops > 0 THEN
2181
2182 l_cost_per_stop := p_Freight_Cost_Amount/ l_number_of_stops;
2183 Round_Cost_Amount(l_cost_per_stop, p_From_Currency_Code, l_round_cost_per_stop, l_return_status);
2184 IF l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS THEN
2185 RAISE WSH_ROUND_AMOUNT_ERROR;
2186 END IF;
2187
2188 OPEN C_Sharing_Stops;
2189 i := 0;
2190 l_distributed_cost := 0;
2191
2192 LOOP
2193 FETCH C_Sharing_Stops into l_stop_id;
2194 EXIT WHEN C_Sharing_Stops%NOTFOUND;
2195 i := i + 1;
2196 IF i < l_number_of_stops THEN
2197 Calculate_Stop_Level_Cost(
2198 p_Stop_Id => l_stop_id
2199 , p_Freight_Cost_Type_Code => p_Freight_Cost_Type_Code
2200 , p_Freight_Cost_Id => p_Freight_cost_id
2201 , p_Freight_Cost_Amount => l_round_cost_per_stop
2202 , p_From_Currency_Code => p_From_Currency_Code
2203 , p_conversion_type_code => p_conversion_type_code
2204 , p_conversion_rate => p_conversion_rate
2205 , p_Relevant_Info_Tab => p_Relevant_Info_Tab
2206 , x_Prorated_Freight_Cost => x_Prorated_Freight_Cost
2207 , x_return_status => x_return_status
2208 );
2209 l_distributed_cost := l_distributed_cost + l_round_cost_per_stop;
2210 ELSE
2211 -- last stop in the trip
2212 l_remained_cost := p_Freight_Cost_Amount - l_distributed_cost;
2213 Calculate_Stop_Level_Cost(
2214 p_Stop_Id => l_stop_id
2215 , p_Freight_Cost_Type_Code => p_Freight_Cost_Type_Code
2216 , p_Freight_Cost_Id => p_Freight_cost_id
2217 , p_Freight_Cost_Amount => l_remained_cost
2218 , p_From_Currency_Code => p_From_Currency_Code
2219 , p_conversion_type_code => p_conversion_type_code
2220 , p_conversion_rate => p_conversion_rate
2221 , p_Relevant_Info_Tab => p_Relevant_Info_Tab
2222 , x_Prorated_Freight_Cost => x_Prorated_Freight_Cost
2223 , x_return_status => x_return_status
2224 );
2225 END IF;
2226 END LOOP;
2227
2228 CLOSE C_Sharing_Stops;
2229 END IF;
2230
2231 --
2232 -- Debug Statements
2233 --
2234 IF l_debug_on THEN
2235 WSH_DEBUG_SV.logmsg(l_module_name, 'END OF CALCULATE TRIP LEVEL COST' );
2236 WSH_DEBUG_SV.pop(l_module_name);
2237 END IF;
2238 --
2239 EXCEPTION
2240
2241 WHEN WSH_ROUND_AMOUNT_ERROR THEN
2242 --
2243 -- Debug Statements
2244 --
2245 IF l_debug_on THEN
2246 WSH_DEBUG_SV.logmsg(l_module_name, 'GET ROUND AMOUNT ERROR AT TRIP ' || P_TRIP_ID );
2247 END IF;
2248 --
2249 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
2250
2251 --
2252 -- Debug Statements
2253 --
2254 IF l_debug_on THEN
2255 WSH_DEBUG_SV.logmsg(l_module_name,'WSH_ROUND_AMOUNT_ERROR exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
2256 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:WSH_ROUND_AMOUNT_ERROR');
2257 END IF;
2258 --
2259 WHEN others THEN
2260 WSH_UTIL_CORE.Default_Handler('WSH_FC_INTERFACE.Calculate_Trip_Level_Cost');
2261 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
2262
2263 --
2264 -- Debug Statements
2265 --
2266 IF l_debug_on THEN
2267 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
2268 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
2269 END IF;
2270 --
2271 END Calculate_Trip_Level_Cost;
2272
2273 -- -----------------------------------------------------------------------------------------
2274 -- PROCEDURE NAME: Round_Cost_Amount
2275 -- ------------------------------------------------------------------------------------------
2276 PROCEDURE Round_Cost_Amount(
2277 p_Amount IN NUMBER
2278 , p_Currency_Code IN VARCHAR2
2279 , x_Round_Amount OUT NOCOPY NUMBER
2280 , x_return_status OUT NOCOPY VARCHAR2
2281 )
2282 IS
2283
2284 l_precision NUMBER;
2285 l_minimum_accountable_unit NUMBER;
2286
2287 WSH_NO_CURRENCY EXCEPTION;
2288
2289 --
2290 l_debug_on BOOLEAN;
2291 --
2292 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'ROUND_COST_AMOUNT';
2293 --
2294 BEGIN
2295
2296 --
2297 -- Debug Statements
2298 --
2299 --
2300 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
2301 --
2302 IF l_debug_on IS NULL
2303 THEN
2304 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
2305 END IF;
2306 --
2307 IF l_debug_on THEN
2308 WSH_DEBUG_SV.push(l_module_name);
2309 --
2310 WSH_DEBUG_SV.log(l_module_name,'P_AMOUNT',P_AMOUNT);
2311 WSH_DEBUG_SV.log(l_module_name,'P_CURRENCY_CODE',P_CURRENCY_CODE);
2312 END IF;
2313 --
2314 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
2315 SELECT precision, nvl(minimum_accountable_unit, 0)
2316 INTO l_precision, l_minimum_accountable_unit
2317 FROM fnd_currencies
2318 WHERE currency_code = p_Currency_Code;
2319 IF SQL%NOTFOUND THEN
2320 RAISE WSH_NO_CURRENCY;
2321 END IF;
2322
2323 x_Round_Amount := Round(p_Amount, l_precision);
2324
2325
2326 --
2327 -- Debug Statements
2328 --
2329 IF l_debug_on THEN
2330 WSH_DEBUG_SV.log(l_module_name,'x_round_amount',x_round_amount);
2331 WSH_DEBUG_SV.pop(l_module_name);
2332 END IF;
2333 --
2334 EXCEPTION
2335
2336 WHEN WSH_NO_CURRENCY THEN
2337 --
2338 -- Debug Statements
2339 --
2340 IF l_debug_on THEN
2341 WSH_DEBUG_SV.logmsg(l_module_name, 'THE FREIGHT COST CURRENCY IS NOT RECOGNIZED IN THE SYSTEM' );
2342 END IF;
2343 --
2344 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
2345
2346 --
2347 -- Debug Statements
2348 --
2349 IF l_debug_on THEN
2350 WSH_DEBUG_SV.logmsg(l_module_name,'WSH_NO_CURRENCY exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
2351 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:WSH_NO_CURRENCY');
2352 END IF;
2353 --
2354 WHEN others THEN
2355 WSH_UTIL_CORE.Default_Handler('WSH_FC_INTERFACE.Round_Cost_Amount');
2356 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
2357
2358 --
2359 -- Debug Statements
2360 --
2361 IF l_debug_on THEN
2362 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
2363 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
2364 END IF;
2365 --
2366 END Round_Cost_Amount;
2367
2368
2369 -- -----------------------------------------------------------------------------------------
2370 -- PROCEDURE NAME: Source_Line_Level_Cost
2371 -- ------------------------------------------------------------------------------------------
2372 PROCEDURE Source_Line_Level_Cost(
2373 p_stop_id IN NUMBER
2374 , p_prorated_freight_cost IN ProratedCostTabType
2375 , x_Final_Cost IN OUT NOCOPY OMInterfaceCostTabType
2376 , x_return_status OUT NOCOPY VARCHAR2
2377 )
2378
2379 IS
2380
2381
2382 CURSOR C_OE_Curr_Info (c_delivery_detail_id NUMBER)
2383 IS
2384 SELECT h.transactional_curr_code,
2385 h.conversion_type_code,
2386 h.conversion_rate_date,
2387 h.conversion_rate,
2388 l.line_id,
2389 l.header_id
2390 FROM oe_order_headers_all h, oe_order_lines_all l, wsh_delivery_details wdd
2391 WHERE wdd.delivery_detail_id = c_delivery_detail_id AND
2392 wdd.source_line_id = l.line_id AND
2393 wdd.source_code = 'OE' and
2394 l.header_id = h.header_id;
2395
2396 l_OE_Curr_Rec C_OE_Curr_Info%ROWTYPE;
2397
2398
2399 CURSOR C_ACTUAL_DEPARTURE_DATE IS
2400 SELECT actual_departure_date
2401 FROM WSH_TRIP_STOPS
2402 WHERE stop_id = p_stop_id;
2403
2404
2405 i NUMBER := 0;
2406 j NUMBER := 0;
2407 l_source_line_id NUMBER := 0;
2408 l_next_tab_id NUMBER := 0;
2409 l_amount NUMBER := 0;
2410 l_line_currency_code VARCHAR2(15) := NULL;
2411 l_conversion_date DATE;
2412 l_conversion_type VARCHAR2(30) := NULL;
2413
2414 l_new_source_line_id BOOLEAN := TRUE;
2415 l_max_roll_days NUMBER := 0;
2416 l_actual_departure_date DATE := NULL;
2417 l_converted_amount NUMBER := 0;
2418 l_denominator NUMBER := 0;
2419 l_numerator NUMBER := 0;
2420 l_rate NUMBER := 0;
2421 l_rate_exists VARCHAR2(1) := 'N' ;
2422 oe_currency_not_exist EXCEPTION;
2423 no_user_defined_rate EXCEPTION;
2424 WSH_NO_ACTUALL_DEPARTURE_DATE EXCEPTION;
2425 WSH_STOP_NOT_FOUND EXCEPTION;
2426
2427 --
2428 l_debug_on BOOLEAN;
2429 --
2430 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'SOURCE_LINE_LEVEL_COST';
2431 --
2432 BEGIN
2433 --
2434 -- Debug Statements
2435 --
2436 --
2437 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
2438 --
2439 IF l_debug_on IS NULL
2440 THEN
2441 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
2442 END IF;
2443 --
2444 IF l_debug_on THEN
2445 WSH_DEBUG_SV.push(l_module_name);
2446 --
2447 WSH_DEBUG_SV.log(l_module_name,'P_STOP_ID',P_STOP_ID);
2448 WSH_DEBUG_SV.log(l_module_name,'p_prorated_freight_cost.COUNT',p_prorated_freight_cost.COUNT);
2449 /*
2450 WSH_DEBUG_SV.log(l_module_name,'p_prorated_freight_cost.delivery_detail_id',p_prorated_freight_cost.delivery_detail_id);
2451 WSH_DEBUG_SV.log(l_module_name,'p_prorated_freight_cost.freight_cost_type_code',p_prorated_freight_cost.freight_cost_type_code);
2452 WSH_DEBUG_SV.log(l_module_name,'p_prorated_freight_cost.freight_cost_id',p_prorated_freight_cost.freight_cost_id);
2453 WSH_DEBUG_SV.log(l_module_name,'p_prorated_freight_cost.amount',p_prorated_freight_cost.amount);
2454 WSH_DEBUG_SV.log(l_module_name,'p_prorated_freight_cost.currency_code',p_prorated_freight_cost.currency_code);
2455 WSH_DEBUG_SV.log(l_module_name,'p_prorated_freight_cost.conversion_type_code',p_prorated_freight_cost.conversion_type_code);
2456 WSH_DEBUG_SV.log(l_module_name,'p_prorated_freight_cost.conversion_rate',p_prorated_freight_cost.conversion_rate);
2457
2458 */
2459 END IF;
2460 --
2461 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
2462 --
2463 -- Debug Statements
2464 --
2465 IF l_debug_on THEN
2466 WSH_DEBUG_SV.logmsg(l_module_name, 'BEGINNING OF SOURCE_LINE_LEVEL_COST , STOP_ID: '|| P_STOP_ID );
2467 END IF;
2468 --
2469 l_next_tab_id := x_Final_Cost.COUNT + 1;
2470 <<Outer_LOOP>>
2471 FOR i IN 1 .. p_prorated_freight_cost.count
2472
2473 LOOP
2474
2475 OPEN C_OE_Curr_Info(p_prorated_freight_cost(i).delivery_detail_id);
2476 FETCH C_OE_Curr_Info INTO l_OE_Curr_Rec;
2477 IF (C_OE_Curr_Info%NOTFOUND) THEN
2478 --
2479 -- Debug Statements
2480 --
2481 IF l_debug_on THEN
2482 WSH_DEBUG_SV.logmsg(l_module_name, 'C_OE_CURR_INFO DOES NOT FETCH A RECORD' );
2483 END IF;
2484 --
2485 END IF;
2486 CLOSE C_OE_Curr_Info;
2487
2488 IF (l_oe_curr_rec.transactional_curr_code IS NULL) THEN
2489 RAISE oe_Currency_Not_Exist;
2490 ELSE
2491 l_line_currency_code := l_OE_Curr_Rec.transactional_curr_code;
2492 END IF;
2493
2494 IF l_debug_on THEN
2495 WSH_DEBUG_SV.log(l_module_name, 'l_OE_Curr_Rec.conversion_type_code',l_OE_Curr_Rec.conversion_type_code );
2496 WSH_DEBUG_SV.log(l_module_name, 'l_OE_Curr_Rec.conversion_rate_date', to_char(l_OE_Curr_Rec.conversion_rate_date, 'DD-MON-YY HH24:MI:SS'));
2497 WSH_DEBUG_SV.log(l_module_name, 'l_OE_Curr_Rec.conversion_rate', to_char(l_OE_Curr_Rec.conversion_rate));
2498 END IF;
2499 -- bug 3455978
2500 l_conversion_type := NVL(l_OE_Curr_Rec.conversion_type_code, 'Corporate');
2501
2502 IF (l_oe_curr_rec.conversion_rate_date IS NOT NULL) THEN
2503 l_conversion_date := l_oe_curr_rec.conversion_rate_date;
2504 ELSE
2505 IF l_actual_departure_date is NULL THEN
2506 OPEN C_ACTUAL_DEPARTURE_DATE;
2507 FETCH C_ACTUAL_DEPARTURE_DATE INTO l_actual_departure_date;
2508 IF C_ACTUAL_DEPARTURE_DATE%NOTFOUND THEN
2509 CLOSE C_ACTUAL_DEPARTURE_DATE;
2510 raise WSH_STOP_NOT_FOUND;
2511 END IF;
2512 CLOSE C_ACTUAL_DEPARTURE_DATE;
2513 IF l_actual_departure_date IS NULL THEN
2514 raise WSH_NO_ACTUALL_DEPARTURE_DATE;
2515 END IF;
2516 END IF;
2517 l_conversion_date := l_actual_departure_date;
2518 END IF;
2519
2520 IF l_debug_on THEN
2521 WSH_DEBUG_SV.log(l_module_name,'l_conversion_type', l_conversion_type);
2522 WSH_DEBUG_SV.log(l_module_name,'l_OE_Curr_Rec.conversion_rate', to_char(l_OE_Curr_Rec.conversion_rate));
2523 WSH_DEBUG_SV.log(l_module_name,'l_conversion_date', to_char(l_conversion_date, 'DD-MON-YY HH24:MI:SS'));
2524 END IF;
2525
2526 l_max_roll_days := 300;
2527
2528 l_new_source_line_id := TRUE;
2529
2530 <<Inner_Loop>>
2531 -- merge the entries which share the same source_line_id and freight_cost_id
2532 FOR j in 1 .. x_Final_Cost.COUNT
2533 LOOP
2534
2535 IF (x_Final_Cost(j).source_line_id = l_OE_Curr_Rec.line_id AND
2536 x_Final_Cost(j).freight_cost_id = p_prorated_freight_cost(i).freight_cost_id) THEN
2537
2538 --
2539 -- Debug Statements
2540 --
2541 IF l_debug_on THEN
2542 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit GL_CURRENCY_API.IS_FIXED_RATE',WSH_DEBUG_SV.C_PROC_LEVEL);
2543 END IF;
2544 --
2545 IF (GL_CURRENCY_API.Is_Fixed_Rate(p_prorated_freight_cost(i).currency_code, l_line_currency_code, l_conversion_date) = 'Y') THEN
2546 --
2547 -- Debug Statements
2548 --
2549 IF l_debug_on THEN
2550 WSH_DEBUG_SV.logmsg(l_module_name, 'ADDING P_PRORATED_FREIGHT_COST ( ' || I ||' ) TO X_FINAL_COST ( '||J||' ) USING FIXED RATE' );
2551 END IF;
2552 --
2553 --
2554 -- Debug Statements
2555 --
2556 IF l_debug_on THEN
2557 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit GL_CURRENCY_API.CONVERT_AMOUNT',WSH_DEBUG_SV.C_PROC_LEVEL);
2558 END IF;
2559 --
2560 x_Final_Cost(j).amount := x_Final_Cost(j).amount + GL_CURRENCY_API.convert_amount(
2561 p_prorated_freight_cost(i).currency_code, l_line_currency_code, l_conversion_date, NVL(p_prorated_freight_cost(i).conversion_type_code,l_conversion_type), p_prorated_freight_cost(i).amount);
2562 -- bug 3455978 use the conversion_type_code from wsh_freight_costs
2563 ELSIF (p_prorated_freight_cost(i).conversion_type_code = 'User') THEN
2564 IF (p_prorated_freight_cost(i).conversion_rate IS NOT NULL) THEN
2565 --
2566 -- Debug Statements
2567 --
2568 IF l_debug_on THEN
2569 WSH_DEBUG_SV.logmsg(l_module_name, 'ADDING P_PRORATED_FREIGHT_COST ( ' || I ||' ) TO X_FINAL_COST ( '||J||' ) USING THE RATE OF THE FREIGHT COST, CONVERSION RATE:' ||to_char(p_prorated_freight_cost(i).conversion_rate ));
2570 END IF;
2571 --
2572 x_Final_Cost(j).amount := x_Final_Cost(j).amount + p_prorated_freight_cost(i).amount * p_prorated_freight_cost(i).conversion_rate;
2573
2574 ELSE
2575 --
2576 -- Debug Statements
2577 --
2578 IF l_debug_on THEN
2579 WSH_DEBUG_SV.logmsg(l_module_name, 'CONVERSION_TYPE IS USER BUT NO CONVERSION_RATE DEFINED IN FREIGHT COST , PROCESS_FREIGHT_COST FAILED' );
2580 END IF;
2581 --
2582 RAISE No_User_Defined_Rate;
2583 END IF;
2584 ELSIF (l_conversion_type = 'User') THEN
2585 IF (l_OE_Curr_Rec.conversion_rate IS NOT NULL) THEN
2586 --
2587 -- Debug Statements
2588 --
2589 IF l_debug_on THEN
2590 WSH_DEBUG_SV.logmsg(l_module_name, 'ADDING P_PRORATED_FREIGHT_COST ( ' || I ||' ) TO X_FINAL_COST ( '||J||' ) USING USER SPCIFIED RATE , CONVERSION RATE:' ||L_OE_CURR_REC.CONVERSION_RATE );
2591 END IF;
2592 --
2593 x_Final_Cost(j).amount := x_Final_Cost(j).amount + p_prorated_freight_cost(i).amount * l_oe_curr_rec.conversion_rate;
2594 ELSE
2595 --
2596 -- Debug Statements
2597 --
2598 IF l_debug_on THEN
2599 WSH_DEBUG_SV.logmsg(l_module_name, 'CONVERSION_TYPE IS USER BUT NO CONVERSION_RATE DEFINED IN OM PART EITHER , PROCESS_FREIGHT_COST FAILED' );
2600 END IF;
2601 --
2602 RAISE No_User_Defined_Rate;
2603 END IF;
2604 ELSE
2605
2606 --
2607 -- Debug Statements
2608 --
2609 IF l_debug_on THEN
2610 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit GL_CURRENCY_API.RATE_EXISTS',WSH_DEBUG_SV.C_PROC_LEVEL);
2611 END IF;
2612 --
2613 l_rate_exists := GL_CURRENCY_API.Rate_Exists(
2614 x_from_currency => p_prorated_freight_cost(i).currency_code,
2615 x_to_currency => l_line_currency_code,
2616 x_conversion_date => l_conversion_date,
2617 x_conversion_type => NVL(p_prorated_freight_cost(i).conversion_type_code,l_conversion_type)
2618 );
2619 IF (l_rate_exists = 'Y') THEN
2620 --
2621 -- Debug Statements
2622 --
2623 IF l_debug_on THEN
2624 WSH_DEBUG_SV.logmsg(l_module_name, 'ADDING P_PRORATED_FREIGHT_COST ( ' || I ||' ) TO X_FINAL_COST ( '||J||' ) USING FLOATING RATE DECIDED BY CONVERSION DATE' || TO_CHAR ( L_CONVERSION_DATE, 'DD-MON-YY HH24:MI:SS' ) );
2625 END IF;
2626 --
2627 --
2628 -- Debug Statements
2629 --
2630 IF l_debug_on THEN
2631 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit GL_CURRENCY_API.CONVERT_AMOUNT',WSH_DEBUG_SV.C_PROC_LEVEL);
2632 END IF;
2633 --
2634 -- bug 4135443
2635 x_Final_Cost(j).amount := x_Final_Cost(j).amount +
2636 GL_CURRENCY_API.convert_amount(p_prorated_freight_cost(i).currency_code,
2637 l_line_currency_code,
2638 l_conversion_date,
2639 NVL(p_prorated_freight_cost(i).conversion_type_code,l_conversion_type),
2640 p_prorated_freight_cost(i).amount);
2641 ELSE
2642 --
2643 -- Debug Statements
2644 --
2645 IF l_debug_on THEN
2646 WSH_DEBUG_SV.logmsg(l_module_name, 'ADDING P_PRORATED_FREIGHT_COST ( ' || I ||' ) TO X_FINAL_COST ( '||J||' ) , NO RATE EXISTS , CONVERT TO CLOSEST AMOUNT , CONVERSION DATE:'|| TO_CHAR ( L_CONVERSION_DATE, 'DD-MON-YY HH24:MI:SS') );
2647 END IF;
2648 --
2649 --
2650 -- Debug Statements
2651 --
2652 IF l_debug_on THEN
2653 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit GL_CURRENCY_API.CONVERT_CLOSEST_AMOUNT',WSH_DEBUG_SV.C_PROC_LEVEL);
2654 END IF;
2655 --
2656 GL_CURRENCY_API.convert_closest_amount(
2657 x_from_currency => p_prorated_freight_cost(i).currency_code,
2658 x_to_currency => l_line_currency_code,
2659 x_conversion_date => l_conversion_date,
2660 x_conversion_type => NVL(p_prorated_freight_cost(i).conversion_type_code,l_conversion_type),
2661 x_user_rate => l_OE_Curr_Rec.conversion_rate,
2662 x_amount => p_prorated_freight_cost(i).amount,
2663 x_max_roll_days => l_max_roll_days,
2664 x_converted_amount=> l_converted_amount,
2665 x_denominator => l_denominator,
2666 x_numerator => l_numerator,
2667 x_rate => l_rate);
2668 x_Final_Cost(j).amount := x_Final_Cost(j).amount + l_converted_amount;
2669 END IF;
2670 END IF;
2671 l_new_source_line_id := FALSE;
2672 END IF;
2673 END LOOP Inner_Loop;
2674
2675 IF (l_new_source_line_id = TRUE) THEN
2676 l_next_tab_id := x_Final_Cost.COUNT + 1;
2677
2678 --
2679 -- Debug Statements
2680 --
2681 IF l_debug_on THEN
2682 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit GL_CURRENCY_API.IS_FIXED_RATE',WSH_DEBUG_SV.C_PROC_LEVEL);
2683 END IF;
2684 --
2685 IF (GL_CURRENCY_API.Is_Fixed_Rate(p_prorated_freight_cost(i).currency_code, l_line_currency_code, l_conversion_date) = 'Y') THEN
2686 --
2687 -- Debug Statements
2688 --
2689 IF l_debug_on THEN
2690 WSH_DEBUG_SV.logmsg(l_module_name, 'SET P_PRORATED_FREIGHT_COST ( ' || I ||' ) TO X_FINAL_COST ( '||L_NEXT_TAB_ID||' ) USING FIXED RATE , CONVERSION DATE:' || TO_CHAR ( L_CONVERSION_DATE,'DD-MON-YY HH24:MI:SS' ) );
2691 END IF;
2692 --
2693 --
2694 -- Debug Statements
2695 --
2696 IF l_debug_on THEN
2697 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit GL_CURRENCY_API.CONVERT_AMOUNT',WSH_DEBUG_SV.C_PROC_LEVEL);
2698 END IF;
2699 --
2700 x_Final_Cost(l_next_tab_id).amount :=
2701 GL_CURRENCY_API.convert_amount(
2702 p_prorated_freight_cost(i).currency_code,
2703 l_line_currency_code,
2704 l_conversion_date,
2705 NVL(p_prorated_freight_cost(i).conversion_type_code,l_conversion_type),
2706 p_prorated_freight_cost(i).amount);
2707 -- bug 3455978 use the conversion_type_code from wsh_freight_costs
2708 ELSIF (p_prorated_freight_cost(i).conversion_type_code = 'User') THEN
2709 IF (p_prorated_freight_cost(i).conversion_rate IS NOT NULL) THEN
2710 --
2711 -- Debug Statements
2712 --
2713 IF l_debug_on THEN
2714 WSH_DEBUG_SV.logmsg(l_module_name, 'SET P_PRORATED_FREIGHT_COST ( ' || I ||' ) TO X_FINAL_COST ( '||L_NEXT_TAB_ID||' ) USING THE RATE OF THE FREIGHT COST , CONVERSION RATE:' ||to_char(p_prorated_freight_cost(i).conversion_rate) );
2715 END IF;
2716 --
2717 x_Final_Cost(l_next_tab_id).amount := p_prorated_freight_cost(i).amount * p_prorated_freight_cost(i).conversion_rate;
2718 ELSE
2719 --
2720 -- Debug Statements
2721 --
2722 IF l_debug_on THEN
2723 WSH_DEBUG_SV.logmsg(l_module_name, 'CONVERSION_TYPE IS USER BUT NO CONVERSION_RATE DEFINED IN FREIGHT COST EITHER , PROCESS_FREIGHT_COST FAILED' );
2724 END IF;
2725 --
2726 RAISE No_User_Defined_Rate;
2727 END IF;
2728 ELSIF (l_conversion_type = 'User') THEN
2729 IF (l_OE_Curr_Rec.conversion_rate IS NOT NULL) THEN
2730 --
2731 -- Debug Statements
2732 --
2733 IF l_debug_on THEN
2734 WSH_DEBUG_SV.logmsg(l_module_name, 'SET P_PRORATED_FREIGHT_COST ( ' || I ||' ) TO X_FINAL_COST ( '||L_NEXT_TAB_ID||' ) USING USER SPCIFIED RATE , CONVERSION RATE:' ||L_OE_CURR_REC.CONVERSION_RATE );
2735 END IF;
2736 --
2737 x_Final_Cost(l_next_tab_id).amount := p_prorated_freight_cost(i).amount * l_oe_curr_rec.conversion_rate;
2738 ELSE
2739 --
2740 -- Debug Statements
2741 --
2742 IF l_debug_on THEN
2743 WSH_DEBUG_SV.logmsg(l_module_name, 'CONVERSION_TYPE IS USER BUT NO CONVERSION_RATE DEFINED IN OM PART EITHER , PROCESS_FREIGHT_COST FAILED' );
2744 END IF;
2745 --
2746 RAISE No_User_Defined_Rate;
2747 END IF;
2748 ELSE
2749 --
2750 -- Debug Statements
2751 --
2752 IF l_debug_on THEN
2753 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit GL_CURRENCY_API.RATE_EXISTS',WSH_DEBUG_SV.C_PROC_LEVEL);
2754 END IF;
2755 --
2756 l_rate_exists := GL_CURRENCY_API.Rate_Exists(
2757 x_from_currency => p_prorated_freight_cost(i).currency_code,
2758 x_to_currency => l_line_currency_code,
2759 x_conversion_date => l_conversion_date,
2760 x_conversion_type => NVL(p_prorated_freight_cost(i).conversion_type_code,l_conversion_type)
2761 );
2762 IF (l_rate_exists = 'Y') THEN
2763 --
2764 -- Debug Statements
2765 --
2766 IF l_debug_on THEN
2767 WSH_DEBUG_SV.logmsg(l_module_name, 'SET P_PRORATED_FREIGHT_COST ( '|| I ||' ) TO X_FINAL_COST ( '||L_NEXT_TAB_ID||' ) USING FLOATING RATE DECIDED BY CONVERSION DATE:' || TO_CHAR ( L_CONVERSION_DATE, 'DD-MON-YY HH24:MI:SS' ) );
2768 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit GL_CURRENCY_API.CONVERT_AMOUNT',WSH_DEBUG_SV.C_PROC_LEVEL);
2769 END IF;
2770 --
2771 -- bug 4135443
2772 x_Final_Cost(l_next_tab_id).amount := GL_CURRENCY_API.convert_amount (
2773 p_prorated_freight_cost(i).currency_code, l_line_currency_code,
2774 l_conversion_date,
2775 NVL(p_prorated_freight_cost(i).conversion_type_code,l_conversion_type),
2776 p_prorated_freight_cost(i).amount);
2777 ELSE
2778 --
2779 -- Debug Statements
2780 --
2781 IF l_debug_on THEN
2782 WSH_DEBUG_SV.logmsg(l_module_name, 'SET P_PRORATED_FREIGHT_COST ( ' || I ||' ) TO X_FINAL_COST ( '||L_NEXT_TAB_ID||' ) , NO RATE EXISTS , CONVERT TO CLOSEST AMOUNT , CONVERSION DATE:'||
2783 TO_CHAR ( L_CONVERSION_DATE , 'DD-MON-YY HH24:MI:SS') );
2784 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit GL_CURRENCY_API.CONVERT_CLOSEST_AMOUNT',WSH_DEBUG_SV.C_PROC_LEVEL);
2785 END IF;
2786 --
2787 --
2788 GL_CURRENCY_API.convert_closest_amount(
2789 x_from_currency => p_prorated_freight_cost(i).currency_code,
2790 x_to_currency => l_line_currency_code,
2791 x_conversion_date => l_conversion_date,
2792 x_conversion_type => NVL(p_prorated_freight_cost(i).conversion_type_code,l_conversion_type),
2793 x_user_rate => l_OE_Curr_Rec.conversion_rate,
2794 x_amount => p_prorated_freight_cost(i).amount,
2795 x_max_roll_days => l_max_roll_days,
2796 x_converted_amount=> l_converted_amount,
2797 x_denominator => l_denominator,
2798 x_numerator => l_numerator,
2799 x_rate => l_rate);
2800 x_Final_Cost(l_next_tab_id).amount := l_converted_amount;
2801 END IF;
2802 END IF;
2803 x_Final_Cost(l_next_tab_id).freight_cost_type_code := p_prorated_freight_cost(i).freight_cost_type_code;
2804 x_Final_Cost(l_next_tab_id).freight_cost_id := p_prorated_freight_cost(i).freight_cost_id;
2805 x_Final_Cost(l_next_tab_id).source_line_id := l_OE_Curr_Rec.line_id;
2806 x_Final_Cost(l_next_tab_id).source_header_id := l_OE_Curr_Rec.header_id;
2807 x_Final_Cost(l_next_tab_id).currency_code := l_line_currency_code;
2808 --
2809 -- Debug Statements
2810 --
2811 IF l_debug_on THEN
2812 WSH_DEBUG_SV.logmsg(l_module_name, 'X_FINAL_COST ( ' || L_NEXT_TAB_ID || ' ) .FREIGHT_COST_TYPE_CODE: '|| X_FINAL_COST ( L_NEXT_TAB_ID ) .FREIGHT_COST_TYPE_CODE );
2813 WSH_DEBUG_SV.logmsg(l_module_name, 'X_FINAL_COST ( ' || L_NEXT_TAB_ID || ' ) .FREIGHT_COST_ID: '|| X_FINAL_COST ( L_NEXT_TAB_ID ) .FREIGHT_COST_ID );
2814 WSH_DEBUG_SV.logmsg(l_module_name, 'X_FINAL_COST ( ' || L_NEXT_TAB_ID || ' ) .SOURCE_LINE_ID: '|| X_FINAL_COST ( L_NEXT_TAB_ID ) .SOURCE_LINE_ID );
2815 WSH_DEBUG_SV.logmsg(l_module_name, 'X_FINAL_COST ( ' || L_NEXT_TAB_ID || ' ) .SOURCE_HEADER_ID: '|| X_FINAL_COST ( L_NEXT_TAB_ID ) .SOURCE_HEADER_ID );
2816 WSH_DEBUG_SV.logmsg(l_module_name, 'X_FINAL_COST ( ' || L_NEXT_TAB_ID || ' ) .CURRENCY_CODE: '|| X_FINAL_COST ( L_NEXT_TAB_ID ) .CURRENCY_CODE );
2817 WSH_DEBUG_SV.logmsg(l_module_name, 'X_FINAL_COST ( ' || L_NEXT_TAB_ID || ' ) .AMOUNT: '|| X_FINAL_COST ( L_NEXT_TAB_ID ) .AMOUNT );
2818 END IF;
2819 --
2820 END IF;
2821 END LOOP Outer_Loop;
2822 --
2823 -- Debug Statements
2824 --
2825 IF l_debug_on THEN
2826 WSH_DEBUG_SV.logmsg(l_module_name, 'END OF SOURCE_LINE_LEVEL_COST , STOP_ID: '|| P_STOP_ID );
2827 WSH_DEBUG_SV.pop(l_module_name);
2828 END IF;
2829 --
2830 EXCEPTION
2831 WHEN WSH_STOP_NOT_FOUND THEN
2832 --
2833 -- Debug Statements
2834 --
2835 IF l_debug_on THEN
2836 WSH_DEBUG_SV.logmsg(l_module_name, 'STOP '|| P_STOP_ID || ' NOT FOUND' );
2837 END IF;
2838 --
2839 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
2840 --
2841 -- Debug Statements
2842 --
2843 IF l_debug_on THEN
2844 WSH_DEBUG_SV.logmsg(l_module_name,'WSH_STOP_NOT_FOUND exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
2845 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:WSH_STOP_NOT_FOUND');
2846 END IF;
2847 --
2848 WHEN WSH_NO_ACTUALL_DEPARTURE_DATE THEN
2849 --
2850 -- Debug Statements
2851 --
2852 IF l_debug_on THEN
2853 WSH_DEBUG_SV.logmsg(l_module_name, 'NO ACTUAL DEPARTURE DATE FOR STOP '|| P_STOP_ID );
2854 END IF;
2855 --
2856 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
2857 --
2858 -- Debug Statements
2859 --
2860 IF l_debug_on THEN
2861 WSH_DEBUG_SV.logmsg(l_module_name,'WSH_NO_ACTUALL_DEPARTURE_DATE exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
2862 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:WSH_NO_ACTUALL_DEPARTURE_DATE');
2863 END IF;
2864 --
2865 WHEN GL_CURRENCY_API.No_Rate THEN
2866 --
2867 -- Debug Statements
2868 --
2869 IF l_debug_on THEN
2870 WSH_DEBUG_SV.logmsg(l_module_name, 'PROCESS FREIGHT COSTS FAILED BECUASE NO RATE EXISTS BETWEEN THESE TWO CURRENCY' );
2871 END IF;
2872 --
2873 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
2874 --
2875 -- Debug Statements
2876 --
2877 IF l_debug_on THEN
2878 WSH_DEBUG_SV.logmsg(l_module_name,'GL_CURRENCY_API.NO_RATE exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
2879 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:GL_CURRENCY_API.NO_RATE');
2880 END IF;
2881 --
2882 WHEN GL_CURRENCY_API.Invalid_Currency THEN
2883 --
2884 -- Debug Statements
2885 --
2886 IF l_debug_on THEN
2887 WSH_DEBUG_SV.logmsg(l_module_name, 'INVALID CURRENCY FOR FROM CURRENCY_CODE OR TO CURRENCY_CODE' );
2888 END IF;
2889 --
2890 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
2891 --
2892 -- Debug Statements
2893 --
2894 IF l_debug_on THEN
2895 WSH_DEBUG_SV.logmsg(l_module_name,'GL_CURRENCY_API.INVALID_CURRENCY exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
2896 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:GL_CURRENCY_API.INVALID_CURRENCY');
2897 END IF;
2898 --
2899 WHEN Oe_currency_Not_Exist THEN
2900 --
2901 -- Debug Statements
2902 --
2903 IF l_debug_on THEN
2904 WSH_DEBUG_SV.logmsg(l_module_name, 'CURRENCY_CODE FROM OM HEADER TABLE DOES NOT EXIST' );
2905 END IF;
2906 --
2907 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
2908 --
2909 -- Debug Statements
2910 --
2911 IF l_debug_on THEN
2912 WSH_DEBUG_SV.logmsg(l_module_name,'OE_CURRENCY_NOT_EXIST exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
2913 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OE_CURRENCY_NOT_EXIST');
2914 END IF;
2915 --
2916 WHEN no_user_defined_rate THEN
2917 --
2918 -- Debug Statements
2919 --
2920 IF l_debug_on THEN
2921 WSH_DEBUG_SV.logmsg(l_module_name, 'FREIGHT COST API FAILED BECAUSE CONVERSION_TYPE IS USER BUG NO USER-DEFINED RATE EXISTS' );
2922 END IF;
2923 --
2924 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
2925 --
2926 -- Debug Statements
2927 --
2928 IF l_debug_on THEN
2929 WSH_DEBUG_SV.logmsg(l_module_name,'NO_USER_DEFINED_RATE exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
2930 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:NO_USER_DEFINED_RATE');
2931 END IF;
2932 --
2933 WHEN others THEN
2934 WSH_UTIL_CORE.Default_Handler('WSH_FC_INTERFACE.Source_line_level_cost');
2935 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
2936
2937 --
2938 -- Debug Statements
2939 --
2940 IF l_debug_on THEN
2941 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
2942 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
2943 END IF;
2944 --
2945 END Source_Line_Level_Cost;
2946
2947 -- -----------------------------------------------------------------------------------------
2948 -- PROCEDURE NAME: Calculate_Freight_Costs
2949 -- ------------------------------------------------------------------------------------------
2950
2951 PROCEDURE Calculate_Freight_Costs(
2952 p_stop_id IN NUMBER
2953 , x_Freight_Costs OUT NOCOPY OMInterfaceCostTabType
2954 , x_return_status OUT NOCOPY VARCHAR2
2955 )
2956 IS
2957
2958 CURSOR C_Trip_Level_FC
2959 IS
2960 SELECT a.trip_id, a.freight_cost_id, a.unit_amount, a.currency_code,
2961 c.freight_cost_type_code
2962 , a.conversion_type_code, a.conversion_rate
2963 FROM wsh_freight_costs a,
2964 wsh_trip_stops b,
2965 wsh_freight_cost_types c
2966 WHERE a.trip_id = b.trip_id and
2967 b.stop_id = p_stop_id and
2968 c.freight_cost_type_id = a.freight_cost_type_id and
2969 /* H projects: pricing integration csun
2970 only line_type_code NULL, PRICE, CHARGE should be
2971 interfaced to OM
2972 */
2973 NVL(a.line_type_code, 'CHARGE') in ('PRICE', 'CHARGE')
2974 AND nvl(b.shipments_type_flag,'O') IN ('O','M')
2975 AND a.unit_amount IS NOT NULL -- bug 5447870: skip NULL records
2976 AND a.currency_code IS NOT NULL;
2977
2978 temp_trip_fc_rec C_Trip_Level_FC%ROWTYPE;
2979
2980
2981 CURSOR C_Stop_Level_FC
2982 IS
2983 SELECT a.stop_id, a.freight_cost_id, a.unit_amount, a.currency_code,
2984 b.freight_cost_type_code
2985 , a.conversion_type_code, a.conversion_rate
2986 FROM wsh_freight_costs a,
2987 wsh_freight_cost_types b
2988 WHERE a.stop_id = p_stop_id and
2989 a.freight_cost_type_id = b.freight_cost_type_id and
2990 /* H projects: pricing integration csun
2991 only line_type_code NULL, PRICE, CHARGE should be
2992 interfaced to OM
2993 */
2994 NVL(a.line_type_code, 'CHARGE') in ('PRICE', 'CHARGE')
2995 AND a.unit_amount IS NOT NULL -- bug 5447870: skip NULL records
2996 AND a.currency_code IS NOT NULL;
2997
2998 temp_stop_fc_rec C_Stop_Level_FC%ROWTYPE;
2999
3000
3001 CURSOR C_Delivery_Level_FC
3002 IS
3003 SELECT wnd.delivery_id, wfc.freight_cost_id,
3004 wfc.unit_amount, wfc.currency_code,
3005 wft.freight_cost_type_code
3006 , wfc.conversion_type_code, wfc.conversion_rate
3007 FROM wsh_freight_costs wfc,
3008 wsh_freight_cost_types wft,
3009 wsh_delivery_legs wdl,
3010 wsh_new_deliveries wnd,
3011 wsh_trip_stops wts
3012 WHERE wts.stop_id = p_stop_id and
3013 wts.stop_id = wdl.pick_up_stop_id and
3014 wts.stop_location_id = wnd.initial_pickup_location_id and
3015 wdl.delivery_id = wnd.delivery_id and
3016 wfc.delivery_id = wdl.delivery_id and
3017 wfc.freight_cost_type_id = wft.freight_cost_type_id and
3018 /* H projects: pricing integration csun
3019 pricing engine calculated freight costs are always at delivery detail level,
3020 however those lines also have delivery id populated, so those lines should be
3021 excluded from delivery level FC calculation
3022 */
3023 NVL(wfc.line_type_code, 'CHARGE') in ('PRICE', 'CHARGE') and
3024 NVL(wfc.charge_source_code, 'MANUAL')= 'MANUAL' and
3025 nvl(wnd.shipment_direction,'O') IN ('O','IO')
3026 AND wfc.unit_amount IS NOT NULL -- bug 5447870: skip NULL records
3027 AND wfc.currency_code IS NOT NULL
3028 order by wnd.delivery_id;
3029
3030 temp_Delivery_fc_rec C_Delivery_Level_FC%ROWTYPE;
3031
3032 CURSOR C_Container_Level_FC
3033 IS
3034 SELECT distinct
3035 wfc.delivery_detail_id,
3036 wfc.freight_cost_id,
3037 wfc.unit_amount,
3038 wfc.currency_code,
3039 wft.freight_cost_type_code,
3040 wfc.conversion_type_code,
3041 wfc.conversion_rate
3042 FROM wsh_delivery_assignments_v wda,
3043 wsh_delivery_details wdd,
3044 wsh_freight_costs wfc,
3045 wsh_freight_cost_types wft,
3046 wsh_delivery_legs wdl,
3047 wsh_new_deliveries wnd,
3048 wsh_trip_stops wts
3049 WHERE wts.stop_id = wdl.pick_up_stop_id and
3050 wts.stop_id = p_stop_id and
3051 wts.stop_location_id = wnd.initial_pickup_location_id and
3052 wdl.delivery_id = wnd.delivery_id and
3053 wda.delivery_id = wdl.delivery_id and
3054 wda.delivery_id IS NOT NULL and
3055 wda.parent_delivery_detail_id = wdd.delivery_detail_id and
3056 wdd.container_flag = 'Y' and
3057 wdd.source_code='WSH' and
3058 wfc.delivery_detail_id = wdd.delivery_detail_id and
3059 wfc.freight_cost_type_id = wft.freight_cost_type_id and
3060 NVL(wfc.line_type_code, 'CHARGE') in ('PRICE' ,'CHARGE','TLPRICE','TLCHARGE') and
3061 nvl(wdd.line_direction,'O') IN ('O','IO')
3062 AND wfc.unit_amount IS NOT NULL -- bug 5447870: skip NULL records
3063 AND wfc.currency_code IS NOT NULL;
3064
3065
3066 temp_container_fc_rec C_Container_Level_FC%ROWTYPE;
3067
3068 CURSOR C_Detail_Level_FC
3069 IS
3070 SELECT
3071 wfc.delivery_detail_id,
3072 wfc.freight_cost_id,
3073 wfc.unit_amount,
3074 wfc.currency_code,
3075 wft.freight_cost_type_code
3076 , wfc.conversion_type_code, wfc.conversion_rate
3077 FROM wsh_delivery_assignments_v wda,
3078 wsh_delivery_details wdd,
3079 oe_order_lines_all ol,
3080 wsh_freight_costs wfc,
3081 wsh_freight_cost_types wft,
3082 wsh_delivery_legs wdl,
3083 wsh_new_deliveries wnd,
3084 wsh_trip_stops wts
3085 WHERE wts.stop_id = wdl.pick_up_stop_id and
3086 wts.stop_id = p_stop_id and
3087 wts.stop_location_id = wnd.initial_pickup_location_id and
3088 wdl.delivery_id = wnd.delivery_id and
3089 wda.delivery_id = wdl.delivery_id and
3090 wda.delivery_id IS NOT NULL and
3091 wda.delivery_detail_id = wdd.delivery_detail_id and
3092 wdd.container_flag = 'N' and
3093 ol.line_id = wdd.source_line_id and
3094 wdd.source_code = 'OE' and
3095 wfc.delivery_detail_id = wdd.delivery_detail_id and
3096 wfc.freight_cost_type_id = wft.freight_cost_type_id and
3097 wdd.container_flag = 'N' and
3098 wdd.oe_interfaced_flag = 'N' and
3099 wdd.released_status = 'C' and
3100 NVL(wdd.shipped_quantity, 0) > 0 and
3101 NVL(wfc.line_type_code, 'CHARGE') in ('PRICE' ,'CHARGE','TLPRICE','TLCHARGE') --TKT
3102 AND wfc.unit_amount IS NOT NULL -- bug 5447870: skip NULL records
3103 AND wfc.currency_code IS NOT NULL;
3104
3105
3106 temp_detail_fc_rec C_Detail_Level_FC%ROWTYPE;
3107
3108 l_prorated_freight_cost ProratedCostTabType;
3109 l_return_status VARCHAR2(5);
3110 l_Relevant_Info_Tab RelavantInfoTabType;
3111 l_already_got_info VARCHAR2(1) := 'F';
3112 l_got_container_info VARCHAR2(1) := 'F';
3113 i NUMBER := 0;
3114 l_old_delivery_id NUMBER DEFAULT NULL;
3115 l_all_deliveries_calculated VARCHAR2(1) := 'F';
3116
3117 WSH_TRIP_FC_ERROR EXCEPTION;
3118 WSH_STOP_FC_ERROR EXCEPTION;
3119 WSH_DELIVERY_FC_ERROR EXCEPTION;
3120 WSH_DETAIL_FC_ERROR EXCEPTION;
3121 WSH_CONTAINER_FC_ERROR EXCEPTION;
3122 WSH_FC_GET_INFO_ERR EXCEPTION;
3123
3124 --
3125 l_debug_on BOOLEAN;
3126 --
3127 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'CALCULATE_FREIGHT_COSTS';
3128 --
3129 BEGIN
3130 --
3131 -- Debug Statements
3132 --
3133 --
3134 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
3135 --
3136 IF l_debug_on IS NULL
3137 THEN
3138 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
3139 END IF;
3140 --
3141 IF l_debug_on THEN
3142 WSH_DEBUG_SV.push(l_module_name);
3143 --
3144 WSH_DEBUG_SV.log(l_module_name,'P_STOP_ID',P_STOP_ID);
3145 END IF;
3146 --
3147 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
3148 --
3149 -- Debug Statements
3150 --
3151 IF l_debug_on THEN
3152 WSH_DEBUG_SV.logmsg(l_module_name, 'BEGINNING OF CALCULATE_FREIGHT_COST , STOP_ID:'|| P_STOP_ID );
3153
3154 END IF;
3155 --
3156 OPEN C_Trip_Level_FC;
3157 <<Trip_Loop>>
3158 LOOP
3159 FETCH C_Trip_Level_FC INTO temp_trip_fc_rec;
3160 EXIT Trip_Loop WHEN C_Trip_Level_FC%NOTFOUND;
3161 IF l_already_got_info = 'F' THEN
3162 Get_Relevant_Info(
3163 p_level=> 'TRIP',
3164 p_trip_id => temp_trip_fc_rec.trip_id,
3165 x_Relevant_Info_Tab => l_Relevant_Info_Tab,
3166 x_return_status => l_return_status );
3167 IF l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS THEN
3168 RAISE WSH_FC_GET_INFO_ERR;
3169 END IF;
3170 l_already_got_info := 'T';
3171 END IF;
3172
3173 Calculate_Trip_Level_Cost(
3174 p_trip_id => temp_trip_fc_rec.trip_id,
3175 p_stop_id => p_stop_id,
3176 p_Freight_Cost_Type_Code => temp_trip_fc_rec.freight_cost_type_code,
3177 p_freight_cost_id => temp_trip_fc_rec.freight_cost_id,
3178 p_freight_cost_amount => temp_trip_fc_rec.unit_amount,
3179 p_from_currency_code => temp_trip_fc_rec.currency_code,
3180 p_conversion_type_code => temp_trip_fc_rec.conversion_type_code,
3181 p_conversion_rate => temp_trip_fc_rec.conversion_rate,
3182 p_Relevant_Info_Tab => l_Relevant_Info_Tab,
3183 x_Prorated_Freight_Cost => l_prorated_freight_cost,
3184 x_return_status => l_return_status);
3185 -- check the status
3186 IF l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS THEN
3187 RAISE WSH_TRIP_FC_ERROR;
3188 END IF;
3189 END LOOP Trip_Loop;
3190 CLOSE C_Trip_Level_FC;
3191
3192 -- calculate stop level freight cost
3193 OPEN C_Stop_Level_FC;
3194 <<Stop_Loop>>
3195 LOOP
3196 FETCH C_Stop_Level_FC INTO temp_stop_fc_rec;
3197 EXIT Stop_Loop WHEN C_Stop_Level_FC%NOTFOUND;
3198 IF l_already_got_info = 'F' THEN
3199 Get_Relevant_Info(
3200 p_level=> 'STOP',
3201 p_stop_id => temp_stop_fc_rec.stop_id,
3202 x_Relevant_Info_Tab => l_Relevant_Info_Tab,
3203 x_return_status => l_return_status );
3204 IF l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS THEN
3205 RAISE WSH_FC_GET_INFO_ERR;
3206 END IF;
3207 l_already_got_info := 'T';
3208 END IF;
3209
3210 Calculate_Stop_Level_Cost(
3211 p_stop_id => temp_stop_fc_rec.stop_id,
3212 p_Freight_Cost_Type_Code => temp_stop_fc_rec.freight_cost_type_code,
3213 p_freight_cost_id => temp_stop_fc_rec.freight_cost_id,
3214 p_freight_cost_amount => temp_stop_fc_rec.unit_amount,
3215 p_from_currency_code => temp_stop_fc_rec.currency_code,
3216 p_conversion_type_code => temp_stop_fc_rec.conversion_type_code,
3217 p_conversion_rate => temp_stop_fc_rec.conversion_rate,
3218 p_Relevant_Info_Tab => l_Relevant_Info_Tab,
3219 x_Prorated_freight_cost => l_prorated_freight_cost,
3220 x_return_status => l_return_status);
3221 -- check the status
3222 IF l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS THEN
3223 RAISE WSH_STOP_FC_ERROR;
3224 END IF;
3225 END LOOP Stop_Loop;
3226 CLOSE C_Stop_Level_FC;
3227
3228 l_all_deliveries_calculated := l_already_got_info ;
3229 -- Calculate Delivery level freight cost
3230 OPEN C_Delivery_Level_FC;
3231 <<Delivery_Loop>>
3232 LOOP
3233 FETCH C_Delivery_Level_FC INTO temp_Delivery_fc_rec;
3234 EXIT Delivery_Loop WHEN C_Delivery_Level_FC%NOTFOUND;
3235
3236 IF l_all_deliveries_calculated = 'F' THEN
3237 IF l_old_delivery_id = temp_Delivery_fc_rec.delivery_id THEN
3238 l_already_got_info := 'T';
3239 ELSE
3240 l_old_delivery_id := temp_Delivery_fc_rec.delivery_id;
3241 l_already_got_info := 'F';
3242 END IF;
3243 END IF;
3244 IF l_already_got_info = 'F' THEN
3245 Get_Relevant_Info(
3246 p_level=> 'DELIVERY',
3247 p_delivery_id => temp_Delivery_fc_rec.delivery_id,
3248 x_Relevant_Info_Tab => l_Relevant_Info_Tab,
3249 x_return_status => l_return_status );
3250 IF l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS THEN
3251 RAISE WSH_FC_GET_INFO_ERR;
3252 END IF;
3253 l_already_got_info := 'T';
3254 END IF;
3255
3256 Calculate_Delivery_Level_Cost(
3257 p_delivery_id => temp_Delivery_fc_rec.delivery_id,
3258 p_Freight_Cost_Type_Code => temp_Delivery_fc_rec.freight_cost_type_code,
3259 p_Freight_Cost_Id => temp_Delivery_fc_rec.freight_cost_id,
3260 p_Freight_Cost_Amount => temp_Delivery_fc_rec.unit_amount,
3261 p_From_Currency_Code => temp_Delivery_fc_rec.currency_code,
3262 p_conversion_type_code => temp_Delivery_fc_rec.conversion_type_code,
3263 p_conversion_rate => temp_Delivery_fc_rec.conversion_rate,
3264 p_Relevant_Info_Tab => l_Relevant_Info_Tab,
3265 x_Prorated_Freight_Cost => l_prorated_freight_cost,
3266 x_return_status => l_return_status);
3267 IF l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS THEN
3268 RAISE WSH_DELIVERY_FC_ERROR;
3269 END IF;
3270 END LOOP Delivery_Loop;
3271 CLOSE C_Delivery_Level_FC;
3272
3273 OPEN C_Container_Level_FC;
3274 <<Container_FC_Loop>>
3275 LOOP
3276 l_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
3277 FETCH C_Container_Level_FC INTO temp_container_fc_rec;
3278 EXIT Container_FC_Loop WHEN C_Container_Level_FC%NOTFOUND;
3279 l_got_container_info := 'F';
3280 FOR i in 1 .. l_Relevant_Info_Tab.count LOOP
3281 IF l_Relevant_Info_Tab(i).container_id = temp_container_fc_rec.delivery_detail_id THEN
3282 l_got_container_info := 'T';
3283 exit;
3284 END IF;
3285 END LOOP;
3286 IF l_got_container_info = 'F' THEN
3287 g_container_relationship.delete;
3288 Get_Relevant_Info(
3289 p_level=> 'CONTAINER',
3290 p_container_id => temp_container_fc_rec.delivery_detail_id ,
3291 x_Relevant_Info_Tab => l_Relevant_Info_Tab,
3292 x_return_status => l_return_status );
3293 IF l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS THEN
3294 RAISE WSH_FC_GET_INFO_ERR;
3295 END IF;
3296 END IF;
3297
3298 Calculate_Container_Level_Cost(
3299 p_container_id=>temp_container_fc_rec.delivery_detail_id,
3300 p_Freight_Cost_Type_Code => temp_container_fc_rec.freight_cost_type_code,
3301 p_freight_cost_id=>temp_container_fc_rec.freight_cost_id,
3302 p_freight_cost_amount=>temp_container_fc_rec.unit_amount,
3303 p_from_currency_code=>temp_container_fc_rec.currency_code,
3304 p_conversion_type_code => temp_container_fc_rec.conversion_type_code,
3305 p_conversion_rate => temp_container_fc_rec.conversion_rate,
3306 p_relevant_info_tab=>l_Relevant_Info_Tab,
3307 x_prorated_freight_cost=>l_prorated_freight_cost,
3308 x_return_status=>l_return_status);
3309 IF l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS THEN
3310 RAISE WSH_CONTAINER_FC_ERROR;
3311 END IF;
3312
3313 END LOOP Container_FC_LOOP;
3314 CLOSE C_Container_Level_FC;
3315
3316 -- Calculate Delivery detail level
3317 OPEN C_detail_level_fc;
3318 <<Detail_Loop>>
3319 LOOP
3320 l_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
3321 FETCH C_detail_level_fc INTO temp_detail_fc_rec;
3322 EXIT Detail_Loop WHEN C_detail_level_fc%NOTFOUND;
3323
3324 Calculate_Detail_Level_Cost(
3325 p_delivery_detail_id=>temp_detail_fc_rec.delivery_detail_id,
3326 p_Freight_Cost_Type_Code => temp_detail_fc_rec.freight_cost_type_code,
3327 p_freight_cost_id=>temp_detail_fc_rec.freight_cost_id,
3328 p_freight_cost_amount=>temp_detail_fc_rec.unit_amount,
3329 p_from_currency_code=>temp_detail_fc_rec.currency_code,
3330 p_conversion_type_code => temp_detail_fc_rec.conversion_type_code,
3331 p_conversion_rate => temp_detail_fc_rec.conversion_rate,
3332 p_relevant_info_tab=>l_Relevant_Info_Tab,
3333 x_prorated_freight_cost=>l_prorated_freight_cost,
3334 x_return_status=>l_return_status);
3335 IF l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS THEN
3336 RAISE WSH_DETAIL_FC_ERROR;
3337 END IF;
3338 END LOOP Detail_Loop;
3339 CLOSE C_detail_level_fc;
3340
3341 l_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
3342 Source_Line_Level_Cost(
3343 p_stop_id => p_stop_id,
3344 p_prorated_freight_cost => l_prorated_freight_cost,
3345 x_final_cost => x_freight_costs,
3346 x_return_status => l_return_status
3347 );
3348 IF (l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS) THEN
3349 --
3350 -- Debug Statements
3351 --
3352 IF l_debug_on THEN
3353 WSH_DEBUG_SV.logmsg(l_module_name, 'SOURCE_LINE_LEVEL_COST API FAILED' );
3354 END IF;
3355 --
3356 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
3357 END IF;
3358
3359 IF x_freight_costs.COUNT = 0 THEN
3360 -- this indicates there is not freight cost for this stop
3361 -- process_freight_cost will check this field the second time
3362 -- process_freight_cost is called for the same stop, if it sees
3363 -- x_freight_costs(1).freight_cost_id = -9999, it returns immediately
3364 x_freight_costs(1).freight_cost_id := -9999;
3365 END IF;
3366
3367 --
3368 -- Debug Statements
3369 --
3370 IF l_debug_on THEN
3371 WSH_DEBUG_SV.logmsg(l_module_name, 'END OF CALCULATE_FREIGHT_COST' );
3372 WSH_DEBUG_SV.pop(l_module_name);
3373 END IF;
3374 --
3375 EXCEPTION
3376
3377 WHEN WSH_TRIP_FC_ERROR THEN
3378 --
3379 -- Debug Statements
3380 --
3381 IF l_debug_on THEN
3382 WSH_DEBUG_SV.logmsg(l_module_name, 'CALCULATE_TRIP_LEVEL_COST FAILED' );
3383 END IF;
3384 --
3385 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
3386
3387 --
3388 -- Debug Statements
3389 --
3390 IF l_debug_on THEN
3391 WSH_DEBUG_SV.logmsg(l_module_name,'WSH_TRIP_FC_ERROR exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
3392 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:WSH_TRIP_FC_ERROR');
3393 END IF;
3394 --
3395 WHEN WSH_STOP_FC_ERROR THEN
3396 --
3397 -- Debug Statements
3398 --
3399 IF l_debug_on THEN
3400 WSH_DEBUG_SV.logmsg(l_module_name, 'CALCULATE_STOP_LEVEL_COST FAILED' );
3401 END IF;
3402 --
3403 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
3404
3405 --
3406 -- Debug Statements
3407 --
3408 IF l_debug_on THEN
3409 WSH_DEBUG_SV.logmsg(l_module_name,'WSH_STOP_FC_ERROR exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
3410 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:WSH_STOP_FC_ERROR');
3411 END IF;
3412 --
3413 WHEN WSH_DELIVERY_FC_ERROR THEN
3414 --
3415 -- Debug Statements
3416 --
3417 IF l_debug_on THEN
3418 WSH_DEBUG_SV.logmsg(l_module_name, 'CALCULATE_DELIVERY_LEVEL_COST FAILED' );
3419 END IF;
3420 --
3421 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
3422
3423 --
3424 -- Debug Statements
3425 --
3426 IF l_debug_on THEN
3427 WSH_DEBUG_SV.logmsg(l_module_name,'WSH_DELIVERY_FC_ERROR exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
3428 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:WSH_DELIVERY_FC_ERROR');
3429 END IF;
3430 --
3431 WHEN WSH_CONTAINER_FC_ERROR THEN
3432 --
3433 -- Debug Statements
3434 --
3435 IF l_debug_on THEN
3436 WSH_DEBUG_SV.logmsg(l_module_name, 'CALCULATE_CONTAINER_LEVEL_COST FAILED' );
3437 END IF;
3438 --
3439 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
3440
3441 --
3442 -- Debug Statements
3443 --
3444 IF l_debug_on THEN
3445 WSH_DEBUG_SV.logmsg(l_module_name,'WSH_CONTAINER_FC_ERROR exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
3446 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:WSH_CONTAINER_FC_ERROR');
3447 END IF;
3448 --
3449 WHEN WSH_DETAIL_FC_ERROR THEN
3450 --
3451 -- Debug Statements
3452 --
3453 IF l_debug_on THEN
3454 WSH_DEBUG_SV.logmsg(l_module_name, 'CALCULATE_DETAIL_LEVEL_COST FAILED' );
3455 END IF;
3456 --
3457 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
3458
3459 --
3460 -- Debug Statements
3461 --
3462 IF l_debug_on THEN
3463 WSH_DEBUG_SV.logmsg(l_module_name,'WSH_DETAIL_FC_ERROR exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
3464 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:WSH_DETAIL_FC_ERROR');
3465 END IF;
3466 --
3467 WHEN WSH_FC_GET_INFO_ERR THEN
3468 --
3469 -- Debug Statements
3470 --
3471 IF l_debug_on THEN
3472 WSH_DEBUG_SV.logmsg(l_module_name, 'GET RELEVANT INFO FAILED' );
3473 END IF;
3474 --
3475 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
3476
3477 --
3478 -- Debug Statements
3479 --
3480 IF l_debug_on THEN
3481 WSH_DEBUG_SV.logmsg(l_module_name,'WSH_FC_GET_INFO_ERR exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
3482 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:WSH_FC_GET_INFO_ERR');
3483 END IF;
3484 --
3485 WHEN others THEN
3486 WSH_UTIL_CORE.Default_Handler('WSH_FC_INTERFACE.Calculate_Freight_Costs');
3487 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
3488
3489 --
3490 -- Debug Statements
3491 --
3492 IF l_debug_on THEN
3493 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
3494 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
3495 END IF;
3496 --
3497 END Calculate_Freight_Costs;
3498
3499 --HVOP heali
3500 PROCEDURE Process_Freight_Costs(
3501 p_stop_id IN NUMBER
3502 , p_start_index IN NUMBER
3503 , p_line_id_tbl IN OE_WSH_BULK_GRP.T_NUM
3504 , x_freight_costs_all IN OUT NOCOPY OMInterfaceCostTabType
3505 , x_freight_costs IN OUT NOCOPY OE_Ship_Confirmation_Pub.Ship_Adj_Rec_Type
3506 , x_end_index OUT NOCOPY NUMBER
3507 , x_return_status OUT NOCOPY VARCHAR2
3508 ) IS
3509 i NUMBER;
3510 j NUMBER;
3511 l_return_status VARCHAR2(30);
3512 l_table_id NUMBER;
3513 l_delivery_id NUMBER;
3514 l_stop_id NUMBER;
3515 l_trip_id NUMBER;
3516
3517 Calculate_failed EXCEPTION;
3518 l_line_index NUMBER;
3519
3520 l_debug_on BOOLEAN;
3521 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'PROCESS_FREIGHT_COSTS';
3522
3523 BEGIN
3524 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
3525 --
3526 IF l_debug_on IS NULL THEN
3527 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
3528 END IF;
3529 --
3530 IF l_debug_on THEN
3531 WSH_DEBUG_SV.push(l_module_name);
3532 WSH_DEBUG_SV.log(l_module_name,'P_STOP_ID',P_STOP_ID);
3533 WSH_DEBUG_SV.log(l_module_name,'P_start_index',p_start_index);
3534 WSH_DEBUG_SV.log(l_module_name,'P_LINE_ID_TBL.count',P_LINE_ID_TBL.count);
3535 END IF;
3536 --
3537 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
3538
3539
3540 IF x_Freight_Costs_ALL.count = 0 THEN
3541 Calculate_Freight_Costs(
3542 p_stop_id => p_stop_id,
3543 x_freight_costs => x_Freight_Costs_ALL,
3544 x_return_status => l_return_status
3545 );
3546 IF (l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS) THEN
3547 RAISE Calculate_failed;
3548 END IF;
3549 --
3550 IF l_debug_on THEN
3551 WSH_DEBUG_SV.logmsg(l_module_name, 'X_FREIGHT_COSTS_ALL.COUNT = '|| X_FREIGHT_COSTS_ALL.COUNT );
3552 END IF;
3553 ELSE
3554 IF l_debug_on THEN
3555 WSH_DEBUG_SV.logmsg(l_module_name, 'SKIP CALCULATE_FREIGHT_COSTS' );
3556 END IF;
3557 END IF;
3558
3559
3560 IF x_Freight_Costs_ALL(1).freight_cost_id <> -9999 THEN
3561 IF l_debug_on THEN
3562 WSH_DEBUG_SV.logmsg(l_module_name, 'NUMBER OF ELEMENTS IN P_LINE_ID_TBL IS : ' || P_LINE_ID_TBL.COUNT );
3563 END IF;
3564
3565 <<Prorate_Loop>>
3566 FOR i IN 1 .. x_Freight_Costs_All.Count LOOP
3567 IF l_debug_on THEN
3568 WSH_DEBUG_SV.logmsg(l_module_name, 'I = '||I );
3569 END IF;
3570
3571 l_line_index := -9999;
3572
3573 -- assign line index to corresponding cost record
3574 <<Line_Index_Loop>>
3575 FOR l_line_counter IN p_start_index .. p_line_id_tbl.COUNT
3576 LOOP
3577 IF l_debug_on THEN
3578 WSH_DEBUG_SV.logmsg(l_module_name,'P_LINE_ID.TBL('||L_LINE_COUNTER||'):'||P_LINE_ID_TBL(L_LINE_COUNTER));
3579 END IF;
3580
3581 --HVOP heali
3582
3583 IF (x_Freight_Costs_ALL(i).source_line_id = p_line_id_tbl(l_line_counter))THEN
3584 l_table_id := x_freight_costs.line_id.count + 1;
3585 l_line_index := l_line_counter;
3586 EXIT Line_Index_Loop;
3587 END IF;
3588 END LOOP Line_Index_Loop;
3589
3590 IF (l_line_index = -9999) THEN
3591 GOTO Next_Cost_Record;
3592 END IF;
3593
3594 IF l_debug_on THEN
3595 WSH_DEBUG_SV.logmsg(l_module_name, 'SOURCE LINE ID = '||X_FREIGHT_COSTS_ALL(I).SOURCE_LINE_ID );
3596 WSH_DEBUG_SV.log(l_module_name, 'l_table_id',l_table_id);
3597 END IF;
3598
3599 x_freight_costs.cost_id.extend;
3600 x_freight_costs.cost_id(l_table_id) := x_Freight_Costs_ALL(i).freight_cost_id;
3601 x_freight_costs.automatic_flag.extend;
3602 x_freight_costs.automatic_flag(l_table_id) := 'N';
3603 x_freight_costs.list_line_type_code.extend;
3604 x_freight_costs.list_line_type_code(l_table_id) := 'COST';
3605 x_freight_costs.charge_type_code.extend;
3606 x_freight_costs.charge_type_code(l_table_id) := x_Freight_Costs_ALL(i).freight_cost_type_code;
3607 x_freight_costs.header_id.extend;
3608 x_freight_costs.header_id(l_table_id) := x_Freight_Costs_ALL(i).source_header_id;
3609 x_freight_costs.line_id.extend;
3610 x_freight_costs.line_id(l_table_id) := x_Freight_Costs_ALL(i).source_line_id;
3611 x_freight_costs.adjusted_amount.extend;
3612 x_freight_costs.adjusted_amount(l_table_id) := x_Freight_Costs_ALL(i).amount;
3613 x_freight_costs.arithmetic_operator.extend;
3614 x_freight_costs.arithmetic_operator(l_table_id) := 'AMT';
3615 x_freight_costs.operation.extend;
3616 x_freight_costs.operation(l_table_id) := OE_GLOBALS.G_OPR_CREATE;
3617
3618
3619 IF l_debug_on THEN
3620 WSH_DEBUG_SV.log(l_module_name,'cost_id',x_freight_costs.cost_id(l_table_id));
3621 WSH_DEBUG_SV.log(l_module_name,'automatic_flag',x_freight_costs.automatic_flag(l_table_id));
3622 WSH_DEBUG_SV.log(l_module_name,'list_line_type_code',x_freight_costs.list_line_type_code(l_table_id));
3623 WSH_DEBUG_SV.log(l_module_name,'charge_type_code',x_freight_costs.charge_type_code(l_table_id));
3624 WSH_DEBUG_SV.log(l_module_name,'header_id',x_freight_costs.header_id(l_table_id));
3625 WSH_DEBUG_SV.log(l_module_name,'line_id',x_freight_costs.line_id(l_table_id));
3626 WSH_DEBUG_SV.log(l_module_name,'adjusted_amount',x_freight_costs.adjusted_amount(l_table_id));
3627 WSH_DEBUG_SV.log(l_module_name,'arithmetic_operator',x_freight_costs.arithmetic_operator(l_table_id));
3628 WSH_DEBUG_SV.log(l_module_name,'operation',x_freight_costs.operation(l_table_id));
3629 END IF;
3630
3631
3632 <<Next_Cost_Record>>
3633 NULL;
3634 END LOOP Prorate_Loop;
3635 END IF;
3636
3637
3638
3639 IF l_debug_on THEN
3640 WSH_DEBUG_SV.pop(l_module_name);
3641 END IF;
3642 EXCEPTION
3643 WHEN Calculate_failed THEN
3644 IF l_debug_on THEN
3645 WSH_DEBUG_SV.logmsg(l_module_name, 'CALCULATE_FREIGHT_COST API FAILED' );
3646 END IF;
3647 --
3648 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
3649
3650 IF l_debug_on THEN
3651 WSH_DEBUG_SV.logmsg(l_module_name,'CALCULATE_FAILED exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
3652 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:CALCULATE_FAILED');
3653 END IF;
3654
3655 WHEN others THEN
3656 WSH_UTIL_CORE.Default_Handler('WSH_FC_INTERFACE.Process_Freight_Costs');
3657 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
3658 --
3659 IF l_debug_on THEN
3660 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
3661 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
3662 END IF;
3663 END Process_Freight_Costs;
3664
3665
3666 FUNCTION Prorate_Freight_Charge(
3667 p_delivery_detail_id IN NUMBER
3668 , p_charge_id IN NUMBER
3669 )
3670 RETURN NUMBER
3671 IS
3672
3673 CURSOR C_Details_Assigned(p_source_line_id NUMBER)
3674 IS
3675 SELECT delivery_detail_id
3676 FROM wsh_delivery_details
3677 WHERE source_line_id = p_source_line_id
3678 AND source_code = 'OE'
3679 AND container_flag = 'N';
3680
3681 l_number_details_assigned NUMBER := 0;
3682 l_line_id NUMBER;
3683 l_charge_line_id NUMBER;
3684 l_detail_id NUMBER;
3685 l_amount NUMBER;
3686 l_uom VARCHAR2(3);
3687 l_unit NUMBER;
3688 l_unit_in_st_uom NUMBER;
3689 l_standard_uom VARCHAR2(3);
3690 l_total_units NUMBER := 0;
3691 l_distributed_charge NUMBER := 0;
3692 l_check NUMBER := 0;
3693 l_round_distributed_charge NUMBER := 0;
3694 l_currency_code VARCHAR2(15);
3695 l_rest_amount NUMBER;
3696 l_temp_table_id NUMBER;
3697 Input_Inconsistent EXCEPTION;
3698 l_return_status VARCHAR2(10);
3699 l_inventory_item_id NUMBER;
3700 --
3701 l_debug_on BOOLEAN;
3702 --
3703 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'PRORATE_FREIGHT_CHARGE';
3704 --
3705 BEGIN
3706 -- User delivery detail to find source line id instead of selecting from
3707 -- oe_charge_lines_v because each source_line_id could have multiple charges
3708 --
3709 -- Debug Statements
3710 --
3711 --
3712 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
3713 --
3714 IF l_debug_on IS NULL
3715 THEN
3716 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
3717 END IF;
3718 --
3719 IF l_debug_on THEN
3720 WSH_DEBUG_SV.push(l_module_name);
3721 --
3722 WSH_DEBUG_SV.log(l_module_name,'P_DELIVERY_DETAIL_ID',P_DELIVERY_DETAIL_ID);
3723 WSH_DEBUG_SV.log(l_module_name,'P_CHARGE_ID',P_CHARGE_ID);
3724 END IF;
3725 --
3726 SELECT wsh.source_line_id
3727 INTO l_line_id
3728 FROM wsh_delivery_details wsh, oe_order_lines_all l
3729 WHERE wsh.delivery_detail_id = p_delivery_detail_id AND
3730 wsh.source_line_id = l.line_id and
3731 wsh.source_code = 'OE';
3732
3733 SELECT COUNT(*)
3734 INTO l_check
3735 FROM oe_charge_lines_v
3736 WHERE line_id = l_line_id AND charge_id = p_charge_id;
3737
3738 IF (l_check = 0) THEN
3739 RAISE Input_Inconsistent;
3740 END IF;
3741
3742 -- first, get the number of delivery details associated with this source_line_id
3743 SELECT COUNT(*)
3744 INTO l_number_details_assigned
3745 FROM wsh_delivery_details
3746 WHERE source_line_id = l_line_id and
3747 source_code = 'OE';
3748
3749 -- Fetch uom of the first detail as standard uom
3750 OPEN C_Details_Assigned(l_line_id);
3751 FETCH C_Details_Assigned INTO l_detail_id;
3752 CLOSE C_Details_Assigned;
3753
3754 SELECT requested_quantity_uom
3755 INTO l_standard_uom
3756 FROM wsh_delivery_details
3757 WHERE delivery_detail_id = l_detail_id;
3758
3759 OPEN C_Details_Assigned(l_line_id);
3760 LOOP
3761 FETCH C_Details_Assigned INTO l_detail_id;
3762 EXIT WHEN C_Details_Assigned%NOTFOUND;
3763
3764 SELECT shipped_quantity, requested_quantity_uom, inventory_item_id
3765 INTO l_unit, l_uom , l_inventory_item_id
3766 FROM wsh_delivery_details
3767 WHERE delivery_detail_id = l_detail_id;
3768
3769 --
3770 -- Debug Statements
3771 --
3772 IF l_debug_on THEN
3773 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_WV_UTILS.CONVERT_UOM',WSH_DEBUG_SV.C_PROC_LEVEL);
3774 END IF;
3775 -- 3935583
3776 l_unit_in_st_uom := WSH_WV_UTILS.convert_uom(
3777 from_uom => l_uom,
3778 to_uom => l_standard_uom,
3779 quantity => l_unit,
3780 item_id => l_inventory_item_id);
3781 l_total_units := l_total_units + l_unit_in_st_uom;
3782 END LOOP;
3783
3784 CLOSE C_Details_Assigned;
3785
3786 SELECT charge_amount, currency_code
3787 INTO l_amount, l_currency_code
3788 FROM oe_charge_lines_v
3789 WHERE charge_id = p_charge_id;
3790
3791 SELECT shipped_quantity, requested_quantity_uom , inventory_item_id
3792 INTO l_unit, l_uom , l_inventory_item_id
3793 FROM wsh_delivery_details
3794 WHERE delivery_detail_id = p_delivery_detail_id;
3795
3796 --
3797 -- Debug Statements
3798 --
3799 IF l_debug_on THEN
3800 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_WV_UTILS.CONVERT_UOM',WSH_DEBUG_SV.C_PROC_LEVEL);
3801 END IF;
3802 --
3803 -- 3935583
3804 l_unit_in_st_uom := WSH_WV_UTILS.convert_uom(
3805 from_uom => l_uom,
3806 to_uom => l_standard_uom,
3807 quantity => l_unit,
3808 item_id => l_inventory_item_id);
3809
3810 l_distributed_charge := l_amount * (l_unit_in_st_uom/l_total_units);
3811 Round_Cost_Amount(l_distributed_charge, l_Currency_Code, l_round_distributed_charge, l_return_status);
3812
3813 --
3814 -- Debug Statements
3815 --
3816 IF l_debug_on THEN
3817 WSH_DEBUG_SV.pop(l_module_name);
3818 END IF;
3819 --
3820 RETURN(l_round_distributed_charge);
3821
3822 EXCEPTION
3823 WHEN input_inconsistent THEN
3824 --
3825 -- Debug Statements
3826 --
3827 IF l_debug_on THEN
3828 WSH_DEBUG_SV.logmsg(l_module_name,'INPUT_INCONSISTENT exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
3829 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:INPUT_INCONSISTENT');
3830 END IF;
3831 --
3832 RETURN (-9999);
3833 --WSH_UTIL_CORE.println('The parameter passed in conflict with each other');
3834
3835 END Prorate_Freight_Charge;
3836
3837
3838 -- Name Get_Cost_Factor
3839 -- Purpose dummy function
3840 -- Since TPA does not support deleting obsolete APIs,
3841 -- this function needs to remain in this package
3842 -- (bug 1948149).
3843 --
3844 -- TPA Selector WSH_TPA_SELECTOR_PKG.FreightCostTP
3845 FUNCTION Get_Cost_Factor(
3846 p_delivery_id IN NUMBER
3847 , p_container_instance_id IN NUMBER
3848 , x_return_status OUT NOCOPY VARCHAR2
3849 ) RETURN VARCHAR2 IS
3850 --
3851 l_debug_on BOOLEAN;
3852 --
3853 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'GET_COST_FACTOR';
3854 --
3855 BEGIN
3856 --
3857 -- Debug Statements
3858 --
3859 --
3860 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
3861 --
3862 IF l_debug_on IS NULL
3863 THEN
3864 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
3865 END IF;
3866 --
3867 IF l_debug_on THEN
3868 WSH_DEBUG_SV.push(l_module_name);
3869 --
3870 WSH_DEBUG_SV.log(l_module_name,'P_DELIVERY_ID',P_DELIVERY_ID);
3871 WSH_DEBUG_SV.log(l_module_name,'P_CONTAINER_INSTANCE_ID',P_CONTAINER_INSTANCE_ID);
3872 END IF;
3873 --
3874 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
3875 --
3876 -- Debug Statements
3877 --
3878 IF l_debug_on THEN
3879 WSH_DEBUG_SV.pop(l_module_name);
3880 END IF;
3881 --
3882 RETURN '';
3883 END Get_Cost_Factor;
3884
3885
3886 END WSH_FC_INTERFACE_PKG;
3887