P_UnrealizedCrcyDiff

@AbapCatalog.sqlViewName: 'PUNREALZCRCYDIFF'
@VDM.private: true
@VDM.viewType: #COMPOSITE
@AccessControl.authorizationCheck: #NOT_REQUIRED //or #NOT_ALLOWED

@AbapCatalog.preserveKey:true
@ClientHandling.algorithm: #SESSION_VARIABLE
@Metadata.ignorePropagatedAnnotations: true
@AbapCatalog.compiler.compareFilter:true
define view P_UnrealizedCrcyDiff
  as select from           I_JournalEntryItem       as _je
    inner join             I_CompanyCode            as _compcode    on _je.CompanyCode = _compcode.CompanyCode
    inner join             I_GLAccountInCompanyCode as _gl          on  _je.CompanyCode = _gl.CompanyCode
                                                                    and _je.GLAccount   = _gl.GLAccount
    left outer to one join I_Bankaccountlinkage     as _bankAccount on  _je.CompanyCode      =  _bankAccount.CompanyCode
                                                                    and _je.HouseBank        =  _bankAccount.HouseBank
                                                                    and _je.HouseBankAccount =  _bankAccount.HouseBankAccount
                                                                    and _je.PostingDate      >= _bankAccount.ValidityStartDate
                                                                    and _je.PostingDate      <= _bankAccount.ValidityEndDate
                                                                    and _je.HouseBankAccount <> ''
    left outer to one join I_BankAccountCurrency    as _bamCrcy     on _bankAccount.BankAccountInternalID = _bamCrcy.BankAccountInternalID
    left outer to one join I_GLAcctLiquidityItemMapping as _lq_comp on  _compcode.ChartOfAccounts   = _lq_comp.ChartOfAccounts
                                                                    and _je.CompanyCode             = _lq_comp.CompanyCode
                                                                    and _je.OffsettingAccount       = _lq_comp.GLAccount
    left outer to one join I_GLAcctLiquidityItemMapping as _lq_coa  on  _compcode.ChartOfAccounts   = _lq_coa.ChartOfAccounts
                                                                    and _lq_coa.CompanyCode         = ''
                                                                    and _je.OffsettingAccount       = _lq_coa.GLAccount

{
  key _je.SourceLedger,
  key _je.CompanyCode,
  key _je.FiscalYear,
  key _je.AccountingDocument,
  key _je.LedgerGLLineItem,
  key _je.Ledger,

      _je.LogicalSystem,
      _je.TransactionCurrency,
      _je.AmountInTransactionCurrency,
      _je.CompanyCodeCurrency,
      _je.AmountInCompanyCodeCurrency,

      _je.AccountingDocCreatedByUser,
      _je.AccountingDocumentItem,
      _je.AccountingDocumentType,
      _je.FiscalPeriod,
      _je.GLAccount,
      _je.FinancialAccountType,
      _je.DocumentItemText,
      _je.DocumentDate                                                      as TransactionDate,
      _je.PostingDate,
      _gl.CashPlanningGroup,
      _gl.PlanningLevel,

      _je.HouseBank,
      _je.HouseBankAccount,
      _bankAccount.BankAccountInternalID,
      coalesce(_bamCrcy.BankAccountCurrency, cast('' as waers))             as BankAccountCurrency,

      _je.Customer,
      _je.Supplier,
      _je.PartnerCompany,
      _je.Material,
      _je.BusinessArea,
      _je.ProfitCenter,
      _je.CostCenter,
      _je.Segment,
      _je.WBSElementInternalID,
      _je.WBSElement                                                        as WBSElementExternalID,
      coalesce(_lq_comp.LiquidityItem, coalesce(_lq_coa.LiquidityItem, cast('' as flqpos))) as LiquidityItem,
      _je.Fund,
      _je.GrantID

}

where
      _je.AmountInTransactionCurrency  =  0
  and _je.AmountInCompanyCodeCurrency  <> 0
  and _je.TransactionTypeDetermination <> 'KDF'
  and _je.AccountingDocumentCategory = 'U'
/*+[internal] {
"BASEINFO":
{
"FROM":
[
"I_BANKACCOUNTCURRENCY",
"I_BANKACCOUNTLINKAGE",
"I_COMPANYCODE",
"I_GLACCOUNTINCOMPANYCODE",
"I_GLACCTLIQUIDITYITEMMAPPING",
"I_JOURNALENTRYITEM"
],
"ASSOCIATED":
[],
"BASE":
[],
"ANNO_REF":
[],
"SCALAR_FUNCTION":
[],
"VERSION":0,
"ANNOREF_EVALUATION_ERROR":""
}
}*/