View Org Hierarchy
select haou.date_from,
haou.attribute1 || ' ' || haou.name,
haou.organization_id,
uoepp.uoe_functions.uoe_get_college_org_code(haou.organization_id) L3,
uoepp.uoe_functions.uoe_get_school_org_code(haou.organization_id) L4,
(select listagg(organization_id, ',') within group (order by haou1.organization_id )from hr_all_organization_units haou1
where haou1.attribute1 = haou.attribute1)
from hr_all_organization_units haou
where date_from = '01-AUG-2018 00.00.00'
order by uoepp.uoe_functions.uoe_get_college_org_code(haou.organization_id), uoepp.uoe_functions.uoe_get_school_org_code(haou.organization_id);
View MySQL Misc
-- auto increment values
SELECT table_name, `AUTO_INCREMENT`
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA = 'hshp';
View ppipmi
-- run in uoepp
select ut.table_name, utc.*
from user_tables ut, user_tab_columns utc
where ut.table_name = utc.table_name
--and ut.table_name like 'PPIPMI%'
and utc.column_name IN ('ASSIGN_ID', 'ASSIGNMENT_ID', 'PERSON_ID');
View Misc eTime
select * from etime_access where uun = 'lhanks'
View grade_sp_salary_details
select pps.parent_spine_id, pps.name pay_scale, pg1.grade_id, pg1.name grade_name, psp.spinal_point_id, psp.spinal_point, pgrf.effective_start_date, pgrf.value salary
from per_parent_spines pps -- details of payscale
,per_grades pg1 -- grade definitions for a business group
,per_grade_spines_f pgsf -- stores the progression points for each grade scale
,per_spinal_point_steps_f pspsf -- grade steps definied in a specific grade scale and their incremental order
,per_spinal_points psp
,pay_grade_rules_f pgrf
where pg1.grade_id = pgsf.grade_id
and pg1.date_to is null
and sysdate between pgsf.effective_start_date and pgsf.effective_end_date
View fast_formula_01.sql
SELECT FORMU.BASE_FORMULA_NAME Formula_Name,
FTY.FORMULA_TYPE_NAME TYPE,
FORMU.EFFECTIVE_START_DATE Effective_Date,
FORMU.FORMULA_TEXT
FROM FF_FORMULAS_F_VL FORMU,
FF_FORMULA_TYPES FTY
WHERE FORMU.BUSINESS_GROUP_ID = 5000
AND FORMU.FORMULA_TYPE_ID = FTY.FORMULA_TYPE_ID;