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

Query for Intercompany Imbalance Report

 SELECT A.*, A.US_Ledger_amount + A.CA_Ledger_amount Imbalance

FROM (

    SELECT  --LEDGER             ,

Comp_Inter_acct    ,

SUM(US_Ledger_amount) US_Ledger_amount   ,

SUM(CA_Ledger_amount) CA_Ledger_amount   ,

Accounting_Year    ,

Accounting_Period

FROM  

( SELECT  

GL.NAME LEDGER,

CASE WHEN GL.NAME = 'XXXXXXXXXXXXXX'

THEN GCC.SEGMENT1||'-'||GCC.SEGMENT7||'-'|| GCC.SEGMENT4

WHEN GL.NAME = 'XXXXXXXXX'

THEN GCC.SEGMENT7||'-'||GCC.SEGMENT1||'-'|| GCC.SEGMENT4

END Comp_Inter_acct,

CASE WHEN GL.NAME = 'XXXXXXXXXXX'

THEN SUM( NVL(GB.PERIOD_NET_DR,0) -  NVL(GB.PERIOD_NET_CR ,0 )) 

   ELSE 0 

   END US_Ledger_amount,

CASE WHEN GL.NAME = 'XXXXXXXX'

-- AND ((GB.translated_flag is null) or (GB.translated_flag = 'Y'))

AND ((GB.translated_flag = 'N') or (GB.translated_flag = 'Y'))

THEN SUM( NVL(GB.PERIOD_NET_DR,0) -  NVL(GB.PERIOD_NET_CR ,0 )) 

ELSE 0 

END CA_Ledger_amount,

GB.PERIOD_YEAR Accounting_Year,

GB.PERIOD_NUM Accounting_Period

FROM GL_CODE_COMBINATIONS GCC,

GL_BALANCES GB,

GL_LEDGERS GL

-- GL_JE_HEADERS GJH,

-- GL_JE_LINES GJL

WHERE 1=1

AND GCC.SEGMENT4 IN ('370500') --, '370000') 

AND  NVL(GB.PERIOD_NET_DR,0) -  NVL(GB.PERIOD_NET_CR ,0 ) != 0

-- AND GCC.SEGMENT1||'-'||GCC.SEGMENT7||'-'|| GCC.SEGMENT4 IN ( 'Z01378-C01031-370500')

-- AND GB.PERIOD_NAME  = 'May-24' 

AND GCC.CODE_COMBINATION_ID = GB.CODE_COMBINATION_ID

-- AND GB.CURRENCY_CODE = GL.CURRENCY_CODE

AND GB.LEDGER_ID = GL.LEDGER_ID  

AND GB.CURRENCY_CODE = 'USD'

AND GL.NAME IN (  XXXXXXXXXX )

AND GL.LEDGER_ID     IN (SELECT gl.ledger_id

FROM fun_user_role_data_asgnmnts f,

per_roles_dn_vl p,

per_users pu,

gl_ledgers gl

   WHERE p.role_common_name = f.role_name

AND pu.user_guid = f.user_guid

AND gl.ledger_id = f.ledger_id

AND f.active_flag = 'Y' 

and upper(pu.username) = upper(:xdo_user_name))

AND GB.PERIOD_YEAR = :P_YEAR

AND GB.PERIOD_NUM =   :P_NUM

-- AND GCC.SEGMENT4 =   NVL(:P_ACCOUNT, GCC.SEGMENT4)  

-- AND GJH.JE_HEADER_ID = GJL.JE_HEADER_ID

-- AND GJH.ledger_id = GJL.ledger_id

-- AND GJH.LEDGER_ID = GL.LEDGER_ID  

-- AND GCC.CODE_COMBINATION_ID = GJL.CODE_COMBINATION_ID 

-- AND GJH.status = 'P'

-- AND GJL.PERIOD_NAME  = 'May-24'  

-- AND GB.PERIOD_NAME =   GJL.PERIOD_NAME

-- AND GB.LEDGER_ID = GJL.LEDGER_ID  

-- AND GB.CODE_COMBINATION_ID = GJL.CODE_COMBINATION_ID 

GROUP BY GL.NAME, GCC.SEGMENT1, GCC.SEGMENT7, GCC.SEGMENT4, 

GB.TRANSLATED_FLAG, GB.PERIOD_YEAR, GB.PERIOD_NUM 

)

WHERE 1=1

   AND (US_Ledger_amount + CA_Ledger_amount) != 0    

GROUP BY --LEDGER             ,

Comp_Inter_acct    ,

Accounting_Year    ,

Accounting_Period

)A

WHERE 1=1

AND (A.US_Ledger_amount + A.CA_Ledger_amount) != 0

Query for Absence and Overlapping employees of Absence

WITH Main_Q as

(SELECT distinct * FROM(

SELECT papf.person_id,

       papf.person_number,

       ppnf.display_name

       employee_name,

       pasf_Sup.manager_name

       manager_name,

       pjft.name

       job_name,

       pgft.name

       grade_name,

       --abt.absence_type_id,

       abt.name

       absence_type,

   paam.ASS_ATTRIBUTE1 sector,

   paam.ASS_ATTRIBUTE2 unit,

       (SELECT DISTINCT( pd.name )

        FROM   per_departments pd

        WHERE  pd.organization_id = paam.organization_id

               AND pd.effective_start_date =

                   (SELECT Max(pd1.effective_start_date)

                    FROM   per_departments pd1

                    WHERE

                   pd1.organization_id = pd.organization_id

       ))

       department_name,

       --To_char(apae.start_date, 'DD-MON-YYYY', 'NLS_DATE_LANGUAGE = AMERICAN')

       apae.start_date start_date,

      -- To_char(apae.end_date, 'DD-MON-YYYY', 'NLS_DATE_LANGUAGE = AMERICAN')

       apae.end_date end_date,

       apae.duration,

   apae.start_date order_by_date,

   apae.attribute1 Delegated_Person

FROM   anc_per_abs_entries apae,

       per_periods_of_service pps,

       anc_absence_types_vl abt,

       per_all_people_f papf,

       per_person_names_f ppnf,

       (SELECT ppnf2.full_name manager_name,

               pasf.person_id

        FROM   per_assignment_supervisors_f pasf,

               per_person_names_f ppnf2

        WHERE  pasf.manager_id = ppnf2.person_id

               AND pasf.manager_type = 'LINE_MANAGER'

               AND Trunc(SYSDATE) BETWEEN pasf.effective_start_date AND

                                          pasf.effective_end_date

               AND Trunc(SYSDATE) BETWEEN ppnf2.effective_start_date AND

                                          ppnf2.effective_end_date

               AND ppnf2.name_type = 'GLOBAL') pasf_Sup,

       per_all_assignments_m paam,

       per_jobs_f_vl pjft,

       per_grades_f_vl pgft,

   hr_organization_units bu,

       hr_organization_units le,

   per_asg_responsibilities resp

   --hr_organization_units dept

WHERE  apae.period_of_service_id = pps.period_of_service_id

       AND apae.absence_type_id = abt.absence_type_id

   AND apae.ABSENCE_STATUS_CD <> 'ORA_WITHDRAWN'

   AND apae.APPROVAL_STATUS_CD = 'APPROVED'

       AND abt.name = 'Tentative Leave Plan'

       AND pps.person_id = papf.person_id

       AND papf.person_id = ppnf.person_id

       AND papf.person_id = pasf_Sup.person_id(+)

       AND papf.person_id = paam.person_id(+)

       AND paam.primary_assignment_flag(+) = 'Y'

       --AND paam.assignment_type = 'E'

       --AND paam.effective_latest_change = 'Y'

       AND paam.job_id = pjft.job_id (+)

       AND paam.grade_id = pgft.grade_id (+)

   AND paam.business_unit_id = bu.organization_id

       AND paam.legal_entity_id = le.organization_id

   AND resp.person_id = HRC_SESSION_UTIL.get_user_personid

       AND resp.responsibility_type ='HR_REP'

       AND resp.status = 'Active'

       AND  resp.legal_entity_id = paam.legal_entity_id

   --AND resp.BUSINESS_UNIT_ID=paam.BUSINESS_UNIT_ID

   --AND resp.organization_id =paam.organization_id 

       AND TRUNC(sysdate) BETWEEN resp.start_date AND NVL(resp.end_date,TRUNC(sysdate))

       AND Trunc(SYSDATE) BETWEEN pjft.effective_start_date(+) AND

                                  pjft.effective_end_date(+)

       AND Trunc(SYSDATE) BETWEEN pgft.effective_start_date(+) AND

                                  pgft.effective_end_date(+)

       AND Trunc(SYSDATE) BETWEEN paam.effective_start_date AND

                                  paam.effective_end_date

       AND Trunc(SYSDATE) BETWEEN abt.effective_start_date AND

                                  abt.effective_end_date

       AND Trunc(SYSDATE) BETWEEN papf.effective_start_date AND

                                  papf.effective_end_date

       AND Trunc(SYSDATE) BETWEEN ppnf.effective_start_date AND

                                  ppnf.effective_end_date

       AND ppnf.name_type = 'GLOBAL'

      --AND papf.person_number IN ( '0015', '0033' )

  AND to_number(Extract(year FROM apae.start_date)) = to_number(:p_date)

  

  UNION

SELECT papf.person_id,

       papf.person_number,

       ppnf.display_name

       employee_name,

       pasf_Sup.manager_name

       manager_name,

       pjft.name

       job_name,

       pgft.name

       grade_name,

       --abt.absence_type_id,

       abt.name

       absence_type,

   paam.ass_attribute2 sector,

       paam.ass_attribute1 unit,

       (SELECT DISTINCT( pd.name )

        FROM   per_departments pd

        WHERE  pd.organization_id = paam.organization_id

               AND pd.effective_start_date =

                   (SELECT Max(pd1.effective_start_date)

                    FROM   per_departments pd1

                    WHERE

                   pd1.organization_id = pd.organization_id))

       department_name,

      -- To_char(apae.start_date, 'DD-MON-YYYY', 'NLS_DATE_LANGUAGE = AMERICAN')

       apae.start_date start_date,

      -- To_char(apae.end_date, 'DD-MON-YYYY', 'NLS_DATE_LANGUAGE = AMERICAN')

      apae.end_date end_date,

       apae.duration,

   apae.start_date order_by_date,

   apae.attribute1 Delegated_Person

FROM   anc_per_abs_entries apae,

       per_periods_of_service pps,

       anc_absence_types_vl abt,

       per_all_people_f papf,

       per_person_names_f ppnf,

       (SELECT ppnf2.full_name manager_name,

               pasf.person_id

        FROM   per_assignment_supervisors_f pasf,

               per_person_names_f ppnf2

        WHERE  pasf.manager_id = ppnf2.person_id

               AND pasf.manager_type = 'LINE_MANAGER'

               AND Trunc(SYSDATE) BETWEEN pasf.effective_start_date AND

                                          pasf.effective_end_date

               AND Trunc(SYSDATE) BETWEEN ppnf2.effective_start_date AND

                                          ppnf2.effective_end_date

               AND ppnf2.name_type = 'GLOBAL') pasf_Sup,

       per_all_assignments_m paam,

       per_jobs_f_vl pjft,

       per_grades_f_vl pgft,

   hr_organization_units bu,

       hr_organization_units le,

   per_asg_responsibilities resp

   --hr_organization_units dept

WHERE  apae.period_of_service_id = pps.period_of_service_id

       AND apae.absence_type_id = abt.absence_type_id

   AND apae.ABSENCE_STATUS_CD <> 'ORA_WITHDRAWN'

   AND apae.APPROVAL_STATUS_CD = 'APPROVED'

       AND abt.name = 'Tentative Leave Plan'

       AND pps.person_id = papf.person_id

       AND papf.person_id = ppnf.person_id

       AND papf.person_id = pasf_Sup.person_id(+)

       AND papf.person_id = paam.person_id(+)

       AND paam.primary_assignment_flag(+) = 'Y'

       --AND paam.assignment_type = 'E'

       --AND paam.effective_latest_change = 'Y'

       AND paam.job_id = pjft.job_id (+)

       AND paam.grade_id = pgft.grade_id (+)

   AND paam.business_unit_id = bu.organization_id

       AND paam.legal_entity_id = le.organization_id

   AND resp.person_id = HRC_SESSION_UTIL.get_user_personid

       AND resp.responsibility_type IN ('Sector Head', 'Department Head')

       AND resp.status = 'Active'

       --AND  resp.legal_entity_id = paam.legal_entity_id

   --AND resp.BUSINESS_UNIT_ID=paam.BUSINESS_UNIT_ID

   AND resp.organization_id =paam.organization_id 

       AND TRUNC(sysdate) BETWEEN resp.start_date AND NVL(resp.end_date,TRUNC(sysdate))

       AND Trunc(SYSDATE) BETWEEN pjft.effective_start_date(+) AND

                                  pjft.effective_end_date(+)

       AND Trunc(SYSDATE) BETWEEN pgft.effective_start_date(+) AND

                                  pgft.effective_end_date(+)

       AND Trunc(SYSDATE) BETWEEN paam.effective_start_date AND

                                  paam.effective_end_date

       AND Trunc(SYSDATE) BETWEEN abt.effective_start_date AND

                                  abt.effective_end_date

       AND Trunc(SYSDATE) BETWEEN papf.effective_start_date AND

                                  papf.effective_end_date

       AND Trunc(SYSDATE) BETWEEN ppnf.effective_start_date AND

                                  ppnf.effective_end_date

       AND ppnf.name_type = 'GLOBAL'

      --AND papf.person_number IN ( '0015', '0033' )

  AND to_number(Extract(year FROM apae.start_date)) = to_number(:p_date)  

)

   WHERE 1=1

    AND (LEAST(:p_sector) IS NULL

OR  trim(sector) IN trim( :p_sector ))

--AND ( (coalesce(null, :p_sector) is null) or (trim(sector) in trim(:p_sector)))

AND (LEAST(:p_dept) IS NULL

OR  department_name IN ( :p_dept ))

--ORDER  BY department_name,order_by_date

)

SELECT mq.person_id,

       mq.person_number,

       mq.employee_name,

       mq.manager_name,

       mq.job_name,

       mq.grade_name,

       --abt.absence_type_id,

       mq.absence_type,

   mq.sector,

   mq.unit,

      mq.department_name,

       To_char(mq.start_date, 'DD-MON-YYYY', 'NLS_DATE_LANGUAGE = AMERICAN')

   start_date,

       To_char(mq.end_date, 'DD-MON-YYYY', 'NLS_DATE_LANGUAGE = AMERICAN')

   end_date,

       mq.duration,

   mq.order_by_date,

   mq.Delegated_Person,

   (SELECT LISTAGG(mq1.person_number, ', ') WITHIN GROUP (ORDER BY mq1.person_number)

   FROM Main_Q mq1

   WHERE mq.person_id!=mq1.person_id

   AND (TRUNC(mq.start_date) BETWEEN TRUNC(mq1.start_date) AND TRUNC(mq1.end_date)

   OR TRUNC(mq1.start_date) BETWEEN TRUNC(mq.start_date) AND TRUNC(mq.end_date)

   )

   ) overlap_emp

from Main_Q mq

Generate Empty File in BI Publisher Bursting sql returns no data

 Currently there is no supported method for that. The workaround is to make sure that your sql will always return at least one record.

You could accomplish this by adding a union to your sql that will always return one row if the main sql returns no data.

We have provided a sample select that you may use as guidance:

We have modified the Data Model main sql in such a way that it will always return a record when the main sql returns no data. This will prevent the report job from failing and, at the same time, the bursting will not send out the email out as the party_site_id is equals to -999, which does not meet the bursting sql criteria.

SELECT s.vendor_id,

  s.vendor_name,

  ps.party_site_name ,

  ps.party_site_id

FROM fusion.poz_suppliers_v s,

  fusion.hz_party_sites ps

WHERE s.party_id = ps.party_id

UNION

SELECT 1,

  '2' ,

  '3' ,

  -99999

FROM dual

WHERE NOT EXISTS

  (SELECT 1

  FROM fusion.poz_suppliers_v s,

    fusion.hz_party_sites ps

  WHERE s.party_id = ps.party_id

  );

Reference link : Doc ID 1925999.1

Query For Payroll Run Balances In Oracle Fusion

 SELECT /*+ MATERIALIZE */ main.person_id

      ,main.Employer_Contribution

  ,main.Member_contribution

  ,main.org_information6 as Nest_Employer_Reference_Number

  ,main.NATIONAL_IDENTIFIER_NUMBER as unique_record_identifier

      ,'Norfolk County Council' as Payment_source

      ,'Monthly'  Frequency

      ,'0.00'  as  Pensionable_Earnings

  , to_char(last_day(add_months(sysdate,-1)),'MM-DD-YYYY') as  Earnings_period_end_date

FROM(

      SELECT /*+ inline */

        Bal_val.person_id, 

       SUM (CASE WHEN Balances.balance_name IN (xxxxxxxxx) Employer_Contribution,

       SUM(CASE WHEN Balances.balance_name IN (xxxxxxxxxx) Member_contribution,

TRU.org_information6,

-- Bal_val.period_name,

-- Legal_Emp.name,

-- Bal_val.payroll_name,

-- flow.flow_name,

Bal_val.NATIONAL_IDENTIFIER_NUMBER

      FROM 

        (

              SELECT /*+ MATERIALIZE */

                prb.balance_value, 

                prb.defined_balance_id, 

                prb.tax_unit_id,

                ppa.effective_date,

                ppa.earn_time_period_id,

                Nvl(prg.assignment_id, ppaf.assignment_id) assignment_id, 

                pldg.legislation_code,

ppa.PAY_REQUEST_ID,

ppaf.legal_entity_id,

ppaf.person_id,

ptp.period_name,

papf.payroll_name,

pni.NATIONAL_IDENTIFIER_NUMBER

              FROM 

                pay_run_balances prb, 

                pay_pay_relationships_dn pprd, 

                pay_payroll_rel_actions ppra, 

                pay_payroll_actions ppa, 

                per_all_assignments_f ppaf, 

                pay_rel_groups_dn PRG, 

                per_legislative_data_groups_vl PLDG,

    pay_all_payrolls_f papf,

    pay_time_periods ptp,

per_national_identifiers pni

              WHERE 

                prb.payroll_rel_action_id = ppra.payroll_rel_action_id 

                AND ppra.payroll_action_id = ppa.payroll_action_id 

                AND prb.payroll_relationship_id = pprd.payroll_relationship_id (+) 

                AND pprd.person_id = ppaf.person_id 

                AND prb.effective_date = ppa.effective_date 

                AND ppa.action_type IN ('R', 'B', 'V', 'Q', 'I') 

                AND ppa.effective_date BETWEEN ppaf.effective_start_date 

                AND ppaf.effective_end_date 

                AND ppaf.primary_flag = 'Y' 

                AND pprd.legislative_data_group_id = pldg.legislative_data_group_id 

                AND ppaf.work_terms_assignment_id IS NOT NULL 

AND ppaf.effective_latest_change = 'Y'

                AND ppaf.assignment_type in ('E','C','N','P')

                AND prg.relationship_group_id (+)= prb.payroll_assignment_id 

                AND ppra.retro_component_id IS NULL

AND ppa.payroll_id = papf.payroll_id(+) 

--AND ppaf.person_id=300000004491016 ---200193

--AND ppaf.person_id=100000001628019  -----1915

AND prb.effective_date BETWEEN papf.effective_start_date(+) AND papf.effective_end_date(+)

               AND ppa.earn_time_period_id = ptp.time_period_id 

   AND prb.effective_date BETWEEN ppaf.effective_start_date AND ppaf.effective_end_date

   AND ppaf.person_id = pni.person_id

   AND ((COALESCE(NULL, :p_payroll_name) IS NULL) OR (papf.payroll_name IN (:p_payroll_name)))

               AND   ptp.period_name = nvl(:p_period_name,ptp.period_name)

        ) Bal_val, 

        (

          SELECT /*+ MATERIALIZE */

            pduv.dimension_usage_id, 

            pduv.dimension_name, 

            pduv.legislation_code, 

            pdb.defined_balance_id 

          FROM 

            pay_dimension_usages_vl pduv, 

            pay_defined_balances pdb 

          WHERE 

            pduv.balance_dimension_id = pdb.balance_dimension_id

and pduv.dimension_name = 'Payroll Relationship, Run'

        )Dimension, 

        

      

        (

          SELECT /*+ MATERIALIZE */

            houcf.org_unit_classification_id, 

            houcf.effective_start_date, 

            houcf.effective_end_date, 

            haouf.organization_id AS unit_eo_organization_id, 

            haouf.effective_start_date AS unit_eo_effective_start_date, 

            haouf.effective_end_date AS unit_eo_effective_end_date, 

            houft.organization_id AS unit_tleo_organization_id, 

            houft.effective_start_date AS unit_tleo_effective_start_date, 

            houft.effective_end_date AS unit_tleo_effective_end_date, 

            houft.language, 

            houft.NAME,

HOIF.ORG_INFORMATION6--nest ids

          FROM 

            hr_all_organization_units_f haouf, 

            hr_org_unit_classifications_f houcf, 

            hr_organization_units_f_tl houft,

hr_organization_information_f HOIF

          WHERE  haouf.organization_id = houcf.organization_id(+) 

              AND haouf.organization_id = houft.organization_id(+) 

              AND haouf.effective_start_date = houft.effective_start_date(+) 

              AND haouf.effective_end_date = houft.effective_end_date(+) 

              AND userenv('LANG')= houft.language(+) 

              AND sysdate BETWEEN haouf.effective_start_date AND haouf.effective_end_date

              AND sysdate BETWEEN houcf.effective_start_date(+)AND houcf.effective_end_date(+)

              AND sysdate BETWEEN houft.effective_start_date(+) AND houft.effective_end_date(+)

              AND houcf.classification_code = 'HCM_TRU'

  AND hoif.org_information_context LIKE 'LRU Additional Details'

              AND hoif.organization_id = houft.organization_id

               

        ) TRU, 

         (

          SELECT /*+ MATERIALIZE */

            houcf.org_unit_classification_id, 

            houcf.effective_start_date, 

            houcf.effective_end_date, 

            haouf.organization_id AS unit_eo_organization_id, 

            haouf.effective_start_date AS unit_eo_effective_start_date, 

            haouf.effective_end_date AS unit_eo_effective_end_date, 

            houft.organization_id AS unit_tleo_organization_id, 

            houft.effective_start_date AS unit_tleo_effective_start_date, 

            houft.effective_end_date AS unit_tleo_effective_end_date, 

            houft.language, 

            houft.NAME 

          FROM 

            hr_all_organization_units_f haouf, 

            hr_org_unit_classifications_f houcf, 

            hr_organization_units_f_tl houft 

          WHERE 

            

              haouf.organization_id = houcf.organization_id(+) 

              AND haouf.organization_id = houft.organization_id(+) 

              AND haouf.effective_start_date = houft.effective_start_date(+) 

              AND haouf.effective_end_date = houft.effective_end_date(+) 

              AND userenv('LANG')  = houft.language(+) 

              AND sysdate BETWEEN haouf.effective_start_date AND haouf.effective_end_date

              AND sysdate BETWEEN houcf.effective_start_date(+) 

              AND houcf.effective_end_date(+)

              AND sysdate BETWEEN houft.effective_start_date(+) AND houft.effective_end_date(+)

              AND houcf.classification_code = 'HCM_LEMP'

  AND ((COALESCE(NULL, :P_Legal_Entity) IS NULL) OR (houft.NAME IN (:P_Legal_Entity)))

            

        ) Legal_Emp, 

        (

          SELECT /*+ MATERIALIZE */

            pdb.balance_type_id, 

            pdb.defined_balance_id,

pbtv.balance_name

          FROM 

            pay_defined_balances pdb,

pay_balance_types_vl pbtv

  WHERE pbtv.balance_name in (xxxxxxx)

      AND pdb.balance_type_id = pbtv.balance_type_id 

        ) Balances, 

        (

          SELECT /*+ MATERIALIZE */

            pr.flow_task_instance_id, 

            pr.pay_request_id,

pfti.flow_instance_id, 

            pfti.flow_task_instance_id,

    pft.flow_name

          FROM 

            pay_requests pr,

pay_flow_task_instances pfti,

pay_flow_instances pfi, 

            pay_flows pf, 

            pay_flows_tl pft 

   WHERE  pf.base_flow_id = pfi.base_flow_id (+) 

              AND pf.legislative_data_group_id = pfi.legislative_data_group_id 

              AND pft.flow_id = pf.flow_id 

              AND pft.language = userenv('LANG')

      AND pfti.flow_instance_id = pfi.flow_instance_id(+)

      AND pr.flow_task_instance_id = pfti.flow_task_instance_id(+) 

  AND ((COALESCE(NULL, :p_flow) IS NULL) OR (pfi.INSTANCE_NAME IN (:p_flow)))

        ) Flow

      

      WHERE   Bal_val.defined_balance_id = Dimension.defined_balance_id 

          AND Bal_val.legislation_code = Dimension.legislation_code 

          AND Bal_val.tax_unit_id = TRU.unit_eo_organization_id(+) 

          AND Bal_val.legal_entity_id = Legal_Emp.unit_eo_organization_id(+) 

          AND Bal_val.defined_balance_id = Balances.defined_balance_id 

          AND Bal_val.pay_request_id = Flow.pay_request_id(+) 

          AND Bal_val.effective_date BETWEEN TRU.effective_start_date(+) AND TRU.effective_end_date(+)

       

     GROUP BY  Bal_val.person_id,

           TRU.ORG_INFORMATION6,

   Bal_val.NATIONAL_IDENTIFIER_NUMBER

   -- Bal_val.period_name,

-- Legal_Emp.name,

-- Bal_val.payroll_name,

-- flow.flow_name

    

) main

Query for Oracle Fusion Payroll Balances

 WITH Employer_con AS

  (

        SELECT /*+ MATERIALIZE */

            SUM(bal.balance_value) as  amount,

            pprd.person_id,

ppa.date_earned,

ptp.end_date,

ppa.payroll_action_id

        FROM

            per_legislative_data_groups_vl ldg,

            pay_pay_relationships_dn pprd,

            pay_payroll_rel_actions pra,

            pay_payroll_actions ppa,

            pay_balance_types_vl pbt,

            TABLE ( pay_balance_view_pkg.get_balance_dimensions(p_balance_type_id => pbt.balance_type_id,p_payroll_rel_action_id => pra.payroll_rel_action_id

,p_payroll_term_id => NULL,p_payroll_assignment_id => NULL) ) bal,

            pay_dimension_usages_vl pdu,

pay_time_periods ptp,

pay_requests req,

            pay_flow_instances flow,

pay_all_payrolls_f paypf

        WHERE

            pprd.legislative_data_group_id = ldg.legislative_data_group_id

            AND   pra.payroll_relationship_id = pprd.payroll_relationship_id

            AND   EXISTS (

                SELECT

                    1

                FROM

                    pay_run_results prr

                WHERE

                    prr.payroll_rel_action_id = pra.payroll_rel_action_id

            )

            AND   ppa.payroll_action_id = pra.payroll_action_id 

            AND   nvl(pbt.legislation_code,ldg.legislation_code) = ldg.legislation_code

            AND   nvl(pbt.legislative_data_group_id,ldg.legislative_data_group_id) = ldg.legislative_data_group_id

            AND   pdu.balance_dimension_id = bal.balance_dimension_id

        AND   pbt.balance_name IN ('xxxxxxxxxx')

            AND   pdu.dimension_name IN ('Payroll Relationship, Run')

AND   nvl(pdu.legislation_code,ldg.legislation_code) = ldg.legislation_code

AND   nvl(pdu.legislative_data_group_id,ldg.legislative_data_group_id) = ldg.legislative_data_group_id

AND   trunc(ppa.date_earned) BETWEEN trunc(nvl(pprd.start_date(+),ppa.date_earned) ) AND trunc(nvl(pprd.end_date(+),ppa.date_earned) )

AND   ptp.period_category = 'E'

AND   ppa.earn_time_period_id=ptp.time_period_id

AND req.pay_request_id = ppa.pay_request_id

AND flow.flow_instance_id = req.flow_instance_id

and paypf.payroll_id = ptp.payroll_id

AND bal.balance_value <> 0

AND trunc(sysdate) BETWEEN paypf.effective_start_date AND paypf.effective_end_date

--AND   ppa.payroll_id = :p_payroll_name

AND ((COALESCE(NULL, :p_payroll_name) IS NULL) OR (paypf.payroll_name IN (:p_payroll_name)))

 AND   ptp.period_name = nvl(:p_period_name,ptp.period_name)

--AND   ppa.payroll_action_id in (:p_flow)

AND ((COALESCE(NULL, :p_flow) IS NULL) OR (flow.INSTANCE_NAME IN (:p_flow)))

 

        GROUP BY

            pprd.person_id,ppa.date_earned, ptp.end_date,ppa.payroll_action_id

    ),

MEMBER_CON AS 

 (

        SELECT /*+ MATERIALIZE */

                 SUM(bal.balance_value) as  amount,

            pprd.person_id,

ppa.date_earned,

ptp.end_date,

ppa.payroll_action_id

        FROM

            per_legislative_data_groups_vl ldg,

            pay_pay_relationships_dn pprd,

            pay_payroll_rel_actions pra,

            pay_payroll_actions ppa,

            pay_balance_types_vl pbt,

            TABLE ( pay_balance_view_pkg.get_balance_dimensions(p_balance_type_id => pbt.balance_type_id,p_payroll_rel_action_id => pra.payroll_rel_action_id

,p_payroll_term_id => NULL,p_payroll_assignment_id => NULL) ) bal,

            pay_dimension_usages_vl pdu,

pay_time_periods ptp,

pay_requests req,

            pay_flow_instances flow,

pay_all_payrolls_f paypf

        WHERE

            pprd.legislative_data_group_id = ldg.legislative_data_group_id

            AND   pra.payroll_relationship_id = pprd.payroll_relationship_id

            AND   EXISTS (

                SELECT

                    1

                FROM

                    pay_run_results prr

                WHERE

                    prr.payroll_rel_action_id = pra.payroll_rel_action_id

            )

            AND   ppa.payroll_action_id = pra.payroll_action_id 

            AND   nvl(pbt.legislation_code,ldg.legislation_code) = ldg.legislation_code

            AND   nvl(pbt.legislative_data_group_id,ldg.legislative_data_group_id) = ldg.legislative_data_group_id

            AND   pdu.balance_dimension_id = bal.balance_dimension_id

             AND  pbt.balance_name IN ('xxxxxxxxxx')

            AND   pdu.dimension_name IN ('xxxxxxxxxxxxxx')

AND   nvl(pdu.legislation_code,ldg.legislation_code) = ldg.legislation_code

AND   nvl(pdu.legislative_data_group_id,ldg.legislative_data_group_id) = ldg.legislative_data_group_id

AND   trunc(ppa.date_earned) BETWEEN trunc(nvl(pprd.start_date(+),ppa.date_earned) ) AND trunc(nvl(pprd.end_date(+),ppa.date_earned) )

AND   ptp.period_category = 'E'

AND   ppa.earn_time_period_id=ptp.time_period_id

AND req.pay_request_id = ppa.pay_request_id

AND flow.flow_instance_id = req.flow_instance_id

and paypf.payroll_id = ptp.payroll_id

AND bal.balance_value <> 0

AND trunc(sysdate) BETWEEN paypf.effective_start_date AND paypf.effective_end_date

--AND   ppa.payroll_id = :p_payroll_name

 AND ((COALESCE(NULL, :p_payroll_name) IS NULL) OR (paypf.payroll_name IN (:p_payroll_name)))

 AND   ptp.period_name = nvl(:p_period_name,ptp.period_name)

--AND   ppa.payroll_action_id in (:p_flow)

 AND ((COALESCE(NULL, :p_flow) IS NULL) OR (flow.INSTANCE_NAME IN (:p_flow)))

 -- and paypf.payroll_name = 'DPSS PFS Payroll Monthly' 

-- and ptp.period_name = '12 2023 Calendar Month'

-- and flow.INSTANCE_NAME = 'Calculate Payroll -  8th March 2024 - PFS'

--and pprd.person_id = 100000001631372

 

        GROUP BY

            pprd.person_id,ppa.date_earned, ptp.end_date,ppa.payroll_action_id

    ),

TRU_DETAILS as

(

SELECT /*+ MATERIALIZE */ DISTINCT 

houft.NAME

,HOIF.ORG_INFORMATION_CONTEXT

,HOIF.ORG_INFORMATION1

,HOIF.ORG_INFORMATION2

,HOIF.ORG_INFORMATION3

,HOIF.ORG_INFORMATION_NUMBER1

,HOIF.ORG_INFORMATION4

,HOIF.ORG_INFORMATION6--nest ids

,HOIF.ORG_INFORMATION5

,pprd.person_id

FROM            pay_run_balances prb,

                pay_pay_relationships_dn pprd,

                pay_payroll_rel_actions ppra,

                pay_payroll_actions ppa,

                pay_rel_groups_dn PRG,

                per_legislative_data_groups_vl PLDG,

                 hr_all_organization_units_f haouf,

                 hr_org_unit_classifications_f houcf,

                hr_organization_units_f_tl houft,

                hr_organization_information_f HOIF

WHERE prb.payroll_rel_action_id=ppra.payroll_rel_action_id

AND ppra.payroll_action_id=ppa.payroll_action_id

AND prb.payroll_relationship_id= pprd.payroll_relationship_id (+)

AND prb.effective_date = ppa.effective_date

AND ppa.action_type IN ('R', 'B',  'V', 'Q', 'I')

AND pprd.legislative_data_group_id=pldg.legislative_data_group_id

AND prg.relationship_group_id (+)=prb.payroll_assignment_id

AND ppra.retro_component_id IS NULL

AND userenv('LANG') = houft.language(+)

AND haouf.organization_id = houcf.organization_id(+)

AND haouf.organization_id = houft.organization_id(+)

AND haouf.effective_start_date = houft.effective_start_date(+)

AND haouf.effective_end_date = houft.effective_end_date(+)

AND sysdate BETWEEN haouf.effective_start_date(+) AND haouf.effective_end_date(+)

 AND sysdate BETWEEN houcf.effective_start_date(+)

AND houcf.effective_end_date(+)

AND sysdate BETWEEN houft.effective_start_date(+)

AND houft.effective_end_date(+)

AND houcf.classification_code(+) = 'HCM_TRU'

AND prb.tax_unit_id = haouf.organization_id (+)

AND ppa.effective_date >= houcf.effective_start_date(+)

AND ppa.effective_date <= houcf.effective_end_date(+)

AND hoif.org_information_context LIKE 'LRU Additional Details'

AND hoif.organization_id(+) = houft.organization_id

and HOIF.ORG_INFORMATION6 is not null

)

SELECT /*+ MATERIALIZE */

    papf.person_number

   ,TD.ORG_INFORMATION6  as Nest_Employer_Reference_Number

   ,'Monthly'  Frequency

   , to_char(last_day(add_months(sysdate,-1)),'MM-DD-YYYY') as  Earnings_period_end_date

   ,pni.NATIONAL_IDENTIFIER_NUMBER as unique_record_identifier

   ,0.00  as  Pensionable_Earnings

   ,ec.amount Employer_Contribution

   ,mc.amount Member_contribution

FROM

    per_all_people_f papf,

    per_all_assignments_m paaf,

    employer_con ec,

member_con   mc,

tru_details td,

    per_national_identifiers pni,

per_legal_employers  ple

WHERE paaf.person_id = papf.person_id      

  AND papf.person_id = ec.person_id

  AND papf.person_id = mc.person_id

  AND paaf.primary_flag = 'Y'

  AND paaf.effective_latest_change = 'Y'

  AND PAAf.assignment_type IN('E','C')  

  AND td.person_id = paaf.person_id

  AND papf.person_id = pni.person_id

  AND ple.organization_id = paaf.legal_entity_id

  AND sysdate between ple.EFFECTIVE_START_DATE and ple.EFFECTIVE_end_DATE

 AND ((COALESCE(NULL, :P_Legal_Entity) IS NULL) OR (ple.name IN (:P_Legal_Entity)))

  AND trunc(nvl(ec.date_earned,SYSDATE)) BETWEEN trunc(nvl(papf.effective_start_date,SYSDATE) ) AND trunc(nvl(papf.effective_end_date,SYSDATE) )

  AND trunc(nvl(ec.date_earned,SYSDATE)) BETWEEN trunc(nvl(paaf.effective_start_date,SYSDATE) ) AND trunc(nvl(paaf.effective_end_date,SYSDATE) )

  AND trunc(nvl(mc.date_earned,SYSDATE)) BETWEEN trunc(nvl(papf.effective_start_date,SYSDATE) ) AND trunc(nvl(papf.effective_end_date,SYSDATE) )

  AND trunc(nvl(mc.date_earned,SYSDATE)) BETWEEN trunc(nvl(paaf.effective_start_date,SYSDATE) ) AND trunc(nvl(paaf.effective_end_date,SYSDATE) )

Rest Api Testing for Oracle Time and Labor (OTL)

 Oracle Time and Labor Rest Api:

 

Rest Api: hcmRestApi/resources/11.13.18.05/timeRecordEventRequests

Method: Post

Payload:

{

"processMode":"TIME_SAVE",

"processInline":"N",

"timeRecordEvent":

[

{

"timeRecordId":"",

"timeRecordVersion":"",

"startTime":"2017-10-13T10:00:00.000-08:00",

"stopTime":"2017-10-13T14:00:00.000-08:00",

"reporterIdType":"PERSON",

"reporterId":"xxxxx", ----Insert Person Number

"assignmentNumber":"",

"comment":"Time Entry Update Measure",

"operationType":"ADD",

"changeReason":"ORA_CA_REASON_MTE_PROC",

"measure":"",

"timeRecordEventAttribute":

[

{

"attributeName":"PayrollTimeType",

"changeReason":"ORA_CA_REASON_MTE_PROC",

"attributeValue":"ZOTL_Regular"

}

]

}

]

}

Postman URL:

https://<<Instance URL>>/hcmRestApi/resources/11.13.18.05/timeRecordEventRequests

 

 

Response from Postman:

{

    "timeRecordEventRequestId": 300000035161120,

    "processMode": "TIME_SAVE",

    "processInline": "N",

    "timeRecordEvent": [

        {

            "comment": "Time Entry Update Measure",

            "crudStatusValue": 0,

            "personId": "XXXXXX", --Person_id

            "referenceDate": null,

            "reporterId": "XXXXX", ---Person_number

            "reporterIdType": "PERSON",

            "startTime": "2017-10-13T10:00:00.000-08:00",

            "stopTime": "2017-10-13T14:00:00.000-08:00",

            "subresourceId": null,

            "timeRecordEventId": 300000035161121,

            "timeRecordEventRequestId": 300000035161120,

            "timeRecordId": null,

            "timeRecordVersion": null,

            "operationType": "ADD",

            "assignmentNumber": "",

            "eventStatusValue": 0,

            "eventStatus": "NEW",

            "measure": null,

            "changeReason": "ORA_CA_REASON_MTE_PROC",

            "timeRecordEventAttribute": [

                {

                    "timeAttributeFieldId": 300000000427517,

                    "timeRecordEventAttributeId": 300000035161122,

                    "timeRecordEventId": 300000035161121,

                    "attributeValue": "ZOTL_Regular",

                    "changeReason": "ORA_CA_REASON_MTE_PROC",

                    "attributeName": "PayrollTimeType",

                    "links": [

                        {

                            "rel": "self",

                            "href": "https:<<Instance URL>>:443/hcmRestApi/resources/11.13.18.05/timeRecordEventRequests/300000035161120/child/timeRecordEvent/300000035161121/child/timeRecordEventAttribute/300000035161122",

                            "name": "timeRecordEventAttribute",

                            "kind": "item",

                            "properties": {

                                "changeIndicator": "ACED0005737200136A6176612E7574696C2E41727261794C6973747881D21D99C7619D03000149000473697A65787000000001770400000001737200116A6176612E6C616E672E496E746567657212E2A0A4F781873802000149000576616C7565787200106A6176612E6C616E672E4E756D62657286AC951D0B94E08B02000078700000000178"

                            }

                        },

                        {

                            "rel": "canonical",

                            "href": "<<Instance URL >>":443/hcmRestApi/resources/11.13.18.05/timeRecordEventRequests/300000035161120/child/timeRecordEvent/300000035161121/child/timeRecordEventAttribute/300000035161122",

                            "name": "timeRecordEventAttribute",

                            "kind": "item"

                        },

                        {

                            "rel": "parent",

                            "href": "<<Instance URL>>":443/hcmRestApi/resources/11.13.18.05/timeRecordEventRequests/300000035161120/child/timeRecordEvent/300000035161121",

                            "name": "timeRecordEvent",

                            "kind": "item"

                        },

                        {

                            "rel": "lov",

                            "href": "<<Instance URL>>:443/hcmRestApi/resources/11.13.18.05/timeRecordEventRequests/300000035161120/child/timeRecordEvent/300000035161121/child/timeRecordEventAttribute/300000035161122/lov/HcmLookupPVO1",

                            "name": "HcmLookupPVO1",

                            "kind": "collection"

                        }

                    ]

                }

            ],

            "links": [

                {

                    "rel": "self",

                    "href": "<<Instance URL>>:443/hcmRestApi/resources/11.13.18.05/timeRecordEventRequests/300000035161120/child/timeRecordEvent/300000035161121",

                    "name": "timeRecordEvent",

                    "kind": "item",

                    "properties": {

                        "changeIndicator": "ACED0005737200136A6176612E7574696C2E41727261794C6973747881D21D99C7619D03000149000473697A65787000000001770400000001737200116A6176612E6C616E672E496E746567657212E2A0A4F781873802000149000576616C7565787200106A6176612E6C616E672E4E756D62657286AC951D0B94E08B02000078700000000178"

                    }

                },

                {

                    "rel": "canonical",

                    "href": "<<Instance URL>>:443/hcmRestApi/resources/11.13.18.05/timeRecordEventRequests/300000035161120/child/timeRecordEvent/300000035161121",

                    "name": "timeRecordEvent",

                    "kind": "item"

                },

                {

                    "rel": "parent",

                    "href": "<<Instance URL>>:443/hcmRestApi/resources/11.13.18.05/timeRecordEventRequests/300000035161120",

                    "name": "timeRecordEventRequests",

                    "kind": "item"

                },

                {

                    "rel": "lov",

                    "href": "<<Instance URL>>:443/hcmRestApi/resources/11.13.18.05/timeRecordEventRequests/300000035161120/child/timeRecordEvent/300000035161121/lov/HcmLookupPVO1",

                    "name": "HcmLookupPVO1",

                    "kind": "collection"

                },

                {

                    "rel": "child",

                    "href": "<<Instance URL>>:443/hcmRestApi/resources/11.13.18.05/timeRecordEventRequests/300000035161120/child/timeRecordEvent/300000035161121/child/timeRecordEventAttribute",

                    "name": "timeRecordEventAttribute",

                    "kind": "collection"

                },

                {

                    "rel": "child",

                    "href": "<<Instance URL>>:443/hcmRestApi/resources/11.13.18.05/timeRecordEventRequests/300000035161120/child/timeRecordEvent/300000035161121/child/timeRecordEventMessage",

                    "name": "timeRecordEventMessage",

                    "kind": "collection"

                }

            ]

        }

    ],

    "links": [

        {

            "rel": "self",

            "href": "<<Instance URL>>:443/hcmRestApi/resources/11.13.18.05/timeRecordEventRequests/300000035161120",

            "name": "timeRecordEventRequests",

            "kind": "item",

            "properties": {

                "changeIndicator": "ACED0005737200136A6176612E7574696C2E41727261794C6973747881D21D99C7619D03000149000473697A65787000000001770400000001737200116A6176612E6C616E672E496E746567657212E2A0A4F781873802000149000576616C7565787200106A6176612E6C616E672E4E756D62657286AC951D0B94E08B02000078700000000178"

            }

        },

        {

            "rel": "canonical",

            "href": "<<Instance URL>>:443/hcmRestApi/resources/11.13.18.05/timeRecordEventRequests/300000035161120",

            "name": "timeRecordEventRequests",

            "kind": "item"

        },

        {

            "rel": "child",

            "href": "<<Instance URL>>:443/hcmRestApi/resources/11.13.18.05/timeRecordEventRequests/300000035161120/child/timeRecordEvent",

            "name": "timeRecordEvent",

            "kind": "collection"

        }

    ]

}

Time Record Entry created In fusion:

Screenshot from Oracle fusion:

 

Multiple Records 1 :

{

"processMode":"TIME_SAVE",

"processInline":"N",

"timeRecordEvent":

[

{

"timeRecordId":"",

"timeRecordVersion":"",

"startTime":"2018-10-13T10:00:00.000-08:00",

"stopTime":"2018-10-13T14:00:00.000-08:00",

"reporterIdType":"PERSON",

"reporterId":"xxxxx",---person_number

"assignmentNumber":"",

"comment":"Time Entry Update Measure",

"operationType":"ADD",

"changeReason":"ORA_CA_REASON_MTE_PROC",

"measure":"",

"timeRecordEventAttribute":

[

{

"attributeName":"PayrollTimeType",

"changeReason":"ORA_CA_REASON_MTE_PROC",

"attributeValue":"ZOTL_Regular"

}

]

}

]

}

 

 

2^nd Record:

{

"processMode":"TIME_SAVE",

"processInline":"N",

"timeRecordEvent":

[

{

"timeRecordId":"",

"timeRecordVersion":"",

"startTime":"2018-10-13T16:00:00.000-08:00",

"stopTime":"2018-10-13T17:00:00.000-08:00",

"reporterIdType":"PERSON",

"reporterId":"xxxxxx",---person_number

"assignmentNumber":"",

"comment":"Time Entry Update Measure",

"operationType":"ADD",

"changeReason":"ORA_CA_REASON_MTE_PROC",

"measure":"",

"timeRecordEventAttribute":

[

{

"attributeName":"PayrollTimeType",

"changeReason":"ORA_CA_REASON_MTE_PROC",

"attributeValue":"ZOTL_Regular"

}

]

}

]

}

 

 

3^rd Record:

{

"processMode":"TIME_SAVE",

"processInline":"N",

"timeRecordEvent":

[

{

"timeRecordId":"",

"timeRecordVersion":"",

"startTime":"2018-10-13T17:30:00.000-08:00",

"stopTime":"2018-10-13T17:45:00.000-08:00",

"reporterIdType":"PERSON",

"reporterId":"person_number",

"assignmentNumber":"",

"comment":"Time Entry Update Measure",

"operationType":"ADD",

"changeReason":"ORA_CA_REASON_MTE_PROC",

"measure":"",

"timeRecordEventAttribute":

[

{

"attributeName":"PayrollTimeType",

"changeReason":"ORA_CA_REASON_MTE_PROC",

"attributeValue":"ZOTL_Regular"

}

]

}

]

}

 

 

 

 

 

4^th Record :

{

"processMode":"TIME_SAVE",

"processInline":"N",

"timeRecordEvent":

[

{

"timeRecordId":"",

"timeRecordVersion":"",

"startTime":"2018-10-13T17:46:00.000-08:00",

"stopTime":"2018-10-13T17:59:00.000-08:00",

"reporterIdType":"PERSON",

"reporterId":"xxxxx",  ---person_number

"assignmentNumber":"",

"comment":"Time Entry Update Measure",

"operationType":"ADD",

"changeReason":"ORA_CA_REASON_MTE_PROC",

"measure":"",

"timeRecordEventAttribute":

[

{

"attributeName":"PayrollTimeType",

"changeReason":"ORA_CA_REASON_MTE_PROC",

"attributeValue":"ZOTL_Regular"

}

]

}

]

}

 

Database Records:

Table : HWM_TM_REC_EVENTS

 

ESS : Process Imported Time Entries

 

Team Time Entries: