if(purchTable.purchTable_ExtFields().RecId != 0)
{
PurchTable_Ext extFields = purchTable.purchTable_ExtFields();
extFields.selectForUpdate(true);
extFields.OfferNumber = agreementHeaderDefault.ExternalReference;
extFields.doUpdate();
}
else
{
PurchTable_Ext extNew;
extNew.OfferNumber = agreementHeaderDefault.ExternalReference;;
purchTable.packAGSPurchTable_ExtFields(extNew);
extNew.SysExtensionSerializerExtensionMap::insertAfterBaseTable(purchTable);
}
Author: chaithu36
Read data from Azure file storage
List headerFileList = AzureFileShareService.listFiles(ledgerParameters.AzureConcurIntegrationFilePath, ledgerParameters.AzureConcurFileName);
ListEnumerator fileListEnumerator = headerFileList .getEnumerator();
while(fileListEnumerator.moveNext())
{
file = fileListEnumerator.current();
str fileName = ledgerParameters.AzureConcurFileName + ‘\’ + file;
using(System.IO.MemoryStream memoryStream = AzureFileShareService.readFile(file, ledgerParameters.AzureConcurIntegrationFilePath))
{
Excel import from Browser using Runbase D365
using System.IO;
using OfficeOpenXml;
using OfficeOpenXml.ExcelPackage;
using OfficeOpenXml.ExcelRange;
class SPTSupplierPortalPOUpdateRunBase extends RunBaseBatch
{
Filename ItemFileName;
Filename filename;
DialogField dialogFilename;
System.IO.Stream stream;
FileUploadTemporaryStorageResult fileUploadResult;
OfficeOpenXml.ExcelRange range;
CommaTextStreamIo commaIo;
RecordInsertList rsl;
boolean newIteration;
int line,
lineimported,lineerror, lineskipped;
Counter loop;
FileUploadBuild dialogFileUpload;
FileUpload fileUpload;
DialogRunbase dialog;
boolean poUpdate;
#define.CurrentVersion(1)
#define.Version1(1)
#localmacro.CurrentList
fileName
#endmacro
void create(int row)
{
str itemId;
price inventPrice;
str recId;
str plant;
str Sparton_Part_No, Commit_Flag, Manufacturer, Mfg_Part_Number, Supplier_Notes1;
str Lead_Time;
str Suggested_Dock_Date;
str Suggested_Dock_Qty;
InventTableModule inventTableModule;
SPAVendorPortal vendorPortal;
recId = range.get_Item(row, 1).Value;
plant = range.get_Item(row, 2).Value;
Sparton_Part_No = range.get_Item(row, 3).Value;
//Suggested_Dock_Date = range.get_Item(row, 4).Value;
Suggested_Dock_Qty = range.get_Item(row, 5).Value;
Commit_Flag = range.get_Item(row, 6).Value;
Manufacturer = range.get_Item(row, 7).Value;
Mfg_Part_Number = range.get_Item(row, 8).Value;
Supplier_Notes1 = range.get_Item(row, 9).Value;
Lead_Time = range.get_Item(row, 10).Value;
try
{
//Info(strFmt("%1-%2-%3-%4-%5-%6-%7-%8-%9-%10", recId, plant, Sparton_Part_No, Commit_Flag, Manufacturer, Mfg_Part_Number, Supplier_Notes1, Lead_Time, Suggested_Dock_Date, Suggested_Dock_Qty));
select forupdate vendorPortal
where vendorPortal.RecId == str2Int64(recId);
if(vendorPortal)
{
ttsbegin;
vendorPortal.spaPlant = plant;
vendorPortal.ItemManPartCommentary = Sparton_Part_No;
//vendorPortal.SPADeliveryDate = Suggested_Dock_Date;
vendorPortal.spaQtyOrdered = str2Int(Suggested_Dock_Qty);
vendorPortal.ActionMarked = NoYes::Yes;
vendorPortal.ItemManMfg = Manufacturer;
vendorPortal.ItemManPartNo = Mfg_Part_Number;
vendorPortal.spaNotes = Supplier_Notes1;
vendorPortal.LeadTime = str2Int(Lead_Time);
vendorPortal.spaProcessed = NoYes::No;
vendorPortal.update();
poUpdate = true;
ttscommit;
}
Info("updated");
}
catch
{
lineerror ++;
// ttsAbort;
}
}
void import()
{
Container con;
InventTable inventTable;
;
setPrefix("Import master data");
poUpdate = false;
if (this.openFile())
{
using (ExcelPackage Package = new ExcelPackage(stream))
{
int rowCount, i, endRow, startRow;
Package.Load(stream);
ExcelWorksheet worksheet = package.get_Workbook().get_Worksheets().get_Item(1);
range = worksheet.Cells;
endRow = worksheet.Dimension.End.Row;
startRow = worksheet.Dimension.Start.Row;
rowCount = endRow - startRow + 1;
try
{
for (i = 2; i<= rowCount; i++)
{
setPrefix(strFmt("Line %1", i));
this.create(i);
}
}
catch
{
throw error("Error - Import template");
}
}
}
if(poUpdate)
{
// SPASupplierPortalPOUpdateService service = new SPASupplierPortalPOUpdateService:: ();
//service.processSupplierPO();
MenuFunction menuFunction;
//salestable _SalesTable = SalesTable::find("SO00125111");
Args args = new Args();
//args.record(_SalesTable);
menuFunction = new MenuFunction(menuitemActionStr(SPASupplierPortalPOUpdateController), MenuItemType::Action);
if (menuFunction && menuFunction.checkAccessRights())
{
menuFunction.run(args);
}
}
info(strfmt("%1 record(s) imported", lineimported));
info(strfmt("%1 record(s) fail", lineerror));
}
boolean openFile()
{
boolean ret = false;
if (fileUploadResult != null && fileUploadResult.getUploadStatus())
{
stream = fileUploadResult.openResult();
ret = true;
}
else
{
throw error(strfmt("@SYS62207",filename));
}
return ret;
}
public Object dialog()
{
dialog = super();
DialogGroup dialogGroup = dialog.addGroup('Upload file');
FormBuildControl formBuildControl = dialog.formBuildDesign().control(dialogGroup.name());
dialogFileUpload = formBuildControl.addControlEx(classstr(FileUpload), 'FileUpload');
dialogFileUpload.style(FileUploadStyle::MinimalWithFilename);
dialogFileUpload.fileNameLabel("@SYS308842");
dialogFileUpload.fileTypesAccepted(".xlsx");
return dialog;
}
/// <summary>
/// Disables the dialog Ok button until the file upload is complete.
/// </summary>
/// <param name="_dialog">The <c>Runbase</c> dialog object.</param>
public void dialogPostRun(DialogRunbase _dialog)
{
fileUpload = _dialog.formRun().control(_dialog.formRun().controlId('FileUpload'));
fileUpload.notifyUploadCompleted += eventhandler(this.uploadCompleted);
this.setDialogOkButtonEnabled(_dialog, false);
}
/// <summary>
/// After the file has been uploaded, the Ok button is enabled.
/// </summary>
public void uploadCompleted()
{
filename = fileUpload.fileName();
this.setDialogOkButtonEnabled(dialog, true);
fileUploadResult = fileUpload.getFileUploadResult();
fileUpload.notifyUploadCompleted -= eventhandler(this.UploadCompleted);
}
/// <summary>
/// Enables or disables the dialog Ok button.
/// </summary>
/// <param name = "_dialog">The <c>Runbase</c> dialog object.</param>
/// <param name = "_isEnabled">Indicates to enable or disable the Ok button.</param>
protected void setDialogOkButtonEnabled(DialogRunbase _dialog, boolean _isEnabled)
{
FormControl okButtonControl = _dialog.formRun().control(_dialog.formRun().controlId('OkButton'));
if (okButtonControl)
{
okButtonControl.enabled(_isEnabled);
}
}
public container pack()
{
return [#CurrentVersion,#CurrentList];
}
public boolean unpack(container packedClass)
{
Version version = runbase::getVersion(packedClass);
;
switch (version)
{
case #CurrentVersion:
[version,#CurrentList] = packedClass;
break;
default:
return false;
}
return true;
}
public void run()
{
this.import();
super();
}
static void main(Args _args)
{
SPTSupplierPortalPOUpdateRunBase import;
FormRun formRun;
Args args;
;
import = new SPTSupplierPortalPOUpdateRunBase();
if (import.prompt())
{
import.run();
}
}
public ClassDescription caption()
{
return 'Import template';
}
}
Json
using System.Net;
using System.Collections.Generic;
using System.IO;
using System.Linq;
using System.Net;
using System.Text;
using System.Xml;
using System.Web.Script.Serialization;
using System.Xml.Serialization;
/// <summary>
/// Accuvax Service class
/// </summary>
class JsonService extends SysOperationServiceBase
{
/// <summary>
/// RunService method
/// </summary>
/// <param name = “_dataContract”>JsonContract</param>
public void runService(JsonContract _dataContract)
{
InventJournalTable inventJournalTable;
InventJournalTrans inventJournalTrans;
InventJournalName inventJournalName;
InventDim inventDim;
JournalCheckPost journalCheckPost;
InventParameters inventParameters = InventParameters::find();
utcdatetime startDate = _dataContract.parmStartDateTime();
utcdatetime endDate = _dataContract.parmendDateTime();
Name scope = _dataContract.parmScope();
NoYesId missingBatch = _dataContract.parmMissingBatches();
NoYesId batchJobFrequency = _dataContract.parmBatchjobFrequency();
int executeJob = _dataContract.parmexExcuteJob();
int totalRecords, totalSkipped, totalLines, totalRecordsError, totalReloadTypeError;
if(executeJob)
{
startDate = inventParameters.BatchJobEndDateTime;
if(DateTimeUtil::date(startDate) == dateNull())
{
startDate = DateTimeUtil::addMinutes(DateTimeUtil::getSystemDateTime(), -batchJobFrequency);
}
endDate = DateTimeUtil::getSystemDateTime();
startDate = DateTimeUtil::applyTimeZoneOffset(startDate, Timezone::GMT_CASABLANCA);// DateTimeUtil::getUserPreferredTimeZone());
endDate = DateTimeUtil::applyTimeZoneOffset(endDate, Timezone::GMT_CASABLANCA);// DateTimeUtil::getUserPreferredTimeZone());
}
else
{
startDate = DateTimeUtil::applyTimeZoneOffset(startDate, Timezone::GMT_CASABLANCA);//DateTimeUtil::getUserPreferredTimeZone());
endDate = DateTimeUtil::applyTimeZoneOffset(endDate, Timezone::GMT_CASABLANCA);//DateTimeUtil::getUserPreferredTimeZone());
}
if(DateTimeUtil::date(endDate) == dateNull())
{
endDate = DateTimeUtil::getSystemDateTime();
}
Info(strFmt(“Start date – %1 UTC, end date – %2 UTC”, startDate, endDate));
str url = @’https://my.accuvax.com/api/connect/usage_transactions.json?scope=’+ scope +’&start_date_time=’
+ date2Str(DateTimeUtil::date(startDate), 321,DateDay::Digits2,DateSeparator::Hyphen,DateMonth::Digits2, DateSeparator::Hyphen, DateYear::Digits4)
+’T’+time2StrHMS(DateTimeUtil::time(startDate))
+’&end_date_time=’
+ date2Str(DateTimeUtil::date(endDate), 321,DateDay::Digits2,DateSeparator::Hyphen,DateMonth::Digits2, DateSeparator::Hyphen, DateYear::Digits4)
+’T’+time2StrHMS(DateTimeUtil::time(endDate))
;
str user = ‘pediatric-assoc-d365’;
str pass = ‘9xxuBW8n475GUA’;
System.Text.Encoding encoding = System.Text.Encoding::UTF8;
System.Byte[] bytes = encoding.GetBytes(user + ‘:’ + pass);
System.String base64 = System.Convert::ToBase64String(bytes);
System.String contentType = @’application/json’;
System.String headerKey = ‘Authorization’;
System.String headerValue = ‘Basic ‘ + base64;
System.String FacilityHeaderKey = ‘FACILITY’;
System.String facilityHeaderValue = ‘hq’;
System.Net.WebRequest request = System.Net.WebRequest::Create(url);
System.Net.WebHeaderCollection headers = request.Headers;
headers.Add(headerKey,headerValue);
request.ContentType = contentType;
System.Net.WebHeaderCollection facilityHeaders = request.Headers;
facilityHeaders.Add(FacilityHeaderKey,facilityHeaderValue);
System.Net.WebResponse response = request.GetResponse();
System.IO.StreamReader reader = new System.IO.StreamReader(
response.GetResponseStream());
str wsResponse = reader.ReadToEnd();
boolean newTransaction = true;
mapEnumerator mapEnumerator;
Map jsonData;
JournalNameId journalNameType;
container conJournalHeader;
try
{
jsonData = RetailCommonWebAPI::getMapFromJsonString(wsResponse);
}
catch
{
throw error(“Json max length excessed, please change date intervals to get enough data only”);
}
mapEnumerator = jsonData.getEnumerator();
while (mapEnumerator.moveNext())
{
switch (mapEnumerator.currentKey())
{
case ‘transactions’ :
container jsoncon = mapEnumerator.currentValue();
if(conPeek(jsoncon, 1) == “”)
{
throw Info(“No data available in specified date ranges.”);
}
List jsonList = List::create(jsoncon);
ListEnumerator mapenum = jsonList.getEnumerator();
str errorMessage = ”;
int i = 1;
try
{
while (mapenum.moveNext())
{
errorMessage = ”;
totalRecords++;
container conJson = mapenum.current();
name transaction_id = conPeek(conJson, conFind(conJson, ‘transaction_id’) + 1);
Name transaction_type = conPeek(conJson, conFind(conJson, ‘transaction_type’) + 1);
Name created_at = conPeek(conJson, conFind(conJson, ‘created_at’) + 1);
Name site_name = conPeek(conJson, conFind(conJson, ‘site_name’) + 1);
Name accuvax_name = conPeek(conJson, conFind(conJson, ‘accuvax_name’) + 1);
Name program = conPeek(conJson, conFind(conJson, ‘program’) + 1);
int quantity = conPeek(conJson, conFind(conJson, ‘quantity’) + 1);
Name product_ndc = conPeek(conJson, conFind(conJson, ‘product_ndc’) + 1);
Name package_ndc = conPeek(conJson, conFind(conJson, ‘package_ndc’) + 1);
Name dosage = conPeek(conJson, conFind(conJson, ‘dosage’) + 1);
Name form_factor = conPeek(conJson, conFind(conJson, ‘form_factor’) + 1);
Name lot_number = conPeek(conJson, conFind(conJson, ‘lot_number’) + 1);
Name expiration_date = conPeek(conJson, conFind(conJson, ‘expiration_date’) + 1);
container condate = str2con(created_at,’T’);
str dateStr = conPeek(condate, 1);
container conTimeValue = str2con(conPeek(condate, 2), “:”);
container conTimeValueSec = str2con(conPeek(conTimeValue, 3), “.”);
container condateValue = str2con(dateStr, ‘-‘);
str myDate = strFmt(“%1/%2/%3 %4”, conPeek(condateValue, 3), conPeek(condateValue, 2), conPeek(condateValue, 1),
strFmt(“%1:%2:22”, conPeek(conTimeValue, 1), conPeek(conTimeValue, 2), str2Num(conPeek(conTimeValueSec, 1))+10));
utcdatetime created_atDate = str2Datetime(myDate, 123);
AccuvaxData accuvaxData;
real balQty = 0;
boolean createRecord = true;
select firstonly accuvaxData
where accuvaxData.Site == site_name
&& accuvaxData.Kiosk == accuvax_name
&& accuvaxData.ProductNDC == product_ndc
&& accuvaxData.PackageNDC == package_ndc
&& accuvaxData.Program == program
&& accuvaxData.Transactionid == transaction_id
&& accuvaxData.TransactionTypestr == transaction_type;
if(accuvaxData)
{
Qty accuvaxDataQty = abs(accuvaxData.Qty);
balQty = quantity > accuvaxDataQty ? quantity – accuvaxDataQty : accuvaxDataQty – quantity;
createRecord = balQty == 0 ? false : true;
if(createRecord == false)
{
totalSkipped++;
}
}
else
{
balQty = quantity;
}
if(createRecord)
{
accuvaxData.Site = site_name;
accuvaxData.Kiosk = accuvax_name;
accuvaxData.ProductNDC = product_ndc;
accuvaxData.PackageNDC = package_ndc;
accuvaxData.Program = program;
accuvaxData.Transactionid = transaction_id;
accuvaxData.TransactionTypestr = transaction_type;
accuvaxData.CreateDateTime = created_atDate;
accuvaxData.Qty = – balQty;
accuvaxData.Dosage = dosage;
accuvaxData.FormFactor = form_factor;
accuvaxData.LotNumber = lot_number;
accuvaxData.ExpirationDateTime = str2Datetime(expiration_date, 213);
InventTable inventTable;
select firstonly ItemId from inventTable
where inventTable.ProductNDC == accuvaxData.ProductNDC
&& inventTable.PackageNDC == accuvaxData.PackageNDC
&& inventTable.ProgramType == accuvaxData.Program;
if(!inventTable.ItemId)
{
errorMessage += ‘\n’+ strFmt(“For transactionid : %4, transaction_type : %5 – Released product cannot be found based on combination : Package NDC : %1, Product NDC : %2, Program : %3”,accuvaxData.PackageNDC, accuvaxData.ProductNDC, accuvaxData.Program, transaction_id, transaction_type);
}
WMSLocation wmsLocation;
if(accuvaxData.Kiosk == ’01’)
{
Info(“CAWESX01”);
}
if(transaction_id == ‘20651013’)
{
info(accuvaxData.Kiosk);
}
select firstonly inventLocationId, wMSLocationId from wmsLocation
where wmsLocation.wMSLocationId == strRem(accuvaxData.Kiosk, ‘ ‘);
InventLocation inventLocation;
select firstonly InventSiteId, InventLocationId from inventLocation
where inventLocation.InventLocationId == wmsLocation.inventLocationId;
if(!inventLocation.InventSiteId)
{
errorMessage += ‘\n’+ strFmt(“For transactionid : %2, transaction_type : %3 – Site id cannot be found based on Location : %1”, accuvaxData.Kiosk, transaction_id, transaction_type);
}
if(!inventLocation.InventLocationId)
{
errorMessage += ‘\n’+ strFmt(“For transactionid : %2, transaction_type : %3 – Warehouse id cannot be found based on Location : %1”, accuvaxData.Kiosk, transaction_id, transaction_type);
}
InventBatch inventBatch;
select forupdate firstonly inventBatch
where inventBatch.inventBatchId == accuvaxData.LotNumber
&& inventBatch.itemId == inventTable.ItemId;
if(!missingBatch &&!inventBatch)
{
errorMessage += ‘\n’+ strFmt(“For transactionid : %2, transaction_type : %4 – Batch number : %1 cannot be found for item : %3”, accuvaxData.LotNumber, transaction_id, inventTable.ItemId, transaction_type);
}
if( errorMessage)
{
totalRecordsError++;
}
if(!errorMessage)
{
accuvaxData.insert();
boolean newTransacationType = false;
if(transaction_type like ‘Dispense*’)
{
transaction_type = ‘Dispense’;
journalNameType = inventParameters.DispenseJournalId;
}
else if(transaction_type like ‘Spoiled*’)
{
transaction_type = ‘Spoiled’;
journalNameType = inventParameters.poilJournalId;
}
else if(transaction_type like ‘Remove*’)
{
transaction_type = ‘Remove’;
journalNameType = inventParameters.RemoveJournalId;
}
else
{
transaction_type = ”;
totalReloadTypeError++;
}
newTransacationType = true;
select firstonly inventJournalName
where inventJournalName.JournalNameId == journalNameType;
if(!inventJournalName)
{
throw Error (strFmt(“Journal name is not available in transaction type %1”,transaction_type));
}
if(transaction_type)
{
InventSite inventSite = InventSite::find(inventLocation.InventSiteId);
ttsbegin;
if(conFind(conJournalHeader, strFmt(“%1-%2”, transaction_type,inventSite.PostInventJournalAuto)) != 0)
{
select firstonly inventJournalTable
where inventJournalTable.JournalId == conPeek(conJournalHeader, conFind(conJournalHeader, strFmt(“%1-%2”, transaction_type,inventSite.PostInventJournalAuto))+1);
}
else
{
ttsbegin;
inventJournalTable.clear();
inventJournalTable.initFromInventJournalName(inventJournalName);
inventJournalTable.insert();
ttscommit;
conJournalHeader = conIns(conJournalHeader, i, strFmt(“%1-%2”,transaction_type, inventSite.PostInventJournalAuto)); i++;
conJournalHeader = conIns(conJournalHeader, i, inventJournalTable.JournalId);i++;
info(strFmt(‘Movement journal %1 created successfully’, inventJournalTable.JournalId));
}
inventJournalTrans.clear();
inventJournalTrans.initFromInventJournalTable(inventJournalTable);
inventJournalTrans.TransDate = DateTimeUtil::date(DateTimeUtil::applyTimeZoneOffset(created_atDate , DateTimeUtil::getUserPreferredTimeZone()));
inventJournalTrans.ItemId = inventTable.ItemId;
inventJournalTrans.initFromInventTable(InventTable::find( inventJournalTrans.ItemId));
inventJournalTrans.modifiedField(fieldNum(InventJournalTrans, ItemId));
inventJournalTrans.Qty = accuvaxData.Qty;
inventJournalTrans.ProjTransId = transaction_id;
inventDim.wMSLocationId = wmsLocation.wMSLocationId;// accuvaxData.Kiosk;
inventDim.InventLocationId = wmsLocation.inventLocationId;
inventDim.InventSiteId = inventLocation.InventSiteId;
if(missingBatch && !inventBatch)
{
ttsbegin;
inventBatch.inventBatchId = accuvaxData.LotNumber;
inventBatch.itemId = inventTable.ItemId;
inventBatch.expDate = DateTimeUtil::date(accuvaxData.ExpirationDateTime);
inventBatch.insert();
ttscommit;
}
if(inventBatch && !missingBatch)
{
ttsbegin;
inventBatch.expDate = str2Date(expiration_date, 213);
inventBatch.update();
ttscommit;
}
inventDim.inventBatchId = accuvaxData.LotNumber;
inventJournalTrans.InventDimId = inventDim::findOrCreate(inventDim).inventDimId;
inventJournalTrans.LedgerDimension = inventJournalName.LedgerDimension;
inventJournalTrans.insert();
if(inventParameters && this.isExecutingInBatch())
{
inventParameters.selectForUpdate(true);
inventParameters.BatchJobEndDateTime = endDate;
inventParameters.update();
}
ttscommit;
totalLines++;
}
}
if(errorMessage)
{
Error(errorMessage);
}
}
}
}
catch
{
Error(errorMessage);
}
break;
}
if(conPeek(conJournalHeader, 1) != 0)
{
int i;
for (i=1; i <= conlen(conJournalHeader); i++)
{
InventJournalTrans inventJournalTransLoc;
InventJournalTable inventJournalTableLoc = InventJournalTable::find(conPeek(conJournalHeader, i), true);
if(inventJournalTableLoc)
{
ttsbegin;
select count(RecId) from inventJournalTransLoc
where inventJournalTransLoc.JournalId == inventJournalTableLoc.JournalId;
inventJournalTableLoc.NumOfLines = any2int(inventJournalTransLoc.RecId);
inventJournalTableLoc.update();
ttscommit;
inventDim inventDimLoc;
InventSite inventSite;
select firstonly inventJournalTransLoc
where inventJournalTransLoc.JournalId == inventJournalTableLoc.JournalId
join inventDimLoc where inventDimLoc.inventDimId == inventJournalTransLoc.InventDimId
join inventSite where inventSite.SiteId == inventDimLoc.InventSiteId
&& inventSite.PostInventJournalAuto == NoYes::No;
if(!inventJournalTransLoc)
{
try
{
journalCheckPost = InventJournalCheckPost::newPostJournal(inventJournalTableLoc);
journalCheckPost.runOperation();
}
catch
{
exceptionTextFallThrough();
continue;
}
}
}
}
}
if(totalRecords > 0)
{
Info(strFmt(“%1 Records imported from Accuvax for the period of %2 – %3.”, totalRecords, startDate, endDate));
Info(strFmt(“%1 records from previous loads skipped”, totalSkipped));
Info(strFmt(“%1 records skipped due to error”, totalRecordsError));
Info(strFmt(“%1 journal lines created.”, totalLines));
Info(strFmt(“%1 reload type records skipped”, totalReloadTypeError));
}
}
}
}
Excel import with browse file option
using System.IO;
using OfficeOpenXml;
using OfficeOpenXml.ExcelPackage;
using OfficeOpenXml.ExcelRange;
class CITPdsCustSellableDaysImportExcel extends RunBaseBatch
{
Filename ItemFileName;
Filename filename;
DialogField dialogFilename;
System.IO.Stream stream;
FileUploadTemporaryStorageResult fileUploadResult;
OfficeOpenXml.ExcelRange range;
CommaTextStreamIo commaIo;
RecordInsertList rsl;
boolean newIteration;
int line,
lineimported,lineerror, lineskipped;
Counter loop;
FileUploadBuild dialogFileUpload;
FileUpload fileUpload;
DialogRunbase dialog;
boolean poUpdate;
#define.CurrentVersion(1)
#define.Version1(1)
#localmacro.CurrentList
fileName
#endmacro
void create(int row)
{
str itemId;
qty inventPrice;
str recId;
str plant;
str Sparton_Part_No, Commit_Flag, Manufacturer, Mfg_Part_Number, Supplier_Notes1;
str Lead_Time;
str Suggested_Dock_Date;
str Suggested_Dock_Qty;
InventTableModule inventTableModule;
str custAcc = range.get_Item(row, 1).Value;
int sellDays = str2Int(range.get_Item(row, 5).Value);
str itemcode = range.get_Item(row, 2).Value;
str itemRelation = range.get_Item(row, 3).Value;
//Description descDocu = range.get_Item(row, 4).Value;
//Notes notesDocu = range.get_Item(row, 5).Value;
TableGroupAll TGL;
if(itemcode == 'Table')
{
TGL = TableGroupAll::Table;
}
else if(itemcode == 'All')
{
TGL = TableGroupAll::All;
}
else
{
TGL = TableGroupAll::GroupId;
}
try
{
PdsCustSellableDays sellableDays;
select firstonly sellableDays
where sellableDays.CustAccount == custAcc
&& sellableDays.itemcode == TGL
&& sellableDays.itemrelation == itemRelation;
if(sellableDays)
{
ttsbegin;
sellableDays.selectforupdate(true);
sellableDays.sellabledays = sellDays;
sellableDays.update();
ttscommit;
}
else
{
sellableDays.clear();
sellableDays.CustAccount = custAcc;
sellableDays.inventdimid = 'AllBlank';
sellableDays.itemcode = TGL;
sellableDays.itemrelation = itemRelation;
sellableDays.sellabledays = sellDays;
if(sellableDays.validatewrite())
{
sellableDays.insert();
}
}
Info("Done");
}
catch
{
lineerror ++;
// ttsAbort;
}
}
void import()
{
Container con;
InventTable inventTable;
;
setPrefix("Import master data");
poUpdate = false;
if (this.openFile())
{
using (ExcelPackage Package = new ExcelPackage(stream))
{
int rowCount, i, endRow, startRow;
Package.Load(stream);
ExcelWorksheet worksheet = package.get_Workbook().get_Worksheets().get_Item(1);
range = worksheet.Cells;
endRow = worksheet.Dimension.End.Row;
startRow = worksheet.Dimension.Start.Row;
rowCount = endRow - startRow + 1;
try
{
for (i = 2; i<= rowCount; i++)
{
setPrefix(strFmt("Line %1", i));
this.create(i);
}
}
catch
{
throw error("Error - Import template");
}
}
}
info(strfmt("%1 record(s) imported", lineimported));
info(strfmt("%1 record(s) fail", lineerror));
}
boolean openFile()
{
boolean ret = false;
if (fileUploadResult != null && fileUploadResult.getUploadStatus())
{
stream = fileUploadResult.openResult();
ret = true;
}
else
{
throw error(strfmt("@SYS62207",filename));
}
return ret;
}
public Object dialog()
{
dialog = super();
DialogGroup dialogGroup = dialog.addGroup('Upload file');
FormBuildControl formBuildControl = dialog.formBuildDesign().control(dialogGroup.name());
dialogFileUpload = formBuildControl.addControlEx(classstr(FileUpload), 'FileUpload');
dialogFileUpload.style(FileUploadStyle::MinimalWithFilename);
dialogFileUpload.fileNameLabel("@SYS308842");
dialogFileUpload.fileTypesAccepted(".xlsx");
return dialog;
}
/// <summary>
/// Disables the dialog Ok button until the file upload is complete.
/// </summary>
/// <param name="_dialog">The <c>Runbase</c> dialog object.</param>
public void dialogPostRun(DialogRunbase _dialog)
{
fileUpload = _dialog.formRun().control(_dialog.formRun().controlId('FileUpload'));
fileUpload.notifyUploadCompleted += eventhandler(this.uploadCompleted);
this.setDialogOkButtonEnabled(_dialog, false);
}
/// <summary>
/// After the file has been uploaded, the Ok button is enabled.
/// </summary>
public void uploadCompleted()
{
filename = fileUpload.fileName();
this.setDialogOkButtonEnabled(dialog, true);
fileUploadResult = fileUpload.getFileUploadResult();
fileUpload.notifyUploadCompleted -= eventhandler(this.UploadCompleted);
}
/// <summary>
/// Enables or disables the dialog Ok button.
/// </summary>
/// <param name = "_dialog">The <c>Runbase</c> dialog object.</param>
/// <param name = "_isEnabled">Indicates to enable or disable the Ok button.</param>
protected void setDialogOkButtonEnabled(DialogRunbase _dialog, boolean _isEnabled)
{
FormControl okButtonControl = _dialog.formRun().control(_dialog.formRun().controlId('OkButton'));
if (okButtonControl)
{
okButtonControl.enabled(_isEnabled);
}
}
public container pack()
{
return [#CurrentVersion,#CurrentList];
}
public boolean unpack(container packedClass)
{
Version version = runbase::getVersion(packedClass);
;
switch (version)
{
case #CurrentVersion:
[version,#CurrentList] = packedClass;
break;
default:
return false;
}
return true;
}
public void run()
{
this.import();
super();
}
static void main(Args _args)
{
CITPdsCustSellableDaysImportExcel import;
FormRun formRun;
Args args;
;
import = new CITPdsCustSellableDaysImportExcel();
if (import.prompt())
{
import.run();
}
}
public ClassDescription caption()
{
return 'Import template';
}
}
CustAging report D365fo
Creating custom fields in Custom aging report and getting data from CustTrans fields.
- Create extension for class CustAgingReportTmpFromCustTmpAccountSum
- Global variables
- public CustAgingReportDPParameters cdp;
- public CustTmpAccountSum tmpAccountSum;
- Get tmp table instance in newFromCustAgingReportDP method
- public static CustAgingReportTmpFromCustTmpAccountSum newFromCustAgingReportDP(CustAgingReportDPParameters _custAgingReportDPParameters)
- {
- CustAgingReportTmpFromCustTmpAccountSum instance = next newFromCustAgingReportDP(_custAgingReportDPParameters);
- instance.cdp = _custAgingReportDPParameters;
- return instance;
- }
- COC fpr onExecuted
- protected void onExecuted()
- {
- next onExecuted();
- CustAgingReportTmp custAgingReportTmp = cdp.custAgingReportTmp;
- tmpAccountSum = cdp.custTmpAccountSum;
- ttsbegin;
- update_recordset custAgingReportTmp
- setting customTxt = custTrans.Txt
- join custTrans
- where custTrans.AccountNum == custAgingReportTmp.AccountNum
- && custTrans.Voucher == custAgingReportTmp.Voucher
- && custTrans.TransDate == custAgingReportTmp.TransDate;
- ttscommit;
- }
Import CSV, Update/insert financial dimension values through code
internal final class CostCenterImport
{
/// /// 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. ///
/// The specified arguments.
public static void main(Args _args)
{
#File
IO io;
str id, company;
Description description;
FilenameOpen filename = @"C:\Temp\CostCentre.csv";
Container record;
;
io = new CommaTextIo(filename, #IO_Read);
if (!io || io.status() != IO_Status::Ok)
{
throw error("@SYS19358");
}
int i = 1;
ttsbegin;
while (io.status() == IO_Status::Ok)
{
record = io.read();
if (record)
{
company = conpeek(record, 5);
id = conpeek(record, 9);
description = conpeek(record, 10);
CostCenter costCenter;
if(CompanyInfo::findDataArea(Company))
{
changecompany(company)
{
costCenter.clear();
costCenter.CostCenterId = id;
costCenter.CostCenterName = description;
costCenter.insert();
info(strFmt("imported - %1", i));
i++;
}
}
}
}
ttscommit;
//import to DimensionFinancialTag
DimensionAttribute _dimensionAttribute;
_dimensionAttribute = DimensionAttribute::findByName('CostCenter');
RecId _financialTagGategoryRecId = _dimensionAttribute.financialTagCategory();
CostCenter costCenter;
try
{
i = 1;
while select crosscompany costCenter
{
DimensionFinancialTag _dimensionFinancialTag = DimensionFinancialTag::findByFinancialTagCategoryAndValue(_financialTagGategoryRecId,
costCenter.CostCenterId,true);
if(!_dimensionFinancialTag)
{
_dimensionFinancialTag.clear();
_dimensionFinancialTag.Description = costCenter.CostCenterName;
_dimensionFinancialTag.Value = costCenter.CostCenterId;
_dimensionFinancialTag.FinancialTagCategory = _financialTagGategoryRecId;
if(_dimensionFinancialTag.validateWrite())
{
_dimensionFinancialTag.insert();
info(strFmt("inserted - %1", i));
}
}
}
}
catch
{
retry;
}
}
//}
}
Get Dimension
RecId getDimension(str _ledgerAccount, str _department, str _state, str _location, str _service, str _lineOfBusiness, str _payer, str _project)
{
LedgerAccountContract LedgerAccountContract = new LedgerAccountContract();
DimensionAttributeValueContract ValueContract;
List ListValueContract = new List(Types::Class);
dimensionAttributeValueCombination dimensionAttributeValueCombination;
DimensionStorage dimStorage;
if (_department)
{
ValueContract = new DimensionAttributeValueContract();
ValueContract.parmName(‘Department’) ;
ValueContract.parmValue(_department);
ListValueContract.addEnd(ValueContract);
}
if (_state)
{
ValueContract = new DimensionAttributeValueContract();
ValueContract.parmName(‘State’) ;
ValueContract.parmValue(_state);
ListValueContract.addEnd(ValueContract);
}
if (_location)
{
ValueContract = new DimensionAttributeValueContract();
ValueContract.parmName(‘Location’) ;
ValueContract.parmValue(_location);
ListValueContract.addEnd(ValueContract);
}
if (_service)
{
ValueContract = new DimensionAttributeValueContract();
ValueContract.parmName(‘Service’) ;
ValueContract.parmValue(_service);
ListValueContract.addEnd(ValueContract);
}
if (_lineOfBusiness)
{
ValueContract = new DimensionAttributeValueContract();
ValueContract.parmName(‘Line_Of_Business’) ;
ValueContract.parmValue(_lineOfBusiness);
ListValueContract.addEnd(ValueContract);
}
if (_payer)
{
ValueContract = new DimensionAttributeValueContract();
ValueContract.parmName(‘Payer’) ;
ValueContract.parmValue(_payer);
ListValueContract.addEnd(ValueContract);
}
if (_project)
{
ValueContract = new DimensionAttributeValueContract();
ValueContract.parmName(‘Project’) ;
ValueContract.parmValue(_project);
ListValueContract.addEnd(ValueContract);
}
LedgerAccountContract.parmMainAccount(_ledgerAccount);
LedgerAccountContract.parmValues(ListValueContract);
dimStorage = DimensionServiceProvider::buildDimensionStorageForLedgerAccount(LedgerAccountContract);
dimensionAttributeValueCombination = DimensionAttributeValueCombination::find(dimStorage.save());
return dimensionAttributeValueCombination.RecId;
}
Admin provisioning tool can’t stop DynamicsAXBatch service
Cannot stop DynamicsAxBatch service on computer ‘.’.
- Click the Start menu
- Click Run or in the search bar type services.msc
- Press Enter
- Look for the service and check the Properties and identify its service name. Service name would be DynamicsAxBatch
- Once found, open a command prompt. Type: sc queryex [servicename].
- Press Enter
- Identify the PID
- In the same command prompt type: taskkill /f /pid [pid number]
- Press Enter