AOT query date ranges

In LogisticsPostalAddress table i had to add a range on ValidFrom and ValidTo fields in order to get only the active addresses.

Having an AOT query, i did the following:

Got the addresses for a customer with a query like this:


SELECT ct.ACCOUNTNUM
, dpt.NAME
, dpl.ISPOSTALADDRESS
, dpl.ISPRIMARY
, ll.DESCRIPTION
, ll.RECID
, lpa.ADDRESS
, lpa.VALIDFROM
, lpa.VALIDTO
FROM CUSTTABLE AS ct

JOIN DIRPARTYTABLE AS dpt
ON ct.PARTY = dpt.RECID

JOIN DIRPARTYLOCATION AS dpl
ON dpl.PARTY = dpt.RECID

JOIN LOGISTICSLOCATION AS ll
ON dpl.LOCATION = ll.RECID

JOIN LOGISTICSPOSTALADDRESS AS lpa
ON lpa.LOCATION = ll.RECID

WHERE ct.ACCOUNTNUM = 'C00000010'

I've just created this address which shows up in my AX view. So, .. today() acts like less then or equal.

Get SalesLines with Invoice but without associated PackingSlip

First of all, I would like to display all the sales lines with a relation in CustInvoiceTrans via InventTransId field. So .. fully or partially invoiced, doesn't matter.
 - Further more, I want to check only those invoices which were not posted from a PackingSlip and for this, i am using CustInvoicePackingSlipQuantityMatch table.

The relation is simple:

•   CustInvoiceTrans (SourceDocumentLine) 
•   CustInvoicePackingSlipQuantityMatch (InvoiceSourceDocumentLine)

and

•  CustPackingSlipTrans(SourceDocumentLine) 
•  CustInvoicePackingSlipQuantityMatch (PackingSlipSourceDocumentLine)

I have noticed one thing: if a packing slip is created first, a line is added in the CustInvoicePackingSlipQuantityMatch with a reference to that packing slip. If you continue and create the invoice it will be added in the table too. 

But if you only create the invoice, without a packing slip, no record will exist in the table regarding your invoice. 

Here is a query for this:

 SELECT sl.SALESID

, CONVERT(INT, sl.QTYORDERED) AS 'SalesQty'

, sl.INVENTTRANSID

, (SELECT cit1.INVOICEID

FROM CUSTINVOICETRANS AS cit1

WHERE cit1.INVENTTRANSID = sl.INVENTTRANSID

AND cit1.PARTITION = sl.PARTITION

AND cit1.DATAAREAID = sl.DATAAREAID) AS 'InvoiceId'

, cpst.PACKINGSLIPID

, CONVERT(INT, cpst.QTY)  AS 'PackingSlipQty'

, cipsqm.PACKINGSLIPSOURCEDOCUMENTLINE

, cipsqm.INVOICESOURCEDOCUMENTLINE

, cit.INVOICEID

FROM SALESTABLE AS st

JOIN SALESLINE AS sl

ON st.SALESID = sl.SALESID

AND st.PARTITION = sl.PARTITION

AND st.DATAAREAID = sl.DATAAREAID

LEFT JOIN CUSTPACKINGSLIPTRANS AS cpst

ON sl.INVENTTRANSID = cpst.INVENTTRANSID

AND sl.PARTITION = cpst.PARTITION

AND sl.DATAAREAID = cpst.DATAAREAID

LEFT JOIN CustInvoicePackingSlipQuantityMatch AS cipsqm

ON cpst.SOURCEDOCUMENTLINE = cipsqm.PACKINGSLIPSOURCEDOCUMENTLINE

LEFT JOIN CUSTINVOICETRANS AS cit

ON cipsqm.INVOICESOURCEDOCUMENTLINE = cit.SOURCEDOCUMENTLINE

WHERE st.CREATEDDATETIME > '2017-01-10'

And the results:

As you can see, the second sales order is there, its invoice is there but no info was added to CustInvoicePackingSlipQuantityMatch.

Let's get to X++ now :

static void salesPackingSlipsInvoicesTest(Args _args)

{

    Query localQuery;

    QueryBuildDataSource qbdsLocalSalesTable, qbdsLocalSalesLine, qbdsLocalCustInvoiceTrans, qbdsLocalCustInvoicePackingSlipQuantityMatch;

    QueryBuildRange localQbr;

    QueryRun localQueryRun;

    SalesTable salesTable;

    CustInvoiceTrans custInvoiceTrans;

    TransDate fromDate, toDate;

    utcDateTime dateToBeginValue, dateToEndValue;

    DimensionProvider dimensionProvider = new DimensionProvider();

    localQuery = new query();

    qbdsLocalSalesTable = localQuery.addDataSource(tableNum(SalesTable));

    qbdsLocalSalesLine = qbdsLocalSalesTable.addDataSource(tableNum(SalesLine));

    qbdsLocalSalesLine.joinMode(JoinMode::InnerJoin);

    qbdsLocalSalesLine.relations(true);

    //qbdsLocalSalesLine.fetchMode(QueryFetchMode::One2One);

    qbdsLocalCustInvoiceTrans = qbdsLocalSalesLine.addDataSource(tableNum(CustInvoiceTrans));

    qbdsLocalCustInvoiceTrans.joinMode(JoinMode::InnerJoin);

    qbdsLocalCustInvoiceTrans.relations(false);

    qbdsLocalCustInvoiceTrans.addLink(fieldNum(SalesLine, InventTransId),

        fieldNum(CustInvoiceTrans, InventTransId));

    //qbdsLocalCustInvoiceTrans.fetchMode(QueryFetchMode::One2One);

    //Use the junction table to go from packing lines to invoice lines

    qbdsLocalCustInvoicePackingSlipQuantityMatch = qbdsLocalCustInvoiceTrans.addDataSource(tableNum(CustInvoicePackingSlipQuantityMatch));

    qbdsLocalCustInvoicePackingSlipQuantityMatch.joinMode(JoinMode::NoExistsJoin);

    qbdsLocalCustInvoicePackingSlipQuantityMatch.relations(false);

    qbdsLocalCustInvoicePackingSlipQuantityMatch.addLink(fieldNum(CustInvoiceTrans, SourceDocumentLine),

        fieldNum(CustInvoicePackingSlipQuantityMatch, InvoiceSourceDocumentLine));

    //qbdsLocalCustInvoicePackingSlipQuantityMatch.fetchMode(QueryFetchMode::One2One);

    

    dimensionProvider.addAttributeRangeToQuery(localQuery

                  , qbdsLocalSalesLine.name()

                  , fieldStr(SalesLine, DefaultDimension)

                  , DimensionComponent::DimensionAttribute

                  , 'b2b'

                  , "CostCenter"

                  , false);

    fromDate = mkDate(9, 1, 2017);

    toDate = mkDate(10, 1, 2017);

    if (fromDate || toDate)

    {

        dateToBeginValue = datetobeginUtcDateTime(fromDate, 0);

        dateToEndValue   = datetoendUtcDateTime(toDate,  0);

        qbdsLocalSalesTable.addRange(fieldnum(SalesTable, CreatedDateTime)).value(queryRange(dateToBeginValue, dateToEndValue));

    }

    localQueryRun = new QueryRun(localQuery);

    while (localQueryRun.next())

    {

        salesTable = localQueryRun.get(tableNum(SalesTable));

        custInvoiceTrans = localQueryRun.get(tableNum(CustInvoiceTrans));

        info(strFmt("Salesid %1 - InvoiceId %2", salesTable.SalesId, custInvoiceTrans.InvoiceId));

    }

}

 After running this query i obtain the following :

So .. what is this ?

you will get an explanation here.

Magical FetchMode property

Just uncomment the red lines and voila !

The desired line is right here. As i have shown above, this is the one with invoice and without packing slip associated.