|
|
发表于 2023-12-26 10:55:11
|
显示全部楼层
/****** Object: View [dbo].[hr_v_HR31] Script Date: 12/26/2023 10:50:21 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
create view [dbo].[hr_v_HR31] as select hr_hi_Person.dEnterDate,hr_hi_Person.dJoinworkDate,hr_hi_Person.dRegularDate,HR_HI_PERSON.dEnterUnitDate,hr_hi_Person.rPerResidence,hr_hi_Person.rNativePlace,hr_hi_Person.rMarriStatus,hr_hi_Person.rNational,hr_hi_Person.vSSNo,HR_HI_PERSON.rCountry,HR_HI_PERSON.dLeaveDate,HR_HI_PERSON.rFigure,hr_hi_Person.rhealthStatus,HR_HI_PERSON.rWorkStatus,hr_hi_Person.vAliaName,HR_HI_PERSON.bProbation,hr_hi_Person.rCheckInFlag,HR_HI_PERSON.EmploymentForm,HR_HI_PERSON.SysAge,HR_HI_PERSON.SysCompAge,HR_HI_PERSON.bPsnPerson, convert(char,convert(money,hr_hi_person.hrts),2) as hrts,HR_HI_PERSON.SysWorkAge,hr_hi_Person.cpsnBankCode,HR_HI_PERSON.CRegion,HR_HI_PERSON.NatrueType,HR_HI_PERSON.KeyPerson,hr_hi_Person.cPsn_Num,hr_hi_Person.cPsn_Name,HR_HI_PERSON.cPsn_NameEN,HR_HI_PERSON.JobNumber,HR_HI_PERSON.cLeaveReson,hr_hi_Person.rPersonType,HR_HI_PERSON.rEmployState,hr_hi_Person.vIDNo,hr_hi_person.vCardNo,hr_hi_Person.rSex,hr_hi_edu.reducation,hr_hi_jobinfo.cDutycode,hr_hi_jobinfo.cjobcode,hr_hi_person.cdutyclass,Person.cDepCode,hr_hi_Person.cDept_num,hr_hi_Person.dBirthDate,Person.dPValidDate,Person.dPInValidDate,hr_hi_Person.cpsnAccount,Person.fCreditQuantity,Person.iCreDate,Person.cCreGrade,hr_hi_Person.iRecordID,hr_hi_Person.cPsnEmail,hr_hi_Person.cPsnFAddr,hr_hi_Person.cPsnFPhone,hr_hi_Person.cPsnInPhone,hr_hi_Person.cPsnMobilePhone,hr_hi_Person.cPsnOPhone,hr_hi_Person.cPsnOSeat,hr_hi_Person.cPsnPostAddr,hr_hi_Person.cPsnPostCode,hr_hi_Person.cPsnProperty,hr_hi_Person.cPsnQQCode,hr_hi_Person.CardEffectiveBeginTime,hr_hi_Person.CardEffectiveEndTime,hr_hi_Person.PersonDepart,hr_hi_Person.cPsnURL,hr_hi_Person.btaketm from hr_hi_Person left JOIN person ON hr_hi_Person.cPsn_Num = person.cpersoncode left join hr_hi_jobinfo on hr_hi_jobinfo.cpsn_num=hr_hi_person.cpsn_num and blastflag=1 and bpartjob=0 left join hr_hi_edu on hr_hi_person.cpsn_num=hr_hi_edu.cpsn_num and hr_hi_edu.blastflag=1 and hr_hi_edu.irecordid=(select isnull(max(hhe.iRecordID),0) from hr_hi_edu hhe where hhe.cpsn_num=hr_hi_person.cpsn_num and hhe.blastflag=1)
GO
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
/****** Object: View [dbo].[hr_v_HR31_show] Script Date: 12/26/2023 10:50:37 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
create view [dbo].[hr_v_HR31_show] as select hr_hi_Person.iRecordID,hr_hi_Person.cPsn_Num,hr_hi_Person.cPsnEmail,hr_hi_Person.cPsnFAddr,hr_hi_Person.cPsnFPhone,hr_hi_Person.cPsnInPhone,hr_hi_Person.cPsnMobilePhone,hr_hi_Person.cPsnOPhone,hr_hi_Person.cPsnOSeat,hr_hi_Person.cPsnPostAddr,hr_hi_Person.cPsnPostCode,hr_hi_Person.cPsnProperty,hr_hi_Person.cPsnQQCode,hr_hi_Person.cPsnURL, convert(char(10),hr_hi_Person.dJoinworkDate,121) dJoinworkDate, convert(char(10),hr_hi_Person.dEnterDate,121) dEnterDate, convert(char(10),HR_HI_PERSON.dEnterUnitDate,121) dEnterUnitDate, convert(char(10),hr_hi_Person.dRegularDate,121) dRegularDate,hr_hi_person.vCardNo,hr_hi_Person.vAliaName,hr_hi_Person.vSSNo, case HR_HI_PERSON.bProbation when '1' then dbo.UDF_GetResourceMessageByID('U8.HR.HM.hr_HRVouchBus.00001') else dbo.UDF_GetResourceMessageByID('U8.HR.HM.hr_HRVouchBus.00002') end as bProbation, convert(char,convert(money,hr_hi_person.hrts),2) as 'hrts',hr_hi_Person.cPsn_Name,hr_hi_Person.cDept_num, convert(char(10),hr_hi_Person.dBirthDate,121) dBirthDate,hr_hi_Person.vIDNo,hr_hi_Person.cpsnAccount,Person.cDepCode,Person.fCreditQuantity,Person.iCreDate,Person.cCreGrade, convert(char(10),Person.dPValidDate,121) dPValidDate, convert(char(10),Person.dPInValidDate,121) dPInValidDate, case HR_HI_PERSON.bPsnPerson when '1' then dbo.UDF_GetResourceMessageByID('U8.HR.HM.hr_HRVouchBus.00001') else dbo.UDF_GetResourceMessageByID('U8.HR.HM.hr_HRVouchBus.00002') end as bPsnPerson, convert(char(10),HR_HI_PERSON.dLeaveDate,121) dLeaveDate,HR_HI_PERSON.cPsn_NameEN,HR_HI_PERSON.SysAge,HR_HI_PERSON.SysCompAge,HR_HI_PERSON.SysWorkAge,HR_HI_PERSON.JobNumber, convert(char(10),hr_hi_Person.CardEffectiveBeginTime,121) CardEffectiveBeginTime, convert(char(10),hr_hi_Person.CardEffectiveEndTime,121) CardEffectiveEndTime,hr_hi_Person.PersonDepart, case HR_HI_PERSON.KeyPerson when '1' then dbo.UDF_GetResourceMessageByID('U8.HR.HM.hr_HRVouchBus.00001') else dbo.UDF_GetResourceMessageByID('U8.HR.HM.hr_HRVouchBus.00002') end as KeyPerson, case hr_hi_Person.btaketm when '1' then dbo.UDF_GetResourceMessageByID('U8.HR.HM.hr_HRVouchBus.00001') else dbo.UDF_GetResourceMessageByID('U8.HR.HM.hr_HRVouchBus.00002') end as btaketm, aa_bank1.cbankname AS cpsnBankCode,department2.cDepName AS cDepName,department3.cDepName AS cDept_Name, HR_CT0004.vdescription AS rPersonType, HR_CT0015.vdescription AS rSex, HR_CT0026.vdescription AS reducation, HR_CT0057.vdescription AS rNational, HR_CT0068.vdescription AS rCountry, HR_CT0079.vdescription AS rNativePlace, HR_CT00710.vdescription AS rPerResidence, HR_CT00811.vdescription AS rhealthStatus, HR_CT00912.vdescription AS rMarriStatus, hr_ct01013.vdescription AS NatrueType, hr_ct01114.vDescription AS cLeaveReson, HR_CT05915.vdescription AS rCheckInFlag, HR_CT15716.vDescription AS rFigure, HR_CT15817.vDescription AS rWorkStatus, HR_CT27018.vdescription AS CRegion, HR_CT41619.vdescription AS rEmployState, HR_CT80320.vDescription AS EmploymentForm, hr_om_duty21.Vdutyname AS cDutycode, hr_om_job22.Vjobname AS cjobcode, hr_tm_DutyClass23.vName AS cdutyclass from hr_hi_Person left JOIN person ON hr_hi_Person.cPsn_Num = person.cpersoncode left join hr_hi_jobinfo on hr_hi_jobinfo.cpsn_num=hr_hi_person.cpsn_num and blastflag=1 and bpartjob=0 left join hr_hi_edu on hr_hi_person.cpsn_num=hr_hi_edu.cpsn_num and hr_hi_edu.blastflag=1 and hr_hi_edu.irecordid=(select isnull(max(hhe.iRecordID),0) from hr_hi_edu hhe where hhe.cpsn_num=hr_hi_person.cpsn_num and hhe.blastflag=1) Left join aa_bank as aa_bank1 on hr_hi_Person.cpsnBankCode=aa_bank1.cbankCode Left join department as department2 on Person.cdepcode=department2.CDepCode Left join department as department3 on hr_hi_Person.cdept_num=department3.CDepCode Left join HR_CT000 as HR_CT0004 on hr_hi_Person.rPersonType=HR_CT0004.ccodeid Left join HR_CT001 as HR_CT0015 on hr_hi_Person.rSex=HR_CT0015.ccodeid Left join HR_CT002 as HR_CT0026 on hr_hi_edu.reducation=HR_CT0026.ccodeid Left join HR_CT005 as HR_CT0057 on hr_hi_Person.rNational=HR_CT0057.ccodeid Left join HR_CT006 as HR_CT0068 on HR_HI_PERSON.rCountry=HR_CT0068.ccodeid Left join HR_CT007 as HR_CT0079 on hr_hi_Person.rNativePlace=HR_CT0079.ccodeid Left join HR_CT007 as HR_CT00710 on hr_hi_Person.rPerResidence=HR_CT00710.ccodeid Left join HR_CT008 as HR_CT00811 on hr_hi_Person.rhealthStatus=HR_CT00811.ccodeid Left join HR_CT009 as HR_CT00912 on hr_hi_Person.rMarriStatus=HR_CT00912.ccodeid Left join hr_ct010 as hr_ct01013 on HR_HI_PERSON.NatrueType=hr_ct01013.ccodeid Left join hr_ct011 as hr_ct01114 on HR_HI_PERSON.cLeaveReson=hr_ct01114.cCodeID Left join HR_CT059 as HR_CT05915 on hr_hi_Person.rCheckInFlag=HR_CT05915.ccodeid Left join HR_CT157 as HR_CT15716 on HR_HI_PERSON.rFigure=HR_CT15716.cCodeID Left join HR_CT158 as HR_CT15817 on HR_HI_PERSON.rWorkStatus=HR_CT15817.cCodeID Left join HR_CT270 as HR_CT27018 on HR_HI_PERSON.CRegion=HR_CT27018.ccodeid Left join HR_CT416 as HR_CT41619 on HR_HI_PERSON.rEmployState=HR_CT41619.ccodeid Left join HR_CT803 as HR_CT80320 on HR_HI_PERSON.EmploymentForm=HR_CT80320.cCodeID Left join hr_om_duty as hr_om_duty21 on hr_hi_jobinfo.cDutycode=hr_om_duty21.Cdutycode Left join hr_om_job as hr_om_job22 on hr_hi_jobinfo.cjobcode=hr_om_job22.cJobcode Left join hr_tm_DutyClass as hr_tm_DutyClass23 on hr_hi_person.cdutyclass=hr_tm_DutyClass23.cCode
GO
|
|
|站长微信|Archiver|手机版|小黑屋|用友之家
( 蜀ICP备07505338号|
51072502110008 )
IP卡
狗仔卡
提升卡
置顶卡
变色卡
千斤顶
显身卡
