SELECT..INTO returns too many rows occurred in table pay_status_processing_rules_f at location 512

 

When attempting to Run Quickpay,

the following error occurs.

ERROR

-----------------------

A system error SQL-02112: SELECT..INTO returns too many rows occurred in table pay_status_processing_rules_f at location 512. Contact your help desk.

The cause of the issue is invalid / incorrect data in fusion.pay_status_proc_rules_f

There are Duplicate rows of Status processing rules for the Element

Run the Below query to identify the element having duplicate status processing Rules

Query1 : select  distinct element_name,ldg.name,s1.STATUS_PROCESSING_RULE_ID,s1.element_type_id

from fusion.pay_status_proc_rules_f s1, fusion.pay_status_proc_rules_f

s2,fusion.pay_element_types_vl petf,fusion.per_legislative_data_groups_vl ldg

where s1.element_type_id=s2.element_type_id

and petf.element_type_id=s2.element_type_id

and ldg.legislative_data_group_id=s1.legislative_data_group_id

and s1.status_processing_rule_id<>s2.status_processing_rule_id

and s1.status_processing_rule_id>s2.status_processing_rule_id

order by ldg.name;

Check the Status Processing Rules for the Element.Check if there are Multiple Rows available.

If there are More than one Status Processing Rules Delete one of the Rules and retest the issue,

 

If you are unable to delete the Status processing rules from UI, you can go for the HDL approach to delete the same,

Query2 : select * from fusion.pay_status_proc_rules_f where ELEMENT_TYPE_ID = <Use the Element Type ID from First Query>

 

Sample data File

METADATA|StatusProcessingRule|ElementId|LegislativeDataGroupName|StatusProcessingRuleId

DELETE|StatusProcessingRule|300000545904797|AE Legislative Data Group|300000545905167

File Name : PayrollElementDefinition.dat

use the StatusProcessingRuleId from Query 2.