;WITH TRN_DEFECT AS (

	SELECT *
	FROM CUSCONFIG.[TrnDefectDetailsUI]
	WHERE i_defect_status_id NOT IN (6, 8)
),
	TRN_SVC_TRACKING AS (

		SELECT *
		FROM [CUSCONFIG].[TrnServiceTrackingSystemUI]
		WHERE DWTENABLED IS NULL
			OR DWTENABLED = 0
)
SELECT defect.i_project_id AS projectid,
	NULL AS phaseid,
	MasVerificationRecordUI.detectbyprocessid AS processdisciplineid,
	effort.f_effort AS actual
FROM TRN_DEFECT AS defect
	INNER JOIN CUSCONFIG.MasVerificationRecordUI AS MasVerificationRecordUI
		ON defect.i_verification_record_id = MasVerificationRecordUI.i_verification_record_id
			AND defect.i_project_id = MasVerificationRecordUI.i_project_id
	INNER JOIN SCHEMABFS.lean_package_projectdetails AS proj
		ON proj.projectid = defect.i_project_id
	INNER JOIN cusconfig.[TrnDefectEffortUI] effort
		ON effort.i_defect_detail_ref = defect.i_defect_detail_ref
			AND effort.d_entry_date <= proj.ENDDATE
	INNER JOIN TRN_SVC_TRACKING AS ServiceTracking
		ON defect.I_PROJECT_ID =ServiceTracking.PROJECT_ID
	INNER JOIN cusconfig.[pcprocesspackagetasksUI] AS task
		ON task.taskid = MasVerificationRecordUI.detectbytaskid
	INNER JOIN cusconfig.[PCPROCESSPACKAGEWORKFLOWSUI] AS workflow
		ON task.workflowid = workflow.workflowid
	INNER JOIN cusconfig.[PCPROCESSPACKAGEPHASESUI] AS phase
		ON workflow.phaseid = phase.phaseid