Fetching Delivery Note Data with QTY and Net Amount from AX 2012 and AX 4 (SQL QUERY)

Hi,

Fetching Invoice and Delivery Note Data from AX 2012 and AX 4.


Summary from AX 2012

select cj.salesid ,cj.PACKINGSLIPID, sum(b.QTY),sum(c.LINEAMOUNT)   from
CUSTPACKINGSLIPJOUR CJ
join  CustPackingSlipTrans b on b.salesId = cj.SalesId  and b.PACKINGSLIPID =cj.PACKINGSLIPID
and cj.DELIVERYDATE  = b.DELIVERYDATE
join SALESLINE  C on c.salesId = b.SalesId and c.InventTransId = b.InventTransId
where b.DATAAREAID ='SSSS' and cj.InvoiceAccount='TEST700'
group by cj.SalesId,cj.PACKINGSLIPID


Line Level from AX 2012

select cj.salesid ,cj.PACKINGSLIPID,c.ITEMID, b.QTY,c.LINEAMOUNT  from
CUSTPACKINGSLIPJOUR CJ
join  CustPackingSlipTrans b on b.salesId = cj.SalesId  and b.PACKINGSLIPID =cj.PACKINGSLIPID
and cj.DELIVERYDATE  = b.DELIVERYDATE
join SALESLINE  C on c.salesId = b.SalesId and c.InventTransId = b.InventTransId
where b.DATAAREAID ='SSSS' and cj.InvoiceAccount='TEST700'
order by cj.SalesId,cj.PACKINGSLIPID


AX 4

select cj.salesid ,cj.PACKINGSLIPID,sum(b.QTY),sum(c.LINEAMOUNT ) from
CUSTPACKINGSLIPJOUR CJ
join  CustPackingSlipTrans b on b.salesId = cj.SalesId  and b.PACKINGSLIPID =cj.PACKINGSLIPID
and cj.DELIVERYDATE  = b.DELIVERYDATE
join CustInvoiceTrans C on c.salesId = b.SalesId and c.InventTransId = b.InventTransId
where cj.DataAreaId='SSSS' and cj.InvoiceAccount='TEST700'
group by cj.SalesId,cj.PACKINGSLIPID
order by cj.SalesId,cj.PACKINGSLIPID