1 Package Body AHL_UTIL_MC_PKG AS
2 /* $Header: AHLUMCB.pls 120.0.12020000.2 2012/12/10 16:51:24 prakkum ship $ */
3
4 G_TRUNC_DATE CONSTANT DATE := TRUNC(SYSDATE);
5
6 ----------------------------------------------------
7 -- Function to validate existence of lookup code --
8 ----------------------------------------------------
9 Function Validate_Lookup_Code
10 (
11 p_lookup_type IN VARCHAR2,
12 p_lookup_code IN VARCHAR2
13 )
14 RETURN BOOLEAN
15 IS
16
17 CURSOR fnd_lookup_csr
18 (
19 p_lookup_type IN VARCHAR2,
20 p_lookup_code IN VARCHAR2
21 )
22 IS
23 SELECT 'x'
24 FROM FND_LOOKUPS
25 WHERE lookup_type = p_lookup_type AND
26 lookup_code = p_lookup_code AND
27 G_TRUNC_DATE between TRUNC(NVL(start_date_active, SYSDATE)) AND TRUNC(NVL(end_date_active, SYSDATE+1)) AND
28 --JKJain, Bug 12593041 29th July 2011.
29 NVL(enabled_flag, 'Y') = 'Y';
30
31 l_dummy VARCHAR2(1);
32 l_return_val BOOLEAN DEFAULT TRUE;
33
34 BEGIN
35
36 OPEN fnd_lookup_csr (p_lookup_type, p_lookup_code);
37 FETCH fnd_lookup_csr INTO l_dummy;
38 IF (fnd_lookup_csr%NOTFOUND)
39 THEN
40 l_return_val := FALSE;
41 END IF;
42 CLOSE fnd_lookup_csr;
43
44 RETURN l_return_val;
45
46 END Validate_LOOKUP_Code;
47
48 -------------------------------------------------------
49 -- Procedure to return lookup code given the meaning --
50 -------------------------------------------------------
51 PROCEDURE Convert_To_LookupCode
52 (
53 p_lookup_type IN VARCHAR2,
54 p_lookup_meaning IN VARCHAR2,
55 x_lookup_code OUT NOCOPY VARCHAR2,
56 x_return_val OUT NOCOPY BOOLEAN
57 )
58 IS
59
60 CURSOR fnd_lookup_csr
61 (
62 p_lookup_type IN VARCHAR2,
63 p_lookup_meaning IN VARCHAR2
64 )
65 IS
66 SELECT lookup_code
67 FROM fnd_lookups
68 WHERE lookup_type = p_lookup_type AND
69 upper(meaning) = upper(p_lookup_meaning) AND
70 G_TRUNC_DATE between TRUNC(NVL(start_date_active, SYSDATE)) AND TRUNC(NVL(end_date_active, SYSDATE+1));
71
72 l_lookup_code fnd_lookups.lookup_code%TYPE DEFAULT NULL;
73 l_dummy_code fnd_lookups.lookup_code%TYPE DEFAULT NULL;
74 l_return_val BOOLEAN DEFAULT TRUE;
75
76 BEGIN
77
78 OPEN fnd_lookup_csr (p_lookup_type, p_lookup_meaning);
79 FETCH fnd_lookup_csr INTO l_lookup_code;
80 -- Compare upper(lookup_meaning) with upper(user keyed-in text)
81 -- If more than one found, then force user to navigate through LOV
82 -- Else If only one found, then use the same
83 IF (fnd_lookup_csr%NOTFOUND)
84 THEN
85 l_return_val := FALSE;
86 l_lookup_code := NULL;
87 ELSE
88 FETCH fnd_lookup_csr INTO l_dummy_code;
89 IF (fnd_lookup_csr%FOUND)
90 THEN
91 FND_MESSAGE.Set_Name('AHL', 'AHL_COM_TOO_MANY_LOOKUP');
92 FND_MESSAGE.Set_Token('FIELD', p_lookup_meaning);
93 FND_MSG_PUB.ADD;
94 CLOSE fnd_lookup_csr;
95 RAISE FND_API.G_EXC_ERROR;
96 END IF;
97 END IF;
98 CLOSE fnd_lookup_csr;
99
100 x_lookup_code := l_lookup_code;
101 x_return_val := l_return_val;
102
103 END Convert_To_LookupCode;
104
105 --------------------------------------------------------
106 -- Procedure to return lookup meaning given the code --
107 --------------------------------------------------------
108 PROCEDURE Convert_To_LookupMeaning
109 (
110 p_lookup_type IN VARCHAR2,
111 p_lookup_code IN VARCHAR2,
112 x_lookup_meaning OUT NOCOPY VARCHAR2,
113 x_return_val OUT NOCOPY BOOLEAN
114 )
115 IS
116
117 CURSOR fnd_lookup_csr
118 (
119 p_lookup_type IN VARCHAR2,
120 p_lookup_code IN VARCHAR2
121 )
122 IS
123 SELECT meaning
124 FROM fnd_lookups
125 WHERE lookup_type = p_lookup_type AND
126 lookup_code = p_lookup_code AND
127 G_TRUNC_DATE between TRUNC(NVL(start_date_active, SYSDATE)) AND TRUNC(NVL(end_date_active, SYSDATE+1));
128
129 l_lookup_meaning fnd_lookups.meaning%TYPE DEFAULT NULL;
130 l_return_val BOOLEAN DEFAULT TRUE;
131
132 BEGIN
133
134 OPEN fnd_lookup_csr(p_lookup_type, p_lookup_code);
135 FETCH fnd_lookup_csr INTO l_lookup_meaning;
136 IF (fnd_lookup_csr%NOTFOUND)
137 THEN
138 l_return_val := FALSE;
139 l_lookup_meaning := NULL;
140 END IF;
141 CLOSE fnd_lookup_csr;
142 x_lookup_meaning := l_lookup_meaning;
143 x_return_val := l_return_val;
144
145 END Convert_To_LookupMeaning;
146
147 --------------------------------------
148 -- Procedure to validate Item group --
149 --------------------------------------
150 PROCEDURE Validate_Item_Group
151 (
152 p_item_group_id IN NUMBER,
153 x_return_val OUT NOCOPY BOOLEAN
154 )
155 IS
156
157 CURSOR Item_group_csr
158 (
159 p_item_group_id IN VARCHAR2
160 )
161 IS
162 SELECT 'x'
163 FROM ahl_item_groups_b
164 WHERE item_group_id = p_item_group_id;
165
166 l_dummy VARCHAR2(1);
167 l_return_val BOOLEAN DEFAULT TRUE;
168
169 BEGIN
170
171 OPEN Item_group_csr(p_item_group_id);
172 FETCH Item_group_csr INTO l_dummy;
173 IF (Item_group_csr%NOTFOUND)
174 THEN
175 l_return_val := FALSE;
176 FND_MESSAGE.Set_Name('AHL','AHL_MC_ITEMGRP_INVALID');
177 FND_MESSAGE.Set_Token('ITEM_GRP',p_item_group_id);
178 FND_MSG_PUB.ADD;
179 END IF;
180 CLOSE Item_group_csr;
181 x_return_val := l_return_val;
182
183 END Validate_Item_Group;
184
185 ----------------------------------------------------
186 -- Procedure to check existence of a relationship --
187 -- and if found, returns the position_ref_code --
188 ----------------------------------------------------
189 Procedure Validate_Relationship
190 (
191 p_relationship_id IN NUMBER,
192 x_position_ref_code OUT NOCOPY VARCHAR2,
193 x_return_val OUT NOCOPY BOOLEAN
194 )
195 IS
196
197 CURSOR l_ahl_relationship_csr
198 (
199 p_relationship_id IN NUMBER
200 )
201 IS
202 SELECT position_ref_code
203 FROM ahl_mc_relationships
204 WHERE relationship_id = p_relationship_id AND
205 -- Since positions with active_start_date > sysdate are also displayed in the MC tree, no need to check for active_start_date
206 -- G_TRUNC_DATE between TRUNC(NVL(active_start_date, SYSDATE)) AND TRUNC(NVL(active_end_date, SYSDATE+1));
207 G_TRUNC_DATE <= TRUNC(NVL(active_end_date, SYSDATE+1));
208
209 l_position_ref_code ahl_mc_relationships.position_ref_code%TYPE DEFAULT NULL;
210 l_return_val BOOLEAN DEFAULT TRUE;
211
212 BEGIN
213
214 OPEN l_ahl_relationship_csr(p_relationship_id);
215 FETCH l_ahl_relationship_csr INTO l_position_ref_code;
216 IF (l_ahl_relationship_csr%NOTFOUND)
217 THEN
218 l_return_val := FALSE;
219 x_position_ref_code := NULL;
220 ELSE
221 x_position_ref_code := l_position_ref_code;
222 END IF;
223 CLOSE l_ahl_relationship_csr;
224 x_return_val := l_return_val;
225
226 END Validate_Relationship;
227
228 -----------------------------------------------------
229 -- Procedure to validate existence of relationship --
230 -----------------------------------------------------
231 Procedure Validate_Relationship
232 (
233 p_relationship_id IN NUMBER,
234 x_return_val OUT NOCOPY BOOLEAN
235 )
236 IS
237
238 CURSOR l_ahl_relationship_csr
239 (
240 p_relationship_id IN NUMBER
241 )
242 IS
243 SELECT 'x'
244 FROM ahl_mc_relationships
245 WHERE relationship_id = p_relationship_id AND
246 -- Since positions with active_start_date > sysdate are also displayed in the MC tree, no need to check for active_start_date
247 -- G_TRUNC_DATE between TRUNC(NVL(active_end_date, SYSDATE)) AND TRUNC(NVL(active_end_date, SYSDATE+1));
248 G_TRUNC_DATE <= TRUNC(NVL(active_end_date, SYSDATE+1));
249
250 l_dummy VARCHAR2(1);
251 l_return_val BOOLEAN DEFAULT TRUE;
252
253 BEGIN
254
255 OPEN l_ahl_relationship_csr(p_relationship_id);
256 FETCH l_ahl_relationship_csr INTO l_dummy;
257 IF (l_ahl_relationship_csr%NOTFOUND)
258 THEN
259 l_return_val := FALSE;
260 END IF;
261 CLOSE l_ahl_relationship_csr;
262 x_return_val := l_return_val;
263
264 END Validate_Relationship;
265
266 END AHL_UTIL_MC_PKG;
[Home] [Help]