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