[Home] [Help]
PACKAGE BODY: APPS.HZ_MIXNM_DYNAMIC_PKG_GENERATOR
Source
1 PACKAGE BODY HZ_MIXNM_DYNAMIC_PKG_GENERATOR AS
2 /*$Header: ARHXGENB.pls 120.39.12020000.3 2013/04/10 12:28:31 rgokavar ship $ */
3
4 --------------------------------------
5 -- declaration of private global varibles
6 --------------------------------------
7
8 TYPE INDEXVARCHAR30List IS TABLE OF VARCHAR2(30) INDEX BY BINARY_INTEGER;
9 TYPE INDEXVARCHAR60List IS TABLE OF VARCHAR2(60) INDEX BY BINARY_INTEGER;
10 TYPE NUMBERList IS TABLE OF NUMBER;
11 TYPE INDEXVARCHAR1List IS TABLE OF VARCHAR2(1) INDEX BY BINARY_INTEGER;
12 TYPE INDEXIDList IS TABLE OF NUMBER(15) INDEX BY BINARY_INTEGER;
13
14 G_O_ALL_ATTRIBUTE_NAMES INDEXVARCHAR30List;
15 G_O_DATA_TYPE INDEXVARCHAR30List;
16 G_O_DATA_LENGTH NUMBERList;
17 G_O_DE_COLUMN_NAMES INDEXVARCHAR30List;
18 G_P_ALL_ATTRIBUTE_NAMES INDEXVARCHAR30List;
19 G_P_DATA_TYPE INDEXVARCHAR30List;
20 G_P_DATA_LENGTH NUMBERList;
21 G_P_DE_COLUMN_NAMES INDEXVARCHAR30List;
22 G_MAX_LENGTH NUMBER := 400;
23 G_SETUP_LOADED VARCHAR2(1) := 'N';
24
25 G_ATTRIBUTE_GROUP_NAME_TAB INDEXVARCHAR30List;
26 G_ATTRIBUTE_NAME_TAB INDEXVARCHAR30List;
27 G_ATTRIBUTE_GROUP_ISTART_TAB INDEXIDList;
28 G_ATTRIBUTE_GROUP_IEND_TAB INDEXIDList;
29 G_ATTRIBUTE_GROUP_ID_TAB INDEXIDList;
30
31 G_O_WINNER_ATTRIBUTE_NAMES INDEXVARCHAR30List;
32 G_O_WINNER_DATA_SRC INDEXVARCHAR30List;
33 G_P_WINNER_ATTRIBUTE_NAMES INDEXVARCHAR30List;
34 G_P_WINNER_DATA_SRC INDEXVARCHAR30List;
35
36 g_indent CONSTANT VARCHAR2(10) := ' ';
37 g_sep CONSTANT VARCHAR2(2) := '%#';
38 g_to_char CONSTANT VARCHAR2(10) := 'TO_CHAR(';
39 g_to_number CONSTANT VARCHAR2(10) := 'TO_NUMBER(';
40 g_to_date CONSTANT VARCHAR2(10) := 'TO_DATE(';
41
42 --------------------------------------
43 -- private procedures and functions
44 --------------------------------------
45
46 /**
47 * PRIVATE PROCEDURE l, li, ll, lli, fp
48 *
49 * DESCRIPTION
50 * Utilities to write line or format line.
51 *
52 * EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
53 *
54 * ARGUMENTS
55 * IN:
56 * p_package_name Package name.
57 *
58 * NOTES
59 *
60 * MODIFICATION HISTORY
61 *
62 * 05-01-2002 Jianying Huang o Created
63 */
64
65 PROCEDURE l(
66 str IN VARCHAR2
67 ) IS
68 BEGIN
69 HZ_GEN_PLSQL.add_line(str);
70 -- FND_FILE.PUT_LINE(FND_FILE.LOG,str);
71 END l;
72
73 PROCEDURE li(
74 str IN VARCHAR2
75 ) IS
76 BEGIN
77 HZ_GEN_PLSQL.add_line(g_indent||str);
78 -- FND_FILE.PUT_LINE(FND_FILE.LOG,g_indent||str);
79 END li;
80
81 PROCEDURE ll(
82 str IN VARCHAR2
83 ) IS
84 BEGIN
85 HZ_GEN_PLSQL.add_line(str, false);
86 -- FND_FILE.PUT(FND_FILE.LOG,str);
87 END ll;
88
89 PROCEDURE lli(
90 str IN VARCHAR2
91 ) IS
92 BEGIN
93 HZ_GEN_PLSQL.add_line(g_indent||str, false);
94 -- FND_FILE.PUT(FND_FILE.LOG,g_indent||str);
95 END lli;
96
97 FUNCTION fp(
98 p_parameter IN VARCHAR2
99 ) RETURN VARCHAR2 IS
100 BEGIN
101 RETURN RPAD(p_parameter,35);
102 END fp;
103
104 /**
105 * PRIVATE PROCEDURE
106 *
107 * DESCRIPTION
108 * Formating Orig System name in
109 * Aliases of dynamic queries.
110 *
111 * EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
112 *
113 * ARGUMENTS
114 * IN:
115 * p_actual_content_source Actual Content Source / Orig System.
116 *
117 * RETURN:
118 * l_frmt_actual_content_source Formated Actual Content Source
119 * NOTES
120 *
121 * MODIFICATION HISTORY
122 *
123 * 25-03-2013 Sudhir Gokavarapu o Created
124 * Format code is added for 3 special '.','-',':'
125 * ':' will be replaced by 'COLON'
126 * '.' will be replaced by 'PERIOD'
127 * '-' will be replaced by 'HYPEN'
128 * As per customer requirement added these 3 characters
129 *
130 */
131
132 FUNCTION frmt_actual_content_source(
133 p_actual_content_source IN VARCHAR2
134 ) RETURN VARCHAR2 IS
135 l_frmt_actual_content_source VARCHAR2(100);
136 BEGIN
137 l_frmt_actual_content_source := p_actual_content_source;
138
139 SELECT REPLACE(l_frmt_actual_content_source,':','_COLON_')
140 INTO l_frmt_actual_content_source
141 FROM DUAL;
142
143 SELECT REPLACE(l_frmt_actual_content_source,'.','_PERIOD_')
144 INTO l_frmt_actual_content_source
145 FROM DUAL;
146
147 SELECT REPLACE(l_frmt_actual_content_source,'-','_HYPHEN_')
148 INTO l_frmt_actual_content_source
149 FROM DUAL;
150
151 --Alias cannot be more than 30 characters (including d_ ).
152 --Trucating the formated value and Concatenating with rownum at the end.
153 IF length(l_frmt_actual_content_source) > 28 THEN
154
155 SELECT substr(l_frmt_actual_content_source,1,25)||substr(rn,1,3)
156 INTO l_frmt_actual_content_source
157 FROM
158 (SELECT ROWNUM rn,orig_system os FROM hz_orig_systems_b ) a
159 WHERE os = p_actual_content_source;
160 END IF;
161
162 RETURN l_frmt_actual_content_source;
163 END frmt_actual_content_source;
164
165
166 PROCEDURE getBulkCreateWhereCondition
167 IS
168 BEGIN
169
170 li(' WHERE d_user_entered.'||'party_id BETWEEN p_from_party_id AND p_to_party_id');
171 li(' AND d_user_entered.actual_content_source = ''SST''');
172 li(' AND d_user_entered.effective_end_date IS NULL');
173
174 END getBulkCreateWhereCondition;
175
176 -- where clause for import data load during create
177 PROCEDURE getImportCreateWhereCondition
178 IS
179 BEGIN
180 li(' WHERE d_user_entered.'||'party_id = sg.party_id ');
181 li(' AND sg.int_row_id = i.rowid ');
182 li(' AND i.interface_status is null ');
183 li(' AND i.batch_id = P_BATCH_ID ');
184 li(' AND i.party_orig_system = P_WU_OS ');
185 li(' AND i.party_orig_system_reference between P_FROM_OSR and P_TO_OSR ');
186 li(' AND d_user_entered.actual_content_source = ''SST''');
187 li(' AND d_user_entered.effective_end_date IS NULL');
188
189 END getImportCreateWhereCondition;
190
191 PROCEDURE getBulkUpdateWhereCondition
192 IS
193 BEGIN
194
195 li(' WHERE sst.'||'party_id BETWEEN p_from_party_id AND p_to_party_id');
196 li(' AND sst.actual_content_source = ''SST''');
197 li(' AND sst.effective_end_date IS NULL');
198
199
200 END getBulkUpdateWhereCondition;
201
202 -- where clause for import data load during update
203 PROCEDURE getImportUpdateWhereCondition
204 IS
205 BEGIN
206
207 li(' WHERE sst.'||'party_id = sg.party_id ');
208 li(' AND sg.int_row_id = i.rowid ');
209 li(' AND i.interface_status is null ');
210 li(' AND i.batch_id = P_BATCH_ID ');
211 li(' AND i.party_orig_system = P_WU_OS ');
212 li(' AND i.party_orig_system_reference between P_FROM_OSR and P_TO_OSR ');
213 li(' AND sst.actual_content_source = ''SST''');
214 li(' AND sst.effective_end_date IS NULL');
215
216 END getImportUpdateWhereCondition;
217
218 /**
219 * PRIVATE PROCEDURE Gen_PackageHeader
220 *
221 * DESCRIPTION
222 * Generate package header.
223 *
224 * EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
225 *
226 * ARGUMENTS
227 * IN:
228 * p_package_name Package name.
229 *
230 * NOTES
231 *
232 * MODIFICATION HISTORY
233 *
234 * 05-01-2002 Jianying Huang o Created
235 */
236
237 PROCEDURE Gen_PackageHeader (
238 p_package_name IN VARCHAR2
239 ) IS
240 BEGIN
241 l('CREATE OR REPLACE PACKAGE BODY '||p_package_name||' AS');
242 l('');
243 l('/*=======================================================================+');
244 l(' | Copyright (c) 2002 Oracle Corporation Redwood Shores, California, USA|');
245 l(' | All rights reserved. |');
246 l(' +=======================================================================+');
247 l(' | NAME '||p_package_name);
248 l(' |');
249 l(' | DESCRIPTION');
250 l(' | This package body is generated by TCA mix-n-match project. ');
251 l(' |');
252 l(' | HISTORY');
253 l(' | '||TO_CHAR(SYSDATE,'MM/DD/YYYY HH:MI:SS')||' Generated.');
254 l(' |');
255 l(' *=======================================================================*/');
256 l('');
257 END Gen_PackageHeader;
258
259 /**
260 * PRIVATE PROCEDURE Gen_PackageTail
261 *
262 * DESCRIPTION
263 * Generate package tail.
264 *
265 * EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
266 *
267 * ARGUMENTS
268 * IN:
269 * p_package_name Package name.
270 *
271 * NOTES
272 *
273 * MODIFICATION HISTORY
274 *
275 * 05-01-2002 Jianying Huang o Created
276 */
277
278 PROCEDURE Gen_PackageTail (
279 p_package_name IN VARCHAR2
280 ) IS
281 BEGIN
282 l('END '||p_package_name||';');
283 END Gen_PackageTail;
284
285 /**
286 * PRIVATE PROCEDURE Get_Index
287 *
288 * DESCRIPTION
289 * Return the index of a name in a name list.
290 *
291 * EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
292 *
293 * IN:
294 * p_list Name list.
295 * p_name Name you want to search.
296 *
297 * MODIFICATION HISTORY
298 *
299 * 04-30-2002 Jianying Huang o Created.
300 */
301
302 FUNCTION Get_Index (
303 p_list IN INDEXVARCHAR30List,
304 p_name IN VARCHAR2
305 ) RETURN NUMBER IS
306
307 l_start NUMBER;
308 l_end NUMBER;
309 l_middle NUMBER;
310
311 BEGIN
312
313 l_start := 1; l_end := p_list.COUNT;
314
315 WHILE l_start <= l_end LOOP
316 l_middle := ROUND((l_end+l_start)/2);
317
318 IF p_name = p_list(l_middle) THEN
319 RETURN l_middle;
320 ELSIF p_name > p_list(l_middle) THEN
321 l_start := l_middle+1;
322 ELSE
323 l_end := l_middle-1;
324 END IF;
325 END LOOP;
326
327 RETURN 0;
328
329 END Get_Index;
330
331 /**
332 * PRIVATE PROCEDURE Load_AllAttrAndGroup
333 *
334 * DESCRIPTION
335 * Generate package tail.
336 *
337 * EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
338 *
339 * ARGUMENTS
340 * IN:
341 * p_entity_name Package name.
342 *
343 * NOTES
344 *
345 * MODIFICATION HISTORY
346 *
347 * 05-01-2002 Jianying Huang o Created
348 */
349
350 PROCEDURE Load_AllAttrAndGroup IS
351 l_status VARCHAR2(255);
352 l_owner1 VARCHAR2(255);
353 l_temp VARCHAR2(255);
354
355 CURSOR c_all_attributes (
356 p_entity_name VARCHAR2,
357 l_apps_schema VARCHAR2,
358 l_ar_schema VARCHAR2
359 ) IS
360 -- Bug 4697840
361 select aa.argument_name, aa.data_type, aa.data_length, party.column_name
362 from sys.all_arguments aa, (
363 select min(a.sequence) id
364 from sys.all_arguments a
365 where a.object_name = 'GET_' ||upper (p_entity_name)||'_REC'
366 and a.type_subname = upper (p_entity_name) || '_REC_TYPE'
367 and a.data_level = 0
368 and a.object_id in (
369 select b.object_id
370 from sys.all_objects b
371 where b.object_name = 'HZ_PARTY_V2PUB'
372 and b.owner = l_apps_schema
373 and b.object_type = 'PACKAGE')) temp1, (
374 --Bug 15893080 - Remove direct access from all_tab_columns
375 /*
376 select column_name
377 from sys.all_tab_columns c
378 where c.table_name = 'HZ_PARTIES'
379 and c.owner = l_ar_schema
380 and exists (
381 select null
382 from sys.all_tab_columns c2
383 where c2.owner = l_ar_schema
387 and c.column_name not like 'GLOBAL_ATTRIBUTE%'
384 and c2.column_name = c.column_name
385 and c2.table_name = 'HZ_' ||upper (p_entity_name) || '_PROFILES')
386 and c.column_name not like 'ATTRIBUTE%'
388 and c.column_name not in ('APPLICATION_ID')
389 */
390 select col.column_name
391 from user_synonyms syn
392 , dba_tab_columns col
393 where syn.synonym_name = 'HZ_PARTIES'
394 and col.owner = syn.table_owner
395 and col.table_name = syn.table_name
396 and exists (
397 select null
398 from user_synonyms syn
399 , dba_tab_columns col2
400 where col2.owner = syn.table_owner
401 and col2.table_name = syn.table_name
402 and col.column_name = col2.column_name
403 and syn.synonym_name = 'HZ_' ||upper (p_entity_name) || '_PROFILES')
404 and col.column_name not like 'ATTRIBUTE%'
405 and col.column_name not like 'GLOBAL_ATTRIBUTE%'
406 and col.column_name not in ('APPLICATION_ID')
407 ) party
408 where aa.object_name = 'GET_' ||upper (p_entity_name)||'_REC'
409 and aa.data_level = 1
410 and aa.data_type <> 'PL/SQL RECORD'
411 and aa.argument_name not in ('CONTENT_SOURCE_TYPE',
412 'ACTUAL_CONTENT_SOURCE', 'APPLICATION_ID')
413 and aa.sequence > temp1.id
414 and aa.object_id in (
415 select b.object_id
416 from sys.all_objects b
417 where b.object_name = 'HZ_PARTY_V2PUB'
418 and b.owner = l_apps_schema
419 and b.object_type = 'PACKAGE')
420 and aa.argument_name = party.column_name (+)
421 union all
422 select 'BANK_CODE' argument_name, 'VARCHAR2' data_type,
423 30 data_length, null column_name
424 from dual
425 union all
426 select 'BANK_OR_BRANCH_NUMBER' argument_name, 'VARCHAR2' data_type,
427 60 data_length, null column_name
428 from dual
429 union all
430 select 'BRANCH_CODE' argument_name, 'VARCHAR2' data_type,
431 30 data_length, null column_name
432 from dual
433 order by argument_name;
434
435 l_entity_name VARCHAR2(30);
436
437 CURSOR c_group IS
438 SELECT attribute_group_name, attribute_name, entity_attr_id
439 FROM hz_entity_attributes
440 WHERE attribute_name IS NOT NULL
441 ORDER BY attribute_group_name;
442
443 l_group INDEXVARCHAR30List;
444 j NUMBER;
445 total NUMBER;
446 l_index NUMBER;
447 l_length NUMBER;
448 l_data_type VARCHAR2(30);
449 l_apps_schema VARCHAR2(255);
450 l_ar_schema VARCHAR2(255);
451 l_bool BOOLEAN;
452 BEGIN
453
454 l_apps_schema := hz_utility_v2pub.Get_AppsSchemaName;
455 l_ar_schema := hz_utility_v2pub.Get_SchemaName('AR');
456
457 IF G_SETUP_LOADED = 'Y' THEN
458 RETURN;
459 END IF;
460
461 --load attributes for organization and person
462
463 FOR i IN 1..2 LOOP
464 IF i = 1 THEN
465 l_entity_name := 'organization' ;
466 ELSE
467 l_entity_name := 'person' ;
468 END IF;
469
470 OPEN c_all_attributes(l_entity_name, l_apps_schema, l_ar_schema);
471 IF l_entity_name = 'organization' THEN
472 FETCH c_all_attributes BULK COLLECT INTO
473 G_O_ALL_ATTRIBUTE_NAMES, G_O_DATA_TYPE, G_O_DATA_LENGTH, G_O_DE_COLUMN_NAMES;
474 ELSE
475 FETCH c_all_attributes BULK COLLECT INTO
476 G_P_ALL_ATTRIBUTE_NAMES, G_P_DATA_TYPE, G_P_DATA_LENGTH, G_P_DE_COLUMN_NAMES;
477 END IF;
478 CLOSE c_all_attributes;
479
480 END LOOP;
481
482 -- load group
483
484 OPEN c_group;
485 FETCH c_group BULK COLLECT INTO l_group, G_ATTRIBUTE_NAME_TAB, G_ATTRIBUTE_GROUP_ID_TAB;
486 CLOSE c_group;
487
488 j := 0; total := 0;
489 FOR i IN 1..l_group.COUNT+1 LOOP
490 IF i = l_group.COUNT+1 OR
491 (i > 1 AND
492 l_group(i-1) <> l_group(i))
493 THEN
494
495 IF total > 1 THEN
496 j := j + 1;
497 G_ATTRIBUTE_GROUP_NAME_TAB(j) := l_group(i-1);
498 G_ATTRIBUTE_GROUP_ISTART_TAB(j) := i-total;
499 G_ATTRIBUTE_GROUP_IEND_TAB(j) := i-1;
500 END IF;
501
502 IF i = l_group.COUNT+1 THEN
503 EXIT;
504 END IF;
505
506 total := 0;
507 END IF;
508
509 l_index := Get_Index(G_O_ALL_ATTRIBUTE_NAMES, G_ATTRIBUTE_NAME_TAB(i));
510 IF l_index = 0 THEN
511 l_index := Get_Index(G_P_ALL_ATTRIBUTE_NAMES, G_ATTRIBUTE_NAME_TAB(i));
512 l_data_type := G_P_DATA_TYPE(l_index);
513 l_length := G_P_DATA_LENGTH(l_index);
514 ELSE
515 l_data_type := G_O_DATA_TYPE(l_index);
516 l_length := G_O_DATA_LENGTH(l_index);
517 END IF;
518
519 IF l_data_type = 'NUMBER' THEN
520 l_length := 50;
521 ELSIF l_data_type = 'DATE' THEN
522 l_length := 25;
523 END IF;
524 l_length := l_length+40;
525
526 IF l_length > G_MAX_LENGTH THEN
527 G_MAX_LENGTH := l_length;
528 END IF;
529
530 total := total + 1;
531 END LOOP;
532
536
533 G_SETUP_LOADED := 'Y';
534
535 END Load_AllAttrAndGroup;
537 /**
538 * PRIVATE PROCEDURE Get_NameListInAGroup
539 *
540 * DESCRIPTION
541 *
542 * EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
543 *
544 * ARGUMENTS
545 *
546 * NOTES
547 *
548 * MODIFICATION HISTORY
549 *
550 * 05-01-2002 Jianying Huang o Created
551 */
552
553 PROCEDURE Get_NameListInAGroup (
554 p_group_name IN VARCHAR2,
555 x_group_name OUT NOCOPY INDEXVARCHAR30List,
556 x_group_id OUT NOCOPY INDEXIDList
557 ) IS
558
559 l_index NUMBER;
560 j NUMBER := 0;
561
562 BEGIN
563
564 IF p_group_name IS NULL THEN
565 RETURN;
566 END IF;
567
568 l_index := Get_Index(G_ATTRIBUTE_GROUP_NAME_TAB, p_group_name);
569 IF l_index > 0 THEN
570 FOR i IN G_ATTRIBUTE_GROUP_ISTART_TAB(l_index)..G_ATTRIBUTE_GROUP_IEND_TAB(l_index) LOOP
571 j := j + 1;
572 x_group_name(j) := G_ATTRIBUTE_NAME_TAB(i);
573 x_group_id(j) := G_ATTRIBUTE_GROUP_ID_TAB(i);
574 END LOOP;
575 END IF;
576
577 END Get_NameListInAGroup;
578
579 /**
580 * PRIVATE PROCEDURE Gen_ProcedureUtilForBulk
581 *
582 * DESCRIPTION
583 *
584 * EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
585 *
586 * ARGUMENTS
587 *
588 * NOTES
589 *
590 * MODIFICATION HISTORY
591 *
592 * 05-01-2002 Jianying Huang o Created
593 * 12-05-2003 Kate shan o add code to genrate import related
594 * procedure and log
595 */
596
597 PROCEDURE Gen_ProcedureUtilForBulk (
598 p_mode IN VARCHAR2,
599 p_procedure_name IN VARCHAR2 := NULL
600 ) IS
601 BEGIN
602
603 IF p_mode = 'Header' THEN
604 l('PROCEDURE '||p_procedure_name||' (');
605 li(fp('p_from_party_id')||'IN NUMBER,');
606 li(fp('p_to_party_id')||'IN NUMBER,');
607 li(fp('p_commit_size')||'IN NUMBER');
608 l(') IS');
609 ELSIF p_mode = 'ImportHeader' THEN
610 l('PROCEDURE '||p_procedure_name||' (');
611 li(fp('p_wu_os')||'IN VARCHAR2,');
612 li(fp('p_from_osr')||'IN VARCHAR2,');
613 li(fp('p_to_osr')||'IN VARCHAR2,');
614 li(fp('p_batch_id')||'IN NUMBER,');
615 li(fp('p_request_id')||'IN NUMBER,');
616 li(fp('p_program_id')||'IN NUMBER,');
617 li(fp('p_program_application_id')||'IN NUMBER');
618 l(') IS');
619 ELSIF p_mode = 'Log' THEN
620 li('write_log(''p_from_party_id = ''||p_from_party_id);');
621 li('write_log(''p_to_party_id = ''||p_to_party_id);');
622 li('write_log(''p_commit_size = ''||p_commit_size);');
623 l('');
624 ELSIF p_mode = 'ImportLog' THEN
625 li('write_log(''p_wu_os = ''||p_wu_os);');
626 li('write_log(''p_from_osr = ''||p_from_osr);');
627 li('write_log(''p_to_osr = ''||p_to_osr);');
628 li('write_log(''p_batch_id = ''||p_batch_id);');
629 li('write_log(''p_request_id = ''||p_request_id);');
630 li('write_log(''p_program_id = ''||p_program_id);');
631 li('write_log(''p_program_application_id = ''||p_program_application_id);');
632 l('');
633 ELSIF p_mode = 'NullBody' THEN
634 l('BEGIN');
635 li('NULL;');
636 l('END '||p_procedure_name||';');
637 l('');
638 END IF;
639
640 END Gen_ProcedureUtilForBulk;
641
642 /**
643 * PRIVATE PROCEDURE Gen_CodeForCursor
644 *
645 * DESCRIPTION
646 *
647 * EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
648 *
649 * ARGUMENTS
650 *
651 * NOTES
652 *
653 * MODIFICATION HISTORY
654 *
655 * 05-01-2002 Jianying Huang o Created
656 * 12-05-2003 Kate shan o add p_purpose for differentiate
657 * import and bulk. Do not generate
658 * commit related variable during import
659 */
660
661 PROCEDURE Gen_CodeForCursor (
662 p_option IN VARCHAR2,
663 p_cursor_name IN VARCHAR2 DEFAULT NULL,
664 p_bulk_flag IN VARCHAR2 DEFAULT NULL,
665 p_purpose IN VARCHAR2 DEFAULT 'BULK'
666 ) IS
667 BEGIN
668
669 IF p_option = 'DECLARE' THEN
670
671 li(fp('rows')||'NUMBER := 500;');
672 IF UPPER(p_purpose) = 'BULK' THEN
673 li(fp('i_commit')||'NUMBER;');
674 END IF;
675 li(fp('l_last_fetch')||'BOOLEAN := FALSE;');
676 li(fp('l_profile_fmt_bkwd_compatible')||' VARCHAR2(30);');
677 li(fp('l_profile_version')||' VARCHAR2(30);');
678 l('');
679
680
681 ELSIF p_option = 'OPEN' THEN
682
683 li('-- initilize env variables.');
684 IF UPPER(p_purpose) = 'BULK' THEN
685 li('i_commit := 0;');
686 END IF;
687 li('l_last_fetch := FALSE;');
688 l('');
689 li('OPEN '||p_cursor_name||';');
690 li('LOOP');
691 lli(' FETCH '||p_cursor_name||' ');
692
693 IF p_bulk_flag = 'Y' THEN
694 l('BULK COLLECT INTO');
695 ELSE
696 l('INTO');
697 END IF;
698
699 ELSIF p_option = 'FETCH' THEN
700
701 li(' IF '||p_cursor_name||'%NOTFOUND THEN');
702 li(' l_last_fetch := TRUE;');
706
703 li(' END IF;');
704
705 ELSIF p_option = 'CLOSE' THEN
707 li(' IF l_last_fetch = TRUE THEN');
708 li(' EXIT;');
709 li(' END IF;');
710 li('END LOOP;');
711 li('CLOSE '||p_cursor_name||';');
712 IF UPPER(p_purpose) = 'BULK' THEN
713 li('COMMIT;');
714 END IF;
715
716 END IF;
717
718 END Gen_CodeForCursor;
719
720 /**
721 * PRIVATE PROCEDURE Gen_CodeForCommit
722 *
723 * DESCRIPTION
724 *
725 * EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
726 *
727 * ARGUMENTS
728 *
729 * NOTES
730 *
731 * MODIFICATION HISTORY
732 *
733 * 05-01-2002 Jianying Huang o Created
734 */
735
736 PROCEDURE Gen_CodeForCommit (
737 p_write_log IN BOOLEAN := true,
738 p_prefix IN VARCHAR2 := ' '
739 ) IS
740 BEGIN
741
742 li(p_prefix||'IF SQL%FOUND THEN');
743
744 IF p_write_log THEN
745 li(p_prefix||' write_log(SQL%ROWCOUNT||'' records have been created / updated.'');');
746 l('');
747 END IF;
748
749 li(p_prefix||' i_commit := i_commit + SQL%ROWCOUNT;');
750 li(p_prefix||' IF i_commit >= p_commit_size THEN');
751 li(p_prefix||' COMMIT;');
752 li(p_prefix||' i_commit := 0;');
753 li(p_prefix||' END IF;');
754 li(p_prefix||'END IF;');
755 l('');
756
757 END Gen_CodeForCommit;
758
759 /**
760 * PRIVATE PROCEDURE Gen_CodeForWhoColumns
761 *
762 * DESCRIPTION
763 *
764 * EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
765 *
766 * ARGUMENTS
767 *
768 * NOTES
769 *
770 * MODIFICATION HISTORY
771 *
772 * 05-01-2002 Jianying Huang o Created
773 */
774
775 PROCEDURE Gen_CodeForWhoColumns (
776 p_insert_update_flag IN VARCHAR2,
777 p_option IN VARCHAR2,
778 p_prefix IN VARCHAR2,
779 p_extended_who_flag IN VARCHAR2
780 ) IS
781 BEGIN
782
783 IF p_insert_update_flag = 'I' THEN
784 IF p_option = 'LIST' THEN
785
786 l(p_prefix||'created_by,');
787 l(p_prefix||'creation_date,');
788 l(p_prefix||'last_update_login,');
789 l(p_prefix||'last_update_date,');
790 ll(p_prefix||'last_updated_by');
791
792 IF p_extended_who_flag = 'Y' THEN
793 l(',');
794 l(p_prefix||'request_id,');
795 l(p_prefix||'program_application_id,');
796 l(p_prefix||'program_id,');
797 ll(p_prefix||'program_update_date');
798 END IF;
799
800 ELSE -- IF p_option = 'LIST' THEN
801
802 l(p_prefix||'g_created_by,');
803 l(p_prefix||'SYSDATE,');
804 l(p_prefix||'g_last_update_login,');
805 l(p_prefix||'SYSDATE,');
806 ll(p_prefix||'g_last_updated_by');
807
808 IF p_extended_who_flag = 'Y' THEN
809 l(',');
810 l(p_prefix||'g_request_id,');
811 l(p_prefix||'g_program_application_id,');
812 l(p_prefix||'g_program_id,');
813 ll(p_prefix||'SYSDATE');
814 END IF;
815
816 END IF;
817
818 ELSE -- IF p_insert_update_flag = 'I' THEN
819
820 l(p_prefix||'last_updated_by = g_last_updated_by,');
821 l(p_prefix||'last_update_login = g_last_update_login,');
822 ll(p_prefix||'last_update_date = SYSDATE');
823
824 IF p_extended_who_flag = 'Y' THEN
825 l(',');
826 l(p_prefix||'request_id = g_request_id,');
827 l(p_prefix||'program_application_id = g_program_application_id,');
828 l(p_prefix||'program_id = g_program_id,');
829 ll(p_prefix||'program_update_date = SYSDATE');
830 END IF;
831
832 END IF;
833
834 END Gen_CodeForWhoColumns;
835
836 /**
837 * PRIVATE PROCEDURE Gen_CodeForOtherAttributes
838 *
839 * DESCRIPTION
840 *
841 * EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
842 *
843 * ARGUMENTS
844 *
845 * NOTES
846 *
847 * MODIFICATION HISTORY
848 *
849 * 05-01-2002 Jianying Huang o Created
850 */
851
852 PROCEDURE Gen_CodeForOtherAttributes (
853 p_option IN VARCHAR2,
854 p_prefix IN VARCHAR2
855 ) IS
856 BEGIN
857
858 IF p_option = 'LIST' THEN
859 l(p_prefix||'content_source_type,');
860 l(p_prefix||'actual_content_source,');
861 -- l(p_prefix||'created_by_module,');
862 ll(p_prefix||'application_id');
863 ELSE
864 l(p_prefix||'''USER_ENTERED'',');
865 l(p_prefix||'''SST'',');
866 -- l(p_prefix||'''TCA-MIXNMATCH-CONCPROGRAM'',');
867 ll(p_prefix||'222');
868 END IF;
869
870 END Gen_CodeForOtherAttributes;
871
872 /**
873 * PRIVATE PROCEDURE Format_AttributeName
874 *
875 * DESCRIPTION
876 *
877 * EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
878 *
879 * ARGUMENTS
880 *
881 * NOTES
882 *
883 * MODIFICATION HISTORY
884 *
885 * 05-01-2002 Jianying Huang o Created
886 */
887
888 FUNCTION Format_AttributeName (
889 p_attribute_name IN VARCHAR2
890 ) RETURN VARCHAR2 IS
891
892 str1 VARCHAR2(30);
893
894 BEGIN
898 str1 := SUBSTRB(str1,3);
895
896 str1 := LOWER(p_attribute_name);
897 IF LENGTHB(str1) > 28 THEN
899 END IF;
900 RETURN str1;
901
902 END Format_AttributeName;
903
904 /**
905 * PRIVATE PROCEDURE Declare_AllAttributes
906 *
907 * DESCRIPTION
908 *
909 * EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
910 *
911 * ARGUMENTS
912 *
913 * NOTES
914 *
915 * MODIFICATION HISTORY
916 *
917 * 05-01-2002 Jianying Huang o Createda
918 * 17-12-2004 Dhaval Mehta SSM SST Project
919 * ~ Added parameter p_attributes_date for
920 * the list of attributes setup for
921 * SST Display method 'By Date'.
922 */
923
924 PROCEDURE Declare_Attributes (
925 p_entity_name IN VARCHAR2,
926 p_restricted_attributes IN INDEXVARCHAR30List,
927 p_attributes_date IN INDEXVARCHAR30List,
928 x_normal_attributes OUT NOCOPY INDEXVARCHAR30List
929 ) IS
930
931 l_all_attribute_names INDEXVARCHAR30List;
932 l_data_type INDEXVARCHAR30List;
933 l_data_length NUMBERList;
934 l_index NUMBER;
935 l_type VARCHAR2(30);
936 j NUMBER;
937 str1 VARCHAR2(100);
938 l_index_date NUMBER;
939
940 BEGIN
941
942 IF p_entity_name = 'organization' THEN
943 l_all_attribute_names := G_O_ALL_ATTRIBUTE_NAMES;
944 l_data_type := G_O_DATA_TYPE;
945 l_data_length := G_O_DATA_LENGTH;
946 ELSE
947 l_all_attribute_names := G_P_ALL_ATTRIBUTE_NAMES;
948 l_data_type := G_P_DATA_TYPE;
949 l_data_length := G_P_DATA_LENGTH;
950 END IF;
951
952 j := 0;
953 FOR i IN 1..l_all_attribute_names.COUNT LOOP
954 If(p_entity_name = 'organization' OR
955 (p_entity_name = 'person' AND
956 l_all_attribute_names(i) <> 'BANK_CODE' AND
957 l_all_attribute_names(i) <> 'BANK_OR_BRANCH_NUMBER' AND
958 l_all_attribute_names(i) <> 'BRANCH_CODE')) Then
959 str1 := Format_AttributeName(l_all_attribute_names(i));
960
961 l_index := Get_Index(p_restricted_attributes, l_all_attribute_names(i));
962 -- SSM SST Project : check if attribute is 'By Date' type
963 l_index_date := Get_Index(p_attributes_date, l_all_attribute_names(i));
964
965 -- SSM SST Project :
966 -- If the attribute is not 'By Rank' and not 'By Date'
967 -- it is unrestricted attribute.
968 -- Declare a table of the data type of attribute
969 -- and local variable of this table type
970 IF (l_index = 0 AND l_index_date = 0) THEN
971 j := j+1; x_normal_attributes(j) := l_all_attribute_names(i);
972
973 l_type := l_data_type(i);
974 IF l_type = 'VARCHAR2' THEN
975 l_type := 'VARCHAR2('||l_data_length(i)||')';
976 END IF;
977
978 li('TYPE t_'||str1||' IS TABLE OF '||l_type||' INDEX BY BINARY_INTEGER;');
979 li(fp('i_'||str1)||'t_'||str1||';');
980
981 ELSIF l_index_date <> 0 THEN
982 -- SSM SST Project :
983 -- it attribute is 'By Date',
984 -- Declare a table of the data type of attribute
985 -- and local variable of this table type
986 l_type := l_data_type(i);
987 IF l_type = 'VARCHAR2' THEN
988 l_type := 'VARCHAR2('||l_data_length(i)||')';
989 END IF;
990
991 -- SSM SST Project :
992 -- also declare variable to store actual_content_source
993 -- for this attribute during BULK COLLECT
994 -- i.e. knownas
995 li('TYPE t_'||str1||' IS TABLE OF '||l_type||' INDEX BY BINARY_INTEGER;');
996 li(fp('i_'||str1)||'t_'||str1||';');
997 li(fp(str1)||'INDEXVARCHARlist;');
998 ELSE
999 -- SSM SST Project :
1000 -- it attribute is 'By Rank',
1001 -- Declare a local variable of type table VARCHARlist
1002 li(fp('i_'||str1)||'INDEXVARCHARlist;');
1003 END IF;
1004 END IF;
1005 END LOOP;
1006
1007 IF p_entity_name = 'organization' THEN
1008 li('TYPE t_duns_number IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;');
1009 li(fp('i_duns_number')||'t_duns_number;');
1010 ELSE
1011 li(fp('i_person_name')||'INDEXVARCHARlist;');
1012 END IF;
1013 l('');
1014
1015 END Declare_Attributes;
1016
1017 /**
1018 * PRIVATE PROCEDURE Gen_CommonProceduresForConc
1019 *
1020 * DESCRIPTION
1021 * Generate common procedures for concurrent program.
1022 *
1023 * EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
1024 *
1025 * ARGUMENTS
1026 *
1027 * NOTES
1028 *
1029 * MODIFICATION HISTORY
1030 *
1031 * 05-01-2002 Jianying Huang o Created
1032 * 17-12-2004 Dhaval Mehta o SSM SST Integration Project
1033 * ~ Added code to generate new procedure
1034 * update_exception_table_date. This
1035 * procedure is to populate exception
1036 * table for 'By Date' attributes.
1037 */
1038
1039 PROCEDURE Gen_CommonProceduresForConc IS
1040
1041 l_procedure_name VARCHAR2(30);
1042
1043 BEGIN
1044
1045 /*===============================================================
1046 + declare global type and variables
1047 +===============================================================*/
1048
1049 l('TYPE INDEXIDlist IS TABLE OF NUMBER(15) INDEX BY BINARY_INTEGER;');
1053 l(fp('g_created_by')||'NUMBER;');
1050 l('TYPE INDEXVARCHARlist IS TABLE OF VARCHAR2('||G_MAX_LENGTH||') INDEX BY BINARY_INTEGER;');
1051 l('TYPE INDEXVARDATElist IS TABLE OF DATE INDEX BY BINARY_INTEGER;');
1052 l('');
1054 l(fp('g_last_update_login')||'NUMBER;');
1055 l(fp('g_last_updated_by')||'NUMBER;');
1056 l(fp('g_request_id')||'NUMBER;');
1057 l(fp('g_program_application_id')||'NUMBER;');
1058 l(fp('g_program_id')||'NUMBER;');
1059 l('');
1060
1061 /*===============================================================
1062 + write_log
1063 +===============================================================*/
1064 --Bug 7188240
1065 --Changed to do conditional logging based on FND DEBUG Logging profile.
1066 --Added If condition.
1067
1068 l_procedure_name := 'write_log';
1069
1070 l('PROCEDURE '||l_procedure_name||' (');
1071 li(fp('str')||'IN VARCHAR2');
1072 l(') IS');
1073 l('BEGIN');
1074 li('IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN');
1075 li('FND_FILE.PUT_LINE(FND_FILE.LOG,');
1076 li(' TO_CHAR(SYSDATE, ''YYYY/MM/DD HH:MI:SS'')||'' -- ''||str);');
1077 li('END IF;');
1078 l('END '||l_procedure_name||';');
1079 l('');
1080
1081 /*===============================================================
1082 + write_out
1083 +===============================================================*/
1084
1085 l_procedure_name := 'write_out';
1086
1087 l('PROCEDURE '||l_procedure_name||' (');
1088 li(fp('str')||'IN VARCHAR2');
1089 l(') IS');
1090 l('BEGIN');
1091 li('FND_FILE.PUT_LINE(FND_FILE.OUTPUT,str);');
1092 l('END '||l_procedure_name||';');
1093 l('');
1094
1095 /*===============================================================
1096 + reset_who
1097 +===============================================================*/
1098
1099 l_procedure_name := 'reset_who';
1100
1101 l('PROCEDURE '||l_procedure_name || ' (');
1102 li('p_request_id NUMBER := hz_utility_v2pub.request_id, ');
1103 li('p_program_id NUMBER := hz_utility_v2pub.program_id, ');
1104 li('p_program_application_id NUMBER := hz_utility_v2pub.program_application_id ');
1105 l(') IS');
1106 l('BEGIN');
1107 li('g_created_by := hz_utility_v2pub.created_by;');
1108 li('g_last_update_login := hz_utility_v2pub.last_update_login;');
1109 li('g_last_updated_by := hz_utility_v2pub.last_updated_by;');
1110 li('g_request_id := p_request_id;');
1111 li('g_program_id := p_program_id;');
1112 li('g_program_application_id := p_program_application_id;');
1113 l('END '||l_procedure_name||';');
1114 l('');
1115
1116 /*===============================================================
1117 + update_rel_party_name
1118 +===============================================================*/
1119
1120 l_procedure_name := 'update_rel_party_name';
1121
1122 l('PROCEDURE '||l_procedure_name||' (');
1123 li(fp('p_party_id')||'IN NUMBER');
1124 l(') IS');
1125 li(fp('CURSOR c_party IS'));
1126 li(' SELECT r.party_id, r.object_id, o.party_name, r.subject_id, s.party_name');
1127 li(' FROM hz_relationships r, hz_parties s, hz_parties o ');
1128 li(' WHERE (r.subject_id = p_party_id OR r.object_id = p_party_id)');
1129 li(' AND r.party_id IS NOT NULL');
1130 li(' AND r.subject_table_name = ''HZ_PARTIES''');
1131 li(' AND r.object_table_name = ''HZ_PARTIES''');
1132 li(' AND r.directional_flag = ''F''');
1133 li(' AND r.subject_id = s.party_id');
1134 li(' AND r.object_id = o.party_id;');
1135 l('');
1136 li(fp('i_party_id')||'INDEXIDlist;');
1137 li(fp('i_subject_id')||'INDEXIDlist;');
1138 li(fp('i_object_id')||'INDEXIDlist;');
1139 li('TYPE NAMElist IS TABLE OF HZ_PARTIES.PARTY_NAME%TYPE;');
1140 li(fp('i_subject_name')||'NAMElist;');
1141 li(fp('i_object_name')||'NAMElist;');
1142 l('');
1143 l('BEGIN');
1144 li('OPEN c_party;');
1145 li('FETCH c_party BULK COLLECT INTO ');
1146 li(' i_party_id, i_object_id, i_object_name, i_subject_id, i_subject_name;');
1147 li('CLOSE c_party;');
1148 li('');
1149 li('FORALL i IN 1..i_party_id.COUNT');
1150 li(' UPDATE hz_parties');
1151 li(' SET party_name = i_subject_name(i)||''-''||i_object_name(i)||''-''||party_number');
1152 li(' WHERE party_id = i_party_id(i);');
1153 l('');
1154 li('FOR i IN 1..i_party_id.COUNT LOOP');
1155 li(' '||l_procedure_name||'(i_party_id(i));');
1156 li('END LOOP;');
1157 l('END '||l_procedure_name||';');
1158 l('');
1159
1160 /*===============================================================
1161 + update_exception_table
1162 +===============================================================*/
1163
1164 l_procedure_name := 'update_exception_table';
1165
1166 l('PROCEDURE '||l_procedure_name||' (');
1167 li(fp('p_create_update_flag')||'IN VARCHAR2,');
1168 li(fp('p_i_party_id')||'IN INDEXIDlist,');
1169 li(fp('p_attribute_name')||'IN VARCHAR2,');
1170 li(fp('p_entity_attr_id')||'IN NUMBER,');
1171 li(fp('p_value_list')||'IN INDEXVARCHARlist,');
1172 li(fp('p_winner')||'IN VARCHAR2');
1173 l(') IS');
1174 li(fp('i_party_id1')||'INDEXIDlist;');
1175 li(fp('i_party_id2')||'INDEXIDlist;');
1176 li(fp('i_value_list1')||'INDEXVARCHARlist;');
1177 li(fp('i_value_list2')||'INDEXVARCHARlist;');
1178 li(fp('i_entity_attr_id')||'INDEXIDlist;');
1179 li(fp('total1')||'NUMBER := 0;');
1180 li(fp('total2')||'NUMBER := 0;');
1181 l('BEGIN');
1182 li('write_log(''processing exceptions on ''||LOWER(p_attribute_name)||''...'');');
1183 li('FOR i IN 1..p_i_party_id.COUNT LOOP');
1187 li(' total1 := total1 + 1;');
1184 li(' IF p_value_list(i) IS NOT NULL AND');
1185 li(' INSTRB(p_value_list(i), '''||g_sep||'''||p_winner) = 0');
1186 li(' THEN');
1188 li(' i_party_id1(total1) := p_i_party_id(i);');
1189 li(' i_value_list1(total1) := p_value_list(i);');
1190 li(' END IF;');
1191 li('END LOOP;');
1192 l('');
1193 li('IF p_create_update_flag = ''C'' THEN');
1194 li(' FORALL i IN 1..total1');
1195 li(' INSERT INTO hz_win_source_exceps (');
1196 li(' party_id, ');
1197 li(' entity_attr_id, ');
1198 li(' content_source_type,');
1199 li(' exception_type,');
1200 Gen_CodeForWhoColumns('I','LIST',g_indent||' ','Y');
1201 l('');
1202 li(' ) VALUES (');
1203 li(' i_party_id1(i),');
1204 li(' p_entity_attr_id,');
1205 li(' trim(SUBSTRB(i_value_list1(i),INSTRB(i_value_list1(i), '''||
1206 g_sep||''')+2)),');
1207 li(' ''Migration'',');
1208 Gen_CodeForWhoColumns('I','VALUE',g_indent||' ','Y');
1209 l('');
1210 li(' );');
1211 l('');
1212 li(' IF total1 > 0 THEN');
1213 li(' write_log(''created ''||sql%ROWCOUNT||'' exceptions.'');');
1214 li(' END IF;');
1215 l('');
1216 li('ELSE');
1217 li(' FOR i IN 1..p_i_party_id.COUNT LOOP');
1218 li(' IF p_value_list(i) IS NULL OR ');
1219 li(' (p_value_list(i) IS NOT NULL AND');
1220 li(' INSTRB(p_value_list(i), '''||g_sep||'''||p_winner) > 0)');
1221 li(' THEN');
1222 li(' total2 := total2 + 1;');
1223 li(' i_party_id2(total2) := p_i_party_id(i);');
1224 li(' i_value_list2(total2) := p_value_list(i);');
1225 li(' END IF;');
1226 li(' END LOOP;');
1227 l('');
1228 li(' FORALL i IN 1..total2');
1229 li(' DELETE hz_win_source_exceps');
1230 li(' WHERE party_id = i_party_id2(i)');
1231 li(' AND entity_attr_id = p_entity_attr_id;');
1232 l('');
1233 li(' IF total2 > 0 THEN');
1234 li(' write_log(''Deleted ''||sql%ROWCOUNT||'' exceptions.'');');
1235 li(' END IF;');
1236 l('');
1237 li(' FORALL i IN 1..total1');
1238 li(' UPDATE hz_win_source_exceps');
1239 li(' SET content_source_type = '||
1240 'trim(SUBSTRB(i_value_list1(i),INSTRB(i_value_list1(i), '''||g_sep||''')+2)),');
1241 li(' exception_type = ''Migration'',');
1242 Gen_CodeForWhoColumns('U','',g_indent||' ','Y');
1243 l('');
1244 li(' WHERE party_id = i_party_id1(i)');
1245 li(' AND entity_attr_id = p_entity_attr_id;');
1246 l('');
1247 li(' IF total1 > 0 THEN');
1248 li(' write_log(''Updated ''||sql%ROWCOUNT||'' exceptions.'');');
1249 li(' END IF;');
1250 l('');
1251 li(' FORALL i IN 1..total1');
1252 li(' INSERT INTO hz_win_source_exceps (');
1253 li(' party_id, ');
1254 li(' entity_attr_id,');
1255 li(' content_source_type,');
1256 li(' exception_type,');
1257 Gen_CodeForWhoColumns('I','LIST',g_indent||' ','Y');
1258 l('');
1259 li(' )');
1260 li(' SELECT');
1261 li(' i_party_id1(i),');
1262 li(' p_entity_attr_id,');
1263 li(' trim(SUBSTRB(i_value_list1(i),INSTRB(i_value_list1(i), '''||
1264 g_sep||''')+2)),');
1265 li(' ''Migration'',');
1266 Gen_CodeForWhoColumns('I','VALUE',g_indent||' ','Y');
1267 l('');
1268 li(' FROM dual');
1269 li(' WHERE NOT EXISTS (');
1270 li(' SELECT ''Y''');
1271 li(' FROM hz_win_source_exceps');
1272 li(' WHERE party_id = i_party_id1(i)');
1273 li(' AND entity_attr_id = p_entity_attr_id );');
1274 l('');
1275 li(' IF total1 > 0 THEN');
1276 li(' write_log(''Created ''||sql%ROWCOUNT||'' exceptions.'');');
1277 li(' END IF;');
1278 li('END IF;');
1279 l('END '||l_procedure_name||';');
1280 l('');
1281
1282 /*===============================================================
1283 + SSM SST Project : update_exception_table for Date Attributes
1284 +===============================================================*/
1285
1286 l_procedure_name := 'update_exception_table_date';
1287
1288 l('PROCEDURE '||l_procedure_name||' (');
1289 li(fp('p_create_update_flag')||'IN VARCHAR2,');
1290 li(fp('p_i_party_id')||'IN INDEXIDlist,');
1291 li(fp('p_attribute_name')||'IN VARCHAR2,');
1292 li(fp('p_entity_attr_id')||'IN NUMBER,');
1293 li(fp('p_winner')||'IN INDEXVARCHARlist');
1294 l(') IS');
1295 li(fp('total1')||'NUMBER := 0;');
1296 l('BEGIN');
1297 li('write_log(''processing exceptions on ''||LOWER(p_attribute_name)||''...'');');
1298 li(' total1 := p_i_party_id.COUNT;');
1299 l('');
1300 li('IF p_create_update_flag = ''C'' THEN');
1301 li(' FORALL i IN 1..total1');
1302 li(' INSERT INTO hz_win_source_exceps (');
1303 li(' party_id, ');
1304 li(' entity_attr_id, ');
1305 li(' content_source_type,');
1306 li(' exception_type,');
1307 Gen_CodeForWhoColumns('I','LIST',g_indent||' ','Y');
1308 l('');
1309 li(' ) VALUES (');
1310 li(' p_i_party_id(i),');
1311 li(' p_entity_attr_id,');
1312 li(' p_winner(i),');
1313 li(' ''MRR'',');
1314 Gen_CodeForWhoColumns('I','VALUE',g_indent||' ','Y');
1315 l('');
1316 li(' );');
1317 l('');
1318 li(' IF total1 > 0 THEN');
1319 li(' write_log(''created ''||sql%ROWCOUNT||'' exceptions.'');');
1320 li(' END IF;');
1321 l('');
1322 li('ELSE');
1323 l('');
1324 li(' FOR i IN 1..total1 loop');
1328 Gen_CodeForWhoColumns('U','',g_indent||' ','Y');
1325 li(' UPDATE hz_win_source_exceps');
1326 li(' SET content_source_type = p_winner(i),');
1327 li(' exception_type = ''MRR'',');
1329 l('');
1330 li(' WHERE party_id = p_i_party_id(i)');
1331 li(' AND entity_attr_id = p_entity_attr_id;');
1332 l('');
1333 li(' IF sql%NOTFOUND THEN');
1334 li(' INSERT INTO hz_win_source_exceps (');
1335 li(' party_id, ');
1336 li(' entity_attr_id, ');
1337 li(' content_source_type,');
1338 li(' exception_type,');
1339 Gen_CodeForWhoColumns('I','LIST',g_indent||' ','Y');
1340 l('');
1341 li(' ) VALUES (');
1342 li(' p_i_party_id(i),');
1343 li(' p_entity_attr_id,');
1344 li(' p_winner(i),');
1345 li(' ''MRR'',');
1346 Gen_CodeForWhoColumns('I','VALUE',g_indent||' ','Y');
1347 l('');
1348 li(' );');
1349 l('');
1350 li(' END IF;');
1351 li(' end loop;');
1352 li(' IF total1 > 0 THEN');
1353 li(' write_log(''Updated ''||sql%ROWCOUNT||'' exceptions.'');');
1354 li(' END IF;');
1355 l('');
1356 li('END IF;');
1357 l('END '||l_procedure_name||';');
1358 l('');
1359
1360 END Gen_CommonProceduresForConc;
1361
1362 /**
1363 * PRIVATE PROCEDURE
1364 *
1365 * DESCRIPTION
1366 *
1367 * MODIFICATION HISTORY
1368 *
1369 */
1370 PROCEDURE Gen_PartyName (
1371 p_entity_name IN VARCHAR2
1372 ) IS
1373 BEGIN
1374
1375 l(',');
1376 IF p_entity_name = 'organization' THEN
1377 li(' party_name = ');
1378 li(' SUBSTRB(i_organization_name(i),1,'||
1379 'INSTRB(i_organization_name(i),'''||g_sep||''')-1),');
1380 li(' customer_key = ');
1381 li(' DECODE(party_name,');
1382 li(' SUBSTRB(i_organization_name(i),1,'||
1383 'INSTRB(i_organization_name(i),'''||g_sep||''')-1),');
1384 li(' customer_key,');
1385 li(' hz_fuzzy_pub.generate_key (');
1386 li(' ''ORGANIZATION'',');
1387 li(' SUBSTRB(i_organization_name(i),1,'||
1388 'INSTRB(i_organization_name(i),'''||g_sep||''')-1)))');
1389 ELSE
1390 li(' party_name = ');
1391 li(' DECODE(trim(party_name), ');
1392 li(' trim(person_first_name||'' ''||person_last_name),');
1393 li(' trim(SUBSTRB(i_person_first_name(i),1,'||
1394 'INSTRB(i_person_first_name(i),'''||g_sep||''')-1)||'' ''||');
1395 li(' SUBSTRB(i_person_last_name(i),1,'||
1396 'INSTRB(i_person_last_name(i),'''||g_sep||''')-1)),');
1397 li(' SUBSTRB(SUBSTRB(i_person_name(i),1,'||
1398 'INSTRB(i_person_name(i),'''||g_sep||''')-1),1,360)),');
1399 li(' customer_key = ');
1400 li(' DECODE(person_first_name,');
1401 li(' SUBSTRB(i_person_first_name(i),1,'||
1402 'INSTRB(i_person_first_name(i),'''||g_sep||''')-1),');
1403 li(' DECODE(person_last_name,');
1404 li(' SUBSTRB(i_person_last_name(i),1,'||
1405 'INSTRB(i_person_last_name(i),'''||g_sep||''')-1),');
1406 li(' customer_key,');
1407 li(' hz_fuzzy_pub.generate_key (');
1408 li(' ''PERSON'',null,null,null,null,null,null,');
1409 li(' SUBSTRB(i_person_first_name(i),1,'||
1410 'INSTRB(i_person_first_name(i),'''||g_sep||''')-1),');
1411 li(' SUBSTRB(i_person_last_name(i),1,'||
1412 'INSTRB(i_person_last_name(i),'''||g_sep||''')-1))))');
1413 END IF;
1414
1415 END Gen_PartyName;
1416
1417 PROCEDURE Gen_RelationshipPartyName IS
1418 BEGIN
1419
1420 /*
1421 li(' write_log(''update party name of relationship parties.'');');
1422 l('');
1423 li(' FOR i IN 1..subtotal LOOP');
1424 li(' update_rel_party_name(i_party_id(i));');
1425 li(' END LOOP;');
1426 */
1427 l('');
1428 -- Gen_CodeForCommit(false);
1429
1430 END Gen_RelationshipPartyName;
1431
1432 /**
1433 * PRIVATE PROCEDURE
1434 *
1435 * DESCRIPTION
1436 *
1437 * MODIFICATION HISTORY
1438 *
1439 */
1440
1441 PROCEDURE Gen_WriteOutputFileForCreate IS
1442 BEGIN
1443
1444 li(' FOR i IN 1..i_sst_profile_id.COUNT LOOP');
1445 li(' write_out(''party id = ''||i_party_id(i)||'', sst profile id = ''||i_sst_profile_id(i));');
1446 li(' END LOOP;');
1447 l('');
1448
1449 END Gen_WriteOutputFileForCreate;
1450
1451 PROCEDURE Gen_WriteOutputFileForUpdate IS
1452 BEGIN
1453
1454 li(' FOR i IN create_start..create_end LOOP');
1455 li(' write_out(''created : party id = ''||i_party_id(i)||'', sst profile id = ''||i_sst_profile_id(i));');
1456 li(' END LOOP;');
1457 l('');
1458 li(' FOR i IN update_start..update_end LOOP');
1459 li(' write_out(''updated : party id = ''||i_party_id(i)||'', sst profile id = ''||i_profile_id(i));');
1460 li(' END LOOP;');
1461 l('');
1462
1463 END Gen_WriteOutputFileForUpdate;
1464
1465
1466 /**
1467 * PRIVATE PROCEDURE date_cursor_create
1468 *
1469 * DESCRIPTION
1470 * This procedure is used to generate cursor for
1471 * passed attribute_id setup as 'By Date' for
1472 * BulkCreateSST and ImportCreateSST procedures.
1473 * Select the attribute and corresponding actual_content_source
1474 * from the last updated profile among the visible data
1475 * sources for this attribute.
1476 *
1477 CURSOR c_KNOWN_AS5 IS
1481 ue.known_as5 , ue.actual_content_source
1478 SELECT user_entered.known_as5, user_entered.actual_content_source
1479 FROM ( SELECT row_number() over
1480 (partition by ue.party_id order by ue.last_update_date desc nulls last) rank,
1482 FROM hz_organization_profiles ue
1483 WHERE ue.party_id BETWEEN p_from_party_id AND p_to_party_id
1484 AND ue.effective_end_date IS NULL
1485 AND ue.actual_content_source in ( 'DNB' ,'USER_ENTERED' ...)
1486 AND NOT EXISTS (
1487 USER_ENTERED profile
1488 AND EXISTS (
1489 NON SST, USER_ENTERED profile
1490 ) user_entered
1491 WHERE user_entered.rank = 1
1492
1493 * MODIFICATION HISTORY
1494 *
1495 * 17-12-2004 Dhaval Metha SSM SST Project
1496 * ~ Created.
1497 */
1498 procedure date_cursor_create(attr_id IN NUMBER,
1499 attr_name IN VARCHAR2,
1500 p_entity_name IN VARCHAR2,
1501 p_purpose IN VARCHAR2) IS
1502 cursor data_sources is
1503 select content_source_type
1504 from hz_select_data_sources
1505 where entity_attr_id = attr_id
1506 and ranking <> 0;
1507
1508 i_content_source_type INDEXVARCHAR30List;
1509 l_cursor_name_date VARCHAR2(30);
1510 l_prefix VARCHAR2(30);
1511
1512 Begin
1513 OPEN data_sources;
1514 FETCH data_sources BULK COLLECT INTO i_content_source_type;
1515 CLOSE data_sources;
1516
1517 l_cursor_name_date := attr_name||'_';
1518 l_prefix := RPAD(g_indent,9+LENGTHB(g_indent));
1519
1520 li('CURSOR '||l_cursor_name_date||' IS');
1521 li(' SELECT ');
1522
1523 l_prefix := RPAD(g_indent,9+LENGTHB(g_indent));
1524 ll(l_prefix||'user_entered.'||LOWER(attr_name));
1525 l(',');
1526 ll(l_prefix||'user_entered.actual_content_source');
1527
1528 l('');
1529 li(' FROM');
1530 li('( SELECT ');
1531 li(' row_number() over ');
1532 ll(' (partition by ue.party_id order by ue.last_update_date desc nulls last) rank, ');
1533 li(l_prefix||'ue.'||LOWER(attr_name));
1534 l(',');
1535 li(l_prefix||'ue.actual_content_source');
1536 li(' FROM ');
1537 IF ( UPPER(p_purpose) = 'BULK') THEN
1538 ll(RPAD(l_prefix,7+LENGTHB(g_indent))||'hz_'||p_entity_name||'_profiles ue');
1539 li(' WHERE ue.'||'party_id BETWEEN p_from_party_id AND p_to_party_id');
1540 ELSIF ( UPPER(p_purpose) = 'IMPORT') THEN
1541 ll(RPAD(l_prefix,7+LENGTHB(g_indent))||
1542 'hz_'||p_entity_name||'_profiles ue');
1543 l(',');
1544 li(' hz_imp_parties_sg sg,');
1545 li(' hz_imp_parties_int i');
1546 li(' WHERE ue.'||'party_id = sg.party_id ');
1547 li(' AND sg.int_row_id = i.rowid ');
1548 li(' AND i.interface_status is null ');
1549 li(' AND i.batch_id = P_BATCH_ID ');
1550 li(' AND i.party_orig_system = P_WU_OS ');
1551 li(' AND i.party_orig_system_reference between P_FROM_OSR and P_TO_OSR ');
1552 END IF;
1553 li(' AND ue.effective_end_date IS NULL');
1554 li(' AND ue.actual_content_source in (');
1555 FOR i IN 1..i_content_source_type.COUNT LOOP
1556 if i <> 1 then
1557 l(',');
1558 end if;
1559 l(''''||i_content_source_type(i)||'''');
1560 END LOOP;
1561 l(')');
1562 li(' AND NOT EXISTS (');
1563 li(' SELECT ''Y''');
1564 li(' FROM hz_'||p_entity_name||'_profiles entity');
1565 li(' WHERE entity.party_id = ue.party_id');
1566 li(' AND entity.actual_content_source = ''USER_ENTERED''');
1567 li(' AND entity.effective_end_date IS NULL )');
1568 li(' --');
1569 li(' -- at least one third party profile exists for this party.');
1570 li(' --');
1571 li(' AND EXISTS (');
1572 li(' SELECT ''Y''');
1573 li(' FROM hz_'||p_entity_name||'_profiles entity');
1574 li(' WHERE entity.party_id = ue.party_id');
1575 li(' AND entity.actual_content_source NOT IN (''USER_ENTERED'',''SST'')');
1576 li(' AND entity.effective_end_date IS NULL )');
1577 li(') user_entered');
1578 li(' WHERE user_entered.rank = 1');
1579 l(';');
1580 END date_cursor_create;
1581
1582 /**
1583 * PRIVATE PROCEDURE date_cursor_update
1584 *
1585 * DESCRIPTION
1586 * This procedure is used to generate cursor for
1587 * passed attribute_id setup as 'By Date' for
1588 * BulkUpdateSST and ImportUpdateSST procedures.
1589 * Select the attribute and corresponding actual_content_source
1590 * from the last updated profile among the visible data
1591 * sources for this attribute.
1592 *
1593 CURSOR c_KNOWN_AS5 IS
1594 SELECT user_entered.known_as5, user_entered.actual_content_source
1595 FROM ( SELECT decode(fnd_profile.value ('HZ_PROFILE_VERSION'),'NEW_VERSION','C',
1596 'NO_VERSION','U',
1597 decode(trunc(ue.effective_start_date),trunc(sysdate),'U','C')) create_update_flag,
1598 ue.organization_profile_id,
1599 ue.party_id,row_number() over
1600 (partition by ue.party_id order by ue.last_update_date desc nulls last) rank,
1601 ue.known_as5 , ue.actual_content_source
1602 FROM hz_organization_profiles ue
1603 WHERE ue.party_id BETWEEN p_from_party_id AND p_to_party_id
1604 AND ue.effective_end_date IS NULL
1605 AND ue.actual_content_source in ( 'DNB' ,'USER_ENTERED' ...)
1606 AND EXISTS (
1607 USER_ENTERED profile
1608 AND EXISTS (
1609 SST profile
1610 ) user_entered
1611 WHERE user_entered.rank = 1
1612 ORDER BY create_update_flag
1613
1614 * MODIFICATION HISTORY
1615 *
1616 * 17-12-2004 Dhaval Metha SSM SST Project
1617 * ~ Created.
1618 */
1619 procedure date_cursor_update(attr_id IN NUMBER,
1620 attr_name IN VARCHAR2,
1621 p_entity_name IN VARCHAR2,
1622 p_purpose IN VARCHAR2) IS
1626 where entity_attr_id = attr_id
1623 cursor data_sources is
1624 select content_source_type
1625 from hz_select_data_sources
1627 and ranking <> 0;
1628
1629 i_content_source_type INDEXVARCHAR30List;
1630 l_cursor_name_date VARCHAR2(30);
1631 l_prefix VARCHAR2(30);
1632
1633 Begin
1634 OPEN data_sources;
1635 FETCH data_sources BULK COLLECT INTO i_content_source_type;
1636 CLOSE data_sources;
1637
1638 l_cursor_name_date := attr_name||'_';
1639 l_prefix := RPAD(g_indent,9+LENGTHB(g_indent));
1640
1641 li('CURSOR '||l_cursor_name_date||' IS');
1642 li(' SELECT ');
1643
1644 l_prefix := RPAD(g_indent,9+LENGTHB(g_indent));
1645 ll(l_prefix||'user_entered.'||LOWER(attr_name));
1646 l(',');
1647 ll(l_prefix||'user_entered.actual_content_source');
1648
1649 l('');
1650 li(' FROM ( ');
1651 li(' SELECT decode(fnd_profile.value (''HZ_PROFILE_VERSION''),''NEW_VERSION'',''C'',''NO_VERSION'',''U'',decode(trunc(sst.effective_start_date),trunc(sysdate),''U'',''C'')) create_update_flag, ');
1652 l(l_prefix||'ue.'||p_entity_name||'_profile_id,');
1653 ll(l_prefix||'ue.party_id,');
1654 -- li('( SELECT ');
1655 li(' row_number() over ');
1656 ll(' (partition by ue.party_id order by ue.last_update_date desc nulls last) rank, ');
1657 li(l_prefix||'ue.'||LOWER(attr_name));
1658 l(',');
1659 li(l_prefix||'ue.actual_content_source');
1660 li(' FROM ');
1661 ll(RPAD(l_prefix,7+LENGTHB(g_indent))||
1662 'hz_'||p_entity_name||'_profiles sst');
1663 l(',');
1664 IF ( UPPER(p_purpose) = 'BULK') THEN
1665 ll(RPAD(l_prefix,7+LENGTHB(g_indent))||'hz_'||p_entity_name||'_profiles ue');
1666 li(' WHERE ue.'||'party_id BETWEEN p_from_party_id AND p_to_party_id');
1667 ELSIF ( UPPER(p_purpose) = 'IMPORT') THEN
1668 ll(RPAD(l_prefix,7+LENGTHB(g_indent))||
1669 'hz_'||p_entity_name||'_profiles ue');
1670 l(',');
1671 li(' hz_imp_parties_sg sg,');
1672 li(' hz_imp_parties_int i');
1673 li(' WHERE ue.'||'party_id = sg.party_id ');
1674 li(' AND sg.int_row_id = i.rowid ');
1675 li(' AND i.interface_status is null ');
1676 li(' AND i.batch_id = P_BATCH_ID ');
1677 li(' AND i.party_orig_system = P_WU_OS ');
1678 li(' AND i.party_orig_system_reference between P_FROM_OSR and P_TO_OSR ');
1679 END IF;
1680 li(' AND sst.'||'party_id = ue.party_id ');
1681 li(' AND sst.effective_end_date IS NULL');
1682 li(' AND sst.actual_content_source=''SST''');
1683 li(' AND ue.effective_end_date IS NULL');
1684 li(' AND ue.actual_content_source in (');
1685 FOR i IN 1..i_content_source_type.COUNT LOOP
1686 if i <> 1 then
1687 l(',');
1688 end if;
1689 l(''''||i_content_source_type(i)||'''');
1690 END LOOP;
1691 l(')');
1692 li(' AND EXISTS (');
1693 li(' SELECT ''Y''');
1694 li(' FROM hz_'||p_entity_name||'_profiles entity');
1695 li(' WHERE entity.party_id = ue.party_id');
1696 li(' AND entity.actual_content_source = ''USER_ENTERED''');
1697 li(' AND entity.effective_end_date IS NULL )');
1698 li(') user_entered');
1699 li(' WHERE user_entered.rank = 1');
1700 li('ORDER BY create_update_flag,user_entered.party_id;');
1701 END date_cursor_update;
1702
1703 /**
1704 * PRIVATE PROCEDURE
1705 *
1706 * DESCRIPTION
1707 * p_entity_name IN VARCHAR2,
1708 * p_purpose IN VARCHAR2
1709 *
1710 * MODIFICATION HISTORY
1711 *
1712 * p_purpose is added for import data load
1713 * p_purpose = 'Bulk' will generate MixNMatch Load procedure
1714 * p_purpose = 'Import' will generate Import Load procedure
1715 *
1716 * 07-12-2004 Dhaval Mehta SSM SST Project
1717 * ~ Added code to generate cursors
1718 * for 'By Date' attributes.
1719 * ~ Bulk collect the values for date attributes
1720 * and use them in same insert statement as
1721 * 'By Rank' attributes
1722 * ~ populate the exception table for
1723 * 'By Date' attributes.
1724 */
1725
1726 PROCEDURE Gen_BulkCreateSST (
1727 p_entity_name IN VARCHAR2,
1728 p_purpose IN VARCHAR2
1729 ) IS
1730
1731 CURSOR c_restricted_attributes IS
1732 SELECT e.attribute_name, e.entity_attr_id,
1733 e.group_flag, s.content_source_type
1734 FROM
1735 (SELECT e.attribute_name, e.entity_attr_id, 'N' group_flag
1736 FROM hz_entity_attributes e
1737 WHERE e.entity_name = 'HZ_'||UPPER(p_entity_name)||'_PROFILES'
1738 UNION
1739 SELECT e.attribute_group_name,
1740 MIN(entity_attr_id) entity_attr_id,
1741 'Y' group_flag
1742 FROM hz_entity_attributes e
1743 WHERE e.entity_name = 'HZ_'||UPPER(p_entity_name)||'_PROFILES'
1744 AND NOT EXISTS (
1745 SELECT 'Y'
1746 FROM hz_entity_attributes e1
1747 WHERE e.attribute_group_name = e1.attribute_name)
1748 GROUP BY e.attribute_group_name) e,
1749 hz_select_data_sources s
1750 WHERE EXISTS (
1751 SELECT 'Y'
1752 FROM hz_select_data_sources s1
1753 WHERE s1.entity_attr_id = e.entity_attr_id
1754 AND s1.content_source_type <> 'USER_ENTERED'
1755 AND s1.ranking > 0 )
1756 AND s.entity_attr_id = e.entity_attr_id
1757 AND s.ranking > 0
1758 ORDER BY e.attribute_name, s.ranking;
1759
1760 i_attribute_name INDEXVARCHAR30List;
1761 i_uq_attribute_name INDEXVARCHAR30List;
1762 i_entity_attr_id INDEXIDList;
1766 i_content_source_type INDEXVARCHAR30List;
1763 i_uq_entity_attr_id INDEXIDList;
1764 i_group_flag INDEXVARCHAR1List;
1765 i_uq_group_flag INDEXVARCHAR1List;
1767 i_uq_data_type INDEXVARCHAR30List;
1768 i_uq_winner_source INDEXVARCHAR30List;
1769 i_normal_attributes INDEXVARCHAR30List;
1770
1771 CURSOR c_sources IS
1772 SELECT UNIQUE s.content_source_type
1773 FROM hz_entity_attributes e,
1774 hz_select_data_sources s
1775 WHERE e.entity_name = 'HZ_'||UPPER(p_entity_name)||'_PROFILES'
1776 AND e.entity_attr_id = s.entity_attr_id
1777 AND s.ranking > 0;
1778
1779 i_sources INDEXVARCHAR30List;
1780
1781 CURSOR c_groups IS
1782 SELECT e.attribute_group_name, e.attribute_name
1783 FROM hz_entity_attributes e,
1784 (SELECT UNIQUE attribute_group_name
1785 FROM hz_entity_attributes
1786 WHERE entity_name = 'HZ_'||UPPER(p_entity_name)||'_PROFILES'
1787 GROUP BY attribute_group_name
1788 HAVING COUNT(*) > 1) e1
1789 WHERE e.entity_name = 'HZ_'||UPPER(p_entity_name)||'_PROFILES'
1790 AND e.attribute_group_name = e1.attribute_group_name
1791 AND e.attribute_group_name <> e.attribute_name
1792 AND EXISTS (
1793 SELECT 'Y'
1794 FROM hz_select_data_sources s1
1795 WHERE s1.entity_attr_id = e.entity_attr_id
1796 AND s1.content_source_type <> 'USER_ENTERED'
1797 AND s1.ranking > 0 );
1798
1799 i_group_name INDEXVARCHAR30List;
1800 i_uq_group_attributes INDEXVARCHAR30List;
1801
1802 l_all_attribute_names INDEXVARCHAR30List;
1803 l_data_type INDEXVARCHAR30List;
1804 l_data_length NUMBERList;
1805 l_de_column_names INDEXVARCHAR30List;
1806
1807 l_procedure_name VARCHAR2(30);
1808 l_cursor_name VARCHAR2(30);
1809 -- Bug 4171892
1810 l_prefix VARCHAR2(1000);
1811 --Bug 9768344 - Changed Str1 size 1000 to 4000
1812 str1 VARCHAR2(4000);
1813 str2 VARCHAR2(1000);
1814 str3 VARCHAR2(1000);
1815 l_start NUMBER;
1816 l_end NUMBER;
1817 l_index NUMBER;
1818 l_index1 NUMBER;
1819 m NUMBER := 0;
1820 k NUMBER := 0;
1821 l_has_party_name BOOLEAN := FALSE;
1822 l_has_dated_party_name BOOLEAN := FALSE;
1823
1824
1825 -- SSM SST Project :
1826 -- Cursor to pick attributes setup for 'By Date' method
1827 -- local variables for generating dynamic package
1828 -- Bug 6472658 : ORDER BY Clause added.
1829 CURSOR c_date_attributes IS
1830 SELECT distinct e.attribute_name, e.entity_attr_id
1831 FROM hz_entity_attributes e,
1832 hz_select_data_sources s
1833 WHERE e.entity_name = 'HZ_'||UPPER(p_entity_name)||'_PROFILES'
1834 AND EXISTS (
1835 SELECT 'Y'
1836 FROM hz_select_data_sources s1
1837 WHERE s1.entity_attr_id = e.entity_attr_id
1838 AND s1.ranking < 0 )
1839 AND s.entity_attr_id = e.entity_attr_id
1840 AND s.ranking < 0
1841 ORDER BY e.attribute_name;
1842
1843 i_attribute_group_name_date INDEXVARCHAR30List;
1844 i_primary_entity_attr_id_date INDEXIDList;
1845 i_group_attribute_name_date INDEXVARCHAR30List;
1846 i_group_entity_attr_id_date INDEXIDList;
1847 i_attribute_name_date INDEXVARCHAR30List;
1848 i_entity_attr_id_date INDEXIDList;
1849 l_index_date NUMBER;
1850 i_data_type VARCHAR2(60);
1851 l_cursor_name_date VARCHAR2(30);
1852
1853 BEGIN
1854
1855 IF p_entity_name = 'organization' THEN
1856 l_procedure_name := p_purpose || 'CreateOrgSST';
1857 l_all_attribute_names := G_O_ALL_ATTRIBUTE_NAMES;
1858 l_data_type := G_O_DATA_TYPE;
1859 l_data_length := G_O_DATA_LENGTH;
1860 l_de_column_names := G_O_DE_COLUMN_NAMES;
1861 ELSE
1862 l_procedure_name := p_purpose || 'CreatePersonSST';
1863 l_all_attribute_names := G_P_ALL_ATTRIBUTE_NAMES;
1864 l_data_type := G_P_DATA_TYPE;
1865 l_data_length := G_P_DATA_LENGTH;
1866 l_de_column_names := G_P_DE_COLUMN_NAMES;
1867 END IF;
1868
1869 /*===============================================================
1870 + start to build the procedure
1871 +===============================================================*/
1872
1873 If UPPER(p_purpose) = 'BULK' THEN
1874 Gen_ProcedureUtilForBulk('Header',l_procedure_name);
1875 ELSE
1876 Gen_ProcedureUtilForBulk('ImportHeader',l_procedure_name);
1877 END IF;
1878
1879 /*===============================================================
1880 + declare local variables
1881 +
1882 + TYPE t_branch_flag IS TABLE OF VARCHAR2(50);
1883 + i_branch_flag t_branch_flag;
1884 + TYPE t_ceo_name IS TABLE OF VARCHAR2(70);
1885 + i_ceo_name t_ceo_name;
1886 + ...
1887 +===============================================================*/
1888
1889 -- bulk load attributes' setup.
1890
1891 OPEN c_restricted_attributes;
1892 FETCH c_restricted_attributes BULK COLLECT INTO
1893 i_attribute_name, i_entity_attr_id,
1894 i_group_flag, i_content_source_type;
1895 CLOSE c_restricted_attributes;
1899 i_attribute_name_date, i_entity_attr_id_date;
1896 -- SSM SST Project : fetch the 'By Date' attributes
1897 OPEN c_date_attributes;
1898 FETCH c_date_attributes BULK COLLECT INTO
1900 CLOSE c_date_attributes;
1901 -- SSM SST Project : If no attribute is setup, package is not required
1902 IF (i_attribute_name.COUNT = 0 AND i_attribute_name_date.COUNT = 0) THEN
1903 Gen_ProcedureUtilForBulk('NullBody', l_procedure_name);
1904 RETURN;
1905 END IF;
1906
1907 -- declare local variables
1908 -- SSM SST Project : pass i_attribute_name_date as extra parameter
1909 Declare_Attributes(p_entity_name, i_attribute_name, i_attribute_name_date, i_normal_attributes);
1910
1911 li(fp('i_profile_id')||'INDEXIDlist;');
1912 li(fp('i_sst_profile_id')||'INDEXIDlist;');
1913 li(fp('i_entity_attr_id')||'INDEXIDlist;');
1914 li(fp('i_party_id')||'INDEXIDlist;');
1915 li(fp('subtotal')||'NUMBER := 0;');
1916 li(fp('total')||'NUMBER := 0;');
1917 li(fp('i_effective_start_date')||'INDEXVARDATElist;');
1918
1919 IF UPPER(p_purpose) = 'BULK' THEN
1920 Gen_CodeForCursor('DECLARE');
1921 ELSE
1922 Gen_CodeForCursor('DECLARE',null, null, 'IMPORT');
1923 END IF;
1924
1925 /*===============================================================
1926 + select for restricted columns (i.e. columns have been selected)
1927 +
1928 + SELECT ...
1929 + --
1930 + -- processing branch_flag.
1931 + DECODE(dnb.branch_flag, NULL,
1932 + DECODE(user_entered.branch_flag, NULL,
1933 + NULL,
1934 + user_entered.branch_flag||'%#USER_ENTERED'),
1935 + dnb.branch_flag||'%#DNB') branch_flag,
1936 + --
1937 + -- processing ceo_name.
1938 + DECODE(user_entered.ceo_name, NULL,
1939 + DECODE(dnb.ceo_name, NULL,
1940 + NULL,
1941 + dnb.ceo_name||'%#DNB'),
1942 + user_entered.ceo_name||'%#USER_ENTERED') ceo_name,
1943 + --
1944 + ...
1945 +===============================================================*/
1946
1947 l_cursor_name := 'c_entity';
1948 l_prefix := RPAD(g_indent,9+LENGTHB(g_indent));
1949
1950 li('CURSOR '||l_cursor_name||' IS');
1951 li(' SELECT d_user_entered.'||p_entity_name||'_profile_id,');
1952 ll(l_prefix||'d_user_entered.party_id,');
1953 ll(l_prefix||'d_user_entered.effective_start_date');
1954
1955 k := 0;
1956 FOR i IN 1..i_attribute_name.COUNT+1 LOOP
1957 IF i = 1 OR
1958 i = i_attribute_name.COUNT+1 OR
1959 i_attribute_name(i-1) <> i_attribute_name(i)
1960 THEN
1961 IF i > 1 THEN
1962 l(l_prefix||'NULL,');
1963
1964 FOR j IN REVERSE l_start..l_end LOOP
1965 IF l_data_type(l_index) IN ('NUMBER','DATE') THEN
1966 str1 := g_to_char; str2 := ''; str3 := ')';
1967 IF l_data_type(l_index) = 'DATE' THEN
1968 str2 := ',''YYYY/MM/DD HH:MI:SS''';
1969 END IF;
1970 ELSE
1971 str1 := ''; str2 := ''; str3 := '';
1972 END IF;
1973
1974 ll(l_prefix||str1||'d_'||LOWER(frmt_actual_content_source(i_content_source_type(j)))||'.'||
1975 LOWER(i_attribute_name(i-1))||str2||str3||'||'''||
1976 g_sep||i_content_source_type(j)||''')');
1977
1978 IF j = l_start THEN
1979 ll(' '||LOWER(i_attribute_name(i-1)));
1980 ELSE
1981 l(',');
1982 END IF;
1983 l_prefix := SUBSTRB(l_prefix, 1, LENGTHB(l_prefix)-2);
1984 END LOOP;
1985 END IF; -- IF i > 1 THEN
1986
1987 IF i = i_attribute_name.COUNT+1 THEN
1988 EXIT;
1989 END IF;
1990
1991 -- start processing a new attribute group.
1992
1993 l_start := i; l_end := i; l_prefix := RPAD(g_indent,9+LENGTHB(g_indent));
1994 IF i_group_flag(i) = 'N' THEN
1995 l_index := Get_Index(l_all_attribute_names, i_attribute_name(i));
1996 END IF;
1997 k := k + 1;
1998
1999 IF i_attribute_name(i) IN ('ORGANIZATION_NAME','PERSON_NAME') THEN
2000 l_has_party_name := true;
2001 END IF;
2002
2003 i_uq_attribute_name(k) := i_attribute_name(i);
2004 i_uq_entity_attr_id(k) := i_entity_attr_id(i);
2005 i_uq_group_flag(k) := i_group_flag(i);
2006 IF i_group_flag(i) = 'N' THEN
2007 i_uq_data_type(k) := l_data_type(l_index);
2008 ELSE
2009 i_uq_data_type(k) := 'VARCHAR2';
2010 END IF;
2011 i_uq_winner_source(k) := i_content_source_type(i);
2012
2013 l(',');
2014 l(l_prefix||'--');
2015 l(l_prefix||'-- processing '||LOWER(i_attribute_name(i))||'.');
2016
2017 ELSE
2018 l_prefix := l_prefix||' '; l_end := l_end+1;
2019 END IF;
2020
2021 l(l_prefix||'DECODE(d_'||LOWER(frmt_actual_content_source(i_content_source_type(i)))||
2022 '.'||LOWER(i_attribute_name(i))||', NULL, ');
2023 END LOOP;
2024
2025 /*===============================================================
2026 + select normal columns (i.e. columns not in setup tables and
2027 + columns not selected)
2028 +
2029 + --
2030 + -- processing attribute1.
2031 + user_entered.attribute1,
2032 + --
2033 + -- processing attribute2.
2034 + user_entered.attribute2,
2035 + ...
2036 +===============================================================*/
2037
2038 FOR i IN 1..i_normal_attributes.COUNT LOOP
2042 l(l_prefix||'-- processing '||LOWER(i_normal_attributes(i))||'.');
2039 l(',');
2040 l_prefix := RPAD(g_indent,9+LENGTHB(g_indent));
2041 l(l_prefix||'--');
2043 ll(l_prefix||'d_user_entered.'||LOWER(i_normal_attributes(i)));
2044 END LOOP;
2045
2046 IF p_entity_name = 'organization' THEN
2047 -- select duns number
2048 l(',');
2049 l_prefix := RPAD(g_indent,9+LENGTHB(g_indent));
2050 l(l_prefix||'--');
2051 l(l_prefix||'-- processing duns_number.');
2052 ll(l_prefix||'d_user_entered.duns_number');
2053 END IF;
2054
2055 /*===============================================================
2056 + from list
2057 +
2058 + FROM hz_organization_profiles dnb,
2059 + hz_organization_profiles user_entered
2060 + ...
2061 +===============================================================*/
2062
2063 -- fetch all of content source types from setup table.
2064
2065 OPEN c_sources;
2066 FETCH c_sources BULK COLLECT INTO i_sources;
2067 CLOSE c_sources;
2068
2069 l('');
2070 li(' FROM');
2071 IF i_sources.COUNT = 0 THEN
2072 ll(RPAD(l_prefix,7+LENGTHB(g_indent))||
2073 'hz_'||p_entity_name||'_profiles d_user_entered');
2074 ELSE
2075 FOR i IN 1..i_sources.COUNT LOOP
2076 IF i > 1 THEN
2077 l(',');
2078 END IF;
2079
2080 ll(RPAD(l_prefix,7+LENGTHB(g_indent))||
2081 'hz_'||p_entity_name||'_profiles d_'||LOWER(frmt_actual_content_source(i_sources(i))));
2082 END LOOP;
2083 END IF;
2084
2085 IF ( UPPER(p_purpose) = 'IMPORT') THEN
2086 l(',');
2087 li(' hz_imp_parties_sg sg,');
2088 li(' hz_imp_parties_int i');
2089 END IF;
2090
2091 l('');
2092
2093 /*===============================================================
2094 + static where clause
2095 +===============================================================*/
2096
2097 li(' --');
2098 li(' -- use party profiles as driving table because in normal');
2099 li(' -- case the number of organization/person in hz_parties');
2100 li(' -- might be much smaller than the number of parties.');
2101 li(' --');
2102
2103 IF ( UPPER(p_purpose) = 'BULK') THEN
2104 getBulkCreateWhereCondition;
2105 ELSE
2106 getImportCreateWhereCondition;
2107 END IF;
2108
2109 /*
2110 li(' WHERE user_entered.'||'party_id BETWEEN p_from_party_id AND p_to_party_id');
2111 li(' AND user_entered.actual_content_source = ''SST''');
2112 li(' AND user_entered.effective_end_date IS NULL');
2113 */
2114 li(' -- no sst profile exists for this party.');
2115 li(' --');
2116 li(' AND NOT EXISTS (');
2117 li(' SELECT ''Y''');
2118 li(' FROM hz_'||p_entity_name||'_profiles entity');
2119 li(' WHERE entity.party_id = d_user_entered.party_id');
2120 li(' AND entity.actual_content_source = ''USER_ENTERED''');
2121 li(' AND entity.effective_end_date IS NULL )');
2122 li(' --');
2123 li(' -- at least one third party profile exists for this party.');
2124 li(' --');
2125 li(' AND EXISTS (');
2126 li(' SELECT ''Y''');
2127 li(' FROM hz_'||p_entity_name||'_profiles entity');
2128 li(' WHERE entity.party_id = d_user_entered.party_id');
2129 li(' AND entity.actual_content_source NOT IN (''USER_ENTERED'',''SST'')');
2130 li(' AND entity.effective_end_date IS NULL )');
2131 li(' --');
2132 li(' -- we use outer join for each third party data source');
2133 li(' -- in case the party does not have the specific third');
2134 li(' -- party profile.');
2135 li(' --');
2136
2137 /*===============================================================
2138 + dynamic where clause
2139 +
2140 + AND dnb.party_id (+) = user_entered.party_id
2141 + AND dnb.actual_content_source (+) = 'DNB'
2142 + AND dnb.effective_end_date IS NULL
2143 + ...
2144 +===============================================================*/
2145
2146 k := 0;
2147 FOR i IN 1..i_sources.COUNT LOOP
2148 IF i_sources(i) <> 'USER_ENTERED' THEN
2149 k := 1;
2150 IF k <> 1 THEN
2151 l('');
2152 END IF;
2153 li(' AND d_'||LOWER(frmt_actual_content_source(i_sources(i)))||'.party_id (+) = d_user_entered.party_id');
2154 li(' AND d_'||LOWER(frmt_actual_content_source(i_sources(i)))||'.actual_content_source (+) = '''||i_sources(i)||'''');
2155 lli(' AND d_'||LOWER(frmt_actual_content_source(i_sources(i)))||'.effective_end_date IS NULL');
2156 END IF;
2157 END LOOP;
2158 l(';');
2159 l('');
2160
2161 -- SSM SST Project :
2162 /*===================================
2163 + Create Cursors for Date ranked attriubtes
2164 +=====================================*/
2165 FOR i IN 1..i_attribute_name_date.COUNT LOOP
2166 date_cursor_create(i_entity_attr_id_date(i),
2167 i_attribute_name_date(i),
2168 p_entity_name,
2169 p_purpose);
2170
2171 IF i_attribute_name_date(i) in ('PERSON_NAME','ORGANIZATION_NAME')
2172 THEN
2173 l_has_dated_party_name := true;
2174 END IF;
2175
2176
2177 END LOOP;
2178 l('');
2179
2180
2181 /*===============================================================
2182 + package body
2183 +===============================================================*/
2184
2185 l('BEGIN');
2186 IF UPPER(p_purpose) = 'BULK' THEN
2187 Gen_ProcedureUtilForBulk('Log',l_procedure_name);
2188 ELSE
2189 Gen_ProcedureUtilForBulk('ImportLog',l_procedure_name);
2193 IF UPPER(p_purpose) = 'BULK' THEN
2190 END IF;
2191
2192 l('');
2194 li('reset_who;');
2195 ELSE
2196 li('reset_who(p_request_id, p_program_id, p_program_application_id);');
2197 END IF;
2198 l('');
2199 li('hz_common_pub.disable_cont_source_security;');
2200 l('');
2201 li('write_log(''open cursor '||l_cursor_name||''');');
2202 l('');
2203
2204 /*===============================================================
2205 + open cursor
2206 +===============================================================*/
2207
2208 -- SSM SST Project : Open cursors for 'By Date' attributes
2209 FOR i IN 1..i_attribute_name_date.COUNT LOOP
2210 li('OPEN '||i_attribute_name_date(i)||'_;');
2211 END LOOP;
2212
2213 IF ( UPPER(p_purpose) = 'BULK') THEN
2214 Gen_CodeForCursor('OPEN', l_cursor_name, 'Y');
2215 ELSE
2216 Gen_CodeForCursor('OPEN', l_cursor_name, 'Y', 'IMPORT');
2217 END IF;
2218
2219 li(' i_profile_id,');
2220 lli(' i_party_id,');
2221 lli(' i_effective_start_date');
2222
2223 /*===============================================================
2224 + a list of pl/sql tables into which the cursor bulk collect
2225 +
2226 + i_branch_flag,
2227 + i_ceo_name,
2228 + ...
2229 +===============================================================*/
2230
2231 FOR i IN 1..i_uq_attribute_name.COUNT LOOP
2232 l(',');
2233 lli(' i_'||Format_AttributeName(i_uq_attribute_name(i)));
2234 END LOOP;
2235
2236 FOR i IN 1..i_normal_attributes.COUNT LOOP
2237 l(',');
2238 lli(' i_'||Format_AttributeName(i_normal_attributes(i)));
2239 END LOOP;
2240
2241 -- process duns number
2242 IF p_entity_name = 'organization' THEN
2243 l(',');
2244 lli(' i_duns_number');
2245 END IF;
2246
2247 l(' LIMIT rows;');
2248
2249 Gen_CodeForCursor('FETCH', l_cursor_name);
2250
2251 -- SSM SST Project : Fetch and BULK COLLECT for 'By Date' attributes
2252 FOR i IN 1..i_attribute_name_date.COUNT LOOP
2253 lli(' FETCH '||i_attribute_name_date(i)||'_ ');
2254 l('BULK COLLECT INTO');
2255 lli(' i_'||Format_AttributeName(i_attribute_name_date(i)));
2256 l(',');
2257 lli(' '||Format_AttributeName(i_attribute_name_date(i)));
2258 l(' LIMIT rows;');
2259 l('');
2260 END LOOP;
2261 l('');
2262
2263
2264 li(' subtotal := i_party_id.COUNT;');
2265 li(' IF subtotal = 0 AND l_last_fetch THEN');
2266 li(' EXIT;');
2267 li(' END IF;');
2268 li(' total := total + i_party_id.COUNT;');
2269 l('');
2270 li(' write_log(''fetched ''||subtotal||'' records.'');');
2271 l('');
2272
2273 /*===============================================================
2274 + reset actual_content_source for user-entered Profiles
2275 +===============================================================*/
2276
2277 li(' write_log(''reset actual_content_source for user-entered profiles.'');');
2278 l('');
2279 li(' -- reset actual_content_source for user-entered profiles.');
2280 l('');
2281 li(' FORALL i IN 1..subtotal');
2282 li(' UPDATE hz_'||p_entity_name||'_profiles ');
2283 li(' SET actual_content_source = ''USER_ENTERED''');
2284 li(' WHERE '||p_entity_name||'_profile_id = i_profile_id(i);');
2285 l('');
2286
2287 IF UPPER(p_purpose) = 'BULK' THEN
2288 Gen_CodeForCommit;
2289 END IF;
2290 /*===============================================================
2291 + select attributes inside an attribute group
2292 +===============================================================*/
2293
2294 OPEN c_groups;
2295 FETCH c_groups BULK COLLECT INTO
2296 i_group_name, i_uq_group_attributes;
2297 CLOSE c_groups;
2298
2299 k := 0;
2300 FOR i IN 1..i_group_name.COUNT+1 LOOP
2301 IF i = 1 OR
2302 i = i_group_name.COUNT+1 OR
2303 i_group_name(i-1) <> i_group_name(i)
2304 THEN
2305 IF i > 1 THEN
2306 IF i_group_name(i-1) = 'DUNS_NUMBER_C' THEN
2307 l(',');
2308 lli(' duns_number');
2309 END IF;
2310
2311 l('');
2312 li(' INTO ');
2313
2314 FOR j IN l_start..l_end LOOP
2315 IF j <> l_start THEN
2316 l(',');
2317 END IF;
2318 lli(' i_'||Format_AttributeName(i_uq_group_attributes(j))||'(i)');
2319 END LOOP;
2320
2321 IF i_group_name(i-1) = 'DUNS_NUMBER_C' THEN
2322 l(',');
2323 lli(' i_duns_number(i)');
2324 END IF;
2325
2326 str1 := Format_AttributeName(i_group_name(i-1));
2327 l('');
2328 li(' FROM hz_'||p_entity_name||'_profiles');
2329 li(' WHERE party_id = i_party_id(i)');
2330 li(' AND effective_end_date IS NULL');
2331 li(' AND actual_content_source = '||
2332 'SUBSTRB(i_'||str1||'(i)'||
2333 ',INSTRB(i_'||str1||'(i)'||', '''||g_sep||''')+2);');
2334
2335 IF i_group_name(i-1) = 'DUNS_NUMBER_C' THEN
2336 li(' ELSE');
2337 li(' i_duns_number(i) := NULL;');
2338 END IF;
2339 li(' END IF;');
2340 li(' END LOOP;');
2341 l('');
2342
2343 END IF; -- IF i > 1 THEN
2344
2345 IF i = i_group_name.COUNT+1 THEN
2346 EXIT;
2347 END IF;
2348
2352 li(' write_log(''processing group '||LOWER(i_group_name(i))||''');');
2349 -- start processing a new attribute group.
2350
2351 l_start := i; l_end := i;
2353 l('');
2354 li(' -- process group '||LOWER(i_group_name(i)));
2355 li(' --');
2356 li(' FOR i IN 1..subtotal LOOP');
2357 li(' IF i_'||Format_AttributeName(i_group_name(i))||'(i) IS NOT NULL THEN');
2358 li(' SELECT');
2359 ELSE
2360 l(','); l_end := l_end+1;
2361 END IF;
2362
2363 lli(' DECODE('||LOWER(i_uq_group_attributes(i))||',NULL,DECODE(i_'||
2364 LOWER(i_uq_group_attributes(i))||'(i),NULL,NULL,'''||g_sep||'''||actual_content_source),'||
2365 LOWER(i_uq_group_attributes(i))||'||'''||g_sep||'''||actual_content_source)');
2366
2367 END LOOP;
2368 --Bug10365551
2369 IF UPPER(p_purpose) = 'IMPORT' AND UPPER(p_entity_name) = 'PERSON' THEN
2370 li('--Bug10365551');
2371 li('--Fethcing HZ_PROFILE_VERSION value and based on profile value Effective Start Date created.');
2372 li(' l_profile_version := nvl(fnd_profile.value('||''''||'HZ_PROFILE_VERSION'||''''||'),'||''''||'Y'||''''||');');
2373 END IF;
2374
2375 /*===============================================================
2376 + create sst profiles
2377 +===============================================================*/
2378
2379 li(' write_log(''create sst profiles.'');');
2380 l('');
2381 li(' -- insert sst profiles');
2382 l('');
2383 li(' FORALL i IN 1..subtotal');
2384 li(' INSERT INTO hz_'||p_entity_name||'_profiles (');
2385
2386 Gen_CodeForWhoColumns('I','LIST',g_indent||' ','Y');
2387 l(',');
2388 Gen_CodeForOtherAttributes ('LIST',g_indent||' ');
2389 l(',');
2390 li(' '||p_entity_name||'_profile_id,');
2391 li(' party_id,');
2392 li(' effective_start_date,');
2393 lli(' object_version_number');
2394
2395 /*===============================================================
2396 + column list of insert statement
2397 +
2398 + duns_number_c,
2399 + employees_total,
2400 + ...
2401 +===============================================================*/
2402
2403 FOR i IN 1..i_uq_attribute_name.COUNT LOOP
2404 l(',');
2405 lli(' '||LOWER(i_uq_attribute_name(i)));
2406 END LOOP;
2407
2408 FOR i IN 1..i_normal_attributes.COUNT LOOP
2409 l(',');
2410 lli(' '||LOWER(i_normal_attributes(i)));
2411 END LOOP;
2412
2413 -- SSM SST Project : Add date attributes in insert statement
2414 FOR i IN 1..i_attribute_name_date.COUNT LOOP
2415 l(',');
2416 lli(' '||LOWER(i_attribute_name_date(i)));
2417 END LOOP;
2418
2419 IF p_entity_name = 'organization' THEN
2420 l(',');
2421 lli(' duns_number');
2422 END IF;
2423
2424 l('');
2425 li(' ) VALUES (');
2426
2427 /*===============================================================
2428 + value list of insert statement
2429 +
2430 + SUBSTRB(i_duns_number_c(i),1,INSTRB(i_duns_number_c(i),'%#')-1),
2431 + TO_NUMBER(SUBSTRB(i_employees_total(i),1, INSTRB(i_employees_total(i),'%#')-1)),
2432 + ...
2433 +===============================================================*/
2434
2435 Gen_CodeForWhoColumns('I','',g_indent||' ','Y');
2436 l(',');
2437 Gen_CodeForOtherAttributes ('',g_indent||' ');
2438 l(',');
2439
2440 li(' hz_'||p_entity_name||'_profiles_s.nextval,');
2441 li(' i_party_id(i),');
2442 IF UPPER(p_purpose) = 'IMPORT' AND UPPER(p_entity_name) = 'PERSON' THEN
2443 li(' DECODE(l_profile_version,'||''''||'NEW_VERSION'||''''||',SYSDATE,'||''''||'NO_VERSION'||''''||',i_effective_start_date(i)),');
2444 ELSE
2445 li(' SYSDATE,');
2446 END IF;
2447 lli(' 1');
2448
2449 FOR i IN 1..i_uq_attribute_name.COUNT LOOP
2450 l(',');
2451
2452 str1 := Format_AttributeName(i_uq_attribute_name(i));
2453
2454 IF i_uq_data_type(i) = 'VARCHAR2' THEN
2455 str2 := ''; str3 := '';
2456 ELSIF i_uq_data_type(i) = 'NUMBER' THEN
2457 str2 := g_to_number; str3 := ')';
2458 ELSIF i_uq_data_type(i) = 'DATE' THEN
2459 str2 := g_to_date; str3 := ',''YYYY/MM/DD HH:MI:SS'')';
2460 END IF;
2461
2462 lli(' '||str2||'SUBSTRB(i_'||str1||'(i),1,'||
2463 'INSTRB(i_'||str1||'(i),'''||g_sep||''')-1)'||str3);
2464 END LOOP;
2465
2466 FOR i IN 1..i_normal_attributes.COUNT LOOP
2467 l(',');
2468 lli(' i_'||Format_AttributeName(i_normal_attributes(i))||'(i)');
2469 END LOOP;
2470
2471 -- SSM SST Project : Values clause for 'By Date' attributes
2472 FOR i IN 1..i_attribute_name_date.COUNT LOOP
2473 l(',');
2474 lli(' i_'||Format_AttributeName(i_attribute_name_date(i))||'(i)');
2475 END LOOP;
2476
2477
2478 IF p_entity_name = 'organization' THEN
2479 -- process duns number
2480 l(',');
2481 li(' i_duns_number(i)');
2482 END IF;
2483
2484 l('');
2485 li(' ) RETURNING '||p_entity_name||'_profile_id BULK COLLECT INTO i_sst_profile_id;');
2486 l('');
2487
2488 IF UPPER(p_purpose) = 'BULK' THEN
2489 Gen_CodeForCommit;
2490 END IF;
2491
2492 /*===============================================================
2493 + denormalize sst to hz_parties (restricted columns only)
2494 +
2495 + duns_number_c =
2496 + SUBSTRB(i_duns_number_c(i),1,INSTRB(i_duns_number_c(i),'%#')-1),
2497 + employees_total =
2501
2498 + TO_NUMBER(SUBSTRB(i_employees_total(i),1, INSTRB(i_employees_total(i),'%#')-1)),
2499 + ...
2500 +===============================================================*/
2502 li(' write_log(''update denormalized columns in hz_parties.'');');
2503 l('');
2504
2505 li(' -- update denormalized columns in hz_parties.');
2506 l('');
2507 li(' FORALL i IN 1..subtotal');
2508 li(' UPDATE hz_parties');
2509 li(' SET');
2510
2511 Gen_CodeForWhoColumns('U','',g_indent||' ','Y');
2512
2513 FOR i IN 1..l_all_attribute_names.COUNT LOOP
2514 IF l_de_column_names(i) IS NOT NULL THEN
2515 l_index := Get_Index(i_uq_attribute_name, l_all_attribute_names(i));
2516 l_index_date := Get_Index(i_attribute_name_date, l_all_attribute_names(i));
2517
2518 IF l_index > 0 THEN
2519 l(',');
2520 lli(' '||LOWER(l_all_attribute_names(i))||' = ');
2521
2522 str1 := Format_AttributeName(l_all_attribute_names(i));
2523
2524 IF i_uq_data_type(l_index) = 'VARCHAR2' THEN
2525 str2 := ''; str3 := '';
2526 ELSIF i_uq_data_type(l_index) = 'NUMBER' THEN
2527 str2 := g_to_number; str3 := ')';
2528 ELSIF i_uq_data_type(l_index) = 'DATE' THEN
2529 str2 := g_to_date; str3 := ',''YYYY/MM/DD HH:MI:SS'')';
2530 END IF;
2531
2532 ll(str2||'SUBSTRB(i_'||str1||'(i),1,'||
2533 'INSTRB(i_'||str1||'(i),'''||g_sep||''')-1)'||str3);
2534 END IF;
2535 -- SSM SST Project : Denormalize 'By Date' columns
2536 IF l_index_date > 0 THEN
2537 l(',');
2538 lli(' '||LOWER(l_all_attribute_names(i))||' = ');
2539 ll('i_'||Format_AttributeName(l_all_attribute_names(i))||'(i)');
2540 END IF;
2541 END IF;
2542 END LOOP;
2543
2544 /*===============================================================
2545 + denormalize duns_number into hz_parties
2546 +===============================================================*/
2547
2548 IF p_entity_name = 'organization' THEN
2549 l(',');
2550 lli(' duns_number = i_duns_number (i)');
2551 END IF;
2552
2553 /*===============================================================
2554 + update party_name, customer_key in hz_parties
2555 +===============================================================*/
2556
2557 IF l_has_party_name THEN
2558 Gen_PartyName (p_entity_name);
2559 END IF;
2560
2561 IF l_has_dated_party_name THEN
2562 IF p_entity_name = 'organization' THEN
2563 l(',');
2564 lli(' party_name = i_organization_name(i)');
2565 ELSIF p_entity_name = 'person' THEN
2566 l(',');
2567 lli(' party_name = i_person_name(i)');
2568 END IF;
2569 END IF;
2570
2571 li(' WHERE party_id = i_party_id(i);');
2572 l('');
2573
2574 IF UPPER(p_purpose) = 'BULK' THEN
2575 Gen_CodeForCommit;
2576 END IF;
2577
2578 /*===============================================================
2579 + update party_name of relationship parties
2580 +===============================================================*/
2581
2582 IF l_has_party_name THEN
2583 Gen_RelationshipPartyName;
2584 IF UPPER(p_purpose) = 'BULK' THEN
2585 Gen_CodeForCommit(false);
2586 END IF;
2587 END IF;
2588
2589 /*===============================================================
2590 + update exception table to track attributes' data source
2591 +===============================================================*/
2592
2593 li(' write_log(''update exception table.'');');
2594 l('');
2595
2596 FOR i IN 1..i_uq_entity_attr_id.COUNT LOOP
2597 IF i_uq_group_flag(i) = 'N' THEN
2598 li(' update_exception_table(''C'','||
2599 'i_party_id,'''||i_uq_attribute_name(i)||''','||
2600 i_uq_entity_attr_id(i)||','||
2601 'i_'||Format_AttributeName(i_uq_attribute_name(i))||','''||
2602 i_uq_winner_source(i)||''');');
2603 l('');
2604 END IF;
2605 END LOOP;
2606
2607 -- SSM SST Project : Update exceptions table for 'By Date' attributes
2608 -- Also, delete the entries from user overwrite rules
2609 FOR i IN 1..i_attribute_name_date.COUNT LOOP
2610 li(' update_exception_table_date(''C'','||
2611 'i_party_id,'''||i_attribute_name_date(i)||''','||
2612 i_entity_attr_id_date(i)||','||
2613 Format_AttributeName(i_attribute_name_date(i))||');');
2614 -- 'i_actual_content_source);');
2615 l('');
2616 END LOOP;
2617
2618 -- SSM SST Project : Delete the entries from user overwrite rules
2619 FOR i IN 1..i_attribute_name_date.COUNT LOOP
2620 li(' delete hz_user_overwrite_rules where entity_attr_id = ' || i_entity_attr_id_date(i) || ' ;');
2621 l('');
2622 END LOOP;
2623
2624 IF UPPER(p_purpose) = 'BULK' THEN
2625 Gen_CodeForCommit(false);
2626 END IF;
2627
2628 /*===============================================================
2629 + write output file
2630 +===============================================================*/
2631
2632 Gen_WriteOutputFileForCreate;
2633
2634 /*===============================================================
2635 + update DQM interface table
2636 +
2637 + As per talk with Srini, right now DQM can not handle huge
2638 + data in interface table. User should run DQM sync program
2639 + after they run this program for mix-n-match.
2640 +===============================================================*/
2641 /*
2642 IF hz_dqm_search_util.is_dqm_available = 'T' THEN
2646
2643 Gen_CodeForDQM(p_entity_name);
2644 END IF;
2645 */
2647 /*===============================================================
2648 + close cursor
2649 +===============================================================*/
2650
2651
2652 IF ( UPPER(p_purpose) = 'BULK') THEN
2653 Gen_CodeForCursor('CLOSE', l_cursor_name);
2654 ELSE
2655 Gen_CodeForCursor('CLOSE', l_cursor_name, null, 'IMPORT');
2656 END IF;
2657 -- SSM SST Project : Close 'By Date' cursors
2658 FOR i IN 1..i_attribute_name_date.COUNT LOOP
2659 li('CLOSE '||i_attribute_name_date(i)||'_;');
2660 END LOOP;
2661
2662 l('');
2663 li('write_out(total||'' sst profile(s) was(were) created.'');');
2664 l('');
2665 li('write_log(total||'' sst profile(s) was(were) successfully created.'');');
2666 l('END '||l_procedure_name||';');
2667 l('');
2668
2669 END Gen_BulkCreateSST;
2670
2671 /**
2672 * PRIVATE PROCEDURE
2673 *
2674 * DESCRIPTION
2675 * p_entity_name IN VARCHAR2,
2676 * p_purpose IN VARCHAR2
2677 *
2678 * MODIFICATION HISTORY
2679 *
2680 * p_purpose is added for import data load
2681 * p_purpose = 'Bulk' will generate MixNMatch Load procedure
2682 * p_purpose = 'Import' will generate Import Load procedure
2683 *
2684 * 07-12-2004 Dhaval Mehta SSM SST Project
2685 * ~ Added code to generate cursors
2686 * for 'By Date' attributes.
2687 * ~ Bulk collect the values for date attributes
2688 * and use them in same insert statement as
2689 * 'By Rank' attributes
2690 * ~ populate the exception table for
2691 * 'By Date' attributes.
2692 */
2693
2694 PROCEDURE Gen_BulkUpdateSST (
2695 p_entity_name IN VARCHAR2,
2696 p_purpose IN VARCHAR2
2697 ) IS
2698
2699 CURSOR c_restricted_attributes IS
2700 SELECT e.attribute_name, e.entity_attr_id,
2701 e.group_flag, s.content_source_type
2702 FROM
2703 (SELECT e.attribute_name, e.entity_attr_id, 'N' group_flag
2704 FROM hz_entity_attributes e
2705 WHERE e.entity_name = 'HZ_'||UPPER(p_entity_name)||'_PROFILES'
2706 AND e.updated_flag = 'Y'
2707 UNION
2708 SELECT e.attribute_group_name,
2709 MIN(entity_attr_id) entity_attr_id,
2710 'Y' group_flag
2711 FROM hz_entity_attributes e
2712 WHERE e.entity_name = 'HZ_'||UPPER(p_entity_name)||'_PROFILES'
2713 AND e.updated_flag = 'Y'
2714 AND NOT EXISTS (
2715 SELECT 'Y'
2716 FROM hz_entity_attributes e1
2717 WHERE e.attribute_group_name = e1.attribute_name)
2718 GROUP BY e.attribute_group_name) e,
2719 hz_select_data_sources s
2720 WHERE /*EXISTS (
2721 SELECT 'Y'
2722 FROM hz_select_data_sources s1
2723 WHERE s1.entity_attr_id = e.entity_attr_id
2724 AND s1.content_source_type <> 'USER_ENTERED'
2725 AND s1.ranking > 0 )
2726 AND */ s.entity_attr_id = e.entity_attr_id
2727 AND s.ranking > 0
2728 ORDER BY e.attribute_name, s.ranking;
2729
2730 CURSOR c_imp_restricted_attributes IS
2731 SELECT e.attribute_name, e.entity_attr_id,
2732 e.group_flag, s.content_source_type
2733 FROM
2734 (SELECT e.attribute_name, e.entity_attr_id, 'N' group_flag
2735 FROM hz_entity_attributes e
2736 WHERE e.entity_name = 'HZ_'||UPPER(p_entity_name)||'_PROFILES'
2737 UNION
2738 SELECT e.attribute_group_name,
2739 MIN(entity_attr_id) entity_attr_id,
2740 'Y' group_flag
2741 FROM hz_entity_attributes e
2742 WHERE e.entity_name = 'HZ_'||UPPER(p_entity_name)||'_PROFILES'
2743 AND NOT EXISTS (
2744 SELECT 'Y'
2745 FROM hz_entity_attributes e1
2746 WHERE e.attribute_group_name = e1.attribute_name)
2747 GROUP BY e.attribute_group_name) e,
2748 hz_select_data_sources s
2749 WHERE /*EXISTS (
2750 SELECT 'Y'
2751 FROM hz_select_data_sources s1
2752 WHERE s1.entity_attr_id = e.entity_attr_id
2753 AND s1.content_source_type <> 'USER_ENTERED'
2754 AND s1.ranking > 0 )
2755 AND*/ s.entity_attr_id = e.entity_attr_id
2756 AND s.ranking > 0
2757 ORDER BY e.attribute_name, s.ranking;
2758
2759 i_attribute_name INDEXVARCHAR30List;
2760 i_uq_attribute_name INDEXVARCHAR30List;
2761 i_entity_attr_id INDEXIDList;
2762 i_uq_entity_attr_id INDEXIDList;
2763 i_group_flag INDEXVARCHAR1List;
2764 i_uq_group_flag INDEXVARCHAR1List;
2765 i_content_source_type INDEXVARCHAR30List;
2766 i_uq_data_type INDEXVARCHAR30List;
2767 i_uq_winner_source INDEXVARCHAR30List;
2768 i_normal_attributes INDEXVARCHAR30List;
2769
2770 CURSOR c_sources IS
2771 SELECT UNIQUE s.content_source_type
2772 FROM hz_entity_attributes e,
2773 hz_select_data_sources s
2774 WHERE e.entity_name = 'HZ_'||UPPER(p_entity_name)||'_PROFILES'
2775 AND e.updated_flag = 'Y'
2776 AND e.entity_attr_id = s.entity_attr_id
2777 AND s.ranking > 0;
2778
2779 -- this cursor will return all the attribute which as data source
2780 -- from third party. It's used by the update cursor for import
2781 -- mixnmatch update is one-time update, it only needs consider
2782 -- the changed attribute, but for import, we need to consider for
2783 -- multiple time run. We nee all the attriute which has third party
2787 FROM hz_entity_attributes e,
2784 -- data source
2785 CURSOR c_imp_sources IS
2786 SELECT UNIQUE s.content_source_type
2788 hz_select_data_sources s
2789 WHERE e.entity_name = 'HZ_'||UPPER(p_entity_name)||'_PROFILES'
2790 AND e.entity_attr_id = s.entity_attr_id
2791 AND s.ranking > 0;
2792
2793 i_sources INDEXVARCHAR30List;
2794
2795 CURSOR c_deselected_sources IS
2796 SELECT s.entity_attr_id, s.content_source_type
2797 FROM hz_entity_attributes e,
2798 hz_select_data_sources s
2799 WHERE e.entity_name = 'HZ_'||UPPER(p_entity_name)||'_PROFILES'
2800 AND e.updated_flag = 'Y'
2801 AND e.entity_attr_id = s.entity_attr_id
2802 AND s.ranking = 0
2803 ORDER BY s.entity_attr_id;
2804
2805 i_deentity_attr_id INDEXIDList;
2806 i_deselected_sources INDEXVARCHAR30List;
2807
2808 CURSOR c_groups IS
2809 SELECT e.attribute_group_name, e.attribute_name
2810 FROM hz_entity_attributes e,
2811 (SELECT UNIQUE attribute_group_name
2812 FROM hz_entity_attributes
2813 WHERE entity_name = 'HZ_'||UPPER(p_entity_name)||'_PROFILES'
2814 GROUP BY attribute_group_name
2815 HAVING COUNT(*) > 1) e1
2816 WHERE e.entity_name = 'HZ_'||UPPER(p_entity_name)||'_PROFILES'
2817 AND e.attribute_group_name = e1.attribute_group_name
2818 AND e.attribute_group_name <> e.attribute_name
2819 AND e.updated_flag = 'Y'
2820 AND EXISTS (
2821 SELECT 'Y'
2822 FROM hz_select_data_sources s1
2823 WHERE s1.entity_attr_id = e.entity_attr_id
2824 -- AND s1.content_source_type <> 'USER_ENTERED'
2825 AND s1.ranking > 0 )
2826 ;
2827
2828 -- return the groups which has third party data source
2829 -- used by import
2830 CURSOR c_imp_groups IS
2831 SELECT e.attribute_group_name, e.attribute_name
2832 FROM hz_entity_attributes e,
2833 (SELECT UNIQUE attribute_group_name
2834 FROM hz_entity_attributes
2835 WHERE entity_name = 'HZ_'||UPPER(p_entity_name)||'_PROFILES'
2836 GROUP BY attribute_group_name
2837 HAVING COUNT(*) > 1) e1
2838 WHERE e.entity_name = 'HZ_'||UPPER(p_entity_name)||'_PROFILES'
2839 AND e.attribute_group_name = e1.attribute_group_name
2840 AND e.attribute_group_name <> e.attribute_name
2841 AND EXISTS (
2842 SELECT 'Y'
2843 FROM hz_select_data_sources s1
2844 WHERE s1.entity_attr_id = e.entity_attr_id
2845 -- AND s1.content_source_type <> 'USER_ENTERED'
2846 AND s1.ranking > 0 );
2847
2848
2849 i_group_name INDEXVARCHAR30List;
2850 i_uq_group_attributes INDEXVARCHAR30List;
2851
2852 l_all_attribute_names INDEXVARCHAR30List;
2853 l_data_type INDEXVARCHAR30List;
2854 l_data_length NUMBERList;
2855 l_de_column_names INDEXVARCHAR30List;
2856
2857 l_procedure_name VARCHAR2(30);
2858 l_cursor_name VARCHAR2(30);
2859 -- Bug 4171892
2860 l_prefix VARCHAR2(1000);
2861 --Bug 9768344 - Changed Str1 size 1000 to 4000
2862 str1 VARCHAR2(4000);
2863 str2 VARCHAR2(1000);
2864 str3 VARCHAR2(1000);
2865 l_start NUMBER;
2866 l_end NUMBER;
2867 l_index NUMBER;
2868 l_index1 NUMBER;
2869 m NUMBER := 0;
2870 k NUMBER := 0;
2871 l_has_party_name BOOLEAN := FALSE;
2872 l_has_dated_party_name BOOLEAN := FALSE;
2873
2874
2875 -- SSM SST Project :
2876 -- Cursor to pick attributes setup for 'By Date' method for BULK update
2877 -- local variables for generating dynamic package
2878 -- Bug 6472658 : ORDER BY Clause added.
2879 CURSOR c_date_attributes IS
2880 SELECT distinct e.attribute_name, e.entity_attr_id
2881 FROM hz_entity_attributes e,
2882 hz_select_data_sources s
2883 WHERE e.entity_name = 'HZ_'||UPPER(p_entity_name)||'_PROFILES'
2884 AND EXISTS (
2885 SELECT 'Y'
2886 FROM hz_select_data_sources s1
2887 WHERE s1.entity_attr_id = e.entity_attr_id
2888 AND s1.ranking < 0 )
2889 AND s.entity_attr_id = e.entity_attr_id
2890 AND e.updated_flag = 'Y'
2891 AND s.ranking < 0
2892 ORDER BY e.attribute_name;
2893
2894 i_attribute_group_name_date INDEXVARCHAR30List;
2895 i_primary_entity_attr_id_date INDEXIDList;
2896 i_group_attribute_name_date INDEXVARCHAR30List;
2897 i_group_entity_attr_id_date INDEXIDList;
2898 i_attribute_name_date INDEXVARCHAR30List;
2899 i_entity_attr_id_date INDEXIDList;
2900 l_index_date NUMBER;
2901 i_data_type VARCHAR2(60);
2902 l_cursor_name_date VARCHAR2(30);
2903
2904 -- SSM SST Project :
2905 -- Cursor to pick attributes setup for 'By Date' method for Import Load
2906 -- Bug 6472658 : ORDER BY Clause added.
2907 CURSOR c_date_attributes_imp IS
2908 SELECT distinct e.attribute_name, e.entity_attr_id
2909 FROM hz_entity_attributes e,
2910 hz_select_data_sources s
2911 WHERE e.entity_name = 'HZ_'||UPPER(p_entity_name)||'_PROFILES'
2912 AND EXISTS (
2913 SELECT 'Y'
2914 FROM hz_select_data_sources s1
2915 WHERE s1.entity_attr_id = e.entity_attr_id
2916 AND s1.ranking < 0 )
2920
2917 AND s.entity_attr_id = e.entity_attr_id
2918 AND s.ranking < 0
2919 ORDER BY e.attribute_name;
2921
2922 BEGIN
2923
2924 IF p_entity_name = 'organization' THEN
2925 l_procedure_name := p_purpose || 'UpdateOrgSST';
2926 l_all_attribute_names := G_O_ALL_ATTRIBUTE_NAMES;
2927 l_data_type := G_O_DATA_TYPE;
2928 l_data_length := G_O_DATA_LENGTH;
2929 l_de_column_names := G_O_DE_COLUMN_NAMES;
2930 ELSE
2931 l_procedure_name := p_purpose || 'UpdatePersonSST';
2932 l_all_attribute_names := G_P_ALL_ATTRIBUTE_NAMES;
2933 l_data_type := G_P_DATA_TYPE;
2934 l_data_length := G_P_DATA_LENGTH;
2935 l_de_column_names := G_P_DE_COLUMN_NAMES;
2936 END IF;
2937
2938 /*===============================================================
2939 + start to build the procedure
2940 +===============================================================*/
2941
2942 If UPPER(p_purpose) = 'BULK' THEN
2943 Gen_ProcedureUtilForBulk('Header',l_procedure_name);
2944 ELSE
2945 Gen_ProcedureUtilForBulk('ImportHeader',l_procedure_name);
2946 END IF;
2947
2948
2949 /*===============================================================
2950 + declare local variables
2951 +
2952 + TYPE t_branch_flag IS TABLE OF VARCHAR2(50);
2953 + i_branch_flag t_branch_flag;
2954 + TYPE t_ceo_name IS TABLE OF VARCHAR2(70);
2955 + i_ceo_name t_ceo_name;
2956 + ...
2957 +===============================================================*/
2958
2959 -- bulk load attributes' setup.
2960
2961 If UPPER(p_purpose) = 'BULK' THEN
2962 OPEN c_restricted_attributes;
2963 FETCH c_restricted_attributes BULK COLLECT INTO
2964 i_attribute_name, i_entity_attr_id,
2965 i_group_flag, i_content_source_type;
2966 CLOSE c_restricted_attributes;
2967
2968 -- SSM SST Project : fetch the 'By Date' attributes
2969 OPEN c_date_attributes;
2970 FETCH c_date_attributes BULK COLLECT INTO
2971 i_attribute_name_date, i_entity_attr_id_date;
2972 CLOSE c_date_attributes;
2973
2974 ELSE
2975 OPEN c_imp_restricted_attributes;
2976 FETCH c_imp_restricted_attributes BULK COLLECT INTO
2977 i_attribute_name, i_entity_attr_id,
2978 i_group_flag, i_content_source_type;
2979 CLOSE c_imp_restricted_attributes;
2980
2981 -- SSM SST Project : fetch the 'By Date' attributes
2982 OPEN c_date_attributes_imp;
2983 FETCH c_date_attributes_imp BULK COLLECT INTO
2984 i_attribute_name_date, i_entity_attr_id_date;
2985 CLOSE c_date_attributes_imp;
2986
2987 END IF;
2988
2989 -- SSM SST Project : If no attribute is setup, package is not required
2990 IF (i_attribute_name.COUNT = 0 AND i_attribute_name_date.COUNT = 0) THEN
2991 Gen_ProcedureUtilForBulk('NullBody', l_procedure_name);
2992 RETURN;
2993 END IF;
2994
2995 -- declare local variables
2996
2997 -- SSM SST Project : pass i_attribute_name_date as extra parameter
2998 Declare_Attributes(p_entity_name, i_attribute_name, i_attribute_name_date, i_normal_attributes);
2999
3000 li(fp('i_profile_id')||'INDEXIDlist;');
3001 li(fp('i_sst_profile_id')||'INDEXIDlist;');
3002 li(fp('i_create_update_flag')||'INDEXVARCHARlist;');
3003 li(fp('i_entity_attr_id')||'INDEXIDlist;');
3004 li(fp('i_party_id')||'INDEXIDlist;');
3005 li(fp('subtotal')||'NUMBER := 0;');
3006 li(fp('total')||'NUMBER := 0;');
3007 li(fp('create_start')||'NUMBER := 0;');
3008 li(fp('create_end')||'NUMBER := 0;');
3009 li(fp('update_start')||'NUMBER := 0;');
3010 li(fp('update_end')||'NUMBER := 0;');
3011 li(fp('j')||'NUMBER := 0;');
3012 li(fp('k')||'NUMBER := 0;');
3013
3014 IF ( UPPER(p_purpose) = 'BULK') THEN
3015 Gen_CodeForCursor('DECLARE');
3016 ELSE
3017 Gen_CodeForCursor('DECLARE', null, null, 'IMPORT');
3018 END IF;
3019 /*===============================================================
3020 + select for restricted columns (i.e. columns have been selected)
3021 +
3022 + SELECT ...
3023 + --
3024 + -- processing branch_flag.
3025 + DECODE(dnb.branch_flag, NULL,
3026 + DECODE(user_entered.branch_flag, NULL,
3027 + NULL,
3028 + user_entered.branch_flag||'%#USER_ENTERED'),
3029 + dnb.branch_flag||'%#DNB') branch_flag,
3030 + --
3031 + -- processing ceo_name.
3032 + DECODE(user_entered.ceo_name, NULL,
3033 + DECODE(dnb.ceo_name, NULL,
3034 + NULL,
3035 + dnb.ceo_name||'%#DNB'),
3036 + user_entered.ceo_name||'%#USER_ENTERED') ceo_name,
3037 + --
3038 + ...
3039 +===============================================================*/
3040
3041 l_cursor_name := 'c_entity';
3042 l_prefix := RPAD(g_indent,9+LENGTHB(g_indent));
3043
3044 -- Bug 4239549.
3045 li('CURSOR '||l_cursor_name||' IS');
3046 li(' SELECT /*+ leading(i) use_nl(sg) index(sg,hz_imp_parties_sg_n1) */');
3047 li('decode(fnd_profile.value (''HZ_PROFILE_VERSION''),''NEW_VERSION'',''C'',''NO_VERSION'',''U'',decode(trunc(sst.effective_start_date),trunc(sysdate),''U'',''C'')) create_update_flag,');
3048 l(l_prefix||'sst.'||p_entity_name||'_profile_id,');
3049 ll(l_prefix||'sst.party_id');
3050
3051 k := 0;
3052 FOR i IN 1..i_attribute_name.COUNT+1 LOOP
3053 IF i = 1 OR
3054 i = i_attribute_name.COUNT+1 OR
3055 i_attribute_name(i-1) <> i_attribute_name(i)
3056 THEN
3057 IF i > 1 THEN
3061 IF l_data_type(l_index) IN ('NUMBER','DATE') THEN
3058 l(l_prefix||'NULL,');
3059
3060 FOR j IN REVERSE l_start..l_end LOOP
3062 str1 := g_to_char; str2 := ''; str3 := ')';
3063 IF l_data_type(l_index) = 'DATE' THEN
3064 str2 := ',''YYYY/MM/DD HH:MI:SS''';
3065 END IF;
3066 ELSE
3067 str1 := ''; str2 := ''; str3 := '';
3068 END IF;
3069
3070 ll(l_prefix||str1||'d_'||LOWER(frmt_actual_content_source(i_content_source_type(j)))||'.'||
3071 LOWER(i_attribute_name(i-1))||str2||str3||'||'''||
3072 g_sep||i_content_source_type(j)||''')');
3073
3074 IF j = l_start THEN
3075 ll(' '||LOWER(i_attribute_name(i-1)));
3076 ELSE
3077 l(',');
3078 END IF;
3079 l_prefix := SUBSTRB(l_prefix, 1, LENGTHB(l_prefix)-2);
3080 END LOOP;
3081 END IF; -- IF i > 1 THEN
3082
3083 IF i = i_attribute_name.COUNT+1 THEN
3084 EXIT;
3085 END IF;
3086
3087 -- start processing a new attribute group.
3088
3089 l_start := i; l_end := i; l_prefix := RPAD(g_indent,9+LENGTHB(g_indent));
3090 IF i_group_flag(i) = 'N' THEN
3091 l_index := Get_Index(l_all_attribute_names, i_attribute_name(i));
3092 END IF;
3093 k := k + 1;
3094
3095 IF i_attribute_name(i) IN ('ORGANIZATION_NAME','PERSON_NAME') THEN
3096 l_has_party_name := true;
3097 END IF;
3098
3099 i_uq_attribute_name(k) := i_attribute_name(i);
3100 i_uq_entity_attr_id(k) := i_entity_attr_id(i);
3101 i_uq_group_flag(k) := i_group_flag(i);
3102 IF i_group_flag(i) = 'N' THEN
3103 i_uq_data_type(k) := l_data_type(l_index);
3104 ELSE
3105 i_uq_data_type(k) := 'VARCHAR2';
3106 END IF;
3107 i_uq_winner_source(k) := i_content_source_type(i);
3108
3109 l(',');
3110 l(l_prefix||'--');
3111 l(l_prefix||'-- processing '||LOWER(i_attribute_name(i))||'.');
3112 ELSE
3113 l_prefix := l_prefix||' '; l_end := l_end+1;
3114 END IF;
3115
3116 l(l_prefix||'DECODE(d_'||LOWER(frmt_actual_content_source(i_content_source_type(i)))||
3117 '.'||LOWER(i_attribute_name(i))||', NULL, ');
3118 END LOOP;
3119
3120 /*===============================================================
3121 + select normal columns (i.e. columns not in setup tables and
3122 + columns not selected)
3123 +
3124 + --
3125 + -- processing attribute1.
3126 + sst.attribute1,
3127 + --
3128 + -- processing attribute2.
3129 + sst.attribute2,
3130 + ...
3131 +===============================================================*/
3132
3133 FOR i IN 1..i_normal_attributes.COUNT LOOP
3134 l(',');
3135 l_prefix := RPAD(g_indent,9+LENGTHB(g_indent));
3136 l(l_prefix||'--');
3137 l(l_prefix||'-- processing '||LOWER(i_normal_attributes(i))||'.');
3138 ll(l_prefix||'sst.'||LOWER(i_normal_attributes(i)));
3139 END LOOP;
3140
3141 IF p_entity_name = 'organization' THEN
3142 -- select duns number
3143 l(',');
3144 l_prefix := RPAD(g_indent,9+LENGTHB(g_indent));
3145 l(l_prefix||'--');
3146 l(l_prefix||'-- processing duns_number.');
3147 ll(l_prefix||'sst.duns_number');
3148 END IF;
3149
3150 /*===============================================================
3151 + from list
3152 +
3153 + FROM hz_organization_profiles dnb,
3154 + hz_organization_profiles user_entered
3155 + ...
3156 +===============================================================*/
3157
3158 -- fetch all of content source types from setup table.
3159
3160 IF ( UPPER(p_purpose) = 'BULK') THEN
3161 OPEN c_sources;
3162 FETCH c_sources BULK COLLECT INTO i_sources;
3163 CLOSE c_sources;
3164 ELSE
3165 OPEN c_imp_sources;
3166 FETCH c_imp_sources BULK COLLECT INTO i_sources;
3167 CLOSE c_imp_sources;
3168 END IF;
3169
3170 l('');
3171 lli(' FROM hz_'||p_entity_name||'_profiles sst');
3172 FOR i IN 1..i_sources.COUNT LOOP
3173 l(',');
3174 ll(RPAD(l_prefix,7+LENGTHB(g_indent))||
3175 'hz_'||p_entity_name||'_profiles d_'||LOWER(frmt_actual_content_source(i_sources(i))));
3176 END LOOP;
3177
3178 IF ( UPPER(p_purpose) = 'IMPORT') THEN
3179 l(',');
3180 li(' hz_imp_parties_sg sg,');
3181 li(' hz_imp_parties_int i');
3182 END IF;
3183
3184 l('');
3185
3186 /*===============================================================
3187 + static where clause
3188 +===============================================================*/
3189
3190 li(' --');
3191 li(' -- use party profiles as driving table because in normal');
3192 li(' -- case the number of organization/person in hz_parties');
3193 li(' -- might be much smaller than the number of parties.');
3194 li(' --');
3195
3196 IF ( UPPER(p_purpose) = 'BULK') THEN
3197 getBulkUpdateWhereCondition;
3198 ELSE
3199 getImportUpdateWhereCondition;
3200 END IF;
3201
3202 /*
3203 li(' WHERE sst.'||'party_id BETWEEN p_from_party_id AND p_to_party_id');
3204 li(' AND sst.actual_content_source = ''SST''');
3205 li(' AND sst.effective_end_date IS NULL');
3206 */
3207 li(' -- sst profile exists for this party.');
3208 li(' --');
3209 li(' AND EXISTS (');
3213 li(' AND entity.actual_content_source = ''USER_ENTERED''');
3210 li(' SELECT ''Y''');
3211 li(' FROM hz_'||p_entity_name||'_profiles entity');
3212 li(' WHERE entity.party_id = sst.party_id');
3214 li(' AND entity.effective_end_date IS NULL )');
3215 li(' -- we use outer join for each third party data source');
3216 li(' -- in case the party does not have the specific third');
3217 li(' -- party profile.');
3218 li(' --');
3219
3220 /*===============================================================
3221 + dynamic where clause
3222 +
3223 + AND dnb.party_id (+) = sst.party_id
3224 + AND dnb.actual_content_source (+) = 'DNB'
3225 + AND dnb.effective_end_date IS NULL
3226 + ...
3227 +===============================================================*/
3228
3229 FOR i IN 1..i_sources.COUNT LOOP
3230 IF i <> 1 THEN
3231 l('');
3232 END IF;
3233 li(' AND d_'||LOWER(frmt_actual_content_source(i_sources(i)))||'.party_id (+) = sst.party_id');
3234 li(' AND d_'||LOWER(frmt_actual_content_source(i_sources(i)))||'.actual_content_source (+) = '''||i_sources(i)||'''');
3235 li(' AND d_'||LOWER(frmt_actual_content_source(i_sources(i)))||'.effective_end_date IS NULL');
3236 END LOOP;
3237 li('ORDER BY create_update_flag, sst.party_id;');
3238 l('');
3239
3240
3241 -- SSM SST Project :
3242 /*===================================
3243 * + Create Cursors for Date ranked attriubtes
3244 * +=====================================*/
3245
3246
3247 FOR i IN 1..i_attribute_name_date.COUNT LOOP
3248 date_cursor_update(i_entity_attr_id_date(i),
3249 i_attribute_name_date(i),
3250 p_entity_name,
3251 p_purpose);
3252
3253 IF i_attribute_name_date(i) in ('PERSON_NAME','ORGANIZATION_NAME')
3254 THEN
3255 l_has_dated_party_name := true;
3256 END IF;
3257
3258 END LOOP;
3259 l('');
3260
3261 /*===================================
3262 + Bug 6796587 : Create Cursor to Check if the call is coming
3263 + from setup change
3264 +=====================================*/
3265 li('CURSOR c_setup_change IS ');
3266 li(' SELECT ''Y'' ');
3267 li(' FROM hz_entity_attributes e ');
3268 li(' WHERE e.updated_flag = ''Y'' ');
3269 li(' AND rownum = 1; ');
3270 l('');
3271 li('l_call_from_setup_change VARCHAR2(10); ');
3272 l('');
3273
3274
3275 /*===============================================================
3276 + package body
3277 +===============================================================*/
3278
3279 l('BEGIN');
3280
3281 IF UPPER(p_purpose) = 'BULK' THEN
3282 Gen_ProcedureUtilForBulk('Log',l_procedure_name);
3283 ELSE
3284 Gen_ProcedureUtilForBulk('ImportLog',l_procedure_name);
3285 END IF;
3286
3287 -- Gen_ProcedureUtilForBulk('Log');
3288 l('');
3289 IF UPPER(p_purpose) = 'BULK' THEN
3290 li('reset_who;');
3291 ELSE
3292 li('reset_who(p_request_id, p_program_id, p_program_application_id);');
3293 END IF;
3294 l('');
3295 li('hz_common_pub.disable_cont_source_security;');
3296 l('');
3297 li('write_log(''open cursor '||l_cursor_name||''');');
3298 l('');
3299
3300 /*===============================================================
3301 + open cursor
3302 +===============================================================*/
3303
3304 -- SSM SST Project : Open cursors for 'By Date' attributes
3305 FOR i IN 1..i_attribute_name_date.COUNT LOOP
3306 li('OPEN '||i_attribute_name_date(i)||'_;');
3307 END LOOP;
3308
3309 IF ( UPPER(p_purpose) = 'BULK') THEN
3310 Gen_CodeForCursor('OPEN', l_cursor_name, 'Y');
3311 ELSE
3312 Gen_CodeForCursor('OPEN', l_cursor_name, 'Y', 'IMPORT');
3313 END IF;
3314
3315 li(' i_create_update_flag,');
3316 li(' i_profile_id,');
3317 lli(' i_party_id');
3318
3319 /*===============================================================
3320 + a list of pl/sql tables into which the cursor bulk collect
3321 +
3322 + i_branch_flag,
3323 + i_ceo_name,
3324 + ...
3325 +===============================================================*/
3326
3327 FOR i IN 1..i_uq_attribute_name.COUNT LOOP
3328 l(',');
3329 lli(' i_'||Format_AttributeName(i_uq_attribute_name(i)));
3330 END LOOP;
3331
3332 FOR i IN 1..i_normal_attributes.COUNT LOOP
3333 l(',');
3334 lli(' i_'||Format_AttributeName(i_normal_attributes(i)));
3335 END LOOP;
3336
3337 IF p_entity_name = 'organization' THEN
3338 -- process duns number
3339 l(',');
3340 lli(' i_duns_number');
3341 END IF;
3342
3343 l(' LIMIT rows;');
3344
3345 Gen_CodeForCursor('FETCH', l_cursor_name);
3346
3347 -- SSM SST Project : Fetch and BULK COLLECT for 'By Date' attributes
3348 FOR i IN 1..i_attribute_name_date.COUNT LOOP
3349 lli(' FETCH '||i_attribute_name_date(i)||'_ ');
3350 l('BULK COLLECT INTO');
3351 lli(' i_'||Format_AttributeName(i_attribute_name_date(i)));
3352 l(',');
3353 lli(' '||Format_AttributeName(i_attribute_name_date(i)));
3354 l(' LIMIT rows;');
3355 l('');
3356 END LOOP;
3357
3358 li(' subtotal := i_party_id.COUNT;');
3359 li(' IF subtotal = 0 AND l_last_fetch THEN');
3360 li(' EXIT;');
3361 li(' END IF;');
3362 li(' total := total + i_party_id.COUNT;');
3363 l('');
3364 li(' write_log(''fetched ''||subtotal||'' records.'');');
3368 + select attributes inside an attribute group
3365 l('');
3366
3367 /*===============================================================
3369 +===============================================================*/
3370
3371 IF ( UPPER(p_purpose) = 'BULK') THEN
3372 OPEN c_groups;
3373 FETCH c_groups BULK COLLECT INTO
3374 i_group_name, i_uq_group_attributes;
3375 CLOSE c_groups;
3376 ELSE
3377 OPEN c_imp_groups;
3378 FETCH c_imp_groups BULK COLLECT INTO
3379 i_group_name, i_uq_group_attributes;
3380 CLOSE c_imp_groups;
3381 END IF;
3382
3383 k := 0;
3384 FOR i IN 1..i_group_name.COUNT+1 LOOP
3385 IF i = 1 OR
3386 i = i_group_name.COUNT+1 OR
3387 i_group_name(i-1) <> i_group_name(i)
3388 THEN
3389 IF i > 1 THEN
3390 IF i_group_name(i-1) = 'DUNS_NUMBER_C' THEN
3391 l(',');
3392 lli(' duns_number');
3393 END IF;
3394
3395 l('');
3396 li(' INTO ');
3397
3398 FOR j IN l_start..l_end LOOP
3399 IF j <> l_start THEN
3400 l(',');
3401 END IF;
3402 lli(' i_'||Format_AttributeName(i_uq_group_attributes(j))||'(i)');
3403 END LOOP;
3404
3405 IF i_group_name(i-1) = 'DUNS_NUMBER_C' THEN
3406 l(',');
3407 lli(' i_duns_number(i)');
3408 END IF;
3409
3410 str1 := Format_AttributeName(i_group_name(i-1));
3411 l('');
3412 li(' FROM hz_'||p_entity_name||'_profiles');
3413 li(' WHERE party_id = i_party_id(i)');
3414 li(' AND effective_end_date IS NULL');
3415 li(' AND actual_content_source = '||
3416 'SUBSTRB(i_'||str1||'(i)'||
3417 ',INSTRB(i_'||str1||'(i)'||', '''||g_sep||''')+2);');
3418
3419 IF i_group_name(i-1) = 'DUNS_NUMBER_C' THEN
3420 li(' ELSE');
3421 li(' i_duns_number(i) := NULL;');
3422 END IF;
3423 li(' END IF;');
3424 li(' END LOOP;');
3425 l('');
3426
3427 END IF; -- IF i > 1 THEN
3428
3429 IF i = i_group_name.COUNT+1 THEN
3430 EXIT;
3431 END IF;
3432
3433 -- start processing a new attribute group.
3434
3435 l_start := i; l_end := i;
3436 li(' write_log(''processing group '||LOWER(i_group_name(i))||''');');
3437 l('');
3438 li(' -- process group '||LOWER(i_group_name(i)));
3439 li(' --');
3440 li(' FOR i IN 1..subtotal LOOP');
3441 li(' IF i_'||Format_AttributeName(i_group_name(i))||'(i) IS NOT NULL THEN');
3442 li(' SELECT');
3443 ELSE
3444 l(','); l_end := l_end+1;
3445 END IF;
3446
3447 lli(' DECODE('||LOWER(i_uq_group_attributes(i))||',NULL,DECODE(i_'||
3448 LOWER(i_uq_group_attributes(i))||'(i),NULL,NULL,'''||g_sep||'''||actual_content_source),'||
3449 LOWER(i_uq_group_attributes(i))||'||'''||g_sep||'''||actual_content_source)');
3450 END LOOP;
3451
3452 /*===============================================================
3453 + put profile ids into 2 table. 1 for create and another for update
3454 +===============================================================*/
3455
3456 li(' create_start := 0; create_end := -1; ');
3457 li(' update_start := 0; update_end := -1; ');
3458 l('');
3459 li(' FOR i IN 1..subtotal LOOP');
3460 li(' IF i_create_update_flag(i) = ''C'' THEN');
3461 li(' IF create_start = 0 THEN create_start := i; END IF;');
3462 li(' ELSE');
3463 li(' IF update_start = 0 THEN ');
3464 li(' update_start := i; ');
3465 li(' IF create_start > 0 THEN create_end := i-1; END IF;');
3466 li(' END IF;');
3467 li(' EXIT;');
3468 li(' END IF;');
3469 li(' END LOOP;');
3470 li(' IF create_start > 0 AND create_end = -1 THEN create_end := subtotal; END IF;');
3471 li(' IF update_start > 0 AND update_end = -1 THEN update_end := subtotal; END IF;');
3472 l('');
3473 li(' write_log((create_end - create_start)||'' record(s) need to be created.'');');
3474 li(' write_log((update_end - update_start)||'' record(s) need to be updated.'');');
3475 l('');
3476
3477 /*===============================================================
3478 + reset effective_end_date
3479 +===============================================================*/
3480
3481 li(' write_log(''end sst profiles which were not created in the same day...'');');
3482 l('');
3483 li(' -- reset effective_end_date.');
3484 l('');
3485 li(' FORALL i IN create_start..create_end');
3486 li(' UPDATE hz_'||p_entity_name||'_profiles ');
3487 li(' SET effective_end_date = TRUNC(SYSDATE-1)');
3488 li(' WHERE '||p_entity_name||'_profile_id = i_profile_id(i);');
3489 l('');
3490
3491 IF UPPER(p_purpose) = 'BULK' THEN
3492 li(' IF (create_end-create_start) > 0 THEN');
3493 Gen_CodeForCommit(p_prefix => ' ');
3494 li(' END IF;');
3495 l('');
3496 END IF;
3497
3498 /*===============================================================
3499 + create sst profiles
3500 +===============================================================*/
3501
3502 li(' write_log(''create new sst profiles for those parties whose sst profiles were end-dated...'');');
3503 l('');
3504 li(' -- insert sst profiles');
3505 l('');
3506 li(' FORALL i IN create_start..create_end');
3507 li(' INSERT INTO hz_'||p_entity_name||'_profiles (');
3508
3512 l(',');
3509 Gen_CodeForWhoColumns('I','LIST',g_indent||' ','Y');
3510 l(',');
3511 Gen_CodeForOtherAttributes ('LIST',g_indent||' ');
3513 li(' '||p_entity_name||'_profile_id,');
3514 li(' party_id,');
3515 li(' effective_start_date,');
3516 lli(' object_version_number');
3517
3518 /*===============================================================
3519 + column list of insert statement
3520 +
3521 + duns_number_c,
3522 + employees_total,
3523 + ...
3524 +===============================================================*/
3525
3526 FOR i IN 1..i_uq_attribute_name.COUNT LOOP
3527 l(',');
3528 lli(' '||LOWER(i_uq_attribute_name(i)));
3529 END LOOP;
3530
3531 FOR i IN 1..i_normal_attributes.COUNT LOOP
3532 l(',');
3533 lli(' '||LOWER(i_normal_attributes(i)));
3534 END LOOP;
3535
3536 -- SSM SST Project : Add date attributes in insert statement
3537 FOR i IN 1..i_attribute_name_date.COUNT LOOP
3538 l(',');
3539 lli(' '||LOWER(i_attribute_name_date(i)));
3540 END LOOP;
3541
3542
3543 IF p_entity_name = 'organization' THEN
3544 l(',');
3545 lli(' duns_number');
3546 END IF;
3547
3548 li(' ) VALUES (');
3549
3550 /*===============================================================
3551 + value list of insert statement
3552 +
3553 + SUBSTRB(i_duns_number_c(i),1,INSTRB(i_duns_number_c(i),'%#')-1),
3554 + TO_NUMBER(SUBSTRB(i_employees_total(i),1, INSTRB(i_employees_total(i),'%#')-1)),
3555 + ...
3556 +===============================================================*/
3557
3558 Gen_CodeForWhoColumns('I','',g_indent||' ','Y');
3559 l(',');
3560 Gen_CodeForOtherAttributes ('',g_indent||' ');
3561 l(',');
3562 li(' hz_'||p_entity_name||'_profiles_s.nextval,');
3563 li(' i_party_id(i),');
3564 li(' SYSDATE,');
3565 lli(' 1');
3566
3567 FOR i IN 1..i_uq_attribute_name.COUNT LOOP
3568 l(',');
3569
3570 str1 := Format_AttributeName(i_uq_attribute_name(i));
3571
3572 IF i_uq_data_type(i) = 'VARCHAR2' THEN
3573 str2 := ''; str3 := '';
3574 ELSIF i_uq_data_type(i) = 'NUMBER' THEN
3575 str2 := g_to_number; str3 := ')';
3576 ELSIF i_uq_data_type(i) = 'DATE' THEN
3577 str2 := g_to_date; str3 := ',''YYYY/MM/DD HH:MI:SS'')';
3578 END IF;
3579
3580 lli(' '||str2||'SUBSTRB(i_'||str1||'(i),1,'||
3581 'INSTRB(i_'||str1||'(i),'''||g_sep||''')-1)'||str3);
3582 END LOOP;
3583
3584 FOR i IN 1..i_normal_attributes.COUNT LOOP
3585 l(',');
3586 lli(' i_'||Format_AttributeName(i_normal_attributes(i))||'(i)');
3587 END LOOP;
3588
3589 -- SSM SST Project : Values clause for 'By Date' attributes
3590 FOR i IN 1..i_attribute_name_date.COUNT LOOP
3591 l(',');
3592 lli(' i_'||Format_AttributeName(i_attribute_name_date(i))||'(i)');
3593 END LOOP;
3594
3595
3596 IF p_entity_name = 'organization' THEN
3597 -- process duns number
3598 l(',');
3599 li(' i_duns_number(i)');
3600 END IF;
3601
3602 li(' ) RETURNING '||p_entity_name||'_profile_id BULK COLLECT INTO i_sst_profile_id;');
3603 l('');
3604 --Bug10365551
3605 --In ImportUpdatePersonSST procedure person_name update is added.
3606 IF UPPER(p_purpose) = 'IMPORT' AND UPPER(p_entity_name) = 'PERSON' THEN
3607 l('');
3608 li('--Bug10365551');
3609 li('--Updating person_name with processed values');
3610 li(' l_profile_fmt_bkwd_compatible := nvl(fnd_profile.value('||''''||'HZ_FMT_BKWD_COMPATIBLE'||''''||'),'||''''||'Y'||''''||');');
3611 li(' IF l_profile_fmt_bkwd_compatible = '||''''||'Y'||''''||' THEN ');
3612 li(' FORALL i IN 1..i_sst_profile_id.COUNT');
3613 li(' UPDATE hz_person_profiles');
3614 li(' SET');
3615 li(' person_name =');
3616 li(' person_title'||' ||');
3617 li(' DECODE(person_title, null, null, '||''''||' '||''''||') ||');
3618 li(' person_first_name'||' || ');
3619 li(' DECODE(person_first_name, null, null, DECODE(person_middle_name, null, DECODE(person_last_name, null, null,'||''''||' '||''''||'), '||''''||' '||''''||')) ||');
3620 li(' person_middle_name || DECODE(person_middle_name, null, null, DECODE(person_last_name, null, null, '||''''||' '||''''||')) ||');
3621 li(' person_last_name || ');
3622 li(' DECODE(person_name_suffix, null, null, '||''''||' '||''''||') ||');
3623 li(' person_name_suffix ');
3624 li(' WHERE person_profile_id = i_sst_profile_id(i);');
3625 li(' END IF;');
3626 l('');
3627 END IF;
3628
3629 IF UPPER(p_purpose) = 'BULK' THEN
3630 li(' IF (create_end-create_start) > 0 THEN');
3631 Gen_CodeForCommit(p_prefix => ' ');
3632 li(' END IF;');
3633 l('');
3634 END IF;
3635
3636 /*===============================================================
3637 + update sst profiles
3638 +===============================================================*/
3639
3640 li(' write_log(''update sst profiles.'');');
3641 l('');
3642 li(' -- update sst profiles');
3643 l('');
3644 li(' FORALL i IN update_start..update_end');
3645 li(' UPDATE hz_'||p_entity_name||'_profiles');
3646 li(' SET');
3647 Gen_CodeForWhoColumns('U','',g_indent||' ','Y');
3648
3649 FOR i IN 1..i_uq_attribute_name.COUNT LOOP
3650 l(',');
3651
3652 str1 := Format_AttributeName(i_uq_attribute_name(i));
3653
3654 IF i_uq_data_type(i) = 'VARCHAR2' THEN
3658 ELSIF i_uq_data_type(i) = 'DATE' THEN
3655 str2 := ''; str3 := '';
3656 ELSIF i_uq_data_type(i) = 'NUMBER' THEN
3657 str2 := g_to_number; str3 := ')';
3659 str2 := g_to_date; str3 := ',''YYYY/MM/DD HH:MI:SS'')';
3660 END IF;
3661
3662 -- Bug 3613620: replaced str1 with LOWER(i_uq_attribute_name(i))
3663
3664 lli(' '||LOWER(i_uq_attribute_name(i))||' = '||str2||'SUBSTRB(i_'||str1||'(i),1,'||
3665 'INSTRB(i_'||str1||'(i),'''||g_sep||''')-1)'||str3);
3666 END LOOP;
3667
3668 -- SSM SST Project : Denormalize 'By Date' columns
3669 FOR i IN 1..i_attribute_name_date.COUNT LOOP
3670 l(',');
3671 lli(' '||LOWER(i_attribute_name_date(i))||' = ');
3672 ll('i_'||Format_AttributeName(i_attribute_name_date(i))||'(i)');
3673 END LOOP;
3674
3675
3676 IF p_entity_name = 'organization' THEN
3677 -- process duns number
3678 l(',');
3679 lli(' duns_number = i_duns_number(i)');
3680 END IF;
3681
3682 l('');
3683 li(' WHERE '||p_entity_name||'_profile_id = i_profile_id(i);');
3684 l('');
3685 --Bug10365551
3686 --In ImportUpdatePersonSST API person_name update is added.
3687 IF UPPER(p_purpose) = 'IMPORT' AND UPPER(p_entity_name) = 'PERSON' THEN
3688 l('');
3689 li('--Bug10365551');
3690 li('--Updating person_name with processed values');
3691 li(' IF l_profile_fmt_bkwd_compatible = '||''''||'Y'||''''||' THEN ');
3692 li(' FORALL i IN update_start..update_end');
3693 li(' UPDATE hz_person_profiles');
3694 li(' SET');
3695 li(' person_name =');
3696 li(' person_title'||' ||');
3697 li(' DECODE(person_title, null, null, '||''''||' '||''''||') ||');
3698 li(' person_first_name'||' || ');
3699 li(' DECODE(person_first_name, null, null, DECODE(person_middle_name, null, DECODE(person_last_name, null, null,'||''''||' '||''''||'), '||''''||' '||''''||')) ||');
3700 li(' person_middle_name || DECODE(person_middle_name, null, null, DECODE(person_last_name, null, null, '||''''||' '||''''||')) ||');
3701 li(' person_last_name || ');
3702 li(' DECODE(person_name_suffix, null, null, '||''''||' '||''''||') ||');
3703 li(' person_name_suffix ');
3704 li(' WHERE person_profile_id = i_profile_id(i);');
3705 li(' END IF;');
3706 l('');
3707 END IF;
3708
3709 IF UPPER(p_purpose) = 'BULK' THEN
3710 li(' IF (update_end-update_start) > 0 THEN');
3711 Gen_CodeForCommit(p_prefix => ' ');
3712 li(' END IF;');
3713 l('');
3714 END IF;
3715
3716 /*===============================================================
3717 + denormalize sst to hz_parties (restricted columns only)
3718 +
3719 + duns_number_c =
3720 + SUBSTRB(i_duns_number_c(i),1,INSTRB(i_duns_number_c(i),'%#')-1),
3721 + employees_total =
3722 + TO_NUMBER(SUBSTRB(i_employees_total(i),1, INSTRB(i_employees_total(i),'%#')-1)),
3723 + ...
3724 +===============================================================*/
3725
3726 li(' write_log(''update denormalized columns in hz_parties.'');');
3727 l('');
3728
3729 li(' -- update denormalized columns in hz_parties.');
3730 l('');
3731 li(' FORALL i IN 1..subtotal');
3732 li(' UPDATE hz_parties');
3733 li(' SET');
3734
3735 Gen_CodeForWhoColumns('U','',g_indent||' ','Y');
3736
3737 FOR i IN 1..l_all_attribute_names.COUNT LOOP
3738 IF l_de_column_names(i) IS NOT NULL THEN
3739 l_index := Get_Index(i_uq_attribute_name, l_all_attribute_names(i));
3740 l_index_date := Get_Index(i_attribute_name_date, l_all_attribute_names(i));
3741
3742 IF l_index > 0 THEN
3743 l(',');
3744 lli(' '||LOWER(l_all_attribute_names(i))||' = ');
3745
3746 str1 := Format_AttributeName(l_all_attribute_names(i));
3747
3748 IF i_uq_data_type(l_index) = 'VARCHAR2' THEN
3749 str2 := ''; str3 := '';
3750 ELSIF i_uq_data_type(l_index) = 'NUMBER' THEN
3751 str2 := g_to_number; str3 := ')';
3752 ELSIF i_uq_data_type(l_index) = 'DATE' THEN
3753 str2 := g_to_date; str3 := ',''YYYY/MM/DD HH:MI:SS'')';
3754 END IF;
3755
3756 ll(str2||'SUBSTRB(i_'||str1||'(i),1,'||
3757 'INSTRB(i_'||str1||'(i),'''||g_sep||''')-1)'||str3);
3758 END IF;
3759
3760 -- SSM SST Project : Denormalize 'By Date' columns
3761 IF l_index_date > 0 THEN
3762 l(',');
3763 lli(' '||LOWER(l_all_attribute_names(i))||' = ');
3764 ll('i_'||Format_AttributeName(l_all_attribute_names(i))||'(i)');
3765 END IF;
3766 END IF;
3767 END LOOP;
3768
3769 /*===============================================================
3770 + denormalize duns_number into hz_parties
3771 +===============================================================*/
3772
3773 IF p_entity_name = 'organization' THEN
3774 l(',');
3775 lli(' duns_number = i_duns_number(i)');
3776 END IF;
3777
3778 /*===============================================================
3779 + update party_name, customer_key in hz_parties
3780 +===============================================================*/
3781
3782 IF l_has_party_name THEN
3783 Gen_PartyName (p_entity_name);
3784 END IF;
3785
3786 IF l_has_dated_party_name THEN
3787 IF p_entity_name = 'organization' THEN
3788 l(',');
3789 lli(' party_name = i_organization_name(i)');
3790 ELSIF p_entity_name = 'person' THEN
3791 l(',');
3792 lli(' party_name = i_person_name(i)');
3793 END IF;
3797 l('');
3794 END IF;
3795
3796 li(' WHERE party_id = i_party_id(i);');
3798 --Bug10365551
3799 --In ImportUpdatePersonSST API party_name update is added.
3800 IF UPPER(p_purpose) = 'IMPORT' AND UPPER(p_entity_name) = 'PERSON' THEN
3801 l('');
3802 li('--Bug10365551');
3803 li('--Updating party_name with processed values');
3804 li(' IF l_profile_fmt_bkwd_compatible = '||''''||'Y'||''''||' THEN ');
3805 li(' FORALL i IN 1..subtotal');
3806 li(' UPDATE hz_parties');
3807 li(' SET party_name = ');
3808 li(' person_title'||' ||');
3809 li(' DECODE(person_title, null, null, '||''''||' '||''''||') ||');
3810 li(' person_first_name'||' || ');
3811 li(' DECODE(person_first_name, null, null, DECODE(person_middle_name, null, DECODE(person_last_name, null, null,'||''''||' '||''''||'), '||''''||' '||''''||')) ||');
3812 li(' person_middle_name || DECODE(person_middle_name, null, null, DECODE(person_last_name, null, null, '||''''||' '||''''||')) ||');
3813 li(' person_last_name || ');
3814 li(' DECODE(person_name_suffix, null, null, '||''''||' '||''''||') ||');
3815 li(' person_name_suffix ');
3816 li(' WHERE party_id = i_party_id(i);');
3817 li(' END IF;');
3818 END IF;
3819 IF UPPER(p_purpose) = 'BULK' THEN
3820 Gen_CodeForCommit;
3821 END IF;
3822
3823 /*===============================================================
3824 + update party_name of relationship parties
3825 +===============================================================*/
3826
3827 IF l_has_party_name THEN
3828 Gen_RelationshipPartyName;
3829 IF UPPER(p_purpose) = 'BULK' THEN
3830 Gen_CodeForCommit(false);
3831 END IF;
3832 END IF;
3833
3834 /*===============================================================
3835 + update exception table to track attributes' data source
3836 +===============================================================*/
3837
3838 li(' write_log(''update exception table.'');');
3839 l('');
3840
3841 -- SSM SST Project :
3842 -- Delete records from exception talbe for all attributes
3843 -- setup 'By Date' and not having exception type = MRR
3844
3845
3846 li(' -- Bug 6913856 : Check if the call is coming from setup change ');
3847 li(' -- We need to delete only if there is setup change. If no setup change');
3848 li(' -- and call is from regular processing API, then no DELETE is required');
3849 li(' OPEN c_setup_change; ');
3850 li(' FETCH c_setup_change INTO l_call_from_setup_change; ');
3851 li(' IF c_setup_change%NOTFOUND THEN ');
3852 li(' l_call_from_setup_change := ''N''; ');
3853 li(' Write_Log(''NO setup for any entity / attribute has been updated. DELETE hz_win_source_exceps call not required.''); ');
3854 li(' END IF; ');
3855 li(' CLOSE c_setup_change; ');
3856 l('');
3857
3858 FOR j IN 1..i_entity_attr_id_date.COUNT LOOP
3859 li(' IF l_call_from_setup_change = ''Y'' THEN ');
3860 li(' Write_Log(''Deleting hz_win_source_exceps for entity_attr_id :'||i_entity_attr_id_date(j)||' '');' );
3861 l('');
3862 li(' DELETE hz_win_source_exceps ');
3863 li(' WHERE party_id BETWEEN i_party_id(1) AND i_party_id(subtotal) ');
3864 li(' AND exception_type <> ''MRR''');
3865 li(' AND entity_attr_id = '||i_entity_attr_id_date(j)||';');
3866 l('');
3867 li(' END IF; ');
3868 END LOOP;
3869
3870 OPEN c_deselected_sources;
3871 FETCH c_deselected_sources BULK COLLECT INTO i_deentity_attr_id, i_deselected_sources;
3872 CLOSE c_deselected_sources;
3873
3874 FOR i IN 1..i_uq_entity_attr_id.COUNT LOOP
3875 IF i_uq_group_flag(i) = 'N' THEN
3876
3877 -- SSM SST Project :
3878 -- Delete records from exception talbe for all attributes
3879 -- setup 'By Rank' and having exception type = MRR
3880 /* Bug 4228765 : Donot delete MRR records here as it is
3881 * deleted in main conc program earlier
3882 li(' DELETE hz_win_source_exceps ');
3883 li(' WHERE party_id BETWEEN i_party_id(1) AND i_party_id(subtotal) ');
3884 li(' AND exception_type = ''MRR''');
3885 li(' AND entity_attr_id = '||i_uq_entity_attr_id(i)||';');
3886 l('');
3887 */
3888 li(' update_exception_table(''U'','||
3889 'i_party_id,'''||i_uq_attribute_name(i)||''','||
3890 i_uq_entity_attr_id(i)||','||
3891 'i_'||Format_AttributeName(i_uq_attribute_name(i))||','''||
3892 i_uq_winner_source(i)||''');');
3893 l('');
3894 END IF;
3895 END LOOP;
3896
3897 FOR i IN 1..i_deentity_attr_id.COUNT+1 LOOP
3898 IF i = 1 OR
3899 i = i_deentity_attr_id.COUNT+1 OR
3900 i_deentity_attr_id(i-1) <> i_deentity_attr_id(i)
3901 THEN
3902 IF i > 1 THEN
3903 li('IF l_call_from_setup_change = ''Y'' THEN ');
3904 li('Write_Log(''Deleting hz_win_source_exceps for entity_attr_id :'||i_deentity_attr_id(i-1)||' '');' );
3905 l('');
3906 li(' DELETE hz_win_source_exceps ');
3907 li(' WHERE party_id BETWEEN i_party_id(1) AND i_party_id(subtotal) ');
3908 li(' AND content_source_type IN ('||substrb(str1,2)||')');
3909 li(' AND entity_attr_id = '||i_deentity_attr_id(i-1)||';');
3910 l('');
3911 li('END IF; ');
3912 END IF; -- IF i > 1 THEN
3913
3914 IF i = i_deentity_attr_id.COUNT+1 THEN
3915 EXIT;
3916 END IF;
3917
3918 str1 := '';
3919 END IF;
3920 -- IF str1 <> '' THEN
3921 str1 := str1||',';
3922 -- END IF;
3923 str1 := str1||''''||i_deselected_sources(i)||'''';
3924 END LOOP;
3925
3929 'i_party_id,'''||i_attribute_name_date(i)||''','||
3926 -- SSM SST Project : Update exceptions table for 'By Date' attributes
3927 FOR i IN 1..i_attribute_name_date.COUNT LOOP
3928 li(' update_exception_table_date(''U'','||
3930 i_entity_attr_id_date(i)||','||
3931 Format_AttributeName(i_attribute_name_date(i))||');');
3932 -- 'i_actual_content_source);');
3933 l('');
3934 END LOOP;
3935
3936 -- SSM SST Project : Delete the entries from user overwrite rules
3937 FOR i IN 1..i_attribute_name_date.COUNT LOOP
3938 li(' delete hz_user_overwrite_rules where entity_attr_id = ' || i_entity_attr_id_date(i)||' ;');
3939 l('');
3940 END LOOP;
3941
3942 IF UPPER(p_purpose) = 'BULK' THEN
3943 Gen_CodeForCommit(false);
3944 END IF;
3945
3946 /*===============================================================
3947 + write output file
3948 +===============================================================*/
3949
3950 Gen_WriteOutputFileForUpdate;
3951
3952 /*===============================================================
3953 + update DQM interface table
3954 +
3955 + As per talk with Srini, right now DQM can not handle huge
3956 + data in interface table. User should run DQM sync program
3957 + after they run this program for mix-n-match.
3958 +===============================================================*/
3959 /*
3960 IF hz_dqm_search_util.is_dqm_available = 'T' THEN
3961 Gen_CodeForDQM(p_entity_name);
3962 END IF;
3963 */
3964
3965 /*===============================================================
3966 + close cursor
3967 +===============================================================*/
3968
3969
3970 IF ( UPPER(p_purpose) = 'BULK') THEN
3971 Gen_CodeForCursor('CLOSE', l_cursor_name);
3972 ELSE
3973 Gen_CodeForCursor('CLOSE', l_cursor_name, null, 'IMPORT');
3974 END IF;
3975 -- SSM SST Project : Close 'By Date' cursors
3976 FOR i IN 1..i_attribute_name_date.COUNT LOOP
3977 li('CLOSE '||i_attribute_name_date(i)||'_;');
3978 END LOOP;
3979 l('');
3980 li('write_out(total||'' sst profile(s) was(were) updated.'');');
3981 l('');
3982 li('write_log(total||'' sst profile(s) was(were) successfully updated.'');');
3983 l('END '||l_procedure_name||';');
3984 l('');
3985
3986 END Gen_BulkUpdateSST;
3987
3988 /**
3989 * PRIVATE PROCEDURE
3990 *
3991 * DESCRIPTION
3992 *
3993 * MODIFICATION HISTORY
3994 *
3995 */
3996
3997 PROCEDURE Gen_CommonProceduresForAPI IS
3998 BEGIN
3999
4000 /*===============================================================
4001 + declare global type and variables
4002 +===============================================================*/
4003
4004 l('TYPE INDEXEDVARCHARLIST IS TABLE OF VARCHAR2(1000) INDEX BY BINARY_INTEGER;');
4005 l('');
4006
4007 END Gen_CommonProceduresForAPI;
4008
4009 /**
4010 * PRIVATE PROCEDURE
4011 *
4012 * DESCRIPTION
4013 *
4014 * MODIFICATION HISTORY
4015 *
4016 */
4017
4018 PROCEDURE Gen_UpdateSSTRecord (
4019 p_entity_name IN VARCHAR2
4020 ) IS
4021
4022 CURSOR c_restricted_attributes IS
4023 SELECT e.attribute_name, e.entity_attr_id,
4024 s.content_source_type
4025 FROM hz_entity_attributes e,
4026 hz_select_data_sources s
4027 WHERE e.entity_name = 'HZ_'||UPPER(p_entity_name)||'_PROFILES'
4028 AND e.attribute_name NOT IN ('BANK_CODE','BANK_OR_BRANCH_NUMBER','BRANCH_CODE')
4029 AND EXISTS (
4030 SELECT 'Y'
4031 FROM hz_select_data_sources s1
4032 WHERE s1.entity_attr_id = e.entity_attr_id
4033 AND s1.content_source_type <> 'USER_ENTERED'
4034 AND s1.ranking > 0 )
4035 AND s.entity_attr_id = e.entity_attr_id
4036 AND s.ranking > 0
4037 ORDER BY e.attribute_name, s.ranking;
4038
4039 i_attribute_name INDEXVARCHAR30List;
4040 i_uq_attribute_name INDEXVARCHAR30List;
4041 i_entity_attr_id INDEXIDList;
4042 i_uq_entity_attr_id INDEXIDList;
4043 i_content_source_type INDEXVARCHAR30List;
4044 i_uq_data_type INDEXVARCHAR60List;
4045 i_uq_winner_source INDEXVARCHAR30List;
4046 i_normal_attributes INDEXVARCHAR30List;
4047
4048 CURSOR c_sources IS
4049 SELECT UNIQUE s.content_source_type
4050 FROM hz_entity_attributes e,
4051 hz_select_data_sources s
4052 WHERE e.entity_name = 'HZ_'||UPPER(p_entity_name)||'_PROFILES'
4053 AND e.attribute_name NOT IN ('BANK_CODE','BANK_OR_BRANCH_NUMBER','BRANCH_CODE')
4054 AND e.entity_attr_id = s.entity_attr_id
4055 AND s.ranking > 0;
4056
4057 i_sources INDEXVARCHAR30List;
4058
4059 l_all_attribute_names INDEXVARCHAR30List;
4060 l_data_type INDEXVARCHAR30List;
4061
4062 CURSOR c_restricted_groups IS
4063 SELECT e.attribute_group_name,
4064 DECODE(e.attribute_group_name, 'PERSON_NAME',
4065 DECODE(e1.attribute_name, 'PERSON_LAST_NAME',
4066 e1.entity_attr_id, null),
4067 e1.attribute_name,
4068 e1.entity_attr_id, null) primary_entity_attr_id,
4069 e1.attribute_name,
4070 e1.entity_attr_id
4071 FROM
4072 (SELECT attribute_group_name
4073 FROM hz_entity_attributes
4074 WHERE entity_name = 'HZ_'||UPPER(p_entity_name)||'_PROFILES'
4075 GROUP BY attribute_group_name
4079 WHERE e1.attribute_group_name = e.attribute_group_name
4076 HAVING COUNT(entity_attr_id) > 1
4077 ) e,
4078 hz_entity_attributes e1
4080 AND e1.attribute_name NOT IN ('BANK_CODE','BANK_OR_BRANCH_NUMBER','BRANCH_CODE')
4081 AND EXISTS (
4082 SELECT 'Y'
4083 FROM hz_select_data_sources s1
4084 WHERE s1.entity_attr_id = e1.entity_attr_id
4085 AND s1.content_source_type <> 'USER_ENTERED'
4086 AND s1.ranking <> 0 )
4087 ORDER BY e.attribute_group_name, primary_entity_attr_id;
4088
4089 i_attribute_group_name INDEXVARCHAR30List;
4090 i_primary_entity_attr_id INDEXIDList;
4091 i_group_attribute_name INDEXVARCHAR30List;
4092 i_group_entity_attr_id INDEXIDList;
4093
4094 l_procedure_name VARCHAR2(30);
4095 -- Bug 4171892
4096 l_prefix VARCHAR2(1000);
4097 str1 VARCHAR2(1000);
4098 str2 VARCHAR2(1000);
4099 str3 VARCHAR2(1000);
4100 l_index NUMBER;
4101 k NUMBER := 0;
4102 j NUMBER := 0;
4103 l_start NUMBER := 0;
4104 l_end NUMBER := 0;
4105 l_group_id NUMBER;
4106 l_index1 NUMBER := -1;
4107 l_index2 NUMBER := -1;
4108 -- Bug 6472658 : ORDER BY Clause added.
4109 CURSOR c_date_attributes IS
4110 SELECT distinct e.attribute_name, e.entity_attr_id
4111 FROM hz_entity_attributes e,
4112 hz_select_data_sources s
4113 WHERE e.entity_name = 'HZ_'||UPPER(p_entity_name)||'_PROFILES'
4114 AND e.attribute_name NOT IN ('BANK_CODE','BANK_OR_BRANCH_NUMBER','BRANCH_CODE')
4115 AND EXISTS (
4116 SELECT 'Y'
4117 FROM hz_select_data_sources s1
4118 WHERE s1.entity_attr_id = e.entity_attr_id
4119 AND s1.ranking < 0 )
4120 AND s.entity_attr_id = e.entity_attr_id
4121 AND s.ranking < 0
4122 ORDER BY e.attribute_name;
4123 -- ORDER BY e.attribute_name, s.ranking;
4124 /*
4125 CURSOR c_date_group_name IS
4126 SELECT e.attribute_group_name,
4127 DECODE(e.attribute_group_name, 'PERSON_NAME',
4128 DECODE(e1.attribute_name, 'PERSON_LAST_NAME',
4129 e1.entity_attr_id, null),
4130 e1.attribute_name,
4131 e1.entity_attr_id, null) primary_entity_attr_id,
4132 e1.attribute_name,
4133 e1.entity_attr_id
4134 FROM
4135 (SELECT attribute_group_name
4136 FROM hz_entity_attributes
4137 WHERE entity_name = 'HZ_'||UPPER(p_entity_name)||'_PROFILES'
4138 GROUP BY attribute_group_name
4139 HAVING COUNT(entity_attr_id) > 1
4140 ) e,
4141 hz_entity_attributes e1
4142 WHERE e1.attribute_group_name = e.attribute_group_name
4143 AND EXISTS (
4144 SELECT 'Y'
4145 FROM hz_select_data_sources s1
4146 WHERE s1.entity_attr_id = e1.entity_attr_id
4147 AND s1.ranking < 0 )
4148 ORDER BY e.attribute_group_name, primary_entity_attr_id;
4149 */
4150 i_attribute_group_name_date INDEXVARCHAR30List;
4151 i_primary_entity_attr_id_date INDEXIDList;
4152 i_group_attribute_name_date INDEXVARCHAR30List;
4153 i_group_entity_attr_id_date INDEXIDList;
4154 i_attribute_name_date INDEXVARCHAR30List;
4155 i_entity_attr_id_date INDEXIDList;
4156 l_index_date NUMBER;
4157 i_data_type VARCHAR2(60);
4158
4159
4160 BEGIN
4161
4162 IF p_entity_name = 'organization' THEN
4163 l_all_attribute_names := G_O_ALL_ATTRIBUTE_NAMES;
4164 l_data_type := G_O_DATA_TYPE;
4165 ELSE
4166 l_all_attribute_names := G_P_ALL_ATTRIBUTE_NAMES;
4167 l_data_type := G_P_DATA_TYPE;
4168 END IF;
4169
4170 OPEN c_restricted_attributes;
4171 FETCH c_restricted_attributes BULK COLLECT INTO
4172 i_attribute_name, i_entity_attr_id,
4173 i_content_source_type;
4174 CLOSE c_restricted_attributes;
4175
4176 /*===============================================================
4177 + procedure for setting SST record
4178 +===============================================================*/
4179
4180 IF p_entity_name = 'organization' THEN
4181 l_procedure_name := 'selectOrgHighestRankedValue';
4182 ELSE
4183 l_procedure_name := 'selectPerHighestRankedValue';
4184 END IF;
4185
4186 l('PROCEDURE '||l_procedure_name||' (');
4187 li(fp('p_party_id')||'IN NUMBER,');
4188 li(fp('x_highest_ranked_value')||'OUT NOCOPY INDEXEDVARCHARLIST,');
4189 li(fp('x_highest_ranked_source')||'OUT NOCOPY HZ_MIXNM_UTILITY.INDEXVARCHAR30List');
4190 l(') IS');
4191 l(' i NUMBER;');
4192 l('BEGIN');
4193
4194 IF i_attribute_name.COUNT = 0 THEN
4195 li('NULL;');
4196 ELSE
4197
4198 li('SELECT ');
4199
4200 k := 0;
4201 FOR i IN 1..i_attribute_name.COUNT+1 LOOP
4202 IF i = 1 OR
4203 i = i_attribute_name.COUNT+1 OR
4204 i_attribute_name(i-1) <> i_attribute_name(i)
4205 THEN
4206 IF i > 1 THEN
4207 l(l_prefix||'NULL,');
4208
4209 FOR j IN REVERSE l_start..l_end LOOP
4210 IF l_data_type(l_index) IN ('NUMBER','DATE') THEN
4211 str1 := g_to_char; str2 := ''; str3 := ')';
4212 IF l_data_type(l_index) = 'DATE' THEN
4213 str2 := ',''YYYY/MM/DD HH:MI:SS''';
4214 END IF;
4215 ELSE
4216 str1 := ''; str2 := ''; str3 := '';
4220 LOWER(i_attribute_name(i-1))||str2||str3||'||'''||
4217 END IF;
4218
4219 ll(l_prefix||str1||'d_'||LOWER(frmt_actual_content_source(i_content_source_type(j)))||'.'||
4221 g_sep||i_content_source_type(j)||''')');
4222
4223 IF j = l_start THEN
4224 ll(' '||LOWER(i_attribute_name(i-1)));
4225 ELSE
4226 l(',');
4227 END IF;
4228 l_prefix := SUBSTRB(l_prefix, 1, LENGTHB(l_prefix)-2);
4229 END LOOP;
4230 END IF; -- IF i > 1 THEN
4231
4232 IF i = i_attribute_name.COUNT+1 THEN
4233 EXIT;
4234 END IF;
4235
4236 IF i > 1 THEN
4237 l(',');
4238 END IF;
4239
4240 -- start processing a new attribute.
4241
4242 l_start := i; l_end := i; l_prefix := RPAD(g_indent,5+LENGTHB(g_indent));
4243 l_index := Get_Index(l_all_attribute_names, i_attribute_name(i));
4244 k := k + 1;
4245 i_uq_attribute_name(k) := i_attribute_name(i);
4246 i_uq_entity_attr_id(k) := i_entity_attr_id(i);
4247 IF l_data_type(l_index) = 'VARCHAR2' THEN
4248 i_uq_data_type(k) := 'fnd_api.g_miss_char';
4249 ELSIF l_data_type(l_index) = 'NUMBER' THEN
4250 i_uq_data_type(k) := 'fnd_api.g_miss_num';
4251 ELSIF l_data_type(l_index) = 'DATE' THEN
4252 i_uq_data_type(k) := 'fnd_api.g_miss_date';
4253 END IF;
4254
4255 l(l_prefix||'--');
4256 l(l_prefix||'-- processing '||LOWER(i_attribute_name(i))||'.');
4257 ELSE
4258 l_prefix := l_prefix||' '; l_end := l_end+1;
4259 END IF;
4260
4261 l(l_prefix||'DECODE(d_'||LOWER(frmt_actual_content_source(i_content_source_type(i)))||
4262 '.'||LOWER(i_attribute_name(i))||', NULL, ');
4263 END LOOP;
4264
4265 /*===============================================================
4266 + into clause
4267 +===============================================================*/
4268
4269 l('');
4270 li('INTO');
4271
4272 l_prefix := RPAD(g_indent,3+LENGTHB(g_indent));
4273
4274 FOR i IN 1..i_uq_attribute_name.COUNT LOOP
4275 IF i > 1 THEN
4276 l(',');
4277 END IF;
4278 lli(l_prefix||'x_highest_ranked_value('||i_uq_entity_attr_id(i)||')');
4279 END LOOP;
4280
4281 /*===============================================================
4282 + from list
4283 +
4284 + FROM hz_organization_profiles dnb,
4285 + hz_organization_profiles user_entered
4286 + ...
4287 +===============================================================*/
4288
4289 -- fetch all of content source types from setup table.
4290
4291 OPEN c_sources;
4292 FETCH c_sources BULK COLLECT INTO i_sources;
4293 CLOSE c_sources;
4294
4295 l('');
4296 li('FROM hz_'||p_entity_name||'_profiles sst,');
4297 FOR i IN 1..i_sources.COUNT LOOP
4298 IF i > 1 THEN
4299 l(',');
4300 END IF;
4301
4302 ll(RPAD(l_prefix,5+LENGTHB(g_indent))||
4303 'hz_'||p_entity_name||'_profiles d_'||LOWER(frmt_actual_content_source(i_sources(i))));
4304 END LOOP;
4305 l('');
4306
4307 /*===============================================================
4308 + static where clause
4309 +===============================================================*/
4310
4311 li('WHERE sst.'||'party_id = p_party_id');
4312 li('AND sst.actual_content_source = ''SST''');
4313 li('AND sst.effective_end_date IS NULL');
4314 li('--');
4315
4316 /*===============================================================
4317 + dynamic where clause
4318 +
4319 + AND dnb.party_id (+) = sst.party_id
4320 + AND dnb.actual_content_source (+) = 'DNB'
4321 + AND dnb.effective_end_date IS NULL
4322 + ...
4323 +===============================================================*/
4324
4325 FOR i IN 1..i_sources.COUNT LOOP
4326 IF i <> 1 THEN
4327 l('');
4328 END IF;
4329 li('AND d_'||LOWER(frmt_actual_content_source(i_sources(i)))||'.party_id (+) = sst.party_id');
4330 li('AND d_'||LOWER(frmt_actual_content_source(i_sources(i)))||'.actual_content_source (+) = '''||i_sources(i)||'''');
4331 lli('AND d_'||LOWER(frmt_actual_content_source(i_sources(i)))||'.effective_end_date IS NULL');
4332 END LOOP;
4333 l(';');
4334 l('');
4335
4336 -- li('FOR i IN 1..x_highest_ranked_value.COUNT LOOP');
4337 li('i := x_highest_ranked_value.first;');
4338 li('while i is not null LOOP');
4339 li(' IF x_highest_ranked_value(i) IS NOT NULL THEN');
4340 li(' x_highest_ranked_source(i) := SUBSTRB(x_highest_ranked_value(i),'||
4341 'INSTRB(x_highest_ranked_value(i),'''||g_sep||''')+2);');
4342 li(' x_highest_ranked_value(i) := SUBSTRB(x_highest_ranked_value(i),1,'||
4343 'INSTRB(x_highest_ranked_value(i),'''||g_sep||''')-1);');
4344 li(' END IF;');
4345 li('i := x_highest_ranked_value.next(i); ');
4346 li('END LOOP;');
4347 END IF;
4348
4349 l('END '||l_procedure_name||';');
4350 l('');
4351
4352 /*===============================================================
4353 + procedure for assigning non-restricted columns
4354 +===============================================================*/
4355 OPEN c_date_attributes;
4356 FETCH c_date_attributes BULK COLLECT INTO
4357 i_attribute_name_date, i_entity_attr_id_date;
4358 CLOSE c_date_attributes;
4362 l('PROCEDURE '||l_procedure_name||' (');
4359
4360 l_procedure_name := 'assignNonRestrictedColumns';
4361
4363 li(fp('p_new_rec')||'IN HZ_PARTY_V2PUB.'||UPPER(p_entity_name)||'_REC_TYPE,');
4364 li(fp('p_sst_rec')||'IN OUT NOCOPY HZ_PARTY_V2PUB.'||UPPER(p_entity_name)||'_REC_TYPE');
4365 l(') IS');
4366 l('BEGIN');
4367
4368 FOR i IN 1..l_all_attribute_names.COUNT LOOP
4369 IF(l_all_attribute_names(i) <> 'BANK_CODE' AND
4370 l_all_attribute_names(i) <> 'BANK_OR_BRANCH_NUMBER' AND
4371 l_all_attribute_names(i) <> 'BRANCH_CODE') THEN
4372 l_index := Get_Index(i_uq_attribute_name, l_all_attribute_names(i));
4373 l_index_date := Get_Index(i_attribute_name_date, l_all_attribute_names(i));
4374
4375 IF (l_index = 0 AND l_index_date = 0) THEN
4376 j := j + 1;
4377 i_normal_attributes(j) := l_all_attribute_names(i);
4378 END IF;
4379 END IF;
4380 END LOOP;
4381
4382 IF i_normal_attributes.COUNT = 0 THEN
4383 li('NULL;');
4384 ELSE
4385 FOR i IN 1..i_normal_attributes.COUNT LOOP
4386 li('p_sst_rec.'||LOWER(i_normal_attributes(i))||
4387 ' := p_new_rec.'||LOWER(i_normal_attributes(i))||';');
4388 END LOOP;
4389 li('p_sst_rec.party_rec := p_new_rec.party_rec;');
4390 END IF;
4391
4392 l('END '||l_procedure_name||';');
4393 l('');
4394
4395 /*===============================================================
4396 + procedure to get column NULL property
4397 +===============================================================*/
4398
4399 l_procedure_name := 'getColumnNullProperty';
4400
4401 l('PROCEDURE '||l_procedure_name||' (');
4402 li(fp('p_sst_rec')||'IN HZ_PARTY_V2PUB.'||UPPER(p_entity_name)||'_REC_TYPE,');
4403 -- l(fp('p_name_list')||'IN HZ_MIXNM_UTILITY.INDEXVARCHAR30List,');
4404 li(fp('x_value_is_null_list')||'OUT NOCOPY HZ_MIXNM_UTILITY.INDEXVARCHAR1List,');
4405 li(fp('x_value_is_not_null_list')||'OUT NOCOPY HZ_MIXNM_UTILITY.INDEXVARCHAR1List');
4406 l(') IS');
4407 l('BEGIN');
4408
4409 IF (i_uq_attribute_name.COUNT = 0 AND i_attribute_name_date.COUNT = 0) THEN
4410 li('NULL;');
4411 ELSE
4412 FOR i IN 1..i_uq_attribute_name.COUNT LOOP
4413 -- li('IF p_name_list.EXISTS('||i_entity_attr_id(i)||') THEN');
4414 li('IF NVL(p_sst_rec.'||LOWER(i_uq_attribute_name(i))||','||i_uq_data_type(i)||') <> '||i_uq_data_type(i));
4415 li('THEN');
4416 li(' x_value_is_null_list('||i_uq_entity_attr_id(i)||') := ''N'';');
4417 li(' x_value_is_not_null_list('||i_uq_entity_attr_id(i)||') := ''Y'';');
4418 li('ELSE');
4419 li(' x_value_is_null_list('||i_uq_entity_attr_id(i)||') := ''Y'';');
4420 li(' x_value_is_not_null_list('||i_uq_entity_attr_id(i)||') := ''N'';');
4421 -- li('END IF;');
4422 li('END IF;');
4423
4424 END LOOP;
4425
4426 FOR i IN 1..i_attribute_name_date.COUNT LOOP
4427 l_index_date := Get_Index(l_all_attribute_names, i_attribute_name_date(i));
4428 IF l_data_type(l_index_date) = 'VARCHAR2' THEN
4429 i_data_type := 'fnd_api.g_miss_char';
4430 ELSIF l_data_type(l_index_date) = 'NUMBER' THEN
4431 i_data_type := 'fnd_api.g_miss_num';
4432 ELSIF l_data_type(l_index_date) = 'DATE' THEN
4433 i_data_type := 'fnd_api.g_miss_date';
4434 END IF;
4435
4436 li('IF NVL(p_sst_rec.'||LOWER(i_attribute_name_date(i))||','||i_data_type||') <> '||i_data_type);
4437 li('THEN');
4438 li(' x_value_is_null_list('||i_entity_attr_id_date(i)||') := ''N'';');
4439 -- Bug 4244112 : for date att, x_value_is_not_null_list is always N
4440 li(' x_value_is_not_null_list('||i_entity_attr_id_date(i)||') := ''N'';');
4441 li('ELSE');
4442 li(' x_value_is_null_list('||i_entity_attr_id_date(i)||') := ''Y'';');
4443 li(' x_value_is_not_null_list('||i_entity_attr_id_date(i)||') := ''N'';');
4444 li('END IF;');
4445 END LOOP;
4446
4447
4448 END IF;
4449
4450 l('END '||l_procedure_name||';');
4451 l('');
4452
4453 /*===============================================================
4454 + procedure for assigning all of columns
4455 +===============================================================*/
4456
4457 l_procedure_name := 'initAttributeList';
4458
4459 l('PROCEDURE '||l_procedure_name||' (');
4460 li(fp('p_create_update_flag')||'IN VARCHAR2,');
4461 li(fp('p_new_rec')||'IN HZ_PARTY_V2PUB.'||UPPER(p_entity_name)||'_REC_TYPE,');
4462 li(fp('p_old_rec')||'IN HZ_PARTY_V2PUB.'||UPPER(p_entity_name)||'_REC_TYPE,');
4463 li(fp('x_name_list')||'OUT NOCOPY HZ_MIXNM_UTILITY.INDEXVARCHAR30List,');
4464 li(fp('x_new_value_is_null_list')||'OUT NOCOPY HZ_MIXNM_UTILITY.INDEXVARCHAR1List');
4465 l(') IS');
4466 l('BEGIN');
4467
4468 IF (i_uq_attribute_name.COUNT = 0 AND i_attribute_name_date.COUNT = 0) THEN
4469 li('NULL;');
4470 ELSE
4471 FOR j IN 1..2 LOOP
4472 IF j = 1 THEN
4473 li('IF p_create_update_flag = ''C'' THEN');
4474 ELSE
4475 li('ELSE');
4476 END IF;
4477
4478 FOR i IN 1..i_uq_attribute_name.COUNT LOOP
4479 IF j = 1 THEN
4480 li(' IF NVL(p_new_rec.'||LOWER(i_uq_attribute_name(i))||','||
4481 i_uq_data_type(i)||') <> '||i_uq_data_type(i));
4482 li(' THEN');
4483 li(' x_name_list('||i_uq_entity_attr_id(i)||') := '''||
4484 LOWER(i_uq_attribute_name(i))||''';');
4485 -- Bug 4201309 : store x_new_value_is_null_list = N also
4486 li(' x_new_value_is_null_list('||i_uq_entity_attr_id(i)||') := ''N'';');
4487 li(' ELSE');
4488 li(' x_new_value_is_null_list('||i_uq_entity_attr_id(i)||') := ''Y'';');
4492 li(' IF p_new_rec.'||LOWER(i_uq_attribute_name(i))||' = '||i_uq_data_type(i)||' THEN');
4489 li(' END IF;');
4490 ELSE
4491 li(' IF p_new_rec.'||LOWER(i_uq_attribute_name(i))||' IS NOT NULL THEN');
4493 li(' x_new_value_is_null_list('||i_uq_entity_attr_id(i)||') := ''Y'';');
4494 li(' END IF;');
4495 li(' IF p_new_rec.'||LOWER(i_uq_attribute_name(i))||
4496 ' <> p_old_rec.'||LOWER(i_uq_attribute_name(i)));
4497 li(' THEN');
4498 li(' x_name_list('||i_uq_entity_attr_id(i)||') := '''||
4499 LOWER(i_uq_attribute_name(i))||''';');
4500 li(' x_new_value_is_null_list('||i_uq_entity_attr_id(i)||') := ''N'';');
4501 li(' END IF;');
4502 li(' ELSE');
4503 li(' x_new_value_is_null_list('||i_uq_entity_attr_id(i)||') := ''N'';');
4504 li(' END IF;');
4505 END IF;
4506 END LOOP;
4507
4508 FOR i IN 1..i_attribute_name_date.COUNT LOOP
4509 -- Bug 4244112 : for date attributes, do not check values
4510 -- always add it to the x_name_list
4511 IF j = 1 THEN
4512 -- li(' IF NVL(p_new_rec.'||LOWER(i_attribute_name_date(i))||','||
4513 -- i_data_type||') <> '||i_data_type);
4514 -- li(' THEN');
4515 li(' x_name_list('||i_entity_attr_id_date(i)||') := '''||
4516 LOWER(i_attribute_name_date(i))||''';');
4517 li(' x_new_value_is_null_list('||i_entity_attr_id_date(i)||') := ''N'';');
4518 -- li(' ELSE');
4519 -- li(' x_new_value_is_null_list('||i_entity_attr_id_date(i)||') := ''Y'';');
4520 -- li(' END IF;');
4521 -- Bug 4201309 : for update case, x_new_value_is_null_list if
4522 -- the value is NULL
4523 ELSE
4524 li(' IF p_new_rec.'||LOWER(i_attribute_name_date(i))||' IS NULL THEN');
4525 -- li(' IF p_new_rec.'||LOWER(i_attribute_name_date(i))||' = '||i_data_type||' THEN');
4526 li(' x_new_value_is_null_list('||i_entity_attr_id_date(i)||') := ''Y'';');
4527 li(' ELSE');
4528 li(' x_new_value_is_null_list('||i_entity_attr_id_date(i)||') := ''N'';');
4529 li(' END IF;');
4530 li(' x_name_list('||i_entity_attr_id_date(i)||') := '''||
4531 LOWER(i_attribute_name_date(i))||''';');
4532 -- li(' END IF;');
4533 END IF;
4534
4535 END LOOP;
4536
4537 END LOOP;
4538
4539 li('END IF;');
4540 END IF;
4541
4542 l('END '||l_procedure_name||';');
4543 l('');
4544
4545 /*===============================================================
4546 + procedure for setting SST record
4547 +===============================================================*/
4548
4549 OPEN c_restricted_groups;
4550 FETCH c_restricted_groups BULK COLLECT INTO
4551 i_attribute_group_name, i_primary_entity_attr_id,
4552 i_group_attribute_name, i_group_entity_attr_id;
4553 CLOSE c_restricted_groups;
4554
4555 l_procedure_name := 'createSSTRecord';
4556
4557 l('PROCEDURE '||l_procedure_name||' (');
4558 li(fp('p_new_data_source')||'IN VARCHAR2,');
4559 li(fp('p_new_rec')||'IN HZ_PARTY_V2PUB.'||UPPER(p_entity_name)||'_REC_TYPE,');
4560 li(fp('p_sst_rec')||'IN OUT NOCOPY HZ_PARTY_V2PUB.'||UPPER(p_entity_name)||'_REC_TYPE,');
4561 li(fp('p_updateable_flag_list')||'IN OUT NOCOPY HZ_MIXNM_UTILITY.INDEXVARCHAR1List,');
4562 li(fp('p_exception_type_list')||'IN OUT NOCOPY HZ_MIXNM_UTILITY.INDEXVARCHAR30List');
4563 l(') IS');
4564 l('BEGIN');
4565
4566 IF (i_uq_attribute_name.COUNT = 0 AND i_attribute_name_date.COUNT = 0) THEN
4567 li('NULL;');
4568 ELSE
4569 l_start := 0; l_end := 0;
4570 FOR i IN 1..i_attribute_group_name.COUNT+1 LOOP
4571 IF i = 1 OR
4572 i = i_attribute_group_name.COUNT+1 OR
4573 i_attribute_group_name(i-1) <> i_attribute_group_name(i)
4574 THEN
4575 IF i <> 1 THEN
4576 l_index := Get_Index(l_all_attribute_names, i_group_attribute_name(l_start));
4577
4578 IF l_data_type(l_index) = 'VARCHAR2' THEN
4579 str1 := 'fnd_api.g_miss_char';
4580 ELSIF l_data_type(l_index) = 'NUMBER' THEN
4581 str1 := 'fnd_api.g_miss_num';
4582 ELSIF l_data_type(l_index) = 'DATE' THEN
4583 str1 := 'fnd_api.g_miss_date';
4584 END IF;
4585
4586 li('ELSIF NVL(p_sst_rec.'||LOWER(i_group_attribute_name(l_start))||','||str1||') <> '||str1||' THEN');
4587
4588 l_end := i-1;
4589 FOR j IN REVERSE l_start+1..l_end LOOP
4590 li(' p_updateable_flag_list('||i_group_entity_attr_id(j)||') := ''N'';');
4591 li(' p_exception_type_list('||i_group_entity_attr_id(j)||') := NULL;');
4592 END LOOP;
4593
4594 li('END IF;');
4595 END IF;
4596
4597 IF i = i_attribute_group_name.COUNT+1 THEN
4598 l('');
4599 EXIT;
4600 END IF;
4601
4602 -- start processing a new attribute group.
4603 li('-- processing group '||LOWER(i_attribute_group_name(i)));
4604 li('IF p_updateable_flag_list.EXISTS('||i_group_entity_attr_id(i)||') AND');
4605 li(' p_updateable_flag_list('||i_group_entity_attr_id(i)||') = ''Y''');
4606 li('THEN');
4607
4608 l_group_id := i_group_entity_attr_id(i); l_start := i;
4609 ELSE
4610 l_index := Get_Index(l_all_attribute_names, i_group_attribute_name(i));
4611
4612 IF l_data_type(l_index) = 'VARCHAR2' THEN
4613 str1 := 'fnd_api.g_miss_char';
4614 ELSIF l_data_type(l_index) = 'NUMBER' THEN
4618 END IF;
4615 str1 := 'fnd_api.g_miss_num';
4616 ELSIF l_data_type(l_index) = 'DATE' THEN
4617 str1 := 'fnd_api.g_miss_date';
4619
4620 li(' IF NVL(p_new_rec.'||LOWER(i_group_attribute_name(i))||','||str1||') <> '||str1||' OR');
4621 li(' NVL(p_sst_rec.'||LOWER(i_group_attribute_name(i))||','||str1||') <> '||str1);
4622 li(' THEN');
4623 li(' p_updateable_flag_list('||i_group_entity_attr_id(i)||') := ''Y'';');
4624 li(' p_exception_type_list('||i_group_entity_attr_id(i)||') := p_exception_type_list('||l_group_id||');');
4625 li(' END IF;');
4626 END IF;
4627 END LOOP;
4628
4629 FOR i IN 1..i_uq_attribute_name.COUNT LOOP
4630 li('IF p_updateable_flag_list.EXISTS('||i_uq_entity_attr_id(i)||') AND');
4631 li(' p_updateable_flag_list('||i_uq_entity_attr_id(i)||') = ''Y''');
4632 li('THEN');
4633 li(' p_sst_rec.'||LOWER(i_uq_attribute_name(i))||
4634 ' := p_new_rec.'||LOWER(i_uq_attribute_name(i))||';');
4635 li('END IF;');
4636 END LOOP;
4637
4638 FOR i IN 1..i_attribute_name_date.COUNT LOOP
4639 li('IF p_updateable_flag_list.EXISTS('||i_entity_attr_id_date(i)||') AND');
4640 li(' p_updateable_flag_list('||i_entity_attr_id_date(i)||') = ''Y''');
4641 li('THEN');
4642 li(' p_sst_rec.'||LOWER(i_attribute_name_date(i))||
4643 ' := p_new_rec.'||LOWER(i_attribute_name_date(i))||';');
4644 li('END IF;');
4645 END LOOP;
4646
4647 li('IF p_new_data_source = ''USER_ENTERED'' THEN');
4648 li(' assignNonRestrictedColumns(p_new_rec, p_sst_rec);');
4649 li('END IF;');
4650 l('');
4651 END IF;
4652
4653 l('END '||l_procedure_name||';');
4654 l('');
4655
4656 l_procedure_name := 'updateSSTRecord';
4657
4658 l('PROCEDURE '||l_procedure_name||' (');
4659 -- Bug 4201309 : add parameter p_create_update_flag
4660 li(fp('p_create_update_flag')||'IN VARCHAR2,');
4661 li(fp('p_new_data_source')||'IN VARCHAR2,');
4662 li(fp('p_new_rec')||'IN HZ_PARTY_V2PUB.'||UPPER(p_entity_name)||'_REC_TYPE,');
4663 li(fp('p_sst_rec')||'IN OUT NOCOPY HZ_PARTY_V2PUB.'||UPPER(p_entity_name)||'_REC_TYPE,');
4664 li(fp('p_updateable_flag_list')||'IN OUT NOCOPY HZ_MIXNM_UTILITY.INDEXVARCHAR1List,');
4665 li(fp('p_exception_type_list')||'IN OUT NOCOPY HZ_MIXNM_UTILITY.INDEXVARCHAR30List,');
4666 li(fp('p_new_value_is_null_list')||'IN HZ_MIXNM_UTILITY.INDEXVARCHAR1List,');
4667 li(fp('x_data_source_list')||'OUT NOCOPY HZ_MIXNM_UTILITY.INDEXVARCHAR30List');
4668 l(') IS');
4669 li(fp('l_highest_ranked_value')||'INDEXEDVARCHARLIST;');
4670 li(fp('l_highest_ranked_source')||'HZ_MIXNM_UTILITY.INDEXVARCHAR30List;');
4671 l('BEGIN');
4672
4673 IF (i_uq_attribute_name.COUNT = 0 AND i_attribute_name_date.COUNT = 0) THEN
4674 li('NULL;');
4675 ELSE
4676 li('IF p_new_data_source <> ''SST'' AND');
4677 li(' p_new_value_is_null_list.COUNT > 0 AND');
4678 li(' p_updateable_flag_list.COUNT > 0');
4679 li('THEN');
4680 IF p_entity_name = 'organization' THEN
4681 li(' selectOrgHighestRankedValue(');
4682 ELSE
4683 li(' selectPerHighestRankedValue(');
4684 END IF;
4685 li(' p_party_id => p_new_rec.party_rec.party_id,');
4686 li(' x_highest_ranked_value => l_highest_ranked_value,');
4687 li(' x_highest_ranked_source => l_highest_ranked_source);');
4688 li('END IF;');
4689
4690 FOR i IN 1..i_uq_attribute_name.COUNT LOOP
4691 li('IF p_updateable_flag_list.EXISTS('||i_uq_entity_attr_id(i)||') AND');
4692 li(' p_updateable_flag_list('||i_uq_entity_attr_id(i)||') = ''Y''');
4693 li('THEN');
4694 li(' IF p_new_data_source <> ''SST'' AND');
4695 li(' p_new_value_is_null_list.EXISTS('||i_uq_entity_attr_id(i)||') AND');
4696 li(' p_new_value_is_null_list('||i_uq_entity_attr_id(i)||') = ''Y'' AND');
4697 li(' p_exception_type_list.EXISTS('||i_uq_entity_attr_id(i)||') AND');
4698 li(' p_exception_type_list('||i_uq_entity_attr_id(i)||') = ''Migration'' AND');
4699 li(' l_highest_ranked_source.EXISTS('||i_uq_entity_attr_id(i)||')');
4700 li(' THEN');
4701 li(' p_sst_rec.'||LOWER(i_uq_attribute_name(i))||
4702 ' := l_highest_ranked_value('||i_uq_entity_attr_id(i)||');');
4703 li(' x_data_source_list('||i_uq_entity_attr_id(i)||')'||
4704 ' := l_highest_ranked_source('||i_uq_entity_attr_id(i)||');');
4705 li(' ELSE');
4706 li(' p_sst_rec.'||LOWER(i_uq_attribute_name(i))||
4707 ' := p_new_rec.'||LOWER(i_uq_attribute_name(i))||';');
4708 li(' END IF;');
4709 li('END IF;');
4710
4711 IF i_uq_attribute_name(i) = 'SIC_CODE' THEN
4712 l_index1 := i;
4713 ELSIF i_uq_attribute_name(i) = 'SIC_CODE_TYPE' THEN
4714 l_index2 := i;
4715 END IF;
4716 END LOOP;
4717
4718 -- sic_code and sic_code_type are either both unrestricted or both
4719 -- restricted.
4720
4721 IF l_index1 > 0 OR l_index2 > 0 THEN
4722 l('');
4723 li('-- sync. sic_code and sic_code_type ');
4724 l('');
4725 li('IF p_updateable_flag_list.EXISTS('||i_uq_entity_attr_id(l_index1)||') AND');
4726 li(' p_updateable_flag_list('||i_uq_entity_attr_id(l_index1)||') = ''Y'' AND');
4727 li(' (NOT p_updateable_flag_list.EXISTS('||i_uq_entity_attr_id(l_index2)||') OR');
4728 li(' p_updateable_flag_list('||i_uq_entity_attr_id(l_index2)||') = ''N'')');
4729 li('THEN');
4730 li(' p_updateable_flag_list('||i_uq_entity_attr_id(l_index2)||') := ''Y'';');
4731 li(' IF p_exception_type_list.EXISTS('||i_uq_entity_attr_id(l_index1)||') THEN');
4735 li(' IF x_data_source_list.EXISTS('||i_uq_entity_attr_id(l_index1)||') THEN');
4732 li(' p_exception_type_list('||i_uq_entity_attr_id(l_index2)||
4733 ') := p_exception_type_list('||i_uq_entity_attr_id(l_index1)||');');
4734 li(' END IF;');
4736 li(' x_data_source_list('||i_uq_entity_attr_id(l_index2)||
4737 ') := x_data_source_list('||i_uq_entity_attr_id(l_index1)||');');
4738 li(' END IF;');
4739 li(' p_sst_rec.'||LOWER(i_uq_attribute_name(l_index2))||
4740 ' := p_new_rec.'||LOWER(i_uq_attribute_name(l_index2))||';');
4741 li('END IF;');
4742 li('IF p_updateable_flag_list.EXISTS('||i_uq_entity_attr_id(l_index2)||') AND');
4743 li(' p_updateable_flag_list('||i_uq_entity_attr_id(l_index2)||') = ''Y'' AND');
4744 li(' (NOT p_updateable_flag_list.EXISTS('||i_uq_entity_attr_id(l_index1)||') OR');
4745 li(' p_updateable_flag_list('||i_uq_entity_attr_id(l_index1)||') = ''N'')');
4746 li('THEN');
4747 li(' p_updateable_flag_list('||i_uq_entity_attr_id(l_index1)||') := ''Y'';');
4748 li(' IF p_exception_type_list.EXISTS('||i_uq_entity_attr_id(l_index2)||') THEN');
4749 li(' p_exception_type_list('||i_uq_entity_attr_id(l_index1)||
4750 ') := p_exception_type_list('||i_uq_entity_attr_id(l_index2)||');');
4751 li(' END IF;');
4752 li(' IF x_data_source_list.EXISTS('||i_uq_entity_attr_id(l_index2)||') THEN');
4753 li(' x_data_source_list('||i_uq_entity_attr_id(l_index1)||
4754 ') := x_data_source_list('||i_uq_entity_attr_id(l_index2)||');');
4755 li(' END IF;');
4756 li(' p_sst_rec.'||LOWER(i_uq_attribute_name(l_index1))||
4757 ' := p_new_rec.'||LOWER(i_uq_attribute_name(l_index1))||';');
4758 li('END IF;');
4759 l('');
4760 END IF;
4761
4762 FOR i IN 1..i_attribute_name_date.COUNT LOOP
4763 -- Bug 4244112 : for date attributes, assign g_miss
4764 -- if the value is NULL.
4765 -- Bug 4201309 : assign g_miss only if it is create flow
4766 l_index_date := Get_Index(l_all_attribute_names, i_attribute_name_date(i));
4767 IF l_data_type(l_index_date) = 'VARCHAR2' THEN
4768 i_data_type := 'fnd_api.g_miss_char';
4769 ELSIF l_data_type(l_index_date) = 'NUMBER' THEN
4770 i_data_type := 'fnd_api.g_miss_num';
4771 ELSIF l_data_type(l_index_date) = 'DATE' THEN
4772 i_data_type := 'fnd_api.g_miss_date';
4773 END IF;
4774 li('IF p_updateable_flag_list.EXISTS('||i_entity_attr_id_date(i)||') AND');
4775 li(' p_updateable_flag_list('||i_entity_attr_id_date(i)||') = ''Y''');
4776 li('THEN');
4777 li(' IF p_create_update_flag = ''U'' THEN');
4778 li(' p_sst_rec.'||LOWER(i_attribute_name_date(i))||
4779 ' := p_new_rec.'||LOWER(i_attribute_name_date(i))||';');
4780 li(' ELSE');
4781 li(' p_sst_rec.'||LOWER(i_attribute_name_date(i))||
4782 ' := nvl(p_new_rec.'||LOWER(i_attribute_name_date(i))||', ' || i_data_type||');');
4783 li(' END IF;');
4784 li('END IF;');
4785 END LOOP;
4786
4787 li('IF p_new_data_source IN (''SST'', ''USER_ENTERED'') THEN');
4788 li(' assignNonRestrictedColumns(p_new_rec, p_sst_rec);');
4789 li('END IF;');
4790 END IF;
4791
4792 l('END '||l_procedure_name||';');
4793 l('');
4794
4795 END Gen_UpdateSSTRecord;
4796
4797 /**
4798 * PRIVATE PROCEDURE
4799 *
4800 * DESCRIPTION
4801 *
4802 * MODIFICATION HISTORY
4803 *
4804 */
4805
4806 --------------------------------------
4807 -- public procedures and functions
4808 --------------------------------------
4809
4810 /**
4811 * PROCEDURE Gen_PackageForConc
4812 *
4813 * DESCRIPTION
4814 * Generate package HZ_MIXNM_CONC_DYNAMIC_PKG for mix-n-match
4815 * concurrent program.
4816 *
4817 * EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
4818 *
4819 * ARGUMENTS
4820 * IN:
4821 * p_package_name Package name.
4822 *
4823 * NOTES
4824 *
4825 * MODIFICATION HISTORY
4826 *
4827 * 05-01-2002 Jianying Huang o Created
4828 */
4829
4830 PROCEDURE Gen_PackageForConc (
4831 p_package_name IN VARCHAR2
4832 ) IS
4833
4834
4835 BEGIN
4836
4837 -- new a package body object
4838 HZ_GEN_PLSQL.new(p_package_name, 'PACKAGE BODY');
4839
4840 Load_AllAttrAndGroup;
4841
4842 Gen_PackageHeader(p_package_name);
4843
4844 Gen_CommonProceduresForConc;
4845
4846 Gen_BulkCreateSST('organization', 'Bulk');
4847 Gen_BulkUpdateSST('organization', 'Bulk');
4848
4849 Gen_BulkCreateSST('person', 'Bulk');
4850 Gen_BulkUpdateSST('person', 'Bulk');
4851
4852 -- generate procedures for data import
4853 Gen_BulkCreateSST('organization', 'Import');
4854 Gen_BulkUpdateSST('organization', 'Import');
4855
4856 Gen_BulkCreateSST('person', 'Import');
4857 Gen_BulkUpdateSST('person', 'Import');
4858
4859 Gen_PackageTail(p_package_name);
4860
4861 -- compile the package.
4862 HZ_GEN_PLSQL.compile_code;
4863
4864 END Gen_PackageForConc;
4865
4866 /**
4867 * PROCEDURE Gen_PackageForAPI
4868 *
4869 * DESCRIPTION
4870 * Generate package HZ_MIXNM_API_DYNAMIC_PKG for
4871 * mix-n-match API.
4872 *
4873 * EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
4874 *
4875 * ARGUMENTS
4876 * IN:
4877 * p_package_name Package name.
4878 *
4879 * NOTES
4880 *
4881 * MODIFICATION HISTORY
4882 *
4883 * 05-01-2002 Jianying Huang o Created
4884 */
4885
4886 PROCEDURE Gen_PackageForAPI (
4887 p_package_name IN VARCHAR2
4888 ) IS
4889 BEGIN
4890
4891 -- new a package body object
4892 HZ_GEN_PLSQL.new(p_package_name, 'PACKAGE BODY');
4893
4894 Load_AllAttrAndGroup;
4895
4896 Gen_PackageHeader(p_package_name);
4897
4898 Gen_CommonProceduresForAPI;
4899
4900 Gen_UpdateSSTRecord('organization');
4901 Gen_UpdateSSTRecord('person');
4902
4903 Gen_PackageTail(p_package_name);
4904
4905 -- compile the package.
4906 HZ_GEN_PLSQL.compile_code;
4907
4908 END Gen_PackageForAPI;
4909
4910 /*
4911 PROCEDURE Gen_CodeForDQM (
4912 p_entity_name IN VARCHAR2
4913 ) IS
4914 BEGIN
4915
4916 li(' write_log(''Process DQM ...'');');
4917 l('');
4918 li(' -- DQM is current available on this site. we need');
4919 li(' -- to update interface table so the affected parties');
4920 li(' -- can be picked up when DQM concurrent program starts');
4921 li(' -- to run. DQM concurrent program is scheduled to run');
4922 li(' -- periodically.');
4923 l('');
4924 li(' FORALL i IN 1..i_party_id.COUNT');
4925 li(' INSERT INTO hz_dqm_sync_interface (');
4926 li(' party_id,');
4927 li(' record_id,');
4928 li(' entity,');
4929 li(' operation,');
4930 li(' staged_flag,');
4931 Gen_CodeForWhoColumns('I','LIST',g_indent||' ','N');
4932 l('');
4933 li(' ) ');
4934
4935 IF p_entity_name = 'organization' THEN
4936 li(' VALUES (');
4937 li(' i_party_id(i),');
4938 li(' NULL, ');
4939 li(' ''PARTY'',');
4940 li(' ''U'',');
4941 li(' ''N'',');
4942 Gen_CodeForWhoColumns('I','VALUE',g_indent||' ','N');
4943 l('');
4944 li(' );');
4945 ELSE
4946 li(' SELECT');
4947 li(' i_party_id(i),');
4948 li(' NULL,');
4949 li(' ''PARTY'',');
4950 li(' ''U'',');
4951 li(' ''N'',');
4952 Gen_CodeForWhoColumns('I','VALUE',g_indent||' ','N');
4953 l('');
4954 li(' FROM dual');
4955 li(' UNION');
4956 li(' SELECT');
4957 li(' i_party_id(i),');
4958 li(' org_contact_id,');
4959 li(' ''CONTACTS'',');
4960 li(' ''U'',');
4961 li(' ''N'',');
4962 Gen_CodeForWhoColumns('I','VALUE',g_indent||' ','N');
4963 l('');
4964 li(' FROM hz_party_relationships pr, ');
4965 li(' hz_org_contacts oc');
4966 li(' WHERE pr.party_relationship_id = oc.party_relationship_id');
4967 li(' AND pr.subject_id = i_party_id(i);');
4968 END IF;
4969 l('');
4970 li(' write_log(''Processed ''||SQL%ROWCOUNT||'' records.'');');
4971 l('');
4972
4973 END Gen_CodeForDQM;
4974 */
4975
4976 END HZ_MIXNM_DYNAMIC_PKG_GENERATOR;