/*
    PeopleSoft Wiki: Check Process Override Options
    Created May 2009 by Praj Basnet

    http://peoplesoft.wikidot.com/check-process-override-options
*/

select
    PRCSTYPE,
    PRCSNAME,
    DESCR,
    (
        select  XLATSHORTNAME
        from PSXLATITEM XI
        where FIELDNAME = 'PARMLISTTYPE'
        and FIELDVALUE = PD.PARMLISTTYPE
        and EFFDT = (
            select  max(EFFDT)
            from    PSXLATITEM
            where   FIELDNAME = XI.FIELDNAME
            and     FIELDVALUE = XI.FIELDVALUE
        )
        and EFF_STATUS = 'A'
    ) as PARMLISTTYPE,
    PARMLIST,
    (
        select  XLATSHORTNAME
        from PSXLATITEM XI
        where FIELDNAME = 'CMDLINETYPE'
        and FIELDVALUE = PD.CMDLINETYPE
        and EFFDT = (
            select  max(EFFDT)
            from    PSXLATITEM
            where   FIELDNAME = XI.FIELDNAME
            and     FIELDVALUE = XI.FIELDVALUE
            and     EFFDT <= sysdate)
        and EFF_STATUS = 'A'
    ) as CMDLINETYPE,
    CMDLINE,
    (
        select  XLATSHORTNAME
        from PSXLATITEM XI
        where FIELDNAME = 'WORKINGDIRTYPE'
        and FIELDVALUE = PD.WORKINGDIRTYPE
        and EFFDT = (
            select  max(EFFDT)
            from    PSXLATITEM
            where   FIELDNAME = XI.FIELDNAME
            and     FIELDVALUE = XI.FIELDVALUE
            and     EFFDT <= sysdate)
        and EFF_STATUS = 'A'
    ) as WORKINGDIRTYPE,
    WORKINGDIR
from
    PS_PRCSDEFN PD
where
    (PARMLISTTYPE != '0' OR CMDLINETYPE != '0' OR WORKINGDIRTYPE != '0')
order by
    PRCSTYPE, PRCSNAME
;