@AbapCatalog.sqlViewName: 'PTRANSOPENBUSVOL'
@AbapCatalog.compiler.compareFilter: true
@AbapCatalog.preserveKey: true
@VDM.private: true
@VDM.viewType: #COMPOSITE
@AccessControl.authorizationCheck: #NOT_REQUIRED
@ObjectModel.usageType.dataClass: #MIXED
@ObjectModel.usageType.serviceQuality: #D
@ObjectModel.usageType.sizeCategory: #L
@ClientHandling.algorithm: #SESSION_VARIABLE
define view P_FinTransOpenFlowBusVol
with parameters
@Environment.systemField: #SYSTEM_DATE
P_StartDate : vdm_v_start_date,
@Environment.systemField: #SYSTEM_DATE
P_EndDate : vdm_v_end_date,
P_FinTransAmtRptgFrequency : ftr_date_granularity
as select from P_FinTransBusVolKeyDate(P_StartDate : $parameters.P_StartDate,
P_EndDate : $parameters.P_EndDate,
P_FinTransAmtRptgFrequency : $parameters.P_FinTransAmtRptgFrequency) as CalendarDate
inner join I_FinTransFlow as Flow on (Flow.FinTransFlowPaymentDate <= CalendarDate.KeyDate
// for FX(600) situation
or (Flow.TreasuryContractType = '4'))
and Flow.FinTransFlowPostingStatus <> '3'
and Flow.FinTransFlowPostingStatus <> '4'
inner join I_FinTransActivity as Activity on Flow.CompanyCode = Activity.CompanyCode
and Flow.FinancialTransaction = Activity.FinancialTransaction
and(Activity.TermEndDate >= CalendarDate.KeyDate or Activity.TermEndDate is initial)
//and(Activity.FinTransNoticeDate >= CalendarDate.KeyDate or Activity.FinTransNoticeDate is initial)
inner join I_FinancialTransaction as FinancialTransaction on FinancialTransaction.TermStartDate <= CalendarDate.KeyDate
//and(FinancialTransaction.TermEndDate >= CalendarDate.KeyDate or FinancialTransaction.TermEndDate is initial)
and Activity.CompanyCode = FinancialTransaction.CompanyCode
and Activity.FinancialTransaction = FinancialTransaction.FinancialTransaction
and Activity.FinancialInstrumentActivity= FinancialTransaction.FinInstrLastActiveActivity
{
key Flow.CompanyCode,
key Flow.FinancialTransaction,
key Flow.FinancialInstrumentActivity,
key CalendarDate.KeyDate,
key Flow.FinTransFlowCreationDate,
key Flow.FinTransFlowCreationTime,
key Flow.FinTransFlowNumber,
Flow.FinancialTransactionDirection,
//On termEndDate, set the amount to zero except securities
case when( KeyDate = Activity.TermEndDate
and FinancialTransaction.FinancialInstrProductCategory <> '010'
and FinancialTransaction.FinancialInstrProductCategory <> '020'
and FinancialTransaction.FinancialInstrProductCategory <> '030'
and FinancialTransaction.FinancialInstrProductCategory <> '040'
and FinancialTransaction.FinancialInstrProductCategory <> '042')
then 0
// On other key dates, for MM and FX and IRD,
// need to calculate different directions and use FinTransFlowNomAmt
else case when( FinancialTransaction.FinancialInstrProductCategory = '510'
or FinancialTransaction.FinancialInstrProductCategory = '520'
or FinancialTransaction.FinancialInstrProductCategory = '530'
or FinancialTransaction.FinancialInstrProductCategory = '540'
or FinancialTransaction.FinancialInstrProductCategory = '550'
or FinancialTransaction.FinancialInstrProductCategory = '580'
or FinancialTransaction.FinancialInstrProductCategory = '610'
or FinancialTransaction.FinancialInstrProductCategory = '620'
or FinancialTransaction.FinancialInstrProductCategory = '630')
then case when Flow.FinTransFlowPaytAmtDirection = '+'
then Flow.FinTransFlowNomAmt
else Flow.FinTransFlowNomAmt * -1
end
// for other productGroup, uset FinTransFlowPayAmt
else Flow.FinTransFlowPaytAmt
end
end as AmountInTransactionCurrency,
Flow.FinTransFlowNomAmt,
Flow.FinTransFlowNomAmtCrcy,
Flow.FinTransFlowPaytAmt,
@Semantics.currencyCode: true
cast(case when( FinancialTransaction.FinancialInstrProductCategory = '510'
or FinancialTransaction.FinancialInstrProductCategory = '520'
or FinancialTransaction.FinancialInstrProductCategory = '530'
or FinancialTransaction.FinancialInstrProductCategory = '540'
or FinancialTransaction.FinancialInstrProductCategory = '550'
or FinancialTransaction.FinancialInstrProductCategory = '580'
or FinancialTransaction.FinancialInstrProductCategory = '610'
or FinancialTransaction.FinancialInstrProductCategory = '620'
or FinancialTransaction.FinancialInstrProductCategory = '630')
then Flow.FinTransFlowNomAmtCrcy
else Flow.FinTransFlowPaytAmtCrcy
end as ftr_gen_trans_currency
) as TransactionCurrency,
Flow.FinTransFlowPaytAmtCrcy,
Flow.FinTransFlowPaytAmtDirection,
Flow.FinTransFlowCategory,
FinancialTransaction.FinancialInstrProductCategory,
Activity.FinancialInstrActivityCategory,
FinancialTransaction.Counterparty,
Flow.FinTransFlowPaymentDate,
cast(case when( FinancialTransaction.FinancialInstrProductCategory = '510'
or FinancialTransaction.FinancialInstrProductCategory = '520'
or FinancialTransaction.FinancialInstrProductCategory = '530'
or FinancialTransaction.FinancialInstrProductCategory = '540'
or FinancialTransaction.FinancialInstrProductCategory = '550'
or FinancialTransaction.FinancialInstrProductCategory = '580')
then 'MM'
when( Activity.FinancialInstrProductCategory = '610'
or Activity.FinancialInstrProductCategory = '620'
or Activity.FinancialInstrProductCategory = '630' )
then 'IRD'
when( Activity.FinancialInstrProductCategory = '850'
or Activity.FinancialInstrProductCategory = '860')
then 'TF'
when( Activity.FinancialInstrProductCategory = '010'
or Activity.FinancialInstrProductCategory = '020'
or Activity.FinancialInstrProductCategory = '030'
or Activity.FinancialInstrProductCategory = '040'
or Activity.FinancialInstrProductCategory = '042')
then 'SEC'
when( Activity.FinancialInstrProductCategory = '600')
then 'FX'
end as ftr_productgroup) as FinTransProductGroup,
FinancialTransaction.Portfolio,
FinancialTransaction.SecurityAccount,
FinancialTransaction.FinancialInstrumentProductType,
FinancialTransaction.FinancialInstrTransactionType
}
where
//Condition for 630
(
(
Activity.FinancialInstrProductCategory = '630'
and FinancialInstrActivityCategory >= '18'
)
and ( FinTransFlowCategory = '10' or FinTransFlowCategory = '11')
)
or
//Condition for FX
(
(
Activity.FinancialInstrProductCategory = '600'
and FinancialInstrActivityCategory >= '18'
)
and ( FinTransFlowCategory = '10' or FinTransFlowCategory = '11')
and ( FinTransFlowPaytAmtCrcy = FinancialTransaction.TransactionCurrency)
)
or
//Condition for MM
(
( Activity.FinancialInstrProductCategory = '510'
or Activity.FinancialInstrProductCategory = '520'
or Activity.FinancialInstrProductCategory = '530'
or Activity.FinancialInstrProductCategory = '540'
or Activity.FinancialInstrProductCategory = '550'
or Activity.FinancialInstrProductCategory = '580')
and FinancialInstrActivityCategory >= '10'
and(FinTransFlowCategory = '10'
or FinTransFlowCategory = '11'
or FinTransFlowCategory = '12'
or FinTransFlowCategory = '13'
or FinTransFlowCategory = '14')
)
or
//Condition for IRD
(
(
(
( Activity.FinancialInstrProductCategory = '610'
or Activity.FinancialInstrProductCategory = '620')
//or Activity.FinancialInstrProductCategory = '630')
and FinancialTransactionDirection = '1'
and(FinTransFlowCategory = '10'
or FinTransFlowCategory = '11')
)
or
//Condition for Trade Finance
(
( Activity.FinancialInstrProductCategory = '850'
or Activity.FinancialInstrProductCategory = '860')
and( FinTransFlowCategory = '10'
or FinTransFlowCategory = '11')
)
or
//Condition for Security
(
( Activity.FinancialInstrProductCategory = '010'
or Activity.FinancialInstrProductCategory = '020'
or Activity.FinancialInstrProductCategory = '030'
or Activity.FinancialInstrProductCategory = '040'
or Activity.FinancialInstrProductCategory = '042')
and( FinTransFlowCategory = '10'
or FinTransFlowCategory = '11' )
)
)
and FinancialInstrActivityCategory >= '20'
)
/*+[internal] {
"BASEINFO":
{
"FROM":
[
"I_FINANCIALTRANSACTION",
"I_FINTRANSACTIVITY",
"I_FINTRANSFLOW",
"P_FINTRANSBUSVOLKEYDATE"
],
"ASSOCIATED":
[],
"BASE":
[],
"ANNO_REF":
[],
"SCALAR_FUNCTION":
[],
"VERSION":0,
"ANNOREF_EVALUATION_ERROR":""
}
}*/