SQL Query to get payslip of an employee

below is a useful SQL statement to retrieve a payslip of a certain employee in a certain calendar month.
just replace the calendar name and the emp number with yours and run it.


SELECT   ppa.date_earned, per.full_name, per.employee_number,
         NVL (pet.reporting_name, pet.element_name), piv.NAME,
         prrv.result_value, ptp.period_name

Query : Get Bank Information from HZ Tables

select * from 
hz_parties bankparty, hz_code_assignments bankca
where 
bankparty.party_type = 'ORGANIZATION'

Query : R12 Link ap_invoice_distributions_all to a GL Header via SLA

SELECT c.code_combination_id, h.je_header_id, l.ae_header_id, l.ae_line_num, te.source_id_int_1, te.application_id, te.entity_id,
h.je_source,
h.je_category,
i.gl_date,
s.vendor_name,

Query : Drilldown from GL to Receiving Transactions

SELECT b.NAME je_batch_name,
b.description je_batch_description,
b.running_total_accounted_dr je_batch_total_dr,
b.running_total_accounted_cr je_batch_total_cr,
b.status je_batch_status,

Query : To get Menus Associated with responsibility

SELECT DISTINCT a.responsibility_name, c.user_menu_name 
FROM apps.fnd_responsibility_tl a, 
apps.fnd_responsibility b, 
apps.fnd_menus_tl c, 
apps.fnd_menus d, 

Query : to find the process in Workflow using a known function name or Activity name

select 
wat1.display_name "process name",wat.display_name "activity name",wa.function "function" 
from wf_process_activities wpa
,wf_activities_tl wat
,wf_activities_tl wat1
,wf_activities wa

Convert BLOB to CLOB

This Function is used to convert BLOB files (Images) to CLOB.

This can be used to display Images in the XML Publisher Reports as you cannot directly display the images stored in BLOB format in XML Publisher.

/* ---------------------------------

    TO GET IMAGES USING FUNCTION:

----------------------------------- */

   FUNCTION getbase64 (p_source BLOB)

      RETURN CLOB

   IS

Function to convert Number to Arabic Number

FUNCTION convert_to_arabic (p_input IN VARCHAR2, p_msg OUT VARCHAR2)

    RETURN VARCHAR2

    IS

    --

    l_output VARCHAR2(20) DEFAULT NULL;

Functions for Gregorian to Hijrah and Hijrah to Gregorian

HIJRAH TO GREGORIAN
--------------------------------------
Syntax: 
to_date(hr_sa_hijrah_functions.hijrah_to_gregorian(<VALUE>), 'YYYY/MM/DD')

eg:
SELECT to_date(hr_sa_hijrah_functions.hijrah_to_gregorian('1433/08/30'), 'YYYY/MM/DD')Greg_date FROM DUAL;

Function for converting Number to Words

Syntax:
--------- 
TO_CHAR (TO_DATE (<VALUE>, 'j'), 'jsp')

eg: SELECT (TO_CHAR(TO_DATE (4566,'j'),'jsp')) num_to_words FROM DUAL;

Query : to find the process in Workflow using a known function name or Activity name

select 
wat1.display_name "process name",wat.display_name "activity name",wa.function "function" 
from wf_process_activities wpa
,wf_activities_tl wat
,wf_activities_tl wat1
,wf_activities wa

Query : Verify that Multi Org is Installed

select multi_org_flag,release_name,
applications_system_name
from apps.fnd_product_groups;

Query : Check responsibility assigned to a specific USER

SELECT UNIQUE u.user_id, SUBSTR (u.user_name, 1, 30) user_name, 
SUBSTR (r.responsibility_name, 1, 60) responsiblity, 
SUBSTR (a.application_name, 1, 50) application 
FROM fnd_user u, 
fnd_user_resp_groups g, 

Query : To get all request with application

SELECT fa.application_short_name, fcpv.user_concurrent_program_name, 
description, 
DECODE (fcpv.execution_method_code, 
'B', 'Request Set Stage Function', 
'Q', 'SQL*Plus', 

Query : To get assigned responsibility to a user

SELECT UNIQUE u.user_id, SUBSTR (u.user_name, 1, 30) user_name, 
SUBSTR (r.responsibility_name, 1, 60) responsiblity, 
SUBSTR (a.application_name, 1, 50) application 
FROM fnd_user u, 
fnd_user_resp_groups g, 

Query: To find Oracle API's for any module

select substr(a.OWNER,1,20)
, substr(a.NAME,1,30)
, substr(a.TYPE,1,20) 
, substr(u.status,1,10) Stat

Query: To find Patch Level for a product searching with application Name

Query to find Patch Level for a product searching with application Name(Product)/application (Product) short name

select fav.application_name,
fav.application_id,
fav.application_short_name,
fpi.status,fpi.patch_level 

QUERY: Which Oracle Apps Version i am using

SELECT substr(a.application_short_name, 1, 5) code,
substr(t.application_name, 1, 50) application_name,
p.product_version version
FROM fnd_application a,

Query to List all the responsibilities attached to a User

select fu.user_name, 
fr.responsibility_name, 
furg.START_DATE, 
furg.END_DATE

Query : To get the list of Custom Alerts

SELECT aa.alert_name, 
aa.creation_date, 
aa.last_update_date,
aa.alert_condition_type,