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()))); } }