CS_DOC_ALL_VW2

(SQL View)
 Index Back

Contract Document Header & Key

Joins cs_doc_hdr to cs_doc_all_keys but just basic fields, used in reporting

SELECT D.CS_DOC_ID , D.CS_SETID , D.CREATED_DTTM , D.LAST_MODIFIED_DATE , D.CS_SOURCE_TRANS , CASE D.CS_SOURCE_TRANS WHEN '01' THEN A3.CS_SETID WHEN '02' THEN C.SETID WHEN '03' THEN P.BUSINESS_UNIT END , CASE D.CS_SOURCE_TRANS WHEN '01' THEN A3.CS_ADHOC_ID WHEN '02' THEN C.CNTRCT_ID WHEN '03' THEN P.PO_ID END , A3.CS_SETID , A3.CS_ADHOC_ID , C.SETID , C.CNTRCT_ID , P.BUSINESS_UNIT , P.PO_ID , CASE D.CS_SOURCE_TRANS WHEN '01' THEN D.CNTRCT_BEGIN_DT WHEN '02' THEN C.CNTRCT_BEGIN_DT WHEN '03' THEN P.PO_DT END , CASE D.CS_SOURCE_TRANS WHEN '01' THEN D.CNTRCT_EXPIRE_DT WHEN '02' THEN C.CNTRCT_EXPIRE_DT END , CASE D.CS_SOURCE_TRANS WHEN '01' THEN ' ' WHEN '02' THEN C.VENDOR_SETID WHEN '03' THEN P.VENDOR_SETID END , CASE D.CS_SOURCE_TRANS WHEN '01' THEN ' ' WHEN '02' THEN C.VENDOR_ID WHEN '03' THEN P.VENDOR_ID END , C.CNTRCT_STATUS FROM PS_CS_DOC_HDR D , (PS_CS_DOC_ALL_KEYS A1 LEFT OUTER JOIN PS_CNTRCT_HDR C ON C.SETID = A1.CNTRCT_SETID AND C.CNTRCT_ID = A1.CNTRCT_ID AND C.VERSION_NBR = A1.VERSION_NBR ) , (PS_CS_DOC_ALL_KEYS A2 LEFT OUTER JOIN PS_PO_HDR P ON P.BUSINESS_UNIT = A2.BUSINESS_UNIT AND P.PO_ID = A2.PO_ID ) , PS_CS_DOC_ALL_KEYS A3 WHERE D.CS_DOC_ID = A1.CS_DOC_ID AND D.CS_DOC_ID = A2.CS_DOC_ID AND D.CS_DOC_ID = A3.CS_DOC_ID

# PeopleSoft Field Name PeopleSoft Field Type Database Column Type Description
1 CS_DOC_ID Character(25) VARCHAR2(25) NOT NULL Document ID
2 CS_SETID Character(5) VARCHAR2(5) NOT NULL setid for the document objects
3 CREATED_DTTM DateTime(26) TIMESTAMP Created Date and time
4 LAST_MODIFIED_DATE DateTime(26) TIMESTAMP Last modified date
5 CS_SOURCE_TRANS Character(2) VARCHAR2(2) NOT NULL Source Transaction 03/22/07 cel F-AHAYT-748L7: Added Purchase Order (03) as a new source transaction 01/12/11 tbg SCMT-92-MDEM-04: Added RFx (04) as new source transaction for strat sourcing (doc authoring)
01=Ad Hoc
02=Purchasing Contracts
03=Purchase Orders
04=Sourcing Events
97=All Sources
98=Multiple Sources
99=Not Transactional
6 CS_SETID_VAR Character(5) VARCHAR2(5) NOT NULL Variable SetID
7 CS_CNTRCT_VAR Character(25) VARCHAR2(25) NOT NULL Variable Contract Document ID
8 SETID Character(5) VARCHAR2(5) NOT NULL SetID
9 CS_ADHOC_ID Character(25) VARCHAR2(25) NOT NULL Ad Hoc ID
10 CNTRCT_SETID Character(5) VARCHAR2(5) NOT NULL SetID
11 CNTRCT_ID Character(25) VARCHAR2(25) NOT NULL Buying Agreement ID
12 BUSINESS_UNIT Character(5) VARCHAR2(5) NOT NULL Business Unit
13 PO_ID Character(10) VARCHAR2(10) NOT NULL Purchase Order
14 CNTRCT_BEGIN_DT Date(10) DATE Contract Begin Date
15 CNTRCT_EXPIRE_DT Date(10) DATE Expire Date
16 VENDOR_SETID Character(5) VARCHAR2(5) NOT NULL Vendor SetID
17 VENDOR_ID Character(10) VARCHAR2(10) NOT NULL Vendor Identifier
18 CNTRCT_STATUS Character(1) VARCHAR2(1) NOT NULL Contract Status
A=Approved
C=Closed
H=On-Hold
O=Open
P=Pre-Approved
X=Canceled