K:\AosService\PackagesLocalDirectory\Plugins\AxReportVmRoleStartupTask\DeployAllReportsToSSRS.ps1 -Module Application suite -ReportName customer.Report -PackageInstallLocation “K:\AosService\PackagesLocalDirectory”
Create General journal Lines Using X++ In D365 fo [ By importing Excel]
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....!
Create General journal lines using X++ by importing Excel in AX 2012
public void clicked()
{
LedgerJournalTable ledgerJournalTableloc;
LedgerJournalTrans ledgerJournalTransloc,ledgerJournalTrans1;
NumberSequenceTable numberSequenceTable;
NumberSeq numberSeq;
FormRun formrun;
SysExcelApplication application;
SysExcelWorkbooks workbooks;
SysExcelWorkbook workbook;
SysExcelWorksheets worksheets;
SysExcelWorksheet worksheet;
SysExcelCells cells;
COMVariantType type;
LedgerJournalACType ljcType;
FileName filename;
real value;
RefRecId ledgerdim,ledgerdim1;
container con;
int row = 1;
// date testdate , validfromdate ,validtodate ;
str invoicenum,Currency1 , Currency2;
Dialog dialog;
DialogField selectfile;
JournalFormTable journalFormTable;
super();
//formrun = element.args().caller();
// formrun = journalFormTable.formRun();
ledgerJournalTableloc = element.args().record() ;
info(ledgerJournalTableloc.JournalNum);
// validfromdate = FromDate.dateValue();
// validtodate = ToDate.dateValue();
dialog = new Dialog("Please select the file to load");
dialog.addText("Select file:");
selectfile = dialog.addField(ExtendedTypeStr("FilenameOpen"));
dialog.run();
if (dialog.closedOK())
{
application = SysExcelApplication::construct();
workbooks = application.workbooks();
filename =selectfile.value();
try
{
workbooks.open(filename);
}
catch (Exception::Error)
{
throw error('File cannot be opened');
}
workbook = workbooks.item(1);
worksheets = workbook.worksheets();
worksheet = worksheets.itemFromNum(1);
cells = worksheet.cells();
do
{
row++;
// testdate = cells.item(row, 1).value().date();
// Currency1 = cells.item(row, 2).value().bStr();
// Currency2 = cells.item(row, 3).value().bStr();
// value = any2real(cells.item(row,4).value().bStr());
//if ( ledgerJournalTableloc)
// {
ledgerJournalTransloc.JournalNum = ledgerJournalTableloc.JournalNum;
ledgerJournalTransloc.TransDate = cells.item(row, 1).value().date();
ledgerJournalTransloc.Company = curext();//range.get_Item(i, 3).value;
ledgerJournalTransloc.AccountType = LedgerJournalACType::Cust;
ledgerdim1 = DimensionStorage::getDynamicAccount(cells.item(row, 3).value().bStr(), LedgerJournalACType::Cust);
// ledgerdim1 = ledgerJournalTrans1.parmLedgerDimension(DimensionStorage::accountNum2LedgerDimension(
//cells.item(row, 3).value().bStr(),
//LedgerJournalACType::Cust)
//,curext());
ledgerJournalTransloc.LedgerDimension = ledgerdim1;
//ledgerJournalTransloc.LedgerDimension = DimensionStorage::getDynamicAccount(
// cells.item(row, 3).value().bStr(),
// LedgerJournalACType::Cust);
ledgerJournalTransloc.Txt = cells.item(row, 4).value().bStr();
ledgerJournalTransloc.AmountCurDebit = any2Real(cells.item(row, 5).value().bStr());
ledgerJournalTransloc.AmountCurCredit = any2real(cells.item(row, 6).value().bStr());
ledgerJournalTransloc.CurrencyCode = cells.item(row, 7).value().bStr();
ledgerJournalTransloc.OffsetAccountType = str2Enum(ljcType,cells.item(row, 9).value().bStr());
if (ledgerJournalTransloc.OffsetAccountType == LedgerJournalACType::Ledger)
{
con = [cells.item(row, 10).value().bStr() ,cells.item(row, 10).value().bStr(),
2,"businessunit", cells.item(row, 15).value().bStr(),"department",cells.item(row, 16).value().bStr()];
ledgerdim = AxdDimensionUtil::getLedgerAccountId(con);
ledgerJournalTransloc.OffsetLedgerDimension = ledgerdim;
}
ledgerJournalTransloc.DocumentNum = cells.item(row, 12).value().bStr();
ledgerJournalTransloc.DocumentDate = cells.item(row, 13).value().date();
ledgerJournalTransloc.Due =any2date( cells.item(row, 14).value().date());
ttsBegin;
numberSeq = NumberSeq::newGetVoucherFromId(ledgerJournalTableloc.NumberSequenceTable, false);
ledgerJournalTransloc.Voucher = numberSeq.voucher();
ttsCommit;
// numberSequenceTable = NumberSequenceTable::find(LedgerJournalName::find (ledgerJournalTableloc.JournalName).NumberSequenceTable);
// ledgerJournalTransloc.Voucher = NumberSeq::newGetVoucherFromCode(numberSequenceTable.NumberSequence).voucher();
invoicenum = ledgerJournalTransloc.daxInvoicenum(ledgerJournalTableloc.JournalNum);
ledgerJournalTransloc.DaxInvoiceNum = invoicenum ;
ledgerJournalTransloc.initValue();
ledgerJournalTransloc.insert();
//}
type = cells.item(row+1, 1).value().variantType();
}
while (type != COMVariantType::VT_EMPTY);
application.quit();
}
}
But In D365 fo above SysExcelApplication classes are removed. To Knw the proces in D365 fo follow my Next blog
Get a particuler postion value from a string value
Here im getting the values which are placed right side of the symbol "-". and adding next value to it
using x++ in d365 fo.
a simple way to increment string values as per the requirement.
sample code:
[Extensionof(formDataSourceStr(LedgerJournalTransDaily,LedgerJournalTrans))]
public final class LedgerJournalTrans_Extension
{
void initValue()
{
int i;
str iNnum,test;
FormDataSource fds = this;
LedgerJournalTrans ledgerJournalTransloc2;
LedgerJournalTable ledgerJournalTableloc;
LedgerJournalTrans ledgerJournalTransloc = fds.cursor();
FormRun formrun;
next initValue();
Args args = new Args();
formrun = element.args().caller();
ledgerJournalTableloc =formrun.dataSource().cursor() as LedgerJournalTable;
Info(ledgerJournalTableloc.JournalNum);
select ledgerJournalTransloc2 ORDER by RecId desc
where ledgerJournalTransloc2.JournalNum == ledgerJournalTableloc.JournalNum;
if(ledgerJournalTransloc2.InvoiceNum)
{
test = ledgerJournalTransloc2.InvoiceNum;
//by this higleted code we can split and get the required value from the string.
iNnum = subStr(test ,strFind(test,"-",1,strLen(test))+1,strLen(test));
//i = ledgerJournalTransloc2.recid+1;
i = str2Int(iNnum)+1;
ledgerJournalTransloc.InvoiceNum = ledgerJournalTableloc.JournalNum +'-'+ int2Str(i);
Info(strFmt("%1",i));
}
else
{
ledgerJournalTransloc.InvoiceNum = ledgerJournalTableloc.JournalNum +'-'+"1";
}
//InvoiceNum.text(ledgerJournalTableloc.JournalNum);
Info(ledgerJournalTransloc.InvoiceNum );
}
}
Create OffsetLedgerDimension in ax2012
DimensionDynamicAccount ledgerdim;
container con =["40100-Disp","110110", 4, "Department","0000114","CostCenter", "0000087", "CustomPurposeA","Nile", "CustomPurposeB", "Site1"];;
ledgerdim = AxdDimensionUtil::getLedgerAccountId(con);
info(strFmt("%1", ledgerdim));
Examples of CoC Extensions.
In Ax7 we cant directly customize source code. For that Microsoft has intoduced CoC extensions
I,e Chain of command extensions.
By Using Coc we can extend any element in AOT and customize as per our requiremnts.
Here some of the examples for CoC extensions.
[Extensionof(formDataSourceStr(LedgerJournalTransDaily,LedgerJournalTrans))]
public final class LedgerJournalTrans_Extension
{
void initValue()
{
FormDataSource fds = this;
LedgerJournalTable ledgerJournalTableloc;
LedgerJournalTrans ledgerJournalTransloc = fds.cursor();
FormRun formrun;
next initValue();
Args args = new Args();
formrun = element.args().caller();
ledgerJournalTableloc =formrun.dataSource().cursor() as LedgerJournalTable;
Info(ledgerJournalTableloc.JournalNum);
ledgerJournalTransloc.InvoiceNum = ledgerJournalTableloc.JournalNum;
//InvoiceNum.text(ledgerJournalTableloc.JournalNum);
}
}
formdatafield extension :
[ExtensionOF(formdatafieldstr(LedgerJournalTransDaily,LedgerJournalTrans,ledgerdimension))]
public final class PMELedgerJournalTransAccNumModified_Extension
{
public void modified()
{
LedgerJournalName ledgerJournalNamelocal ;
Ledgerjournaltrans ledgerJournalTrans ;
formdataobject fdobj = any2object(this) as formdataobject;
next modified();
ledgerJournalTrans = fdobj.datasource().cursor();
LedgerJournalTable ledgerjournaltableloc = element.args().record();
ledgerJournalNamelocal = LedgerJournalName::find(ledgerjournaltableloc.JournalName);
if(ledgerJournalNamelocal.PMECrijAutoGen == NoYes::Yes)
{
ledgerJournalTrans.Invoice = ledgerJournalTrans.PMEGetInvoiceNum(ledgerJournalTrans.ledgerdimension,ledgerjournaltableloc.JournalNum);
info(ledgerJournalTrans.Invoice);
}
}
}
Get required financial dimensions from LedgerJournaltable in ax 2012
static void GetFinancialDimensionValue1(Args _args)
{
LedgerJournalTable ledgjournal = LedgerJournalTable::find("00578");
DimensionAttributeValueSetStorage dimStorage;
Counter i;
dimStorage = DimensionAttributeValueSetStorage::find(ledgjournal.DefaultDimension);
for (i=1 ; i<= dimStorage.elements() ; i++)
{
//if you want particular Dimension then put if condition like this
if(strFmt("%1",DimensionAttribute::find(dimStorage.getAttributeByIndex(i)).Name)=="department")
{
info(strFmt("%1 = %2", DimensionAttribute::find(dimStorage.getAttributeByIndex(i)).Name,
dimStorage.getDisplayValueByIndex(i)));
}
}
}
Getting Required display value from offset main account in ax2012
static void getoffsetacount(Args _args)
{
DimensionStorage dimensionStorage;
DimensionStorageSegment segment;
int segmentCount, segmentIndex;
int hierarchyCount, hierarchyIndex;
str segmentName, segmentDescription;
SysDim segmentValue;
LEDGERJOURNALTRANS ledgerJournalTrans;
select ledgerJournalTrans where ledgerJournalTrans.RecId ==35637324216;
//ledgerJournalTrans =LedgerJournalTrans::find('00078','GNJL000861',false);
dimensionStorage = DimensionStorage::findById(ledgerJournalTrans.OffsetLedgerDimension);
if (dimensionStorage == null)
{
throw error("@SYS83964");
}
hierarchyCount = dimensionStorage.hierarchyCount();
//Loop through hierarchies to get individual segments
for(hierarchyIndex = 1; hierarchyIndex <= hierarchyCount; hierarchyIndex++)
{
segmentCount = dimensionStorage.segmentCountForHierarchy(hierarchyIndex);
//Loop through segments and display required values
for (segmentIndex = 1; segmentIndex <= segmentCount; segmentIndex++)
{
// Get segment
segment = dimensionStorage.getSegmentForHierarchy(hierarchyIndex, segmentIndex);
// Get the segment information
if (segment.parmDimensionAttributeValueId() != 0 &&
DimensionAttribute::find(DimensionAttributeValue::find(segment.parmDimensionAttributeValueId())
.DimensionAttribute).Name == "Department")
{
segmentValue = segment.parmDisplayValue();
segmentDescription = segment.getName();
}
}
}
info(strFmt("Department : %1 %2", segmentValue ,segmentDescription));
// return segmentValue + " (" + segmentDescription + ")";
}
Subscribe to:
Posts (Atom)