CREATE PROCEDURE dbo.sp_Vecellio_JobInquiry
(
	@JobNumber INT,
	@UserID VARCHAR(10),
	@Text VARCHAR(20) = NULL,
	@DateFrom DATETIME = NULL,
	@DateTo DATETIME = NULL,
             @RetValue CHAR(50) OUTPUT
)


AS

SET NOCOUNT ON


CREATE TABLE	#AuthorityTable
		(
			busunit_from BIGINT,
			busunit_to BIGINT
		)

INSERT	#AuthorityTable
	(
		busunit_from,
		busunit_to
	)


SELECT			msmcuf,
	          			msmcut
FROM				OPENQUERY(vgsys400, 'SELECT * FROM vgisec.f0001')

WHERE			msuser = @UserID

IF @@ROWCOUNT = 0
	BEGIN
		SET @RetValue = 'You are not authorized to any selection.'
		RETURN
	END

SELECT	dbo.Job.CompanyJobID,
		dbo.Job.ChangeDate as date,
		dbo.Job.Name,
		dbo.Region.CompanyRegionID,
		dbo.Job.Active,
		MAX(CASE dbo.SourceType.CompanySourceTypeID WHEN 'MA' THEN 'Yes' ELSE 'No' END) AS Material,
		MAX(CASE dbo.SourceType.CompanySourceTypeID WHEN 'PR' THEN 'Yes' ELSE'No' END) AS Production,
                           MAX(CASE dbo.SourceType.CompanySourceTypeID WHEN 'SB' THEN 'Yes' ELSE'No' END) AS Sub,
		MAX(CASE dbo.EquipmentLaborEvent.Hours WHEN 0 THEN 'No' ELSE 'Yes' END) AS Equipment,
		MAX(CASE dbo.EmployeeLaborEvent.Hours WHEN 0 THEN 'No' ELSE 'Yes' END) AS Labor,
		MAX(CASE dbo.Diary.DiaryEntry WHEN 0 THEN 'No' ELSE 'Yes' END) AS Diary
FROM		dbo.Job
			INNER JOIN	dbo.Event ON dbo.Event.JobGuid = dbo.Job.JobGuid
                                        INNER JOIN	dbo.ProductionEvent ON dbo.ProductionEvent.EventGuid = dbo.Event.EventGuid
			LEFT JOIN	dbo.Product ON dbo.Product.ProductGuid = dbo.ProductionEvent.ProductGuid
			LEFT JOIN	dbo.Item ON dbo.Item.ItemGuid = dbo.Event.ItemGuid
			INNER JOIN	dbo.Source ON dbo.Source.SourceGuid = dbo.ProductionEvent.SourceGuid
			INNER JOIN	dbo.SourceType ON dbo.SourceType.SourceTypeGuid = dbo.Source.SourceTypeGuid
			LEFT JOIN	dbo.EquipmentLaborEvent ON dbo.EquipmentLaborEvent.EventGuid = dbo.Event.EventGuid
			LEFT JOIN	dbo.EmployeeLaborEvent ON dbo.EmployeeLaborEvent.EventGuid = dbo.Event.EventGuid
                                        LEFT JOIN         dbo.Batch on dbo.Batch.BatchGuid = dbo.Event.EventGuid
			LEFT JOIN         dbo.Diary ON dbo.Diary.BatchGuid = dbo.Batch.BatchGuid
			INNER JOIN	#AuthorityTable AS at ON at.busunit_from <= dbo.Job.CompanyJobId
		    			AND at.busunit_to >= dbo.Job.CompanyJobId
			LEFT JOIN	dbo.Region ON dbo.Region.RegionGuid = dbo.Job.RegionGuid
WHERE	dbo.SourceType.CompanySourceTypeID IN ('PR', 'MA', 'SB')
			AND (dbo.Job.CompanyJobID LIKE CAST(@JobNumber AS VARCHAR(10)) + '%' OR @JobNumber = 0 OR @JobNumber IS NULL)
			AND (dbo.Job.Name LIKE '%' + @Text + '%' OR @Text IS NULL)
			AND (dbo.Job.ChangeDate >= @DateFrom OR @DateFrom IS NULL)
			AND (dbo.Job.ChangeDate <= @DateTo OR @DateTo IS NULL)
GROUP BY	dbo.Job.CompanyJobID,
			dbo.Job.ChangeDate,
			dbo.Region.CompanyRegionID,
			dbo.Job.Name,
			dbo.Job.Active
ORDER BY       dbo.Job.CompanyJobID

IF @@ROWCOUNT = 0
                          SET @RetValue = 'No match for selection were found.'
		RETURN
	            --RAISERROR('No matching records.', 16, 1)

DROP TABLE	#AuthorityTable
GO