2009: 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) = 'abcd'


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')

select
	'DeliveryDate' = convert(date, vpst.DeliveryDate),
	vpst.PackingSlipId,
	ict.Voucher,
	'RecValue' = vpst.ValueMst * (ict.VarianceQty / vpst.Qty),
	pl.PurchId,
	'ItemGroup' = i.ItemGroupId,
	pl.ItemId,
	'ItemName' = i.ItemName,
	'Qty' = ict.VarianceQty,
	'PPV' = ictv.CostAmountPosted,
	'VendGroup' = isnull(v.VendGroup, ''),
	'VendAccount' = isnull(vpsj.OrderAccount, ''),
	'VendName' = v.Name,
	'SiteId' = coalesce(vpst.Dimension, vpsj.Dimension, '')
into #PPV
from PurchLine pl
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 VendPackingSlipTrans vpst
	on vpst.DataAreaId = ict.DataAreaId
	and vpst.InventTransId = ict.InventTransId
	and vpst.DeliveryDate = ict.TransDate
join VendPackingSlipJour vpsj
	on vpsj.DataAreaId = vpst.DataAreaId
	and vpsj.sourceDocumentheader = vpst.recid
	--and vpsj.InternalPackingSlipId = vpst.InternalPackingSlipId
	and vpsj.LedgerVoucher = ict.Voucher
join VendTable v
	on v.DataAreaId = vpsj.DataAreaId
	and v.AccountNum = vpsj.OrderAccount
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 ('', vpst.Dimension, vpsj.Dimension)
	and vpst.DeliveryDate between @dtmDateFrom and @dtmDateTo

-- Sum it up by packing slip and item
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