P_PL_SAFTMaterialDocumentRecd

@AbapCatalog.sqlViewName: 'PPLSAFTMATDOCREC'
@AbapCatalog.compiler.compareFilter: true
@AbapCatalog.preserveKey: true
@AccessControl.authorizationCheck: #NOT_REQUIRED
@VDM.private: true
@VDM.viewType: #COMPOSITE
@ObjectModel.usageType.sizeCategory: #XL
@ObjectModel.usageType.dataClass:  #MIXED
@ObjectModel.usageType.serviceQuality: #P
@ClientHandling.algorithm: #SESSION_VARIABLE
define view P_PL_SAFTMaterialDocumentRecd
  as select distinct from P_PL_SAFTMaterialDocRecdBasic as z
    inner join   I_MaterialDocumentRecord      as a on  a.MaterialDocumentYear = z.MaterialDocumentYear
                                                    and a.MaterialDocument     = z.MaterialDocument
                                                    and a.MaterialDocumentItem = z.MaterialDocumentItem

    inner join   I_PL_SAFTMaterialDocItemBasic as v on  v.MaterialDocument     = a.MaterialDocument
                                                    and v.MaterialDocumentYear = a.MaterialDocumentYear
                                                    and v.MaterialDocumentItem = a.MaterialDocumentItem
    inner join   I_Material                    as m on  m.Material     =  a.Material
                                                    and m.MaterialType <> 'DIEN'
                                                    and m.MaterialType <> 'SERV'

  association [0..1] to I_ProductValuationBasic     as _ProductValuation             on  a.Material               = _ProductValuation.Product
                                                                                     and a.InventoryValuationType = _ProductValuation.ValuationType
                                                                                     and a.CompanyCode            = _ProductValuation.ValuationArea

  association [0..1] to P_PL_SAFTPOSupplierInvoice  as _POSupplierInvoice            on  a.PurchaseOrder     = _POSupplierInvoice.PurchasingDocument
                                                                                     and a.PurchaseOrderItem = _POSupplierInvoice.PurchasingDocumentItem

  association [0..1] to P_PL_SAFTGdsMvtBillDocument as _GoodsMovementBillingDocument on  a.DeliveryDocument     = _GoodsMovementBillingDocument.DeliveryDocument
                                                                                     and a.DeliveryDocumentItem = _GoodsMovementBillingDocument.DeliveryDocumentItem

{
  key a.CompanyCode,
  key z.PL_GoodsMovementTypeCategory,
  key a.Plant,
  key a.StorageLocation,
  key z.MaterialDocumentYear,
  key z.MaterialDocument,
  key z.MaterialDocumentItem,

      a.FiscalYear,

      a.Material,
      a.InventoryValuationType,


      a.Customer,
      a.Supplier,

      a.IssuingOrReceivingPlant,
      a.IssuingOrReceivingStorageLoc,
      a.IssgOrRcvgSpclStockInd,
      a.GoodsReceiptType,

      a.DocumentDate,
      a.PostingDate,

      a.PurchaseOrder,
      a.PurchaseOrderItem,

      a.DeliveryDocument,
      a.DeliveryDocumentItem,

      a.BillOfLading,

      a.GoodsMovementType,
      a.DebitCreditCode,

      a.IsAutomaticallyCreated,
      a.InventorySpecialStockType,

      a.MasterFixedAsset,
      a.FixedAsset,
      a.ControllingArea,
      a.CostCenter,
      a.ManufacturingOrder,
      a.WBSElementInternalID,
      a.GLAccount,

      @Semantics.unitOfMeasure: true
      @ObjectModel.foreignKey.association: '_MaterialBaseUnit'
      a.MaterialBaseUnit,
      @Semantics.quantity.unitOfMeasure: 'MaterialBaseUnit'
      case when a.DebitCreditCode = 'H'
        then - a.QuantityInBaseUnit
        else a.QuantityInBaseUnit
      end             as QuantityInBaseUnit,

      @Semantics.currencyCode: true
      a.CompanyCodeCurrency,

      //Material unit price

      @Semantics.amount.currencyCode: 'CompanyCodeCurrency'
      abs( cast( case
        when a.TotalGoodsMvtAmtInCCCrcy <> 0
          then division(TotalGoodsMvtAmtInCCCrcy, a.QuantityInBaseUnit,2)
        when a.TotalGoodsMvtAmtInCCCrcy = 0 and v.StockValueInCCCrcy <> 0 and v.TotalValStockQuantity <> 0
          then division(v.StockValueInCCCrcy,v.TotalValStockQuantity,2)
        when z.PL_MMDocumentHasAlwaysPriceAmt = 'X' and _ProductValuation.MovingAveragePrice is not null
          then _ProductValuation.MovingAveragePrice
        else 0
      end as netpr) ) as NetPriceInCompanyCodeCrcy,

      //Material movement total amount

      @Semantics.amount.currencyCode: 'CompanyCodeCurrency'
      cast( case
        when a.TotalGoodsMvtAmtInCCCrcy <> 0
          then
            case when a.DebitCreditCode = 'H'
              then - a.TotalGoodsMvtAmtInCCCrcy
              else a.TotalGoodsMvtAmtInCCCrcy
            end
        when a.TotalGoodsMvtAmtInCCCrcy = 0 and v.StockValueInCCCrcy <> 0 and v.TotalValStockQuantity <> 0
          then
            case when a.DebitCreditCode = 'H'
              then  - a.QuantityInBaseUnit * division(v.StockValueInCCCrcy,v.TotalValStockQuantity,2)
              else (a.QuantityInBaseUnit * division(v.StockValueInCCCrcy,v.TotalValStockQuantity,2))
            end
        when z.PL_MMDocumentHasAlwaysPriceAmt = 'X' and _ProductValuation.MovingAveragePrice is not null
          then
            case when a.DebitCreditCode = 'H'
              then - _ProductValuation.MovingAveragePrice * a.QuantityInBaseUnit
              else _ProductValuation.MovingAveragePrice * a.QuantityInBaseUnit
            end
        else 0
      end as ck_gwbk) as TotalAmountInCoCodeCrcy,

      _Plant,
      _StorageLocation,

      _IssuingOrReceivingPlant,
      _IssuingOrReceivingStorageLoc,

      _Material,
      _MaterialBaseUnit,
      _ProductValuation,

      _Customer,
      _Supplier,

      _PurchaseOrder,
      _PurchaseOrderItem,

      _DeliveryDocument,
      _DeliveryDocumentItem,

      _GoodsMovementBillingDocument,

      _POSupplierInvoice,

      _MasterFixedAsset,
      _FixedAsset,
      _CostCenter,
      _ManufacturingOrder,
      _WBSElement,
      _GLAccount


}
where
       z.PL_GoodsMovementTypeCategory <> 'MM'
  or(
       a.Plant                        <> a.IssuingOrReceivingPlant
    or a.StorageLocation              <> a.IssuingOrReceivingStorageLoc
    or a.Material                     <> a.IssgOrRcvgMaterial
  )
/*+[internal] {
"BASEINFO":
{
"FROM":
[
"I_MATERIAL",
"I_MATERIALDOCUMENTRECORD",
"I_PL_SAFTMATERIALDOCITEMBASIC",
"I_PRODUCTVALUATIONBASIC",
"P_PL_SAFTMATERIALDOCRECDBASIC"
],
"ASSOCIATED":
[
"I_COSTCENTER",
"I_CUSTOMER",
"I_DELIVERYDOCUMENT",
"I_DELIVERYDOCUMENTITEM",
"I_FIXEDASSET",
"I_GLACCOUNT",
"I_MANUFACTURINGORDER",
"I_MASTERFIXEDASSET",
"I_MATERIAL",
"I_PLANT",
"I_PRODUCTVALUATIONBASIC",
"I_PURCHASEORDER",
"I_PURCHASEORDERITEM",
"I_STORAGELOCATION",
"I_SUPPLIER",
"I_UNITOFMEASURE",
"I_WBSELEMENTBYINTERNALKEY",
"P_PL_SAFTGDSMVTBILLDOCUMENT",
"P_PL_SAFTPOSUPPLIERINVOICE"
],
"BASE":
[
"I_MATERIALDOCUMENTRECORD"
],
"ANNO_REF":
[],
"SCALAR_FUNCTION":
[],
"VERSION":0,
"ANNOREF_EVALUATION_ERROR":""
}
}*/