P_RU_MaterialRcptDateMM3

@VDM.private: true
@VDM.viewType: #CONSUMPTION
@AbapCatalog.sqlViewName: 'PRUMATRCDTMM3'
@AbapCatalog.compiler.compareFilter: true
@ClientHandling.algorithm: #SESSION_VARIABLE
@AccessControl.authorizationCheck: #NOT_REQUIRED
define view P_RU_MaterialRcptDateMM3
  as select from            P_RU_AcctgDocReferenceID  as InvoiceAccDocHead

    inner join              I_OperationalAcctgDocItem as InvoiceAccDocItem  on  InvoiceAccDocItem.CompanyCode        = InvoiceAccDocHead.CompanyCode
                                                                            and InvoiceAccDocItem.AccountingDocument = InvoiceAccDocHead.AccountingDocument
                                                                            and InvoiceAccDocItem.FiscalYear         = InvoiceAccDocHead.FiscalYear

    inner join              I_PurchasingDocumentItem  as PurchDocItem       on  PurchDocItem.CompanyCode            = InvoiceAccDocItem.CompanyCode
                                                                            and PurchDocItem.PurchasingDocument     = InvoiceAccDocItem.PurchasingDocument
                                                                            and PurchDocItem.PurchasingDocumentItem = InvoiceAccDocItem.PurchasingDocumentItem


    inner join              I_PurchaseOrderHistory    as PurchDocItemHistIR on  PurchDocItemHistIR.MaterialDocument     = InvoiceAccDocHead.OriginalReferenceDocumentNum
                                                                            and PurchDocItemHistIR.MaterialDocumentYear = InvoiceAccDocHead.OriginalReferenceDocumentCntxt
                                                                            and PurchDocItemHistIR.PurchaseOrder        = InvoiceAccDocItem.PurchasingDocument
                                                                            and PurchDocItemHistIR.PurchaseOrderItem    = InvoiceAccDocItem.PurchasingDocumentItem

    left outer to many join P_RU_MaterialRcptDateMM2  as PurchDocItemHistGR on  PurchDocItemHistGR.CompanyCode                 = InvoiceAccDocHead.CompanyCode
                                                                            and PurchDocItemHistGR.PurchaseOrder               = PurchDocItemHistIR.PurchaseOrder
                                                                            and PurchDocItemHistGR.PurchaseOrderItem           = PurchDocItemHistIR.PurchaseOrderItem
                                                                            and PurchDocItemHistGR.ReferenceDocumentFiscalYear = PurchDocItemHistIR.ReferenceDocumentFiscalYear
                                                                            and PurchDocItemHistGR.ReferenceDocument           = PurchDocItemHistIR.ReferenceDocument
                                                                            and PurchDocItemHistGR.ReferenceDocumentItem       = PurchDocItemHistIR.ReferenceDocumentItem

{
  InvoiceAccDocHead.CompanyCode,
  InvoiceAccDocHead.AccountingDocument,
  InvoiceAccDocHead.FiscalYear,
  InvoiceAccDocHead.DocumentDate,
  InvoiceAccDocHead.PostingDate,

  InvoiceAccDocHead.IsReversal,
  InvoiceAccDocHead.IsReversed,

  InvoiceAccDocItem.PurchasingDocument,
  InvoiceAccDocItem.PurchasingDocumentItem,
  PurchDocItem.PurchasingDocumentItemCategory,
  PurchDocItem.GoodsReceiptIsExpected,
  PurchDocItem.InvoiceIsGoodsReceiptBased,

  PurchDocItemHistIR.MaterialDocument            as IR_MaterialDocument,
  PurchDocItemHistIR.MaterialDocumentYear        as IR_MaterialDocumentYear,
  PurchDocItemHistIR.MaterialDocumentItem        as IR_MaterialDocumentItem,
  PurchDocItemHistIR.PurchaseOrderHistCategory   as IR_PurchaseOrderHistCategory,

  PurchDocItemHistGR.MaterialDocument            as GR_MaterialDocument,
  PurchDocItemHistGR.MaterialDocumentYear        as GR_MaterialDocumentYear,
  PurchDocItemHistGR.MaterialDocumentItem        as GR_MaterialDocumentItem,
  PurchDocItemHistGR.PurchaseOrderHistCategory   as GR_PurchaseOrderHistCategory,
  PurchDocItemHistGR.PostingDate                 as GR_PostingDate,

  PurchDocItemHistIR.ReferenceDocument           as IR_ReferenceDocument,
  PurchDocItemHistIR.ReferenceDocumentFiscalYear as IR_ReferenceDocumentFiscalYear,
  PurchDocItemHistIR.ReferenceDocumentItem       as IR_ReferenceDocumentItem,

  case when PurchDocItem.GoodsReceiptIsExpected = 'X'
    then 'MIGO'
    else 'MIGO->PMIRO'
  end                                            as SourceType,
  case when PurchDocItem.GoodsReceiptIsExpected = 'X'
    then PurchDocItemHistGR.PostingDate
    else InvoiceAccDocHead.PostingDate
  end                                            as MaterialDocumentPostingDate
}
where
       InvoiceAccDocHead.SenderLogicalSystem        =  ''
  and  InvoiceAccDocHead.ReferenceDocumentType      =  'RMRP'
  and  InvoiceAccDocItem.ReferenceDocumentType      =  'RMRP'
  and  InvoiceAccDocItem.PurchasingDocument         <> ''
  and  InvoiceAccDocItem.TaxCode                    <> ''
  and  PurchDocItem.PurchasingDocumentCategory      =  'F'
  and(
       PurchDocItemHistIR.PurchaseOrderHistCategory =  'Q'
    or PurchDocItemHistIR.PurchaseOrderHistCategory =  'N'
  )
/*+[internal] {
"BASEINFO":
{
"FROM":
[
"I_OPERATIONALACCTGDOCITEM",
"I_PURCHASEORDERHISTORY",
"I_PURCHASINGDOCUMENTITEM",
"P_RU_ACCTGDOCREFERENCEID",
"P_RU_MATERIALRCPTDATEMM2"
],
"ASSOCIATED":
[],
"BASE":
[],
"ANNO_REF":
[],
"SCALAR_FUNCTION":
[],
"VERSION":0,
"ANNOREF_EVALUATION_ERROR":""
}
}*/