VC_AWDSTT_VW

(SQL View)
 Index Back

Award Status transition

Award Status view to limit status list based upon current status.

SELECT DISTINCT B.VC_PLAN_ID ,B.VC_PAYOUT_PRD_ID ,B.GB_GROUP_ID ,B.EMPLID ,B.EMPL_RCD ,C.VC_AWARD_STATUS_TO ,A.XLATLONGNAME FROM PSXLATITEM A , PS_VC_AWARD B , PS_VC_AWDSTT C , PS_VC_PLAN_EFFDT D , PS_VC_PAYPRD_TBL E WHERE A.FIELDNAME='VC_AWARD_STATUS' AND D.VC_PLAN_ID = B.VC_PLAN_ID AND D.VC_PLAN_ID = E.VC_PLAN_ID AND D.EFFDT = ( SELECT MAX(EFFDT) FROM PS_VC_PLAN_EFFDT PE1 WHERE PE1.VC_PLAN_ID = E.VC_PLAN_ID AND PE1.EFFDT <= E.PRD_END_DT ) AND B.VC_PAYOUT_PRD_ID = E.VC_PAYOUT_PRD_ID AND D.VC_APPROVAL_IND=C.VC_APPROVAL_IND AND A.FIELDVALUE =C.VC_AWARD_STATUS_TO AND B.VC_AWARD_STATUS = C.VC_AWARD_STATUS AND A.EFFDT = ( SELECT MAX(A2.EFFDT) FROM PSXLATITEM A2 WHERE A2.EFFDT <= E.PRD_END_DT AND A.FIELDNAME = A2.FIELDNAME AND A.FIELDVALUE =A2.FIELDVALUE) AND A.EFF_STATUS = 'A' AND (C.VC_AWARD_STATUS IN ('A') OR C.VC_AWARD_STATUS_TO NOT IN ('A'))

  • Related Language Record: VC_AWDSTT_LN_VW

    • # PeopleSoft Field Name PeopleSoft Field Type Database Column Type Description
    1 VC_PLAN_ID Character(10) VARCHAR2(10) NOT NULL VC Plan ID
    2 VC_PAYOUT_PRD_ID Character(10) VARCHAR2(10) NOT NULL Payout Period ID
    3 GB_GROUP_ID Character(15) VARCHAR2(15) NOT NULL Group Build ID.
    4 EMPLID Character(11) VARCHAR2(11) NOT NULL Employee ID
    5 EMPL_RCD Number(3,0) SMALLINT NOT NULL Empl Rcd Nbr
    6 VC_AWARD_STATUS Character(2) VARCHAR2(2) NOT NULL Award Status
    A=Approved
    C=Cancelled
    D=Ready to Submit
    E=Error
    I=Initial Calculation
    J=Rejected
    P=Paid/Granted
    R=Recommended
    S=Submitted
    T=Accepted
    7 VC_AWD_STATUS_NAME Character(30) VARCHAR2(30) NOT NULL Award Status Description