| Description |
Volume posted by invoice and actual sales are not matching.
Take db from central non stock ccop
!pastedImage_d78962_0.png!
Select C.strCustomerNumber, C.strStockStatus, P.strCategoryCode, V.Volume, I.InvoiceAmount, A.AdjQty, (I.InvoiceAmount+isnull(A.AdjQty,0)) CalcVolume, (V.Volume - (I.InvoiceAmount+isnull(A.AdjQty,0))) Diff from
(select intCustomerPatronId, intPatronageCategoryId, sum(dblVolume) Volume
from tblPATCustomerVolume
where intFiscalYear = 7
group by intCustomerPatronId, intPatronageCategoryId) V
join tblARCustomer C on V.intCustomerPatronId = C.intEntityCustomerId
join tblPATPatronageCategory P on V.intPatronageCategoryId = P.intPatronageCategoryId
join
(select IH.intEntityCustomerId, I.intPatronageCategoryId, sum(round(ID.dblQtyShipped*ID.dblPrice,2)) InvoiceAmount from tblARInvoice IH
inner join tblARInvoiceDetail ID on IH.intInvoiceId = ID.intInvoiceId
inner join tblICItem I on ID.intItemId = I.intItemId
inner join tblARCustomer C on IH.intEntityCustomerId = C.intEntityCustomerId
inner join tblPATPatronageCategory PC on I.intPatronageCategoryId = PC.intPatronageCategoryId
where I.intPatronageCategoryId is not null and PC.strPurchaseSale = 'Sale' and IH.ysnPosted = 1 and C.strStockStatus <> ''
and IH.dtmDate between '01-june-2016' and '31-may-2017'
group by IH.intEntityCustomerId, I.intPatronageCategoryId ) I
on V.intCustomerPatronId = I.intEntityCustomerId
and V.intPatronageCategoryId = I.intPatronageCategoryId
left join
(select intCustomerId, intPatronageCategoryId, sum(dblQuantityAdjusted) AdjQty
from tblPATAdjustVolumeDetails VD inner join tblPATAdjustVolume AV on VD.intAdjustmentId = AV.intAdjustmentId
where intPatronageCategoryId <> 0
and dtmAdjustmentDate between '01-june-2016' and '31-may-2017'
group by intCustomerId, intPatronageCategoryId) A
on V.intCustomerPatronId = A.intCustomerId
and V.intPatronageCategoryId = A.intPatronageCategoryId
|