Query to display the Project query with last run date.(fetch the data based on the last run date)

 

WITH last_success_date_tbl AS 

(

   SELECT

( 

      SELECT

         NVL(MAX(rhv.processstart), LAST_DAY(ADD_MONTHS(SYSDATE, - 1)) + 1) + 1/1440*-90

      FROM

         fusion_ora_ess.request_property_view rpv , 

fusion_ora_ess.request_history_view  rhv 

      WHERE

         1 = 1 

         AND rpv.name              = 'reportID' 

         AND rpv.value             LIKE '/Custom/XX Custom/Projects/Interfaces/XX PPM POET Details to Coupa.xdo' 

         AND rpv.requestid         = rhv.requestid 

         AND rhv.executable_status = 'SUCCEEDED' 

         AND rhv.processend        IS NOT NULL ) last_success_date 

     FROM dual

)

SELECT  'X' as key,

'"'||SUBSTR(ppa.segment1||' - '||ppt.name,1,78)||'"' "Name",

CASE

WHEN ppa.project_status_code = 'ACTIVE' AND NVL(ppa.completion_date,SYSDATE+1)>SYSDATE THEN 'Yes'

ELSE 'No'

END "Active",

'PROJECT_TASK' "Lookup",

'"'||SUBSTR(ppa.segment1||' - '||ppt.name,1,78)||'"' "Description",

ppa.segment1 "External Ref Num",

NULL "External Ref Code",

/*CASE

       WHEN ppa.org_id = 300000006621001 THEN 'XX_USA'

       WHEN poev.name LIKE 'QC%' THEN 'XX_QBC'

       ELSE 'XX_CAN'

   END AS */

     CASE

       WHEN hou.name = 'XX US Business Unit' 

   THEN 'XX_USA'

   WHEN hou.name = 'XX CA Business Unit'  

   and (cfm.VALUE_CONSTANT like 'M%' or cfm.VALUE_CONSTANT like 'P%')

   and poev.name LIKE 'QC%'

   THEN 'XX_QLC'

   WHEN hou.name = 'XX CA Business Unit' 

   and (cfm.VALUE_CONSTANT like 'M%' or cfm.VALUE_CONSTANT like 'P%')

   and poev.name NOT LIKE 'QC%'

   THEN 'XX_ULC'

   WHEN hou.name = 'XX CA Business Unit' 

   and (cfm.VALUE_CONSTANT = 'A01674')

   and poev.name NOT LIKE 'QC%'

   THEN 'XX_ULC'

   WHEN hou.name = 'XX CA Business Unit' 

   and (cfm.VALUE_CONSTANT = 'A01674')

   and poev.name LIKE 'QC%'

   THEN 'XX_QLC'

   WHEN hou.name = 'XX CA Business Unit' 

   and (cfm.VALUE_CONSTANT not like 'M%' or cfm.VALUE_CONSTANT not like 'P%' or cfm.VALUE_CONSTANT <> 'A01674')

   and poev.name NOT LIKE 'QC%'

   THEN 'XX_CAN'

   WHEN hou.name = 'XX CA Business Unit' 

   and (cfm.VALUE_CONSTANT not like 'M%' or cfm.VALUE_CONSTANT not like 'P%' or cfm.VALUE_CONSTANT <> 'A01674')

   and poev.name  LIKE 'QC%'

   THEN 'XX_QBC'

   END AS    "Chart of Accounts",--JIRA EPM-1041, JIRA-EPM-1077

NULL "Parent External Ref Code",

NULL "Default"

FROM 

    pjf_Organizations_Expend_V poev,

pjf_projects_all_b ppa,

pjf_projects_all_tl ppt,

pjo_plan_versions_b ppv,

pjo_plan_types_b pptb,

pjo_plan_types_tl pptt,

fnd_lookup_values flv,

hr_operating_units hou,

last_success_date_tbl,

XLA_MAPPING_SET_VALUES cfm

WHERE 

--  poev.name LIKE '%QC%' AND

    ppa.Carrying_Out_Organization_ID = poev.Organization_ID

and cfm.mapping_set_code = 'PPM_DEP_COM_SET'

and cfm.INPUT_VALUE_CONSTANT1= substr(poev.name,1,6)

AND ppa.project_id = ppt.project_id

AND ppa.project_id = ppv.project_id

AND pptb.plan_type_id = pptt.plan_type_id 

AND ppv.plan_type_id = pptb.plan_type_id

AND ppv.plan_status_code = flv.lookup_code

AND ppt.LANGUAGE = USERENV('LANG')

AND flv.LANGUAGE = USERENV('LANG')

AND pptt.LANGUAGE = USERENV('LANG') 

AND flv.lookup_type = 'PJO_PLAN_STATUS'

AND pptt.name = 'Cumulative Funding Amount'

AND flv.meaning = 'Baseline' 

AND hou.organization_id = ppa.ORG_ID

AND (:p_first_run = 'Yes' OR ppa.last_update_date >= last_success_date_tbl.last_success_date OR ppv.baselined_date >= last_success_date_tbl.last_success_date)

AND ppa.project_id IN (SELECT DISTINCT(ppb.project_id) 

FROM pjf_proj_elements_b ppb,

pjf_proj_elements_tl pptl

WHERE ppb.proj_element_id = pptl.proj_element_id  

AND ppb.chargeable_flag = 'Y' 

AND NVL(ppb.completion_date,SYSDATE+1)>SYSDATE

AND pptl.name NOT IN (SELECT meaning 

FROM fnd_lookup_values 

WHERE lookup_type = 'XX_EXCLUDED_TASKS_COUPA' 

AND language = USERENV('LANG'))

)

AND ((:p_first_run = 'Yes' AND ppa.project_status_code = 'ACTIVE' AND NVL(ppa.completion_date,SYSDATE+1)>SYSDATE) OR :p_first_run = 'No')

AND (:p_first_run = 'Yes' OR NOT((ppa.creation_date >= last_success_date_tbl.last_success_date) AND (NVL(ppa.completion_date,SYSDATE+1)<SYSDATE OR ppa.project_status_code != 'ACTIVE')))

UNION 

SELECT  'X' as key, 

'"'||SUBSTR(ppeb.element_number||' - '||ppet.name,1,78)||'"' "Name",

CASE

WHEN ppeb.chargeable_flag = 'Y' AND NVL(ppeb.completion_date,SYSDATE+1)>SYSDATE THEN 'Yes'

ELSE 'No'

END "Active",

'PROJECT_TASK' "Lookup",

'"'||SUBSTR(ppeb.element_number||' - '||ppet.name,1,78)||'"' "Description",

TO_CHAR(ppeb.proj_element_id) "External Ref Num",

NULL "External Ref Code",

/*CASE

       WHEN ppa.org_id = 300000006621001 THEN 'XX_USA'

       WHEN poev.name LIKE 'QC%' THEN 'XX_QBC'

       ELSE 'XX_CAN'

   END AS "Chart of Accounts",*/--JIRA EPM-1041, EPM-1077

CASE

       WHEN hou.name = 'XX US Business Unit'  THEN 'XX_USA'

   WHEN hou.name = 'XX CA Business Unit'   

   and (cfm.VALUE_CONSTANT like 'M%' or cfm.VALUE_CONSTANT like 'P%')

   and poev.name LIKE 'QC%'

   THEN 'XX_QLC'

   WHEN hou.name = 'XX CA Business Unit' 

   and (cfm.VALUE_CONSTANT like 'M%' or cfm.VALUE_CONSTANT like 'P%')

   and poev.name NOT LIKE 'QC%'

   THEN 'XX_ULC'

   WHEN hou.name = 'XX CA Business Unit' 

   and (cfm.VALUE_CONSTANT = 'A01674')

   and poev.name NOT LIKE 'QC%'

   THEN 'XX_ULC'

   WHEN hou.name = 'XX CA Business Unit' 

   and (cfm.VALUE_CONSTANT = 'A01674')

   and poev.name LIKE 'QC%'

   THEN 'XX_QLC'

   WHEN hou.name = 'XX CA Business Unit'  

   and (cfm.VALUE_CONSTANT not like 'M%' or cfm.VALUE_CONSTANT not like 'P%' or cfm.VALUE_CONSTANT <> 'A01674')

   and poev.name NOT LIKE 'QC%'

   THEN 'XX_CAN'

   WHEN hou.name = 'XX CA Business Unit' 

   and (cfm.VALUE_CONSTANT not like 'M%' or cfm.VALUE_CONSTANT not like 'P%' or cfm.VALUE_CONSTANT <> 'A01674')

   and poev.name  LIKE 'QC%'

   THEN 'XX_QBC'

    END AS "Chart of Accounts",

ppa.segment1 "Parent External Ref Code",

NULL "Default"

FROM 

    pjf_Organizations_Expend_V poev,

pjf_proj_elements_b ppeb,

pjf_proj_elements_tl ppet,

pjf_projects_all_b ppa,

pjo_plan_versions_b ppv,

pjo_plan_types_b pptb,

pjo_plan_types_tl pptt,

fnd_lookup_values flv,

last_success_date_tbl,

hr_operating_units hou,

XLA_MAPPING_SET_VALUES cfm

WHERE 

--poev.name LIKE 'QC%'  and

    ppa.Carrying_Out_Organization_ID = poev.Organization_ID

AND ppeb.proj_element_id = ppet.proj_element_id 

AND hou.organization_id = ppa.ORG_ID

and cfm.mapping_set_code = 'PPM_DEP_COM_SET'

and cfm.INPUT_VALUE_CONSTANT1= substr(poev.name,1,6)

AND ppeb.project_id = ppa.project_id 

AND ppa.project_id = ppv.project_id

AND ppv.plan_type_id = pptb.plan_type_id 

AND pptb.plan_type_id = pptt.plan_type_id 

AND ppv.plan_status_code = flv.lookup_code 

AND ppet.LANGUAGE = USERENV('LANG') 

AND flv.LANGUAGE = USERENV('LANG') 

AND pptt.LANGUAGE = USERENV('LANG') 

AND NVL(ppa.completion_date,SYSDATE+1)>SYSDATE 

AND ppa.project_status_code = 'ACTIVE' 

AND flv.lookup_type = 'PJO_PLAN_STATUS'

AND pptt.name = 'Cumulative Funding Amount' 

AND flv.meaning = 'Baseline' 

AND ppeb.object_type = 'PJF_TASKS' 

AND (:p_first_run = 'Yes' OR ppeb.last_update_date >= last_success_date_tbl.last_success_date OR ppv.baselined_date >= last_success_date_tbl.last_success_date)

AND ppet.name NOT IN (SELECT meaning 

FROM fnd_lookup_values 

WHERE lookup_type = 'XX_EXCLUDED_TASKS_COUPA' 

AND language = USERENV('LANG')

AND NVL(end_date_active,SYSDATE+1)>SYSDATE) 

AND ((:p_first_run = 'Yes' AND ppeb.chargeable_flag = 'Y' AND NVL(ppeb.completion_date,SYSDATE+1)>SYSDATE) OR :p_first_run = 'No')

AND (:p_first_run = 'Yes' OR NOT((ppeb.creation_date >= last_success_date_tbl.last_success_date) AND (NVL(ppeb.completion_date,SYSDATE+1)<SYSDATE)))

UNION

SELECT '-99' AS KEY,

NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL

FROM dual

ORDER BY 9 DESC,1