P_StkPostgHistBasicNoReversal

@AbapCatalog.viewEnhancementCategory: [#NONE]
@AccessControl.authorizationCheck: #NOT_REQUIRED
@EndUserText.label: 'Stock posting history documents without reversal'
@Metadata.ignorePropagatedAnnotations: true
@ObjectModel.usageType:{
  serviceQuality: #X,
  sizeCategory: #S,
  dataClass: #MIXED
}
@VDM:
      {
        viewType: #COMPOSITE,
        private: true
      }
define view entity P_StkPostgHistBasicNoReversal
  with parameters
    @Environment.systemField: #SYSTEM_DATE
    P_StartDate : sydate,
    @Environment.systemField: #SYSTEM_DATE
    P_EndDate   : sydate

  as select from           I_MaterialDocumentRecord as matdoc

    left outer to one join P_StkPostgHistAcctg      as _MatDocAccounting on  matdoc.MaterialDocument         =  _MatDocAccounting.MaterialDocument
                                                                         and matdoc.MaterialDocumentYear     =  _MatDocAccounting.MaterialDocumentYear
                                                                         and matdoc.MaterialDocumentItem     =  _MatDocAccounting.MaterialDocumentItem
                                                                         and matdoc.StockIdentifyingMaterial =  _MatDocAccounting.Product
                                                                         and matdoc.CostEstimate             <> '000000000000'
{

  '0'                                                                        as StockLevelIsValuated,
  matdoc.MaterialDocument,
  matdoc.MaterialDocumentYear,
  matdoc.MaterialDocumentItem,

  // Accounting document

  case when _MatDocAccounting.AccountingDocument is null
        then cast('' as farp_belnr_d)
       else
        _MatDocAccounting.AccountingDocument                                       
       end                                                                   as AccountingDocument,
 
  case when _MatDocAccounting.FiscalYear  is null
         then cast('0000' as gjahr)
       else
         _MatDocAccounting.FiscalYear
       end                                                                   as FiscalYear,

  matdoc.CostEstimate,
  matdoc.CompanyCode                                                         as CompanyCode,

  // stock fields for filter

  matdoc.StockIdentifyingMaterial                                            as Material,
  matdoc.Plant,
  matdoc.StockIdfgStorageLocation                                            as StorageLocation,
  matdoc.StockIdentifyingBatch                                               as Batch,
  matdoc.SpecialStockIdfgSupplier                                            as Supplier,
  matdoc.SpecialStockIdfgSalesOrder                                          as SalesOrder,
  matdoc.SpecialStockIdfgSalesOrderItem                                      as SalesOrderItem,
  matdoc.SpecialStockIdfgWBSElement                                          as WBSElementInternalID,
  matdoc.SpecialStockIdfgCustomer                                            as Customer,
  matdoc.InventoryStockType,
  matdoc.InventorySpecialStockType,
  matdoc.SpecialStockIdfgStockOwner,
  matdoc.ResourceID,
  matdoc.InventoryValuationType,

  // stock quantity

  matdoc.MaterialBaseUnit,
  @Semantics.quantity.unitOfMeasure: 'MaterialBaseUnit'
  matdoc.MatlStkChangeQtyInBaseUnit,

  matdoc.CompanyCodeCurrency,
  @Semantics.amount.currencyCode: 'CompanyCodeCurrency'
  case
    when matdoc.CostEstimate = '000000000000' // when cost estimate is initial, value is 0

     then cast (0 as nsdm_stk_val_cccrcy)
    else cast(matdoc.GoodsMovementStkAmtInCCCrcy as nsdm_stk_val_cccrcy) end as AmountInCompanyCodeCurrency,

  // reference documents

  matdoc.PurchaseOrder,
  matdoc.PurchaseOrderItem,
  matdoc.ManufacturingOrder,
  matdoc.ManufacturingOrderItem,
  matdoc.DeliveryDocument,
  matdoc.DeliveryDocumentItem,
  matdoc.Reservation,
  matdoc.ReservationItem,

  // other fields

  matdoc.GoodsMovementType,
  matdoc.GoodsMovementRefDocType,
  matdoc.GoodsReceiptType,
  matdoc.ConsumptionPosting,
  matdoc.PostingDate
}
where
       matdoc.PostingDate                   <= $parameters.P_EndDate
  and  matdoc.PostingDate                   >= $parameters.P_StartDate
  and  matdoc.GoodsMovementIsCancelled      =  ''
  and  matdoc.GoodsMovementCancellationType <> '2'
  and(
       matdoc.MatlStkChangeQtyInBaseUnit    <> 0
    or matdoc.TotalGoodsMvtAmtInCCCrcy      =  0
  ) // stock_qty <> 0                                 => matdoc case

    // stock_qty = 0 and dmbtr = 0                    => matdoc case, no FI document

    // stock_qty = 0 and dmbtr <> 0 (dmbtr_stock = 0) => acdoca case, because costestimate is initial in matdoc but FI document is created



union all

// FI document with reference to Material Document which has stock quantity posting 0, but value change is not 0, e.g. movement 121

select from  P_StkPostgHistAcctgNoStkQty as acdoca

  inner join I_MaterialDocumentRecord    as _matdoc on  acdoca.MaterialDocument            = _matdoc.MaterialDocument
                                                    and acdoca.MaterialDocumentYear        = _matdoc.MaterialDocumentYear
                                                    and acdoca.MaterialDocumentItem        = _matdoc.MaterialDocumentItem
                                                    and acdoca.Material                    = _matdoc.StockIdentifyingMaterial
                                                    and acdoca.InventoryValuationType      = _matdoc.InventoryValuationType
                                                    and acdoca.CompanyCode                 = _matdoc.CompanyCode

{

  '0'                                    as StockLevelIsValuated,
  acdoca.MaterialDocument,
  acdoca.MaterialDocumentYear,
  acdoca.MaterialDocumentItem,

  acdoca.AccountingDocument,
  acdoca.FiscalYear,

  acdoca.CostEstimate,
  acdoca.CompanyCode,

  // stock fields

  _matdoc.StockIdentifyingMaterial       as Material,
  _matdoc.Plant,
  _matdoc.StockIdfgStorageLocation       as StorageLocation,
  _matdoc.StockIdentifyingBatch          as Batch,
  _matdoc.SpecialStockIdfgSupplier       as Supplier,
  _matdoc.SpecialStockIdfgSalesOrder     as SalesOrder,
  _matdoc.SpecialStockIdfgSalesOrderItem as SalesOrderItem,
  _matdoc.SpecialStockIdfgWBSElement     as WBSElementInternalID,
  _matdoc.SpecialStockIdfgCustomer       as Customer,
  _matdoc.InventoryStockType,
  _matdoc.InventorySpecialStockType,
  _matdoc.SpecialStockIdfgStockOwner,
  _matdoc.ResourceID,
  _matdoc.InventoryValuationType,

  // stock quantity

  acdoca.MaterialBaseUnit,
  acdoca.MatlStkChangeQtyInBaseUnit,

  acdoca.CompanyCodeCurrency,
  acdoca.AmountInCompanyCodeCurrency,

  // reference documents

  _matdoc.PurchaseOrder,
  _matdoc.PurchaseOrderItem,
  _matdoc.ManufacturingOrder,
  _matdoc.ManufacturingOrderItem,
  _matdoc.DeliveryDocument,
  _matdoc.DeliveryDocumentItem,
  _matdoc.Reservation,
  _matdoc.ReservationItem,

  // other fields

  _matdoc.GoodsMovementType,
  _matdoc.GoodsMovementRefDocType,
  _matdoc.GoodsReceiptType,
  _matdoc.ConsumptionPosting,
  acdoca.PostingDate
}
where
      acdoca.PostingDate >= $parameters.P_StartDate
  and acdoca.PostingDate <= $parameters.P_EndDate

union all

select from              P_StkPostgHistCancelBasic( P_StartDate: $parameters.P_StartDate, P_EndDate: $parameters.P_EndDate) as matdoc

  left outer to one join P_StkPostgHistAcctg                                                                                as _MatDocAccounting on  matdoc.MaterialDocument     =  _MatDocAccounting.MaterialDocument
                                                                                                                                                 and matdoc.MaterialDocumentYear =  _MatDocAccounting.MaterialDocumentYear
                                                                                                                                                 and matdoc.MaterialDocumentItem =  _MatDocAccounting.MaterialDocumentItem
                                                                                                                                                 and matdoc.Material             =  _MatDocAccounting.Product
                                                                                                                                                 and matdoc.CostEstimate         <> '000000000000'

{

  '0'                                                                          as StockLevelIsValuated,
  matdoc.MaterialDocument,
  matdoc.MaterialDocumentYear,
  matdoc.MaterialDocumentItem,

  // Accounting document

  case when _MatDocAccounting.AccountingDocument is null
        then cast('' as farp_belnr_d)
       else
        _MatDocAccounting.AccountingDocument                                       
       end                                                                   as AccountingDocument,
 
  case when _MatDocAccounting.FiscalYear  is null
         then cast('0000' as gjahr)
       else
         _MatDocAccounting.FiscalYear
       end                                                                   as FiscalYear,

  matdoc.CostEstimate,
  matdoc.CompanyCode,

  // stock fields

  matdoc.Material,
  matdoc.Plant,
  matdoc.StorageLocation,
  matdoc.Batch,
  matdoc.Supplier,
  matdoc.SalesOrder,
  matdoc.SalesOrderItem,
  matdoc.WBSElementInternalID,
  matdoc.Customer,
  matdoc.InventoryStockType,
  matdoc.InventorySpecialStockType,
  matdoc.SpecialStockIdfgStockOwner,
  matdoc.ResourceID,
  matdoc.InventoryValuationType,

  // stock quantity

  matdoc.MaterialBaseUnit,
  matdoc.MatlStkChangeQtyInBaseUnit,

  matdoc.CompanyCodeCurrency,
  //  cast(matdoc.GoodsMovementStkAmtInCCCrcy as nsdm_stk_val_cccrcy) as AmountInCompanyCodeCurrency,

  case
      when matdoc.CostEstimate = '000000000000' // when cost estimate is initial, value is 0

       then cast (0 as nsdm_stk_val_cccrcy)
      else cast(matdoc.GoodsMovementStkAmtInCCCrcy as nsdm_stk_val_cccrcy) end as AmountInCompanyCodeCurrency,

  // reference documents

  matdoc.PurchaseOrder,
  matdoc.PurchaseOrderItem,
  matdoc.ManufacturingOrder,
  matdoc.ManufacturingOrderItem,
  matdoc.DeliveryDocument,
  matdoc.DeliveryDocumentItem,
  matdoc.Reservation,
  matdoc.ReservationItem,

  // other fields

  matdoc.GoodsMovementType,
  matdoc.GoodsMovementRefDocType,
  matdoc.GoodsReceiptType,
  matdoc.ConsumptionPosting,
  matdoc.PostingDate

}

union all

select from  I_GLAccountLineItemRawData as acdoca
  inner join finsc_ledger on  acdoca.SourceLedger   = finsc_ledger.rldnr
                          and finsc_ledger.xleading = 'X'
{

  '1'                                           as StockLevelIsValuated,
  cast('' as mblnr)                             as MaterialDocument,
  cast('0000' as mjahr)                         as MaterialDocumentYear,
  cast('0000' as mblpo)                         as MaterialDocumentItem,

  acdoca.AccountingDocument                     as AccountingDocument,
  acdoca.FiscalYear                             as FiscalYear,

  acdoca.CostEstimate                           as CostEstimate,
  acdoca.CompanyCode                            as CompanyCode,
  // stock fields

  acdoca.Product                                as Material,
  acdoca.Plant                                  as Plant,
  ''                                            as StorageLocation,
  ''                                            as Batch,
  ''                                            as Supplier,
  ''                                            as SalesOrder,
  cast('000000' as mat_kdpos)                   as SalesOrderItem,
  cast('00000000' as mat_pspnr)                 as WBSElementInternalID,
  ''                                            as Customer,
  ''                                            as InventoryStockType,
  ''                                            as InventorySpecialStockType,
  ''                                            as SpecialStockIdfgStockOwner,
  ''                                            as ResourceID,
  acdoca.InventoryValuationType                 as InventoryValuationType,

  // stock quantity

  acdoca.CostSourceUnit                         as MaterialBaseUnit,
  acdoca.ValuationQuantity                      as MatlStkChangeQtyInBaseUnit,

  acdoca.CompanyCodeCurrency                    as CompanyCodeCurrency,
  acdoca.AmountInCompanyCodeCurrency            as AmountInCompanyCodeCurrency,

  // reference documents

  acdoca.PurchasingDocument                     as PurchaseOrder,
  acdoca.PurchasingDocumentItem                 as PurchaseOrderItem,
  acdoca.OrderID                                as ManufacturingOrder,
  acdoca.OrderItem                              as ManufacturingOrderItem,
  ''                                            as DeliveryDocument,
  cast('000000' as nsdm_delivery_document_item) as DeliveryDocumentItem,
  cast('0000000000' as rsnum)                   as Reservation,
  cast('0000' as nsdm_rspos)                    as ReservationItem,

  // other fields

  ''                                            as GoodsMovementType,
  ''                                            as GoodsMovementRefDocType,
  ''                                            as GoodsReceiptType,
  ''                                            as ConsumptionPosting,
  acdoca.PostingDate                            as PostingDate

}
where
      acdoca.PostingDate                  >= $parameters.P_StartDate
  and acdoca.PostingDate                  <= $parameters.P_EndDate
  and acdoca.ReferenceDocumentType        <> 'MKPF'
  and acdoca.AccountingDocument           <> ''
  and acdoca.TransactionTypeDetermination =  'BSX'
/*+[internal] {
"BASEINFO":
{
"FROM":
[
"I_GLACCOUNTLINEITEMRAWDATA",
"I_MATERIALDOCUMENTRECORD",
"P_STKPOSTGHISTACCTG",
"P_STKPOSTGHISTACCTGNOSTKQTY",
"P_STKPOSTGHISTCANCELBASIC",
"FINSC_LEDGER"
],
"ASSOCIATED":
[],
"BASE":
[],
"ANNO_REF":
[],
"SCALAR_FUNCTION":
[],
"VERSION":0,
"ANNOREF_EVALUATION_ERROR":""
}
}*/