Hi, Can any one help me on this?. i want a SQL query to join cost center with HCM department

menna allah muhammed

I'm trying to get the join between cost center and HCM department, But it kept return nulls when i try to join them together as it shown in the below query

(SELECT pcak.segment2 cost_centre,ffvt.DESCRIPTION
FROM pay_cost_allocation_keyflex pcak
,fnd_flex_values ffv
,fnd_flex_values_tl ffvt
WHERE pcak.cost_allocation_keyflex_id =
(SELECT pcaf.cost_allocation_keyflex_id
FROM pay_cost_allocations_f pcaf
WHERE pcaf.assignment_id =
(SELECT paaf.assignment_id
FROM per_all_assignments_f paaf
WHERE paaf.person_id =
(SELECT papf.person_id
FROM per_all_people_f papf
WHERE papf.employee_number = '1001'
AND papf.effective_end_date > SYSDATE)
AND paaf.effective_end_date > SYSDATE)
AND pcaf.effective_end_date > SYSDATE)
AND pcak.end_date_active IS NULL
and ffv.FLEX_VALUE = pcak.segment2
and ffvt.FLEX_VALUE_ID = ffv.FLEX_VALUE_ID

——————————————————————————————————————————————-

SELECT
PAPF.PERSON_NUMBER,
PCAA.*
FROM
PAY_COST_ALLOC_ACCOUNTS PCAA,
HR_ALL_ORGANIZATION_UNITS_F HROU,
PER_ALL_PEOPLE_F PAPF,
PER_ALL_ASSIGNMENTS_M PAAM,
PAY_COST_ALLOCATIONS_F PCA
WHERE
PAPF.PERSON_ID = PAAM.PERSON_ID
AND PAAM.ASSIGNMENT_TYPE = 'E'
AND PAAM.ORGANIZATION_ID = HROU.ORGANIZATION_ID
AND HROU.ORGANIZATION_ID = PCA.ORGANIZATION_ID
AND PCA.COST_ALLOCATION_ID = PCAA.COST_ALLOC_ACCOUNT_ID)

Nathan CCC

Hi, Try this SQL

select all 0 s_0

, "Department"."Department Name" as department

, "Department_GL Cost Center Information"."Department_ORG_INFORMATION_EFF_PER_GL_COST_CENTER_INFO__COST_CENTER_VALUESET" as costcenter_valueset_id

, "Department_GL Cost Center Information"."Department_ORG_INFORMATION_EFF_PER_GL_COST_CENTER_INFO__COST_CENTER_VALUESET_v" as costcenter_valueset

, "Department_GL Cost Center Information"."Department_ORG_INFORMATION_EFF_PER_GL_COST_CENTER_INFO_recordIdentifier" as costcenter_id

, "Department_GL Cost Center Information"."Department_ORG_INFORMATION_EFF_PER_GL_COST_CENTER_INFO_EffectiveStartDate" as costcenter_start_date

, "Department_GL Cost Center Information"."Department_ORG_INFORMATION_EFF_PER_GL_COST_CENTER_INFO_EffectiveEndDate" as costcenter_end_date

, "Department_GL Cost Center Information"."Department_ORG_INFORMATION_EFF_PER_GL_COST_CENTER_INFO__COST_CENTER" as costcenter_code

from "Workforce Management - Worker Assignment Real Time"

menna allah muhammed

Hi Nathan, Thanks for your help.

The department column contains data, while the other columns are null.

User_5XSJQ

Hello Menna

You will find this information in HR_ORGANIZATION_INFORMATION_F where ORG_INFORMATION_CONTEXT  = 'PER_GL_COST_CENTER_INFO'.

Please check if below post helps:

Please let me know if you still face any issues. http://fusionhcmconsulting.com/2021/10/bip-query-to-extract-gl-cost-details-from-department/

Nathan CCC

Hi Menna, The cost center data may be empty either because you have not configured cost centres on the departments or your user does not have data access to view this data. If the issue is data access then even if you get no data found you can view log in manage sessions to get the physical sql on the application tables and views (but comment out the where clause with any data security).

select all count(*) over (partition by null) as n,

to_char(department.organization_unit_classification_id) as organization_unit_classification_id

, department.organization_unit_classification_effective_start_date as organization_unit_classification_effective_start_date

, department.organization_unit_classification_effective_end_date as organization_unit_classification_effective_end_date

, to_char(department.organization_id) as department_id

, department.organization_name as department_code

, department.organization_unit_classification_effective_start_date as department_effective_start_date

, department.organization_unit_classification_effective_end_date as department_effective_end_date

, department.organization_status_code as department_status_code

, department.organization_status_name as department_status_name

, costcenter.record_identifier as record_identifier

, costcenter.cost_center_valueset as cost_center_valueset

, costcenter.cost_center_code as cost_center_code, costcenter.organization_unit_classification_effective_start_date as costcenter_effective_start_date

, costcenter.organization_unit_classification_effective_end_date as costcenter_effective_end_date

, costcenter.cost_center_manager_person_id as cost_center_manager_person_id

, costcenter.cost_center_manager_name as cost_center_manager_name

, costcenter.cost_center_manager_desc as cost_center_manager_desc

, costcenter.cost_center_manager_job_level as cost_center_manager_job_level

from

( --department

select all 

c.org_unit_classification_id as organization_unit_classification_id

, c.effective_start_date as organization_unit_classification_effective_start_date

, c.effective_end_date as organization_unit_classification_effective_end_date

, c.classification_code as organization_classification_code

, c.status as organization_status_code

, lkp_status.meaning as organization_status_name

, t.organization_id as organization_id

, t.effective_start_date as organization_effective_start_date

, t.effective_end_date as organization_effective_end_date, tl.language

, tl.effective_start_date as organization_translation_effective_start_date

, tl.effective_end_date as organization_translation_effective_end_date

, tl.name as organization_name


from

(

select all t.* from

-- https://docs.oracle.com/en/cloud/saas/human-resources/23b/oedmh/hrallorganizationunitsf-17414.html#hrallorganizationunitsf-17414

-- hr_all_organization_units_pk unique  fusion_ts_tx_idx    organization_id, effective_start_date, effective_end_date

hr_all_organization_units_f t

where (trunc(nvl(:p_effective_as_at_date,sysdate)) between t.effective_start_date and t.effective_end_date)

) t

inner join

(

select all t.* from

-- https://docs.oracle.com/en/cloud/saas/human-resources/23b/oedmh/hrorganizationunitsftl-9983.html#hrorganizationunitsftl-9983

-- hr_organization_units_f_tl_pk    unique  default organization_id, language, effective_start_date, effective_end_date

hr_organization_units_f_tl t

where (trunc(nvl(:p_effective_as_at_date,sysdate)) between t.effective_start_date and t.effective_end_date)

) tl

on (

tl.language = upper('us')

and t.organization_id = tl.organization_id

and t.effective_start_date = tl.effective_start_date

and t.effective_end_date = tl.effective_end_date

)

inner join

(

select all t.* from

-- https://docs.oracle.com/en/cloud/saas/human-resources/23b/oedmh/hrorgunitclassificationsf-22540.html#hrorgunitclassificationsf-22540

-- hr_org_unit_classification_pk    unique  default org_unit_classification_id, effective_start_date, effective_end_date

-- hr_org_unit_class_u2 unique  default organization_id, classification_code, effective_start_date, effective_end_date

hr_org_unit_classifications_f t

where (trunc(nvl(:p_effective_as_at_date,sysdate)) between t.effective_start_date and t.effective_end_date)

and (t.classification_code in (upper('department')))

) c

on (

c.organization_id = t.organization_id

)

-- lookups

-- fnd_appl_taxonomy

-- https://docs.oracle.com/en/cloud/saas/applications-common/22d/oedma/fndappltaxonomy-24773.html#fndappltaxonomy-24773

-- fnd_appl_taxonomy_pk module_id, enterprise_id

-- view fnd_application

-- https://docs.oracle.com/en/cloud/saas/applications-common/22d/oedma/fndapplication-6625.html#fndapplication-6625

-- alternative_id as application_id,

-- where module_type = 'application' and product_line = 1

-- view hcm_lookups

-- https://docs.oracle.com/en/cloud/saas/human-resources/22d/oedmh/hcmlookups-3515.html#hcmlookups-3515

-- from fn d_lookup_values_tl t, fnd_lookup_values_b b

-- where

-- t.lookup_type = b.lookup_type

-- and t.lookup_code = b.lookup_code

-- and t.view_application_id = b.view_application_id

-- and t.set_id = b.set_id

-- and t.language = userenv('lang')

-- and t.view_application_id = 3

-- oracle fusion cloud applications tables and views for common features f61430-01 22d

-- https://docs.oracle.com/en/cloud/saas/applications-common/22d/oedma/fndlookupvaluesb-15092.html#fndlookupvaluesb-15092

-- https://docs.oracle.com/en/cloud/saas/applications-common/22d/oedma/fndlookupvaluestl-13887.html#fndlookupvaluestl-13887

-- fnd_lookup_values_tl_pk lookup_type, lookup_code, view_application_id, set_id, language, enterprise_id, sandbox_id

-- fnd_lookup_values_tl_u1  unique  default lookup_type, view_application_id, lookup_code, set_id, language, enterprise_id, sandbox_id, ora_seed_set1

-- fnd_lookup_values_tl_u11 unique  default lookup_type, view_application_id, lookup_code, set_id, language, enterprise_id, sandbox_id, ora_seed_set2

-- fnd_lookup_values_tl_u2  unique  default lookup_type, view_application_id, meaning, set_id, language, enterprise_id, sandbox_id, ora_seed_set1

-- fnd_lookup_values_tl_u21 unique  default lookup_type, view_application_id, meaning, set_id, language, enterprise_id, sandbox_id, ora_seed_set2

left outer join

(

select all

tl.lookup_type

, tl.lookup_code

, tl.view_application_id

, tl.set_id

, tl.enterprise_id

, tl.sandbox_id

, tl.language

, tl.meaning

from fnd_lookup_values_tl tl

where tl.language = upper('US')

and tl.view_application_id = 3

and tl.lookup_type = upper('ACTIVE_INACTIVE')

--and tl.set_id = 0

--and tl.enterprise_id = 1

--and tl.sandbox_id = 1

) lkp_status

on (

lkp_status.lookup_code = c.status

)

) department
left outer join

( -- costcenter

select all

c.org_unit_classification_id as organization_unit_classification_id

, c.effective_start_date as organization_unit_classification_effective_start_date

, c.effective_end_date as organization_unit_classification_effective_end_date

, c.classification_code as organization_classification_code

, c.organization_id as organization_id

, t.org_information_id as organization_information_id

, t.effective_start_date as organization_information_effective_start_date

, t.effective_end_date as organization_information_effective_end_date

, t.org_information_context as organization_information_context

, t.record_identifier --per_organization_information_eff per_gl_cost_center_info 7

, t.cost_center_valueset --per_organization_information_eff per_gl_cost_center_info 4

, t.cost_center as cost_center_code --per_organization_information_eff per_gl_cost_center_info 1

, t.cost_center_manager as cost_center_manager_person_id --per_organization_information_eff per_gl_cost_center_info 6 --person_id

, p.value as cost_center_manager_name

, p.description as cost_center_manager_desc

, to_char(p.xxjob_level) as cost_center_manager_job_level

from

(

select all t.* from

-- https://docs.oracle.com/en/cloud/saas/human-resources/23b/oedmh/hrorgunitclassificationsf-22540.html#hrorgunitclassificationsf-22540

-- hr_org_unit_classification_pk    unique  default org_unit_classification_id, effective_start_date, effective_end_date

-- hr_org_unit_class_u2 unique  default organization_id, classification_code, effective_start_date, effective_end_date

hr_org_unit_classifications_f t

where (trunc(nvl(:p_effective_as_at_date,sysdate)) between t.effective_start_date and t.effective_end_date)

) c

inner join

(

select all t.*

-- manage organization structure extensible flexfields --per_organization_information_eff

-- context department/gl cost center information

, t.org_information7 as record_identifier

, t.org_information4 as cost_center_valueset

, t.org_information1 as cost_center

, t.org_information6 as cost_center_manager

from

--https://docs.oracle.com/en/cloud/saas/human-resources/24c/oedmh/hrorganizationinformationf-15606.html

--HR_ORG_INFORMATION_PK ORG_INFORMATION_ID, EFFECTIVE_START_DATE, EFFECTIVE_END_DATE

hr_organization_information_f t

where (trunc(nvl(:p_effective_as_at_date,sysdate)) between t.effective_start_date and t.effective_end_date)

and t.org_information_context = upper('PER_GL_COST_CENTER_INFO')) t

on (

t.organization_id = c.organization_id

)

left outer join

(

select all --count() over (partition by null, t.id) as n_person_id,

t.

from

--https://docs.oracle.com/en/cloud/saas/human-resources/24c/oedmh/perpersonlistv-5504.html

--select all to_char(pn.person_id) as id

--pn.list_name as value

--'('||p.person_number ||') '

--|| '('||l.meaning ||') ' --HCM_LOOKUPS l 'PER_ASS_SYS_STATUS'

--|| case when j.name is not null then j.name || ' - ' end

--|| case when a.assignment_number<>a.assignment_name then a.assignment_number||' - '||a.assignment_name else a.assignment_number end

--|| decode(pn.legislation_code,null,'',' ('||pn.legislation_code||')') as description

--row_number() over (partition by person_id order by decode(assignment_status_type

--,'active','1'

--,'suspended','2','3'||assignment_status_type)

--,effective_end_date desc

--, effective_start_date desc, rowid ) rn

--primary_flag='Y'

--and assignment_type in('E','C')

--and trunc(sysdate) between effective_start_date and effective_end_date

--and effective_latest_change = 'Y'

--per_person_list_v t

(

select all

to_char(pn.person_id) as id

, pn.list_name as value

, '('||p.person_number ||') '

|| '('||l.meaning ||') '

|| case when j.name is not null then j.name || ' - ' end

|| case when a.assignment_number<>a.assignment_name then a.assignment_number||' - '||a.assignment_name else a.assignment_number end

|| decode(pn.legislation_code,null,'',' ('||pn.legislation_code||')')

as description

, j.approval_authority as xxjob_level

from per_person_names_f pn

,

lateral(select * from

(

select person_id,assignment_number,assignment_name,job_id,assignment_status_type

, row_number() over (partition by

person_id

order by

decode(assignment_status_type,'ACTIVE','1','SUSPENDED','2','3'||assignment_status_type)

,effective_end_date desc

, effective_start_date desc

, rowid ) rn

from per_all_assignments_m a

where pn.person_id = a.person_id

and primary_flag = upper('Y')

and ASSIGNMENT_TYPE in (upper('E'), upper('C'))

and trunc(sysdate) between effective_start_date and effective_end_date

and effective_latest_change = upper('Y')

) where rn = 1

) a

, per_all_people_f p

, per_jobs_f_vl j

, hcm_lookups l

where pn.person_id= p.person_id

and pn.name_type = upper('GLOBAL')

and (trunc(sysdate) between pn.effective_start_date and pn.effective_end_date)

and (trunc(sysdate) between p.effective_start_date and p.effective_end_date)

and a.job_id = j.job_id(+)

and (trunc(sysdate) between j.effective_start_date(+) and j.effective_end_date(+))

and l.lookup_type(+) = upper('PER_ASS_SYS_STATUS')

and a.assignment_status_type=l.lookup_code(+)

) t

) p

on (

p.id = t.cost_center_manager

)

) costcenter

on (

department.organization_unit_classification_id = costcenter.organization_unit_classification_id

and department.organization_unit_classification_effective_start_date = costcenter.organization_unit_classification_effective_start_date

and department.organization_unit_classification_effective_end_date = costcenter.organization_unit_classification_effective_end_date

)

Fazal Subhan111

 You could try below.     
  
  SELECT  hauft.organization_id department_id

           ,hauft.name department_name

           ,nvl (haouf.attribute2

                ,haouf.attribute3) cost_center

    FROM    hr_org_unit_classifications_f houcf

           ,hr_all_organization_units_f haouf

           ,hr_organization_units_f_tl hauft

    WHERE   haouf.organization_id = houcf.organization_id

    AND     haouf.organization_id = hauft.organization_id

    AND     trunc (sysdate) BETWEEN houcf.effective_start_date

                            AND     houcf.effective_end_date

    AND     trunc (sysdate) BETWEEN hauft.effective_start_date

                            AND     hauft.effective_end_date

    AND     trunc (sysdate) BETWEEN haouf.effective_start_date

                            AND     haouf.effective_end_date

    AND     hauft.language = 'US'

    AND     houcf.classification_code = 'DEPARTMENT'