SSR_AIRITEM_V3L

(SQL View)
 Index Back

SELECT a1.INSTITUTION , a1.SSR_ITEM_TYPE , a1.SSR_ITEM_ID , s1.OPRID , a1RL.LANGUAGE_CD , a1.EFFDT , a1RL.DESCR50 , b1RL.DESCR FROM PS_SSR_AIR_HDR a1 , PS_SSR_AIR_HDR_LN a1RL , PS_SSR_ITEM_TYPE b1 , PS_SSR_ITEMTYPE_LN b1RL , ps_ssr_air_scrty c1 , ps_scrty_tbl_acad s1 WHERE a1.INSTITUTION = a1RL.INSTITUTION AND a1.SSR_ITEM_ID = a1RL.SSR_ITEM_ID AND a1.EFFDT = a1RL.EFFDT AND a1.SSR_ITEM_TYPE = b1.SSR_ITEM_TYPE AND b1.SSR_ITEM_TYPE = b1RL.SSR_ITEM_TYPE AND a1RL.LANGUAGE_CD = b1RL.LANGUAGE_CD AND a1.institution = c1.institution AND a1.ssr_item_id = c1.ssr_item_id AND a1.effdt = c1.effdt AND s1.access_cd='Y' AND EXISTS ( SELECT 'x' FROM pstreenode t1 WHERE t1.setid=' ' AND t1.tree_name='ACAD_ORGANIZATION' AND t1.effdt=s1.tree_effdt AND t1.tree_node=c1.acad_org AND t1.tree_node_num BETWEEN s1.tree_node_num AND s1.tree_node_num_end AND NOT EXISTS ( SELECT 'x' FROM ps_scrty_tbl_acad s1a WHERE s1a.oprid=s1.oprid AND s1a.tree_node_num<>s1.tree_node_num AND t1.tree_node_num BETWEEN s1a.tree_node_num AND s1a.tree_node_num_end AND s1a.tree_node_num BETWEEN s1.tree_node_num AND s1.tree_node_num_end)) UNION SELECT a2.INSTITUTION , a2.SSR_ITEM_TYPE , a2.SSR_ITEM_ID , s2.OPRID , a2RL.LANGUAGE_CD , a2.EFFDT , a2RL.DESCR50 , b2.DESCR FROM PS_SSR_AIR_HDR a2 , PS_SSR_AIR_HDR_LN a2RL , PS_SSR_ITEM_TYPE b2 , ps_ssr_air_scrty c2 , ps_scrty_tbl_acad s2 WHERE a2.INSTITUTION = a2RL.INSTITUTION AND a2.SSR_ITEM_ID = a2RL.SSR_ITEM_ID AND a2.EFFDT = a2RL.EFFDT AND a2.SSR_ITEM_TYPE = b2.SSR_ITEM_TYPE AND NOT EXISTS ( SELECT 'X' FROM PS_SSR_ITEMTYPE_LN b2t WHERE b2t.SSR_ITEM_TYPE = b2.SSR_ITEM_TYPE AND b2t.LANGUAGE_CD = a2RL.LANGUAGE_CD) AND a2.institution = c2.institution AND a2.ssr_item_id = c2.ssr_item_id AND a2.effdt = c2.effdt AND s2.access_cd='Y' AND EXISTS ( SELECT 'x' FROM pstreenode t2 WHERE t2.setid=' ' AND t2.tree_name='ACAD_ORGANIZATION' AND t2.effdt=s2.tree_effdt AND t2.tree_node=c2.acad_org AND t2.tree_node_num BETWEEN s2.tree_node_num AND s2.tree_node_num_end AND NOT EXISTS ( SELECT 'x' FROM ps_scrty_tbl_acad s2a WHERE s2a.oprid=s2.oprid AND s2a.tree_node_num<>s2.tree_node_num AND t2.tree_node_num BETWEEN s2a.tree_node_num AND s2a.tree_node_num_end AND s2a.tree_node_num BETWEEN s2.tree_node_num AND s2.tree_node_num_end)) UNION SELECT a3.INSTITUTION , a3.SSR_ITEM_TYPE , a3.SSR_ITEM_ID , s3.OPRID , b3RL.LANGUAGE_CD , a3.EFFDT , a3.DESCR50 , b3RL.DESCR FROM PS_SSR_AIR_HDR a3 , PS_SSR_ITEM_TYPE b3 , PS_SSR_ITEMTYPE_LN b3RL , ps_ssr_air_scrty c3 , ps_scrty_tbl_acad s3 WHERE a3.SSR_ITEM_TYPE = b3.SSR_ITEM_TYPE AND NOT EXISTS ( SELECT 'X' FROM PS_SSR_AIR_HDR_LN a3t WHERE a3t.INSTITUTION = a3.INSTITUTION AND a3t.SSR_ITEM_ID = a3.SSR_ITEM_ID AND a3t.EFFDT = a3.EFFDT ) AND a3.institution = c3.institution AND a3.ssr_item_id = c3.ssr_item_id AND a3.effdt = c3.effdt AND s3.access_cd='Y' AND EXISTS ( SELECT 'x' FROM pstreenode t3 WHERE t3.setid=' ' AND t3.tree_name='ACAD_ORGANIZATION' AND t3.effdt=s3.tree_effdt AND t3.tree_node=c3.acad_org AND t3.tree_node_num BETWEEN s3.tree_node_num AND s3.tree_node_num_end AND NOT EXISTS ( SELECT 'x' FROM ps_scrty_tbl_acad s3a WHERE s3a.oprid=s3.oprid AND s3a.tree_node_num<>s3.tree_node_num AND t3.tree_node_num BETWEEN s3a.tree_node_num AND s3a.tree_node_num_end AND s3a.tree_node_num BETWEEN s3.tree_node_num AND s3.tree_node_num_end))

  • Related Language Record for SSR_AIR_ITEM_V3

    • # PeopleSoft Field Name PeopleSoft Field Type Database Column Type Description
    1 INSTITUTION Character(5) VARCHAR2(5) NOT NULL Academic Institution

    Prompt Table: INSTITUTION_TBL

    2 SSR_ITEM_TYPE3 Character(20) VARCHAR2(20) NOT NULL Item Type
    3 SSR_ITEM_ID_SRCH Character(11) VARCHAR2(11) NOT NULL Academic Item ID
    4 OPRID Character(30) VARCHAR2(30) NOT NULL A user's ID (see PSOPRDEFN).
    5 LANGUAGE_CD Character(3) VARCHAR2(3) NOT NULL Language Code
    6 EFFDT Date(10) DATE NOT NULL Effective Date

    Default Value: %date

    7 DESCR50 Character(50) VARCHAR2(50) NOT NULL Description of length 50
    8 DESCR Character(30) VARCHAR2(30) NOT NULL Description