HXC Retrieval Error Handling

            select
               htb.start_time,
               to_char(htb.stop_time,'DD-MON-YY') stop_time,
               htb.resource_id,
               htb.resource_type,
               htb.scope,
               htd.exception_description,
               htd.transaction_id,
               ht.transaction_code,
               ht.transaction_process_id,
               htd.time_building_block_id,
               htd.time_building_block_ovn,
               hrp.retrieval_process_id,
               ht.transaction_process_id,
               hrp.name,
                HXC_HXCRTERR_XMLP_PKG.cf_resourceformula(htb.resource_type, htb.resource_id) CF_resource,
                HXC_HXCRTERR_XMLP_PKG.CP_employee_number_p CP_employee_number,
                HXC_HXCRTERR_XMLP_PKG.CP_last_name_p CP_last_name,
				'X' dummy
            from   hxc_transactions ht,
                       hxc_retrieval_processes hrp,
                       hxc_transaction_details htd,
                       hxc_time_building_blocks htb
            where ht.type                        = 'RETRIEVAL'
            and     htd.status                   = 'ERRORS'
            and     htd.transaction_id           = ht.transaction_id
            and     htd.time_building_block_id   = htb.time_building_block_id
            and     htd.time_building_block_ovn  = htb.object_version_number
            and     htb.scope                    = 'TIMECARD'
            and     ht.transaction_code          = nvl(:p_transaction_code,ht.transaction_code)
            and     hrp.retrieval_process_id     = nvl(:p_retrieval_process,hrp.retrieval_process_id)
            and   (
                       hrp.retrieval_process_id     = ht.transaction_process_id
                 OR   (  DECODE( :p_retrieval_process, :p_BEE_RP_ID, -1, -999) =ht.transaction_process_id )
                     )
            and     ht.transaction_date between :p_start_date and (:p_end_date + 1 - 1/(24*3600) )
            order by htd.time_building_block_id