Journal lines import in d365

using System.IO;
using OfficeOpenXml;
using OfficeOpenXml.ExcelPackage;
using OfficeOpenXml.ExcelRange;
class INTC_JournalImport
{
public static void main(Args _args)
{
System.IO.Stream stream;
ExcelSpreadsheetName sheeet;
FileUploadBuild fileUpload;
DialogGroup dlgUploadGroup;
FileUploadBuild fileUploadBuild;
FormBuildControl formBuildControl;
container conDimValue;
container conVal;
int64 ledgerLoc;
str dimValue;
FormDataSource ledgerJOurnalTrans_DS = _args.record().dataSource() as FormDataSource;
ledgerjournalTrans ledgerJournalTrans = ledgerJOurnalTrans_DS.cursor();
TransDate postingDate;
str ledgerAccType, offsetAccType;
LedgerJournalTransTxt transTxt;
MainAccountNum ledgerMainAccount, offsetMainAccount;
AmountCurDebit amountDebit;
AmountCurCredit amountCredit;
CurrencyCode currencyCode;
ExchRate ExchRate;
InvoiceId invoiceID;
DocumentNum documentNum;
DocumentDate documentDate;
DueDate dueDate;
AssetTransTypeJournal assetTransType;
TaxGroupJournal taxGroup, taxItemGroup;
Voucher voucher;
NumberSeq numberseq;
LineNum lineNum;
int64 ledgerDim;
int64 offsetLedgerDim;
MainAccountNum mainAccountNum;
int hierarchyCount, offsethierarchyCount;
int hierarchyIdx, offsethierarchyIdx;
LedgerRecId ledgerRecId, offsetLedgerRecId;
LedgerJournalId journalNum;

LedgerJournalTable ledgerJournalTable;
// LedgerJournalTrans ledgerJOurnalTrans;
Numbersequencetable numSeqTable;
MainAccount mainAccount;
LedgerJournalTrans_Asset LedgerJournalTrans_Asset;
RefRecId recordvalue, offsetrecordvalue;
//DimensionAttribute dimensionAttribute;
//DimensionAttributeValue dimensionAttributeValue;
//DimensionSetSegmentName dimensionSet;
//DimensionStorage dimStorage, offsetdimStorage;
//LedgerJournalTrans_Asset LedgerJournalTrans_Asset;
//LedgerAccountContract ledgerAccountContract;// = new LedgerAccountContract();
//LedgerAccountContract offsetledgerAccountContract;// = new LedgerAccountContract();
//DimensionAttributeValueContract valueContract;
//List valueContracts = new List(Types::Class);

//dimensionAttributeValueCombination dimensionAttributeValueCombination;
common loccom;

DimensionValueName tmpLegal_Entity, tmpOffice_Location, tmpProject_Location, tmpDepartment, tmpService_Line, tmpSub_Service_Line, tmpIndustry,
tmpSub_Industry, tmpCoIndustry, tmpPartner, tmpPM_Employee, tmpProject;

container conAttr = [ "A_LegalEntity","B_Office_Location","C_Project_Location","D_Department","E_Service_Line","F_Sub_Service_Line","H_Industry","I_Sub_Industry","J_CoIndustry","G_Partner","PM_Employee","Project"];

Dialog dialog = new Dialog("Import Journal lines");

dlgUploadGroup = dialog.addGroup("@SYS54759");
formBuildControl = dialog.formBuildDesign().control(dlgUploadGroup.name());
fileUploadBuild = formBuildControl.addControlEx(classstr(FileUpload), "Import");

fileUploadBuild.style(FileUploadStyle::MinimalWithFilename);
fileUploadBuild.fileTypesAccepted('.xlsx');

if (dialog.run() && dialog.closedOk())
{

FileUpload fileUploadControl = dialog.formRun().control(dialog.formRun().controlId('Import'));
FileUploadTemporaryStorageResult fileUploadResult = fileUploadControl.getFileUploadResult();

if (fileUploadResult != null && fileUploadResult.getUploadStatus())
{
stream = fileUploadResult.openResult();
using (ExcelPackage Package = new ExcelPackage(stream))
{
int rowCount, i, j, k;
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;

for (i = 2; i<= rowCount; i++)
{
ledgerJournalTrans = ledgerJOurnalTrans_DS.cursor();
postingDate = range.get_Item(i, 1).value;
ledgerAccType = range.get_Item(i, 2).value;
ledgerMainAccount = range.get_Item(i, 3).value;
transTxt = range.get_Item(i, 4).value;
amountDebit = range.get_Item(i, 5).value;
amountCredit = range.get_Item(i, 6).value;
currencyCode = range.get_Item(i, 7).value;
ExchRate = range.get_Item(i, 8).value;
offsetAccType = range.get_Item(i, 9).value;
offsetMainAccount = range.get_Item(i, 10).value;
invoiceID = range.get_Item(i, 11).value;
documentNum = range.get_Item(i, 12).value;
documentDate = range.get_Item(i, 13).value;
dueDate = range.get_Item(i, 14).value;
assetTransType = range.get_Item(i, 15).value;
taxGroup = range.get_Item(i, 16).value;
taxItemGroup = range.get_Item(i, 17).value;
tmpLegal_Entity = range.get_Item(i, 18).value;
tmpOffice_Location = range.get_Item(i, 19).value;
tmpProject_Location = range.get_Item(i, 20).value;
tmpDepartment = range.get_Item(i, 21).value;
tmpService_Line = range.get_Item(i, 22).value;
tmpSub_Service_Line = range.get_Item(i, 23).value;
tmpIndustry = range.get_Item(i, 24).value;
tmpSub_Industry = range.get_Item(i, 25).value;
tmpCoIndustry = range.get_Item(i, 26).value;
tmpPartner = range.get_Item(i, 27).value;
tmpPM_Employee = range.get_Item(i, 28).value;
tmpProject = range.get_Item(i, 29).value;
//journalNum = ledgerJournalTrans.JournalNum;

conVal = [tmpLegal_Entity, tmpOffice_Location, tmpProject_Location, tmpDepartment, tmpService_Line, tmpSub_Service_Line, tmpIndustry,
tmpSub_Industry, tmpCoIndustry, tmpPartner, tmpPM_Employee, tmpProject];
//ttsbegin;
if(ledgerJournalTrans.JournalNum)
{
//ledgerJournalTrans.clear();
//ledgerJournalTrans.initValue();
//ledgerAccountContract = new LedgerAccountContract();
//offsetledgerAccountContract = new LedgerAccountContract();

LedgerJournalTable = LedgerJournalTable::find(ledgerJournalTrans.JournalNum);
numSeqTable = NumberSequenceTable::find(LedgerJournalName::find(ledgerJournalTable.journalName).NumberSequenceTable);
numberseq = numberseq::newGetVoucherFromCode(numSeqTable.NumberSequence);
voucher = numberseq.voucher();
ledgerJournalTrans.Voucher = voucher;
//Ledger Dim Begin
if(ledgeraccType == '5')
{

ledgerJournalTrans.AccountType = LedgerJournalACType::Bank;
ledgerDim = LedgerDynamicAccountHelper::getDynamicAccountFromAccountNumber(ledgerMainAccount, LedgerJournalACType::Bank);
}
if(ledgeraccType == '1')
{

ledgerJournalTrans.AccountType = LedgerJournalACType::Cust;
ledgerDim = LedgerDynamicAccountHelper::getDynamicAccountFromAccountNumber(ledgerMainAccount, LedgerJournalACType::Cust);
}
if(ledgeraccType == '2')
{

ledgerJournalTrans.AccountType = LedgerJournalACType::Vend;
ledgerDim = LedgerDynamicAccountHelper::getDynamicAccountFromAccountNumber(ledgerMainAccount, LedgerJournalACType::Vend);
}
if(ledgeraccType == '4')
{

ledgerJournalTrans.AccountType = LedgerJournalACType::FixedAssets;
ledgerDim = LedgerDynamicAccountHelper::getDynamicAccountFromAccountNumber(ledgerMainAccount, LedgerJournalACType::FixedAssets);
}
if(ledgeraccType == '3')
{

ledgerJournalTrans.AccountType = LedgerJournalACType::Project;
ledgerDim = LedgerDynamicAccountHelper::getDynamicAccountFromAccountNumber(ledgerMainAccount, LedgerJournalACType::Project);
}
if(ledgerAccType == '0')
{
DimensionServiceProvider DimensionServiceProvider = new DimensionServiceProvider();
LedgerAccountContract LedgerAccountContract = new LedgerAccountContract();
DimensionAttributeValueContract ValueContract;
List ListValueContract = new List(Types::Class);
dimensionAttributeValueCombination dimensionAttributeValueCombination;
DimensionStorage dimStorage;
DimensionSetSegmentName dimensionSet;
DimensionAttribute dimensionAttribute;
DimensionAttributeValue dimensionAttributeValue;

int64 ledger = ledger::current();
mainAccount = MainAccount::findByMainAccountId(ledgerMainAccount);
select mainAccount where mainAccount.MainAccountId == ledgerMainAccount;
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(conval,hierarchyidx-1));
if(dimensionattributevalue)
{
valuecontract = new dimensionattributevaluecontract();
valuecontract.parmname(dimensionattribute.name) ;
valuecontract.parmvalue(dimensionattributevalue.cacheddisplayvalue);
ListValueContract.addend(valuecontract);
}
}
}

LedgerAccountContract.parmMainAccount(ledgerMainAccount);
LedgerAccountContract.parmValues(ListValueContract);
dimStorage = DimensionServiceProvider::buildDimensionStorageForLedgerAccount(LedgerAccountContract);
dimensionAttributeValueCombination = DimensionAttributeValueCombination::find(dimStorage.save());
ledgerRecId = dimensionAttributeValueCombination.RecId;
ledgerDim = ledgerRecId;
}
//Ledger Dim endds

//Offset Begin
if(offsetAccType == '5')
{

ledgerJournalTrans.OffsetAccountType = LedgerJournalACType::Bank;
offsetLedgerDim = LedgerDynamicAccountHelper::getDynamicAccountFromAccountNumber(offsetMainAccount, LedgerJournalACType::Bank);
}
if(offsetAccType == '1')
{

ledgerJournalTrans.OffsetAccountType = LedgerJournalACType::Cust;
offsetLedgerDim = LedgerDynamicAccountHelper::getDynamicAccountFromAccountNumber(offsetMainAccount, LedgerJournalACType::Cust);
}
if(offsetAccType == '2')
{

ledgerJournalTrans.OffsetAccountType = LedgerJournalACType::Vend;
offsetLedgerDim = LedgerDynamicAccountHelper::getDynamicAccountFromAccountNumber(offsetMainAccount, LedgerJournalACType::Vend);
}
if(offsetAccType == '4')
{

ledgerJournalTrans.OffsetAccountType = LedgerJournalACType::FixedAssets;
offsetLedgerDim = LedgerDynamicAccountHelper::getDynamicAccountFromAccountNumber(offsetMainAccount, LedgerJournalACType::FixedAssets);
}
if(offsetAccType == '3')
{

ledgerJournalTrans.OffsetAccountType = LedgerJournalACType::Project;
offsetLedgerDim = LedgerDynamicAccountHelper::getDynamicAccountFromAccountNumber(offsetMainAccount, LedgerJournalACType::Project);
}
if(offsetAccType == '0')
{
DimensionServiceProvider offsetDimensionServiceProvider = new DimensionServiceProvider();
LedgerAccountContract offsetLedgerAccountContract = new LedgerAccountContract();
DimensionAttributeValueContract offsetValueContract;
List offsetListValueContract = new List(Types::Class);
dimensionAttributeValueCombination offsetdimensionAttributeValueCombination;
DimensionStorage offsetdimStorage;
container offsetdimensionName = INTC_Global::getDimensionNameTID();
DimensionSetSegmentName offsetdimensionSet;
DimensionAttribute offsetdimensionAttribute;
DimensionAttributeValue offsetdimensionAttributeValue;

int64 ledger = ledger::current();
mainAccount = MainAccount::findByMainAccountId(offsetMainAccount);
select mainAccount where mainAccount.MainAccountId == offsetMainAccount;
offsetrecordvalue = DimensionHierarchy::getAccountStructure(mainAccount.RecId,Ledger::current());
offsethierarchyCount = DimensionHierarchy::getLevelCount(offsetrecordvalue);
offsetdimensionSet = DimensionHierarchyLevel::getDimensionHierarchyLevelNames(offsetrecordvalue);
for(offsethierarchyIdx = 1; offsethierarchyIdx <= offsethierarchyCount; offsethierarchyIdx++)
{
if(offsethierarchyIdx == 1)
continue;
offsetdimensionAttribute = DimensionAttribute::findByLocalizedName(offsetdimensionSet[offsethierarchyIdx],false,"en-us");
if(offsetdimensionAttribute)
{
offsetdimensionAttributeValue = DimensionAttributeValue::findByDimensionAttributeAndValue(offsetdimensionAttribute,conPeek(conVal,offsethierarchyIdx-1));
if(offsetdimensionAttributeValue)
{
offsetValueContract = new DimensionAttributeValueContract();
offsetValueContract.parmName(offsetdimensionAttribute.Name) ;
offsetValueContract.parmValue(offsetdimensionAttributeValue.CachedDisplayValue);
offsetListValueContract.addEnd(offsetValueContract);
}
}
}
offsetledgerAccountContract.parmMainAccount(offsetMainAccount);
offsetledgerAccountContract.parmValues(offsetListValueContract);
offsetdimStorage = DimensionServiceProvider::buildDimensionStorageForLedgerAccount(offsetledgerAccountContract);
offsetdimensionAttributeValueCombination = DimensionAttributeValueCombination::find(offsetdimStorage.save());
offsetLedgerRecId = offsetdimensionAttributeValueCombination.RecId;
offsetLedgerDim = offsetLedgerRecId;
}
// Offset Dim End

LedgerjournalTrans.LedgerDimension = ledgerDim;
LedgerjournalTrans.OffsetLedgerDimension = offsetLedgerDim;
LedgerjournalTrans.JournalNum = ledgerJournalTable.JournalNum;
LedgerjournalTrans.TransDate = postingDate;
ledgerJournalTrans.Txt = transTxt;
ledgerJournalTrans.AmountCurDebit = amountDebit;
ledgerJournalTrans.AmountCurCredit = amountCredit;
ledgerJournalTrans.CurrencyCode = currencyCode;
ledgerJournalTrans.ExchRate = ExchRate;
ledgerJournalTrans.Invoice = invoiceID;
ledgerJournalTrans.DocumentNum = documentNum;
ledgerJournalTrans.DocumentDate = documentDate;
ledgerJournalTrans.Due = dueDate;
LedgerJournalTrans_Asset.TransType = assetTransType;
ledgerJournalTrans.TaxGroup = taxGroup;
ledgerJournalTrans.TaxItemGroup = taxItemGroup;

LedgerJournalEngine ledgerJournalEngine = new LedgerJournalEngine();
ledgerJournalEngine.accountNumModified(ledgerJournalTrans);

ledgerJournalTrans.modifiedField(fieldNum(LedgerJournalTrans, LedgerDimension));
ledgerJournalTrans.modifiedField(fieldNum(LedgerJournalTrans, OffsetLedgerDimension));
ledgerJournalTrans.insert();

}
//ttscommit;
}
}
}
else
{
error("Error");
}
}
}

}

MSD AX | D365FO

Tuesday, 12 March 2019

Journal lines import in d365

No comments:

Post a Comment

Blog Archive