SSP Maximum SSP Weeks

Description
Categories: BI Publisher
Application: SSP
Source: Maximum SSP Weeks (XML)
Short Name: SSPRPWKS_XML
DB package: SSP_SSPRPWKS_XMLP_PKG
select
          O_pay.PAYROLL_NAME PAYROLL_NAME,
          substr(o_ppl.FULL_NAME,1,30) EMPLOYEE_NAME,
                                                                 EMPLOYEE_NUMBER,
                                                                 NATIONAL_IDENTIFIER,
                                                                 O_SSP.PIW_ID,
          min(O_PAB.SICKNESS_START_DATE)   START_DATE,
          to_char(max(DATE_TO))                           END_DATE ,
          sum(SSP_WEEKS)                     TOTAL_SSP_WEEKS
from     per_assignments_f            O_pas,
             per_people_x                      O_ppl,
             ssp_ssp_entries_v             O_SSP,
             pay_payrolls_x                   O_PAY,
             per_absence_attendances      O_PAB
where    O_pas.BUSINESS_GROUP_ID + 0   = :P_BUSINESS_GROUP_ID
and        O_pas.ASSIGNMENT_ID        = O_ssp.ASSIGNMENT_ID
and        O_pas.PERSON_ID                   = O_ppl.PERSON_ID
and        O_PAB.ABSENCE_ATTENDANCE_ID = O_ssp.PIW_ID
&L_PAYROLL_ID
and        O_pas.PAYROLL_ID + 0          = O_pay.PAYROLL_ID
and        :P_SESSION_DATE    between 	O_pas.EFFECTIVE_START_DATE and   O_pas.EFFECTIVE_END_DATE
--and     56 >= (select :P_SESSION_DATE - max(DATE_TO)
and     56 >= (select trunc(:P_SESSION_DATE) - max(DATE_TO)
                     from   SSP_SSP_ENTRIES_V      I_ssp,
                               PER_ASSIGNMENTS_F     I_pas
                     where I_ssp.PIW_ID =O_ssp.PIW_ID
                     and     I_ssp.ASSIGNMENT_ID = O_ssp.ASSIGNMENT_ID
                     and     I_pas.ASSIGNMENT_ID = I_ssp.ASSIGNMENT_ID
                     and     :P_SESSION_DATE between O_pas.EFFECTIVE_START_DATE and O_pas.EFFECTIVE_END_DATE)
group by PAYROLL_NAME,
                substr(O_ppl.FULL_NAME,1,30) ,
                EMPLOYEE_NUMBER,
                NATIONAL_IDENTIFIER,
                PIW_ID
having sum(SSP_WEEKS) >= 21
order by
                PAYROLL_NAME,
                EMPLOYEE_NAME
Parameter Name SQL text Validation
Payroll Name
 
LOV Oracle
Business Group
 
Number
Session Date
 
Date