CA_AP_MGTCRM_VW(SQL View) |
Index Back |
|---|---|
CRM Revenue Management View |
| SELECT AP.CONTRACT_NUM ,AP.ACCT_PLAN_ID ,AP.DESCR ,TXN.BASE_CURRENCY ,TXN.BUSINESS_UNIT ,TXN.BUSINESS_UNIT_GL ,HDR.SOLD_TO_CUST_ID ,AP.AP_STATUS ,AP.HOLD_FLAG ,AP.HOLD_DATE ,CRM.CA_TXN_TYPE ,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 , CRM.INST_PROD_ID , CRM.PRODUCT_ID ,TXN.AMOUNT_BSE FROM PS_CA_ACCTPLAN AP ,PS_CA_ACCTG_LN_TXN TXN ,PS_CA_CONTR_HDR HDR ,PS_CA_TXN_CRM CRM WHERE AP.CONTRACT_NUM = TXN.CONTRACT_NUM AND AP.ACCT_PLAN_ID = TXN.ACCT_PLAN_ID AND HDR.CONTRACT_NUM = AP.CONTRACT_NUM AND CRM.CA_TXN_ID = TXN.CA_TXN_ID AND HDR.CA_RQST_SRC <> 'OND' AND TXN.DST_USE = 'REV' AND HDR.CA_STATUS <> 'CLOSED' |
| # | PeopleSoft Field Name | PeopleSoft Field Type | Database Column Type | Description |
|---|---|---|---|---|
| 1 | CONTRACT_NUM | Character(25) | VARCHAR2(25) NOT NULL | Contract |
| 2 | ACCT_PLAN_ID | Character(10) | VARCHAR2(10) NOT NULL | Revenue Recognition Plan |
| 3 | DESCR | Character(30) | VARCHAR2(30) NOT NULL | Description |
| 4 | CURRENCY_CD | Character(3) | VARCHAR2(3) NOT NULL | Currency Code |
| 5 | BUSINESS_UNIT | Character(5) | VARCHAR2(5) NOT NULL | Business Unit |
| 6 | BUSINESS_UNIT_GL | Character(5) | VARCHAR2(5) NOT NULL | GL Business Unit |
| 7 | SOLD_TO_CUST_ID | Character(15) | VARCHAR2(15) NOT NULL | Sold To Customer |
| 8 | AP_STATUS | Character(3) | VARCHAR2(3) NOT NULL |
Rev Rec Plan Status
CAN=Cancelled DON=Completed NEW=Pending PRG=In Progress RDY=Ready RIP=Reversal in Progress RQD=Action Required RVS=Reversed |
| 9 | HOLD_FLAG | Character(1) | VARCHAR2(1) NOT NULL |
Hold
N=Hold Y=Hold |
| 10 | HOLD_DATE | Date(10) | DATE | Put On Hold Date |
| 11 | CA_TXN_TYPE | Character(3) | VARCHAR2(3) NOT NULL |
CRM Transaction Type
01=Agreement 02=Service Order 03=Case 04=Order Capture |
| 12 | SETID_AGR | Character(5) | VARCHAR2(5) NOT NULL | Agreement SetID |
| 13 | AGREEMENT_CODE | Character(30) | VARCHAR2(30) NOT NULL | This field represents the agreement code (name) for an agreement |
| 14 | 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) |
| 15 | AGREEMENT_LINE | Character(10) | VARCHAR2(10) NOT NULL | This field records the value for the agreement line number |
| 16 | BUSINESS_UNIT_RF | Character(5) | VARCHAR2(5) NOT NULL | Field Services Business Unit |
| 17 | SO_ID | Character(15) | VARCHAR2(15) NOT NULL | Service Order ID |
| 18 | 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. |
| 19 | BUSINESS_UNIT_RO | Character(5) | VARCHAR2(5) NOT NULL | Order Capture Business Unit |
| 20 | CAPTURE_ID | Character(20) | VARCHAR2(20) NOT NULL | Order Capture ID |
| 21 | INST_PROD_ID | Character(20) | VARCHAR2(20) NOT NULL | This field is used to represent the installed product ID (key field) |
| 22 | PRODUCT_ID | Character(18) | VARCHAR2(18) NOT NULL | Product ID |
| 23 | AMOUNT | Signed Number(28,3) | DECIMAL(26,3) NOT NULL | Amount |