Home » Applications » Oracle Fusion Apps & E-Business Suite » Query to find Number of employee in Payroll run per organization and others.. (Payroll R12)
Query to find Number of employee in Payroll run per organization and others.. [message #475960] Mon, 20 September 2010 01:16 Go to next message
nestor
Messages: 34
Registered: September 2010
Member
Please help me to create sql statements for the following.

A. Number of employee in Payroll run per organization per month for this year.

B. Number of employee in Payroll pre-payment process per organization per month for this year.

C. Number of employee assigned salary per organization.

D. Number of employee not assigned salary per organization.

Any help would be highly appreciated.

Thanks

Report message to a moderator

Re: Query to find Number of employee in Payroll run per organization and others.. [message #477312 is a reply to message #475960] Thu, 30 September 2010 01:48 Go to previous message
nestor
Messages: 34
Registered: September 2010
Member
I already created these query. Please find time to look and suggest if theres something i have missed from it.

A.
SELECT 'HQ'
     , TO_CHAR(ppa.effective_date,'YYYYMM')
     , COUNT(DISTINCT paaf.person_id)
FROM hr.pay_run_results  prr            
   , hr.pay_payroll_actions ppa         
   , hr.per_all_assignments_f paaf      
   , hr.pay_assignment_actions paa      
   , hr.pay_run_result_values prrv      
   , hr.pay_input_values_f pivf
   , hr.hr_all_organization_units_tl otl
WHERE prr.assignment_action_id = paa.assignment_action_id
AND paa.assignment_id = paaf.assignment_id
AND paa.payroll_action_id = ppa.payroll_action_id
AND TO_CHAR(ppa.effective_date, 'YYYY') = '2010'
AND paaf.primary_flag = 'Y'
AND paaf.organization_id = otl.organization_id
AND otl.language = userenv('LANG')
AND otl.name LIKE 'HO%'
AND paa.action_status = 'C'
AND prr.run_result_id = prrv.run_result_id
AND prrv.input_value_id = pivf.input_value_id
AND pivf.Name = 'Pay Value'
AND ppa.effective_date BETWEEN paaf.effective_start_date AND paaf.effective_end_date
AND ppa.effective_date BETWEEN pivf.effective_start_date AND pivf.effective_end_date
GROUP BY 'HQ', TO_CHAR(ppa.effective_date,'YYYYMM')
UNION
SELECT 'RCBU'
     , TO_CHAR(ppa.effective_date,'YYYYMM')
     , COUNT(DISTINCT paaf.person_id)
FROM hr.pay_run_results  prr            
   , hr.pay_payroll_actions ppa         
   , hr.per_all_assignments_f paaf      
   , hr.pay_assignment_actions paa      
   , hr.pay_run_result_values prrv      
   , hr.pay_input_values_f pivf         
   , hr.hr_all_organization_units_tl otl
WHERE prr.assignment_action_id = paa.assignment_action_id
AND paa.assignment_id = paaf.assignment_id
AND paa.payroll_action_id = ppa.payroll_action_id
AND TO_CHAR(ppa.effective_date, 'YYYY') = '2010'
AND paaf.primary_flag = 'Y'
AND paaf.organization_id = otl.organization_id
AND otl.language = userenv('LANG')
AND otl.name LIKE 'R%'
AND paa.action_status = 'C'
AND prr.run_result_id = prrv.run_result_id
AND prrv.input_value_id = pivf.input_value_id
AND pivf.Name = 'Pay Value'
AND ppa.effective_date BETWEEN paaf.effective_start_date AND paaf.effective_end_date
AND ppa.effective_date BETWEEN pivf.effective_start_date AND pivf.effective_end_date
GROUP BY 'RCBU', TO_CHAR(ppa.effective_date,'YYYYMM')
UNION
SELECT 'JCBU'
     , TO_CHAR(ppa.effective_date,'YYYYMM')
     , COUNT(DISTINCT paaf.person_id)
FROM hr.pay_run_results  prr            
   , hr.pay_payroll_actions ppa         
   , hr.per_all_assignments_f paaf      
   , hr.pay_assignment_actions paa      
   , hr.pay_run_result_values prrv      
   , hr.pay_input_values_f pivf         
   , hr.hr_all_organization_units_tl otl
WHERE prr.assignment_action_id = paa.assignment_action_id
AND paa.assignment_id = paaf.assignment_id
AND paa.payroll_action_id = ppa.payroll_action_id
AND TO_CHAR(ppa.effective_date, 'YYYY') = '2010'
AND paaf.primary_flag = 'Y'
AND paaf.organization_id = otl.organization_id
AND otl.language = userenv('LANG')
AND otl.name LIKE 'J%'
AND paa.action_status = 'C'
AND prr.run_result_id = prrv.run_result_id
AND prrv.input_value_id = pivf.input_value_id
AND pivf.Name = 'Pay Value'
AND ppa.effective_date BETWEEN paaf.effective_start_date AND paaf.effective_end_date
AND ppa.effective_date BETWEEN pivf.effective_start_date AND pivf.effective_end_date
GROUP BY 'JCBU', TO_CHAR(ppa.effective_date,'YYYYMM')
UNION
SELECT 'TO_BE_CLASSIFIED'
     , TO_CHAR(ppa.effective_date,'YYYYMM')
     , COUNT(DISTINCT paaf.person_id)
FROM hr.pay_run_results  prr            
   , hr.pay_payroll_actions ppa         
   , hr.per_all_assignments_f paaf      
   , hr.pay_assignment_actions paa      
   , hr.pay_run_result_values prrv      
   , hr.pay_input_values_f pivf         
   , hr.hr_all_organization_units_tl otl
WHERE prr.assignment_action_id = paa.assignment_action_id
AND paa.assignment_id = paaf.assignment_id
AND paa.payroll_action_id = ppa.payroll_action_id
AND TO_CHAR(ppa.effective_date, 'YYYY') = '2010'
AND paaf.primary_flag = 'Y'
AND paaf.organization_id = otl.organization_id
AND otl.language = userenv('LANG')
AND paaf.organization_id = 81
AND paa.action_status = 'C'
AND prr.run_result_id = prrv.run_result_id
AND prrv.input_value_id = pivf.input_value_id
AND pivf.Name = 'Pay Value'
AND ppa.effective_date BETWEEN paaf.effective_start_date AND paaf.effective_end_date
AND ppa.effective_date BETWEEN pivf.effective_start_date AND pivf.effective_end_date
GROUP BY 'TO_BE_CLASSIFIED', TO_CHAR(ppa.effective_date,'YYYYMM')
ORDER BY 1,2


B.
SELECT 'HQ'
     , TO_CHAR(ppa.effective_date,'YYYYMM')
     , COUNT(paaf.person_id)
FROM hr.pay_pre_payments ppp            
   , hr.pay_payroll_actions ppa         
   , hr.per_all_assignments_f paaf      
   , hr.pay_assignment_actions paa      
   , hr.hr_all_organization_units_tl otl
WHERE ppp.assignment_action_id = paa.assignment_action_id
AND paa.assignment_id = paaf.assignment_id
AND paa.payroll_action_id = ppa.payroll_action_id
AND TO_CHAR(ppa.effective_date, 'YYYY') = '2010'
AND paaf.primary_flag = 'Y'
AND paaf.organization_id = otl.organization_id
AND otl.language = userenv('LANG')
AND otl.name LIKE 'HO%'
AND ppa.effective_date BETWEEN paaf.effective_start_date AND paaf.effective_end_date
AND paa.action_status = 'C'
GROUP BY 'HQ', TO_CHAR(ppa.effective_date,'YYYYMM')
UNION
SELECT 'RCBU'
     , TO_CHAR(ppa.effective_date,'YYYYMM')
     , COUNT(paaf.person_id)
FROM hr.pay_pre_payments ppp            
   , hr.pay_payroll_actions ppa         
   , hr.per_all_assignments_f paaf      
   , hr.pay_assignment_actions paa
   , hr.hr_all_organization_units_tl otl
WHERE ppp.assignment_action_id = paa.assignment_action_id
AND paa.assignment_id = paaf.assignment_id
AND paa.payroll_action_id = ppa.payroll_action_id
AND TO_CHAR(ppa.effective_date, 'YYYY') = '2010'
AND paaf.primary_flag = 'Y'
AND paaf.organization_id = otl.organization_id
AND otl.language = userenv('LANG')
AND otl.name LIKE 'R%'
AND ppa.effective_date BETWEEN paaf.effective_start_date AND paaf.effective_end_date
AND paa.action_status = 'C'
GROUP BY 'RCBU', TO_CHAR(ppa.effective_date,'YYYYMM')
UNION
SELECT 'JCBU'
     , TO_CHAR(ppa.effective_date,'YYYYMM')
     , COUNT(paaf.person_id)
FROM hr.pay_pre_payments ppp            
   , hr.pay_payroll_actions ppa         
   , hr.per_all_assignments_f paaf      
   , hr.pay_assignment_actions paa      
   , hr.hr_all_organization_units_tl otl
WHERE ppp.assignment_action_id = paa.assignment_action_id
AND paa.assignment_id = paaf.assignment_id
AND paa.payroll_action_id = ppa.payroll_action_id
AND TO_CHAR(ppa.effective_date, 'YYYY') = '2010'
AND paaf.primary_flag = 'Y'
AND paaf.organization_id = otl.organization_id
AND otl.language = userenv('LANG')
AND otl.name LIKE 'J%'
AND ppa.effective_date BETWEEN paaf.effective_start_date AND paaf.effective_end_date
AND paa.action_status = 'C'
GROUP BY 'JCBU', TO_CHAR(ppa.effective_date,'YYYYMM')
UNION
SELECT 'TO_BE_CLASSIFIED'
     , TO_CHAR(ppa.effective_date,'YYYYMM')
     , COUNT(paaf.person_id)
FROM hr.pay_pre_payments ppp            
   , hr.pay_payroll_actions ppa         
   , hr.per_all_assignments_f paaf      
   , hr.pay_assignment_actions paa      
   , hr.hr_all_organization_units_tl otl
WHERE ppp.assignment_action_id = paa.assignment_action_id
AND paa.assignment_id = paaf.assignment_id
AND paa.payroll_action_id = ppa.payroll_action_id
AND TO_CHAR(ppa.effective_date, 'YYYY') = '2010'
AND paaf.primary_flag = 'Y'
AND paaf.organization_id = otl.organization_id
AND otl.language = userenv('LANG')
AND paAF.organization_id = 81
AND ppa.effective_date BETWEEN paaf.effective_start_date AND paaf.effective_end_date
AND paa.action_status = 'C'
GROUP BY 'TO_BE_CLASSIFIED', TO_CHAR(ppa.effective_date,'YYYYMM')
ORDER BY 1,2


C.
SELECT 'HQ', COUNT(1)
FROM hr.per_all_assignments_f paa
   , per_pay_proposals  ppp
   , hr.hr_all_organization_units_tl otl
WHERE paa.assignment_id = ppp.assignment_id(+)
AND ( ppp.change_date IS NULL OR
      ppp.change_date = (SELECT MAX (change_date)
                          FROM per_pay_proposals ppp2
                          WHERE ppp.assignment_id = ppp2.assignment_id
                          AND ppp2.approved = 'Y') )
AND SYSDATE BETWEEN paa.effective_start_date AND effective_end_date
AND paa.organization_id = otl.organization_id
AND otl.language = userenv('LANG')
AND paa.primary_flag = 'Y'
AND otl.name LIKE 'HO%'
AND ppp.proposed_salary_n IS NOT NULL
AND ppp.proposed_salary_n > 0
GROUP BY 'HQ'
UNION
SELECT 'RCBU', COUNT(1)
FROM hr.per_all_assignments_f paa
   , per_pay_proposals  ppp
   , hr.hr_all_organization_units_tl otl
WHERE paa.assignment_id = ppp.assignment_id(+)
AND ( ppp.change_date IS NULL OR
      ppp.change_date = (SELECT MAX (change_date)
                          FROM per_pay_proposals ppp2
                          WHERE ppp.assignment_id = ppp2.assignment_id
                          AND ppp2.approved = 'Y') )
AND SYSDATE BETWEEN paa.effective_start_date AND effective_end_date
AND paa.organization_id = otl.organization_id
AND otl.language = userenv('LANG')
AND paa.primary_flag = 'Y'
AND otl.name like 'R%'
AND ppp.proposed_salary_n IS NOT NULL
AND ppp.proposed_salary_n > 0
GROUP BY 'RCBU'
UNION
SELECT 'JCBU', COUNT(1)
FROM hr.per_all_assignments_f paa
   , per_pay_proposals  ppp
   , hr.hr_all_organization_units_tl otl
WHERE paa.assignment_id = ppp.assignment_id(+)
AND ( ppp.change_date IS NULL OR
      ppp.change_date = (SELECT MAX (change_date)
                          FROM per_pay_proposals ppp2
                          WHERE ppp.assignment_id = ppp2.assignment_id
                          AND ppp2.approved = 'Y') )
AND SYSDATE BETWEEN paa.effective_start_date AND effective_end_date
AND paa.organization_id = otl.organization_id
AND otl.language = userenv('LANG')
AND paa.primary_flag = 'Y'
AND otl.name like 'J%'
AND ppp.proposed_salary_n IS NOT NULL
AND ppp.proposed_salary_n > 0
GROUP BY 'JCBU'
UNION
SELECT 'TO_BE_CLASSIFIED', COUNT(1)
FROM hr.per_all_assignments_f paa
   , per_pay_proposals  ppp
   , hr.hr_all_organization_units_tl otl
WHERE paa.assignment_id = ppp.assignment_id(+)
AND ( ppp.change_date IS NULL OR
      ppp.change_date = (SELECT MAX (change_date)
                          FROM per_pay_proposals ppp2
                          WHERE ppp.assignment_id = ppp2.assignment_id
                          AND ppp2.approved = 'Y') )
AND SYSDATE BETWEEN paa.effective_start_date AND effective_end_date
AND paa.organization_id = otl.organization_id
AND otl.language = userenv('LANG')
AND paa.primary_flag = 'Y'
AND paa.organization_id = 81
AND ppp.proposed_salary_n IS NOT NULL
AND ppp.proposed_salary_n > 0
GROUP BY 'TO_BE_CLASSIFIED'
ORDER BY 1


D.
SELECT 'HQ', COUNT(1)
FROM hr.per_all_assignments_f paa
   , per_pay_proposals  ppp
   , hr.hr_all_organization_units_tl otl
WHERE paa.assignment_id = ppp.assignment_id(+)
AND ( ppp.change_date IS NULL OR
      ppp.change_date = (SELECT MAX (change_date)
                          FROM per_pay_proposals ppp2
                          WHERE ppp.assignment_id = ppp2.assignment_id
                          AND ppp2.approved = 'Y') )
AND SYSDATE BETWEEN paa.effective_start_date AND effective_end_date
AND paa.organization_id = otl.organization_id
AND otl.language = userenv('LANG')
AND paa.primary_flag = 'Y'
AND otl.name LIKE 'HO%'
AND ( ppp.proposed_salary_n IS NULL OR ppp.proposed_salary_n = 0 )
GROUP BY 'HQ'
UNION
SELECT 'RCBU', COUNT(1)
FROM hr.per_all_assignments_f paa
   , per_pay_proposals  ppp
   , hr.hr_all_organization_units_tl otl
WHERE paa.assignment_id = ppp.assignment_id(+)
AND ( ppp.change_date IS NULL OR
      ppp.change_date = (SELECT MAX (change_date)
                          FROM per_pay_proposals ppp2
                          WHERE ppp.assignment_id = ppp2.assignment_id
                          AND ppp2.approved = 'Y') )
AND SYSDATE BETWEEN paa.effective_start_date AND effective_end_date
AND paa.organization_id = otl.organization_id
AND otl.language = userenv('LANG')
AND paa.primary_flag = 'Y'
AND otl.name LIKE 'R%'
AND ( ppp.proposed_salary_n IS NULL OR ppp.proposed_salary_n = 0 )
GROUP BY 'RCBU'
UNION
SELECT 'JCBU', COUNT(1)
FROM hr.per_all_assignments_f paa
   , per_pay_proposals  ppp
   , hr.hr_all_organization_units_tl otl
WHERE paa.assignment_id = ppp.assignment_id(+)
AND ( ppp.change_date IS NULL OR
      ppp.change_date = (SELECT MAX (change_date)
                          FROM per_pay_proposals ppp2
                          WHERE ppp.assignment_id = ppp2.assignment_id
                          AND ppp2.approved = 'Y') )
AND SYSDATE BETWEEN paa.effective_start_date AND effective_end_date
AND paa.organization_id = otl.organization_id
AND otl.language = userenv('LANG')
AND paa.primary_flag = 'Y'
AND otl.name LIKE 'J%'
AND ( ppp.proposed_salary_n IS NULL OR ppp.proposed_salary_n = 0 )
GROUP BY 'JCBU'
UNION
SELECT 'TO_BE_CLASSIFIED', COUNT(1)
FROM hr.per_all_assignments_f paa
   , per_pay_proposals  ppp
   , hr.hr_all_organization_units_tl otl
WHERE paa.assignment_id = ppp.assignment_id(+)
AND ( ppp.change_date IS NULL OR
      ppp.change_date = (SELECT MAX (change_date)
                          FROM per_pay_proposals ppp2
                          WHERE ppp.assignment_id = ppp2.assignment_id
                          AND ppp2.approved = 'Y') )
AND SYSDATE BETWEEN paa.effective_start_date AND effective_end_date
AND paa.organization_id = otl.organization_id
AND otl.language = userenv('LANG')
AND paa.primary_flag = 'Y'
AND paa.organization_id = 81
AND ( ppp.proposed_salary_n IS NULL OR ppp.proposed_salary_n = 0 )
GROUP BY 'TO_BE_CLASSIFIED'
ORDER BY 1

Report message to a moderator

Previous Topic: Send Separate Remittance
Next Topic: HR Person API Update
Goto Forum:
  

[ Syndicate this forum (XML) ] [ RSS ]

Current Time: Fri May 03 08:07:43 CDT 2024
.:: Forum Home :: Blogger Home :: Wiki Home :: Contact :: Privacy ::.