Getting value of Database Items

Many a times to debug issues with fast formula which uses database items, we want to know exact sql query executed to get the value. Also, to get YTD values of any balance using DBI, we may need to know the value it is returning based on the context which we are setting.

Following steps can be used to get to the value of any DBI.

1. Get user entity id.

SELECT USER_NAME, DEFINITION_TEXT, USER_ENTITY_ID

FROM FF_DATABASE_ITEMS

WHERE USER_NAME = 'XX_HOURS_ASG_YTD';

2. Get Route Id.

SELECT ROUTE_ID FROM FF_USER_ENTITIES

WHERE USER_ENTITY_ID = 300000467685757;

3. Get the SQL query used for this DBI:

SELECT TEXT

FROM FF_ROUTES

WHERE ROUTE_ID = 300000000327764;

In this case as we are fetching YTD value which uses seeded function, it returns dual.

4. The result from above query may use contexts (&B1, &B2, etc). To get the context name use below query:

select  a.context_id, a.context_name, b.sequence_no

from ff_contexts a, FF_ROUTE_CONTEXT_USAGES b

where a.context_id = b.context_id

and b.route_id = 300000000327764

order by 3

5. If the query uses parameters &U1, &U2 etc, use following SQL to get the parameter name:

select user_entity_id, Route_parameter_id, value

from FF_ROUTE_PARAMETER_VALUES ffrpv

where ffrpv.user_entity_id in(select ffdbi.user_entity_id

from ff_database_items ffdbi

where user_name = 'XX_HOURS_ASG_YTD') ;

Select parameter_name

from ff_route_parameters

where route_Parameter_id in (300000000327765);

6. In this case, it is DEFINED_BALANCE_ID. Get value of defined_balance_id:

SELECT pdb.defined_balance_id , pbt.balance_name,  pdu.dimension_name

FROM pay_defined_balances pdb ,

pay_balance_types_vl pbt,

pay_balance_dimensions pbd,

pay_dimension_usages_vl pdu

WHERE 1 = 1

AND pdb.balance_type_id = pbt.balance_type_id

AND pdb.balance_dimension_id = pbd.balance_dimension_id

AND pbd.balance_dimension_id = pdu.balance_dimension_id

AND pdu.legislation_code = 'US'

AND pbt.balance_name = 'XX Hours' 

AND currency_code='USD'

and pdu.dimension_name = 'Assignment Year to Date' 

;

7. Now the final SQL query to get the value will be

SELECT DEFINITION_TEXT (From SQL1)

FROM TEXT (From SQL3)

In this case it is :

select pay_balance_pkg.get_value_dbi(&U1

, &B1

,null

,&B2

,null

,null

,fnd_number.number_to_canonical(&B3)

,null

,null

,null

,null

,null

,null

,null

,null

,null

)

from dual;

&U1 = DEFINED_BALANCE_ID

&B1 =  PAYROLL_REL_ACTION_ID

&B2 = PAYROLL_ASSIGNMENT_ID

&B3 = CALC_BREAKDOWN_ID