[Home] [Help]
PACKAGE BODY: APPS.HZ_MIXNM_REGISTRY_PUB
Source
1 PACKAGE BODY HZ_MIXNM_REGISTRY_PUB AS
2 /*$Header: ARHXREGB.pls 120.9 2006/02/08 12:48:00 dmmehta noship $ */
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 (-)',
418
415 p_prefix=>l_debug_prefix,
416 p_msg_level=>fnd_log.level_procedure);
417 END IF;
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 *
443 * 07-23-2001 Jianying Huang o Created.
444 *
445 */
446
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';
545 LoadGroupId(G_ORG_ENTITY, x_group, G_DUNS_NUMBER_ID_GROUP);
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
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;
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);
576 END IF;
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 (
679 ) IS
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
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
717 IF x_return_status = FND_API.G_RET_STS_SUCCESS THEN
718
719 --------------------------------------
720 -- validate attribute_name
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;
801 hz_utility_v2pub.validate_lookup (
798 x_return_status := FND_API.G_RET_STS_ERROR;
799 ELSE
800 /*
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 *
855 * EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
856 *
857 * ARGUMENTS
858 * IN:
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 select column_name
900 from sys.all_tab_columns c
901 where c.table_name = 'HZ_PARTIES'
902 and c.owner = p_ar_schema
903 and exists (
904 select null
905 from sys.all_tab_columns c2
906 where c2.owner = p_ar_schema
907 and c2.column_name = c.column_name
908 and c2.table_name = 'HZ_' ||upper (p_name) || '_PROFILES')
909 and c.column_name not like 'ATTRIBUTE%'
910 and c.column_name not like 'GLOBAL_ATTRIBUTE%'
911 and c.column_name not in ('APPLICATION_ID')) party
912 where aa.object_name = 'GET_' ||upper (p_name)||'_REC'
913 and aa.data_level = 1
914 and aa.data_type <> 'PL/SQL RECORD'
915 and aa.argument_name not in ('CONTENT_SOURCE_TYPE',
916 'ACTUAL_CONTENT_SOURCE', 'APPLICATION_ID')
917 and aa.sequence > temp1.id
918 and aa.object_id in (
919 select b.object_id
920 from sys.all_objects b
921 where b.object_name = 'HZ_PARTY_V2PUB'
922 and b.owner = p_apps_schema
923 and b.object_type = 'PACKAGE')
924 and aa.argument_name = party.column_name (+)
925 order by argument_name;
926
930 l_debug_prefix VARCHAR2(30) := '';
927 l_name VARCHAR2(30);
928 i NUMBER;
929 l_raise_error BOOLEAN := FALSE;
931 l_bool BOOLEAN;
932 l_status VARCHAR2(255);
933 l_apps_schema VARCHAR2(255);
934 l_ar_schema VARCHAR2(255);
935 l_tmp VARCHAR2(2000);
936
937 BEGIN
938
939 -- Debug info.
940 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
941 hz_utility_v2pub.debug(p_message=>'Validate_Attribute (+)',
942 p_prefix=>l_debug_prefix,
943 p_msg_level=>fnd_log.level_procedure);
944 END IF;
945
946 l_apps_schema := hz_utility_v2pub.Get_AppsSchemaName;
947 l_ar_schema := hz_utility_v2pub.Get_SchemaName('AR');
948
949 IF p_entity_name = G_ORG_ENTITY THEN
950 l_name := 'ORGANIZATION';
951
952 IF G_ORG_ATTRIBUTE_NAME_TAB IS NULL OR
953 G_ORG_ATTRIBUTE_TYPE_TAB.COUNT = 0
954 THEN
955 OPEN c_attribute_name(l_name, l_apps_schema, l_ar_schema);
956 FETCH c_attribute_name BULK COLLECT INTO
957 G_ORG_ATTRIBUTE_NAME_TAB, G_ORG_ATTRIBUTE_TYPE_TAB,
958 G_ORG_DEN_ATTRIBUTE_NAME_TAB;
959 CLOSE c_attribute_name;
960
961 END IF;
962
963 i := getIndex(G_ORG_ATTRIBUTE_NAME_TAB, p_attribute_name);
964 IF i = 0 THEN
965 l_raise_error := TRUE;
966 END IF;
967 ELSE
968 l_name := 'PERSON';
969
970 IF G_PER_ATTRIBUTE_NAME_TAB IS NULL OR
971 G_PER_ATTRIBUTE_TYPE_TAB.COUNT = 0
972 THEN
973 OPEN c_attribute_name(l_name, l_apps_schema, l_ar_schema);
974 FETCH c_attribute_name BULK COLLECT INTO
975 G_PER_ATTRIBUTE_NAME_TAB, G_PER_ATTRIBUTE_TYPE_TAB,
976 G_PER_DEN_ATTRIBUTE_NAME_TAB;
977 CLOSE c_attribute_name;
978 END IF;
979
980 i := getIndex(G_PER_ATTRIBUTE_NAME_TAB, p_attribute_name);
981 IF i = 0 THEN
982 l_raise_error := TRUE;
983 END IF;
984 END IF;
985
986 IF l_raise_error THEN
987 fnd_message.set_name('AR', 'HZ_API_INVALID_ATTRIBUTE');
988 fnd_message.set_token('ATTRIBUTE', p_attribute_name);
989 fnd_message.set_token('ENTITY', l_name);
990 fnd_msg_pub.add;
991 x_return_status := fnd_api.g_ret_sts_error;
992 END IF;
993
994 -- Debug info.
995 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
996 hz_utility_v2pub.debug(p_message=>'Validate_Attribute (-)' ,
997 p_prefix=>l_debug_prefix,
998 p_msg_level=>fnd_log.level_procedure);
999 END IF;
1000
1001 END Validate_Attribute;
1002
1003 /**
1004 * PRIVATE FUNCTION getIndex
1005 *
1006 * DESCRIPTION
1007 * Returns the index of an element in an ordered varchar2 list.
1008 *
1009 * EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
1010 *
1011 * ARGUMENTS
1012 * IN:
1013 * p_list VARCHAR2 List
1014 * p_value Element Value
1015 * IN/OUT:
1016 * OUT:
1017 *
1018 * NOTES
1019 *
1020 * MODIFICATION HISTORY
1021 *
1022 * 02-12-2002 Jianying Huang o Created.
1023 */
1024
1025 FUNCTION getIndex (
1026 p_list IN VARCHARList,
1027 p_value IN VARCHAR2
1028 ) RETURN NUMBER IS
1029
1030 l_start NUMBER;
1031 l_end NUMBER;
1032 l_middle NUMBER;
1033 l_debug_prefix VARCHAR2(30) := '';
1034
1035 BEGIN
1036
1037 -- Debug info.
1038 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
1039 hz_utility_v2pub.debug(p_message=>'getIndex (+)',
1040 p_prefix=>l_debug_prefix,
1041 p_msg_level=>fnd_log.level_procedure);
1042 END IF;
1043
1044 l_start := 1; l_end := p_list.COUNT;
1045 WHILE l_start <= l_end LOOP
1046 l_middle := ROUND((l_end+l_start)/2);
1047 IF p_value = p_list(l_middle) THEN
1048 RETURN l_middle;
1049 ELSIF p_value > p_list(l_middle) THEN
1050 l_start := l_middle+1;
1051 ELSE
1052 l_end := l_middle-1;
1053 END IF;
1054 END LOOP;
1055
1056 RETURN 0;
1057
1058 -- Debug info.
1059 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
1060 hz_utility_v2pub.debug(p_message=>'getIndex (-)',
1061 p_prefix=>l_debug_prefix,
1062 p_msg_level=>fnd_log.level_procedure);
1063 END IF;
1064
1065 END getIndex;
1066
1067 /**
1068 * PRIVATE PROCEDURE db_InsertEntityAttribute
1069 *
1070 * DESCRIPTION
1074 *
1071 * Private procedure to insert entity / attribute into the table.
1072 *
1073 * EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
1075 * ARGUMENTS
1076 * IN:
1077 * p_entity_attribute_rec Entity Attribute record.
1078 * OUT:
1079 * x_entity_attr_id Dictionary ID.
1080 *
1081 * NOTES
1082 *
1083 * MODIFICATION HISTORY
1084 *
1085 * 02-12-2002 Jianying Huang o Created.
1086 * 11-24-2004 Rajib Ranjan Borah o SSM SST Integration and Extension.
1087 * User Overwrite rule and Third Party Rule
1088 * are orig_system specific. No default
1089 * records will be created in these tables
1090 * and records with overwrite_flag = 'N' will
1091 * not be stored.
1092 *
1093 */
1094
1095 PROCEDURE db_InsertEntityAttribute (
1096 p_entity_attribute_rec IN ENTITY_ATTRIBUTE_REC_TYPE,
1097 p_group_name IN VARCHAR2,
1098 x_entity_attr_id OUT NOCOPY NUMBER
1099 ) IS
1100 /*
1101 CURSOR c_user_overwrite_rule IS
1102 SELECT UNIQUE rule_id
1103 FROM hz_user_overwrite_rules;
1104
1105 i_rule_id INDEXIDList;
1106
1107 CURSOR c_third_party_rule IS
1108 SELECT 'Y'
1109 FROM hz_thirdparty_rule
1110 WHERE ROWNUM = 1;
1111
1112 l_dummy VARCHAR2(1);
1113 */
1114 l_debug_prefix VARCHAR2(30) := '';
1115
1116 BEGIN
1117
1118 -- Debug info.
1119 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
1120 hz_utility_v2pub.debug(p_message=>'db_InsertEntityAttribute (+)',
1121 p_prefix=>l_debug_prefix,
1122 p_msg_level=>fnd_log.level_procedure);
1123 END IF;
1124
1125 INSERT INTO hz_entity_attributes (
1126 entity_attr_id,
1127 entity_name,
1128 attribute_name,
1129 attribute_group_name,
1130 created_by_module,
1131 application_id,
1132 created_by,
1133 creation_date,
1134 last_update_login,
1135 last_update_date,
1136 last_updated_by
1137 ) VALUES (
1138 --
1139 -- entity_attr_id
1140 hz_entity_attributes_s.NEXTVAL,
1141 DECODE(p_entity_attribute_rec.entity_name,
1142 FND_API.G_MISS_CHAR, NULL, p_entity_attribute_rec.entity_name),
1143 DECODE(p_entity_attribute_rec.attribute_name,
1144 FND_API.G_MISS_CHAR, NULL, p_entity_attribute_rec.attribute_name),
1145 p_group_name,
1146 DECODE(p_entity_attribute_rec.created_by_module,
1147 FND_API.G_MISS_CHAR, NULL, p_entity_attribute_rec.created_by_module),
1148 DECODE(p_entity_attribute_rec.application_id,
1149 FND_API.G_MISS_NUM, NULL, p_entity_attribute_rec.application_id),
1150 hz_utility_v2pub.created_by,
1151 SYSDATE,
1152 hz_utility_v2pub.last_update_login,
1153 SYSDATE,
1154 hz_utility_v2pub.last_updated_by )
1155 RETURNING entity_attr_id INTO x_entity_attr_id;
1156
1157 /*
1158 OPEN c_user_overwrite_rule;
1159 FETCH c_user_overwrite_rule BULK COLLECT INTO i_rule_id;
1160 CLOSE c_user_overwrite_rule;
1161
1162 FORALL i IN 1..i_rule_id.COUNT
1163 INSERT INTO hz_user_overwrite_rules (
1164 rule_id,
1165 entity_attr_id,
1166 overwrite_flag,
1167 created_by,
1168 creation_date,
1169 last_update_login,
1170 last_update_date,
1171 last_updated_by
1172 ) VALUES (
1173 i_rule_id(i),
1174 x_entity_attr_id,
1175 -- by default, user can overwrite third party data.
1176 'Y',
1177 hz_utility_v2pub.created_by,
1178 SYSDATE,
1179 hz_utility_v2pub.last_update_login,
1180 SYSDATE,
1181 hz_utility_v2pub.last_updated_by );
1182 */
1183 /*
1184 OPEN c_third_party_rule;
1185 FETCH c_third_party_rule INTO l_dummy;
1186 IF c_third_party_rule%NOTFOUND THEN
1187 l_dummy := 'N';
1188 END IF;
1189 CLOSE c_third_party_rule;
1190
1191 IF l_dummy = 'Y' THEN
1192 INSERT INTO hz_thirdparty_rule (
1193 entity_attr_id,
1194 overwrite_flag,
1195 created_by,
1196 creation_date,
1197 last_update_login,
1198 last_update_date,
1199 last_updated_by
1200 )
1201 VALUES (
1202 x_entity_attr_id,
1203 -- by default, third party can not overwrite user data.
1204 'N',
1205 hz_utility_v2pub.created_by,
1206 SYSDATE,
1207 hz_utility_v2pub.last_update_login,
1208 SYSDATE,
1209 hz_utility_v2pub.last_updated_by
1210 );
1211 END IF;
1212 */
1213 -- Debug info.
1217 p_msg_level=>fnd_log.level_procedure);
1214 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
1215 hz_utility_v2pub.debug(p_message=>'db_InsertEntityAttribute (-)',
1216 p_prefix=>l_debug_prefix,
1218 END IF;
1219
1220 END db_InsertEntityAttribute;
1221
1222 /**
1223 * PRIVATE PROCEDURE db_InsertDataSource
1224 *
1225 * DESCRIPTION
1226 * Private procedure to insert data source setup into the table.
1227 *
1228 * EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
1229 *
1230 * ARGUMENTS
1231 * IN:
1232 * p_entity_attr_id Dictionary ID.
1233 * p_data_source_tab PL/SQL table for data source setup.
1234 * IN/OUT:
1235 * OUT:
1236 *
1237 * NOTES
1238 *
1239 * MODIFICATION HISTORY
1240 *
1241 * 02-12-2002 Jianying Huang o Created.
1242 * 12-12-2004 Rajib Ranjan Borah o SSM SST Integration and Extension.
1243 * If p_new_item_flag = 'O'(i.e. other entity),
1244 * set ranking to 1.
1245 *
1246 */
1247
1248 PROCEDURE db_InsertDataSource (
1249 p_new_item_flag IN VARCHAR2,
1250 p_entity_attr_id IN NUMBER,
1251 p_data_source_tab IN DATA_SOURCE_TBL
1252 ) IS
1253 l_debug_prefix VARCHAR2(30) := '';
1254 BEGIN
1255
1256 -- Debug info.
1257 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
1258 hz_utility_v2pub.debug(p_message=>'db_InsertDataSource (+)',
1259 p_prefix=>l_debug_prefix,
1260 p_msg_level=>fnd_log.level_procedure);
1261 END IF;
1262
1263 FORALL i IN 1..p_data_source_tab.COUNT
1264 INSERT INTO hz_select_data_sources (
1265 entity_attr_id,
1266 content_source_type,
1267 ranking,
1268 created_by,
1269 creation_date,
1270 last_update_login,
1271 last_update_date,
1272 last_updated_by
1273 )
1274 SELECT
1275 p_entity_attr_id,
1276 p_data_source_tab(i),
1277 --
1278 -- ranking
1279 DECODE(p_new_item_flag,
1280 'Y', DECODE(p_data_source_tab(i), 'USER_ENTERED', 1, 0),
1281 'O',1, -- For other entities.
1282 0),
1283 hz_utility_v2pub.created_by,
1284 SYSDATE,
1285 hz_utility_v2pub.last_update_login,
1286 SYSDATE,
1287 hz_utility_v2pub.last_updated_by
1288 FROM dual
1289 WHERE NOT EXISTS (
1290 SELECT 'Y'
1291 FROM hz_select_data_sources source2
1292 WHERE source2.entity_attr_id = p_entity_attr_id
1293 AND source2.content_source_type = p_data_source_tab(i));
1294
1295 -- Debug info.
1296
1297 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
1298 hz_utility_v2pub.debug(p_message=>'db_InsertDataSource (-)',
1299 p_prefix=>l_debug_prefix,
1300 p_msg_level=>fnd_log.level_procedure);
1301 END IF;
1302
1303 END db_InsertDataSource;
1304
1305 --------------------------------------
1306 -- public procedures and functions
1307 --------------------------------------
1308
1309 /**
1310 * PROCEDURE Add_EntityAttribute
1311 *
1312 * DESCRIPTION
1313 * Add the new entity and / or attribute into the dictionary.
1314 *
1315 * EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
1316 *
1317 * ARGUMENTS
1318 * IN:
1319 * p_init_msg_list Initialize message stack if it is set to
1320 * FND_API.G_TRUE. Default is FND_API.G_FALSE.
1321 * p_entity_attribute_rec Entity Attribute record.
1322 * p_data_source_tbl PL/SQL Table for Data Source Setup.
1323 * IN/OUT:
1324 * OUT:
1325 * x_entity_attr_id Dictionary ID.
1326 * x_return_status Return status after the call. The status can
1327 * be FND_API.G_RET_STS_SUCCESS (success),
1328 * FND_API.G_RET_STS_ERROR (error),
1329 * FND_API.G_RET_STS_UNEXP_ERROR (unexpected error).
1330 * x_msg_count Number of messages in message stack.
1331 * x_msg_data Message text if x_msg_count is 1.
1332 *
1333 * NOTES
1334 *
1335 * MODIFICATION HISTORY
1336 *
1337 * 02-12-2002 Jianying Huang o Created.
1338 */
1339
1340 PROCEDURE Add_EntityAttribute (
1341 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
1342 p_entity_attribute_rec IN ENTITY_ATTRIBUTE_REC_TYPE,
1343 p_data_source_tab IN DATA_SOURCE_TBL,
1344 x_entity_attr_id OUT NOCOPY NUMBER,
1345 x_return_status OUT NOCOPY VARCHAR2,
1346 x_msg_count OUT NOCOPY NUMBER,
1347 x_msg_data OUT NOCOPY VARCHAR2
1348 ) IS
1352 -- Standard start of API savepoint
1349 l_debug_prefix VARCHAR2(30) := '';
1350 BEGIN
1351
1353 SAVEPOINT Add_EntityAttribute;
1354
1355 -- Check if API is called in debug mode. If yes, enable debug.
1356 --enable_debug;
1357
1358 -- Debug info.
1359 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
1360 hz_utility_v2pub.debug(p_message=>'Add_EntityAttribute (+)',
1361 p_prefix=>l_debug_prefix,
1362 p_msg_level=>fnd_log.level_procedure);
1363 END IF;
1364
1365 -- Initialize message list if p_init_msg_list is set to TRUE.
1366 IF fnd_api.to_Boolean(p_init_msg_list) THEN
1367 fnd_msg_pub.initialize;
1368 END IF;
1369
1370 -- Initialize API return status to success.
1371 x_return_status := FND_API.G_RET_STS_SUCCESS;
1372
1373 -- Call business logic.
1374 do_AddEntityAttribute (
1375 p_entity_attribute_rec,
1376 p_data_source_tab,
1377 x_entity_attr_id,
1378 x_return_status );
1379
1380 -- Standard call to get message count and if count is 1, get message info.
1381 fnd_msg_pub.Count_And_Get(
1382 p_encoded => FND_API.G_FALSE,
1383 p_count => x_msg_count,
1384 p_data => x_msg_data );
1385
1386 -- Debug info.
1387 IF fnd_log.level_exception>=fnd_log.g_current_runtime_level THEN
1388 hz_utility_v2pub.debug_return_messages(p_msg_count=>x_msg_count,
1389 p_msg_data=>x_msg_data,
1390 p_msg_type=>'WARNING',
1391 p_msg_level=>fnd_log.level_exception);
1392 END IF;
1393 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
1394 hz_utility_v2pub.debug(p_message=>'Add_EntityAttribute (-)',
1395 p_prefix=>l_debug_prefix,
1396 p_msg_level=>fnd_log.level_procedure);
1397 END IF;
1398
1399 -- Check if API is called in debug mode. If yes, disable debug.
1400 --disable_debug;
1401
1402 EXCEPTION
1403 WHEN FND_API.G_EXC_ERROR THEN
1404 ROLLBACK TO Add_EntityAttribute;
1405 x_return_status := FND_API.G_RET_STS_ERROR;
1406
1407 fnd_msg_pub.Count_And_Get(
1408 p_encoded => FND_API.G_FALSE,
1409 p_count => x_msg_count,
1410 p_data => x_msg_data );
1411
1412 -- Debug info.
1413 IF fnd_log.level_error>=fnd_log.g_current_runtime_level THEN
1414 hz_utility_v2pub.debug_return_messages(p_msg_count=>x_msg_count,
1415 p_msg_data=>x_msg_data,
1416 p_msg_type=>'ERROR',
1417 p_msg_level=>fnd_log.level_error);
1418 END IF;
1419 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
1420 hz_utility_v2pub.debug(p_message=>'Add_EntityAttribute (-)',
1421 p_prefix=>l_debug_prefix,
1422 p_msg_level=>fnd_log.level_procedure);
1423 END IF;
1424
1425 -- Check if API is called in debug mode. If yes, disable debug.
1426 --disable_debug;
1427
1428 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1429 ROLLBACK TO Add_EntityAttribute;
1430 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1431
1432 fnd_msg_pub.Count_And_Get(
1433 p_encoded => FND_API.G_FALSE,
1434 p_count => x_msg_count,
1435 p_data => x_msg_data );
1436
1437 -- Debug info.
1438 IF fnd_log.level_error>=fnd_log.g_current_runtime_level THEN
1439 hz_utility_v2pub.debug_return_messages(p_msg_count=>x_msg_count,
1440 p_msg_data=>x_msg_data,
1441 p_msg_type=>'UNEXPECTED ERROR',
1442 p_msg_level=>fnd_log.level_error);
1443 END IF;
1444 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
1445 hz_utility_v2pub.debug(p_message=>'Add_EntityAttribute (-)',
1446 p_prefix=>l_debug_prefix,
1447 p_msg_level=>fnd_log.level_procedure);
1448 END IF;
1449
1450 -- Check if API is called in debug mode. If yes, disable debug.
1451 --disable_debug;
1452
1453 WHEN OTHERS THEN
1454 ROLLBACK TO Add_EntityAttribute;
1455 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1456
1457 fnd_message.set_name('AR','HZ_API_OTHERS_EXCEP');
1458 fnd_message.set_token('ERROR',SQLERRM);
1459 fnd_msg_pub.add;
1460
1461 fnd_msg_pub.Count_And_Get(
1462 p_encoded => FND_API.G_FALSE,
1463 p_count => x_msg_count,
1464 p_data => x_msg_data );
1465
1466 -- Debug info.
1467 IF fnd_log.level_error>=fnd_log.g_current_runtime_level THEN
1468 hz_utility_v2pub.debug_return_messages(p_msg_count=>x_msg_count,
1469 p_msg_data=>x_msg_data,
1470 p_msg_type=>'SQL ERROR',
1471 p_msg_level=>fnd_log.level_error);
1472 END IF;
1473 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
1477 END IF;
1474 hz_utility_v2pub.debug(p_message=>'Add_EntityAttribute (-)',
1475 p_prefix=>l_debug_prefix,
1476 p_msg_level=>fnd_log.level_procedure);
1478
1479 -- Check if API is called in debug mode. If yes, disable debug.
1480 --disable_debug;
1481
1482 END Add_EntityAttribute;
1483
1484 /**
1485 * PROCEDURE Get_EntityAttribute
1486 *
1487 * DESCRIPTION
1488 * Get the entity / attribute from the dictionary.
1489 *
1490 * EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
1491 *
1492 * ARGUMENTS
1493 * IN:
1494 * p_init_msg_list Initialize message stack if it is set to
1495 * FND_API.G_TRUE. Default is FND_API.G_FALSE.
1496 * p_entity_name Entity Name
1497 * p_attribute_name Attribute Name
1498 * IN/OUT:
1499 * OUT:
1500 * x_data_source_tbl PL/SQL Table for Data Source Setup.
1501 * x_return_status Return status after the call. The status can
1502 * be FND_API.G_RET_STS_SUCCESS (success),
1503 * FND_API.G_RET_STS_ERROR (error),
1504 * FND_API.G_RET_STS_UNEXP_ERROR (unexpected error).
1505 * x_msg_count Number of messages in message stack.
1506 * x_msg_data Message text if x_msg_count is 1.
1507 *
1508 * NOTES
1509 *
1510 * MODIFICATION HISTORY
1511 *
1512 * 02-12-2002 Jianying Huang o Created.
1513 */
1514
1515 PROCEDURE Get_EntityAttribute (
1516 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
1517 p_entity_name IN VARCHAR2,
1518 p_attribute_name IN VARCHAR2,
1519 x_data_source_tbl OUT NOCOPY DATA_SOURCE_TBL,
1520 x_return_status OUT NOCOPY VARCHAR2,
1521 x_msg_count OUT NOCOPY NUMBER,
1522 x_msg_data OUT NOCOPY VARCHAR2
1523 ) IS
1524
1525 l_entity_attr_id NUMBER;
1526 l_data_source_tbl DATA_SOURCE_TBL;
1527 l_debug_prefix VARCHAR2(30) := '';
1528
1529 CURSOR c_entity IS
1530 SELECT entity_attr_id
1531 FROM hz_entity_attributes
1532 WHERE entity_name = p_entity_name
1533 AND ((attribute_name IS NULL AND
1534 (p_attribute_name IS NULL OR
1535 p_attribute_name = FND_API.G_MISS_CHAR)) OR
1536 (attribute_name = p_attribute_name));
1537
1538 CURSOR c_data_sources (
1539 p_entity_attr_id NUMBER
1540 ) IS
1541 SELECT content_source_type
1542 FROM hz_select_data_sources
1543 WHERE entity_attr_id = p_entity_attr_id;
1544
1545 BEGIN
1546
1547 -- Check if API is called in debug mode. If yes, enable debug.
1548 --enable_debug;
1549
1550 -- Debug info.
1551
1552 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
1553 hz_utility_v2pub.debug(p_message=>'Get_EntityAttribute (+)',
1554 p_prefix=>l_debug_prefix,
1555 p_msg_level=>fnd_log.level_procedure);
1556 END IF;
1557
1558 -- Initialize message list if p_init_msg_list is set to TRUE.
1559 IF fnd_api.to_Boolean(p_init_msg_list) THEN
1560 fnd_msg_pub.initialize;
1561 END IF;
1562
1563 -- Initialize API return status to success.
1564 x_return_status := FND_API.G_RET_STS_SUCCESS;
1565
1566 -- find the entity and / or attribute in the dictionary
1567 OPEN c_entity;
1568 FETCH c_entity INTO l_entity_attr_id;
1569
1570 IF c_entity%NOTFOUND THEN
1571 fnd_message.set_name('AR', 'HZ_API_NO_RECORD');
1572 fnd_message.set_token('RECORD', 'entity attribute');
1573 fnd_message.set_token('VALUE', '<'||p_entity_name||','||
1574 NVL(p_attribute_name,'null'||'>'));
1575 fnd_msg_pub.add;
1576 RAISE FND_API.G_EXC_ERROR;
1577 END IF;
1578 CLOSE c_entity;
1579
1580 -- select data sources.
1581 OPEN c_data_sources(l_entity_attr_id);
1582 FETCH c_data_sources BULK COLLECT INTO l_data_source_tbl;
1583 CLOSE c_data_sources;
1584
1585 x_data_source_tbl := l_data_source_tbl;
1586
1587 -- Standard call to get message count and if count is 1, get message info.
1588 fnd_msg_pub.Count_And_Get(
1589 p_encoded => FND_API.G_FALSE,
1590 p_count => x_msg_count,
1591 p_data => x_msg_data );
1592
1593 -- Debug info.
1594
1595 IF fnd_log.level_exception>=fnd_log.g_current_runtime_level THEN
1596 hz_utility_v2pub.debug_return_messages(p_msg_count=>x_msg_count,
1597 p_msg_data=>x_msg_data,
1598 p_msg_type=>'WARNING',
1599 p_msg_level=>fnd_log.level_exception);
1600 END IF;
1601 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
1602 hz_utility_v2pub.debug(p_message=>'Get_EntityAttribute (-)',
1603 p_prefix=>l_debug_prefix,
1607
1604 p_msg_level=>fnd_log.level_procedure);
1605 END IF;
1606
1608 -- Check if API is called in debug mode. If yes, disable debug.
1609 --disable_debug;
1610
1611 EXCEPTION
1612 WHEN FND_API.G_EXC_ERROR THEN
1613 x_return_status := FND_API.G_RET_STS_ERROR;
1614
1615 fnd_msg_pub.Count_And_Get(
1616 p_encoded => FND_API.G_FALSE,
1617 p_count => x_msg_count,
1618 p_data => x_msg_data );
1619
1620 -- Debug info.
1621 IF fnd_log.level_error>=fnd_log.g_current_runtime_level THEN
1622 hz_utility_v2pub.debug_return_messages(p_msg_count=>x_msg_count,
1623 p_msg_data=>x_msg_data,
1624 p_msg_type=>'ERROR',
1625 p_msg_level=>fnd_log.level_error);
1626 END IF;
1627 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
1628 hz_utility_v2pub.debug(p_message=>'Get_EntityAttribute (-)',
1629 p_prefix=>l_debug_prefix,
1630 p_msg_level=>fnd_log.level_procedure);
1631 END IF;
1632
1633
1634 -- Check if API is called in debug mode. If yes, disable debug.
1635 --disable_debug;
1636
1637 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1638 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1639
1640 fnd_msg_pub.Count_And_Get(
1641 p_encoded => FND_API.G_FALSE,
1642 p_count => x_msg_count,
1643 p_data => x_msg_data );
1644
1645 -- Debug info.
1646 IF fnd_log.level_error>=fnd_log.g_current_runtime_level THEN
1647 hz_utility_v2pub.debug_return_messages(p_msg_count=>x_msg_count,
1648 p_msg_data=>x_msg_data,
1649 p_msg_type=>'UNEXPECTED ERROR',
1650 p_msg_level=>fnd_log.level_error);
1651 END IF;
1652 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
1653 hz_utility_v2pub.debug(p_message=>'Get_EntityAttribute (-)',
1654 p_prefix=>l_debug_prefix,
1655 p_msg_level=>fnd_log.level_procedure);
1656 END IF;
1657
1658 -- Check if API is called in debug mode. If yes, disable debug.
1659 --disable_debug;
1660
1661 WHEN OTHERS THEN
1662 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1663
1664 fnd_message.set_name('AR','HZ_API_OTHERS_EXCEP');
1665 fnd_message.set_token('ERROR',SQLERRM);
1666 fnd_msg_pub.add;
1667
1668 fnd_msg_pub.Count_And_Get(
1669 p_encoded => FND_API.G_FALSE,
1670 p_count => x_msg_count,
1671 p_data => x_msg_data );
1672
1673 -- Debug info.
1674 IF fnd_log.level_error>=fnd_log.g_current_runtime_level THEN
1675 hz_utility_v2pub.debug_return_messages(p_msg_count=>x_msg_count,
1676 p_msg_data=>x_msg_data,
1677 p_msg_type=>'SQL ERROR',
1678 p_msg_level=>fnd_log.level_error);
1679 END IF;
1680 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
1681 hz_utility_v2pub.debug(p_message=> 'Get_EntityAttribute (-)',
1682 p_prefix=>l_debug_prefix,
1683 p_msg_level=>fnd_log.level_procedure);
1684 END IF;
1685
1686 -- Check if API is called in debug mode. If yes, disable debug.
1687 --disable_debug;
1688
1689 END Get_EntityAttribute;
1690
1691 /**
1692 * PROCEDURE Remove_EntityAttribute
1693 *
1694 * DESCRIPTION
1695 * Remove the entity / attribute from the dictionary.
1696 *
1697 * EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
1698 *
1699 * ARGUMENTS
1700 * IN:
1701 * p_init_msg_list Initialize message stack if it is set to
1702 * FND_API.G_TRUE. Default is FND_API.G_FALSE.
1703 * p_entity_name Entity Name
1704 * p_attribute_name Attribute Name
1705 * IN/OUT:
1706 * OUT:
1707 * x_return_status Return status after the call. The status can
1708 * be FND_API.G_RET_STS_SUCCESS (success),
1709 * FND_API.G_RET_STS_ERROR (error),
1710 * FND_API.G_RET_STS_UNEXP_ERROR (unexpected error).
1711 * x_msg_count Number of messages in message stack.
1712 * x_msg_data Message text if x_msg_count is 1.
1713 *
1714 * NOTES
1715 *
1716 * MODIFICATION HISTORY
1717 *
1718 * 02-12-2002 Jianying Huang o Created.
1719 */
1720
1721 PROCEDURE Remove_EntityAttribute (
1722 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
1723 p_entity_name IN VARCHAR2,
1724 p_attribute_name IN VARCHAR2,
1725 x_return_status OUT NOCOPY VARCHAR2,
1726 x_msg_count OUT NOCOPY NUMBER,
1727 x_msg_data OUT NOCOPY VARCHAR2
1728 ) IS
1729
1733 l_group VARCHARList;
1730 l_entity_attr_id NUMBER;
1731 l_dummy VARCHAR2(1);
1732 l_group_name VARCHAR2(30);
1734 l_group_id INDEXIDList;
1735 l_total NUMBER := 1;
1736 l_debug_prefix VARCHAR2(30) := '';
1737
1738 CURSOR c_entity IS
1739 SELECT entity_attr_id
1740 FROM hz_entity_attributes
1741 WHERE entity_name = p_entity_name
1742 AND ((attribute_name IS NULL AND
1743 (p_attribute_name IS NULL OR
1744 p_attribute_name = FND_API.G_MISS_CHAR)) OR
1745 (attribute_name = p_attribute_name));
1746
1747 CURSOR c_selected_data_source (
1748 p_entity_attr_id NUMBER
1749 ) IS
1750 SELECT 'Y'
1751 FROM hz_select_data_sources
1752 WHERE entity_attr_id = p_entity_attr_id
1753 AND ranking > 0
1754 AND content_source_type <> 'USER_ENTERED'
1755 AND ROWNUM = 1;
1756
1757 BEGIN
1758
1759 -- Standard start of API savepoint
1760 SAVEPOINT Remove_EntityAttribute;
1761
1762 -- Check if API is called in debug mode. If yes, enable debug.
1763 --enable_debug;
1764
1765 -- Debug info.
1766 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
1767 hz_utility_v2pub.debug(p_message=>'Remove_EntityAttribute (+)',
1768 p_prefix=>l_debug_prefix,
1769 p_msg_level=>fnd_log.level_procedure);
1770 END IF;
1771
1772 -- Initialize message list if p_init_msg_list is set to TRUE.
1773 IF fnd_api.to_Boolean(p_init_msg_list) THEN
1774 fnd_msg_pub.initialize;
1775 END IF;
1776
1777 -- Initialize API return status to success.
1778 x_return_status := FND_API.G_RET_STS_SUCCESS;
1779
1780 -- find the entity and / or attribute in the dictionary
1781 OPEN c_entity;
1782 FETCH c_entity INTO l_entity_attr_id;
1783
1784 IF c_entity%NOTFOUND THEN
1785 fnd_message.set_name('AR', 'HZ_API_NO_RECORD');
1786 fnd_message.set_token('RECORD', 'entity attribute');
1787 fnd_message.set_token('VALUE', '<'||p_entity_name||','||
1788 NVL(p_attribute_name,'null'||'>'));
1789 fnd_msg_pub.add;
1790 RAISE FND_API.G_EXC_ERROR;
1791 END IF;
1792 CLOSE c_entity;
1793
1794 -- find the group the attribute belongs to
1795
1796 IF p_attribute_name IS NOT NULL AND
1797 p_attribute_name <> FND_API.G_MISS_CHAR
1798 THEN
1799 Find_NameListInAGroup (
1800 p_entity_name => p_entity_name,
1801 p_attribute_name => p_attribute_name,
1802 x_group_name => l_group_name,
1803 x_group => l_group,
1804 x_group_id => l_group_id);
1805 l_total := l_group.COUNT;
1806 END IF;
1807
1808 FOR i IN 1..l_total LOOP
1809 IF l_total > 1 THEN
1810 l_entity_attr_id := l_group_id(i);
1811 END IF;
1812
1813 -- find the data source. delete the entity and / or attribute
1814 -- if there is no selected data source for it.
1815
1816 OPEN c_selected_data_source(l_entity_attr_id);
1817 FETCH c_selected_data_source INTO l_dummy;
1818
1819 IF c_selected_data_source%NOTFOUND THEN
1820 -- delete the data sources.
1821 DELETE hz_select_data_sources
1822 WHERE entity_attr_id = l_entity_attr_id;
1823
1824 -- delete the entity and / or attribute.
1825 DELETE hz_entity_attributes
1826 WHERE entity_attr_id = l_entity_attr_id;
1827
1828 -- delete corresponding rules
1829 DELETE hz_user_overwrite_rules
1830 WHERE entity_attr_id = l_entity_attr_id;
1831
1832 DELETE hz_thirdparty_rule
1833 WHERE entity_attr_id = l_entity_attr_id;
1834
1835 ELSE
1836 fnd_message.set_name('AR', 'HZ_API_CANNOT_DELETE_ENTITY');
1837 fnd_message.set_token('ENTITY_ATTRIBUTE', '<'||p_entity_name||','||
1838 NVL(p_attribute_name,'null'||'>'));
1839 fnd_msg_pub.add;
1840 RAISE FND_API.G_EXC_ERROR;
1841 END IF;
1842 CLOSE c_selected_data_source;
1843 END LOOP;
1844
1845 -- Standard call to get message count and if count is 1, get message info.
1846 fnd_msg_pub.Count_And_Get(
1847 p_encoded => FND_API.G_FALSE,
1848 p_count => x_msg_count,
1849 p_data => x_msg_data );
1850
1851 -- Debug info.
1852 IF fnd_log.level_exception>=fnd_log.g_current_runtime_level THEN
1853 hz_utility_v2pub.debug_return_messages(p_msg_count=>x_msg_count,
1854 p_msg_data=>x_msg_data,
1855 p_msg_type=>'WARNING',
1856 p_msg_level=>fnd_log.level_exception);
1857 END IF;
1858 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
1859 hz_utility_v2pub.debug(p_message=>'Remove_EntityAttribute (-)',
1860 p_prefix=>l_debug_prefix,
1861 p_msg_level=>fnd_log.level_procedure);
1862 END IF;
1863
1864 -- Check if API is called in debug mode. If yes, disable debug.
1865 --disable_debug;
1869 ROLLBACK TO Remove_EntityAttribute;
1866
1867 EXCEPTION
1868 WHEN FND_API.G_EXC_ERROR THEN
1870 x_return_status := FND_API.G_RET_STS_ERROR;
1871
1872 fnd_msg_pub.Count_And_Get(
1873 p_encoded => FND_API.G_FALSE,
1874 p_count => x_msg_count,
1875 p_data => x_msg_data );
1876
1877 -- Debug info.
1878 IF fnd_log.level_error>=fnd_log.g_current_runtime_level THEN
1879 hz_utility_v2pub.debug_return_messages(p_msg_count=>x_msg_count,
1880 p_msg_data=>x_msg_data,
1881 p_msg_type=>'ERROR',
1882 p_msg_level=>fnd_log.level_error);
1883 END IF;
1884 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
1885 hz_utility_v2pub.debug(p_message=>'Remove_EntityAttribute (-)',
1886 p_prefix=>l_debug_prefix,
1887 p_msg_level=>fnd_log.level_procedure);
1888 END IF;
1889
1890 -- Check if API is called in debug mode. If yes, disable debug.
1891 --disable_debug;
1892
1893 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1894 ROLLBACK TO Remove_EntityAttribute;
1895 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1896
1897 fnd_msg_pub.Count_And_Get(
1898 p_encoded => FND_API.G_FALSE,
1899 p_count => x_msg_count,
1900 p_data => x_msg_data );
1901
1902 -- Debug info.
1903 IF fnd_log.level_error>=fnd_log.g_current_runtime_level THEN
1904 hz_utility_v2pub.debug_return_messages(p_msg_count=>x_msg_count,
1905 p_msg_data=>x_msg_data,
1906 p_msg_type=>'UNEXPECTED ERROR',
1907 p_msg_level=>fnd_log.level_error);
1908 END IF;
1909 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
1910 hz_utility_v2pub.debug(p_message=>'Remove_EntityAttribute (-)',
1911 p_prefix=>l_debug_prefix,
1912 p_msg_level=>fnd_log.level_procedure);
1913 END IF;
1914
1915 -- Check if API is called in debug mode. If yes, disable debug.
1916 --disable_debug;
1917
1918 WHEN OTHERS THEN
1919 ROLLBACK TO Remove_EntityAttribute;
1920 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1921
1922 fnd_message.set_name('AR','HZ_API_OTHERS_EXCEP');
1923 fnd_message.set_token('ERROR',SQLERRM);
1924 fnd_msg_pub.add;
1925
1926 fnd_msg_pub.Count_And_Get(
1927 p_encoded => FND_API.G_FALSE,
1928 p_count => x_msg_count,
1929 p_data => x_msg_data );
1930
1931 -- Debug info.
1932 IF fnd_log.level_error>=fnd_log.g_current_runtime_level THEN
1933 hz_utility_v2pub.debug_return_messages(p_msg_count=>x_msg_count,
1934 p_msg_data=>x_msg_data,
1935 p_msg_type=>'SQL ERROR',
1936 p_msg_level=>fnd_log.level_error);
1937 END IF;
1938 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
1939 hz_utility_v2pub.debug(p_message=>'Remove_EntityAttribute (-)',
1940 p_prefix=>l_debug_prefix,
1941 p_msg_level=>fnd_log.level_procedure);
1942 END IF;
1943
1944 -- Check if API is called in debug mode. If yes, disable debug.
1945 --disable_debug;
1946
1947 END Remove_EntityAttribute;
1948
1949 /**
1950 * PROCEDURE Remove_EntityAttrDataSource
1951 *
1952 * DESCRIPTION
1953 * Remove the entity / attribute's data sources from the dictionary.
1954 * The data sources must be un-selected.
1955 *
1956 * EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
1957 *
1958 * ARGUMENTS
1959 * IN:
1960 * p_init_msg_list Initialize message stack if it is set to
1961 * FND_API.G_TRUE. Default is FND_API.G_FALSE.
1962 * p_entity_name Entity Name
1963 * p_attribute_name Attribute Name
1964 * IN/OUT:
1965 * OUT:
1966 * x_return_status Return status after the call. The status can
1967 * be FND_API.G_RET_STS_SUCCESS (success),
1968 * FND_API.G_RET_STS_ERROR (error),
1969 * FND_API.G_RET_STS_UNEXP_ERROR (unexpected error).
1970 * x_msg_count Number of messages in message stack.
1971 * x_msg_data Message text if x_msg_count is 1.
1972 *
1973 * NOTES
1974 *
1975 * MODIFICATION HISTORY
1976 *
1977 * 02-12-2002 Jianying Huang o Created.
1978 */
1979
1980 PROCEDURE Remove_EntityAttrDataSource (
1981 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
1982 p_entity_name IN VARCHAR2,
1983 p_attribute_name IN VARCHAR2,
1984 p_data_source_tbl IN DATA_SOURCE_TBL,
1985 x_return_status OUT NOCOPY VARCHAR2,
1986 x_msg_count OUT NOCOPY NUMBER,
1987 x_msg_data OUT NOCOPY VARCHAR2
1988 ) IS
1989
1993 l_group VARCHARList;
1990 l_entity_attr_id NUMBER;
1991 l_dummy VARCHAR2(1);
1992 l_group_name VARCHAR2(30);
1994 l_group_id INDEXIDList;
1995 l_total NUMBER := 1;
1996
1997 CURSOR c_entity IS
1998 SELECT entity_attr_id
1999 FROM hz_entity_attributes
2000 WHERE entity_name = p_entity_name
2001 AND ((attribute_name IS NULL AND
2002 (p_attribute_name IS NULL OR
2003 p_attribute_name = FND_API.G_MISS_CHAR)) OR
2004 (attribute_name = p_attribute_name));
2005
2006 CURSOR c_data_source (
2007 p_entity_attr_id NUMBER,
2008 p_data_source VARCHAR2
2009 ) IS
2010 SELECT 'Y'
2011 FROM hz_select_data_sources
2012 WHERE entity_attr_id = p_entity_attr_id
2013 AND content_source_type = p_data_source
2014 AND ranking > 0;
2015
2016 i_entity_attr_id INDEXIDList;
2017 l_debug_prefix VARCHAR2(30) := '';
2018
2019 BEGIN
2020
2021 -- Standard start of API savepoint
2022 SAVEPOINT Remove_EntityAttrDataSource;
2023
2024 -- Check if API is called in debug mode. If yes, enable debug.
2025 --enable_debug;
2026
2027 -- Debug info.
2028 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
2029 hz_utility_v2pub.debug(p_message=>'Remove_EntityAttrDataSource (+)',
2030 p_prefix=>l_debug_prefix,
2031 p_msg_level=>fnd_log.level_procedure);
2032 END IF;
2033
2034 -- Initialize message list if p_init_msg_list is set to TRUE.
2035 IF fnd_api.to_Boolean(p_init_msg_list) THEN
2036 fnd_msg_pub.initialize;
2037 END IF;
2038
2039 -- Initialize API return status to success.
2040 x_return_status := FND_API.G_RET_STS_SUCCESS;
2041
2042 -- find the entity and / or attribute in the dictionary
2043 OPEN c_entity;
2044 FETCH c_entity INTO l_entity_attr_id;
2045
2046 IF c_entity%NOTFOUND THEN
2047 fnd_message.set_name('AR', 'HZ_API_NO_RECORD');
2048 fnd_message.set_token('RECORD', 'entity attribute');
2049 fnd_message.set_token('VALUE', '<'||p_entity_name||','||
2050 NVL(p_attribute_name,'null'||'>'));
2051 fnd_msg_pub.add;
2052 RAISE FND_API.G_EXC_ERROR;
2053 END IF;
2054 CLOSE c_entity;
2055
2056 -- find the group the attribute belongs to
2057
2058 IF p_attribute_name IS NOT NULL AND
2059 p_attribute_name <> FND_API.G_MISS_CHAR
2060 THEN
2061 Find_NameListInAGroup (
2062 p_entity_name => p_entity_name,
2063 p_attribute_name => p_attribute_name,
2064 x_group_name => l_group_name,
2065 x_group => l_group,
2066 x_group_id => l_group_id);
2067 l_total := l_group.COUNT;
2068 END IF;
2069
2070 FOR i IN 1..l_total LOOP
2071 IF l_total > 1 THEN
2072 l_entity_attr_id := l_group_id(i);
2073 END IF;
2074
2075 -- for each data source in the plsql table, delete it from dictionary
2076 -- if the data source has not been selected.
2077
2078 FOR i IN 1..p_data_source_tbl.COUNT LOOP
2079 OPEN c_data_source(l_entity_attr_id, p_data_source_tbl(i));
2080 FETCH c_data_source INTO l_dummy;
2081
2082 IF c_data_source%NOTFOUND THEN
2083 -- delete the data sources.
2084 DELETE hz_select_data_sources
2085 WHERE entity_attr_id = l_entity_attr_id
2086 AND content_source_type = p_data_source_tbl(i);
2087 ELSE
2088 fnd_message.set_name('AR', 'HZ_CANNOT_DELETE_ENTITY_SOURCE');
2089 fnd_message.set_token('ENTITY_ATTRIBUTE', '<'||p_entity_name||','||
2090 NVL(p_attribute_name,'null'||'>'));
2091 fnd_message.set_token('SOURCE', p_data_source_tbl(i));
2092 fnd_msg_pub.add;
2093 RAISE FND_API.G_EXC_ERROR;
2094 END IF;
2095 CLOSE c_data_source;
2096 END LOOP;
2097
2098 -- delete the entity and / or attribute if there is no selected data
2099 -- source for it.
2100
2101 DELETE hz_entity_attributes
2102 WHERE entity_attr_id = l_entity_attr_id
2103 AND NOT EXISTS (
2104 SELECT 'Y'
2105 FROM hz_select_data_sources
2106 WHERE entity_attr_id = l_entity_attr_id)
2107 RETURNING entity_attr_id BULK COLLECT INTO i_entity_attr_id;
2108
2109 -- delete corresponding rules
2110 FORALL i IN 1..i_entity_attr_id.COUNT
2111 DELETE hz_user_overwrite_rules
2112 WHERE entity_attr_id = i_entity_attr_id(i);
2113
2114 FORALL i IN 1..i_entity_attr_id.COUNT
2115 DELETE hz_thirdparty_rule
2116 WHERE entity_attr_id = i_entity_attr_id(i);
2117
2118 END LOOP;
2119
2120 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2121 RAISE FND_API.G_EXC_ERROR;
2122 END IF;
2123
2124 -- Standard call to get message count and if count is 1, get message info.
2125 fnd_msg_pub.Count_And_Get(
2131 IF fnd_log.level_exception>=fnd_log.g_current_runtime_level THEN
2126 p_encoded => FND_API.G_FALSE,
2127 p_count => x_msg_count,
2128 p_data => x_msg_data );
2129
2130 -- Debug info.
2132 hz_utility_v2pub.debug_return_messages(p_msg_count=>x_msg_count,
2133 p_msg_data=>x_msg_data,
2134 p_msg_type=>'WARNING',
2135 p_msg_level=>fnd_log.level_exception);
2136 END IF;
2137 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
2138 hz_utility_v2pub.debug(p_message=>'Remove_EntityAttrDataSource (-)',
2139 p_prefix=>l_debug_prefix,
2140 p_msg_level=>fnd_log.level_procedure);
2141 END IF;
2142
2143
2144 -- Check if API is called in debug mode. If yes, disable debug.
2145 --disable_debug;
2146
2147 EXCEPTION
2151
2148 WHEN FND_API.G_EXC_ERROR THEN
2149 ROLLBACK TO Remove_EntityAttrDataSource;
2150 x_return_status := FND_API.G_RET_STS_ERROR;
2152 fnd_msg_pub.Count_And_Get(
2153 p_encoded => FND_API.G_FALSE,
2154 p_count => x_msg_count,
2155 p_data => x_msg_data );
2156
2157 -- Debug info.
2158 IF fnd_log.level_error>=fnd_log.g_current_runtime_level THEN
2159 hz_utility_v2pub.debug_return_messages(p_msg_count=>x_msg_count,
2160 p_msg_data=>x_msg_data,
2161 p_msg_type=>'ERROR',
2162 p_msg_level=>fnd_log.level_error);
2163 END IF;
2164 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
2165 hz_utility_v2pub.debug(p_message=>'Remove_EntityAttrDataSource (-)',
2166 p_prefix=>l_debug_prefix,
2167 p_msg_level=>fnd_log.level_procedure);
2168 END IF;
2169
2170 -- Check if API is called in debug mode. If yes, disable debug.
2171 --disable_debug;
2172
2173 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2174 ROLLBACK TO Remove_EntityAttrDataSource;
2175 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2176
2177 fnd_msg_pub.Count_And_Get(
2178 p_encoded => FND_API.G_FALSE,
2179 p_count => x_msg_count,
2180 p_data => x_msg_data );
2181
2182 -- Debug info.
2183 IF fnd_log.level_error>=fnd_log.g_current_runtime_level THEN
2184 hz_utility_v2pub.debug_return_messages(p_msg_count=>x_msg_count,
2185 p_msg_data=>x_msg_data,
2186 p_msg_type=>'UNEXPECTED ERROR',
2187 p_msg_level=>fnd_log.level_error);
2188 END IF;
2189 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
2190 hz_utility_v2pub.debug(p_message=>'Remove_EntityAttrDataSource (-)',
2191 p_prefix=>l_debug_prefix,
2192 p_msg_level=>fnd_log.level_procedure);
2193 END IF;
2194
2195 -- Check if API is called in debug mode. If yes, disable debug.
2196 --disable_debug;
2197
2198 WHEN OTHERS THEN
2199 ROLLBACK TO Remove_EntityAttrDataSource;
2200 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2201
2202 fnd_message.set_name('AR','HZ_API_OTHERS_EXCEP');
2203 fnd_message.set_token('ERROR',SQLERRM);
2204 fnd_msg_pub.add;
2205
2206 fnd_msg_pub.Count_And_Get(
2207 p_encoded => FND_API.G_FALSE,
2208 p_count => x_msg_count,
2209 p_data => x_msg_data );
2210
2211 -- Debug info.
2212 IF fnd_log.level_error>=fnd_log.g_current_runtime_level THEN
2213 hz_utility_v2pub.debug_return_messages(p_msg_count=>x_msg_count,
2214 p_msg_data=>x_msg_data,
2215 p_msg_type=>'SQL ERROR',
2216 p_msg_level=>fnd_log.level_error);
2217 END IF;
2218 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
2219 hz_utility_v2pub.debug(p_message=>'Remove_EntityAttrDataSource (-)',
2220 p_prefix=>l_debug_prefix,
2221 p_msg_level=>fnd_log.level_procedure);
2222 END IF;
2223
2224 -- Check if API is called in debug mode. If yes, disable debug.
2225 --disable_debug;
2226
2227 END Remove_EntityAttrDataSource;
2228
2229 END HZ_MIXNM_REGISTRY_PUB;