P_AU_StRpTPARSupplierDetails

@AbapCatalog.sqlViewName: 'PAUSRTPARSUPLRD'
@AbapCatalog.compiler.compareFilter: true
@AbapCatalog.preserveKey: true
@AccessControl.authorizationCheck: #CHECK
@VDM.viewType: #COMPOSITE
@ObjectModel.usageType.serviceQuality: #X
@ObjectModel.usageType.sizeCategory: #XL
@ObjectModel.usageType.dataClass: #MIXED
@VDM.private: true
@ClientHandling.algorithm: #SESSION_VARIABLE

define view P_AU_StRpTPARSupplierDetails

  as select from           P_AU_StRpTPARAggrgSupplierAmt as StRpTPARAggrgSplrAmt

    inner join             I_Supplier                    as _Supplier            on StRpTPARAggrgSplrAmt.Supplier = _Supplier.Supplier
  //                                                                                 and _Supplier.IsOneTimeAccount = ' '

    left outer to one join P_AU_TPARSupplierBankDetails  as _SupplierBankDetails on _Supplier.Supplier = _SupplierBankDetails.Supplier

{
          @Consumption.filter : { selectionType : #SINGLE, multipleSelections : false}
  key     StatryRptgEntity                                                                      as StatryRptgEntity,
          @Consumption.filter : { selectionType : #SINGLE, multipleSelections : false}
  key     StatryRptCategory                                                                     as StatryRptCategory,
          @Consumption.filter : { selectionType : #SINGLE, multipleSelections : false}
  key     StatryRptRunID                                                                        as StatryRptRunID,
  key     StRpTPARAggrgSplrAmt.Supplier,
  key     StRpTPARAggrgSplrAmt.AU_TPARServiceType,
  key     StRpTPARAggrgSplrAmt.AU_TPARGrantName,
          TotalGrossAmountInCoCodeCrcy,
          TotalVATAmtInCoCodeCrcy,
          ReportingCurrency,
          //Adding leading zeros and truncating the values after decimal (Length in TPAR file = 11)

          lpad( cast(ceil(floor(TotalGrossAmountInCoCodeCrcy)) as abap.sstring(11) ), 11 , '0') as TotalGrossAmtWithLeadingZeros,
          lpad( cast(ceil(floor(TotalVATAmtInCoCodeCrcy)) as abap.sstring(11) ), 11 , '0')      as TotalTaxAmtWithLeadingZeros,


          //Grant Date in format DDMMYYYY

          concat  (concat( substring(AU_TPARGrantDate, 7, 2), substring(AU_TPARGrantDate, 5, 2) ),
          substring(AU_TPARGrantDate, 1, 4)       )                                             as DateFormat,
          AU_TPARGrantDate,

          //     Payee Details

          case when _Supplier.VATRegistration is initial
          then '0'
          else _Supplier.VATRegistration
          end                                                                                   as VATRegistration,

          case when _Supplier.IsNaturalPerson = 'X' //FormOfAddress <> 'Company'

          then _Supplier.OrganizationBPName1
          else ''
          end                                                                                   as BusinessPartnerName1,
          //OrganizationBPName1,


          case when _Supplier.IsNaturalPerson = 'X' //FormOfAddress <> 'Company'

          then _Supplier.OrganizationBPName2
          else ''
          end                                                                                   as BusinessPartnerName2, //OrganizationBPName2,


          case when _Supplier.IsNaturalPerson = ' '  //FormOfAddress = 'Company'

          then concat_with_space( _Supplier.OrganizationBPName1 , _Supplier.OrganizationBPName2 , 1 )
          else ''
          end                                                                                   as BusinessPartnerFullName,

          case when _Supplier._AddressDefaultRepresentation.POBox is not initial
          then  _Supplier._AddressDefaultRepresentation.POBox
          else _Supplier.StreetName
          end                                                                                   as StreetName,

          case when _Supplier._AddressDefaultRepresentation.POBox is not initial
          then _Supplier._AddressDefaultRepresentation.POBoxDeviatingCityName
          else _Supplier.CityName
          end                                                                                   as CityName,

          case when _Supplier.Country = 'AU'
          then _Supplier.Region
          else 'OTH'
          end                                                                                   as Region,

          case when _Supplier.Country = 'AU'
          then
            case when _Supplier._AddressDefaultRepresentation.POBox is not initial
                 then _Supplier._AddressDefaultRepresentation.POBoxPostalCode
                 else _Supplier.PostalCode
                 end
          else '9999'
          end                                                                                   as PostalCode,

          case when _Supplier.Country <> 'AU'
          then _Supplier._AddressDefaultRepresentation._Country._Text[ 1: Language = $session.system_language ].CountryShortName
          else ' '
          end                                                                                   as SupplierCountryName,
          _Supplier.PhoneNumber1,
          _SupplierBankDetails.Bank,
          _SupplierBankDetails.BankAccount,

          _Supplier._CurrentDfltEmailAddress.EmailAddress                                       as BusinessPartnerEmailAddress,
          //  _Supplier._AddressDefaultRepresentation._CurrentDfltEmailAddress.EmailAddress                                                                          as BusinessPartnerEmailAddress,


          //No of payee records

          1                                                                                     as NumberOfRecords,

          case when AU_PayerIsPayingToCarryOnEnt  is not initial  or AU_IndividualIsUnder18        is not initial
                 or AU_PaymentIsExceeding75       is not initial  or AU_PaymentIsWhollyInputTaxed  is not initial
                 or AU_PartnerIsSupplyWithoutGain is not initial  or AU_SupplierIsEntitledToABN    is not initial
                 or AU_PaymentIsIncomeExempted    is not initial  or AU_SupplyIsMadeAsPrivateHobby is not initial
                 or AU_SupplyMadeIsOfDmstcNature  is not initial
           then 'Y'
           else 'N'
           end                                                                                  as AU_TPARIsStatementBySupplier

          //          concat_with_space(_Supplier.StreetName , concat_with_space( _Supplier.CityName , concat_with_space(_Supplier.Region, _Supplier.PostalCode , 1 ) ,1 ) ,1 ) as AddressInALV

}
where
  IsOneTimeAccount is initial