@AbapCatalog.sqlViewName: 'ISRUSWHLDGTXITM'
@AbapCatalog.compiler.compareFilter: true
@VDM.viewType: #COMPOSITE
@ClientHandling.algorithm: #SESSION_VARIABLE
@AccessControl.authorizationCheck: #CHECK
@Analytics: { dataCategory: #CUBE }
@EndUserText.label: 'Withholding Tax View for US'
@ObjectModel.usageType.serviceQuality: #D
@ObjectModel.usageType.sizeCategory: #XL
@ObjectModel.usageType.dataClass: #MIXED
@Metadata.allowExtensions:true
@AccessControl.personalData.blocking: #BLOCKED_DATA_INCLUDED
@AbapCatalog.preserveKey: true
define view I_US_StrpWhldgTaxItem
as select distinct from I_StRpWhldgTaxItem as WhldgTaxItem
left outer join I_SupplierCompany as SupplierCompany on SupplierCompany.CompanyCode = WhldgTaxItem.CompanyCode
and SupplierCompany.Supplier = WhldgTaxItem.Creditor
{
key WhldgTaxItem.CompanyCode,
key AccountingDocument,
key FiscalYear,
key AccountingDocumentItem,
key WithholdingTaxType,
key WhldgTaxItem.StatryRptgEntity,
key WhldgTaxItem.StatryRptCategory,
key WhldgTaxItem.StatryRptRunID,
WithholdingTaxCode,
cast ( case WithholdingTaxCode
when '01' then 1
when 'F1' then 1
when '02' then 2
when 'F2' then 2
when '03' then 3
when 'F3' then 3
when '05' then 5
when 'F5' then 5
when '06' then 6
when 'F6' then 6
when '07' then 7
when 'F7' then 7
when '08' then 8
when 'F8' then 8
when '09' then 9
when 'F9' then 9
when '10' then 10
when 'F0' then 10
when '3A' then 10
when '13' then 13
when 'FB' then 13
when '3B' then 13
when '14' then 14
when 'FC' then 14
when '3C' then 14
when '15' then 151
when 'FD' then 151
when '7B' then 152
when 'FE' then 152
else
0
end as int1 ) as WhldgTxCodeGrp,
Country,
Creditor,
cast(WhldgTaxItem.BusinessPartner as /scdl/dl_partyno) as BusinessPartner,
PostingDate,
DocumentDate,
ClearingDate,
cast( ReportingDate as ftr_reporting_date) as ReportingDate,
@Semantics.currencyCode: true
ReportingCurrency,
@DefaultAggregation:#SUM
@Semantics.amount.currencyCode: 'ReportingCurrency'
cast(WhldgTaxItem.WhldgTaxBaseAmtInCoCodeCrcy as fis_qsshh) as WhldgTaxBaseAmtInCoCodeCrcy,
@DefaultAggregation:#SUM
@Semantics.amount.currencyCode: 'ReportingCurrency'
cast(WhldgTaxItem.WhldgTaxAmtInCoCodeCrcy as fis_qbshh) as WhldgTaxAmtInCoCodeCrcy,
@DefaultAggregation:#SUM
@Semantics.amount.currencyCode: 'ReportingCurrency'
WhldgTaxItem.WhldgTaxExmptAmtInCoCodeCrcy,
WhldgTaxItem._WithholdingTaxCode.WhldgTaxReferenceText,
// Start of Company Code Information
WhldgTaxItem._CompanyCode._Address.StreetName,
WhldgTaxItem._CompanyCode._Address.HouseNumber,
WhldgTaxItem._CompanyCode.CompanyCodeName,
cast(WhldgTaxItem._CompanyCode._Address.PostalCode as ad_pstcd1 ) as CompanyPostalCode,
WhldgTaxItem._CompanyCode._Address.CityCode as CityCode,
WhldgTaxItem._CompanyCode._Address.POBoxDeviatingCityName as POBoxDeviatingCityName,
//End of Company Code Information
WhldgTaxItem.OrganizationBPName1 as OrganizationBPName1,
WhldgTaxItem.OrganizationBPName2 as OrganizationBPName2,
WhldgTaxItem.OrganizationBPName2 as OrganizationBPName3,
WhldgTaxItem.POBox as BusPartPOBox,
WhldgTaxItem.HouseNumber as CustomerSupplierHouseNumber,
WhldgTaxItem.Region as BPRgn,
cast (WhldgTaxItem.PostalCode as glo_partner_pocode) as CustomerSupplierPostalCode,
cast(WhldgTaxItem.CityName as glo_partner_cityname) as CustomerSupplierCityName,
cast(WhldgTaxItem.StreetName as glo_partner_street ) as CustomerSupplierStreetName,
/* Need to change to One time if required */
// cast(I_StRpWhldgTaxItem.POBoxDeviatingCityName as glo_partner_pobox_dvtg_city ) as BusPartPOBoxDvtgCityName,
/* End of change to One time*/
cast(WhldgTaxItem.HouseNumberSupplementText as glo_partner_houseno_suppl ) as BPHsNmbrSuplmntTxt,
cast(WhldgTaxItem.BusinessPartnerCountry as glo_partner_country) as BusinessPartnerCountry,
cast (WhldgTaxItem.POBoxPostalCode as glo_partner_poboxpostalcode) as BusPartPOBoxPostlCode,
WhldgTaxItem._Supplier._StandardAddress.POBoxDeviatingRegion as BusPartPOBoxRgn,
WhldgTaxItem._Supplier._StandardAddress.StreetPrefixName as BPStPrefixName,
case WhldgTaxItem._Supplier._StandardAddress.POBoxDeviatingRegion
when '' then
Region
else
WhldgTaxItem._Supplier._StandardAddress.POBoxDeviatingRegion
end as CustomerSupplierAddress,
case POBoxPostalCode
when '' then
PostalCode
else
POBoxPostalCode
end as POBoxPostalCode,
case SupplierCompany.WithholdingTaxCountry
when 'US' then ''
else SupplierCompany.WithholdingTaxCountry
end as WithholdingTaxCountry,
cast(case when WhldgTaxItem._Supplier.SupplierLanguage <> 'EN'
then 'EN'
else WhldgTaxItem._Supplier.SupplierLanguage
end as spras ) as Language,
cast(Name as glo_partner) as Name,
cast( case WhldgTaxItem.BusinessPartnerCountry
when 'US' then ''
else '1'
end as glo_partner_foreign ) as FrgnBP,
cast(TaxNumber1 as stcd1) as TaxNumber1,
cast(TaxNumber2 as stcd2) as TaxNumber2,
cast (TaxNumber4 as stcd4) as TaxNumber4,
cast(TaxNumber5 as stcd5) as TaxNumber5,
cast(case when TaxNumber1 = ''
then TaxNumber2
else
TaxNumber1
end as bptaxnum) as BPTaxNumber,
WhldgTaxItem._Supplier._StandardAddress._Region.ProvincialTaxCode,
cast(WhldgTaxItem._Supplier._StandardAddress.POBoxDeviatingCityName as glo_partner_pobox_dvtg_city ) as BusPartPOBoxDvtgCityName,
WhldgTaxItem._Supplier._StandardAddress.POBoxDeviatingRegion,
cast (case
when WithholdingTaxCode = '01' or WithholdingTaxCode = 'F1' then
concat_with_space (concat(TaxNumber1, TaxNumber2), '1' , 1 )
when WithholdingTaxCode = '03' or WithholdingTaxCode = 'F3' then
concat_with_space (concat(TaxNumber1, TaxNumber2), '3' , 1 )
when WithholdingTaxCode = '04' or WithholdingTaxCode = 'F4' then
concat_with_space (concat(TaxNumber1, TaxNumber2), '4' , 1 )
when WithholdingTaxCode = '05' or WithholdingTaxCode = 'F5' then
concat_with_space (concat(TaxNumber1, TaxNumber2), '5' , 1 )
when WithholdingTaxCode = '06' or WithholdingTaxCode = 'F6' then
concat_with_space (concat(TaxNumber1, TaxNumber2), '6' , 1 )
when WithholdingTaxCode = '07' or WithholdingTaxCode = 'F7' then
concat_with_space (concat(TaxNumber1, TaxNumber2), '7' , 1 )
when WithholdingTaxCode = '09' or WithholdingTaxCode = 'F9' then
concat_with_space (concat(TaxNumber1, TaxNumber2), '9' , 1 )
when WithholdingTaxCode = 'F0' or WithholdingTaxCode = '10' or WithholdingTaxCode = '3A' then
concat_with_space (concat(TaxNumber1, TaxNumber2), '10', 1 )
when WithholdingTaxCode = 'FB' or WithholdingTaxCode = '13' or WithholdingTaxCode = '3B' then
concat_with_space (concat(TaxNumber1, TaxNumber2), '13' , 1 )
when WithholdingTaxCode = 'FC' or WithholdingTaxCode = '14' or WithholdingTaxCode = '3C' then
concat_with_space (concat(TaxNumber1, TaxNumber2), '14' , 1 )
when WithholdingTaxCode = '02' or WithholdingTaxCode = 'F2' then
concat_with_space (concat(TaxNumber1, TaxNumber2), '2' , 1 )
when WithholdingTaxCode = '08' or WithholdingTaxCode = 'F8' then
concat_with_space (concat(TaxNumber1, TaxNumber2), '8', 1 )
when WithholdingTaxCode = '09' or WithholdingTaxCode = 'F9' then
concat_with_space (concat(TaxNumber1, TaxNumber2), '9' , 1 )
when WithholdingTaxCode = 'FB' or WithholdingTaxCode = '13' or WithholdingTaxCode = '3B' then
concat_with_space (concat(TaxNumber1, TaxNumber2), '13' , 1 )
when WithholdingTaxCode = 'FD' or WithholdingTaxCode = '15' then
concat_with_space (concat(TaxNumber1, TaxNumber2), '151', 1 )
when WithholdingTaxCode = 'FE' or WithholdingTaxCode = '7B' then
concat_with_space (concat(TaxNumber1, TaxNumber2), '152' , 1 )
end as glo_us_txnmbrwhldgtxcodegrp ) as US_TaxNumberWhldgTxCodeGrp,
WhldgTaxItem._CompanyCode as _CompanyCode,
WhldgTaxItem._Supplier as _Supplier,
WhldgTaxItem._WithholdingTaxCode as _WithholdingTaxCode,
_ReportingCurrency as _ReportingCurrency
}
where
IsReversed = ''
and FinancialAccountType = 'K'
and(
WhldgTaxDocumentType = '002'
or WhldgTaxDocumentType = '007'
or WhldgTaxDocumentType = '005'
)