I_NO_SAFTCustomerMasterData

@AbapCatalog.viewEnhancementCategory: [#NONE]
@AccessControl.authorizationCheck: #MANDATORY
@AccessControl.personalData.blocking: #BLOCKED_DATA_INCLUDED
@EndUserText.label: 'Master data of customers for SAFT'
@Metadata.ignorePropagatedAnnotations: true
@ObjectModel.usageType:{
  serviceQuality: #D,
  sizeCategory: #XXL,
  dataClass: #MIXED
}
@VDM.viewType: #COMPOSITE
@Consumption.dbHints: ['USE_HEX_PLAN']

define view entity I_NO_SAFTCustomerMasterData
  with parameters
    P_FromPostingDate : budat,
    P_ToPostingDate   : budat,
    P_Ledger          : fins_ledger,
    P_FiscalYear      : gjahr

  as select from           I_SAFTCustomerBalance( P_FromPostingDate: $parameters.P_FromPostingDate,
                                                  P_ToPostingDate: $parameters.P_ToPostingDate,
                                                  P_FiscalYear: $parameters.P_FiscalYear ) as Balance
    inner join             I_Customer                                                      as Customer        on Customer.Customer = Balance.Customer
    inner join             I_CustomerCompany                                               as CustomerCompany on  CustomerCompany.CompanyCode = Balance.CompanyCode
                                                                                                              and CustomerCompany.Customer    = Balance.Customer
    inner join             I_CustomerToBusinessPartner                                     as CustToBP        on CustToBP.Customer = Balance.Customer
    inner join             I_BusinessPartner                                               as BP              on BP.BusinessPartnerUUID = CustToBP.BusinessPartnerUUID

    inner join             I_BPCurrentDefaultAddress                                       as BPAddress       on BPAddress.BusinessPartner = BP.BusinessPartner

    inner join             I_Address_2                                                     as Address         on  Address.AddressID                 = BPAddress.AddressID
                                                                                                              and Address.AddressRepresentationCode is initial
                                                                                                              and Address.AddressPersonID           is initial

    left outer to one join I_RegionText                                                    as RegionText      on  RegionText.Country  = Address.Country
                                                                                                              and RegionText.Region   = Address.Region
                                                                                                              and RegionText.Language = $session.system_language
{
      /* start suppress warning shlporigin_not_inherited */
      @ObjectModel.foreignKey.association: '_CompanyCode'
  key CustomerCompany.CompanyCode,
      @ObjectModel.foreignKey.association: '_Customer'
  key CustomerCompany.Customer,
      /* end suppress warning shlporigin_not_inherited */
  key cast('' as fis_belnr)                                      as AccountingDocument,
  key cast('' as fis_docln)                                      as LedgerGLLineItem,

      cast(ltrim(Customer.Customer,'0') as saftn_no_customer_id) as NO_SAFTCustomerID,

      BP.BusinessPartner,
      BP.BusinessPartnerUUID,

      /* start suppress warning shlporigin_not_inherited */
      CustomerCompany.ReconciliationAccount as GLAccount,
      /* end suppress warning shlporigin_not_inherited */
      Customer.IsOneTimeAccount,
      Customer.TaxNumber1,
      Customer.TaxNumber2,
      Customer.BusinessPartnerName1,
      Customer.BusinessPartnerName2,
      Customer.VATRegistration,
      
      /* Field used by the one-time customer query below */
      cast('' as md_customer_name) as CustomerName, 

      Address.Country,
      Address.Region,
      Address.CityName,
      Address.StreetName,
      Address.HouseNumber,
      Address.PostalCode,
      Address.POBox,
      Address.POBoxPostalCode,
      cast('' as bankn) as BankAccount, /* Bank info for normal customers will come from C_NO_SAFTGenLedgerCustBankAcct */
      cast('' as bankk) as BankNumber,
      
      RegionText.RegionName,

      //Associations

      CustomerCompany._CompanyCode,
      CustomerCompany._Customer
}
where
      Balance.Ledger                  = $parameters.P_Ledger
  and Balance.FiscalYear              = $parameters.P_FiscalYear
  and Balance.Customer                <> ''
  and ( Balance.StartingBalanceAmtInCoCodeCrcy <> 0 or Balance.EndingBalanceAmtInCoCodeCrcy <> 0 )
  and Customer.IsOneTimeAccount       =  ''
  and BPAddress.ValidityStartDateTime <= cast( concat($parameters.P_ToPostingDate, '000000') as bu_addr_valid_from )
  and BPAddress.ValidityEndDateTime   >= cast( concat($parameters.P_FromPostingDate, '000000') as bu_addr_valid_from )

union all

select from              I_GLAccountLineItemRawData                                           as JournalItem

  inner join             I_NO_SAFTCustomersInPeriod( P_FromPostingDate: $parameters.P_FromPostingDate,
                                                     P_ToPostingDate: $parameters.P_ToPostingDate,
                                                     P_Ledger: $parameters.P_Ledger,
                                                     P_FiscalYear: $parameters.P_FiscalYear ) as Customer        on  JournalItem.CompanyCode = Customer.CompanyCode
                                                                                                                 and JournalItem.Customer    = Customer.Customer

  inner join             I_CustomerCompany                                                    as CustomerCompany on  CustomerCompany.CompanyCode = Customer.CompanyCode
                                                                                                                 and CustomerCompany.Customer    = Customer.Customer

  inner join             I_JournalEntryItemOneTimeData                                        as OneTimeBP       on  JournalItem.CompanyCode            = OneTimeBP.CompanyCode
                                                                                                                 and JournalItem.FiscalYear             = OneTimeBP.FiscalYear
                                                                                                                 and JournalItem.AccountingDocument     = OneTimeBP.AccountingDocument
                                                                                                                 and JournalItem.AccountingDocumentItem = OneTimeBP.AccountingDocumentItem

  left outer to one join I_RegionText                                                         as RegionText      on  RegionText.Country  = OneTimeBP.Country
                                                                                                                 and RegionText.Region   = OneTimeBP.Region
                                                                                                                 and RegionText.Language = $session.system_language
{
      /* start suppress warning shlporigin_not_inherited */
  key CustomerCompany.CompanyCode,
  key CustomerCompany.Customer,
      /* end suppress warning shlporigin_not_inherited */
  key JournalItem.AccountingDocument,
  key JournalItem.LedgerGLLineItem,

      case
      when OneTimeBP.TaxID1 is not initial
        then concat(concat(ltrim(JournalItem.Customer,'0'), '/'), OneTimeBP.TaxID1)
      when OneTimeBP.TaxID2 is not initial
        then concat(concat(ltrim(JournalItem.Customer,'0'), '/'), OneTimeBP.TaxID2)
      else
        concat(ltrim(JournalItem.Customer,'0'), '/NA')
      end                          as NO_SAFTCustomerID,

      Customer.BusinessPartner,
      Customer.BusinessPartnerUUID,

      /* start suppress warning shlporigin_not_inherited */
      CustomerCompany.ReconciliationAccount as GLAccount,
      /* end suppress warning shlporigin_not_inherited */      
      Customer.IsOneTimeAccount,
      Customer.TaxNumber1,
      Customer.TaxNumber2,      
      
      OneTimeBP.BusinessPartnerName1,
      OneTimeBP.BusinessPartnerName2,
      OneTimeBP.TaxID1             as VATRegistration,
      
      /* Field used to store the name of the master customer for this one-time customer */
      cast(substring(concat_with_space(Customer.BusinessPartnerName1, Customer.BusinessPartnerName2, 1), 1, 80) as md_customer_name) as CustomerName,
      
      OneTimeBP.Country,
      OneTimeBP.Region,
      OneTimeBP.CityName,
      OneTimeBP.StreetAddressName  as StreetName,
      ''                           as HouseNumber,
      OneTimeBP.PostalCode,
      OneTimeBP.POBox,
      OneTimeBP.POBoxPostalCode,
      OneTimeBP.BankAccount,
      OneTimeBP.BankNumber,
      
      RegionText.RegionName,

      //Associations

      CustomerCompany._CompanyCode,
      CustomerCompany._Customer
}
where
      JournalItem.SourceLedger         = $parameters.P_Ledger
  and JournalItem.FiscalYear           = $parameters.P_FiscalYear
  and JournalItem.PostingDate          between $parameters.P_FromPostingDate and $parameters.P_ToPostingDate
  and JournalItem.FinancialAccountType =  'D'
  and JournalItem.Customer             <> ''
  and Customer.IsOneTimeAccount        =  'X'