@VDM.private: true
@VDM.viewType: #BASIC
@AbapCatalog.sqlViewName: 'PFQMFLOW'
@AbapCatalog.compiler.compareFilter: true
@ClientHandling.algorithm: #SESSION_VARIABLE
@AccessControl.authorizationCheck: #NOT_REQUIRED
@ObjectModel.usageType.serviceQuality: #D
@ObjectModel.usageType.sizeCategory: #XXL
@ObjectModel.usageType.dataClass: #TRANSACTIONAL
define view P_Fqmflow
as select from fqm_flow
inner join P_FqmFlowTypeKeyFigure on fqm_flow.flow_category = P_FqmFlowTypeKeyFigure.FlowCategory
association [1..1] to I_CompanyCode as _CompanyCode on $projection.CompanyCode = _CompanyCode.CompanyCode
association [0..1] to I_AccountingDocument as _AccountingDocument on $projection.OriginDocumentRl = _AccountingDocument.OriginalReferenceDocument
and $projection.OriginApplication = _AccountingDocument.ReferenceDocumentType
association [0..1] to I_JournalEntry as _JournalEntry on $projection.CompanyCode = _JournalEntry.CompanyCode
and $projection.ReferenceDocument = _JournalEntry.AccountingDocument
and $projection.FiscalYear = _JournalEntry.FiscalYear
association [0..1] to I_JournalEntryItem as _JournalEntryItem on $projection.ReferenceDocument = _JournalEntryItem.ReferenceDocument
and $projection.ReferenceDocumentContext = _JournalEntryItem.ReferenceDocumentContext
and $projection.LedgerGLLineItem = _JournalEntryItem.LedgerGLLineItem
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
association [0..1] to P_Fqmbankstatement as _BankStatement on $projection.BankStatementShortID = _BankStatement.BankStatementShortID
and $projection.BankStatementItem = _BankStatement.BankStatementItem
{
fqm_flow.valid_from as ValidFrom,
fqm_flow.valid_to as ValidTo,
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 OriginFlowId,
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' sum(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_FqmFlowTypeKeyFigure.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,
_AccountingDocument,
_JournalEntry,
_JournalEntryItem,
_OperationalAcctgDocItem,
_BankStatement
}
where
fqm_flow.deleted <> 'X'
and fqm_flow.flg_actual = 'X'
group by
fqm_flow.valid_from,
fqm_flow.valid_to,
fqm_flow.create_user,
fqm_flow.last_update_user,
fqm_flow.origin_system,
fqm_flow.origin_application,
fqm_flow.origin_document_id,
fqm_flow.origin_transaction_id,
fqm_flow.origin_trans_qualifier,
fqm_flow.origin_flow_id,
fqm_flow.origin_system_rl,
fqm_flow.origin_application_rl,
fqm_flow.origin_document_id_rl,
fqm_flow.origin_transaction_id_rl,
fqm_flow.origin_trans_qualifier_rl,
fqm_flow.origin_flow_id_rl,
fqm_flow.certainty_level,
fqm_flow.transaction_date,
fqm_flow.flow_category,
fqm_flow.flow_type,
fqm_flow.payment_method,
fqm_flow.fi_account,
fqm_flow.currency,
fqm_flow.trm_product_type,
fqm_flow.trm_transaction_type,
fqm_flow.trm_activity_category,
fqm_flow.trm_security_id,
fqm_flow.trm_security_account,
fqm_flow.trm_portfolio,
fqm_flow.house_bank,
fqm_flow.house_bank_account,
fqm_flow.bank_account_id,
fqm_flow.company_code,
fqm_flow.customer_number,
fqm_flow.vendor_number,
fqm_flow.partner,
fqm_flow.material,
fqm_flow.business_area,
fqm_flow.profit_center,
fqm_flow.project,
fqm_flow.cost_center,
fqm_flow.trading_partner,
fqm_flow.liquidity_item,
fqm_flow.segment,
fqm_flow.planning_level,
fqm_flow.planning_group,
fqm_flow.assigned_company_code,
fqm_flow.internal_reference,
fqm_flow.characteristics,
fqm_flow.assignment,
P_FqmFlowTypeKeyFigure.KeyFigure
having
sum(
fqm_flow.amount
) <> 0
/*+[internal] {
"BASEINFO":
{
"FROM":
[
"P_FQMFLOWTYPEKEYFIGURE",
"FQM_FLOW"
],
"ASSOCIATED":
[
"I_ACCOUNTINGDOCUMENT",
"I_COMPANYCODE",
"I_JOURNALENTRY",
"I_JOURNALENTRYITEM",
"I_OPERATIONALACCTGDOCITEM",
"P_FQMBANKSTATEMENT"
],
"BASE":
[],
"ANNO_REF":
[],
"SCALAR_FUNCTION":
[],
"VERSION":0,
"ANNOREF_EVALUATION_ERROR":""
}
}*/