CNTRCT_HDR_MFG

(SQL View)
 Index Back

Contract Header FS View

PO002 Manufacturer contract. Contract Header view for MFG contract. This view joins distributor contract table for fetching vendor id and vendor setid.

SELECT A.SETID ,A.CNTRCT_ID ,A.VERSION_NBR ,DST.VENDOR_SETID ,DST.VENDOR_ID ,A.CNTRCT_STATUS ,A.OPRID_APPROVED_BY ,A.APPROVAL_DT ,A.AMT_RELSD_OITM_CUM ,A.CNTRCT_TYPE ,A.DESCR ,A.VNDR_CNTRCT_REF ,A.FILENAME ,A.FILE_EXTENSION ,CASE WHEN A.CNTRCT_BEGIN_DT >= DST.CNTRCT_BEGIN_DT THEN A.CNTRCT_BEGIN_DT ELSE DST.CNTRCT_BEGIN_DT END ,CASE WHEN A.CNTRCT_EXPIRE_DT <= DST.CNTRCT_EXPIRE_DT THEN A.CNTRCT_EXPIRE_DT ELSE DST.CNTRCT_EXPIRE_DT END ,A.AMT_CNTRCT_MAX ,A.RETENTION_CD ,A.RETENTION_PCT ,A.RETENTION_AMT ,A.TAX_EXEMPT ,A.TAX_EXEMPT_ID ,A.CURRENCY_CD ,A.RT_TYPE ,A.ORIGIN ,A.INVOICE_ID ,A.MSTR_CNTRCT_ID ,A.OPRID_ENTERED_BY ,A.ENTERED_DT ,%subrec(LAST_UPDATE_SBR,A) ,A.ALLOW_OPEN_ITEM ,A.PRICE_CAN_CHANGE ,A.ADJ_BEFORE ,A.GROSS_AMT ,A.VCHR_TTL_LINES ,A.BUSINESS_UNIT_AP ,A.VAT_ENTRD_AMT ,A.TAX_CD_VAT ,A.VAT_DCLRTN_POINT ,A.VAT_CALC_TYPE ,A.VAT_CALC_GROSS_NET ,A.VAT_RECALC_FLG ,A.VAT_CALC_FRGHT_FLG ,A.VAT_RCRD_INPT_FLG ,A.VAT_RCRD_OUTPT_FLG ,A.VAT_TXN_TYPE_CD ,A.VAT_RECOVERY_PCT ,A.ALLOW_MC_PO ,A.USE_CNTRCT_RTDT ,A.RATE_DATE ,A.CNTRCT_EXPIRY_FLG ,A.CNTRCT_NOTIFY ,A.BUYER_ID ,A.CNTRCT_CORP_FLG ,A.DST_CNTRL_ID ,A.PYMNT_TERMS_CD ,A.CNTRCT_DISP_MTHD ,A.SALETX_AMT ,A.FREIGHT_AMT ,A.MISC_AMT ,A.CNTCT_SEQ_NUM ,A.PAY_TRM_BSE_DT_OPT ,A.TERMS_BASIS_DT ,A.VAT_ROUND_RULE ,A.VAT_CALC_MISC_FLG ,%subrec(PUR_USR_HDR_SBR, A) ,A.VCHR_SRC ,A.CNTRCT_PROC_OPT ,A.PREPAID_REF ,A.CNTRCT_CF_LOCK ,A.NOTIFY_AMT ,A.VERSION_STATUS ,A.VERSION_DATE ,A.RENEWAL_DATE ,A.RENEWAL_NOTIFY ,A.CNTRCT_AUTO_DFLT ,A.DRAFT_DUE_DATE ,A.DRAFT_NOTIFY ,A.CNT_SINGLE_SUPP ,A.CNTRCT_STYLE ,A.ALL_SUPP_LOC ,A.CNTRCT_MARKUP ,A.MFG_ID ,A.PCHOUTPRICE_OPTION ,A.CONTROL_TYPE ,DST.CNTRCT_ID ,MD.PRIORITY FROM PS_CNTRCT_HDR A , PS_CNTRCT_MFG_DST MD , PS_CNTRCT_HDR DST WHERE A.CNTRCT_PROC_OPT = 'MFG' AND MD.SETID = A.SETID AND MD.CNTRCT_ID = A.CNTRCT_ID AND MD.VERSION_NBR = A.VERSION_NBR AND MD.RELTN_STATUS = 'A' AND DST.SETID = MD.SETID AND DST.CNTRCT_ID = MD.CNTRCT_ID_DST AND DST.VERSION_NBR = 1 AND DST.CNTRCT_STATUS = 'A'

# 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

Default Value: 1

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_OITM_CUM Number(27,3) DECIMAL(26,3) NOT NULL Cumulative Relsd for Open Item
10 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

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

Prompt Table: FILE_DIR_BU_VW

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

Default Value: %date

16 CNTRCT_EXPIRE_DT Date(10) DATE Expire Date
17 AMT_CNTRCT_MAX Number(27,3) DECIMAL(26,3) NOT NULL Maximum Amount of Contract.
18 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

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

Y/N Table Edit

Default Value: N

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

Default Value: CNTRCT_CONTROL.CURRENCY_CD

Prompt Table: CURRENCY_CD_TBL

24 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

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

Prompt Table: CNTRCT_MSTR_VW

28 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
29 ENTERED_DT Date(10) DATE NOT NULL Entered on

Default Value: %date

30 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.
31 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.
32 ALLOW_OPEN_ITEM Character(1) VARCHAR2(1) NOT NULL Allow Open Item Reference
N=No
Y=Yes

Y/N Table Edit

Default Value: N

33 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

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

Default Value: N

35 GROSS_AMT Signed Number(28,3) DECIMAL(26,3) NOT NULL Gross Invoice Amount
36 VCHR_TTL_LINES Number(5,0) INTEGER NOT NULL Lines Entered
37 BUSINESS_UNIT_AP Character(5) VARCHAR2(5) NOT NULL AP Business Unit
38 VAT_ENTRD_AMT Signed Number(28,3) DECIMAL(26,3) NOT NULL Entered VAT Amount
39 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.
40 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
41 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
42 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
43 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.
44 VAT_CALC_FRGHT_FLG Character(1) VARCHAR2(1) NOT NULL Include Freight
45 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.
46 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.
47 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.
48 VAT_RECOVERY_PCT Signed Number(7,2) DECIMAL(5,2) NOT NULL VAT Recovery Percent
49 ALLOW_MC_PO Character(1) VARCHAR2(1) NOT NULL Allow Multicurrency PO
N=No
Y=Yes

Y/N Table Edit

Default Value: Y

50 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

51 RATE_DATE Date(10) DATE Exchange Rate Date

Default Value: %date

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

Y/N Table Edit

Default Value: N

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

Prompt Table: BUYER_ACTIVE_VW

55 CNTRCT_CORP_FLG Character(1) VARCHAR2(1) NOT NULL Corporate Contract Flag
N=No
Y=Yes
56 DST_CNTRL_ID Character(10) VARCHAR2(10) NOT NULL Accounting Template
57 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.
58 CNTRCT_DISP_MTHD Character(3) VARCHAR2(3) NOT NULL Contract Dispatch Method
EML=Email Dispatch
FAX=Fax Transmission
PHN=Phone Dispatch
PRN=Print
59 SALETX_AMT Signed Number(28,3) DECIMAL(26,3) NOT NULL Sales Tax Amount
60 FREIGHT_AMT Signed Number(28,3) DECIMAL(26,3) NOT NULL Freight Amount
61 MISC_AMT Signed Number(28,3) DECIMAL(26,3) NOT NULL Misc. Amount
62 CNTCT_SEQ_NUM Number(5,0) INTEGER NOT NULL Contact Sequence Number
63 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
64 TERMS_BASIS_DT Date(10) DATE Payment Terms Basis Date
65 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
66 VAT_CALC_MISC_FLG Character(1) VARCHAR2(1) NOT NULL Include Miscellaneous
67 USER_HDR_CHAR1 Character(1) VARCHAR2(1) NOT NULL Custom Character 1
68 CUSTOM_C100_A1 Character(100) VARCHAR2(100) NOT NULL Custom Field 1
69 CUSTOM_C100_A2 Character(100) VARCHAR2(100) NOT NULL Custom Field 2
70 CUSTOM_C100_A3 Character(100) VARCHAR2(100) NOT NULL Custom Field 3
71 CUSTOM_C100_A4 Character(100) VARCHAR2(100) NOT NULL Custom Field 4
72 CUSTOM_DATE_A Date(10) DATE Custom Date
73 CUSTOM_C1_A Character(1) VARCHAR2(1) NOT NULL Custom Character 2
74 VCHR_SRC Character(4) VARCHAR2(4) NOT NULL Voucher Source

Default Value: CNTR

75 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

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

Y/N Table Edit

Default Value: N

78 NOTIFY_AMT Signed Number(28,3) DECIMAL(26,3) NOT NULL Notify Amount
79 VERSION_STATUS Character(1) VARCHAR2(1) NOT NULL Contract version status
C=Current
D=Draft
H=History

Default Value: C

80 VERSION_DATE Date(10) DATE Date version was first approved
81 RENEWAL_DATE Date(10) DATE Renewal Date
82 RENEWAL_NOTIFY Number(4,0) SMALLINT NOT NULL Number of Days for contract renewal date notification workflow
83 CNTRCT_AUTO_DFLT Character(1) VARCHAR2(1) NOT NULL Auto defaulting option on the Procurement Contract
N=No
Y=Yes

Y/N Table Edit

Default Value: Y

84 DRAFT_DUE_DATE Date(10) DATE Draft Due Date
85 DRAFT_NOTIFY Number(4,0) SMALLINT NOT NULL Number of Days for contract renewal date notification workflow
86 CNT_SINGLE_SUPP Character(1) VARCHAR2(1) NOT NULL Single Supplier

Y/N Table Edit

87 CNTRCT_STYLE Character(10) VARCHAR2(10) NOT NULL Contract Style
88 ALL_SUPP_LOC Character(1) VARCHAR2(1) NOT NULL 07/22/2015 JOHAN Master Contract
89 CNTRCT_MARKUP Signed Number(10,4) DECIMAL(8,4) NOT NULL PO002
90 MFG_ID Character(50) VARCHAR2(50) NOT NULL Manufacturer ID
91 PCHOUTPRICE_OPTION Character(4) VARCHAR2(4) NOT NULL Punchout Pricing Option
AUCP=Always Use Contract Price
AUPP=Always Use Punchout Price
UPPL=Use Punchout Price if Lower
X=Not Used
92 CONTROL_TYPE Character(4) VARCHAR2(4) NOT NULL PO002 Manufacturer Contract
BU=Business Unit
CDOM=Contract Domain
SHIP=Ship To
X=Not Used
93 CNTRCT_ID_DST Character(25) VARCHAR2(25) NOT NULL Contract ID To
94 PRIORITY Number(3,0) SMALLINT NOT NULL Priority