PER Saudi Contract Expiry
Description
Categories: BI Publisher
Imported from BI Publisher
Description: Saudi Contract Expiry Report
Application: Human Resources
Source: Saudi Contract Expiry Report (XML)
Short Name: PERSACTR_XML
DB package: PER_PERSACTR_XMLP_PKG
Description: Saudi Contract Expiry Report
Application: Human Resources
Source: Saudi Contract Expiry Report (XML)
Short Name: PERSACTR_XML
DB package: PER_PERSACTR_XMLP_PKG
Run
PER Saudi Contract Expiry and other Oracle EBS reports with Blitz Report™ on our demo environment
SELECT org.name ,people.employee_number ,people.full_name ,con.reference ,hr1.meaning type ,decode(con.ctr_information1,null,null,hr2.meaning) status ,con.active_start_date ,con.duration ,con.duration_units /*,NVL(to_date(con.ctr_information2,'RRRR/MM/DD HH24:MI:SS'), DECODE(duration_units,'D',(active_start_date+duration), 'W', (active_start_date+(duration*7)), 'M',(add_months(active_start_date,duration)), 'Y',(add_months(active_start_date,(duration*12))),'')) expiry_date,*/ ,fnd_date.date_to_displaydate(NVL(to_date(con.ctr_information2,'RRRR/MM/DD HH24:MI:SS'), DECODE(duration_units,'D',(active_start_date+duration), 'W', (active_start_date+(duration*7)), 'M',(add_months(active_start_date,duration)), 'Y',(add_months(active_start_date,(duration*12))),'')), calendar_aware=>1) expiry_date, PER_PERSACTR_XMLP_PKG.cf_1formula() CF_1 FROM per_all_assignments_f assg ,per_all_people people ,per_contracts con ,hr_lookups hr1 ,hr_lookups hr2 ,hr_all_organization_units org WHERE assg.person_id = people.person_id AND assg.ASSIGNMENT_TYPE='E' AND NVL(:P_DATE,sysdate) BETWEEN assg.effective_start_date AND assg.effective_end_date AND NVL(:P_DATE,sysdate) between people.effective_start_date AND people.effective_end_date AND assg.contract_id = con.contract_id AND hr1.lookup_type = 'CONTRACT_TYPE' AND hr1.lookup_code = con.type AND assg.organization_id = org.organization_id AND con.ctr_information_category = 'SA' AND nvl(con.ctr_information1,'BACHELOR_STATUS') = hr2.lookup_code (+) AND decode(:P_EMP_STATUS,null,nvl(con.ctr_information1,'0000'), :P_EMP_STATUS) = nvl(con.ctr_information1,'0000') AND hr2.lookup_type(+) = 'SA_EMPLOYMENT_STATUS' AND con.status like 'A-%' &l_org_condition &l_type_condition &l_status_condition AND org.business_group_id = :p_business_group_id AND NVL(to_date(ctr_information2,'RRRR/MM/DD HH24:MI:SS'), DECODE(duration_units,'D',(active_start_date+duration),'W',(active_start_date+(duration*7)), 'M',(add_months(active_start_date,duration)), 'Y',(add_months(active_start_date,(duration*12))),'')) BETWEEN NVL(:P_DATE,sysdate) AND DECODE(:P_EXP_UNITS, 'D',(NVL(:P_DATE,sysdate)+:P_EXP), 'W',(NVL(:P_DATE,sysdate)+(:P_EXP*7)), 'M',(add_months(NVL(:P_DATE,sysdate),:P_EXP)), 'Y',(add_months(NVL(:P_DATE,sysdate),(:P_EXP*12)))) ORDER BY org.name, expiry_date, full_name |
Parameter Name | SQL text | Validation | |
---|---|---|---|
Organization Structure |
|
LOV Oracle | |
Version |
|
LOV Oracle | |
Organization |
|
LOV Oracle | |
Contract Type |
|
LOV Oracle | |
Employment Status |
|
LOV Oracle | |
Expires In |
|
Number | |
Expires In Units |
|
LOV Oracle | |
Date |
|
Date |