GP_MESSAGES_VW

(SQL View)
 Index Back

PAYEE MESSAGES WORK ITEM VIEW

PAYEE MESSAGES WORK ITEM VIEW

SELECT DISTINCT A.EMPLID , ( SELECT B.NAME FROM PS_NAMES B WHERE B.EMPLID = A.EMPLID AND B.NAME_TYPE = 'PRI' AND B.EFFDT = ( SELECT MAX(B1.EFFDT) FROM PS_NAMES B1 WHERE B1.EMPLID = B.EMPLID AND B1.NAME_TYPE = B.NAME_TYPE AND B1.EFFDT <= %CurrentDateIn)), A.EMPL_RCD ,A.MESSAGE_SET_NBR ,A.MESSAGE_NBR ,REPLACE( REPLACE(REPLACE(REPLACE(REPLACE( C.MESSAGE_TEXT ,'%1' , CASE WHEN A.PIN_IND1= 'Y' THEN ( SELECT PIN_NM FROM PS_GP_PIN WHERE PIN_NUM =A.MSGDATA1 ) ELSE A.MSGDATA1 END),'%2' , CASE WHEN PIN_IND2= 'Y' THEN ( SELECT PIN_NM FROM PS_GP_PIN WHERE PIN_NUM =A.MSGDATA2 ) ELSE A.MSGDATA2 END) ,'%3' , CASE WHEN A.PIN_IND3= 'Y' THEN ( SELECT PIN_NM FROM PS_GP_PIN WHERE PIN_NUM =A.MSGDATA3 ) ELSE A.MSGDATA3 END) ,'%4' , CASE WHEN A.PIN_IND4= 'Y' THEN ( SELECT PIN_NM FROM PS_GP_PIN WHERE PIN_NUM =A.MSGDATA4 ) ELSE A.MSGDATA4 END) ,'%5' , CASE WHEN A.PIN_IND5= 'Y' THEN ( SELECT PIN_NM FROM PS_GP_PIN WHERE PIN_NUM =A.MSGDATA5 ) ELSE A.MSGDATA5 END) ,A.GP_MSG_SEVERITY , CASE WHEN A.EMPLID <> ' ' AND A.PYE_CALC_STAT IS NULL THEN '11' WHEN A.EMPLID = ' ' AND A.PYE_CALC_STAT IS NULL THEN '-1' ELSE A.PYE_CALC_STAT END , CASE WHEN ( SELECT DISTINCT D.RUN_OPEN_IND FROM PS_GP_CAL_RUN D WHERE D.CAL_RUN_ID = A.CAL_RUN_ID AND RUN_FINALIZED_IND = 'N') = 'Y' THEN 'O' WHEN ( SELECT DISTINCT D.RUN_FINALIZED_IND FROM PS_GP_CAL_RUN D WHERE D.CAL_RUN_ID = A.CAL_RUN_ID AND D.PMT_SENT_IND = 'N') = 'Y' THEN 'F' WHEN ( SELECT DISTINCT D.RUN_FINALIZED_IND FROM PS_GP_CAL_RUN D WHERE D.CAL_RUN_ID = A.CAL_RUN_ID AND D.PMT_SENT_IND = 'Y') = 'Y' THEN 'B' END, A.CAL_RUN_ID ,A.CAL_ID , ( SELECT D.OFF_CYCLE FROM PS_GP_CAL_RUN D WHERE D.CAL_RUN_ID = A.CAL_RUN_ID), A.GP_PAYGROUP , ( SELECT J.COMPANY FROM PS_JOB J WHERE J.EMPLID = A.EMPLID AND J.EMPL_RCD = A.EMPL_RCD AND J.EFFDT = ( SELECT MAX(J1.EFFDT) FROM PS_JOB J1 WHERE J1.EMPLID = J.EMPLID AND J1.EMPL_RCD= J.EMPL_RCD AND J1.EFFDT < %CurrentDateIn) AND J.EFFSEQ = ( SELECT MAX(J2.EFFSEQ) FROM PS_JOB J2 WHERE J2.EMPLID = J.EMPLID AND J2.EMPL_RCD= J.EMPL_RCD AND J2.EFFDT = J.EFFDT) ), ( SELECT J.DEPTID FROM PS_JOB J WHERE J.EMPLID = A.EMPLID AND J.EMPL_RCD = A.EMPL_RCD AND J.EFFDT = ( SELECT MAX(J1.EFFDT) FROM PS_JOB J1 WHERE J1.EMPLID = J.EMPLID AND J1.EMPL_RCD= J.EMPL_RCD AND J1.EFFDT < %CurrentDateIn) AND J.EFFSEQ = ( SELECT MAX(J2.EFFSEQ) FROM PS_JOB J2 WHERE J2.EMPLID = J.EMPLID AND J2.EMPL_RCD= J.EMPL_RCD AND J2.EFFDT = J.EFFDT) ) FROM PS_GP_PYE_MSG_VW A ,PSMSGCATDEFN C WHERE C.MESSAGE_SET_NBR=A.MESSAGE_SET_NBR AND C.MESSAGE_NBR = A.MESSAGE_NBR AND A.GP_MSG_SEVERITY IN ( 'E' ,'W')

  • Related Language Record: GP_WC_PYMLNG_VW

    • # PeopleSoft Field Name PeopleSoft Field Type Database Column Type Description
    1 EMPLID Character(11) VARCHAR2(11) NOT NULL Employee ID
    2 NAME Character(50) VARCHAR2(50) NOT NULL Name
    3 EMPL_RCD Number(3,0) SMALLINT NOT NULL Empl Record
    4 MESSAGE_SET_NBR Number(5,0) INTEGER NOT NULL Message Set Number. This field refers to the Message Set Number in the Message Catalog.
    5 MESSAGE_NBR Number(5,0) INTEGER NOT NULL Message Number. This field refers to the Message Number in the Message Catalog.
    6 MESSAGE_TEXT_254 Character(254) VARCHAR2(254) NOT NULL Message Text
    7 GP_MSG_SEVERITY Character(1) VARCHAR2(1) NOT NULL Message Severity. Shows the severity of a message after a calculation. Stored in GP_MESSAGES
    E=Error
    W=Warning
    8 PYE_CALC_STAT Character(2) VARCHAR2(2) NOT NULL Calculation Status: field indicates the processing status of a segment (payment)
    00=Identified
    02=Calculation Error
    03=Calculation Error - Bypassed
    04=Calculation Error - By Rule
    11=No Segment Created
    50=Calculation Successful
    55=Frozen For Further Calc
    70=Finalized
    75=Finalized - With Banking
    9 GP_CAL_STATUS Character(1) VARCHAR2(1) NOT NULL Calculation status
    B=Finalized with Banking
    F=Finalized
    O=Open
    10 CAL_RUN_ID Character(18) VARCHAR2(18) NOT NULL Calendar Run Id
    11 CAL_ID Character(18) VARCHAR2(18) NOT NULL calendar id
    12 GP_OFF_CYCLE Character(2) VARCHAR2(2) NOT NULL OFF CYCLE Translate values
    N=No
    Y=Yes

    Y/N Table Edit

    13 GP_PAYGROUP Character(10) VARCHAR2(10) NOT NULL Global Payroll pay group
    14 COMPANY Character(3) VARCHAR2(3) NOT NULL Company
    15 DEPTID Character(10) VARCHAR2(10) NOT NULL Department