CS_CNT_HDR_VW

(SQL View)
 Index Back

Contract Header Mappings

Bind mapping view for Contracts PO002 Manufacturer Contract.

SELECT H.SETID ,H.CNTRCT_ID ,H.VERSION_NBR ,H.VENDOR_SETID ,H.VENDOR_ID ,H.CNTRCT_STATUS ,H.OPRID_APPROVED_BY ,H.APPROVAL_DT ,B.AMT_RELSD_OPEN_ITM ,H.AMT_RELSD_OITM_CUM ,H.CNTRCT_TYPE ,H.DESCR ,H.VNDR_CNTRCT_REF ,H.FILENAME ,H.FILE_EXTENSION ,H.CNTRCT_BEGIN_DT ,H.CNTRCT_EXPIRE_DT ,H.AMT_CNTRCT_MAX ,H.RETENTION_CD ,H.RETENTION_PCT ,H.RETENTION_AMT ,H.TAX_EXEMPT ,H.TAX_EXEMPT_ID ,H.CURRENCY_CD ,H.RT_TYPE ,H.ORIGIN ,H.INVOICE_ID ,H.MSTR_CNTRCT_ID ,H.OPRID_ENTERED_BY ,H.ENTERED_DT ,%subrec(LAST_UPDATE_SBR,H) ,H.ALLOW_OPEN_ITEM ,H.PRICE_CAN_CHANGE ,H.ADJ_BEFORE ,H.GROSS_AMT ,H.VCHR_TTL_LINES ,H.BUSINESS_UNIT_AP ,H.VAT_ENTRD_AMT ,H.TAX_CD_VAT ,H.VAT_DCLRTN_POINT ,H.VAT_CALC_TYPE ,H.VAT_CALC_GROSS_NET ,H.VAT_RECALC_FLG ,H.VAT_CALC_FRGHT_FLG ,H.VAT_RCRD_INPT_FLG ,H.VAT_RCRD_OUTPT_FLG ,H.VAT_TXN_TYPE_CD ,H.VAT_RECOVERY_PCT ,H.ALLOW_MC_PO ,H.USE_CNTRCT_RTDT ,H.RATE_DATE ,H.CNTRCT_EXPIRY_FLG ,H.CNTRCT_NOTIFY ,H.BUYER_ID ,H.CNTRCT_CORP_FLG ,H.DST_CNTRL_ID ,H.PYMNT_TERMS_CD ,H.CNTRCT_DISP_MTHD ,H.SALETX_AMT ,H.FREIGHT_AMT ,H.MISC_AMT ,H.CNTCT_SEQ_NUM ,H.PAY_TRM_BSE_DT_OPT ,H.TERMS_BASIS_DT ,H.VAT_ROUND_RULE ,H.VAT_CALC_MISC_FLG ,%subrec(PUR_USR_HDR_SBR, H) ,H.VCHR_SRC ,H.CNTRCT_PROC_OPT ,H.PREPAID_REF ,H.CNTRCT_CF_LOCK ,H.NOTIFY_AMT , V.NAME1 , V.NAME2 , V.VENDOR_NAME_SHORT , V1.ADDRESS1 , V1.ADDRESS2 , V1.ADDRESS3 , V1.ADDRESS4 , V1.CITY , V1.STATE , V1.COUNTY , V1.COUNTRY , V1.POSTAL , V1.GEO_CODE , H.VERSION_STATUS , H.VERSION_DATE , H.RENEWAL_DATE , H.RENEWAL_NOTIFY , H.ALL_SUPP_LOC , H.CNTRCT_STYLE , CASE WHEN H.CNTRCT_STYLE = ' ' THEN ' ' ELSE ( SELECT S.CNTRCT_STYLE_NAME FROM PS_CNTRCT_STYLE S WHERE H.CNTRCT_STYLE = S.CNTRCT_STYLE) END , H.CONTROL_TYPE FROM PS_CNTRCT_HDR H , PS_CNTRCT_HDR_RLS B , PS_VENDOR V , PS_VENDOR_ADDR V1 WHERE B.SETID = H.SETID AND B.CNTRCT_ID = H.CNTRCT_ID AND V.SETID = H.VENDOR_SETID AND V.VENDOR_ID = H.VENDOR_ID AND V1.SETID = V.SETID AND V1.VENDOR_ID = V.VENDOR_ID AND V1.ADDRESS_SEQ_NUM = V.ADDR_SEQ_NUM_ORDR AND V1.EFF_STATUS = 'A' AND V1.EFFDT = ( SELECT MAX(V2.EFFDT) FROM PS_VENDOR_ADDR V2 WHERE V2.SETID = V1.SETID AND V2.VENDOR_ID = V1.VENDOR_ID AND V2.ADDRESS_SEQ_NUM = V1.ADDRESS_SEQ_NUM AND V2.EFFDT <= %CurrentDateIn)

# PeopleSoft Field Name PeopleSoft Field Type Database Column Type Description
1 SETID Character(5) VARCHAR2(5) NOT NULL SetID

Default Value: OPR_DEF_TBL_FS.SETID

Prompt Table: SP_SETID_NONVW

2 CNTRCT_ID Character(25) VARCHAR2(25) NOT NULL Buying Agreement ID

Default Value: NEXT

3 VERSION_NBR Number(5,0) INTEGER NOT NULL Contract Version number
4 VENDOR_SETID Character(5) VARCHAR2(5) NOT NULL Vendor SetID

Prompt Table: SP_SETID_NONVW

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

Prompt Table: VENDOR_VW

6 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

7 OPRID_APPROVED_BY Character(30) VARCHAR2(30) NOT NULL Approved By
8 APPROVAL_DT Date(10) DATE Date of Approval
9 AMT_RELSD_OPEN_ITM Signed Number(28,3) DECIMAL(26,3) NOT NULL Amt relsd for Open Item
10 AMT_RELSD_OITM_CUM Number(27,3) DECIMAL(26,3) NOT NULL Cumulative Relsd for Open Item
11 CNTRCT_TYPE Character(2) VARCHAR2(2) NOT NULL Contract Type
AP=Recurring Voucher
GN=General Contract
MS=Master Contract
PO=Purchase Order

Default Value: PO

12 DESCR Character(30) VARCHAR2(30) NOT NULL Description
13 VNDR_CNTRCT_REF Character(30) VARCHAR2(30) NOT NULL Supplier Contract Ref
14 FILENAME Character(80) VARCHAR2(80) NOT NULL File Name
15 FILE_EXTENSION Character(4) VARCHAR2(4) NOT NULL File Extension

Prompt Table: FILE_DIR_BU_VW

16 CNTRCT_BEGIN_DT Date(10) DATE NOT NULL Contract Begin Date

Default Value: %date

17 CNTRCT_EXPIRE_DT Date(10) DATE Expire Date
18 AMT_CNTRCT_MAX Number(27,3) DECIMAL(26,3) NOT NULL Maximum Amount of Contract.
19 RETENTION_CD Character(1) VARCHAR2(1) NOT NULL Retention
E=Retain on Each Payment
F=Retain on First Payment
L=Retain on Last Payment
N=No Retention

Default Value: N

20 RETENTION_PCT Number(7,3) DECIMAL(6,3) NOT NULL Retention Percentage
21 RETENTION_AMT Number(27,3) DECIMAL(26,3) NOT NULL Retention Amount
22 TAX_EXEMPT Character(1) VARCHAR2(1) NOT NULL Tax Exempt Flag

Y/N Table Edit

Default Value: N

23 TAX_EXEMPT_ID Character(30) VARCHAR2(30) NOT NULL Tax Exempt ID
24 CURRENCY_CD Character(3) VARCHAR2(3) NOT NULL Currency Code

Default Value: CNTRCT_CONTROL.CURRENCY_CD

Prompt Table: CURRENCY_CD_TBL

25 RT_TYPE Character(5) VARCHAR2(5) NOT NULL Defines a category of market rates for currency conversion. Some examples of rate types are commercial, average, floating, and historical.

Default Value: CNTRCT_CONTROL.CUR_RT_TYPE

Prompt Table: RT_TYPE_TBL

26 ORIGIN Character(3) VARCHAR2(3) NOT NULL Origin
27 INVOICE_ID Character(30) VARCHAR2(30) NOT NULL Invoice Number
28 MSTR_CNTRCT_ID Character(10) VARCHAR2(10) NOT NULL Master Contract ID

Prompt Table: CNTRCT_MSTR_VW

29 OPRID_ENTERED_BY Character(30) VARCHAR2(30) NOT NULL Entered By 07/25/2011 MRAD 12383033 :Ensured that OPRID_ENTERED_BY is set with format type of MixedCase. 03/22/2013 GL 16482301: Switched OPRID_ENTERED_BY back to MixedCase again. Please don't change it to UpperCase!!! FYI - The alternatives to use a User ID as uppercase: 1) Create your own User ID, add comments in the Field Properties, and fill out the Owner ID 2) Use %Upper meta-SQL in SQL statements 3) Use Upper function in peoplecodes
30 ENTERED_DT Date(10) DATE NOT NULL Entered on

Default Value: %date

31 OPRID_MODIFIED_BY Character(30) VARCHAR2(30) NOT NULL Last User to Modify Entered By 07/25/2011:Ensured that OPRID_MODIFIED_BY is set with format type of MixedCase.
32 LAST_DTTM_UPDATE DateTime(26) TIMESTAMP Specifies the date and time of the last update to an entry. This field is maintained by PeopleSoft and is used in a variety of contexts.
33 ALLOW_OPEN_ITEM Character(1) VARCHAR2(1) NOT NULL Allow Open Item Reference
N=No
Y=Yes

Y/N Table Edit

Default Value: N

34 PRICE_CAN_CHANGE Character(1) VARCHAR2(1) NOT NULL Price Can Be Changed on Order
N=No
Y=Yes

Y/N Table Edit

Default Value: N

35 ADJ_BEFORE Character(1) VARCHAR2(1) NOT NULL Adjust Supplier Pricing First
N=After Contract Adjustments
Y=Before Contract Adjustments

Default Value: N

36 GROSS_AMT Signed Number(28,3) DECIMAL(26,3) NOT NULL Gross Invoice Amount
37 VCHR_TTL_LINES Number(5,0) INTEGER NOT NULL Lines Entered
38 BUSINESS_UNIT_AP Character(5) VARCHAR2(5) NOT NULL AP Business Unit
39 VAT_ENTRD_AMT Signed Number(28,3) DECIMAL(26,3) NOT NULL Entered VAT Amount
40 TAX_CD_VAT Character(8) VARCHAR2(8) NOT NULL Defines the VAT (Value Added Tax) percentage (via link to the Tax Authority) and is used to retrieve VAT accounting ChartFields. The VAT code determines how the VAT amount is calculated on a transaction and how that amount is accounted and reported for.
41 VAT_DCLRTN_POINT Character(1) VARCHAR2(1) NOT NULL A flag that specifies when VAT information for sales or purchase transaction is recognized for reporting to the appropriate VAT authority. VAT is declared at invoice time, delivery time, accounting date or the time of payment.
A=At Accounting Date
D=At Delivery Time
I=At Invoice Time
P=At Payment Time
42 VAT_CALC_TYPE Character(1) VARCHAR2(1) NOT NULL "A flag that indicates how VAT is stated and determines how the system performs the relevant VAT calculations. If ""Exclusive"", the VAT amount is stated separately from the merchandise amount. If ""Inclusive"", the VAT is not stated separately but is included with the merchandise amount."
E=Exclusive
I=Inclusive
43 VAT_CALC_GROSS_NET Character(1) VARCHAR2(1) NOT NULL A flag that indicates whether VAT calculation should be done on the Gross (G) amount or on the Net (N) amount of sales or purchase transaction. When calculating VAT at net the early payment discount is applied to the goods amount before calculating the VAT. When calculating VAT at gross the VAT is initially calculated based on the gross transaction amount.
G=Calculate at Gross
N=Calculate at Net
44 VAT_RECALC_FLG Character(1) VARCHAR2(1) NOT NULL A flag that indicates whether or not the VAT discount amount is recalculated at payment time. This field is only used if the VAT Calculation Type (VAT_CAL_GROSS_NET) is gross.
45 VAT_CALC_FRGHT_FLG Character(1) VARCHAR2(1) NOT NULL Include Freight
46 VAT_RCRD_INPT_FLG Character(1) VARCHAR2(1) NOT NULL A flag that indicates whether or not (Y or N) a business unit will pay the VAT and recover it later from the VAT tax authority. VAT input is usually associated with purchase transactions.
47 VAT_RCRD_OUTPT_FLG Character(1) VARCHAR2(1) NOT NULL A flag that indicates whether or not (Y or N) VAT will be collected by the vendor on behalf of the government. VAT output is usually associated with sales transactions.
48 VAT_TXN_TYPE_CD Character(4) VARCHAR2(4) NOT NULL Specifies a user-defined category of business transaction that is subject to VAT accounting and reporting. The VAT code and the VAT transaction type are used in conjunction with the VAT account type to obtain the ChartFields for accounting entries. Some examples of VAT Transaction Types are Exempt Sales Exempt Purchases Triangulation EU Sales and Domestic Sales.
49 VAT_RECOVERY_PCT Signed Number(7,2) DECIMAL(5,2) NOT NULL VAT Recovery Percent
50 ALLOW_MC_PO Character(1) VARCHAR2(1) NOT NULL Allow Multicurrency PO
N=No
Y=Yes

Y/N Table Edit

Default Value: Y

51 USE_CNTRCT_RTDT Character(1) VARCHAR2(1) NOT NULL Must Use Contract Rate Date
N=No
Y=Yes

Y/N Table Edit

Default Value: N

52 RATE_DATE Date(10) DATE Exchange Rate Date

Default Value: %date

53 CNTRCT_EXPIRY_FLG Character(1) VARCHAR2(1) NOT NULL Contract Expiry Flag

Y/N Table Edit

Default Value: N

54 CNTRCT_NOTIFY Number(4,0) SMALLINT NOT NULL Notify Days Before Expires
55 BUYER_ID Character(30) VARCHAR2(30) NOT NULL Buyer

Prompt Table: BUYER_ACTIVE_VW

56 CNTRCT_CORP_FLG Character(1) VARCHAR2(1) NOT NULL Corporate Contract Flag
N=No
Y=Yes
57 DST_CNTRL_ID Character(10) VARCHAR2(10) NOT NULL Accounting Template
58 PYMNT_TERMS_CD Character(5) VARCHAR2(5) NOT NULL Specifies how the payment due date and discount due date are calculated. A payment terms code is associated with various business units bill-to customers vendors as well as sales orders purchase orders and vouchers.
59 CNTRCT_DISP_MTHD Character(3) VARCHAR2(3) NOT NULL Contract Dispatch Method
EML=Email Dispatch
FAX=Fax Transmission
PHN=Phone Dispatch
PRN=Print
60 SALETX_AMT Signed Number(28,3) DECIMAL(26,3) NOT NULL Sales Tax Amount
61 FREIGHT_AMT Signed Number(28,3) DECIMAL(26,3) NOT NULL Freight Amount
62 MISC_AMT Signed Number(28,3) DECIMAL(26,3) NOT NULL Misc. Amount
63 CNTCT_SEQ_NUM Number(5,0) INTEGER NOT NULL Contact Sequence Number
64 PAY_TRM_BSE_DT_OPT Character(1) VARCHAR2(1) NOT NULL Payment Terms Basis Date Type
A=Acct Date
C=Doc Date
I=Inv Date
R=Recpt Date
S=Ship Date
U=User Date
65 TERMS_BASIS_DT Date(10) DATE Payment Terms Basis Date
66 VAT_ROUND_RULE Character(1) VARCHAR2(1) NOT NULL " Rounding rule to be applied to VAT calculations. Options are 'Natural'
D=Round Down
N=Natural Round
U=Round Up
67 VAT_CALC_MISC_FLG Character(1) VARCHAR2(1) NOT NULL Include Miscellaneous
68 USER_HDR_CHAR1 Character(1) VARCHAR2(1) NOT NULL Custom Character 1
69 CUSTOM_C100_A1 Character(100) VARCHAR2(100) NOT NULL Custom Field 1
70 CUSTOM_C100_A2 Character(100) VARCHAR2(100) NOT NULL Custom Field 2
71 CUSTOM_C100_A3 Character(100) VARCHAR2(100) NOT NULL Custom Field 3
72 CUSTOM_C100_A4 Character(100) VARCHAR2(100) NOT NULL Custom Field 4
73 CUSTOM_DATE_A Date(10) DATE Custom Date
74 CUSTOM_C1_A Character(1) VARCHAR2(1) NOT NULL Custom Character 2
75 VCHR_SRC Character(4) VARCHAR2(4) NOT NULL Voucher Source

Default Value: CNTR

76 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

77 PREPAID_REF Character(10) VARCHAR2(10) NOT NULL Prepayment Reference
78 CNTRCT_CF_LOCK Character(1) VARCHAR2(1) NOT NULL Lock Chartfields
N=No
Y=Yes

Y/N Table Edit

Default Value: N

79 NOTIFY_AMT Signed Number(28,3) DECIMAL(26,3) NOT NULL Notify Amount
80 NAME1 Character(40) VARCHAR2(40) NOT NULL Name 1
81 NAME2 Character(40) VARCHAR2(40) NOT NULL Name 2
82 VENDOR_NAME_SHORT Character(14) VARCHAR2(14) NOT NULL Short Vendor Name
83 ADDRESS1 Character(55) VARCHAR2(55) NOT NULL Address 1
84 ADDRESS2 Character(55) VARCHAR2(55) NOT NULL Address 2
85 ADDRESS3 Character(55) VARCHAR2(55) NOT NULL Address 3
86 ADDRESS4 Character(55) VARCHAR2(55) NOT NULL Address 4
87 CITY Character(30) VARCHAR2(30) NOT NULL City
88 STATE Character(6) VARCHAR2(6) NOT NULL State
89 COUNTY Character(30) VARCHAR2(30) NOT NULL County
90 COUNTRY Character(3) VARCHAR2(3) NOT NULL Country
91 POSTAL Character(12) VARCHAR2(12) NOT NULL Postal
92 GEO_CODE Character(11) VARCHAR2(11) NOT NULL Geo Code
93 VERSION_STATUS Character(1) VARCHAR2(1) NOT NULL Contract version status
C=Current
D=Draft
H=History
94 VERSION_DATE Date(10) DATE Date version was first approved
95 RENEWAL_DATE Date(10) DATE Renewal Date
96 RENEWAL_NOTIFY Number(4,0) SMALLINT NOT NULL Number of Days for contract renewal date notification workflow
97 ALL_SUPP_LOC Character(1) VARCHAR2(1) NOT NULL 07/22/2015 JOHAN Master Contract

Y/N Table Edit

98 CNTRCT_STYLE Character(10) VARCHAR2(10) NOT NULL Contract Style
99 CNTRCT_STYLE_NAME Character(30) VARCHAR2(30) NOT NULL Style Description
100 CONTROL_TYPE Character(4) VARCHAR2(4) NOT NULL PO002 Manufacturer Contract
BU=Business Unit
CDOM=Contract Domain
SHIP=Ship To
X=Not Used