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