@VDM.private: true
@VDM.viewType: #CONSUMPTION
@AbapCatalog.preserveKey:true
@AbapCatalog.sqlViewName: 'PJPCBPITEMS'
@AccessControl.authorizationCheck: #CHECK
@ClientHandling.type: #CLIENT_DEPENDENT
@ClientHandling.algorithm: #SESSION_VARIABLE
@EndUserText.label: 'View for Japan Central Bank Payment Detail Items'
@AbapCatalog.compiler.compareFilter:true
define view P_JP_CentralBkPaytWithItems
with parameters
@Consumption.hidden: true
P_StatryRptgEntity : srf_reporting_entity,
@Consumption.hidden: true
P_StatryRptCategory: srf_rep_cat_id,
@Consumption.hidden: true
P_StatryRptRunID : srf_report_run_id
as select from I_OperationalAcctgDocItem as AccItm
left outer join C_StRpRptdJournalEntry( P_StatryRptgEntity : $parameters.P_StatryRptgEntity,
P_StatryRptCategory: $parameters.P_StatryRptCategory ) as StRptLog
on AccItm.CompanyCode = StRptLog.CompanyCode
and AccItm.AccountingDocument = StRptLog.AccountingDocument
and AccItm.FiscalYear = StRptLog.FiscalYear
left outer join I_Customer_to_BusinessPartner as CustLink
on AccItm.Customer = CustLink.Customer
left outer join I_BusinessPartner as BusinessPartnerCustomer
on CustLink.BusinessPartnerUUID = BusinessPartnerCustomer.BusinessPartnerUUID
left outer join I_Supplier_to_BusinessPartner as SuplLink
on AccItm.Supplier = SuplLink.Supplier
left outer join I_BusinessPartner as BusinessPartnerSupplier
on SuplLink.BusinessPartnerUUID = BusinessPartnerSupplier.BusinessPartnerUUID
//but0is
left outer join I_BusinessPartnerIndustry as BPCustomer
on BusinessPartnerCustomer.BusinessPartner = BPCustomer.BusinessPartner
left outer join I_BusinessPartnerIndustry as BPSupplier
on BusinessPartnerSupplier.BusinessPartner = BPSupplier.BusinessPartner
left outer join I_AccountingDocument as Acchdr
on AccItm.CompanyCode = Acchdr.CompanyCode
and AccItm.AccountingDocument = Acchdr.AccountingDocument
and AccItm.FiscalYear = Acchdr.FiscalYear
association[1..1] to I_CalendarDate as _PostingCalendar
on Acchdr.PostingDate = _PostingCalendar.CalendarDate
association[1..1] to I_CalendarDate as _ClearingCalendar
on AccItm.ClearingDate = _ClearingCalendar.CalendarDate
association[0..1] to I_Customer as _Customer
on AccItm.Customer = _Customer.Customer
association[0..1] to I_Supplier as _Supplier
on AccItm.Supplier = _Supplier.Supplier
// Customer
association[0..1] to I_Industries as _IndustryCustomer
on BPCustomer.IndustrySector = _IndustryCustomer.IndustrySector
and BPCustomer.IndustrySystemType = _IndustryCustomer.IndustrySystemType
association[0..1] to I_Industrykeysystemtexts as _IndustrytextCustomer
on BPCustomer.IndustrySector = _IndustrytextCustomer.IndustrySector
and BPCustomer.IndustrySystemType = _IndustrytextCustomer.IndustrySystemType
and _IndustrytextCustomer.Language = $session.system_language
// Supplier
association[0..1] to I_Industries as _IndustrySupplier
on BPSupplier.IndustrySector = _IndustrySupplier.IndustrySector
and BPSupplier.IndustrySystemType = _IndustrySupplier.IndustrySystemType
association[0..1] to I_Industrykeysystemtexts as _IndustrytextSupplier
on BPSupplier.IndustrySector = _IndustrytextSupplier.IndustrySector
and BPSupplier.IndustrySystemType = _IndustrytextSupplier.IndustrySystemType
and _IndustrytextSupplier.Language = $session.system_language
// T015L
association[1..1] to I_StateCentralBankPaytRsn as _SCBR
on AccItm.StateCentralBankPaymentReason = _SCBR.StateCentralBankPaymentReason
//Payment Method Configuration Table
association[1..1] to I_JP_CentralBankReportStyle as _stylecfg
on AccItm.PaymentMethod = _stylecfg.PaymentMethod
{
key AccItm.CompanyCode,
key AccItm.FiscalYear,
key AccItm.AccountingDocument,
key AccItm.AccountingDocumentItem,
key _stylecfg.JP_CentralBkPaymentReportStyle,
key case
when ( _SCBR.StateCentralBankDocumentType = '5' and _SCBR.StateCentralBankServiceSpec = '012' )
or ( _SCBR.StateCentralBankDocumentType = '5' and _SCBR.StateCentralBankServiceSpec = '021' )
or ( _SCBR.StateCentralBankDocumentType = '5' and _SCBR.StateCentralBankServiceSpec = '031' )
or ( _SCBR.StateCentralBankDocumentType = '5' and _SCBR.StateCentralBankServiceSpec = '041' )
or ( _SCBR.StateCentralBankDocumentType = '5' and _SCBR.StateCentralBankServiceSpec = '068' )
or ( _SCBR.StateCentralBankDocumentType = '8' and _SCBR.StateCentralBankServiceSpec = '011' )
or ( _SCBR.StateCentralBankDocumentType = '8' and _SCBR.StateCentralBankServiceSpec = '012' )
or ( _SCBR.StateCentralBankDocumentType = '8' and _SCBR.StateCentralBankServiceSpec = '013' )
or ( _SCBR.StateCentralBankDocumentType = '8' and _SCBR.StateCentralBankServiceSpec = '015' )
or ( _SCBR.StateCentralBankDocumentType = '8' and _SCBR.StateCentralBankServiceSpec = '017' )
or ( _SCBR.StateCentralBankDocumentType = '8' and _SCBR.StateCentralBankServiceSpec = '020' )
or ( _SCBR.StateCentralBankDocumentType = '8' and _SCBR.StateCentralBankServiceSpec = '023' )
or ( _SCBR.StateCentralBankDocumentType = '9' and _SCBR.StateCentralBankServiceSpec = '011' )
or ( _SCBR.StateCentralBankDocumentType = '9' and _SCBR.StateCentralBankServiceSpec = '012' )
or ( _SCBR.StateCentralBankDocumentType = '9' and _SCBR.StateCentralBankServiceSpec = '013' )
or ( _SCBR.StateCentralBankDocumentType = '9' and _SCBR.StateCentralBankServiceSpec = '015' )
or ( _SCBR.StateCentralBankDocumentType = '9' and _SCBR.StateCentralBankServiceSpec = '017' )
or ( _SCBR.StateCentralBankDocumentType = '9' and _SCBR.StateCentralBankServiceSpec = '020' )
or ( _SCBR.StateCentralBankDocumentType = '9' and _SCBR.StateCentralBankServiceSpec = '023' )
then 'X'
else
''
end as CentralBankReportChart,
key case
when _Customer.Country <> '' then _Customer.Country
when _Supplier.Country <> '' then _Supplier.Country
end as CountryCode,
key concat( _SCBR.StateCentralBankDocumentType,right(_SCBR.StateCentralBankServiceSpec,2) ) as JP_StateCentralBankItemDesc,
key case
when _IndustryCustomer.IndustrySector is not null
then _IndustryCustomer.IndustrySector
when _IndustrySupplier.IndustrySector is not null
then _IndustrySupplier.IndustrySector
else
'0'
end as IndustrySector,
key cast(case // D: Customers; K: Vendors --
when AccItm.FinancialAccountType = 'D' then '0' // Receipt 0/R
when AccItm.FinancialAccountType = 'K' then '1' // Payment 1/P
end as scbr_payrec ) as FinancialAccountType,
// @Semantics.currencyCode: true
key cast('JPY' as fis_hwaer) as LocalCurrency,
AccItm.Customer,
AccItm.Supplier,
AccItm.PaymentMethod, // Payment Method(E/I/C)
AccItm.ClearingDate, // BSEG-AUGDT
@API.element.releaseState: #DEPRECATED
@API.element.successor: 'ClearingJournalEntry'
@VDM.lifecycle.status: #DEPRECATED
@VDM.lifecycle.successor: 'ClearingJournalEntry'
AccItm.ClearingAccountingDocument, // BSEG-AUGBL
@API.element.releaseState: #DEPRECATED
@API.element.successor: 'ClearingJournalEntryFiscalYear'
@VDM.lifecycle.status: #DEPRECATED
@VDM.lifecycle.successor: 'ClearingJournalEntryFiscalYear'
AccItm.ClearingDocFiscalYear, // BSEG-AUGGJ
AccItm.ClearingJournalEntry ,
AccItm.ClearingJournalEntryFiscalYear,
AccItm.ClearingItem, // BSEG-AGZEI
// Partial(Z) / Residual(V) Payment
// G Credit memo
// R Credit memo and Reversed
AccItm.FollowOnDocumentType, // BSEG-REBZT
AccItm.InvoiceReference, // BSEG-REBZG
AccItm.InvoiceItemReference, // BSEG-REBZZ
AccItm.InvoiceReferenceFiscalYear, // BSEG-REBZJ
Acchdr.PostingDate,
Acchdr.ReverseDocument, // BKPF-STBLG
concat( _PostingCalendar.CalendarYear, _PostingCalendar.CalendarMonth ) as PostingMonth,
case
when AccItm.ClearingDate = '00000000' then '000000'
else concat( _ClearingCalendar.CalendarYear,_ClearingCalendar.CalendarMonth )
end as ClearingMonth,
// Indicator: Is Posting Key Used in a Payment Transaction
AccItm.IsUsedInPaymentTransaction,
AccItm.StateCentralBankPaymentReason,
_SCBR.StateCentralBankPaytRsnDesc,
AccItm.CompanyCodeCurrency,
// AccItm.AmountInCompanyCodeCurrency,
case // D: Customers; K: Vendors --
when AccItm.FinancialAccountType = 'D' then -AccItm.AmountInCompanyCodeCurrency
when AccItm.FinancialAccountType = 'K' then AccItm.AmountInCompanyCodeCurrency
end as AmountInCompanyCodeCurrency,
//Amount In Company Code Currency
cast(
currency_conversion(
amount => AccItm.AmountInCompanyCodeCurrency,
source_currency => AccItm.CompanyCodeCurrency,
target_currency => cast('JPY' as waers),
exchange_rate_date => AccItm.ClearingDate,
exchange_rate_type => 'M',
error_handling => 'SET_TO_NULL',
// error_handling => 'FAIL_ON_ERROR',
round => #CDSBoolean.true,
decimal_shift => #CDSBoolean.true,
decimal_shift_back => #CDSBoolean.true
) as dmbtr ) * -100 as TransactionAmountInLocalCrcy,
AccItm.TransactionCurrency,
// AccItm.AmountInTransactionCurrency,
case // D: Customers; K: Vendors --
when AccItm.FinancialAccountType = 'D' then -AccItm.AmountInTransactionCurrency
when AccItm.FinancialAccountType = 'K' then AccItm.AmountInTransactionCurrency
end as AmountInTransactionCurrency
}
where ( AccItm.FinancialAccountType = 'D' or AccItm.FinancialAccountType = 'K' )
and AccItm._CompanyCode._Country.Country = 'JP'
/*+[internal] {
"BASEINFO":
{
"FROM":
[
"C_STRPRPTDJOURNALENTRY",
"I_ACCOUNTINGDOCUMENT",
"I_BUSINESSPARTNER",
"I_BUSINESSPARTNERINDUSTRY",
"I_CALENDARDATE",
"I_COMPANYCODE",
"I_COUNTRY",
"I_CUSTOMER",
"I_CUSTOMER_TO_BUSINESSPARTNER",
"I_INDUSTRIES",
"I_JP_CENTRALBANKREPORTSTYLE",
"I_OPERATIONALACCTGDOCITEM",
"I_STATECENTRALBANKPAYTRSN",
"I_SUPPLIER",
"I_SUPPLIER_TO_BUSINESSPARTNER"
],
"ASSOCIATED":
[
"I_CALENDARDATE",
"I_CUSTOMER",
"I_INDUSTRIES",
"I_INDUSTRYKEYSYSTEMTEXTS",
"I_JP_CENTRALBANKREPORTSTYLE",
"I_STATECENTRALBANKPAYTRSN",
"I_SUPPLIER"
],
"BASE":
[],
"ANNO_REF":
[],
"SCALAR_FUNCTION":
[],
"VERSION":0,
"ANNOREF_EVALUATION_ERROR":""
}
}*/