Dynamics Ax Technical: June 2018

Monday, June 25, 2018

Date Field value exporting to excell

if date value is exported with time and if the date value is null but in excell getting value use follow below to conver date to str.

str documentDate;

if(ledgerJournalTrans.DocumentDate != dateNull())
{
documentDate = date2str(ledgerJournalTrans.DocumentDate,321,DateDay::Digits2,DateSeparator::Slash,DateMonth::Digits2,DateSeparator::Slash,DateYear::Digits4);
}
if(ledgerJournalTrans.DocumentDate == dateNull())
{
documentDate = date2str(dateNull(),321,DateDay::Digits2,DateSeparator::Slash,DateMonth::Digits2,DateSeparator::Slash,DateYear::Digits4);
}
cell = cells.item(Row,30);
cell.value(documentDate);

Wednesday, June 20, 2018

how to find the year wise week days through X++

static void WeekofDays(Args _args)
{
int i;
date j;
;
info(strFmt("%1",dayOfMth(systemDateGet())));
info(strFmt("%1",dayOfWk(systemDateGet())));
i = dayOfWk(systemDateGet());
switch(i)
{
case WeekDays::Monday :
info("Monday");
break;
case WeekDays::Tuesday:
info("Tuesday");
break;
case WeekDays::Wednesday:
info("Wednesday");
break;
case WeekDays::Thursday:
info("Thursday");
break;
case WeekDays::Friday:
info("Friday");
break;
case WeekDays::Saturday:
info("Saturday");
break;
case WeekDays::Sunday:
info("Sunday");
break;
default:
info("Nothing");
}

for(i=7;i<=365;i+=7)
{
j = systemDateGet()+i;
info(strFmt("%1--%2",date2str(j,123,2,-1,2,-1,4),dayOfMth(j)));
}
}

Free Text invoice values Importing before posting

class BFIL_createDefaultDimension
{
}

public DimensionDefault createDefaultDimension(container conAttr, container conValue)
{
DimensionAttributeValueSetStorage valueSetStorage = new DimensionAttributeValueSetStorage();
DimensionDefault result;
DimensionAttribute dimensionAttribute;
DimensionAttributeValue dimensionAttributeValue;
str dimValue;
int i;
boolean _createIfNotFound = true;

for (i = 1; i <= conLen(conAttr); i++)
{
dimensionAttribute = dimensionAttribute::findByName(conPeek(conAttr,i));
if (dimensionAttribute.RecId == 0)
{
continue;
}
dimValue = conPeek(conValue,i);
if (dimValue != "")
{
// The last parameter is "true". A dimensionAttributeValue record will be created if not found.
dimensionAttributeValue = dimensionAttributeValue::findByDimensionAttributeAndValue(dimensionAttribute,dimValue,false,true);
// Add the dimensionAttibuteValue to the default dimension
valueSetStorage.addItem(dimensionAttributeValue);
}
}
result = valueSetStorage.save();
return result;
}

Public void run(Filename _filename)
{
SysExcelApplication application;
SysExcelWorkbooks workbooks;
SysExcelWorkbook workbook;
SysExcelWorksheets worksheets;
SysExcelWorksheet worksheet;
SysExcelCells cells;
COMVariantType type;
Name name;
FileName filename;
LineNum lineNum;
int row = 1;
CustAccount custAccount,precustAccount;
TransDate invDate;
Description invDescription;
str numberSequenceGroup,accountNo,locationId,mainAccount;
str Accountingregion,Department,Entities,Product,Purpose,Region,Vendor;// Header Financial Dimensions declaration
str Accountingregion1,Department1,Entities1,Product1,Purpose1,Region1,Vendor1; //Line Financial Dimensions declaration
str HSNRate,SACRate,itcCategory,Exempt,HSNCode,SAC,salesTaxGroup;
str invoiceDate,qty,unitPr,amt;
Location companylocation,Companylocationid,customerlocation,Customerlocationid;
InvoiceQuantity quantity;
UnitPrice unitPrice;
Amount amount;
String10 TDSGroup;
Container account,conAttr, conValue;
int counter =0;
RefRecId ledgerDimension;
Set failedCustAccount = new Set(Types::String);
SetEnumerator setEnum;
BFIL_createDefaultDimension createDefaultDimension;
NoYes noYes;
ITCCategory_IN iTCCategory_IN;

// tables
CustTable custTable;
CustInvoiceTable custInvoiceTable;
CustInvoiceLine custInvoiceLine,custInvLine;
ServiceAccountingCodeTable_IN serviceAccountingCodeTable_IN;
HSNCodeTable_IN hSNCodeTable_IN;
LogisticsLocation logisticsLocation;
CustInvoiceLineTaxExtensionIN custInvoiceLineTaxExtensionIN;
SKS_ServiceAccountingCodeTable_IN sKS_ServiceAccountingCodeTable_IN;

str COMVariant2Str(COMVariant _cv, int _decimals = 0,int _characters = 0,int _separator1 = 0,int _separator2 = 0)
{
switch(_cv.variantType())
{
case (COMVariantType::VT_BSTR):
return _cv.bStr();
case (COMVariantType::VT_R4):
return num2str(_cv.float(),_characters,_decimals, _separator1,_separator2);
case (COMVariantType::VT_R8):
return num2str(_cv.double(),_characters,_decimals,_separator1,_separator2);
case (COMVariantType::VT_DECIMAL):
return num2str(_cv.decimal(),_characters,_decimals, _separator1, _separator2);
case (COMVariantType::VT_DATE):
return date2str(_cv.date(),123,2,1,2, 1,4);
case (COMVariantType::VT_EMPTY):
return "";
default:
throw error(strfmt("@SYS26908",_cv.variantType()));
}
return "";
}

createDefaultDimension = new BFIL_createDefaultDimension();

application = SysExcelApplication::construct();
workbooks = application.workbooks();
application.displayAlerts(false);
filename =_filename;
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();
cells.range('D:D').numberFormat('@');
cells.range('E:E').numberFormat('@');
cells.range('U:U').numberFormat('@');
cells.range('Z:Z').numberFormat('@');

ttsBegin;
do
{
row++;

CustAccount = COMVariant2Str(cells.item(row, 1).value());
numberSequenceGroup = COMVariant2Str(cells.item(row, 2).value());
invoiceDate = COMVariant2Str(cells.item(row, 3).value());
accountNo = COMVariant2Str(cells.item(row, 4).value());
locationId = COMVariant2Str(cells.item(row, 5).value());
TDSGroup = COMVariant2Str(cells.item(row, 6).value());
Accountingregion = COMVariant2Str(cells.item(row, 7).value());
Department = COMVariant2Str(cells.item(row, 8).value());
Entities = COMVariant2Str(cells.item(row, 9).value());
Product = COMVariant2Str(cells.item(row, 10).value());
Purpose = COMVariant2Str(cells.item(row, 11).value());
Region = COMVariant2Str(cells.item(row, 12).value());
Vendor = COMVariant2Str(cells.item(row, 13).value());
invDescription = COMVariant2Str(cells.item(row, 14).value());
mainAccount = COMVariant2Str(cells.item(row, 15).value());
HSNCode = COMVariant2Str(cells.item(row, 16).value());
SAC = COMVariant2Str(cells.item(row, 17).value());
qty = COMVariant2Str(cells.item(row, 18).value());
unitPr = COMVariant2Str(cells.item(row, 19).value());
amt = COMVariant2Str(cells.item(row, 20).value());
companylocation = COMVariant2Str(cells.item(row, 21).value());
HSNRate = COMVariant2Str(cells.item(row, 22).value());
SACRate = COMVariant2Str(cells.item(row, 23).value());
itcCategory = COMVariant2Str(cells.item(row, 24).value());
Exempt = COMVariant2Str(cells.item(row, 25).value());
customerlocation = COMVariant2Str(cells.item(row, 26).value());
Accountingregion1 = COMVariant2Str(cells.item(row, 27).value());
Department1 = COMVariant2Str(cells.item(row, 28).value());
Entities1 = COMVariant2Str(cells.item(row, 29).value());
Product1 = COMVariant2Str(cells.item(row, 30).value());
Purpose1 = COMVariant2Str(cells.item(row, 31).value());
Region1 = COMVariant2Str(cells.item(row, 32).value());
Vendor1 = COMVariant2Str(cells.item(row, 33).value());

account = [mainAccount, mainAccount];
ledgerDimension = AxdDimensionUtil::getLedgerAccountId(account);
invDate = str2Date(invoiceDate,123);
quantity = str2int(qty);
unitPrice = any2real(unitPr);
amount = quantity*unitPrice;
if(precustAccount != CustAccount && !failedCustAccount.in(CustAccount))
{
select custTable where custTable.AccountNum == CustAccount;
custInvoiceTable.clear();
if (custTable.RecId > 0)
{
custInvoiceTable.clear();
custInvoiceTable.OrderAccount = custTable.AccountNum;
custInvoiceTable.modifiedField(fieldNum(CustInvoiceTable, OrderAccount));
custInvoiceTable.DefaultDimension = ledgerDimension;
custInvoiceTable.InvoiceDate = invDate;
custInvoiceTable.SKS_BeneficiaryAccount = accountNo;
custInvoiceTable.DeliveryLocation = LogisticsLocation::findByLocationId(locationId).RecId;
conAttr = ["AccountingRegion","Department","Entities","Product","Purpose","Region","Vendor"];
conValue = [ Accountingregion, Department, Entities,Product,Purpose,Region,Vendor];
custInvoiceTable.DefaultDimension = createDefaultDimension.createDefaultDimension(conAttr, conValue);
custInvoiceTable.TDSGroup_IN = TDSGroup;
custInvoiceTable.insert();
lineNum = 0;
precustAccount = CustAccount;
}
else
{
if (!failedCustAccount.in(CustAccount))
{
failedCustAccount.add(CustAccount);
}
}
}
if (custInvoiceTable.RecId > 0)
{
counter++;

custInvoiceLine.clear();
custInvoiceLine.initValue();
custInvoiceLine.initFromCustInvoiceTable(custInvoiceTable);
custInvoiceLine.LedgerDimension = DimensionStorage::getDefaultAccount(MainAccount::findByMainAccountId(mainAccount).RecId);
custInvoiceLine.DefaultDimension = ledgerDimension;
conAttr = ["AccountingRegion","Department","Entities","Product","Purpose","Region","Vendor"];
conValue = [ Accountingregion1, Department1, Entities1,Product1,Purpose1,Region1,Vendor1];
custInvoiceLine.DefaultDimension = createDefaultDimension.createDefaultDimension(conAttr, conValue);
custInvoiceLine.Description = invDescription;
custInvoiceLine.Quantity = quantity;
custInvoiceLine.UnitPrice = unitPrice;
custInvoiceLine.AmountCur = amount;
custInvoiceLine.ParentRecId = custInvoiceTable.RecId;
custInvoiceLine.InvoiceTxt = invDescription;
custInvoiceLine.SKS_HSNCode = SKS_HSNCodeTable_IN::findByCode(HSNCode);

select firstonly sKS_ServiceAccountingCodeTable_IN
where sKS_ServiceAccountingCodeTable_IN.SAC == COMVariant2Str(cells.item(row, 17).value());

custInvoiceLine.SKS_ServiceAccountingCodeTable = sKS_ServiceAccountingCodeTable_IN.RecId; //SKS_ServiceAccountingCodeTable_IN::findByServiceAccountingCode(SAC);
custInvoiceLine.Exempt_IN = str2enum(noYes,Exempt);
custInvoiceLine.ITCCategory_IN = str2enum(iTCCategory_IN,itcCategory);

//Tax Informatiom
custInvoiceLine.CompanyLocation_IN = LogisticsLocation::findByLocationId(companylocation).RecId;
custInvoiceLine.HSNCodeTable_IN = HSNCodeTable_IN::findByCode(HSNRate);
custInvoiceLine.ServiceCodeTable_IN = ServiceAccountingCodeTable_IN::findByServiceAccountingCode(SACRate);
custInvoiceLine.CustomerLocation_IN = LogisticsLocation::findByLocationId(customerlocation).RecId;
lineNum += 1;
custInvoiceLine.LineNum = lineNum;
custInvoiceLine.insert();
//Added by sarath-->>start
select forupdate custinvline where custinvline.recid == custinvoiceline.recid
&& custinvline.linenum == linenum;
ttsbegin;
custinvline.serviceaccountingcodetable_in = serviceaccountingcodetable_in::findbyserviceaccountingcode(SACRate);
custinvline.update();
ttscommit;
//Added by sarath-->>ended
//Specific to Indian localization, not required for other localizations
custInvoiceLineTaxExtensionIN.CustInvoiceLine = custInvoiceLine.RecId;
custInvoiceLineTaxExtensionIN.TaxInformation_IN = TaxInformation_IN::findDefaultbyLocation(DirPartyTable::find(CompanyInfo::findDataArea(curext()).PartyNumber).PrimaryAddressLocation).RecId;
custInvoiceLineTaxExtensionIN.initValue();
custInvoiceLineTaxExtensionIN.insert();

}
type = cells.item(row+1, 1).value().variantType();

}

while (type != COMVariantType::VT_EMPTY);
ttsCommit;
application.workbooks().close();
application.quit();
application.finalize();
application = null;

info(strFmt("%1 - Uploaded Successfully",counter));
}

Friday, June 15, 2018

Export Legalentity Multiple Address

static void BFIL_LegalEntityMultipleAddress(Args _args)
{
SysExcelApplication application;
SysExcelWorkbooks workbooks;
SysExcelWorkbook workbook;
SysExcelWorksheets worksheets;
SysExcelWorksheet worksheet;
SysExcelCells cells;
SysExcelCell cell;
SysExcelFont font;
int row;

CompanyInfo companyInfo;
DirPartyTable dirPartyTable;
LogisticsPostalAddress postalAddress;
LogisticsLocation location;
DirPartyLocation PartyLocation;

// intializing classes to export excel
application = SysExcelApplication::construct();
workbooks = application.workbooks();
workbook = workbooks.add();
worksheets = workbook.worksheets();
worksheet = worksheets.itemFromNum(1);
cells = worksheet.cells();
cells.range('C:C').numberFormat('@');

// Setting Header values
cell = cells.item(1, 1);
cell.value('LegalEntity Id');
font = cell.font();
font.bold(true);

cell = cells.item(1, 2);
cell.value('LegalEntity Name');
font = cell.font();
font.bold(true);
row = 1;
cell = cells.item(1, 3);
cell.value('Location Id');
font = cell.font();
font.bold(true);

cell = cells.item(1, 4);
cell.value('Location Name');
font = cell.font();
font.bold(true);
row = 1;

cell = cells.item(1, 5);
cell.value('Address');
font = cell.font();
font.bold(true);
row = 1;

cell = cells.item(1, 6);
cell.value('State');
font = cell.font();
font.bold(true);

cell = cells.item(1, 7);
cell.value('City');
font = cell.font();
font.bold(true);
row = 1;

/*
// inserting the Multiple record for only one current legalentity ----
companyInfo = companyInfo::findDataArea(curext());
dirPartyTable = DirPartyTable::findRec(companyInfo.RecId);

while select PartyLocation where PartyLocation.party == dirPartyTable.recid
join location where location.recid == PartyLocation.Location
join postalAddress where postalAddress.Location == location.RecId
&& partyLocation.Location == postalAddress.Location

*/

// insert the all legalEntity multiple address

while select companyInfo
{
dirPartyTable = DirPartyTable::findRec(companyInfo.RecId);
while select PartyLocation where PartyLocation.party == dirPartyTable.recid
join location where location.recid == PartyLocation.Location
join postalAddress where postalAddress.Location == location.RecId
&& partyLocation.Location == postalAddress.Location
{

row++;
cell = cells.item(row, 1);
cell.value(companyInfo.DataArea);
cell = cells.item(row, 2);
cell.value(companyInfo.Name);
cell = cells.item(row, 3);
cell.value(location.LocationId);
cell = cells.item(row, 4);
cell.value(location.Description);
cell = cells.item(row, 5);
cell.value(postalAddress.Address);
cell = cells.item(row, 6);
cell.value(postalAddress.State);
cell = cells.item(row, 7);
cell.value(postalAddress.City);
}
}

application.visible(true);
}

Export customer Multiple address Using X++

static void BFIL_CustomerMultipleAddressExport(Args _args)
{
SysExcelApplication application;
SysExcelWorkbooks workbooks;
SysExcelWorkbook workbook;
SysExcelWorksheets worksheets;
SysExcelWorksheet worksheet;
SysExcelCells cells;
SysExcelCell cell;
SysExcelFont font;
int row;
CustTable custTable;
DirPartyTable dirPartyTable;
LogisticsPostalAddress postalAddress;
LogisticsLocation location;
DirPartyLocation PartyLocation;

// intializing classes to export excel
application = SysExcelApplication::construct();
workbooks = application.workbooks();
workbook = workbooks.add();
worksheets = workbook.worksheets();
worksheet = worksheets.itemFromNum(1);
cells = worksheet.cells();
cells.range('A:A').numberFormat('@');

// Setting Header values
cell = cells.item(1, 1);
cell.value('Customer Account');
font = cell.font();
font.bold(true);

cell = cells.item(1, 2);
cell.value('Customer Name');
font = cell.font();
font.bold(true);
row = 1;
cell = cells.item(1, 3);
cell.value('Location Id');
font = cell.font();
font.bold(true);

cell = cells.item(1, 4);
cell.value('Location Name');
font = cell.font();
font.bold(true);
row = 1;

cell = cells.item(1, 6);
cell.value('Address');
font = cell.font();
font.bold(true);
row = 1;

cell = cells.item(1, 7);
cell.value('State');
font = cell.font();
font.bold(true);

cell = cells.item(1, 8);
cell.value('City');
font = cell.font();
font.bold(true);
row = 1;

// inserting data row wise selecting Multiple record----
while select custTable
where custTable.AccountNum
join dirPartyTable
where custTable.Party == dirPartyTable.RecId
join PartyLocation
where PartyLocation.party == dirPartyTable.recid
join location
where location.recid == PartyLocation.Location
join postalAddress
where postalAddress.Location == location.RecId
&& partyLocation.Location == postalAddress.Location
{
row++;
cell = cells.item(row, 1);
cell.value(custTable.AccountNum);
cell = cells.item(row, 2);
cell.value(dirPartyTable.Name);
cell = cells.item(row, 3);
cell.value(location.LocationId);
cell = cells.item(row, 4);
cell.value(location.Description);
cell = cells.item(row, 5);
cell.value(postalAddress.Address);
cell = cells.item(row, 6);
cell.value(postalAddress.State);
cell = cells.item(row, 7);
cell.value(postalAddress.City);
}

application.visible(true);
}

Tuesday, June 5, 2018

Enable/Disable Recall button in workflow details form

Goto Class ----->WorkflowStatusForm ------>OnActive method

write below logic

//Added by sarath for recall button will enable only for admin ...>>start
str curUser;
curUser = curUserId();
if(curUser == "axadmin" && statusTable.DocumentType == "Q&Q receipt header")
{
terminateButton.enabled(workflowStatusTreeInfo.parmStatus() ==WorkflowTrackingStatus::Pending || workflowStatusTreeInfo.parmStatus() == WorkflowTrackingStatus::Faulted);
}
else if (curUser != "axadmin" && statusTable.DocumentType == "Q&Q receipt header")
{
terminateButton.enabled(false);
}
//Added by sarath for recall button will enable only for admin ...>>end

Dynamics Ax Technical