1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

a.count of empoloyees(5 sql scripts)

Discussion in 'Oracle HRMS & Payroll' started by nestor, Sep 16, 2010.

  1. nestor

    nestor Active Member

    Messages:
    28
    Likes Received:
    1
    Trophy Points:
    90
    Please help me in creating a n sql script for the following:
    a. Number employees per organization.
    b. Number of employee in payroll run per month and per organization.
    c. Number of employee in Payroll pre-payment process.
    d. Number of employee assigned salary
    e. Number of employee not assigned salary

    Thanks in advance.
     
  2. Sadik

    Sadik Community Moderator Forum Guru

    Messages:
    1,906
    Likes Received:
    252
    Trophy Points:
    1,455
    srinivas_apps likes this.
  3. nestor

    nestor Active Member

    Messages:
    28
    Likes Received:
    1
    Trophy Points:
    90
    Sir, i have already used that table. My big problem is on how to connect it to another table with a column of org_id or organization_id to satisfy the per organization condition.In short, in which table will i be joining per_all_people_f. Thanks
     
  4. Sadik

    Sadik Community Moderator Forum Guru

    Messages:
    1,906
    Likes Received:
    252
    Trophy Points:
    1,455
    I suggest you first go through the tables mentioned in the doc I linked you to.
     
  5. nestor

    nestor Active Member

    Messages:
    28
    Likes Received:
    1
    Trophy Points:
    90
    Here are the scripts i have created for this post. Please look at it and tell me if there is something i need to change. This scripts are running:
    A.
    Code (SQL):
    SELECT 'HQ', COUNT(1)
    FROM hr.per_all_assignments_f pa
       , hr.hr_all_organization_units_tl otl
    WHERE pa.organization_id = otl.organization_id
    AND otl.LANGUAGE = userenv('LANG')
    AND pa.primary_flag = 'Y'
    AND otl.name LIKE 'HO%'
    AND SYSDATE BETWEEN pa.effective_start_date AND pa.effective_end_date
    GROUP BY 'HQ'
    UNION
    SELECT 'RCBU', COUNT(1)
    FROM hr.per_all_assignments_f pa
       , hr.hr_all_organization_units_tl otl
    WHERE pa.organization_id = otl.organization_id
    AND otl.LANGUAGE = userenv('LANG')
    AND pa.primary_flag = 'Y'
    AND otl.name LIKE 'R%'
    AND SYSDATE BETWEEN pa.effective_start_date AND pa.effective_end_date
    GROUP BY 'RCBU'
    UNION
    SELECT 'JCBU', COUNT(1)
    FROM hr.per_all_assignments_f pa
       , hr.hr_all_organization_units_tl otl
    WHERE pa.organization_id = otl.organization_id
    AND otl.LANGUAGE = userenv('LANG')
    AND pa.primary_flag = 'Y'
    AND otl.name LIKE 'J%'
    AND SYSDATE BETWEEN pa.effective_start_date AND pa.effective_end_date
    GROUP BY 'JCBU'
    UNION
    SELECT 'TO_BE_CLASSIFIED', COUNT(1)
    FROM hr.per_all_assignments_f pa
       , hr.hr_all_organization_units_tl otl
    WHERE pa.organization_id = otl.organization_id
    AND otl.LANGUAGE = userenv('LANG')
    AND pa.primary_flag = 'Y'
    AND pa.organization_id = 81
    AND SYSDATE BETWEEN pa.effective_start_date AND pa.effective_end_date
    GROUP BY 'TO_BE_CLASSIFIED'
    B.
    Code (SQL):
    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
    C.
    Code (SQL):
    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
    D.
    Code (SQL):
    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
    E.
    Code (SQL):
    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
    Hoping to hear some review from the forumn.
    Thanks