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.