SELECT * from (

SELECT
	SOO.Contract "Site"
	,SOO.DEPARTMENT_NO "Department"
	,CASE WHEN SOO2.Resource_ID  is not null THEN SOO2.Resource_ID  ELSE SOO2.PREFERRED_RESOURCE_ID END "Scheduled Resource"
	,SOO2.Resource_ID
	,SOO2.PREFERRED_RESOURCE_ID
	,SOO.CF$_MOLD "Mold"


	,SOO.OP_START_DATE "Start Date"
	,SOO.OP_FINISH_DATE "End Date"
	,SOO.ORDER_NO "Order No"
	,SOO.RELEASE_NO "Release No"
	,SOO.SEQUENCE_NO "Sequence No"
	,SOO.CF$_LOT_BATCH_NO "Lot Batch"
	,SOO.PART_NO "Part No"
	,IP.Description

	,SOO.CF$_COLOR "Color"
	,SOO.CF$_PANTONE "Pantone"
	,SOO.CF$_PCKG_SKETCH "Pckg Sketch"
	,SOO.CF$_DECORATION "Decoration"
	,SOO.CF$_PLATE_NO "Plate No"
	,SOO.CF$_ASSY_CONFIG "Assy Config"

	,SOO.CF$_INSERT_CONFIG "Insert Config"
	,LastUtil.ATTR_VALUE_ALPHA "Last Insert Config"
	,LastUtil.Part_No "Last Tool Usage Part"
	,LastUtil.Description "Last Tool Usage Part Description"

	,CASE when SOO.CF$_MOLD <> LAG(SOO.CF$_MOLD,1) OVER (PARTITION BY SOO.DEPARTMENT_NO,CASE WHEN SOO2.Resource_ID  is not null THEN SOO2.Resource_ID  ELSE SOO2.PREFERRED_RESOURCE_ID END ORDER BY SOO.DEPARTMENT_NO,CASE WHEN SOO2.Resource_ID  is not null THEN SOO2.Resource_ID  ELSE SOO2.PREFERRED_RESOURCE_ID END,SOO.OP_START_DATE) then 1 else 0 end "Mold Change"
	,CASE when SOO.CF$_MOLD <> LAG(SOO.CF$_MOLD,1) OVER (PARTITION BY SOO.DEPARTMENT_NO,CASE WHEN SOO2.Resource_ID  is not null THEN SOO2.Resource_ID  ELSE SOO2.PREFERRED_RESOURCE_ID END ORDER BY SOO.DEPARTMENT_NO,CASE WHEN SOO2.Resource_ID  is not null THEN SOO2.Resource_ID  ELSE SOO2.PREFERRED_RESOURCE_ID END,SOO.OP_START_DATE) then 'From ' || LAG (SOO.CF$_MOLD,1) OVER (PARTITION BY SOO.DEPARTMENT_NO,CASE WHEN SOO2.Resource_ID  is not null THEN SOO2.Resource_ID  ELSE SOO2.PREFERRED_RESOURCE_ID END ORDER BY SOO.DEPARTMENT_NO,CASE WHEN SOO2.Resource_ID  is not null THEN SOO2.Resource_ID  ELSE SOO2.PREFERRED_RESOURCE_ID END,SOO.OP_START_DATE) || ' to ' || SOO.CF$_MOLD else '' end "Mold Change details"


	,CASE when SOO.CF$_COLOR <> LAG(SOO.CF$_COLOR,1) OVER (PARTITION BY SOO.DEPARTMENT_NO,CASE WHEN SOO2.Resource_ID  is not null THEN SOO2.Resource_ID  ELSE SOO2.PREFERRED_RESOURCE_ID END ORDER BY SOO.DEPARTMENT_NO,CASE WHEN SOO2.Resource_ID  is not null THEN SOO2.Resource_ID  ELSE SOO2.PREFERRED_RESOURCE_ID END,SOO.OP_START_DATE) then 1 else 0 end "Color Change"
	,CASE when SOO.CF$_COLOR <> LAG(SOO.CF$_COLOR,1) OVER (PARTITION BY SOO.DEPARTMENT_NO,CASE WHEN SOO2.Resource_ID  is not null THEN SOO2.Resource_ID  ELSE SOO2.PREFERRED_RESOURCE_ID END ORDER BY SOO.DEPARTMENT_NO,CASE WHEN SOO2.Resource_ID  is not null THEN SOO2.Resource_ID  ELSE SOO2.PREFERRED_RESOURCE_ID END,SOO.OP_START_DATE) then 'From ' || LAG (SOO.CF$_COLOR,1) OVER (PARTITION BY SOO.DEPARTMENT_NO,CASE WHEN SOO2.Resource_ID  is not null THEN SOO2.Resource_ID  ELSE SOO2.PREFERRED_RESOURCE_ID END ORDER BY SOO.DEPARTMENT_NO,CASE WHEN SOO2.Resource_ID  is not null THEN SOO2.Resource_ID  ELSE SOO2.PREFERRED_RESOURCE_ID END,SOO.OP_START_DATE) || ' to ' || SOO.CF$_COLOR else '' end "Color change details"


	,CASE when SOO.CF$_PANTONE <> LAG(SOO.CF$_PANTONE,1) OVER (PARTITION BY SOO.DEPARTMENT_NO,CASE WHEN SOO2.Resource_ID  is not null THEN SOO2.Resource_ID  ELSE SOO2.PREFERRED_RESOURCE_ID END ORDER BY SOO.DEPARTMENT_NO,CASE WHEN SOO2.Resource_ID  is not null THEN SOO2.Resource_ID  ELSE SOO2.PREFERRED_RESOURCE_ID END,SOO.OP_START_DATE) then 1 else 0 end "Pantone Change"
	,CASE when SOO.CF$_PANTONE <> LAG(SOO.CF$_PANTONE,1) OVER (PARTITION BY SOO.DEPARTMENT_NO,CASE WHEN SOO2.Resource_ID  is not null THEN SOO2.Resource_ID  ELSE SOO2.PREFERRED_RESOURCE_ID END ORDER BY SOO.DEPARTMENT_NO,CASE WHEN SOO2.Resource_ID  is not null THEN SOO2.Resource_ID  ELSE SOO2.PREFERRED_RESOURCE_ID END,SOO.OP_START_DATE) then 'From ' || LAG (SOO.CF$_PANTONE,1) OVER (PARTITION BY SOO.DEPARTMENT_NO,CASE WHEN SOO2.Resource_ID  is not null THEN SOO2.Resource_ID  ELSE SOO2.PREFERRED_RESOURCE_ID END ORDER BY SOO.DEPARTMENT_NO,CASE WHEN SOO2.Resource_ID  is not null THEN SOO2.Resource_ID  ELSE SOO2.PREFERRED_RESOURCE_ID END,SOO.OP_START_DATE) || ' to ' || SOO.CF$_PANTONE else '' end "Pantone change details"


	,CASE when CF$_PCKG_SKETCH <> LAG(CF$_PCKG_SKETCH,1) OVER (PARTITION BY SOO.DEPARTMENT_NO,CASE WHEN SOO2.Resource_ID  is not null THEN SOO2.Resource_ID  ELSE SOO2.PREFERRED_RESOURCE_ID END ORDER BY SOO.DEPARTMENT_NO,CASE WHEN SOO2.Resource_ID  is not null THEN SOO2.Resource_ID  ELSE SOO2.PREFERRED_RESOURCE_ID END,SOO.OP_START_DATE) then 1 else 0 end "Pckg Sketch Change"
	,CASE when CF$_PCKG_SKETCH <> LAG(CF$_PCKG_SKETCH,1) OVER (PARTITION BY SOO.DEPARTMENT_NO,CASE WHEN SOO2.Resource_ID  is not null THEN SOO2.Resource_ID  ELSE SOO2.PREFERRED_RESOURCE_ID END ORDER BY SOO.DEPARTMENT_NO,CASE WHEN SOO2.Resource_ID  is not null THEN SOO2.Resource_ID  ELSE SOO2.PREFERRED_RESOURCE_ID END,SOO.OP_START_DATE) then 'From ' || LAG (CF$_PCKG_SKETCH,1) OVER (PARTITION BY SOO.DEPARTMENT_NO,CASE WHEN SOO2.Resource_ID  is not null THEN SOO2.Resource_ID  ELSE SOO2.PREFERRED_RESOURCE_ID END ORDER BY SOO.DEPARTMENT_NO,CASE WHEN SOO2.Resource_ID  is not null THEN SOO2.Resource_ID  ELSE SOO2.PREFERRED_RESOURCE_ID END,SOO.OP_START_DATE) || ' to ' || SOO.CF$_PCKG_SKETCH else '' end "Pckg Sketch change details"


	,CASE when SOO.CF$_DECORATION <> LAG(SOO.CF$_DECORATION,1) OVER (PARTITION BY SOO.DEPARTMENT_NO,CASE WHEN SOO2.Resource_ID  is not null THEN SOO2.Resource_ID  ELSE SOO2.PREFERRED_RESOURCE_ID END ORDER BY SOO.DEPARTMENT_NO,CASE WHEN SOO2.Resource_ID  is not null THEN SOO2.Resource_ID  ELSE SOO2.PREFERRED_RESOURCE_ID END,SOO.OP_START_DATE) then 1 else 0 end "Decoration Change"
	,CASE when SOO.CF$_DECORATION <> LAG(SOO.CF$_DECORATION,1) OVER (PARTITION BY SOO.DEPARTMENT_NO,CASE WHEN SOO2.Resource_ID  is not null THEN SOO2.Resource_ID  ELSE SOO2.PREFERRED_RESOURCE_ID END ORDER BY SOO.DEPARTMENT_NO,CASE WHEN SOO2.Resource_ID  is not null THEN SOO2.Resource_ID  ELSE SOO2.PREFERRED_RESOURCE_ID END,SOO.OP_START_DATE) then 'From ' || LAG (SOO.CF$_DECORATION,1) OVER (PARTITION BY SOO.DEPARTMENT_NO,CASE WHEN SOO2.Resource_ID  is not null THEN SOO2.Resource_ID  ELSE SOO2.PREFERRED_RESOURCE_ID END ORDER BY SOO.DEPARTMENT_NO,CASE WHEN SOO2.Resource_ID  is not null THEN SOO2.Resource_ID  ELSE SOO2.PREFERRED_RESOURCE_ID END,SOO.OP_START_DATE) || ' to ' || SOO.CF$_DECORATION else '' end "Decoration change details"


	,CASE when SOO.CF$_INSERT_CONFIG <> LAG(SOO.CF$_INSERT_CONFIG,1) OVER (PARTITION BY SOO.DEPARTMENT_NO,CASE WHEN SOO2.Resource_ID  is not null THEN SOO2.Resource_ID  ELSE SOO2.PREFERRED_RESOURCE_ID END ORDER BY SOO.DEPARTMENT_NO,CASE WHEN SOO2.Resource_ID  is not null THEN SOO2.Resource_ID  ELSE SOO2.PREFERRED_RESOURCE_ID END,SOO.OP_START_DATE) then 1 else 0 end "Insert Change"
	,CASE when SOO.CF$_INSERT_CONFIG <> LAG(SOO.CF$_INSERT_CONFIG,1) OVER (PARTITION BY SOO.DEPARTMENT_NO,CASE WHEN SOO2.Resource_ID  is not null THEN SOO2.Resource_ID  ELSE SOO2.PREFERRED_RESOURCE_ID END ORDER BY SOO.DEPARTMENT_NO,CASE WHEN SOO2.Resource_ID  is not null THEN SOO2.Resource_ID  ELSE SOO2.PREFERRED_RESOURCE_ID END,SOO.OP_START_DATE) then 'From ' || LAG (SOO.CF$_INSERT_CONFIG,1) OVER (PARTITION BY SOO.DEPARTMENT_NO,CASE WHEN SOO2.Resource_ID  is not null THEN SOO2.Resource_ID  ELSE SOO2.PREFERRED_RESOURCE_ID END ORDER BY SOO.DEPARTMENT_NO,CASE WHEN SOO2.Resource_ID  is not null THEN SOO2.Resource_ID  ELSE SOO2.PREFERRED_RESOURCE_ID END,SOO.OP_START_DATE) || ' to ' || SOO.CF$_INSERT_CONFIG else '' end "Insert change details"


	,CASE when SOO.CF$_ASSY_CONFIG <> LAG(SOO.CF$_ASSY_CONFIG,1) OVER (PARTITION BY SOO.DEPARTMENT_NO,CASE WHEN SOO2.Resource_ID  is not null THEN SOO2.Resource_ID  ELSE SOO2.PREFERRED_RESOURCE_ID END ORDER BY SOO.DEPARTMENT_NO,CASE WHEN SOO2.Resource_ID  is not null THEN SOO2.Resource_ID  ELSE SOO2.PREFERRED_RESOURCE_ID END,SOO.OP_START_DATE) then 1 else 0 end "Assy Config Change"
	,CASE when SOO.CF$_ASSY_CONFIG <> LAG(SOO.CF$_ASSY_CONFIG,1) OVER (PARTITION BY SOO.DEPARTMENT_NO,CASE WHEN SOO2.Resource_ID  is not null THEN SOO2.Resource_ID  ELSE SOO2.PREFERRED_RESOURCE_ID END ORDER BY SOO.DEPARTMENT_NO,CASE WHEN SOO2.Resource_ID  is not null THEN SOO2.Resource_ID  ELSE SOO2.PREFERRED_RESOURCE_ID END,SOO.OP_START_DATE) then 'From ' || LAG (SOO.CF$_ASSY_CONFIG,1) OVER (PARTITION BY SOO.DEPARTMENT_NO,CASE WHEN SOO2.Resource_ID  is not null THEN SOO2.Resource_ID  ELSE SOO2.PREFERRED_RESOURCE_ID END ORDER BY SOO.DEPARTMENT_NO,CASE WHEN SOO2.Resource_ID  is not null THEN SOO2.Resource_ID  ELSE SOO2.PREFERRED_RESOURCE_ID END,SOO.OP_START_DATE) || ' to ' || SOO.CF$_ASSY_CONFIG else '' end "Assy Config change details"

	,SOO.NEED_DATE "Need Date"
	,SOOM.Remaining_mfg_hours "Rem Hrs"
	,SOOM.Remaining_qty_to_report "Rem Qty"
	,SOO.CF$_CARTON_REMAINING "Rem Carton"

	,CASE 
		WHEN SOO.CF$_MOLD <> LAG(SOO.CF$_MOLD,1) OVER (PARTITION BY SOO.DEPARTMENT_NO,CASE WHEN SOO2.Resource_ID  is not null THEN SOO2.Resource_ID  ELSE SOO2.PREFERRED_RESOURCE_ID END ORDER BY SOO.DEPARTMENT_NO,CASE WHEN SOO2.Resource_ID  is not null THEN SOO2.Resource_ID  ELSE SOO2.PREFERRED_RESOURCE_ID END,SOO.OP_START_DATE) 
			and SOO.MACH_SETUP_TIME=0 
		then 'Mold change Machine SetUp time should not be 0' 

		WHEN SOO.CF$_MOLD = LAG(SOO.CF$_MOLD,1) OVER (PARTITION BY SOO.DEPARTMENT_NO,CASE WHEN SOO2.Resource_ID  is not null THEN SOO2.Resource_ID  ELSE SOO2.PREFERRED_RESOURCE_ID END ORDER BY SOO.DEPARTMENT_NO,CASE WHEN SOO2.Resource_ID  is not null THEN SOO2.Resource_ID  ELSE SOO2.PREFERRED_RESOURCE_ID END,SOO.OP_START_DATE) 
			and SOO.MACH_SETUP_TIME<>0 
			and SOO.CF$_COLOR = LAG(SOO.CF$_COLOR,1) OVER (PARTITION BY SOO.DEPARTMENT_NO,CASE WHEN SOO2.Resource_ID  is not null THEN SOO2.Resource_ID  ELSE SOO2.PREFERRED_RESOURCE_ID END ORDER BY SOO.DEPARTMENT_NO,CASE WHEN SOO2.Resource_ID  is not null THEN SOO2.Resource_ID  ELSE SOO2.PREFERRED_RESOURCE_ID END,SOO.OP_START_DATE)
		then 'No Machine or Color change. SetUp time should be 0'

		WHEN SOO.CF$_MOLD = LAG(SOO.CF$_MOLD,1) OVER (PARTITION BY SOO.DEPARTMENT_NO,CASE WHEN SOO2.Resource_ID  is not null THEN SOO2.Resource_ID  ELSE SOO2.PREFERRED_RESOURCE_ID END ORDER BY SOO.DEPARTMENT_NO,CASE WHEN SOO2.Resource_ID  is not null THEN SOO2.Resource_ID  ELSE SOO2.PREFERRED_RESOURCE_ID END,SOO.OP_START_DATE) 
			and SOO.MACH_SETUP_TIME=0 
			and SOO.CF$_COLOR <> LAG(SOO.CF$_COLOR,1) OVER (PARTITION BY SOO.DEPARTMENT_NO,CASE WHEN SOO2.Resource_ID  is not null THEN SOO2.Resource_ID  ELSE SOO2.PREFERRED_RESOURCE_ID END ORDER BY SOO.DEPARTMENT_NO,CASE WHEN SOO2.Resource_ID  is not null THEN SOO2.Resource_ID  ELSE SOO2.PREFERRED_RESOURCE_ID END,SOO.OP_START_DATE)
		then 'Color change Machine SetUp time should not be 0'

		WHEN SOO.CF$_MOLD = LAG(SOO.CF$_MOLD,1) OVER (PARTITION BY SOO.DEPARTMENT_NO,CASE WHEN SOO2.Resource_ID  is not null THEN SOO2.Resource_ID  ELSE SOO2.PREFERRED_RESOURCE_ID END ORDER BY SOO.DEPARTMENT_NO,CASE WHEN SOO2.Resource_ID  is not null THEN SOO2.Resource_ID  ELSE SOO2.PREFERRED_RESOURCE_ID END,SOO.OP_START_DATE) 
			and SOO.MACH_SETUP_TIME>'&Color_Chg_Std_Duration' 
			and SOO.CF$_COLOR <> LAG(SOO.CF$_COLOR,1) OVER (PARTITION BY SOO.DEPARTMENT_NO,CASE WHEN SOO2.Resource_ID  is not null THEN SOO2.Resource_ID  ELSE SOO2.PREFERRED_RESOURCE_ID END ORDER BY SOO.DEPARTMENT_NO,CASE WHEN SOO2.Resource_ID  is not null THEN SOO2.Resource_ID  ELSE SOO2.PREFERRED_RESOURCE_ID END,SOO.OP_START_DATE)
		then 'Color change SetUp time higher than standard'

	else '' end "SetUp Validation"

	,SOO.MACH_SETUP_TIME "SetUp"
	,SOO.LABOR_SETUP_TIME "Labor SetUp"


	,SOO.STATE "SO Status"
	,SOO.OPER_STATUS_CODE "Oper Status"
	,SOO.ROUTING_ALTERNATIVE "Alternative"
	,SOO.CF$_COMMODITY "Commodity"
	,SOO.MACH_RUN_FACTOR "PPH"
	,SOO.CREW_SIZE "Crew Size"
	,CF$_BAFFLE Baffle
	,CF$_WHEEL Wheel
	,SOO.BOM_TYPE "Shop Order Type"
,sysdate "Report execution"

FROM
	SHOP_ORDER_OPERATION_JOIN_cfv SOO 
	inner join Inventory_Part IP on SOO.Contract=IP.Contract and SOO.Part_No=IP.Part_No
	inner join SHOP_ORDER_OPERATION SOO2 on SOO.Order_No=SOO2.Order_No and SOO.Release_No=SOO2.Release_No and SOO.Sequence_No=SOO2.Sequence_No and SOO.Operation_No=SOO2.Operation_No 
	left join Shop_Order_Operation_mvb SOOM on SOO.Order_No=SOOM.Order_No and SOO.Release_No=SOOM.Release_No and SOO.Sequence_No=SOOM.Sequence_No and SOO.Operation_No=SOOM.Operation_No
	left join 
	(Select 
	LTU.Tool_ID
	,LTU.Contract
	,LTU.Part_No
	,IP.Description
	,C.ATTR_VALUE_ALPHA
from
	(Select 
		LC.TOOL_ID
		,LC.Contract
		,LC.Part_No
	FROM 
		(Select T.Tool_ID,T.Contract,T.PART_NO,T.Order_No,T.Release_No,T.Sequence_No, row_number() over (partition By Tool_ID order by REPORTED_TIME desc) RN from SHOP_ORD_OPER_TOOL_USAGE T) LC 
	WHERE RN=1) LTU
	inner join Inventory_Part_Char_All C on C.Contract=LTU.Contract and C.Part_No=LTU.Part_No and C.CHARACTERISTIC_CODE='00013'
	inner join Inventory_Part IP on IP.Contract=LTU.Contract and IP.Part_No=LTU.Part_No
	) LastUtil on LastUtil.Contract=SOO.Contract and LastUtil.Tool_ID=SOO.CF$_MOLD

WHERE
	SOO.Operation_No='10'
	and SOO.STATE in ('Planned','Released','Reserved','Started','Parked')
	and SOO.OPER_STATUS_CODE in ('Planned','Released','Partially Reported','Setup Started','Reserved','Setup Completed','Parked','In Process')
	and SOOM.remaining_qty_to_report>0

	and SOO.CONTRACT LIKE '%&Site%'
	and SOO.DEPARTMENT_NO like nvl ('&Department' , '%' )
	and CASE WHEN SOO2.Resource_ID  is not null THEN SOO2.Resource_ID  ELSE SOO2.PREFERRED_RESOURCE_ID END like nvl( '&Scheduled_Resource','%')


Order By
	SOO.DEPARTMENT_NO
	,CASE WHEN SOO2.Resource_ID  is not null then SOO2.Resource_ID  else SOO2.PREFERRED_RESOURCE_ID end
	,SOO.OP_START_DATE
	)

WHERE
	"Mold Change" like NVL('&Mold_Changeover','%')
	or "Color Change" like NVL('&Color_Changeover','%')
	or "Pantone Change" like NVL('&Pantone_Changeover','%')
	or "Pckg Sketch Change" like NVL('&Pckg_Sketch_Changeover','%')
	or "Decoration Change" like NVL('&Decoration_Changeover','%')
	or "Insert Change" like NVL('&Insert_Changeover','%')
	or "Assy Config Change" like NVL('&Assy_Config_Changeover','%')