1 PACKAGE BODY HRI_OLTP_PMV_PER_MGR_LOV AS
2 /* $Header: hriopmgrlov.pkb 120.8 2006/02/08 02:53 smohapat noship $ */
3
4 PROCEDURE GET_SQL(p_page_parameter_tbl IN BIS_PMV_PAGE_PARAMETER_TBL,
5 x_custom_sql OUT NOCOPY VARCHAR2,
6 x_custom_output OUT NOCOPY BIS_QUERY_ATTRIBUTES_TBL)
7 IS
8
9 /* Parameter values */
10 l_parameter_rec hri_oltp_pmv_util_param.HRI_PMV_PARAM_REC_TYPE;
11 l_bind_tab hri_oltp_pmv_util_param.HRI_PMV_BIND_TAB_TYPE;
12 l_custom_rec BIS_QUERY_ATTRIBUTES ;
13 l_search VARCHAR2(500);
14 l_value VARCHAR2(250);
15
16 BEGIN
17
18 /* Get common parameter values */
19 hri_oltp_pmv_util_param.get_parameters_from_table
20 (p_page_parameter_tbl => p_page_parameter_tbl,
21 p_parameter_rec => l_parameter_rec,
22 p_bind_tab => l_bind_tab);
23
24 /* Collect Search Value More Window only */
25 FOR i IN 1..p_page_parameter_tbl.COUNT
26 LOOP
27 IF (p_page_parameter_tbl(i).parameter_name = 'HRI_P_CHAR1_GA') THEN
28 l_value := p_page_parameter_tbl(i).parameter_value;
29 END IF;
30 END LOOP;
31 /* Append search filter if Value entered */
32 IF (l_value is not null) THEN
33 l_search:= ' AND upper(value) like upper('''||l_value||''')';
34 ELSE
35 l_search:= ' ';
36 END IF;
37
38 x_custom_sql :=
39
40 '
41 select ID, VALUE, PARENT_ID
42 FROM
43 (
44 SELECT suph.sup_person_id id
45 ,substr(per.value,1,20) value
46 ,asg.supervisor_id parent_id
47 ,suph.effective_start_date effective_date
48 ,TO_CHAR(suph.sup_level) || per.order_by order_by
49 ,suph.sub_person_id selected_mgr_person_id
50 ,suph.sub_level selected_mgr_level
51 ,suph.sup_person_id mgr_person_id
52 ,suph.sup_level mgr_level
53 FROM hri_cs_suph suph
54 ,hri_cl_wkr_sup_status_ct stt
55 ,hri_dbi_cl_per_n_v per
56 ,per_all_assignments_f asg
57 WHERE
58 stt.person_id = suph.sub_person_id
59 AND suph.sub_invalid_flag_code = ''N''
60 AND asg.person_id = suph.sup_person_id
61 AND per.id = suph.sup_person_id
62 AND asg.primary_flag = ''Y''
63 AND asg.assignment_type IN (''E'', ''C'')
64 AND stt.supervisor_flag = ''Y''
65 AND &AS_OF_DATE BETWEEN stt.effective_start_date AND stt.effective_end_date
66 AND &AS_OF_DATE BETWEEN suph.effective_start_date AND suph.effective_end_date
67 AND &AS_OF_DATE BETWEEN per.effective_start_date AND per.effective_end_date
68 AND &AS_OF_DATE BETWEEN asg.effective_start_date AND asg.effective_end_date
69 AND EXISTS (SELECT NULL
70 FROM hri_cs_suph sec
71 WHERE sec.sub_invalid_flag_code = ''N''
72 AND sec.sup_person_id IN (hri_bpl_security.get_apps_signin_person_id,&BIS_SELECTED_TOP_MANAGER)
73 AND sec.sub_person_id = suph.sup_person_id
74 AND &AS_OF_DATE BETWEEN sec.effective_start_date AND sec.effective_end_date )
75 UNION ALL
76 SELECT suph.sub_person_id id
77 ,substr(per.value,1,20) value
78 ,suph.sup_person_id parent_id
79 ,suph.effective_start_date effective_date
80 ,TO_CHAR(suph.sub_level) || per.order_by order_by
81 ,suph.sup_person_id selected_mgr_person_id
82 ,suph.sup_level selected_mgr_level
83 ,suph.sub_person_id mgr_person_id
84 ,suph.sub_level mgr_level
85 FROM hri_cs_suph suph
86 ,hri_cl_wkr_sup_status_ct stt
87 ,hri_dbi_cl_per_n_v per
88 WHERE stt.person_id = suph.sub_person_id
89 AND suph.sub_invalid_flag_code = ''N''
90 AND per.id = suph.sub_person_id
91 AND suph.sub_relative_level = 1
92 AND stt.supervisor_flag = ''Y''
93 AND &AS_OF_DATE BETWEEN stt.effective_start_date AND stt.effective_end_date
94 AND &AS_OF_DATE BETWEEN suph.effective_start_date AND suph.effective_end_date
95 AND &AS_OF_DATE BETWEEN per.effective_start_date AND per.effective_end_date
96 AND EXISTS (SELECT NULL FROM hri_cs_suph sec
97 WHERE sec.sub_invalid_flag_code = ''N''
98 AND sec.sup_person_id IN (hri_bpl_security.get_apps_signin_person_id,&BIS_SELECTED_TOP_MANAGER)
99 AND sec.sub_person_id = suph.sub_person_id
100 AND &AS_OF_DATE BETWEEN sec.effective_start_date AND sec.effective_end_date )
101 )
102 WHERE SELECTED_MGR_PERSON_ID = &HRI_PERSON+HRI_PER_USRDR_H
103 '
104 || l_search ||
105 '
106 ORDER BY order_by';
107
108 EXCEPTION
109 WHEN OTHERS THEN
110 null ;
111 END;
112
113 PROCEDURE GET_SQL_LEAF(p_page_parameter_tbl IN BIS_PMV_PAGE_PARAMETER_TBL,
114 x_custom_sql OUT NOCOPY VARCHAR2,
115 x_custom_output OUT NOCOPY BIS_QUERY_ATTRIBUTES_TBL)
116 IS
117
118 /* Parameter values */
119 l_parameter_rec hri_oltp_pmv_util_param.HRI_PMV_PARAM_REC_TYPE;
120 l_bind_tab hri_oltp_pmv_util_param.HRI_PMV_BIND_TAB_TYPE;
121 l_custom_rec BIS_QUERY_ATTRIBUTES ;
122
123 l_search VARCHAR2(500);
124 l_value VARCHAR2(250);
125
126
127 BEGIN
128
129 /* Initialize out parameters */
130 l_custom_rec := BIS_PMV_PARAMETERS_PUB.INITIALIZE_QUERY_TYPE;
131 x_custom_output := BIS_QUERY_ATTRIBUTES_TBL();
132
133 /* Get common parameter values */
134 hri_oltp_pmv_util_param.get_parameters_from_table
135 (p_page_parameter_tbl => p_page_parameter_tbl,
136 p_parameter_rec => l_parameter_rec,
137 p_bind_tab => l_bind_tab);
138
139 /* Collect Search Value More Window only */
140 FOR i IN 1..p_page_parameter_tbl.COUNT
141 LOOP
142 IF (p_page_parameter_tbl(i).parameter_name = 'HRI_P_CHAR1_GA') THEN
143 l_value := p_page_parameter_tbl(i).parameter_value;
144 END IF;
145 END LOOP;
146 /* Append search filter if Value entered */
147 IF (l_value is not null) THEN
148 l_search:= ' AND upper(value) like upper('''||l_value||''')';
149 ELSE
150 l_search:= ' ';
151 END IF;
152
153 x_custom_sql :=
154
155 '
156 select ID, VALUE, PARENT_ID
157 FROM
158 (
159 SELECT suph.sup_person_id id
160 ,substr(per.value,1,20) value
161 ,asg.supervisor_id parent_id
162 ,suph.effective_start_date effective_date
163 ,TO_CHAR(suph.sup_level) || per.order_by order_by
164 ,suph.sub_person_id selected_mgr_person_id
165 ,suph.sub_level selected_mgr_level
166 ,suph.sup_person_id mgr_person_id
167 ,suph.sup_level mgr_level
168 FROM hri_cs_suph suph
169 ,hri_dbi_cl_per_n_v per
170 ,per_all_assignments_f asg
171 WHERE
172 suph.sub_invalid_flag_code = ''N''
173 AND asg.person_id = suph.sup_person_id
174 AND per.id = suph.sup_person_id
175 AND asg.primary_flag = ''Y''
176 AND asg.assignment_type in (''E'',''C'')
177 AND &AS_OF_DATE BETWEEN suph.effective_start_date AND suph.effective_end_date
178 AND &AS_OF_DATE BETWEEN per.effective_start_date AND per.effective_end_date
179 AND &AS_OF_DATE BETWEEN asg.effective_start_date AND asg.effective_end_date
180 AND EXISTS (SELECT NULL FROM hri_cs_suph sec
181 WHERE sec.sub_invalid_flag_code = ''N''
182 AND sec.sup_person_id IN (hri_bpl_security.get_apps_signin_person_id,&BIS_SELECTED_TOP_MANAGER)
183 AND sec.sub_person_id = suph.sup_person_id
184 AND &AS_OF_DATE BETWEEN sec.effective_start_date
185 AND sec.effective_end_date )
186 AND EXISTS (SELECT NULL FROM hri_cl_wkr_sup_status_ct mstt
187 WHERE mstt.person_id = suph.sup_person_id
188 AND mstt.supervisor_flag = ''Y''
189 AND (mstt.effective_start_date BETWEEN &AS_OF_DATE -365
190 AND &AS_OF_DATE
191 OR &AS_OF_DATE -365 BETWEEN mstt.effective_start_date
192 AND mstt.effective_end_date) )
193 UNION ALL
194 SELECT suph.sub_person_id id
195 ,substr(per.value,1,20) value
196 ,suph.sup_person_id parent_id
197 ,suph.effective_start_date effective_date
198 ,TO_CHAR(suph.sub_level) || per.order_by order_by
199 ,suph.sup_person_id selected_mgr_person_id
200 ,suph.sup_level selected_mgr_level
201 ,suph.sub_person_id mgr_person_id
202 ,suph.sub_level mgr_level
203 FROM hri_cs_suph suph
204 ,hri_cl_wkr_sup_status_ct stt
205 ,hri_dbi_cl_per_n_v per
206 WHERE stt.person_id = suph.sub_person_id
207 AND suph.sub_invalid_flag_code = ''N''
208 AND per.id = suph.sub_person_id
209 AND suph.sub_relative_level = 1
210 AND stt.supervisor_flag = ''Y''
211 AND &AS_OF_DATE BETWEEN stt.effective_start_date AND stt.effective_end_date
212 AND &AS_OF_DATE BETWEEN suph.effective_start_date AND suph.effective_end_date
213 AND &AS_OF_DATE BETWEEN per.effective_start_date AND per.effective_end_date
214 AND EXISTS (SELECT NULL FROM hri_cs_suph sec
215 WHERE sec.sub_invalid_flag_code = ''N''
216 AND sec.sup_person_id IN (hri_bpl_security.get_apps_signin_person_id,&BIS_SELECTED_TOP_MANAGER)
217 AND sec.sub_person_id = suph.sub_person_id
218 AND &AS_OF_DATE BETWEEN sec.effective_start_date AND sec.effective_end_date )
219 )
220 WHERE SELECTED_MGR_PERSON_ID = &HRI_PERSON+HRI_PER_USRDR_H
221 '
222 || l_search ||
223 '
224 ORDER BY order_by';
225
226
227 EXCEPTION
228 WHEN OTHERS THEN
229 null ;
230 END;
231
232 END;
[Home] [Help]