PACKAGE BODY: APPS.HRI_OLTP_PMV_PER_ORGMGR_LOV

Source

1 PACKAGE BODY HRI_OLTP_PMV_PER_ORGMGR_LOV AS
2 /* $Header: hriopomlov.pkb 120.2 2005/12/06 02:27:24 rlpatil 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(100);
14   l_value                VARCHAR2(100);
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_CHAR8_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       'SELECT ID,
40               VALUE,
41 	      PARENT_ID
42          FROM
43       (SELECT distinct mgr.sup_person_id id,
44               per.value value,
45 	      mgr.sup_person_id parent_id,
46 	      TO_CHAR(mgr.sup_level) || per.order_by order_by,
47 	      mgr.effective_start_date,
48 	      mgr.effective_end_date,
49 	      mgr.sub_person_id selected_mgr_person_id,
50 	      mgr.sub_level selected_mgr_level,
51 	      mgr.sup_person_id mgr_person_id,
52 	      mgr.sup_level mgr_level
53          FROM hri_cs_suph_orgmgr_ct mgr ,
54 	      hri_dbi_cl_per_n_v per
55         WHERE per.id = mgr.sup_person_id
56 	  AND trunc(&AS_OF_DATE) BETWEEN per.effective_start_date AND per.effective_end_date
57 	  AND EXISTS (SELECT NULL
58 	                FROM hri_cs_suph sec
59 	               WHERE sec.sup_person_id = &BIS_SELECTED_TOP_MANAGER
60 		         AND sec.sub_person_id = mgr.sup_person_id
61 			 AND mgr.effective_start_date BETWEEN sec.effective_start_date AND sec.effective_end_date )
62         UNION ALL
63 	SELECT distinct mgr.sub_person_id id,
64 	       per.value value,
65 	       mgr.sup_person_id parent_id,
66 	       TO_CHAR(mgr.sub_level) || per.order_by order_by,
67 	       mgr.effective_start_date,
68 	       mgr.effective_end_date,
69 	       mgr.sup_person_id selected_mgr_person_id,
70 	       mgr.sup_level selected_mgr_level,
71 	       mgr.sub_person_id mgr_person_id,
72 	       mgr.sub_level mgr_level
73 	  FROM hri_cs_suph_orgmgr_ct mgr ,
74 	       hri_dbi_cl_per_n_v per
75 	 WHERE per.id = mgr.sub_person_id
76 	   AND mgr.sub_relative_level = 1
77 	   AND trunc(&AS_OF_DATE) BETWEEN per.effective_start_date AND per.effective_end_date
78 	   AND EXISTS (SELECT NULL
79 	                 FROM hri_cs_suph sec
80 		        WHERE sec.sup_person_id = &BIS_SELECTED_TOP_MANAGER
81 			  AND sec.sub_person_id = mgr.sub_person_id
82 			  AND mgr.effective_start_date BETWEEN sec.effective_start_date AND sec.effective_end_date ))
83          WHERE SELECTED_MGR_PERSON_ID = &HRI_PERSON+HRI_PER_USRDR_H
84 	   AND &AS_OF_DATE BETWEEN EFFECTIVE_START_DATE AND EFFECTIVE_END_DATE
85 	   '
86            || l_search  ;
87 
88 EXCEPTION
89    WHEN OTHERS THEN
90           null ;
91    END;
92 
93 
94 END HRI_OLTP_PMV_PER_ORGMGR_LOV;
95