RC_SOLN_KB_VW

(SQL View)
 Index Back

Solution

Base view for the Solution Management Inisght

SELECT sl.setid , sl.solution_id , SL.RC_SOLUTION_TYPE , (%Sql(RC_XLAT_FIELD_DESC,'RC_SOLUTION_TYPE' ,SL.RC_SOLUTION_TYPE)) , sl.soln_status , (%Sql(RC_XLAT_FIELD_DESC,'SOLN_STATUS' ,sl.soln_status)) , sl.rc_soln_visibility , sl.RC_SUMMARY , sl.EXPIRY_IND , sl.EXPIRY_DATE , sl.USAGE_COUNT , COALESCE (sl.LAST_USAGE_DTTM, sl.START_DATE, sl.ROW_ADDED_DTTM) , sl.RC_SOLVED_COUNT , sl.RC_UNSOLVED_COUNT , sl.FAQ_FLAG , sl.URL , SL.RC_SOLN_OWNERID , CO.RC_SOLN_OWNER_DESC , sl.START_DATE , sl.RC_SS_SOLVED_COUNT , sl.RC_SS_UNSOLVED_CNT , sl.SS_USAGE_COUNT , sl.ROW_ADDED_DTTM , sl.ROW_LASTMANT_DTTM , fav.RC_SOL_FAV_CNT , sl.USAGE_COUNT + sl.SS_USAGE_COUNT Total_usage , CASE WHEN (sl.USAGE_COUNT + sl.SS_USAGE_COUNT) > 0 THEN (sl.RC_SOLVED_COUNT / (sl.USAGE_COUNT + sl.SS_USAGE_COUNT ) ) * 100 ELSE 0 END SOL_SUCCESS_PCT , COALESCE (fdbk.RC_SOL_FDBK_CNT,0) , COALESCE (FDBK.RC_SOL_FDBKOP_CNT ,0) FROM ps_rc_solution sl LEFT OUTER JOIN ps_rc_sol_fav_vw fav ON (sl.setid=fav.setid AND sl.solution_id=fav.solution_id) LEFT OUTER JOIN ps_rc_soln_ctnt_vw CO ON (sl.setid=CO.setid AND SL.RC_SOLN_OWNERID = CO.RC_SOLN_OWNERID) LEFT OUTER JOIN PS_RC_SOL_FDBK_CNT fdbk ON (sl.setid=fdbk.setid AND sl.solution_id=fdbk.solution_id)

# PeopleSoft Field Name PeopleSoft Field Type Database Column Type Description
1 SETID Character(5) VARCHAR2(5) NOT NULL This field is used to store the value of SetID on various setup tables.

Default Value: OPR_DEF_TBL_RB.SETID

Prompt Table: SP_SETID_NONVW

2 SOLUTION_ID Number(15,0) DECIMAL(15) NOT NULL Solution ID

Default Value: 999999999999999

3 RC_SOLUTION_TYPE Character(4) VARCHAR2(4) NOT NULL Solution Type
ADHC=Adhoc Solution
CAND=Canned Solution
FILE=File
LINK=Link
S=Standard Solution
W=Workaround Solution

Default Value: ADHC

4 RC_TYPE_DESCR Character(30) VARCHAR2(30) NOT NULL CTD - Type Description
5 SOLN_STATUS Character(4) VARCHAR2(4) NOT NULL Solution Status
ACTV=4 - Active
DNED=5 - Denied
DRFT=1 - Draft
EXPR=6 - Expired
REVW=3 - In Review
RWRK=8 - Rework
SUBM=2 - Submitted for Review
SUPR=7 - Superseded

Default Value: DRFT

6 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.
7 RC_SOLN_VISIBILITY Character(4) VARCHAR2(4) NOT NULL Solution Visibility
A=All
I=Internal

Default Value: A

8 RC_SUMMARY Character(80) VARCHAR2(80) NOT NULL Typically a summary of a long field.
9 EXPIRY_IND Character(1) VARCHAR2(1) NOT NULL Expiry Indicator
N=Not Expired
Y=Expired

Default Value: N

10 EXPIRY_DATE Date(10) DATE Expiry Date
11 USAGE_COUNT Number(8,0) INTEGER NOT NULL Counter of how often this row was selected in a Search Results page and marked as 'Used'. Usage Count is incremented by the USAGE_INCREMENT amount on the Search Setup Parameters page each time the row is 'Used'.

Default Value: 0

12 LAST_USAGE_DTTM DateTime(26) TIMESTAMP Used to identify last time the record was marked 'Used' in a Search result
13 RC_SOLVED_COUNT Number(6,0) INTEGER NOT NULL Solved Count Field in Solution

Default Value: 0

14 RC_UNSOLVED_COUNT Number(6,0) INTEGER NOT NULL Counting number of times a solution failed to solve cases

Default Value: 0

15 FAQ_FLAG Character(1) VARCHAR2(1) NOT NULL Flag to identify a Frequently Asked Question

Y/N Table Edit

Default Value: N

16 URL Character(254) VARCHAR2(254) NOT NULL Internet URL (Universal Resource Locator)
17 RC_SOLN_OWNERID Character(5) VARCHAR2(5) NOT NULL Solution Content Owner ID field - uppercase denoting an Owner

Prompt Table: RC_SOLN_CTNT_VW

18 RC_SOLN_OWNER_DESC Character(50) VARCHAR2(50) NOT NULL Content owner name
19 START_DATE Date(10) DATE Start Date

Default Value: %Date

20 RC_SS_SOLVED_COUNT Number(6,0) INTEGER NOT NULL Solved Count Field in Solution
21 RC_SS_UNSOLVED_CNT Number(6,0) INTEGER NOT NULL Solved Count Field in Solution
22 SS_USAGE_COUNT Number(8,0) INTEGER NOT NULL Counter of how often this row was selected in a Search Results page and marked as 'Used'. Usage Count is incremented by the USAGE_INCREMENT amount on the Search Setup Parameters page each time the row is 'Used'.
23 ROW_ADDED_DTTM DateTime(26) TIMESTAMP This is a datetime field for record creation.
24 ROW_LASTMANT_DTTM DateTime(26) TIMESTAMP The date and time that the row was last updated.
25 RC_SOL_FAV_CNT Number(5,0) INTEGER NOT NULL Solution Favorite Count
26 TOTAL_USAGE_COUNT Number(8,0) INTEGER NOT NULL Total number of times a solution was used.
27 SUCCESS_PCT Number(6,2) DECIMAL(5,2) NOT NULL Success Percent
28 RC_SOL_FDBK_CNT Number(6,0) INTEGER NOT NULL Solution Feedback Count
29 RC_SOL_FDBKOP_CNT Number(6,0) INTEGER NOT NULL Open Feedback Count