SQL Fundamentals

Introduction to SQL and Syntax

What is SQL?

SQL stands for Structured Query Language.

is a standard programming language for accessing databases.

What can SQL do?

execute, retrieve , insert , update, delete, create, set permission.

Types of SQL Commands/Syntax

Data retrieval is a language used to allow users select records from the database

DML is a language that enables users to access or manipulate data as organized by the appropriate data model.

DDL is a language used to allow users to define the database and its objects. 

TC includes SQL transactions used to control transactions. 

DCL is a language that provides users with privilege commands

Constraints

Joins

Sorting and Filtering Data

Filtering

Use of WHERE clause

The use of LIKE, IN, EXISTS, BETWEEN, ANY, SOME, and ALL comparison conditions

Logical condition precedence: (), NOT, AND, OR

NULL values and sorting

Sorting

Use of ORDER BY clause

Sorting results in ascending and descending order 

Sorting by Multiple Columns 

Sorting by one column, then another 2. Understanding the order of sorting columns

Grouping and Aggregating Data (GROUP BY, HAVING)

Indexes

Naming Conventions

Oracle SQL Functions

There are predefined functions within SQL that can be used to perform specific actions on the data retrieved. Commonly used in our current code is:

Single Row Functions

Numeric Functions (i.e. ABS, CEIL, FLOOR, MOD, TRUNC, ROUND)

Character Functions returning Character Values (i.e. REPLACE, LPAD, RPAD, LTRIM, RTRIM, CONCAT, TRIM, SUBSTR)

Character Functions returning Numeric Values (i.e. LENGTH, INSTR)

Aggregate Functions- Returns a Single Row for the Entire Set

Typically needs the GROUP BY Clause to work (i.e. AVG, MIN, MAX, SUM, RANK, DENSE_RANK, COUNT)

Analytic Functions – Can return a multi-row data set, with one column having the calculated value

PARTITION BY

ORDER BY in the SELECT clause

OVER

WHERE clause

Used to filter data based on a set criteria with the use of different filtering conditions:

Equi (=), Anti (!=, <>), and Range (<, >, <=, >=). 

LIKE / NOT LIKE

IN / NOT IN

EXISTS / NOT EXISTS

BETWEEN

IS NULL / IS NOT NULL

Indexes

B-Tree Index

Bitmap Index

Function-Based Index

Reverse Key Index

Index-Organized Table (IOT)

Domain Index

Bitmap Join Index

Spatial Index

Moving Soon!

 Hey Guys, I've started a website of my own and will be publishing articles there moving forward.

Take a look if you're interested: www.migsisip.com/

Thanks for all your support throughout the years!

Stop using Dynamic SQL! Seriously.

From my experience, research and general knowledge, Dynamic SQL should be avoided as much as possible,

There are multiple reliable sources that say this, even Oracle’s revered top DBA, Tom Kyte, and Oracle’s own PL/SQL Evangelist and Oracle ACE, Steven Feuerstein has mentioned this multiple times in AskTOM and Oracle Blogs.

To list some of the disadvantages of Dynamic SQL (from research and my own inputs):

It cannot be detected by the Oracle Enterprise Manager to have bad statistics because it is not parsed before it is run. It’s basically invisible to the DB parser.

The query this is not being “recorded” in the pool, therefore, we cannot predict if this certain piece of code will perform badly or not, we cannot quantify its statistics.

There are great efficiencies to be gained by using static sql, as PLSQL will cacheopen cursors and reuse statements, even before they are run.

Dynamic SQL is not scalable. Static SQL is scalable.

Does not really have any big advantage over static SQL apart from being “flexible”, but the disadvantages greatly outweighs these advantages.

It’s actually not true that Dynamic SQL performs better, in fact: dynamic SQL is more complex than static SQL, it executes more slowly than static SQL. Especially when done wrong.

We should Re-use code whenever possible, but only when that is appropriate. And I believe we can re-use code without doing Dynamic SQL. Do we REALLY need that SQL to be dynamic? 

Dynamic SQL is only really considered when we don’t know what parameters are used before runtime, something like in OBIEE, where the user will be the one to input the columns they want and need, then run the query.

I believe we can re-write these programs to use fixed, structured, and static queries instead.

Dynamic SQL is prone to SQL-Injection in General. I don’t know if there will be a time where we will encounter this issue, but it will be a huge mess if it does happen.

Personally, I find them extremely hard to debug, hard to read, and impractical because we only find out the actual query at runtime.

We cannot see the dependencies between programs because as I mentioned, they are invisible and only appear during runtime.

It will be hard to see which programs get impacted when we change a certain code, which in turn, will result in another bug, etc. 

I can list down more Pros and Cons, but I believe these are enough details to establish that the disadvantages of Dynamic SQL outweighs the advantages.

I strongly urge that our dev teams start to write Static SQL instead of Dynamic SQL.

To be edited further...

Converting Financial Reports' Amounts to Words with Currencies

If ever you need to add currencies into Words and have to include their Decimals (i.e. Cents), this query below demonstrates that using to TO_CHAR(TO_DATE(X, 'J'), 'JSP') trick:

SELECT  AMOUNT_SIGN||AMOUNT_IN_WORDS COMPLETE_AMOUNT_IN_WORDS
FROM    (SELECT  AMOUNT
        ,   CASE WHEN SIGN_AMT is not null then SIGN_AMT||' ' END AMOUNT_SIGN
        ,   CASE WHEN DOLLAR_AMT IS NOT NULL AND CENT_AMT IS NOT NULL THEN
                DOLLAR_AMT||' AND '||CENT_AMT
                 WHEN DOLLAR_AMT IS NULL AND CENT_AMT IS NOT NULL THEN
                CENT_AMT
                 WHEN DOLLAR_AMT IS NOT NULL AND CENT_AMT IS NULL THEN
                DOLLAR_AMT
            END AMOUNT_IN_WORDS
    FROM    (select  AMOUNT
                ,   CASE WHEN INSTR(AMOUNT, '-') > 0 THEN
                        'NEGATIVE'
                    END SIGN_AMT
                ,   CASE WHEN ABS(amount) >= 1 THEN -- ONLY THE ONES WITH WHOLE NUMBERS
                        to_char( to_date(TRUNC(ABS(amount)), 'J'), 'JSP') || ' BAHT' 
                    END DOLLAR_AMT
                ,   CASE WHEN INSTR(AMOUNT, '.') > 0 THEN -- ONLY THE ONES WITH DECIMALS
                        TO_CHAR(to_date(RPAD(SUBSTR(AMOUNT, INSTR(AMOUNT, '.') + 1), 2, '0'), 'J'), 'JSP') || ' SATANG'
                    END CENT_AMT
            from    (select 30 amount from dual
                    union 
                    select 30.500 amount from dual
                    union 
                    select 0.501 amount from dual
                    union
                    select -30 amount from dual
                    union 
                    select -30.500 amount from dual
                    union 
                    select -0.501 amount from dual
                    )
                )
            );

This sample above shows six scenarios:

1. Positive Whole Numbers only
2. Positive Whole Numbers with Decimals
3. Positive Decimals Only
4. Negative Whole Numbers only
5. Negative Whole Numbers with Decimals
6. Negative Decimals Only

Just change the Currencies if needed.

For more full-detailed Tutorials and Tips, check out #TheOracleProdigy at https://lifeofanoracleprodigy.blogspot.com/
Follow The Oracle Prodigy on Facebook (https://www.facebook.com/theOracleProdigy/) and Twitter (https://twitter.com/D_OracleProdigy)

Oracle SQL Tuning Fundamentals

Introduction & Objectives

Structured Query Language (SQL) is basically a language for storing, manipulating and retrieving data in databases. Although there are a lot of “flavors” of SQL (MySQL, SQL Server, MS Access, Oracle, Sybase, Informix, Postgres). The American National Standards Institute (ANSI) has set a standard in 1986 that most RDBMS’s employ.
While most simple SQL Queries are straight-forward and quick to execute, there are instances wherein we experience a slowness in the query execution. In this deck, we will explore, identify and address the numerous factors that come into play in this performance degradation.

Target Audience

The target audience of this deck would be individuals with sufficient experience in writing SQL queries and have sufficient knowledge in Database Fundamentals.
You should be able to:

• Understand why SQL Tuning is Important
• Understand when to Tune a SQL Script
• Understand the needed skills and knowledge to Tune SQL
• Understand how to achieve your tuning goals

Why should we tune SQL?

Two Words: Optimum Performance!

• Query Performance can be affected by the following:
• Hardware
• Network
• Application
• Program (i.e. PL/SQL Code)
• SQL Code

When the underlying SQL is Tuned effectively, The PL/SQL program is tuned as well! I know what you’re thinking:

“Isn’t there an automatic way to Tune SQL?”

• Yes, but it is costly and proprietary.
• Yes, but sometimes its not good enough.
• Yes, but sometimes it’s even worse than the manual method.

“If that’s the case, then what’s the solution?”

• Manual SQL Tuning Addresses the concerns above. Why? Because:
  1. It’s Free
  2. Improves coding skill
  3. Provides more control over your code

When do we need to Tune SQL?

Ideally, the time we develop code is the time we need to consider efficient and fast queries.

• A good, well-thought out and defined structure will yield the best results when it comes to performance.
• Planning the table structure, indexes, data types and unique identifiers and the data is highly valuable when it comes to performance.

However, if it is unavoidable for the objects have already been existing for some time, tuning is still possible. Consider tuning when the following occurs:

1. There is a Change of Hardware
   • If there is a hardware update in terms of processor, memory, storage, it might affect the performance of the SQL Query.
   • Usually occurs during an upgrade or downgrade of the actual server.
2. There is a Change of Table Structure (i.e. New or Modified Columns, Indexes, etc.)
   
   • If there are changes in the table that is referenced by the query, this may impact the retrieval of data.
   • If indexes are changed, there will be a significant performance impact to the query.
3. Significant change in the amount of Data
   • Data count significantly affects the query’s performance, affecting cardinality, selectivity and cost.
4. Change of Query Script
   • If a Report’s query has been changed, there is a high chance that the performance of the report will also change

What you need to know before you tune a SQL?

Before doing the actual work of tuning a script, one needs to know first the environment. This is highly important as this will lead you to the use best approach for the current problem at hand and ensuring that it doesn’t happen in the future.

Below are the things you need to know before turning the script.

1. Knowledge and Skill in SQL and Database Fundamentals
   • A good grasp of SQL skill and knowledge will be the first and foremost thing you need to know to tune SQL.
   • What SQL operators you need to use and what clauses you need to add will greatly reduced the work you need to put into tuning a script.
2. Familiarity with the Table Structure
   • Knowing what the table is used for, its structure, and its data is essential for effective and efficient tuning.
   • Correct Identification of each column’s use allows us to formulate a better plan to tune the SQL.
3. Familiarity with Index Types and the Explain Plan
   • Knowing the different index types, their usages and their advantage and disadvantages is highly recommended for you to identify if the index you want to use is actually being used by the explain plan.

How to Tune SQL?

Below are my personal guidelines on how to tune SQL scripts:

1. Identify High-Load SQLs
   1. Unless specified, you would need to figure out the under performing queries.
   2. There are numerous methods to know the Top High Load SQL statements in a Database

      1. Using the Oracle Enterprise Manager (OEM)

      2. Using the Dynamic Performance Views such as v$SQL and v$SQL_PLAN

2. Measure current performance metric
   1. For us to compare if the changes that will be applied to the script has any real improvement, it is imperative that we take a snapshot of how the performance was before it was modified.
   2. We would then need to take either of the following before Modification of the SQL Script:
      1. AWR
      2. SQLHC
      3. Explain Plan
3. Identify the root cause
4. Analyze and Identify the Best approach
   1. Re-write the script?
   2. Correct the Data?
   3. Add Indexes?
   4. Change the Table Structure?
   5. Split the table?
   6. Convert to Materialized Views?
   7. Use Hints?
   8. Change the Database Parameters?
5. Test, Test and Test
6. Measure the new performance metric
   1. Similar to Step #2, we would then need to take a snapshot of the performance after the script modification. The same tools apply to getting the metrics.

Cardinality, Selectivity and Cost

Oracle uses the Cost Estimator, to estimate the resources that will be used to execute a given query using the following measurements:

1. Selectivity
   1. The percentage of rows in the row set that the query selects, with 0 meaning no rows and 1 meaning all rows. Selectivity is tied to a query predicate, such as WHERE last_name LIKE 'A%', or a combination of predicates. Simplest term: Uniqueness
   2. A record becomes more selective as the selectivity value approaches 0 and less selective (or more unselective) as the value approaches 1.
2. Cardinality
   1. The cardinality is the estimated number of rows returned by each operation in an execution plan. Cardinality can be derived from the table statistics collected by DBMS_STATS, or derived after accounting for effects from predicates (filter, join, and so on), DISTINCT or GROUP BY operations, and so on.
3. Cost
   1. This measure represents units of work or resource used. The query optimizer uses disk I/O, CPU usage, and memory usage as units of work.

These three are very important for the estimator in figuring out how to map the Explain Plan, what access paths to be used and what Indexes should be used to execute the query.

Indexes

What are Indexes?

By definition: An index is a schema object that contains an entry for each value that appears in the indexed column(s) of the table or cluster and provides direct, fast access to rows.
Oracle Database supports the following indexes:

1. Binary Tree (B-Tree Index)
   • These indexes are the standard index type and uses only 1 column
   • They are excellent for primary key and highly-selective indexes.
   • B-tree indexes have the following subtypes
     1. Composite Indexes
     2. Index-organized tables (IoT)
     3. Reverse key indexes
     4. Descending indexes
     5. B-tree cluster indexes
2. Bitmap Index - In a bitmap index, an index entry uses a bitmap to point to multiple columns. In contrast, a B-tree index entry points to a single row. A bitmap join index is a bitmap index for the join of two or more tables.
3. Partitioned Indexes
4. Function-based Indexes
5. Application Domain Index - An Index that is application-specific, can be used inside or outside the Oracle Database.

Binary Tree (B-Tree Index)

Bitmap Index

Partitioned Indexes

Function-based Indexes

Application Domain Index

The Explain Plan

Access Paths

Types of Access Paths

Access Paths (aka Execution Paths), is basically the "road" that the parser plans to traverse to execute the query script, step-by-step.
An Access Path is shown inside an Explain plan. Treat Explain Plan as the Map, and the Access Path as the Road.
Depending on your various factors (i.e. Tables, Complexity, Database Parameters), the Explain plan will show you the recommended approach the parser will use to execute your query.

There are multiple types of Access Paths, as listed below:

• Full Table Scans
• Table Access by RowId
• Sample Table Scans
• By Index
  • Index Unique Scans
  • Index Range Scans
  • Index Full Scans
  • Index Fast Full Scans
  • Index Skip Scans
  • Index Join Scans
• By Bitmap
• Bitmap Index Single Value
• Bitmap Index Range Scans
• Bitmap Merge
• Bitmap Index Range Scans
• Cluster Scans
• Hash Scans

References

• Manuel, J., Oracle SQL Tuning Training (Oracle University – TechEdge Solutions)
• http://stackoverflow.com/questions/10621077/what-is-cardinality-in-databases
• http://docs.oracle.com/cd/A87860_01/doc/inter.817/a77063/aoptim2.htm
• https://docs.oracle.com/database/121/TGSQL/tgsql_optcncpt.htm
• https://docs.oracle.com/cloud/latest/db112/CNCPT/indexiot.htm#CNCPT1182
• https://use-the-index-luke.com/
• https://www.w3schools.com/sql/
• https://www.iso.org/standard/63555.html
• https://docs.oracle.com/cd/B19306_01/server.102/b14200/statements_5010.htm#:~:text=Oracle%20Database%20supports%20several%20types,key%20value%20as%20a%20bitmap

Fixing "failed to validate certificate nonforms" issue in E-Business Suite R12

When opening Forms, the error encountered is below:

This is because the certificate is not Imported into the Security Console. If you have the certificate file, you can import the file into the Security Console:

However, If the certificate is not provided, you can disable the revocation checks for Java:

Once this has been disabled, you can open forms properly.

For more full-detailed Tutorials and Tips, check out #TheOracleProdigy at https://lifeofanoracleprodigy.blogspot.com/
Follow The Oracle Prodigy on Facebook (https://www.facebook.com/theOracleProdigy/) and Twitter (https://twitter.com/D_OracleProdigy)
Subscribe to The Oracle Nerd on Youtube! https://www.youtube.com/c/OracleNerd

Print XML to DBMS_OUTPUT.PUT_LINE or FND_FILE.PUT_LINE

I recently faced an issue in needing to generate a huge XML file without using the UTL_FILE package.

Since I was working on Oracle EBS and/or Fusion, I can only use FND_FILE.PUT_LINE.

FND_FILE is a seeded package within Oracle Applications (both EBS and Fusion) and prints to a specific directory in the file system. Two common usages are:

fnd_file.put_line (fnd_file.OUTPUT, p_message );

and 

fnd_file.put_line (fnd_file.LOG, p_message );

p_message has a data type of VARCHAR2. This means it's limited to 32767 bytes. So how can we print a huge XML when we're limited to 32767 characters? The Answer is Chunking.

Taken from a StackOverflow post, we will chunk the XML into pieces that FND_FILE.PUT_LINE can process.

Below is an Example:

declare
  
  xml_out xmltype;
  
  -- Internal procedure to print a CLOB using dbms_output in chunks
  procedure print_clob( p_clob in clob ) is
    v_offset number := 1;
    v_chunk_size number := 10000;
  begin
    loop
      exit when v_offset > dbms_lob.getlength(p_clob);
      dbms_output.put_line( dbms_lob.substr( p_clob, v_chunk_size, v_offset ) );
      v_offset := v_offset + v_chunk_size;
    end loop;
  end print_clob;
  
begin

        -- generate an XML --
	select  xmlAgg(xmlconcat(xmlelement("dbaObjects"
            ,   xmlelement("objName", object_name)
            ,   xmlelement("objType", object_Type)
            )))
	into	xml_out
	from    dba_objects;
	print_clob(xml_out.getClobVal);
	
end;

However, if you want to use it in Oracle Apps (EBS and/or Fusion), you can use FND_FILE.PUT_LINE instead of DBMS_OUTPUT.

	procedure print_clob( p_clob in clob ) is
		v_offset number := 1;
		v_chunk_size number := 10000;
	  begin
		loop
		  exit when v_offset > dbms_lob.getlength(p_clob);
		  --dbms_output.put_line( dbms_lob.substr( p_clob, v_chunk_size, v_offset ) );
		  fnd_file.put_line (fnd_file.OUTPUT, dbms_lob.substr( p_clob, v_chunk_size, v_offset ) );
		  
		  v_offset := v_offset + v_chunk_size;
		end loop;
	end print_clob;	  

For more full-detailed Tutorials and Tips, check out #TheOracleProdigy at https://lifeofanoracleprodigy.blogspot.com/
Follow The Oracle Prodigy on Facebook (https://www.facebook.com/theOracleProdigy/) and Twitter (https://twitter.com/D_OracleProdigy)
Subscribe to The Oracle Nerd on Youtube! https://www.youtube.com/c/OracleNerd

Using DataLoad in Oracle E-Business Suite R12

For more full-detailed Tutorials and Tips, check out #TheOracleProdigy at https://lifeofanoracleprodigy.blogspot.com/

Follow The Oracle Prodigy on Facebook (https://www.facebook.com/theOracleProdigy/) and Twitter (https://twitter.com/D_OracleProdigy)

Subscribe to The Oracle Nerd on Youtube! https://www.youtube.com/c/OracleNerd

List of Oracle E-Business Suite Tax Tables

ZX_ACCOUNT_RATES

ZX_ACCOUNTS

ZX_ACCT_TX_CLS_DEFS_ALL

ZX_API_CODE_COMBINATIONS

ZX_API_OWNER_STATUSES

ZX_API_REGISTRATIONS

ZX_COMPOUND_ERRORS

ZX_COMPOUND_ERRORS_T

ZX_CONDITION_GROUPS_B

ZX_CONDITION_GROUPS_TL

ZX_CONDITIONS

ZX_CONTENT_CHOICES_TMP

ZX_CONTENT_SOURCES

ZX_DATA_UPLOAD_DISCARD

ZX_DATA_UPLOAD_INTERFACE

ZX_DET_FACTOR_TEMPL_B

ZX_DET_FACTOR_TEMPL_DTL

ZX_DET_FACTOR_TEMPL_TL

ZX_DET_FACTORS_TL

ZX_DETAIL_TAX_LINES_GT1

ZX_DETERMINING_FACTORS_B

ZX_DIST_BKP_12345

ZX_DISTCCID_DET_FACTS_GT

ZX_DISTRIBUTION_LINES_GT

ZX_DISTS_B12345

ZX_ERRORS_GT

ZX_ERRORS_INT

ZX_EVENT_CLASS_PARAMS

ZX_EVENT_CLASSES_B

ZX_EVENT_CLASSES_TL

ZX_EVNT_CLS_MAPPINGS

ZX_EVNT_CLS_OPTIONS

ZX_EVNT_CLS_TYPS

ZX_EVNT_TYP_MAPPINGS

ZX_EXCEPTIONS

ZX_EXEMPTIONS

ZX_EXEMPTIONS_INT

ZX_FC_CODES_B

ZX_FC_CODES_CATEG_ASSOC

ZX_FC_CODES_DENORM_B

ZX_FC_CODES_TL

ZX_FC_COUNTRY_DEFAULTS

ZX_FC_TYPES_B

ZX_FC_TYPES_REG_ASSOC

ZX_FC_TYPES_TL

ZX_FORMULA_B

ZX_FORMULA_DETAILS

ZX_FORMULA_TL

ZX_ID_TCC_MAPPING_ALL

ZX_IMPORT_TAX_LINES_GT

ZX_ITM_DISTRIBUTIONS_GT

ZX_JURISDICTIONS_B

ZX_JURISDICTIONS_GT

ZX_JURISDICTIONS_TL

ZX_LINES

ZX_LINES_B12345

ZX_LINES_BKP_12345

ZX_LINES_DET_FACTORS

ZX_LINES_SUMMARY

ZX_MRC_GT

ZX_PARAM_DETAILS

ZX_PARAMETERS_B

ZX_PARAMETERS_TL

ZX_PARTY_TAX_PROFILE

ZX_PARTY_TAX_PROFILE_INT

ZX_PARTY_TYPES

ZX_PO_REC_DIST

ZX_PROCESS_RESULTS

ZX_PRODUCT_OPTIONS_ALL

ZX_PRODUCT_OPTIONS_ALL_A

ZX_PRVDR_HDR_EXTNS_GT

ZX_PRVDR_LINE_EXTNS_GT

ZX_PTNR_LOCATION_INFO_GT

ZX_PTNR_NEG_LINE_GT

ZX_PTNR_NEG_TAX_LINE_GT

ZX_PURGE_TRANSACTIONS_GT

ZX_RATES_B

ZX_RATES_TL

ZX_REC_NREC_DIST

ZX_REC_NREC_DIST_GT

ZX_RECOVERY_TYPES_B

ZX_RECOVERY_TYPES_TL

ZX_REGIME_RELATIONS

ZX_REGIMES_B

ZX_REGIMES_TL

ZX_REGIMES_USAGES

ZX_REGISTRATIONS

ZX_REGISTRATIONS_INT

ZX_REP_ACTG_EXT_T

ZX_REP_CONTEXT_T

ZX_REP_MATRIX_EXT_T

ZX_REP_TRX_DETAIL_T

ZX_REP_TRX_JX_EXT_T

ZX_REPORT_CODES_ASSOC

ZX_REPORT_CODES_ASSOC_INT

ZX_REPORT_TYPES_USAGES

ZX_REPORTING_CODES_B

ZX_REPORTING_CODES_TL

ZX_REPORTING_TYPES_B

ZX_REPORTING_TYPES_TL

ZX_REV_TRX_HEADERS_GT

ZX_REVERSE_DIST_GT

ZX_REVERSE_TRX_LINES_GT

ZX_RULES_B

ZX_RULES_TL

ZX_SERVICE_TYPES

ZX_SIM_CONDITIONS

ZX_SIM_PROCESS_RESULTS

ZX_SIM_PURGE

ZX_SIM_RULE_CONDITIONS

ZX_SIM_RULES_B

ZX_SIM_RULES_TL

ZX_SIM_TRX_DISTS

ZX_SRVC_SBSCRPTN_EXCLS

ZX_SRVC_SUBSCRIPTIONS

ZX_SRVC_TYP_PARAMS

ZX_STATUS_B

ZX_STATUS_TL

ZX_SUBSCRIPTION_DETAILS

ZX_SUBSCRIPTION_OPTIONS

ZX_SUMMARY_B12345

ZX_SUMMARY_BKP_12345

ZX_SUMMARY_TAX_LINES_GT

ZX_TAX_DIST_ID_GT

ZX_TAX_PRIORITIES_T

ZX_TAX_RELATIONS_T

ZX_TAXES_B

ZX_TAXES_TL

ZX_TEST_API_GT

ZX_TRANSACTION

ZX_TRANSACTION_LINES

ZX_TRANSACTION_LINES_GT

ZX_TRANSACTIONS_GT

ZX_TRX_HEADERS_GT

ZX_TRX_LINE_APP_REGIMES

ZX_TRX_PRE_PROC_OPTIONS_GT

ZX_TRX_TAX_LINK_GT

ZX_UPDATE_CRITERIA_RESULTS

ZX_VALDN_STATUSES_GT

ZX_VALIDATION_ERRORS_GT

Create an XML out of an XSD Schema

Step 1: Open Eclipse and Create a New XML Project

Step 2: Load the XSD in the XML Project

Step 3: Right Click on the XSD and Click on Generate > XML File

Step 4: Set the Filename and Click Next

Step 5: Select on "Create optional attributes” if you want to add the optional attributes

Step 6: Select on "Create optional elements” if you want to add the optional elements

Step 7: Click on "Finish (button)" to create the XML file

Step 8: You can now see the XML file generated from the Project Pane

Step 9: View the XML's contents in the Content Pane.

Reporting in Financial Reporting Compliance

In Financial Reporting Compliance provides a set of predefined reports organized into five categories.

Assessment Reports

• Assessment Details Reports - displays information about assessment conducted against selected objects.
• Control Assessment Report - lists controls and their related assessment activities in PDF format
• Control Assessment Extract - lists controls and their related assessment activities in Excel Format

Control Reports

• Control Details Report

Issue Reports

• Issue Details Report - provides information about selected issues, including the object against which each issue is raised, issue status and state, users who created or updated and when they did so, and other values.
• Issue Details Extract - provides similar information for export to an application such as Excel.

Risk Report

• Risk Control Matrix Report - lists risks, controls and related information: perspectives and other values
• Risk Control Matrix Extract - lists risks, controls and related information for export to Excel Format

Administration Reports

• Change history Report
• Pending Worklist Items Report
• Related Objects report
• Worklist Item Requiring Reassignment

Activating Email Alerts

Setup e-mail messaging in Financial Reporting Compliance users when tasks require their attention.

1. select the Enable check box in the E-Mail Alerts region
2. Select the test connection button to view a message that connectivity with your email server is established.
3. Create an email alert schedule

Overview of Surveys in Risk Management Cloud

What is a Survey in Risk Management Cloud?

A Survey is a set of questions that may be associated with assessments or distributed independently of assessments.

You may link a survey to an assessment activity in an assessment plan. Survey questions concern the type of object (process, risk or control) and the activity specified in the plan. Answers to the questions help assessment participants form judgments about objects in assessments developed from the plan. Assessment participants are selected automatically on the basis of their job roles.

As you prepare a survey, you may start with any of the following components:

1. Choice Sets. A choice is a possible answer to a question, and a choice set is an assortment of answers a person may select from. You can associate a given choice set with any number of questions.
2. Questions. These may or may not require choice sets. Or you can select choices are you create questions and save them into choice sets.
3. Template. As you create a template, you can select existing questions for it, or create questions. Moreover, you can use an existing template to distribute a new survey or create a new template for a survey.

Survey question formats

Survey questions may take the following formats. For any format other than open text, you can associate a question with a choice set.

Control Management in Risk Management Cloud

What is a Control?
A control defines measures to address risks. It describes actions taken automatically in other systems or manually. For example:

• Ensure segregation of duties within payroll functions
• Review changes to master data, including change owner.

Relating Controls to a Risk

As you create risks, you can relate them to controls.

• The relationship indicates that a control mitigates the risk it is related to
• you can relate any number of controls to a risk, or a control to any number of risks.

A control requires only two values:

1. A Name, which should suggest what the control does to mitigate risk. You may choose to add a description that expands upon the name.
2. A Method. Either Manual or Automatic:

• Manual Indicates the control requires human action.
• Automatic indicates the control runs in a system external to Financial Reporting Compliance.

As you create a control, you can create a test plan for it. Completed as part of a control assessment, it test whether the control is effective in alleviating the related risks.

Issue Management and Lifecycle in Risk Management Cloud

Manage the Issue Resolution Process

The resolution of an issue includes these steps:

1. A User creates an Issue
2. A User with proper privileges validates the issue, either determining that it requires investigation, closing it or putting it on hold
3. If the issue is valid, a user with proper permissions determines whether a remediation plan is required for the issue to be resolved. If not, this user closes the issue.
4. If so the user creates or selects a remediation plan. Other users respond to the worklists to complete remediation tasks. The remediation plan is marked as complete and the issue is closed.

The Issue object records defects or deficiencies detected for risks, controls or assessments. Typically, you discover issues when you assess risks or controls. Typically, one user identifies an issue, another verifies it and another resolves it.

Raising an Issue

A user may raise an issue from several places:

• From an issue-management work area
• from the issues tab in the management page for an individual risk or control, create an issue specific to that object or review its details
• within an assessment of a risk or control

Resolving an Issue

Once an issue exists, the process of resolving it may include:

1. Validating the issue
2. Take appropriate actions to resolve the issue
3. Closing an Issue

Validating the issue

When an issue is created, a user may receive a worklist notification to validate if. This user may:

• Determine that it requires investigation.
• Determine that it does not require investigation, and close it
• Put it on Hold

To Receive a validation worklist, a user must be assigned a duty role called Issue Validator Composite. In effect, this user determines whether the issue is genuine, and so should be a user other than the one who creates the issue. However, the validation workflow is optional. If no user is assigned the Issue Validator Composite duty role, no validation worklist is issued.

Users with the Issue Validator Composite, or Issue Manager Composite duty role may oversee the validation and resolution of issues, and close them.

Closing an Issue

you can close an issue:

1. when it is resolved; when points of concern have been addressed
2. At any other time. You may, for example, determine during the validation step that the issue is invalid or cannot be resolved.

For more full-detailed Tutorials and Tips, check out #TheOracleProdigy at https://lifeofanoracleprodigy.blogspot.com/

Follow The Oracle Prodigy on Facebook (https://www.facebook.com/theOracleProdigy/) and Twitter (https://twitter.com/D_OracleProdigy)

Overview of Assessments in Oracle Risk Management Cloud

What is an assessment?

An assessment is the review of a risk or a control to ensure that it is defined correctly or that its definition remains appropriate over time.

An assessment may

• focus on objects themselves, or on activities involving object, such as certification or audit
• be batch or ad-hoc
• concern a single risk or control, or encompass many.
• Involve the participation of business stakeholders, internal and external auditors, or other users.
• Incorporate test plans for controls

To determine what an assessment is meant to uncover, you assign one or more activity types to it. assessment activity types include:

The Assessment Page

• An Introduction page presents an overview of the item being assessed. It includes guidance text, which is a broad statement of the assessment's purpose.
• A "Review Prior Results" page displays records for any prior actions taken for this assessment.
• An "Enter Test Results" page enables you to complete a test plan. It appears only if you are assessing a control for which a test plan has been created.
• Use the response field to select an answer to an activity question. This determines whether the object passes or fails the assessment. You can also create a summary statement, create an issue, or attach a file to the assessment.

Batch Assessments follows the flow:

A batch assessment depends on several components

1. A template designates a primary object of assessment, Risk and Control. The template also designates one or more activities to be completed in assessments.
2. From the template, you develop a plan. It may contain filters that select instances of the primary object specified by the template. 
3. From a plan, you initiate an assessment, selecting object instances made available by the plan.

A batch assessment offers an array of options:

1. It not only involves multiple object instances, but also may designate multiple activities to be completed
2. Its generation involves the use of supporting tools: templates and plans. You use these to select assessment activities to define a set of objects for assessments.
3. You Initiate it and manage the components that support it within the Assessment work area

Initiating a Batch Assessment

1. Provide the general information
2. Review the selection criteria
3. select risks or controls to be assessed
4. review participants

The final page in the initiate assessment series identifies the assessors for each risk or control selected for assessment. These people are selected according to role assignments, and you cannot modify that selection in this page.

The purpose of this review is to identify risks or controls that have no assessors, so that you can return to the components page and remove them from the assessment

Adhoc Assessments

An adhoc assessment is simpler:

1. It not only focuses on a single object, but also designates a single activity to be completed
2. You initiate it from within the page to manage the risk or control you want to assess

Completing an Assessment

An assessment may include any number of risk or controls. You assess each of these individually. You May:

• Select a Worklist notification for a risk or control included in the assessment
• Select the "Complete Assessment" option in the tasks panel tab of any page in the Assessments work area. In a search page, search of an Assessment, select one of its risks or controls, and select "Complete Assessment".
• Navigate to the Assessments tab of the Management page for the risk or control being assessed. Select the row for an Assessment and the "Complete Assessment" action.

For more full-detailed Tutorials and Tips, check out #TheOracleProdigy at https://lifeofanoracleprodigy.blogspot.com/

Follow The Oracle Prodigy on Facebook (https://www.facebook.com/theOracleProdigy/) and Twitter (https://twitter.com/D_OracleProdigy)

Manage Role Provisioning Rules in Oracle Fusion Applications

If you have numerous job roles and want to assign them automatically to users, you can use Job-Role Mappings to automate this process.

This is basically an If-Then Condition to assign roles automatically to users.

You can run a background program to automate this process.

For more full-detailed Tutorials and Tips, check out #TheOracleProdigy at https://lifeofanoracleprodigy.blogspot.com/

Follow The Oracle Prodigy on Facebook (https://www.facebook.com/theOracleProdigy/) and Twitter (https://twitter.com/D_OracleProdigy)

Overview of the Security Console in Oracle Fusion Applications

What is the Security Console?

Use the Security Console to

1. Implement, customize and manage security
2. Create and Edit Custom roles
3. Create and Manage User Accounts

Access the Security Console through the Welcome Springboard > Navigator Menu > Tools

Functional Security Overview

Job and Duty Roles grant access to functionality:

• A Duty Role grants access to privileges required to complete a specific task, or a set of related tasks.
• A Job role combines duty roles required to grant access to a broad range of tasks.
• You can assign job roles to users. In combination, the job roles assigned to a person encompass all that he or she is hired to do.
• You cannot assign duty roles directly to users. A User is granted duty role indirectly as components of job roles.
• You work with Risk Management roles in the Security Console.

Seeded Roles have the Prefix of "ORA" in their Codes. These cannot be modified but can only be duplicated. In the below screenshot we see two entries for Accounts Payables Manager, but one of them is a seeded Role and the other is a Customized Role:

Roles can also be viewed Graphically:

Editing a Role

1. Function Security Policies
2. Data Security Policies allow a role to work with a specific Business Unit or a Ledger
3. Role Hierarchy 
4. Segregation of Duties
5. Users. Directly Assign the role to a specific User

Role Administration:

1. Set Role Preferences such as Prefix for Copied Role Name and Code
2. Copy Role Status for copied seeded Roles
3. Bridge for Active Directory

Role-Assignment Strategies

More than one mapping of duty roles to job roles may correctly grant functional access to a user.

1. You may create a single job role for a given user. For it, you would select all the duty roles the user needs.
2. You may assign multiple job roles to a given user. Each would contain a subset of the duty roles the user requires.

The second approach is recommended because it gives greater flexibility. Each job role remains available for assignment to other users in varying combinations with other job roles.

One strategy is to use Role Provisioning Rules to automatically assign roles to users based on a criteria. More information can be found in another article: Manage Role Provisioning Rules in Oracle Fusion Applications.

For more full-detailed Tutorials and Tips, check out #TheOracleProdigy at https://lifeofanoracleprodigy.blogspot.com/

Follow The Oracle Prodigy on Facebook (https://www.facebook.com/theOracleProdigy/) and Twitter (https://twitter.com/D_OracleProdigy)

Overview of Perspectives in Risk Management

What is a Perspective?

A Perspective is a set of related, hierarchically organized values. You can

1. Create other perspective hierarchies
2. Assign perspective values to processes, risks, models, controls, and incidents
3. Assign perspective values to data security policies
4. Use perspectives as a filter for searching and reporting

Perspectives are used for filtering/security and control rights. These are hierarchy of values that can either be based on your Organizational structure , Regulatory compliance, Geographic Location or Processes.

Before a perspective hierarchy is available for use, you must associate it to Module Objects. These Modules are

• Financial Reporting Compliance - Perspectives are instrumental to Financial Reporting Compliance Security
• Advanced Controls

Below is a quick demonstration of Assigning perspectives to Module Objects:

You cannot add more modules, but you can modify them according to your business needs

After assigning a perspective to a module object, you would need to run a couple of jobs. Jobs are individual requests to synchronize data, evaluate models or advanced controls, export results, generate reports, or perform other background tasks. You run a job on the page to which the job applies, but you manage it in the Monitor Jobs page. You can:

• See the current status of the job
• Manage files created by the import or export jobs
• Cancel some jobs
• Purge the Job History

A perspective filter may use an "Includes Children" condition. If so, it grants access to objects tagged with a perspective value you select for the filter, or with any of its child values.

A single perspective filter  may select more than one perspective value. If so, those values have an OR relationship. The filter grants access to objects associated with any of the values.

A data security policy may include multiple perspective filters. If so, they have an AND relationship. The policy grants access only to objects associated with values selected by all the filters.

For more full-detailed Tutorials and Tips, check out #TheOracleProdigy at https://lifeofanoracleprodigy.blogspot.com/

Follow The Oracle Prodigy on Facebook (https://www.facebook.com/theOracleProdigy/) and Twitter (https://twitter.com/D_OracleProdigy)

Financial Reporting Compliance in Risk Management Cloud

What is Financial Reporting Compliance?

Financial Reporting Compliance is, in effect, a module of Risk Management. Its objects include Risk, Control (along with a test plan, test instruction and test step), and Process (along with action item).

Financial Reporting Compliance consolidates the documentation of your business practices to satisfy financial reporting regulations. This Enterprise-scope solution enables you to:

1. Define and interrelate processes, risk, controls, assessments and issues.
2. Automate periodic reviews, approvals, test and follow through.
3. Secure what users see and do.
4. Let stakeholders get the information they need to make the best decisions.
5. Lower cost by implementing efficient, repeatable, and reliable day-to-day usage and administration.

The Financial Reporting Compliance module includes three object types: Process, Risk and Control

1. Process is the parent of Risk. As you create or edit a process, you can relate it to risks that may affect it, or create related risks.
2. Risk is the parent of Control, as you create or edit a risk, you can relate it to controls meant to address it. 
3. Controls may work together to address a given risk, and if so, other configurations values may apply to them.

Risk-Control Matrix

Financial Reporting Compliance maintains a risk-control matrix:

Every business process is subject to risks, and a company enacts controls to minimize those risks.
A risk-control matrix is an organized record of all the material risks that may affect each process and all the controls created to address those risks.

Predefined Job Roles for Financial Reporting Compliance:

1. Enterprise Risk and Control Manager 
2. Compliance Manager 

Both of these are Superuser roles providing functional and setup access to anything a person can do in Financial Reporting Compliance.

Best Practice Financial Reporting Compliance

The Best Practice Solution is a prescriptive set of steps for deploying key elements of Financial Reporting Compliance with maximum Speed and efficiency, and with minimum cost and upkeep.

Best Practice Solution Steps

1. Gather Configuration Data

• Retrieve existing risk and control definitions from spreadsheets, email-records, file-sharing system, and any other Repositories.
• Collect related data, such as documentation needed to support risks and controls.
• Consider who is to work with risks and controls and the roles they are to fill.

1. Prepare and Import Data

• Use the data migration utility to import this data into Financial Reporting Compliance

1. Configure Roles and Users

• Use Oracle Identity Manager and Authorization Policy Manager to define risk management roles and assign them to users. You can create job roles from predefined duty roles.

Use risk-management and control-management work ares to create new risks and controls, or modify existing ones.
Optionally, use Risk Management workflow to route risks and controls to reviewers and approvers
Regularly assess risk and controls to ensure their continued viability.

For more full-detailed Tutorials and Tips, check out #TheOracleProdigy at https://lifeofanoracleprodigy.blogspot.com/

Follow The Oracle Prodigy on Facebook (https://www.facebook.com/theOracleProdigy/) and Twitter (https://twitter.com/D_OracleProdigy)