2009: PO Receipt and Invoice PPV

This Query is more or less an amalgamation of this post and this post. This uses the same methodology to collect data but presents it in a different way. The same assumptions, constraints and warning apply to this as with the other two referenced posts.

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

create table #ppv
(
	InventTransId varchar(30) not null,
	DeliveryDate date null,
	InvoiceDate datetime null,
	RecVoucher varchar(30) null,
	InvVoucher varchar(30) null,
	PurchId varchar(30) not null,
	SiteId varchar(10) not null,
	VendAccount varchar(20) not null,
	VendName varchar(60) not null,
	ItemGroupId varchar(20) not null,
	ItemId varchar(30) not null,
	ItemName varchar(60) not null,
	RecQty numeric(28, 12) null,
	RecValue numeric(28, 12) null,
	RecPPV numeric(28, 12) null,
	InvQty numeric(28, 12) null,
	InvValue numeric(28, 12) null,
	InvPPV numeric(28, 12) null,
	TotalPPV numeric(28, 12) not null
)

-- Receipt PPV
insert #ppv
select
	vpst.InventTransId,
	'DeliveryDate' = convert(date, vpst.DeliveryDate),
	'InvoiceDate' = null,
	'RecVoucher' = ict.Voucher,
	'InvVoucher' = null,
	pl.PurchId,
	'SiteId' = coalesce(vpst.Dimension, vpsj.Dimension, ''),
	'VendAccount' = isnull(vpsj.OrderAccount, ''),
	'VendName' = v.Name,
	i.ItemGroupId,
	pl.ItemId,
	i.ItemName,
	'RecQty' = ict.VarianceQty,
	'RecValue' = vpst.ValueMst * (ict.VarianceQty / vpst.Qty),
	'RecPPV' = ictv.CostAmountPosted,
	'InvQty' = null,
	'InvValue' = null,
	'InvPPV' = null,
	'TotalPPV' = ictv.CostAmountPosted
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.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
	
-- InvoicePPV
insert #ppv
select
	pl.InventTransId,
	'DeliveryDate' = null,
	'InvoiceDate' = convert(date, vit.InvoiceDate),
	'RecVoucher' = null,
	'InvVoucher' = vij.LedgerVoucher,
	pl.PurchId,
	'SiteId' = coalesce(vpst.Dimension, vpsj.Dimension, ''),
	'VendAccount' = isnull(vpsj.OrderAccount, ''),
	'VendName' = v.Name,
	i.ItemGroupId,
	pl.ItemId,
	i.ItemName,
	'RecQty' = null,
	'RecValue' = null,
	'RecPPV' = null,
	'InvQty' = it.Qty,
	'InvValue' = vit.LineAmountMst * (it.Qty / vit.Qty),
	'InvPPV' = (vit.LineAmountMst + isnull(mt.MiscPPV, 0)) * (it.Qty / vit.Qty) - vpst.ValueMst * (it.Qty / vpst.Qty),
	'TotalPPV' = (vit.LineAmountMst + isnull(mt.MiscPPV, 0)) * (it.Qty / vit.Qty) - vpst.ValueMst * (it.Qty / vpst.Qty)
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
		--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

select
	DeliveryDate,
	InvoiceDate,
	'Voucher' = coalesce(RecVoucher, InvVoucher),
	PurchId,
	SiteId,
	VendAccount,
	VendName,
	ItemGroupId,
	ItemId,
	ItemName,
	'PurchQty' = min(pl.PurchQty),
	'RecQty' = sum(RecQty),
	'RecValue' = sum(RecValue),
	'RecPPV' = sum(RecPPV),
	'InvQty' = sum(InvQty),
	'InvValue' = sum(InvValue),
	'InvPPV' = sum(InvPPV) + isnull(ictv.CostTransPPV, 0),
	'TotalPPV' = sum(TotalPPV) + 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
	DeliveryDate,
	InvoiceDate,
	RecVoucher,
	InvVoucher,
	PurchId,
	SiteId,
	VendAccount,
	VendName,
	ItemGroupId,
	ItemId,
	ItemName,
	ictv.CostTransPPV
having sum(TotalPPV) + isnull(ictv.CostTransPPV, 0) <> 0
GO