Question
Tata Consultancy Services
IN
Last activity: 11 Jan 2016 18:11 EST
query to get current op ID in sql developer
Hi,
As part of my requirement i have to write an store proc for getnextwork(whenever a user click on the button a case has to be picked based on the skills, prority , region and certain other parameters).
In Oracle Sql developer. i have below tables:
Skill Table:(columns: SKILL_ID, SKILL_SEQ_NO, SKILL_NAME, CALL_TYPE, BUSINESS_UNIT,SPECIALITY_TYPE)
Call Type: (columns: CALL_TYPE, PRIORITY)
OCL Table: contains caseID
Please find the below algorithm:
in sql query how to get the skills of current operator ID so that i can query with the skill table and can get the remaining details for the particular skill.
Please help on this..
Thanks in advance..
GovCIO
US
Hello Saravan,
As pySkills is a pagelist property and is present in BLOB in PR_OPERATORS table, you need to build a logic from Pega by using Obj-Browse and all. Below is the generated SQL from Clipboard after I ran the activity on Operators table using Obj-Browse.
| pxSQLStatementPost | SELECT pzPVStream AS "pzPVStream" from pr_operators WHERE ( PYUSERIDENTIFIER = ? ) AND ( pxObjClass = ? ) |
| pxSQLStatementPre | SELECT pzPVStream AS "pzPVStream" from {CLASS:Data-Admin-Operator-ID} WHERE ( PYUSERIDENTIFIER = {pyReportParameters_temp.pyPreparedValues(1)} ) AND ( pxObjClass = {pyReportParameters_temp.pyPreparedValues(2)} ) |
SingleValue properties of page pySkills(1)
| Property | Value |
| pxObjClass | Embed-Data-Admin-Skill |
| pySkillName | |
pySkillRating |
|
Hope this gives an idea for your requirement.
Thanks,
Hello Saravan,
As pySkills is a pagelist property and is present in BLOB in PR_OPERATORS table, you need to build a logic from Pega by using Obj-Browse and all. Below is the generated SQL from Clipboard after I ran the activity on Operators table using Obj-Browse.
| pxSQLStatementPost | SELECT pzPVStream AS "pzPVStream" from pr_operators WHERE ( PYUSERIDENTIFIER = ? ) AND ( pxObjClass = ? ) |
| pxSQLStatementPre | SELECT pzPVStream AS "pzPVStream" from {CLASS:Data-Admin-Operator-ID} WHERE ( PYUSERIDENTIFIER = {pyReportParameters_temp.pyPreparedValues(1)} ) AND ( pxObjClass = {pyReportParameters_temp.pyPreparedValues(2)} ) |
SingleValue properties of page pySkills(1)
| Property | Value |
| pxObjClass | Embed-Data-Admin-Skill |
| pySkillName | |
pySkillRating |
|
Hope this gives an idea for your requirement.
Thanks,
Ravi Kumar