static void TIDF_UploadJournal(Args _args)
{
container accEntryPattern;
container offSetEntryPattern;
container financialDimensionFromExcel;
container conDimensionName;
int countCon;
int countLedger;
RecId recIdDefaultDimension;
DimensionDynamicAccount parmLedgerDimension;
DimensionDynamicAccount parmOffsetLedgerDimension;
LedgerJournalTable ledgerJournalTable;
LedgerJournalACType LedgerJournalACType;
ledgerJournalName ledgerJournalName;
journalID JournalID;
axLedgerJournalTable axLedgerJournalTable;
axledgerJournalTrans axLedgerJournalTrans;
LedgerJournalType JournalType = ledgerJournalType::Daily;
SysExcelApplication application = SysExcelApplication::construct();
SysExcelWorkbooks workbooks = application.workbooks();
SysExcelWorkbook workbook;
SysExcelWorksheets workSheets;
SysExcelWorksheet workSheet;
SysExcelCells cells;
SysExcelCell cell;
int row;
str filename, numSeq;
boolean _return = true;
;
startLengthyOperation();
filename = "C:\\test.xlsx";
JournalID = "G0615-0040";// <<< JOURNALNUM
//filename = _filePath;
try
{
if (workbooks.open(filename, false /*Update links*/, true /*Read only*/))
{
workbook = workbooks.item(1);
workSheets = workbook.worksheets();
workSheet = workSheets.itemFromNum(1); //worksheet keberapa dari excel di mulai dari angka 1
cells = workSheet.cells();
row = 5;
ledgerJournalTable = ledgerJournalTable::find(JournalID);
conDimensionName = Global::TIDgetDimensionName();
ttsBegin;
while (cells.item(row,2).value().bStr() != "")
{
//empty container
financialDimensionFromExcel = conNull();
countLedger = 0;
//Create Trans ...
axLedgerJournalTrans = new axLedgerJournalTrans();
axLedgerJournalTrans.parmJournalNum(JournalID);
//start from A
axLedgerJournalTrans.parmTransDate(cells.item(row,1).value().date());
axLedgerJournalTrans.parmAccountType(str2Enum(LedgerJournalACType, cells.item(row,2).value().bStr()));
//dimension value 1
financialDimensionFromExcel += cells.item(row,17).value().bStr();
if(cells.item(row,17).value().bStr() != "") {countLedger++;}
//dimension value 2
financialDimensionFromExcel += cells.item(row,18).value().bStr();
if(cells.item(row,18).value().bStr() != "") {countLedger++;}
//dimension value 3
financialDimensionFromExcel += cells.item(row,19).value().bStr();
if(cells.item(row,19).value().bStr() != "") {countLedger++;}
//dimension value 4
financialDimensionFromExcel += cells.item(row,20).value().bStr();
if(cells.item(row,20).value().bStr() != "") {countLedger++;}
//dimension value 5
financialDimensionFromExcel += cells.item(row,21).value().bStr();
if(cells.item(row,21).value().bStr() != "") {countLedger++;}
//dimension value 6
financialDimensionFromExcel += cells.item(row,22).value().bStr();
if(cells.item(row,22).value().bStr() != "") {countLedger++;}
//ledger Dimension
if(axLedgerJournalTrans.parmAccountType() == LedgerJournalACType::Ledger)
{
accEntryPattern =
[ cells.item(row, 3).value().bStr(),
cells.item(row, 3).value().bStr(),
countLedger
];
countCon = 1;
while(countCon <= countLedger)
{
accEntryPattern += conPeek(conDimensionName,countCon);
accEntryPattern += conPeek(financialDimensionFromExcel,countCon);
countCon++;
}
parmLedgerDimension = axLedgerJournalTrans.parmLedgerDimension(AxdDimensionUtil::getLedgerAccountId(accEntryPattern));
}
else
{
parmLedgerDimension = DimensionStorage::accountNum2LedgerDimension(cells.item(row, 3).value().bStr(),axLedgerJournalTrans.parmAccountType());
}
AxLedgerJournalTrans.clearField(fieldNum(ledgerJournalTrans, LedgerDimension), false);
axLedgerJournalTrans.parmLedgerDimension(parmLedgerDimension);
//offset dimension
axLedgerJournalTrans.parmOffsetAccountType(str2Enum(LedgerJournalACType, cells.item(row,8).value().bStr()));
if(axLedgerJournalTrans.parmOffsetAccountType() == LedgerJournalACType::Ledger)
{
offSetEntryPattern =
[ cells.item(row, 9).value().bStr(),
cells.item(row, 9).value().bStr(),
countLedger
];
countCon = 1;
while(countCon <= countLedger)
{
offSetEntryPattern += conPeek(conDimensionName,countCon);
offSetEntryPattern += conPeek(financialDimensionFromExcel,countCon);
countCon++;
}
parmOffsetLedgerDimension = axLedgerJournalTrans.parmLedgerDimension(AxdDimensionUtil::getLedgerAccountId(offSetEntryPattern));
}
else
{
parmOffsetLedgerDimension = DimensionStorage::accountNum2LedgerDimension(cells.item(row, 9).value().bStr(),axLedgerJournalTrans.parmAccountType());
}
axLedgerJournalTrans.clearField(fieldNum(ledgerJournalTrans, offsetLedgerDimension), false);
axLedgerJournalTrans.parmOffsetLedgerDimension(parmOffsetLedgerDimension);
if(cells.item(row,5).value().double() != 0)
{
axLedgerJournalTrans.parmAmountCurDebit(cells.item(row,5).value().double());
}
if(cells.item(row,6).value().double() != 0)
{
axLedgerJournalTrans.parmAmountCurCredit(cells.item(row,6).value().double());
}
axLedgerJournalTrans.parmtxt(cells.item(row,7).value().bStr());
axLedgerJournalTrans.parmCurrencyCode(cells.item(row,10).value().bStr());
axLedgerJournalTrans.parmExchRate(cells.item(row,11).value().double());
axLedgerJournalTrans.parmInvoice(cells.item(row,12).value().bStr());
axLedgerJournalTrans.parmPaymReference(cells.item(row,13).value().bStr());
axLedgerJournalTrans.parmBankTransType(cells.item(row,14).value().bStr());
axLedgerJournalTrans.parmDocumentNum(cells.item(row,15).value().bStr());
axLedgerJournalTrans.parmDocumentDate(cells.item(row,16).value().date());
//
axLedgerJournalTrans.parmPostingProfile(cells.item(row,23).value().bStr());
recIdDefaultDimension = Global::TIDcreateDefaultDimension(conDimensionName,financialDimensionFromExcel);
axLedgerJournalTrans.parmDefaultDimension(recIdDefaultDimension);
axLedgerJournalTrans.parmOffsetDefaultDimension(recIdDefaultDimension);
axLedgerJournalTrans.save();
row++;
}
ttsCommit;
info('Success !!');
application.quit();
}
}
catch(Exception::Error)
{
info("Journal Upload Error");
application.quit();
}
}
Month: July 2015
Job upload fixed asset ( AssetTable and AssetBook) example code
static void TIDF_uploadFAwithValueModels(Args _args)
{
AssetId assetId;
AssetGroupId assetGroupId;
AssetName assetName;
AssetLocationId assetLocationId;
AssetServiceLife assetServiceLife;
AssetLifeTimeRest assetLifeTimeRest;
AssetPostingProfile assetPostingProfile;
AssetBookId assetBookId;
TransDate depreciationStartDate,LastDepreciationDate,AcquisitionDate;
RecId recIdDefaultDimension;
AxAssetTable axAssetTable;
//table
AssetTable assetTable;
AssetGroup assetGroup;
AssetLocation assetLocation;
AssetBook assetBook;
AssetLedger assetLedger;
container financialDimensionFromExcel;
container conDimensionName;
SysExcelApplication application = SysExcelApplication::construct();
SysExcelWorkbooks workbooks = application.workbooks();
SysExcelWorkbook workbook;
SysExcelWorksheets workSheets;
SysExcelWorksheet workSheet;
SysExcelCells cells;
SysExcelCell cell;
int row;
str filename;
;
startLengthyOperation();
//variable yang diisi manual
filename = "C:\\FA master.xlsx";
assetPostingProfile = "FA POSTING";
depreciationStartDate = today();
LastDepreciationDate = today();
AcquisitionDate = today();
try
{
if (workbooks.open(filename, false /*Update links*/, true /*Read only*/))
{
workbook = workbooks.item(1);
workSheets = workbook.worksheets();
workSheet = workSheets.itemFromNum(1); //worksheet keberapa dari excel di mulai dari angka 1
cells = workSheet.cells();
conDimensionName = TIDgetDimensionName();
row = 5;
ttsBegin;
while (cells.item(row,2).value().bStr() != "")
{
//financial dimension
financialDimensionFromExcel = conNull();
//dimension value 1
financialDimensionFromExcel += cells.item(row,7).value().bStr();
//dimension value 2
financialDimensionFromExcel += cells.item(row,8).value().bStr();
//dimension value 3
financialDimensionFromExcel += cells.item(row,9).value().bStr();
//dimension value 4
financialDimensionFromExcel += cells.item(row,10).value().bStr();
//dimension value 5
financialDimensionFromExcel += cells.item(row,11).value().bStr();
//dimension value 6
financialDimensionFromExcel += cells.item(row,12).value().bStr();
//generateDefaultDimension
recIdDefaultDimension = TIDcreateDefaultDimension(conDimensionName,financialDimensionFromExcel);
//variable
assetId = TIDComVariant2STR(cells.item(row,2).value());
assetGroupId = TIDComVariant2STR(cells.item(row,1).value());
assetLocationId = TIDComVariant2STR(cells.item(row,6).value());
assetName = TIDComVariant2STR(cells.item(row,3).value());
assetServiceLife = str2num(TIDComVariant2STR(cells.item(row,4).value()));
assetLifeTimeRest = str2num(TIDComVariant2STR(cells.item(row,5).value()));
assetBookId = TIDComVariant2STR(cells.item(row,13).value());
//table
assetTable = assetTable::find(assetId);
assetGroup = AssetGroup::find(assetGroupId);
assetLocation = AssetLocation::find(assetLocationId);
//validation
if(!assetGroup || !assetLocation)
{
throw error(strFmt("Row : %1 ,Asset group or asset location not existed, please check again",row));
}
//create asset Table
if(!assetTable)
{
axAssetTable = new AxAssetTable();
axAssetTable.parmAssetId(assetId);
axAssetTable.parmAssetGroup(assetGroupId);
axAssetTable.parmName(assetName);
axAssetTable.parmLocation(assetLocationId);
axAssetTable.save();
}
//create assetBook
assetBook = assetBook::find(assetId,assetBookId);
if(!assetBook)
{
assetBook.clear();
assetBook.initValue();
assetBook.AssetId = assetId;
assetBook.BookId = assetBookId;
assetBook.PostingProfile = assetPostingProfile;
assetBook.ServiceLife = assetServiceLife;
assetBook.LifeTime = assetServiceLife*12;
assetBook.LifeTimeRest = assetLifeTimeRest;
assetBook.DepreciationStartDate = depreciationStartDate;
assetBook.LastDepreciationDate = LastDepreciationDate;
assetBook.AcquisitionDate = AcquisitionDate;
assetBook.DefaultDimension = recIdDefaultDimension;
assetBook.insert();
}
else
{
assetBook.selectForUpdate(true);
assetBook.ServiceLife = assetServiceLife;
assetBook.LifeTime = assetServiceLife*12;
assetBook.LifeTimeRest = assetLifeTimeRest;
assetBook.DepreciationStartDate = depreciationStartDate;
assetBook.LastDepreciationDate = LastDepreciationDate;
assetBook.AcquisitionDate = AcquisitionDate;
assetBook.DefaultDimension = recIdDefaultDimension;
assetBook.update();
}
row++;
}
ttsCommit;
application.quit();
}
}
catch(Exception::Error)
{
info("Fixed Asset upload Error");
application.quit();
}
}
TIDGetDimensionName
public static container TIDgetDimensionName()
{
DimensionAttribute dimAttr;
DimensionAttributeSetItem dimAttrSetItem;
DimensionEnumeration dimensionSetId;
DimensionAttributeValue dimAttributeValue;
container DimensionName;
dimensionSetId = DimensionCache::getDimensionAttributeSetForLedger();
while select dimAttr order by Name
where dimAttr.Type != DimensionAttributeType::MainAccount
join RecId from dimAttrSetItem
where dimAttrSetItem.DimensionAttribute == dimAttr.RecId &&
dimAttrSetItem.DimensionAttributeSet == dimensionSetId
{
dimensionName += dimAttr.Name;
}
return DimensionName;
}
TIDcreateDefaultDimension
static DimensionDefault TIDcreateDefaultDimension(container _attr, container _value, boolean _createIfNotFound = true)
{
DimensionAttributeValueSetStorage valueSetStorage = new DimensionAttributeValueSetStorage();
DimensionDefault result;
int i;
DimensionAttribute dimensionAttribute;
DimensionAttributeValue dimensionAttributeValue;
//_attr is dimension name in table DimensionAttribute
container conAttr = _attr;
container conValue = _value;
str dimValue;
for (i = 1; i <= conLen(conAttr); i++)
{
dimensionAttribute = dimensionAttribute::findByName(conPeek(conAttr,i));
if (dimensionAttribute.RecId == 0)
{
continue;
}
dimValue = conPeek(conValue,i);
if (dimValue != "")
{
// _createIfNotFound is "true". A dimensionAttributeValue record will be created if not found.
dimensionAttributeValue = dimensionAttributeValue::findByDimensionAttributeAndValue(dimensionAttribute,dimValue,false,_createIfNotFound);
// Add the dimensionAttibuteValue to the default dimension
valueSetStorage.addItem(dimensionAttributeValue);
}
}
result = valueSetStorage.save();
return result;
}
TIDComVariant2STR
public static str TIDComVariant2STR(COMVariant _variant)
{
str valueStr;
;
switch(_variant.variantType())
{
case COMVariantType::VT_EMPTY :
valueStr = '';
break;
case COMVariantType::VT_BSTR :
valueStr = _variant.bStr();
break;
case COMVariantType::VT_R4 :
case COMVariantType::VT_R8 :
if(_variant.double())
{
valueStr = strFmt("@SYS311964",
num2Str0(_variant.double(), 0),
num2str(_variant.double(),
0,
numOfDec(_variant.double()),
1,
0));
}
break;
default :
throw error(strfmt("@SYS26908",
_variant.variantType()));
}
return valueStr;
}
source :
http://krishhdax.blogspot.com/2012/05/ax2012-import-fixed-assets-table.html
https://axblog4u.wordpress.com/2012/09/06/tip-comvarianttype-for-real-values-in-dynamics-ax/
Convert null date from outer join on view to maxDate with SysComputedColumn
Example Code :
public static server str ReleasePostingDate()
{
return SysComputedColumn::if(
SysComputedColumn::isNullExpression(SysComputedColumn::returnField(
tableStr(TIDAPUninvoiceConsignmentItem) ,
identifierStr(LedgerJournalTableRealease),
fieldStr(LedgerJournalTable, PostedDateTime))),
SysComputedColumn::cast(strFmt("'%1'",DateTimeUtil::date(DateTimeUtil::maxValue())),'NVARCHAR'),
SysComputedColumn::cast(SysComputedColumn::returnField(
tableStr(TIDAPUninvoiceConsignmentItem) ,
identifierStr(LedgerJournalTableRealease),
fieldStr(LedgerJournalTable, PostedDateTime)),'NVARCHAR')
);
}
open form from infolog.
example code
SysInfoAction_FormRun infoAction = SysInfoAction_FormRun::newFormName(formStr(TIDFakturPajakTableIN));
infoAction.parmDescription("Open form VAT IN");
infoAction.parmCallerBuffer(VendInvoiceJour); // automatic dynalink
info("Purchase invoice contains VAT IN. Please record TAX Invoice Number in ID Localization module after this.", "", infoAction);
example max value datetime on Query AOT ranges
example :
value example is “>1/1/2154 06:59:59 am”
you can modify the query AOT value with condition expression see example on :
https://msdn.microsoft.com/en-us/library/bb314753.aspx
create Address , Phone , Email for Worker example code
Static void WorkerHireAddressUpdate(HCMworker _worker,StreetName _address = "",str _Phone = "",str _email = "")
{
HcmWorker hcmWorker;
DirPersonRecId _dirPersonRecid;
DirPartyPostalAddressView addressView;
dirPartyContactInfoView contactView;
DirParty dirParty;
container roles;
HcmWorker = _worker;
_dirPersonRecid = HcmWorker.Person;
roles = [LogisticsLocationRole::findBytype(LogisticsLocationRoleType::Home).RecId];
//create address
DirParty = DirParty::constructFromPartyRecId(_dirPersonRecid);
if(_address)
{
//addressView.LocationName
//addressView.City =
//addressView.State =
//addressView.ZipCode =
addressView.CountryRegionId = LogisticsAddressCountryRegion::findByISOCode(SysCountryRegionCode::countryInfo(curext())).CountryRegionId;
addressView.Street = _address;
addressView.IsPrimary = NoYes::Yes;
addressView.Party = _dirPersonRecid;
DirParty.createOrUpdatePostalAddress(addressView,roles);
}
//create primary email
if(_email)
{
contactView.clear();
contactView.LocationName = 'Primary Email';
contactView.Locator = _email;
contactView.Type = LogisticsElectronicAddressMethodType::Email;
contactView.Party = _dirPersonRecid;
contactView.IsPrimary = NoYes::Yes;
dirParty.createOrUpdateContactInfo(contactView);
}
//create primary phone
if(_Phone)
{
contactView.clear();
contactView.LocationName = 'Primary Phone';
contactView.Locator = _Phone;
contactView.Type = LogisticsElectronicAddressMethodType::Phone;
contactView.Party = _dirPersonRecid;
contactView.IsPrimary = NoYes::Yes;
dirParty.createOrUpdateContactInfo(contactView);
}
}
get company current Country Region Code example code
addressView.CountryRegionId = LogisticsAddressCountryRegion::findByISOCode(SysCountryRegionCode::countryInfo(curext())).CountryRegionId;
Get all financial dimension value and description from DimensionAttribute
static void TIDF_getDimensionValue(Args _args)
{
DimensionAttribute dimAttr;
DimensionAttributeViewContract dimAttrViewContract;
Query q;
QueryRun qR;
QueryBuildDataSource qbds;
Common common;
dimAttr = DimensionAttribute::findByName("1. STORE");
dimAttrViewContract = DimensionAttribute::getViewContract(dimAttr.RecId);
q = new Query();
changecompany (dimAttr.company())
{
qbds = q.addDataSource(dimAttrViewContract.parmViewId());
qbds.addSortField(dimAttrViewContract.parmValueFieldId(),SortOrder::Ascending);
// Apply ranges on language ID fields.
DimensionAttribute::addTranslViewRangesToBackingEntityQuery(dimAttr.RecId, qbds);
if (DimensionCache::instance().dimensionAttributeHasCategorization(dimAttr.RecId))
{
if (dimAttr.Type == DimensionAttributeType::MainAccount)
{
qbds.addRange(dimAttrViewContract.parmCategoryFieldId()).value(queryValue(LedgerChartOfAccounts::current()));
}
else if (dimAttr.Type == DimensionAttributeType::CustomList)
{
qbds = qbds.addDataSource(tablenum(DimensionAttributeDirCategory), 'DimAttDirCat');
qbds.relations(true);
qbds.joinMode(JoinMode::InnerJoin);
qbds.fetchMode(QueryFetchMode::One2One);
qbds.addLink(dimAttrViewContract.parmCategoryFieldId(), fieldnum(DimensionAttributeDirCategory, DirCategory));
qbds.addRange(fieldnum(DimensionAttributeDirCategory, DimensionAttribute)).value(strfmt('%1', dimAttr.RecId));
}
}
}
qR = new QueryRun(q);
while(qR.next())
{
common = qR.get(dimAttrViewContract.parmViewId());
info(strFmt("%1 %2",common.getFieldValue(dimAttrViewContract.parmValueFieldName()), common.getFieldValue(dimAttrViewContract.parmNameFieldName())));
}
}
