CNTRCT_CATS_SVW

(SQL View)
 Index Back

Contract PTSF Categories

Line and Category

SELECT DISTINCT HDR.SETID , HDR.CNTRCT_ID , ALL_CATS.CATEGORY_ID , ALL_CATS.CATEGORY_CD , ALL_CATS.DESCR60 , ALL_CATS.DESCRSHORT FROM PS_CNTRCT_HDR HDR LEFT OUTER JOIN ( SELECT DISTINCT A.SETID , A.CNTRCT_ID , A.CATEGORY_ID , B.CATEGORY_CD , B.DESCR60 , B.DESCRSHORT FROM PS_CNTRCT_LINE A , PS_ITM_CAT_TBL B WHERE ( A.SETID = B.SETID AND B.CATEGORY_ID = A.CATEGORY_ID AND B.EFFDT = ( SELECT MAX(B_ED.EFFDT) FROM PS_ITM_CAT_TBL B_ED WHERE B.SETID = B_ED.SETID AND B.CATEGORY_TYPE = B_ED.CATEGORY_TYPE AND B.CATEGORY_CD = B_ED.CATEGORY_CD AND B.CATEGORY_ID = B_ED.CATEGORY_ID AND B_ED.EFFDT <= %CurrentDateIn) ) UNION SELECT DISTINCT A.SETID , A.CNTRCT_ID , A.CATEGORY_ID , B.CATEGORY_CD , B.DESCR60 , B.DESCRSHORT FROM PS_CNTRCT_CATEGORY A , PS_ITM_CAT_TBL B WHERE ( A.SETID = B.SETID AND B.CATEGORY_ID = A.CATEGORY_ID AND B.EFFDT = ( SELECT MAX(B_ED.EFFDT) FROM PS_ITM_CAT_TBL B_ED WHERE B.SETID = B_ED.SETID AND B.CATEGORY_TYPE = B_ED.CATEGORY_TYPE AND B.CATEGORY_CD = B_ED.CATEGORY_CD AND B.CATEGORY_ID = B_ED.CATEGORY_ID AND B_ED.EFFDT <= %CurrentDateIn)) ) ALL_CATS ON HDR.SETID = ALL_CATS.SETID AND HDR.CNTRCT_ID = ALL_CATS.CNTRCT_ID

# PeopleSoft Field Name PeopleSoft Field Type Database Column Type Description
1 SETID Character(5) VARCHAR2(5) NOT NULL SetID
2 CNTRCT_ID Character(25) VARCHAR2(25) NOT NULL Buying Agreement ID
3 CATEGORY_ID Character(5) VARCHAR2(5) NOT NULL Category ID
4 CATEGORY_CD Character(18) VARCHAR2(18) NOT NULL Category Code
5 DESCR60 Character(60) VARCHAR2(60) NOT NULL Description
6 DESCRSHORT Character(10) VARCHAR2(10) NOT NULL Short Description