Untitled

// DSCAE-493 Created by PRALS for final allocation report to gcp , this is the variant level of the idoc has the variant level details
//this is the header level view name ZARP_FINAL_ALLO_HEADER
@AbapCatalog.sqlViewName: 'ZARP_FINALLO_VAR'
@AbapCatalog.compiler.compareFilter: true
@AbapCatalog.preserveKey: true
@AccessControl.authorizationCheck: #NOT_REQUIRED
@EndUserText.label: 'Final allo header details'

define view ZARP_FINAL_ALLO_VARIANT
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 z2.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   
   {
  key z2.planning_market as PM,
  key substring( ltrim( A.matnr, '0' ), 1 , ( length( ltrim( A.matnr, '0' ) ) - 3 ) ) as len_matnr,
  key  
   substring( ltrim( A.matnr, '0' ), 1,12  ) 
  as PASCODE,
/* case 
      when a.attyp = '2' 
             then substring(ltrim(a.matnr, '0'), 1, 10) 
        else substring(ltrim(a.matnr, '0'), 1, 12) 
        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.allo_perc as ALLO_PERCENT_PARAM,  
  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,
    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