Sql Scripts
Will contain sql from various custom portlets:
Attachment Quick View:
SELECT b.source_id SOURCE_ID, 'http://pmsapp01.ladbrokes.com:8080/itg/servlet/Document?ID='parameter2chr(38)'viewOnly=TRUE' ATTACHMENT_LINK, a.description DESCRIPTION, b.visible_parameter2 ATTACHMENT, a.creation_date CREATED_ON
FROM knta_references b, kcrt_requests a
WHERE b.visible_parameter2 is not null
AND a.request_id = b.source_id
AND a.request_id in (SELECT a.request_id
FROM kcrt_requests a
WHERE a.status_code NOT LIKE 'CLOSED%'
AND a.status_code NOT LIKE 'CANCEL%')a.creation_date DESC
Added in filter:
created filter, called it Attachment with token name ATTACHMENT.
the where clause filter is:
AND UPPER(b.visible_parameter2) like UPPER('%[P.ATTACHMENT]%')
the field type is text
This allows someone to enter 'sportsbook' into the filter and any attachment with this word in it, either lower or upper case will be found and displayed back to the portlet.
========================================================
Enhancement Info:
SELECT a.request_id REQUEST_ID, a.visible_parameter16 OXI_TEAM, a.visible_parameter15 GAMES_TEAM, a.visible_parameter14 TBS_TEAM, a.visible_parameter13 CORE_TEAM, f.status_name WORKFLOW_STATUS, d.visible_parameter2 PROJECT_MANAGER, d.visible_parameter7 ACE_REVENUE_NUMBER, c.description DESCRIPTION, b.request_type_name REQUEST_TYPE, a.visible_parameter24 PRIORITY, a.parameter44 TECHNICAL_LEAD, a.visible_parameter42 PORTAL, a.Parameter43 TECHNICAL_OWNER, a.visible_parameter22 TOTAL_ESTIMATE, a.visible_parameter21 ESTIMATED_DATE_TO_TEST, a.parameter23 DEVELOPER, a.visible_parameter25 ASSIGNED_TO, a.visible_parameter20 ESTIMATE_OXI, a.visible_parameter19 ESTIMATE_GAMES, a.visible_parameter18 ESTIMATE_TBS, a.visible_parameter17 ESTIMATE_CORE, a.request_id REQUEST_ID_HYPERLINK, a.visible_parameter26 PID_DUE_DATE
FROM kcrt_request_details a, kcrt_request_types b, kcrt_requests c, kcrt_req_header_details d, kcrt_statuses f, knta_users g
WHERE a.batch_number = 1
AND a.request_type_id = 30507
AND a.request_id = c.request_id
AND a.request_id = d.request_id
AND c.request_id = d.request_id
AND a.request_type_id = b.request_type_id
AND a.request_type_id = c.request_type_id
AND c.status_id = f.status_id
AND g.full_name = d.visible_parameter2
AND c.request_id in (SELECT c.request_id
FROM kcrt_requests c
WHERE c.status_code NOT LIKE 'CLOSED%'
AND c.status_code NOT LIKE 'CANCEL%')
--This section comes from the filter fields
AND g.user_id IN ([P.PROJECT_MANAGER])
AND f.status_id in ([P.WORKFLOW_STATUS])
--End section from the filter fields
========================================================
Proposal Info
SELECT a.visible_parameter15 ASSIGNED_TO, a.visible_parameter11 ESTIMATE_GAMES, a.visible_parameter12 ESTIMATE_TBS, a.visible_parameter9 ESTIMATE_CORE, a.request_id REQUEST_ID_HYPERLINK, a.request_id REQUEST_ID, a.visible_parameter5 OXI_TEAM, a.visible_parameter8 GAMES_TEAM, a.visible_parameter7 TBS_TEAM, a.visible_parameter6 CORE_TEAM, a.parameter44 TECHNICAL_LEAD, a.parameter43 DEVELOPER, a.parameter42 TECHNICAL_OWNER, f.status_name WORKFLOW_STATUS, e.Prop_Project_Manager_Username PROJECT_MANAGER, d.visible_parameter7 ACE_REVENUE_CODE, c.description DESCRIPTION, b.request_type_name REQUEST_TYPE, a.visible_parameter16 PRIORITY, a.visible_parameter41 PORTAL, a.visible_parameter14 TOTAL_ESTIMATE, a.visible_parameter13 ESTIMATED_DATE_TO_TEST, a.visible_parameter10 ESTIMATE_OXI, a.visible_parameter17 PID_DUE_DATE
FROM kcrt_request_details a, kcrt_request_types b, kcrt_requests c, kcrt_req_header_details d, kcrt_fg_pfm_proposal e, kcrt_statuses f, knta_users g
WHERE a.batch_number = 1
AND a.request_type_id = 30509
AND a.request_id = c.request_id
AND a.request_id = d.request_id
AND c.request_id = d.request_id
AND a.request_type_id = b.request_type_id
AND a.request_type_id = c.request_type_id
AND d.request_id = e.request_idAND c.status_id = f.status_id
AND c.request_id = e.request_id
AND g.full_name = e.PROP_PROJECT_MANAGER_USERNAMEAND c.request_id in (SELECT c.request_id
FROM kcrt_requests c
WHERE c.status_code NOT LIKE 'CLOSED%'
AND c.status_code NOT LIKE 'CANCEL%')
--This section comes from the filter fields
AND g.user_id IN ([P.PROJECT_MANAGER])
f.status_id in ([P.WORKFLOW_STATUS])
--End section from the filter fields
========================================================
Reports (pulling out user-defined report names to dashboard):
SELECT ks.report_submission_id REPORT_ID, ks.visible_parameter2 REPORT_NAME, ku.username USERNAME, ks.creation_date RUN_DATE, 'http://pmsapp01.ladbrokes.com:8080/itg/reports/rep_' report_submission_id '.html' REPORT_NUMBER_HYPERLINK
FROM knta_report_submissions ks, knta_users ku
WHERE report_type_id in (20020, 20080, 20081, 20161, 20162, 30069)
AND ku.user_id = ks.created_by
ks.report_submission_id DESC
========================================================
Spec Quick View:
SELECT a.description DESCRIPTION, b.visible_parameter3 API_DOCUMENT, b.visible_parameter2 TECHNICAL_SPECIFICATION, b.visible_parameter1 PID, a.request_id REQUEST_NUMBER, a.request_id REQUEST_NUMBER_LINK, 'http://pmsapp01.ladbrokes.com:8080/itg/servlet/Document?ID='b.parameter3chr(38)'viewOnly=TRUE' API_DOC_LINK, 'http://pmsapp01.ladbrokes.com:8080/itg/servlet/Document?ID='b.parameter2chr(38)'viewOnly=TRUE' TECH_SPEC_LINK, 'http://pmsapp01.ladbrokes.com:8080/itg/servlet/Document?ID='b.parameter1chr(38)'viewOnly=TRUE' PID_LINK, c.visible_parameter3 MASTER_DOC, 'http://pmsapp01.ladbrokes.com:8080/itg/servlet/Document?ID='c.parameter3chr(38)'viewOnly=TRUE' MASTER_DOC_LINK, a.creation_date CREATED_ON
FROM kcrt_requests a, kcrt_request_details b, kcrt_req_header_details c
WHERE a.request_id = b.request_id
AND b.request_id = c.request_id
AND a.request_id = c.request_id
AND a.request_type_id in (30501, 30502, 30504, 30506, 30508, 30509, 30112, 30496)
AND a.request_id in (SELECT a.request_id
FROM kcrt_requests a
WHERE a.status_code NOT LIKE 'CLOSED%'
AND a.status_code NOT LIKE 'CANCEL%')a.creation_date DESC
========================================================
-- This script identifies users who still have work relating to them:
SELECT request_id
FROM kcrt_requests
WHERE request_id IN (SELECT source_id
FROM kwfl_resolved_sec_tokens
WHERE user_id IN (SELECT user_id
FROM knta_users
WHERE username IN ('user_name')))
AND status_code = 'IN_PROGRESS'
========================================================
If you want to create "request no" in a portlet and want it to be clickable and not a full url but rather the words "request number",see the attached configuration.
I wanted to add "request no" as a column in a portlet and make it a link, however when I tried it out, I kept getting a long url rather than just the request number. I finally worked it out with a little help from the forum. I have documented the configuration for others who might struggle with it. This URL links to a mercury knowledge base article that I wrote:
http://support.mercury.com/uploads/cmc_upload/0/536/923/227/setting_up_clickable_request_id.doc
=========================================================
Configuring automatic value matching and interactive select page (see page 85 of the Command Token guide):
Auto-complete field behavior can be divided into the following areas:
Field behavior. A user types a character in the field and presses the Tab
key. If an exact match is not available, the Select page opens.
The Select page behavior. For lists that are configured appropriately,
when a user types a character or characters into the field at the top of the
page, the results are automatically limited to display only matching entries.
For both the field and Select page behaviors, automatic value matching can be
based on either “starts with” character matching or “contains” character
matching.
Starts with:
UPPER(value) like UPPER('?%') and (value like
upper(substr('?',1,1)) '%' or value like
lower(substr('?',1,1)) '%')
Contains:
UPPER(value) like UPPER('%?%') and (value like '%'
upper(substr('?',1,1)) '%' or value like '%'
lower(substr('?',1,1)) '%')
To configure “starts with” matching within the interactive selection window:
1. Open the auto-complete’s Validation window.
2. From the Expected list length field, select Short.
This feature is only available for short lists.
3. From the Selection mode radio button, select Starts With.
4. Save the validation
To configure “contains” matching within the interactive selection window:
1. Open the auto-complete’s Validation window.
2. From the Expected list length field, select Short.
This feature is only available for short lists.
3. From the Selection mode radio button, select Contains.
4. Save the validation.
========================================================
How to understand KNTA errors? eg: KNTA-10976
SELECT *
from KNTA_MESSAGES
where message_id = 10976
Returns meaningful data
========================================================
Attachment Quick View:
SELECT b.source_id SOURCE_ID, 'http://pmsapp01.ladbrokes.com:8080/itg/servlet/Document?ID='parameter2chr(38)'viewOnly=TRUE' ATTACHMENT_LINK, a.description DESCRIPTION, b.visible_parameter2 ATTACHMENT, a.creation_date CREATED_ON
FROM knta_references b, kcrt_requests a
WHERE b.visible_parameter2 is not null
AND a.request_id = b.source_id
AND a.request_id in (SELECT a.request_id
FROM kcrt_requests a
WHERE a.status_code NOT LIKE 'CLOSED%'
AND a.status_code NOT LIKE 'CANCEL%')a.creation_date DESC
Added in filter:
created filter, called it Attachment with token name ATTACHMENT.
the where clause filter is:
AND UPPER(b.visible_parameter2) like UPPER('%[P.ATTACHMENT]%')
the field type is text
This allows someone to enter 'sportsbook' into the filter and any attachment with this word in it, either lower or upper case will be found and displayed back to the portlet.
========================================================
Enhancement Info:
SELECT a.request_id REQUEST_ID, a.visible_parameter16 OXI_TEAM, a.visible_parameter15 GAMES_TEAM, a.visible_parameter14 TBS_TEAM, a.visible_parameter13 CORE_TEAM, f.status_name WORKFLOW_STATUS, d.visible_parameter2 PROJECT_MANAGER, d.visible_parameter7 ACE_REVENUE_NUMBER, c.description DESCRIPTION, b.request_type_name REQUEST_TYPE, a.visible_parameter24 PRIORITY, a.parameter44 TECHNICAL_LEAD, a.visible_parameter42 PORTAL, a.Parameter43 TECHNICAL_OWNER, a.visible_parameter22 TOTAL_ESTIMATE, a.visible_parameter21 ESTIMATED_DATE_TO_TEST, a.parameter23 DEVELOPER, a.visible_parameter25 ASSIGNED_TO, a.visible_parameter20 ESTIMATE_OXI, a.visible_parameter19 ESTIMATE_GAMES, a.visible_parameter18 ESTIMATE_TBS, a.visible_parameter17 ESTIMATE_CORE, a.request_id REQUEST_ID_HYPERLINK, a.visible_parameter26 PID_DUE_DATE
FROM kcrt_request_details a, kcrt_request_types b, kcrt_requests c, kcrt_req_header_details d, kcrt_statuses f, knta_users g
WHERE a.batch_number = 1
AND a.request_type_id = 30507
AND a.request_id = c.request_id
AND a.request_id = d.request_id
AND c.request_id = d.request_id
AND a.request_type_id = b.request_type_id
AND a.request_type_id = c.request_type_id
AND c.status_id = f.status_id
AND g.full_name = d.visible_parameter2
AND c.request_id in (SELECT c.request_id
FROM kcrt_requests c
WHERE c.status_code NOT LIKE 'CLOSED%'
AND c.status_code NOT LIKE 'CANCEL%')
--This section comes from the filter fields
AND g.user_id IN ([P.PROJECT_MANAGER])
AND f.status_id in ([P.WORKFLOW_STATUS])
--End section from the filter fields
========================================================
Proposal Info
SELECT a.visible_parameter15 ASSIGNED_TO, a.visible_parameter11 ESTIMATE_GAMES, a.visible_parameter12 ESTIMATE_TBS, a.visible_parameter9 ESTIMATE_CORE, a.request_id REQUEST_ID_HYPERLINK, a.request_id REQUEST_ID, a.visible_parameter5 OXI_TEAM, a.visible_parameter8 GAMES_TEAM, a.visible_parameter7 TBS_TEAM, a.visible_parameter6 CORE_TEAM, a.parameter44 TECHNICAL_LEAD, a.parameter43 DEVELOPER, a.parameter42 TECHNICAL_OWNER, f.status_name WORKFLOW_STATUS, e.Prop_Project_Manager_Username PROJECT_MANAGER, d.visible_parameter7 ACE_REVENUE_CODE, c.description DESCRIPTION, b.request_type_name REQUEST_TYPE, a.visible_parameter16 PRIORITY, a.visible_parameter41 PORTAL, a.visible_parameter14 TOTAL_ESTIMATE, a.visible_parameter13 ESTIMATED_DATE_TO_TEST, a.visible_parameter10 ESTIMATE_OXI, a.visible_parameter17 PID_DUE_DATE
FROM kcrt_request_details a, kcrt_request_types b, kcrt_requests c, kcrt_req_header_details d, kcrt_fg_pfm_proposal e, kcrt_statuses f, knta_users g
WHERE a.batch_number = 1
AND a.request_type_id = 30509
AND a.request_id = c.request_id
AND a.request_id = d.request_id
AND c.request_id = d.request_id
AND a.request_type_id = b.request_type_id
AND a.request_type_id = c.request_type_id
AND d.request_id = e.request_idAND c.status_id = f.status_id
AND c.request_id = e.request_id
AND g.full_name = e.PROP_PROJECT_MANAGER_USERNAMEAND c.request_id in (SELECT c.request_id
FROM kcrt_requests c
WHERE c.status_code NOT LIKE 'CLOSED%'
AND c.status_code NOT LIKE 'CANCEL%')
--This section comes from the filter fields
AND g.user_id IN ([P.PROJECT_MANAGER])
f.status_id in ([P.WORKFLOW_STATUS])
--End section from the filter fields
========================================================
Reports (pulling out user-defined report names to dashboard):
SELECT ks.report_submission_id REPORT_ID, ks.visible_parameter2 REPORT_NAME, ku.username USERNAME, ks.creation_date RUN_DATE, 'http://pmsapp01.ladbrokes.com:8080/itg/reports/rep_' report_submission_id '.html' REPORT_NUMBER_HYPERLINK
FROM knta_report_submissions ks, knta_users ku
WHERE report_type_id in (20020, 20080, 20081, 20161, 20162, 30069)
AND ku.user_id = ks.created_by
ks.report_submission_id DESC
========================================================
Spec Quick View:
SELECT a.description DESCRIPTION, b.visible_parameter3 API_DOCUMENT, b.visible_parameter2 TECHNICAL_SPECIFICATION, b.visible_parameter1 PID, a.request_id REQUEST_NUMBER, a.request_id REQUEST_NUMBER_LINK, 'http://pmsapp01.ladbrokes.com:8080/itg/servlet/Document?ID='b.parameter3chr(38)'viewOnly=TRUE' API_DOC_LINK, 'http://pmsapp01.ladbrokes.com:8080/itg/servlet/Document?ID='b.parameter2chr(38)'viewOnly=TRUE' TECH_SPEC_LINK, 'http://pmsapp01.ladbrokes.com:8080/itg/servlet/Document?ID='b.parameter1chr(38)'viewOnly=TRUE' PID_LINK, c.visible_parameter3 MASTER_DOC, 'http://pmsapp01.ladbrokes.com:8080/itg/servlet/Document?ID='c.parameter3chr(38)'viewOnly=TRUE' MASTER_DOC_LINK, a.creation_date CREATED_ON
FROM kcrt_requests a, kcrt_request_details b, kcrt_req_header_details c
WHERE a.request_id = b.request_id
AND b.request_id = c.request_id
AND a.request_id = c.request_id
AND a.request_type_id in (30501, 30502, 30504, 30506, 30508, 30509, 30112, 30496)
AND a.request_id in (SELECT a.request_id
FROM kcrt_requests a
WHERE a.status_code NOT LIKE 'CLOSED%'
AND a.status_code NOT LIKE 'CANCEL%')a.creation_date DESC
========================================================
-- This script identifies users who still have work relating to them:
SELECT request_id
FROM kcrt_requests
WHERE request_id IN (SELECT source_id
FROM kwfl_resolved_sec_tokens
WHERE user_id IN (SELECT user_id
FROM knta_users
WHERE username IN ('user_name')))
AND status_code = 'IN_PROGRESS'
========================================================
If you want to create "request no" in a portlet and want it to be clickable and not a full url but rather the words "request number",see the attached configuration.
I wanted to add "request no" as a column in a portlet and make it a link, however when I tried it out, I kept getting a long url rather than just the request number. I finally worked it out with a little help from the forum. I have documented the configuration for others who might struggle with it. This URL links to a mercury knowledge base article that I wrote:
http://support.mercury.com/uploads/cmc_upload/0/536/923/227/setting_up_clickable_request_id.doc
=========================================================
Configuring automatic value matching and interactive select page (see page 85 of the Command Token guide):
Auto-complete field behavior can be divided into the following areas:
Field behavior. A user types a character in the field and presses the Tab
key. If an exact match is not available, the Select page opens.
The Select page behavior. For lists that are configured appropriately,
when a user types a character or characters into the field at the top of the
page, the results are automatically limited to display only matching entries.
For both the field and Select page behaviors, automatic value matching can be
based on either “starts with” character matching or “contains” character
matching.
Starts with:
UPPER(value) like UPPER('?%') and (value like
upper(substr('?',1,1)) '%' or value like
lower(substr('?',1,1)) '%')
Contains:
UPPER(value) like UPPER('%?%') and (value like '%'
upper(substr('?',1,1)) '%' or value like '%'
lower(substr('?',1,1)) '%')
To configure “starts with” matching within the interactive selection window:
1. Open the auto-complete’s Validation window.
2. From the Expected list length field, select Short.
This feature is only available for short lists.
3. From the Selection mode radio button, select Starts With.
4. Save the validation
To configure “contains” matching within the interactive selection window:
1. Open the auto-complete’s Validation window.
2. From the Expected list length field, select Short.
This feature is only available for short lists.
3. From the Selection mode radio button, select Contains.
4. Save the validation.
========================================================
How to understand KNTA errors? eg: KNTA-10976
SELECT *
from KNTA_MESSAGES
where message_id = 10976
Returns meaningful data
========================================================
posted by gaz at 12:37 PM
0 Comments:
Post a Comment
<< Home