CS_CW_AUCDHD_VW

(SQL View)
 Index Back

Doc Hdr joined to AUC keys

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

SELECT A.CS_DOC_ID , KEYS.BUSINESS_UNIT , KEYS.AUC_ID , KEYS.AUC_ROUND , KEYS.AUC_VERSION , 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.AUC_FORMAT , HDR.AUC_TYPE , HDR.AUC_NAME , HDR.AUC_STATUS , HDR.AUC_DTTM_START , HDR.AUC_DTTM_FINISH , HDR.BUYER_ID , HDR.NAME1 , %Substring(%Cast(HDR.AUC_DTTM_START, DateTime, Character), 1, 4) , %Substring(%Cast(HDR.AUC_DTTM_START, Datetime, Character), 9, 2) , %Substring(%Cast(HDR.AUC_DTTM_START, DateTime, Character), 6, 2) , A.CNTRCT_STYLE , S.CNTRCT_STYLE_NAME FROM PS_CS_DOC_HDR A ,PS_CS_DOC_AUC_KEYS KEYS ,PS_AUC_HDR HDR , PS_CS_TMPL_TBL tt , PS_CNTRCT_STYLE S WHERE A.CS_DOC_ID = KEYS.CS_DOC_ID AND KEYS.BUSINESS_UNIT = HDR.BUSINESS_UNIT AND KEYS.AUC_ID = HDR.AUC_ID AND KEYS.AUC_ROUND = HDR.AUC_ROUND AND KEYS.AUC_VERSION = HDR.AUC_VERSION AND TT.SETID = A.CS_SETID AND TT.CS_TEMPLATE_ID = A.CS_TEMPLATE_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 AUC_ID Character(10) VARCHAR2(10) NOT NULL Event ID
4 AUC_ROUND Number(5,0) INTEGER NOT NULL Event Round
5 AUC_VERSION Number(5,0) INTEGER NOT NULL Event Version
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 CREATED_DTTM DateTime(26) TIMESTAMP Created Date and time
24 CREATED_BY_USER Character(30) VARCHAR2(30) NOT NULL Created By
25 LAST_MODIFIED_DATE DateTime(26) TIMESTAMP Last modified date
26 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.
27 CS_IMPORT_CODE Character(1) VARCHAR2(1) NOT NULL Import Type
1=Authored
2=Imported
3=Hybrid
4=Automatic Import

Default Value: 1

28 CS_DOC_DUE_DATE Date(10) DATE Cycle Due Date
29 CS_DOC_START_DATE Date(10) DATE Cycle Start Date
30 CS_DOC_STATUS_DATE Date(10) DATE Last Status Change Date
31 AUC_FORMAT Character(1) VARCHAR2(1) NOT NULL Event Format
F=Sell
I=RFI
R=Buy
S=Service
32 AUC_TYPE Character(1) VARCHAR2(1) NOT NULL Event Type
E=Auction
S=RFx
33 AUC_NAME Character(50) VARCHAR2(50) NOT NULL Event Name
34 AUC_STATUS Character(1) VARCHAR2(1) NOT NULL Event Status
A=Awarded
C=Collaborating Event
D=Collaborating Bid Analysis
E=Evaluating
F=RFI Reviewed
I=Pending RFI Review
N=Not Awarded
O=Open
P=Posted
Q=Paused
R=Pending Scheduled Review
S=Pending Post Approval
T=Pending Award
U=Pending Award Approval
W=Post to Third Party Failed
X=Cancelled
Z=Inactive Version
35 AUC_DTTM_START DateTime(26) TIMESTAMP Date Time Start
36 AUC_DTTM_FINISH DateTime(26) TIMESTAMP Date Time Finish
37 BUYER_ID Character(30) VARCHAR2(30) NOT NULL Buyer

Prompt Table: BUYER_ACTIVE_VW

38 NAME1 Character(40) VARCHAR2(40) NOT NULL Name 1
39 SES_YEAR Character(4) VARCHAR2(4) NOT NULL Document Date Hierarchy
40 SES_DAY Character(2) VARCHAR2(2) NOT NULL Day
41 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
42 CNTRCT_STYLE Character(10) VARCHAR2(10) NOT NULL Contract Style
43 CNTRCT_STYLE_NAME Character(30) VARCHAR2(30) NOT NULL Style Description