Untitled

@AbapCatalog.sqlViewName: 'zarp_fin_atalig'
@AbapCatalog.compiler.compareFilter: true
@AbapCatalog.preserveKey: true
@AccessControl.authorizationCheck: #NOT_REQUIRED
@EndUserText.label: 'zarp_a_at_alig for finallocation report to gcp'
@Metadata.ignorePropagatedAnnotations: true
define view zarp_fin_at_alig as select distinct from zarp_at_alig as z2
left outer join aupo as a on
z2.vbeln = a.vbeln and z2.abeln = a.abeln and z2.mandt = a.mandt
 left outer join aufi as a2 on z2.abeln = a2.abeln and z2.mandt = a2.mandt
 and a2.zzmatnr = a.matnr and a2.abelp = a.abelp
  left outer join zarp_at_alig_rec as z3 on a2.abeln = z3.abeln and z2.satnr = z3.satnr 
   and z2.mandt = z3.mandt and a2.filnr = z3.kunnr
//  left outer join lips as lips on z2.vbeln = lips.vbeln and a2.zzmatnr = lips.matnr and z2.mandt = lips.mandt
// left outer join zarp_al_forecast ZF on z2.planning_market = ZF.planningmarketid and z2.orderno = ZF.orderno 
//  and z2.mandt = ZF.mandt and a.matnr = ZF.variant and a.mandt = ZF.mandt 
//  and z3.kunnr = ZF.customer_to and z3.mandt = ZF.mandt  -- enable it later 
//  left outer join ZARP_FINAL_ALLO_DD07T as artcat on artcat.domName = 'ATTYP' and artcat.domValue = a.attyp   
//  left outer join ZARP_FINALLO_MATNR as zfm on  a2.zzmatnr = zfm.variant
   {
  key z2.planning_market as PM,
//  key
//  case 
//        when zfm.len_matnr = 16 then substring(ltrim(a2.zzmatnr , '0' ), 1, 13) 
//        when zfm.len_matnr = 15 then substring(ltrim(a2.zzmatnr , '0' ), 1, 12)
//        else ' '   
//    end as pascode,
  key right(z2.orderno, 6) as HMOrderNO,
  key z2.vbeln as InboundDelivery,
  key z2.awhse as AWHSE,
  key a2.abeln as allocationtable,
  a.matnr as variant,
//  lips.lfimg as totalqtyatvariantlevel,
  a2.filnr as locationcode,
  z3.kunnr as store,-- temporary code
 //  z3.allo_perc as ALLO_PERCENT_PARAM,  
 z2.satnr as aligsat,
 z3.satnr as recsat,
   z3.allo_perc_param as ALLO_PERCENT_FINAL,
//    z3.allo_stop as Stopstore,
//  a2.zzstoreprio as storeprio,
 // z3.kunnr as Eligble_Store,  
//  z3.not_prioritized as NOT_PRIORITIZED,
//  a2.zztsq_bef_oval as TO_SITE_QTY,  
//  a2.zzoverallo_qty as OVER_ALLO_PIECES,
//  a2.pmngu as SINGLE_SOLIDS,
//  (a2.zztsq_aft_oval - a2.pmngu) as PACK_PIECES,  
//  z3.allo_from_rw as ALLOCATION_FROM_RW,
//  case when ZF.customer_to is not null then 'X' 
//else '' 
//end as Forecast_available,
//   ZF.total_demand_qty as total_demand_qty,
//       @Semantics.businessDate.createdAt: true
// ZF.created_on as R3_IMPORT_TO_SAP_DATE,
//   z3.rwhse as rwhse,
   a.attyp as article_category,
   a.abelp as aupoab,
   a2.abelp as aufiab
//    artcat.description as article_category_t
  }
where 
(z2.allo_trigger = 'ZA02' or z2.allo_trigger = 'ZA03' or z2.allo_trigger = 'ZA04' or z2.allo_trigger = 'ZA05' or z2.allo_trigger = 'ZA06') 
  and z2.released = 'X'
    and z2.mandt = $session.client
    and a2.mandt = $session.client
    and a2.vtweg = '10'
    and z3.vtweg = '10' ; //to fetch stores alone