---===============DATA WORKING LEVEL======================-----------------
DECLARE @date NVARCHAR(8), @to NVARCHAR(8);
SET @date = '202412';
SET @to = '202501';
with
dn as
(
select substring(vbeln_im,PATINDEX('%[^0]%',vbeln_im),len(vbeln_im)) 'vbeln_im',max(budat_mkpf) 'dn_gr_date'
from mseg
where shkzg = 'S' and bwart !='107' and vbeln_im is not null and substring(vbeln_im,PATINDEX('%[^0]%',vbeln_im),len(vbeln_im)) in (
select distinct [deliveryorder]
from dwh.OTIFDashboard o
where OTIFVersion between @date and @to and ordertype ='SO'
)
group by vbeln_im
)
select
OTIFVersion,
YearMonthReqDelDate,
supplyingmrparea,
receivingmrparea,
receivingcustomer,
ltrim(rtrim(receivingcountry)) receivingcountry,
material,
[order],
[item],
[ordertype],
ServiceOrderType,
SalesDocType,
[priority],
supplychainlevel,
creationdate,
requesteddeliverydate,
RequestedDeliveryDateAdj,
materialavaildate,
promiseddate,
[1stpromiseddate],
[scheduledGIdate],
[1stscheduledGIdate],
[actualgidate],
[actualgrdate],
deliveryorder,
deliveryordercreationdate,
deliveryorderplannedgidate,
deliverycreator1,
orderedqty,
confirmedqty,
deliveredqty,
mseggoodsreceiptqty,
scheduleline,
supplyingbu,
receivingbu,
plannedLeadtime,
actualLeadtime,
Material_category_description,
cogsperunit,
countbaseRDD,
countavailabilityadj,
SBUReceivingCountry,
KNTTP,
PSTYV,
countbaseRDD 'countbaseRDD',
countavailabilityadj 'countavailabilityadj',
case when deliveredqty >= orderedqty then 1 else 0 end as 'Status - Delivery Quantity',
case when [Revised GR Quantity]>= OrderedQty then 1 else 0 end as 'Status - Goods Receipt Quantity',
case
when ((deliveryordercreationdate <= creationdate) OR
(deliveryordercreationdate <= dateadd(d,1,creationdate) and [Priority] not in ('70','80')) OR
(PromisedDate <= RequestedDeliveryDate and DeliveryOrderCreationDate <= MaterialAvailDate))
AND DeliveredQty >=OrderedQty then 1
else 0 end as 'Status : Delivery',
case
when deliveryordercreationdate <= creationdate AND DeliveredQty >=OrderedQty then 'DNC <= CD'
when deliveryordercreationdate <= dateadd(d,1,creationdate) and [Priority] not in ('70','80') AND DeliveredQty >=OrderedQty then 'DNC+1 (non priority)'
when PromisedDate <= RequestedDeliveryDate and DeliveryOrderCreationDate <= MaterialAvailDate AND DeliveredQty >=OrderedQty then 'PRD based'
else '' end as 'Status : Delivery comment',
case when ActualGIDate <= [Requested GI date] and deliveredqty >= orderedqty then 1
else 0 end as 'Status : Goods issue',
case when [Revised GR Date] <= RequestedDeliveryDate and [Revised GR Quantity] >= orderedqty then 1
else 0 end as 'Status : Goods receipt',
case when ActualGIDate <= [Requested GI date adj] and deliveredqty >= orderedqty then 1
else 0 end as 'AdjStatus : Goods issue',
case when [Revised GR Date] <= RequestedDeliveryDateAdj and [Revised GR Quantity] >= orderedqty then 1
else 0 end as 'AdjStatus : Goods receipt',
case
when [Revised GR Date] <= RequestedDeliveryDateAdj and [Revised GR Quantity] >= orderedqty then 'Ontime'
when (case
when ((deliveryordercreationdate <= creationdate) OR
(deliveryordercreationdate <= dateadd(d,1,creationdate) and [Priority] not in ('70','80')) OR
(PromisedDate <= RequestedDeliveryDate and DeliveryOrderCreationDate <= MaterialAvailDate))
AND DeliveredQty >=OrderedQty then 1
else 0 end) = '0' then 'Stock out'
when (case when ActualGIDate <= [Requested GI date] and deliveredqty >= orderedqty then 1
else 0 end) = 0 then 'DN/GI Issue'
else 'GR Issue' end as 'RCA'
from
(
select *,
case when ordertype = 'SO' then isnull(DeliveredQty,0) else isnull(mseggoodsreceiptqty,0) end as 'Revised GR Quantity',
case when actualgrdate is null then dn_gr_date else actualgrdate end as 'Revised GR Date',
case when PromisedDate is not null and ScheduledGIDate is not null and ScheduledGIDate <=PromisedDate
then datediff(d,promisedDate,scheduledGIdate)
else -1 end as 'Delta PRD-SGI',
case when PromisedDate is not null and ScheduledGIDate is not null and ScheduledGIDate <=PromisedDate
then dateadd(d,datediff(d,promisedDate,scheduledGIdate),requesteddeliverydate)
else dateadd(d,1,CreationDate) end as 'Requested GI date',
case when PromisedDate is not null and ScheduledGIDate is not null and ScheduledGIDate <=PromisedDate
then dateadd(d,datediff(d,promisedDate,scheduledGIdate),RequestedDeliveryDateAdj)
else dateadd(d,1,CreationDate) end as 'Requested GI date adj'
from dwh.OTIFDashboard o
left join vbap so on so.vbeln = o.[order] and so.posnr = o.[item]
left join ekpo po on po.ebeln = o.[order] and po.ebelp = o.[item]
left join dn on dn.vbeln_im = o.[deliveryorder]
--left join mdma on o.material = mdma.matnr and mdma.berid = isnull(po.werks+isnull(po.lgort,''),so.plant+isnull(so.storagelocationkey,''))
--left join t460a on mdma.werks = t460a.werks and mdma.sobsl = t460a.sobsl
where
OTIFVersion between @date and @to
and countbaserdd = '1'
and measurelevel = 'Line Level'
and isnull(deletionindicator,'') = ''
and Material_Category_Description in ('Spares', 'Consumeables','Mains','Main')
and FlowType != 'Return'
and SBUReceivingCountry in ('APAC','MED','NCE','LATAM','AME')
) d
