VIEW: APPS.EDWBV_ORGA_LEG_ENTITY_LCV

Source

View Text - Preformatted

SELECT to_char(le.organization_id) || '-' || i.instance_code legal_entity_pk ,to_char(lebg.organization_id) || '-' || i.instance_code business_group_fk ,le.name || ' (' || lebg.name || ')' name ,le.name || ' (' || lebg.name || ')' legal_entity_dp ,sob.name set_of_books ,le.date_from date_from ,le.date_to date_to ,hr_general.decode_lookup('INTL_EXTL',le.internal_external_flag) int_ext_flag ,mp.organization_code org_code ,hr_general.decode_lookup('ORG_TYPE',le.type) org_type ,mp.primary_cost_method primary_cst_mthd ,i.instance_code instance ,le.organization_id legal_entity_id ,'LE' level_name ,'_DF:PER:PER_ORGANIZATION_UNITS:le' ,'_DF:PER:HR_ORGANIZATION_INFORMATION:oi1' ,'_DF:PER:Org Developer DF:oi1' ,'_DF:JG:JG_GL_SETS_OF_BOOKS:sob' ,'_DF:SQLGL:GL_SETS_OF_BOOKS:sob' ,greatest( NVL( le.last_update_date,to_date('01-01-2000','DD-MM-YYYY')), NVL(lebg.last_update_date,to_date('01-01-2000','DD-MM-YYYY')), NVL( oi1.last_update_date,to_date('01-01-2000','DD-MM-YYYY')), NVL( oi2.last_update_date,to_date('01-01-2000','DD-MM-YYYY')), NVL( sob.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 ,le.creation_date creation_date FROM hr_all_organization_units le ,hr_all_organization_units lebg ,hr_organization_information oi1 ,hr_organization_information oi2 ,gl_sets_of_books sob ,mtl_parameters mp ,edw_local_instance i WHERE le.business_group_id = lebg.organization_id AND le.organization_id = oi1.organization_id AND oi1.org_information_context = 'CLASS' AND oi1.org_information1 = 'HR_LEGAL' AND oi1.org_information2 = 'Y' AND le.organization_id = oi2.organization_id (+) AND oi2.org_information_context (+) = 'Legal Entity Accounting' AND to_number(oi2.org_information1) = sob.set_of_books_id (+) AND le.organization_id = mp.organization_id (+)

View Text - HTML Formatted

SELECT TO_CHAR(LE.ORGANIZATION_ID) || '-' || I.INSTANCE_CODE LEGAL_ENTITY_PK
, TO_CHAR(LEBG.ORGANIZATION_ID) || '-' || I.INSTANCE_CODE BUSINESS_GROUP_FK
, LE.NAME || ' (' || LEBG.NAME || ')' NAME
, LE.NAME || ' (' || LEBG.NAME || ')' LEGAL_ENTITY_DP
, SOB.NAME SET_OF_BOOKS
, LE.DATE_FROM DATE_FROM
, LE.DATE_TO DATE_TO
, HR_GENERAL.DECODE_LOOKUP('INTL_EXTL'
, LE.INTERNAL_EXTERNAL_FLAG) INT_EXT_FLAG
, MP.ORGANIZATION_CODE ORG_CODE
, HR_GENERAL.DECODE_LOOKUP('ORG_TYPE'
, LE.TYPE) ORG_TYPE
, MP.PRIMARY_COST_METHOD PRIMARY_CST_MTHD
, I.INSTANCE_CODE INSTANCE
, LE.ORGANIZATION_ID LEGAL_ENTITY_ID
, 'LE' LEVEL_NAME
, '_DF:PER:PER_ORGANIZATION_UNITS:LE'
, '_DF:PER:HR_ORGANIZATION_INFORMATION:OI1'
, '_DF:PER:ORG DEVELOPER DF:OI1'
, '_DF:JG:JG_GL_SETS_OF_BOOKS:SOB'
, '_DF:SQLGL:GL_SETS_OF_BOOKS:SOB'
, GREATEST( NVL( LE.LAST_UPDATE_DATE
, TO_DATE('01-01-2000'
, 'DD-MM-YYYY'))
, NVL(LEBG.LAST_UPDATE_DATE
, TO_DATE('01-01-2000'
, 'DD-MM-YYYY'))
, NVL( OI1.LAST_UPDATE_DATE
, TO_DATE('01-01-2000'
, 'DD-MM-YYYY'))
, NVL( OI2.LAST_UPDATE_DATE
, TO_DATE('01-01-2000'
, 'DD-MM-YYYY'))
, NVL( SOB.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
, LE.CREATION_DATE CREATION_DATE
FROM HR_ALL_ORGANIZATION_UNITS LE
, HR_ALL_ORGANIZATION_UNITS LEBG
, HR_ORGANIZATION_INFORMATION OI1
, HR_ORGANIZATION_INFORMATION OI2
, GL_SETS_OF_BOOKS SOB
, MTL_PARAMETERS MP
, EDW_LOCAL_INSTANCE I
WHERE LE.BUSINESS_GROUP_ID = LEBG.ORGANIZATION_ID
AND LE.ORGANIZATION_ID = OI1.ORGANIZATION_ID
AND OI1.ORG_INFORMATION_CONTEXT = 'CLASS'
AND OI1.ORG_INFORMATION1 = 'HR_LEGAL'
AND OI1.ORG_INFORMATION2 = 'Y'
AND LE.ORGANIZATION_ID = OI2.ORGANIZATION_ID (+)
AND OI2.ORG_INFORMATION_CONTEXT (+) = 'LEGAL ENTITY ACCOUNTING'
AND TO_NUMBER(OI2.ORG_INFORMATION1) = SOB.SET_OF_BOOKS_ID (+)
AND LE.ORGANIZATION_ID = MP.ORGANIZATION_ID (+)