Skip to content

Instantly share code, notes, and snippets.

johnboychan

Block or report user

Report or block johnboychan

Hide content and notifications from this user.

Learn more about blocking users

Contact Support about this user’s behavior.

Learn more about reporting abuse

Report abuse
View GitHub Profile
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;
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 Misc eTime
select * from etime_access where uun = 'lhanks'
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 MySQL Misc
-- auto increment values
SELECT table_name, `AUTO_INCREMENT`
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA = 'hshp';
@johnboychan
johnboychan / Org Hierarchy
Last active Sep 22, 2018
Org Hierarchy #ebs
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 expenses_performace_cfadmin
export COLUMNS=2048
top -Hcb-p 54018
View oracle send email
DECLARE
v_mail_conn utl_smtp.connection;
v_boundary varchar2(50) := '----=*#abc1234321cba#*=';
v_step pls_integer := 24573;
v_env varchar2(5);
v_mail_server varchar2(64) := 'bulkmailrelay.ucs.ed.ac.uk';
v_from varchar2(100) := 'john.chan@ed.ac.uk';
BEGIN
View sql server table size
SELECT
t.NAME AS TableName,
s.Name AS SchemaName,
p.rows AS RowCounts,
SUM(a.total_pages) * 8 AS TotalSpaceKB,
CAST(ROUND(((SUM(a.total_pages) * 8) / 1024.00), 2) AS NUMERIC(36, 2)) AS TotalSpaceMB,
SUM(a.used_pages) * 8 AS UsedSpaceKB,
CAST(ROUND(((SUM(a.used_pages) * 8) / 1024.00), 2) AS NUMERIC(36, 2)) AS UsedSpaceMB,
@johnboychan
johnboychan / Oracle HR - P1703-008
Last active Feb 26, 2019
[P1703-008] HRSS01 HR not informing IDM to end some Staff affiliations every month
View Oracle HR - P1703-008
-- new epp4220 code put live on August 09 2018
-- Adam removed ended accounts on August 20th
select 'Assignment Leaver Start Date: ' || trunc(effective_start_date) || ', Assignment Last Updated: ' || last_update_date, person_id, assignment_id, effective_start_date, effective_end_date, assignment_status_type_id, payroll_id, assignment_number, ass_attribute15, last_update_date
from per_all_assignments_f where person_id = (select distinct person_id from per_all_people_f where employee_number IN ('160286') )
order by assignment_id, effective_start_date asc
You can’t perform that action at this time.