P_CUSTOMERORDER

@AbapCatalog.sqlViewName: 'P_CUSTORDER'
@AbapCatalog.compiler.compareFilter: true
@AccessControl.authorizationCheck: #NOT_REQUIRED
@VDM.viewType: #BASIC
@VDM.private: true
@ObjectModel.usageType: {serviceQuality: #D, sizeCategory: #XL, dataClass: #MASTER}
define view P_CustomerOrder
  as select distinct from PPH_E_VBBE      as sales
    inner join            vbak                         on sales.delnr = vbak.vbeln
    inner join            vbap                         on sales.delnr = vbap.vbeln
    inner join            kna1                         on vbak.kunnr = kna1.kunnr

    inner join            knvv                         on kna1.kunnr = knvv.kunnr
    inner join            P_MaterialInfo               on sales.matnr = P_MaterialInfo.Material
    inner join            stck_cust_table as StockCust on StockCust.mandt = $session.client
    left outer join       v_mrp_currency  as currency  on sales.werks = currency.plant
{
  key  vbak.mandt                                                                                                                                                                        as mandt,
  key  vbak.kunnr                                                                                                                                                                        as Customer,
  key  sales.werks                                                                                                                                                                       as Plant,
  key  sales.matnr                                                                                                                                                                       as Material,
  key  sales.delps                                                                                                                                                                       as PositionNumber,
  key  sales.delnr                                                                                                                                                                       as OrderNumber,
       sales.delet                                                                                                                                                                       as ReferenceDocumentScheduleLine,
       sales.dat00                                                                                                                                                                       as DeliveryDate,

       cast ( 0 - sales.mng01 as abap.quan(13,3))                                                                                                                                        as OriginalBaseAmount,
       //avg(sales.mng01)                             as QuantityDelivered,

       vbap.meins                                                                                                                                                                        as QuantityBaseUnitOfMeasure,
       unit_Conversion( quantity => sales.mng01,
                        source_unit => vbap.meins,
                        target_unit => P_MaterialInfo.BaseUnitOfMeasure,
                        error_handling =>'SET_TO_NULL' )                                                                                                                                 as QuantityInBaseUnitOfMeasure,
       P_MaterialInfo.BaseUnitOfMeasure                                                                                                                                                  as BaseUnitOfMeasure,
       kna1.name1                                                                                                                                                                        as Name,
       //kna1.katr1                                        as CustomerType,

       knvv.uebto                                                                                                                                                                        as IncreaseTolerance,
       case
       when vbap.kpein <> 0
       then
       currency_conversion( amount => vbap.netpr,
                           source_currency => vbap.waerk ,
                           round =>  'X',
                           target_currency => currency.companycodecurrency,
                           exchange_rate_date => P_MaterialInfo.todaysdate,
                           error_handling => 'SET_TO_NULL' )
       else cast( 0 as netpr )
       end                                                                                                                                                                               as PricePerUnit,

       P_MaterialInfo.Price                                                                                                                                                              as MaterialPrice,
       P_MaterialInfo.PriceUnit                                                                                                                                                          as MaterialPriceUnit,

       vbap.netpr                                                                                                                                                                        as NetPriceAmount,
       vbap.waerk                                                                                                                                                                        as DocumentCurrency,

       //In company code currency

       P_MaterialInfo.Costprice                                                                                                                                                          as StandardPrice,
       P_MaterialInfo.MaterialPriceInCoCodeCurrency                                                                                                                                      as MaterialPriceInCoCodeCurrency,
       currency.companycodecurrency                                                                                                                                                      as companycodecurrency,





       //DATS_ADD_DAYS( cast('20161005' as abap.dats) , 10 , 'FAIL' )               as Date,

       // ADD_DAYS Date will come from salvage date - customizing table percent * shelf life

       $session.system_date                                                                                                                                                              as TodaysDate, //Need to be changed

       P_MaterialInfo.Umrez                                                                                                                                                              as Umrez,
       case
       when StockCust.customertype = 'KATR1'
       then kna1.katr1
       when StockCust.customertype = 'KATR2'
       then kna1.katr2
       when StockCust.customertype = 'KATR3'
       then kna1.katr3
       when StockCust.customertype = 'KATR4'
       then kna1.katr4
       when StockCust.customertype = 'KATR5'
       then kna1.katr5
       when StockCust.customertype = 'KATR6'
       then kna1.katr6
       when StockCust.customertype = 'KATR7'
       then kna1.katr7
       when StockCust.customertype = 'KATR8'
       then kna1.katr8
       when StockCust.customertype = 'KATR9'
       then kna1.katr9
       when StockCust.customertype = 'KATR10'
       then kna1.katr10
       end                                                                                                                                                                               as CustomerType,
       case
       when StockCust.tolerancelevel = 'KATR1'
       then kna1.katr1
       when StockCust.customertype = 'KATR2'
       then kna1.katr2
       when StockCust.customertype = 'KATR3'
       then kna1.katr3
       when StockCust.customertype = 'KATR4'
       then kna1.katr4
       when StockCust.customertype = 'KATR5'
       then kna1.katr5
       when StockCust.customertype = 'KATR6'
       then kna1.katr6
       when StockCust.customertype = 'KATR7'
       then kna1.katr7
       when StockCust.customertype = 'KATR8'
       then kna1.katr8
       when StockCust.customertype = 'KATR9'
       then kna1.katr9
       when StockCust.customertype = 'KATR10'
       then kna1.katr10
       end                                                                                                                                                                               as ToleranceLevel


       // TO_INT(kna1.KATR2) AS RSLR, "To be checked for Customsing

       //ADD_DAYS(:BESTBDATE, -(:TOTAL_SLED_DAYS)* nullif(TO_INT(customers.KATR2), 100)/100) AS LAST_POSSIBLE_DATE,

       //   customer_products."QuantityDelivered" AS AVERAGE_VOLUME,

} 
  
 /*+[internal] {
"BASEINFO":
{
"FROM":
[
"PPH_E_VBBE",
"P_MATERIALINFO",
"KNA1",
"KNVV",
"STCK_CUST_TABLE",
"VBAK",
"VBAP",
"V_MRP_CURRENCY"
],
"ASSOCIATED":
[],
"BASE":
[],
"ANNO_REF":
[],
"SCALAR_FUNCTION":
[],
"VERSION":0,
"ANNOREF_EVALUATION_ERROR":""
}
}*/