Finance | Project Operations, Human Resources, ...

To add one more table in custom query in data provider class

(1) Share

Report

Posted on by Dineshkarlekar

1,836

Hi ,

Everyone ,

i have created the report which contains controller and dp class and the dp class was using query ,

now i have to add the contract class to it for from date and to date filter i have made required changes in dp class but i am not getting final query in debugger , can anybody help me out with this , please suggest me what changes i have to make in my dp class to get required output between dates . i am trying to do something like this .

my code is below.

SELECT VendPackingSlipJour.DELIVERYDATE,* FROM PurchLine
JOIN PurchTable ON PurchLine.PurchId = PurchTable.PurchId
join VendPackingSlipJour ON PurchTable.PurchId = VendPackingSlipJour.PurchId
WHERE ((IsDeleted = 0))
and VendPackingSlipJour.DELIVERYDATE >= '2019-03-27'
and VendPackingSlipJour.DELIVERYDATE <= '2019-05-14'

thanks,

regards,

Dinesh

I have the same question (0)

All responses (11)

Answers (0)

Dineshkarlekar

1,836 on at

Like (0)

Report

my dp class code ,

[
   SRSReportQueryAttribute(queryStr(DTRecivedNotInvoicedQue)),
   SRSReportParameterAttribute(classstr(DTRecievedButNotInvoicedContract))
]
class DTRecivedButNotInvoicedDp extends SRSReportDataProviderBase
{
    DTRcvdNotInvoicedTmp                DTRcvdNotInvoicedTmp;
    Purchline                           purchline,purchlineLoc;
    LedgerJournalTrans                  ledgerJournalTrans;
    VendPackingSlipTrans                vendPackingSlipTrans;
    VendInvoiceJour                     vendInvoiceJour;
    VendInvoiceTrans                    vendInvoiceTrans;
    PurchTable                          purchTable;
    VendAccount                         vendAccount;
    str                                 name;
    ExchRate                            rate;
    AmountCur                           invamt,notinvamt;
    PurchQty                            qty,notinvqty,cancelqty,lineQty;

    [SrsReportDataSetAttribute(tableStr('DTRcvdNotInvoicedTmp'))]
    public DTRcvdNotInvoicedTmp GetDTRcvdNotInvoicedTmp()
    {
        select * from DTRcvdNotInvoicedTmp;
        return		  DTRcvdNotInvoicedTmp;
    }

    Public void processReport()
    {
        Query							 q;
        QueryRun					     qr;
        QueryBuildDataSource		     qbdsVendPackingSlipJour;
        QueryBuildDataSource			 qbdsobjDirPartyTable;
        QueryBuildDataSource			 qbdsobjLogisticsPostalAddress;
        QueryBuildRange					 qbr;
        TransDate                        fromDate;
        TransDate                        toDate;


        DTRecievedButNotInvoicedContract contract =
            this.parmDataContract() as DTRecievedButNotInvoicedContract;

        fromDate = contract.parmFromDate();
        toDate   = contract.parmToDate();

        DTRcvdNotInvoicedTmp.clear();

        q = this.parmQuery();
        q.dataSourceName(tableStr(PurchLine)).addRange(fieldNum(PurchLine,IsDeleted)).value(queryValue(0));

        if(fromDate && toDate)
        {  
            qbdsVendPackingSlipJour = q.addDataSource(tableNum(VendPackingSlipJour));
            qbdsVendPackingSlipJour.relations(false);
            qbdsVendPackingSlipJour.addLink(fieldNum(PurchLine, PurchId),fieldNum(VendPackingSlipJour, PurchId));
            q.dataSourceName(tableStr(VendPackingSlipJour)).addRange(fieldNum(VendPackingSlipJour, DeliveryDate)).value(queryRange(fromDate, toDate));
        }

        qr = new QueryRun(q);

        while(qr.next())
        {
            purchTable = qr.get(tableNum(PurchTable));
            purchline = qr.get(tableNum(Purchline));

            DTRcvdNotInvoicedTmp.clear();
            DTRcvdNotInvoicedTmp.PurchId     = purchTable.PurchId;
            DTRcvdNotInvoicedTmp.PurchName   = purchTable.PurchName;
            DTRcvdNotInvoicedTmp.LineNumber  = purchline.LineNumber;
            DTRcvdNotInvoicedTmp.ItemId      = purchline.ItemId;
            DTRcvdNotInvoicedTmp.ItemName    = purchline.itemName();
            DTRcvdNotInvoicedTmp.PurchQty    = purchline.PurchQty;

            select sum(LineAmount)from purchlineLoc
                where purchlineLoc.PurchId == purchTable.PurchId;

            DTRcvdNotInvoicedTmp.POTotalAmount = purchlineLoc.LineAmount;

            select sum(Qty),sum(ValueMST) from vendPackingSlipTrans
                    where vendPackingSlipTrans.ItemId ==  purchline.ItemId
                     && vendPackingSlipTrans.InventTransId == purchline.InventTransId;

            rate =   ExchangeRateHelper::getExchangeRate1_Static(Ledger::current(),purchTable.CurrencyCode,today())/100;

            DTRcvdNotInvoicedTmp.RecivedQty   = vendPackingSlipTrans.Qty;
            DTRcvdNotInvoicedTmp.RecivedAmount = vendPackingSlipTrans.ValueMST/rate;
            qty = 0;
            invamt = 0;

            while select * from vendInvoiceTrans
                join vendInvoiceJour
                    where vendInvoiceTrans.InvoiceId == vendInvoiceJour.InvoiceId
                    &&  vendInvoiceTrans.InvoiceDate == vendInvoiceJour.InvoiceDate
                    &&  vendInvoiceTrans.numberSequenceGroup == vendInvoiceJour.numberSequenceGroup
                    &&  vendInvoiceTrans.InternalInvoiceId == vendInvoiceJour.InternalInvoiceId
                    &&  vendInvoiceTrans.OrigPurchId == purchTable.PurchId
                    && vendInvoiceTrans.PurchaseLineLineNumber == purchline.LineNumber
                    &&  vendInvoiceTrans.ItemId == purchline.ItemId
            {   
                qty   += vendInvoiceTrans.Qty;
                invamt += vendInvoiceTrans.lineAmountInclTax();
            }
            DTRcvdNotInvoicedTmp.InvoicedQty   = qty;
            DTRcvdNotInvoicedTmp.InvoiceAmount = invamt;
            notinvqty = 0;
            notinvamt = 0;
            cancelqty = 0;
            lineQty = 0;
            lineQty = purchline.PurchQty;

            if(qty <= lineQty)
            {
              cancelqty = purchline.PurchQty - vendPackingSlipTrans.Qty;
            }
            else
            {
                cancelqty = 0;
            }

            if(cancelqty != 0
                && purchline.RemainPurchPhysical == 0 )
            {
                DTRcvdNotInvoicedTmp.CancelQty =  cancelqty;
            }
            notinvqty = purchline.RemainPurchFinancial + purchline.RemainPurchPhysical;
            notinvamt = notinvqty * purchline.PurchPrice;
            DTRcvdNotInvoicedTmp.NotInvoicedQty = notinvqty ;
            DTRcvdNotInvoicedTmp.NotInvoiceAmount =  notinvamt;

            DTRcvdNotInvoicedTmp.insert();
        }
    }

}

Was this reply helpful? Yes No

Martin Dráb 237,976 Most Valuable Professional on at

Like (1)

Report

Please look at what query you've generated by your code and share it with us.

I can say for sure that your code doesn't match your SQL code above, because there you join VendPackingSlipJour with PurchTable, while you join it with PurchLine in X++.

You can simplify your code by adding VendPackingSlipJour to the query instead of doing it at runtime.

Next time, please remove code irrelevant to the problem. Most of code below is not related to the query and therefore it just make the problem harder to understand.

Was this reply helpful? Yes No
Martin Dráb 237,976 Most Valuable Professional on at

Like (1)

Report

Another problem is that even your SQL code is wrong, because it ignores partial and summary postings. The correct way of joining purchase orders and packing slips is over VendPackingSlipPurchLink table.

Was this reply helpful? Yes No
Suggested answer

Bharani Preetham Pe... 3,634 Moderator on at

Like (1)

Report

We are not exactly aware of the query which you created and using in the report as contract. Please share the aot query schema for more understanding.

Also, when adding datasource to the query, ideally it should be added to the datasource not directly to the query object. And as Martin suggested the join you made is different from the thing which you showed in the question level.

Are you getting query object from this.parmquery() method?

Was this reply helpful? Yes No

Dineshkarlekar

1,836 on at

Like (0)

Report

hi , every one

i have made changes to the code please let me know if i am doing anything wrong.below query i am getting

SELECT * FROM PurchLine(PurchLine) WHERE ((IsDeleted = 0)) 
JOIN * FROM PurchTable(PurchTable)
 ON PurchLine.PurchId = PurchTable.PurchId 
JOIN * FROM VendPackingSlipJour(VendPackingSlipJour_1)
 ON PurchTable.PurchId = VendPackingSlipJour.PurchId 
AND ((DeliveryDate>={ts '2024-01-01 00:00:00.000'} AND DeliveryDate<={ts '2024-02-29 00:00:00.000'}))}

[
   SRSReportQueryAttribute(queryStr(DTRecivedNotInvoicedQue)),
   SRSReportParameterAttribute(classstr(DTRecievedButNotInvoicedContract))
]
class DTRecivedButNotInvoicedDp extends SRSReportDataProviderBase
{
    DTRcvdNotInvoicedTmp                DTRcvdNotInvoicedTmp;
    Purchline                           purchline,purchlineLoc;
    LedgerJournalTrans                  ledgerJournalTrans;
    VendPackingSlipTrans                vendPackingSlipTrans;
    VendInvoiceJour                     vendInvoiceJour;
    VendInvoiceTrans                    vendInvoiceTrans;
    PurchTable                          purchTable;
    VendAccount                         vendAccount;
    str                                 name;
    ExchRate                            rate;
    AmountCur                           invamt,notinvamt;
    PurchQty                            qty,notinvqty,cancelqty,lineQty;

    [SrsReportDataSetAttribute(tableStr('DTRcvdNotInvoicedTmp'))]
    public DTRcvdNotInvoicedTmp GetDTRcvdNotInvoicedTmp()
    {
        select * from DTRcvdNotInvoicedTmp;
        return		  DTRcvdNotInvoicedTmp;
    }

    Public void processReport()
    {
        Query							 q;
        Query							 qnew = new Query();
        QueryRun					     qr;
        QueryBuildDataSource		     qbdsVendPackingSlipJour;
        QueryBuildDataSource			 qbdsobjDirPartyTable;
        QueryBuildDataSource			 qbdsobjLogisticsPostalAddress;
        QueryBuildRange					 qbr;
        TransDate                        fromDate;
        TransDate                        toDate;


        DTRecievedButNotInvoicedContract contract =
            this.parmDataContract() as DTRecievedButNotInvoicedContract;

        fromDate = contract.parmFromDate();
        toDate   = contract.parmToDate();

        DTRcvdNotInvoicedTmp.clear();

        q = this.parmQuery();
        q.dataSourceName(tableStr(PurchLine)).addRange(fieldNum(PurchLine,IsDeleted)).value(queryValue(0));

        if(fromDate && toDate)
        {  
            qnew = q;
            qbdsVendPackingSlipJour = qnew.dataSourceTable(tablenum(PurchTable)).addDataSource(tablenum(VendPackingSlipJour));
            qbdsVendPackingSlipJour.relations(false);
            qbdsVendPackingSlipJour.joinMode(JoinMode::InnerJoin);
            qbdsVendPackingSlipJour.addLink(fieldNum(VendPackingSlipJour, PurchId),fieldNum(PurchTable, PurchId));
            qbdsVendPackingSlipJour.orderMode(OrderMode::GroupBy);
            qbdsVendPackingSlipJour.addGroupByField(fieldNum(PurchLine, ItemId));
            qbdsVendPackingSlipJour.addRange(fieldNum(VendPackingSlipJour, DeliveryDate)).value(queryRange(fromDate, toDate));
            qr = new QueryRun(qnew);
        }
         else
        {
            qr = new QueryRun(q);
        }

        while(qr.next())
        {
            purchTable = qr.get(tableNum(PurchTable));
            purchline = qr.get(tableNum(Purchline));

            DTRcvdNotInvoicedTmp.clear();
            DTRcvdNotInvoicedTmp.PurchId     = purchTable.PurchId;
            DTRcvdNotInvoicedTmp.PurchName   = purchTable.PurchName;
            DTRcvdNotInvoicedTmp.LineNumber  = purchline.LineNumber;
            DTRcvdNotInvoicedTmp.ItemId      = purchline.ItemId;
            DTRcvdNotInvoicedTmp.ItemName    = purchline.itemName();
            DTRcvdNotInvoicedTmp.PurchQty    = purchline.PurchQty;

            select sum(LineAmount)from purchlineLoc
                where purchlineLoc.PurchId == purchTable.PurchId;

            DTRcvdNotInvoicedTmp.POTotalAmount = purchlineLoc.LineAmount;

            select sum(Qty),sum(ValueMST) from vendPackingSlipTrans
                    where vendPackingSlipTrans.ItemId ==  purchline.ItemId
                     && vendPackingSlipTrans.InventTransId == purchline.InventTransId;

            rate =   ExchangeRateHelper::getExchangeRate1_Static(Ledger::current(),purchTable.CurrencyCode,today())/100;

            DTRcvdNotInvoicedTmp.RecivedQty   = vendPackingSlipTrans.Qty;
            DTRcvdNotInvoicedTmp.RecivedAmount = vendPackingSlipTrans.ValueMST/rate;
            qty = 0;
            invamt = 0;

            while select * from vendInvoiceTrans
                join vendInvoiceJour
                    where vendInvoiceTrans.InvoiceId == vendInvoiceJour.InvoiceId
                    &&  vendInvoiceTrans.InvoiceDate == vendInvoiceJour.InvoiceDate
                    &&  vendInvoiceTrans.numberSequenceGroup == vendInvoiceJour.numberSequenceGroup
                    &&  vendInvoiceTrans.InternalInvoiceId == vendInvoiceJour.InternalInvoiceId
                    &&  vendInvoiceTrans.OrigPurchId == purchTable.PurchId
                    && vendInvoiceTrans.PurchaseLineLineNumber == purchline.LineNumber
                    &&  vendInvoiceTrans.ItemId == purchline.ItemId
            {   
                qty   += vendInvoiceTrans.Qty;
                invamt += vendInvoiceTrans.lineAmountInclTax();
            }
            DTRcvdNotInvoicedTmp.InvoicedQty   = qty;
            DTRcvdNotInvoicedTmp.InvoiceAmount = invamt;
            notinvqty = 0;
            notinvamt = 0;
            cancelqty = 0;
            lineQty = 0;
            lineQty = purchline.PurchQty;

            if(qty <= lineQty)
            {
              cancelqty = purchline.PurchQty - vendPackingSlipTrans.Qty;
            }
            else
            {
                cancelqty = 0;
            }

            if(cancelqty != 0
                && purchline.RemainPurchPhysical == 0 )
            {
                DTRcvdNotInvoicedTmp.CancelQty =  cancelqty;
            }
            notinvqty = purchline.RemainPurchFinancial + purchline.RemainPurchPhysical;
            notinvamt = notinvqty * purchline.PurchPrice;
            DTRcvdNotInvoicedTmp.NotInvoicedQty = notinvqty ;
            DTRcvdNotInvoicedTmp.NotInvoiceAmount =  notinvamt;
            DTRcvdNotInvoicedTmp.NotInvoiceAmountSupplier = vendPackingSlipTrans.ValueMST/rate -invamt ;

            DTRcvdNotInvoicedTmp.insert();
        }
    }

}

Was this reply helpful? Yes No

Martin Dráb 237,976 Most Valuable Professional on at

Like (0)

Report

I see you love posting all the code unrelated to your question...

Anyway, so what problem do you have with your query? It seems to look like you wanted. It's difficult to give you an answer if you don't describe the problem.

Was this reply helpful? Yes No
Bharani Preetham Pe... 3,634 Moderator on at

Like (0)

Report

Code change is fine. But before making changes please answer our questions so that we can resolve problem a bit faster.

After making changes did you try to check if it is working? As Martin suggested you need to give your full problem statement.

Was this reply helpful? Yes No
Dineshkarlekar 1,836 on at

Like (0)

Report
hi every on report is working but i am getting lines repeated in report in some cases , if i want to group by in query the reports reports is not getting open , but if i remove group by reports runs but with repeated records . is anything wrong with my group by query .

qnew = q; qbdsVendPackingSlipJour = qnew.dataSourceTable(tablenum(PurchTable)).addDataSource(tablenum(VendPackingSlipJour)); qbdsVendPackingSlipJour.relations(false); qbdsVendPackingSlipJour.joinMode(JoinMode::InnerJoin); qbdsVendPackingSlipJour.addLink(fieldNum(VendPackingSlipJour, PurchId),fieldNum(PurchTable, PurchId)); qbdsVendPackingSlipJour.orderMode(OrderMode::GroupBy); qbdsVendPackingSlipJour.addGroupByField(fieldNum(PurchLine, ItemId)); qbdsVendPackingSlipJour.addRange(fieldNum(VendPackingSlipJour, DeliveryDate)).value(queryRange(fromDate, toDate)); qr = new QueryRun(qnew);

Was this reply helpful? Yes No
Martin Dráb 237,976 Most Valuable Professional on at

Like (1)

Report

I already mentioned one bug - that you join PurchTable and VendPackingSlipJour. The reason why you some records twice may be related - maybe these are orders included in more then a single packing slip.

If you add group by ItemId, you'll get a single record for each item ID and no other fields will be populated, therefore you won't get any information about orders or packing slips. That's clearly not what you want. By the way, you would have immediately seen the problem if you test your query. You really need to learn how to debug code; it's a crucial skill for a developer.

Was this reply helpful? Yes No
Dineshkarlekar 1,836 on at

Like (0)

Report

hi , martin

thanks for reply ,

can you please suggest how can i resolve this error , should i join vendpackingsliptrans with sales line using inventtransid . or any other table i have to use .

thanks,

Regards,

Dinesh

Was this reply helpful? Yes No