P_Projectwiprevdetlbasic

@AbapCatalog.sqlViewName: 'PPROJ_DLIWPRV_BS'
@ClientDependent: true
@AbapCatalog.compiler.compareFilter: true
@VDM.private:true //= @VDM.private

@VDM.viewType: #BASIC
@AccessControl.authorizationCheck: #NOT_REQUIRED
// @EndUserText.label: 'Project WIP with revenue from DIP'


@ObjectModel.usageType.serviceQuality: #D
@ObjectModel.usageType.sizeCategory: #XL
@ObjectModel.usageType.dataClass: #MIXED

//Selects from P_Wipdetailbasic, prps, proj, tka01, cobk, P_Wiprevdetailbasic and P_Wiprevdetsrvkb 


//... no 1


define view P_Projectwiprevdetlbasic
  as

  select from       P_Wipdetailbasic as a
    inner join      prps             as b on b.objnr = a.ControllingObject
    inner join      proj             as c on c.pspnr = b.psphi
    inner join      tka01            as d on d.kokrs = b.pkokr
    left outer join cobk             as e on  e.kokrs = b.pkokr
                                          and e.belnr = a.ControllingDocument
{
  a.PostingPeriod,
  a.TransactionCurrency,
  a.UnitOfMeasure,
  a.CostElement,
  a.CostCenter,
  a.CostCtrActivityType,
  a.Material,
  a.BusinessProcess,
  a.PersonnelNumber,
  e.bldat                                                    as DocumentDate,
  cast ( a.ManualInvoiceItem as pro_dw_man_item )            as ManualInvoiceItem,
  //  a.ProfitCenter,

  a.ControllingObject,
  
  cast ( ( a.TransacCrcyForInvoiceAmount - a.TransacCrcyInvoicedAmount ) as pro_dw_in_wtgbtr )    as TransacCrcyForInvoiceAmount,
  cast ( ( a.ObjectCrcyForInvoiceAmount - a.ObjectCrcyInvoicedAmount ) as pro_dw_in_wogbtr )      as ObjectCrcyForInvoiceAmount,
  cast ( ( a.CtrlgAreaCrcyForInvoiceAmount - a.CtrlgAreaCrcyInvoicedAmount )as pro_dw_in_wkgbtr ) as CtrlgAreaCrcyForInvoiceAmount,  
  cast ( ( a.ForInvoiceQuantity - a.InvoicedQuantity ) as pro_dw_in_quantity )                    as ForInvoiceQuantity,
  
  cast ( a.TransacCrcyForRjcnAmount as pro_dw_no_wtgbtr )    as TransacCrcyForRjcnAmount,
  cast ( a.ObjectCrcyForRjcnAmount as pro_dw_no_wogbtr )     as ObjectCrcyForRjcnAmount,
  cast ( a.CtrlgAreaCrcyForRjcnAmount as pro_dw_no_wkgbtr )  as CtrlgAreaCrcyForRjcnAmount, 
  cast ( a.ForRjcnQuantity as pro_dw_no_quantity )           as ForRjcnQuantity,
  
  a.SalesDocumentItemCurrency,
  
  cast ( a.TransacCrcyInvoicedAmount as ad01abc_wtg )        as TransacCrcyInvoicedAmount,
  cast ( a.ObjectCrcyInvoicedAmount as ad01abc_wog )         as ObjectCrcyInvoicedAmount,
  cast ( a.CtrlgAreaCrcyInvoicedAmount as ad01abc_wkg )      as CtrlgAreaCrcyInvoicedAmount,
  cast ( a.InvoicedQuantity as ad01abc_meg )                 as InvoicedQuantity,
  
  cast ( a.TransacCrcyRejectedAmount as ad01noc_wtg )        as TransacCrcyRejectedAmount,
  cast ( a.ObjectCrcyRejectedAmount as ad01noc_wog )         as ObjectCrcyRejectedAmount,
  cast ( a.CtrlgAreaCrcyRejectedAmount as ad01noc_wkg )      as CtrlgAreaCrcyRejectedAmount,
  cast ( a.RejectedQuantity as ad01noc_meg )                 as RejectedQuantity,
  
  cast ( a.TransacCrcyWIPAmount as ad01tbc_wtg )             as TransacCrcyWIPAmount,
  cast ( a.ObjectCrcyWIPAmount as ad01tbc_wog )              as ObjectCrcyWIPAmount,
  cast ( a.CtrlgAreaCrcyWIPAmount as ad01tbc_wkg )           as CtrlgAreaCrcyWIPAmount,
  cast ( a.WIPQuantity as ad01tbc_meg )                      as WIPQuantity,
  
  cast ( a.TotTransacCrcyInvoiceSimlnAmt as ad01rev_wtg )         as TotTransacCrcyInvoiceSimlnAmt,
  cast ( a.TotObjectCrcyInvoiceSimlnAmt as pro_dw_revsim_wogbtr ) as TotObjectCrcyInvoiceSimlnAmt,
  cast ( a.TotCtrlgAreaCrcyInvcSimlnAmt as ad01rev_wkg )          as TotCtrlgAreaCrcyInvcSimlnAmt,
  
  cast ( a.RevenueCostElement as pro_dw_rev_kstar )          as RevenueCostElement,
  cast ( a.SoldMaterial as pro_dw_rev_matnr )                as SoldMaterial,
  
  cast ( a.TransacCrcyInvoiceSimlnAmt as ad01rev_wtg )   as TransacCrcyInvoiceSimlnAmt,
  cast ( a.ObjectCrcyInvoiceSimlnAmt as ad01rev_wog )    as ObjectCrcyInvoiceSimlnAmt,
  cast ( a.CtrlgAreaCrcyInvoiceSimlnAmt as ad01rev_wkg ) as CtrlgAreaCrcyInvoiceSimlnAmt,
  
  a.ControllingObjectCurrency,
  a.ControllingAreaCurrency,
  a.TransacCurrencyInvoiceSimln,
  a.ResultAnalysisVersion,
  b.pspnr                                                    as WBSElementInternalID,
  b.posid                                                    as WBSElement,
  b.post1                                                    as WBSDescription,
  c.pspnr                                                    as ProjectInternalID,
  c.pspid                                                    as Project,
  c.post1                                                    as ProjectDescription,
  b.pbukr                                                    as CompanyCode,
  b.pgsbr                                                    as BusinessArea,
  b.pkokr                                                    as ControllingArea,
  b.prctr                                                    as ProfitCenter,
  cast(' ' as  abgr_schl)                                    as ResultAnalysisInternalID,
  b.werks                                                    as Plant,
  cast ( b.scope as scope preserving type )                  as ControllingObjectClass,
  b.func_area                                                as FunctionalArea,
  cast(' ' as saknr_soll)                                    as ProfitLossAccountUnbilledRev,
  cast(' ' as saknr_habe)                                    as BlanceSheetAccountUnbilledRev
}

union all select from P_Wiprevdetailbasic as a
  inner join          prps                as b on b.objnr = a.ControllingObject
  inner join          proj                as c on c.pspnr = b.psphi
  inner join          tka01               as d on d.kokrs = b.pkokr
  left outer join     cobk                as e on  e.kokrs = b.pkokr
                                               and e.belnr = a.ControllingDocument
  left outer join     P_Wiprevdetsrvkb    as z on  z.kokrs = b.pkokr
                                               and z.versa = a.ResultAnalysisVersion
                                               and z.abgsl = a.ResultAnalysisInternalID
                                               and z.bukrs = b.pbukr
                                               and z.kstar = a.RevenueCostElement
{
  a.PostingPeriod,
  a.TransactionCurrency,
  a.UnitOfMeasure,
  a.CostElement,
  a.CostCenter,
  a.CostCtrActivityType,
  a.Material,
  a.BusinessProcess,
  a.PersonnelNumber,
  e.bldat                                                    as DocumentDate,
  cast ( a.ManualInvoiceItem as pro_dw_man_item )            as ManualInvoiceItem,
  //  a.ProfitCenter,

  a.ControllingObject,
  cast ( 0 as pro_dw_in_wtgbtr ) as TransacCrcyForInvoiceAmount,
  cast ( 0 as pro_dw_in_wogbtr ) as ObjectCrcyForInvoiceAmount,
  cast ( 0 as pro_dw_in_wkgbtr ) as CtrlgAreaCrcyForInvoiceAmount,  
  cast ( 0 as pro_dw_in_quantity ) as ForInvoiceQuantity,
  
  cast ( 0 as pro_dw_no_wtgbtr ) as TransacCrcyForRjcnAmount,
  cast ( 0 as pro_dw_no_wogbtr ) as ObjectCrcyForRjcnAmount,
  cast ( 0 as pro_dw_no_wkgbtr ) as CtrlgAreaCrcyForRjcnAmount, 
  cast ( 0 as pro_dw_no_quantity ) as ForRjcnQuantity,
  
  a.SalesDocumentItemCurrency,
  cast ( 0 as ad01abc_wtg ) as TransacCrcyInvoicedAmount,
  cast ( 0 as ad01abc_wog ) as ObjectCrcyInvoicedAmount,
  cast ( 0 as ad01abc_wkg ) as CtrlgAreaCrcyInvoicedAmount,
  cast ( 0 as ad01abc_meg ) as InvoicedQuantity,
  
  cast ( 0 as ad01noc_wtg ) as TransacCrcyRejectedAmount,
  cast ( 0 as ad01noc_wog ) as ObjectCrcyRejectedAmount,
  cast ( 0 as ad01noc_wkg ) as CtrlgAreaCrcyRejectedAmount,
  cast ( 0 as ad01noc_meg ) as RejectedQuantity,
  
  cast ( 0 as ad01tbc_wtg ) as TransacCrcyWIPAmount,
  cast ( 0 as ad01tbc_wog ) as ObjectCrcyWIPAmount,
  cast ( 0 as ad01tbc_wkg ) as CtrlgAreaCrcyWIPAmount,
  cast ( 0 as ad01tbc_meg ) as WIPQuantity,
  
  cast ( 0 as ad01rev_wtg )          as TotTransacCrcyInvoiceSimlnAmt,
  cast ( 0 as pro_dw_revsim_wogbtr ) as TotObjectCrcyInvoiceSimlnAmt,
  cast ( 0 as ad01rev_wkg )          as TotCtrlgAreaCrcyInvcSimlnAmt,
  
  cast ( a.RevenueCostElement as pro_dw_rev_kstar )          as RevenueCostElement,
  cast ( a.SoldMaterial as pro_dw_rev_matnr )                as SoldMaterial,
  
  cast ( a.TransacCrcyInvoiceSimlnAmt as ad01rev_wtg )   as TransacCrcyInvoiceSimlnAmt,
  cast ( a.ObjectCrcyInvoiceSimlnAmt as ad01rev_wog )    as ObjectCrcyInvoiceSimlnAmt,
  cast ( a.CtrlgAreaCrcyInvoiceSimlnAmt as ad01rev_wkg ) as CtrlgAreaCrcyInvoiceSimlnAmt,
  
  a.ControllingObjectCurrency,
  a.ControllingAreaCurrency,
  a.TransacCurrencyInvoiceSimln,
  a.ResultAnalysisVersion,
  b.pspnr                                                    as WBSElementInternalID,
  b.posid                                                    as WBSElement,
  b.post1                                                    as WBSDescription,
  c.pspnr                                                    as ProjectInternalID,
  c.pspid                                                    as Project,
  c.post1                                                    as ProjectDescription,
  b.pbukr                                                    as CompanyCode,
  b.pgsbr                                                    as BusinessArea,
  b.pkokr                                                    as ControllingArea,
  b.prctr                                                    as ProfitCenter,
  a.ResultAnalysisInternalID                                 as ResultAnalysisInternalID,
  b.werks                                                    as Plant,
  b.scope                                                    as ControllingObjectClass,
  b.func_area                                                as FunctionalArea,
  z.sakso                                                    as ProfitLossAccountUnbilledRev,
  z.sakha                                                    as BlanceSheetAccountUnbilledRev
}