[Home] [Help]
PACKAGE BODY: APPS.EGO_UPLOAD_PUB
Source
1 PACKAGE BODY EGO_UPLOAD_PUB AS
2 /* $Header: EGOPUPLB.pls 120.4 2011/10/24 09:04:11 nendrapu ship $ */
3
4 -----------
5 -- private procedures
6 -----------
7 procedure code_debug(p_message IN VARCHAR2) IS
8 BEGIN
9 -- sri_debug(p_message);
10 NULL;
11 END code_debug;
12
13
14 FUNCTION get_object_id RETURN NUMBER IS
15 l_object_id NUMBER;
16 BEGIN
17 SELECT object_id
18 INTO l_object_id
19 FROM FND_OBJECTS
20 WHERE OBJ_NAME = 'EGO_ITEM';
21 RETURN l_object_id;
22 EXCEPTION
23 WHEN OTHERS THEN
24 RETURN NULL;
25 END;
26
27
28 PROCEDURE get_catalog_group_id (p_catalog_group IN VARCHAR2
29 ,x_catalog_group_id OUT NOCOPY NUMBER
30 ) IS
31 BEGIN
32 SELECT CATALOG_GROUP_ID
33 INTO x_catalog_group_id
34 from EGO_CATALOG_GROUPS_V
35 where catalog_group = p_catalog_group;
36 EXCEPTION
37 WHEN OTHERS THEN
38 x_catalog_group_id := NULL;
39 END get_catalog_group_id;
40
41
42 PROCEDURE get_attr_group_id (p_attr_group_name IN VARCHAR2
43 ,p_attr_group_type IN VARCHAR2
44 ,x_attr_group_id OUT NOCOPY NUMBER
45 ) IS
46 BEGIN
47 SELECT attr_group_id
48 INTO x_attr_group_id
49 from ego_fnd_dsc_flx_ctx_ext
50 WHERE descriptive_flexfield_name = p_attr_group_type
51 AND descriptive_flex_context_code = p_attr_group_name
52 AND application_id = 431;
53 EXCEPTION
54 WHEN OTHERS THEN
55 x_attr_group_id := NULL;
56 END get_attr_group_id;
57
58
59 PROCEDURE get_association_id (p_object_id IN NUMBER
60 ,p_catalog_group_id IN NUMBER
61 ,p_attr_group_id IN NUMBER
62 ,x_association_id OUT NOCOPY NUMBER
63 ) IS
64 BEGIN
65 SELECT association_id
66 INTO x_association_id
67 FROM EGO_OBJ_AG_ASSOCS_B
68 WHERE object_id = p_object_id
69 AND classification_code = TO_CHAR(p_catalog_group_id)
70 AND attr_group_id = p_attr_group_id;
71 EXCEPTION
72 WHEN OTHERS THEN
73 x_association_id := NULL;
74 END get_association_id;
75
76
77 PROCEDURE get_page_id (p_object_id IN NUMBER
78 ,p_catalog_group_id IN NUMBER
79 ,p_page_int_name IN VARCHAR2
80 ,x_page_id OUT NOCOPY NUMBER
81 ) IS
82 BEGIN
83 SELECT page_id
84 INTO x_page_id
85 FROM EGO_PAGES_B
86 WHERE object_id = p_object_id
87 AND classification_code = TO_CHAR(p_catalog_group_id)
88 AND internal_name = p_page_int_name;
89 EXCEPTION
90 WHEN OTHERS THEN
91 x_page_id := NULL;
92 END get_page_id;
93
94
95 PROCEDURE get_page_entry (p_page_id IN NUMBER
96 ,p_association_id IN NUMBER
97 ,p_catalog_group_id IN NUMBER
98 ,x_sequence OUT NOCOPY NUMBER
99 ) IS
100 BEGIN
101 SELECT sequence
102 INTO x_sequence
103 FROM ego_page_entries_b
104 WHERE page_id = p_page_id
105 AND association_id = p_association_id
106 AND classification_code = TO_CHAR(p_catalog_group_id);
107 EXCEPTION
108 WHEN OTHERS THEN
109 x_sequence := NULL;
110 END get_page_entry;
111
112
113 PROCEDURE createBaseAttributePages (p_catalog_group_id IN NUMBER
114 ,x_return_status OUT NOCOPY VARCHAR2
115 ) IS
116 l_object_id NUMBER;
117 --
118 -- Bug 9949093. Meta data import creating default pages to
119 -- item level. It should be at item org level.
120 -- sreharih. Wed Apr 13 13:54:12 PDT 2011
121 --
122 l_data_level VARCHAR2(20) := 'ITEM_ORG';
123 l_application_id NUMBER := 431;
124 l_attr_group_type VARCHAR2(40) := 'EGO_MASTER_ITEMS';
125 l_page_desc VARCHAR2(80) := 'Auto-generated operational attribute page';
126 l_page_id NUMBER;
127 l_association_id NUMBER;
128 l_pages_array EGO_COL_NAME_VALUE_PAIR_ARRAY;
129 l_pages_int_array EGO_COL_NAME_VALUE_PAIR_ARRAY; -- Bug 9949093.
130 l_page_entry_array EGO_COL_NAME_VALUE_PAIR_ARRAY;
131 -- l_name_value_obj EGO_COL_NAME_VALUE_PAIR_OBJ;
132 l_return_status VARCHAR2(1);
133 l_msg_count NUMBER;
134 l_msg_data VARCHAR2(4000);
135
136 BEGIN
137 l_object_id := get_object_id();
138 for k in 1..2 LOOP
139 IF K = 1 THEN
140 l_pages_array := EGO_COL_NAME_VALUE_PAIR_ARRAY (
141 EGO_COL_NAME_VALUE_PAIR_OBJ('10','Physical Attributes'),
142 EGO_COL_NAME_VALUE_PAIR_OBJ('20','Sales and Order Management'),
143 EGO_COL_NAME_VALUE_PAIR_OBJ('30','Planning'),
144 EGO_COL_NAME_VALUE_PAIR_OBJ('40','Purchasing'),
145 EGO_COL_NAME_VALUE_PAIR_OBJ('50','Inventory/WMS'),
146 EGO_COL_NAME_VALUE_PAIR_OBJ('60','Manufacturing')
147 );
148 --
149 -- Bug 9949093. Meta data import creating default pages with
150 -- incorrect internal name. New array for internal names.
151 -- sreharih. Wed Apr 13 13:54:12 PDT 2011
152 --
153 l_pages_int_array := EGO_COL_NAME_VALUE_PAIR_ARRAY (
154 EGO_COL_NAME_VALUE_PAIR_OBJ('10', 'EGO_PHYSICAL_ATTRS_PAGE_NAME'), --Physical Attributes
155 EGO_COL_NAME_VALUE_PAIR_OBJ('20', 'EGO_SALES_PAGE_NAME'),--Sales and Order Management
156 EGO_COL_NAME_VALUE_PAIR_OBJ('30', 'EGO_PLANNING_PAGE_NAME'),--Planning
157 EGO_COL_NAME_VALUE_PAIR_OBJ('40', 'EGO_PURCHASING_PAGE_NAME'),--Purchasing
158 EGO_COL_NAME_VALUE_PAIR_OBJ('50', 'EGO_INVENTORY_PAGE_NAME'),--Inventory/WMS
159 EGO_COL_NAME_VALUE_PAIR_OBJ('60', 'EGO_MANUFACTURING_PAGE_NAME')--Manufacturing
160 );
161
162 ELSE
163 l_pages_array := EGO_COL_NAME_VALUE_PAIR_ARRAY (
164 EGO_COL_NAME_VALUE_PAIR_OBJ('70','Service')
165 );
166 l_pages_int_array := EGO_COL_NAME_VALUE_PAIR_ARRAY (
167 EGO_COL_NAME_VALUE_PAIR_OBJ('70','EGO_SERVICE_PAGE_NAME')--Service
168 );
169 END IF;
170 FOR i IN l_pages_array.FIRST .. l_pages_array.LAST LOOP
171
172 --
173 -- Bug 9949093. Meta data import creating default pages with
174 -- incorrect internal name. Pass correct internal array to the method.
175 -- sreharih. Wed Apr 13 13:54:12 PDT 2011
176 --
177
178
179 sync_cat_item_pages (
180 p_api_version => 1.0
181 ,p_commit => 'F'
182 ,p_catalog_group_id => p_catalog_group_id
183 ,p_catalog_group => NULL
184 ,p_data_level => l_data_level
185 ,p_page_int_name => l_pages_int_array(i).value -- Bug 9949093.
186 ,p_name => l_pages_array(i).value
187 ,p_desc => l_page_desc
188 ,p_sequence => l_pages_array(i).name
189 ,x_page_id => l_page_id
190 ,x_return_status => l_return_status
191 ,x_msg_count => l_msg_count
192 ,x_msg_data => l_msg_data
193 );
194 IF l_pages_array(i).name = 10 THEN
195 l_page_entry_array := EGO_COL_NAME_VALUE_PAIR_ARRAY (
196 EGO_COL_NAME_VALUE_PAIR_OBJ('10','PhysicalAttributes')
197 );
198 ELSIF l_pages_array(i).name = 20 THEN
199 l_page_entry_array := EGO_COL_NAME_VALUE_PAIR_ARRAY (
200 EGO_COL_NAME_VALUE_PAIR_OBJ('10','OrderManagement'),
201 EGO_COL_NAME_VALUE_PAIR_OBJ('20','Invoicing'),
202 EGO_COL_NAME_VALUE_PAIR_OBJ('30','WebOption')
203 );
204 ELSIF l_pages_array(i).name = 30 THEN
205 l_page_entry_array := EGO_COL_NAME_VALUE_PAIR_ARRAY (
206 EGO_COL_NAME_VALUE_PAIR_OBJ('10','GeneralPlanning'),
207 EGO_COL_NAME_VALUE_PAIR_OBJ('20','MPSMRPPlanning'),
208 EGO_COL_NAME_VALUE_PAIR_OBJ('30','LeadTimes')
209 );
210 ELSIF l_pages_array(i).name = 40 THEN
211 l_page_entry_array := EGO_COL_NAME_VALUE_PAIR_ARRAY (
212 EGO_COL_NAME_VALUE_PAIR_OBJ('10','Purchasing'),
213 EGO_COL_NAME_VALUE_PAIR_OBJ('20','Receiving')
214 );
215 ELSIF l_pages_array(i).name = 50 THEN
216 l_page_entry_array := EGO_COL_NAME_VALUE_PAIR_ARRAY (
217 EGO_COL_NAME_VALUE_PAIR_OBJ('10','Inventory')
218 );
219 ELSIF l_pages_array(i).name = 60 THEN
220 l_page_entry_array := EGO_COL_NAME_VALUE_PAIR_ARRAY (
221 EGO_COL_NAME_VALUE_PAIR_OBJ('10','BillofMaterials'),
222 EGO_COL_NAME_VALUE_PAIR_OBJ('20','Costing'),
223 EGO_COL_NAME_VALUE_PAIR_OBJ('30','WorkInProgress'),
224 EGO_COL_NAME_VALUE_PAIR_OBJ('40','ProcessManufacturing')
225 );
226 ELSIF l_pages_array(i).name = 70 THEN
227 l_page_entry_array := EGO_COL_NAME_VALUE_PAIR_ARRAY (
228 EGO_COL_NAME_VALUE_PAIR_OBJ('10','AssetManagement'),
229 EGO_COL_NAME_VALUE_PAIR_OBJ('20','Service')
230 );
231 END IF;
232 FOR j in l_page_entry_array.FIRST .. l_page_entry_array.LAST LOOP
233 sync_cat_attr_grp_assoc (
234 p_api_version => 1.0
235 ,p_commit => 'F'
236 ,p_catalog_group_id => p_catalog_group_id
237 ,p_catalog_group => NULL
238 ,p_data_level => l_data_level
239 ,p_attr_group_name => l_page_entry_array(j).value
240 ,p_attr_group_type => l_attr_group_type
241 ,p_enabled_flag => 'Y'
242 ,x_association_id => l_association_id
243 ,x_return_status => l_return_status
244 ,x_msg_count => l_msg_count
245 ,x_msg_data => l_msg_data
246 );
247
248 --
249 -- Bug 9949093. Meta data import is not creating proper
250 -- page entries for default pages. Need to pass catalog_group_id,
251 -- p_attr_group_name and p_attr_group_type to the method.
252 -- sreharih. Wed Apr 13 13:54:12 PDT 2011
253 --
254
255
256 sync_cat_item_page_entries (
257 p_api_version => 1.0
258 ,p_commit => 'F'
259 ,p_catalog_group => NULL
260 ,p_catalog_group_id => p_catalog_group_id -- Bug 9949093.
261 ,p_page_id => l_page_id
262 ,p_page_int_name => NULL
263 ,p_attr_group_name => l_page_entry_array(j).value -- Bug 9949093.
264 ,p_attr_group_type => l_attr_group_type -- Bug 9949093.
265 ,p_sequence => l_page_entry_array(j).name
266 ,p_association_id => l_association_id
267 ,x_return_status => l_return_status
268 ,x_msg_count => l_msg_count
269 ,x_msg_data => l_msg_data
270 );
271
272 END LOOP;
273 l_page_entry_array := NULL;
274 END LOOP;
275 l_pages_array := NULL;
276 END LOOP;
277 EXCEPTION
278 WHEN OTHERS THEN
279 code_debug('createBaseAttributePages raise EXCEPTION '||SQLERRM);
280
281 END createBaseAttributePages;
282
283 -- Bug 13081424 : Start
284 -- Below api is used to create base attribute groups association alone
285 -- with out creating pages and page entries for base attributes.
286 -- For base attributes, pages and page entries can be deleted or modified,
287 -- So we do not create pages and page entries while uploading an ldt file to create ICC.
288
289 -- If pages and page entires to be created they should exist in the ldt file
290 -- So that those will be created from ldt file.
291
292 PROCEDURE createBaseAttributeAssocs (p_catalog_group_id IN NUMBER
293 ,x_return_status OUT NOCOPY VARCHAR2
294 ) IS
295 l_object_id NUMBER;
296 l_data_level VARCHAR2(20) := 'ITEM_ORG';
297 l_application_id NUMBER := 431;
298 l_attr_group_type VARCHAR2(40) := 'EGO_MASTER_ITEMS';
299 l_association_id NUMBER;
300 l_ag_assocs_array EGO_COL_NAME_VALUE_PAIR_ARRAY;
301 l_return_status VARCHAR2(1);
302 l_msg_count NUMBER;
303 l_msg_data VARCHAR2(4000);
304
305 BEGIN
306 l_object_id := get_object_id();
307
308 FOR i IN 1..3 LOOP
309 IF i = 1 THEN
310 l_ag_assocs_array := EGO_COL_NAME_VALUE_PAIR_ARRAY (
311 EGO_COL_NAME_VALUE_PAIR_OBJ('1','PhysicalAttributes'),
312 EGO_COL_NAME_VALUE_PAIR_OBJ('2','OrderManagement'),
313 EGO_COL_NAME_VALUE_PAIR_OBJ('3','Invoicing'),
314 EGO_COL_NAME_VALUE_PAIR_OBJ('4','WebOption'),
315 EGO_COL_NAME_VALUE_PAIR_OBJ('5','GeneralPlanning'),
316 EGO_COL_NAME_VALUE_PAIR_OBJ('6','Manufacturing')
317 );
318 ELSIF i = 2 THEN
319 l_ag_assocs_array := EGO_COL_NAME_VALUE_PAIR_ARRAY (
320 EGO_COL_NAME_VALUE_PAIR_OBJ('1','LeadTimes'),
321 EGO_COL_NAME_VALUE_PAIR_OBJ('2','Purchasing'),
322 EGO_COL_NAME_VALUE_PAIR_OBJ('3','Receiving'),
323 EGO_COL_NAME_VALUE_PAIR_OBJ('4','Inventory'),
324 EGO_COL_NAME_VALUE_PAIR_OBJ('5','BillofMaterials'),
325 EGO_COL_NAME_VALUE_PAIR_OBJ('6','Costing')
326 );
327 ELSE
328 l_ag_assocs_array := EGO_COL_NAME_VALUE_PAIR_ARRAY (
329 EGO_COL_NAME_VALUE_PAIR_OBJ('1','WorkInProgress'),
330 EGO_COL_NAME_VALUE_PAIR_OBJ('2','ProcessManufacturing'),
331 EGO_COL_NAME_VALUE_PAIR_OBJ('3','AssetManagement'),
332 EGO_COL_NAME_VALUE_PAIR_OBJ('4','Service')
333 );
334 END IF;
335
336 FOR j in l_ag_assocs_array.FIRST .. l_ag_assocs_array.LAST LOOP
337 code_debug(' createBaseAttributeAssocs, Creatin attr group association for '|| l_ag_assocs_array(j).Value);
338 -- Create association for base attribute groups.
339 sync_cat_attr_grp_assoc (
340 p_api_version => 1.0
341 ,p_commit => 'F'
342 ,p_catalog_group_id => p_catalog_group_id
343 ,p_catalog_group => NULL
344 ,p_data_level => l_data_level
345 ,p_attr_group_name => l_ag_assocs_array(j).value
346 ,p_attr_group_type => l_attr_group_type
347 ,p_enabled_flag => 'Y'
348 ,x_association_id => l_association_id
349 ,x_return_status => l_return_status
350 ,x_msg_count => l_msg_count
351 ,x_msg_data => l_msg_data
352 );
353 END LOOP;
354 l_ag_assocs_array := NULL;
355 END LOOP;
356
357 EXCEPTION
358 WHEN OTHERS THEN
359 code_debug('createBaseAttributeAssocs raise EXCEPTION '||SQLERRM);
360
361 END createBaseAttributeAssocs;
362 -- Bug 13081424 : End
363
364 --------------------------------------------------------
365
366 Procedure Sync_Catalog_Group (
367 p_api_version IN NUMBER
368 ,p_commit IN VARCHAR2
369 ,p_catalog_group IN VARCHAR2
370 ,p_parent_catalog_group IN VARCHAR2
371 ,p_description IN VARCHAR2
372 ,p_template_name IN VARCHAR2
373 ,p_creation_allowed IN VARCHAR2
374 ,p_end_date IN DATE
375 ,p_owner IN VARCHAR2
376 ,p_extra_params IN EGO_COL_NAME_VALUE_PAIR_ARRAY DEFAULT NULL
377 ,x_catalog_group_id OUT NOCOPY NUMBER
378 ,x_return_status OUT NOCOPY VARCHAR2
379 ,x_msg_count OUT NOCOPY NUMBER
380 ,x_msg_data OUT NOCOPY VARCHAR2
381 ) IS
382 l_user_id NUMBER;
383 l_party_id NUMBER;
384 l_language VARCHAR2(30);
385 l_transaction_type VARCHAR2(30);
386 l_catalog_name VARCHAR2(100);
387 l_catalog_group_id NUMBER;
388 l_errorcode NUMBER;
389 l_grant_guid RAW(50);
390 BEGIN
391 code_debug('Sync_Catalog_Group called with params ');
392 -- code_debug('Sync_Catalog_Group p_catalog_group: '||p_catalog_group||' p_parent_catalog_group '||p_parent_catalog_group);
393 -- code_debug('Sync_Catalog_Group p_description: '||p_description||' p_template_name '||p_template_name);
394 -- code_debug('Sync_Catalog_Group p_creation_allowed: '||p_creation_allowed||' p_end_date '||p_end_date);
395 -- code_debug('Sync_Catalog_Group p_owner: '||p_owner);
396 -- mandatory param check
397 -- check if the catalog group exists, if so, please update else create new
398 -- if creating catalog category, then create roles as well
399 BEGIN
400 SELECT user_id, party_id
401 INTO l_user_id, l_party_id
402 from ego_user_v
403 where user_name = DECODE(p_owner,EGO_ITEM_PUB.G_ALL_USERS_PARTY_TYPE,'MFG',p_owner);
404 IF p_owner = EGO_ITEM_PUB.G_ALL_USERS_PARTY_TYPE THEN
405 l_party_id := -1000;
406 END IF;
407 EXCEPTION
408 WHEN OTHERS THEN
409 -- defaulting to MFG
410 SELECT user_id, party_id
411 INTO l_user_id, l_party_id
412 from ego_user_v
413 where user_name = 'MFG';
414 -- l_user_id := 1068;
415 -- l_party_id := 6530;
416 END;
417 get_catalog_group_id (p_catalog_group => p_catalog_group
418 ,x_catalog_group_id => l_catalog_group_id
419 );
420 IF l_catalog_group_id IS NULL THEN
421 l_transaction_type := EGO_ITEM_PUB.G_TTYPE_CREATE;
422 ELSE
423 l_transaction_type := EGO_ITEM_PUB.G_TTYPE_UPDATE;
424 END IF;
425 l_language := USERENV ('LANG');
426 code_debug('Sync_Catalog_Group calling EGO_ITEM_CATALOG_PUB.Process_Catalog_Group trans type '||l_transaction_type);
427 EGO_ITEM_CATALOG_PUB.Process_Catalog_Group
428 ( p_Catalog_Group_Name => p_catalog_group
429 , p_Parent_Catalog_Group_Name => p_parent_catalog_group
430 -- , p_Catalog_Group_Id IN NUMBER := NULL
431 -- , p_Parent_Catalog_Group_Id IN NUMBER := NULL
432 , p_Description => p_description
433 , p_Item_Creation_Allowed_Flag => p_creation_allowed
434 -- , p_Start_Effective_Date IN DATE := NULL
435 , p_Inactive_date => p_end_date
436 -- , p_Enabled_Flag IN VARCHAR2 := NULL
437 -- , p_Summary_Flag IN VARCHAR2 := NULL
438 -- , p_segment1 IN VARCHAR2 := NULL
439 -- , p_segment2 IN VARCHAR2 := NULL
440 -- , p_segment3 IN VARCHAR2 := NULL
441 -- , p_segment4 IN VARCHAR2 := NULL
442 -- , p_segment5 IN VARCHAR2 := NULL
443 -- , p_segment6 IN VARCHAR2 := NULL
444 -- , p_segment7 IN VARCHAR2 := NULL
445 -- , p_segment8 IN VARCHAR2 := NULL
446 -- , p_segment9 IN VARCHAR2 := NULL
447 -- , p_segment10 IN VARCHAR2 := NULL
448 -- , p_segment11 IN VARCHAR2 := NULL
449 -- , p_segment12 IN VARCHAR2 := NULL
450 -- , p_segment13 IN VARCHAR2 := NULL
451 -- , p_segment14 IN VARCHAR2 := NULL
452 -- , p_segment15 IN VARCHAR2 := NULL
453 -- , p_segment16 IN VARCHAR2 := NULL
454 -- , p_segment17 IN VARCHAR2 := NULL
455 -- , p_segment18 IN VARCHAR2 := NULL
456 -- , p_segment19 IN VARCHAR2 := NULL
457 -- , p_segment20 IN VARCHAR2 := NULL
458 -- , Attribute_category IN VARCHAR2 := NULL
459 -- , Attribute1 IN VARCHAR2 := NULL
460 -- , Attribute2 IN VARCHAR2 := NULL
461 -- , Attribute3 IN VARCHAR2 := NULL
462 -- , Attribute4 IN VARCHAR2 := NULL
463 -- , Attribute5 IN VARCHAR2 := NULL
464 -- , Attribute6 IN VARCHAR2 := NULL
465 -- , Attribute7 IN VARCHAR2 := NULL
466 -- , Attribute8 IN VARCHAR2 := NULL
467 -- , Attribute9 IN VARCHAR2 := NULL
468 -- , Attribute10 IN VARCHAR2 := NULL
469 -- , Attribute11 IN VARCHAR2 := NULL
470 -- , Attribute12 IN VARCHAR2 := NULL
471 -- , Attribute13 IN VARCHAR2 := NULL
472 -- , Attribute14 IN VARCHAR2 := NULL
473 -- , Attribute15 IN VARCHAR2 := NULL
474 , p_User_id => l_user_id
475 , p_Language_Code => l_language
476 , p_Transaction_Type => l_transaction_type
477 , x_Return_Status => x_return_status
478 , x_msg_count => x_msg_count
479 -- , p_debug IN VARCHAR2 := 'N'
480 -- , p_output_dir IN VARCHAR2 := NULL
481 -- , p_debug_filename IN VARCHAR2 := 'Ego_Catalog_Grp.log'
482 , x_catalog_group_id => x_catalog_group_id
483 , x_catalog_group_name => l_catalog_name
484 );
485 -- code_debug('Sync_Catalog_Group returning EGO_ITEM_CATALOG_PUB.Process_Catalog_Group with status '||x_Return_Status);
486 x_catalog_group_id := NVL(x_catalog_group_id, l_catalog_group_id);
487 x_return_status := NVL(x_return_status,EGO_ITEM_PUB.G_RET_STS_SUCCESS);
488 IF x_return_status = EGO_ITEM_PUB.G_RET_STS_SUCCESS AND
489 l_transaction_type = EGO_ITEM_PUB.G_TTYPE_CREATE THEN
490
491 -- Bug fix# 10306119 - CHECHAND - START
492 -- Default the value of "NEW_ITEM_REQUEST_REQD" field to 'N'. This is required to mark the ICC as one without any new item request setup.
493 UPDATE MTL_ITEM_CATALOG_GROUPS_B
494 SET NEW_ITEM_REQUEST_REQD = 'N'
495 WHERE ITEM_CATALOG_GROUP_ID = x_catalog_group_id;
496 -- Bug fix# 10306119 - CHECHAND - END
497
498 IF p_parent_catalog_group IS NULL THEN
499 -- Bug 13081424 : Start
500 -- Call below api to create base attribute groups association alone
501 -- with out creating pages and page entries for base attributes while uploading using an ldt.
502 createBaseAttributeAssocs(p_catalog_group_id => x_catalog_group_id
503 ,x_return_status => x_return_status
504 );
505 /*
506 createBaseAttributePages(p_catalog_group_id => x_catalog_group_id
507 ,x_return_status => x_return_status
508 );
509 */
510 -- Bug 13081424 : End
511 END IF;
512 EGO_SECURITY_PUB.grant_role_guid(
513 p_api_version => p_api_version
514 , p_role_name => 'EGO_CATALOG_GROUP_USER'
515 , p_object_name => 'EGO_CATALOG_GROUP'
516 , p_instance_type => EGO_ITEM_PUB.G_INSTANCE_TYPE_INSTANCE
517 , p_instance_set_id => NULL
518 , p_instance_pk1_value => x_catalog_group_id
519 , p_instance_pk2_value => NULL
520 , p_instance_pk3_value => NULL
521 , p_instance_pk4_value => NULL
522 , p_instance_pk5_value => NULL
523 , p_party_id => l_party_id
524 , p_start_date => NULL
525 , p_end_date => NULL
526 , x_return_status => x_return_status
527 , x_errorcode => l_errorcode
528 , x_grant_guid => l_grant_guid
529 );
530 IF FND_API.TO_BOOLEAN(x_return_status) THEN
531 x_return_status := EGO_ITEM_PUB.G_RET_STS_SUCCESS;
532 ELSE
533 x_return_status := EGO_ITEM_PUB.G_RET_STS_ERROR;
534 END IF;
535 END IF;
536 IF FND_API.TO_BOOLEAN(p_commit) AND
537 x_return_status = EGO_ITEM_PUB.G_RET_STS_SUCCESS THEN
538 COMMIT WORK;
539 END IF;
540 -- code_debug('Sync_Catalog_Group returning with status '||x_return_status);
541 EXCEPTION
542 WHEN OTHERS THEN
543 code_debug('Sync_Catalog_Group returning EXCEPTION '||SQLERRM);
544 x_return_status := EGO_ITEM_PUB.G_RET_STS_UNEXP_ERROR;
545 x_msg_data := SQLERRM;
546 END Sync_Catalog_Group;
547
548 --------------------------------------------------------
549 PROCEDURE sync_cat_attr_grp_assoc (
550 p_api_version IN NUMBER
551 ,p_commit IN VARCHAR2
552 ,p_catalog_group_id IN NUMBER
553 ,p_catalog_group IN VARCHAR2
554 ,p_data_level IN VARCHAR2
555 ,p_attr_group_name IN VARCHAR2
556 ,p_attr_group_type IN VARCHAR2
557 ,p_enabled_flag IN VARCHAR2
558 ,p_extra_params IN EGO_COL_NAME_VALUE_PAIR_ARRAY DEFAULT NULL
559 ,x_association_id OUT NOCOPY NUMBER
560 ,x_return_status OUT NOCOPY VARCHAR2
561 ,x_msg_count OUT NOCOPY NUMBER
562 ,x_msg_data OUT NOCOPY VARCHAR2
563 ) IS
564 l_catalog_group_id NUMBER;
565 l_attr_group_id NUMBER;
566 l_object_id NUMBER;
567 l_association_id NUMBER;
568 l_errorcode NUMBER;
569 BEGIN
570 -- code_debug('sync_cat_attr_grp_assoc called with params p_attr_group_name '||p_attr_group_name);
571 IF p_catalog_group_id IS NULL THEN
572 get_catalog_group_id (p_catalog_group => p_catalog_group
573 ,x_catalog_group_id => l_catalog_group_id
574 );
575 ELSE
576 l_catalog_group_id := p_catalog_group_id;
577 END IF;
578 IF l_catalog_group_id IS NULL THEN
579 x_return_status := EGO_ITEM_PUB.G_RET_STS_ERROR;
580 x_msg_data := 'sync_cat_attr_grp_assoc Catalog Category does not exist for '||p_catalog_group;
581 RETURN;
582 END IF;
583 get_attr_group_id (p_attr_group_name => p_attr_group_name
584 ,p_attr_group_type => p_attr_group_type
585 ,x_attr_group_id => l_attr_group_id
586 );
587 IF l_attr_group_id IS NULL THEN
588 x_return_status := EGO_ITEM_PUB.G_RET_STS_ERROR;
589 x_msg_data := 'sync_cat_attr_grp_assoc attr group does not exist for '||p_attr_group_name;
590 RETURN;
591 END IF;
592 l_object_id := get_object_id ();
593 get_association_id (p_object_id => l_object_id
594 ,p_catalog_group_id => l_catalog_group_id
595 ,p_attr_group_id => l_attr_group_id
596 ,x_association_id => x_association_id
597 );
598 IF x_association_id IS NULL THEN
599 EGO_EXT_FWK_PUB.Create_Association (
600 p_api_version => p_api_version
601 ,p_association_id => NULL
602 ,p_object_id => l_object_id
603 ,p_classification_code => TO_CHAR(l_catalog_group_id)
604 ,p_data_level => p_data_level
605 ,p_attr_group_id => l_attr_group_id
606 ,p_enabled_flag => p_enabled_flag
607 ,p_view_privilege_id => NULL --ignored for now
608 ,p_edit_privilege_id => NULL --ignored for now
609 -- ,p_init_msg_list IN VARCHAR2 := fnd_api.g_FALSE
610 ,p_commit => p_commit
611 ,x_association_id => x_association_id
612 ,x_return_status => x_return_status
613 ,x_errorcode => l_errorcode
614 ,x_msg_count => x_msg_count
615 ,x_msg_data => x_msg_data
616 );
617 ELSE
618 EGO_EXT_FWK_PUB. Update_Association (
619 p_api_version => p_api_version
620 ,p_association_id => x_association_id
621 ,p_enabled_flag => p_enabled_flag
622 ,p_view_privilege_id => NULL --ignored for now
623 ,p_edit_privilege_id => NULL --ignored for now
624 -- ,p_init_msg_list IN VARCHAR2 := fnd_api.g_FALSE
625 ,p_commit => p_commit
626 ,x_return_status => x_return_status
627 ,x_errorcode => l_errorcode
628 ,x_msg_count => x_msg_count
629 ,x_msg_data => x_msg_data
630 );
631 END IF;
632 x_return_status := NVL(x_return_status,EGO_ITEM_PUB.G_RET_STS_SUCCESS);
633 -- code_debug('sync_cat_attr_grp_assoc returning with status '||x_return_status);
634
635 EXCEPTION
636 WHEN OTHERS THEN
637 code_debug('sync_cat_attr_grp_assoc returning EXCEPTION '||SQLERRM);
638 x_return_status := EGO_ITEM_PUB.G_RET_STS_UNEXP_ERROR;
639 x_msg_data := SQLERRM;
640 END sync_cat_attr_grp_assoc;
641
642 --------------------------------------------------------
643 PROCEDURE sync_cat_item_pages (
644 p_api_version IN NUMBER
645 ,p_commit IN VARCHAR2
646 ,p_catalog_group_id IN NUMBER
647 ,p_catalog_group IN VARCHAR2
648 ,p_data_level IN VARCHAR2
649 ,p_page_int_name IN VARCHAR2
650 ,p_name IN VARCHAR2
651 ,p_desc IN VARCHAR2
652 ,p_sequence IN NUMBER
653 ,p_extra_params IN EGO_COL_NAME_VALUE_PAIR_ARRAY DEFAULT NULL
654 ,x_page_id OUT NOCOPY NUMBER
655 ,x_return_status OUT NOCOPY VARCHAR2
656 ,x_msg_count OUT NOCOPY NUMBER
657 ,x_msg_data OUT NOCOPY VARCHAR2
658 ) IS
659 l_catalog_group_id NUMBER;
660 l_attr_group_id NUMBER;
661 l_object_id NUMBER;
662 l_errorcode NUMBER;
663 BEGIN
664 -- code_debug('sync_cat_item_pages called with params p_page_int_name '||p_page_int_name);
665 IF p_catalog_group_id IS NULL THEN
666 get_catalog_group_id (p_catalog_group => p_catalog_group
667 ,x_catalog_group_id => l_catalog_group_id
668 );
669 ELSE
670 l_catalog_group_id := p_catalog_group_id;
671 END IF;
672 IF l_catalog_group_id IS NULL THEN
673 x_return_status := EGO_ITEM_PUB.G_RET_STS_ERROR;
674 x_msg_data := 'sync_cat_item_pages Catalog Category does not exist for '||p_catalog_group;
675 RETURN;
676 END IF;
677 l_object_id := get_object_id();
678 get_page_id (p_object_id => l_object_id
679 ,p_catalog_group_id => l_catalog_group_id
680 ,p_page_int_name => p_page_int_name
681 ,x_page_id => x_page_id
682 );
683 IF x_page_id IS NULL THEN
684 EGO_EXT_FWK_PUB.Create_Page (
685 p_api_version => p_api_version
686 ,p_page_id => NULL
687 ,p_object_id => l_object_id
688 ,p_classification_code => TO_CHAR(l_catalog_group_id)
689 ,p_data_level => p_data_level
690 ,p_internal_name => p_page_int_name
691 ,p_display_name => p_name
692 ,p_description => p_desc
693 ,p_sequence => p_sequence
694 -- ,p_init_msg_list IN VARCHAR2 := fnd_api.g_FALSE
695 ,p_commit => p_commit
696 ,x_page_id => x_page_id
697 ,x_return_status => x_return_status
698 ,x_errorcode => l_errorcode
699 ,x_msg_count => x_msg_count
700 ,x_msg_data => x_msg_data
701 );
702 ELSE
703 EGO_EXT_FWK_PUB.Update_Page (
704 p_api_version => p_api_version
705 ,p_page_id => x_page_id
706 ,p_internal_name => p_page_int_name
707 ,p_display_name => p_name
708 ,p_description => p_desc
709 ,p_sequence => p_sequence
710 -- ,p_init_msg_list IN VARCHAR2 := fnd_api.g_FALSE
711 ,p_commit => p_commit
712 -- ,p_is_nls_mode IN VARCHAR2 DEFAULT FND_API.G_FALSE
713 ,x_return_status => x_return_status
714 ,x_errorcode => l_errorcode
715 ,x_msg_count => x_msg_count
716 ,x_msg_data => x_msg_data
717 );
718 END IF;
719 x_return_status := NVL(x_return_status,EGO_ITEM_PUB.G_RET_STS_SUCCESS);
720 EXCEPTION
721 WHEN OTHERS THEN
722 code_debug('sync_cat_item_pages returning EXCEPTION '||SQLERRM);
723 x_return_status := EGO_ITEM_PUB.G_RET_STS_UNEXP_ERROR;
724 x_msg_data := SQLERRM;
725 END sync_cat_item_pages;
726
727 --
728 -- Bug 9949093. Meta data import is not creating proper
729 -- page entries for default pages. Added new param for
730 -- p_catalog_group_id.
731 -- sreharih. Wed Apr 13 13:54:12 PDT 2011
732 --
733
734
735
736 --------------------------------------------------------
737 PROCEDURE sync_cat_item_page_entries (
738 p_api_version IN NUMBER
739 ,p_commit IN VARCHAR2
740 ,p_catalog_group IN VARCHAR2
741 ,p_catalog_group_id IN NUMBER
742 ,p_page_id IN NUMBER
743 ,p_page_int_name IN VARCHAR2
744 ,p_attr_group_name IN VARCHAR2
745 ,p_attr_group_type IN VARCHAR2
746 ,p_sequence IN NUMBER
747 ,p_association_id IN NUMBER
748 ,p_extra_params IN EGO_COL_NAME_VALUE_PAIR_ARRAY DEFAULT NULL
749 ,x_return_status OUT NOCOPY VARCHAR2
750 ,x_msg_count OUT NOCOPY NUMBER
751 ,x_msg_data OUT NOCOPY VARCHAR2
752 ) IS
753 l_catalog_group_id NUMBER;
754 l_attr_group_id NUMBER;
755 l_object_id NUMBER;
756 l_association_id NUMBER;
757 l_page_id NUMBER;
758 l_errorcode NUMBER;
759 l_sequence NUMBER;
760 BEGIN
761 -- code_debug('sync_cat_item_page_entries called with params p_catalog_group '||p_catalog_group||' p_page_int_name '||p_page_int_name||' p_attr_group_name '||p_attr_group_name);
762 IF p_association_id IS NULL THEN
763
764 --
765 -- Bug 9949093. Meta data import is not creating proper
766 -- page entries for default pages. Added logic for deriving
767 -- correct catalog group id.
768 -- sreharih. Wed Apr 13 13:54:12 PDT 2011
769 --
770
771
772 IF p_catalog_group_id IS NULL THEN
773 get_catalog_group_id (p_catalog_group => p_catalog_group
774 ,x_catalog_group_id => l_catalog_group_id
775 );
776 ELSE
777 l_catalog_group_id := p_catalog_group_id;
778 END IF;
779
780 IF l_catalog_group_id IS NULL THEN
781 x_return_status := EGO_ITEM_PUB.G_RET_STS_ERROR;
782 x_msg_data := 'sync_cat_item_page_entries Catalog Category does not exist for '||p_catalog_group;
783 RETURN;
784 END IF;
785 get_attr_group_id (p_attr_group_name => p_attr_group_name
786 ,p_attr_group_type => p_attr_group_type
787 ,x_attr_group_id => l_attr_group_id
788 );
789
790 IF l_attr_group_id IS NULL THEN
791 x_return_status := EGO_ITEM_PUB.G_RET_STS_ERROR;
792 x_msg_data := 'sync_cat_item_page_entries attr group does not exist for '||p_attr_group_name;
793 RETURN;
794 END IF;
795 l_object_id := get_object_id ();
796 get_association_id (p_object_id => l_object_id
797 ,p_catalog_group_id => l_catalog_group_id
798 ,p_attr_group_id => l_attr_group_id
799 ,x_association_id => l_association_id
800 );
801 ELSE
802 l_association_id := p_association_id;
803 END IF;
804 IF l_association_id IS NULL THEN
805 x_return_status := EGO_ITEM_PUB.G_RET_STS_ERROR;
806 x_msg_data := 'sync_cat_item_page_entries association id does not exist for '||l_attr_group_id;
807 RETURN;
808 END IF;
809 IF p_page_id IS NULL THEN
810 get_page_id (p_object_id => l_object_id
811 ,p_catalog_group_id => l_catalog_group_id
812 ,p_page_int_name => p_page_int_name
813 ,x_page_id => l_page_id
814 );
815
816 ELSE
817 l_page_id := p_page_id;
818 END IF;
819 IF l_page_id IS NULL THEN
820 x_return_status := EGO_ITEM_PUB.G_RET_STS_ERROR;
821 x_msg_data := 'sync_cat_item_page_entries page id does not exist for '||p_page_int_name;
822 RETURN;
823 END IF;
824
825 get_page_entry (p_page_id => l_page_id
826 ,p_association_id => l_association_id
827 ,p_catalog_group_id => l_catalog_group_id
828 ,x_sequence => l_sequence
829 );
830 IF l_sequence IS NULL THEN
831 -- sequence is ready to be allocated
832 EGO_EXT_FWK_PUB.Create_Page_Entry (
833 p_api_version => p_api_version
834 ,p_page_id => l_page_id
835 ,p_association_id => l_association_id
836 ,p_sequence => p_sequence
837 ,p_classification_code => TO_CHAR(l_catalog_group_id)
838 -- ,p_init_msg_list IN VARCHAR2 := fnd_api.g_FALSE
839 ,p_commit => p_commit
840 ,x_return_status => x_return_status
841 ,x_errorcode => l_errorcode
842 ,x_msg_count => x_msg_count
843 ,x_msg_data => x_msg_data
844 );
845 ELSIF l_sequence = p_sequence THEN
846 -- nothing needs to be done
847 NULL;
848 ELSE
849 -- sequence must be updated
850 EGO_EXT_FWK_PUB.Update_Page_Entry (
851 p_api_version => p_api_version
852 ,p_page_id => l_page_id
853 ,p_new_association_id => l_association_id
854 ,p_old_association_id => l_association_id
855 ,p_sequence => p_sequence
856 -- ,p_init_msg_list IN VARCHAR2 := fnd_api.g_FALSE
857 ,p_commit => p_commit
858 ,x_return_status => x_return_status
859 ,x_errorcode => l_errorcode
860 ,x_msg_count => x_msg_count
861 ,x_msg_data => x_msg_data
862 );
863 END IF;
864 x_return_status := NVL(x_return_status,EGO_ITEM_PUB.G_RET_STS_SUCCESS);
865 EXCEPTION
866 WHEN OTHERS THEN
867 code_debug('sync_cat_item_page_entries returning EXCEPTION '||SQLERRM);
868 x_return_status := EGO_ITEM_PUB.G_RET_STS_UNEXP_ERROR;
869 x_msg_data := SQLERRM;
870 END sync_cat_item_page_entries;
871
872
873 END EGO_UPLOAD_PUB;