Just want to share the query I have worked on to extract all the Employee Payroll Elements and Pay Elements generated dynamically for an Employee on a single Pay Period June'2012:
*******************************************************************************************************
SELECT person_id, employee_number, assignment_id, full_name, position_number, position_name, sex, ethnicity, years, employment_category
-- ,actual_fte
,fte
,actual_hours, reporting_name, team, service, MAX(NVL(BASIC_PAY , 0)) "BASIC_PAY",SUM(NVL(ADDITIONAL_PAY, 0)) "ADDITIONAL_PAY"
,MAX(NVL(BASIC_PAY , 0)) + SUM(NVL(ADDITIONAL_PAY, 0)) "GROSS_PAY"
,MAX(GROSS_PAY) "NET_PAY"
FROM (
SELECT papf.person_id, papf.employee_number, paaf.assignment_id, papf.full_name, SUBSTR(pap.name, 1, INSTR(pap.name, '.')-1) "POSITION_NUMBER"
,SUBSTR(pap.name, INSTR(pap.name, '.') + 1) "POSITION_NAME", papf.sex, flv.meaning "ETHNICITY"
,trunc((to_date('01-JUN-2012', 'DD-MON-RRRR') - papf.date_of_birth)/365) "YEARS"
,flv1.meaning "EMPLOYMENT_CATEGORY"
-- ,wcev.fte "ACTUAL_FTE"
,pabf.value "FTE"
,DECODE(NVL(paaf.normal_hours, 0) , 0 , NVL(otlh.total_hours, 0) * 4.34, paaf.normal_hours) "ACTUAL_HOURS"
,NVL(wpbc.basic_salary, 0) "BASIC_PAY"
,NVL(wpbc1.basic_salary, 0) "ADDITIONAL_PAY"
,wpbc1.reporting_name "REPORTING_NAME"
,NVL(bp1.gross_pay, 0) "GROSS_PAY"
,DECODE(pap.position_id, 10297, 'CE Office', whg.name) "TEAM"
,DECODE(pap.position_id, 10297, WBC_SERVICE_UNIT_NAME(83), whg.name) "SERVICE"
FROM per_all_people_f papf
,per_all_assignments_f paaf
,per_all_positions pap
,fnd_lookup_types flt
,fnd_lookup_values flv
,fnd_lookup_types flt1
,fnd_lookup_values flv1
,wbc_otl_hours_v otlh
,per_assignment_budget_values_f pabf
-- ,wdc_current_establishment_v wcev
,wbc_hr_org_information_mv whg
,(SELECT prb1.assignment_id, MAX( NVL( DECODE(defined_balance_id, 163, NVL(balance_value, 0), 0), 0) ) "GROSS_PAY"
FROM pay_run_balances prb1
,gl_period_statuses gps1
WHERE /*prb1.defined_balance_id in (163, 166, 238)
AND */gps1.application_id = 101
AND prb1.effective_date = gps1.end_date
AND gps1.period_name = 'Jun-12'
GROUP BY prb1.assignment_id) bp1
,(SELECT pasa.assignment_id "ASSIGNMENT_ID"
, petf.element_name "ELEMENT_NAME"
, petf.reporting_name "REPORTING_NAME"
, petf.description "DESCRIPTION"
, pivf.name "NAME"
, SUM(prrv.result_value) "BASIC_SALARY"
FROM pay_run_types_f prt
,pay_run_results prrt
,pay_run_result_values prrv
,pay_input_values_f pivf
,pay_payroll_actions papa
,pay_assignment_actions pasa
,pay_element_types_f petf
WHERE prrt.assignment_action_id = pasa.assignment_action_id
and prrt.element_type_id = petf.element_type_id
and pasa.run_type_id = prt.run_type_id
and prrv.run_result_id = prrt.run_result_id
and prrv.input_value_id = pivf.input_value_id
and trunc(papa.effective_date) between trunc(pivf.effective_start_date) and trunc(pivf.effective_end_date)
and trunc(papa.effective_date) between trunc(petf.effective_start_date) and trunc(petf.effective_end_date)
and papa.business_group_id = petf.business_group_id
and papa.business_group_id = pivf.business_group_id
and to_char(papa.effective_date, 'DD-MON-RRRR') = '30-JUN-2012'
and papa.business_group_id = &business_group_id
and papa.payroll_id = &Payroll_id
and papa.action_status = 'C'
and papa.action_type = 'R'
and pasa.payroll_action_id = papa.payroll_action_id
and pivf.name = 'Pay Value'
and petf.reporting_name = 'Basic Salary'
GROUP BY assignment_id
, petf.element_name
, petf.reporting_name
, petf.description
, pivf.name ) wpbc
,(SELECT pasa.assignment_id "ASSIGNMENT_ID"
, petf.element_name "ELEMENT_NAME"
, petf.reporting_name "REPORTING_NAME"
, petf.description "DESCRIPTION"
, pivf.name "NAME"
, prrv.result_value "BASIC_SALARY"
FROM pay_run_types_f prt
,pay_run_results prrt
,pay_run_result_values prrv
,pay_input_values_f pivf
,pay_payroll_actions papa
,pay_assignment_actions pasa
,pay_element_types_f petf
,pay_element_classifications pecl
WHERE prrt.assignment_action_id = pasa.assignment_action_id
and prrt.element_type_id = petf.element_type_id
and pasa.run_type_id = prt.run_type_id
and prrv.run_result_id = prrt.run_result_id
and prrv.input_value_id = pivf.input_value_id
and trunc(papa.effective_date) between trunc(pivf.effective_start_date) and trunc(pivf.effective_end_date)
and trunc(papa.effective_date) between trunc(petf.effective_start_date) and trunc(petf.effective_end_date)
and papa.business_group_id = petf.business_group_id
and papa.business_group_id = pivf.business_group_id
and to_char(papa.effective_date, 'DD-MON-RRRR') = '30-JUN-2012'
and papa.business_group_id = &business_group_id
and papa.payroll_id = &Payroll_id
and papa.action_status = 'C'
and papa.action_type = 'R'
and pasa.payroll_action_id = papa.payroll_action_id
and pivf.name = 'Pay Value'
and petf.classification_id = pecl.classification_id
and pecl.classification_name = 'Earnings'
and pecl.legislation_code = 'GB'
and petf.reporting_name <> 'Basic Salary') wpbc1
--,wbc_user_assignments wua
WHERE /*papf.person_id = wua.person_id
AND wua.assignment_id = wpbc.assignment_id(+)
and */paaf.assignment_id = wpbc.assignment_id(+)
AND paaf.assignment_id = wpbc1.assignment_id(+)
AND papf.person_id = otlh.resource_id(+)
AND trunc(to_date('30-JUN-2012', 'DD-MON-RRRR')) between trunc(papf.effective_start_date) AND trunc(NVL(papf.effective_end_date, sysdate))
AND trunc(to_date('30-JUN-2012', 'DD-MON-RRRR')) between trunc(paaf.effective_start_date) AND trunc(NVL(paaf.effective_end_date, sysdate))
AND papf.person_id = paaf.person_id
AND paaf.position_id = pap.position_id
-- and wcev.person_id = papf.person_id
-- and wcev.assignment_id = paaf.assignment_id
and paaf.organization_id = pap.organization_id
and trunc(to_date('30-JUN-2012', 'DD-MON-RRRR')) between trunc(pap.date_effective) and trunc(NVL(pap.date_end, sysdate))
and paaf.job_id = pap.job_id
and paaf.business_group_id = pap.business_group_id
and flv.lookup_code = papf.per_information1
and flt.lookup_type = 'ETH_TYPE'
and flt.lookup_type = flv.lookup_type
and flv1.lookup_code = paaf.employment_category
and flt1.lookup_type = 'EMP_CAT'
and flt1.lookup_type = flv1.lookup_type
and paaf.assignment_id = bp1.assignment_id(+)
and paaf.organization_id = whg.org_id
and paaf.assignment_id = pabf.assignment_id
and pabf.unit = 'FTE'
and trunc(to_date('30-JUN-2012', 'DD-MON-RRRR')) between trunc(pabf.effective_start_date) and trunc(pabf.effective_end_date)
)
GROUP BY person_id, employee_number, assignment_id, full_name, position_number, position_name, sex, ethnicity, years, employment_category
-- ,actual_fte
,fte
,actual_hours ,reporting_name , team, service)
*******************************************************************************************************
Print This Post
*******************************************************************************************************
SELECT person_id, employee_number, assignment_id, full_name, position_number, position_name, sex, ethnicity, years, employment_category
-- ,actual_fte
,fte
,actual_hours, reporting_name, team, service, MAX(NVL(BASIC_PAY , 0)) "BASIC_PAY",SUM(NVL(ADDITIONAL_PAY, 0)) "ADDITIONAL_PAY"
,MAX(NVL(BASIC_PAY , 0)) + SUM(NVL(ADDITIONAL_PAY, 0)) "GROSS_PAY"
,MAX(GROSS_PAY) "NET_PAY"
FROM (
SELECT papf.person_id, papf.employee_number, paaf.assignment_id, papf.full_name, SUBSTR(pap.name, 1, INSTR(pap.name, '.')-1) "POSITION_NUMBER"
,SUBSTR(pap.name, INSTR(pap.name, '.') + 1) "POSITION_NAME", papf.sex, flv.meaning "ETHNICITY"
,trunc((to_date('01-JUN-2012', 'DD-MON-RRRR') - papf.date_of_birth)/365) "YEARS"
,flv1.meaning "EMPLOYMENT_CATEGORY"
-- ,wcev.fte "ACTUAL_FTE"
,pabf.value "FTE"
,DECODE(NVL(paaf.normal_hours, 0) , 0 , NVL(otlh.total_hours, 0) * 4.34, paaf.normal_hours) "ACTUAL_HOURS"
,NVL(wpbc.basic_salary, 0) "BASIC_PAY"
,NVL(wpbc1.basic_salary, 0) "ADDITIONAL_PAY"
,wpbc1.reporting_name "REPORTING_NAME"
,NVL(bp1.gross_pay, 0) "GROSS_PAY"
,DECODE(pap.position_id, 10297, 'CE Office', whg.name) "TEAM"
,DECODE(pap.position_id, 10297, WBC_SERVICE_UNIT_NAME(83), whg.name) "SERVICE"
FROM per_all_people_f papf
,per_all_assignments_f paaf
,per_all_positions pap
,fnd_lookup_types flt
,fnd_lookup_values flv
,fnd_lookup_types flt1
,fnd_lookup_values flv1
,wbc_otl_hours_v otlh
,per_assignment_budget_values_f pabf
-- ,wdc_current_establishment_v wcev
,wbc_hr_org_information_mv whg
,(SELECT prb1.assignment_id, MAX( NVL( DECODE(defined_balance_id, 163, NVL(balance_value, 0), 0), 0) ) "GROSS_PAY"
FROM pay_run_balances prb1
,gl_period_statuses gps1
WHERE /*prb1.defined_balance_id in (163, 166, 238)
AND */gps1.application_id = 101
AND prb1.effective_date = gps1.end_date
AND gps1.period_name = 'Jun-12'
GROUP BY prb1.assignment_id) bp1
,(SELECT pasa.assignment_id "ASSIGNMENT_ID"
, petf.element_name "ELEMENT_NAME"
, petf.reporting_name "REPORTING_NAME"
, petf.description "DESCRIPTION"
, pivf.name "NAME"
, SUM(prrv.result_value) "BASIC_SALARY"
FROM pay_run_types_f prt
,pay_run_results prrt
,pay_run_result_values prrv
,pay_input_values_f pivf
,pay_payroll_actions papa
,pay_assignment_actions pasa
,pay_element_types_f petf
WHERE prrt.assignment_action_id = pasa.assignment_action_id
and prrt.element_type_id = petf.element_type_id
and pasa.run_type_id = prt.run_type_id
and prrv.run_result_id = prrt.run_result_id
and prrv.input_value_id = pivf.input_value_id
and trunc(papa.effective_date) between trunc(pivf.effective_start_date) and trunc(pivf.effective_end_date)
and trunc(papa.effective_date) between trunc(petf.effective_start_date) and trunc(petf.effective_end_date)
and papa.business_group_id = petf.business_group_id
and papa.business_group_id = pivf.business_group_id
and to_char(papa.effective_date, 'DD-MON-RRRR') = '30-JUN-2012'
and papa.business_group_id = &business_group_id
and papa.payroll_id = &Payroll_id
and papa.action_status = 'C'
and papa.action_type = 'R'
and pasa.payroll_action_id = papa.payroll_action_id
and pivf.name = 'Pay Value'
and petf.reporting_name = 'Basic Salary'
GROUP BY assignment_id
, petf.element_name
, petf.reporting_name
, petf.description
, pivf.name ) wpbc
,(SELECT pasa.assignment_id "ASSIGNMENT_ID"
, petf.element_name "ELEMENT_NAME"
, petf.reporting_name "REPORTING_NAME"
, petf.description "DESCRIPTION"
, pivf.name "NAME"
, prrv.result_value "BASIC_SALARY"
FROM pay_run_types_f prt
,pay_run_results prrt
,pay_run_result_values prrv
,pay_input_values_f pivf
,pay_payroll_actions papa
,pay_assignment_actions pasa
,pay_element_types_f petf
,pay_element_classifications pecl
WHERE prrt.assignment_action_id = pasa.assignment_action_id
and prrt.element_type_id = petf.element_type_id
and pasa.run_type_id = prt.run_type_id
and prrv.run_result_id = prrt.run_result_id
and prrv.input_value_id = pivf.input_value_id
and trunc(papa.effective_date) between trunc(pivf.effective_start_date) and trunc(pivf.effective_end_date)
and trunc(papa.effective_date) between trunc(petf.effective_start_date) and trunc(petf.effective_end_date)
and papa.business_group_id = petf.business_group_id
and papa.business_group_id = pivf.business_group_id
and to_char(papa.effective_date, 'DD-MON-RRRR') = '30-JUN-2012'
and papa.business_group_id = &business_group_id
and papa.payroll_id = &Payroll_id
and papa.action_status = 'C'
and papa.action_type = 'R'
and pasa.payroll_action_id = papa.payroll_action_id
and pivf.name = 'Pay Value'
and petf.classification_id = pecl.classification_id
and pecl.classification_name = 'Earnings'
and pecl.legislation_code = 'GB'
and petf.reporting_name <> 'Basic Salary') wpbc1
--,wbc_user_assignments wua
WHERE /*papf.person_id = wua.person_id
AND wua.assignment_id = wpbc.assignment_id(+)
and */paaf.assignment_id = wpbc.assignment_id(+)
AND paaf.assignment_id = wpbc1.assignment_id(+)
AND papf.person_id = otlh.resource_id(+)
AND trunc(to_date('30-JUN-2012', 'DD-MON-RRRR')) between trunc(papf.effective_start_date) AND trunc(NVL(papf.effective_end_date, sysdate))
AND trunc(to_date('30-JUN-2012', 'DD-MON-RRRR')) between trunc(paaf.effective_start_date) AND trunc(NVL(paaf.effective_end_date, sysdate))
AND papf.person_id = paaf.person_id
AND paaf.position_id = pap.position_id
-- and wcev.person_id = papf.person_id
-- and wcev.assignment_id = paaf.assignment_id
and paaf.organization_id = pap.organization_id
and trunc(to_date('30-JUN-2012', 'DD-MON-RRRR')) between trunc(pap.date_effective) and trunc(NVL(pap.date_end, sysdate))
and paaf.job_id = pap.job_id
and paaf.business_group_id = pap.business_group_id
and flv.lookup_code = papf.per_information1
and flt.lookup_type = 'ETH_TYPE'
and flt.lookup_type = flv.lookup_type
and flv1.lookup_code = paaf.employment_category
and flt1.lookup_type = 'EMP_CAT'
and flt1.lookup_type = flv1.lookup_type
and paaf.assignment_id = bp1.assignment_id(+)
and paaf.organization_id = whg.org_id
and paaf.assignment_id = pabf.assignment_id
and pabf.unit = 'FTE'
and trunc(to_date('30-JUN-2012', 'DD-MON-RRRR')) between trunc(pabf.effective_start_date) and trunc(pabf.effective_end_date)
)
GROUP BY person_id, employee_number, assignment_id, full_name, position_number, position_name, sex, ethnicity, years, employment_category
-- ,actual_fte
,fte
,actual_hours ,reporting_name , team, service)
*******************************************************************************************************
I'm here to share my testimony of what a good trusted loan company did for me. My name is Nikita Tanya, from Russian and I’m a lovely mother of 3 kids I lost my funds on trying to get a loan it was so hard for me and my children, I went online to seek for a loan assistance all hope was lost until one faithful day when I met this friend of mine who recently secured a loan from Le_Meridian Funding Service She introduced me to this honest loan company who helped me get a loan in within 5 working days, I will forever be grateful to Mr Benjamin, for helping me get back on feet again. You can contact Mr Benjamin via email: lfdsloans@lemeridianfds.com, they do not know I’m doing this for them, but i just have to do it because a lot of people are out there who are in need of a loan assistance please come to this company and be saved.WhatsApp:(+1 989-394-3740)
ReplyDelete