VFCLM_BAM_DDL_BPHIERCORE

@AbapCatalog.sqlViewName: 'VFCLMBAMBHCORE'
@EndUserText.label: 'BAM BP based Hierarchy Core'
@AccessControl.authorizationCheck: #NOT_REQUIRED
@ObjectModel.usageType.serviceQuality: #D
@ObjectModel.usageType.sizeCategory: #S
@ObjectModel.usageType.dataClass: #MASTER
@ClientHandling.algorithm: #SESSION_VARIABLE
define view VFCLM_BAM_DDL_BPHIERCORE 
  with parameters plang : abap.lang
as select from VFCLM_BAM_DDL_HIERNODECORE as header
  left outer join t001 as t001 on header.bukrs = t001.bukrs
  left outer join fclm_bam_amd_t as fclm_bam_amd_t
    on header.acc_id = fclm_bam_amd_t.acc_id
    and fclm_bam_amd_t.revision = '0000'//only active revision 

    and fclm_bam_amd_t.deleted_flag <> 'X'
    and fclm_bam_amd_t.langu = $parameters.plang  
  left outer join VFCLM_BAM_DDL_ACCNUMTXT as VFCLM_BAM_DDL_ACCNUMTXT
    on header.acc_num = VFCLM_BAM_DDL_ACCNUMTXT.domvalue_l 
    and VFCLM_BAM_DDL_ACCNUMTXT.ddlanguage = $parameters.plang 
  left outer join tcurt as curr_desp on header.waers = curr_desp.waers
    and curr_desp.spras = $parameters.plang
  left outer join VFCLM_BAM_DDL_ACCOUNTSTATUS as domaintxt 
    on header.status = domaintxt.domvalue_l 
    and domaintxt.ddlanguage = $parameters.plang
  left outer join fclm_bam_ac_typt as acctyptxt 
    on header.acc_type_id = acctyptxt.acc_type_id
    and acctyptxt.langu = $parameters.plang 
  left outer join t005t as countries
    on header.banks = countries.land1
    and countries.spras = $parameters.plang
  left outer join fagl_segmt as fagl_segmt
    on header.segment = fagl_segmt.segment
    and fagl_segmt.langu = $parameters.plang
  left outer join tgsbt as tgsbt
    on header.gsber = tgsbt.gsber
    and tgsbt.spras = $parameters.plang
  left outer join fclm_bam_bsimp_t as bsimport
    on header.edbsum = bsimport.import_type
    and bsimport.langu =$parameters.plang
//  left outer join fclm_bam_bsimp_t as bsimport2

//    on header.idbsum = bsimport2.import_type

//    and bsimport2.langu = $parameters.plang

{
    key header.acc_id,
  
    header.banks,
    header.bankl,
      
    header.acc_num,
    case header.acc_num
    when '' then VFCLM_BAM_DDL_ACCNUMTXT.ddtext
    else header.acc_num
    end as acc_num_txt,
      
    header.acc_type_id,
    header.valid_from,
    header.valid_to,
    header.iban,
      
    header.status,
      
    header.bukrs,
    header.prctr,
    header.gsber,
    header.segment,
    header.beneficial,
    header.bank_contact,
    header.rel_manager,
    header.company_contact,
    header.account_supervisor,
    header.edbsum,
    header.idbsum,
    header.bs_upload_time,
      
    header.importing_channel,
    header.cutoff_time_d,
    header.cutoff_time_c,
    header.planned_closing_date,
    header.upload_bs_flag,
      
    header.created_by,
    header.opened_by,
    header.changed_by,
    header.closed_by,
      
    header.control_key,
    header.iban_valid_from,      

    header.waers,
      
    header.banka,
    header.swift,
    header.bnklz,
    
    header.hbkid,
    header.hktid,
      
    fclm_bam_amd_t.description,      
    t001.butxt,
    header.InApprovalProcess,

    // Display purpose columns

    acctyptxt.acc_type_desc as AccountTypeDesp,
    domaintxt.ddtext as StatusDesp,
    curr_desp.ktext as CurrencyDesp,
    tgsbt.gtext as BusinessAreaDesp,
    fagl_segmt.name as SegmentDesp,
    bsimport.import_type_desc as EoDImportDesp,
    //bsimport2.import_type_desc as IntradayImportDesp,

    countries.landx as BankCountryDesp,
    case header.acc_num
    when '' then VFCLM_BAM_DDL_ACCNUMTXT.ddtext
    else header.acc_num
    end as AccountNumberDesp,
      
    $extension.*
}
where header.banks is not null
  and header.banks <> ''
  and header.bankl is not null
  and header.bankl <> ''; 
  
 
  
  
  
  
  
 /*+[internal] {
"BASEINFO":
{
"FROM":
[
"VFCLM_BAM_DDL_ACCNUMTXT",
"VFCLM_BAM_DDL_ACCOUNTSTATUS",
"VFCLM_BAM_DDL_HIERNODECORE",
"FAGL_SEGMT",
"FCLM_BAM_AC_TYPT",
"FCLM_BAM_AMD_T",
"FCLM_BAM_BSIMP_T",
"T001",
"T005T",
"TCURT",
"TGSBT"
],
"ASSOCIATED":
[],
"BASE":
[],
"ANNO_REF":
[],
"SCALAR_FUNCTION":
[],
"VERSION":0,
"ANNOREF_EVALUATION_ERROR":""
}
}*/