Author Archives: admin

Byadmin

Delete Customers/Vendors in AX 2012

Many a times during Data Migration, we encounter a need to delete all customers/vendors and reload them. But in AX 2012, Customer and Vendor entities have a lot of tables which can contain their related information like addresses, party, location, electronic addresses etc. The below code will help you in such scenarios to delete the customer/vendor completely.

 

static void DeleteCustDetails(Args _args)

{

LogisticsLocation location;

DirPartyLocation  partyLocation;

LogisticsPostalAddress postalAdress;

LogisticsElectronicAddress elecAddress;

CustTable custTable;

DirPartyTable partyTable;

ContactPerson contactPerson;

CustBankAccount custBankAccount;

RecId partyRecId;

 

ttsBegin;

while select custTable

{

delete_from custBankAccount

where custBankAccount.custAccount == custTable.AccountNum;

 

delete_from postalAdress

exists join location

where postalAdress.Location == location.RecId

join partyLocation

where partyLocation.Party == custTable.Party &&

location.RecId == partyLocation.Location;

 

delete_from location

exists join partyLocation

where partyLocation.Party == custTable.Party &&

location.RecId == partyLocation.Location;

 

delete_from partyLocation

where partyLocation.Party == custTable.Party;

 

delete_from elecAddress

exists join location

where elecAddress.Location == location.RecId

exists join contactPerson

where contactPerson.ContactForParty == custTable.Party

join partyLocation

where partyLocation.Party == contactPerson.Party &&

location.RecId == partyLocation.Location;

 

delete_from location

exists join partyLocation

join contactPerson

where partyLocation.Party == contactPerson.Party &&

contactPerson.ContactForParty == custTable.Party &&

location.RecId == partyLocation.Location;

 

delete_from partyLocation

exists join contactPerson

where partyLocation.Party == contactPerson.Party &&

contactPerson.ContactForParty == custTable.Party;

 

delete_from partyTable

exists join contactPerson

where contactPerson.ContactForParty == custTable.Party &&

partyTable.RecId == contactPerson.Party;

 

delete_from contactPerson

where contactPerson.ContactForParty == custTable.Party;

partyRecId = custTable.Party;

custTable.selectForUpdate(true);

custTable.delete();

 

delete_from partyTable

where partyTable.RecId == partyRecId;

}

ttsCommit;

}

Byadmin

Clear BatchHistory from SQL – AX 2012

Many a times, we are faced with a scenario that Batch History is stuffing up the DB and sometimes becoming a DB overhead. So to keep the DB size in check and getting rid of the unnecessary Batch History that is more than 10/20/30 days (whatever project decides) old. We can schedule the Job to run on SQL every midnight so that the DB is least effected in terms of performance for Business use.

Here is the SQL Job code:

DECLARE @Count INT

Declare @for_delete INT

Declare @chunk_size INT

SELECT @chunk_size=1000

SELECT @Count = 0

select @for_delete=count(*) from BATCHJOBHISTORY

where BATCHJOBHISTORY.CREATEDDATETIME < (GETDATE()-10<<Number of Days>>)

While (@Count < @for_delete)

BEGIN

SELECT @Count = @Count + @chunk_size

BEGIN TRAN

delete top(@chunk_size) from BATCHJOBHISTORY

where BATCHJOBHISTORY.CREATEDDATETIME < (GETDATE()-10<<Number of Days>>)

delete BATCHHISTORY

where not exists (select RECID from BATCHJOBHISTORY

where BATCHJOBHISTORY.RECID = BATCHHISTORY.BATCHJOBHISTORYID)

delete BATCHCONSTRAINTSHISTORY

where not exists (select RECID from BATCHHISTORY

where BATCHHISTORY.RECID = BATCHCONSTRAINTSHISTORY.BATCHID)

COMMIT TRAN

END

Byadmin

DIXF data load using fixed field length files AX 2012

I have encountered scenario where I need to load data into AX 2012 using fixed field txt files from legacy system. I used DMF to upload the data. The catch is the fixed field txt file upload is different from the regular csv or excel file upload in DMF.

The file format settings will be as follows.

Here we need to perform additional steps to mention the field lengths of various fields and make sure they stay as-is post mapping them to the staging fields.

Once we create the Processing group, Entity record for the required Entity and file format as the one created for Fixed field file, click ‘Specify File Format’ button and click next.

Remove all the field list and add manually the fields from the way they are coming from the fixed length file and also mention the field lengths of the fields.

Click ‘Finish’.

Select the file path where the file for upload is placed on the file system.

Click on Generate Source mapping, this will enable AX to map from file to staging table

Click ‘Modify Source Mapping’ and map the fields mentioned in the mappings sheet ‘FileToStagingMappings’. Once done make sure that the field lengths stay same as above by running the ‘Specify file format’ wizard and selecting ‘No’ in the dialog box.

Now we can follow the regular process of Processing Group -> Get Staging Data

to start the data load.

Byadmin

DIXF – Changes in Generate methods – AX 2012 R3

Till AX 2012 R2, the Generate methods in DMF entity classes do not have attributes. These were identified by the field groups with names starting with Generate.

In AX 2012 R3, the Generate methods of entity classes need to have the Attributes mentioned so that they will be recognized in the Target entity mapping.

[DMFTargetTransformationAttribute(true),DMFTargetTransformationDescAttribute(<<Label for Descriotion>>),

DMFTargetTransformationSequenceAttribute(<<Attribute sequence>>)

,DMFTargetTransFieldListAttribute([fieldStr(<<DMFEntityTable>>,<<FieldName>>)])

]
And there is no need for us to create the field group with <<Generate method name>>.