LOCATION_VW2

(SQL View)
 Index Back

View shows only setid,location

This view shows only setid, location and descr Date Initials Reference# Description 03/20/09 Nash 1841241002 Modified the SQL so that the newly added location with future effdt is shown in the search list.

SELECT DISTINCT A.SETID , A.LOCATION , A.EFF_STATUS , A.DESCR , %Coalesce((SELECT ID.STD_ID_NUM FROM PS_LOCATION_ID_NBR ID WHERE ID.LOCATION = A.LOCATION AND ID.STD_ID_NUM_QUAL ='GLN') , ' ') FROM PS_LOCATION_TBL A ,PS_LOCATION_ID_NBR ID WHERE ((A.EFFDT = ( SELECT MAX(L.EFFDT) FROM PS_LOCATION_TBL L WHERE L.SETID = A.SETID AND L.LOCATION = A.LOCATION AND L.EFFDT <=%CurrentDateIn)) OR (A.EFFDT = ( SELECT L1.EFFDT FROM PS_LOCATION_TBL L1 WHERE L1.SETID = A.SETID AND L1.LOCATION = A.LOCATION AND L1.EFFDT >%CurrentDateIn AND L1.EFFDT = ( SELECT MIN(L2.EFFDT) FROM PS_LOCATION_TBL L2 WHERE L2.SETID = L1.SETID AND L2.LOCATION = L1.LOCATION)))) UNION SELECT DISTINCT A.SETID , A.LOCATION , A.EFF_STATUS , A.DESCR ,' ' FROM PS_LOCATION_TBL A WHERE ((A.EFFDT = ( SELECT MAX(L.EFFDT) FROM PS_LOCATION_TBL L WHERE L.SETID = A.SETID AND L.LOCATION = A.LOCATION AND L.EFFDT <=%CurrentDateIn)) OR (A.EFFDT = ( SELECT L1.EFFDT FROM PS_LOCATION_TBL L1 WHERE L1.SETID = A.SETID AND L1.LOCATION = A.LOCATION AND L1.EFFDT >%CurrentDateIn AND L1.EFFDT = ( SELECT MIN(L2.EFFDT) FROM PS_LOCATION_TBL L2 WHERE L2.SETID = L1.SETID AND L2.LOCATION = L1.LOCATION)))) AND NOT EXISTS ( SELECT 'X' FROM PS_LOCATION_ID_NBR WHERE A.LOCATION =PS_LOCATION_ID_NBR.LOCATION)

  • Related Language Record: LOCATION_LVW2

    • # PeopleSoft Field Name PeopleSoft Field Type Database Column Type Description
    1 SETID Character(5) VARCHAR2(5) NOT NULL SetID

    Default Value: OPR_DEF_TBL_FS.SETID

    Prompt Table: SP_SETID_NONVW

    2 LOCATION Character(10) VARCHAR2(10) NOT NULL Location Code
    3 EFF_STATUS Character(1) VARCHAR2(1) NOT NULL Effective Status
    A=Active
    I=Inactive

    Default Value: A

    4 DESCR Character(30) VARCHAR2(30) NOT NULL Description
    5 STD_ID_NUM Character(35) VARCHAR2(35) NOT NULL ID Number