[Home] [Help]
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;
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
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;
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;
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: --
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
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: --
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
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: --
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
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: --
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
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: --
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
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: --
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
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: --
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
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: --
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;
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
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:
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;
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;
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: --
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: --
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
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: --
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;
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: --
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
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:
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
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;
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;
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;
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: --
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: --
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
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: --
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:
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
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;
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;
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;
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: --
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: --
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
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: --
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:
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
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;
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: --
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
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: --
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;
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: --
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
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: --
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
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
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
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
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
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
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
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
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: --
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
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;
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;
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;
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;
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;
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: <
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
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:
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;
3316: -- Calculate Delivery detail level
3317: OPEN C_detail_level_fc;
3318: <
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(
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;
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,
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
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
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: --
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: --
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: --
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: --
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: --
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: --
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
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: --
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
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');
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);
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');
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:
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