SQL.txt

SELECT wydz,Sum(Decode(Sign(REVISED_DUE_DATE-(SELECT ifsapp.work_time_calendar_api.Get_Previous_Work_Day('SITS',SYSDATE) FROM dual)),'-1',ILOsc-On_time,ILOsc)) IL_Plan,Sum(Decode(Sign(REVISED_DUE_DATE-(SELECT ifsapp.work_time_calendar_api.Get_Previous_Work_Day('SITS',SYSDATE) FROM dual)),'-1',0,On_time)) MADE_ON_TIME,Sum(Too_late) MADE_TOo_LATE,Round(Sum(Decode(Sign(REVISED_DUE_DATE-(SELECT ifsapp.work_time_calendar_api.Get_Previous_Work_Day('SITS',SYSDATE) FROM dual)),'-1',0,On_time))/(Sum(Decode(Sign(REVISED_DUE_DATE-(SELECT ifsapp.work_time_calendar_api.Get_Previous_Work_Day('SITS',SYSDATE) FROM dual)),'-1',ILOsc-On_time,ILOsc)))*100,2) Termin,(SELECT ifsapp.work_time_calendar_api.Get_Previous_Work_Day('SITS',SYSDATE) FROM dual) DAY,(SELECT To_char(SYSDATE,'yyyymmddHH24MI') FROM dual) REFR_DATE,100-Round(Sum(REST_H)/Sum(ALL_H)*100,2) MADE_PROC,(SELECT Round((To_Number(to_char(SYSDATE,'HH24'))+To_Number(to_char(SYSDATE,'MI'))/60)/24*100,2) FROM dual) PERC_day,'_teraz_' REFR,'_TYP_' TYP_RAP FROM (SELECT a.wydz,a.order_no,a.REVISED_QTY_DUE ILOsc,a.REVISED_DUE_DATE,a.ALL_H,a.REST_H,Min(c.on_time) ON_TIME,max(c.TOo_late) TOo_LATE FROM (SELECT a.order_no||b.wydz ID,b.wydz,a.order_no,a.REVISED_QTY_DUE,b.ALL_H,b.REST_H,REVISED_DUE_DATE from ifsapp.shop_ord a,(SELECT ORDER_NO,Decode(WORK_CENTER_NO,'400MM','MAG_WE','490KJ','KJ','520WP','401ST',ifsapp.work_center_api.Get_Department_No('ST',WORK_CENTER_NO)) wydz,Sum(REVISED_QTY_DUE*MACH_RUN_FACTOR) ALL_H,Sum((REVISED_QTY_DUE-QTY_COMPLETE)*MACH_RUN_FACTOR) REST_H FROM ifsapp.shop_order_operation GROUP BY  ORDER_NO,Decode(WORK_CENTER_NO,'400MM','MAG_WE','490KJ','KJ','520WP','401ST',ifsapp.work_center_api.Get_Department_No('ST',WORK_CENTER_NO))) b WHERE SubStr(part_no,1,1) NOT IN ('T','P','N','W') AND (REVISED_DUE_DATE=(SELECT ifsapp.work_time_calendar_api.Get_Previous_Work_Day('SITS',SYSDATE) FROM dual) OR (REVISED_DUE_DATE<(SELECT ifsapp.work_time_calendar_api.Get_Previous_Work_Day('SITS',SYSDATE) FROM dual) AND (OBJSTATE!='Closed' AND NOT (OBJSTATE='Started' AND ifsapp.Shop_ord_API.Get_Oper_State__(a.order_no,a.release_no,a.sequence_no)= 'Zgl. w caloœci'))) OR (REVISED_DUE_DATE<(SELECT ifsapp.work_time_calendar_api.Get_Previous_Work_Day('SITS',SYSDATE) FROM dual) AND (To_Date(close_date)=(SELECT ifsapp.work_time_calendar_api.Get_Previous_Work_Day('SITS',SYSDATE) FROM dual) OR (OBJSTATE='Started' AND ifsapp.Shop_ord_API.Get_Oper_State__(a.order_no,a.release_no,a.sequence_no)= 'Zgl. w caloœci' )) AND a.order_no IN (SELECT order_no FROM ifsapp.OPERATION_HISTORY WHERE REVERSED_FLAG_DB='N' AND TRANSACTION_CODE='OPFEED' AND TO_DATE(DATE_APPLIED)=(SELECT ifsapp.work_time_calendar_api.Get_Previous_Work_Day('SITS',SYSDATE) FROM dual)))) AND OBJSTATE!='Parked' AND b.order_no=a.ORDER_no) a left JOIN (SELECT b.ORDER_NO||Decode(b.WORK_CENTER_NO,'400MM','MAG_WE','490KJ','KJ','520WP','401ST',ifsapp.work_center_api.Get_Department_No('ST',b.WORK_CENTER_NO)) ID,ifsapp.shop_ord_api.Get_Revised_Due_Date(b.ORDER_NO,b.RELEASE_NO,b.SEQUENCE_NO) TERM,b.OPERATION_NO,Sum(Decode (Sign(ifsapp.shop_ord_api.Get_Revised_Due_Date(b.ORDER_NO,b.RELEASE_NO,b.SEQUENCE_NO)+2-a.date_applied),1,Nvl(zgl,0),0)) on_time,Sum(Decode(Sign(ifsapp.shop_ord_api.Get_Revised_Due_Date(b.ORDER_NO,b.RELEASE_NO,b.SEQUENCE_NO)+1-a.date_applied),-1,Nvl(zgl,0),0)) too_late,b.REVISED_QTY_DUE FROM ifsapp.shop_ord c,ifsapp.shop_order_operation b left join (SELECT a.order_no,a.OPERATION_NO,to_date(a.DATED-0.02083333333333333333333333333333) DATE_APPLIED,sum(a.QTY_COMPLETE) zgl FROM ifsapp.OPERATION_HISTORY a WHERE REVERSED_FLAG_DB='N' AND TRANSACTION_CODE='OPFEED' GROUP BY a.order_no,a.OPERATION_NO,to_date(a.DATED-0.02083333333333333333333333333333)) a ON a.order_no||'_'||a.OPERATION_NO=b.order_no||'_'||b.OPERATION_NO WHERE SubStr(c.part_no,1,1) NOT IN ('T','P','N','W') AND (c.REVISED_DUE_DATE=(SELECT ifsapp.work_time_calendar_api.Get_Previous_Work_Day('SITS',SYSDATE) FROM dual) OR (c.REVISED_DUE_DATE<(SELECT ifsapp.work_time_calendar_api.Get_Previous_Work_Day('SITS',SYSDATE) FROM dual) AND c.OBJSTATE!='Closed') OR (c.REVISED_DUE_DATE<(SELECT ifsapp.work_time_calendar_api.Get_Previous_Work_Day('SITS',SYSDATE) FROM dual) AND To_Date(c.close_date)=(SELECT ifsapp.work_time_calendar_api.Get_Previous_Work_Day('SITS',SYSDATE) FROM dual))) AND b.order_no=c.order_no GROUP BY b.ORDER_NO,Decode(b.WORK_CENTER_NO,'400MM','MAG_WE','490KJ','KJ','520WP','401ST',ifsapp.work_center_api.Get_Department_No('ST',b.WORK_CENTER_NO)),ifsapp.shop_ord_api.Get_Revised_Due_Date(b.ORDER_NO,b.RELEASE_NO,b.SEQUENCE_NO),b.OPERATION_NO,b.REVISED_QTY_DUE) c ON c.ID=a.ID GROUP BY a.wydz,a.order_no,a.REVISED_QTY_DUE,a.REVISED_DUE_DATE,a.ALL_H,a.REST_H ORDER BY order_no) HAVING Sum(Decode(Sign(REVISED_DUE_DATE-(SELECT ifsapp.work_time_calendar_api.Get_Previous_Work_Day('SITS',SYSDATE) FROM dual)),'-1',ILOsc-On_time,ILOsc))>0 GROUP BY wydz