CA_BI_MGTCRM_VW(SQL View) |
Index Back |
|---|
| SELECT BP.CONTRACT_NUM ,BP.BILL_PLAN_ID ,BP.DESCR ,BP.CURRENCY_CD ,XREF.BUSINESS_UNIT_BI ,HDR.BUSINESS_UNIT ,BP.BILL_TO_CUST_ID ,BP.BP_STATUS ,XREF.XREF_STATUS ,CRM.SETID_AGR ,CRM.AGREEMENT_CODE ,CRM.AGR_RENEWAL_NUM ,CRM.AGREEMENT_LINE ,CRM.BUSINESS_UNIT_RF ,CRM.BUSINESS_UNIT_RF ,CRM.SO_ID ,CRM.CASE_ID ,CRM.BUSINESS_UNIT_RO ,CRM.CAPTURE_ID ,CRM.INST_PROD_ID ,CRM.PRODUCT_ID ,BP.HOLD_FLAG ,BP.HOLD_DATE ,CRM.CA_TXN_TYPE ,SUM(XREF.GROSS_AMT) FROM PS_CA_BILL_PLAN BP ,PS_CA_BP_XREF XREF ,PS_CA_TXN_CRM CRM ,PS_CA_CONTR_HDR HDR WHERE BP.CONTRACT_NUM = XREF.CONTRACT_NUM AND BP.BILL_PLAN_ID = XREF.BILL_PLAN_ID AND CRM.CA_TXN_ID = XREF.CA_TXN_ID AND HDR.CONTRACT_NUM = XREF.CONTRACT_NUM AND HDR.CA_RQST_SRC <> 'OND' AND HDR.CA_STATUS <> 'CLOSED' GROUP BY BP.CONTRACT_NUM,BP.BILL_PLAN_ID,BP.DESCR,BP.CURRENCY_CD,XREF.BUSINESS_UNIT_BI, HDR.BUSINESS_UNIT,BP.BILL_TO_CUST_ID,BP.BP_STATUS,XREF.XREF_STATUS, CRM.SETID_AGR,CRM.AGREEMENT_CODE,CRM.AGR_RENEWAL_NUM,CRM.AGREEMENT_LINE, CRM.BUSINESS_UNIT_RF, CRM.SO_ID,CRM.CASE_ID, CRM.BUSINESS_UNIT_RO, CRM.CAPTURE_ID, BP.HOLD_FLAG, BP.HOLD_DATE, CRM.CA_TXN_TYPE, CRM.INST_PROD_ID, CRM.PRODUCT_ID |
| # | PeopleSoft Field Name | PeopleSoft Field Type | Database Column Type | Description |
|---|---|---|---|---|
| 1 | CONTRACT_NUM | Character(25) | VARCHAR2(25) NOT NULL | Contract |
| 2 | BILL_PLAN_ID | Character(10) | VARCHAR2(10) NOT NULL | Billing Plan ID |
| 3 | DESCR | Character(30) | VARCHAR2(30) NOT NULL | Description |
| 4 | CURRENCY_CD | Character(3) | VARCHAR2(3) NOT NULL | Currency Code |
| 5 | BUSINESS_UNIT_BI | Character(5) | VARCHAR2(5) NOT NULL | Billing Business Unit |
| 6 | BUSINESS_UNIT | Character(5) | VARCHAR2(5) NOT NULL | Business Unit |
| 7 | BILL_TO_CUST_ID | Character(15) | VARCHAR2(15) NOT NULL | Customer |
| 8 | 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 |
| 9 | XREF_STATUS | Character(3) | VARCHAR2(3) NOT NULL |
Cross Reference Status
ACP=Accepted DEL=Deleted FIN=Finalized NEW=New RCV=Received REC=Received REJ=Rejected REV=Reversed RVS=Reversed |
| 10 | SETID_AGR | Character(5) | VARCHAR2(5) NOT NULL | Agreement SetID |
| 11 | AGREEMENT_CODE | Character(30) | VARCHAR2(30) NOT NULL | This field represents the agreement code (name) for an agreement |
| 12 | AGR_RENEWAL_NUM | Number(4,0) | SMALLINT NOT NULL | This field represents the agreement renewal number (agreements begin at a value of one and the number is incremented with every renewal) |
| 13 | AGREEMENT_LINE | Character(10) | VARCHAR2(10) NOT NULL | This field records the value for the agreement line number |
| 14 | BUSINESS_UNIT_RF | Character(5) | VARCHAR2(5) NOT NULL | Field Services Business Unit |
| 15 | BUSINESS_UNIT_RC | Character(5) | VARCHAR2(5) NOT NULL | Call Center Business Unit |
| 16 | SO_ID | Character(15) | VARCHAR2(15) NOT NULL | Service Order ID |
| 17 | CASE_ID | Number(15,0) | DECIMAL(15) NOT NULL | Case ID. This is a unique ID assigned to a case when it is saved. Unique ID is ensured by getting the next value from the auto-numbering table and updating the table. |
| 18 | BUSINESS_UNIT_RO | Character(5) | VARCHAR2(5) NOT NULL | Order Capture Business Unit |
| 19 | CAPTURE_ID | Character(20) | VARCHAR2(20) NOT NULL | Order Capture ID |
| 20 | INST_PROD_ID | Character(20) | VARCHAR2(20) NOT NULL | This field is used to represent the installed product ID (key field) |
| 21 | PRODUCT_ID | Character(18) | VARCHAR2(18) NOT NULL | Product ID |
| 22 | HOLD_FLAG | Character(1) | VARCHAR2(1) NOT NULL |
Hold
N=Hold Y=Hold |
| 23 | HOLD_DATE | Date(10) | DATE | Put On Hold Date |
| 24 | CA_TXN_TYPE | Character(3) | VARCHAR2(3) NOT NULL |
CRM Transaction Type
01=Agreement 02=Service Order 03=Case 04=Order Capture |
| 25 | GROSS_AMT | Signed Number(28,3) | DECIMAL(26,3) NOT NULL | Gross Invoice Amount |