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
