[Home] [Help]
PACKAGE BODY: APPS.HZ_MIXNM_REGISTRY_PUB
Source
1 PACKAGE BODY HZ_MIXNM_REGISTRY_PUB AS
2 /*$Header: ARHXREGB.pls 120.9.12020000.2 2012/11/27 12:36:32 rgokavar ship $ */
3
4 --------------------------------------
5 -- declaration of private global varibles
6 --------------------------------------
7
8 TYPE VARCHARList IS TABLE OF VARCHAR2(30);
9 TYPE INDEXIDList IS TABLE OF NUMBER(15) INDEX BY BINARY_INTEGER;
10
11 G_DEBUG_COUNT NUMBER := 0;
12 --G_DEBUG BOOLEAN := FALSE;
13
14 -- org attribute name and type list
15
16 G_ORG_ATTRIBUTE_NAME_TAB VARCHARList;
17 G_ORG_ATTRIBUTE_TYPE_TAB VARCHARList;
18 G_ORG_DEN_ATTRIBUTE_NAME_TAB VARCHARList;
19
20 -- person attribute name and type list
21
22 G_PER_ATTRIBUTE_NAME_TAB VARCHARList;
23 G_PER_ATTRIBUTE_TYPE_TAB VARCHARList;
24 G_PER_DEN_ATTRIBUTE_NAME_TAB VARCHARList;
25
26 -- attribute grouping. The attribute name MUST be in alphanumeric order
27
28 G_PERSON_NAME_GROUP VARCHARList := VARCHARList(
29 'MIDDLE_NAME_PHONETIC', 'PERSON_FIRST_NAME', 'PERSON_FIRST_NAME_PHONETIC',
30 'PERSON_LAST_NAME', 'PERSON_LAST_NAME_PHONETIC', 'PERSON_MIDDLE_NAME', 'PERSON_INITIALS');
31 G_PERSON_NAME_ID_GROUP INDEXIDList;
32
33 G_PERSON_IDENTIFIER_GROUP VARCHARList := VARCHARList(
34 'PERSON_IDENTIFIER', 'PERSON_IDEN_TYPE');
35 G_PERSON_IDENTIFIER_ID_GROUP INDEXIDList;
36
37 G_HQ_BRANCH_IND_GROUP VARCHARList := VARCHARList(
38 'BRANCH_FLAG', 'HQ_BRANCH_IND');
39 G_HQ_BRANCH_IND_ID_GROUP INDEXIDList;
40
41 G_ORGANIZATION_NAME_GROUP VARCHARList := VARCHARList(
42 'ORGANIZATION_NAME', 'ORGANIZATION_NAME_PHONETIC');
43 G_ORGANIZATION_NAME_ID_GROUP INDEXIDList;
44
45 G_LOCAL_ACTIVITY_CODE_GROUP VARCHARList := VARCHARList(
46 'LOCAL_ACTIVITY_CODE', 'LOCAL_ACTIVITY_CODE_TYPE');
47 G_LOCAL_ACTIVITY_CODE_ID_GROUP INDEXIDList;
48
49 G_LOCAL_BUS_IDEN_GROUP VARCHARList := VARCHARList(
50 'LOCAL_BUS_IDENTIFIER', 'LOCAL_BUS_IDEN_TYPE');
51 G_LOCAL_BUS_IDEN_ID_GROUP INDEXIDList;
52
53 G_SIC_CODE_GROUP VARCHARList := VARCHARList(
54 'SIC_CODE', 'SIC_CODE_TYPE');
55 G_SIC_CODE_ID_GROUP INDEXIDList;
56
57 G_DUNS_NUMBER_GROUP VARCHARList := VARCHARList(
58 'DISPLAYED_DUNS_PARTY_ID', 'DUNS_NUMBER_C', 'ENQUIRY_DUNS');
59 G_DUNS_NUMBER_ID_GROUP INDEXIDList;
60
61 G_CEO_GROUP VARCHARList := VARCHARList(
62 'CEO_NAME', 'CEO_TITLE');
63 G_CEO_ID_GROUP INDEXIDList;
64
65 G_PRINCIPAL_GROUP VARCHARList := VARCHARList(
66 'PRINCIPAL_NAME', 'PRINCIPAL_TITLE');
67 G_PRINCIPAL_ID_GROUP INDEXIDList;
68
69 G_MINORITY_OWNED_GROUP VARCHARList := VARCHARList(
70 'MINORITY_OWNED_IND', 'MINORITY_OWNED_TYPE');
71 G_MINORITY_OWNED_ID_GROUP INDEXIDList;
72
73 G_PERSON_ENTITY CONSTANT VARCHAR2(30) := 'HZ_PERSON_PROFILES';
74 G_ORG_ENTITY CONSTANT VARCHAR2(30) := 'HZ_ORGANIZATION_PROFILES';
75
76 --------------------------------------
77 -- declaration of private procedures and functions
78 --------------------------------------
79
80 /*PROCEDURE enable_debug;
81
82 PROCEDURE disable_debug;
83 */
84
85 PROCEDURE do_AddEntityAttribute (
86 p_entity_attribute_rec IN ENTITY_ATTRIBUTE_REC_TYPE,
87 p_data_source_tab IN DATA_SOURCE_TBL,
88 x_entity_attr_id OUT NOCOPY NUMBER,
89 x_return_status IN OUT NOCOPY VARCHAR2
90 );
91
92 PROCEDURE Find_NameListInAGroup (
93 p_create_update_flag IN VARCHAR2 := NULL,
94 p_entity_name IN VARCHAR2,
95 p_attribute_name IN VARCHAR2,
96 x_group_name OUT NOCOPY VARCHAR2,
97 x_group OUT NOCOPY VARCHARList,
98 x_group_id OUT NOCOPY INDEXIDList
99 );
100
101 PROCEDURE Set_EntityAttrIdInAGroup (
102 p_group_name IN VARCHAR2,
103 p_index IN NUMBER,
104 p_entity_attr_id IN NUMBER
105 );
106
107 PROCEDURE Validate_EntityAttribute (
108 p_entity_attribute_rec IN ENTITY_ATTRIBUTE_REC_TYPE,
109 p_data_source_tab IN DATA_SOURCE_TBL,
110 x_return_status IN OUT NOCOPY VARCHAR2
111 );
112
113 PROCEDURE Validate_Attribute (
114 p_entity_name IN VARCHAR2,
115 p_attribute_name IN VARCHAR2,
116 x_return_status IN OUT NOCOPY VARCHAR2
117 );
118
119 FUNCTION getIndex (
120 p_list IN VARCHARList,
121 p_value IN VARCHAR2
122 ) RETURN NUMBER;
123
124 PROCEDURE db_InsertEntityAttribute (
125 p_entity_attribute_rec IN ENTITY_ATTRIBUTE_REC_TYPE,
126 p_group_name IN VARCHAR2,
127 x_entity_attr_id OUT NOCOPY NUMBER
128 );
129
130 PROCEDURE db_InsertDataSource (
131 p_new_item_flag IN VARCHAR2,
132 p_entity_attr_id IN NUMBER,
133 p_data_source_tab IN DATA_SOURCE_TBL
134 );
135
136 PROCEDURE LoadGroupId (
137 p_entity_name IN VARCHAR2,
138 p_name_group IN VARCHARList,
139 p_id_group IN OUT NOCOPY INDEXIDList
140 );
141
142 --------------------------------------
143 -- private procedures and functions
144 --------------------------------------
145
146 /**
147 * PRIVATE PROCEDURE enable_debug
148 *
149 * DESCRIPTION
150 * Turn on debug mode.
151 *
152 * EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
153 * HZ_UTILITY_V2PUB.enable_debug
154 *
155 * MODIFICATION HISTORY
156 *
157 * 07-23-2001 Jianying Huang o Created.
158 *
159 */
160
161 /*PROCEDURE enable_debug IS
162 BEGIN
163 G_DEBUG_COUNT := G_DEBUG_COUNT + 1;
164
165 IF G_DEBUG_COUNT = 1 THEN
166 IF FND_PROFILE.value( 'HZ_API_FILE_DEBUG_ON' ) = 'Y' OR
167 FND_PROFILE.value( 'HZ_API_DBMS_DEBUG_ON' ) = 'Y'
168 THEN
169 HZ_UTILITY_V2PUB.enable_debug;
170 G_DEBUG := TRUE;
171 END IF;
172 END IF;
173 END enable_debug;
174 */
175
176 /**
177 * PRIVATE PROCEDURE disable_debug
178 *
179 * DESCRIPTION
180 * Turn off debug mode.
181 *
182 * EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
183 * HZ_UTILITY_V2PUB.disable_debug
184 *
185 * MODIFICATION HISTORY
186 *
187 * 07-23-2001 Jianying Huang o Created.
188 *
189 */
190
191 /*PROCEDURE disable_debug IS
192 BEGIN
193 IF G_DEBUG THEN
194 G_DEBUG_COUNT := G_DEBUG_COUNT - 1;
195
196 IF G_DEBUG_COUNT = 0 THEN
197 HZ_UTILITY_V2PUB.disable_debug;
198 G_DEBUG := FALSE;
199 END IF;
200 END IF;
201 END disable_debug;
202 */
203
204 /**
205 * PRIVATE PROCEDURE LoadGroupId
206 *
207 * DESCRIPTION
208 * Private procedure to load Ids for the attributes in a group.
209 *
210 * EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
211 *
212 * ARGUMENTS
213 * IN:
214 * p_entity_name Entity name.
215 * p_attribute_name Attribute name.
216 * IN OUT:
217 * p_id_group Attribute id list in a group.
218 *
219 * NOTES
220 *
221 * MODIFICATION HISTORY
222 *
223 * 03-01-2004 Jianying Huang o Created.
224 *
225 */
226
227 PROCEDURE LoadGroupId (
228 p_entity_name IN VARCHAR2,
229 p_name_group IN VARCHARList,
230 p_id_group IN OUT NOCOPY INDEXIDList
231 ) IS
232
233 CURSOR c_entity (
234 p_entity_name VARCHAR2,
235 p_attribute_name VARCHAR2
236 ) IS
237 SELECT entity_attr_id
238 FROM hz_entity_attributes
239 WHERE entity_name = p_entity_name
240 AND attribute_name = p_attribute_name;
241
242 l_entity_attr_id NUMBER;
243
244 BEGIN
245
246 FOR i IN 1..p_name_group.COUNT LOOP
247 OPEN c_entity(p_entity_name, p_name_group(i));
248 FETCH c_entity INTO l_entity_attr_id;
249 IF c_entity%FOUND THEN
250 p_id_group(i) := l_entity_attr_id;
251 END IF;
252 CLOSE c_entity;
253 END LOOP;
254
255 END LoadGroupId;
256
257 /**
258 * PRIVATE PROCEDURE do_AddEntityAttribute
259 *
260 * DESCRIPTION
261 * Private procedure to add entity / attribute into the dictionary.
262 *
263 * EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
264 *
265 * ARGUMENTS
266 * IN:
267 * p_entity_attribute_rec Entity Attribute record.
268 * p_data_source_tab PL/SQL table for data source setup.
269 * IN/OUT:
270 * x_return_status Return status after the call. The status can
271 * be FND_API.G_RET_STS_SUCCESS (success),
272 * FND_API.G_RET_STS_ERROR (error),
273 * FND_API.G_RET_STS_UNEXP_ERROR (unexpected error).
274 * OUT:
275 * x_entity_attr_id Dictionary ID.
276 *
277 * NOTES
278 *
279 * MODIFICATION HISTORY
280 *
281 * 07-23-2001 Jianying Huang o Created.
282 * 12-12-2004 Rajib Ranjan Borah o SSM SST Integration and Extension.
283 * For other entities pass 'O' as p_new_item_flag.
284 *
285 */
286
287 PROCEDURE do_AddEntityAttribute (
288 p_entity_attribute_rec IN ENTITY_ATTRIBUTE_REC_TYPE,
289 p_data_source_tab IN DATA_SOURCE_TBL,
290 x_entity_attr_id OUT NOCOPY NUMBER,
291 x_return_status IN OUT NOCOPY VARCHAR2
292 ) IS
293
294 l_group_name VARCHAR2(30) := NULL;
295 l_group VARCHARList;
296 l_group_id INDEXIDList;
297 l_entity_attribute_rec ENTITY_ATTRIBUTE_REC_TYPE := p_entity_attribute_rec;
298 l_entity_attr_id NUMBER;
299 l_total NUMBER := 1;
300 l_create_update_flag VARCHAR2(1) := 'U';
301 l_debug_prefix VARCHAR2(30) := '';
302 l_new_item_flag VARCHAR2(1);
303 CURSOR c_entity (
304 p_entity_name VARCHAR2,
305 p_attribute_name VARCHAR2
306 ) IS
307 SELECT entity_attr_id
308 FROM hz_entity_attributes
309 WHERE entity_name = p_entity_name
310 AND ((attribute_name IS NULL AND
311 (p_attribute_name IS NULL OR
312 p_attribute_name = FND_API.G_MISS_CHAR)) OR
313 (attribute_name = p_attribute_name));
314
315 BEGIN
316
317 -- Debug info.
318 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
319 hz_utility_v2pub.debug(p_message=> 'do_AddEntityAttribute (+)',
320 p_prefix=>l_debug_prefix,
321 p_msg_level=>fnd_log.level_procedure);
322 END IF;
323
324 -- add the entity and / or attribute into the dictionary
325 OPEN c_entity(
326 p_entity_attribute_rec.entity_name,
327 p_entity_attribute_rec.attribute_name);
328 FETCH c_entity INTO x_entity_attr_id;
329
330 IF c_entity%NOTFOUND THEN
331 l_create_update_flag := 'C';
332 END IF;
333 CLOSE c_entity;
334
335 -- find the group the attribute belongs to.
336
337 IF p_entity_attribute_rec.entity_name IS NOT NULL AND
338 p_entity_attribute_rec.entity_name <> FND_API.G_MISS_CHAR AND
339 p_entity_attribute_rec.attribute_name IS NOT NULL AND
340 p_entity_attribute_rec.attribute_name <> FND_API.G_MISS_CHAR
341 THEN
342 Find_NameListInAGroup (
343 p_create_update_flag => l_create_update_flag,
344 p_entity_name => p_entity_attribute_rec.entity_name,
345 p_attribute_name => p_entity_attribute_rec.attribute_name,
346 x_group_name => l_group_name,
347 x_group => l_group,
348 x_group_id => l_group_id);
349
350 l_total := l_group.COUNT;
351 END IF;
352
353 IF l_create_update_flag = 'C' THEN
354 FOR i IN 1..l_total LOOP
355 l_entity_attribute_rec.attribute_name := l_group(i);
356
357 -- validate inputs
358 Validate_EntityAttribute (
359 p_entity_attribute_rec => l_entity_attribute_rec,
360 p_data_source_tab => p_data_source_tab,
361 x_return_status => x_return_status);
362
363 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
364 RAISE FND_API.G_EXC_ERROR;
365 END IF;
366
367 db_InsertEntityAttribute (
368 p_entity_attribute_rec => l_entity_attribute_rec,
369 p_group_name => l_group_name,
370 x_entity_attr_id => x_entity_attr_id);
371
372 IF l_total > 1 THEN
373 Set_EntityAttrIdInAGroup (
374 p_group_name => l_group_name,
375 p_index => i,
376 p_entity_attr_id => x_entity_attr_id);
377 END IF;
378
379 IF p_entity_attribute_rec.attribute_name IS NULL THEN
380 l_new_item_flag := 'O';
381 ELSE
382 l_new_item_flag := 'Y';
383 END IF;
384
385 -- add the data source.
386 db_InsertDataSource (
387 p_new_item_flag => l_new_item_flag,
388 p_entity_attr_id => x_entity_attr_id,
389 p_data_source_tab => p_data_source_tab);
390 END LOOP;
391 ELSE
392 FOR i IN 1..l_total LOOP
393 IF l_total > 1 THEN
394 l_entity_attr_id := l_group_id(i);
395 ELSE
396 l_entity_attr_id := x_entity_attr_id;
397 END IF;
398
399 IF p_entity_attribute_rec.attribute_name IS NULL THEN
400 l_new_item_flag := 'O';
401 ELSE
402 l_new_item_flag := 'N';
403 END IF;
404 -- add the data source.
405 db_InsertDataSource (
406 p_new_item_flag => l_new_item_flag,
407 p_entity_attr_id => l_entity_attr_id,
408 p_data_source_tab => p_data_source_tab);
409 END LOOP;
410 END IF;
411
412 -- Debug info.
413 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
414 hz_utility_v2pub.debug(p_message=>'do_AddEntityAttribute (-)',
415 p_prefix=>l_debug_prefix,
416 p_msg_level=>fnd_log.level_procedure);
417 END IF;
418
419 END do_AddEntityAttribute;
420
421 /**
422 * PRIVATE PROCEDURE Find_NameListInAGroup
423 *
424 * DESCRIPTION
425 * Private procedure to return the attribute list in a given group.
426 *
427 * EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
428 *
429 * ARGUMENTS
430 * IN:
431 * p_create_update_flag 'C' is for create.
432 * p_entity_name Entity name.
433 * p_attribute_name Attribute name.
434 * OUT:
435 * x_group_name Group name.
436 * x_group Attribute name list in a group.
437 * x_group_id Attribute id list in a group.
438 *
439 * NOTES
440 *
441 * MODIFICATION HISTORY
442 *
446
443 * 07-23-2001 Jianying Huang o Created.
444 *
445 */
447 PROCEDURE Find_NameListInAGroup (
448 p_create_update_flag IN VARCHAR2 := NULL,
449 p_entity_name IN VARCHAR2,
450 p_attribute_name IN VARCHAR2,
451 x_group_name OUT NOCOPY VARCHAR2,
452 x_group OUT NOCOPY VARCHARList,
453 x_group_id OUT NOCOPY INDEXIDList
454 ) IS
455 BEGIN
456
457 IF p_entity_name = G_PERSON_ENTITY THEN
458
459 IF p_attribute_name = 'PERSON_NAME' OR
460 getIndex(G_PERSON_NAME_GROUP, p_attribute_name) > 0
461 THEN
462 x_group_name := 'PERSON_NAME';
463 x_group := G_PERSON_NAME_GROUP;
464 IF p_create_update_flag = 'U' THEN
465 IF G_PERSON_NAME_GROUP.COUNT > G_PERSON_NAME_ID_GROUP.COUNT THEN
466 LoadGroupId(G_PERSON_ENTITY, x_group, G_PERSON_NAME_ID_GROUP);
467 END IF;
468 x_group_id := G_PERSON_NAME_ID_GROUP;
469 END IF;
470
471 ELSIF getIndex(G_PERSON_IDENTIFIER_GROUP, p_attribute_name) > 0 THEN
472 x_group_name := 'PERSON_IDENTIFIER';
473 x_group := G_PERSON_IDENTIFIER_GROUP;
474 IF p_create_update_flag = 'U' THEN
475 IF G_PERSON_IDENTIFIER_GROUP.COUNT > G_PERSON_IDENTIFIER_ID_GROUP.COUNT THEN
476 LoadGroupId(G_PERSON_ENTITY, x_group, G_PERSON_IDENTIFIER_ID_GROUP);
477 END IF;
478 x_group_id := G_PERSON_IDENTIFIER_ID_GROUP;
479 END IF;
480
481 ELSE
482 x_group_name := p_attribute_name;
483 x_group := VARCHARList();
484 x_group.EXTEND(1);
485 x_group(1) := p_attribute_name;
486 END IF;
487
488 ELSIF p_entity_name = G_ORG_ENTITY THEN
489
490 IF getIndex(G_HQ_BRANCH_IND_GROUP, p_attribute_name) > 0 THEN
491 x_group_name := 'HQ_BRANCH_IND';
492 x_group := G_HQ_BRANCH_IND_GROUP;
493 IF p_create_update_flag = 'U' THEN
494 IF G_HQ_BRANCH_IND_GROUP.COUNT > G_HQ_BRANCH_IND_ID_GROUP.COUNT THEN
495 LoadGroupId(G_ORG_ENTITY, x_group, G_HQ_BRANCH_IND_ID_GROUP);
496 END IF;
497 x_group_id := G_HQ_BRANCH_IND_ID_GROUP;
498 END IF;
499
500 ELSIF getIndex(G_ORGANIZATION_NAME_GROUP, p_attribute_name) > 0 THEN
501 x_group_name := 'ORGANIZATION_NAME';
502 x_group := G_ORGANIZATION_NAME_GROUP;
503 IF p_create_update_flag = 'U' THEN
504 IF G_ORGANIZATION_NAME_GROUP.COUNT > G_ORGANIZATION_NAME_ID_GROUP.COUNT THEN
505 LoadGroupId(G_ORG_ENTITY, x_group, G_ORGANIZATION_NAME_ID_GROUP);
506 END IF;
507 x_group_id := G_ORGANIZATION_NAME_ID_GROUP;
508 END IF;
509
510 ELSIF getIndex(G_LOCAL_ACTIVITY_CODE_GROUP, p_attribute_name) > 0 THEN
511 x_group_name := 'LOCAL_ACTIVITY_CODE';
512 x_group := G_LOCAL_ACTIVITY_CODE_GROUP;
513 IF p_create_update_flag = 'U' THEN
514 IF G_LOCAL_ACTIVITY_CODE_GROUP.COUNT > G_LOCAL_ACTIVITY_CODE_ID_GROUP.COUNT THEN
515 LoadGroupId(G_ORG_ENTITY, x_group, G_LOCAL_ACTIVITY_CODE_ID_GROUP);
516 END IF;
517 x_group_id := G_LOCAL_ACTIVITY_CODE_ID_GROUP;
518 END IF;
519
520 ELSIF getIndex(G_LOCAL_BUS_IDEN_GROUP, p_attribute_name) > 0 THEN
521 x_group_name := 'LOCAL_BUS_IDENTIFIER';
522 x_group := G_LOCAL_BUS_IDEN_GROUP;
523 IF p_create_update_flag = 'U' THEN
524 IF G_LOCAL_BUS_IDEN_GROUP.COUNT > G_LOCAL_BUS_IDEN_ID_GROUP.COUNT THEN
525 LoadGroupId(G_ORG_ENTITY, x_group, G_LOCAL_BUS_IDEN_ID_GROUP);
526 END IF;
527 x_group_id := G_LOCAL_BUS_IDEN_ID_GROUP;
528 END IF;
529
530 ELSIF getIndex(G_SIC_CODE_GROUP, p_attribute_name) > 0 THEN
531 x_group_name := 'SIC_CODE';
532 x_group := G_SIC_CODE_GROUP;
533 IF p_create_update_flag = 'U' THEN
534 IF G_SIC_CODE_GROUP.COUNT > G_SIC_CODE_ID_GROUP.COUNT THEN
535 LoadGroupId(G_ORG_ENTITY, x_group, G_SIC_CODE_ID_GROUP);
536 END IF;
537 x_group_id := G_SIC_CODE_ID_GROUP;
538 END IF;
539
540 ELSIF getIndex(G_DUNS_NUMBER_GROUP, p_attribute_name) > 0 THEN
541 x_group_name := 'DUNS_NUMBER_C';
542 x_group := G_DUNS_NUMBER_GROUP;
543 IF p_create_update_flag = 'U' THEN
544 IF G_DUNS_NUMBER_GROUP.COUNT > G_DUNS_NUMBER_ID_GROUP.COUNT THEN
545 LoadGroupId(G_ORG_ENTITY, x_group, G_DUNS_NUMBER_ID_GROUP);
546 END IF;
547 x_group_id := G_DUNS_NUMBER_ID_GROUP;
548 END IF;
549
550 ELSIF getIndex(G_CEO_GROUP, p_attribute_name) > 0 THEN
551 x_group_name := 'CEO_NAME';
552 x_group := G_CEO_GROUP;
553 IF p_create_update_flag = 'U' THEN
554 IF G_CEO_GROUP.COUNT > G_CEO_ID_GROUP.COUNT THEN
555 LoadGroupId(G_ORG_ENTITY, x_group, G_CEO_ID_GROUP);
556 END IF;
557 x_group_id := G_CEO_ID_GROUP;
558 END IF;
559
560 ELSIF getIndex(G_PRINCIPAL_GROUP, p_attribute_name) > 0 THEN
561 x_group_name := 'PRINCIPAL_NAME';
562 x_group := G_PRINCIPAL_GROUP;
563 IF p_create_update_flag = 'U' THEN
564 IF G_PRINCIPAL_GROUP.COUNT > G_PRINCIPAL_ID_GROUP.COUNT THEN
565 LoadGroupId(G_ORG_ENTITY, x_group, G_PRINCIPAL_ID_GROUP);
566 END IF;
567 x_group_id := G_PRINCIPAL_ID_GROUP;
568 END IF;
569
570 ELSIF getIndex(G_MINORITY_OWNED_GROUP, p_attribute_name) > 0 THEN
571 x_group_name := 'MINORITY_OWNED_IND';
572 x_group := G_MINORITY_OWNED_GROUP;
576 END IF;
573 IF p_create_update_flag = 'U' THEN
574 IF G_MINORITY_OWNED_GROUP.COUNT > G_MINORITY_OWNED_ID_GROUP.COUNT THEN
575 LoadGroupId(G_ORG_ENTITY, x_group, G_MINORITY_OWNED_ID_GROUP);
577 x_group_id := G_MINORITY_OWNED_ID_GROUP;
578 END IF;
579
580 ELSE
581 x_group_name := p_attribute_name;
582 x_group := VARCHARList();
583 x_group.EXTEND(1);
584 x_group(1) := p_attribute_name;
585 END IF;
586
587 END IF;
588
589 END Find_NameListInAGroup;
590
591 /**
592 * PRIVATE PROCEDURE Set_EntityAttrIdInAGroup
593 *
594 * DESCRIPTION
595 * Private procedure to set the attribute id in a group.
596 *
597 * EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
598 *
599 * ARGUMENTS
600 * IN:
601 * p_group_name Group name.
602 * p_index Index.
603 * p_entity_attr_id Attribute id.
604 *
605 * NOTES
606 *
607 * MODIFICATION HISTORY
608 *
609 * 07-23-2001 Jianying Huang o Created.
610 *
611 */
612
613 PROCEDURE Set_EntityAttrIdInAGroup (
614 p_group_name IN VARCHAR2,
615 p_index IN NUMBER,
616 p_entity_attr_id IN NUMBER
617 ) IS
618 BEGIN
619
620 IF p_group_name = 'PERSON_NAME' THEN
621 G_PERSON_NAME_ID_GROUP(p_index) := p_entity_attr_id;
622 ELSIF p_group_name = 'PERSON_IDENTIFIER' THEN
623 G_PERSON_IDENTIFIER_ID_GROUP(p_index) := p_entity_attr_id;
624 ELSIF p_group_name = 'HQ_BRANCH_IND' THEN
625 G_HQ_BRANCH_IND_ID_GROUP(p_index) := p_entity_attr_id;
626 ELSIF p_group_name = 'ORGANIZATION_NAME' THEN
627 G_ORGANIZATION_NAME_ID_GROUP(p_index) := p_entity_attr_id;
628 ELSIF p_group_name = 'LOCAL_ACTIVITY_CODE' THEN
629 G_LOCAL_ACTIVITY_CODE_ID_GROUP(p_index) := p_entity_attr_id;
630 ELSIF p_group_name = 'LOCAL_BUS_IDENTIFIER' THEN
631 G_LOCAL_BUS_IDEN_ID_GROUP(p_index) := p_entity_attr_id;
632 ELSIF p_group_name = 'SIC_CODE' THEN
633 G_SIC_CODE_ID_GROUP(p_index) := p_entity_attr_id;
634 ELSIF p_group_name = 'DUNS_NUMBER_C' THEN
635 G_DUNS_NUMBER_ID_GROUP(p_index) := p_entity_attr_id;
636 ELSIF p_group_name = 'CEO_NAME' THEN
637 G_CEO_ID_GROUP(p_index) := p_entity_attr_id;
638 ELSIF p_group_name = 'PRINCIPAL_NAME' THEN
639 G_PRINCIPAL_ID_GROUP(p_index) := p_entity_attr_id;
640 ELSIF p_group_name = 'MINORITY_OWNED_IND' THEN
641 G_MINORITY_OWNED_ID_GROUP(p_index) := p_entity_attr_id;
642 END IF;
643
644 END Set_EntityAttrIdInAGroup;
645
646 /**
647 * PRIVATE PROCEDURE Validate_EntityAttribute
648 *
649 * DESCRIPTION
650 * Private procedure to validate entity / attribute.
651 *
652 * EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
653 *
654 * ARGUMENTS
655 * IN:
656 * p_entity_attribute_rec Entity Attribute record.
657 * p_data_source_tab PL/SQL table for data source setup.
658 * IN/OUT:
659 * x_return_status Return status after the call. The status can
660 * be FND_API.G_RET_STS_SUCCESS (success),
661 * FND_API.G_RET_STS_ERROR (error),
662 * FND_API.G_RET_STS_UNEXP_ERROR (unexpected error).
663 * OUT:
664 *
665 * NOTES
666 *
667 * MODIFICATION HISTORY
668 *
669 * 07-23-2001 Jianying Huang o Created.
670 * 11-24-2004 Rajib Ranjan Borah o SSM SST Integration and Extension.
671 * Data Source will not be validated against lookup
672 * type CONTENT_SOURCE_TYPE.
673 */
674
675 PROCEDURE Validate_EntityAttribute (
676 p_entity_attribute_rec IN ENTITY_ATTRIBUTE_REC_TYPE,
677 p_data_source_tab IN DATA_SOURCE_TBL,
678 x_return_status IN OUT NOCOPY VARCHAR2
679 ) IS
680 l_debug_prefix VARCHAR2(30) := '';
681 BEGIN
682
683 -- Debug info.
684 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
685 hz_utility_v2pub.debug(p_message=>'Validate_EntityAttribute (+)',
686 p_prefix=>l_debug_prefix,
687 p_msg_level=>fnd_log.level_procedure);
688 END IF;
689
690 --------------------------------------
691 -- validate entity_name
692 --------------------------------------
693
694 -- entity_name is mandatory field.
695
696 hz_utility_v2pub.validate_mandatory (
697 p_create_update_flag => 'C',
698 p_column => 'entity_name',
699 p_column_value => p_entity_attribute_rec.entity_name,
700 x_return_status => x_return_status );
701
702 -- entity_name is lookup code in lookup type ENTITY_NAME
703
704 IF p_entity_attribute_rec.entity_name IS NOT NULL AND
705 p_entity_attribute_rec.entity_name <> FND_API.G_MISS_CHAR
706 THEN
707 hz_utility_v2pub.validate_lookup (
708 p_column => 'entity_name',
709 p_lookup_type => 'ENTITY_NAME',
710 p_column_value => p_entity_attribute_rec.entity_name,
711 x_return_status => x_return_status );
712 END IF;
713
714 -- the validation for attribute_name only makes sense when the entity_name
715 -- has a valid value.
716
720 -- validate attribute_name
717 IF x_return_status = FND_API.G_RET_STS_SUCCESS THEN
718
719 --------------------------------------
721 --------------------------------------
722
723 -- attribute_name must be null when entity_name is for other
724 -- entities. attribute_name is mandatory if entity_name is
725 -- for party profiles.
726
727 IF p_entity_attribute_rec.entity_name NOT IN
728 (G_ORG_ENTITY, G_PERSON_ENTITY) AND
729 p_entity_attribute_rec.attribute_name IS NOT NULL AND
730 p_entity_attribute_rec.attribute_name <> FND_API.G_MISS_CHAR
731 THEN
732 fnd_message.set_name('AR','HZ_API_COLUMN_SHOULD_BE_NULL');
733 fnd_message.set_token('COLUMN','attribute_name');
734 fnd_message.set_token('TABLE','hz_entity_attributes');
735 fnd_msg_pub.add;
736 x_return_status := FND_API.G_RET_STS_ERROR;
737 ELSIF p_entity_attribute_rec.entity_name IN
738 (G_ORG_ENTITY, G_PERSON_ENTITY)
739 THEN
740 -- attribute_name is mandatory field.
741
742 hz_utility_v2pub.validate_mandatory (
743 p_create_update_flag => 'C',
744 p_column => 'attribute_name',
745 p_column_value => p_entity_attribute_rec.attribute_name,
746 x_return_status => x_return_status );
747
748 -- attribute must be a valid attribute in the
749 -- corresponding api record type.
750
751 Validate_Attribute (
752 p_entity_name => p_entity_attribute_rec.entity_name,
753 p_attribute_name => p_entity_attribute_rec.attribute_name,
754 x_return_status => x_return_status );
755 /*
756 -- attribute must be a valid lookup code
757
758 hz_utility_v2pub.validate_lookup (
759 p_column => 'attribute name',
760 p_lookup_type => p_entity_attribute_rec.entity_name,
761 p_column_value => p_entity_attribute_rec.attribute_name,
762 x_return_status => x_return_status );
763 */
764 END IF;
765 END IF;
766
767 --------------------------------------
768 -- validate created_by_module
769 --------------------------------------
770
771 hz_utility_v2pub.validate_created_by_module(
772 p_create_update_flag => 'C',
773 p_created_by_module => p_entity_attribute_rec.created_by_module,
774 p_old_created_by_module => null,
775 x_return_status => x_return_status);
776
777 --------------------------------------
778 -- validate data sources
779 --------------------------------------
780
781 -- p_data_source_tab can not be empty.
782 -- Every data source must be a valid lookup code under
783 -- CONTENT_SOURCE_TYPE excluding SST.
784
785 -- SSM SST Integration and Extension: data source will be a foreign key in hz_orig_systems_b
786 -- and will not be a lookup of type CONTENT_SOURCE_TYPE.
787 IF p_data_source_tab.COUNT = 0 THEN
788 fnd_message.set_name('AR', 'HZ_API_NO_DATA_SOURCE');
789 fnd_msg_pub.add;
790 x_return_status := FND_API.G_RET_STS_ERROR;
791 ELSE
792 FOR i IN 1..p_data_source_tab.COUNT LOOP
793 IF p_data_source_tab(i) IS NULL OR
794 p_data_source_tab(i) = 'SST'
795 THEN
796 fnd_message.set_name('AR', 'HZ_API_INVALID_DATA_SOURCE');
797 fnd_msg_pub.add;
798 x_return_status := FND_API.G_RET_STS_ERROR;
799 ELSE
800 /*
801 hz_utility_v2pub.validate_lookup (
802 p_column => 'data source',
803 p_lookup_type => 'CONTENT_SOURCE_TYPE',
804 p_column_value => p_data_source_tab(i),
805 x_return_status => x_return_status );
806 */
807 DECLARE
808 CURSOR c_valid_data_source IS
809 SELECT '1'
810 FROM HZ_ORIG_SYSTEMS_B
811 WHERE orig_system = p_data_source_tab(i)
812 AND sst_flag = 'Y';
813 l_dummy VARCHAR2(1);
814 BEGIN
815 OPEN c_valid_data_source;
816 LOOP
817 FETCH c_valid_data_source
818 INTO l_dummy;
819
820 IF c_valid_data_source%NOTFOUND THEN
821 FND_MESSAGE.SET_NAME('AR','HZ_API_INVALID_FK');
822 FND_MESSAGE.SET_TOKEN('TABLE','HZ_ORIG_SYSTEM_B');
823 FND_MESSAGE.SET_TOKEN('COLUMN','ORIG_SYSTEM');
824 FND_MESSAGE.SET_TOKEN('FK','DATA SOURCE');
825 FND_MSG_PUB.ADD;
826 x_return_status := FND_API.G_RET_STS_ERROR;
827 END IF;
828 END LOOP;
829 CLOSE c_valid_data_source;
830 EXCEPTION
831 WHEN OTHERS THEN
832 x_return_status := FND_API.G_RET_STS_ERROR;
833 RAISE FND_API.G_EXC_ERROR;
834 END;
835
836 END IF;
837 END LOOP;
838 END IF;
839
840 -- Debug info.
841 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
842 hz_utility_v2pub.debug(p_message=>'Validate_EntityAttribute (-)',
843 p_prefix=>l_debug_prefix,
844 p_msg_level=>fnd_log.level_procedure);
845 END IF;
846
847 END Validate_EntityAttribute;
848
849 /**
850 * PRIVATE PROCEDURE Validate_Attribute
851 *
852 * DESCRIPTION
853 * Validate attribute name against V2 API rec type.
854 *
858 * IN:
855 * EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
856 *
857 * ARGUMENTS
859 * p_entity_name Entity Name
860 * p_attribute_name Attribute Name
861 * IN/OUT:
862 * x_return_status Return status after the call. The status can
863 * be FND_API.G_RET_STS_SUCCESS (success),
864 * FND_API.G_RET_STS_ERROR (error),
865 * FND_API.G_RET_STS_UNEXP_ERROR (unexpected error).
866 *
867 * NOTES
868 *
869 * MODIFICATION HISTORY
870 *
871 * 02-12-2002 Jianying Huang o Created.
872 */
873
874 PROCEDURE Validate_Attribute (
875 p_entity_name IN VARCHAR2,
876 p_attribute_name IN VARCHAR2,
877 x_return_status IN OUT NOCOPY VARCHAR2
878 ) IS
879
880 CURSOR c_attribute_name (
881 p_name VARCHAR2,
882 p_apps_schema VARCHAR2,
883 p_ar_schema VARCHAR2
884 ) IS
885 -- Bug 4956769 : Modify for perf
886 select aa.argument_name, aa.data_type, party.column_name
887 from sys.all_arguments aa, (
888 select min(a.sequence) id
889 from sys.all_arguments a
890 where a.object_name = 'GET_' ||upper (p_name)||'_REC'
891 and a.type_subname = upper (p_name) || '_REC_TYPE'
892 and a.data_level = 0
893 and a.object_id in (
894 select b.object_id
895 from sys.all_objects b
896 where b.object_name = 'HZ_PARTY_V2PUB'
897 and b.owner = p_apps_schema
898 and b.object_type = 'PACKAGE')) temp1, (
899 --Bug 15893080 - Remove direct access from all_tab_columns
900 /*
901 select column_name
902 from sys.all_tab_columns c
903 where c.table_name = 'HZ_PARTIES'
904 and c.owner = p_ar_schema
905 and exists (
906 select null
907 from sys.all_tab_columns c2
908 where c2.owner = p_ar_schema
909 and c2.column_name = c.column_name
910 and c2.table_name = 'HZ_' ||upper (p_name) || '_PROFILES')
911 and c.column_name not like 'ATTRIBUTE%'
912 and c.column_name not like 'GLOBAL_ATTRIBUTE%'
913 and c.column_name not in ('APPLICATION_ID')
914 */
915 select col.column_name
916 from user_synonyms syn
917 , dba_tab_columns col
918 where syn.synonym_name = 'HZ_PARTIES'
919 and col.owner = syn.table_owner
920 and col.table_name = syn.table_name
921 and exists (
922 select null
923 from user_synonyms syn
924 , dba_tab_columns col2
925 where col2.owner = syn.table_owner
926 and col2.table_name = syn.table_name
927 and col.column_name = col2.column_name
928 and syn.synonym_name = 'HZ_' ||upper (p_name) || '_PROFILES')
929 and col.column_name not like 'ATTRIBUTE%'
930 and col.column_name not like 'GLOBAL_ATTRIBUTE%'
931 and col.column_name not in ('APPLICATION_ID')
932
933 ) party
934 where aa.object_name = 'GET_' ||upper (p_name)||'_REC'
935 and aa.data_level = 1
936 and aa.data_type <> 'PL/SQL RECORD'
937 and aa.argument_name not in ('CONTENT_SOURCE_TYPE',
938 'ACTUAL_CONTENT_SOURCE', 'APPLICATION_ID')
939 and aa.sequence > temp1.id
940 and aa.object_id in (
941 select b.object_id
942 from sys.all_objects b
943 where b.object_name = 'HZ_PARTY_V2PUB'
944 and b.owner = p_apps_schema
945 and b.object_type = 'PACKAGE')
946 and aa.argument_name = party.column_name (+)
947 order by argument_name;
948
949 l_name VARCHAR2(30);
950 i NUMBER;
951 l_raise_error BOOLEAN := FALSE;
952 l_debug_prefix VARCHAR2(30) := '';
953 l_bool BOOLEAN;
954 l_status VARCHAR2(255);
955 l_apps_schema VARCHAR2(255);
956 l_ar_schema VARCHAR2(255);
957 l_tmp VARCHAR2(2000);
958
959 BEGIN
960
961 -- Debug info.
962 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
963 hz_utility_v2pub.debug(p_message=>'Validate_Attribute (+)',
964 p_prefix=>l_debug_prefix,
965 p_msg_level=>fnd_log.level_procedure);
966 END IF;
967
968 l_apps_schema := hz_utility_v2pub.Get_AppsSchemaName;
969 l_ar_schema := hz_utility_v2pub.Get_SchemaName('AR');
970
971 IF p_entity_name = G_ORG_ENTITY THEN
972 l_name := 'ORGANIZATION';
973
974 IF G_ORG_ATTRIBUTE_NAME_TAB IS NULL OR
975 G_ORG_ATTRIBUTE_TYPE_TAB.COUNT = 0
976 THEN
977 OPEN c_attribute_name(l_name, l_apps_schema, l_ar_schema);
978 FETCH c_attribute_name BULK COLLECT INTO
979 G_ORG_ATTRIBUTE_NAME_TAB, G_ORG_ATTRIBUTE_TYPE_TAB,
980 G_ORG_DEN_ATTRIBUTE_NAME_TAB;
981 CLOSE c_attribute_name;
982
983 END IF;
984
985 i := getIndex(G_ORG_ATTRIBUTE_NAME_TAB, p_attribute_name);
986 IF i = 0 THEN
987 l_raise_error := TRUE;
988 END IF;
989 ELSE
993 G_PER_ATTRIBUTE_TYPE_TAB.COUNT = 0
990 l_name := 'PERSON';
991
992 IF G_PER_ATTRIBUTE_NAME_TAB IS NULL OR
994 THEN
995 OPEN c_attribute_name(l_name, l_apps_schema, l_ar_schema);
996 FETCH c_attribute_name BULK COLLECT INTO
997 G_PER_ATTRIBUTE_NAME_TAB, G_PER_ATTRIBUTE_TYPE_TAB,
998 G_PER_DEN_ATTRIBUTE_NAME_TAB;
999 CLOSE c_attribute_name;
1000 END IF;
1001
1002 i := getIndex(G_PER_ATTRIBUTE_NAME_TAB, p_attribute_name);
1003 IF i = 0 THEN
1004 l_raise_error := TRUE;
1005 END IF;
1006 END IF;
1007
1008 IF l_raise_error THEN
1009 fnd_message.set_name('AR', 'HZ_API_INVALID_ATTRIBUTE');
1010 fnd_message.set_token('ATTRIBUTE', p_attribute_name);
1011 fnd_message.set_token('ENTITY', l_name);
1012 fnd_msg_pub.add;
1013 x_return_status := fnd_api.g_ret_sts_error;
1014 END IF;
1015
1016 -- Debug info.
1017 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
1018 hz_utility_v2pub.debug(p_message=>'Validate_Attribute (-)' ,
1019 p_prefix=>l_debug_prefix,
1020 p_msg_level=>fnd_log.level_procedure);
1021 END IF;
1022
1023 END Validate_Attribute;
1024
1025 /**
1026 * PRIVATE FUNCTION getIndex
1027 *
1028 * DESCRIPTION
1029 * Returns the index of an element in an ordered varchar2 list.
1030 *
1031 * EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
1032 *
1033 * ARGUMENTS
1034 * IN:
1035 * p_list VARCHAR2 List
1036 * p_value Element Value
1037 * IN/OUT:
1038 * OUT:
1039 *
1040 * NOTES
1041 *
1042 * MODIFICATION HISTORY
1043 *
1044 * 02-12-2002 Jianying Huang o Created.
1045 */
1046
1047 FUNCTION getIndex (
1048 p_list IN VARCHARList,
1049 p_value IN VARCHAR2
1050 ) RETURN NUMBER IS
1051
1052 l_start NUMBER;
1053 l_end NUMBER;
1054 l_middle NUMBER;
1055 l_debug_prefix VARCHAR2(30) := '';
1056
1057 BEGIN
1058
1059 -- Debug info.
1060 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
1061 hz_utility_v2pub.debug(p_message=>'getIndex (+)',
1062 p_prefix=>l_debug_prefix,
1063 p_msg_level=>fnd_log.level_procedure);
1064 END IF;
1065
1066 l_start := 1; l_end := p_list.COUNT;
1067 WHILE l_start <= l_end LOOP
1068 l_middle := ROUND((l_end+l_start)/2);
1069 IF p_value = p_list(l_middle) THEN
1070 RETURN l_middle;
1071 ELSIF p_value > p_list(l_middle) THEN
1072 l_start := l_middle+1;
1073 ELSE
1074 l_end := l_middle-1;
1075 END IF;
1076 END LOOP;
1077
1078 RETURN 0;
1079
1080 -- Debug info.
1081 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
1082 hz_utility_v2pub.debug(p_message=>'getIndex (-)',
1083 p_prefix=>l_debug_prefix,
1084 p_msg_level=>fnd_log.level_procedure);
1085 END IF;
1086
1087 END getIndex;
1088
1089 /**
1090 * PRIVATE PROCEDURE db_InsertEntityAttribute
1091 *
1092 * DESCRIPTION
1093 * Private procedure to insert entity / attribute into the table.
1094 *
1095 * EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
1096 *
1097 * ARGUMENTS
1098 * IN:
1099 * p_entity_attribute_rec Entity Attribute record.
1100 * OUT:
1101 * x_entity_attr_id Dictionary ID.
1102 *
1103 * NOTES
1104 *
1105 * MODIFICATION HISTORY
1106 *
1107 * 02-12-2002 Jianying Huang o Created.
1108 * 11-24-2004 Rajib Ranjan Borah o SSM SST Integration and Extension.
1109 * User Overwrite rule and Third Party Rule
1110 * are orig_system specific. No default
1111 * records will be created in these tables
1112 * and records with overwrite_flag = 'N' will
1113 * not be stored.
1114 *
1115 */
1116
1117 PROCEDURE db_InsertEntityAttribute (
1118 p_entity_attribute_rec IN ENTITY_ATTRIBUTE_REC_TYPE,
1119 p_group_name IN VARCHAR2,
1120 x_entity_attr_id OUT NOCOPY NUMBER
1121 ) IS
1122 /*
1123 CURSOR c_user_overwrite_rule IS
1124 SELECT UNIQUE rule_id
1125 FROM hz_user_overwrite_rules;
1126
1127 i_rule_id INDEXIDList;
1128
1129 CURSOR c_third_party_rule IS
1130 SELECT 'Y'
1131 FROM hz_thirdparty_rule
1132 WHERE ROWNUM = 1;
1133
1134 l_dummy VARCHAR2(1);
1135 */
1136 l_debug_prefix VARCHAR2(30) := '';
1137
1138 BEGIN
1139
1140 -- Debug info.
1141 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
1142 hz_utility_v2pub.debug(p_message=>'db_InsertEntityAttribute (+)',
1143 p_prefix=>l_debug_prefix,
1144 p_msg_level=>fnd_log.level_procedure);
1145 END IF;
1146
1147 INSERT INTO hz_entity_attributes (
1148 entity_attr_id,
1149 entity_name,
1150 attribute_name,
1151 attribute_group_name,
1155 creation_date,
1152 created_by_module,
1153 application_id,
1154 created_by,
1156 last_update_login,
1157 last_update_date,
1158 last_updated_by
1159 ) VALUES (
1160 --
1161 -- entity_attr_id
1162 hz_entity_attributes_s.NEXTVAL,
1163 DECODE(p_entity_attribute_rec.entity_name,
1164 FND_API.G_MISS_CHAR, NULL, p_entity_attribute_rec.entity_name),
1165 DECODE(p_entity_attribute_rec.attribute_name,
1166 FND_API.G_MISS_CHAR, NULL, p_entity_attribute_rec.attribute_name),
1167 p_group_name,
1168 DECODE(p_entity_attribute_rec.created_by_module,
1169 FND_API.G_MISS_CHAR, NULL, p_entity_attribute_rec.created_by_module),
1170 DECODE(p_entity_attribute_rec.application_id,
1171 FND_API.G_MISS_NUM, NULL, p_entity_attribute_rec.application_id),
1172 hz_utility_v2pub.created_by,
1173 SYSDATE,
1174 hz_utility_v2pub.last_update_login,
1175 SYSDATE,
1176 hz_utility_v2pub.last_updated_by )
1177 RETURNING entity_attr_id INTO x_entity_attr_id;
1178
1179 /*
1180 OPEN c_user_overwrite_rule;
1181 FETCH c_user_overwrite_rule BULK COLLECT INTO i_rule_id;
1182 CLOSE c_user_overwrite_rule;
1183
1184 FORALL i IN 1..i_rule_id.COUNT
1185 INSERT INTO hz_user_overwrite_rules (
1186 rule_id,
1187 entity_attr_id,
1188 overwrite_flag,
1189 created_by,
1190 creation_date,
1191 last_update_login,
1192 last_update_date,
1193 last_updated_by
1194 ) VALUES (
1195 i_rule_id(i),
1196 x_entity_attr_id,
1197 -- by default, user can overwrite third party data.
1198 'Y',
1199 hz_utility_v2pub.created_by,
1200 SYSDATE,
1201 hz_utility_v2pub.last_update_login,
1202 SYSDATE,
1203 hz_utility_v2pub.last_updated_by );
1204 */
1205 /*
1206 OPEN c_third_party_rule;
1207 FETCH c_third_party_rule INTO l_dummy;
1208 IF c_third_party_rule%NOTFOUND THEN
1209 l_dummy := 'N';
1210 END IF;
1211 CLOSE c_third_party_rule;
1212
1213 IF l_dummy = 'Y' THEN
1214 INSERT INTO hz_thirdparty_rule (
1215 entity_attr_id,
1216 overwrite_flag,
1217 created_by,
1218 creation_date,
1219 last_update_login,
1220 last_update_date,
1221 last_updated_by
1222 )
1223 VALUES (
1224 x_entity_attr_id,
1225 -- by default, third party can not overwrite user data.
1226 'N',
1227 hz_utility_v2pub.created_by,
1228 SYSDATE,
1229 hz_utility_v2pub.last_update_login,
1230 SYSDATE,
1231 hz_utility_v2pub.last_updated_by
1232 );
1233 END IF;
1234 */
1235 -- Debug info.
1236 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
1237 hz_utility_v2pub.debug(p_message=>'db_InsertEntityAttribute (-)',
1238 p_prefix=>l_debug_prefix,
1239 p_msg_level=>fnd_log.level_procedure);
1240 END IF;
1241
1242 END db_InsertEntityAttribute;
1243
1244 /**
1245 * PRIVATE PROCEDURE db_InsertDataSource
1246 *
1247 * DESCRIPTION
1248 * Private procedure to insert data source setup into the table.
1249 *
1250 * EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
1251 *
1252 * ARGUMENTS
1253 * IN:
1254 * p_entity_attr_id Dictionary ID.
1255 * p_data_source_tab PL/SQL table for data source setup.
1256 * IN/OUT:
1257 * OUT:
1258 *
1259 * NOTES
1260 *
1261 * MODIFICATION HISTORY
1262 *
1263 * 02-12-2002 Jianying Huang o Created.
1264 * 12-12-2004 Rajib Ranjan Borah o SSM SST Integration and Extension.
1265 * If p_new_item_flag = 'O'(i.e. other entity),
1266 * set ranking to 1.
1267 *
1268 */
1269
1270 PROCEDURE db_InsertDataSource (
1271 p_new_item_flag IN VARCHAR2,
1272 p_entity_attr_id IN NUMBER,
1273 p_data_source_tab IN DATA_SOURCE_TBL
1274 ) IS
1275 l_debug_prefix VARCHAR2(30) := '';
1276 BEGIN
1277
1278 -- Debug info.
1279 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
1280 hz_utility_v2pub.debug(p_message=>'db_InsertDataSource (+)',
1281 p_prefix=>l_debug_prefix,
1282 p_msg_level=>fnd_log.level_procedure);
1283 END IF;
1284
1285 FORALL i IN 1..p_data_source_tab.COUNT
1286 INSERT INTO hz_select_data_sources (
1287 entity_attr_id,
1288 content_source_type,
1289 ranking,
1290 created_by,
1291 creation_date,
1292 last_update_login,
1293 last_update_date,
1294 last_updated_by
1295 )
1296 SELECT
1297 p_entity_attr_id,
1298 p_data_source_tab(i),
1299 --
1300 -- ranking
1301 DECODE(p_new_item_flag,
1302 'Y', DECODE(p_data_source_tab(i), 'USER_ENTERED', 1, 0),
1303 'O',1, -- For other entities.
1304 0),
1305 hz_utility_v2pub.created_by,
1306 SYSDATE,
1307 hz_utility_v2pub.last_update_login,
1308 SYSDATE,
1309 hz_utility_v2pub.last_updated_by
1310 FROM dual
1314 WHERE source2.entity_attr_id = p_entity_attr_id
1311 WHERE NOT EXISTS (
1312 SELECT 'Y'
1313 FROM hz_select_data_sources source2
1315 AND source2.content_source_type = p_data_source_tab(i));
1316
1317 -- Debug info.
1318
1319 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
1320 hz_utility_v2pub.debug(p_message=>'db_InsertDataSource (-)',
1321 p_prefix=>l_debug_prefix,
1322 p_msg_level=>fnd_log.level_procedure);
1323 END IF;
1324
1325 END db_InsertDataSource;
1326
1327 --------------------------------------
1328 -- public procedures and functions
1329 --------------------------------------
1330
1331 /**
1332 * PROCEDURE Add_EntityAttribute
1333 *
1334 * DESCRIPTION
1335 * Add the new entity and / or attribute into the dictionary.
1336 *
1337 * EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
1338 *
1339 * ARGUMENTS
1340 * IN:
1341 * p_init_msg_list Initialize message stack if it is set to
1342 * FND_API.G_TRUE. Default is FND_API.G_FALSE.
1343 * p_entity_attribute_rec Entity Attribute record.
1344 * p_data_source_tbl PL/SQL Table for Data Source Setup.
1345 * IN/OUT:
1346 * OUT:
1347 * x_entity_attr_id Dictionary ID.
1348 * x_return_status Return status after the call. The status can
1349 * be FND_API.G_RET_STS_SUCCESS (success),
1350 * FND_API.G_RET_STS_ERROR (error),
1351 * FND_API.G_RET_STS_UNEXP_ERROR (unexpected error).
1352 * x_msg_count Number of messages in message stack.
1353 * x_msg_data Message text if x_msg_count is 1.
1354 *
1355 * NOTES
1356 *
1357 * MODIFICATION HISTORY
1358 *
1359 * 02-12-2002 Jianying Huang o Created.
1360 */
1361
1362 PROCEDURE Add_EntityAttribute (
1363 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
1364 p_entity_attribute_rec IN ENTITY_ATTRIBUTE_REC_TYPE,
1365 p_data_source_tab IN DATA_SOURCE_TBL,
1366 x_entity_attr_id OUT NOCOPY NUMBER,
1367 x_return_status OUT NOCOPY VARCHAR2,
1368 x_msg_count OUT NOCOPY NUMBER,
1369 x_msg_data OUT NOCOPY VARCHAR2
1370 ) IS
1371 l_debug_prefix VARCHAR2(30) := '';
1372 BEGIN
1373
1374 -- Standard start of API savepoint
1375 SAVEPOINT Add_EntityAttribute;
1376
1377 -- Check if API is called in debug mode. If yes, enable debug.
1378 --enable_debug;
1379
1380 -- Debug info.
1381 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
1382 hz_utility_v2pub.debug(p_message=>'Add_EntityAttribute (+)',
1383 p_prefix=>l_debug_prefix,
1384 p_msg_level=>fnd_log.level_procedure);
1385 END IF;
1386
1387 -- Initialize message list if p_init_msg_list is set to TRUE.
1388 IF fnd_api.to_Boolean(p_init_msg_list) THEN
1389 fnd_msg_pub.initialize;
1390 END IF;
1391
1392 -- Initialize API return status to success.
1393 x_return_status := FND_API.G_RET_STS_SUCCESS;
1394
1395 -- Call business logic.
1396 do_AddEntityAttribute (
1397 p_entity_attribute_rec,
1398 p_data_source_tab,
1399 x_entity_attr_id,
1400 x_return_status );
1401
1402 -- Standard call to get message count and if count is 1, get message info.
1403 fnd_msg_pub.Count_And_Get(
1404 p_encoded => FND_API.G_FALSE,
1405 p_count => x_msg_count,
1406 p_data => x_msg_data );
1407
1408 -- Debug info.
1409 IF fnd_log.level_exception>=fnd_log.g_current_runtime_level THEN
1410 hz_utility_v2pub.debug_return_messages(p_msg_count=>x_msg_count,
1411 p_msg_data=>x_msg_data,
1412 p_msg_type=>'WARNING',
1413 p_msg_level=>fnd_log.level_exception);
1414 END IF;
1415 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
1416 hz_utility_v2pub.debug(p_message=>'Add_EntityAttribute (-)',
1417 p_prefix=>l_debug_prefix,
1418 p_msg_level=>fnd_log.level_procedure);
1419 END IF;
1420
1421 -- Check if API is called in debug mode. If yes, disable debug.
1422 --disable_debug;
1423
1424 EXCEPTION
1425 WHEN FND_API.G_EXC_ERROR THEN
1426 ROLLBACK TO Add_EntityAttribute;
1427 x_return_status := FND_API.G_RET_STS_ERROR;
1428
1429 fnd_msg_pub.Count_And_Get(
1430 p_encoded => FND_API.G_FALSE,
1431 p_count => x_msg_count,
1432 p_data => x_msg_data );
1433
1434 -- Debug info.
1435 IF fnd_log.level_error>=fnd_log.g_current_runtime_level THEN
1436 hz_utility_v2pub.debug_return_messages(p_msg_count=>x_msg_count,
1437 p_msg_data=>x_msg_data,
1438 p_msg_type=>'ERROR',
1439 p_msg_level=>fnd_log.level_error);
1440 END IF;
1441 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
1442 hz_utility_v2pub.debug(p_message=>'Add_EntityAttribute (-)',
1443 p_prefix=>l_debug_prefix,
1444 p_msg_level=>fnd_log.level_procedure);
1445 END IF;
1446
1447 -- Check if API is called in debug mode. If yes, disable debug.
1451 ROLLBACK TO Add_EntityAttribute;
1448 --disable_debug;
1449
1450 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1452 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1453
1454 fnd_msg_pub.Count_And_Get(
1455 p_encoded => FND_API.G_FALSE,
1456 p_count => x_msg_count,
1457 p_data => x_msg_data );
1458
1459 -- Debug info.
1460 IF fnd_log.level_error>=fnd_log.g_current_runtime_level THEN
1461 hz_utility_v2pub.debug_return_messages(p_msg_count=>x_msg_count,
1462 p_msg_data=>x_msg_data,
1463 p_msg_type=>'UNEXPECTED ERROR',
1464 p_msg_level=>fnd_log.level_error);
1465 END IF;
1466 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
1467 hz_utility_v2pub.debug(p_message=>'Add_EntityAttribute (-)',
1468 p_prefix=>l_debug_prefix,
1469 p_msg_level=>fnd_log.level_procedure);
1470 END IF;
1471
1472 -- Check if API is called in debug mode. If yes, disable debug.
1473 --disable_debug;
1474
1475 WHEN OTHERS THEN
1476 ROLLBACK TO Add_EntityAttribute;
1477 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1478
1479 fnd_message.set_name('AR','HZ_API_OTHERS_EXCEP');
1480 fnd_message.set_token('ERROR',SQLERRM);
1481 fnd_msg_pub.add;
1482
1483 fnd_msg_pub.Count_And_Get(
1484 p_encoded => FND_API.G_FALSE,
1485 p_count => x_msg_count,
1486 p_data => x_msg_data );
1487
1488 -- Debug info.
1489 IF fnd_log.level_error>=fnd_log.g_current_runtime_level THEN
1490 hz_utility_v2pub.debug_return_messages(p_msg_count=>x_msg_count,
1491 p_msg_data=>x_msg_data,
1492 p_msg_type=>'SQL ERROR',
1493 p_msg_level=>fnd_log.level_error);
1494 END IF;
1495 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
1496 hz_utility_v2pub.debug(p_message=>'Add_EntityAttribute (-)',
1497 p_prefix=>l_debug_prefix,
1498 p_msg_level=>fnd_log.level_procedure);
1499 END IF;
1500
1501 -- Check if API is called in debug mode. If yes, disable debug.
1502 --disable_debug;
1503
1504 END Add_EntityAttribute;
1505
1506 /**
1507 * PROCEDURE Get_EntityAttribute
1508 *
1509 * DESCRIPTION
1510 * Get the entity / attribute from the dictionary.
1511 *
1512 * EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
1513 *
1514 * ARGUMENTS
1515 * IN:
1516 * p_init_msg_list Initialize message stack if it is set to
1517 * FND_API.G_TRUE. Default is FND_API.G_FALSE.
1518 * p_entity_name Entity Name
1519 * p_attribute_name Attribute Name
1520 * IN/OUT:
1521 * OUT:
1522 * x_data_source_tbl PL/SQL Table for Data Source Setup.
1523 * x_return_status Return status after the call. The status can
1524 * be FND_API.G_RET_STS_SUCCESS (success),
1525 * FND_API.G_RET_STS_ERROR (error),
1526 * FND_API.G_RET_STS_UNEXP_ERROR (unexpected error).
1527 * x_msg_count Number of messages in message stack.
1528 * x_msg_data Message text if x_msg_count is 1.
1529 *
1530 * NOTES
1531 *
1532 * MODIFICATION HISTORY
1533 *
1534 * 02-12-2002 Jianying Huang o Created.
1535 */
1536
1537 PROCEDURE Get_EntityAttribute (
1538 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
1539 p_entity_name IN VARCHAR2,
1540 p_attribute_name IN VARCHAR2,
1541 x_data_source_tbl OUT NOCOPY DATA_SOURCE_TBL,
1542 x_return_status OUT NOCOPY VARCHAR2,
1543 x_msg_count OUT NOCOPY NUMBER,
1544 x_msg_data OUT NOCOPY VARCHAR2
1545 ) IS
1546
1547 l_entity_attr_id NUMBER;
1548 l_data_source_tbl DATA_SOURCE_TBL;
1549 l_debug_prefix VARCHAR2(30) := '';
1550
1551 CURSOR c_entity IS
1552 SELECT entity_attr_id
1553 FROM hz_entity_attributes
1554 WHERE entity_name = p_entity_name
1555 AND ((attribute_name IS NULL AND
1556 (p_attribute_name IS NULL OR
1557 p_attribute_name = FND_API.G_MISS_CHAR)) OR
1558 (attribute_name = p_attribute_name));
1559
1560 CURSOR c_data_sources (
1561 p_entity_attr_id NUMBER
1562 ) IS
1563 SELECT content_source_type
1564 FROM hz_select_data_sources
1565 WHERE entity_attr_id = p_entity_attr_id;
1566
1567 BEGIN
1568
1569 -- Check if API is called in debug mode. If yes, enable debug.
1570 --enable_debug;
1571
1572 -- Debug info.
1573
1574 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
1575 hz_utility_v2pub.debug(p_message=>'Get_EntityAttribute (+)',
1576 p_prefix=>l_debug_prefix,
1577 p_msg_level=>fnd_log.level_procedure);
1578 END IF;
1579
1580 -- Initialize message list if p_init_msg_list is set to TRUE.
1581 IF fnd_api.to_Boolean(p_init_msg_list) THEN
1582 fnd_msg_pub.initialize;
1583 END IF;
1584
1585 -- Initialize API return status to success.
1589 OPEN c_entity;
1586 x_return_status := FND_API.G_RET_STS_SUCCESS;
1587
1588 -- find the entity and / or attribute in the dictionary
1590 FETCH c_entity INTO l_entity_attr_id;
1591
1592 IF c_entity%NOTFOUND THEN
1593 fnd_message.set_name('AR', 'HZ_API_NO_RECORD');
1594 fnd_message.set_token('RECORD', 'entity attribute');
1595 fnd_message.set_token('VALUE', '<'||p_entity_name||','||
1596 NVL(p_attribute_name,'null'||'>'));
1597 fnd_msg_pub.add;
1598 RAISE FND_API.G_EXC_ERROR;
1599 END IF;
1600 CLOSE c_entity;
1601
1602 -- select data sources.
1603 OPEN c_data_sources(l_entity_attr_id);
1604 FETCH c_data_sources BULK COLLECT INTO l_data_source_tbl;
1605 CLOSE c_data_sources;
1606
1607 x_data_source_tbl := l_data_source_tbl;
1608
1609 -- Standard call to get message count and if count is 1, get message info.
1610 fnd_msg_pub.Count_And_Get(
1611 p_encoded => FND_API.G_FALSE,
1612 p_count => x_msg_count,
1613 p_data => x_msg_data );
1614
1615 -- Debug info.
1616
1617 IF fnd_log.level_exception>=fnd_log.g_current_runtime_level THEN
1618 hz_utility_v2pub.debug_return_messages(p_msg_count=>x_msg_count,
1619 p_msg_data=>x_msg_data,
1620 p_msg_type=>'WARNING',
1621 p_msg_level=>fnd_log.level_exception);
1622 END IF;
1623 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
1624 hz_utility_v2pub.debug(p_message=>'Get_EntityAttribute (-)',
1625 p_prefix=>l_debug_prefix,
1626 p_msg_level=>fnd_log.level_procedure);
1627 END IF;
1628
1629
1630 -- Check if API is called in debug mode. If yes, disable debug.
1631 --disable_debug;
1632
1633 EXCEPTION
1634 WHEN FND_API.G_EXC_ERROR THEN
1635 x_return_status := FND_API.G_RET_STS_ERROR;
1636
1637 fnd_msg_pub.Count_And_Get(
1638 p_encoded => FND_API.G_FALSE,
1639 p_count => x_msg_count,
1640 p_data => x_msg_data );
1641
1642 -- Debug info.
1643 IF fnd_log.level_error>=fnd_log.g_current_runtime_level THEN
1644 hz_utility_v2pub.debug_return_messages(p_msg_count=>x_msg_count,
1645 p_msg_data=>x_msg_data,
1646 p_msg_type=>'ERROR',
1647 p_msg_level=>fnd_log.level_error);
1648 END IF;
1649 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
1650 hz_utility_v2pub.debug(p_message=>'Get_EntityAttribute (-)',
1651 p_prefix=>l_debug_prefix,
1652 p_msg_level=>fnd_log.level_procedure);
1653 END IF;
1654
1655
1656 -- Check if API is called in debug mode. If yes, disable debug.
1657 --disable_debug;
1658
1659 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1660 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1661
1662 fnd_msg_pub.Count_And_Get(
1663 p_encoded => FND_API.G_FALSE,
1664 p_count => x_msg_count,
1665 p_data => x_msg_data );
1666
1667 -- Debug info.
1668 IF fnd_log.level_error>=fnd_log.g_current_runtime_level THEN
1669 hz_utility_v2pub.debug_return_messages(p_msg_count=>x_msg_count,
1670 p_msg_data=>x_msg_data,
1671 p_msg_type=>'UNEXPECTED ERROR',
1672 p_msg_level=>fnd_log.level_error);
1673 END IF;
1674 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
1675 hz_utility_v2pub.debug(p_message=>'Get_EntityAttribute (-)',
1676 p_prefix=>l_debug_prefix,
1677 p_msg_level=>fnd_log.level_procedure);
1678 END IF;
1679
1680 -- Check if API is called in debug mode. If yes, disable debug.
1681 --disable_debug;
1682
1683 WHEN OTHERS THEN
1684 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1685
1686 fnd_message.set_name('AR','HZ_API_OTHERS_EXCEP');
1687 fnd_message.set_token('ERROR',SQLERRM);
1688 fnd_msg_pub.add;
1689
1690 fnd_msg_pub.Count_And_Get(
1691 p_encoded => FND_API.G_FALSE,
1692 p_count => x_msg_count,
1693 p_data => x_msg_data );
1694
1695 -- Debug info.
1696 IF fnd_log.level_error>=fnd_log.g_current_runtime_level THEN
1697 hz_utility_v2pub.debug_return_messages(p_msg_count=>x_msg_count,
1698 p_msg_data=>x_msg_data,
1699 p_msg_type=>'SQL ERROR',
1700 p_msg_level=>fnd_log.level_error);
1701 END IF;
1702 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
1703 hz_utility_v2pub.debug(p_message=> 'Get_EntityAttribute (-)',
1704 p_prefix=>l_debug_prefix,
1705 p_msg_level=>fnd_log.level_procedure);
1706 END IF;
1707
1708 -- Check if API is called in debug mode. If yes, disable debug.
1709 --disable_debug;
1710
1711 END Get_EntityAttribute;
1712
1713 /**
1714 * PROCEDURE Remove_EntityAttribute
1715 *
1716 * DESCRIPTION
1717 * Remove the entity / attribute from the dictionary.
1718 *
1719 * EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
1720 *
1721 * ARGUMENTS
1722 * IN:
1723 * p_init_msg_list Initialize message stack if it is set to
1724 * FND_API.G_TRUE. Default is FND_API.G_FALSE.
1728 * OUT:
1725 * p_entity_name Entity Name
1726 * p_attribute_name Attribute Name
1727 * IN/OUT:
1729 * x_return_status Return status after the call. The status can
1730 * be FND_API.G_RET_STS_SUCCESS (success),
1731 * FND_API.G_RET_STS_ERROR (error),
1732 * FND_API.G_RET_STS_UNEXP_ERROR (unexpected error).
1733 * x_msg_count Number of messages in message stack.
1734 * x_msg_data Message text if x_msg_count is 1.
1735 *
1736 * NOTES
1737 *
1738 * MODIFICATION HISTORY
1739 *
1740 * 02-12-2002 Jianying Huang o Created.
1741 */
1742
1743 PROCEDURE Remove_EntityAttribute (
1744 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
1745 p_entity_name IN VARCHAR2,
1746 p_attribute_name IN VARCHAR2,
1747 x_return_status OUT NOCOPY VARCHAR2,
1748 x_msg_count OUT NOCOPY NUMBER,
1749 x_msg_data OUT NOCOPY VARCHAR2
1750 ) IS
1751
1752 l_entity_attr_id NUMBER;
1753 l_dummy VARCHAR2(1);
1754 l_group_name VARCHAR2(30);
1755 l_group VARCHARList;
1756 l_group_id INDEXIDList;
1757 l_total NUMBER := 1;
1758 l_debug_prefix VARCHAR2(30) := '';
1759
1760 CURSOR c_entity IS
1761 SELECT entity_attr_id
1762 FROM hz_entity_attributes
1763 WHERE entity_name = p_entity_name
1764 AND ((attribute_name IS NULL AND
1765 (p_attribute_name IS NULL OR
1766 p_attribute_name = FND_API.G_MISS_CHAR)) OR
1767 (attribute_name = p_attribute_name));
1768
1769 CURSOR c_selected_data_source (
1770 p_entity_attr_id NUMBER
1771 ) IS
1772 SELECT 'Y'
1773 FROM hz_select_data_sources
1774 WHERE entity_attr_id = p_entity_attr_id
1775 AND ranking > 0
1776 AND content_source_type <> 'USER_ENTERED'
1777 AND ROWNUM = 1;
1778
1779 BEGIN
1780
1781 -- Standard start of API savepoint
1782 SAVEPOINT Remove_EntityAttribute;
1783
1784 -- Check if API is called in debug mode. If yes, enable debug.
1785 --enable_debug;
1786
1787 -- Debug info.
1788 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
1789 hz_utility_v2pub.debug(p_message=>'Remove_EntityAttribute (+)',
1790 p_prefix=>l_debug_prefix,
1791 p_msg_level=>fnd_log.level_procedure);
1792 END IF;
1793
1794 -- Initialize message list if p_init_msg_list is set to TRUE.
1795 IF fnd_api.to_Boolean(p_init_msg_list) THEN
1796 fnd_msg_pub.initialize;
1797 END IF;
1798
1799 -- Initialize API return status to success.
1800 x_return_status := FND_API.G_RET_STS_SUCCESS;
1801
1802 -- find the entity and / or attribute in the dictionary
1803 OPEN c_entity;
1804 FETCH c_entity INTO l_entity_attr_id;
1805
1806 IF c_entity%NOTFOUND THEN
1807 fnd_message.set_name('AR', 'HZ_API_NO_RECORD');
1808 fnd_message.set_token('RECORD', 'entity attribute');
1809 fnd_message.set_token('VALUE', '<'||p_entity_name||','||
1810 NVL(p_attribute_name,'null'||'>'));
1811 fnd_msg_pub.add;
1812 RAISE FND_API.G_EXC_ERROR;
1813 END IF;
1814 CLOSE c_entity;
1815
1816 -- find the group the attribute belongs to
1817
1818 IF p_attribute_name IS NOT NULL AND
1819 p_attribute_name <> FND_API.G_MISS_CHAR
1820 THEN
1821 Find_NameListInAGroup (
1822 p_entity_name => p_entity_name,
1823 p_attribute_name => p_attribute_name,
1824 x_group_name => l_group_name,
1825 x_group => l_group,
1826 x_group_id => l_group_id);
1827 l_total := l_group.COUNT;
1828 END IF;
1829
1830 FOR i IN 1..l_total LOOP
1831 IF l_total > 1 THEN
1832 l_entity_attr_id := l_group_id(i);
1833 END IF;
1834
1835 -- find the data source. delete the entity and / or attribute
1836 -- if there is no selected data source for it.
1837
1838 OPEN c_selected_data_source(l_entity_attr_id);
1839 FETCH c_selected_data_source INTO l_dummy;
1840
1841 IF c_selected_data_source%NOTFOUND THEN
1842 -- delete the data sources.
1843 DELETE hz_select_data_sources
1844 WHERE entity_attr_id = l_entity_attr_id;
1845
1846 -- delete the entity and / or attribute.
1847 DELETE hz_entity_attributes
1848 WHERE entity_attr_id = l_entity_attr_id;
1849
1850 -- delete corresponding rules
1851 DELETE hz_user_overwrite_rules
1852 WHERE entity_attr_id = l_entity_attr_id;
1853
1854 DELETE hz_thirdparty_rule
1855 WHERE entity_attr_id = l_entity_attr_id;
1856
1857 ELSE
1858 fnd_message.set_name('AR', 'HZ_API_CANNOT_DELETE_ENTITY');
1859 fnd_message.set_token('ENTITY_ATTRIBUTE', '<'||p_entity_name||','||
1860 NVL(p_attribute_name,'null'||'>'));
1861 fnd_msg_pub.add;
1862 RAISE FND_API.G_EXC_ERROR;
1863 END IF;
1864 CLOSE c_selected_data_source;
1865 END LOOP;
1866
1867 -- Standard call to get message count and if count is 1, get message info.
1871 p_data => x_msg_data );
1868 fnd_msg_pub.Count_And_Get(
1869 p_encoded => FND_API.G_FALSE,
1870 p_count => x_msg_count,
1872
1873 -- Debug info.
1874 IF fnd_log.level_exception>=fnd_log.g_current_runtime_level THEN
1875 hz_utility_v2pub.debug_return_messages(p_msg_count=>x_msg_count,
1876 p_msg_data=>x_msg_data,
1877 p_msg_type=>'WARNING',
1878 p_msg_level=>fnd_log.level_exception);
1879 END IF;
1880 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
1881 hz_utility_v2pub.debug(p_message=>'Remove_EntityAttribute (-)',
1882 p_prefix=>l_debug_prefix,
1883 p_msg_level=>fnd_log.level_procedure);
1884 END IF;
1885
1886 -- Check if API is called in debug mode. If yes, disable debug.
1887 --disable_debug;
1888
1889 EXCEPTION
1890 WHEN FND_API.G_EXC_ERROR THEN
1891 ROLLBACK TO Remove_EntityAttribute;
1892 x_return_status := FND_API.G_RET_STS_ERROR;
1893
1894 fnd_msg_pub.Count_And_Get(
1895 p_encoded => FND_API.G_FALSE,
1896 p_count => x_msg_count,
1897 p_data => x_msg_data );
1898
1899 -- Debug info.
1900 IF fnd_log.level_error>=fnd_log.g_current_runtime_level THEN
1901 hz_utility_v2pub.debug_return_messages(p_msg_count=>x_msg_count,
1902 p_msg_data=>x_msg_data,
1903 p_msg_type=>'ERROR',
1904 p_msg_level=>fnd_log.level_error);
1905 END IF;
1906 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
1907 hz_utility_v2pub.debug(p_message=>'Remove_EntityAttribute (-)',
1908 p_prefix=>l_debug_prefix,
1909 p_msg_level=>fnd_log.level_procedure);
1910 END IF;
1911
1912 -- Check if API is called in debug mode. If yes, disable debug.
1913 --disable_debug;
1914
1915 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1916 ROLLBACK TO Remove_EntityAttribute;
1917 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1918
1919 fnd_msg_pub.Count_And_Get(
1920 p_encoded => FND_API.G_FALSE,
1921 p_count => x_msg_count,
1922 p_data => x_msg_data );
1923
1924 -- Debug info.
1925 IF fnd_log.level_error>=fnd_log.g_current_runtime_level THEN
1926 hz_utility_v2pub.debug_return_messages(p_msg_count=>x_msg_count,
1927 p_msg_data=>x_msg_data,
1928 p_msg_type=>'UNEXPECTED ERROR',
1929 p_msg_level=>fnd_log.level_error);
1930 END IF;
1931 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
1932 hz_utility_v2pub.debug(p_message=>'Remove_EntityAttribute (-)',
1933 p_prefix=>l_debug_prefix,
1934 p_msg_level=>fnd_log.level_procedure);
1935 END IF;
1936
1937 -- Check if API is called in debug mode. If yes, disable debug.
1938 --disable_debug;
1939
1940 WHEN OTHERS THEN
1941 ROLLBACK TO Remove_EntityAttribute;
1942 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1943
1944 fnd_message.set_name('AR','HZ_API_OTHERS_EXCEP');
1945 fnd_message.set_token('ERROR',SQLERRM);
1946 fnd_msg_pub.add;
1947
1948 fnd_msg_pub.Count_And_Get(
1949 p_encoded => FND_API.G_FALSE,
1950 p_count => x_msg_count,
1951 p_data => x_msg_data );
1952
1953 -- Debug info.
1954 IF fnd_log.level_error>=fnd_log.g_current_runtime_level THEN
1955 hz_utility_v2pub.debug_return_messages(p_msg_count=>x_msg_count,
1956 p_msg_data=>x_msg_data,
1957 p_msg_type=>'SQL ERROR',
1958 p_msg_level=>fnd_log.level_error);
1959 END IF;
1960 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
1961 hz_utility_v2pub.debug(p_message=>'Remove_EntityAttribute (-)',
1962 p_prefix=>l_debug_prefix,
1963 p_msg_level=>fnd_log.level_procedure);
1964 END IF;
1965
1966 -- Check if API is called in debug mode. If yes, disable debug.
1967 --disable_debug;
1968
1969 END Remove_EntityAttribute;
1970
1971 /**
1972 * PROCEDURE Remove_EntityAttrDataSource
1973 *
1974 * DESCRIPTION
1975 * Remove the entity / attribute's data sources from the dictionary.
1976 * The data sources must be un-selected.
1977 *
1978 * EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
1979 *
1980 * ARGUMENTS
1981 * IN:
1982 * p_init_msg_list Initialize message stack if it is set to
1983 * FND_API.G_TRUE. Default is FND_API.G_FALSE.
1984 * p_entity_name Entity Name
1985 * p_attribute_name Attribute Name
1986 * IN/OUT:
1987 * OUT:
1988 * x_return_status Return status after the call. The status can
1989 * be FND_API.G_RET_STS_SUCCESS (success),
1990 * FND_API.G_RET_STS_ERROR (error),
1991 * FND_API.G_RET_STS_UNEXP_ERROR (unexpected error).
1992 * x_msg_count Number of messages in message stack.
1993 * x_msg_data Message text if x_msg_count is 1.
1994 *
1995 * NOTES
1996 *
1997 * MODIFICATION HISTORY
1998 *
1999 * 02-12-2002 Jianying Huang o Created.
2000 */
2001
2005 p_attribute_name IN VARCHAR2,
2002 PROCEDURE Remove_EntityAttrDataSource (
2003 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
2004 p_entity_name IN VARCHAR2,
2006 p_data_source_tbl IN DATA_SOURCE_TBL,
2007 x_return_status OUT NOCOPY VARCHAR2,
2008 x_msg_count OUT NOCOPY NUMBER,
2009 x_msg_data OUT NOCOPY VARCHAR2
2010 ) IS
2011
2012 l_entity_attr_id NUMBER;
2013 l_dummy VARCHAR2(1);
2014 l_group_name VARCHAR2(30);
2015 l_group VARCHARList;
2016 l_group_id INDEXIDList;
2017 l_total NUMBER := 1;
2018
2019 CURSOR c_entity IS
2020 SELECT entity_attr_id
2021 FROM hz_entity_attributes
2022 WHERE entity_name = p_entity_name
2023 AND ((attribute_name IS NULL AND
2024 (p_attribute_name IS NULL OR
2025 p_attribute_name = FND_API.G_MISS_CHAR)) OR
2026 (attribute_name = p_attribute_name));
2027
2028 CURSOR c_data_source (
2029 p_entity_attr_id NUMBER,
2030 p_data_source VARCHAR2
2031 ) IS
2032 SELECT 'Y'
2033 FROM hz_select_data_sources
2034 WHERE entity_attr_id = p_entity_attr_id
2035 AND content_source_type = p_data_source
2036 AND ranking > 0;
2037
2038 i_entity_attr_id INDEXIDList;
2039 l_debug_prefix VARCHAR2(30) := '';
2040
2041 BEGIN
2042
2043 -- Standard start of API savepoint
2044 SAVEPOINT Remove_EntityAttrDataSource;
2045
2046 -- Check if API is called in debug mode. If yes, enable debug.
2047 --enable_debug;
2048
2049 -- Debug info.
2050 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
2051 hz_utility_v2pub.debug(p_message=>'Remove_EntityAttrDataSource (+)',
2052 p_prefix=>l_debug_prefix,
2053 p_msg_level=>fnd_log.level_procedure);
2054 END IF;
2055
2056 -- Initialize message list if p_init_msg_list is set to TRUE.
2057 IF fnd_api.to_Boolean(p_init_msg_list) THEN
2058 fnd_msg_pub.initialize;
2059 END IF;
2060
2061 -- Initialize API return status to success.
2062 x_return_status := FND_API.G_RET_STS_SUCCESS;
2063
2064 -- find the entity and / or attribute in the dictionary
2065 OPEN c_entity;
2066 FETCH c_entity INTO l_entity_attr_id;
2067
2068 IF c_entity%NOTFOUND THEN
2069 fnd_message.set_name('AR', 'HZ_API_NO_RECORD');
2070 fnd_message.set_token('RECORD', 'entity attribute');
2071 fnd_message.set_token('VALUE', '<'||p_entity_name||','||
2072 NVL(p_attribute_name,'null'||'>'));
2073 fnd_msg_pub.add;
2074 RAISE FND_API.G_EXC_ERROR;
2075 END IF;
2076 CLOSE c_entity;
2077
2078 -- find the group the attribute belongs to
2079
2080 IF p_attribute_name IS NOT NULL AND
2081 p_attribute_name <> FND_API.G_MISS_CHAR
2082 THEN
2083 Find_NameListInAGroup (
2084 p_entity_name => p_entity_name,
2085 p_attribute_name => p_attribute_name,
2086 x_group_name => l_group_name,
2087 x_group => l_group,
2088 x_group_id => l_group_id);
2089 l_total := l_group.COUNT;
2090 END IF;
2091
2092 FOR i IN 1..l_total LOOP
2093 IF l_total > 1 THEN
2094 l_entity_attr_id := l_group_id(i);
2095 END IF;
2096
2097 -- for each data source in the plsql table, delete it from dictionary
2098 -- if the data source has not been selected.
2099
2100 FOR i IN 1..p_data_source_tbl.COUNT LOOP
2101 OPEN c_data_source(l_entity_attr_id, p_data_source_tbl(i));
2102 FETCH c_data_source INTO l_dummy;
2103
2104 IF c_data_source%NOTFOUND THEN
2105 -- delete the data sources.
2106 DELETE hz_select_data_sources
2107 WHERE entity_attr_id = l_entity_attr_id
2108 AND content_source_type = p_data_source_tbl(i);
2109 ELSE
2110 fnd_message.set_name('AR', 'HZ_CANNOT_DELETE_ENTITY_SOURCE');
2111 fnd_message.set_token('ENTITY_ATTRIBUTE', '<'||p_entity_name||','||
2112 NVL(p_attribute_name,'null'||'>'));
2113 fnd_message.set_token('SOURCE', p_data_source_tbl(i));
2114 fnd_msg_pub.add;
2115 RAISE FND_API.G_EXC_ERROR;
2116 END IF;
2117 CLOSE c_data_source;
2118 END LOOP;
2119
2120 -- delete the entity and / or attribute if there is no selected data
2121 -- source for it.
2122
2123 DELETE hz_entity_attributes
2124 WHERE entity_attr_id = l_entity_attr_id
2125 AND NOT EXISTS (
2126 SELECT 'Y'
2127 FROM hz_select_data_sources
2128 WHERE entity_attr_id = l_entity_attr_id)
2129 RETURNING entity_attr_id BULK COLLECT INTO i_entity_attr_id;
2130
2131 -- delete corresponding rules
2132 FORALL i IN 1..i_entity_attr_id.COUNT
2133 DELETE hz_user_overwrite_rules
2134 WHERE entity_attr_id = i_entity_attr_id(i);
2135
2136 FORALL i IN 1..i_entity_attr_id.COUNT
2137 DELETE hz_thirdparty_rule
2138 WHERE entity_attr_id = i_entity_attr_id(i);
2139
2140 END LOOP;
2141
2142 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2143 RAISE FND_API.G_EXC_ERROR;
2144 END IF;
2145
2149 p_count => x_msg_count,
2146 -- Standard call to get message count and if count is 1, get message info.
2147 fnd_msg_pub.Count_And_Get(
2148 p_encoded => FND_API.G_FALSE,
2150 p_data => x_msg_data );
2151
2152 -- Debug info.
2153 IF fnd_log.level_exception>=fnd_log.g_current_runtime_level THEN
2154 hz_utility_v2pub.debug_return_messages(p_msg_count=>x_msg_count,
2155 p_msg_data=>x_msg_data,
2156 p_msg_type=>'WARNING',
2157 p_msg_level=>fnd_log.level_exception);
2158 END IF;
2159 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
2160 hz_utility_v2pub.debug(p_message=>'Remove_EntityAttrDataSource (-)',
2161 p_prefix=>l_debug_prefix,
2162 p_msg_level=>fnd_log.level_procedure);
2163 END IF;
2164
2165
2166 -- Check if API is called in debug mode. If yes, disable debug.
2167 --disable_debug;
2168
2169 EXCEPTION
2170 WHEN FND_API.G_EXC_ERROR THEN
2171 ROLLBACK TO Remove_EntityAttrDataSource;
2172 x_return_status := FND_API.G_RET_STS_ERROR;
2173
2174 fnd_msg_pub.Count_And_Get(
2175 p_encoded => FND_API.G_FALSE,
2176 p_count => x_msg_count,
2177 p_data => x_msg_data );
2178
2179 -- Debug info.
2180 IF fnd_log.level_error>=fnd_log.g_current_runtime_level THEN
2181 hz_utility_v2pub.debug_return_messages(p_msg_count=>x_msg_count,
2182 p_msg_data=>x_msg_data,
2183 p_msg_type=>'ERROR',
2184 p_msg_level=>fnd_log.level_error);
2185 END IF;
2186 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
2187 hz_utility_v2pub.debug(p_message=>'Remove_EntityAttrDataSource (-)',
2188 p_prefix=>l_debug_prefix,
2189 p_msg_level=>fnd_log.level_procedure);
2190 END IF;
2191
2192 -- Check if API is called in debug mode. If yes, disable debug.
2193 --disable_debug;
2194
2195 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2196 ROLLBACK TO Remove_EntityAttrDataSource;
2197 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2198
2199 fnd_msg_pub.Count_And_Get(
2200 p_encoded => FND_API.G_FALSE,
2201 p_count => x_msg_count,
2202 p_data => x_msg_data );
2203
2204 -- Debug info.
2205 IF fnd_log.level_error>=fnd_log.g_current_runtime_level THEN
2206 hz_utility_v2pub.debug_return_messages(p_msg_count=>x_msg_count,
2207 p_msg_data=>x_msg_data,
2208 p_msg_type=>'UNEXPECTED ERROR',
2209 p_msg_level=>fnd_log.level_error);
2210 END IF;
2211 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
2212 hz_utility_v2pub.debug(p_message=>'Remove_EntityAttrDataSource (-)',
2213 p_prefix=>l_debug_prefix,
2214 p_msg_level=>fnd_log.level_procedure);
2215 END IF;
2216
2217 -- Check if API is called in debug mode. If yes, disable debug.
2218 --disable_debug;
2219
2220 WHEN OTHERS THEN
2221 ROLLBACK TO Remove_EntityAttrDataSource;
2222 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2223
2224 fnd_message.set_name('AR','HZ_API_OTHERS_EXCEP');
2225 fnd_message.set_token('ERROR',SQLERRM);
2226 fnd_msg_pub.add;
2227
2228 fnd_msg_pub.Count_And_Get(
2229 p_encoded => FND_API.G_FALSE,
2230 p_count => x_msg_count,
2231 p_data => x_msg_data );
2232
2233 -- Debug info.
2234 IF fnd_log.level_error>=fnd_log.g_current_runtime_level THEN
2235 hz_utility_v2pub.debug_return_messages(p_msg_count=>x_msg_count,
2236 p_msg_data=>x_msg_data,
2237 p_msg_type=>'SQL ERROR',
2238 p_msg_level=>fnd_log.level_error);
2239 END IF;
2240 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
2241 hz_utility_v2pub.debug(p_message=>'Remove_EntityAttrDataSource (-)',
2242 p_prefix=>l_debug_prefix,
2243 p_msg_level=>fnd_log.level_procedure);
2244 END IF;
2245
2246 -- Check if API is called in debug mode. If yes, disable debug.
2247 --disable_debug;
2248
2249 END Remove_EntityAttrDataSource;
2250
2251 END HZ_MIXNM_REGISTRY_PUB;