Introduction:
This blog has Projects Import FBDI Query that can be used to retrieve data as per Projects FBDI Format in Oracle Cloud application.
Cause of the issue:
Business wants to update the existing Projects information in Oracle Cloud Application.
How do we solve:
We have created below query to retrieve data as per Projects FBDI Format and required columns in Oracle Cloud application.
Query:
select ppav.name project_name,
ppav.segment1 project_number,
(select segment1
from pjf_projects_all_vl
where project_id = ppav.created_from_project_id) source_template_number,
(select name
from pjf_projects_all_vl
where project_id = ppav.created_from_project_id) source_template_name,
‘VISION’ Source_Application_Code,
hov.name Organization,
xep.NAME Legal_entity,
ppav.DESCRIPTION Project_Description,
pmgr.email Project_Manager_Email,
to_char(ppav.start_date,’YYYY/MM/DD’) project_start_date,
to_char(ppav.completion_date,’YYYY/MM/DD’) project_finish_date,
ppsv.project_status_name PROJECT_STATUS,
ppav.PROJECT_CURRENCY_CODE PROJECT_CURRENCY,
/*Product*/
ppav.ATTRIBUTE1,
/*Location*/
ppav.ATTRIBUTE2,
/*Function*/
ppav.ATTRIBUTE3
–ppav.closed_date closed_date,
from pjf_projects_all_vl ppav,
–pjf_project_types_vl pptv,
xle_entity_profiles xep,
pjf_project_statuses_vl ppsv,
hr_organization_v hov,
pjt_primaryprojmanager_v pmgr
where 1=1
–AND ppav.project_type_id = pptv.project_type_id(+)
AND xep.legal_entity_id = ppav.legal_entity_id
AND ppav.project_status_code = ppsv.project_status_code
AND ppav.carrying_out_organization_id = hov.organization_id
AND hov.classification_code = ‘PA_PROJECT_ORG’
AND trunc(sysdate) BETWEEN trunc(hov.effective_start_date) AND trunc(hov.effective_end_date)
AND ppav.project_id = pmgr.project_id(+)
order by ppav.name
