Project invoice transactions in D365fo x++

 Project invoice transactions in D365fo x++

/// <summary>
/// Service Contract Invoice Details Report
/// Hassan Farooq - 29 Aug, 2025
/// The DP class of the report
/// </summary>
[
    SRSReportParameterAttribute(classStr(MK_RevenueReportContract))
]
public class MK_RevenueReportDP extends SrsReportDataProviderPreProcessTempDB
{
    MK_RevenueReportTmp tmpTable;
    
    [
        SRSReportDataSet('MK_RevenueReportTmp')
    ]
    public MK_RevenueReportTmp gettmpTable()
    {
        select tmpTable;
        return tmpTable;
    }

    public void processReport()
    {
        ProjProposalJour             projProposalJour;
        MK_DefaultDimensionView      defaultDimensionView;
        MK_LedgerDimensionCostCenter ledgerDimCostCenter;

        MK_RevenueReportContract contract = this.parmDataContract();
        TransDate   fromDate              = contract.parmFromDate();
        TransDate   toDate                = contract.parmToDate();
        List        vinNumList            = contract.parmVINNumList();
        List        workOrderList         = contract.parmWorkOrderList();
        List        invoiceAccountList    = contract.parminvoiceAccountList();

        Query query = new Query();
        QueryBuildDataSource qbdsProjInvoiceJour = query.addDataSource(tableNum(ProjInvoiceJour));
        qbdsProjInvoiceJour.addRange(fieldNum(ProjInvoiceJour, InvoiceDate)).value(queryValue(fromDate) + '..' + queryValue(toDate));

        QueryBuildDataSource qbdsWorkOrderTable = qbdsProjInvoiceJour.addDataSource(tableNum(EntAssetWorkOrderTable));
        qbdsWorkOrderTable.relations(false);
        qbdsWorkOrderTable.joinMode(JoinMode::InnerJoin);
        qbdsWorkOrderTable.addLink(fieldNum(ProjInvoiceJour, ProjInvoiceProjId), fieldNum(EntAssetWorkOrderTable, ProjInvoiceProjId));

        QueryBuildDataSource qbdsworkOrderView = qbdsWorkOrderTable.addDataSource(tableNum(MK_WorkOrderView));
        qbdsworkOrderView.relations(false);
        qbdsworkOrderView.joinMode(JoinMode::OuterJoin);
        qbdsworkOrderView.addLink(fieldNum(EntAssetWorkOrderTable, RecId), fieldNum(MK_WorkOrderView, WORecId));
        
        
        if (invoiceAccountList.elements())
        {
            qbdsProjInvoiceJour.addRange(fieldNum(ProjInvoiceJour, InvoiceAccount)).value(con2StrUnlimited(list2Con(invoiceAccountList)));
        }

        if (workOrderList.elements())
        {
            qbdsProjInvoiceJour.addRange(fieldNum(ProjInvoiceJour, ProjInvoiceId)).value(con2StrUnlimited(list2Con(workOrderList)));
        }

        if (vinNumList.elements())
        {
            qbdsworkOrderView.addRange(fieldNum(MK_WorkOrderView, AssetId)).value(con2StrUnlimited(list2Con(vinNumList)));
        }
        
        QueryRun queryRun = new QueryRun(query);
        while (queryRun.next())
        {
            ProjInvoiceJour         projInvoiceJour = queryRun.get(tableNum(ProjInvoiceJour));
            EntAssetWorkOrderTable  workOrderTable  = queryRun.get(tableNum(EntAssetWorkOrderTable));
            MK_WorkOrderView        workOrderView   = queryRun.get(tableNum(MK_WorkOrderView));
            CustTable               customerAccount = CustTable::find(workOrderTable.CustAccount);
            CustTable               invoiceAccount  = CustTable::find(projInvoiceJour.InvoiceAccount);

            this.insertHoursLines(projInvoiceJour,   workOrderTable, workOrderView, customerAccount, invoiceAccount);
            this.insertItemLines(projInvoiceJour,    workOrderTable, workOrderView, customerAccount, invoiceAccount);
            this.insertExpenseLines(projInvoiceJour, workOrderTable, workOrderView, customerAccount, invoiceAccount);
        }
             
        // Update Cost Center details
        ProjInvoiceJour ProjInvoiceJour;
        GeneralJournalEntry GeneralJournalEntry;
        GeneralJournalAccountEntry GeneralJournalAccountEntry;

        update_recordset tmpTable
        setting CostCenter = ledgerDimCostCenter.CostCenter
        join ProjInvoiceJour
        where  ProjInvoiceJour.PROJINVOICEID == tmpTable.ProjInvoiceId
        && ProjInvoiceJour.InvoiceDate == tmpTable.InvoiceDate
        join GeneralJournalEntry
        where ProjInvoiceJour.LEDGERVOUCHER == GeneralJournalEntry.SUBLEDGERVOUCHER
        && ProjInvoiceJour.INVOICEDATE == GeneralJournalEntry.ACCOUNTINGDATE
        join GeneralJournalAccountEntry
        where GeneralJournalAccountEntry.GeneralJournalEntry == GeneralJournalEntry.RecId
        &&  GeneralJournalAccountEntry.PostingType == LedgerPostingType::ProjTurnover
        join ledgerDimCostCenter
        where GeneralJournalAccountEntry.LEDGERDIMENSION == ledgerDimCostCenter.LedgerDimenison;
        
       
        // Update Project Proposal CreatedBy details
        update_recordset tmpTable
        setting ProjUserId = projProposalJour.CreatedBy
        join projProposalJour
        where tmpTable.ProposalId == projProposalJour.ProposalId;
    }

    private void insertHoursLines(ProjInvoiceJour _projInvoiceJour, EntAssetWorkOrderTable _workOrderTable, MK_workOrderView _workOrderView, CustTable _CustomerAccount, CustTable _InvoiceAccount)
    {
        ProjInvoiceEmpl projInvoiceEmpl;
        ProjTrans projTrans;

        while select projInvoiceEmpl
        where projInvoiceEmpl.ProjInvoiceId == _projInvoiceJour.ProjInvoiceId
        && projInvoiceEmpl.InvoiceDate    == _projInvoiceJour.InvoiceDate
        {
            tmpTable.clear();
            tmpTable.LineType               = 'Hour';
            tmpTable.ProposalId             = _projInvoiceJour.ProposalId;
            tmpTable.ProjInvoiceId          = _projInvoiceJour.ProjInvoiceId;
            tmpTable.InvoiceDate            = _projInvoiceJour.InvoiceDate;
            tmpTable.InvoiceAccount         = _projInvoiceJour.InvoiceAccount;
            tmpTable.WorkOrderId            = _projInvoiceJour.ProjInvoiceProjId;
            tmpTable.Voucher                = _projInvoiceJour.LedgerVoucher;
            tmpTable.LedgerDefaultDimension = _projInvoiceJour.LedgerDefaultDimension;
            tmpTable.VINNumber              = _workOrderView.AssetId;

            tmpTable.CustAccount            = _CustomerAccount.AccountNum;
            tmpTable.CustName               = _CustomerAccount.name();
            tmpTable.CustGroup              = _CustomerAccount.CustGroup;
            tmpTable.InvoiceAccount         = _InvoiceAccount.AccountNum;
            tmpTable.InvoiceAccountName     = _InvoiceAccount.name();

            projTrans = ProjTrans::newProjInvoiceEmpl(projInvoiceEmpl);
            tmpTable.LedgerDefaultDimension = projTrans.defaultDimension();
            tmpTable.CategoryId             = projInvoiceEmpl.CategoryId;
            tmpTable.Txt                    = projInvoiceEmpl.Txt;
            tmpTable.Qty                    = projInvoiceEmpl.Qty;
            tmpTable.SalesPrice             = projInvoiceEmpl.salesPrice();
            tmpTable.LineAmount             = projInvoiceEmpl.LineAmount;
            tmpTable.TaxAmount              = projInvoiceEmpl.TaxAmount;
            
            tmpTable.insert();
        }
    }

    private void insertItemLines(ProjInvoiceJour _projInvoiceJour, EntAssetWorkOrderTable _workOrderTable, MK_workOrderView _workOrderView, CustTable _CustomerAccount, CustTable _InvoiceAccount)
    {
        ProjInvoiceItem projInvoiceItem;

        while select projInvoiceItem
            where projInvoiceItem.ProjInvoiceId == _projInvoiceJour.ProjInvoiceId
            && projInvoiceItem.InvoiceDate    == _projInvoiceJour.InvoiceDate
        {
            
            tmpTable.clear();
            tmpTable.LineType               = 'Item';
            tmpTable.ProposalId             = _projInvoiceJour.ProposalId;
            tmpTable.ProjInvoiceId          = _projInvoiceJour.ProjInvoiceId;
            tmpTable.InvoiceDate            = _projInvoiceJour.InvoiceDate;
            tmpTable.InvoiceAccount         = _projInvoiceJour.InvoiceAccount;
            tmpTable.WorkOrderId            = _projInvoiceJour.ProjInvoiceProjId;
            tmpTable.Voucher                = _projInvoiceJour.LedgerVoucher;
            tmpTable.LedgerDefaultDimension = _projInvoiceJour.LedgerDefaultDimension;
            tmpTable.VINNumber              = _workOrderView.AssetId;

            tmpTable.CustAccount            = _CustomerAccount.AccountNum;
            tmpTable.CustName               = _CustomerAccount.name();
            tmpTable.CustGroup              = _CustomerAccount.CustGroup;
            tmpTable.InvoiceAccount         = _InvoiceAccount.AccountNum;
            tmpTable.InvoiceAccountName     = _InvoiceAccount.name();
            
            ProjTrans projTrans = ProjTrans::newProjInvoiceItem(projInvoiceItem);
            tmpTable.ItemId                 = projInvoiceItem.ItemId;
            tmpTable.LedgerDefaultDimension = projInvoiceItem.DefaultDimension;
            tmpTable.CategoryId             = projInvoiceItem.CategoryId;
            tmpTable.Txt                    = projInvoiceItem.Txt;
            tmpTable.Qty                    = projInvoiceItem.Qty;
            tmpTable.SalesPrice             = projInvoiceItem.salesPrice();
            tmpTable.LineAmount             = projInvoiceItem.LineAmount;
            tmpTable.TaxAmount              = projInvoiceItem.TaxAmount;

            tmpTable.insert();
        }
    }

    private void insertExpenseLines(ProjInvoiceJour _projInvoiceJour, EntAssetWorkOrderTable _workOrderTable, MK_workOrderView _workOrderView, CustTable _CustomerAccount, CustTable _InvoiceAccount)
    {
        ProjInvoiceCost projInvoiceCost;

        while select projInvoiceCost
            where projInvoiceCost.ProjInvoiceId == _projInvoiceJour.ProjInvoiceId
            && projInvoiceCost.InvoiceDate    == _projInvoiceJour.InvoiceDate
        {
            tmpTable.clear();

            tmpTable.LineType               = 'Expense';
            tmpTable.ProposalId             = _projInvoiceJour.ProposalId;
            tmpTable.ProjInvoiceId          = _projInvoiceJour.ProjInvoiceId;
            tmpTable.InvoiceDate            = _projInvoiceJour.InvoiceDate;
            tmpTable.InvoiceAccount         = _projInvoiceJour.InvoiceAccount;
            tmpTable.WorkOrderId            = _projInvoiceJour.ProjInvoiceProjId;
            tmpTable.Voucher                = _projInvoiceJour.LedgerVoucher;
            tmpTable.LedgerDefaultDimension = _projInvoiceJour.LedgerDefaultDimension;
            tmpTable.VINNumber              = _workOrderView.AssetId;
            
            tmpTable.CustAccount            = _CustomerAccount.AccountNum;
            tmpTable.CustName               = _CustomerAccount.name();
            tmpTable.CustGroup              = _CustomerAccount.CustGroup;
            tmpTable.InvoiceAccount         = _InvoiceAccount.AccountNum;
            tmpTable.InvoiceAccountName     = _InvoiceAccount.name();

            
            ProjTrans projTrans = ProjTrans::newProjInvoiceCost(projInvoiceCost);
            tmpTable.LedgerDefaultDimension = projInvoiceCost.DefaultDimension;
            tmpTable.CategoryId             = projInvoiceCost.CategoryId;
            tmpTable.Txt                    = projInvoiceCost.Txt;
            tmpTable.Qty                    = projInvoiceCost.Qty;
            tmpTable.SalesPrice             = projInvoiceCost.salesPrice();
            tmpTable.LineAmount             = projInvoiceCost.LineAmount;
            tmpTable.TaxAmount              = projInvoiceCost.TaxAmount;

            tmpTable.insert();
        }
    }

}

Comments

Post a Comment

Popular posts from this blog

D365FO – AX – X++ –Refresh, Reread, Research, and ExecuteQuery

 D365FO – AX – X++ – Tutorial: Choosing Between Refresh, Reread, Research, and ExecuteQuery X++ developers often face challenges with the four datasource methods—refresh, reread, research, and executeQuery—in Dynamics 365 Finance and Operations (D365FO). Regardless of their seniority in AX, developers frequently make mistakes in the sequence of calling these methods. To address this, I've created a brief hands-on tutorial illustrating the typical use cases for each method. I've organized the methods based on their impact on the displayed grid rows. 1. Common Mistakes: Developers often incorrectly call two of these methods in the following order: - formDataSource.refresh() followed by formDataSource.research(), or - formDataSource.reread() followed by formDataSource.research(), or - formDataSource.research() followed by formDataSource.executeQuery(), or - formDataSource.research() followed by formDataSource.refresh() / formDataSource.reread(). These sequences are either entirely...
Read more

Create Inventory Journal through Code in D365FO X++

 Create Inventory Journal through Code in D365FO X++ Code:  internal final class Cls_FPS_MOVJRNL {     /// <summary>     /// Class entry point. The system will call this method when a designated menu      /// is selected or when execution starts and this class is set as the startup class.     /// </summary>     /// <param name = "_args">The specified arguments.</param>     public static void main(Args _args)     {         CBLFPS_StkAdjTbl       CBLFPS_StkAdjTbl;         ttsbegin;             // Create journal table             InventJournalTable inventJournalTable = Cls_FPS_MOVJRNL::createJournalTable();                          // Initialize journal trans             ...
Read more

SalesLine Reservation in D365fo x++

SalesLine Reservation in D365fo x++ SQL: /// <summary> /// reserve sales line /// </summary> /// <param name = "_salesline">SalesLine</param> public static void reserveSalesLine(SalesLine _salesline) { InventBatch inventBatch; InventSum inventSum; SalesLine salesLine; InventDim inventDim; InventTable inventTable; int daysLeftforExpiry = 0; int shelflife; int rule = 0; int acceptedPR = 0; Qty qtyReserve = 0; Qty saleLineQty = 0; DSAPickingRulePerId pickingrule; boolean qtyReservedCheck = false; salesLine = _salesline; pickingrule = _salesline.salesTable().DSAPickingRule; if(pickingrule == '') { warning("Picking rule is not available on sales order for reservation."); } else { ...
Read more