PAY Employee Run Results Report (CA)

SELECT    &p_hint_clause,
    pect.CLASSIFICATION_NAME, 
	peo.FULL_NAME, 
	paf.ASSIGNMENT_NUMBER, 
	paa.TAX_UNIT_ID, 
	ppa.EFFECTIVE_DATE, 
	paa.ASSIGNMENT_ACTION_ID, 
	petl.ELEMENT_NAME, 
	pet.ELEMENT_INFORMATION_CATEGORY,  
	pet.ELEMENT_INFORMATION1,
	rrs.JURISDICTION_CODE, 
	rrsv.RESULT_VALUE, 
	pivt.NAME, 
	PAY_PAYCARRS_XMLP_PKG.gre_nameformula(paa.TAX_UNIT_ID) GRE_NAME, 
	PAY_PAYCARRS_XMLP_PKG.element_categoryformula(pet.ELEMENT_INFORMATION_CATEGORY, pet.ELEMENT_INFORMATION1) ELEMENT_CATEGORY, 
	PAY_PAYCARRS_XMLP_PKG.RVALUEFormula(rrsv.RESULT_VALUE) RVALUE
FROM 	
	PAY_INPUT_VALUES_F           piv, 
	PAY_INPUT_VALUES_F_TL				 pivt,
	PAY_ELEMENT_CLASSIFICATIONS  pec, 
	PAY_ELEMENT_CLASSIFICATIONS_TL  pect, 
	PAY_ELEMENT_TYPES_F          pet,
	PAY_ELEMENT_TYPES_F_TL       petl, 
	PAY_RUN_RESULT_VALUES        rrsv, 
	PAY_RUN_RESULTS              rrs, 
	PAY_PAYROLL_ACTIONS          ppa, 
	PAY_ASSIGNMENT_ACTIONS       paa, 
	PER_ASSIGNMENTS_F            paf, 
	PER_PEOPLE_F                 peo
&p_from_clause
  WHERE  
      paf.ASSIGNMENT_ID = paa.ASSIGNMENT_ID 
  AND paa.ASSIGNMENT_ACTION_ID = rrs.ASSIGNMENT_ACTION_ID 
  AND ppa.PAYROLL_ACTION_ID = paa.PAYROLL_ACTION_ID 
  AND ppa.EFFECTIVE_DATE BETWEEN :p_start_date AND :p_end_date
  AND ppa.EFFECTIVE_DATE BETWEEN paf.EFFECTIVE_START_DATE AND paf.EFFECTIVE_END_DATE 
	AND piv.input_value_id = pivt.input_value_id
	AND pivt.language = userenv('LANG')
  AND ppa.DATE_EARNED BETWEEN piv.EFFECTIVE_START_DATE 
                                 AND piv.EFFECTIVE_END_DATE 
  AND ppa.EFFECTIVE_DATE BETWEEN peo.EFFECTIVE_START_DATE 
                                 AND peo.EFFECTIVE_END_DATE 
  AND ppa.DATE_EARNED BETWEEN pet.EFFECTIVE_START_DATE 
  AND pet.EFFECTIVE_END_DATE AND rrs.RUN_RESULT_ID = rrsv.RUN_RESULT_ID
  AND rrs.ELEMENT_TYPE_ID    = pet.ELEMENT_TYPE_ID 
  AND pet.CLASSIFICATION_ID  = pec.CLASSIFICATION_ID 
	AND pec.CLASSIFICATION_ID  = pect.CLASSIFICATION_ID
	AND pet.element_type_id = petl.element_type_id
	AND petl.language = userenv('LANG')
	AND pect.language = userenv('LANG')
  AND rrsv.INPUT_VALUE_ID    = piv.INPUT_VALUE_ID 
  AND EXISTS ( SELECT 'Y' FROM pay_balance_feeds_f pbff 
               WHERE pbff.input_value_id = piv.input_value_id )
    AND decode ( rrsv.result_value , null , 'N' , '0' , 'N' , 'Y' ) = 'Y' 
    AND peo.BUSINESS_GROUP_ID = :p_business_group_id
  AND peo.PERSON_ID = paf.PERSON_ID
  &p_where_clause
  ORDER BY paa.TAX_UNIT_ID ASC, 
    paf.ASSIGNMENT_NUMBER ASC, 
     pect.CLASSIFICATION_NAME ASC,
     ELEMENT_CATEGORY,--ADDED AS A PART OF FIX
        pet.ELEMENT_NAME ASC,
	     ppa.EFFECTIVE_DATE ASC,
         rrs.JURISDICTION_CODE ASC,
         paa.ASSIGNMENT_ACTION_ID