@VDM.lifecycle.status: #DEPRECATED
@VDM.lifecycle.successor: '<P_EG_StRpDocLineItemWthRefDoc>'
@AbapCatalog.sqlViewName: 'CSRLINEITMWTHREF'
@AbapCatalog.compiler.compareFilter: true
@AccessControl.authorizationCheck: #CHECK
@VDM.viewType: #CONSUMPTION
@ClientHandling.algorithm: #SESSION_VARIABLE
@ObjectModel.usageType.sizeCategory: #XL
@ObjectModel.usageType.serviceQuality: #D
@ObjectModel.usageType.dataClass: #MIXED
@AccessControl.personalData.blocking: #BLOCKED_DATA_INCLUDED
@AbapCatalog.preserveKey: true
@EndUserText.label: 'Tax Items with Reference Accounting Documents'
define view C_StRpDocLineItemWthRefDoc
with parameters
P_StatryRptgEntity : srf_reporting_entity,
P_StatryRptCategory : srf_rep_cat_id,
P_StatryRptRunID : srf_report_run_id
as select from C_StRpVATReturnDocLineItem(
P_StatryRptgEntity : $parameters.P_StatryRptgEntity,
P_StatryRptCategory : $parameters.P_StatryRptCategory,
P_StatryRptRunID : $parameters.P_StatryRptRunID
)
inner join I_TaxCode on I_TaxCode.TaxCode = C_StRpVATReturnDocLineItem.TaxCode
and I_TaxCode.TaxCalculationProcedure = C_StRpVATReturnDocLineItem.TaxCalculationProcedure
left outer join I_StRpBillingWithRefAcctgDoc as _Bill on _Bill.BillingDocument = C_StRpVATReturnDocLineItem.BillingDocument
left outer join I_JournalEntry as _journalentry on C_StRpVATReturnDocLineItem.CompanyCode = _journalentry.CompanyCode
and C_StRpVATReturnDocLineItem.ReferenceDocumentFiscalYear = _journalentry.FiscalYear
and C_StRpVATReturnDocLineItem.ReferenceDocumentNumber = _journalentry.AccountingDocument
left outer join I_JournalEntryItemOneTimeData as _OneTimeAccountBP on C_StRpVATReturnDocLineItem.CompanyCode = _OneTimeAccountBP.CompanyCode
and C_StRpVATReturnDocLineItem.FiscalYear = _OneTimeAccountBP.FiscalYear
and C_StRpVATReturnDocLineItem.AccountingDocument = _OneTimeAccountBP.AccountingDocument
and C_StRpVATReturnDocLineItem.AccountingDocumentItem = _OneTimeAccountBP.AccountingDocumentItem
association [0..1] to I_Supplier as _Supplier on $projection.Supplier = _Supplier.Supplier
association [0..1] to I_Customer as _Customer on $projection.Customer = _Customer.Customer
association [0..1] to I_Product as _Product on $projection.Material = _Product.Product
association [0..*] to I_ProductDescription as _ProductText on $projection.Material = _ProductText.Product
{
key C_StRpVATReturnDocLineItem.StatryRptgEntity,
key C_StRpVATReturnDocLineItem.StatryRptCategory,
key C_StRpVATReturnDocLineItem.StatryRptRunID,
key C_StRpVATReturnDocLineItem.CompanyCode,
key C_StRpVATReturnDocLineItem.FiscalYear,
key C_StRpVATReturnDocLineItem.AccountingDocument,
key C_StRpVATReturnDocLineItem.AccountingDocumentItem,
key C_StRpVATReturnDocLineItem.DocumentItemNumber,
SupplierInvoice,
SupplierInvoiceItem,
C_StRpVATReturnDocLineItem.BillingDocument,
C_StRpVATReturnDocLineItem.BillingDocumentItem,
C_StRpVATReturnDocLineItem.TaxCode,
I_TaxCode.TaxType,
TaxItemGroup,
C_StRpVATReturnDocLineItem.AccountingDocumentType,
AccountingDocumentItemType,
C_StRpVATReturnDocLineItem.ReferenceDocumentType,
TransactionTypeDetermination,
PostingKey,
// DebitCreditCode,
case
when C_StRpVATReturnDocLineItem.DebitCreditCodeIsChanged <> '' and C_StRpVATReturnDocLineItem.DebitCreditCode = 'H'
then 'S'
when C_StRpVATReturnDocLineItem.DebitCreditCodeIsChanged <> '' and C_StRpVATReturnDocLineItem.DebitCreditCode = 'S'
then 'H'
else C_StRpVATReturnDocLineItem.DebitCreditCode
end as DebitCreditCode,
DebitCreditCodeIsChanged,
GLAccount,
FinancialAccountType,
AccountType,
C_StRpVATReturnDocLineItem.FiscalPeriod,
ReportingDate,
C_StRpVATReturnDocLineItem.PostingDate,
C_StRpVATReturnDocLineItem.DocumentDate,
C_StRpVATReturnDocLineItem.DocumentCurrency,
C_StRpVATReturnDocLineItem.CompanyCodeCurrency,
C_StRpVATReturnDocLineItem.ReportingCurrency,
C_StRpVATReturnDocLineItem.CountryCurrency,
C_StRpVATReturnDocLineItem.AdditionalCurrency1,
C_StRpVATReturnDocLineItem.AdditionalCurrency2,
Material,
DocumentItemText,
@Semantics.quantity.unitOfMeasure: 'QuantityUnit'
QuantityInActlPostg,
@Semantics.unitOfMeasure: true
QuantityUnit,
TaxRate,
C_StRpVATReturnDocLineItem.OriginalReferenceDocument,
BusinessArea,
Supplier,
Customer,
_Supplier.SupplierAccountGroup,
ReportingCountry,
CompanyCodeCountry,
coalesce( _OneTimeAccountBP.TaxID1, coalesce(_Customer.TaxNumber1, _Supplier.TaxNumber1)) as TaxNumber1,
// case when FinancialAccountType = 'D'
// then coalesce( _OneTimeAccountBP.TaxID1 , _Customer.TaxNumber1)
// when FinancialAccountType = 'K'
// then coalesce( _OneTimeAccountBP.TaxID1 , _Supplier.TaxNumber1)
// end as TaxNumber1,
coalesce( _OneTimeAccountBP.TaxID2, coalesce(_Customer.TaxNumber2, _Supplier.TaxNumber2)) as TaxNumber2,
// case when FinancialAccountType = 'D'
// then coalesce( _OneTimeAccountBP.TaxID2 , _Customer.TaxNumber2)
// when FinancialAccountType = 'K'
// then coalesce( _OneTimeAccountBP.TaxID2 , _Supplier.TaxNumber2)
// end as TaxNumber2,
coalesce( _OneTimeAccountBP.TaxID3, coalesce(_Customer.TaxNumber3, _Supplier.TaxNumber3)) as TaxNumber3,
// case when FinancialAccountType = 'D'
// then coalesce( _OneTimeAccountBP.TaxID3 , _Customer.TaxNumber3)
// when FinancialAccountType = 'K'
// then coalesce( _OneTimeAccountBP.TaxID3 , _Supplier.TaxNumber3)
// end as TaxNumber3,
// coalesce( _OneTimeAccountBP.BusinessPartnerName1, coalesce(_Customer.CustomerName, _Supplier.SupplierName)) as BusinessPartnerName,
coalesce( _OneTimeAccountBP.BusinessPartnerName1,
coalesce( coalesce( _Customer[1:Language = $session.system_language].BPCustomerName, _Customer[1:Language = 'E'].BPCustomerName ),
coalesce( _Supplier[1:SupplierLanguage = $session.system_language].BPSupplierName, _Supplier[1:SupplierLanguage = 'E'].BPSupplierName )
) ) as BusinessPartnerName,
// case when FinancialAccountType = 'D'
// then coalesce( _OneTimeAccountBP.BusinessPartnerName1 , _Customer.CustomerName)
// when FinancialAccountType = 'K'
// then coalesce( _OneTimeAccountBP.BusinessPartnerName1 , _Supplier.SupplierName)
// end as BusinessPartnerName,
coalesce(_Customer._AddressRepresentation.HouseNumber, _Supplier._AddressRepresentation.HouseNumber) as HouseNumber,
coalesce( _OneTimeAccountBP.StreetAddressName, coalesce(_Customer._AddressRepresentation.StreetName, _Supplier._AddressRepresentation.StreetName)) as StreetAddressName,
coalesce( _OneTimeAccountBP.CityName, coalesce(_Customer._AddressRepresentation.CityName, _Supplier._AddressRepresentation.CityName)) as CityName,
coalesce( _OneTimeAccountBP.Country, coalesce(_Customer._AddressRepresentation.Country,_Supplier._AddressRepresentation.Country )) as BusinessPartnerCountry,
coalesce( _OneTimeAccountBP.PostalCode, coalesce(_Customer._AddressRepresentation.PostalCode,_Supplier._AddressRepresentation.PostalCode )) as PostalCode,
case when FinancialAccountType = 'D'
then coalesce(concat_with_space(concat_with_space( _OneTimeAccountBP.StreetAddressName,
_OneTimeAccountBP.CityName, 1),concat_with_space( _OneTimeAccountBP.Country, _OneTimeAccountBP.PostalCode, 1), 1),
concat_with_space( _Customer._AddressRepresentation.HouseNumber,concat_with_space(concat_with_space( _Customer._AddressRepresentation.StreetName, _Customer._AddressRepresentation.CityName, 1),
concat_with_space( _Customer._AddressRepresentation.Country, _Customer._AddressRepresentation.PostalCode, 1), 1), 1))
when FinancialAccountType = 'K'
then coalesce(concat_with_space(concat_with_space( _OneTimeAccountBP.StreetAddressName,
_OneTimeAccountBP.CityName, 1),concat_with_space( _OneTimeAccountBP.Country, _OneTimeAccountBP.PostalCode, 1), 1),
concat_with_space( _Supplier._AddressRepresentation.HouseNumber,concat_with_space(concat_with_space( _Supplier._AddressRepresentation.StreetName, _Supplier._AddressRepresentation.CityName, 1),
concat_with_space( _Supplier._AddressRepresentation.Country, _Supplier._AddressRepresentation.PostalCode, 1), 1), 1))
end as CustomerSupplierAddress,
// case when FinancialAccountType = 'D'
// then coalesce( _OneTimeAccountBP.Country , _Customer.Country)
// when FinancialAccountType = 'K'
// then coalesce( _OneTimeAccountBP.Country , _Supplier.Country)
// end as BusinessPartnerCountry,
coalesce(_Customer._CustomerToBusinessPartner._BusinessPartner.BusinessPartner,
_Supplier._SupplierToBusinessPartner._BusinessPartner.BusinessPartner ) as BusinessPartner,
// case when FinancialAccountType = 'D'
// then _Customer._CustomerToBusinessPartner._BusinessPartner.BusinessPartner
// when FinancialAccountType = 'K'
// then _Supplier._SupplierToBusinessPartner._BusinessPartner.BusinessPartner
// end as BusinessPartner,
coalesce(_Customer._CustomerToBusinessPartner._BusinessPartner.LegalEntityOfOrganization,
coalesce(_Supplier._SupplierToBusinessPartner._BusinessPartner.LegalEntityOfOrganization, '' ) ) as LegalEntityOfOrganization,
// case when FinancialAccountType = 'D'
// then _Customer._CustomerToBusinessPartner._BusinessPartner.LegalForm
// when FinancialAccountType = 'K'
// then _Supplier._SupplierToBusinessPartner._BusinessPartner.LegalForm
// end as LegalForm,
coalesce( _OneTimeAccountBP.IsNaturalPerson , coalesce(_Customer.NFPartnerIsNaturalPerson,_Supplier.IsNaturalPerson )) as IsNaturalPerson,
// case when FinancialAccountType = 'D'
// then coalesce( _OneTimeAccountBP.IsNaturalPerson , _Customer.NFPartnerIsNaturalPerson)
// when FinancialAccountType = 'K'
// then coalesce( _OneTimeAccountBP.IsNaturalPerson , _Supplier.IsNaturalPerson)
// end as IsNaturalPerson,
C_StRpVATReturnDocLineItem.ReverseDocument,
C_StRpVATReturnDocLineItem.ReverseDocumentFiscalYear,
InvoiceReference,
InvoiceReferenceFiscalYear,
ReferenceDocumentNumber,
ReferenceDocumentFiscalYear,
DistributedTaxAmountInCCrcy,
DistrdTaxBaseAmountInCCrcy,
DistributedTaxAmountInTC,
DistrdTaxBaseAmountInTransCrcy,
DistributedTaxAmtInCountryCrcy,
DistrdTaxBaseAmountInCntryCrcy,
DistrdTaxAmountInAddlCrcy1,
DistrdTaxBaseAmountInAddlCrcy1,
DistrdTaxAmountInAddlCrcy2,
DistrdTaxBaseAmountInAddlCrcy2,
case when ( _journalentry.AccountingDocument = '' and _Bill.AccountingDocument = '')
then ''
when _journalentry.AccountingDocument = ''
then _Bill.AccountingDocument
else _journalentry.AccountingDocument
end as RefAccountingDocument,
// case when _journalentry.AccountingDocument <> ''
// then _journalentry.AccountingDocument
// else
// case when _Bill.AccountingDocument <> ''
// then _Bill.AccountingDocument
// else ''
// end
// end as RefAccountingDocument,
case when ( (_journalentry.PostingDate = '' or _journalentry.PostingDate = '00000000' or _journalentry.PostingDate is null ) and
(_Bill.PostingDate = '' or _Bill.PostingDate = '00000000' or _Bill.PostingDate is null ) )
then C_StRpVATReturnDocLineItem.ReportingDate
when ( _journalentry.PostingDate = '' or _journalentry.PostingDate = '00000000' or _journalentry.PostingDate is null )
then _Bill.PostingDate
else _journalentry.PostingDate
end as RefAccountingDocumentPostgDate,
// case when _journalentry.PostingDate <> ''
// then _journalentry.PostingDate
// else
// case when _Bill.PostingDate <> ''
// then _Bill.PostingDate
// else C_StRpVATReturnDocLineItem.ReportingDate
// end
// end as RefAccountingDocumentPostgDate,
C_StRpVATReturnDocLineItem.CashDiscountBaseAmount,
C_StRpVATReturnDocLineItem.CashDiscountAmount,
C_StRpVATReturnDocLineItem.TaxRateValidityStartDate,
C_StRpVATReturnDocLineItem.DocumentReferenceID,
_Supplier,
_Customer,
_Product,
_ProductText
}