EX_PERS_CORP_VW

(SQL View)
 Index Back

CC Personal vs Corp View

Personal vs corporate spendings using Credit Card

SELECT B.EMPLID , (CASE WHEN SUM(A.MONETARY_AMOUNT) IS NOT NULL THEN 'Y' ELSE 'N' END) FROM PS_EX_SHEET_LINE A , PS_EX_SHEET_HDR B , PS_EX_EXP_MTHD_TBL C , PS_EX_APRV_KIB_VW D WHERE A.SHEET_ID = B.SHEET_ID AND C.EXPEND_MTHD_EDIT = 'VND' AND A.PERSONAL_EXPENSE = 'Y' AND A.EX_LINE_STATUS <> 'PND' AND A.SHEET_ID = D.SHEET_ID AND D.APPROVAL_OPRID IN (SELECT Q.OPRID FROM PS_EX_EE_JOB_DT_VW P, PSOPRDEFN Q WHERE P.SUPERVISOR_ID = Q.EMPLID) AND D.APPROVAL_OPRID IS NOT NULL AND C.SETID = ( SELECT L.SETID FROM PS_SET_CNTRL_REC L WHERE L.REC_GROUP_ID = ( SELECT N.REC_GROUP_ID FROM PS_REC_GROUP_REC N WHERE N.RECNAME = 'EX_EXP_MTHD_TBL') AND L.RECNAME = 'EX_EXP_MTHD_TBL' AND L.SETCNTRLVALUE = B.BUSINESS_UNIT_GL) AND C.EXPEND_MTHD = A.EXPEND_MTHD AND C.EFFDT = ( SELECT MAX(C_ED.EFFDT) FROM PS_EX_EXP_MTHD_TBL C_ED WHERE C.SETID = C_ED.SETID AND C.EXPEND_MTHD = C_ED.EXPEND_MTHD AND C_ED.EFFDT <= A.TRANS_DT) GROUP BY B.EMPLID INTERSECT SELECT B.EMPLID , (CASE WHEN SUM(A.MONETARY_AMOUNT) IS NOT NULL THEN 'Y' ELSE 'N' END) FROM PS_EX_SHEET_LINE A , PS_EX_SHEET_HDR B , PS_EX_EXP_MTHD_TBL C , PS_EX_APRV_KIB_VW D WHERE A.SHEET_ID = B.SHEET_ID AND C.EXPEND_MTHD_EDIT = 'VND' AND A.PERSONAL_EXPENSE = 'N' AND A.EX_LINE_STATUS <> 'PND' AND A.SHEET_ID = D.SHEET_ID AND D.APPROVAL_OPRID IN (SELECT Q.OPRID FROM PS_EX_EE_JOB_DT_VW P, PSOPRDEFN Q WHERE P.SUPERVISOR_ID = Q.EMPLID) AND D.APPROVAL_OPRID IS NOT NULL AND C.SETID = ( SELECT L.SETID FROM PS_SET_CNTRL_REC L WHERE L.REC_GROUP_ID = ( SELECT N.REC_GROUP_ID FROM PS_REC_GROUP_REC N WHERE N.RECNAME = 'EX_EXP_MTHD_TBL') AND L.RECNAME = 'EX_EXP_MTHD_TBL' AND L.SETCNTRLVALUE = B.BUSINESS_UNIT_GL) AND C.EXPEND_MTHD = A.EXPEND_MTHD AND C.EFFDT = ( SELECT MAX(C_ED.EFFDT) FROM PS_EX_EXP_MTHD_TBL C_ED WHERE C.SETID = C_ED.SETID AND C.EXPEND_MTHD = C_ED.EXPEND_MTHD AND C_ED.EFFDT <= A.TRANS_DT) GROUP BY B.EMPLID

# PeopleSoft Field Name PeopleSoft Field Type Database Column Type Description
1 EMPLID Character(11) VARCHAR2(11) NOT NULL Employee ID
2 EX_CC_PERS_VS_CORP Character(1) VARCHAR2(1) NOT NULL Credit Card spendings - Personal vs Corporate