VIEW: APPS.HRFV_ORGANIZATION_ROLLUP

Source

View Text - Preformatted

SELECT bgrT.name business_group_name ,str.name organization_hierarchy ,hr_bis.bis_decode_lookup('YES_NO',str.primary_structure_flag) primary_flag ,orgtopT.name top_organization ,orggroupT.name group_organization ,orgsubT.name organization ,decode(opa.organization_process, 'ISNR', hr_bis.bis_decode_lookup('YES_NO','Y'), 'ISRO', hr_bis.bis_decode_lookup('YES_NO','Y'), hr_bis.bis_decode_lookup('YES_NO','N')) include_subordinates ,decode(opa.organization_process, 'ISNR', hr_bis.bis_decode_lookup('YES_NO','N'), 'SINR', hr_bis.bis_decode_lookup('YES_NO','N'), hr_bis.bis_decode_lookup('YES_NO','Y')) rollup ,str.business_group_id business_group_id ,opa.organization_id top_org_id ,opl.organization_id_group rollup_org_id ,opl.organization_id_child org_id FROM per_organization_structures str , hri_org_param_list opl , hri_org_params opa , hr_all_organization_units_tl bgrT , hr_all_organization_units_tl orgtopT , hr_all_organization_units_tl orggroupT , hr_all_organization_units_tl orgsubT WHERE str.organization_structure_id = opa.organization_structure_id AND opl.org_param_id = opa.org_param_id AND orgtopT.organization_id = opa.organization_id AND orgtopT.language = userenv('LANG') AND orggroupT.organization_id = opl.organization_id_group AND orggroupT.language = userenv('LANG') AND orgsubT.organization_id = opl.organization_id_child AND orgsubT.language = userenv('LANG') AND str.business_group_id = bgrT.organization_id AND bgrT.language = userenv('LANG') AND str.business_group_id = NVL(hr_bis.get_sec_profile_bg_id, str.business_group_id) WITH READ ONLY

View Text - HTML Formatted

SELECT BGRT.NAME BUSINESS_GROUP_NAME
, STR.NAME ORGANIZATION_HIERARCHY
, HR_BIS.BIS_DECODE_LOOKUP('YES_NO'
, STR.PRIMARY_STRUCTURE_FLAG) PRIMARY_FLAG
, ORGTOPT.NAME TOP_ORGANIZATION
, ORGGROUPT.NAME GROUP_ORGANIZATION
, ORGSUBT.NAME ORGANIZATION
, DECODE(OPA.ORGANIZATION_PROCESS
, 'ISNR'
, HR_BIS.BIS_DECODE_LOOKUP('YES_NO'
, 'Y')
, 'ISRO'
, HR_BIS.BIS_DECODE_LOOKUP('YES_NO'
, 'Y')
, HR_BIS.BIS_DECODE_LOOKUP('YES_NO'
, 'N')) INCLUDE_SUBORDINATES
, DECODE(OPA.ORGANIZATION_PROCESS
, 'ISNR'
, HR_BIS.BIS_DECODE_LOOKUP('YES_NO'
, 'N')
, 'SINR'
, HR_BIS.BIS_DECODE_LOOKUP('YES_NO'
, 'N')
, HR_BIS.BIS_DECODE_LOOKUP('YES_NO'
, 'Y')) ROLLUP
, STR.BUSINESS_GROUP_ID BUSINESS_GROUP_ID
, OPA.ORGANIZATION_ID TOP_ORG_ID
, OPL.ORGANIZATION_ID_GROUP ROLLUP_ORG_ID
, OPL.ORGANIZATION_ID_CHILD ORG_ID
FROM PER_ORGANIZATION_STRUCTURES STR
, HRI_ORG_PARAM_LIST OPL
, HRI_ORG_PARAMS OPA
, HR_ALL_ORGANIZATION_UNITS_TL BGRT
, HR_ALL_ORGANIZATION_UNITS_TL ORGTOPT
, HR_ALL_ORGANIZATION_UNITS_TL ORGGROUPT
, HR_ALL_ORGANIZATION_UNITS_TL ORGSUBT
WHERE STR.ORGANIZATION_STRUCTURE_ID = OPA.ORGANIZATION_STRUCTURE_ID
AND OPL.ORG_PARAM_ID = OPA.ORG_PARAM_ID
AND ORGTOPT.ORGANIZATION_ID = OPA.ORGANIZATION_ID
AND ORGTOPT.LANGUAGE = USERENV('LANG')
AND ORGGROUPT.ORGANIZATION_ID = OPL.ORGANIZATION_ID_GROUP
AND ORGGROUPT.LANGUAGE = USERENV('LANG')
AND ORGSUBT.ORGANIZATION_ID = OPL.ORGANIZATION_ID_CHILD
AND ORGSUBT.LANGUAGE = USERENV('LANG')
AND STR.BUSINESS_GROUP_ID = BGRT.ORGANIZATION_ID
AND BGRT.LANGUAGE = USERENV('LANG')
AND STR.BUSINESS_GROUP_ID = NVL(HR_BIS.GET_SEC_PROFILE_BG_ID
, STR.BUSINESS_GROUP_ID) WITH READ ONLY