CA_PV_SUM_VW(SQL View) |
Index Back |
|---|---|
Main vw for Online PV SummThis is the main view to bring in all other details to be used in Online summary page |
| SELECT CHDR.BUSINESS_UNIT , CD.CONTRACT_NUM , CD.CONTRACT_LINE_NUM , CHDR.CONTRACT_TYPE , CHDR.CA_RQST_SRC , CHDR.SOLD_TO_CUST_ID , CD.CA_FEE_TYPE , CHDR.SEPARATE_BIL_REV , CD.HOLD_UNPAID_BI , CD.HOLD_UNPAID_REV , CHDR.CURRENCY_CD , CHDR.RT_TYPE , LMT.LIMIT_AMT_BIL , LMT.LIMIT_AMT_REV ,CD.END_DT ,CHDR.DESCR ,CD.START_DT ,CHDR.BILL_TO_CUST_ID ,CHDR.LOC_REFERENCE_ID ,CHDR.CA_STATUS ,CHDR.REGION_CD ,CHDR.CONTRACT_ADMIN ,CHDR.GM_METHOD_PAYMENT ,CUST.NAME1 ,CUST.SPNSR_TYPE FROM PS_CA_CONTR_HDR CHDR , PS_CA_DETAIL CD ,PS_CA_LMT_FEE_DTL LMT , PS_CUSTOMER CUST , PS_SET_CNTRL_REC REC WHERE (CD.HOLD_UNPAID_BI='Y' OR CD.HOLD_UNPAID_REV='Y') AND CHDR.CONTRACT_NUM=CD.CONTRACT_NUM AND CD.CONTRACT_NUM=LMT.CONTRACT_NUM AND CD.CONTRACT_LINE_NUM=LMT.CONTRACT_LINE_NUM AND CHDR.SOLD_TO_CUST_ID = CUST.CUST_ID AND CHDR.BUSINESS_UNIT = REC.SETCNTRLVALUE AND REC.RECNAME = 'CUSTOMER' AND REC.SETID = CUST.SETID AND CHDR.CA_PROC_STATUS IN ('A','P') AND CHDR.CA_RQST_SRC <>'OND' |
| # | PeopleSoft Field Name | PeopleSoft Field Type | Database Column Type | Description |
|---|---|---|---|---|
| 1 |  BUSINESS_UNIT |
Character(5) | VARCHAR2(5) NOT NULL | Business Unit |
| 2 | CONTRACT_NUM |
Character(25) | VARCHAR2(25) NOT NULL | Contract |
| 3 | CONTRACT_LINE_NUM |
Number(3,0) | SMALLINT NOT NULL | Contract Line Num |
| 4 | CONTRACT_TYPE | Character(15) | VARCHAR2(15) NOT NULL | Contract Type |
| 5 | 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 |
| 6 | SOLD_TO_CUST_ID | Character(15) | VARCHAR2(15) NOT NULL | Sold To Customer |
| 7 | CA_FEE_TYPE | Character(2) | VARCHAR2(2) NOT NULL |
Cost Plus Fee Type
01=None 02=Fixed Fee 03=Award Fee 04=Incentive Fee 05=Other Fee |
| 8 | SEPARATE_BIL_REV | Character(1) | VARCHAR2(1) NOT NULL | Flag used by Governement Contracts to separate Billing and Revenue transactions |
| 9 | HOLD_UNPAID_BI | Character(1) | VARCHAR2(1) NOT NULL | Hold Billing on Unpaid Cost |
| 10 | HOLD_UNPAID_REV | Character(1) | VARCHAR2(1) NOT NULL | Hold Revenue on Unpaid Cost |
| 11 | CONTRACT_CURRENCY | Character(3) | VARCHAR2(3) NOT NULL | Currency Code |
| 12 | 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 |
| 13 | LIMIT_AMT_BIL | Signed Number(28,3) | DECIMAL(26,3) NOT NULL | Funded/Billing Limit Amount |
| 14 | LIMIT_AMT_REV | Signed Number(28,3) | DECIMAL(26,3) NOT NULL | Revenue Limit Amount |
| 15 | END_DT | Date(10) | DATE | End Date |
| 16 | DESCR | Character(30) | VARCHAR2(30) NOT NULL | Description |
| 17 | START_DT_2 | Date(10) | DATE | Start Date |
| 18 | BILL_TO_CUST_ID | Character(15) | VARCHAR2(15) NOT NULL | Customer |
| 19 | LOC_REFERENCE_ID | Character(10) | VARCHAR2(10) NOT NULL | Letter of Credit ID |
| 20 | CA_STATUS | Character(24) | VARCHAR2(24) NOT NULL | Contract Status |
| 21 | 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. |
| 22 | CONTRACT_ADMIN | Character(40) | VARCHAR2(40) NOT NULL | Contract Administrator defined on CABU. |
| 23 | GM_METHOD_PAYMENT | Character(3) | VARCHAR2(3) NOT NULL |
Method of Payment
CST=Cost Reimbursable LOC=Letter of Credit |
| 24 | NAME1_CUST | Character(40) | VARCHAR2(40) NOT NULL | Customer Name1 |
| 25 | SPNSR_TYPE | Character(4) | VARCHAR2(4) NOT NULL | Sponsor Type |