Sales Order Packing Slip

CREATE VIEW [dbo].[vw_salesSalesPackingSlip] 
AS 
  SELECT 'CUSTPACKINGSLIPJOUR_BILLOFLADINGID' = 
         cpsj.billofladingid, 
         'CUSTPACKINGSLIPJOUR_BOLADDRESS' = cpsj.boladdress, 
         'CUSTPACKINGSLIPJOUR_BOLCARRIERNAME' = 
            cpsj.bolcarriername, 
         'CUSTPACKINGSLIPJOUR_BOLFREIGHTEDBY' = 
            cpsj.bolfreightedby, 
         'CUSTPACKINGSLIPJOUR_BOLPACKAGEAPPEARANCE' = 
         cpsj.bolpackageappearance, 
         'CUSTPACKINGSLIPJOUR_COMPILER' = cpsj.compiler, 
         'CUSTPACKINGSLIPJOUR_CONTACTPERSONID' = 
            cpsj.contactpersonid, 
         'CUSTPACKINGSLIPJOUR_CREATEDDATETIME' = 
            cpsj.createddatetime, 
         'CUSTPACKINGSLIPJOUR_CUSTOMERREF' = 
         cpsj.customerref, 
         'CUSTPACKINGSLIPJOUR_DATAAREAID' = cpsj.dataareaid, 
         'CUSTPACKINGSLIPJOUR_DEFAULTDIMENSION' = 
         cpsj.defaultdimension, 
         'CUSTPACKINGSLIPJOUR_DEL_CREATEDTIME' = 
            cpsj.del_createdtime, 
         'CUSTPACKINGSLIPJOUR_DELIVERYDATE' = 
            cpsj.deliverydate, 
         'CUSTPACKINGSLIPJOUR_DELIVERYNAME' = 
            cpsj.deliveryname, 
         'CUSTPACKINGSLIPJOUR_DELIVERYPOSTALADDRESS' = 
         cpsj.deliverypostaladdress, 
         'CUSTPACKINGSLIPJOUR_DLVMODE' = cpsj.dlvmode, 
         'CUSTPACKINGSLIPJOUR_DLVREASON' = cpsj.dlvreason, 
         'CUSTPACKINGSLIPJOUR_DLVTERM' = cpsj.dlvterm, 
         'CUSTPACKINGSLIPJOUR_DOCUMENTDATE' = 
            cpsj.documentdate, 
         'CUSTPACKINGSLIPJOUR_EXPORTREASON' = 
            cpsj.exportreason, 
         'CUSTPACKINGSLIPJOUR_FREIGHTSLIPNUM' = 
            cpsj.freightslipnum, 
         'CUSTPACKINGSLIPJOUR_FREIGHTSLIPTYPE' = 
            cpsj.freightsliptype, 
         'CUSTPACKINGSLIPJOUR_INTERCOMPANYCOMPANYID' = 
         cpsj.intercompanycompanyid, 
         'CUSTPACKINGSLIPJOUR_INTERCOMPANYPOSTED' = 
         cpsj.intercompanyposted, 
         'CUSTPACKINGSLIPJOUR_INTERCOMPANYPURCHID' = 
         cpsj.intercompanypurchid, 
         'CUSTPACKINGSLIPJOUR_INTERNALPACKINGSLIPID' = 
         cpsj.internalpackingslipid, 
         'CUSTPACKINGSLIPJOUR_INTRASTATDISPATCH' = 
         cpsj.intrastatdispatch, 
         'CUSTPACKINGSLIPJOUR_INTRASTATFULFILLMENTDATE_HU' = 
         cpsj.intrastatfulfillmentdate_hu, 
         'CUSTPACKINGSLIPJOUR_INVENTLOCATIONID' = 
         cpsj.inventlocationid, 
         'CUSTPACKINGSLIPJOUR_INVENTPROFILETYPE_RU' = 
         cpsj.inventprofiletype_ru, 
         'CUSTPACKINGSLIPJOUR_INVOICEACCOUNT' = 
            cpsj.invoiceaccount, 
         'CUSTPACKINGSLIPJOUR_INVOICEISSUEDUEDATE_W' = 
         cpsj.invoiceissueduedate_w, 
         'CUSTPACKINGSLIPJOUR_INVOICEPOSTALADDRESS' = 
         cpsj.invoicepostaladdress, 
         'CUSTPACKINGSLIPJOUR_INVOICINGNAME' = 
            cpsj.invoicingname, 
         'CUSTPACKINGSLIPJOUR_LANGUAGEID' = cpsj.languageid, 
         'CUSTPACKINGSLIPJOUR_LEDGERVOUCHER' = 
            cpsj.ledgervoucher, 
         'CUSTPACKINGSLIPJOUR_LISTCODE' = cpsj.listcode, 
         'CUSTPACKINGSLIPJOUR_NUMBERSEQUENCEGROUP' = 
         cpsj.numbersequencegroup, 
         'CUSTPACKINGSLIPJOUR_OFFSESSIONID_RU' = 
            cpsj.offsessionid_ru, 
         'CUSTPACKINGSLIPJOUR_ORDERACCOUNT' = 
            cpsj.orderaccount, 
         'CUSTPACKINGSLIPJOUR_PACKINGSLIPID' = 
            cpsj.packingslipid, 
         'CUSTPACKINGSLIPJOUR_PACKINGSLIPNUMBERINGCODE_LT' = 
         cpsj.packingslipnumberingcode_lt, 
         'CUSTPACKINGSLIPJOUR_PACKINGSLIPREGISTER_LT' = 
         cpsj.packingslipregister_lt, 
         'CUSTPACKINGSLIPJOUR_PACKINGSLIPSTATUS_LT' = 
         cpsj.packingslipstatus_lt, 
         'CUSTPACKINGSLIPJOUR_PARMID' = cpsj.parmid, 
         'CUSTPACKINGSLIPJOUR_PARTITION' = cpsj.partition, 
         'CUSTPACKINGSLIPJOUR_PDSCWQTY' = cpsj.pdscwqty, 
         'CUSTPACKINGSLIPJOUR_PRINTBLANKDATE_LT' = 
         cpsj.printblankdate_lt, 
         'CUSTPACKINGSLIPJOUR_PRINTED' = cpsj.printed, 
         'CUSTPACKINGSLIPJOUR_PRINTMGMTSITEID' = 
            cpsj.printmgmtsiteid, 
         'CUSTPACKINGSLIPJOUR_PURCHASEORDER' = 
            cpsj.purchaseorder, 
         'CUSTPACKINGSLIPJOUR_QTY' = cpsj.qty, 
         'CUSTPACKINGSLIPJOUR_REASONTABLEREF_BR' = 
         cpsj.reasontableref_br, 
         'CUSTPACKINGSLIPJOUR_RECID' = cpsj.recid, 
         'CUSTPACKINGSLIPJOUR_RECVERSION' = cpsj.recversion, 
         'CUSTPACKINGSLIPJOUR_REFNUM' = cpsj.refnum, 
         'CUSTPACKINGSLIPJOUR_RETURNITEMNUM' = 
            cpsj.returnitemnum, 
         'CUSTPACKINGSLIPJOUR_RETURNPACKINGSLIPID' = 
         cpsj.returnpackingslipid, 
         'CUSTPACKINGSLIPJOUR_SALESID' = cpsj.salesid, 
         'CUSTPACKINGSLIPJOUR_SALESTYPE' = cpsj.salestype, 
         'CUSTPACKINGSLIPJOUR_SHIPCARRIERACCOUNT' = 
         cpsj.shipcarrieraccount, 
         'CUSTPACKINGSLIPJOUR_SHIPCARRIERBLINDSHIPMENT' = 
         cpsj.shipcarrierblindshipment, 
         'CUSTPACKINGSLIPJOUR_SHIPCARRIERDELIVERYCONTACT' = 
         cpsj.shipcarrierdeliverycontact, 
         'CUSTPACKINGSLIPJOUR_SHIPCARRIEREMAIL' = 
         cpsj.shipcarrieremail, 
         'CUSTPACKINGSLIPJOUR_SHIPCARRIERID' = 
            cpsj.shipcarrierid, 
         'CUSTPACKINGSLIPJOUR_SHIPCARRIERPHONE' = 
         cpsj.shipcarrierphone, 
         'CUSTPACKINGSLIPJOUR_SOURCEDOCUMENTHEADER' = 
         cpsj.sourcedocumentheader, 
  'CUSTPACKINGSLIPJOUR_TRANSPORTATIONDELIVERYCONTRACTOR' = 
  cpsj.transportationdeliverycontractor, 
  'CUSTPACKINGSLIPJOUR_TRANSPORTATIONDELIVERYLOADER' = 
  cpsj.transportationdeliveryloader, 
  'CUSTPACKINGSLIPJOUR_TRANSPORTATIONDELIVERYOWNER' = 
  cpsj.transportationdeliveryowner, 
  'CUSTPACKINGSLIPJOUR_TRANSPORTATIONDOCUMENT' = 
  cpsj.transportationdocument, 
  'CUSTPACKINGSLIPJOUR_VOLUME' = cpsj.volume, 
  'CUSTPACKINGSLIPJOUR_WEIGHT' = cpsj.weight, 
  'CUSTPACKINGSLIPJOUR_WORKERSALESTAKER' = 
  cpsj.workersalestaker, 
  'CUSTPACKINGSLIPSALESLINK_CUSTOMERREF' = 
         cpssl.customerref, 
  'CUSTPACKINGSLIPSALESLINK_DATAAREAID' = 
  cpssl.dataareaid, 
  'CUSTPACKINGSLIPSALESLINK_DELIVERYDATE' = 
            cpssl.deliverydate, 
  'CUSTPACKINGSLIPSALESLINK_DELIVERYNAME' = 
  cpssl.deliveryname, 
  'CUSTPACKINGSLIPSALESLINK_DELIVERYPOSTALADDRESS' = 
  cpssl.deliverypostaladdress, 
  'CUSTPACKINGSLIPSALESLINK_INVOICEACCOUNT' = 
  cpssl.invoiceaccount, 
  'CUSTPACKINGSLIPSALESLINK_ORDERACCOUNT' = 
            cpssl.orderaccount, 
  'CUSTPACKINGSLIPSALESLINK_ORIGSALESID' = 
  cpssl.origsalesid, 
  'CUSTPACKINGSLIPSALESLINK_PACKINGSLIPID' = 
  cpssl.packingslipid, 
  'CUSTPACKINGSLIPSALESLINK_PARMID' = cpssl.parmid, 
  'CUSTPACKINGSLIPSALESLINK_PARTITION' = cpssl.partition, 
  'CUSTPACKINGSLIPSALESLINK_PURCHASEORDER' = 
  cpssl.purchaseorder, 
  'CUSTPACKINGSLIPSALESLINK_RECID' = cpssl.recid, 
  'CUSTPACKINGSLIPSALESLINK_RECVERSION' = cpssl.recversion, 
  'CUSTPACKINGSLIPSALESLINK_SALESID' = cpssl.salesid, 
  'CUSTPACKINGSLIPTRANS_AMOUNTCUR' = cpst.amountcur, 
  'CUSTPACKINGSLIPTRANS_COUNTRYREGIONOFSHIPMENT' = 
  cpst.countryregionofshipment, 
  'CUSTPACKINGSLIPTRANS_CREATEDDATETIME' = 
  cpst.createddatetime, 
  'CUSTPACKINGSLIPTRANS_CURRENCYCODE' = cpst.currencycode, 
  'CUSTPACKINGSLIPTRANS_DATAAREAID' = cpst.dataareaid, 
  'CUSTPACKINGSLIPTRANS_DEFAULTDIMENSION' = 
  cpst.defaultdimension, 
  'CUSTPACKINGSLIPTRANS_DEL_CREATEDTIME' = 
  cpst.del_createdtime, 
  'CUSTPACKINGSLIPTRANS_DELIVERYDATE' = cpst.deliverydate, 
  'CUSTPACKINGSLIPTRANS_DELIVERYPOSTALADDRESS' = 
  cpst.deliverypostaladdress, 
  'CUSTPACKINGSLIPTRANS_DELIVERYTYPE' = cpst.deliverytype, 
  'CUSTPACKINGSLIPTRANS_DLVTERM' = cpst.dlvterm, 
  'CUSTPACKINGSLIPTRANS_EXTERNALITEMID' = 
         cpst.externalitemid, 
  'CUSTPACKINGSLIPTRANS_FULLYMATCHED' = cpst.fullymatched, 
  'CUSTPACKINGSLIPTRANS_INTERCOMPANYINVENTTRANSID' = 
  cpst.intercompanyinventtransid, 
  'CUSTPACKINGSLIPTRANS_INTRASTATDISPATCHID' = 
  cpst.intrastatdispatchid, 
  'CUSTPACKINGSLIPTRANS_INTRASTATFULFILLMENTDATE_HU' = 
  cpst.intrastatfulfillmentdate_hu, 
  'CUSTPACKINGSLIPTRANS_INVENTDIMID' = cpst.inventdimid, 
  'CUSTPACKINGSLIPTRANS_INVENTQTY' = cpst.inventqty, 
  'CUSTPACKINGSLIPTRANS_INVENTREFID' = cpst.inventrefid, 
  'CUSTPACKINGSLIPTRANS_INVENTREFTRANSID' = 
  cpst.inventreftransid, 
  'CUSTPACKINGSLIPTRANS_INVENTREFTYPE' = cpst.inventreftype, 
  'CUSTPACKINGSLIPTRANS_INVENTTRANSID' = cpst.inventtransid, 
  'CUSTPACKINGSLIPTRANS_INVOICETRANSREFRECID' = 
  cpst.invoicetransrefrecid, 
  'CUSTPACKINGSLIPTRANS_ITEMCODEID' = cpst.itemcodeid, 
  'CUSTPACKINGSLIPTRANS_ITEMID' = cpst.itemid, 
  'CUSTPACKINGSLIPTRANS_LINEHEADER' = cpst.lineheader, 
  'CUSTPACKINGSLIPTRANS_LINENUM' = cpst.linenum, 
  'CUSTPACKINGSLIPTRANS_NAME' = cpst.NAME, 
  'CUSTPACKINGSLIPTRANS_NGPCODESTABLE_FR' = 
  cpst.ngpcodestable_fr, 
  'CUSTPACKINGSLIPTRANS_NUMBERSEQUENCEGROUP' = 
  cpst.numbersequencegroup, 
  'CUSTPACKINGSLIPTRANS_ORDERED' = cpst.ordered, 
  'CUSTPACKINGSLIPTRANS_ORIGCOUNTRYREGIONID' = 
  cpst.origcountryregionid, 
  'CUSTPACKINGSLIPTRANS_ORIGSALESID' = cpst.origsalesid, 
  'CUSTPACKINGSLIPTRANS_ORIGSTATEID' = cpst.origstateid, 
  'CUSTPACKINGSLIPTRANS_PACKINGSLIPID' = cpst.packingslipid, 
  'CUSTPACKINGSLIPTRANS_PARTITION' = cpst.partition, 
  'CUSTPACKINGSLIPTRANS_PDSCWQTY' = cpst.pdscwqty, 
  'CUSTPACKINGSLIPTRANS_PDSCWREMAIN' = cpst.pdscwremain, 
  'CUSTPACKINGSLIPTRANS_PORT' = cpst.port, 
  'CUSTPACKINGSLIPTRANS_PRICEUNIT' = cpst.priceunit, 
  'CUSTPACKINGSLIPTRANS_QTY' = cpst.qty, 
  'CUSTPACKINGSLIPTRANS_RECID' = cpst.recid, 
  'CUSTPACKINGSLIPTRANS_RECVERSION' = cpst.recversion, 
  'CUSTPACKINGSLIPTRANS_REMAIN' = cpst.remain, 
  'CUSTPACKINGSLIPTRANS_REMAININVENT' = cpst.remaininvent, 
  'CUSTPACKINGSLIPTRANS_SALESCATEGORY' = cpst.salescategory, 
  'CUSTPACKINGSLIPTRANS_SALESGROUP' = cpst.salesgroup, 
  'CUSTPACKINGSLIPTRANS_SALESID' = cpst.salesid, 
  'CUSTPACKINGSLIPTRANS_SALESLINESHIPPINGDATECONFIRMED' = 
  cpst.saleslineshippingdateconfirmed, 
  'CUSTPACKINGSLIPTRANS_SALESLINESHIPPINGDATEREQUESTED' = 
  cpst.saleslineshippingdaterequested, 
  'CUSTPACKINGSLIPTRANS_SALESUNIT' = cpst.salesunit, 
  'CUSTPACKINGSLIPTRANS_SCRAP' = cpst.scrap, 
  'CUSTPACKINGSLIPTRANS_SOURCEDOCUMENTLINE' = 
  cpst.sourcedocumentline, 
  'CUSTPACKINGSLIPTRANS_STATISTICVALUE_LT' = 
  cpst.statisticvalue_lt, 
  'CUSTPACKINGSLIPTRANS_STATPROCID' = cpst.statprocid, 
  'CUSTPACKINGSLIPTRANS_STATVALUEMST' = cpst.statvaluemst, 
  'CUSTPACKINGSLIPTRANS_STOCKEDPRODUCT' = 
         cpst.stockedproduct, 
  'CUSTPACKINGSLIPTRANS_TRANSACTIONCODE' = 
  cpst.transactioncode, 
  'CUSTPACKINGSLIPTRANS_TRANSPORT' = cpst.transport, 
  'CUSTPACKINGSLIPTRANS_VALUEMST' = cpst.valuemst, 
  'CUSTPACKINGSLIPTRANS_WEIGHT' = cpst.weight, 
  '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, 
  'ECORESCATEGORY_CATEGORYHIERARCHY' = 
         erc.categoryhierarchy, 
  'ECORESCATEGORY_CHANGESTATUS' = 
         erc.changestatus, 
  'ECORESCATEGORY_CODE' = erc.code, 
  'ECORESCATEGORY_CREATEDBY' = erc.createdby, 
  'ECORESCATEGORY_CREATEDDATETIME' = erc.createddatetime, 
  'ECORESCATEGORY_DEFAULTPROJECTGLOBALCATEGORY' = 
  erc.defaultprojectglobalcategory, 
  'ECORESCATEGORY_DEFAULTTHRESHOLD_PSN' = 
  erc.defaultthreshold_psn, 
  'ECORESCATEGORY_INSTANCERELATIONTYPE' = 
  erc.instancerelationtype, 
  'ECORESCATEGORY_ISACTIVE' = erc.isactive, 
  'ECORESCATEGORY_ISCATEGORYATTRIBUTESINHERITED' = 
  erc.iscategoryattributesinherited, 
  'ECORESCATEGORY_ISTANGIBLE' = erc.istangible, 
  'ECORESCATEGORY_LEVEL_' = erc.level_, 
  'ECORESCATEGORY_MODIFIEDBY' = erc.modifiedby, 
  'ECORESCATEGORY_MODIFIEDDATETIME' = erc.modifieddatetime, 
  'ECORESCATEGORY_NAME' = erc.NAME, 
  'ECORESCATEGORY_NESTEDSETLEFT' = erc.nestedsetleft, 
  'ECORESCATEGORY_NESTEDSETRIGHT' = erc.nestedsetright, 
  'ECORESCATEGORY_PARENTCATEGORY' = erc.parentcategory, 
  'ECORESCATEGORY_PARTITION' = erc.partition, 
  'ECORESCATEGORY_PKWIUCODE' = erc.pkwiucode, 
  'ECORESCATEGORY_RECID' = erc.recid, 
  'ECORESCATEGORY_RECVERSION' = erc.recversion, 
  'ECORESCATEGORY_RELATIONTYPE' = erc.relationtype, 
  'ECORESCATEGORY_REUSEENABLED' = erc.reuseenabled 
  FROM   custpackingslipjour cpsj 
         INNER JOIN custpackingslipsaleslink cpssl 
                 ON cpssl.salesid = cpsj.salesid 
                    AND cpssl.packingslipid = 
                        cpsj.packingslipid 
                    AND cpssl.deliverydate = 
                        cpsj.deliverydate 
                    AND cpssl.dataareaid = cpsj.dataareaid 
                    AND cpssl.partition = cpsj.partition 
         INNER JOIN custpackingsliptrans cpst 
                 ON cpst.salesid = cpsj.salesid 
                    AND cpst.packingslipid = 
                        cpsj.packingslipid 
                    AND cpst.deliverydate = 
                        cpsj.deliverydate 
                    AND cpst.dataareaid = cpsj.dataareaid 
                    AND cpst.partition = cpsj.partition 
         INNER JOIN inventdim id 
                 ON id.inventdimid = cpst.inventdimid 
                    AND id.dataareaid = cpst.dataareaid 
                    AND id.partition = cpst.partition 
         LEFT JOIN ecorescategory erc 
                ON erc.recid = cpst.salescategory 
                   AND erc.partition = cpsj.partition 

go