-
Notifications
You must be signed in to change notification settings - Fork 60
Expand file tree
/
Copy pathPER Organizations.sql
More file actions
123 lines (122 loc) · 4.82 KB
/
PER Organizations.sql
File metadata and controls
123 lines (122 loc) · 4.82 KB
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
/*************************************************************************/
/* */
/* (c) 2010-2024 Enginatics GmbH */
/* www.enginatics.com */
/* */
/*************************************************************************/
-- Report Name: PER Organizations
-- Description: Master data report showing organizations, classifications, address details and additional information attributes.
-- Excel Examle Output: https://www.enginatics.com/example/per-organizations/
-- Library Link: https://www.enginatics.com/reports/per-organizations/
-- Run Report: https://demo.enginatics.com/
select
haouv0.name business_group,
ftv.territory_short_name country,
hla.town_or_city city,
xxen_util.meaning(haouv.type,'ORG_TYPE',3) type,
haouv.name organization,
mp.organization_code,
decode(haouv.internal_external_flag,'EXT','External','INT','Internal') internal_or_external,
&col_classification
decode(haouv.head_count,0,null,haouv.head_count) head_count,
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.region_2,hla.region_2||', ','') address,
hla.postal_code,
&col_attributes
haouv.date_to,
xxen_util.user_name(haouv.created_by) created_by,
xxen_util.client_time(haouv.creation_date) creation_date,
xxen_util.user_name(haouv.last_updated_by) last_updated_by,
xxen_util.client_time(haouv.last_update_date) last_update_date,
haouv.organization_id
from
(
select
(select count(distinct paaf.person_id) from per_all_assignments_f paaf where haouv.organization_id=paaf.organization_id and paaf.assignment_type in ('E','C') and paaf.primary_flag='Y' and trunc(sysdate) between paaf.effective_start_date and paaf.effective_end_date) head_count,
haouv.*
from
hr_all_organization_units_vl haouv
) haouv,
hr_all_organization_units_vl haouv0,
hr_locations_all hla,
fnd_territories_vl ftv,
(select xxen_util.meaning(hoi.org_information1,'ORG_CLASS',3) classification, hoi.* from hr_organization_information hoi where '&show_classification'='Y' and hoi.org_information_context='CLASS' and hoi.org_information2='Y') hoi,
(
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(hoi0.classification,', ') within group (order by hoi0.classification) over (partition by hoi.organization_id) info_class
from
hr_org_info_types_by_class hoitbc,
(select xxen_util.meaning(hoi.org_information1,'ORG_CLASS',3) classification, hoi.* from hr_organization_information hoi) hoi0
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
) 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
'&show_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.location_id=hla.location_id(+) and
hla.country=ftv.territory_code(+) and
haouv.organization_id=hoi.organization_id(+) and
haouv.organization_id=x.organization_id(+) and
haouv.organization_id=mp.organization_id(+)
order by
haouv0.name,
ftv.territory_short_name,
type,
haouv.name,
hoi.classification,
x.info_class,
x.information_type,
x.org_information_id,
x.column_seq_num