[Home] [Help]
PACKAGE BODY: APPS.HZ_MIXNM_UTILITY
Source
1 PACKAGE BODY HZ_MIXNM_UTILITY AS
2 /*$Header: ARHXUTLB.pls 120.24 2005/11/28 07:22:38 dmmehta noship $ */
3
4 --------------------------------------------------------------------------
5 -- declaration of private types
6 --------------------------------------------------------------------------
7
8 TYPE INDEXVARCHAR400List IS TABLE OF VARCHAR2(400) INDEX BY BINARY_INTEGER;
9 TYPE INDEXIDList IS TABLE OF NUMBER(15) INDEX BY BINARY_INTEGER;
10
11 --------------------------------------------------------------------------
12 -- declaration of private global varibles
13 --------------------------------------------------------------------------
14
15 -- for debug purpose
16 g_debug_count NUMBER := 0;
17 --g_debug BOOLEAN := FALSE;
18
19 -- default value of content source type
20 G_MISS_CONTENT_SOURCE_TYPE CONSTANT VARCHAR2(30) := 'USER_ENTERED';
21 G_MISS_ACTUAL_CONTENT_SOURCE CONSTANT VARCHAR2(30) := 'SST';
22
23 -- entity id for person and organization profiles
24 G_PERSON_PROFILE_ID CONSTANT NUMBER := -1;
25 G_ORGANIZATION_PROFILE_ID CONSTANT NUMBER := -2;
26
27 -- schema name
28 G_AR_SCHEMA_NAME VARCHAR2(30);
29 G_APPS_SCHEMA_NAME VARCHAR2(30);
30
31 -- flag to indicate if perticular setup has been loaded into memory
32 -- G_ORG_SETUP_LAST_UPDATE_DATE DATE;
33 -- G_PER_SETUP_LAST_UPDATE_DATE DATE;
34 -- G_DATASOURCE_LAST_UPDATE_DATE DATE;
35 G_ORG_SETUP_LOADED VARCHAR2(1) := 'N';
36 G_PER_SETUP_LOADED VARCHAR2(1) := 'N';
37 G_DATASOURCE_LOADED VARCHAR2(1) := 'N';
38
39 -- cached party id
40 --G_PARTY_ID NUMBER;
41
42 -- cached overwrite third party rule id
43 G_OVERWRITE_THIRD_PARTY_RULE NUMBER;
44
45 -- cache user creation rule id
46 G_CREATE_USER_ENTERED_RULE NUMBER;
47
48 -- attribute name and id list for party profiles
49 G_ORG_ATTRIBUTE_NAME INDEXVARCHAR30List;
50 G_ORG_ATTRIBUTE_ID INDEXIDList;
51 G_PER_ATTRIBUTE_NAME INDEXVARCHAR30List;
52 G_PER_ATTRIBUTE_ID INDEXIDList;
53
54 -- attribute available data sources
55 G_ATTRIBUTE_DATA_SOURCE INDEXVARCHAR400List;
56
57 -- real data source
58 G_REAL_DATA_SOURCE INDEXVARCHAR30List;
59
60 -- exception ist
61 G_EXCEPTION_TYPE INDEXVARCHAR30List;
62
63 -- entity name and id list
64 G_ENTITY_NAME INDEXVARCHAR30List;
65 G_ENTITY_ID INDEXIDList;
66
67 -- entity available data source
68 G_ENTITY_DATA_SOURCE INDEXVARCHAR400List;
69
70 -- overwrite third party rule setup
71 G_OVERWRITE_THIRD_PARTY INDEXVARCHAR400List;
72
73 -- overwrite user rule setup
74 G_OVERWRITE_USER_RULE INDEXVARCHAR400List;
75
76 -- user creation rule setup
77 G_CREATE_USER_ENTERED INDEXVARCHAR1List;
78
79 -- a list to cache if mix-n-match is enabled
80 G_MIXNM_ENABLED_FLAG INDEXVARCHAR1List;
81
82 -- SSM SST Integration and Extension
83 -- List of valid Content Sources.
84 G_ORIG_SYSTEM_LIST INDEXVARCHAR30List;
85 G_ORIG_SYSTEM_LIST_LOADED VARCHAR2(1) := 'N';
86
87 -- Overwrite third party data by user(for other entities)
88 G_OTHER_ENT_USER_OVERWRITE INDEXVARCHAR400List;
89
90 --------------------------------------------------------------------------
91 -- declaration of private procedures and functions
92 --------------------------------------------------------------------------
93
94 FUNCTION get_max (
95 p_value1 IN NUMBER,
96 p_value2 IN NUMBER,
97 p_value3 IN NUMBER := NULL,
98 p_value4 IN NUMBER := NULL,
99 p_value5 IN NUMBER := NULL
100 ) RETURN NUMBER;
101
102 PROCEDURE cacheSetupForPartyProfiles (
103 p_party_id IN NUMBER := NULL,
104 p_entity_name IN VARCHAR2 := NULL
105 );
106
107 PROCEDURE cacheSetupForOtherEntities (
108 p_load_rule IN BOOLEAN := FALSE
109 );
110
111 FUNCTION isThirdPartyDataOverwriteable (
112 p_entity_attr_id IN NUMBER,
113 p_orig_system IN VARCHAR2
114 ) RETURN VARCHAR2;
115
116 FUNCTION isUserDataOverwriteable (
117 p_entity_attr_id IN NUMBER,
118 p_orig_system IN VARCHAR2
119 ) RETURN VARCHAR2;
120
121 FUNCTION getDataSourceRanking (
122 p_entity_attr_id IN NUMBER,
123 p_data_source_type IN VARCHAR2
124 ) RETURN NUMBER;
125
126 FUNCTION getIndex (
127 p_list IN INDEXVARCHAR30List,
128 p_name IN VARCHAR2
129 ) RETURN NUMBER;
130
131 FUNCTION getEntityAttrId (
132 p_entity_name IN VARCHAR2,
133 p_attribute_name IN VARCHAR2 := NULL
134 ) RETURN NUMBER;
135
136 FUNCTION isSSTColumnUpdatable (
137 p_party_id IN NUMBER,
138 p_entity_name IN VARCHAR2,
139 p_attribute_name IN VARCHAR2,
140 p_entity_attr_id IN NUMBER,
141 p_value_is_null IN VARCHAR2,
142 p_data_source_type IN VARCHAR2,
143 x_exception_type OUT NOCOPY VARCHAR2,
144 p_is_null IN VARCHAR2
145 ) RETURN VARCHAR2;
146
147 FUNCTION isSSTColumnUpdatable (
148 p_party_id IN NUMBER,
149 p_entity_attr_id IN NUMBER,
150 p_real_data_source_type IN VARCHAR2,
151 p_real_data_source_ranking IN NUMBER,
152 p_new_data_source_type IN VARCHAR2,
153 p_new_data_source_ranking IN NUMBER,
154 p_exception_type IN OUT NOCOPY VARCHAR2
155 ) RETURN VARCHAR2;
156
157 PROCEDURE areSSTColumnsUpdeable (
158 p_party_id IN NUMBER,
159 p_entity_name IN VARCHAR2,
160 p_attribute_name_list IN INDEXVARCHAR30List,
161 p_value_is_null_list IN INDEXVARCHAR1List,
162 p_data_source_type IN VARCHAR2 := G_MISS_ACTUAL_CONTENT_SOURCE,
163 x_updatable_flag_list OUT NOCOPY INDEXVARCHAR1List,
164 x_exception_type_list OUT NOCOPY INDEXVARCHAR30List,
165 x_return_status IN OUT NOCOPY VARCHAR2,
166 p_raise_error_flag IN VARCHAR2 DEFAULT 'N',
167 p_known_dict_id IN VARCHAR2 DEFAULT 'N',
168 p_new_value_is_null_list IN HZ_MIXNM_UTILITY.INDEXVARCHAR1List
169 );
170
171 PROCEDURE updateExceptions (
172 p_create_update_sst_flag IN VARCHAR2,
173 p_party_id IN NUMBER,
174 p_data_source_type IN VARCHAR2,
175 p_name_list IN INDEXVARCHAR30List,
176 p_updatable_flag_list IN INDEXVARCHAR1List,
177 p_exception_type_list IN INDEXVARCHAR30List,
178 p_sst_value_is_not_null_list IN INDEXVARCHAR1List,
179 p_data_source_list IN INDEXVARCHAR30List
180 );
181
182 --------------------------------------------------------------------------
183 -- debug procedures
184 --------------------------------------------------------------------------
185
186 /**
187 * PRIVATE PROCEDURE enable_debug
188 *
189 * DESCRIPTION
190 * Turn on debug mode.
191 *
192 * EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
193 * HZ_UTILITY_V2PUB.enable_debug
194 *
195 * MODIFICATION HISTORY
196 *
197 * 07-23-2001 Jianying Huang o Created.
198 *
199 */
200
201 /*PROCEDURE enable_debug IS
202 BEGIN
203 g_debug_count := g_debug_count + 1;
204
205 IF g_debug_count = 1 THEN
206 IF fnd_profile.value('HZ_API_FILE_DEBUG_ON') = 'Y' OR
207 fnd_profile.value('HZ_API_DBMS_DEBUG_ON') = 'Y'
208 THEN
209 hz_utility_v2pub.enable_debug;
210 g_debug := TRUE;
211 END IF;
212 END IF;
213 END enable_debug;
214 */
215
216 /**
217 * PRIVATE PROCEDURE disable_debug
218 *
219 * DESCRIPTION
220 * Turn off debug mode.
221 *
222 * EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
223 * hz_utility_v2pub.disable_debug
224 *
225 * MODIFICATION HISTORY
226 *
227 * 07-23-2001 Jianying Huang o Created.
228 *
229 */
230
231 /*PROCEDURE disable_debug IS
232 BEGIN
233 g_debug_count := g_debug_count - 1;
234
235 IF g_debug THEN
236 IF g_debug_count = 0 THEN
237 hz_utility_v2pub.disable_debug;
238 g_debug := FALSE;
239 END IF;
240 END IF;
241 END disable_debug;
242 */
243
244 --------------------------------------------------------------------------
245 -- private procedures and functions
246 --------------------------------------------------------------------------
247
248 /**
249 * PRIVATE FUNCTION get_max
250 *
251 * DESCRIPTION
252 * Return max value.
253 *
254 * EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
255 *
256 * IN:
257 * p_value1 Value 1.
258 * p_value2 Value 2.
259 * p_value3 Value 3.
260 * p_value4 Value 4.
261 * p_value5 Value 5.
262 *
263 * MODIFICATION HISTORY
264 *
265 * 06-23-2002 Jianying Huang o Created.
266 *
267 */
268
269 FUNCTION get_max (
270 p_value1 IN NUMBER,
271 p_value2 IN NUMBER,
272 p_value3 IN NUMBER := NULL,
273 p_value4 IN NUMBER := NULL,
274 p_value5 IN NUMBER := NULL
275 ) RETURN NUMBER IS
276
277 l_max NUMBER := 0;
278
279 BEGIN
280
281 IF p_value1 IS NOT NULL AND p_value1 > l_max THEN l_max := p_value1; END IF;
282 IF p_value2 IS NOT NULL AND p_value2 > l_max THEN l_max := p_value2; END IF;
283 IF p_value3 IS NOT NULL AND p_value3 > l_max THEN l_max := p_value3; END IF;
284 IF p_value4 IS NOT NULL AND p_value4 > l_max THEN l_max := p_value4; END IF;
285 IF p_value5 IS NOT NULL AND p_value5 > l_max THEN l_max := p_value5; END IF;
286
287 RETURN l_max;
288
289 END get_max;
290
291 /**
292 * PRIVATE PROCEDURE cacheSetupForPartyProfiles
293 *
294 * DESCRIPTION
295 * cache mix-n-match setup for performance reason.
296 *
297 * EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
298 *
299 * IN:
300 * p_party_id Party id.
301 * p_entity_name Entity name.
302 *
303 * MODIFICATION HISTORY
304 *
305 * 04-30-2002 Jianying Huang o Created.
306 * 12-27-2004 Rajib Ranjan Borah o SSM SST Integration and Extension.
307 * Changed caching logic for G_OVERWRITE_THIRD_PARTY and
308 * G_OVERWRITE_USER_RULE.
309 * o Cached content sources in G_ORIG_SYSTEM_LIST.
310 * o Cursors c_overwrite_third_party_rule and
311 * c_overwrite_user_rule are modified as now these
312 * rules are orig system specific.
313 * o Cursors c_data_source, c_party_data_sources1,
314 * c_party_data_sources2 are modified as rank = -1
315 * is possible for selected data sources(Ranking method = date).
316 * 02-28-2005 Rajib Ranjan Borah o Bug 4156090. Changed the caching logic for
317 * G_ATTRIBUTE_DATA_SOURCE.
318 * Order by is removed from cursor c_data_source.
319 */
320
321 PROCEDURE cacheSetupForPartyProfiles (
322 p_party_id IN NUMBER := NULL,
323 p_entity_name IN VARCHAR2 := NULL
324 ) IS
325
326 /*
327 -- load last update date for party profiles' setup
328
329 CURSOR c_profile_last_update_date (
330 p_entity_name VARCHAR2
331 ) IS
332 SELECT max(last_update_date) last_update_date
333 FROM hz_entity_attributes
334 WHERE entity_name = p_entity_name;
335 */
336
337 -- load attribute names in setup table for party profiles
338
339 CURSOR c_entity_dict (
340 p_entity_name VARCHAR2
341 ) IS
342 SELECT e.entity_attr_id, e.attribute_name
343 FROM hz_entity_attributes e
344 WHERE e.entity_name = p_entity_name
345 ORDER BY e.attribute_name;
346
347 -- load data source ranking in setup table for party profiles
348
349 CURSOR c_data_source (
350 p_entity_name VARCHAR2
351 ) IS
352 SELECT s.entity_attr_id,
353 s.content_source_type,
354 s.ranking
355 FROM hz_entity_attributes e,
356 hz_select_data_sources s
357 WHERE e.entity_name = p_entity_name
358 AND s.entity_attr_id = e.entity_attr_id
359 AND s.ranking <> 0;
360 -- Bug 4156090. Order by is no longer required.
361 -- ORDER BY s.entity_attr_id, s.ranking;
362
363 -- check if there is a sst profile for the given org party
364
365 CURSOR c_exist_org_sst_profile (
366 p_party_id NUMBER
367 ) IS
368 SELECT 'Y'
369 FROM hz_organization_profiles
370 WHERE party_id = p_party_id
371 AND actual_content_source = G_MISS_CONTENT_SOURCE_TYPE
372 AND effective_end_date is NULL;
373
374 -- check if there is a sst profile for the given person party
375
376 CURSOR c_exist_per_sst_profile (
377 p_party_id NUMBER
378 ) IS
379 SELECT 'Y'
380 FROM hz_person_profiles
381 WHERE party_id = p_party_id
382 AND actual_content_source = G_MISS_CONTENT_SOURCE_TYPE
383 AND effective_end_date is NULL;
384
385 -- find out the real data source of each column for the given party
386
387 CURSOR c_party_data_sources1 (
388 p_entity_name VARCHAR2,
389 p_party_id NUMBER
390 ) IS
391 SELECT e.entity_attr_id,
392 NVL(exp.content_source_type, s1.content_source_type),
393 exp.exception_type
394 FROM hz_entity_attributes e,
395 hz_select_data_sources s1,
396 (SELECT entity_attr_id,
397 content_source_type,
398 exception_type
399 FROM hz_win_source_exceps
400 WHERE party_id = p_party_id ) exp
401 WHERE e.entity_name = p_entity_name
402 AND e.entity_attr_id = s1.entity_attr_id
403 AND (s1.ranking = 1 or (s1.ranking = -1 and exp.content_source_type = s1.content_source_type))
404 AND exp.entity_attr_id (+) = e.entity_attr_id;
405
406 -- find out the real data source of each column for the given party
407 -- which does not have a sst profile.
408
409 CURSOR c_party_data_sources2 (
410 p_entity_name VARCHAR2,
411 p_party_id NUMBER
412 ) IS
413 SELECT e.entity_attr_id,
414 NVL(exp.content_source_type, 'USER_ENTERED'),
415 exp.exception_type
416 FROM hz_entity_attributes e,
417 (SELECT entity_attr_id,
418 content_source_type,
419 exception_type
420 FROM hz_win_source_exceps
421 WHERE party_id = p_party_id ) exp
422 WHERE e.entity_name = p_entity_name
423 AND exp.entity_attr_id (+) = e.entity_attr_id
424 and exists (select 'Y' from hz_select_data_sources s1
425 where s1.entity_attr_id = e.entity_attr_id
426 and s1.ranking <> 0);
427
428 -- find out the attributes which can be overwrited by user
429 -- when they store third party data.
430
431 -- SSM SST Integration and Extension
432 -- Now hz_user_overwrite_rules will store orig_system for overwrite_flag = 'Y'.
433 -- Select orig_system instead of overwrite_flag in cursor query.
434 CURSOR c_overwrite_third_party_rule (
435 p_rule_id NUMBER
436 ) IS
437 SELECT entity_attr_id,/* overwrite_flag*/
438 orig_system
439 FROM hz_user_overwrite_rules
440 WHERE rule_id = p_rule_id
441 AND overwrite_flag = 'Y'
442 ORDER BY entity_attr_id;
443
444 -- find out the attributes which can be overwrited by third
445 -- party when they store user entered data.
446
447 -- SSM SST Integration and Extension
448 -- hz_thirdparty_rule will now be orig_system specific.
449 -- Retrieve orig_system in the cursor.
450 CURSOR c_overwrite_user_rule (
451 p_party_id NUMBER
452 ) IS
453 SELECT rule.entity_attr_id,
454 rule.orig_system
455 FROM hz_thirdparty_rule rule
456 WHERE rule.overwrite_flag = 'Y'
457 AND NOT EXISTS
458 (SELECT '1'
459 FROM hz_thirdparty_exceps exceps
460 WHERE exceps.party_id = p_party_id
461 AND exceps.entity_attr_id = rule.entity_attr_id)
462 ORDER BY rule.entity_attr_id;
463
464 -- SSM SST Integration and Extension
465 -- All valid content source types will be stored in PL/SQL tables for faster access.
466 CURSOR c_orig_systems
467 IS
468 SELECT orig_system
469 FROM hz_orig_systems_b
470 WHERE sst_flag = 'Y'
471 -- AND status = 'A'
472 ORDER BY orig_system;
473
474 i_entity_attr_id INDEXIDList;
475 i_attribute_name INDEXVARCHAR30List;
476 i_content_source_type INDEXVARCHAR30List;
477 i_exception_type INDEXVARCHAR30List;
478 i_orig_system INDEXVARCHAR30List;
479 i_creation_flag INDEXVARCHAR30List;
480 i_ranking INDEXIDList;
481
482 l_entity_id NUMBER;
483 l_entity_name VARCHAR2(30);
484 -- l_last_update_date DATE;
485 -- l_reload BOOLEAN := FALSE;
486 l_rule_id NUMBER;
487 l_dummy VARCHAR2(1);
488 -- Bug 4171892
489 l_str VARCHAR2(1000);
490 l_len NUMBER;
491 l_start NUMBER;
492 i NUMBER;
493 j NUMBER;
494 l_debug_prefix VARCHAR2(30) := '';
495
496 BEGIN
497
498 -- Debug info.
499 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
500 hz_utility_v2pub.debug(p_message=>'cacheSetupForPartyProfiles (+)',
501 p_prefix=>l_debug_prefix,
502 p_msg_level=>fnd_log.level_procedure);
503 END IF;
504
505
506
507 -- SSM SST Integration and Extension
508 -- Load table G_ORIG_SYSTEM_LIST.
509 IF G_ORIG_SYSTEM_LIST_LOADED = 'N' THEN
510 OPEN c_orig_systems;
511 FETCH c_orig_systems BULK COLLECT INTO
512 G_ORIG_SYSTEM_LIST;
513 CLOSE c_orig_systems;
514 G_ORIG_SYSTEM_LIST_LOADED := 'Y';
515 END IF;
516
517 IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
518 -- Log count of content_source_types
519 hz_utility_v2pub.debug(
520 p_message => 'G_ORIG_SYSTEM_LIST.COUNT = '
521 || G_ORIG_SYSTEM_LIST.COUNT,
522 p_prefix => l_debug_prefix,
523 p_msg_level => fnd_log.level_statement);
524
525 -- Log orig_system_list.
526 FOR i in 1..G_ORIG_SYSTEM_LIST.COUNT LOOP
527 hz_utility_v2pub.debug(
528 p_message => 'G_ORIG_SYSTEM_LIST(' || i || ') = '
529 || G_ORIG_SYSTEM_LIST(i),
530 p_prefix => l_debug_prefix,
531 p_msg_level => fnd_log.level_statement);
532 END LOOP;
533
534 END IF;
535
536
537
538 -- loading attribute dictionary
539
540 -- Debug info.
541 IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
542 hz_utility_v2pub.debug(p_message=>'entity_name = '||p_entity_name||
543 ', G_ORG_SETUP_LOADED = '||G_ORG_SETUP_LOADED||
544 ', G_PER_SETUP_LOADED = '||G_PER_SETUP_LOADED,
545 p_prefix =>l_debug_prefix,
546 p_msg_level=>fnd_log.level_statement);
547 END IF;
548
549 /*
550 -- IF p_entity_name IN ('HZ_ORGANIZATION_PROFILES','HZ_PERSON_PROFILES') THEN
551
552 OPEN c_profile_last_update_date(p_entity_name);
553 FETCH c_profile_last_update_date INTO l_last_update_date;
554 CLOSE c_profile_last_update_date;
555
556 IF l_last_update_date IS NULL THEN
557 RETURN;
558 END IF;
559
560 -- IF l_last_update_date IS NOT NULL THEN
561
562 IF p_entity_name = 'HZ_ORGANIZATION_PROFILES' THEN
563 IF G_ORG_SETUP_LAST_UPDATE_DATE IS NULL OR
564 G_ORG_SETUP_LAST_UPDATE_DATE <> l_last_update_date
565 THEN
566 G_ORG_SETUP_LAST_UPDATE_DATE := l_last_update_date;
567 l_reload := TRUE;
568 END IF;
569 ELSE
570 IF G_PER_SETUP_LAST_UPDATE_DATE IS NULL OR
571 G_PER_SETUP_LAST_UPDATE_DATE <> l_last_update_date
572 THEN
573 G_PER_SETUP_LAST_UPDATE_DATE := l_last_update_date;
574 l_reload := TRUE;
575 END IF;
576 END IF;
577 -- END IF;
578
579 IF l_reload THEN
580 */
581
582 IF p_entity_name = 'HZ_ORGANIZATION_PROFILES' AND
583 G_ORG_SETUP_LOADED = 'N' OR
584 p_entity_name = 'HZ_PERSON_PROFILES' AND
585 G_PER_SETUP_LOADED = 'N'
586 THEN
587
588 -- load attribute names
589
590 OPEN c_entity_dict(p_entity_name);
591 IF p_entity_name = 'HZ_ORGANIZATION_PROFILES' THEN
592 FETCH c_entity_dict BULK COLLECT INTO
593 G_ORG_ATTRIBUTE_ID, G_ORG_ATTRIBUTE_NAME;
594 ELSE
595 FETCH c_entity_dict BULK COLLECT INTO
596 G_PER_ATTRIBUTE_ID, G_PER_ATTRIBUTE_NAME;
597 END IF;
598 CLOSE c_entity_dict;
599
600 -- load attributes data source and ranking
601
602 OPEN c_data_source(p_entity_name);
603 FETCH c_data_source BULK COLLECT INTO
604 i_entity_attr_id, i_content_source_type, i_ranking;
605 CLOSE c_data_source;
606
607 /*
608 l_str := ''; l_ranking := 1;
609 FOR i IN 1..i_entity_attr_id.COUNT+1 LOOP
610 IF i = i_entity_attr_id.COUNT+1 OR
611 (i > 1 AND
612 i_entity_attr_id(i-1) <> i_entity_attr_id(i))
613 THEN
614 G_ATTRIBUTE_DATA_SOURCE(i_entity_attr_id(i-1)) := l_str;
615
616 IF i = i_entity_attr_id.COUNT+1 THEN
617 EXIT;
618 END IF;
619 l_str := ''; l_ranking := 1;
620 END IF;
621 l_str := l_str||i_content_source_type(i)||','||i_ranking(i)||',';
622 l_ranking := l_ranking+1;
623 END LOOP;
624 */
625
626
627 -- Bug 4156090.
628 -- Changed the caching logic for G_ATTRIBUTE_DATA_SOURCE.
629
630 FOR i IN 1..i_entity_attr_id.COUNT LOOP
631 -- Bug 4244112 : Added debug log
632 /* IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
633 hz_utility_v2pub.debug(p_message=>'Loading attribute - '||i_entity_attr_id(i),
634 p_prefix =>l_debug_prefix,
635 p_msg_level=>fnd_log.level_statement);
636 END IF;
637 */
638
639 IF G_ATTRIBUTE_DATA_SOURCE.EXISTS(i_entity_attr_id(i)) THEN
640 G_ATTRIBUTE_DATA_SOURCE(i_entity_attr_id(i)) :=
641 G_ATTRIBUTE_DATA_SOURCE(i_entity_attr_id(i))
642 || getIndex(p_list => G_ORIG_SYSTEM_LIST,
643 p_name => i_content_source_type(i))
644 || ':'
645 ||i_ranking(i)
646 ||',';
647 ELSE
648 G_ATTRIBUTE_DATA_SOURCE(i_entity_attr_id(i)) :=
649 ','
650 || getIndex(p_list => G_ORIG_SYSTEM_LIST,
651 p_name => i_content_source_type(i))
652 || ':'
653 ||i_ranking(i)
654 ||',';
655 END IF;
656
657 END LOOP;
658 -- Bug 4228765 : Set the global varialbe for
659 -- person and organization profile entity
660 -- setup seperately
661 -- G_ORG_SETUP_LOADED := 'Y';
662
663 IF p_entity_name = 'HZ_ORGANIZATION_PROFILES' THEN
664 G_ORG_SETUP_LOADED := 'Y';
665 -- Bug 4244112 : Added debug log
666 IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
667 hz_utility_v2pub.debug(p_message=>'Loaded Organization Profiles',
668 p_prefix =>l_debug_prefix,
669 p_msg_level=>fnd_log.level_statement);
670 END IF;
671 END IF;
672
673 IF p_entity_name = 'HZ_PERSON_PROFILES' THEN
674 G_PER_SETUP_LOADED := 'Y';
675 -- Bug 4244112 : Added debug log
676 IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
677 hz_utility_v2pub.debug(p_message=>'Loaded Person Profiles',
678 p_prefix =>l_debug_prefix,
679 p_msg_level=>fnd_log.level_statement);
680 END IF;
681 END IF;
682
683 END IF; -- if G_ORG_SETUP_LOADED = 'N'
684
685 -- END IF; -- if p_entity_name in (..)
686
687
688 -- Loading overwrite third party data rule
689
690 l_rule_id := fnd_profile.value('HZ_USER_OVERWRITE_RULE');
691
692 -- Debug info.
693 IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
694 hz_utility_v2pub.debug(p_message=>'Loading overwrite third party data rule. '||
695 'HZ_USER_OVERWRITE_RULE = '||l_rule_id||
696 ', G_OVERWRITE_THIRD_PARTY_RULE = '||G_OVERWRITE_THIRD_PARTY_RULE,
697 p_prefix =>l_debug_prefix,
698 p_msg_level=>fnd_log.level_statement);
699 END IF;
700
701
702 -- SSM SST Integration and Extension
703 -- G_OVERWRITE_THIRD_PARTY will have entries of the form:
704 -- ,1,2,3,4, where 1, 2, 3, 4, are a mapping for 1st,2nd,3rd,4th orig_system in G_ORIG_SYSTEM_LIST
705 -- note that each id will be surrounded by commas(,) on either sides.
706
707 IF NVL(l_rule_id, -999) <> NVL(G_OVERWRITE_THIRD_PARTY_RULE, -999) THEN
708 G_OVERWRITE_THIRD_PARTY.DELETE;
709
710 IF l_rule_id IS NOT NULL THEN
711 OPEN c_overwrite_third_party_rule(l_rule_id);
712 FETCH c_overwrite_third_party_rule BULK COLLECT INTO
713 i_entity_attr_id, i_orig_system;
714 CLOSE c_overwrite_third_party_rule;
715
716
717 FOR i IN 1..i_entity_attr_id.COUNT LOOP
718 --G_OVERWRITE_THIRD_PARTY(i_entity_attr_id(i)) := i_overwrite_flag(i);
719 IF G_OVERWRITE_THIRD_PARTY.EXISTS(i_entity_attr_id(i)) THEN
720 G_OVERWRITE_THIRD_PARTY(i_entity_attr_id(i)) :=
721 G_OVERWRITE_THIRD_PARTY(i_entity_attr_id(i))
722 || getIndex( p_list => G_ORIG_SYSTEM_LIST,
723 p_name => i_orig_system(i))
724 || ',';
725 ELSE
726 G_OVERWRITE_THIRD_PARTY(i_entity_attr_id(i)) :=
727 ','
728 || getIndex( p_list => G_ORIG_SYSTEM_LIST,
729 p_name => i_orig_system(i))
730 || ',';
731 END IF;
732 /*
733 IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
734 hz_utility_v2pub.debug(
735 p_message => 'i = ' || i
736 || ', i_entity_attr_id(i) = '|| i_entity_attr_id(i)
737 || ', G_OVERWRITE_THIRD_PARTY('||i_entity_attr_id(i)||') = '||G_OVERWRITE_THIRD_PARTY(i_entity_attr_id(i)),
738 p_prefix => l_debug_prefix,
739 p_msg_level => fnd_log.level_statement);
740 END IF;
741 */
742 END LOOP;
743
744 END IF;
745
746 G_OVERWRITE_THIRD_PARTY_RULE := l_rule_id;
747
748 END IF;
749
750 -- Loading attributes' real data source for a given party
751
752 -- Debug info.
753 IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
754 hz_utility_v2pub.debug(p_message=>'Loading attributes real data source for a given party. '||
755 'party_id = '||p_party_id,
756 p_prefix =>l_debug_prefix,
757 p_msg_level=>fnd_log.level_statement);
758 END IF;
759
760 -- can not cache real data source and exception type because
761 -- they are transactional data and should be cleared when
762 -- rollback.
763
764 IF p_entity_name = 'HZ_ORGANIZATION_PROFILES' THEN
765 OPEN c_exist_org_sst_profile(p_party_id);
766 FETCH c_exist_org_sst_profile INTO l_dummy;
767 IF c_exist_org_sst_profile%NOTFOUND THEN
768 l_dummy := 'N';
769 END IF;
770 CLOSE c_exist_org_sst_profile;
771 ELSE
772 OPEN c_exist_per_sst_profile(p_party_id);
773 FETCH c_exist_per_sst_profile INTO l_dummy;
774 IF c_exist_per_sst_profile%NOTFOUND THEN
775 l_dummy := 'N';
776 END IF;
777 CLOSE c_exist_per_sst_profile;
778 END IF;
779
780 -- Debug info.
781 IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
782 hz_utility_v2pub.debug(p_message=>'SST profile exists. Exists ? '||l_dummy,
783 p_prefix =>l_debug_prefix,
784 p_msg_level=>fnd_log.level_statement);
785 END IF;
786
787 -- SST profile exists.
788
789 IF l_dummy = 'Y' THEN
790 OPEN c_party_data_sources1(p_entity_name, p_party_id);
791 FETCH c_party_data_sources1 BULK COLLECT INTO
792 i_entity_attr_id, i_content_source_type, i_exception_type;
793 CLOSE c_party_data_sources1;
794 ELSE
795 OPEN c_party_data_sources2(p_entity_name, p_party_id);
796 FETCH c_party_data_sources2 BULK COLLECT INTO
797 i_entity_attr_id, i_content_source_type, i_exception_type;
798 CLOSE c_party_data_sources2;
799 END IF;
800
801 G_REAL_DATA_SOURCE.DELETE;
802 G_EXCEPTION_TYPE.DELETE;
803
804 FOR i IN 1..i_entity_attr_id.COUNT LOOP
805 G_REAL_DATA_SOURCE(i_entity_attr_id(i)) := i_content_source_type(i);
806 G_EXCEPTION_TYPE(i_entity_attr_id(i)) := NVL(i_exception_type(i),'Migration');
807 END LOOP;
808
809 -- Loading overwrite user data rule
810
811 -- Debug info.
812 IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
813 hz_utility_v2pub.debug(p_message=>'Loading overwrite user data rule. '||
814 'party_id = '||p_party_id,
815 p_prefix =>l_debug_prefix,
816 p_msg_level=>fnd_log.level_statement);
817 END IF;
818
819 G_OVERWRITE_USER_RULE.DELETE;
820
821 -- SSM SST Integration and Extension
822 -- Now G_OVERWRITE_USER_RULE will contain the list of orig_systems instead of 'Y'.
823 OPEN c_overwrite_user_rule(p_party_id);
824 FETCH c_overwrite_user_rule BULK COLLECT INTO
825 i_entity_attr_id,i_orig_system;
826 CLOSE c_overwrite_user_rule;
827
828 FOR i IN 1..i_entity_attr_id.COUNT LOOP
829 IF G_OVERWRITE_USER_RULE.EXISTS(i_entity_attr_id(i)) THEN
830 G_OVERWRITE_USER_RULE(i_entity_attr_id(i)) :=
831 G_OVERWRITE_USER_RULE(i_entity_attr_id(i))
832 || getIndex( p_list => G_ORIG_SYSTEM_LIST,
833 p_name => i_orig_system(i))
834 || ',';
835 ELSE
836 G_OVERWRITE_USER_RULE(i_entity_attr_id(i)) :=
837 ','
838 || getIndex( p_list => G_ORIG_SYSTEM_LIST,
839 p_name => i_orig_system(i))
840 || ',';
841 END IF;
842 /*
843 IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
844 hz_utility_v2pub.debug(
845 p_message => 'i = ' || i
846 || ', i_entity_attr_id(i) = '|| i_entity_attr_id(i)
847 || ', G_OVERWRITE_USER_RULE('||i_entity_attr_id(i)||') = '||G_OVERWRITE_USER_RULE(i_entity_attr_id(i)),
848 p_prefix => l_debug_prefix,
849 p_msg_level => fnd_log.level_statement);
850 END IF;
851 */
852 END LOOP;
853
854 -- Debug info.
855 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
856 hz_utility_v2pub.debug(p_message=>'cacheSetupForPartyProfiles (-)',
857 p_prefix=>l_debug_prefix,
858 p_msg_level=>fnd_log.level_procedure);
859 END IF;
860
861 END cacheSetupForPartyProfiles;
862
863 /**
864 * PRIVATE PROCEDURE cacheSetupForOtherEntities
865 *
866 * DESCRIPTION
867 * cache mix-n-match setup for performance reason.
868 *
869 * EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
870 *
871 * IN:
872 * p_load_rule If we need to load the creation rule.
873 *
874 * MODIFICATION HISTORY
875 *
876 * 04-30-2002 Jianying Huang o Created.
877 * 01-03-2005 Rajib Ranjan Borah o SSM SST Integration and Extension.
878 * Populated G_OTHER_ENT_USER_OVERWRITE and
879 * G_ORIG_SYSTEM_LIST.
880 * o Added cursors c_overwrite_rule_other
881 * and c_orig_systems.
882 * o G_ENTITY_DATA_SOURCE will not be populated
883 * for non-profile entities.
884 * 07-07-2005 Dhaval Mehta o Bug 4376604. Changed caching logic for
885 * G_ENTITY_DATA_SOURCE to improve scalability.
886 */
887
888 PROCEDURE cacheSetupForOtherEntities (
889 p_load_rule IN BOOLEAN := FALSE
890 ) IS
891
892 /*
893 -- load last update date for data source' setup
894
895 CURSOR c_datasource_last_update_date IS
896 SELECT max(last_update_date) last_update_date
897 FROM hz_entity_attributes;
898 */
899
900 -- load entity names in setup table for other entities
901
902 CURSOR c_entity_dict_other IS
903 SELECT e.entity_attr_id, e.entity_name
904 FROM hz_entity_attributes e
905 WHERE e.entity_name NOT IN ('HZ_ORGANIZATION_PROFILES','HZ_PERSON_PROFILES')
906 ORDER BY e.entity_name;
907
908 /* SSM SST Integration and Extension
909 The concept of selected data sources for other entities is obsoleted.
910
911 -- load data source in setup table for other entities.
912
913 CURSOR c_data_source_other IS
914 SELECT s.entity_attr_id,
915 s.content_source_type
916 FROM hz_entity_attributes e,
917 hz_select_data_sources s
918 WHERE e.entity_name NOT IN ('HZ_ORGANIZATION_PROFILES','HZ_PERSON_PROFILES')
919 AND s.entity_attr_id = e.entity_attr_id
920 AND s.ranking > 0
921 ORDER BY s.entity_attr_id;
922 */
923 -- load data source in setup table for party profiles.
924
925 CURSOR c_data_source_profile (
926 p_entity_name VARCHAR2
927 ) IS
928 SELECT UNIQUE s.content_source_type
929 FROM hz_entity_attributes e,
930 hz_select_data_sources s
931 WHERE e.entity_name = p_entity_name
932 AND s.entity_attr_id = e.entity_attr_id
933 AND s.ranking <> 0; -- SSM SST Integration and Extension -->> ranking of -1 denotes MRR
934
935 -- find out if user can create user-entered data
936
937 CURSOR c_user_create_rule (
938 p_rule_id NUMBER
939 ) IS
940 SELECT entity_attr_id, creation_flag
941 FROM hz_user_create_rules
942 WHERE rule_id = p_rule_id;
943
944
945 -- SSM SST Integration and Extension
946 -- Other entities can now be overwriteable if the rules are setup for this.
947
948 CURSOR c_user_overwrite_rule_other (
949 p_rule_id NUMBER
950 ) IS
951 SELECT entity_attr_id,
952 orig_system
953 FROM hz_user_overwrite_rules
954 WHERE overwrite_flag = 'Y'
955 AND rule_id = p_rule_id;
956
957 -- SSM SST Integration and Extension
958 -- All valid content source types will be stored in PL/SQL tables for faster access.
959 CURSOR c_orig_systems
960 IS
961 SELECT orig_system
962 FROM hz_orig_systems_b
963 WHERE sst_flag = 'Y'
964 -- AND status = 'A'
965 ORDER BY orig_system;
966
967 i_entity_attr_id INDEXIDList;
968 i_content_source_type INDEXVARCHAR30List;
969 i_creation_flag INDEXVARCHAR30List;
970
971 l_entity_id NUMBER;
972 l_entity_name VARCHAR2(30);
973 -- l_last_update_date DATE;
974 l_rule_id NUMBER;
975 -- Bug 4171892
976 l_str VARCHAR2(1000);
977 l_len NUMBER;
978 l_start NUMBER;
979 i NUMBER;
980 j NUMBER;
981 l_debug_prefix VARCHAR2(30) := '';
982 i_orig_system INDEXVARCHAR30List;
983 BEGIN
984
985 -- Debug info.
986 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
987 hz_utility_v2pub.debug(p_message=>'cacheSetupForOtherEntities (+)',
988 p_prefix=>l_debug_prefix,
989 p_msg_level=>fnd_log.level_procedure);
990 END IF;
991
992 -- loading dictionary for other entities
993
994 -- Debug info.
995 IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
996 hz_utility_v2pub.debug(p_message=>'G_DATASOURCE_LOADED = '||G_DATASOURCE_LOADED,
997 p_prefix =>l_debug_prefix,
998 p_msg_level=>fnd_log.level_statement);
999 END IF;
1000
1001 /*
1002 OPEN c_datasource_last_update_date;
1003 FETCH c_datasource_last_update_date INTO l_last_update_date;
1004 CLOSE c_datasource_last_update_date;
1005
1006 IF l_last_update_date IS NULL THEN
1007 RETURN;
1008 END IF;
1009
1010 IF G_DATASOURCE_LAST_UPDATE_DATE IS NULL OR
1011 G_DATASOURCE_LAST_UPDATE_DATE <> l_last_update_date
1012 THEN
1013 */
1014
1015 IF G_DATASOURCE_LOADED = 'N' THEN
1016
1017 -- load entity names
1018
1019 OPEN c_entity_dict_other;
1020 FETCH c_entity_dict_other BULK COLLECT INTO
1021 G_ENTITY_ID, G_ENTITY_NAME;
1022 CLOSE c_entity_dict_other;
1023
1024
1025 /*******************************
1026 ^ SSM SST Integration and Extension
1027 | The concept of selected data sources for other entities is obsoleted.
1028 | Comment out the code below while handling other entities.
1029
1030
1031 -- G_MIXNM_ENABLED_FLAG.DELETE;
1032 -- G_ENTITY_DATA_SOURCE.DELETE;
1033
1034 -- load entities' data source
1035
1036 OPEN c_data_source_other;
1037 FETCH c_data_source_other BULK COLLECT INTO
1038 i_entity_attr_id, i_content_source_type;
1039 CLOSE c_data_source_other;
1040
1041 IF i_entity_attr_id.COUNT > 0 THEN
1042 l_str := '';
1043 FOR i IN 1..i_entity_attr_id.COUNT+1 LOOP
1044 IF i = i_entity_attr_id.COUNT+1 OR
1045 (i > 1 AND
1046 i_entity_attr_id(i-1) <> i_entity_attr_id(i))
1047 THEN
1048 IF l_str IS NOT NULL THEN
1049 l_len := LENGTHB(l_str);
1050 IF l_len > 1 THEN
1051 l_str := SUBSTRB(l_str,1,l_len-1);
1052 END IF;
1053 END IF;
1054
1055 -- Debug info.
1056 IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1057 hz_utility_v2pub.debug(p_message=>'l_str = '||l_str,
1058 p_prefix =>l_debug_prefix,
1059 p_msg_level=>fnd_log.level_statement);
1060 END IF;
1061
1062 G_ENTITY_DATA_SOURCE(i_entity_attr_id(i-1)) := l_str;
1063
1064 IF i = i_entity_attr_id.COUNT+1 THEN
1065 EXIT;
1066 END IF;
1067 l_str := '';
1068 END IF;
1069 l_str := l_str||''''||i_content_source_type(i)||''',';
1070 END LOOP;
1071 END IF;
1072
1073 l_start := G_ENTITY_ID.COUNT;
1074
1075 -- Debug info.
1076 IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1077 hz_utility_v2pub.debug(p_message=>'l_start = '||l_start,
1078 | p_prefix =>l_debug_prefix,
1079 | p_msg_level=>fnd_log.level_statement);
1080 V END IF;
1081 *************************/
1082
1083 -- Bug 4376604.
1084 -- Moved caching of orig system list out of the rules block as this caching will be used for
1085 -- G_ENTITY_DATA_SOURCE also.
1086
1087 -- SSM SST Integration and Extension
1088 -- Load table G_ORIG_SYSTEM_LIST.
1089
1090 IF G_ORIG_SYSTEM_LIST_LOADED = 'N' THEN
1091 OPEN c_orig_systems;
1092 FETCH c_orig_systems BULK COLLECT INTO
1093 G_ORIG_SYSTEM_LIST;
1094 CLOSE c_orig_systems;
1095 G_ORIG_SYSTEM_LIST_LOADED := 'Y';
1096 END IF;
1097
1098 IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1099 -- Log count of content_source_types
1100 hz_utility_v2pub.debug(
1101 p_message => 'G_ORIG_SYSTEM_LIST.COUNT = '
1102 || G_ORIG_SYSTEM_LIST.COUNT,
1103 p_prefix => l_debug_prefix,
1104 p_msg_level => fnd_log.level_statement);
1105
1106 -- Log orig_system_list.
1107 FOR i in 1..G_ORIG_SYSTEM_LIST.COUNT LOOP
1108 hz_utility_v2pub.debug(
1109 p_message => 'G_ORIG_SYSTEM_LIST(' || i || ') = '
1110 || G_ORIG_SYSTEM_LIST(i),
1111 p_prefix => l_debug_prefix,
1112 p_msg_level => fnd_log.level_statement);
1113 END LOOP;
1114
1115 END IF;
1116
1117 FOR i IN 1..2 LOOP
1118 i_content_source_type.DELETE;
1119
1120 -- Debug info.
1121 IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1122 hz_utility_v2pub.debug(p_message=>'i = '||i,
1123 p_prefix =>l_debug_prefix,
1124 p_msg_level=>fnd_log.level_statement);
1125 END IF;
1126
1127 IF i = 1 THEN
1128 l_entity_name := 'HZ_PERSON_PROFILES';
1129 l_entity_id := G_PERSON_PROFILE_ID;
1130 ELSE
1131 l_entity_name := 'HZ_ORGANIZATION_PROFILES';
1132 l_entity_id := G_ORGANIZATION_PROFILE_ID;
1133 END IF;
1134
1135 -- Debug info.
1136 IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1137 hz_utility_v2pub.debug(p_message=>'l_entity_name = '''||l_entity_name||'''',
1138 p_prefix =>l_debug_prefix,
1139 p_msg_level=>fnd_log.level_statement);
1140 hz_utility_v2pub.debug(p_message=>'l_entity_id = '||l_entity_id,
1141 p_prefix =>l_debug_prefix,
1142 p_msg_level=>fnd_log.level_statement);
1143
1144 END IF;
1145
1146 OPEN c_data_source_profile(l_entity_name);
1147 FETCH c_data_source_profile BULK COLLECT INTO
1148 i_content_source_type;
1149 CLOSE c_data_source_profile;
1150
1151 -- Debug info.
1152 IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1153 hz_utility_v2pub.debug(p_message=>'i_content_source_type.COUNT = '||i_content_source_type.COUNT,
1154 p_prefix =>l_debug_prefix,
1155 p_msg_level=>fnd_log.level_statement);
1156 END IF;
1157
1158 /*
1159 l_str := '';
1160 FOR j IN 1..i_content_source_type.COUNT LOOP
1161 l_str := l_str||''''||i_content_source_type(j)||''',';
1162 END LOOP;
1163 IF l_str IS NOT NULL THEN
1164 l_len := LENGTHB(l_str);
1165 IF l_len > 1 THEN
1166 l_str := SUBSTRB(l_str,1,l_len-1);
1167 END IF;
1168 END IF;
1169 */
1170
1171 -- New caching logic for G_ENTITY_DATA_SOURCE
1172 FOR j IN 1..i_content_source_type.COUNT LOOP
1173 IF G_ENTITY_DATA_SOURCE.EXISTS(l_entity_id) THEN
1174 G_ENTITY_DATA_SOURCE(l_entity_id) := G_ENTITY_DATA_SOURCE(l_entity_id)
1175 || getIndex( p_list => G_ORIG_SYSTEM_LIST,
1176 p_name => i_content_source_type(j))
1177 || ',';
1178 ELSE
1179 G_ENTITY_DATA_SOURCE(l_entity_id) := ','
1180 || getIndex( p_list => G_ORIG_SYSTEM_LIST,
1181 p_name => i_content_source_type(j))
1182 || ',';
1183 END IF;
1184 END LOOP;
1185 -- Debug info.
1186 IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1187 hz_utility_v2pub.debug(p_message=>'l_str = '||l_str,
1188 p_prefix =>l_debug_prefix,
1189 p_msg_level=>fnd_log.level_statement);
1190 END IF;
1191
1192 -- G_ENTITY_DATA_SOURCE(l_entity_id) := l_str;
1193 END LOOP;
1194
1195 -- Debug info.
1196 IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1197 hz_utility_v2pub.debug(p_message=>'total = '||G_ENTITY_DATA_SOURCE.COUNT,
1198 p_prefix =>l_debug_prefix,
1199 p_msg_level=>fnd_log.level_statement);
1200 WHILE j <= G_ENTITY_DATA_SOURCE.COUNT LOOP
1201 IF G_ENTITY_DATA_SOURCE.EXISTS(i) THEN
1202 hz_utility_v2pub.debug(p_message=>'G_ENTITY_DATA_SOURCE('||i||')='||
1203 G_ENTITY_DATA_SOURCE(i),
1204 p_prefix =>l_debug_prefix,
1205 p_msg_level=>fnd_log.level_statement);
1206 ELSE
1207 hz_utility_v2pub.debug(p_message=>'G_ENTITY_DATA_SOURCE('||i||')=null',
1208 p_prefix =>l_debug_prefix,
1209 p_msg_level=>fnd_log.level_statement);
1210 END IF;
1211 i := G_ENTITY_DATA_SOURCE.NEXT(i);
1212 hz_utility_v2pub.debug(p_message=>'i = '||i,
1213 p_prefix =>l_debug_prefix,
1214 p_msg_level=>fnd_log.level_statement);
1215 j := j + 1;
1216 END LOOP;
1217 END IF;
1218
1219 -- G_DATASOURCE_LAST_UPDATE_DATE := l_last_update_date;
1220 G_DATASOURCE_LOADED := 'Y';
1221
1222 END IF;
1223
1224 IF p_load_rule THEN
1225
1226 -- Loading creation user-entered data rule
1227
1228 l_rule_id := fnd_profile.value('HZ_USER_DATA_CREATION_RULE');
1229
1230 -- Debug info.
1231 IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1232 hz_utility_v2pub.debug(p_message=>'Loading creation user-entered data rule.' ||
1233 'HZ_USER_DATA_CREATION_RULE = '||l_rule_id||
1234 ', G_CREATE_USER_ENTERED_RULE = '||G_CREATE_USER_ENTERED_RULE,
1235 p_prefix =>l_debug_prefix,
1236 p_msg_level=>fnd_log.level_statement);
1237 END IF;
1238
1239 IF NVL(l_rule_id, -999) <> NVL(G_CREATE_USER_ENTERED_RULE, -999) THEN
1240
1241 G_CREATE_USER_ENTERED.DELETE;
1242 G_OTHER_ENT_USER_OVERWRITE.DELETE;
1243
1244 IF l_rule_id IS NOT NULL THEN
1245 OPEN c_user_create_rule(l_rule_id);
1246 FETCH c_user_create_rule BULK COLLECT INTO
1247 i_entity_attr_id, i_creation_flag;
1248 CLOSE c_user_create_rule;
1249
1250 FOR i IN 1..i_entity_attr_id.COUNT LOOP
1251 G_CREATE_USER_ENTERED(i_entity_attr_id(i)) := i_creation_flag(i);
1252 END LOOP;
1253
1254 OPEN c_user_overwrite_rule_other(l_rule_id);
1255 FETCH c_user_overwrite_rule_other BULK COLLECT INTO
1256 i_entity_attr_id, i_orig_system;
1257 CLOSE c_user_overwrite_rule_other;
1258
1259 FOR i IN 1..i_entity_attr_id.COUNT LOOP
1260 IF G_OTHER_ENT_USER_OVERWRITE.EXISTS(i_entity_attr_id(i)) THEN
1261 G_OTHER_ENT_USER_OVERWRITE(i_entity_attr_id(i)) :=
1262 G_OTHER_ENT_USER_OVERWRITE(i_entity_attr_id(i))
1263 || getIndex( p_list => G_ORIG_SYSTEM_LIST,
1264 p_name => i_orig_system(i))
1265 || ',';
1266 ELSE
1267 G_OTHER_ENT_USER_OVERWRITE(i_entity_attr_id(i)) :=
1268 ','
1269 || getIndex( p_list => G_ORIG_SYSTEM_LIST,
1270 p_name => i_orig_system(i))
1271 || ',';
1272 END IF;
1273 END LOOP;
1274
1275 END IF;
1276
1277 G_CREATE_USER_ENTERED_RULE := l_rule_id;
1278
1279 END IF;
1280
1281 END IF;
1282
1283 -- Debug info.
1284 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
1285 hz_utility_v2pub.debug(p_message=>'cacheSetupForOtherEntities (-)',
1286 p_prefix=>l_debug_prefix,
1287 p_msg_level=>fnd_log.level_procedure);
1288 END IF;
1289
1290 END cacheSetupForOtherEntities;
1291
1292 /**
1293 * PRIVATE PROCEDURE isThirdPartyDataOverwriteable
1294 *
1295 * DESCRIPTION
1296 * Return 'Y' if third party data is overwritable.
1297 *
1298 * EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
1299 *
1300 * IN:
1301 * p_entity_attr_id Attribute Id.
1302 *
1303 * MODIFICATION HISTORY
1304 *
1305 * 04-30-2002 Jianying Huang o Created.
1306 * 12-30-2004 Rajib Ranjan Borah o SSM SST Integration and Extension.
1307 * Added parameter p_orig_system and related logic.
1308 *
1309 */
1310
1311 FUNCTION isThirdPartyDataOverwriteable (
1312 p_entity_attr_id IN NUMBER,
1313 p_orig_system IN VARCHAR2
1314 ) RETURN VARCHAR2 IS
1315 l_debug_prefix VARCHAR2(30) := '';
1316 BEGIN
1317
1318 -- Debug info.
1319 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
1320 hz_utility_v2pub.debug(p_message=>'isThirdPartyDataOverwriteable (+)',
1321 p_prefix=>l_debug_prefix,
1322 p_msg_level=>fnd_log.level_procedure);
1323 END IF;
1324 IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1325 hz_utility_v2pub.debug(
1326 p_message => ' p_orig_system = '||p_orig_system
1327 || ', p_entity_attr_id = '||p_entity_attr_id,
1328 p_prefix => l_debug_prefix,
1329 p_msg_level => fnd_log.level_statement);
1330 END IF;
1331
1332 -- Bug 4201309 : By default, user can overwrite any attribute
1333 -- if there is no rule restricting the update
1334 IF G_OVERWRITE_THIRD_PARTY_RULE IS NULL THEN
1335 RETURN 'Y';
1336 END IF;
1337
1338 IF G_OVERWRITE_THIRD_PARTY.EXISTS(p_entity_attr_id) THEN
1339 --RETURN G_OVERWRITE_THIRD_PARTY(p_entity_attr_id);
1340 IF instrb ( G_OVERWRITE_THIRD_PARTY(p_entity_attr_id),
1341 ',' || getIndex ( p_list => G_ORIG_SYSTEM_LIST,
1342 p_name => p_orig_system ) || ',',
1343 1,
1344 1
1345 ) <> 0
1346 THEN
1347
1348 IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1349 hz_utility_v2pub.debug(
1350 p_message => 'G_OVERWRITE_THIRD_PARTY('||p_entity_attr_id||') = '
1351 || G_OVERWRITE_THIRD_PARTY(p_entity_attr_id)
1352 || '. Case 1-Y',
1353 p_prefix => l_debug_prefix,
1354 p_msg_level => fnd_log.level_statement);
1355 END IF;
1356 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
1357 hz_utility_v2pub.debug(
1358 p_message => 'isThirdPartyDataOverwriteable (-)',
1359 p_prefix => l_debug_prefix,
1360 p_msg_level => fnd_log.level_procedure);
1361 END IF;
1362
1363 RETURN 'Y';
1364 ELSE
1365
1366 IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1367 hz_utility_v2pub.debug(
1368 p_message => 'G_OVERWRITE_THIRD_PARTY('||p_entity_attr_id||') = '
1369 || G_OVERWRITE_THIRD_PARTY(p_entity_attr_id)
1370 || '. Case 2-N',
1371 p_prefix => l_debug_prefix,
1372 p_msg_level => fnd_log.level_statement);
1373 END IF;
1374 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
1375 hz_utility_v2pub.debug(
1376 p_message => 'isThirdPartyDataOverwriteable (-)',
1377 p_prefix => l_debug_prefix,
1378 p_msg_level => fnd_log.level_procedure);
1379 END IF;
1380
1381 RETURN 'N';
1382 END IF;
1383 ELSE
1384
1385 IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1386 hz_utility_v2pub.debug(
1387 p_message => 'G_OVERWRITE_THIRD_PARTY('||p_entity_attr_id||') does not exist '
1388 || '. Case 3-N',
1389 p_prefix => l_debug_prefix,
1390 p_msg_level => fnd_log.level_statement);
1391 END IF;
1392 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
1393 hz_utility_v2pub.debug(
1394 p_message => 'isThirdPartyDataOverwriteable (-)',
1395 p_prefix => l_debug_prefix,
1396 p_msg_level => fnd_log.level_procedure);
1397 END IF;
1398
1399 RETURN 'N';
1400 END IF;
1401
1402 END isThirdPartyDataOverwriteable;
1403
1404 /**
1405 * PRIVATE PROCEDURE isUserDataOverwriteable
1406 *
1407 * DESCRIPTION
1408 * Return 'Y' if user data is overwritable.
1409 *
1410 * EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
1411 *
1412 * IN:
1413 * p_entity_attr_id Attribute Id.
1414 *
1415 * MODIFICATION HISTORY
1416 *
1417 * 04-30-2002 Jianying Huang o Created.
1418 * 12-30-2004 Rajib Ranjan Borah o SSM SST Integration and Extension.
1419 * Added parameter p_orig_system and related logic.
1420 */
1421
1422 FUNCTION isUserDataOverwriteable (
1423 p_entity_attr_id IN NUMBER,
1424 p_orig_system IN VARCHAR2
1425 ) RETURN VARCHAR2 IS
1426 l_debug_prefix VARCHAR2(30) := '';
1427 BEGIN
1428
1429 -- Debug info.
1430 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
1431 hz_utility_v2pub.debug(p_message=>'isUserDataOverwriteable (+)',
1432 p_prefix=>l_debug_prefix,
1433 p_msg_level=>fnd_log.level_procedure);
1434 END IF;
1435 IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1436 hz_utility_v2pub.debug(
1437 p_message => 'p_orig_system = '||p_orig_system
1438 || ', p_entity_attr_id = '||p_entity_attr_id,
1439 p_prefix => l_debug_prefix,
1440 p_msg_level => fnd_log.level_statement);
1441 END IF;
1442
1443 IF G_OVERWRITE_USER_RULE.EXISTS(p_entity_attr_id)
1444 THEN
1445 IF instrb ( G_OVERWRITE_USER_RULE(p_entity_attr_id),
1446 ',' || getIndex ( p_list => G_ORIG_SYSTEM_LIST,
1447 p_name => p_orig_system ) || ',',
1448 1,
1449 1
1450 ) <> 0
1451 THEN
1452 IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1453 hz_utility_v2pub.debug(
1454 p_message => 'G_OVERWRITE_USER_RULE('||p_entity_attr_id||') = '
1455 || G_OVERWRITE_USER_RULE(p_entity_attr_id)
1456 || '. Case 1-Y',
1457 p_prefix => l_debug_prefix,
1458 p_msg_level => fnd_log.level_statement);
1459 END IF;
1460 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
1461 hz_utility_v2pub.debug(
1462 p_message => 'isThirdPartyDataOverwriteable (-)',
1463 p_prefix => l_debug_prefix,
1464 p_msg_level => fnd_log.level_procedure);
1465 END IF;
1466
1467 RETURN 'Y';
1468 ELSE
1469 IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1470 hz_utility_v2pub.debug(
1471 p_message => 'G_OVERWRITE_THIRD_PARTY('||p_entity_attr_id||') = '
1472 || G_OVERWRITE_THIRD_PARTY(p_entity_attr_id)
1473 || '. Case 2-N',
1474 p_prefix => l_debug_prefix,
1475 p_msg_level => fnd_log.level_statement);
1476 END IF;
1477 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
1478 hz_utility_v2pub.debug(
1479 p_message => 'isThirdPartyDataOverwriteable (-)',
1480 p_prefix => l_debug_prefix,
1481 p_msg_level => fnd_log.level_procedure);
1482 END IF;
1483
1484 RETURN 'N';
1485 END IF;
1486 ELSE
1487 IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1488 hz_utility_v2pub.debug(
1489 p_message => 'G_OVERWRITE_THIRD_PARTY('||p_entity_attr_id||') does not exist '
1490 || '. Case 3-N',
1491 p_prefix => l_debug_prefix,
1492 p_msg_level => fnd_log.level_statement);
1493 END IF;
1494 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
1495 hz_utility_v2pub.debug(
1496 p_message => 'isThirdPartyDataOverwriteable (-)',
1497 p_prefix => l_debug_prefix,
1498 p_msg_level => fnd_log.level_procedure);
1499 END IF;
1500
1501 RETURN 'N';
1502 END IF;
1503
1504 END isUserDataOverwriteable;
1505
1506 /**
1507 * PRIVATE PROCEDURE getDataSourceRanking
1508 *
1509 * DESCRIPTION
1510 * Return data source ranking for a given attribute id and
1511 * data source type.
1512 *
1513 * EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
1514 *
1515 * IN:
1516 * p_entity_attr_id Attribute Id.
1517 * p_data_source_type Data source type.
1518 *
1519 * MODIFICATION HISTORY
1520 *
1521 * 04-30-2002 Jianying Huang o Created.
1522 * 02-28-2005 Rajib Ranjan Borah o Bug 4156090. Caching logic is changed for
1523 * G_ATTRIBUTE_DATA_SOURCE
1524 */
1525
1526 FUNCTION getDataSourceRanking (
1527 p_entity_attr_id IN NUMBER,
1528 p_data_source_type IN VARCHAR2
1529 ) RETURN NUMBER IS
1530
1531 l_pos NUMBER;
1532 l_pos1 NUMBER;
1533 l_pos2 NUMBER;
1534 l_str VARCHAR2(400);
1535 l_debug_prefix VARCHAR2(30) := '';
1536
1537 BEGIN
1538
1539 -- Debug info.
1540 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
1541 hz_utility_v2pub.debug(p_message=>'getDataSourceRanking (+)',
1542 p_prefix=>l_debug_prefix,
1543 p_msg_level=>fnd_log.level_procedure);
1544 END IF;
1545
1546
1547 -- the element in the pl/sql table has the format
1548 -- <data source1>,1,<data source2>,2,...
1549 -- for instance, USER_ENTERED,1,DNB,2,...
1550 -- So we only need to find the number right after
1551 -- the data source.
1552 /*
1553 IF G_ATTRIBUTE_DATA_SOURCE.EXISTS(p_entity_attr_id) THEN
1554 l_str := G_ATTRIBUTE_DATA_SOURCE(p_entity_attr_id);
1555 l_pos := INSTRB(l_str, p_data_source_type||',');
1556 IF l_pos = 0 THEN
1557 RETURN 0;
1558 ELSE
1559 l_pos1 := l_pos+LENGTHB(p_data_source_type)+1;
1560 l_pos2 := INSTRB(l_str, ',', l_pos, 2);
1561 RETURN TO_NUMBER(SUBSTRB(l_str, l_pos1, l_pos2-l_pos1));
1562 END IF;
1563 ELSE
1564 RETURN 0;
1565 END IF;
1566 */
1567
1568 -- Bug 4156090.
1569 -- Changed the caching logic in G_ATTRIBUTE_DATA_SOURCE.
1570
1571 IF G_ATTRIBUTE_DATA_SOURCE.EXISTS(p_entity_attr_id) THEN
1572 l_str := G_ATTRIBUTE_DATA_SOURCE(p_entity_attr_id);
1573 l_pos := INSTRB(l_str, ','||getIndex(p_list => G_ORIG_SYSTEM_LIST, p_name => p_data_source_type)||':');
1574 IF l_pos = 0 THEN
1575 RETURN 0;
1576 ELSE
1577 l_pos1 := INSTRB(l_str, ':', l_pos) + 1;
1578
1579 l_pos2 := INSTRB(l_str, ',', l_pos1);
1580 RETURN TO_NUMBER(SUBSTRB(l_str, l_pos1, l_pos2-l_pos1));
1581 END IF;
1582 ELSE
1583 RETURN 0;
1584 END IF;
1585
1586 END getDataSourceRanking;
1587
1588 /**
1589 * PRIVATE PROCEDURE getIndex
1590 *
1591 * DESCRIPTION
1592 * Return the index of a name in a name list.
1593 *
1594 * EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
1595 *
1596 * IN:
1597 * p_list Name list.
1598 * p_name Name of the entity / attribute.
1599 *
1600 * MODIFICATION HISTORY
1601 *
1602 * 04-30-2002 Jianying Huang o Created.
1603 */
1604
1605 FUNCTION getIndex (
1606 p_list IN INDEXVARCHAR30List,
1607 p_name IN VARCHAR2
1608 ) RETURN NUMBER IS
1609
1610 l_start NUMBER;
1611 l_end NUMBER;
1612 l_middle NUMBER;
1613 l_debug_prefix VARCHAR2(30) := '';
1614
1615 BEGIN
1616 /* Bug 4244112 : comment debug log
1617 -- Debug info.
1618 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
1619 hz_utility_v2pub.debug(p_message=>'getIndex (+)',
1620 p_prefix=>l_debug_prefix,
1621 p_msg_level=>fnd_log.level_procedure);
1622 END IF;
1623 */
1624
1625 -- binary search
1626
1627 l_start := 1; l_end := p_list.COUNT;
1628 /* Bug 4244112 : comment debug log
1629 -- Debug info.
1630 IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1631 hz_utility_v2pub.debug(p_message=>'l_end = '||l_end,
1632 p_prefix =>l_debug_prefix,
1633 p_msg_level=>fnd_log.level_statement);
1634 END IF;
1635 */
1636 WHILE l_start <= l_end LOOP
1637 l_middle := ROUND((l_end+l_start)/2);
1638 /*
1639 -- Debug info.
1640 IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1641 hz_utility_v2pub.debug(p_message=>'p_list('||l_middle||') = '||p_list(l_middle),
1642 p_prefix =>l_debug_prefix,
1643 p_msg_level=>fnd_log.level_statement);
1644 END IF;
1645 */
1646 IF p_name = p_list(l_middle) THEN
1647 /* Bug 4244112 : comment debug log
1648 -- Debug info.
1649 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
1650 hz_utility_v2pub.debug(p_message=>'getIndex (-)',
1651 p_prefix=>l_debug_prefix,
1652 p_msg_level=>fnd_log.level_procedure);
1653 END IF;
1654 */
1655 RETURN l_middle;
1656 ELSIF p_name > p_list(l_middle) THEN
1657 l_start := l_middle+1;
1658 ELSE
1659 l_end := l_middle-1;
1660 END IF;
1661 END LOOP;
1662
1663 RETURN 0;
1664
1665 END getIndex;
1666
1667 /**
1668 * PRIVATE PROCEDURE getEntityAttrId
1669 *
1670 * DESCRIPTION
1671 * Return the id of a given entity / attribute.
1672 *
1673 * EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
1674 *
1675 * IN:
1676 * p_entity_name Entity name.
1677 * p_attribute_name Attribute name.
1678 *
1679 * MODIFICATION HISTORY
1680 *
1681 * 04-30-2002 Jianying Huang o Created.
1682 */
1683
1684 FUNCTION getEntityAttrId (
1685 p_entity_name IN VARCHAR2,
1686 p_attribute_name IN VARCHAR2 := NULL
1687 ) RETURN NUMBER IS
1688
1689 l_index NUMBER;
1690 l_debug_prefix VARCHAR2(30) := '';
1691
1692 BEGIN
1693
1694 -- Debug info.
1695 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
1696 hz_utility_v2pub.debug(p_message=>'getEntityAttrId (+)',
1697 p_prefix=>l_debug_prefix,
1698 p_msg_level=>fnd_log.level_procedure);
1699 END IF;
1700
1701 IF p_attribute_name IS NOT NULL THEN
1702 -- IF G_ORG_SETUP_LAST_UPDATE_DATE IS NOT NULL AND
1703 IF G_ORG_SETUP_LOADED = 'Y' AND
1704 p_entity_name = 'HZ_ORGANIZATION_PROFILES'
1705 THEN
1706 l_index := getIndex(G_ORG_ATTRIBUTE_NAME, UPPER(p_attribute_name));
1707
1708 -- Debug info.
1709 IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1710 hz_utility_v2pub.debug(p_message=>'index = '||l_index||', attribute = ' || p_attribute_name,
1711 p_prefix =>l_debug_prefix,
1712 p_msg_level=>fnd_log.level_statement);
1713 END IF;
1714 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
1715 hz_utility_v2pub.debug(p_message=>'getEntityAttrId (-)',
1716 p_prefix=>l_debug_prefix,
1717 p_msg_level=>fnd_log.level_procedure);
1718 END IF;
1719
1720 IF l_index > 0 THEN
1721 RETURN G_ORG_ATTRIBUTE_ID(l_index);
1722 ELSE
1723 RETURN 0;
1724 END IF;
1725 -- ELSIF G_PER_SETUP_LAST_UPDATE_DATE IS NOT NULL AND
1726 ELSIF G_PER_SETUP_LOADED = 'Y' AND
1727 p_entity_name = 'HZ_PERSON_PROFILES'
1728 THEN
1729 l_index := getIndex(G_PER_ATTRIBUTE_NAME, UPPER(p_attribute_name));
1730
1731 -- Debug info.
1732 IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1733 hz_utility_v2pub.debug(p_message=>'index = '||l_index||', attribute = ' || p_attribute_name,
1734 p_prefix =>l_debug_prefix,
1735 p_msg_level=>fnd_log.level_statement);
1736 END IF;
1737 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
1738 hz_utility_v2pub.debug(p_message=>'getEntityAttrId (-)',
1739 p_prefix=>l_debug_prefix,
1740 p_msg_level=>fnd_log.level_procedure);
1741 END IF;
1742
1743 IF l_index > 0 THEN
1744 RETURN G_PER_ATTRIBUTE_ID(l_index);
1745 ELSE
1746 RETURN 0;
1747 END IF;
1748 END IF;
1749 -- ELSIF G_DATASOURCE_LAST_UPDATE_DATE IS NOT NULL THEN
1750 ELSIF G_DATASOURCE_LOADED = 'Y' THEN
1751 IF p_entity_name = 'HZ_PERSON_PROFILES' THEN
1752 RETURN G_PERSON_PROFILE_ID;
1753 ELSIF p_entity_name = 'HZ_ORGANIZATION_PROFILES' THEN
1754 RETURN G_ORGANIZATION_PROFILE_ID;
1755 ELSE
1756 l_index := getIndex(G_ENTITY_NAME, p_entity_name);
1757
1758 -- Debug info.
1759 IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1760 hz_utility_v2pub.debug(p_message=>'index = '||l_index||', attribute = ' || p_attribute_name,
1761 p_prefix =>l_debug_prefix,
1762 p_msg_level=>fnd_log.level_statement);
1763 END IF;
1764 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
1765 hz_utility_v2pub.debug(p_message=>'getEntityAttrId (-)',
1766 p_prefix=>l_debug_prefix,
1767 p_msg_level=>fnd_log.level_procedure);
1768 END IF;
1769
1770
1771 IF l_index > 0 THEN
1772 RETURN G_ENTITY_ID(l_index);
1773 ELSE
1774 RETURN 0;
1775 END IF;
1776 END IF;
1777 END IF;
1778
1779 RETURN 0;
1780
1781 END getEntityAttrId;
1782
1783 /**
1784 * PRIVATE PROCEDURE isSSTColumnUpdatable
1785 *
1786 * DESCRIPTION
1787 * Return 'Y' if the sst column is updatable.
1788 *
1789 * EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
1790 *
1791 * IN:
1792 * p_party_id Party id.
1793 * p_entity_attr_id Entity / attribute id.
1794 * p_entity_name Entity name.
1795 * p_attribute_name Attribute name.
1796 * p_value_is_null 'Y' if the attribute is NULL.
1797 * p_data_source_type Data source type.
1798 * OUT:
1799 * x_exception_type Exception type.
1800 *
1801 * MODIFICATION HISTORY
1802 *
1803 * 04-30-2002 Jianying Huang o Created.
1804 */
1805
1806 FUNCTION isSSTColumnUpdatable (
1807 p_party_id IN NUMBER,
1808 p_entity_name IN VARCHAR2,
1809 p_attribute_name IN VARCHAR2,
1810 p_entity_attr_id IN NUMBER,
1811 p_value_is_null IN VARCHAR2,
1812 p_data_source_type IN VARCHAR2,
1813 x_exception_type OUT NOCOPY VARCHAR2,
1814 p_is_null IN VARCHAR2
1815 ) RETURN VARCHAR2 IS
1816
1817 l_entity_attr_id NUMBER;
1818 l_real_data_source_type VARCHAR2(30);
1819 l_real_data_source_ranking NUMBER;
1820 l_new_data_source_ranking NUMBER;
1821 l_data_source_type VARCHAR2(30) := p_data_source_type;
1822 l_debug_prefix VARCHAR2(30) := '';
1823
1824 BEGIN
1825 -- Debug info.
1826 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
1827 hz_utility_v2pub.debug(p_message=>'isSSTColumnUpdatable (+)',
1828 p_prefix=>l_debug_prefix,
1829 p_msg_level=>fnd_log.level_procedure);
1830 END IF;
1831
1832 IF p_data_source_type = G_MISS_ACTUAL_CONTENT_SOURCE THEN
1833 l_data_source_type := G_MISS_CONTENT_SOURCE_TYPE;
1834 END IF;
1835
1836 -- get entity / attribute id.
1837
1838 IF p_entity_attr_id IS NULL THEN
1839 l_entity_attr_id := getEntityAttrId(p_entity_name, p_attribute_name);
1840 ELSE
1841 l_entity_attr_id := p_entity_attr_id;
1842 END IF;
1843 x_exception_type := 'Migration';
1844
1845 -- find out the ranking of the comming data source.
1846 l_new_data_source_ranking :=
1847 getDataSourceRanking(
1848 p_entity_attr_id => l_entity_attr_id,
1849 p_data_source_type => l_data_source_type);
1850
1851 -- Debug info.
1852 IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1853 hz_utility_v2pub.debug(p_message=>'new_data_source = '||l_data_source_type||', '||
1854 'new_data_source_ranking = '||l_new_data_source_ranking,
1855 p_prefix =>l_debug_prefix,
1856 p_msg_level=>fnd_log.level_statement);
1857 END IF;
1858
1859 IF l_new_data_source_ranking = 0 THEN
1860 -- Debug info.
1861 IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1862 hz_utility_v2pub.debug(p_message=>'return N: new data source ranking is 0',
1863 p_prefix =>l_debug_prefix,
1864 p_msg_level=>fnd_log.level_statement);
1865 END IF;
1866
1867 RETURN 'N';
1868 END IF;
1869
1870 IF l_new_data_source_ranking = -1 THEN
1871 -- Debug info.
1872 IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1873 hz_utility_v2pub.debug(p_message=>'return Y (MRR): both data source ranking is -1',
1874 p_prefix =>l_debug_prefix,
1875 p_msg_level=>fnd_log.level_statement);
1876 END IF;
1877 IF p_is_null = 'N' THEN
1878 x_exception_type := 'MRR';
1879 ELSE
1880 x_exception_type := 'MRN';
1881 END IF;
1882
1883 RETURN 'Y';
1884 END IF;
1885
1886 -- if the value is null, the column is updatable.
1887
1888 IF p_value_is_null = 'Y' THEN
1889 -- Debug info.
1890 IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1891 hz_utility_v2pub.debug(p_message=>'return Y: value is null',
1892 p_prefix =>l_debug_prefix,
1893 p_msg_level=>fnd_log.level_statement);
1894 END IF;
1895
1896 RETURN 'Y';
1897 END IF;
1898
1899
1900 -- Debug info.
1901 IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1902 hz_utility_v2pub.debug(p_message=>'entity_attr_id = '||l_entity_attr_id||', entity_name = '||
1903 p_entity_name||', attribute_name = '||p_attribute_name,
1904 p_prefix =>l_debug_prefix,
1905 p_msg_level=>fnd_log.level_statement);
1906 END IF;
1907
1908 -- if the attribute is not in setup table, the attribute is updatable.
1909
1910 IF l_entity_attr_id = 0 THEN
1911 -- Debug info.
1912 IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1913 hz_utility_v2pub.debug(p_message=>'return Y: entity_attr_id is 0 (non-restricted column)',
1914 p_prefix =>l_debug_prefix,
1915 p_msg_level=>fnd_log.level_statement);
1916 END IF;
1917
1918 RETURN 'Y';
1919 END IF;
1920
1921 -- find out the real data source and the ranking of the data source.
1922
1923 IF G_REAL_DATA_SOURCE.EXISTS(l_entity_attr_id) THEN
1924 l_real_data_source_type := G_REAL_DATA_SOURCE(l_entity_attr_id);
1925
1926 l_real_data_source_ranking :=
1927 getDataSourceRanking(
1928 p_entity_attr_id => l_entity_attr_id,
1929 p_data_source_type => l_real_data_source_type);
1930
1931 -- Debug info.
1932 IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1933 hz_utility_v2pub.debug(p_message=>'real_data_source = '||l_real_data_source_type||', '||
1934 'real_data_source_ranking = '||l_real_data_source_ranking,
1935 p_prefix =>l_debug_prefix,
1936 p_msg_level=>fnd_log.level_statement);
1937 END IF;
1938
1939 -- if real data source ranking is 0, the attribute is updatable.
1940
1941 IF l_real_data_source_ranking = 0 THEN
1942 -- Debug info.
1943 IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1944 hz_utility_v2pub.debug(p_message=>'return Y (Migration): real data source ranking is 0',
1945 p_prefix =>l_debug_prefix,
1946 p_msg_level=>fnd_log.level_statement);
1947 END IF;
1948
1949 RETURN 'Y';
1950 END IF;
1951
1952
1953 -- find out the exception type.
1954
1955 IF NOT G_EXCEPTION_TYPE.EXISTS(l_entity_attr_id) OR
1956 G_EXCEPTION_TYPE(l_entity_attr_id) IS NULL
1957 THEN
1958 x_exception_type := 'Migration';
1959 ELSE
1960 x_exception_type := G_EXCEPTION_TYPE(l_entity_attr_id);
1961 END IF;
1962
1963 -- Debug info.
1964 IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1965 hz_utility_v2pub.debug(p_message=>'exp_type = '||x_exception_type,
1966 p_prefix =>l_debug_prefix,
1967 p_msg_level=>fnd_log.level_statement);
1968 END IF;
1969 ELSE
1970 -- Debug info.
1971 IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1972 hz_utility_v2pub.debug(p_message=>'return Y (Migration): real data source does not exist',
1973 p_prefix =>l_debug_prefix,
1974 p_msg_level=>fnd_log.level_statement);
1975 END IF;
1976
1977 RETURN 'Y';
1978 END IF;
1979
1980 -- Debug info.
1981 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
1982 hz_utility_v2pub.debug(p_message=>'isSSTColumnUpdatable (-)',
1983 p_prefix=>l_debug_prefix,
1984 p_msg_level=>fnd_log.level_procedure);
1985 END IF;
1986
1987 -- call real function for updatable checking.
1988
1989 RETURN
1990 isSSTColumnUpdatable (
1991 p_party_id => p_party_id,
1992 p_entity_attr_id => l_entity_attr_id,
1993 p_real_data_source_type => l_real_data_source_type,
1994 p_real_data_source_ranking => l_real_data_source_ranking,
1995 p_new_data_source_type => p_data_source_type,
1996 p_new_data_source_ranking => l_new_data_source_ranking,
1997 p_exception_type => x_exception_type );
1998
1999 END isSSTColumnUpdatable;
2000
2001 /**
2002 * PRIVATE PROCEDURE isSSTColumnUpdatable
2003 *
2004 * DESCRIPTION
2005 * Return 'Y' if the sst column is updatable.
2006 *
2007 * EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
2008 *
2009 * IN:
2010 * p_party_id Party id.
2011 * p_entity_attr_id Entity / attribute id.
2012 * p_real_data_source_type Real data source type.
2013 * p_real_data_source_ranking Real data source ranking.
2014 * p_new_data_source_type New data source type.
2015 * p_new_data_source_ranking New data source ranking.
2016 * p_exception_type Exception type.
2017 * OUT:
2018 * x_exception_type Exception type.
2019 *
2020 * MODIFICATION HISTORY
2021 *
2022 * 04-30-2002 Jianying Huang o Created.
2023 * 12-30-2004 Rajib Ranjan Borah o SSM SST Integration and Extension.
2024 * Pass relevant orig_systems to isThirdPartyDataOverwriteable
2025 * and isUserDataOverwriteable.
2026 * Exception type will be 'Migration' after
2027 * third party overwrites user data because of third party rule.
2028 */
2029
2030 FUNCTION isSSTColumnUpdatable (
2031 p_party_id IN NUMBER,
2032 p_entity_attr_id IN NUMBER,
2033 p_real_data_source_type IN VARCHAR2,
2034 p_real_data_source_ranking IN NUMBER,
2035 p_new_data_source_type IN VARCHAR2,
2036 p_new_data_source_ranking IN NUMBER,
2037 p_exception_type IN OUT NOCOPY VARCHAR2
2038 ) RETURN VARCHAR2 IS
2039
2040 l_update VARCHAR2(1) := 'N';
2041 l_debug_prefix VARCHAR2(30) := '';
2042 l_tmp_d VARCHAR2(1);
2043 p_entity VARCHAR2(30);
2044
2045 BEGIN
2046
2047 -- Debug info.
2048 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
2049 hz_utility_v2pub.debug(p_message=>'isSSTColumnUpdatable (+)',
2050 p_prefix=>l_debug_prefix,
2051 p_msg_level=>fnd_log.level_procedure);
2052 END IF;
2053
2054 IF p_real_data_source_ranking = p_new_data_source_ranking THEN
2055 l_update := 'Y';
2056 ELSIF p_real_data_source_ranking < p_new_data_source_ranking THEN
2057 IF p_new_data_source_type = G_MISS_ACTUAL_CONTENT_SOURCE /*G_MISS_CONTENT_SOURCE_TYPE*/ THEN
2058 IF isThirdPartyDataOverwriteable(p_entity_attr_id,p_real_data_source_type) = 'Y'
2059 THEN
2060 l_update := 'Y'; p_exception_type := 'Exception';
2061
2062 -- Debug info.
2063 IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
2064 hz_utility_v2pub.debug(p_message=>'case 1',
2065 p_prefix =>l_debug_prefix,
2066 p_msg_level=>fnd_log.level_statement);
2067 END IF;
2068
2069 END IF;
2070 ELSE
2071
2072 select entity_name into p_entity from hz_entity_attributes
2073 where entity_attr_id = p_entity_attr_id;
2074 -- Bug 4244112 : Added to populate exceptions for case -
2075 -- DNB = 1, no DNB profile exists
2076 -- SBL = 3, creating SBL profile with value for this attribute
2077
2078 if p_entity = 'HZ_ORGANIZATION_PROFILES' THEN
2079 BEGIN
2080 select '1' into l_tmp_d from hz_organization_profiles where party_id = p_party_id
2081 and actual_content_source = p_real_data_source_type
2082 -- Bug 4482630 : query only active profiles
2083 and EFFECTIVE_END_DATE is NULL;
2084 exception
2085 when no_data_found then
2086 l_update := 'Y'; p_exception_type := 'Migration';
2087 IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
2088 hz_utility_v2pub.debug(p_message=>'case 1 - for two third parties',
2089 p_prefix =>l_debug_prefix,
2090 p_msg_level=>fnd_log.level_statement);
2091 END IF;
2092 END;
2093 elsif p_entity = 'HZ_PERSON_PROFILES' THEN
2094 BEGIN
2095 select '1' into l_tmp_d from hz_person_profiles where party_id = p_party_id
2096 and actual_content_source = p_real_data_source_type
2097 and EFFECTIVE_END_DATE is NULL;
2098 exception
2099 when no_data_found then
2100 l_update := 'Y'; p_exception_type := 'Migration';
2101 IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
2102 hz_utility_v2pub.debug(p_message=>'case 1 - for two third parties',
2103 p_prefix =>l_debug_prefix,
2104 p_msg_level=>fnd_log.level_statement);
2105 END IF;
2106 END;
2107 end if;
2108
2109 END IF;
2110 ELSE
2111 IF p_real_data_source_type <> G_MISS_CONTENT_SOURCE_TYPE AND
2112 p_new_data_source_type NOT IN (G_MISS_ACTUAL_CONTENT_SOURCE, G_MISS_CONTENT_SOURCE_TYPE)
2113 THEN
2114 l_update := 'Y'; --x_exception_type := 'Migration';
2115
2116 -- Debug info.
2117 IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
2118 hz_utility_v2pub.debug(p_message=>'case 2',
2119 p_prefix =>l_debug_prefix,
2120 p_msg_level=>fnd_log.level_statement);
2121 END IF;
2122
2123
2124 ELSIF p_exception_type = 'Migration' THEN
2125 l_update := 'Y';
2126
2127 -- Debug info.
2128 IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
2129 hz_utility_v2pub.debug(p_message=>'case 3',
2130 p_prefix =>l_debug_prefix,
2131 p_msg_level=>fnd_log.level_statement);
2132 END IF;
2133 ELSIF p_real_data_source_type = G_MISS_CONTENT_SOURCE_TYPE THEN
2134 IF isUserDataOverwriteable(p_entity_attr_id,p_new_data_source_type) = 'Y' THEN
2135 l_update := 'Y';
2136 p_exception_type := 'Migration';
2137
2138 -- Debug info.
2139 IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
2140 hz_utility_v2pub.debug(p_message=>'case 4',
2141 p_prefix =>l_debug_prefix,
2142 p_msg_level=>fnd_log.level_statement);
2143 END IF;
2144 END IF;
2145 END IF;
2146 END IF;
2147
2148 -- Debug info.
2149 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
2150 hz_utility_v2pub.debug(p_message=>'isSSTColumnUpdatable (-)',
2151 p_prefix=>l_debug_prefix,
2152 p_msg_level=>fnd_log.level_procedure);
2153 END IF;
2154 RETURN l_update;
2155
2156 END isSSTColumnUpdatable;
2157
2158 /**
2159 * PROCEDURE areSSTColumnsUpdeable
2160 *
2161 * DESCRIPTION
2162 * Return a list to indicate which SST attributes are updatable and which are not.
2163 *
2164 * EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
2165 *
2166 * ARGUMENTS
2167 * IN:
2168 * p_party_id Party Id.
2169 * p_entity_name Entity name.
2170 * p_attribute_name_list Attribute name list.
2171 * p_value_is_null_list 'Y' if the corresponding SST column is null.
2172 * p_data_source_type Comming data source.
2173 * p_raise_error_flag Raise error flag.
2174 * p_known_dict_id 'Y' if use knew entity id.
2175 * IN/OUT:
2176 * x_return_status Return status.
2177 * OUT:
2178 * x_updatable_flag_list Updatable list.
2179 * x_exception_type_list Exception type list.
2180 *
2181 * NOTES
2182 *
2183 * MODIFICATION HISTORY
2184 * The procedure should only be called if the mix-n-match is enable for
2185 * the entity.
2186 *
2187 * 05-01-2002 Jianying Huang o Created
2188 */
2189
2190 PROCEDURE areSSTColumnsUpdeable (
2191 p_party_id IN NUMBER,
2192 p_entity_name IN VARCHAR2,
2193 p_attribute_name_list IN INDEXVARCHAR30List,
2194 p_value_is_null_list IN INDEXVARCHAR1List,
2195 p_data_source_type IN VARCHAR2 := G_MISS_ACTUAL_CONTENT_SOURCE,
2196 x_updatable_flag_list OUT NOCOPY INDEXVARCHAR1List,
2197 x_exception_type_list OUT NOCOPY INDEXVARCHAR30List,
2198 x_return_status IN OUT NOCOPY VARCHAR2,
2199 p_raise_error_flag IN VARCHAR2 DEFAULT 'N',
2200 p_known_dict_id IN VARCHAR2 DEFAULT 'N',
2201 p_new_value_is_null_list IN HZ_MIXNM_UTILITY.INDEXVARCHAR1List
2202 ) IS
2203
2204 i NUMBER;
2205 l_entity_attr_id NUMBER;
2206 l_names VARCHAR2(255);
2207 l_message_name VARCHAR2(30);
2208 l_count NUMBER;
2209 l_debug_prefix VARCHAR2(30) := '';
2210
2211 BEGIN
2212
2213 -- Debug info.
2214 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
2215 hz_utility_v2pub.debug(p_message=>'areSSTColumnsUpdeable (+)',
2216 p_prefix=>l_debug_prefix,
2217 p_msg_level=>fnd_log.level_procedure);
2218 END IF;
2219 IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
2220 hz_utility_v2pub.debug(p_message=>'raise_error = '||p_raise_error_flag||', '||
2221 'known_dict = '||p_known_dict_id,
2222 p_prefix =>l_debug_prefix,
2223 p_msg_level=>fnd_log.level_statement);
2224 END IF;
2225
2226 -- return if the attribute name list is empty.
2227 IF p_attribute_name_list IS NULL OR
2228 p_attribute_name_list.COUNT = 0
2229 THEN
2230 -- Debug info.
2231 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
2232 hz_utility_v2pub.debug(p_message=>'areSSTColumnsUpdeable (-)',
2233 p_prefix=>l_debug_prefix,
2234 p_msg_level=>fnd_log.level_procedure);
2235 END IF;
2236
2237 RETURN;
2238 END IF;
2239
2240 -- load all of related setups and cache them.
2241 cacheSetupForPartyProfiles(p_party_id, p_entity_name);
2242
2243 -- start to process each attribute.
2244
2245 i := p_attribute_name_list.FIRST;
2246 WHILE i <= p_attribute_name_list.LAST LOOP
2247
2248 -- find out the attribute id if user knew the attribute id.
2249
2250 IF p_known_dict_id = 'Y' THEN
2251 l_entity_attr_id := i;
2252 ELSE
2253 l_entity_attr_id := NULL;
2254 END IF;
2255
2256 -- Debug info.
2257 IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
2258 hz_utility_v2pub.debug(p_message=>'i = '||i||', attribute_name = '||p_attribute_name_list(i),
2259 p_prefix =>l_debug_prefix,
2260 p_msg_level=>fnd_log.level_statement);
2261 END IF;
2262
2263 -- find out if the attribute is updatable.
2264 x_updatable_flag_list(i) :=
2265 isSSTColumnupdatable(
2266 p_party_id => p_party_id,
2267 p_entity_name => p_entity_name,
2268 p_attribute_name => p_attribute_name_list(i),
2269 p_entity_attr_id => l_entity_attr_id,
2270 p_value_is_null => p_value_is_null_list(i),
2271 p_data_source_type => p_data_source_type,
2272 x_exception_type => x_exception_type_list(i),
2273 p_is_null => p_new_value_is_null_list(i));
2274 -- Debug info.
2275 IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
2276 hz_utility_v2pub.debug(p_message=>'updatable = '||x_updatable_flag_list(i)||
2277 ', exp_type = '||x_exception_type_list(i),
2278 p_prefix =>l_debug_prefix,
2279 p_msg_level=>fnd_log.level_statement);
2280 END IF;
2281
2282 i := p_attribute_name_list.NEXT(i);
2283 END LOOP;
2284
2285 -- raise error for those non-updatable attributes.
2286
2287 IF p_raise_error_flag = 'Y' THEN
2288 l_names := ''; l_count := 0;
2289
2290 i := p_attribute_name_list.FIRST;
2291 WHILE i <= p_attribute_name_list.LAST LOOP
2292 IF x_updatable_flag_list(i) = 'N' THEN
2293 IF l_names IS NULL OR
2294 LENGTHB(l_names) <= 220
2295 THEN
2296 l_count := l_count + 1;
2297 l_names := l_names||p_attribute_name_list(i)||', ';
2298 END IF;
2299 END IF;
2300 i := p_attribute_name_list.NEXT(i);
2301 END LOOP;
2302
2303 IF l_count > 0 THEN
2304 IF l_count = 1 THEN
2305 l_message_name := 'HZ_API_SST_NONUPDATEABLE_COL';
2306 ELSE
2307 l_message_name := 'HZ_API_SST_NONUPDATEABLE_COLS';
2308 END IF;
2309
2310 l_names := SUBSTRB(l_names, 1, LENGTHB(l_names)-2);
2311
2312 fnd_message.set_name('AR', l_message_name);
2313 fnd_message.set_token('COLUMN',l_names);
2314 fnd_msg_pub.add;
2315 x_return_status := FND_API.G_RET_STS_ERROR;
2316 RAISE FND_API.G_EXC_ERROR;
2317 END IF;
2318 END IF;
2319
2320 -- Debug info.
2321 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
2322 hz_utility_v2pub.debug(p_message=>'areSSTColumnsUpdeable (-)',
2323 p_prefix=>l_debug_prefix,
2324 p_msg_level=>fnd_log.level_procedure);
2325 END IF;
2326
2327 END areSSTColumnsUpdeable;
2328
2329 /**
2330 * PRIVATE PROCEDURE updateExceptions
2331 *
2332 * DESCRIPTION
2333 * Update exception table which is used to trace data source
2334 * for each restricted attribute.
2335 *
2336 * EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
2337 *
2338 * IN:
2339 * p_create_update_sst_flag Create / update SST profile flag.
2340 * p_party_id Party id.
2341 * p_data_source_type Data source type.
2342 * p_updatable_flag_list A list of updatable property.
2343 * p_exception_type_list A list of exception type.
2344 * p_sst_value_is_not_null_list A 'Y'/'N' list to indicate if the attribute
2345 * in the SST record is NULL.
2346 * p_data_source_list Data source list.
2347 *
2348 * MODIFICATION HISTORY
2349 *
2350 * 04-30-2002 Jianying Huang o Created.
2351 * 02-28-2005 Rajib Ranjan Borah o Bug 4156090. Caching logic for G_ATTRIBUTE_DATA_SOURCE
2352 * is changed.
2353 */
2354
2355 PROCEDURE updateExceptions (
2356 p_create_update_sst_flag IN VARCHAR2,
2357 p_party_id IN NUMBER,
2358 p_data_source_type IN VARCHAR2,
2359 p_name_list IN INDEXVARCHAR30List,
2360 p_updatable_flag_list IN INDEXVARCHAR1List,
2361 p_exception_type_list IN INDEXVARCHAR30List,
2362 p_sst_value_is_not_null_list IN INDEXVARCHAR1List,
2363 p_data_source_list IN INDEXVARCHAR30List
2364 ) IS
2365
2366 i_entity_attr_id INDEXIDList;
2367 i_entity_attr_id1 INDEXIDList;
2368 i_exception_type INDEXVARCHAR30List;
2369 i_real_data_source INDEXVARCHAR30List;
2370 i_winner INDEXVARCHAR30List;
2371 l_winner VARCHAR2(30);
2372 l_real_data_source VARCHAR2(30);
2373 i NUMBER;
2374 j NUMBER;
2375 k NUMBER;
2376 l_data_source_type VARCHAR2(30) := p_data_source_type;
2377 l_max NUMBER := 0;
2378 l_debug_prefix VARCHAR2(30) := '';
2379 l_pos2 NUMBER;
2380 l_pos1 NUMBER;
2381
2382 BEGIN
2383
2384 -- Debug info.
2385 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
2386 hz_utility_v2pub.debug(p_message=>'updateExceptions (+)',
2387 p_prefix=>l_debug_prefix,
2388 p_msg_level=>fnd_log.level_procedure);
2389 END IF;
2390 IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
2391 hz_utility_v2pub.debug(p_message=>'p_create_update_sst_flag = '||p_create_update_sst_flag,
2392 p_prefix =>l_debug_prefix,
2393 p_msg_level=>fnd_log.level_statement);
2394 END IF;
2395
2396 IF p_data_source_type = G_MISS_ACTUAL_CONTENT_SOURCE THEN
2397 l_data_source_type := G_MISS_CONTENT_SOURCE_TYPE;
2398 END IF;
2399
2400 -- we always do insert into the exception table if we are creating
2401 -- a SST profile.
2402
2403 IF p_create_update_sst_flag = 'C' THEN
2404
2405 l_max := get_max(
2406 p_sst_value_is_not_null_list.LAST,
2407 p_name_list.LAST,
2408 p_updatable_flag_list.LAST);
2409
2410 IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
2411 hz_utility_v2pub.debug( p_message => 'L_MAX = '|| to_char(l_max),
2412 p_prefix => l_debug_prefix,
2413 p_msg_level => fnd_log.level_statement);
2414 END IF;
2415 i := 1; j := 0; k :=0;
2416 WHILE i <= l_max LOOP
2417
2418 IF G_ATTRIBUTE_DATA_SOURCE.EXISTS(i) THEN
2419 l_winner := 'SST';
2420
2421 -- l_winner :=
2422 -- SUBSTRB(G_ATTRIBUTE_DATA_SOURCE(i),1,INSTRB(G_ATTRIBUTE_DATA_SOURCE(i),',')-1);
2423
2424 -- Bug 4156090.
2425 -- Caching logic of G_ATTRIBUTE_DATA_SOURCE is changed.
2426
2427 IF p_updatable_flag_list.EXISTS(i) AND
2428 p_updatable_flag_list(i) = 'Y'
2429 THEN
2430 IF p_exception_type_list.EXISTS(i) AND p_exception_type_list(i) = 'MRR' THEN
2431 l_winner := l_data_source_type;
2432 ELSE
2433 l_pos2 := instrb(G_ATTRIBUTE_DATA_SOURCE(i),':1,');
2434 l_pos1 := instrb(G_ATTRIBUTE_DATA_SOURCE(i),',',-(lengthb(G_ATTRIBUTE_DATA_SOURCE(i))-l_pos2),1);
2435 l_winner := G_ORIG_SYSTEM_LIST(substrb(G_ATTRIBUTE_DATA_SOURCE(i),l_pos1+1,l_pos2-l_pos1-1));
2436 END IF;
2437 l_real_data_source := l_data_source_type;
2438
2439 IF p_exception_type_list.EXISTS(i) AND p_exception_type_list(i) = 'MRR' THEN
2440
2441 k := k+1;
2442 i_entity_attr_id1(k) := i;
2443 G_EXCEPTION_TYPE(i) := 'MRR';
2444 G_REAL_DATA_SOURCE(i) := l_real_data_source;
2445 IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
2446 hz_utility_v2pub.debug(p_message=>'attribute_id = ' || i || ', L_WINNER = '||
2447 l_winner||', ex = '||G_EXCEPTION_TYPE(i),
2448 p_prefix =>l_debug_prefix,
2449 p_msg_level=>fnd_log.level_statement);
2450 END IF;
2451 ELSIF l_winner <> l_real_data_source THEN
2452 j := j+1;
2453 i_entity_attr_id(j) := i;
2454 IF p_exception_type_list.EXISTS(i) AND
2455 p_exception_type_list(i) IS NOT NULL
2456 THEN
2457 i_exception_type(j) := p_exception_type_list(i);
2458 ELSE
2459 i_exception_type(j) := 'Migration';
2460 END IF;
2461 i_real_data_source(j) := l_real_data_source;
2462 G_EXCEPTION_TYPE(j) := i_exception_type(j);
2463 G_REAL_DATA_SOURCE(j) := l_real_data_source;
2464 IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
2465 hz_utility_v2pub.debug(p_message=>'attribute_id = ' || i || ', L_WINNER = '||
2466 l_winner||', ex = '||G_EXCEPTION_TYPE(j),
2467 p_prefix =>l_debug_prefix,
2468 p_msg_level=>fnd_log.level_statement);
2469 END IF;
2470 END IF;
2471 ELSIF ((p_sst_value_is_not_null_list.EXISTS(i) AND
2472 p_sst_value_is_not_null_list(i) = 'Y') OR
2473 p_name_list.EXISTS(i)) AND
2474 (NOT p_updatable_flag_list.EXISTS(i) OR
2475 p_updatable_flag_list(i) = 'N')
2476 THEN
2477 l_real_data_source := G_MISS_CONTENT_SOURCE_TYPE;
2478 IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
2479 hz_utility_v2pub.debug(p_message=>'attribute_id = ' || i || ', L_WINNER = '||
2480 l_winner||', ex = migration',
2481 p_prefix =>l_debug_prefix,
2482 p_msg_level=>fnd_log.level_statement);
2483 END IF;
2484 IF l_winner <> l_real_data_source THEN
2485 j := j+1;
2486 i_entity_attr_id(j) := i;
2487 i_exception_type(j) := 'Migration';
2488 i_real_data_source(j) := l_real_data_source;
2489 G_EXCEPTION_TYPE(i) := 'Migration';
2490 G_REAL_DATA_SOURCE(i) := l_real_data_source;
2491 END IF;
2492 END IF;
2493 END IF;
2494
2495 i := i+1;
2496 END LOOP;
2497
2498 FORALL i IN 1..k
2499 UPDATE hz_win_source_exceps
2500 SET content_source_type = l_data_source_type,
2501 -- exception_type = i_exception_type(i),
2502 last_updated_by = hz_utility_v2pub.last_updated_by,
2503 last_update_login = hz_utility_v2pub.last_update_login,
2504 last_update_date = SYSDATE
2505 WHERE party_id = p_party_id
2506 AND entity_attr_id = i_entity_attr_id1(i);
2507 -- AND content_source_type <> i_real_data_source(i)
2508 -- AND i_real_data_source(i) <> i_winner(i);
2509
2510 FORALL i IN 1..j
2511 INSERT INTO hz_win_source_exceps (
2512 party_id,
2513 entity_attr_id,
2514 content_source_type,
2515 exception_type,
2516 created_by,
2517 creation_date,
2518 last_update_login,
2519 last_update_date,
2520 last_updated_by
2521 ) --VALUES (
2522 -- Bug 4244112 : insert only if it is Rank attribute
2523 SELECT
2524 p_party_id,
2525 i_entity_attr_id(i),
2526 i_real_data_source(i),
2527 i_exception_type(i),
2528 hz_utility_v2pub.created_by,
2529 SYSDATE,
2530 hz_utility_v2pub.last_update_login,
2531 SYSDATE,
2532 hz_utility_v2pub.last_updated_by
2533 FROM hz_select_data_sources
2534 WHERE ranking > 0
2535 and content_source_type = 'USER_ENTERED'
2536 and entity_attr_id = i_entity_attr_id(i)
2537 and i_exception_type(i) <> 'MRN';
2538
2539 ELSE -- p_create_update_sst_flag = 'U'
2540 l_real_data_source := l_data_source_type;
2541 i := p_updatable_flag_list.FIRST; j := 0;
2542 WHILE i <= p_updatable_flag_list.LAST LOOP
2543 IF p_updatable_flag_list(i) = 'Y' THEN
2544 j := j + 1;
2545
2546 i_entity_attr_id(j) := i;
2547 IF p_exception_type_list.EXISTS(i) AND
2548 p_exception_type_list(i) IS NOT NULL
2549 THEN
2550 i_exception_type(j) := p_exception_type_list(i);
2551 ELSE
2552 i_exception_type(j) := 'Migration';
2553 END IF;
2554 IF p_data_source_list.EXISTS(i) AND
2555 p_data_source_list(i) IS NOT NULL
2556 THEN
2557 i_real_data_source(j) := p_data_source_list(i);
2558 ELSE
2559 i_real_data_source(j) := l_data_source_type;
2560 END IF;
2561 G_EXCEPTION_TYPE(i) := i_exception_type(j);
2562 G_REAL_DATA_SOURCE(i) := i_real_data_source(j);
2563
2564 -- Bug 4156090.
2565 -- Caching logic for G_ATTRIBUTE_DATA_SOURCE is changed.
2566
2567 --i_winner(j) :=
2568 --SUBSTRB(G_ATTRIBUTE_DATA_SOURCE(i),1,INSTRB(G_ATTRIBUTE_DATA_SOURCE(i),',')-1);
2569 -- Bug 4156090 : for Date attributes, winner data source is same as
2570 -- the passed in actual_content_source.
2571 IF p_exception_type_list.EXISTS(i) AND (p_exception_type_list(i) = 'MRR' OR p_exception_type_list(i) = 'MRN')THEN
2572 i_winner(j) := l_data_source_type;
2573 ELSE
2574 l_pos2 := instrb(G_ATTRIBUTE_DATA_SOURCE(i),':1,');
2575 l_pos1 := instrb(G_ATTRIBUTE_DATA_SOURCE(i),',',-(lengthb(G_ATTRIBUTE_DATA_SOURCE(i))-l_pos2),1);
2576 i_winner(j) := G_ORIG_SYSTEM_LIST(substrb(G_ATTRIBUTE_DATA_SOURCE(i),l_pos1+1,l_pos2-l_pos1-1));
2577 END IF;
2578
2579 END IF;
2580 i := p_updatable_flag_list.NEXT(i);
2581 END LOOP;
2582 -- Debug info.
2583 IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
2584 FOR i IN 1..j LOOP
2585 hz_utility_v2pub.debug(p_message=>i_entity_attr_id(i)||' : exp_type = '||i_exception_type(i)||
2586 ', winner = '||i_winner(i),
2587 p_prefix =>l_debug_prefix,
2588 p_msg_level=>fnd_log.level_statement);
2589 END LOOP;
2590 END IF;
2591
2592 FORALL i IN 1..j
2593 DELETE hz_win_source_exceps
2594 WHERE party_id = p_party_id
2595 AND entity_attr_id = i_entity_attr_id(i)
2596 AND i_real_data_source(i) = i_winner(i)
2597 AND exception_type <> 'MRR';
2598
2599 IF j > 0 THEN
2600 -- Debug info.
2601 IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
2602 hz_utility_v2pub.debug(p_message=>'Delete '||SQL%ROWCOUNT||' records',
2603 p_prefix =>l_debug_prefix,
2604 p_msg_level=>fnd_log.level_statement);
2605 END IF;
2606 END IF;
2607
2608 FOR i IN 1..j LOOP
2609 IF i_exception_type(i) = 'MRR' THEN
2610
2611 UPDATE hz_win_source_exceps
2612 SET content_source_type = l_real_data_source,
2613 exception_type = i_exception_type(i),
2614 last_updated_by = hz_utility_v2pub.last_updated_by,
2615 last_update_login = hz_utility_v2pub.last_update_login,
2616 last_update_date = SYSDATE
2617 WHERE party_id = p_party_id
2618 AND entity_attr_id = i_entity_attr_id(i);
2619
2620 ELSE
2621 UPDATE hz_win_source_exceps
2622 SET content_source_type = l_real_data_source,
2623 exception_type = i_exception_type(i),
2624 last_updated_by = hz_utility_v2pub.last_updated_by,
2625 last_update_login = hz_utility_v2pub.last_update_login,
2626 last_update_date = SYSDATE
2627 WHERE party_id = p_party_id
2628 AND entity_attr_id = i_entity_attr_id(i)
2629 AND content_source_type <> i_real_data_source(i)
2630 AND i_real_data_source(i) <> i_winner(i)
2631 AND i_exception_type(i) <> 'MRN';
2632 END IF;
2633 END LOOP;
2634
2635 IF j > 0 THEN
2636 -- Debug info.
2637 IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
2638 hz_utility_v2pub.debug(p_message=>'Update '||SQL%ROWCOUNT||' records',
2639 p_prefix =>l_debug_prefix,
2640 p_msg_level=>fnd_log.level_statement);
2641 END IF;
2642 END IF;
2643
2644 FOR i IN 1..j LOOP
2645 IF i_real_data_source(i) <> i_winner(i) THEN
2646 INSERT INTO hz_win_source_exceps (
2647 party_id,
2648 entity_attr_id,
2649 content_source_type,
2650 exception_type,
2651 created_by,
2652 creation_date,
2653 last_update_login,
2654 last_update_date,
2655 last_updated_by
2656 ) SELECT
2657 p_party_id,
2658 i_entity_attr_id(i),
2659 i_real_data_source(i),
2660 i_exception_type(i),
2661 hz_utility_v2pub.created_by,
2662 SYSDATE,
2663 hz_utility_v2pub.last_update_login,
2664 SYSDATE,
2665 hz_utility_v2pub.last_updated_by
2666 FROM dual
2667 WHERE NOT EXISTS (
2668 SELECT 'Y'
2669 FROM hz_win_source_exceps
2670 WHERE party_id = p_party_id
2671 AND entity_attr_id = i_entity_attr_id(i)
2672 AND i_exception_type(i) <> 'MRN');
2673
2674 IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
2675 hz_utility_v2pub.debug(p_message=>'Insert '||SQL%ROWCOUNT||' records',
2676 p_prefix =>l_debug_prefix,
2677 p_msg_level=>fnd_log.level_statement);
2678 END IF;
2679 END IF;
2680 END LOOP;
2681
2682 END IF;
2683
2684 -- Debug info.
2685 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
2686 hz_utility_v2pub.debug(p_message=>'updateExceptions (-)',
2687 p_prefix=>l_debug_prefix,
2688 p_msg_level=>fnd_log.level_procedure);
2689 END IF;
2690
2691 END updateExceptions;
2692
2693 /**
2694 * PROCEDURE
2695 * create_exception.
2696 *
2697 * DESCRIPTION
2698 * Creates records in HZ_WIN_SOURCE_EXCEPTIONS when a party (organization/ person)
2699 * is created from non-user_entered source systems and no prior user-entered profile
2700 * exist for that party.
2701 *
2702 *
2703 * EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
2704 * HZ_MIXNM_API_DYNAMIC_PKG.initAttributeList
2705 * HZ_MIXNM_UTILITY.cacheSetupForPartyProfiles
2706 * HZ_MIXNM_UTILITY.getEntityAttrId
2707 * HZ_MIXNM_UTILITY.getDataSourceRanking
2708 *
2709 * ARGUMENTS
2710 * IN:
2711 * p_party_type Either 'ORGANIZATION' or 'PERSON'
2712 * p_organization_rec
2713 * p_person_rec
2714 * p_third_party_content_source
2715 *
2716 * OUT:
2717 *
2718 * NOTES
2719 * This will be called only from HZ_PARTY_V2PUB.do_create_party.
2720 * And only when a new party is created by a non-user_entered source system.
2721 *
2722 * MODIFICATION HISTORY
2723 *
2724 * 12-30-2004 Rajib Ranjan Borah o SSM SST Integration and Extension. Created.
2725 */
2726
2727 PROCEDURE create_exceptions (
2728 p_party_type IN VARCHAR2,
2729 p_organization_rec IN HZ_PARTY_V2PUB.ORGANIZATION_REC_TYPE
2730 DEFAULT HZ_PARTY_V2PUB.G_MISS_ORGANIZATION_REC,
2731 p_person_rec IN HZ_PARTY_V2PUB.PERSON_REC_TYPE
2732 DEFAULT HZ_PARTY_V2PUB.G_MISS_PERSON_REC,
2733 p_third_party_content_source IN VARCHAR2,
2734 p_party_id IN NUMBER
2735 )
2736 IS
2737 l_name_list INDEXVARCHAR30List;
2738 l_null_list INDEXVARCHAR1List;
2739 i NUMBER;
2740 third_party_rank NUMBER;
2741 user_entered_rank NUMBER;
2742 l_entity_name VARCHAR2(30);
2743 l_entity_attr_id NUMBER;
2744 l_party_id NUMBER;
2745 l_debug_prefix VARCHAR2(30);
2746 BEGIN
2747
2748 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
2749 hz_utility_v2pub.debug(p_message=>'HZ_MIXNM_UTILITY.create_exceptions (+)',
2750 p_prefix=>l_debug_prefix,
2751 p_msg_level=>fnd_log.level_procedure);
2752 END IF;
2753
2754 IF p_party_type = 'ORGANIZATION' THEN
2755
2756 HZ_MIXNM_API_DYNAMIC_PKG.initAttributeList
2757 (p_create_update_flag => 'C',
2758 p_new_rec => p_organization_rec,
2759 p_old_rec => HZ_PARTY_V2PUB.G_MISS_ORGANIZATION_REC,
2760 x_name_list => l_name_list,
2761 x_new_value_is_null_list => l_null_list);
2762
2763 l_entity_name := 'HZ_ORGANIZATION_PROFILES';
2764
2765 ELSE -- 'PERSON'
2766
2767 HZ_MIXNM_API_DYNAMIC_PKG.initAttributeList
2768 (p_create_update_flag => 'C',
2769 p_new_rec => p_person_rec,
2770 p_old_rec => HZ_PARTY_V2PUB.G_MISS_PERSON_REC,
2771 x_name_list => l_name_list,
2772 x_new_value_is_null_list => l_null_list);
2773
2774
2775 l_entity_name := 'HZ_PERSON_PROFILES';
2776
2777 END IF;
2778
2779 cacheSetupForPartyProfiles
2780 (p_party_id => p_party_id ,
2781 p_entity_name => l_entity_name);
2782
2783 i := l_name_list.FIRST;
2784 WHILE i <= l_name_list.LAST LOOP
2785
2786 l_entity_attr_id := getEntityAttrId
2787 ( p_entity_name => l_entity_name,
2788 p_attribute_name => l_name_list(i));
2789
2790 third_party_rank :=
2791 getDataSourceRanking
2792 (p_entity_attr_id => l_entity_attr_id,
2793 p_data_source_type => p_third_party_content_source );
2794
2795 IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
2796 hz_utility_v2pub.debug(
2797 p_message => 'i= '||i
2798 ||' ;l_name_list('||i||')= '||l_name_list(i)
2799 ||' ;l_entity_attr_id= '||l_entity_attr_id
2800 ||' ;third_party_rank = '||third_party_rank,
2801 p_prefix => l_debug_prefix,
2802 p_msg_level => fnd_log.level_statement);
2803 END IF;
2804
2805 IF third_party_rank = -1 THEN
2806 -- Ranking type = Most Recent Record (MRR)
2807 NULL;
2808 ELSIF third_party_rank = 1 THEN
2809 -- this third party is already the highest ranked.
2810 NULL;
2811 ELSIF third_party_rank = 0 THEN
2812 INSERT INTO hz_win_source_exceps (
2813 party_id,
2814 entity_attr_id,
2815 content_source_type,
2816 exception_type,
2817 created_by,
2818 creation_date,
2819 last_update_login,
2820 last_update_date,
2821 last_updated_by
2822 ) --VALUES (
2823 -- Bug 4244112 : populate only for rank attributes
2824 SELECT
2825 p_party_id,
2826 l_entity_attr_id,
2827 G_MISS_CONTENT_SOURCE_TYPE,
2828 'Migration',
2829 hz_utility_v2pub.created_by,
2830 SYSDATE,
2831 hz_utility_v2pub.last_update_login,
2832 SYSDATE,
2833 hz_utility_v2pub.last_updated_by
2834 FROM hz_select_data_sources
2835 WHERE ranking > 1
2836 and content_source_type = 'USER_ENTERED'
2837 and entity_attr_id = l_entity_attr_id;
2838 ELSE
2839 -- this third party is a selected data source but is not the highest rank.
2840 INSERT INTO hz_win_source_exceps (
2841 party_id,
2842 entity_attr_id,
2843 content_source_type,
2844 exception_type,
2845 created_by,
2846 creation_date,
2847 last_update_login,
2848 last_update_date,
2849 last_updated_by
2850 ) VALUES (
2851 p_party_id,
2852 l_entity_attr_id,
2853 p_third_party_content_source,
2854 'Migration',
2855 hz_utility_v2pub.created_by,
2856 SYSDATE,
2857 hz_utility_v2pub.last_update_login,
2858 SYSDATE,
2859 hz_utility_v2pub.last_updated_by );
2860
2861 END IF;
2862 i := l_name_list.NEXT(i);
2863 END LOOP;
2864
2865 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
2866 hz_utility_v2pub.debug(p_message=>'HZ_MIXNM_UTILITY.create_exceptions (-)',
2867 p_prefix=>l_debug_prefix,
2868 p_msg_level=>fnd_log.level_procedure);
2869 END IF;
2870
2871 END create_exceptions;
2872
2873 --------------------------------------------------------------------------
2874 -- public procedures and functions
2875 --------------------------------------------------------------------------
2876
2877 /**
2878 * FUNCTION FindDataSource
2879 *
2880 * DESCRIPTION
2881 * Finds real data source based on content_source_type
2882 * and actual_content_source. This is for backward
2883 * compatibility because even the content_source_type is
2884 * obsolete, we can not assume user will not pass the
2885 * value into this column anymore.
2886 *
2887 * EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
2888 *
2889 * ARGUMENTS
2890 * IN:
2891 * p_content_source_type Value of obsolete column content_source_type
2892 * p_def_content_source_type Default value of obsolete column content_source_type
2893 * p_actual_content_source Value of new column actual_content_source
2894 * p_def_actual_content_source Default value of new column actual_content_source
2895 * OUT:
2896 * x_data_source_from Column name of where real data source is from.
2897 *
2898 * NOTES
2899 *
2900 * MODIFICATION HISTORY
2901 *
2902 * 03-01-2002 Jianying Huang o Created.
2903 * 12-31-2004 Rajib Ranjan Borah o SSM SST Integration and Extension.
2904 * content_source_type is foreign key to orig_system
2905 * in hz_orig_systems_b with sst_flag = 'Y'.
2906 */
2907
2908 FUNCTION FindDataSource (
2909 p_content_source_type IN VARCHAR2,
2910 p_actual_content_source IN VARCHAR2,
2911 p_def_actual_content_source IN VARCHAR2,
2912 x_data_source_from OUT NOCOPY VARCHAR2
2913 ) RETURN VARCHAR2 IS
2914
2915 l_content_source_type VARCHAR2(30);
2916 l_actual_content_source VARCHAR2(30);
2917 l_final_data_source VARCHAR2(30);
2918 l_return_status VARCHAR2(1);
2919
2920 -- SSM SST Integration and Extension
2921 CURSOR c_valid_content_source_type (p_content_source_type IN VARCHAR2) IS
2922 SELECT '1'
2923 FROM hz_orig_systems_b
2924 WHERE orig_system = p_content_source_type
2925 AND sst_flag = 'Y'
2926 AND status = 'A';
2927 l_exists VARCHAR2(1) := 'N';
2928
2929 BEGIN
2930
2931 IF p_content_source_type = FND_API.G_MISS_CHAR THEN
2932 l_content_source_type := G_MISS_CONTENT_SOURCE_TYPE;
2933 ELSE
2934 l_content_source_type := NVL(p_content_source_type,
2935 G_MISS_CONTENT_SOURCE_TYPE);
2936 END IF;
2937
2938 IF p_actual_content_source = FND_API.G_MISS_CHAR THEN
2939 l_actual_content_source := p_def_actual_content_source;
2940 ELSE
2941 l_actual_content_source := NVL(p_actual_content_source,
2942 p_def_actual_content_source);
2943 END IF;
2944
2945 -- by default, we use the value of actual_content_source as
2946 -- the real data source.
2947
2948 l_final_data_source := l_actual_content_source;
2949 x_data_source_from := 'actual_content_source';
2950
2951 -- if user populates content_source_type which is not as the same
2952 -- as actual_content_source and user does not populates the
2953 -- actual_content_source, we returns the value of content_source_type
2954 -- as the real data source.
2955
2956 IF l_content_source_type <> l_actual_content_source AND
2957 l_content_source_type <> G_MISS_CONTENT_SOURCE_TYPE AND
2958 l_actual_content_source = p_def_actual_content_source
2959 THEN
2960 l_final_data_source := l_content_source_type;
2961 x_data_source_from := 'content_source_type';
2962 END IF;
2963
2964 -- real data source much be a valid lookup code of CONTENT_SOURCE_TYPE.
2965
2966 IF l_final_data_source <> p_def_actual_content_source THEN
2967
2968 /* SSM SST Integration and Extension
2969 * Lookup content_source_type is obsoleted.
2970 * Instead, all content_source_types should be valid orig_systems in HZ_ORIG_SYSTEMS_B
2971 * with sst_flag = 'Y'.
2972
2973 HZ_UTILITY_V2PUB.validate_lookup (
2974 p_column => x_data_source_from,
2975 p_lookup_type => 'CONTENT_SOURCE_TYPE',
2976 p_column_value => l_final_data_source,
2977 x_return_status => l_return_status);
2978
2979 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2980 RAISE FND_API.G_EXC_ERROR;
2981 END IF;
2982 */
2983 OPEN c_valid_content_source_type(l_final_data_source);
2984 FETCH c_valid_content_source_type INTO l_exists;
2985 IF c_valid_content_source_type%NOTFOUND THEN
2986 FND_MESSAGE.SET_NAME ('AR','HZ_API_INVALID_CONTENT_SOURCE');
2987 FND_MESSAGE.SET_TOKEN('CONTENT_SOURCE',l_final_data_source);
2988 FND_MSG_PUB.ADD;
2989 END IF;
2990 CLOSE c_valid_content_source_type;
2991
2992 IF l_exists = 'N' THEN
2993 RAISE FND_API.G_EXC_ERROR;
2994 END IF;
2995
2996 END IF;
2997
2998 RETURN l_final_data_source;
2999
3000 END FindDataSource;
3001
3002 /**
3003 * FUNCTION CheckUserCreationPrivilege
3004 *
3005 * DESCRIPTION
3006 * Check if user has privilege to create user entered data when
3007 * after mix-n-match is enabled.
3008 *
3009 * EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
3010 *
3011 * ARGUMENTS
3012 * IN:
3013 * p_entity_name Entity name. Can not be party profiles.
3014 * p_entity_attr_id Entity id. Entity id is used only for
3015 * performance consideration. It can speed
3016 * the query if it is passed.
3017 * p_mixnmatch_enabled 'Y'/'N' flag to indicate if mix-n-match
3018 * if enabled for this entity. You can get
3019 * the info. via HZ_MIXNM_UTILITY.
3020 * p_actual_content_source Actual content source.
3021 * OUT:
3022 * x_return_status Return FND_API.G_RET_STS_ERROR if the
3023 * user under this site/application/
3024 * responsibility is not allowed to create
3025 * user-entered data for this entity.
3026 *
3027 * NOTES
3028 * The procedure can only be called for other entities like HZ_CONTACT_POINTS,
3029 * HZ_LOCATIONS etc. It can not be called on party profiles HZ_ORGANIZATION_PROFILES,
3030 * HZ_PERSON_PROFILES.
3031 *
3032 * MODIFICATION HISTORY
3033 *
3034 * 03-01-2002 Jianying Huang o Created.
3035 * 12-31-2004 Rajib Ranjan Borah o SSM SST Integration and Extension.
3036 * p_mixnmatch_enabled will not be considered in
3037 * the IF check as mixnmatch concept is obsoleted
3038 * for other entities.
3039 */
3040
3041 PROCEDURE CheckUserCreationPrivilege (
3042 p_entity_name IN VARCHAR2,
3043 p_entity_attr_id IN OUT NOCOPY NUMBER,
3044 p_mixnmatch_enabled IN VARCHAR2,
3045 p_actual_content_source IN VARCHAR2,
3046 x_return_status IN OUT NOCOPY VARCHAR2
3047 ) IS
3048 BEGIN
3049
3050 IF/* NVL(p_mixnmatch_enabled, 'N') = 'Y' AND*/
3051 p_actual_content_source = G_MISS_CONTENT_SOURCE_TYPE AND
3052 isEntityUserCreatable(p_entity_name, p_entity_attr_id) = 'N'
3053 THEN
3054 /* new message */
3055 FND_MESSAGE.SET_NAME('AR', 'HZ_DISALLOW_USER_CREATION');
3056 FND_MESSAGE.SET_TOKEN('ENTITY',
3057 hz_utility_v2pub.Get_LookupMeaning(
3058 'AR_LOOKUPS','ENTITY_NAME', p_entity_name));
3059 FND_MSG_PUB.ADD;
3060 x_return_status := FND_API.G_RET_STS_ERROR;
3061 END IF;
3062
3063 END CheckUserCreationPrivilege;
3064
3065 /**
3066 * FUNCTION CheckUserUpdatePrivilege
3067 *
3068 * DESCRIPTION
3069 * Check if user has privilege to update a third party record.
3070 *
3071 * EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
3072 *
3073 * ARGUMENTS
3074 * IN:
3075 * p_actual_content_source Actual content source.
3076 * OUT:
3077 * x_return_status Return FND_API.G_RET_STS_ERROR if the
3078 * user under this site/application/
3079 * responsibility is not allowed to create
3080 * user-entered data for this entity.
3081 *
3082 * NOTES
3083 * The procedure can only be called for other entities like HZ_CONTACT_POINTS,
3084 * HZ_LOCATIONS etc.
3085 *
3086 * MODIFICATION HISTORY
3087 *
3088 * 03-01-2002 Jianying Huang o Created.
3089 * 12-31-2004 Rajib Ranjan Borah o SSM SST Integration and Extension.
3090 * Added parameters p_entity_name and p_new_actual_content_source.
3091 * User overwrite rules will be checked now
3092 * instead of profile option 'HZ_UPDATE_THIRD_PARTY_DATA'.
3093 * o If this is a purchased source system throw error straightaway.
3094 * o Call cacheSetupForOtherEntities first to
3095 * load the other entities related setup.
3096 */
3097
3098 PROCEDURE CheckUserUpdatePrivilege (
3099 p_actual_content_source IN VARCHAR2,
3100 p_new_actual_content_source IN VARCHAR2,
3101 p_entity_name IN VARCHAR2,
3102 x_return_status IN OUT NOCOPY VARCHAR2
3103 ) IS
3104 l_entity_attr_id NUMBER;
3105 l_entity_name VARCHAR2(30);
3106 BEGIN
3107 -- Bug 4226199 : initialize x_return_status
3108 --x_return_status := FND_API.G_RET_STS_SUCCESS;
3109
3110 /* SSM SST Integration and Extension
3111 *
3112 IF p_actual_content_source <> G_MISS_CONTENT_SOURCE_TYPE AND
3113 NVL(FND_PROFILE.value('HZ_UPDATE_THIRD_PARTY_DATA'), 'N') = 'N'
3114 THEN
3115 */
3116 /* new message */
3117 /* FND_MESSAGE.SET_NAME('AR', 'HZ_NOTALLOW_UPDATE_THIRD_PARTY');
3118 FND_MSG_PUB.ADD;
3119 RAISE FND_API.G_EXC_ERROR;
3120 END IF;
3121 */
3122
3123 -- SSM SST Integration and Extension
3124 -- If the actual_content_sources match or if the existing record is 'USER_ENTERED' then return.
3125 IF (NVL(p_new_actual_content_source,G_MISS_CONTENT_SOURCE_TYPE) = p_actual_content_source OR
3126 p_actual_content_source = G_MISS_CONTENT_SOURCE_TYPE)
3127 THEN
3128 RETURN;
3129 END IF;
3130
3131 IF HZ_UTILITY_V2PUB.is_purchased_content_source(p_actual_content_source) = 'N' THEN
3132 -- Spoke source system can be updated by other source systems.
3133 -- However users can update spoke source systems only if rules allow it to.
3134 IF (p_new_actual_content_source <> G_MISS_CONTENT_SOURCE_TYPE
3135 AND p_new_actual_content_source IS NOT NULL)
3136 THEN
3137 RETURN;
3138 END IF;
3139
3140 IF p_entity_name = 'HZ_FINANCIAL_NUMBERS' THEN
3141 l_entity_name := 'HZ_FINANCIAL_REPORTS';
3142 ELSIF p_entity_name = 'HZ_PARTY_SITES' THEN
3143 l_entity_name := 'HZ_LOCATIONS';
3144 ELSE
3145 l_entity_name := p_entity_name;
3146 END IF;
3147
3148 cacheSetupForOtherEntities(TRUE);
3149
3150 l_entity_attr_id := G_ENTITY_ID( getIndex( p_list => G_ENTITY_NAME,
3151 p_name => l_entity_name));
3152
3153 IF G_OTHER_ENT_USER_OVERWRITE.EXISTS(l_entity_attr_id) THEN
3154 IF INSTRB(G_OTHER_ENT_USER_OVERWRITE(l_entity_attr_id),
3155 ','||getIndex( p_list => G_ORIG_SYSTEM_LIST,
3156 p_name => p_actual_content_source)||',',
3157 1,
3158 1
3159 ) <> 0
3160 THEN
3161 RETURN;
3162 END IF;
3163 END IF;
3164 END IF;
3165
3166 FND_MESSAGE.SET_NAME('AR', 'HZ_NOTALLOW_UPDATE_THIRD_PARTY');
3167 FND_MSG_PUB.ADD;
3168 x_return_status := FND_API.G_RET_STS_ERROR;
3169 RAISE FND_API.G_EXC_ERROR;
3170
3171 END CheckUserUpdatePrivilege;
3172
3173 /**
3174 * FUNCTION isDataSourceSelected
3175 *
3176 * DESCRIPTION
3177 * Internal use only!!!
3178 * Return 'Y' if the data source has been selected.
3179 *
3180 * EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
3181 *
3182 * ARGUMENTS
3183 * IN:
3184 * p_selected_datasources A list of selected data sources. You can
3185 * get it via HZ_MIXNM_UTILITY.
3186 * p_actual_content_source Actual content source.
3187 *
3188 * NOTES
3189 *
3190 * *** SSM SST Integration and Extension ***
3191 * This function will only be called for profiles as for other entities, the
3192 * concept of selected/ deselected data sources is obsoleted.
3193 * *** ***
3194 *
3195 * MODIFICATION HISTORY
3196 *
3197 * 03-01-2002 Jianying Huang o Created.
3198 * 07-07-2005 Dhaval Mehta o Bug 4376604. Changed the signature to p_entity_name
3199 * instead of p_selected_datasources
3200 */
3201
3202 FUNCTION isDataSourceSelected(
3203 -- p_selected_datasources IN VARCHAR2,
3204 p_entity_name IN VARCHAR2,
3205 p_actual_content_source IN VARCHAR2
3206 ) RETURN VARCHAR2 IS
3207
3208 l_ret VARCHAR2(1) := 'N';
3209 l_actual_content_source VARCHAR2(30) := p_actual_content_source;
3210 l_entity_id NUMBER := 0;
3211 BEGIN
3212
3213 IF p_actual_content_source = G_MISS_ACTUAL_CONTENT_SOURCE THEN
3214 l_actual_content_source := G_MISS_CONTENT_SOURCE_TYPE;
3215 END IF;
3216
3217 IF p_entity_name = 'PERSON' THEN
3218 l_entity_id := G_PERSON_PROFILE_ID;
3219 ELSIF p_entity_name = 'ORGANIZATION' THEN
3220 l_entity_id := G_ORGANIZATION_PROFILE_ID;
3221 END IF;
3222
3223 /* IF INSTRB(p_selected_datasources, ''''||l_actual_content_source||'''') > 0 THEN
3224 l_ret := 'Y';
3225 END IF;*/
3226 IF INSTRB(G_ENTITY_DATA_SOURCE(l_entity_id), ','
3227 ||getIndex(p_list => G_ORIG_SYSTEM_LIST,
3228 p_name => l_actual_content_source)
3229 ||',') > 0 THEN
3230 l_ret := 'Y';
3231 END IF;
3232 RETURN l_ret;
3233
3234 END isDataSourceSelected;
3235
3236 /**
3237 * FUNCTION ValidateContentSource
3238 *
3239 * DESCRIPTION
3240 * Validate content source type.
3241 *
3242 * EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
3243 *
3244 * ARGUMENTS
3245 * IN:
3246 * p_api_version API version. 'V1' is for V1 API. 'V2' is for V2 API.
3247 * p_create_update_flag Create or update flag. 'C' is for create. 'U' is for
3248 * update.
3249 * p_check_update_privilege Check if user has privilege to update third party data.
3250 * p_content_source_type Content source type.
3251 * p_old_content_source_type Old content source type.
3252 * p_actual_content_source Actual content source.
3253 * p_old_actual_content_source Old actual content source.
3254 * IN/OUT:
3255 * x_return_status Return FND_API.G_RET_STS_ERROR if the
3256 * user under this site/application/
3257 * responsibility is not allowed to create
3258 * user-entered data for this entity.
3259 *
3260 * NOTES
3261 * The procedure can only be called for other entities like HZ_CONTACT_POINTS,
3262 * HZ_LOCATIONS etc. It can not be called on party profiles HZ_ORGANIZATION_PROFILES,
3263 * HZ_PERSON_PROFILES.
3264 *
3265 * MODIFICATION HISTORY
3266 *
3267 * 03-01-2002 Jianying Huang o Created.
3268 * 12-31-2004 Rajib Ranjan Borah o SSM SST Integration and Extension.
3269 * Added parameter p_entity_name as user update privileges
3270 * vary for different entities with different content sources.
3271 * o validate_nonupdateable for actual_content_source is commented out.
3272 * o Call CheckUserUpdatePrivilege only if user_entered is trying to
3273 * update and not if any other content source is trying to.
3274 */
3275
3276 PROCEDURE ValidateContentSource (
3277 p_api_version IN VARCHAR2,
3278 p_create_update_flag IN VARCHAR2,
3279 p_check_update_privilege IN VARCHAR2,
3280 p_content_source_type IN VARCHAR2,
3281 p_old_content_source_type IN VARCHAR2,
3282 p_actual_content_source IN VARCHAR2,
3283 p_old_actual_content_source IN VARCHAR2,
3284 p_entity_name IN VARCHAR2,
3285 x_return_status IN OUT NOCOPY VARCHAR2
3286 ) IS
3287
3288 l_data_source_from VARCHAR2(30);
3289 l_content_source_type VARCHAR2(30) := p_content_source_type;
3290 l_actual_content_source VARCHAR2(30) := p_actual_content_source;
3291 l_debug_prefix VARCHAR2(30) := '';
3292
3293 BEGIN
3294
3295 --enable_debug;
3296
3297 -- Debug info.
3298 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
3299 hz_utility_v2pub.debug(p_message=>'ValidateContentSource (+)',
3300 p_prefix=>l_debug_prefix,
3301 p_msg_level=>fnd_log.level_procedure);
3302 END IF;
3303 IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
3304 hz_utility_v2pub.debug(p_message=>'p_api_version = '||p_api_version||', '||
3305 'p_create_update_flag = '||p_create_update_flag||', '||
3306 'p_content_source_type = '||p_content_source_type||','||
3307 'p_old_content_source_type = '||p_old_content_source_type||','||
3308 'p_actual_content_source = '||p_actual_content_source||','||
3309 'p_old_actual_content_source = '||p_old_actual_content_source||','||
3310 'x_return_status = '||x_return_status,
3311 p_prefix =>l_debug_prefix,
3312 p_msg_level=>fnd_log.level_statement);
3313 END IF;
3314
3315
3316 -- check if the user has privilege to update third party data
3317 -- we can trust old_actual_content_source here because the
3318 -- column is not updatable.
3319
3320 IF p_create_update_flag = 'U' AND
3321 p_check_update_privilege = 'Y' AND
3322 p_old_actual_content_source <> G_MISS_CONTENT_SOURCE_TYPE
3323 THEN
3324 CheckUserUpdatePrivilege (
3325 p_actual_content_source => p_old_actual_content_source,
3326 p_new_actual_content_source => p_actual_content_source,
3327 p_entity_name => p_entity_name,
3328 x_return_status => x_return_status );
3329
3330 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
3331 RAISE FND_API.G_EXC_ERROR;
3332 END IF;
3333 END IF;
3334
3335 -- obsolete content_source_type. Raise error in development site
3336 -- if user tries to populate value into this column.
3337
3338 IF NVL(FND_PROFILE.value('HZ_API_ERR_ON_OBSOLETE_COLUMN'), 'N') = 'Y'
3339 THEN
3340 HZ_UTILITY_V2PUB.Check_ObsoleteColumn (
3341 p_api_version => p_api_version,
3342 p_create_update_flag => p_create_update_flag,
3343 p_column => 'content_source_type',
3344 p_column_value => p_content_source_type,
3345 p_default_value => G_MISS_CONTENT_SOURCE_TYPE,
3346 p_old_column_value => p_old_content_source_type,
3347 x_return_status => x_return_status);
3348
3349 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
3350 RAISE FND_API.G_EXC_ERROR;
3351 END IF;
3352 END IF;
3353
3354 -- actual_content_source is mandatory. Since it has default value, we
3355 -- do not need to check mandatory in validation phase.
3356
3357 -- content_source_type is non-updatable. Since it is obsolete,
3358 -- we do not need to check it. Instead, we pass NULL to
3359 -- table handler to make sure it will not be updated.
3360
3361 -- actual_content_source is non-updatable.
3362
3363 IF p_create_update_flag = 'U' THEN
3364
3365 -- Find real data source via comparing content_source_type
3366 -- and actual_content_source.
3367
3368 IF (p_api_version = 'V1' AND
3369 l_actual_content_source = FND_API.G_MISS_CHAR) OR
3370 (p_api_version = 'V2' AND
3371 l_actual_content_source IS NULL)
3372 THEN
3373 l_actual_content_source :=
3374 FindDataSource (
3375 p_content_source_type => l_content_source_type,
3376 p_actual_content_source => NVL(l_actual_content_source,FND_API.G_MISS_CHAR),
3377 p_def_actual_content_source => FND_API.G_MISS_CHAR,
3378 x_data_source_from => l_data_source_from );
3379
3380 -- actual_content_source and content_source_type can not be 'SST'.
3381 -- actual_content_source is lookup code in lookup type CONTENT_SOURCE_TYPE.
3382 -- Since actual_content_source and content_source_type are non-updatable,
3383 -- we only need to do checking in creation mode.
3384
3385 IF l_actual_content_source = G_MISS_ACTUAL_CONTENT_SOURCE THEN
3386 /* new message */
3387 FND_MESSAGE.SET_NAME('AR', 'HZ_SST_INVALID_SOURCE');
3388 FND_MESSAGE.SET_TOKEN('COLUMN', l_data_source_from);
3389 FND_MSG_PUB.ADD;
3390 x_return_status := FND_API.G_RET_STS_ERROR;
3391 END IF;
3392
3393 IF l_data_source_from = 'actual_content_source' THEN
3394 l_actual_content_source := p_actual_content_source;
3395 END IF;
3396
3397 -- Debug info.
3398 IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
3399 hz_utility_v2pub.debug(p_message=>'l_data_source_from = '||l_data_source_from||', '||
3400 'l_actual_content_source = '||l_actual_content_source,
3401 p_prefix =>l_debug_prefix,
3402 p_msg_level=>fnd_log.level_statement);
3403 END IF;
3404
3405 END IF;
3406
3407 -- Debug info.
3408 IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
3409 hz_utility_v2pub.debug(p_message=>'l_actual_content_source = '||l_actual_content_source||', '||
3410 'p_old_actual_content_source = '||p_old_actual_content_source||', '||
3411 'l_data_source_from = '||l_data_source_from,
3412 p_prefix =>l_debug_prefix,
3413 p_msg_level=>fnd_log.level_statement);
3414 END IF;
3415
3416
3417 IF (p_api_version = 'V1' AND
3418 l_actual_content_source IS NULL) OR
3419 (p_api_version = 'V2' AND
3420 l_actual_content_source = FND_API.G_MISS_CHAR)
3421 THEN
3422 FND_MESSAGE.SET_NAME( 'AR', 'HZ_API_NONUPDATEABLE_TO_NULL' );
3423 FND_MESSAGE.SET_TOKEN( 'COLUMN', 'actual_content_source' );
3424 FND_MSG_PUB.ADD;
3425 x_return_status := FND_API.G_RET_STS_ERROR;
3426
3427 -- SSM SST Integration and Extension
3428 -- Actual content source is updateable for SSM enabled entities like 'HZ_CONTACT_POINTS'
3429 -- ,'HZ_LOCATIONS' and 'HZ_PARTY_SITES'.
3430 -- For other entities, use the new value of actual content source to check privilege.
3431 -- The table handlers will not update the actual_content_source for such entities.
3432
3433 /* ELSIF (p_api_version = 'V1' AND
3434 l_actual_content_source IS NOT NULL AND
3435 l_actual_content_source <> FND_API.G_MISS_CHAR) OR
3436 (p_api_version = 'V2' AND
3437 l_actual_content_source IS NOT NULL)
3438 THEN
3439 HZ_UTILITY_V2PUB.validate_nonupdateable (
3440 p_column => 'actual_content_source',
3441 p_column_value => l_actual_content_source,
3442 p_old_column_value => p_old_actual_content_source,
3443 x_return_status => x_return_status);
3444 */
3445 END IF;
3446 END IF;
3447
3448 -- Debug info.
3449 IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
3450 hz_utility_v2pub.debug(p_message=>'x_return_status = '||x_return_status,
3451 p_prefix =>l_debug_prefix,
3452 p_msg_level=>fnd_log.level_statement);
3453 END IF;
3454 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
3455 hz_utility_v2pub.debug(p_message=>'ValidateContentSource (-)',
3456 p_prefix=>l_debug_prefix,
3457 p_msg_level=>fnd_log.level_procedure);
3458 END IF;
3459
3460
3461 --disable_debug;
3462
3463 END ValidateContentSource;
3464
3465 /**
3466 * FUNCTION AssignDataSourceDuringCreation
3467 *
3468 * DESCRIPTION
3469 * Assign data source during entity creation. Check validity of the data
3470 * source and check if user has privilege to create user-entered data.
3471 *
3472 * EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
3473 *
3474 * ARGUMENTS
3475 * IN:
3476 * p_entity_name Entity name. Can not be party profiles.
3477 * p_entity_attr_id Entity id. Entity id is used only for
3478 * performance consideration. It can speed
3479 * the query if it is passed.
3480 * p_mixnmatch_enabled 'Y'/'N' flag to indicate if mix-n-match
3481 * if enabled for this entity. You can get
3482 * the info. via HZ_MIXNM_UTILITY.
3483 * p_selected_datasources A list of selected data sources. You can
3484 * get it via HZ_MIXNM_UTILITY.
3485 * p_content_source_type Content source type.
3486 * p_actual_content_source Actual content source.
3487 * OUT:
3488 * x_is_datasource_selected Return 'Y'/'N' to indicate if the data
3489 * source is visible.
3490 * x_return_status Return FND_API.G_RET_STS_ERROR if any
3491 * validation fails.
3492 *
3493 * NOTES
3494 * The procedure can only be called for other entities like HZ_CONTACT_POINTS,
3495 * HZ_LOCATIONS etc. It can not be called on party profiles HZ_ORGANIZATION_PROFILES,
3496 * HZ_PERSON_PROFILES.
3497 *
3498 * MODIFICATION HISTORY
3499 *
3500 * 03-01-2002 Jianying Huang o Created.
3501 * 01-03-2005 Rajib Ranjan Borah o SSM SST Integration and Extension.
3502 * x_is_datasource_selected will be set to 'Y'
3503 * always.
3504 * Actually parameters p_mixnmatch_enabled,
3505 * p_selected_data_sources and x_is_datasource_selected
3506 * are redundant and are retained for back-ward compatibility.
3507 */
3508
3509 PROCEDURE AssignDataSourceDuringCreation (
3510 p_entity_name IN VARCHAR2,
3511 p_entity_attr_id IN OUT NOCOPY NUMBER,
3512 p_mixnmatch_enabled IN VARCHAR2,
3513 p_selected_datasources IN VARCHAR2,
3514 p_content_source_type IN OUT NOCOPY VARCHAR2,
3515 p_actual_content_source IN OUT NOCOPY VARCHAR2,
3516 x_is_datasource_selected OUT NOCOPY VARCHAR2,
3517 x_return_status IN OUT NOCOPY VARCHAR2,
3518 p_api_version IN VARCHAR2
3519 ) IS
3520
3521 l_data_source_from VARCHAR2(30);
3522 l_debug_prefix VARCHAR2(30) := '';
3523
3524 BEGIN
3525
3526 -- obsolete content_source_type. Raise error in development site
3527 -- if user tries to populate value into this column.
3528
3529 IF NVL(FND_PROFILE.value('HZ_API_ERR_ON_OBSOLETE_COLUMN'), 'N') = 'Y'
3530 THEN
3531 HZ_UTILITY_V2PUB.Check_ObsoleteColumn (
3532 p_api_version => p_api_version,
3533 p_create_update_flag => 'C',
3534 p_column => 'content_source_type',
3535 p_column_value => p_content_source_type,
3536 p_default_value => G_MISS_CONTENT_SOURCE_TYPE,
3537 p_old_column_value => null,
3538 x_return_status => x_return_status);
3539
3540 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
3541 RAISE FND_API.G_EXC_ERROR;
3542 END IF;
3543
3544 -- Debug info.
3545 IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
3546 hz_utility_v2pub.debug(p_message=>'p_api_version = '||p_api_version||', '||
3547 'p_content_source_type = '||p_content_source_type||','||
3548 'x_return_status = '||x_return_status,
3549 p_prefix =>l_debug_prefix,
3550 p_msg_level=>fnd_log.level_statement);
3551 END IF;
3552 END IF;
3553
3554 -- Find real data source via comparing content_source_type
3555 -- and actual_content_source.
3556
3557 p_actual_content_source :=
3558 FindDataSource (
3559 p_content_source_type => p_content_source_type,
3560 p_actual_content_source => NVL(p_actual_content_source,FND_API.G_MISS_CHAR),
3561 p_def_actual_content_source => FND_API.G_MISS_CHAR,
3562 x_data_source_from => l_data_source_from );
3563
3564 IF p_actual_content_source = FND_API.G_MISS_CHAR THEN
3565 p_actual_content_source := G_MISS_CONTENT_SOURCE_TYPE;
3566 END IF;
3567
3568 -- actual_content_source and content_source_type can not be 'SST'.
3569 -- actual_content_source is lookup code in lookup type CONTENT_SOURCE_TYPE.
3570 -- Since actual_content_source and content_source_type are non-updatable,
3571 -- we only need to do checking in creation mode.
3572
3573 IF p_actual_content_source = G_MISS_ACTUAL_CONTENT_SOURCE THEN
3574 /* new message */
3575 FND_MESSAGE.SET_NAME('AR', 'HZ_SST_INVALID_SOURCE');
3576 FND_MESSAGE.SET_TOKEN('COLUMN', l_data_source_from);
3577 FND_MSG_PUB.ADD;
3578 x_return_status := FND_API.G_RET_STS_ERROR;
3579 END IF;
3580
3581 -- Check if user has privilege to create user entered data.
3582
3583 IF/* NVL(p_mixnmatch_enabled, 'N') = 'Y' AND*/
3584 p_actual_content_source = G_MISS_CONTENT_SOURCE_TYPE
3585 THEN
3586 CheckUserCreationPrivilege (
3587 p_entity_name => p_entity_name,
3588 p_entity_attr_id => p_entity_attr_id,
3589 p_mixnmatch_enabled => p_mixnmatch_enabled,
3590 p_actual_content_source => p_actual_content_source,
3591 x_return_status => x_return_status );
3592 END IF;
3593
3594 -- reset the content_source_type to 'USER_ENTERED' to take care
3595 -- of extra where clause 'content_source_type = 'USER_ENTERED''
3596 -- in the existing code.
3597
3598 -- SSM SST Integration and Extension
3599 -- The concept of selected/de-selected data sources is obsoleted for other entities.
3600 -- Therefore return 'Y' always.
3601 x_is_datasource_selected :='Y';
3602 /* isDataSourceSelected(
3603 p_selected_datasources => p_selected_datasources,
3604 p_actual_content_source => p_actual_content_source );
3605 */
3606
3607 p_content_source_type := G_MISS_CONTENT_SOURCE_TYPE;
3608
3609 END AssignDataSourceDuringCreation;
3610
3611 /**
3612 * FUNCTION isMixNMatchEnabled
3613 *
3614 * DESCRIPTION
3615 * Is mix-n-match is enabled in the given entity.
3616 *
3617 * EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
3618 *
3619 * ARGUMENTS
3620 * IN:
3621 * p_entity_name Entity name.
3622 * p_called_from_policy_function A flag to indicate if the procedure is called
3623 * from policy function.
3624 * IN/OUT:
3625 * p_entity_attr_id Entity Id.
3626 *
3627 * NOTES
3628 *
3629 * MODIFICATION HISTORY
3630 *
3631 * 05-01-2002 Jianying Huang o Created
3632 * 01-03-2005 Rajib Ranjan Borah o SSM SST Integration and Extension.
3633 * This should not be called for other entities.
3634 * Nevertheless return 'Y' always for backward compatibility.
3635 */
3636
3637 FUNCTION isMixNMatchEnabled (
3638 p_entity_name IN VARCHAR2,
3639 p_entity_attr_id IN OUT NOCOPY NUMBER,
3640 p_called_from_policy_function IN VARCHAR2
3641 ) RETURN VARCHAR2 IS
3642
3643 l_entity_name VARCHAR2(30) := p_entity_name;
3644 l_return VARCHAR2(1);
3645 l_src_selected VARCHAR2(1) := 'N';
3646
3647 BEGIN
3648 -- SSM SST Integration and Extension
3649 IF p_entity_name NOT IN ('HZ_ORGANIZATION_PROFILES','HZ_PERSON_PROFILES')
3650 THEN
3651 RETURN 'Y';
3652 END IF;
3653
3654 -- load all entities related setups and cache them.
3655 cacheSetupForOtherEntities;
3656
3657 IF p_entity_attr_id IS NULL OR
3658 p_entity_attr_id = 0
3659 THEN
3660 IF p_entity_name = 'HZ_ORGANIZATION_PROFILES' THEN
3661 p_entity_attr_id := G_ORGANIZATION_PROFILE_ID;
3662 ELSIF p_entity_name = 'HZ_PERSON_PROFILES' THEN
3663 p_entity_attr_id := G_PERSON_PROFILE_ID;
3664 /*ELSE
3665 IF p_entity_name = 'HZ_PARTY_SITES' THEN
3666 l_entity_name := 'HZ_LOCATIONS';
3667 ELSIF p_entity_name = 'HZ_FINANCIAL_NUMBERS' THEN
3668 l_entity_name := 'HZ_FINANCIAL_REPORTS';
3669 END IF;
3670 p_entity_attr_id := getEntityAttrId(l_entity_name);
3671
3672 */
3673 END IF;
3674 END IF;
3675
3676 IF p_entity_attr_id = 0 THEN
3677 RETURN 'N';
3678 END IF;
3679
3680 -- first find out if the value is cached. If it is not,
3681 -- check if the SST policy exists on this entity.
3682
3683 IF G_MIXNM_ENABLED_FLAG.EXISTS(p_entity_attr_id) AND
3684 G_MIXNM_ENABLED_FLAG(p_entity_attr_id) IS NOT NULL
3685 THEN
3686 -- return cached value.
3687 RETURN G_MIXNM_ENABLED_FLAG(p_entity_attr_id);
3688 ELSE
3689
3690 IF p_entity_name = 'HZ_ORGANIZATION_PROFILES' THEN
3691 l_return := NVL(fnd_profile.value('HZ_ORG_PROF_MIXNMATCH_ENABLED'), 'N');
3692 ELSIF p_entity_name = 'HZ_PERSON_PROFILES' THEN
3693 l_return := NVL(fnd_profile.value('HZ_PER_PROF_MIXNMATCH_ENABLED'), 'N');
3694 ELSE
3695 IF p_called_from_policy_function = 'Y' THEN
3696 l_return := 'Y';
3697 ELSE
3698 -- get AR schema name.
3699 IF G_AR_SCHEMA_NAME IS NULL THEN
3700 G_AR_SCHEMA_NAME := hz_utility_v2pub.Get_SchemaName('AR');
3701 END IF;
3702
3703 -- check if policy exists.
3704 -- bug fix 2731008
3705 BEGIN
3706 select 'Y' into l_src_selected
3707 from hz_select_data_sources d, hz_entity_attributes e
3708 where e.entity_attr_id = d.entity_attr_id
3709 and UPPER(e.entity_name) = UPPER(p_entity_name)
3710 and d.content_source_type <> G_MISS_CONTENT_SOURCE_TYPE
3711 and d.ranking > 0
3712 and rownum =1;
3713 EXCEPTION
3714 WHEN NO_DATA_FOUND THEN
3715 null;
3716 END;
3717
3718 l_return := l_src_selected;
3719
3720 /*
3721 IF fnd_access_control_util.Policy_Exists (
3722 G_AR_SCHEMA_NAME, p_entity_name, 'content_source_type_sec' ) = 'FALSE'
3723 THEN
3724 l_return := 'N';
3725 ELSE
3726 l_return := 'Y';
3727 END IF;
3728 */
3729 END IF;
3730 END IF;
3731
3732 -- cache the value.
3733 G_MIXNM_ENABLED_FLAG(p_entity_attr_id) := l_return;
3734 END IF;
3735
3736 RETURN l_return;
3737
3738 END isMixNMatchEnabled;
3739
3740 /**
3741 * PROCEDURE updateSSTProfile
3742 *
3743 * DESCRIPTION
3744 * Return new SST record to create / update SST profile.
3745 *
3746 * EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
3747 *
3748 * ARGUMENTS
3749 * IN:
3750 * p_create_update_flag Create update flag.
3751 * p_create_update_sst_flag Create update SST profile flag.
3752 * p_raise_error_flag Raise error flag.
3753 * p_party_type Party type.
3754 * p_party_id Party Id.
3755 * p_new_person_rec New person record.
3756 * p_old_person_rec New person record.
3757 * p_sst_person_rec Current SST person record.
3758 * p_new_organization_rec New organization record.
3759 * p_old_organization_rec New organization record.
3760 * p_sst_organization_rec Current SST organization record.
3761 * p_data_source_type Comming data source type.
3762 * IN/OUT:
3763 * p_new_sst_person_rec New SST person record.
3764 * p_new_sst_organization_rec New SST organization record.
3765 * x_return_status Return status.
3766 *
3767 * NOTES
3768 * The procedure should only be called if the mix-n-match is enable for
3769 * the entity.
3770 *
3771 * MODIFICATION HISTORY
3772 *
3773 * 05-01-2002 Jianying Huang o Created
3774 */
3775
3776 PROCEDURE updateSSTProfile (
3777 p_create_update_flag IN VARCHAR2,
3778 p_create_update_sst_flag IN VARCHAR2,
3779 p_raise_error_flag IN VARCHAR2,
3780 p_party_type IN VARCHAR2,
3781 p_party_id IN NUMBER,
3782 p_new_person_rec IN HZ_PARTY_V2PUB.PERSON_REC_TYPE,
3783 p_old_person_rec IN HZ_PARTY_V2PUB.PERSON_REC_TYPE,
3784 p_sst_person_rec IN HZ_PARTY_V2PUB.PERSON_REC_TYPE,
3785 p_new_sst_person_rec IN OUT NOCOPY HZ_PARTY_V2PUB.PERSON_REC_TYPE,
3786 p_new_organization_rec IN HZ_PARTY_V2PUB.ORGANIZATION_REC_TYPE,
3787 p_old_organization_rec IN HZ_PARTY_V2PUB.ORGANIZATION_REC_TYPE,
3788 p_sst_organization_rec IN HZ_PARTY_V2PUB.ORGANIZATION_REC_TYPE,
3789 p_new_sst_organization_rec IN OUT NOCOPY HZ_PARTY_V2PUB.ORGANIZATION_REC_TYPE,
3790 p_data_source_type IN VARCHAR2,
3791 x_return_status IN OUT NOCOPY VARCHAR2
3792 ) IS
3793 BEGIN
3794
3795 IF p_party_type = 'PERSON' THEN
3796 updatePerSSTProfile (
3797 p_create_update_flag => p_create_update_flag,
3798 p_create_update_sst_flag => p_create_update_sst_flag,
3799 p_raise_error_flag => p_raise_error_flag,
3800 p_party_id => p_party_id,
3801 p_new_person_rec => p_new_person_rec,
3802 p_old_person_rec => p_old_person_rec,
3803 p_sst_person_rec => p_sst_person_rec,
3804 p_new_sst_person_rec => p_new_sst_person_rec,
3805 p_data_source_type => p_data_source_type,
3806 x_return_status => x_return_status );
3807 ELSIF p_party_type = 'ORGANIZATION' THEN
3808 updateOrgSSTProfile (
3809 p_create_update_flag => p_create_update_flag,
3810 p_create_update_sst_flag => p_create_update_sst_flag,
3811 p_raise_error_flag => p_raise_error_flag,
3812 p_party_id => p_party_id,
3813 p_new_organization_rec => p_new_organization_rec,
3814 p_old_organization_rec => p_old_organization_rec,
3815 p_sst_organization_rec => p_sst_organization_rec,
3816 p_new_sst_organization_rec => p_new_sst_organization_rec,
3817 p_data_source_type => p_data_source_type,
3818 x_return_status => x_return_status );
3819 END IF;
3820
3821 END updateSSTProfile;
3822
3823 /**
3824 * PROCEDURE updateSSTPerProfile
3825 *
3826 * DESCRIPTION
3827 * Return new SST record to create / update person SST profile.
3828 *
3829 * EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
3830 *
3831 * ARGUMENTS
3832 * IN:
3833 * p_create_update_flag Create update flag.
3834 * p_create_update_sst_flag Create update SST profile flag.
3835 * p_raise_error_flag Raise error flag.
3836 * p_party_type Party type.
3837 * p_party_id Party Id.
3838 * p_new_person_rec New person record.
3839 * p_old_person_rec New person record.
3840 * p_sst_person_rec Current SST person record.
3841 * p_data_source_type Comming data source type.
3842 * IN/OUT:
3843 * p_new_sst_person_rec New SST person record.
3844 * x_return_status Return status.
3845 *
3846 * NOTES
3847 * The procedure should only be called if the mix-n-match is enable for
3848 * the entity.
3849 *
3850 * MODIFICATION HISTORY
3851 *
3852 * 05-01-2002 Jianying Huang o Created
3853 */
3854
3855 PROCEDURE updatePerSSTProfile (
3856 p_create_update_flag IN VARCHAR2,
3857 p_create_update_sst_flag IN VARCHAR2,
3858 p_raise_error_flag IN VARCHAR2,
3859 p_party_id IN NUMBER,
3860 p_new_person_rec IN HZ_PARTY_V2PUB.PERSON_REC_TYPE,
3861 p_old_person_rec IN HZ_PARTY_V2PUB.PERSON_REC_TYPE,
3862 p_sst_person_rec IN HZ_PARTY_V2PUB.PERSON_REC_TYPE,
3863 p_new_sst_person_rec IN OUT NOCOPY HZ_PARTY_V2PUB.PERSON_REC_TYPE,
3864 p_data_source_type IN VARCHAR2,
3865 x_return_status IN OUT NOCOPY VARCHAR2
3866 ) IS
3867
3868 l_new_value_is_null_list INDEXVARCHAR1List;
3869 l_sst_value_is_null_list INDEXVARCHAR1List;
3870 l_sst_value_is_not_null_list INDEXVARCHAR1List;
3871 l_updatable_flag_list INDEXVARCHAR1List;
3872 l_exception_type_list INDEXVARCHAR30List;
3873 l_name_list INDEXVARCHAR30List;
3874 l_data_source_list INDEXVARCHAR30List;
3875 l_debug_prefix VARCHAR2(30) := '';
3876
3877 BEGIN
3878
3879 -- Check if API is called in debug mode. If yes, enable debug.
3880 --enable_debug;
3881
3882 -- Debug info.
3883 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
3884 hz_utility_v2pub.debug(p_message=>'updatePerSSTProfile (+)',
3885 p_prefix=>l_debug_prefix,
3886 p_msg_level=>fnd_log.level_procedure);
3887 END IF;
3888
3889 -- find out those attributes we need to check.
3890
3891 hz_mixnm_api_dynamic_pkg.initAttributeList(
3892 p_create_update_flag => p_create_update_flag,
3893 p_new_rec => p_new_person_rec,
3894 p_old_rec => p_old_person_rec,
3895 x_name_list => l_name_list,
3896 x_new_value_is_null_list => l_new_value_is_null_list);
3897
3898 -- Debug info.
3899 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
3900 hz_utility_v2pub.debug(p_message=>'initAttributeList (-)',
3901 p_prefix=>l_debug_prefix,
3902 p_msg_level=>fnd_log.level_procedure);
3903 END IF;
3904
3905 -- if there no attributes have been passed in in the record, we do not
3906 -- need to do further check.
3907
3908 IF l_name_list IS NULL OR
3909 l_name_list.COUNT = 0
3910 THEN
3911 -- Debug info.
3912 IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
3913 hz_utility_v2pub.debug(p_message=>'name list is null. No need to do further check',
3914 p_prefix =>l_debug_prefix,
3915 p_msg_level=>fnd_log.level_statement);
3916 END IF;
3917 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
3918 hz_utility_v2pub.debug(p_message=>'updatePerSSTColumn (-)',
3919 p_prefix=>l_debug_prefix,
3920 p_msg_level=>fnd_log.level_procedure);
3921 END IF;
3922
3923 RETURN;
3924 END IF;
3925
3926 -- get column Null property.
3927
3928 hz_mixnm_api_dynamic_pkg.getColumnNullProperty(
3929 p_sst_rec => p_sst_person_rec,
3930 x_value_is_null_list => l_sst_value_is_null_list,
3931 x_value_is_not_null_list => l_sst_value_is_not_null_list);
3932
3933 -- Debug info.
3934 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
3935 hz_utility_v2pub.debug(p_message=>'getColumnNullProperty (-)',
3936 p_prefix=>l_debug_prefix,
3937 p_msg_level=>fnd_log.level_procedure);
3938 END IF;
3939
3940 -- return a updatable of all of attributes we passed in.
3941
3942 areSSTColumnsUpdeable (
3943 p_party_id => p_party_id,
3944 p_entity_name => 'HZ_PERSON_PROFILES',
3945 p_attribute_name_list => l_name_list,
3946 p_value_is_null_list => l_sst_value_is_null_list,
3947 p_data_source_type => p_data_source_type,
3948 x_updatable_flag_list => l_updatable_flag_list,
3949 x_exception_type_list => l_exception_type_list,
3950 x_return_status => x_return_status,
3951 p_raise_error_flag => p_raise_error_flag,
3952 p_known_dict_id => 'Y',
3953 p_new_value_is_null_list => l_new_value_is_null_list );
3954
3955 -- set final SST record. API can use this record to create / update
3956 -- SST record.
3957
3958 IF p_create_update_sst_flag = 'C' THEN
3959 hz_mixnm_api_dynamic_pkg.createSSTRecord(
3960 p_new_data_source => p_data_source_type,
3961 p_new_rec => p_new_person_rec,
3962 p_sst_rec => p_new_sst_person_rec,
3963 p_updateable_flag_list => l_updatable_flag_list,
3964 p_exception_type_list => l_exception_type_list);
3965 ELSE
3966 hz_mixnm_api_dynamic_pkg.updateSSTRecord(
3967 p_create_update_flag => p_create_update_flag,
3968 p_new_data_source => p_data_source_type,
3969 p_new_rec => p_new_person_rec,
3970 p_sst_rec => p_new_sst_person_rec,
3971 p_updateable_flag_list => l_updatable_flag_list,
3972 p_exception_type_list => l_exception_type_list,
3973 p_new_value_is_null_list => l_new_value_is_null_list,
3974 x_data_source_list => l_data_source_list);
3975 END IF;
3976
3977 -- Debug info.
3978 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
3979 hz_utility_v2pub.debug(p_message=>'setSSTRecord (-)',
3980 p_prefix=>l_debug_prefix,
3981 p_msg_level=>fnd_log.level_procedure);
3982 END IF;
3983
3984 -- update exception table to trace data source for each attribute.
3985
3986 updateExceptions (
3987 p_create_update_sst_flag => p_create_update_sst_flag,
3988 p_party_id => p_party_id,
3989 p_data_source_type => p_data_source_type,
3990 p_name_list => l_name_list,
3991 p_updatable_flag_list => l_updatable_flag_list,
3992 p_exception_type_list => l_exception_type_list,
3993 p_sst_value_is_not_null_list => l_sst_value_is_not_null_list,
3994 p_data_source_list => l_data_source_list);
3995
3996 -- Debug info.
3997 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
3998 hz_utility_v2pub.debug(p_message=>'updatePerSSTProfile (-)',
3999 p_prefix=>l_debug_prefix,
4000 p_msg_level=>fnd_log.level_procedure);
4001 END IF;
4002
4003 -- Check if API is called in debug mode. If yes, disable debug.
4004 --disable_debug;
4005
4006 END updatePerSSTProfile;
4007
4008 /**
4009 * PROCEDURE updateSSTOrgProfile
4010 *
4011 * DESCRIPTION
4012 * Return new SST record to create / update organization SST profile.
4013 *
4014 * EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
4015 *
4016 * ARGUMENTS
4017 * IN:
4018 * p_create_update_flag Create update flag.
4019 * p_create_update_sst_flag Create update SST profile flag.
4020 * p_raise_error_flag Raise error flag.
4021 * p_party_type Party type.
4022 * p_party_id Party Id.
4023 * p_new_organization_rec New organization record.
4024 * p_old_organization_rec New organization record.
4025 * p_sst_organization_rec Current SST organization record.
4026 * p_data_source_type Comming data source type.
4027 * IN/OUT:
4028 * p_new_sst_organization_rec New SST organization record.
4029 * x_return_status Return status.
4030 *
4031 * NOTES
4032 * The procedure should only be called if the mix-n-match is enable for
4033 * the entity.
4034 *
4035 * MODIFICATION HISTORY
4036 *
4037 * 05-01-2002 Jianying Huang o Created
4038 */
4039
4040 PROCEDURE updateOrgSSTProfile (
4041 p_create_update_flag IN VARCHAR2,
4042 p_create_update_sst_flag IN VARCHAR2,
4043 p_raise_error_flag IN VARCHAR2,
4044 p_party_id IN NUMBER,
4045 p_new_organization_rec IN HZ_PARTY_V2PUB.ORGANIZATION_REC_TYPE,
4046 p_old_organization_rec IN HZ_PARTY_V2PUB.ORGANIZATION_REC_TYPE,
4047 p_sst_organization_rec IN HZ_PARTY_V2PUB.ORGANIZATION_REC_TYPE,
4048 p_new_sst_organization_rec IN OUT NOCOPY HZ_PARTY_V2PUB.ORGANIZATION_REC_TYPE,
4049 p_data_source_type IN VARCHAR2,
4050 x_return_status IN OUT NOCOPY VARCHAR2
4051 ) IS
4052
4053 l_new_organization_rec HZ_PARTY_V2PUB.ORGANIZATION_REC_TYPE := p_new_organization_rec;
4054 l_new_value_is_null_list INDEXVARCHAR1List;
4055 l_sst_value_is_null_list INDEXVARCHAR1List;
4056 l_sst_value_is_not_null_list INDEXVARCHAR1List;
4057 l_updatable_flag_list INDEXVARCHAR1List;
4058 l_exception_type_list INDEXVARCHAR30List;
4059 l_name_list INDEXVARCHAR30List;
4060 l_data_source_list INDEXVARCHAR30List;
4061 i NUMBER;
4062 l_debug_prefix VARCHAR2(30) := '';
4063
4064 BEGIN
4065
4066 -- Check if API is called in debug mode. If yes, enable debug.
4067 --enable_debug;
4068
4069 -- Debug info.
4070 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
4071 hz_utility_v2pub.debug(p_message=>'updateOrgSSTProfile (+)',
4072 p_prefix=>l_debug_prefix,
4073 p_msg_level=>fnd_log.level_procedure);
4074 END IF;
4075 IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
4076 hz_utility_v2pub.debug(p_message=>'p_create_update_sst_flag = '||p_create_update_sst_flag,
4077 p_prefix =>l_debug_prefix,
4078 p_msg_level=>fnd_log.level_statement);
4079 END IF;
4080
4081 -- find out those attributes we need to check.
4082
4083 hz_mixnm_api_dynamic_pkg.initAttributeList(
4084 p_create_update_flag => p_create_update_flag,
4085 p_new_rec => p_new_organization_rec,
4086 p_old_rec => p_old_organization_rec,
4087 x_name_list => l_name_list,
4088 x_new_value_is_null_list => l_new_value_is_null_list);
4089
4090 -- Debug info.
4091 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
4092 hz_utility_v2pub.debug(p_message=>'initAttributeList (-)',
4093 p_prefix=>l_debug_prefix,
4094 p_msg_level=>fnd_log.level_procedure);
4095 END IF;
4096
4097 -- if there no attributes have been passed in in the record, we do not
4098 -- need to do further check.
4099
4100 IF l_name_list IS NULL OR
4101 l_name_list.COUNT = 0
4102 THEN
4103 -- Debug info.
4104 IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
4105 hz_utility_v2pub.debug(p_message=>'name list is null. No need to do further check',
4106 p_prefix =>l_debug_prefix,
4107 p_msg_level=>fnd_log.level_statement);
4108 END IF;
4109 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
4110 hz_utility_v2pub.debug(p_message=>'updatePerSSTColumn (-)',
4111 p_prefix=>l_debug_prefix,
4112 p_msg_level=>fnd_log.level_procedure);
4113 END IF;
4114
4115 RETURN;
4116 END IF;
4117
4118 -- get column Null property.
4119
4120 hz_mixnm_api_dynamic_pkg.getColumnNullProperty(
4121 p_sst_rec => p_sst_organization_rec,
4122 x_value_is_null_list => l_sst_value_is_null_list,
4123 x_value_is_not_null_list => l_sst_value_is_not_null_list);
4124
4125 -- Debug info.
4126 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
4127 hz_utility_v2pub.debug(p_message=>'getColumnNullProperty (-)',
4128 p_prefix=>l_debug_prefix,
4129 p_msg_level=>fnd_log.level_procedure);
4130 END IF;
4131
4132 -- return a updatable of all of attributes we passed in.
4133
4134 areSSTColumnsUpdeable (
4135 p_party_id => p_party_id,
4136 p_entity_name => 'HZ_ORGANIZATION_PROFILES',
4137 p_attribute_name_list => l_name_list,
4138 p_value_is_null_list => l_sst_value_is_null_list,
4139 p_data_source_type => p_data_source_type,
4140 x_updatable_flag_list => l_updatable_flag_list,
4141 x_exception_type_list => l_exception_type_list,
4142 x_return_status => x_return_status,
4143 p_raise_error_flag => p_raise_error_flag,
4144 p_known_dict_id => 'Y',
4145 p_new_value_is_null_list => l_new_value_is_null_list );
4146
4147 -- set final SST record. API can use this record to create / update
4148 -- SST record.
4149
4150 IF p_create_update_sst_flag = 'C' THEN
4151 hz_mixnm_api_dynamic_pkg.createSSTRecord(
4152 p_new_data_source => p_data_source_type,
4153 p_new_rec => p_new_organization_rec,
4154 p_sst_rec => p_new_sst_organization_rec,
4155 p_updateable_flag_list => l_updatable_flag_list,
4156 p_exception_type_list => l_exception_type_list);
4157 ELSE
4158 -- sync data source for sic_code and sic_code_type.
4159
4160 l_new_organization_rec.sic_code :=
4161 nvl(l_new_organization_rec.sic_code, p_old_organization_rec.sic_code);
4162 l_new_organization_rec.sic_code_type :=
4163 nvl(l_new_organization_rec.sic_code_type, p_old_organization_rec.sic_code_type);
4164
4165 hz_mixnm_api_dynamic_pkg.updateSSTRecord(
4166 p_create_update_flag => p_create_update_flag,
4167 p_new_data_source => p_data_source_type,
4168 p_new_rec => l_new_organization_rec,
4169 p_sst_rec => p_new_sst_organization_rec,
4170 p_updateable_flag_list => l_updatable_flag_list,
4171 p_exception_type_list => l_exception_type_list,
4172 p_new_value_is_null_list => l_new_value_is_null_list,
4173 x_data_source_list => l_data_source_list);
4174 END IF;
4175
4176 -- Debug info.
4177 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
4178 hz_utility_v2pub.debug(p_message=>'setSSTRecord (-)',
4179 p_prefix=>l_debug_prefix,
4180 p_msg_level=>fnd_log.level_procedure);
4181 END IF;
4182
4183 -- update exception table to trace data source for each attribute.
4184
4185 updateExceptions (
4186 p_create_update_sst_flag => p_create_update_sst_flag,
4187 p_party_id => p_party_id,
4188 p_data_source_type => p_data_source_type,
4189 p_name_list => l_name_list,
4190 p_updatable_flag_list => l_updatable_flag_list,
4191 p_exception_type_list => l_exception_type_list,
4192 p_sst_value_is_not_null_list => l_sst_value_is_not_null_list,
4193 p_data_source_list => l_data_source_list);
4194
4195 -- Debug info.
4196 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
4197 hz_utility_v2pub.debug(p_message=>'updateOrgSSTProfile (-)',
4198 p_prefix=>l_debug_prefix,
4199 p_msg_level=>fnd_log.level_procedure);
4200 END IF;
4201
4202 -- Check if API is called in debug mode. If yes, disable debug.
4203 --disable_debug;
4204
4205 END updateOrgSSTProfile;
4206
4207 /**
4208 * PROCEDURE getDictIndexedNameList
4209 *
4210 * DESCRIPTION
4211 * Split a new list into non-restricted attributes list and restricted
4212 * attributes list.
4213 *
4214 * EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
4215 *
4216 * ARGUMENTS
4217 * IN:
4218 * p_entity_name Entity name.
4219 * p_name_list Attribute name list.
4220 * OUT:
4221 * x_restricted_name_list Restricted attributes' name list.
4222 * x_nonrestricted_name_list Non-Restricted attributes' name list.
4223 *
4224 * NOTES
4225 * The procedure should only be called if the mix-n-match is enable for
4226 * the entity.
4227 *
4228 * MODIFICATION HISTORY
4229 *
4230 * 05-01-2002 Jianying Huang o Created
4231 */
4232
4233 PROCEDURE getDictIndexedNameList (
4234 p_entity_name IN VARCHAR2,
4235 p_name_list IN INDEXVARCHAR30List,
4236 x_restricted_name_list OUT NOCOPY INDEXVARCHAR30List,
4237 x_nonrestricted_name_list OUT NOCOPY INDEXVARCHAR30List
4238 ) IS
4239
4240 l_entity_attr_id NUMBER;
4241 i NUMBER;
4242 j NUMBER := 1;
4243 l_debug_prefix VARCHAR2(30) := '';
4244
4245 BEGIN
4246
4247 -- Check if API is called in debug mode. If yes, enable debug.
4248 --enable_debug;
4249
4250 -- Debug info.
4251 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
4252 hz_utility_v2pub.debug(p_message=>'getDictIndexedNameList (+)',
4253 p_prefix=>l_debug_prefix,
4254 p_msg_level=>fnd_log.level_procedure);
4255 END IF;
4256
4257 -- load all of related setups and cache them.
4258 cacheSetupForPartyProfiles(null, p_entity_name);
4259
4260 -- for each attribute in the list, check if it is in the setup table.
4261 -- if it is, put it in the restricted_name_list.
4262
4263 i := p_name_list.FIRST;
4264 WHILE i <= p_name_list.LAST LOOP
4265 l_entity_attr_id := getEntityAttrId(p_entity_name, p_name_list(i));
4266
4267 IF l_entity_attr_id = 0 THEN
4268 x_nonrestricted_name_list(i) := p_name_list(i);
4269 j := j + 1;
4270 ELSE
4271 x_restricted_name_list(l_entity_attr_id) := p_name_list(i);
4272 END IF;
4273
4274 i := p_name_list.NEXT(i);
4275 END LOOP;
4276
4277 -- Debug info.
4278 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
4279 hz_utility_v2pub.debug(p_message=>'getDictIndexedNameList (-)',
4280 p_prefix=>l_debug_prefix,
4281 p_msg_level=>fnd_log.level_procedure);
4282 END IF;
4283
4284 -- Check if API is called in debug mode. If yes, disable debug.
4285 --disable_debug;
4286
4287 END getDictIndexedNameList;
4288
4289 /**
4290 * PROCEDURE areSSTColumnsUpdeable
4291 *
4292 * DESCRIPTION
4293 * Return a list to indicate which SST attributes are updatable and which are not.
4294 *
4295 * EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
4296 *
4297 * ARGUMENTS
4298 * IN:
4299 * p_party_id Party Id.
4300 * p_entity_name Entity name.
4301 * p_attribute_name_list Attribute name list.
4302 * p_value_is_null_list 'Y' if the corresponding SST column is null.
4303 * p_data_source_type Comming data source.
4304 * p_raise_error_flag Raise error flag.
4305 * p_known_dict_id 'Y' if use knew entity id.
4306 * IN/OUT:
4307 * x_return_status Return status.
4308 * OUT:
4309 * x_updatable_flag_list Updatable list.
4310 *
4311 * NOTES
4312 *
4313 * MODIFICATION HISTORY
4314 * The procedure should only be called if the mix-n-match is enable for
4315 * the entity.
4316 *
4317 * 05-01-2002 Jianying Huang o Created
4318 */
4319
4320 PROCEDURE areSSTColumnsUpdeable (
4321 p_party_id IN NUMBER,
4322 p_entity_name IN VARCHAR2,
4323 p_attribute_name_list IN INDEXVARCHAR30List,
4324 p_value_is_null_list IN INDEXVARCHAR1List,
4325 p_data_source_type IN VARCHAR2,
4326 x_updatable_flag_list OUT NOCOPY INDEXVARCHAR1List,
4327 x_return_status IN OUT NOCOPY VARCHAR2,
4328 p_raise_error_flag IN VARCHAR2,
4329 p_known_dict_id IN VARCHAR2
4330 ) IS
4331
4332 l_exception_type_list INDEXVARCHAR30List;
4333 l_new_value_is_null_list INDEXVARCHAR1List;
4334
4335
4336 BEGIN
4337
4338 -- Check if API is called in debug mode. If yes, enable debug.
4339 --enable_debug;
4340
4341 -- return if the attribute name list is empty.
4342
4343 IF p_attribute_name_list IS NULL OR
4344 p_attribute_name_list.COUNT = 0
4345 THEN
4346 RETURN;
4347 END IF;
4348
4349 areSSTColumnsUpdeable (
4350 p_party_id => p_party_id,
4351 p_entity_name => p_entity_name,
4352 p_attribute_name_list => p_attribute_name_list,
4353 p_value_is_null_list => p_value_is_null_list,
4354 p_data_source_type => p_data_source_type,
4355 x_updatable_flag_list => x_updatable_flag_list,
4356 x_exception_type_list => l_exception_type_list,
4357 x_return_status => x_return_status,
4358 p_raise_error_flag => p_raise_error_flag,
4359 p_known_dict_id => p_known_dict_id,
4360 p_new_value_is_null_list => l_new_value_is_null_list
4361 );
4362
4363 -- Check if API is called in debug mode. If yes, disable debug.
4364 --disable_debug;
4365
4366 END areSSTColumnsUpdeable;
4367
4368 /**
4369 * PROCEDURE LoadDataSources
4370 *
4371 * DESCRIPTION
4372 * Load data sources for a given entity.
4373 *
4374 * EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
4375 *
4376 * ARGUMENTS
4377 * IN:
4378 * p_entity_name Entity name.
4379 * p_called_from_policy_function A flag to indicate if the procedure is called
4380 * from policy function.
4381 * IN/OUT:
4382 * p_entity_attr_id Entity Id.
4383 * p_mixnmatch_enabled If the mix-n-match is enabled for this entity.
4384 * p_selected_datasources Select data sources for this entity.
4385 *
4386 * NOTES
4387 *
4388 * MODIFICATION HISTORY
4389 *
4390 * 05-01-2002 Jianying Huang o Created
4391 * 07-07-2005 Dhaval Mehta o Bug 4376604. p_selected_data_sources has been made
4392 * redundant and has been retained for backward compatibility.
4393 */
4394
4395 PROCEDURE LoadDataSources (
4396 p_entity_name IN VARCHAR2,
4397 p_entity_attr_id IN OUT NOCOPY NUMBER,
4398 p_mixnmatch_enabled IN OUT NOCOPY VARCHAR2,
4399 p_selected_datasources IN OUT NOCOPY VARCHAR2,
4400 p_called_from_policy_function IN VARCHAR2
4401 ) IS
4402 l_debug_prefix VARCHAR2(30) := '';
4403 BEGIN
4404
4405 --enable_debug;
4406
4407 -- Debug info.
4408 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
4409 hz_utility_v2pub.debug(p_message=>'LoadDataSources (+)',
4410 p_prefix=>l_debug_prefix,
4411 p_msg_level=>fnd_log.level_procedure);
4412 END IF;
4413
4414 --IF p_mixnmatch_enabled IS NULL THEN
4415 p_mixnmatch_enabled :=
4416 isMixNMatchEnabled(p_entity_name, p_entity_attr_id, p_called_from_policy_function);
4417 --END IF;
4418 IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
4419 hz_utility_v2pub.debug(p_message=>'p_mixnmatch_enabled = '||p_mixnmatch_enabled,
4420 p_prefix =>l_debug_prefix,
4421 p_msg_level=>fnd_log.level_statement);
4422 END IF;
4423
4424 /*
4425 IF p_mixnmatch_enabled = 'Y' AND
4426 p_entity_attr_id <> 0 AND
4427 G_ENTITY_DATA_SOURCE.EXISTS(p_entity_attr_id) AND
4428 G_ENTITY_DATA_SOURCE(p_entity_attr_id) IS NOT NULL
4429 THEN
4430 -- p_entity_attr_id := l_entity_attr_id;
4431 p_selected_datasources := G_ENTITY_DATA_SOURCE(p_entity_attr_id);
4432 ELSE
4433 p_selected_datasources := ''''||G_MISS_CONTENT_SOURCE_TYPE||'''';
4434 END IF;
4435 */
4436 -- Bug 4376604. This parameter has been retained for backward compatibility only.
4437 p_selected_datasources := NULL;
4438
4439 -- Debug info.
4440 IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
4441 hz_utility_v2pub.debug(p_message=>'p_selected_datasources = '||p_selected_datasources,
4442 p_prefix =>l_debug_prefix,
4443 p_msg_level=>fnd_log.level_statement);
4444 END IF;
4445 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
4446 hz_utility_v2pub.debug(p_message=>'LoadDataSources (-)',
4447 p_prefix=>l_debug_prefix,
4448 p_msg_level=>fnd_log.level_procedure);
4449 END IF;
4450
4451 --disable_debug;
4452
4453 END LoadDataSources;
4454
4455 /**
4456 * FUNCTION getSelectedDataSources
4457 *
4458 * DESCRIPTION
4459 * Return selected data sources for a given entity.
4460 * Return selected data sources for a given entity. The
4461 * function is created for policy function. For anywhere
4462 * else, you should call LoadDataSources.
4463 *
4464 * EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
4465 *
4466 * ARGUMENTS
4467 * IN:
4468 * p_entity_name Entity name.
4469 * IN/OUT:
4470 * p_entity_attr_id Entity Id.
4471 *
4472 * NOTES
4473 *
4474 * MODIFICATION HISTORY
4475 *
4476 * 05-01-2002 Jianying Huang o Created
4477 */
4478
4479 FUNCTION getSelectedDataSources (
4480 p_entity_name IN VARCHAR2,
4481 p_entity_attr_id IN OUT NOCOPY NUMBER
4482 ) RETURN VARCHAR2 IS
4483
4484 -- Bug 4171892
4485 l_selected_datasources VARCHAR2(1000);
4486 l_mixnmatch_enabled VARCHAR2(1);
4487
4488 BEGIN
4489
4490 LoadDataSources(
4491 p_entity_name,
4492 p_entity_attr_id,
4493 l_mixnmatch_enabled,
4494 l_selected_datasources,
4495 'Y');
4496
4497 RETURN l_selected_datasources;
4498
4499 END getSelectedDataSources;
4500
4501 /**
4502 * FUNCTION isEntityUserCreatable
4503 *
4504 * DESCRIPTION
4505 * Return if user can create user-entered data.
4506 *
4507 * EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
4508 *
4509 * ARGUMENTS
4510 * IN:
4511 * p_entity_name Entity name.
4512 * IN/OUT:
4513 * p_entity_attr_id Entity Id.
4514 *
4515 * NOTES
4516 * The procedure should only be called if the mix-n-match is enable for
4517 * the entity.
4518 *
4519 * MODIFICATION HISTORY
4520 *
4521 * 05-01-2002 Jianying Huang o Created
4522 * 01-05-2005 Rajib Ranjan Borah o SSM SST Integration and Extension.
4523 * Default value will be 'N' instead of 'Y'.
4524 */
4525
4526 FUNCTION isEntityUserCreatable (
4527 p_entity_name IN VARCHAR2,
4528 p_entity_attr_id IN OUT NOCOPY NUMBER
4529 ) RETURN VARCHAR2 IS
4530 BEGIN
4531
4532 -- load all of other entity related setups and cache them.
4533 cacheSetupForOtherEntities(TRUE);
4534
4535 IF p_entity_attr_id IS NULL OR
4536 p_entity_attr_id = 0
4537 THEN
4538 p_entity_attr_id := getEntityAttrId(p_entity_name);
4539 END IF;
4540
4541 IF p_entity_attr_id > 0 AND
4542 G_CREATE_USER_ENTERED.EXISTS(p_entity_attr_id)
4543 THEN
4544 RETURN G_CREATE_USER_ENTERED(p_entity_attr_id);
4545 ELSE
4546 -- SSM SST Integration and Extension
4547 -- default value will be 'N'
4548 RETURN 'Y';
4549 END IF;
4550
4551 END isEntityUserCreatable;
4552
4553 --------------------------------------------------------------------------
4554 -- the following procedures are called by mix-n-match concurrent program.
4555 --------------------------------------------------------------------------
4556
4557 /**
4558 * PRIVATE PROCEDURE Write_Log
4559 *
4560 * DESCRIPTION
4561 * Write message into log file.
4562 *
4563 * EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
4564 *
4565 * IN:
4566 * p_str Message.
4567 *
4568 * MODIFICATION HISTORY
4569 *
4570 * 04-30-2002 Jianying Huang o Created.
4571 */
4572
4573 PROCEDURE Write_Log (
4574 p_str IN VARCHAR2
4575 ) IS
4576 BEGIN
4577 FND_FILE.PUT_LINE(FND_FILE.LOG,TO_CHAR(SYSDATE, 'YYYY/MM/DD HH:MI:SS')||' -- '||p_str);
4578 END Write_Log;
4579
4580 /**
4581 * PRIVATE PROCEDURE ResetUpdatedFlag
4582 *
4583 * DESCRIPTION
4584 * Reset updated flag in hz_entity_attributes.
4585 *
4586 * EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
4587 *
4588 * MODIFICATION HISTORY
4589 *
4590 * 04-30-2002 Jianying Huang o Created.
4591 */
4592
4593 PROCEDURE ResetUpdatedFlag IS
4594 BEGIN
4595 UPDATE hz_entity_attributes
4596 SET updated_flag = 'N',
4597 last_updated_by = hz_utility_v2pub.last_updated_by,
4598 last_update_login = hz_utility_v2pub.last_update_login,
4599 last_update_date = SYSDATE,
4600 request_id = hz_utility_v2pub.request_id,
4601 program_application_id = hz_utility_v2pub.program_application_id,
4602 program_id = hz_utility_v2pub.program_id,
4603 program_update_date = SYSDATE;
4604 COMMIT;
4605 END ResetUpdatedFlag;
4606
4607 /**
4608 * PRIVATE PROCEDURE ProcessPartyProfiles
4609 *
4610 * DESCRIPTION
4611 * Return information like how many records need to be processed etc.
4612 *
4613 * EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
4614 *
4615 * OUT:
4616 * x_process_org Flag to indicate if we need to
4617 * process data in organization profiles.
4618 * x_process_org_mode C/U flag for create / update.
4619 * x_org_total Total records need to be processed.
4620 * x_org_id_count PL/SQL table to store organization profile id.
4621 * x_org_id_start PL/SQL table to store the start position.
4622 * x_org_id_end PL/SQL table to store the end position.
4623 * x_process_person Flag to indicate if we need to
4624 * process data in person profiles.
4625 * x_process_person_mode C/U flag for create / update.
4626 * x_per_total Total records need to be processed.
4627 * x_per_id_count PL/SQL table to store organization profile id.
4628 * x_per_id_start PL/SQL table to store the start position.
4629 * x_per_id_end PL/SQL table to store the end position.
4630 *
4631 * MODIFICATION HISTORY
4632 *
4633 * 04-30-2002 Jianying Huang o Created.
4634 */
4635
4636 PROCEDURE ProcessPartyProfiles (
4637 x_process_org OUT NOCOPY BOOLEAN,
4638 x_process_org_mode OUT NOCOPY VARCHAR2,
4639 x_org_total OUT NOCOPY NUMBER,
4640 x_org_id_count OUT NOCOPY INDEXIDList,
4641 x_org_id_start OUT NOCOPY INDEXIDList,
4642 x_org_id_end OUT NOCOPY INDEXIDList,
4643 x_process_person OUT NOCOPY BOOLEAN,
4644 x_process_person_mode OUT NOCOPY VARCHAR2,
4645 x_per_total OUT NOCOPY NUMBER,
4646 x_per_id_count OUT NOCOPY INDEXIDList,
4647 x_per_id_start OUT NOCOPY INDEXIDList,
4648 x_per_id_end OUT NOCOPY INDEXIDList
4649 ) IS
4650
4651 CURSOR c_prof_setup (
4652 p_entity_name VARCHAR2
4653 ) IS
4654 SELECT 'Y'
4655 FROM hz_entity_attributes e
4656 WHERE e.updated_flag = 'Y'
4657 AND e.entity_name = p_entity_name
4658 AND rownum = 1;
4659
4660 CURSOR c_org_parties IS
4661 SELECT distinct party_id
4662 FROM hz_organization_profiles
4663 WHERE effective_end_date IS NULL
4664 AND actual_content_source <> G_MISS_ACTUAL_CONTENT_SOURCE
4665 ORDER BY party_id;
4666
4667 CURSOR c_person_parties IS
4668 SELECT distinct party_id
4669 FROM hz_person_profiles
4670 WHERE effective_end_date IS NULL
4671 AND actual_content_source <> G_MISS_ACTUAL_CONTENT_SOURCE
4672 ORDER BY party_id;
4673
4674 i_org_parties INDEXIDList;
4675 i_person_parties INDEXIDList;
4676 l_result BOOLEAN;
4677 l_rows NUMBER := 1000;
4678 j NUMBER := 0;
4679 l_total NUMBER := 0;
4680 l_subtotal NUMBER := 0;
4681 l_dummy VARCHAR2(1);
4682 l_last_fetch BOOLEAN := false;
4683
4684 BEGIN
4685
4686 -- generate packages which are called to create/update sst record
4687 -- in concurrent program and in API.
4688 hz_mixnm_dynamic_pkg_generator.Gen_PackageForAPI('HZ_MIXNM_API_DYNAMIC_PKG');
4689
4690 hz_mixnm_dynamic_pkg_generator.Gen_PackageForConc('HZ_MIXNM_CONC_DYNAMIC_PKG');
4691
4692 x_process_org := true; x_org_total := 0;
4693
4694 OPEN c_prof_setup('HZ_ORGANIZATION_PROFILES');
4695 FETCH c_prof_setup INTO l_dummy;
4696 IF c_prof_setup%NOTFOUND THEN
4697 x_process_org := false;
4698 END IF;
4699 CLOSE c_prof_setup;
4700
4701 IF x_process_org THEN
4702 Write_Log('process org profiles ...');
4703
4704 IF NVL(fnd_profile.value('HZ_ORG_PROF_MIXNMATCH_ENABLED'), 'N') = 'N'
4705 THEN
4706 x_process_org_mode := 'C';
4707
4708 l_result := fnd_profile.save('HZ_ORG_PROF_MIXNMATCH_ENABLED', 'Y', 'SITE');
4709
4710 IF NOT l_result THEN
4711 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
4712 END IF;
4713 ELSE
4714 x_process_org_mode := 'U';
4715 END IF;
4716
4717 Write_Log('x_process_org_mode = '||x_process_org_mode);
4718
4719 j := 0;
4720 OPEN c_org_parties;
4721 LOOP
4722 FETCH c_org_parties BULK COLLECT INTO i_org_parties LIMIT l_rows;
4723 IF c_org_parties%NOTFOUND THEN
4724 l_last_fetch := true;
4725 END IF;
4726
4727 l_subtotal := i_org_parties.COUNT;
4728 Write_Log('l_subtotal = '||l_subtotal);
4729
4730 IF l_subtotal = 0 AND l_last_fetch THEN
4731 EXIT;
4732 END IF;
4733
4734 j := j + 1;
4735 x_org_id_count(j) := l_subtotal;
4736 x_org_id_start(j) := i_org_parties(1);
4737 x_org_id_end(j) := i_org_parties(l_subtotal);
4738 x_org_total := x_org_total + l_subtotal;
4739
4740 IF l_last_fetch THEN
4741 EXIT;
4742 END IF;
4743 END LOOP;
4744 CLOSE c_org_parties;
4745
4746 Write_Log('COUNT('||x_org_total||') -- START -- END');
4747 FOR i IN 1..x_org_id_count.COUNT LOOP
4748 Write_Log(RPAD(x_org_id_count(i),10)||RPAD(x_org_id_start(i),10)||RPAD(x_org_id_end(i),10));
4749 END LOOP;
4750 END IF;
4751
4752 x_process_person := true; x_per_total := 0;
4753
4754 OPEN c_prof_setup('HZ_PERSON_PROFILES');
4755 FETCH c_prof_setup INTO l_dummy;
4756 IF c_prof_setup%NOTFOUND THEN
4757 x_process_person := false;
4758 END IF;
4759 CLOSE c_prof_setup;
4760
4761 IF x_process_person THEN
4762 Write_Log('process person profiles ...');
4763
4764 IF NVL(fnd_profile.value('HZ_PER_PROF_MIXNMATCH_ENABLED'), 'N') = 'N'
4765 THEN
4766 x_process_person_mode := 'C';
4767
4768 l_result := fnd_profile.save('HZ_PER_PROF_MIXNMATCH_ENABLED', 'Y', 'SITE');
4769
4770 IF NOT l_result THEN
4771 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
4772 END IF;
4773 ELSE
4774 x_process_person_mode := 'U';
4775 END IF;
4776
4777 Write_Log('x_process_person_mode = '||x_process_person_mode);
4778 Write_Log(fnd_profile.value('HZ_PER_PROF_MIXNMATCH_ENABLED'));
4779
4780 j := 0; l_last_fetch := false;
4781 OPEN c_person_parties;
4782 LOOP
4783 FETCH c_person_parties BULK COLLECT INTO i_person_parties LIMIT l_rows;
4784 IF c_person_parties%NOTFOUND THEN
4785 l_last_fetch := true;
4786 END IF;
4787
4788 l_subtotal := i_person_parties.COUNT;
4789 Write_Log('l_subtotal = '||l_subtotal);
4790
4791 IF l_subtotal = 0 AND l_last_fetch THEN
4792 EXIT;
4793 END IF;
4794
4795 j := j + 1;
4796 x_per_id_count(j) := l_subtotal;
4797 x_per_id_start(j) := i_person_parties(1);
4798 x_per_id_end(j) := i_person_parties(l_subtotal);
4799 x_per_total := x_per_total + l_subtotal;
4800
4801 IF l_last_fetch THEN
4802 EXIT;
4803 END IF;
4804 END LOOP;
4805 CLOSE c_person_parties;
4806
4807 Write_Log('COUNT('||x_per_total||') -- START -- END');
4808 FOR i IN 1..x_per_id_count.COUNT LOOP
4809 Write_Log(RPAD(x_per_id_count(i),10)||RPAD(x_per_id_start(i),10)||RPAD(x_per_id_end(i),10));
4810 END LOOP;
4811 END IF;
4812
4813 END ProcessPartyProfiles;
4814
4815 /**
4816 * PRIVATE PROCEDURE CreateUpdatePartyProfiles
4817 *
4818 * DESCRIPTION
4819 * Submit sub-requests to process records in party profile.s
4820 *
4821 * EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
4822 *
4823 * IN:
4824 * p_entity_name Entity name.
4825 * p_create_update_flag C/U flag for create / update.
4826 * p_commit_size Commit size.
4827 * p_party_per_worker Number of records per worker.
4828 * p_id_count Number of records.
4829 * p_id_start Start position.
4830 * p_id_end End position.
4831 *
4832 * MODIFICATION HISTORY
4833 *
4834 * 04-30-2002 Jianying Huang o Created.
4835 */
4836
4837 PROCEDURE CreateUpdatePartyProfiles (
4838 p_entity_name IN VARCHAR2,
4839 p_create_update_flag IN VARCHAR2,
4840 p_commit_size IN NUMBER,
4841 p_party_per_worker IN NUMBER,
4842 p_id_count IN INDEXIDList,
4843 p_id_start IN INDEXIDList,
4844 p_id_end IN INDEXIDList
4845 ) IS
4846
4847 l_subtotal NUMBER := 0;
4848 l_start NUMBER := 0;
4849 l_end NUMBER := 0;
4850 l_request_id NUMBER := 0;
4851 errbuf VARCHAR2(100);
4852 retcode VARCHAR2(100);
4853
4854 BEGIN
4855
4856 FOR i IN 1..p_id_count.COUNT LOOP
4857 IF l_start = 0 THEN
4858 l_start := p_id_start(i);
4859 END IF;
4860 l_subtotal := l_subtotal + p_id_count(i);
4861 l_end := p_id_end(i);
4862
4863 IF l_subtotal >= p_party_per_worker OR
4864 i = p_id_count.COUNT
4865 THEN
4866 -- conc_sub(errbuf, retcode,p_create_update_flag||','||p_entity_name,l_start,l_end,p_commit_size);
4867
4868 l_request_id :=
4869 FND_REQUEST.SUBMIT_REQUEST(
4870 'AR', 'HZ_THIRD_PARTY_UPDATE_SUB', '',
4871 SYSDATE, FALSE,
4872 p_create_update_flag||','||p_entity_name,
4873 TO_CHAR(l_start), TO_CHAR(l_end),
4874 TO_CHAR(p_commit_size));
4875
4876 l_start := 0; l_subtotal := 0;
4877 END IF;
4878 END LOOP;
4879
4880 END CreateUpdatePartyProfiles;
4881
4882 /**
4883 * PRIVATE PROCEDURE ProcessOtherEntities
4884 *
4885 * DESCRIPTION
4886 * Return flags to indicate is we need to process data in other entities.
4887 *
4888 * EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
4889 *
4890 * OUT:
4891 * x_process_loc Flag to indicate if we need to process
4892 * records in hz_locations.
4893 * x_process_cp Flag to indicate if we need to process
4894 * records in hz_contact_points.
4895 *
4896 * MODIFICATION HISTORY
4897 *
4898 * 04-30-2002 Jianying Huang o Created.
4899 */
4900
4901 PROCEDURE ProcessOtherEntities (
4902 x_process_loc OUT NOCOPY BOOLEAN,
4903 x_process_cp OUT NOCOPY BOOLEAN
4904 ) IS
4905
4906 CURSOR c_other_setup (
4907 p_entity_name VARCHAR2
4908 ) IS
4909 SELECT 'Y'
4910 FROM hz_entity_attributes e
4911 WHERE e.updated_flag = 'Y'
4912 AND e.entity_name = p_entity_name;
4913
4914 l_dummy VARCHAR2(1);
4915
4916 BEGIN
4917
4918 OPEN c_other_setup('HZ_LOCATIONS');
4919 FETCH c_other_setup INTO l_dummy;
4920 IF c_other_setup%NOTFOUND THEN
4921 x_process_loc := false;
4922 END IF;
4923 CLOSE c_other_setup;
4924
4925 OPEN c_other_setup('HZ_CONTACT_POINTS');
4926 FETCH c_other_setup INTO l_dummy;
4927 IF c_other_setup%NOTFOUND THEN
4928 x_process_cp := false;
4929 END IF;
4930 CLOSE c_other_setup;
4931
4932 END ProcessOtherEntities;
4933
4934 /**
4935 * PRIVATE PROCEDURE AddPolicy
4936 *
4937 * DESCRIPTION
4938 * Add policy functions.
4939 *
4940 * EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
4941 *
4942 * IN:
4943 * p_entities Entity list.
4944 *
4945 * MODIFICATION HISTORY
4946 *
4947 * 04-30-2002 Jianying Huang o Created.
4948 * 01-13-2005 Rajib Ranjan Borah o SSM SST Integration and Extension.
4949 * Policy function will not be called for
4950 * non-profile entities.
4951 */
4952
4953 PROCEDURE AddPolicy (
4954 p_entities IN INDEXVARCHAR30List
4955 ) IS
4956
4957 l_policy_name CONSTANT VARCHAR2(30):= 'content_source_type_sec';
4958 l_policy_function CONSTANT VARCHAR2(80) := 'hz_common_pub.content_source_type_security';
4959
4960 BEGIN
4961
4962 -- get AR schema name.
4963 IF G_AR_SCHEMA_NAME IS NULL THEN
4964 G_AR_SCHEMA_NAME := hz_utility_v2pub.Get_SchemaName('AR');
4965 END IF;
4966
4967 -- get APPS schema name.
4968 IF G_APPS_SCHEMA_NAME IS NULL THEN
4969 G_APPS_SCHEMA_NAME := hz_utility_v2pub.Get_AppsSchemaName;
4970 END IF;
4971
4972 -- add third party policy.
4973 FOR i IN 1..p_entities.COUNT LOOP
4974 IF fnd_access_control_util.Policy_Exists(
4975 G_AR_SCHEMA_NAME, p_entities(i), l_policy_name ) = 'FALSE'
4976 THEN
4977 fnd_access_control_util.Add_Policy(
4978 G_AR_SCHEMA_NAME, p_entities(i), l_policy_name,
4979 G_APPS_SCHEMA_NAME, l_policy_function);
4980 END IF;
4981
4982 /*
4983 IF p_entities(i) = 'HZ_LOCATIONS' AND
4984 fnd_access_control_util.Policy_Exists(
4985 G_AR_SCHEMA_NAME, 'HZ_PARTY_SITES', l_policy_name ) = 'FALSE'
4986 THEN
4987 fnd_access_control_util.Add_Policy(
4988 G_AR_SCHEMA_NAME, 'HZ_PARTY_SITES', l_policy_name,
4989 G_APPS_SCHEMA_NAME, l_policy_function);
4990 ELSIF p_entities(i) = 'HZ_FINANCIAL_REPORTS' AND
4991 fnd_access_control_util.Policy_Exists(
4992 G_AR_SCHEMA_NAME, 'HZ_FINANCIAL_NUMBERS', l_policy_name ) = 'FALSE'
4993 THEN
4994 fnd_access_control_util.Add_Policy(
4995 G_AR_SCHEMA_NAME, 'HZ_FINANCIAL_NUMBERS', l_policy_name,
4996 G_APPS_SCHEMA_NAME, l_policy_function);
4997 END IF;
4998 */
4999 END LOOP;
5000
5001 END AddPolicy;
5002
5003 /**
5004 * PROCEDURE conc_main
5005 *
5006 * DESCRIPTION
5007 * Main concurrent program for mix-n-match.
5008 *
5009 * EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
5010 *
5011 * IN:
5012 * p_commit_size Commit size.
5013 * p_num_of_worker Number of workers.
5014 * OUT:
5015 * errbuf Buffer for error message.
5016 * retcode Return code.
5017 *
5018 * MODIFICATION HISTORY
5019 *
5020 * 04-30-2002 Jianying Huang o Created.
5021 * 01-13-2005 Rajib Ranjan Borah o SSM SST Integration and Extension.
5022 * Policy function will not be called for
5023 * non-profile entities.
5024 *
5025 */
5026
5027 PROCEDURE conc_main (
5028 errbuf OUT NOCOPY VARCHAR2,
5029 retcode OUT NOCOPY VARCHAR2,
5030 p_commit_size IN VARCHAR2,
5031 p_num_of_worker IN VARCHAR2
5032 ) IS
5033
5034 CURSOR c_setup IS
5035 SELECT 'Y'
5036 FROM hz_entity_attributes e
5037 WHERE e.updated_flag = 'Y'
5038 AND rownum = 1;
5039
5040 CURSOR c_prof_setup IS
5041 SELECT 'Y'
5042 FROM hz_entity_attributes e
5043 WHERE e.updated_flag = 'Y'
5044 AND e.entity_name IN ('HZ_ORGANIZATION_PROFILES','HZ_PERSON_PROFILES')
5045 AND rownum = 1;
5046
5047 l_process_profile BOOLEAN := true;
5048
5049 CURSOR c_other_setup IS
5050 SELECT 'Y'
5051 FROM hz_entity_attributes e,
5052 hz_select_data_sources s
5053 WHERE e.updated_flag = 'Y'
5054 AND e.entity_name NOT IN ('HZ_ORGANIZATION_PROFILES','HZ_PERSON_PROFILES')
5055 AND rownum = 1;
5056
5057 l_process_other_entities BOOLEAN := true;
5058
5059 CURSOR c_entities IS
5060 SELECT UNIQUE e.entity_name
5061 FROM hz_entity_attributes e
5062 WHERE e.updated_flag = 'Y'
5063 -- SSM SST Integration and Extension
5064 -- Only profile entities will be passed for policy function.
5065 AND e.attribute_name is not null;
5066
5067 i_entities INDEXVARCHAR30List;
5068 l_process_org BOOLEAN;
5069 l_process_org_mode VARCHAR2(1);
5070 l_process_person BOOLEAN;
5071 l_process_person_mode VARCHAR2(1);
5072 l_org_id_count INDEXIDList;
5073 l_org_id_start INDEXIDList;
5074 l_org_id_end INDEXIDList;
5075 l_per_id_count INDEXIDList;
5076 l_per_id_start INDEXIDList;
5077 l_per_id_end INDEXIDList;
5078 l_org_total NUMBER;
5079 l_per_total NUMBER;
5080 l_num_of_worker NUMBER := NVL(TO_NUMBER(p_num_of_worker),1);
5081 l_commit_size NUMBER := NVL(TO_NUMBER(p_commit_size),500);
5082 l_party_per_worker NUMBER;
5083 l_org_party_per_worker NUMBER;
5084 l_per_party_per_worker NUMBER;
5085 l_org_num_of_worker NUMBER;
5086 l_person_num_of_worker NUMBER;
5087 l_process_loc BOOLEAN;
5088 l_process_cp BOOLEAN;
5089 l_dummy VARCHAR2(1);
5090
5091 BEGIN
5092 -- standard start of API savepoint
5093 SAVEPOINT conc_main;
5094
5095 -- We will return to the caller if no change, i.e. the selection
5096 -- of data sources, the ranking, has been done in setup.
5097 OPEN c_setup;
5098 FETCH c_setup INTO l_dummy;
5099 IF c_setup%NOTFOUND THEN
5100 Write_Log('No setup for any entity / attribute has been updated.');
5101 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,
5102 'You did not change or your change to the setup does not have impact to existing data.');
5103 CLOSE c_setup;
5104 RETURN;
5105 END IF;
5106 CLOSE c_setup;
5107
5108 -- if setup has been changed for party profiles.
5109
5110 OPEN c_prof_setup;
5111 FETCH c_prof_setup INTO l_dummy;
5112 IF c_prof_setup%NOTFOUND THEN
5113 l_process_profile := false;
5114 END IF;
5115 CLOSE c_prof_setup;
5116
5117 hz_common_pub.disable_cont_source_security;
5118
5119 -- process party profiles.
5120 IF l_process_profile THEN
5121 ProcessPartyProfiles(
5122 l_process_org,
5123 l_process_org_mode,
5124 l_org_total,
5125 l_org_id_count,
5126 l_org_id_start,
5127 l_org_id_end,
5128 l_process_person,
5129 l_process_person_mode,
5130 l_per_total,
5131 l_per_id_count,
5132 l_per_id_start,
5133 l_per_id_end);
5134 IF l_commit_size < 500 THEN
5135 l_commit_size := 500;
5136 END IF;
5137 IF l_num_of_worker < 1 THEN
5138 l_num_of_worker := 1;
5139 END IF;
5140 -- Bug 4227865 : Delete exceptions type = MRR when an
5141 -- attribute is setup as rank
5142 IF l_process_org AND l_org_total <> 0 THEN
5143 DELETE hz_win_source_exceps
5144 WHERE entity_attr_id IN
5145 (SELECT e.entity_attr_id
5146 FROM hz_entity_attributes e, hz_select_data_sources s
5147 WHERE e.entity_name = 'HZ_ORGANIZATION_PROFILES'
5148 AND s.ranking > 0
5149 AND s.content_source_type = 'USER_ENTERED'
5150 AND e.entity_attr_id = s.entity_attr_id
5151 )
5152 and exception_type='MRR';
5153 END IF;
5154 IF l_process_person AND l_per_total <> 0 THEN
5155 DELETE hz_win_source_exceps
5156 WHERE entity_attr_id IN
5157 (SELECT e.entity_attr_id
5158 FROM hz_entity_attributes e, hz_select_data_sources s
5159 WHERE e.entity_name = 'HZ_PERSON_PROFILES'
5160 AND s.ranking > 0
5161 AND s.content_source_type = 'USER_ENTERED'
5162 AND e.entity_attr_id = s.entity_attr_id
5163 )
5164 and exception_type='MRR';
5165 END IF;
5166 IF l_num_of_worker > 1 THEN
5167 l_party_per_worker := ROUND((l_org_total+l_per_total)/l_num_of_worker);
5168 Write_Log('l_party_per_worker = '||l_party_per_worker);
5169
5170 IF l_org_total <> 0 THEN
5171 l_org_num_of_worker := FLOOR(l_org_total/l_party_per_worker);
5172 IF l_org_num_of_worker = 0 THEN
5173 l_org_num_of_worker := 1;
5174 END IF;
5175 l_org_party_per_worker := ROUND(l_org_total/l_org_num_of_worker);
5176 END IF;
5177
5178 IF l_per_total <> 0 THEN
5179 l_person_num_of_worker := FLOOR(l_per_total/l_party_per_worker);
5180 IF l_person_num_of_worker = 0 THEN
5181 l_person_num_of_worker := 1;
5182 END IF;
5183 l_per_party_per_worker := ROUND(l_per_total/l_person_num_of_worker);
5184 END IF;
5185
5186 Write_Log('l_org_num_of_worker = '||l_org_num_of_worker);
5187 Write_Log('l_person_num_of_worker = '||l_person_num_of_worker);
5188 Write_Log('l_org_party_per_worker = '||l_org_party_per_worker);
5189 Write_Log('l_per_party_per_worker = '||l_per_party_per_worker);
5190
5191 IF l_process_org AND l_org_total <> 0 THEN
5192 CreateUpdatePartyProfiles (
5193 'HZ_ORGANIZATION_PROFILES',
5194 l_process_org_mode,
5195 l_commit_size,
5196 l_org_party_per_worker,
5197 l_org_id_count,
5198 l_org_id_start,
5199 l_org_id_end);
5200 END IF;
5201
5202 IF l_process_person AND l_per_total <> 0 THEN
5203 CreateUpdatePartyProfiles (
5204 'HZ_PERSON_PROFILES',
5205 l_process_person_mode,
5206 l_commit_size,
5207 l_per_party_per_worker,
5208 l_per_id_count,
5209 l_per_id_start,
5210 l_per_id_end);
5211 END IF;
5212 ELSE
5213 IF l_process_org AND l_org_total <> 0 THEN
5214 conc_sub (
5215 errbuf, retcode,
5216 l_process_org_mode||',HZ_ORGANIZATION_PROFILES',
5217 l_org_id_start(1),
5218 l_org_id_end(l_org_id_end.COUNT),
5219 l_commit_size);
5220 END IF;
5221
5222 IF l_process_person AND l_per_total <> 0 THEN
5223 conc_sub (
5224 errbuf, retcode,
5225 l_process_org_mode||',HZ_PERSON_PROFILES',
5226 l_per_id_start(1),
5227 l_per_id_end(l_per_id_end.COUNT),
5228 l_commit_size);
5229 END IF;
5230 END IF;
5231 END IF;
5232
5233 -- if setup has been changed for other entities.
5234
5235 OPEN c_other_setup;
5236 FETCH c_other_setup INTO l_dummy;
5237 IF c_other_setup%NOTFOUND THEN
5238 l_process_other_entities := false;
5239 END IF;
5240 CLOSE c_other_setup;
5241
5242 IF l_process_other_entities THEN
5243 Write_Log('process other entities ...');
5244
5245 ProcessOtherEntities(
5246 l_process_loc, l_process_cp);
5247 END IF;
5248
5249 -- add policy functions.
5250
5251 IF NVL(fnd_profile.value('HZ_DNB_POLICY_EXISTS'), 'N') = 'N' THEN
5252 OPEN c_entities;
5253 FETCH c_entities BULK COLLECT INTO i_entities;
5254 CLOSE c_entities;
5255
5256 AddPolicy(i_entities);
5257 END IF;
5258
5259 -- reset updated flag in hz_entity_attributes.
5260 ResetUpdatedFlag;
5261
5262 EXCEPTION
5263 WHEN OTHERS THEN
5264 ROLLBACK to conc_main;
5265 retcode := 2;
5266 errbuf := SQLERRM;
5267
5268 END conc_main;
5269
5270 /**
5271 * PROCEDURE conc_sub
5272 *
5273 * DESCRIPTION
5274 * Sub concurrent program for mix-n-match.
5275 *
5276 * EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
5277 *
5278 * IN:
5279 * p_entity_type Entity type.
5280 * p_from_id From id.
5281 * p_to_id To id.
5282 * p_commit_size Commit size.
5283 * OUT:
5284 * errbuf Buffer for error message.
5285 * retcode Return code.
5286 *
5287 * MODIFICATION HISTORY
5288 *
5289 * 04-30-2002 Jianying Huang o Created.
5290 */
5291
5292 PROCEDURE conc_sub (
5293 errbuf OUT NOCOPY VARCHAR2,
5294 retcode OUT NOCOPY VARCHAR2,
5295 p_entity_type IN VARCHAR2,
5296 p_from_id IN VARCHAR2,
5297 p_to_id IN VARCHAR2,
5298 p_commit_size IN VARCHAR2
5299 ) IS
5300
5301 l_from_id NUMBER := TO_NUMBER(p_from_id);
5302 l_to_id NUMBER := TO_NUMBER(p_to_id);
5303 l_commit_size NUMBER := TO_NUMBER(p_commit_size);
5304 l_process_mode VARCHAR2(30);
5305 l_entity_name VARCHAR2(30);
5306 l_pos1 NUMBER;
5307
5308 BEGIN
5309
5310 --standard start of API savepoint
5311 SAVEPOINT conc_sub;
5312
5313 FND_FILE.PUT_LINE(FND_FILE.LOG,
5314 'p_entity_type = '||p_entity_type||
5315 ', p_from_id = '||p_from_id||
5316 ', p_to_id = '||p_to_id||
5317 ', p_commit_size = '||p_commit_size);
5318
5319 IF INSTRB(p_entity_type, '_PROFILES') > 0 THEN
5320 l_pos1 := INSTRB(p_entity_type, ',');
5321 l_process_mode := SUBSTRB(p_entity_type, 1, l_pos1-1);
5322 l_entity_name := SUBSTRB(p_entity_type, l_pos1+1, LENGTHB(p_entity_type)-l_pos1);
5323
5324 IF l_process_mode = 'C' THEN
5325 IF l_entity_name = 'HZ_ORGANIZATION_PROFILES' THEN
5326 Write_Log('HZ_MIXNM_CONC_DYNAMIC_PKG.BulkCreateOrgSST('||l_from_id||', '||l_to_id||', '||l_commit_size||');');
5327 HZ_MIXNM_CONC_DYNAMIC_PKG.BulkCreateOrgSST(l_from_id, l_to_id, l_commit_size);
5328 ELSE
5329 Write_Log('HZ_MIXNM_CONC_DYNAMIC_PKG.BulkCreatePerSST('||l_from_id||', '||l_to_id||', '||l_commit_size||');');
5330 HZ_MIXNM_CONC_DYNAMIC_PKG.BulkCreatePersonSST(l_from_id, l_to_id, l_commit_size);
5331 END IF;
5332 ELSE
5333 IF l_entity_name = 'HZ_ORGANIZATION_PROFILES' THEN
5334 Write_Log('HZ_MIXNM_CONC_DYNAMIC_PKG.BulkUpdateOrgSST('||l_from_id||', '||l_to_id||', '||l_commit_size||');');
5335 HZ_MIXNM_CONC_DYNAMIC_PKG.BulkUpdateOrgSST(l_from_id, l_to_id, l_commit_size);
5336 ELSE
5337 Write_Log('HZ_MIXNM_CONC_DYNAMIC_PKG.BulkUpdatePerSST('||l_from_id||', '||l_to_id||', '||l_commit_size||');');
5338 HZ_MIXNM_CONC_DYNAMIC_PKG.BulkUpdatePersonSST(l_from_id, l_to_id, l_commit_size);
5339 END IF;
5340 END IF;
5341 END IF;
5342
5343 retcode := 0;
5344
5345 EXCEPTION
5346 WHEN OTHERS THEN
5347 ROLLBACK to conc_sub;
5348 retcode := 2;
5349 errbuf := SQLERRM;
5350
5351 END conc_sub;
5352
5353 -- SSM SST Integration and Extension
5354
5355 Procedure populateMRRExc(
5356 p_entity_name IN VARCHAR2,
5357 p_data_source_type IN VARCHAR2,
5358 p_party_id IN NUMBER
5359 )IS
5360
5361 cursor mmr_attributes IS
5362 select e.entity_attr_id, s.content_source_type
5363 from hz_entity_attributes e, hz_select_data_sources s
5364 where s.ranking = -1
5365 AND e.entity_name = p_entity_name
5366 AND e.entity_attr_id = s.entity_attr_id
5367 AND (s.content_source_type= p_data_source_type or
5368 (s.content_source_type='USER_ENTERED' AND
5369 NOT EXISTS(select 'Y' from hz_select_data_sources s1
5370 where s1.ranking = -1 and s1.content_source_type = p_data_source_type
5371 and s1.entity_attr_id = e.entity_attr_id)))
5372 -- Bug 4244112 : populate only if does not exist
5373 AND NOT EXISTS(select 'Y' from hz_win_source_exceps
5374 where party_id = p_party_id
5375 and entity_attr_id = e.entity_attr_id);
5376
5377 TYPE ATTR_IDList IS TABLE OF hz_entity_attributes.entity_attr_id%TYPE;
5378 TYPE SOURCE_List IS TABLE OF hz_select_data_sources.content_source_type%TYPE;
5379 I_ATTR_ID ATTR_IDList;
5380 I_SOURCE SOURCE_List;
5381
5382 i NUMBER;
5383 Begin
5384 OPEN mmr_attributes;
5385 FETCH mmr_attributes BULK COLLECT INTO
5386 I_ATTR_ID, I_SOURCE;
5387 CLOSE mmr_attributes;
5388
5389 FORALL i IN 1..I_ATTR_ID.COUNT
5390 INSERT INTO hz_win_source_exceps (
5391 party_id,
5392 entity_attr_id,
5393 content_source_type,
5394 exception_type,
5395 created_by,
5396 creation_date,
5397 last_update_login,
5398 last_update_date,
5399 last_updated_by
5400 ) VALUES (
5401 p_party_id,
5402 I_ATTR_ID(i),
5403 I_SOURCE(i),
5404 'MRR',
5405 hz_utility_v2pub.created_by,
5406 SYSDATE,
5407 hz_utility_v2pub.last_update_login,
5408 SYSDATE,
5409 hz_utility_v2pub.last_updated_by );
5410 End populateMRRExc;
5411
5412 Function getUserRestriction(
5413 p_entity_attr_id IN NUMBER
5414 ) Return VARCHAR2 IS
5415
5416 cursor update_allowed is
5417 select v.orig_system_name
5418 from hz_thirdparty_rule t, hz_orig_systems_vl v
5419 where t.entity_attr_id = p_entity_attr_id
5420 and t.orig_system = v.orig_system
5421 and t.overwrite_flag = 'Y';
5422
5423 -- Bug 4171892
5424 l_str VARCHAR2(355);
5425 l_len NUMBER;
5426 begin
5427 l_str := '';
5428 for content_source in update_allowed loop
5429 IF LENGTHB(l_str) > 225 THEN
5430 l_str := l_str || '....';
5431 EXIT;
5432 ELSE
5433 l_str := l_str || content_source.orig_system_name;
5434 l_str := l_str || ', ';
5435 END IF;
5436 end loop;
5437
5438 l_len := LENGTHB(l_str);
5439 IF l_len > 1 THEN
5440 l_str := SUBSTRB(l_str,1,l_len-2);
5441 END IF;
5442 return l_str;
5443 end;
5444
5445
5446 Function getUserOverwrite(
5447 p_entity_attr_id IN NUMBER,
5448 p_rule_id IN NUMBER
5449 ) Return VARCHAR2 IS
5450
5451 cursor overwrite_allowed is
5452 select v.orig_system_name
5453 from hz_user_overwrite_rules u, hz_orig_systems_vl v
5454 where u.entity_attr_id = p_entity_attr_id
5455 and u.orig_system = v.orig_system
5456 and u.overwrite_flag = 'Y'
5457 and u.rule_id = p_rule_id;
5458
5459 l_str VARCHAR2(350);
5460 l_len NUMBER;
5461 begin
5462 l_str := '';
5463 for content_source in overwrite_allowed loop
5464 IF LENGTHB(l_str) > 225 THEN
5465 l_str := l_str || '....';
5466 EXIT;
5467 ELSE
5468 l_str := l_str || content_source.orig_system_name;
5469 l_str := l_str || ', ';
5470 END IF;
5471 end loop;
5472 l_len := LENGTHB(l_str);
5473 IF l_len > 1 THEN
5474 l_str := SUBSTRB(l_str,1,l_len-2);
5475 END IF;
5476 return l_str;
5477 end;
5478
5479 Function getGroupMeaningList(
5480 p_entity IN VARCHAR2,
5481 p_group IN VARCHAR2
5482 ) Return VARCHAR2 IS
5483
5484 cursor meaning is
5485 select attribute_name
5486 from hz_entity_attributes
5487 where attribute_group_name = p_group
5488 and entity_name = p_entity;
5489
5490 l_str VARCHAR2(1000);
5491 l_len NUMBER;
5492 begin
5493 l_str := '';
5494 for attributes in meaning loop
5495 l_str := l_str || hz_utility_v2pub.Get_LookupMeaning('AR_LOOKUPS',p_entity, attributes.attribute_name);
5496 l_str := l_str || ' / ';
5497 end loop;
5498 l_len := LENGTHB(l_str);
5499 IF l_len > 1 THEN
5500 l_str := SUBSTRB(l_str,1,l_len-3);
5501 END IF;
5502 return l_str;
5503 end;
5504
5505 END HZ_MIXNM_UTILITY;