P_NO_SAFTJournalCustSupMD

@AbapCatalog.sqlViewName: 'PNOSAFTJCSMD'
@AbapCatalog.compiler.compareFilter: true
@AccessControl.authorizationCheck: #NOT_REQUIRED
@VDM.private: true
@VDM.viewType: #COMPOSITE
@ObjectModel.usageType.sizeCategory: #XXL
@ObjectModel.usageType.dataClass:  #MIXED
@ObjectModel.usageType.serviceQuality: #P
@ClientHandling.algorithm: #SESSION_VARIABLE
@AccessControl.personalData.blocking : #NOT_REQUIRED
@EndUserText.label: 'SAF-T Norway Journal Item BP Master Data'
define view P_NO_SAFTJournalCustSupMD

  as select from           I_NO_SAFTJournalItem        as JournalLineItem
    inner join             I_CustomerToBusinessPartner as CustToBP   on CustToBP.Customer = JournalLineItem.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
    inner join             I_Customer                  as Customer   on Customer.Customer = CustToBP.Customer
    inner join             I_CustomerCompany           as CustomerCompany on  CustomerCompany.Customer = Customer.Customer
                                                                          and CustomerCompany.CompanyCode = JournalLineItem.CompanyCode
    left outer to one join I_RegionText                as RegionText on  RegionText.Region   = Address.Region
                                                                     and RegionText.Country  = Address.Country
                                                                     and RegionText.Language = $session.system_language

{
      @ObjectModel.foreignKey.association: '_CompanyCode'
  key JournalLineItem.CompanyCode                     as CompanyCode,
      @ObjectModel.foreignKey.association: '_FiscalYear'
  key JournalLineItem.FiscalYear                      as FiscalYear,
      @ObjectModel.foreignKey.association: '_JournalEntry'
  key JournalLineItem.AccountingDocument              as AccountingDocument,
  key JournalLineItem.LedgerGLLineItem                as LedgerGLLineItem,
      @ObjectModel.foreignKey.association: '_Ledger'
  key JournalLineItem.Ledger                          as Ledger,

      JournalLineItem.PostingDate,
      JournalLineItem.Customer,
      JournalLineItem.Supplier,

      // This fields will be used to access the customer contacs

      BP.BusinessPartner,
      BP.BusinessPartnerUUID,

      Customer.VATRegistration,
      ''                                              as IsOneTimeAccount,
      CustomerCompany.ReconciliationAccount as GLAccount,
      Customer.AddressID,
      Customer.TaxNumber1,
      Customer.TaxNumber2,
      Customer.OrganizationBPName1,
      Customer.OrganizationBPName2,
      Address.CityName,
      Address.StreetName,
      Address.HouseNumber,
      Address.PostalCode,
      Address.Country,
      Address.POBox,
      Address.POBoxPostalCode,
      RegionText.RegionName                           as Region,

      JournalLineItem._CompanyCode,
      JournalLineItem._FiscalYear,
      JournalLineItem._Ledger,
      JournalLineItem._JournalEntry
}
where
      Customer.IsOneTimeAccount            = ' '
  and JournalLineItem.FinancialAccountType = 'D'


union select from        I_NO_SAFTJournalItem        as JournalLineItem
  inner join             I_SupplierToBusinessPartner as SupToBP    on SupToBP.Supplier = JournalLineItem.Supplier
  inner join             I_BusinessPartner           as BP         on BP.BusinessPartnerUUID = SupToBP.BusinessPartnerUUID
  inner join             I_BPCurrentDefaultAddress   as BPAddress  on BPAddress.BusinessPartner = BP.BusinessPartner
  inner join             I_Address_2                 as Address    on Address.AddressID = BPAddress.AddressID
  inner join             I_Supplier                  as Supplier   on Supplier.Supplier = SupToBP.Supplier
  inner join             I_SupplierCompany           as SupplierCompany on  SupplierCompany.Supplier = Supplier.Supplier
                                                                        and SupplierCompany.CompanyCode = JournalLineItem.CompanyCode

  left outer to one join I_RegionText                as RegionText on  RegionText.Region   = Address.Region
                                                                   and RegionText.Country  = Address.Country
                                                                   and RegionText.Language = $session.system_language
{
      @ObjectModel.foreignKey.association: '_CompanyCode'
  key JournalLineItem.CompanyCode        as CompanyCode,
      @ObjectModel.foreignKey.association: '_FiscalYear'
  key JournalLineItem.FiscalYear         as FiscalYear,
      @ObjectModel.foreignKey.association: '_JournalEntry'
  key JournalLineItem.AccountingDocument as AccountingDocument,
  key JournalLineItem.LedgerGLLineItem   as LedgerGLLineItem,
      @ObjectModel.foreignKey.association: '_Ledger'
  key JournalLineItem.Ledger             as Ledger,

      JournalLineItem.PostingDate,
      JournalLineItem.Customer,
      JournalLineItem.Supplier,

      // This fields will be used to access the supplier contacs

      BP.BusinessPartner,
      BP.BusinessPartnerUUID,

      Supplier.VATRegistration,
      ''                                 as IsOneTimeAccount,
      SupplierCompany.ReconciliationAccount as GLAccount,
      Supplier.AddressID,
      Supplier.TaxNumber1,
      Supplier.TaxNumber2,
      Supplier.OrganizationBPName1,
      Supplier.OrganizationBPName2,
      Address.CityName,
      Address.StreetName,
      Address.HouseNumber,
      Address.PostalCode,
      Address.Country,
      Address.POBox,
      Address.POBoxPostalCode,
      RegionText.RegionName              as Region,

      JournalLineItem._CompanyCode,
      JournalLineItem._FiscalYear,
      JournalLineItem._Ledger,
      JournalLineItem._JournalEntry
}
where
      Supplier.IsOneTimeAccount            = ' '
  and JournalLineItem.FinancialAccountType = 'K'


union select from        I_NO_SAFTJournalItem        as JournalLineItem
  inner join             I_CustomerToBusinessPartner as CustToBP   on CustToBP.Customer = JournalLineItem.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
  inner join             I_Customer                  as Customer   on Customer.Customer = CustToBP.Customer
  inner join             I_OneTimeAccountBP          as OneTimeBP  on  JournalLineItem.CompanyCode            = OneTimeBP.CompanyCode
                                                                   and JournalLineItem.FiscalYear             = OneTimeBP.FiscalYear
                                                                   and JournalLineItem.AccountingDocument     = OneTimeBP.AccountingDocument
                                                                   and JournalLineItem.AccountingDocumentItem = OneTimeBP.AccountingDocumentItem
  left outer to one join I_RegionText                as RegionText on  RegionText.Region   = Address.Region
                                                                   and RegionText.Country  = Address.Country
                                                                   and RegionText.Language = $session.system_language

{

      @ObjectModel.foreignKey.association: '_CompanyCode'
  key JournalLineItem.CompanyCode        as CompanyCode,
      @ObjectModel.foreignKey.association: '_FiscalYear'
  key JournalLineItem.FiscalYear         as FiscalYear,
      @ObjectModel.foreignKey.association: '_JournalEntry'
  key JournalLineItem.AccountingDocument as AccountingDocument,
  key JournalLineItem.LedgerGLLineItem   as LedgerGLLineItem,
      @ObjectModel.foreignKey.association: '_Ledger'
  key JournalLineItem.Ledger             as Ledger,

      JournalLineItem.PostingDate,
      JournalLineItem.Customer,
      JournalLineItem.Supplier,

      // This fields will be used to access the customer contacs

      BP.BusinessPartner,
      BP.BusinessPartnerUUID,

      case
        when OneTimeBP.TaxID1 is not initial
            then OneTimeBP.TaxID1
        else substring(JournalLineItem.VATRegistration ,3,18)
      end                                as VATRegistration,
      'X'                                as IsOneTimeAccount,
      ''                                 as GLAccount,
      OneTimeBP.AddressID,
      ''                                 as TaxNumber1,
      ''                                 as TaxNumber2,
      ''                                 as OrganizationBPName1,
      ''                                 as OrganizationBPName2,
      Address.CityName,
      Address.StreetName,
      Address.HouseNumber,
      Address.PostalCode,
      Address.Country,
      Address.POBox,
      Address.POBoxPostalCode,
      RegionText.RegionName              as Region,

      JournalLineItem._CompanyCode,
      JournalLineItem._FiscalYear,
      JournalLineItem._Ledger,
      JournalLineItem._JournalEntry
}
where
      Customer.IsOneTimeAccount            = 'X'
  and JournalLineItem.FinancialAccountType = 'D'


union select from        I_NO_SAFTJournalItem        as JournalLineItem
  inner join             I_SupplierToBusinessPartner as SupToBP    on SupToBP.Supplier = JournalLineItem.Supplier
  inner join             I_BusinessPartner           as BP         on BP.BusinessPartnerUUID = SupToBP.BusinessPartnerUUID
  inner join             I_BPCurrentDefaultAddress   as BPAddress  on BPAddress.BusinessPartner = BP.BusinessPartner
  inner join             I_Address_2                 as Address    on Address.AddressID = BPAddress.AddressID
  inner join             I_Supplier                  as Supplier   on Supplier.Supplier = SupToBP.Supplier
  inner join             I_OneTimeAccountBP          as OneTimeBP  on  JournalLineItem.CompanyCode            = OneTimeBP.CompanyCode
                                                                   and JournalLineItem.FiscalYear             = OneTimeBP.FiscalYear
                                                                   and JournalLineItem.AccountingDocument     = OneTimeBP.AccountingDocument
                                                                   and JournalLineItem.AccountingDocumentItem = OneTimeBP.AccountingDocumentItem
  left outer to one join I_RegionText                as RegionText on  RegionText.Region   = Address.Region
                                                                   and RegionText.Country  = Address.Country
                                                                   and RegionText.Language = $session.system_language
{

      @ObjectModel.foreignKey.association: '_CompanyCode'
  key JournalLineItem.CompanyCode        as CompanyCode,
      @ObjectModel.foreignKey.association: '_FiscalYear'
  key JournalLineItem.FiscalYear         as FiscalYear,
      @ObjectModel.foreignKey.association: '_JournalEntry'
  key JournalLineItem.AccountingDocument as AccountingDocument,
  key JournalLineItem.LedgerGLLineItem   as LedgerGLLineItem,
      @ObjectModel.foreignKey.association: '_Ledger'
  key JournalLineItem.Ledger             as Ledger,

      JournalLineItem.PostingDate,
      JournalLineItem.Customer,
      JournalLineItem.Supplier,

      // This fields will be used to access the customer contacs

      BP.BusinessPartner,
      BP.BusinessPartnerUUID,

      case
         when OneTimeBP.TaxID1 is not initial
             then substring(OneTimeBP.TaxID1 ,3,18)
         when JournalLineItem.VATRegistration is not initial
             then substring(JournalLineItem.VATRegistration ,3,18)
       else ''
       end                               as VATRegistration,
      'X'                                as IsOneTimeAccount,
      ''                                 as GLAccount,
      OneTimeBP.AddressID,
      ''                                 as TaxNumber1,
      ''                                 as TaxNumber2,
      ''                                 as OrganizationBPName1,
      ''                                 as OrganizationBPName2,
      Address.CityName,
      Address.StreetName,
      Address.HouseNumber,
      Address.PostalCode,
      Address.Country,
      Address.POBox,
      Address.POBoxPostalCode,
      RegionText.RegionName              as Region,

      JournalLineItem._CompanyCode,
      JournalLineItem._FiscalYear,
      JournalLineItem._Ledger,
      JournalLineItem._JournalEntry
}
where
      Supplier.IsOneTimeAccount            = 'X'
  and JournalLineItem.FinancialAccountType = 'K'