with CTEComm as
(
select CTComLi.commisionlineid, ctcomm.commisionfullnr
from
[wms].[commisionline] CTComLi
inner join [wms].[commision] CtComm on ctcomm.commisionid=CTComLi.commisionid
where
ctcomm.commisionid=@commisionidd
)
,
CTEOstatniacena as
(
select
ddl.itemID
,max(DDl.sysCorrectedItemPrice) maxprice
,sum(ddl.itemQuantity) sumkk
,min(doc.addDate) datmin
,case when doc.documentType = 'pw' then max(DDl.sysCorrectedItemPrice) else 0 end cenaPW
,case when doc.documentType in ('pzi','pz') and it.itemCodeTemplate <> 'de' then max(DDl.sysCorrectedItemPrice) else 0 end cenaPZiPZ
,case when doc.documentType in ('pzi','pz') and it.itemCodeTemplate ='de' then max(DDl.sysCorrectedItemPrice) else 0 end cenaPZiPZde
,case when doc.documentType = '*pzk' then max(DDl.additionalPrice) else 0 end cenaPZK
,count(ddl.itemID) over (partition by ddl.itemID) ilo
,doc.documentType
from
[document].[documentLine] DDl
inner join [document].[document] Doc on doc.documentID = ddl.documentID
inner join [wms].[item] it on it.itemid = ddl.itemid
inner join
(select ddll.itemid, max( ddll.addDate ) minDate
from [document].[documentLine] ddll
inner join [document].[document] Docl on docl.documentID = ddll.documentID
where docl.documentType in ('pz','pzi', 'pw','*pzk') and ddll.status>0 and ddll.correctedItemQuantity > 0
group by ddll.itemID ) midoc on midoc.itemID = ddl.itemID and midoc.minDate = ddl.addDate
where doc.documentType in ('pz','pzi', 'pw','*pzk') and ddl.status > 0 and ddl.correctedItemQuantity > 0
group by
ddl.itemID
,it.itemCodeTemplate
,doc.documentType
)
select
--p1.commisionID
--,p1.commisionLineID
p1.commisionfullnr
,ITp1.itemCode
,ITp1.itemName
--,p1.elementItemId
--,p1.elementItemName
--,sum(isnull(p1.structureQuantity,0)) SUMstructureQuantity
,sum(isnull(p1.structureCommisionQuantity,0)) [Il planowana]
--,sum(isnull(p1.itemQuantityPerItem,0)) sumitemQuantityPerItem
,sum(isnull(p1.qualnityDDli,0)) Rozpisano
,sum(isnull(p1.qualnityDDli,0)) - sum(isnull(p1.structureCommisionQuantity,0)) [Różnica]
,ITp1.itemLastPrice
,CTEOst.maxprice [Ost cena 2]
, cast(sum(isnull(p1.qualnityDDli,0)) - sum(isnull(p1.structureCommisionQuantity,0)) as decimal(18,2)) * cast( /*itp1.itemLastPrice*/ CTEOst.maxprice as decimal(18,2)) [Wartość]
, ABS(cast(sum(isnull(p1.qualnityDDli,0)) - sum(isnull(p1.structureCommisionQuantity,0)) as decimal(18,2)) * cast( /*itp1.itemLastPrice*/ CTEOst.maxprice as decimal(18,2))) [Wartość bezwzg]
from
(
select
req.commisionID
,Req.commisionLineID
,req.elementItemId
--,req.elementItemName
,req.structureQuantity
,req.structureCommisionQuantity
,req.itemQuantityPerItem
,null qualnityDDli
,CTcom.commisionfullnr
from
[MRP].[requisition] Req
inner join ctecomm CTcom on ctcom.commisionlineid=Req.commisionLineID
where Req.status >= 0
union all
select
ddli.commisionID
,ddli.commisionLineID
,ddli.itemID
--,null
,null
,null
,null
,ddli.correctedItemQuantity
,CTcom.commisionfullnr
from
[document].[documentLine] DDli
inner join [document].[document] dd on dd.documentid = ddli.documentid
inner join [wms].[item] ItDDli on ItDDli.itemID=ddli.itemID
inner join ctecomm CTcom on ctcom.commisionlineid=DDli.commisionLineID
where DDli.status>0
and dd.documentType in ('rw-p', 'rw', 'po', 'ps' )
)p1
inner join [wms].[item] ITp1 on ITp1.itemID=p1.elementItemId
left join CTEOstatniacena CTEOst on CTEOst.itemid = p1.elementItemId
where itp1.isdivision ='false'
group by
p1.commisionID
--,p1.commisionLineID
,ITp1.itemCode
,ITp1.itemName
,p1.commisionfullnr
,itp1.itemLastPrice
,CTEOst.maxprice
having sum(isnull(p1.structureCommisionQuantity,0)) - sum(isnull(p1.qualnityDDli,0)) <> 0
order by
ITp1.itemName
/*CGS-FORCE-SEND-EMPTY-PARAMETERS*/
