Query for getting SIT Details for the Employees

SELECT
papf.employee_number,
papf.full_name,
pac.segment1,
pac.segment2,
pac.segment3,
pac.segment4,
pac.segment5
FROM
per_all_people_f papf,
fnd_id_flex_structures_tl fifs,
per_analysis_criteria pac,
per_person_analyses ppa
WHERE
papf.business_group_id                 = P_BUSINESS_GROUP_ID
AND TRUNC(SYSDATE) BETWEEN papf.effective_start_date AND papf.effective_end_date
AND papf.current_employee_flag         = 'Y'
AND UPPER(fifs.id_flex_structure_name) = UPPER('Passport')
AND fifs.id_flex_num = pac.id_flex_num
AND ppa.id_flex_num = fifs.id_flex_num
AND ppa.analysis_criteria_id = pac.analysis_criteria_id
AND ppa.person_id = papf.person_id;

Query to get the Competence Details for the PERFORMANCE level

SELECT
pc.name,
prl.step_value
FROM
per_appraisal_templates     pat,
per_assessments    pass,
per_competence_elements    pce,
per_competence_elements    pce1,
per_competences    pc,
per_rating_levels    prl
WHERE
pat.appraisal_template_id = <P_APPRAISAL_TEMPLATE_ID>
AND pat.assessment_type_id = pass.assessment_type_id
AND pass.appraisal_id = <P_APPRAISAL_ID>                        
AND pce.assessment_type_id = pat.assessment_type_id
AND UPPER(pce.type) = UPPER('ASSESSMENT_GROUP')
AND pce1.parent_competence_element_id = pce.competence_element_id
AND pce1.assessment_id = pass.assessment_id          
AND UPPER(pce1.type) = UPPER('ASSESSMENT')
AND pc.competence_id = pce1.competence_id            
AND pce1.rating_level_id = prl.rating_level_id; 

Query for getting the Competences attached to an Appraisal:

SELECT
pc.name,
prl.step_value
FROM
per_appraisal_templates     pat,
per_assessments    pass,
per_competence_elements    pce,
per_competence_elements    pce1,
per_competences    pc,
per_rating_levels    prl
WHERE
pat.appraisal_template_id = <P_APPRAISAL_TEMPLATE_ID>
AND pat.assessment_type_id = pass.assessment_type_id
AND pass.appraisal_id = <P_APPRAISAL_ID>                        
AND pce.assessment_type_id = pat.assessment_type_id
AND UPPER(pce.type) = UPPER('ASSESSMENT_GROUP')
AND pce1.parent_competence_element_id = pce.competence_element_id
AND pce1.assessment_id = pass.assessment_id
AND UPPER(pce1.type) = UPPER('ASSESSMENT')
AND pc.competence_id = pce1.competence_id            
AND pce1.proficiency_level_id = prl.rating_level_id; 

Query for getting the Competences attached to an Assessment Template

SELECT
pce.group_competence_type,
pc.name,
pce1.attribute1
FROM
per_competence_elements     pce,
per_competence_elements     pce1,
per_competences             pc
WHERE
pce.assessment_type_id = <P_ASSESSMENT_TYPE_ID>
AND UPPER(pce.type) = UPPER('ASSESSMENT_GROUP')
AND pce1.parent_competence_element_id = pce.competence_element_id
AND UPPER(pce1.type) = UPPER('ASSESSMENT_COMPETENCE')
and pce1.competence_id = pc.competence_id;

Query for getting all the Flex Values for a given Flex Structure/Segment Name:

SELECT
ffvv.flex_value
FROM
fnd_id_flex_structures fifs,
fnd_id_flex_segments fifs1,
fnd_flex_values_vl ffvv
WHERE
UPPER(fifs.id_flex_structure_code) = UPPER('UK_JOBS')
AND fifs.id_flex_num = fifs1.id_flex_num
AND UPPER(fifs1.segment_name) = UPPER('Job Name')
AND fifs1.flex_value_set_id = ffvv.flex_value_set_id;

The following Query is for getting all the Flex Values for a given Flex
Value Set:

SELECT
ffvv.flex_value
FROM  
fnd_flex_values_vl ffvv,
fnd_flex_value_sets ffvs  
WHERE
ffvv.flex_value_set_id    = ffvs.flex_value_set_id
AND UPPER(ffvs.flex_value_set_name) = UPPER('UK_JOB_NAME');

Query for getting Applicant Details.

SELECT
papf.person_id,
papf.applicant_number,
papf.full_name,
papf.date_of_birth,
papf.nationality,
papf.marital_status,
paaf.organization_id,
paaf.location_id,
paaf.supervisor_id,
pe.type,
pe.location_id,
pe.date_start
FROM
per_all_people_f papf,
per_all_assignments_f paaf,
per_events pe
WHERE
papf.business_group_id = P_BUSINESS_GROUP_ID
AND papf.current_applicant_flag = 'Y'
AND TRUNC(SYSDATE) BETWEEN papf.effective_start_date AND papf.effective_end_date
AND TRUNC(SYSDATE) BETWEEN paaf.effective_start_date AND paaf.effective_end_date
AND papf.person_id = paaf.person_id
AND paaf.primary_flag = 'Y'
AND pe.assignment_id = paaf.assignment_id;

Query to lists the Grades, their present and next Grade Step, and their corresponding values.

SELECT   DISTINCT
         papf.employee_number    staffno,
         pgrf.value              basic,
         pgrf1.value             basic1,
         pg.name                 grade,
         psp.spinal_point        grade_step,
         psp1.spinal_point       grade_step1
FROM  
per_all_people_f                papf,
per_all_assignments_f paaf,
         per_grades                      pg,
         per_spinal_point_placements_f   psppf,
         per_spinal_point_steps_f        pspsf,
         per_spinal_point_steps_f        pspsf1,
         per_spinal_points               psp,
         per_spinal_points               psp1,
         per_grade_spines_f              pgsf,
         pay_grade_rules_f               pgrf,
         pay_grade_rules_f               pgrf1
WHERE
papf.business_group_id =  P_BUSINESS_GROUP_ID
AND papf.current_employee_flag = 'Y'
         AND TRUNC(SYSDATE)  BETWEEN papf.effective_start_date AND papf.effective_end_date
         AND papf.person_id = paaf.person_id
         AND TRUNC(SYSDATE)  BETWEEN paaf.effective_start_date AND paaf.effective_end_date
AND paaf.primary_flag = 'Y'
         AND paaf.grade_id = pg.grade_id
         AND paaf.assignment_id = psppf.assignment_id
         AND TRUNC(SYSDATE)  BETWEEN psppf.effective_start_date AND psppf.effective_end_date
         AND psppf.step_id = pspsf.step_id
         AND pspsf.spinal_point_id = psp.spinal_point_id
         AND pspsf1.spinal_point_id = psp1.spinal_point_id
         AND TRUNC(SYSDATE)   BETWEEN pspsf.effective_start_date AND pspsf.effective_end_date
         AND psp.parent_spine_id = pgsf.parent_spine_id
         AND psp1.parent_spine_id = pgsf.parent_spine_id
         AND TRUNC(SYSDATE)  BETWEEN pgsf.effective_start_date AND pgsf.effective_end_date
         AND psp.spinal_point_id = pgrf.grade_or_spinal_point_id
         AND psp1.spinal_point_id = pgrf1.grade_or_spinal_point_id
         AND (psppf.step_id !=  NVL(paaf.special_ceiling_step_id,pgsf.ceiling_step_id)
         AND psppf.auto_increment_flag = 'Y'              
AND (psp1.sequence = (SELECT
MIN(psp2.sequence)
 FROM
per_spinal_points           psp2,
per_spinal_point_steps_f    pspsf2
 WHERE
pspsf.grade_spine_id    = pspsf.grade_spine_id
AND   pspsf2.spinal_point_id  = psp2.spinal_point_id
AND   TRUNC(SYSDATE)  BETWEEN pspsf2.effective_start_date AND pspsf2.effective_end_date
AND   psp2.sequence > psp.sequence))
         OR  psppf.auto_increment_flag = 'N'
         AND psp1.sequence = psp.sequence
         OR  psppf.step_id = NVL(paaf.special_ceiling_step_id,pgsf.ceiling_step_id)
         AND psp1.sequence = psp.sequence)
         AND TRUNC(SYSDATE) BETWEEN pgrf1.effective_start_date AND pgrf1.effective_end_date;

Query for getting Pay Scale Details for the Employees.

SELECT
papf.employee_number,
pg.name,
hrfg.grade_step,
pgrf.value
FROM
per_all_people_f papf,
per_all_assignments_f paaf,
pay_grade_rules_f pgrf,
        per_spinal_point_steps_f pgspsf,
        per_spinal_points psp,
        hrfg_grade_steps hrfg,
        per_grade_spines_f pgsf,
        per_grades pg
WHERE
papf.business_group_id = P_BUSINESS_GROUP_ID
AND papf.current_employee_flag = 'Y'
AND TRUNC(SYSDATE) BETWEEN papf.effective_start_date AND papf.effective_end_date
AND papf.person_id = paaf.person_id
AND TRUNC(SYSDATE) BETWEEN paaf.effective_start_date AND paaf.effective_end_date
AND paaf.primary_flag = 'Y'
AND pg.name = paaf.grade_id
        AND hrfg.grade_id = pg.grade_id
        AND hrfg.grade_step_id = pgspsf.step_id
        AND TRUNC(SYSDATE) BETWEEN pgspsf.effective_start_date AND pgspsf.effective_end_date
        AND psp.spinal_point_id = pgspsf.grade_spine_id
        AND pgsf.grade_spine_id = pgspsf.grade_spine_id
        AND hrfg.grade_id = pgsf.grade_id
        AND TRUNC(SYSDATE) BETWEEN pgsf.effective_start_date AND pgsf.effective_end_date
        AND pgspsf.spinal_point_id = pgrf.grade_or_spinal_point_id
        AND TRUNC(SYSDATE) BETWEEN pgrf.effective_start_date AND pgrf.effective_end_date;

Query for getting Address Details for the Employees.

SELECT
papf.employee_number,
pa.style,
pa.address_line1,
pa.address_line2,
pa.address_line3,
pa.addresss_type,
pa.country,
pa.postal_code,
pa.telephone_number_1,
pa.town_or_city
FROM
per_all_people_f papf,
per_addresses pa
WHERE
papf.business_group_id = P_BUSINESS_GROUP_ID
AND papf.current_employee_flag = 'Y'
AND TRUNC(SYSDATE) BETWEEN papf.effective_start_date AND papf.effective_end_date
AND papf.person_id = pa.person_id;

Query for getting Phone Details for the Employees.

SELECT
papf.employee_number,
pp.phone_type,
pp.phone_number
FROM
per_all_people_f papf,
per_phones pp
WHERE
papf.business_group_id = P_BUSINESS_GROUP_ID
AND papf.current_employee_flag = 'Y'
AND TRUNC(SYSDATE) BETWEEN papf.effective_start_date AND papf.effective_end_date
AND papf.person_id = pp.parent_id;

Query for getting Employee`s Contact Details.

SELECT
papf.employee_number,  
papf.full_name,
papf1.full_name,
ppt.user_person_type,
fcl.meaning
FROM
per_all_people_f papf,
per_contact_relationships pcr,
per_all_people_f papf1,
fnd_common_lookups fcl,
per_person_type_usages_f ppu,
per_person_types ppt
WHERE
papf.business_group_id = P_BUSINESS_GROUP_ID
AND papf.current_employee_flag = 'Y'
AND TRUNC(SYSDATE) BETWEEN papf.effective_start_date AND papf.effective_end_date
AND pcr.person_id = papf.person_id
AND TRUNC(SYSDATE) BETWEEN papf1.effective_start_date AND papf1.effective_end_date
AND papf1.person_id = pcr.contact_person_id
AND UPPER(fcl.lookup_type) = UPPER('CONTACT')
AND fcl.lookup_code = pcr.contact_type
AND papf1.person_id = ppu.person_id
AND ppu.person_type_id = ppt.person_type_id
AND TRUNC(SYSDATE) BETWEEN ppu.effective_start_date AND ppu.effective_end_date;

Query/Function for writing Amount into Words.

DECLARE
a number(15);
ans varchar2(1000);
BEGIN
a := &temp;
SELECT
DECODE(SIGN(LENGTH(a)-9),
1,TO_CHAR(TO_DATE(TO_NUMBER(SUBSTR(TO_CHAR(a),-LENGTH(a),LENGTH(a)-9)),'J'),'Jsp') || ' Billion '
||DECODE(SIGN(LENGTH(a)-6),
1,TO_CHAR(TO_DATE(TO_NUMBER(SUBSTR(TO_CHAR(a),LENGTH(a)-8,3)),'J'),'Jsp') || ' Million '
||TO_CHAR(TO_DATE(TO_NUMBER(SUBSTR(TO_CHAR(a),-6,6)),'J'),'Jsp'),
TO_CHAR(TO_DATE(a,'J'),'Jsp')),
DECODE(SIGN(LENGTH(a)-6),
1,TO_CHAR(TO_DATE(TO_NUMBER(SUBSTR(TO_CHAR(a),-LENGTH(a),LENGTH(a)-6)),'J'),'Jsp') || ' Million '
||TO_CHAR(TO_DATE(TO_NUMBER(SUBSTR(TO_CHAR(a),-6,6)),'J'),'Jsp'),
TO_CHAR(TO_DATE(a,'J'),'Jsp')))

|| ' Dollars '
INTO ans
FROM DUAL;
DBMS_OUTPUT.PUT_LINE(INITCAP(ans));
END;
/

DECLARE
a number(15);
ans varchar2(1000);
BEGIN
a := &temp;

SELECT
DECODE(SIGN(LENGTH(a)-8),
1,TO_CHAR(TO_DATE(TO_NUMBER(SUBSTR(TO_CHAR(a),-LENGTH(a),LENGTH(a)-7)),'J'),'JSP')||' Crore ',
0,TO_CHAR(TO_DATE(TO_NUMBER(SUBSTR(TO_CHAR(a),-8,1)),'J'),'JSP')||' Crore ')
||
DECODE(SIGN(LENGTH(a)-6),
1,TO_CHAR(TO_DATE(TO_NUMBER(SUBSTR(TO_CHAR(a),-7,2)),'J'),'JSP')||' Lakh ',
0,TO_CHAR(TO_DATE(TO_NUMBER(SUBSTR(TO_CHAR(a),-6,1)),'J'),'JSP')||' Lakh ')
||
DECODE(SIGN(LENGTH(a)-4),
1,TO_CHAR(TO_DATE(TO_NUMBER(SUBSTR(TO_CHAR(a),-5,2)),'J'),'JSP')||' Thousand ',
0,TO_CHAR(TO_DATE(TO_NUMBER(SUBSTR(TO_CHAR(a),-4,1)),'J'),'JSP')||' Thousand ')
||
DECODE(SIGN(LENGTH(a)-2),
1,DECODE(SUBSTR(TO_CHAR(a),-3,3),'000',NULL,
TO_CHAR(TO_DATE(TO_NUMBER(SUBSTR(TO_CHAR(a),-3,3)),'J'),'JSP')),
0,TO_CHAR(TO_DATE(a,'J'),'JSP'),-1,TO_CHAR(TO_DATE(a,'J'),'JSP'))
|| ' Rupees'
INTO
ans
FROM
DUAL;

DBMS_OUTPUT.PUT_LINE(INITCAP(ans));
END;
/

Query for getting Employee Personal and Assignment Details.

SELECT
papf.person_id Person_Id,
papf.employee_number Employee_Number,
papf.full_name Full_Name,
DECODE(papf.sex,'M','Male','F','Female') Sex,
fcl1.meaning Marital_Status,
fcl2.meaning Nationality,
papf.date_of_birth Date_Of_Birth,
haou.name Organization,
pj.name Job_Name,
pg.name Grade_Name,
pp.name Position_Name,
hla.location_code Location_Name
   FROM  
per_all_people_f papf,
per_periods_of_service ppos,
fnd_common_lookups fcl1,
fnd_common_lookups fcl2,
per_all_assignments_f paaf,
hr_all_organization_units haou,
per_jobs pj,
per_grades pg,
per_positions pp,
hr_locations_all hla
   WHERE
papf.business_group_id = P_Business_Group_Id
AND papf.current_employee_flag = 'Y'
AND TRUNC(SYSDATE) BETWEEN papf.effective_start_date AND papf.effective_end_date
AND papf.person_id = ppos.person_id
AND papf.marital_status = fcl1.lookup_code
AND UPPER(fcl1.lookup_type) = UPPER('MAR_STATUS')
AND fcl1.enabled_flag = 'Y'
AND papf.nationality = fcl2.lookup_code
AND UPPER(fcl2.lookup_type) = UPPER('NATIONALITY')
AND fcl2.enabled_flag = 'Y'
AND papf.person_id = paaf.person_id
AND TRUNC(SYSDATE) BETWEEN paaf.effective_start_date AND paaf.effective_end_date
AND paaf.primary_flag = 'Y'
AND paaf.organization_id = haou.organization_id
AND paaf.job_id = pj.job_id (+)
AND paaf.grade_id = pg.grade_id (+)
AND paaf.position_id = pp.position_id (+)
AND paaf.location_id = hla.location_id (+);