P_PrcSttgMethExecutionAnalysis

@AbapCatalog.sqlViewName: 'PCMM_PSMPC01'
//@EndUserText.label: 'Price Setting Method Execution Analysis'


@AbapCatalog.compiler.compareFilter: true
@VDM.private: true
@VDM.viewType: #BASIC
@ClientHandling.algorithm: #SESSION_VARIABLE

@ObjectModel.usageType.dataClass: #TRANSACTIONAL
@ObjectModel.usageType.serviceQuality: #D
@ObjectModel.usageType.sizeCategory: #M

@AccessControl.authorizationCheck:  #NOT_ALLOWED
@AccessControl.personalData.blocking: #BLOCKED_DATA_EXCLUDED

define view P_PrcSttgMethExecutionAnalysis
  as select from    cped_terminput   as terminput
    left outer join cped_termgrpout  as termgrpout        on terminput.terminput_guid = termgrpout.terminput_guid
    left outer join cped_formdoc     as formdoc           on termgrpout.forminput_guid = formdoc.forminput_guid
    left outer join cped_knumvtoguid as knumvtoguid       on knumvtoguid.docitem_guid = formdoc.docitem_guid
    left outer join ekko             as ekko              on ekko.knumv = knumvtoguid.knumv
    left outer join ekpo             as ekpo              on  ekko.ebeln = ekpo.ebeln
                                                          and ekpo.ebelp = substring(
      knumvtoguid.kposn, 2, 5
    )
    left outer join vbak             as vbak              on vbak.knumv = knumvtoguid.knumv
    left outer join vbap             as vbap              on  vbak.vbeln        = vbap.vbeln
                                                          and knumvtoguid.kposn = vbap.posnr
    left outer join wbhk             as GTMHeaderSales    on GTMHeaderSales.knumv_sd = knumvtoguid.knumv
    left outer join wbhi             as GTMItemSales      on  GTMItemSales.tkonn = GTMHeaderSales.tkonn
                                                          and GTMItemSales.tposn = knumvtoguid.kposn
    left outer join wbhd             as GTMHeaderPurchase on GTMHeaderPurchase.knumv_mm = knumvtoguid.knumv
    left outer join wbhi             as GTMItemPurchase   on  GTMItemPurchase.tkonn = GTMHeaderPurchase.tkonn
                                                          and GTMItemPurchase.tposn = knumvtoguid.kposn
    left outer join cmmfdor_d_doc    as orderdoc          on cmmdtyordreqdocexternalid = terminput.psmguid
    left outer join cmmfdor_d_order  as orderreq          on orderreq.commodityorderrequestuuid = orderdoc.commodityorderrequestuuid
    left outer join cmmfdof_d_flpkt  as fillpacket        on fillpacket.commodityorderrequestuuid = orderdoc.commodityorderrequestuuid
                                                          and fillpacket.commodityorderfillpacketuuid = terminput.fillpacketuuid
    left outer join cmmfdof_d_fill   as fill              on fill.commodityorderfillpacketuuid = fillpacket.commodityorderfillpacketuuid
                                                           
{


  key knumvtoguid.knumv                                              as PricingDocument,
  key knumvtoguid.kposn                                              as PricingDocumentItem,
  key formdoc.cond_type                                              as ConditionType,
  key termgrpout.termno                                              as ConditionTermRateFixation,

      cast ( terminput.number_of_lots as astueck )                   as NumberOfCommodityContracts,
      terminput.psmethod                                             as PriceSettingMethod,
      terminput.psmstatus                                            as PriceSettingMethodStatus,
      terminput.dcsid                                                as DerivativeContrSpecification,
      terminput.mic                                                  as MarketIdentifierCode,
      terminput.pricetype                                            as PriceQuotationType,
      terminput.keydate                                              as MaturityKeyDate,

      termgrpout.termcommquan                                        as ConditionTermQuantity,
      termgrpout.termcommuom                                         as ConditionTermUnit,

      ekko.bstyp                                                     as PurchasingDocumentCategory,
      cast ( ekko.ebeln as fis_ebeln )                               as PurchasingDocument,


      case when (vbak.vbtyp is null ) then ' #'
           else vbak.vbtyp end                                       as SDDocumentCategory,
      vbak.vbeln                                                     as SalesDocument,

      // cast (

      //  case when (vbak.vbtyp is null ) then ' #'

      //       else vbak.vbtyp end  as vbtyp)    as SDDocumentCategory,

      //  vbak.vbeln                             as SalesDocument,


      case when ( GTMHeaderPurchase.tkonn is null ) then GTMHeaderSales.tkonn
           else GTMHeaderPurchase.tkonn end                          as ContractNum,

      case when ( GTMItemPurchase.tposn is null ) then GTMItemSales.tposn
           else GTMItemPurchase.tposn end                            as TradingContractItem,

      case when ( ekpo.matnr is not null ) then ekpo.matnr
           when ( GTMItemPurchase.matnr is not null ) then GTMItemPurchase.matnr
           when ( GTMItemSales.matnr is not null ) then GTMItemSales.matnr
           else vbap.matnr end                                       as Material,

      cast (
      case when ( ekpo.menge is not null ) then ekpo.menge
           when ( GTMItemPurchase.menge is not null ) then GTMItemPurchase.menge
           when ( GTMItemSales.menge is not null ) then GTMItemSales.menge
           else vbap.kwmeng end    as menge_d )                      as QuantityInBaseUnit,

      case when ( ekpo.meins is not null ) then ekpo.meins
           when ( GTMItemPurchase.meins is not null ) then GTMItemPurchase.meins
           when ( GTMItemSales.meins is not null ) then GTMItemSales.meins
           else vbap.meins end                                       as BaseUnit,

      orderdoc.cmmdtyorderfillprcgupdtstatus                         as CmmdtyOrderFillPrcgUpdtStatus,
      orderdoc.cmmdtyordreqdocpricinglot                             as CmmdtyOrdReqDocPricingLot,
      orderdoc.cmmdtyordreqdocapplication                            as CmmdtyOrdReqDocApplication,
      orderdoc.cmmdtyordreqdocpricingstatus                          as CmmdtyOrdReqDocPricingStatus,
      orderdoc.cmmdtyordrequestdocumenttype                          as CmmdtyOrdRequestDocumentType,
      orderdoc.commodityorderrequestdocument                         as CommodityOrderRequestDocument,
      orderdoc.cmmdtyordreqdoctofillqty                              as CmmdtyOrdReqDocToFillQty,
      orderdoc.cmmdtyordreqdoctofillqtyunit                          as CmmdtyOrdReqDocToFillQtyUnit,       

      orderreq.commodityorderrequest                                 as CommodityOrderRequest,
      orderreq.commoditysubaccount                                   as CommoditySubAccount,
      orderreq.cmmdtyorderrequeststatus                              as CmmdtyOrderRequestStatus,
      orderreq.cmmdtyorderrequesttype                                as CmmdtyOrderRequestType,
      orderreq.cmmdtyordreqcntrptysubacct                            as CmmdtyOrdReqCntrPtySubAcct,
       
      //  cast ( orderreq.creationdatetime as timestampl )               as CreationDateTime,

      orderreq.creationdatetime                                      as CreationDateTime,
      orderreq.lastchangedatetime                                    as LastChangeDateTime,

//    fillpacket.commodityorderrequesttrader                         as CommodityOrderRequestTrader,

      fillpacket.cmmdtyorderfillpacketstatus                         as CmmdtyOrderFillPacketStatus, 
//      fillpacket.cmmdtyordfillrejectionreason                        as CmmdtyOrdFillRejectionReason,   

      fill.commodityorderfill                                        as CommodityOrderFill,
      fill.cmmdtyordfillfinancialtransid                             as CmmdtyOrdFillFinancialTransID,      
      fill.cmmdtyorderfillquantityinlots                             as CmmdtyOrderFillQuantityInLots,
      fill.cmmdtyordfilluomperlot                                    as CmmdtyOrdFillUoMPerLot,
      fill.cmmdtyordfillquantityperlot                               as CmmdtyOrdFillQuantityPerLot,      
      
      fill.cmmdtyorderfillprice                                      as CmmdtyOrderFillPrice,
      fill.cmmdtyorderfillpricecrcy                                  as CmmdtyOrderFillPriceCrcy
//    fill.cmmdtyordfillrejectionreason                              as CmmdtyOrdFillRejectionReason


}


where not ( ekko.knumv is null and vbak.knumv is null and GTMHeaderPurchase.knumv_mm is null and GTMHeaderSales.knumv_sd is null )