[Home] [Help]
PACKAGE BODY: APPS.EGO_UPLOAD_PUB
Source
1 PACKAGE BODY EGO_UPLOAD_PUB AS
2 /* $Header: EGOPUPLB.pls 120.0 2006/05/26 12:45:55 srajapar noship $ */
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 l_data_level VARCHAR2(20) := 'ITEM_LEVEL';
118 l_application_id NUMBER := 431;
119 l_attr_group_type VARCHAR2(40) := 'EGO_MASTER_ITEMS';
120 l_page_desc VARCHAR2(80) := 'Auto-generated operational attribute page';
121 l_page_id NUMBER;
122 l_association_id NUMBER;
123 l_pages_array EGO_COL_NAME_VALUE_PAIR_ARRAY;
124 l_page_entry_array EGO_COL_NAME_VALUE_PAIR_ARRAY;
125 -- l_name_value_obj EGO_COL_NAME_VALUE_PAIR_OBJ;
126 l_return_status VARCHAR2(1);
127 l_msg_count NUMBER;
128 l_msg_data VARCHAR2(4000);
129
130 BEGIN
131 l_object_id := get_object_id();
132 for k in 1..2 LOOP
133 IF K = 1 THEN
134 l_pages_array := EGO_COL_NAME_VALUE_PAIR_ARRAY (
135 EGO_COL_NAME_VALUE_PAIR_OBJ('10','Physical Attributes'),
136 EGO_COL_NAME_VALUE_PAIR_OBJ('20','Sales and Order Management'),
137 EGO_COL_NAME_VALUE_PAIR_OBJ('30','Planning'),
138 EGO_COL_NAME_VALUE_PAIR_OBJ('40','Purchasing'),
139 EGO_COL_NAME_VALUE_PAIR_OBJ('50','Inventory/WMS'),
140 EGO_COL_NAME_VALUE_PAIR_OBJ('60','Manufacturing')
141 );
142 ELSE
143 l_pages_array := EGO_COL_NAME_VALUE_PAIR_ARRAY (
144 EGO_COL_NAME_VALUE_PAIR_OBJ('70','Service')
145 );
146 END IF;
147 FOR i IN l_pages_array.FIRST .. l_pages_array.LAST LOOP
148 sync_cat_item_pages (
149 p_api_version => 1.0
150 ,p_commit => 'F'
151 ,p_catalog_group_id => p_catalog_group_id
152 ,p_catalog_group => NULL
153 ,p_data_level => l_data_level
154 ,p_page_int_name => l_pages_array(i).value
155 ,p_name => l_pages_array(i).value
156 ,p_desc => l_page_desc
157 ,p_sequence => l_pages_array(i).name
158 ,x_page_id => l_page_id
159 ,x_return_status => l_return_status
160 ,x_msg_count => l_msg_count
161 ,x_msg_data => l_msg_data
162 );
163 IF l_pages_array(i).name = 10 THEN
164 l_page_entry_array := EGO_COL_NAME_VALUE_PAIR_ARRAY (
165 EGO_COL_NAME_VALUE_PAIR_OBJ('10','PhysicalAttributes')
166 );
167 ELSIF l_pages_array(i).name = 20 THEN
168 l_page_entry_array := EGO_COL_NAME_VALUE_PAIR_ARRAY (
169 EGO_COL_NAME_VALUE_PAIR_OBJ('10','OrderManagement'),
170 EGO_COL_NAME_VALUE_PAIR_OBJ('20','Invoicing'),
171 EGO_COL_NAME_VALUE_PAIR_OBJ('30','WebOption')
172 );
173 ELSIF l_pages_array(i).name = 30 THEN
174 l_page_entry_array := EGO_COL_NAME_VALUE_PAIR_ARRAY (
175 EGO_COL_NAME_VALUE_PAIR_OBJ('10','GeneralPlanning'),
176 EGO_COL_NAME_VALUE_PAIR_OBJ('20','MPSMRPPlanning'),
177 EGO_COL_NAME_VALUE_PAIR_OBJ('30','LeadTimes')
178 );
179 ELSIF l_pages_array(i).name = 40 THEN
180 l_page_entry_array := EGO_COL_NAME_VALUE_PAIR_ARRAY (
181 EGO_COL_NAME_VALUE_PAIR_OBJ('10','Purchasing'),
182 EGO_COL_NAME_VALUE_PAIR_OBJ('20','Receiving')
183 );
184 ELSIF l_pages_array(i).name = 50 THEN
185 l_page_entry_array := EGO_COL_NAME_VALUE_PAIR_ARRAY (
186 EGO_COL_NAME_VALUE_PAIR_OBJ('10','Inventory')
187 );
188 ELSIF l_pages_array(i).name = 60 THEN
189 l_page_entry_array := EGO_COL_NAME_VALUE_PAIR_ARRAY (
190 EGO_COL_NAME_VALUE_PAIR_OBJ('10','BillofMaterials'),
191 EGO_COL_NAME_VALUE_PAIR_OBJ('20','Costing'),
192 EGO_COL_NAME_VALUE_PAIR_OBJ('30','WorkInProgress'),
193 EGO_COL_NAME_VALUE_PAIR_OBJ('40','ProcessManufacturing')
194 );
195 ELSIF l_pages_array(i).name = 70 THEN
196 l_page_entry_array := EGO_COL_NAME_VALUE_PAIR_ARRAY (
197 EGO_COL_NAME_VALUE_PAIR_OBJ('10','AssetManagement'),
198 EGO_COL_NAME_VALUE_PAIR_OBJ('20','Service')
199 );
200 END IF;
201 FOR j in l_page_entry_array.FIRST .. l_page_entry_array.LAST LOOP
202 sync_cat_attr_grp_assoc (
203 p_api_version => 1.0
204 ,p_commit => 'F'
205 ,p_catalog_group_id => p_catalog_group_id
206 ,p_catalog_group => NULL
207 ,p_data_level => l_data_level
208 ,p_attr_group_name => l_page_entry_array(j).value
209 ,p_attr_group_type => l_attr_group_type
210 ,p_enabled_flag => 'Y'
211 ,x_association_id => l_association_id
212 ,x_return_status => l_return_status
213 ,x_msg_count => l_msg_count
214 ,x_msg_data => l_msg_data
215 );
216 sync_cat_item_page_entries (
217 p_api_version => 1.0
218 ,p_commit => 'F'
219 ,p_catalog_group => NULL
220 ,p_page_id => l_page_id
221 ,p_page_int_name => NULL
222 ,p_attr_group_name => NULL
223 ,p_attr_group_type => NULL
224 ,p_sequence => l_page_entry_array(j).name
225 ,p_association_id => l_association_id
226 ,x_return_status => l_return_status
227 ,x_msg_count => l_msg_count
228 ,x_msg_data => l_msg_data
229 );
230 END LOOP;
231 l_page_entry_array := NULL;
232 END LOOP;
233 l_pages_array := NULL;
234 END LOOP;
235 EXCEPTION
236 WHEN OTHERS THEN
237 code_debug('createBaseAttributePages raise EXCEPTION '||SQLERRM);
238
239 END createBaseAttributePages;
240
241 --------------------------------------------------------
242
243 Procedure Sync_Catalog_Group (
244 p_api_version IN NUMBER
245 ,p_commit IN VARCHAR2
246 ,p_catalog_group IN VARCHAR2
247 ,p_parent_catalog_group IN VARCHAR2
248 ,p_description IN VARCHAR2
249 ,p_template_name IN VARCHAR2
250 ,p_creation_allowed IN VARCHAR2
251 ,p_end_date IN DATE
252 ,p_owner IN VARCHAR2
253 ,p_extra_params IN EGO_COL_NAME_VALUE_PAIR_ARRAY DEFAULT NULL
254 ,x_catalog_group_id OUT NOCOPY NUMBER
255 ,x_return_status OUT NOCOPY VARCHAR2
256 ,x_msg_count OUT NOCOPY NUMBER
257 ,x_msg_data OUT NOCOPY VARCHAR2
258 ) IS
259 l_user_id NUMBER;
260 l_party_id NUMBER;
261 l_language VARCHAR2(30);
262 l_transaction_type VARCHAR2(30);
263 l_catalog_name VARCHAR2(100);
264 l_catalog_group_id NUMBER;
265 l_errorcode NUMBER;
266 l_grant_guid RAW(50);
267 BEGIN
268 code_debug('Sync_Catalog_Group called with params ');
269 -- code_debug('Sync_Catalog_Group p_catalog_group: '||p_catalog_group||' p_parent_catalog_group '||p_parent_catalog_group);
270 -- code_debug('Sync_Catalog_Group p_description: '||p_description||' p_template_name '||p_template_name);
271 -- code_debug('Sync_Catalog_Group p_creation_allowed: '||p_creation_allowed||' p_end_date '||p_end_date);
272 -- code_debug('Sync_Catalog_Group p_owner: '||p_owner);
273 -- mandatory param check
274 -- check if the catalog group exists, if so, please update else create new
275 -- if creating catalog category, then create roles as well
276 BEGIN
277 SELECT user_id, party_id
278 INTO l_user_id, l_party_id
279 from ego_user_v
280 where user_name = DECODE(p_owner,EGO_ITEM_PUB.G_ALL_USERS_PARTY_TYPE,'MFG',p_owner);
281 IF p_owner = EGO_ITEM_PUB.G_ALL_USERS_PARTY_TYPE THEN
282 l_party_id := -1000;
283 END IF;
284 EXCEPTION
285 WHEN OTHERS THEN
286 -- defaulting to MFG
287 SELECT user_id, party_id
288 INTO l_user_id, l_party_id
289 from ego_user_v
290 where user_name = 'MFG';
291 -- l_user_id := 1068;
292 -- l_party_id := 6530;
293 END;
294 get_catalog_group_id (p_catalog_group => p_catalog_group
295 ,x_catalog_group_id => l_catalog_group_id
296 );
297 IF l_catalog_group_id IS NULL THEN
298 l_transaction_type := EGO_ITEM_PUB.G_TTYPE_CREATE;
299 ELSE
300 l_transaction_type := EGO_ITEM_PUB.G_TTYPE_UPDATE;
301 END IF;
302 l_language := USERENV ('LANG');
303 code_debug('Sync_Catalog_Group calling EGO_ITEM_CATALOG_PUB.Process_Catalog_Group trans type '||l_transaction_type);
304 EGO_ITEM_CATALOG_PUB.Process_Catalog_Group
305 ( p_Catalog_Group_Name => p_catalog_group
306 , p_Parent_Catalog_Group_Name => p_parent_catalog_group
307 -- , p_Catalog_Group_Id IN NUMBER := NULL
308 -- , p_Parent_Catalog_Group_Id IN NUMBER := NULL
309 , p_Description => p_description
310 , p_Item_Creation_Allowed_Flag => p_creation_allowed
311 -- , p_Start_Effective_Date IN DATE := NULL
312 , p_Inactive_date => p_end_date
313 -- , p_Enabled_Flag IN VARCHAR2 := NULL
314 -- , p_Summary_Flag IN VARCHAR2 := NULL
315 -- , p_segment1 IN VARCHAR2 := NULL
316 -- , p_segment2 IN VARCHAR2 := NULL
317 -- , p_segment3 IN VARCHAR2 := NULL
318 -- , p_segment4 IN VARCHAR2 := NULL
319 -- , p_segment5 IN VARCHAR2 := NULL
320 -- , p_segment6 IN VARCHAR2 := NULL
321 -- , p_segment7 IN VARCHAR2 := NULL
322 -- , p_segment8 IN VARCHAR2 := NULL
323 -- , p_segment9 IN VARCHAR2 := NULL
324 -- , p_segment10 IN VARCHAR2 := NULL
325 -- , p_segment11 IN VARCHAR2 := NULL
326 -- , p_segment12 IN VARCHAR2 := NULL
327 -- , p_segment13 IN VARCHAR2 := NULL
328 -- , p_segment14 IN VARCHAR2 := NULL
329 -- , p_segment15 IN VARCHAR2 := NULL
330 -- , p_segment16 IN VARCHAR2 := NULL
331 -- , p_segment17 IN VARCHAR2 := NULL
332 -- , p_segment18 IN VARCHAR2 := NULL
333 -- , p_segment19 IN VARCHAR2 := NULL
334 -- , p_segment20 IN VARCHAR2 := NULL
335 -- , Attribute_category IN VARCHAR2 := NULL
336 -- , Attribute1 IN VARCHAR2 := NULL
337 -- , Attribute2 IN VARCHAR2 := NULL
338 -- , Attribute3 IN VARCHAR2 := NULL
339 -- , Attribute4 IN VARCHAR2 := NULL
340 -- , Attribute5 IN VARCHAR2 := NULL
341 -- , Attribute6 IN VARCHAR2 := NULL
342 -- , Attribute7 IN VARCHAR2 := NULL
343 -- , Attribute8 IN VARCHAR2 := NULL
344 -- , Attribute9 IN VARCHAR2 := NULL
345 -- , Attribute10 IN VARCHAR2 := NULL
346 -- , Attribute11 IN VARCHAR2 := NULL
347 -- , Attribute12 IN VARCHAR2 := NULL
348 -- , Attribute13 IN VARCHAR2 := NULL
349 -- , Attribute14 IN VARCHAR2 := NULL
350 -- , Attribute15 IN VARCHAR2 := NULL
351 , p_User_id => l_user_id
352 , p_Language_Code => l_language
353 , p_Transaction_Type => l_transaction_type
354 , x_Return_Status => x_return_status
355 , x_msg_count => x_msg_count
356 -- , p_debug IN VARCHAR2 := 'N'
357 -- , p_output_dir IN VARCHAR2 := NULL
358 -- , p_debug_filename IN VARCHAR2 := 'Ego_Catalog_Grp.log'
359 , x_catalog_group_id => x_catalog_group_id
360 , x_catalog_group_name => l_catalog_name
361 );
362 -- code_debug('Sync_Catalog_Group returning EGO_ITEM_CATALOG_PUB.Process_Catalog_Group with status '||x_Return_Status);
363 x_catalog_group_id := NVL(x_catalog_group_id, l_catalog_group_id);
364 x_return_status := NVL(x_return_status,EGO_ITEM_PUB.G_RET_STS_SUCCESS);
365 IF x_return_status = EGO_ITEM_PUB.G_RET_STS_SUCCESS AND
366 l_transaction_type = EGO_ITEM_PUB.G_TTYPE_CREATE THEN
367 IF p_parent_catalog_group IS NULL THEN
368 createBaseAttributePages(p_catalog_group_id => x_catalog_group_id
369 ,x_return_status => x_return_status
370 );
371 END IF;
372 EGO_SECURITY_PUB.grant_role_guid(
373 p_api_version => p_api_version
374 , p_role_name => 'EGO_CATALOG_GROUP_USER'
375 , p_object_name => 'EGO_CATALOG_GROUP'
376 , p_instance_type => EGO_ITEM_PUB.G_INSTANCE_TYPE_INSTANCE
377 , p_instance_set_id => NULL
378 , p_instance_pk1_value => x_catalog_group_id
379 , p_instance_pk2_value => NULL
380 , p_instance_pk3_value => NULL
381 , p_instance_pk4_value => NULL
382 , p_instance_pk5_value => NULL
383 , p_party_id => l_party_id
384 , p_start_date => NULL
385 , p_end_date => NULL
386 , x_return_status => x_return_status
387 , x_errorcode => l_errorcode
388 , x_grant_guid => l_grant_guid
389 );
390 IF FND_API.TO_BOOLEAN(x_return_status) THEN
391 x_return_status := EGO_ITEM_PUB.G_RET_STS_SUCCESS;
392 ELSE
393 x_return_status := EGO_ITEM_PUB.G_RET_STS_ERROR;
394 END IF;
395 END IF;
396 IF FND_API.TO_BOOLEAN(p_commit) AND
397 x_return_status = EGO_ITEM_PUB.G_RET_STS_SUCCESS THEN
398 COMMIT WORK;
399 END IF;
400 -- code_debug('Sync_Catalog_Group returning with status '||x_return_status);
401 EXCEPTION
402 WHEN OTHERS THEN
403 code_debug('Sync_Catalog_Group returning EXCEPTION '||SQLERRM);
404 x_return_status := EGO_ITEM_PUB.G_RET_STS_UNEXP_ERROR;
405 x_msg_data := SQLERRM;
406 END Sync_Catalog_Group;
407
408 --------------------------------------------------------
409 PROCEDURE sync_cat_attr_grp_assoc (
410 p_api_version IN NUMBER
411 ,p_commit IN VARCHAR2
412 ,p_catalog_group_id IN NUMBER
413 ,p_catalog_group IN VARCHAR2
414 ,p_data_level IN VARCHAR2
415 ,p_attr_group_name IN VARCHAR2
416 ,p_attr_group_type IN VARCHAR2
417 ,p_enabled_flag IN VARCHAR2
418 ,p_extra_params IN EGO_COL_NAME_VALUE_PAIR_ARRAY DEFAULT NULL
419 ,x_association_id OUT NOCOPY NUMBER
420 ,x_return_status OUT NOCOPY VARCHAR2
421 ,x_msg_count OUT NOCOPY NUMBER
422 ,x_msg_data OUT NOCOPY VARCHAR2
423 ) IS
424 l_catalog_group_id NUMBER;
425 l_attr_group_id NUMBER;
426 l_object_id NUMBER;
427 l_association_id NUMBER;
428 l_errorcode NUMBER;
429 BEGIN
430 -- code_debug('sync_cat_attr_grp_assoc called with params p_attr_group_name '||p_attr_group_name);
431 IF p_catalog_group_id IS NULL THEN
432 get_catalog_group_id (p_catalog_group => p_catalog_group
433 ,x_catalog_group_id => l_catalog_group_id
434 );
435 ELSE
436 l_catalog_group_id := p_catalog_group_id;
437 END IF;
438 IF l_catalog_group_id IS NULL THEN
439 x_return_status := EGO_ITEM_PUB.G_RET_STS_ERROR;
440 x_msg_data := 'sync_cat_attr_grp_assoc Catalog Category does not exist for '||p_catalog_group;
441 RETURN;
442 END IF;
443 get_attr_group_id (p_attr_group_name => p_attr_group_name
444 ,p_attr_group_type => p_attr_group_type
445 ,x_attr_group_id => l_attr_group_id
446 );
447 IF l_attr_group_id IS NULL THEN
448 x_return_status := EGO_ITEM_PUB.G_RET_STS_ERROR;
449 x_msg_data := 'sync_cat_attr_grp_assoc attr group does not exist for '||p_attr_group_name;
450 RETURN;
451 END IF;
452 l_object_id := get_object_id ();
453 get_association_id (p_object_id => l_object_id
454 ,p_catalog_group_id => l_catalog_group_id
455 ,p_attr_group_id => l_attr_group_id
456 ,x_association_id => x_association_id
457 );
458 IF x_association_id IS NULL THEN
459 EGO_EXT_FWK_PUB.Create_Association (
460 p_api_version => p_api_version
461 ,p_association_id => NULL
462 ,p_object_id => l_object_id
463 ,p_classification_code => TO_CHAR(l_catalog_group_id)
464 ,p_data_level => p_data_level
465 ,p_attr_group_id => l_attr_group_id
466 ,p_enabled_flag => p_enabled_flag
467 ,p_view_privilege_id => NULL --ignored for now
468 ,p_edit_privilege_id => NULL --ignored for now
469 -- ,p_init_msg_list IN VARCHAR2 := fnd_api.g_FALSE
470 ,p_commit => p_commit
471 ,x_association_id => x_association_id
472 ,x_return_status => x_return_status
473 ,x_errorcode => l_errorcode
474 ,x_msg_count => x_msg_count
475 ,x_msg_data => x_msg_data
476 );
477 ELSE
478 EGO_EXT_FWK_PUB. Update_Association (
479 p_api_version => p_api_version
480 ,p_association_id => x_association_id
481 ,p_enabled_flag => p_enabled_flag
482 ,p_view_privilege_id => NULL --ignored for now
483 ,p_edit_privilege_id => NULL --ignored for now
484 -- ,p_init_msg_list IN VARCHAR2 := fnd_api.g_FALSE
485 ,p_commit => p_commit
486 ,x_return_status => x_return_status
487 ,x_errorcode => l_errorcode
488 ,x_msg_count => x_msg_count
489 ,x_msg_data => x_msg_data
490 );
491 END IF;
492 x_return_status := NVL(x_return_status,EGO_ITEM_PUB.G_RET_STS_SUCCESS);
493 -- code_debug('sync_cat_attr_grp_assoc returning with status '||x_return_status);
494
495 EXCEPTION
496 WHEN OTHERS THEN
497 code_debug('sync_cat_attr_grp_assoc returning EXCEPTION '||SQLERRM);
498 x_return_status := EGO_ITEM_PUB.G_RET_STS_UNEXP_ERROR;
499 x_msg_data := SQLERRM;
500 END sync_cat_attr_grp_assoc;
501
502 --------------------------------------------------------
503 PROCEDURE sync_cat_item_pages (
504 p_api_version IN NUMBER
505 ,p_commit IN VARCHAR2
506 ,p_catalog_group_id IN NUMBER
507 ,p_catalog_group IN VARCHAR2
508 ,p_data_level IN VARCHAR2
509 ,p_page_int_name IN VARCHAR2
510 ,p_name IN VARCHAR2
511 ,p_desc IN VARCHAR2
512 ,p_sequence IN NUMBER
513 ,p_extra_params IN EGO_COL_NAME_VALUE_PAIR_ARRAY DEFAULT NULL
514 ,x_page_id OUT NOCOPY NUMBER
515 ,x_return_status OUT NOCOPY VARCHAR2
516 ,x_msg_count OUT NOCOPY NUMBER
517 ,x_msg_data OUT NOCOPY VARCHAR2
518 ) IS
519 l_catalog_group_id NUMBER;
520 l_attr_group_id NUMBER;
521 l_object_id NUMBER;
522 l_errorcode NUMBER;
523 BEGIN
524 -- code_debug('sync_cat_item_pages called with params p_page_int_name '||p_page_int_name);
525 IF p_catalog_group_id IS NULL THEN
526 get_catalog_group_id (p_catalog_group => p_catalog_group
527 ,x_catalog_group_id => l_catalog_group_id
528 );
529 ELSE
530 l_catalog_group_id := p_catalog_group_id;
531 END IF;
532 IF l_catalog_group_id IS NULL THEN
533 x_return_status := EGO_ITEM_PUB.G_RET_STS_ERROR;
534 x_msg_data := 'sync_cat_item_pages Catalog Category does not exist for '||p_catalog_group;
535 RETURN;
536 END IF;
537 l_object_id := get_object_id();
538 get_page_id (p_object_id => l_object_id
539 ,p_catalog_group_id => l_catalog_group_id
540 ,p_page_int_name => p_page_int_name
541 ,x_page_id => x_page_id
542 );
543 IF x_page_id IS NULL THEN
544 EGO_EXT_FWK_PUB.Create_Page (
545 p_api_version => p_api_version
546 ,p_page_id => NULL
547 ,p_object_id => l_object_id
548 ,p_classification_code => TO_CHAR(l_catalog_group_id)
549 ,p_data_level => p_data_level
550 ,p_internal_name => p_page_int_name
551 ,p_display_name => p_name
552 ,p_description => p_desc
553 ,p_sequence => p_sequence
554 -- ,p_init_msg_list IN VARCHAR2 := fnd_api.g_FALSE
555 ,p_commit => p_commit
556 ,x_page_id => x_page_id
557 ,x_return_status => x_return_status
558 ,x_errorcode => l_errorcode
559 ,x_msg_count => x_msg_count
560 ,x_msg_data => x_msg_data
561 );
562 ELSE
563 EGO_EXT_FWK_PUB.Update_Page (
564 p_api_version => p_api_version
565 ,p_page_id => x_page_id
566 ,p_internal_name => p_page_int_name
567 ,p_display_name => p_name
568 ,p_description => p_desc
569 ,p_sequence => p_sequence
570 -- ,p_init_msg_list IN VARCHAR2 := fnd_api.g_FALSE
571 ,p_commit => p_commit
572 -- ,p_is_nls_mode IN VARCHAR2 DEFAULT FND_API.G_FALSE
573 ,x_return_status => x_return_status
574 ,x_errorcode => l_errorcode
575 ,x_msg_count => x_msg_count
576 ,x_msg_data => x_msg_data
577 );
578 END IF;
579 x_return_status := NVL(x_return_status,EGO_ITEM_PUB.G_RET_STS_SUCCESS);
580 EXCEPTION
581 WHEN OTHERS THEN
582 code_debug('sync_cat_item_pages returning EXCEPTION '||SQLERRM);
583 x_return_status := EGO_ITEM_PUB.G_RET_STS_UNEXP_ERROR;
584 x_msg_data := SQLERRM;
585 END sync_cat_item_pages;
586
587 --------------------------------------------------------
588 PROCEDURE sync_cat_item_page_entries (
589 p_api_version IN NUMBER
590 ,p_commit IN VARCHAR2
591 ,p_catalog_group IN VARCHAR2
592 ,p_page_id IN NUMBER
593 ,p_page_int_name IN VARCHAR2
594 ,p_attr_group_name IN VARCHAR2
595 ,p_attr_group_type IN VARCHAR2
596 ,p_sequence IN NUMBER
597 ,p_association_id IN NUMBER
598 ,p_extra_params IN EGO_COL_NAME_VALUE_PAIR_ARRAY DEFAULT NULL
599 ,x_return_status OUT NOCOPY VARCHAR2
600 ,x_msg_count OUT NOCOPY NUMBER
601 ,x_msg_data OUT NOCOPY VARCHAR2
602 ) IS
603 l_catalog_group_id NUMBER;
604 l_attr_group_id NUMBER;
605 l_object_id NUMBER;
606 l_association_id NUMBER;
607 l_page_id NUMBER;
608 l_errorcode NUMBER;
609 l_sequence NUMBER;
610 BEGIN
611 -- 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);
612 IF p_association_id IS NULL THEN
613 get_catalog_group_id (p_catalog_group => p_catalog_group
614 ,x_catalog_group_id => l_catalog_group_id
615 );
616 IF l_catalog_group_id IS NULL THEN
617 x_return_status := EGO_ITEM_PUB.G_RET_STS_ERROR;
618 x_msg_data := 'sync_cat_item_page_entries Catalog Category does not exist for '||p_catalog_group;
619 RETURN;
620 END IF;
621 get_attr_group_id (p_attr_group_name => p_attr_group_name
622 ,p_attr_group_type => p_attr_group_type
623 ,x_attr_group_id => l_attr_group_id
624 );
625 IF l_attr_group_id IS NULL THEN
626 x_return_status := EGO_ITEM_PUB.G_RET_STS_ERROR;
627 x_msg_data := 'sync_cat_item_page_entries attr group does not exist for '||p_attr_group_name;
628 RETURN;
629 END IF;
630 l_object_id := get_object_id ();
631 get_association_id (p_object_id => l_object_id
632 ,p_catalog_group_id => l_catalog_group_id
633 ,p_attr_group_id => l_attr_group_id
634 ,x_association_id => l_association_id
635 );
636 ELSE
637 l_association_id := p_association_id;
638 END IF;
639 IF l_association_id IS NULL THEN
640 x_return_status := EGO_ITEM_PUB.G_RET_STS_ERROR;
641 x_msg_data := 'sync_cat_item_page_entries association id does not exist for '||l_attr_group_id;
642 RETURN;
643 END IF;
644 IF p_page_id IS NULL THEN
645 get_page_id (p_object_id => l_object_id
646 ,p_catalog_group_id => l_catalog_group_id
647 ,p_page_int_name => p_page_int_name
648 ,x_page_id => l_page_id
649 );
650 ELSE
651 l_page_id := p_page_id;
652 END IF;
653 IF l_page_id IS NULL THEN
654 x_return_status := EGO_ITEM_PUB.G_RET_STS_ERROR;
655 x_msg_data := 'sync_cat_item_page_entries page id does not exist for '||p_page_int_name;
656 RETURN;
657 END IF;
658
659 get_page_entry (p_page_id => l_page_id
660 ,p_association_id => l_association_id
661 ,p_catalog_group_id => l_catalog_group_id
662 ,x_sequence => l_sequence
663 );
664 IF l_sequence IS NULL THEN
665 -- sequence is ready to be allocated
666 EGO_EXT_FWK_PUB.Create_Page_Entry (
667 p_api_version => p_api_version
668 ,p_page_id => l_page_id
669 ,p_association_id => l_association_id
670 ,p_sequence => p_sequence
671 ,p_classification_code => TO_CHAR(l_catalog_group_id)
672 -- ,p_init_msg_list IN VARCHAR2 := fnd_api.g_FALSE
673 ,p_commit => p_commit
674 ,x_return_status => x_return_status
675 ,x_errorcode => l_errorcode
676 ,x_msg_count => x_msg_count
677 ,x_msg_data => x_msg_data
678 );
679 ELSIF l_sequence = p_sequence THEN
680 -- nothing needs to be done
681 NULL;
682 ELSE
683 -- sequence must be updated
684 EGO_EXT_FWK_PUB.Update_Page_Entry (
685 p_api_version => p_api_version
686 ,p_page_id => l_page_id
687 ,p_new_association_id => l_association_id
688 ,p_old_association_id => l_association_id
689 ,p_sequence => p_sequence
690 -- ,p_init_msg_list IN VARCHAR2 := fnd_api.g_FALSE
691 ,p_commit => p_commit
692 ,x_return_status => x_return_status
693 ,x_errorcode => l_errorcode
694 ,x_msg_count => x_msg_count
695 ,x_msg_data => x_msg_data
696 );
697 END IF;
698 x_return_status := NVL(x_return_status,EGO_ITEM_PUB.G_RET_STS_SUCCESS);
699 EXCEPTION
700 WHEN OTHERS THEN
701 code_debug('sync_cat_item_page_entries returning EXCEPTION '||SQLERRM);
702 x_return_status := EGO_ITEM_PUB.G_RET_STS_UNEXP_ERROR;
703 x_msg_data := SQLERRM;
704 END sync_cat_item_page_entries;
705
706
707 END EGO_UPLOAD_PUB;