1 PACKAGE BODY hri_mtdt_conc_request AS
2 /* $Header: hrimcncr.pkb 120.4 2006/12/01 13:56:36 jtitmas noship $ */
3
4 TYPE g_varchar2_tab_type IS TABLE OF VARCHAR2(240) INDEX BY VARCHAR2(240);
5
6 g_workforce_owned_tables g_varchar2_tab_type;
7 g_absence_owned_tables g_varchar2_tab_type;
8 g_recruitment_owned_tables g_varchar2_tab_type;
9
10 -- ------------------------------------------------------------
11 -- Defines which tables will be fully refreshed by area
12 -- ------------------------------------------------------------
13 PROCEDURE set_metadata IS
14
15 BEGIN
16
17 -- Tables to full refresh in workforce initial load request set
18 g_workforce_owned_tables('HRI_CS_SUPH') := 'Y';
19 g_workforce_owned_tables('HRI_CS_ORGH_CT') := 'Y';
20 g_workforce_owned_tables('HRI_CS_JOBH_CT') := 'Y';
21 g_workforce_owned_tables('HRI_CS_JOB_JOB_ROLE_CT') := 'Y';
22 g_workforce_owned_tables('HRI_CS_GEO_LOCHR_CT') := 'Y';
23 g_workforce_owned_tables('HRI_CS_POW_BAND_CT') := 'Y';
24 g_workforce_owned_tables('HRI_CS_PRSNTYP_CT') := 'Y';
25 g_workforce_owned_tables('HRI_INV_SPRTN_RSNS') := 'Y';
26 g_workforce_owned_tables('HRI_MB_ASGN_EVENTS_CT') := 'Y';
27 g_workforce_owned_tables('HRI_CL_WKR_SUP_STATUS_CT') := 'Y';
28 g_workforce_owned_tables('HRI_MAP_SUP_WRKFC_ASG') := 'Y';
29 g_workforce_owned_tables('HRI_MAP_SUP_WRKFC') := 'Y';
30 g_workforce_owned_tables('HRI_MB_WRKFC_EVT_CT') := 'Y';
31 g_workforce_owned_tables('HRI_MDS_WRKFC_MNTH_CT') := 'Y';
32 g_workforce_owned_tables('HRI_MDS_WRKFC_MGRH_C01_CT') := 'Y';
33 g_workforce_owned_tables('HRI_MDS_WRKFC_ORGH_C01_CT') := 'Y';
34 g_workforce_owned_tables('HRI_CS_PER_PERSON_CT') := 'Y';
35
36 -- Tables to full refresh in absence initial load request set
37 g_absence_owned_tables('HRI_CS_ABSENCE_CT') := 'Y';
38 g_absence_owned_tables('HRI_MB_UTL_ABSNC_CT') := 'Y';
39 g_absence_owned_tables('HRI_MDP_SUP_ABSNC_OCC_CT') := 'Y';
40
41 -- Tables to full refresh in recruitment initial load request set
42 g_recruitment_owned_tables('HRI_MB_REC_CAND_PIPLN_CT') := 'Y';
43 g_recruitment_owned_tables('HRI_MB_REC_VACANCT_CT') := 'Y';
44
45 END set_metadata;
46
47 -- ------------------------------------------------------------
48 -- Returns whether a table belongs to an area based on
49 -- the area associated with the page
50 -- ------------------------------------------------------------
51 FUNCTION is_table_owned_by_page
52 (p_page_name IN VARCHAR2,
53 p_page_type IN VARCHAR2,
54 p_table_name IN VARCHAR2)
55 RETURN VARCHAR2 IS
56
57 l_return_result VARCHAR2(30);
58
59 BEGIN
60
61 -- Trap no data found if no ownership is found
62 BEGIN
63
64 -- BI 2006 Subject Area
65 IF p_page_type = 'REPORT' THEN
66
67 -- If the page is absence check that area
68 IF (p_page_name = 'HRI_ABSENCE_SUBJECTAREA') THEN
69
70 l_return_result := g_absence_owned_tables(p_table_name);
71
72 -- If the page is workforce check that area
73 ELSIF (p_page_name = 'HRI_WORKFORCE_SUBJECTAREA') THEN
74
75 l_return_result := g_workforce_owned_tables(p_table_name);
76
77 -- If the page is recruitment check that area
78 ELSIF (p_page_name = 'HRI_RECRUITMENT_SUBJECTAREA') THEN
79
80 l_return_result := g_recruitment_owned_tables(p_table_name);
81
82 -- Otherwise no ownership
83 ELSE
84 l_return_result := 'N';
85
86 END IF;
87
88 -- DBI page type
89 ELSE
90
91 -- If the page is absence check that area
92 IF (p_page_name = 'HRI_DBI_LINE_MGR_ABS') THEN
93
94 l_return_result := g_absence_owned_tables(p_table_name);
95
96 -- If the page is workforce check that area
97 ELSIF (p_page_name = 'HRI_DBI_CHO_HDC' OR
98 p_page_name = 'HRI_DBI_CHO_OVR' OR
99 p_page_name = 'HRI_DBI_CHO_TRN' OR
100 p_page_name = 'HRI_DBI_LINE_MGR_TRN' OR
101 p_page_name = 'HRI_DBI_LINE_MGR_WMV' OR
102 p_page_name = 'HRI_DBI_LINE_MGR_WMV_C' OR
103 p_page_name = 'HRI_DBI_OA_LINE_MGR') THEN
104
105 l_return_result := g_workforce_owned_tables(p_table_name);
106
107 -- Otherwise no ownership
108 ELSE
109 l_return_result := 'N';
110
111 END IF;
112
113 END IF;
114
115 -- If no data found there is no ownership
116 EXCEPTION WHEN OTHERS THEN
117 l_return_result := 'N';
118 END;
119
120 RETURN l_return_result;
121
122 END is_table_owned_by_page;
123
124 -- ------------------------------------------------------------
125 -- Returns whether a table belongs to an area based on
126 -- whether any page in the list owns the table
127 -- ------------------------------------------------------------
128 FUNCTION is_table_owned_by_page
129 (p_page_list IN hri_bpl_conc_admin.page_list_tab_type,
130 p_table_name IN VARCHAR2)
131 RETURN VARCHAR2 IS
132
133 l_page_found VARCHAR2(30);
134 l_index PLS_INTEGER;
135
136 BEGIN
137
138 -- Initialize variables
139 l_page_found := 'N';
140
141 -- Trap exception if page list is empty
142 BEGIN
143
144 -- Loop through the pages in the list
145 l_index := p_page_list.FIRST;
146 WHILE l_index IS NOT NULL LOOP
147
148 -- Check whether each page owns the table
149 IF (is_table_owned_by_page
150 (p_page_name => p_page_list(l_index).page_name,
151 p_page_type => p_page_list(l_index).page_type,
152 p_table_name => p_table_name) = 'Y') THEN
153 l_page_found := 'Y';
154 END IF;
155
156 -- Move to next page in list
157 l_index := p_page_list.NEXT(l_index);
158
159 END LOOP;
160
161 EXCEPTION WHEN OTHERS THEN
162 null;
163 END;
164
165 RETURN l_page_found;
166
167 END is_table_owned_by_page;
168
169 -- ------------------------------------------------------------
170 -- Returns whether a table is a core table i.e. shared
171 -- among different HRI applications e.g. DBI, Discoverer
172 -- ------------------------------------------------------------
173 FUNCTION is_core_hri_process
174 (p_table_name IN VARCHAR2)
175 RETURN VARCHAR2 IS
176
177 BEGIN
178
179 -- Currently only the supervisor hierarchy table
180 -- is common between DBI and non-DBI
181 IF (p_table_name = 'HRI_CS_SUPH') THEN
182
183 RETURN 'Y';
184
185 END IF;
186
187 RETURN 'N';
188
189 END is_core_hri_process;
190
191 -- ------------------------------------------------------------
192 -- Initializes metadata
193 -- ------------------------------------------------------------
194 BEGIN
195
196 set_metadata;
197
198 END hri_mtdt_conc_request;
[Home] [Help]