JPM_APRJOB_VW

(SQL View)
 Index Back

Profile Approval Job

Profile Approval Transaction Header record.

SELECT HDR.JPM_PROFILE_ID , HDR.JPM_JP_TRAN_NUM , JC.DESCR FROM PS_JPM_JP_WF_HDR HDR , PS_JPM_PROFILE PRF , PS_JOB J , PS_JOBCODE_TBL JC WHERE PRF.JPM_PROFILE_ID = HDR.JPM_PROFILE_ID AND J.EMPLID = PRF.EMPLID AND J.EMPL_RCD = COALESCE( ( SELECT MIN(A.EMPL_RCD) FROM PS_JOB A WHERE A.EMPLID = J.EMPLID AND A.EFFDT = ( SELECT MAX (A1.EFFDT) FROM PS_JOB A1 WHERE A1.EMPLID = A.EMPLID AND A1.EMPL_RCD = A.EMPL_RCD AND A1.EFFDT <= %CurrentDateIn) AND A.EFFSEQ = ( SELECT MAX(A3.EFFSEQ) FROM PS_JOB A3 WHERE A3.EMPLID = A.EMPLID AND A3.EMPL_RCD = A.EMPL_RCD AND A3.EFFDT = A.EFFDT) AND A.HR_STATUS = 'A' AND A.JOB_INDICATOR = 'P') , ( SELECT MIN(B.EMPL_RCD) FROM PS_JOB B WHERE B.EMPLID = J.EMPLID AND B.EFFDT = ( SELECT MAX (B1.EFFDT) FROM PS_JOB B1 WHERE B1.EMPLID = B.EMPLID AND B1.EMPL_RCD = B.EMPL_RCD AND B1.EFFDT <= %CurrentDateIn) AND B.EFFSEQ = ( SELECT MAX(B3.EFFSEQ) FROM PS_JOB B3 WHERE B3.EMPLID = B.EMPLID AND B3.EMPL_RCD = B.EMPL_RCD AND B3.EFFDT = B.EFFDT) AND B.HR_STATUS = 'A')) 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(J1.EFFSEQ) FROM PS_JOB J1 WHERE J1.EMPLID = J.EMPLID AND J1.EMPL_RCD = J.EMPL_RCD AND J1.EFFDT = J.EFFDT) AND JC.SETID = J.SETID_JOBCODE AND JC.JOBCODE = J.JOBCODE AND JC.EFFDT = ( SELECT MAX(JC1.EFFDT) FROM PS_JOBCODE_TBL JC1 WHERE JC1.SETID = JC.SETID AND JC1.JOBCODE = JC.JOBCODE AND JC1.EFFDT <= J.EFFDT) AND JC.EFF_STATUS = 'A'

  • Related Language Record: JPM_APRJOB_LVW

    • # PeopleSoft Field Name PeopleSoft Field Type Database Column Type Description
    1 JPM_PROFILE_ID Character(12) VARCHAR2(12) NOT NULL The id, autoassigned or user assigned of the profile. This id is used to group items and other related material together into one coherent profile.
    2 JPM_JP_TRAN_NUM Number(12,0) DECIMAL(12) NOT NULL Transaction Number assigned by JPM when Profile updates are submitted for approval.
    3 JOB_DESCR Character(30) VARCHAR2(30) NOT NULL Job Description