PC_WC_CA_UPD_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 , CB.BUSINESS_UNIT_BI , CD.CONTRACT_NUM , CA.GM_METHOD_PAYMENT , CD.CONTRACT_LINE_NUM , CF.PRICING_STRUCTURE , CA.CA_STATUS , CA.BILL_TO_CUST_ID , CF.BILL_PLAN_ID , CB.BP_STATUS , CB.HOLD_FLAG , CB.HOLD_DATE , CB.HOLD_RSN , CB.HOLD_OPRID , 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 | BUSINESS_UNIT_BI | Character(5) | VARCHAR2(5) NOT NULL | Billing Business Unit |
| 6 | CONTRACT_NUM |
Character(25) | VARCHAR2(25) NOT NULL | Contract |
| 7 | GM_METHOD_PAYMENT | Character(3) | VARCHAR2(3) NOT NULL |
Method of Payment
CST=Cost Reimbursable LOC=Letter of Credit |
| 8 | CONTRACT_LINE_NUM |
Number(3,0) | SMALLINT NOT NULL | Contract Line Num |
| 9 | PRICING_STRUCTURE | Character(4) | VARCHAR2(4) NOT NULL |
Price Type
AMT=Amount PCT=Percent RATE=Rate RCR=Recurring |
| 10 | CA_STATUS | Character(24) | VARCHAR2(24) NOT NULL | Contract Status |
| 11 | BILL_TO_CUST_ID | Character(15) | VARCHAR2(15) NOT NULL | Customer |
| 12 | BILL_PLAN_ID | Character(10) | VARCHAR2(10) NOT NULL | Billing Plan ID |
| 13 | BP_STATUS | Character(3) | VARCHAR2(3) NOT NULL |
Billing Plan Status
CAN=Cancelled DON=Completed PND=Pending PRG=In Progress RCL=Recycled RDY=Ready RIP=Reversal In Progress RQD=Action Required RRP=Recycled RVS=Reversed |
| 14 | HOLD_FLAG | Character(1) | VARCHAR2(1) NOT NULL |
Hold
N=Hold Y=Hold |
| 15 | HOLD_DATE | Date(10) | DATE | Put On Hold Date |
| 16 | HOLD_RSN | Character(4) | VARCHAR2(4) NOT NULL | Hold Reason |
| 17 | HOLD_OPRID | Character(30) | VARCHAR2(30) NOT NULL | Hold User |
| 18 | BILL_CYCLE_ID | Character(10) | VARCHAR2(10) NOT NULL | Billing Cycle Identifier |
| 19 | BILLING_SPECIALIST | Character(8) | VARCHAR2(8) NOT NULL | Billing Specialist |
| 20 | EFFDT |
Date(10) | DATE |
Effective Date
Default Value: %date |
| 21 | EFF_STATUS | Character(1) | VARCHAR2(1) NOT NULL |
Effective Status
A=Active I=Inactive |
| 22 | 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 |
| 23 | PC_TEMPLATE_ID | Character(15) | VARCHAR2(15) NOT NULL | Rate Template ID |