PER Organizations

Description
Categories: Enginatics, Toolkit - Operations, Toolkit - Setup & Support
Repository: Github Columns: Business Group, Country, Type, Org, Org Code, Internal Or External, Location Code, Address, Organization Id ...
Master data report showing organizations, classifications, address details and additional information attributes.
select
haout0.name business_group,
ftv.territory_short_name country,
flv1.meaning type,
haout.name organization,
mp.organization_code,
decode(haouv.internal_external_flag,'EXT','External','INT','Internal') internal_or_external,
&col_classification
hla.location_code,
nvl2(hla.address_line_1,hla.address_line_1||' ','')||
nvl2(hla.address_line_2,hla.address_line_2||' ','')||
nvl2(hla.address_line_3,hla.address_line_3||' ','')||
nvl2(hla.town_or_city,hla.town_or_city||' ','')||
nvl2(hla.region_2,hla.region_2||' ','')||
hla.postal_code address,
&col_attributes
haouv.organization_id
from
hr_all_organization_units_vl haouv,
hr_all_organization_units_vl haouv0,
hr_all_organization_units_tl haout,
hr_all_organization_units_tl haout0,
fnd_lookup_values flv1,
hr_locations_all hla,
fnd_territories_vl ftv,
(select hoi.* from hr_organization_information hoi where '&enable_classification'='Y' and hoi.org_information_context='CLASS' and hoi.org_information2='Y') hoi,
fnd_lookup_values flv2,
(
select
fdfcuv.application_id,
fdfcuv.descriptive_flexfield_name,
fdfcuv.application_column_name,
fdfcuv.column_seq_num,
fdfcuv.form_left_prompt attribute_name,
decode(fdfcuv.application_column_name,
'ORG_INFORMATION1',hoi.org_information1,
'ORG_INFORMATION2',hoi.org_information2,
'ORG_INFORMATION3',hoi.org_information3,
'ORG_INFORMATION4',hoi.org_information4,
'ORG_INFORMATION5',hoi.org_information5,
'ORG_INFORMATION6',hoi.org_information6,
'ORG_INFORMATION7',hoi.org_information7,
'ORG_INFORMATION8',hoi.org_information8,
'ORG_INFORMATION9',hoi.org_information9,
'ORG_INFORMATION10',hoi.org_information10,
'ORG_INFORMATION11',hoi.org_information11,
'ORG_INFORMATION12',hoi.org_information12,
'ORG_INFORMATION13',hoi.org_information13,
'ORG_INFORMATION14',hoi.org_information14,
'ORG_INFORMATION15',hoi.org_information15,
'ORG_INFORMATION16',hoi.org_information16,
'ORG_INFORMATION17',hoi.org_information17,
'ORG_INFORMATION18',hoi.org_information18,
'ORG_INFORMATION19',hoi.org_information19,
'ORG_INFORMATION20',hoi.org_information20
) system_value,
(select distinct
listagg(flv.meaning,', ') within group (order by flv.meaning) over (partition by hoi.organization_id) info_class
from
hr_org_info_types_by_class hoitbc,
hr_organization_information hoi0,
fnd_lookup_values flv
where
hoi.org_information_context=hoitbc.org_information_type and
hoi.organization_id=hoi0.organization_id and
hoi0.org_information_context='CLASS' and
hoi0.org_information2='Y' and
hoi0.org_information1=hoitbc.org_classification and
hoi0.org_information1=flv.lookup_code(+) and
flv.lookup_type(+)='ORG_CLASS' and
flv.language(+)=userenv('lang') and
flv.view_application_id(+)=3 and
flv.security_group_id(+)=0
) info_class,
hoit.displayed_org_information_type information_type,
hoi.*,
hoi.rowid row_id
from
hr_organization_information hoi,
fnd_descr_flex_col_usage_vl fdfcuv,
hr_org_information_types hoit
where
2=2 and
'&enable_attributes'='Y' and
hoi.org_information_context<>'CLASS' and
hoi.org_information_context=fdfcuv.descriptive_flex_context_code and
fdfcuv.application_id=800 and
fdfcuv.descriptive_flexfield_name='Org Developer DF' and
hoi.org_information_context=hoit.org_information_type(+)
) x,
mtl_parameters mp
where
1=1 and
haouv.business_group_id=haouv0.organization_id(+) and
haouv.organization_id=haout.organization_id and
haouv0.organization_id=haout0.organization_id and
haout.language=userenv('lang') and
haout0.language=userenv('lang') and
haouv.organization_id=mp.organization_id(+) and
haouv.type=flv1.lookup_code(+) and
flv1.lookup_type(+)='ORG_TYPE' and
flv2.lookup_type(+)='ORG_CLASS' and
flv1.language(+)=userenv('lang') and
flv2.language(+)=userenv('lang') and
flv1.view_application_id(+)=3 and
flv2.view_application_id(+)=3 and
flv1.security_group_id(+)=0 and
flv2.security_group_id(+)=0 and
haouv.location_id=hla.location_id(+) and
hla.country=ftv.territory_code(+) and
haouv.organization_id=hoi.organization_id(+) and
hoi.org_information1=flv2.lookup_code(+) and
haouv.organization_id=x.organization_id(+)
order by
haouv0.name,
ftv.territory_short_name,
flv1.meaning,
haouv.name,
flv2.meaning,
x.info_class,
x.information_type,
x.org_information_id,
x.column_seq_num
Parameter Name SQL text Validation
Country
ftv.territory_short_name=:country
LOV
Organization Type
flv1.meaning=:type
LOV
Organization
haouv.name=:org_name
LOV
Business Group
haouv0.name=:business_group
LOV
Organization Classification
haouv.organization_id in (
select
hoi.organization_id
from
hr_organization_information hoi
where
hoi.org_information1=xxen_util.lookup_code(:classification,'ORG_CLASS',3) and
hoi.org_information_context='CLASS' and
hoi.org_information2='Y'
)
LOV
Show Attributes
Y
LOV
Internal or External
decode(haouv.internal_external_flag,'EXT','External','INT','Internal')=:internal_external
LOV
Information Type
hoit.displayed_org_information_type=:information_type
LOV
Attribute Name
fdfcuv.form_left_prompt=:attribute_name
LOV
Expand Classifications
Y
LOV Oracle
Show Attributes
x.info_class,
x.information_type,
x.attribute_name,
decode(x.system_value,null,null,xxen_util.display_flexfield_value(x.application_id,x.descriptive_flexfield_name,x.org_information_context,x.application_column_name,x.row_id,haouv.business_group_id,haouv.organization_id,x.org_information_id)) value,
x.application_column_name,
x.system_value,
Information Type
x.info_class,
x.information_type,
x.attribute_name,
decode(x.system_value,null,null,xxen_util.display_flexfield_value(x.application_id,x.descriptive_flexfield_name,x.org_information_context,x.application_column_name,x.row_id,haouv.business_group_id,haouv.organization_id,x.org_information_id)) value,
x.system_value,
Information Type
Y
Information Type
x.information_type=:information_type
Attribute Name
x.info_class,
x.information_type,
x.attribute_name,
decode(x.system_value,null,null,xxen_util.display_flexfield_value(x.application_id,x.descriptive_flexfield_name,x.org_information_context,x.application_column_name,x.row_id,haouv.business_group_id,haouv.organization_id,x.org_information_id)) value,
x.system_value,
Attribute Name
Y
Attribute Name
x.attribute_name=:attribute_name
Expand Classifications
(select 'BG' from hr_organization_information hoi where haouv.organization_id=hoi.organization_id and hoi.org_information1='HR_BG' and hoi.org_information_context='CLASS' and hoi.org_information2='Y') bg,
(select 'LE' from hr_organization_information hoi where haouv.organization_id=hoi.organization_id and hoi.org_information1='HR_LEGAL' and hoi.org_information_context='CLASS' and hoi.org_information2='Y') le,
(select 'OU' from hr_organization_information hoi where haouv.organization_id=hoi.organization_id and hoi.org_information1='OPERATING_UNIT' and hoi.org_information_context='CLASS' and hoi.org_information2='Y') ou,
(select 'INV' from hr_organization_information hoi where haouv.organization_id=hoi.organization_id and hoi.org_information1='INV' and hoi.org_information_context='CLASS' and hoi.org_information2='Y') inv,
(select 'PJM' from hr_organization_information hoi where haouv.organization_id=hoi.organization_id and hoi.org_information1='PJM' and hoi.org_information_context='CLASS' and hoi.org_information2='Y') pjm,
(select 'WIP' from hr_organization_information hoi where haouv.organization_id=hoi.organization_id and hoi.org_information1='WIP' and hoi.org_information_context='CLASS' and hoi.org_information2='Y') wip,
(select 'MRP' from hr_organization_information hoi where haouv.organization_id=hoi.organization_id and hoi.org_information1='MRP' and hoi.org_information_context='CLASS' and hoi.org_information2='Y') mrp,
(select 'FA' from hr_organization_information hoi where haouv.organization_id=hoi.organization_id and hoi.org_information1='FA_ORG' and hoi.org_information_context='CLASS' and hoi.org_information2='Y') fa,
(select 'HR' from hr_organization_information hoi where haouv.organization_id=hoi.organization_id and hoi.org_information1='HR_ORG' and hoi.org_information_context='CLASS' and hoi.org_information2='Y') hr,
(select 'CC' from hr_organization_information hoi where haouv.organization_id=hoi.organization_id and hoi.org_information1='CC' and hoi.org_information_context='CLASS' and hoi.org_information2='Y') cc,
(select distinct
listagg(flv.meaning,', ') within group (order by flv.meaning) over (partition by hoi.organization_id) classification
from
hr_organization_information hoi,
fnd_lookup_values flv
where
haouv.organization_id=hoi.organization_id and
hoi.org_information_context='CLASS' and
hoi.org_information2='Y' and
hoi.org_information1=flv.lookup_code(+) and
flv.lookup_type(+)='ORG_CLASS' and
flv.language(+)=userenv('lang') and
flv.view_application_id(+)=3 and
flv.security_group_id(+)=0
) classification,
Expand Classifications
flv2.meaning classification,