@VDM.private: true
@VDM.viewType: #BASIC
@AbapCatalog.compiler.compareFilter: true
@AccessControl.authorizationCheck: #NOT_REQUIRED
@AbapCatalog.sqlViewName: 'PFQMFLOWP'
define view P_FqmflowP
as select from fqm_flow
inner join P_FqmFlowcatKfCash on fqm_flow.flow_category = P_FqmFlowcatKfCash.FlowCategory
association [1..1] to I_CompanyCode as _CompanyCode on $projection.CompanyCode = _CompanyCode.CompanyCode
association [0..1] to I_OperationalAcctgDocItem as _OperationalAcctgDocItem on $projection.CompanyCode = _OperationalAcctgDocItem.CompanyCode
and $projection.ReferenceDocument = _OperationalAcctgDocItem.AccountingDocument
and $projection.FiscalYear = _OperationalAcctgDocItem.FiscalYear
and $projection.AccountingDocumentItem = _OperationalAcctgDocItem.AccountingDocumentItem
{
fqm_flow.create_user as CreatedByUser,
fqm_flow.last_update_user as LastChangedByUser,
fqm_flow.origin_system as OriginSystem,
fqm_flow.origin_application as OriginApplication,
fqm_flow.origin_document_id as OriginDocument,
fqm_flow.origin_transaction_id as OriginTransaction,
fqm_flow.origin_trans_qualifier as OriginTransQualifier,
fqm_flow.origin_flow_id as CashFlow,
fqm_flow.origin_system_rl as OriginSystemRl,
fqm_flow.origin_application_rl as OriginApplicationRl,
fqm_flow.origin_document_id_rl as OriginDocumentRl,
fqm_flow.origin_transaction_id_rl as OriginTransactionRl,
fqm_flow.origin_trans_qualifier_rl as OriginTransQualifierRl,
fqm_flow.origin_flow_id_rl as OriginFlowIdRl,
fqm_flow.certainty_level as CertaintyLevel,
fqm_flow.transaction_date as TransactionDate,
fqm_flow.flow_category as FlowCategory,
fqm_flow.flow_type as FlowType,
fqm_flow.payment_method as PaymentMethodCode,
fqm_flow.fi_account as GLAccount,
@Semantics.amount.currencyCode: 'CURRENCY' fqm_flow.amount as Amount,
@Semantics.currencyCode fqm_flow.currency as Currency,
fqm_flow.trm_product_type as ProductType,
fqm_flow.trm_transaction_type as FinancialTransactionType,
fqm_flow.trm_activity_category as TrmActivityCategory,
fqm_flow.trm_security_id as SecurityClass,
fqm_flow.trm_security_account as TrmSecurityAccount,
fqm_flow.trm_portfolio as Portfolio,
fqm_flow.house_bank as HouseBank,
fqm_flow.house_bank_account as HouseBankAccount,
fqm_flow.bank_account_id as BankAccountId,
fqm_flow.company_code as CompanyCode,
fqm_flow.customer_number as Customer,
fqm_flow.vendor_number as Vendor,
fqm_flow.partner as Partner,
fqm_flow.material as Material,
fqm_flow.business_area as BusinessArea,
fqm_flow.profit_center as ProfitCenter,
fqm_flow.project as Project,
fqm_flow.cost_center as CostCenter,
fqm_flow.trading_partner as PartnerCompany,
fqm_flow.liquidity_item as LiquidityItem,
fqm_flow.segment as Segment,
fqm_flow.planning_level as PlanningLevel,
fqm_flow.planning_group as PlanningGroup,
fqm_flow.assigned_company_code as AssignedCompanyCode,
fqm_flow.internal_reference as DocumentReferenceID,
fqm_flow.characteristics as Characteristics,
fqm_flow.assignment as AssignmentReference,
P_FqmFlowcatKfCash.KeyFigure as KeyFigure,
case fqm_flow.origin_application
when 'BKPF' then substring( fqm_flow.origin_document_id, 11, 8 ) // BKPF: document number, company code, fiscal year
when 'BKPFE' then substring( fqm_flow.origin_document_id, 11, 8 ) // BKPFE: document number, company code, fiscal year
when 'BKPFF' then substring( fqm_flow.origin_document_id, 11, 8 ) // BKPFF: document number, company code, fiscal year
when 'IBKPF' then substring( fqm_flow.origin_document_id, 11, 8 ) // IBKPF: document number, company code, fiscal year
when 'BSEGV' then substring( fqm_flow.origin_document_id, 11, 8 ) // BSEGV: copied from original transaction: document number, company code, fiscal year
when 'RMRP' then substring( fqm_flow.origin_document_id, 11, 4 ) // RMRP: document number, fiscal year
when 'TR-TM' then substring( fqm_flow.origin_document_id, 11, 10 ) // TR-TM: AWREF, AWORG in payment journal
when 'VBRK' then ' ' // VBRK: only document number
when 'LOANS' then substring( fqm_flow.origin_document_id, 1, 4 ) // LOANS: Company Code, Contract Number
else ' '
end as ReferenceDocumentContext,
case fqm_flow.origin_application
when 'BKPF' then substring( fqm_flow.origin_document_id, 1, 10 ) // BKPF: document number, company code, fiscal year
when 'BKPFE' then substring( fqm_flow.origin_document_id, 1, 10 ) // BKPFE: document number, company code, fiscal year
when 'BKPFF' then substring( fqm_flow.origin_document_id, 1, 10 ) // BKPFF: document number, company code, fiscal year
when 'IBKPF' then substring( fqm_flow.origin_document_id, 1, 10 ) // IBKPF: document number, company code, fiscal year
when 'BSEGV' then substring( fqm_flow.origin_document_id, 1, 10 ) // BSEGV: copied from original transaction: document number, company code, fiscal year
when 'RMRP' then substring( fqm_flow.origin_document_id, 1, 10 ) // RMRP: document number, fiscal year
when 'TR-TM' then substring( fqm_flow.origin_document_id, 1, 10 ) // TR-TM: AWREF, AWORG in payment journal
when 'VBRK' then substring( fqm_flow.origin_document_id, 1, 10 ) // VBRK: only document number
when 'LOANS' then substring( fqm_flow.origin_document_id, 5, 13 ) // LOANS: Company Code, Contract Number
else ' '
end as ReferenceDocument,
case fqm_flow.origin_application
when 'BKPF' then substring( fqm_flow.origin_document_id, 15, 4 ) // BKPF: document number, company code, fiscal year
when 'BKPFE' then substring( fqm_flow.origin_document_id, 15, 4 ) // BKPFE: document number, company code, fiscal year
when 'BKPFF' then substring( fqm_flow.origin_document_id, 15, 4 ) // BKPFF: document number, company code, fiscal year
when 'IBKPF' then substring( fqm_flow.origin_document_id, 15, 4 ) // IBKPF: document number, company code, fiscal year
when 'BSEGV' then substring( fqm_flow.origin_document_id, 15, 4 ) // BSEGV: copied from original transaction: document number, company code, fiscal year
when 'RMRP' then substring( fqm_flow.origin_document_id, 11, 4 ) // RMRP: document number, fiscal year
else ' '
end as FiscalYear,
case when
fqm_flow.origin_application = 'BKPF' or
fqm_flow.origin_application = 'BKPFE' or
fqm_flow.origin_application = 'BKPFF' or
fqm_flow.origin_application = 'BSEGV' or
fqm_flow.origin_application = 'IBKPF' or
fqm_flow.origin_application = 'RMRP' or
fqm_flow.origin_application = 'VBRK'
then substring( fqm_flow.origin_flow_id, 1, 3 ) else '000'
end as AccountingDocumentItem,
case when
fqm_flow.origin_application = 'BKPF' or
fqm_flow.origin_application = 'BKPFE' or
fqm_flow.origin_application = 'BKPFF' or
fqm_flow.origin_application = 'BSEGV' or
fqm_flow.origin_application = 'IBKPF' or
fqm_flow.origin_application = 'RMRP' or
fqm_flow.origin_application = 'VBRK'
then concat( '000', substring( fqm_flow.origin_flow_id, 1, 3 )) else '000000'
end as LedgerGLLineItem,
case when
fqm_flow.origin_application = 'BS' and
fqm_flow.origin_document_id between '00000001' and '99999999' and
fqm_flow.origin_transaction_id between '0000000100001' and '9999999999999'
then substring( fqm_flow.origin_transaction_id, 1, 8) else ' '
end as BankStatementShortID,
case when
fqm_flow.origin_application = 'BS' and
fqm_flow.origin_document_id between '00000001' and '99999999' and
fqm_flow.origin_transaction_id between '0000000100001' and '9999999999999'
then substring( fqm_flow.origin_transaction_id, 9, 5) else ' '
end as BankStatementItem,
_CompanyCode,
_OperationalAcctgDocItem
}
where
fqm_flow.deleted = ''
and fqm_flow.flg_actual = 'X'
and fqm_flow.transaction_date < '99991231'
/*+[internal] {
"BASEINFO":
{
"FROM":
[
"P_FQMFLOWCATKFCASH",
"FQM_FLOW"
],
"ASSOCIATED":
[
"I_COMPANYCODE",
"I_OPERATIONALACCTGDOCITEM"
],
"BASE":
[],
"ANNO_REF":
[],
"SCALAR_FUNCTION":
[],
"VERSION":0,
"ANNOREF_EVALUATION_ERROR":""
}
}*/