[Home] [Help]
PACKAGE BODY: APPS.EGO_VALIDATE_CATALOG_GROUP
Source
1 PACKAGE BODY EGO_Validate_Catalog_Group AS
2 /* $Header: EGOLCAGB.pls 120.1.12020000.2 2012/07/13 01:30:48 mshirkol 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 MICG.item_catalog_group_id
306 INTO l_ccid
307 FROM mtl_item_catalog_groups MICG
308 WHERE upper(decode(MICG.item_catalog_group_id, NULL, NULL,
309 FND_FLEX_SERVER.GET_KFV_CONCAT_SEGS_BY_CCID('COMPACT',401,'MICG',
310 101,MICG.item_catalog_group_id,NULL) )) = upper(EGO_Globals.G_Catalog_Group_Rec.catalog_group_name);
311
312 EXCEPTION WHEN NO_DATA_FOUND THEN
313 l_ccid := NULL;
314
315 WHEN OTHERS THEN
316 l_ccid := NULL;
317 Error_Handler.Add_Error_Token
318 ( p_message_text => 'Error in Check Entity : Catalog group name is '||EGO_Globals.G_Catalog_Group_Rec.Catalog_group_name||'/'||SQLERRM
319 , x_mesg_token_tbl => l_mesg_token_tbl
320 );
321 l_return_status := FND_API.G_RET_STS_ERROR;
322
323 END;
324
325 IF l_ccid IS NOT NULL
326 THEN
327 IF ( EGO_Globals.G_Catalog_Group_Rec.Transaction_Type = EGO_Globals.G_OPR_CREATE )
328 OR
329 ( EGO_Globals.G_Catalog_Group_Rec.Transaction_Type = EGO_Globals.G_OPR_UPDATE AND
330 EGO_Globals.G_Catalog_Group_Rec.Catalog_group_id <> l_ccid )
331 THEN
332 -- dbms_output.put_line('Duplicate error in : '||EGO_Globals.G_Catalog_Group_Rec.Transaction_Type);
333 Error_Handler.Add_Error_Token
334 ( p_message_name => 'EGO_CATALOG_ALREADY_EXISTS'
335 , p_application_id => 'EGO'
336 , x_mesg_token_tbl => l_mesg_token_tbl
337 );
338 l_return_status := FND_API.G_RET_STS_ERROR;
339 END IF;
340 END IF;
341
342 x_return_status := l_return_status;
343 x_mesg_token_tbl := l_mesg_token_tbl;
344
345 END Check_Entity;
346
347
348 PROCEDURE Check_Entity_Delete
349 ( x_return_status OUT NOCOPY VARCHAR2
350 , x_Mesg_Token_Tbl OUT NOCOPY Error_Handler.Mesg_Token_Tbl_Type
351 )
352 IS
353 l_mesg_token_tbl Error_Handler.Mesg_Token_Tbl_Type;
354 l_dummy VARCHAR2(1);
355 BEGIN
356
357 x_return_status := FND_API.G_RET_STS_SUCCESS;
358
359 /* Check if there are any items exist for this catalog group */
360
361 BEGIN
362
363 -- dbms_output.put_line('Checking for items during delete');
364
365 SELECT 'x' INTO l_dummy FROM dual WHERE EXISTS (SELECT null FROM mtl_system_items_b
366 WHERE item_catalog_group_id = EGO_Globals.G_Catalog_Group_Rec.Catalog_group_id);
367
368 Error_Handler.Add_Error_Token
369 ( p_message_name => 'EGO_CATALOG_HAS_ITEMS'
370 , p_application_id => 'EGO'
371 , x_mesg_token_tbl => x_mesg_token_tbl
372 );
373 x_return_status := FND_API.G_RET_STS_ERROR;
374
375 Return;
376
377 EXCEPTION WHEN NO_DATA_FOUND
378 THEN
379 Null;
380
381 WHEN OTHERS
382 THEN
383 Error_Handler.Add_Error_Token
384 ( p_message_text => 'Error in Check Entity Delete : Catalog group name is '||EGO_Globals.G_Catalog_Group_Rec.Catalog_group_name||'/'||SQLERRM
385 , x_mesg_token_tbl => x_mesg_token_tbl
386 );
387 x_return_status := FND_API.G_RET_STS_ERROR;
388
389 Return;
390 END;
391
392 /* Check if this catalog group is bing used as a parent of another catalog group */
393
394 BEGIN
395 -- dbms_output.put_line('Checking for parent catalog group during delete');
396
397 SELECT 'x' INTO l_dummy FROM dual WHERE EXISTS (SELECT null FROM mtl_item_catalog_groups_b
398 WHERE parent_catalog_group_id = EGO_Globals.G_Catalog_Group_Rec.Catalog_group_id);
399
400 -- dbms_output.put_line('Found parent catalog group during delete');
401
402 Error_Handler.Add_Error_Token
403 ( p_message_name => 'EGO_CATALOG_IS_PARENT'
404 , p_application_id => 'EGO'
405 , x_mesg_token_tbl => x_mesg_token_tbl
406 );
407 x_return_status := FND_API.G_RET_STS_ERROR;
408
409 Return;
410
411 EXCEPTION WHEN NO_DATA_FOUND
412 THEN
413 Null;
414
415 WHEN OTHERS
416 THEN
417 Error_Handler.Add_Error_Token
418 ( p_message_text => 'Error in Check Entity Delete : Catalog group name is '||EGO_Globals.G_Catalog_Group_Rec.Catalog_group_name||'/'||SQLERRM
419 , x_mesg_token_tbl => x_mesg_token_tbl
420 );
421 x_return_status := FND_API.G_RET_STS_ERROR;
422
423 Return;
424 END;
425
426 END Check_Entity_Delete;
427
428
429 END EGO_Validate_Catalog_Group;