P_CshPosFlow

@VDM.private: true
@VDM.viewType: #COMPOSITE
@AccessControl.authorizationCheck: #NOT_REQUIRED
@AbapCatalog.sqlViewName: 'PCSHPOSFLW'
@ClientHandling.algorithm: #SESSION_VARIABLE
@Metadata.ignorePropogatedAnnotations: true
define view P_CshPosFlow
with parameters P_StartDate : vdm_v_start_date,
                @Environment.systemField: #SYSTEM_DATE
                P_KeyDate : sydate
   as select from I_CashFlow as flow
--inner join I_BankAccount as bam
--on flow.BankAccountInternalID = bam.BankAccountInternalID
--and bam.ValidityStartDate <= $parameters.P_StartDate and bam.ValidityEndDate >= $parameters.P_StartDate
--and bam.ValidityStartDate <= flow.TransactionDate and bam.ValidityEndDate >= flow.TransactionDate
{
  key flow.CompanyCode,
  key flow.TransactionDate,
  key flow.BankAccountInternalID,
  key flow.TransactionCurrency,
  key flow.CertaintyLevel,
    flow.HouseBank,
    flow.HouseBankAccount,
  case when flow._BankAccount.BankAccountNumber is null then cast('' as fclm_bam_acc_num) else flow._BankAccount.BankAccountNumber end as BankAccount,
  case when flow._BankAccount.Bank is null then cast('' as fclm_bam_bank_key) else flow._BankAccount.Bank end as Bank,
  case when flow._BankAccount.BankCountry is null then cast('' as fclm_bam_bank_country_key) else flow._BankAccount.BankCountry end as BankCountry,
  flow.BankAccountCurrency,
  sum(flow.AmountInTransactionCurrency) as AmountInTransactionCurrency,
  flow.CompanyCodeCurrency,
  sum(flow.AmountInCompanyCodeCurrency) as AmountInCompanyCodeCurrency,
  /* Associations */
  flow._BankAccount,
  flow._CompanyCode
} where flow.ExpirationDate >= $parameters.P_KeyDate 
   and (flow.TransactionDate = :P_StartDate or (flow.TransactionDate < :P_StartDate and flow.CertaintyLevel <> 'ACTUAL'))
   and  (flow.ValidFrom <= TSTMP_CURRENT_UTCTIMESTAMP() and flow.ValidTo >= TSTMP_CURRENT_UTCTIMESTAMP() )
-- performance tuning 20151109 WM
group by  flow.CompanyCode,
          flow.TransactionDate,
          flow.BankAccountInternalID,
          flow.HouseBank,
          flow.HouseBankAccount,
          flow.CertaintyLevel,
          flow._BankAccount.BankAccountNumber,
          flow._BankAccount.Bank,
          flow._BankAccount.BankCountry,
          flow.TransactionCurrency,
          flow.CompanyCodeCurrency,
          flow.BankAccountCurrency

                                    
                                 
  
  
  
  
  
  
  
  
  
  
 /*+[internal] {
"BASEINFO":
{
"FROM":
[
"I_BANKACCOUNT",
"I_CASHFLOW"
],
"ASSOCIATED":
[
"I_BANKACCOUNT",
"I_COMPANYCODE"
],
"BASE":
[
"I_CASHFLOW"
],
"ANNO_REF":
[],
"SCALAR_FUNCTION":
[],
"VERSION":0,
"ANNOREF_EVALUATION_ERROR":""
}
}*/