RC_CASE_SW_VW19(SQL View) |
Index Back |
|---|---|
Dummy View for Case SearchThis view is used to search cases using Site Address. |
| SELECT %Substring(E.BO_NAME_DISPLAY,1,50) , E.BO_NAME_DISP_UPPER , E.BO_NAME_AC , ' ' , ' ' , ' ' , ' ' , ' ' , ' ' , M.BO_NAME , M.BO_NAME_AC , G.DESCRSHORT , G.DESCR , ' ' , ' ' , ' ' , ' ' , ' ' , ' ' , ' ' , ' ' , ' ' , ' ' , ' ' , ' ' , K.PHONE , K.EXTENSION , K.EMAIL_ADDR , K.PHONE , K.EXTENSION , K.EMAIL_ADDR , Q.SETID , Q.RC_SHORT_DESCR , H.CM_TYPE_ID , H.PROFILE_CM_SEQ , H.CM_ID , J.DESCRSHORT , %CurrentDateOut , %CurrentDateOut , K.ADDRESS1 , K.ADDRESS1_AC , K.ADDRESS2 , K.ADDRESS2_AC , K.ADDRESS3 , K.ADDRESS3_AC , K.ADDRESS4 , K.ADDRESS4_AC , K.CITY , K.CITY_AC , K.STATE , K.POSTAL , K.COUNTRY , ' ' , ' ' , ' ' , ' ' %Sql(RC_CASE_SBR_UPDATE, O) , O.SYNCID %Sql(RC_CASE_2_SBR_UPDATE, O) %Sql(RCTCASESRCH_SBR) %Sql(RCTCASESRCH2SBR) FROM PS_RC_CASE O , PS_BO_NAME E , PS_RD_SITE L , PS_BO_NAME M , PS_BO_ROLE_TYPE G , PS_RC_STATUS_TBL Q , PS_SET_CNTRL_REC R , PS_BO_CM H , PS_CM_PURP_TYPE J , PS_CM K WHERE O.BO_ID_CUST = E.BO_ID AND E.PRIMARY_IND = 'Y' AND L.BO_ID = O.BO_ID_SITE AND L.BO_ID = M.BO_ID AND M.PRIMARY_IND = 'Y' AND G.ROLE_TYPE_ID = 3 AND O.RC_STATUS = Q.RC_STATUS AND Q.SETID = R.SETID AND R.SETCNTRLVALUE = O.BUSINESS_UNIT AND R.RECNAME = 'RC_STATUS_TBL' AND L.BO_ID = H.BO_ID AND H.BO_CM_START_DT <= %CurrentDateIn AND H.BO_CM_END_DT > %CurrentDateIn AND H.CM_TYPE_ID = J.CM_TYPE_ID AND H.CM_PURPOSE_TYPE_ID = J.CM_PURPOSE_TYPE_ID AND H.CM_ID = K.CM_ID AND H.CM_TYPE_ID = K.CM_TYPE_ID AND K.CM_TYPE_ID = 1 |
| # | PeopleSoft Field Name | PeopleSoft Field Type | Database Column Type | Description |
|---|---|---|---|---|
| 1 | BO_NAME | Character(50) | VARCHAR2(50) NOT NULL | Name Description of Business Object |
| 2 | BO_NAME_DISP_UPPER | Character(80) | VARCHAR2(80) NOT NULL | Full name of the Business Object in Upper |
| 3 | BO_NAME_AC | Character(50) | VARCHAR2(50) NOT NULL | Name Description of Business Object |
| 4 | BO_NAME_2 | Character(50) | VARCHAR2(50) NOT NULL | BO Name 2 |
| 5 | BO_NAME_CONTACT_AC | Character(50) | VARCHAR2(50) NOT NULL | Alt-Char Contact Name |
| 6 | BO_NAME_3 | Character(50) | VARCHAR2(50) NOT NULL | BO Name 3 |
| 7 | BO_NAME_SITE_AC | Character(50) | VARCHAR2(50) NOT NULL | Name Description of Business Object |
| 8 | BO_NAME_DISPLAY_4 | Character(80) | VARCHAR2(80) NOT NULL | Full name of the Business Object |
| 9 | BO_NAME_PARTCNT_AC | Character(50) | VARCHAR2(50) NOT NULL | Partner Contact Name in Alternate Characters. |
| 10 | BO_NAME_DISPLAY_5 | Character(80) | VARCHAR2(80) NOT NULL | Full name of the Business Object |
| 11 | BO_NAME_PARTNER_AC | Character(50) | VARCHAR2(50) NOT NULL | This field stores the Partner name in Alternate Character format. |
| 12 | ROLE_TYPE_DESCR | Character(10) | VARCHAR2(10) NOT NULL | Short Description |
| 13 | DESCR | Character(30) | VARCHAR2(30) NOT NULL | Description |
| 14 | RC_CONTACT_NAME | Character(50) | VARCHAR2(50) NOT NULL | The name of the customer contact or the employee based on support/helpdesk. |
| 15 | CUST_STATUS | Character(1) | VARCHAR2(1) NOT NULL |
Customer Status Indicator. BC.CUST_STATUS
A=Active I=Inactive |
| 16 | EMPL_STATUS | Character(1) | VARCHAR2(1) NOT NULL |
Employment Status. e.g., Active, Leave of Absence, Terminated, Retired, etc.
A=Active D=Deceased L=Leave of Absence P=Leave With Pay Q=Retired With Pay R=Retired S=Suspended T=Terminated U=Terminated With Pay V=Terminated Pension Pay Out W=Short Work Break X=Retired-Pension Administration |
| 17 | EMPLID | Character(11) | VARCHAR2(11) NOT NULL | Employee ID |
| 18 | EBS_EMPLID | Character(30) | VARCHAR2(30) NOT NULL | EBS Employee Number used in the EBS - HR HelpDesk integration. |
| 19 | SETID_DEPT | Character(5) | VARCHAR2(5) NOT NULL | Department SetID |
| 20 | DEPTID | Character(15) | VARCHAR2(15) NOT NULL | Department |
| 21 | RC_DEPT_DESCR | Character(30) | VARCHAR2(30) NOT NULL | Department Description to be displayed on HelpDesk page. |
| 22 | SETID_LOCATION | Character(5) | VARCHAR2(5) NOT NULL | Location SetID |
| 23 | LOCATION | Character(15) | VARCHAR2(15) NOT NULL | This field stores the Location Code. |
| 24 | RC_LOC_DESCR | Character(30) | VARCHAR2(30) NOT NULL | Descripton of the Location to be displayed in helpdesk page. |
| 25 | PHYSICAL_LOCATION | Character(30) | VARCHAR2(30) NOT NULL | Physical Location (Free-Form) |
| 26 | RC_PHONE_CUST | Character(24) | VARCHAR2(24) NOT NULL | This is the phone field used in case search. The reason is to have the ability to enter partial value for the phone and search on it. The standard PHONE field has a format mask associated with it, which does not permit entering partial values. |
| 27 | EXTENSION_CUST | Character(6) | VARCHAR2(6) NOT NULL | Customer Extension |
| 28 | EMAIL_ADDR_CUST | Character(70) | VARCHAR2(70) NOT NULL | email address for the customer - used in case |
| 29 | RC_PHONE | Character(24) | VARCHAR2(24) NOT NULL | This is the phone field used in case search. The reason is to have the ability to enter partial value for the phone and search on it. The standard PHONE field has a format mask associated with it, which does not permit entering partial values. |
| 30 | EXTENSION | Character(6) | VARCHAR2(6) NOT NULL | This field stores the Phone extension. |
| 31 | EMAIL_ADDR | Character(70) | VARCHAR2(70) NOT NULL | Email address to store the address of email. |
| 32 | SETID_STATUS | Character(5) | VARCHAR2(5) NOT NULL | MOC SETID Field |
| 33 | RC_DESC_STATUS | Character(20) | VARCHAR2(20) NOT NULL | Used to display short description of Case Status in search results grid on case search page. |
| 34 | CM_TYPE_ID | Number(6,0) | INTEGER NOT NULL | Contact Method Type ID |
| 35 | PROFILE_CM_SEQ | Number(31,0) | DECIMAL(31) NOT NULL | The Contact Method Profile ID is a key field that is used to reference the details of a Contact Method in various CM tables. |
| 36 | CM_ID | Number(31,0) | DECIMAL(31) NOT NULL | Contact Method ID |
| 37 | RC_PURP_TYPE_DESCR | Character(30) | VARCHAR2(30) NOT NULL | Short Description |
| 38 | START_DT | Date(10) | DATE | Start date field to store start date value. |
| 39 | END_DT | Date(10) | DATE | This field stores the end date values. |
| 40 | ADDRESS1 | Character(55) | VARCHAR2(55) NOT NULL | Address 1 |
| 41 | ADDRESS1_AC | Character(55) | VARCHAR2(55) NOT NULL | ADDRESS1_AC |
| 42 | ADDRESS2 | Character(55) | VARCHAR2(55) NOT NULL | Address 2 |
| 43 | ADDRESS2_AC | Character(55) | VARCHAR2(55) NOT NULL | ADDRESS2_AC |
| 44 | ADDRESS3 | Character(55) | VARCHAR2(55) NOT NULL | Address 3 |
| 45 | ADDRESS3_AC | Character(55) | VARCHAR2(55) NOT NULL | ADDRESS3_AC |
| 46 | ADDRESS4 | Character(55) | VARCHAR2(55) NOT NULL | Address 4 |
| 47 | ADDRESS4_AC | Character(55) | VARCHAR2(55) NOT NULL | Alternate Character Address 4. |
| 48 | CITY | Character(30) | VARCHAR2(30) NOT NULL | City |
| 49 | CITY_AC | Character(30) | VARCHAR2(30) NOT NULL | CITY_AC |
| 50 | STATE | Character(6) | VARCHAR2(6) NOT NULL | State |
| 51 | POSTAL | Character(12) | VARCHAR2(12) NOT NULL | Postal |
| 52 | COUNTRY | Character(3) | VARCHAR2(3) NOT NULL | Country |
| 53 | NATIONAL_ID | Character(20) | VARCHAR2(20) NOT NULL | National ID |
| 54 | NAME_ASSIGNED_TO | Character(80) | VARCHAR2(80) NOT NULL | The name of the assigned agent. |
| 55 | WO_ID | Character(10) | VARCHAR2(10) NOT NULL | This field contains the Work Order ID for the work order in FMS. |
| 56 | DESCR254 | Character(254) | VARCHAR2(254) NOT NULL | Description of length 254 |
| 57 | 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. |
| 58 | BUSINESS_UNIT | Character(5) | VARCHAR2(5) NOT NULL | Business Unit |
| 59 | RC_VERTICAL | Character(4) | VARCHAR2(4) NOT NULL |
Specifies the CallCenter type.
See translates for valid values.
HD=HelpDesk SW=Support |
| 60 | MARKET | Character(3) | VARCHAR2(3) NOT NULL | Market |
| 61 | RC_SUMMARY | Character(80) | VARCHAR2(80) NOT NULL | Typically a summary of a long field. |
| 62 | RC_STATUS | Character(5) | VARCHAR2(5) NOT NULL | Case Status |
| 63 | CASE_TYPE | Character(5) | VARCHAR2(5) NOT NULL | Case Type |
| 64 | CASE_SUBTYPE | Character(5) | VARCHAR2(5) NOT NULL | Field to capture the case subtype. |
| 65 | RC_SOURCE | Character(5) | VARCHAR2(5) NOT NULL | Case Source How this case origianated |
| 66 | RC_PRIORITY | Character(5) | VARCHAR2(5) NOT NULL | Case Priority |
| 67 | URGENCY | Character(5) | VARCHAR2(5) NOT NULL | Case Urgency |
| 68 | RC_SEVERITY | Character(5) | VARCHAR2(5) NOT NULL | Case severity |
| 69 | RC_CATEGORY | Character(5) | VARCHAR2(5) NOT NULL | CallCenter Category. Used in HD. |
| 70 | RC_TYPE | Character(5) | VARCHAR2(5) NOT NULL | Specialty type. Used on HD case. |
| 71 | RC_DETAIL | Character(5) | VARCHAR2(5) NOT NULL | Detail field on HD Case. Used in Quick Code. |
| 72 | CREATION_DATE | Date(10) | DATE | Creation date of the case. |
| 73 | CLOSED_DATE | Date(10) | DATE | Date Closed |
| 74 | ROW_ADDED_DTTM | DateTime(26) | TIMESTAMP | This is a datetime field for record creation. |
| 75 | CLOSED_DTTM | DateTime(26) | TIMESTAMP | Date Time stamp for when the case status category was changed to closed. |
| 76 | PROBLEM_TYPE | Character(8) | VARCHAR2(8) NOT NULL | Problem Type for Support Cases |
| 77 | SETID_PRODUCT | Character(5) | VARCHAR2(5) NOT NULL | Product SetID |
| 78 | INST_PROD_ID | Character(20) | VARCHAR2(20) NOT NULL | This field is used to represent the installed product ID (key field) |
| 79 | SERIAL_ID | Character(20) | VARCHAR2(20) NOT NULL | This field is used to represent the Serial ID of an item or installed product |
| 80 | PRODUCT_GROUP | Character(10) | VARCHAR2(10) NOT NULL | PRoduct Group |
| 81 | PRODUCT_ID | Character(18) | VARCHAR2(18) NOT NULL | Product ID |
| 82 | ASSETTAG | Character(40) | VARCHAR2(40) NOT NULL | Asset Tag |
| 83 | SETID_PROV_GRP | Character(5) | VARCHAR2(5) NOT NULL | SetID |
| 84 | PROVIDER_GRP_ID | Character(10) | VARCHAR2(10) NOT NULL | Identification number for the provider group. |
| 85 | ASSIGNED_TO | Character(15) | VARCHAR2(15) NOT NULL | This is a field to store assignment information. |
| 86 | RC_TRACKING_NBR | Character(30) | VARCHAR2(30) NOT NULL | Customer Tracking Number Field. |
| 87 | SECURE_CASE_FLG | Character(1) | VARCHAR2(1) NOT NULL |
Used in HRHD to Secure the case so that only people in the provider group assigned to the case can access the case.
N=No Y=Yes |
| 88 | REMOTE_CASE_ID | Character(18) | VARCHAR2(18) NOT NULL | Remote system case ID. Used to store case unique identify ID from 3rd party system. |
| 89 | DISP_TMPL_FAM_CD | Character(30) | VARCHAR2(30) NOT NULL | Display Template Family Code |
| 90 | RB_ANONYMOUS_FLAG | Character(1) | VARCHAR2(1) NOT NULL | RB_ANONYMOUS_FLAG |
| 91 | PROFILE_CM_SEQ_ADD | Number(31,0) | DECIMAL(31) NOT NULL | This field is used to store the profile contact method sequence number for an address. |
| 92 | PROFILE_CM_SEQ_EML | Number(31,0) | DECIMAL(31) NOT NULL | Used to store the contact method sequence number for a email address. |
| 93 | PROFILE_CM_SEQ_PHN | Number(31,0) | DECIMAL(31) NOT NULL | Used to store the contact method sequence number for a phone number. |
| 94 | SYNCID | Number(10,0) | DECIMAL(10) NOT NULL | The Synchronization ID field stores a value generated by the sync processor. The value is used to identify the type of object that the sync processor is about to handle. |
| 95 | SETID_CUSTOMER | Character(5) | VARCHAR2(5) NOT NULL | Customer SetID |
| 96 | BO_ID_PARTNER | Signed Number(32,0) | DECIMAL(31) NOT NULL | Business Object ID of Partner Company. |
| 97 | ROLE_TYPE_ID_PART | Number(12,0) | DECIMAL(12) NOT NULL | Stores ROLE_TYPE_ID for the partner |
| 98 | BO_ID_PART_CONT | Signed Number(32,0) | DECIMAL(31) NOT NULL | This is the business object identifier for the partner contact. |
| 99 | ROLE_TYPE_ID_PARTC | Number(12,0) | DECIMAL(12) NOT NULL | Stores ROLE_TYPE_ID for the partner contact |
| 100 | BO_ID_CUST | Number(31,0) | DECIMAL(31) NOT NULL | This field stores the Business Object ID for the customer. |
| 101 | ROLE_TYPE_ID_CUST | Number(12,0) | DECIMAL(12) NOT NULL | Stores ROLE_TYPE_ID for the customer |
| 102 | BO_ID_CONTACT | Number(31,0) | DECIMAL(31) NOT NULL | This field indicates the Business Object ID for the contact. |
| 103 | ROLE_TYPE_ID_CNTCT | Number(12,0) | DECIMAL(12) NOT NULL | Stores ROLE_TYPE_ID for the contact |
| 104 | BO_ID_SITE | Number(31,0) | DECIMAL(31) NOT NULL | Business Object ID |
| 105 | ROLE_TYPE_ID_SITE | Number(12,0) | DECIMAL(12) NOT NULL | Role Type ID |
| 106 | SITE_ID | Character(15) | VARCHAR2(15) NOT NULL | Uniquely identifies an organizational entity that purchases goods or services from the enterprise. Represents the sold-to customer which is the customer organization that places orders. The CUST_ID may or may not be the same as the identifiers for the bill-to and ship-to locations. |
| 107 | PERSON_PIN | Character(40) | VARCHAR2(40) NOT NULL | This field is used to store the Personal Identification Number of a person for use in agreements |
| 108 | SIN | Character(40) | VARCHAR2(40) NOT NULL | This field is used to represent the Site Identification Number |
| 109 | CASE_CONTACT | Character(15) | VARCHAR2(15) NOT NULL | Contact Identifier for Treasury Contacts |
| 110 | SETID_ENTL | Character(5) | VARCHAR2(5) NOT NULL | SetID |
| 111 | BO_ID_AGR | Number(31,0) | DECIMAL(31) NOT NULL | Business Object ID |
| 112 | ROLE_TYPE_ID_AGR | Number(12,0) | DECIMAL(12) NOT NULL | Stores ROLE_TYPE_ID for the contact |
| 113 | RBTACCTNO | Character(20) | VARCHAR2(20) NOT NULL |
Account No
Prompt Table: RCT_ACCOUNT_VW |
| 114 | RBTACCTID | Character(15) | VARCHAR2(15) NOT NULL | Unique service account indentifier |
| 115 | RCTINSTPHONENO | Character(11) | VARCHAR2(11) NOT NULL | RCTINSTPHONENO |