[Home] [Help]
PACKAGE BODY: APPS.EDW_HR_PERSON_PKG
Source
1 PACKAGE BODY EDW_HR_PERSON_PKG AS
2 /*$Header: hriekpsn.pkb 120.1 2005/06/07 05:40:55 anmajumd noship $*/
3 FUNCTION Regular_Employee_FK (
4 p_person_id in NUMBER,
5 p_instance_code in VARCHAR2 :=NULL)
6 RETURN VARCHAR2 IS
7 --
8 l_emp VARCHAR2(240) := 'NA_EDW';
9 l_instance VARCHAR2(30) := NULL;
10 --
11 BEGIN
12 --
13 IF(p_person_id is NULL) THEN
14 --
15 RETURN 'NA_EDW';
16 --
17 END IF;
18 --
19 IF (p_instance_code is NOT NULL) then
20 --
21 l_instance := p_instance_code;
22 --
23 ELSE
24 --
25 select instance_code into l_instance
26 from edw_local_instance;
27 --
28 END IF;
29 --
30 l_emp := p_person_id || '-' || l_instance
31 || '-' || 'EMPLOYEE'
32 || '-' || 'PERS';
33 --
34 RETURN l_emp;
35 --
36 EXCEPTION
37 --
38 WHEN OTHERS THEN
39 RETURN 'NA_EDW';
40 --
41 END Regular_Employee_FK;
42 ---------------------------------------------------
43 FUNCTION Planner_FK (
44 p_organization_id in NUMBER,
45 p_planner_code in VARCHAR2,
46 p_instance_code in VARCHAR2 :=NULL)
47 RETURN VARCHAR2 IS
48 --
49 l_emp VARCHAR2(240) := 'NA_EDW';
50 l_instance VARCHAR2(30) := NULL;
51 --
52 BEGIN
53 --
54 IF(p_planner_code is NULL OR p_organization_id is NULL) THEN
55 --
56 RETURN 'NA_EDW';
57 --
58 END IF;
59 --
60 IF (p_instance_code is NOT NULL) then
61 --
62 l_instance := p_instance_code;
63 --
64 ELSE
65 --
66 select instance_code into l_instance
67 from edw_local_instance;
68 --
69 END IF;
70 --
71 l_emp := p_planner_code || '-' || p_organization_id
72 || '-' || l_instance
73 || '-' || 'PLANNER'
74 || '-' || 'PERS';
75 --
76 RETURN l_emp;
77 --
78 EXCEPTION
79 --
80 WHEN OTHERS THEN
81 RETURN 'NA_EDW';
82 --
83 END Planner_FK;
84 ---------------------------------------------------
85 FUNCTION Sales_Rep_FK (
86 p_salesrep_id in NUMBER,
87 p_organization_id in NUMBER,
88 p_instance_code in VARCHAR2 :=NULL)
89 RETURN VARCHAR2 IS
90 --
91 l_emp VARCHAR2(240) := 'NA_EDW';
92 l_instance VARCHAR2(30) := NULL;
93 --
94 BEGIN
95 --
96 IF(p_salesrep_id is NULL) THEN
97 --
98 RETURN 'NA_EDW';
99 --
100 END IF;
101 --
102 IF (p_instance_code is NOT NULL) THEN
103 --
104 l_instance := p_instance_code;
105 --
106 ELSE
107 --
108 select instance_code into l_instance
109 from edw_local_instance;
110 --
111 END IF;
112 --
113 l_emp := p_salesrep_id || '-' || p_organization_id
114 || '-' || l_instance
115 || '-' || 'SALESREP'
116 || '-' || 'PERS';
117 --
118 RETURN l_emp;
119 --
120 EXCEPTION
121 --
122 WHEN OTHERS THEN
123 RETURN 'NA_EDW';
124 --
125 END Sales_Rep_FK;
126 ---------------------------------------------------------------
127 FUNCTION Buyer_Flag (p_person_id in NUMBER)
128 RETURN VARCHAR2 IS
129 --
130 l_tmp NUMBER := NULL;
131 --
132 BEGIN
133 --
134 SELECT agent_id into l_tmp
135 FROM po_agents
136 WHERE agent_id = p_person_id
137 AND rownum < 2;
138 --
139 IF l_tmp IS NULL THEN
140 --
141 RETURN 'N';
142 --
143 ELSE
144 --
145 RETURN 'Y';
146 --
147 END IF;
148 --
149 EXCEPTION
150 WHEN OTHERS THEN
151 --
152 RETURN 'N';
153 --
154 END Buyer_Flag;
155 ---------------------------------------------------------------
156 FUNCTION Planner_Flag (p_person_id in NUMBER)
157 RETURN VARCHAR2 IS
158 --
159 l_tmp NUMBER := NULL;
160 --
161 Begin
162 --
163 SELECT employee_id into l_tmp
164 FROM mtl_planners
165 WHERE employee_id = p_person_id
166 AND rownum < 2;
167 --
168 IF l_tmp IS NULL THEN
169 --
170 RETURN 'N';
171 --
172 ELSE
173 --
174 RETURN 'Y';
175 --
176 END IF;
177 --
178 EXCEPTION
179 --
180 WHEN OTHERS THEN
181 RETURN 'N';
182 --
183 END Planner_Flag;
184 ---------------------------------------------------------------
185 FUNCTION Sales_Rep_Flag (p_person_id in NUMBER)
186 RETURN VARCHAR2 IS
187 --
188 l_tmp NUMBER := NULL;
189 --
190 BEGIN
191 --
192 SELECT person_id into l_tmp
193 FROM ra_salesreps_all
194 WHERE person_id = p_person_id
195 AND rownum < 2;
196 --
197 IF l_tmp IS NULL THEN
198 --
199 RETURN 'N';
200 --
201 ELSE
202 --
203 RETURN 'Y';
204 --
205 END IF;
206 --
207 EXCEPTION
208 --
209 WHEN OTHERS THEN
210 RETURN 'N';
211 --
212 END Sales_Rep_Flag;
213 --
214 END EDW_HR_PERSON_PKG;