Hi Zeeshan,
As I said earlier, you have to add PostedDateTime field to LedgerTransStatementTmp and LedgerTransStatementStagingTmp.
Then modify populateTempTableLedgerInStaging method in LedgerTransStatementDP class as below:
private void populateTempTableLedgerInStaging(
LedgerTransStatementStagingTmp _ledgerTransStatementTmp,
Query _query,
FromDate _startDate,
ToDate _endDate,
boolean _includeOpening,
boolean _includeClosing,
Name _dimensionFocusName,
boolean _includeIntercompanyTaxVoucher)
{
boolean hasRangesOrFilters = SysQuery::queryHasRangesOrFilters(_query);
RecordInsertList recordInsertList;
TransDate periodStartDate;
QueryBuildDataSource qbdsGJAE, qbdsGJE, qbdsFCP, qbds, qbdsLedger, qbdsCompanyInfo;
QueryRun queryRun;
GeneralJournalAccountEntry generalJournalAccountEntry;
GeneralJournalEntry generalJournalEntry;
Ledger ledger;
CompanyInfo companyInfo;
FiscalCalendarPeriod fiscalCalendarPeriod;
FiscalPeriodType opening = FiscalPeriodType::Opening;
SubledgerVoucherGeneralJournalEntry subledgerVoucherGeneralJournalEntry, subledgerVoucherGeneralJournalEntry2;
AmountMST zeroAmount = 0.0;
Voucher emptyVoucher = '';
int i;
DimensionFocusBalance dimensionFocusBalance;
TaxTransGeneralJournalAccountEntry taxTransGeneralJournalAccountEntryForTax, taxTransGeneralJournalAccountEntryForVoucher;
TaxTrans taxTrans;
LedgerTransStatementStagingTmp localLedgerTransStatmentTmp;
this.setUserConnection(localLedgerTransStatmentTmp);
localLedgerTransStatmentTmp.linkPhysicalTableInstance(_ledgerTransStatementTmp);
periodStartDate = FiscalCalendars::findOpeningStartDateByDate(CompanyInfo::fiscalCalendarRecId(), _startDate);
if (hasRangesOrFilters)
{
// The query has multiple top level data sources all of which get run when the quer is run. We are interested in only
// Ledger records so disable the other top level data sources.
for (i = 1; i <= _query.childDataSourceCount(); i++)
{
qbds = _query.childDataSourceNo(i);
if (qbds.table() != tableNum(GeneralJournalAccountEntry))
{
qbds.enabled(false);
}
}
// Ranges or filters exist, so the GeneralJournalAccountEntry records to process must
// be looped over individually. Group by RecId and include other
// fields that are in the select list to populate the trans list.
qbdsGJAE = _query.dataSourceTable(tableNum(GeneralJournalAccountEntry));
// Filter to the current date range
qbdsGJE = qbdsGJAE.addDataSource(tableNum(GeneralJournalEntry));
qbdsGJE.fetchMode(QueryFetchMode::One2One);
qbdsGJE.joinMode(JoinMode::InnerJoin);
qbdsGJE.addLink(fieldNum(GeneralJournalAccountEntry, GeneralJournalEntry), fieldNum(GeneralJournalEntry, RecId));
qbdsGJE.addRange(fieldNum(GeneralJournalEntry, AccountingDate)).value(SysQuery::range(periodStartDate, _endDate, true));
qbdsGJE.addRange(fieldNum(GeneralJournalEntry, Ledger)).value(queryValue(Ledger::current()));
// Get the fiscal period type
qbdsFCP = qbdsGJE.addDataSource(tableNum(FiscalCalendarPeriod));
qbdsFCP.fetchMode(QueryFetchMode::One2One);
qbdsFCP.joinMode(JoinMode::InnerJoin);
qbdsFCP.addLink(fieldNum(GeneralJournalEntry, FiscalCalendarPeriod), fieldnum(FiscalCalendarPeriod, RecId));
// Get the company
qbdsLedger = qbdsGJE.addDataSource(tableNum(Ledger));
qbdsLedger.fetchMode(QueryFetchMode::One2One);
qbdsLedger.joinMode(JoinMode::InnerJoin);
qbdsLedger.addSelectionField(fieldNum(Ledger, PrimaryForLegalEntity));
qbdsLedger.addLink(fieldNum(GeneralJournalEntry, Ledger), fieldnum(Ledger, RecId));
qbdsCompanyInfo = qbdsLedger.addDataSource(tableNum(CompanyInfo));
qbdsCompanyInfo.fetchMode(QueryFetchMode::One2One);
qbdsCompanyInfo.joinMode(JoinMode::InnerJoin);
qbdsCompanyInfo.addSelectionField(fieldNum(CompanyInfo, DataArea));
qbdsCompanyInfo.addLink(fieldNum(Ledger, PrimaryForLegalEntity), fieldnum(CompanyInfo, RecId));
// Filter out closing transactions if necessary
if (!_includeClosing)
{
qbdsFCP.addRange(fieldNum(FiscalCalendarPeriod, Type)).value(SysQuery::valueNot(FiscalPeriodType::Closing));
}
// Group by GeneralJournalAccountEntry.RecId to prevent the cardinality of the result set from increasing
// due to other data sources added by the user in the SysQuery form
qbdsGJAE.addGroupByField(fieldNum(GeneralJournalAccountEntry, RecId));
qbdsGJAE.addGroupByField(fieldNum(GeneralJournalAccountEntry, GeneralJournalEntry));
qbdsGJAE.addGroupByField(fieldNum(GeneralJournalAccountEntry, LedgerDimension));
qbdsGJAE.addGroupByField(fieldNum(GeneralJournalAccountEntry, Text));
qbdsGJAE.addGroupByField(fieldNum(GeneralJournalAccountEntry, TransactionCurrencyCode));
qbdsGJAE.addGroupByField(fieldNum(GeneralJournalAccountEntry, PostingType));
qbdsGJE.addGroupByField(fieldNum(GeneralJournalEntry, AccountingDate));
qbdsGJE.addGroupByField(fieldNum(GeneralJournalEntry, PostingLayer));
qbdsFCP.addGroupByField(fieldNum(FiscalCalendarPeriod, Type));
// SEFA.DUM:
//-->
qbdsGJE.addGroupByField(fieldNum(GeneralJournalEntry, DocumentDate));
//<--
// Fill the staging table with the set of GJAE records to process
new SkipAOSValidationPermission().assert();
recordInsertList = new RecordInsertList(tableNum(LedgerTransStatementStagingTmp), true, true, true, true, true, _ledgerTransStatementTmp);
queryRun = new queryRun(_query);
while (queryRun.next())
{
_ledgerTransStatementTmp.clear();
generalJournalAccountEntry = queryRun.get(tableNum(GeneralJournalAccountEntry)) as GeneralJournalAccountEntry;
_ledgerTransStatementTmp.GeneralJournalAccountEntry = generalJournalAccountEntry.RecId;
_ledgerTransStatementTmp.GeneralJournalEntry = generalJournalAccountEntry.GeneralJournalEntry;
_ledgerTransStatementTmp.LedgerDimension = generalJournalAccountEntry.LedgerDimension;
_ledgerTransStatementTmp.TransTxt = generalJournalAccountEntry.Text;
_ledgerTransStatementTmp.CurrencyCode = generalJournalAccountEntry.TransactionCurrencyCode;
_ledgerTransStatementTmp.LedgerPostingType = generalJournalAccountEntry.PostingType;
generalJournalEntry = queryRun.get(tableNum(GeneralJournalEntry)) as GeneralJournalEntry;
_ledgerTransStatementTmp.TransDate = generalJournalEntry.AccountingDate;
_ledgerTransStatementTmp.PostingLayer = generalJournalEntry.PostingLayer;
// SEFA.DUM:
//-->
_ledgerTransStatementTmp.LcwDocumentDate = generalJournalEntry.DocumentDate;
//<--
fiscalCalendarPeriod = queryRun.get(tableNum(FiscalCalendarPeriod)) as FiscalCalendarPeriod;
_ledgerTransStatementTmp.PeriodCode = fiscalCalendarPeriod.Type;
if (_includeOpening && _ledgerTransStatementTmp.PeriodCode == FiscalPeriodType::Opening && _startDate == periodStartDate)
{
// Special case if this is an opening transaction and the date range includes the period start date and
// includeOpeningTransactions is selected, then the opening transactions should be treated like normal
// operating transactions to include them in the list of transactions
_ledgerTransStatementTmp.PeriodCode = FiscalPeriodType::Operating;
}
else if (generalJournalEntry.AccountingDate < _startDate)
{
// Other special case where start date of the report is after the start of the period, so operating
// transactions should be treated as opening to include them in the opening balance
_ledgerTransStatementTmp.PeriodCode = FiscalPeriodType::Opening;
}
companyInfo = queryRun.get(tableNum(CompanyInfo)) as CompanyInfo;
_ledgerTransStatementTmp.GeneralJournalEntryDataArea = companyInfo.DataArea;
recordInsertList.add(_ledgerTransStatementTmp);
}
recordInsertList.insertDatabase();
CodeAccessPermission::revertAssert();
// Re-enable the data sources
for (i = 1; i <= _query.childDataSourceCount(); i++)
{
qbds = _query.childDataSourceNo(i);
if (qbds.table() != tableNum(GeneralJournalAccountEntry))
{
qbds.enabled(true);
}
}
}
else
{
// No filters or ranges were added, so instead populate the table with a simple set-based insert
// optionally including closing transactions if required
if (_includeClosing)
{
insert_recordset _ledgerTransStatementTmp
(GeneralJournalAccountEntry,
GeneralJournalEntry,
LedgerDimension,
LedgerPostingType,
TransTxt,
CurrencyCode,
TransDate,
PostingLayer,
//Sefa
LcwDocumentDate,
//--<
GeneralJournalEntryDataArea,
PeriodCode)
select
RecId,
GeneralJournalEntry,
LedgerDimension,
PostingType,
Text,
TransactionCurrencyCode
from generalJournalAccountEntry
join
AccountingDate,
PostingLayer,
//Lcw SEFA:DUM:
DocumentDate,
//--<
from generalJournalEntry
where generalJournalEntry.RecId == generalJournalAccountEntry.GeneralJournalEntry
&& generalJournalEntry.AccountingDate >= _startDate
&& generalJournalEntry.AccountingDate <= _endDate
&& generalJournalEntry.Ledger == Ledger::current()
join ledger
where ledger.RecId == generalJournalEntry.Ledger
join DataArea from companyInfo
where companyInfo.RecId == ledger.PrimaryForLegalEntity
join Type from fiscalCalendarPeriod
where fiscalCalendarPeriod.RecId == generalJournalEntry.FiscalCalendarPeriod;
}
else
{
insert_recordset _ledgerTransStatementTmp
(GeneralJournalAccountEntry,
GeneralJournalEntry,
LedgerDimension,
LedgerPostingType,
TransTxt,
CurrencyCode,
TransDate,
PostingLayer,
//LCW Sefa
LcwDocumentDate,
//--<
GeneralJournalEntryDataArea,
PeriodCode)
select
RecId,
GeneralJournalEntry,
LedgerDimension,
PostingType,
Text,
TransactionCurrencyCode
from generalJournalAccountEntry
join AccountingDate,
PostingLayer,
//Lcw SEFA:DUM:
DocumentDate,
//--<
from generalJournalEntry
where generalJournalEntry.RecId == generalJournalAccountEntry.GeneralJournalEntry
&& generalJournalEntry.AccountingDate >= _startDate
&& generalJournalEntry.AccountingDate <= _endDate
&& generalJournalEntry.Ledger == Ledger::current()
join ledger
where ledger.RecId == generalJournalEntry.Ledger
join DataArea from companyInfo
where companyInfo.RecId == ledger.PrimaryForLegalEntity
join Type from fiscalCalendarPeriod
where fiscalCalendarPeriod.RecId == generalJournalEntry.FiscalCalendarPeriod
&& fiscalCalendarPeriod.Type != FiscalPeriodType::Closing;
}
// Update for the special case of opening transactions being included in the date range and includeOpening being specified
// so those transactions should be treated as regular operating transactions
if (_includeOpening && periodStartDate == _startDate)
{
update_recordSet _ledgerTransStatementTmp setting
PeriodCode = FiscalPeriodType::Operating
where _ledgerTransStatementTmp.PeriodCode == FiscalPeriodType::Opening;
}
// Add any additional operating transactions between the period opening and the start of the report and treat them as
// opening transactions so that opening + operating == current balance
if (_startDate > periodStartDate)
{
insert_recordset _ledgerTransStatementTmp
(LedgerDimension, TransDate, PostingLayer, PeriodCode,
AmountCredit, TransactionCurrencyAmountCredit, PreviousCreditTotal,
AmountDebit, TransactionCurrencyAmountDebit, PreviousDebitTotal, GeneralJournalEntryDataArea)
select FocusLedgerDimension, minOf(AccountingDate), PostingLayer, opening,
sum(CreditAccountingCurrencyAmount), sum(CreditAccountingCurrencyAmount), sum(CreditAccountingCurrencyAmount),
sum(DebitAccountingCurrencyAmount), sum(DebitAccountingCurrencyAmount), sum(DebitAccountingCurrencyAmount) from dimensionFocusBalance
group by FocusLedgerDimension, PostingLayer
where dimensionFocusBalance.AccountingDate >= periodStartDate &&
dimensionFocusBalance.AccountingDate < _startDate &&
dimensionFocusBalance.Ledger == Ledger::current() &&
(dimensionFocusBalance.FiscalCalendarPeriodType == FiscalPeriodType::Opening ||
dimensionFocusBalance.FiscalCalendarPeriodType == FiscalPeriodType::Operating) &&
dimensionFocusBalance.FocusDimensionHierarchy ==
DimensionHierarchy::findByTypeAndName(DimensionHierarchyType::Focus, _dimensionFocusName).RecId
join ledger
where ledger.RecId == dimensionFocusBalance.Ledger
join DataArea from companyInfo
group by DataArea
where companyInfo.RecId == ledger.PrimaryForLegalEntity;
update_recordSet _ledgerTransStatementTmp
setting AmountCredit = _ledgerTransStatementTmp.AmountCredit * -1,
TransactionCurrencyAmountCredit = _ledgerTransStatementTmp.TransactionCurrencyAmountCredit * -1,
PreviousCreditTotal = _ledgerTransStatementTmp.PreviousCreditTotal * -1
where _ledgerTransStatementTmp.GeneralJournalAccountEntry == 0;
}
}
// Include revenue/expense posting entry(s) associated with intercompany tax postings
if (_includeIntercompanyTaxVoucher)
{
insert_recordset localLedgerTransStatmentTmp
(GeneralJournalAccountEntry,
GeneralJournalEntry,
LedgerDimension,
LedgerPostingType,
TransTxt,
CurrencyCode,
TransDate,
PostingLayer,
//LCW Sefa
LcwDocumentDate,
//--<
PeriodCode,
GeneralJournalEntryDataArea)
select _ledgerTransStatementTmp
where _ledgerTransStatementTmp.LedgerPostingType == LedgerPostingType::Tax
join taxTransGeneralJournalAccountEntryForTax
where taxTransGeneralJournalAccountEntryForTax.GeneralJournalAccountEntry == _ledgerTransStatementTmp.GeneralJournalAccountEntry &&
taxTransGeneralJournalAccountEntryForTax.TaxTransRelationship == TaxTransRelationshipType::Tax
join taxTrans
where taxTrans.RecId == taxTransGeneralJournalAccountEntryForTax.TaxTrans &&
taxTrans.TaxObligationCompany == TaxObligationCompany::Source
join taxTransGeneralJournalAccountEntryForVoucher
where taxTransGeneralJournalAccountEntryForVoucher.TaxTrans == taxTrans.RecId &&
taxTransGeneralJournalAccountEntryForVoucher.TaxTransRelationship == TaxTransRelationshipType::TransactionLineAccount
join RecId, GeneralJournalEntry, LedgerDimension, PostingType, Text, TransactionCurrencyCode
from generalJournalAccountEntry
where generalJournalAccountEntry.RecId == taxTransGeneralJournalAccountEntryForVoucher.GeneralJournalAccountEntry
join AccountingDate, PostingLayer,
//Lcw SEFA:DUM:
DocumentDate,
//--<
from generalJournalEntry
where generalJournalEntry.RecId == generalJournalAccountEntry.GeneralJournalEntry
join Type
from fiscalCalendarPeriod
where fiscalCalendarPeriod.RecId == generalJournalEntry.FiscalCalendarPeriod &&
fiscalCalendarPeriod.Type != FiscalPeriodType::Closing
join ledger
where ledger.RecId == generalJournalEntry.Ledger
join DataArea
from companyInfo
where companyInfo.RecId == ledger.PrimaryForLegalEntity &&
companyInfo.DataArea != taxTrans.dataAreaId; // ignore entries from the same company as they will already be included
// Identify revenue/expense posting entry(s) associated with intercompany tax postings opening balance transactions
if (_startDate > periodStartDate)
{
update_recordSet localLedgerTransStatmentTmp setting
PeriodCode = opening
where localLedgerTransStatmentTmp.GeneralJournalEntryDataArea != taxTrans.dataAreaId &&
localLedgerTransStatmentTmp.TransDate < _startDate;
}
}
// Fill in the credit amount
update_recordSet _ledgerTransStatementTmp setting
AmountCredit = generalJournalAccountEntry.AccountingCurrencyAmount * -1,
PreviousCreditTotal = generalJournalAccountEntry.AccountingCurrencyAmount * -1,
TransactionCurrencyAmountCredit = generalJournalAccountEntry.TransactionCurrencyAmount * -1
join generalJournalAccountEntry where
generalJournalAccountEntry.RecId == _ledgerTransStatementTmp.GeneralJournalAccountEntry &&
generalJournalAccountEntry.IsCredit == true;
// Fill in the debit amount
update_recordSet _ledgerTransStatementTmp setting
AmountDebit = generalJournalAccountEntry.AccountingCurrencyAmount,
PreviousDebitTotal = generalJournalAccountEntry.AccountingCurrencyAmount,
TransactionCurrencyAmountDebit = generalJournalAccountEntry.TransactionCurrencyAmount
join generalJournalAccountEntry where
generalJournalAccountEntry.RecId == _ledgerTransStatementTmp.GeneralJournalAccountEntry &&
generalJournalAccountEntry.IsCredit == false;
// Fill in the summation
update_recordSet _ledgerTransStatementTmp setting
AccumulatedMST = _ledgerTransStatementTmp.AmountDebit - _ledgerTransStatementTmp.AmountCredit,
AmountMSTDebCred = _ledgerTransStatementTmp.AmountDebit - _ledgerTransStatementTmp.AmountCredit;
// Fill in voucher information
update_recordSet _ledgerTransStatementTmp setting
Voucher = subledgerVoucherGeneralJournalEntry.Voucher
join subledgerVoucherGeneralJournalEntry
where subledgerVoucherGeneralJournalEntry.GeneralJournalEntry == _ledgerTransStatementTmp.GeneralJournalEntry
notExists join subledgerVoucherGeneralJournalEntry2
// Only fill in voucher if summarization didn't happen
where subledgerVoucherGeneralJournalEntry2.GeneralJournalEntry == _ledgerTransStatementTmp.GeneralJournalEntry
&& subledgerVoucherGeneralJournalEntry2.RecId != subledgerVoucherGeneralJournalEntry.RecId;
// Fill in the voucher information for summarized records
update_recordSet _ledgerTransStatementTmp setting
Voucher = "@SYS342661" // This label is the text "Summarized"
where _ledgerTransStatementTmp.Voucher == '';
}
After that modify copyToReportTable method in LedgerTransStatementDP as below:
private void copyToReportTable(LedgerTransStatementStagingTmp _ledgerTransStmtStagingTmp)
{
NoYes isFinalTrue = NoYes::Yes;
insert_recordset ledgerTransStatementTmp
(
MainFocusValue,
MainFocusName,
MainFocusDescription,
PeriodCode,
TransDate,
Voucher,
AmountDebit,
AmountCredit,
AccumulatedMST,
AmountCur,
TransactionCurrencyAmountDebit,
TransactionCurrencyAmountCredit,
AmountMSTDebCred,
CurrencyCode,
GeneralJournalAccountEntry,
GeneralJournalEntry,
GeneralJournalEntryDataArea,
PreviousCreditTotal,
PreviousDebitTotal,
Reversed,
SecondaryFocus,
SecondaryFocusName,
TaxAmount,
TaxCode,
TraceNum,
TransTxt,
OffsetAccount,
IsFinalRecord,
BudgetModelId,
LedgerPostingType,
RecordType,
BudgetTransactionStatus,
IsEndingBalance,
GroupRowNum,
//Sefa
LcwDocumentDate,
//--<
)
select
MainFocusValue,
MainFocusName,
MainFocusDescription,
PeriodCode,
TransDate,
Voucher,
AmountDebit,
AmountCredit,
AccumulatedMST,
AmountCur,
TransactionCurrencyAmountDebit,
TransactionCurrencyAmountCredit,
AmountMSTDebCred,
CurrencyCode,
GeneralJournalAccountEntry,
GeneralJournalEntry,
GeneralJournalEntryDataArea,
PreviousCreditTotal,
PreviousDebitTotal,
Reversed,
SecondaryFocus,
SecondaryFocusName,
TaxAmount,
TaxCode,
TraceNum,
TransTxt,
OffsetAccount,
isFinalTrue,
BudgetModelId,
LedgerPostingType,
RecordType,
BudgetTransactionStatus,
IsEndingBalance,
GroupRowNum,
//Sefa
LcwDocumentDate,
//--<
from _ledgerTransStmtStagingTmp
// Re-order the records since the report prints in recid order and the opening transactions were calculated
// after some initial operating records were created
order by MainFocusValue, IsEndingBalance, SecondaryFocus, GeneralJournalEntryDataArea, PeriodCode, RecordType, TransDate, Voucher, TaxCode;
}
