Dynamics Community Forum Thread Details

Difficulty to get date difference

Hi Experts,

I am facing difficulty to get difference (workflowTrackingTableNew.CreatedDateTime - workflowTrackingTable.CreatedDateTime) in X++ code. I do not find in which place I should put. I can get this in SSRS expression but problem is if have to sum up (workflowTrackingTableNew.CreatedDateTime - workflowTrackingTable.CreatedDateTime) the divide number of purchase order. SSRS expression not allowed me. The last column expression is =DateDiff("d",Fields!PRWFDate.Value,Fields!POWFDate.Value)

 public void processReport()
        {   
            contract = this.parmDataContract();
            TransDate reportDate = contract.parmFromDate();
            VendGroupId vendGroup = contract.parmVendGroup();
            Timezone daxTimeZone = DateTimeUtil::getUserPreferredTimeZone();

            PurchReqTable purchReqTable;
            PurchTable purchTable;
            PurchReqLine purchReqLine;
            PurchLine purchLine;
            VendGroup vendGroupTable;
            WorkflowTrackingTable workflowTrackingTable, workflowTrackingTableNew;
            WorkflowTrackingStatusTable workflowTrackingStatusTable, workflowTrackingStatusTableNew;
            WorkflowTrackingCommentTable workflowTrackingCommentTable, workflowTrackingCommentTableNew;

            integer poCount;
            AmountMST dateDiffSum,totalDayDiff;

            delete_from tmpTable;

            // Loop through purchase requisitions within the specified date range
            while select PurchReqId, CreatedDateTime
    from purchReqTable
    where purchReqTable.RequisitionStatus == PurchReqRequisitionStatus::Closed
       && (purchReqTable.CreatedDateTime >= datetobeginUtcDateTime((reportDate - 7), daxTimeZone)
       && purchReqTable.CreatedDateTime <= datetoendUtcDateTime(reportDate, daxTimeZone))
            {
                tmpTable.clear();
                tmpTable.PRId = purchReqTable.PurchReqId;
                tmpTable.PRDate = DateTimeUtil::date(purchReqTable.CreatedDateTime);

                // Fetch workflow tracking for purchase requisitions
                while select RecId, User, CreatedDateTime, TrackingContext
        from workflowTrackingTable Order by CreatedDateTime desc
                where workflowTrackingTable.TrackingType == WorkflowTrackingType::Approval
        exists join workflowTrackingStatusTable
              where workflowTrackingStatusTable.RecId == workflowTrackingTable.WorkflowTrackingStatusTable
              && workflowTrackingStatusTable.ContextRecId == purchReqTable.RecId
              && workflowTrackingStatusTable.ContextTableId == tableNum(PurchReqTable)
                {
                    tmpTable.PRUser = workflowTrackingTable.User;
                    tmpTable.PRWFDate = workflowTrackingTable.CreatedDateTime;
                    tmpTable.PRTrackingContext = workflowTrackingTable.TrackingContext;
                }

                // Loop through purchase requisition lines to find corresponding purchase orders
                while select PurchId
        from purchReqLine
        where purchReqLine.PurchReqTable == purchReqTable.RecId
                {
                    // Find corresponding purchase order
                    select PurchId, CreatedDateTime
            from purchTable
            where purchTable.PurchId == purchReqLine.PurchId
               exists join vendGroupTable
                 where vendGroupTable.VendGroup == purchTable.VendGroup
                    && vendGroupTable.VendGroup == vendGroup;
                    {

                       
                        dateDiffSum = DateTimeUtil::date(purchTable.CreatedDateTime) - DateTimeUtil::date(purchReqTable.CreatedDateTime);
                    if(purchTable.RecId>0)
                    {
                        // Calculate the difference in days between PO and PR creation dates
                        if ((vendGroup == 'LOCAL' && dateDiffSum > 0) || (vendGroup == 'FOREIGN' && dateDiffSum > 0))
                       // if ((vendGroup == 'LOCAL') || (vendGroup == 'FOREIGN'))
                        {
                            tmpTable.POId = purchTable.PurchId;
                            tmpTable.PODate = DateTimeUtil::date(purchTable.CreatedDateTime);
                            tmpTable.DayDiff = dateDiffSum;

                            // Fetch workflow tracking for purchase orders
                            while select RecId, User, CreatedDateTime, TrackingContext
                    from workflowTrackingTableNew order by CreatedDateTime asc
                                where workflowTrackingTableNew.TrackingType == WorkflowTrackingType::Submission
                    exists join workflowTrackingStatusTableNew
                          where workflowTrackingStatusTableNew.RecId == workflowTrackingTableNew.WorkflowTrackingStatusTable
                          && workflowTrackingStatusTableNew.ContextRecId == purchTable.RecId
                          && workflowTrackingStatusTableNew.ContextTableId == tableNum(PurchTable)
                            {
                                tmpTable.POUser = workflowTrackingTableNew.User;
                                tmpTable.POWFDate = workflowTrackingTableNew.CreatedDateTime;
                                tmpTable.POTrackingContext = workflowTrackingTableNew.TrackingContext;
                                //  tmpTable.TotalDayDiff = workflowTrackingTable.CreatedDateTime;
                            }
                        }
                    }
                    }
                }

                tmpTable.insert(); // Insert the record after processing all data
            }

        }

Thanks,

Faiz

Categories:

Supply Chain Management

dateDiffSum = DateTimeUtil::date(purchTable.CreatedDateTime) - DateTimeUtil::date(purchReqTable.CreatedDateTime); if(purchTable.RecId>0) { // Calculate the difference in days between PO and PR creation dates if ((vendGroup == 'LOCAL' && dateDiffSum > 0) || (vendGroup == 'FOREIGN' && dateDiffSum > 0)) // if ((vendGroup == 'LOCAL') || (vendGroup == 'FOREIGN')) { tmpTable.POId = purchTable.PurchId; tmpTable.PODate = DateTimeUtil::date(purchTable.CreatedDateTime); tmpTable.DayDiff = dateDiffSum; // Fetch workflow tracking for purchase orders select RecId, User, CreatedDateTime, TrackingContext from workflowTrackingTableNew order by CreatedDateTime asc where workflowTrackingTableNew.TrackingType == WorkflowTrackingType::Submission exists join workflowTrackingStatusTableNew where workflowTrackingStatusTableNew.RecId == workflowTrackingTableNew.WorkflowTrackingStatusTable && workflowTrackingStatusTableNew.ContextRecId == purchTable.RecId && workflowTrackingStatusTableNew.ContextTableId == tableNum(PurchTable); { tmpTable.POUser = workflowTrackingTableNew.User; tmpTable.POWFDate = workflowTrackingTableNew.CreatedDateTime; tmpTable.POTrackingContext = workflowTrackingTableNew.TrackingContext; tmpTable.UTCDayDiff=DateTimeUtil::date(workflowTrackingTableNew.CreatedDateTime)-DateTimeUtil::date(workflowTrackingTable.CreatedDateTime); // tmpTable.TotalDayDiff = workflowTrackingTable.CreatedDateTime; } }

Quick Links

Leaderboard