When implementing Oracle Services Procurement there are a number of steps in the process that can easily be controlled by a number of Alerts. The Alerts will notify you when a certain step in the process is not performed as expected. Examples are:

1. The Purchase Order for a Contingent Worker is approved but the Purchase Order is not yet assigned to a Contingent Worker Assignment in Oracle HR
2. Purchase Orders for Contingent Workers that are expiring within the next 14 days
3. There is a mismatches between the Start date on the Purchase Order and the Start date of the Contingent Worker Assignment in HR
4. Contingent Workers with an active assignment but without active user
5. Contingent Worker Requisitions that are not charged to the Clearing Project (CWK Project)

Alert for Approved Purchase Orders that are not yet assigned to a contingent worker assignment

select  	o.name
,		ph.segment1
,      	 	pl.line_num
,       	pv.vendor_name
,       	pl.contractor_first_name
,      		pl.contractor_last_name
,       	pl.start_date
, 		pl.expiration_date
,       	ph.approved_date
into    	&OPERATING_UNIT
, 		&PO_NUMBER
, 		&LINE_NUMBER
, 		&VENDOR_NAME
, 		&FIRST_NAME
, 		&LAST_NAME
, 		&START_DATE
, 		&END_DATE
, 		&APPROVED_DATE
from 		po_headers_all 			ph
, 		po_lines_all 			pl
, 		po_vendors 			pv
, 		po_vendor_sites_all 		vs
, 		hr_all_organization_units	o
where		ph.po_header_id = pl.po_header_id
and 		ph.vendor_id = pv.vendor_id
and 		ph.vendor_site_id = vs.vendor_site_id
and 		ph.org_id = o.organization_id
and 		ph.authorization_status = 'APPROVED'
and 		nvl(pl.closed_code,'OPEN') = 'OPEN'   
and 		nvl(pl.cancel_flag,'N') = 'N' 
and 		pl.job_id is not null
and 		ph.type_lookup_code = 'STANDARD'  
and 		pl.po_line_id NOT IN (select nvl(po_line_id,-1) from per_all_assignments_f)

Alert for Purchase Orders that are about to expire within the next 14 days without a new assignment for the Contingent Worker

select	ph.segment1
, 		pl.line_num
, 		pv.vendor_name
, 		emp.full_name
, 		pl.start_date
, 		pl.expiration_date
into		&PO_NUMBER
,		&LINE_NUMBER
,		&VENDOR
,		&CONTINGENT_WORKER
,		&START_DATE
,		EXPIRATION_DATE
from 		po_headers_all ph
, 		po_lines_all pl
, 		po_vendors pv
, 		per_all_assignments_f pa
, 		hr_employees emp
where 		ph.po_header_id = pl.po_header_id
and 		ph.vendor_id = pv.vendor_id
and 		pl.job_id is not null
and 		ph.type_lookup_code = 'STANDARD' 
and 		pl.po_line_id = pa.po_line_id
and 		pa.person_id = emp.employee_id
and 		nvl(pl.closed_code,'OPEN') = 'OPEN' 
and 		nvl(pl.cancel_flag,'N') = 'N'
and 		ph.authorization_status = 'APPROVED'
and 		nvl(ph.closed_code,'OPEN') = 'OPEN'
and 		sysdate between pa.effective_start_date and pa.effective_end_date
and 		pl.expiration_date between sysdate and sysdate+14
and 		pa.person_id not in    (select 	a.person_id 
					from 	per_all_assignments_f a
					, 	po_lines_all b 
					where 	a.po_line_id = b.po_line_id 
					and 	b.expiration_date > sysdate+14)