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
PamS
PamS Rank 3 - Community Apprentice
in Oracle Transactional Business Intelligence

Summary:

Hi All,

I am hoping someone can help me - I have been struggling with a report request for a while now and I didn't think it was going to be this hard

Our business users have asked for a list of all Jobs with their grade information (active and inactive) -this includes any future transactions as well.

I created an Analysis using Workforce Management - Worker Assignment Event Real Time pulling data from Job, Job-Additional Attributes, Grade, Grade-Additional Attributes

At first the report appeared to be working EXCEPT it doesn't show the future transactions

For example - in the case of a Grade Update - the change was made effective Dec 1 but the future record isn't showing and a previous grade update is not showing

How do I capture all changes to a Job/Grades

I also tried to create a SQL to do the same thing but I must be missing a table or to because I can't seem to link the information correctly.

I tried starting with the per_valid_grades_f table as it seems to be the only 1 that I could find that contains both the Job id and the grade id

For my test record - there are 4 records in this table

then I tried to pull in the job information from per_jobs_f (has 3 records) but whenever I attempt to link by job_id and effective_start_date - I end up with 12 records

Sorry for the long winded request but if anyone could help that would be great.

Content (required):

OTBI or SQL query

Version (include the version you are using, if applicable):

21D

Code Snippet (add any code snippets that support your topic, if applicable):

XML

<saw:report xmlns:saw="com.siebel.analytics.web/report/v1.1" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:sawx="com.siebel.analytics.web/expression/v1.1" xmlVersion="201201160">

  <saw:criteria xsi:type="saw:simpleCriteria" subjectArea="&quot;Workforce Management - Worker Assignment Event Real Time&quot;" withinHierarchy="true">

   <saw:columns>

     <saw:column xsi:type="saw:regularColumn" columnID="cdec5ac7a8c3d0f23">

      <saw:columnFormula>

        <sawx:expr xsi:type="sawx:sqlExpression">"Job"."Job Code"</sawx:expr></saw:columnFormula>

      <saw:displayFormat>

        <saw:formatSpec suppress="repeat" wrapText="false"/></saw:displayFormat>

      <saw:tableHeading>

        <saw:caption fmt="text">

         <saw:text>Job</saw:text></saw:caption></saw:tableHeading>

      <saw:columnHeading>

        <saw:displayFormat>

         <saw:formatSpec/></saw:displayFormat>

        <saw:caption fmt="text">

         <saw:text>Job Code</saw:text></saw:caption></saw:columnHeading></saw:column>

     <saw:column xsi:type="saw:regularColumn" columnID="cf7102afbafb19d19">

      <saw:columnFormula>

        <sawx:expr xsi:type="sawx:sqlExpression">cast("Job - Additional Attributes"."Job Last Update Date" as Date)</sawx:expr></saw:columnFormula>

      <saw:displayFormat>

        <saw:formatSpec suppress="repeat" wrapText="true"/></saw:displayFormat>

      <saw:tableHeading>

        <saw:caption fmt="text">

         <saw:text>Job - Additional Attributes</saw:text></saw:caption></saw:tableHeading>

      <saw:columnHeading>

        <saw:displayFormat>

         <saw:formatSpec/></saw:displayFormat>

        <saw:caption fmt="text">

         <saw:text>Job Last Update Date</saw:text></saw:caption></saw:columnHeading></saw:column>

     <saw:column xsi:type="saw:regularColumn" columnID="ccff65b4dd9c5cf36">

      <saw:columnFormula>

        <sawx:expr xsi:type="sawx:sqlExpression">cast("Grade - Additional Attributes"."Grade Last Update Date" as Date)</sawx:expr></saw:columnFormula>

      <saw:displayFormat>

        <saw:formatSpec suppress="repeat" wrapText="true"/></saw:displayFormat>

      <saw:tableHeading>

        <saw:caption fmt="text">

         <saw:text>Grade - Additional Attributes</saw:text></saw:caption></saw:tableHeading>

      <saw:columnHeading>

        <saw:displayFormat>

         <saw:formatSpec/></saw:displayFormat>

        <saw:caption fmt="text">

         <saw:text>Grade Last Update Date</saw:text></saw:caption></saw:columnHeading></saw:column>

     <saw:column xsi:type="saw:regularColumn" columnID="ccff65b4dd9c5cf36_d1">

      <saw:columnFormula>

        <sawx:expr xsi:type="sawx:sqlExpression">"Grade"."Grade Type"</sawx:expr></saw:columnFormula>

      <saw:displayFormat>

        <saw:formatSpec visibility="hidden" suppress="suppress" wrapText="true"/></saw:displayFormat>

      <saw:columnHeading>

        <saw:displayFormat>

         <saw:formatSpec/></saw:displayFormat></saw:columnHeading></saw:column>

     <saw:column xsi:type="saw:regularColumn" columnID="ccff65b4dd9c5cf36_d2">

      <saw:columnFormula>

        <sawx:expr xsi:type="sawx:sqlExpression">"Grade"."Grade Code"</sawx:expr></saw:columnFormula></saw:column>

     <saw:column xsi:type="saw:regularColumn" columnID="ccff65b4dd9c5cf36_d3">

      <saw:columnFormula>

        <sawx:expr xsi:type="sawx:sqlExpression">"Grade"."Grade Name"</sawx:expr></saw:columnFormula></saw:column>

     <saw:column xsi:type="saw:regularColumn" columnID="c4e6f0d721f4642da">

      <saw:columnFormula>

        <sawx:expr xsi:type="sawx:sqlExpression">"Assignment Event"."# Of Hires"</sawx:expr></saw:columnFormula>

      <saw:displayFormat>

        <saw:formatSpec suppress="repeat" wrapText="true" visibility="hidden">

         <saw:dataFormat xsi:type="saw:number" commas="false" negativeType="minus" minDigits="0" maxDigits="0"/></saw:formatSpec></saw:displayFormat>

      <saw:columnHeading>

        <saw:displayFormat>

         <saw:formatSpec/></saw:displayFormat></saw:columnHeading></saw:column>

     <saw:column xsi:type="saw:regularColumn" columnID="c6fadc1ae13c65912">

      <saw:columnFormula>

        <sawx:expr xsi:type="sawx:sqlExpression">"Grade"."Set Name"</sawx:expr></saw:columnFormula>

      <saw:displayFormat>

        <saw:formatSpec suppress="repeat" wrapText="false"/></saw:displayFormat>

      <saw:columnHeading>

        <saw:displayFormat>

         <saw:formatSpec/></saw:displayFormat></saw:columnHeading></saw:column>

     <saw:column xsi:type="saw:regularColumn" columnID="c6c9784080c98edad">

      <saw:columnFormula>

        <sawx:expr xsi:type="sawx:sqlExpression">"Job"."Job Name"</sawx:expr></saw:columnFormula>

      <saw:displayFormat>

        <saw:formatSpec suppress="repeat" wrapText="false"/></saw:displayFormat>

      <saw:tableHeading>

        <saw:caption fmt="text">

         <saw:text>Job</saw:text></saw:caption></saw:tableHeading>

      <saw:columnHeading>

        <saw:displayFormat>

         <saw:formatSpec/></saw:displayFormat>

        <saw:caption fmt="text">

         <saw:text>Job Title</saw:text></saw:caption></saw:columnHeading></saw:column>

     <saw:column xsi:type="saw:regularColumn" columnID="ca3867f221958bb25">

      <saw:columnFormula>

        <sawx:expr xsi:type="sawx:sqlExpression">cast("Job"."Effective End Date" as date)</sawx:expr></saw:columnFormula>

      <saw:displayFormat>

        <saw:formatSpec suppress="repeat" wrapText="false"/></saw:displayFormat>

      <saw:tableHeading>

        <saw:caption fmt="text">

         <saw:text>Job</saw:text></saw:caption></saw:tableHeading>

      <saw:columnHeading>

        <saw:displayFormat>

         <saw:formatSpec/></saw:displayFormat>

        <saw:caption fmt="text">

         <saw:text>Job Effective End Date</saw:text></saw:caption></saw:columnHeading></saw:column>

     <saw:column xsi:type="saw:regularColumn" columnID="cde8e2e077447bd9a">

      <saw:columnFormula>

        <sawx:expr xsi:type="sawx:sqlExpression">"Job"."Set Name"</sawx:expr></saw:columnFormula>

      <saw:displayFormat>

        <saw:formatSpec suppress="repeat" wrapText="false"/></saw:displayFormat>

      <saw:columnHeading>

        <saw:displayFormat>

         <saw:formatSpec/></saw:displayFormat></saw:columnHeading></saw:column>

     <saw:column xsi:type="saw:regularColumn" columnID="cefd311d6902d9dd8">

      <saw:columnFormula>

        <sawx:expr xsi:type="sawx:sqlExpression">cast("Job"."Effective Start Date" as date)</sawx:expr></saw:columnFormula>

      <saw:displayFormat>

        <saw:formatSpec suppress="repeat" wrapText="false"/></saw:displayFormat>

      <saw:tableHeading>

        <saw:caption fmt="text">

         <saw:text>Job</saw:text></saw:caption></saw:tableHeading>

      <saw:columnHeading>

        <saw:displayFormat>

         <saw:formatSpec/></saw:displayFormat>

        <saw:caption fmt="text">

         <saw:text>Job Effective Start Date</saw:text></saw:caption></saw:columnHeading></saw:column>

     <saw:column xsi:type="saw:regularColumn" columnID="c36c451e936b193c2">

      <saw:columnFormula>

        <sawx:expr xsi:type="sawx:sqlExpression">"Grade"."Effective End Date"</sawx:expr></saw:columnFormula>

      <saw:displayFormat>

        <saw:formatSpec suppress="repeat" wrapText="false"/></saw:displayFormat>

      <saw:tableHeading>

        <saw:caption fmt="text">

         <saw:text>Grade</saw:text></saw:caption></saw:tableHeading>

      <saw:columnHeading>

        <saw:displayFormat>

         <saw:formatSpec/></saw:displayFormat>

        <saw:caption fmt="text">

         <saw:text>Grade Effective End Date</saw:text></saw:caption></saw:columnHeading></saw:column>

     <saw:column xsi:type="saw:regularColumn" columnID="c182cbb803bf35a95">

      <saw:columnFormula>

        <sawx:expr xsi:type="sawx:sqlExpression">"Grade"."Effective Start Date"</sawx:expr></saw:columnFormula>

      <saw:displayFormat>

        <saw:formatSpec suppress="repeat" wrapText="false"/></saw:displayFormat>

      <saw:tableHeading>

        <saw:caption fmt="text">

         <saw:text>Grade</saw:text></saw:caption></saw:tableHeading>

      <saw:columnHeading>

        <saw:displayFormat>

         <saw:formatSpec/></saw:displayFormat>

        <saw:caption fmt="text">

         <saw:text>Grade Effective Start Date</saw:text></saw:caption></saw:columnHeading></saw:column>

     <saw:column xsi:type="saw:regularColumn" columnID="ca20399a6d18962e9">

      <saw:columnFormula>

        <sawx:expr xsi:type="sawx:sqlExpression">"Job"."PER_JOBS_DFF_COMPENSATIONGROUP_v"</sawx:expr></saw:columnFormula>

      <saw:displayFormat>

        <saw:formatSpec suppress="repeat" wrapText="false"/></saw:displayFormat>

      <saw:columnHeading>

        <saw:displayFormat>

         <saw:formatSpec/></saw:displayFormat></saw:columnHeading></saw:column>

     <saw:column xsi:type="saw:regularColumn" columnID="c7a9d50ca47b9b356">

      <saw:columnFormula>

        <sawx:expr xsi:type="sawx:sqlExpression">"Job"."Job Active Status"</sawx:expr></saw:columnFormula>

      <saw:displayFormat>

        <saw:formatSpec suppress="repeat" wrapText="true"/></saw:displayFormat>

      <saw:columnHeading>

        <saw:displayFormat>

         <saw:formatSpec/></saw:displayFormat></saw:columnHeading></saw:column>

     <saw:column xsi:type="saw:regularColumn" columnID="cedbd5e093b3a73e6">

      <saw:columnFormula>

        <sawx:expr xsi:type="sawx:sqlExpression">"Job"."Job Manager Level"</sawx:expr></saw:columnFormula>

      <saw:displayFormat>

        <saw:formatSpec visibility="hidden" suppress="repeat" wrapText="true"/></saw:displayFormat>

      <saw:columnHeading>

        <saw:displayFormat>

         <saw:formatSpec/></saw:displayFormat></saw:columnHeading></saw:column>

     <saw:column xsi:type="saw:regularColumn" columnID="c195548df59c27ca1">

      <saw:columnFormula>

        <sawx:expr xsi:type="sawx:sqlExpression">"Job"."Job Family Name"</sawx:expr></saw:columnFormula>

      <saw:displayFormat>

        <saw:formatSpec suppress="repeat" wrapText="false"/></saw:displayFormat>

      <saw:columnHeading>

        <saw:displayFormat>

         <saw:formatSpec/></saw:displayFormat></saw:columnHeading></saw:column>

     <saw:column xsi:type="saw:regularColumn" columnID="c8387c934c333bafb">

      <saw:columnFormula>

        <sawx:expr xsi:type="sawx:sqlExpression">"Job"."Job Function Name"</sawx:expr></saw:columnFormula>

      <saw:displayFormat>

        <saw:formatSpec suppress="repeat" wrapText="false" visibility="hidden"/></saw:displayFormat>

      <saw:columnHeading>

        <saw:displayFormat>

         <saw:formatSpec/></saw:displayFormat></saw:columnHeading></saw:column>

     <saw:column xsi:type="saw:regularColumn" columnID="cc03613318e60db94">

      <saw:columnFormula>

        <sawx:expr xsi:type="sawx:sqlExpression">"Job"."Full/Part Time"</sawx:expr></saw:columnFormula>

      <saw:displayFormat>

        <saw:formatSpec visibility="hidden" suppress="repeat" wrapText="true"/></saw:displayFormat>

      <saw:columnHeading>

        <saw:displayFormat>

         <saw:formatSpec/></saw:displayFormat></saw:columnHeading></saw:column>

     <saw:column xsi:type="saw:regularColumn" columnID="c606e0bbfe6e7e2d1">

      <saw:columnFormula>

        <sawx:expr xsi:type="sawx:sqlExpression">"Job"."Regular/Temporary"</sawx:expr></saw:columnFormula>

      <saw:displayFormat>

        <saw:formatSpec visibility="hidden" suppress="repeat" wrapText="true"/></saw:displayFormat>

      <saw:columnHeading>

        <saw:displayFormat>

         <saw:formatSpec/></saw:displayFormat></saw:columnHeading></saw:column>

     <saw:column xsi:type="saw:regularColumn" columnID="cd77cfd241e37bfbb">

      <saw:columnFormula>

        <sawx:expr xsi:type="sawx:sqlExpression">"Job"."Valid Grades"</sawx:expr></saw:columnFormula>

      <saw:displayFormat>

        <saw:formatSpec suppress="repeat" wrapText="false"/></saw:displayFormat>

      <saw:tableHeading>

        <saw:caption fmt="text">

         <saw:text>Job</saw:text></saw:caption></saw:tableHeading>

      <saw:columnHeading>

        <saw:caption fmt="text">

         <saw:text>Grade Code</saw:text></saw:caption>

        <saw:displayFormat>

         <saw:formatSpec/></saw:displayFormat></saw:columnHeading></saw:column>

     <saw:column xsi:type="saw:regularColumn" columnID="cb74cd6a102e35d56">

      <saw:columnFormula>

        <sawx:expr xsi:type="sawx:sqlExpression">LEFT("Job"."Valid Grades", 2)</sawx:expr></saw:columnFormula>

      <saw:displayFormat>

        <saw:formatSpec visibility="hidden" suppress="suppress" wrapText="true"/></saw:displayFormat>

      <saw:tableHeading>

        <saw:caption fmt="text">

         <saw:text>Job</saw:text></saw:caption></saw:tableHeading>

      <saw:columnHeading>

        <saw:caption fmt="text">

         <saw:text>Grade</saw:text></saw:caption>

        <saw:displayFormat>

         <saw:formatSpec/></saw:displayFormat></saw:columnHeading></saw:column>

     <saw:column xsi:type="saw:regularColumn" columnID="cc3393951c5532315">

      <saw:columnFormula>

        <sawx:expr xsi:type="sawx:sqlExpression">"Job Legislation Data_United States Job Information"."Job Legislation Data_PER_JOBS_LEG_EFF_HRX_US_JOBS__FLSA_STATUS_v"</sawx:expr></saw:columnFormula>

      <saw:displayFormat>

        <saw:formatSpec visibility="hidden" suppress="repeat" wrapText="true"/></saw:displayFormat>

      <saw:columnHeading>

        <saw:displayFormat>

         <saw:formatSpec/></saw:displayFormat></saw:columnHeading></saw:column>

     <saw:column xsi:type="saw:regularColumn" columnID="c6b5c2b22c878d68f">

      <saw:columnFormula>

        <sawx:expr xsi:type="sawx:sqlExpression">"Job Legislation Data_United States Job Information"."Job Legislation Data_PER_JOBS_LEG_EFF_HRX_US_JOBS__EEO1_CATEGORY_v"</sawx:expr></saw:columnFormula>

      <saw:displayFormat>

        <saw:formatSpec visibility="hidden" suppress="repeat" wrapText="true"/></saw:displayFormat>

      <saw:columnHeading>

        <saw:displayFormat>

         <saw:formatSpec/></saw:displayFormat></saw:columnHeading></saw:column></saw:columns>

   <saw:filter>

     <sawx:expr op="in" xsi:type="sawx:list">

      <sawx:expr xsi:type="sawx:columnExpression" formulaUse="code" displayUse="display">

        <saw:columnFormula formulaUse="display">

         <sawx:expr xsi:type="sawx:sqlExpression">"Job"."Job Active Status"</sawx:expr></saw:columnFormula></sawx:expr>

      <sawx:expr xsi:type="xsd:string">A</sawx:expr>

      <sawx:expr xsi:type="xsd:string">I</sawx:expr></sawx:expr></saw:filter></saw:criteria>

  <saw:views currentView="0">

   <saw:view xsi:type="saw:compoundView" name="compoundView!1">

     <saw:cvTable>

      <saw:cvRow>

        <saw:cvCell viewName="tableView!1">

         <saw:displayFormat>

           <saw:formatSpec/></saw:displayFormat></saw:cvCell></saw:cvRow></saw:cvTable></saw:view>

   <saw:view xsi:type="saw:titleView" name="titleView!1"/>

   <saw:view xsi:type="saw:tableView" name="tableView!1" scrollingEnabled="false">

     <saw:edges>

      <saw:edge axis="page" showColumnHeader="true"/>

      <saw:edge axis="section"/>

      <saw:edge axis="row" showColumnHeader="true">

        <saw:edgeLayers>

         <saw:edgeLayer type="column" columnID="cdec5ac7a8c3d0f23"/>

         <saw:edgeLayer type="column" columnID="c6c9784080c98edad"/>

         <saw:edgeLayer type="column" columnID="c7a9d50ca47b9b356"/>

         <saw:edgeLayer type="column" columnID="cefd311d6902d9dd8"/>

         <saw:edgeLayer type="column" columnID="ca3867f221958bb25"/>

         <saw:edgeLayer type="column" columnID="cf7102afbafb19d19"/>

         <saw:edgeLayer type="column" columnID="cedbd5e093b3a73e6"/>

         <saw:edgeLayer type="column" columnID="cc03613318e60db94"/>

         <saw:edgeLayer type="column" columnID="c606e0bbfe6e7e2d1"/>

         <saw:edgeLayer type="column" columnID="c195548df59c27ca1"/>

         <saw:edgeLayer type="column" columnID="cde8e2e077447bd9a"/>

         <saw:edgeLayer type="column" columnID="c8387c934c333bafb"/>

         <saw:edgeLayer type="column" columnID="ca20399a6d18962e9"/>

         <saw:edgeLayer type="column" columnID="cc3393951c5532315"/>

         <saw:edgeLayer type="column" columnID="c6b5c2b22c878d68f"/>

         <saw:edgeLayer type="column" columnID="cd77cfd241e37bfbb"/>

         <saw:edgeLayer type="column" columnID="cb74cd6a102e35d56"/>

         <saw:edgeLayer type="column" columnID="c6fadc1ae13c65912"/>

         <saw:edgeLayer columnID="ccff65b4dd9c5cf36_d3" type="column"/>

         <saw:edgeLayer columnID="ccff65b4dd9c5cf36_d1" type="column"/>

         <saw:edgeLayer columnID="ccff65b4dd9c5cf36_d2" type="column"/>

         <saw:edgeLayer type="column" columnID="c182cbb803bf35a95"/>

         <saw:edgeLayer type="column" columnID="c36c451e936b193c2"/>

         <saw:edgeLayer type="column" columnID="ccff65b4dd9c5cf36"/>

         <saw:edgeLayer type="column" columnID="c4e6f0d721f4642da"/></saw:edgeLayers></saw:edge>

      <saw:edge axis="column" showColumnHeader="rollover"/></saw:edges></saw:view></saw:views></saw:report>


Thanks

Pam

Tagged:
«1

Answers

  • MandeepGupta
    MandeepGupta Rank 6 - Analytics Lead

    Hi Pam,

    Please check if below helps:

    SELECT DISTINCT 

      TO_CHAR (pvgf.effective_start_date, 'DD/MON/YYYY') effective_start_date,

      TO_CHAR (pvgf.effective_end_date, 'DD/MON/YYYY') effective_end_date,

      pjfv.job_code,

      pjfv.name job_name,

      pgfv.grade_code,

      pgfv.name grade_name,

      pvgf.valid_grade_id,

      pgfv.grade_id,

      pjfv.job_id

     FROM per_valid_grades_f pvgf,

        per_jobs_f_vl pjfv,

        per_grades_f_vl pgfv 

     WHERE 1=1

      AND pvgf.job_id = pjfv.job_id

      AND pvgf.grade_id = pgfv.grade_id

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

      AND pvgf.effective_start_date BETWEEN pgfv.effective_start_date AND pgfv.effective_end_date

    ORDER BY job_code,grade_code

    Thanks.

  • PamS
    PamS Rank 3 - Community Apprentice

    Thanks Mandeep

    So far this looks like what I was looking for - I have a couple of other fields to add so fingers crossed I don't end up with the same issue I had before

  • PamS
    PamS Rank 3 - Community Apprentice

    Hi Mandeep

    After some further testing, this logic doesn't seem to be working either :-(

    I have a Change in the Job Name that isn't pulling into the query - I tried adding logic to pull in changes that are greater than the sysdate but it didn't work unfortunately


    Thanks

    Pam

  • MandeepGupta
    MandeepGupta Rank 6 - Analytics Lead

    Hi @PamS ,

    Please share the modified query. I can take a look.

    Thanks.

  • PamS
    PamS Rank 3 - Community Apprentice

    Hi Mandeep

    Here is the logic - I am wondering if the view doesn't have access to some of the data... I know that I have a Job Name change that should be pulling in from Dec 1

    I tried just querying the table and for the 2021-12-01 record - the Job Name should be Administrative Support - TEST

    Is there any way to see the logic for the _VL table?


    SELECT DISTINCT

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

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

      pjfv.job_code,

      pjfv.name                              job_name,

      pjfv.attribute1                           comp_group,

      pjfv.set_id                             job_set_id,

      pgfv.grade_code,

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

      pjftl.job_family_name,

      pgfv.name                              grade_name,

      pvgf.valid_grade_id,

      pgfv.grade_id,

      pjfv.job_id

    FROM

      per_valid_grades_f  pvgf,

      per_jobs_f_vl    pjfv,

      per_grades_f_vl   pgfv,

      per_job_family_f_tl pjftl

    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 pjftl.job_family_id = pjfv.job_family_id

      AND pjftl.language = 'US'

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

         OR pvgf.effective_start_date >= sysdate )

      AND ( pvgf.effective_start_date BETWEEN pgfv.effective_start_date AND pgfv.effective_end_date

         OR pvgf.effective_start_date >= sysdate )

      AND ( pvgf.effective_start_date BETWEEN pjftl.effective_start_date AND pjftl.effective_end_date

         OR pvgf.effective_start_date >= sysdate )

    ORDER BY

      job_code,

      grade_code

  • MandeepGupta
    MandeepGupta Rank 6 - Analytics Lead

    Hi Pam,

    This is very strange. You can view the logic on below link:

    The logic is very simple.

    Please see if below helps:

    SELECT DISTINCT

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

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

     pjfv.job_code,

     pjfv.name               job_name,

     pjfv.attribute1              comp_group,

     pjfv.set_id               job_set_id,

     pgfv.grade_code,

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

     pjftl.job_family_name,

     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

      from PER_JOBS_F pjf,

         PER_JOBS_F_TL pjft

      where pjf.job_id = pjft.job_id

       and pjft.language = 'US')  pjfv,

     per_grades_f_vl  pgfv,

     per_job_family_f_tl pjftl

    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 pjftl.job_family_id = pjfv.job_family_id

     AND pjftl.language = 'US'

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

       OR pvgf.effective_start_date >= sysdate )

     AND ( pvgf.effective_start_date BETWEEN pgfv.effective_start_date AND pgfv.effective_end_date

       OR pvgf.effective_start_date >= sysdate )

     AND ( pvgf.effective_start_date BETWEEN pjftl.effective_start_date AND pjftl.effective_end_date

       OR pvgf.effective_start_date >= sysdate )

    ORDER BY

     job_code,

     grade_code

    Thanks.

  • PamS
    PamS Rank 3 - Community Apprentice

    Hi Mandeep,

    Thanks for the fix - I can see the new job name now :-)

    I have a couple more fields to add for the Grade but this looks like it is working better now

    Pam

  • MandeepGupta
    MandeepGupta Rank 6 - Analytics Lead

    Glad it worked @PamS. Do let me know if you need any other details.

    Thanks.

  • PamS
    PamS Rank 3 - Community Apprentice

    Hi Mandeep,

    I am having the same issue with the Job Family Name - it is not showing a future change in the query

    Can I just add that table to the Job query that you created?

    Pam

  • MandeepGupta
    MandeepGupta Rank 6 - Analytics Lead

    Hi @PamS

    You want to show data as of effective start date of valid grades? or any other table? It will depend upon your date conditions.

    Try with below:

     AND ( pvjf.effective_start_date BETWEEN pjftl.effective_start_date AND pjftl.effective_end_date

       OR pvjf.effective_start_date >= sysdate )

    Thanks.