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

Question

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)

Fazal Subhan111 · Answer

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'

Nathan CCC · Answer

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 )

User_5XSJQ · Answer

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/