Let's walk through creating a standard Build VM
You are here
Purchase Order Receipts List
Purchase Order Receipts List
Submitted by Nathan Clouse on 09/14/15.
Provides common info for Purchase Order Receipts lists.
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