2009: PO Invoice PPV

Below is a 2009 Query to aid in determining Invoice PPV. This recalculates PPV rather than read the LedgerTrans table for specific posting types or accounts. This query has several assumptions that should be called out. First, that all PPV accounts have an account category of "PPV". Next, that all misc. charges are actually PPV and are attached to the Vendor Invoice Transactions, not the journal. Finally, this only works for Items of type BOM and Item. 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, ''), 'scpc')

-- 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 / vit.Qty),
	'RecDate' = vpst.DeliveryDate,
	vpst.PackingSlipId,
	'RecVoucher' = vpsj.LedgerVoucher,
	'RecValue' = vpst.ValueMst * (it.Qty / vpst.Qty),
	pl.PurchId,
	'ItemGroup' = i.ItemGroupId,
	pl.ItemId,
	'ItemName' = i.ItemName,
	it.Qty,
	'PPV' = (vit.LineAmountMst + isnull(mt.MiscPPV, 0)) * (it.Qty / vit.Qty) - vpst.ValueMst * (it.Qty / vpst.Qty),
	'VendGroup' = isnull(v.VendGroup, ''),
	'VendAccount' = isnull(vpsj.OrderAccount, ''),
	'VendName' = v.Name,
	'SiteId' = coalesce(vpst.Dimension, vpsj.Dimension, '')
into #PPV
from PurchLine pl
join InventTrans it
	on it.DataAreaId = pl.DataAreaId
	and it.InventTransId = pl.InventTransId
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 = it.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 = it.InventTransId
	and vpst.PackingSlipId = it.PackingSlipId
	and vpst.DeliveryDate = it.DatePhysical
join VendPackingSlipJour vpsj
	on vpsj.DataAreaId = vpst.DataAreaId
	and vpsj.InternalPackingSlipId = vpst.InternalPackingSlipId
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
	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