P_ACCOUNTINGCOSTRATE

@AbapCatalog.sqlViewName: 'PFIACCOSTRATE'
@AbapCatalog.compiler.compareFilter: true
@AbapCatalog.preserveKey:true
@AccessControl.authorizationCheck: #CHECK
@VDM.private: true
@VDM.viewType: #BASIC
@ObjectModel.usageType: {
        sizeCategory: #L,
        serviceQuality: #C,
        dataClass: #TRANSACTIONAL
    }
define view P_AccountingCostRate
  //as select from           accostrate as r

  as select from P_ACCOSTRATERawData as r
    left outer to one join cssl       as c on  r.kokrs         = c.kokrs
                                           and r.kostl         = c.kostl
                                           and r.activity_type = c.lstar
                                           and r.gjahrfrom     = c.gjahr
    left outer to one join prps as _prps   on  _prps.pspnr     = r.ps_psp_pnr   
    
{

  key r.rateuuid                             as   AccountingCostRateUUID,

      r.cvtyp                                as   CurrencyRole,
      r.ledger                               as   Ledger,
      r.bukrs                                as   CompanyCode,
      r.kostl                                as   CostCenter,
      r.activity_type                        as   ActivityType,
      r.gjahrfrom                            as   ValidityStartFiscalYear,
      r.periodfrom                           as   ValidityStartFiscalPeriod,
      r.datefrom                             as   ValidityStartDate,
      r.gjahrto                              as   ValidityEndFiscalYear,
      r.periodto                             as   ValidityEndFiscalPeriod,
      r.dateto                               as   ValidityEndDate,
      r.kokrs                                as   ControllingArea,
      r.currency                             as   Currency,
      r.totalamount                          as   CostRateTotalAmount,
      r.fixedamount                          as   CostRateFixedAmount,
      r.scalefactor                          as   CostRateScaleFactor,
      r.activity_unit                        as   CostCtrActivityTypeQtyUnit,
      r.category                             as   PlanningCategory,
      r.bttype                               as   BusinessTransactionType,
      r.usnam                                as   CreatedByUser,
      r.timestamp                            as   CreationDateTime,
      r.usnam                                as   LastChangedByUser,
      r.deleted                              as   CostRateIsDeleted,
      r.deleted_by                           as   DeletedByUser,
      
      cast(case
            when r.deleted_time <> 0 then r.deleted_time
            else cast('99991231000000' as abap.dec(15,0)) 
           end as fco_deleted_time)        as   DeletionDateTime,
      
      r.ico_rate                             as   IsIntercompanyRate,
      r.comp_receiving                       as   ReceivingCompanyCode,
      r.srv_cost_level                       as   ServiceCostLevel,
      r.pernr                                as   PersonnelNumber,
      
      r.ps_psp_pnr                         as   WBSElementInternalID,
      cast(r.ps_psp_pnr as ps_s4_pspnr
             preserving type )             as   WBSElementInternalID_2,
      
      r.ps_posid                             as   WBSElementExternalID,     
      cast( coalesce(_prps.posid_edit, '') 
              as ps_posid_edit 
              preserving type )              as  WBSElementExternalID_2,

      
      r.work_item_id                         as   WorkItem,
      r.overtimecat                          as   TimeSheetOvertimeCategory,
      coalesce(c.objnr, '')                  as   ControllingObject,
      cast(case
             when r.kostl <> '' and r.activity_type <> ''
               then concat(concat( ltrim(r.kostl, '0'), '/'), r.activity_type )
             else ''
           end
        as fco_cost_object)                as   CostObject,
      cast(
        concat_with_space(
          concat_with_space(
            concat_with_space(
              concat_with_space(
                concat_with_space(cast(r.totalamount as abap.sstring( 31 ) ), r.currency, 1),
                  '/', 1),
              concat_with_space(cast(r.scalefactor as abap.sstring( 7 ) ), r.activity_unit, 1), 1),
            '@', 1),
          concat(concat(r.gjahrfrom, '.'),r.periodfrom), 1)
        as fco_cost_rate_desc)             as   AccountingCostRateDesc

}
where
      //deleted = '' and 

//      cvtyp   = '00'

//remove the dirty entries with empty creation datetime

 timestamp > 0
/*+[internal] {
"BASEINFO":
{
"FROM":
[
"P_ACCOSTRATERAWDATA",
"CSSL",
"PRPS"
],
"ASSOCIATED":
[],
"BASE":
[],
"ANNO_REF":
[],
"SCALAR_FUNCTION":
[],
"VERSION":0,
"ANNOREF_EVALUATION_ERROR":""
}
}*/