Hi , can you please help me , why the last line on my code don't works ?
static void SAUInvoicedPO(Args _args)
{
Query query;
QueryRun qRun;
QueryBuildDatasource datasource;
VendInvoiceTrans vendInvoiceTrans;
VendInvoiceJour vendInvoiceJour;
PurchLine purchLine;
AccountingDistribution accountDistribution;
//VendInvoiceTrans
query = new Query();
datasource = query.addDataSource(tableNum(VendInvoiceTrans));
//VendInvoiceJour
datasource = datasource.addDataSource(tableNum(VendInvoiceJour));
datasource.joinMode(JoinMode::InnerJoin);
datasource.relations(true);
datasource.addRange(fieldnum(VendInvoiceJour,PurchId)).value("=Test1");
datasource.addRange(fieldnum(VendInvoiceJour,InvoiceDate)).value(queryRange(19\4\2016,20\04\2016));
//PurchLine
datasource = datasource.addDataSource(tableNum(PurchLine));
datasource.joinMode(JoinMode::InnerJoin);
datasource.relations(false);
datasource.addLink(fieldNum(vendInvoiceJour,PurchId), fieldNum(purchLine, PurchId));
datasource.addLink(fieldNum(vendInvoiceTrans, PurchaseLineLineNumber), fieldNum(purchLine,LineNumber));
//ACCOUNTINGDISTRIBUTION
datasource = datasource.addDataSource(tableNum(AccountingDistribution));
datasource.joinMode(JoinMode::InnerJoin);
datasource.addLink(fieldNum(accountingDistribution, SOURCEDOCUMENTLINE), fieldNum(purchLine,SOURCEDOCUMENTLINE));
qRun = new QueryRun(query);
while(qRun.next())
{
vendInvoiceTrans = qRun.get(tablenum(VendInvoiceTrans));
vendInvoiceJour = qRun.get(tablenum(VendInvoiceJour));
purchLine = qRun.get(tablenum(PurchLine));
accountDistribution = qRun.get(tablenum(AccountingDistribution));
info(strfmt('%1 ,%2 , %3, %4 %5 , %6, %7 %8',vendInvoiceJour.LedgerVoucher,
vendInvoiceTrans.PurchID,
vendInvoiceTrans.ItemId,
vendInvoiceTrans.Qty,
vendInvoiceTrans.PurchUnit
,PurchLine.REMAINPURCHFINANCIAL,
PurchLine.REMAINPURCHPHYSICAL
,accountDistribution.LEDGERDIMENSION
));
}
}
I have tested that on SQL and it 's works perfeclty
SELECT *
INTO #tmp
From MicrosoftDynamicsAX.dbo.VENDINVOICETRANS AS VENDINVOICETRANS
INNER JOIN MicrosoftDynamicsAx.dbo.VENDINVOICEJOUR AS VENDINVOICEJOUR
ON VENDINVOICEJOUR.PARTITION = @Partition
AND VENDINVOICEJOUR.PURCHID = VENDINVOICETRANS.PURCHID
AND VENDINVOICEJOUR.INVOICEID = VENDINVOICETRANS.INVOICEID
AND VENDINVOICEJOUR.INVOICEDATE = VENDINVOICETRANS.INVOICEDATE
AND VENDINVOICEJOUR.NUMBERSEQUENCEGROUP = VENDINVOICETRANS.NUMBERSEQUENCEGROUP
AND VENDINVOICEJOUR.INTERNALINVOICEID = VENDINVOICETRANS.INTERNALINVOICEID
INNER JOIN MicrosoftDynamicsAx.dbo.purchline AS PurchLine
On PurchLine.PARTITION = @Partition
AND PurchLine.PURCHID = VENDINVOICEJOUR.PURCHID
AND PurchLine.LINENUMBER = VENDINVOICETRANS.PURCHASELINELINENUMBER
INNER JOIN MicrosoftDynamicsAx.dbo.ACCOUNTINGDISTRIBUTION AS AccDISTRIBUTION
ON AccDISTRIBUTION.PARTITION = @Partition
AND AccDISTRIBUTION.SOURCEDOCUMENTLINE = PurchLine.SOURCEDOCUMENTLINE
WHERE VENDINVOICETRANS.PURCHID ='Test1'
