CS_PURCOPY_VW (SQL View)	Index Back
Contract Search for PO's

CS_PURCOPY_VW

(SQL View)

Contract Search for PO's

SELECT B.BUSINESS_UNIT , B.PO_ID , H.BUYER_ID , A.CNTRCT_BEGIN_DT , A.CNTRCT_EXPIRE_DT , A.CS_DOC_ID , A.CS_DOC_OWNER , A.CS_DOC_STATUS , A.CS_DOCUMENT_TYPE , A.CS_SETID , A.CS_SPONSOR , A.DEPTID , A.DESCR60 , H.ORIGIN , H.PO_DT , H.PO_REF , H.PO_STATUS , H.VENDOR_ID , V.VENDOR_NAME_SHORT , A.CS_TEMPLATE_ID , F.CS_FILE_FORMAT , F.CS_RELXD_CHKIN , F.SCM_ATTACH_ID , F.ATT_VERSION FROM PS_CS_DOC_HDR A , PS_CS_DOC_PUR_KEYS B , PS_PO_HDR H , PS_VENDOR V , PS_CS_DOC_FILES F WHERE A.CS_SOURCE_TRANS = '03' AND A.CS_IMPORT_CODE = '1' AND A.CS_DOC_BLD_STATUS = '1' AND B.CS_DOC_ID = A.CS_DOC_ID AND H.BUSINESS_UNIT = B.BUSINESS_UNIT AND H.PO_ID = B.PO_ID AND V.SETID = H.VENDOR_SETID AND V.VENDOR_ID = H.VENDOR_ID AND F.CS_DOC_ID = A.CS_DOC_ID AND F.CS_DOC_TYPE = '001'

# PeopleSoft Field Name PeopleSoft Field Type Database Column Type Description

1 BUSINESS_UNIT Character(5) VARCHAR2(5) NOT NULL Business Unit

2 PO_ID Character(10) VARCHAR2(10) NOT NULL Purchase Order

3 BUYER_ID Character(30) VARCHAR2(30) NOT NULL Buyer

4 CNTRCT_BEGIN_DT Date(10) DATE Contract Begin Date

5 CNTRCT_EXPIRE_DT Date(10) DATE Expire Date

6 CS_DOC_ID Character(25) VARCHAR2(25) NOT NULL Document ID

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

8 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

9 CS_DOCUMENT_TYPE Character(20) VARCHAR2(20) NOT NULL Document Type
001=Main Document
002=Amendment
003=Preview Document
004=Compare File

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

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

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

13 DESCR60 Character(60) VARCHAR2(60) NOT NULL Description

14 ORIGIN Character(3) VARCHAR2(3) NOT NULL Origin

15 PO_DT Date(10) DATE Date

16 PO_REF Character(30) VARCHAR2(30) NOT NULL Specifies the purchase order number associated with a receivables item.

17 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

18 VENDOR_ID Character(10) VARCHAR2(10) NOT NULL Vendor Identifier

19 VENDOR_NAME_SHORT Character(14) VARCHAR2(14) NOT NULL Short Vendor Name

20 CS_TEMPLATE_ID Character(20) VARCHAR2(20) NOT NULL SCC 09/01/04

21 CS_FILE_FORMAT Character(1) VARCHAR2(1) NOT NULL Created in 9.2 to indicate xml version - word xml vs. open
0=Imported or Signature Document
1=Word 2003 XML
2=Word Open XML

22 CS_RELXD_CHKIN Character(1) VARCHAR2(1) NOT NULL Relaxed CheckIn Used

23 SCM_ATTACH_ID Character(64) VARCHAR2(64) NOT NULL The unique ID of each file whose attachment was handled by the attachment utility. Unique across all attachments, all applications.

24 ATT_VERSION Number(3,0) SMALLINT NOT NULL The version of the attached file. Currently always 1, this field will find more use once the attachment utitlity starts supporting versioning of files.

#	PeopleSoft Field Name	PeopleSoft Field Type	Database Column Type	Description
1	BUSINESS_UNIT	Character(5)	VARCHAR2(5) NOT NULL	Business Unit
2	PO_ID	Character(10)	VARCHAR2(10) NOT NULL	Purchase Order
3	BUYER_ID	Character(30)	VARCHAR2(30) NOT NULL	Buyer
4	CNTRCT_BEGIN_DT	Date(10)	DATE	Contract Begin Date
5	CNTRCT_EXPIRE_DT	Date(10)	DATE	Expire Date
6	CS_DOC_ID	Character(25)	VARCHAR2(25) NOT NULL	Document ID
7	CS_DOC_OWNER	Character(30)	VARCHAR2(30) NOT NULL	Document Administrator
8	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
9	CS_DOCUMENT_TYPE	Character(20)	VARCHAR2(20) NOT NULL	Document Type 001=Main Document 002=Amendment 003=Preview Document 004=Compare File
10	CS_SETID	Character(5)	VARCHAR2(5) NOT NULL	setid for the document objects
11	CS_SPONSOR	Character(30)	VARCHAR2(30) NOT NULL	Sponsor
12	DEPTID	Character(10)	VARCHAR2(10) NOT NULL	Department
13	DESCR60	Character(60)	VARCHAR2(60) NOT NULL	Description
14	ORIGIN	Character(3)	VARCHAR2(3) NOT NULL	Origin
15	PO_DT	Date(10)	DATE	Date
16	PO_REF	Character(30)	VARCHAR2(30) NOT NULL	Specifies the purchase order number associated with a receivables item.
17	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
18	VENDOR_ID	Character(10)	VARCHAR2(10) NOT NULL	Vendor Identifier
19	VENDOR_NAME_SHORT	Character(14)	VARCHAR2(14) NOT NULL	Short Vendor Name
20	CS_TEMPLATE_ID	Character(20)	VARCHAR2(20) NOT NULL	SCC 09/01/04
21	CS_FILE_FORMAT	Character(1)	VARCHAR2(1) NOT NULL	Created in 9.2 to indicate xml version - word xml vs. open 0=Imported or Signature Document 1=Word 2003 XML 2=Word Open XML
22	CS_RELXD_CHKIN	Character(1)	VARCHAR2(1) NOT NULL	Relaxed CheckIn Used
23	SCM_ATTACH_ID	Character(64)	VARCHAR2(64) NOT NULL	The unique ID of each file whose attachment was handled by the attachment utility. Unique across all attachments, all applications.
24	ATT_VERSION	Number(3,0)	SMALLINT NOT NULL	The version of the attached file. Currently always 1, this field will find more use once the attachment utitlity starts supporting versioning of files.