Here By the following code we can import a excel file in D365 fo. I provide the detailed Generation of journal lines based on every offset account type. Follow below clases to know detaily
1. create on import class .
using System.IO;
using OfficeOpenXml;
using OfficeOpenXml.ExcelPackage;
using OfficeOpenXml.ExcelRange;
class PMELedgerJournalLinesImport
{
/// <summary>
///
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
[FormControlEventHandler(formControlStr(LedgerJournalTransDaily, PMELinesImportFromExcel), FormControlEventType::Clicked)]
public static void PMELinesImportFromExcel_OnClicked(FormControl sender, FormControlEventArgs e)
{
System.IO.Stream stream;
LedgerJournalTable ledgerJournalTableLoc;
LedgerJournalTrans ledgerJournalTransloc;
NumberSeq numberSeq;
NumberSequenceTable numberSequenceTable;
ExcelSpreadsheetName sheeet;
RefRecId ledgerdim,ledgerdim1;
LedgerJournalACType ljcType;
container con , convalue ;
TaxRateType Taxratetype;
ServiceAccountingCodeTable_IN serviceAccountingCodeTable_IN;
LedgerJournalTransTaxExtensionIN ledgerJournalTransTaxExtensionInloc;
//FileUploadBuild fileUpload;
DialogGroup dlgUploadGroup;
FileUploadBuild fileUploadBuild;
FormBuildControl formBuildControl;
PMEGenerateLedgerDimension pMEGenerateLedgerDimension = new PMEGenerateLedgerDimension();
//DimensionServiceProvider DimensionServiceProvider = new DimensionServiceProvider();
//LedgerAccountContract LedgerAccountContract = new LedgerAccountContract();
DimensionAttributeValueContract ValueContract;
List ListValueContract = new List(Types::Class);
dimensionAttributeValueCombination dimensionAttributeValueCombination;
DimensionStorage dimStorage;
str invoicenum;
Dialog dialog = new Dialog("@PME - import form excel");
FormRun formRun = sender.formRun() as FormRun;
FormRun callerformrun = formRun.args().caller();
ledgerJournalTableLoc = callerformrun.dataSource().cursor() as LedgerJournalTable ;
dlgUploadGroup = dialog.addGroup("@SYS54759");
formBuildControl = dialog.formBuildDesign().control(dlgUploadGroup.name());
fileUploadBuild = formBuildControl.addControlEx(classstr(FileUpload), 'Upload');
fileUploadBuild.style(FileUploadStyle::MinimalWithFilename);
fileUploadBuild.fileTypesAccepted('.xlsx');
if (dialog.run() && dialog.closedOk())
{
FileUpload fileUploadControl = dialog.formRun().control(dialog.formRun().controlId('Upload'));
FileUploadTemporaryStorageResult fileUploadResult = fileUploadControl.getFileUploadResult();
if (fileUploadResult != null && fileUploadResult.getUploadStatus())
{
stream = fileUploadResult.openResult();
using (ExcelPackage Package = new ExcelPackage(stream))
{
int rowCount, i;
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;
try
{
for (i = 2; i<= rowCount; i++)
{
ttsbegin;
// DimensionDisplayValue offsetValue = range.get_Item(i, 10).value;
//DimensionDisplayValue accountValue = range.get_Item(i, 3).value;
// lederJournalTable.JournalName = range.get_Item(i, 1).value;
// lederJournalTable.insert();
ledgerJournalTransloc.JournalNum = ledgerJournalTableLoc.journalnum;
numberSeq = NumberSeq::newGetVoucherFromId(ledgerJournalTableloc.NumberSequenceTable, false);
ledgerJournalTransloc.Voucher = numberSeq.voucher();
ledgerJournalTransloc.TransDate = DateTimeUtil::date(range.get_Item(i, 1).value);
ledgerJournalTransloc.Company = curExt();
ledgerJournalTransloc.AccountType = LedgerJournalACType::Cust;
// ledgerdim1 = DimensionStorage::getDynamicAccount(range.get_Item(i, 3).value, LedgerJournalACType::Cust);
ledgerdim1 = LedgerDynamicAccountHelper::getDynamicAccountFromAccountNumber(range.get_Item(i, 3).value, LedgerJournalACType::Cust);
ledgerJournalTransloc.LedgerDimension = ledgerdim1;
// ledgerJournalTrans.AccountType = range.get_Item(i, 2).value;;
// ledgerJournalTrans.LedgerDimension = LedgerDynamicAccountHelper::getDynamicAccountFromAccountNumber(range.get_Item(i, 4).value, LedgerJournalACType::Cust);
// if (ledgerJournalTrans.AccountType == LedgerJournalACType::Cust)
// {
// ledgerJournalTrans.DefaultDimension = CustTable::find(accountValue).DefaultDimension;
// }
// ledgerJournalTrans.MarkedInvoice = range.get_Item(i, 5).value;
//ledgerJournalTrans.Txt = range.get_Item(i, 6).value;
ledgerJournalTransloc.Txt = range.get_Item(i, 4).value;
ledgerJournalTransloc.AmountCurDebit = any2Real(range.get_Item(i, 5).value);
ledgerJournalTransloc.AmountCurCredit = any2real(range.get_Item(i, 6).value);
ledgerJournalTransloc.CurrencyCode = range.get_Item(i, 7).value;
ledgerJournalTransloc.OffsetAccountType = str2Enum(ljcType,range.get_Item(i, 9).value);
if (ledgerJournalTransloc.OffsetAccountType == LedgerJournalACType::Ledger)
{
// con = ['MainAccount ', 'Location', 'Segment' , ' Department' , 'Profitcentre'];
convalue = [ range.get_Item(i, 10).value, range.get_Item(i, 15).value, range.get_Item(i, 18).value, range.get_Item(i, 16).value, range.get_Item(i, 17).value ];
ledgerJournalTransloc.OffsetLedgerDimension = pMEGenerateLedgerDimension.generateLedgerDimension(convalue ,range.get_Item(i, 10).value );
}
if(ledgerJournalTransloc.OffsetAccountType == LedgerJournalACType::Bank)
{
ledgerJournalTransloc.OffsetLedgerDimension =
LedgerDynamicAccountHelper::getDynamicAccountFromAccountNumber(range.get_Item(i, 10).value, LedgerJournalACType::Bank);
}
if(ledgerJournalTransloc.OffsetAccountType == LedgerJournalACType::Cust)
{
ledgerJournalTransloc.OffsetLedgerDimension =
LedgerDynamicAccountHelper::getDynamicAccountFromAccountNumber(range.get_Item(i, 10).value, LedgerJournalACType::Cust);
}
if(ledgerJournalTransloc.OffsetAccountType == LedgerJournalACType::Vend)
{
ledgerJournalTransloc.OffsetLedgerDimension =
LedgerDynamicAccountHelper::getDynamicAccountFromAccountNumber(range.get_Item(i, 10).value, LedgerJournalACType::Vend);
}
ledgerJournalTransloc.DocumentNum = range.get_Item(i, 12).value;
ledgerJournalTransloc.DocumentDate = any2Date( range.get_Item(i, 13).value);
ledgerJournalTransloc.Due = any2date(range.get_Item(i, 14).value);
invoicenum = ledgerJournalTransloc.PMEGetInvoiceNum(ledgerJournalTableLoc.journalnum);
ledgerJournalTransloc.Invoice = invoicenum;
// ledgerJournalTransloc.initValue();
ledgerJournalTransloc.insert();
ttscommit;
if(ledgerJournalTransloc.TableId && ledgerJournalTransloc.RecId)
{
TransTaxInformation transTaxInformationLocal = TransTaxInformationHelper_IN::findOrCreateTransTaxInformation(ledgerJournalTransloc.TableId, ledgerJournalTransloc.RecId);
if (transTaxInformationLocal)
{
ttsbegin;
transTaxInformationLocal.selectForUpdate(true);
//ransTaxInformationHelper_IN::initFromCustVend(transTaxInformationLocal, vendTable);
transTaxInformationLocal.HSNCodeTable = HSNCodeTable_IN::findByCode(range.get_Item(i, 20).value);
select RecId from TaxRateType where TaxRateType.Name == range.get_Item(i, 21).value;
if(TaxRateType)
{
transTaxInformationLocal.TaxRateType = TaxRateType.RecId;
}
//select RecId from serviceAccountingCodeTable_IN where serviceAccountingCodeTable_IN.SAC == range.get_Item(i, 22).value;
// if( serviceAccountingCodeTable_IN.RecId)
//{
transTaxInformationLocal.ServiceAccountingCodeTable = ServiceAccountingCodeTable_IN::findByServiceAccountingCode(range.get_Item(i, 22).value); //serviceAccountingCodeTable_IN.RecId;
//}
transTaxInformationLocal.update();
ttscommit;
}
select forupdate ledgerJournalTransTaxExtensionInloc where ledgerJournalTransTaxExtensionInloc.LedgerJournalTrans == ledgerJournalTransloc.RecId;
if(ledgerJournalTransTaxExtensionInloc)
{
ttsbegin;
ledgerJournalTransTaxExtensionInloc.TDSGroup = range.get_Item(i, 19).value;
ledgerJournalTransTaxExtensionInloc.Update();
ttscommit;
}
else
{
ledgerJournalTransTaxExtensionInloc.LedgerJournalTrans = ledgerJournalTransloc.RecId;
ledgerJournalTransTaxExtensionInloc.TDSGroup = range.get_Item(i, 19).value;
ledgerJournalTransTaxExtensionInloc.insert();
}
}
}
}
catch(Exception::Error)
{
info("@PME:Error");
}
}
}
else
{
error("Error here");
}
}
}
}
2. For financial dimensions for offset account I have created following class
Meanwhile in D365 fo some of the classes Like AxledgerUtil Are obsolate so we are using following classes.
public class PMEGenerateLedgerDimension
{
public DimensionDynamicAccount generateLedgerDimension(container _conData, MainAccountNum _mainAccount)
{
int hierarchyCount;
int hierarchyIdx;
RecId dimAttId_MainAccount;
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;
try
{
mainAccount = MainAccount::findByMainAccountId(_mainAccount);
recordvalue = DimensionHierarchy::getAccountStructure(mainAccount.RecId,Ledger::current());
hierarchyCount = DimensionHierarchy::getLevelCount(recordvalue);
DimensionSet = DimensionHierarchyLevel::getDimensionHierarchyLevelNames(recordvalue);
for(hierarchyIdx = 1;hierarchyIdx<=hierarchyCount;hierarchyIdx++)
{
if(hierarchyIdx == 1)
continue;
dimensionAttribute = DimensionAttribute::findByLocalizedName(DimensionSet[hierarchyIdx],false,"en-us");
if(dimensionAttribute)
{
dimensionAttributeValue = DimensionAttributeValue::findByDimensionAttributeAndValue(dimensionAttribute,conPeek(_conData,hierarchyIdx) ,false ,true);
if(dimensionAttributeValue)
{
ValueContract = new DimensionAttributeValueContract();
ValueContract.parmName(dimensionAttribute.Name) ;
ValueContract.parmValue(dimensionAttributeValue.CachedDisplayValue);
valueContracts.addEnd(ValueContract);
}
}
}
LedgerAccountContract.parmMainAccount(_mainAccount);
LedgerAccountContract.parmValues(valueContracts);
dimStorage = DimensionServiceProvider::buildDimensionStorageForLedgerAccount(LedgerAccountContract);
dimensionAttributeValueCombination = DimensionAttributeValueCombination::find(dimStorage.save());
ledgerRecId = dimensionAttributeValueCombination.RecId;
}
catch
{
throw Exception::error;
}
return ledgerRecId;
}
}
Thank you....!
No comments:
Post a Comment