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
}
}
#TimeConstants
utcDateTime a = DateTimeUtil::newDateTime(mkDate(1,9,2024), 8*#SecondsPerHour);
utcDateTime b = DateTimeUtil::newDateTime(mkDate(5,9,2024), 13*#SecondsPerHour);
System.DateTime bNet = b;
System.TimeSpan diff = bNet.Subtract(a);
info(strFmt("%1 days, %2 hours", diff.Days, diff.Hours));
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;
PRWFDate=workflowTrackingTable.CreatedDateTime;
}
// 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
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=workflowTrackingTableNew.CreatedDateTime-workflowTrackingTable.CreatedDateTime;
tmpTable.UTCDayDiff=DateTimeUtil::date(workflowTrackingTableNew.CreatedDateTime)-DateTimeUtil::date(PRWFDate);
}
}
}
}
}
tmpTable.insert(); // Insert the record after processing all data
}
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;
}
}
Stay up to date on forum activity by subscribing. You can also customize your in-app and email Notification settings across all subscriptions.
André Arnaud de Cal... 291,151 Super User 2024 Season 2
Martin Dráb 229,993 Most Valuable Professional
nmaenpaa 101,156