PC_WC_CA_VW(SQL View) |
Index Back |
|---|---|
Contract Info ViewContract Info View for WorkCenter Exceptions |
| SELECT CD.BUSINESS_UNIT_PC , CD.PROJECT_ID , CD.ACTIVITY_ID , CA.BUSINESS_UNIT , CD.CONTRACT_NUM , CA.GM_METHOD_PAYMENT , CD.CONTRACT_LINE_NUM , CF.PRICING_STRUCTURE , CA.CONTRACT_TYPE , CA.BILL_TO_CUST_ID , CF.BILL_PLAN_ID , CB.BILL_CYCLE_ID , CB.BILLING_SPECIALIST , CE.EFFDT , CE.EFF_STATUS , CE.RATE_TMPL_OPTION , CASE WHEN (CE.RATE_TMPL_OPTION = 'URTP' OR CE.RATE_TMPL_OPTION = 'CRTP') THEN CE.RATE_PLAN WHEN (CE.RATE_TMPL_OPTION = 'TMPL' OR CE.RATE_TMPL_OPTION = 'NSTD') THEN CE.PC_TEMPLATE_ID END FROM PS_CA_CONTR_HDR CA , PS_CA_DETAIL_PROJ CD LEFT OUTER JOIN PS_CA_RATE CE ON ( CE.CONTRACT_NUM = CD.CONTRACT_NUM AND CE.CONTRACT_LINE_NUM = CD.CONTRACT_LINE_NUM ) , PS_CA_DETAIL CF LEFT OUTER JOIN PS_CA_BILL_PLAN CB ON (CF.CONTRACT_NUM = CB.CONTRACT_NUM AND CB.BILL_PLAN_ID = CF.BILL_PLAN_ID) WHERE CA.CONTRACT_NUM = CD.CONTRACT_NUM AND CF.CONTRACT_NUM = CD.CONTRACT_NUM AND CF.CONTRACT_LINE_NUM = CD.CONTRACT_LINE_NUM AND %Coalesce(CE.EFFDT, %CurrentDateIn) = CASE WHEN CE.EFFDT IS NOT NULL THEN ( SELECT MAX(EFFDT) FROM PS_CA_RATE CA_RATE WHERE CA_RATE.CONTRACT_NUM=CE.CONTRACT_NUM AND CA_RATE.CONTRACT_LINE_NUM=CE.CONTRACT_LINE_NUM AND CA_RATE.EFFDT <= %CurrentDateIn) ELSE %CurrentDateIn END |
| # | PeopleSoft Field Name | PeopleSoft Field Type | Database Column Type | Description |
|---|---|---|---|---|
| 1 |  BUSINESS_UNIT |
Character(5) | VARCHAR2(5) NOT NULL | Business Unit |
| 2 | PROJECT_ID |
Character(15) | VARCHAR2(15) NOT NULL | Project Id ChartField |
| 3 | ACTIVITY_ID |
Character(15) | VARCHAR2(15) NOT NULL | Activity ID |
| 4 | BUSINESS_UNIT_CA | Character(5) | VARCHAR2(5) NOT NULL | Contracts Business Unit |
| 5 | CONTRACT_NUM |
Character(25) | VARCHAR2(25) NOT NULL | Contract |
| 6 | GM_METHOD_PAYMENT | Character(3) | VARCHAR2(3) NOT NULL |
Method of Payment
CST=Cost Reimbursable LOC=Letter of Credit |
| 7 | CONTRACT_LINE_NUM |
Number(3,0) | SMALLINT NOT NULL | Contract Line Num |
| 8 | PRICING_STRUCTURE | Character(4) | VARCHAR2(4) NOT NULL |
Price Type
AMT=Amount PCT=Percent RATE=Rate RCR=Recurring |
| 9 | CONTRACT_TYPE | Character(15) | VARCHAR2(15) NOT NULL | Contract Type |
| 10 | BILL_TO_CUST_ID | Character(15) | VARCHAR2(15) NOT NULL | Customer |
| 11 | BILL_PLAN_ID | Character(10) | VARCHAR2(10) NOT NULL | Billing Plan ID |
| 12 | BILL_CYCLE_ID | Character(10) | VARCHAR2(10) NOT NULL | Billing Cycle Identifier |
| 13 | BILLING_SPECIALIST | Character(8) | VARCHAR2(8) NOT NULL | Billing Specialist |
| 14 | EFFDT |
Date(10) | DATE |
Effective Date
Default Value: %date |
| 15 | EFF_STATUS | Character(1) | VARCHAR2(1) NOT NULL |
Effective Status
A=Active I=Inactive |
| 16 | RATE_TMPL_OPTION | Character(4) | VARCHAR2(4) NOT NULL |
Rate Option
CRTP=Contract Rate Plan NSTD=Contract Rate Set TMPL=Rate Set URTP=Rate Plan |
| 17 | PC_TEMPLATE_ID | Character(15) | VARCHAR2(15) NOT NULL | Rate Template ID |