CS_CW_PODHDR_VW

(SQL View)
 Index Back

Doc Hdr joined to PO keys

SCMT003 - Join doc header to PO keys 11/2015 JOHAN Master contract changes

SELECT A.CS_DOC_ID , KEYS.BUSINESS_UNIT , KEYS.PO_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 , A.CNTRCT_BEGIN_DT , A.CNTRCT_EXPIRE_DT , 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.PO_STATUS , HDR.PO_REF , HDR.PO_DT , HDR.BUYER_ID , HDR.PO_TYPE , A.CS_EXT_SIGN_STATUS , A.CS_INT_SIGN_STATUS , A.CS_SIGN_PREP_DONE , %Substring(%Cast(HDR.PO_DT, DateTime, Character), 1, 4) , %Substring(%Cast(HDR.PO_DT, Datetime, Character), 9, 2) , %Substring(%Cast(HDR.PO_DT, DateTime, Character), 6, 2) , A.CNTRCT_STYLE , S.CNTRCT_STYLE_NAME FROM PS_CS_DOC_HDR A LEFT OUTER JOIN PS_CS_TMPL_TBL TT ON TT.SETID = A.CS_SETID AND TT.CS_TEMPLATE_ID = A.CS_TEMPLATE_ID ,PS_CS_DOC_PUR_KEYS KEYS ,PS_PO_HDR HDR ,PS_VENDOR V , PS_CNTRCT_STYLE S WHERE A.CS_DOC_ID = KEYS.CS_DOC_ID AND KEYS.BUSINESS_UNIT = HDR.BUSINESS_UNIT AND KEYS.PO_ID = HDR.PO_ID AND V.SETID = HDR.VENDOR_SETID AND V.VENDOR_ID = HDR.VENDOR_ID AND S.CNTRCT_STYLE = A.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 BUSINESS_UNIT Character(5) VARCHAR2(5) NOT NULL Business Unit
3 PO_ID Character(10) VARCHAR2(10) NOT NULL Purchase Order
4 DESCR60 Character(60) VARCHAR2(60) NOT NULL Description
5 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
6 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

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

Prompt Table: PSOPRDEFN_VW

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

Prompt Table: PSOPRDEFN_VW

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

Prompt Table: CS_DEPTID_VW

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

Prompt Table: SP_SETID_NONVW

11 CS_TMPL_TYPE Character(4) VARCHAR2(4) NOT NULL SCC 09/01/04
12 CS_TEMPLATE_ID Character(20) VARCHAR2(20) NOT NULL SCC 09/01/04

Prompt Table: CS_TEMPLATE_DVW

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

Y/N Table Edit

Default Value: N

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

Y/N Table Edit

Default Value: N

19 CNTRCT_BEGIN_DT Date(10) DATE Contract Begin Date
20 CNTRCT_EXPIRE_DT Date(10) DATE Expire Date
21 CREATED_DTTM DateTime(26) TIMESTAMP Created Date and time
22 CREATED_BY_USER Character(30) VARCHAR2(30) NOT NULL Created By
23 LAST_MODIFIED_DATE DateTime(26) TIMESTAMP Last modified date
24 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.
25 CS_IMPORT_CODE Character(1) VARCHAR2(1) NOT NULL Import Type
1=Authored
2=Imported
3=Hybrid
4=Automatic Import

Default Value: 1

26 CS_DOC_DUE_DATE Date(10) DATE Cycle Due Date
27 CS_DOC_START_DATE Date(10) DATE Cycle Start Date
28 CS_DOC_STATUS_DATE Date(10) DATE Last Status Change Date
29 VENDOR_SETID Character(5) VARCHAR2(5) NOT NULL Vendor SetID
30 VENDOR_ID Character(10) VARCHAR2(10) NOT NULL Vendor Identifier

Prompt Table: %EDITTABLE18

31 VENDOR_NAME_SHORT Character(14) VARCHAR2(14) NOT NULL Short Vendor Name
32 PO_STATUS Character(2) VARCHAR2(2) NOT NULL PO Status
A=Approved
C=Complete
D=Dispatched
DA=Denied
I=Initial
LD=Line Denied
O=Open
PA=Pending Approval/Approved
PX=Pending Cancel
X=Canceled
33 PO_REF Character(30) VARCHAR2(30) NOT NULL Specifies the purchase order number associated with a receivables item.
34 PO_DT Date(10) DATE Date
35 BUYER_ID Character(30) VARCHAR2(30) NOT NULL Buyer
36 PO_TYPE Character(4) VARCHAR2(4) NOT NULL PO Type
GEN=General
KAN=Kanban
37 CS_EXT_SIGN_STATUS Character(2) VARCHAR2(2) NOT NULL External Signature Status
NR=Not Initiated
PS=Pending Signatures
RJ=Declined
SS=Signed
38 CS_INT_SIGN_STATUS Character(2) VARCHAR2(2) NOT NULL Internal Signature Status
NR=Not Required
PS=Pending Signatures
SS=Signed
39 CS_SIGN_PREP_DONE Character(1) VARCHAR2(1) NOT NULL Adobe Signature Prep
40 SES_YEAR Character(4) VARCHAR2(4) NOT NULL Document Date Hierarchy
41 SES_DAY Character(2) VARCHAR2(2) NOT NULL Day
42 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
43 CNTRCT_STYLE Character(10) VARCHAR2(10) NOT NULL Contract Style
44 CNTRCT_STYLE_NAME Character(30) VARCHAR2(30) NOT NULL Style Description