Oracle Transactional Business Intelligence

Home› Oracle Analytics› Oracle Analytics Forums› More Solutions› Oracle Transactional Business Intelligence

Categories

Welcome to the Oracle Analytics Community: Please complete your User Profile and upload your Profile Picture

Help with an OTBI or SQL query to produce a list of all job codes with Grade information

Received Response
1799
Views
16
Comments
2»

Answers

  • PamS
    PamS Rank 3 - Community Apprentice

    HI Mandeep,

    I need to show changes in the Job Family Name as well as the job name, Set name, grade

    In the per_jobs_f table, I have records with more than one job_family_id

    I tried adding it to the query for the jobs

     (

        SELECT

          pjf.job_code,

          pjf.attribute1,

          pjf.set_id,

          pjf.job_id,

          pjf.active_status,

          pjf.job_family_id,

          pjft.name,

          pjf.effective_start_date,

          pjf.effective_end_date,

          setid_lu.set_name AS job_set,

          pjftl.job_family_name

        FROM

          per_jobs_f      pjf,

          per_jobs_f_tl    pjft,

          fnd_setid_sets_vl  setid_lu,

          per_job_family_f_tl pjftl

        WHERE

            pjf.job_id = pjft.job_id

          AND pjft.language = 'US'

          AND setid_lu.set_id = pjf.set_id

          AND pjftl.job_family_id = pjf.job_family_id

      )          pjfv,

    but I get an error

    when I try to run the query

    Can you see what I have wrong in the query?

    Thanks

    Pam

  • MandeepGupta
    MandeepGupta Rank 6 - Analytics Lead

    In the main select, you will need to change below:

     pjftl.job_family_name -->  pjfv.job_family_name

    Thanks.

  • PamS
    PamS Rank 3 - Community Apprentice

    Thanks Mandeep

    That definitely got rid of my error:-)

    I'm beginning to think I can't get all the changes in one query

    For my test case, only the job family name changed - no change to Job or Grade

    Ultimately I would want to see the 2 records with the Job Family Name = Administration and 2 records with Corporate Communications

    any ideas?

    Thanks again

    Pam

  • MandeepGupta
    MandeepGupta Rank 6 - Analytics Lead

    Hi Pam,

    Please check if below helps:

    SELECT DISTINCT

    to_char(pjfv.effective_start_date, 'dd/mm/yyyy')    effective_start_date,

    to_char(pjfv.effective_end_date, 'dd/mm/yyyy')    effective_end_date,

    pjfv.job_code job_code,

    pjfv.name         job_name,

    pjfv.attribute1       comp_group,

    pjfv.set_id         job_set_id,

    decode(pjfv.active_status, 'A', 'Active', 'I', 'Inactive')  active_status,

    pjfv.job_family_name job_family_name,

    pgfv.grade_code grade_code, 

    pgfv.name         grade_name,

    pvgf.valid_grade_id,

    pgfv.grade_id,

    pjfv.job_id

      FROM per_valid_grades_f pvgf,

        (select pjf.job_code

           ,pjf.attribute1

           ,pjf.set_id

           ,pjf.job_id

           ,pjf.active_status

           ,pjf.job_family_id

           ,pjft.name

           ,pjf.effective_start_date  

           ,pjf.effective_end_date

     ,pjftl.job_family_name

         from per_jobs_f pjf,

           per_jobs_f_tl pjft,

     fnd_setid_sets_vl setid_lu,

     per_job_family_f_tl pjftl

        where pjf.job_id = pjft.job_id

         and pjft.language = 'US'

     and setid_lu.set_id = pjf.set_id

         and pjftl.job_family_id = pjf.job_family_id

     and pjftl.language = 'US'

     and pjf.effective_start_date BETWEEN pjftl.effective_start_date AND pjftl.effective_end_date) pjfv,

    per_grades_f_vl pgfv

    WHERE 1 = 1

     AND pvgf.job_id = pjfv.job_id

     -- AND pjfv.job_id IN ( 300000004122446, 300000004122240, 300000004122999, 300000004140251 )

     AND pvgf.grade_id = pgfv.grade_id

     AND pjfv.effective_start_date BETWEEN pgfv.effective_start_date AND pgfv.effective_end_date

     AND pjfv.effective_start_date BETWEEN pvgf.effective_start_date AND pvgf.effective_end_date

     AND pjfv.effective_end_date >= trunc(sysdate) 

    ORDER BY job_code, grade_code

    Thanks.

  • PamS
    PamS Rank 3 - Community Apprentice

    Hi Mandeep,

    I am reviewing the results from the above query but things are looking promising

    Thanks so much for all the help

    Pam

  • MandeepGupta
    MandeepGupta Rank 6 - Analytics Lead

    Hi @PamS,

    Did the above query work as expected?

    Thanks.