2012: PO Receipt PPV

The attached query seeks to recalculate PPV based on the current PO Line price and the receipt line price. This may not match the ledger exactly and may not work for all end users. This query is specific to one AX instance. That being said, the methodology may be useful to others and be useful in helping gain some insight into PPV.

declare	@dtmDateFrom date,
		@dtmDateTo date,
		@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, ''), 'ceu')


select  
		'DeliveryDate' = convert(date, vpst.DeliveryDate),
		vpst.PackingSlipId,
		ict.Voucher,
		'RecValue' = vpst.ValueMst * (ict.VarianceQty / isnull(nullif(vpst.Qty,0),1)),
		pl.PurchId,
		--'ItemGroup' = i.ItemGroupId,
		pl.ItemId,
		'ItemName' = erpt.name,
		'Qty' = ict.VarianceQty,
		'PPV' = ictv.CostAmountPosted,
		'VendGroup' = isnull(v.VendGroup, ''),
		'VendAccount' = isnull(vpsj.OrderAccount, ''),
		----'VendName' = dpv.n
		'SiteId' = id.inventLocationId
into #PPV
from PurchLine pl
join inventDim id
	on id.inventDimId = pl.inventDimId
	and id.dataAreaId = pl.DataAreaId
join InventCostTrans ict
	on ict.DataAreaId = pl.DataAreaId
	and ict.InventTransId = pl.InventTransId
join InventCostTransVariance ictv
	on ictv.DataAreaId = ict.DataAreaId
	and ictv.InventCostTransRefRecId = ict.RecId
join InventTable i
	on i.DataAreaId = ict.DataAreaId
	and i.ItemId = ict.ItemId
	and i.ItemType in (0,1) -- Item, BOM
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 VendPackingSlipTrans vpst
	on vpst.DataAreaId = ict.DataAreaId
	and vpst.InventTransId = ict.InventTransId
	and vpst.DeliveryDate = ict.TransDate
	and vpst.CostLedgerVoucher = ict.Voucher
join VendPackingSlipJour vpsj
	on vpsj.DataAreaId = vpst.DataAreaId
	and vpsj.recid = vpst.vendPackingSlipJour
	--and vpsj.InternalPackingSlipId = vpst.InternalPackingSlipId
	--and vpsj.LedgerVoucher = ict.Voucher
join VendTable v
	on v.DataAreaId = vpsj.DataAreaId
	and v.AccountNum = vpsj.OrderAccount
--join dirPartyView dpv
--	on dpv.party = v.party
	--and dpt.dataareaid = v.dataareaid
where pl.DataAreaId = @varDataAreaId
	and @varBeginVendGroup <= v.VendGroup
	and (@varEndVendGroup = '' or @varEndVendGroup >= v.VendGroup)
	and @varBeginVendAccount <= vpsj.InvoiceAccount
	and (@varEndVendAccount = '' or @varEndVendAccount >= vpsj.InvoiceAccount)
	and @varBeginItemId <= pl.ItemId
	and (@varEndItemId = '' or @varEndItemId >= pl.ItemId)
	and @varSiteId in ('', id.inventLocationId)
	and vpst.DeliveryDate between @dtmDateFrom and @dtmDateTo
  
select
	DeliveryDate,
	PackingSlipId,
	Voucher,
	PurchId,
	--ItemGroup,
	ItemId,
	ItemName,
	VendGroup,
	VendAccount,
	--VendName,
	SiteId,
	'PurchQty' = min(pl.PurchQty),
	'RecQty' = sum(Qty),
	'RecValue' = sum(RecValue),
	'PPV' = sum(PPV)
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
group by
	DeliveryDate,
	PackingSlipId,
	Voucher,
	PurchId,
	--ItemGroup,
	ItemId,
	ItemName,
	VendGroup,
	VendAccount,
	--VendName,
	SiteId
having sum(PPV) <> 0

drop table #PPV