Mmim_MatDocDocFlow

@AbapCatalog.preserveKey: true
@AbapCatalog.sqlViewName: 'MMIMMDOCDOCFLOW'
@AbapCatalog.compiler.compareFilter: true
@AccessControl.authorizationCheck: #NOT_REQUIRED
@EndUserText.label: 'Material Documents Document Flow'
@ObjectModel.usageType.sizeCategory: #XXL
@ObjectModel.usageType.serviceQuality: #C
@ObjectModel.usageType.dataClass:#MIXED
@ClientHandling.algorithm: #SESSION_VARIABLE
@DataAging.noAgingRestriction:true

//////////////////////////////////////////////////////////////////////////

//

// Defined document categories are (historical emerged):

// PR -> PuRchase order

// D  -> inbound Delivery

// PO -> Production Order

// MO -> Maintenance Order

// PC -> ProCess order

// M  -> Material document

// R  -> Reversal (canceled) material document

// R2 -> Reversal of a transfer material document

// M2 -> Inner plant transfer document

// IP -> cross plant transfer Issuing Plant

// RP -> cross plant transfer Receiving Plant

// A  -> Accounting document

// PI  -> Physical inventory document

//

//////////////////////////////////////////////////////////////////////////


define view Mmim_MatDocDocFlow
  as select distinct from matdoc
  // PRECEDING DOCUMENT: PuRchase order (PR)

  // SUBSEQUENT DOCUMENT: Material document (M)

  inner join ekko on matdoc.ebeln = ekko.ebeln
{
  key cast(matdoc.ebeln as mmim_preced_doc) as  PrecedingDocument,
  key cast('0000' as char4)     as  PrecedingDocumentYear,
  key cast(matdoc.mblnr as mmim_subsequ_doc) as  SubsequentDocument,
  key cast(matdoc.mjahr as char4)      as  SubsequentDocumentYear,
  key cast(ebelp as char20)     as  PrecedingDocumentItem,
  key cast(zeile as char20)     as  SubsequentDocumentItem,
  key cast(matdoc.mblnr as char20)     as  MaterialDocument,
  key matdoc.mjahr                     as  MaterialDocumentYear,
  key cast(zeile as char20)     as  MaterialDocumentItem,
  key cast('0000' as char4)     as  FiscalYear,
      case ekko.bstyp
      when 'L'  then cast ('SA' as char2)
      else      cast ('PR' as char2)      
      end                       as  PrecedingDocumentCategory,
      cast('M ' as char2)       as  SubsequentDocumentCategory,
      cast('' as bukrs)         as  CompanyCode
}
where xauto = '' and matdoc.ebeln <> '' and matdoc.mblnr <> '' and vbeln_im = '' and smbln = '' and record_type = 'MDOC'
  and zeile = urzei

union all select distinct from matdoc
   inner join likp on matdoc.vbeln_im = likp.vbeln 
   inner join tvlk on likp.lfart = tvlk.lfart
// PRECEDING DOCUMENT: Inbound Delivery (D), Outbound Delivery (OD)

// SUBSEQUENT DOCUMENT: Material document (M)

{
  key cast(vbeln_im as mmim_preced_doc) as PrecedingDocument,
  key cast('0000' as char4)           as PrecedingDocumentYear,
  key cast(mblnr as mmim_subsequ_doc) as SubsequentDocument,
  key cast(mjahr as char4)            as SubsequentDocumentYear,
  key cast(vbelp_im as char20)        as PrecedingDocumentItem,
  key cast(zeile as char20)           as SubsequentDocumentItem,
  key cast(mblnr as char20)           as MaterialDocument,
  key mjahr                           as MaterialDocumentYear,
  key cast(zeile as char20)           as MaterialDocumentItem,
  key cast('0000' as char4)           as FiscalYear,
      case tvlk.vbtyp 
        when '7' then 'D'
        when 'J' then 'OD'
        when 'T' then 'RD'
        else '7'
      end as PrecedingDocumentCategory,
//      cast('D' as char2)              as PrecedingDocumentCategory,

      cast('M ' as char2)             as SubsequentDocumentCategory,
      cast('' as bukrs)               as CompanyCode
}
where xauto = '' and vbeln_im <> '' and smbln = '' and record_type = 'MDOC'

union all select distinct from matdoc as m
  inner join                   aufk   as o on m.aufnr = o.aufnr
// PRECEDING DOCUMENT: Production Order (PO), Maintenance Order (MO), ProCess order (PC)

// SUBSEQUENT DOCUMENT: Material document (M)

{
  key cast(m.aufnr as mmim_preced_doc) as PrecedingDocument,
  key cast('0000' as char4)        as PrecedingDocumentYear,
  key cast(mblnr as mmim_subsequ_doc) as SubsequentDocument,
  key cast(mjahr as char4)         as SubsequentDocumentYear,
  key cast(aufps as char20)        as PrecedingDocumentItem,
  key cast(zeile as char20)        as SubsequentDocumentItem,
  key cast(mblnr as char20)        as MaterialDocument,
  key mjahr                        as MaterialDocumentYear,
  key cast(zeile as char20)        as MaterialDocumentItem,
  key cast('0000' as char4)        as FiscalYear,
      case
           when o.autyp = '10' then 'PO'         
           when o.autyp = '30' then 'MO'
           when o.autyp = '40' then 'PC'
           else '00'   //UNDEFINED: If that case occurs, this part has to be adjusted

      end                          as PrecedingDocumentCategory,
      cast('M ' as char2)          as SubsequentDocumentCategory,
      cast('' as bukrs)            as CompanyCode
}
where xauto = '' and m.aufnr <> '' and m.aufps <> '0000' and mblnr <> '' and smbln = '' and record_type = 'MDOC'

union all select distinct from Mmim_MatDocAccounting
  inner join                   matdoc on  MaterialDocument      = matdoc.mblnr
                                      and MaterialDocumentYear  = matdoc.mjahr
                                      and PrecedingDocumentItem = matdoc.zeile
                                      and matdoc.record_type    = 'MDOC'
// PRECEDING DOCUMENT: Material document (M), Reversal of a transfer posting (R2)

// SUBSEQUENT DOCUMENT: Accounting document (A)

{
  key PrecedingDocument,
  key PrecedingDocumentYear,
  key SubsequentDocument,
  key SubsequentDocumentYear,
  key PrecedingDocumentItem,
  key SubsequentDocumentItem,
  key MaterialDocument,
  key MaterialDocumentYear,
  key cast(zeile as char20)                as MaterialDocumentItem,
  key FiscalYear,
      case
           when matdoc.lbbsa_cid <> ''
                and matdoc.smbln = ''
                and matdoc.vbeln_im = ''
                and matdoc.aufnr = ''
                and matdoc.ebeln = ''
           then 'R2' //RP

           else PrecedingDocumentCategory
      end                                  as PrecedingDocumentCategory,
      SubsequentDocumentCategory,
      CompanyCode
}

// Transfer material documents (LBBSA_CID <> '')

union all select distinct from matdoc
// PRECEDING DOCUMENT: One step transfer, Inner plant transfer document (M2)

// SUBSEQUENT DOCUMENT: Reversal of a transfer material document (R2)

{
  key cast(mblnr as mmim_preced_doc) as PrecedingDocument,
  key cast(mjahr as char4)      as PrecedingDocumentYear,
  key cast(mblnr as mmim_subsequ_doc) as SubsequentDocument,
  key cast(mjahr as char4)      as SubsequentDocumentYear,
  key cast(zeile as char20)     as PrecedingDocumentItem,
  key cast(zeile as char20)     as SubsequentDocumentItem,
  key cast(mblnr as char20)     as MaterialDocument,
  key mjahr                     as MaterialDocumentYear,
  key cast(zeile as char20)     as MaterialDocumentItem,
  key cast('0000' as char4)     as FiscalYear,
      cast('M2' as char2)       as PrecedingDocumentCategory,
      cast('R2' as char2)       as SubsequentDocumentCategory,
      cast('' as bukrs)         as CompanyCode
}
where xauto = '' and lbbsa_cid <> '' and record_type = 'MDOC' and smbln  = '' and vbeln_im = '' and aufnr = '' and ebeln = ''

union all select distinct from matdoc
// PRECEDING DOCUMENT: Cross-plant transfer, issuing plant (IP)

// SUBSEQUENT DOCUMENT: Cross-plant transfer, receiving plant (RP)

{
//  key cast(concat(mblnr, zeile ) as char20 )                                                  as PrecedingDocument,

  key cast(concat(mblnr, cast(cast(zeile as abap.int2) as char10) ) as mmim_preced_doc )      as PrecedingDocument,
//  key cast(concat(concat(mblnr, '/'), cast(cast(zeile as abap.int2) as char10) ) as char20 )  as PrecedingDocument,

  //  key concat(MaterialDocument,cast(cast(MaterialDocumentItem as abap.int2) as char10 ) ) as MaterialDocument,  

  key cast(mjahr as char4)                                                                    as PrecedingDocumentYear,
  key cast(concat(mblnr, cast( ( cast(zeile as abap.int2) + 1 ) as char20 ) ) as mmim_subsequ_doc ) as SubsequentDocument,
//  key cast(concat(concat(mblnr, '/'), cast( ( cast(zeile as abap.int2) + 1 ) as char20 ) ) as char20 ) as SubsequentDocument,

//  key cast(concat_with_space(mblnr, cast( ( cast(zeile as abap.int2) + 1 ) as char11 ), 1 ) as char20 ) as SubsequentDocument,

  key cast(mjahr as char4)                                                                    as SubsequentDocumentYear,
  key cast(zeile as char20)                                                                   as PrecedingDocumentItem,
  key cast( ( cast(zeile as abap.int2) + 1 ) as char20 )                                      as SubsequentDocumentItem,
  key cast(mblnr as char20)                                                                   as MaterialDocument,
  key mjahr                                                                                   as MaterialDocumentYear,
  key cast(zeile as char20)                                                                   as MaterialDocumentItem,
  key cast('0000' as char4)                                                                   as FiscalYear,
      cast('IP' as char2)                                                                     as PrecedingDocumentCategory,
      cast('RP' as char2)                                                                     as SubsequentDocumentCategory,
      cast('' as bukrs)                                                                       as CompanyCode
}
where xauto = '' and lbbsa_cid <> '' and record_type = 'MDOC' and smbln = '' and vbeln_im = '' and aufnr = '' and ebeln = ''

// case: 4710 was created, cancelled by 4711.

// 4710 is selected as MaterialDocument and 4711 is the subsequent document

union all select distinct from matdoc // association between mblnr and smbln

// PRECEDING DOCUMENT: Material document (M), Inner plant transfer document (M2)

// SUBSEQUENT DOCUMENT: Canceled material document (R), Reversal of a transfer material document

{
  key cast(smbln as mmim_preced_doc) as PrecedingDocument,
  key cast(sjahr as char4)      as PrecedingDocumentYear,
  key cast(mblnr as mmim_subsequ_doc) as SubsequentDocument,
  key cast(mjahr as char4)      as SubsequentDocumentYear,
  key cast(smblp as char20)     as PrecedingDocumentItem,
  key cast(zeile as char20)     as SubsequentDocumentItem,
  key cast(smbln as char20)     as MaterialDocument,
  key sjahr                     as MaterialDocumentYear,
  key cast(smblp as char20)     as MaterialDocumentItem,
  key cast('0000' as char4)     as FiscalYear,
      case
           when
                matdoc.lbbsa_cid <> ''
                and matdoc.smbln <> ''
                and matdoc.vbeln_im = ''
                and matdoc.aufnr = ''
                and matdoc.ebeln = ''
           then 'M2'
           else 'M'
      end                       as PrecedingDocumentCategory,
      case
           when
                matdoc.lbbsa_cid <> ''
                and matdoc.smbln <> ''
                and matdoc.vbeln_im = ''
                and matdoc.aufnr = ''
                and matdoc.ebeln = ''
           then 'R2'
           else 'R'
      end                       as SubsequentDocumentCategory,
      cast('' as bukrs)         as CompanyCode
}
where xauto = '' and mblnr <> '' and smbln <> '' and record_type = 'MDOC'

// case: 4710 was created, cancelled by 4711.

// 4711 is selected as MaterialDocument and 4710 is the precending document

union all select distinct from matdoc as mat
// PRECEDING DOCUMENT: Canceled material document (R)

// SUBSEQUENT DOCUMENT: Material document (M)

{
  key cast(mat.smbln as mmim_preced_doc) as PrecedingDocument,
  key cast(mat.sjahr as char4)      as PrecedingDocumentYear,
  key cast(mat.mblnr as mmim_subsequ_doc) as SubsequentDocument,
  key cast(mat.mjahr as char4)      as SubsequentDocumentYear,
  key cast(mat.smblp as char20)     as PrecedingDocumentItem,
  key cast(mat.zeile as char20)     as SubsequentDocumentItem,
  key cast(mat.mblnr as char20)     as MaterialDocument,
  key mjahr                         as MaterialDocumentYear,
  key cast(zeile as char20)         as MaterialDocumentItem,
  key cast('0000' as char4)         as FiscalYear,
      cast('R' as char2)            as PrecedingDocumentCategory,
      cast('M ' as char2)           as SubsequentDocumentCategory,
      cast('' as bukrs)             as CompanyCode
}
where mat.xauto = '' and mat.mblnr <> '' and mat.smbln <> '' and record_type = 'MDOC'

union all select distinct from matdoc as mat
    inner join                 iseg   as  pi_document_item on mat.mblnr = pi_document_item.mblnr
                                                           and  mat.mjahr = pi_document_item.mjahr
                                                           and  mat.zeile = pi_document_item.zeile
// PRECEDING DOCUMENT: Physical inventory Document (PI)

// SUBSEQUENT DOCUMENT: Material document (M)

{
  key cast(pi_document_item.iblnr as mmim_preced_doc) as PrecedingDocument,
  key cast(pi_document_item.gjahr as char4)           as PrecedingDocumentYear,
  key cast(mat.mblnr as mmim_subsequ_doc)             as SubsequentDocument,
  key cast(mat.mjahr as char4)                        as SubsequentDocumentYear,
  key cast(pi_document_item.zeili as char20)          as PrecedingDocumentItem,
  key cast(mat.zeile as char20)                       as SubsequentDocumentItem,
  key cast(mat.mblnr as char20)                       as MaterialDocument,
  key mat.mjahr                                       as MaterialDocumentYear,
  key cast(mat.zeile as char20)                       as MaterialDocumentItem,
  key cast(pi_document_item.gjahr as char4)           as FiscalYear,
      cast('PI' as char2)                             as PrecedingDocumentCategory,
      cast('M ' as char2)                             as SubsequentDocumentCategory,
      cast('' as bukrs)                               as CompanyCode
}
where mat.xauto = '' and mat.mblnr <> ''  and mat.smbln = ''  and record_type = 'MDOC'

/*+[internal] {
"BASEINFO":
{
"FROM":
[
"MMIM_MATDOCACCOUNTING",
"AUFK",
"EKKO",
"ISEG",
"LIKP",
"MATDOC",
"TVLK"
],
"ASSOCIATED":
[],
"BASE":
[],
"ANNO_REF":
[],
"SCALAR_FUNCTION":
[],
"VERSION":0,
"ANNOREF_EVALUATION_ERROR":""
}
}*/