How to run APIs using standalone anonymous block

In this illustration we would try and delete an existing element entry using oracle's seeded API

Pay Period: May 22^nd – Jun 04^th 2017

Element to be deleted: EA037 Public Holiday

Oracle SQL Statement Types and Usage - Part III

Data Control Language (DCL) 

The Data Control Language (DCL) is a subset of the Structured Query Lanaguge (SQL) that allows database administrators to configure security access to relational databases.Two types of DCL commands are GRANT and REVOKE. Only Database Administrator’s or owner’s of the database object can provide/remove privileges on a database object.

GRANT:
Gives user’s access privileges to database.

Oracle SQL Statement Types and Usage - Part II

Data Retrieval or Query Language (DQL or DRL) :

SELECT clause will come under this category. Main usage of select to identify the result set column list.

Oracle SQL Statement Types and Usage - Part I

Let me introduce SQL by statements type, here statements defines set of commands. Generally statements can fall under the following category

• Data definition Language (DDL)
• Data Retrieval or Query Language (DQL or DRL)
• Data Manipulation Language (DML)
• Data Control Language (DCL)
• Transaction Control Language (TCL)

HR and PAYROLL Interview Preparation - Part IV

1. What is the Process to Reverse a Termination of employee?

You reverse a termination in order to make an employee active again.

Navigation: HR Responsibility >> Enter & Maintain >> Others >> End Employment

Search for the separated employee record  and click on ‘Reverse Termination’ button to undo the termination of an employee. As a result the person will become active employee again.

Date / Time related queries

Get the first day of the month

Quickly returns the first day of current month. Instead of current month you want to find first day of month where a date falls, replace SYSDATE with any date column/value

SELECT TRUNC (SYSDATE, 'MONTH') "First day of current month" FROM DUAL

SQL Query - Form Personalisation

Below is the list of tables that are populated when any Forms Personalisation is done

• FND_FORM_CUSTOM_RULES
• FND_FORM_CUSTOM_SCOPES
• FND_FORM_CUSTOM_ACTIONS
• FND_FORM_CUSTOM_PARAMS
• FND_FORM_CUSTOM_PROP_VALUES
• FND_FORM_CUSTOM_PROP_LIST

Table Joins in SQL – Part III

Moving on, let us see the different types of Oracle joins - 

• Oracle INNER JOIN (or sometimes called simple join)
• Oracle LEFT OUTER JOIN (or sometimes called LEFT JOIN)
• Oracle RIGHT OUTER JOIN (or sometimes called RIGHT JOIN)
• Oracle FULL OUTER JOIN (or sometimes called FULL JOIN)

Table Joins in SQL – Part II

How Many Comparisons Do You Need?

When using Oracle syntax for table joins, a query on data from more than two tables must contain the right number of equality operations to avoid a Cartesian product. To avoid confusion, use this simple rule: If the number of tables to be joined equals N, include at least N-1 equality conditions in the select statement so that each common column is referenced at least once.

SQL Query - Accounting Flexfield Details

The below query will fetch the details of accounting flexfield structure:

select sob.name Ledger_Name
, sob.ledger_id Ledger_Id
, sob.chart_of_accounts_id coa_id
, fifst.id_flex_structure_name struct_name

SQL Query - Form Personalisation Details

The below SQL query will fetch details of form level personalisations :

SELECT   ffcr.function_name
       , ffcr.form_name
       , fft.user_form_name
       , fat.application_name application
       , ffcr.sequence seq
       , ffcr.description

SQL Query - Display Database Version and Server Operating System Name

The below sql query can be used to get database and other details

SELECT banner FROM v$version

SQL Query - List of users with access to a specific responsibility

The below sql query can be used to get the list of users who have access to a specific given responsibility

SELECT
  fu.user_name,
  fu.description,
  frt.responsibility_name,