U9C的數據查詢視圖Sql

if object_id('TEMPDB..#priceTable') is not null
begin
drop table #priceTable
endcreate 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.PurchaseOrderwhere 1=1 
--and
--purchaseOrder.DocNo='20PO2405120029'
--poline.ItemInfo_ItemCode='4604-00128'
group bypoline.FinallyPriceTC,
poline.ItemInfo_ItemID ,
purchaseOrder.DocNo,poline.CreatedOnorder 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 whqleft 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.idleft join UBF_MD_Class ec on ec.Local_ID=evtrl.ExtEnumType and evtrl.ExtEnumTypeUID=ec.idwhere 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.idleft join UBF_MD_Class ec on ec.Local_ID=evtrl.ExtEnumType and evtrl.ExtEnumTypeUID=ec.idwhere ec.FullName='UFIDA.U9.CBO.SCM.Item.ItemTypeAttributeEnum') as ItemTypeAttributeEnum on ItemTypeAttributeEnum.EValue=itemMaster.ItemFormAttribute
left join #priceTable pricetable on pricetable.itemid=whq.ItemInfo_ItemIDwhere     whq.StoreMainQty>0 
--and  org.Code='20'  
--and  whq.ItemInfo_ItemCode='4604-00128' order by pricetable.polineCreatedOn desc