Copy Data With Columns Names In Toad

If you are a newbie in Toad For Oracle world, like me, then, here is a great tip for you. To copy a result from data grid to an Excel sheet along with column names, simply use CTRL + INSERT

You might have struggled with how to do that! Toad does not show you that option when you right-click! not even under Edit menu! 

Run a SQL Script File From SQL*Plus

Ok, this is another simple post. It is about SQL*Plus this time , particularly about how to execute a SQL script file from your ORACLE database server command prompt.

We have this simple Hello World SQL code. 

SET serveroutput ON;

DECLARE

   message  varchar2(20):= 'Hello, World!';

BEGIN

   dbms_output.put_line(message);

END;

/

Please pay attention to the first line:

SET serveroutput ON;

and ,also , don't forget the slash '/' at the end of the file. You will not see the desired output on SQL*Plus if you miss any of those ,although the script will work perfectly on a GUI tool like SQL Developer or Toad without them.
The first line is basically to turn output messages on. The slash "/" is mandatory to tell SQL*Plus that this is the end of the file. 

In this example, I used a Solaris 11 test machine with ORACLE 11g database server. I uploaded the SQL script file to my server and gave it the name hello.sql

Here you can see the file in path:
/ora-dev/devdb/home/oradev

Then I navigated to that path before launching SQL*plus.

And here I logged in to SQL*Plus as SYSDBA

Finally, I executed the SQL file by simply typing the file name preceded by '@' symbol



That's all

Getting Payroll Summary Information For A Specific Period

This query gives the payroll summary for a specific period. I got it from some blog but I do not remember it now.

SELECT 

ppf.employee_number,

ppf.full_name,

(ppa.EFFECTIVE_DATE)Payroll_Run_Date,

TP.PERIOD_NAME,

sum(decode(pec.CLASSIFICATION_NAME,'Earnings',to_number(rrv.result_value),0)) Earnings,

sum(decode(pec.CLASSIFICATION_NAME,'Involuntary Deductions',to_number(rrv.result_value),0)) Involuntary_Deductions,

sum(decode(pec.CLASSIFICATION_NAME,'Voluntary Deductions',to_number(rrv.result_value),0)) Voluntary_Deductions,

sum(decode(pec.CLASSIFICATION_NAME,'Statutory Deductions',to_number(rrv.result_value),0)) Statutory_Deductions

FROM per_people_x ppf,

per_assignments_x paf,

pay_assignment_actions pas,

pay_payroll_actions ppa,

pay_run_results rr,

pay_run_result_values rrv,

pay_element_types_f ety,

pay_input_values_F I,

PER_TIME_PERIODS TP,

PAY_ELEMENT_CLASSIFICATIONS_VL pec

WHERE ppf.person_id = paf.person_id

AND paf.assignment_id = pas.assignment_id

AND pas.assignment_action_id = rr.assignment_action_id

AND ppa.payroll_action_id = pas.payroll_action_id

AND rr.element_type_id = ety.element_type_id

AND i.element_type_id = ety.element_type_id

AND rrv.run_result_id = rr.run_result_id

AND rrv.input_value_id = i.input_value_id

and  TP.TIME_PERIOD_ID = PPA.TIME_PERIOD_ID

and ety.CLASSIFICATION_ID=pec.CLASSIFICATION_ID

AND i.name = 'Pay Value'

and ppa.EFFECTIVE_DATE  BETWEEN :p_st_effect_date  AND  :p_end_effect_date

and ppf.employee_number  = nvl(:p_emp_number,ppf.employee_number)

group by ppf.employee_number,

ppf.person_id,

ppf.full_name,

ppa.TIME_PERIOD_ID,

ppa.EFFECTIVE_DATE,

TP.PERIOD_NAME,

paf.ORGANIZATION_ID

order by ppf.employee_number

Query To Get Absence Entitlements For All Employees

This query will give Absence entitlements for active all employees

I used CUST_GET_NET_ENTITLEMENT function from this blog

select 

papf.employee_number,

CUST_GET_NET_ENTITLEMENT(papf.person_id,61,'31-Dec-2016') as Annual_Leave_Entitlement from PER_PERSON_TYPE_USAGES_F pptu,per_person_types ppt,per_all_people_f papf 

where 1=1

and

ppt.person_type_id = pptu.person_type_id

and

papf.person_id = pptu.person_id

and

ppt.USER_PERSON_TYPE = 'Employee'

and

sysdate between pptu.effective_start_date 

    and pptu.effective_end_date

and

sysdate between papf.effective_start_date 

    and papf.effective_end_date