Hi Guys,
Import General journal from excel in D365 F&O
Code:
using System.IO;
using OfficeOpenXml;
using OfficeOpenXml.ExcelPackage;
using OfficeOpenXml.ExcelRange;
public class SWC_GeneralJournalUpload
{
str legalentityid,journalbatchnumber,journalname, voucher,accountType,accountdisplayvalue,defaultdimensionsdisplayvalue,text,currencycode,invoice,offsetAccountType, curJournal;
TransDate transDate,documentDate,dueDate,reverseDate;
int rowCount, i, invoicingItemIterator = 0;
str offsetLedgerDimension, offsetdefalutdimensiondisplayvalue,paymReference,postingProfile,postinglayer,reverseEntry;
real lineNum,debitamount,creditamount ;
container journalContainer;
boolean colorDimval = false, sizeDimVal = false;
int toDeleteIterator = 0;
container toDeleteCon;
boolean checkErrorJour = true;
Notes errorNote = '';
int errorCount;
boolean receivedErrorInFile, createRecord = true;
public static void main(Args _args)
{
System.IO.Stream stream;
DialogGroup dlgUploadGroup;
FileUploadBuild fileUploadBuild;
FormBuildControl formBuildControl;
SWC_GeneralJournalUpload itemJournalImport;
Name itemJournalForm;
FormRun callerForm;
Dialog dialog = new Dialog("Import the data from Excel");
dlgUploadGroup = dialog.addGroup("General Journal Import");
formBuildControl = dialog.formBuildDesign().control(dlgUploadGroup.name());
fileUploadBuild = formBuildControl.addControlEx(classstr(FileUpload), "Upload");
fileUploadBuild.style(FileUploadStyle::MinimalWithFilename);
fileUploadBuild.fileTypesAccepted(".xlsx");
itemJournalImport = new SWC_GeneralJournalUpload();
if (dialog.run() && dialog.closedOk())
{
infolog.clear();
FileUpload fileUploadControl = dialog.formRun().control(dialog.formRun().controlId("Upload"));
FileUploadTemporaryStorageResult fileUploadResult = fileUploadControl.getFileUploadResult();
if (fileUploadResult != null && fileUploadResult.getUploadStatus())
{
stream = fileUploadResult.openResult();
callerForm = _args.caller();
if(callerForm)
{
itemJournalForm = callerForm.args().menuItemName();
}
ttsbegin;
itemJournalImport.insertbudget(stream); //todo
ttscommit;
}
else
{
error("Error here");
}
}
}
/// <summary>
/// Static method to get default dimension
/// </summary>
/// <param name = "conAttr">Dimension attribute container</param>
/// <param name = "conValue">Dimension value container</param>
/// <returns>defualt dimension</returns>
public static DimensionDefault getDefaultDim(container conAttr, container conValue)
{
DimensionAttributeValueSetStorage valueSetStorage = new DimensionAttributeValueSetStorage();
int i;
DimensionAttribute dimensionAttribute;
DimensionAttributeValue dimensionAttributeValue;
str dimValue;
if((conLen(conAttr) <= 0) || (conLen(conValue) <= 0))
{
throw error("@SYS313886");
}
for (i = 1; i <= conLen(conAttr); i++)
{
dimensionAttribute = dimensionAttribute::findByName(conPeek(conAttr,i));
if (dimensionAttribute.RecId == 0)
{
continue;
}
dimValue = conPeek(conValue,i);
if (dimValue != "")
{
dimensionAttributeValue = dimensionAttributeValue::findByDimensionAttributeAndValue(dimensionAttribute,dimValue,false,true);
valueSetStorage.addItem(dimensionAttributeValue);
}
}
return valueSetStorage.save();
}
// Started Here 10082023
public void validateDimenison(container _conValue, container _conAttr)
{
DimensionAttributeValueSetStorage valueSetStorage = new DimensionAttributeValueSetStorage();
DimensionDefault result;
int counti;
DimensionAttribute dimensionAttribute;
DimensionAttributeValue dimensionAttributeValue;
str dimValue;
for (counti = 1; counti <= conLen(_conAttr); counti++)
{
dimensionAttribute = dimensionAttribute::findByName(conPeek(_conAttr,counti));
if (dimensionAttribute.RecId == 0)
{
continue;
}
dimValue = conPeek(_conValue,counti);
if (dimValue != "")
{
dimensionAttributeValue = dimensionAttributeValue::findByDimensionAttributeAndValueNoError(dimensionAttribute,dimValue,false,true);
if (!dimensionAttributeValue)
{
errorNote += strFmt("%3: Value %1 does not existis for dimension %2 " , conPeek(_conValue, counti), conPeek(_conAttr, counti), errorCount);
receivedErrorInFile = true;
errorCount++;
}
}
}
}
// Ended Here
/// <summary>
/// Account type Container Ledger dimesion is ledger combination of Main account And FD 10082023 Stated
/// </summary>
/// <param name = "_conData"></param>
/// <param name = "mainAccountNumber"></param>
/// <param name = "_conDimName"></param>
/// <returns></returns>
public DimensionDynamicAccount generateLedgerDimension_loc(container _conData,MainAccountNum mainAccountNumber, container _conDimName)
{
int hierarchyCount;
int hierarchyIdx;
LedgerRecId ledgerRecId;
MainAccount mainAccount;
RefRecId recordvalue;
DimensionAttribute dimensionAttribute;
DimensionAttributeValue dimensionAttributeValue;
DimensionSetSegmentName dimensionSet;
DimensionStorage dimStorage;
LedgerAccountContract ledgerAccountContract = new LedgerAccountContract();
DimensionAttributeValueContract valueContract;
List valueContracts = new List(Types::Class);
dimensionAttributeValueCombination dimensionAttributeValueCombination;
mainAccount = MainAccount::findByMainAccountId(mainAccountNumber);
recordvalue = DimensionHierarchy::getAccountStructure(mainAccount.RecId, Ledger::current());
hierarchyCount = DimensionHierarchy::getLevelCount(recordvalue);
dimensionSet = DimensionHierarchyLevel::getDimensionHierarchyLevelNames(recordvalue);
for (hierarchyIdx = 1; hierarchyIdx <= conLen(_conDimName); hierarchyIdx++)
{
if (hierarchyIdx <= 1)
{
continue;
}
dimensionAttribute = DimensionAttribute::findByLocalizedName(conPeek(_conDimName, hierarchyIdx),false);
if (dimensionAttribute)
{
dimensionAttributeValue = DimensionAttributeValue::findByDimensionAttributeAndValue(dimensionAttribute,conPeek(_conData,hierarchyIdx));
if (dimensionAttributeValue)
{
valueContract = new DimensionAttributeValueContract();
valueContract.parmName(dimensionAttribute.Name) ;
valueContract.parmValue(dimensionAttributeValue.CachedDisplayValue);
valueContracts.addEnd(valueContract);
}
else if (!dimensionAttributeValue && conPeek(_conData,hierarchyIdx))
{
throw Error(strFmt("The dimension value - %1 for dimension - %2 doesnot exists ", conPeek(_conData,hierarchyIdx), dimensionAttribute.DimensionKeyColumnName));
}
}
}
ledgerAccountContract.parmMainAccount(mainAccountNumber);
ledgerAccountContract.parmValues(valueContracts);
dimStorage = DimensionServiceProvider::buildDimensionStorageForLedgerAccount(ledgerAccountContract);
dimensionAttributeValueCombination = DimensionAttributeValueCombination::find(dimStorage.save());
ledgerRecId = dimensionAttributeValueCombination.RecId;
LedgerDimensionBudget accountDimension = LedgerDimensionFacade::serviceCreateLedgerDimensionForType(LedgerDimensionType::Account, ledgerRecId);
return accountDimension;
}
//---------------EndHERE-------------------
public static DimensionDynamicAccount createLedgerDimFromMainAccount(container conAttr, container conValue, Name mainAccount, LedgerJournalACType accountType)
{
DimensionDefault dimensionDefault;
DimensionDynamicAccount dimensionDynamicAccount;
LedgerDimensionBase ledgerDimBase;
LedgerDimensionAccount ledgerDim;
dimensionDefault = SWC_GeneralJournalUpload::getDefaultDim(conAttr, conValue);
dimensionDynamicAccount = LedgerDynamicAccountHelper::getDynamicAccountFromAccountNumber(mainAccount, accountType);
ledgerDim = LedgerDimensionFacade::serviceCreateLedgerDimension(dimensionDynamicAccount);
ledgerDimBase = LedgerDimensionFacade::serviceCreateLedgerDimForDefaultDim(dimensionDefault, ledgerDim);
return ledgerDimBase;
}
public void insertbudget(System.IO.Stream _stream)
{
CommaTextIO csvFile;
container readCon;
counter icount,inserted;
Dialog dialog;
DialogField dfFileName;
FileName fileName;
LedgerJournalTable ledgerJournalTable;
LedgerJournalTrans ledgerJournalTrans;
container offsetdimesionCon, offsetdimesionConValue;
using (ExcelPackage Package = new ExcelPackage(_stream))
{
RecordInsertList recordinsertlist;
int insertedRecords =0, failedrecords=0;
Package.Load(_stream);
ExcelWorksheet worksheet = package.get_Workbook().get_Worksheets().get_Item(1);
OfficeOpenXml.ExcelRange range = worksheet.Cells;
rowCount = worksheet.Dimension.End.Row - worksheet.Dimension.Start.Row + 1;
//recordinsertlist = new recordinsertlist(tableNum(BudgetTransactionLine)); //todo
for (i = 2; i<= rowCount; i++)
{
legalentityid = strLRTrim(range.get_Item(i, 1).value);
journalbatchnumber = strLRTrim(range.get_Item(i, 2).value);
journalname = strLRTrim(range.get_Item(i, 3).value);
transDate = any2Date(range.get_Item(i, 4).value);
voucher = strLRTrim(range.get_Item(i, 5).value);
lineNum = any2Real(range.get_Item(i, 6).value);
accountType = strLRTrim(range.get_Item(i, 7).value);
accountdisplayvalue = strLRTrim(range.get_Item(i, 8).value);
defaultdimensionsdisplayvalue = strLRTrim(range.get_Item(i, 9).value);
text = strLRTrim(range.get_Item(i, 10).value);
currencycode = strLRTrim(range.get_Item(i, 11).value);
debitamount = any2Real(range.get_Item(i, 12).value);
creditamount = any2Real(range.get_Item(i, 13).value);
invoice = strLRTrim(range.get_Item(i, 14).value);
documentDate = any2Date(range.get_Item(i, 15).value);
dueDate = any2Date(range.get_Item(i, 16).value);
offsetLedgerDimension = strLRTrim(range.get_Item(i, 17).value);
offsetAccountType = strLRTrim(range.get_Item(i, 18).value);
offsetdefalutdimensiondisplayvalue = strLRTrim(range.get_Item(i, 19).value);
paymReference = strLRTrim(range.get_Item(i, 20).value);
postinglayer = strLRTrim(range.get_Item(i, 21).value);
postingProfile = strLRTrim(range.get_Item(i, 22).value);
reverseDate = any2Date(range.get_Item(i, 23).value);
//reverseEntry = strLRTrim(range.get_Item(i, 24).value);
changecompany(legalentityid)
{
if (!LedgerJournalTable::find(journalbatchnumber))
{
ledgerJournalTable.clear();
ledgerJournalTable.initValue();
//ledgerJournalTable.JournalName;
ledgerJournalTable.initFromLedgerJournalName();
ledgerJournalTable.JournalNum = journalbatchnumber;//JournalTableData::newTable(ledgerJournalTable).nextJournalId();
ledgerJournalTable.JournalName = journalname;
ledgerJournalTable.insert();
info(strFmt("Journal %1 created", ledgerJournalTable.JournalNum));
}
LedgerJournalACType ledgerJournalACType;
offsetdimesionCon = ["","A_Budget_L3","B_Vehicle","C_Electricity_Meter","D_FDR","E_Mutual_Fund","F_Employee","G_Vendor","H_Budget_L2","I_Budget_L1","J_Vehicle_Ownership","K_Electricity_Meter_Subhead"];
offsetdimesionConValue = conNull();//["","","","","","",""];
//coded by validation start 10082023
offsetdimesionConValue = str2con_RU(accountdisplayvalue, "|");
MainAccount mainAccount = MainAccount::findByMainAccountId(conPeek (offsetdimesionConValue, 1));
if (!mainAccount)
{
errorNote += strFmt("%2. Main account %1 does not exsits " , conPeek (offsetdimesionConValue, 1), errorCount);
receivedErrorInFile = true;
errorCount++;
}
offsetdimesionConValue = conPoke(offsetdimesionConValue, 1, "");
this.validateDimenison(offsetdimesionConValue, offsetdimesionCon);
//this.generateLedgerDimension_loc(offsetdimesionConValue, conPeek(offsetdimesionConValue, 1), offsetdimesionCon);
//coded by validation End Here 10082023
ledgerJournalTrans.clear();
ledgerJournalTrans.initValue();
ledgerJournalTrans.JournalNum = ledgerJournalTable.JournalNum;
ledgerJournalTrans.TransDate = transDate;
//LedgerJournalName ledgerJournalName;
//NumberSeq numberseq;
//NumberSequenceTable numberSequenceTable;
//Voucher voucherNum;
//LedgerParameters LedgerParameters;
//ledgerParameters = LedgerParameters::find();
//ledgerJournalName = LedgerJournalName::find(JournalName);
//numberseq = NumberSeq::newGetVoucherFromCode(NumberSequenceTable::find(ledgerJournalName.NumberSequenceTable).NumberSequence);
//numberseq.voucher();
ledgerJournalTrans.Voucher = voucher;
ledgerJournalTrans.LineNum = lineNum;
ledgerJournalTrans.AccountType = str2Enum(ledgerJournalACType, accountType );
if(accountType =="Bank")
{
ledgerJournalTrans.LedgerDimension = LedgerDynamicAccountHelper::getDynamicAccountFromAccountNumber(accountdisplayvalue,LedgerJournalACType::Bank);//str2Int64(accountdisplayvalue);
}
if(accountType =="Vend")
{
ledgerJournalTrans.LedgerDimension = LedgerDynamicAccountHelper::getDynamicAccountFromAccountNumber(accountdisplayvalue,LedgerJournalACType::Vend);//str2Int64(accountdisplayvalue);
}
if(accountType =="Cust")
{
ledgerJournalTrans.LedgerDimension = LedgerDynamicAccountHelper::getDynamicAccountFromAccountNumber(accountdisplayvalue,LedgerJournalACType::Cust);//str2Int64(accountdisplayvalue);
}
if(accountType =="Ledger")
{
offsetdimesionCon = ["","A_Budget_L3","B_Vehicle","C_Electricity_Meter","D_FDR","E_Mutual_Fund","F_Employee","G_Vendor","H_Budget_L2","I_Budget_L1","J_Vehicle_Ownership","K_Electricity_Meter_Subhead"];
offsetdimesionConValue = conNull();
offsetdimesionConValue = str2con_RU(accountdisplayvalue, "|");
//ledgerJournalTrans.LedgerDimension = LedgerDynamicAccountHelper::getDynamicAccountFromAccountNumber(accountdisplayvalue,LedgerJournalACType::Ledger);
ledgerJournalTrans.LedgerDimension = this.generateLedgerDimension_loc(offsetdimesionConValue, conPeek(offsetdimesionConValue, 1), offsetdimesionCon);
//SWC_GeneralJournalUpload::createLedgerDimFromMainAccount(offsetdimesionCon,offsetdimesionConValue,accountdisplayvalue,LedgerJournalACType::Ledger);
}
ledgerJournalTrans.CurrencyCode = currencycode;
ledgerJournalTrans.AmountCurDebit = debitamount;
ledgerJournalTrans.AmountCurCredit = creditamount;
ledgerJournalTrans.Invoice = invoice;
ledgerJournalTrans.DocumentDate = documentDate;
ledgerJournalTrans.Due = dueDate;
ledgerJournalTrans.Txt = text;
ledgerJournalTrans.OffsetAccountType = str2Enum(LedgerJournalACType, offsetAccountType );
//ledgerJournalTrans.OffsetDefaultDimension
ledgerJournalTrans.OffsetLedgerDimension = SWC_GeneralJournalUpload::createLedgerDimFromMainAccount(offsetdimesionCon,offsetdimesionConValue,offsetLedgerDimension,ledgerJournalTrans.OffsetAccountType);
ledgerJournalTrans.DefaultDimension = ledgerJournalTrans.OffsetLedgerDimension;
ledgerJournalTrans.PaymReference = paymReference;
ledgerJournalTrans.PostingProfile = postingProfile;
ledgerJournalTrans.ReverseDate = reverseDate;
//ledgerJournalTrans.ReverseEntry = reverseEntry;
ledgerJournalTrans.insert();
insertedRecords++;
}
}
if(failedrecords == 0 && insertedRecords > 0)
{
//recordinsertlist.insertDatabase(); //todo
for(i = 1; i <= conLen(journalContainer); i++)
{
info(strFmt("Journal Id - '%1' created", conPeek(journalContainer, i)));
}
info("Upload successful");
}
}
}
}
Excel Template :
