P_PURCONTRACTPRICEVARIANCE1

@AbapCatalog.sqlViewName: 'PMMPCPRCVAR1'
@AbapCatalog.compiler.compareFilter: true
@AccessControl.authorizationCheck: #CHECK
@VDM.viewType : #CONSUMPTION
@VDM.private: true
@AccessControl.personalData.blocking: #NOT_REQUIRED
define view P_PurContractPriceVariance1 
with parameters 
      P_Material        : matnr,
      P_StartDate       : vdm_validitystart,
      P_EndDate         : vdm_validityend
      
  as select from I_PurchaseContractItem  as ContractItem
  left outer join P_ChangeDocumentItem2 ( P_StartDate: $parameters.P_StartDate,
                                                         P_EndDate: $parameters.P_EndDate ) as ChangeItem on ContractItem.PurchaseContract       = ChangeItem.ChangeDocObject
                                                    and ContractItem.PurchaseContractItem  = ChangeItem.Item  
                                                     and ChangeItem.DatabaseTable = 'EKPO'
  left outer join P_ChangeDocumentItem3 ( P_StartDate: $parameters.P_StartDate,
                                                         P_EndDate: $parameters.P_EndDate ) as NumberOfChangeItems on ContractItem.PurchaseContract = NumberOfChangeItems.ChangeDocObject
                                                                and ContractItem.PurchaseContractItem = NumberOfChangeItems.Item
                                                                
  association [1..1] to I_PurchasingCategoryMatlGroup as _PurchasingCategoryMatlGroup on  $projection.MaterialGroup = _PurchasingCategoryMatlGroup.MaterialGroup

  
                                      
{
  key ContractItem.PurchaseContract,                                                                 
  key PurchaseContractItem,
  key ChangeItem.ChangeDocument,
  
      _PurchaseContract.CreationDate,
      _PurchaseContract.PurchasingOrganization,
      _PurchaseContract.PurchasingGroup,
      _PurchaseContract.Supplier,
      _PurchaseContract.CompanyCode,
      
 //     ContractItem.ContractNetPriceAmount,

      Material,
      MaterialGroup,
      ContractItem.Plant,
      _PurchasingCategoryMatlGroup._PurchasingCategory.PurchasingCategory                                              as PurchasingCategory,
      
      ContractItem.DocumentCurrency as ContractCurrency, 
      ChangeItem.Currency,
      
      case when ChangeDocument is not initial and ChangeItem.Currency is not initial 
              then ChangeItem.Currency
           else ContractItem.DocumentCurrency 
      end as DocumentCurrency,      

    
      case when ChangeDocument is not initial
              then CreationDate 
           else  _PurchaseContract.CreationDate
      end as DocumentChangeDate,
      
      case when NetPriceAmount is not null
            then  cast(NetPriceAmount + division(cast(ValueAfterDecimal as abap.dec( 4, 2 )), 100,2) as abap.curr( 21, 2 )) 
           else ContractItem.ContractNetPriceAmount 
      end as ContractNetPriceAmount,
      
      case
           when OrdPriceUnitToOrderUnitDnmntr = 0
                then 0 
                else DIVISION(OrderPriceUnitToOrderUnitNmrtr, OrdPriceUnitToOrderUnitDnmntr, 1 ) * TargetQuantity
                end
        as QuantityInBaseUnit,

      NetPriceQuantity,
      OrderPriceUnit,        
      OrderQuantityUnit,
      TargetQuantity,
      TargetAmount,
      IsDocumentChanged,
      NumberOfChanges,
      ChangeItem.NumberOfPriceChanges,
      NumberOfChangeItems.NumberOfPriceChanges as TotalNumberOfChanges,
      _Material,
      _MaterialGroup,
      _Plant,
      _PurchasingCategoryMatlGroup,
      ContractItem._PurchaseContract._Supplier,
      ContractItem._PurchaseContract._CompanyCode,
      ContractItem._PurchaseContract._PurchasingGroup,
      ContractItem._PurchaseContract._PurchasingOrganization
  
}
 where PurchasingContractDeletionCode =  ''
 and _PurchaseContract.PurchasingDocumentDeletionCode != 'X'
 and _PurchaseContract.IsEndOfPurposeBlocked = ''
 and Material = $parameters.P_Material
 and _PurchaseContract.CreationDate between $parameters.P_StartDate and $parameters.P_EndDate
  
  
  
//     as select from P_PURCONTRACTPRICEVARIANCE ( P_Material : $parameters.P_Material, P_StartDate: $parameters.P_StartDate,

//                                                         P_EndDate: $parameters.P_EndDate )     as PriceChange

//    {

//    

//        key PurchaseContract,                                                                 

//        PurchaseContractItem,

//        ChangeDocument,

////  

//        CreationDate,

//        PurchasingOrganization,

//        PurchasingGroup,

//        Supplier,

//        CompanyCode,

////      

//        ContractNetPriceAmount,

//        Material,

//        MaterialGroup,

//        Plant,

//        PurchasingCategory                                              as PurchasingCategory,

////      

//        ContractCurrency, 

//        Currency,

//        DocumentCurrency,      

//        DocumentChangeDate,

////      

////      case when NetPriceAmount is not null

////            then  cast(NetPriceAmount + division(cast(ValueAfterDecimal as abap.dec( 4, 2 )), 100,2) as abap.curr( 21, 2 )) 

////           else ContractItem.ContractNetPriceAmount 

////      end as ContractNetPriceAmount,

////      

//        QuantityInBaseUnit,

////

////      

//      OrderQuantityUnit,

//      TargetQuantity,

//      TargetAmount,

//      IsDocumentChanged,

//      NumberOfChanges,

//      NumberOfPriceChanges,

//      TotalNumberOfChanges,

//      _Material,

//      _MaterialGroup,

//      _Plant,

//        _PurchasingCategoryMatlGroup,

//        _Supplier,

//        _CompanyCode,

//        _PurchasingGroup,

//        _PurchasingOrganization

//    

//    }  

/*+[internal] {
"BASEINFO":
{
"FROM":
[
"I_PURCHASECONTRACT",
"I_PURCHASECONTRACTITEM",
"I_PURCHASINGCATEGORY",
"I_PURCHASINGCATEGORYMATLGROUP",
"P_CHANGEDOCUMENTITEM2",
"P_CHANGEDOCUMENTITEM3"
],
"ASSOCIATED":
[
"I_COMPANYCODE",
"I_MATERIAL",
"I_MATERIALGROUP",
"I_PLANT",
"I_PURCHASINGCATEGORYMATLGROUP",
"I_PURCHASINGGROUP",
"I_PURCHASINGORGANIZATION",
"I_SUPPLIER"
],
"BASE":
[
"I_PURCHASECONTRACT",
"I_PURCHASECONTRACTITEM"
],
"ANNO_REF":
[],
"SCALAR_FUNCTION":
[],
"VERSION":0,
"ANNOREF_EVALUATION_ERROR":""
}
}*/