SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE OR ALTER VIEW [sStudent].[vStudentAcademic]
AS
SELECT        A.Id AS AcademicId, A.ClassId, A.DivisionId, A.RollNo, A.OrgId, A.BatchId, SM.Id AS StudentId, SM.Code, ISNULL(SM.Number, 0) AS Number, SM.RegistrationNumber, SM.FirstName, SM.MiddleName, SM.LastName, 
                         sStudent.fStudentNameGet(SM.FirstName, SM.MiddleName, SM.LastName, OM.StudentNameFormatId) AS Name, CONVERT(Nvarchar(20), A.RollNo) + '-' + SM.FirstName + ' ' + SM.LastName AS RollNoName, SM.StatusId, 
                         CM.DisplayTitle AS ClassName, DM.DisplayTitle AS DivisionName, STM.Title AS StatusName, SM.RecordStatusId, DM.InchargeEmployeeId, BM.OrgGroupBatchId, ISNULL(A.AdmissionCategoryId, 0) AS AdmissionCategoryId, 
                         DM.OrderIndex AS DivisionOrder, BM.IsActive AS IsActiveBatch, BM.Title AS BatchName, ISNULL(DeptM.Id, 0) AS DepartmentId, ISNULL(DeptM.Title, '') AS DepartmentName, SM.IsBlackList, SM.BlackListReason, 
                         CM.OrderIndex AS ClassOrderIndex, ISNULL(A.ExemptionTypeId, 0) AS ExemptionTypeId, ISNULL(OM.DisplayTitle, '') AS OrganizationName, OM.StreetOne, OM.StreetTwo, OM.StreetThree, OM.ZipCode, ISNULL(SM.SUId, '') 
                         AS SUId, ISNULL(OM.ReportOrgGroupTitle, '') AS ReportOrgGroupTitle, ISNULL(OM.ReportOrgTitle, '') AS ReportOrgTitle, ISNULL(A.FeeStructureId, 0) AS FeeStructureId, SM.ReferenceSourceId, SM.StudentGroupId, 
                         SM.StudentOtherGroupId, A.BatchStatusId, A.ClassStatusId, STM1.Title AS ClassStatusName, ISNULL(SO.GenderId, 0) AS GenderId, ISNULL(A.FeesRemark, '') AS FeesRemark,
						 --Mantis Start -0031338  
						 ISNULL(SM.PENNumber, 0) AS PENNumber,
						 --Mantis End -0031338 
						 ISNULL(SM.LocalName,'') AS LocalName,
						 ISNULL(SM.ApaarIdNumber, '') AS ApaarIdNumber--Added by dhananjay for Mantis -38255  

FROM            sStudent.tAcademic AS A INNER JOIN
                         sStudent.tStudentMaster AS SM ON A.StudentId = SM.Id INNER JOIN
                         sSetup.tClassMaster AS CM ON CM.Id = A.ClassId INNER JOIN
                         sSetup.tDivisionMaster AS DM ON DM.Id = A.DivisionId INNER JOIN
                         sSetup.tBatchMaster AS BM ON BM.Id = A.BatchId INNER JOIN
                         sStudent.tStatusMaster AS STM ON STM.Id = SM.StatusId INNER JOIN
                         sSetup.tDepartmentMaster AS DeptM ON DeptM.Id = CM.DepartmentId INNER JOIN
                         sSetup.tOrganizationMaster AS OM ON A.OrgId = OM.Id INNER JOIN
                         sStudent.tStatusMaster AS STM1 ON STM1.Id = A.ClassStatusId LEFT OUTER JOIN
                         sStudent.tStudentOther AS SO ON SO.StudentId = SM.Id

GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE OR ALTER VIEW [sCertificate].[vPrintTemplateTransferCertificateReport]
AS
	SELECT
		T.Id,
		SM.Code,
		T.AcademicId,
		SM.FirstName,SM.MiddleName,SM.LastName,
		[sStudent].[fStudentNameGet](SM.FirstName , SM.MiddleName, SM.LastName,OM.StudentNameFormatId ) AS Name,
		ISNULL(SF.FatherName,'') As FatherName,
		ISNULL(SF.MotherName,'') As MotherName,
		ISNULL(CTM.Nationality,'') AS Nationality,
		ISNULL(RM.Title,'') AS Religion,
		ISNULL(so.BirthPlace,'') AS BirthPlace,
		ISNULL(SO.BirthTaluka,'') AS BirthTaluka,
		ISNULL(SO.BirthDistrict,'') AS BirthDistrict,
		SO.DateOfBirth,
		ISNULL(SO.BirthDateInWord,'') AS DOBInWord,
		SA.PreviousSchool,
		SA.AdmissionDate,
		--L.LCNumber,
		T.Number,
		T.TCDate,
		T.LeftDate,
		T.LeftDate as [TransferDate],
		T.SemesterDuration,
		T.ExamName,
		T.ExamSeatNo,
		T.ExamYear,
		T.ExamResult,
		T.PromotedClass,
		T.TransferConduct,
		T.OptionalSubject,
		T.Reason,
		T.Remark,
		SO.GenderId,
		ISNULL(SO.FamilySubCast,'') AS SubCaste,
		ISNULL(SO.FamilyCast,'') AS Caste,
		ISNULL(OM.CityName,'') AS City,
		ISNULL(OM.StateName,'') AS CityState,
		CM.DisplayTitle AS AdmissionStandard,
		CMS.DisplayTitle AS CurrentStandard,
		CMS.Title AS CurrentStandardTitle,
		SM.StatusId,
		OM.OrgLogo,
		OM.Title AS OrgTitle,
		OM.StreetOne+' '+OM.StreetTwo+' '+OM.StreetThree AS OrgAddress,
		OM.PhoneNo,
		vSA.RollNo,
		DM.OrderIndex AS DivisionOrder,
		OM.SmallAddress1,
		OM.SmallAddress2,
		OM.RegistrationDetail,
		AD.PresentDaysAnnual AS PresentDaysSum,
		AD.TotalDaysAnnual AS TotalDaysSum,
		vSA.ClassName AS ClassName,
		vSA.DivisionName AS DivisionName,
		OI.CertificateHeaderA4,
		isnull(MainCastCategoryId,0) AS CastCategoryId,
		ISNULL(OM.University,'')AS University,
		ISNULL(CSG.Inword,'')AS CurrentStandardInWords,
		SO.MotherTongue,
		SO.AdhaarNumber,
		vSA.SUId,
		CASE WHEN isnull(BM.FromDate,'')>Isnull(SA.AdmissionDate,'') THEN BM.FromDate ELSE SA.AdmissionDate END AS BatchDate,
		SO.BirthState,
		SO.BirthCountry,
		SD.BankAccountNumber,
		SO.UIDNumber,
		SD.BankName,
		SD.BankIFSCCode,
		SA.PreviousStandard,		
		DM.InchargeEmployeeId AS ClassTeacher,
		vSA.BatchName AS BatchName,
		CSM.Title As CasteCategory,
		vSA.BatchId,
		ISNULL(SM.LocalName,'') AS LocalName,	
		CASE WHEN SO.GenderId = 1 Then 'Kumar'
			WHEN SO.GenderId = 2 Then 'Kumari' END AS [Kumar-Kumari],
		CASE WHEN SO.GenderId = 1 Then 'He'
			WHEN SO.GenderId = 2 Then 'She' END AS [He-She],
		CASE WHEN SO.GenderId = 1 Then 'His'
			WHEN SO.GenderId = 2 Then 'Her' END AS [His-Her],
		CASE WHEN SM.StatusId = 1 Then 'is'
			ELSE 'was' END AS [is-was],
		'[Number],[TCDate],[TransferDate],[SemesterDuration],[ExamName],[ExamSeatNo],[ExamYear],[ExamResult],
		[PromotedClass],[TransferConduct],[OptionalSubject],[Reason],[Remark],
		[FirstName],[MiddleName],[LastName],[FatherName],[MotherName],[Name],[Code],[ClassName],[Divisionname],[DateOfBirth],[DOBInWord],[Kumar-Kumari],[He-She],[His-Her],[is-was]' As ColumnList

	FROM		
		sCertificate.tTransfer T
		INNER JOIN sStudent.vStudentAcademic vSA ON vSA.AcademicId=T.AcademicId
		LEFT JOIN sStudent.tStudentAdmission SA ON SA.StudentId=vSA.StudentId
		INNER JOIN sStudent.tStudentMaster SM ON SM.Id=vSA.StudentId
		INNER JOIN sSetup.tOrganizationMaster OM ON OM.Id=vSA.OrgId
		LEFT JOIN sStudent.tStudentOther SO ON SO.StudentId=vSA.StudentId
		LEFT JOIN sCommon.tReligionMaster RM ON SO.ReligionId=RM.Id
		LEFT JOIN sSetup.tClassMaster CM ON CM.Id=SA.AdmissionStandardId
		INNER JOIN sSetup.tDivisionMaster DM ON vSA.DivisionId = DM.Id
		INNER JOIN sSetup.tClassMaster CMS ON CMS.Id= DM.ClassId
		LEFT JOIN sStudent.tStudentFamily SF ON SF.StudentId=vSA.StudentId
		LEFT JOIN sCommon.tCountryMaster CTM ON CTM.Id=SO.NationalityId
		LEFT JOIN sStudent.tAcademicDetail AD ON AD.AcademicId=vSA.AcademicId
		LEFT JOIN sSetup.tOrgImages OI ON OI.OrgId = OM.Id
		LEFT JOIN sCommon.tCastCategoryMaster CSM ON CSM.Id=SO.CastCategoryId
		LEFT JOIN sSetup.tClassGroupMaster CSG ON CSG.Id=CMS.ClassGroupId
		INNER JOIN sSetup.tBatchMaster BM ON BM.Id=vSA.BatchId
		LEFT JOIN sStudent.tStudentAdditional SD ON SD.StudentId=vSA.StudentId

GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE OR ALTER VIEW [sCertificate].[vPrintTemplateLeavingCertificate]
AS
	SELECT
		L.Id,
		SM.Code,
		L.AcademicId,
		SM.FirstName,SM.MiddleName,SM.LastName,
		[sStudent].[fStudentNameGet](SM.FirstName , SM.MiddleName, SM.LastName,OM.StudentNameFormatId ) AS Name,
		ISNULL(SF.FatherName,'') As FatherName,
		ISNULL(SF.MotherName,'') As MotherName,
		ISNULL(CTM.Nationality,'') AS Nationality,
		ISNULL(RM.Title,'') AS Religion,
		ISNULL(so.BirthPlace,'') AS BirthPlace,
		ISNULL(SO.BirthTaluka,'') AS BirthTaluka,
		ISNULL(SO.BirthDistrict,'') AS BirthDistrict,
		SO.DateOfBirth,
		ISNULL(SO.BirthDateInWord,'') AS DOBInWord,
		SA.PreviousSchool,
		SA.AdmissionDate,
		L.LCNumber,
		L.LeftDate,
		L.LCReason,
		L.LCProgress,
		L.LCConduct,
		L.LCRemark,
		L.LCDate,
		L.LCBookNumber,
		SO.GenderId,
		ISNULL(SO.FamilySubCast,'') AS SubCaste,
		ISNULL(SO.FamilyCast,'') AS Caste,
		ISNULL(OM.CityName,'') AS City,
		ISNULL(OM.StateName,'') AS CityState,
		CM.DisplayTitle AS AdmissionStandard,
		CMS.DisplayTitle AS CurrentStandard,
		CMS.Title AS CurrentStandardTitle,
		SM.StatusId,
		L.LastExamName AS Result,
		L.LastExamResult AS FailOrPass,
		L.SubjectStudied AS SubjectNAme,
		L.RepeatDetail as PromotedorNot,
		L.PromoteToClass  AS PromotedToClass,
		L.FeeDetail AS LastFeesPaid,
		L.FeeConcessionDetail AS AnyConcession,
		L.NCCScoutDetail  AS NCCScoutDetail,
		L.ExtraCurricularActivities AS GamesPlayed,
		OM.OrgLogo,
		OM.Title AS OrgTitle,
		OM.StreetOne+' '+OM.StreetTwo+' '+OM.StreetThree AS OrgAddress,
		OM.PhoneNo,
		vSA.RollNo,
		DM.OrderIndex AS DivisionOrder,
		OM.SmallAddress1,
		OM.SmallAddress2,
		OM.RegistrationDetail,
		AD.PresentDaysAnnual AS PresentDaysSum,
		AD.TotalDaysAnnual AS TotalDaysSum,
		vSA.ClassName AS ClassName,
		vSA.DivisionName AS DivisionName,
		OI.CertificateHeaderA4,
		isnull(MainCastCategoryId,0) AS CastCategoryId,
		ISNULL(L.ToCollege,'') AS ToCollege,
		ISNULL(L.ToCollegeAddress,'')AS ToCollegeAddress,
		ISNULL(OM.University,'')AS University,
		ISNULL(CSG.Inword,'')AS CurrentStandardInWords,
		SO.MotherTongue,
		SO.AdhaarNumber,
		vSA.SUId,
		CASE WHEN isnull(BM.FromDate,'')>Isnull(SA.AdmissionDate,'') THEN BM.FromDate ELSE SA.AdmissionDate END AS BatchDate,
		SO.BirthState,
		SO.BirthCountry,
		SD.BankAccountNumber,
		SO.UIDNumber,
		SD.BankName,
		SD.BankIFSCCode,
		SA.PreviousStandard,
		L.TermsInInstitute,
		L.LCCopy,
		(EM.FirstName + ' ' + EM.LastName) AS ClassTeacher,
		vSA.BatchName AS BatchName,
		CSM.Title As CasteCategory,
		vSA.BatchId,
		GetDate() AS PrintDate, --Change for Mantis id 29447 and also added in below row "[PrintDate]"
		ISNULL(SM.LocalName,'') As LocalName,	--Add for Mantis: 32199
		ISNULL(L.DisplayName,NULL) As DisplayName,	--Add for mantis : 0041217
		CASE WHEN SO.GenderId = 1 Then 'Kumar'
			WHEN SO.GenderId = 2 Then 'Kumari' END AS [Kumar-Kumari],
		CASE WHEN SO.GenderId = 1 Then 'He'
			WHEN SO.GenderId = 2 Then 'She' END AS [He-She],
		CASE WHEN SO.GenderId = 1 Then 'His'
			WHEN SO.GenderId = 2 Then 'Her' END AS [His-Her],
		CASE WHEN SM.StatusId = 1 Then 'is'
			ELSE 'was' END AS [is-was],
		'[AdhaarNumber],[AdmissionDate],[AdmissionStandard],[AnyConcession],[BankAccountNumber],[BankIFSCCode],[BankName],[BatchDate],[BatchName],[BirthCountry],[DOBInWord],[BirthDistrict],[BirthPlace],[BirthState],[BirthTaluka],[Caste],[CasteCategory],[CertificateHeaderA4],[City],[CityState],[ClassName],[ClassTeacher],[Code],[CurrentStandard],[CurrentStandardInWords],[CurrentStandardTitle],[DateOfBirth],[DivisionName],[DivisionOrder],[FailOrPass],[FatherName],[FirstName],[GamesPlayed],[LastFeesPaid],[LastName],[LCBookNumber],[LCConduct],[LCCopy],[LCDate],[LCNumber],[LCProgress],[LCReason],[LCRemark],[LeftDate],[MiddleName],[MotherName],[MotherTongue],[Name],[Nationality],[NCCScoutDetail],[OrgAddress],[OrgLogo],[OrgTitle],[PhoneNo],[PresentDaysSum],[PreviousSchool],[PreviousStandard],[PromotedorNot],[PromotedToClass],[RegistrationDetail],[Religion],[Result],[RollNo],[SUId],[SmallAddress1],[SmallAddress2],[SubCaste],[SubjectNAme],[TermsInInstitute],[ToCollege],[ToCollegeAddress],[TotalDaysSum],[UIDNumber],[University],[PrintDate],[LocalName],[Kumar-Kumari],[He-She],[His-Her],[is-was]' As ColumnList
		--Mantis: 32199, add LocalName in ColumnList
	FROM
		sCertificate.tLeaving L
		INNER JOIN sStudent.vStudentAcademic vSA ON vSA.AcademicId=L.AcademicId
		LEFT JOIN sStudent.tStudentAdmission SA ON SA.StudentId=vSA.StudentId
		INNER JOIN sStudent.tStudentMaster SM ON SM.Id=vSA.StudentId
		INNER JOIN sSetup.tOrganizationMaster OM ON OM.Id=vSA.OrgId
		LEFT JOIN sStudent.tStudentOther SO ON SO.StudentId=vSA.StudentId
		LEFT JOIN sCommon.tReligionMaster RM ON SO.ReligionId=RM.Id
		LEFT JOIN sSetup.tClassMaster CM ON CM.Id=SA.AdmissionStandardId
		INNER JOIN sSetup.tDivisionMaster DM ON vSA.DivisionId = DM.Id
		INNER JOIN sSetup.tClassMaster CMS ON CMS.Id= DM.ClassId
		LEFT JOIN sStudent.tStudentFamily SF ON SF.StudentId=vSA.StudentId
		LEFT JOIN sCommon.tCountryMaster CTM ON CTM.Id=SO.NationalityId
		LEFT JOIN sStudent.tAcademicDetail AD ON AD.AcademicId=vSA.AcademicId
		LEFT JOIN sSetup.tOrgImages OI ON OI.OrgId = OM.Id
		LEFT JOIN sCommon.tCastCategoryMaster CSM ON CSM.Id=SO.CastCategoryId
		LEFT JOIN sSetup.tClassGroupMaster CSG ON CSG.Id=CMS.ClassGroupId
		INNER JOIN sSetup.tBatchMaster BM ON BM.Id=vSA.BatchId
		LEFT JOIN sStudent.tStudentAdditional SD ON SD.StudentId=vSA.StudentId
		LEFT JOIN sHrm.tEmployeeMaster EM ON DM.InchargeEmployeeId = EM.Id			-- MantisId : 0033805

GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE OR ALTER VIEW [sCertificate].[vPrintTemplateTransferCertificate]
AS
	SELECT
		L.Id,
		SM.Code,
		L.AcademicId,
		SM.FirstName,SM.MiddleName,SM.LastName,
		[sStudent].[fStudentNameGet](SM.FirstName , SM.MiddleName, SM.LastName,OM.StudentNameFormatId ) AS Name,
		ISNULL(SF.FatherName,'') As FatherName,
		ISNULL(SF.MotherName,'') As MotherName,
		ISNULL(CTM.Nationality,'') AS Nationality,
		ISNULL(RM.Title,'') AS Religion,
		ISNULL(so.BirthPlace,'') AS BirthPlace,
		ISNULL(SO.BirthTaluka,'') AS BirthTaluka,
		ISNULL(SO.BirthDistrict,'') AS BirthDistrict,
		SO.DateOfBirth,
		ISNULL(SO.BirthDateInWord,'') AS DOBInWord,
		SA.PreviousSchool,
		SA.AdmissionDate,
		L.LCNumber,
		L.LeftDate,
		L.LCReason,
		L.LCProgress,
		L.LCConduct,
		L.LCRemark,
		L.LCDate,
		L.LCBookNumber,
		SO.GenderId,
		ISNULL(SO.FamilySubCast,'') AS SubCaste,
		ISNULL(SO.FamilyCast,'') AS Caste,
		ISNULL(OM.CityName,'') AS City,
		ISNULL(OM.StateName,'') AS CityState,
		CM.DisplayTitle AS AdmissionStandard,
		CMS.DisplayTitle AS CurrentStandard,
		CMS.Title AS CurrentStandardTitle,
		SM.StatusId,
		L.LastExamName AS Result,
		L.LastExamResult AS FailOrPass,
		L.SubjectStudied AS SubjectNAme,
		L.RepeatDetail as PromotedorNot,
		L.PromoteToClass  AS PromotedToClass,
		L.FeeDetail AS LastFeesPaid,
		L.FeeConcessionDetail AS AnyConcession,
		L.NCCScoutDetail  AS NCCScoutDetail,
		L.ExtraCurricularActivities AS GamesPlayed,
		OM.OrgLogo,
		OM.Title AS OrgTitle,
		OM.StreetOne+' '+OM.StreetTwo+' '+OM.StreetThree AS OrgAddress,
		OM.PhoneNo,
		vSA.RollNo,
		DM.OrderIndex AS DivisionOrder,
		OM.SmallAddress1,
		OM.SmallAddress2,
		OM.RegistrationDetail,
		AD.PresentDaysAnnual AS PresentDaysSum,
		AD.TotalDaysAnnual AS TotalDaysSum,
		vSA.ClassName AS ClassName,
		vSA.DivisionName AS DivisionName,
		OI.CertificateHeaderA4,
		isnull(MainCastCategoryId,0) AS CastCategoryId,
		ISNULL(L.ToCollege,'') AS ToCollege,
		ISNULL(L.ToCollegeAddress,'')AS ToCollegeAddress,
		ISNULL(OM.University,'')AS University,
		ISNULL(CSG.Inword,'')AS CurrentStandardInWords,
		SO.MotherTongue,
		SO.AdhaarNumber,
		vSA.SUId,
		CASE WHEN isnull(BM.FromDate,'')>Isnull(SA.AdmissionDate,'') THEN BM.FromDate ELSE SA.AdmissionDate END AS BatchDate,
		SO.BirthState,
		SO.BirthCountry,
		SD.BankAccountNumber,
		SO.UIDNumber,
		SD.BankName,
		SD.BankIFSCCode,
		SA.PreviousStandard,
		L.TermsInInstitute,
		L.LCCopy,
		DM.InchargeEmployeeId AS ClassTeacher,
		vSA.BatchName AS BatchName,
		CSM.Title As CasteCategory,
		vSA.BatchId,
		ISNULL(SM.LocalName,'') AS LocalName,	--Add for Mantis: 32199
		'[AdhaarNumber],[AdmissionDate],[AdmissionStandard],[AnyConcession],[BankAccountNumber],[BankIFSCCode],[BankName],[BatchDate],[BatchName],[BirthCountry],[DOBInWord],[BirthDistrict],[BirthPlace],[BirthState],[BirthTaluka],[Caste],[CasteCategory],[CertificateHeaderA4],[City],[CityState],[ClassName],[ClassTeacher],[Code],[CurrentStandard],[CurrentStandardInWords],[CurrentStandardTitle],[DateOfBirth],[DivisionName],[DivisionOrder],[FailOrPass],[FatherName],[FirstName],[GamesPlayed],[LastFeesPaid],[LastName],[LCBookNumber],[LCConduct],[LCCopy],[LCDate],[LCNumber],[LCProgress],[LCReason],[LCRemark],[LeftDate],[MiddleName],[MotherName],[MotherTongue],[Name],[Nationality],[NCCScoutDetail],[OrgAddress],[OrgLogo],[OrgTitle],[PhoneNo],[PresentDaysSum],[PreviousSchool],[PreviousStandard],[PromotedorNot],[PromotedToClass],[RegistrationDetail],[Religion],[Result],[RollNo],[SmallAddress1],[SmallAddress2],[SubCaste],[SubjectNAme],[TermsInInstitute],[ToCollege],[ToCollegeAddress],[TotalDaysSum],[UIDNumber],[University],[LocalName]' As ColumnList
		--Mantis: 32199, add LocalName in ColumnList
	FROM
		sCertificate.tLeaving L
		INNER JOIN sStudent.vStudentAcademic vSA ON vSA.AcademicId=L.AcademicId
		LEFT JOIN sStudent.tStudentAdmission SA ON SA.StudentId=vSA.StudentId
		INNER JOIN sStudent.tStudentMaster SM ON SM.Id=vSA.StudentId
		INNER JOIN sSetup.tOrganizationMaster OM ON OM.Id=vSA.OrgId
		LEFT JOIN sStudent.tStudentOther SO ON SO.StudentId=vSA.StudentId
		LEFT JOIN sCommon.tReligionMaster RM ON SO.ReligionId=RM.Id
		LEFT JOIN sSetup.tClassMaster CM ON CM.Id=SA.AdmissionStandardId
		INNER JOIN sSetup.tDivisionMaster DM ON vSA.DivisionId = DM.Id
		INNER JOIN sSetup.tClassMaster CMS ON CMS.Id= DM.ClassId
		LEFT JOIN sStudent.tStudentFamily SF ON SF.StudentId=vSA.StudentId
		LEFT JOIN sCommon.tCountryMaster CTM ON CTM.Id=SO.NationalityId
		LEFT JOIN sStudent.tAcademicDetail AD ON AD.AcademicId=vSA.AcademicId
		LEFT JOIN sSetup.tOrgImages OI ON OI.OrgId = OM.Id
		LEFT JOIN sCommon.tCastCategoryMaster CSM ON CSM.Id=SO.CastCategoryId
		LEFT JOIN sSetup.tClassGroupMaster CSG ON CSG.Id=CMS.ClassGroupId
		INNER JOIN sSetup.tBatchMaster BM ON BM.Id=vSA.BatchId
		LEFT JOIN sStudent.tStudentAdditional SD ON SD.StudentId=vSA.StudentId

GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE OR ALTER VIEW [sStudent].[vStudentDetails]    
AS    
SELECT 	
	  SA.AcademicId,	  
	  SA.ClassId,
	  SA.DivisionId,
	  SA.RollNo,
	  SA.OrgId,
	  SA.BatchId,
	  BM.Title AS Batch,
	  SA.StudentId,
	  SA.SUId,
	  SA.Code,
	  SA.Number,
	  SA.FirstName,
	  SA.MiddleName,
	  SA.LastName,
	  SA.Name,
	  --SA.DepartmentId,
	  SA.StatusId,
	  SA.ClassName,
	  SA.DivisionName,
	  SA.StatusName,
	  SO.UIDNumber,
	  SO.AdhaarNumber,
      CONVERT(VARCHAR(10), SO.DateOfBirth, 111) AS DateOfBirth,
      SO.BirthdateInWord,
      SO.TransportModeId,
      TM.Title AS TransportMode,
      SO.GenderId,
      GM.Title AS Gender,
      SO.BloodGroupId,
      BGM.Title AS BloodGroup,
      SO.BirthPlace,
      SO.BirthTaluka,
      SO.BirthDistrict,
      SO.FamilyCast AS FamilyCaste,
      SO.FamilySubCast,
      
      
      SO.CastCategoryId,
      CCM.Title AS CasteCategory,
      SO.ReligionId,
      RM.Title AS Religion,
      SO.HouseId,
      HM.Title AS House,
      SO.MotherTongueId,
     SO.MotherTongue AS MotherTongue,
      SO.NationalityId,
      NM.Nationality AS Nationality,
      SO.Hobby,
      SH.IdentificationMark1,
      SH.IdentificationMark2,
      SH.IsPysicalDeformity,
      SH.PysicalDeformityRemark,
      SH.IsMajorIllness,
      SH.MajorIllnessRemark,
      SH.FamilyDoctorName,
      SH.FamilyDoctorContactNo,
      
	  SC.PresentStreetOne,
      SC.PresentStreetTwo,
      SC.PresentStreetThree,
      SC.PresentCountry,
      SC.PresentState,
      SC.PresentCity,
      SC.PresentZipCode,
      SC.PresentContactNo,
      SC.PermanentStreetOne,
      SC.PermanentStreetTwo,
      SC.PermanentStreetThree,
      SC.PermanentCountry,
      SC.PermanentState,
      SC.PermanentCity,
      SC.PermanentZipCode,
      SC.PermanentContactNo,
      SC.EmergencyStreetOne,
      SC.EmergencyStreetTwo,
      SC.EmergencyStreetThree,
      SC.EmergencyCountry,
      SC.EmergencyState,
      SC.EmergencyCity,
      SC.EmergencyZipCode,
      SC.EmergencyContactNo,
      SC.ParentContactNo,
      SC.ParentEmail,
      SC.StudentContactNo,
      SC.StudentEmail,
	  RT.BusRouteDestinationId AS BusRouteDestinationId,
	  RT.BusRouteDestination AS BusRouteDestination,
	  SF.FatherName,
	  CONVERT(VARCHAR(10), SF.FatherDateOfBirth, 111)AS FatherDateOfBirth,
	  SF.FatherQualification,
	  SF.FatherOccupation,
	  SF.FatherContactNo,
	  SF.FatherMail,
	  SF.FatherOfficeAddress,
	  SF.FatherIncome,
	  SF.MotherName,
	 CONVERT(VARCHAR(10),  SF.MotherDateOfBirth,111) AS MotherDateOfBirth,
	  SF.MotherQualification,
	  SF.MotherOccupation,
      SF.MotherContactNo,
      SF.MotherMail,
      SF.MotherOfficeAddress,
      SF.MotherIncome,      
	  ISNULL(SA.AdmissionCategoryId,0) AS AdmissionCategoryId,
	  AC.Title AS AdmissionCategory,
	  SAD.PreviousSchool,
	  CONVERT(VARCHAR(10), SAD.AdmissionDate, 111) AS AdmissionDate,
	  SAD.AdmissionFormNumber,
	  SAD.AdmissionNumber,
	  SAD.IsTransfer,
	  SAD.TransferFrom,
	  SAD.PassingYear,
	  SAD.IsTFWS,
	  SAD.IsCertificateHolder,
	  SAD.CertificateName,
	  AQM.Title AS AdmissionQuota,
	  CONVERT(VARCHAR(10),SAD.LeftDate, 111) AS LeftDate,
	  SA.IsBlackList,
	  SA.BlackListReason,
	  SA.DivisionOrder,
	  SA.ClassOrderIndex,
	  AD.RepeatCount,
	  ADI.BankAccountNumber,
	  ADi.BankBranch,
	  ADI.BankIFSCCode,
	  ADI.BankName,
	  ESM.Title AS StudentBatchStatus,
	  EnrollmentStatusId AS StudentStatusId,
	  ADI.Class9BoardRegistrationNumber,
	  ADI.Class11BoardRegistrationNumber,
	  ADI.PassportNumber,
	  ADI.PassportType,
	  ADI.PassportCountryCode,
	CONVERT(VARCHAR(10),ADI.PassportIssueDate,111) AS PassportIssueDate,
	  ADI.PassportExpiryDate,
	  '' SiblingName1,
	  '' SiblingInstitue1,
	  '' SiblingClass1,
	  '' SiblingAge1,
	  '' SiblingRelation1,
	  '' SiblingName2,
	  '' SiblingInstitue2,
	  '' SiblingClass2,
	  '' SiblingAge2,
	  '' SiblingRelation2,
	  '' SiblingName3,
	  '' SiblingInstitue3,
	  '' SiblingClass3,
	  '' SiblingAge3,
	  '' SiblingRelation3,
	  ISNULL(HHM.Id,'') AS HostelId,
	  ISNULL(HHM.Title,'') AS HostelName,
	  ISNULL(HRM.Id,'') AS RoomId,
	  ISNULL(HRM.Title,'') AS Room,
	  ISNULL(HBM.Title,'') AS BuildingName,
	  ISNULL(HAR.BedNumber,'') AS BedNumber,
	  
	  ISNULL(SM.StudentGroupId,0) AS StudentGroupId,
	  ISNULL(SM.StudentOtherGroupId,0) AS StudentOtherGroupId,
	  
	  SGM.Title AS StudentGroup,
	  SOGM.Title AS StudentOtherGroup,
	  SO.BirthState,
	  So.BirthCountry,
	  ISNULL(RT.BusRouteId,0) AS BusRouteId,
	  ISNULL(RT.PickupTime,'') AS PickupTime,
	  ISNULL(RT.Droptime,'') AS Droptime,
	  ISNULL(RT.BusRoute,'' )AS BusRoute,
	   CD.SSCBoard,
	   CD.SSCSchool,
	  CD.SSCPassingYear,
	  CD.SSCObtainMarks,
	  CD.SSCOutOfMarks,
	  CD.SSCPercentage,
	  CD.SSCMathMarks,
	  CD.SSCMathOutOfMarks,
	  CD.HSCBoard,
	  CD.HSCSchool,
	  CD.HSCPassingYear,
	  CD.HSCObtainMarks,
	  CD.HSCOutOfMarks,
	  CD.HSCPercentage,
	  
	  CD.HSCBiologyMarks,
	  CD.HSCBiologyOutOfMarks,
	 
	  CD.HSCChemistryMarks,
	  CD.HSCChemistryOutOfMarks,
	  CD.HSCMathMarks,
	  CD.HSCMathOutOfMarks,
	  cd.HSCPhysicsMarks,
	  CD.HSCPhysicsOutOfMarks,
	  CD.PCMMarks,
	  CD.PCBMarks,
	  CD.JEERollNo,
	  CD.JEEMeritNo,
	  CD.JEEMeritMarks,
	  CD.JEEOutOfMarks,
	  CD.DiplomaBoard,
	  CD.DiplomaCollege,
	  CD.DiplomaPassingYear,
	  CD.DiplomaObtainMarks,
	  CD.DiplomaOutOfMarks,
	  CD.DiplomaPercentage,
	  CD.ApplicationNo,
	  CD.AdmissionRound,
	  CD.MaritalStatusId,
	  EX.Id As ExemptionTypeId,
	  SAD.PreviousStandard,
	  SO.ReferenceRemark,
	  SAD.LCNumber,
	  CM.Title AS AdmissionStandard,
	  SH.IsAllergy,
      SH.AllergyRemark
	  	  	   
  FROM sStudent.vStudentAcademic SA
    INNER JOIN sStudent.tStudentMaster SM ON SM.Id = SA.StudentId
	LEFT JOIN sStudent.tStudentAdmission SAD ON SAD.StudentId=SA.StudentId
	LEFT JOIN sSetup.tClassMaster CM ON CM.Id=SAD.AdmissionStandardId
	LEFT JOIN sCommon.tAdmissionQuotaMaster AQM ON AQM.Id = SAD.AdmissionQuotaId
	LEFT JOIN sSetup.tBatchMaster BM ON BM.Id=SA.BatchId
	LEFT JOIN sStudent.tStudentOther SO ON SO.StudentId=SA.StudentId
	LEFT JOIN sStudent.tStudentCommunication SC ON SC.StudentId=SA.StudentId
	--LEFT JOIN sTransport.tAssignRoute AR ON AR.SourceId=SA.StudentId
	LEFT JOIN sCommon.tTransportModeMaster TM ON TM.Id=SO.TransportModeId
	LEFT JOIN sCommon.tGenderMaster GM ON GM.Id=SO.GenderId
	LEFT JOIN sCommon.tBloodGroupMaster BGM ON BGM.Id=SO.BloodGroupId
	LEFT JOIN sCommon.tCastCategoryMaster CCM ON CCM.Id=SO.CastCategoryId
	LEFT JOIN sCommon.tReligionMaster RM ON RM.Id=SO.ReligionId
	LEFT JOIN sActivity.tHouseMaster HM ON HM.Id=SO.HouseId
	LEFT JOIN sCommon.tMotherTongueMaster MTM ON MTM.Id=SO.MotherTongueId
	LEFT JOIN sCommon.tCountryMaster NM ON NM.Id=SO.NationalityId
	--LEFT JOIN sTransport.tBusRouteDestinationMaster BDM ON BDM.Id=AR.PickupDestinationId
	LEFT JOIN sStudent.tStudentFamily SF ON SF.StudentId=SA.StudentId
	LEFT JOIN sStudent.tStudentHealth SH ON SH.StudentId=SA.StudentId
	LEFT JOIN sStudent.tAdmissionCategoryMaster AC ON AC.Id=SA.AdmissionCategoryId
	LEFT JOIN sStudent.tAcademicDetail AD ON AD.AcademicId = SA.AcademicId
	LEFT JOIN sStudent.tStudentadditional ADI ON ADI.StudentId = SA.StudentId
	LEFT JOIN sStudent.tEnrollmentStatusMaster ESM ON ESM.Id = SM.EnrollmentStatusId
	--LEFT JOIN sStudent.tStudentSibling SS ON SS.StudentId = SA.StudentId
	LEFT JOIN sHostel.tAssignRoom HAR ON HAR.SourceId = SM.Id AND HAR.OrgGroupBatchId = SA.OrgGroupBatchId
	LEFT JOIN sHostel.tRoomMaster HRM ON HRM.Id = HAR.RoomId
	LEFT JOIN sHostel.tBuildingMaster HBM ON HRM.BuildingId = HBM.Id 
	LEFT JOIN sHostel.tHostelMaster HHM ON HHM.Id = HRM.HostelId
	
	LEFT JOIN sStudent.tStudentGroupMaster SGM ON SGM.Id=SM.StudentGroupId
	LEFT JOIN sStudent.tStudentOtherGroupMaster SOGM ON SOGM.Id=SM.StudentOtherGroupId
	--LEFT JOIN sTransport.tBusRouteMaster BRM ON BRM.Id=BDM.BusRouteId
	LEFT JOIN sStudent.tCollegeDetail CD ON CD.StudentId = SA.StudentId
	LEFT JOIN sFee.tExemptionTypeMaster EX ON Ex.Id=SA.ExemptionTypeId
   LEFT JOIN
	(SELECT AR.SourceId,AR.PickupDestinationId AS BusRouteDestinationId,BDM.Title AS BusRouteDestination, ISNULL(BRM.Id,0) AS BusRouteId,
	  ISNULL(BDM.PickupTime,'') AS PickupTime,
	  ISNULL(BDM.Droptime,'') AS Droptime,
	  ISNULL(BRM.Title,'' )AS BusRoute,BRM.OrgGroupBatchId FROM sTransport.tAssignRoute AR 
    INNER JOIN sTransport.tBusRouteDestinationMaster BDM ON BDM.Id=AR.PickupDestinationId
    INNER JOIN sTransport.tBusRouteMaster BRM ON BRM.Id=BDM.BusRouteId AND  AR.SourceTypeId=2003  )  RT 
   
    ON RT.SourceId = SA.StudentId     AND RT.OrgGroupBatchId =SA.OrgGroupBatchId

GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE OR ALTER VIEW [sStudent].[vStudentCustomSearch]  
  
AS  
  
SELECT 	
	  SA.AcademicId,	  
	  SA.ClassId,
	  SA.DivisionId,
	  SA.RollNo,
	  SA.OrgId,
	  OM.Title AS Organization,
	  SA.BatchId,
	  BM.Title AS Batch,
	  BM.OrgGroupBatchId,
	  SA.DepartmentId,
	  DM.Title AS Department,
	  SA.StudentId,
	  SA.SUId,
	  SA.Code,
	  SA.Number,
	  SA.FirstName,
	  SA.MiddleName,
	  SA.LastName,
	  SA.Name,
	  --SA.DepartmentId,
	  SA.StatusId,
	  SA.ClassName,
	  SA.DivisionName,
	  SA.StatusName,
	  SO.UIDNumber,
	  SO.AdhaarNumber,
      CONVERT(VARCHAR(10), SO.DateOfBirth, 111) AS DateOfBirth,
      SO.BirthdateInWord,
      SO.TransportModeId,
      TM.Title AS TransportMode,
      SO.GenderId,
      GM.Title AS Gender,
      SO.BloodGroupId,
      BGM.Title AS BloodGroup,
      SO.BirthPlace,
      SO.BirthTaluka,
      SO.BirthDistrict,
      SO.FamilyCast AS FamilyCaste,
      SO.FamilySubCast,
      
      
      SO.CastCategoryId,
      CCM.Title AS CasteCategory,
      SO.ReligionId,
      RM.Title AS Religion,
      SO.HouseId,
      HM.Title AS House,
      SO.MotherTongueId,
     SO.MotherTongue AS MotherTongue,
      SO.NationalityId,
      NM.Nationality AS Nationality,
      SO.Hobby,
      SH.IdentificationMark1,
      SH.IdentificationMark2,
      SH.IsPysicalDeformity,
      SH.PysicalDeformityRemark,
      SH.IsMajorIllness,
      SH.MajorIllnessRemark,
      SH.FamilyDoctorName,
      SH.FamilyDoctorContactNo,
      
	  SC.PresentStreetOne,
      SC.PresentStreetTwo,
      SC.PresentStreetThree,
      SC.PresentCountry,
      SC.PresentState,
      SC.PresentCity,
      SC.PresentZipCode,
      SC.PresentContactNo,
      SC.PermanentStreetOne,
      SC.PermanentStreetTwo,
      SC.PermanentStreetThree,
      SC.PermanentCountry,
      SC.PermanentState,
      SC.PermanentCity,
      SC.PermanentZipCode,
      SC.PermanentContactNo,
      SC.EmergencyStreetOne,
      SC.EmergencyStreetTwo,
      SC.EmergencyStreetThree,
      SC.EmergencyCountry,
      SC.EmergencyState,
      SC.EmergencyCity,
      SC.EmergencyZipCode,
      SC.EmergencyContactNo,
      SC.ParentContactNo,
      SC.ParentEmail,
      SC.StudentContactNo,
      SC.StudentEmail,
	  ISNULL(SC.WhatsAppContactNo,'') AS WhatsAppContactNo,
	  RT.BusRouteDestinationId AS BusRouteDestinationId,
	  RT.PickupPoint AS BusRouteDestination, -- Changed Value as PickupPoint for mantis : 34483
	  SF.FatherName,
	  CONVERT(VARCHAR(10), SF.FatherDateOfBirth, 111)AS FatherDateOfBirth,
	  SF.FatherQualification,
	  SF.FatherOccupation,
	  SF.FatherContactNo,
	  SF.FatherMail,
	  SF.FatherOfficeAddress,
	  SF.FatherIncome AS FatherAnnualIncome,	-- MantisId : 0029214
	  SF.FatherOfficeContactNo,
	  SF.MotherName,
	 CONVERT(VARCHAR(10),  SF.MotherDateOfBirth,111) AS MotherDateOfBirth,
	  SF.MotherQualification,
	  SF.MotherOccupation,
      SF.MotherContactNo,
      SF.MotherMail,
	  SF.MotherOfficeContactNo,
      SF.MotherOfficeAddress,
      SF.MotherIncome AS MotherAnnualIncome,    -- MantisId : 0029214  
	  ISNULL(SA.AdmissionCategoryId,0) AS AdmissionCategoryId,
	  AC.Title AS AdmissionCategory,
	  SAD.PreviousSchool,
	  CONVERT(VARCHAR(10), SAD.AdmissionDate, 111) AS AdmissionDate,
	  SAD.AdmissionFormNumber,
	  SAD.AdmissionNumber,
	  SAD.IsTransfer,
	  SAD.TransferFrom,
	  SAD.PassingYear,
	  SAD.IsTFWS,
	  SAD.IsCertificateHolder,
	  SAD.CertificateName,
	  AQM.Title AS AdmissionQuota,
	  CONVERT(VARCHAR(10),SAD.LeftDate, 111) AS LeftDate,
	  SA.IsBlackList,
	  SA.BlackListReason,
	  SA.DivisionOrder,
	  SA.ClassOrderIndex,
	  AD.RepeatCount,
	  ADI.BankAccountNumber,
	  ADi.BankBranch,
	  ADI.BankIFSCCode,
	  ADI.BankName,
	  ADI.BankAccountHolderName,
	  ESM.Title AS StudentBatchStatus,
	  EnrollmentStatusId AS StudentStatusId,
	  ADI.Class9BoardRegistrationNumber,
	  ADI.Class11BoardRegistrationNumber,
	  ADI.PassportNumber,
	  ADI.PassportType,
	  ADI.PassportCountryCode,
	CONVERT(VARCHAR(10),ADI.PassportIssueDate,111) AS PassportIssueDate,
	  ADI.PassportExpiryDate,
	  ISNULL(SS.SiblingName1,'') As SiblingName1,
	  ISNULL(SS.SiblingInstitue1,'') As SiblingInstitue1,
	  ISNULL(SS.SiblingClass1,'') As SiblingClass1,
	  ISNULL(SS.SiblingAge1,'') As SiblingAge1,
	  ISNULL(SS.SiblingRelation1,'') As SiblingRelation1,
	  ISNULL(SS.SiblingName2,'') As SiblingName2,
	  ISNULL(SS.SiblingInstitue2,'') As SiblingInstitue2,
	  ISNULL(SS.SiblingClass2,'') As SiblingClass2,
	  ISNULL(SS.SiblingAge2,'') As SiblingAge2,
	  ISNULL(SS.SiblingRelation2,'') As SiblingRelation2,
	  ISNULL(SS.SiblingName3,'') As SiblingName3,
	  ISNULL(SS.SiblingInstitue3,'') As SiblingInstitue3,
	  ISNULL(SS.SiblingClass3,'') As SiblingClass3,
	  ISNULL(SS.SiblingAge3,'') As SiblingAge3,
	  ISNULL(SS.SiblingRelation3,'') As SiblingRelation3,
	  ISNULL(HHM.Title,'') AS HostelName,
	  ISNULL(HRM.Title,'') AS Room,
	  ISNULL(HBM.Title,'') AS BuildingName,
	  ISNULL(HAR.BedNumber,'') AS BedNumber,
	  
	  ISNULL(SM.StudentGroupId,0) AS StudentGroupId,
	  ISNULL(SM.StudentOtherGroupId,0) AS StudentOtherGroupId,
	  
	  SGM.Title AS StudentGroup,
	  SOGM.Title AS StudentOtherGroup,
	  SO.BirthState,
	  So.BirthCountry,
	  ISNULL(RT.BusRouteId,0) AS BusRouteId,
	  ISNULL(RT.PickupTime,'') AS PickupTime,
	  ISNULL(RT.Droptime,'') AS Droptime,
	  ISNULL(RT.BusRoute,'' )AS BusRoute,
	   CD.SSCBoard,
	   CD.SSCSchool,
	  CD.SSCPassingYear,
	  CD.SSCObtainMarks,
	  CD.SSCOutOfMarks,
	  CD.SSCPercentage,
	  CD.SSCMathMarks,
	  CD.SSCMathOutOfMarks,
	  CD.HSCBoard,
	  CD.HSCSchool,
	  CD.HSCPassingYear,
	  CD.HSCObtainMarks,
	  CD.HSCOutOfMarks,
	  CD.HSCPercentage,
	  
	  CD.HSCBiologyMarks,
	  CD.HSCBiologyOutOfMarks,
	 
	  CD.HSCChemistryMarks,
	  CD.HSCChemistryOutOfMarks,
	  CD.HSCMathMarks,
	  CD.HSCMathOutOfMarks,
	  cd.HSCPhysicsMarks,
	  CD.HSCPhysicsOutOfMarks,
	  CD.PCMMarks,
	  CD.PCBMarks,
	  CD.JEERollNo,
	  CD.JEEMeritNo,
	  CD.JEEMeritMarks,
	  CD.JEEOutOfMarks,
	  CD.DiplomaBoard,
	  CD.DiplomaCollege,
	  CD.DiplomaPassingYear,
	  CD.DiplomaObtainMarks,
	  CD.DiplomaOutOfMarks,
	  CD.DiplomaPercentage,
	  CD.ApplicationNo,
	  CD.AdmissionRound,
	  CD.MaritalStatusId,
	  EX.Id As ExemptionTypeId,
	  SAD.PreviousStandard,
	  SO.ReferenceRemark,
	  SAD.LCNumber,
	  CM.Title AS AdmissionStandard,
	  SH.IsAllergy,
      SH.AllergyRemark,
      SF.RelativeName,
      SF.RelativeDateOfBirth,
      SF.RelativeOccupation,
      SF.RelativeQualification,
      SF.RelativeContactNo,
      SF.RelativeMail,
      SF.RelativeOfficeAddress,      
      SF.RelativeIncome AS RelativeAnnualIncome,		-- MantisId : 0029214
      STUFF(
           COALESCE(',' + NULLIF(SC.PresentStreetOne, ''), '')  + 
           COALESCE(',' + NULLIF(SC.PresentStreetTwo, ''), '') +
           COALESCE(',' + NULLIF(SC.PresentStreetThree, ''), '') +
		   COALESCE(',' + NULLIF(SC.PresentCity, ''), '') + -- changed sorting order for mantis : 40447
           COALESCE(',' + NULLIF(SC.PresentState, ''), '') +
           COALESCE(',' + NULLIF(SC.PresentCountry, ''), '') +
            COALESCE(',' + NULLIF(SC.PresentZipCode, ''), '') +
            COALESCE(',' + NULLIF(SC.PresentContactNo, ''), ''),
           1, 1, '') AS PresentAddress,
      STUFF(
           COALESCE(',' + NULLIF(SC.PermanentStreetOne, ''), '')  + 
           COALESCE(',' + NULLIF(SC.PermanentStreetTwo, ''), '') +
           COALESCE(',' + NULLIF(SC.PermanentStreetThree, ''), '') +           
		   COALESCE(',' + NULLIF(SC.PermanentCity, ''), '') + -- changed sorting order for mantis : 40447
           COALESCE(',' + NULLIF(SC.PermanentState, ''), '') +
           COALESCE(',' + NULLIF(SC.PermanentCountry, ''), '') +
            COALESCE(',' + NULLIF(SC.PermanentZipCode, ''), '') +
            COALESCE(',' + NULLIF(SC.PermanentContactNo, ''), ''),
           1, 1, '') AS PermanentAddress ,
      STUFF(
           COALESCE(',' + NULLIF(SC.EmergencyStreetOne, ''), '')  + 
           COALESCE(',' + NULLIF(SC.EmergencyStreetTwo, ''), '') +
           COALESCE(',' + NULLIF(SC.EmergencyStreetThree, ''), '') +
		   COALESCE(',' + NULLIF(SC.EmergencyCity, ''), '') + -- changed sorting order for mantis : 40447
           COALESCE(',' + NULLIF(SC.EmergencyState, ''), '') +
           COALESCE(',' + NULLIF(SC.EmergencyCountry, ''), '') +
            COALESCE(',' + NULLIF(SC.EmergencyZipCode, ''), '') +
            COALESCE(',' + NULLIF(SC.EmergencyContactNo, ''), ''),
           1, 1, '') AS EmergencyAddress ,
	  (SELECT STUFF( (SELECT DISTINCT ',' + CTM.Title FROM sStudent.tStudentCustomTag SCT INNER JOIN sStudent.tCustomTagMaster CTM ON SCT.CustomTagId=CTM.Id 
							WHERE SCT.StudentId = SA.StudentId FOR XML PATH ('')), 1, 1, '')  AS CustomTag FROM sStudent.tStudentMaster SM1 WHERE SM1.Id = SM.Id GROUP BY SM1.Id) AS CustomTag,
		STM.Title AS RecordTag,
		SC.IsStopApplicationAccess,
		CASE WHEN ISNULL(SO.DateOfBirth, '') = '' THEN '' ELSE (SELECT [sCommon].[fGetDateOfBirthInWord](SO.DateOfBirth)) END AS Age,
		RT.PickupPoint as PickupPoint,
		ISNULL(SA.LocalName, '') AS LocalName, -- Added for mantis : 34211
		ISNULL(RT.DropRoute, '') AS DropRoute, -- Added for mantis : 34483
		ISNULL(SA.PENNumber,0) AS PENNumber,    --Mantis - 35453
		 -- added by dhananjay for Mantis - 39234
		ISNULL(SF.FatherAdhaarNumber,'') AS FatherAdhaarNumber,
		ISNULL(SF.MotherAdhaarNumber,'') AS MotherAdhaarNumber,
		-- added by dhananjay for Mantis - 39234
		ISNULL(SA.ApaarIdNumber, '') AS ApaarIdNumber, -- add for mantis : 0040679
		ISNULL(SAD.PreviousLCResult,'') AS PreviousResult			-- Mantis Id : 0043737

  FROM sStudent.vStudentAcademic SA
    INNER JOIN sStudent.tStudentMaster SM ON SM.Id = SA.StudentId
	LEFT JOIN sStudent.tStudentAdmission SAD ON SAD.StudentId=SA.StudentId
	LEFT JOIN sSetup.tClassMaster CM ON CM.Id=SAD.AdmissionStandardId
	LEFT JOIN sCommon.tAdmissionQuotaMaster AQM ON AQM.Id = SAD.AdmissionQuotaId
	LEFT JOIN sSetup.tBatchMaster BM ON BM.Id=SA.BatchId
	LEFT JOIN sStudent.tStudentOther SO ON SO.StudentId=SA.StudentId
	LEFT JOIN sStudent.tStudentCommunication SC ON SC.StudentId=SA.StudentId
	--LEFT JOIN sTransport.tAssignRoute AR ON AR.SourceId=SA.StudentId
	LEFT JOIN sCommon.tTransportModeMaster TM ON TM.Id=SO.TransportModeId
	LEFT JOIN sCommon.tGenderMaster GM ON GM.Id=SO.GenderId
	LEFT JOIN sCommon.tBloodGroupMaster BGM ON BGM.Id=SO.BloodGroupId
	LEFT JOIN sCommon.tCastCategoryMaster CCM ON CCM.Id=SO.CastCategoryId
	LEFT JOIN sCommon.tReligionMaster RM ON RM.Id=SO.ReligionId
	LEFT JOIN sActivity.tHouseMaster HM ON HM.Id=SO.HouseId
	LEFT JOIN sCommon.tMotherTongueMaster MTM ON MTM.Id=SO.MotherTongueId
	LEFT JOIN sCommon.tCountryMaster NM ON NM.Id=SO.NationalityId
	--LEFT JOIN sTransport.tBusRouteDestinationMaster BDM ON BDM.Id=AR.PickupDestinationId
	LEFT JOIN sStudent.tStudentFamily SF ON SF.StudentId=SA.StudentId
	LEFT JOIN sStudent.tStudentHealth SH ON SH.StudentId=SA.StudentId
	LEFT JOIN sStudent.tAdmissionCategoryMaster AC ON AC.Id=SA.AdmissionCategoryId
	LEFT JOIN sStudent.tAcademicDetail AD ON AD.AcademicId = SA.AcademicId
	LEFT JOIN sStudent.tStudentadditional ADI ON ADI.StudentId = SA.StudentId
	LEFT JOIN sStudent.tEnrollmentStatusMaster ESM ON ESM.Id = SM.EnrollmentStatusId
	LEFT JOIN sStudent.tStudentSibling SS ON SS.StudentId = SA.StudentId
	LEFT JOIN sHostel.tAssignRoom HAR ON HAR.SourceId = SM.Id AND HAR.OrgGroupBatchId = SA.OrgGroupBatchId
	LEFT JOIN sHostel.tRoomMaster HRM ON HRM.Id = HAR.RoomId
	LEFT JOIN sHostel.tBuildingMaster HBM ON HRM.BuildingId = HBM.Id 
	LEFT JOIN sHostel.tHostelMaster HHM ON HHM.Id = HRM.HostelId
	
	LEFT JOIN sStudent.tStudentGroupMaster SGM ON SGM.Id=SM.StudentGroupId
	LEFT JOIN sStudent.tStudentOtherGroupMaster SOGM ON SOGM.Id=SM.StudentOtherGroupId
	--LEFT JOIN sTransport.tBusRouteMaster BRM ON BRM.Id=BDM.BusRouteId
	LEFT JOIN sStudent.tCollegeDetail CD ON CD.StudentId = SA.StudentId
	LEFT JOIN sFee.tExemptionTypeMaster EX ON Ex.Id=SA.ExemptionTypeId
   LEFT JOIN
	(SELECT AR.SourceId,AR.DropDestinationId AS BusRouteDestinationId,BDM2.Title AS BusRouteDestination,AR.PickupDestinationId AS BusRoutePickupDestinationId,BDM.Title AS PickupPoint, ISNULL(BRM.Id,0) AS BusRouteId,
	  ISNULL(BDM.PickupTime,'') AS PickupTime,
	  ISNULL(BDM.Droptime,'') AS Droptime,
	  ISNULL(BRM.Title,'' )AS BusRoute,BRM.OrgGroupBatchId,
	  ISNULL(BRM2.Title,'') AS DropRoute FROM sTransport.tAssignRoute AR  -- Added for mantis : 34483
    INNER JOIN sTransport.tBusRouteDestinationMaster BDM ON BDM.Id=AR.PickupDestinationId
	LEFT JOIN sTransport.tBusRouteDestinationMaster BDM2 ON BDM2.Id=AR.DropDestinationId
    INNER JOIN sTransport.tBusRouteMaster BRM ON BRM.Id=BDM.BusRouteId AND  AR.SourceTypeId=2003
	LEFT JOIN sTransport.tBusRouteMaster BRM2 On BRM2.Id=BDM2.BusRouteId AND AR.SourceTypeId=2003 )  RT -- Added for mantis : 34483
    ON RT.SourceId = SA.StudentId     AND RT.OrgGroupBatchId =SA.OrgGroupBatchId
	INNER JOIN sCommon.tStatusMaster STM ON STM.Id = SA.RecordStatusId AND STM.StatusTypeId=102
	INNER JOIN sSetup.tOrganizationMaster OM ON OM.Id = SA.OrgId
	INNER JOIN sSetup.tDepartmentMaster DM ON DM.Id = SA.DepartmentId

GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE OR ALTER VIEW [sStudent].[vStudentColumnName]  
AS 
SELECT 	
	  --SA.AcademicId,	  
	  --SA.ClassId,
	  --SA.DivisionId,
	  SA.RollNo,
	  --SA.OrgId,
	  --SA.BatchId,
	  OM.Title AS Organization,
	  BM.Title AS Batch,
	  DM.Title AS Department,
	  --SA.StudentId,
	  SA.SUId,
	  SA.Code AS [ID No],
	  SA.Code AS [GR No],
	  SA.Number,
	  SA.FirstName,
	  SA.MiddleName,
	  SA.LastName,
	  SA.Name,
	  --SA.DepartmentId,
	  --SA.StatusId,
	  SA.ClassName,
	  SA.DivisionName,
	  SA.StatusName,
	  SO.UIDNumber,
	  SO.AdhaarNumber,
      SO.DateOfBirth,
      SO.BirthdateInWord,
      --SO.TransportModeId,
      TM.Title AS TransportMode,
      --SO.GenderId,
      GM.Title AS Gender,
      --SO.BloodGroupId,
      BGM.Title AS BloodGroup,
      SO.BirthPlace,
      SO.BirthTaluka,
      SO.BirthDistrict,
      SO.FamilyCast As FamilyCaste,
      --SO.FamilySubCast,
      --SO.CastCategoryId,
      
      CCM.Title AS CasteCategory,
      --SO.ReligionId,
      RM.Title AS Religion,
      --SO.HouseId,
      HM.Title AS House,
      --SO.MotherTongueId,
      MTM.Title AS MotherTongue,
      --SO.NationalityId,
      NM.Title AS Nationality,
      SO.Hobby,
      SH.IdentificationMark1,
      SH.IdentificationMark2,
      SH.IsPysicalDeformity AS IsPhysicalDeformity,
      SH.PysicalDeformityRemark AS PhysicalDeformityRemark,
      SH.IsMajorIllness,
      SH.MajorIllnessRemark,
      SH.FamilyDoctorName,
      SH.FamilyDoctorContactNo,
    STUFF(
        COALESCE(',' + NULLIF(SC.PresentStreetOne, ''), '')  + 
        COALESCE(',' + NULLIF(SC.PresentStreetTwo, ''), '') +
        COALESCE(',' + NULLIF(SC.PresentStreetThree, ''), '') +
        COALESCE(',' + NULLIF(SC.PresentCountry, ''), '') +
        COALESCE(',' + NULLIF(SC.PresentState, ''), '') +
        COALESCE(',' + NULLIF(SC.PresentCity, ''), '') +
        COALESCE(',' + NULLIF(SC.PresentZipCode, ''), '') +
        COALESCE(',' + NULLIF(SC.PresentContactNo, ''), ''),
        1, 1, '') AS PresentAddress,
	  SC.PresentStreetOne,
      SC.PresentStreetTwo,
      SC.PresentStreetThree,
      SC.PresentCountry,
      SC.PresentState,
      SC.PresentCity,
      SC.PresentZipCode,
      SC.PresentContactNo,
	   STUFF(
           COALESCE(',' + NULLIF(SC.PermanentStreetOne, ''), '')  + 
           COALESCE(',' + NULLIF(SC.PermanentStreetTwo, ''), '') +
           COALESCE(',' + NULLIF(SC.PermanentStreetThree, ''), '') +
           COALESCE(',' + NULLIF(SC.PermanentCountry, ''), '') +
           COALESCE(',' + NULLIF(SC.PermanentState, ''), '') +
           COALESCE(',' + NULLIF(SC.PermanentCity, ''), '') +
            COALESCE(',' + NULLIF(SC.PermanentZipCode, ''), '') +
            COALESCE(',' + NULLIF(SC.PermanentContactNo, ''), ''),
           1, 1, '') AS PermanentAddress ,
      SC.PermanentStreetOne,
      SC.PermanentStreetTwo,
      SC.PermanentStreetThree,
      SC.PermanentCountry,
      SC.PermanentState,
      SC.PermanentCity,
      SC.PermanentZipCode,
      SC.PermanentContactNo,
	   
      STUFF(
           COALESCE(',' + NULLIF(SC.EmergencyStreetOne, ''), '')  + 
           COALESCE(',' + NULLIF(SC.EmergencyStreetTwo, ''), '') +
           COALESCE(',' + NULLIF(SC.EmergencyStreetThree, ''), '') +
           COALESCE(',' + NULLIF(SC.EmergencyCountry, ''), '') +
           COALESCE(',' + NULLIF(SC.EmergencyState, ''), '') +
           COALESCE(',' + NULLIF(SC.EmergencyCity, ''), '') +
            COALESCE(',' + NULLIF(SC.EmergencyZipCode, ''), '') +
            COALESCE(',' + NULLIF(SC.EmergencyContactNo, ''), ''),
           1, 1, '') AS EmergencyAddress ,
      SC.EmergencyStreetOne,
      SC.EmergencyStreetTwo,
      SC.EmergencyStreetThree,
      SC.EmergencyCountry,
      SC.EmergencyState,
      SC.EmergencyCity,
      SC.EmergencyZipCode,
      SC.EmergencyContactNo,
      SC.ParentContactNo,
      SC.ParentEmail,
      SC.StudentContactNo,
      SC.StudentEmail,
	  --AR.PickupDestinationId AS BusRouteDestinationId,
	  BDM.Title AS BusRouteDestination,
	  SF.FatherName,
	  SF.FatherDateOfBirth,	 
	  SF.FatherQualification,
	  SF.FatherOccupation,
	  SF.FatherContactNo,
	  SF.FatherMail,
	  SF.FatherOfficeContactNo,
	  SF.FatherOfficeAddress,
	  SF.FatherIncome AS FatherAnnualIncome,	-- MantisId : 0029214
	  SF.MotherName,
	  SF.MotherDateOfBirth,
	  SF.MotherQualification,
	  SF.MotherOccupation,
      SF.MotherContactNo,
      SF.MotherMail,
	  SF.MotherOfficeContactNo,
      SF.MotherOfficeAddress,
      SF.MotherIncome AS MotherAnnualIncome,	-- MantisId : 0029214   
	  AC.Title AS AdmissionCategory,
	  SAD.PreviousSchool,
	  SAD.AdmissionDate,
	  SAD.AdmissionFormNumber,
	  SAD.AdmissionNumber,
	  SAD.IsTransfer,
	  SAD.TransferFrom,
	  SAD.PassingYear,
	  SAD.IsTFWS,
	  SAD.IsCertificateHolder,
	  SAD.CertificateName,
	  AQM.Title AS AdmissionQuota,
      SAD.InactiveDate,
	  SAD.InActiveReason,
	  SAD.LCBook,
	  SAD.LeftStandardId AS LeftStandard,
	  SAD.LeftDate,
	  SAD.LCDate,
	  SAD.LCCopy,
	  SAD.LeftReason,
	  SAD.LCProgress,
	  SAD.LCConduct,
	  SAD.LCRemark,
	  SA.BlackListReason,
	  SA.DivisionOrder,
	  SA.ClassOrderIndex,
	  AD.BankAccountNumber,
	  AD.BankName,
	  AD.BankBranch,
	  AD.BankIFSCCode,
	  AD.BankAccountHolderName,
	  BSM.Title AS StudentBatchStatus,
	  AD.Class9BoardRegistrationNumber,
	  AD.Class11BoardRegistrationNumber,
	  AD.PassportNumber,
	  AD.PassportType,
	  AD.PassportCountryCode,
	  AD.PassportIssueDate,
	  AD.PassportExpiryDate,
	  SS.SiblingName1,
	  SS.SiblingInstitue1,
	  SS.SiblingClass1,
	  SS.SiblingAge1,
	  SS.SiblingRelation1,
	  SS.SiblingName2,
	  SS.SiblingInstitue2,
	  SS.SiblingClass2,
	  SS.SiblingAge2,
	  SS.SiblingRelation2,
	  SS.SiblingName3,
	  SS.SiblingInstitue3,
	  SS.SiblingClass3,
	  SS.SiblingAge3,
	  SS.SiblingRelation3,
	  CD.SSCBoard,
	  CD.SSCSchool,
	  CD.SSCPassingYear,
	  CD.SSCObtainMarks,
	  CD.SSCOutOfMarks,
	  CD.SSCPercentage,
	  CD.SSCMathMarks,
	  CD.SSCMathOutOfMarks,
	  CD.HSCBoard,
	  CD.HSCSchool,
	  CD.HSCPassingYear,
	  CD.HSCObtainMarks,
	  CD.HSCOutOfMarks,
	  CD.HSCPercentage,
	  
	  CD.HSCBiologyMarks,
	  CD.HSCBiologyOutOfMarks,
	 
	  CD.HSCChemistryMarks,
	  CD.HSCChemistryOutOfMarks,
	  CD.HSCMathMarks,
	  CD.HSCMathOutOfMarks,
	  cd.HSCPhysicsMarks,
	  CD.HSCPhysicsOutOfMarks,
	  CD.PCMMarks,
	  CD.PCBMarks,
	  CD.JEERollNo,
	  CD.JEEMeritNo,
	  CD.JEEMeritMarks,
	  CD.JEEOutOfMarks,
	  CD.DiplomaBoard,
	  CD.DiplomaCollege,
	  CD.DiplomaPassingYear,
	  CD.DiplomaObtainMarks,
	  CD.DiplomaOutOfMarks,
	  CD.DiplomaPercentage,
	  CD.ApplicationNo,
	  CD.AdmissionRound,
	  CD.MaritalStatusId,
	  ISNULL(HHM.Title,'') AS HostelName,
	  ISNULL(HRM.Title,'') AS Room,
	  ISNULL(HBM.Title,'') AS BuildingName,
	  ISNULL(HAR.BedNumber,'') AS BedNumber,
	  ISNULL(SGM.Title,'') AS StudentGroup,
	  ISNULL(SGMO.Title,'') AS StudentOtherGroup,
	  SO.BirthState,
	  SO.BirthCountry,
	  ISNULL(BDM.PickupTime,'') AS PickupTime,
	  ISNULL(BDM.DropTime,'') AS DropTime,
	  ISNULL(BRM.Title,'') AS BusRoute,
	  ISNULL(SAD.PreviousStandard,'') AS PreviousStandard,
	  ISNULL(So.ReferenceRemark,'') AS ReferenceRemark,
	  ISNULL(SAD.LCNumber,'') AS LCNumber,
	  CM.Title AS AdmissionStandard,
	  SF.IsFatherExStudent,
	  SF.IsMotherExStudent,
	  SF.FatherOrganization,
	  SF.FatherDesignation,
	  SF.MotherOrganization,
	  SF.MotherDesignation,
	  SF.RelativeOrganization,
	  SF.RelativeDesignation,
	  SH.IsAllergy,
      SH.AllergyRemark,
      SO.FamilySubCast,
      SF.RelativeName,
      SF.RelativeDateOfBirth,
      SF.RelativeOccupation,
      SF.RelativeQualification,
      SF.RelativeContactNo,
      SF.RelativeMail,
      SF.RelativeOfficeAddress,      
      SF.RelativeIncome AS RelativeAnnualIncome,		-- MantisId : 0029214
     
    
		STUFF( (SELECT DISTINCT ',' + CTM.Title FROM sStudent.tStudentCustomTag SCT INNER JOIN sStudent.tCustomTagMaster CTM ON SCT.CustomTagId=CTM.Id 
							WHERE SCT.StudentId = SA.StudentId FOR XML PATH ('')), 1, 1, '')  AS CustomTag,
		STM.Title AS RecordTag,
		SC.WhatsAppContactNo,
        SC.IsStopApplicationAccess,
		CASE WHEN ISNULL(SO.DateOfBirth, '') = '' THEN '' ELSE (SELECT [sCommon].[fGetDateOfBirthInWord](SO.DateOfBirth)) END AS Age,
		BDM.Title AS PickupPoint,
		ISNULL(SA.LocalName,'') AS LocalName, -- Added for mantis : 34211
		ISNULL(BRM2.Title, '') AS DropRoute, -- Added for mantis : 34483
		ISNULL(SA.PENNumber,0) AS PENNumber,    --Mantis - 35453
		 -- added by dhananjay for Mantis - 39234
		ISNULL(SF.FatherAdhaarNumber,'') AS FatherAdhaarNumber,
		ISNULL(SF.MotherAdhaarNumber,'') AS MotherAdhaarNumber,
		-- added by dhananjay for Mantis - 39234
		ISNULL(SA.ApaarIdNumber, '') AS ApaarIdNumber, -- add for mantis : 0040679
		ISNULL(SAD.PreviousLCResult,'') AS PreviousResult		-- Mantis Id : 0043737
  FROM sStudent.vStudentAcademic SA
	INNER JOIN sStudent.tStudentAdmission SAD ON SAD.StudentId=SA.StudentId
	LEFT JOIN sSetup.tClassMaster CM ON CM.Id=SAD.AdmissionStandardId
	INNER JOIN sCommon.tAdmissionQuotaMaster AQM ON AQM.Id = SAD.AdmissionQuotaId
	INNER JOIN sSetup.tBatchMaster BM ON BM.Id=SA.BatchId
	INNER JOIN sStudent.tStudentOther SO ON SO.StudentId=SA.StudentId
	INNER JOIN sStudent.tStudentCommunication SC ON SC.StudentId=SA.StudentId
	INNER JOIN sTransport.tAssignRoute AR ON AR.SourceId=SA.StudentId
	INNER JOIN sCommon.tTransportModeMaster TM ON TM.Id=SO.TransportModeId
	INNER JOIN sCommon.tGenderMaster GM ON GM.Id=SO.GenderId
	INNER JOIN sCommon.tBloodGroupMaster BGM ON BGM.Id=SO.BloodGroupId
	INNER JOIN sCommon.tCastCategoryMaster CCM ON CCM.Id=SO.CastCategoryId
	INNER JOIN sCommon.tReligionMaster RM ON RM.Id=SO.ReligionId
	INNER JOIN sActivity.tHouseMaster HM ON HM.Id=SO.HouseId
	INNER JOIN sCommon.tMotherTongueMaster MTM ON MTM.Id=SO.MotherTongueId
	INNER JOIN sCommon.tCountryMaster NM ON NM.Id=SO.NationalityId
	INNER JOIN sTransport.tBusRouteDestinationMaster BDM ON BDM.Id=AR.PickupDestinationId
	INNER JOIN sStudent.tStudentFamily SF ON SF.StudentId=SA.StudentId
	INNER JOIN sStudent.tStudentHealth SH ON SH.StudentId=SA.StudentId
	INNER JOIN sStudent.tAdmissionCategoryMaster AC ON AC.Id=SA.AdmissionCategoryId
	INNER JOIN sStudent.tStudentadditional AD ON AD.StudentId= SA.StudentId
	INNER JOIN sStudent.tStudentBatchStatus SBS ON SBS.AcademicId = SA.AcademicId
	INNER JOIN sStudent.tBatchStatusMaster BSM ON BSM.Id = SBS.BatchStatusId
	INNER JOIN sStudent.tStudentSibling SS ON SS.StudentId= SA.StudentId
	INNER JOIN sStudent.tCollegeDetail CD ON CD.StudentId = SA.StudentId
	INNER JOIN sHostel.tAssignRoom HAR ON HAR.SourceId = SA.StudentId
	INNER JOIN sHostel.tRoomMaster HRM ON HRM.Id = HAR.RoomId
	INNER JOIN sHostel.tBuildingMaster HBM ON HBM.HostelId = HRM.HostelId
	INNER JOIN sHostel.tHostelMaster HHM ON HHM.Id = HRM.HostelId
	INNER JOIN sStudent.tStudentGroupMaster SGM ON SGM.Id = SA.StudentGroupId
	INNER JOIN sStudent.tStudentOtherGroupMaster SGMO ON SGMO.Id = SA.StudentOtherGroupId
    INNER JOIN sTransport.tBusRouteMaster BRM ON BRM.Id=BDM.BusRouteId
	INNER JOIN sCommon.tStatusMaster STM ON STM.Id = SA.RecordStatusId AND STM.StatusTypeId=102
	INNER JOIN sSetup.tOrganizationMaster OM ON OM.Id = SA.OrgId
	INNER JOIN sSetup.tDepartmentMaster DM ON DM.Id = SA.DepartmentId
	LEFT JOIN sTransport.tBusRouteDestinationMaster BDM2 ON BDM2.Id=AR.DropDestinationId -- Added for mantis : 34483
	LEFT JOIN sTransport.tBusRouteMaster BRM2 ON BRM2.Id=BDM2.BusRouteId -- Added for mantis : 34483

GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO













/******************************************************************************  
 Purpose          : To get Doctor details  
 Created date     : 18 Jan 2012  
 Author           : Rajendra Patel 
 Copyright©       : Copyright © Sapphire Software Solutions
 Last Change Desc : Moved EmployeeMaster from sCommon to sHrm.
*******************************************************************************/  
CREATE OR ALTER VIEW [sStudent].[vStudentName]  
  
AS  
  
SELECT 
	  A.Id AS AcademicId,	  
	  A.ClassId,
	  A.DivisionId,
	  A.RollNo,
	  A.OrgId,
	  A.BatchId,
	  SM.Id AS StudentId,
	  SM.Code,
	  SM.Number,
	  SM.FirstName,
	  SM.MiddleName,
	  SM.LastName,
	   [sStudent].[fStudentNameGet](SM.FirstName , SM.MiddleName, SM.LastName,OM.StudentNameFormatId ) AS Name,
	  --SM.DepartmentId,
	  SM.StatusId,
	  OM.OrgGroupId 
 
  FROM 
  
	sStudent.tAcademic A
	INNER JOIN sStudent.tStudentMaster SM
		ON A.StudentId=SM.Id
		
	INNER JOIN sSetup.tOrganizationMaster OM
	    ON SM.OrgId = OM.Id

GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO






/******************************************************************************
	Purpose          : External view to get data from sEmployee and User tables
	Created date     : 02 Sept 2009
	Author           : Rajendra Patel
	Copyright©       : Copyright © Sapphire Software
	Last Change Desc : Added Employee Company Mails and Phone nos
*******************************************************************************/

CREATE OR ALTER VIEW [sCommon].[vExtStudentWithAllUser]
AS
	 SELECT 
		SN.StudentId						AS StudentId,
		SN.Code						AS StudentCode,
		SN.Name						AS FullName,
		SN.FirstName				AS FirstName,
		--SN.MiddleName				AS MiddleName,
		SN.LastName					AS LastName,
		--EM.EmployeeTypeId			AS EmployeeTypeId,
		--1		AS EmployeeCategoryId,
		--EM.GenderId					AS GenderId,
		--EM.DateOfBirth				AS DateOfBirth,
		--EM.DateOfJoining			AS DateOfJoining,
		--EM.PermanentAddressId		AS PermanentAddressId,
		--EM.CommunicationAddressId	AS CommunicationAddressId,
		----EM.CommunicationContactId	AS CommunicationContactId,
		--EM.StatusId					AS EmployeeStatusId,
		--SN.IsObsolete				AS StudentIsObsolete,
		ISNULL(SN.ClassId, 0)		AS ClassId,
		ISNULL(SN.DivisionId, 0)		AS DivisionId,
		--ODM.Title					AS OrganizationDepartment,
		--EM.PhoneNoCompany,
		--EM.CompanyEmail,
		--EM.ExtensionNo,	
		U.Id						AS UserId,		
		U.DisplayTitle				AS DisplayTitle,
		U.LoginTitle				AS LoginTitle,
		U.LoginPassword				AS LoginPassword,
		U.LastLockOutDate			AS LastLockOutDate,
		U.NumberOfLogonAttempt		AS NumberOfLogonAttempt,
		U.IsObsolete				AS UserIsObsolete,
		SN.OrgId
		--EM.EmployeeLocationId AS LocationId
		
	FROM
		sStudent.vStudentName SN
		LEFT OUTER JOIN sSecurity.tUser U
			ON U.SourceId = SN.StudentId AND U.SourceTypeId=2003
	WHERE SN.StatusId=1

GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO






/******************************************************************************  
 Purpose          : To get Batch Wise  Class
 Created date     : 21 Nov 2013
 Author           : Kundan Patel 
 Copyright©       : Copyright © Sapphire Software Solutions
 ******************************************************************************/  
CREATE OR ALTER VIEW [sSetup].[vClassSubject]  
  
AS  
  
SELECT 	
	  SM.Id as SubjectId,
	  CS.Id as ClassSubjectId,
	  SM.Code AS SubjectCode,
	  SM.Title as SubjectName,
	  SM.Number,
	  CS.ClassId,
	  CS.IsOptional,
	  CS.IsObsolete,
	  SM.OrderIndex,
	  CS.OrgId,
	  CS.OrderIndex AS ClassSubjectOrderIndex,
	  CS.IsPractical 
  
  FROM 
  
	sSetup.tClassSubject CS
	INNER JOIN sSetup.tSubjectMaster SM
	ON SM.Id=CS.SubjectId


GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO


CREATE OR ALTER VIEW [sOmr].[vOMRSubject]
AS
SELECT     CS.ClassSubjectId, CS.SubjectName, ED.ExamId,CS.ClassId,ESM.Title AS Name
FROM         sOmr.tExamSetup AS ES 
				INNER JOIN    sAssessment.tExamDetail AS ED ON ED.Id = ES.ExamDetailId 
                INNER JOIN    sSetup.vClassSubject AS CS ON CS.ClassSubjectId = ED.ClassSubjectId
                INNER JOIN sAssessment.tExamMaster EM ON EM.Id = ED.ExamId
                INNER JOIN sAssessment.tExamSubGroupMaster ESM ON EM.ExamSubGroupId = ESM.Id

GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO




/******************************************************************************  
 Purpose          : To get Batch Wise  Class
 Created date     : 21 Nov 2013
 Author           : Kundan Patel 
 Copyright©       : Copyright © Sapphire Software Solutions
 ******************************************************************************/  
CREATE OR ALTER VIEW [sAttendance].[vBatchSubject]  
  
AS  
  
Select 
	CS.SubjectId,
	CS.SubjectName,
	DM.Id AS DivisionId,
	CS.OrgId,
	CS.IsObsolete,
	CS.OrderIndex

from 
		sSetup.tDivisionMaster DM 
	INNER JOIN sSetup.vClassSubject CS ON CS.ClassId = DM.ClassId 

GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO


/******************************************************************************
	Purpose          : External view to get data from sHrm.tEmployeeMaster table
	Created date     : 03 Sep 2010
	Author           : Rajendra Patel
	Copyright©       : Copyright © Sapphire Software
	
	Last Change Desc : Added for jobtitle
*******************************************************************************/

CREATE OR ALTER VIEW [sHrm].[vEmployeeCommonDetail]

AS
	 SELECT EM.OrgID,
	        EM.Id AS EmployeeId,
	        EM.Code,
	        ISNULL(EM.FirstName, '') AS FirstName,
			ISNULL(EM.MiddleName, '') AS MiddleName,
	        ISNULL(EM.LastName, '') AS LastName,
	        ISNULL(EM.FirstName, '') + ' ' + ISNULL(EM.LastName, '') AS  EmployeeName,	 
	        EM.StatusId AS EmployeeStatusId,		
	        EJD.DateOfleaving,				
			--EM.FirstName,
			--EM.MiddleName,
			--EM.LastName,			
			--EM.OrgID,
			--EPD.GenderId,
			--EPD.DateOfBirth,
			 EJD.DateOfJoining,
			--EM.StatusId,
			--EM.CreatedUserId,
			--EM.CreatedDateTime,
			--EM.UpdatedUserId,
			--EM.UpdatedDateTime,
			--EM.IsObsolete,
			ISNULL(EC.PhoneNoCompany,'') AS PhoneNoCompany,
			ISNULL(EC.CompanyEmail,'') AS CompanyEmail,
			ISNULL(EC.ExtensionNo,'') AS ExtensionNo,		 
			ISNULL(ODM.Id,0) AS DepartmentId,  
			--ISNULL(ODM.Title,'') AS DepartmentCode,  
			ISNULL(ODM.Title,'') AS DepartmentName,
		    ISNULL(EC.MobileNumber,'') AS MobileNumber,
		    0 AS EmployeeJobTitleId		    
		    ,ISNULL(EJD.EmployeeDesignationId,0) AS EmployeeDesignationId
			,ISNULL(EJD.OrganizationDepartmentId,0) AS OrganizationDepartmentId
			,ISNULL(EJD.EmployeeTypeId,0) AS EmployeeTypeId
			,ISNULL(EPD.BloodGroupId,0) AS BloodGroupId
			,ISNULL(BG.Title,'UNKNOWN') AS BloodGroup
			,ISNULL(EPD.GenderId,0) AS GenderId			 
			,EPD.DateOfBirth	 
		    ,MSM.Id AS MaritalStatusId
		    ,GM.Title As GenderTitle
		    ,MSM.Title As MaritialStatusTitle
			,EJD.EmployeeGroupId
		    ,EM.IsObsolete
		    ,ISNULL(EDM.Title,'') as EmployeeDesignation 
		    ,0 AS GradeNumber
		    ,ISNULL(EJD.EmployeeGradeId,0) AS EmployeeGradeId
		    ,EGM.Title AS EmployeeGrade
		    --,RM.Title As RelationshipTitle
		    --,RM.Id As RelationshipId
			--ISNULL(EDM.Id,0) AS DesignationId
			,ISNULL(EM.Number,0) As Number
			,ISNULL(EM.IsOtherUser,0) As IsOtherUser
			,ISNULL(EC.WhatsAppContactNo,'') AS WhatsAppContactNo
			,ISNULL(EJD.JobRemark,'') AS JobRemark --Add for mantis : 0038429
		  
	    FROM 
			sHrm.tEmployeeMaster EM
	    INNER JOIN sHrm.tEmployeeJobDetail EJD ON EM.Id = EJD.EmployeeId AND EM.IsObsolete = 0 
	    INNER JOIN sSetup.tDepartmentMaster ODM ON EJD.OrganizationDepartmentId = ODM.Id AND ODM.IsObsolete = 0					     
	    INNER JOIN sHrm.tEmployeePersonalDetail EPD ON EM.Id = EPD.EmployeeId	  	     
	    LEFT JOIN sHrm.tEmployeeCommunications EC ON EM.Id = EC.EmployeeId  
		LEFT JOIN sCommon.tBloodGroupMaster BG ON EPD.BloodGroupId = BG.Id AND BG.IsObsolete = 0			  
		LEFT JOIN sHrm.tEmployeeDesignationMaster EDM ON EJD.EmployeeDesignationId  = EDM.Id AND EDM.IsObsolete = 0	
		LEFT JOIN sHrm.tEmployeeGradeMaster EGM ON EGM.Id = EJD.EmployeeGradeId 
		LEFT JOIN sCommon.tGenderMaster  GM ON EPD.GenderId = GM.Id AND GM.IsObsolete = 0	
        LEFT JOIN sCommon.tMaritalStatusMaster MSM ON EPD.MaritalStatusId= MSM.Id AND MSM.IsObsolete = 0

GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/******************************************************************************    
 Purpose          : External view to get data from sHrm.tEmployeeJobDetail table    
 Created date     : 03 Sep 2010    
 Author           : Rajendra Patel   
 Copyright©       : Copyright © Sapphire Software    
 Last Change Desc : EJD Added in Select
 *******************************************************************************/
CREATE OR ALTER VIEW [sHrm].[vExtEmployeeFamilyDetail]
AS
SELECT        EFD.Id AS DependentId, RM.Title + '  -  ' + EFD.Name AS Name, EM.Code AS EmployeeCode, EM.EmployeeId, RM.Title AS Relation, RM.Id AS RelationId, EFD.Name AS DependentName, ISNULL(EFD.DateOfBirth, '') 
                         AS DateOfBirth, ISNULL(DATEDIFF(yy, EFD.DateOfBirth, GETDATE()), 0) AS AGE, EM.EmployeeName, EM.OrgID, EM.DepartmentId, EM.DepartmentName, EM.EmployeeDesignationId AS DesignationId, ISNULL(EM.IsOtherUser, 0) 
                         AS IsOtherUser
FROM            sHrm.tEmployeeFamilyDetails AS EFD INNER JOIN
                         sHrm.vEmployeeCommonDetail AS EM ON EFD.EmployeeId = EM.EmployeeId INNER JOIN
                         sCommon.tRelationshipMaster AS RM ON RM.Id = EFD.RelationshipId INNER JOIN
                         sHrm.tEmployeeJobDetail AS EJD ON EJD.EmployeeId = EFD.EmployeeId

GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE OR ALTER VIEW [sFee].[vFeeColumnName]

AS

SELECT
		--SA.AcademicId,
		--SA.ClassId,
		--SA.DivisionId,
		SA.SUId,
		SA.RollNo,
		--SA.OrgId,
		--SA.BatchId,
		OGBM.Title AS OrgGroupBatch,
		OM.Title AS Organization,
		BM.Title AS Batch,
		--SA.StudentId,
		SA.Code AS [ID No],
		SA.Code AS [GR No],
		SA.Number,
		SA.FirstName,
		SA.LastName,
		SA.Name,
		--SA.DepartmentId,
		--SA.StatusId,
		SA.ClassName,
		SA.DivisionName,
		SA.StatusName,
		--FA.VariableAmount,
		--FA.DiscountAmount,
		--FA.AdditionalAmount,
		--FA.NotApplicable,
		--FCM.Number AS FeeCategoryNumber,
		--FCM.Code AS FeeCategoryCode,
		--FCM.Title AS FeeCategoryName,
		--FCM.OrderIndex AS FeeCategoryOrderIndex,
		--FHM.Code AS FeeHeadCode,
		--FHM.Title AS FeeHeadTitle,
		--FM.Code AS FeeCode,
		--FM.Number AS FeeNumber,
		--FM.Title AS FeeName,
		--FM.OrderIndex AS FeeOrderIndex,
		--FS.Amount AS FeeSetupAmount,
		--FS.StartDate,
		--FS.EndDate,
		R.ReceiptNumber,
		R.ReceiptDate,
		R.ReceiptAmount,
		R.ChequeNo,
		R.ChequeDate,
		R.ChequeClearDate,
		R.BankName,
		R.Remark,
		RBM.Code AS ReceiptBookCode,
		RBM.Title AS ReceiptBookName,
		RBM.Address1,
		RBM.Address2,
		RBM.BankAccountNumber,
		--RD.Amount AS ReceiptDetailAmount,
		PTM.Title AS PayType,
		U.DisplayTitle AS CreatedUserName,
		SC.ParentContactNo,
		R.CreatedDateTime,
		STUFF(
			COALESCE(',' + NULLIF(SC.PresentStreetOne, ''), '')  +
			COALESCE(',' + NULLIF(SC.PresentStreetTwo, ''), '') +
			COALESCE(',' + NULLIF(SC.PresentStreetThree, ''), '') +
			COALESCE(',' + NULLIF(SC.PresentCity, ''), '') +
			COALESCE(',' + NULLIF(SC.PresentState, ''), '') +
			COALESCE(',' + NULLIF(SC.PresentCountry, ''), '') +
			COALESCE(',' + NULLIF(SC.PresentZipCode, ''), '') +
			COALESCE(',' + NULLIF(SC.PresentContactNo, ''), ''),
			1, 1, '') AS LocalAddress,  --changes the Sequence of the country, City, State by dhanajay for Mantis :40680
		ISNULL(ACM.Title,'') AS [Fees Category]  --mantis: 42471, add

FROM
		sStudent.vStudentAcademic SA
		INNER JOIN sSetup.tOrgGroupBatchMaster OGBM ON OGBM.Id=SA.OrgGroupBatchId
		INNER JOIN sSetup.tOrganizationMaster OM ON OM.Id=SA.OrgId
		INNER JOIN sSetup.tBatchMaster BM ON BM.Id=SA.BatchId
		--INNER JOIN sFee.tFeeAllocation FA ON FA.SourceId=SA.AcademicId
		INNER JOIN sFee.tReceipt R ON R.SourceId=SA.AcademicId
		INNER JOIN sFee.tReceiptBookMaster RBM ON RBM.Id=R.ReceiptBookId
		--INNER JOIN sFee.tReceiptDetail RD ON RD.ReceiptId=R.Id
		--INNER JOIN sFee.tFeeSetup FS ON FS.Id=RD.FeeSetupId
		--INNER JOIN sFee.tFeeCategoryMaster FCM ON FCM.Id=FS.FeeCategoryId
		--INNER JOIN sFee.tFeeCategoryClass FCC ON FCC.FeeCategoryId=FCM.Id
		--INNER JOIN sFee.tFeeMaster FM ON FM.Id=FS.FeeId
		--INNER JOIN sFee.tFeeHeadMaster FHM ON FHM.Id=FM.FeeHeadId
		INNER JOIN sFinanceCommon.tPayTypeMaster PTM ON PTM.Id=R.PayTypeId
		INNER JOIN sSecurity.tUser U ON U.Id=R.CreatedUserId
		LEFT JOIN sStudent.tStudentCommunication SC ON SC.StudentId=SA.StudentId
		LEFT JOIN sStudent.tAdmissionCategoryMaster ACM ON ACM.Id=SA.AdmissionCategoryId  --mantis: 42471, add

GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE OR ALTER VIEW [sFee].[vFeeCustomSearch]

AS

SELECT
		SA.AcademicId,
		SA.ClassId,
		SA.DivisionId,
		SA.RollNo,
		SA.OrgGroupBatchId,
		SA.OrgId,
		SA.BatchId,
		OGBM.Title AS OrgGroupBatch,
		OM.Title AS Organization,
		BM.Title AS Batch,
		SA.StudentId,
		SA.Code,
		SA.Number,
		SA.FirstName,
		SA.LastName,
		SA.Name,
		SA.StatusId,
		SA.ClassName,
		SA.DivisionName,
		SA.StatusName,
		--FA.Id AS FeeAllocationId,
		--FA.VariableAmount,
		--FA.DiscountAmount,
		--FA.AdditionalAmount,
		--FA.NotApplicable,
		--FCC.IsCommon,
		--FCM.Id AS FeeCategoryId,
		--FCM.Number AS FeeCategoryNumber,
		--FCM.Code AS FeeCategoryCode,
		--FCM.Title AS FeeCategoryName,
		--FCM.OrderIndex AS FeeCategoryOrderIndex,
		--FHM.Id AS FeeHeadId,
		--FHM.Code AS FeeHeadCode,
		--FHM.Title AS FeeHeadTitle,
		--FM.Id AS FeeId,
		--FM.Code AS FeeCode,
		--FM.Number AS FeeNumber,
		--FM.Title AS FeeName,
		--FM.OrderIndex AS FeeOrderIndex,
		--FS.Id AS FeeSetupId,
		--FS.IsVariable,
		--FS.Amount AS FeeSetupAmount,
		--FS.StartDate,
		--FS.EndDate,
		R.Id AS ReceiptId,
		R.ReceiptNumber,
		R.ReceiptDate,
		R.ReceiptAmount,
		R.ChequeNo,
		R.ChequeDate,
		R.ChequeClearDate,
		R.BankName,
		R.Remark,
		R.IsObsolete,
		RBM.Id AS FeeReceiptBookId,
		RBM.Code AS ReceiptBookCode,
		RBM.Title AS ReceiptBookName,
		RBM.Address1,
		RBM.Address2,
		RBM.BankAccountNumber,
		--RD.Amount AS ReceiptDetailAmount,
		PTM.Id AS PayTypeId,
		PTM.Title AS PayType,
		U.Id AS CreatedUserId,
		U.DisplayTitle AS CreatedUserName,
		SA.SUId,
		SA.AdmissionCategoryId,
		SC.ParentContactNo,
		R.CreatedDateTime,
		STUFF(
			COALESCE(',' + NULLIF(SC.PresentStreetOne, ''), '')  +
			COALESCE(',' + NULLIF(SC.PresentStreetTwo, ''), '') +
			COALESCE(',' + NULLIF(SC.PresentStreetThree, ''), '') +
			COALESCE(',' + NULLIF(SC.PresentCity, ''), '') +
			COALESCE(',' + NULLIF(SC.PresentState, ''), '') +
			COALESCE(',' + NULLIF(SC.PresentCountry, ''), '') +
			COALESCE(',' + NULLIF(SC.PresentZipCode, ''), '') +
			COALESCE(',' + NULLIF(SC.PresentContactNo, ''), ''),
			1, 1, '') AS LocalAddress,--changes the Sequence of the country, City, State by dhanajay for Mantis :40680
		ISNULL(ACM.Title,'') AS [Fees Category]  --mantis: 42471, add

FROM
		sStudent.vStudentAcademic SA
		INNER JOIN sSetup.tOrgGroupBatchMaster OGBM ON OGBM.Id=SA.OrgGroupBatchId
		INNER JOIN sSetup.tOrganizationMaster OM ON OM.Id=SA.OrgId
		INNER JOIN sSetup.tBatchMaster BM ON BM.Id=SA.BatchId
		--LEFT JOIN sFee.tFeeAllocation FA ON FA.SourceId=SA.AcademicId
		INNER JOIN sFee.tReceipt R ON R.SourceId=SA.AcademicId
		INNER JOIN sFee.tReceiptBookMaster RBM ON RBM.Id=R.ReceiptBookId
		--INNER JOIN sFee.tReceiptDetail RD ON RD.ReceiptId=R.Id
		--LEFT JOIN sFee.tFeeSetup FS ON FS.Id=RD.FeeSetupId
		--LEFT JOIN sFee.tFeeCategoryMaster FCM ON FCM.Id=FS.FeeCategoryId
		--LEFT JOIN sFee.tFeeCategoryClass FCC ON FCC.FeeCategoryId=FCM.Id
		--INNER JOIN sFee.tFeeMaster FM ON FM.Id=FS.FeeId
		--INNER JOIN sFee.tFeeHeadMaster FHM ON FHM.Id=FM.FeeHeadId
		INNER JOIN sFinanceCommon.tPayTypeMaster PTM ON PTM.Id=R.PayTypeId
		LEFT JOIN sSecurity.tUser U ON U.Id=R.CreatedUserId
		LEFT JOIN sStudent.tStudentCommunication SC ON SC.StudentId = SA.StudentId
		LEFT JOIN sStudent.tAdmissionCategoryMaster ACM ON ACM.Id=SA.AdmissionCategoryId  --mantis: 42471, add

GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO


/******************************************************************************
	Purpose          : External view to get data from sHrm.tEmployeeMaster table
	Created date     : 03 Sep 2010
	Author           : Rajendra Patel
	Copyright©       : Copyright © Sapphire Software
	
	Last Change Desc : Added for jobtitle
*******************************************************************************/

CREATE OR ALTER VIEW [sHrm].[vExtEmployeeMaster]

AS
	 SELECT EM.Id,
	        EM.Id AS EmployeeId,
	        EM.Code,
	       -- ISNULL(EM.FirstName, '') + ' ' + ISNULL(EM.MiddleName, '') + ' ' + ISNULL(EM.LastName, '') EmployeeName,	 
	        ISNULL(EM.FirstName, '') + ' ' + ISNULL(EM.LastName, '') EmployeeName,
	       	EM.FirstName,
			EM.MiddleName,
			EM.LastName,	
			ISNULL(EM.ShortName,'') AS 	ShortName,	
			EM.OrgID,
			EPD.GenderId,
			EPD.DateOfBirth,
			EJD.DateOfJoining,
			EJD.DateOfleaving,
			EJD.ReasonOfleaving,
			EJD.JobRemark,
			EM.StatusId,
			EM.CreatedUserId,
			EM.CreatedDateTime,
			EM.UpdatedUserId,
			EM.UpdatedDateTime,
			EM.IsObsolete,
			ISNULL(EC.PhoneNoCompany,'') AS PhoneNoCompany,
			ISNULL(EC.CompanyEmail,'') AS CompanyEmail,
			ISNULL(EC.ExtensionNo,'') AS ExtensionNo,
			vEU.Id AS UserId,  
			ISNULL(ODM.Id,0) AS DepartmentId,  
			ISNULL(ODM.Title,'') AS DepartmentCode,  
			ISNULL(ODM.Title,'') AS DepartmentName,
		    ISNULL(EC.MobileNumber,'') AS MobileNumber,
			EJD.EmployeeTypeId,
			ISNULL(ETM.Title,'') AS EmployeeType,
			ISNULL(EDM.Id,0) AS DesignationId,
			ISNULL(EDM.Title,'') AS Designation,
			95 AS ParticipantSourceTypeId,
			DATEDIFF(yy,DateOfBirth,GETDATE()) AS Age,
			ISNULL(EGM.Title,'') AS EmployeeGroup,
			ISNULL(EGM.Id,0) AS EmployeeGroupId,
			0 AS GradeNumber,
		    ISNULL(EJD.EmployeeGradeId,0) AS EmployeeGradeId,
		    EGM.Title AS EmployeeGrade,
		    EI.EmployeePhoto,
		    ISNULL(EPD.Qualification,'') AS Qualification,
		    EPD.MaritalStatusId,
			EM.Number,
			EM.IsOtherUser
	   FROM 
		   sHrm.tEmployeeMaster EM
	   INNER JOIN sHrm.tEmployeeJobDetail EJD ON EM.Id = EJD.EmployeeId 
	   INNER JOIN sHrm.tEmployeePersonalDetail EPD ON EM.Id = EPD.EmployeeId	 		     
	   LEFT JOIN sHrm.tEmployeeDesignationMaster EDM ON EDM.Id = EJD.EmployeeDesignationId	
	   LEFT JOIN sHrm.tEmployeeTypeMaster ETM ON ETM.Id = EJD.EmployeeTypeId	
	   LEFT JOIN sHrm.tEmployeeCommunications EC ON EM.Id = EC.EmployeeId 
	   LEFT JOIN sHrm.tEmployeeGroupMaster EGM ON EGM.Id=EJD.EmployeeGroupId 	  
	   LEFT JOIN sSecurity.tUser vEU ON EM.Id = vEU.SourceId AND vEU.SourceTypeId=95
	   LEFT JOIN sSetup.tDepartmentMaster ODM ON EJD.OrganizationDepartmentId = ODM.Id 
	   LEFT JOIN sHrm.tEmployeeImages EI ON EM.Id=EI.EmployeeId
 



GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE OR ALTER VIEW [sHrm].[vPrintTemplateJoiningCertificateGet]  

 AS
   
 SELECT   
   EM.Id AS EmployeeId,
   CJ.Number,
   EM.EmployeeName,
   EM.FirstName,  
   EM.MiddleName,  
   EM.LastName,      
   EM.Code,    
   EM.ShortName,  
   EM.OrgID,  
   EM.DepartmentId,  
   EM.DepartmentName,  
   EM.DesignationId,  
   EM.Designation,  
   EM.EmployeePhoto, 
   EM.DateOfJoining,    
   OI.CertificateHeaderA4 ,  
   CJ.Remark,  
   ISNULL(CJ.CertificateDate,'') AS CertificateDate,  
   EM.MaritalStatusId,  
   CJ.PayScale,  
   CJ.AgreementPeriod,
   CJ.CertificateRemark,
   OM.Title As OrgTitle,
   CJ.CreatedDateTime As CreatedDateTime,
   U.DisplayTitle As UserDisplayName,
   EDM.Title AS EmployeeDesignation,
   OM.DisplayTitle As ShortTitle,
   DM.Title AS DepartmentName1,
   EM.MobileNumber,
    EC.PresentStreetOne  
	,EC.PresentStreetTwo 
	,EC.PresentStreetThree 
   ,EC.PresentCity
   ,EC.PresentState
   ,EC.PresentZipCode
   ,EC.PresentContactNo 	
   ,ISNULL(EDM1.Title,'Admin') AS UserDesignation,   
	ISNULL(CJ.DateOfJoining,'') As CertificateDateOfJoining,
	EM.GenderId
	,'[Number],[EmployeeName],[FirstName],[LastName],[Code],[ShortName],[DepartmentName],[Designation],[EmployeePhoto],[DateOfJoining],[CertificateHeaderA4]
	,[Remark],[CertificateDate],[PayScale],[AgreementPeriod],[CertificateRemark],[OrgTitle],[CreatedDateTime],[UserDisplayName]
	,[EmployeeDesignation],[ShortTitle],[DepartmentName1],[MobileNumber],[PresentStreetOne],[PresentStreetTwo],[PresentStreetThree]
	,[PresentCity],[PresentState],[PresentZipCode],[PresentContactNo],[UserDesignation],[CertificateDateOfJoining]
	,[MiddleName]' AS ColumnList --MiddleName add for mantis : 0043976

 FROM   
	  sHrm.tCertificateJoining CJ   
      INNER JOIN [sHrm].[vExtEmployeeMaster] EM  ON EM.EmployeeId=CJ.EmployeeId 
      INNER JOIN sSetup.tOrganizationMaster OM ON OM.Id=EM.OrgId   
      INNER JOIN  sSecurity.tUser U ON U.Id=CJ.CreatedUserId 
	  --AND U.SourceTypeId = 95
      INNER JOIN  sHrm.tEmployeeJobDetail EJD ON EJD.EmployeeId=EM.Id
	  INNER JOIN  sSetup.tDepartmentMaster DM ON DM.Id=CJ.DepartmentId
	  LEFT JOIN  sHrm.tEmployeeDesignationMaster EDM ON EDM.Id=EJD.EmployeeDesignationId  --added
	  LEFT JOIN  sHrm.tEmployeeCommunications EC ON EC.EmployeeId=CJ.EmployeeId  --added
	  LEFT JOIN sSetup.tOrgImages OI ON OI.OrgId = OM.Id 
      LEFT JOIN  sHrm.tEmployeeJobDetail EJD1 ON EJD1.EmployeeId=U.SourceId
	  LEFT JOIN  sHrm.tEmployeeDesignationMaster EDM1 ON EDM1.Id=EJD1.EmployeeDesignationId

GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/******************************************************************************  
 Purpose          : To get Batch Wise  Class
 Created date     : 21 Nov 2013
 Author           : Kundan Patel 
 Copyright©       : Copyright © Sapphire Software Solutions
 ******************************************************************************/  
CREATE OR ALTER VIEW [sAssessment].[vGeneralExamCustomSearch]  
  
AS  
  
SELECT 	
		 SA.AcademicId,	  
		 SA.ClassId,
		 SA.DivisionId,
		 SA.RollNo,
		 SA.OrgId,
		 SA.BatchId,
		 OM.Title AS Organization,
		 BM.Title AS Batch,
		 SA.StudentId,
		 SA.Code,
		 SA.Number,
		 SA.FirstName,
		 SA.LastName,
		 SA.Name,
		 SA.StatusId,
		 SA.ClassName,
		 SA.DivisionName,
		 SA.StatusName,
		 R.Id AS ResultId,
		 R.ExamId AS ExamId,
		 R.TotalMarks,
		 R.TotalObtain,
		 R.ResultStatus,
		 R.ResultGrade,
		 R.ResultGradePoint,
		 R.RankNumber,
		 R.Percentage,
		 R.Percentile,
		 R.ResultRemark,
		 R.OtherRemark1,
		 ESGM.Code AS ExamCode,
		 ESGM.Title AS ExamName,
	     U.Id AS CreatedUserId,
	     U.DisplayTitle AS CreatedUserName,
	     EM.ExamSubGroupId,
	     SR.ExamSeatNo,
	     SR.TotalDays,
	     SR.PresentDays,
	     SR.StudentHeight,
	     SR.StudentWeight,
	     SR.ResultRemark1,
	     SR.ResultRemark2,
	     SR.MyGoal,
	     SR.MyInterestHobby,
	     SR.MyStrength,
	     SR.Achievements,
	     SR.AreaOfInterest,
	     SR.Responsibility,
	  	 SR.DentalHygiene,
	  	 SR.VisionLeft,
	  	 SR.VisionRight  ,
	  	  CM.DepartmentId,
	  	  SR. MyActivity,
	  	  SR.ClassTeacherRemark,
	  	  SR.HouseRemark,
	  	  SR.ResultRemark3,
	  	  SR.GeneralRemark,
		  CCM.Id As CastCategoryId,
		  CCM.Code As CastCategory,

		  SF.FatherName,
		  SF.MotherName,
	      ACM.Title AS FeeCategory,
		  HM.Title AS House,
		  CONVERT(VARCHAR(10), SO.DateOfBirth, 103) AS DateOfBirth
		  --SM.Id AS SubjectId,
		  --SM.Title AS SubjectTitle   
FROM 
		sStudent.vStudentAcademic SA
	 INNER JOIN sSetup.tClassMaster  CM ON CM.Id= SA.ClassId
	 --Add By Vishkha
	 --INNER Join sSetup.tClassSubject CS ON CS.ClassId=CM.Id
	 --INNER JOIN sSetup.tSubjectMaster SM on CS.SubjectId=SM.Id AND SM.OrgId = SA.OrgId
	
	INNER JOIN sSetup.tOrganizationMaster OM ON OM.Id=SA.OrgId
	INNER JOIN sSetup.tBatchMaster BM ON BM.Id=SA.BatchId
	INNER JOIN sAssessment.tResult R ON R.AcademicId=SA.AcademicId
	INNER JOIN sAssessment.tExamMaster EM ON EM.Id=R.ExamId
	LEFT JOIN sSecurity.tUser U ON U.Id=R.UpdatedUserId
	INNER JOIN sAssessment.tExamSubGroupMaster ESGM ON ESGM.Id = EM.ExamSubGroupId
	LEFT JOIN sAssessment.tStudentRemark SR ON SR.AcademicId = SA.AcademicId AND SR.ExamId = EM.Id
	LEFT JOIN sStudent.tStudentOther SO ON SO.StudentId=SA.StudentId
	LEFT JOIN sCommon.tCastCategoryMaster CCM ON CCM.Id=SO.CastCategoryId

	LEFT JOIN sStudent.tStudentFamily SF ON SF.StudentId = SA.StudentId
	LEFT JOIN sStudent.tAdmissionCategoryMaster ACM ON SA.AdmissionCategoryId = ACM.Id
	LEFT JOIN sActivity.tHouseMaster HM ON SO.HouseId = HM.Id

GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO








/******************************************************************************
	Purpose          : External view to get data from sEmployee and User tables
	Created date     : 02 Sept 2009
	Author           : Rajendra Patel
	Copyright©       : Copyright © Sapphire Software
	Last Change Desc : Added Employee Company Mails and Phone nos
*******************************************************************************/

CREATE OR ALTER VIEW [sCommon].[vExtEmployeeWithAllUser]
AS
	 SELECT 
			EM.Id						AS EmployeeId,
			EM.Code						AS EmployeeCode,
			ISNULL(EM.FirstName, '') +
			' ' + 
			ISNULL(EM.MiddleName, '') +
			' ' + 
			ISNULL(EM.LastName, '')		AS FullName,
			EM.FirstName				AS FirstName,
			EM.MiddleName				AS MiddleName,
			EM.LastName					AS LastName,
			EM.EmployeeTypeId			AS EmployeeTypeId,
			1		AS EmployeeCategoryId,
			EM.GenderId					AS GenderId,
			EM.DateOfBirth				AS DateOfBirth,
			EM.DateOfJoining			AS DateOfJoining,
			EM.StatusId					AS EmployeeStatusId,
			EM.IsObsolete				AS EmployeeIsObsolete,
			ISNULL(EM.DepartmentId, 0)				AS OrganizationDepartmentId,
			ODM.Title					AS OrganizationDepartment,
			EM.PhoneNoCompany,
			EM.CompanyEmail,
			EM.ExtensionNo,	
			U.Id						AS UserId,		
			U.DisplayTitle				AS DisplayTitle,
			U.LoginTitle				AS LoginTitle,
			U.LoginPassword				AS LoginPassword,
			U.LastLockOutDate			AS LastLockOutDate,
			U.NumberOfLogonAttempt		AS NumberOfLogonAttempt,
			U.IsObsolete				AS UserIsObsolete,
			EM.OrgId
		
	FROM
			sHrm.vExtEmployeeMaster EM
		LEFT OUTER JOIN sSecurity.tUser U ON U.SourceId = EM.Id AND U.SourceTypeId=95
		LEFT OUTER JOIN sSetup.tDepartmentMaster ODM ON EM.DepartmentId = ODM.Id
	WHERE StatusId=7

GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO



 
/******************************************************************************  
 Purpose          : To get Batch Wise  Class
 Created date     : 21 Nov 2013
 Author           : Kundan Patel 
 Copyright©       : Copyright © Sapphire Software Solutions
 ******************************************************************************/  
CREATE OR ALTER VIEW [sAssessment].[vGeneralExamColumnName]  
  
AS  
  
SELECT 	
		 --SA.AcademicId,	  
		 --SA.ClassId,
		 --SA.DivisionId,
		 SA.RollNo,
		 --SA.OrgId,
		 --SA.BatchId,
		 OM.Title AS Organization,
		 BM.Title AS Batch,
		 --SA.StudentId,
		 SA.Code AS [ID No],
		  SA.Code AS [GR No],
		 SA.Number,
		 SA.FirstName,
		 SA.LastName,
		 SA.Name,
		 --SA.DepartmentId,
		 --SA.StatusId,
		 SA.ClassName,
		 SA.DivisionName,
		 SA.StatusName,
		 R.TotalMarks,
		 R.TotalObtain,
		 R.ResultStatus,
		 R.ResultGrade,
		 R.ResultGradePoint,
		 R.RankNumber,
		 R.Percentage,
		 R.Percentile,
		 R.ResultRemark,
		 R.OtherRemark1,
		 ESGM.Code AS ExamCode,
		 ESGM.Title AS ExamName,
		 --EM.Id AS Id,
	     U.DisplayTitle AS CreatedUserName,
	     SR.ExamSeatNo,
	     SR.TotalDays,
	     SR.PresentDays,
	     SR.StudentHeight,
	     SR.StudentWeight,
	     SR.ResultRemark1,
	     SR.ResultRemark2,
	     SR.MyGoal,
	     SR.MyStrength,
	     SR.MyInterestHobby,
	     SR.Achievements,
	     SR.AreaOfInterest,
	     SR.Responsibility,
	     SR.DentalHygiene,
	     SR.VisionLeft,
	     SR.VisionRight,
	     SR.MyActivity,
	     SR.ClassTeacherRemark,
	     SR.HouseRemark,
	     SR.ResultRemark3,
	     SR.GeneralRemark,
		 CCM.Code As CastCategory,
		 SF.FatherName,
		 SF.MotherName,
	     ACM.Title AS FeeCategory,
		 HM.Title AS House,
		 SO.DateOfBirth
	     
	  	  	   
FROM 
		sStudent.vStudentAcademic SA
	INNER JOIN sSetup.tOrganizationMaster OM ON OM.Id=SA.OrgId
	INNER JOIN sSetup.tBatchMaster BM ON BM.Id=SA.BatchId
	INNER JOIN sAssessment.tResult R ON R.AcademicId=SA.AcademicId
	INNER JOIN sAssessment.tExamMaster EM ON EM.Id=R.ExamId
	INNER JOIN sSecurity.tUser U ON U.Id=R.UpdatedUserId
	INNER JOIN sAssessment.tExamSubGroupMaster ESGM ON ESGM.Id = EM.ExamSubGroupId
	LEFT JOIN sAssessment.tStudentRemark SR ON SR.AcademicId=SA.AcademicId
	LEFT JOIN sStudent.tStudentOther SO ON SO.StudentId=SA.StudentId
	LEFT JOIN sCommon.tCastCategoryMaster CCM ON CCM.Id=SO.CastCategoryId
	LEFT JOIN sStudent.tStudentFamily SF ON SF.StudentId = SA.StudentId
	LEFT JOIN sStudent.tAdmissionCategoryMaster ACM ON SA.AdmissionCategoryId = ACM.Id
	LEFT JOIN sActivity.tHouseMaster HM ON SO.HouseId = HM.Id

GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

/******************************************************************************
	Purpose          : To get User Details
	Created date     : 18 Jun 2009
	Author           : Rajendra Patel
	Copyright©       : Copyright © Sapphire Software
	Last Change Desc : Altered Employee Name
*******************************************************************************/
CREATE OR ALTER VIEW [sSecurity].[vExtUser]

AS

	SELECT	
			U.OrgId,
			U.Id,
			U.DisplayTitle,
			U.LoginTitle,
			U.LoginPassword,
			U.LastLockOutDate,
			U.NumberOfLogonAttempt,
			U.IsObsolete,
			U.CreatedUserId,
			U.CreatedDateTime,
			U.UpdatedUserId,
			U.UpdatedDateTime,
			U.SourceId,
			(EM.FirstName + ISNULL(' ' + EM.MiddleName, '') + ' ' + EM.LastName) AS EmployeeName
	
	FROM	sSecurity.tUser U
		-- Commented for Employee Migration
		--	LEFT OUTER JOIN sCommon.tEmployeeMaster EM
		-- End of Comment for Employee Migration
		-- Addition For Employee Migration
			LEFT OUTER JOIN sHrm.vExtEmployeeMaster EM
		-- End of addition for Employee Migration
				ON U.SourceId = EM.Id ANd U.OrgId = EM.OrgId


GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE OR ALTER VIEW [sProcurement].[vExtGoodsReceiptNote]

AS
	 SELECT Id,
			PurchaseOrderId,
			Number,
			ReceivedByName,
			DeliveryOrderNumber,
			DeliveryOrderDate,
			Amount,
			PackagingCharge,
			RoundedNetAmount,
			Remark,
			IsObsolete,
			UpdatedUserId,
			StoreLocationId,
			CreatedDateTime ,
			OrgGroupId
			 
	   FROM sProcurement.tGoodsReceiptNote

GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE OR ALTER VIEW [sProcurement].[vExtGoodsReceiptNoteBatch]

AS
	 SELECT Id,
			GoodsReceiptNoteDetailId,
			Number,
			ExpiryDate,
			ReceiptQuantity,
			ReceiptBonusQuantity,
			IsObsolete,
			UpdatedUserId,
			ReturnedQuantity,
			OrgGroupId
	   FROM sProcurement.tGoodsReceiptNoteBatch

GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE OR ALTER VIEW [sProcurement].[vExtGoodsReceiptNoteDetail]

AS
	SELECT	GRND.Id,
			GRND.GoodsReceiptNoteId,
			GRND.PurchaseOrderDetailId,
			GRND.ReceiptQuantity,
			GRND.ReceiptBonusQuantity,
			GRND.NetUnitPrice,
			GRND.TaxGroupId,
			GRND.TaxRate,
			GRND.TaxAmount,
			GRND.OtherTaxAmount,
			GRND.DiscountAmount,
			GRND.DiscountPercentage,
			GRND.IsObsolete,
			GRND.AddonCharges,
			GRND.InvoicedQuantity,
			GRN.Number,
			GRN.Remark,
			GRND.OrgGroupId
			
	FROM	sProcurement.tGoodsReceiptNoteDetail GRND
	
			INNER JOIN sProcurement.tGoodsReceiptNote GRN
				ON GRND.GoodsReceiptNoteId = GRN.Id
				
		--UNION ALL
		--	SELECT 		
		--	SGRN.Id,
		--	SGRN.GoodsReceiptNoteId,		
		--	SGRN.PurchaseOrderDetailId,	
		--	SGRN.ReceiptQuantity,
		--	0 AS ReceiptBonusQuantity,		
		--	SGRN.UnitPrice,		
		--	SGRN.TaxGroupId,
		--	SGRN.TaxRate,
		--	SGRN.TaxAmount,
		--	SGRN.OtherTaxAmount,
		--	SGRN.DiscountAmount,
		--	SGRN.DiscountPercentage,
		--	0 AS IsObsolete,
		--	0 AS AddonCharges,		
		--	SGRN.InvoicedQuantity,
		--	GRN.Number,
		--	GRN.Remark
			
			
		--	FROM sConsignment.VExtGoodsReceiptNoteDetail  SGRN
		--	INNER JOIN sConsignment.VExtGoodsReceiptNote GRN
		--		ON SGRN.GoodsReceiptNoteId = GRN.Id

GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE OR ALTER VIEW [sProcurement].[vGoodsReceiptNoteSearch]

AS

SELECT DISTINCT

GRN.Id,

GRN.PurchaseOrderId,

GRN.Number,
GRN.OrgGroupId,
GRN.StoreLocationId


--(SELECT ((SUM(ReceiptQuantity) + SUM(ReceiptBonusQuantity)) - SUM(ReturnedQuantity)) FROM sProcurement.vExtGoodsReceiptNoteBatch

--WHERE GoodsReceiptNoteDetailId = GRND.Id) AS BalanceQuantity

 

FROM sProcurement.vExtGoodsReceiptNote GRN
INNER JOIN sProcurement.vExtGoodsReceiptNoteDetail GRND
ON GRN.Id = GRND.GoodsReceiptNoteId
WHERE 

(SELECT ((SUM(ReceiptQuantity) + SUM(ReceiptBonusQuantity)) - SUM(ReturnedQuantity)) FROM sProcurement.vExtGoodsReceiptNoteBatch
WHERE GoodsReceiptNoteDetailId = GRND.Id) > 0

GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

/******************************************************************************  
 Purpose          : To get Doctor details  
 Created date     : 18 Jan 2012  
 Author           : Rajendra Patel 
 Copyright©       : Copyright © Sapphire Software Solutions
 Last Change Desc : Moved EmployeeMaster from sCommon to sHrm.
*******************************************************************************/  
CREATE OR ALTER VIEW [sCommon].[vEmployeeMaster]  
  
AS  
  
SELECT 
			EM.OrgId,
			EM.Id,  
			EM.Code,  
			EM.FirstName,  
			EM.MiddleName,  
			EM.LastName,  
			(EM.FirstName + ISNULL(' ' + EM.MiddleName, '') + ISNULL(' ' + EM.LastName, '')) EmployeeName, 
			EM.GenderId,  
			EM.DateOfBirth,  
			EM.DateOfJoining,   
			EM.StatusId,  
			EM.CreatedUserId,  
			EM.CreatedDateTime,  
			EM.UpdatedUserId,  
			EM.UpdatedDateTime, 
			EM.IsObsolete,
			vEU.Id AS UserId,  
			ODM.Id DepartmentId,  
			ODM.Title  DepartmentCode,  
			ODM.Title DepartmentName
 
  FROM 
			sHrm.vExtEmployeeMaster EM  
	  INNER JOIN sSecurity.vExtUser vEU ON EM.Id = vEU.SourceId ANd EM.OrgId = vEU.OrgId
	  LEFT JOIN sSetup.tDepartmentMaster ODM ON EM.DepartmentId = ODM.Id ANd EM.OrgId = ODM.OrgId

GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO




/******************************************************************************  
 Purpose          : To get Batch Wise  Class
 Created date     : 21 Nov 2013
 Author           : Kundan Patel 
 Copyright©       : Copyright © Sapphire Software Solutions
 ******************************************************************************/  
CREATE OR ALTER VIEW [sAcademic].[vExamTeacherSubjectDivision]  
  
AS  
  
SELECT 	
	  SM.Id AS SubjectId,
	  SM.Code AS SubjectCode,
	  SM.Title AS SubjectName,	  
	  SM.Number,
	  SM.OrderIndex,
	  SM.OrgId,
	  SM.IsObsolete,
	  TS.EmployeeId,
	  DM.Id AS DivisionId,
	  DM.Title AS DivisionTitle,
	  DM.OrderIndex AS DivisionOrderIndex,
	  CM.Id AS ClassId,
	  CM.Title AS ClassTitle,
	  CM.OrderIndex AS ClassOrderIndex,
	  ED.Id AS ExamDetailId,
	  EM.Id As ExamId,
	  CS.Id AS ClassSubjectId
	  
	  
  FROM 
	sSetup.tSubjectMaster SM
	INNER JOIN sAcademic.tTeacherSubject TS ON TS.SubjectId= SM.Id
	INNER JOIN sAcademic.tTeacherSubjectDivision TSD ON TS.SubjectId = TSD.SubjectId AND TS.EmployeeId = TSD.EmployeeId
	INNER JOIN sSetup.tDivisionMaster DM ON TSD.DivisionId = DM.Id
	INNER JOIN sSetup.tClassMaster CM ON DM.ClassId = CM.Id
	INNER JOIN sSetup.tClassSubject CS ON CS.ClassId = CM.Id AND CS.SubjectId = SM.Id
	INNER JOIN sAssessment.tExamMaster EM ON EM.ClassId = CM.Id 
	INNER JOIN sAssessment.tExamDetail ED ON Ed.ExamId = EM.Id AND ED.ClassSubjectId = CS.Id

GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO


/******************************************************************************  
 Purpose          : To get Batch Wise  Class
 Created date     : 21 Nov 2013
 Author           : Kundan Patel 
 Copyright©       : Copyright © Sapphire Software Solutions
 ******************************************************************************/  
CREATE OR ALTER VIEW [sAcademic].[vTeacherSubject]  
  
AS  
  
SELECT 	
	  SM.Id AS SubjectId,
	  SM.Code AS SubjectCode,
	  SM.Title AS SubjectName,
	  SM.Number,
	  SM.OrderIndex,
	  SM.OrgId,
	  SM.IsObsolete,
	  TS.EmployeeId
  FROM 
	sSetup.tSubjectMaster SM
	INNER JOIN sAcademic.tTeacherSubject TS ON TS.SubjectId= SM.Id

GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

/******************************************************************************  
 Purpose          : To get Batch Wise  Class
 Created date     : 21 Nov 2013
 Author           : Kundan Patel 
 Copyright©       : Copyright © Sapphire Software Solutions
 ******************************************************************************/
CREATE OR ALTER VIEW [sAcademic].[vTeacherSubjectDivision]
AS
SELECT        SM.Id AS SubjectId, SM.Code AS SubjectCode, SM.Title AS SubjectName, SM.Number, SM.OrderIndex, SM.OrgId, SM.IsObsolete, TS.EmployeeId, DM.Id AS DivisionId, DM.Title AS DivisionTitle, 
                         DM.OrderIndex AS DivisionOrderIndex, CM.Id AS ClassId, CM.Title AS ClassTitle, CM.OrderIndex AS ClassOrderIndex, CM.IsObsolete AS CMIsObsolete, DM.IsObsolete AS DMIsObsolete, TSD.SubjectCordinatorEmployeeId,CM.ClassGroupId
FROM            sSetup.tSubjectMaster AS SM INNER JOIN
                         sAcademic.tTeacherSubject AS TS ON TS.SubjectId = SM.Id INNER JOIN
                         sAcademic.tTeacherSubjectDivision AS TSD ON TS.SubjectId = TSD.SubjectId AND TS.EmployeeId = TSD.EmployeeId INNER JOIN
                         sSetup.tDivisionMaster AS DM ON TSD.DivisionId = DM.Id INNER JOIN
                         sSetup.tClassMaster AS CM ON DM.ClassId = CM.Id

GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO


CREATE OR ALTER VIEW [sAccounting].[vLedgerMaster]

AS
	select	L.Id,
		L.Title,
		L.LedgerGroupId,
		L.LedgerTypeId,
		L.AccountingYearId,		 
		AY.AccountId,
		LedgerMainGroupId,
		LGM.Code
		
	FROM sAccounting.tLedgerMaster L 
		 INNER JOIN  sAccounting.tAccountingYearMaster AY 
	ON   L.AccountingYearId = AY.Id AND IsObsolete = 0
	 INNER JOIN sAccounting.tLedgerGroupMaster LGM ON LedgerGroupId = LGM.Id  
	 AND L.AccountingYearId = LGM.AccountingYearId 
	 WHERE L.IsObsolete = 0

GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO



CREATE OR ALTER VIEW [sAccounting].[vMainLedgerTypeMaster]

AS
	SELECT LTM.Title AS LedgerMainType,LTM.Id AS LedgerMainTypeId ,LGM.Id AS LedgerGroupId FROM sAccounting.tLedgerGroupMaster LGM INNER JOIN 
	sAccounting.tLedgerMainGroupMaster LMG ON LGM.LedgerMainGroupId = LMG.Id
    INNER JOIN sAccounting.tMainLedgerTypeMaster LTM ON LTM.Id = LMG.MainLedgerTypId

GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO





/******************************************************************************  
 Purpose          : To get Batch Wise  Class
 Created date     : 21 Nov 2013
 Author           : Kundan Patel 
 Copyright©       : Copyright © Sapphire Software Solutions
 ******************************************************************************/  
CREATE OR ALTER VIEW [sAccounting].[vUserAccount]  
  
AS  
  
SELECT 	
	  AM.Id as Id,
	  AM.Title,
	  AM.OrgGroupId,
	  AM.IsActive AS IsObsolete,
	  UA.UserId
	  	   
  FROM 
  
	sAccounting.tAccountMaster AM
	INNER JOIN sSecurity.tUserAccess UA ON UA.SourceId=AM.Id AND UA.SourceTypeId=113

GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO



/******************************************************************************
	Purpose          : External view to get data from sAlert.tTemplatemaster table
	Created date     : 27 May 2009
	Author           : System Generated
	Copyright©       : Copyright © Sapphire Software
	Last Change Desc : 
*******************************************************************************/

CREATE OR ALTER VIEW [sAlert].[vEventDetail]

AS
	 SELECT     
		 DISTINCT(EM.Id),
		 EM.Title,
		 AC.OrgId,
		 AC.IsObsolete,
		 AC.Template
	 
	FROM         
		sAlert.tAlertConfiguration AC
	LEFT JOIN sAlert.tEventMaster EM ON EM.Id=AC.EventId



GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO





/******************************************************************************
	Purpose          : External view to get data from sAlert.tTemplatemaster table
	Created date     : 27 May 2009
	Author           : System Generated
	Copyright©       : Copyright © Sapphire Software
	Last Change Desc : 
*******************************************************************************/

CREATE OR ALTER VIEW [sAlert].[vEventMaster]

AS
	 SELECT 
			   EM.[Id]
			  ,EM.[ModuleId]
			  ,EM.[Title]
			  ,EM.[IsObsolete]
			  ,MP.OrgGroupId
	FROM 
			[sAlert].[tEventMaster] EM
		INNER JOIN sSecurity.tModulePermission MP ON MP.ModuleId=EM.ModuleId

GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/******************************************************************************
	Purpose          : External view to get data from sAlert.tTemplatemaster table
	Created date     : 27 May 2009
	Author           : System Generated
	Copyright©       : Copyright © Sapphire Software
	Last Change Desc : 
*******************************************************************************/

CREATE OR ALTER VIEW [sAlert].[vExtTemplatemaster]

AS
	 SELECT     
		 Title, 
		 Id, 
		 AlertModeId, 
		 EventId, 
		 Remark, 
		 IsObsolete
	 
	FROM         
		sAlert.tTemplateMaster

GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE OR ALTER VIEW [sAssessment].[vCCECSGROUPMASTER]  
  
AS  
  
SELECT CG.Id,
	   CG.Title,
	   CG.OrderIndex,
	   CG.GradeSetId,
	   CG.OrgId,
	   CC.ClassId ,
	   CC.BatchId
FROM sAssessment.tCCECSGroupMaster CG 
INNER JOIN sAssessment.tCCECSClass CC ON CG.Id=CC.CCECSGroupId

GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
 

















/******************************************************************************  
 Purpose          : To get Batch Wise  Class
 Created date     : 21 Nov 2013
 Author           : Kundan Patel 
 Copyright©       : Copyright © Sapphire Software Solutions
 ******************************************************************************/  
CREATE OR ALTER VIEW [sAssessment].[vCCEExamClassSubject]  
  
AS  
  
SELECT 	
		SM.Title AS SubjectName,
		CM.DisplayTitle AS ClassName,
		CM.Id AS ClassId,
		SM.Id AS SubjectId,
		CS.Id AS ClassSubjectId,
		CS.OrgId,
		CS.OrderIndex,
		CS.IsOptional
FROM 
		sSetup.tSubjectMaster SM 
	INNER JOIN sSetup.tClassSubject CS ON CS.SubjectId=SM.Id
	INNER JOIN sSetup.tClassMaster CM ON CM.Id=CS.ClassId

GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO










/******************************************************************************  
 Purpose          : To get Batch Wise  Class
 Created date     : 21 Nov 2013
 Author           : Kundan Patel 
 Copyright©       : Copyright © Sapphire Software Solutions
 ******************************************************************************/  
CREATE OR ALTER VIEW [sAssessment].[vExamClass]  
  
AS  
  
SELECT 
	ExamSubGroupId,
	CM.Id AS ClassId,
	CM.Title AS ClassName,
	CM.OrderIndex AS OrderIndex	,
	EM.BatchId 
 FROM sSetup.tClassMaster CM
 INNER JOIN sAssessment.tExamMaster EM ON CM.Id=EM.ClassId

GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

















/******************************************************************************  
 Purpose          : To get Batch Wise  Class
 Created date     : 21 Nov 2013
 Author           : Kundan Patel 
 Copyright©       : Copyright © Sapphire Software Solutions
 ******************************************************************************/  
CREATE OR ALTER VIEW [sAssessment].[vExamClassDivision]  
  
AS  
  
SELECT 	
		 EM.Id,
		 ESGM.Id AS ExamSubGroupId,
		 ESGM.Title AS Name,
		 EM.BatchId,
	  	 EM.ClassId,
	  	 DM.Id AS DivisionId
FROM 
		sAssessment.tExamMaster EM
	INNER JOIN sSetup.tDivisionMaster DM ON DM.ClassId=EM.ClassId
	INNER JOIN sAssessment.tExamSubGroupMaster ESGM ON ESGM.Id = EM.ExamSubGroupId

GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO























/******************************************************************************  
 Purpose          : To get Batch Wise  Class
 Created date     : 21 Nov 2013
 Author           : Kundan Patel 
 Copyright©       : Copyright © Sapphire Software Solutions
 ******************************************************************************/  
CREATE OR ALTER VIEW [sAssessment].[vExamMaster]  
  
AS  
  
SELECT 	
		EM.Id AS Id,		
		ESGM.Code AS Code,
		ESGM.Title AS Name,
		EM.IsProcessResult,
		EM.ClassId,
		EM.BatchId,
		EGM.Id AS ExamGroupId,
		EGM.Title As ExamGroup,
		EGM.OrgId AS OrgId,
		ISNULL(EM.IsIncludeCoScholastic,0) AS IsIncludeCoScholastic,
		ISNULL(EM.IsLockForEdit,0) AS IsLockForEdit,
		ESGM.Id AS ExamSubGroupId,
		ESGM.OrderIndex 
FROM 
		sAssessment.tExamMaster EM
	INNER JOIN sAssessment.tExamSubGroupMaster ESGM ON ESGM.Id = EM.ExamSubGroupId
	INNER JOIN sAssessment.tExamGroupMaster EGM ON EGM.Id = ESGM.ExamGroupId

GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO







/******************************************************************************  
 Purpose          : To get Batch Wise  Class
 Created date     : 21 Nov 2013
 Author           : Kundan Patel 
 Copyright©       : Copyright © Sapphire Software Solutions
 ******************************************************************************/  
CREATE OR ALTER VIEW [sAssessment].[vExamSubGroupMaster]  
  
AS  
  
SELECT DISTINCT 
ESGM.Id,
ESGM.Title,
ESGM.OrgId,
EM.Id AS ExamId,
EM.ClassId,
EM.IsProcessResult,
ISNULL(EM.ISLOCKFOREDIT,0) AS ISLOCKFOREDIT,
EM.BatchId,
ESGM.OrderIndex

FROM sAssessment.tExamSubGroupMaster ESGM 
INNER JOIN sAssessment.tExamMaster EM ON ESGM.Id = EM.ExamSubGroupId

GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/******************************************************************************
	Purpose          : External view to get data from sAssetManagement.tAssetAdjustment table
	Created date     : 21 Jan 2012
	Author           : Rajendra Patel 
	Copyright©       : Copyright © Sapphire Software
	Last Change Desc : 
*******************************************************************************/

CREATE OR ALTER VIEW [sAssetManagement].[vExtAssetAdjustment]

AS
	 SELECT Id,
			Number
	   FROM sAssetManagement.tAssetAdjustment

GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/******************************************************************************
	Purpose          : External view to get data from sAssetManagement.tAssetAdjustmentDetail table
	Created date     : 21 Jan 2012
	Author           : Rajendra Patel 
	Copyright©       : Copyright © Sapphire Software
	Last Change Desc : 
*******************************************************************************/

CREATE OR ALTER VIEW [sAssetManagement].[vExtAssetAdjustmentDetail]

AS
	 SELECT Id,
			AssetAdjustmentId,
			AssetAuditDetailId,
			Remark,
			ReasonId
	   FROM sAssetManagement.tAssetAdjustmentDetail

GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/******************************************************************************
	Purpose          : External view to get data from sAssetManagement.tAssetAudit table
	Created date     : 15 Jan 2012
	Author           : Rajendra Patel 
	Copyright©       : Copyright © Sapphire Software
	Last Change Desc : 
*******************************************************************************/

CREATE OR ALTER VIEW [sAssetManagement].[vExtAssetAudit]

AS
	 SELECT Id,
			Number,
			LocationId,
			Remark,
			StatusId,
			OrgId,
			StoreLocationId
	   FROM sAssetManagement.tAssetAudit

GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/******************************************************************************
	Purpose          : External view to get data from sAssetManagement.tAssetAuditDetail table
	Created date     : 15 Jan 2012
	Author           : Rajendra Patel 
	Copyright©       : Copyright © Sapphire Software
	Last Change Desc : 
*******************************************************************************/

CREATE OR ALTER VIEW [sAssetManagement].[vExtAssetAuditDetail]

AS
	 SELECT Id,
			AssetAuditId,
			AssetId,
			AssetCheck
	   FROM sAssetManagement.tAssetAuditDetail

GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO


/******************************************************************************
	Purpose          : External view to get data from sAssetManagement.vAssetDepartment table
	Created date     : 22 Jan 2012
	Author           : Rajendra Patel 
	Copyright©       : Copyright © Sapphire Software
	Last Change Desc : Rename departmentId field, 123
*******************************************************************************/
CREATE OR ALTER VIEW [sAssetManagement].[vExtAssetDepartment]
AS
      
            SELECT 
                        ODM.Id DepartmentId,
                        ODM.Title DepartmentCode,
                        ODM.Title DepartmentTitle,
                        LM.Id LocationId,
                        LM.Code LocationCode,
                        LM.Title LocationTitle,
                        DL.OrgId
            FROM
                  sAssetManagement.tDepartmentLocation DL
            INNER JOIN
                  sSetup.tDepartmentMaster  ODM
            ON
                  ODM.Id = DL.OrganizationDepartmentId
            INNER JOIN  
                  sFixedAsset.tLocationMaster LM
            ON
                  LM.Id = DL.AssetLocationId
                  
            WHERE LM.IsObsolete = 0



GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/******************************************************************************
	Purpose          : External view to get data from sAssetManagement.tAssetMaster table
	Created date     : 13 Jan 2012
	Author           : Rajendra Patel 
	Copyright©       : Copyright © Sapphire Software
	Last Change Desc : 
*******************************************************************************/

CREATE OR ALTER VIEW [sAssetManagement].[vExtAssetMaster]

AS
	 SELECT Id,
			Code,
			Title,
			Specification,
			Accessories,
			LocationId,
			StatusId,
			VendorId,
			GoodsReceiptNumber,
			GoodsReceiptDate,
			IsInstallationRequired,
			InstallationDate,
			CategoryId,
			DepreciationUptoDate,
			IsFullyDepreciated,
			StandardPrice,
			MinStockRequired,
			MaintenancePeriod,
			IsAmc,
			MaintenanceAlertPeriod,
			AmcVendorId,
			UsageStatusId,
			LastServiceCost,
			TotalServiceCost,
			LastDownTime,
			TotalDownTime,
			LastMaintenanceDate,
			NextMaintenanceDate,
			ModelNumber,
			IsCritical,
			IsObsolete,
			UpdatedUserId
	   FROM sAssetManagement.tAssetMaster

GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/******************************************************************************
		Purpose          : To get model number
		Created date     : 14 Dec 2010
		Author           : Rajendra Patel 
		Copyright©       : Copyright © Sapphire Software
		Last Change Desc : 
	*******************************************************************************/
CREATE OR ALTER VIEW [sAssetManagement].[vExtAssetModelNumber]
AS
(

SELECT DISTINCT ModelNumber, Id, IsObsolete,OrgId,StoreLocationId FROM sAssetManagement.tAssetMaster

)

GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/******************************************************************************
	Purpose          : External view to get data from sAssetManagement.tDepartmentLocation table
	Created date     : 22 Jan 2012
	Author           : Rajendra Patel
	Copyright©       : Copyright © Sapphire Software
	Last Change Desc : 
*******************************************************************************/

CREATE OR ALTER VIEW [sAssetManagement].[vExtDepartmentLocation]

AS
	 SELECT Id,
			OrganizationDepartmentId,
			AssetLocationId,
			UpdatedUserId
	   FROM sAssetManagement.tDepartmentLocation

GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/******************************************************************************
	Purpose          : External view to get data from sAssetManagement.tMaintenence table
	Created date     : 21 Jan 2012
	Author           : Rajendra Patel
	Copyright©       : Copyright © Sapphire Software
	Last Change Desc : 
*******************************************************************************/

CREATE OR ALTER VIEW [sAssetManagement].[vExtMaintenence]

AS
	 SELECT Id,
			Number,
			AssetId,
			MaintenenceDate,
			MaintenenceBy,
			MaintenenceReason,
			MaintenenceCost,
			NextMaintenenceDate,
			OrgId,
			StoreLocationId
	   FROM sAssetManagement.tMaintenence

GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/******************************************************************************
	Purpose          : External view to get data from sAssetManagement.tMovement table
	Created date     : 15 Jan 2012
	Author           : Rajendra Patel 
	Copyright©       : Copyright © Sapphire Software
	Last Change Desc : 
*******************************************************************************/

CREATE OR ALTER VIEW [sAssetManagement].[vExtMovement]

AS
	 SELECT Id,
			Number,
			MovementDate,
			Remark,
			FromLocationId,
			ToLocationId
	   FROM sAssetManagement.tMovement

GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/******************************************************************************
	Purpose          : External view to get data from sAssetManagement.tMovementDetail table
	Created date     : 15 Jan 2012
	Author           : Rajendra Patel 
	Copyright©       : Copyright © Sapphire Software
	Last Change Desc : 
*******************************************************************************/

CREATE OR ALTER VIEW [sAssetManagement].[vExtMovementDetail]

AS
	 SELECT Id,
			MovementId,
			AssetId
	   FROM sAssetManagement.tMovementDetail

GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/******************************************************************************
	Purpose          : External view to get data from sAssetManagement.tRecordManualEvent table
	Created date     : 21 Jan 2012
	Author           : Rajendra Patel
	Copyright©       : Copyright © Sapphire Software
	Last Change Desc : 
*******************************************************************************/

CREATE OR ALTER VIEW [sAssetManagement].[vExtRecordManualEvent]

AS
	 SELECT Id,
			Number,
			AssetId,
			StatusId,
			Remark,
			OrgId,
			StoreLocationId
	   FROM sAssetManagement.tRecordManualEvent

GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO





/******************************************************************************  
 Purpose          : To get Batch Wise  Class
 Created date     : 21 Nov 2013
 Author           : Kundan Patel 
 Copyright©       : Copyright © Sapphire Software Solutions
 ******************************************************************************/  
CREATE OR ALTER VIEW [sAttendance].[vApplicableAttendanceType]  
  
AS  
  
SELECT 	
	  ATM.Id as AttendanceTypeId,
	  ATM.Title as AttendanceType,
	  ATM.IsObsolete,
	  ATM.OrderIndex,
	  AAT.OrgId
	  	   
  FROM 
  
	sAttendance.tApplicableAttendanceType AAT
	INNER JOIN sAttendance.tAttendanceTypeMaster ATM
		ON ATM.Id=AAT.AttendanceTypeId
		







GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/**************************************************************************************************  
    Purpose          : This view will give table list.
	Created Date     : 24 Feb 2009
    Author           : Rajendra Patel 
    Copyright©       : Copyright © Sapphire Software      
	Last Change Desc : 
****************************************************************************************************/ 
CREATE OR ALTER VIEW [sAudit].[vAuditTrailTableList]
AS

SELECT ROW_NUMBER() OVER (ORDER BY O.Name) AS RowId, 
	   S.name + '.' + O.Name TableName, 0 AS IsDeleted
  FROM sys.objects O
	   INNER JOIN sys.schemas S	
			   ON O.schema_id = S.schema_id 
 WHERE type = 'U' 
       AND O.name LIKE 't%' 
	   AND O.name NOT IN (SELECT DISTINCT O.name
                             FROM (SELECT ID, name
                                     FROM sysobjects
                                          WHERE name LIKE 't%') O 
									INNER JOIN syscolumns C 
											ON O.ID = C.ID 
                                    INNER JOIN (SELECT name, xtype
                                                  FROM systypes
                                                 WHERE name = 'image') T 
											ON C.xtype = T .xtype) 
		AND O.name NOT IN (SELECT name
                             FROM sysobjects
                            WHERE name LIKE 't%' 
								  AND name NOT IN (SELECT O.name
                                                     FROM (SELECT ID, name
                                                             FROM sysobjects
                                                            WHERE name LIKE 't%') O 
                                                          INNER JOIN sys.columns C 
																  ON O.ID = C.Object_ID
                                                    WHERE C.Is_Identity = 1))

GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/******************************************************************************
	Purpose          : View to get Audit schema names
	Created date     : 17 Feb 2011
	Author           : Rajendra Patel
	Copyright©       : Copyright © Sapphire Software
	Last Change Desc : 

*******************************************************************************/

CREATE OR ALTER VIEW [sAudit].[vExtAuditSchemaName]

AS
SELECT DISTINCT  SchemaTitle, S.schema_id  AS Id FROM sAudit.tAuditTable A
INNER JOIN sys.schemas S
ON A.SchemaTitle = S.name

GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/******************************************************************************
	Purpose          : View to get Audit Table names
	Created date     : 23 Feb 2011
	Author           : Rajendra Patel
	Copyright©       : Copyright © Sapphire Software
	Last Change Desc : 

*******************************************************************************/

CREATE OR ALTER VIEW [sAudit].[vExtGetAuditTableName]

AS

SELECT Id AS TableId,
       TableTitle AS TableName, 
       SchemaTitle AS SchemaName,
       IsObsolete AS IsDeleted
FROM sAudit.tAuditTable

GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/******************************************************************************
	Purpose          : View to get schema names
	Created date     : 17 Feb 2011
	Author           : Rajendra Patel
	Copyright©       : Copyright © Sapphire Software
	Last Change Desc : 

*******************************************************************************/

CREATE OR ALTER VIEW [sAudit].[vExtGetSchemaName]

AS
SELECT schema_id AS Id, name AS Title

FROM sys.schemas

WHERE schema_id < 16000

AND schema_id NOT IN (1, 2, 3, 4)

GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/******************************************************************************
	Purpose          : View to get Table names
	Created date     : 18 Feb 2011
	Author           : Rajendra Patel
	Copyright©       : Copyright © Sapphire Software
	Last Change Desc : Modified sp

*******************************************************************************/

CREATE OR ALTER VIEW [sAudit].[vExtGetTableName]

AS

SELECT  name AS Title, schema_id AS SchemaId  FROM sys.tables WHERE name  NOT IN
(
SELECT T.name     
FROM   sys.extended_properties AS EXP
LEFT OUTER JOIN sys.tables AS T
ON  T.object_id = EXP.major_id
INNER JOIN sys.schemas S
ON S.schema_id = T.schema_id 
WHERE exp.name = 'type' AND exp.value = 'StaticMaster'
) AND name <> 'sysdiagrams'

GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/******************************************************************************
	Purpose          : View to get Table names
	Created date     : 18 Feb 2011
	Author           : Rajendra Patel
	Copyright©       : Copyright © Sapphire Software
	Last Change Desc : Modified sp

*******************************************************************************/

CREATE OR ALTER VIEW [sAudit].[vExtGetTableNameBySchema]

AS

SELECT  ST.name  AS TableTitle, SS.schema_id AS SchemaId, SS.name AS SchemaTitle FROM sys.tables ST
INNER JOIN  sys.schemas SS 
ON SS.schema_id = ST.schema_id 
WHERE ST.name NOT IN
(
SELECT T.name     
FROM   sys.extended_properties AS EXP
LEFT OUTER JOIN sys.tables AS T
ON  T.object_id = EXP.major_id
INNER JOIN sys.schemas S
ON S.schema_id = T.schema_id 
WHERE exp.name = 'type' AND exp.value = 'StaticMaster'
) AND ST.name <> 'sysdiagrams'

GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO


/******************************************************************************
	Purpose          : External view to get data from sAssetManagement.tAssetAudit table
	Created date     : 15 Jan 2012
	Author           : Rajendra Patel 
	Copyright©       : Copyright © Sapphire Software
	Last Change Desc : 
*******************************************************************************/

CREATE OR ALTER VIEW [sBi].[vActionAudit]

AS
	Select A.*,
		   F.DocumentSourceId AS FindingAuditId
		    
	FROM sBi.tAction A 
	LEFT JOIN sBi.tAudit AU ON AU.Id = A.DocumentSourceId AND A.DocumentSourceTypeId = 1018
	LEFT JOIN sBi.tFinding F ON F.Id = A.DocumentSourceId AND A.DocumentSourceTypeId = 1020



GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

/******************************************************************************
	Purpose          : External view to get data from sAssetManagement.tAssetAudit table
	Created date     : 15 Jan 2012
	Author           : Rajendra Patel 
	Copyright©       : Copyright © Sapphire Software
	Last Change Desc : 
*******************************************************************************/

CREATE OR ALTER VIEW [sBi].[vAuditLocation]

AS
	SELECT [Id]
      ,[Number]
      ,[FunctionalityId]
      ,[AuditTypeId]
      ,[AuditStatusId]
      ,[StartDate]
      ,[EndDate]
      ,[AuditReasonId]
      ,[LeadAuditorEmployeeId]
      ,[GuestAuditor]
      ,[BackupAuditorEmployeeId]
      ,[QuestionnairesId]
      ,[Remark]
      ,[IsObsolete]
      ,[CreatedUserId]
      ,[CreatedDateTime]
      ,[UpdatedUserId]
      ,[UpdatedDateTime]
      ,[OrgId]
      ,AL.StoreLocationId
  FROM  [sBi].[tAudit] A
  INNER JOIN sBi.tAuditLocation AL ON AL.AuditId = A.Id


GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO








/******************************************************************************  
 Purpose          : To get Batch Wise  Class
 Created date     : 21 Nov 2013
 Author           : Kundan Patel 
 Copyright©       : Copyright © Sapphire Software Solutions
 ******************************************************************************/  
CREATE OR ALTER VIEW [sCanteen].[vUserCanteen]  
  
AS  
  
SELECT 	
	  CM.Id as Id,
	  CM.Title,
	  CM.OrgGroupId,
	  CM.IsObsolete,
	  UA.UserId
	  	   
  FROM 
  
	sCanteen.tCanteenMaster CM
	INNER JOIN sSecurity.tUserAccess UA ON UA.SourceId=CM.Id 
	AND UA.SourceTypeId=2026

GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE OR ALTER VIEW [sCertificate].[vPrintTemplateBonafideCertificate]
AS
	SELECT
		B.Id,
		B.Number,
		B.AcademicId,
		SM.Code,
		[sStudent].[fStudentNameGet](SM.FirstName , SM.MiddleName, SM.LastName,OM.StudentNameFormatId ) AS Name,
		CM.DisplayTitle+' - '+DM.DisplayTitle AS Class,
		BM.Title AS Batch,
		SM.FirstName,
		SM.MiddleName,
		SM.LastName,
		SO.DateOfBirth,
		ISNULL(SO.BirthDateInWord,'') AS DOBInWords,
		ISNULL(SO.BirthPlace,'') AS BirthPlace,
		ISNULL(SO.BirthTaluka,'') AS Taluka,
		ISNULL(SO.BirthDistrict,'')	AS District,
		B.BonafideDate,
		ISNULL(SO.FamilyCast,'') AS Caste,
		ISNULL(SO.FamilySubCast,'') AS SubCaste,
		ISNULL(OM.CityName,'') AS City,
		ISNULL(OM.StateName,'') AS CityState,
		SO.GenderId,
		SI.StudentPhoto,
		OM.OrgLogo,
		OM.Title AS OrgTitle,
		SM.StatusId,
		A.RollNo,
		DM.OrderIndex AS DivisionOrder,
		ATM.Title AS AffiliationType,
		OM.ReportOrgTitle,
		OM.SmallAddress1,
		OM.SmallAddress2,
		OM.RegistrationDetail,
		ISNULL(RM.Title,'') AS ReligionName,
		B.Remark AS ReceiptNo,
		SF.FatherName,
		SA.AdmissionDate,
		DP.Title AS DepartmentName,
		ISNULL(OM.DegreeAwarded,'') AS DegreeAwarded,
		OI.CertificateHeaderA4,
		REPLICATE(',',OM.LetterHeadMargin)    AS LetterHeadMargin,
		ISNULL(B.BonafiedReason,'') AS BonafiedReason,
		CT.Title AS Category,
		SF.MotherName,
		C.PermanentStreetOne,
		C.PermanentStreetTwo,
		C.PermanentStreetThree,
		C.PermanentCountry,
		C.PermanentState,
		C.PermanentCity,
		C.PermanentZipCode,
		C.PermanentContactNo,
		C.PresentStreetOne,
		C.PresentStreetTwo,
		C.PresentStreetThree,
		C.PresentCountry,
		C.PresentState,
		C.PresentCity,
		C.PresentContactNo,
		C.PresentZipCode,
		SM.SUId,
		SO.AdhaarNumber,
		CM.DisplayTitle AS ClassName,
		ISNULL(CTM.Nationality,'') AS Nationality,
		OM.PrincipalSignature,
		ISnull(AR.SourceId,0) AS StudentidForhostel,
		DM.DisplayTitle AS Divisionname,
		SM.RecordStatusId,
		SO.BirthState AS BirthState,
		CM1.Title AS AdmissionStandard,
		A.BatchId,
		GM.Title AS Gender,
		ISNULL(SM.LocalName,'') AS LocalName,	--Add for Mantis: 32199
		ISNULL(SM.PENNumber,'') AS PENNo,	--Add for Mantis: 33853
        '[AdhaarNumber],[AdmissionDate],[AdmissionStandard],[AffiliationType],[Batch],[BirthPlace],[BirthState],[BonafideDate],[BonafiedReason],[Caste],[Category],[CertificateHeaderA4],[City],[CityState],[Class],[ClassName],[Code],[ColumnList],[DateOfBirth],[DegreeAwarded],[DepartmentName],[District],[Divisionname],[DivisionOrder],[DOBInWords],[FatherName],[FirstName],[Gender],[LastName],[LetterHeadMargin],[MiddleName],[MotherName],[Name],[Nationality],[Number],[OrgLogo],[OrgTitle],[PermanentCity],[PermanentContactNo],[PermanentCountry],[PermanentState],[PermanentStreetOne],[PermanentStreetThree],[PermanentStreetTwo],[PermanentZipCode],[PresentCity],[PresentContactNo],[PresentCountry],[PresentState],[PresentStreetOne],[PresentStreetThree],[PresentStreetTwo],[PresentZipCode],[PrincipalSignature],[ReceiptNo],[RecordStatusId],[RegistrationDetail],[ReligionName],[ReportOrgTitle],[RollNo],[SmallAddress1],[SmallAddress2],[StudentPhoto],[SubCaste],[Taluka],[LocalName],[PENNo],[SUID],[Remark],[UIDNumber],[Kumar-Kumari],[He-She],[His-Her],[is-was],[Mr-Miss],[Son-Daughter],[LeftDate]' As ColumnList,  --added[Remark] by dhananjay for mantis - 38238
		ISNULL(B.Remark,'') AS Remark, --added by dhananjay for mantis - 38238
		--Mantis: 32199, add LocalName in ColumnList
		--Manits: 33853, add PENNo and SUID in ColumnList
		ISNULL(SO.UIDNumber ,'') AS UIDNumber,  --Mantis - 38768
		CASE WHEN SO.GenderId = 1 Then 'Kumar'
			WHEN SO.GenderId = 2 Then 'Kumari' END AS [Kumar-Kumari],
		CASE WHEN SO.GenderId = 1 Then 'He'
			WHEN SO.GenderId = 2 Then 'She' END AS [He-She],
		CASE WHEN SO.GenderId = 1 Then 'His'
			WHEN SO.GenderId = 2 Then 'Her' END AS [His-Her],
		CASE WHEN SM.StatusId = 1 Then 'is'
			ELSE 'was' END AS [is-was],
		CASE WHEN SO.GenderId = 1 Then 'Mr.'
			WHEN SO.GenderId = 2 Then 'Miss' END AS [Mr-Miss],
		CASE WHEN SO.GenderId = 1 Then 'Son'
			WHEN SO.GenderId = 2 Then 'Daughter' END AS [Son-Daughter],
		SA.LeftDate

	FROM
		sCertificate.tBonafide B
		INNER JOIN sStudent.tAcademic A ON B.AcademicId=A.Id
		LEFT JOIN sStudent.tStudentImages SI ON SI.StudentId=A.StudentId
		INNER JOIN sStudent.tStudentMaster SM ON SM.Id=A.StudentId
		LEFT JOIN sStudent.tStudentOther SO ON SO.StudentId=A.StudentId
		INNER JOIN sSetup.tDivisionMaster DM ON A.DivisionId = DM.Id
		INNER JOIN sSetup.tClassMaster CM ON CM.Id= DM.ClassId
		INNER JOIN sSetup.tBatchMaster BM ON BM.Id=A.BatchId
		INNER JOIN sSetup.tOrganizationMaster OM ON OM.Id=A.OrgId
		left JOIN sSetup.tOrgImages OI ON OI.OrgId = OM.Id
		INNER JOIN sSetup.tAffiliationMaster AM ON OM.AffiliationId = AM.Id
		INNER JOIN sSetup.tAffiliationTypeMaster ATM ON AM.AffiliationTypeId = ATM.Id
		LEFT JOIN sCommon.tReligionMaster RM ON SO.ReligionId=RM.Id
		LEFT JOIN sStudent.tStudentFamily SF ON SF.StudentId = SM.Id
		INNER JOIN sStudent.tStudentAdmission SA ON SA.StudentId = SM.Id
		LEFT JOIN sSetup.tClassMaster CM1 ON CM1.Id = SA.AdmissionStandardId
		INNER JOIN sSetup.tDepartmentMaster DP ON CM.DepartmentId = DP.Id
		LEFT JOIN sCommon.tCastCategoryMaster CT ON CT.Id = SO.CastCategoryId
		LEFT JOIN sStudent.tStudentCommunication C ON C.StudentId = A.StudentId
		LEFT JOIN sCommon.tCountryMaster CTM ON CTM.Id=SO.NationalityId
		LEFT JOIN SHostel.tAssignRoom Ar On Ar.SourceId=A.StudentId
		INNER JOIN sCommon.tGenderMaster GM ON GM.Id = SO.GenderId

GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE OR ALTER VIEW [sCertificate].[vPrintTemplateCharacterCertificate]
AS
	SELECT
		C.Id AS Id,
		C.AcademicId AS AcademicId,
		SM.Code AS Code,
		[sStudent].[fStudentNameGet](SM.FirstName , SM.MiddleName, SM.LastName,OM.StudentNameFormatId ) AS Name,
		CM.DisplayTitle+' - '+DM.DisplayTitle AS Class,
		CM.DisplayTitle AS ClassOnly,
		BM.Title AS Batch,
		SO.DateOfBirth,
		ISNULL(SO.BirthDateInWord,'') AS DOBInWords,
		ISNULL(SO.BirthPlace,'') AS BirthPlace,
		ISNULL(SO.BirthTaluka,'') AS Taluka,
		ISNULL(SO.BirthDistrict,'')	AS District,
		C.characterDate AS characterDate,
		C.Number AS Number,
		ISNULL(SO.FamilyCast,'') AS Caste,
		ISNULL(SO.FamilySubCast,'') AS SubCaste,
		ISNULL(OM.CityName,'') AS City,
		ISNULL(OM.StateName,'') AS CityState,
		SO.GenderId AS GenderId,
		SI.StudentPhoto AS StudentPhoto,
		OM.OrgLogo AS OrgLogo,
		OM.Title AS OrgName,
		OM.StateName+' '+OM.StreetTwo+' '+OM.StreetThree AS OrgAddress,
		OM.PhoneNo AS PhoneNo,
		SM.StatusId AS StatusId,
		A.RollNo AS RollNo,
		DM.OrderIndex AS DivisionOrder,
		OM.ReportOrgTitle AS ReportOrgTitle,
		OM.SmallAddress1 AS SmallAddress1,
		OM.SmallAddress2 AS SmallAddress2,
		OM.RegistrationDetail AS RegistrationDetail,
		OI.CertificateHeaderA4 AS CertificateHeaderA4,
		SA.AdmissionDate AS AdmissionDate,
		SF.FatherName AS FatherName,
		A.BatchId AS BatchId,
		ISNULL(SM.LocalName,'') AS LocalName,	--Add for Mantis: 32199
		CASE WHEN SO.GenderId = 1 Then 'Kumar'
			WHEN SO.GenderId = 2 Then 'Kumari' END AS [Kumar-Kumari],
		CASE WHEN SO.GenderId = 1 Then 'He'
			WHEN SO.GenderId = 2 Then 'She' END AS [He-She],
		CASE WHEN SO.GenderId = 1 Then 'His'
			WHEN SO.GenderId = 2 Then 'Her' END AS [His-Her],
		CASE WHEN SM.StatusId = 1 Then 'is'
			ELSE 'was' END AS [is-was],
		'[AdmissionDate],[Batch],[BirthPlace],[Caste],[CertificateHeaderA4],[characterDate],[City],[CityState],[Class],[ClassOnly],[Code],[DateOfBirth],[District],[DivisionOrder],[DOBInWords],[FatherName],[Name],[Number],[OrgAddress],[OrgLogo],[OrgName],[PhoneNo],[RegistrationDetail],[ReportOrgTitle],[RollNo],[SmallAddress1],[SmallAddress2],[StudentPhoto],[SubCaste],[Taluka],[LocalName],[Kumar-Kumari],[He-She],[His-Her],[is-was]' As ColumnList
		--Mantis: 32199, add LocalName in ColumnList
	FROM
		sCertificate.tCharacter C
		INNER JOIN sStudent.tAcademic A ON C.AcademicId=A.Id
		LEFT JOIN sStudent.tStudentImages SI ON SI.StudentId=A.StudentId
		INNER JOIN sStudent.tStudentMaster SM ON SM.Id=A.StudentId
		INNER JOIN sStudent.tStudentOther SO ON SO.StudentId=A.StudentId
		INNER JOIN sSetup.tDivisionMaster DM ON A.DivisionId = DM.Id
		INNER JOIN sSetup.tClassMaster CM ON CM.Id= DM.ClassId
		INNER JOIN sSetup.tBatchMaster BM ON BM.Id=A.BatchId
		INNER JOIN sSetup.tOrganizationMaster OM ON OM.Id=A.OrgId
		LEFT JOIN sSetup.tOrgImages OI ON OI.OrgId = OM.Id
		LEFT JOIN sStudent.tStudentAdmission SA ON SA.StudentId=A.StudentId
		LEFT JOIN sStudent.tStudentFamily SF ON SF.StudentId=A.StudentId

GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO


CREATE OR ALTER VIEW [sCertificate].[vPrintTemplateFirstTrialCertificate]
AS
	SELECT
		FT.Id,
		FT.Number,
		FT.AcademicId,
		FT.FirstTrialDate,
		FT.ExamCentre,
		FT.ExamYear,
		FT.SeatNo,
		FT.Trial,
		SM.Code,
		[sStudent].[fStudentNameGet](SM.FirstName , SM.MiddleName, SM.LastName,OM.StudentNameFormatId ) AS Name,
		SM.LastName as Surname,
		SO.GenderId,
		ISNULL(OM.CityName,'') AS City,
		ISNULL(OM.StateName,'') AS CityState,
		SM.StatusId,
		OM.OrgLogo,
		OM.Title AS OrgTitle,
		OM.StreetOne+' '+OM.StreetTwo+' '+OM.StreetThree AS OrgAddress,
		OM.PhoneNo,
		A.RollNo,
		DM.OrderIndex AS DivisionOrder,
		OM.ReportOrgTitle,
		OM.SmallAddress1,
		OM.SmallAddress2,
		OM.RegistrationDetail,
		CM.DisplayTitle+' - '+DM.DisplayTitle AS Class,
		CM.DisplayTitle  AS ClassName,
		OI.CertificateHeaderA4,
		(CASE WHEN CGM.Title='10' THEN  'SSC'
		WHEN  CGM.Title='12'  THEN 'HSC'  ElSE CGM.Title + ' '+'std'
		END) AS Exam,
		FT.Remark AS Remark,
		ISNULL(SF.FatherName,'') As FatherName,
		SA.AdmissionDate,
		SO.BirthDateInWord,
		SO.DateOfBirth,
		SI.StudentPhoto,
		SM.SUID,
		A.BatchId,
		ISNULL(SM.LocalName,'') AS LocalName,	--Add for Mantis: 32199
		ISNULL(DeptM.DisplayTitle,'') AS DepartmentName, --add for mantis : 0040988
		ISNULL(FT.LastExamName,'') AS LastExamName, --add for mantis : 0041177
		ISNULL(FT.ObtainedMark,'') AS ObtainedMarkInLastExam, --add for mantis : 0041177
		CASE WHEN SO.GenderId = 1 Then 'Kumar'
			WHEN SO.GenderId = 2 Then 'Kumari' END AS [Kumar-Kumari],
		CASE WHEN SO.GenderId = 1 Then 'He'
			WHEN SO.GenderId = 2 Then 'She' END AS [He-She],
		CASE WHEN SO.GenderId = 1 Then 'His'
			WHEN SO.GenderId = 2 Then 'Her' END AS [His-Her],
		CASE WHEN SM.StatusId = 1 Then 'is'
			ELSE 'was' END AS [is-was],
		CASE WHEN SO.GenderId = 1 Then 'Mr.'
			WHEN SO.GenderId = 2 Then 'Miss' END AS [Mr.-Miss],
		CASE WHEN SO.GenderId = 1 Then 'Son'
			WHEN SO.GenderId = 2 Then 'Daughter' END AS [Son-Daughter],
		CASE WHEN SO.GenderId = 1 Then 'Him'
			WHEN SO.GenderId = 2 Then 'Her' END AS [Him-Her],
		'[AdmissionDate],[BirthDateInWord],[CertificateHeaderA4],[City],[CityState],[Class],[ClassName],[Code],[DateOfBirth],[DivisionOrder],[Exam],[ExamCentre],[ExamYear],[FatherName],[FirstTrialDate],[Name],[Number],[OrgAddress],[OrgLogo],[OrgTitle],[PhoneNo]
,[RegistrationDetail],[Remark],[ReportOrgTitle],[RollNo],[SeatNo],[SmallAddress1],[SmallAddress2],[StudentPhoto],[SUID],[Surname],[Trial],[LocalName],[DepartmentName],[LastExamName],[ObtainedMarkInLastExam],[Kumar-Kumari],[He-She],[His-Her],[is-was],[Mr.-Miss],[Son-Daughter],[Him-Her]' As ColumnList
		--Mantis: 32199, add LocalName in ColumnList  
		--Mantis: 0041177, add [LastExamName],[ObtainedMarkInLastExam] in ColumnList  
	FROM
		sCertificate.tFirstTrial FT
		INNER JOIN sStudent.tAcademic A ON FT.AcademicId=A.Id
		INNER JOIN sStudent.tStudentMaster SM ON SM.Id=A.StudentId
		INNER JOIN sSetup.tOrganizationMaster OM ON OM.Id=A.OrgId
		INNER JOIN sStudent.tStudentOther SO ON SO.StudentId=A.StudentId
		INNER JOIN sSetup.tDivisionMaster DM ON A.DivisionId = DM.Id
		INNER JOIN sSetup.tClassMaster CM ON CM.Id= DM.ClassId
		Left JOIN sSetup.tOrgImages OI ON OI.OrgId = OM.Id
		LEFT JOIN sSetup.tClassGroupMaster CGM ON CGM.Id = CM.ClassGroupId
		LEFT JOIN sStudent.tStudentFamily SF ON SF.StudentId=A.StudentId
		LEFT JOIN sStudent.tStudentAdmission SA ON SA.StudentId=A.StudentId
		LEFT JOIN sStudent.tStudentImages SI ON SI.StudentId=A.StudentId
		INNER JOIN sSetup.tDepartmentMaster DeptM ON DeptM.Id = CM.DepartmentId --add for mantis : 0040988

GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO


CREATE OR ALTER VIEW [sCertificate].[vPrintTemplateStudentBasicDetails]
AS
	SELECT
		[sStudent].[fStudentNameGet](SM.FirstName , SM.MiddleName, SM.LastName,OM.StudentNameFormatId ) AS StudentName,
		SM.Code,
		DM.Code AS ClassDivision,
		A.RollNo,
		BM.Title AS BatchName,
		SO.DateOfBirth AS DateOfBirth,
		GetDate() AS PrintDate, 	-- Change for Mantis id 29447 and also added in below row "[PrintDate]"
		'[BatchName],[ClassDivision],[Code],[DateOfBirth],[RollNo],[FirstName],[MiddleName],
		[LastName],[PreviousSchool],[Gender],[Caste],[SubCaste],[BirthPlace],[DOBInWords],[PreviousSchoolYear],
		[OldGRNo],[PreviousLCDate],[PreviousLCResult],[PreviousLCNumber],[PreviousStandard],
		[BirthTaluka],[BirthState],[BirthCountry],[BirthDistrict],[Address],[ContactNo],[Email],[Religion],[FamilyCaste],[CasteCategory],[Nationality],[MotherTongue],[BloodGroup],[AadhaarNumber],[PhysicalCategory],
		[FatherName],[FatherQualification],[FatherOccupation],[FatherContactNo],[FatherOfficeAddress],[FatherEmail],[FatherExStudent],[FatherPassingYear],[FatherIncome],
		[MotherName],[MotherQualification],[MotherOccupation],[MotherContactNo],[MotherOfficeAddress],[MotherEmail],[MotherExStudent],[MotherPassingYear],[MotherIncome],
		[AdmissionDate],[AdmissionStandard],[ClassName],[UIDNumber],[BankHolderName],[BankName],[BankBranch],[BankIFSCcode],[BankAccountNo],[StudentPhoto],[WhatsAppContactNo],[PrintDate],[SUIDNumber],[LocalName],[PENNo]' As ColumnList
		--Manits: 31034, add SUIDNumber in ColumnList
		--Mantis: 32199, add LocalName in ColumnList
		--[PENNo] For Mantis Id : 0035456
	FROM
		sStudent.tAcademic A
		INNER JOIN sStudent.tStudentMaster SM
			ON A.StudentId=SM.Id
		INNER JOIN sStudent.tStudentAdmission SA
			ON SA.StudentId=SM.Id
		INNER JOIN sSetup.tClassMaster CM
			ON CM.Id=A.ClassId
		INNER JOIN sSetup.tDivisionMaster DM
			ON DM.Id=A.DivisionId
		INNER JOIN sSetup.tBatchMaster BM ON BM.Id = A.BatchId
		INNER JOIN sStudent.tStatusMaster STM
			ON STM.Id=SM.StatusId
		INNER JOIN sSetup.tDepartmentMaster DeptM
			ON DeptM.Id=CM.DepartmentId
		INNER JOIN sSetup.tOrganizationMaster OM
			ON A.OrgId = OM.Id
		INNER JOIN [sStudent].[tStudentOther] SO
			ON SO.StudentId=SM.Id

GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO


CREATE OR ALTER VIEW [sCertificate].[vPrintTemplateStudentDetail] 
AS
	SELECT
		A.Id AS AcademicId,
		DivM.Id AS DivisionId,
		DivM.DisplayTitle AS DivisionName,
		CM.Id AS ClassId,
		CM.DisplayTitle AS ClassName,
		CM.DisplayTitle+'-'+DivM.DisplayTitle AS ClassDivision,
		DM.Id AS DepartmentId,
		DM.DisplayTitle AS DepartmentName,
		SM.FirstName AS FirstName,
		SM.MiddleName AS MiddleName,
		SM.LastName AS LastName,
		SM.Code,
		A.RollNo,
		A.BatchId,
		BM.Title AS BatchName,
		CONVERT(VARCHAR(10),SO.DateOfBirth,103) AS DateOfBirth,
		SA.PreviousSchool,
		ISNULL(GM.Title,'') AS Gender,
		SO.FamilyCast AS Caste,
		SO.FamilySubCast AS SubCaste,
		SO.BirthPlace,
		SO.BirthDateInWord AS DOBInWords,
		SA.PreviousLCDate AS PreviousLCDate,
		SA.PreviousLCResult AS PreviousLCResult,
		SA.PreviousLCNumber AS PreviousLCNumber,
		SA.AdmissionNumber AS OldGRNo,
		SA.PassingYear AS PreviousSchoolYear,
		SA.PreviousStandard AS PreviousStandard,
		SO.BirthTaluka AS BirthTaluka,
		SO.BirthState AS BirthState,
		SO.BirthCountry AS BirthCountry,
		SO.BirthDistrict AS BirthDistrict,
		ISNULL(SC.PresentStreetOne,'')+' '+ISNULL(SC.PresentStreetTwo,'')+' '+ISNULL(SC.PresentStreetThree,'')+' '+ISNULL(SC.PresentCity,'')+' '+ISNULL(SC.PresentState,'')+' '+ISNULL(SC.PresentZipCode,'') AS Address,
		SC.ParentContactNo AS ContactNo,
		SC.ParentEmail AS Email,
		ISNULL(RM.Title,'') AS Religion,
		SO.FamilyCast AS FamilyCaste,
		ISNULL(CCM.Title,'') AS CasteCategory,
		ISNULL(NM.Title,'') AS Nationality,
		ISNULL(SO.MotherTongue,'') AS MotherTongue,
		ISNULL(BGM.Title,'') As BloodGroup,
		ISNULL(SO.AdhaarNumber,'') AS AadhaarNumber,
		ISNULL(PDCM.Title,'') AS PhysicalCategory,
		SF.FatherName AS FatherName,
		SF.FatherQualification AS FatherQualification,
		SF.FatherOccupation AS FatherOccupation,
		SF.FatherContactNo AS FatherContactNo,
		SF.FatherOfficeAddress AS FatherOfficeAddress,
		SF.FatherMail AS FatherEmail,
		SF.IsFatherExStudent AS FatherExStudent,
		SF.FatherPassOutYear AS FatherPassingYear,
		SF.FatherIncome AS FatherIncome,
		SF.MotherName AS MotherName,
		SF.MotherQualification AS MotherQualification,
		SF.MotherOccupation AS MotherOccupation,
		SF.MotherContactNo AS MotherContactNo,
		SF.MotherOfficeAddress AS MotherOfficeAddress,
		SF.MotherMail AS MotherEmail,
		SF.IsMotherExStudent AS MotherExStudent,
		SF.MotherPassOutYear AS MotherPassingYear,
		SF.MotherIncome AS MotherIncome,
		SA.AdmissionDate AS AdmissionDate,
		CM1.Title AS AdmissionStandard,
		SO.UIDNumber AS UIDNumber,
		ISNULL(StuA.BankAccountHolderName,'') AS BankHolderName,
		ISNULL(StuA.BankName,'') AS BankName,
		ISNULL(StuA.BankBranch,'') AS BankBranch,
		ISNULL(StuA.BankIFSCcode,'') AS BankIFSCcode,
		ISNULL(StuA.BankAccountNumber,'') AS BankAccountNo,
		ISNULL(SI.StudentPhoto, CONVERT(VARBINARY(MAX), 0)) AS StudentPhoto,
		ISNULL(SC.WhatsAppContactNo,'') AS WhatsAppContactNo,
		GetDate() AS PrintDate, -- Change for Mantis id 29447 added "[PrintDate]"
		SM.SUId AS SUIDNumber,	--Add for Manits: 31034
		ISNULL(SM.LocalName,'') AS LocalName,	--Add for Mantis: 32199
		ISNULL(PENNumber,0) AS PENNo			-- Mantis Id : 0035456
	FROM
		sStudent.tAcademic A
		INNER JOIN sStudent.tStudentMaster SM ON SM.Id=A.StudentId
		INNER JOIN sStudent.tStudentOther SO ON SO.StudentId=SM.Id
		INNER JOIN sSetup.tClassMaster CM ON CM.Id=A.ClassId
		INNER JOIN sSetup.tDivisionMaster DivM ON DivM.Id=A.DivisionId
		INNER JOIN sSetup.tDepartmentMaster DM ON DM.Id = CM.DepartmentId
		INNER JOIN sSetup.tBatchMaster BM ON BM.Id = A.BatchId
		LEFT JOIN sStudent.tStudentAdmission SA ON SA.StudentId=A.StudentId
		LEFT JOIN sStudent.tStudentFamily SF ON SF.StudentId = A.StudentId
		LEFT JOIN sCommon.tBloodGroupMaster BGM ON BGM.Id=SO.BloodGroupId
		LEFT JOIN sCommon.tCastCategoryMaster CCM  ON CCM.Id=SO.CastCategoryId
		LEFT JoIN sCommon.tReligionMaster RM ON RM.Id=SO.ReligionId
		LEFT JOIN sCommon.tMotherTongueMaster MT ON MT.Id = SO.MotherTongueId
		LEFT JOIN sCommon.tCountryMaster NM ON NM.Id = SO.NationalityId
		LEFT JOIN sStudent.tStudentCommunication SC ON SC.StudentId = A.StudentId
		LEFT JOIN sStudent.tStudentHealth SH ON SH.StudentId = A.StudentId
		LEFT JOIN sCommon.tPhysicalDisabilityCategoryMaster PDCM ON PDCM.Id = SH.PhysicalDisabilityCategoryId
		LEFT JOIN sCommon.tGenderMaster GM ON GM.Id = SO.GenderId
		LEFT JOIN sSetup.tClassMaster CM1 ON CM1.Id=SA.AdmissionStandardId
		LEFT JOIN sStudent.tStudentAdditional StuA ON StuA.StudentId = SM.Id
		LEFT JOIN sStudent.tStudentImages SI ON SM.Id = SI.StudentId

GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO




/******************************************************************************  
 Purpose          : To get Batch Wise  Class
 Created date     : 21 Nov 2013
 Author           : Kundan Patel 
 Copyright©       : Copyright © Sapphire Software Solutions
 ******************************************************************************/  
CREATE OR ALTER VIEW [sCommunication].[vOrgwiseQueryCategory]  
  
AS  
  
SELECT 	
	  QCM.Id,
	  QCM.Title,
	  QCM.OrgGroupId,
	  QCM.IsObsolete,
	  QCO.OrgId,
	  ISNULL(QCM.AssignedToId,1) AS AssignedToId
	  	   
  FROM 
  
	sCommunication.tQueryCategoryMaster QCM
	INNER JOIN sCommunication.tQueryCategoryOrg QCO ON QCO.QueryCategoryId=QCM.Id
	--AND QCM.IsObsolete = 0

GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO










/******************************************************************************  
 Purpose          : To get Batch Wise  Class
 Created date     : 21 Nov 2013
 Author           : Kundan Patel 
 Copyright©       : Copyright © Sapphire Software Solutions
 ******************************************************************************/  
CREATE OR ALTER VIEW [sEExam].[vClassSubjectTopicType]  
  
AS  
  
SELECT 	
	  CS.Id AS ClassSubjectId,
	  SM.Id AS SubjectId,
	  --SM.Title as SubjectName,
	  TTM.IsObsolete,
	  TTM.OrderIndex,
	  TTM.Id AS TopicTypeId,
	  TTM.Title AS TopicTypeTitle,
	  TTM.OrgGroupId
  FROM 
  		sSetup.tClassSubject CS
	INNER JOIN sSetup.tSubjectMaster SM
		ON SM.Id=CS.SubjectId
	INNER JOIN sEExam.tDataTopicTypeMaster TTM
		on TTM.DataSubjectId=SM.Id

GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO





/******************************************************************************  
 Purpose          : To get Batch Wise  Class
 Created date     : 21 Nov 2013
 Author           : Kundan Patel 
 Copyright©       : Copyright © Sapphire Software Solutions
 ******************************************************************************/  
CREATE OR ALTER VIEW [sEExam].[vUserDataBank]  
  
AS  
  
SELECT 	
	  DBM.Id as Id,
	  DBM.Title,
	  DBM.OrgGroupId,
	  DBM.IsObsolete,
	  UA.UserId
	  	   
  FROM 
  
	sEExam.tDataBankMaster DBM
	INNER JOIN sSecurity.tUserAccess UA ON UA.SourceId=DBM.Id AND UA.SourceTypeId=2012

GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO



/******************************************************************************  
 Purpose          : To get Batch Wise  Class
 Created date     : 21 Nov 2013
 Author           : Kundan Patel 
 Copyright©       : Copyright © Sapphire Software Solutions
 ******************************************************************************/  
CREATE OR ALTER VIEW [sFee].[vChargeReceiptBook]  
  
AS  
  
SELECT 	
			DISTINCT(RBM.Id),
			RBM.Title,
			RBM.IsObsolete,
			FS.BatchId, 
			RBM.OrderIndex
FROM 
			sFee.tChargeReceipt FS
	INNER JOIN sFee.tChargeReceiptBookMaster RBM ON RBM.Id=FS.ChargeReceiptBookId

GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO



/******************************************************************************  
 Purpose          : To get Batch Wise  Class
 Created date     : 21 Nov 2013
 Author           : Kundan Patel 
 Copyright©       : Copyright © Sapphire Software Solutions
 ******************************************************************************/  
CREATE OR ALTER VIEW [sFee].[vFeeSetup]  
  
AS  
  
SELECT 	
			FS.Id AS FeeSetupId,
			FM.Id AS FeeId,
			FS.ClassId,
			FS.AdmissionCategoryId,
			FM.Title AS FeeName,
			FM.OrderIndex,
			FM.IsObsolete,
			FS.BatchId,
			FM.OrgGroupId,
			FS.IsVariable,
			FHM.Number
FROM 
			sFee.tFeeSetup FS
	INNER JOIN sFee.tFeeMaster FM ON FM.Id=FS.FeeId 
	INNER JOIN sFee.tFeeHeadMaster FHM ON FHM.Id=FM.FeeHeadId

GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO














/******************************************************************************  
 Purpose          : To get Batch Wise  Class
 Created date     : 21 Nov 2013
 Author           : Kundan Patel 
 Copyright©       : Copyright © Sapphire Software Solutions
 ******************************************************************************/  
CREATE OR ALTER VIEW [sFee].[vOptionalFeeHeadSetup]  
  
AS  
  
SELECT 	    DISTINCT
			FS.ClassId,
			FS.AdmissionCategoryId,			 
			FHM.OrderIndex,
			FHM.IsObsolete,
			FS.BatchId,
			FHM.OrgGroupId,
			FS.IsVariable,			
			FHM.Id AS FeeHeadId,
			FHM.Title AS FeeHeadName
			 
FROM 
			sFee.tFeeSetup FS
	INNER JOIN sFee.tFeeMaster FM ON FM.Id=FS.FeeId
	INNER JOIN sFee.tFeeHeadMaster FHM ON FM.FeeHeadId = FHM.Id
WHERE
			IsOptional=1

GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO












/******************************************************************************  
 Purpose          : To get Batch Wise  Class
 Created date     : 21 Nov 2013
 Author           : Kundan Patel 
 Copyright©       : Copyright © Sapphire Software Solutions
 ******************************************************************************/  
CREATE OR ALTER VIEW [sFee].[vOptionalFeeSetup]  
  
AS  
  
SELECT 	
			FS.Id AS FeeSetupId,
			FM.Id AS FeeId,
			FS.ClassId,
			FS.AdmissionCategoryId,
			FM.Title AS FeeName,
			FM.OrderIndex,
			FM.IsObsolete,
			FS.BatchId,
			FM.OrgGroupId,
			FS.IsVariable,
			FM.FeeHeadId
FROM 
			sFee.tFeeSetup FS
	INNER JOIN sFee.tFeeMaster FM ON FM.Id=FS.FeeId
WHERE
			IsOptional=1

GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO














/******************************************************************************  
 Purpose          : To get Batch Wise  Class
 Created date     : 21 Nov 2013
 Author           : Kundan Patel 
 Copyright©       : Copyright © Sapphire Software Solutions
 ******************************************************************************/  
CREATE OR ALTER VIEW [sFee].[vReceiptBook]  
  
AS  
  
SELECT 	
			DISTINCT(RBM.Id),
			RBM.Title,
			RBM.IsObsolete,
			FS.BatchId,
			FS.ClassId,
			FS.AdmissionCategoryId,
			RBM.OrderIndex
FROM 
			sFee.tFeeSetup FS
	INNER JOIN sFee.tReceiptBookMaster RBM ON RBM.Id=FS.ReceiptBookId

GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO















/******************************************************************************  
 Purpose          : To get Batch Wise  Class
 Created date     : 21 Nov 2013
 Author           : Kundan Patel 
 Copyright©       : Copyright © Sapphire Software Solutions
 ******************************************************************************/  
CREATE OR ALTER VIEW [sFee].[vReceiptBookBatch]  
  
AS  
  
SELECT 	
			DISTINCT(RBM.Id),
			RBM.Title,
			RBM.IsObsolete,
			FS.BatchId
		 
FROM 
			sFee.tFeeSetup FS
	INNER JOIN sFee.tReceiptBookMaster RBM ON RBM.Id=FS.ReceiptBookId

GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO












/******************************************************************************  
 Purpose          : To get Batch Wise  Class
 Created date     : 21 Nov 2013
 Author           : Kundan Patel 
 Copyright©       : Copyright © Sapphire Software Solutions
 ******************************************************************************/  
CREATE OR ALTER VIEW [sFee].[vUserChrageReceiptBook]  
  
AS  
  
  SELECT 	
	  CRBM.Id as Id,
	  CRBM.Title,
	  CRBM.OrgGroupId,
	  CRBM.IsObsolete,
	  UA.UserId,
	  CRBM.OrderIndex
	  	   
  FROM 
  
	sFee.tChargeReceiptBookMaster CRBM
	INNER JOIN sSecurity.tUserAccess UA ON UA.SourceId=CRBM.Id AND UA.SourceTypeId=2018
  
--SELECT 	
--	  DISTINCT(RBM.Id) as Id,
--	  RBM.Title,
--	  RBM.OrgGroupId,
--	  RBM.IsObsolete,
--	  UA.UserId,
--	  RBM.OrderIndex
	  	   
--  FROM 
  
--	sFee.tReceiptBookMaster RBM
--	INNER JOIN sSecurity.tUserAccess UA ON UA.SourceId=RBM.Id AND UA.SourceTypeId=2013
--	INNER JOIN sFee.tChargeCategoryMaster CCM ON CCM.ReceiptBookId=RBM.Id

GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO






/******************************************************************************  
 Purpose          : To get Batch Wise  Class
 Created date     : 21 Nov 2013
 Author           : Kundan Patel 
 Copyright©       : Copyright © Sapphire Software Solutions
 ******************************************************************************/  
CREATE OR ALTER VIEW [sFee].[vUserReceiptBook]  
  
AS  
  
SELECT 	
	  RBM.Id as Id,
	  RBM.Title,
	  RBM.OrgGroupId,
	  RBM.IsObsolete,
	  UA.UserId,
	  RBM.OrderIndex
	  	   
  FROM 
  
	sFee.tReceiptBookMaster RBM
	INNER JOIN sSecurity.tUserAccess UA ON UA.SourceId=RBM.Id AND UA.SourceTypeId=2013

GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE OR ALTER VIEW [sFixedAsset].[vExtLocationMaster]

AS
	 SELECT Id,
			Code,
			Title,
			IsObsolete,
			UpdatedUserId,
			OrgId
	   FROM sFixedAsset.tLocationMaster WHERE IsObsolete = 0

GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO




CREATE OR ALTER VIEW [sGSEB].[vClassActivity]

AS
	SELECT 
		ActivityId,
		ClassId,
		Title,
		OrderIndex 
	
	FROM sGSEB.tClassActivity CA 
	INNER JOIN sGSEB.tActivityMaster AM	ON CA.ActivityId = AM.Id 
	
	WHERE IsObsolete = 0

GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO



/******************************************************************************  
 Purpose          : To get Batch Wise  Class
 Created date     : 21 Nov 2013
 Author           : Kundan Patel 
 Copyright©       : Copyright © Sapphire Software Solutions
 ******************************************************************************/  
CREATE OR ALTER VIEW [sGSEB].[vClassSubjectHetu]  
  
AS  
  
SELECT 	DISTINCT
	  SM.Id as SubjectId,
	  CS.Id as ClassSubjectId,
	  SM.Code AS SubjectCode,
	  SM.Title as SubjectName,
	  SM.Number,
	  CS.ClassId,
	  CS.IsOptional,
	  CS.IsObsolete,
	  SM.OrderIndex,
	  CS.OrgId
	  
  FROM 
  
	sSetup.tClassSubject CS
	INNER JOIN sSetup.tSubjectMaster SM ON SM.Id=CS.SubjectId
	INNER JOIN sGSEB.tHetuMaster HM ON HM.ClassSubjectId = CS.Id

GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO







/******************************************************************************  
 Purpose          : To get Batch Wise  Class
 Created date     : 21 Nov 2013
 Author           : Kundan Patel 
 Copyright©       : Copyright © Sapphire Software Solutions
 ******************************************************************************/  
CREATE OR ALTER VIEW [sHostel].[vUserHostel]  
  
AS  
  
SELECT 	
	  HM.Id as Id,
	  HM.Title,
	  HM.OrgGroupId,
	  HM.IsObsolete,
	  UA.UserId
	  	   
  FROM 
  
	sHostel.tHostelMaster HM
	INNER JOIN sSecurity.tUserAccess UA ON UA.SourceId=HM.Id 
	AND UA.SourceTypeId=2014

GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
 
/******************************************************************************
	Purpose          : External view to get data from sHrm.tEmployeeMaster table
	Created date     : 03 Sep 2010
	Author           : Rajendra Patel
	Copyright©       : Copyright © Sapphire Software
	
	Last Change Desc : Added for jobtitle
*******************************************************************************/

CREATE OR ALTER VIEW [sHrm].[vAvailableLeaveEmployeeWise]

AS
	  SELECT 
	    EM.Id,
	    EJD.EmployeeGradeId,
	    LTS.NoOfLeave,
	    LTS.LeaveTypeId,
	    EM.OrgID,
	    LTM.IsLWP
	    
	  FROM sHrm.tEmployeeMaster EM INNER JOIN sHrm.tEmployeeJobDetail EJD 
	  ON EJD.EmployeeId = EM.Id INNER JOIN sHrm.tLeaveTypeGradeSettings LTS
	  ON LTS.EmployeeGradeId = EJD.EmployeeGradeId 
	  INNER JOIN sHrm.tLeaveTypeMaster LTM ON LTM.Id = LTS.LeaveTypeId

GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

 
/******************************************************************************
	Purpose          : External view to get data from sHrm.tEmployeeMaster table
	Created date     : 03 Sep 2010
	Author           : Rajendra Patel
	Copyright©       : Copyright © Sapphire Software
	
	Last Change Desc : Added for jobtitle
*******************************************************************************/

CREATE OR ALTER VIEW [sHrm].[vEmployeeAttendanceYearMonth]

AS
	Select distinct BatchId,ForMonth,ForYear,DateName( month , DateAdd( month ,ForMonth , -1 ) ) AS MonthName FROM 
	sHrm.tEmployeeAttendance

GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE OR ALTER VIEW [sHrm].[vEmployeeAutoSearch]  
AS  
	SELECT
		EM.Id AS EmployeeId,
		EM.FirstName +' '+EM.MiddleName +' '+EM.LastName+' '+DM.DisplayTitle+' '+EM.Code AS EmployeeData,
		EM.OrgId
	FROM 
		sHrm.tEmployeeMaster EM
		INNER JOIN sHrm.tEmployeeJobDetail EJD ON EJD.EmployeeId = EM.Id
		INNER JOIN sSetup.tDepartmentMaster DM ON DM.Id = EJD.OrganizationDepartmentId
	WHERE
		EM.StatusId = 7

GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE OR ALTER VIEW [sHrm].[vEmployeeColumnName]  
  
AS  
 
SELECT 	
		--EM.OrgId
		--,EM.Id AS EmployeeId
		OM.Title AS Organization
		,EM.Code
		,EM.FirstName
		,EM.MiddleName
		,EM.LastName
		,ISNULL(EM.FirstName, '') + ' ' + ISNULL(EM.MiddleName, '') + ' ' + ISNULL(EM.LastName, '') AS Name
		--,EM.StatusId
		,SM.Title AS StatusName
		--,EM.IsObsolete
		--,EJD.Id AS EmployeeJobDetailId
		--,EJD.EmployeeDesignationId
		,EDM.Title AS EmployeeDesignation
		--,EJD.OrganizationDepartmentId
		,ODM.Title AS OrganizationDepartment
		,EJD.DateOfJoining
		--,EJD.EPFNumber
		--,EJD.ESINumber
		--,EJD.EmployeeTypeId
		,ETM.Title AS EmployeeType
		,EGM.Title AS EmployeeGroup
		,EJD.DateOfleaving
		,EJD.ReasonOfleaving
		,EJD.Subjects
		--,ELM.StreetOne
		--,ELM.StreetTwo
		--,ELM.StreetThree
		--,ELM.CityName
		--,ELM.StateName
		--,ELM.CountryName
		--,ELM.ZipCode
		--,ELM.PhoneNo
		--,ELM.FaxNo
		--,ELM.NameOfOccupier
		--,ELM.AddressOfOccupier
		--,ELM.SmallAddress1
		--,ELM.SmallAddress2
		--,ELM.RegistrationNumber
		--,ELM.LicenceNumber
		--,ELM.EsicNumber
		--,ELM.EsicAddress1
		--,ELM.EsicAddress2
		--,ELM.NatureOfIndustry
		--,ELM.ByProduct
		--,ELM.DangerousOperation
		--,ELM.NatureOfTest
		--,ELM.NatureOfTestNonHazardous
		--,ELM.RowMaterialUsed
		--,ELM.HazardousProcess
		--,ELM.Cmo
		--,ELM.NicNumber
		--,ELM.CountryId
		--,ELM.StateId
		--,EPD.Id AS EmployeePersonalDetailId
		,EPD.UIDNumber
		,EPD.DateOfBirth
		,EPD.Qualification
		,EPD.FamilyCast
		,CCM.Title AS CastCategory
		--,EPD.GenderId
		,GM.Title AS Gender
		--,EPD.ReligionId
		,RM.Title AS Religion
		--,EPD.MaritalStatusId
		,MSM.Title AS MaritalStatus
		,EPD.IdentificationMark1
		--,EPD.BloodGroupId
		,BGM.Title AS BloodGroup,
		EC.MobileNumber,
		EC.PersonalEmail
		,EC.CompanyEmail
		,EC.PhoneNoCompany
		,EC.ExtensionNo
		,EC.PresentStreetOne
		,Ec.PresentStreetTwo
		,Ec.PresentStreetThree
		,Ec.PresentCountry
		,Ec.PresentState
		,Ec.PresentCity
		,Ec.PresentZipCode
		,Ec.PresentContactNo
		,Ec.PermanentStreetOne
		,Ec.PermanentStreetTwo
		,Ec.PermanentStreetThree
		,Ec.PermanentCountry
		,Ec.PermanentState
		,Ec.PermanentCity
		,Ec.PermanentZipCode
		,Ec.PermanentContactNo
		,Ec.EmergencyStreetOne
		,Ec.EmergencyStreetTwo
		,Ec.EmergencyStreetThree
		,Ec.EmergencyCountry
		,Ec.EmergencyState
		,Ec.EmergencyCity
		,Ec.EmergencyZipCode
		,Ec.EmergencyContactNo
		,ES.EPFNumber
		,ES.ESINumber
		,ES.AccountNumber
		,ES.BankName
		,ES.Branch
		,ES.IFSCNumber
		,ES.PanNumber
		,ES.BasicPay
		,ES.IncrementDate
		,ES.EPFJoinDate
		,ISNULL(ES.Payscale,'') AS Payscale
		,ISNULL(EPD.AdhaarNumber,'') AS AdhaarNumber
		,ISNULL(ES.RetirementDate,'') AS RetirementDate
		,EM.Number
		,ISNULL(EJD.ProbationUptoDate,'') As ProbationUptoDate
		,ISNULL(EJD.AhdocUptoDate,'') As AhdocUptoDate
		,ISNULL(EJD.PermanentDate,'') As PermanentDate
		,ISNULL(EJD.DateOfResignation,'') As DateOfResignation
		,STUFF( (SELECT DISTINCT ',' + CTM.Title FROM sHrm.tEmployeeCustomTag ECT INNER JOIN sHrm.tCustomTagMaster CTM ON ECT.CustomTagId=CTM.Id 
							WHERE ECT.EmployeeId = EM.Id FOR XML PATH ('')), 1, 1, '')  AS CustomTag
		,ES.AccountHolderName
		,ISNULL(EC.WhatsAppContactNo,'') AS WhatsAppContactNo
		,EM.PunchId AS PunchId
		,ISNULL(ShiftName,'') AS ShiftName
		,STUFF(
			COALESCE(',' + NULLIF(EC.PresentStreetOne, ''), '')  + 
			COALESCE(',' + NULLIF(EC.PresentStreetTwo, ''), '') +
			COALESCE(',' + NULLIF(EC.PresentStreetThree, ''), '') +
			COALESCE(',' + NULLIF(EC.PresentCountry, ''), '') +
			COALESCE(',' + NULLIF(EC.PresentState, ''), '') +
			COALESCE(',' + NULLIF(EC.PresentCity, ''), '') +
			COALESCE(',' + NULLIF(EC.PresentZipCode, ''), '') +
			COALESCE(',' + NULLIF(EC.PresentContactNo, ''), ''),
			1, 1, '') AS PresentAddress
		,[sCommon].[Age](EPD.DateOfBirth) AS Age	--add for mantis: 37124
FROM sHrm.tEmployeeMaster EM
		INNER JOIN sHrm.tEmployeeJobDetail EJD ON EJD.EmployeeId = EM.Id
		--LEFT JOIN sHrm.tEmployeeJobTitleMaster EJTM ON EJTM.Id=EJD.EmployeeJobTitleId
		LEFT JOIN sHrm.tEmployeeDesignationMaster EDM ON EDM.Id=EJD.EmployeeDesignationId
		INNER JOIN sSetup.tDepartmentMaster ODM ON ODM.Id = EJD.OrganizationDepartmentId
		INNER JOIN sHrm.tEmployeePersonalDetail EPD ON EPD.EmployeeId = EM.Id
		LEFT JOIN sCommon.tCastCategoryMaster CCM ON CCM.Id = EPD.CastCategoryId
		LEFT JOIN sCommon.tGenderMaster GM ON GM.Id=EPD.GenderId
		LEFT JOIN sCommon.tReligionMaster RM ON RM.Id=EPD.ReligionId
		LEFT JOIN sCommon.tMaritalStatusMaster MSM ON MSM.Id=EPD.MaritalStatusId
		LEFT JOIN sCommon.tBloodGroupMaster BGM ON BGM.Id=EPD.BloodGroupId
		--LEFT JOIN sHrm.tQualificationMaster QM ON QM.Id=EPD.HigestQualificationId
		INNER JOIN sHrm.tEmployeeTypeMaster ETM ON ETM.Id = EJD.EmployeeTypeId
		INNER JOIN sCommon.tStatusMaster SM ON SM.Id = EM.StatusId AND SM.StatusTypeId = 4
		LEFT JOIN sHrm.tEmployeeCommunications EC ON EC.EmployeeId = EM.Id
		LEFT JOIN sHrm.tEmployeeStatutoryAndOther ES ON ES.EmployeeId = EM.Id
		LEFT JOIN sHrm.tEmployeeGroupMaster EGM ON EGM.Id = EJD.EmployeeGroupId
		INNER JOIN sSetup.tOrganizationMaster OM ON EM.OrgId = OM.Id
		left JOIn [sBiometric].[tShiftMasterCloud] SMC ON SMC.ShiftCode=EM.ShiftId

GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
 


/******************************************************************************
	Purpose          : External view to get data from sHrm.tEmployeeMaster table
	Created date     : 03 Sep 2010
	Author           : Rajendra Patel
	Copyright©       : Copyright © Sapphire Software
	
	Last Change Desc : Added for jobtitle
*******************************************************************************/
CREATE OR ALTER VIEW [sHrm].[vEmployeeCommunication]
AS
SELECT        EM.Id, EM.Id AS EmployeeId, EM.Code, ISNULL(EM.FirstName, '') + ' ' + ISNULL(EM.LastName, '') AS EmployeeName, EM.FirstName, EM.MiddleName, EM.LastName, EM.OrgID, EJD.DateOfJoining, EM.StatusId, 
                         EM.CreatedUserId, EM.CreatedDateTime, EM.UpdatedUserId, EM.UpdatedDateTime, EM.IsObsolete, ISNULL(EC.PhoneNoCompany, '') AS PhoneNoCompany, ISNULL(EC.CompanyEmail, '') AS CompanyEmail, 
                         ISNULL(EC.ExtensionNo, '') AS ExtensionNo, ISNULL(ODM.Id, 0) AS DepartmentId, ISNULL(ODM.Title, '') AS DepartmentCode, ISNULL(ODM.Title, '') AS DepartmentName, ISNULL(EC.MobileNumber, '') AS MobileNumber, 
                         95 AS ParticipantSourceTypeId, EC.PersonalEmail, EPD.DateOfBirth, EJD.EmployeeGroupId, EM.Number, ISNULL(EM.IsOtherUser,0) AS IsOtherUser, ISNULL(EC.WhatsAppContactNo,'') AS WhatsAppContactNo
FROM            sHrm.tEmployeeMaster AS EM INNER JOIN
                         sHrm.tEmployeeJobDetail AS EJD ON EM.Id = EJD.EmployeeId LEFT OUTER JOIN
                         sHrm.tEmployeeCommunications AS EC ON EM.Id = EC.EmployeeId LEFT OUTER JOIN
                         sSetup.tDepartmentMaster AS ODM ON EJD.OrganizationDepartmentId = ODM.Id LEFT OUTER JOIN
                         sHrm.tEmployeePersonalDetail AS EPD ON EPD.EmployeeId = EM.Id

GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE OR ALTER VIEW [sHrm].[vEmployeeCustomSearch]
AS
SELECT        
		EM.OrgID,
		OM.DisplayTitle AS Organization, 
		EM.Id AS EmployeeId, 
		EM.Code, 
		EM.FirstName, 
		EM.MiddleName, 
		EM.LastName, 
		ISNULL(EM.FirstName, '') + ' ' + ISNULL(EM.MiddleName, '') + ' ' + ISNULL(EM.LastName, '') AS Name, 
		EM.StatusId, 
		SM.Title AS StatusName, 
		EM.IsObsolete, 
		0 AS EmployeeJobDetail, 
		EJD.EmployeeDesignationId, 
		EDM.Title AS EmployeeDesignation, 
		EJD.OrganizationDepartmentId, 
		ODM.Title AS OrganizationDepartment, 
		CONVERT(VARCHAR(10), EJD.DateOfJoining, 111) AS DateOfJoining, 
		EJD.EmployeeTypeId, 
		ETM.Title AS EmployeeType, 
		EGM.Title AS EmployeeGroup, 
		CONVERT(VARCHAR(10), EJD.DateOfleaving, 111) AS DateOfleaving, 
		EJD.ReasonOfleaving, 
		EJD.Subjects, 
		0 AS EmployeePersonalDetailId, 
		EPD.UIDNumber, 
		CONVERT(VARCHAR(10), EPD.DateOfBirth, 111) AS DateOfBirth, 
		EPD.GenderId, 
		GM.Title AS Gender, 
		EPD.ReligionId, 
		RM.Title AS Religion, 
		EPD.MaritalStatusId, 
		MSM.Title AS MaritalStatus, 
		EPD.IdentificationMark1, 
		EPD.BloodGroupId, 
		BGM.Title AS BloodGroup, 
		EPD.Qualification, 
		EPD.FamilyCast, 
		CCM.Title AS CastCategory, 
		EJD.EmployeeGroupId, 
		EC.MobileNumber, 
		EC.PersonalEmail, 
		EC.CompanyEmail, 
		EC.PhoneNoCompany, 
		EC.ExtensionNo, 
		EC.PresentStreetOne, 
		EC.PresentStreetTwo, 
		EC.PresentStreetThree, 
		EC.PresentCountry, 
		EC.PresentState, 
		EC.PresentCity, 
		EC.PresentZipCode, 
		EC.PresentContactNo, 
		EC.PermanentStreetOne, 
		EC.PermanentStreetTwo, 
		EC.PermanentStreetThree, 
		EC.PermanentCountry, 
		EC.PermanentState, 
		EC.PermanentCity, 
		EC.PermanentZipCode, 
		EC.PermanentContactNo, 
		EC.EmergencyStreetOne, 
		EC.EmergencyStreetTwo, 
		EC.EmergencyStreetThree, 
		EC.EmergencyCountry, 
		EC.EmergencyState, 
		EC.EmergencyCity, 
		EC.EmergencyZipCode, 
		EC.EmergencyContactNo, 
		ES.EPFNumber, 
		ES.ESINumber, 
		ES.AccountNumber, 
		ES.BankName, 
		ES.Branch, 
		ES.IFSCNumber, 
		ES.PanNumber, 
		ES.BasicPay, 
		CONVERT(VARCHAR(10), ES.IncrementDate, 111) AS IncrementDate, 
		CONVERT(VARCHAR(10), ES.EPFJoinDate, 111) AS EPFJoinDate, 
		ISNULL(ES.Payscale, '') AS Payscale, 
		ISNULL(EPD.AdhaarNumber, '') AS AdhaarNumber, 
		ISNULL(CONVERT(VARCHAR(50), ES.RetirementDate, 103), '') AS RetirementDate, 
		ISNULL(EM.Number, 0) AS Number, 
		CONVERT(VARCHAR(10), EJD.ProbationUptoDate, 111) AS ProbationUptoDate, 
		CONVERT(VARCHAR(10), EJD.AhdocUptoDate, 111) AS AhdocUptoDate, 
		CONVERT(VARCHAR(10), EJD.PermanentDate, 111) AS PermanentDate, 
		CONVERT(VARCHAR(10), EJD.DateOfResignation, 111) AS DateOfResignation, 
		EM.IsOtherUser,
		(SELECT STUFF((
			SELECT DISTINCT ',' + CTM.Title 
			FROM sHrm.tEmployeeCustomTag ECT 
				INNER JOIN sHrm.tCustomTagMaster CTM ON ECT.CustomTagId=CTM.Id 
			WHERE ECT.EmployeeId = EM.Id FOR XML PATH ('')), 1, 1, '')  AS CustomTag 
		FROM sHrm.tEmployeeMaster EM1 
		WHERE EM1.Id = EM.Id 
		GROUP BY EM1.Id) AS CustomTag,
		OM.OrgGroupId,
		ES.AccountHolderName,
		ISNULL(EC.WhatsAppContactNo,'') AS WhatsAppContactNo,
		EM.PunchId AS PunchId,
		ISNULL(ShiftName,'') AS ShiftName,
		STUFF(
			COALESCE(',' + NULLIF(EC.PresentStreetOne, ''), '')  + 
			COALESCE(',' + NULLIF(EC.PresentStreetTwo, ''), '') +
			COALESCE(',' + NULLIF(EC.PresentStreetThree, ''), '') +
			COALESCE(',' + NULLIF(EC.PresentCountry, ''), '') +
			COALESCE(',' + NULLIF(EC.PresentState, ''), '') +
			COALESCE(',' + NULLIF(EC.PresentCity, ''), '') +
			COALESCE(',' + NULLIF(EC.PresentZipCode, ''), '') +
			COALESCE(',' + NULLIF(EC.PresentContactNo, ''), ''),
			1, 1, '') AS PresentAddress,
		[sCommon].[Age](EPD.DateOfBirth) AS Age		--add for mantis: 37124

FROM	sHrm.tEmployeeMaster AS EM 
			INNER JOIN sHrm.tEmployeeJobDetail AS EJD ON EJD.EmployeeId = EM.Id 
			LEFT OUTER JOIN sHrm.tEmployeeDesignationMaster AS EDM ON EDM.Id = EJD.EmployeeDesignationId 
			INNER JOIN sSetup.tDepartmentMaster AS ODM ON ODM.Id = EJD.OrganizationDepartmentId 
			INNER JOIN sHrm.tEmployeePersonalDetail AS EPD ON EPD.EmployeeId = EM.Id 
			LEFT OUTER JOIN sCommon.tCastCategoryMaster AS CCM ON CCM.Id = EPD.CastCategoryId 
			LEFT OUTER JOIN sCommon.tGenderMaster AS GM ON GM.Id = EPD.GenderId 
			LEFT OUTER JOIN sCommon.tReligionMaster AS RM ON RM.Id = EPD.ReligionId 
			LEFT OUTER JOIN sCommon.tMaritalStatusMaster AS MSM ON MSM.Id = EPD.MaritalStatusId 
			LEFT OUTER JOIN sCommon.tBloodGroupMaster AS BGM ON BGM.Id = EPD.BloodGroupId 
			INNER JOIN sHrm.tEmployeeTypeMaster AS ETM ON ETM.Id = EJD.EmployeeTypeId 
			INNER JOIN sCommon.tStatusMaster AS SM ON SM.Id = EM.StatusId AND SM.StatusTypeId = 4 
			LEFT OUTER JOIN sHrm.tEmployeeCommunications AS EC ON EC.EmployeeId = EM.Id 
			LEFT OUTER JOIN sHrm.tEmployeeStatutoryAndOther AS ES ON ES.EmployeeId = EM.Id 
			LEFT OUTER JOIN sHrm.tEmployeeGroupMaster AS EGM ON EGM.Id = EJD.EmployeeGroupId 
			INNER JOIN sSetup.tOrganizationMaster AS OM ON EM.OrgID = OM.Id
			left JOIn [sBiometric].[tShiftMasterCloud] SMC ON SMC.ShiftCode=EM.ShiftId

GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/******************************************************************************    
 Purpose          : External view to get data from sHrm.tEmployeeJobDetail table    
 Created date     : 03 Sep 2010    
 Author           : Rajendra Patel   
 Copyright©       : Copyright © Sapphire Software    
 Last Change Desc : EJD Added in Select
 *******************************************************************************/
CREATE OR ALTER VIEW [sHrm].[vExtEmployee]
AS
SELECT        EM.Id AS EmployeeId, EM.Code AS EmployeeCode, ISNULL(ISNULL(EM.FirstName, ' ') + ' ' + ISNULL(EM.LastName, ' '), ' ') AS EmployeeName, EM.OrgID, EM.StatusId, EJD.EmployeeGroupId, EM.IsOtherUser, EM.FirstName, 
                         EM.MiddleName, EM.LastName
FROM            sHrm.tEmployeeMaster AS EM INNER JOIN
                         sHrm.tEmployeeJobDetail AS EJD ON EM.Id = EJD.EmployeeId

GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO


/******************************************************************************    
 Purpose          : External view to get data from sHrm.tEmployeeJobDetail table    
 Created date     : 03 Sep 2010    
 Author           : Rajendra Patel   
 Copyright©       : Copyright © Sapphire Software    
 Last Change Desc : EJD Added in Select
 *******************************************************************************/    
CREATE OR ALTER VIEW [sHrm].[vExtEmployeeJobDetail]  AS     
 
SELECT 
		0 AS Id,
		EJD.EmployeeId,
		ISNULL(ISNULL(EM.FirstName, ' ') + ' ' + ISNULL(EM.MiddleName, ' ') + ' ' + ISNULL(EM.LastName, ' '), ' ') AS EmployeeName,  
		EM.IsObsolete AS EmployeeMasterIsObsolete, 
		EJD.OrganizationDepartmentId,
		EJD.EmployeeTypeId,
		EM.Code AS EmployeeCode,   
		EJD.UpdatedUserId AS UpdatedUserId, 
		ODM.Title AS DepartmentName,  
		EDM.Id AS EmployeeDesignationId,  
		EDM.Title AS EmployeeDesignation,  
		EDM.IsObsolete AS  EmployeeDesignationMasterIsObsolete       
 FROM 
		sHrm.tEmployeeJobDetail EJD  
	INNER JOIN sHrm.tEmployeeMaster EM ON EJD.EmployeeId = EM.Id  
	INNER JOIN sSetup.tDepartmentMaster ODM ON EJD.OrganizationDepartmentId = ODM.Id 
	LEFT JOIN sHrm.tEmployeeDesignationMaster EDM ON EJD.EmployeeDesignationId = EDM.Id

GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/******************************************************************************
	Purpose          : External view to get data from sHrm.tQualificationMaster table
	Created date     : 03 Sep 2010
	Author           : Rajendra Patel
	Copyright©       : Copyright © Sapphire Software
	Last Change Desc : 
*******************************************************************************/

CREATE OR ALTER VIEW [sHrm].[vExtQualificationMaster]

AS
	 SELECT Id,
			Code,
			Title,
			IsObsolete,
			UpdatedUserId
	   FROM sHrm.tQualificationMaster

GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO




/******************************************************************************
	Purpose          : External view to get data from sHrm.tEmployeeMaster table
	Created date     : 03 Sep 2010
	Author           : Rajendra Patel
	Copyright©       : Copyright © Sapphire Software
	
	Last Change Desc : Added for jobtitle
*******************************************************************************/

CREATE OR ALTER VIEW [sHrm].[vTeacherWorkload]

AS
	 SELECT EM.Id,
	        EM.Id AS EmployeeId,
	        EM.Code,
			--ISNULL(EM.FirstName, '') + ' ' + ISNULL(EM.MiddleName, '') + ' ' + ISNULL(EM.LastName, '') EmployeeName,	 
	        ISNULL(EM.FirstName, '') + ' ' + ISNULL(EM.LastName, '') EmployeeName,
	       	EM.FirstName,
			EM.MiddleName,
			EM.LastName,	
			ISNULL(EM.ShortName,'') AS 	ShortName,	
			EM.OrgID,
			EPD.GenderId,
			EPD.DateOfBirth,
			EJD.DateOfJoining,
			EJD.DateOfleaving,
			EJD.ReasonOfleaving,
			EJD.JobRemark,
			EM.StatusId,
			EM.CreatedUserId,
			EM.CreatedDateTime,
			EM.UpdatedUserId,
			EM.UpdatedDateTime,
			EM.IsObsolete,
			ISNULL(EC.PhoneNoCompany,'') AS PhoneNoCompany,
			ISNULL(EC.CompanyEmail,'') AS CompanyEmail,
			ISNULL(EC.ExtensionNo,'') AS ExtensionNo,
			vEU.Id AS UserId,  
			ISNULL(ODM.Id,0) AS DepartmentId,  
			ISNULL(ODM.Title,'') AS DepartmentCode,  
			ISNULL(ODM.Title,'') AS DepartmentName,
		    ISNULL(EC.MobileNumber,'') AS MobileNumber,
			EJD.EmployeeTypeId,
			ISNULL(ETM.Title,'') AS EmployeeType,
			ISNULL(EDM.Id,0) AS DesignationId,
			ISNULL(EDM.Title,'') AS Designation,
			95 AS ParticipantSourceTypeId,
			DATEDIFF(yy,DateOfBirth,GETDATE()) AS Age,
			ISNULL(EGM.Title,'') AS EmployeeGroup,
			ISNULL(EGM.Id,0) AS EmployeeGroupId,
			0 AS GradeNumber,
		    ISNULL(EJD.EmployeeGradeId,0) AS EmployeeGradeId,
		    EGM.Title AS EmployeeGrade,
		    EI.EmployeePhoto,
		    ISNULL(EPD.Qualification,'') AS Qualification,
		  AT.TimeTableSchemaId
	 
	   FROM 
		   sHrm.tEmployeeMaster EM
	   INNER JOIN sHrm.tEmployeeJobDetail EJD ON EM.Id = EJD.EmployeeId 
	   INNER JOIN sHrm.tEmployeePersonalDetail EPD ON EM.Id = EPD.EmployeeId	 		     
	   LEFT JOIN sHrm.tEmployeeDesignationMaster EDM ON EDM.Id = EJD.EmployeeDesignationId	
	   LEFT JOIN sHrm.tEmployeeTypeMaster ETM ON ETM.Id = EJD.EmployeeTypeId	
	   LEFT JOIN sHrm.tEmployeeCommunications EC ON EM.Id = EC.EmployeeId 
	   LEFT JOIN sHrm.tEmployeeGroupMaster EGM ON EGM.Id=EJD.EmployeeGroupId 	  
	   LEFT JOIN sSecurity.tUser vEU ON EM.Id = vEU.SourceId AND vEU.SourceTypeId=95
	   LEFT JOIN sSetup.tDepartmentMaster ODM ON EJD.OrganizationDepartmentId = ODM.Id 
	   LEFT JOIN sHrm.tEmployeeImages EI ON EM.Id=EI.EmployeeId
	  INNER JOIN sTimetable.tAssignTeacher AT ON AT.EmployeeId=Em.Id

GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE OR ALTER VIEW [sIp].[vExtStockItemMainGroupList]
AS 
SELECT SIM.Id StockItemId,
       SIM.Code StockItemCode, 
       SIM.Title StockItemTitle,
       ISGM.Id SubGroupId,
       ISGM.Code SubGroupCode,
       ISGM.Title SubGroupTitle,
       IMGM.Id MainGroupId,
       IMGM.Code MainGroupCode,
       IMGM.Title MainGroupTitle,
       ISNULL(SIP.WEIGHTEDAVERAGECOST, '0') Wac,
       --ISNULL(POSD.UnitPrice, '0') 
       0 AS SALEPRICE,
       0 AS OrgId,
       SIM.LocationId,
       SIP.StoreLocationId
  FROM sIp.tStockItemMaster SIM
  INNER JOIN sIp.tStockItemPlanning SIP ON SIP.StockItemId=SIM.Id
INNER JOIN sIp.tItemSubGroupMaster ISGM
          ON SIM.ItemSubGroupId = ISGM.Id
INNER JOIN sIp.tItemMainGroupMaster IMGM
     ON ISGM.ItemMainGroupId = IMGM.Id 
--LEFT OUTER JOIN(SELECT A.StockItemId, A.UnitPrice, A.CreatedDateTime
--FROM sPharmacy.vExtPointOfSaleDetail  AS A 
--JOIN (SELECT StockItemId, MAX(CreatedDateTime) AS MaxCreationDate
--FROM sPharmacy.vExtPointOfSaleDetail
-- GROUP BY StockItemId) AS Pref
--           ON A.StockItemId = Pref.StockItemId AND A.CreatedDateTime = Pref.MaxCreationDate) POSD
--           ON  POSD.StockItemId = SIM.Id

GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO


CREATE OR ALTER VIEW [sIp].[vExtStockItemMaster]
AS
	SELECT	SIM.Id,
			SIM.Code, 
			SIM.Title, 
			SIM.IssueStockUomId, 
			SIM.IsBatchWiseTracking, 
            --SIM.IsDrug,
			SIM.TaxGroupId,
			SIP.StandardPrice,
			SIP.LastPurchasePrice,
			IMGM.Id AS ItemMainGroupId,
			ISGM.Id AS ItemSubGroupId,
			SIP.WeightedAverageCost,
			SIM.IsObsolete,
			IMGM.Id ItemManGroupId,
			SIM.OrgGroupId ,
			SIM.LocationId,
			SIP.StoreLocationId
			--SIM.IsFirstAdd 
		
	FROM	sIp.tStockItemMaster SIM
			 INNER JOIN sIp.tStockItemPlanning SIP 
	   ON SIP.StockItemId=SIM.Id	
			LEFT OUTER JOIN sIp.tItemSubGroupMaster ISGM
				ON SIM.ItemSubGroupId = ISGM.Id
				
			LEFT OUTER JOIN sIp.tItemMainGroupMaster IMGM
				ON ISGM.ItemMainGroupId = IMGM.Id

GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE OR ALTER VIEW [sIp].[vExtStockItemPurchaseUom]

AS
	SELECT  SIPU.Id,
			SIPU.StockItemId,
			SIPU.StockUomId,
			SIPU.ConversionFactor,
			SIPU.IsObsolete,
			SIPU.UpdatedUserId,
			SU.Title,
			SU.Code
	FROM 
			sIp.tStockItemPurchaseUom  SIPU
	INNER JOIN
			sFinanceCommon.tStockUomMaster SU
	ON
			SU.Id = SIPU.StockUomId
	WHERE SIPU.IsObsolete = 0

GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE OR ALTER VIEW [sIp].[vExtStockUtilizationItemMaster]

AS

            

 SELECT     SIM.Id,

                  SIM.Code,

                  SIM.Title,

                  SIM.IsObsolete,               

                  SGM.ItemMainGroupId AS MainGroupId,

                  SGM.Id AS SubGroupId

                           

                                    

 FROM sIp.tStockItemMaster SIM

  

  LEFT JOIN  sIp.tItemSubGroupMaster  SGM 

       ON SIM.ItemSubGroupId = SGM.Id

GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO



CREATE OR ALTER VIEW [sIp].[vExtStoreLocation]
AS
SELECT
SL.Id,
SL.Code,
SL.Title,
SL.OrgGroupId
 

  FROM sIp.tStoreLocationMaster SL
  WHERE SL.IsObsolete = 0

GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO





/******************************************************************************  
 Purpose          : To get Batch Wise  Class
 Created date     : 21 Nov 2013
 Author           : Kundan Patel 
 Copyright©       : Copyright © Sapphire Software Solutions
 ******************************************************************************/  
CREATE OR ALTER VIEW [sIp].[vUserStoreLocation]  
  
AS  
  
SELECT 	
	  SLM.Id as Id,
	  SLM.Title,
	  SLM.OrgGroupId,
	  SLM.IsObsolete,
	  UA.UserId
	  	   
  FROM 
  
	sIp.tStoreLocationMaster SLM
	INNER JOIN sSecurity.tUserAccess UA ON UA.SourceId=SLM.Id AND UA.SourceTypeId=1014

GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

/******************************************************************************  
 Purpose          : To get Batch Wise  Class
 Created date     : 21 Nov 2013
 Author           : Kundan Patel 
 Copyright©       : Copyright © Sapphire Software Solutions
 ******************************************************************************/  
CREATE OR ALTER VIEW [sLibrary].[vBookDepartment]  
  
AS  
  
SELECT 	
	  DISTINCT 
	  ISNULL(BM.BookDepartment,'') AS BookDepartment,
	  BM.LibraryId
  FROM 
	  sLibrary.tBookMaster BM

GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO








/******************************************************************************  
 Purpose          : To get Batch Wise  Class
 Created date     : 21 Nov 2013
 Author           : Kundan Patel 
 Copyright©       : Copyright © Sapphire Software Solutions
 ******************************************************************************/  
CREATE OR ALTER VIEW [sLibrary].[vOrganizationLibrary]  
  
AS  
  
SELECT 	
	  OM.Id as OrgId,
	  OM.DisplayTitle AS Organization,
	  LM.Id AS LibraryId,
	  LM.Title AS Library,
	  LM.IsObsolete,
	  OM.IsObsolete AS OrgIsObsolete
	  	   
  FROM 
	sLibrary.tLibraryMaster LM
	INNER JOIN sLibrary.tAssignOrganization AO ON AO.LibraryId=LM.Id
	INNER JOIN sSetup.tOrganizationMaster OM ON OM.Id=AO.OrgId

GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/******************************************************************************  
 Purpose          : To get Batch Wise  Class
 Created date     : 21 Nov 2013
 Author           : Kundan Patel 
 Copyright©       : Copyright © Sapphire Software Solutions
 ******************************************************************************/  
CREATE OR ALTER VIEW [sLibrary].[vUserLibrary]  
 
AS  
  
SELECT 	
	  LM.Id as Id,
	  LM.Title,
	  LM.OrgGroupId,
	  LM.IsObsolete,
	  UA.UserId
	  	   
  FROM 
  
	sLibrary.tLibraryMaster LM
	INNER JOIN sSecurity.tUserAccess UA ON UA.SourceId=LM.Id AND UA.SourceTypeId=2011

GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/******************************************************************************
	Purpose          : External view to get data from sOrderEntry.tOrderEntryBatch table
	Created date     : 11 Mar 2011
	Author           : Rajendra Patel
	Copyright©       : Copyright © Sapphire Software
	Last Change Desc : Added BatchSourceTypeId
*******************************************************************************/

CREATE OR ALTER VIEW [sOrderEntry].[vExtOrderEntryBatch]

AS
	 SELECT Id,
			OrderEntryId,
			BatchSourceId,
			BatchSourceTypeId,
			Quantity
	 FROM sOrderEntry.tOrderEntryBatch

GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO


/******************************************************************************
	Purpose          : External view to get data from sOrderEntry.tServiceItemTypeMaster table
	Created date     : 27 May 2009
	Author           : System Generated
	Copyright©       : Copyright © Sapphire Software
	Last Change Desc : 
*******************************************************************************/

CREATE OR ALTER VIEW [sOrderEntry].[vExtServiceItemTypeMaster]

AS
	 SELECT Id,
			Code,
			Title,
			IsObsolete,
			OrgGroupId
	   FROM sOrderEntry.tServiceItemTypeMaster

GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

/******************************************************************************  
 Purpose          : To get Batch Wise  Class
 Created date     : 21 Nov 2013
 Author           : Kundan Patel 
 Copyright©       : Copyright © Sapphire Software Solutions
 ******************************************************************************/  
 
CREATE OR ALTER VIEW [sPayroll].[vEmployeePayrollYear]  
  
AS  
  
	SELECT
	
		PYM.Id AS PayrollYearId,
		PYM.Code,
		PYM.Title AS PayrollYear,
		ESO.EmployeeId,
		PYM.IsActive AS IsObsolete,
		PYM.OrgGroupId
		
	FROM
	
	sPayroll.tPayrollMaster PM
	INNER JOIN sPayroll.tPayrollGroupMaster PGM
		ON PM.Id=PGM.PayrollId
	INNER JOIN sPayroll.tPayrollYearMaster PYM
		ON PYM.PayrollId=PM.Id
	INNER JOIN sHrm.tEmployeeStatutoryAndOther ESO
		ON ESO.PayrollId=PM.Id AND ESO.PayrollGroupId=PGM.Id

GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
 

CREATE OR ALTER VIEW [sPayroll].[vExtAttendance]

AS
	 SELECT 0 AS ID,
			0 AS EmployeeId,
			0 AS ForMonth,
			0 AS ForYear,
			0 AS TotalWorkingDays,
			0 AS Lwp,
			0 AS UpdatedUserId
	 --  FROM sPayroll.tAttendance

GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO


CREATE OR ALTER VIEW [sPayroll].[vExtEsiProcess]

AS
	 SELECT PayProcessElementId,
			EmployeeEsiAmount,
			EmployerEsiAmount
	   FROM sPayroll.tEsiProcess


GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

 


 

CREATE OR ALTER VIEW [sPayroll].[vExtEsiSetup]

AS
	 SELECT ID,
			EmployeeEsiPercent,
			EmployerEsiPercent,
			EsiSalaryLimit,
			RoundingTypeId,
			RoundingPrecision,
			EffectiveDate,
			CreatedUserId
	   FROM sPayroll.tEsiSetup



GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
 

CREATE OR ALTER VIEW [sPayroll].[vExtEsiSetupDetail]

AS
	 SELECT 0 as ID,
			0 as ESISetUpId,
			0 as DependentPayElementId,
			0 as IsObsolete,
			0 as UpdatedUserId
	 --  FROM sPayroll.tEsiSetupDetail
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
 

CREATE OR ALTER VIEW [sPayroll].[vExtFiscalMonthClosure]

AS
	 SELECT 0 as ID,
			0 as FiscalYearTypeId,
			0 as ClosedDate
	 --  FROM sPayroll.tFiscalMonthClosure
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
 

CREATE OR ALTER VIEW [sPayroll].[vExtFiscalYear]

AS
	 SELECT 0 as ID,
			0 as FiscalYearTypeId,
			0 as StartDate,
			0 as EndDate,
			0 as IsClosed,
			0 as UpdatedUserId
	 --  FROM sPayroll.tFiscalYear
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE OR ALTER VIEW [sPayroll].[vExtFiscalYearType]

AS
	 SELECT 0 as Id,
			0 as FiscalYearType
	 --  FROM sPayroll.tFiscalYearType

GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
 

CREATE OR ALTER VIEW [sPayroll].[vExtPaidCategory]

AS
	 SELECT Id,
			Title
	   FROM sPayroll.tPaidCategory

GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
 

CREATE OR ALTER VIEW [sPayroll].[vExtPayElementType]

AS
	 SELECT Id,
			Title
	   FROM sPayroll.tPayElementType

GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO


CREATE OR ALTER VIEW [sPayroll].[vExtPfProcess]

AS
	 SELECT  
			PayProcessElementId,
			EmployeePfAmount,
			EmployerPfAmount,
			EmployerFpsAmount
			
	   FROM sPayroll.tPfProcess


GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

 


 

CREATE OR ALTER VIEW [sPayroll].[vExtPfSetup]

AS
	 SELECT Id,
			PfSalaryLimit,
			FpsSalaryLimit,
			EmployeePfPercent,
			EmployerPfPercent,
			EmployerFpsPercent,
			AdminChargesPercent,
			EdliPercent,
			InspectionChargesPercent,
			MaximumAgeForFps,
			RoundingTypeId,
			RoundingPrecision,
			MaximumVpfPercent,
			MaximumVpfAmount
			--,
			--EffectiveDate,
			--UpdatedUserId
	   FROM sPayroll.tPfSetup



GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE OR ALTER VIEW [sPayroll].[vExtPfSetupDetail]

AS
	 SELECT 0 as ID,
			0 as PfSetupId,
			0 as DependentPayElementId,
			0 as IsObsolete,
			0 as UpdatedUserId
	 --  FROM sPayroll.tPFSetupDetail

GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
 

CREATE OR ALTER VIEW [sPayroll].[vExtRoundingType]

AS
	 SELECT Id,
			Title
	   FROM sPayroll.tRoundingType

GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO



/******************************************************************************  
 Purpose          : To get Batch Wise  Class
 Created date     : 21 Nov 2013
 Author           : Kundan Patel 
 Copyright©       : Copyright © Sapphire Software Solutions
 ******************************************************************************/  
 
CREATE OR ALTER VIEW [sPayroll].[vPayElement]  
  
AS  
  
 SELECT 	
 DISTINCT
	  PE.Id,
	  PE.Title,
	  PE.IsObsolete,
	  PE.IsEmployeeSpecific,
	  PE.IsDepend,
	  PE.SortOrder,
	  PE.PayrollId,
	  PE.PayElementTypeId
  FROM 
	sPayroll.tPayElement PE
	INNER JOIN sPayroll.tPaySetup PS ON PE.ID=PS.PayElementId

GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO


CREATE OR ALTER VIEW [sPayroll].[vPayrollColumnName]
AS
SELECT
	--EM.OrgId
	--  ,EM.Id AS EmployeeId
	EM.Code
	,EM.FirstName
	,EM.MiddleName
	,EM.LastName
	,ISNULL(EM.FirstName, '') + ' ' + ISNULL(EM.MiddleName, '') + ' ' + ISNULL(EM.LastName, '') AS Name
	--,EM.StatusId
	,SM.Title AS StatusName
	--,EM.IsObsolete
	--,EJD.Id AS EmployeeJobDetailId
	--,EJD.EmployeeDesignationId
	,EDM.Title AS EmployeeDesignation
	--,EJD.OrganizationDepartmentId
	,ODM.Title AS OrganizationDepartment
	,EJD.DateOfJoining
	--,EJD.EPFNumber
	--,EJD.ESINumber
	--,EJD.EmployeeTypeId
	,ETM.Title AS EmployeeType
	,EGM.Title AS EmployeeGroup
	,EJD.DateOfleaving
	,EJD.ReasonOfleaving
	--,ELM.StreetOne
	--,ELM.StreetTwo
	--,ELM.StreetThree
	--,ELM.CityName
	--,ELM.StateName
	--,ELM.CountryName
	--,ELM.ZipCode
	--,ELM.PhoneNo
	--,ELM.FaxNo
	--,ELM.NameOfOccupier
	--,ELM.AddressOfOccupier
	--,ELM.SmallAddress1
	--,ELM.SmallAddress2
	--,ELM.RegistrationNumber
	--,ELM.LicenceNumber
	--,ELM.EsicNumber
	--,ELM.EsicAddress1
	--,ELM.EsicAddress2
	--,ELM.NatureOfIndustry
	--,ELM.ByProduct
	--,ELM.DangerousOperation
	--,ELM.NatureOfTest
	--,ELM.NatureOfTestNonHazardous
	--,ELM.RowMaterialUsed
	--,ELM.HazardousProcess
	--,ELM.Cmo
	--,ELM.NicNumber
	--,ELM.CountryId
	--,ELM.StateId
	--,EPD.Id AS EmployeePersonalDetailId
	,EPD.UIDNumber
	,EPD.DateOfBirth
	,EPD.Qualification
	,EPD.FamilyCast
	,CCM.Title AS CasteCategory
	--,EPD.GenderId
	,GM.Title AS Gender
	--,EPD.ReligionId
	,RM.Title AS Religion
	--,EPD.MaritalStatusId
	,MSM.Title AS MaritalStatus
	,EPD.IdentificationMark1
	--,EPD.BloodGroupId
	,BGM.Title AS BloodGroup
	,EC.MobileNumber
	,EC.PersonalEmail
	,EC.CompanyEmail
	,EC.PhoneNoCompany
	,EC.ExtensionNo
	,EC.PresentStreetOne
	,Ec.PresentStreetTwo
	,Ec.PresentStreetThree
	,Ec.PresentCountry
	,Ec.PresentState
	,Ec.PresentCity
	,Ec.PresentZipCode
	,Ec.PresentContactNo
	,Ec.PermanentStreetOne
	,Ec.PermanentStreetTwo
	,Ec.PermanentStreetThree
	,Ec.PermanentCountry
	,Ec.PermanentState
	,Ec.PermanentCity
	,Ec.PermanentZipCode
	,Ec.PermanentContactNo
	,ES.EPFNumber
	,ES.ESINumber
	,ES.AccountNumber
	,ES.BankName
	,ES.Branch
	,ES.IFSCNumber
	,ES.PanNumber
	,ES.BasicPay
	,ES.IncrementDate
	,ES.EPFJoinDate
	,ISNULL(ES.Payscale, '') AS Payscale
	,'' AS SalaryMonth
	,ISNULL(EM.Number, 0) AS Number
	,0 AS NetAmount
	,CASE WHEN ISNULL(ES.PayTypeId,0) = 3 THEN	'Bank' Else PTM.Title END AS PaymentMode--Added by dhananjay for mantis : 40577

FROM sHrm.tEmployeeMaster EM
INNER JOIN sHrm.tEmployeeJobDetail EJD ON EJD.EmployeeId = EM.Id
--LEFT JOIN sHrm.tEmployeeJobTitleMaster EJTM ON EJTM.Id=EJD.EmployeeJobTitleId
LEFT JOIN sHrm.tEmployeeDesignationMaster EDM ON EDM.Id = EJD.EmployeeDesignationId
INNER JOIN sSetup.tDepartmentMaster ODM ON ODM.Id = EJD.OrganizationDepartmentId
INNER JOIN sHrm.tEmployeePersonalDetail EPD ON EPD.EmployeeId = EM.Id
LEFT JOIN sCommon.tCastCategoryMaster CCM ON CCM.Id = EPD.CastCategoryId
LEFT JOIN sCommon.tGenderMaster GM ON GM.Id = EPD.GenderId
LEFT JOIN sCommon.tReligionMaster RM ON RM.Id = EPD.ReligionId
LEFT JOIN sCommon.tMaritalStatusMaster MSM ON MSM.Id = EPD.MaritalStatusId
LEFT JOIN sCommon.tBloodGroupMaster BGM ON BGM.Id = EPD.BloodGroupId
--LEFT JOIN sHrm.tQualificationMaster QM ON QM.Id=EPD.HigestQualificationId
INNER JOIN sHrm.tEmployeeTypeMaster ETM ON ETM.Id = EJD.EmployeeTypeId
INNER JOIN sCommon.tStatusMaster SM ON SM.Id = EM.StatusId
	AND SM.StatusTypeId = 4
LEFT JOIN sHrm.tEmployeeCommunications EC ON EC.EmployeeId = EM.Id
LEFT JOIN sHrm.tEmployeeStatutoryAndOther ES ON ES.EmployeeId = EM.Id
LEFT JOIN sFinanceCommon.tPayTypeMaster PTM ON PTM.Id = ES.PayTypeId --Added by dhananjay for mantis : 40577
LEFT JOIN sHrm.tEmployeeGroupMaster EGM ON EGM.Id = EJD.EmployeeGroupId

GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE OR ALTER VIEW [sPayroll].[vPayrollCustomSearch]
AS
SELECT        EM.OrgID, EM.Id AS EmployeeId, EM.Code, EM.FirstName, EM.MiddleName, EM.LastName, ISNULL(EM.FirstName, '') + ' ' + ISNULL(EM.MiddleName, '') + ' ' + ISNULL(EM.LastName, '') AS Name, EM.StatusId, 
                         SM.Title AS StatusName, EM.IsObsolete, 0 AS EmployeeJobDetail, EJD.EmployeeDesignationId, EDM.Title AS EmployeeDesignation, EJD.OrganizationDepartmentId, ODM.Title AS OrganizationDepartment, 
                         CONVERT(VARCHAR(10), EJD.DateOfJoining, 111) AS DateOfJoining, EJD.EmployeeTypeId, ETM.Title AS EmployeeType, EGM.Title AS EmployeeGroup, CONVERT(VARCHAR(10), EJD.DateOfleaving, 111) AS DateOfleaving, 
                         EJD.ReasonOfleaving, 0 AS EmployeePersonalDetailId, EPD.UIDNumber, CONVERT(VARCHAR(10), EPD.DateOfBirth, 111) AS DateOfBirth, EPD.GenderId, GM.Title AS Gender, EPD.ReligionId, RM.Title AS Religion, 
                         EPD.MaritalStatusId, MSM.Title AS MaritalStatus, EPD.IdentificationMark1, EPD.BloodGroupId, BGM.Title AS BloodGroup, EPD.Qualification, EPD.FamilyCast, CCM.Title AS CastCategory, EJD.EmployeeGroupId, 
                         EC.MobileNumber, EC.PersonalEmail, EC.CompanyEmail, EC.PhoneNoCompany, EC.ExtensionNo, EC.PresentStreetOne, EC.PresentStreetTwo, EC.PresentStreetThree, EC.PresentCountry, EC.PresentState, EC.PresentCity, 
                         EC.PresentZipCode, EC.PresentContactNo, EC.PermanentStreetOne, EC.PermanentStreetTwo, EC.PermanentStreetThree, EC.PermanentCountry, EC.PermanentState, EC.PermanentCity, EC.PermanentZipCode, 
                         EC.PermanentContactNo, ES.EPFNumber, ES.ESINumber, ES.AccountNumber, ES.BankName, ES.Branch, ES.IFSCNumber, ES.PanNumber, ES.BasicPay, CONVERT(VARCHAR(10), ES.IncrementDate, 111) AS IncrementDate, 
                         CONVERT(VARCHAR(10), ES.EPFJoinDate, 111) AS EPFJoinDate, ISNULL(ES.Payscale, '') AS Payscale, ISNULL(ES.PayrollId, 0) AS PayrollId, OM.Title AS OrgName, ISNULL(EM.IsOtherUser, 0) AS IsOtherUser,ISNULL(EM.Number,0) AS Number,
						 CASE WHEN ISNULL(ES.PayTypeId,0) = 3 THEN	'Bank' Else PTM.Title END AS PaymentMode--Added by dhananjay for mantis : 40577
FROM            sHrm.tEmployeeMaster AS EM INNER JOIN
                         sHrm.tEmployeeJobDetail AS EJD ON EJD.EmployeeId = EM.Id LEFT OUTER JOIN
                         sHrm.tEmployeeDesignationMaster AS EDM ON EDM.Id = EJD.EmployeeDesignationId INNER JOIN
                         sSetup.tDepartmentMaster AS ODM ON ODM.Id = EJD.OrganizationDepartmentId INNER JOIN
                         sHrm.tEmployeePersonalDetail AS EPD ON EPD.EmployeeId = EM.Id LEFT OUTER JOIN
                         sCommon.tCastCategoryMaster AS CCM ON CCM.Id = EPD.CastCategoryId LEFT OUTER JOIN
                         sCommon.tGenderMaster AS GM ON GM.Id = EPD.GenderId LEFT OUTER JOIN
                         sCommon.tReligionMaster AS RM ON RM.Id = EPD.ReligionId LEFT OUTER JOIN
                         sCommon.tMaritalStatusMaster AS MSM ON MSM.Id = EPD.MaritalStatusId LEFT OUTER JOIN
                         sCommon.tBloodGroupMaster AS BGM ON BGM.Id = EPD.BloodGroupId INNER JOIN
                         sHrm.tEmployeeTypeMaster AS ETM ON ETM.Id = EJD.EmployeeTypeId INNER JOIN
                         sCommon.tStatusMaster AS SM ON SM.Id = EM.StatusId AND SM.StatusTypeId = 4 LEFT OUTER JOIN
                         sHrm.tEmployeeCommunications AS EC ON EC.EmployeeId = EM.Id LEFT OUTER JOIN
                         sHrm.tEmployeeStatutoryAndOther AS ES ON ES.EmployeeId = EM.Id LEFT OUTER JOIN
                         sHrm.tEmployeeGroupMaster AS EGM ON EGM.Id = EJD.EmployeeGroupId 
						 LEFT OUTER JOIN sFinanceCommon.tPayTypeMaster AS PTM ON PTM.Id = ES.PayTypeId --Added by dhananjay for mantis : 40577
						 INNER JOIN sSetup.tOrganizationMaster AS OM ON OM.Id = EM.OrgID

GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO




/******************************************************************************  
 Purpose          : To get Batch Wise  Class
 Created date     : 21 Nov 2013
 Author           : Kundan Patel 
 Copyright©       : Copyright © Sapphire Software Solutions
 ******************************************************************************/  
 
CREATE OR ALTER VIEW [sPayroll].[vUserPayroll]  
  
AS  
  
SELECT 	
	  PM.Id as Id,
	  PM.Title,
	  PM.OrgGroupId,
	  PM.IsActive AS IsObsolete,
	  UA.UserId
  FROM 
	sPayroll.tPayrollMaster PM
	INNER JOIN sSecurity.tUserAccess UA ON UA.SourceId=PM.Id AND UA.SourceTypeId=2019

GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE OR ALTER VIEW [sProcurement].[vExtGoodsReceiptNoteReturn]

AS
	 SELECT Id,
			GoodsReceiptNoteId,
			Number,
			DeliveryOrderNumber,
			DeliveryOrderDate,
			ReturnDate,
			Remark,
			IsObsolete,
			UpdatedUserId,
			CreatedDateTime
	   FROM sProcurement.tGoodsReceiptNoteReturn

GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE OR ALTER VIEW [sProcurement].[vExtGoodsReceiptNoteReturnBatch]

AS
	 SELECT 0 AS Id,
			0 AS GoodsReceiptNoteReturnDetailId,
			0 AS GoodsReceiptNoteBatchId,
			0 AS ReturnQuantity,
			0 AS IsObsolete,
			0 AS OrgId
	  -- FROM sProcurement.tGoodsReceiptNoteReturnBatch

GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE OR ALTER VIEW [sProcurement].[vExtGoodsReceiptNoteReturnDetail]

AS
	SELECT	GRNRD.Id,
			GRNRD.GoodsReceiptNoteReturnId,
			GRNRD.GoodsReceiptNoteDetailId,
			GRNRD.ReturnQuantity,
			GRNRD.IsObsolete,
			GRNRD.ReasonId,
			GRNR.Number,
			GRNR.Remark,
			GRNR.CreatedDateTime,
			GRNR.GoodsReceiptNoteId 
			
	FROM	sProcurement.tGoodsReceiptNoteReturnDetail GRNRD
	
			INNER JOIN sProcurement.tGoodsReceiptNoteReturn GRNR
				ON GRNRD.GoodsReceiptNoteReturnId = GRNR.Id

GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE OR ALTER VIEW [sProcurement].[vExtPurchaseOrder]
AS
(SELECT * FROM sProcurement.tPurchaseOrder)

GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE OR ALTER VIEW [sProcurement].[vExtPurchaseOrderDetail]

AS
	 SELECT Id,
			PurchaseOrderId,
			ItemSourceTypeId,
			ItemSourceId,
			OrderQuantity,
			ReceivedOrderQuantity,
			BonusQuantity,
			NetUnitPrice,
			TaxGroupId,
			TaxRate,
			TaxAmount,
			OtherTaxAmount,
			DiscountAmount,
			DiscountPercentage,
			RequestedDeliveryDate,
			IsObsolete,
			UpdatedUserId,
			StockItemPurchaseUomId,
			ReceivedBonusQuantity
	   FROM sProcurement.tPurchaseOrderDetail

GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE OR ALTER VIEW [sProcurement].[vExtPurchaseOrderForGrn]

AS

SELECT Id, Number,OrgGroupId,StoreLocationId
FROM sProcurement.tPurchaseOrder 
WHERE Id IN (SELECT PO.Id 
			   FROM sProcurement.tPurchaseOrderDetail POD 
				    INNER JOIN sProcurement.tPurchaseOrder PO 
							ON PO.Id = POD.PurchaseOrderId 
			  WHERE StatusId = 43 
				   AND ((OrderQuantity - ReceivedOrderQuantity) > 0)) 
--AND Number LIKE {0} ORDER BY Number

GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE OR ALTER VIEW [sProcurement].[vExtStockAdjustment]

AS
SELECT  

SA.Id,
SAD.PhysicalQuantity,
SAD.SystemQuantity,
SAD.StockItemId,
SA.CreatedDateTime


FROM 

sProcurement.tStockAdjustment  SA
INNER JOIN sProcurement.tStockAdjustmentDetail SAD
ON SAD.StockAdjustmentId = SA.Id

GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE OR ALTER VIEW [sProcurement].[vExtStockAdjustmentDetail]

AS
	SELECT	SAD.Id,
			SAD.StockAdjustmentId,
			SAD.StockItemId,
			SAD.StoreLocationId,
			SAD.SystemQuantity,
			SAD.Remark,
			SAD.ReasonId,
			SAD.PhysicalQuantity,
			SAD.StockBatchId,
			SA.Number
			
	FROM	sProcurement.tStockAdjustmentDetail SAD
	
			INNER JOIN sProcurement.tStockAdjustment SA
				ON SAD.StockAdjustmentId = SA.Id

GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE OR ALTER VIEW [sProcurement].[vExtStockAudit]

AS
	 SELECT Id,
			Number,
			StoreLocationId,
			Remark
	   FROM sProcurement.tStockAudit

GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE OR ALTER VIEW [sProcurement].[vExtStockBatch]

AS
	 SELECT Id,
			StoreLocationId,
			StockItemId,
			Number,
			ExpiryDate,
			AvailableQuantity,
			UpdatedUserId,
			OrgGroupId
	   FROM sProcurement.tStockBatch

GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE OR ALTER VIEW [sProcurement].[vExtStockFreezeAdjustment]

AS
	 SELECT Id,
			Number
	   FROM sProcurement.tStockFreezeAdjustment

GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE OR ALTER VIEW [sProcurement].[vExtStockFreezeAdjustmentDetail]

AS
	 SELECT Id,
			StockFreezeAdjustmentId,
			StockBatchId,
			StockItemId,
			StoreLocationId,
			SystemQuantity,
			PhysicalQuantity,
			StockVerificationApprovalId
	   FROM sProcurement.tStockFreezeAdjustmentDetail

GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE OR ALTER VIEW [sProcurement].[vExtStockIssue]

AS
	 SELECT Id,
			Number,
			IssuedByName,
			ToOrganizationDepartmentId,
			FromStoreLocationId,
			Remark,
			StockIssueRequestId,
			CreatedDateTime 
			
	   FROM sProcurement.tStockIssue

GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE OR ALTER VIEW [sProcurement].[vExtStockIssueBatch]

AS
	 SELECT Id,
			StockIssueDetailId,
			Quantity,
			StockBatchId
	   FROM sProcurement.tStockIssueBatch

GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE OR ALTER VIEW [sProcurement].[vExtStockIssueDetail]

AS
	 SELECT C.Id,
			C.StockIssueId,
			C.StockItemId,
			C.RequestedQuantity,
			C.IssuedQuantity,
			C.Remark,
			D.Number
	   FROM sProcurement.tStockIssueDetail C
	   INNER JOIN sProcurement.tStockIssue D
	   ON C.StockIssueId = D.Id

GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE OR ALTER VIEW [sProcurement].[vExtStockItemFreeze]

AS
	 SELECT Id,
			--StockItemTypeId,
			StoreLocationId,
			StatusId,
			UnfreezedBy,
			UnfreezedDateTime
	   FROM sProcurement.tStockItemFreeze

GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE OR ALTER VIEW [sProcurement].[vExtStockTransferBatch]

AS
	 SELECT Id,
			StockTransferDetailId,
			Quantity,
			FromStockBatchId,
			ToStockBatchId
	   FROM sProcurement.tStockTransferBatch

GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE OR ALTER VIEW [sProcurement].[vExtStockVerification]

AS
	 SELECT Id,
			StockItemFreezeId,
			StockBatchId,
			SystemQuantity,
			PhysicalQuantity,
			IsObsolete,
			UpdatedUserId
	   FROM sProcurement.tStockVerification

GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE OR ALTER VIEW [sProcurement].[vExtStockVerificationApproval]

AS
	 SELECT Id,
			StockVerificationId,
			ApprovedQuantity,
			UpdatedUserId
	   FROM sProcurement.tStockVerificationApproval

GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE OR ALTER VIEW [sProcurement].[vExtVendorMaster]

AS
	 SELECT VM.Id,
			VM.Code,
			VM.Title,
			VM.IsObsolete,
			VM.UpdatedUserId,
			VM.Area,
			VM.City,
			VM.ContactPersonName,
			VM.Country,
			VM.CreditDays,
			VM.CreditLimit,
			VM.Email,
			VM.FaxNumber,
		 
			VM.PhoneNumberMobile,
			VM.PhoneNumberOffice,	 
			VM.State,
			VM.StreetFour,
			VM.StreetOne,
			VM.StreetThree,
			VM.StreetTwo,
		 
			VM.VendorCategoryId,
			VM.ZipCode,
			 
			VCM.Title  AS Category,
			VCM.Id AS CategoryId
	   FROM sProcurement.tVendorMaster VM
	   INNER JOIN sProcurement.tVendorCategoryMaster VCM
	   ON VM.VendorCategoryId = VCM.Id

GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE OR ALTER VIEW [sProcurement].[vPurchaseOrderDraftPo]
AS
SELECT Id, Number, RoundedNetAmount,OrgGroupId,StoreLocationId FROM sProcurement.tPurchaseOrder 
WHERE StatusId = 42
AND IsObsolete = 0
AND Id NOT IN(SELECT ISNULL(ParentPurchaseOrderId, 0) Id 
                FROM sProcurement.tPurchaseOrder)

GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/******************************************************************************
	Purpose          : To get User Location Detail
	Created date     : 11 Aug 2012
	Author           : Rajendra Patel
	Copyright©       : Copyright © Sapphire Software
	Last Change Desc : created
*******************************************************************************/
CREATE OR ALTER VIEW [sSecurity].[vExtMenuScreen]

AS

	--SELECT	
	--		S.Id,
	--		S.Title,
	--		M.ParentMenuId,
	--		S.IsObsolete
	--FROM	 
 --        sSecurity.tMenu M INNER JOIN sSecurity.tScreen S ON S.Id = M.ScreenId and ISNULL(M.ScreenId,0) != 0


	select S.Id,
		   S.Title,
		   T.ModuleId,
		   S.IsObsolete,
		   T.ResourceTypeId
		   
		   from sSecurity.tTask T Inner JOIN sSecurity.tScreen S ON S.TaskId = T.Id 
		   INNER JOIN sSecurity.tMenu M ON M.ScreenId = S.Id AND T.ResourceTypeId IN (1,2,3)

GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/******************************************************************************
	Purpose          : External view to get data from sSecurity.tTask table
	Created date     : 08 Jun 2009
	Author           : System Generated
	Copyright©       : Copyright © Sapphire Software
	Last Change Desc : 
*******************************************************************************/

CREATE OR ALTER VIEW [sSecurity].[vExtTask]

AS
	 SELECT Id,
			Title,			 
			IsObsolete,
			ModuleId,
			AvailablePermission
	   FROM sSecurity.tTask

GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO








/******************************************************************************
	Purpose          : External view to get data from sAssetManagement.tAssetAudit table
	Created date     : 15 Jan 2012
	Author           : Rajendra Patel 
	Copyright©       : Copyright © Sapphire Software
	Last Change Desc : 
*******************************************************************************/

CREATE OR ALTER VIEW [sSetup].[vAffiliationClassGroup]

AS
	Select  

			 --DSM.Title,
			 --DSM.Id,
			 
		   ACG.AffiliationId,
		   ACG.ClassGroupId,
		   CGM.Id,
		   CGM.Title,
		   CGM.OrderIndex,
		   CGM.IsObsolete
		    
	FROM  sSetup.tAffiliationClassGroup ACG
	INNER JOIN sSetup.tClassGroupMaster CGM ON ACG.ClassGroupId=CGM.Id
	--INNER JOIN  sEExam.tDataClassGroupSubject CGS ON CGS.ClassGroupId = CGM.Id AND ACG.AffiliationId = CGS.AffiliationId
	--INNER JOIN sEExam.tDataSubjectMaster DSM ON CGS.DataSubjectId = DSM.Id

GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO




 
/******************************************************************************  
 Purpose          : To get Batch Wise  Class
 Created date     : 21 Nov 2013
 Author           : Kundan Patel 
 Copyright©       : Copyright © Sapphire Software Solutions
 ******************************************************************************/  
CREATE OR ALTER VIEW [sSetup].[vBatchClass]  
  
AS  
  
SELECT 	
	  CM.Id as ClassId,
	  BC.BatchId as BatchId,
	  CM.DisplayTitle as ClassName,
	  CM.IsObsolete,
	 Cm.ClassGroupId,
	  CM.OrderIndex,
	  CM.DepartmentId,
	  CM.OrgId,
	  BM.Title As Batch,
	  BM.IsHistory
	  	   
  FROM 
  
	sSetup.tBatchClass BC
	INNER JOIN sSetup.tClassMaster CM
		ON CM.Id=BC.ClassId 
	INNER JOIN sSetup.tBatchMaster BM
		ON BM.Id=BC.BatchId AND BM.OrgId = CM.OrgId

GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO














/******************************************************************************  
 Purpose          : To get Batch Wise  Class
 Created date     : 21 Nov 2013
 Author           : Kundan Patel 
 Copyright©       : Copyright © Sapphire Software Solutions
 ******************************************************************************/  
CREATE OR ALTER VIEW [sSetup].[vBatchClassDivision]  
  
AS  
  
SELECT 	
	  DM.Id as DivisionId,
	  ISNULL(CM.Id,0) AS ClassId,
	  BC.BatchId as BatchId,
	  CM.DisplayTitle AS ClassName,
	  DM.Title AS DivisionName,
	  ISNULL(CM.DisplayTitle,'')+'-'+ISNULL(DM.Title,'') as ClassDivisionName,
	  CM.IsObsolete,
	  CM.OrderIndex,
	  DM.OrgId,
	  DM.IsObsolete AS DivisionIsObsolete,
	  DM.OrderIndex	AS DivisionOrderIndex
  FROM 
	sSetup.tDivisionMaster DM
	INNER JOIN sSetup.tClassMaster CM ON CM.Id=DM.ClassId
	INNER JOIN sSetup.tBatchClass BC ON CM.Id=BC.ClassId

GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO











/******************************************************************************  
 Purpose          : To get Batch Wise  Class
 Created date     : 21 Nov 2013
 Author           : Kundan Patel 
 Copyright©       : Copyright © Sapphire Software Solutions
 ******************************************************************************/  
CREATE OR ALTER VIEW [sSetup].[vBatchClassDivisionWorkload]  

  

AS  
  
SELECT 	

	  DM.Id as DivisionId,
	  ISNULL(CM.Id,0) AS ClassId,
	  --BC.BatchId as BatchId,
	  CM.DisplayTitle AS ClassName,
	  DM.Title AS DivisionName,
	  ISNULL(CM.DisplayTitle,'')+'-'+ISNULL(DM.Title,'') as ClassDivisionName,
	  CM.IsObsolete,
	  CM.OrderIndex,
	  Dm.OrgId ,
	  AO.TimeTableSchemaId, 
	  CASE WHEN AO.OrgId IS NULL THEN 'False' ELSE 'True' END AS CheckOrg
  FROM 
	sSetup.tDivisionMaster DM
	INNER JOIN sSetup.tClassMaster CM ON CM.Id=DM.ClassId
	--INNER JOIN sSetup.tBatchClass BC ON CM.Id=BC.ClassId
	INNER JOIN sSetup.tOrganizationMaster OM ON OM.Id = Dm.OrgId
	INNER JOIN sTimetable.tAssignOrg AO ON AO.OrgId=OM.Id

GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

 
/******************************************************************************  
 Purpose          : To get Batch Wise  Class
 Created date     : 21 Nov 2013
 Author           : Kundan Patel 
 Copyright©       : Copyright © Sapphire Software Solutions
 ******************************************************************************/  
CREATE OR ALTER VIEW [sSetup].[vBatchCommon]  
  
AS  
  
SELECT 
	  DISTINCT
      Title AS BatchName,
      OrgGroupBatchId	
	 ---- CM.Id as ClassId,
	 ---- BC.BatchId as BatchId,
	 ---- CM.DisplayTitle as ClassName,
	 ---- CM.IsObsolete,
	 ----Cm.ClassGroupId,
	 -- CM.OrderIndex
	  	   
 FROM
  
  sSetup.tBatchMaster

GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO













/******************************************************************************  
 Purpose          : To get Batch Wise  Class
 Created date     : 21 Nov 2013
 Author           : Kundan Patel 
 Copyright©       : Copyright © Sapphire Software Solutions
 ******************************************************************************/  
CREATE OR ALTER VIEW [sSetup].[vClassDivision]  
  
AS  
  
SELECT 	
	  DM.Id as DivisionId,
	  ISNULL(CM.Id,0) AS ClassId,	  
	  CM.DisplayTitle AS ClassName,
	  DM.Title AS DivisionName,
	  --ISNULL(CM.DisplayTitle,'')+'-'+ISNULL(DM.Title,'') as ClassDivisionName,
	  DM.Code AS ClassDivisionName,
	  CM.IsObsolete,
	  CM.OrderIndex,
	  DM.OrgId,
	  DM.IsObsolete AS DivisionIsObsolete
  FROM 
	sSetup.tDivisionMaster DM
	INNER JOIN sSetup.tClassMaster CM ON CM.Id=DM.ClassId

GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO




 
/******************************************************************************  
 Purpose          : To get Department
 Created date     : 21 Nov 2013
 Author           : Kundan Patel 
 Copyright©       : Copyright © Sapphire Software Solutions
 ******************************************************************************/  
CREATE OR ALTER VIEW [sSetup].[vDepartment]  
  
AS  
  
SELECT 	
	  DM.Id,
	  DM.Title, 
	  0 AS  OrderIndex,
	  DM.OrgId,
	  DM.IsObsolete,
	  UA.UserId,
	  DM.OrderIndex AS DivisionOrderIndex	   
  FROM 
  
	    sSetup.tDepartmentMaster DM
	    INNER JOIN sSecurity.tUserAccess UA ON DM.Id = UA.SourceId AND SourceTypeId = 2017

GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO


/******************************************************************************  
 Purpose          : To get Batch Wise  Class
 Created date     : 21 Nov 2013
 Author           : Kundan Patel 
 Copyright©       : Copyright © Sapphire Software Solutions
 ******************************************************************************/  
CREATE OR ALTER VIEW [sSetup].[vDivisionEmployee]  
  
AS  
  
Select 
distinct TWL.EmployeeId,
EM.FirstName+' '+EM.LastName AS Employee,
BatchId,
DivisionId
From sTimetable.tTeacherWorkLoad TWL INNER JOIN sHrm.tEmployeeMaster EM 
ON EM.Id = TWL.EmployeeId 



GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO





/******************************************************************************  
 Purpose          : To get Batch Wise  Class
 Created date     : 21 Nov 2013
 Author           : Kundan Patel 
 Copyright©       : Copyright © Sapphire Software Solutions
 ******************************************************************************/  
CREATE OR ALTER VIEW [sSetup].[vDivisionSubject]  
  
AS  
  
Select 
distinct SubjectId,
SM.Title AS SubjectName,
BatchId,
DivisionId

from sTimetable.tTeacherWorkLoad TWL INNER JOIN sSetup.tSubjectMaster SM 
ON SM.Id = TWL.SubjectId 






GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO











/******************************************************************************  
 Purpose          : To get Batch Wise  Class
 Created date     : 21 Nov 2013
 Author           : Kundan Patel 
 Copyright©       : Copyright © Sapphire Software Solutions
 ******************************************************************************/  
CREATE OR ALTER VIEW [sSetup].[vExamSubject]  
  
AS  
  
SELECT 	
	  SM.Id AS SubjectId,
	  ED.Id AS ExamDetailId,
	  ED.ExamId,
	  SM.Title AS SubjectName,
	  SM.IsObsolete,
	  ED.DisplayOrder AS OrderIndex,
	  CS.Id AS ClassSubjectId,
	  SM.Code As SubjectCode,
	  CS.ClassId
	  	   
  FROM 
  
	sAssessment.tExamDetail ED
	INNER JOIN sSetup.tClassSubject CS ON CS.Id = ED.ClassSubjectId
	INNER JOIN sSetup.tSubjectMaster SM ON SM.Id = CS.SubjectId

GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO




/******************************************************************************
	Purpose          : External view to get data from sAssetManagement.tAssetAudit table
	Created date     : 15 Jan 2012
	Author           : Rajendra Patel 
	Copyright©       : Copyright © Sapphire Software
	Last Change Desc : 
*******************************************************************************/

CREATE OR ALTER VIEW [sSetup].[vOrgMainGroupAndOrgSubGroup]

AS
	Select  
		
		OSGM.Id,
		OMGM.Code +' - '+ OSGM.Code AS JoinGroupCode, 
		OSGM.IsObsolete,
		OSGM.OrgGroupId
		    
	FROM  sSetup.tOrgMainGroupMaster OMGM
		  INNER JOIN sSetup.tOrgSubGroupMaster OSGM 
				ON OMGM.Id=OSGM.OrgMainGroupId

GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO





/******************************************************************************  
 Purpose          : To get Batch Wise  Class
 Created date     : 21 Nov 2013
 Author           : Kundan Patel 
 Copyright©       : Copyright © Sapphire Software Solutions
 ******************************************************************************/  
CREATE OR ALTER VIEW [sSetup].[vUserOrganization]  
  
AS  
  
SELECT 	
	  OM.Id as Id,
	  OM.DisplayTitle,
	  OM.OrgGroupId,
	  OM.IsObsolete,
	  UO.UserId,
	  ISNULL(OM.OrderIndex,0) AS OrderIndex
	  	   
  FROM 
  
	sSetup.tOrganizationMaster OM
	INNER JOIN sSecurity.tUserOrganization UO ON UO.OrgId=OM.Id

GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO



















/******************************************************************************  
 Purpose          : To get Doctor details  
 Created date     : 18 Jan 2012  
 Author           : Rajendra Patel 
 Copyright©       : Copyright © Sapphire Software Solutions
 Last Change Desc : Moved EmployeeMaster from sCommon to sHrm.
*******************************************************************************/  
CREATE OR ALTER VIEW [sStudent].[vArea]  
  
AS  
  
SELECT 
	  Distinct(ISNULL(SC.PermanentStreetThree,'')) AS Area,
	  A.OrgId
	   
  FROM 
  
	sStudent.tAcademic A
	INNER JOIN sStudent.tStudentCommunication SC
		ON A.StudentId=SC.StudentId
			




















GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO




















/******************************************************************************  
 Purpose          : To get Doctor details  
 Created date     : 18 Jan 2012  
 Author           : Rajendra Patel 
 Copyright©       : Copyright © Sapphire Software Solutions
 Last Change Desc : Moved EmployeeMaster from sCommon to sHrm.
*******************************************************************************/  
CREATE OR ALTER VIEW [sStudent].[vCity]  
  
AS  
  
SELECT 
	  Distinct(ISNULL(SC.PermanentCity,'')) AS City,
	  A.OrgId
	   
  FROM 
  
	sStudent.tAcademic A
	INNER JOIN sStudent.tStudentCommunication SC
		ON A.StudentId=SC.StudentId

GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO


















/******************************************************************************  
 Purpose          : To get Doctor details  
 Created date     : 18 Jan 2012  
 Author           : Rajendra Patel 
 Copyright©       : Copyright © Sapphire Software Solutions
 Last Change Desc : Moved EmployeeMaster from sCommon to sHrm.
*******************************************************************************/  
CREATE OR ALTER VIEW [sStudent].[vOccupation]  
  
AS  
  
SELECT 
	  Distinct(ISNULL(SF.FatherOccupation,'')) AS Occupation,
	  A.OrgId
	   
  FROM 
  
	sStudent.tAcademic A
	INNER JOIN sStudent.tStudentFamily SF
		ON A.StudentId=SF.StudentId
		
		UNION
		
SELECT 
	  Distinct(ISNULL(SF.MotherOccupation,'')) AS Occupation,
	  A.OrgId
	   
  FROM 
  
	sStudent.tAcademic A
	INNER JOIN sStudent.tStudentFamily SF
		ON A.StudentId=SF.StudentId

GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO



















/******************************************************************************  
 Purpose          : To get Doctor details  
 Created date     : 18 Jan 2012  
 Author           : Rajendra Patel 
 Copyright©       : Copyright © Sapphire Software Solutions
 Last Change Desc : Moved EmployeeMaster from sCommon to sHrm.
*******************************************************************************/  
CREATE OR ALTER VIEW [sStudent].[vQualification]  
  
AS  
  
SELECT 
	  Distinct(ISNULL(SF.FatherQualification,'')) AS Qualification,
	  A.OrgId
	   
  FROM 
  
	sStudent.tAcademic A
	INNER JOIN sStudent.tStudentFamily SF
		ON A.StudentId=SF.StudentId
		
	UNION
	
SELECT 
	  Distinct(ISNULL(SF.MotherQualification,'')) AS Qualification,
	  A.OrgId
	   
  FROM 
  
	sStudent.tAcademic A
	INNER JOIN sStudent.tStudentFamily SF
		ON A.StudentId=SF.StudentId

GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE OR ALTER VIEW [sStudent].[vStudentAllDetail]  
  
AS  
  
SELECT 
	  A.Id AS AcademicId,	  
	  A.ClassId,
	  A.DivisionId,
	  A.RollNo,
	  A.OrgId,
	  A.BatchId,
	  SM.Id AS StudentId,
	  SM.Code,
	  SM.Number,
	  SM.FirstName,
	  SM.MiddleName,
	  SM.LastName,
	  [sStudent].[fStudentNameGet](SM.FirstName , SM.MiddleName, SM.LastName,OM.StudentNameFormatId ) AS Name,
	  CM.DisplayTitle+' / '+DM.DisplayTitle AS ClassDivision,
	  SM.StatusId,
	  CM.DisplayTitle AS ClassName,
	  DM.DisplayTitle AS DivisionName,
	  STM.Title AS StatusName,
	  SA.LCProgress,
	  SA.LCConduct,
	  SA.LeftDate,
	  SA.PreviousSchool,
	  SA.LeftReason,
	  SA.InactiveDate,
	  SA.AdmissionDate,
	  AdmissionStandard=(SELECT Title FROM SSetup.tClassMaster WHERE Id=SA.AdmissionStandardId),
	  LeftStandard=(SELECT Title FROM SSetup.tClassMaster WHERE Id=SA.LeftStandardId),
	  SA.LCNumber,
	  SA.LCDate,
	  RM.Title AS Religion,
	  SO.FamilyCast,
	  SO.FamilySubCast,
	  SO.BirthPlace,
	  SO.BirthTaluka,
	  SO.BirthDistrict,
	  ISNULL(SO.DateOfBirth,'0001/01/01') AS DateOfBirth,
	  SO.BirthDateInWord,
	  (SI.StudentPhoto) AS StudentPhoto,
	  (SI.FatherPhoto) AS FatherPhoto,
	  (SI.MotherPhoto) AS MotherPhoto,
	  (SI.RelativePhoto) AS RelativePhoto,
	  BGM.Title AS BloodGroup,
	  ISNULL(SC.PermanentStreetOne,'')+' '+ISNULL(SC.PresentStreetTwo,'')+' '+ISNULL(SC.PresentStreetThree,'')+' '+ISNULL(SC.PresentCity,'')+' '+ISNULL(SC.PresentState,'')+' '+ISNULL(SC.PresentZipCode,'') AS PresentAddress,
	  GM.Id AS GenderId,
	  ISNULL(GM.Title,'') AS Gender,
	  ISNULL(SC.StudentContactNo,'') AS ContactNo,
	  BM.Title AS Batch,
	  ISNULL(ACM.Title,'') AS AdmissionCategory,
	  SO.HouseId,
	  ISNULL(SC.PresentStreetone,'') AS PresentStreetone,
	  ISNULL(SC.PresentStreetTwo,'') AS PresentStreetTwo,
	  ISNULL(SC.PresentStreetThree,'') AS PresentStreetThree,
	  ISNULL(SC.PresentCity,'-') AS PresentCity,
	  ISNULL(SC.PresentState,'-') As PresentState,
	  ISNULL(SC.PresentZipCode,'-') as PresentZipCode,
	  ISNULL(SC.PresentCountry,'-') AS PresentCountry,
	  ISNULL(SC.PresentContactNo,'-') as PresentContactNo,
	  ISNULL(SC.ParentEmail,'-') AS ParentEmail,
	  ISNULL(SC.ParentContactNo,'-') AS ParentContactNo,
	  ISNULL(SC.StudentContactNo,'-') AS StudentContactNo,
	  DM.OrderIndex AS DivisionOrder,
	  SM.RegistrationNumber,
	  BM.IsActive AS IsActiveBatch,
	  CM.DepartmentId,
	  DEM.Title as DepartmentName,
	  BM.OrgGroupBatchId ,
	  ISNULL(SO.UIDNumber,'' ) AS UIDNumber,
	  ISNULL(SO.AdhaarNumber,'') AS AdhaarNumber,
	  ISNULL(SM.SUId,'') AS SUId,
	  ISNUll(SA.IsTransfer,0)AS IsTransfer,
	  ISNULL(SA.TransferFrom,'') AS TransferFrom,
	  ISNULL(SA.CertificateName,'') AS CertificateName,
	  ISNUll(SA.IsTFWS,0)AS IsTFWS,
	  ISNUll(SA.IsCertificateHolder,0)AS IsCertificateHolder,
	  ISNULL(SC.EmergencyStreetOne,'') AS EmergencyStreetOne,
	  ISNULL(SC.EmergencyStreetTwo,'') AS EmergencyStreetTwo,
	  ISNULL(SC.EmergencyStreetThree,'') AS EmergencyStreetThree,
	  ISNULL(SC.EmergencyCountry,'') AS EmergencyCountry,
	  ISNULL(SC.EmergencyState,'') As EmergencyState,
	  ISNULL(SC.EmergencyCity,'') AS EmergencyCity,
	  ISNULL(SC.EmergencyZipCode,'') as EmergencyZipCode,
	  ISNULL(SC.EmergencyContactNo,'') AS EmergencyContactNo,
	  ISNULL(CM.StreamId,0) AS StreamId,
	  Isnull(SO.CastCategoryId,0) AS CastCategoryId,
	  Isnull(SO.NationalityId,0) AS NationalityId,
	  ISNULL(SO.MotherTongue,'') As MotherTongue,
	  Isnull(SO.BirthState,'') AS BirthState,
	  ISNULL(SO.BirthCountry,'') As BirthCountry,
	  ISNULL(SC.PermanentStreetOne,'') AS  PermanentStreetOne ,
	  ISNULL(SC.PermanentStreetTwo,'')  AS  PermanentStreetTwo,
	  ISNULL(SC.PermanentStreetThree,'') AS PermanentStreetThree,
	  ISNULL(SC.PermanentCity,'') AS PermanentCity,
	  ISNULL(SC.PermanentState,'') AS PermanentState,
	  ISNULL(SC.PermanentCountry,'') AS PermanentCountry,
	  ISNULL(SC.PermanentZipCode,'') AS PermanentZipCode,
	  ISNULL(SC.PermanentContactNo,'') AS PermanentContactNo,
	  ISNULL(Sc.StudentEmail,'') AS StudentEmail,
	  ISNULL(SC.PresentDistanceFromSchool,0) AS PresentDistanceFromSchool,
	  ISNULL(NM.Nationality,'') AS Nationality,
	  ISNULL(SA.PreviousStandard,'')AS PreviousStandard,
	  A.ClassStatusId,
	  STM1.Title AS ClassStatusName,
	  ISNULL(WhatsAppContactNo, '-') AS WhatsAppContactNo,
	  SO.ReferenceRemark,
	  ISNULL(SM.StudentGroupId,0) AS StudentGroupId,  -- Add for mantis : 0035772
	  ISNULL(SM.StudentOtherGroupId,0) AS StudentOtherGroupId -- Add for mantis : 0036150

  FROM 
  
	sStudent.tAcademic A
	INNER JOIN sStudent.tStudentMaster SM ON A.StudentId=SM.Id	
	INNER JOIN sSetup.tClassMaster CM ON CM.Id=A.ClassId
	INNER JOIN sSetup.tDepartmentMaster DEM on DEM.Id=CM.DepartmentId
	INNER JOIN sSetup.tDivisionMaster DM ON DM.Id=A.DivisionId
	INNER JOIN sStudent.tStatusMaster STM ON STM.Id=SM.StatusId
	LEFT JOIN sStudent.tStudentCommunication SC ON SC.StudentId=A.StudentId
	LEFT JOIN sStudent.tStudentOther SO ON SO.StudentId=A.StudentId
	LEFT JOIN sStudent.tStudentAdmission SA ON SA.StudentId=A.StudentId
	LEFT JOIN sCommon.tReligionMaster RM ON RM.Id=SO.ReligionId
	LEFT JOIN sStudent.tStudentImages SI ON SI.StudentId=A.StudentId
	LEFT JOIN 	sCommon.tBloodGroupMaster BGM ON BGM.Id=SO.BloodGroupId
	INNER JOIN sSetup.tBatchMaster BM ON BM.Id=A.BatchId
	LEFT JOIN sCommon.tGenderMaster GM ON GM.Id=SO.GenderId
	LEFT JOIN sStudent.tAdmissionCategoryMaster ACM ON ACM.Id=A.AdmissionCategoryId
	INNER JOIN sSetup.tOrganizationMaster OM ON SM.OrgId = OM.Id
	LEFT JOIN sCommon.tCountryMaster NM ON Nm.Id=SO.NationalityId
	INNER JOIN sStudent.tStatusMaster STM1 ON STM1.Id=A.ClassStatusId

GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
  

CREATE OR ALTER VIEW [sStudent].[vStudentAutoSearch]  
AS  
	SELECT
		A.Id AS AcademicId,
		SM.FirstName +' '+SM.MiddleName +' '+SM.LastName+' '+ CM.DisplayTitle+' '+DM.DisplayTitle+' ['+SM.Code+']' AS StudentData,
		SM.FirstName +' '+SM.MiddleName +' '+SM.LastName+' '+ CM.DisplayTitle+' '+DM.DisplayTitle+' '+SM.Code+' '+ISNULL(SC.ParentContactNo,'') AS StudentDataWithMobile,
		A.OrgId,
		A.BatchId	
	FROM 
	sStudent.tAcademic A
	INNER JOIN sStudent.tStudentMaster SM ON A.StudentId=SM.Id --AND SM.StatusId=1
	INNER JOIN sSetup.tClassMaster CM ON CM.Id=A.ClassId
	INNER JOIN sSetup.tDivisionMaster DM ON DM.Id=A.DivisionId
	LEFT JOIN sStudent.tStudentCommunication SC ON SC.StudentId = A.StudentId

GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
 
 
 
CREATE OR ALTER VIEW [sStudent].[vStudentCommunication]  
  
AS  
  
SELECT 
	  A.Id AS AcademicId,	  
	  A.ClassId,
	  A.DivisionId,
	  A.RollNo,
	  A.OrgId,
	  A.BatchId,
	  SM.Id AS StudentId,
	  SM.Code,
	  SM.Number,
	  SM.FirstName,
	  SM.LastName,
	  [sStudent].[fStudentNameGet](SM.FirstName , SM.MiddleName, SM.LastName,OM.StudentNameFormatId ) AS Name,
	  CM.DepartmentId,
	  SM.StatusId,
	  CM.DisplayTitle AS ClassName,
	  DM.DisplayTitle AS DivisionName,
	  STM.Title AS StatusName,
	  ISNULL(SC.ParentContactNo,'') AS ParentContactNo,
	  ISNULL(SC.ParentEmail,'') AS ParentEmail,
	  ISNULL(SC.StudentContactNo,'') AS StudentContactNo,
	  ISNULL(SC.StudentEmail,'') AS StudentEmail,
	  SO.DateOfBirth,
	  CM.OrderIndex AS ClassOrderIndex,
	  DM.OrderIndex AS DivisionOrderIndex,
	  ISNULL(BM.IsActive,0) AS IsActiveBatch,
	  BM.OrgGroupBatchId,
	   ISNULL(SF.FatherContactNo,'') AS FatherContactNo,
	   ISNULL(SF.FatherMail,'') AS FatherMail,
	   ISNULL(SF.MotherContactNo,'') AS MotherContactNo,
	   ISNULL(SF.MotherMail,'') AS MotherMail,
	  OM.OrgGroupId,
	  ISNULL(SM.SUId,'') AS SUId,
	  --SM.AdmissionCategoryId
	  ISNULL(A.AdmissionCategoryId,0) AS AdmissionCategoryId,
	  ISNULL(BM.IsInContext,0) AS IsInContext,
	  ISNULL(DM.IsBlockAttendanceAlert,0) AS IsBlockAttendanceAlert,
	  ISNULL(SC.IsBlockAllAlert,0) AS IsBlockAllAlert,
	  ISNULL(SC.WhatsAppContactNo,'') AS WhatsAppContactNo
  FROM 
	sStudent.tAcademic A
	INNER JOIN sStudent.tStudentMaster SM
		ON A.StudentId=SM.Id	
	INNER JOIN sSetup.tClassMaster CM
		ON CM.Id=A.ClassId
	INNER JOIN sSetup.tDivisionMaster DM
		ON DM.Id=A.DivisionId
	INNER JOIN sStudent.tStatusMaster STM
		ON STM.Id=SM.StatusId	 
	LEFT JOIN sStudent.tStudentCommunication SC
		ON SC.StudentId=A.StudentId
	LEFT JOIN sStudent.tStudentFamily SF
		ON SF.StudentId=A.StudentId
	LEFT JOIN sStudent.tStudentOther SO
		ON SO.StudentId=A.StudentId
	INNER JOIN sSetup.tOrganizationMaster OM
	    ON SM.OrgId = OM.Id	
	INNER JOIN sSetup.tBatchMaster BM 
		ON BM.Id = A.BatchId

GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

/******************************************************************************  
 Purpose          : To get Doctor details  
 Created date     : 18 Jan 2012  
 Author           : Rajendra Patel 
 Copyright©       : Copyright © Sapphire Software Solutions
 Last Change Desc : Moved EmployeeMaster from sCommon to sHrm.
*******************************************************************************/  
CREATE OR ALTER VIEW [sStudent].[vStudentFull]  
  
AS  
  
SELECT 
	  A.Id AS AcademicId,	  
	  A.ClassId,
	  A.DivisionId,
	  A.RollNo,
	  A.OrgId,
	  A.BatchId,
	  SM.Id AS StudentId,
	  SM.Code,
	  ISNULL(SM.Number,0) AS Number,
	  SM.FirstName,
	  SM.MiddleName,
	  SM.LastName,
	  [sStudent].[fStudentNameGet](SM.FirstName , SM.MiddleName, SM.LastName,OM.StudentNameFormatId ) AS Name,
	  --SM.DepartmentId,
	  SM.StatusId,
	  CM.DisplayTitle AS ClassName,
	  DM.DisplayTitle AS DivisionName,
	  STM.Title AS StatusName,
	  SI.StudentPhoto,
	  CM.DepartmentId,
	  ISNULL(SM.EnrollmentStatusId,0) AS EnrollmentStatusId,
	  ISNULL(SC.ParentContactNo,'') AS ParentContactNo,
	  A.ClassStatusId,
	  STM1.Title AS ClassStatusName
  FROM 
  
	sStudent.tAcademic A
	INNER JOIN sStudent.tStudentMaster SM
		ON A.StudentId=SM.Id	
	INNER JOIN sSetup.tClassMaster CM
		ON CM.Id=A.ClassId
	INNER JOIN sSetup.tDivisionMaster DM
		ON DM.Id=A.DivisionId
	INNER JOIN sStudent.tStatusMaster STM
		ON STM.Id=SM.StatusId
	LEFT JOIN sStudent.tStudentCommunication SC 
		ON SC.StudentId=A.StudentId
	LEFT JOIN sStudent.tStudentOther SO
		ON SO.StudentId=A.StudentId
		LEFT JOIN sStudent.tStudentImages SI
		ON SI.StudentId=A.StudentId
	INNER JOIN sSetup.tOrganizationMaster OM
	    ON SM.OrgId = OM.Id	
	INNER JOIN sStudent.tStatusMaster STM1
		ON STM1.Id=A.ClassStatusId

GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

 
CREATE OR ALTER VIEW [sStudent].[vStudentLastAcademic]  
AS  
 
SELECT 
	  A.Id AS AcademicId,	  
	  A.ClassId,
	  A.DivisionId,
	  A.RollNo,
	  A.OrgId,
	  A.BatchId,
	  SM.Id AS StudentId,
	  SM.Code,
	  ISNULL(SM.Number,0) AS Number,
	  SM.RegistrationNumber,
	  SM.FirstName,
	  SM.MiddleName,
	  SM.LastName,
	   [sStudent].[fStudentNameGet](SM.FirstName , SM.MiddleName, SM.LastName,OM.StudentNameFormatId ) AS Name,
	  Convert(Nvarchar(20),A.RollNo) + '-' + SM.FirstName+' '+SM.LastName AS RollNoName,	  
	  SM.StatusId,
	  CM.DisplayTitle AS ClassName,
	  DM.DisplayTitle AS DivisionName,
	  STM.Title AS StatusName,	  
	  SM.RecordStatusId,	  
	  DM.InchargeEmployeeId, 
	  BM.OrgGroupBatchId,	  
	  --SM.AdmissionCategoryId,
	  ISNULL(A.AdmissionCategoryId,0) AS AdmissionCategoryId,
	  DM.OrderIndex DivisionOrder,
	  BM.IsActive AS IsActiveBatch,
	  BM.Title AS BatchName,
	  ISNULL(DeptM.Id,0) AS DepartmentId,
	  ISNULL(DeptM.Title,'') AS DepartmentName,
	  SM.IsBlackList,
	  SM.BlackListReason,
	  CM.OrderIndex AS ClassOrderIndex,
	  ISNULL(A.ExemptionTypeId,0) AS ExemptionTypeId,
	  ISNULL(OM.DisplayTitle,'')  AS OrganizationName,
	  OM.StreetOne,
	  OM.StreetTwo,
	  OM.StreetThree,
	  OM.ZipCode,
	  ISNULL(SM.SUId,'') AS SUId ,
	  ISNULL(OM.ReportOrgGroupTitle,'') AS ReportOrgGroupTitle,
	  ISNULL(OM.ReportOrgTitle,'') AS ReportOrgTitle,
	  A.ClassStatusId,
	  STM1.Title AS ClassStatusName
  FROM 
	(
SELECT Id 
FROM (select A.ID,-- add other columns here
             ROW_NUMBER() over (partition by studentId ORDER BY GM.OrderIndex DESC) as MAX_ID
      from sStudent.tAcademic A INNER JOIN sSetup.tClassMaster CM ON A.ClassId = CM.Id
      INNER JOIN sSetup.tClassGroupMaster GM ON CM.ClassGroupId = GM.Id ) x
WHERE MAX_ID = 1
) LA INNER JOIN sStudent.tAcademic A ON LA.Id = A.Id
	
	INNER JOIN sStudent.tStudentMaster SM
		ON A.StudentId=SM.Id	
	INNER JOIN sSetup.tClassMaster CM
		ON CM.Id=A.ClassId
	INNER JOIN sSetup.tDivisionMaster DM
		ON DM.Id=A.DivisionId
	INNER JOIN sSetup.tBatchMaster BM ON BM.Id = A.BatchId 
	INNER JOIN sStudent.tStatusMaster STM
		ON STM.Id=SM.StatusId
	INNER JOIN sSetup.tDepartmentMaster DeptM
		ON DeptM.Id=CM.DepartmentId
	INNER JOIN sSetup.tOrganizationMaster OM 
	    ON SM.OrgId = OM.Id
	INNER JOIN sStudent.tStatusMaster STM1
		ON STM1.Id=A.ClassStatusId

GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO


/******************************************************************************  
 Purpose          : To get Doctor details  
 Created date     : 18 Jan 2012  
 Author           : Rajendra Patel 
 Copyright©       : Copyright © Sapphire Software Solutions
 Last Change Desc : Moved EmployeeMaster from sCommon to sHrm.
*******************************************************************************/  
CREATE OR ALTER VIEW [sStudent].[vStudentOther]  
  
AS  
  
SELECT 
	  A.Id AS AcademicId,	  
	  A.ClassId,
	  A.DivisionId,
	  A.RollNo,
	  A.OrgId,
	  A.BatchId,
	  SM.Id AS StudentId,
	  ISNULL(SM.RecordStatusId,0) AS RecordStatusId,
	  SM.Code,
	  SM.Number,
	  SM.FirstName,
	  SM.MiddleName,
	  SM.LastName,
	    [sStudent].[fStudentNameGet](SM.FirstName , SM.MiddleName, SM.LastName,OM.StudentNameFormatId ) AS Name,
	  SM.StatusId,
	  CM.DisplayTitle AS ClassName,
	  DM.DisplayTitle AS DivisionName,
	  STM.Title AS StatusName,
	  ISNULL(SO.GenderId,0) AS GenderId,
	  ISNULL(SO.HouseId,0) AS HouseId
  FROM 
  
	sStudent.tAcademic A
	INNER JOIN sStudent.tStudentMaster SM
		ON A.StudentId=SM.Id	
	INNER JOIN sSetup.tClassMaster CM
		ON CM.Id=A.ClassId
	INNER JOIN sSetup.tDivisionMaster DM
		ON DM.Id=A.DivisionId
	INNER JOIN sStudent.tStatusMaster STM
		ON STM.Id=SM.StatusId	 
	LEFT JOIN sStudent.tStudentOther SO
		ON SO.StudentId=A.StudentId
	INNER JOIN sSetup.tOrganizationMaster OM
	    ON SM.OrgId = OM.Id

GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO


CREATE OR ALTER VIEW [sStudent].[vStudentSUID]   
AS
SELECT 
      ISNULL(SM.SUId,'') AS SUId,
      SM.Code,
      SM.Id AS StudentId, 
	  A.Id AS AcademicId,
	  A.OrgId,
	  A.BatchId,   
	  BM.OrgGroupBatchId	    
  FROM 
	sStudent.tAcademic A	
	INNER JOIN sStudent.tStudentMaster SM
		ON A.StudentId=SM.Id	
	INNER JOIN sSetup.tBatchMaster BM ON BM.Id = A.BatchId

GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
 
 
CREATE OR ALTER VIEW [sStudent].[vStudentVerification]  
  
AS  
 
SELECT 
	  A.Id AS AcademicId,	  
	  A.ClassId,
	  A.DivisionId,
	  A.RollNo,
	  A.OrgId,
	  A.BatchId,
	  SM.Id AS StudentId,
	  SM.Code,
	  ISNULL(SM.Number,0) AS Number,
	  SM.RegistrationNumber,
	  SM.FirstName,
	  SM.MiddleName,
	  SM.LastName,
	    SM.StatusId,
	  CM.DisplayTitle AS ClassName,
	  DM.DisplayTitle AS DivisionName,	   
	  SM.RecordStatusId,	  
	  DM.InchargeEmployeeId, 
	  BM.OrgGroupBatchId,	  
	  SM.AdmissionCategoryId,
	  DM.OrderIndex DivisionOrder,
	  BM.IsActive AS IsActiveBatch,
	  BM.Title AS BatchName,
	  SM.IsBlackList,
	  SM.BlackListReason,
	  CM.OrderIndex AS ClassOrderIndex,
	  ISNULL(A.ExemptionTypeId,0) AS ExemptionTypeId,
	  ISNULL(SM.SUId,'') AS SUId ,
	  SA.AdmissionDate,
	  SA.PreviousSchool,
	  SO.DateOfBirth,
	  SO.BirthPlace,
	  GEN.Title As Gender,
	  SO.FamilyCast,
	  CC.Title As CastCatagory,
	  SO.MotherTongue,
	  --NM.Title AS Nationality,
	  SF.FatherName,
	 SF.FatherContactNo,
	 sf.MotherName,
	 SF.MotherContactNo,
	 SC.PresentStreetOne,
	 SC.PresentStreetTwo,
	 SC.PresentStreetThree,
	 SC.StudentContactNo,
	 SO.UIDNumber,
	 SO.AdhaarNumber,
	 STA.BankBranch,
	 STA.BankAccountNumber,
	 STA.BankName,
	 STA.BankIFSCCode,
	Dept.ID as DepartmentId,
	  SA.ADMISSIONStandardId,
	  NM.OrgGroupId,
	   [sStudent].[fStudentNameGet](SM.FirstName , SM.MiddleName, SM.LastName,OM.StudentNameFormatId ) AS Name,
	   R.Title As Religion,
	   SC.StudentEmail,
	   CON.Nationality,
	   SO.BirthState,
	   SO.BirthCountry,
	   SO.BirthTaluka,
	   SO.BirthDistrict,
	   STA.BankAccountHolderName,	   
	   SF.MarriageAnniversaryDate
	  
  FROM 
	sStudent.tAcademic A 
	INNER JOIN sStudent.tStudentMaster SM ON A.StudentId=SM.Id
	 
	INNER JOIN sSetup.tClassMaster CM ON CM.Id=A.ClassId
	INNER JOIN sStudent.tStudentAdmission SA ON SA.StudentID=SM.Id   
	INNER JOIN sSetup.tDivisionMaster DM ON DM.Id=A.DivisionId
	INNER JOIN sSetup.tBatchMaster BM ON BM.Id = A.BatchId 
	LEFT JOIN sStudent.tStudentOther SO ON SO.StudentId=SM.Id
	INNER JOIN sCommon.tGenderMaster GEN ON GEN.Id=SO.GenderId
	LEFT JOIN sCommon.tCastCategoryMaster CC oN CC.Id=SO.CastCategoryId
	LEFT JOIN sCommon.tCountryMaster NM ON NM.Id=SO.NationalityId 
	LEFT JOIN sStudent.tStudentFamily SF ON SF.StudentId = SM.Id
	LEFT JOIN sStudent.tStudentCommunication SC ON SC.StudentId =SM.Id
	LEFT JOIN sStudent.tStudentAdditional STA ON STA.StudentId=Sm.Id
	INNER JOIN sSetup.tDepartmentMaster Dept ON Dept.Id=Cm.DepartmentId
	LEFT JOIN sCommon.tReligionMaster R ON R.id=SO.ReligionId
 INNER JOIN sSetup.tOrganizationMaster OM
	    ON SM.OrgId = OM.Id
	LEFT JOIN sCommon.tCountryMaster CON ON CON.ID = SO.NationalityID

GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/******************************************************************************
	Purpose          : External view to get data from sSystem.tTable table
	Created date     : 23 Apr 2010
	Author           : Rajendra Patel
	Copyright©       : Copyright © Sapphire Software
	Last Change Desc : Added ScreenID
*******************************************************************************/

CREATE OR ALTER VIEW [sSystem].[vExtTable]

AS
	 SELECT Id,
			TableName, 
			ScreenId
	   FROM sSystem.tTable

GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

/******************************************************************************
		Purpose          : To fetch Account Code 
		Created date     : 19 Dec 2009
		Author           :	Rajendra.j 
		Copyright©       : Copyright © Sapphire Software
		Last Change Desc : added TaxRate
*******************************************************************************/
CREATE OR ALTER VIEW [sTax].[vExtTaxGroupMaster]
AS		
		
SELECT		TGM.Id,
   			TGM.TaxTypeId,
   			TGM.Code,
   			TGM.Title,
   			TGM.PurchaseTaxAccountId,
   			TGM.SalesTaxAccountId,
   			TGM.TaxAccrualAccountId,
   			TGM.IsObsolete,
   			ISNULL(TaxRate.TaxRate, 0) AS TaxRate,
   			TaxRate.EffectiveFromDateTime,
   			IsHistory,
   			Taxrate.IsObsolete As Inactive
FROM		sTax.tTaxGroupMaster TGM
LEFT JOIN	(
			SELECT		
			DISTINCT	TRS.Id,
						TRS.TaxGroupId,
						TRS.TaxRate,
						TRS.EffectiveFromDateTime,
						IsHistory,
						IsObsolete	
			FROM		sTax.tTaxRateSetup TRS
			INNER JOIN (
						SELECT	TaxGroupId,
								MAX(EffectiveFromDateTime) AS EffectiveFromDateTime
						FROM	sTax.tTaxRateSetup 
						WHERE	IsHistory = 0 
						AND		IsObsolete = 0 
						AND		EffectiveFromDateTime <= sCommon.fGetDate()
						GROUP BY TaxGroupId
						) ActiveTaxRate
						ON ActiveTaxRate.TaxGroupId = TRS.TaxGroupId AND ActiveTaxRate.EffectiveFromDateTime = TRS.EffectiveFromDateTime 
			) TaxRate
			ON TaxRate.TaxGroupId = TGM.Id


GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO



/******************************************************************************  
 Purpose          : To get Batch Wise  Class
 Created date     : 21 Nov 2013
 Author           : Kundan Patel 
 Copyright©       : Copyright © Sapphire Software Solutions
 ******************************************************************************/  
CREATE OR ALTER VIEW [sTimetable].[vAssignOrganization]  
  
AS  
  
SELECT 
		OM.Id,
		OM.Title,
		AO.TimeTableSchemaId
FROM	sTimetable.tAssignOrg AO 
		INNER JOIN sSetup.tOrganizationMaster OM ON OM.Id= AO.OrgId
Where IsObsolete = 0

GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO



/******************************************************************************  
 Purpose          : To get Batch Wise  Class
 Created date     : 21 Nov 2013
 Author           : Kundan Patel 
 Copyright©       : Copyright © Sapphire Software Solutions
 ******************************************************************************/  
CREATE OR ALTER VIEW [sTimetable].[vClass]  
  
AS  
  
SELECT 
			CM.Id,
			OM.CODE + ' :' + CM.CODE AS CODE,
			AO.TimeTableSchemaId,
			AO.OrgId
			
FROM		sSetup.tClassMaster CM 
			INNER JOIN sTimetable.tAssignOrg AO ON AO.OrgId = CM.OrgId 
			INNER JOIN sSetup.tOrganizationMaster OM ON OM.Id= AO.OrgId

GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO





/******************************************************************************  
 Purpose          : To get Batch Wise  Class
 Created date     : 21 Nov 2013
 Author           : Kundan Patel 
 Copyright©       : Copyright © Sapphire Software Solutions
 ******************************************************************************/  
CREATE OR ALTER VIEW [sTimetable].[vDivision]  
  
AS  
  
SELECT	DM.Id,
		OM.CODE + ' :' + DM.CODE AS CODE,
		AO.TimeTableSchemaId,
		AO.OrgId,
		DM.InchargeEmployeeId,
		DM.ClassId,
		DM.OrderIndex		 
FROM	sSetup.tDivisionMaster DM 
		INNER JOIN sTimetable.tAssignOrg AO ON AO.OrgId = DM.OrgId 
		INNER JOIN sSetup.tOrganizationMaster OM ON OM.Id= AO.OrgId

GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO




/******************************************************************************  
 Purpose          : To get Batch Wise  Class
 Created date     : 21 Nov 2013
 Author           : Kundan Patel 
 Copyright©       : Copyright © Sapphire Software Solutions
 ******************************************************************************/  
CREATE OR ALTER VIEW [sTimetable].[vDivisionWorkLoad]  
  
AS  
  
SELECT DW.*,
	   SM.Title  AS SubjectName,
	   SM.Code AS Subjectcode,
	   SM.OrgId		   
FROM sTimeTable.tDivisionWorkLoad DW
	 INNER JOIN sSetup.tSubjectMaster SM ON SM.Id = DW.SubjectId

GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
 





/******************************************************************************  
 Purpose          : To get Employee For Proxy Setting 
 Created date     : 21 Nov 2013
 Author           : Kundan Patel 
 Copyright©       : Copyright © Sapphire Software Solutions
 ******************************************************************************/  
CREATE OR ALTER VIEW [sTimetable].[vEmployeeProxySetting]  
  
AS  
  
Select  
		TT.EmployeeId,
		EM.FirstName+' '+EM.LastName AS Employee,
		DivisionId,
		DayId,
		LectureNumber,
		TimeTableSchemaId
From 
		sTimetable.tTimeTable TT
	INNER JOIN sHrm.tEmployeeMaster EM ON EM.Id = TT.EmployeeId 
--WHERE 
--	EmployeeId NOT IN (Select DISTINCT EmployeeId FROM sTimetable.tProxySetting Where ProxyDate = CONVERT(DATE,GETDATE(),23))

GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO


/******************************************************************************  
 Purpose          : Time Table Subjects
 Created date     : 21 Nov 2013
 Author           : Kundan Patel 
 Copyright©       : Copyright © Sapphire Software Solutions
 ******************************************************************************/  
CREATE OR ALTER VIEW [sTimetable].[vSubject]  
  
AS  
  
SELECT 
		SM.Id,
		OM.CODE + ' :' + SM.CODE AS CODE,
		AO.TimeTableSchemaId,
		AO.OrgId,
		CS.ClassId 
FROM	sSetup.tSubjectMaster SM 
		INNER JOIN sSetup.tClassSubject CS ON CS.SubjectId = SM.Id
		INNER JOIN sTimetable.tAssignOrg AO ON AO.OrgId = SM.OrgId 
		INNER JOIN sSetup.tOrganizationMaster OM ON OM.Id= AO.OrgId

GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO



/******************************************************************************  
 Purpose          : Time Table Subjects
 Created date     : 21 Nov 2013
 Author           : Kundan Patel 
 Copyright©       : Copyright © Sapphire Software Solutions
 ******************************************************************************/  
CREATE OR ALTER VIEW [sTimetable].[vTeacher]  
  
AS  
  
SELECT 
		EM.Id,
		OM.CODE + ' :' + EM.CODE AS CODE,
		EM.ShortName,
		ISNULL(EM.FirstName,'') + ' '  + ISNULL(EM.MiddleName,'') + ' ' + ISNULL(EM.LastName,'') as TeacherName,
		AO.TimeTableSchemaId,
		AO.OrgId
		--CS.ClassId 
FROM	shrm.tEmployeeMaster EM 
		INNER JOIN sTimetable.tAssignOrg AO ON AO.OrgId = EM.OrgId 	
		INNER JOIN sSetup.tOrganizationMaster OM ON OM.Id= EM.OrgId

GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO









/******************************************************************************  
 Purpose          : To get Batch Wise  Class
 Created date     : 21 Nov 2013
 Author           : Kundan Patel 
 Copyright©       : Copyright © Sapphire Software Solutions
 ******************************************************************************/  
CREATE OR ALTER VIEW [sTimetable].[vTeacherWorkLoad]  
  
AS  
  
SELECT	TWL.*,
		DM.CODE DivisionCode,
		CM.Id AS ClassId,		
		CM.Title AS ClassName,
		DM.Title AS DivisionName,
		EM.ShortName + ' - ' + SM.Code AS Code,		
		EM.FirstName + ' ' + EM.MiddleName + ' ' + EM.LastName AS EmployeeName,
		SM.Title As SubjectName,
		EM.OrgID,
	    TWL.NOOFLECTURE -(SELECT COUNT(*) FROM sTimetable.tTimeTable TT WHERE
		TT.SubjectId=TWL.SubjectId AND TT.EmployeeId=TWL.EmployeeId 
		AND TT.DivisionId=TWL.DivisionId) AS RemainLecture
FROM sTimeTable.tTeacherWorkLoad TWL
 INNER JOIN sHRM.tEmployeeMaster EM ON EM.Id = TWL.EmployeeId
 INNER JOIN sSetup.tDivisionMaster DM ON DM.Id = TWL.DivisionId
 INNER JOIN sSetup.tClassMaster CM ON CM.Id = DM.ClassId
 INNER JOIN sSetup.tSubjectMaster SM ON SM.Id = TWL.SubjectId

GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO








/******************************************************************************  
 Purpose          : To get Batch Wise  Class
 Created date     : 21 Nov 2013
 Author           : Kundan Patel 
 Copyright©       : Copyright © Sapphire Software Solutions
 ******************************************************************************/  
CREATE OR ALTER VIEW [sTimetable].[vUserTimeTableSchema]  
  
AS  
  
SELECT 	
	  TS.Id as Id,
	  TS.Title,
	  TS.OrgGroupBatchId,
	  TS.IsObsolete,
	  UA.UserId
	  	   
  FROM 
  
	sTimetable.tTimeTableSchema TS
	INNER JOIN sSecurity.tUserAccess UA ON UA.SourceId=TS.Id 
	AND UA.SourceTypeId=2023

GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO


/******************************************************************************  
 Purpose          : To get Batch Wise  Class
 Created date     : 21 Nov 2013
 Author           : Kundan Patel 
 Copyright©       : Copyright © Sapphire Software Solutions
 ******************************************************************************/  
CREATE OR ALTER VIEW [sTransport].[vUserTransport]  
  
AS  
  
SELECT 	
	  TM.Id as Id,
	  TM.Title,
	  TM.OrgGroupId,
	  TM.IsObsolete,
	  UA.UserId	  	   
  FROM 
	sTransport.tTransportMaster TM
	INNER JOIN sSecurity.tUserAccess UA ON UA.SourceId=TM.Id 
	AND UA.SourceTypeId=2022

GO
