D365FO - Getting Packing Slip Id I.e. Delivery Note Number from Invoice Id For FOOD Item

Hi,

D365FO - Getting Packing Slip Id I.e. Delivery Note Number from Invoice Id For FOOD Item

Below code snippet will get DN id from invoice id.

private str getPackingSlipIds(InvoiceId invoiceId)

    {

        CustPackingSlipTrans    custPackingSlipTrans;

        CustInvoiceTrans        custInvoiceTrans;

        InventTrans             inventTrans;

        InventTransOrigin       inventTransOrigin;

        Set                     s = new Set(Types::String);

        SetEnumerator           se;

        String255                   packingSlip;

        while select PackingSlipId from custPackingSlipTrans

        join custInvoiceTrans join inventTransOrigin join inventTrans

            where custPackingSlipTrans.InventTransId     == custInvoiceTrans.InventTransId

            &&    custPackingSlipTrans.InventTransId     == inventTransOrigin.InventTransId

            &&    inventTrans.InventTransOrigin          == inventTransOrigin.RecId

            &&    inventTrans.PackingSlipId              == custPackingSlipTrans.PackingSlipId

            &&    inventTrans.InvoiceId                  == invoiceId

        {

            s.add(custPackingSlipTrans.PackingSlipId);

        }

        se = s.getEnumerator();

        while(se.moveNext())

        {

            if(packingSlip)

            packingSlip += ", " + se.current();

            else

            packingSlip  = se.current();

        }

        return packingSlip;

    }

   how to join  custPackingSlipTrans and custInvoiceTrans in d365

 //To get Corresponding DN Number to Invoice Id

           CustPackingSlipTrans    custPackingSlipTrans;

           CUSTINVOICEJOUR localCUSTINVOICEJOUR;

           CUSTINVOICETRANS localCUSTINVOICETRANS;

            InventTrans             inventTrans;

            InventTransOrigin       inventTransOrigin;

            select firstonly PackingSlipId,DeliveryDate from custPackingSlipTrans

        join localCUSTINVOICETRANS join inventTransOrigin join inventTrans

            where custPackingSlipTrans.InventTransId     == custInvoiceTrans.InventTransId

            &&    custPackingSlipTrans.InventTransId     == inventTransOrigin.InventTransId

            &&    inventTrans.InventTransOrigin          == inventTransOrigin.RecId

            &&    inventTrans.PackingSlipId              == custPackingSlipTrans.PackingSlipId

                &&    inventTrans.InvoiceId                  == custInvoiceTrans.invoiceId

            && localCUSTINVOICETRANS.SALESID == CUSTPACKINGSLIPTRANS.SALESID

            && localCUSTINVOICETRANS.INVOICEID == custInvoiceTrans.INVOICEID ;

                    SalesInvoiceDetailsReportTmp.PackingSlipId  = custPackingSlipTrans.PackingSlipId ;

           SalesInvoiceDetailsReportTmp.DeliveryDate =custPackingSlipTrans.DeliveryDate ;

            //To get Corresponding DN Number to Invoice Id

Job Class for update DN Number 

class DNNumberUpdateByInvoiceId4InvForm extends RunBase

{

    

    DialogFIeld field_InvoiceId;

    CustInvoiceId _invoiceId;

  

    public Object Dialog()

    {

        Dialog dialog;

        ;

 

        dialog = super();

 

        // Set a title for dialog

        dialog.caption('DN number Update in Invoice Form via Invoice ID');

 

        // Add a new field to Dialog

       

        field_InvoiceId = dialog.addField( extendedTypeStr(CustInvoiceId), 'Invoice Id' );

        return dialog;

    }

    public boolean getFromDialog()

    {

        // Retrieve values from Dialog

     

        _invoiceId = field_InvoiceId.value();

       

        return super();

    }

    public void run()

    {

      

        //DN number update 2018-09-25

        CustInvoiceTrans custInvoiceTrans;

        CustPackingSlipTrans custPackingSlipTrans;

        PackingSlipId _packingSlipId;

          select  custInvoiceTrans where custInvoiceTrans.InvoiceId == _invoiceId ;

        //To get Corresponding DN Number to Invoice Id

        //   CustPackingSlipTrans    custPackingSlipTrans;

        CUSTINVOICEJOUR localCUSTINVOICEJOUR;

        CUSTINVOICETRANS localCUSTINVOICETRANS;

        InventTrans             inventTrans;

        InventTransOrigin       inventTransOrigin;

        select firstonly PackingSlipId,DeliveryDate from custPackingSlipTrans

                    join localCUSTINVOICETRANS join inventTransOrigin join inventTrans

                    where custPackingSlipTrans.InventTransId     == custInvoiceTrans.InventTransId

                    &&    custPackingSlipTrans.InventTransId     == inventTransOrigin.InventTransId

                    &&    inventTrans.InventTransOrigin          == inventTransOrigin.RecId

                    &&    inventTrans.PackingSlipId              == custPackingSlipTrans.PackingSlipId

                    &&    inventTrans.InvoiceId                  == custInvoiceTrans.invoiceId

                    && localCUSTINVOICETRANS.SALESID == custPackingSlipTrans.SALESID

                    && localCUSTINVOICETRANS.INVOICEID == custInvoiceTrans.INVOICEID ;

        

            

            _packingSlipId  = custPackingSlipTrans.PackingSlipId  ;

           

            CustInvoiceJour custInvoiceJourUpdate;

            ttsbegin ;

        select firstonly forupdate  custInvoiceJourUpdate  order by custInvoiceJourUpdate.RecId desc  where  custInvoiceJourUpdate.SalesId== custInvoiceTrans.SalesId && custInvoiceJourUpdate.InvoiceId ==custInvoiceTrans.InvoiceId ;

            

        

            if(_packingSlipId)

            {

                custInvoiceJourUpdate.DNnumber =_packingSlipId;

            }

        

            custInvoiceJourUpdate.doUpdate();

            ttscommit;

        info( strFmt('DN Number %1 --Sales Order %2 -- Invoice Id %3' , _packingSlipId ,custInvoiceTrans.salesId,_invoiceId ));

        

    }

    public static void main(Args _args)

    {

        DNNumberUpdateByInvoiceId4InvForm DNNumberUpdateByInvoiceId4InvForm = new DNNumberUpdateByInvoiceId4InvForm();

 

        // Prompt the dialog, if user clicks in OK it returns true

        if (DNNumberUpdateByInvoiceId4InvForm.prompt())

        {

            DNNumberUpdateByInvoiceId4InvForm.run();

        }

      

    }

}

https://rahulmsdax.blogspot.com/2020/08/how-to-get-packing-slip-number-of.html

select CUSTPACKINGSLIPTRANS.PACKINGSLIPID,CUSTPACKINGSLIPTRANS.DELIVERYDATE as PackingslipDate, CUSTINVOICEJOUR.INVOICEID, CUSTINVOICEJOUR.INVOICEDATE, CUSTINVOICEJOUR.SALESID from CUSTINVOICEJOUR

join CUSTINVOICETRANS on CUSTINVOICEJOUR.INVOICEID = CUSTINVOICETRANS.INVOICEID 

and  CUSTINVOICEJOUR.INVOICEDATE = CUSTINVOICETRANS.INVOICEDATE

and CUSTINVOICEJOUR.SALESID = CUSTINVOICETRANS.SALESID

join  CUSTPACKINGSLIPTRANS on CUSTINVOICETRANS.INVENTTRANSID = CUSTPACKINGSLIPTRANS.INVENTTRANSID

               and CUSTINVOICETRANS.SALESID = CUSTPACKINGSLIPTRANS.SALESID

where CUSTINVOICEJOUR.DATAAREAID = 'ABC'-- data area id code

 and CUSTINVOICEJOUR.INVOICEDATE between '2019-12-29 00:00:00.000' and '2020-01-31 00:00:00.000' -- YYYY-MM-DD