CS_DOC_H_ALL_VW

(SQL View)
 Index Back

Contract Document Header

9/8/08 RMK CS_ADHOC_SETID - if source transaction is adhoc doc, then get setid from transaction record cs_doc_hdr_tmp, otherwise leave it blank. Was incorrect when we were getting setid from cs_doc_hdr.cs_setid

SELECT DISTINCT '0008' ,H.CS_DOC_ID ,TXN.SETID ,TXN.CNTRCT_ID ,F.CS_DOC_TYPE ,F.SCM_ATTACH_ID ,F.ATT_VERSION ,TXN.BUSINESS_UNIT ,H.DESCR60 ,H.CS_DOC_STATUS ,H.CS_DOCUMENT_TYPE ,H.CS_DOC_OWNER ,H.CS_SPONSOR ,H.DEPTID ,H.CS_SETID , CASE h.cs_source_trans WHEN '01' THEN txn.setid ELSE ' ' END ,TXN.CS_TMPL_TYPE , CASE f.cs_doc_type WHEN '010' THEN txn.cs_ex_summ_tmpl_id WHEN '002' THEN txn.cs_template_amend WHEN '096' THEN txn.cs_template_amend ELSE txn.cs_template_id END ,H.CS_VERSION ,H.CS_AMENDMENT ,H.CS_TWOFILE_AMEND ,H.CS_SOURCE_TRANS ,H.CS_TRANKEY_REC ,H.CREATED_DTTM ,H.CREATED_BY_USER ,H.DATETIME_CHECKOUT ,H.CHECKED_OUT_BY ,H.COMMENT1 ,H.COLLABED_DTTM ,H.APPROVAL_DTTM ,H.DATETIME_DISPATCH ,H.EXECUTED_DTTM ,TXN.CNTRCT_BEGIN_DT ,TXN.CNTRCT_EXPIRE_DT ,TXN.LAST_MODIFIED_DATE ,H.LASTUPDOPRID ,H.CLOSE_FLAG ,H.CS_IMPORT_CODE ,F.CS_IMPORT_FLG ,F.ATTACH_DESCR ,F.FILENAME ,TXN.AMT_CNTRCT_MAX ,TXN.AMT_RELEASED ,TXN.AMT_REMAINING ,TXN.LAST_MODIFIED_DATE ,TXN.PO_ID ,TXN.AMT_OPEN ,H.CS_CHAR_USER1 ,H.CS_CHAR_USER2 ,H.CS_CHAR_USER3 ,H.CS_CHAR_USER4 ,H.CS_CHAR_USER5 ,H.CS_CHAR_USER6 ,H.CS_CHAR_USER7 ,H.CS_CHAR_USER8 ,H.CS_CHAR_USER9 ,H.CS_CHAR_USER10 ,H.CS_CHAR_USER11 ,H.CS_CHAR_USER12 ,H.CS_CHAR_USER13 ,H.CS_CHAR_USER14 ,H.CS_CHAR_USER15 ,H.CS_CHAR_USER16 ,H.CS_CHAR_USER17 ,H.CS_CHAR_USER18 ,H.CS_CHAR_USER19 ,H.CS_CHAR_USER20 ,H.CS_CHAR_USER21 ,H.CS_CHAR_USER22 ,H.CS_CHAR_USER23 ,H.CS_CHAR_USER24 ,H.CS_CHAR_USER25 ,H.CS_CHAR_USER26 ,H.CS_CHAR_USER27 ,H.CS_CHAR_USER28 ,H.CS_CHAR_USER29 ,H.CS_CHAR_USER30 ,H.CS_CHAR_USER31 ,H.CS_CHAR_USER32 ,H.CS_CHAR_USER33 ,H.CS_CHAR_USER34 ,H.CS_CHAR_USER35 ,H.CS_CHAR_USER36 ,H.CS_CHAR_USER37 ,H.CS_CHAR_USER38 ,H.CS_DATE_USER1 ,H.CS_DATE_USER2 ,H.CS_DATE_USER3 ,H.CS_DATE_USER4 ,H.CS_DATE_USER5 ,H.CS_DATE_USER6 ,H.CS_DATE_USER7 ,H.CS_DATE_USER8 ,H.CS_DATE_USER9 ,H.CS_DATE_USER10 ,H.CS_DATE_USER11 ,H.CS_DATE_USER12 ,H.CS_DATE_USER13 ,H.CS_DATE_USER14 ,H.CS_DATE_USER15 ,H.CS_DEC_USER1 ,H.CS_DEC_USER2 ,H.CS_DEC_USER3 ,H.CS_DEC_USER4 ,H.CS_DEC_USER5 ,H.CS_DEC_USER6 ,H.CS_DEC_USER7 ,H.CS_DEC_USER8 ,H.CS_DEC_USER9 ,H.CS_DEC_USER10 ,H.CS_DEC_USER11 ,H.CS_DEC_USER12 ,H.CS_DEC_USER13 ,H.CS_DEC_USER14 ,H.CS_DEC_USER15 ,H.CS_INT_USER1 ,H.CS_INT_USER2 ,H.CS_INT_USER3 ,H.CS_INT_USER4 ,H.CS_INT_USER5 ,H.CS_INT_USER6 ,H.CS_INT_USER7 ,H.CS_INT_USER8 ,H.CS_INT_USER9 ,H.CS_INT_USER10 ,H.CS_INT_USER11 ,H.CS_INT_USER12 ,H.CS_INT_USER13 ,H.CS_INT_USER14 ,H.CS_INT_USER15 ,H.CS_INT_USER16 ,H.CS_INT_USER17 ,H.CS_INT_USER18 ,H.CS_INT_USER19 ,H.CS_INT_USER20 ,H.CS_INT_USER21 ,H.CS_INT_USER22 ,H.CS_INT_USER23 ,H.CS_INT_USER24 , CASE h.cs_source_trans WHEN '01' THEN TXN.CNTRCT_ID ELSE ' ' END FROM PS_CS_DOC_HDR H ,PS_CS_DOC_HDR_TMP TXN ,PS_CS_DOC_ATT_TBL F WHERE H.CS_DOC_ID = TXN.CS_DOC_ID AND H.CS_DOC_ID = F.CS_DOC_ID AND F.CS_DOC_TYPE IN ('001','002','095', '096', '010') AND H.CS_SOURCE_TRANS <> '04'

  • Related Language Record: CS_DOC_H_ALLLVW

    • # PeopleSoft Field Name PeopleSoft Field Type Database Column Type Description
    1 CS_CONTENT_TYPE Character(4) VARCHAR2(4) NOT NULL 06/08/09 F-AHAYT-XOES6 Add capability to use binds in headers and/or footers of contract document; new translate value added to support Where Used from Document Templates and Styles 06/25/07 F-AHAYT-XOES6 Added 0016 for Document Format and Options; this type is only used when displayed where a word template is used with doc options. We do not search doc format and options.
    0001=Clause
    0002=Section
    0003=Ad Hoc Clause
    0004=Rule
    0005=Bind Variables
    0006=Wizard Definition
    0007=Document Configurator
    0008=Document
    0009=Contract Agreement
    0010=Bid Factor
    0011=Contract
    0012=Sourcing Event
    0013=Question Group
    0014=Document Type
    0015=Word Template
    0016=Document Format and Options
    2 CS_DOC_ID Character(25) VARCHAR2(25) NOT NULL Document ID
    3 SETID Character(5) VARCHAR2(5) NOT NULL SetID
    4 CS_CONTENT_ID Character(30) VARCHAR2(30) NOT NULL Content ID
    5 CS_DOC_TYPE Character(3) VARCHAR2(3) NOT NULL stores the file type -- main file, amendment, preview, throwaway Date Initials Issue Description ========================================== 06/2007 CEL F-AHAYT-25EQ8 added the external main and amendment types 7/2008 cel Added new executive summary file type. 9/30/2008 RMK Changed values 005 and 006 to 095 and 096, and added 010 for executive summary. This change is due to the search on file type using range (001-010 for non-attachment search, and 001-096 for attachment search)
    001=Main Document
    002=Amendment
    003=Preview Document
    004=Compare File
    005=Document Attachment
    006=Transaction Attachment
    007=Config Selector
    008=Main Signature Document
    009=Amendment Signature File
    010=Executive Summary
    011=Exec-Sign
    095=Document Attachment
    096=Transaction Attachment
    101=Main Document
    102=Amendment Document
    998=Main Document - ext collab
    999=Amendment External Collab
    6 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.
    7 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.
    8 BUSINESS_UNIT Character(5) VARCHAR2(5) NOT NULL Business Unit
    9 DESCR60 Character(60) VARCHAR2(60) NOT NULL Description
    10 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
    11 CS_DOCUMENT_TYPE Character(20) VARCHAR2(20) NOT NULL Document Type
    001=Main Document
    002=Amendment
    003=Preview Document
    004=Compare File
    12 CS_DOC_OWNER Character(30) VARCHAR2(30) NOT NULL Document Administrator
    13 CS_SPONSOR Character(30) VARCHAR2(30) NOT NULL Sponsor

    Prompt Table: PSOPRDEFN_VW

    14 DEPTID Character(10) VARCHAR2(10) NOT NULL Department
    15 CS_SETID Character(5) VARCHAR2(5) NOT NULL setid for the document objects
    16 CS_ADHOC_SETID Character(5) VARCHAR2(5) NOT NULL Setid for the Adhoc documents
    17 CS_TMPL_TYPE Character(4) VARCHAR2(4) NOT NULL SCC 09/01/04
    18 CS_TEMPLATE_ID Character(20) VARCHAR2(20) NOT NULL SCC 09/01/04
    19 CS_VERSION Number(6,2) DECIMAL(5,2) NOT NULL Version
    20 CS_AMENDMENT Number(3,0) SMALLINT NOT NULL Amendment
    21 CS_TWOFILE_AMEND Character(1) VARCHAR2(1) NOT NULL Amendment Option
    1=Amend Contract Only
    2=Amend Contract with Amendments
    3=Amendment Files Only

    Y/N Table Edit

    Default Value: N

    22 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
    23 CS_TRANKEY_REC Character(15) VARCHAR2(15) NOT NULL Source Key Record
    24 CREATED_DTTM DateTime(26) TIMESTAMP Created Date and time
    25 CREATED_BY_USER Character(30) VARCHAR2(30) NOT NULL Created By
    26 DATETIME_CHECKOUT DateTime(26) TIMESTAMP Checked Out On
    27 CHECKED_OUT_BY Character(30) VARCHAR2(30) NOT NULL Checked Out By
    28 COMMENT1 Character(254) VARCHAR2(254) NOT NULL Comments
    29 COLLABED_DTTM DateTime(26) TIMESTAMP Collaborated On
    30 APPROVAL_DTTM DateTime(26) TIMESTAMP A system generated value that reflects the date and time a given transaction is approved by an oper
    31 DATETIME_DISPATCH DateTime(26) TIMESTAMP 02/2001 cel WV-802-1.0
    32 EXECUTED_DTTM DateTime(26) TIMESTAMP Executed On
    33 CNTRCT_BEGIN_DT Date(10) DATE NOT NULL Contract Begin Date

    Default Value: %date

    34 CNTRCT_EXPIRE_DT Date(10) DATE Expire Date
    35 LAST_MODIFIED_DATE DateTime(26) TIMESTAMP Last modified date
    36 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.
    37 CLOSE_FLAG Character(1) VARCHAR2(1) NOT NULL Close Contract Flag
    38 CS_IMPORT_CODE Character(1) VARCHAR2(1) NOT NULL Import Type
    1=Authored
    2=Imported
    3=Hybrid
    4=Automatic Import

    Default Value: 1

    39 CS_IMPORT_FLG Character(1) VARCHAR2(1) NOT NULL Imported Document
    40 ATTACH_DESCR Character(100) VARCHAR2(100) NOT NULL A description of the file being attached. This field is provided as a convenience to app developers and customers. The attachment utility does not interpret its value in any way, simply storing it as set by the application.
    41 FILENAME Character(80) VARCHAR2(80) NOT NULL File Name
    42 AMT_CNTRCT_MAX Number(27,3) DECIMAL(26,3) NOT NULL Maximum Amount of Contract.
    43 AMT_RELEASED Number(27,3) DECIMAL(26,3) NOT NULL Total Amount Released
    44 AMT_REMAINING Signed Number(28,3) DECIMAL(26,3) NOT NULL Remaining Amount
    45 DTTM_MODIFIED DateTime(26) TIMESTAMP Datetime modified
    46 PO_ID Character(10) VARCHAR2(10) NOT NULL Purchase Order
    47 AMT_OPEN Signed Number(28,3) DECIMAL(26,3) NOT NULL Open Amount
    48 CS_CHAR_USER1 Character(10) VARCHAR2(10) NOT NULL Character 10 User Field 1
    49 CS_CHAR_USER2 Character(10) VARCHAR2(10) NOT NULL Character 10 User Field 2
    50 CS_CHAR_USER3 Character(10) VARCHAR2(10) NOT NULL Character 10 User Field 3
    51 CS_CHAR_USER4 Character(10) VARCHAR2(10) NOT NULL Character 10 User Field 4
    52 CS_CHAR_USER5 Character(30) VARCHAR2(30) NOT NULL Character 30 User Field 5
    53 CS_CHAR_USER6 Character(30) VARCHAR2(30) NOT NULL Character 30 User Field 6
    54 CS_CHAR_USER7 Character(30) VARCHAR2(30) NOT NULL Character 30 User Field 7
    55 CS_CHAR_USER8 Character(30) VARCHAR2(30) NOT NULL Character 30 User Field 8
    56 CS_CHAR_USER9 Character(60) VARCHAR2(60) NOT NULL Character 60 User Field 9
    57 CS_CHAR_USER10 Character(60) VARCHAR2(60) NOT NULL Character 60 User Field 10
    58 CS_CHAR_USER11 Character(10) VARCHAR2(10) NOT NULL Character 10 User Field 11
    59 CS_CHAR_USER12 Character(10) VARCHAR2(10) NOT NULL Character 10 User Field 12
    60 CS_CHAR_USER13 Character(10) VARCHAR2(10) NOT NULL Character 10 User Field 13
    61 CS_CHAR_USER14 Character(10) VARCHAR2(10) NOT NULL Character 10 User Field 14
    62 CS_CHAR_USER15 Character(10) VARCHAR2(10) NOT NULL Character 10 User Field 15
    63 CS_CHAR_USER16 Character(10) VARCHAR2(10) NOT NULL Character 10 User Field 16
    64 CS_CHAR_USER17 Character(10) VARCHAR2(10) NOT NULL Character 10 User Field 17
    65 CS_CHAR_USER18 Character(10) VARCHAR2(10) NOT NULL Character 10 User Field 18
    66 CS_CHAR_USER19 Character(30) VARCHAR2(30) NOT NULL Character 30 User Field 19
    67 CS_CHAR_USER20 Character(30) VARCHAR2(30) NOT NULL Character 30 User Field 20
    68 CS_CHAR_USER21 Character(30) VARCHAR2(30) NOT NULL Character 30 User Field 21
    69 CS_CHAR_USER22 Character(30) VARCHAR2(30) NOT NULL Character 30 User Field 22
    70 CS_CHAR_USER23 Character(30) VARCHAR2(30) NOT NULL Character 30 User Field 23
    71 CS_CHAR_USER24 Character(30) VARCHAR2(30) NOT NULL Character 30 User Field 24
    72 CS_CHAR_USER25 Character(30) VARCHAR2(30) NOT NULL Character 30 User Field 25
    73 CS_CHAR_USER26 Character(30) VARCHAR2(30) NOT NULL Character 30 User Field 26
    74 CS_CHAR_USER27 Character(60) VARCHAR2(60) NOT NULL Character 60 User Field 27
    75 CS_CHAR_USER28 Character(60) VARCHAR2(60) NOT NULL Character 60 User Field 28
    76 CS_CHAR_USER29 Character(60) VARCHAR2(60) NOT NULL Character 60 User Field 29
    77 CS_CHAR_USER30 Character(60) VARCHAR2(60) NOT NULL Character 60 User Field 30
    78 CS_CHAR_USER31 Character(60) VARCHAR2(60) NOT NULL Character 60 User Field 31
    79 CS_CHAR_USER32 Character(60) VARCHAR2(60) NOT NULL Character 60 User Field 32
    80 CS_CHAR_USER33 Character(120) VARCHAR2(120) NOT NULL Character 120 User Field 33
    81 CS_CHAR_USER34 Character(120) VARCHAR2(120) NOT NULL Character 120 User Field 34
    82 CS_CHAR_USER35 Character(120) VARCHAR2(120) NOT NULL Character 120 User Field 35
    83 CS_CHAR_USER36 Character(120) VARCHAR2(120) NOT NULL Character 120 User Field 36
    84 CS_CHAR_USER37 Character(120) VARCHAR2(120) NOT NULL Character 120 User Field 37
    85 CS_CHAR_USER38 Character(120) VARCHAR2(120) NOT NULL Character 120 User Field 38
    86 CS_DATE_USER1 Date(10) DATE Date User Field 1
    87 CS_DATE_USER2 Date(10) DATE Date User Field 2
    88 CS_DATE_USER3 Date(10) DATE Date User Field 3
    89 CS_DATE_USER4 Date(10) DATE Date User Field 4
    90 CS_DATE_USER5 Date(10) DATE Date User Field 5
    91 CS_DATE_USER6 Date(10) DATE Date User Field 6
    92 CS_DATE_USER7 Date(10) DATE Date User Field 7
    93 CS_DATE_USER8 Date(10) DATE Date User Field 8
    94 CS_DATE_USER9 Date(10) DATE Date User Field 9
    95 CS_DATE_USER10 Date(10) DATE Date User Field 10
    96 CS_DATE_USER11 Date(10) DATE Date User Field 11
    97 CS_DATE_USER12 Date(10) DATE Date User Field 12
    98 CS_DATE_USER13 Date(10) DATE Date User Field 13
    99 CS_DATE_USER14 Date(10) DATE Date User Field 14
    100 CS_DATE_USER15 Date(10) DATE Date User Field 15
    101 CS_DEC_USER1 Signed Number(28,3) DECIMAL(26,3) NOT NULL Decimal User Field 1 (23.3)
    102 CS_DEC_USER2 Signed Number(28,3) DECIMAL(26,3) NOT NULL Decimal User Field 2 (23.3)
    103 CS_DEC_USER3 Signed Number(28,3) DECIMAL(26,3) NOT NULL Decimal User Field 3 (23.3)
    104 CS_DEC_USER4 Signed Number(17,4) DECIMAL(15,4) NOT NULL Decimal User Field 4 (11.4)
    105 CS_DEC_USER5 Signed Number(17,4) DECIMAL(15,4) NOT NULL Decimal User Field5 (11.4)
    106 CS_DEC_USER6 Signed Number(28,3) DECIMAL(26,3) NOT NULL Decimal User Field 6 (23.3)
    107 CS_DEC_USER7 Signed Number(28,3) DECIMAL(26,3) NOT NULL Decimal User Field 7 (23.3)
    108 CS_DEC_USER8 Signed Number(28,3) DECIMAL(26,3) NOT NULL Decimal User Field 8 (23.3)
    109 CS_DEC_USER9 Signed Number(28,3) DECIMAL(26,3) NOT NULL Decimal User Field 9 (23.3)
    110 CS_DEC_USER10 Signed Number(28,3) DECIMAL(26,3) NOT NULL Decimal User Field 10 (23.3)
    111 CS_DEC_USER11 Signed Number(28,3) DECIMAL(26,3) NOT NULL Decimal User Field 11 (23.3)
    112 CS_DEC_USER12 Signed Number(17,4) DECIMAL(15,4) NOT NULL Decimal User Field 12 (11.4)
    113 CS_DEC_USER13 Signed Number(17,4) DECIMAL(15,4) NOT NULL Decimal User Field 13 (11.4)
    114 CS_DEC_USER14 Signed Number(17,4) DECIMAL(15,4) NOT NULL Decimal User Field 14 (11.4)
    115 CS_DEC_USER15 Signed Number(17,4) DECIMAL(15,4) NOT NULL Decimal User Field 15 (11.4)
    116 CS_INT_USER1 Number(8,0) INTEGER NOT NULL Integer User Field 1
    117 CS_INT_USER2 Number(8,0) INTEGER NOT NULL Integer User Field 2
    118 CS_INT_USER3 Number(8,0) INTEGER NOT NULL Integer User Field 3
    119 CS_INT_USER4 Number(8,0) INTEGER NOT NULL Integer User Field 4
    120 CS_INT_USER5 Number(8,0) INTEGER NOT NULL Integer User Field 5
    121 CS_INT_USER6 Number(8,0) INTEGER NOT NULL Integer User Field 6
    122 CS_INT_USER7 Number(8,0) INTEGER NOT NULL Integer User Field 7
    123 CS_INT_USER8 Number(8,0) INTEGER NOT NULL Integer User Field 8
    124 CS_INT_USER9 Number(8,0) INTEGER NOT NULL Integer User Field 9
    125 CS_INT_USER10 Number(8,0) INTEGER NOT NULL Integer User Field 10
    126 CS_INT_USER11 Number(8,0) INTEGER NOT NULL Integer User Field 11
    127 CS_INT_USER12 Number(8,0) INTEGER NOT NULL Integer User Field 12
    128 CS_INT_USER13 Number(8,0) INTEGER NOT NULL Integer User Field 13
    129 CS_INT_USER14 Number(8,0) INTEGER NOT NULL Integer User Field 14
    130 CS_INT_USER15 Number(8,0) INTEGER NOT NULL Integer User Field 15
    131 CS_INT_USER16 Number(8,0) INTEGER NOT NULL Integer User Field 16
    132 CS_INT_USER17 Number(8,0) INTEGER NOT NULL Integer User Field 17
    133 CS_INT_USER18 Number(8,0) INTEGER NOT NULL Integer User Field 18
    134 CS_INT_USER19 Number(8,0) INTEGER NOT NULL Integer User Field 19
    135 CS_INT_USER20 Number(8,0) INTEGER NOT NULL Integer User Field 20
    136 CS_INT_USER21 Number(8,0) INTEGER NOT NULL Integer User Field 21
    137 CS_INT_USER22 Number(8,0) INTEGER NOT NULL Integer User Field 22
    138 CS_INT_USER23 Number(8,0) INTEGER NOT NULL Integer User Field 23
    139 CS_INT_USER24 Number(8,0) INTEGER NOT NULL Integer User Field 24
    140 CS_ADHOC_ID Character(25) VARCHAR2(25) NOT NULL Ad Hoc ID