How to create CDS view to return Service order item detail data

Requirement

You are asked to create a CDS view which must return the following information of Service order line item.

(1). Create a CDS view which return system status code and description like below:

view source code:

@AbapCatalog.sqlViewName: 'zstatustext'
@AbapCatalog.compiler.compareFilter: true
@AccessControl.authorizationCheck: #CHECK
@EndUserText.label: 'system status code and description'
define view Z_C_Status_Text as select from tj02 inner join tj02t as _tj02t on 
 tj02.istat = _tj02t.istat and _tj02t.spras = 'E'                                
{
   key tj02.istat,
   _tj02t.txt04,
   _tj02t.txt30
}

(2). Create a CDS view which contains product guid together with their status code and description:

source code:

@AbapCatalog.sqlViewName: 'zprdstatus'
@AbapCatalog.compiler.compareFilter: true
@AccessControl.authorizationCheck: #CHECK
@EndUserText.label: 'product guid and status code'
define view Z_I_Prod_Status as select from crmd_product_i 
inner join crm_jest as _status on crmd_product_i.guid = _status.objnr
inner join Z_C_Status_Text as _text on _status.stat = _text.istat
{
   key crmd_product_i.guid,
   _status.stat,
   _text.txt04,
   _text.txt30
}

where _status.inact = '' and _status.stat like 'I%' and _status.stat <> 'I1030';

(3) Create final CDS view which returns all necessary information:

source code:

@AbapCatalog.sqlViewName: 'zitemdetail'
@AbapCatalog.compiler.compareFilter: true
@AccessControl.authorizationCheck: #CHECK
@EndUserText.label: 'item detail'
define view Z_I_Item_Detail as select from crmd_orderadm_i 
inner join crmd_schedlin as _schedule 
  on crmd_orderadm_i.guid = _schedule.item_guid
inner join crmd_product_i as _prod on crmd_orderadm_i.guid = _prod.guid
inner join Z_I_Prod_Status as _prd_status
  on crmd_orderadm_i.guid = _prd_status.guid
{
   key crmd_orderadm_i.guid,
   key crmd_orderadm_i.header,
   crmd_orderadm_i.number_int,
   crmd_orderadm_i.description,
   _schedule.quantity,
   _prod.process_qty_unit as unit,
   _prd_status.stat,
   _prd_status.txt04,
   _prd_status.txt30

}

要获取更多Jerry的原创文章，请关注公众号"汪子熙":