SSP ShPP Birth Element Results

Description
Categories: BI Publisher, Human Resources
Application: SSP
Source: ShPP Birth Element Results(XML)
Short Name: SSPSHPBR
DB package: SSP_SHARED_BIRTH_RESULTS
select         pas.ASSIGNMENT_NUMBER      ASSIGNMENT_NUMBER,
	  substr(ppl.FULL_NAME,1,20) EMPLOYEE_NAME,
	  to_number(prv.RESULT_VALUE)           RESULT_VALUE,
	  prr.SOURCE_ID              ELEMENT_ENTRY_ID
from           pay_payroll_actions         pra,
               pay_assignment_actions      paa,
	           per_assignments_f           pas,
	           per_people_f                ppl,
               pay_run_results             prr,
	           pay_element_types_f         pet,
	           pay_element_classifications pec,
               pay_run_result_values       prv,
	  pay_input_values_f          piv 
where  pra.BUSINESS_GROUP_ID    = :P_BUSINESS_GROUP_ID 
and      pra.TIME_PERIOD_ID       = :P_TIME_PERIOD_ID
and      pra.PAYROLL_ID           = :P_PAYROLL_ID
and      pra.CONSOLIDATION_SET_ID = NVL(:P_CONSOLIDATION_SET,pra.CONSOLIDATION_SET_ID)
and      pra.PAYROLL_ACTION_ID    = paa.PAYROLL_ACTION_ID
and      paa.ASSIGNMENT_ID        = pas.ASSIGNMENT_ID 
and      pas.PERSON_ID            = ppl.PERSON_ID
and      paa.ASSIGNMENT_ACTION_ID = prr.ASSIGNMENT_ACTION_ID
and      prr.STATUS               = 'P'           
and      prr.ELEMENT_TYPE_ID      = pet.ELEMENT_TYPE_ID
and      pet.ELEMENT_NAME         in (:c_element_name1,:c_element_name2) 
and      pet.CLASSIFICATION_ID    = pec.CLASSIFICATION_ID
and      pec.CLASSIFICATION_NAME  = :c_classification_name
and      prr.RUN_RESULT_ID        = prv.RUN_RESULT_ID
and      prv.INPUT_VALUE_ID       =  piv.INPUT_VALUE_ID
and      piv.NAME                 = 'Amount'
and      prr.SOURCE_TYPE          = 'E'
and	:P_SESSION_DATE between pas.effective_start_date and pas.effective_end_date
and	:P_SESSION_DATE between ppl.effective_start_date and ppl.effective_end_date
and	:P_SESSION_DATE between pet.effective_start_date and pet.effective_end_date
&ORDER_BY_CLAUSE
Parameter Name SQL text Validation
Session Date
 
Date
Business Group
 
Number
Debug Flag
 
Sort Option
 
LOV Oracle
Period
 
LOV Oracle
Consolidation Set
 
LOV Oracle
Payroll Name
 
LOV Oracle