I_DocWithWorkflowStatus

@ClientHandling.algorithm: #SESSION_VARIABLE
@AbapCatalog.sqlViewName: 'IDOCWITHSTATUS'
@AbapCatalog.compiler.compareFilter: true
@AccessControl.authorizationCheck: #NOT_REQUIRED
@ObjectModel.usageType.serviceQuality: #C
@ObjectModel.usageType.sizeCategory: #L
@ObjectModel.usageType.dataClass: #MIXED
@EndUserText.label: 'document with work flow status'
@VDM.viewType: #BASIC
define view I_DocWithWorkflowStatus
  as select from bkpf
{
  key bkpf.ausbk                                                                                                           as SourceCompanyCode, // not key in table

  key bkpf.bukrs                                                                                                           as CompanyCode,
  key bkpf.belnr                                                                                                           as AccountingDocument,
  key bkpf.gjahr                                                                                                           as FiscalYear,
      bkpf.blart                                                                                                           as AccountingDocumentType,
      bkpf.xblnr                                                                                                           as ReferenceDocument,
      bkpf.bldat                                                                                                           as DocumentDate,
      bkpf.monat                                                                                                           as FiscalPeriod,
      bkpf.aedat                                                                                                           as LastChangeDate, //Date of the Last Document Change

      bkpf.ldgrp                                                                                                           as LedgerGroup,
      bkpf.bktxt                                                                                                           as HeaderDescription,
      bkpf.budat                                                                                                           as PostingDate,
      bkpf.usnam                                                                                                           as CreatedByUser,
      cast( cast(concat(bkpf.cpudt, bkpf.cputm) as abap.numc( 14 )) as fac_creation_date_time)                             as CreationDateTime,
      bkpf.cpudt                                                                                                           as AccountingDocumentCreationDate,
      // rpad and replace cannot add spaces, but we need 4 characters long bukrs

      cast(replace(replace(concat(concat(bkpf.belnr,rpad(bkpf.bukrs, 4, '$')),bkpf.gjahr), '$', '| |'), '|', '') as awkey) as ObjectKey,
      bkpf.bstat                                                                                                           as AccountingDocumentCategory,
      bkpf.tcode                                                                                                           as TransactionCode,
      //    add by i337033

      bkpf.pptcod                                                                                                          as ParkingTransactionCode,
      bkpf.waers                                                                                                           as Currency,
      bkpf.hwaer                                                                                                           as CompanyCodeCurrency,
      bkpf.hwae2                                                                                                           as GlobalCurrency,
      bkpf.hwae3                                                                                                           as FreeDefinedCurrency1, //3rd FI currency

      bkpf.ppnam                                                                                                           as ParkedByUser,
      bkpf.grpid                                                                                                           as Batch, //,

      bkpf.bvorg                                                                                                           as IntercompanyTransaction,

      // auto reversal

      bkpf.stgrd                                                                                                           as ReversalReason,
      bkpf.stodt                                                                                                           as PlannedReversalDate

}
where
      bkpf.bukrs  =    bkpf.ausbk
  and bkpf.tcode  like 'FBDC_P%' //Parked documents

  or  bkpf.ausbk  =    ''
  and bkpf.pptcod like 'FBDC_P%' //Posted parked documents



union all //For posted documents derived by intercompany posting

select from bkpf as bkpf_receiving
  join      bkpf as bkpf_sending on  bkpf_receiving.bvorg =    bkpf_sending.bvorg
                                 and bkpf_receiving.ausbk =    bkpf_sending.bukrs
                                 and bkpf_receiving.gjahr =    bkpf_sending.gjahr
                                 and bkpf_sending.bvorg   <>   '' //Get sending posting document for sending AWKEY and ParkedBy

                                 and bkpf_sending.ausbk   =    '' //Sending posting document should have no source company code

                                 and bkpf_receiving.ausbk <>   ''
                                 and bkpf_receiving.ausbk <>   bkpf_receiving.bukrs // Derived posted documents during intercompany posting

                                 and bkpf_sending.pptcod  like 'FBDC_P%'


{
  key bkpf_receiving.ausbk                                                                                                                          as SourceCompanyCode, // not key in table

  key bkpf_receiving.bukrs                                                                                                                          as CompanyCode,
  key bkpf_receiving.belnr                                                                                                                          as AccountingDocument,
  key bkpf_receiving.gjahr                                                                                                                          as FiscalYear,
      bkpf_receiving.blart                                                                                                                          as AccountingDocumentType,
      bkpf_receiving.xblnr                                                                                                                          as ReferenceDocument,
      bkpf_receiving.bldat                                                                                                                          as DocumentDate,
      bkpf_receiving.monat                                                                                                                          as FiscalPeriod,
      bkpf_receiving.aedat                                                                                                                          as LastChangeDate, //Date of the Last Document Change

      bkpf_receiving.ldgrp                                                                                                                          as LedgerGroup,
      bkpf_receiving.bktxt                                                                                                                          as HeaderDescription,
      bkpf_receiving.budat                                                                                                                          as PostingDate,
      bkpf_receiving.usnam                                                                                                                          as CreatedByUser,
      cast( cast(concat(bkpf_receiving.cpudt, bkpf_receiving.cputm) as abap.numc( 14 )) as fac_creation_date_time)                                  as CreationDateTime,
      bkpf_receiving.cpudt                                                                                                                          as AccountingDocumentCreationDate,
      cast(replace(replace(concat(concat(bkpf_sending.belnr,rpad(bkpf_sending.bukrs, 4, '$')), bkpf_sending.gjahr), '$', '| |'), '|', '') as awkey) as ObjectKey, //Change it to awkey from sending doc

      bkpf_receiving.bstat                                                                                                                          as AccountingDocumentCategory,
      bkpf_receiving.tcode                                                                                                                          as TransactionCode,
      bkpf_receiving.pptcod                                                                                                                         as ParkingTransactionCode,
      bkpf_receiving.waers                                                                                                                          as Currency,
      bkpf_receiving.hwaer                                                                                                                          as CompanyCodeCurrency,
      bkpf_receiving.hwae2                                                                                                                          as GlobalCurrency,
      bkpf_receiving.hwae3                                                                                                                          as FreeDefinedCurrency1, //3rd FI currency,

      bkpf_sending.ppnam                                                                                                                            as ParkedByUser, //Change it to parkedby from sending doc

      bkpf_receiving.grpid                                                                                                                          as Batch, //,

      bkpf_receiving.bvorg                                                                                                                          as IntercompanyTransaction,

      // auto reversal

      bkpf_receiving.stgrd                                                                                                                          as ReversalReason,
      bkpf_receiving.stodt                                                                                                                          as PlannedReversalDate

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