How to create ESS Job for the BI report in Oracle Fusion

Go to Setting and Action from Top Corner and choose “Setup & Maintenance”

ð Search task "Manage Enterprise Scheduler Job Definitions and Job Sets for Human Capital Management and Related Applications" and click on the hyperlink

ð  Create New with the following values

o   XX OTL Missing Timecard Alert to Employee

o   OTLEMPALERTMISSINGTIME

o   EarHcmEss

o   BIPJobType

o   Bursting => Yes

o   Path => /financials/

o   Add Parameter

§  Timecard Till Date

o   Report ID

§  /Custom/Human Capital Management/XX OTL Missing Timecard EMP Alert Report.xdo

ð  Save

ð  Go to Home Navigation => Tools=> Schedule Processes

o   Find the JOB and submit it as per the parameters

How to change date format in BI report RTF

Use the following to change the date format 

<?xdofx:to_char(to_date(substr(TO_CHECK_DATE,1,10),’YYYY-MM-DD’),’DD-Mon-YYYY’)?>

Query to get basic employee details in Oracle ERP fusion

--Query to get basic employee details in Oracle ERP fusion

--R12:
SELECT (SELECT name 

          FROM hr_all_organization_units 

         WHERE organization_id = paaf.business_group_id

       ) business_group
      ,papf1.employee_number
      ,papf1.first_name
      ,papf1.last_name
      ,papf1.full_name
      ,papf1.sex gender
      ,papf1.effective_start_date
      ,papf1.effective_end_date
      ,papf1.email_address
      ,(SELECT hloc.location_code 

          FROM hr_locations_all hloc 

         WHERE hloc.location_id = paaf.location_id 

       ) employee_location
      ,papf2.employee_number supervisor_employee_number
      ,papf2.full_name supervisor_name
      ,(SELECT pp.phone_number 

          FROM per_phones pp 

         WHERE papf1.person_id = pp.parent_id 

           AND pp.parent_table = 'PER_ALL_PEOPLE_F'
           AND pp.date_from = papf1.effective_start_date 

           AND NVL(pp.date_to, papf1.effective_end_date) 

              = papf1.effective_end_date

       ) work_telephone_number
      ,(SELECT name FROM per_jobs 

         WHERE job_id = paaf.job_id ) job_name
      ,(SELECT name FROM per_all_positions 

         WHERE position_id = paaf.position_id ) position_name
      ,(SELECT concatenated_segments FROM gl_code_combinations_kfv 

         WHERE code_combination_id = paaf.default_code_comb_id 

       ) default_charge_account

      ,(SELECT name FROM gl_ledgers 

         WHERE ledger_id = paaf.set_of_books_id ) ledger_name
      ,(SELECT pd.name FROM PER_DEPARTMENTS pd 

         WHERE organization_id = paaf.organization_id 

           AND trunc(sysdate) BETWEEN pd.effective_start_date 

                              AND nvl(pd.effective_end_date, sysdate+1) 

       )department
 FROM per_all_people_f papf1,
      per_all_people_f papf2,
      per_all_assignments_f paaf      
WHERE papf1.person_id(+) = paaf.person_id
  AND papf2.person_id(+) = paaf.supervisor_id
  AND paaf.primary_flag  = 'Y'
  AND papf1.current_employee_flag = 'Y'
  AND papf2.current_employee_flag = 'Y'
  
--Fusion
SELECT papf.person_number employee_number

     , pjft.name jobname

     , paam.assignment_number emp_assignment_number
     , gcc.segment1||'-'||gcc.segment2||'-'||

       gcc.segment3||'-'||gcc.segment4||'-'||

       gcc.segment5||'-'||gcc.segment6 exp_account

     , paam.effective_start_date assg_effective_start_date

     , pea.email_address,ppnf.full_name supervisor_name

     , papf2.person_number supervisor_number

     , hauft.name businessunit

     , papf.start_date hire_date

     , (SELECT pd.name FROM per_departments pd 

         WHERE organization_id = paaf.organization_id 

           AND TRUNC(SYSDATE) BETWEEN pd.effective_start_date 

                           AND NVL(pd.effective_end_date,SYSDATE+1) 

       ) department
  FROM per_all_people_f papf
     , per_all_people_f papf2
     , per_all_assignments_m paam
     , per_jobs_f pjf
     , per_jobs_f_tl pjft
     , gl_code_combinations gcc
     , per_email_addresses pea
     , per_assignment_supervisors_f pasf
     , per_person_names_f ppnf
     , hr_organization_units_f_tl hauft
 WHERE papf.person_id = paam.person_id
   AND TRUNC(SYSDATE) BETWEEN papf.effective_start_date 

                          AND papf.effective_end_date
   AND paam.primary_assignment_flag = 'Y'
   AND paam.assignment_type = 'E'
   and paam.effective_latest_change = 'Y'
   and paam.DEFAULT_CODE_COMB_ID=gcc.code_combination_id
   AND TRUNC(SYSDATE) BETWEEN paam.effective_start_date 

                          AND paam.effective_end_date
   AND paam.job_id = pjf.job_id
   AND TRUNC(SYSDATE) BETWEEN pjf.effective_start_date 

                          AND pjf.effective_end_date
   AND PEA.EMAIL_ADDRESS_ID=PAPF.PRIMARY_EMAIL_ID
   AND papf.person_id = pasf.person_id
   AND pasf.manager_type = 'LINE_MANAGER'
   AND ppnf.person_id = pasf.manager_id
   AND papf2.person_id = pasf.manager_id
   AND TRUNC(SYSDATE) BETWEEN papf2.effective_start_date 

                          AND papf2.effective_end_date
   AND TRUNC(SYSDATE) BETWEEN pasf.effective_start_date 

                          AND pasf.effective_end_date
   AND TRUNC(SYSDATE) BETWEEN ppnf.effective_start_date 

                          AND ppnf.effective_end_date
   AND pjf.job_id = pjft.job_id
   AND pjft.language = 'US'
   AND ppnf.name_type = 'GLOBAL'
   AND TRUNC(SYSDATE) BETWEEN pjft.effective_start_date 

                          AND pjft.effective_end_date
   AND hauft.organization_id = paam.business_unit_id
 ORDER BY papf.person_number

Query to get DFF for Project Table in Fusion

--Query to get DFF for Project Table in Fusion

SELECT ffv.descriptive_flexfield_name DFF_Name,

ffv.application_table_name App_Table_Name,

ffv.title ,

ap.application_name ,

ffc.descriptive_flex_context_code Context_Code,

ffc.descriptive_flex_context_name Context_Name,

ffc.description Context_Desc,

ffc.enabled_flag Context_Enable_Flag,

att.column_seq_num Segment_Number,

att.form_left_prompt Segment_Name,

att.application_column_name app_column_name,

fvs.flex_value_set_name Value_Set_Name,

att.display_flag ,

att.enabled_flag ,

att.required_flag 

FROM fnd_descriptive_flexs_vl ffv,

     fnd_descr_flex_contexts_vl ffc,

     fnd_descr_flex_col_usage_vl att,

     fnd_flex_value_sets fvs,

     fnd_application_vl ap

WHERE ffv.descriptive_flexfield_name = att.descriptive_flexfield_name

AND ap.application_id=ffv.application_id

AND ffv.descriptive_flexfield_name = ffc.descriptive_flexfield_name

AND ffv.application_id = ffc.application_id

AND ffc.descriptive_flex_context_code=att.descriptive_flex_context_code

AND fvs.flex_value_set_id=att.flex_value_set_id

AND ffv.application_table_name = 'PJF_PROJECTS_ALL_B'

ORDER BY att.column_seq_num

Query-Oracle-SQL- Get All Monday for the date range

Query-Oracle-SQL- Get All Monday for the date range 

SELECT 

  to_char(dt, 'mm/dd/yyyy') || '-' || 

  to_char(dt + 6, 'mm/dd/yyyy') time_card_datetime 

  FROM 

  (

    SELECT 

      TO_DATE(

        to_char(:P_FROM_DATE, 'mm/dd/yyyy'), 'mm/dd/yyyy'

               ) + (LEVEL -1) dt 

      FROM DUAL CONNECT BY 

           LEVEL <= TO_DATE (to_char(:P_TO_DATE, 'mm/dd/yyyy'), 

                                    'mm/dd/yyyy') 

                  - TO_DATE (to_char(:P_FROM_DATE, 'mm/dd/yyyy'), 

                                    'mm/dd/yyyy'

      )

  ) main_tab 

WHERE TRIM ( TO_CHAR (dt, 'DAY')) = '1'