SSF_CR_HIST_VW6

(SQL View)
Index Back

Credit History


SELECT A.BUSINESS_UNIT , A.EMPLID , A.EFFDT , A.AGING_SET , A.AGE_CATEGORY , B.SUMMARY_CATEGORY , B.DESCR , T.XLATLONGNAME , %Round(SUM(AMOUNT),2) FROM PS_CREDIT_HISTORY A , PS_AGING_CAT_TBL B , PSXLATITEM T WHERE B.SETID = ( SELECT C.SETID FROM PS_SET_CNTRL_REC C WHERE C.SETCNTRLVALUE=A.BUSINESS_UNIT AND C.REC_GROUP_ID = 'AGING' AND C.RECNAME='AGING_CAT_TBL') AND A.AGING_SET = B.AGING_SET AND A.AGE_CATEGORY = B.AGE_CATEGORY AND A.EMPLID > ' ' AND B.EFFDT = ( SELECT MAX(D.EFFDT) FROM PS_AGING_CAT_TBL D WHERE D.SETID = B.SETID AND D.AGING_SET = B.AGING_SET AND D.EFFDT <= %CurrentDateIn) AND A.EFFDT = ( SELECT MAX(E.EFFDT) FROM PS_CREDIT_HISTORY E WHERE E.BUSINESS_UNIT = A.BUSINESS_UNIT AND E.EMPLID = A.EMPLID AND E.EXT_ORG_ID = E.EXT_ORG_ID AND E.EFFDT <= %CurrentDateIn) AND T.FIELDNAME = 'SUMMARY_CATEGORY' AND T.FIELDVALUE = B.SUMMARY_CATEGORY AND T.EFF_STATUS = 'A' GROUP BY A.BUSINESS_UNIT, A.EMPLID, A.EFFDT, A.AGING_SET, A.AGE_CATEGORY, B.SUMMARY_CATEGORY,B.DESCR,T.XLATLONGNAME

# PeopleSoft Field Name PeopleSoft Field Type Database Column Type Description
1 BUSINESS_UNIT Character(5) VARCHAR2(5) NOT NULL Business Unit
2 EMPLID Character(11) VARCHAR2(11) NOT NULL Employee ID
3 EFFDT Date(10) DATE Effective Date

Default Value: %date

4 AGING_SET Character(5) VARCHAR2(5) NOT NULL Aging Set
5 AGE_CATEGORY Character(2) VARCHAR2(2) NOT NULL Aging Category
6 SUMMARY_CATEGORY Character(1) VARCHAR2(1) NOT NULL Summary Category
C=Current Due
D=Dispute Due
F=Future Due
O=Other
P=Past Due
7 DESCR Character(30) VARCHAR2(30) NOT NULL Description
8 XLATLONGNAME Character(30) VARCHAR2(30) NOT NULL Translate Long Name
9 SSF_AMOUNT Signed Number(19,2) DECIMAL(17,2) NOT NULL Amount