P_SalesQuotationSituation

@ClientHandling.algorithm: #SESSION_VARIABLE
@ObjectModel.usageType.dataClass: #MIXED
@ObjectModel.usageType.serviceQuality: #D
@ObjectModel.usageType.sizeCategory: #S
@AbapCatalog.sqlViewName: 'PSLSQTANSTN'
@AbapCatalog.compiler.compareFilter: true
@AbapCatalog.preserveKey: true
@AccessControl.authorizationCheck: #NOT_REQUIRED
@VDM.private: true
@VDM.viewType: #COMPOSITE

define view P_SalesQuotationSituation
  as select from           I_SalesDocument            as SD
    left outer to one join P_ConvertedSalesQuotations as CnvrtdSalesQuotation on SD.SalesDocument = CnvrtdSalesQuotation.SalesQuotation
    left outer to one join I_SDDocStandardPartner     as StandardPartner      on SD.SalesDocument = StandardPartner.SDDocument
    left outer to one join P_SalesQtanPrdtvAmt        as Predict              on SD.SalesDocument = Predict.SalesQuotation
{
      // Key

  key cast(SalesDocument as sales_quotation preserving type)          as SalesQuotation,
      SDDocumentCategory,
      @ObjectModel.foreignKey.association: '_SalesDocumentType'
      SalesDocumentType,

      //Organization

      @ObjectModel.foreignKey.association: '_SalesOrganization'
      SalesOrganization,
      @ObjectModel.foreignKey.association: '_DistributionChannel'
      DistributionChannel,
      @ObjectModel.foreignKey.association: '_OrganizationDivision'
      OrganizationDivision,
      @ObjectModel.foreignKey.association: '_SalesGroup'
      SalesGroup,
      @ObjectModel.foreignKey.association: '_SalesOffice'
      SalesOffice,

      //Sales

      @ObjectModel.foreignKey.association: '_SoldToParty'
      SD.SoldToParty,
      @ObjectModel.foreignKey.association: '_CustomerGroup'
      SD.CustomerGroup,
      @ObjectModel.foreignKey.association: '_AdditionalCustomerGroup1'
      AdditionalCustomerGroup1,
      @ObjectModel.foreignKey.association: '_AdditionalCustomerGroup2'
      AdditionalCustomerGroup2,
      @ObjectModel.foreignKey.association: '_AdditionalCustomerGroup3'
      AdditionalCustomerGroup3,
      @ObjectModel.foreignKey.association: '_AdditionalCustomerGroup4'
      AdditionalCustomerGroup4,
      @ObjectModel.foreignKey.association: '_AdditionalCustomerGroup5'
      AdditionalCustomerGroup5,
      
   
      cast(StandardPartner.ResponsibleEmployeeWorkAgrmt as resp_empl) as ResponsibleEmployee,
      
  
      cast(StandardPartner.SalesEmployeeWorkAgreement as sales_empl)  as SalesEmployee,

    
      cast(StandardPartner.ShipToParty as kunwe)                      as ShipToParty,

      //Admin

      CreatedByUser,
      @Semantics.systemDate.createdAt: true
      SD.CreationDate,
      @Semantics.systemDate.lastChangedAt: true
      LastChangeDate,
      SalesDocumentDate                                               as SalesQuotationDate,
      BindingPeriodValidityStartDate,
      BindingPeriodValidityEndDate,
      cast($session.system_date as abap.dats(8))                      as TodayDate,

      OverallSDProcessStatus,
      OverallSDDocReferenceStatus,

      //Pricing

      @Semantics.currencyCode: true
      @ObjectModel.foreignKey.association: '_TransactionCurrency'
      SD.TransactionCurrency,
      @DefaultAggregation: #SUM
      @Semantics.amount.currencyCode: 'TransactionCurrency'
      TotalNetAmount,

      // KPI: Converted Quotation

      @DefaultAggregation: #SUM
      @Semantics.amount.currencyCode: 'TransactionCurrency'
      cast( case
        when CnvrtdSalesQuotation.CnvrtdSalesQuotationNetAmount is null
        then 0
        else CnvrtdSalesQuotation.CnvrtdSalesQuotationNetAmount
      end as cnvrtd_sls_qtan_net_amount )                             as CnvrtdSalesQuotationNetAmount,

      @DefaultAggregation: #MAX
      case
       when CnvrtdSalesQuotation.CnvrtdSalesQuotationNetAmount is null
        then 0
      else
         cast ( ( division (CnvrtdSalesQuotation.CnvrtdSalesQuotationNetAmount, TotalNetAmount,4) * 100 ) as sd_conversion_rate )
      end                                                             as SlsQtanConversionRateInPct,

      // KPI: Converted Quotation

      @DefaultAggregation: #SUM
      @Semantics.amount.currencyCode: 'TransactionCurrency'
      Predict.PrdtvSlsQtanCnvrsnAmount,

      @DefaultAggregation: #MAX
      case
       when Predict.PrdtvSlsQtanCnvrsnAmount is null
        then 0
      else
         cast ( ( division (Predict.PrdtvSlsQtanCnvrsnAmount, TotalNetAmount,4) * 100 ) as sd_prdtv_conversion_rate )
      end                                                             as PrdtvSlsQtanCnvrsnRateInPct,

      //Associtation

      _TransactionCurrency,
      _SalesDocumentType,
      _SalesOrganization,
      _DistributionChannel,
      _OrganizationDivision,
      _SalesGroup,
      _SalesOffice,
      SD._SoldToParty,
      _CustomerGroup,
      _AdditionalCustomerGroup1,
      _AdditionalCustomerGroup2,
      _AdditionalCustomerGroup3,
      _AdditionalCustomerGroup4,
      _AdditionalCustomerGroup5
}

where
       SDDocumentCategory             = 'B'
  and  BindingPeriodValidityStartDate <= $session.system_date
  and(
       BindingPeriodValidityEndDate   = '00000000'
    or BindingPeriodValidityEndDate   >= $session.system_date
  )
  and  TotalNetAmount != 0
  and  TotalNetAmount                 is not null
  and  OverallSDDocReferenceStatus != 'C'
  and  OverallSDProcessStatus != 'C'
/*+[internal] {
"BASEINFO":
{
"FROM":
[
"I_SALESDOCUMENT",
"I_SDDOCSTANDARDPARTNER",
"P_CONVERTEDSALESQUOTATIONS",
"P_SALESQTANPRDTVAMT"
],
"ASSOCIATED":
[
"I_ADDITIONALCUSTOMERGROUP1",
"I_ADDITIONALCUSTOMERGROUP2",
"I_ADDITIONALCUSTOMERGROUP3",
"I_ADDITIONALCUSTOMERGROUP4",
"I_ADDITIONALCUSTOMERGROUP5",
"I_CURRENCY",
"I_CUSTOMER",
"I_CUSTOMERGROUP",
"I_DISTRIBUTIONCHANNEL",
"I_DIVISION",
"I_SALESDOCUMENTTYPE",
"I_SALESGROUP",
"I_SALESOFFICE",
"I_SALESORGANIZATION"
],
"BASE":
[
"I_SALESDOCUMENT"
],
"ANNO_REF":
[],
"SCALAR_FUNCTION":
[],
"VERSION":0,
"ANNOREF_EVALUATION_ERROR":""
}
}*/