[Home] [Help]
Skip to content
PACKAGE BODY: APPS.WSH_FC_INTERFACE_PKG
Source
1 PACKAGE BODY WSH_FC_INTERFACE_PKG AS
2 /* $Header: WSHFCIFB.pls 120.5 2012/01/06 12:48:53 skanduku ship $ */
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 --Bug 12955070 : Instead of using Round, using TRUNC so that the resultant
2324 -- will never exceed the original value.
2325 -- Also introduced the if condition, so that the amount is not
2326 -- returned as null when precision is not specfied.
2327 if l_precision is not null then
2328 x_Round_Amount := TRUNC(p_Amount, l_precision);
2329 else
2330 x_Round_Amount := p_Amount;
2331 end if;
2332
2333 --
2334 -- Debug Statements
2335 --
2336 IF l_debug_on THEN
2337 WSH_DEBUG_SV.log(l_module_name,'x_round_amount',x_round_amount);
2338 WSH_DEBUG_SV.pop(l_module_name);
2339 END IF;
2340 --
2341 EXCEPTION
2342
2343 WHEN WSH_NO_CURRENCY THEN
2344 --
2345 -- Debug Statements
2346 --
2347 IF l_debug_on THEN
2348 WSH_DEBUG_SV.logmsg(l_module_name, 'THE FREIGHT COST CURRENCY IS NOT RECOGNIZED IN THE SYSTEM' );
2349 END IF;
2350 --
2351 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
2352
2353 --
2354 -- Debug Statements
2355 --
2356 IF l_debug_on THEN
2357 WSH_DEBUG_SV.logmsg(l_module_name,'WSH_NO_CURRENCY exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
2358 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:WSH_NO_CURRENCY');
2359 END IF;
2360 --
2361 WHEN others THEN
2362 WSH_UTIL_CORE.Default_Handler('WSH_FC_INTERFACE.Round_Cost_Amount');
2363 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
2364
2365 --
2366 -- Debug Statements
2367 --
2368 IF l_debug_on THEN
2369 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
2370 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
2371 END IF;
2372 --
2373 END Round_Cost_Amount;
2374
2375
2376 -- -----------------------------------------------------------------------------------------
2377 -- PROCEDURE NAME: Source_Line_Level_Cost
2378 -- ------------------------------------------------------------------------------------------
2379 PROCEDURE Source_Line_Level_Cost(
2380 p_stop_id IN NUMBER
2381 , p_prorated_freight_cost IN ProratedCostTabType
2382 , x_Final_Cost IN OUT NOCOPY OMInterfaceCostTabType
2383 , x_return_status OUT NOCOPY VARCHAR2
2384 )
2385
2386 IS
2387
2388
2389 CURSOR C_OE_Curr_Info (c_delivery_detail_id NUMBER)
2390 IS
2391 SELECT h.transactional_curr_code,
2392 h.conversion_type_code,
2393 h.conversion_rate_date,
2394 h.conversion_rate,
2395 l.line_id,
2396 l.header_id
2397 FROM oe_order_headers_all h, oe_order_lines_all l, wsh_delivery_details wdd
2398 WHERE wdd.delivery_detail_id = c_delivery_detail_id AND
2399 wdd.source_line_id = l.line_id AND
2400 wdd.source_code = 'OE' and
2401 l.header_id = h.header_id;
2402
2403 l_OE_Curr_Rec C_OE_Curr_Info%ROWTYPE;
2404
2405
2406 CURSOR C_ACTUAL_DEPARTURE_DATE IS
2407 SELECT actual_departure_date
2408 FROM WSH_TRIP_STOPS
2409 WHERE stop_id = p_stop_id;
2410
2411
2412 i NUMBER := 0;
2413 j NUMBER := 0;
2414 l_source_line_id NUMBER := 0;
2415 l_next_tab_id NUMBER := 0;
2416 l_amount NUMBER := 0;
2417 l_line_currency_code VARCHAR2(15) := NULL;
2418 l_conversion_date DATE;
2419 l_conversion_type VARCHAR2(30) := NULL;
2420
2421 l_new_source_line_id BOOLEAN := TRUE;
2422 l_max_roll_days NUMBER := 0;
2423 l_actual_departure_date DATE := NULL;
2424 l_converted_amount NUMBER := 0;
2425 l_denominator NUMBER := 0;
2426 l_numerator NUMBER := 0;
2427 l_rate NUMBER := 0;
2428 l_rate_exists VARCHAR2(1) := 'N' ;
2429 oe_currency_not_exist EXCEPTION;
2430 no_user_defined_rate EXCEPTION;
2431 WSH_NO_ACTUALL_DEPARTURE_DATE EXCEPTION;
2432 WSH_STOP_NOT_FOUND EXCEPTION;
2433
2434 --
2435 l_debug_on BOOLEAN;
2436 --
2437 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'SOURCE_LINE_LEVEL_COST';
2438 --
2439 BEGIN
2440 --
2441 -- Debug Statements
2442 --
2443 --
2444 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
2445 --
2446 IF l_debug_on IS NULL
2447 THEN
2448 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
2449 END IF;
2450 --
2451 IF l_debug_on THEN
2452 WSH_DEBUG_SV.push(l_module_name);
2453 --
2454 WSH_DEBUG_SV.log(l_module_name,'P_STOP_ID',P_STOP_ID);
2455 WSH_DEBUG_SV.log(l_module_name,'p_prorated_freight_cost.COUNT',p_prorated_freight_cost.COUNT);
2456 /*
2457 WSH_DEBUG_SV.log(l_module_name,'p_prorated_freight_cost.delivery_detail_id',p_prorated_freight_cost.delivery_detail_id);
2458 WSH_DEBUG_SV.log(l_module_name,'p_prorated_freight_cost.freight_cost_type_code',p_prorated_freight_cost.freight_cost_type_code);
2459 WSH_DEBUG_SV.log(l_module_name,'p_prorated_freight_cost.freight_cost_id',p_prorated_freight_cost.freight_cost_id);
2460 WSH_DEBUG_SV.log(l_module_name,'p_prorated_freight_cost.amount',p_prorated_freight_cost.amount);
2461 WSH_DEBUG_SV.log(l_module_name,'p_prorated_freight_cost.currency_code',p_prorated_freight_cost.currency_code);
2462 WSH_DEBUG_SV.log(l_module_name,'p_prorated_freight_cost.conversion_type_code',p_prorated_freight_cost.conversion_type_code);
2463 WSH_DEBUG_SV.log(l_module_name,'p_prorated_freight_cost.conversion_rate',p_prorated_freight_cost.conversion_rate);
2464
2465 */
2466 END IF;
2467 --
2468 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
2469 --
2470 -- Debug Statements
2471 --
2472 IF l_debug_on THEN
2473 WSH_DEBUG_SV.logmsg(l_module_name, 'BEGINNING OF SOURCE_LINE_LEVEL_COST , STOP_ID: '|| P_STOP_ID );
2474 END IF;
2475 --
2476 l_next_tab_id := x_Final_Cost.COUNT + 1;
2477 <<Outer_LOOP>>
2478 FOR i IN 1 .. p_prorated_freight_cost.count
2479
2480 LOOP
2481
2482 OPEN C_OE_Curr_Info(p_prorated_freight_cost(i).delivery_detail_id);
2483 FETCH C_OE_Curr_Info INTO l_OE_Curr_Rec;
2484 IF (C_OE_Curr_Info%NOTFOUND) THEN
2485 --
2486 -- Debug Statements
2487 --
2488 IF l_debug_on THEN
2489 WSH_DEBUG_SV.logmsg(l_module_name, 'C_OE_CURR_INFO DOES NOT FETCH A RECORD' );
2490 END IF;
2491 --
2492 END IF;
2493 CLOSE C_OE_Curr_Info;
2494
2495 IF (l_oe_curr_rec.transactional_curr_code IS NULL) THEN
2496 RAISE oe_Currency_Not_Exist;
2497 ELSE
2498 l_line_currency_code := l_OE_Curr_Rec.transactional_curr_code;
2499 END IF;
2500
2501 IF l_debug_on THEN
2502 WSH_DEBUG_SV.log(l_module_name, 'l_OE_Curr_Rec.conversion_type_code',l_OE_Curr_Rec.conversion_type_code );
2503 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'));
2504 WSH_DEBUG_SV.log(l_module_name, 'l_OE_Curr_Rec.conversion_rate', to_char(l_OE_Curr_Rec.conversion_rate));
2505 END IF;
2506 -- bug 3455978
2507 l_conversion_type := NVL(l_OE_Curr_Rec.conversion_type_code, 'Corporate');
2508
2509 IF (l_oe_curr_rec.conversion_rate_date IS NOT NULL) THEN
2510 l_conversion_date := l_oe_curr_rec.conversion_rate_date;
2511 ELSE
2512 IF l_actual_departure_date is NULL THEN
2513 OPEN C_ACTUAL_DEPARTURE_DATE;
2514 FETCH C_ACTUAL_DEPARTURE_DATE INTO l_actual_departure_date;
2515 IF C_ACTUAL_DEPARTURE_DATE%NOTFOUND THEN
2516 CLOSE C_ACTUAL_DEPARTURE_DATE;
2517 raise WSH_STOP_NOT_FOUND;
2518 END IF;
2519 CLOSE C_ACTUAL_DEPARTURE_DATE;
2520 IF l_actual_departure_date IS NULL THEN
2521 raise WSH_NO_ACTUALL_DEPARTURE_DATE;
2522 END IF;
2523 END IF;
2524 l_conversion_date := l_actual_departure_date;
2525 END IF;
2526
2527 IF l_debug_on THEN
2528 WSH_DEBUG_SV.log(l_module_name,'l_conversion_type', l_conversion_type);
2529 WSH_DEBUG_SV.log(l_module_name,'l_OE_Curr_Rec.conversion_rate', to_char(l_OE_Curr_Rec.conversion_rate));
2530 WSH_DEBUG_SV.log(l_module_name,'l_conversion_date', to_char(l_conversion_date, 'DD-MON-YY HH24:MI:SS'));
2531 END IF;
2532
2533 l_max_roll_days := 300;
2534
2535 l_new_source_line_id := TRUE;
2536
2537 <<Inner_Loop>>
2538 -- merge the entries which share the same source_line_id and freight_cost_id
2539 FOR j in 1 .. x_Final_Cost.COUNT
2540 LOOP
2541
2542 IF (x_Final_Cost(j).source_line_id = l_OE_Curr_Rec.line_id AND
2543 x_Final_Cost(j).freight_cost_id = p_prorated_freight_cost(i).freight_cost_id) THEN
2544
2545 --
2546 -- Debug Statements
2547 --
2548 IF l_debug_on THEN
2549 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit GL_CURRENCY_API.IS_FIXED_RATE',WSH_DEBUG_SV.C_PROC_LEVEL);
2550 END IF;
2551 --
2552 IF (GL_CURRENCY_API.Is_Fixed_Rate(p_prorated_freight_cost(i).currency_code, l_line_currency_code, l_conversion_date) = 'Y') THEN
2553 --
2554 -- Debug Statements
2555 --
2556 IF l_debug_on THEN
2557 WSH_DEBUG_SV.logmsg(l_module_name, 'ADDING P_PRORATED_FREIGHT_COST ( ' || I ||' ) TO X_FINAL_COST ( '||J||' ) USING FIXED RATE' );
2558 END IF;
2559 --
2560 --
2561 -- Debug Statements
2562 --
2563 IF l_debug_on THEN
2564 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit GL_CURRENCY_API.CONVERT_AMOUNT',WSH_DEBUG_SV.C_PROC_LEVEL);
2565 END IF;
2566 --
2567 x_Final_Cost(j).amount := x_Final_Cost(j).amount + GL_CURRENCY_API.convert_amount(
2568 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);
2569 -- bug 3455978 use the conversion_type_code from wsh_freight_costs
2570 ELSIF (p_prorated_freight_cost(i).conversion_type_code = 'User') THEN
2571 IF (p_prorated_freight_cost(i).conversion_rate IS NOT NULL) THEN
2572 --
2573 -- Debug Statements
2574 --
2575 IF l_debug_on THEN
2576 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 ));
2577 END IF;
2578 --
2579 x_Final_Cost(j).amount := x_Final_Cost(j).amount + p_prorated_freight_cost(i).amount * p_prorated_freight_cost(i).conversion_rate;
2580
2581 ELSE
2582 --
2583 -- Debug Statements
2584 --
2585 IF l_debug_on THEN
2586 WSH_DEBUG_SV.logmsg(l_module_name, 'CONVERSION_TYPE IS USER BUT NO CONVERSION_RATE DEFINED IN FREIGHT COST , PROCESS_FREIGHT_COST FAILED' );
2587 END IF;
2588 --
2589 RAISE No_User_Defined_Rate;
2590 END IF;
2591 ELSIF (l_conversion_type = 'User') THEN
2592 IF (l_OE_Curr_Rec.conversion_rate IS NOT NULL) THEN
2593 --
2594 -- Debug Statements
2595 --
2596 IF l_debug_on THEN
2597 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 );
2598 END IF;
2599 --
2600 x_Final_Cost(j).amount := x_Final_Cost(j).amount + p_prorated_freight_cost(i).amount * l_oe_curr_rec.conversion_rate;
2601 ELSE
2602 --
2603 -- Debug Statements
2604 --
2605 IF l_debug_on THEN
2606 WSH_DEBUG_SV.logmsg(l_module_name, 'CONVERSION_TYPE IS USER BUT NO CONVERSION_RATE DEFINED IN OM PART EITHER , PROCESS_FREIGHT_COST FAILED' );
2607 END IF;
2608 --
2609 RAISE No_User_Defined_Rate;
2610 END IF;
2611 ELSE
2612
2613 --
2614 -- Debug Statements
2615 --
2616 IF l_debug_on THEN
2617 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit GL_CURRENCY_API.RATE_EXISTS',WSH_DEBUG_SV.C_PROC_LEVEL);
2618 END IF;
2619 --
2620 l_rate_exists := GL_CURRENCY_API.Rate_Exists(
2621 x_from_currency => p_prorated_freight_cost(i).currency_code,
2622 x_to_currency => l_line_currency_code,
2623 x_conversion_date => l_conversion_date,
2624 x_conversion_type => NVL(p_prorated_freight_cost(i).conversion_type_code,l_conversion_type)
2625 );
2626 IF (l_rate_exists = 'Y') THEN
2627 --
2628 -- Debug Statements
2629 --
2630 IF l_debug_on THEN
2631 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' ) );
2632 END IF;
2633 --
2634 --
2635 -- Debug Statements
2636 --
2637 IF l_debug_on THEN
2638 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit GL_CURRENCY_API.CONVERT_AMOUNT',WSH_DEBUG_SV.C_PROC_LEVEL);
2639 END IF;
2640 --
2641 -- bug 4135443
2642 x_Final_Cost(j).amount := x_Final_Cost(j).amount +
2643 GL_CURRENCY_API.convert_amount(p_prorated_freight_cost(i).currency_code,
2644 l_line_currency_code,
2645 l_conversion_date,
2646 NVL(p_prorated_freight_cost(i).conversion_type_code,l_conversion_type),
2647 p_prorated_freight_cost(i).amount);
2648 ELSE
2649 --
2650 -- Debug Statements
2651 --
2652 IF l_debug_on THEN
2653 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') );
2654 END IF;
2655 --
2656 --
2657 -- Debug Statements
2658 --
2659 IF l_debug_on THEN
2660 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit GL_CURRENCY_API.CONVERT_CLOSEST_AMOUNT',WSH_DEBUG_SV.C_PROC_LEVEL);
2661 END IF;
2662 --
2663 GL_CURRENCY_API.convert_closest_amount(
2664 x_from_currency => p_prorated_freight_cost(i).currency_code,
2665 x_to_currency => l_line_currency_code,
2666 x_conversion_date => l_conversion_date,
2667 x_conversion_type => NVL(p_prorated_freight_cost(i).conversion_type_code,l_conversion_type),
2668 x_user_rate => l_OE_Curr_Rec.conversion_rate,
2669 x_amount => p_prorated_freight_cost(i).amount,
2670 x_max_roll_days => l_max_roll_days,
2671 x_converted_amount=> l_converted_amount,
2672 x_denominator => l_denominator,
2673 x_numerator => l_numerator,
2674 x_rate => l_rate);
2675 x_Final_Cost(j).amount := x_Final_Cost(j).amount + l_converted_amount;
2676 END IF;
2677 END IF;
2678 l_new_source_line_id := FALSE;
2679 END IF;
2680 END LOOP Inner_Loop;
2681
2682 IF (l_new_source_line_id = TRUE) THEN
2683 l_next_tab_id := x_Final_Cost.COUNT + 1;
2684
2685 --
2686 -- Debug Statements
2687 --
2688 IF l_debug_on THEN
2689 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit GL_CURRENCY_API.IS_FIXED_RATE',WSH_DEBUG_SV.C_PROC_LEVEL);
2690 END IF;
2691 --
2692 IF (GL_CURRENCY_API.Is_Fixed_Rate(p_prorated_freight_cost(i).currency_code, l_line_currency_code, l_conversion_date) = 'Y') THEN
2693 --
2694 -- Debug Statements
2695 --
2696 IF l_debug_on THEN
2697 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' ) );
2698 END IF;
2699 --
2700 --
2701 -- Debug Statements
2702 --
2703 IF l_debug_on THEN
2704 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit GL_CURRENCY_API.CONVERT_AMOUNT',WSH_DEBUG_SV.C_PROC_LEVEL);
2705 END IF;
2706 --
2707 x_Final_Cost(l_next_tab_id).amount :=
2708 GL_CURRENCY_API.convert_amount(
2709 p_prorated_freight_cost(i).currency_code,
2710 l_line_currency_code,
2711 l_conversion_date,
2712 NVL(p_prorated_freight_cost(i).conversion_type_code,l_conversion_type),
2713 p_prorated_freight_cost(i).amount);
2714 -- bug 3455978 use the conversion_type_code from wsh_freight_costs
2715 ELSIF (p_prorated_freight_cost(i).conversion_type_code = 'User') THEN
2716 IF (p_prorated_freight_cost(i).conversion_rate IS NOT NULL) THEN
2717 --
2718 -- Debug Statements
2719 --
2720 IF l_debug_on THEN
2721 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) );
2722 END IF;
2723 --
2724 x_Final_Cost(l_next_tab_id).amount := p_prorated_freight_cost(i).amount * p_prorated_freight_cost(i).conversion_rate;
2725 ELSE
2726 --
2727 -- Debug Statements
2728 --
2729 IF l_debug_on THEN
2730 WSH_DEBUG_SV.logmsg(l_module_name, 'CONVERSION_TYPE IS USER BUT NO CONVERSION_RATE DEFINED IN FREIGHT COST EITHER , PROCESS_FREIGHT_COST FAILED' );
2731 END IF;
2732 --
2733 RAISE No_User_Defined_Rate;
2734 END IF;
2735 ELSIF (l_conversion_type = 'User') THEN
2736 IF (l_OE_Curr_Rec.conversion_rate IS NOT NULL) THEN
2737 --
2738 -- Debug Statements
2739 --
2740 IF l_debug_on THEN
2741 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 );
2742 END IF;
2743 --
2744 x_Final_Cost(l_next_tab_id).amount := p_prorated_freight_cost(i).amount * l_oe_curr_rec.conversion_rate;
2745 ELSE
2746 --
2747 -- Debug Statements
2748 --
2749 IF l_debug_on THEN
2750 WSH_DEBUG_SV.logmsg(l_module_name, 'CONVERSION_TYPE IS USER BUT NO CONVERSION_RATE DEFINED IN OM PART EITHER , PROCESS_FREIGHT_COST FAILED' );
2751 END IF;
2752 --
2753 RAISE No_User_Defined_Rate;
2754 END IF;
2755 ELSE
2756 --
2757 -- Debug Statements
2758 --
2759 IF l_debug_on THEN
2760 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit GL_CURRENCY_API.RATE_EXISTS',WSH_DEBUG_SV.C_PROC_LEVEL);
2761 END IF;
2762 --
2763 l_rate_exists := GL_CURRENCY_API.Rate_Exists(
2764 x_from_currency => p_prorated_freight_cost(i).currency_code,
2765 x_to_currency => l_line_currency_code,
2766 x_conversion_date => l_conversion_date,
2767 x_conversion_type => NVL(p_prorated_freight_cost(i).conversion_type_code,l_conversion_type)
2768 );
2769 IF (l_rate_exists = 'Y') THEN
2770 --
2771 -- Debug Statements
2772 --
2773 IF l_debug_on THEN
2774 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' ) );
2775 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit GL_CURRENCY_API.CONVERT_AMOUNT',WSH_DEBUG_SV.C_PROC_LEVEL);
2776 END IF;
2777 --
2778 -- bug 4135443
2779 x_Final_Cost(l_next_tab_id).amount := GL_CURRENCY_API.convert_amount (
2780 p_prorated_freight_cost(i).currency_code, l_line_currency_code,
2781 l_conversion_date,
2782 NVL(p_prorated_freight_cost(i).conversion_type_code,l_conversion_type),
2783 p_prorated_freight_cost(i).amount);
2784 ELSE
2785 --
2786 -- Debug Statements
2787 --
2788 IF l_debug_on THEN
2789 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:'||
2790 TO_CHAR ( L_CONVERSION_DATE , 'DD-MON-YY HH24:MI:SS') );
2791 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit GL_CURRENCY_API.CONVERT_CLOSEST_AMOUNT',WSH_DEBUG_SV.C_PROC_LEVEL);
2792 END IF;
2793 --
2794 --
2795 GL_CURRENCY_API.convert_closest_amount(
2796 x_from_currency => p_prorated_freight_cost(i).currency_code,
2797 x_to_currency => l_line_currency_code,
2798 x_conversion_date => l_conversion_date,
2799 x_conversion_type => NVL(p_prorated_freight_cost(i).conversion_type_code,l_conversion_type),
2800 x_user_rate => l_OE_Curr_Rec.conversion_rate,
2801 x_amount => p_prorated_freight_cost(i).amount,
2802 x_max_roll_days => l_max_roll_days,
2803 x_converted_amount=> l_converted_amount,
2804 x_denominator => l_denominator,
2805 x_numerator => l_numerator,
2806 x_rate => l_rate);
2807 x_Final_Cost(l_next_tab_id).amount := l_converted_amount;
2808 END IF;
2809 END IF;
2810 x_Final_Cost(l_next_tab_id).freight_cost_type_code := p_prorated_freight_cost(i).freight_cost_type_code;
2811 x_Final_Cost(l_next_tab_id).freight_cost_id := p_prorated_freight_cost(i).freight_cost_id;
2812 x_Final_Cost(l_next_tab_id).source_line_id := l_OE_Curr_Rec.line_id;
2813 x_Final_Cost(l_next_tab_id).source_header_id := l_OE_Curr_Rec.header_id;
2814 x_Final_Cost(l_next_tab_id).currency_code := l_line_currency_code;
2815 --
2816 -- Debug Statements
2817 --
2818 IF l_debug_on THEN
2819 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 );
2820 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 );
2821 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 );
2822 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 );
2823 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 );
2824 WSH_DEBUG_SV.logmsg(l_module_name, 'X_FINAL_COST ( ' || L_NEXT_TAB_ID || ' ) .AMOUNT: '|| X_FINAL_COST ( L_NEXT_TAB_ID ) .AMOUNT );
2825 END IF;
2826 --
2827 END IF;
2828 END LOOP Outer_Loop;
2829 --
2830 -- Debug Statements
2831 --
2832 IF l_debug_on THEN
2833 WSH_DEBUG_SV.logmsg(l_module_name, 'END OF SOURCE_LINE_LEVEL_COST , STOP_ID: '|| P_STOP_ID );
2834 WSH_DEBUG_SV.pop(l_module_name);
2835 END IF;
2836 --
2837 EXCEPTION
2838 WHEN WSH_STOP_NOT_FOUND THEN
2839 --
2840 -- Debug Statements
2841 --
2842 IF l_debug_on THEN
2843 WSH_DEBUG_SV.logmsg(l_module_name, 'STOP '|| P_STOP_ID || ' NOT FOUND' );
2844 END IF;
2845 --
2846 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
2847 --
2848 -- Debug Statements
2849 --
2850 IF l_debug_on THEN
2851 WSH_DEBUG_SV.logmsg(l_module_name,'WSH_STOP_NOT_FOUND exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
2852 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:WSH_STOP_NOT_FOUND');
2853 END IF;
2854 --
2855 WHEN WSH_NO_ACTUALL_DEPARTURE_DATE THEN
2856 --
2857 -- Debug Statements
2858 --
2859 IF l_debug_on THEN
2860 WSH_DEBUG_SV.logmsg(l_module_name, 'NO ACTUAL DEPARTURE DATE FOR STOP '|| P_STOP_ID );
2861 END IF;
2862 --
2863 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
2864 --
2865 -- Debug Statements
2866 --
2867 IF l_debug_on THEN
2868 WSH_DEBUG_SV.logmsg(l_module_name,'WSH_NO_ACTUALL_DEPARTURE_DATE exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
2869 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:WSH_NO_ACTUALL_DEPARTURE_DATE');
2870 END IF;
2871 --
2872 WHEN GL_CURRENCY_API.No_Rate THEN
2873 --
2874 -- Debug Statements
2875 --
2876 IF l_debug_on THEN
2877 WSH_DEBUG_SV.logmsg(l_module_name, 'PROCESS FREIGHT COSTS FAILED BECUASE NO RATE EXISTS BETWEEN THESE TWO CURRENCY' );
2878 END IF;
2879 --
2880 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
2881 --
2882 -- Debug Statements
2883 --
2884 IF l_debug_on THEN
2885 WSH_DEBUG_SV.logmsg(l_module_name,'GL_CURRENCY_API.NO_RATE exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
2886 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:GL_CURRENCY_API.NO_RATE');
2887 END IF;
2888 --
2889 WHEN GL_CURRENCY_API.Invalid_Currency THEN
2890 --
2891 -- Debug Statements
2892 --
2893 IF l_debug_on THEN
2894 WSH_DEBUG_SV.logmsg(l_module_name, 'INVALID CURRENCY FOR FROM CURRENCY_CODE OR TO CURRENCY_CODE' );
2895 END IF;
2896 --
2897 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
2898 --
2899 -- Debug Statements
2900 --
2901 IF l_debug_on THEN
2902 WSH_DEBUG_SV.logmsg(l_module_name,'GL_CURRENCY_API.INVALID_CURRENCY exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
2903 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:GL_CURRENCY_API.INVALID_CURRENCY');
2904 END IF;
2905 --
2906 WHEN Oe_currency_Not_Exist THEN
2907 --
2908 -- Debug Statements
2909 --
2910 IF l_debug_on THEN
2911 WSH_DEBUG_SV.logmsg(l_module_name, 'CURRENCY_CODE FROM OM HEADER TABLE DOES NOT EXIST' );
2912 END IF;
2913 --
2914 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
2915 --
2916 -- Debug Statements
2917 --
2918 IF l_debug_on THEN
2919 WSH_DEBUG_SV.logmsg(l_module_name,'OE_CURRENCY_NOT_EXIST exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
2920 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OE_CURRENCY_NOT_EXIST');
2921 END IF;
2922 --
2923 WHEN no_user_defined_rate THEN
2924 --
2925 -- Debug Statements
2926 --
2927 IF l_debug_on THEN
2928 WSH_DEBUG_SV.logmsg(l_module_name, 'FREIGHT COST API FAILED BECAUSE CONVERSION_TYPE IS USER BUG NO USER-DEFINED RATE EXISTS' );
2929 END IF;
2930 --
2931 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
2932 --
2933 -- Debug Statements
2934 --
2935 IF l_debug_on THEN
2936 WSH_DEBUG_SV.logmsg(l_module_name,'NO_USER_DEFINED_RATE exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
2937 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:NO_USER_DEFINED_RATE');
2938 END IF;
2939 --
2940 WHEN others THEN
2941 WSH_UTIL_CORE.Default_Handler('WSH_FC_INTERFACE.Source_line_level_cost');
2942 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
2943
2944 --
2945 -- Debug Statements
2946 --
2947 IF l_debug_on THEN
2948 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
2949 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
2950 END IF;
2951 --
2952 END Source_Line_Level_Cost;
2953
2954 -- -----------------------------------------------------------------------------------------
2955 -- PROCEDURE NAME: Calculate_Freight_Costs
2956 -- ------------------------------------------------------------------------------------------
2957
2958 PROCEDURE Calculate_Freight_Costs(
2959 p_stop_id IN NUMBER
2960 , x_Freight_Costs OUT NOCOPY OMInterfaceCostTabType
2961 , x_return_status OUT NOCOPY VARCHAR2
2962 )
2963 IS
2964
2965 CURSOR C_Trip_Level_FC
2966 IS
2967 SELECT a.trip_id, a.freight_cost_id, a.unit_amount, a.currency_code,
2968 c.freight_cost_type_code
2969 , a.conversion_type_code, a.conversion_rate
2970 FROM wsh_freight_costs a,
2971 wsh_trip_stops b,
2972 wsh_freight_cost_types c
2973 WHERE a.trip_id = b.trip_id and
2974 b.stop_id = p_stop_id and
2975 c.freight_cost_type_id = a.freight_cost_type_id and
2976 /* H projects: pricing integration csun
2977 only line_type_code NULL, PRICE, CHARGE should be
2978 interfaced to OM
2979 */
2980 NVL(a.line_type_code, 'CHARGE') in ('PRICE', 'CHARGE')
2981 AND nvl(b.shipments_type_flag,'O') IN ('O','M')
2982 AND a.unit_amount IS NOT NULL -- bug 5447870: skip NULL records
2983 AND a.currency_code IS NOT NULL;
2984
2985 temp_trip_fc_rec C_Trip_Level_FC%ROWTYPE;
2986
2987
2988 CURSOR C_Stop_Level_FC
2989 IS
2990 SELECT a.stop_id, a.freight_cost_id, a.unit_amount, a.currency_code,
2991 b.freight_cost_type_code
2992 , a.conversion_type_code, a.conversion_rate
2993 FROM wsh_freight_costs a,
2994 wsh_freight_cost_types b
2995 WHERE a.stop_id = p_stop_id and
2996 a.freight_cost_type_id = b.freight_cost_type_id and
2997 /* H projects: pricing integration csun
2998 only line_type_code NULL, PRICE, CHARGE should be
2999 interfaced to OM
3000 */
3001 NVL(a.line_type_code, 'CHARGE') in ('PRICE', 'CHARGE')
3002 AND a.unit_amount IS NOT NULL -- bug 5447870: skip NULL records
3003 AND a.currency_code IS NOT NULL;
3004
3005 temp_stop_fc_rec C_Stop_Level_FC%ROWTYPE;
3006
3007
3008 CURSOR C_Delivery_Level_FC
3009 IS
3010 SELECT wnd.delivery_id, wfc.freight_cost_id,
3011 wfc.unit_amount, wfc.currency_code,
3012 wft.freight_cost_type_code
3013 , wfc.conversion_type_code, wfc.conversion_rate
3014 FROM wsh_freight_costs wfc,
3015 wsh_freight_cost_types wft,
3016 wsh_delivery_legs wdl,
3017 wsh_new_deliveries wnd,
3018 wsh_trip_stops wts
3019 WHERE wts.stop_id = p_stop_id and
3020 wts.stop_id = wdl.pick_up_stop_id and
3021 wts.stop_location_id = wnd.initial_pickup_location_id and
3022 wdl.delivery_id = wnd.delivery_id and
3023 wfc.delivery_id = wdl.delivery_id and
3024 wfc.freight_cost_type_id = wft.freight_cost_type_id and
3025 /* H projects: pricing integration csun
3026 pricing engine calculated freight costs are always at delivery detail level,
3027 however those lines also have delivery id populated, so those lines should be
3028 excluded from delivery level FC calculation
3029 */
3030 NVL(wfc.line_type_code, 'CHARGE') in ('PRICE', 'CHARGE') and
3031 NVL(wfc.charge_source_code, 'MANUAL')= 'MANUAL' and
3032 nvl(wnd.shipment_direction,'O') IN ('O','IO')
3033 AND wfc.unit_amount IS NOT NULL -- bug 5447870: skip NULL records
3034 AND wfc.currency_code IS NOT NULL
3035 order by wnd.delivery_id;
3036
3037 temp_Delivery_fc_rec C_Delivery_Level_FC%ROWTYPE;
3038
3039 CURSOR C_Container_Level_FC
3040 IS
3041 SELECT distinct
3042 wfc.delivery_detail_id,
3043 wfc.freight_cost_id,
3044 wfc.unit_amount,
3045 wfc.currency_code,
3046 wft.freight_cost_type_code,
3047 wfc.conversion_type_code,
3048 wfc.conversion_rate
3049 FROM wsh_delivery_assignments_v wda,
3050 wsh_delivery_details wdd,
3051 wsh_freight_costs wfc,
3052 wsh_freight_cost_types wft,
3053 wsh_delivery_legs wdl,
3054 wsh_new_deliveries wnd,
3055 wsh_trip_stops wts
3056 WHERE wts.stop_id = wdl.pick_up_stop_id and
3057 wts.stop_id = p_stop_id and
3058 wts.stop_location_id = wnd.initial_pickup_location_id and
3059 wdl.delivery_id = wnd.delivery_id and
3060 wda.delivery_id = wdl.delivery_id and
3061 wda.delivery_id IS NOT NULL and
3062 wda.parent_delivery_detail_id = wdd.delivery_detail_id and
3063 wdd.container_flag = 'Y' and
3064 wdd.source_code='WSH' and
3065 wfc.delivery_detail_id = wdd.delivery_detail_id and
3066 wfc.freight_cost_type_id = wft.freight_cost_type_id and
3067 NVL(wfc.line_type_code, 'CHARGE') in ('PRICE' ,'CHARGE','TLPRICE','TLCHARGE') and
3068 nvl(wdd.line_direction,'O') IN ('O','IO')
3069 AND wfc.unit_amount IS NOT NULL -- bug 5447870: skip NULL records
3070 AND wfc.currency_code IS NOT NULL;
3071
3072
3073 temp_container_fc_rec C_Container_Level_FC%ROWTYPE;
3074
3075 CURSOR C_Detail_Level_FC
3076 IS
3077 SELECT
3078 wfc.delivery_detail_id,
3079 wfc.freight_cost_id,
3080 wfc.unit_amount,
3081 wfc.currency_code,
3082 wft.freight_cost_type_code
3083 , wfc.conversion_type_code, wfc.conversion_rate
3084 FROM wsh_delivery_assignments_v wda,
3085 wsh_delivery_details wdd,
3086 oe_order_lines_all ol,
3087 wsh_freight_costs wfc,
3088 wsh_freight_cost_types wft,
3089 wsh_delivery_legs wdl,
3090 wsh_new_deliveries wnd,
3091 wsh_trip_stops wts
3092 WHERE wts.stop_id = wdl.pick_up_stop_id and
3093 wts.stop_id = p_stop_id and
3094 wts.stop_location_id = wnd.initial_pickup_location_id and
3095 wdl.delivery_id = wnd.delivery_id and
3096 wda.delivery_id = wdl.delivery_id and
3097 wda.delivery_id IS NOT NULL and
3098 wda.delivery_detail_id = wdd.delivery_detail_id and
3099 wdd.container_flag = 'N' and
3100 ol.line_id = wdd.source_line_id and
3101 wdd.source_code = 'OE' and
3102 wfc.delivery_detail_id = wdd.delivery_detail_id and
3103 wfc.freight_cost_type_id = wft.freight_cost_type_id and
3104 wdd.container_flag = 'N' and
3105 wdd.oe_interfaced_flag = 'N' and
3106 wdd.released_status = 'C' and
3107 NVL(wdd.shipped_quantity, 0) > 0 and
3108 NVL(wfc.line_type_code, 'CHARGE') in ('PRICE' ,'CHARGE','TLPRICE','TLCHARGE') --TKT
3109 AND wfc.unit_amount IS NOT NULL -- bug 5447870: skip NULL records
3110 AND wfc.currency_code IS NOT NULL;
3111
3112
3113 temp_detail_fc_rec C_Detail_Level_FC%ROWTYPE;
3114
3115 l_prorated_freight_cost ProratedCostTabType;
3116 l_return_status VARCHAR2(5);
3117 l_Relevant_Info_Tab RelavantInfoTabType;
3118 l_already_got_info VARCHAR2(1) := 'F';
3119 l_got_container_info VARCHAR2(1) := 'F';
3120 i NUMBER := 0;
3121 l_old_delivery_id NUMBER DEFAULT NULL;
3122 l_all_deliveries_calculated VARCHAR2(1) := 'F';
3123
3124 WSH_TRIP_FC_ERROR EXCEPTION;
3125 WSH_STOP_FC_ERROR EXCEPTION;
3126 WSH_DELIVERY_FC_ERROR EXCEPTION;
3127 WSH_DETAIL_FC_ERROR EXCEPTION;
3128 WSH_CONTAINER_FC_ERROR EXCEPTION;
3129 WSH_FC_GET_INFO_ERR EXCEPTION;
3130
3131 --
3132 l_debug_on BOOLEAN;
3133 --
3134 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'CALCULATE_FREIGHT_COSTS';
3135 --
3136 BEGIN
3137 --
3138 -- Debug Statements
3139 --
3140 --
3141 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
3142 --
3143 IF l_debug_on IS NULL
3144 THEN
3145 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
3146 END IF;
3147 --
3148 IF l_debug_on THEN
3149 WSH_DEBUG_SV.push(l_module_name);
3150 --
3151 WSH_DEBUG_SV.log(l_module_name,'P_STOP_ID',P_STOP_ID);
3152 END IF;
3153 --
3154 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
3155 --
3156 -- Debug Statements
3157 --
3158 IF l_debug_on THEN
3159 WSH_DEBUG_SV.logmsg(l_module_name, 'BEGINNING OF CALCULATE_FREIGHT_COST , STOP_ID:'|| P_STOP_ID );
3160
3161 END IF;
3162 --
3163 OPEN C_Trip_Level_FC;
3164 <<Trip_Loop>>
3165 LOOP
3166 FETCH C_Trip_Level_FC INTO temp_trip_fc_rec;
3167 EXIT Trip_Loop WHEN C_Trip_Level_FC%NOTFOUND;
3168 IF l_already_got_info = 'F' THEN
3169 Get_Relevant_Info(
3170 p_level=> 'TRIP',
3171 p_trip_id => temp_trip_fc_rec.trip_id,
3172 x_Relevant_Info_Tab => l_Relevant_Info_Tab,
3173 x_return_status => l_return_status );
3174 IF l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS THEN
3175 RAISE WSH_FC_GET_INFO_ERR;
3176 END IF;
3177 l_already_got_info := 'T';
3178 END IF;
3179
3180 Calculate_Trip_Level_Cost(
3181 p_trip_id => temp_trip_fc_rec.trip_id,
3182 p_stop_id => p_stop_id,
3183 p_Freight_Cost_Type_Code => temp_trip_fc_rec.freight_cost_type_code,
3184 p_freight_cost_id => temp_trip_fc_rec.freight_cost_id,
3185 p_freight_cost_amount => temp_trip_fc_rec.unit_amount,
3186 p_from_currency_code => temp_trip_fc_rec.currency_code,
3187 p_conversion_type_code => temp_trip_fc_rec.conversion_type_code,
3188 p_conversion_rate => temp_trip_fc_rec.conversion_rate,
3189 p_Relevant_Info_Tab => l_Relevant_Info_Tab,
3190 x_Prorated_Freight_Cost => l_prorated_freight_cost,
3191 x_return_status => l_return_status);
3192 -- check the status
3193 IF l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS THEN
3194 RAISE WSH_TRIP_FC_ERROR;
3195 END IF;
3196 END LOOP Trip_Loop;
3197 CLOSE C_Trip_Level_FC;
3198
3199 -- calculate stop level freight cost
3200 OPEN C_Stop_Level_FC;
3201 <<Stop_Loop>>
3202 LOOP
3203 FETCH C_Stop_Level_FC INTO temp_stop_fc_rec;
3204 EXIT Stop_Loop WHEN C_Stop_Level_FC%NOTFOUND;
3205 IF l_already_got_info = 'F' THEN
3206 Get_Relevant_Info(
3207 p_level=> 'STOP',
3208 p_stop_id => temp_stop_fc_rec.stop_id,
3209 x_Relevant_Info_Tab => l_Relevant_Info_Tab,
3210 x_return_status => l_return_status );
3211 IF l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS THEN
3212 RAISE WSH_FC_GET_INFO_ERR;
3213 END IF;
3214 l_already_got_info := 'T';
3215 END IF;
3216
3217 Calculate_Stop_Level_Cost(
3218 p_stop_id => temp_stop_fc_rec.stop_id,
3219 p_Freight_Cost_Type_Code => temp_stop_fc_rec.freight_cost_type_code,
3220 p_freight_cost_id => temp_stop_fc_rec.freight_cost_id,
3221 p_freight_cost_amount => temp_stop_fc_rec.unit_amount,
3222 p_from_currency_code => temp_stop_fc_rec.currency_code,
3223 p_conversion_type_code => temp_stop_fc_rec.conversion_type_code,
3224 p_conversion_rate => temp_stop_fc_rec.conversion_rate,
3225 p_Relevant_Info_Tab => l_Relevant_Info_Tab,
3226 x_Prorated_freight_cost => l_prorated_freight_cost,
3227 x_return_status => l_return_status);
3228 -- check the status
3229 IF l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS THEN
3230 RAISE WSH_STOP_FC_ERROR;
3231 END IF;
3232 END LOOP Stop_Loop;
3233 CLOSE C_Stop_Level_FC;
3234
3235 l_all_deliveries_calculated := l_already_got_info ;
3236 -- Calculate Delivery level freight cost
3237 OPEN C_Delivery_Level_FC;
3238 <<Delivery_Loop>>
3239 LOOP
3240 FETCH C_Delivery_Level_FC INTO temp_Delivery_fc_rec;
3241 EXIT Delivery_Loop WHEN C_Delivery_Level_FC%NOTFOUND;
3242
3243 IF l_all_deliveries_calculated = 'F' THEN
3244 IF l_old_delivery_id = temp_Delivery_fc_rec.delivery_id THEN
3245 l_already_got_info := 'T';
3246 ELSE
3247 l_old_delivery_id := temp_Delivery_fc_rec.delivery_id;
3248 l_already_got_info := 'F';
3249 END IF;
3250 END IF;
3251 IF l_already_got_info = 'F' THEN
3252 Get_Relevant_Info(
3253 p_level=> 'DELIVERY',
3254 p_delivery_id => temp_Delivery_fc_rec.delivery_id,
3255 x_Relevant_Info_Tab => l_Relevant_Info_Tab,
3256 x_return_status => l_return_status );
3257 IF l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS THEN
3258 RAISE WSH_FC_GET_INFO_ERR;
3259 END IF;
3260 l_already_got_info := 'T';
3261 END IF;
3262
3263 Calculate_Delivery_Level_Cost(
3264 p_delivery_id => temp_Delivery_fc_rec.delivery_id,
3265 p_Freight_Cost_Type_Code => temp_Delivery_fc_rec.freight_cost_type_code,
3266 p_Freight_Cost_Id => temp_Delivery_fc_rec.freight_cost_id,
3267 p_Freight_Cost_Amount => temp_Delivery_fc_rec.unit_amount,
3268 p_From_Currency_Code => temp_Delivery_fc_rec.currency_code,
3269 p_conversion_type_code => temp_Delivery_fc_rec.conversion_type_code,
3270 p_conversion_rate => temp_Delivery_fc_rec.conversion_rate,
3271 p_Relevant_Info_Tab => l_Relevant_Info_Tab,
3272 x_Prorated_Freight_Cost => l_prorated_freight_cost,
3273 x_return_status => l_return_status);
3274 IF l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS THEN
3275 RAISE WSH_DELIVERY_FC_ERROR;
3276 END IF;
3277 END LOOP Delivery_Loop;
3278 CLOSE C_Delivery_Level_FC;
3279
3280 OPEN C_Container_Level_FC;
3281 <<Container_FC_Loop>>
3282 LOOP
3283 l_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
3284 FETCH C_Container_Level_FC INTO temp_container_fc_rec;
3285 EXIT Container_FC_Loop WHEN C_Container_Level_FC%NOTFOUND;
3286 l_got_container_info := 'F';
3287 FOR i in 1 .. l_Relevant_Info_Tab.count LOOP
3288 IF l_Relevant_Info_Tab(i).container_id = temp_container_fc_rec.delivery_detail_id THEN
3289 l_got_container_info := 'T';
3290 exit;
3291 END IF;
3292 END LOOP;
3293 IF l_got_container_info = 'F' THEN
3294 g_container_relationship.delete;
3295 Get_Relevant_Info(
3296 p_level=> 'CONTAINER',
3297 p_container_id => temp_container_fc_rec.delivery_detail_id ,
3298 x_Relevant_Info_Tab => l_Relevant_Info_Tab,
3299 x_return_status => l_return_status );
3300 IF l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS THEN
3301 RAISE WSH_FC_GET_INFO_ERR;
3302 END IF;
3303 END IF;
3304
3305 Calculate_Container_Level_Cost(
3306 p_container_id=>temp_container_fc_rec.delivery_detail_id,
3307 p_Freight_Cost_Type_Code => temp_container_fc_rec.freight_cost_type_code,
3308 p_freight_cost_id=>temp_container_fc_rec.freight_cost_id,
3309 p_freight_cost_amount=>temp_container_fc_rec.unit_amount,
3310 p_from_currency_code=>temp_container_fc_rec.currency_code,
3311 p_conversion_type_code => temp_container_fc_rec.conversion_type_code,
3312 p_conversion_rate => temp_container_fc_rec.conversion_rate,
3313 p_relevant_info_tab=>l_Relevant_Info_Tab,
3314 x_prorated_freight_cost=>l_prorated_freight_cost,
3315 x_return_status=>l_return_status);
3316 IF l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS THEN
3317 RAISE WSH_CONTAINER_FC_ERROR;
3318 END IF;
3319
3320 END LOOP Container_FC_LOOP;
3321 CLOSE C_Container_Level_FC;
3322
3323 -- Calculate Delivery detail level
3324 OPEN C_detail_level_fc;
3325 <<Detail_Loop>>
3326 LOOP
3327 l_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
3328 FETCH C_detail_level_fc INTO temp_detail_fc_rec;
3329 EXIT Detail_Loop WHEN C_detail_level_fc%NOTFOUND;
3330
3331 Calculate_Detail_Level_Cost(
3332 p_delivery_detail_id=>temp_detail_fc_rec.delivery_detail_id,
3333 p_Freight_Cost_Type_Code => temp_detail_fc_rec.freight_cost_type_code,
3334 p_freight_cost_id=>temp_detail_fc_rec.freight_cost_id,
3335 p_freight_cost_amount=>temp_detail_fc_rec.unit_amount,
3336 p_from_currency_code=>temp_detail_fc_rec.currency_code,
3337 p_conversion_type_code => temp_detail_fc_rec.conversion_type_code,
3338 p_conversion_rate => temp_detail_fc_rec.conversion_rate,
3339 p_relevant_info_tab=>l_Relevant_Info_Tab,
3340 x_prorated_freight_cost=>l_prorated_freight_cost,
3341 x_return_status=>l_return_status);
3342 IF l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS THEN
3343 RAISE WSH_DETAIL_FC_ERROR;
3344 END IF;
3345 END LOOP Detail_Loop;
3346 CLOSE C_detail_level_fc;
3347
3348 l_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
3349 Source_Line_Level_Cost(
3350 p_stop_id => p_stop_id,
3351 p_prorated_freight_cost => l_prorated_freight_cost,
3352 x_final_cost => x_freight_costs,
3353 x_return_status => l_return_status
3354 );
3355 IF (l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS) THEN
3356 --
3357 -- Debug Statements
3358 --
3359 IF l_debug_on THEN
3360 WSH_DEBUG_SV.logmsg(l_module_name, 'SOURCE_LINE_LEVEL_COST API FAILED' );
3361 END IF;
3362 --
3363 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
3364 END IF;
3365
3366 IF x_freight_costs.COUNT = 0 THEN
3367 -- this indicates there is not freight cost for this stop
3368 -- process_freight_cost will check this field the second time
3369 -- process_freight_cost is called for the same stop, if it sees
3370 -- x_freight_costs(1).freight_cost_id = -9999, it returns immediately
3371 x_freight_costs(1).freight_cost_id := -9999;
3372 END IF;
3373
3374 --
3375 -- Debug Statements
3376 --
3377 IF l_debug_on THEN
3378 WSH_DEBUG_SV.logmsg(l_module_name, 'END OF CALCULATE_FREIGHT_COST' );
3379 WSH_DEBUG_SV.pop(l_module_name);
3380 END IF;
3381 --
3382 EXCEPTION
3383
3384 WHEN WSH_TRIP_FC_ERROR THEN
3385 --
3386 -- Debug Statements
3387 --
3388 IF l_debug_on THEN
3389 WSH_DEBUG_SV.logmsg(l_module_name, 'CALCULATE_TRIP_LEVEL_COST FAILED' );
3390 END IF;
3391 --
3392 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
3393
3394 --
3395 -- Debug Statements
3396 --
3397 IF l_debug_on THEN
3398 WSH_DEBUG_SV.logmsg(l_module_name,'WSH_TRIP_FC_ERROR exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
3399 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:WSH_TRIP_FC_ERROR');
3400 END IF;
3401 --
3402 WHEN WSH_STOP_FC_ERROR THEN
3403 --
3404 -- Debug Statements
3405 --
3406 IF l_debug_on THEN
3407 WSH_DEBUG_SV.logmsg(l_module_name, 'CALCULATE_STOP_LEVEL_COST FAILED' );
3408 END IF;
3409 --
3410 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
3411
3412 --
3413 -- Debug Statements
3414 --
3415 IF l_debug_on THEN
3416 WSH_DEBUG_SV.logmsg(l_module_name,'WSH_STOP_FC_ERROR exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
3417 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:WSH_STOP_FC_ERROR');
3418 END IF;
3419 --
3420 WHEN WSH_DELIVERY_FC_ERROR THEN
3421 --
3422 -- Debug Statements
3423 --
3424 IF l_debug_on THEN
3425 WSH_DEBUG_SV.logmsg(l_module_name, 'CALCULATE_DELIVERY_LEVEL_COST FAILED' );
3426 END IF;
3427 --
3428 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
3429
3430 --
3431 -- Debug Statements
3432 --
3433 IF l_debug_on THEN
3434 WSH_DEBUG_SV.logmsg(l_module_name,'WSH_DELIVERY_FC_ERROR exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
3435 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:WSH_DELIVERY_FC_ERROR');
3436 END IF;
3437 --
3438 WHEN WSH_CONTAINER_FC_ERROR THEN
3439 --
3440 -- Debug Statements
3441 --
3442 IF l_debug_on THEN
3443 WSH_DEBUG_SV.logmsg(l_module_name, 'CALCULATE_CONTAINER_LEVEL_COST FAILED' );
3444 END IF;
3445 --
3446 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
3447
3448 --
3449 -- Debug Statements
3450 --
3451 IF l_debug_on THEN
3452 WSH_DEBUG_SV.logmsg(l_module_name,'WSH_CONTAINER_FC_ERROR exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
3453 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:WSH_CONTAINER_FC_ERROR');
3454 END IF;
3455 --
3456 WHEN WSH_DETAIL_FC_ERROR THEN
3457 --
3458 -- Debug Statements
3459 --
3460 IF l_debug_on THEN
3461 WSH_DEBUG_SV.logmsg(l_module_name, 'CALCULATE_DETAIL_LEVEL_COST FAILED' );
3462 END IF;
3463 --
3464 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
3465
3466 --
3467 -- Debug Statements
3468 --
3469 IF l_debug_on THEN
3470 WSH_DEBUG_SV.logmsg(l_module_name,'WSH_DETAIL_FC_ERROR exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
3471 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:WSH_DETAIL_FC_ERROR');
3472 END IF;
3473 --
3474 WHEN WSH_FC_GET_INFO_ERR THEN
3475 --
3476 -- Debug Statements
3477 --
3478 IF l_debug_on THEN
3479 WSH_DEBUG_SV.logmsg(l_module_name, 'GET RELEVANT INFO FAILED' );
3480 END IF;
3481 --
3482 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
3483
3484 --
3485 -- Debug Statements
3486 --
3487 IF l_debug_on THEN
3488 WSH_DEBUG_SV.logmsg(l_module_name,'WSH_FC_GET_INFO_ERR exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
3489 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:WSH_FC_GET_INFO_ERR');
3490 END IF;
3491 --
3492 WHEN others THEN
3493 WSH_UTIL_CORE.Default_Handler('WSH_FC_INTERFACE.Calculate_Freight_Costs');
3494 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
3495
3496 --
3497 -- Debug Statements
3498 --
3499 IF l_debug_on THEN
3500 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
3501 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
3502 END IF;
3503 --
3504 END Calculate_Freight_Costs;
3505
3506 --HVOP heali
3507 PROCEDURE Process_Freight_Costs(
3508 p_stop_id IN NUMBER
3509 , p_start_index IN NUMBER
3510 , p_line_id_tbl IN OE_WSH_BULK_GRP.T_NUM
3511 , x_freight_costs_all IN OUT NOCOPY OMInterfaceCostTabType
3512 , x_freight_costs IN OUT NOCOPY OE_Ship_Confirmation_Pub.Ship_Adj_Rec_Type
3513 , x_end_index OUT NOCOPY NUMBER
3514 , x_return_status OUT NOCOPY VARCHAR2
3515 ) IS
3516 i NUMBER;
3517 j NUMBER;
3518 l_return_status VARCHAR2(30);
3519 l_table_id NUMBER;
3520 l_delivery_id NUMBER;
3521 l_stop_id NUMBER;
3522 l_trip_id NUMBER;
3523
3524 Calculate_failed EXCEPTION;
3525 l_line_index NUMBER;
3526
3527 l_debug_on BOOLEAN;
3528 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'PROCESS_FREIGHT_COSTS';
3529
3530 BEGIN
3531 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
3532 --
3533 IF l_debug_on IS NULL THEN
3534 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
3535 END IF;
3536 --
3537 IF l_debug_on THEN
3538 WSH_DEBUG_SV.push(l_module_name);
3539 WSH_DEBUG_SV.log(l_module_name,'P_STOP_ID',P_STOP_ID);
3540 WSH_DEBUG_SV.log(l_module_name,'P_start_index',p_start_index);
3541 WSH_DEBUG_SV.log(l_module_name,'P_LINE_ID_TBL.count',P_LINE_ID_TBL.count);
3542 END IF;
3543 --
3544 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
3545
3546
3547 IF x_Freight_Costs_ALL.count = 0 THEN
3548 Calculate_Freight_Costs(
3549 p_stop_id => p_stop_id,
3550 x_freight_costs => x_Freight_Costs_ALL,
3551 x_return_status => l_return_status
3552 );
3553 IF (l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS) THEN
3554 RAISE Calculate_failed;
3555 END IF;
3556 --
3557 IF l_debug_on THEN
3558 WSH_DEBUG_SV.logmsg(l_module_name, 'X_FREIGHT_COSTS_ALL.COUNT = '|| X_FREIGHT_COSTS_ALL.COUNT );
3559 END IF;
3560 ELSE
3561 IF l_debug_on THEN
3562 WSH_DEBUG_SV.logmsg(l_module_name, 'SKIP CALCULATE_FREIGHT_COSTS' );
3563 END IF;
3564 END IF;
3565
3566
3567 IF x_Freight_Costs_ALL(1).freight_cost_id <> -9999 THEN
3568 IF l_debug_on THEN
3569 WSH_DEBUG_SV.logmsg(l_module_name, 'NUMBER OF ELEMENTS IN P_LINE_ID_TBL IS : ' || P_LINE_ID_TBL.COUNT );
3570 END IF;
3571
3572 <<Prorate_Loop>>
3573 FOR i IN 1 .. x_Freight_Costs_All.Count LOOP
3574 IF l_debug_on THEN
3575 WSH_DEBUG_SV.logmsg(l_module_name, 'I = '||I );
3576 END IF;
3577
3578 l_line_index := -9999;
3579
3580 -- assign line index to corresponding cost record
3581 <<Line_Index_Loop>>
3582 FOR l_line_counter IN p_start_index .. p_line_id_tbl.COUNT
3583 LOOP
3584 IF l_debug_on THEN
3585 WSH_DEBUG_SV.logmsg(l_module_name,'P_LINE_ID.TBL('||L_LINE_COUNTER||'):'||P_LINE_ID_TBL(L_LINE_COUNTER));
3586 END IF;
3587
3588 --HVOP heali
3589
3590 IF (x_Freight_Costs_ALL(i).source_line_id = p_line_id_tbl(l_line_counter))THEN
3591 l_table_id := x_freight_costs.line_id.count + 1;
3592 l_line_index := l_line_counter;
3593 EXIT Line_Index_Loop;
3594 END IF;
3595 END LOOP Line_Index_Loop;
3596
3597 IF (l_line_index = -9999) THEN
3598 GOTO Next_Cost_Record;
3599 END IF;
3600
3601 IF l_debug_on THEN
3602 WSH_DEBUG_SV.logmsg(l_module_name, 'SOURCE LINE ID = '||X_FREIGHT_COSTS_ALL(I).SOURCE_LINE_ID );
3603 WSH_DEBUG_SV.log(l_module_name, 'l_table_id',l_table_id);
3604 END IF;
3605
3606 x_freight_costs.cost_id.extend;
3607 x_freight_costs.cost_id(l_table_id) := x_Freight_Costs_ALL(i).freight_cost_id;
3608 x_freight_costs.automatic_flag.extend;
3609 x_freight_costs.automatic_flag(l_table_id) := 'N';
3610 x_freight_costs.list_line_type_code.extend;
3611 x_freight_costs.list_line_type_code(l_table_id) := 'COST';
3612 x_freight_costs.charge_type_code.extend;
3613 x_freight_costs.charge_type_code(l_table_id) := x_Freight_Costs_ALL(i).freight_cost_type_code;
3614 x_freight_costs.header_id.extend;
3615 x_freight_costs.header_id(l_table_id) := x_Freight_Costs_ALL(i).source_header_id;
3616 x_freight_costs.line_id.extend;
3617 x_freight_costs.line_id(l_table_id) := x_Freight_Costs_ALL(i).source_line_id;
3618 x_freight_costs.adjusted_amount.extend;
3619 x_freight_costs.adjusted_amount(l_table_id) := x_Freight_Costs_ALL(i).amount;
3620 x_freight_costs.arithmetic_operator.extend;
3621 x_freight_costs.arithmetic_operator(l_table_id) := 'AMT';
3622 x_freight_costs.operation.extend;
3623 x_freight_costs.operation(l_table_id) := OE_GLOBALS.G_OPR_CREATE;
3624
3625
3626 IF l_debug_on THEN
3627 WSH_DEBUG_SV.log(l_module_name,'cost_id',x_freight_costs.cost_id(l_table_id));
3628 WSH_DEBUG_SV.log(l_module_name,'automatic_flag',x_freight_costs.automatic_flag(l_table_id));
3629 WSH_DEBUG_SV.log(l_module_name,'list_line_type_code',x_freight_costs.list_line_type_code(l_table_id));
3630 WSH_DEBUG_SV.log(l_module_name,'charge_type_code',x_freight_costs.charge_type_code(l_table_id));
3631 WSH_DEBUG_SV.log(l_module_name,'header_id',x_freight_costs.header_id(l_table_id));
3632 WSH_DEBUG_SV.log(l_module_name,'line_id',x_freight_costs.line_id(l_table_id));
3633 WSH_DEBUG_SV.log(l_module_name,'adjusted_amount',x_freight_costs.adjusted_amount(l_table_id));
3634 WSH_DEBUG_SV.log(l_module_name,'arithmetic_operator',x_freight_costs.arithmetic_operator(l_table_id));
3635 WSH_DEBUG_SV.log(l_module_name,'operation',x_freight_costs.operation(l_table_id));
3636 END IF;
3637
3638
3639 <<Next_Cost_Record>>
3640 NULL;
3641 END LOOP Prorate_Loop;
3642 END IF;
3643
3644
3645
3646 IF l_debug_on THEN
3647 WSH_DEBUG_SV.pop(l_module_name);
3648 END IF;
3649 EXCEPTION
3650 WHEN Calculate_failed THEN
3651 IF l_debug_on THEN
3652 WSH_DEBUG_SV.logmsg(l_module_name, 'CALCULATE_FREIGHT_COST API FAILED' );
3653 END IF;
3654 --
3655 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
3656
3657 IF l_debug_on THEN
3658 WSH_DEBUG_SV.logmsg(l_module_name,'CALCULATE_FAILED exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
3659 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:CALCULATE_FAILED');
3660 END IF;
3661
3662 WHEN others THEN
3663 WSH_UTIL_CORE.Default_Handler('WSH_FC_INTERFACE.Process_Freight_Costs');
3664 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
3665 --
3666 IF l_debug_on THEN
3667 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
3668 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
3669 END IF;
3670 END Process_Freight_Costs;
3671
3672
3673 FUNCTION Prorate_Freight_Charge(
3674 p_delivery_detail_id IN NUMBER
3675 , p_charge_id IN NUMBER
3676 )
3677 RETURN NUMBER
3678 IS
3679
3680 CURSOR C_Details_Assigned(p_source_line_id NUMBER)
3681 IS
3682 SELECT delivery_detail_id
3683 FROM wsh_delivery_details
3684 WHERE source_line_id = p_source_line_id
3685 AND source_code = 'OE'
3686 AND container_flag = 'N';
3687
3688 l_number_details_assigned NUMBER := 0;
3689 l_line_id NUMBER;
3690 l_charge_line_id NUMBER;
3691 l_detail_id NUMBER;
3692 l_amount NUMBER;
3693 l_uom VARCHAR2(3);
3694 l_unit NUMBER;
3695 l_unit_in_st_uom NUMBER;
3696 l_standard_uom VARCHAR2(3);
3697 l_total_units NUMBER := 0;
3698 l_distributed_charge NUMBER := 0;
3699 l_check NUMBER := 0;
3700 l_round_distributed_charge NUMBER := 0;
3701 l_currency_code VARCHAR2(15);
3702 l_rest_amount NUMBER;
3703 l_temp_table_id NUMBER;
3704 Input_Inconsistent EXCEPTION;
3705 l_return_status VARCHAR2(10);
3706 l_inventory_item_id NUMBER;
3707 --
3708 l_debug_on BOOLEAN;
3709 --
3710 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'PRORATE_FREIGHT_CHARGE';
3711 --
3712 BEGIN
3713 -- User delivery detail to find source line id instead of selecting from
3714 -- oe_charge_lines_v because each source_line_id could have multiple charges
3715 --
3716 -- Debug Statements
3717 --
3718 --
3719 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
3720 --
3721 IF l_debug_on IS NULL
3722 THEN
3723 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
3724 END IF;
3725 --
3726 IF l_debug_on THEN
3727 WSH_DEBUG_SV.push(l_module_name);
3728 --
3729 WSH_DEBUG_SV.log(l_module_name,'P_DELIVERY_DETAIL_ID',P_DELIVERY_DETAIL_ID);
3730 WSH_DEBUG_SV.log(l_module_name,'P_CHARGE_ID',P_CHARGE_ID);
3731 END IF;
3732 --
3733 SELECT wsh.source_line_id
3734 INTO l_line_id
3735 FROM wsh_delivery_details wsh, oe_order_lines_all l
3736 WHERE wsh.delivery_detail_id = p_delivery_detail_id AND
3737 wsh.source_line_id = l.line_id and
3738 wsh.source_code = 'OE';
3739
3740 SELECT COUNT(*)
3741 INTO l_check
3742 FROM oe_charge_lines_v
3743 WHERE line_id = l_line_id AND charge_id = p_charge_id;
3744
3745 IF (l_check = 0) THEN
3746 RAISE Input_Inconsistent;
3747 END IF;
3748
3749 -- first, get the number of delivery details associated with this source_line_id
3750 SELECT COUNT(*)
3751 INTO l_number_details_assigned
3752 FROM wsh_delivery_details
3753 WHERE source_line_id = l_line_id and
3754 source_code = 'OE';
3755
3756 -- Fetch uom of the first detail as standard uom
3757 OPEN C_Details_Assigned(l_line_id);
3758 FETCH C_Details_Assigned INTO l_detail_id;
3759 CLOSE C_Details_Assigned;
3760
3761 SELECT requested_quantity_uom
3762 INTO l_standard_uom
3763 FROM wsh_delivery_details
3764 WHERE delivery_detail_id = l_detail_id;
3765
3766 OPEN C_Details_Assigned(l_line_id);
3767 LOOP
3768 FETCH C_Details_Assigned INTO l_detail_id;
3769 EXIT WHEN C_Details_Assigned%NOTFOUND;
3770
3771 SELECT shipped_quantity, requested_quantity_uom, inventory_item_id
3772 INTO l_unit, l_uom , l_inventory_item_id
3773 FROM wsh_delivery_details
3774 WHERE delivery_detail_id = l_detail_id;
3775
3776 --
3777 -- Debug Statements
3778 --
3779 IF l_debug_on THEN
3780 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_WV_UTILS.CONVERT_UOM',WSH_DEBUG_SV.C_PROC_LEVEL);
3781 END IF;
3782 -- 3935583
3783 l_unit_in_st_uom := WSH_WV_UTILS.convert_uom(
3784 from_uom => l_uom,
3785 to_uom => l_standard_uom,
3786 quantity => l_unit,
3787 item_id => l_inventory_item_id);
3788 l_total_units := l_total_units + l_unit_in_st_uom;
3789 END LOOP;
3790
3791 CLOSE C_Details_Assigned;
3792
3793 SELECT charge_amount, currency_code
3794 INTO l_amount, l_currency_code
3795 FROM oe_charge_lines_v
3796 WHERE charge_id = p_charge_id;
3797
3798 SELECT shipped_quantity, requested_quantity_uom , inventory_item_id
3799 INTO l_unit, l_uom , l_inventory_item_id
3800 FROM wsh_delivery_details
3801 WHERE delivery_detail_id = p_delivery_detail_id;
3802
3803 --
3804 -- Debug Statements
3805 --
3806 IF l_debug_on THEN
3807 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_WV_UTILS.CONVERT_UOM',WSH_DEBUG_SV.C_PROC_LEVEL);
3808 END IF;
3809 --
3810 -- 3935583
3811 l_unit_in_st_uom := WSH_WV_UTILS.convert_uom(
3812 from_uom => l_uom,
3813 to_uom => l_standard_uom,
3814 quantity => l_unit,
3815 item_id => l_inventory_item_id);
3816
3817 l_distributed_charge := l_amount * (l_unit_in_st_uom/l_total_units);
3818 Round_Cost_Amount(l_distributed_charge, l_Currency_Code, l_round_distributed_charge, l_return_status);
3819
3820 --
3821 -- Debug Statements
3822 --
3823 IF l_debug_on THEN
3824 WSH_DEBUG_SV.pop(l_module_name);
3825 END IF;
3826 --
3827 RETURN(l_round_distributed_charge);
3828
3829 EXCEPTION
3830 WHEN input_inconsistent THEN
3831 --
3832 -- Debug Statements
3833 --
3834 IF l_debug_on THEN
3835 WSH_DEBUG_SV.logmsg(l_module_name,'INPUT_INCONSISTENT exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
3836 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:INPUT_INCONSISTENT');
3837 END IF;
3838 --
3839 RETURN (-9999);
3840 --WSH_UTIL_CORE.println('The parameter passed in conflict with each other');
3841
3842 END Prorate_Freight_Charge;
3843
3844
3845 -- Name Get_Cost_Factor
3846 -- Purpose dummy function
3847 -- Since TPA does not support deleting obsolete APIs,
3848 -- this function needs to remain in this package
3849 -- (bug 1948149).
3850 --
3851 -- TPA Selector WSH_TPA_SELECTOR_PKG.FreightCostTP
3852 FUNCTION Get_Cost_Factor(
3853 p_delivery_id IN NUMBER
3854 , p_container_instance_id IN NUMBER
3855 , x_return_status OUT NOCOPY VARCHAR2
3856 ) RETURN VARCHAR2 IS
3857 --
3858 l_debug_on BOOLEAN;
3859 --
3860 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'GET_COST_FACTOR';
3861 --
3862 BEGIN
3863 --
3864 -- Debug Statements
3865 --
3866 --
3867 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
3868 --
3869 IF l_debug_on IS NULL
3870 THEN
3871 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
3872 END IF;
3873 --
3874 IF l_debug_on THEN
3875 WSH_DEBUG_SV.push(l_module_name);
3876 --
3877 WSH_DEBUG_SV.log(l_module_name,'P_DELIVERY_ID',P_DELIVERY_ID);
3878 WSH_DEBUG_SV.log(l_module_name,'P_CONTAINER_INSTANCE_ID',P_CONTAINER_INSTANCE_ID);
3879 END IF;
3880 --
3881 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
3882 --
3883 -- Debug Statements
3884 --
3885 IF l_debug_on THEN
3886 WSH_DEBUG_SV.pop(l_module_name);
3887 END IF;
3888 --
3889 RETURN '';
3890 END Get_Cost_Factor;
3891
3892
3893 END WSH_FC_INTERFACE_PKG;
3894