I_BankAccountWithRevision

@AbapCatalog.sqlViewName: 'IBANKACCTWR'
@AccessControl.authorizationCheck: #CHECK
@VDM.viewType: #BASIC
@EndUserText.label: 'Bank Account With Revision'
@ObjectModel.usageType.dataClass: #MASTER
@ObjectModel.usageType.sizeCategory: #M
@ObjectModel.usageType.serviceQuality: #C
@ClientHandling.algorithm: #SESSION_VARIABLE
@AbapCatalog.compiler.compareFilter: true
@AccessControl.personalData.blocking:#REQUIRED
define view I_BankAccountWithRevision
  as select from    I_ValidBankAccountWithRevision as validAmd
    left outer join fclm_bam_amd  as amd on validAmd.BankAccountInternalID = amd.acc_id
    left outer join fclm_bam_rev as _Rev on  amd.acc_id   = _Rev.acc_id
                                         and amd.revision = _Rev.revision
  association [0..*] to I_BankAccountTextRev     as _AccountTextInCurrentLang on  $projection.BankAccountInternalID = _AccountTextInCurrentLang.BankAccountInternalID
                                                                              and $projection.BankAccountRevision   = _AccountTextInCurrentLang.BankAccountRevision
  association [0..1] to I_Bank_2                 as _Bank                     on  $projection.BankCountry = _Bank.BankCountry
                                                                              and $projection.Bank        = _Bank.BankInternalID
  association [0..1] to I_CompanyCode            as _CompanyCode              on  $projection.CompanyCode = _CompanyCode.CompanyCode
  association [0..1] to I_BusinessPartner        as _BankContact              on  $projection.BankContactPerson = _BankContact.BusinessPartner
  association [0..*] to I_BusinessPartnerAddress as _BankContactAddress       on  $projection.BankContactPerson = _BankContactAddress.BusinessPartner

{
    
  key amd.acc_id                                      as BankAccountInternalID,
  key amd.revision                                    as BankAccountRevision,
      amd.banks                                       as BankCountry,
      amd.bankl                                       as Bank,
      amd.acc_num                                     as BankAccountNumber,
      amd.acc_type_id                                 as BankAccountType,
      amd.valid_from                                  as ValidityStartDate,
      amd.valid_to                                    as ValidityEndDate,
      amd.iban                                        as IBAN,
      amd.status                                      as BankAccountStatus,
      amd.cash_pool_id                                as LeadingCashPoolID,
      amd.bukrs                                       as CompanyCode,
      amd.prctr                                       as ProfitCenter,
      amd.gsber                                       as BusinessArea,
      amd.segment                                     as Segment,
      amd.beneficial                                  as BankAccountHolderName,
      amd.bank_contact                                as BankContactPerson,
      amd.rel_manager                                 as BankRelationshipManager,
      amd.company_contact                             as CompanyContactPerson,
      amd.account_supervisor                          as BankAccountSupervisor,
      amd.edbsum                                      as BankStatementImportMethod,
      amd.idbsum                                      as IntraDayBankStateImportMethod,
      amd.bs_upload_time                              as BankStatementCheckTime,
      amd.created_by                                  as CreatedByUser,
      amd.opened_by                                   as OpenedByUser,
      amd.changed_by                                  as LastChangedByUser,
      amd.reviewed_by                                 as ReviewedByUser,
      amd.closed_by                                   as ClosedByUser,
      amd.importing_channel                           as BankStatementImportChannel,
      amd.cutoff_time_d                               as DomesticCutOffTime,
      amd.cutoff_time_c                               as CrossBorderCutOffTime,
      amd.planned_closing_date                        as PlannedClosingDate,
      amd.upload_bs_flag                              as IsUploadOfIntraDayStatement,
      amd.created_at_dat                              as CreationDate,
      amd.created_at_tim                              as CreationTime,
      amd.opened_at_dat                               as OpenedDate,
      amd.changed_at_dat                              as LastChangeDate,
      amd.changed_at_tim                              as LastChangeTime,
      amd.closed_at_dat                               as ClosedDate,
      amd.closed_at_tim                               as ClosedTime,
      amd.control_key                                 as BankControlKey,
      amd.iban_valid_from                             as IBANValidityStartDate,
      amd.objnr                                       as ArchObjectNumber,
      amd.vgtyp                                       as PaymentTransactionTypeGroup,
      amd.procstat_act                                as ProcessingStatus,
      amd.balstat_act                                 as DifferenceStatus,
      amd.serstat_act                                 as SerialNumberStatus,
      amd.reconstat_act                               as ReconciliationStatus,
      amd.exp_number                                  as ExpectedInterval,
      amd.exp_rhyth                                   as ControlFrequency,
      amd.calendar                                    as FactoryCalendar,
      amd.sort_idx                                    as DisplayItem,
      amd.amt_diff                                    as DifferenceAmount,
      amd.waers                                       as BankAccountCurrency,
      amd.cash_pool_id_participant                    as ParticipantCashPoolID,
      amd.cash_pool_target_amt_max                    as MaxTargetAmount,
      amd.cash_pool_target_amt_min                    as MinTargetAmount,
      amd.cash_pool_transfer_amt_min                  as MinTransferAmount,
      amd.payment_method_l                            as LeadingPaymentMethod,
      amd.payment_method_p                            as ParticipantPaymentMethod,
      amd.payment_service_provider                    as PaymentServiceProvider,
      cast( case when _Rev.rev_status is null then '  '
         else _Rev.rev_status
      end    as fclm_bam_rev_status preserving type ) as BankAccountRevisionStatus,
      _Rev.activated_by                               as BankAccountRevActivatedByUser,   
      _Bank,    
      _CompanyCode,    
      _BankContact,
      _BankContactAddress,  
      _AccountTextInCurrentLang


}
where
  amd.status = '01' or amd.status = '02' or amd.status = '09' or amd.status = '10' or amd.status = '11' or amd.status = '15'
  or amd.status = '22' or amd.status = '24' or amd.status = '26' or amd.status = '28'
/*+[internal] {
"BASEINFO":
{
"FROM":
[
"I_VALIDBANKACCOUNTWITHREVISION",
"FCLM_BAM_AMD",
"FCLM_BAM_REV"
],
"ASSOCIATED":
[
"I_BANKACCOUNTTEXTREV",
"I_BANK_2",
"I_BUSINESSPARTNER",
"I_BUSINESSPARTNERADDRESS",
"I_COMPANYCODE"
],
"BASE":
[],
"ANNO_REF":
[],
"SCALAR_FUNCTION":
[],
"VERSION":0,
"ANNOREF_EVALUATION_ERROR":""
}
}*/