后台取物料的实时单价sql

//根据某个期间和采购入库和库存余额表取实时单价 

select r.storNum  storNum,r.storFname storName,  r.matNum matNum,r.matName matName, r.nowPrice nowPrice

from

(

select  res.storNum  storNum, res.storFname storFname,  res.matNum  matNum,res.matName matName

,

case when sum(res.purQty+res.monthBeginQty) >0 then  (sum(res.purAmt)+ sum(res.monthBeginAmt) )/(sum(res.purQty)+sum(res.monthBeginQty))  else 0 end  nowPrice,0  usedQty from (

SELECT  stor.fnumber  storNum, stor.fname_l2  storFname,

mat.fnumber  matNum ,mat.fname_l2   matName

,   SUM(inv.FperiodBeginQty)   monthBeginQty , SUM(inv.FPERIODBEGINACTUALCOST) monthBeginAmt,0 purQty,

0 purAmt

FROM  T_IM_INVENTORYBALANCE inv

left join  t_bd_material mat  on inv.fmaterialid =mat.fid

left join T_IM_STORESTATE  sta  on sta.fid=inv.fstorestatusid

left join T_ORG_STORAGE stor on stor.fid=inv.FSTORAGEORGUNITID

WHERE

stor.fnumber='0104122' and

inv.FPERIOD = '7' and inv.fyear='2023'

and sta.fnumber='1'  //库存状态为可用

//and mat.fnumber='03.03.01.26'

GROUP BY    stor.fnumber, stor.fname_l2, mat.fnumber ,mat.fname_l2

union all

select stor.fnumber, stor.fname_l2,   mat.fnumber  matNum ,mat.fname_l2   matName,  0  monthBeginQty ,0 monthBeginAmt,sum(entry.fqty)  purQty,

sum(entry.famount)  purAmt  from  T_IM_PURINWAREHSBILL  bill

left join T_IM_PURINWAREHSENTRY entry on entry.fparentid=bill.fid

left join t_bd_material mat on mat.fid=entry.fmaterialid

left join T_ORG_STORAGE stor on stor.fid=bill.FSTORAGEORGUNITID

where bill.fbaseStatus=4   // and  mat.fnumber='03.03.01.26'

and bill.fbizdate >=to_date('2023-06-26')  and  bill.fbizdate < to_date('2023-07-25')

and  stor.fnumber='0104122'

group  BY    mat.fnumber ,mat.fname_l2,stor.fname_l2,stor.fnumber

) res

group by  res.matNum ,res.matName, res.storNum  , res.storFname

)

r