Oracle Balance Queries

 

— Balance_value_based_on balance_name

Continue reading →

Advertisements

Oracle Benefits Tables

select * from apps.BEN_YR_PERD ; —-Plan years like periods of plan fiscal year are stored in this table

select * from apps.BEN_PL_TYP_F where name like ‘DENT%TEST%’ ; —- Plan types are stored here , PL_TYP_ID is stored here.

 

select * from apps.BEN_ACTL_PREM_F ;
select * from apps.BEN_OPT_F ; –opt_id,opt_name

select * from apps.BEN_PL_TYP_V ;
select * from apps.ben_opt_v ;

— plan types

select * from all_objects where object_name like ‘%BEN%P%TY%F’ and object_type=’TABLE’;

Element Query

SELECT
PETF.ELEMENT_NAME “Element Entry Name”
, PAAF.PERSON_ID, PAPF.FULL_NAME “Employee Name”
, PIVF.NAME “Input Fields”
, PEEVF.EFFECTIVE_START_DATE “Entry Start Date”
, PEEVF.EFFECTIVE_END_DATE “Entry End Date”
, PEEVF.SCREEN_ENTRY_VALUE “Entry Values”

FROM PAY_ELEMENT_TYPES_F PETF
,PAY_ELEMENT_LINKS_F PELF
,PAY_ELEMENT_ENTRIES_F PEEF
,PER_ALL_ASSIGNMENTS_F PAAF
,PER_ALL_PEOPLE_F PAPF
,PAY_ELEMENT_ENTRY_VALUES_F PEEVF
,PAY_INPUT_VALUES_F PIVF

WHERE 1 = 1
AND PETF.ELEMENT_NAME = ‘&Entry_Name’
AND PELF.ELEMENT_TYPE_ID = PETF.ELEMENT_TYPE_ID
AND PEEF.ELEMENT_TYPE_ID = PETF.ELEMENT_TYPE_ID
AND TRUNC(SYSDATE) BETWEEN TRUNC(PEEF.EFFECTIVE_START_DATE) AND TRUNC(PEEF.EFFECTIVE_END_DATE)
AND PAAF.ASSIGNMENT_ID = PEEF.ASSIGNMENT_ID
AND TRUNC(SYSDATE) BETWEEN TRUNC(PAAF.EFFECTIVE_START_DATE) AND TRUNC(PAAF.EFFECTIVE_END_DATE)
AND PAPF.PERSON_ID = PAAF.PERSON_ID
AND PAPF.CURRENT_EMP_OR_APL_FLAG = ‘Y’
AND TRUNC(SYSDATE) BETWEEN TRUNC(PAPF.EFFECTIVE_START_DATE) AND TRUNC(PAPF.EFFECTIVE_END_DATE)

AND PEEVF.ELEMENT_ENTRY_ID = PEEF.ELEMENT_ENTRY_ID

AND PIVF.INPUT_VALUE_ID = PEEVF.INPUT_VALUE_ID