Salary Sum Query

Sum of all earnings in payroll:

/* Formatted on 2011/10/27 13:36 (Formatter Plus v4.8.8) */

SELECT pee.assignment_id, pee.element_entry_id, pee.element_link_id,
pel.element_type_id, pet.element_name, pettl.reporting_name,
DECODE (pet.element_name,
'Basic_New', 'Basic Salary',
'HRA', 'House Rent Allowance',
pet.element_name
) display_name,
(SELECT MAX (screen_entry_value)
FROM pay_element_entry_values_f
WHERE element_entry_id = pee.element_entry_id) screen_entry_value
FROM pay_element_entries_f pee,
pay_element_links_f pel,
pay_element_types_f pet,
pay_element_types_f_tl pettl,
pay_element_classifications pec
WHERE pee.assignment_id = :assignment_id
AND TRUNC (SYSDATE) BETWEEN pee.effective_start_date
AND pee.effective_end_date
AND pee.element_link_id = pel.element_link_id
AND TRUNC (SYSDATE) BETWEEN pel.effective_start_date
AND pel.effective_end_date
AND pel.element_type_id = pet.element_type_id
AND TRUNC (SYSDATE) BETWEEN pet.effective_start_date
AND pet.effective_end_date
AND pet.processing_type = 'R'
AND pet.element_type_id = pettl.element_type_id
AND TRUNC (SYSDATE) BETWEEN peev.effective_start_date
AND peev.effective_end_date*/
AND pet.classification_id = pec.classification_id
AND pec.classification_name = 'Earnings'
ORDER BY DECODE (pet.element_name, 'Basic_New', 1, 'HRA', 2, 3)

FND_CANONICAL

In HRMS we do use date with timestamp for EIT ans SIT segments.

To change the format od the date we can use fnd_date.canonical_to_date default function:

e.g:

before using fnd_date function

SELECT pei_information2 Visit_date from per_people_extra_info where pei_attribute_category = '02' and person_id=1101

After using fnd_date function:

SELECT fnd_date.canonical_to_date(pei_information2) from per_people_extra_info where pei_attribute_category = '02' and person_id=1101

 And you can use to_chat over uit to make it in words as

SELECT TO_CHAR(fnd_date.canonical_to_date(pei_information2),'Month ddTH, YYYY') from per_people_extra_info where pei_attribute_category = '02' and person_id=1101

 

Kill a session

To kill a session use:

ALTER SYSTEM KILL SESSION 'sid,serial#,@inst_id';



To kill a particular session use

ALTER SYSTEM KILL SESSION '7,15';(SID ID)

Index

Indexing...


why we should go for indexing?


When we are using more then two tables with 'n' number of check with DB.
We can create index for the specific column name to be referenced.


e.g:


create

So now when i run the query in my package it will refer to the index in the ratio 1:1:1:1

i.e everything join conditionally.

This will reduce the time consumption also. index ix_index on per_all_people_f(person_id,effective_end_date,person_type_id,national_identifier);

SQL Tunning

• 
  
  Tuning is a search for lost time
• 
  
  You need to identify where you are losing time and why. Then you can do something about it.
• 
  
  It nearly always comes down to a poorly performing SQL statement. The question is which statement and why.
•  An execution plan is a list of steps that Oracle will follow in order to execute a SQL statement. Each step is one of a finite number of basic operations known to the database server. Even the most complex SQL statement can be broken down into a series of basic operations.
• EXPLAIN PLAN is a statement that allows you to have Oracle generate the execution plan for any SQL statement without actually executing it. You will be able to examine the execution plan by querying the plan table.
• A plan table holds execution plans generated by the EXPLAIN PLAN statement.
• The typical name for a plan table is plan_table, but you may use any name you wish.
• Create the plan table by running utlxplan.sql, located in $ORACLE_HOME/rdbms/admin.

EXPLAIN PLAN


[SET STATEMENT_ID = ]

[INTO ]

FOR

;


******************************************
The autotrace feature in SQL*Plus


SET AUTOTRACE OFF ON TRACEONLY [EXPLAIN] [STATISTICS]


At the instance level:


sql_trace = true

timed_statistics = true (optional)

In your own session:

ALTER SESSION SET sql_trace = TRUE;

ALTER SESSION SET timed_statistics = TRUE; (optional)

In another session:

SYS.dbms_system.set_sql_trace_in_session

(, , TRUE)

Invoke TKPROF from the operating system prompt like this:




tkprof

\

[explain=] \

[sys=n] [insert=] \

[record=] [sort=]
 
  Examples:


1. tkprof ora_11598.trc /tmp/myfilename sys=no

2. tkprof ora_11598.trc /tmp/myfilename explain=ap/ap

3. tkprof ora_23532.trc myfilename sort=execpu (if timed_statistics=true)

How to get the values of Bind Variables:


Dump the event 10046 using,

In Init.ora set,

event = ‘10046 trace name context forever, level 4’;

OR

SQL> oradebug setospid (taken from V$PROCESS)

OR

SQL> oradebug event 10046 trace name context forever, level 4

TKProf does not know what data type bind variables. It assumes VARCHAR2. This may cause EXPLAIN PLAN to appear as though an index is not being used when in fact it is.