I
When I copy of the SQL in Crystal Report as command MFG Completion Date is showing date time because of which OTIF is not calculating right.
For example : Lot # 1320 OTIF need to be "1 ". The SQL works fine in Toad but gives incorrect data in crystal report. Thanks in advance for yours help
select
EP.name as "LOCATION",
substr( nvl(WCS.CNTR\_DESC,WCH.CNTR\_DESC),1,3)line,
substr(nvl(WCS.CNTR\_DESC,WCH.CNTR\_DESC),4)machine,
to\_char(awo.id) lotno,
inv.ITEMNO||'-'||inv.DESCRIP itemno,
to_date(decode(CD.COMPLETION_DATE,null,case when fd.tot >= PT.ORIG_WO_QTY Then RH.TEAR_DOWN_TIME else null end,cd.completion_date)) mfg_completion_dt,
to\_date(nvl(nvl(WO.START\_TIME,WH.START\_TIME)+(BOM.SETUPHRS/24)+
((BOM.CYCLETM\*((nvl(WO.CYCLES\_PLANNED,nvl(WH.CYCLES\_PLANNED,RH.CYCLES\_PLANNED))/(1-(BOM.SCRAP/100)))/(BOM.EFF\_FACTOR/100)))/86400),rh.planned\_end\_time))orig\_ship\_date,
to\_char(next\_day (nvl(nvl(WO.START\_TIME,WH.START\_TIME)+(BOM.SETUPHRS/24)+
((BOM.CYCLETM\*((nvl(WO.CYCLES\_PLANNED,nvl(WH.CYCLES\_PLANNED,RH.CYCLES\_PLANNED))/(1-(BOM.SCRAP/100)))/(BOM.EFF\_FACTOR/100)))/86400),rh.planned\_end\_time)-7,'SUNDAY'),'MM/DD/YY') week\_due\_bucket,
nvl(FD.TOT,0) Units\_packed,
nvl(nvl(PT.ORIG\_WO\_QTY,nvl(WO.CYCLES\_PLANNED,WH.CYCLES\_PLANNED)\*PN.ACTCAV),0) Units\_scheduled,
nvl(RH.CYCLES\_TOTAL\* PT.ACTCAV,0) pieces\_made,
**CASE WHEN (to\_date(nvl(nvl(WO.START\_TIME,WH.START\_TIME)+(BOM.SETUPHRS/24)+**
**((BOM.CYCLETM\*((nvl(WO.CYCLES\_PLANNED,nvl(WH.CYCLES\_PLANNED,RH.CYCLES\_PLANNED))/(1-(BOM.SCRAP/100)))/(BOM.EFF\_FACTOR/100)))/86400),rh.planned\_end\_time))-to\_date(decode(CD.COMPLETION\_DATE,null,case when fd.tot >= PT.ORIG\_WO\_QTY Then RH.TEAR\_DOWN\_TIME else null end,cd.completion\_date)))>=0 and**
**(PT.ORIG\_WO\_QTY-nvl(FD.TOT,0))\<=0 THEN '1' ELSE '0' END AS OTIF,**
CASE WHEN (nvl(FD.TOT,0)-PT.ORIG\_WO\_QTY)\<0 THEN 0 ELSE
(nvl(FD.TOT,0)-PT.ORIG\_WO\_QTY) END AS Overproduction,
BOM.MFG\_TYPE,
cus.CUSTNO,
awo.id workorder\_id
from
(select * from
(select WO.ID,WO.STANDARD_ID,wo.arcusto_id from IQMS.WORKORDER wo
union
select WH.ID,WH.STANDARD_ID,wh.arcusto_id from IQMS.HIST_WORKORDER wh
union
select RH.WORKORDER_ID id,RH.STANDARD_ID,rh.arcusto_id id from IQMS.HIST_ILLUM_RT rh where RH.WORKORDER_ID is not null)
where standard_id is not null) awo,
IQMS.WORKORDER wo,
IQMS.HIST_WORKORDER wh,
(select distinct TL.WORKORDER_ID,sum(TL.TRANS_QUAN)over(partition by tl.workorder_id)tot
from iqms.translog tl where TL.TRANS\_REASON='Floor Disposition' and TL.TRANS\_IN\_OUT='IN')FD,
(select rh.* from IQMS.HIST_ILLUM_RT rh
where workorder_id is not null and
rh.id in (select distinct max(rh.id)over(partition by rh.workorder_id) from IQMS.HIST_ILLUM_RT rh)) RH,
IQMS.HIST_ILLUM_PART pt,
IQMS.V_SCHED_WORKORDER sch,
IQMS.WORK_CENTER wcs,
IQMS.WORK_CENTER wch,
IQMS.STANDARD bom,
IQMS.PARTNO pn,
IQMS.ARINVT inv,
IQMS.EPLANT ep,
IQMS.ALP_WO_COMP_DATE cd,
IQMS.ARCUSTO cus
where
awo.id=WO.ID and
awo.id=WH.ID and
awo.id=FD.workorder_id and
awo.id=rh.workorder_id and
awo.id=SCH.WORKORDER_ID and
awo.standard_id=bom.id and
awo.id=CD.WORKORDER_ID and
awo.arcusto_id=CUS.ID and
bom.id=PN.STANDARD_ID and
PN.ARINVT_ID=INV.ID and
BOM.EPLANT_ID=EP.ID and
SCH.WORK_CENTER_ID=WCS.ID and
rh.work_center_id=wch.id and
rh.id=PT.HIST_ILLUM_RT_ID and
INV.ITEMNO not like ('%SA%') and
inv.class = 'FG' and
nvl(WCS.ID,WCH.ID) is not null --if WO has no machine ID then don't show
order by
awo.id