Oracle Fusion - SQL - Project Plan Employee Assignment

SELECT prj.project_id, pre.person_id ,PPE.planning_start_date ,PPE.planning_end_date FROM pjf_class_codes_tl pcct , pjf_project_classes ppc , PJO_PLANNING_ELEMENTS PPE , PJF_RBS_ELEMENTS PRE , PJO_PLAN_TYPES_B PPT , PJO_PLAN_VERSIONS_B PPV , pjo_plan_lines PPL , pjf_projects_all_b prj WHERE pcct.language = 'US' AND pcct.class_code_id = ppc.class_code_id AND ppc.project_id = prj.project_id AND PPV.project_id = PRJ.project_id AND PPE.rbs_element_id = PRE.rbs_element_id AND PPT.PLAN_TYPE_CODE = 'PROJECT_PLAN' AND SYSDATE BETWEEN NVL(PPT.start_date,SYSDATE-1) AND NVL(PPT.end_date,SYSDATE+1) AND PPT.plan_type_id = PPV.plan_type_id AND PPV.plan_version_id = PPL.plan_version_id AND ppl.planning_element_id = ppe.planning_element_id AND PPV.CURRENT_PLAN_STATUS_FLAG = 'Y' AND PPV.PLAN_STATUS_CODE = 'B' AND PPE.planning_start_date <= :P_TO_DATE AND PPE.planning_end_date >= :P_FROM_DATE AND pre.person_id IS NOT NULL ORDER BY 1,2,3

Oracle Fusion - OIC - Helpful Points

OIC Helpful/Reference Points

Q1 - How to pass current date (SYSDATE) in required format in OIC

A1: fn:current-dateTime() => Send current date

To change the format, we can use the following

xp20:format-dateTime(DateTime,Format)

Example: xp20:format-dateTime(string(fn:current-dateTime()),'[MNn,3-3]-[YYYY]')

Q2 - How to add months in the date stored in the string variable in OIC integration?

A2: string(xsd:date('2016-12-25') + xsd:yearMonthDuration("P1M"))

Q3 - How to get the date by adding days to the current date in OIC integration?

A3: string(fn:current-dateTime()-xsd:dayTimeDuration(‘P30D’))