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 |