VIEW: APPS.EDWBV_ORGA_BUSINESS_GRP_LCV

Source

View Text - Preformatted

SELECT to_char(bgr.organization_id) || '-' || inst.instance_code business_group_pk ,'ALL' all_fk ,bgr.name name ,bgr.name business_group_dp ,bgr.date_from date_from ,bgr.date_to date_to ,hr_general.decode_lookup('INTL_EXTL',bgr.internal_external_flag) int_ext_flag ,mp.organization_code org_code ,hr_general.decode_lookup('ORG_TYPE',bgr.type) org_type ,mp.primary_cost_method primary_cst_mthd ,trt.territory_short_name legislation ,flx.id_flex_structure_name cost_allocation_flexfield ,inst.instance_code instance ,bgr.organization_id business_group_id ,'_DF:PER:PER_ORGANIZATION_UNITS:bgr' ,greatest(NVL(bgr.last_update_date,to_date('01-01-2000','DD-MM-YYYY')), NVL(mp.last_update_date, to_date('01-01-2000','DD-MM-YYYY'))) last_update_date ,bgr.creation_date creation_date FROM hr_all_organization_units bgr ,hr_organization_information oi ,hr_organization_information oi2 ,mtl_parameters mp ,fnd_territories_vl trt ,fnd_id_flex_structures_vl flx ,edw_local_instance inst WHERE bgr.organization_id = oi.organization_id AND oi.org_information_context = 'CLASS' AND oi.org_information1 = 'HR_BG' AND oi.org_information2 = 'Y' AND oi2.organization_id = bgr.organization_id AND oi2.org_information_context = 'Business Group Information' AND oi2.org_information7 = flx.id_flex_num AND flx.application_id = 801 AND flx.id_flex_code = 'COST' AND oi2.org_information9 = trt.territory_code AND bgr.organization_id = mp.organization_id (+)

View Text - HTML Formatted

SELECT TO_CHAR(BGR.ORGANIZATION_ID) || '-' || INST.INSTANCE_CODE BUSINESS_GROUP_PK
, 'ALL' ALL_FK
, BGR.NAME NAME
, BGR.NAME BUSINESS_GROUP_DP
, BGR.DATE_FROM DATE_FROM
, BGR.DATE_TO DATE_TO
, HR_GENERAL.DECODE_LOOKUP('INTL_EXTL'
, BGR.INTERNAL_EXTERNAL_FLAG) INT_EXT_FLAG
, MP.ORGANIZATION_CODE ORG_CODE
, HR_GENERAL.DECODE_LOOKUP('ORG_TYPE'
, BGR.TYPE) ORG_TYPE
, MP.PRIMARY_COST_METHOD PRIMARY_CST_MTHD
, TRT.TERRITORY_SHORT_NAME LEGISLATION
, FLX.ID_FLEX_STRUCTURE_NAME COST_ALLOCATION_FLEXFIELD
, INST.INSTANCE_CODE INSTANCE
, BGR.ORGANIZATION_ID BUSINESS_GROUP_ID
, '_DF:PER:PER_ORGANIZATION_UNITS:BGR'
, GREATEST(NVL(BGR.LAST_UPDATE_DATE
, TO_DATE('01-01-2000'
, 'DD-MM-YYYY'))
, NVL(MP.LAST_UPDATE_DATE
, TO_DATE('01-01-2000'
, 'DD-MM-YYYY'))) LAST_UPDATE_DATE
, BGR.CREATION_DATE CREATION_DATE
FROM HR_ALL_ORGANIZATION_UNITS BGR
, HR_ORGANIZATION_INFORMATION OI
, HR_ORGANIZATION_INFORMATION OI2
, MTL_PARAMETERS MP
, FND_TERRITORIES_VL TRT
, FND_ID_FLEX_STRUCTURES_VL FLX
, EDW_LOCAL_INSTANCE INST
WHERE BGR.ORGANIZATION_ID = OI.ORGANIZATION_ID
AND OI.ORG_INFORMATION_CONTEXT = 'CLASS'
AND OI.ORG_INFORMATION1 = 'HR_BG'
AND OI.ORG_INFORMATION2 = 'Y'
AND OI2.ORGANIZATION_ID = BGR.ORGANIZATION_ID
AND OI2.ORG_INFORMATION_CONTEXT = 'BUSINESS GROUP INFORMATION'
AND OI2.ORG_INFORMATION7 = FLX.ID_FLEX_NUM
AND FLX.APPLICATION_ID = 801
AND FLX.ID_FLEX_CODE = 'COST'
AND OI2.ORG_INFORMATION9 = TRT.TERRITORY_CODE
AND BGR.ORGANIZATION_ID = MP.ORGANIZATION_ID (+)