P_PURCHASEORDERHISTDETAIL

@AbapCatalog.sqlViewName: 'PURORDHISTDET'
@AbapCatalog.compiler.compareFilter: true
@VDM.viewType: #CONSUMPTION
//@Analytics.dataCategory: #CUBE

@VDM.private: true
@AccessControl.authorizationCheck: #NOT_REQUIRED
@EndUserText.label: 'PurchaseOrderHistDetail'
@AccessControl.personalData.blocking: #NOT_REQUIRED

define view P_PurchaseOrderHistDetail

  with parameters
    P_StartDate : bedat,
    P_EndDate   : bedat
  as select from I_PurchaseOrderHistory

{

  key PurchaseOrder,

  key PurchaseOrderItem,

  key PurchaseOrderTransactionType,
      //_PurchaseOrderItem._PurchaseOrder._CompanyCode,

      //_PurchaseOrderItem._PurchaseOrder._Supplier,


      I_PurchaseOrderHistory.PostingDate,

      _PurchaseOrderItem._PurchaseOrder._CompanyCode.Currency          as CompanyCodeCurrency,

      _PurchaseOrderItem.DocumentCurrency                              as PurOrdTransactionCurrency,

      PurchaseOrderHistCategory,
      _PurchaseOrderItem._PurchaseOrder.PurchaseOrderType,
      _PurchaseOrderItem._PurchaseOrder.PurchasingOrganization,
      _PurchaseOrderItem._PurchaseOrder.PurchasingGroup,
      _PurchaseOrderItem.Plant,
      _PurchaseOrderItem.PurchaseOrderCategory                         as PurchasingDocumentCategory,

      _PurchaseOrderItem.GoodsReceiptIsNonValuated, //remove

      PurOrdAmountInCompanyCodeCrcy,
      _PurchaseOrderItem.NetPriceAmount,
      I_PurchaseOrderHistory.Quantity,

       case PurchaseOrderTransactionType
        when '2' then case DebitCreditCode
                        when 'H' then -1 * PurOrdAmountInCompanyCodeCrcy
                        when 'S' then 1 * PurOrdAmountInCompanyCodeCrcy
                        else 0
                      end
        when '3' then case PurchaseOrderHistCategory
                        when 'W' then 0
                        else case DebitCreditCode
                               when 'H' then  -1 * PurOrdAmountInCompanyCodeCrcy
                               when 'S' then 1 *  PurOrdAmountInCompanyCodeCrcy
                               else 0
                             end
                      end
        else 0

      end                                     as TotalInvoiceAmountInCoCodeCrcy

}
where
  (
     PurchaseOrderTransactionType                        = '1' or
     PurchaseOrderTransactionType                        = '2'
    or PurchaseOrderTransactionType                        = '3'
  )
  and  _PurchaseOrderItem._PurchaseOrder.PurchaseOrderDate <= $parameters.P_EndDate
  and  _PurchaseOrderItem._PurchaseOrder.PurchaseOrderDate >= $parameters.P_StartDate
  

union all

select from I_PurOrdHistDelivCost

association [0..1] to I_PurchaseOrderHistCategory as _PurchaseOrderHistCategory on  $projection.PurchaseOrderHistCategory = _PurchaseOrderHistCategory.PurchaseOrderHistCategory

association [0..1] to I_PurchaseOrderItem         as _PurchaseOrderItem         on  $projection.PurchaseOrder     = _PurchaseOrderItem.PurchaseOrder
                                                                                and $projection.PurchaseOrderItem = _PurchaseOrderItem.PurchaseOrderItem

{

  key PurchaseOrder,

  key PurchaseOrderItem,

  key PurchaseOrderTransactionType,

      //_PurchaseOrderItem._PurchaseOrder._CompanyCode,

      //_PurchaseOrderItem._PurchaseOrder._Supplier,


      PostingDate,

      _PurchaseOrderItem._PurchaseOrder._CompanyCode.Currency                  as CompanyCodeCurrency,

      _PurchaseOrderItem.DocumentCurrency                                      as PurOrdTransactionCurrency,

      PurchaseOrderHistCategory,
      _PurchaseOrderItem._PurchaseOrder.PurchaseOrderType,
      _PurchaseOrderItem._PurchaseOrder.PurchasingOrganization,
      _PurchaseOrderItem._PurchaseOrder.PurchasingGroup,
      _PurchaseOrderItem.Plant,
      _PurchaseOrderItem.PurchaseOrderCategory                                 as PurchasingDocumentCategory,

      _PurchaseOrderItem.GoodsReceiptIsNonValuated,

      PurOrdAmountInCompanyCodeCrcy,
      _PurchaseOrderItem.NetPriceAmount,
      PurchaseOrderItemQuantity                                                as Quantity,

       case PurchaseOrderTransactionType
        when '2' then    case QuantityDebitCreditCode
                           when 'H' then case  _PurchaseOrderItem.IsReturnsItem
                                           when '' then -1 * PurOrdAmountInCompanyCodeCrcy
                                           else 1 * PurOrdAmountInCompanyCodeCrcy
                                          end
                           when 'S' then case  _PurchaseOrderItem.IsReturnsItem
                                           when '' then 1 * PurOrdAmountInCompanyCodeCrcy
                                           else -1 * PurOrdAmountInCompanyCodeCrcy
                                          end
                           else 1 * PurOrdAmountInCompanyCodeCrcy
                         end
        when '3' then   case QuantityDebitCreditCode
                          when 'H' then case _PurchaseOrderHistCategory.SubsequentDebitCreditIsAllowed
                                          when '' then 0
                                          else -1 * PurOrdAmountInCompanyCodeCrcy
                                        end
                          else case _PurchaseOrderHistCategory.SubsequentDebitCreditIsAllowed
                                 when '' then 0
                                 else 1 * PurOrdAmountInCompanyCodeCrcy
                               end
                        end
        else 0
      end                                              as TotalInvoiceAmountInCoCodeCrcy


}
where
  (
    PurchaseOrderTransactionType                        = '1'
    or 
    PurchaseOrderTransactionType                        = '2'
    or PurchaseOrderTransactionType                        = '3'
  )
  and  _PurchaseOrderItem._PurchaseOrder.PurchaseOrderDate <= $parameters.P_EndDate
  and  _PurchaseOrderItem._PurchaseOrder.PurchaseOrderDate >= $parameters.P_StartDate