@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
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 )
)
and PH_BSET_GLO.AccountingDocument = SuplrCustJournalAmount.AccountingDocument
and PH_BSET_GLO.BusinessPlace = SuplrCustJournalAmount.BusinessPlace
/* 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_OneTimeAccountBP as OneTimeAccountBP on SuplrCustJournalAmount.AccountingDocument = OneTimeAccountBP.AccountingDocument
and SuplrCustJournalAmount.FiscalYear = OneTimeAccountBP.FiscalYear
and SuplrCustJournalAmount.CompanyCode = OneTimeAccountBP.CompanyCode
{
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.SupplierName )
else coalesce(concat_with_space(concat_with_space(concat_with_space(OneTimeAccountBP.OneTimeAccountBPSalutationText ,OneTimeAccountBP.BusinessPartnerName1 , 1) ,
OneTimeAccountBP.BusinessPartnerName2 , 1) ,
OneTimeAccountBP.BusinessPartnerName3 , 1) ,Customer.CustomerName )
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.BusinessPlace,
@DefaultAggregation: #SUM
@Semantics: { amount : {currencyCode: 'CompanyCodeCurrency'} }
case when SuplrCustJournalAmount.Creditor != '' //Supplier
then -SuplrCustJournalAmount.AmountInCompanyCodeCurrency
else SuplrCustJournalAmount.AmountInCompanyCodeCurrency
end as AmountInCompanyCodeCurrency,
@DefaultAggregation: #SUM
@Semantics: { amount : {currencyCode: 'CompanyCodeCurrency'} }
case when SuplrCustJournalAmount.Creditor != '' //Supplier
then -SuplrCustJournalAmount.CashDiscountAmtInCoCodeCrcy
else SuplrCustJournalAmount.CashDiscountAmtInCoCodeCrcy
end as CashDiscountAmtInCoCodeCrcy,
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
}
where
SuplrCustJournalAmount.FinancialAccountType = :P_FinancialAccountType
/*+[internal] {
"BASEINFO":
{
"FROM":
[
"I_ACCOUNTINGDOCUMENT",
"I_ADDRESS_2",
"I_CUSTOMER",
"I_ONETIMEACCOUNTBP",
"I_SUPPLIER",
"P_PH_SUPLRCUSTJOURNALAMOUNT",
"P_PH_SUPLRCUSTJRNLTAXAMOUNT"
],
"ASSOCIATED":
[],
"BASE":
[],
"ANNO_REF":
[],
"SCALAR_FUNCTION":
[],
"VERSION":0,
"ANNOREF_EVALUATION_ERROR":""
}
}*/