P_CPBUPAEMAILALL

@AbapCatalog.sqlViewName: 'PCPBUPAEMAILA'

@AbapCatalog.compiler.compareFilter: true
@AbapCatalog.preserveKey: true
@AccessControl.authorizationCheck: #NOT_REQUIRED

@VDM.private: true
@VDM.viewType: #CONSUMPTION

@ObjectModel.usageType: {
  dataClass: #MASTER,
  serviceQuality: #D,
  sizeCategory: #XXL
}

@ClientHandling.algorithm: #SESSION_VARIABLE

@EndUserText.label: 'Business Partner Emails from all sources'
define view P_CPBUPAEMAILALL as
  // ----- Customer Clerk's Internet Address -----

  select from I_CustomerCompany as _CustomerCompany
    {
      key cast( _CustomerCompany.AccountingClerkInternetAddress as ad_smtpadr )                                     as EmailAddress,
      key _CustomerCompany._Customer._CustomerToBusinessPartner._BusinessPartner.BusinessPartner                    as BusinessPartner,
      key cast ( '1' as fin_corr_email_source_type )                                                                as EmailSourceType,
          cast( '' as ad_addrnum )                                                                                  as AddressID,
          _CustomerCompany.CompanyCode                                                                              as CompanyCode,
          _CustomerCompany.Customer                                                                                 as ContactPerson,
          _CustomerCompany._Customer._CustomerToBusinessPartner._BusinessPartner.AuthorizationGroup                 as AuthorizationGroup,
          cast( '' as bu_reltyp )                                                                                   as RelationshipCategory,
          _CustomerCompany._Customer._CustomerToBusinessPartner._BusinessPartner.BusinessPartnerCategory            as BusinessPartnerCategory,
          _CustomerCompany._Customer._CustomerToBusinessPartner._BusinessPartner.FirstName                          as UxFcFirstName,
          _CustomerCompany._Customer._CustomerToBusinessPartner._BusinessPartner.LastName                           as UxFcLastName,
          _CustomerCompany._Customer._CustomerToBusinessPartner._BusinessPartner.GroupBusinessPartnerName1          as UxFcGroupBusinessPartnerName1,
          _CustomerCompany._Customer._CustomerToBusinessPartner._BusinessPartner.GroupBusinessPartnerName2          as UxFcGroupBusinessPartnerName2,
          _CustomerCompany._Customer._CustomerToBusinessPartner._BusinessPartner.OrganizationBPName1                as UxFcOrganizationBPName1,
          _CustomerCompany._Customer._CustomerToBusinessPartner._BusinessPartner.OrganizationBPName2                as UxFcOrganizationBPName2,
          cast( $session.system_date as abap.dats )                                                                 as AuxValidityStartDate,
          cast( $session.system_date as abap.dats )                                                                 as AuxValidityEndDate          
    }
    where
      _CustomerCompany.AccountingClerkInternetAddress <> '' // need to check for empty string, does not work with null 

union
  // ----- Supplier Clerk's Internet Address -----

  select from I_SupplierCompany as _SupplierCompany
    {
      key cast( _SupplierCompany.AccountingClerkInternetAddress as ad_smtpadr )                                     as EmailAddress,
      key _SupplierCompany._Supplier._SupplierToBusinessPartner._BusinessPartner.BusinessPartner                    as BusinessPartner,
      key cast ( '6' as fin_corr_email_source_type )                                                                as EmailSourceType,
          cast( '' as ad_addrnum )                                                                                  as AddressID,
          _SupplierCompany.CompanyCode                                                                              as CompanyCode,   
          _SupplierCompany.Supplier                                                                                 as ContactPerson,
          _SupplierCompany._Supplier._SupplierToBusinessPartner._BusinessPartner.AuthorizationGroup                 as AuthorizationGroup,
          cast( '' as bu_reltyp )                                                                                   as RelationshipCategory,
          _SupplierCompany._Supplier._SupplierToBusinessPartner._BusinessPartner.BusinessPartnerCategory            as BusinessPartnerCategory,
          _SupplierCompany._Supplier._SupplierToBusinessPartner._BusinessPartner.FirstName                          as UxFcFirstName,
          _SupplierCompany._Supplier._SupplierToBusinessPartner._BusinessPartner.LastName                           as UxFcLastName,
          _SupplierCompany._Supplier._SupplierToBusinessPartner._BusinessPartner.GroupBusinessPartnerName1          as UxFcGroupBusinessPartnerName1,
          _SupplierCompany._Supplier._SupplierToBusinessPartner._BusinessPartner.GroupBusinessPartnerName2          as UxFcGroupBusinessPartnerName2,
          _SupplierCompany._Supplier._SupplierToBusinessPartner._BusinessPartner.OrganizationBPName1                as UxFcOrganizationBPName1,
          _SupplierCompany._Supplier._SupplierToBusinessPartner._BusinessPartner.OrganizationBPName2                as UxFcOrganizationBPName2,
          cast( $session.system_date as abap.dats )                                                                 as AuxValidityStartDate,
          cast( $session.system_date as abap.dats )                                                                 as AuxValidityEndDate
    }
    where
      _SupplierCompany.AccountingClerkInternetAddress <> '' // need to check for empty string, does not work with null

union 
  // independent email addresses

  select from I_BusinessPartner as _BusinessPartner
      association [0..*] to I_AddressEmailAddress as _AddressEmailAddress on  _AddressEmailAddress.AddressID           = _BusinessPartner.IndependentAddressID
                                                                          and _AddressEmailAddress.CommNumberIsNotUsed = ''
    {
      key _AddressEmailAddress.EmailAddress,
      key _BusinessPartner.BusinessPartner,
      key cast ( '4' as fin_corr_email_source_type )                                                                  as EmailSourceType,
          IndependentAddressID as AddressID,
          cast( '' as bukrs )                                                                                         as CompanyCode,
          _BusinessPartner.BusinessPartner                                                                            as ContactPerson,
          _BusinessPartner.AuthorizationGroup                                                                         as AuthorizationGroup,
          cast( '' as bu_reltyp )                                                                                     as RelationshipCategory,
          _BusinessPartner.BusinessPartnerCategory                                                                    as BusinessPartnerCategory,
          _BusinessPartner.FirstName                                                                                  as UxFcFirstName,
          _BusinessPartner.LastName                                                                                   as UxFcLastName,
          _BusinessPartner.GroupBusinessPartnerName1                                                                  as UxFcGroupBusinessPartnerName1,
          _BusinessPartner.GroupBusinessPartnerName2                                                                  as UxFcGroupBusinessPartnerName2,
          _BusinessPartner.OrganizationBPName1                                                                        as UxFcOrganizationBPName1,
          _BusinessPartner.OrganizationBPName2                                                                        as UxFcOrganizationBPName2,
          cast( _AddressEmailAddress.ValidityStartDateTime as abap.dats )                                             as AuxValidityStartDate,
          cast( _AddressEmailAddress.ValidityEndDateTime as abap.dats )                                               as AuxValidityEndDate
    }
union
  // communication email addresses 

    select from I_BusinessPartnerAddress as _BusinessPartnerAddress
      association [1] to I_BusinessPartner as _BusinessPartner on _BusinessPartner.BusinessPartner = $projection.BusinessPartner
      association [0..*] to I_AddressEmailAddress as _AddressEmailAddress on  _AddressEmailAddress.AddressID           = _BusinessPartnerAddress.AddressNumber
                                                                          and _AddressEmailAddress.CommNumberIsNotUsed = ''                                                                        
    {
      key _AddressEmailAddress.EmailAddress,
      key _BusinessPartnerAddress.BusinessPartner,
      key case
            when _BusinessPartner._CurrentDefaultAddress.AddressID = _BusinessPartnerAddress.AddressNumber then '2'
            else '3'
          end                                                                                                           as EmailSourceType,
          _BusinessPartnerAddress.AddressNumber as AddressID,
          cast( '' as bukrs )                                                                                           as CompanyCode,
          _BusinessPartnerAddress.BusinessPartner                                                                       as ContactPerson,
          _BusinessPartner.AuthorizationGroup                                                                           as AuthorizationGroup,
          cast( '' as bu_reltyp )                                                                                       as RelationshipCategory,
          _BusinessPartner.BusinessPartnerCategory                                                                      as BusinessPartnerCategory,
          _BusinessPartner.FirstName                                                                                    as UxFcFirstName,
          _BusinessPartner.LastName                                                                                     as UxFcLastName,
          _BusinessPartner.GroupBusinessPartnerName1                                                                    as UxFcGroupBusinessPartnerName1,
          _BusinessPartner.GroupBusinessPartnerName2                                                                    as UxFcGroupBusinessPartnerName2,
          _BusinessPartner.OrganizationBPName1                                                                          as UxFcOrganizationBPName1,
          _BusinessPartner.OrganizationBPName2                                                                          as UxFcOrganizationBPName2,
          cast( _AddressEmailAddress.ValidityStartDateTime as abap.dats )                                               as AuxValidityStartDate,
          cast( _AddressEmailAddress.ValidityEndDateTime as abap.dats )                                                 as AuxValidityEndDate
    }  
union
  // Standard Functional Departments Business Partner Address ----

  select from I_BPContactToFuncAndDept as _BPRelationship
    association [1] to I_BusinessPartner as _BusinessPartner on _BusinessPartner.BusinessPartner = $projection.BusinessPartner
    
    {
      key _BPRelationship.EmailAddress,
      key _BPRelationship.BusinessPartnerCompany                                                                        as BusinessPartner,
      key cast ( '5' as fin_corr_email_source_type )                                                                    as EmailSourceType,     
          cast('' as ad_addrnum )                                                                                       as AddressID,
          cast( '' as bukrs )                                                                                           as CompanyCode,
          _BPRelationship.BusinessPartnerPerson                                                                         as ContactPerson,
          _BusinessPartner.AuthorizationGroup                                                                           as AuthorizationGroup,
          _BPRelationship.RelationshipCategory                                                                          as RelationshipCategory,        
          _BusinessPartner.BusinessPartnerCategory                                                                      as BusinessPartnerCategory,
          _BusinessPartner.FirstName                                                                                    as UxFcFirstName,
          _BusinessPartner.LastName                                                                                     as UxFcLastName,
          _BusinessPartner.GroupBusinessPartnerName1                                                                    as UxFcGroupBusinessPartnerName1,
          _BusinessPartner.GroupBusinessPartnerName2                                                                    as UxFcGroupBusinessPartnerName2,
          _BusinessPartner.OrganizationBPName1                                                                          as UxFcOrganizationBPName1,
          _BusinessPartner.OrganizationBPName2                                                                          as UxFcOrganizationBPName2,
          cast( $session.system_date as abap.dats )                                                                     as AuxValidityStartDate,
          cast( $session.system_date as abap.dats )                                                                     as AuxValidityEndDate          
    } where
      _BPRelationship.ValidityEndDate >= $session.system_date and 
      _BPRelationship.RelationshipCategory = 'BUR001' and
      _BPRelationship.EmailAddress <> ''
/*+[internal] {
"BASEINFO":
{
"FROM":
[
"I_ADDRESSEMAILADDRESS",
"I_BPCONTACTTOFUNCANDDEPT",
"I_BPCURRENTDEFAULTADDRESS",
"I_BUSINESSPARTNER",
"I_BUSINESSPARTNERADDRESS",
"I_CUSTOMER",
"I_CUSTOMERCOMPANY",
"I_CUSTOMERTOBUSINESSPARTNER",
"I_SUPPLIER",
"I_SUPPLIERCOMPANY",
"I_SUPPLIERTOBUSINESSPARTNER"
],
"ASSOCIATED":
[],
"BASE":
[],
"ANNO_REF":
[],
"SCALAR_FUNCTION":
[],
"VERSION":0,
"ANNOREF_EVALUATION_ERROR":""
}
}*/