Oracle HRMS Query to Get Previous Grade
SELECT
pg_old.name
from apps.per_all_people_f papf,
apps.per_all_assignments_f paaf_old,
apps.per_grades pg_old,
apps.per_all_assignments_f paaf_new,
apps.per_grades pg_new
where 1=1
and papf.person_id = paaf_old.person_id
and papf.person_id = paaf_new.person_id
and paaf_old.person_id = paaf_new.person_id
and paaf_old.assignment_type = 'E'
and paaf_old.primary_flag = 'Y'
and paaf_new.assignment_type = 'E'
and paaf_new.primary_flag = 'Y'
and pg_old.grade_id = paaf_old.grade_id
and pg_new.grade_id = paaf_new.grade_id
and paaf_old.grade_id <> paaf_new.grade_id
and trunc(paaf_old.effective_end_date)+1 =trunc(paaf_new.effective_start_date)
and trunc(sysdate) between papf.effective_start_date and papf.effective_end_date
and trunc(paaf_new.effective_start_date) between paaf_new.effective_start_date and paaf_new.effective_end_date
--and papf.person_id = 162
and papf.person_id = paaf.person_id
and paaf_new.effective_start_date =(select
((to_char(max(paaf_new.effective_start_date),'DD-MON-RRRR')))
from apps.per_all_people_f papf,
apps.per_all_assignments_f paaf_old,
apps.per_grades pg_old,
apps.per_all_assignments_f paaf_new,
apps.per_grades pg_new
where 1=1
and papf.person_id = paaf_old.person_id
and papf.person_id = paaf_new.person_id
and paaf_old.person_id = paaf_new.person_id
and paaf_old.assignment_type = 'E'
and paaf_old.primary_flag = 'Y'
and paaf_new.assignment_type = 'E'
and paaf_new.primary_flag = 'Y'
and pg_old.grade_id = paaf_old.grade_id
and pg_new.grade_id = paaf_new.grade_id
and paaf_old.grade_id <> paaf_new.grade_id
and trunc(paaf_old.effective_end_date)+1 =trunc(paaf_new.effective_start_date)
and trunc(sysdate) between papf.effective_start_date and papf.effective_end_date
and trunc(paaf_new.effective_start_date) between paaf_new.effective_start_date and paaf_new.effective_end_date
--and papf.person_id = 162
and papf.person_id = paaf.person_id )
Great Query It worked. Acutally I tried to work out and took some time. But while searching I came upon yours and its good..
ReplyDeletehow to add all the gross salary,detection of a year and calculate income tax
ReplyDeleteI was diagnosed with stage 3 breast cancer in August of 2010. A precious friend told me about Dr Itua Herbal Center in West Africa,She gave me his contact number and email address,I contacted him quickly so he give me guaranty that his herbal medicine will cure my cancer and i will be heal forever I said Okay.I ask him about the process for the cure,he ask me to pay for the fees which i did and within 7 working days he sent me the herbal medicine then he instructed me on how to drink it for two weeks to cure.I told Gomez my friend about the herbal medicine so he gave me go ahead to drink it.So after drinking it for two weeks i was cured I'm so grateful and i promise i will recommend anyone with cancer to him and that what i'm doing.Dr Itua Herbal Medicine makes me believes there is a hope for people suffering from,Parkinson's disease,Schizophrenia,Cancer,Scoliosis,Bladder Cancer,Colorectal Cancer,Breast Cancer,Kidney Cancer,Leukemia,Lung Cancer,Skin Cancer,Uterine Cancer,Prostate Cancer,Fibromyalgia,a
ReplyDeleteSyndrome Fibrodysplasia ,Epilepsy Dupuytren's disease, Diabetes ,Coeliac disease, Angiopathy, Ataxia,Arthritis,Amyotrophic Lateral Sclerosis,Alzheimer's disease,Adrenocortical carcinoma.Asthma,Allergic diseases.Hiv_ Aids,Herpes,Inflammatory bowel disease ,Copd,Diabetes.
Here Is His Contact Info......[Email...drituaherbalcenter@gmail.com/Whatsapp...+2348149277967]