PAY Employee Run Results

Description
Categories: BI Publisher
Application: Payroll
Source: Employee Run Results Report (XML)
Short Name: PAYUSRRS_XML
DB package: PAY_PAYUSRRS_XMLP_PKG
SELECT    &p_hint_clause,
--SELECT    &p_hint_clause
    pec.CLASSIFICATION_NAME, 
	peo.FULL_NAME, 
	paf.ASSIGNMENT_NUMBER, 
	paa.TAX_UNIT_ID, 
	ppa.EFFECTIVE_DATE, 
	paa.ASSIGNMENT_ACTION_ID, 
	pet.ELEMENT_NAME, 
	pet.ELEMENT_INFORMATION_CATEGORY,  
	pet.ELEMENT_INFORMATION1,
	rrs.JURISDICTION_CODE, 
	rrsv.RESULT_VALUE, 
	piv.NAME, 
	PAY_PAYUSRRS_XMLP_PKG.gre_nameformula(paa.TAX_UNIT_ID) GRE_NAME, 
	PAY_PAYUSRRS_XMLP_PKG.element_categoryformula(pet.ELEMENT_INFORMATION_CATEGORY, pet.ELEMENT_INFORMATION1) ELEMENT_CATEGORY, 
	--&RESULT_VALUE RVALUE
	PAY_PAYUSRRS_XMLP_PKG.RVALUEFormula(rrsv.RESULT_VALUE) RVALUE
FROM 	
	PAY_INPUT_VALUES_F           piv, 
	PAY_ELEMENT_CLASSIFICATIONS  pec, 
	PAY_ELEMENT_TYPES_F          pet, 
	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 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 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, 
     pec.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
Parameter Name SQL text Validation
Start Date
 
Date
End Date
 
Date
Selection Criterion
 
LOV Oracle
Employee Name
 
LOV Oracle
Assignment Number
 
LOV Oracle
Assignment Set
 
LOV Oracle
Government Reporting Entity
 
LOV Oracle
Classification
 
LOV Oracle
PER_ID
 
Number
PER_CHAR
 
PER_CHAR
 
PER_CHAR