SSP SMP Element Results

Description
Categories: BI Publisher, Human Resources
Application: SSP
Source: SMP Element Results (XML)
Short Name: SMPRPELR_XML
DB package: SSP_SMPRPELR_XMLP_PKG
            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_x           pas,
                  per_people_x                ppl,
                              pay_run_results             prr,
                  pay_element_types_x         pet,
                  pay_element_classifications pec,
                              pay_run_result_values       prv,
                  pay_input_values_x          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 ('Statutory Maternity Pay','SMP Corrections')
            and      pet.CLASSIFICATION_ID    = pec.CLASSIFICATION_ID
            and      pec.CLASSIFICATION_NAME  = 'SMP Non Payment'
            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'
            &P_SORT_OPTION_m
Parameter Name SQL text Validation
Session Date
 
Date
Business Group
 
Number
Sort Option
 
LOV Oracle
Period
 
LOV Oracle
Consolidation Set
 
LOV Oracle
Payroll Name
 
LOV Oracle