P_UA_CompanyCodeData

@AbapCatalog.sqlViewName: 'PUACOMPANYDATA'
@ClientHandling.algorithm: #SESSION_VARIABLE
@AccessControl.authorizationCheck: #CHECK
@VDM.viewType: #COMPOSITE
@ObjectModel.usageType.sizeCategory: #L
@ObjectModel.usageType.dataClass:  #MASTER
@ObjectModel.usageType.serviceQuality: #C
@VDM.private: true
@AbapCatalog.preserveKey:true
@AbapCatalog.compiler.compareFilter:true
define view P_UA_CompanyCodeData
  as select from    I_CompanyCode
    left outer join P_UA_OrganizationAddress     as CompanyCodeAddress on CompanyCodeAddress.AddressID = I_CompanyCode.AddressID
    left outer join I_AddlCompanyCodeInformation as AddlInfoTIN        on  AddlInfoTIN.CompanyCode              = I_CompanyCode.CompanyCode
                                                                       and AddlInfoTIN.CompanyCodeParameterType = 'SAPU03'

    left outer join I_AddlCompanyCodeInformation as AddlInfoSPATO      on  AddlInfoSPATO.CompanyCode              = I_CompanyCode.CompanyCode
                                                                       and AddlInfoSPATO.CompanyCodeParameterType = 'SAPU06'

    left outer join I_AddlCompanyCodeInformation as AddlInfoKVED       on  AddlInfoKVED.CompanyCode              = I_CompanyCode.CompanyCode
                                                                       and AddlInfoKVED.CompanyCodeParameterType = 'SAPU08'

    left outer join I_AddlCompanyCodeInformation as AddlInfoRUK        on  AddlInfoRUK.CompanyCode              = I_CompanyCode.CompanyCode
                                                                       and AddlInfoRUK.CompanyCodeParameterType = 'J1UBOS'

    left outer join I_AddlCompanyCodeInformation as AddlInfoBUH        on  AddlInfoBUH.CompanyCode              = I_CompanyCode.CompanyCode
                                                                       and AddlInfoBUH.CompanyCodeParameterType = 'J1UGLB'

    left outer join I_AddlCompanyCodeInformation as AddlInfoOPFCD      on  AddlInfoOPFCD.CompanyCode              = I_CompanyCode.CompanyCode
                                                                       and AddlInfoOPFCD.CompanyCodeParameterType = 'SAPU09'

    left outer join I_AddlCompanyCodeInformation as AddlInfoUSR        on  AddlInfoUSR.CompanyCode              = I_CompanyCode.CompanyCode
                                                                       and AddlInfoUSR.CompanyCodeParameterType = 'SAPUSR'

    left outer join I_AddlCompanyCodeInformation as AddlInfoKATOT      on  AddlInfoKATOT.CompanyCode              = I_CompanyCode.CompanyCode
                                                                       and AddlInfoKATOT.CompanyCodeParameterType = 'J1UPLC'

{

  key I_CompanyCode.CompanyCode,
      I_CompanyCode.AddressID,
      CompanyCodeAddress.OrganizationName                        as CompanyName,
      AddlInfoTIN.CompanyCodeParameterValue                      as CompanyCodeTIN,
      AddlInfoSPATO.CompanyCodeParameterValue                    as CompanyCodeSPATO,
      CompanyCodeAddress.CareOfName                              as CompanyCodeTERR,
      CompanyCodeAddress.CompanyCodePhoneNum                     as CompanyCodePhoneNum,
      //addition for UA VAT DRC report

      CompanyCodeAddress.PostalCode                              as PostalCode,
      CompanyCodeAddress.EmailAddress                            as EmailAddress,
      //end of addition

      AddlInfoKVED.CompanyCodeParameterValue                     as CompanyCodeKVED,
      AddlInfoRUK.CompanyCodeParameterValue                      as CompanyCodeRUK,
      AddlInfoBUH.CompanyCodeParameterValue                      as CompanyCodeBUH,
      AddlInfoOPFCD.CompanyCodeParameterValue                    as CompanyCodeOPFCD,
      AddlInfoUSR.CompanyCodeParameterValue                      as CompanyCodeUSR,
      AddlInfoKATOT.CompanyCodeParameterValue                    as CompanyCodeKATOTTG,
      substring( AddlInfoSPATO.CompanyCodeParameterValue, 1, 2 ) as CompanyOBL,
      substring( AddlInfoSPATO.CompanyCodeParameterValue, 3, 3 ) as CompanyDistCode,

      concat( AddressStreetName, 
                 concat(AddressHouseNumber, 
                      concat(AddrHouseNumberSupplement,
                             concat(AdditionalStreetSuffixName,
                                   concat(District,Region)
                                   )
                            )
                        )
            )                                                        as CompanyAddressName,


      concat_with_space( CompanyCodeAddress.CityName,
        concat_with_space( CompanyCodeAddress.StreetName,
            CompanyCodeAddress.HouseNumber, 1 ), 1 )             as FirmAdress

}