static void TIDF_UploadFAJournal(Args _args) { container accEntryPattern; container offSetEntryPattern; //for default dimension container financialDimensionFromExcel; DimensionDefault defaultDimension; int countLedger; int countCon; AssetBookTable _AssetBookTable; AssetBookId _AssetBookId; LedgerJournalTrans_Asset _LedgerJournalTrans_Asset; LedgerJournalTrans _LedgerJournalTrans; LedgerJournalTable _LedgerJournalTable; AssetBookTableDerived _AssetBookTableDerived; AssetBookTableDerivedJournal _AssetBookTableDerivedJournal; AssetTransTypeJournal _AssetTransTypeJournal; AssetTransType _AssetTransType; LedgerJournalACType LedgerJournalACType; ledgerJournalName ledgerJournalName; journalID JournalID; LedgerJournalId _journalId; axLedgerJournalTable axLedgerJournalTable; axledgerJournalTrans axLedgerJournalTrans; LedgerJournalType JournalType = ledgerJournalType::Daily; TransDate _TransDate; LedgerTransType _LedgerTransType; str AccountNum; LedgerJournalACType _AccountType; CurrencyCode _CurrencyCode; real _ExhcangeRate; real _AmountCurDebit; real _AmountCurCredit; str _InvoiceId; TransTxt _TransTxt; DueDate _DueDate; DocumentNum _DoucumentNum; DocumentDate _DocumentDate; str Branch; str Departement; str Product; str Project; TaxGroupJournal _TaxJournal; TaxGroupJournal _TaxItemJournal; NoYes _Transferred; BankTransactionType _BankTransactionType; TaxCode _TaxCode; LedgerJournalACType _OffsetAccountType; str _OffsetAcountNum; VendPostingProfile _PostingProfile; SysExcelApplication application = SysExcelApplication::construct(); SysExcelWorkbooks workbooks = application.workbooks(); SysExcelWorkbook workbook; SysExcelWorksheets workSheets; SysExcelWorksheet workSheet; SysExcelCells cells; SysExcelCell cell; int row; str filename, numSeq; str strAccount,strAccountOffset; Name dimensionName1,dimensionName2,dimensionName3,dimensionName4; str MainAcc,Dim1,Dim2,Dim3,Dim4,OffsetAcc; str AccountType,OffsetAccountType; str PayReff,BankTransType,DocNo,PostingProfile,noInvoice, transType; TransDate DocDate; str StrDate; boolean _return = true; container dimensionAttrName = Global::TIDgetDimensionName(); _OffsetAcountNum = '9900-0000'; startLengthyOperation(); filename = @"C:\\#Tectura#\fanddy\UploadFAJournal";//FA Dep Commercial.xlsx";//Depreciation commercial //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 = 6; ttsBegin; while (cells.item(row,5).value().bStr() != '' ) { //financial dimension financialDimensionFromExcel = conNull(); //dimension value 1 financialDimensionFromExcel += cells.item(row,10).value().bStr(); if(cells.item(row,10).value().bStr() != '' ) {countLedger++;} //dimension value 2 financialDimensionFromExcel += cells.item(row,11).value().bStr(); if(cells.item(row,11).value().bStr() != '' ) {countLedger++;} //dimension value 3 financialDimensionFromExcel += cells.item(row,12).value().bStr(); if(cells.item(row,12).value().bStr() != '' ) {countLedger++;} //dimension value 4 financialDimensionFromExcel += cells.item(row,13).value().bStr(); if(cells.item(row,13).value().bStr() != '' ) {countLedger++;} //dimension value 5 financialDimensionFromExcel += cells.item(row,14).value().bStr(); if(cells.item(row,14).value().bStr() != '' ) {countLedger++;} //dimension value 6 financialDimensionFromExcel += cells.item(row,15).value().bStr(); if(cells.item(row,15).value().bStr() != '' ) {countLedger++;} //start from A _journalId = cells.item(row,1).value().bStr(); _TransDate = cells.item(row,2).value().date(); AccountNum = cells.item(row,3).value().bStr(); transType = cells.item(row,4).value().bStr(); _AssetBookId = cells.item(row,5).value().bStr(); _CurrencyCode = cells.item(row,6).value().bStr(); _ExhcangeRate = cells.item(row,7).value().double(); _AmountCurDebit = cells.item(row,8).value().double(); _AmountCurCredit = cells.item(row,9).value().double(); //_TransDate = str2Date(StrDate, 123); defaultDimension = TIDcreateDefaultDimension(dimensionAttrName,financialDimensionFromExcel); offSetEntryPattern = [ _OffsetAcountNum, _OffsetAcountNum, countLedger ]; countCon = 1; while(countCon <= countLedger) { offSetEntryPattern += conPeek(dimensionAttrName,countCon); offSetEntryPattern += conPeek(financialDimensionFromExcel,countCon); countCon++; } _AssetTransType = str2enum(_AssetTransType, transType); _AssetTransTypeJournal = str2enum(_AssetTransTypeJournal, transType); _LedgerJournalTable = LedgerJournalTable::find(_journalId); if (_LedgerJournalTable) { _LedgerJournalTrans.clear(); _LedgerJournalTrans.JournalNum = _journalId; _LedgerJournalTrans.TransDate = _TransDate; _LedgerJournalTrans.AccountType = LedgerJournalACType::FixedAssets; _LedgerJournalTrans.LedgerDimension = DimensionStorage::getDynamicAccount(AccountNum, LedgerJournalACType::FixedAssets); _LedgerJournalTrans.DefaultDimension = defaultDimension; _LedgerJournalTrans.CurrencyCode = _CurrencyCode; _LedgerJournalTrans.ExchRate = _ExhcangeRate; _LedgerJournalTrans.PostingProfile = _PostingProfile; if(_AmountCurDebit != 0) { _LedgerJournalTrans.AmountCurDebit = _AmountCurDebit; } if(_AmountCurCredit != 0) { _LedgerJournalTrans.AmountCurCredit = _AmountCurCredit; } if(_OffsetAcountNum != "") { _LedgerJournalTrans.OffsetAccountType = LedgerJournalACType::Ledger; _LedgerJournalTrans.OffsetLedgerDimension = AxdDimensionUtil::getLedgerAccountId(offSetEntryPattern); } _LedgerJournalTrans.Voucher = NumberSeq::newGetNumFromId(_LedgerJournalTable.ledgerJournalName().NumberSequenceTable).num(); _LedgerJournalTrans.insert(); if (_LedgerJournalTrans) { if (_AssetBookId) { _AssetBookTable = AssetBookTable::find(_AssetBookId); if (_AssetBookTable) { _LedgerJournalTrans_Asset.clear(); _LedgerJournalTrans_Asset.RefRecId = _LedgerJournalTrans.RecId; _LedgerJournalTrans_Asset.AssetId = AccountNum; _LedgerJournalTrans_Asset.TransType = _AssetTransTypeJournal; _LedgerJournalTrans_Asset.BookId = _AssetBookTable.BookId; _LedgerJournalTrans_Asset.insert(); } select firstOnly _AssetBookTableDerived where _AssetBookTableDerived.BookId == _AssetBookId && _AssetBookTableDerived.AssetTransType == _AssetTransType; if (_AssetBookTableDerived) { _AssetBookTableDerivedJournal.clear(); _AssetBookTableDerivedJournal.RefRecId = _LedgerJournalTrans.RecId; _AssetBookTableDerivedJournal.AssetBookId = _AssetBookTableDerived.BookIdDerived; if (_LedgerJournalTrans.AmountCurCredit > 0) { _AssetBookTableDerivedJournal.AmountCur = _LedgerJournalTrans.AmountCurCredit; } else if (_LedgerJournalTrans.AmountCurDebit > 0) { _AssetBookTableDerivedJournal.AmountCur = _LedgerJournalTrans.AmountCurDebit; } _AssetBookTableDerivedJournal.insert(); } } } print strFmt('Row Number %1 - Asset ID : %2', row,AccountNum); row++; } } info(strFmt('Imported %1 Items, Journal Numbers : %2',row ,_journalId)); ttsCommit; } } catch(Exception::Error) { _return = false; } application.quit(); }
JOURNAL
Auto settle from custTransOpen when create journal payment lines via x++
example code :
public static void autoSettlementJournalFromPAYDetails(JournalId _journalNum) { CustVendOpenTransManager manager; CustTrans custTrans; CustTransOpen custTransOpen; LedgerJournalTrans ledgerJournalTrans; AxLedgerJournalTable axLedgerJournalTable; AxLedgerJournalTrans axLedgerJournalTrans; ExchangeRateHelper exchangeRateHelper; AmountCur totalSettleAmount; AmountCur amountCurDebit,amountCurCredit; HRSPayJournalDetails hrsPayJournalDetails; HRSPayJournalTable hrsPayJournalTable; int Row; AxLedgerJournalTable = AxLedgerJournalTable::newLedgerJournalTable(LedgerJournalTable::find(_journalNum)); ttsBegin; //details journal lines from Pay Journal Details while select hrsPayJournalTable join hrsPayJournalDetails where hrsPayJournalTable.HRSPayJournalId == hrsPayJournalDetails.HRSPayJournalId && HRSPayJournalTable.JournalNum == _journalNum { CustTrans = CustTrans::findFromInvoice(hrsPayJournalDetails.ReferenceNumber,HRSPayJournalDetails.AccountNum); CustTransOpen = CustTransOpen::findRefId(CustTrans.RecId); if(CustTransOpen) { //Create Trans ... axLedgerJournalTrans = new axLedgerJournalTrans(); axLedgerJournalTrans.parmJournalNum(AxLedgerJournalTable.ledgerJournalTable().JournalNum); axLedgerJournalTrans.parmTransDate(today()); axLedgerJournalTrans.parmDue(today()); axLedgerJournalTrans.parmAccountType(LedgerJournalACType::Cust); axLedgerJournalTrans.parmLedgerDimension(DimensionStorage::getDynamicAccount(hrsPayJournalDetails.AccountNum, LedgerJournalACType::Cust)); axLedgerJournalTrans.parmAmountCurCredit(hrsPayJournalDetails.Credit); axLedgerJournalTrans.parmAmountCurDebit(hrsPayJournalDetails.Debit); axLedgerJournalTrans.save(); manager = CustVendOpenTransManager::construct(axLedgerJournalTrans.ledgerJournalTrans()); manager.updateTransMarked(custTransOpen,true); if(hrsPayJournalDetails.Debit) { manager.updateSettleAmount(custTransOpen,hrsPayJournalDetails.Debit); } else { manager.updateSettleAmount(custTransOpen,hrsPayJournalDetails.Credit); } //update after settle ledgerJournalTrans = axLedgerJournalTrans.ledgerJournalTrans(); ledgerJournalTrans.selectForUpdate(true); ledgerJournalTrans.SettleVoucher = SettlementType::SelectedTransact; ledgerJournalTrans.update(); Row++; } } ttsCommit; info(strFmt("Total Lines settled : %1",Row)); }