@VDM.viewType: #CONSUMPTION
@Analytics: { dataCategory: #CUBE }
@AbapCatalog.sqlViewName: 'CROSPJOURNALIS2C'
@AbapCatalog.compiler.compareFilter: true
@EndUserText.label: 'RO: Sales/Purchase Journal Items'
@AccessControl.authorizationCheck: #CHECK
@AccessControl.personalData.blocking: #BLOCKED_DATA_INCLUDED
@ClientHandling.algorithm: #SESSION_VARIABLE
@ObjectModel.usageType.sizeCategory: #XL
@ObjectModel.usageType.serviceQuality: #D
@ObjectModel.usageType.dataClass: #MIXED
@Metadata.allowExtensions:true
@Consumption.dbHints: ['USE_HEX_PLAN']
define view C_RO_StRpSalesPurchaseItemC
with parameters
P_TaxIsDeferredRelevant : figen_deferred_check,
P_TaxIsMossRelevant : figen_moss_check,
P_StatryRptgEntity : srf_reporting_entity,
P_StatryRptCategory : srf_rep_cat_id,
P_StatryRptRunID : srf_report_run_id,
P_ReportingCountry : land1,
P_TaxBoxConfiguration : figen_tdt_taxbox_config,
P_TaxReportingToDate : vatdate
as select distinct from I_StRpTaxReturnBoxCube( P_TaxIsDeferredRelevant: $parameters.P_TaxIsDeferredRelevant,
P_TaxIsMossRelevant: $parameters.P_TaxIsMossRelevant,
P_StatryRptgEntity: $parameters.P_StatryRptgEntity,
P_StatryRptCategory: $parameters.P_StatryRptCategory,
P_StatryRptRunID: $parameters.P_StatryRptRunID,
P_ReportingCountry: $parameters.P_ReportingCountry,
P_TaxBoxConfiguration: $parameters.P_TaxBoxConfiguration ) as strp
left outer join P_DEFTAX_ITEM_3(P_TaxReportingToDate : $parameters.P_TaxReportingToDate) as dti on strp.CompanyCode = dti.CompanyCode
and strp.AccountingDocument = dti.AccountingDocument
and strp.FiscalYear = dti.FiscalYear
and strp.TransactionTypeDetermination = dti.TransactionTypeDetermination
and strp.TaxCode = dti.TaxCode // and
//dti.TaxTransferDocument <> '' and
//dti.TaxTransferDocumentYear = strp.FiscalYear
left outer join P_DEFTAX_ITEM_2 as trdti on strp.CompanyCode = trdti.CompanyCode
and strp.AccountingDocument = trdti.TaxTransferDocument
and strp.FiscalYear = trdti.TaxTransferDocumentYear
left outer join I_OperationalAcctgDocItem as oadi on strp.CompanyCode = oadi.CompanyCode
and strp.AccountingDocument = oadi.AccountingDocument
and strp.FiscalYear = oadi.FiscalYear
and (
strp.TaxCode = oadi.TaxCode
or oadi.TaxCode = '**'
or oadi.TaxCode = ''
)
and (
oadi.FinancialAccountType = 'K'
or oadi.FinancialAccountType = 'D'
)
and oadi.SpecialGLCode is initial
left outer join P_RO_OperAcctgDocItemNegPost as oadiNeg on strp.CompanyCode = oadiNeg.CompanyCode
and strp.AccountingDocument = oadiNeg.AccountingDocument
and strp.FiscalYear = oadiNeg.FiscalYear
and (
strp.TaxCode = oadiNeg.TaxCode
or oadiNeg.TaxCode = '**'
or oadiNeg.TaxCode = ''
)
{
key strp.CompanyCode,
key strp.AccountingDocument,
key strp.FiscalYear,
key strp.TaxItem,
key StatryRptgEntity,
key StatryRptCategory,
key StatryRptRunID,
key strp.TaxCode,
key strp.TransactionTypeDetermination,
key TaxBoxStrucValidityStartDate,
key TaxBoxStructureType,
strp.PostingDate,
strp.DocumentDate,
TaxReportingDate,
ReportingDate,
strp.FiscalPeriod,
strp.AccountingDocumentType,
DocumentReferenceID,
CompanyCodeCountry,
ReportingCountry,
TaxReturnCountry,
ExchangeRate,
division(strp.TaxRate , 1, 0) as TaxRate,
strp.GLAccount,
strp.BusinessPlace,
strp.TaxJurisdiction,
LowestLevelTaxJurisdiction,
TaxNumber1,
TaxNumber2,
TaxNumber3,
cast( case
when _DebtorCreditorRblsPyblsItem._Customer.Customer <> ''
then coalesce( _DebtorCreditorRblsPyblsItem._OneTimeAccountBP.TaxID4 , _DebtorCreditorRblsPyblsItem._Customer.TaxNumber4)
when _DebtorCreditorRblsPyblsItem._Supplier.Supplier <> ''
then coalesce( _DebtorCreditorRblsPyblsItem._OneTimeAccountBP.TaxID4, _DebtorCreditorRblsPyblsItem._Supplier.TaxNumber4)
end as stcd4 ) as TaxNumber4,
BusinessPartner,
BusinessPartnerName,
cast( case
when oadi.VATRegistration <> '' and oadi.VATRegistration is not null
then oadi.VATRegistration
when _DebtorCreditorRblsPyblsItem._Supplier.VATRegistration <> '' and _DebtorCreditorRblsPyblsItem._Supplier.VATRegistration is not null
then _DebtorCreditorRblsPyblsItem._Supplier.VATRegistration
when _DebtorCreditorRblsPyblsItem._Customer.VATRegistration <> '' and _DebtorCreditorRblsPyblsItem._Customer.VATRegistration is not null
then _DebtorCreditorRblsPyblsItem._Customer.VATRegistration
when _DebtorCreditorRblsPyblsItem.VATRegistration <> '' and _DebtorCreditorRblsPyblsItem.VATRegistration is not null
then _DebtorCreditorRblsPyblsItem.VATRegistration
else strp.ReportingVATRegistration
end as fis_stceg ) as VATRegistration,
CustomerSupplierAddress,
strp.TaxType,
TargetTaxCode,
strp.DebitCreditCode,
oadiNeg.IsNegativePosting,
strp.IsReversal,
strp.IsReversed,
strp.AmountInReportingCurrency,
@Semantics.currencyCode:true
ReportingCurrency,
@DefaultAggregation:#SUM
@Semantics.amount.currencyCode: 'ReportingCurrency'
case
when TaxBox = 'C01'
then AmountInReportingCurrency * -1
else 0 end as TaxBox01AmtInReportingCurrency,
@DefaultAggregation:#SUM
@Semantics.amount.currencyCode: 'ReportingCurrency'
case
when TaxBox = 'C02'
then AmountInReportingCurrency * -1
else 0 end as TaxBox02AmtInReportingCurrency,
@DefaultAggregation:#SUM
@Semantics.amount.currencyCode: 'ReportingCurrency'
case
when TaxBox = 'C03'
then AmountInReportingCurrency * -1
else 0 end as TaxBox03AmtInReportingCurrency,
@DefaultAggregation:#SUM
@Semantics.amount.currencyCode: 'ReportingCurrency'
case
when TaxBox = 'C04'
then AmountInReportingCurrency * -1
else 0 end as TaxBox04AmtInReportingCurrency,
@DefaultAggregation:#SUM
@Semantics.amount.currencyCode: 'ReportingCurrency'
case
when TaxBox = 'C05'
then AmountInReportingCurrency * -1
else 0 end as TaxBox05AmtInReportingCurrency,
@DefaultAggregation:#SUM
@Semantics.amount.currencyCode: 'ReportingCurrency'
case
when TaxBox = 'C06'
then AmountInReportingCurrency * -1
else 0 end as TaxBox06AmtInReportingCurrency,
@DefaultAggregation:#SUM
@Semantics.amount.currencyCode: 'ReportingCurrency'
case
when TaxBox = 'C07'
then AmountInReportingCurrency * -1
else 0 end as TaxBox07AmtInReportingCurrency,
@DefaultAggregation:#SUM
@Semantics.amount.currencyCode: 'ReportingCurrency'
case
when TaxBox = 'C08'
then AmountInReportingCurrency * -1
else 0 end as TaxBox08AmtInReportingCurrency,
@DefaultAggregation:#SUM
@Semantics.amount.currencyCode: 'ReportingCurrency'
case
when TaxBox = 'C09'
then AmountInReportingCurrency * -1
else 0 end as TaxBox09AmtInReportingCurrency,
@DefaultAggregation:#SUM
@Semantics.amount.currencyCode: 'ReportingCurrency'
case
when TaxBox = 'C10'
then AmountInReportingCurrency * -1
else 0 end as TaxBox10AmtInReportingCurrency,
@DefaultAggregation:#SUM
@Semantics.amount.currencyCode: 'ReportingCurrency'
case
when TaxBox = 'C11'
then AmountInReportingCurrency * -1
else 0 end as TaxBox11AmtInReportingCurrency,
@DefaultAggregation:#SUM
@Semantics.amount.currencyCode: 'ReportingCurrency'
case
when TaxBox = 'C12'
then AmountInReportingCurrency * -1
else 0 end as TaxBox12AmtInReportingCurrency,
@DefaultAggregation:#SUM
@Semantics.amount.currencyCode: 'ReportingCurrency'
case
when TaxBox = 'C13'
then AmountInReportingCurrency * -1
else 0 end as TaxBox13AmtInReportingCurrency,
@DefaultAggregation:#SUM
@Semantics.amount.currencyCode: 'ReportingCurrency'
case
when TaxBox = 'C14'
then AmountInReportingCurrency * -1
else 0 end as TaxBox14AmtInReportingCurrency,
@DefaultAggregation:#SUM
@Semantics.amount.currencyCode: 'ReportingCurrency'
case
when TaxBox = 'C15'
then AmountInReportingCurrency * -1
else 0 end as TaxBox15AmtInReportingCurrency,
@DefaultAggregation:#SUM
@Semantics.amount.currencyCode: 'ReportingCurrency'
case
when TaxBox = 'C16'
then AmountInReportingCurrency * -1
else 0 end as TaxBox16AmtInReportingCurrency,
@DefaultAggregation:#SUM
@Semantics.amount.currencyCode: 'ReportingCurrency'
case
when TaxBox = 'C17'
then AmountInReportingCurrency * -1
else 0 end as TaxBox17AmtInReportingCurrency,
@DefaultAggregation:#SUM
@Semantics.amount.currencyCode: 'ReportingCurrency'
case
when TaxBox = 'C18'
then AmountInReportingCurrency * -1
else 0 end as TaxBox18AmtInReportingCurrency,
@DefaultAggregation: #SUM
@Semantics.amount.currencyCode: 'ReportingCurrency'
case
when TaxBox = 'C19'
then AmountInReportingCurrency * -1
else 0 end as TaxBox19AmtInReportingCurrency,
@DefaultAggregation: #SUM @Semantics.amount.currencyCode: 'ReportingCurrency'
case
when TaxBox = 'C20'
then AmountInReportingCurrency * -1
else 0 end as TaxBox20AmtInReportingCurrency,
@DefaultAggregation: #SUM @Semantics.amount.currencyCode: 'ReportingCurrency'
case
when TaxBox = 'C21'
then AmountInReportingCurrency * -1
else 0 end as TaxBox21AmtInReportingCurrency,
//TotalAmount
@DefaultAggregation: #SUM @Semantics.amount.currencyCode: 'ReportingCurrency'
case
when strp.TaxType = 'V' then AmountInReportingCurrency * -1
when strp.TaxType = 'A' then AmountInReportingCurrency * -1
end as TaxBox30AmtInReportingCurrency,
//TotalAmountDeferred
@DefaultAggregation:#SUM
@Semantics.amount.currencyCode: 'ReportingCurrency'
case
when TaxBox <> 'C07' and TaxBox <> 'C08' then
AmountInReportingCurrency
else
0
end as TaxBox31AmtInReportingCurrency,
//TotalPayment
@DefaultAggregation:#SUM
@Semantics.amount.currencyCode: 'ReportingCurrency'
case
when TaxBox = 'C07' or TaxBox = 'C08'
then AmountInReportingCurrency
else 0 end as TaxBox32AmtInReportingCurrency,
case
when TargetTaxCode is not initial
then concat(strp.CompanyCode, concat('/',concat(strp.AccountingDocument,concat('/',strp.FiscalYear))))
else concat(strp.CompanyCode, concat('/',concat(trdti.AccountingDocument,concat('/',trdti.FiscalYear))))
end as SortingNumber,
//NonEligibleBaseAmount
@DefaultAggregation:#SUM
@Semantics.amount.currencyCode: 'ReportingCurrency'
case
when TargetTaxCode <> '' and TaxDeclnAmountType = 'B' and CompanyCodeCountry = ReportingCountry
then AmountInReportingCurrency - coalesce(dti.PartialBaseAmtInLocalCrcy,0)
when TargetTaxCode <> '' and TaxDeclnAmountType = 'B'
then AmountInReportingCurrency - coalesce(dti.PartialBaseAmtInNationalCrcy,0)
else
0
end as TaxBox33AmtInReportingCurrency,
//NonEligibleVatAmount
@DefaultAggregation:#SUM
@Semantics.amount.currencyCode: 'ReportingCurrency'
case
when TargetTaxCode <> '' and TaxDeclnAmountType = 'T' and CompanyCodeCountry = ReportingCountry
then AmountInReportingCurrency - coalesce(dti.PartialTaxAmtInLocalCrcy,0)
when TargetTaxCode <> '' and TaxDeclnAmountType = 'T'
then AmountInReportingCurrency - coalesce(dti.PartialTaxAmtInNationalCrcy,0)
else
0
end as TaxBox34AmtInReportingCurrency
}
where
TaxBox <> ''
and ReportingCountry = 'RO'