nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

Oracle Communities

Oracle Analytics and AI

Child Item

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

PamS

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=""Workforce Management - Worker Assignment Event Real Time"" 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

Find more posts tagged with

SQL

OTBI

Accepted answers

All comments

MandeepGupta

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

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

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

Hi @PamS ,

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

Thanks.

PamS

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

Hi Pam,

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

https://docs.oracle.com/en/cloud/saas/human-resources/21c/oedmh/global-human-resources.html#perjobsfvl-3355

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

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

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

Thanks.

PamS

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

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.