1 PACKAGE BODY hri_edw_dim_person AS
2 /* $Header: hriedpsn.pkb 120.0 2005/05/29 07:09:03 appldev noship $ */
3
4 /******************************************************************************/
5 /* get_nearest_parent returns the 15th level supervisor over the person who */
6 /* is below the 16th level */
7 /******************************************************************************/
8 FUNCTION get_nearest_parent(p_person_id IN NUMBER)
9 RETURN NUMBER IS
10
11 l_parent_id NUMBER;
12
13 CURSOR parent_csr IS
14 SELECT supv_person_id
15 FROM hri_supv_hrchy_summary
16 WHERE sub_person_id = p_person_id
17 AND supv_level = 14;
18
19 BEGIN
20
21 OPEN parent_csr;
22 FETCH parent_csr INTO l_parent_id;
23 CLOSE parent_csr;
24
25 RETURN l_parent_id;
26
27 END get_nearest_parent;
28
29 /******************************************************************************/
30 /* is_a_buyer tests whether the given person also exists as a buyer */
31 /******************************************************************************/
32 FUNCTION is_a_buyer (p_person_id IN NUMBER)
33 RETURN VARCHAR2 IS
34
35 l_temp NUMBER := NULL; -- variable to hold buyer id if one exists
36
37 BEGIN
38
39 SELECT agent_id INTO l_temp
40 FROM po_agents
41 WHERE agent_id = p_person_id
42 AND rownum < 2;
43
44 IF l_temp IS NULL THEN
45 RETURN 'N';
46 ELSE
47 RETURN 'Y';
48 END IF;
49
50 EXCEPTION WHEN OTHERS THEN
51 RETURN 'N';
52
53 END is_a_buyer;
54
55
56 /******************************************************************************/
57 /* is_a_planner tests whether the given person also exists as a planner */
58 /******************************************************************************/
59 FUNCTION is_a_planner (p_person_id IN NUMBER)
60 RETURN VARCHAR2 IS
61
62 l_temp NUMBER := NULL; -- variable to hold planner id if one exists
63
64 BEGIN
65
66 SELECT employee_id INTO l_temp
67 FROM mtl_planners
68 WHERE employee_id = p_person_id
69 AND rownum < 2;
70
71 IF l_temp IS NULL THEN
72 RETURN 'N';
73 ELSE
74 RETURN 'Y';
75 END IF;
76
77 EXCEPTION WHEN OTHERS THEN
78 RETURN 'N';
79
80 END is_a_planner;
81
82
83 /******************************************************************************/
84 /* is_a_sales_rep tests whether the given person also exists as a sales rep */
85 /******************************************************************************/
86 FUNCTION is_a_sales_rep (p_person_id IN NUMBER)
87 RETURN VARCHAR2 IS
88
89 l_temp NUMBER := NULL; -- variable to hold sales rep id if one exists
90
91 BEGIN
92
93 SELECT person_id INTO l_temp
94 FROM ra_salesreps_all
95 WHERE person_id = p_person_id
96 AND rownum < 2;
97
98 IF l_temp IS NULL THEN
99 RETURN 'N';
100 ELSE
101 RETURN 'Y';
102 END IF;
103
104 EXCEPTION WHEN OTHERS THEN
105 RETURN 'N';
106
107 END is_a_sales_rep;
108
109 END hri_edw_dim_person;
[Home] [Help]