CDS View Lookup

[praveen_kumar334]

Hi Team,

I have one requirement where I have one CDS view(CDS1) with date,TSLExpdate, RSLExpDate etc.. as shown below:

@AbapCatalog.sqlViewName: 'Zcdsview1'
@AbapCatalog.compiler.compareFilter: true
@AbapCatalog.preserveKey: true
@AccessControl.authorizationCheck: #CHECK
@EndUserText.label: 'CDS view '
define view zcdsview1 as 
select from table as apo 
         left outer join ZBV_QA_MCH1 as mch1 on   apo.MATNR = mch1.matnr and 
                                                     apo.CHARG = mch1.charg    
         left outer join ZBV_QA_MARA  as mara on  apo.MATNR = mara.matnr  
         left outer join I_FiscalCalendarDate as cdate on apo.CREATE_DATE = cdate.CalendarDate and 
                                                       cdate.FiscalYearVariant   = 'WK'           
{
//ZSCM_APO_AOS
//key mandt,
key CREATE_DATE,
key apo.WERKS  as werks,
key apo.MATNR  as matnr,
key apo.CHARG  as charg,
cdate.FiscalYearWeek  as FiscalYearWeek,

//TSL derivation part
mch1.vfdat   as TSLExpDate, 

case 
when mara.mhdrz <> 0 then dats_add_days(mch1.hsdat,ceil(mara.mhdrz), 'INITIAL') 
else mch1.hsdat 
end as RSLExpDate ,

//Calculation of LSSL date...
case 
when mara.mhdhb = 0 then 
dats_add_days(mch1.hsdat,ceil(mara.mhdrz),'INITIAL') 
when mara.mhdrz = 0 then 
dats_add_days(mch1.hsdat,-ceil(mara.mhdhb),'INITIAL') 
else
dats_add_days(dats_add_days(mch1.hsdat,-ceil(mara.mhdhb),'INITIAL') ,ceil(mara.mhdrz), 'INITIAL') 
end  as LSSLExpDate
}

and I have one more CDS view zcdsview2 which is built on the top of I_FiscalCalendarDate by adding additional fields as shown below :

@AbapCatalog.sqlViewName: 'Zcdsview2'
@AbapCatalog.compiler.compareFilter: true
@AbapCatalog.preserveKey: true
@AccessControl.authorizationCheck: #CHECK
@EndUserText.label: 'Basic View On Fiscal Calendar Date in KO Format'
define view ZBV_FISCALDATE_KO
as select from I_FiscalCalendarDate as Date {
Date.FiscalYearVariant,
Date.CalendarDate,
Date.FiscalYear,
Date.FiscalQuarter,
concat(concat(Date.FiscalQuarter,'Q'),right(Date.FiscalYear,2)) as YearQuarter,
concat(left(Date.FiscalYearPeriod,3),right(Date.FiscalYearPeriod,4)) as YearMonth,
:
:
}

Now my requirement is we have 3 different date in zcdsview1 and for this 3 different date i have to derive yearquarter, yearmonth etc. fields from zcdsview2 as shown below :

Please let me know on to achieve this.

Thanks & Regards,

Praveen.