Purchase Order Packing Slip / Product Receipt

CREATE VIEW [dbo].[vw_purchPurchPackingSlip] 
AS 
  SELECT 'VENDPACKINGSLIPJOUR_CONSIGNEEACCOUNT_RU' = 
         vpsj.consigneeaccount_ru, 
         'VENDPACKINGSLIPJOUR_CONSIGNORACCOUNT_RU' = 
         vpsj.consignoraccount_ru, 
         'VENDPACKINGSLIPJOUR_COUNTRYREGIONID' = 
            vpsj.countryregionid, 
         'VENDPACKINGSLIPJOUR_CURRENCYCODE_W' = 
            vpsj.currencycode_w, 
         'VENDPACKINGSLIPJOUR_DATAAREAID' = vpsj.dataareaid, 
         'VENDPACKINGSLIPJOUR_DEFAULTDIMENSION' = 
         vpsj.defaultdimension, 
         'VENDPACKINGSLIPJOUR_DELIVERYDATE' = 
            vpsj.deliverydate, 
         'VENDPACKINGSLIPJOUR_DELIVERYNAME' = 
            vpsj.deliveryname, 
         'VENDPACKINGSLIPJOUR_DELIVERYPOSTALADDRESS' = 
         vpsj.deliverypostaladdress, 
         'VENDPACKINGSLIPJOUR_DELIVERYTYPE' = 
            vpsj.deliverytype, 
         'VENDPACKINGSLIPJOUR_DLVMODE' = vpsj.dlvmode, 
         'VENDPACKINGSLIPJOUR_DLVTERM' = vpsj.dlvterm, 
         'VENDPACKINGSLIPJOUR_DOCUMENTDATE' = 
            vpsj.documentdate, 
         'VENDPACKINGSLIPJOUR_ENDDISC_RU' = vpsj.enddisc_ru, 
         'VENDPACKINGSLIPJOUR_FREIGHTSLIPNUM' = 
            vpsj.freightslipnum, 
         'VENDPACKINGSLIPJOUR_FREIGHTSLIPTYPE' = 
            vpsj.freightsliptype, 
         'VENDPACKINGSLIPJOUR_GRNNUMBER_IN' = 
            vpsj.grnnumber_in, 
         'VENDPACKINGSLIPJOUR_INTERCOMPANYCOMPANYID' = 
         vpsj.intercompanycompanyid, 
         'VENDPACKINGSLIPJOUR_INTERCOMPANYPOSTED' = 
         vpsj.intercompanyposted, 
         'VENDPACKINGSLIPJOUR_INTERCOMPANYSALESID' = 
         vpsj.intercompanysalesid, 
         'VENDPACKINGSLIPJOUR_INTRASTATDISPATCH' = 
         vpsj.intrastatdispatch, 
         'VENDPACKINGSLIPJOUR_INTRASTATFULFILLMENTDATE_HU' = 
         vpsj.intrastatfulfillmentdate_hu, 
         'VENDPACKINGSLIPJOUR_INVENTPROFILETYPE_RU' = 
         vpsj.inventprofiletype_ru, 
         'VENDPACKINGSLIPJOUR_INVOICEACCOUNT' = 
            vpsj.invoiceaccount, 
         'VENDPACKINGSLIPJOUR_INVOICEISSUEDUEDATE_W' = 
         vpsj.invoiceissueduedate_w, 
         'VENDPACKINGSLIPJOUR_ITEMBUYERGROUPID' = 
         vpsj.itembuyergroupid, 
         'VENDPACKINGSLIPJOUR_LANGUAGEID' = vpsj.languageid, 
         'VENDPACKINGSLIPJOUR_NUMBERSEQUENCEGROUP' = 
         vpsj.numbersequencegroup, 
         'VENDPACKINGSLIPJOUR_OFFSESSIONID_RU' = 
            vpsj.offsessionid_ru, 
         'VENDPACKINGSLIPJOUR_ORDERACCOUNT' = 
            vpsj.orderaccount, 
         'VENDPACKINGSLIPJOUR_ORDERBALANCE_RU' = 
            vpsj.orderbalance_ru, 
         'VENDPACKINGSLIPJOUR_PACKINGSLIPID' = 
            vpsj.packingslipid, 
         'VENDPACKINGSLIPJOUR_PACKINGSLIPNUMBERINGCODE_LT' = 
         vpsj.packingslipnumberingcode_lt, 
         'VENDPACKINGSLIPJOUR_PACKINGSLIPREGISTER_LT' = 
         vpsj.packingslipregister_lt, 
         'VENDPACKINGSLIPJOUR_PACKINGSLIPSTATUS_LT' = 
         vpsj.packingslipstatus_lt, 
         'VENDPACKINGSLIPJOUR_PARTITION' = vpsj.partition, 
         'VENDPACKINGSLIPJOUR_PURCHASETYPE' = 
            vpsj.purchasetype, 
         'VENDPACKINGSLIPJOUR_PURCHID' = vpsj.purchid, 
         'VENDPACKINGSLIPJOUR_RECEIPTLISTDEVIATIONTYPE_RU' = 
         vpsj.receiptlistdeviationtype_ru, 
         'VENDPACKINGSLIPJOUR_RECID' = vpsj.recid, 
         'VENDPACKINGSLIPJOUR_RECVERSION' = vpsj.recversion, 
         'VENDPACKINGSLIPJOUR_REQATTENTION' = 
            vpsj.reqattention, 
         'VENDPACKINGSLIPJOUR_REQUESTER' = vpsj.requester, 
         'VENDPACKINGSLIPJOUR_RETURNITEMNUM' = 
            vpsj.returnitemnum, 
         'VENDPACKINGSLIPJOUR_SOURCEDOCUMENTHEADER' = 
         vpsj.sourcedocumentheader, 
         'VENDPACKINGSLIPTRANS_ACCEPTEDQTY_IN' = 
            vpst.acceptedqty_in, 
         'VENDPACKINGSLIPTRANS_ACCOUNTINGDATE' = 
            vpst.accountingdate, 
         'VENDPACKINGSLIPTRANS_COSTLEDGERVOUCHER' = 
         vpst.costledgervoucher, 
         'VENDPACKINGSLIPTRANS_CURRENCYCODE_W' = 
            vpst.currencycode_w, 
         'VENDPACKINGSLIPTRANS_DATAAREAID' = 
         vpst.dataareaid, 
'VENDPACKINGSLIPTRANS_DEFAULTDIMENSION' = 
vpst.defaultdimension, 
'VENDPACKINGSLIPTRANS_DELIVERYDATE' = 
   vpst.deliverydate, 
'VENDPACKINGSLIPTRANS_DESTCOUNTRYREGIONID' = 
vpst.destcountryregionid, 
'VENDPACKINGSLIPTRANS_DESTCOUNTY' = vpst.destcounty, 
'VENDPACKINGSLIPTRANS_DESTSTATE' = vpst.deststate, 
'VENDPACKINGSLIPTRANS_DEVIATIONQTY_RU' = 
vpst.deviationqty_ru, 
'VENDPACKINGSLIPTRANS_EXCISEAMOUNT_RU' = 
vpst.exciseamount_ru, 
'VENDPACKINGSLIPTRANS_EXCISEVALUE_RU' = 
   vpst.excisevalue_ru, 
'VENDPACKINGSLIPTRANS_EXTERNALITEMID' = 
   vpst.externalitemid, 
'VENDPACKINGSLIPTRANS_FULLYMATCHED' = 
   vpst.fullymatched, 
'VENDPACKINGSLIPTRANS_INTERCOMPANYINVENTTRANSID' = 
vpst.intercompanyinventtransid, 
'VENDPACKINGSLIPTRANS_INTRASTATDISPATCHID' = 
vpst.intrastatdispatchid, 
'VENDPACKINGSLIPTRANS_INTRASTATFULFILLMENTDATE_HU' = 
vpst.intrastatfulfillmentdate_hu, 
'VENDPACKINGSLIPTRANS_INVENTDATE' = vpst.inventdate, 
'VENDPACKINGSLIPTRANS_INVENTDIMID' = 
   vpst.inventdimid, 
'VENDPACKINGSLIPTRANS_INVENTQTY' = 
   vpst.inventqty, 
'VENDPACKINGSLIPTRANS_INVENTREFID' = 
   vpst.inventrefid, 
'VENDPACKINGSLIPTRANS_INVENTREFTRANSID' 
   = 
vpst.inventreftransid, 
'VENDPACKINGSLIPTRANS_INVENTREFTYPE' = 
   vpst.inventreftype, 
'VENDPACKINGSLIPTRANS_INVENTTRANSID' = 
   vpst.inventtransid, 
'VENDPACKINGSLIPTRANS_INVOICETRANSREFRECID' = 
vpst.invoicetransrefrecid, 
'VENDPACKINGSLIPTRANS_ITEMCODEID' = vpst.itemcodeid, 
'VENDPACKINGSLIPTRANS_ITEMID' = vpst.itemid, 
'VENDPACKINGSLIPTRANS_LINEAMOUNT_W' = 
   vpst.lineamount_w, 
'VENDPACKINGSLIPTRANS_LINENUM' = vpst.linenum, 
'VENDPACKINGSLIPTRANS_NAME' = vpst.NAME, 
'VENDPACKINGSLIPTRANS_NGPCODESTABLE_FR' = 
vpst.ngpcodestable_fr, 
'VENDPACKINGSLIPTRANS_NUMBERSEQUENCEGROUP' = 
vpst.numbersequencegroup, 
'VENDPACKINGSLIPTRANS_ORDERED' = vpst.ordered, 
'VENDPACKINGSLIPTRANS_ORIGCOUNTRYREGIONID' = 
vpst.origcountryregionid, 
'VENDPACKINGSLIPTRANS_ORIGPURCHID' = 
   vpst.origpurchid, 
'VENDPACKINGSLIPTRANS_ORIGSTATEID' = 
vpst.origstateid, 
'VENDPACKINGSLIPTRANS_PACKINGSLIPID' = 
   vpst.packingslipid, 
'VENDPACKINGSLIPTRANS_PARTITION' = vpst.partition, 
'VENDPACKINGSLIPTRANS_PDSCWORDERED' = 
   vpst.pdscwordered, 
'VENDPACKINGSLIPTRANS_PDSCWQTY' = vpst.pdscwqty, 
'VENDPACKINGSLIPTRANS_PDSCWREMAIN' = 
   vpst.pdscwremain, 
'VENDPACKINGSLIPTRANS_PORT' = 
   vpst.port, 
'VENDPACKINGSLIPTRANS_PRICEUNIT' = 
   vpst.priceunit, 
'VENDPACKINGSLIPTRANS_PROCUREMENTCATEGORY' = 
vpst.procurementcategory, 
'VENDPACKINGSLIPTRANS_PURCHASELINEEXPECTEDDELIVERYDATE' = 
vpst.purchaselineexpecteddeliverydate, 
'VENDPACKINGSLIPTRANS_PURCHASELINELINENUMBER' = 
vpst.purchaselinelinenumber, 
'VENDPACKINGSLIPTRANS_PURCHUNIT' = vpst.purchunit, 
'VENDPACKINGSLIPTRANS_QTY' = vpst.qty, 
'VENDPACKINGSLIPTRANS_REASONTABLEREF' = vpst.reasontableref, 
'VENDPACKINGSLIPTRANS_RECEIVEDQTY_IN' = vpst.receivedqty_in, 
'VENDPACKINGSLIPTRANS_RECID' = vpst.recid, 
'VENDPACKINGSLIPTRANS_RECVERSION' = vpst.recversion, 
'VENDPACKINGSLIPTRANS_REJECTEDQTY_IN' = vpst.rejectedqty_in, 
'VENDPACKINGSLIPTRANS_REMAIN' = vpst.remain, 
'VENDPACKINGSLIPTRANS_REMAININVENT' = vpst.remaininvent, 
'VENDPACKINGSLIPTRANS_RETURNACTIONID' = vpst.returnactionid, 
'VENDPACKINGSLIPTRANS_SOURCEDOCUMENTLINE' = 
vpst.sourcedocumentline, 
'VENDPACKINGSLIPTRANS_STATISTICVALUE_LT' = 
vpst.statisticvalue_lt, 
'VENDPACKINGSLIPTRANS_STATPROCID' = vpst.statprocid, 
'VENDPACKINGSLIPTRANS_STOCKEDPRODUCT' = vpst.stockedproduct, 
'VENDPACKINGSLIPTRANS_TAXAMOUNT_RU' = vpst.taxamount_ru, 
'VENDPACKINGSLIPTRANS_TAXGROUP_RU' = vpst.taxgroup_ru, 
'VENDPACKINGSLIPTRANS_TAXITEMGROUP_RU' = 
vpst.taxitemgroup_ru, 
'VENDPACKINGSLIPTRANS_TRANSACTIONCODE' = 
vpst.transactioncode, 
'VENDPACKINGSLIPTRANS_TRANSPORT' = vpst.transport, 
'VENDPACKINGSLIPTRANS_VALUEMST' = vpst.valuemst, 
'VENDPACKINGSLIPTRANS_VATAMOUNT_RU' = vpst.vatamount_ru, 
'VENDPACKINGSLIPTRANS_VATVALUE_RU' = vpst.vatvalue_ru, 
'VENDPACKINGSLIPTRANS_VENDPACKINGSLIPJOUR' = 
vpst.vendpackingslipjour, 
'VENDPACKINGSLIPTRANS_WEIGHT' = vpst.weight, 
'VENDPACKINGSLIPTRANS_WORKERPURCHASER' = 
vpst.workerpurchaser, 
'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   vendpackingslipjour vpsj 
INNER JOIN vendpackingsliptrans vpst 
        ON vpst.vendpackingslipjour = vpsj.recid 
           AND vpst.dataareaid = vpsj.dataareaid 
           AND vpst.partition = vpsj.partition 
INNER JOIN inventdim id 
        ON id.inventdimid = vpst.inventdimid 
           AND id.dataareaid = vpsj.dataareaid 
           AND id.partition = vpsj.partition 
LEFT JOIN ecorescategory erc 
       ON erc.recid = vpst.procurementcategory 
          AND erc.partition = vpsj.partition 

go