PER Saudi Contract Expiry

Description
Categories: BI Publisher, Human Resources
Application: Human Resources
Source: Saudi Contract Expiry Report (XML)
Short Name: PERSACTR_XML
DB package: PER_PERSACTR_XMLP_PKG
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
Business Group Id
 
Number
Date
 
Date
Expires In Units
 
LOV Oracle
Expires In
 
Number
Employment Status
 
LOV Oracle
Contract Type
 
LOV Oracle
Organization
 
LOV Oracle
Version
 
LOV Oracle
Organization Structure
 
LOV Oracle