Oracle Database Discussions

Hello,

Can someone let me know the difference between fnd_lookup_values and hr_lookups. I know hr_lookups is built on top of fnd_lookup_values, but it does filter off some records based on the value of TAG (one of the column in fnd_lookups). I want to know the purpose behind it.

For example for the Type = LEAV_REAS there is a code called 'WR' which translates to Work Refusal. If you see the TAG it says '+US'.

If you query...

SELECT *
FROM fnd_lookup_values
WHERE lookup_code = 'WR'

..it returns values for LEAV_REAS type.

However if you run the query...

SELECT *
FROM hr_lookups
WHERE lookup_code = 'WR'

... it does not return LEAV_REAS type.

The reason behind this is because of the following decode statement in hr_lookups view...

I want to know the purpose behind this.

AND DECODE (

FLV.TAG,

NULL,

'Y',

DECODE (

SUBSTR (FLV.TAG, 1, 1),

'+',

DECODE (

SIGN (INSTR (FLV.TAG, SYS_CONTEXT ('HR_SESSION_DATA', 'LEG_CODE'))),

1,

'Y',

'N'

),

'-',

DECODE (

SIGN (INSTR (FLV.TAG, SYS_CONTEXT ('HR_SESSION_DATA', 'LEG_CODE'))),

1,

'N',

'Y'

),

'Y'

)

) = 'Y'

Thanks,
Sai