SAD_BPV_ORG_SR4(SQL View) |
Index Back |
---|---|
BPV Organisations viewFS 10062004 |
SELECT DISTINCT B.HEGIS_CODE , A.EXT_ORG_ID , A.EFFDT , A.EFF_STATUS , C.OPRID , A.DESCR50 , A.DESCRSHORT FROM PS_EXT_ORG_TBL A ,PS_SAD_ACC_ORG_NLD B ,PS_SCRTY_TBL_ACAD C , PS_SAD_BPV_ORG_NLD D WHERE A.EXT_ORG_ID = B.EXT_ORG_ID AND B.SAD_INTSHP_APP_NLD IN ('AC','IB') AND A.EFFDT = ( SELECT MAX(A_ED.EFFDT) FROM PS_EXT_ORG_TBL A_ED WHERE A.EXT_ORG_ID = A_ED.EXT_ORG_ID AND A_ED.EFFDT <= %CurrentDateIn) AND C.ACCESS_CD='Y' AND EXISTS ( SELECT 'X' FROM PSTREENODE E WHERE E.SETID=' ' AND E.TREE_NAME='ACAD_ORGANIZATION' AND E.EFFDT=C.TREE_EFFDT AND E.TREE_NODE_NUM BETWEEN C.TREE_NODE_NUM AND C.TREE_NODE_NUM_END AND NOT EXISTS ( SELECT 'X' FROM PS_SCRTY_TBL_ACAD G WHERE C.OPRID=G.OPRID AND C.TREE_NODE_NUM<>G.TREE_NODE_NUM AND E.TREE_NODE_NUM BETWEEN G.TREE_NODE_NUM AND G.TREE_NODE_NUM_END AND G.TREE_NODE_NUM BETWEEN C.TREE_NODE_NUM AND C.TREE_NODE_NUM_END)) AND D.EXT_ORG_ID = A.EXT_ORG_ID |
# | PeopleSoft Field Name | PeopleSoft Field Type | Database Column Type | Description |
---|---|---|---|---|
1 | HEGIS_CODE | Character(8) | VARCHAR2(8) NOT NULL |
HEGIS Code
Default Value: ACAD_PROG_TBL.HEGIS_CODE Prompt Table: ACAD_PROG_TBL |
2 | EXT_ORG_ID | Character(11) | VARCHAR2(11) NOT NULL |
External Org ID
Default Value: NEW Prompt Table: EXT_ORG_TBL |
3 | EFFDT | Date(10) | DATE |
Effective Date
Default Value: %date |
4 | EFF_STATUS | Character(1) | VARCHAR2(1) NOT NULL |
Effective Status
A=Active I=Inactive Default Value: A |
5 | OPRID | Character(30) | VARCHAR2(30) NOT NULL | A user's ID (see PSOPRDEFN). |
6 | DESCR50 | Character(50) | VARCHAR2(50) NOT NULL | Description of length 50 |
7 | DESCRSHORT | Character(10) | VARCHAR2(10) NOT NULL | Short Description |