@AbapCatalog.sqlViewName: 'PSRTIEUTAXCLS7'
@AbapCatalog.preserveKey:true
@VDM.viewType: #COMPOSITE
@VDM.private: true
//@Analytics: { dataCategory: #DIMENSION }
@AccessControl.authorizationCheck: #NOT_REQUIRED
//@EndUserText.label: 'EU Sales Tax Item view for Statutory Reporting'
@ClientHandling.algorithm: #SESSION_VARIABLE
define view P_StRpTaxItmWithEUTaxClass7
as select from P_StRpTaxItmWithEUTaxClass5
inner join I_Country on ( (P_StRpTaxItmWithEUTaxClass5.TaxCountry is initial and I_Country.Country = P_StRpTaxItmWithEUTaxClass5.Country) or
(P_StRpTaxItmWithEUTaxClass5.TaxCountry is not initial and I_Country.Country = P_StRpTaxItmWithEUTaxClass5.TaxCountry) )
inner join I_TaxCode on I_TaxCode.TaxCalculationProcedure = I_Country.TaxCalculationProcedure
and I_TaxCode.TaxCode = P_StRpTaxItmWithEUTaxClass5.TaxCode
inner join P_T007B_GLO on P_T007B_GLO.ktosl = P_StRpTaxItmWithEUTaxClass5.TransactionTypeDetermination
left outer join I_StRpJournalEntryLog on I_StRpJournalEntryLog.AccountingDocument = P_StRpTaxItmWithEUTaxClass5.AccountingDocument
and I_StRpJournalEntryLog.CompanyCode = P_StRpTaxItmWithEUTaxClass5.CompanyCode
and I_StRpJournalEntryLog.FiscalYear = P_StRpTaxItmWithEUTaxClass5.FiscalYear
left outer join P_OplAcctgDocItemDebitSum on
P_StRpTaxItmWithEUTaxClass5.CompanyCode = P_OplAcctgDocItemDebitSum.CompanyCode and
P_StRpTaxItmWithEUTaxClass5.AccountingDocument = P_OplAcctgDocItemDebitSum.AccountingDocument and
P_StRpTaxItmWithEUTaxClass5.FiscalYear = P_OplAcctgDocItemDebitSum.FiscalYear and
P_StRpTaxItmWithEUTaxClass5.TaxCode = P_OplAcctgDocItemDebitSum.TaxCode
left outer join P_OplAcctgDocItemCreditSum on
P_StRpTaxItmWithEUTaxClass5.CompanyCode = P_OplAcctgDocItemCreditSum.CompanyCode and
P_StRpTaxItmWithEUTaxClass5.AccountingDocument = P_OplAcctgDocItemCreditSum.AccountingDocument and
P_StRpTaxItmWithEUTaxClass5.FiscalYear = P_OplAcctgDocItemCreditSum.FiscalYear and
P_StRpTaxItmWithEUTaxClass5.TaxCode = P_OplAcctgDocItemCreditSum.TaxCode
// Join with supplier's master record to retrieve stcd6
left outer join I_Supplier as S on Creditor = S.Supplier
// Join with I_Country to get ISO Code of Supplier's country
left outer join I_Country as Country on S.Country = Country.Country
// Validity period of Northern Ireland exception is retrieved from prepared CDS view
inner join I_BrexitProtocolValidity as niprot on niprot.BrexitProtocolType = 'NIPROT'
// List of regions relevant to Northern Ireland protocol is available via this CDS view
left outer join P_niprotocolregions( P_SAPClient:$session.client ) as niregions on niregions.region = S.Region
association [0..1] to I_Currency as _ReportingCurrency on $projection.ReportingCurrency = _ReportingCurrency.Currency
association [0..*] to P_StRpTaxItmWithEUTaxClass6 as _JournalEntry on $projection.CompanyCode = _JournalEntry.CompanyCode
and $projection.FiscalYear = _JournalEntry.FiscalYear
and $projection.AccountingDocument = _JournalEntry.AccountingDocument
and $projection.TaxCode = _JournalEntry.TaxCode
and $projection.TaxItemGroup = _JournalEntry.TaxItemGroup
and $projection.TaxJurisdiction = _JournalEntry.TaxJurisdiction
and $projection.TaxDebitCreditCode = _JournalEntry.DebitCreditCode // This condition was added based on SAP note 1626904
association to I_OneTimeAccountBP as _OneTimeAccount on _OneTimeAccount.CompanyCode = $projection.CompanyCode
and _OneTimeAccount.AccountingDocument = $projection.AccountingDocument
and _OneTimeAccount.FiscalYear = $projection.FiscalYear
and _OneTimeAccount.AccountingDocumentItem = $projection.TaxItem
{
key P_StRpTaxItmWithEUTaxClass5.CompanyCode,
key P_StRpTaxItmWithEUTaxClass5.AccountingDocument,
key P_StRpTaxItmWithEUTaxClass5.FiscalYear,
key P_StRpTaxItmWithEUTaxClass5.TaxCode,
key TransactionTypeDetermination,
key TaxItem,
key I_StRpJournalEntryLog.StatryRptCategory,
key I_StRpJournalEntryLog.StatryRptRunID,
key I_StRpJournalEntryLog.StatryRptgEntity,
cast(Debtor as fis_kunnr) as Debtor,
CustomerName,
cast(Creditor as fis_lifnr) as Creditor,
P_StRpTaxItmWithEUTaxClass5.SupplierName,
_BusinessPartner.BusinessPartner,
cast(_BusinessPartner.BusinessPartnerName as bp_name_ca preserving type ) as BusinessPartnerName,
cast(coalesce(_JournalEntry.VATRegistration, P_StRpTaxItmWithEUTaxClass5.VATRegistration) as stceg) as VATRegistration,
cast(coalesce(_JournalEntry.VATRegistration, P_StRpTaxItmWithEUTaxClass5.VATRegistrationFromBSEG) as stceg) as VATRegistrationFromBSEG,
cast(coalesce(_JournalEntry.IsEUTriangularDeal, P_StRpTaxItmWithEUTaxClass5.IsEUTriangularDeal) as xegdr ) as IsEUTriangularDeal,
case
when
/** Following conditions must be met to satisfy Northern Ireland protocol and search for XI VAT number**/
/** Supplier's VAT Registration number was not yet found and it is a supplier from one of GB regions representing Northern Ireland **/
Creditor <> '' and P_StRpTaxItmWithEUTaxClass5.VATRegistration = '' and
Country.CountryISOCode = 'GB' and niregions.region is not null and
/** Purchase side is reported only in several countries **/
(P_StRpTaxItmWithEUTaxClass5.ReportingCountry = 'RO' or P_StRpTaxItmWithEUTaxClass5.ReportingCountry = 'ES'
or P_StRpTaxItmWithEUTaxClass5.ReportingCountry = 'HU' or P_StRpTaxItmWithEUTaxClass5.ReportingCountry = 'HR') and
/** Not valid for services **/
I_TaxCode.EUTaxClassification <> '2' and I_TaxCode.EUTaxClassification <> '4' and
I_TaxCode.EUTaxClassification <> '5' and I_TaxCode.EUTaxClassification <> 'B' and
I_TaxCode.EUTaxClassification <> 'T' and I_TaxCode.EUTaxClassification <> 'U' and
/** Valid only in certain time frame **/
ReportingDate between niprot.BrexitProtocolValidFromDate and niprot.BrexitProtocolValidToDate
then S.TaxNumber6
else ''
end as VATRegistrationXI,
P_StRpTaxItmWithEUTaxClass5.IsOneTimeAccount,
P_StRpTaxItmWithEUTaxClass5.AuthorizationGroup,
P_StRpTaxItmWithEUTaxClass5.TaxItemGroup,
P_StRpTaxItmWithEUTaxClass5.TaxJurisdiction,
P_StRpTaxItmWithEUTaxClass5.Country,
@Semantics.currencyCode: true
cast(ReportingCurrency as glo_reporting_currency) as ReportingCurrency,
@DefaultAggregation:#SUM
@Semantics.amount.currencyCode: 'ReportingCurrency'
cast(coalesce(_JournalEntry.AmountInReportingCurrency, P_StRpTaxItmWithEUTaxClass5.AmountInReportingCurrency) as glo_amount_rptg_crcy preserving type) as AmountInReportingCurrency,
cast( ReportingCountry as glo_country ) as ReportingCountry,
P_StRpTaxItmWithEUTaxClass5.TaxReturnCountry,
ReportingDate,
@DefaultAggregation:#SUM
@Semantics.amount.currencyCode: 'CompanyCodeCurrency'
cast(TaxBaseAmountInCoCodeCrcy as hwbas) as TaxBaseAmountInCoCodeCrcy,
@Semantics.currencyCode: true
cast ( CompanyCodeCurrency as fis_hwaer) as CompanyCodeCurrency,
@DefaultAggregation:#SUM
@Semantics.amount.currencyCode: 'DocumentCurrency'
cast(TaxBaseAmountInTransCrcy as fwbas) as TaxBaseAmountInTransCrcy,
@Semantics.currencyCode: true
DocumentCurrency,
@DefaultAggregation:#SUM
@Semantics.amount.currencyCode: 'CountryCurrency'
cast(TaxBaseAmountInCountryCrcy as glo_cntry_base ) as TaxBaseAmountInCountryCrcy,
@Semantics.currencyCode: true
P_StRpTaxItmWithEUTaxClass5.CountryCurrency,
IsReversal,
IsReversed,
TaxableEntity,
PostingDate,
DocumentDate,
TaxReportingDate,
cast( DelivOfGoodsDestCountry as egbld) as DelivOfGoodsDestCountry,
cast( DelivOfGoodsOriginCountry as eglld) as DelivOfGoodsOriginCountry,
I_TaxCode.EUTaxClassification as EUTaxClassification,
I_TaxCode.TaxType as TaxType,
_ReportingCurrency,
_OneTimeAccount,
cast(
coalesce(abs(P_OplAcctgDocItemDebitSum.AmountInCompanyCodeCurrency),0)
as glo_amount_rptg_crcy) as AmountInCompanyCodeCurrSumForS,
cast(
coalesce(abs(P_OplAcctgDocItemCreditSum.AmountInCompanyCodeCurrency),0)
as glo_amount_rptg_crcy) as AmountInCompanyCodeCurrSumForH,
TaxDebitCreditCode,
ReferenceDocumentType
}
where I_TaxCode.EUTaxClassification <> '' and (
P_T007B_GLO.stgrp = '1' or (
( P_T007B_GLO.stgrp = '3' or P_T007B_GLO.stgrp = '4' ) and I_TaxCode.TaxType = 'A'
)
)
/*+[internal] {
"BASEINFO":
{
"FROM":
[
"I_BREXITPROTOCOLVALIDITY",
"I_BUSINESSPARTNER",
"I_COUNTRY",
"I_STRPJOURNALENTRYLOG",
"I_SUPPLIER",
"I_TAXCODE",
"P_OPLACCTGDOCITEMCREDITSUM",
"P_OPLACCTGDOCITEMDEBITSUM",
"P_STRPTAXITMWITHEUTAXCLASS5",
"P_STRPTAXITMWITHEUTAXCLASS6",
"P_T007B_GLO"
],
"ASSOCIATED":
[
"I_CURRENCY",
"I_ONETIMEACCOUNTBP",
"P_STRPTAXITMWITHEUTAXCLASS6"
],
"BASE":
[],
"ANNO_REF":
[],
"SCALAR_FUNCTION":
[],
"VERSION":0,
"ANNOREF_EVALUATION_ERROR":""
}
}*/