Find the physical date for item with site/warehouse combination through batch job in d365 :
class FirstReceiptDateItemWarehouseBatch extends RunBaseBatch
{
#DEFINE.CurrentVersion(1)
#DEFINE.Version1(1)
boolean canGoBatchJournal()
{
//true if the class is shown in the list of Journal types; otherwise, false.
return true;
}
protected void new()
{
super();
}
public container pack()
{
//Serializes the current instance of the RunBase class.
return [#CurrentVersion];
}
void run()
{
#OCCRetryCount
;
try
{
this.insertFirstReceiptDate();
}
catch (Exception::Deadlock)
{
retry;
}
catch (Exception::UpdateConflict)
{
if (appl.ttsLevel() == 0)
{
if (xSession::currentRetryCount() >= #RetryNum)
{
throw Exception::UpdateConflictNotRecovered;
}
else
{
retry;
}
}
else
{
throw Exception::UpdateConflict;
}
}
}
public boolean runsImpersonated()
{
return true;
}
public boolean unpack(container packedClass)
{
//Deserializes the packedClass parameter value to an instance of the RunBase class.
boolean isSuccessful;
Version version = RunBase::getVersion(packedClass);
;
switch (version)
{
case #CurrentVersion:
{
[version] = packedClass;
isSuccessful = true;
break;
}
default :
return false;
}
return isSuccessful;
}
server static FirstReceiptDateItemWarehouseBatch construct()
{
return new FirstReceiptDateItemWarehouseBatch();
}
client server static ClassDescription description()
{
return "ProductFirstReceiptDate";
}
public static void main(Args args)
{
FirstReceiptDateItemWarehouseBatch firstReceiptBatch = FirstReceiptDateItemWarehouseBatch::construct();
if (firstReceiptBatch.prompt())
{
firstReceiptBatch.runOperation();
}
}
public void insertFirstReceiptDate()
{
ItemSiteLocationTmp usvItemSiteLocationTmp;
ProductFirstReceiptDate usvProductFirstReceiptDate;
InventTable inventTable;
InventLocation inventLocation;
InventTrans inventTrans;
InventTransOrigin inventTransOrigin;
InventDim inventDim;
FirstReceiptDate usvFirstReceiptDate;
Counter cntProcessed;
Counter cntInserted;
setprefix(USVFirstReceiptDateItemWarehouseBatch::description());
insert_recordset ItemSiteLocationTmp (ItemId, InventSiteId, InventLocationId)
select ItemId from inventTrans // process only if Item transactions
group by inventTrans.ItemId, inventDim.InventSiteId, inventDim.inventLocationId
where inventTrans.StatusReceipt == StatusReceipt::Purchased
&& inventTrans.DatePhysical != dateNull() // must have date
join InventSiteId, InventLocationId from inventDim // process only if transactions in Site/Warehouse
where inventDim.inventDimId == inventTrans.inventDimId
&& inventDim.InventSiteId != ''
&& inventDim.inventLocationId != ''
exists join inventTransOrigin // process only if these types exist
where inventTransOrigin.RecId == inventTrans.InventTransOrigin
&& (inventTransOrigin.ReferenceCategory == InventTransType::TransferOrderReceive
|| inventTransOrigin.ReferenceCategory == InventTransType::InventTransaction
|| inventTransOrigin.ReferenceCategory == InventTransType::InventCounting
|| inventTransOrigin.ReferenceCategory == InventTransType::WHSWork
|| inventTransOrigin.ReferenceCategory == InventTransType::Purch);
ttscommit;
info(strfmt("@SYS76766", "@SYS74037", ItemSiteLocationTmp.RowCount()));
// process combinations (eliminating certain combinations)
while select ItemSiteLocationTmp
exists join inventTable
where inventTable.ItemId == usvItemSiteLocationTmp.ItemId
&& inventTable.ItemType == ItemType::Item // Items only
exists join inventLocation
where inventLocation.InventLocationType == InventLocationType::Standard // Default warehouses only
&& inventLocation.InventSiteId == usvItemSiteLocationTmp.InventSiteId
&& inventLocation.InventLocationId == ItemSiteLocationTmp.InventLocationId
notexists join usvProductFirstReceiptDate // process only if no FirstReceipt record
where ProductFirstReceiptDate.ItemId == ItemSiteLocationTmp.ItemId
&& ProductFirstReceiptDate.SiteId == ItemSiteLocationTmp.InventSiteId
&& ProductFirstReceiptDate.WarehouseId == ItemSiteLocationTmp.InventLocationId
{
cntprocessed++;
usvFirstReceiptDate = dateNull();
// find earliest DatePhysical for Item/Site/Warehouse
while select DatePhysical from inventTrans
order by DatePhysical asc
where inventTrans.ItemId == usvItemSiteLocationTmp.ItemId
&& inventTrans.StatusReceipt == StatusReceipt::Purchased
&& inventTrans.DatePhysical != dateNull() // must have date
join ReferenceCategory, ReferenceId, RecId from inventTransOrigin // process only if these types exist
where inventTransOrigin.RecId == inventTrans.InventTransOrigin
&& (inventTransOrigin.ReferenceCategory == InventTransType::TransferOrderReceive
|| inventTransOrigin.ReferenceCategory == InventTransType::InventTransaction
|| inventTransOrigin.ReferenceCategory == InventTransType::InventCounting
|| inventTransOrigin.ReferenceCategory == InventTransType::WHSWork
|| inventTransOrigin.ReferenceCategory == InventTransType::Purch)
join RecId from inventDim // process only if transactions in Site/Warehouse
where inventDim.inventDimId == inventTrans.inventDimId
&& inventDim.InventSiteId == ItemSiteLocationTmp.InventSiteId
&& inventDim.inventLocationId == ItemSiteLocationTmp.InventLocationId
{
if (FirstReceiptDate != dateNull())
{
break;
}
if (inventTransOrigin.ReferenceCategory == InventTransType::WHSWork)
{
WHSWorkTable whsWorkTable;
select firstonly RecId from whsWorkTable
where whsWorkTable.WorkId == inventTransOrigin.ReferenceId
&& whsWorkTable.WorkStatus == WHSWorkStatus::Closed
&& (whsWorkTable.WorkTransType == WHSWorkTransType::Purch
|| whsWorkTable.WorkTransType == WHSWorkTransType::TransferReceipt);
if (whsWorkTable.RecId)
{
usvFirstReceiptDate = inventTrans.DatePhysical;
}
}
else
{
usvFirstReceiptDate = inventTrans.DatePhysical;
}
}
if (FirstReceiptDate != dateNull())
{
ProductFirstReceiptDate ProductFirstReceiptDateInsert;
ProductFirstReceiptDateInsert.ItemId = ItemSiteLocationTmp.ItemId;
ProductFirstReceiptDateInsert.SiteId = ItemSiteLocationTmp.InventSiteId;
ProductFirstReceiptDateInsert.WarehouseId = ItemSiteLocationTmp.InventLocationId;
ProductFirstReceiptDateInsert.FirstReceiptDate = FirstReceiptDate;
ProductFirstReceiptDateInsert.insert();
cntInserted++;
}
}
info(strfmt("@SYS76766", "@SYS103900", cntProcessed));
info(strfmt("@SYS76766", "@SYS77068", cntInserted));
}
}
