The following describes features (and tasks required for certification) of what is known as level one query access. A user must complete an IS access request form to request access to the query tool.
Will still be able to create/modify queries (no limit on the number of record joins).
Will require the user pass a certification test after attending a required 2 hour training session. Create a public query (one for each branch of the query security tree granted to you) named their eidxx (xx=sequence number) with the following specifications:
Includes at least one outer join, explain when outer joining is desired/required. Use either old or new outer join syntax. http://download.oracle.com/docs/cd/E15645_01/pt850pbr0/eng/psbooks/tpsq/book.htm?File=tpsq/htm/tpsq06.htm#H4011
Includes at least one prompt and explain when/why prompts are useful. http://download.oracle.com/docs/cd/E15645_01/pt850pbr0/eng/psbooks/tpsq/book.htm?File=tpsq/htm/tpsq04.htm#H4021
Includes at least one effective dated record with appropriate criteria to either look into the future or returns data as of a specific date (versus the default effective dated logic which returns data as of the system date) or returns future dated data. Explain the logic behind effective dating and how it may apply to their specific records. Waive this requirement if NONE of the records the user has access to includes effective dated fields. http://download.oracle.com/docs/cd/E15645_01/pt850pbr0/eng/psbooks/tpsq/book.htm?File=tpsq/htm/tpsq04.htm#H4016
Includes at least one aggregate function (feel free to use a different query name). http://download.oracle.com/docs/cd/E15645_01/pt850pbr0/eng/psbooks/tpsq/book.htm?File=tpsq/htm/tpsq06.htm#H4003
Includes at least one translate value with either the short or long description vs. the field value. http://download.oracle.com/docs/cd/E15645_01/pt850pbr0/eng/psbooks/tpsq/book.htm?File=tpsq/htm/tpsq06.htm#H4003
Includes at least one exists or does not exist sub query OR the ability to explain in writing why such criteria may be desired/required/useful. http://download.oracle.com/docs/cd/E15645_01/pt850pbr0/eng/psbooks/tpsq/book.htm?File=tpsq/htm/tpsq06.htm#H3005
Includes at least one of the three types of drilling URL’s http://download.oracle.com/docs/cd/E15645_01/pt850pbr0/eng/psbooks/tpsq/book.htm?File=tpsq/htm/tpsq04.htm#H3029
Level 2 create query access
The following describes features (and tasks required for certification) of what is known as level two create query access. A user must complete an IS access request form to request access to the query tool.
Nothing will prevent a user from seeking level 2 access if their job duties, and their skills/abilities allow them to become certified.
Will require user pass a certification test on their own time after attending a required 4+ hour 1:1 training session. Will let users seeking this role know they are looking at least a 16 hour time commitment to attend the training session and create the objects for the certification process. In addition to passing the level 1 certification, additional tasks include those below. Embedded hyperlinks can be used to research features.
Query manager task. Create/save a public query named eid01 (replace the three letters eid with your eid). The query should include the following specifications. Note: if all of the following specs are placed in the same query, it may not necessarily return data. The query writer needs to be prepared to answer why the query does not return data.
At least one expression making use of the following SQL commands: DECODE, SUBSTR, CASE, concatenation.
Query viewer task. Demonstrate use of the advanced search functions and explain when each might be used. http://download.oracle.com/docs/cd/E15645_01/pt850pbr0/eng/psbooks/tpsq/chapter.htm?File=tpsq/htm/tpsq03.htm%23g037ee99c9453fb39_ef90c_10c791ddc07_518
Scheduled query task. Schedule your eid01 query with a recurrence of your choosing. http://download.oracle.com/docs/cd/E15645_01/pt850pbr0/eng/psbooks/tgbl/book.htm?File=tgbl/htm/tgbl12.htm#H4003
Connected query task. Create/save a public connected query named eid01 (replace the three letters eid with your eid). The query should include the following specifications. http://download.oracle.com/docs/cd/E15645_01/pt850pbr0/eng/psbooks/tpsq/book.htm
At least one child query with at least one related field mapped to the parent. Use any query naming convention you desire for the parent/child queries.
BI publisher tasks. http://download.oracle.com/docs/cd/E15645_01/pt850pbr0/eng/psbooks/tBI publisher/book.htm
Add a data source, whose source is the connected query previously created. Name the data source your eid. Generate the BI PUBLISHER file.
Create an RTF document which makes use of the data elements present in the connected query previously created. Name the document your eid01. The document should include one table for each of the parent and child queries present in the connected query. Do NOT group the parent and child data.
Create an RTF document which makes use of the data elements present in the connected query previously created. Name the document your eid02. The document should include one table for each of the parent and child queries present in the connected query. Group the parent and child data and be prepared to explain how/why the results may be different than the version of the document without grouping.
Add appropriate report definitions for each of the RTF documents created above. Name them the same as the RTF document and include the RTF document as the only active template. If you care to change any of the default settings in any of the report definitions, be prepared to explain why and what affect the change may have on the output.
