Oracle Fusion - Oracle Project Resource Allocation

 SELECT PA.project_id

       ,ppa.segment1 project_code

       ,ppnf.person_id

       ,START_DATE_ACTIVE

       ,END_DATE_ACTIVE 

       ,(ppnf.first_name||' '||ppnf.last_name) EMP_Name
  FROM PJF_PROJECT_PARTIES  PA, per_person_names_f ppnf
     , PJF_PROJECTS_ALL_VL PPA
 WHERE pa.project_id = ppa.project_id
   AND ppa.segment1 = 'PROJECT_CODE'
   AND ppnf.person_id = pa.RESOURCE_SOURCE_ID
   AND ppnf.name_type = 'GLOBAL'
   AND SYSDATE BETWEEN TRUNC(ppnf.effective_start_date) 

                   AND TRUNC(ppnf.effective_end_date) 

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'

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’))

Oracle EBS - Multi Org Tables - MO_GLOBAL Set Policy / Org - AOL Tables

 
Setup Environment on Toad/SQL Developer
-------------------------------------------
BEGIN MO_GLOBAL.SET_POLICY_CONTEXT('S',101); END;
Note: SQL Developer is not providing data after that setting then check NLS_LANG parameter

HR_ALL_ORGANIZATION_UNITS
HR_OPERATING_UNITS
ORG_ORGANIZATION_DEFINITIONS => Inventory Org for OU
HRFV_BUSINESS_GROUPS => Business Group
HR_LEGAL_ENTITIES => LE
GL_LEDGERS => Ledger
GL_PERIODS_V
GL_PERIODS
GL_PERIOD_TYPES
FND_CURRENCIES
GL_CODE_COMBINATIONS
GLFV_CHARTS_OF_ACCOUNTS => Chart of accounts
AOL Tables
=====================
AD_APPLIED_PATCHES
AD_BUGS
FND_ENV_CONTEXT
FND_PRODUCT_GROUPS
----
FND_APPLICATION
FND_APPLICATION_VL
----
FND_CONCURRENT_PROGRAMS_TL
FND_CONCURRENT_REQUESTS
----
FND_DESCRIPTIVE_FLEXS_VL
FND_DESCR_FLEX_COL_USAGE_VL
FND_DESCR_FLEX_CONTEXTS_VL
----
FND_FLEX_VALUE_SETS
FND_FLEX_VALUE_SETS
FND_FLEX_VALUES
----
FND_ID_FLEX_SEGMENTS
FND_ID_FLEX_STRUCTURES
FND_ID_FLEX_STRUCTURES_TL
----
FND_LOOKUP_TYPES
FND_LOOKUP_VALUES
----
FND_PROFILE_OPTION_VALUES
FND_PROFILE_OPTIONS
----
FND_REQUEST_GROUP_UNITS
FND_REQUEST_GROUPS
----
FND_REQUEST_SETS_TL
----
FND_RESPONSIBILITY
FND_RESPONSIBILITY_TL
FND_SEGMENT_ATTRIBUTE_VALUES

FND_USER

Query to get values of the Independent/Dependent Value Set

The query will provide the value set values entered from the front-end. For query-based value sets, we need to use direct queries.

SELECT ffvs.flex_value_set_id

     , ffvs.flex_value_set_name

     , ffvs.description 
     , ffvs.validation_type

     , ffv.flex_value

     , ffvt.description

     , ffv.enabled_flag
     , ffv.last_update_date

     , ffv.last_updated_by
FROM fnd_flex_value_sets ffvs

   , fnd_flex_values ffv

   , fnd_flex_values_tl ffvt
WHERE ffvs.flex_value_set_id = ffv.flex_value_set_id
  and ffv.flex_value_id = ffvt.flex_value_id
  and ffvt.language = 'US'
  and flex_value_set_name in ('PROJECT_TYPE')
ORDER BY flex_value_set_name, flex_value asc
 

Query - Get DFF Structure Information

Following is the query, which can provide the DFF structure on a specific table in Oracle EBS/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 like 'PJF_PROJECTS_ALL%'
ORDER BY att.column_seq_num