P_Fa_Flowitem

@AbapCatalog.sqlViewName: 'PFALITEM'
@AbapCatalog.compiler.compareFilter: true
@ClientHandling.algorithm: #SESSION_VARIABLE
@AccessControl.authorizationCheck: #NOT_ALLOWED
@ObjectModel.usageType.serviceQuality: #D
@ObjectModel.usageType.sizeCategory: #XL
@ObjectModel.usageType.dataClass: #MIXED
@Metadata.ignorePropagatedAnnotations: true
@VDM.private: true
@VDM.viewType: #BASIC
define view P_Fa_Flowitem

with parameters
@Consumption.defaultValue: '20170101000000'
P_htstp:timestampl

 as
select from fqm_flow as flow
 inner join P_FqmFlwCatKFigCsh as flowcat
        on  flow.flow_category = flowcat.FlowCategory
/* left outer join P_RECONCILIATION_STATISTICS_TB (iv_mandt:$session.client) as reconcil
        on  flow.flow_id = reconcil.flow_id
        and flow.valid_from = reconcil.valid_from*/
 left outer join I_OperationalAcctgDocItem as fi_doc
        on  flow.company_code = fi_doc.CompanyCode
        and flow.fi_fiscal_year = fi_doc.FiscalYear
        and flow.fi_document_number = fi_doc.AccountingDocument
        and flow.fi_document_line_item = fi_doc.AccountingDocumentItem
 {
      flow.company_code as CompanyCode,
      coalesce(fi_doc.AccountingDocumentType, cast('' as blart)) as FiDocumentType,
      flow.transaction_date as TransactionDate,
      flow.house_bank as HouseBank,
      flow.house_bank_account as HouseBankAccount,
      case when flow.bank_account_id = '' then '0000000000'
           else flow.bank_account_id
      end   as BankAccountID,
      flow.certainty_level as CertaintyLevel,
      flow.currency as Currency,
      flow.planning_level as PlanningLevel,
      flow.planning_group as CashPlanningGroup,
      flow.liquidity_item as LiquidityItem,
      flow.fi_account as GLAccount,
      flow.profit_center as  ProfitCenter,
      flow.segment as Segment,
      flow.business_area as BusinessArea,
      flow.trading_partner as TradingPartner,
      case when ( flow.certainty_level = 'MEMO' or flow.certainty_level = 'INTRAM' ) and flow.fi_due_date <>'00000000'   then flow.fi_due_date
           else '99991231' end as ExpirationDate,
      case when flow.amount < 0 then '-'
      else '+'
      end as direction,
      sum(flow.amount) as AMOUNT,
      sum(flow.base_amount) as BaseAmount,
      flow.base_currency as    BaseCurrency,
      flow.valid_from,
      flow.valid_to,

      // release statues

      flow.rel_status,

      // reconciliation status

      --case when reconcil.reconciliation_id is null
      --then 'U'
      --else '' end as reconcil_status
      -- 0 Actual/1 Intraday Recncln/2 Intraday Unrecncln/3 Forecast Recncln/4 Forecast Unrecncln
/*
      case when flow.certainty_level = 'INTRAM' and reconcil.reconciliation_id is not null
           then '1'
      when flow.certainty_level = 'INTRAM' and reconcil.reconciliation_id is null
           then '2'
      when flow.certainty_level <> 'INTRAM' and flow.certainty_level <> 'ACTUAL' and reconcil.reconciliation_id is not null
           then '3'
      when flow.certainty_level <> 'INTRAM' and flow.certainty_level <> 'ACTUAL' and reconcil.reconciliation_id is null
           then '4'
           else '0'
      end as reconcil_status*/
      '' as reconcil_status
}

  where  ( flow.deleted          = '' or flow.deleted is null )
//  and  fqm_flow.flg_actual       =  'X'      - Removed for snapshot enablement

    and  ( flow.cmm_state        = '0' or flow.cmm_state        = '' )
    and  flow.transaction_date <> '99991231'

group by
      flow.company_code ,
      flow.transaction_date ,
      flow.house_bank ,
      flow.house_bank_account ,
      flow.bank_account_id ,
      flow.certainty_level ,
      flow.currency ,
      flow.profit_center,
      flow.segment,
      flow.business_area,
      flow.trading_partner,
      flow.fi_due_date,
      flow.planning_level ,
      flow.planning_group ,
      flow.liquidity_item ,
      fi_doc.AccountingDocumentType,
      flow.fi_account,
      flow.amount,
      flow.base_currency,
      flow.valid_from,
      flow.valid_to,
      flow.rel_status
      --,
      --reconcil.reconciliation_id
;

/*+[internal] {
"BASEINFO":
{
"FROM":
[
"I_OPERATIONALACCTGDOCITEM",
"P_FQMFLWCATKFIGCSH",
"FQM_FLOW"
],
"ASSOCIATED":
[],
"BASE":
[],
"ANNO_REF":
[],
"SCALAR_FUNCTION":
[],
"VERSION":0,
"ANNOREF_EVALUATION_ERROR":""
}
}*/