Description:
Oracle Projects provides default processes for each workflow. You can modify the default processes and create additional methods to accommodate the needs of your business, using the Oracle Workflow Builder
SELECT pou.NAME carrying_out_org_name
,ppa.NAME project_name
,ppa.segment1 project_num
,ppa.segment1 project_number
–,TO_CHAR(ppa.completion_date, ‘DD-MON-YYYY’, ‘NLS_DATE_LANGUAGE = american’) completion_date
–,ppa.description
–,TO_CHAR(ppp.end_date_active, ‘DD-MON-YYYY’, ‘NLS_DATE_LANGUAGE = american’) end_date_active
,TO_CHAR((SELECT MAX (a.effective_start_date)
FROM per_all_people_f a, per_person_types_tl b, per_assignments_f c
WHERE 1=1
AND a.person_id=c.person_id(+)
AND b.person_type_id=c.person_type_id(+)
AND b.user_person_type=’Employee’
AND a.person_id = pap.person_id), ‘DD-MON-YYYY’, ‘NLS_DATE_LANGUAGE = american’) effective_start_date
,TO_CHAR((pap.effective_start_date-1), ‘DD-MON-YYYY’, ‘NLS_DATE_LANGUAGE = american’) effective_end_date
,pap.person_number employee_number
,ppn.full_name
,prt.project_role_name project_role
,pps.project_status_name
–,ppt.project_type
–,TO_CHAR(ppp.start_date_active, ‘DD-MON-YYYY’, ‘NLS_DATE_LANGUAGE = american’) start_date_active
–,ppa.attribute1 OMS
— ,NULL auto_billing_frequency
FROM pjf_projects_all_vl ppa,
pjf_project_types_vl ppt,
hr_all_organization_units pou,
pjf_project_statuses_vl pps,
pjf_project_parties ppp,
–pa_role_controls prc,
pjf_proj_role_types_v prt,
per_all_people_f pap,
per_person_names_f ppn,
per_person_types_tl pptl,
per_assignments_f paf
WHERE ppa.project_type_id = ppt.project_type_id
AND ppa.project_status_code = pps.project_status_code
AND ppa.carrying_out_organization_id = pou.organization_id
AND ppp.project_id = ppa.project_id
AND ppp.project_role_id = prt.project_role_id
AND ppp.resource_source_id = pap.person_id
AND ppp.resource_source_id = ppn.person_id
AND ppn.name_type = ‘GLOBAL’
AND ppp.object_type = ‘PA_PROJECTS’
–AND ppp.resource_type_id = 101
–AND ppp.project_role_id = prc.project_role_id
–AND prc.role_control_code = ‘ALLOW_AS_PROJ_MEMBER’
AND TRUNC (SYSDATE) BETWEEN pap.effective_start_date
AND pap.effective_end_date
AND UPPER(pps.project_status_name) IN (‘SUBMITTED’, ‘ACTIVE’)
AND pap.person_id=paf.person_id(+)
AND pptl.person_type_id=paf.person_type_id(+)
AND pptl.user_person_type=’Ex-employee’
Order By pap.effective_end_date,ppn.full_name
Summary:
This Post described the script Open Project with Terminated PMs or PDs in Oracle EBS.
Queries?
Do drop a note by writing us at doyen.ebiz@gmail.com or use the comment section below to ask your questions.
