ENGINATICS SYSTEM_ADMINISTRATOR SYSADMIN C6E085D4728E51A9E05362FB09050512Y 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 SSP SSPRPWKS_XML US SSP Maximum SSP Weeks Imported from BI Publisher Description: Maximum SSP Weeks Report Application: SSP Source: Maximum SSP Weeks (XML) Short Name: SSPRPWKS_XML DB package: SSP_SSPRPWKS_XMLP_PKG BI Publisher &l_payroll_id :c_business_group_name :c_payroll_name :c_report_subtitle :l_payroll_id :lp_session_date :p_business_group_id :p_conc_request_id :p_payroll :p_session_date 1 -30 :p_business_group_id Number fnd_profile.value('PER_BUSINESS_GROUP_ID') Y US Business Group Business Group ID 2 -20 :p_session_date Date select effective_date from fnd_sessions where session_id=userenv('SESSIONID') Y US Session Date Session Date 3 10 :p_payroll LOV Oracle PY_SRS_PAYROLL Y select pay.payroll_id id, pay.payroll_name value, null description from pay_payrolls_f pay,fnd_sessions fnd where pay.business_group_id= fnd_global.per_business_group_id and fnd.session_id=userenv('sessionid') and fnd.effective_date between pay.effective_start_date and pay.effective_end_date order by pay.payroll_name US Payroll Name Payroll Name