ISIR_00_1_EC_VW

(SQL View)
 Index Back

1999-2000 ISIR Sect 1 EC

This is an exact view of ISIR_00_1_EC table. The view adds masking for ORIG_SSN, SSN and BIRTHDATE. Section 1 of the 1999-2000 ISIR staging tables. Contains ISIR fields 1- 112 plus some additional helpful fields.

SELECT B.OPRCLASS ,A.ECTRANSID ,A.ECQUEUEINSTANCE ,A.ECTRANSINOUTSW ,A.ISIR_SEQ_NO ,A.ISIR_LOAD_STATUS ,A.ISIR_LOAD_ACTION ,A.SSN ,CASE WHEN B.MASK_CFG_NID='1' THEN '*********' WHEN B.MASK_CFG_NID='2' THEN CONCAT('*****' , SUBSTR(A.SSN , 6 ,4)) ELSE A.SSN END ,A.NAME_CD ,A.EMPLID ,A.IWD_STD_LAST_NAME ,A.IWD_STD_FIRST_NM02 ,A.IWD_STU_MI ,A.BIRTHDATE ,CASE WHEN B.MASK_CFG_DOB='1' THEN '**/**/****' WHEN B.MASK_CFG_DOB='2' THEN TO_CHAR(A.BIRTHDATE , 'MM/DD') ELSE TO_CHAR(A.BIRTHDATE , 'MM/DD/YYYY') END FROM PS_ISIR_00_1_EC A ,PS_PERS_MSK_CFG B

# PeopleSoft Field Name PeopleSoft Field Type Database Column Type Description
1 OPRCLASS Character(30) VARCHAR2(30) NOT NULL Operator Class
2 ECTRANSID Character(15) VARCHAR2(15) NOT NULL Electronic Commerce Transaction ID. Used in EDI Manager. One of three(3) Key Fields(ECTRANSID, ECTRANSINOUTSW, ECQUEUEINSTANCE) used in Electronic Commerce(EC) Staging Tables. ECTRANSID in EDI Manager identifies a transaction type(like "PO" for Purchase Orders)
3 ECQUEUEINSTANCE Number(9,0) DECIMAL(9) NOT NULL Electronic Commerce Queue Instance ID. Used in EDI Manager. One of three(3) Key Fields(ECTRANSID, ECTRANSINOUTSW, ECQUEUEINSTANCE) used in Electronic Commerce(EC) Staging Tables.
4 ECTRANSINOUTSW Character(1) VARCHAR2(1) NOT NULL Electronic Commerce Inbound/Outbound Switch. Used in EDI Manager. One of three(3) Key Fields(ECTRANSID, ECTRANSINOUTSW, ECQUEUEINSTANCE) used in Electronic Commerce(EC) Staging Tables. The field value is "I" for Inbound Transactions, and "O" for Outbound.
I=Inbound
O=Outbound
5 ISIR_SEQ_NO Number(5,0) INTEGER NOT NULL Sequence Number
6 ISIR_LOAD_STATUS Character(1) VARCHAR2(1) NOT NULL Load Status
C=Change Processed
E=Error Encountered
K=Skipped
L=Loaded
S=Suspended
U=Unprocessed
7 ISIR_LOAD_ACTION Character(1) VARCHAR2(1) NOT NULL Load Action
A=Add ISIR
I=Skip/Done
R=Recycle
8 SSN Character(9) VARCHAR2(9) NOT NULL Social Security #
9 SSN_MSK Character(9) VARCHAR2(9) NOT NULL Social Security #
10 NAME_CD Character(2) VARCHAR2(2) NOT NULL Name Code
11 EMPLID Character(11) VARCHAR2(11) NOT NULL Employee ID

Prompt Table: PEOPLE_SRCH

12 IWD_STD_LAST_NAME Character(35) VARCHAR2(35) NOT NULL Last Name
13 IWD_STD_FIRST_NM02 Character(35) VARCHAR2(35) NOT NULL Student's First Name
14 IWD_STU_MI Character(15) VARCHAR2(15) NOT NULL Student Middle Initial
15 BIRTHDATE Date(10) DATE Date of Birth
16 BIRTHDATE_MSK Character(10) VARCHAR2(10) NOT NULL Date of Birth