Purchase Order Receipts List

CREATE VIEW [dbo].[vw_purchPurchReceiptList] 
AS 
  SELECT 'VENDRECEIPTSLISTJOUR_DATAAREAID' = 
         vrlj.dataareaid, 
         'VENDRECEIPTSLISTJOUR_DELIVERYDATE' = 
            vrlj.deliverydate, 
         'VENDRECEIPTSLISTJOUR_DELIVERYNAME' = 
            vrlj.deliveryname, 
         'VENDRECEIPTSLISTJOUR_DELIVERYPOSTALADDRESS' = 
         vrlj.deliverypostaladdress, 
         'VENDRECEIPTSLISTJOUR_DLVMODE' = vrlj.dlvmode, 
         'VENDRECEIPTSLISTJOUR_DLVTERM' = vrlj.dlvterm, 
         'VENDRECEIPTSLISTJOUR_INTERCOMPANYPOSTED' = 
         vrlj.intercompanyposted, 
         'VENDRECEIPTSLISTJOUR_INVOICEACCOUNT' = 
            vrlj.invoiceaccount, 
         'VENDRECEIPTSLISTJOUR_LANGUAGEID' = 
         vrlj.languageid, 
         'VENDRECEIPTSLISTJOUR_PARMID' = 
         vrlj.parmid, 
         'VENDRECEIPTSLISTJOUR_PARTITION' = vrlj.partition, 
         'VENDRECEIPTSLISTJOUR_PURCHID' = vrlj.purchid, 
         'VENDRECEIPTSLISTJOUR_RECEIPTSLISTDATE' = 
         vrlj.receiptslistdate, 
         'VENDRECEIPTSLISTJOUR_RECEIPTSLISTID' = 
            vrlj.receiptslistid, 
         'VENDRECEIPTSLISTJOUR_RECID' = vrlj.recid, 
         'VENDRECEIPTSLISTJOUR_RECVERSION' = 
         vrlj.recversion, 
         'VENDRECEIPTSLISTJOUR_REQATTENTION' = 
            vrlj.reqattention, 
         'VENDRECEIPTSLISTJOUR_REQUESTER' = vrlj.requester, 
         'VENDRECEIPTSLISTJOUR_VENDACCOUNT' = 
            vrlj.vendaccount, 
         'VENDRECEIPTSLISTTRANS_DATAAREAID' = 
         vrlt.dataareaid, 
         'VENDRECEIPTSLISTTRANS_DELIVERYDATE' = 
            vrlt.deliverydate, 
         'VENDRECEIPTSLISTTRANS_INVENTDIMID' = 
            vrlt.inventdimid, 
         'VENDRECEIPTSLISTTRANS_INVENTQTY' = vrlt.inventqty, 
         'VENDRECEIPTSLISTTRANS_INVENTTRANSID' = 
            vrlt.inventtransid, 
         'VENDRECEIPTSLISTTRANS_ITEMID' = vrlt.itemid, 
         'VENDRECEIPTSLISTTRANS_LINENUM' = vrlt.linenum, 
         'VENDRECEIPTSLISTTRANS_NAME' = vrlt.NAME, 
         'VENDRECEIPTSLISTTRANS_ORIGPURCHID' = 
            vrlt.origpurchid, 
         'VENDRECEIPTSLISTTRANS_PARTITION' = vrlt.partition, 
         'VENDRECEIPTSLISTTRANS_PDSCWINVENTQTY' = 
            vrlt.pdscwinventqty, 
         'VENDRECEIPTSLISTTRANS_PROCUREMENTCATEGORY' = 
         vrlt.procurementcategory, 
         'VENDRECEIPTSLISTTRANS_PURCHASELINELINENUMBER' = 
         vrlt.purchaselinelinenumber, 
         'VENDRECEIPTSLISTTRANS_PURCHID' = vrlt.purchid, 
         'VENDRECEIPTSLISTTRANS_PURCHQTY' = vrlt.purchqty, 
         'VENDRECEIPTSLISTTRANS_PURCHUNIT' = vrlt.purchunit, 
         'VENDRECEIPTSLISTTRANS_REASONTABLEREF' = 
            vrlt.reasontableref, 
         'VENDRECEIPTSLISTTRANS_RECEIPTSLISTDATE' = 
         vrlt.receiptslistdate, 
         'VENDRECEIPTSLISTTRANS_RECEIPTSLISTID' = 
            vrlt.receiptslistid, 
         'VENDRECEIPTSLISTTRANS_RECID' = vrlt.recid, 
         'VENDRECEIPTSLISTTRANS_RECVERSION' = 
         vrlt.recversion, 
         'VENDRECEIPTSLISTTRANS_STOCKEDPRODUCT' = 
            vrlt.stockedproduct, 
         '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   vendreceiptslistjour vrlj 
         INNER JOIN vendreceiptslisttrans vrlt 
                 ON vrlt.purchid = vrlj.purchid 
                    AND vrlt.receiptslistid = 
                        vrlj.receiptslistid 
                    AND vrlt.receiptslistdate = 
                        vrlj.receiptslistdate 
                    AND vrlt.dataareaid = vrlj.dataareaid 
                    AND vrlt.partition = vrlj.partition 
         INNER JOIN inventdim id 
                 ON id.inventdimid = vrlt.inventdimid 
                    AND id.dataareaid = vrlj.dataareaid 
                    AND id.partition = vrlj.partition 
         LEFT JOIN ecorescategory erc 
                ON erc.recid = vrlt.procurementcategory 
                   AND erc.partition = vrlj.partition 

go