MIL_SRVC_RANK (SQL View)	Index Back
Military View -Service Ranks Created for Military Rank Fluid Approvals to take the associated Military fields

MIL_SRVC_RANK

(SQL View)

Military View -Service Ranks

Created for Military Rank Fluid Approvals to take the associated Military fields

SELECT DISTINCT DAT.EMPLID ,DAT.EMPL_RCD ,DAT.EFFDT ,DAT.EFFSEQ ,ORG_MIL.MILITARY_SERVICE ,JOB_MIL.MIL_SVC_COMP_CD ,JOB_MIL.MIL_CMP_CATEGORY ,ORG.ORG_INSTANCE_ERN ,JOB.PER_ORG FROM PS_MIL_RANK_DAT DAT , PS_JOB JOB ,PS_PER_ORG_INS_MIL ORG_MIL ,PS_JOB_MIL JOB_MIL ,PS_PER_ORG_ASGN ORG WHERE DAT.EMPLID=JOB.EMPLID AND DAT.EMPL_RCD=JOB.EMPL_RCD AND JOB.EFFDT=( SELECT MAX(EFFDT) FROM PS_JOB A WHERE JOB.EMPLID=A.EMPLID AND JOB.EMPL_RCD=A.EMPL_RCD ) AND DAT.EMPLID=ORG.EMPLID AND DAT.EMPL_RCD=ORG.EMPL_RCD AND JOB.PER_ORG=ORG.PER_ORG AND ORG.EMPLID=ORG_MIL.EMPLID AND ORG.ORG_INSTANCE_ERN=ORG_MIL.ORG_INSTANCE_ERN AND DAT.EMPLID=JOB_MIL.EMPLID AND DAT.EMPL_RCD=JOB_MIL.EMPL_RCD AND JOB_MIL.EFFDT=( SELECT MAX(EFFDT) FROM PS_JOB_MIL MIL WHERE JOB_MIL.EMPLID=MIL.EMPLID AND JOB_MIL.EMPL_RCD=MIL.EMPL_RCD AND MIL.EFFDT<=DAT.EFFDT)

# PeopleSoft Field Name PeopleSoft Field Type Database Column Type Description

1 EMPLID Character(11) VARCHAR2(11) NOT NULL Employee ID

2 EMPL_RCD Number(3,0) SMALLINT NOT NULL Empl Record
Default Value: 0

3 EFFDT Date(10) DATE Effective Date
Default Value: %date

4 EFFSEQ Number(3,0) SMALLINT NOT NULL Effective Sequence

5 DESCR30 Character(30) VARCHAR2(30) NOT NULL Description

6 DESCR1 Character(30) VARCHAR2(30) NOT NULL Descr

7 DESCR2 Character(30) VARCHAR2(30) NOT NULL Descr2

8 ORG_INSTANCE_ERN Number(3,0) SMALLINT NOT NULL Indicates which EMPL_RCD (ERN) is considered the Organizational Instance for this particular ERN. An Organizational Instance defines an Instance of Employment or an Instance of Contingent Workforce. Hire, Termination, and Service dates are captured at the Organiational Instance level.
Default Value: PER_ORG_INST.ORG_INSTANCE_ERN
Prompt Table: PER_ORG_INST

9 PER_ORG Character(3) VARCHAR2(3) NOT NULL Defines the Organizational Relationship(s) that a Person has to the Organization. These are Employee, Contingent Worker, and Persons of Interest.
CWR=Contingent Worker
EMP=Employee
POI=Person of Interest
Default Value: PER_ORG_INST.PER_ORG
Prompt Table: PER_ORG_VW

#	PeopleSoft Field Name	PeopleSoft Field Type	Database Column Type	Description
1	EMPLID	Character(11)	VARCHAR2(11) NOT NULL	Employee ID
2	EMPL_RCD	Number(3,0)	SMALLINT NOT NULL	Empl Record Default Value: 0
3	EFFDT	Date(10)	DATE	Effective Date Default Value: %date
4	EFFSEQ	Number(3,0)	SMALLINT NOT NULL	Effective Sequence
5	DESCR30	Character(30)	VARCHAR2(30) NOT NULL	Description
6	DESCR1	Character(30)	VARCHAR2(30) NOT NULL	Descr
7	DESCR2	Character(30)	VARCHAR2(30) NOT NULL	Descr2
8	ORG_INSTANCE_ERN	Number(3,0)	SMALLINT NOT NULL	Indicates which EMPL_RCD (ERN) is considered the Organizational Instance for this particular ERN. An Organizational Instance defines an Instance of Employment or an Instance of Contingent Workforce. Hire, Termination, and Service dates are captured at the Organiational Instance level. Default Value: PER_ORG_INST.ORG_INSTANCE_ERN Prompt Table: PER_ORG_INST
9	PER_ORG	Character(3)	VARCHAR2(3) NOT NULL	Defines the Organizational Relationship(s) that a Person has to the Organization. These are Employee, Contingent Worker, and Persons of Interest. CWR=Contingent Worker EMP=Employee POI=Person of Interest Default Value: PER_ORG_INST.PER_ORG Prompt Table: PER_ORG_VW