R_ProdSpecCmpstnHdrWithUsage

@AbapCatalog.sqlViewName: 'RPSCMPHWUSG'
@AbapCatalog.compiler.compareFilter: true
@AbapCatalog.preserveKey: true

@AccessControl.authorizationCheck: #NOT_REQUIRED

@ClientHandling.algorithm: #SESSION_VARIABLE

@EndUserText.label: 'Product Spec. Comp. Header with Usage'
@ObjectModel.usageType.sizeCategory: #L
@ObjectModel.usageType.serviceQuality: #C
@ObjectModel.usageType.dataClass: #MASTER

@VDM.lifecycle.contract.type: #SAP_INTERNAL_API
@VDM.viewType: #COMPOSITE


define view R_ProdSpecCmpstnHdrWithUsage
  as select from I_SpecValAssgmtHeader as header
    inner join   I_SpecValAssgmt       as va       on  header.SpecificationInternalID = va.SpecificationInternalID
                                                   and header.SpecValAssgmtHdr        = va.SpecValAssgmtHdr
    inner join   I_SpecValAssgmtUsage  as usage    on  va.SpecValAssgmt                  =  usage.MasterTableElementInternalID
                                                   and header.SpecificationInternalID    =  usage.SpecificationInternalID
                                                   and va.SpecValAssgmtValidityStartDate <= usage.SpecValAssgmtUsgeValdtyEndDte
                                                   and va.SpecValAssgmtValidityEndDate   >= usage.SpecValAssgmtUsgeValdtyStrtDte
    inner join   I_ProdSpecNonHistoric as prodspec on prodspec.ProductSpecificationInternalID = header.SpecificationInternalID

  association [1..*] to I_ProductSpecification        as _ProductSpecification        on _ProductSpecification.ProductSpecificationInternalID = $projection.ProductSpecificationInternalID
  association [1..1] to I_ProdSpecNonHistoric         as _ProdSpecNonHistoric         on _ProdSpecNonHistoric.ProductSpecificationInternalID = $projection.ProductSpecificationInternalID
  association [1..1] to I_ProdSpecificationLastChange as _ProdSpecificationLastChange on _ProdSpecificationLastChange.ProductSpecificationInternalID = $projection.ProductSpecificationInternalID

{

  key      cast(va.SpecValAssgmt as /plmb/spc_prod_comp_hdr_id preserving type )                    as ProdSpecCmpstnHeader,
  key      cast(va.SpecValAssgmtChangeState as /plmb/spc_prod_comp_hdr_ch_st preserving type )      as ProdSpecCmpstnHdrChangeState,
  key      usage.SpecValAssgmtUsageChangeState,
  key      usage.SpecValAssgmtUsageInternalID,

           cast(header.SpecificationInternalID as /plmb/spc_prod_int_id preserving type )           as ProductSpecificationInternalID,
           cast(header.SpecValAssgmtType as /plmb/spc_comp_hdr_type preserving type )               as ProdSpecCmpstnHdrValAssgmtType,

           usage.SpecValAssgmtUsgeValdtyStrtDte,
           usage.SpecValAssgmtUsgeValdtyEndDte,

           cast(usage.SpecificationRating as /plmb/spc_comp_hdr_rating preserving type )            as ProdSpecCmpstnHdrRating,
           cast(usage.SpecificationValidityArea as /plmb/spc_comp_hdr_val_area preserving type )    as ProdSpecCmpstnHdrValidityArea,

           usage.SpecValAssgmtUsageIsActive,
           usage.SpecValAssgmtUsageIsDeleted,
           usage.SpecValAssgmtUsageIsRelevant,
           va.SpecValAssgmtIsDeleted                                                                as ProdSpecCmpstnHdrIsDeleted,
           cast(va.SpecValAssgmtChangeNumber as /plmb/spc_comp_hdr_ch_number preserving type )      as ProdSpecCmpstnHdrChangeNumber,
           cast(va.SpecValAssgmtSortSequence as /plmb/spc_comp_hdr_sort_seq preserving type )       as ProdSpecCmpstnHdrSortSequence,

           cast(va.SpecValAssgmtValidityStartDate as /plmb/spc_comp_hval_strt_dat preserving type ) as ProdSpecCmpstnHdrValdtyStrtDte,
           cast(va.SpecValAssgmtValidityEndDate as /plmb/spc_comp_hval_end_dat preserving type )    as ProdSpecCmpstnHdrValdtyEndDte,

           // reference value exposure

           va.SpecValAssgmtRefValueCode,

           _ProductSpecification,
           _ProdSpecNonHistoric,
           _ValueAssignmentType,
           va._SpecValAssgmtReferenceValue,
           _ProdSpecificationLastChange
}
where
      SpecValAssgmtUsageIsActive   = 'X'
  and SpecValAssgmtInheritanceRoot = '00000000000000000000' // data should not come via inheritance

  and SpecValAssgmtUsageIsDeleted  = ''                     // usage is not exposed in above layers, deleted entries are removed here

  and SpecValAssgmtUsageIsExcluded = ''
/*+[internal] {
"BASEINFO":
{
"FROM":
[
"I_PRODSPECNONHISTORIC",
"I_SPECVALASSGMT",
"I_SPECVALASSGMTHEADER",
"I_SPECVALASSGMTUSAGE"
],
"ASSOCIATED":
[
"I_PRODSPECIFICATIONLASTCHANGE",
"I_PRODSPECNONHISTORIC",
"I_PRODUCTSPECIFICATION",
"I_SPECVALASSGMTREFERENCEVALUE",
"I_SPECVALASSGMTTYPE"
],
"BASE":
[
"I_SPECVALASSGMT",
"I_SPECVALASSGMTHEADER"
],
"ANNO_REF":
[],
"SCALAR_FUNCTION":
[],
"VERSION":0,
"ANNOREF_EVALUATION_ERROR":""
}
}*/