Query to get element input values sum for already approved or approval pending entries

This was an interesting requirement where we need to find out the sum of input values for the particular month as particular element was multiple entries allowed.

Below query was build to achieve the requirement -

select sum(PEEVF.SCREEN_ENTRY_VALUE) 	
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=:{PARAMETER.P_ELEMENT}
and PETL.LANGUAGE='US'
and PEEF.ELEMENT_TYPE_ID = PIVF.ELEMENT_TYPE_ID
and PIVF.BASE_NAME=:{PARAMETER.P_INPUT_VALUE}
and PAPF.person_id= :{PARAMETER.P_PERSON_ID}
AND PEEVF.EFFECTIVE_START_DATE BETWEEN :{PARAMETER.P_START_DATE} AND :{PARAMETER.P_END_DATE}

But there was a catch, we were missing those element entries which were yet to get approved. Now it depends from requirement to requirement whether we need to consider approval pending entries or not, but in my scenario it was required. Thus, this lead to additional development of query to fulfill the requirement -

select SUM(
to_number(NVL((
SELECT
extractValue(value(TXML),'//ScreenEntryValue/DATA') as NewValue
FROM
TABLE(xmlsequence(extract(xmltype('<ROOT>'|| TXNDATA.DATA_CACHE || '</ROOT>'), './/EO/ElementEntryValueDEORow'))) TXML
WHERE extractValue(value(TXML),'//InputValueId/DATA') =300000014153218
), '0')))					
from HRC_TXN_DATA TXNDATA,
HRC_TXN_HEADER TXNHEAD
where 
TXNHEAD.TRANSACTION_ID=TXNDATA.transaction_id 
and TXNHEAD.module_identifier = 'Make Personal Contribution'
and TXNHEAD.module_group='CMP'
and TXNHEAD.initiator_user_id =  :{PARAMETER.P_PERSON_ID}
AND TXNDATA.STATUS 						= 'PENDING'

Second query may differ in other scenarios, as this is fetching the required data from transaction tables, but this can be referred t build up query for other scenarios.

Happy Reading!