Tuesday, October 31, 2017

Process AIF in Ax 2012

static void San_ProcessAIF(Args _args)
{
new AifGatewayReceiveService().run();
new AifInboundProcessingService().run();
new AifOutboundProcessingService().run();
new AifGatewaySendService().run();
}

Project Quotation Sent/Confirm & transfer in AX 2012 (X++)

//Sales Project Quotation Sent/Confirm & transfer in X++

static void SalesProjQuotation(Args _args)
{
SalesQuotationLine salesQuotationLine;
CustQuotationJour custQuotationJour;
CustQuotationTrans locCustQuotationTrans;
SalesQuotationTable salesQuotationTable;
SalesQuotationProjLinkWizard salesQuotationProjLinkWizard;
ProjTable projTable;
CustQuotationConfirmJour custQuotationConfirmJour;
SalesQuotationTransferToProject salesQuotationTransferToProject;

#AviFiles
SysOperationProgress progress = new SysOperationProgress();
int i;
//Function to send Project Quotation
void salesProjectQuotationSent(Common _record, DocumentStatus _documentStatus, QuotationDocNum _quotationDocNum = '')
{
#define.project('Project')
SalesQuotationTable salesQuotationTable;
SalesQuotationParmTable salesQuotationParmTable;
SalesQuotationEditLinesForm salesQuotationEditLinesForm = SalesQuotationEditLinesForm::construct(_documentStatus, #project, true);
ParmId parmId = salesQuotationEditLinesForm.parmId();

if (_record)
{
if (_record.TableId == tableNum(SalesQuotationTable))
{
salesQuotationTable = _record;
salesQuotationEditLinesForm.initParmSalesQuotationTable(salesQuotationTable);
}
salesQuotationEditLinesForm.createParmUpdate();
salesQuotationEditLinesForm.createParmTable(salesQuotationParmTable, salesQuotationTable);
salesQuotationParmTable.insert();

salesQuotationEditLinesForm.callerModuleAxapta(SalesQuotationEditLinesForm::getCallerModuleFromParm(#project));
salesQuotationEditLinesForm.parmId(parmId);
salesQuotationEditLinesForm.parmTransDate(systemDateGet());
salesQuotationEditLinesForm.prePromptInit();
salesQuotationEditLinesForm.run();
}
}

;
// Approve Sales Quotations
ttsBegin;
update_recordSet salesQuotationTable
setting QuotationStatus = SalesQuotationStatus::Approved
where salesQuotationTable.QuotationStatus == SalesQuotationStatus::Created;
ttsCommit;

info("Updated to Approved successfully.");

// Send the Quotation
ttsBegin;
while select forUpdate salesQuotationTable
where salesQuotationTable.QuotationStatus == SalesQuotationStatus::Approved
{
salesProjectQuotationSent(salesQuotationTable, DocumentStatus::Quotation);
}
ttsCommit;

info("Updated to Sent successfully.");

// Confirm and Transfering to Project
salesQuotationTransferToProject = new SalesQuotationTransferToProject();
salesQuotationProjLinkWizard = new SalesQuotationProjLinkWizard ();

progress = SysOperationProgress::newGeneral(#aviUpdate, 'Confirming Sales Project Quotation.', 1000);

while select salesQuotationTable
where salesQuotationTable.QuotationStatus == SalesQuotationStatus::Sent
{
infolog.clear();
projTable.clear();
salesQuotationLine.clear();
custQuotationJour.clear();
custQuotationConfirmJour.clear();

projTable = ProjTable::find(salesQuotationTable.ProjIdRef, true);

select firstOnly forUpdate custQuotationJour
where custQuotationJour.ProjIdRef == salesQuotationTable.ProjIdRef;

if(projTable.status().validateConfirmAndTransfer())
{
salesQuotationProjLinkWizard.parmProjTable(projTable);
salesQuotationProjLinkWizard.parmSalesQuotationTable(salesQuotationTable);
salesQuotationProjLinkWizard.linkQuotationToProject(projTable.ProjId, projTable.ProjInvoiceProjId);
salesQuotationProjLinkWizard.linkConfirmationToProject(projTable.ProjId, projTable.ProjInvoiceProjId);

ttsBegin;
// update the Sales project Quotation
salesQuotationProjLinkWizard.parmModelId(ProjParameters::find().ModelId);
salesQuotationProjLinkWizard.transferForecastToProject();
salesQuotationProjLinkWizard.transferItemReq();
salesQuotationProjLinkWizard.updateTransferredToProject(true, true);
ttsCommit;
}

i++;
progress.incCount();
progress.setText(strfmt("Sales Project Quotation confirmed: %1", i));
sleep(100);
}
}

Posting Project BegBalance in AX 2012

ProjJournalTable projJournalTable;
ProjBegBalJournalCheckPost projBegBalJournalCheckPost;
LedgerVoucher ledgerVoucher;
JournalTableData journalTableData;
;

try
{
ttsBegin;

while select forUpdate projJournalTable
where projJournalTable.Posted == NoYes::No &&
projJournalTable.JournalType == ProjJournalType::BegBalance
{
projBegBalJournalCheckPost = null;
projBegBalJournalCheckPost = ProjBegBalJournalCheckPost::newJournalCheckPost(false,
false,
JournalCheckPostType::Post,
projJournalTable.TableId,
projJournalTable.JournalId);

journalTableData = JournalTableData::newTable(projJournalTable);

projBegBalJournalCheckPost.postTransLedger(journalTableData.journalTransList().journalTransData(), ledgerVoucher);

}

ttsCommit;

info("Posted successfully.");
}
catch
{
info("Posting failed.");
}
}

Get Organisation hierarchies through X++/SQL

In AX 2012

//Project XPO Download link

Organisation Hierarchies

XPO Project Contains,

1) Query - OrganisationHierarchies
2) View - OrganisationHierarchies
3) Job - GetOrgansationalHierarchy

static void GetOrgansationalHierarchy(Args _args)
{
//Reference Query : OMHierarchy
OMHierarchyType OMHierarchyType1;
OMHierarchyRelationship OMHierarchyRelationship1;
OMRevisionEdit OMRevisionEdit1; //check this too
OMInternalOrganization omInternalOrganizationP,omInternalOrganizationC;
OMOperatingUnit omOperatingUntInt;

//Function to Clear
void clearBuffer()
{
OMHierarchyType1.clear();
omInternalOrganizationP.clear();
omInternalOrganizationC.clear();
omOperatingUntInt.clear();
OMRevisionEdit1.clear();
OMHierarchyRelationship1.clear();
}
;
clearBuffer();
while select Name,RecId
from OMHierarchyType1
{
while select HierarchyType,ParentOrganization,ChildOrganization
from OMHierarchyRelationship1
where OMHierarchyRelationship1.HierarchyType == OMHierarchyType1.RecId
&& OMHierarchyRelationship1.ParentOrganization != 0
outer join omInternalOrganizationP //Parent Organization
where omInternalOrganizationP.RecId == OMHierarchyRelationship1.ParentOrganization
outer join omInternalOrganizationC //Child Organization
where omInternalOrganizationC.RecId == OMHierarchyRelationship1.ChildOrganization
outer join omOperatingUntInt // Operating unit Type
where omOperatingUntInt.PartyNumber == omInternalOrganizationC.PartyNumber

{
info(strFmt("Org,%1,%2,%3,%4",OMHierarchyType1.Name,
omInternalOrganizationP.Name,
omInternalOrganizationC.Name,
omOperatingUntInt.OMOperatingUnitType
));
}
}
}

In SQL

SQL Script Query

CREATE VIEW vORGANISATIONHIERARCHIES
AS
SELECT
T1.NAME AS HIERARCHYNAME,
T2.CHILDORGANIZATION AS CHILDORGANIZATION,
T2.PARENTORGANIZATION AS PARENTORGANIZATION,
T2.HIERARCHYTYPE AS HIERARCHYTYPE,
T3.NAME AS PARENT,
T4.NAME AS CHILD,
T5.OMOPERATINGUNITTYPE AS ChildOMOPERATINGUNITTYPE,
T5.OMOPERATINGUNITNUMBER AS ChildOMOPERATINGUNITNUMBER,
T6.OMOPERATINGUNITNUMBER AS ParentOMOPERATINGUNITNUMBER,
T6.OMOPERATINGUNITTYPE AS ParentOMOPERATINGUNITTYPE
FROM OMHIERARCHYTYPE T1
CROSS JOIN OMHIERARCHYRELATIONSHIP T2
LEFT OUTER JOIN DIRPARTYTABLE T3
ON (T2.PARENTORGANIZATION=T3.RECID)
LEFT OUTER JOIN DIRPARTYTABLE T4
ON (T2.CHILDORGANIZATION=T4.RECID)
LEFT OUTER JOIN DIRPARTYTABLE T5
ON ((T4.RECID=T5.RECID)
AND (T5.INSTANCERELATIONTYPE IN (2377) ))
LEFT OUTER JOIN DIRPARTYTABLE T6
ON ((T3.RECID=T6.RECID)
AND (T6.INSTANCERELATIONTYPE IN (2377) ))
WHERE ( NOT ((T2.PARENTORGANIZATION=0))
AND (T1.RECID=T2.HIERARCHYTYPE))

Thursday, October 26, 2017

Import Currency Exchange Rates in Dynamics AX

\\Input
Parm Class

CurrencyCode fromCurrencyCode;
CurrencyCode toCurrencyCode;
TransDate exchangeRateDate;
CurrencyExchangeRate exchangeRate;
ExchangeRateTypeRecId exchangeRateTypeRecId;
ExchangeRateDisplayFactor exchangeRateDisplayFactor;

fromCurrencyCode = CompanyInfo::standardCurrency();
exchangeRateType = "Default";
exchangeRateTypeRecId = ExchangeRateType::find(exchangeRateType).RecId;
updateExchangeRates = Noyes::yes;
exchangeRateDisplayFactor = ExchangeRateDisplayFactor::One;

Table: ExchangeRate
Table Method
/// <summary>
/// Imports an exchange rate between the from currency and the accounting currency for the default
/// exchange rate type.
/// </summary>
/// <param name="_fromCurrencyCode">
/// The from currency.
/// </param>
/// <param name="_toCurrencyCode">
/// The to currency.
/// </param>
/// <param name="_exchangeRateTypeRecId">
/// The exchange rate type ID.
/// </param>
/// <param name="_exchangeRateDate">
/// The exchange rate date.
/// </param>
/// <param name="_exchangeRate">
/// The exchange rate.
/// </param>
/// <param name="_insertExchangeRate">
/// A Boolean value that determines whether to insert exchange rates.
/// </param>
/// <param name="_updateExchangeRate">
/// A Boolean value that determines whether to update exchange rates.
/// </param>
/// /// <param name="_exchangeRateDisplayFactor">
/// An exchange rate display factor
/// </param>
public static void importExchangeRate(
CurrencyCode _fromCurrencyCode,
CurrencyCode _toCurrencyCode,
ExchangeRateTypeRecId _exchangeRateTypeRecId,
TransDate _exchangeRateDate,
CurrencyExchangeRate _exchangeRate,
boolean _insertExchangeRate,
boolean _updateExchangeRate,
ExchangeRateDisplayFactor _exchangeRateDisplayFactor = 0)
{
ExchangeRateCurrencyPair exchangeRateCurrencyPair;
ExchangeRate exchangeRate;
;
date exchangeRateDate = _exchangeRateDate;
ttsbegin;
if (_insertExchangeRate || _updateExchangeRate)
{
select RecId from exchangeRateCurrencyPair
where exchangeRateCurrencyPair.FromCurrencyCode == _fromCurrencyCode
&& exchangeRateCurrencyPair.ToCurrencyCode == _toCurrencyCode
&& exchangeRateCurrencyPair.ExchangeRateType == _exchangeRateTypeRecId;
if (exchangeRateCurrencyPair)
{
select firstonly forupdate validtimestate(exchangeRateDate) ExchangeRate from exchangeRate
where exchangeRate.ExchangeRateCurrencyPair == exchangeRateCurrencyPair.RecId
&& exchangeRate.ValidFrom == exchangeRateDate;
}
if (exchangeRate)
{
if (_updateExchangeRate)
{
exchangeRate.ExchangeRate = _exchangeRate;
exchangeRate.update();
}
}
else
{
if (_insertExchangeRate)
{
if (!exchangeRateCurrencyPair)
{
exchangeRateCurrencyPair.FromCurrencyCode = _fromCurrencyCode;
exchangeRateCurrencyPair.ToCurrencyCode = _toCurrencyCode;
exchangeRateCurrencyPair.ExchangeRateType = _exchangeRateTypeRecId;

if (_exchangeRateDisplayFactor)
{
exchangeRateCurrencyPair.ExchangeRateDisplayFactor = _exchangeRateDisplayFactor;
}

exchangeRateCurrencyPair.insert();
}
exchangeRate.clear();
exchangeRate.ExchangeRateCurrencyPair = exchangeRateCurrencyPair.RecId;
exchangeRate.ExchangeRate = _exchangeRate;
exchangeRate.ValidFrom = _exchangeRateDate;
if (_exchangeRateDisplayFactor)
{
exchangeRateCurrencyPair.ExchangeRateDisplayFactor = _exchangeRateDisplayFactor;
}
exchangeRate.insert();
}
}
}
ttscommit;
}

Wednesday, October 25, 2017

Multiselect lookup in form Datasource fields in AX 2012

// Multi select lookup in form Data source fields in AX 2012

Note: This will not work for Dynamic string control in FORM

Class declaration
SysLookupMultiSelectCtrl    msCtrl;

Init
SysLookupMultiSelectCtrl::construct(element, <control name>, querystr(<queryName>));

Properties
string control auto declaration property set to Yes

SysLookupMultiSelectCtrl class
Method -> Init
//Function
boolean isOnGrid(FormStringControl _ctrl)
    {
        FormControl     parentControl;
        FormControl     currentControl;
        boolean         retValue;
        currentControl = _ctrl;
        retValue        = false;
        while(currentControl.parentControl() && !retValue)
        {
            parentControl = currentControl.parentControl();
            if(parentControl is FormGridControl)
                retValue = true;
            else
                currentControl = currentControl.parentControl();
        }
        return retValue;
    }
;
     if(isOnGrid(fsCtrlNames))
    {
        fsCtrlNames.registerOverrideMethod('modified', 'ctrlNames_textChange', this);
    }
    else
    {
        fsCtrlNames.registerOverrideMethod('textChange', 'ctrlNames_textChange', this);
    }

Method -> ctrlNames_textChange
FormDataSource      fds;
    Common              anyBuffer;
    _fsCtrlNames.text(fsCtrlNamesTmp.text());
    fds = _fsCtrlNames.dataSourceObject();
    if(fds)
    {
        anyBuffer = fds.cursor();
        anyBuffer.(_fsCtrlNames.dataField()) = fsCtrlNamesTmp.text();
        fds.refresh();
    }
    fsCtrlNames.modified();

Other Reference: https://krishhdax.blogspot.in/2015/06/editable-multi-select-lookup-in-grid.html

Friday, October 13, 2017

Encrypt and Decrypt in SQL

declare @encrypt varbinary(20)
select @encrypt = EncryptByPassPhrase('key', 'admin@1234' )
select @encrypt
select convert(varchar(100),DecryptByPassPhrase('key', @encrypt ))

//Encryption and Decryption in AX 2012

https://sangeethwiki.blogspot.in/2014/11/encrypt-and-decrypt-string-in-ax-2012.html

Using SQLPackage to import or export Azure SQL DB

Reference Source Link:

https://blogs.msdn.microsoft.com/azuresqldbsupport/2017/01/31/using-sqlpackage-to-import-or-export-azure-sql-db/

Purpose:

Explain how to easily and quickly use SQLPackage to import or export your Azure SQL Database.

Locating the SQLPackage.exe:

The SQLPackage.exe is part of the DacFramework which installed with SSMS or with SQL Server Data Tools

Or you can download only the DacFramework if you do not have already the management tools

Once the DacFramework / SSMS / SSDT is installed you can locate the SQLPackage.exe in this path

C:\Program Files (x86)\Microsoft SQL Server\130\DAC\bin

* Change the drive letter if you installed it to different location.

Export from Azure SQL DB to bacpac file:

to export database from your Azure SQL DB to bacpac file use this command:

sqlpackage.exe /Action:Export /ssn:tcp:<ServerName>.database.windows.net,1433 /sdn:<DatabaseName> /su:<UserName> /sp:<Password> /tf:<TargetFile> /p:Storage=File

example:

sqlpackage.exe /Action:Export /ssn:tcp:MyOwnServer.database.windows.net,1433 /sdn:AdventureWorks /su:AdminUser /sp:AdminPassword1 /tf:C:\Temp\AW.bacpac /p:Storage=File

Import from bacpac file to Azure SQL DB:

to import database from bacpac file to your Azure SQL DB use this command:

sqlpackage.exe /Action:Import /tsn:tcp:<ServerName>.database.windows.net,1433 /tdn:<TargetDatabaseName> /tu:<UserName> /tp:<Password> /sf:<Path to bacpac file> /p:DatabaseEdition=Premium /p:DatabaseServiceObjective=P4 /p:Storage=File

example:

import database to default target service tier S0:

sqlpackage.exe /Action:Import /tsn:tcp:MyServer.database.windows.net,1433 /tdn:AdventureWorks /tu:AdminUser /tp:AdminPassword1 /sf:C:\temp\AW.bacpac /p:Storage=File

import database and set target service tier to P2

sqlpackage.exe /Action:Import /tsn:tcp:MyServer.database.windows.net,1433 /tdn:AdventureWorks /tu:AdminUser /tp:AdminPassword1 /sf:C:\temp\AW.bacpac /p:DatabaseEdition=Premium /p:DatabaseServiceObjective=P2 /p:Storage=File

Remarks:

/p:Storage=File : is used to redirect the backing storage for the schema model used during extraction, this helpful with large databases that may cause out-of-memory exception if the default memory location is used.
Note that for large databases its recommended to set the service tier during the import, this help import process to run faster.
When exporting from active database, the database may not be transactional consistent as this process go object by object. If transactional consistent is needed it's recommended to export from copied database. (learn how to copy Azure SQL DB)

(thanks to ErikEJ for his comment)

Migrate your SQL Server database to Azure SQL Database

https://docs.microsoft.com/en-us/azure/sql-database/sql-database-migrate-your-sql-server-database

Thursday, October 12, 2017

Tuple in Python

Tuple: like as container or array

Sample:
Tuple:
tup1 = ('Sangeeth', 'chennai','1309','gowtham',1990', '2000','0406');
tup2 = (1,2,3,4,5,6,7,8);
print tup1[1]
print tup2[3:5]

output:
chennai
3,4,5

String:
var1 = "sangeeth"
var2 = "gowtham"
print var1[0]

print var2[0:3]

output:
s
gow

Packing and Unpacking

Sample:

x = ("Sangeeth", chennai, "madurai") # Tuple packing code

(me, where, too) = x # Tuple unpacking Code

print me

print where

print to

Output

Sangeeth

chennai

madurai

Comparison Operator

A comparison operator in Python can work with tuples.It starts with comparing the first element from each of the tuples

Sample:

a=(3,8)

b=(2,4)

if (a>b):print "a is bigger"

else: print "b is bigger"

a=(4,7)

b=(4,3)

if (a>b):print "a is bigger"

else: print "b is bigger"

a=(2,3)

b=(4,1)

if (a>b):print "a is bigger"

else: print "b is bigger"

Built In Function

all(), any(), enumerate(), max(), min(), sorted(), len(), tuple(), etc.

Variables and their uses in Python

Variable:
A variable is nothing but a reserved memory location to store values

Assignment
a = 100
print a

a="sangeeth"
b = 1309
print a+str(b)

Advantage:
Reuse, Delete variables

Reuse Variables:
a = 100
print a

a="san"
print a

Delete Variables:

w 100
a =140

delete w
print a

Accessing Variables Values
var1 = "Sangeeth"
var2 = "Pythin Testing in http://pythonfiddle.com/"
print "Print var1[0]:",var1[0]
print "Print var2[1:6]:",var2[1:6]

Operators:
[] - Return letters in specified Index.
[ : ] - Return letters from given range in specified Index.
in - Return true if specified char or set of char in string is present else False(ex: s= "sangeeth" print "ee" in s).
not in - Same as above, but not present in string.
+ - It concatenates two strings
r/R - Raw string suppresses actual meaning of escape characters.
%r - It insert the canonical string representation of the object (i.e., repr(o))
%s- It insert the presentation string representation of the object (i.e., str(o))

%d- it will format a number for display
* - Repeat (ex: x="Sangeeth"
y=3
print x*y

String Variables Replace:
str1 = 'I miss Australia'
str2 = str1.replace('miss', 'will go')
print(str2)

Output:
I will goAustralia

Change Casing in String:
string="I m in Australia"

print string.upper()

Print string.capitalize() # It capitalize only first letter of string

String within String:
print " sangeeth ".join("sangeeth")

Reversing String:
string="Sangeeth"

print ''.join(reversed(string))

Split String:
string="sangeeth is a good boy"

print string.split(' ')

Python First Sample Code

Sample Code Python

Sample 1:
def main():
print "Hello Folks!"

print "Welcome to Python Programming"

Sample 1 - Output:
Welcome to Python Programming

Reason:
It is Important that after defining the main function, you call the code by
if__name__=="__main__"
and then run the code, only then you will get the output "hello Folks" in console

Sample 2:

def main():
print "Hello Folks!"

if __name__== "__main__":
main()

print "Welcome to Python Programming"

Sample 2 - Output:
Hello Folks!
Welcome to Python Programming

Python

Python is an object-oriented programming language created by Guido Rossum in 1989. It is ideally designed for rapid prototyping of complex applications. It has interfaces to many OS system calls and libraries and is extensible to C or C++. Many large companies use the Python programming language include NASA, Google..

Python is widely used in Artificial Intelligence, Natural Language Generation, Neural Networks and other advanced fields of Computer Science. Python had deep focus on code readability & this class will teach you python from basics.

Characteristics:

It provides rich data types and easier to read syntax than any other programming languages
It is a platform independent scripted language with full access to operating system API's
Compared to other programming languages, it allows more run-time flexibility
It includes the basic text manipulation facilities of Perl and Awk
A module in Python may have one or more classes and free functions
Libraries in Pythons are cross-platform compatible with Linux, MacIntosh, and Windows
For building large applications, Python can be compiled to byte-code
Python supports functional and structured programming as well as OOP
It supports interactive mode that allows interacting testing and debugging of snippets of code
In Python, since there is no compilation step, editing, debugging and testing is fast.

Uses

Programming video games
Artificial Intelligence algorithm
Programming various scientific programs such as statistical models

Informatica Architecture

Informatica ETL tool consists of following services & components

Repository Service – Responsible for maintaining Informatica metadata & providing access of same to other services.
Integration Service – Responsible for the movement of data from sources to targets
Reporting Service - Enables the generation of reports
Nodes – Computing platform where the above services are executed

Informatica Designer - Used for creation of mappings between source and target
Workflow Manager – Used to create workflows and other task & their execution
Workflow Monitor – Used to monitor the execution of workflows
Repository Manager – Used to manage objects in repository

Sources & Targets

Informatica being an ETL and Data integration tool, you would be always handling and transforming some form of data. The input to our mappings in Informatica is called source system. We import source definitions from the source and then connect to it to fetch the source data in our mappings. There can be different types of sources and can be located at multiple locations. Based upon your requirement the target system can be a relational or flat file system. Flat file targets are generated on the Informatica server machine, which can be transferred later on using ftp.

Relational– these types of sources are database system tables. These database systems are generally owned by other applications which create and maintain this data. It can be a Customer Relationship Management Database, Human Resource Database, etc. for using such sources in Informatica we either get a replica of these datasets, or we get select privileges on these systems.

Flat Files - Flat files are most common data sources after relational databases in Informatica. A flat file can be a comma separated file, a tab delimited file or fixed width file. Informatica supports any of the code pages like ascii or Unicode. To use the flat file in Informatica, its definitions must be imported similar to as we do for relational tables.

ETL Informatica

What is Informatica?

Informatica is a Software development company, which offers data integration products. If offers products for ETL, data masking, data Quality, data replica, data virtualization, master data management, etc.

Informatica Powercenter ETL/Data Integration tool is a most widely used tool and in the common term when we say Informatica, it refers to the Informatica PowerCenter tool for ETL.

Informatica Powercenter is used for Data integration. It offers the capability to connect & fetch data from different heterogeneous source and processing of data.

For example, you can connect to an SQL Server Database and Oracle Database both and can integrate the data into a third system.

The latest version of Informatica PowerCenter available is 9.6.0. The different editions for the PowerCenter are

Standard edition
Advanced edition
Premium edition

Typical use cases for Informatica can be

An organization migrating from existing legacy system like mainframe to a new database system. So the migration of its existing data into a system can be performed.
Enterprises setting up their Data Warehouse would require an ETL tool to move data from the Production system to Warehouse.
Integration of data from various heterogeneous systems like multiple databases and file-based systems can be done using Informatica.
Informatica can be used as a data cleansing tool.

Why do we need Informatica?

Informatica comes to the picture wherever we have a data system available and at the backend we want to perform certain operations on the data. It can be like cleaning up of data, modifying the data, etc. based on certain set of rules or simply loading of bulk data from one system to another.

Informatica offers a rich set of features like operations at row level on data, integration of data from multiple structured, semi-structured or unstructured systems, scheduling of data operation. It also has the feature of metadata, so the information about the process and data operations are also preserved.

Wednesday, October 11, 2017

AX Export to Excel Sample X++

static void AX_ExportToExcel_Project(Args _args)
{
#AviFiles
SysOperationProgress progress = new SysOperationProgress();
ProjTable projTable;
SysExcelWorksheetHelper worksheetHelper;
SysExcelHelper sysExcelHelper;
SysExcelWorksheet worksheet;
int rowCount = 1;
int sumRow;
str worksheetName;
// COMVariant cellValue = new COMVariant(COMVariantInOut::Out);
str fileName;
str attachmentPath = "C:\\Temp\\";

#define.ProjId(1)
#define.Name(2)
#define.ProjGroupId(3)
#define.Type(4)
#define.Status(5)

progress.setCaption('Export To Excel');
progress.setAnimation(#AviTransfer);

worksheetName = "Project List";

sysExcelHelper = SysExcelHelper::construct();

sysExcelHelper.initialize();

worksheet = sysExcelHelper.addWorksheet(worksheetName);

worksheetHelper = SysExcelWorksheetHelper::construct(worksheet);

worksheetHelper.addColumnFromTableField(#ProjId, tablenum(ProjTable), fieldnum(ProjTable, ProjId));
worksheetHelper.addColumnFromTableField(#Name, tablenum(ProjTable), fieldnum(ProjTable, Name));
worksheetHelper.addColumnFromTableField(#ProjGroupId, tablenum(ProjTable), fieldnum(ProjTable, ProjGroupId));
worksheetHelper.addColumnFromTableField(#Type, tablenum(ProjTable), fieldnum(ProjTable, Type));
worksheetHelper.addColumnFromTableField(#Status, tablenum(ProjTable), fieldnum(ProjTable, Status));

//worksheetHelper.addColumn(#Dimension, "Dimension", Types::String);

while select *
from projTable
{
progress.setText(strfmt("Current Project %1", projTable.ProjId));

rowCount ++;
worksheetHelper.setCellValue(#ProjId, rowCount, ProjTable.ProjId);
worksheetHelper.setCellValue(#Name, rowCount, ProjTable.Name);
worksheetHelper.setCellValue(#ProjGroupId, rowCount, ProjTable.ProjGroupId);
worksheetHelper.setCellValue(#Type,rowCount, enum2str(ProjTable.Type));
worksheetHelper.setCellValue(#Status,rowCount, enum2str(ProjTable.Status));
}

worksheetHelper.autoFitColumns();
worksheetHelper.formatWorksheetTableStyle(sysExcelHelper.getOfficeVersion());

fileName = strFmt('%1_ProjectDetails_%2%3', attachmentPath,today(),sysExcelHelper.getFileExtension());

worksheet.protect("axProjects");
sysExcelHelper.save(filename);
sysExcelHelper.launchExcel();
}

Find query Range exists on Parameter Query in AX, DAX

Boolean hasRangesOnParmFilter;
Query _query = this.ParmQuery();

hasRangesOnParmFilter = SysQuery::queryHasRangesOrFilters(_query);

Monday, October 9, 2017

Workflow Scope in Dynamics 365 for Finance and Operations

Workflow Scope

Scopes

1) User

2) Business unit

3) Parent: Child business unit

4) Organization

1. User - Choosing this scope means the workflow will run only on the records owned by the same user as the workflow user.

2. Business Unit - Means the workflow will run on all records owned by the users of the same business unit as the workflow user.

3. Parent: Child Business Unit - The workflow will run on the records owned by the users of the same business unit as the workflow user as well as any child business units.

4. Organization - The workflow will run on records owned by any user in Application. Since it will trigger for all records, organization scope is the most used scope option.

Type of Grids and Grid Features, Editable Grids in Dynamics 365 for Finance and Operation

Editable grid

Before release of D365, users needed to open the existing records to update any data in the entity. Even if users want to update single field in multiple records, they had to open the individual record and update the field and save the record. For each record, they had to repeat these steps. This approach of updating data was time consuming and irritating.

With introduction to editable grid, Microsoft has allowed users to update the records right away with all those validations which was applicable on the form.

Editable grid is nothing but a list or view of records having editing capabilities without opening the record.

Type Of Grids

Types of grids

Read-only grid – View only, Can't be edit in DAX 365
Home grid – Entity’s public views and personal views with editing capabilities are called home grid.
Sub grid – Show related records in Same Form. Sub grids can also be made editable.
Nested grid – Nested grids are shown when we expand any record in view itself without opening form. This grid also shows related records of expanded record from view. (As of it is applicable only for Mobile and Tablet, Not for web client)
Dashboard grid

Features
1) Below types of fields are read only in editable grid:

Customer
Roll up Fields
Calculated Fields
Composite Fields
Status and Status Reason Field

2) Users don’t have to explicitly click on save button. Records are saved automatically when focus from the record is moved.

3) As its name suggests, only inline editing is possible and users cannot create records through inline editable grids. Users can still use quick create form to quickly create the records.

4) Below grid functionality is available for editable grids:

Sorting
Searching
Grouping
Filtering
Pagination
Column resizing
Column movement by simply drag and drop capabilities

// Editable Grids in Dynamics 365

Editable Grid on Entity

Steps:
1) Settings > Customization > Customize The System > Default Solution > Entity
2) Add Control -> Editable grid option
3) Select Editable Grid and click on Add.
4) Make the requires change in the Control section(Web, Tablet, Phone)
5) Save and Publish

Editable grid on FORM

Steps:
1) Go to your form , Open the Form Editor of that form.
2) Add Control -> Editable grid option
3) Select Editable Grid and click on Add.
4) Make the requires change in the Control section(Web, Tablet, Phone)
5) Save and Publish

Ledger and Default Dimension combination in Single view

Dimension View Project XPO

SQL Query:

Default dimension:
SELECT
T1.DefaultDimension AS DefaultDimension,
T2.DISPLAYVALUE AS Employee,
T3.DISPLAYVALUE AS CostCenter,
T4.DISPLAYVALUE AS Division,
T5.DISPLAYVALUE as Department,
'Default' as DimType
FROM
DefaultDimensionVIEW T1
LEFT OUTER JOIN DefaultDimensionVIEW T2
ON T1.DefaultDimension=T2.DefaultDimension
and T2.Name = 'Employee'
LEFT OUTER JOIN DefaultDimensionVIEW T3
ON T1.DefaultDimension=T3.DefaultDimension
and T3.Name = 'CostCenter'
LEFT OUTER JOIN DefaultDimensionVIEW T4
ON T1.DefaultDimension=T4.DefaultDimension
and T4.Name = 'Division'
LEFT OUTER JOIN DefaultDimensionVIEW T5
ON T1.DefaultDimension=T5.DefaultDimension
and T5.Name = 'Department'
GROUP BY T1.DefaultDimension,
T2.DISPLAYVALUE,
T3.DISPLAYVALUE,
T4.DISPLAYVALUE,
T5.DISPLAYVALUE

Ledger Dimension:

//View 1 staging

CREATE VIEW [dbo].[vDIMENSIONATTRIBUTELEVELVALUEALLVIEW_stag]

SELECT

T1.DIMENSIONATTRIBUTEVALUECOMBINATION AS VALUECOMBINATIONRECID,

T1.ORDINAL AS GROUPORDINAL,

T2.DISPLAYVALUE AS DISPLAYVALUE,

T2.RECID AS LEVELVALUERECID,

T2.ORDINAL AS VALUEORDINAL,

T2.DIMENSIONATTRIBUTEVALUEGROUP AS DIMENSIONATTRIBUTEVALUEGROUP,

T3.DIMENSIONATTRIBUTE AS DIMENSIONATTRIBUTE,

T3.RECID AS ATTRIBUTEVALUERECID,

T3.ENTITYINSTANCE AS ENTITYINSTANCE,

T3.HASHKEY AS ATTRIBUTEVALUEHASHKEY,

T4.Name as DimensionName,

1010 AS RECID

FROM DIMENSIONATTRIBUTEVALUEGROUPCOMBINATION T1

CROSS JOIN DIMENSIONATTRIBUTELEVELVALUE T2

CROSS JOIN DIMENSIONATTRIBUTEVALUE T3

CROSS JOIN DIMENSIONATTRIBUTE T4

WHERE (T1.DIMENSIONATTRIBUTEVALUEGROUP=T2.DIMENSIONATTRIBUTEVALUEGROUP

AND t4.RecId =t3.DIMENSIONATTRIBUTE

AND T2.DIMENSIONATTRIBUTEVALUE=T3.RECID)

GROUP BY T1.DIMENSIONATTRIBUTEVALUECOMBINATION,T1.ORDINAL,T2.DISPLAYVALUE,T2.RECID,T3.RECID,

T2.ORDINAL,T2.DIMENSIONATTRIBUTEVALUEGROUP, T3.DIMENSIONATTRIBUTE,T3.ENTITYINSTANCE,T3.HASHKEY,T4.NAME

//view 2

CREATE VIEW [dbo].[vDIMENSIONATTRIBUTELEVELVALUEALLVIEW]

SELECT

T1.VALUECOMBINATIONRECID as LedgerDimension,

T2.DISPLAYVALUE as AccountNum,

T3.DISPLAYVALUE as BusinessUnit,

T4.DISPLAYVALUE as CostCenter,

T5.DISPLAYVALUE as Project,

T6.DISPLAYVALUE

as OnsiteOffshore,

T7.DISPLAYVALUE

as Worker,

T8.DISPLAYVALUE

as Geo,

'LedgerDimension'

as Type

FROM

DIMENSIONATTRIBUTELEVELVALUEALLVIEW T1

LEFT OUTER JOIN vDIMENSIONATTRIBUTELEVELVALUEALLVIEW_stag T2

ON T1.VALUECOMBINATIONRECID=T2.VALUECOMBINATIONRECID

and T2.DimensionName = 'MainAccount'

LEFT OUTER JOIN vDIMENSIONATTRIBUTELEVELVALUEALLVIEW_stag T3

ON T1.VALUECOMBINATIONRECID=T3.VALUECOMBINATIONRECID

and T3.DimensionName = 'BusinessUnit'

LEFT OUTER JOIN vDIMENSIONATTRIBUTELEVELVALUEALLVIEW_stag T4

ON T1.VALUECOMBINATIONRECID=T4.VALUECOMBINATIONRECID

and T4.DimensionName = 'CostCenter'

LEFT OUTER JOIN vDIMENSIONATTRIBUTELEVELVALUEALLVIEW_stag T5

ON T1.VALUECOMBINATIONRECID=T5.VALUECOMBINATIONRECID

and T5.DimensionName = 'Project'

LEFT OUTER JOIN vDIMENSIONATTRIBUTELEVELVALUEALLVIEW_stag T6

ON T1.VALUECOMBINATIONRECID=T6.VALUECOMBINATIONRECID

and T6.DimensionName = 'OnsiteOffshore'

LEFT OUTER JOIN vDIMENSIONATTRIBUTELEVELVALUEALLVIEW_stag T7

ON T1.VALUECOMBINATIONRECID=T7.VALUECOMBINATIONRECID

and T7.DimensionName = 'Worker'

LEFT OUTER JOIN vDIMENSIONATTRIBUTELEVELVALUEALLVIEW_stag T8

ON T1.VALUECOMBINATIONRECID=T8.VALUECOMBINATIONRECID

and T8.DimensionName = 'Geo'

GROUP BY T1.VALUECOMBINATIONRECID, T2.DISPLAYVALUE,

T3.DISPLAYVALUE,

T4.DISPLAYVALUE,

T5.DISPLAYVALUE, T6.DISPLAYVALUE,

T7.DISPLAYVALUE,

T8.DISPLAYVALUE