CA_PGP_SRCH

(SQL View)
 Index Back

SELECT A.BUSINESS_UNIT , A.CONTRACT_NUM , A.DESCR , A.SOLD_TO_CUST_ID , C.NAME1 , A.CA_STATUS , A.CA_PROC_STATUS , A.ADDR_SEQ_NUM_SOLD , A.DFR_REV_ACCT_DT , A.CONTRACT_ADMIN FROM PS_CUSTOMER C , PS_SET_CNTRL_REC B , PS_CA_CONTR_HDR A , PS_INSTALLATION I WHERE C.CUST_ID = A.SOLD_TO_CUST_ID AND C.SETID = B.SETID AND B.RECNAME = 'CUSTOMER' AND B.SETCNTRLVALUE = A.BUSINESS_UNIT AND A.CA_RQST_SRC = 'GOV' AND I.GVC = 'Y' AND (EXISTS ( SELECT 1 FROM PS_CA_DETAIL WHERE CONTRACT_NUM = A.CONTRACT_NUM AND PRICING_STRUCTURE = 'AMT' AND CA_LINE_STATUS = 'A') OR EXISTS ( SELECT 1 FROM PS_CA_PGP_TERMS WHERE CONTRACT_NUM = A.CONTRACT_NUM ) )

# PeopleSoft Field Name PeopleSoft Field Type Database Column Type Description
1 BUSINESS_UNIT Character(5) VARCHAR2(5) NOT NULL Business Unit

Default Value: OPR_DEF_TBL_CA.BUSINESS_UNIT

Prompt Table: SP_BU_CA_NONVW

2 CONTRACT_NUM Character(25) VARCHAR2(25) NOT NULL Contract
3 DESCR Character(30) VARCHAR2(30) NOT NULL Description
4 SOLD_TO_CUST_ID Character(15) VARCHAR2(15) NOT NULL Sold To Customer

Prompt Table: CUST_SLD_VW

5 NAME1 Character(40) VARCHAR2(40) NOT NULL Name 1
6 CA_STATUS Character(24) VARCHAR2(24) NOT NULL Contract Status

Prompt Table: CA_STATUS_TBL

7 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
8 ADDR_SEQ_NUM Number(5,0) INTEGER NOT NULL Address Sequence Number
9 DFR_REV_ACCT_DT Date(10) DATE Deferred Revenue Acctg Date
10 CONTRACT_ADMIN Character(40) VARCHAR2(40) NOT NULL Contract Administrator defined on CABU.

Prompt Table: MEMBER_VW