HR_I9_DC_EXP_VW

(SQL View)
 Index Back

I9 Documents

I9 Documents

SELECT A.EMPLID , A.EFFDT , A.EFFSEQ , COALESCE(XL.I9_DOCTITLE_A , A.I9_DOCTITLE_A ) , CASE WHEN A.I9_RECEIPT_FLG_A = 'Y' THEN A.I9_RECEIPT_NBR_A1 ELSE A.I9_DOC_NBR_A1 END , COALESCE(A.I9_DOC_EXP_DT_A1 ,A.I9_REC_EXP_DT_A1) FROM PS_HR_I9_PERSON A LEFT JOIN PS_I9_IAUTH_A_VW XL ON ( XL.I9_DOC_TITLE_A = A.I9_DOC_TITLE_A ) WHERE A.I9_DOC_TITLE_A <> '00' UNION SELECT A.EMPLID , A.EFFDT , A.EFFSEQ , COALESCE(XL.I9_DOCTITLE_A2 , A.I9_DOCTITLE_A2 ) ,CASE WHEN A.I9_RECEIPT_FLG_A2 = 'Y' THEN A.I9_RECEIPT_NBR_A2 ELSE A.I9_DOC_NBR_A2 END , COALESCE(A.I9_DOC_EXP_DT_A2 ,A.I9_REC_EXP_DT_A2) FROM PS_HR_I9_PERSON A LEFT JOIN PS_I9_IAUTH_A2_VW XL ON ( XL.I9_DOC_TITLE_A2 = A.I9_DOC_TITLE_A2 ) WHERE A.I9_DOC_TITLE_A2 <> '00' UNION SELECT A.EMPLID , A.EFFDT , A.EFFSEQ , COALESCE(XL.I9_DOCTITLE_A3 , A.I9_DOCTITLE_A3 ) , CASE WHEN A.I9_RECEIPT_FLG_A3 = 'Y' THEN A.I9_RECEIPT_NBR_A3 ELSE A.I9_DOC_NBR_A3 END , COALESCE(A.I9_DOC_EXP_DT_A3 ,A.I9_REC_EXP_DT_A3) FROM PS_HR_I9_PERSON A LEFT JOIN PS_I9_IAUTH_A3_VW XL ON ( XL.I9_DOC_TITLE_A3 = A.I9_DOC_TITLE_A3 ) WHERE A.I9_DOC_TITLE_A3 <> '00' UNION SELECT A.EMPLID , A.EFFDT , A.EFFSEQ , COALESCE(XL.I9_DOCTITLE_B ,A.I9_DOCTITLE_B ) , CASE WHEN A.I9_RECEIPT_FLG_B = 'Y' THEN A.I9_RECEIPT_NBR_B ELSE A.I9_DOC_NBR_B END , COALESCE(A.I9_DOC_EXP_DT_B ,A.I9_REC_EXP_DT_B) FROM PS_HR_I9_PERSON A LEFT JOIN PS_I9_IAUTH_B_VW XL ON ( XL.I9_DOC_TITLE_B = A.I9_DOC_TITLE_B ) WHERE A.I9_DOC_TITLE_B <> '00' UNION SELECT A.EMPLID , A.EFFDT , A.EFFSEQ , COALESCE(XL.I9_DOCTITLE_C ,A.I9_DOCTITLE_C ) , CASE WHEN A.I9_RECEIPT_FLG_C = 'Y' THEN A.I9_RECEIPT_NBR_C ELSE A.I9_DOC_NBR_C END , COALESCE(A.I9_DOC_EXP_DT_C ,A.I9_REC_EXP_DT_C) FROM PS_HR_I9_PERSON A LEFT JOIN PS_I9_IAUTH_C_VW XL ON ( XL.I9_DOC_TITLE_C = A.I9_DOC_TITLE_C ) WHERE A.I9_DOC_TITLE_C <> '00'

# PeopleSoft Field Name PeopleSoft Field Type Database Column Type Description
1 EMPLID Character(11) VARCHAR2(11) NOT NULL Employee ID
2 EFFDT Date(10) DATE Effective Date

Default Value: %date

3 EFFSEQ Number(3,0) SMALLINT NOT NULL Effective Sequence
4 HR_I9_DOCUMENT_NM Character(50) VARCHAR2(50) NOT NULL Document
5 HR_I9_DOC_NBR Character(39) VARCHAR2(39) NOT NULL Document Number
6 HR_I9_DOC_EXP_DT Date(10) DATE I9 Document Expiry Date