Oracle HR and Payroll Related Queries
Oracle EBS/Apps R12. ( Oracle HR / Payroll ) Important Queries
Note: All following queries are customized and may not fit in your setup but can give you some idea to get appropriate results. Thanks
Costing Detail Query:
select
paf.ASSIGNMENT_NUMBER, ppf.FULL_NAME ,pet.element_name,pca.CONCATENATED_SEGMENTS,
decode(pc.DEBIT_OR_CREDIT,’D’,pc.COSTED_VALUE) Debit,
decode(pc.DEBIT_OR_CREDIT,’C’,pc.COSTED_VALUE) Credit
from
per_people_f ppf,
per_assignments_f paf,
pay_assignment_actions pav,
pay_payroll_actions ppa,
pay_costs pc,
PAY_COST_ALLOCATION_KEYFLEX pca,
pay_element_types_f pet,
pay_run_results prr,
pay_run_result_values prrv
where ppf.PERSON_ID=paf.PERSON_ID
and paf.ASSIGNMENT_ID=pav.ASSIGNMENT_ID
and paf.PRIMARY_FLAG=’Y’
and ppf.EMPLOYEE_NUMBER=:p_emp_no
and pav.PAYROLL_ACTION_ID=ppa.PAYROLL_ACTION_ID
and trunc(ppa.EFFECTIVE_DATE) between :p_start_date and :p_end_date
and pav.ASSIGNMENT_ACTION_ID=pc.ASSIGNMENT_ACTION_ID
and pc.COST_ALLOCATION_KEYFLEX_ID=pca.COST_ALLOCATION_KEYFLEX_ID
and pet.ELEMENT_TYPE_ID=prr.ELEMENT_TYPE_ID
and prr.RUN_RESULT_ID=prrv.RUN_RESULT_ID
and pc.RUN_RESULT_ID=prrv.RUN_RESULT_ID
and pc.INPUT_VALUE_ID=prrv.INPUT_VALUE_ID
and :p_end_date between pet.EFFECTIVE_START_DATE and pet.EFFECTIVE_END_DATE
and ppf.EFFECTIVE_END_DATE = (select max(effective_end_date) from per_people_f where person_id=ppf.PERSON_ID)
and paf.EFFECTIVE_END_DATE = (select max(effective_end_date) from per_assignments_f where assignment_id=paf.ASSIGNMENT_ID)
Query to Find of Number of Working Days in Months.
SELECT themonth, count(theday) cnt
FROM ( SELECT TO_CHAR(TRUNC(SYSDATE,’YY’)+LEVEL-1,’Month’) themonth
, TO_CHAR(TRUNC(SYSDATE,’YY’)+LEVEL-1,’DY’) theday
FROM dual
CONNECT BY LEVEL <= ADD_MONTHS(TRUNC(SYSDATE,’YY’),12)-TRUNC(SYSDATE,’YY’)
)
WHERE theday NOT IN (‘SAT’,’SUN’)
GROUP BY themonth
ORDER BY TO_DATE(themonth,’MM’)
Query to to find the Employee Supervisor name
SELECT papf1.full_name supervisor_nameFrom apps.per_all_people_f papf,apps.per_all_assignments_f paaf,apps.per_all_people_f papf1WHERE papf.person_id = paaf.person_idAND paaf.primary_flag = ‘Y’AND paaf.assignment_type = ‘E’AND paaf.supervisor_id = papf1.person_idAND papf1.current_employee_flag = ‘Y’AND papf.business_group_id = paaf.business_group_idAND SYSDATE BETWEEN papf.effective_start_date and papf.effective_end_dateAND SYSDATE BETWEEN paaf.effective_start_date AND paaf.effective_end_dateAND SYSDATE BETWEEN papf1.effective_start_date AND papf1.effective_end_dateAND papf.employee_number = :p_emp_number;
Query to find out payroll Costing Detail / “How to find out payroll costed accounts” / “Query to find out Payroll Charged Accounts”.
SELECT distinct d.EMPLOYEE_NUMBER,d.FULL_NAME,a.CONCATENATED_SEGMENTS, a.element_name,decode(a.debit_or_credit,’Debit’,COSTED_VALUE,”) Debit ,
decode(debit_or_credit,’Credit’,COSTED_VALUE,”) Credit
FROM pay_costs_v a, pay_assignment_actions_v b,per_assignments_x c,per_people_x d
where d.EMPLOYEE_NUMBER= nvl(:p_emp_no,d.EMPLOYEE_NUMBER)
and trunc (b.EFFECTIVE_DATE) between :p_from_date and :p_to_date
and a.ASSIGNMENT_ACTION_ID=b.ASSIGNMENT_ACTION_ID
and b.ASSIGNMENT_ID=c.ASSIGNMENT_ID
and c.PERSON_ID=d.PERSON_ID
Query to Find All Active Employees and Current Salary.
select EMPLOYEE_NUMBER,a.FULL_NAME,x.USER_PERSON_TYPE, c.PROPOSED_SALARY_n Basi_Salary–,c.CHANGE_DATE–,c.*
from per_people_f A,per_assignments_f b,pER_PAY_pROposals c
,per_person_types x
, per_person_type_usages_f e
where a.PERSON_ID=b.PERSON_ID
and b.ASSIGNMENT_ID=C.ASSIGNMENT_ID
and a.EMPLOYEE_NUMBER is not null
–and a.EMPLOYEE_NUMBER=:emp_num
and c.CHANGE_DATE = (select max(d.CHANGE_DATE) from pER_PAY_pROposals d where d.ASSIGNMENT_ID=b.ASSIGNMENT_ID and d.approved = ‘Y’)
–and c.CHANGE_DATE>=:change_date
and b.PAYROLL_ID=62
and :p_effective_date between a.EFFECTIVE_START_DATE and a.EFFECTIVE_END_DATE
and :p_effective_date between b.EFFECTIVE_START_DATE and b.EFFECTIVE_END_DATE
and a.PERSOn_id = e.PERSON_ID
and a.EFFECTIVE_START_DATE between e.EFFECTIVE_START_DATE and e.EFFECTIVE_END_DATE
and e.PERSON_TYPE_ID = x.PERSON_TYPE_ID
and x.SYSTEM_PERSON_TYPE = ‘EMP’
Query fo Find out all Earning and Deduction Elements and values after Payroll Run
SELECT ppf.employee_number,ppf.person_id,ppf.full_name,ppa.TIME_PERIOD_ID,ppa.EFFECTIVE_DATE,TP.PERIOD_NAME,paf.ORGANIZATION_ID,
sum(decode(pec.CLASSIFICATION_NAME,’Earnings’,to_number(rrv.result_value),
0)
) Earnings,
sum(decode(pec.CLASSIFICATION_NAME,’Voluntary Deductions’,to_number(rrv.result_value),
‘Involuntary Deductions’,to_number(rrv.result_value),
‘Employer Charges’,to_number(rrv.result_value),
0)
) Deductions
–ety.element_name,ety.CLASSIFICATION_ID
— PD.SEGMENT5 POSITION_NO,PD.SEGMENT6 POSITION_NAME,
FROM per_people_x ppf,
per_assignments_x paf,
pay_assignment_actions pas,
pay_payroll_actions ppa,
pay_run_results rr,
pay_run_result_values rrv,
pay_element_types_f ety,
pay_input_values_F I,
PER_TIME_PERIODS TP,
PAY_ELEMENT_CLASSIFICATIONS_VL pec
WHERE ppf.person_id = paf.person_id
AND paf.assignment_id = pas.assignment_id
AND pas.assignment_action_id = rr.assignment_action_id
AND ppa.payroll_action_id = pas.payroll_action_id
AND rr.element_type_id = ety.element_type_id
AND i.element_type_id = ety.element_type_id
AND rrv.run_result_id = rr.run_result_id
AND rrv.input_value_id = i.input_value_id
and TP.TIME_PERIOD_ID = PPA.TIME_PERIOD_ID
and ety.CLASSIFICATION_ID=pec.CLASSIFICATION_ID
AND i.name = ‘Pay Value’
— AND HR_GENERAL.DECODE_LATEST_POSITION_DEF_ID(PAF.POSITION_ID) = PD.POSITION_DEFINITION_ID
and ppa.EFFECTIVE_DATE BETWEEN :p_st_effect_date AND :p_end_effect_date
and ppf.employee_number = nvl(:p_emp_number,ppf.employee_number)
group by ppf.full_name,ppa.TIME_PERIOD_ID,effective_date,–To_Number(Wassa_HR_PACKAGE.Nid_Salary_By_Date(paf.assignment_id,ppa.EFFECTIVE_DATE,’N’)),
ppf.employee_number,ppf.person_id ,–PD.SEGMENT5 ,PD.SEGMENT6,
TP.period_name,paf.ORGANIZATION_ID
Query for Oracle Payroll Salary Slip / Salary Slip Detail Report Query / How to find out detail of salary slip/ Element wise Landscape Salary Slip at Payroll Run Levle or quick pay level
Note: I have hardcoded element names. You can also hardcode your elements to get Landscaped salary slip.
SELECT ppf.employee_number,ppf.person_id,ppf.full_name,ppa.TIME_PERIOD_ID,ppa.EFFECTIVE_DATE ,TP.PERIOD_NAME,
sum(decode(ety.element_name,’Basic Salary’ ,TO_NUMBER(rrv.result_value),
‘Basic Sick Leave Payment’ ,TO_NUMBER(rrv.result_value),
‘Basic Out Work Incident Leave Payment’,TO_NUMBER(rrv.result_value),
0)) Earned_salary,
/********************************************************************************************************/
sum(decode(ety.element_name,’Transportation Allowance’ ,TO_NUMBER(rrv.result_value),
‘Transportation Sick Leave Payment’ ,TO_NUMBER(rrv.result_value),
‘Transportation Out Work Incident Leave Payment’,TO_NUMBER(rrv.result_value),
0)) Transportation_allowance,
/*************************************************************************************************************/
sum(decode(ety.element_name,’Work Type Allowance’ ,TO_NUMBER(rrv.result_value),
‘Work Type Sick Leave Payment’ ,TO_NUMBER(rrv.result_value),
‘Work Type Out Work Incident Leave Payment’,TO_NUMBER(rrv.result_value),
0)) worktype_allowance,
/***************************************************************************************************************/
sum(decode(ety.element_name,’Damages Allowance’ ,TO_NUMBER(rrv.result_value),
‘Damages Sick Leave Payment’ ,TO_NUMBER(rrv.result_value),
‘Damages Out Work Incident Leave Payment’,TO_NUMBER(rrv.result_value),
0)) Damage_allowance,
/*****************************************************************************************************************/
sum(decode(ety.element_name,’Danger Allowance’ ,TO_NUMBER(rrv.result_value),
‘Danger Sick Leave Payment’ ,TO_NUMBER(rrv.result_value),
‘Danger Out Work Incident Leave Payment’,TO_NUMBER(rrv.result_value),
0)) Danger_allowance,
/*************************************************************************************************************/
sum(decode(ety.element_name,’Inflation Allowance’ ,TO_NUMBER(rrv.result_value),0)) Inflation_Allowance,
/********************************************************* Deductions ************************************************/
sum(decode(ety.element_name,’Loan Recovery’ ,TO_NUMBER(rrv.result_value),0)) Loan_recovery,
sum(decode(ety.element_name,’Loan Recovery Housing’ ,TO_NUMBER(rrv.result_value),0)) Loan_Recovery_Housing,
sum(decode(ety.element_name,’Loan Recovery Others’ ,TO_NUMBER(rrv.result_value),0)) Loan_Recovery_Others,
sum(decode(ety.element_name,’Housing Deduction’ ,TO_NUMBER(rrv.result_value),0)) Housing_Deduction,
sum(decode(ety.element_name,’Penalty’ ,TO_NUMBER(rrv.result_value),0)) Penalty,
/***********************************************************************************************************************/
sum(decode(ety.element_name,’Civil Pension’ ,TO_NUMBER(rrv.result_value),
‘Social Insurance’ ,TO_NUMBER(rrv.result_value),
0)) Civil_pension,
/************************************************************************************************************************************/
sum(decode(ety.element_name,’In Out Leave Deduction’ ,TO_NUMBER(rrv.result_value),0)) In_Out_Leave_Deduction,
sum(decode(ety.element_name,’Unpaid Leave Deduction’ ,TO_NUMBER(rrv.result_value),0)) Unpaid_Leave_Deduction,
sum(decode(ety.element_name,’Retrieve Mony Recovery’ ,TO_NUMBER(rrv.result_value),0)) Retrieve_Mony_Recovery
FROM per_people_x ppf,
per_assignments_x paf,
pay_assignment_actions pas ,
pay_payroll_actions ppa,
pay_run_results rr,
pay_run_result_values rrv,
pay_element_types_f ety,
pay_input_values_F I ,
PER_TIME_PERIODS TP
— PER_POSITION_DEFINITIONS PD
— PAY_INPUT_VALUES_F
WHERE ppf.person_id = paf.person_id
AND paf.assignment_id = pas.assignment_id
AND pas.assignment_action_id = rr.assignment_action_id
AND ppa.payroll_action_id = pas.payroll_action_id
AND rr.element_type_id = ety.element_type_id
AND i.element_type_id = ety.element_type_id
AND rrv.run_result_id = rr.run_result_id
AND rrv.input_value_id = i.input_value_id
and TP.TIME_PERIOD_ID = PPA.TIME_PERIOD_ID
AND i.name = ‘Pay Value’
— AND HR_GENERAL.DECODE_LATEST_POSITION_DEF_ID(PAF.POSITION_ID) = PD.POSITION_DEFINITION_ID
and ppa.EFFECTIVE_DATE BETWEEN :P_FROM_DATE AND :P_TO_DATE
and ppf.employee_number = :P_Employee_number
–in(34000/*1546014859,14666,35343,15201,15202*/)
group by ppf.full_name,ppa.TIME_PERIOD_ID,effective_date,
ppf.employee_number,ppf.person_id ,–PD.SEGMENT5 ,PD.SEGMENT6,
TP.period_name
order by ppa.EFFECTIVE_DATE
I visited your web site, you did a real y great a great job the queries & other material regarding oracle is very helpful.
Regards,
Najeebullah
HI I visited your web site and the queries what are posted are really excellent and very helpful to me
thank you very much
Excellent work Dude.
Thank you.
Hi,
When Payroll Rollback runs, then what changes required to above queries? Is it like, when I run payroll again then, one more record will get inserted for same element in the same period. But we have one more record with same data. How to handle this scenario?
Rollback simply delete records from database. No Need to change any thing. Just Rerun Payroll/prepayment etc and execute query.
your blog gives me a lot of information and helped a lot. sir i have a problem of duplication in my report the query of the report as follows
/*query*/
select DISTINCT
ppx.employee_number,
ppx.person_id,
nvl(UPDATED_BY(ppx.person_id,PAAF.last_updated_by,EMP_STATUS(ppx.person_id,NVL(ppos.actual_termination_date,paaf.EFFECTIVE_END_DATE)),paaf.EFFECTIVE_END_DATE),’Âáí’) UPDATED_BY_NAME,
PHON_NUM(ppx.person_id) PHON_NUM,
ppx.NATIONAL_IDENTIFIER,
ppx.full_name,
DECODE(ppx.SEX,’M’,’ÐßÑ’,’ÃäËì’) sex,
ppd.segment1 pos_name,
ppd.segment2 pos_nu,
paaou.name pos_org_name,
–DECODE(person_type_id,1120,’??? ??? ????’,’??E???’) employed ,
—
TO_CHAR(paaf.LAST_UPDATE_DATE,’RRRR-MM-DD HH:MM:SS’,’nls_calendar=”Arabic Hijrah”’) ASS_CREATE_DATE,
TO_CHAR(ppx.LAST_UPDATE_DATE,’RRRR-MM-DD HH:MM:SS’,’nls_calendar=”Arabic Hijrah”’) PER_CREATE_DATE,
— TO_CHAR(pp.LAST_UPDATE_DATE,’RRRR-MM-DD HH:MM:SS’,’nls_calendar=”Arabic Hijrah”’) ADD_CREATE_DATE,
trunc(months_between(sysdate,ppx.date_of_birth)/12) year,
trunc(mod(months_between(sysdate,ppx.date_of_birth),12)) month,
trunc(sysdate-Add_Months(ppx.date_of_birth,Trunc(months_between(sysdate,ppx.date_of_birth)/12)*12
+TRUNC(MOD(months_between(sysdate,ppx.date_of_birth),12)))) DAY,
trunc(months_between(DECODE(paaf.EFFECTIVE_END_DATE,to_date(’12/31/4712′,’MM/DD/YYYY’),sysdate,paaf.EFFECTIVE_END_DATE),paaf.EFFECTIVE_START_DATE)/12) ASS_year,
trunc(mod(months_between(DECODE(paaf.EFFECTIVE_END_DATE,to_date(’12/31/4712′,’MM/DD/YYYY’),sysdate,paaf.EFFECTIVE_END_DATE),paaf.EFFECTIVE_START_DATE),12)) ASS_month,
trunc(DECODE(paaf.EFFECTIVE_END_DATE,to_date(’12/31/4712′,’MM/DD/YYYY’),sysdate,paaf.EFFECTIVE_END_DATE)-Add_Months(paaf.EFFECTIVE_START_DATE,Trunc(months_between(DECODE(paaf.EFFECTIVE_END_DATE,to_date(’12/31/4712′,’MM/DD/YYYY’),sysdate,paaf.EFFECTIVE_END_DATE),paaf.EFFECTIVE_START_DATE)/12)*12
+TRUNC(MOD(months_between(DECODE(paaf.EFFECTIVE_END_DATE,to_date(’12/31/4712′,’MM/DD/YYYY’),sysdate,paaf.EFFECTIVE_END_DATE),paaf.EFFECTIVE_START_DATE),12)))) ASS_DAY,
TO_CHAR(paaf.EFFECTIVE_START_DATE,’RRRR-MM-DD’,’nls_calendar=”Arabic Hijrah”’) ASS_START_DATE,
TO_CHAR(DECODE(paaf.EFFECTIVE_END_DATE,to_date(’12/31/4712′,’MM/DD/YYYY’),sysdate,paaf.EFFECTIVE_END_DATE),’RRRR-MM-DD’,’nls_calendar=”Arabic Hijrah”’) ASS_END_DATE,
ca.NAME Actual_ass,
pg.name Grade_name, hr_general.decode_STEP (paaf.special_ceiling_step_id ,SYSDATE) grade_STEP,
TO_CHAR(ppx.START_DATE ,’RRRR-MM-DD’,’nls_calendar=”Arabic Hijrah”’) START_DATE,
TO_CHAR(ppx.date_of_birth ,’RRRR-MM-DD’,’nls_calendar=”Arabic Hijrah”’) birth_date,
substr(ppg.group_name,1,instr(ppg.group_name,’.’)-1) p_group,
fcl.MEANING NATIONALITY,
ppx.EMAIL_ADDRESS,
pca.name emoloyee_curr_org,
pqtv.name degree,
pq.title,
pq.grade_attained,
pq.awarding_body,
ppx.TOWN_OF_BIRTH,
DECODE(ppx.MARITAL_STATUS,’M’,’ãÊÒæÌ’,’S’,’ÃÚÒÈ’) Mat_status,
rg.MEANING hired_as,
–TO_CHAR(pp.phone_number) phone_number,
pq.comments grad_date,
uni_pos.MEANING uni_position,
TO_CHAR(ppos.actual_termination_date ,’RRRR-MM-DD’,’nls_calendar=”Arabic Hijrah”’) leave_date,
leave_reas.MEANING leave_reason,
ppp.proposed_salary_n
from per_all_assignments_f paaf,
per_people_x ppx,
PAY_PEOPLE_GROUPS ppg,
per_grades pg,
per_position_definitions ppd,
HR_ALL_POSITIONS_F hapf,
per_all_organization_units paaou,
PER_QUALIFICATIONS_V pq,
fnd_common_lookups fcl,
PER_QUALIFICATION_TYPES_V pqtv,
per_collective_agreements pca ,
–per_phones pp ,
fnd_common_lookups rg,
fnd_common_lookups uni_pos,
Per_Periods_Of_Service ppos,
fnd_common_lookups leave_reas,
per_pay_proposals ppp,
–OMAR
–FOR ACCTUAL ORGANIZATION
PER_COLLECTIVE_AGREEMENTS CA
where ppx.person_id = paaf.person_id
and ppos.person_id = ppx.PERSON_ID
and rg.LOOKUP_TYPE (+) = ‘REGISTERED_DISABLED’
and rg.LOOKUP_CODE (+) = ppx.REGISTERED_DISABLED_FLAG
and uni_pos.LOOKUP_TYPE (+) = ‘EMP_CAT’
and uni_pos.LOOKUP_code (+) = paaf.employment_category
and leave_reas.LOOKUP_TYPE (+) = ‘LEAV_REAS’
AND leave_reas.LOOKUP_CODE (+) = ppos.leaving_reason
— and pp.parent_id(+) = ppx.PERSON_ID
and pq.person_id(+) = ppx.person_id
and pqtv.qualification_type_id(+) = pq.qualification_type_id
and ppg.people_group_id = paaf.people_group_id
and paaf.organization_id = paaou.organization_id
and paaf.position_id = hapf.position_id (+)
and hapf.position_definition_id = ppd.position_definition_id (+)
and pg.grade_id(+) = paaf.grade_id
and fcl.LOOKUP_TYPE = ‘NATIONALITY’
AND fcl.LOOKUP_CODE(+) = ppx.NATIONALITY
and pca.collective_agreement_id(+) = paaf.collective_agreement_id
and to_number(to_char(paaf.effective_end_date,’yyyy’)) > 4000
and ppp.assignment_id = paaf.assignment_id
and ppp.change_date = (select max(ppp1.change_date)
from per_pay_proposals ppp1
where ppp1.assignment_id = ppp.assignment_id)
and ppx.person_type_id = 1120
— AND sysdate between paaf.EFFECTIVE_START_DATE and paaf.EFFECTIVE_END_DATE
— AND sysdate between ppx.EFFECTIVE_START_DATE and ppx.EFFECTIVE_END_DATE
–FOR ACCTUAL ORGANIZATION
AND CA.COLLECTIVE_AGREEMENT_ID(+) = PAAF.COLLECTIVE_AGREEMENT_ID
union
select distinct
ppx.employee_number,
ppx.person_id,
nvl(UPDATED_BY(ppx.person_id,PAAF.last_updated_by,EMP_STATUS(ppx.person_id,NVL(ppos.actual_termination_date,paaf.EFFECTIVE_END_DATE)),paaf.EFFECTIVE_END_DATE),’Âáí’) UPDATED_BY_NAME,
PHON_NUM(ppx.person_id) PHON_NUM,
ppx.NATIONAL_IDENTIFIER , ppx.full_name,decode(ppx.SEX,’M’,’ÐßÑ’,’ÃäËì’) sex,
ppd.segment1 pos_name, ppd.segment2 pos_nu,paaou.name pos_org_name,
— decode(person_type_id,1120,’??? ??? ????’,’??E???’) employed ,
TO_CHAR(paaf.LAST_UPDATE_DATE,’RRRR-MM-DD HH:MM:SS’,’nls_calendar=”Arabic Hijrah”’) ASS_CREATE_DATE,
TO_CHAR(ppx.LAST_UPDATE_DATE,’RRRR-MM-DD HH:MM:SS’,’nls_calendar=”Arabic Hijrah”’) PER_CREATE_DATE,
–TO_CHAR(pp.LAST_UPDATE_DATE,’RRRR-MM-DD HH:MM:SS’,’nls_calendar=”Arabic Hijrah”’) ADD_CREATE_DATE,
trunc(months_between(sysdate,ppx.date_of_birth)/12) B_year,
trunc(mod(months_between(sysdate,ppx.date_of_birth),12)) B_month,
trunc(sysdate-Add_Months(ppx.date_of_birth,Trunc(months_between(sysdate,ppx.date_of_birth)/12)*12
+TRUNC(MOD(months_between(sysdate,ppx.date_of_birth),12)))) B_DAY,
trunc(months_between(DECODE(paaf.EFFECTIVE_END_DATE,to_date(’12/31/4712′,’MM/DD/YYYY’),sysdate,paaf.EFFECTIVE_END_DATE),paaf.EFFECTIVE_START_DATE)/12) ASS_year,
trunc(mod(months_between(DECODE(paaf.EFFECTIVE_END_DATE,to_date(’12/31/4712′,’MM/DD/YYYY’),sysdate,paaf.EFFECTIVE_END_DATE),paaf.EFFECTIVE_START_DATE),12)) ASS_month,
trunc(DECODE(paaf.EFFECTIVE_END_DATE,to_date(’12/31/4712′,’MM/DD/YYYY’),sysdate,paaf.EFFECTIVE_END_DATE)-Add_Months(paaf.EFFECTIVE_START_DATE,Trunc(months_between(DECODE(paaf.EFFECTIVE_END_DATE,to_date(’12/31/4712′,’MM/DD/YYYY’),sysdate,paaf.EFFECTIVE_END_DATE),paaf.EFFECTIVE_START_DATE)/12)*12
+TRUNC(MOD(months_between(DECODE(paaf.EFFECTIVE_END_DATE,to_date(’12/31/4712′,’MM/DD/YYYY’),sysdate,paaf.EFFECTIVE_END_DATE),paaf.EFFECTIVE_START_DATE),12)))) ASS_DAY,
TO_CHAR(paaf.EFFECTIVE_START_DATE,’RRRR-MM-DD’,’nls_calendar=”Arabic Hijrah”’) ASS_START_DATE,
TO_CHAR(DECODE(paaf.EFFECTIVE_END_DATE,to_date(’12/31/4712′,’MM/DD/YYYY’),sysdate,paaf.EFFECTIVE_END_DATE),’RRRR-MM-DD’,’nls_calendar=”Arabic Hijrah”’) ASS_END_DATE,
CA.name Actual_ass,
pg.name Grade_name, hr_general.decode_STEP (paaf.special_ceiling_step_id ,SYSDATE) grade_STEP,
TO_CHAR(ppx.START_DATE ,’RRRR-MM-DD’,’nls_calendar=”Arabic Hijrah”’) START_DATE,
TO_CHAR(ppx.date_of_birth ,’RRRR-MM-DD’,’nls_calendar=”Arabic Hijrah”’) birth_date,
substr(ppg.group_name,1,instr(ppg.group_name,’.’)-1) p_group,
fcl.MEANING NATIONALITY, ppx.EMAIL_ADDRESS, pca.name emoloyee_curr_org,
pqtv.name degree, pq.title, pq.grade_attained, pq.awarding_body, ppx.TOWN_OF_BIRTH,
decode(ppx.MARITAL_STATUS,’M’,’ãÊÒæÌ’,’S’,’ÃÚÒÈ’) Mat_status,
rg.MEANING hired_as,
–pp.phone_number,
pq.comments grad_date,
uni_pos.MEANING uni_position,
TO_CHAR(ppos.actual_termination_date ,’RRRR-MM-DD’,’nls_calendar=”Arabic Hijrah”’) leave_date,
leave_reas.MEANING leave_reason,
ppp.proposed_salary_n
from per_all_assignments_f paaf,
per_people_x ppx,
PAY_PEOPLE_GROUPS ppg,
per_grades pg,
per_position_definitions ppd,
HR_ALL_POSITIONS_F hapf,
per_all_organization_units paaou,
PER_QUALIFICATIONS_V pq,
fnd_common_lookups fcl,
PER_QUALIFICATION_TYPES_V pqtv,
per_collective_agreements pca ,
–per_phones pp ,
fnd_common_lookups rg,
fnd_common_lookups uni_pos,
Per_Periods_Of_Service ppos,
fnd_common_lookups leave_reas,
per_pay_proposals ppp,
–OMAR
–FOR ACCTUAL ORGANIZATION
PER_COLLECTIVE_AGREEMENTS CA
where ppx.person_id = paaf.person_id
and ppos.person_id = ppx.PERSON_ID
and rg.LOOKUP_TYPE (+) = ‘REGISTERED_DISABLED’
and rg.LOOKUP_CODE (+) = ppx.REGISTERED_DISABLED_FLAG
and uni_pos.LOOKUP_TYPE (+) = ‘EMP_CAT’
and uni_pos.LOOKUP_code (+) = paaf.employment_category
and leave_reas.LOOKUP_TYPE (+) = ‘LEAV_REAS’
AND leave_reas.LOOKUP_CODE (+) = ppos.leaving_reason
–and pp.parent_id(+) = ppx.PERSON_ID
and pq.person_id(+) = ppx.person_id
and pqtv.qualification_type_id(+) = pq.qualification_type_id
and ppg.people_group_id = paaf.people_group_id
and paaf.organization_id = paaou.organization_id
and paaf.position_id = hapf.position_id (+)
and hapf.position_definition_id = ppd.position_definition_id (+)
and pg.grade_id(+) = paaf.grade_id
and fcl.LOOKUP_TYPE = ‘NATIONALITY’
AND fcl.LOOKUP_CODE(+) = ppx.NATIONALITY
and pca.collective_agreement_id(+) = paaf.collective_agreement_id
—- and to_number(to_char(paaf.effective_end_date,’yyyy’)) > 4000
and ppp.assignment_id = paaf.assignment_id
and ppp.change_date = (select max(ppp1.change_date)
from per_pay_proposals ppp1
where ppp1.assignment_id = ppp.assignment_id)
–FOR ACCTUAL ORGANIZATION
AND CA.COLLECTIVE_AGREEMENT_ID(+) = PAAF.COLLECTIVE_AGREEMENT_ID
and ppx.person_type_id = 1123
— AND sysdate between paaf.EFFECTIVE_START_DATE and paaf.EFFECTIVE_END_DATE
— AND sysdate between ppx.EFFECTIVE_START_DATE and ppx.EFFECTIVE_END_DATE
order by full_name
please help me where exactly a mistake is
Is your issue resolved?
no its showing duplicate rows for ex- employee information
send me result of following query
select *
from per_all_assignments_f paaf,
per_people_x ppx,
PAY_PEOPLE_GROUPS ppg,
per_grades pg,
per_position_definitions ppd,
HR_ALL_POSITIONS_F hapf,
per_all_organization_units paaou,
PER_QUALIFICATIONS_V pq,
fnd_common_lookups fcl,
PER_QUALIFICATION_TYPES_V pqtv,
per_collective_agreements pca ,
–per_phones pp ,
fnd_common_lookups rg,
fnd_common_lookups uni_pos,
Per_Periods_Of_Service ppos,
fnd_common_lookups leave_reas,
per_pay_proposals ppp,
–OMAR
–FOR ACCTUAL ORGANIZATION
PER_COLLECTIVE_AGREEMENTS CA
where ppx.person_id = paaf.person_id
and ppos.person_id = ppx.PERSON_ID
and rg.LOOKUP_TYPE (+) = ‘REGISTERED_DISABLED’
and rg.LOOKUP_CODE (+) = ppx.REGISTERED_DISABLED_FLAG
and uni_pos.LOOKUP_TYPE (+) = ‘EMP_CAT’
and uni_pos.LOOKUP_code (+) = paaf.employment_category
and leave_reas.LOOKUP_TYPE (+) = ‘LEAV_REAS’
AND leave_reas.LOOKUP_CODE (+) = ppos.leaving_reason
–and pp.parent_id(+) = ppx.PERSON_ID
and pq.person_id(+) = ppx.person_id
and pqtv.qualification_type_id(+) = pq.qualification_type_id
and ppg.people_group_id = paaf.people_group_id
and paaf.organization_id = paaou.organization_id
and paaf.position_id = hapf.position_id (+)
and hapf.position_definition_id = ppd.position_definition_id (+)
and pg.grade_id(+) = paaf.grade_id
and fcl.LOOKUP_TYPE = ‘NATIONALITY’
AND fcl.LOOKUP_CODE(+) = ppx.NATIONALITY
and pca.collective_agreement_id(+) = paaf.collective_agreement_id
—- and to_number(to_char(paaf.effective_end_date,’yyyy’)) > 4000
and ppp.assignment_id = paaf.assignment_id
and ppp.change_date = (select max(ppp1.change_date)
from per_pay_proposals ppp1
where ppp1.assignment_id = ppp.assignment_id)
–FOR ACCTUAL ORGANIZATION
AND CA.COLLECTIVE_AGREEMENT_ID(+) = PAAF.COLLECTIVE_AGREEMENT_ID
and ppx.person_type_id = 1123
– AND sysdate between paaf.EFFECTIVE_START_DATE and paaf.EFFECTIVE_END_DATE
– AND sysdate between ppx.EFFECTIVE_START_DATE and ppx.EFFECTIVE_END_DATE
its giving non duplicate data fro single employee for giving employee parameter
but when i am removing the parameter then its displays only 16 rows instead of 1800+ rows should be displayed
sir
i want to create a disc for monthly salary report by using payroll group. could you please provide information about how to create it .
best regards
syed zubair
this is the report for which the disc to be prepared
SELECT ALL b.SESSION_ID, b.PERSON_ID, b.EMPLOYEE_NUMBER, b.FULL_NAME,
b.GRADE, b.GRADE_STEP, b.GRADE_SEQ, b.JOB, b.POSITION, b.PAYROLL_ID, b.PAYROLL_NAME,
b.PAID_PERIOD, b.PAYROLL_ACTION_ID, b.ELEMENT_TYPE_ID, b.ELEMENT_NAME,
b.ELEMENT_VAL1, b.ELEMENT_VAL2, b.ELEMENT_VAL3, b.ELEMENT_VAL4,
b.ELEMENT_VAL5, b.ELEMENT_VAL6, b.ELEMENT_VAL7, b.ELEMENT_VAL8, b.ELEMENT_VAL9,
b.ELEMENT_VAL10, b.ELEMENT_VAL11, b.ELEMENT_VAL12, b.ELEMENT_VAL13, b.ELEMENT_VAL14,
b.ELEMENT_VAL15, b.ELEMENT_VAL16, b.ELEMENT_VAL17, b.ELEMENT_VAL18,
b.ELEMENT_VAL19, b.ELEMENT_VAL20, b.ELEMENT_VAL21, b.ELEMENT_VAL22, b.GROUP_SEG1, b.GROUP_SEG2,
nprv.ELEMENT_VAL1 el1, nprv.ELEMENT_VAL2 el2, nprv.ELEMENT_VAL3 el3, nprv.ELEMENT_VAL4 el4,
nprv.ELEMENT_VAL5 el5, nprv.ELEMENT_VAL6 el6, nprv.ELEMENT_VAL7 el7, nprv.ELEMENT_VAL8 el8, nprv.ELEMENT_VAL9 el9,
nprv.ELEMENT_VAL10 el10, nprv.ELEMENT_VAL11 el11, nprv.ELEMENT_VAL12 el12, nprv.ELEMENT_VAL13 el13, nprv.ELEMENT_VAL14 el14,
nprv.ELEMENT_VAL15 el15, nprv.ELEMENT_VAL16 el16, nprv.ELEMENT_VAL17 el17, nprv.ELEMENT_VAL18 el18,
nprv.ELEMENT_VAL19 el19, nprv.ELEMENT_VAL20 el20, nprv.ELEMENT_VAL21 el21, nprv.ELEMENT_VAL22 el21
FROM XX_PAYROLL_GROUP b,
XX_PAYROLL_GROUP_nprv nprv
WHERE b.SESSION_ID = USERENV(‘SESSIONID’)
and nprv.person_id = b.person_id
and nprv.payroll_action_id = b.payroll_action_id
order by b.full_name
this is the great and informative site for apps dba, nice work. kindly post query to find the employees department name.
thanks
BR
Syed Hassan Khalique
use following “Select name from HR_ORGANIZATION_UNITS a where a.ORGANIZATION_ID=:p_org_id”.
Regards,
Faisal
hi,
faisal your query is not working.
hi,
i need to find the full name of the oracle ebs users, from where i find it, please help.
thanks
BR
SYED HASSAN
hi all,
i got one requirement from user,user want to display monthly gross pay for employees but parameters period form_date 01-jan-2015 and to_date 31-dec-2015 , i will display montly wise grosspay inforamtion
JAN FEB MAR APR JUN JUL AUG SEP OCT NOV DEC
20 20 30 20 20 20 30 20 20 20 20
didn’t compare above values just examples.
not include overtime amount above gross pay payroll, how can i achieve on this.
please help on this.
send me sample code