Sales Order Picking List

CREATE VIEW [dbo].[vw_salesSalesPickingList] 
AS 
  SELECT 'WMSPICKINGROUTE_ACTIVATIONDATETIME' = 
         wpr.activationdatetime, 
         'WMSPICKINGROUTE_ACTIVATIONDATETIMETZID' = 
         wpr.activationdatetimetzid, 
         'WMSPICKINGROUTE_AUTODECREASEQTY' = 
            wpr.autodecreaseqty, 
         'WMSPICKINGROUTE_CREATEDBY' = wpr.createdby, 
         'WMSPICKINGROUTE_CREATEDDATETIME' = 
            wpr.createddatetime, 
         'WMSPICKINGROUTE_CREATEDTRANSACTIONID' = 
         wpr.createdtransactionid, 
         'WMSPICKINGROUTE_CURRENTPICKPALLETID' = 
         wpr.currentpickpalletid, 
         'WMSPICKINGROUTE_CUSTOMER' = wpr.customer, 
         'WMSPICKINGROUTE_DATAAREAID' = wpr.dataareaid, 
         'WMSPICKINGROUTE_DELIVERYNAME' = wpr.deliveryname, 
         'WMSPICKINGROUTE_DELIVERYPOSTALADDRESS' = 
         wpr.deliverypostaladdress, 
         'WMSPICKINGROUTE_DLVDATE' = wpr.dlvdate, 
         'WMSPICKINGROUTE_DLVMODEID' = wpr.dlvmodeid, 
         'WMSPICKINGROUTE_DLVTERMID' = wpr.dlvtermid, 
         'WMSPICKINGROUTE_ENDDATETIME' = wpr.enddatetime, 
         'WMSPICKINGROUTE_ENDDATETIMETZID' = 
            wpr.enddatetimetzid, 
         'WMSPICKINGROUTE_EXPECTEDEXPEDITIONTIME' = 
         wpr.expectedexpeditiontime, 
         'WMSPICKINGROUTE_EXPEDITIONSTATUS' = 
            wpr.expeditionstatus, 
         'WMSPICKINGROUTE_HANDLINGTYPE' = wpr.handlingtype, 
         'WMSPICKINGROUTE_INTERCOMPANYPOSTED' = 
         wpr.intercompanyposted, 
         'WMSPICKINGROUTE_INVENTLOCATIONID' = 
            wpr.inventlocationid, 
         'WMSPICKINGROUTE_MCRPACKINGBOXNAME' = 
            wpr.mcrpackingboxname, 
         'WMSPICKINGROUTE_MCRPICKINGWAVEREF' = 
            wpr.mcrpickingwaveref, 
         'WMSPICKINGROUTE_MODIFIEDBY' = wpr.modifiedby, 
         'WMSPICKINGROUTE_MODIFIEDDATETIME' = 
            wpr.modifieddatetime, 
         'WMSPICKINGROUTE_MODIFIEDTRANSACTIONID' = 
         wpr.modifiedtransactionid, 
         'WMSPICKINGROUTE_OPERATORWORKER' = 
            wpr.operatorworker, 
         'WMSPICKINGROUTE_OPTIMIZEDPICKING' = 
            wpr.optimizedpicking, 
         'WMSPICKINGROUTE_PALLETTAGGING' = 
         wpr.pallettagging, 
         'WMSPICKINGROUTE_PARMID' = wpr.parmid, 
         'WMSPICKINGROUTE_PARTITION' = wpr.partition, 
         'WMSPICKINGROUTE_PICKINGAREAID' = 
         wpr.pickingareaid, 
         'WMSPICKINGROUTE_PICKINGROUTEID' = 
            wpr.pickingrouteid, 
         'WMSPICKINGROUTE_PRINTMGMTSITEID' = 
            wpr.printmgmtsiteid, 
         'WMSPICKINGROUTE_PRIORITY' = wpr.priority, 
         'WMSPICKINGROUTE_RECID' = wpr.recid, 
         'WMSPICKINGROUTE_RECVERSION' = wpr.recversion, 
         'WMSPICKINGROUTE_SHIPMENTID' = wpr.shipmentid, 
         'WMSPICKINGROUTE_SHIPMENTTYPE' = wpr.shipmenttype, 
         'WMSPICKINGROUTE_STARTDATETIME' = 
         wpr.startdatetime, 
         'WMSPICKINGROUTE_STARTDATETIMETZID' = 
            wpr.startdatetimetzid, 
         'WMSPICKINGROUTE_TRANSREFID' = wpr.transrefid, 
         'WMSPICKINGROUTE_TRANSTYPE' = wpr.transtype, 
         'WMSPICKINGROUTE_VOLUME' = wpr.volume, 
         'WMSPICKINGROUTELINK_ACTIVATIONDATETIME' = 
         wprl.activationdatetime, 
         'WMSPICKINGROUTELINK_ACTIVATIONDATETIMETZID' = 
         wprl.activationdatetimetzid, 
         'WMSPICKINGROUTELINK_DATAAREAID' = wprl.dataareaid, 
         'WMSPICKINGROUTELINK_INVENTTRANSREFID' = 
         wprl.inventtransrefid, 
         'WMSPICKINGROUTELINK_INVENTTRANSTYPE' = 
            wprl.inventtranstype, 
         'WMSPICKINGROUTELINK_ORIGINVENTTRANSREFID' = 
         wprl.originventtransrefid, 
         'WMSPICKINGROUTELINK_PARTITION' = wprl.partition, 
         'WMSPICKINGROUTELINK_PICKINGROUTEID' = 
            wprl.pickingrouteid, 
         'WMSPICKINGROUTELINK_RECID' = wprl.recid, 
         'WMSPICKINGROUTELINK_RECVERSION' = wprl.recversion, 
         'WMSORDERTRANS_CASETAGGING' = wot.casetagging, 
         'WMSORDERTRANS_CREATEDBY' = wot.createdby, 
         'WMSORDERTRANS_CREATEDDATETIME' = 
            wot.createddatetime, 
         'WMSORDERTRANS_CREATEDTRANSACTIONID' = 
         wot.createdtransactionid, 
         'WMSORDERTRANS_CUSTOMER' = wot.customer, 
         'WMSORDERTRANS_DATAAREAID' = wot.dataareaid, 
         'WMSORDERTRANS_DELIVERYPOSTALADDRESS' = 
         wot.deliverypostaladdress, 
         'WMSORDERTRANS_DLVDATE' = wot.dlvdate, 
         'WMSORDERTRANS_EXPECTEDEXPEDITIONTIME' = 
         wot.expectedexpeditiontime, 
         'WMSORDERTRANS_EXPEDITIONSTATUS' = 
            wot.expeditionstatus, 
         'WMSORDERTRANS_EXPEDITIONTIME' = 
         wot.expeditiontime, 
         'WMSORDERTRANS_FULLPALLET' = 
         wot.fullpallet, 
         'WMSORDERTRANS_INVENTDIMFIXED' = 
         wot.inventdimfixed, 
         'WMSORDERTRANS_INVENTDIMID' = 
         wot.inventdimid, 
         'WMSORDERTRANS_INVENTTRANSID' = wot.inventtransid, 
         'WMSORDERTRANS_INVENTTRANSREFID' = 
            wot.inventtransrefid, 
         'WMSORDERTRANS_INVENTTRANSTYPE' = 
            wot.inventtranstype, 
         'WMSORDERTRANS_ISRESERVED' = 
            wot.isreserved, 
         'WMSORDERTRANS_ITEMID' = wot.itemid, 
         'WMSORDERTRANS_ITEMSORTCODE' = wot.itemsortcode, 
         'WMSORDERTRANS_ITEMTAGGING' = wot.itemtagging, 
         'WMSORDERTRANS_MODIFIEDBY' = wot.modifiedby, 
         'WMSORDERTRANS_MODIFIEDDATETIME' = 
            wot.modifieddatetime, 
         'WMSORDERTRANS_MODIFIEDTRANSACTIONID' = 
         wot.modifiedtransactionid, 
         'WMSORDERTRANS_OPERATORWORKER' = 
         wot.operatorworker, 
         'WMSORDERTRANS_OPTIMIZEDPICKING' = 
            wot.optimizedpicking, 
         'WMSORDERTRANS_ORDERID' = wot.orderid, 
         'WMSORDERTRANS_ORDERTYPE' = wot.ordertype, 
         'WMSORDERTRANS_PALLETIDPICKED' = 
         wot.palletidpicked, 
         'WMSORDERTRANS_PALLETTAGGING' = 
         wot.pallettagging, 
         'WMSORDERTRANS_PARTITION' = wot.partition, 
         'WMSORDERTRANS_PDSCWQTY' = wot.pdscwqty, 
         'WMSORDERTRANS_PDSCWZEROCWLINE' = 
            wot.pdscwzerocwline, 
         'WMSORDERTRANS_POSTTRANSFER' = 
            wot.posttransfer, 
         'WMSORDERTRANS_PRERESERVED' = wot.prereserved, 
         'WMSORDERTRANS_QTY' = wot.qty, 
         'WMSORDERTRANS_RECID' = wot.recid, 
         'WMSORDERTRANS_RECVERSION' = wot.recversion, 
         'WMSORDERTRANS_ROUTEID' = wot.routeid, 
         'WMSORDERTRANS_SHIPMENTID' = wot.shipmentid, 
         'WMSORDERTRANS_SHIPMENTIDORIGINAL' = 
            wot.shipmentidoriginal, 
         'WMSORDERTRANS_SORTCODE' = wot.sortcode, 
         'WMSORDERTRANS_TOINVENTDIMID' = wot.toinventdimid, 
         'WMSORDERTRANS_VOLUME' = wot.volume, 
         'INVENTDIM_CONFIGID' = id.configid, 
         'INVENTDIM_CREATEDDATETIME' = id.createddatetime, 
         'INVENTDIM_DATAAREAID' = id.dataareaid, 
         'INVENTDIM_INVENTBATCHID' = id.inventbatchid, 
         'INVENTDIM_INVENTCOLORID' = id.inventcolorid, 
         'INVENTDIM_INVENTDIMID' = id.inventdimid, 
         'INVENTDIM_INVENTGTDID_RU' = id.inventgtdid_ru, 
         'INVENTDIM_INVENTLOCATIONID' = id.inventlocationid, 
         'INVENTDIM_INVENTOWNERID_RU' = id.inventownerid_ru, 
         'INVENTDIM_INVENTPROFILEID_RU' = 
            id.inventprofileid_ru, 
         'INVENTDIM_INVENTSERIALID' = id.inventserialid, 
         'INVENTDIM_INVENTSITEID' = id.inventsiteid, 
         'INVENTDIM_INVENTSIZEID' = id.inventsizeid, 
         'INVENTDIM_INVENTSTATUSID' = id.inventstatusid, 
         'INVENTDIM_INVENTSTYLEID' = id.inventstyleid, 
         'INVENTDIM_LICENSEPLATEID' = id.licenseplateid, 
         'INVENTDIM_MODIFIEDBY' = id.modifiedby, 
         'INVENTDIM_MODIFIEDDATETIME' = id.modifieddatetime, 
         'INVENTDIM_PARTITION' = id.partition, 
         'INVENTDIM_RECID' = id.recid, 
         'INVENTDIM_RECVERSION' = id.recversion, 
         'INVENTDIM_SHA1HASH' = id.sha1hash, 
         'INVENTDIM_WMSLOCATIONID' = id.wmslocationid, 
         'INVENTDIM_WMSPALLETID' = id.wmspalletid 
  FROM   wmspickingroute wpr 
         INNER JOIN wmspickingroutelink wprl 
                 ON wprl.pickingrouteid = wpr.pickingrouteid 
                    AND wprl.activationdatetime = 
                        wpr.activationdatetime 
                    AND wprl.dataareaid = wpr.dataareaid 
                    AND wprl.partition = wpr.partition 
         INNER JOIN wmsordertrans wot 
                 ON wot.routeid = wpr.pickingrouteid 
                    AND wot.dataareaid = wpr.dataareaid 
                    AND wot.partition = wpr.partition 
         INNER JOIN inventdim id 
                 ON id.inventdimid = wot.inventdimid 
                    AND id.dataareaid = wot.dataareaid 
                    AND id.partition = wot.partition 

go