select
	ct.ObjectNumber as 'Contract Reference No.',
	ct.ObjectName as 'SESAMi Contract No.',
	v.ObjectName as 'Vendor Name',

	w.ObjectNumber as 'Work Number',
	w.CurrentActivityName as 'Work Status',
	w.CreatedDateTime as 'Work Created Date',
	c.ObjectNumber as 'SR Number',
	c.ReportedDateTime as 'SR Date',
	w.WorkDescription as 'Work Description',
	tow.ObjectName as 'Type of Work',
	tos.ObjectName as 'Type of Service',
	topr.ObjectName as 'Type of Problem',
	w.AcknowledgementDateTime as 'Acknowledgement',
	w.ArrivalDateTime as 'Arrival',
	w.ScheduledStartDateTime as 'Scheduled Start',
	w.ScheduledEndDateTime as 'Scheduled End',
	tech.ObjectName as 'SPA',
	act.ObjectNumber as 'WBS',
	w.EstimatedWorkCost as 'Work Estimated Cost',
	w.CauseOfProblemText as 'Cause Of Problem',
	w.ResolutionDescription as 'Resolution Description',
	w.ActualStartDateTime as 'Actual Start',
	w.ActualEndDateTime as 'Actual End',
	atow.ObjectName as 'Actual Type of Work',
	atos.ObjectName as 'Actual Type of Service',
	atopr.ObjectName as 'Actual Type of Problem',

	bq.ObjectNumber as 'BQ Number',
	bq.CurrentActivityName as 'BQ Status',
	bq.CreatedDateTime as 'BQ Created Date',
	bq.Date as 'BQ Date',
	bqicgl.ObjectNumber as 'GL No.',
	case
		when bq.ObjectID is not null then SUM(ISNULL(bqic.UnitPrice, 0) * ISNULL(bqic.Quantity, 0)) OVER(PARTITION BY bq.ObjectID)
	end as 'BQ Amount',

	po.SAPPONumber as 'EPS PO Number',
	po.CurrentActivityName as 'PO Status',
	case po.InterfaceStatus
		when 0 then 'Pending SESAMi Acknowledgement'	-- SESAMiPOInterfaceStatus.PendingSESAMiAcknowledgement
		when 1 then 'Pending SESAMi Approval'			-- SESAMiPOInterfaceStatus.PendingForApproval
		when 2 then 'Failed SESAMi Validation'			-- SESAMiPOInterfaceStatus.Rejected
		when 3 then 'Rejected by Approver'				-- SESAMiPOInterfaceStatus.ApproverRejected
		when 4 then 'SAP PO Approved'					-- SESAMiPOInterfaceStatus.POCreated
		when 7 then 'SAP PO Creation Failed'			-- SESAMiPOInterfaceStatus.SAPProcessFailed
		else ''
	end as 'PO Interface Status',
	po.SESAMiApprover1ID as 'SESAMi Approver 1',
	po.SESAMiApprover2ID as 'SESAMi Approver 2',
	poi.ItemNumber as 'PO Item - Number',
	cws.SupplierPartNumber + ' - ' + cws.ShortDescription as 'PO Item - SOR',
	poi.UnitPrice as 'PO Item - Price',
	poi.Quantity as 'PO Item - Quantity',
	case
		when po.ObjectID is not null then SUM(ISNULL(poi.UnitPrice, 0) * ISNULL(poi.Quantity, 0)) OVER(PARTITION BY po.ObjectID)
	end as 'PO Amount',

	gr.ObjectNumber as 'GRS Number',
	gr.CurrentActivityName as 'GRS Status',
	case gr.InterfaceStatus
		when 0 then 'Pending SESAMi Acknowledgement'	-- SESAMiPOInterfaceStatus.PendingSESAMiAcknowledgement
		when 1 then 'Pending SAP Creation'				-- SESAMiPOInterfaceStatus.PendingSAPCreation
		when 2 then 'SESAMi Validation Failed'			-- SESAMiPOInterfaceStatus.Rejected
		when 3 then 'SAP GRS Approved'					-- SESAMiPOInterfaceStatus.GRSCreated
		when 5 then 'SAP GRS/GRN Creation Failed'		-- SESAMiPOInterfaceStatus.SAPProcessFailed
		else ''
	end as 'GRS Interface Status',
	case
		when gr.ObjectID is not null then SUM(ISNULL(gri.UnitPrice, 0) * ISNULL(gri.Quantity, 0)) OVER(PARTITION BY gr.ObjectID)
	end as 'GRS Amount'
from [Contract] ct
left join Vendor v on v.ObjectID = ct.VendorID

-- Work
left join Work w on w.ContractID = ct.ObjectID
	and w.IsDeleted = 0
	and w.CostRequiredIndicator = 1
left join [Case] c on c.ObjectID = w.CaseID
left join Code tow on tow.ObjectID = w.TypeOfWorkID
left join Code tos on tos.ObjectID = w.TypeOfServiceID
left join Code topr on topr.ObjectID = w.TypeOfProblemID
left join Code atow on atow.ObjectID = w.ActualTypeOfWorkID
left join Code atos on atos.ObjectID = w.ActualTypeOfServiceID
left join Code atopr on atopr.ObjectID = w.ActualTypeOfProblemID
left join GLAccount gl on gl.ObjectID = w.ExpenseAccountID
left join GLSegmentValue act on act.ObjectID = gl.SegmentValue4ID
left join [User] tech on tech.ObjectID = w.TechnicianID

-- BQ
left join BillingQuantityItemCost bqic on bqic.IsDeleted = 0
	and w.ObjectID = (
		select tbqi.WorkID
		from BillingQuantityItem tbqi
		left join BillingQuantity tbq on tbq.ObjectID = tbqi.BillingQuantityID
		where tbqi.IsDeleted = 0 and tbq.IsDeleted = 0
		and tbqi.ObjectID = bqic.BillingQuantityItemID
	)
left join BillingQuantityItem bqi on bqi.ObjectID = bqic.BillingQuantityItemID
left join BillingQuantity bq on bq.ObjectID = bqi.BillingQuantityID
left join BillingQuantityItemSummary bqis on bqis.ObjectID = bqic.BillingQuantityItemSummaryID
left join GLSegmentValue bqicgl on bqicgl.ObjectID = bqic.GLID

-- PO
--left join FMSPurchaseOrderItem poi on poi.BillingQuantityItemSummaryID = bqis.ObjectID
--	and EXISTS(select tpo.ObjectID
--		from FMSPurchaseOrder tpo
--		where tpo.IsDeleted = 0
--		and tpo.ObjectID = poi.FMSPurchaseOrderID
--	)
left join FMSPurchaseOrder po on (po.ObjectID = bq.ActualFMSPurchaseOrderID and po.IsDeleted=0)
left join FMSPurchaseOrderItem poi on poi.FMSPurchaseOrderID = po.ObjectID
left join ContractWorkService cws on cws.ObjectID = poi.ContractWorkServiceID

-- GR
left join FMSGoodReceiptItem gri on gri.FMSPurchaseOrderItemID = poi.ObjectID
	and EXISTS(select tgr.ObjectID
		from FMSGoodReceipt tgr
		where tgr.IsDeleted = 0
		and tgr.ObjectID = gri.FMSGoodReceiptID
	)
left join FMSGoodReceipt gr on gr.FMSPurchaseOrderID = po.ObjectID

where EXISTS (
	select cl.ObjectID
	from ContractLocation cl
	where cl.IsDeleted = 0
	and cl.ContractID = ct.ObjectID
)
and ct.ObjectNumber='#00000082'