DBA Data[Home] [Help]

PACKAGE BODY: APPS.WSH_FC_INTERFACE_PKG

Source


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