Important SQL Queries related to the ICPs (Individual Compensation Plans)

This article aims to discuss several key SQL queries related to the ICP, which assist in validation within fast formulas and other areas.

1. Select the base name, name, INPUT_VALUE_ID from an element.

select NAME,BASE_NAME, INPUT_VALUE_ID from

pay_input_values_vl

where

element_type_id in

(select element_type_id from pay_element_types_vl where element_name =

<Element Name>)

2. This SQL query retrieves the approved ICP data using the Base Name and element.

Select 

	ii.SCREEN_ENTRY_VALUE

From

	pay_element_entries_f aa,PAY_ELEMENT_TYPES_TL bb,PER_ALL_PEOPLE_F 	cc,PAY_INPUT_VALUES_F ff,PAY_ELEMENT_ENTRY_VALUES_F ii

Where

	aa.ELEMENT_TYPE_ID = bb.ELEMENT_TYPE_ID	

	and cc.person_id=aa.person_id

	and ii.INPUT_VALUE_ID = ff.INPUT_VALUE_ID

	and ii.ELEMENT_ENTRY_ID = aa.ELEMENT_ENTRY_ID

 	and bb.ELEMENT_NAME =<ELEMENT_NAME>

	and bb.LANGUAGE='US'

	and aa.ELEMENT_TYPE_ID = ff.ELEMENT_TYPE_ID

	and ff.BASE_NAME=<BASE_NAME>

 	and TRUNC(SYSDATE) BETWEEN cc.effective_start_date AND 		cc.effective_end_date

	 AND cc.person_id=Person Id

3.      Retrieve the values of submitted element fields using this SQL query

Select

	g.SCREEN_ENTRY_VALUE||' - '||h.SCREEN_ENTRY_VALUE||' - '||i.SCREEN_ENTRY_VALUE

From

	pay_element_entries_f a,PAY_ELEMENT_TYPES_TL b,PER_ALL_PEOPLE_F c, 		 	PAY_INPUT_VALUES_F d,PAY_INPUT_VALUES_F e,PAY_INPUT_VALUES_F f, 			 	PAY_ELEMENT_ENTRY_VALUES_F g, PAY_ELEMENT_ENTRY_VALUES_F h, 				PAY_ELEMENT_ENTRY_VALUES_F i              

Where

	a.ELEMENT_TYPE_ID = b.ELEMENT_TYPE_ID

	and c.person_id=a.person_id

	and g.INPUT_VALUE_ID = d.INPUT_VALUE_ID	

	and h.INPUT_VALUE_ID = e.INPUT_VALUE_ID

	and i.INPUT_VALUE_ID = f.INPUT_VALUE_ID

	and g.ELEMENT_ENTRY_ID = a.ELEMENT_ENTRY_ID	

	and h.ELEMENT_ENTRY_ID = a.ELEMENT_ENTRY_ID

	and i.ELEMENT_ENTRY_ID = a.ELEMENT_ENTRY_ID

	and b.ELEMENT_NAME IN (<ElementName 1>,<ElementName 2>)

	and b.LANGUAGE='US'

	and a.ELEMENT_TYPE_ID = d.ELEMENT_TYPE_ID

	and a.ELEMENT_TYPE_ID = e.ELEMENT_TYPE_ID

	and a.ELEMENT_TYPE_ID = f.ELEMENT_TYPE_ID

	and d.BASE_NAME=<Overtime Start Date>

	and e.BASE_NAME=<Overtime End Date>

	 and f.BASE_NAME=<Total Overtime Hours>

	AND TRUNC(SYSDATE) BETWEEN c.effective_start_date AND c.effective_end_date

	and  to_char(a.ELEMENT_ENTRY_ID ) not in (Select distinct nvl(gp.SCREEN_ENTRY_VALUE, '0' )

                               from

                                         pay_element_entries_f ap

                                        ,PAY_ELEMENT_TYPES_TL bp

                                        ,PAY_INPUT_VALUES_F dp

                                        ,PAY_ELEMENT_ENTRY_VALUES_F gp

                              where 1=1

                                        and ap.ELEMENT_TYPE_ID = bp.ELEMENT_TYPE_ID

               			  and ap.person_id = c.person_id

                                        and gp.INPUT_VALUE_ID = dp.INPUT_VALUE_ID

                                        and gp.ELEMENT_ENTRY_ID = ap.ELEMENT_ENTRY_ID

                                        and bp.ELEMENT_NAME IN (<Element Name 3>)

                                        and ap.ELEMENT_TYPE_ID = dp.ELEMENT_TYPE_ID

                                        and dp.BASE_NAME='Total Overtime Hours'    

                                       and gp.ELEMENT_ENTRY_ID  <> :{PARAMETER.ElementEntryId} )	

	AND c.person_id=:{PARAMETER.PersonId}

4.      Verify the pending transaction with identical details. This formula is used to identify duplicate entries for an ICP.

Select

	substr((EXTRACTVALUE (VALUE (pj),'*/ElementEntryValueDEORow/ScreenEntryValue/DATA')),1,60)

from

	HRC_TXN_DATA TXNDATA, HRC_TXN_HEADER TXNHEAD,fa_fusion_soainfra.wftask wft, TABLE(XMLSEQUENCE (EXTRACT (xml_data_cache,'/TRANSACTION//EO[@Name=''oracle.apps.hcm.payrolls.elements.entries.protectedModel.entity.ElementEntryValueDEO'']'))) pj

where

	TXNHEAD.TRANSACTION_ID = TXNDATA.transaction_id

	AND TXNDATA.STATUS = 'PENDING'

  	AND extractValue(value(pj),'//ScreenEntryValue/DATA') =<BASE NAME>

	AND  extractValue(value(pj),'//InputValueId/DATA') =<InputValueID>

	AND TXNHEAD.initiator_user_id = :{PARAMETER.P_PERSON_ID}

	And To_Char(TXNHEAD.Transaction_Id) = Wft.Identificationkey

	AND  (wft.STATE is not null OR wft.STATE='WITHDRAWN')

 	AND ROWNUM=1

5.      Use this SQL query to obtain the list of submitted element fields that have not been submitted in another ICP

Select 

	pay_element_entries_f PEEF,  PAY_ELEMENT_TYPES_TL PETL, PER_ALL_PEOPLE_F PAPF, PAY_INPUT_VALUES_F PIVF, PAY_ELEMENT_ENTRY_VALUES_F PEEVF

From 

	pay_element_entries_f PEEF,  PAY_ELEMENT_TYPES_TL PETL, PER_ALL_PEOPLE_F PAPF, PAY_INPUT_VALUES_F PIVF, PAY_ELEMENT_ENTRY_VALUES_F PEEVF

Where

	PEEF.ELEMENT_TYPE_ID = PETL.ELEMENT_TYPE_ID

	and PAPF.person_id=PEEF.person_id

	and PEEVF.INPUT_VALUE_ID = PIVF.INPUT_VALUE_ID

	and PEEVF.ELEMENT_ENTRY_ID = PEEF.ELEMENT_ENTRY_ID

	and PETL.ELEMENT_NAME in (<Child Element Name 1>, <Child Element Name 2>)

	and PETL.LANGUAGE='US'

	and PEEF.ELEMENT_TYPE_ID = PIVF.ELEMENT_TYPE_ID

	and PIVF.BASE_NAME=<From Date>

	AND trunc(sysdate) between PAPF.EFFECTIVE_START_DATE and 		PAPF.EFFECTIVE_END_DATE

	and to_char(PEEF.ELEMENT_ENTRY_ID ) not in (Select distinct 	nvl(gp.SCREEN_ENTRY_VALUE,0)

			from 

				pay_element_entries_f ap

				,PAY_ELEMENT_TYPES_TL bp

				,PAY_INPUT_VALUES_F dp

				,PAY_ELEMENT_ENTRY_VALUES_F gp

			where 1=1

				and ap.ELEMENT_TYPE_ID = bp.ELEMENT_TYPE_ID

                			and ap.person_id = PAPF.person_id

				and gp.INPUT_VALUE_ID = dp.INPUT_VALUE_ID

				and gp.ELEMENT_ENTRY_ID = ap.ELEMENT_ENTRY_ID

				and bp.ELEMENT_NAME IN (<Parent Element 1>)

				and ap.ELEMENT_TYPE_ID = dp.ELEMENT_TYPE_ID

				and dp.BASE_NAME=<Business List>               

				and gp.ELEMENT_ENTRY_ID  <> :{PARAMETER.ElementEntryId}

            )

	   and to_char(PEEF.ELEMENT_ENTRY_ID ) not in (Select

	   substr(extractValue(value(pj),'//ScreenEntryValue/DATA'),1,60)

	   from 

		HRC_TXN_DATA TXNDATA, HRC_TXN_HEADER TXNHEAD, TABLE(XMLSEQUENCE (EXTRACT (xml_data_cache,'/TRANSACTION//EO[@Name=''oracle.apps.hcm.payrolls.elements.entries.protectedModel.entity.ElementEntryValueDEO'']'))) pj

	Where

		TXNHEAD.TRANSACTION_ID = TXNDATA.transaction_id

       AND TXNDATA.STATUS = 'PENDING'

	   and extractValue(value(pj),'//InputValueId/DATA') =<InputValueID>

       AND TXNHEAD.initiator_user_id = Person Number

	   )

and PAPF.person_id= Person Number