C360_DS_CNTR_VW

(SQL View)
 Index Back

Comprehensive Customer View

Contract Header

SELECT HDR.CONTRACT_NUM ,CUST.SETID , CUST.CUST_ID ,HDR.BUSINESS_UNIT ,HDR.CA_STATUS ,HDR.CA_PROC_STATUS ,HDR.SOLD_TO_CUST_ID ,HDR.CA_RQST_SRC ,HDR.ADDR_SEQ_NUM_BILL ,HDR.ALLOCATION_DONE ,HDR.ADDR_SEQ_NUM_SOLD ,HDR.BUSINESS_UNIT_BI ,HDR.BILL_TYPE_ID ,HDR.BILL_TO_CUST_ID ,HDR.CONTR_LEGAL_ENTITY ,HDR.CONTR_NET_AMT ,HDR.CONTR_TEMPLATE_FLG ,HDR.SUBCUST_QUAL1 ,HDR.SUBCUST_QUAL2 ,HDR.CNTCT_SEQ_SOLD ,HDR.CNTCT_SEQ_BILL ,HDR.CONTR_MSTR_FLG ,HDR.CONTRACT_SIGN_DT ,HDR.CONTRACT_TYPE ,HDR.CREDIT_CHECK ,HDR.REVISED_GROSS ,HDR.REVISED_NET ,HDR.CURRENCY_CD ,HDR.END_DT ,HDR.LEGAL_REVIEW_FLG ,HDR.LASTUPDDTTM ,HDR.LASTUPDOPRID ,HDR.MAST_CONTR_ID ,HDR.PYMNT_TERMS_CD ,HDR.PROPOSAL_ID ,HDR.PO_REF ,HDR.DESCR ,HDR.PARENT_CONTR_ID ,HDR.REGION_CD ,HDR.START_DT ,HDR.SHIP_TO_CUST_ID ,HDR.TOT_CONTR_AMT ,HDR.DFR_REV_ACCT_DT ,HDR.RT_TYPE ,HDR.COTERMINATION ,HDR.LAST_BILL_PLAN_NBR ,HDR.LAST_ACCT_PLAN_NBR ,HDR.PERCENTAGE ,HDR.TOTAL_REDUCTIONS ,HDR.AMENDMENT_DT ,HDR.CHG_ID ,HDR.CHG_CMP_DT ,HDR.GM_CS_DTL_FLAG ,HDR.GM_SAL_DTL_FLAG ,HDR.GM_METHOD_PAYMENT ,HDR.LOC_REFERENCE_ID ,HDR.CONTRACT_ADMIN ,HDR.CA_REIMB_AGREE ,HDR.PAYMENT_METHOD FROM PS_CA_CONTR_HDR HDR , PS_CUSTOMER CUST , PS_SET_CNTRL_GROUP D WHERE HDR.SOLD_TO_CUST_ID = CUST.CUST_ID AND D.REC_GROUP_ID = ( SELECT REC_GROUP_ID FROM PS_REC_GROUP_REC WHERE RECNAME = 'CUSTOMER') AND CUST.SETID = D.SETID AND HDR.BUSINESS_UNIT = D.SETCNTRLVALUE AND CUST.CUST_STATUS = 'A' AND CUST.EXP_PENDING = 'N' AND CUST.SOLD_TO_FLG = 'Y' AND HDR.CA_RQST_SRC <> 'OND'

# PeopleSoft Field Name PeopleSoft Field Type Database Column Type Description
1 CONTRACT_NUM Character(25) VARCHAR2(25) NOT NULL Contract

Prompt Table: CA_CONTR_HDR

2 SETID Character(5) VARCHAR2(5) NOT NULL SetID

Prompt Table: SP_SETID_NONVW

3 CUST_ID Character(15) VARCHAR2(15) NOT NULL Uniquely identifies an organizational entity that purchases goods or services from the enterprise. Represents the sold-to customer which is the customer organization that places orders. The CUST_ID may or may not be the same as the identifiers for the bill-to and ship-to locations.
4 BUSINESS_UNIT Character(5) VARCHAR2(5) NOT NULL Business Unit

Default Value: OPR_DEF_TBL_CA.BUSINESS_UNIT

Prompt Table: SP_BU_CA_NONVW

5 CA_STATUS Character(24) VARCHAR2(24) NOT NULL Contract Status

Prompt Table: %EDITTABLE

6 CA_PROC_STATUS Character(1) VARCHAR2(1) NOT NULL CA_STATUS is a user defined status field that maps (many to 1) to the contract Processing Status field. Process Status indicates what change processing is available.
A=Active
C=Closed
P=Pending

Default Value: P

7 SOLD_TO_CUST_ID Character(15) VARCHAR2(15) NOT NULL Sold To Customer

Prompt Table: CUST_SLD_VW

8 CA_RQST_SRC Character(3) VARCHAR2(3) NOT NULL Contract Classification
FED=Federal Reimbursable Agreement
GEN=Generic Application
GOV=Government
INT=Internal
OND=CRM Field Services On Demand
STD=Standard

Default Value: STD

9 ADDR_SEQ_NUM_BILL Number(5,0) INTEGER NOT NULL Address Sequence Number

Prompt Table: CA_CUST_BILL_VW

10 ALLOCATION_DONE Character(1) VARCHAR2(1) NOT NULL Allocation is complete
N=Incomplete
Y=Complete

Default Value: N

11 ADDR_SEQ_NUM_SOLD Number(5,0) INTEGER NOT NULL Address Sequence Number

Prompt Table: CA_CUST_ADD_VW

12 BUSINESS_UNIT_BI Character(5) VARCHAR2(5) NOT NULL Billing Business Unit

Default Value: BUS_UNIT_TBL_CA.BUSINESS_UNIT_BI

Prompt Table: SP_BUCABI_NONVW

13 BILL_TYPE_ID Character(3) VARCHAR2(3) NOT NULL Bill Type Identifier

Default Value: BUS_UNIT_TBL_CA.BILL_TYPE_ID

Prompt Table: BI_TYPE_EF_VW
Set Control Field: BUSINESS_UNIT_BI

14 BILL_TO_CUST_ID Character(15) VARCHAR2(15) NOT NULL Customer

Prompt Table: %EDITTABLE2

15 CONTR_LEGAL_ENTITY Character(15) VARCHAR2(15) NOT NULL Contract Legal Entity

Default Value: BUS_UNIT_TBL_CA.CONTR_LEGAL_ENTITY

Prompt Table: CA_LEGAL_ENTITY

16 CONTR_NET_AMT Number(27,3) DECIMAL(26,3) NOT NULL Net Amount
17 CONTR_TEMPLATE_FLG Character(1) VARCHAR2(1) NOT NULL National or Global Contract

Y/N Table Edit

Default Value: N

18 SUBCUST_QUAL1 Character(15) VARCHAR2(15) NOT NULL Specifies a user-defined grouping of customer receivables activity. A subcustomer qualifier provides the ability to record and report receivables activity across multiple business units and/or multiple customers. For example a subcustomer qualifier could represent a sales region or a geographic area. There are two possible subcustomer qualifiers: SUBCUST_QUAL1 and SUBCUST_QUAL2.

Prompt Table: SUBCUST_Q1_TBL

19 SUBCUST_QUAL2 Character(15) VARCHAR2(15) NOT NULL Specifies a user-defined grouping of customer receivables activity. A subcustomer qualifier provides the ability to record and report receivables activity across multiple business units and/or multiple customers. For example a subcustomer qualifier could represent a sales region or a geographic area. There are two possible subcustomer qualifiers: SUBCUST_QUAL1 and SUBCUST_QUAL2.

Prompt Table: SUBCUST_Q2_TBL

20 CNTCT_SEQ_SOLD Number(5,0) INTEGER NOT NULL Primary Contact - Sold To

Prompt Table: CA_CUST_CNCT_VW

21 CNTCT_SEQ_BILL Number(5,0) INTEGER NOT NULL Contact Sequence - Bill To

Prompt Table: CA_CUST_BIC_VW

22 CONTR_MSTR_FLG Character(1) VARCHAR2(1) NOT NULL Master Contract

Y/N Table Edit

Default Value: N

23 CONTRACT_SIGN_DT Date(10) DATE Contract Signed

Default Value: %DATE

24 CONTRACT_TYPE Character(15) VARCHAR2(15) NOT NULL Contract Type

Default Value: BUS_UNIT_TBL_CA.CONTRACT_TYPE

Prompt Table: CA_TYPE_TBL

25 CREDIT_CHECK Character(1) VARCHAR2(1) NOT NULL Credit Check
N=Credit Check No
Y=Credit Check Yes

Y/N Table Edit

Default Value: N

26 REVISED_GROSS Signed Number(28,3) DECIMAL(26,3) NOT NULL Negotiated Amount
27 REVISED_NET Signed Number(28,3) DECIMAL(26,3) NOT NULL Revised Net Amount
28 CURRENCY_CD Character(3) VARCHAR2(3) NOT NULL Currency Code

Default Value: BUS_UNIT_TBL_CA.CURRENCY_CD

Prompt Table: CURRENCY_CD_TBL

29 END_DT Date(10) DATE End Date
30 LEGAL_REVIEW_FLG Character(1) VARCHAR2(1) NOT NULL Legal Review Flag

Y/N Table Edit

Default Value: N

31 LASTUPDDTTM 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 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.
33 MAST_CONTR_ID Character(25) VARCHAR2(25) NOT NULL Master Contract #

Prompt Table: CA_MASTER_VW

34 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.

Prompt Table: AR_TRMS_VW

35 PROPOSAL_ID Character(25) VARCHAR2(25) NOT NULL Proposal ID
36 PO_REF Character(30) VARCHAR2(30) NOT NULL Specifies the purchase order number associated with a receivables item.
37 DESCR Character(30) VARCHAR2(30) NOT NULL Description
38 PARENT_CONTR_ID Character(25) VARCHAR2(25) NOT NULL Parent Contract

Prompt Table: CA_PARENT_VW

39 REGION_CD Character(10) VARCHAR2(10) NOT NULL Identifies the Sales Region. Populated on the Item Table to identify the Sales Region the Item is applied to.

Default Value: OPR_DEF_TBL_CA.REGION_CD

Prompt Table: CA_REGION_CD_VW

40 START_DT Date(10) DATE Start Date
41 SHIP_TO_CUST_ID Character(15) VARCHAR2(15) NOT NULL Ship To Customer
42 TOT_CONTR_AMT Number(27,3) DECIMAL(26,3) NOT NULL Gross value
43 DFR_REV_ACCT_DT Date(10) DATE Deferred Revenue Acctg Date
44 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: BUS_UNIT_TBL_CA.RT_TYPE

Prompt Table: RT_TYPE_TBL

45 COTERMINATION Character(1) VARCHAR2(1) NOT NULL PSIRS-KK

Default Value: N

46 LAST_BILL_PLAN_NBR Number(3,0) SMALLINT NOT NULL Last Bill Plan Number Used

Default Value: 100

47 LAST_ACCT_PLAN_NBR Number(3,0) SMALLINT NOT NULL Last Acct Plan Number Used

Default Value: 100

48 PERCENTAGE Signed Number(7,2) DECIMAL(5,2) NOT NULL Percentage
49 TOTAL_REDUCTIONS Signed Number(28,3) DECIMAL(26,3) NOT NULL Total Discounts
50 AMENDMENT_DT Date(10) DATE Amendment Date
51 CHG_ID Character(10) VARCHAR2(10) NOT NULL Change Package Identifier. This is sequenced from 1 to N for each contract.
52 CHG_CMP_DT DateTime(26) TIMESTAMP Amend Completion Date
53 GM_CS_DTL_FLAG Character(1) VARCHAR2(1) NOT NULL Cost Sharing Detail

Y/N Table Edit

Default Value: N

54 GM_SAL_DTL_FLAG Character(1) VARCHAR2(1) NOT NULL Salary Detail Report

Y/N Table Edit

Default Value: N

55 GM_METHOD_PAYMENT Character(3) VARCHAR2(3) NOT NULL Method of Payment
CST=Cost Reimbursable
LOC=Letter of Credit
56 LOC_REFERENCE_ID Character(10) VARCHAR2(10) NOT NULL Letter of Credit ID

Prompt Table: GM_SPNSR_LC2_VW
Set Control Field: BUSINESS_UNIT

57 CONTRACT_ADMIN Character(40) VARCHAR2(40) NOT NULL Contract Administrator defined on CABU.

Default Value: BUS_UNIT_TBL_CA.CONTRACT_ADMIN

Prompt Table: MEMBER_VW

58 CA_REIMB_AGREE Character(1) VARCHAR2(1) NOT NULL Indicator that the installation of Contracts is for Reimbursabale Agreements

Y/N Table Edit

Default Value: INSTALLATION_CA.CA_REIMB_AGREE

59 PAYMENT_METHOD Character(3) VARCHAR2(3) NOT NULL Specifies the preferred method of payment by a customer. Examples of payment method are check direct debit and draft. 10/28/2003 edw 655070000 - Removed LTC and CSH xlats.
ACH=ACH
CC=Credit Card
CHK=Check
CSH=Cash
DD=Direct Debit
DR=Draft
EC=Electronic Check
EFT=Electronic Fund Transfer
GE=Giro - EFT
PL=PayPal
WIR=Wire Report