@AbapCatalog.sqlViewName: 'IPHSCJRNLCUBE'
@AbapCatalog.compiler.compareFilter: true
@AccessControl.authorizationCheck: #CHECK
@Analytics: { dataCategory: #CUBE }
@ClientHandling.algorithm: #SESSION_VARIABLE
@ObjectModel.usageType.sizeCategory: #XXL
@ObjectModel.usageType.dataClass: #MIXED
@ObjectModel.usageType.serviceQuality: #D
@VDM.viewType: #COMPOSITE
@AccessControl.personalData.blocking: #BLOCKED_DATA_INCLUDED
@EndUserText.label: 'PH CAS Supplier Customer Journal Cube'
@Metadata.allowExtensions:true
@Consumption.dbHints: ['USE_HEX_PLAN']
define view I_PH_SuplrCustJournalCube
with parameters
@EndUserText.label: 'Company Code'
P_CompanyCode : fis_bukrs,
@EndUserText.label: 'Financial Account Type'
P_FinancialAccountType : koart
as select from P_PH_SuplrCustJournalAmount(P_CompanyCode: :P_CompanyCode) as SuplrCustJournalAmount
left outer join I_Supplier as Supplier on Supplier.Supplier = SuplrCustJournalAmount.Creditor
and SuplrCustJournalAmount.Creditor != ''
left outer join I_Customer as Customer on Customer.Customer = SuplrCustJournalAmount.Debtor
and SuplrCustJournalAmount.Debtor != ''
left outer join I_AccountingDocument as AccountingDocument on AccountingDocument.CompanyCode = SuplrCustJournalAmount.CompanyCode
and AccountingDocument.FiscalYear = SuplrCustJournalAmount.FiscalYear
and AccountingDocument.AccountingDocument = SuplrCustJournalAmount.AccountingDocument
left outer join P_PH_SuplrCustJrnlTaxAmount as PH_BSET_GLO on PH_BSET_GLO.CompanyCode = SuplrCustJournalAmount.CompanyCode
and PH_BSET_GLO.FiscalYear = SuplrCustJournalAmount.FiscalYear
and (
(
SuplrCustJournalAmount.Creditor != ''
and PH_BSET_GLO.Supplier = SuplrCustJournalAmount.Creditor
)
or(
SuplrCustJournalAmount.Debtor != ''
and PH_BSET_GLO.Customer = SuplrCustJournalAmount.Debtor
)
or(
PH_BSET_GLO.Customer = ''
and PH_BSET_GLO.Supplier = ''
)
)
and PH_BSET_GLO.AccountingDocument = SuplrCustJournalAmount.AccountingDocument
and PH_BSET_GLO.BusinessPlace = SuplrCustJournalAmount.BusinessPlace
left outer join P_PH_JrnlWhldgTaxAmount as WhldgTaxItem on WhldgTaxItem.CompanyCode = SuplrCustJournalAmount.CompanyCode
and WhldgTaxItem.AccountingDocument = SuplrCustJournalAmount.AccountingDocument
and WhldgTaxItem.FiscalYear = SuplrCustJournalAmount.FiscalYear
and WhldgTaxItem.Supplier = SuplrCustJournalAmount.Creditor
and WhldgTaxItem.Customer = SuplrCustJournalAmount.Debtor
and WhldgTaxItem.WhgdTaxCalculationTimePoint = '1'
/* fetch one time bp information, not support multiple one time bp or mix normal bp and one time bp in one document */
left outer to one join I_JournalEntryItemOneTimeData as OneTimeAccountBP on SuplrCustJournalAmount.AccountingDocument = OneTimeAccountBP.AccountingDocument
and SuplrCustJournalAmount.FiscalYear = OneTimeAccountBP.FiscalYear
and SuplrCustJournalAmount.CompanyCode = OneTimeAccountBP.CompanyCode
and SuplrCustJournalAmount.AccountingDocumentItem = OneTimeAccountBP.AccountingDocumentItem
{
key SuplrCustJournalAmount.CompanyCode,
key SuplrCustJournalAmount.AccountingDocument,
key SuplrCustJournalAmount.FiscalYear,
key SuplrCustJournalAmount.Creditor as Supplier,
key SuplrCustJournalAmount.Debtor as Customer,
SuplrCustJournalAmount.FiscalPeriod,
case when SuplrCustJournalAmount.Creditor != ''
then coalesce(concat_with_space(concat_with_space(concat_with_space(OneTimeAccountBP.OneTimeAccountBPSalutationText ,OneTimeAccountBP.BusinessPartnerName1 , 1) ,
OneTimeAccountBP.BusinessPartnerName2 , 1) ,
OneTimeAccountBP.BusinessPartnerName3 , 1) ,Supplier.BPSupplierName )
else coalesce(concat_with_space(concat_with_space(concat_with_space(OneTimeAccountBP.OneTimeAccountBPSalutationText ,OneTimeAccountBP.BusinessPartnerName1 , 1) ,
OneTimeAccountBP.BusinessPartnerName2 , 1) ,
OneTimeAccountBP.BusinessPartnerName3 , 1) ,Customer.BPCustomerName )
end as BusinessPartnerName,
cast(case when SuplrCustJournalAmount.Creditor != ''
then coalesce(OneTimeAccountBP.Country,Supplier.Country)
else coalesce(OneTimeAccountBP.Country,Customer.Country)
end as bu_partner_country) as BusinessPartnerCountry,
cast(case when SuplrCustJournalAmount.Creditor != ''
then concat_with_space(Supplier._AddressRepresentation.HouseNumber,
concat_with_space(Supplier._AddressRepresentation.StreetName,
concat_with_space(Supplier._AddressRepresentation.CityName,
concat_with_space(Supplier._AddressRepresentation.Country,
Supplier._AddressRepresentation.PostalCode,
1),
1),
1),
1)
else concat_with_space(Customer._AddressRepresentation.HouseNumber,
concat_with_space(Customer._AddressRepresentation.StreetName,
concat_with_space(Customer._AddressRepresentation.CityName,
concat_with_space(Customer._AddressRepresentation.Country,
Customer._AddressRepresentation.PostalCode,
1),
1),
1),
1) end as business_partner_address) as CustomerSupplierAddress,
//House Number+Street 2+Street 3+Street+Street 4+Street 5, City,+Country,+Postal Code
case when SuplrCustJournalAmount.Creditor != ''
then cast(coalesce(concat(concat(concat(concat(OneTimeAccountBP.StreetAddressName, ','), concat(OneTimeAccountBP.CityName, ',')), concat(OneTimeAccountBP.Country, ',')), OneTimeAccountBP.PostalCode),
concat(concat(concat(concat(concat_with_space(concat_with_space(concat_with_space(concat_with_space(concat_with_space(Supplier._AddressRepresentation.HouseNumber, Supplier._AddressRepresentation.StreetPrefixName1, 1), Supplier._AddressRepresentation.StreetPrefixName2, 1), Supplier._AddressRepresentation.StreetName, 1), Supplier._AddressRepresentation.StreetSuffixName1, 1), Supplier._AddressRepresentation.StreetSuffixName2, 1), ','), concat(Supplier._AddressRepresentation.CityName, ',')), concat(Supplier._AddressRepresentation.Country, ',')),Supplier._AddressRepresentation.PostalCode)) as fiph_addr)
else cast(coalesce(concat(concat(concat(concat(OneTimeAccountBP.StreetAddressName, ','), concat(OneTimeAccountBP.CityName, ',')), concat(OneTimeAccountBP.Country, ',')), OneTimeAccountBP.PostalCode),
concat(concat(concat(concat(concat_with_space(concat_with_space(concat_with_space(concat_with_space(concat_with_space(Customer._AddressRepresentation.HouseNumber, Customer._AddressRepresentation.StreetPrefixName1, 1), Customer._AddressRepresentation.StreetPrefixName2, 1), Customer._AddressRepresentation.StreetName, 1), Customer._AddressRepresentation.StreetSuffixName1, 1), Customer._AddressRepresentation.StreetSuffixName2, 1), ','), concat(Customer._AddressRepresentation.CityName, ',')), concat(Customer._AddressRepresentation.Country, ',')),Customer._AddressRepresentation.PostalCode)) as fiph_addr)
end as AddressID,
case when SuplrCustJournalAmount.Creditor != ''
then case when OneTimeAccountBP.TaxID3 != '' or Supplier.TaxNumber3 !=''
then coalesce(OneTimeAccountBP.TaxID3, Supplier.TaxNumber3)
else coalesce(OneTimeAccountBP.TaxID1, Supplier.TaxNumber1)
end
else case when OneTimeAccountBP.TaxID3 != '' or Customer.TaxNumber3 !=''
then coalesce(OneTimeAccountBP.TaxID3, Customer.TaxNumber3)
else coalesce(OneTimeAccountBP.TaxID1, Customer.TaxNumber1)
end
end as VATRegistration,
case when SuplrCustJournalAmount.DocumentItemText = ''
then SuplrCustJournalAmount.AccountingDocumentHeaderText
else SuplrCustJournalAmount.DocumentItemText
end as AccountingDocumentHeaderText,
// case when SuplrCustJournalAmount.Creditor = ''
// then SuplrCustJournalAmount.SalesOrganization
// else SuplrCustJournalAmount.PurchasingOrganization
// end as SalesOrganization,
SuplrCustJournalAmount.AccountingDocumentType,
SuplrCustJournalAmount.DocumentDate,
SuplrCustJournalAmount.PostingDate,
SuplrCustJournalAmount.FinancialAccountType,
case when AccountingDocument.DocumentReferenceID = '0000000000000000'
then ''
else AccountingDocument.DocumentReferenceID
end as DocumentReferenceID,
left(AccountingDocument.OriginalReferenceDocument,10) as OriginalReferenceDocument,
PH_BSET_GLO.TaxCode as TaxCode,
PH_BSET_GLO.TaxRate,
PH_BSET_GLO.TaxRateValidityStartDate,
SuplrCustJournalAmount.CompanyCodeCurrency,
SuplrCustJournalAmount.TransactionCurrency,
SuplrCustJournalAmount.BusinessPlace,
@DefaultAggregation: #SUM
@Semantics: { amount : {currencyCode: 'CompanyCodeCurrency'} }
case when SuplrCustJournalAmount.Creditor != '' //Supplier
then
case when WhldgTaxItem.WhgdTaxCalculationTimePoint ='1' //Invoice
then -(SuplrCustJournalAmount.AmountInCompanyCodeCurrency + WhldgTaxItem.WhldgTaxAmtInCoCodeCrcy)
else -SuplrCustJournalAmount.AmountInCompanyCodeCurrency
end
else SuplrCustJournalAmount.AmountInCompanyCodeCurrency
end as AmountInCompanyCodeCurrency,
@DefaultAggregation: #SUM
@Semantics: { amount : {currencyCode: 'TransactionCurrency'} }
case when SuplrCustJournalAmount.Creditor != '' //Supplier
then
case when WhldgTaxItem.WhgdTaxCalculationTimePoint ='1' //Invoice
then -(SuplrCustJournalAmount.AmountInTransactionCurrency + WhldgTaxItem.WhldgTaxAmtInTransacCrcy)
else -SuplrCustJournalAmount.AmountInTransactionCurrency
end
else SuplrCustJournalAmount.AmountInTransactionCurrency
end as AmountInTransactionCurrency,
@DefaultAggregation: #SUM
@Semantics: { amount : {currencyCode: 'CompanyCodeCurrency'} }
case when SuplrCustJournalAmount.Creditor != '' //Supplier
then -SuplrCustJournalAmount.CashDiscountAmtInCoCodeCrcy
else SuplrCustJournalAmount.CashDiscountAmtInCoCodeCrcy
end as CashDiscountAmtInCoCodeCrcy,
@DefaultAggregation: #SUM
@Semantics: { amount : {currencyCode: 'TransactionCurrency'} }
case when SuplrCustJournalAmount.Creditor != '' //Supplier
then -SuplrCustJournalAmount.CashDiscountAmount
else SuplrCustJournalAmount.CashDiscountAmount
end as CashDiscountAmount,
case when PH_BSET_GLO.CountryCurrency is null
then SuplrCustJournalAmount.CompanyCodeCurrency
else PH_BSET_GLO.CountryCurrency
end as CountryCurrency,
@DefaultAggregation: #SUM
@Semantics: { amount : {currencyCode: 'CountryCurrency'} }
case when PH_BSET_GLO.CountryCurrency is null
then
case when SuplrCustJournalAmount.Debtor != '' //Customer
then -PH_BSET_GLO.TaxBaseAmountInCoCodeCrcy
else PH_BSET_GLO.TaxBaseAmountInCoCodeCrcy
end
else
case when SuplrCustJournalAmount.Debtor != ''
then -PH_BSET_GLO.TaxBaseAmountInCountryCrcy
else PH_BSET_GLO.TaxBaseAmountInCountryCrcy
end
end as TaxBaseAmountInCountryCrcy,
@DefaultAggregation: #SUM
@Semantics: { amount : {currencyCode: 'CountryCurrency'} }
case when PH_BSET_GLO.CountryCurrency is null
then
case when SuplrCustJournalAmount.Debtor != '' //Customer
then -PH_BSET_GLO.TaxAmountInCoCodeCrcy
else PH_BSET_GLO.TaxAmountInCoCodeCrcy
end
else
case when SuplrCustJournalAmount.Debtor != ''
then -PH_BSET_GLO.TaxAmountInCountryCrcy
else PH_BSET_GLO.TaxAmountInCountryCrcy
end
end as TaxAmountInCountryCrcy,
@DefaultAggregation: #SUM
@Semantics: { amount : {currencyCode: 'CompanyCodeCurrency'} }
case when SuplrCustJournalAmount.Debtor != ''
then -PH_BSET_GLO.TaxBaseAmountInCoCodeCrcy
else PH_BSET_GLO.TaxBaseAmountInCoCodeCrcy
end as TaxBaseAmountInCoCodeCrcy,
@DefaultAggregation: #SUM
@Semantics: { amount : {currencyCode: 'CompanyCodeCurrency'} }
case when SuplrCustJournalAmount.Debtor != ''
then -PH_BSET_GLO.TaxAmountInCoCodeCrcy
else PH_BSET_GLO.TaxAmountInCoCodeCrcy
end as TaxAmountInCoCodeCrcy,
@DefaultAggregation: #SUM
@Semantics: { amount : {currencyCode: 'TransactionCurrency'} }
case when SuplrCustJournalAmount.Debtor != ''
then -PH_BSET_GLO.TaxBaseAmountInTransCrcy
else PH_BSET_GLO.TaxBaseAmountInTransCrcy
end as TaxBaseAmountInTransCrcy,
@DefaultAggregation: #SUM
@Semantics: { amount : {currencyCode: 'TransactionCurrency'} }
case when SuplrCustJournalAmount.Debtor != ''
then -PH_BSET_GLO.TaxAmountInTransCrcy
else PH_BSET_GLO.TaxAmountInTransCrcy
end as TaxAmountInTransCrcy,
cast(case
when AccountingDocument.ExchangeRate = 0
then 1
else AccountingDocument.ExchangeRate
end as fis_absolute_exchangerate) as ExchangeRate,
@DefaultAggregation: #SUM
@Semantics: { amount : {currencyCode: 'TransactionCurrency'} }
case when SuplrCustJournalAmount.Debtor != ''
then -( PH_BSET_GLO.TaxBaseAmountInTransCrcy + PH_BSET_GLO.TaxAmountInTransCrcy )
else ( PH_BSET_GLO.TaxBaseAmountInTransCrcy + PH_BSET_GLO.TaxAmountInTransCrcy )
end as TotalAmountInTransactionCrcy,
@DefaultAggregation: #SUM
@Semantics: { amount : {currencyCode: 'CompanyCodeCurrency'} }
case when SuplrCustJournalAmount.Debtor != ''
then -( PH_BSET_GLO.TaxBaseAmountInCoCodeCrcy + PH_BSET_GLO.TaxAmountInCoCodeCrcy )
else ( PH_BSET_GLO.TaxBaseAmountInCoCodeCrcy + PH_BSET_GLO.TaxAmountInCoCodeCrcy )
end as TotalAmountInCoCodeCrcy
}
where
SuplrCustJournalAmount.FinancialAccountType = :P_FinancialAccountType