[Home] [Help]
PACKAGE BODY: APPS.EGO_VALIDATE_CATALOG_GROUP
Source
1 PACKAGE BODY EGO_Validate_Catalog_Group AS
2 /* $Header: EGOLCAGB.pls 120.1.12010000.1 2008/07/24 12:20:21 appldev ship $ */
3 /****************************************************************************
4 --
5 -- Copyright (c) 1996 Oracle Corporation, Redwood Shores, CA, USA
6 -- All rights reserved.
7 --
8 -- FILENAME
9 --
10 -- EGOLCAGB.pls
11 --
12 -- DESCRIPTION
13 --
14 -- Body of package EGO_Validate_Catalog_Group
15 --
16 -- NOTES
17 --
18 -- HISTORY
19 --
20 -- 20-SEP-2002 Rahul Chitko Initial Creation
21 -- 10-10-2002 Refai Farook Added Check_Entity procedure
22 -- 19-FEB-2003 Refai Farook Inactive Date validations (Check_Attributes)
23 ****************************************************************************/
24 G_Pkg_Name VARCHAR2(30) := 'EGO_Validate_Catalog_Group';
25 g_token_tbl Error_Handler.Token_Tbl_Type;
26
27
28 /*******************************************************************
29 * Procedure : Check_Existence
30 * Returns : None
31 * Purpose :
32 *********************************************************************/
33 PROCEDURE Check_Existence
34 ( x_Mesg_Token_Tbl OUT NOCOPY Error_Handler.Mesg_Token_Tbl_Type
35 , x_return_status OUT NOCOPY VARCHAR2
36 )
37 IS
38 l_token_tbl Error_Handler.Token_Tbl_Type;
39 l_Mesg_Token_Tbl Error_Handler.Mesg_Token_Tbl_Type;
40 l_return_status VARCHAR2(1);
41 BEGIN
42 EGO_Catalog_Group_Util.Query_Row
43 (
44 x_mesg_token_tbl => l_mesg_token_tbl
45 , x_return_status => l_return_status
46 );
47 Error_Handler.Write_Debug('Query Row Returned with : ' || l_return_status);
48
49 IF l_return_status = EGO_Globals.G_RECORD_FOUND AND
50 EGO_Globals.G_Catalog_Group_Rec.transaction_type = EGO_Globals.G_OPR_CREATE
51 THEN
52 l_token_tbl(1).token_name := 'CATALOG_GROUP_NAME';
53 l_token_tbl(1).token_value :=
54 EGO_Globals.G_Catalog_Group_Rec.Catalog_Group_Name;
55 Error_Handler.Add_Error_Token
56 ( x_Mesg_token_tbl => l_Mesg_Token_Tbl
57 , p_application_id => 'EGO'
58 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
59 , p_message_name => 'EGO_CATGRP_ALREADY_EXISTS'
60 , p_token_tbl => l_token_tbl
61 );
62 l_return_status := FND_API.G_RET_STS_ERROR;
63 ELSIF l_return_status = EGO_Globals.G_RECORD_NOT_FOUND AND
64 EGO_Globals.G_Catalog_Group_Rec.transaction_type IN
65 (EGO_Globals.G_OPR_UPDATE, EGO_Globals.G_OPR_DELETE)
66 THEN
67 l_token_tbl(1).token_name := 'CATALOG_GROUP_NAME';
68 l_token_tbl(1).token_value :=
69 EGO_Globals.G_Catalog_Group_Rec.Catalog_Group_Name;
70 Error_Handler.Add_Error_Token
71 ( x_Mesg_token_tbl => l_Mesg_Token_Tbl
72 , p_application_id => 'EGO'
73 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
74 , p_message_name => 'EGO_CATGRP_DOESNOT_EXIST'
75 , p_token_tbl => l_token_tbl
76 );
77 l_return_status := FND_API.G_RET_STS_ERROR;
78 ELSIF l_Return_status = FND_API.G_RET_STS_UNEXP_ERROR
79 THEN
80 Error_Handler.Add_Error_Token
81 ( x_Mesg_token_tbl => l_Mesg_Token_Tbl
82 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
83 , p_message_name => NULL
84 , p_application_id => 'EGO'
85 , p_message_text =>
86 'Unexpected error while existence verification of ' ||
87 'Catalog Group '|| EGO_Globals.G_Catalog_Group_Rec.Catalog_Group_Name
88 , p_token_tbl => l_token_tbl
89 );
90 l_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
91 ELSE
92
93 /* Assign the relevant transaction type for SYNC operations */
94
95 IF EGO_Globals.G_Catalog_Group_Rec.transaction_type = 'SYNC' THEN
96 IF l_return_status = EGO_Globals.G_RECORD_FOUND THEN
97 EGO_Globals.G_Old_Catalog_Group_Rec.transaction_type :=
98 EGO_Globals.G_OPR_UPDATE;
99 ELSE
100 EGO_Globals.G_Old_Catalog_Group_Rec.transaction_type :=
101 EGO_Globals.G_OPR_CREATE;
102 END IF;
103 END IF;
104 l_return_status := FND_API.G_RET_STS_SUCCESS;
105
106 END IF;
107
108 x_return_status := l_return_status;
109 x_mesg_token_tbl := l_mesg_token_tbl;
110
111 END Check_Existence;
112
113
114 /*******************************************************************
115 * Procedure : Check_Access
116 * Returns : None
117 * Parameters OUT: Return Status
118 * Message Token Table
119 * Purpose : Checks if the user has the necessary privilege
120 *********************************************************************/
121 PROCEDURE Check_Access
122 ( x_mesg_token_tbl OUT NOCOPY Error_Handler.Mesg_Token_Tbl_Type
123 , x_return_status OUT NOCOPY VARCHAR2
124 )
125 IS
126 l_return_status VARCHAR2(1) := FND_API.G_RET_STS_SUCCESS;
127 l_Mesg_Token_Tbl Error_Handler.Mesg_Token_Tbl_Type;
128 l_token_tbl Error_Handler.Token_Tbl_Type;
129
130 BEGIN
131 /* Code to be added for proper data security checks. */
132
133 x_return_status := l_return_status;
134 x_mesg_token_tbl := l_mesg_token_tbl;
135
136 Error_Handler.Write_Debug('Check Access returning with ' || l_return_status );
137
138 END Check_Access;
139
140
141 /********************************************************************
142 * Procedure : Check_Attributes
143 * Parameters IN : Revised Item Exposed Column record
144 * Revised Item Unexposed Column record
145 * Old Revised Item Exposed Column record
146 * Old Revised Item unexposed column record
147 * Parameters OUT: Return Status
148 * Mesg Token Table
149 * Purpose : Check_Attrbibutes procedure will validate every
150 * revised item attrbiute in its entirety.
151 **********************************************************************/
152 PROCEDURE Check_Attributes
153 ( x_return_status OUT NOCOPY VARCHAR2
154 , x_Mesg_Token_Tbl OUT NOCOPY Error_Handler.Mesg_Token_Tbl_Type
155 )
156 IS
157 l_err_text VARCHAR2(2000) := NULL;
158 l_Mesg_Token_Tbl Error_Handler.Mesg_Token_Tbl_Type;
159 l_Token_Tbl Error_Handler.Token_Tbl_Type;
160 l_parent_inactive_date DATE;
161
162 BEGIN
163
164 x_return_status := FND_API.G_RET_STS_SUCCESS;
165
166 IF (EGO_Globals.G_Catalog_Group_Rec.catalog_group_id =
167 EGO_Globals.G_Catalog_Group_Rec.parent_catalog_group_id
168 )
169 THEN
170 x_return_status := FND_API.G_RET_STS_ERROR;
171 Error_Handler.Add_Error_Token
172 ( p_message_name => 'EGO_CATALOG_ID_SAMEAS_PARENT'
173 , p_application_id => 'EGO'
174 , x_mesg_token_tbl => l_mesg_token_tbl
175 );
176 END IF;
177
178 IF EGO_Globals.G_Catalog_Group_Rec.parent_catalog_group_id IS NOT NULL AND
179 EGO_Globals.G_Catalog_Group_Rec.parent_catalog_group_id <> FND_API.G_MISS_NUM
180 THEN
181
182 BEGIN
183 SELECT inactive_date INTO l_parent_inactive_date FROM mtl_item_catalog_groups_b
184 WHERE item_catalog_group_id = EGO_Globals.G_Catalog_Group_Rec.parent_catalog_group_id;
185 EXCEPTION WHEN OTHERS THEN
186 x_return_status := FND_API.G_RET_STS_ERROR;
187 Error_Handler.Add_Error_Token
188 ( p_message_name => 'EGO_CG_PARENT_NOT_FOUND'
189 , p_application_id => 'EGO'
190 , x_mesg_token_tbl => l_mesg_token_tbl
191 );
192 END;
193
194 END IF;
195
196 IF EGO_Globals.G_Catalog_Group_Rec.inactive_date IS NOT NULL AND
197 EGO_Globals.G_Catalog_Group_Rec.inactive_date <> FND_API.G_MISS_DATE
198 THEN
199
200 IF trunc(EGO_Globals.G_Catalog_Group_Rec.inactive_date) <>
201 trunc(nvl(EGO_Globals.G_Old_Catalog_Group_Rec.inactive_date,
202 EGO_Globals.G_Catalog_Group_Rec.inactive_date))
203 THEN
204
205 /* Inactive date should be greater than the current date */
206
207 IF trunc(EGO_Globals.G_Catalog_Group_Rec.inactive_date) < trunc(SYSDATE)
208 THEN
209 x_return_status := FND_API.G_RET_STS_ERROR;
210 Error_Handler.Add_Error_Token
211 ( p_message_name => 'EGO_CG_ENDDATE_LESS_CURRDATE'
212 , p_application_id => 'EGO'
213 , x_mesg_token_tbl => l_mesg_token_tbl
214 );
215 END IF;
216
217 /* Incative date cannnot be greater than the parent's inactive date */
218
219 IF nvl(l_parent_inactive_date,EGO_Globals.G_Catalog_Group_Rec.inactive_date) <
220 EGO_Globals.G_Catalog_Group_Rec.inactive_date
221 THEN
222 x_return_status := FND_API.G_RET_STS_ERROR;
223 Error_Handler.Add_Error_Token
224 ( p_message_name => 'EGO_CG_ENDDATE_GREAT_PARENTDT'
225 , p_application_id => 'EGO'
226 , x_mesg_token_tbl => l_mesg_token_tbl
227 );
228 END IF;
229
230 END IF;
231
232 ELSIF EGO_Globals.G_Catalog_Group_Rec.inactive_date = FND_API.G_MISS_DATE
233 THEN
234 /* Cannot nullify the end date for a cg when it's parent has one */
235
236 IF l_parent_inactive_date is NOT NULL
237 THEN
238 x_return_status := FND_API.G_RET_STS_ERROR;
239 Error_Handler.Add_Error_Token
240 ( p_message_name => 'EGO_CG_PARENT_HAS_ENDDATE'
241 , p_application_id => 'EGO'
242 , x_mesg_token_tbl => l_mesg_token_tbl
243 );
244 END IF;
245
246 END IF;
247
248 Error_Handler.Write_Debug('Within Catalog Group Check Attributes . . . ');
249
250 x_Mesg_Token_Tbl := l_Mesg_Token_Tbl;
251
252 END Check_Attributes;
253
254 /*********************************************************************
255 * Procedure : Check_Required
256 * Parameters OUT: Mesg Token Table
257 * Return_Status
258 * Purpose :
259 **********************************************************************/
260 PROCEDURE Check_Required
261 ( x_return_status OUT NOCOPY VARCHAR2
262 , x_Mesg_Token_Tbl OUT NOCOPY Error_Handler.Mesg_Token_Tbl_Type
263 )
264 IS
265 l_Mesg_Token_Tbl Error_Handler.Mesg_Token_Tbl_Type;
266 l_Token_Tbl Error_Handler.Token_Tbl_Type;
267 BEGIN
268 x_return_status := FND_API.G_RET_STS_SUCCESS;
269
270
271 x_Mesg_Token_Tbl := l_Mesg_Token_Tbl;
272
273 END Check_Required;
274
275
276 /********************************************************************
277 * Procedure : Check_Entity
278 * Parameters IN :
279 * Parameters OUT: Message Token Table
280 * Return Status
281 * Purpose : Checks for duplicate segment values
282 *********************************************************************/
283 PROCEDURE Check_Entity
284 ( x_mesg_token_tbl OUT NOCOPY Error_Handler.Mesg_Token_Tbl_Type
285 , x_return_status OUT NOCOPY VARCHAR2
286 )
287 IS
288 l_return_status VARCHAR2(3) := FND_API.G_RET_STS_SUCCESS;
289 l_mesg_token_tbl Error_Handler.Mesg_Token_Tbl_Type;
290 l_Token_Tbl Error_Handler.Token_Tbl_Type;
291 l_ccid NUMBER;
292 BEGIN
293
294 /*
295 l_ccid := EGO_ItemCat_Val_To_Id.Get_Catalog_Group_Id
296 (p_catalog_group_name =>EGO_Globals.G_Catalog_Group_Rec.catalog_group_name
297 , p_operation => 'FIND_COMBINATION');
298 */
299
300 -- Work around to check the duplicate segment values since the FLEX API fails to
301 -- perform the check. Please see bug 2682719
302
303 BEGIN
304
305 SELECT item_catalog_group_id INTO l_ccid FROM mtl_item_catalog_groups_kfv WHERE
306 upper(concatenated_segments) = upper(EGO_Globals.G_Catalog_Group_Rec.catalog_group_name);
307
308 EXCEPTION WHEN NO_DATA_FOUND THEN
309 l_ccid := NULL;
310
311 WHEN OTHERS THEN
312 l_ccid := NULL;
313 Error_Handler.Add_Error_Token
314 ( p_message_text => 'Error in Check Entity : Catalog group name is '||EGO_Globals.G_Catalog_Group_Rec.Catalog_group_name||'/'||SQLERRM
315 , x_mesg_token_tbl => l_mesg_token_tbl
316 );
317 l_return_status := FND_API.G_RET_STS_ERROR;
318
319 END;
320
321 IF l_ccid IS NOT NULL
322 THEN
323 IF ( EGO_Globals.G_Catalog_Group_Rec.Transaction_Type = EGO_Globals.G_OPR_CREATE )
324 OR
325 ( EGO_Globals.G_Catalog_Group_Rec.Transaction_Type = EGO_Globals.G_OPR_UPDATE AND
326 EGO_Globals.G_Catalog_Group_Rec.Catalog_group_id <> l_ccid )
327 THEN
328 -- dbms_output.put_line('Duplicate error in : '||EGO_Globals.G_Catalog_Group_Rec.Transaction_Type);
329 Error_Handler.Add_Error_Token
330 ( p_message_name => 'EGO_CATALOG_ALREADY_EXISTS'
331 , p_application_id => 'EGO'
332 , x_mesg_token_tbl => l_mesg_token_tbl
333 );
334 l_return_status := FND_API.G_RET_STS_ERROR;
335 END IF;
336 END IF;
337
338 x_return_status := l_return_status;
339 x_mesg_token_tbl := l_mesg_token_tbl;
340
341 END Check_Entity;
342
343
344 PROCEDURE Check_Entity_Delete
345 ( x_return_status OUT NOCOPY VARCHAR2
346 , x_Mesg_Token_Tbl OUT NOCOPY Error_Handler.Mesg_Token_Tbl_Type
347 )
348 IS
349 l_mesg_token_tbl Error_Handler.Mesg_Token_Tbl_Type;
350 l_dummy VARCHAR2(1);
351 BEGIN
352
353 x_return_status := FND_API.G_RET_STS_SUCCESS;
354
355 /* Check if there are any items exist for this catalog group */
356
357 BEGIN
358
359 -- dbms_output.put_line('Checking for items during delete');
360
361 SELECT 'x' INTO l_dummy FROM dual WHERE EXISTS (SELECT null FROM mtl_system_items_b
362 WHERE item_catalog_group_id = EGO_Globals.G_Catalog_Group_Rec.Catalog_group_id);
363
364 Error_Handler.Add_Error_Token
365 ( p_message_name => 'EGO_CATALOG_HAS_ITEMS'
366 , p_application_id => 'EGO'
367 , x_mesg_token_tbl => x_mesg_token_tbl
368 );
369 x_return_status := FND_API.G_RET_STS_ERROR;
370
371 Return;
372
373 EXCEPTION WHEN NO_DATA_FOUND
374 THEN
375 Null;
376
377 WHEN OTHERS
378 THEN
379 Error_Handler.Add_Error_Token
380 ( p_message_text => 'Error in Check Entity Delete : Catalog group name is '||EGO_Globals.G_Catalog_Group_Rec.Catalog_group_name||'/'||SQLERRM
381 , x_mesg_token_tbl => x_mesg_token_tbl
382 );
383 x_return_status := FND_API.G_RET_STS_ERROR;
384
385 Return;
386 END;
387
388 /* Check if this catalog group is bing used as a parent of another catalog group */
389
390 BEGIN
391 -- dbms_output.put_line('Checking for parent catalog group during delete');
392
393 SELECT 'x' INTO l_dummy FROM dual WHERE EXISTS (SELECT null FROM mtl_item_catalog_groups_b
394 WHERE parent_catalog_group_id = EGO_Globals.G_Catalog_Group_Rec.Catalog_group_id);
395
396 -- dbms_output.put_line('Found parent catalog group during delete');
397
398 Error_Handler.Add_Error_Token
399 ( p_message_name => 'EGO_CATALOG_IS_PARENT'
400 , p_application_id => 'EGO'
401 , x_mesg_token_tbl => x_mesg_token_tbl
402 );
403 x_return_status := FND_API.G_RET_STS_ERROR;
404
405 Return;
406
407 EXCEPTION WHEN NO_DATA_FOUND
408 THEN
409 Null;
410
411 WHEN OTHERS
412 THEN
413 Error_Handler.Add_Error_Token
414 ( p_message_text => 'Error in Check Entity Delete : Catalog group name is '||EGO_Globals.G_Catalog_Group_Rec.Catalog_group_name||'/'||SQLERRM
415 , x_mesg_token_tbl => x_mesg_token_tbl
416 );
417 x_return_status := FND_API.G_RET_STS_ERROR;
418
419 Return;
420 END;
421
422 END Check_Entity_Delete;
423
424
425 END EGO_Validate_Catalog_Group;