CS_CW_CNTDHD_VW

(SQL View)
 Index Back

Doc Hdr joined to CNT

SCMT003 - Join doc header to Contract and Key info 11/2015 JOHAN Master contract changes PO002 Manufacturer Contract.

SELECT A.CS_DOC_ID , HDR.SETID , HDR.CNTRCT_ID , HDR.VERSION_NBR , HDR.CNTRCT_ID , A.DESCR60 , A.CS_DOC_STATUS , A.CS_DOCUMENT_TYPE , A.CS_DOC_OWNER , A.CS_SPONSOR , A.DEPTID , A.CS_SETID , TT.CS_TMPL_TYPE , A.CS_TEMPLATE_ID , A.CS_TEMPLATE_AMEND , A.CS_VERSION , A.CS_AMENDMENT , A.CS_SOURCE_TRANS , A.CLOSE_FLAG , A.HOLD_FLAG , HDR.CNTRCT_BEGIN_DT , HDR.CNTRCT_EXPIRE_DT , HDR.APPROVAL_DT , HDR.RENEWAL_DATE , A.CREATED_DTTM , A.CREATED_BY_USER , A.LAST_MODIFIED_DATE , A.LASTUPDOPRID , A.CS_IMPORT_CODE , A.CS_DOC_DUE_DATE , A.CS_DOC_START_DATE , A.CS_DOC_STATUS_DATE , HDR.VENDOR_SETID , HDR.VENDOR_ID ,V.VENDOR_NAME_SHORT , HDR.CNTRCT_STATUS , HDR.CNTRCT_PROC_OPT , HDR.VNDR_CNTRCT_REF , HDR.DESCR , HDR.MSTR_CNTRCT_ID , HDR.BUYER_ID , HDR.VERSION_STATUS , A.CS_EXT_SIGN_STATUS , A.CS_INT_SIGN_STATUS , A.CS_SIGN_PREP_DONE , %Substring(%Cast(%COALESCE(A.LAST_MODIFIED_DATE,HDR.LAST_DTTM_UPDATE,%Dttm(HDR.CNTRCT_BEGIN_DT, %TIMEIN('00.00.00.000000'))), DateTime, Character), 1, 4) , %Substring(%Cast(%COALESCE(A.LAST_MODIFIED_DATE,HDR.LAST_DTTM_UPDATE,%Dttm(HDR.CNTRCT_BEGIN_DT, %TIMEIN('00.00.00.000000'))), Datetime, Character), 9, 2) , %Substring(%Cast(%COALESCE(A.LAST_MODIFIED_DATE,HDR.LAST_DTTM_UPDATE,%Dttm(HDR.CNTRCT_BEGIN_DT, %TIMEIN('00.00.00.000000'))), DateTime, Character), 6, 2) , %Coalesce(A.LAST_MODIFIED_DATE,HDR.LAST_DTTM_UPDATE,%Dttm(HDR.CNTRCT_BEGIN_DT, %TIMEIN('00.00.00.000000'))) , %Coalesce(A.CS_SOURCE_TRANS,'02') , CASE WHEN A.CS_DOC_ID IS NULL THEN 'N' ELSE 'Y' END , HDR.CNTRCT_STYLE , S.CNTRCT_STYLE_NAME ,HDR.CONTROL_TYPE FROM PS_CNTRCT_HDR HDR LEFT OUTER JOIN ( PS_CS_DOC_PO_KEYS KEYS JOIN PS_CS_DOC_HDR A ON KEYS.CS_DOC_ID = A.CS_DOC_ID LEFT OUTER JOIN PS_CS_TMPL_TBL TT ON TT.SETID = A.CS_SETID AND TT.CS_TEMPLATE_ID = A.CS_TEMPLATE_ID ) ON HDR.SETID = KEYS.SETID AND HDR.CNTRCT_ID = KEYS.CNTRCT_ID AND HDR.VERSION_NBR = KEYS.VERSION_NBR AND A.CS_DOC_ID = KEYS.CS_DOC_ID JOIN PS_VENDOR V ON V.SETID = HDR.VENDOR_SETID AND V.VENDOR_ID = HDR.VENDOR_ID AND HDR.VERSION_NBR = ( SELECT MAX(c2.version_nbr) FROM ps_cntrct_hdr c2 WHERE c2.setid = hdr.setid AND c2.cntrct_id = hdr.cntrct_id) JOIN PS_CNTRCT_STYLE S ON S.CNTRCT_STYLE = HDR.CNTRCT_STYLE

# PeopleSoft Field Name PeopleSoft Field Type Database Column Type Description
1 CS_DOC_ID Character(25) VARCHAR2(25) NOT NULL Document ID
2 SETID Character(5) VARCHAR2(5) NOT NULL SetID
3 CNTRCT_ID Character(25) VARCHAR2(25) NOT NULL Buying Agreement ID

Default Value: NEXT

4 VERSION_NBR Number(5,0) INTEGER NOT NULL Contract Version number

Default Value: 1

5 CS_CONTENT_ID Character(30) VARCHAR2(30) NOT NULL Content ID
6 DESCR60 Character(60) VARCHAR2(60) NOT NULL Description
7 CS_DOC_STATUS Character(2) VARCHAR2(2) NOT NULL Document Status
AA=Approved
AD=Denied
AP=Pending Approval
CC=Collaborated
CP=Pending Collaboration
CR=Collaborated, Pending Review
DC=Complete
DR=Draft
DS=Dispatched
EC=Pending External Collaboration
EX=Executed
PI=Pending Internal Review
8 CS_DOCUMENT_TYPE Character(20) VARCHAR2(20) NOT NULL Document Type
001=Main Document
002=Amendment
003=Preview Document
004=Compare File

Prompt Table: CS_DOCT_ALL_VW

9 CS_DOC_OWNER Character(30) VARCHAR2(30) NOT NULL Document Administrator

Prompt Table: PSOPRDEFN_VW

10 CS_SPONSOR Character(30) VARCHAR2(30) NOT NULL Sponsor

Prompt Table: PSOPRDEFN_VW

11 DEPTID Character(10) VARCHAR2(10) NOT NULL Department

Prompt Table: CS_DEPTID_VW

12 CS_SETID Character(5) VARCHAR2(5) NOT NULL setid for the document objects

Prompt Table: SP_SETID_NONVW

13 CS_TMPL_TYPE Character(4) VARCHAR2(4) NOT NULL SCC 09/01/04
14 CS_TEMPLATE_ID Character(20) VARCHAR2(20) NOT NULL SCC 09/01/04

Prompt Table: CS_TEMPLATE_DVW

15 CS_TEMPLATE_AMEND Character(20) VARCHAR2(20) NOT NULL SCC 09/01/04
16 CS_VERSION Number(6,2) DECIMAL(5,2) NOT NULL Version
17 CS_AMENDMENT Number(3,0) SMALLINT NOT NULL Amendment
18 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
19 CLOSE_FLAG Character(1) VARCHAR2(1) NOT NULL Close Contract Flag

Y/N Table Edit

Default Value: N

20 HOLD_FLAG Character(1) VARCHAR2(1) NOT NULL Hold
N=Hold
Y=Hold

Y/N Table Edit

Default Value: N

21 CNTRCT_BEGIN_DT Date(10) DATE Contract Begin Date
22 CNTRCT_EXPIRE_DT Date(10) DATE Expire Date
23 APPROVAL_DT Date(10) DATE Date of Approval
24 RENEWAL_DATE Date(10) DATE Renewal Date
25 CREATED_DTTM DateTime(26) TIMESTAMP Created Date and time
26 CREATED_BY_USER Character(30) VARCHAR2(30) NOT NULL Created By
27 LAST_MODIFIED_DATE DateTime(26) TIMESTAMP Last modified date
28 LASTUPDOPRID Character(30) VARCHAR2(30) NOT NULL Specifies the User ID which made the last update to an entry. This field is maintained by PeopleSoft and is used in a variety of contexts.
29 CS_IMPORT_CODE Character(1) VARCHAR2(1) NOT NULL Import Type
1=Authored
2=Imported
3=Hybrid
4=Automatic Import

Default Value: 1

30 CS_DOC_DUE_DATE Date(10) DATE Cycle Due Date
31 CS_DOC_START_DATE Date(10) DATE Cycle Start Date
32 CS_DOC_STATUS_DATE Date(10) DATE Last Status Change Date
33 VENDOR_SETID Character(5) VARCHAR2(5) NOT NULL Vendor SetID
34 VENDOR_ID Character(10) VARCHAR2(10) NOT NULL Vendor Identifier

Prompt Table: %EDITTABLE18

35 VENDOR_NAME_SHORT Character(14) VARCHAR2(14) NOT NULL Short Vendor Name
36 CNTRCT_STATUS Character(1) VARCHAR2(1) NOT NULL Contract Status
A=Approved
C=Closed
H=On-Hold
O=Open
P=Pre-Approved
X=Canceled

Default Value: O

37 CNTRCT_PROC_OPT Character(4) VARCHAR2(4) NOT NULL Contract Process Option
AP=Recurring Voucher
BPO=Release to Single PO Only
DST=Distributor
GN=General Contract
GRPM=Group Multi Supplier
GRPS=Group Single Supplier
MFG=Manufacturer
PADV=Prepaid Voucher w/ Advance PO
PO=Purchase Order
PPAY=Prepaid Voucher
RPOV=Recurring PO Voucher
SPP=Special Purpose

Default Value: GN

38 VNDR_CNTRCT_REF Character(30) VARCHAR2(30) NOT NULL Supplier Contract Ref
39 DESCR Character(30) VARCHAR2(30) NOT NULL Description
40 MSTR_CNTRCT_ID Character(10) VARCHAR2(10) NOT NULL Master Contract ID

Prompt Table: CNTRCT_MSTR_VW

41 BUYER_ID Character(30) VARCHAR2(30) NOT NULL Buyer
42 VERSION_STATUS Character(1) VARCHAR2(1) NOT NULL Contract version status
C=Current
D=Draft
H=History
43 CS_EXT_SIGN_STATUS Character(2) VARCHAR2(2) NOT NULL External Signature Status
NR=Not Initiated
PS=Pending Signatures
RJ=Declined
SS=Signed
44 CS_INT_SIGN_STATUS Character(2) VARCHAR2(2) NOT NULL Internal Signature Status
NR=Not Required
PS=Pending Signatures
SS=Signed
45 CS_SIGN_PREP_DONE Character(1) VARCHAR2(1) NOT NULL Adobe Signature Prep
46 SES_YEAR Character(4) VARCHAR2(4) NOT NULL Document Date Hierarchy
47 SES_DAY Character(2) VARCHAR2(2) NOT NULL Day
48 SES_MONTH Character(2) VARCHAR2(2) NOT NULL Month
01=01: January
02=02: February
03=03: March
04=04: April
05=05: May
06=06: June
07=07: July
08=08: August
09=09: September
10=10: October
11=11: November
12=12: December
49 SES_LAST_DTTM DateTime(26) TIMESTAMP PTSF Last Modified DateTimestamp
50 CS_CWB_TRANS_SES 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
51 CS_DOC_AVAILABLE Character(1) VARCHAR2(1) NOT NULL 03/11/2015 JOHAN SCMT009 Adding PTSF indexing to Supplier Contract Workbench
52 CNTRCT_STYLE Character(10) VARCHAR2(10) NOT NULL Contract Style
53 CNTRCT_STYLE_NAME Character(30) VARCHAR2(30) NOT NULL Style Description
54 CONTROL_TYPE Character(4) VARCHAR2(4) NOT NULL PO002 Manufacturer Contract
BU=Business Unit
CDOM=Contract Domain
SHIP=Ship To
X=Not Used