[Home] [Help]
1367:
1368: -- added by giyer
1369: IF l_exception_type = 220 THEN --Item Mode Incompatibility
1370: IF P_LINE_NUM = 1 THEN
1371: fnd_message.set_name('MST', 'MST_EXCEP_TOKEN_220_1');
1372: --Trip_number, mode_of_transport
1373: SELECT mt.trip_number, wlk.meaning
1374: INTO l_id1, l_temp
1375: FROM mst_trips mt, wsh_lookups wlk
1376: WHERE plan_id = P_PLAN_ID
1377: AND trip_id = P_TRIP_ID
1378: and wlk.lookup_type = 'WSH_MODE_OF_TRANSPORT'
1379: and mt.mode_of_transport = wlk.lookup_code;
1380: fnd_message.set_token('TRIP_NUMBER', l_id1);
1381: fnd_message.set_token('MODE_OF_TRANSPORT', l_temp);
1382: -- item_description
1383: select description, med.delivery_detail_id
1384: into l_temp1, l_id2
1377: AND trip_id = P_TRIP_ID
1378: and wlk.lookup_type = 'WSH_MODE_OF_TRANSPORT'
1379: and mt.mode_of_transport = wlk.lookup_code;
1380: fnd_message.set_token('TRIP_NUMBER', l_id1);
1381: fnd_message.set_token('MODE_OF_TRANSPORT', l_temp);
1382: -- item_description
1383: select description, med.delivery_detail_id
1384: into l_temp1, l_id2
1385: from mtl_system_items_tl msitl, mst_delivery_details mdd, mst_exception_details med
1389: and med.plan_id = mdd.plan_id
1390: and mdd.inventory_item_id = msitl.inventory_item_id
1391: and mdd.organization_id = msitl.organization_id
1392: and msitl.language = userenv('LANG');
1393: fnd_message.set_token('DELIVERY_DETAIL_ID', l_id2);
1394: fnd_message.set_token('ITEM_DESCRIPTION', l_temp1);
1395: l_message := fnd_message.get;
1396: END IF;
1397:
1390: and mdd.inventory_item_id = msitl.inventory_item_id
1391: and mdd.organization_id = msitl.organization_id
1392: and msitl.language = userenv('LANG');
1393: fnd_message.set_token('DELIVERY_DETAIL_ID', l_id2);
1394: fnd_message.set_token('ITEM_DESCRIPTION', l_temp1);
1395: l_message := fnd_message.get;
1396: END IF;
1397:
1398: ELSIF l_exception_type = 221 THEN --Item Carrier Incompatibility
1391: and mdd.organization_id = msitl.organization_id
1392: and msitl.language = userenv('LANG');
1393: fnd_message.set_token('DELIVERY_DETAIL_ID', l_id2);
1394: fnd_message.set_token('ITEM_DESCRIPTION', l_temp1);
1395: l_message := fnd_message.get;
1396: END IF;
1397:
1398: ELSIF l_exception_type = 221 THEN --Item Carrier Incompatibility
1399: IF P_LINE_NUM = 1 THEN
1396: END IF;
1397:
1398: ELSIF l_exception_type = 221 THEN --Item Carrier Incompatibility
1399: IF P_LINE_NUM = 1 THEN
1400: fnd_message.set_name('MST', 'MST_EXCEP_TOKEN_221_1');
1401: --Trip_number
1402: SELECT trip_number, carrier_id
1403: INTO l_id1, l_id2
1404: FROM mst_trips
1418: --carrier name
1419: SELECT freight_code into l_temp1
1420: FROM wsh_carriers
1421: WHERE carrier_id = l_id2;
1422: fnd_message.set_token('TRIP_NUMBER', l_id1);
1423: fnd_message.set_token('DELIVERY_DETAIL_ID', l_id3);
1424: fnd_message.set_token('CARRIER_NAME', l_temp1);
1425: fnd_message.set_token('ITEM_DESCRIPTION', l_temp);
1426: l_message := fnd_message.get;
1419: SELECT freight_code into l_temp1
1420: FROM wsh_carriers
1421: WHERE carrier_id = l_id2;
1422: fnd_message.set_token('TRIP_NUMBER', l_id1);
1423: fnd_message.set_token('DELIVERY_DETAIL_ID', l_id3);
1424: fnd_message.set_token('CARRIER_NAME', l_temp1);
1425: fnd_message.set_token('ITEM_DESCRIPTION', l_temp);
1426: l_message := fnd_message.get;
1427: END IF;
1420: FROM wsh_carriers
1421: WHERE carrier_id = l_id2;
1422: fnd_message.set_token('TRIP_NUMBER', l_id1);
1423: fnd_message.set_token('DELIVERY_DETAIL_ID', l_id3);
1424: fnd_message.set_token('CARRIER_NAME', l_temp1);
1425: fnd_message.set_token('ITEM_DESCRIPTION', l_temp);
1426: l_message := fnd_message.get;
1427: END IF;
1428:
1421: WHERE carrier_id = l_id2;
1422: fnd_message.set_token('TRIP_NUMBER', l_id1);
1423: fnd_message.set_token('DELIVERY_DETAIL_ID', l_id3);
1424: fnd_message.set_token('CARRIER_NAME', l_temp1);
1425: fnd_message.set_token('ITEM_DESCRIPTION', l_temp);
1426: l_message := fnd_message.get;
1427: END IF;
1428:
1429: ELSIF l_exception_type = 222 THEN --Ship Set Violation
1422: fnd_message.set_token('TRIP_NUMBER', l_id1);
1423: fnd_message.set_token('DELIVERY_DETAIL_ID', l_id3);
1424: fnd_message.set_token('CARRIER_NAME', l_temp1);
1425: fnd_message.set_token('ITEM_DESCRIPTION', l_temp);
1426: l_message := fnd_message.get;
1427: END IF;
1428:
1429: ELSIF l_exception_type = 222 THEN --Ship Set Violation
1430: IF P_LINE_NUM = 1 THEN
1427: END IF;
1428:
1429: ELSIF l_exception_type = 222 THEN --Ship Set Violation
1430: IF P_LINE_NUM = 1 THEN
1431: fnd_message.set_name('MST', 'MST_EXCEP_TOKEN_222_1');
1432: --Ship_set
1433: select number1
1434: into l_id1
1435: from mst_exception_details med
1438: -- Ship_set Name
1439: select set_name into l_temp
1440: from oe_sets os
1441: where os.set_id=l_id1;
1442: fnd_message.set_token('SHIP_SET', l_temp);
1443: l_message := fnd_message.get;
1444: END IF;
1445:
1446: ELSIF l_exception_type = 223 THEN -- Arrival Set Violation
1439: select set_name into l_temp
1440: from oe_sets os
1441: where os.set_id=l_id1;
1442: fnd_message.set_token('SHIP_SET', l_temp);
1443: l_message := fnd_message.get;
1444: END IF;
1445:
1446: ELSIF l_exception_type = 223 THEN -- Arrival Set Violation
1447: IF P_LINE_NUM = 1 THEN
1444: END IF;
1445:
1446: ELSIF l_exception_type = 223 THEN -- Arrival Set Violation
1447: IF P_LINE_NUM = 1 THEN
1448: fnd_message.set_name('MST', 'MST_EXCEP_TOKEN_223_1');
1449: --Arrival_set
1450: select number1
1451: into l_id1
1452: from mst_exception_details med
1455: --Arrival_set Name
1456: select set_name into l_temp
1457: from oe_sets os
1458: where os.set_id=l_id1;
1459: fnd_message.set_token('ARRIVAL_SET', l_temp);
1460: l_message := fnd_message.get;
1461: END IF;
1462:
1463: ELSIF l_exception_type = 301 THEN --Carrier Commitment Underutilization
1456: select set_name into l_temp
1457: from oe_sets os
1458: where os.set_id=l_id1;
1459: fnd_message.set_token('ARRIVAL_SET', l_temp);
1460: l_message := fnd_message.get;
1461: END IF;
1462:
1463: ELSIF l_exception_type = 301 THEN --Carrier Commitment Underutilization
1464: IF P_LINE_NUM = 1 THEN
1461: END IF;
1462:
1463: ELSIF l_exception_type = 301 THEN --Carrier Commitment Underutilization
1464: IF P_LINE_NUM = 1 THEN
1465: fnd_message.set_name('MST', 'MST_EXCEP_TOKEN_301_1');
1466: --start_date, end_date, shortfall
1467: select carrier_id
1468: , fnd_date.DATE_TO_CHARDATE(date1)
1469: , fnd_date.DATE_TO_CHARDATE(date2)
1478: where med.exception_detail_id = p_exception_detail_id
1479: and med.number1 = fsrr.rule_id
1480: and med.number1 = fsl.rule_id
1481: and med.plan_id=p_plan_id;
1482: fnd_message.set_token('START_DATE', l_temp1);
1483: fnd_message.set_token('END_DATE', l_temp2);
1484: fnd_message.set_token('SHORTFALL', l_temp3);
1485: --Carrier name
1486: SELECT freight_code INTO l_temp
1479: and med.number1 = fsrr.rule_id
1480: and med.number1 = fsl.rule_id
1481: and med.plan_id=p_plan_id;
1482: fnd_message.set_token('START_DATE', l_temp1);
1483: fnd_message.set_token('END_DATE', l_temp2);
1484: fnd_message.set_token('SHORTFALL', l_temp3);
1485: --Carrier name
1486: SELECT freight_code INTO l_temp
1487: FROM wsh_carriers
1480: and med.number1 = fsl.rule_id
1481: and med.plan_id=p_plan_id;
1482: fnd_message.set_token('START_DATE', l_temp1);
1483: fnd_message.set_token('END_DATE', l_temp2);
1484: fnd_message.set_token('SHORTFALL', l_temp3);
1485: --Carrier name
1486: SELECT freight_code INTO l_temp
1487: FROM wsh_carriers
1488: WHERE carrier_id = l_id2;
1485: --Carrier name
1486: SELECT freight_code INTO l_temp
1487: FROM wsh_carriers
1488: WHERE carrier_id = l_id2;
1489: fnd_message.set_token('CARRIER', l_temp);
1490: --get Lane_Number
1491: select LANE
1492: into l_temp
1493: from(
1502: where med.lane_set_id = flg.lane_group_id
1503: and med.exception_detail_id=p_exception_detail_id
1504: and med.plan_id=p_plan_id);
1505: --as per dld, Lane_Number = fl.lane_number or flg.name whichever is not null
1506: fnd_message.set_token('LANE_NAME', l_temp);
1507: l_message := fnd_message.get;
1508: END IF;
1509:
1510: ELSIF l_exception_type = 406 THEN --Carrier Facility Appointment violation
1503: and med.exception_detail_id=p_exception_detail_id
1504: and med.plan_id=p_plan_id);
1505: --as per dld, Lane_Number = fl.lane_number or flg.name whichever is not null
1506: fnd_message.set_token('LANE_NAME', l_temp);
1507: l_message := fnd_message.get;
1508: END IF;
1509:
1510: ELSIF l_exception_type = 406 THEN --Carrier Facility Appointment violation
1511: IF P_LINE_NUM = 1 THEN
1508: END IF;
1509:
1510: ELSIF l_exception_type = 406 THEN --Carrier Facility Appointment violation
1511: IF P_LINE_NUM = 1 THEN
1512: fnd_message.set_name('MST', 'MST_EXCEP_TOKEN_406_1');
1513: --facility_description
1514: select flp.facility_code
1515: into l_temp
1516: from fte_location_parameters flp, mst_trip_stops mts, mst_exception_details med
1518: and mts.stop_location_id = flp.location_id
1519: and med.exception_detail_id = p_exception_detail_id
1520: and med.plan_id = mts.plan_id
1521: and med.plan_id = p_plan_id;
1522: fnd_message.set_token('FACILITY_DESCRIPTION', l_temp);
1523: --trip_number,carrier_name
1524: SELECT trip_number, carrier_id
1525: INTO l_id1, l_id2
1526: FROM mst_trips
1529: --carrier name
1530: SELECT freight_code INTO l_temp
1531: FROM wsh_carriers
1532: WHERE carrier_id = l_id2;
1533: fnd_message.set_token('CARRIER_NAME', l_temp);
1534: fnd_message.set_token('TRIP_NUMBER', l_id1);
1535: l_message := fnd_message.get;
1536: END IF;
1537:
1530: SELECT freight_code INTO l_temp
1531: FROM wsh_carriers
1532: WHERE carrier_id = l_id2;
1533: fnd_message.set_token('CARRIER_NAME', l_temp);
1534: fnd_message.set_token('TRIP_NUMBER', l_id1);
1535: l_message := fnd_message.get;
1536: END IF;
1537:
1538: ELSIF l_exception_type = 602 THEN --Item Vehicle Incompatibility
1531: FROM wsh_carriers
1532: WHERE carrier_id = l_id2;
1533: fnd_message.set_token('CARRIER_NAME', l_temp);
1534: fnd_message.set_token('TRIP_NUMBER', l_id1);
1535: l_message := fnd_message.get;
1536: END IF;
1537:
1538: ELSIF l_exception_type = 602 THEN --Item Vehicle Incompatibility
1539: IF P_LINE_NUM = 1 THEN
1536: END IF;
1537:
1538: ELSIF l_exception_type = 602 THEN --Item Vehicle Incompatibility
1539: IF P_LINE_NUM = 1 THEN
1540: fnd_message.set_name('MST', 'MST_EXCEP_TOKEN_602_1');
1541: --Trip_number, item_description,vehicle_type
1542: --Trip_number
1543: SELECT mt.trip_number,vehicle_type_id
1544: INTO l_id1,l_id2
1544: INTO l_id1,l_id2
1545: FROM mst_trips mt
1546: WHERE plan_id = P_PLAN_ID
1547: AND trip_id = P_TRIP_ID;
1548: fnd_message.set_token('TRIP_NUMBER', l_id1);
1549: -- item_description
1550: select msitl.description into l_temp1
1551: from mtl_system_items_tl msitl, mst_delivery_details mdd, mst_exception_details med
1552: where med.plan_id=p_plan_id
1555: and med.delivery_detail_id = mdd.delivery_detail_id
1556: and mdd.inventory_item_id = msitl.inventory_item_id
1557: and mdd.organization_id = msitl.organization_id
1558: and msitl.language = userenv('LANG');
1559: fnd_message.set_token('ITEM_DESCRIPTION', l_temp1);
1560: -- vehicle_type
1561: SELECT msikfv.concatenated_segments
1562: into l_temp
1563: FROM mtl_system_items_kfv msikfv, fte_vehicle_types fvt
1563: FROM mtl_system_items_kfv msikfv, fte_vehicle_types fvt
1564: WHERE fvt.vehicle_type_id = l_id2
1565: AND fvt.organization_id = msikfv.organization_id
1566: AND fvt.inventory_item_id = msikfv.inventory_item_id;
1567: fnd_message.set_token('VEHICLE_TYPE', l_temp);
1568: l_message := fnd_message.get;
1569: END IF;
1570:
1571: ELSIF l_exception_type = 705 THEN --Facility Item Incompatibility
1564: WHERE fvt.vehicle_type_id = l_id2
1565: AND fvt.organization_id = msikfv.organization_id
1566: AND fvt.inventory_item_id = msikfv.inventory_item_id;
1567: fnd_message.set_token('VEHICLE_TYPE', l_temp);
1568: l_message := fnd_message.get;
1569: END IF;
1570:
1571: ELSIF l_exception_type = 705 THEN --Facility Item Incompatibility
1572: IF P_LINE_NUM = 1 THEN
1569: END IF;
1570:
1571: ELSIF l_exception_type = 705 THEN --Facility Item Incompatibility
1572: IF P_LINE_NUM = 1 THEN
1573: fnd_message.set_name('MST', 'MST_EXCEP_TOKEN_705_1');
1574: --Trip_Number, Item_description, facility_description
1575: --Trip_number
1576: SELECT mt.trip_number
1577: INTO l_id1
1577: INTO l_id1
1578: FROM mst_trips mt
1579: WHERE plan_id = P_PLAN_ID
1580: AND trip_id = P_TRIP_ID;
1581: fnd_message.set_token('TRIP_NUMBER', l_id1);
1582: -- item_description
1583: select msitl.description into l_temp1
1584: from mtl_system_items_tl msitl, mst_delivery_details mdd, mst_exception_details med
1585: where med.plan_id=p_plan_id
1588: and med.delivery_detail_id = mdd.delivery_detail_id
1589: and mdd.inventory_item_id = msitl.inventory_item_id
1590: and mdd.organization_id = msitl.organization_id
1591: and msitl.language = userenv('LANG');
1592: fnd_message.set_token('ITEM_DESCRIPTION', l_temp1);
1593: -- Facility_Description
1594: select flp.description
1595: into l_temp
1596: from fte_location_parameters flp, mst_exception_details med
1597: where
1598: med.location_id = flp.location_id
1599: and med.plan_id=p_plan_id
1600: and med.exception_detail_id=p_exception_detail_id;
1601: fnd_message.set_token('FACILITY_DESCRIPTION', l_temp);
1602: l_message := fnd_message.get;
1603: END IF;
1604:
1605: ELSIF l_exception_type = 706 THEN --Facility Mode Incompatibility
1598: med.location_id = flp.location_id
1599: and med.plan_id=p_plan_id
1600: and med.exception_detail_id=p_exception_detail_id;
1601: fnd_message.set_token('FACILITY_DESCRIPTION', l_temp);
1602: l_message := fnd_message.get;
1603: END IF;
1604:
1605: ELSIF l_exception_type = 706 THEN --Facility Mode Incompatibility
1606: IF P_LINE_NUM = 1 THEN
1603: END IF;
1604:
1605: ELSIF l_exception_type = 706 THEN --Facility Mode Incompatibility
1606: IF P_LINE_NUM = 1 THEN
1607: fnd_message.set_name('MST', 'MST_EXCEP_TOKEN_706_1');
1608: --Trip_number, mode_of_transport, facility_description
1609: --Trip_number, mode_of_transport
1610: SELECT mt.trip_number, wlk.meaning
1611: INTO l_id1, l_temp
1613: WHERE plan_id = P_PLAN_ID
1614: AND trip_id = P_TRIP_ID
1615: and wlk.lookup_type = 'WSH_MODE_OF_TRANSPORT'
1616: and mt.mode_of_transport = wlk.lookup_code;
1617: fnd_message.set_token('TRIP_NUMBER', l_id1);
1618: fnd_message.set_token('MODE_OF_TRANSPORT', l_temp);
1619: --facility_description
1620: select flp.description
1621: into l_temp1
1614: AND trip_id = P_TRIP_ID
1615: and wlk.lookup_type = 'WSH_MODE_OF_TRANSPORT'
1616: and mt.mode_of_transport = wlk.lookup_code;
1617: fnd_message.set_token('TRIP_NUMBER', l_id1);
1618: fnd_message.set_token('MODE_OF_TRANSPORT', l_temp);
1619: --facility_description
1620: select flp.description
1621: into l_temp1
1622: from fte_location_parameters flp, mst_exception_details med
1623: where
1624: med.location_id = flp.location_id
1625: and med.plan_id=p_plan_id
1626: and med.exception_detail_id=p_exception_detail_id;
1627: fnd_message.set_token('FACILITY_DESCRIPTION', l_temp1);
1628: l_message := fnd_message.get;
1629: END IF;
1630:
1631: ELSIF l_exception_type = 707 THEN --Facility Facility Incompatibility
1624: med.location_id = flp.location_id
1625: and med.plan_id=p_plan_id
1626: and med.exception_detail_id=p_exception_detail_id;
1627: fnd_message.set_token('FACILITY_DESCRIPTION', l_temp1);
1628: l_message := fnd_message.get;
1629: END IF;
1630:
1631: ELSIF l_exception_type = 707 THEN --Facility Facility Incompatibility
1632: IF P_LINE_NUM = 1 THEN
1629: END IF;
1630:
1631: ELSIF l_exception_type = 707 THEN --Facility Facility Incompatibility
1632: IF P_LINE_NUM = 1 THEN
1633: fnd_message.set_name('MST', 'MST_EXCEP_TOKEN_707_1');
1634: --Delivery_number
1635: select md.delivery_number
1636: into l_id1
1637: from mst_deliveries md, mst_exception_details med
1638: where med.delivery_id = md.delivery_id
1639: and med.plan_id=p_plan_id
1640: and med.plan_id = md.plan_id
1641: and med.exception_detail_id=p_exception_detail_id;
1642: fnd_message.set_token('DELIVERY_NUMBER', l_id1);
1643: --End_facility_name, intermediate_facility_name
1644: select flp1.facility_code, flp2.facility_code
1645: into l_temp1, l_temp2
1646: from fte_location_parameters flp1, fte_location_parameters flp2, mst_exception_details med
1647: where med.location_id = flp1.location_id
1648: and med.number1 = flp2.location_id
1649: and med.plan_id = p_plan_id
1650: and med.exception_detail_id = p_exception_detail_id;
1651: fnd_message.set_token('END_FACILITY_NAME', l_temp1);
1652: fnd_message.set_token('INTERMEDIATE_FACILITY_NAME', l_temp2);
1653: l_message := fnd_message.get;
1654: END IF;-- end giyer
1655:
1648: and med.number1 = flp2.location_id
1649: and med.plan_id = p_plan_id
1650: and med.exception_detail_id = p_exception_detail_id;
1651: fnd_message.set_token('END_FACILITY_NAME', l_temp1);
1652: fnd_message.set_token('INTERMEDIATE_FACILITY_NAME', l_temp2);
1653: l_message := fnd_message.get;
1654: END IF;-- end giyer
1655:
1656: ELSIF l_exception_type = 200 THEN
1649: and med.plan_id = p_plan_id
1650: and med.exception_detail_id = p_exception_detail_id;
1651: fnd_message.set_token('END_FACILITY_NAME', l_temp1);
1652: fnd_message.set_token('INTERMEDIATE_FACILITY_NAME', l_temp2);
1653: l_message := fnd_message.get;
1654: END IF;-- end giyer
1655:
1656: ELSIF l_exception_type = 200 THEN
1657: IF P_LINE_NUM = 1 THEN
1654: END IF;-- end giyer
1655:
1656: ELSIF l_exception_type = 200 THEN
1657: IF P_LINE_NUM = 1 THEN
1658: fnd_message.set_name('MST', 'MST_EXCEP_TOKEN_200_1');
1659: SELECT carrier_id, origin_location_id, destination_location_id, trip_number
1660: INTO l_id1, l_id2, l_id3, l_id4
1661: FROM mst_trips
1662: WHERE plan_id = P_PLAN_ID
1660: INTO l_id1, l_id2, l_id3, l_id4
1661: FROM mst_trips
1662: WHERE plan_id = P_PLAN_ID
1663: AND trip_id = P_TRIP_ID;
1664: fnd_message.set_token('TRIP_NUMBER', l_id4);
1665:
1666: SELECT freight_code INTO l_temp
1667: FROM wsh_carriers
1668: WHERE carrier_id = l_id1;
1665:
1666: SELECT freight_code INTO l_temp
1667: FROM wsh_carriers
1668: WHERE carrier_id = l_id1;
1669: fnd_message.set_token('FREIGHT_CODE', l_temp);
1670:
1671: SELECT city INTO l_temp
1672: FROM wsh_locations
1673: WHERE wsh_location_id = l_id2;
1670:
1671: SELECT city INTO l_temp
1672: FROM wsh_locations
1673: WHERE wsh_location_id = l_id2;
1674: fnd_message.set_token('ORIGIN_CITY', l_temp);
1675:
1676: SELECT city INTO l_temp
1677: FROM wsh_locations
1678: WHERE wsh_location_id = l_id3;
1675:
1676: SELECT city INTO l_temp
1677: FROM wsh_locations
1678: WHERE wsh_location_id = l_id3;
1679: fnd_message.set_token('DESTINATION_CITY', l_temp);
1680:
1681: SELECT distance_uom
1682: INTO l_str1
1683: FROM mst_plans
1681: SELECT distance_uom
1682: INTO l_str1
1683: FROM mst_plans
1684: WHERE plan_id = P_PLAN_ID;
1685: fnd_message.set_token('DISTANCE_UOM', l_str1);
1686:
1687: SELECT round(number3), round(number4)
1688: INTO l_id1, l_id2
1689: FROM mst_exception_details
1688: INTO l_id1, l_id2
1689: FROM mst_exception_details
1690: WHERE plan_id = P_PLAN_ID
1691: AND exception_detail_id = P_EXCEPTION_DETAIL_ID;
1692: fnd_message.set_token('MAX_DISTANCE_IN_24HR', l_id2);
1693: fnd_message.set_token('DISTANCE_IN_24HR', l_id1);
1694:
1695: SELECT round(number1), round(number2)
1696: INTO l_id1, l_id2
1689: FROM mst_exception_details
1690: WHERE plan_id = P_PLAN_ID
1691: AND exception_detail_id = P_EXCEPTION_DETAIL_ID;
1692: fnd_message.set_token('MAX_DISTANCE_IN_24HR', l_id2);
1693: fnd_message.set_token('DISTANCE_IN_24HR', l_id1);
1694:
1695: SELECT round(number1), round(number2)
1696: INTO l_id1, l_id2
1697: FROM mst_exception_details
1696: INTO l_id1, l_id2
1697: FROM mst_exception_details
1698: WHERE plan_id = P_PLAN_ID
1699: AND exception_detail_id = P_EXCEPTION_DETAIL_ID;
1700: fnd_message.set_token('MAXIMUM_DISTANCE', l_id2);
1701: fnd_message.set_token('DISTANCE', l_id1);
1702: l_message := fnd_message.get;
1703:
1704: END IF;
1697: FROM mst_exception_details
1698: WHERE plan_id = P_PLAN_ID
1699: AND exception_detail_id = P_EXCEPTION_DETAIL_ID;
1700: fnd_message.set_token('MAXIMUM_DISTANCE', l_id2);
1701: fnd_message.set_token('DISTANCE', l_id1);
1702: l_message := fnd_message.get;
1703:
1704: END IF;
1705: ELSIF l_exception_type = 207 THEN
1698: WHERE plan_id = P_PLAN_ID
1699: AND exception_detail_id = P_EXCEPTION_DETAIL_ID;
1700: fnd_message.set_token('MAXIMUM_DISTANCE', l_id2);
1701: fnd_message.set_token('DISTANCE', l_id1);
1702: l_message := fnd_message.get;
1703:
1704: END IF;
1705: ELSIF l_exception_type = 207 THEN
1706: IF P_LINE_NUM = 1 THEN
1703:
1704: END IF;
1705: ELSIF l_exception_type = 207 THEN
1706: IF P_LINE_NUM = 1 THEN
1707: fnd_message.set_name('MST', 'MST_EXCEP_TOKEN_207_1');
1708: SELECT carrier_id, origin_location_id, destination_location_id, trip_number
1709: INTO l_id1, l_id2, l_id3, l_id4
1710: FROM mst_trips
1711: WHERE plan_id = P_PLAN_ID
1709: INTO l_id1, l_id2, l_id3, l_id4
1710: FROM mst_trips
1711: WHERE plan_id = P_PLAN_ID
1712: AND trip_id = P_TRIP_ID;
1713: fnd_message.set_token('TRIP_NUMBER', l_id4);
1714:
1715: SELECT freight_code INTO l_temp
1716: FROM wsh_carriers
1717: WHERE carrier_id = l_id1;
1714:
1715: SELECT freight_code INTO l_temp
1716: FROM wsh_carriers
1717: WHERE carrier_id = l_id1;
1718: fnd_message.set_token('FREIGHT_CODE', l_temp);
1719:
1720: SELECT city INTO l_temp
1721: FROM wsh_locations
1722: WHERE wsh_location_id = l_id2;
1719:
1720: SELECT city INTO l_temp
1721: FROM wsh_locations
1722: WHERE wsh_location_id = l_id2;
1723: fnd_message.set_token('ORIGIN_CITY', l_temp);
1724:
1725: SELECT city INTO l_temp
1726: FROM wsh_locations
1727: WHERE wsh_location_id = l_id3;
1724:
1725: SELECT city INTO l_temp
1726: FROM wsh_locations
1727: WHERE wsh_location_id = l_id3;
1728: fnd_message.set_token('DESTINATION_CITY', l_temp);
1729:
1730: SELECT nvl(round(wcs.max_driving_time_in_24hr),0)
1731: INTO l_id2
1732: FROM wsh_carrier_services wcs,
1733: mst_exception_details med
1734: WHERE wcs.carrier_service_id = med.carrier_service_id
1735: AND med.plan_id = P_PLAN_ID
1736: AND med.exception_detail_id = P_EXCEPTION_DETAIL_ID;
1737: fnd_message.set_token('ALLOWED_DRIVING_TIME', l_id2);
1738:
1739: SELECT nvl(round(number1),0)
1740: INTO l_id1
1741: FROM mst_exception_details
1740: INTO l_id1
1741: FROM mst_exception_details
1742: WHERE plan_id = P_PLAN_ID
1743: AND exception_detail_id = P_EXCEPTION_DETAIL_ID;
1744: fnd_message.set_token('DRIVING_TIME', l_id1);
1745:
1746: SELECT nvl(round(wcs.max_total_time),0)
1747: INTO l_id2
1748: FROM wsh_carrier_services wcs,
1749: mst_exception_details med
1750: WHERE wcs.carrier_service_id = med.carrier_service_id
1751: AND med.plan_id = P_PLAN_ID
1752: AND med.exception_detail_id = P_EXCEPTION_DETAIL_ID;
1753: fnd_message.set_token('MAX_TIME', l_id2);
1754:
1755: SELECT nvl(round(wcs.max_duty_time_in_24hr),0)
1756: INTO l_id2
1757: FROM wsh_carrier_services wcs,
1758: mst_exception_details med
1759: WHERE wcs.carrier_service_id = med.carrier_service_id
1760: AND med.plan_id = P_PLAN_ID
1761: AND med.exception_detail_id = P_EXCEPTION_DETAIL_ID;
1762: fnd_message.set_token('ALLOWED_DUTY_TIME', l_id2);
1763:
1764: SELECT nvl(round(number2),0)
1765: INTO l_id1
1766: FROM mst_exception_details
1765: INTO l_id1
1766: FROM mst_exception_details
1767: WHERE plan_id = P_PLAN_ID
1768: AND exception_detail_id = P_EXCEPTION_DETAIL_ID;
1769: fnd_message.set_token('DUTY_TIME', l_id1);
1770:
1771: SELECT nvl(round(wcs.min_layover_time),0)
1772: INTO l_id2
1773: FROM wsh_carrier_services wcs,
1774: mst_exception_details med
1775: WHERE wcs.carrier_service_id = med.carrier_service_id
1776: AND med.plan_id = P_PLAN_ID
1777: AND med.exception_detail_id = P_EXCEPTION_DETAIL_ID;
1778: fnd_message.set_token('MIN_LAYOVER_TIME', l_id2);
1779:
1780: SELECT nvl(round(number3),0)
1781: INTO l_id1
1782: FROM mst_exception_details
1781: INTO l_id1
1782: FROM mst_exception_details
1783: WHERE plan_id = P_PLAN_ID
1784: AND exception_detail_id = P_EXCEPTION_DETAIL_ID;
1785: fnd_message.set_token('LAYOVER_TIME', l_id1);
1786:
1787: SELECT nvl(round(((trip_end_date - trip_start_date) * 24)),0)
1788: INTO l_id1
1789: FROM mst_trips
1788: INTO l_id1
1789: FROM mst_trips
1790: WHERE plan_id = P_PLAN_ID
1791: AND trip_id = P_TRIP_ID;
1792: fnd_message.set_token('TIME', l_id1);
1793:
1794: l_message := fnd_message.get;
1795: END IF;
1796: ELSIF l_exception_type = 206 THEN
1790: WHERE plan_id = P_PLAN_ID
1791: AND trip_id = P_TRIP_ID;
1792: fnd_message.set_token('TIME', l_id1);
1793:
1794: l_message := fnd_message.get;
1795: END IF;
1796: ELSIF l_exception_type = 206 THEN
1797: IF P_LINE_NUM = 1 THEN
1798: fnd_message.set_name('MST', 'MST_EXCEP_TOKEN_206_1');
1794: l_message := fnd_message.get;
1795: END IF;
1796: ELSIF l_exception_type = 206 THEN
1797: IF P_LINE_NUM = 1 THEN
1798: fnd_message.set_name('MST', 'MST_EXCEP_TOKEN_206_1');
1799: SELECT carrier_id, origin_location_id, destination_location_id, trip_number
1800: INTO l_id1, l_id2, l_id3, l_id4
1801: FROM mst_trips
1802: WHERE plan_id = P_PLAN_ID
1800: INTO l_id1, l_id2, l_id3, l_id4
1801: FROM mst_trips
1802: WHERE plan_id = P_PLAN_ID
1803: AND trip_id = P_TRIP_ID;
1804: fnd_message.set_token('TRIP_NUMBER', l_id4);
1805:
1806: SELECT freight_code INTO l_temp
1807: FROM wsh_carriers
1808: WHERE carrier_id = l_id1;
1805:
1806: SELECT freight_code INTO l_temp
1807: FROM wsh_carriers
1808: WHERE carrier_id = l_id1;
1809: fnd_message.set_token('FREIGHT_CODE', l_temp);
1810:
1811: SELECT city INTO l_temp
1812: FROM wsh_locations
1813: WHERE wsh_location_id = l_id2;
1810:
1811: SELECT city INTO l_temp
1812: FROM wsh_locations
1813: WHERE wsh_location_id = l_id2;
1814: fnd_message.set_token('ORIGIN_CITY', l_temp);
1815:
1816: SELECT city INTO l_temp
1817: FROM wsh_locations
1818: WHERE wsh_location_id = l_id3;
1815:
1816: SELECT city INTO l_temp
1817: FROM wsh_locations
1818: WHERE wsh_location_id = l_id3;
1819: fnd_message.set_token('DESTINATION_CITY', l_temp);
1820: l_message := fnd_message.get;
1821: ELSIF P_LINE_NUM = 2 THEN
1822: fnd_message.set_name('MST', 'MST_EXCEP_TOKEN_206_2');
1823: SELECT number1, number2
1816: SELECT city INTO l_temp
1817: FROM wsh_locations
1818: WHERE wsh_location_id = l_id3;
1819: fnd_message.set_token('DESTINATION_CITY', l_temp);
1820: l_message := fnd_message.get;
1821: ELSIF P_LINE_NUM = 2 THEN
1822: fnd_message.set_name('MST', 'MST_EXCEP_TOKEN_206_2');
1823: SELECT number1, number2
1824: INTO l_id1, l_id2
1818: WHERE wsh_location_id = l_id3;
1819: fnd_message.set_token('DESTINATION_CITY', l_temp);
1820: l_message := fnd_message.get;
1821: ELSIF P_LINE_NUM = 2 THEN
1822: fnd_message.set_name('MST', 'MST_EXCEP_TOKEN_206_2');
1823: SELECT number1, number2
1824: INTO l_id1, l_id2
1825: FROM mst_exception_details
1826: WHERE plan_id = P_PLAN_ID
1825: FROM mst_exception_details
1826: WHERE plan_id = P_PLAN_ID
1827: AND trip_id1 = P_TRIP_ID
1828: AND exception_detail_id = P_EXCEPTION_DETAIL_ID;
1829: fnd_message.set_token('STOPS', l_id1);
1830: fnd_message.set_token('MAX_STOPS', l_id2);
1831: l_message := fnd_message.get;
1832: END IF;
1833: ELSIF l_exception_type = 208 THEN
1826: WHERE plan_id = P_PLAN_ID
1827: AND trip_id1 = P_TRIP_ID
1828: AND exception_detail_id = P_EXCEPTION_DETAIL_ID;
1829: fnd_message.set_token('STOPS', l_id1);
1830: fnd_message.set_token('MAX_STOPS', l_id2);
1831: l_message := fnd_message.get;
1832: END IF;
1833: ELSIF l_exception_type = 208 THEN
1834: IF P_LINE_NUM = 1 THEN
1827: AND trip_id1 = P_TRIP_ID
1828: AND exception_detail_id = P_EXCEPTION_DETAIL_ID;
1829: fnd_message.set_token('STOPS', l_id1);
1830: fnd_message.set_token('MAX_STOPS', l_id2);
1831: l_message := fnd_message.get;
1832: END IF;
1833: ELSIF l_exception_type = 208 THEN
1834: IF P_LINE_NUM = 1 THEN
1835: fnd_message.set_name('MST', 'MST_EXCEP_TOKEN_208_1');
1831: l_message := fnd_message.get;
1832: END IF;
1833: ELSIF l_exception_type = 208 THEN
1834: IF P_LINE_NUM = 1 THEN
1835: fnd_message.set_name('MST', 'MST_EXCEP_TOKEN_208_1');
1836: SELECT carrier_id, origin_location_id, destination_location_id, trip_number
1837: INTO l_id1, l_id2, l_id3, l_id4
1838: FROM mst_trips
1839: WHERE plan_id = P_PLAN_ID
1837: INTO l_id1, l_id2, l_id3, l_id4
1838: FROM mst_trips
1839: WHERE plan_id = P_PLAN_ID
1840: AND trip_id = P_TRIP_ID;
1841: fnd_message.set_token('TRIP_NUMBER', l_id4);
1842:
1843: SELECT freight_code INTO l_temp
1844: FROM wsh_carriers
1845: WHERE carrier_id = l_id1;
1842:
1843: SELECT freight_code INTO l_temp
1844: FROM wsh_carriers
1845: WHERE carrier_id = l_id1;
1846: fnd_message.set_token('FREIGHT_CODE', l_temp);
1847:
1848: SELECT city INTO l_temp
1849: FROM wsh_locations
1850: WHERE wsh_location_id = l_id2;
1847:
1848: SELECT city INTO l_temp
1849: FROM wsh_locations
1850: WHERE wsh_location_id = l_id2;
1851: fnd_message.set_token('ORIGIN_CITY', l_temp);
1852:
1853: SELECT city INTO l_temp
1854: FROM wsh_locations
1855: WHERE wsh_location_id = l_id3;
1852:
1853: SELECT city INTO l_temp
1854: FROM wsh_locations
1855: WHERE wsh_location_id = l_id3;
1856: fnd_message.set_token('DESTINATION_CITY', l_temp);
1857: l_message := fnd_message.get;
1858: END IF;
1859: ELSIF l_exception_type = 201 THEN
1860: IF P_LINE_NUM = 1 THEN
1853: SELECT city INTO l_temp
1854: FROM wsh_locations
1855: WHERE wsh_location_id = l_id3;
1856: fnd_message.set_token('DESTINATION_CITY', l_temp);
1857: l_message := fnd_message.get;
1858: END IF;
1859: ELSIF l_exception_type = 201 THEN
1860: IF P_LINE_NUM = 1 THEN
1861: fnd_message.set_name('MST', 'MST_EXCEP_TOKEN_201_1');
1857: l_message := fnd_message.get;
1858: END IF;
1859: ELSIF l_exception_type = 201 THEN
1860: IF P_LINE_NUM = 1 THEN
1861: fnd_message.set_name('MST', 'MST_EXCEP_TOKEN_201_1');
1862: SELECT distance_uom INTO l_temp
1863: FROM mst_plans
1864: WHERE plan_id = P_PLAN_ID;
1865: fnd_message.set_token('DISTANCE_UOM', l_temp);
1861: fnd_message.set_name('MST', 'MST_EXCEP_TOKEN_201_1');
1862: SELECT distance_uom INTO l_temp
1863: FROM mst_plans
1864: WHERE plan_id = P_PLAN_ID;
1865: fnd_message.set_token('DISTANCE_UOM', l_temp);
1866:
1867: SELECT carrier_id, continuous_move_id, round(number2), round(number3)
1868: INTO l_id1, l_id2, l_id3, l_id4
1869: FROM mst_exception_details
1869: FROM mst_exception_details
1870: WHERE plan_id = P_PLAN_ID
1871: AND exception_detail_id = P_EXCEPTION_DETAIL_ID;
1872:
1873: fnd_message.set_token('ACTUAL_DEADHEAD_DISTANCE', l_id3);
1874: fnd_message.set_token('MAX_DEADHEAD_DISTANCE', l_id4);
1875:
1876: SELECT freight_code INTO l_temp
1877: FROM wsh_carriers
1870: WHERE plan_id = P_PLAN_ID
1871: AND exception_detail_id = P_EXCEPTION_DETAIL_ID;
1872:
1873: fnd_message.set_token('ACTUAL_DEADHEAD_DISTANCE', l_id3);
1874: fnd_message.set_token('MAX_DEADHEAD_DISTANCE', l_id4);
1875:
1876: SELECT freight_code INTO l_temp
1877: FROM wsh_carriers
1878: WHERE carrier_id = l_id1;
1875:
1876: SELECT freight_code INTO l_temp
1877: FROM wsh_carriers
1878: WHERE carrier_id = l_id1;
1879: fnd_message.set_token('FREIGHT_CODE', l_temp);
1880:
1881: SELECT cm_trip_number, start_location_id, end_location_id
1882: INTO l_id1, l_id3, l_id4
1883: FROM mst_cm_trips
1882: INTO l_id1, l_id3, l_id4
1883: FROM mst_cm_trips
1884: WHERE plan_id = P_PLAN_ID
1885: AND continuous_move_id = l_id2;
1886: fnd_message.set_token('CM_TRIP_NUMBER', l_id1);
1887:
1888: SELECT city INTO l_temp
1889: FROM wsh_locations
1890: WHERE wsh_location_id = l_id3;
1887:
1888: SELECT city INTO l_temp
1889: FROM wsh_locations
1890: WHERE wsh_location_id = l_id3;
1891: fnd_message.set_token('ORIGIN_CITY', l_temp);
1892:
1893: SELECT city INTO l_temp
1894: FROM wsh_locations
1895: WHERE wsh_location_id = l_id4;
1892:
1893: SELECT city INTO l_temp
1894: FROM wsh_locations
1895: WHERE wsh_location_id = l_id4;
1896: fnd_message.set_token('DESTINATION_CITY', l_temp);
1897: l_message := fnd_message.get;
1898: END IF;
1899: ELSIF l_exception_type = 202 THEN
1900: IF P_LINE_NUM = 1 THEN
1893: SELECT city INTO l_temp
1894: FROM wsh_locations
1895: WHERE wsh_location_id = l_id4;
1896: fnd_message.set_token('DESTINATION_CITY', l_temp);
1897: l_message := fnd_message.get;
1898: END IF;
1899: ELSIF l_exception_type = 202 THEN
1900: IF P_LINE_NUM = 1 THEN
1901: fnd_message.set_name('MST', 'MST_EXCEP_TOKEN_202_1');
1897: l_message := fnd_message.get;
1898: END IF;
1899: ELSIF l_exception_type = 202 THEN
1900: IF P_LINE_NUM = 1 THEN
1901: fnd_message.set_name('MST', 'MST_EXCEP_TOKEN_202_1');
1902: SELECT origin_location_id, destination_location_id, trip_number
1903: INTO l_id2, l_id3, l_id4
1904: FROM mst_trips
1905: WHERE plan_id = P_PLAN_ID
1903: INTO l_id2, l_id3, l_id4
1904: FROM mst_trips
1905: WHERE plan_id = P_PLAN_ID
1906: AND trip_id = P_TRIP_ID;
1907: fnd_message.set_token('TRIP_NUMBER', l_id4);
1908:
1909: SELECT city INTO l_temp
1910: FROM wsh_locations
1911: WHERE wsh_location_id = l_id2;
1908:
1909: SELECT city INTO l_temp
1910: FROM wsh_locations
1911: WHERE wsh_location_id = l_id2;
1912: fnd_message.set_token('ORIGIN_CITY', l_temp);
1913:
1914: SELECT city INTO l_temp
1915: FROM wsh_locations
1916: WHERE wsh_location_id = l_id3;
1913:
1914: SELECT city INTO l_temp
1915: FROM wsh_locations
1916: WHERE wsh_location_id = l_id3;
1917: fnd_message.set_token('DESTINATION_CITY', l_temp);
1918: l_message := fnd_message.get;
1919: END IF;
1920: ELSIF l_exception_type = 203 THEN
1921: IF P_LINE_NUM = 1 THEN
1914: SELECT city INTO l_temp
1915: FROM wsh_locations
1916: WHERE wsh_location_id = l_id3;
1917: fnd_message.set_token('DESTINATION_CITY', l_temp);
1918: l_message := fnd_message.get;
1919: END IF;
1920: ELSIF l_exception_type = 203 THEN
1921: IF P_LINE_NUM = 1 THEN
1922: fnd_message.set_name('MST', 'MST_EXCEP_TOKEN_203_1');
1918: l_message := fnd_message.get;
1919: END IF;
1920: ELSIF l_exception_type = 203 THEN
1921: IF P_LINE_NUM = 1 THEN
1922: fnd_message.set_name('MST', 'MST_EXCEP_TOKEN_203_1');
1923: SELECT trip_number
1924: INTO l_id1
1925: FROM mst_trips
1926: WHERE plan_id = P_PLAN_ID
1924: INTO l_id1
1925: FROM mst_trips
1926: WHERE plan_id = P_PLAN_ID
1927: AND trip_id = P_TRIP_ID;
1928: fnd_message.set_token('TRIP_NUMBER', l_id1);
1929:
1930: SELECT mts.stop_sequence_number, mts.stop_location_id
1931: INTO l_id2, l_id3
1932: FROM mst_trip_stops mts,
1935: AND med.plan_id = P_PLAN_ID
1936: AND mts.trip_id = med.trip_id1
1937: AND mts.stop_id = med.stop_id1
1938: AND med.exception_detail_id = P_EXCEPTION_DETAIL_ID;
1939: fnd_message.set_token('STOP_SEQUENCE_NUMBER', l_id2);
1940:
1941: SELECT city INTO l_temp
1942: FROM wsh_locations
1943: WHERE wsh_location_id = l_id3;
1940:
1941: SELECT city INTO l_temp
1942: FROM wsh_locations
1943: WHERE wsh_location_id = l_id3;
1944: fnd_message.set_token('CITY', l_temp);
1945:
1946: l_message := fnd_message.get;
1947: END IF;
1948: ELSIF l_exception_type = 204 THEN
1942: FROM wsh_locations
1943: WHERE wsh_location_id = l_id3;
1944: fnd_message.set_token('CITY', l_temp);
1945:
1946: l_message := fnd_message.get;
1947: END IF;
1948: ELSIF l_exception_type = 204 THEN
1949: IF P_LINE_NUM = 1 THEN
1950: fnd_message.set_name('MST', 'MST_EXCEP_TOKEN_204_1');
1946: l_message := fnd_message.get;
1947: END IF;
1948: ELSIF l_exception_type = 204 THEN
1949: IF P_LINE_NUM = 1 THEN
1950: fnd_message.set_name('MST', 'MST_EXCEP_TOKEN_204_1');
1951: SELECT trip_number
1952: INTO l_id1
1953: FROM mst_trips
1954: WHERE plan_id = P_PLAN_ID
1952: INTO l_id1
1953: FROM mst_trips
1954: WHERE plan_id = P_PLAN_ID
1955: AND trip_id = P_TRIP_ID;
1956: fnd_message.set_token('TRIP_NUMBER', l_id1);
1957:
1958: SELECT mts.stop_location_id, mts.planned_departure_date, round(med.number1)
1959: INTO l_id1, l_date1, l_id3
1960: FROM mst_trip_stops mts,
1966:
1967: SELECT city INTO l_temp
1968: FROM wsh_locations
1969: WHERE wsh_location_id = l_id1;
1970: fnd_message.set_token('ORIGIN_CITY', l_temp);
1971: fnd_message.set_token('REQUIRED_TRANSIT_TIME', l_id3);
1972:
1973: SELECT mts.stop_location_id, mts.planned_arrival_date
1974: INTO l_id3, l_date2
1967: SELECT city INTO l_temp
1968: FROM wsh_locations
1969: WHERE wsh_location_id = l_id1;
1970: fnd_message.set_token('ORIGIN_CITY', l_temp);
1971: fnd_message.set_token('REQUIRED_TRANSIT_TIME', l_id3);
1972:
1973: SELECT mts.stop_location_id, mts.planned_arrival_date
1974: INTO l_id3, l_date2
1975: FROM mst_trip_stops mts,
1981:
1982: SELECT city INTO l_temp
1983: FROM wsh_locations
1984: WHERE wsh_location_id = l_id3;
1985: fnd_message.set_token('DESTINATION_CITY', l_temp);
1986:
1987: l_id1 := round(((l_date2 - l_date1) * 24));
1988: fnd_message.set_token('PLANNED_TRANSIT_TIME', l_id1);
1989: l_message := fnd_message.get;
1984: WHERE wsh_location_id = l_id3;
1985: fnd_message.set_token('DESTINATION_CITY', l_temp);
1986:
1987: l_id1 := round(((l_date2 - l_date1) * 24));
1988: fnd_message.set_token('PLANNED_TRANSIT_TIME', l_id1);
1989: l_message := fnd_message.get;
1990: END IF;
1991: ELSIF l_exception_type = 205 THEN
1992: IF P_LINE_NUM = 1 THEN
1985: fnd_message.set_token('DESTINATION_CITY', l_temp);
1986:
1987: l_id1 := round(((l_date2 - l_date1) * 24));
1988: fnd_message.set_token('PLANNED_TRANSIT_TIME', l_id1);
1989: l_message := fnd_message.get;
1990: END IF;
1991: ELSIF l_exception_type = 205 THEN
1992: IF P_LINE_NUM = 1 THEN
1993: fnd_message.set_name('MST', 'MST_EXCEP_TOKEN_205_1');
1989: l_message := fnd_message.get;
1990: END IF;
1991: ELSIF l_exception_type = 205 THEN
1992: IF P_LINE_NUM = 1 THEN
1993: fnd_message.set_name('MST', 'MST_EXCEP_TOKEN_205_1');
1994: SELECT trip_number
1995: INTO l_id1
1996: FROM mst_trips
1997: WHERE plan_id = P_PLAN_ID
1995: INTO l_id1
1996: FROM mst_trips
1997: WHERE plan_id = P_PLAN_ID
1998: AND trip_id = P_TRIP_ID;
1999: fnd_message.set_token('TRIP_NUMBER', l_id1);
2000:
2001: SELECT round(nvl(number1, 0)), round(nvl(number2, 0)), round(nvl(number3, 0)), round(nvl(number4, 0))
2002: INTO l_id1, l_id2, l_id3, l_id7
2003: FROM mst_exception_details med
2008: INTO l_id4, l_id5, l_id6, l_id8
2009: FROM mst_parameters
2010: WHERE user_id = -9999;
2011:
2012: fnd_message.set_token('ALLOWED_DRIVING_TIME', l_id4);
2013: fnd_message.set_token('DRIVING_TIME', l_id2);
2014: fnd_message.set_token('ALLOWED_DUTY_TIME', l_id5);
2015: fnd_message.set_token('DUTY_TIME', l_id3);
2016: fnd_message.set_token('MINIMUM_LAYOVER_TIME', l_id6);
2009: FROM mst_parameters
2010: WHERE user_id = -9999;
2011:
2012: fnd_message.set_token('ALLOWED_DRIVING_TIME', l_id4);
2013: fnd_message.set_token('DRIVING_TIME', l_id2);
2014: fnd_message.set_token('ALLOWED_DUTY_TIME', l_id5);
2015: fnd_message.set_token('DUTY_TIME', l_id3);
2016: fnd_message.set_token('MINIMUM_LAYOVER_TIME', l_id6);
2017: fnd_message.set_token('LAYOVER_TIME', l_id1);
2010: WHERE user_id = -9999;
2011:
2012: fnd_message.set_token('ALLOWED_DRIVING_TIME', l_id4);
2013: fnd_message.set_token('DRIVING_TIME', l_id2);
2014: fnd_message.set_token('ALLOWED_DUTY_TIME', l_id5);
2015: fnd_message.set_token('DUTY_TIME', l_id3);
2016: fnd_message.set_token('MINIMUM_LAYOVER_TIME', l_id6);
2017: fnd_message.set_token('LAYOVER_TIME', l_id1);
2018: fnd_message.set_token('MAX_DISTANCE', l_id8);
2011:
2012: fnd_message.set_token('ALLOWED_DRIVING_TIME', l_id4);
2013: fnd_message.set_token('DRIVING_TIME', l_id2);
2014: fnd_message.set_token('ALLOWED_DUTY_TIME', l_id5);
2015: fnd_message.set_token('DUTY_TIME', l_id3);
2016: fnd_message.set_token('MINIMUM_LAYOVER_TIME', l_id6);
2017: fnd_message.set_token('LAYOVER_TIME', l_id1);
2018: fnd_message.set_token('MAX_DISTANCE', l_id8);
2019: fnd_message.set_token('DISTANCE', l_id7);
2012: fnd_message.set_token('ALLOWED_DRIVING_TIME', l_id4);
2013: fnd_message.set_token('DRIVING_TIME', l_id2);
2014: fnd_message.set_token('ALLOWED_DUTY_TIME', l_id5);
2015: fnd_message.set_token('DUTY_TIME', l_id3);
2016: fnd_message.set_token('MINIMUM_LAYOVER_TIME', l_id6);
2017: fnd_message.set_token('LAYOVER_TIME', l_id1);
2018: fnd_message.set_token('MAX_DISTANCE', l_id8);
2019: fnd_message.set_token('DISTANCE', l_id7);
2020:
2013: fnd_message.set_token('DRIVING_TIME', l_id2);
2014: fnd_message.set_token('ALLOWED_DUTY_TIME', l_id5);
2015: fnd_message.set_token('DUTY_TIME', l_id3);
2016: fnd_message.set_token('MINIMUM_LAYOVER_TIME', l_id6);
2017: fnd_message.set_token('LAYOVER_TIME', l_id1);
2018: fnd_message.set_token('MAX_DISTANCE', l_id8);
2019: fnd_message.set_token('DISTANCE', l_id7);
2020:
2021: l_message := fnd_message.get;
2014: fnd_message.set_token('ALLOWED_DUTY_TIME', l_id5);
2015: fnd_message.set_token('DUTY_TIME', l_id3);
2016: fnd_message.set_token('MINIMUM_LAYOVER_TIME', l_id6);
2017: fnd_message.set_token('LAYOVER_TIME', l_id1);
2018: fnd_message.set_token('MAX_DISTANCE', l_id8);
2019: fnd_message.set_token('DISTANCE', l_id7);
2020:
2021: l_message := fnd_message.get;
2022: END IF;
2015: fnd_message.set_token('DUTY_TIME', l_id3);
2016: fnd_message.set_token('MINIMUM_LAYOVER_TIME', l_id6);
2017: fnd_message.set_token('LAYOVER_TIME', l_id1);
2018: fnd_message.set_token('MAX_DISTANCE', l_id8);
2019: fnd_message.set_token('DISTANCE', l_id7);
2020:
2021: l_message := fnd_message.get;
2022: END IF;
2023: ELSIF l_exception_type = 400 THEN
2017: fnd_message.set_token('LAYOVER_TIME', l_id1);
2018: fnd_message.set_token('MAX_DISTANCE', l_id8);
2019: fnd_message.set_token('DISTANCE', l_id7);
2020:
2021: l_message := fnd_message.get;
2022: END IF;
2023: ELSIF l_exception_type = 400 THEN
2024: IF P_LINE_NUM = 1 THEN
2025: fnd_message.set_name('MST', 'MST_EXCEP_TOKEN_400_1');
2021: l_message := fnd_message.get;
2022: END IF;
2023: ELSIF l_exception_type = 400 THEN
2024: IF P_LINE_NUM = 1 THEN
2025: fnd_message.set_name('MST', 'MST_EXCEP_TOKEN_400_1');
2026: SELECT trip_number
2027: INTO l_id1
2028: FROM mst_trips
2029: WHERE plan_id = P_PLAN_ID
2027: INTO l_id1
2028: FROM mst_trips
2029: WHERE plan_id = P_PLAN_ID
2030: AND trip_id = P_TRIP_ID;
2031: fnd_message.set_token('TRIP_NUMBER', l_id1);
2032:
2033: SELECT description into l_temp
2034: FROM fte_location_parameters
2035: WHERE location_id in (SELECT mts.stop_location_id
2038: WHERE mts.plan_id = med.plan_id
2039: AND mts.stop_id = med.stop_id1
2040: AND med.plan_id = P_PLAN_ID
2041: AND med.exception_detail_id = P_EXCEPTION_DETAIL_ID);
2042: fnd_message.set_token('FACILITY_DESC', l_temp);
2043:
2044: l_message := fnd_message.get;
2045: END IF;
2046: ELSIF l_exception_type = 401 THEN
2040: AND med.plan_id = P_PLAN_ID
2041: AND med.exception_detail_id = P_EXCEPTION_DETAIL_ID);
2042: fnd_message.set_token('FACILITY_DESC', l_temp);
2043:
2044: l_message := fnd_message.get;
2045: END IF;
2046: ELSIF l_exception_type = 401 THEN
2047: IF P_LINE_NUM = 1 THEN
2048: fnd_message.set_name('MST', 'MST_EXCEP_TOKEN_401_1');
2044: l_message := fnd_message.get;
2045: END IF;
2046: ELSIF l_exception_type = 401 THEN
2047: IF P_LINE_NUM = 1 THEN
2048: fnd_message.set_name('MST', 'MST_EXCEP_TOKEN_401_1');
2049: SELECT trip_number
2050: INTO l_id1
2051: FROM mst_trips
2052: WHERE plan_id = P_PLAN_ID
2050: INTO l_id1
2051: FROM mst_trips
2052: WHERE plan_id = P_PLAN_ID
2053: AND trip_id = P_TRIP_ID;
2054: fnd_message.set_token('TRIP_NUMBER', l_id1);
2055:
2056: SELECT flp.description, round(nvl(med.number1, 0), 1), round((mts.planned_departure_date - mts.planned_arrival_date) * 24, 1)
2057: INTO l_temp, l_id1, l_id2
2058: FROM fte_location_parameters flp,
2062: AND mts.plan_id = med.plan_id
2063: AND mts.stop_id = med.stop_id1
2064: AND med.plan_id = P_PLAN_ID
2065: AND med.exception_detail_id = P_EXCEPTION_DETAIL_ID;
2066: fnd_message.set_token('FACILITY_DESC', l_temp);
2067: fnd_message.set_token('REQUIRED_STOP_TIME', ltrim(to_char(l_id1, '999990.0')));
2068: fnd_message.set_token('STOP_TIME', ltrim(to_char(l_id2, '999990.0')));
2069:
2070: l_message := fnd_message.get;
2063: AND mts.stop_id = med.stop_id1
2064: AND med.plan_id = P_PLAN_ID
2065: AND med.exception_detail_id = P_EXCEPTION_DETAIL_ID;
2066: fnd_message.set_token('FACILITY_DESC', l_temp);
2067: fnd_message.set_token('REQUIRED_STOP_TIME', ltrim(to_char(l_id1, '999990.0')));
2068: fnd_message.set_token('STOP_TIME', ltrim(to_char(l_id2, '999990.0')));
2069:
2070: l_message := fnd_message.get;
2071: END IF;
2064: AND med.plan_id = P_PLAN_ID
2065: AND med.exception_detail_id = P_EXCEPTION_DETAIL_ID;
2066: fnd_message.set_token('FACILITY_DESC', l_temp);
2067: fnd_message.set_token('REQUIRED_STOP_TIME', ltrim(to_char(l_id1, '999990.0')));
2068: fnd_message.set_token('STOP_TIME', ltrim(to_char(l_id2, '999990.0')));
2069:
2070: l_message := fnd_message.get;
2071: END IF;
2072: ELSIF l_exception_type = 500 THEN
2066: fnd_message.set_token('FACILITY_DESC', l_temp);
2067: fnd_message.set_token('REQUIRED_STOP_TIME', ltrim(to_char(l_id1, '999990.0')));
2068: fnd_message.set_token('STOP_TIME', ltrim(to_char(l_id2, '999990.0')));
2069:
2070: l_message := fnd_message.get;
2071: END IF;
2072: ELSIF l_exception_type = 500 THEN
2073: IF P_LINE_NUM = 1 THEN
2074: fnd_message.set_name('MST', 'MST_EXCEP_TOKEN_500_1');
2070: l_message := fnd_message.get;
2071: END IF;
2072: ELSIF l_exception_type = 500 THEN
2073: IF P_LINE_NUM = 1 THEN
2074: fnd_message.set_name('MST', 'MST_EXCEP_TOKEN_500_1');
2075: SELECT trip_number, origin_location_id, destination_location_id, carrier_id,
2076: round((nvl(total_basic_transport_cost,0) + nvl(total_accessorial_cost,0) + nvl(total_layover_cost,0) + nvl(total_load_unload_cost,0) + nvl(total_stop_cost,0)))
2077: INTO l_id1, l_id2, l_id3, l_id4, l_id5
2078: FROM mst_trips
2077: INTO l_id1, l_id2, l_id3, l_id4, l_id5
2078: FROM mst_trips
2079: WHERE plan_id = P_PLAN_ID
2080: AND trip_id = P_TRIP_ID;
2081: fnd_message.set_token('TRIP_NUMBER', l_id1);
2082:
2083: SELECT freight_code
2084: INTO l_temp
2085: FROM wsh_carriers
2083: SELECT freight_code
2084: INTO l_temp
2085: FROM wsh_carriers
2086: WHERE carrier_id = l_id4;
2087: fnd_message.set_token('FREIGHT_CODE', l_temp);
2088:
2089: SELECT city
2090: INTO l_temp
2091: FROM wsh_locations
2089: SELECT city
2090: INTO l_temp
2091: FROM wsh_locations
2092: WHERE wsh_location_id = l_id2;
2093: fnd_message.set_token('ORIGIN_CITY', l_temp);
2094:
2095: SELECT city
2096: INTO l_temp
2097: FROM wsh_locations
2095: SELECT city
2096: INTO l_temp
2097: FROM wsh_locations
2098: WHERE wsh_location_id = l_id3;
2099: fnd_message.set_token('DESTINATION_CITY', l_temp);
2100:
2101: SELECT wc.freight_code, round(nvl(med.number1, 0))
2102: INTO l_temp, l_id1
2103: FROM wsh_carriers wc,
2105: WHERE med.plan_id = P_PLAN_ID
2106: AND med.exception_detail_id = P_EXCEPTION_DETAIL_ID
2107: AND med.carrier_id = wc.carrier_id;
2108:
2109: fnd_message.set_token('LOW_COST_CARRIER_COST', l_id1);
2110: fnd_message.set_token('LOW_COST_CARRIER', l_temp);
2111: fnd_message.set_token('COST', l_id5);
2112:
2113: l_message := fnd_message.get;
2106: AND med.exception_detail_id = P_EXCEPTION_DETAIL_ID
2107: AND med.carrier_id = wc.carrier_id;
2108:
2109: fnd_message.set_token('LOW_COST_CARRIER_COST', l_id1);
2110: fnd_message.set_token('LOW_COST_CARRIER', l_temp);
2111: fnd_message.set_token('COST', l_id5);
2112:
2113: l_message := fnd_message.get;
2114: END IF;
2107: AND med.carrier_id = wc.carrier_id;
2108:
2109: fnd_message.set_token('LOW_COST_CARRIER_COST', l_id1);
2110: fnd_message.set_token('LOW_COST_CARRIER', l_temp);
2111: fnd_message.set_token('COST', l_id5);
2112:
2113: l_message := fnd_message.get;
2114: END IF;
2115: ELSIF l_exception_type = 501 THEN
2109: fnd_message.set_token('LOW_COST_CARRIER_COST', l_id1);
2110: fnd_message.set_token('LOW_COST_CARRIER', l_temp);
2111: fnd_message.set_token('COST', l_id5);
2112:
2113: l_message := fnd_message.get;
2114: END IF;
2115: ELSIF l_exception_type = 501 THEN
2116: IF P_LINE_NUM = 1 THEN
2117: fnd_message.set_name('MST', 'MST_EXCEP_TOKEN_501_1');
2113: l_message := fnd_message.get;
2114: END IF;
2115: ELSIF l_exception_type = 501 THEN
2116: IF P_LINE_NUM = 1 THEN
2117: fnd_message.set_name('MST', 'MST_EXCEP_TOKEN_501_1');
2118: SELECT trip_number
2119: INTO l_id1
2120: FROM mst_trips
2121: WHERE plan_id = P_PLAN_ID
2119: INTO l_id1
2120: FROM mst_trips
2121: WHERE plan_id = P_PLAN_ID
2122: AND trip_id = P_TRIP_ID;
2123: fnd_message.set_token('TRIP_NUMBER', l_id1);
2124:
2125: l_message := fnd_message.get;
2126: END IF;
2127: ELSIF l_exception_type = 600 THEN
2121: WHERE plan_id = P_PLAN_ID
2122: AND trip_id = P_TRIP_ID;
2123: fnd_message.set_token('TRIP_NUMBER', l_id1);
2124:
2125: l_message := fnd_message.get;
2126: END IF;
2127: ELSIF l_exception_type = 600 THEN
2128: IF P_LINE_NUM = 1 THEN
2129: fnd_message.set_name('MST', 'MST_EXCEP_TOKEN_600_1');
2125: l_message := fnd_message.get;
2126: END IF;
2127: ELSIF l_exception_type = 600 THEN
2128: IF P_LINE_NUM = 1 THEN
2129: fnd_message.set_name('MST', 'MST_EXCEP_TOKEN_600_1');
2130: SELECT mt.trip_number, mt.peak_weight_utilization,
2131: mt.peak_volume_utilization,
2132: mt.peak_pallet_utilization,
2133: med.number2,
2140: WHERE mt.plan_id = med.plan_id
2141: AND med.plan_id = P_PLAN_ID
2142: AND mt.trip_id = P_TRIP_ID
2143: AND med.exception_detail_id = P_EXCEPTION_DETAIL_ID;
2144: fnd_message.set_token('TRIP_NUMBER', l_id1);
2145:
2146: fnd_message.set_token('PEAK_WEIGHT_UTILIZATION', round(l_id2 * 100));
2147: fnd_message.set_token('PEAK_VOLUME_UTILIZATION', round(l_id3 * 100));
2148: fnd_message.set_token('PEAK_PALLET_UTILIZATION', round(l_id4 * 100));
2142: AND mt.trip_id = P_TRIP_ID
2143: AND med.exception_detail_id = P_EXCEPTION_DETAIL_ID;
2144: fnd_message.set_token('TRIP_NUMBER', l_id1);
2145:
2146: fnd_message.set_token('PEAK_WEIGHT_UTILIZATION', round(l_id2 * 100));
2147: fnd_message.set_token('PEAK_VOLUME_UTILIZATION', round(l_id3 * 100));
2148: fnd_message.set_token('PEAK_PALLET_UTILIZATION', round(l_id4 * 100));
2149:
2150: fnd_message.set_token('PEAK_WEIGHT', round(l_id2 * l_id5));
2143: AND med.exception_detail_id = P_EXCEPTION_DETAIL_ID;
2144: fnd_message.set_token('TRIP_NUMBER', l_id1);
2145:
2146: fnd_message.set_token('PEAK_WEIGHT_UTILIZATION', round(l_id2 * 100));
2147: fnd_message.set_token('PEAK_VOLUME_UTILIZATION', round(l_id3 * 100));
2148: fnd_message.set_token('PEAK_PALLET_UTILIZATION', round(l_id4 * 100));
2149:
2150: fnd_message.set_token('PEAK_WEIGHT', round(l_id2 * l_id5));
2151: fnd_message.set_token('PEAK_VOLUME', round(l_id3 * l_id6));
2144: fnd_message.set_token('TRIP_NUMBER', l_id1);
2145:
2146: fnd_message.set_token('PEAK_WEIGHT_UTILIZATION', round(l_id2 * 100));
2147: fnd_message.set_token('PEAK_VOLUME_UTILIZATION', round(l_id3 * 100));
2148: fnd_message.set_token('PEAK_PALLET_UTILIZATION', round(l_id4 * 100));
2149:
2150: fnd_message.set_token('PEAK_WEIGHT', round(l_id2 * l_id5));
2151: fnd_message.set_token('PEAK_VOLUME', round(l_id3 * l_id6));
2152: fnd_message.set_token('PEAK_PALLETS', round(l_id4 * l_id7));
2146: fnd_message.set_token('PEAK_WEIGHT_UTILIZATION', round(l_id2 * 100));
2147: fnd_message.set_token('PEAK_VOLUME_UTILIZATION', round(l_id3 * 100));
2148: fnd_message.set_token('PEAK_PALLET_UTILIZATION', round(l_id4 * 100));
2149:
2150: fnd_message.set_token('PEAK_WEIGHT', round(l_id2 * l_id5));
2151: fnd_message.set_token('PEAK_VOLUME', round(l_id3 * l_id6));
2152: fnd_message.set_token('PEAK_PALLETS', round(l_id4 * l_id7));
2153:
2154: fnd_message.set_token('MAX_WEIGHT', round(l_id5));
2147: fnd_message.set_token('PEAK_VOLUME_UTILIZATION', round(l_id3 * 100));
2148: fnd_message.set_token('PEAK_PALLET_UTILIZATION', round(l_id4 * 100));
2149:
2150: fnd_message.set_token('PEAK_WEIGHT', round(l_id2 * l_id5));
2151: fnd_message.set_token('PEAK_VOLUME', round(l_id3 * l_id6));
2152: fnd_message.set_token('PEAK_PALLETS', round(l_id4 * l_id7));
2153:
2154: fnd_message.set_token('MAX_WEIGHT', round(l_id5));
2155: fnd_message.set_token('MAX_VOLUME', round(l_id6));
2148: fnd_message.set_token('PEAK_PALLET_UTILIZATION', round(l_id4 * 100));
2149:
2150: fnd_message.set_token('PEAK_WEIGHT', round(l_id2 * l_id5));
2151: fnd_message.set_token('PEAK_VOLUME', round(l_id3 * l_id6));
2152: fnd_message.set_token('PEAK_PALLETS', round(l_id4 * l_id7));
2153:
2154: fnd_message.set_token('MAX_WEIGHT', round(l_id5));
2155: fnd_message.set_token('MAX_VOLUME', round(l_id6));
2156: fnd_message.set_token('MAX_PALLETS', round(l_id7));
2150: fnd_message.set_token('PEAK_WEIGHT', round(l_id2 * l_id5));
2151: fnd_message.set_token('PEAK_VOLUME', round(l_id3 * l_id6));
2152: fnd_message.set_token('PEAK_PALLETS', round(l_id4 * l_id7));
2153:
2154: fnd_message.set_token('MAX_WEIGHT', round(l_id5));
2155: fnd_message.set_token('MAX_VOLUME', round(l_id6));
2156: fnd_message.set_token('MAX_PALLETS', round(l_id7));
2157:
2158: SELECT volume_uom
2151: fnd_message.set_token('PEAK_VOLUME', round(l_id3 * l_id6));
2152: fnd_message.set_token('PEAK_PALLETS', round(l_id4 * l_id7));
2153:
2154: fnd_message.set_token('MAX_WEIGHT', round(l_id5));
2155: fnd_message.set_token('MAX_VOLUME', round(l_id6));
2156: fnd_message.set_token('MAX_PALLETS', round(l_id7));
2157:
2158: SELECT volume_uom
2159: INTO l_temp
2152: fnd_message.set_token('PEAK_PALLETS', round(l_id4 * l_id7));
2153:
2154: fnd_message.set_token('MAX_WEIGHT', round(l_id5));
2155: fnd_message.set_token('MAX_VOLUME', round(l_id6));
2156: fnd_message.set_token('MAX_PALLETS', round(l_id7));
2157:
2158: SELECT volume_uom
2159: INTO l_temp
2160: FROM mst_plans
2171: AND msikfv.organization_id = fvt.organization_id
2172: AND fvt.vehicle_type_id = l_id8;
2173:
2174: If l_id1 is not null Then
2175: fnd_message.set_token('PHYSICAL_CAPACITY', l_id1);
2176: Else
2177: fnd_message.set_token('PHYSICAL_CAPACITY', '');
2178: End If;
2179:
2173:
2174: If l_id1 is not null Then
2175: fnd_message.set_token('PHYSICAL_CAPACITY', l_id1);
2176: Else
2177: fnd_message.set_token('PHYSICAL_CAPACITY', '');
2178: End If;
2179:
2180: SELECT weight_uom, volume_uom
2181: INTO l_temp, l_temp1
2181: INTO l_temp, l_temp1
2182: FROM mst_plans
2183: WHERE plan_id = P_PLAN_ID;
2184:
2185: fnd_message.set_token('WEIGHT_UOM', l_temp);
2186: fnd_message.set_token('VOLUME_UOM', l_temp1);
2187:
2188: l_message := fnd_message.get;
2189: END IF;
2182: FROM mst_plans
2183: WHERE plan_id = P_PLAN_ID;
2184:
2185: fnd_message.set_token('WEIGHT_UOM', l_temp);
2186: fnd_message.set_token('VOLUME_UOM', l_temp1);
2187:
2188: l_message := fnd_message.get;
2189: END IF;
2190: ELSIF l_exception_type = 601 THEN
2184:
2185: fnd_message.set_token('WEIGHT_UOM', l_temp);
2186: fnd_message.set_token('VOLUME_UOM', l_temp1);
2187:
2188: l_message := fnd_message.get;
2189: END IF;
2190: ELSIF l_exception_type = 601 THEN
2191: IF P_LINE_NUM = 1 THEN
2192: fnd_message.set_name('MST', 'MST_EXCEP_TOKEN_601_1');
2188: l_message := fnd_message.get;
2189: END IF;
2190: ELSIF l_exception_type = 601 THEN
2191: IF P_LINE_NUM = 1 THEN
2192: fnd_message.set_name('MST', 'MST_EXCEP_TOKEN_601_1');
2193: SELECT mt.trip_number, mt.peak_weight_utilization,
2194: mt.peak_volume_utilization,
2195: mt.peak_pallet_utilization,
2196: med.number2,
2202: WHERE mt.plan_id = med.plan_id
2203: AND med.plan_id = P_PLAN_ID
2204: AND mt.trip_id = P_TRIP_ID
2205: AND med.exception_detail_id = P_EXCEPTION_DETAIL_ID;
2206: fnd_message.set_token('TRIP_NUMBER', l_id1);
2207:
2208: fnd_message.set_token('PEAK_WEIGHT_UTILIZATION', round(l_id2 * 100));
2209: fnd_message.set_token('PEAK_VOLUME_UTILIZATION', round(l_id3 * 100));
2210: fnd_message.set_token('PEAK_PALLET_UTILIZATION', round(l_id4 * 100));
2204: AND mt.trip_id = P_TRIP_ID
2205: AND med.exception_detail_id = P_EXCEPTION_DETAIL_ID;
2206: fnd_message.set_token('TRIP_NUMBER', l_id1);
2207:
2208: fnd_message.set_token('PEAK_WEIGHT_UTILIZATION', round(l_id2 * 100));
2209: fnd_message.set_token('PEAK_VOLUME_UTILIZATION', round(l_id3 * 100));
2210: fnd_message.set_token('PEAK_PALLET_UTILIZATION', round(l_id4 * 100));
2211:
2212: fnd_message.set_token('PEAK_WEIGHT', round(l_id2 * l_id5));
2205: AND med.exception_detail_id = P_EXCEPTION_DETAIL_ID;
2206: fnd_message.set_token('TRIP_NUMBER', l_id1);
2207:
2208: fnd_message.set_token('PEAK_WEIGHT_UTILIZATION', round(l_id2 * 100));
2209: fnd_message.set_token('PEAK_VOLUME_UTILIZATION', round(l_id3 * 100));
2210: fnd_message.set_token('PEAK_PALLET_UTILIZATION', round(l_id4 * 100));
2211:
2212: fnd_message.set_token('PEAK_WEIGHT', round(l_id2 * l_id5));
2213: fnd_message.set_token('PEAK_VOLUME', round(l_id3 * l_id6));
2206: fnd_message.set_token('TRIP_NUMBER', l_id1);
2207:
2208: fnd_message.set_token('PEAK_WEIGHT_UTILIZATION', round(l_id2 * 100));
2209: fnd_message.set_token('PEAK_VOLUME_UTILIZATION', round(l_id3 * 100));
2210: fnd_message.set_token('PEAK_PALLET_UTILIZATION', round(l_id4 * 100));
2211:
2212: fnd_message.set_token('PEAK_WEIGHT', round(l_id2 * l_id5));
2213: fnd_message.set_token('PEAK_VOLUME', round(l_id3 * l_id6));
2214: fnd_message.set_token('PEAK_PALLETS', round(l_id4 * l_id7));
2208: fnd_message.set_token('PEAK_WEIGHT_UTILIZATION', round(l_id2 * 100));
2209: fnd_message.set_token('PEAK_VOLUME_UTILIZATION', round(l_id3 * 100));
2210: fnd_message.set_token('PEAK_PALLET_UTILIZATION', round(l_id4 * 100));
2211:
2212: fnd_message.set_token('PEAK_WEIGHT', round(l_id2 * l_id5));
2213: fnd_message.set_token('PEAK_VOLUME', round(l_id3 * l_id6));
2214: fnd_message.set_token('PEAK_PALLETS', round(l_id4 * l_id7));
2215:
2216: fnd_message.set_token('MAX_WEIGHT', round(l_id5));
2209: fnd_message.set_token('PEAK_VOLUME_UTILIZATION', round(l_id3 * 100));
2210: fnd_message.set_token('PEAK_PALLET_UTILIZATION', round(l_id4 * 100));
2211:
2212: fnd_message.set_token('PEAK_WEIGHT', round(l_id2 * l_id5));
2213: fnd_message.set_token('PEAK_VOLUME', round(l_id3 * l_id6));
2214: fnd_message.set_token('PEAK_PALLETS', round(l_id4 * l_id7));
2215:
2216: fnd_message.set_token('MAX_WEIGHT', round(l_id5));
2217: fnd_message.set_token('MAX_VOLUME', round(l_id6));
2210: fnd_message.set_token('PEAK_PALLET_UTILIZATION', round(l_id4 * 100));
2211:
2212: fnd_message.set_token('PEAK_WEIGHT', round(l_id2 * l_id5));
2213: fnd_message.set_token('PEAK_VOLUME', round(l_id3 * l_id6));
2214: fnd_message.set_token('PEAK_PALLETS', round(l_id4 * l_id7));
2215:
2216: fnd_message.set_token('MAX_WEIGHT', round(l_id5));
2217: fnd_message.set_token('MAX_VOLUME', round(l_id6));
2218: fnd_message.set_token('MAX_PALLETS', round(l_id7));
2212: fnd_message.set_token('PEAK_WEIGHT', round(l_id2 * l_id5));
2213: fnd_message.set_token('PEAK_VOLUME', round(l_id3 * l_id6));
2214: fnd_message.set_token('PEAK_PALLETS', round(l_id4 * l_id7));
2215:
2216: fnd_message.set_token('MAX_WEIGHT', round(l_id5));
2217: fnd_message.set_token('MAX_VOLUME', round(l_id6));
2218: fnd_message.set_token('MAX_PALLETS', round(l_id7));
2219:
2220: SELECT weight_uom, volume_uom
2213: fnd_message.set_token('PEAK_VOLUME', round(l_id3 * l_id6));
2214: fnd_message.set_token('PEAK_PALLETS', round(l_id4 * l_id7));
2215:
2216: fnd_message.set_token('MAX_WEIGHT', round(l_id5));
2217: fnd_message.set_token('MAX_VOLUME', round(l_id6));
2218: fnd_message.set_token('MAX_PALLETS', round(l_id7));
2219:
2220: SELECT weight_uom, volume_uom
2221: INTO l_temp, l_temp1
2214: fnd_message.set_token('PEAK_PALLETS', round(l_id4 * l_id7));
2215:
2216: fnd_message.set_token('MAX_WEIGHT', round(l_id5));
2217: fnd_message.set_token('MAX_VOLUME', round(l_id6));
2218: fnd_message.set_token('MAX_PALLETS', round(l_id7));
2219:
2220: SELECT weight_uom, volume_uom
2221: INTO l_temp, l_temp1
2222: FROM mst_plans
2221: INTO l_temp, l_temp1
2222: FROM mst_plans
2223: WHERE plan_id = P_PLAN_ID;
2224:
2225: fnd_message.set_token('WEIGHT_UOM', l_temp);
2226: fnd_message.set_token('VOLUME_UOM', l_temp1);
2227:
2228: l_message := fnd_message.get;
2229: END IF;
2222: FROM mst_plans
2223: WHERE plan_id = P_PLAN_ID;
2224:
2225: fnd_message.set_token('WEIGHT_UOM', l_temp);
2226: fnd_message.set_token('VOLUME_UOM', l_temp1);
2227:
2228: l_message := fnd_message.get;
2229: END IF;
2230: ELSIF l_exception_type = 800 THEN
2224:
2225: fnd_message.set_token('WEIGHT_UOM', l_temp);
2226: fnd_message.set_token('VOLUME_UOM', l_temp1);
2227:
2228: l_message := fnd_message.get;
2229: END IF;
2230: ELSIF l_exception_type = 800 THEN
2231: IF P_LINE_NUM = 1 THEN
2232: fnd_message.set_name('MST', 'MST_EXCEP_TOKEN_800_1');
2228: l_message := fnd_message.get;
2229: END IF;
2230: ELSIF l_exception_type = 800 THEN
2231: IF P_LINE_NUM = 1 THEN
2232: fnd_message.set_name('MST', 'MST_EXCEP_TOKEN_800_1');
2233: SELECT trip_number
2234: INTO l_id1
2235: FROM mst_trips
2236: WHERE plan_id = P_PLAN_ID
2234: INTO l_id1
2235: FROM mst_trips
2236: WHERE plan_id = P_PLAN_ID
2237: AND trip_id = P_TRIP_ID;
2238: fnd_message.set_token('TRIP_NUMBER', l_id1);
2239:
2240: SELECT wc.freight_code
2241: INTO l_temp
2242: FROM mst_exception_details med,
2243: wsh_carriers wc
2244: WHERE med.plan_id = P_PLAN_ID
2245: AND med.exception_detail_id = P_EXCEPTION_DETAIL_ID
2246: AND med.carrier_id = wc.carrier_id;
2247: fnd_message.set_token('FREIGHT_CODE', l_temp);
2248:
2249: SELECT Get_Partner_Name(decode(med.customer_id, null, med.supplier_id, med.customer_id),
2250: decode(med.customer_id, null, 2, 1))
2251: INTO l_temp
2252: FROM mst_exception_details med
2253: WHERE med.plan_id = P_PLAN_ID
2254: AND med.exception_detail_id = P_EXCEPTION_DETAIL_ID;
2255:
2256: fnd_message.set_token('TRADING_PARTNER', l_temp);
2257: fnd_message.set_token('FACILITY_DESC', 'facility description');
2258:
2259: l_message := fnd_message.get;
2260: END IF;
2253: WHERE med.plan_id = P_PLAN_ID
2254: AND med.exception_detail_id = P_EXCEPTION_DETAIL_ID;
2255:
2256: fnd_message.set_token('TRADING_PARTNER', l_temp);
2257: fnd_message.set_token('FACILITY_DESC', 'facility description');
2258:
2259: l_message := fnd_message.get;
2260: END IF;
2261: ELSIF l_exception_type = 801 THEN
2255:
2256: fnd_message.set_token('TRADING_PARTNER', l_temp);
2257: fnd_message.set_token('FACILITY_DESC', 'facility description');
2258:
2259: l_message := fnd_message.get;
2260: END IF;
2261: ELSIF l_exception_type = 801 THEN
2262: IF P_LINE_NUM = 1 THEN
2263: fnd_message.set_name('MST', 'MST_EXCEP_TOKEN_801_1');
2259: l_message := fnd_message.get;
2260: END IF;
2261: ELSIF l_exception_type = 801 THEN
2262: IF P_LINE_NUM = 1 THEN
2263: fnd_message.set_name('MST', 'MST_EXCEP_TOKEN_801_1');
2264: SELECT trip_number, carrier_id
2265: INTO l_id1, l_id2
2266: FROM mst_trips
2267: WHERE plan_id = P_PLAN_ID
2265: INTO l_id1, l_id2
2266: FROM mst_trips
2267: WHERE plan_id = P_PLAN_ID
2268: AND trip_id = P_TRIP_ID;
2269: fnd_message.set_token('TRIP_NUMBER', l_id1);
2270:
2271: SELECT wc.freight_code
2272: INTO l_temp
2273: FROM wsh_carriers wc
2271: SELECT wc.freight_code
2272: INTO l_temp
2273: FROM wsh_carriers wc
2274: WHERE wc.carrier_id = l_id2;
2275: fnd_message.set_token('FREIGHT_CODE', l_temp);
2276:
2277: SELECT flp.description
2278: INTO l_temp
2279: FROM fte_location_parameters flp,
2282: WHERE med.stop_id1 = mts.stop_id
2283: AND mts.plan_id = P_PLAN_ID
2284: AND med.exception_detail_id = P_EXCEPTION_DETAIL_ID
2285: AND mts.stop_location_id = flp.location_id;
2286: fnd_message.set_token('FACILITY_DESC', l_temp);
2287:
2288: SELECT Get_Partner_Name(decode(med.customer_id, null, med.supplier_id, med.customer_id),
2289: decode(med.customer_id, null, 2, 1))
2290: INTO l_temp
2290: INTO l_temp
2291: FROM mst_exception_details med
2292: WHERE med.plan_id = P_PLAN_ID
2293: AND med.exception_detail_id = P_EXCEPTION_DETAIL_ID;
2294: fnd_message.set_token('CUST_SUPPLIER', l_temp);
2295: l_message := fnd_message.get;
2296: END IF;
2297: ELSIF l_exception_type = 803 THEN
2298: IF P_LINE_NUM = 1 THEN
2291: FROM mst_exception_details med
2292: WHERE med.plan_id = P_PLAN_ID
2293: AND med.exception_detail_id = P_EXCEPTION_DETAIL_ID;
2294: fnd_message.set_token('CUST_SUPPLIER', l_temp);
2295: l_message := fnd_message.get;
2296: END IF;
2297: ELSIF l_exception_type = 803 THEN
2298: IF P_LINE_NUM = 1 THEN
2299: fnd_message.set_name('MST', 'MST_EXCEP_TOKEN_803_1');
2295: l_message := fnd_message.get;
2296: END IF;
2297: ELSIF l_exception_type = 803 THEN
2298: IF P_LINE_NUM = 1 THEN
2299: fnd_message.set_name('MST', 'MST_EXCEP_TOKEN_803_1');
2300: SELECT trip_number
2301: INTO l_id1
2302: FROM mst_trips
2303: WHERE plan_id = P_PLAN_ID
2301: INTO l_id1
2302: FROM mst_trips
2303: WHERE plan_id = P_PLAN_ID
2304: AND trip_id = P_TRIP_ID;
2305: fnd_message.set_token('TRIP_NUMBER', l_id1);
2306:
2307: SELECT Get_Partner_Name(med.customer_id, 1)
2308: INTO l_temp
2309: FROM mst_exception_details med
2308: INTO l_temp
2309: FROM mst_exception_details med
2310: WHERE med.plan_id = P_PLAN_ID
2311: AND med.exception_detail_id = P_EXCEPTION_DETAIL_ID;
2312: fnd_message.set_token('CUSTOMER1', l_temp);
2313:
2314: SELECT Get_Partner_Name(med.number1, 1)
2315: INTO l_temp
2316: FROM mst_exception_details med
2315: INTO l_temp
2316: FROM mst_exception_details med
2317: WHERE med.plan_id = P_PLAN_ID
2318: AND med.exception_detail_id = P_EXCEPTION_DETAIL_ID;
2319: fnd_message.set_token('CUSTOMER2', l_temp);
2320:
2321: l_message := fnd_message.get;
2322: END IF;
2323: ELSE
2317: WHERE med.plan_id = P_PLAN_ID
2318: AND med.exception_detail_id = P_EXCEPTION_DETAIL_ID;
2319: fnd_message.set_token('CUSTOMER2', l_temp);
2320:
2321: l_message := fnd_message.get;
2322: END IF;
2323: ELSE
2324: SELECT ml.meaning INTO l_message
2325: FROM mfg_lookups ml, mst_exception_details med
4345:
4346: request_id := l_req_id;
4347:
4348: if (l_req_id = 0) then
4349: -- fnd_message.retrieve;
4350: -- fnd_message.error;
4351: null;
4352: else
4353: commit;
4346: request_id := l_req_id;
4347:
4348: if (l_req_id = 0) then
4349: -- fnd_message.retrieve;
4350: -- fnd_message.error;
4351: null;
4352: else
4353: commit;
4354: end if;
4463:
4464: l_new_trip VARCHAR2(200);
4465:
4466: BEGIN
4467: FND_MESSAGE.SET_NAME('MST', 'MST_NEW_TRIP');
4468: l_new_trip := FND_MESSAGE.GET;
4469: IF P_EXCEPTION_TYPE IN (100, 101, 102, 103) THEN
4470: SELECT nvl(number1, 0), round(nvl(number2, 0), 1)
4471: INTO l_id1, l_id2
4464: l_new_trip VARCHAR2(200);
4465:
4466: BEGIN
4467: FND_MESSAGE.SET_NAME('MST', 'MST_NEW_TRIP');
4468: l_new_trip := FND_MESSAGE.GET;
4469: IF P_EXCEPTION_TYPE IN (100, 101, 102, 103) THEN
4470: SELECT nvl(number1, 0), round(nvl(number2, 0), 1)
4471: INTO l_id1, l_id2
4472: FROM mst_out_request_details
4472: FROM mst_out_request_details
4473: WHERE out_request_detail_id = P_OUT_REQUEST_DETAIL_ID
4474: AND exception_type = P_EXCEPTION_TYPE;
4475: l_str1 := ltrim(to_char(l_id2, '999990.0'));
4476: fnd_message.set_name('MST', 'MST_EXCEP_TOKEN_' || P_EXCEPTION_TYPE || '_1');
4477: fnd_message.set_token('DELIVERY_DETAIL_ID', l_id1);
4478: fnd_message.set_token('SEVERITY', l_str1);
4479: l_message := fnd_message.get;
4480: ELSIF P_EXCEPTION_TYPE = 200 THEN
4473: WHERE out_request_detail_id = P_OUT_REQUEST_DETAIL_ID
4474: AND exception_type = P_EXCEPTION_TYPE;
4475: l_str1 := ltrim(to_char(l_id2, '999990.0'));
4476: fnd_message.set_name('MST', 'MST_EXCEP_TOKEN_' || P_EXCEPTION_TYPE || '_1');
4477: fnd_message.set_token('DELIVERY_DETAIL_ID', l_id1);
4478: fnd_message.set_token('SEVERITY', l_str1);
4479: l_message := fnd_message.get;
4480: ELSIF P_EXCEPTION_TYPE = 200 THEN
4481: IF P_LINE_NUM = 1 THEN
4474: AND exception_type = P_EXCEPTION_TYPE;
4475: l_str1 := ltrim(to_char(l_id2, '999990.0'));
4476: fnd_message.set_name('MST', 'MST_EXCEP_TOKEN_' || P_EXCEPTION_TYPE || '_1');
4477: fnd_message.set_token('DELIVERY_DETAIL_ID', l_id1);
4478: fnd_message.set_token('SEVERITY', l_str1);
4479: l_message := fnd_message.get;
4480: ELSIF P_EXCEPTION_TYPE = 200 THEN
4481: IF P_LINE_NUM = 1 THEN
4482: SELECT number1, number2, number3, number4
4475: l_str1 := ltrim(to_char(l_id2, '999990.0'));
4476: fnd_message.set_name('MST', 'MST_EXCEP_TOKEN_' || P_EXCEPTION_TYPE || '_1');
4477: fnd_message.set_token('DELIVERY_DETAIL_ID', l_id1);
4478: fnd_message.set_token('SEVERITY', l_str1);
4479: l_message := fnd_message.get;
4480: ELSIF P_EXCEPTION_TYPE = 200 THEN
4481: IF P_LINE_NUM = 1 THEN
4482: SELECT number1, number2, number3, number4
4483: INTO l_id1, l_id2, l_id3, l_id4
4483: INTO l_id1, l_id2, l_id3, l_id4
4484: FROM mst_out_request_details
4485: WHERE out_request_detail_id = P_OUT_REQUEST_DETAIL_ID
4486: AND exception_type = P_EXCEPTION_TYPE;
4487: fnd_message.set_name('MST', 'MST_EXCEP_TOKEN_200_1');
4488: IF l_id1 < 0 THEN
4489: fnd_message.set_token('TRIP_NUMBER', l_new_trip);
4490: ELSE
4491: fnd_message.set_token('TRIP_NUMBER', l_id1);
4485: WHERE out_request_detail_id = P_OUT_REQUEST_DETAIL_ID
4486: AND exception_type = P_EXCEPTION_TYPE;
4487: fnd_message.set_name('MST', 'MST_EXCEP_TOKEN_200_1');
4488: IF l_id1 < 0 THEN
4489: fnd_message.set_token('TRIP_NUMBER', l_new_trip);
4490: ELSE
4491: fnd_message.set_token('TRIP_NUMBER', l_id1);
4492: END IF;
4493: Open l_city_cur(l_id3);
4487: fnd_message.set_name('MST', 'MST_EXCEP_TOKEN_200_1');
4488: IF l_id1 < 0 THEN
4489: fnd_message.set_token('TRIP_NUMBER', l_new_trip);
4490: ELSE
4491: fnd_message.set_token('TRIP_NUMBER', l_id1);
4492: END IF;
4493: Open l_city_cur(l_id3);
4494: Fetch l_city_cur Into l_str1;
4495: If l_city_cur%NotFound Then
4495: If l_city_cur%NotFound Then
4496: l_str1 := '';
4497: End If;
4498: Close l_city_cur;
4499: fnd_message.set_token('ORIGIN_CITY', l_str1);
4500:
4501: Open l_city_cur(l_id4);
4502: Fetch l_city_cur Into l_str2;
4503: If l_city_cur%NotFound Then
4503: If l_city_cur%NotFound Then
4504: l_str2 := '';
4505: End If;
4506: Close l_city_cur;
4507: fnd_message.set_token('DESTINATION_CITY', l_str2);
4508:
4509: Open l_carrier_cur(l_id2);
4510: Fetch l_carrier_cur Into l_str3;
4511: If l_carrier_cur%NotFound Then
4511: If l_carrier_cur%NotFound Then
4512: l_str3 := '';
4513: End If;
4514: Close l_carrier_cur;
4515: fnd_message.set_token('FREIGHT_CODE', l_str3);
4516:
4517: SELECT round(nvl(number7, 0)), round(nvl(number8, 0))
4518: INTO l_id1, l_id2
4519: FROM mst_out_request_details
4518: INTO l_id1, l_id2
4519: FROM mst_out_request_details
4520: WHERE out_request_detail_id = P_OUT_REQUEST_DETAIL_ID
4521: AND exception_type = P_EXCEPTION_TYPE;
4522: fnd_message.set_token('MAX_DISTANCE_IN_24HR', l_id2);
4523: fnd_message.set_token('DISTANCE_IN_24HR', l_id1);
4524:
4525: SELECT round(nvl(number5, 0)), round(nvl(number6, 0))
4526: INTO l_id1, l_id2
4519: FROM mst_out_request_details
4520: WHERE out_request_detail_id = P_OUT_REQUEST_DETAIL_ID
4521: AND exception_type = P_EXCEPTION_TYPE;
4522: fnd_message.set_token('MAX_DISTANCE_IN_24HR', l_id2);
4523: fnd_message.set_token('DISTANCE_IN_24HR', l_id1);
4524:
4525: SELECT round(nvl(number5, 0)), round(nvl(number6, 0))
4526: INTO l_id1, l_id2
4527: FROM mst_out_request_details
4535: Else
4536: l_str1 := '';
4537: End If;
4538: Close l_uom_cur;
4539: fnd_message.set_token('DISTANCE_UOM', l_str1);
4540: fnd_message.set_token('MAXIMUM_DISTANCE', l_id2);
4541: fnd_message.set_token('DISTANCE', l_id1);
4542: END IF;
4543: l_message := fnd_message.get;
4536: l_str1 := '';
4537: End If;
4538: Close l_uom_cur;
4539: fnd_message.set_token('DISTANCE_UOM', l_str1);
4540: fnd_message.set_token('MAXIMUM_DISTANCE', l_id2);
4541: fnd_message.set_token('DISTANCE', l_id1);
4542: END IF;
4543: l_message := fnd_message.get;
4544: ELSIF P_EXCEPTION_TYPE = 201 THEN
4537: End If;
4538: Close l_uom_cur;
4539: fnd_message.set_token('DISTANCE_UOM', l_str1);
4540: fnd_message.set_token('MAXIMUM_DISTANCE', l_id2);
4541: fnd_message.set_token('DISTANCE', l_id1);
4542: END IF;
4543: l_message := fnd_message.get;
4544: ELSIF P_EXCEPTION_TYPE = 201 THEN
4545: SELECT number1, number2, number3, number4, round(nvl(number5, 0)), round(nvl(number6, 0))
4539: fnd_message.set_token('DISTANCE_UOM', l_str1);
4540: fnd_message.set_token('MAXIMUM_DISTANCE', l_id2);
4541: fnd_message.set_token('DISTANCE', l_id1);
4542: END IF;
4543: l_message := fnd_message.get;
4544: ELSIF P_EXCEPTION_TYPE = 201 THEN
4545: SELECT number1, number2, number3, number4, round(nvl(number5, 0)), round(nvl(number6, 0))
4546: INTO l_id1, l_id2, l_id3, l_id4, l_id5, l_id6
4547: FROM mst_out_request_details
4547: FROM mst_out_request_details
4548: WHERE out_request_detail_id = P_OUT_REQUEST_DETAIL_ID
4549: AND exception_type = P_EXCEPTION_TYPE;
4550:
4551: fnd_message.set_name('MST', 'MST_EXCEP_TOKEN_201_1');
4552: fnd_message.set_token('CM_TRIP_NUMBER', l_id1);
4553: fnd_message.set_token('ACTUAL_DEADHEAD_DISTANCE', l_id5);
4554: fnd_message.set_token('MAX_DEADHEAD_DISTANCE', l_id6);
4555: Open l_uom_cur;
4548: WHERE out_request_detail_id = P_OUT_REQUEST_DETAIL_ID
4549: AND exception_type = P_EXCEPTION_TYPE;
4550:
4551: fnd_message.set_name('MST', 'MST_EXCEP_TOKEN_201_1');
4552: fnd_message.set_token('CM_TRIP_NUMBER', l_id1);
4553: fnd_message.set_token('ACTUAL_DEADHEAD_DISTANCE', l_id5);
4554: fnd_message.set_token('MAX_DEADHEAD_DISTANCE', l_id6);
4555: Open l_uom_cur;
4556: Fetch l_uom_cur Into l_uom;
4549: AND exception_type = P_EXCEPTION_TYPE;
4550:
4551: fnd_message.set_name('MST', 'MST_EXCEP_TOKEN_201_1');
4552: fnd_message.set_token('CM_TRIP_NUMBER', l_id1);
4553: fnd_message.set_token('ACTUAL_DEADHEAD_DISTANCE', l_id5);
4554: fnd_message.set_token('MAX_DEADHEAD_DISTANCE', l_id6);
4555: Open l_uom_cur;
4556: Fetch l_uom_cur Into l_uom;
4557: If l_uom_cur%Found Then
4550:
4551: fnd_message.set_name('MST', 'MST_EXCEP_TOKEN_201_1');
4552: fnd_message.set_token('CM_TRIP_NUMBER', l_id1);
4553: fnd_message.set_token('ACTUAL_DEADHEAD_DISTANCE', l_id5);
4554: fnd_message.set_token('MAX_DEADHEAD_DISTANCE', l_id6);
4555: Open l_uom_cur;
4556: Fetch l_uom_cur Into l_uom;
4557: If l_uom_cur%Found Then
4558: l_str1 := l_uom.distance_uom;
4559: Else
4560: l_str1 := '';
4561: End If;
4562: Close l_uom_cur;
4563: fnd_message.set_token('DISTANCE_UOM', l_str1);
4564: Open l_city_cur(l_id3);
4565: Fetch l_city_cur Into l_str1;
4566: If l_city_cur%NotFound Then
4567: l_str1 := '';
4566: If l_city_cur%NotFound Then
4567: l_str1 := '';
4568: End If;
4569: Close l_city_cur;
4570: fnd_message.set_token('ORIGIN_CITY', l_str1);
4571:
4572: Open l_city_cur(l_id4);
4573: Fetch l_city_cur Into l_str2;
4574: If l_city_cur%NotFound Then
4574: If l_city_cur%NotFound Then
4575: l_str2 := '';
4576: End If;
4577: Close l_city_cur;
4578: fnd_message.set_token('DESTINATION_CITY', l_str2);
4579: Open l_carrier_cur(l_id2);
4580: Fetch l_carrier_cur Into l_str3;
4581: If l_carrier_cur%NotFound Then
4582: l_str3 := '';
4581: If l_carrier_cur%NotFound Then
4582: l_str3 := '';
4583: End If;
4584: Close l_carrier_cur;
4585: fnd_message.set_token('FREIGHT_CODE', l_str3);
4586: l_message := fnd_message.get;
4587: ELSIF P_EXCEPTION_TYPE = 202 THEN
4588: SELECT nvl(number1, -1)
4589: INTO l_id1
4582: l_str3 := '';
4583: End If;
4584: Close l_carrier_cur;
4585: fnd_message.set_token('FREIGHT_CODE', l_str3);
4586: l_message := fnd_message.get;
4587: ELSIF P_EXCEPTION_TYPE = 202 THEN
4588: SELECT nvl(number1, -1)
4589: INTO l_id1
4590: FROM mst_out_request_details
4596: FROM mst_trips
4597: WHERE plan_id = P_PLAN_ID
4598: AND trip_id = l_id1;
4599:
4600: fnd_message.set_name('MST', 'MST_EXCEP_TOKEN_202_1');
4601: IF l_id2 < 0 THEN
4602: fnd_message.set_token('TRIP_NUMBER', l_new_trip);
4603: ELSE
4604: fnd_message.set_token('TRIP_NUMBER', l_id2);
4598: AND trip_id = l_id1;
4599:
4600: fnd_message.set_name('MST', 'MST_EXCEP_TOKEN_202_1');
4601: IF l_id2 < 0 THEN
4602: fnd_message.set_token('TRIP_NUMBER', l_new_trip);
4603: ELSE
4604: fnd_message.set_token('TRIP_NUMBER', l_id2);
4605: END IF;
4606:
4600: fnd_message.set_name('MST', 'MST_EXCEP_TOKEN_202_1');
4601: IF l_id2 < 0 THEN
4602: fnd_message.set_token('TRIP_NUMBER', l_new_trip);
4603: ELSE
4604: fnd_message.set_token('TRIP_NUMBER', l_id2);
4605: END IF;
4606:
4607: Open l_city_cur(l_id3);
4608: Fetch l_city_cur Into l_str1;
4609: If l_city_cur%NotFound Then
4610: l_str1 := '';
4611: End If;
4612: Close l_city_cur;
4613: fnd_message.set_token('ORIGIN_CITY', l_str1);
4614:
4615: Open l_city_cur(l_id4);
4616: Fetch l_city_cur Into l_str2;
4617: If l_city_cur%NotFound Then
4617: If l_city_cur%NotFound Then
4618: l_str2 := '';
4619: End If;
4620: Close l_city_cur;
4621: fnd_message.set_token('DESTINATION_CITY', l_str2);
4622: l_message := fnd_message.get;
4623: ELSIF P_EXCEPTION_TYPE = 203 THEN
4624: fnd_message.set_name('MST', 'MST_EXCEP_TOKEN_203_1');
4625: SELECT nvl(number1, -1), nvl(number2, -1)
4618: l_str2 := '';
4619: End If;
4620: Close l_city_cur;
4621: fnd_message.set_token('DESTINATION_CITY', l_str2);
4622: l_message := fnd_message.get;
4623: ELSIF P_EXCEPTION_TYPE = 203 THEN
4624: fnd_message.set_name('MST', 'MST_EXCEP_TOKEN_203_1');
4625: SELECT nvl(number1, -1), nvl(number2, -1)
4626: INTO l_id1, l_id2
4620: Close l_city_cur;
4621: fnd_message.set_token('DESTINATION_CITY', l_str2);
4622: l_message := fnd_message.get;
4623: ELSIF P_EXCEPTION_TYPE = 203 THEN
4624: fnd_message.set_name('MST', 'MST_EXCEP_TOKEN_203_1');
4625: SELECT nvl(number1, -1), nvl(number2, -1)
4626: INTO l_id1, l_id2
4627: FROM mst_out_request_details
4628: WHERE out_request_detail_id = P_OUT_REQUEST_DETAIL_ID
4634: WHERE trip_id = l_id1
4635: AND plan_id = P_PLAN_ID;
4636:
4637: IF l_id3 < 0 THEN
4638: fnd_message.set_token('TRIP_NUMBER', l_new_trip);
4639: ELSE
4640: fnd_message.set_token('TRIP_NUMBER', l_id3);
4641: END IF;
4642:
4636:
4637: IF l_id3 < 0 THEN
4638: fnd_message.set_token('TRIP_NUMBER', l_new_trip);
4639: ELSE
4640: fnd_message.set_token('TRIP_NUMBER', l_id3);
4641: END IF;
4642:
4643: SELECT stop_location_id, stop_sequence_number
4644: INTO l_id4, l_id5
4644: INTO l_id4, l_id5
4645: FROM mst_trip_stops
4646: WHERE plan_id = P_PLAN_ID
4647: AND stop_id = l_id2;
4648: fnd_message.set_token('STOP_SEQUENCE_NUMBER', l_id5);
4649:
4650: Open l_city_cur(l_id4);
4651: Fetch l_city_cur Into l_str1;
4652: If l_city_cur%NotFound Then
4652: If l_city_cur%NotFound Then
4653: l_str1 := '';
4654: End If;
4655: Close l_city_cur;
4656: fnd_message.set_token('CITY', l_str1);
4657: l_message := fnd_message.get;
4658: ELSIF P_EXCEPTION_TYPE = 204 THEN
4659: fnd_message.set_name('MST', 'MST_EXCEP_TOKEN_204_1');
4660: SELECT number1, number2, number3, round(nvl(number4, 0)), round(nvl(number5, 0))
4653: l_str1 := '';
4654: End If;
4655: Close l_city_cur;
4656: fnd_message.set_token('CITY', l_str1);
4657: l_message := fnd_message.get;
4658: ELSIF P_EXCEPTION_TYPE = 204 THEN
4659: fnd_message.set_name('MST', 'MST_EXCEP_TOKEN_204_1');
4660: SELECT number1, number2, number3, round(nvl(number4, 0)), round(nvl(number5, 0))
4661: INTO l_id1, l_id2, l_id3, l_id4, l_id5
4655: Close l_city_cur;
4656: fnd_message.set_token('CITY', l_str1);
4657: l_message := fnd_message.get;
4658: ELSIF P_EXCEPTION_TYPE = 204 THEN
4659: fnd_message.set_name('MST', 'MST_EXCEP_TOKEN_204_1');
4660: SELECT number1, number2, number3, round(nvl(number4, 0)), round(nvl(number5, 0))
4661: INTO l_id1, l_id2, l_id3, l_id4, l_id5
4662: FROM mst_out_request_details
4663: WHERE out_request_detail_id = P_OUT_REQUEST_DETAIL_ID
4663: WHERE out_request_detail_id = P_OUT_REQUEST_DETAIL_ID
4664: AND exception_type = P_EXCEPTION_TYPE;
4665:
4666: IF l_id1 < 0 THEN
4667: fnd_message.set_token('TRIP_NUMBER', l_new_trip);
4668: ELSE
4669: fnd_message.set_token('TRIP_NUMBER', l_id1);
4670: END IF;
4671:
4665:
4666: IF l_id1 < 0 THEN
4667: fnd_message.set_token('TRIP_NUMBER', l_new_trip);
4668: ELSE
4669: fnd_message.set_token('TRIP_NUMBER', l_id1);
4670: END IF;
4671:
4672: fnd_message.set_token('PLANNED_TRANSIT_TIME', l_id4);
4673: fnd_message.set_token('REQUIRED_TRANSIT_TIME', l_id5);
4668: ELSE
4669: fnd_message.set_token('TRIP_NUMBER', l_id1);
4670: END IF;
4671:
4672: fnd_message.set_token('PLANNED_TRANSIT_TIME', l_id4);
4673: fnd_message.set_token('REQUIRED_TRANSIT_TIME', l_id5);
4674:
4675: Open l_city_cur(l_id2);
4676: Fetch l_city_cur Into l_str1;
4669: fnd_message.set_token('TRIP_NUMBER', l_id1);
4670: END IF;
4671:
4672: fnd_message.set_token('PLANNED_TRANSIT_TIME', l_id4);
4673: fnd_message.set_token('REQUIRED_TRANSIT_TIME', l_id5);
4674:
4675: Open l_city_cur(l_id2);
4676: Fetch l_city_cur Into l_str1;
4677: If l_city_cur%NotFound Then
4677: If l_city_cur%NotFound Then
4678: l_str1 := '';
4679: End If;
4680: Close l_city_cur;
4681: fnd_message.set_token('ORIGIN_CITY', l_str1);
4682:
4683: Open l_city_cur(l_id3);
4684: Fetch l_city_cur Into l_str2;
4685: If l_city_cur%NotFound Then
4685: If l_city_cur%NotFound Then
4686: l_str2 := '';
4687: End If;
4688: Close l_city_cur;
4689: fnd_message.set_token('DESTINATION_CITY', l_str2);
4690: l_message := fnd_message.get;
4691: ELSIF P_EXCEPTION_TYPE = 205 THEN
4692: SELECT number1, round(nvl(number2, 0)), round(nvl(number3, 0)), round(nvl(number4, 0)), round(nvl(number5, 0)), round(nvl(number6, 0)), round(nvl(number7, 0)), round(nvl(number8, 0)), round(nvl(number9, 0))
4693: INTO l_id1, l_id2, l_id3, l_id4, l_id5, l_id6, l_id7, l_id8, l_id9
4686: l_str2 := '';
4687: End If;
4688: Close l_city_cur;
4689: fnd_message.set_token('DESTINATION_CITY', l_str2);
4690: l_message := fnd_message.get;
4691: ELSIF P_EXCEPTION_TYPE = 205 THEN
4692: SELECT number1, round(nvl(number2, 0)), round(nvl(number3, 0)), round(nvl(number4, 0)), round(nvl(number5, 0)), round(nvl(number6, 0)), round(nvl(number7, 0)), round(nvl(number8, 0)), round(nvl(number9, 0))
4693: INTO l_id1, l_id2, l_id3, l_id4, l_id5, l_id6, l_id7, l_id8, l_id9
4694: FROM mst_out_request_details
4693: INTO l_id1, l_id2, l_id3, l_id4, l_id5, l_id6, l_id7, l_id8, l_id9
4694: FROM mst_out_request_details
4695: WHERE out_request_detail_id = P_OUT_REQUEST_DETAIL_ID
4696: AND exception_type = P_EXCEPTION_TYPE;
4697: fnd_message.set_name('MST', 'MST_EXCEP_TOKEN_205_1');
4698: IF l_id1 < 0 THEN
4699: fnd_message.set_token('TRIP_NUMBER', l_new_trip);
4700: ELSE
4701: fnd_message.set_token('TRIP_NUMBER', l_id1);
4695: WHERE out_request_detail_id = P_OUT_REQUEST_DETAIL_ID
4696: AND exception_type = P_EXCEPTION_TYPE;
4697: fnd_message.set_name('MST', 'MST_EXCEP_TOKEN_205_1');
4698: IF l_id1 < 0 THEN
4699: fnd_message.set_token('TRIP_NUMBER', l_new_trip);
4700: ELSE
4701: fnd_message.set_token('TRIP_NUMBER', l_id1);
4702: END IF;
4703: fnd_message.set_token('MINIMUM_LAYOVER_TIME', l_id3);
4697: fnd_message.set_name('MST', 'MST_EXCEP_TOKEN_205_1');
4698: IF l_id1 < 0 THEN
4699: fnd_message.set_token('TRIP_NUMBER', l_new_trip);
4700: ELSE
4701: fnd_message.set_token('TRIP_NUMBER', l_id1);
4702: END IF;
4703: fnd_message.set_token('MINIMUM_LAYOVER_TIME', l_id3);
4704: fnd_message.set_token('LAYOVER_TIME', l_id2);
4705: fnd_message.set_token('ALLOWED_DRIVING_TIME', l_id5);
4699: fnd_message.set_token('TRIP_NUMBER', l_new_trip);
4700: ELSE
4701: fnd_message.set_token('TRIP_NUMBER', l_id1);
4702: END IF;
4703: fnd_message.set_token('MINIMUM_LAYOVER_TIME', l_id3);
4704: fnd_message.set_token('LAYOVER_TIME', l_id2);
4705: fnd_message.set_token('ALLOWED_DRIVING_TIME', l_id5);
4706: fnd_message.set_token('DRIVING_TIME', l_id4);
4707: fnd_message.set_token('ALLOWED_DUTY_TIME', l_id7);
4700: ELSE
4701: fnd_message.set_token('TRIP_NUMBER', l_id1);
4702: END IF;
4703: fnd_message.set_token('MINIMUM_LAYOVER_TIME', l_id3);
4704: fnd_message.set_token('LAYOVER_TIME', l_id2);
4705: fnd_message.set_token('ALLOWED_DRIVING_TIME', l_id5);
4706: fnd_message.set_token('DRIVING_TIME', l_id4);
4707: fnd_message.set_token('ALLOWED_DUTY_TIME', l_id7);
4708: fnd_message.set_token('DUTY_TIME', l_id6);
4701: fnd_message.set_token('TRIP_NUMBER', l_id1);
4702: END IF;
4703: fnd_message.set_token('MINIMUM_LAYOVER_TIME', l_id3);
4704: fnd_message.set_token('LAYOVER_TIME', l_id2);
4705: fnd_message.set_token('ALLOWED_DRIVING_TIME', l_id5);
4706: fnd_message.set_token('DRIVING_TIME', l_id4);
4707: fnd_message.set_token('ALLOWED_DUTY_TIME', l_id7);
4708: fnd_message.set_token('DUTY_TIME', l_id6);
4709: fnd_message.set_token('MAX_DISTANCE', l_id9);
4702: END IF;
4703: fnd_message.set_token('MINIMUM_LAYOVER_TIME', l_id3);
4704: fnd_message.set_token('LAYOVER_TIME', l_id2);
4705: fnd_message.set_token('ALLOWED_DRIVING_TIME', l_id5);
4706: fnd_message.set_token('DRIVING_TIME', l_id4);
4707: fnd_message.set_token('ALLOWED_DUTY_TIME', l_id7);
4708: fnd_message.set_token('DUTY_TIME', l_id6);
4709: fnd_message.set_token('MAX_DISTANCE', l_id9);
4710: fnd_message.set_token('DISTANCE', l_id8);
4703: fnd_message.set_token('MINIMUM_LAYOVER_TIME', l_id3);
4704: fnd_message.set_token('LAYOVER_TIME', l_id2);
4705: fnd_message.set_token('ALLOWED_DRIVING_TIME', l_id5);
4706: fnd_message.set_token('DRIVING_TIME', l_id4);
4707: fnd_message.set_token('ALLOWED_DUTY_TIME', l_id7);
4708: fnd_message.set_token('DUTY_TIME', l_id6);
4709: fnd_message.set_token('MAX_DISTANCE', l_id9);
4710: fnd_message.set_token('DISTANCE', l_id8);
4711: l_message := fnd_message.get;
4704: fnd_message.set_token('LAYOVER_TIME', l_id2);
4705: fnd_message.set_token('ALLOWED_DRIVING_TIME', l_id5);
4706: fnd_message.set_token('DRIVING_TIME', l_id4);
4707: fnd_message.set_token('ALLOWED_DUTY_TIME', l_id7);
4708: fnd_message.set_token('DUTY_TIME', l_id6);
4709: fnd_message.set_token('MAX_DISTANCE', l_id9);
4710: fnd_message.set_token('DISTANCE', l_id8);
4711: l_message := fnd_message.get;
4712: ELSIF P_EXCEPTION_TYPE = 206 THEN
4705: fnd_message.set_token('ALLOWED_DRIVING_TIME', l_id5);
4706: fnd_message.set_token('DRIVING_TIME', l_id4);
4707: fnd_message.set_token('ALLOWED_DUTY_TIME', l_id7);
4708: fnd_message.set_token('DUTY_TIME', l_id6);
4709: fnd_message.set_token('MAX_DISTANCE', l_id9);
4710: fnd_message.set_token('DISTANCE', l_id8);
4711: l_message := fnd_message.get;
4712: ELSIF P_EXCEPTION_TYPE = 206 THEN
4713: IF P_LINE_NUM = 1 THEN
4706: fnd_message.set_token('DRIVING_TIME', l_id4);
4707: fnd_message.set_token('ALLOWED_DUTY_TIME', l_id7);
4708: fnd_message.set_token('DUTY_TIME', l_id6);
4709: fnd_message.set_token('MAX_DISTANCE', l_id9);
4710: fnd_message.set_token('DISTANCE', l_id8);
4711: l_message := fnd_message.get;
4712: ELSIF P_EXCEPTION_TYPE = 206 THEN
4713: IF P_LINE_NUM = 1 THEN
4714: SELECT number1, number2, number3, number4
4707: fnd_message.set_token('ALLOWED_DUTY_TIME', l_id7);
4708: fnd_message.set_token('DUTY_TIME', l_id6);
4709: fnd_message.set_token('MAX_DISTANCE', l_id9);
4710: fnd_message.set_token('DISTANCE', l_id8);
4711: l_message := fnd_message.get;
4712: ELSIF P_EXCEPTION_TYPE = 206 THEN
4713: IF P_LINE_NUM = 1 THEN
4714: SELECT number1, number2, number3, number4
4715: INTO l_id1, l_id2, l_id3, l_id4
4715: INTO l_id1, l_id2, l_id3, l_id4
4716: FROM mst_out_request_details
4717: WHERE out_request_detail_id = P_OUT_REQUEST_DETAIL_ID
4718: AND exception_type = P_EXCEPTION_TYPE;
4719: fnd_message.set_name('MST', 'MST_EXCEP_TOKEN_206_1');
4720: IF l_id1 < 0 THEN
4721: fnd_message.set_token('TRIP_NUMBER', l_new_trip);
4722: ELSE
4723: fnd_message.set_token('TRIP_NUMBER', l_id1);
4717: WHERE out_request_detail_id = P_OUT_REQUEST_DETAIL_ID
4718: AND exception_type = P_EXCEPTION_TYPE;
4719: fnd_message.set_name('MST', 'MST_EXCEP_TOKEN_206_1');
4720: IF l_id1 < 0 THEN
4721: fnd_message.set_token('TRIP_NUMBER', l_new_trip);
4722: ELSE
4723: fnd_message.set_token('TRIP_NUMBER', l_id1);
4724: END IF;
4725: Open l_city_cur(l_id3);
4719: fnd_message.set_name('MST', 'MST_EXCEP_TOKEN_206_1');
4720: IF l_id1 < 0 THEN
4721: fnd_message.set_token('TRIP_NUMBER', l_new_trip);
4722: ELSE
4723: fnd_message.set_token('TRIP_NUMBER', l_id1);
4724: END IF;
4725: Open l_city_cur(l_id3);
4726: Fetch l_city_cur Into l_str1;
4727: If l_city_cur%NotFound Then
4727: If l_city_cur%NotFound Then
4728: l_str1 := '';
4729: End If;
4730: Close l_city_cur;
4731: fnd_message.set_token('ORIGIN_CITY', l_str1);
4732:
4733: Open l_city_cur(l_id4);
4734: Fetch l_city_cur Into l_str2;
4735: If l_city_cur%NotFound Then
4735: If l_city_cur%NotFound Then
4736: l_str2 := '';
4737: End If;
4738: Close l_city_cur;
4739: fnd_message.set_token('DESTINATION_CITY', l_str2);
4740:
4741: Open l_carrier_cur(l_id2);
4742: Fetch l_carrier_cur Into l_str3;
4743: If l_carrier_cur%NotFound Then
4743: If l_carrier_cur%NotFound Then
4744: l_str3 := '';
4745: End If;
4746: Close l_carrier_cur;
4747: fnd_message.set_token('FREIGHT_CODE', l_str3);
4748: l_message := fnd_message.get;
4749: ELSIF P_LINE_NUM = 2 THEN
4750: SELECT nvl(number5, 0), nvl(number6, 0)
4751: INTO l_id1, l_id2
4744: l_str3 := '';
4745: End If;
4746: Close l_carrier_cur;
4747: fnd_message.set_token('FREIGHT_CODE', l_str3);
4748: l_message := fnd_message.get;
4749: ELSIF P_LINE_NUM = 2 THEN
4750: SELECT nvl(number5, 0), nvl(number6, 0)
4751: INTO l_id1, l_id2
4752: FROM mst_out_request_details
4751: INTO l_id1, l_id2
4752: FROM mst_out_request_details
4753: WHERE out_request_detail_id = P_OUT_REQUEST_DETAIL_ID
4754: AND exception_type = P_EXCEPTION_TYPE;
4755: fnd_message.set_name('MST', 'MST_EXCEP_TOKEN_206_2');
4756: fnd_message.set_token('MAX_STOPS', l_id2);
4757: fnd_message.set_token('STOPS', l_id1);
4758: l_message := fnd_message.get;
4759: END IF;
4752: FROM mst_out_request_details
4753: WHERE out_request_detail_id = P_OUT_REQUEST_DETAIL_ID
4754: AND exception_type = P_EXCEPTION_TYPE;
4755: fnd_message.set_name('MST', 'MST_EXCEP_TOKEN_206_2');
4756: fnd_message.set_token('MAX_STOPS', l_id2);
4757: fnd_message.set_token('STOPS', l_id1);
4758: l_message := fnd_message.get;
4759: END IF;
4760: ELSIF P_EXCEPTION_TYPE = 207 THEN
4753: WHERE out_request_detail_id = P_OUT_REQUEST_DETAIL_ID
4754: AND exception_type = P_EXCEPTION_TYPE;
4755: fnd_message.set_name('MST', 'MST_EXCEP_TOKEN_206_2');
4756: fnd_message.set_token('MAX_STOPS', l_id2);
4757: fnd_message.set_token('STOPS', l_id1);
4758: l_message := fnd_message.get;
4759: END IF;
4760: ELSIF P_EXCEPTION_TYPE = 207 THEN
4761: IF P_LINE_NUM = 1 THEN
4754: AND exception_type = P_EXCEPTION_TYPE;
4755: fnd_message.set_name('MST', 'MST_EXCEP_TOKEN_206_2');
4756: fnd_message.set_token('MAX_STOPS', l_id2);
4757: fnd_message.set_token('STOPS', l_id1);
4758: l_message := fnd_message.get;
4759: END IF;
4760: ELSIF P_EXCEPTION_TYPE = 207 THEN
4761: IF P_LINE_NUM = 1 THEN
4762: SELECT number1, number2, number3, number4
4763: INTO l_id1, l_id2, l_id3, l_id4
4764: FROM mst_out_request_details
4765: WHERE out_request_detail_id = P_OUT_REQUEST_DETAIL_ID
4766: AND exception_type = P_EXCEPTION_TYPE;
4767: fnd_message.set_name('MST', 'MST_EXCEP_TOKEN_207_1');
4768: IF l_id1 < 0 THEN
4769: fnd_message.set_token('TRIP_NUMBER', l_new_trip);
4770: ELSE
4771: fnd_message.set_token('TRIP_NUMBER', l_id1);
4765: WHERE out_request_detail_id = P_OUT_REQUEST_DETAIL_ID
4766: AND exception_type = P_EXCEPTION_TYPE;
4767: fnd_message.set_name('MST', 'MST_EXCEP_TOKEN_207_1');
4768: IF l_id1 < 0 THEN
4769: fnd_message.set_token('TRIP_NUMBER', l_new_trip);
4770: ELSE
4771: fnd_message.set_token('TRIP_NUMBER', l_id1);
4772: END IF;
4773: Open l_city_cur(l_id3);
4767: fnd_message.set_name('MST', 'MST_EXCEP_TOKEN_207_1');
4768: IF l_id1 < 0 THEN
4769: fnd_message.set_token('TRIP_NUMBER', l_new_trip);
4770: ELSE
4771: fnd_message.set_token('TRIP_NUMBER', l_id1);
4772: END IF;
4773: Open l_city_cur(l_id3);
4774: Fetch l_city_cur Into l_str1;
4775: If l_city_cur%NotFound Then
4775: If l_city_cur%NotFound Then
4776: l_str1 := '';
4777: End If;
4778: Close l_city_cur;
4779: fnd_message.set_token('ORIGIN_CITY', l_str1);
4780:
4781: Open l_city_cur(l_id4);
4782: Fetch l_city_cur Into l_str2;
4783: If l_city_cur%NotFound Then
4783: If l_city_cur%NotFound Then
4784: l_str2 := '';
4785: End If;
4786: Close l_city_cur;
4787: fnd_message.set_token('DESTINATION_CITY', l_str2);
4788:
4789: Open l_carrier_cur(l_id2);
4790: Fetch l_carrier_cur Into l_str3;
4791: If l_carrier_cur%NotFound Then
4791: If l_carrier_cur%NotFound Then
4792: l_str3 := '';
4793: End If;
4794: Close l_carrier_cur;
4795: fnd_message.set_token('FREIGHT_CODE', l_str3);
4796:
4797: SELECT round(nvl(number5, 0)), round(nvl(number6, 0))
4798: INTO l_id1, l_id2
4799: FROM mst_out_request_details
4798: INTO l_id1, l_id2
4799: FROM mst_out_request_details
4800: WHERE out_request_detail_id = P_OUT_REQUEST_DETAIL_ID
4801: AND exception_type = P_EXCEPTION_TYPE;
4802: fnd_message.set_token('ALLOWED_DRIVING_TIME', l_id2);
4803: fnd_message.set_token('DRIVING_TIME', l_id1);
4804:
4805: SELECT round(nvl(number7, 0)), round(nvl(number8, 0))
4806: INTO l_id1, l_id2
4799: FROM mst_out_request_details
4800: WHERE out_request_detail_id = P_OUT_REQUEST_DETAIL_ID
4801: AND exception_type = P_EXCEPTION_TYPE;
4802: fnd_message.set_token('ALLOWED_DRIVING_TIME', l_id2);
4803: fnd_message.set_token('DRIVING_TIME', l_id1);
4804:
4805: SELECT round(nvl(number7, 0)), round(nvl(number8, 0))
4806: INTO l_id1, l_id2
4807: FROM mst_out_request_details
4806: INTO l_id1, l_id2
4807: FROM mst_out_request_details
4808: WHERE out_request_detail_id = P_OUT_REQUEST_DETAIL_ID
4809: AND exception_type = P_EXCEPTION_TYPE;
4810: fnd_message.set_token('ALLOWED_DUTY_TIME', l_id2);
4811: fnd_message.set_token('DUTY_TIME', l_id1);
4812:
4813: SELECT round(nvl(number9, 0)), round(nvl(number10, 0))
4814: INTO l_id1, l_id2
4807: FROM mst_out_request_details
4808: WHERE out_request_detail_id = P_OUT_REQUEST_DETAIL_ID
4809: AND exception_type = P_EXCEPTION_TYPE;
4810: fnd_message.set_token('ALLOWED_DUTY_TIME', l_id2);
4811: fnd_message.set_token('DUTY_TIME', l_id1);
4812:
4813: SELECT round(nvl(number9, 0)), round(nvl(number10, 0))
4814: INTO l_id1, l_id2
4815: FROM mst_out_request_details
4814: INTO l_id1, l_id2
4815: FROM mst_out_request_details
4816: WHERE out_request_detail_id = P_OUT_REQUEST_DETAIL_ID
4817: AND exception_type = P_EXCEPTION_TYPE;
4818: fnd_message.set_token('MIN_LAYOVER_TIME', l_id2);
4819: fnd_message.set_token('LAYOVER_TIME', l_id1);
4820:
4821: SELECT round(nvl(number11, 0)), round(nvl(number12, 0))
4822: INTO l_id1, l_id2
4815: FROM mst_out_request_details
4816: WHERE out_request_detail_id = P_OUT_REQUEST_DETAIL_ID
4817: AND exception_type = P_EXCEPTION_TYPE;
4818: fnd_message.set_token('MIN_LAYOVER_TIME', l_id2);
4819: fnd_message.set_token('LAYOVER_TIME', l_id1);
4820:
4821: SELECT round(nvl(number11, 0)), round(nvl(number12, 0))
4822: INTO l_id1, l_id2
4823: FROM mst_out_request_details
4822: INTO l_id1, l_id2
4823: FROM mst_out_request_details
4824: WHERE out_request_detail_id = P_OUT_REQUEST_DETAIL_ID
4825: AND exception_type = P_EXCEPTION_TYPE;
4826: fnd_message.set_token('MAX_TIME', l_id2);
4827: fnd_message.set_token('TIME', l_id1);
4828: END IF;
4829: l_message := fnd_message.get;
4830: ELSIF P_EXCEPTION_TYPE = 208 THEN
4823: FROM mst_out_request_details
4824: WHERE out_request_detail_id = P_OUT_REQUEST_DETAIL_ID
4825: AND exception_type = P_EXCEPTION_TYPE;
4826: fnd_message.set_token('MAX_TIME', l_id2);
4827: fnd_message.set_token('TIME', l_id1);
4828: END IF;
4829: l_message := fnd_message.get;
4830: ELSIF P_EXCEPTION_TYPE = 208 THEN
4831: SELECT number1, number2, number3, number4
4825: AND exception_type = P_EXCEPTION_TYPE;
4826: fnd_message.set_token('MAX_TIME', l_id2);
4827: fnd_message.set_token('TIME', l_id1);
4828: END IF;
4829: l_message := fnd_message.get;
4830: ELSIF P_EXCEPTION_TYPE = 208 THEN
4831: SELECT number1, number2, number3, number4
4832: INTO l_id1, l_id2, l_id3, l_id4
4833: FROM mst_out_request_details
4832: INTO l_id1, l_id2, l_id3, l_id4
4833: FROM mst_out_request_details
4834: WHERE out_request_detail_id = P_OUT_REQUEST_DETAIL_ID
4835: AND exception_type = P_EXCEPTION_TYPE;
4836: fnd_message.set_name('MST', 'MST_EXCEP_TOKEN_208_1');
4837: IF l_id1 < 0 THEN
4838: fnd_message.set_token('TRIP_NUMBER', l_new_trip);
4839: ELSE
4840: fnd_message.set_token('TRIP_NUMBER', l_id1);
4834: WHERE out_request_detail_id = P_OUT_REQUEST_DETAIL_ID
4835: AND exception_type = P_EXCEPTION_TYPE;
4836: fnd_message.set_name('MST', 'MST_EXCEP_TOKEN_208_1');
4837: IF l_id1 < 0 THEN
4838: fnd_message.set_token('TRIP_NUMBER', l_new_trip);
4839: ELSE
4840: fnd_message.set_token('TRIP_NUMBER', l_id1);
4841: END IF;
4842: Open l_city_cur(l_id3);
4836: fnd_message.set_name('MST', 'MST_EXCEP_TOKEN_208_1');
4837: IF l_id1 < 0 THEN
4838: fnd_message.set_token('TRIP_NUMBER', l_new_trip);
4839: ELSE
4840: fnd_message.set_token('TRIP_NUMBER', l_id1);
4841: END IF;
4842: Open l_city_cur(l_id3);
4843: Fetch l_city_cur Into l_str1;
4844: If l_city_cur%NotFound Then
4844: If l_city_cur%NotFound Then
4845: l_str1 := '';
4846: End If;
4847: Close l_city_cur;
4848: fnd_message.set_token('ORIGIN_CITY', l_str1);
4849: Open l_city_cur(l_id4);
4850: Fetch l_city_cur Into l_str2;
4851: If l_city_cur%NotFound Then
4852: l_str2 := '';
4851: If l_city_cur%NotFound Then
4852: l_str2 := '';
4853: End If;
4854: Close l_city_cur;
4855: fnd_message.set_token('DESTINATION_CITY', l_str2);
4856:
4857: Open l_carrier_cur(l_id2);
4858: Fetch l_carrier_cur Into l_str3;
4859: If l_carrier_cur%NotFound Then
4859: If l_carrier_cur%NotFound Then
4860: l_str3 := '';
4861: End If;
4862: Close l_carrier_cur;
4863: fnd_message.set_token('FREIGHT_CODE', l_str3);
4864: l_message := fnd_message.get;
4865: ELSIF P_EXCEPTION_TYPE = 220 THEN --Item Mode Incompatibility
4866: IF P_LINE_NUM = 1 THEN
4867: fnd_message.set_name('MST', 'MST_EXCEP_TOKEN_220_1');
4860: l_str3 := '';
4861: End If;
4862: Close l_carrier_cur;
4863: fnd_message.set_token('FREIGHT_CODE', l_str3);
4864: l_message := fnd_message.get;
4865: ELSIF P_EXCEPTION_TYPE = 220 THEN --Item Mode Incompatibility
4866: IF P_LINE_NUM = 1 THEN
4867: fnd_message.set_name('MST', 'MST_EXCEP_TOKEN_220_1');
4868: SELECT number1, number2, char1
4863: fnd_message.set_token('FREIGHT_CODE', l_str3);
4864: l_message := fnd_message.get;
4865: ELSIF P_EXCEPTION_TYPE = 220 THEN --Item Mode Incompatibility
4866: IF P_LINE_NUM = 1 THEN
4867: fnd_message.set_name('MST', 'MST_EXCEP_TOKEN_220_1');
4868: SELECT number1, number2, char1
4869: INTO l_id1, l_id2, l_str1
4870: FROM mst_out_request_details
4871: WHERE out_request_detail_id = P_OUT_REQUEST_DETAIL_ID
4870: FROM mst_out_request_details
4871: WHERE out_request_detail_id = P_OUT_REQUEST_DETAIL_ID
4872: AND exception_type = P_EXCEPTION_TYPE;
4873: IF l_id1 < 0 THEN
4874: fnd_message.set_token('TRIP_NUMBER', l_new_trip);
4875: ELSE
4876: fnd_message.set_token('TRIP_NUMBER', l_id1);
4877: END IF;
4878: fnd_message.set_token('DELIVERY_DETAIL_ID', l_id2);
4872: AND exception_type = P_EXCEPTION_TYPE;
4873: IF l_id1 < 0 THEN
4874: fnd_message.set_token('TRIP_NUMBER', l_new_trip);
4875: ELSE
4876: fnd_message.set_token('TRIP_NUMBER', l_id1);
4877: END IF;
4878: fnd_message.set_token('DELIVERY_DETAIL_ID', l_id2);
4879:
4880: SELECT wlk.meaning
4874: fnd_message.set_token('TRIP_NUMBER', l_new_trip);
4875: ELSE
4876: fnd_message.set_token('TRIP_NUMBER', l_id1);
4877: END IF;
4878: fnd_message.set_token('DELIVERY_DETAIL_ID', l_id2);
4879:
4880: SELECT wlk.meaning
4881: INTO l_str2
4882: FROM wsh_lookups wlk
4881: INTO l_str2
4882: FROM wsh_lookups wlk
4883: WHERE wlk.lookup_type = 'WSH_MODE_OF_TRANSPORT'
4884: and wlk.lookup_code = l_str1;
4885: fnd_message.set_token('MODE_OF_TRANSPORT', l_str2);
4886:
4887: -- item_description
4888: select msitl.description into l_str3
4889: from mtl_system_items_tl msitl, mst_delivery_details mdd
4891: and mdd.delivery_detail_id = l_id2
4892: and mdd.inventory_item_id = msitl.inventory_item_id
4893: and mdd.organization_id = msitl.organization_id
4894: and msitl.language = userenv('LANG');
4895: fnd_message.set_token('ITEM_DESCRIPTION', l_str3);
4896: l_message := fnd_message.get;
4897: END IF;
4898: ELSIF P_EXCEPTION_TYPE = 221 THEN --Item Carrier Incompatibility
4899: IF P_LINE_NUM = 1 THEN
4892: and mdd.inventory_item_id = msitl.inventory_item_id
4893: and mdd.organization_id = msitl.organization_id
4894: and msitl.language = userenv('LANG');
4895: fnd_message.set_token('ITEM_DESCRIPTION', l_str3);
4896: l_message := fnd_message.get;
4897: END IF;
4898: ELSIF P_EXCEPTION_TYPE = 221 THEN --Item Carrier Incompatibility
4899: IF P_LINE_NUM = 1 THEN
4900: fnd_message.set_name('MST', 'MST_EXCEP_TOKEN_221_1');
4896: l_message := fnd_message.get;
4897: END IF;
4898: ELSIF P_EXCEPTION_TYPE = 221 THEN --Item Carrier Incompatibility
4899: IF P_LINE_NUM = 1 THEN
4900: fnd_message.set_name('MST', 'MST_EXCEP_TOKEN_221_1');
4901: --Trip_number
4902: SELECT number1, number2, number3
4903: INTO l_id1, l_id2, l_id3
4904: FROM mst_out_request_details
4904: FROM mst_out_request_details
4905: WHERE out_request_detail_id = P_OUT_REQUEST_DETAIL_ID
4906: AND exception_type = P_EXCEPTION_TYPE;
4907: IF l_id1 < 0 THEN
4908: fnd_message.set_token('TRIP_NUMBER', l_new_trip);
4909: ELSE
4910: fnd_message.set_token('TRIP_NUMBER', l_id1);
4911: END IF;
4912: fnd_message.set_token('DELIVERY_DETAIL_ID', l_id2);
4906: AND exception_type = P_EXCEPTION_TYPE;
4907: IF l_id1 < 0 THEN
4908: fnd_message.set_token('TRIP_NUMBER', l_new_trip);
4909: ELSE
4910: fnd_message.set_token('TRIP_NUMBER', l_id1);
4911: END IF;
4912: fnd_message.set_token('DELIVERY_DETAIL_ID', l_id2);
4913:
4914: -- item_description
4908: fnd_message.set_token('TRIP_NUMBER', l_new_trip);
4909: ELSE
4910: fnd_message.set_token('TRIP_NUMBER', l_id1);
4911: END IF;
4912: fnd_message.set_token('DELIVERY_DETAIL_ID', l_id2);
4913:
4914: -- item_description
4915: select msitl.description into l_str2
4916: from mtl_system_items_tl msitl, mst_delivery_details mdd
4918: and mdd.delivery_detail_id = l_id2
4919: and mdd.inventory_item_id = msitl.inventory_item_id
4920: and mdd.organization_id = msitl.organization_id
4921: and msitl.language = userenv('LANG');
4922: fnd_message.set_token('ITEM_DESCRIPTION', l_str2);
4923:
4924: --carrier name
4925: SELECT freight_code into l_str3
4926: FROM wsh_carriers
4924: --carrier name
4925: SELECT freight_code into l_str3
4926: FROM wsh_carriers
4927: WHERE carrier_id = l_id3;
4928: fnd_message.set_token('CARRIER_NAME', l_str3);
4929:
4930: l_message := fnd_message.get;
4931: END IF;
4932: ELSIF P_EXCEPTION_TYPE = 222 THEN --Ship Set Violation
4926: FROM wsh_carriers
4927: WHERE carrier_id = l_id3;
4928: fnd_message.set_token('CARRIER_NAME', l_str3);
4929:
4930: l_message := fnd_message.get;
4931: END IF;
4932: ELSIF P_EXCEPTION_TYPE = 222 THEN --Ship Set Violation
4933: IF P_LINE_NUM = 1 THEN
4934: fnd_message.set_name('MST', 'MST_EXCEP_TOKEN_222_1');
4930: l_message := fnd_message.get;
4931: END IF;
4932: ELSIF P_EXCEPTION_TYPE = 222 THEN --Ship Set Violation
4933: IF P_LINE_NUM = 1 THEN
4934: fnd_message.set_name('MST', 'MST_EXCEP_TOKEN_222_1');
4935: --Ship_set
4936: SELECT number1
4937: INTO l_id1
4938: FROM mst_out_request_details
4942: -- Ship_set Name
4943: select set_name into l_str1
4944: from oe_sets os
4945: where os.set_id=l_id1;
4946: fnd_message.set_token('SHIP_SET', l_str1);
4947: l_message := fnd_message.get;
4948: END IF;
4949: ELSIF P_EXCEPTION_TYPE = 223 THEN --Ship Set Violation
4950: IF P_LINE_NUM = 1 THEN
4943: select set_name into l_str1
4944: from oe_sets os
4945: where os.set_id=l_id1;
4946: fnd_message.set_token('SHIP_SET', l_str1);
4947: l_message := fnd_message.get;
4948: END IF;
4949: ELSIF P_EXCEPTION_TYPE = 223 THEN --Ship Set Violation
4950: IF P_LINE_NUM = 1 THEN
4951: fnd_message.set_name('MST', 'MST_EXCEP_TOKEN_223_1');
4947: l_message := fnd_message.get;
4948: END IF;
4949: ELSIF P_EXCEPTION_TYPE = 223 THEN --Ship Set Violation
4950: IF P_LINE_NUM = 1 THEN
4951: fnd_message.set_name('MST', 'MST_EXCEP_TOKEN_223_1');
4952: --Ship_set
4953: SELECT number1
4954: INTO l_id1
4955: FROM mst_out_request_details
4959: -- Ship_set Name
4960: select set_name into l_str1
4961: from oe_sets os
4962: where os.set_id=l_id1;
4963: fnd_message.set_token('ARRIVAL_SET', l_str1);
4964: l_message := fnd_message.get;
4965: END IF;
4966: ELSIF P_EXCEPTION_TYPE = 300 THEN
4967: SELECT number1, number2, number3, number4, nvl(number5, 0), nvl(number6, 0)
4960: select set_name into l_str1
4961: from oe_sets os
4962: where os.set_id=l_id1;
4963: fnd_message.set_token('ARRIVAL_SET', l_str1);
4964: l_message := fnd_message.get;
4965: END IF;
4966: ELSIF P_EXCEPTION_TYPE = 300 THEN
4967: SELECT number1, number2, number3, number4, nvl(number5, 0), nvl(number6, 0)
4968: INTO l_id1, l_id2, l_id3, l_id4, l_id5, l_id6
4968: INTO l_id1, l_id2, l_id3, l_id4, l_id5, l_id6
4969: FROM mst_out_request_details
4970: WHERE out_request_detail_id = P_OUT_REQUEST_DETAIL_ID
4971: AND exception_type = P_EXCEPTION_TYPE;
4972: fnd_message.set_name('MST', 'MST_EXCEP_TOKEN_300_1');
4973: Open l_carrier_cur(l_id1);
4974: Fetch l_carrier_cur Into l_str1;
4975: If l_carrier_cur%NotFound Then
4976: l_str1 := '';
4974: Fetch l_carrier_cur Into l_str1;
4975: If l_carrier_cur%NotFound Then
4976: l_str1 := '';
4977: End If;
4978: fnd_message.set_token('FREIGHT_CODE', l_str1);
4979: Close l_carrier_cur;
4980: Open l_vehicle_cur(l_id2);
4981: Fetch l_vehicle_cur Into l_str2;
4982: If l_vehicle_cur%NotFound Then
4982: If l_vehicle_cur%NotFound Then
4983: l_str2 := '';
4984: End If;
4985: Close l_vehicle_cur;
4986: fnd_message.set_token('VEHICLE_TYPE', l_str2);
4987:
4988: Open l_lanes_cur (l_id3);
4989: Fetch l_lanes_cur Into l_str3;
4990: If l_lanes_cur%NotFound Then
4990: If l_lanes_cur%NotFound Then
4991: l_str3 := '';
4992: End If;
4993: Close l_lanes_cur;
4994: fnd_message.set_token('LANE', l_str3);
4995: fnd_message.set_token('TOTAL_LOADS', l_id5);
4996: fnd_message.set_token('VEHICLE_AVAILABILITY', l_id6);
4997: l_message := fnd_message.get;
4998: ELSIF P_EXCEPTION_TYPE = 301 THEN -- Carrier commitment under-utilization
4991: l_str3 := '';
4992: End If;
4993: Close l_lanes_cur;
4994: fnd_message.set_token('LANE', l_str3);
4995: fnd_message.set_token('TOTAL_LOADS', l_id5);
4996: fnd_message.set_token('VEHICLE_AVAILABILITY', l_id6);
4997: l_message := fnd_message.get;
4998: ELSIF P_EXCEPTION_TYPE = 301 THEN -- Carrier commitment under-utilization
4999: fnd_message.set_name('MST', 'MST_EXCEP_TOKEN_301_1');
4992: End If;
4993: Close l_lanes_cur;
4994: fnd_message.set_token('LANE', l_str3);
4995: fnd_message.set_token('TOTAL_LOADS', l_id5);
4996: fnd_message.set_token('VEHICLE_AVAILABILITY', l_id6);
4997: l_message := fnd_message.get;
4998: ELSIF P_EXCEPTION_TYPE = 301 THEN -- Carrier commitment under-utilization
4999: fnd_message.set_name('MST', 'MST_EXCEP_TOKEN_301_1');
5000:
4993: Close l_lanes_cur;
4994: fnd_message.set_token('LANE', l_str3);
4995: fnd_message.set_token('TOTAL_LOADS', l_id5);
4996: fnd_message.set_token('VEHICLE_AVAILABILITY', l_id6);
4997: l_message := fnd_message.get;
4998: ELSIF P_EXCEPTION_TYPE = 301 THEN -- Carrier commitment under-utilization
4999: fnd_message.set_name('MST', 'MST_EXCEP_TOKEN_301_1');
5000:
5001: SELECT number1, number3, number4, number5, date1, date2
4995: fnd_message.set_token('TOTAL_LOADS', l_id5);
4996: fnd_message.set_token('VEHICLE_AVAILABILITY', l_id6);
4997: l_message := fnd_message.get;
4998: ELSIF P_EXCEPTION_TYPE = 301 THEN -- Carrier commitment under-utilization
4999: fnd_message.set_name('MST', 'MST_EXCEP_TOKEN_301_1');
5000:
5001: SELECT number1, number3, number4, number5, date1, date2
5002: INTO l_id1, l_id3, l_id4, l_id5, l_date1, l_date2
5003: FROM mst_out_request_details
5003: FROM mst_out_request_details
5004: WHERE out_request_detail_id = P_OUT_REQUEST_DETAIL_ID
5005: AND exception_type = P_EXCEPTION_TYPE;
5006:
5007: fnd_message.set_token('START_DATE', fnd_date.DATE_TO_CHARDATE(l_date1));
5008: fnd_message.set_token('END_DATE', fnd_date.DATE_TO_CHARDATE(l_date2));
5009: fnd_message.set_token('SHORTFALL', round(l_id5));
5010:
5011: --Carrier name
5004: WHERE out_request_detail_id = P_OUT_REQUEST_DETAIL_ID
5005: AND exception_type = P_EXCEPTION_TYPE;
5006:
5007: fnd_message.set_token('START_DATE', fnd_date.DATE_TO_CHARDATE(l_date1));
5008: fnd_message.set_token('END_DATE', fnd_date.DATE_TO_CHARDATE(l_date2));
5009: fnd_message.set_token('SHORTFALL', round(l_id5));
5010:
5011: --Carrier name
5012: SELECT freight_code INTO l_str1
5005: AND exception_type = P_EXCEPTION_TYPE;
5006:
5007: fnd_message.set_token('START_DATE', fnd_date.DATE_TO_CHARDATE(l_date1));
5008: fnd_message.set_token('END_DATE', fnd_date.DATE_TO_CHARDATE(l_date2));
5009: fnd_message.set_token('SHORTFALL', round(l_id5));
5010:
5011: --Carrier name
5012: SELECT freight_code INTO l_str1
5013: FROM wsh_carriers
5011: --Carrier name
5012: SELECT freight_code INTO l_str1
5013: FROM wsh_carriers
5014: WHERE carrier_id = l_id1;
5015: fnd_message.set_token('CARRIER', l_str1);
5016:
5017: --get Lane_Number
5018: select LANE
5019: into l_str2
5025: select flg.name "LANE"
5026: from fte_lane_groups flg
5027: where flg.lane_group_id = l_id4);
5028: --as per dld, Lane_Number = fl.lane_number or flg.name whichever is not null
5029: fnd_message.set_token('LANE_NAME', l_str2);
5030: l_message := fnd_message.get;
5031: ELSIF P_EXCEPTION_TYPE = 400 THEN
5032: SELECT number1, number2
5033: INTO l_id1, l_id2
5026: from fte_lane_groups flg
5027: where flg.lane_group_id = l_id4);
5028: --as per dld, Lane_Number = fl.lane_number or flg.name whichever is not null
5029: fnd_message.set_token('LANE_NAME', l_str2);
5030: l_message := fnd_message.get;
5031: ELSIF P_EXCEPTION_TYPE = 400 THEN
5032: SELECT number1, number2
5033: INTO l_id1, l_id2
5034: FROM mst_out_request_details
5033: INTO l_id1, l_id2
5034: FROM mst_out_request_details
5035: WHERE out_request_detail_id = P_OUT_REQUEST_DETAIL_ID
5036: AND exception_type = P_EXCEPTION_TYPE;
5037: fnd_message.set_name('MST', 'MST_EXCEP_TOKEN_400_1');
5038: IF l_id1 < 0 THEN
5039: fnd_message.set_token('TRIP_NUMBER', l_new_trip);
5040: ELSE
5041: fnd_message.set_token('TRIP_NUMBER', l_id1);
5035: WHERE out_request_detail_id = P_OUT_REQUEST_DETAIL_ID
5036: AND exception_type = P_EXCEPTION_TYPE;
5037: fnd_message.set_name('MST', 'MST_EXCEP_TOKEN_400_1');
5038: IF l_id1 < 0 THEN
5039: fnd_message.set_token('TRIP_NUMBER', l_new_trip);
5040: ELSE
5041: fnd_message.set_token('TRIP_NUMBER', l_id1);
5042: END IF;
5043:
5037: fnd_message.set_name('MST', 'MST_EXCEP_TOKEN_400_1');
5038: IF l_id1 < 0 THEN
5039: fnd_message.set_token('TRIP_NUMBER', l_new_trip);
5040: ELSE
5041: fnd_message.set_token('TRIP_NUMBER', l_id1);
5042: END IF;
5043:
5044: Open l_fac_desc_cur (l_id2);
5045: Fetch l_fac_desc_cur Into l_str1;
5046: If l_fac_desc_cur%NotFound Then
5047: l_str1 := '';
5048: End If;
5049: Close l_fac_desc_cur;
5050: fnd_message.set_token('FACILITY_DESC', l_str1);
5051: l_message := fnd_message.get;
5052: ELSIF P_EXCEPTION_TYPE = 401 THEN
5053: SELECT number1, number2, round(nvl(number3, 0), 2), round(nvl(number4, 0), 2)
5054: INTO l_id1, l_id2, l_id3, l_id4
5047: l_str1 := '';
5048: End If;
5049: Close l_fac_desc_cur;
5050: fnd_message.set_token('FACILITY_DESC', l_str1);
5051: l_message := fnd_message.get;
5052: ELSIF P_EXCEPTION_TYPE = 401 THEN
5053: SELECT number1, number2, round(nvl(number3, 0), 2), round(nvl(number4, 0), 2)
5054: INTO l_id1, l_id2, l_id3, l_id4
5055: FROM mst_out_request_details
5054: INTO l_id1, l_id2, l_id3, l_id4
5055: FROM mst_out_request_details
5056: WHERE out_request_detail_id = P_OUT_REQUEST_DETAIL_ID
5057: AND exception_type = P_EXCEPTION_TYPE;
5058: fnd_message.set_name('MST', 'MST_EXCEP_TOKEN_401_1');
5059: IF l_id1 < 0 THEN
5060: fnd_message.set_token('TRIP_NUMBER', l_new_trip);
5061: ELSE
5062: fnd_message.set_token('TRIP_NUMBER', l_id1);
5056: WHERE out_request_detail_id = P_OUT_REQUEST_DETAIL_ID
5057: AND exception_type = P_EXCEPTION_TYPE;
5058: fnd_message.set_name('MST', 'MST_EXCEP_TOKEN_401_1');
5059: IF l_id1 < 0 THEN
5060: fnd_message.set_token('TRIP_NUMBER', l_new_trip);
5061: ELSE
5062: fnd_message.set_token('TRIP_NUMBER', l_id1);
5063: END IF;
5064:
5058: fnd_message.set_name('MST', 'MST_EXCEP_TOKEN_401_1');
5059: IF l_id1 < 0 THEN
5060: fnd_message.set_token('TRIP_NUMBER', l_new_trip);
5061: ELSE
5062: fnd_message.set_token('TRIP_NUMBER', l_id1);
5063: END IF;
5064:
5065: Open l_fac_desc_cur (l_id2);
5066: Fetch l_fac_desc_cur Into l_str1;
5067: If l_fac_desc_cur%NotFound Then
5068: l_str1 := '';
5069: End If;
5070: Close l_fac_desc_cur;
5071: fnd_message.set_token('FACILITY_DESC', l_str1);
5072: fnd_message.set_token('REQUIRED_STOP_TIME', ltrim(to_char(l_id4, '999990.0')));
5073: fnd_message.set_token('STOP_TIME', ltrim(to_char(l_id3, '999990.0')));
5074: l_message := fnd_message.get;
5075: ELSIF P_EXCEPTION_TYPE = 402 THEN
5068: l_str1 := '';
5069: End If;
5070: Close l_fac_desc_cur;
5071: fnd_message.set_token('FACILITY_DESC', l_str1);
5072: fnd_message.set_token('REQUIRED_STOP_TIME', ltrim(to_char(l_id4, '999990.0')));
5073: fnd_message.set_token('STOP_TIME', ltrim(to_char(l_id3, '999990.0')));
5074: l_message := fnd_message.get;
5075: ELSIF P_EXCEPTION_TYPE = 402 THEN
5076: SELECT number1, number2, number3, number4, round(nvl(number5, 0), 2), round(nvl(number6, 0), 2)
5069: End If;
5070: Close l_fac_desc_cur;
5071: fnd_message.set_token('FACILITY_DESC', l_str1);
5072: fnd_message.set_token('REQUIRED_STOP_TIME', ltrim(to_char(l_id4, '999990.0')));
5073: fnd_message.set_token('STOP_TIME', ltrim(to_char(l_id3, '999990.0')));
5074: l_message := fnd_message.get;
5075: ELSIF P_EXCEPTION_TYPE = 402 THEN
5076: SELECT number1, number2, number3, number4, round(nvl(number5, 0), 2), round(nvl(number6, 0), 2)
5077: INTO l_id1, l_id2, l_id3, l_id4, l_id5, l_id6
5070: Close l_fac_desc_cur;
5071: fnd_message.set_token('FACILITY_DESC', l_str1);
5072: fnd_message.set_token('REQUIRED_STOP_TIME', ltrim(to_char(l_id4, '999990.0')));
5073: fnd_message.set_token('STOP_TIME', ltrim(to_char(l_id3, '999990.0')));
5074: l_message := fnd_message.get;
5075: ELSIF P_EXCEPTION_TYPE = 402 THEN
5076: SELECT number1, number2, number3, number4, round(nvl(number5, 0), 2), round(nvl(number6, 0), 2)
5077: INTO l_id1, l_id2, l_id3, l_id4, l_id5, l_id6
5078: FROM mst_out_request_details
5077: INTO l_id1, l_id2, l_id3, l_id4, l_id5, l_id6
5078: FROM mst_out_request_details
5079: WHERE out_request_detail_id = P_OUT_REQUEST_DETAIL_ID
5080: AND exception_type = P_EXCEPTION_TYPE;
5081: fnd_message.set_name('MST', 'MST_EXCEP_TOKEN_402_1');
5082: fnd_message.set_token('DELIVERY_DETAIL_ID', l_id1);
5083: IF l_id2 < 0 THEN
5084: fnd_message.set_token('TRIP_NUMBER1', l_new_trip);
5085: ELSE
5078: FROM mst_out_request_details
5079: WHERE out_request_detail_id = P_OUT_REQUEST_DETAIL_ID
5080: AND exception_type = P_EXCEPTION_TYPE;
5081: fnd_message.set_name('MST', 'MST_EXCEP_TOKEN_402_1');
5082: fnd_message.set_token('DELIVERY_DETAIL_ID', l_id1);
5083: IF l_id2 < 0 THEN
5084: fnd_message.set_token('TRIP_NUMBER1', l_new_trip);
5085: ELSE
5086: fnd_message.set_token('TRIP_NUMBER1', l_id2);
5080: AND exception_type = P_EXCEPTION_TYPE;
5081: fnd_message.set_name('MST', 'MST_EXCEP_TOKEN_402_1');
5082: fnd_message.set_token('DELIVERY_DETAIL_ID', l_id1);
5083: IF l_id2 < 0 THEN
5084: fnd_message.set_token('TRIP_NUMBER1', l_new_trip);
5085: ELSE
5086: fnd_message.set_token('TRIP_NUMBER1', l_id2);
5087: END IF;
5088: IF l_id3 < 0 THEN
5082: fnd_message.set_token('DELIVERY_DETAIL_ID', l_id1);
5083: IF l_id2 < 0 THEN
5084: fnd_message.set_token('TRIP_NUMBER1', l_new_trip);
5085: ELSE
5086: fnd_message.set_token('TRIP_NUMBER1', l_id2);
5087: END IF;
5088: IF l_id3 < 0 THEN
5089: fnd_message.set_token('TRIP_NUMBER2', l_new_trip);
5090: ELSE
5085: ELSE
5086: fnd_message.set_token('TRIP_NUMBER1', l_id2);
5087: END IF;
5088: IF l_id3 < 0 THEN
5089: fnd_message.set_token('TRIP_NUMBER2', l_new_trip);
5090: ELSE
5091: fnd_message.set_token('TRIP_NUMBER2', l_id3);
5092: END IF;
5093:
5087: END IF;
5088: IF l_id3 < 0 THEN
5089: fnd_message.set_token('TRIP_NUMBER2', l_new_trip);
5090: ELSE
5091: fnd_message.set_token('TRIP_NUMBER2', l_id3);
5092: END IF;
5093:
5094: Open l_fac_desc_cur (l_id4);
5095: Fetch l_fac_desc_cur Into l_str1;
5096: If l_fac_desc_cur%NotFound Then
5097: l_str1 := '';
5098: End If;
5099: Close l_fac_desc_cur;
5100: fnd_message.set_token('FACILITY_DESC', l_str1);
5101: fnd_message.set_token('REQUIRED_CONNECT_TIME', ltrim(to_char(l_id6, '999990.0')));
5102: fnd_message.set_token('CONNECT_TIME', ltrim(to_char(l_id5, '999990.0')));
5103: l_message := fnd_message.get;
5104: ELSIF P_EXCEPTION_TYPE = 403 THEN
5097: l_str1 := '';
5098: End If;
5099: Close l_fac_desc_cur;
5100: fnd_message.set_token('FACILITY_DESC', l_str1);
5101: fnd_message.set_token('REQUIRED_CONNECT_TIME', ltrim(to_char(l_id6, '999990.0')));
5102: fnd_message.set_token('CONNECT_TIME', ltrim(to_char(l_id5, '999990.0')));
5103: l_message := fnd_message.get;
5104: ELSIF P_EXCEPTION_TYPE = 403 THEN
5105: SELECT number1, number2, number3
5098: End If;
5099: Close l_fac_desc_cur;
5100: fnd_message.set_token('FACILITY_DESC', l_str1);
5101: fnd_message.set_token('REQUIRED_CONNECT_TIME', ltrim(to_char(l_id6, '999990.0')));
5102: fnd_message.set_token('CONNECT_TIME', ltrim(to_char(l_id5, '999990.0')));
5103: l_message := fnd_message.get;
5104: ELSIF P_EXCEPTION_TYPE = 403 THEN
5105: SELECT number1, number2, number3
5106: INTO l_id1, l_id2, l_id3
5099: Close l_fac_desc_cur;
5100: fnd_message.set_token('FACILITY_DESC', l_str1);
5101: fnd_message.set_token('REQUIRED_CONNECT_TIME', ltrim(to_char(l_id6, '999990.0')));
5102: fnd_message.set_token('CONNECT_TIME', ltrim(to_char(l_id5, '999990.0')));
5103: l_message := fnd_message.get;
5104: ELSIF P_EXCEPTION_TYPE = 403 THEN
5105: SELECT number1, number2, number3
5106: INTO l_id1, l_id2, l_id3
5107: FROM mst_out_request_details
5106: INTO l_id1, l_id2, l_id3
5107: FROM mst_out_request_details
5108: WHERE out_request_detail_id = P_OUT_REQUEST_DETAIL_ID
5109: AND exception_type = P_EXCEPTION_TYPE;
5110: fnd_message.set_name('MST', 'MST_EXCEP_TOKEN_403_1');
5111: fnd_message.set_token('CM_TRIP_NUMBER', l_id1);
5112: IF l_id2 < 0 THEN
5113: fnd_message.set_token('TRIP_NUMBER1', l_new_trip);
5114: ELSE
5107: FROM mst_out_request_details
5108: WHERE out_request_detail_id = P_OUT_REQUEST_DETAIL_ID
5109: AND exception_type = P_EXCEPTION_TYPE;
5110: fnd_message.set_name('MST', 'MST_EXCEP_TOKEN_403_1');
5111: fnd_message.set_token('CM_TRIP_NUMBER', l_id1);
5112: IF l_id2 < 0 THEN
5113: fnd_message.set_token('TRIP_NUMBER1', l_new_trip);
5114: ELSE
5115: fnd_message.set_token('TRIP_NUMBER1', l_id2);
5109: AND exception_type = P_EXCEPTION_TYPE;
5110: fnd_message.set_name('MST', 'MST_EXCEP_TOKEN_403_1');
5111: fnd_message.set_token('CM_TRIP_NUMBER', l_id1);
5112: IF l_id2 < 0 THEN
5113: fnd_message.set_token('TRIP_NUMBER1', l_new_trip);
5114: ELSE
5115: fnd_message.set_token('TRIP_NUMBER1', l_id2);
5116: END IF;
5117: IF l_id3 < 0 THEN
5111: fnd_message.set_token('CM_TRIP_NUMBER', l_id1);
5112: IF l_id2 < 0 THEN
5113: fnd_message.set_token('TRIP_NUMBER1', l_new_trip);
5114: ELSE
5115: fnd_message.set_token('TRIP_NUMBER1', l_id2);
5116: END IF;
5117: IF l_id3 < 0 THEN
5118: fnd_message.set_token('TRIP_NUMBER2', l_new_trip);
5119: ELSE
5114: ELSE
5115: fnd_message.set_token('TRIP_NUMBER1', l_id2);
5116: END IF;
5117: IF l_id3 < 0 THEN
5118: fnd_message.set_token('TRIP_NUMBER2', l_new_trip);
5119: ELSE
5120: fnd_message.set_token('TRIP_NUMBER2', l_id3);
5121: END IF;
5122: l_message := fnd_message.get;
5116: END IF;
5117: IF l_id3 < 0 THEN
5118: fnd_message.set_token('TRIP_NUMBER2', l_new_trip);
5119: ELSE
5120: fnd_message.set_token('TRIP_NUMBER2', l_id3);
5121: END IF;
5122: l_message := fnd_message.get;
5123: ELSIF P_EXCEPTION_TYPE = 406 THEN --Carrier Facility Appointment violation
5124: IF P_LINE_NUM = 1 THEN
5118: fnd_message.set_token('TRIP_NUMBER2', l_new_trip);
5119: ELSE
5120: fnd_message.set_token('TRIP_NUMBER2', l_id3);
5121: END IF;
5122: l_message := fnd_message.get;
5123: ELSIF P_EXCEPTION_TYPE = 406 THEN --Carrier Facility Appointment violation
5124: IF P_LINE_NUM = 1 THEN
5125: fnd_message.set_name('MST', 'MST_EXCEP_TOKEN_406_1');
5126: SELECT number1, number2, number3, number4
5121: END IF;
5122: l_message := fnd_message.get;
5123: ELSIF P_EXCEPTION_TYPE = 406 THEN --Carrier Facility Appointment violation
5124: IF P_LINE_NUM = 1 THEN
5125: fnd_message.set_name('MST', 'MST_EXCEP_TOKEN_406_1');
5126: SELECT number1, number2, number3, number4
5127: INTO l_id1, l_id2, l_id3, l_id4
5128: FROM mst_out_request_details
5129: WHERE out_request_detail_id = P_OUT_REQUEST_DETAIL_ID
5128: FROM mst_out_request_details
5129: WHERE out_request_detail_id = P_OUT_REQUEST_DETAIL_ID
5130: AND exception_type = P_EXCEPTION_TYPE;
5131: IF l_id1 < 0 THEN
5132: fnd_message.set_token('TRIP_NUMBER', l_new_trip);
5133: ELSE
5134: fnd_message.set_token('TRIP_NUMBER', l_id1);
5135: END IF;
5136:
5130: AND exception_type = P_EXCEPTION_TYPE;
5131: IF l_id1 < 0 THEN
5132: fnd_message.set_token('TRIP_NUMBER', l_new_trip);
5133: ELSE
5134: fnd_message.set_token('TRIP_NUMBER', l_id1);
5135: END IF;
5136:
5137: --facility_description
5138: select flp.facility_code
5139: into l_str1
5140: from fte_location_parameters flp
5141: where flp.location_id = l_id2;
5142:
5143: fnd_message.set_token('FACILITY_DESCRIPTION', l_str1);
5144:
5145: --carrier name
5146: SELECT freight_code INTO l_str2
5147: FROM wsh_carriers
5145: --carrier name
5146: SELECT freight_code INTO l_str2
5147: FROM wsh_carriers
5148: WHERE carrier_id = l_id4;
5149: fnd_message.set_token('CARRIER_NAME', l_str2);
5150:
5151: l_message := fnd_message.get;
5152: END IF;
5153: ELSIF P_EXCEPTION_TYPE = 500 THEN
5147: FROM wsh_carriers
5148: WHERE carrier_id = l_id4;
5149: fnd_message.set_token('CARRIER_NAME', l_str2);
5150:
5151: l_message := fnd_message.get;
5152: END IF;
5153: ELSIF P_EXCEPTION_TYPE = 500 THEN
5154: SELECT number1, number2, number3, number4, round(nvl(number5, 0)), number6, round(nvl(number7, 0))
5155: INTO l_id1, l_id2, l_id3, l_id4, l_id5, l_id6, l_id7
5155: INTO l_id1, l_id2, l_id3, l_id4, l_id5, l_id6, l_id7
5156: FROM mst_out_request_details
5157: WHERE out_request_detail_id = P_OUT_REQUEST_DETAIL_ID
5158: AND exception_type = P_EXCEPTION_TYPE;
5159: fnd_message.set_name('MST', 'MST_EXCEP_TOKEN_500_1');
5160: IF l_id1 < 0 THEN
5161: fnd_message.set_token('TRIP_NUMBER', l_new_trip);
5162: ELSE
5163: fnd_message.set_token('TRIP_NUMBER', l_id1);
5157: WHERE out_request_detail_id = P_OUT_REQUEST_DETAIL_ID
5158: AND exception_type = P_EXCEPTION_TYPE;
5159: fnd_message.set_name('MST', 'MST_EXCEP_TOKEN_500_1');
5160: IF l_id1 < 0 THEN
5161: fnd_message.set_token('TRIP_NUMBER', l_new_trip);
5162: ELSE
5163: fnd_message.set_token('TRIP_NUMBER', l_id1);
5164: END IF;
5165: fnd_message.set_token('LOW_COST_CARRIER_COST', l_id7);
5159: fnd_message.set_name('MST', 'MST_EXCEP_TOKEN_500_1');
5160: IF l_id1 < 0 THEN
5161: fnd_message.set_token('TRIP_NUMBER', l_new_trip);
5162: ELSE
5163: fnd_message.set_token('TRIP_NUMBER', l_id1);
5164: END IF;
5165: fnd_message.set_token('LOW_COST_CARRIER_COST', l_id7);
5166:
5167: Open l_city_cur(l_id2);
5161: fnd_message.set_token('TRIP_NUMBER', l_new_trip);
5162: ELSE
5163: fnd_message.set_token('TRIP_NUMBER', l_id1);
5164: END IF;
5165: fnd_message.set_token('LOW_COST_CARRIER_COST', l_id7);
5166:
5167: Open l_city_cur(l_id2);
5168: Fetch l_city_cur Into l_str1;
5169: If l_city_cur%NotFound Then
5169: If l_city_cur%NotFound Then
5170: l_str1 := '';
5171: End If;
5172: Close l_city_cur;
5173: fnd_message.set_token('ORIGIN_CITY', l_str1);
5174:
5175: Open l_city_cur(l_id3);
5176: Fetch l_city_cur Into l_str2;
5177: If l_city_cur%NotFound Then
5177: If l_city_cur%NotFound Then
5178: l_str2 := '';
5179: End If;
5180: Close l_city_cur;
5181: fnd_message.set_token('DESTINATION_CITY', l_str2);
5182:
5183: Open l_carrier_cur(l_id4);
5184: Fetch l_carrier_cur Into l_str1;
5185: If l_carrier_cur%NotFound Then
5185: If l_carrier_cur%NotFound Then
5186: l_str1 := '';
5187: End If;
5188: Close l_carrier_cur;
5189: fnd_message.set_token('FREIGHT_CODE', l_str1);
5190:
5191: Open l_carrier_cur(l_id6);
5192: Fetch l_carrier_cur Into l_str2;
5193: If l_carrier_cur%NotFound Then
5193: If l_carrier_cur%NotFound Then
5194: l_str2 := '';
5195: End If;
5196: Close l_carrier_cur;
5197: fnd_message.set_token('LOW_COST_CARRIER', l_str2);
5198: fnd_message.set_token('COST', l_id5);
5199: l_message := fnd_message.get;
5200: ELSIF P_EXCEPTION_TYPE = 501 THEN
5201: SELECT number1
5194: l_str2 := '';
5195: End If;
5196: Close l_carrier_cur;
5197: fnd_message.set_token('LOW_COST_CARRIER', l_str2);
5198: fnd_message.set_token('COST', l_id5);
5199: l_message := fnd_message.get;
5200: ELSIF P_EXCEPTION_TYPE = 501 THEN
5201: SELECT number1
5202: INTO l_id1
5195: End If;
5196: Close l_carrier_cur;
5197: fnd_message.set_token('LOW_COST_CARRIER', l_str2);
5198: fnd_message.set_token('COST', l_id5);
5199: l_message := fnd_message.get;
5200: ELSIF P_EXCEPTION_TYPE = 501 THEN
5201: SELECT number1
5202: INTO l_id1
5203: FROM mst_out_request_details
5202: INTO l_id1
5203: FROM mst_out_request_details
5204: WHERE out_request_detail_id = P_OUT_REQUEST_DETAIL_ID
5205: AND exception_type = P_EXCEPTION_TYPE;
5206: fnd_message.set_name('MST', 'MST_EXCEP_TOKEN_501_1');
5207: fnd_message.set_token('TRIP_NUMBER', l_id1);
5208: l_message := fnd_message.get;
5209: ELSIF P_EXCEPTION_TYPE = 600 THEN
5210: SELECT number1, round(number2), round(number3), round(number4), round(number5), round(number6), round(number7), number8
5203: FROM mst_out_request_details
5204: WHERE out_request_detail_id = P_OUT_REQUEST_DETAIL_ID
5205: AND exception_type = P_EXCEPTION_TYPE;
5206: fnd_message.set_name('MST', 'MST_EXCEP_TOKEN_501_1');
5207: fnd_message.set_token('TRIP_NUMBER', l_id1);
5208: l_message := fnd_message.get;
5209: ELSIF P_EXCEPTION_TYPE = 600 THEN
5210: SELECT number1, round(number2), round(number3), round(number4), round(number5), round(number6), round(number7), number8
5211: INTO l_id1, l_id2, l_id3, l_id4, l_id5, l_id6, l_id7, l_id8
5204: WHERE out_request_detail_id = P_OUT_REQUEST_DETAIL_ID
5205: AND exception_type = P_EXCEPTION_TYPE;
5206: fnd_message.set_name('MST', 'MST_EXCEP_TOKEN_501_1');
5207: fnd_message.set_token('TRIP_NUMBER', l_id1);
5208: l_message := fnd_message.get;
5209: ELSIF P_EXCEPTION_TYPE = 600 THEN
5210: SELECT number1, round(number2), round(number3), round(number4), round(number5), round(number6), round(number7), number8
5211: INTO l_id1, l_id2, l_id3, l_id4, l_id5, l_id6, l_id7, l_id8
5212: FROM mst_out_request_details
5211: INTO l_id1, l_id2, l_id3, l_id4, l_id5, l_id6, l_id7, l_id8
5212: FROM mst_out_request_details
5213: WHERE out_request_detail_id = P_OUT_REQUEST_DETAIL_ID
5214: AND exception_type = P_EXCEPTION_TYPE;
5215: fnd_message.set_name('MST', 'MST_EXCEP_TOKEN_' || P_EXCEPTION_TYPE || '_1');
5216: IF l_id1 < 0 THEN
5217: fnd_message.set_token('TRIP_NUMBER', l_new_trip);
5218: ELSE
5219: fnd_message.set_token('TRIP_NUMBER', l_id1);
5213: WHERE out_request_detail_id = P_OUT_REQUEST_DETAIL_ID
5214: AND exception_type = P_EXCEPTION_TYPE;
5215: fnd_message.set_name('MST', 'MST_EXCEP_TOKEN_' || P_EXCEPTION_TYPE || '_1');
5216: IF l_id1 < 0 THEN
5217: fnd_message.set_token('TRIP_NUMBER', l_new_trip);
5218: ELSE
5219: fnd_message.set_token('TRIP_NUMBER', l_id1);
5220: END IF;
5221:
5215: fnd_message.set_name('MST', 'MST_EXCEP_TOKEN_' || P_EXCEPTION_TYPE || '_1');
5216: IF l_id1 < 0 THEN
5217: fnd_message.set_token('TRIP_NUMBER', l_new_trip);
5218: ELSE
5219: fnd_message.set_token('TRIP_NUMBER', l_id1);
5220: END IF;
5221:
5222: SELECT volume_uom
5223: INTO l_str1
5235: AND msikfv.organization_id = fvt.organization_id
5236: AND fvt.vehicle_type_id = l_id8;
5237:
5238: If l_id9 is not null Then
5239: fnd_message.set_token('PHYSICAL_CAPACITY', l_id9);
5240: Else
5241: fnd_message.set_token('PHYSICAL_CAPACITY', '');
5242: End If;
5243:
5237:
5238: If l_id9 is not null Then
5239: fnd_message.set_token('PHYSICAL_CAPACITY', l_id9);
5240: Else
5241: fnd_message.set_token('PHYSICAL_CAPACITY', '');
5242: End If;
5243:
5244: If l_id3 > 0 Then
5245: l_id8 := round((l_id2/l_id3) * 100);
5246: l_str1 := l_id8;
5247: Else
5248: l_str1 := ' ';
5249: End If;
5250: fnd_message.set_token('PEAK_VOLUME_UTILIZATION', l_str1);
5251: fnd_message.set_token('PEAK_VOLUME', l_id2);
5252: fnd_message.set_token('MAX_VOLUME', l_id3);
5253:
5254: If l_id5 > 0 Then
5247: Else
5248: l_str1 := ' ';
5249: End If;
5250: fnd_message.set_token('PEAK_VOLUME_UTILIZATION', l_str1);
5251: fnd_message.set_token('PEAK_VOLUME', l_id2);
5252: fnd_message.set_token('MAX_VOLUME', l_id3);
5253:
5254: If l_id5 > 0 Then
5255: l_id8 := round((l_id4/l_id5) * 100);
5248: l_str1 := ' ';
5249: End If;
5250: fnd_message.set_token('PEAK_VOLUME_UTILIZATION', l_str1);
5251: fnd_message.set_token('PEAK_VOLUME', l_id2);
5252: fnd_message.set_token('MAX_VOLUME', l_id3);
5253:
5254: If l_id5 > 0 Then
5255: l_id8 := round((l_id4/l_id5) * 100);
5256: l_str1 := l_id8;
5256: l_str1 := l_id8;
5257: Else
5258: l_str1 := ' ';
5259: End If;
5260: fnd_message.set_token('PEAK_WEIGHT_UTILIZATION', l_str1);
5261: fnd_message.set_token('PEAK_WEIGHT', l_id4);
5262: fnd_message.set_token('MAX_WEIGHT', l_id5);
5263:
5264: If l_id7 > 0 Then
5257: Else
5258: l_str1 := ' ';
5259: End If;
5260: fnd_message.set_token('PEAK_WEIGHT_UTILIZATION', l_str1);
5261: fnd_message.set_token('PEAK_WEIGHT', l_id4);
5262: fnd_message.set_token('MAX_WEIGHT', l_id5);
5263:
5264: If l_id7 > 0 Then
5265: l_id8 := round((l_id6/l_id7) * 100);
5258: l_str1 := ' ';
5259: End If;
5260: fnd_message.set_token('PEAK_WEIGHT_UTILIZATION', l_str1);
5261: fnd_message.set_token('PEAK_WEIGHT', l_id4);
5262: fnd_message.set_token('MAX_WEIGHT', l_id5);
5263:
5264: If l_id7 > 0 Then
5265: l_id8 := round((l_id6/l_id7) * 100);
5266: l_str1 := l_id8;
5266: l_str1 := l_id8;
5267: Else
5268: l_str1 := ' ';
5269: End If;
5270: fnd_message.set_token('PEAK_PALLET_UTILIZATION', l_str1);
5271: fnd_message.set_token('PEAK_PALLETS', l_id6);
5272: fnd_message.set_token('MAX_PALLETS', l_id7);
5273:
5274: Open l_uom_cur;
5267: Else
5268: l_str1 := ' ';
5269: End If;
5270: fnd_message.set_token('PEAK_PALLET_UTILIZATION', l_str1);
5271: fnd_message.set_token('PEAK_PALLETS', l_id6);
5272: fnd_message.set_token('MAX_PALLETS', l_id7);
5273:
5274: Open l_uom_cur;
5275: Fetch l_uom_cur Into l_uom;
5268: l_str1 := ' ';
5269: End If;
5270: fnd_message.set_token('PEAK_PALLET_UTILIZATION', l_str1);
5271: fnd_message.set_token('PEAK_PALLETS', l_id6);
5272: fnd_message.set_token('MAX_PALLETS', l_id7);
5273:
5274: Open l_uom_cur;
5275: Fetch l_uom_cur Into l_uom;
5276: If l_uom_cur%Found Then
5280: l_str1 := '';
5281: l_str2 := '';
5282: End If;
5283: Close l_uom_cur;
5284: fnd_message.set_token('WEIGHT_UOM', l_str1);
5285: fnd_message.set_token('VOLUME_UOM', l_str2);
5286: l_message := fnd_message.get;
5287: ELSIF P_EXCEPTION_TYPE = 601 THEN
5288: SELECT number1, round(number2), round(number3), round(number4), round(number5), round(number6), round(number7)
5281: l_str2 := '';
5282: End If;
5283: Close l_uom_cur;
5284: fnd_message.set_token('WEIGHT_UOM', l_str1);
5285: fnd_message.set_token('VOLUME_UOM', l_str2);
5286: l_message := fnd_message.get;
5287: ELSIF P_EXCEPTION_TYPE = 601 THEN
5288: SELECT number1, round(number2), round(number3), round(number4), round(number5), round(number6), round(number7)
5289: INTO l_id1, l_id2, l_id3, l_id4, l_id5, l_id6, l_id7
5282: End If;
5283: Close l_uom_cur;
5284: fnd_message.set_token('WEIGHT_UOM', l_str1);
5285: fnd_message.set_token('VOLUME_UOM', l_str2);
5286: l_message := fnd_message.get;
5287: ELSIF P_EXCEPTION_TYPE = 601 THEN
5288: SELECT number1, round(number2), round(number3), round(number4), round(number5), round(number6), round(number7)
5289: INTO l_id1, l_id2, l_id3, l_id4, l_id5, l_id6, l_id7
5290: FROM mst_out_request_details
5289: INTO l_id1, l_id2, l_id3, l_id4, l_id5, l_id6, l_id7
5290: FROM mst_out_request_details
5291: WHERE out_request_detail_id = P_OUT_REQUEST_DETAIL_ID
5292: AND exception_type = P_EXCEPTION_TYPE;
5293: fnd_message.set_name('MST', 'MST_EXCEP_TOKEN_' || P_EXCEPTION_TYPE || '_1');
5294: IF l_id1 < 0 THEN
5295: fnd_message.set_token('TRIP_NUMBER', l_new_trip);
5296: ELSE
5297: fnd_message.set_token('TRIP_NUMBER', l_id1);
5291: WHERE out_request_detail_id = P_OUT_REQUEST_DETAIL_ID
5292: AND exception_type = P_EXCEPTION_TYPE;
5293: fnd_message.set_name('MST', 'MST_EXCEP_TOKEN_' || P_EXCEPTION_TYPE || '_1');
5294: IF l_id1 < 0 THEN
5295: fnd_message.set_token('TRIP_NUMBER', l_new_trip);
5296: ELSE
5297: fnd_message.set_token('TRIP_NUMBER', l_id1);
5298: END IF;
5299:
5293: fnd_message.set_name('MST', 'MST_EXCEP_TOKEN_' || P_EXCEPTION_TYPE || '_1');
5294: IF l_id1 < 0 THEN
5295: fnd_message.set_token('TRIP_NUMBER', l_new_trip);
5296: ELSE
5297: fnd_message.set_token('TRIP_NUMBER', l_id1);
5298: END IF;
5299:
5300: If l_id3 > 0 Then
5301: l_id8 := round((l_id2/l_id3) * 100);
5302: l_str1 := l_id8;
5303: Else
5304: l_str1 := ' ';
5305: End If;
5306: fnd_message.set_token('PEAK_VOLUME_UTILIZATION', l_str1);
5307: fnd_message.set_token('PEAK_VOLUME', l_id2);
5308: fnd_message.set_token('MAX_VOLUME', l_id3);
5309:
5310: If l_id5 > 0 Then
5303: Else
5304: l_str1 := ' ';
5305: End If;
5306: fnd_message.set_token('PEAK_VOLUME_UTILIZATION', l_str1);
5307: fnd_message.set_token('PEAK_VOLUME', l_id2);
5308: fnd_message.set_token('MAX_VOLUME', l_id3);
5309:
5310: If l_id5 > 0 Then
5311: l_id8 := round((l_id4/l_id5) * 100);
5304: l_str1 := ' ';
5305: End If;
5306: fnd_message.set_token('PEAK_VOLUME_UTILIZATION', l_str1);
5307: fnd_message.set_token('PEAK_VOLUME', l_id2);
5308: fnd_message.set_token('MAX_VOLUME', l_id3);
5309:
5310: If l_id5 > 0 Then
5311: l_id8 := round((l_id4/l_id5) * 100);
5312: l_str1 := l_id8;
5312: l_str1 := l_id8;
5313: Else
5314: l_str1 := ' ';
5315: End If;
5316: fnd_message.set_token('PEAK_WEIGHT_UTILIZATION', l_str1);
5317: fnd_message.set_token('PEAK_WEIGHT', l_id4);
5318: fnd_message.set_token('MAX_WEIGHT', l_id5);
5319:
5320: If l_id7 > 0 Then
5313: Else
5314: l_str1 := ' ';
5315: End If;
5316: fnd_message.set_token('PEAK_WEIGHT_UTILIZATION', l_str1);
5317: fnd_message.set_token('PEAK_WEIGHT', l_id4);
5318: fnd_message.set_token('MAX_WEIGHT', l_id5);
5319:
5320: If l_id7 > 0 Then
5321: l_id8 := round((l_id6/l_id7) * 100);
5314: l_str1 := ' ';
5315: End If;
5316: fnd_message.set_token('PEAK_WEIGHT_UTILIZATION', l_str1);
5317: fnd_message.set_token('PEAK_WEIGHT', l_id4);
5318: fnd_message.set_token('MAX_WEIGHT', l_id5);
5319:
5320: If l_id7 > 0 Then
5321: l_id8 := round((l_id6/l_id7) * 100);
5322: l_str1 := l_id8;
5322: l_str1 := l_id8;
5323: Else
5324: l_str1 := ' ';
5325: End If;
5326: fnd_message.set_token('PEAK_PALLET_UTILIZATION', l_str1);
5327: fnd_message.set_token('PEAK_PALLETS', l_id6);
5328: fnd_message.set_token('MAX_PALLETS', l_id7);
5329:
5330: Open l_uom_cur;
5323: Else
5324: l_str1 := ' ';
5325: End If;
5326: fnd_message.set_token('PEAK_PALLET_UTILIZATION', l_str1);
5327: fnd_message.set_token('PEAK_PALLETS', l_id6);
5328: fnd_message.set_token('MAX_PALLETS', l_id7);
5329:
5330: Open l_uom_cur;
5331: Fetch l_uom_cur Into l_uom;
5324: l_str1 := ' ';
5325: End If;
5326: fnd_message.set_token('PEAK_PALLET_UTILIZATION', l_str1);
5327: fnd_message.set_token('PEAK_PALLETS', l_id6);
5328: fnd_message.set_token('MAX_PALLETS', l_id7);
5329:
5330: Open l_uom_cur;
5331: Fetch l_uom_cur Into l_uom;
5332: If l_uom_cur%Found Then
5336: l_str1 := '';
5337: l_str2 := '';
5338: End If;
5339: Close l_uom_cur;
5340: fnd_message.set_token('WEIGHT_UOM', l_str1);
5341: fnd_message.set_token('VOLUME_UOM', l_str2);
5342: l_message := fnd_message.get;
5343: ELSIF P_EXCEPTION_TYPE = 602 THEN --Item Vehicle Incompatibility
5344: IF P_LINE_NUM = 1 THEN
5337: l_str2 := '';
5338: End If;
5339: Close l_uom_cur;
5340: fnd_message.set_token('WEIGHT_UOM', l_str1);
5341: fnd_message.set_token('VOLUME_UOM', l_str2);
5342: l_message := fnd_message.get;
5343: ELSIF P_EXCEPTION_TYPE = 602 THEN --Item Vehicle Incompatibility
5344: IF P_LINE_NUM = 1 THEN
5345: fnd_message.set_name('MST', 'MST_EXCEP_TOKEN_602_1');
5338: End If;
5339: Close l_uom_cur;
5340: fnd_message.set_token('WEIGHT_UOM', l_str1);
5341: fnd_message.set_token('VOLUME_UOM', l_str2);
5342: l_message := fnd_message.get;
5343: ELSIF P_EXCEPTION_TYPE = 602 THEN --Item Vehicle Incompatibility
5344: IF P_LINE_NUM = 1 THEN
5345: fnd_message.set_name('MST', 'MST_EXCEP_TOKEN_602_1');
5346:
5341: fnd_message.set_token('VOLUME_UOM', l_str2);
5342: l_message := fnd_message.get;
5343: ELSIF P_EXCEPTION_TYPE = 602 THEN --Item Vehicle Incompatibility
5344: IF P_LINE_NUM = 1 THEN
5345: fnd_message.set_name('MST', 'MST_EXCEP_TOKEN_602_1');
5346:
5347: SELECT number1, number2, number3
5348: INTO l_id1, l_id2, l_id3
5349: FROM mst_out_request_details
5349: FROM mst_out_request_details
5350: WHERE out_request_detail_id = P_OUT_REQUEST_DETAIL_ID
5351: AND exception_type = P_EXCEPTION_TYPE;
5352: IF l_id1 < 0 THEN
5353: fnd_message.set_token('TRIP_NUMBER', l_new_trip);
5354: ELSE
5355: fnd_message.set_token('TRIP_NUMBER', l_id1);
5356: END IF;
5357:
5351: AND exception_type = P_EXCEPTION_TYPE;
5352: IF l_id1 < 0 THEN
5353: fnd_message.set_token('TRIP_NUMBER', l_new_trip);
5354: ELSE
5355: fnd_message.set_token('TRIP_NUMBER', l_id1);
5356: END IF;
5357:
5358: -- item_description
5359: select msitl.description into l_str1
5362: and mdd.delivery_detail_id = l_id2
5363: and mdd.inventory_item_id = msitl.inventory_item_id
5364: and mdd.organization_id = msitl.organization_id
5365: and msitl.language = userenv('LANG');
5366: fnd_message.set_token('ITEM_DESCRIPTION', l_str1);
5367:
5368: SELECT msikfv.concatenated_segments
5369: into l_str2
5370: FROM mtl_system_items_kfv msikfv, fte_vehicle_types fvt
5370: FROM mtl_system_items_kfv msikfv, fte_vehicle_types fvt
5371: WHERE fvt.vehicle_type_id = l_id3
5372: AND fvt.organization_id = msikfv.organization_id
5373: AND fvt.inventory_item_id = msikfv.inventory_item_id;
5374: fnd_message.set_token('VEHICLE_TYPE', l_str2);
5375: l_message := fnd_message.get;
5376: END IF;
5377: ELSIF P_EXCEPTION_TYPE IN (700, 701) THEN
5378: SELECT number1
5371: WHERE fvt.vehicle_type_id = l_id3
5372: AND fvt.organization_id = msikfv.organization_id
5373: AND fvt.inventory_item_id = msikfv.inventory_item_id;
5374: fnd_message.set_token('VEHICLE_TYPE', l_str2);
5375: l_message := fnd_message.get;
5376: END IF;
5377: ELSIF P_EXCEPTION_TYPE IN (700, 701) THEN
5378: SELECT number1
5379: INTO l_id1
5379: INTO l_id1
5380: FROM mst_out_request_details
5381: WHERE out_request_detail_id = P_OUT_REQUEST_DETAIL_ID
5382: AND exception_type = P_EXCEPTION_TYPE;
5383: fnd_message.set_name('MST', 'MST_EXCEP_TOKEN_' || P_EXCEPTION_TYPE || '_1');
5384:
5385: Open l_fac_desc_cur (l_id1);
5386: Fetch l_fac_desc_cur Into l_str1;
5387: If l_fac_desc_cur%NotFound Then
5387: If l_fac_desc_cur%NotFound Then
5388: l_str1 := '';
5389: End If;
5390: Close l_fac_desc_cur;
5391: fnd_message.set_token('FACILITY_DESC', l_str1);
5392: l_message := fnd_message.get;
5393: ELSIF P_EXCEPTION_TYPE = 702 THEN
5394: SELECT number1, number2, number3
5395: INTO l_id1, l_id2, l_id3
5388: l_str1 := '';
5389: End If;
5390: Close l_fac_desc_cur;
5391: fnd_message.set_token('FACILITY_DESC', l_str1);
5392: l_message := fnd_message.get;
5393: ELSIF P_EXCEPTION_TYPE = 702 THEN
5394: SELECT number1, number2, number3
5395: INTO l_id1, l_id2, l_id3
5396: FROM mst_out_request_details
5395: INTO l_id1, l_id2, l_id3
5396: FROM mst_out_request_details
5397: WHERE out_request_detail_id = P_OUT_REQUEST_DETAIL_ID
5398: AND exception_type = P_EXCEPTION_TYPE;
5399: fnd_message.set_name('MST', 'MST_EXCEP_TOKEN_702_1');
5400: IF l_id1 < 0 THEN
5401: fnd_message.set_token('TRIP_NUMBER', l_new_trip);
5402: ELSE
5403: fnd_message.set_token('TRIP_NUMBER', l_id1);
5397: WHERE out_request_detail_id = P_OUT_REQUEST_DETAIL_ID
5398: AND exception_type = P_EXCEPTION_TYPE;
5399: fnd_message.set_name('MST', 'MST_EXCEP_TOKEN_702_1');
5400: IF l_id1 < 0 THEN
5401: fnd_message.set_token('TRIP_NUMBER', l_new_trip);
5402: ELSE
5403: fnd_message.set_token('TRIP_NUMBER', l_id1);
5404: END IF;
5405:
5399: fnd_message.set_name('MST', 'MST_EXCEP_TOKEN_702_1');
5400: IF l_id1 < 0 THEN
5401: fnd_message.set_token('TRIP_NUMBER', l_new_trip);
5402: ELSE
5403: fnd_message.set_token('TRIP_NUMBER', l_id1);
5404: END IF;
5405:
5406: Open l_vehicle_cur(l_id2);
5407: Fetch l_vehicle_cur Into l_str2;
5408: If l_vehicle_cur%NotFound Then
5409: l_str2 := '';
5410: End If;
5411: Close l_vehicle_cur;
5412: fnd_message.set_token('VEHICLE_TYPE', l_str2);
5413:
5414: Open l_fac_desc_cur (l_id3);
5415: Fetch l_fac_desc_cur Into l_str1;
5416: If l_fac_desc_cur%NotFound Then
5416: If l_fac_desc_cur%NotFound Then
5417: l_str1 := '';
5418: End If;
5419: Close l_fac_desc_cur;
5420: fnd_message.set_token('FACILITY_DESC', l_str1);
5421: l_message := fnd_message.get;
5422: ELSIF P_EXCEPTION_TYPE = 703 THEN
5423: SELECT number1, number2, number3
5424: INTO l_id1, l_id2, l_id3
5417: l_str1 := '';
5418: End If;
5419: Close l_fac_desc_cur;
5420: fnd_message.set_token('FACILITY_DESC', l_str1);
5421: l_message := fnd_message.get;
5422: ELSIF P_EXCEPTION_TYPE = 703 THEN
5423: SELECT number1, number2, number3
5424: INTO l_id1, l_id2, l_id3
5425: FROM mst_out_request_details
5424: INTO l_id1, l_id2, l_id3
5425: FROM mst_out_request_details
5426: WHERE out_request_detail_id = P_OUT_REQUEST_DETAIL_ID
5427: AND exception_type = P_EXCEPTION_TYPE;
5428: fnd_message.set_name('MST', 'MST_EXCEP_TOKEN_703_1');
5429: IF l_id1 < 0 THEN
5430: fnd_message.set_token('TRIP_NUMBER', l_new_trip);
5431: ELSE
5432: fnd_message.set_token('TRIP_NUMBER', l_id1);
5426: WHERE out_request_detail_id = P_OUT_REQUEST_DETAIL_ID
5427: AND exception_type = P_EXCEPTION_TYPE;
5428: fnd_message.set_name('MST', 'MST_EXCEP_TOKEN_703_1');
5429: IF l_id1 < 0 THEN
5430: fnd_message.set_token('TRIP_NUMBER', l_new_trip);
5431: ELSE
5432: fnd_message.set_token('TRIP_NUMBER', l_id1);
5433: END IF;
5434:
5428: fnd_message.set_name('MST', 'MST_EXCEP_TOKEN_703_1');
5429: IF l_id1 < 0 THEN
5430: fnd_message.set_token('TRIP_NUMBER', l_new_trip);
5431: ELSE
5432: fnd_message.set_token('TRIP_NUMBER', l_id1);
5433: END IF;
5434:
5435: Open l_carrier_cur(l_id2);
5436: Fetch l_carrier_cur Into l_str2;
5437: If l_carrier_cur%NotFound Then
5438: l_str2 := '';
5439: End If;
5440: Close l_carrier_cur;
5441: fnd_message.set_token('FREIGHT_CODE', l_str2);
5442:
5443: Open l_fac_desc_cur (l_id3);
5444: Fetch l_fac_desc_cur Into l_str1;
5445: If l_fac_desc_cur%NotFound Then
5445: If l_fac_desc_cur%NotFound Then
5446: l_str1 := '';
5447: End If;
5448: Close l_fac_desc_cur;
5449: fnd_message.set_token('FACILITY_DESC', l_str1);
5450: l_message := fnd_message.get;
5451: ELSIF P_EXCEPTION_TYPE = 705 THEN --Facility Item Incompatibility
5452: IF P_LINE_NUM = 1 THEN
5453: fnd_message.set_name('MST', 'MST_EXCEP_TOKEN_705_1');
5446: l_str1 := '';
5447: End If;
5448: Close l_fac_desc_cur;
5449: fnd_message.set_token('FACILITY_DESC', l_str1);
5450: l_message := fnd_message.get;
5451: ELSIF P_EXCEPTION_TYPE = 705 THEN --Facility Item Incompatibility
5452: IF P_LINE_NUM = 1 THEN
5453: fnd_message.set_name('MST', 'MST_EXCEP_TOKEN_705_1');
5454: SELECT number1, number2, number3
5449: fnd_message.set_token('FACILITY_DESC', l_str1);
5450: l_message := fnd_message.get;
5451: ELSIF P_EXCEPTION_TYPE = 705 THEN --Facility Item Incompatibility
5452: IF P_LINE_NUM = 1 THEN
5453: fnd_message.set_name('MST', 'MST_EXCEP_TOKEN_705_1');
5454: SELECT number1, number2, number3
5455: INTO l_id1, l_id2, l_id3
5456: FROM mst_out_request_details
5457: WHERE out_request_detail_id = P_OUT_REQUEST_DETAIL_ID
5456: FROM mst_out_request_details
5457: WHERE out_request_detail_id = P_OUT_REQUEST_DETAIL_ID
5458: AND exception_type = P_EXCEPTION_TYPE;
5459: IF l_id1 < 0 THEN
5460: fnd_message.set_token('TRIP_NUMBER', l_new_trip);
5461: ELSE
5462: fnd_message.set_token('TRIP_NUMBER', l_id1);
5463: END IF;
5464:
5458: AND exception_type = P_EXCEPTION_TYPE;
5459: IF l_id1 < 0 THEN
5460: fnd_message.set_token('TRIP_NUMBER', l_new_trip);
5461: ELSE
5462: fnd_message.set_token('TRIP_NUMBER', l_id1);
5463: END IF;
5464:
5465: -- item_description
5466: select msitl.description into l_str1
5469: and mdd.delivery_detail_id = l_id2
5470: and mdd.inventory_item_id = msitl.inventory_item_id
5471: and mdd.organization_id = msitl.organization_id
5472: and msitl.language = userenv('LANG');
5473: fnd_message.set_token('ITEM_DESCRIPTION', l_str1);
5474:
5475: -- Facility_Description
5476: select flp.description
5477: into l_str2
5476: select flp.description
5477: into l_str2
5478: from fte_location_parameters flp
5479: where flp.location_id = l_id3;
5480: fnd_message.set_token('FACILITY_DESCRIPTION', l_str2);
5481: l_message := fnd_message.get;
5482: END IF;
5483: ELSIF P_EXCEPTION_TYPE = 706 THEN --Facility Mode Incompatibility
5484: IF P_LINE_NUM = 1 THEN
5477: into l_str2
5478: from fte_location_parameters flp
5479: where flp.location_id = l_id3;
5480: fnd_message.set_token('FACILITY_DESCRIPTION', l_str2);
5481: l_message := fnd_message.get;
5482: END IF;
5483: ELSIF P_EXCEPTION_TYPE = 706 THEN --Facility Mode Incompatibility
5484: IF P_LINE_NUM = 1 THEN
5485: fnd_message.set_name('MST', 'MST_EXCEP_TOKEN_706_1');
5481: l_message := fnd_message.get;
5482: END IF;
5483: ELSIF P_EXCEPTION_TYPE = 706 THEN --Facility Mode Incompatibility
5484: IF P_LINE_NUM = 1 THEN
5485: fnd_message.set_name('MST', 'MST_EXCEP_TOKEN_706_1');
5486: SELECT number1, number3, char1
5487: INTO l_id1, l_id3, l_str1
5488: FROM mst_out_request_details
5489: WHERE out_request_detail_id = P_OUT_REQUEST_DETAIL_ID
5488: FROM mst_out_request_details
5489: WHERE out_request_detail_id = P_OUT_REQUEST_DETAIL_ID
5490: AND exception_type = P_EXCEPTION_TYPE;
5491: IF l_id1 < 0 THEN
5492: fnd_message.set_token('TRIP_NUMBER', l_new_trip);
5493: ELSE
5494: fnd_message.set_token('TRIP_NUMBER', l_id1);
5495: END IF;
5496:
5490: AND exception_type = P_EXCEPTION_TYPE;
5491: IF l_id1 < 0 THEN
5492: fnd_message.set_token('TRIP_NUMBER', l_new_trip);
5493: ELSE
5494: fnd_message.set_token('TRIP_NUMBER', l_id1);
5495: END IF;
5496:
5497: SELECT wlk.meaning
5498: INTO l_str2
5499: FROM wsh_lookups wlk
5500: WHERE wlk.lookup_type = 'WSH_MODE_OF_TRANSPORT'
5501: and wlk.lookup_code = l_str1;
5502:
5503: fnd_message.set_token('MODE_OF_TRANSPORT', l_str2);
5504:
5505: -- Facility_Description
5506: select flp.description
5507: into l_str3
5506: select flp.description
5507: into l_str3
5508: from fte_location_parameters flp
5509: where flp.location_id = l_id3;
5510: fnd_message.set_token('FACILITY_DESCRIPTION', l_str3);
5511: l_message := fnd_message.get;
5512: END IF;
5513: ELSIF P_EXCEPTION_TYPE = 707 THEN --Facility Facility Incompatibility
5514: IF P_LINE_NUM = 1 THEN
5507: into l_str3
5508: from fte_location_parameters flp
5509: where flp.location_id = l_id3;
5510: fnd_message.set_token('FACILITY_DESCRIPTION', l_str3);
5511: l_message := fnd_message.get;
5512: END IF;
5513: ELSIF P_EXCEPTION_TYPE = 707 THEN --Facility Facility Incompatibility
5514: IF P_LINE_NUM = 1 THEN
5515: fnd_message.set_name('MST', 'MST_EXCEP_TOKEN_707_1');
5511: l_message := fnd_message.get;
5512: END IF;
5513: ELSIF P_EXCEPTION_TYPE = 707 THEN --Facility Facility Incompatibility
5514: IF P_LINE_NUM = 1 THEN
5515: fnd_message.set_name('MST', 'MST_EXCEP_TOKEN_707_1');
5516:
5517: SELECT number1, number2, number3, number4
5518: INTO l_id1, l_id2, l_id3, l_id4
5519: FROM mst_out_request_details
5518: INTO l_id1, l_id2, l_id3, l_id4
5519: FROM mst_out_request_details
5520: WHERE out_request_detail_id = P_OUT_REQUEST_DETAIL_ID
5521: AND exception_type = P_EXCEPTION_TYPE;
5522: fnd_message.set_token('DELIVERY_NUMBER', l_id1);
5523:
5524: --End_facility_name, intermediate_facility_name
5525: select flp1.facility_code
5526: into l_str1
5526: into l_str1
5527: from fte_location_parameters flp1
5528: where flp1.location_id = l_id2;
5529:
5530: fnd_message.set_token('END_FACILITY_NAME', l_str1);
5531: select flp1.facility_code
5532: into l_str2
5533: from fte_location_parameters flp1
5534: where flp1.location_id = l_id3;
5531: select flp1.facility_code
5532: into l_str2
5533: from fte_location_parameters flp1
5534: where flp1.location_id = l_id3;
5535: fnd_message.set_token('INTERMEDIATE_FACILITY_NAME', l_str2);
5536: l_message := fnd_message.get;
5537: END IF;
5538: ELSIF P_EXCEPTION_TYPE = 800 THEN
5539: SELECT number1, number2, number3, number4, number5
5532: into l_str2
5533: from fte_location_parameters flp1
5534: where flp1.location_id = l_id3;
5535: fnd_message.set_token('INTERMEDIATE_FACILITY_NAME', l_str2);
5536: l_message := fnd_message.get;
5537: END IF;
5538: ELSIF P_EXCEPTION_TYPE = 800 THEN
5539: SELECT number1, number2, number3, number4, number5
5540: INTO l_id1, l_id2, l_id3, l_id4, l_id5
5540: INTO l_id1, l_id2, l_id3, l_id4, l_id5
5541: FROM mst_out_request_details
5542: WHERE out_request_detail_id = P_OUT_REQUEST_DETAIL_ID
5543: AND exception_type = P_EXCEPTION_TYPE;
5544: fnd_message.set_name('MST', 'MST_EXCEP_TOKEN_800_1');
5545: IF l_id1 < 0 THEN
5546: fnd_message.set_token('TRIP_NUMBER', l_new_trip);
5547: ELSE
5548: fnd_message.set_token('TRIP_NUMBER', l_id1);
5542: WHERE out_request_detail_id = P_OUT_REQUEST_DETAIL_ID
5543: AND exception_type = P_EXCEPTION_TYPE;
5544: fnd_message.set_name('MST', 'MST_EXCEP_TOKEN_800_1');
5545: IF l_id1 < 0 THEN
5546: fnd_message.set_token('TRIP_NUMBER', l_new_trip);
5547: ELSE
5548: fnd_message.set_token('TRIP_NUMBER', l_id1);
5549: END IF;
5550:
5544: fnd_message.set_name('MST', 'MST_EXCEP_TOKEN_800_1');
5545: IF l_id1 < 0 THEN
5546: fnd_message.set_token('TRIP_NUMBER', l_new_trip);
5547: ELSE
5548: fnd_message.set_token('TRIP_NUMBER', l_id1);
5549: END IF;
5550:
5551: Open l_carrier_cur(l_id2);
5552: Fetch l_carrier_cur Into l_str2;
5553: If l_carrier_cur%NotFound Then
5554: l_str2 := '';
5555: End If;
5556: Close l_carrier_cur;
5557: fnd_message.set_token('FREIGHT_CODE', l_str2);
5558:
5559: Open l_fac_desc_cur (l_id3);
5560: Fetch l_fac_desc_cur Into l_str1;
5561: If l_fac_desc_cur%NotFound Then
5561: If l_fac_desc_cur%NotFound Then
5562: l_str1 := '';
5563: End If;
5564: Close l_fac_desc_cur;
5565: fnd_message.set_token('FACILITY_DESC', l_str1);
5566:
5567: If l_id4 Is Not Null Then
5568: l_str2 := Get_Partner_Name(l_id4, 1);
5569: Elsif l_id5 Is Not Null Then
5569: Elsif l_id5 Is Not Null Then
5570: l_str2 := Get_Partner_Name(l_id5, 2);
5571: End If;
5572:
5573: fnd_message.set_token('TRADING_PARTNER', l_str2);
5574: l_message := fnd_message.get;
5575: ELSIF P_EXCEPTION_TYPE = 801 THEN
5576: SELECT number1, number2, number3, number4, number5, number6
5577: INTO l_id1, l_id2, l_id3, l_id4, l_id5, l_id6
5570: l_str2 := Get_Partner_Name(l_id5, 2);
5571: End If;
5572:
5573: fnd_message.set_token('TRADING_PARTNER', l_str2);
5574: l_message := fnd_message.get;
5575: ELSIF P_EXCEPTION_TYPE = 801 THEN
5576: SELECT number1, number2, number3, number4, number5, number6
5577: INTO l_id1, l_id2, l_id3, l_id4, l_id5, l_id6
5578: FROM mst_out_request_details
5577: INTO l_id1, l_id2, l_id3, l_id4, l_id5, l_id6
5578: FROM mst_out_request_details
5579: WHERE out_request_detail_id = P_OUT_REQUEST_DETAIL_ID
5580: AND exception_type = P_EXCEPTION_TYPE;
5581: fnd_message.set_name('MST', 'MST_EXCEP_TOKEN_801_1');
5582: IF l_id1 < 0 THEN
5583: fnd_message.set_token('TRIP_NUMBER', l_new_trip);
5584: ELSE
5585: fnd_message.set_token('TRIP_NUMBER', l_id1);
5579: WHERE out_request_detail_id = P_OUT_REQUEST_DETAIL_ID
5580: AND exception_type = P_EXCEPTION_TYPE;
5581: fnd_message.set_name('MST', 'MST_EXCEP_TOKEN_801_1');
5582: IF l_id1 < 0 THEN
5583: fnd_message.set_token('TRIP_NUMBER', l_new_trip);
5584: ELSE
5585: fnd_message.set_token('TRIP_NUMBER', l_id1);
5586: END IF;
5587:
5581: fnd_message.set_name('MST', 'MST_EXCEP_TOKEN_801_1');
5582: IF l_id1 < 0 THEN
5583: fnd_message.set_token('TRIP_NUMBER', l_new_trip);
5584: ELSE
5585: fnd_message.set_token('TRIP_NUMBER', l_id1);
5586: END IF;
5587:
5588: Open l_carrier_cur(l_id2);
5589: Fetch l_carrier_cur Into l_str2;
5590: If l_carrier_cur%NotFound Then
5591: l_str2 := '';
5592: End If;
5593: Close l_carrier_cur;
5594: fnd_message.set_token('FREIGHT_CODE', l_str2);
5595:
5596: Open l_fac_desc_cur (l_id3);
5597: Fetch l_fac_desc_cur Into l_str1;
5598: If l_fac_desc_cur%NotFound Then
5598: If l_fac_desc_cur%NotFound Then
5599: l_str1 := '';
5600: End If;
5601: Close l_fac_desc_cur;
5602: fnd_message.set_token('FACILITY_DESC', l_str1);
5603:
5604: If l_id4 Is Not Null Then
5605: l_str2 := Get_Partner_Name(l_id4, 1);
5606: Elsif l_id5 Is Not Null Then
5607: l_str2 := Get_Partner_Name(l_id5, 2);
5608: Elsif l_id6 Is Not Null Then
5609: l_str2 := fnd_profile.value('MST_COMPANY_NAME');
5610: End If;
5611: fnd_message.set_token('CUST_SUPPLIER', l_str2);
5612: l_message := fnd_message.get;
5613: ELSIF P_EXCEPTION_TYPE = 1000 THEN
5614: SELECT number1
5615: INTO l_id1
5608: Elsif l_id6 Is Not Null Then
5609: l_str2 := fnd_profile.value('MST_COMPANY_NAME');
5610: End If;
5611: fnd_message.set_token('CUST_SUPPLIER', l_str2);
5612: l_message := fnd_message.get;
5613: ELSIF P_EXCEPTION_TYPE = 1000 THEN
5614: SELECT number1
5615: INTO l_id1
5616: FROM mst_out_request_details
5615: INTO l_id1
5616: FROM mst_out_request_details
5617: WHERE out_request_detail_id = P_OUT_REQUEST_DETAIL_ID
5618: AND exception_type = P_EXCEPTION_TYPE;
5619: fnd_message.set_name('MST', 'MST_EXCEP_TOKEN_1000_1');
5620: fnd_message.set_token('DELIVERY_NUMBER', l_id1);
5621: l_message := fnd_message.get;
5622: ELSE
5623: SELECT meaning
5616: FROM mst_out_request_details
5617: WHERE out_request_detail_id = P_OUT_REQUEST_DETAIL_ID
5618: AND exception_type = P_EXCEPTION_TYPE;
5619: fnd_message.set_name('MST', 'MST_EXCEP_TOKEN_1000_1');
5620: fnd_message.set_token('DELIVERY_NUMBER', l_id1);
5621: l_message := fnd_message.get;
5622: ELSE
5623: SELECT meaning
5624: INTO l_message
5617: WHERE out_request_detail_id = P_OUT_REQUEST_DETAIL_ID
5618: AND exception_type = P_EXCEPTION_TYPE;
5619: fnd_message.set_name('MST', 'MST_EXCEP_TOKEN_1000_1');
5620: fnd_message.set_token('DELIVERY_NUMBER', l_id1);
5621: l_message := fnd_message.get;
5622: ELSE
5623: SELECT meaning
5624: INTO l_message
5625: FROM mfg_lookups