单价、库存视图

if object_id('TEMPDB..#priceTable') is not null
begin
drop table #priceTable
end

create table #priceTable (polineCreatedOn date,price varchar(max),itemid varchar(max),purchaseOrderdocno varchar(max),)

insert into #priceTable
select   
max(poline.CreatedOn),
poline.FinallyPriceTC,
poline.ItemInfo_ItemID ,
purchaseOrder.DocNo
from
PM_POLine poline 
left join PM_PurchaseOrder purchaseOrder on purchaseOrder.id=poline.PurchaseOrder

where 1=1 
--and
--purchaseOrder.DocNo='20PO2405120029'
--poline.ItemInfo_ItemCode='4604-00128'
group by

poline.FinallyPriceTC,
poline.ItemInfo_ItemID ,
purchaseOrder.DocNo,
 poline.CreatedOn

order by poline.CreatedOn desc 



select  DISTINCT 
categoryTrl.Name 主分类,
itemMaster.Code  物料号,
itemMaster.SPECS 规格,
ItemTypeAttributeEnum.name 物料属性,
uomTrl.Name 库存主单位名称,
itemMaster.DescFlexField_PrivateDescSeg1 图号,
itemMaster.DescFlexField_PrivateDescSeg2  材质,
itemMaster.DescFlexField_PrivateDescSeg3  生产厂家,
itemMaster.name   物料名称,
seibanMaster.SeibanNO 番号,
wh.Code  存储地点编码,
bin.Code 库位,
binTrl.name  库位名称,
whTrl.Name 存储地点名称,
lotMaster.LotCode 批号,
orgTrl.name 组织名称,
org.Code 组织编码,
StorageTypeEnum.Name 存储类型,
project.Code 项目编码,
projectTrl.name 项目,
whq.ToRetStQtyCU     采购待退数量,
transferApply.DocNo 调拨申请单号 ,
transApplyLine.ApplyQty 调入在途量 ,
transferOut.DocNo 调出单号,
transOutLine.StoreUOMQty 调出在途数量 ,
whq.StoreMainQty 库存量,
pricetable.price 价格,
pricetable.polineCreatedOn 物料购买创建时间

from  InvTrans_WhQoh whq

left join CBO_ItemMaster itemMaster on itemMaster.id=whq.ItemInfo_ItemID
left join CBO_Wh wh on wh.id=whq.wh
left join CBO_Wh_Trl whTrl on whTrl.id=wh.id
left join Base_Organization org on org.id=whq.ItemOwnOrg
left join Base_Organization_Trl orgTrl on org.id=orgTrl.id
left join InvDoc_TransApplyLine transApplyLine  on transApplyLine.ItemInfo_ItemID=whq.ItemInfo_ItemID and transApplyLine.Status=2
left join InvDoc_TransferApply transferApply  on transferApply.id=transApplyLine.TransferApply and transferApply.Status=2
left join InvDoc_TransOutLine transOutLine  on transOutLine.ItemInfo_ItemID=whq.ItemInfo_ItemID and transOutLine.BusiClose=0
left join InvDoc_TransferOut  transferOut  on transferOut.id=transOutLine.transferOut 
left join (select ev.name,evtrl.code,evtrl.evalue from UBF_Sys_ExtEnumValue_Trl ev 
                                    left join ubf_sys_extenumvalue evtrl on evtrl.id=ev.id
                                    left join UBF_MD_Class ec on ec.Local_ID=evtrl.ExtEnumType and evtrl.ExtEnumTypeUID=ec.id
                                    where ec.FullName='UFIDA.U9.CBO.Enums.StorageTypeEnum') as StorageTypeEnum on StorageTypeEnum.EValue=whq.StorageType
left join CBO_Project project on project.id=whq.Project
left join CBO_Project_Trl projectTrl on projectTrl.id=project.id
left join CBO_Category  category on itemMaster.MainItemCategory=category.id
left join CBO_Category_Trl categoryTrl on categoryTrl.id=category.id
left join CBO_SeibanMaster  seibanMaster on seibanMaster.id=whq.SeiBan_EntityID
left join CBO_Bin  bin on bin.id=whq.BinInfo_Bin
left join CBO_Bin_Trl binTrl on binTrl.id=bin.id
left join Lot_LotMaster  lotMaster on lotMaster.id=whq.LotInfo_LotMaster_EntityID
left join Base_UOM uom on uom.id=itemMaster.InventoryUOM
left join Base_UOM_Trl uomTrl on uomTrl.id=uom.id
left join (select ev.name,evtrl.code,evtrl.evalue from UBF_Sys_ExtEnumValue_Trl ev 
                                    left join ubf_sys_extenumvalue evtrl on evtrl.id=ev.id
                                    left join UBF_MD_Class ec on ec.Local_ID=evtrl.ExtEnumType and evtrl.ExtEnumTypeUID=ec.id
                                    where ec.FullName='UFIDA.U9.CBO.SCM.Item.ItemTypeAttributeEnum') as ItemTypeAttributeEnum on ItemTypeAttributeEnum.EValue=itemMaster.ItemFormAttribute
left join #priceTable pricetable on pricetable.itemid=whq.ItemInfo_ItemID


where     whq.StoreMainQty>0 
--and  org.Code='20'  
--and  whq.ItemInfo_ItemCode='4604-00128' 

order by pricetable.polineCreatedOn desc