[Home] [Help]
1216: SELECT distinct wdl.delivery_leg_id,
1217: decode( (select distinct WDI.sequence_number from wsh_document_instances WDI,FTE_INVOICE_HEADERS FH
1218: where
1219: WDI.ENTITY_ID = DECODE(WT.MODE_OF_TRANSPORT,'LTL',WDL.DELIVERY_LEG_ID,'TRUCK',WT.TRIP_ID)
1220: AND WDI.ENTITY_NAME = DECODE(WT.MODE_OF_TRANSPORT,'LTL','WSH_DELIVERY_LEGS','TRUCK','WSH_TRIPS')
1221: AND WDI.SEQUENCE_NUMBER = FH.BOL
1222: AND FH.BILL_STATUS <> 'OBSOLETE'
1223: AND FH.BILL_STATUS = decode(FH.BOL,p_bol,FH.BILL_STATUS,'APPROVED')
1224: ),NULL,1,0) bol_flag
1226: WSH_DELIVERY_DETAILS WDD,
1227: WSH_DELIVERY_ASSIGNMENTS WDA,
1228: WSH_DELIVERY_LEGS WDL,
1229: WSH_TRIP_STOPS WTS,
1230: WSH_TRIPS WT
1231: WHERE
1232: WDD.DELIVERY_DETAIL_ID = WDA.DELIVERY_DETAIL_ID
1233: AND (WDA.DELIVERY_ID = WDL.DELIVERY_ID OR WDA.PARENT_DELIVERY_ID = WDL.DELIVERY_ID)
1234: AND WDL.PARENT_DELIVERY_LEG_ID IS NULL
1254: WDD.WEIGHT_UOM_CODE,
1255: WDD.CURRENCY_CODE,
1256: (SELECT WDI.SEQUENCE_NUMBER FROM WSH_DOCUMENT_INSTANCES WDI
1257: WHERE WDI.ENTITY_ID = DECODE(WT.MODE_OF_TRANSPORT,'LTL',NVL(WDL.PARENT_DELIVERY_LEG_ID,WDL.DELIVERY_LEG_ID),'TRUCK',WT.TRIP_ID)
1258: AND WDI.ENTITY_NAME = DECODE(WT.MODE_OF_TRANSPORT,'LTL','WSH_DELIVERY_LEGS','TRUCK','WSH_TRIPS')
1259: ) SEQUENCE_NUMBER
1260: FROM
1261: WSH_DELIVERY_DETAILS WDD,
1262: WSH_DELIVERY_ASSIGNMENTS WDA,
1261: WSH_DELIVERY_DETAILS WDD,
1262: WSH_DELIVERY_ASSIGNMENTS WDA,
1263: WSH_DELIVERY_LEGS WDL,
1264: WSH_TRIP_STOPS WTS,
1265: WSH_TRIPS WT,
1266: MTL_ITEM_CATEGORIES MIC,
1267: MTL_CATEGORIES_KFV C,
1268: MTL_CATEGORY_SETS S
1269: WHERE
1558: WSH_DELIVERY_DETAILS WDD,
1559: WSH_DELIVERY_ASSIGNMENTS WDA,
1560: WSH_DELIVERY_LEGS WDL,
1561: WSH_TRIP_STOPS WTS,
1562: WSH_TRIPS WT,
1563: WSH_DOCUMENT_INSTANCES WDI
1564: WHERE
1565: WDD.DELIVERY_DETAIL_ID = WDA.DELIVERY_DETAIL_ID
1566: AND WDD.LINE_DIRECTION = 'I'
1567: AND WDA.DELIVERY_ID = WDL.DELIVERY_ID
1568: AND WTS.STOP_ID = WDL.PICK_UP_STOP_ID
1569: AND WT.TRIP_ID = WTS.TRIP_ID
1570: AND WDI.ENTITY_ID = WT.TRIP_ID
1571: AND WDI.ENTITY_NAME = 'WSH_TRIPS'
1572: AND WDI.DOCUMENT_TYPE = 'MBOL'
1573: AND WDI.SEQUENCE_NUMBER = p_bol_no;
1574:
1575:
1579: SELECT distinct wdl.delivery_leg_id,
1580: decode( (select WDI.sequence_number from wsh_document_instances WDI,FTE_INVOICE_HEADERS FH
1581: where
1582: WDI.ENTITY_ID = DECODE(WT.MODE_OF_TRANSPORT,'LTL',WDL.DELIVERY_LEG_ID,'TRUCK',WT.TRIP_ID)
1583: AND WDI.ENTITY_NAME = DECODE(WT.MODE_OF_TRANSPORT,'LTL','WSH_DELIVERY_LEGS','TRUCK','WSH_TRIPS')
1584: AND WDI.SEQUENCE_NUMBER = FH.BOL
1585: AND FH.BILL_STATUS = decode(FH.BOL,p_bol,FH.BILL_STATUS,'APPROVED')
1586: ),NULL,1,0) bol_flag
1587: FROM
1588: WSH_DELIVERY_DETAILS WDD,
1589: WSH_DELIVERY_ASSIGNMENTS WDA,
1590: WSH_DELIVERY_LEGS WDL,
1591: WSH_TRIP_STOPS WTS,
1592: WSH_TRIPS WT
1593: WHERE
1594: WDD.DELIVERY_DETAIL_ID = WDA.DELIVERY_DETAIL_ID
1595: AND (WDA.DELIVERY_ID = WDL.DELIVERY_ID OR WDA.PARENT_DELIVERY_ID = WDL.DELIVERY_ID)
1596: AND WDL.PARENT_DELIVERY_LEG_ID IS NULL
1616: WDD.WEIGHT_UOM_CODE,
1617: WDD.CURRENCY_CODE,
1618: (SELECT WDI.SEQUENCE_NUMBER FROM WSH_DOCUMENT_INSTANCES WDI
1619: WHERE WDI.ENTITY_ID = DECODE(WT.MODE_OF_TRANSPORT,'LTL',NVL(WDL.PARENT_DELIVERY_LEG_ID,WDL.DELIVERY_LEG_ID),'TRUCK',WT.TRIP_ID)
1620: AND WDI.ENTITY_NAME = DECODE(WT.MODE_OF_TRANSPORT,'LTL','WSH_DELIVERY_LEGS','TRUCK','WSH_TRIPS')
1621: ) SEQUENCE_NUMBER
1622: FROM
1623: WSH_DELIVERY_DETAILS WDD,
1624: WSH_DELIVERY_ASSIGNMENTS WDA,
1623: WSH_DELIVERY_DETAILS WDD,
1624: WSH_DELIVERY_ASSIGNMENTS WDA,
1625: WSH_DELIVERY_LEGS WDL,
1626: WSH_TRIP_STOPS WTS,
1627: WSH_TRIPS WT,
1628: MTL_ITEM_CATEGORIES MIC,
1629: MTL_CATEGORIES_KFV C,
1630: MTL_CATEGORY_SETS S
1631: WHERE