2012: PO Invoice PPV

This post is very similar to this post but is for 2012 with a few columns removed for simplicity sake.

declare	@dtmDateFrom date = '2010-01-01',
		@dtmDateTo date = '2013-01-01',
		@varBeginVendGroup varchar(500) = '',
		@varEndVendGroup varchar(500) = '',
		@varBeginVendAccount varchar(20) = '',
		@varEndVendAccount varchar(20) = '',
		@varBeginItemId varchar(20) = '',
		@varEnditemId varchar(20) = '',
		@varSiteId	varchar(3) = '',
		@varDataAreaId varchar(4) = 'ceu'


select
	@varBeginVendGroup = isnull(nullif(@varBeginVendGroup, 'all'), ''),
	@varEndVendGroup = isnull(nullif(@varEndVendGroup, 'all'), ''),
	@varBeginVendAccount = isnull(nullif(@varBeginVendAccount, 'all'), ''),
	@varEndVendAccount = isnull(nullif(@varEndVendAccount, 'all'), ''),
	@varBeginItemId = isnull(nullif(@varBeginItemId, 'all'), ''),	
	@varEnditemId = isnull(nullif(@varEnditemId, 'all'), ''),
	@varSiteId = isnull(nullif(@varSiteId, 'all'), ''),
	@varDataAreaId = isnull(nullif(@varDataAreaId, ''), 'abcd')

-- Calculate PPV at InventTrans
-- (it.Qty / vit.Qty) == percent of invoice line
-- (it.Qty / vpst.Qty) == percent of packing slip line
select
	pl.InventTransId,
	'InvoiceDate' = convert(date, vit.InvoiceDate),
	vit.InvoiceId,
	'InvVoucher' = vij.LedgerVoucher,
	'InvValue' = vit.LineAmountMst * (it.Qty / isnull(nullif(vit.Qty,0),1)),
	'RecDate' = vpst.DeliveryDate,
	vpst.PackingSlipId,
	'RecVoucher' = vpst.costLedgerVoucher,
	'RecValue' = vpst.ValueMst * (it.Qty / isnull(nullif(vpst.Qty,0),1)),
	pl.PurchId,
	--'ItemGroup' = i.ItemGroupId,
	pl.ItemId,
	--'ItemName' = i.ItemName,
	it.Qty,
	'PPV' = (vit.LineAmountMst + isnull(null, 0)) * (it.Qty / isnull(nullif(vit.Qty,0),1)) - vpst.ValueMst * (it.Qty / isnull(nullif(vpst.Qty,0),1)),
	'VendGroup' = isnull(v.VendGroup, ''),
	'VendAccount' = isnull(vpsj.OrderAccount, ''),
	--'VendName' = v.Name,
	'SiteId' = id.inventLocationId
into #PPV
from PurchLine pl
join inventTransOrigin ito
	on ito.inventTransId = pl.inventTransId
	and ito.dataAreaid = pl.dataAreaId
join InventTrans it
	on it.DataAreaId = pl.DataAreaId
	and it.inventTransOrigin = ito.recid
join InventTable i
	on i.DataAreaId = it.DataAreaId
	and i.ItemId = it.ItemId
	and i.ItemType in (0,1) -- Item, BOM
join VendInvoiceTrans vit
	on vit.DataAreaId = it.DataAreaId
	and vit.InventTransId = ito.InventTransId
	and vit.InvoiceId = it.InvoiceId
	and vit.InvoiceDate = it.DateFinancial
join VendInvoiceJour vij
	on vij.DataAreaId = vit.DataAreaId
	and vij.InternalInvoiceId = vit.InternalInvoiceId
join VendPackingSlipTrans vpst
	on vpst.DataAreaId = it.DataAreaId
	and vpst.InventTransId = ito.InventTransId
	and vpst.PackingSlipId = it.PackingSlipId
	and vpst.DeliveryDate = it.DatePhysical
join VendPackingSlipJour vpsj
	on vpsj.DataAreaId = vpst.DataAreaId
	and vpsj.PackingSlipId = vpst.PackingSlipId
join inventDim id
	on id.inventDimId = vpst.inventDimId
	and id.dataareaid = vpsj.dataareaid
join ecoResProduct erp
	on erp.recid = i.Product
	--and erp.dataAreaId = i.dataareaid
join ecoResProductTranslation erpt
	on erpt.product = erp.recid
	--and ertp.dataareaid = i.dataareaid
join VendTable v
	on v.DataAreaId = vpsj.DataAreaId
	and v.AccountNum = vpsj.OrderAccount
--outer apply (
--	select 'MiscPPV' = sum(mt.Value)
--	from MarkupTrans mt
--	join MarkupTable m
--		on m.DataAreaId = mt.DataAreaId
--		and m.ModuleType = mt.ModuleType
--		and m.MarkupCode = mt.MarkupCode
--		--and m.CustAccount like '75%'
--	join LedgerTable l
--		on l.DataAreaId = m.DataAreaId
--		and l.AccountNum = m.CustAccount
--	join LedgerAccountCategory lac
--		on lac.DataAreaId = m.DataAreaId
--		and lac.AccountCategoryRef = l.AccountCategoryRef
--		and lac.AccountCategory = 'PPV'
--	where mt.DataAreaId = vit.DataAreaId
--		and mt.TransRecId = vit.RecId
--		and mt.TransTableId = 492 -- VendInvoiceTrans
--) mt
where pl.DataAreaId = @varDataAreaId
	and @varBeginVendGroup <= v.VendGroup
	and (@varEndVendGroup = '' or @varEndVendGroup >= v.VendGroup)
	and @varBeginVendAccount <= vpsj.OrderAccount
	and (@varEndVendAccount = '' or @varEndVendAccount >= vpsj.OrderAccount)
	and @varBeginItemId <= pl.ItemId
	and (@varEndItemId = '' or @varEndItemId >= pl.ItemId)
	--and @varSiteId in ('', vpst.Dimension, vpsj.Dimension)
	and vit.InvoiceDate between @dtmDateFrom and @dtmDateTo

-- Sum it up by invoice and item
select
	InvoiceDate,
	InvoiceId,
	InvVoucher,
	PurchId,
	--ItemGroup,
	ItemId,
	--ItemName,
	VendGroup,
	VendAccount,
	--VendName,
	SiteId,
	'PurchQty' = min(pl.PurchQty),
	'InvQty' = sum(Qty),
	'InvValue' = sum(InvValue),
	'RecValue' = sum(RecValue),
	'PPV' = sum(PPV) + min(isnull(ictv.CostTransPPV, 0))
from #PPV ppv
outer apply (
	select 'PurchQty' = sum(pl.QtyOrdered)
	from PurchLine pl
	where pl.DataAreaId = @varDataAreaId
		and pl.PurchId = ppv.PurchId
		and pl.ItemId = ppv.ItemId
) pl
outer apply (
	select 'CostTransPPV' = sum(ictv.CostAmountPosted)
	from InventCostTrans ict
	join InventCostTransVariance ictv
		on ictv.DataAreaId = ict.DataAreaId
		and ictv.InventCostTransRefRecId = ict.RecId
	where ict.DataAreaId = @varDataAreaId
		and ict.Voucher = ppv.InvVoucher
		and ict.InventTransId = ppv.InventTransId
) ictv
group by
	InvoiceDate,
	InvoiceId,
	InvVoucher,
	PurchId,
	--ItemGroup,
	ItemId,
	--ItemName,
	VendGroup,
	VendAccount,
	--VendName,
	SiteId

drop table #PPV