I_CHMLCMPLNCMKTCVRGPRODASSGMT

@AbapCatalog.sqlViewName: 'ICCMKTCVRGPROD'

@AbapCatalog.compiler.compareFilter: true

@EndUserText.label: 'Marketability Coverages for PP and UP'

--Access Control: Authorizations Checks
@AccessControl:
{
  authorizationCheck: #CHECK
}

--Client Handling of the view
@ClientHandling:
{
  type: #INHERITED,
  algorithm: #SESSION_VARIABLE
}

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

@ObjectModel:
{
  --Performance Annotations
  usageType:
  {
    dataClass: #MIXED,
    sizeCategory: #L,
    serviceQuality: #C
  }
}

define view I_ChmlCmplncMktCvrgProdAssgmt

  // Purpose: Determine the relevant ChmlCmplncInfoUUID to display the coverage of a packaged product

  //          If coverage exist for PP then return ChmlCmplncInfoUUID of PP

  //          Else return active ChmlCmplncInfoUUID of UP

  //

  // Remark:  Do not use outer join for I_ChmlCmplncMktCvrgTP. An entry in I_ChmlCmplncMktCvrgTP is mandatory

  //          because the country is needed in view C_ChmlCmplncMktCvrgProdAssgmt for correct filtering via

  //          association [0..*] to C_ChmlCmplncMktCvrgProdAssgmt

  //

  // Remark:  Returning draft ChmlCmplncInfoUUID of UP in case a draft coverage for UP exists is not required because

  //          consumption view C_ChmlCmplncMktCvrgProdAssgmt with association [1..1] to I_ChmlCmplncMktCvrgTP

  //          is not draft enabled and can not handle the draft ChmlCmplncInfoUUID in the on condition.


  as select from    I_ChmlCmplncProdAssgmt as ProdAssgmt
    join            I_ChmlCmplncMktCvrgTP  as MktCvrgUP on MktCvrgUP.ChmlCmplncInfoUUID = ProdAssgmt.ChmlCmplncProdUUID
    left outer to one join I_ChmlCmplncMktCvrgTP  as MktCvrgPP on  MktCvrgPP.ChmlCmplncInfoUUID = ProdAssgmt.ChmlCmplncInfoUUID
                                                               and MktCvrgUP.Country            = MktCvrgPP.Country
//  -- Chemical Compliance Info of UnPackaged Product

//  association [1..1] to I_ChmlCmplncInfoTP as _ChmlCmplncInfoProduct on _ChmlCmplncInfoProduct.ChmlCmplncInfoUUID = $projection.ChmlCmplncProdUUID


  -- Chemical Compliance Info of Packaged Product
  association [1..1] to I_ChmlCmplncInfoTP as _ChmlCmplncInfo        on _ChmlCmplncInfo.ChmlCmplncInfoUUID = $projection.PckgdChmlCmplncInfoUUID

{

         // Key of Product Assignment

  key    ChmlCmplncProdAssgmtUUID,

         // Covered Country

  key    MktCvrgUP.Country,

         // Chemical Compliance Info UUID of Unpackaged Product

         ProdAssgmt.ChmlCmplncProdUUID as  ChmlCmplncProdUUID,

//         // Active Chemical Compliance Info UUID of Unpackaged Product

//         _ChmlCmplncInfoProduct.ActiveChmlCmplncInfoUUID,


         // Chemical Compliance Info UUID of Packaged Product

         ProdAssgmt.ChmlCmplncInfoUUID as  PckgdChmlCmplncInfoUUID,

         // Chemical Compliance Info UUID of Product (Unpackaged or Packaged Product) from which coverage should be displayed

         case  MktCvrgPP.Country
         // If coverage for packaged product exists then return UUID of packaged product

           when  MktCvrgUP.Country then ProdAssgmt.ChmlCmplncInfoUUID
         // If coverage for packaged product not exists then return UUID of unpackaged product

           else ProdAssgmt.ChmlCmplncProdUUID
         end                           as  ChmlCmplncInfoUUID,

         -- Associations
         _ChmlCmplncInfo
//         _ChmlCmplncInfoProduct

}

// Union is required in case a new coverage is created. In this case the new coverage draft instance

// is not contained in I_ChmlCmplncMktCvrgTP but in I_ChmlCmplncInfoDraft (ehfndw_cci_mcvrg) instead.


union select from I_ChmlCmplncProdAssgmt   as ProdAssgmt
  left outer join I_ChmlCmplncInfoDraft    as CCIDraft  on CCIDraft.ActiveChmlCmplncInfoUUID = ProdAssgmt.ChmlCmplncProdUUID
  join            I_ChmlCmplncMktCvrgDraft as MktCvrgUP on MktCvrgUP.ChmlCmplncInfoUUID = CCIDraft.ChmlCmplncInfoUUID
  left outer join I_ChmlCmplncMktCvrgTP    as MktCvrgPP on  MktCvrgPP.ChmlCmplncInfoUUID = ProdAssgmt.ChmlCmplncInfoUUID
                                                        and MktCvrgUP.Country            = MktCvrgPP.Country

//-- Chemical Compliance Info of UnPackaged Product

//association [1..1] to I_ChmlCmplncInfoTP as _ChmlCmplncInfoProduct on _ChmlCmplncInfoProduct.ChmlCmplncInfoUUID = $projection.ChmlCmplncProdUUID


-- Chemical Compliance Info of Packaged Product
association [1..1] to I_ChmlCmplncInfoTP as _ChmlCmplncInfo        on _ChmlCmplncInfo.ChmlCmplncInfoUUID = $projection.PckgdChmlCmplncInfoUUID

{

         // Key of Product Assignment

  key    ChmlCmplncProdAssgmtUUID,

         // Covered Country

  key    MktCvrgUP.Country,

         // Chemical Compliance Info UUID of Unpackaged Product

         ProdAssgmt.ChmlCmplncProdUUID as  ChmlCmplncProdUUID,

//         // Active Chemical Compliance Info UUID of Unpackaged Product

//         _ChmlCmplncInfoProduct.ActiveChmlCmplncInfoUUID,


         // Chemical Compliance Info UUID of Packaged Product

         ProdAssgmt.ChmlCmplncInfoUUID as  PckgdChmlCmplncInfoUUID,

         // Chemical Compliance Info UUID of Product (Unpackaged or Packaged Product) from which coverage should be displayed

         case  MktCvrgPP.Country
         // If coverage for packaged product exists then return UUID of packaged product

           when  MktCvrgUP.Country then ProdAssgmt.ChmlCmplncInfoUUID
         // If coverage for packaged product not exists then return UUID of unpackaged product

           else ProdAssgmt.ChmlCmplncProdUUID

         end                           as  ChmlCmplncInfoUUID,

         -- Associations
         _ChmlCmplncInfo
//         _ChmlCmplncInfoProduct

}