[Home] [Help]
PACKAGE BODY: APPS.EGO_ITEM_CATALOG_PUB
Source
1 PACKAGE BODY EGO_ITEM_CATALOG_PUB AS
2 /* $Header: EGOBCAGB.pls 120.7.12010000.3 2009/05/04 07:16:58 chechand ship $ */
3
4 g_pkg_name VARCHAR2(30) := 'EGO_ITEM_CATALOG_PUB';
5 g_app_name VARCHAR2(3) := 'EGO';
6 g_current_user_id NUMBER := EGO_SCTX.Get_User_Id();
7 g_current_login_id NUMBER := FND_GLOBAL.Login_Id;
8 g_plsql_err VARCHAR2(17) := 'EGO_PLSQL_ERR';
9 g_pkg_name_token VARCHAR2(8) := 'PKG_NAME';
10 g_api_name_token VARCHAR2(8) := 'API_NAME';
11 g_sql_err_msg_token VARCHAR2(11) := 'SQL_ERR_MSG';
12
13
14
15 /******************************************************************
16 ** Procedure: Set_Debug_Parameters (unexposed)
17 ** Purpose: Will take input as the debug parameters and check if
18 ** a debug session needs to be eastablished. If yes, the it will
19 ** open a debug session file and all developer messages will be
20 ** logged into a debug error file. File name will be the parameter
21 ** debug_file_name_<session_id>
22 ********************************************************************/
23 Procedure Set_Debug_Parameters( p_debug_flag IN VARCHAR2
24 , p_output_dir IN VARCHAR2
25 , p_debug_filename IN VARCHAR2
26 )
27 IS
28 l_Mesg_Token_tbl Error_Handler.Mesg_Token_Tbl_Type;
29 l_token_Tbl Error_Handler.Token_Tbl_Type;
30 l_return_status VARCHAR2(1);
31 l_Debug_Flag VARCHAR2(1) := p_debug_flag;
32 BEGIN
33
34 IF p_debug_flag = 'Y'
35 THEN
36
37 -- dbms_output.put_line('Debug is Yes ' );
38
39 IF trim(p_output_dir) IS NULL OR
40 trim(p_output_dir) = ''
41 THEN
42 -- If debug is Y then out dir must be
43 -- specified
44
45 Error_Handler.Add_Error_Token
46 ( p_Message_text =>
47 'Debug is set to Y so an output directory' ||
48 ' must be specified. Debug will be turned' ||
49 ' off since no directory is specified'
50 , p_Mesg_Token_Tbl => l_mesg_token_tbl
51 , x_Mesg_Token_Tbl => l_mesg_token_tbl
52 , p_Token_Tbl => l_token_tbl
53 );
54
55 Ego_Catalog_Group_Err_Handler.Log_Error
56 ( p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
57 , p_error_status => 'W'
58 , p_error_level => Error_Handler.G_BO_LEVEL
59 );
60 l_debug_flag := 'N';
61
62 -- dbms_output.put_line('Reverting debug to N ' );
63
64 END IF;
65
66 IF trim(p_debug_filename) IS NULL OR
67 trim(p_debug_filename) = ''
68 THEN
69
70 Error_Handler.Add_Error_Token
71 ( p_Message_text =>
72 'Debug is set to Y so an output filename' ||
73 ' must be specified. Debug will be turned' ||
74 ' off since no filename is specified'
75 , p_Mesg_Token_Tbl => l_mesg_token_tbl
76 , x_Mesg_Token_Tbl => l_mesg_token_tbl
77 , p_Token_Tbl => l_token_tbl
78 );
79
80 Ego_Catalog_Group_Err_Handler.Log_Error
81 ( p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
82 , p_error_status => 'W'
83 , p_error_level => Error_Handler.G_BO_LEVEL
84 );
85 l_debug_flag := 'N';
86
87
88 -- dbms_output.put_line('Reverting debug to N ' );
89
90 END IF;
91
92 Error_Handler.Set_Debug(l_debug_flag);
93
94 IF p_debug_flag = 'Y'
95 THEN
96 Error_Handler.Open_Debug_Session
97 ( p_debug_filename => p_debug_filename
98 , p_output_dir => p_output_dir
99 , x_return_status => l_return_status
100 , p_mesg_token_tbl => l_mesg_token_tbl
101 , x_mesg_token_tbl => l_mesg_token_tbl
102 );
103
104 IF l_return_status <> FND_API.G_RET_STS_SUCCESS
105 THEN
106 Error_Handler.Set_Debug('N');
107 END IF;
108 END IF;
109 END IF;
110
111 END Set_Debug_Parameters;
112
113
114 /***************************************************************************
115 ** Procedure : Process_Catalog_Groups
116 ** Purpose : This is the exposed procedure which most applications will
117 ** call to create the catalog group hierarchy.
118 **
119 ** Return : error status
120 ** error message count
121 ****************************************************************************/
122 Procedure Process_Catalog_Groups
123 ( p_bo_identifier IN VARCHAR2 := 'ICG'
124 , p_api_version_number IN NUMBER := 1.0
125 , p_init_msg_list IN BOOLEAN := FALSE
126 , p_catalog_group_tbl IN Ego_Item_Catalog_Pub.Catalog_Group_Tbl_Type
127 , p_user_id IN NUMBER
128 , p_language_code IN VARCHAR2 := 'US'
129 , x_catalog_group_tbl OUT NOCOPY Ego_Item_Catalog_Pub.Catalog_Group_Tbl_Type
130 , x_return_status OUT NOCOPY VARCHAR2
131 , x_msg_count OUT NOCOPY NUMBER
132 , p_debug IN VARCHAR2 := 'N'
133 , p_output_dir IN VARCHAR2 := NULL
134 , p_debug_filename IN VARCHAR2 := 'Ego_Catalog_Grp.log'
135 ) IS
136 l_return_status VARCHAR2(1);
137 l_other_message VARCHAR2(30);
138 l_other_token_tbl Error_Handler.Token_Tbl_Type;
139 l_token_tbl Error_Handler.Token_Tbl_Type;
140 BEGIN
141 --
142 -- store a global reference to the table so that other processes do not need to be
143 -- passed the whole table.
144 --
145 -- dbms_output.put_line('In record count: ' || p_catalog_group_tbl.COUNT);
146
147 EGO_Globals.G_Catalog_Group_Tbl := p_catalog_group_tbl;
148 EGO_Globals.Set_User_Id(p_user_id);
149 EGO_Globals.Set_Language_Code(p_language_code);
150
151 -- Initialize the Flex column holder
152 --
153 -- Update the flex field global reference
154 --
155
156 FOR i in 1..20
157 LOOP
158 EGO_Item_Catalog_Pub.G_KF_Segment_Values(i) := null;
159 END LOOP;
160
161 EGO_Globals.G_Catalog_Group_Rec := null;
162
163 --
164 -- Set Business Object Idenfier in the System Information
165 -- record.
166 --
167 Error_Handler.Set_Bo_Identifier
168 (p_bo_identifier => p_bo_identifier);
169
170 --
171 -- Initialize the message list if the user has set the
172 -- Init Message List parameter
173 --
174 IF p_init_msg_list
175 THEN
176 Error_Handler.Initialize;
177 END IF;
178
179 Set_Debug_Parameters( p_debug_flag => p_debug
180 , p_output_dir => p_output_dir
181 , p_debug_filename => p_debug_filename
182 );
183
184 if(Error_Handler.Get_Debug = 'Y')
185 then
186 Error_Handler.write_debug('Within business object public api ...');
187 -- dbms_output.put_line('Within business object public api ...');
188 end if;
189
190 --
191 -- Call the Private API for performing further business
192 -- rules validation
193 --
194 EGO_Item_Catalog_Pvt.Process_Catalog_Groups
195 ( x_return_status => l_return_status
196 , x_msg_count => x_msg_count
197 );
198
199 -- dbms_output.put_line('Returned from private API ...');
200
201 IF l_return_status <> 'S'
202 THEN
203 -- Call Error Handler
204 -- dbms_output.put_line('Calling log error ...');
205 Ego_Catalog_Group_Err_Handler.Log_Error
206 ( p_error_status => l_return_status
207 , p_error_scope => Error_Handler.G_SCOPE_ALL
208 , p_error_level => Error_Handler.G_BO_LEVEL
209 --, p_other_message => 'EGO_ERROR_BUSINESS_OBJECT'
210 --, p_other_status => l_return_status
211 );
212 -- dbms_output.put_line('Called log error ...');
213 END IF;
214
215 x_return_status := l_return_status;
216 x_msg_count := Error_Handler.Get_Message_Count;
217
218 Error_Handler.write_debug('Return status is ...'||x_return_status);
219
220 IF Error_Handler.Get_Debug = 'Y'
221 THEN
222 Error_Handler.Close_Debug_Session;
223 END IF;
224
225 -- return back the table from the global reference.
226
227 x_catalog_group_tbl := EGO_Globals.G_Catalog_Group_Tbl;
228
229 EXCEPTION
230 WHEN EGO_Globals.G_EXC_SEV_QUIT_OBJECT THEN
231
232 -- Call Error Handler
233
234 Ego_Catalog_Group_Err_Handler.Log_Error
235 ( p_error_status => Error_Handler.G_STATUS_ERROR
236 , p_error_scope => Error_Handler.G_SCOPE_ALL
237 , p_error_level => Error_Handler.G_BO_LEVEL
238 , p_other_message => l_other_message
239 , p_other_status => Error_Handler.G_STATUS_ERROR
240 );
241
242 x_return_status := Error_Handler.G_STATUS_ERROR;
243 x_msg_count := Error_Handler.Get_Message_Count;
244 IF Error_Handler.Get_Debug = 'Y'
245 THEN
246 Error_Handler.Close_Debug_Session;
247 END IF;
248
249 WHEN EGO_Globals.G_EXC_UNEXP_SKIP_OBJECT THEN
250
251 -- Call Error Handler
252
253 Ego_Catalog_Group_Err_Handler.Log_Error
254 ( p_error_status => Error_Handler.G_STATUS_UNEXPECTED
255 , p_error_level => Error_Handler.G_BO_LEVEL
256 , p_other_status => Error_Handler.G_STATUS_NOT_PICKED
257 , p_other_message => l_other_message
258 , p_other_token_tbl => l_token_tbl
259 );
260
261 x_return_status := Error_Handler.G_STATUS_UNEXPECTED;
262 x_msg_count := Error_Handler.Get_Message_Count;
263 IF Error_Handler.Get_Debug = 'Y'
264 THEN
265 Error_Handler.Close_Debug_Session;
266 END IF;
267
268
269 END Process_Catalog_Groups;
270
271
272 /* Process_Catalog_Group
273 ** Convenience method that can be called once for every catalog group in the catalog group
274 ** hierarchy
275 */
276 Procedure Process_Catalog_Group
277 ( p_Catalog_Group_Name IN VARCHAR2 := NULL
278 , p_Parent_Catalog_Group_Name IN VARCHAR2 := NULL
279 , p_Catalog_Group_Id IN NUMBER := NULL
280 , p_Parent_Catalog_Group_Id IN NUMBER := NULL
281 , p_Description IN VARCHAR2 := NULL
282 , p_Item_Creation_Allowed_Flag IN VARCHAR2 := NULL
283 , p_Start_Effective_Date IN DATE := NULL
284 , p_Inactive_date IN DATE := NULL
285 , p_Enabled_Flag IN VARCHAR2 := NULL
286 , p_Summary_Flag IN VARCHAR2 := NULL
287 , p_segment1 IN VARCHAR2 := NULL
288 , p_segment2 IN VARCHAR2 := NULL
289 , p_segment3 IN VARCHAR2 := NULL
290 , p_segment4 IN VARCHAR2 := NULL
291 , p_segment5 IN VARCHAR2 := NULL
292 , p_segment6 IN VARCHAR2 := NULL
293 , p_segment7 IN VARCHAR2 := NULL
294 , p_segment8 IN VARCHAR2 := NULL
295 , p_segment9 IN VARCHAR2 := NULL
296 , p_segment10 IN VARCHAR2 := NULL
297 , p_segment11 IN VARCHAR2 := NULL
298 , p_segment12 IN VARCHAR2 := NULL
299 , p_segment13 IN VARCHAR2 := NULL
300 , p_segment14 IN VARCHAR2 := NULL
301 , p_segment15 IN VARCHAR2 := NULL
302 , p_segment16 IN VARCHAR2 := NULL
303 , p_segment17 IN VARCHAR2 := NULL
304 , p_segment18 IN VARCHAR2 := NULL
305 , p_segment19 IN VARCHAR2 := NULL
306 , p_segment20 IN VARCHAR2 := NULL
307 , Attribute_category IN VARCHAR2 := NULL
308 , Attribute1 IN VARCHAR2 := NULL
309 , Attribute2 IN VARCHAR2 := NULL
310 , Attribute3 IN VARCHAR2 := NULL
311 , Attribute4 IN VARCHAR2 := NULL
312 , Attribute5 IN VARCHAR2 := NULL
313 , Attribute6 IN VARCHAR2 := NULL
314 , Attribute7 IN VARCHAR2 := NULL
315 , Attribute8 IN VARCHAR2 := NULL
316 , Attribute9 IN VARCHAR2 := NULL
317 , Attribute10 IN VARCHAR2 := NULL
318 , Attribute11 IN VARCHAR2 := NULL
319 , Attribute12 IN VARCHAR2 := NULL
320 , Attribute13 IN VARCHAR2 := NULL
321 , Attribute14 IN VARCHAR2 := NULL
322 , Attribute15 IN VARCHAR2 := NULL
323 , p_User_id IN NUMBER
324 , p_Language_Code IN VARCHAR2 := 'US'
325 , p_Transaction_Type IN VARCHAR2
326 , x_Return_Status OUT NOCOPY VARCHAR2
327 , x_msg_count OUT NOCOPY NUMBER
328 , p_debug IN VARCHAR2 := 'N'
329 , p_output_dir IN VARCHAR2 := NULL
330 , p_debug_filename IN VARCHAR2 := 'Ego_Catalog_Grp.log'
331 , x_catalog_group_id OUT NOCOPY NUMBER
332 , x_catalog_group_name OUT NOCOPY VARCHAR2
333 ) IS
334 l_catalog_group_tbl EGO_Item_Catalog_Pub.Catalog_Group_Tbl_Type;
335 x_catalog_group_tbl EGO_Item_Catalog_Pub.Catalog_Group_Tbl_Type;
336 BEGIN
337 EGO_Globals.G_Catalog_Group_Rec.catalog_group_name := p_catalog_group_name;
338 EGO_Globals.G_Catalog_Group_Rec.parent_catalog_group_name := p_parent_catalog_group_name;
339 EGO_Globals.G_Catalog_Group_Rec.catalog_group_id := p_catalog_group_id;
340 EGO_Globals.G_Catalog_Group_Rec.parent_catalog_group_id := p_parent_catalog_group_id;
341 EGO_Globals.G_Catalog_Group_Rec.segment1 := p_segment1;
342 EGO_Globals.G_Catalog_Group_Rec.segment2 := p_segment2;
343 EGO_Globals.G_Catalog_Group_Rec.segment3 := p_segment3;
344 EGO_Globals.G_Catalog_Group_Rec.segment4 := p_segment4;
345 EGO_Globals.G_Catalog_Group_Rec.segment5 := p_segment5;
346 EGO_Globals.G_Catalog_Group_Rec.segment6 := p_segment6;
347 EGO_Globals.G_Catalog_Group_Rec.segment7 := p_segment7;
348 EGO_Globals.G_Catalog_Group_Rec.segment8 := p_segment8;
349 EGO_Globals.G_Catalog_Group_Rec.segment9 := p_segment9;
350 EGO_Globals.G_Catalog_Group_Rec.segment10 := p_segment10;
351 EGO_Globals.G_Catalog_Group_Rec.segment11 := p_segment11;
352 EGO_Globals.G_Catalog_Group_Rec.segment12 := p_segment12;
353 EGO_Globals.G_Catalog_Group_Rec.segment13 := p_segment13;
354 EGO_Globals.G_Catalog_Group_Rec.segment14 := p_segment14;
355 EGO_Globals.G_Catalog_Group_Rec.segment15 := p_segment15;
356 EGO_Globals.G_Catalog_Group_Rec.segment16 := p_segment16;
357 EGO_Globals.G_Catalog_Group_Rec.segment17 := p_segment17;
358 EGO_Globals.G_Catalog_Group_Rec.segment18 := p_segment18;
359 EGO_Globals.G_Catalog_Group_Rec.segment19 := p_segment19;
360 EGO_Globals.G_Catalog_Group_Rec.segment20 := p_segment20;
361 EGO_Globals.G_Catalog_Group_Rec.summary_flag := p_summary_flag;
362 EGO_Globals.G_Catalog_Group_Rec.enabled_flag := p_enabled_flag;
363 EGO_Globals.G_Catalog_Group_Rec.inactive_date := p_inactive_date;
364 EGO_Globals.G_Catalog_Group_Rec.item_creation_allowed_flag := p_item_creation_Allowed_flag;
365 EGO_Globals.G_Catalog_Group_Rec.description := p_description;
366 EGO_Globals.G_Catalog_Group_Rec.Transaction_Type := p_transaction_type;
367
368 l_catalog_group_tbl(1) := EGO_Globals.G_Catalog_Group_Rec;
369
370 -- -- dbms_output.put_line('local table count of records before start : ' || l_catalog_group_tbl.count);
371
372 EGO_Item_Catalog_Pub.Process_Catalog_Groups
373 ( p_catalog_group_tbl => l_catalog_group_tbl
374 , p_user_id => p_user_id
375 , p_Language_Code => p_Language_Code
376 , x_catalog_group_tbl => x_catalog_group_tbl
377 , x_return_status => x_return_status
378 , x_msg_count => x_msg_count
379 , p_debug => p_debug
380 , p_debug_filename => p_debug_filename
381 , p_output_dir => p_output_dir
382 );
383
384 x_catalog_group_id := x_catalog_group_tbl(1).catalog_group_id;
385 x_catalog_group_name := x_catalog_group_tbl(1).catalog_group_name;
386
387 END Process_Catalog_Group;
388
389
390 PROCEDURE Create_Catalog_Group
391 ( p_Catalog_Group_Id IN NUMBER := NULL
392 , p_Parent_Catalog_Group_Id IN NUMBER := NULL
393 , p_Description IN VARCHAR2 := NULL
394 , p_Item_Creation_Allowed_Flag IN VARCHAR2 := NULL
395 , p_Start_Effective_Date IN DATE := NULL
396 , p_Inactive_date IN DATE := NULL
397 , p_Enabled_Flag IN VARCHAR2 := NULL
398 , p_Summary_Flag IN VARCHAR2 := NULL
399 , p_segment1 IN VARCHAR2 := NULL
400 , p_segment2 IN VARCHAR2 := NULL
401 , p_segment3 IN VARCHAR2 := NULL
402 , p_segment4 IN VARCHAR2 := NULL
403 , p_segment5 IN VARCHAR2 := NULL
404 , p_segment6 IN VARCHAR2 := NULL
405 , p_segment7 IN VARCHAR2 := NULL
406 , p_segment8 IN VARCHAR2 := NULL
407 , p_segment9 IN VARCHAR2 := NULL
408 , p_segment10 IN VARCHAR2 := NULL
409 , p_segment11 IN VARCHAR2 := NULL
410 , p_segment12 IN VARCHAR2 := NULL
411 , p_segment13 IN VARCHAR2 := NULL
412 , p_segment14 IN VARCHAR2 := NULL
413 , p_segment15 IN VARCHAR2 := NULL
414 , p_segment16 IN VARCHAR2 := NULL
415 , p_segment17 IN VARCHAR2 := NULL
416 , p_segment18 IN VARCHAR2 := NULL
417 , p_segment19 IN VARCHAR2 := NULL
418 , p_segment20 IN VARCHAR2 := NULL
419 , Attribute_category IN VARCHAR2 := NULL
420 , Attribute1 IN VARCHAR2 := NULL
421 , Attribute2 IN VARCHAR2 := NULL
422 , Attribute3 IN VARCHAR2 := NULL
423 , Attribute4 IN VARCHAR2 := NULL
424 , Attribute5 IN VARCHAR2 := NULL
425 , Attribute6 IN VARCHAR2 := NULL
426 , Attribute7 IN VARCHAR2 := NULL
427 , Attribute8 IN VARCHAR2 := NULL
428 , Attribute9 IN VARCHAR2 := NULL
429 , Attribute10 IN VARCHAR2 := NULL
430 , Attribute11 IN VARCHAR2 := NULL
431 , Attribute12 IN VARCHAR2 := NULL
432 , Attribute13 IN VARCHAR2 := NULL
433 , Attribute14 IN VARCHAR2 := NULL
434 , Attribute15 IN VARCHAR2 := NULL
435 , p_Template_Id IN NUMBER
436 , p_User_id IN NUMBER
437 , x_return_status OUT NOCOPY VARCHAR2
438 , x_msg_count OUT NOCOPY NUMBER
439 , x_msg_data OUT NOCOPY VARCHAR2
440 , p_debug IN VARCHAR2 := 'N'
441 , p_output_dir IN VARCHAR2 := NULL
442 , p_debug_filename IN VARCHAR2 := 'Ego_Catalog_Grp.log'
443 , x_catalog_group_id OUT NOCOPY NUMBER
444 , x_catalog_group_name OUT NOCOPY VARCHAR2
445 )
446
447 IS
448 l_entity_index number;
449 l_entity_id varchar2(2000);
450 l_message_type varchar2(2000);
451 l_message_list varchar2(2000);
452
453 --GET PROFILE OPTION VALUE FOR PIM FOR TELCO
454 profile_value varchar2(1) := fnd_profile.value('EGO_ENABLE_P4T');
455 draft_str VARCHAR2(2000);
456
457 -- Checks for any active version of PARENT ICC passed as parameter.
458 cursor icc_with_active_ver (parent_icc_id VARCHAR2)
459 IS
460 SELECT version_seq_id
461 FROM ego_mtl_catalog_grp_vers_b
462 WHERE item_catalog_group_id = parent_icc_id
463 AND version_seq_id > 0
464 AND start_active_date <= SYSDATE;
465
466 BEGIN
467
468 IF profile_value = 'Y' AND p_Parent_Catalog_Group_Id <> null THEN
469 OPEN icc_with_active_ver (p_Parent_Catalog_Group_Id);
470 IF icc_with_active_ver%NOTFOUND THEN
471 CLOSE icc_with_active_ver;
472 -- Returning false since an icc without any active version at the time of release cannot be chosen as a parent.
473 x_return_status := 'F';
474 RETURN;
475 END IF;
476 END IF;
477
478 ---------------------------------------------------
479 -- Insert catalog group
480 EGO_Item_Catalog_Pub.Process_Catalog_Group
481 ( p_Catalog_Group_Id => p_Catalog_Group_Id
482 , p_Parent_Catalog_Group_Id => p_Parent_Catalog_Group_Id
483 , p_Description => p_Description
484 , p_Item_Creation_Allowed_Flag => p_Item_Creation_Allowed_Flag
485 , p_Inactive_Date => p_Inactive_Date
486 , p_segment1 => p_segment1
487 , p_segment2 => p_segment2
488 , p_segment3 => p_segment3
489 , p_segment4 => p_segment4
490 , p_segment5 => p_segment5
491 , p_segment6 => p_segment6
492 , p_segment7 => p_segment7
493 , p_segment8 => p_segment8
494 , p_segment9 => p_segment9
495 , p_segment10 => p_segment10
496 , p_segment11 => p_segment11
497 , p_segment12 => p_segment12
498 , p_segment13 => p_segment13
499 , p_segment14 => p_segment14
500 , p_segment15 => p_segment15
501 , p_segment16 => p_segment16
502 , p_segment17 => p_segment17
503 , p_segment18 => p_segment18
504 , p_segment19 => p_segment19
505 , p_segment20 => p_segment20
506 , p_user_id => p_user_id
507 , p_Language_Code => userenv('LANG')
508 , p_Transaction_Type => 'CREATE'
509 , x_return_status => x_return_status
510 , x_msg_count => x_msg_count
511 , p_debug => p_debug
512 , p_output_dir => p_output_dir
513 , p_debug_filename => p_debug_filename
514 , x_catalog_group_id => x_catalog_group_id
515 , x_catalog_group_name => x_catalog_group_name
516 );
517
518 -- PIM4TELCO: CHECHAND: INSERT A ROW IN VERSIONS TABLE FOR DEFAULT DRAFT VERSION. - START
519 IF x_return_status = FND_API.G_RET_STS_SUCCESS THEN
520 -- dbms_output.put_line ('PIM Telco Profile Value: '||profile_value);
521 if profile_value = 'Y' then
522 SELECT message_text into draft_str
523 FROM fnd_new_messages
524 WHERE
525 application_id = (SELECT application_id
526 FROM fnd_application
527 WHERE application_short_name = 'EGO') AND
528 message_name = 'EGO_ICC_DRAFT_VERSION' AND
529 language_code = USERENV('LANG') ;
530
531 -- dbms_output.put_line ('draft text: '||draft_str);
532
533 insert into EGO_MTL_CATALOG_GRP_VERS_B
534 (item_catalog_group_id,
535 version_seq_id,
536 version_description,
537 start_active_date,
538 end_active_date,
539 created_by,
540 creation_date,
541 last_updated_by,
542 last_update_date,
543 last_update_login)
544 values
545 ( x_catalog_group_id,
546 0,
547 draft_str,
548 null,
549 null,
550 FND_GLOBAL.USER_ID,
551 sysdate,
552 FND_GLOBAL.USER_ID,
553 sysdate,
554 FND_GLOBAL.LOGIN_ID);
555 end if;
556 END IF;
557 -- PIM4TELCO: CHECHAND: - END
558
559
560 -- Create an association between Default Template and Catalog Group
561 IF x_return_status = FND_API.G_RET_STS_SUCCESS AND
562 p_Template_Id is not null THEN
563
564 INSERT INTO ego_cat_grp_templates
565 ( template_id
566 , catalog_group_id
567 , created_by
568 , creation_date
569 , last_updated_by
570 , last_update_date
571 )
572 VALUES
573 ( p_Template_Id
574 , x_catalog_group_id
575 , p_user_id
576 , sysdate
577 , p_user_id
578 , sysdate
579 );
580
581 ELSIF x_return_status <> 'S' THEN
582 Error_Handler.Get_Message
583 ( x_message_text => l_message_list
584 , x_entity_index => l_entity_index
585 , x_entity_id => l_entity_id
586 , x_message_type => l_message_type
587 );
588
589 x_msg_count := ERROR_HANDLER.Get_Message_Count();
590 x_msg_data := 'Executing - EGO_ITEM_CATALOG_PUB.CREATE_CATALOG_GROUP '||l_message_list;
591 END IF;
592
593 EXCEPTION
594 WHEN OTHERS THEN
595 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
596
597 Ego_Catalog_Group_Err_Handler.Log_Error
598 ( p_error_status => x_return_status
599 , p_error_scope => Error_Handler.G_SCOPE_ALL
600 , p_error_level => Error_Handler.G_BO_LEVEL
601 );
602
603 Error_Handler.Get_Message
604 ( x_message_text => l_message_list
605 , x_entity_index => l_entity_index
606 , x_entity_id => l_entity_id
607 , x_message_type => l_message_type
608 );
609
610 x_msg_count := ERROR_HANDLER.Get_Message_Count();
611 x_msg_data := 'Executing - EGO_ITEM_CATALOG_PUB.CREATE_CATALOG_GROUP '||l_message_list;
612
613 END CREATE_CATALOG_GROUP;
614
615 ---------------------------------------------------------
616
617 PROCEDURE Update_Catalog_Group
618 ( p_Catalog_Group_Id IN NUMBER := NULL
619 , p_Parent_Catalog_Group_Id IN NUMBER := NULL
620 , p_Description IN VARCHAR2 := NULL
621 , p_Item_Creation_Allowed_Flag IN VARCHAR2 := NULL
622 , p_Start_Effective_Date IN DATE := NULL
623 , p_Inactive_date IN DATE := NULL
624 , p_Enabled_Flag IN VARCHAR2 := NULL
625 , p_Summary_Flag IN VARCHAR2 := NULL
626 , p_segment1 IN VARCHAR2 := NULL
627 , p_segment2 IN VARCHAR2 := NULL
628 , p_segment3 IN VARCHAR2 := NULL
629 , p_segment4 IN VARCHAR2 := NULL
630 , p_segment5 IN VARCHAR2 := NULL
631 , p_segment6 IN VARCHAR2 := NULL
632 , p_segment7 IN VARCHAR2 := NULL
633 , p_segment8 IN VARCHAR2 := NULL
634 , p_segment9 IN VARCHAR2 := NULL
635 , p_segment10 IN VARCHAR2 := NULL
636 , p_segment11 IN VARCHAR2 := NULL
637 , p_segment12 IN VARCHAR2 := NULL
638 , p_segment13 IN VARCHAR2 := NULL
639 , p_segment14 IN VARCHAR2 := NULL
640 , p_segment15 IN VARCHAR2 := NULL
641 , p_segment16 IN VARCHAR2 := NULL
642 , p_segment17 IN VARCHAR2 := NULL
643 , p_segment18 IN VARCHAR2 := NULL
644 , p_segment19 IN VARCHAR2 := NULL
645 , p_segment20 IN VARCHAR2 := NULL
646 , Attribute_category IN VARCHAR2 := NULL
647 , Attribute1 IN VARCHAR2 := NULL
648 , Attribute2 IN VARCHAR2 := NULL
649 , Attribute3 IN VARCHAR2 := NULL
650 , Attribute4 IN VARCHAR2 := NULL
651 , Attribute5 IN VARCHAR2 := NULL
652 , Attribute6 IN VARCHAR2 := NULL
653 , Attribute7 IN VARCHAR2 := NULL
654 , Attribute8 IN VARCHAR2 := NULL
655 , Attribute9 IN VARCHAR2 := NULL
656 , Attribute10 IN VARCHAR2 := NULL
657 , Attribute11 IN VARCHAR2 := NULL
658 , Attribute12 IN VARCHAR2 := NULL
659 , Attribute13 IN VARCHAR2 := NULL
660 , Attribute14 IN VARCHAR2 := NULL
661 , Attribute15 IN VARCHAR2 := NULL
662 , p_Template_Id IN NUMBER
663 , p_User_id IN NUMBER
664 , x_Return_Status OUT NOCOPY VARCHAR2
665 , x_msg_count OUT NOCOPY NUMBER
666 , x_msg_data OUT NOCOPY VARCHAR2
667 , p_debug IN VARCHAR2 := 'N'
668 , p_output_dir IN VARCHAR2 := NULL
669 , p_debug_filename IN VARCHAR2 := 'Ego_Catalog_Grp.log'
670 , x_catalog_group_id OUT NOCOPY NUMBER
671 , x_catalog_group_name OUT NOCOPY VARCHAR2
672 )
673
674 IS
675 l_entity_index number;
676 l_entity_id varchar2(2000);
677 l_message_type varchar2(2000);
678 l_message_list varchar2(2000);
679 v_dummy varchar2(1);
680
681 BEGIN
682
683 ---------------------------------------------------
684 -- Update catalog group
685 EGO_Item_Catalog_Pub.Process_Catalog_Group
686 ( p_Catalog_Group_Id => p_Catalog_Group_Id
687 , p_Parent_Catalog_Group_Id => p_Parent_Catalog_Group_Id
688 , p_Description => p_Description
689 , p_Item_Creation_Allowed_Flag => p_Item_Creation_Allowed_Flag
690 , p_Inactive_Date => p_Inactive_Date
691 , p_segment1 => p_segment1
692 , p_segment2 => p_segment2
693 , p_segment3 => p_segment3
694 , p_segment4 => p_segment4
695 , p_segment5 => p_segment5
696 , p_segment6 => p_segment6
697 , p_segment7 => p_segment7
698 , p_segment8 => p_segment8
699 , p_segment9 => p_segment9
700 , p_segment10 => p_segment10
701 , p_segment11 => p_segment11
702 , p_segment12 => p_segment12
703 , p_segment13 => p_segment13
704 , p_segment14 => p_segment14
705 , p_segment15 => p_segment15
706 , p_segment16 => p_segment16
707 , p_segment17 => p_segment17
708 , p_segment18 => p_segment18
709 , p_segment19 => p_segment19
710 , p_segment20 => p_segment20
711 , p_user_id => p_user_id
712 , p_Language_Code => userenv('LANG')
713 , p_Transaction_Type => 'UPDATE'
714 , x_return_status => x_return_status
715 , x_msg_count => x_msg_count
716 , p_debug => p_debug
717 , p_output_dir => p_output_dir
718 , p_debug_filename => p_debug_filename
719 , x_catalog_group_id => x_catalog_group_id
720 , x_catalog_group_name => x_catalog_group_name
721 );
722
723 -- Create an association between Default Template and Catalog Group
724 IF x_return_status = FND_API.G_RET_STS_SUCCESS THEN
725
726 -- check if catalog group is already associated with a default template
727 SELECT 'x' INTO v_dummy
728 FROM ego_cat_grp_templates
729 WHERE catalog_group_id = x_catalog_group_id;
730
731 IF p_Template_Id is not null THEN
732
733 -- update current association of catalog group with default template
734 UPDATE ego_cat_grp_templates
735 SET template_id = p_Template_Id
736 WHERE catalog_group_id = x_catalog_group_id;
737
738 ELSE
739
740 -- delete current association of catalog group with default template
741 DELETE FROM ego_cat_grp_templates
742 WHERE catalog_group_id = x_catalog_group_id;
743
744 END IF;
745
746 ELSIF x_return_status <> 'S' THEN
747 x_msg_count := Error_Handler.Get_Message_Count();
748
749 Error_Handler.Get_Message
750 ( x_message_text => l_message_list
751 , x_entity_index => l_entity_index
752 , x_entity_id => l_entity_id
753 , x_message_type => l_message_type
754 );
755
756 x_msg_count := ERROR_HANDLER.Get_Message_Count();
757 x_msg_data := 'Executing - EGO_ITEM_CATALOG_PUB.UPDATE_CATALOG_GROUP '||l_message_list;
758 END IF;
759
760
761
762 EXCEPTION
763 WHEN NO_DATA_FOUND THEN
764 -- There is no current association of default template to catalog group
765 IF p_Template_Id is not null THEN
766
767 INSERT INTO ego_cat_grp_templates
768 ( template_id
769 , catalog_group_id
770 , created_by
771 , creation_date
772 , last_updated_by
773 , last_update_date
774 )
775 VALUES
776 ( p_Template_Id
777 , x_catalog_group_id
778 , p_user_id
779 , sysdate
780 , p_user_id
781 , sysdate
782 );
783
784
785 END IF;
786
787 WHEN OTHERS THEN
788 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
789
790 Ego_Catalog_Group_Err_Handler.Log_Error
791 ( p_error_status => x_return_status
792 , p_error_scope => Error_Handler.G_SCOPE_ALL
793 , p_error_level => Error_Handler.G_BO_LEVEL
794 );
795
796 Error_Handler.Get_Message
797 ( x_message_text => l_message_list
798 , x_entity_index => l_entity_index
799 , x_entity_id => l_entity_id
800 , x_message_type => l_message_type
801 );
802
803 x_msg_count := ERROR_HANDLER.Get_Message_Count();
804 x_msg_data := 'Executing - EGO_ITEM_CATALOG_PUB.UPDATE_CATALOG_GROUP '||l_message_list;
805
806 END UPDATE_CATALOG_GROUP;
807
808 ---------------------------------------------------------------
809 -- Check before deleting an attribute group assoc ----
810 ---------------------------------------------------------------
811
812 PROCEDURE Check_Delete_AttrGroup_Assoc
813 (
814 p_api_version IN NUMBER
815 ,p_association_id IN NUMBER
816 ,p_classification_code IN VARCHAR2
817 ,p_data_level IN VARCHAR2
818 ,p_attr_group_id IN NUMBER
819 ,p_application_id IN NUMBER
820 ,p_attr_group_type IN VARCHAR2
821 ,p_attr_group_name IN VARCHAR2
822 ,p_enabled_code IN VARCHAR2
823 ,p_init_msg_list IN VARCHAR2 := fnd_api.g_FALSE
824 ,x_ok_to_delete OUT NOCOPY VARCHAR2
825 ,x_return_status OUT NOCOPY VARCHAR2
826 ,x_errorcode OUT NOCOPY NUMBER
827 ,x_msg_count OUT NOCOPY NUMBER
828 ,x_msg_data OUT NOCOPY VARCHAR2
829 )
830 IS
831
832 l_api_version CONSTANT NUMBER := 1.0;
833 l_count VARCHAR2(3);
834 l_api_name CONSTANT VARCHAR2(30) := 'Check_Delete_AttrGroup_Assoc';
835 l_message VARCHAR2(4000);
836 l_classification_codes VARCHAR2(32767);
837 l_attr_group_id VARCHAR2(40);
838 l_dynamic_sql VARCHAR2(32767);
839 l_attr_display_name VARCHAR2(250);
840 l_variant_Behaviour VARCHAR2(10);
841 l_style_exists VARCHAR2(1);
842
843 BEGIN
844
845 --Standard checks
846 IF NOT FND_API.Compatible_API_Call (l_api_version
847 ,p_api_version
848 ,l_api_name
849 ,g_pkg_name)
850 THEN
851 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
852 END IF;
853
854 -- Initialize message list if p_init_msg_list is set to TRUE
855 IF FND_API.To_Boolean(p_init_msg_list) THEN
856 FND_MSG_PUB.Initialize;
857 END IF;
858
859 SELECT
860 attr_group_disp_name INTO l_attr_display_name
861 FROM
862 ego_obj_attr_grp_assocs_v
863 WHERE association_id = p_association_id;
864
865 --Check if there are any entries for in EGO_PAGE_ENTRIES_V
866
867 -- Bug - 5068278 , avoided Full Table Scans
868 SELECT COUNT(*) INTO l_count
869 FROM EGO_PAGE_ENTRIES_B A,
870 FND_OBJECTS B ,
871 EGO_OBJ_ATTR_GRP_ASSOCS_V C
872 WHERE C.ASSOCIATION_ID = A.ASSOCIATION_ID
873 AND A.ASSOCIATION_ID = p_association_id
874 AND C.OBJECT_ID = B.OBJECT_ID
875 AND B.OBJ_NAME = 'EGO_ITEM' ;
876
877 IF (l_count > 0)
878 THEN
879 x_ok_to_delete := FND_API.G_FALSE;
880 l_message := 'EGO_ASSOCIATED_AG_IN_USE';
881 FND_MESSAGE.Set_Name(g_app_name, l_message);
882 FND_MESSAGE.Set_Token('ATTR_GROUP_NAME', l_attr_display_name);
883 FND_MSG_PUB.Add;
884 x_return_status := FND_API.G_RET_STS_ERROR;
885 END IF;
886
887 -- now need to get all the classification code that has p_classification_code as its parent
888 IF (l_count = 0) THEN
889
890 l_attr_group_id := p_attr_group_id || '%';
891
892 -- check if this ag is used to create any search criterias
893
894 l_dynamic_sql := 'SELECT COUNT (*) '||
895 'FROM AK_CRITERIA cols , '||
896 ' EGO_CRITERIA_TEMPLATES_V criterions '||
897 'WHERE cols.customization_code = criterions.customization_code '||
898 ' AND ( criterions.classification1 IN ( SELECT item_catalog_group_id '||
899 ' FROM mtl_item_catalog_groups_b CONNECT BY PRIOR item_catalog_group_id = '||
900 ' parent_catalog_group_id START WITH parent_catalog_group_id = :1 ) '||
901 ' OR criterions.classification1 = :2 ) '||
902 ' AND cols.attribute_code LIKE :3 '||
903 ' AND criterions.REGION_CODE LIKE ''EGO%'' '||
904 ' AND COLS.REGION_CODE = CRITERIONS.REGION_CODE ';
905
906 EXECUTE IMMEDIATE l_dynamic_sql INTO l_count USING p_classification_code, p_classification_code, l_attr_group_id;
907 IF (l_count > 0)
908 THEN
909 x_ok_to_delete := FND_API.G_FALSE;
910 l_message := 'EGO_ASSOCIATED_AG_IN_USE';
911 FND_MESSAGE.Set_Name(g_app_name, l_message);
912 FND_MESSAGE.Set_Token('ATTR_GROUP_NAME', l_attr_display_name);
913 FND_MSG_PUB.Add;
914 x_return_status := FND_API.G_RET_STS_ERROR;
915 END IF;
916
917 IF (l_count = 0) THEN
918 -- check if this ag is used to create any result formats
919 l_dynamic_sql := 'SELECT COUNT(*) ' ||
920 'FROM AK_CUSTOM_REGION_ITEMS COLS, ' ||
921 ' EGO_RESULTS_FORMAT_V RF ' ||
922 'WHERE cols.customization_code = RF.customization_code ' ||
923 ' AND ( RF.classification1 IN ( SELECT item_catalog_group_id '||
924 ' FROM mtl_item_catalog_groups_b CONNECT BY PRIOR item_catalog_group_id = '||
925 ' parent_catalog_group_id START WITH parent_catalog_group_id = :1 ) '||
926 ' OR RF.classification1 = :2 ) '||
927 ' AND cols.attribute_code LIKE :3 '||
928 ' AND RF.REGION_CODE LIKE ''EGO%'' ' ||
929 ' AND COLS.REGION_CODE = RF.REGION_CODE ';
930
931 EXECUTE IMMEDIATE l_dynamic_sql INTO l_count USING p_classification_code, p_classification_code, l_attr_group_id;
932 IF (l_count > 0)
933 THEN
934 x_ok_to_delete := FND_API.G_FALSE;
935 l_message := 'EGO_ASSOCIATED_AG_IN_USE';
936 FND_MESSAGE.Set_Name(g_app_name, l_message);
937 FND_MESSAGE.Set_Token('ATTR_GROUP_NAME', l_attr_display_name);
938 FND_MSG_PUB.Add;
939 x_return_status := FND_API.G_RET_STS_ERROR;
940 END IF;
941 END IF; --if no search criteria exist
942 END IF; -- no page entry exist
943
944 -- In Case the Attribute Group is Variant in Behavior it can not be deleted if
945 -- there exists any style or SKU item for this ICC
946 BEGIN
947 SELECT VARIANT INTO l_variant_Behaviour
948 FROM EGO_FND_DSC_FLX_CTX_EXT
949 WHERE ATTR_GROUP_ID = p_attr_group_id;
950 EXCEPTION
951 WHEN OTHERS THEN
952 l_variant_Behaviour := 'N';
953 END;
954
955 IF l_variant_Behaviour = 'Y' THEN
956 -- Style Exists check is enough for SKU Exists check,
957 -- Since SKU cannot exists with out a Style
958 l_style_exists := EGO_STYLE_SKU_ITEM_PVT.IsStyle_Item_Exist_For_ICC( p_classification_code );
959 IF l_style_exists = FND_API.G_TRUE THEN
960 x_ok_to_delete := FND_API.G_FALSE;
961 l_message := 'EGO_ASSOCIATED_AG_IN_USE';
962 FND_MESSAGE.Set_Name(g_app_name, l_message);
963 FND_MESSAGE.Set_Token('ATTR_GROUP_NAME', l_attr_display_name);
964 FND_MSG_PUB.Add;
965 x_return_status := FND_API.G_RET_STS_ERROR;
966 END IF;
967 END IF;
968
969
970 FND_MSG_PUB.Count_And_Get(
971 p_encoded => FND_API.G_FALSE,
972 p_count => x_msg_count,
973 p_data => x_msg_data
974 );
975
976 IF (l_message IS NULL) THEN
977 x_return_status := FND_API.G_RET_STS_SUCCESS;
978 x_ok_to_delete := FND_API.G_TRUE;
979 END IF;
980 EXCEPTION
981 WHEN OTHERS THEN
982 x_ok_to_delete := FND_API.G_FALSE;
983 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
984 FND_MESSAGE.Set_Name(g_app_name, g_plsql_err);
985 FND_MESSAGE.Set_Token(g_pkg_name_token, g_pkg_name);
986 FND_MESSAGE.Set_Token(g_api_name_token, l_api_name);
987 FND_MESSAGE.Set_Token(g_sql_err_msg_token, SQLERRM);
988 FND_MSG_PUB.Add;
989
990 END Check_Delete_AttrGroup_Assoc;
991 ---------------------------------------------------------------
992 -- Check before deleting an attribute group assoc ----
993 ---------------------------------------------------------------
994
995 PROCEDURE LOCK_ROW (
996 p_item_catalog_group_id IN NUMBER,
997 p_parent_catalog_group_id IN NUMBER,
998 p_item_creation_allowed_flag IN VARCHAR2,
999 p_inactive_date IN DATE,
1000 p_segment1 IN VARCHAR2,
1001 p_segment2 IN VARCHAR2,
1002 p_segment3 IN VARCHAR2,
1003 p_segment4 IN VARCHAR2,
1004 p_segment5 IN VARCHAR2,
1005 p_segment6 IN VARCHAR2,
1006 p_segment7 IN VARCHAR2,
1007 p_segment8 IN VARCHAR2,
1008 p_segment9 IN VARCHAR2,
1009 p_segment10 IN VARCHAR2,
1010 p_segment11 IN VARCHAR2,
1011 p_segment12 IN VARCHAR2,
1012 p_segment13 IN VARCHAR2,
1013 p_segment14 IN VARCHAR2,
1014 p_segment15 IN VARCHAR2,
1015 p_segment16 IN VARCHAR2,
1016 p_segment17 IN VARCHAR2,
1017 p_segment18 IN VARCHAR2,
1018 p_segment19 IN VARCHAR2,
1019 p_segment20 IN VARCHAR2,
1020 p_description IN VARCHAR2
1021 ) is
1022 cursor c is select
1023 PARENT_CATALOG_GROUP_ID,
1024 ITEM_CREATION_ALLOWED_FLAG,
1025 INACTIVE_DATE,
1026 SUMMARY_FLAG,
1027 ENABLED_FLAG,
1028 START_DATE_ACTIVE,
1029 END_DATE_ACTIVE,
1030 SEGMENT1,
1031 SEGMENT2,
1032 SEGMENT3,
1033 SEGMENT4,
1034 SEGMENT5,
1035 SEGMENT6,
1036 SEGMENT7,
1037 SEGMENT8,
1038 SEGMENT9,
1039 SEGMENT10,
1040 SEGMENT11,
1041 SEGMENT12,
1042 SEGMENT13,
1043 SEGMENT14,
1044 SEGMENT15,
1045 SEGMENT16,
1046 SEGMENT17,
1047 SEGMENT18,
1048 SEGMENT19,
1049 SEGMENT20,
1050 ATTRIBUTE_CATEGORY,
1051 ATTRIBUTE1,
1052 ATTRIBUTE2,
1053 ATTRIBUTE3,
1054 ATTRIBUTE4,
1055 ATTRIBUTE5,
1056 ATTRIBUTE6,
1057 ATTRIBUTE7,
1058 ATTRIBUTE8,
1059 ATTRIBUTE9,
1060 ATTRIBUTE10,
1061 ATTRIBUTE11,
1062 ATTRIBUTE12,
1063 ATTRIBUTE13,
1064 ATTRIBUTE14,
1065 ATTRIBUTE15,
1066 REQUEST_ID
1067 from MTL_ITEM_CATALOG_GROUPS_B
1068 where ITEM_CATALOG_GROUP_ID = p_item_catalog_group_id
1069 for update of ITEM_CATALOG_GROUP_ID nowait;
1070 recinfo c%rowtype;
1071
1072 cursor c1 is select
1073 DESCRIPTION,
1074 decode(LANGUAGE, userenv('LANG'), 'Y', 'N') BASELANG
1075 from MTL_ITEM_CATALOG_GROUPS_TL
1076 where ITEM_CATALOG_GROUP_ID = p_item_catalog_group_id
1077 and userenv('LANG') in (LANGUAGE, SOURCE_LANG)
1078 for update of ITEM_CATALOG_GROUP_ID nowait;
1079
1080
1081 BEGIN
1082
1083
1084 OPEN c;
1085 FETCH c into recinfo;
1086 IF (c%notfound) THEN
1087 CLOSE c;
1088 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
1089 app_exception.raise_exception;
1090 END IF;
1091 CLOSE c;
1092
1093 IF ( ((recinfo.PARENT_CATALOG_GROUP_ID = p_parent_catalog_group_id)
1094 OR ((recinfo.PARENT_CATALOG_GROUP_ID is null) AND (p_parent_catalog_group_id is null)))
1095 AND (recinfo.ITEM_CREATION_ALLOWED_FLAG = p_item_creation_allowed_flag)
1096 AND ((recinfo.INACTIVE_DATE = p_inactive_date)
1097 OR ((recinfo.INACTIVE_DATE is null) AND (p_inactive_date is null)))
1098 AND ((recinfo.SEGMENT1 = p_segment1)
1099 or ((recinfo.SEGMENT1 is null) AND (p_segment1 is null)))
1100 AND ((recinfo.SEGMENT2 = p_segment2)
1101 or ((recinfo.SEGMENT2 is null) AND (p_segment2 is null)))
1102 AND ((recinfo.SEGMENT3 = p_segment3)
1103 or ((recinfo.SEGMENT3 is null) AND (p_segment3 is null)))
1104 AND ((recinfo.SEGMENT4 = p_segment4)
1105 or ((recinfo.SEGMENT4 is null) AND (p_segment4 is null)))
1106 AND ((recinfo.SEGMENT5 = p_segment5)
1107 or ((recinfo.SEGMENT5 is null) AND (p_segment5 is null)))
1108 AND ((recinfo.SEGMENT6 = p_segment6)
1109 or ((recinfo.SEGMENT6 is null) AND (p_segment6 is null)))
1110 AND ((recinfo.SEGMENT7 = p_segment7)
1111 or ((recinfo.SEGMENT7 is null) AND (p_segment7 is null)))
1112 AND ((recinfo.SEGMENT8 = p_segment8)
1113 or ((recinfo.SEGMENT8 is null) AND (p_segment8 is null)))
1114 AND ((recinfo.SEGMENT9 = p_segment9)
1115 or ((recinfo.SEGMENT9 is null) AND (p_segment9 is null)))
1116 AND ((recinfo.SEGMENT10 = p_segment10)
1117 or ((recinfo.SEGMENT10 is null) AND (p_segment10 is null)))
1118 AND ((recinfo.SEGMENT11 = p_segment11)
1119 or ((recinfo.SEGMENT11 is null) AND (p_segment11 is null)))
1120 AND ((recinfo.SEGMENT12 = p_segment12)
1121 or ((recinfo.SEGMENT12 is null) AND (p_segment12 is null)))
1122 AND ((recinfo.SEGMENT13 = p_segment13)
1123 or ((recinfo.SEGMENT13 is null) AND (p_segment13 is null)))
1124 AND ((recinfo.SEGMENT14 = p_segment14)
1125 or ((recinfo.SEGMENT14 is null) AND (p_segment14 is null)))
1126 AND ((recinfo.SEGMENT15 = p_segment15)
1127 or ((recinfo.SEGMENT15 is null) AND (p_segment15 is null)))
1128 AND ((recinfo.SEGMENT16 = p_segment16)
1129 or ((recinfo.SEGMENT16 is null) AND (p_segment16 is null)))
1130 AND ((recinfo.SEGMENT17 = p_segment17)
1131 or ((recinfo.SEGMENT17 is null) AND (p_segment17 is null)))
1132 AND ((recinfo.SEGMENT18 = p_segment18)
1133 or ((recinfo.SEGMENT18 is null) AND (p_segment18 is null)))
1134 AND ((recinfo.SEGMENT19 = p_segment19)
1135 or ((recinfo.SEGMENT19 is null) AND (p_segment19 is null)))
1136 AND ((recinfo.SEGMENT20 = p_segment20)
1137 or ((recinfo.SEGMENT20 is null) AND (p_segment20 is null)))
1138 ) THEN
1139 null;
1140 ELSE
1141 --failed to lock row b/c data has changed since last fetch
1142 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
1143 app_exception.raise_exception;
1144 end if;
1145
1146
1147 for tlinfo in c1 loop
1148 if (tlinfo.BASELANG = 'Y') then
1149 if ( ((tlinfo.DESCRIPTION = p_description)
1150 or ((tlinfo.DESCRIPTION is null) AND (p_description is null)))
1151 ) then
1152 null;
1153 else
1154 --failed to lock row b/c data has changed since last fetch
1155 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
1156 app_exception.raise_exception;
1157 end if;
1158 end if;
1159 end loop;
1160
1161 return;
1162 end LOCK_ROW;
1163
1164
1165
1166
1167
1168
1169 END EGO_ITEM_CATALOG_PUB;