[Home] [Help]
PACKAGE BODY: APPS.HZ_PARTY_STAGE_SHADOW
Source
1 PACKAGE BODY HZ_PARTY_STAGE_SHADOW AS
2 /*$Header: ARHDSHSB.pls 120.13 2006/05/05 22:35:37 repuri noship $ */
3
4
5 g_batch_size NUMBER := 200;
6 g_num_stage_steps NUMBER := 3;
7 g_num_stage_new_steps NUMBER := 6;
8 g_schema_name VARCHAR2(30) ;
9 G_PS_QKEY_STR VARCHAR2(400) := '''#''||H_TX22(I)||''#''||H_TX14(I)||''#''||'||
10 'H_TX21(I)||''#''||H_TX10(I)||''#''||H_TX11(I)||''#''||'||
11 'TRANSLATE(INITCAP(H_TX4(I)),''ABCDEFGHIJKLMNOPQRSTUVWXYZ! abcdefghijklmnopqrstuvwxyz'',''ABCDEFGHIJKLMNOPQRSTUVWXYZ'')||''#''';
12 --#EXACT(Country)#WR(State)#Cleanse(County)#Cleanse(City)#postal_code#first/letter(cleanse(address))#
13 G_C_QKEY_STR VARCHAR2(200) := '''#''||TRANSLATE(INITCAP(H_TX23(I)),''ABCDEFGHIJKLMNOPQRSTUVWXYZ! abcdefghijklmnopqrstuvwxyz'',''ABCDEFGHIJKLMNOPQRSTUVWXYZ'')||''#''||H_TX22(I)||''#'''; -- #first letters(WR(Name))#jobtitle#
14 G_CPT_QKEY_STR VARCHAR2(200) := '''#''||H_CONTACT_POINT_TYPE(I)||''#''||H_TX2(I)||''#''||H_TX5(I)||''#'''; -- #cpttype#phonenumber#email#
15
16
17 PROCEDURE l(str VARCHAR2) IS
18 BEGIN
19 HZ_GEN_PLSQL.add_line(str);
20 END;
21
22 PROCEDURE verify_all_procs;
23
24 PROCEDURE create_btree_indexes (p_entity VARCHAR2);
25
26 PROCEDURE log(
27 message IN VARCHAR2,
28 newline IN BOOLEAN DEFAULT TRUE);
29
30 PROCEDURE outandlog(
31 message IN VARCHAR2,
32 newline IN BOOLEAN DEFAULT TRUE);
33
34 FUNCTION logerror(SQLERRM VARCHAR2 DEFAULT NULL)
35 RETURN VARCHAR2;
36
37 PROCEDURE generate_map_proc (
38 p_entity IN VARCHAR2,
39 p_proc_name IN VARCHAR2,
40 p_command IN VARCHAR2
41 );
42
43 PROCEDURE generate_party_query_proc;
44 PROCEDURE generate_contact_query_proc;
45 PROCEDURE generate_contact_pt_query_proc;
46 PROCEDURE generate_party_site_query_proc;
47
48 PROCEDURE generate_declarations;
49 PROCEDURE generate_uds_proc;
50
51 PROCEDURE create_initial_prefs( p_tablespace IN VARCHAR2,
52 l_num_prll OUT nocopy VARCHAR2,
53 l_idx_mem OUT nocopy VARCHAR2) IS
54 ctx_tbsp VARCHAR2(255);
55 ctx_index_tbsp VARCHAR2(255);
56
57 BEGIN
58 --- Create memory, storage and lexer preferences.
59
60 log('---------------------------------------------------');
61 log('Calling create_initial_prefs');
62
63 IF FND_PROFILE.value('HZ_DQM_INDEX_MEMORY') IS NOT NULL THEN
64 l_idx_mem := FND_PROFILE.value('HZ_DQM_INDEX_MEMORY');
65 ELSE
66 BEGIN
67 SELECT PAR_VALUE INTO l_idx_mem
68 FROM CTX_PARAMETERS
69 WHERE PAR_NAME = 'MAX_INDEX_MEMORY';
70 EXCEPTION
71 WHEN NO_DATA_FOUND THEN
72 BEGIN
73 SELECT PAR_VALUE INTO l_idx_mem
74 FROM CTX_PARAMETERS
75 WHERE PAR_NAME = 'DEFAULT_INDEX_MEMORY';
76 EXCEPTION
77 WHEN NO_DATA_FOUND THEN
78 l_idx_mem := '0';
79 END;
80 END;
81 END IF;
82
83 IF FND_PROFILE.value('HZ_DQM_INDEX_PARALLEL') IS NOT NULL THEN
84 l_num_prll := FND_PROFILE.value('HZ_DQM_INDEX_PARALLEL');
85 ELSE
86 l_num_prll := NULL;
87 END IF;
88
89 BEGIN
90 ctx_ddl.drop_preference('HZ_DQM_STORAGE');
91 EXCEPTION
92 WHEN OTHERS THEN
93 NULL;
94 END;
95
96 IF p_tablespace IS NOT NULL THEN
97 ctx_tbsp := p_tablespace;
98 ctx_index_tbsp := p_tablespace;
99 ELSE
100 select tablespace, index_tablespace
101 into ctx_tbsp, ctx_index_tbsp
102 from fnd_product_installations
103 where application_id = '222';
104 END IF;
105
106 ctx_ddl.create_preference('HZ_DQM_STORAGE', 'BASIC_STORAGE');
107 ctx_ddl.set_attribute('HZ_DQM_STORAGE', 'I_TABLE_CLAUSE', 'tablespace '|| ctx_tbsp|| ' storage (initial 4K next 8M pctincrease 0)');
108 ctx_ddl.set_attribute('HZ_DQM_STORAGE', 'K_TABLE_CLAUSE', 'tablespace ' || ctx_tbsp || ' storage (initial 4K next 8M pctincrease 0)');
109 ctx_ddl.set_attribute('HZ_DQM_STORAGE', 'R_TABLE_CLAUSE', 'tablespace '|| ctx_tbsp || ' storage (initial 4K next 8M pctincrease 0) lob (data) store as (cache) ');
110 ctx_ddl.set_attribute('HZ_DQM_STORAGE', 'I_INDEX_CLAUSE', 'tablespace '|| ctx_index_tbsp || ' storage (initial 4K next 8M pctincrease 0) compress 2');
111
112 BEGIN
113 ctx_ddl.drop_preference('dqm_lexer');
114 EXCEPTION
115 WHEN OTHERS THEN
116 null;
117 END;
118
119 ctx_ddl.create_preference('dqm_lexer', 'BASIC_LEXER');
120 ctx_ddl.set_attribute ( 'dqm_lexer', 'index_themes', 'NO');
121 ctx_ddl.set_attribute ( 'dqm_lexer', 'index_text', 'YES');
122
123 END ;
124
125
126
127 FUNCTION wait_for_request(
128 p_request_id NUMBER) RETURN VARCHAR2;
129
130 FUNCTION has_trx_context(proc VARCHAR2) RETURN BOOLEAN;
131 FUNCTION has_context(proc VARCHAR2) RETURN BOOLEAN;
132
133 --------------------------------------------------------------
134 -- CHANGE: This should change to reflect the correct datastore
135 --------------------------------------------------------------
136
140 log ('Calling insert_into_thin_tables for ' || p_entity);
137 PROCEDURE insert_into_thin_tables( p_entity IN VARCHAR2) IS
138 BEGIN
139 log ('-------------------------------------');
141
142
143 IF p_entity = 'PARTIES'
144 THEN
145 log('Start Time for Parties ' || TO_CHAR(SYSDATE, 'MM-DD-YY HH24:MI:SS'));
146 insert /*+ append */ into hz_thin_st_parties
147 (party_id, status, partition_id, parent_rowid, concat_col)
148 select party_id, status, decode(TX36, 'ORGANIZATION ',0,'PERSON ',1,1),ROWID,null
149 from hz_shadow_st_parties;
150 log('End Time for Parties ' || TO_CHAR(SYSDATE, 'MM-DD-YY HH24:MI:SS'));
151 ELSIF p_entity = 'PARTY_SITES'
152 THEN
153 log('Start Time for Party Sites ' || TO_CHAR(SYSDATE, 'MM-DD-YY HH24:MI:SS'));
154 insert /*+ append */ into hz_thin_st_psites
155 (party_id, party_site_id, person_party_id, qkey, org_contact_id,
156 parent_rowid, concat_col,status_flag) -- Bug No: 4299785
157 select decode(party_id,person_party_id,NULL,party_id), party_site_id,person_party_id, qkey,org_contact_id,ROWID,null,status_flag -- Fix for bug 5155761
158 --select party_id, party_site_id,person_party_id, qkey,org_contact_id,ROWID,null,status_flag -- Bug No: 4299785
159 from hz_shadow_st_psites;
160 log('End Time for Party Sites ' || TO_CHAR(SYSDATE, 'MM-DD-YY HH24:MI:SS'));
161 ELSIF p_entity = 'CONTACTS'
162 THEN
163 log('Start Time for Contacts ' || TO_CHAR(SYSDATE, 'MM-DD-YY HH24:MI:SS'));
164 insert /*+ append */ into hz_thin_st_contacts
165 (party_id, person_party_id, qkey, org_contact_id, parent_rowid, concat_col,status_flag) -- Fix for bug 5155761
166 select decode(party_id,person_party_id,NULL,party_id),person_party_id, qkey,org_contact_id,ROWID,null,status_flag -- Fix for bug 5155761
167 --(party_id, qkey, org_contact_id, parent_rowid, concat_col,status_flag)-- Bug No: 4299785
168 --select party_id, qkey,org_contact_id,ROWID,null,status_flag -- Bug No: 4299785
169 from hz_shadow_st_contacts;
170 log('End Time for Contacts ' || TO_CHAR(SYSDATE, 'MM-DD-YY HH24:MI:SS'));
171 ELSIF p_entity = 'CONTACT_POINTS'
172 THEN
173 log('Start Time for Contact Points ' || TO_CHAR(SYSDATE, 'MM-DD-YY HH24:MI:SS'));
174 insert /*+ append */ into hz_thin_st_cpts
175 (party_id, partition_id, contact_point_id, party_site_id,
176 person_party_id, parent_rowid, org_contact_id, qkey, concat_col,status_flag) -- Bug No: 4299785
177 select decode(party_id,person_party_id,NULL,party_id), decode(contact_point_type,'PHONE',0,'EMAIL',1,2),contact_point_id,party_site_id, person_party_id,ROWID,org_contact_id, qkey, null,status_flag -- Fix for bug 5155761
178 --select party_id, decode(contact_point_type,'PHONE',0,'EMAIL',1,2),contact_point_id,party_site_id,person_party_id,ROWID,org_contact_id, qkey, null,status_flag -- Bug No: 4299785
179 from hz_shadow_st_cpts;
180 log('End Time for Contact Points ' || TO_CHAR(SYSDATE, 'MM-DD-YY HH24:MI:SS'));
181 END IF ;
182 END ;
183
184
185 PROCEDURE truncate_staging_tables IS
186
187 l_owner VARCHAR2(255);
188 l_status VARCHAR2(255);
189 l_owner1 VARCHAR2(255);
190 l_temp VARCHAR2(255);
191 l_sql VARCHAR2(4000);
192 BEGIN
193 log('---------------------------------------------------');
194 log('Calling truncate_staging_tables ');
195 IF (fnd_installation.GET_APP_INFO('AR',l_status,l_temp,l_owner1)) THEN
196 /* select owner into l_owner from sys.all_objects
197 where object_name = 'HZ_STAGED_PARTIES' and OBJECT_TYPE = 'TABLE' and owner = l_owner1;*/
198 l_sql := ' select owner from sys.all_tables where table_name = ''HZ_STAGED_PARTIES'' and owner = :1';
199 EXECUTE IMMEDIATE l_sql into l_owner USING l_owner1;
200 END IF;
201 log('Truncating HZ_SHADOW_ST_PARTIES .. ', FALSE);
202 EXECUTE IMMEDIATE 'TRUNCATE TABLE '||l_owner||'.HZ_SHADOW_ST_PARTIES';
203 log('Done');
204
205 log('Truncating HZ_SHADOW_ST_PSITES .. ', FALSE);
206 EXECUTE IMMEDIATE 'TRUNCATE TABLE '||l_owner||'.HZ_SHADOW_ST_PSITES';
207 log('Done');
208
209 log('Truncating HZ_SHADOW_ST_CONTACTS .. ', FALSE);
210 EXECUTE IMMEDIATE 'TRUNCATE TABLE '||l_owner||'.HZ_SHADOW_ST_CONTACTS';
211 log('Done');
212
213 log('Truncating HZ_SHADOW_ST_CPTS .. ', FALSE);
214 EXECUTE IMMEDIATE 'TRUNCATE TABLE '||l_owner||'.hz_shadow_st_cpts';
215 log('Done');
216
217 /* Not Needed for staging SHADOW
218 log('Attempting to truncate HZ_DQM_SYNC_INTERFACE ..',FALSE);
219 BEGIN
220 EXECUTE IMMEDIATE 'TRUNCATE TABLE '||l_owner||'.hz_dqm_sync_interface';
221 log('Done Successfully');
222 EXCEPTION
223 WHEN OTHERS THEN
224 log('Lock on table. Unable to truncate');
225 END;
226 */
227
228 END;
229
230 PROCEDURE truncate_thin_tables IS
231 l_owner VARCHAR2(255);
232 l_status VARCHAR2(255);
233 l_owner1 VARCHAR2(255);
234 l_temp VARCHAR2(255);
235 l_sql VARCHAR2(4000);
236 BEGIN
237 log('---------------------------------------------------');
238 log('Calling truncate_thin_tables ');
239
240 IF (fnd_installation.GET_APP_INFO('AR',l_status,l_temp,l_owner1)) THEN
241 l_sql := ' select owner from sys.all_tables where table_name = ''HZ_THIN_ST_PARTIES'' and owner = :1';
242 EXECUTE IMMEDIATE l_sql into l_owner USING l_owner1;
243 /* select owner into l_owner from sys.all_objects
244 where object_name = 'HZ_THIN_ST_PARTIES' and OBJECT_TYPE = 'TABLE' and
245 owner = l_owner1;*/
246 END IF;
247 log('Truncating HZ_THIN_ST_PARTIES .. ', FALSE);
248 EXECUTE IMMEDIATE 'TRUNCATE TABLE '||l_owner||'.HZ_THIN_ST_PARTIES';
249 log('Done');
250
251 log('Truncating HZ_THIN_ST_PSITES .. ', FALSE);
252 EXECUTE IMMEDIATE 'TRUNCATE TABLE '||l_owner||'.HZ_THIN_ST_PSITES';
253 log('Done');
257 log('Done');
254
255 log('Truncating HZ_THIN_ST_CONTACTS .. ', FALSE);
256 EXECUTE IMMEDIATE 'TRUNCATE TABLE '||l_owner||'.HZ_THIN_ST_CONTACTS';
258
259 log('Truncating HZ_THIN_ST_CPTS .. ', FALSE);
260 EXECUTE IMMEDIATE 'TRUNCATE TABLE '||l_owner||'.HZ_THIN_ST_CPTS';
261 log('Done');
262
263 EXCEPTION
264 WHEN OTHERS THEN
265 log('Lock on table. Unable to truncate'||SQLERRM);
266 END;
267
268
269
270
271 PROCEDURE generate_map_pkg_nolog IS
272 BEGIN
273 HZ_GEN_PLSQL.new('HZ_STAGE_MAP_TRANSFORM_SHADOW', 'PACKAGE BODY');
274 l('CREATE OR REPLACE PACKAGE BODY HZ_STAGE_MAP_TRANSFORM_SHADOW AS');
275
276 generate_declarations;
277 generate_uds_proc;
278 generate_party_query_proc;
279 generate_contact_query_proc;
280 generate_contact_pt_query_proc;
281 generate_party_site_query_proc;
282
283 l('END;');
284
285 HZ_GEN_PLSQL.compile_code;
286 END;
287
288 PROCEDURE generate_uds_proc(p_entity varchar2, p_proc_name varchar2) IS
289 CURSOR c_trns(cp_entity VARCHAR2) IS
290 SELECT staged_attribute_column
291 FROM HZ_TRANS_ATTRIBUTES_VL a, HZ_TRANS_FUNCTIONS_VL f
292 WHERE ENTITY_NAME = cp_entity
293 AND f.ATTRIBUTE_ID = a.ATTRIBUTE_ID
294 AND nvl(f.ACTIVE_FLAG,'Y') = 'Y'
295 --AND nvl(f.PRIMARY_FLAG,'Y') = 'Y' --5044716
296 ;
297 l_trns varchar2(2000);
298 l_trns1 varchar2(2000);
299 first_time boolean := TRUE;
300 BEGIN
301 l('procedure '||p_proc_name||'(rid in rowid, tlob in out clob) IS');
302 l('data varchar2(32767);');
303 l('l_rec_id NUMBER(15);');
304 l('BEGIN');
305 l(' FOR R IN ( ');
306 l(' SELECT ');
307 OPEN c_trns(p_entity);
308 LOOP
309 FETCH c_trns INTO l_trns;
310 EXIT WHEN c_trns%NOTFOUND;
311 if first_time = true then
312 first_time := false;
313 else
314 l('||');
315 end if;
316 -- To add '<TX1>'||TX1||'</TX1>'
317 l('''<'||l_trns||'>'''||'||'||l_trns||'||''</'||l_trns||'>''');
318 END LOOP;
319 CLOSE c_trns; -- Nimit New
320
321 -- Begin Code changes for bug 5155761
322 if p_entity = 'PARTY' then
323 l(' data ');
324 l(' ,p.STATUS from hz_shadow_st_parties p,hz_thin_st_parties t '); -- Bug 5209633
325 end if;
326
327 --Create party_id, person_party and contact_point_type section
328 if p_entity = 'PARTY_SITES' then
329 l_trns := 'PARTY_ID';
330 l_trns1 := 'P.PARTY_ID';
331 l('||');
332 l('''<'||l_trns||'>'''||'||'||l_trns1||'||''</'||l_trns||'>''');
333 l_trns := 'PERSON_PARTY_ID';
334 l_trns1 := 'P.PERSON_PARTY_ID';
335 l('||');
336 l('''<'||l_trns||'>'''||'||'||l_trns1||'||''</'||l_trns||'>''');
337 l(' data ');
338 l(' from hz_shadow_st_psites p,hz_thin_st_psites t ');
339 end if;
340 if p_entity = 'CONTACTS' then
341 l_trns := 'PARTY_ID';
342 l_trns1 := 'P.PARTY_ID';
343 l('||');
344 l('''<'||l_trns||'>'''||'||'||l_trns1||'||''</'||l_trns||'>''');
345 l_trns := 'PERSON_PARTY_ID';
346 l_trns1 := 'P.PERSON_PARTY_ID';
347 l('||');
348 l('''<'||l_trns||'>'''||'||'||l_trns1||'||''</'||l_trns||'>''');
349 l(' data ');
350 l(' from hz_shadow_st_contacts p,hz_thin_st_contacts t ');
351 end if;
352 if p_entity = 'CONTACT_POINTS' then
353 l_trns := 'PARTY_ID';
354 l_trns1 := 'P.PARTY_ID';
355 l('||');
356 l('''<'||l_trns||'>'''||'||'||l_trns1||'||''</'||l_trns||'>''');
357 l_trns := 'PERSON_PARTY_ID';
358 l_trns1 := 'P.PERSON_PARTY_ID';
359 l('||');
360 l('''<'||l_trns||'>'''||'||'||l_trns1||'||''</'||l_trns||'>''');
361 l_trns := 'CONTACT_POINT_TYPE';
362 l_trns1 := 'P.CONTACT_POINT_TYPE';
363 l('||');
364 l('''<'||l_trns||'>'''||'||'||l_trns1||'||''</'||l_trns||'>''');
365 l(' data ');
366 l(' from HZ_SHADOW_ST_CPTS p,hz_thin_st_cpts t '); -- Bug 5209633
367 end if;
368 l(' where t.ROWID = rid and p.ROWID=parent_rowid) LOOP ');
369 l(' data := R.data;');
370 l(' dbms_lob.writeappend(tlob, length(data),data);');
371 l(' END LOOP;');
372 l('END;');
373 log('Done');
374 END;
375
376 PROCEDURE generate_uds_proc IS
377 BEGIN
378 log('---------------------------------------------------');
379 log('Calling generate_uds_proc');
380 generate_uds_proc('PARTY', 'PARTY_DS');
381 generate_uds_proc('PARTY_SITES', 'PARTY_SITE_DS');
382 generate_uds_proc('CONTACTS', 'CONTACT_DS');
383 generate_uds_proc('CONTACT_POINTS', 'CONTACT_POINT_DS');
384 END;
385 -------------------------------------------------------------------------------------
386 -- CHANGE : See if you can add a procedure called generate_user_ds_columns
387 --- which will generate four procedures party_pref, ps_pref, ct_pref, cpt_pref
388 -- in HZ_STAGE_MAP_TRANSFORM_R12
389 ------------------------------------------------------------------------------------
390
391
392 PROCEDURE generate_map_pkg IS
393 BEGIN
394 log('------------------------------------------------');
395 log('Calling generate_map_pkg');
396 log('Generating package body for HZ_STAGE_MAP_TRANSFORM_SHADOW');
397 HZ_GEN_PLSQL.new('HZ_STAGE_MAP_TRANSFORM_SHADOW', 'PACKAGE BODY');
398 l('CREATE OR REPLACE PACKAGE BODY HZ_STAGE_MAP_TRANSFORM_SHADOW AS');
399
400 generate_declarations;
401 generate_uds_proc;
402 generate_party_query_proc;
403 generate_contact_query_proc;
404 generate_contact_pt_query_proc;
405 generate_party_site_query_proc;
406
407 l('END;');
408
412 END;
409 log('Compiling package body .. ', false);
410 HZ_GEN_PLSQL.compile_code;
411 log('Done');
413
414
415 PROCEDURE add_section (
416 p_dsname VARCHAR2,
417 p_attr VARCHAR2,
418 p_stype VARCHAR2) IS
419 BEGIN
420 log('------------------------------------');
421 log('Calling add_section');
422 IF p_stype = 'ZONE' THEN
423 ctx_ddl.add_zone_section(p_dsname, p_attr,p_attr);
424 ELSE
425 ctx_ddl.add_field_section(p_dsname, p_attr,p_attr,TRUE);
426 END IF;
427 END;
428
429 PROCEDURE create_uds_prefs
430 IS
431 BEGIN
432 log(' In create_uds_prefs ');
433 begin
434 log('Trying to drop user data store preferences');
435 ctx_ddl.drop_preference(G_SCHEMA_NAME || '.hz_party_uds');
436 ctx_ddl.drop_preference(G_SCHEMA_NAME || '.hz_party_site_uds');
437 ctx_ddl.drop_preference(G_SCHEMA_NAME || '.hz_contact_uds');
438 ctx_ddl.drop_preference(G_SCHEMA_NAME || '.hz_contact_point_uds');
439
440 exception
441 when others then
442 log(' Exception while dropping preferences');
443 null;
444 end;
445
446 begin
447 log('Creating user data store preferences for PARTY');
448 ctx_ddl.create_preference('hz_party_uds', 'user_datastore');
449 ctx_ddl.set_attribute( 'hz_party_uds', 'procedure', 'HZ_STAGE_MAP_TRANSFORM_SHADOW.party_ds');
450 ctx_ddl.set_attribute( 'hz_party_uds', 'output_type', 'CLOB');
451
452 log('Creating user data store preferences for PARTY SITE');
453 ctx_ddl.create_preference('hz_party_site_uds', 'user_datastore');
454 ctx_ddl.set_attribute('hz_party_site_uds', 'procedure', 'HZ_STAGE_MAP_TRANSFORM_SHADOW.party_site_ds');
455 ctx_ddl.set_attribute('hz_party_site_uds', 'output_type', 'CLOB');
456
457 log('Creating user data store preferences for CONTACT');
458 ctx_ddl.create_preference('hz_contact_uds', 'user_datastore');
459 ctx_ddl.set_attribute('hz_contact_uds', 'procedure', 'HZ_STAGE_MAP_TRANSFORM_SHADOW.contact_ds');
460 ctx_ddl.set_attribute('hz_contact_uds', 'output_type', 'CLOB');
461
462 log('Creating user data store preferences for CONTACT POINT');
463 ctx_ddl.create_preference('hz_contact_point_uds', 'user_datastore');
464 ctx_ddl.set_attribute('hz_contact_point_uds', 'procedure', 'HZ_STAGE_MAP_TRANSFORM_SHADOW.contact_point_ds');
465 ctx_ddl.set_attribute('hz_contact_point_uds', 'output_type', 'CLOB');
466
467
468
469 exception
470 when others then
471 log('Error creating preferences. Error is '|| SQLERRM );
472 RAISE ;
473 end ;
474
475 log('Done creating user data store preferences ') ;
476 END ;
477
478
479 PROCEDURE create_section_group_prefs(p_entity VARCHAR2) IS
480
481 -- Cursor to get number of active acquistion enabled transformations
482 CURSOR c_num_trns(cp_entity VARCHAR2) IS
483 SELECT COUNT(1)
484 FROM HZ_TRANS_ATTRIBUTES_VL a, HZ_TRANS_FUNCTIONS_VL f
485 WHERE ENTITY_NAME = cp_entity
486 AND f.ATTRIBUTE_ID = a.ATTRIBUTE_ID
487 AND nvl(f.ACTIVE_FLAG,'Y') = 'Y'
488 --AND nvl(f.PRIMARY_FLAG,'Y') = 'Y' --5044716
489 ;
490
491 -- Cursor to get active acquistion enabled transformations
492 CURSOR c_trns(cp_entity VARCHAR2) IS
493 SELECT staged_attribute_column
494 FROM HZ_TRANS_ATTRIBUTES_VL a, HZ_TRANS_FUNCTIONS_VL f
495 WHERE ENTITY_NAME = cp_entity
496 AND f.ATTRIBUTE_ID = a.ATTRIBUTE_ID
497 AND nvl(f.ACTIVE_FLAG,'Y') = 'Y'
498 --AND nvl(f.PRIMARY_FLAG,'Y') = 'Y' --5044716
499 ;
500
501 l_trns VARCHAR2(255);
502
503 -- Nimit New End
504 l_cnt NUMBER;
505 l_stype VARCHAR2(255);
506
507 pref_cols VARCHAR2(1000);
508 proc_cols VARCHAR2(2000);
509 tmp VARCHAR2(2000);
510 section_grp_name varchar2(50);
511
512 BEGIN
513 log('-------------------------------------');
514 log('In create_section_group_prefs for ' || p_entity);
515 log('Trying to drop section group preferences');
516 BEGIN
517 IF p_entity='PARTY' THEN
518 ctx_ddl.drop_section_group(g_schema_name || '.HZ_DQM_PARTY_GRP');
519 /* No Need for R12 SHADOW Staging
520 UPDATE HZ_TRANS_ATTRIBUTES_B set TEMP_SECTION=NULL
521 WHERE ENTITY_NAME='PARTY';
522 */
523 END IF;
524
525 IF p_entity='PARTY_SITES' THEN
526 ctx_ddl.drop_section_group(G_SCHEMA_NAME || '.HZ_DQM_PS_GRP');
527 /* No Need for R12 SHADOW Staging
528 UPDATE HZ_TRANS_ATTRIBUTES_B set TEMP_SECTION=NULL
529 WHERE ENTITY_NAME='PARTY_SITES';
530 */
531 END IF;
532
533 IF p_entity='CONTACTS' THEN
534 ctx_ddl.drop_section_group(G_SCHEMA_NAME || '.HZ_DQM_CONTACT_GRP');
535 /* No Need for R12 SHADOW Staging
536 UPDATE HZ_TRANS_ATTRIBUTES_B set TEMP_SECTION=NULL
537 WHERE ENTITY_NAME='CONTACTS';
538 */
539 END IF;
540
541 IF p_entity='CONTACT_POINTS' THEN
542 ctx_ddl.drop_section_group(G_SCHEMA_NAME || '.HZ_DQM_CPT_GRP');
543 /* No Need for R12 SHADOW Staging
544 UPDATE HZ_TRANS_ATTRIBUTES_B set TEMP_SECTION=NULL
545 WHERE ENTITY_NAME='CONTACT_POINTS';
546 */
547 END IF;
548 /* No Need for R12 SHADOW Staging
549 log('Update of temp_section in HZ_TRANS_ATTRIBUTES_B successful for ' || p_entity);
550 */
551 EXCEPTION
552 WHEN OTHERS THEN
553 log('Exception thrown. Error message is ' || SQLERRM );
554 NULL;
555 END;
556
557 IF p_entity='PARTY' THEN
561 OPEN c_trns('PARTY');
558 log('Creating party section group',FALSE);
559 ctx_ddl.create_section_group(G_SCHEMA_NAME || '.HZ_DQM_PARTY_GRP','BASIC_SECTION_GROUP');
560
562 LOOP
563 FETCH c_trns INTO l_trns;
564 EXIT WHEN c_trns%NOTFOUND;
565 add_section(G_SCHEMA_NAME || '.HZ_DQM_PARTY_GRP',l_trns,'FIELD');
566 END LOOP;
567 CLOSE c_trns;
568
569 END IF;
570
571 IF p_entity='PARTY_SITES' THEN
572 log('Creating party site section group',FALSE);
573 ctx_ddl.create_section_group(G_SCHEMA_NAME || '.HZ_DQM_PS_GRP','BASIC_SECTION_GROUP');
574
575 OPEN c_trns('PARTY_SITES');
576 LOOP
577 FETCH c_trns INTO l_trns;
578 EXIT WHEN c_trns%NOTFOUND;
579 add_section(G_SCHEMA_NAME || '.HZ_DQM_PS_GRP',l_trns,'FIELD');
580 END LOOP;
581 CLOSE c_trns;
582 add_section(G_SCHEMA_NAME || '.HZ_DQM_PS_GRP','PARTY_ID','FIELD'); -- Fix for bug 5155761
583 add_section(G_SCHEMA_NAME || '.HZ_DQM_PS_GRP','PERSON_PARTY_ID','FIELD'); -- Fix for bug 5155761
584
585 END IF;
586
587 IF p_entity='CONTACTS' THEN
588 log('Creating contact section group',FALSE);
589 ctx_ddl.create_section_group(G_SCHEMA_NAME || '.HZ_DQM_CONTACT_GRP','BASIC_SECTION_GROUP');
590 OPEN c_trns('CONTACTS');
591 LOOP
592 FETCH c_trns INTO l_trns;
593 EXIT WHEN c_trns%NOTFOUND;
594 add_section(G_SCHEMA_NAME || '.HZ_DQM_CONTACT_GRP',l_trns,'FIELD');
595 END LOOP;
596 CLOSE c_trns;
597 add_section(G_SCHEMA_NAME || '.HZ_DQM_CONTACT_GRP','PARTY_ID','FIELD'); -- Fix for bug 5155761
598 add_section(G_SCHEMA_NAME || '.HZ_DQM_CONTACT_GRP','PERSON_PARTY_ID','FIELD'); -- Fix for bug 5155761
599
600 END IF;
601
602 IF p_entity='CONTACT_POINTS' THEN
603 log('Creating contact point section group',FALSE);
604 ctx_ddl.create_section_group(G_SCHEMA_NAME || '.HZ_DQM_CPT_GRP','BASIC_SECTION_GROUP');
605 -- add_section(G_SCHEMA_NAME || '.HZ_DQM_CPT_GRP','CONTACT_POINT_TYPE','FIELD');
606
607 OPEN c_trns('CONTACT_POINTS');
608 LOOP
609 FETCH c_trns INTO l_trns;
610 EXIT WHEN c_trns%NOTFOUND;
611 add_section(G_SCHEMA_NAME || '.HZ_DQM_CPT_GRP',l_trns,'FIELD');
612 END LOOP;
613 CLOSE c_trns;
614 add_section(G_SCHEMA_NAME || '.HZ_DQM_CPT_GRP','CONTACT_POINT_TYPE','FIELD'); -- Fix for bug 5155761
615 add_section(G_SCHEMA_NAME || '.HZ_DQM_CPT_GRP','PARTY_ID','FIELD'); -- Fix for bug 5155761
616 add_section(G_SCHEMA_NAME || '.HZ_DQM_CPT_GRP','PERSON_PARTY_ID','FIELD'); -- Fix for bug 5155761
617 END IF;
618
619
620 EXCEPTION
621 WHEN OTHERS THEN
622 log('Error create_section_group_prefs '||SQLERRM);
623 RAISE;
624 END;
625
626
627 PROCEDURE create_section_group_prefs IS
628
629 BEGIN
630 create_section_group_prefs('PARTY');
631 create_section_group_prefs('PARTY_SITES');
632 create_section_group_prefs('CONTACTS');
633 create_section_group_prefs('CONTACT_POINTS');
634 END;
635
636
637 PROCEDURE drop_context_indexes IS
638
639 l_status VARCHAR2(255);
640 l_index_owner VARCHAR2(255);
641 l_temp VARCHAR2(255);
642
643 l_bool BOOLEAN;
644
645 BEGIN
646
647 l_bool := fnd_installation.GET_APP_INFO('AR',l_status,l_temp,l_index_owner);
648 log('---------------------------------------------------');
649 log('Calling drop_context_indexes');
650 log('Dropping Context Indexes on Thin tables');
651 BEGIN
652 EXECUTE IMMEDIATE 'DROP INDEX ' || l_index_owner || '.hz_thin_st_parties_t1 FORCE';
653 log('Dropped hz_thin_st_parties_t1');
654 EXCEPTION
655 WHEN OTHERS THEN
656 log('Exception while dropping hz_thin_st_parties_t1. Error is ' || SQLERRM );
657 NULL;
658 END;
659 BEGIN
660 EXECUTE IMMEDIATE 'DROP INDEX ' || l_index_owner || '.hz_thin_st_psites_t1 FORCE';
661 log('Dropped hz_thin_st_psites_t1');
662 EXCEPTION
663 WHEN OTHERS THEN
664 log('Exception while dropping hz_thin_st_psites_t1. Error is ' || SQLERRM );
665 NULL;
666 END;
667 BEGIN
668 EXECUTE IMMEDIATE 'DROP INDEX ' || l_index_owner || '.hz_thin_st_contacts_t1 FORCE';
669 log('Dropped hz_thin_st_contacts_t1');
670 EXCEPTION
671 WHEN OTHERS THEN
672 log('Exception while dropping hz_thin_st_contacts_t1. Error is ' || SQLERRM );
673 NULL;
674 END;
675 BEGIN
676 EXECUTE IMMEDIATE 'DROP INDEX ' || l_index_owner || '.hz_thin_st_cpts_t1 FORCE';
677 log('Dropped hz_thin_st_cpts_t1');
678 EXCEPTION
679 WHEN OTHERS THEN
680 log('Exception while dropping hz_thin_st_cpts_t1. Error is ' || SQLERRM );
681 NULL;
682 END;
683 log('Done with dropping context indexes on Thin tables');
684 END;
685
686 PROCEDURE drop_btree_indexes IS
687 l_status VARCHAR2(255);
688 l_owner1 VARCHAR2(255);
689 l_temp VARCHAR2(255);
690 BEGIN
691 log('---------------------------------------------------');
692 log('Calling drop_btree_indexes');
693 log('Dropping Btree Indexes on Base Staging tables');
694 IF (fnd_installation.GET_APP_INFO('AR',l_status,l_temp,l_owner1)) THEN
695 FOR IDX in (
696 SELECT OWNER||'.'||INDEX_NAME idx_name
697 FROM sys.all_indexes i, hz_trans_attributes_vl a, hz_trans_functions_vl f
698 WHERE f.attribute_id = a.attribute_id
699 AND i.owner = l_owner1
700 AND f.index_required_flag in ('Y','T')
701 AND i.INDEX_NAME = decode(a.entity_name,'PARTY','HZ_SHADOW_ST_PARTIES',
702 'PARTY_SITES','HZ_SHADOW_ST_PSITES','CONTACTS','HZ_SHADOW_ST_CONTACTS',
703 'CONTACT_POINTS','HZ_SHADOW_ST_CPTS')||'_N0'||f.function_id) LOOP
707 /* Not Needed for R12 SHADOW STAGING
704 EXECUTE IMMEDIATE 'DROP INDEX '||IDX.idx_name;
705 END LOOP;
706 log('Done with dropping btree indexes on Base Staging tables');
708 UPDATE hz_trans_functions_b set index_required_flag='N' where index_required_flag='T';
709 */
710 END IF;
711 END;
712
713
714 PROCEDURE create_log(
715 p_operation VARCHAR2,
716 p_step VARCHAR2,
717 p_worker_number NUMBER DEFAULT 0,
718 p_num_workers NUMBER DEFAULT 0) IS
719
720 BEGIN
721
722 INSERT INTO HZ_DQM_STAGE_LOG (
723 OPERATION,
724 NUMBER_OF_WORKERS,
725 WORKER_NUMBER,
726 STEP,
727 START_FLAG,
728 START_TIME,
729 END_FLAG,
730 END_TIME,
731 CREATED_BY,
732 CREATION_DATE,
733 LAST_UPDATE_LOGIN,
734 LAST_UPDATE_DATE,
735 LAST_UPDATED_BY)
736 VALUES (
737 substr(p_operation,1,30),
738 p_num_workers,
739 p_worker_number,
740 p_step,
741 NULL,
742 NULL,
743 NULL,
744 NULL,
745 hz_utility_pub.created_by,
746 hz_utility_pub.creation_date,
747 hz_utility_pub.last_update_login,
748 hz_utility_pub.last_update_date,
749 hz_utility_pub.user_id
750 );
751 END;
752
753
754 PROCEDURE Stage (
755 errbuf OUT NOCOPY VARCHAR2,
756 retcode OUT NOCOPY VARCHAR2,
757 p_num_workers IN VARCHAR2,
758 p_continue IN VARCHAR2,
759 p_tablespace IN VARCHAR2,
760 p_index_creation IN VARCHAR2
761 ) IS
762
763 TYPE nTable IS TABLE OF NUMBER index by binary_integer;
764 TYPE vTable IS TABLE OF VARCHAR2(255) index by binary_integer;
765 l_sub_requests nTable;
766 l_req_status vTable;
767
768 uphase VARCHAR2(255);
769 dphase VARCHAR2(255);
770 ustatus VARCHAR2(255);
771 dstatus VARCHAR2(255);
772 message VARCHAR2(32000);
773
774 l_start_flag VARCHAR2(1);
775 l_end_flag VARCHAR2(1);
776
777 CURSOR c_primary(cp_entity_name VARCHAR2) IS
778 SELECT f.TRANSFORMATION_NAME, f.STAGED_ATTRIBUTE_COLUMN
779 FROM HZ_TRANS_FUNCTIONS_VL f, HZ_TRANS_ATTRIBUTES_VL a
780 WHERE PRIMARY_FLAG = 'Y'
781 AND nvl(f.ACTIVE_FLAG,'Y') = 'Y'
782 AND f.ATTRIBUTE_ID = a.ATTRIBUTE_ID
783 AND a.ENTITY_NAME = cp_entity_name;
784
785 l_trans_name HZ_TRANS_FUNCTIONS_VL.TRANSFORMATION_NAME%TYPE;
786 l_stg_attr HZ_TRANS_FUNCTIONS_VL.STAGED_ATTRIBUTE_COLUMN%TYPE;
787
788 l_cols VARCHAR2(4000);
789 l_message VARCHAR2(4000);
790 l_status VARCHAR2(255) := 'NORMAL';
791
792 l_num_workers NUMBER;
793 l_num_funcs NUMBER;
794 l_req_id NUMBER;
795 l_bool BOOLEAN;
796 l_continue VARCHAR2(1) := 'N';
797
798 l_party_prim_tx NUMBER := 1;
799 l_ps_prim_tx NUMBER := 1;
800 l_contact_prim_tx NUMBER := 1;
801 l_cpt_prim_tx NUMBER := 1;
802
803 l_index_owner VARCHAR2(255);
804 l_temp VARCHAR2(255);
805
806 l_idx_mem VARCHAR2(255);
807 l_num_prll VARCHAR2(255);
808
809
810
811
812
813 T NUMBER;
814
815 l_index_creation VARCHAR2(255) := 'PARALLEL';
816
817 l_command VARCHAR2(255);
818 l_last_num_workers NUMBER;
819 l_num_stage_stepS NUMBER;
820 reco_staging_size NUMBER;
821 reco_staging_parties NUMBER;
822 reco_staging_party_sites NUMBER;
823 reco_staging_contacts NUMBER;
824 reco_staging_contact_points NUMBER;
825 reco_index_size NUMBER;
826 safety_factor NUMBER(5, 1) := 2.0 ;
827 sizing_factor NUMBER(5, 1) := 5;
828
829 l_index VARCHAR2(255);
830
831 l_workers_completed boolean;
832
833
834 l_step VARCHAR2(255);
835 l_is_wildchar NUMBER;
836 req_data varchar2(100);
837
838 STAGE_ALL_DATA boolean := false;
839 GENERATE_MAP_PROC boolean := false;
840 CREATE_INDEXES boolean := false;
841 GEN_MISSING_INVALID_INDEXES boolean := false;
842 ESTIMATE_SIZE boolean := false;
843 STAGE_NEW_TRANSFORMATIONS boolean := false;
844 ANALYZE_STAGED_TABLES boolean := false;
845 create_index_flag boolean := false;
846 wait_for_child_flag boolean := false;
847
848 l_rebuild_party_idx boolean;
849 l_rebuild_psite_idx boolean;
850 l_rebuild_contact_idx boolean;
851 l_rebuild_cpt_idx boolean;
852
853 BEGIN
854
855 l_index_creation := nvl(p_index_creation,'PARALLEL');
856
857 IF p_num_workers IS NULL THEN
858 l_num_workers:=1;
859 ELSE
860 l_num_workers := to_number(p_num_workers);
861 END IF;
862
863 l_command:='STAGE_ALL_DATA';
864 l_continue:=nvl(p_continue,'N');
865 if (l_command = 'STAGE_ALL_DATA') then
866 STAGE_ALL_DATA := true;
867 CREATE_INDEXES := true;
868 GENERATE_MAP_PROC := true;
869 elsif (l_command = 'CREATE_INDEXES') then
870 CREATE_INDEXES := true;
871 l_continue := 'N';
872 elsif (l_command = 'STAGE_NEW_TRANSFORMATIONS') then
873 STAGE_NEW_TRANSFORMATIONS := true;
874 l_continue := 'N';
875 elsif (l_command = 'CREATE_MISSING_INVALID_INDEXES') then
876 GEN_MISSING_INVALID_INDEXES := true;
877 l_continue := 'N';
878 elsif (l_command = 'GENERATE_MAP_PROC') then
879 GENERATE_MAP_PROC := true;
880 l_continue := 'N';
881 elsif (l_command = 'ESTIMATE_SIZE') then
885 ANALYZE_STAGED_TABLES := true;
882 ESTIMATE_SIZE := true;
883 l_continue := 'N';
884 elsif (l_command = 'ANALYZE_STAGED_TABLES') then
886 l_continue := 'N';
887 end if;
888
889 -- REPURI. Bug 4884742. To delete the record
890 -- at the beginning of the staging program.
891
892 DELETE FROM HZ_DQM_STAGE_LOG
893 WHERE operation = 'SHADOW_STAGING'
894 AND step = 'COMPLETE';
895
896 -- req_data will be null the first time, by default
897 req_data := fnd_conc_global.request_data;
898
899 l_bool := fnd_installation.GET_APP_INFO('AR',l_status,l_temp,l_index_owner);
900 -- First Phase
901 IF (req_data IS NULL)
902 THEN
903 retcode := 0;
904
905 log('------------------------------');
906 outandlog('Starting Concurrent Program ''Stage Party Data''');
907 outandlog('Start Time ' || TO_CHAR(SYSDATE, 'MM-DD-YY HH24:MI:SS'));
908 outandlog('NEWLINE');
909
910
911 FND_MSG_PUB.initialize;
912
913
914 IF l_command = 'STAGE_ALL_DATA' THEN
915 BEGIN
916 SELECT count(*) into l_is_wildchar from HZ_DQM_STAGE_LOG where operation = 'SHADOW_STAGE_FOR_WILDCHAR_SRCH' and rownum = 1 ;
917 IF l_is_wildchar < 1 THEN
918 INSERT INTO HZ_DQM_STAGE_LOG(operation, number_of_workers, worker_number, step,
919 last_update_date, creation_date, created_by, last_updated_by)
920 VALUES ('SHADOW_STAGE_FOR_WILDCHAR_SRCH', '-1', '-1', 'Y', SYSDATE, SYSDATE, 0, 0);
921 END IF;
922
923 SELECT number_of_workers INTO l_last_num_workers
924 FROM HZ_DQM_STAGE_LOG
925 WHERE operation = l_command
926 AND STEP = 'SHADOW_INIT';
927
928 IF l_last_num_workers<>l_num_workers AND l_continue = 'Y' THEN
929 log('Cannot continue with different number of workers. Using ' ||
930 l_last_num_workers ||' workers, as specified in first run');
931 l_num_workers := l_last_num_workers;
932 END IF;
933 EXCEPTION
934 WHEN NO_DATA_FOUND THEN
935 l_continue := 'N';
936 END;
937 END IF;
938
939 if CREATE_INDEXES and l_continue <> 'Y' then
940 -- drop context indexes, btree indexes on all tables and truncate them
941 drop_context_indexes;
942 drop_btree_indexes ;
943 end if;
944
945 if STAGE_ALL_DATA and l_continue <> 'Y' then
946 truncate_staging_tables ;
947 truncate_thin_tables ;
948 -- Verify the validity of transformations and custom procedures,
949 verify_all_procs;
950 DELETE from HZ_DQM_STAGE_LOG where operation = l_command and
951 step like 'SHADOW%';
952
953
954
955 create_log(
956 p_operation=>l_command,
957 p_step=>'SHADOW_INIT',
958 p_num_workers=>l_num_workers);
959
960 l_num_stage_steps := g_num_stage_steps;
961
962 FOR I in 1..l_num_stage_steps LOOP
963 FOR J in 0..(l_num_workers-1) LOOP
964 create_log(
965 p_operation=>l_command,
966 p_step=>'SHADOW_STEP'||I,
967 p_worker_number=> J,
968 p_num_workers=>l_num_workers);
969 END LOOP;
970 END LOOP;
971 DELETE from HZ_DQM_STAGE_LOG where operation = 'SHADOW_POPULATE_THIN';
972 create_log(
973 p_operation=>'SHADOW_POPULATE_THIN',
974 p_step=>'HZ_PARTIES');
975 create_log(
976 p_operation=>'SHADOW_POPULATE_THIN',
977 p_step=>'HZ_PARTY_SITES');
978 create_log(
979 p_operation=>'SHADOW_POPULATE_THIN',
980 p_step=>'HZ_ORG_CONTACTS');
981 create_log(
982 p_operation=>'SHADOW_POPULATE_THIN',
983 p_step=>'HZ_CONTACT_POINTS');
984
985 DELETE from HZ_DQM_STAGE_LOG where operation = 'SHADOW_CREATE_INDEXES';
986 create_log(
987 p_operation=>'SHADOW_CREATE_INDEXES',
988 p_step=>'HZ_PARTIES');
989 create_log(
990 p_operation=>'SHADOW_CREATE_INDEXES',
991 p_step=>'HZ_PARTY_SITES');
992 create_log(
993 p_operation=>'SHADOW_CREATE_INDEXES',
994 p_step=>'HZ_ORG_CONTACTS');
995 create_log(
996 p_operation=>'SHADOW_CREATE_INDEXES',
997 p_step=>'HZ_CONTACT_POINTS');
998
999
1000
1001 -- Update Transformation metadata, to signify that none of the transformations
1002 -- are usable right now
1003 /* Not needed for R12 SHADOW Staging
1004 UPDATE HZ_TRANS_FUNCTIONS_B SET STAGED_FLAG='N';
1005 */
1006 end if;
1007
1008 if GENERATE_MAP_PROC then
1009 -- Create preferences for section groups
1010 create_section_group_prefs ;
1011 -- Create preferences for user data store
1012 create_uds_prefs ;
1013 -- Generate the procedures in HZ_STAGE_MAP_TRANSFORM_SHADOW, which will be eventually used by the
1014 -- Data Workers, to stage Data.
1015 generate_map_pkg;
1016 end if;
1017
1018
1022 -- DATA STAGING PART
1019 if STAGE_ALL_DATA or STAGE_NEW_TRANSFORMATIONS then
1020 --------------------------------------------------------------
1021 --------------------------------------------------------------
1023 --------------------------------------------------------------
1024 --------------------------------------------------------------
1025
1026
1027 -- Step : Fire off the Data Workers with the assumption that we will never fire off more than 10
1028 FOR I in 1..10 LOOP
1029 l_sub_requests(i) := 1;
1030 END LOOP;
1031
1032
1033 log('Spawning ' || l_num_workers || ' Data Workers for staging');
1034 FOR I in 1..l_num_workers LOOP
1035 l_sub_requests(i) := FND_REQUEST.SUBMIT_REQUEST('AR', 'ARHDQMSSTW',
1036 'Stage Party Shadow Data Worker ' || to_char(i),
1037 to_char(sysdate,'DD-MON-YY HH:MI:SS'),
1038 TRUE, to_char(l_num_workers), TO_CHAR(I), l_command,l_continue);
1039 IF l_sub_requests(i) = 0 THEN
1040 log('Error submitting worker ' || i);
1041 log(fnd_message.get);
1042 ELSE
1043 log('Submitted request for Worker ' || TO_CHAR(I) );
1044 log('Request ID : ' || l_sub_requests(i));
1045 END IF;
1046 EXIT when l_sub_requests(i) = 0;
1047 END LOOP;
1048
1049 -- wait for completion of all workers
1050 fnd_conc_global.set_req_globals(conc_status => 'PAUSED', request_data => 'DQM_DATA_WORKERS') ;
1051 return;
1052 end if;
1053 end if;
1054 if (req_data = 'DQM_DATA_WORKERS') then
1055 -- AFTER ALL THE WORKERS ARE DONE, SEE IF THEY HAVE ALL COMPLETED NORMALLY
1056
1057
1058 -- assume that all concurrent dup workers completed normally, unless found otherwise
1059 l_workers_completed := TRUE;
1060
1061 Select request_id BULK COLLECT into l_sub_requests
1062 from Fnd_Concurrent_Requests R
1063 Where Parent_Request_Id = FND_GLOBAL.conc_request_id
1064 and (phase_code<>'C' or status_code<>'C');
1065
1066 IF l_sub_requests.count>0 THEN
1067 l_workers_completed:=FALSE;
1068 FOR I in 1..l_sub_requests.COUNT LOOP
1069 outandlog('Data worker with request id ' || l_sub_requests(I) );
1070 outandlog('did not complete normally');
1071 retcode := 2;
1072 END LOOP;
1073 END IF;
1074
1075 if (l_workers_completed = false) then
1076 return;
1077 end if;
1078
1079 log('Data workers completed');
1080 if (STAGE_ALL_DATA) then
1081 BEGIN
1082 SELECT start_flag, end_flag INTO l_start_flag, l_end_flag
1083 FROM HZ_DQM_STAGE_LOG
1084 WHERE OPERATION = 'SHADOW_POPULATE_THIN'
1085 AND step = 'HZ_PARTIES';
1086 EXCEPTION
1087 WHEN no_data_found THEN
1088 l_start_flag:=NULL;
1089 l_end_flag:=NULL;
1090 END;
1091 IF nvl(l_end_flag,'N') = 'N' THEN
1092 if nvl(l_start_flag, 'N') = 'Y' then
1093 log('Truncating HZ_THIN_ST_PARTIES .. ', FALSE);
1094 EXECUTE IMMEDIATE 'TRUNCATE TABLE '||l_index_owner||'.HZ_THIN_ST_PARTIES';
1095 log('Done');
1096 end if;
1097 UPDATE HZ_DQM_STAGE_LOG set START_FLAG = 'Y', END_TIME=SYSDATE
1098 WHERE operation = 'SHADOW_POPULATE_THIN' AND step ='HZ_PARTIES';
1099 COMMIT;
1100 insert_into_thin_tables('PARTIES');
1101 UPDATE HZ_DQM_STAGE_LOG set END_FLAG = 'Y', END_TIME=SYSDATE
1102 WHERE operation = 'SHADOW_POPULATE_THIN' AND step ='HZ_PARTIES';
1103 COMMIT;
1104 END IF;
1105 BEGIN
1106 SELECT start_flag, end_flag INTO l_start_flag, l_end_flag
1107 FROM HZ_DQM_STAGE_LOG
1108 WHERE OPERATION = 'SHADOW_POPULATE_THIN'
1109 AND step = 'HZ_PARTY_SITES';
1110 EXCEPTION
1111 WHEN no_data_found THEN
1112 l_start_flag:=NULL;
1113 l_end_flag:=NULL;
1114 END;
1115 IF nvl(l_end_flag,'N') = 'N' THEN
1116 if nvl(l_start_flag, 'N') = 'Y' then
1117 log('Truncating HZ_THIN_ST_PSITES .. ', FALSE);
1118 EXECUTE IMMEDIATE 'TRUNCATE TABLE '||l_index_owner||'.HZ_THIN_ST_PSITES';
1119 log('Done');
1120 end if;
1121 UPDATE HZ_DQM_STAGE_LOG set START_FLAG = 'Y', END_TIME=SYSDATE
1122 WHERE operation = 'SHADOW_POPULATE_THIN' AND step ='HZ_PARTY_SITES';
1123 COMMIT;
1124 insert_into_thin_tables('PARTY_SITES');
1125 UPDATE HZ_DQM_STAGE_LOG set END_FLAG = 'Y', END_TIME=SYSDATE
1126 WHERE operation = 'SHADOW_POPULATE_THIN' AND step ='HZ_PARTY_SITES';
1127 COMMIT;
1128 END IF;
1129 BEGIN
1130 SELECT start_flag, end_flag INTO l_start_flag, l_end_flag
1131 FROM HZ_DQM_STAGE_LOG
1132 WHERE OPERATION = 'SHADOW_POPULATE_THIN'
1133 AND step = 'HZ_ORG_CONTACTS';
1134 EXCEPTION
1135 WHEN no_data_found THEN
1136 l_start_flag:=NULL;
1137 l_end_flag:=NULL;
1138 END;
1139 IF nvl(l_end_flag,'N') = 'N' THEN
1140 if nvl(l_start_flag, 'N') = 'Y' then
1144 end if;
1141 log('Truncating HZ_THIN_ST_CONTACTS .. ', FALSE);
1142 EXECUTE IMMEDIATE 'TRUNCATE TABLE '||l_index_owner||'.HZ_THIN_ST_CONTACTS';
1143 log('Done');
1145 UPDATE HZ_DQM_STAGE_LOG set START_FLAG = 'Y', END_TIME=SYSDATE
1146 WHERE operation = 'SHADOW_POPULATE_THIN' AND step ='HZ_ORG_CONTACTS';
1147 COMMIT;
1148 insert_into_thin_tables('CONTACTS') ;
1149 UPDATE HZ_DQM_STAGE_LOG set END_FLAG = 'Y', END_TIME=SYSDATE
1150 WHERE operation = 'SHADOW_POPULATE_THIN' AND step ='HZ_ORG_CONTACTS';
1151 COMMIT;
1152 END IF;
1153 BEGIN
1154 SELECT start_flag, end_flag INTO l_start_flag, l_end_flag
1155 FROM HZ_DQM_STAGE_LOG
1156 WHERE OPERATION = 'SHADOW_POPULATE_THIN'
1157 AND step = 'HZ_CONTACT_POINTS';
1158 EXCEPTION
1159 WHEN no_data_found THEN
1160 l_start_flag:=NULL;
1161 l_end_flag:=NULL;
1162 END;
1163 IF nvl(l_end_flag,'N') = 'N' THEN
1164 if nvl(l_start_flag, 'N') = 'Y' then
1165 log('Truncating HZ_THIN_ST_CPTS .. ', FALSE);
1166 EXECUTE IMMEDIATE 'TRUNCATE TABLE '||l_index_owner||'.HZ_THIN_ST_CPTS';
1167 log('Done');
1168 end if;
1169 UPDATE HZ_DQM_STAGE_LOG set START_FLAG = 'Y', END_TIME=SYSDATE
1170 WHERE operation = 'SHADOW_POPULATE_THIN' AND step ='HZ_CONTACT_POINTS';
1171 COMMIT;
1172 insert_into_thin_tables('CONTACT_POINTS') ;
1173 UPDATE HZ_DQM_STAGE_LOG set END_FLAG = 'Y', END_TIME=SYSDATE
1174 WHERE operation = 'SHADOW_POPULATE_THIN' AND step ='HZ_CONTACT_POINTS';
1175 COMMIT;
1176 END IF;
1177 log('inserted into thin tables');
1178 end if;
1179 end if;
1180
1181 if (CREATE_INDEXES or GEN_MISSING_INVALID_INDEXES or STAGE_NEW_TRANSFORMATIONS) and
1182 (req_data IS NULL or req_data = 'DQM_DATA_WORKERS') then
1183 --------------------------------------------------------------
1184 --------------------------------------------------------------
1185 -- INDEX STAGING PART
1186 --------------------------------------------------------------
1187 --------------------------------------------------------------
1188
1189 -- Create Initial Preferences -- Storage, Memory, Theme etc.,
1190 create_initial_prefs( p_tablespace, l_num_prll, l_idx_mem) ;
1191 outandlog('index mem : '||l_idx_mem||' num parallel :'||l_num_prll);
1192
1193 if (l_idx_mem = '0') then
1194 l_idx_mem := '500M' ;
1195 end if;
1196 wait_for_child_flag := false;
1197 -- Step : Fire off the PARTY Index worker, once we have the request id for the first Data Worker
1198 for I in 1..4 loop
1199 if (CREATE_INDEXES or (STAGE_NEW_TRANSFORMATIONS and
1200 ((I = 1 and l_rebuild_party_idx) or
1201 (I = 2 and l_rebuild_psite_idx) or
1202 (I = 3 and l_rebuild_contact_idx) or
1203 (I = 4 and l_rebuild_cpt_idx)))) then
1204 create_index_flag := true;
1205 else
1206 create_index_flag := false;
1207 end if;
1208 if I = 1 then
1209 l_index := 'HZ_PARTIES';
1210 if create_index_flag = false and STAGE_NEW_TRANSFORMATIONS = false then
1211 BEGIN
1212 SELECT 1 INTO T FROM HZ_THIN_ST_PARTIES
1213 WHERE ROWNUM=1
1214 AND CONTAINS (concat_col, 'dummy_string')>0;
1215 EXCEPTION
1216 WHEN NO_DATA_FOUND THEN
1217 UPDATE HZ_DQM_STAGE_LOG
1218 SET START_FLAG ='Y', START_TIME=SYSDATE,END_FLAG = 'Y', END_TIME=SYSDATE
1219 WHERE OPERATION = 'SHADOW_CREATE_INDEXES' AND step = 'HZ_PARTIES';
1220 log('hz_thin_st_parties_t1 is valid. No recreation necessary.');
1221 WHEN OTHERS THEN
1222 BEGIN
1223 EXECUTE IMMEDIATE 'DROP INDEX ' || l_index_owner || '.hz_thin_st_parties_t1 FORCE';
1224 log('Dropped hz_thin_st_parties_t1');
1225 EXCEPTION
1226 WHEN OTHERS THEN
1227 log('Exception while dropping hz_thin_st_parties_t1. Error is ' || SQLERRM );
1228 NULL;
1229 END;
1230 create_index_flag := true;
1231 END;
1232 end if;
1233 elsif I = 2 then
1234 l_index := 'HZ_PARTY_SITES';
1235 if create_index_flag = false and STAGE_NEW_TRANSFORMATIONS = false then
1236 BEGIN
1237 SELECT 1 INTO T FROM HZ_THIN_ST_PSITES
1238 WHERE ROWNUM=1
1239 AND CONTAINS (concat_col, 'dummy_string')>0;
1240 EXCEPTION
1241 WHEN NO_DATA_FOUND THEN
1242 UPDATE HZ_DQM_STAGE_LOG
1243 SET START_FLAG ='Y', START_TIME=SYSDATE,END_FLAG = 'Y', END_TIME=SYSDATE
1244 WHERE OPERATION = 'SHADOW_CREATE_INDEXES' AND step = 'HZ_PARTY_SITES';
1245 log('hz_thin_st_psites_t1 is valid. No recreation necessary.');
1246 WHEN OTHERS THEN
1247 BEGIN
1248 EXECUTE IMMEDIATE 'DROP INDEX ' || l_index_owner || '.hz_thin_st_psites_t1 FORCE';
1249 log('Dropped hz_thin_st_psites_t1');
1250 EXCEPTION
1251 WHEN OTHERS THEN
1255 create_index_flag := true;
1252 log('Exception while dropping hz_thin_st_psites_t1. Error is ' || SQLERRM );
1253 NULL;
1254 END;
1256 END;
1257 end if;
1258 elsif I = 3 then
1259 l_index := 'HZ_ORG_CONTACTS';
1260 if create_index_flag = false and STAGE_NEW_TRANSFORMATIONS = false then
1261 BEGIN
1262 SELECT 1 INTO T FROM HZ_THIN_ST_CONTACTS
1263 WHERE ROWNUM=1
1264 AND CONTAINS (concat_col, 'dummy_string')>0;
1265 EXCEPTION
1266 WHEN NO_DATA_FOUND THEN
1267 UPDATE HZ_DQM_STAGE_LOG
1268 SET START_FLAG ='Y', START_TIME=SYSDATE,END_FLAG = 'Y', END_TIME=SYSDATE
1269 WHERE OPERATION = 'SHADOW_CREATE_INDEXES' AND step = 'HZ_ORG_CONTACTS';
1270 log('hz_thin_st_contacts_t1 is valid. No recreation necessary.');
1271 WHEN OTHERS THEN
1272 BEGIN
1273 EXECUTE IMMEDIATE 'DROP INDEX ' || l_index_owner || '.hz_thin_st_contacts_t1 FORCE';
1274 log('Dropped hz_thin_st_contacts_t1');
1275 EXCEPTION
1276 WHEN OTHERS THEN
1277 log('Exception while dropping hz_thin_st_contacts_t1. Error is ' || SQLERRM );
1278 NULL;
1279 END;
1280 create_index_flag := true;
1281 END;
1282 end if;
1283 elsif I = 4 and STAGE_NEW_TRANSFORMATIONS = false then
1284 l_index := 'HZ_CONTACT_POINTS';
1285 if create_index_flag = false then
1286 BEGIN
1287 SELECT 1 INTO T FROM HZ_THIN_ST_CPTS
1288 WHERE ROWNUM=1
1289 AND CONTAINS (concat_col, 'dummy_string')>0;
1290 EXCEPTION
1291 WHEN NO_DATA_FOUND THEN
1292 UPDATE HZ_DQM_STAGE_LOG
1293 SET START_FLAG ='Y', START_TIME=SYSDATE,END_FLAG = 'Y', END_TIME=SYSDATE
1294 WHERE OPERATION = 'SHADOW_CREATE_INDEXES' AND step = 'HZ_CONTACT_POINTS';
1295 log('hz_thin_st_cpts_t1 is valid. No recreation necessary.');
1296 WHEN OTHERS THEN
1297 BEGIN
1298 EXECUTE IMMEDIATE 'DROP INDEX ' || l_index_owner || '.hz_thin_st_cpts_t1 FORCE';
1299 log('Dropped hz_thin_st_cpts_t1');
1300 EXCEPTION
1301 WHEN OTHERS THEN
1302 log('Exception while dropping hz_thin_st_cpts_t1. Error is ' || SQLERRM );
1303 NULL;
1304 END;
1305 create_index_flag := true;
1306 END;
1307 end if;
1308 end if;
1309 if create_index_flag then
1310 l_req_id := FND_REQUEST.SUBMIT_REQUEST('AR', 'ARHDQMSCR',
1311 'DQM Create Index On Thin Tables', to_char(sysdate,'DD-MON-YY HH:MI:SS'),
1312 TRUE, l_command, l_idx_mem, l_num_prll, l_index);
1313 IF l_req_id = 0 THEN
1314 log('Error submitting request');
1315 log(fnd_message.get);
1316 ELSE
1317 log('Submitted request ID for '||l_index||': ' || l_req_id );
1318 log('Request ID : ' || l_req_id);
1319 END IF;
1320 wait_for_child_flag := true;
1321 end if;
1322 end loop;
1323
1324
1325 if (wait_for_child_flag) then
1326 -- wait for completion of all workers
1327 fnd_conc_global.set_req_globals(conc_status => 'PAUSED', request_data => 'DQM_INDEX_WORKERS') ;
1328 return;
1329 /* Not needed for R12 SHADOW Staging
1330 else
1331 if (STAGE_ALL_DATA or STAGE_NEW_TRANSFORMATIONS) then
1332 -- FINALLY UPDATE THE STAGED_FLAG IN HZ_TRANS_FUNCTIONS
1333 UPDATE HZ_TRANS_FUNCTIONS_B
1334 SET STAGED_FLAG='Y' WHERE nvl(ACTIVE_FLAG,'Y') = 'Y';
1335 end if;
1336 */
1337 end if;
1338 end if;
1339
1340 if req_data = 'DQM_INDEX_WORKERS' then
1341 -- AFTER ALL THE WORKERS ARE DONE, SEE IF THEY HAVE ALL COMPLETED NORMALLY
1342
1343
1344 -- assume that all concurrent dup workers completed normally, unless found otherwise
1345 l_workers_completed := TRUE;
1346
1347 Select request_id BULK COLLECT into l_sub_requests
1348 from Fnd_Concurrent_Requests R
1349 Where Parent_Request_Id = FND_GLOBAL.conc_request_id
1350 and (phase_code<>'C' or status_code<>'C');
1351
1352 IF l_sub_requests.count>0 THEN
1353 l_workers_completed:=FALSE;
1354 FOR I in 1..l_sub_requests.COUNT LOOP
1355 outandlog('Index worker with request id ' || l_sub_requests(I) );
1356 outandlog('did not complete normally');
1357 retcode := 2;
1358 END LOOP;
1359 END IF;
1360
1361 if (l_workers_completed = false) then
1362 return;
1363 end if;
1364
1365 log('Index workers completed');
1366 /* Not needed for R12 SHADOW Staging
1367 if (STAGE_ALL_DATA or STAGE_NEW_TRANSFORMATIONS) then
1368 -- FINALLY UPDATE THE STAGED_FLAG IN HZ_TRANS_FUNCTIONS
1369 UPDATE HZ_TRANS_FUNCTIONS_B
1370 SET STAGED_FLAG='Y' WHERE nvl(ACTIVE_FLAG,'Y') = 'Y';
1371 end if;
1372 */
1373 end if;
1374
1375 -- REPURI. Bug 4884742. To insert a record into hz_dqm_stage_log table
1376 -- indicating that shadow staging conc prog has completed successfully.
1377
1381 outandlog('End Time : '|| TO_CHAR(SYSDATE, 'MM-DD-YY HH24:MI:SS'));
1378 create_log ('SHADOW_STAGING','COMPLETE');
1379
1380 outandlog('Concurrent Program Execution completed ');
1382
1383 EXCEPTION
1384 WHEN FND_API.G_EXC_ERROR THEN
1385 outandlog('Error: Aborting staging');
1386 retcode := 2;
1387 errbuf := errbuf || logerror || SQLERRM;
1388 FND_FILE.close;
1389 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1390 outandlog('Error: Aborting staging');
1391 retcode := 2;
1392 errbuf := errbuf || logerror || SQLERRM;
1393 FND_FILE.close;
1394 WHEN OTHERS THEN
1395 log(fnd_message.get);
1396 outandlog('Error: Aborting staging');
1397 outandlog(SQLERRM);
1398 retcode := 2;
1399 errbuf := errbuf || logerror || SQLERRM;
1400 FND_FILE.close;
1401 END;
1402
1403
1404 PROCEDURE Stage_worker(
1405 errbuf OUT NOCOPY VARCHAR2,
1406 retcode OUT NOCOPY VARCHAR2,
1407 p_num_workers IN VARCHAR2,
1408 p_worker_number IN VARCHAR2,
1409 p_command IN VARCHAR2,
1410 p_continue IN VARCHAR2
1411
1412 ) IS
1413
1414 CURSOR l_log_cur(p_command VARCHAR2, l_worker_number NUMBER, l_step VARCHAR2) is
1415 ( SELECT start_flag, end_flag
1416 FROM HZ_DQM_STAGE_LOG
1417 WHERE OPERATION = p_command
1418 AND WORKER_NUMBER = l_worker_number AND step = l_step);
1419
1420 l_party_id NUMBER;
1421 l_party_type HZ_PARTIES.PARTY_TYPE%TYPE;
1422
1423 l_party_staged VARCHAR2(1);
1424 l_temp_party_id NUMBER;
1425 l_worker_number NUMBER;
1426 l_num_workers NUMBER;
1427
1428 l_index_owner VARCHAR2(255);
1429 l_temp VARCHAR2(255);
1430 l_bool BOOLEAN;
1431 l_status VARCHAR2(255);
1432
1433 l_error VARCHAR2(2000);
1434
1435 l_party_cur StageCurTyp;
1436 l_party_site_cur StageCurTyp;
1437 l_contact_cur StageCurTyp;
1438 l_cpt_cur StageCurTyp;
1439
1440 l_start_flag VARCHAR2(30);
1441 l_end_flag VARCHAR2(30);
1442
1443 l_number_of_workers NUMBER;
1444 l_startdate DATE;
1445
1446 l_log_step VARCHAR2(30);
1447
1448 BEGIN
1449
1450 retcode := 0;
1451 l_worker_number := TO_NUMBER(p_worker_number);
1452 l_num_workers := TO_NUMBER(p_num_workers);
1453 IF l_worker_number = l_num_workers THEN
1454 l_worker_number := 0;
1455 END IF;
1456
1457 log('----------------------------------------------------------');
1458 log('Starting Concurrent Program ''Stage Party Data'', Worker: ' ||
1459 p_worker_number);
1460 log('Start Time ' || TO_CHAR(SYSDATE, 'MM-DD-YY HH24:MI:SS'));
1461
1462
1463 log('This worker stages all parties whose party_id/10 produces a remainder equal to the worker number ');
1464 log('');
1465 log('Stage Parties begin');
1466
1467 FND_MSG_PUB.initialize;
1468 HZ_TRANS_PKG.set_staging_context('Y');
1469
1470 SELECT SYSDATE INTO l_startdate FROM DUAL;
1471
1472 log('Staging Organization Party Records');
1473
1474 OPEN l_log_cur(p_command, l_worker_number, 'SHADOW_STEP1');
1475 FETCH l_log_cur INTO l_start_flag, l_end_flag;
1476 IF (l_log_cur%NOTFOUND) THEN
1477 l_start_flag:=NULL;
1478 l_end_flag:=NULL;
1479 END IF;
1480 CLOSE l_log_cur;
1481
1482 log('Start Flag is ' || l_start_flag );
1483 log('End Flag is ' || l_end_flag );
1484 log('Command is ' || p_command);
1485
1486
1487 IF nvl(l_end_flag,'N') = 'N' THEN
1488 IF nvl(l_start_flag ,'N') = 'N' THEN
1489
1490 l_log_step := 'SHADOW_STEP1';
1491 UPDATE HZ_DQM_STAGE_LOG set start_flag='Y', start_time = SYSDATE
1492 WHERE OPERATION = p_command
1493 AND WORKER_NUMBER = l_worker_number AND step = l_log_step;
1494 COMMIT;
1495 log(' Regular Flow -- Fresh Run of Staging');
1496 HZ_TRANS_PKG.set_party_type('ORGANIZATION');
1497 HZ_STAGE_MAP_TRANSFORM_SHADOW.open_party_cursor(
1498 'ALL_PARTIES', 'ORGANIZATION',l_worker_number, l_num_workers, NULL,'N', l_party_cur);
1499 HZ_STAGE_MAP_TRANSFORM_SHADOW.insert_stage_parties('N',l_party_cur);
1500 ELSE
1501 HZ_TRANS_PKG.set_party_type('ORGANIZATION');
1502 log(' Continue for Org cursor');
1503 HZ_STAGE_MAP_TRANSFORM_SHADOW.open_party_cursor(
1504 'ALL_PARTIES', 'ORGANIZATION',l_worker_number, l_num_workers, NULL,'Y', l_party_cur);
1505 HZ_STAGE_MAP_TRANSFORM_SHADOW.insert_stage_parties('Y',l_party_cur);
1506 END IF;
1507
1508 CLOSE l_party_cur;
1509 l_log_step := 'SHADOW_STEP1';
1510 UPDATE HZ_DQM_STAGE_LOG set end_flag='Y', end_time = SYSDATE
1511 WHERE OPERATION = p_command
1512 AND WORKER_NUMBER = l_worker_number AND step = l_log_step;
1513
1514 COMMIT;
1515 END IF;
1516
1517 log('Staging Person Party Records');
1518 OPEN l_log_cur(p_command, l_worker_number, 'SHADOW_STEP2');
1519 FETCH l_log_cur INTO l_start_flag, l_end_flag;
1520 IF (l_log_cur%NOTFOUND) THEN
1521 l_start_flag:=NULL;
1522 l_end_flag:=NULL;
1523 END IF;
1524 CLOSE l_log_cur;
1525
1526 log('Start Flag is ' || l_start_flag );
1527 log('End Flag is ' || l_end_flag );
1528 log('Command is ' || p_command);
1529
1530 IF nvl(l_end_flag,'N') = 'N' THEN
1531 IF nvl(l_start_flag,'N') = 'N' THEN
1532 l_log_step := 'SHADOW_STEP2';
1536 COMMIT;
1533 UPDATE HZ_DQM_STAGE_LOG set start_flag='Y', start_time = SYSDATE
1534 WHERE OPERATION = p_command
1535 AND WORKER_NUMBER = l_worker_number AND step = l_log_step;
1537 log(' Regular Flow -- Fresh Run of Staging');
1538 HZ_TRANS_PKG.set_party_type('PERSON');
1539 HZ_STAGE_MAP_TRANSFORM_SHADOW.open_party_cursor(
1540 'ALL_PARTIES', 'PERSON',l_worker_number, l_num_workers, NULL,'N', l_party_cur);
1541 HZ_STAGE_MAP_TRANSFORM_SHADOW.insert_stage_parties('N',l_party_cur);
1542 ELSE
1543 log(' Continue for Per cursor');
1544 HZ_TRANS_PKG.set_party_type('PERSON');
1545 HZ_STAGE_MAP_TRANSFORM_SHADOW.open_party_cursor(
1546 'ALL_PARTIES', 'PERSON',l_worker_number, l_num_workers, NULL,'Y', l_party_cur);
1547 HZ_STAGE_MAP_TRANSFORM_SHADOW.insert_stage_parties('Y',l_party_cur);
1548 END IF;
1549
1550 CLOSE l_party_cur;
1551 l_log_step := 'SHADOW_STEP2';
1552 UPDATE HZ_DQM_STAGE_LOG set end_flag='Y', end_time = SYSDATE
1553 WHERE OPERATION = p_command
1554 AND WORKER_NUMBER = l_worker_number AND step = l_log_step;
1555
1556 COMMIT;
1557 END IF;
1558
1559 log('Staging Group Party Records');
1560 OPEN l_log_cur(p_command, l_worker_number, 'SHADOW_STEP3');
1561 FETCH l_log_cur INTO l_start_flag, l_end_flag;
1562 IF (l_log_cur%NOTFOUND) THEN
1563 l_start_flag:=NULL;
1564 l_end_flag:=NULL;
1565 END IF;
1566 CLOSE l_log_cur;
1567
1568 log('Start Flag is ' || l_start_flag );
1569 log('End Flag is ' || l_end_flag );
1570 log('Command is ' || p_command);
1571
1572 IF nvl(l_end_flag,'N') = 'N' THEN
1573 IF nvl(l_start_flag,'N') = 'N' THEN
1574 l_log_step := 'SHADOW_STEP3';
1575 UPDATE HZ_DQM_STAGE_LOG set start_flag='Y', start_time = SYSDATE
1576 WHERE OPERATION = p_command
1577 AND WORKER_NUMBER = l_worker_number AND step = l_log_step;
1578 COMMIT;
1579 log(' Regular Flow -- Fresh Run of Staging');
1580 HZ_TRANS_PKG.set_party_type('OTHER');
1581 HZ_STAGE_MAP_TRANSFORM_SHADOW.open_party_cursor(
1582 'ALL_PARTIES', 'OTHER',l_worker_number, l_num_workers, NULL,'N', l_party_cur);
1583 HZ_STAGE_MAP_TRANSFORM_SHADOW.insert_stage_parties('N',l_party_cur);
1584 ELSE
1585 log(' Continue for Oth cursor');
1586 HZ_TRANS_PKG.set_party_type('OTHER');
1587 HZ_STAGE_MAP_TRANSFORM_SHADOW.open_party_cursor(
1588 'ALL_PARTIES', 'OTHER',l_worker_number, l_num_workers, NULL,'Y', l_party_cur);
1589 HZ_STAGE_MAP_TRANSFORM_SHADOW.insert_stage_parties('Y',l_party_cur);
1590 END IF;
1591
1592 CLOSE l_party_cur;
1593 l_log_step := 'SHADOW_STEP3';
1594 UPDATE HZ_DQM_STAGE_LOG set end_flag='Y', end_time = SYSDATE
1595 WHERE OPERATION = p_command
1596 AND WORKER_NUMBER = l_worker_number AND step = l_log_step;
1597
1598 COMMIT;
1599
1600 END IF;
1601
1602 /* Not needed for R12 Shadow Staging
1603 log('Deleting records from HZ_DQM_SYNC_INTERFACE, that have a earlier creation date');
1604 DELETE FROM HZ_DQM_SYNC_INTERFACE WHERE mod(PARTY_ID,l_num_workers) = l_worker_number
1605 AND creation_date<=l_startdate;
1606 */
1607
1608 log('Concurrent Program Execution completed ');
1609 log('End Time : '|| TO_CHAR(SYSDATE, 'MM-DD-YY HH24:MI:SS'));
1610
1611 EXCEPTION
1612 WHEN FND_API.G_EXC_ERROR THEN
1613 outandlog('Error: Aborting staging ' || FND_MESSAGE.GET);
1614 retcode := 2;
1615 errbuf := errbuf || logerror;
1616 FND_FILE.close;
1617 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1618 outandlog('Error: Aborting staging ' || FND_MESSAGE.GET);
1619 retcode := 2;
1620 errbuf := errbuf || logerror;
1621 FND_FILE.close;
1622 WHEN OTHERS THEN
1623 outandlog('Error: Aborting staging '|| SQLERRM);
1624 retcode := 2;
1625 errbuf := errbuf || logerror;
1626 FND_FILE.close;
1627 END;
1628
1629 FUNCTION new_primary_trans(p_entity VARCHAR2
1630 ) RETURN BOOLEAN IS
1631 cursor is_new_tran (p_entity VARCHAR2) is
1632 select 'Y'
1633 from hz_trans_functions_vl t, hz_trans_attributes_b a
1634 where a.attribute_id = t.attribute_id
1635 and ENTITY_NAME = p_entity
1636 and nvl(staged_flag, 'N') = 'N'
1637 and primary_flag = 'Y'
1638 and nvl(active_flag, 'Y') = 'Y'
1639 and rownum = 1;
1640 l_var VARCHAR2(1);
1641 BEGIN
1642 l_var := 'N';
1643 OPEN is_new_tran(p_entity);
1644 FETCH is_new_tran INTO l_var;
1645 CLOSE is_new_tran;
1646 IF (l_var = 'Y') THEN
1647 return true;
1648 ELSE
1649 return false;
1650 END IF;
1651 EXCEPTION WHEN OTHERS THEN
1652 CLOSE is_new_tran;
1653 return true;
1654 END new_primary_trans;
1655
1656 PROCEDURE Stage_create_index (
1657 errbuf OUT NOCOPY VARCHAR2,
1658 retcode OUT NOCOPY VARCHAR2,
1659 p_command IN VARCHAR2,
1660 p_idx_mem IN VARCHAR2,
1661 p_num_prll IN VARCHAR2,
1662 p_index IN VARCHAR2
1663 ) IS
1664
1665 CURSOR c_num_attrs (p_entity VARCHAR2) IS
1666 SELECT count(1) FROM
1667 (SELECT distinct f.staged_attribute_column
1668 FROM HZ_TRANS_FUNCTIONS_VL f, HZ_TRANS_ATTRIBUTES_VL a
1669 WHERE
1670 -- PRIMARY_FLAG = 'Y' --5044716
1671 nvl(f.ACTIVE_FLAG,'Y') = 'Y'
1675
1672 and f.ATTRIBUTE_ID = a.ATTRIBUTE_ID
1673 AND a.entity_name = p_entity
1674 );
1676 l_num_sections NUMBER;
1677
1678 uphase VARCHAR2(255);
1679 dphase VARCHAR2(255);
1680 ustatus VARCHAR2(255);
1681 dstatus VARCHAR2(255);
1682 l_index_owner VARCHAR2(255);
1683 message VARCHAR2(32000);
1684
1685 l_bool BOOLEAN;
1686
1687 l_num_parts NUMBER;
1688 l_num_jobs NUMBER;
1689 l_num_prll NUMBER;
1690
1691 l_start_flag VARCHAR2(30);
1692 l_end_flag VARCHAR2(30);
1693 l_command VARCHAR2(255);
1694
1695 l_section_grp VARCHAR2(255);
1696 l_min_id number;
1697 l_max_id number;
1698 tmp number;
1699
1700
1701 CREATE_PARTY_INDEXES BOOLEAN := FALSE ;
1702 CREATE_PS_INDEXES BOOLEAN := FALSE ;
1703 CREATE_CONTACT_INDEXES BOOLEAN := FALSE ;
1704 CREATE_CPT_INDEXES BOOLEAN := FALSE ;
1705
1706
1707 BEGIN
1708
1709 retcode := 0;
1710 l_command := p_command;
1711
1712 log ('--------------------------------------');
1713 outandlog('Starting Concurrent Program ''Create DQM indexes''');
1714 outandlog('Start Time ' || TO_CHAR(SYSDATE, 'MM-DD-YY HH24:MI:SS'));
1715 outandlog('Waiting for workers to complete');
1716
1717
1718 log('Data Workers have completed successfully');
1719
1720 l_bool := fnd_installation.GET_APP_INFO('AR',ustatus,dstatus,l_index_owner);
1721
1722 log('About to create index');
1723 log('Index owner is ' || l_index_owner);
1724 log('Schema Name is ' || g_schema_name );
1725 log('p_idx_mem is '|| p_idx_mem);
1726 -- Determine what needs to be done
1727
1728 IF p_index = 'ALL'
1729 THEN
1730 CREATE_PARTY_INDEXES := TRUE ;
1731 CREATE_PS_INDEXES := TRUE ;
1732 CREATE_CONTACT_INDEXES := TRUE ;
1733 CREATE_CPT_INDEXES := TRUE ;
1734 ELSIF p_index = 'HZ_PARTIES'
1735 THEN
1736 CREATE_PARTY_INDEXES := TRUE ;
1737 log('Creating Party Indexes');
1738 ELSIF p_index = 'HZ_PARTY_SITES'
1739 THEN
1740 CREATE_PS_INDEXES := TRUE ;
1741 log('Creating party sites Indexes');
1742 ELSIF p_index = 'HZ_ORG_CONTACTS'
1743 THEN
1744 CREATE_CONTACT_INDEXES := TRUE ;
1745 log('Creating Contact Indexes');
1746 ELSIF p_index = 'HZ_CONTACT_POINTS'
1747 THEN
1748 CREATE_CPT_INDEXES := TRUE ;
1749 log('Creating Contact Point Indexes');
1750 END IF ;
1751
1752 -- PARTY INDEX CREATION
1753 IF CREATE_PARTY_INDEXES THEN
1754 create_btree_indexes ('PARTY');
1755
1756 BEGIN
1757 SELECT start_flag, end_flag INTO l_start_flag, l_end_flag
1758 FROM HZ_DQM_STAGE_LOG
1759 WHERE OPERATION = 'SHADOW_CREATE_INDEXES'
1760 AND step = 'HZ_PARTIES';
1761 EXCEPTION
1762 WHEN no_data_found THEN
1763 l_start_flag:=NULL;
1764 l_end_flag:=NULL;
1765 END;
1766 log(' start flag = '||l_start_flag||' end flag = '||l_end_flag);
1767 -- Continue From Previous Run of Staging
1768 IF nvl(l_end_flag,'N') = 'N' THEN
1769 BEGIN
1770 execute immediate 'begin ctx_output.start_log(''party_index''); end;';
1771 EXCEPTION
1772 WHEN OTHERS THEN
1773 NULL;
1774 END;
1775
1776 IF nvl(l_start_flag,'N') = 'Y' THEN
1777 BEGIN
1778 log('Attempting restart build of index '||l_index_owner || '.hz_thin_st_parties_t1');
1779 EXECUTE IMMEDIATE 'ALTER INDEX ' || l_index_owner ||
1780 '.hz_thin_st_parties_t1 rebuild parameters(''resume memory ' || p_idx_mem || ''')';
1781 log('Index Successfully built');
1782
1783 UPDATE HZ_DQM_STAGE_LOG set END_FLAG = 'Y', END_TIME=SYSDATE
1784 WHERE operation = 'SHADOW_CREATE_INDEXES' AND step ='HZ_PARTIES';
1785 COMMIT;
1786
1787 EXCEPTION
1788 WHEN OTHERS THEN
1789 log('Restart Unsuccesful .. Recreating');
1790 BEGIN
1791 EXECUTE IMMEDIATE 'DROP INDEX ' || l_index_owner || '.hz_thin_st_parties_t1 FORCE';
1792 log('Dropped hz_thin_st_parties_t1');
1793 EXCEPTION
1794 WHEN OTHERS THEN
1795 NULL;
1796 END;
1797 l_start_flag := 'N';
1798 l_command := 'STAGE_ALL_DATA';
1799
1800 END;
1801 END IF;
1802
1803 -- Regular Flow, We do not Continue from a previous Run of Staging
1804 IF nvl(l_start_flag,'N') = 'N' THEN
1805 log('regular creation of party indexes');
1806 UPDATE HZ_DQM_STAGE_LOG set START_FLAG = 'Y', START_TIME=SYSDATE
1807 WHERE operation = 'SHADOW_CREATE_INDEXES' AND step ='HZ_PARTIES';
1808 COMMIT;
1809
1810 l_section_grp := g_schema_name || '.HZ_DQM_PARTY_GRP';
1811
1812 log(' Creating hz_thin_st_parties_t1 on hz_thin_st_parties .');
1813 log(' Index Memory ' || p_idx_mem);
1814 log(' Starting at ' || to_char(SYSDATE, 'HH24:MI:SS') );
1815
1816 -- commenting the sync part || ' sync (every "sysdate+(1/24)")'
1817 EXECUTE IMMEDIATE 'CREATE INDEX ' || l_index_owner || '.hz_thin_st_parties_t1 ON ' ||
1818 'hz_thin_st_parties(concat_col) indextype is ctxsys.context ' ||
1819 'parameters (''storage '||g_schema_name || '.HZ_DQM_STORAGE datastore '||g_schema_name || '.hz_party_uds ' ||
1823 UPDATE HZ_DQM_STAGE_LOG set END_FLAG = 'Y', END_TIME=SYSDATE
1820 'SECTION GROUP '||l_section_grp||' STOPLIST CTXSYS.EMPTY_STOPLIST LEXER '||g_schema_name || '.dqm_lexer memory ' ||
1821 p_idx_mem /* || ' sync (every "sysdate+(5/1440)")' */ || ''')';
1822
1824 WHERE operation = 'SHADOW_CREATE_INDEXES' AND step ='HZ_PARTIES';
1825 COMMIT;
1826 END IF;
1827
1828 log(' Completed at ' || to_char(SYSDATE, 'HH24:MI:SS'));
1829
1830 END IF;
1831 END IF;
1832
1833
1834 ---- PARTY SITE INDEX CREATION
1835 IF CREATE_PS_INDEXES THEN
1836 create_btree_indexes ('PARTY_SITES');
1837 BEGIN
1838 SELECT start_flag, end_flag INTO l_start_flag, l_end_flag
1839 FROM HZ_DQM_STAGE_LOG
1840 WHERE OPERATION = 'SHADOW_CREATE_INDEXES'
1841 AND step = 'HZ_PARTY_SITES';
1842 EXCEPTION
1843 WHEN no_data_found THEN
1844 l_start_flag:=NULL;
1845 l_end_flag:=NULL;
1846 END;
1847
1848 -- Continue From Previous Run of Staging
1849 IF nvl(l_end_flag,'N') = 'N' THEN
1850 BEGIN
1851 execute immediate 'begin ctx_output.start_log(''party_site_index''); end;';
1852 EXCEPTION
1853 WHEN OTHERS THEN
1854 NULL;
1855 END;
1856
1857 IF nvl(l_start_flag,'N') = 'Y' THEN
1858 BEGIN
1859 log('Attempting restart build of index '||l_index_owner || '.hz_thin_st_psites_t1');
1860 EXECUTE IMMEDIATE 'ALTER INDEX ' || l_index_owner ||
1861 '.hz_thin_st_psites_t1 rebuild parameters(''resume memory ' || p_idx_mem /*|| 'sync (every "sysdate+(1/24)")'*/ || ''')';
1862 log('Index Successfully built');
1863
1864 UPDATE HZ_DQM_STAGE_LOG set END_FLAG = 'Y', END_TIME=SYSDATE
1865 WHERE operation = 'SHADOW_CREATE_INDEXES' AND step ='HZ_PARTY_SITES';
1866 COMMIT;
1867
1868 EXCEPTION
1869 WHEN OTHERS THEN
1870 log('Restart Unsuccesful .. Recreating');
1871 BEGIN
1872 EXECUTE IMMEDIATE 'DROP INDEX ' || l_index_owner || '.hz_thin_st_psites_t1 FORCE';
1873 log('Dropped hz_thin_st_psites_t1');
1874 EXCEPTION
1875 WHEN OTHERS THEN
1876 NULL;
1877 END;
1878 l_start_flag := 'N';
1879 l_command := 'STAGE_ALL_DATA';
1880
1881 END;
1882 END IF;
1883
1884
1885
1886
1887 IF nvl(l_start_flag,'N') = 'N' THEN
1888 UPDATE HZ_DQM_STAGE_LOG set START_FLAG = 'Y', START_TIME=SYSDATE
1889 WHERE operation = 'SHADOW_CREATE_INDEXES' AND step ='HZ_PARTY_SITES';
1890 COMMIT;
1891
1892 l_section_grp := g_schema_name || '.HZ_DQM_PS_GRP';
1893
1894 log(' Creating hz_thin_st_psites_t1 on hz_thin_st_psites. ');
1895 log(' Index Memory ' || p_idx_mem);
1896 log(' Starting at ' || to_char(SYSDATE, 'HH24:MI:SS'));
1897
1898 -- commenting the sync part || ' sync (every "sysdate+(1/24)")'
1899 EXECUTE IMMEDIATE 'CREATE INDEX ' || l_index_owner || '.hz_thin_st_psites_t1 ON ' ||
1900 'hz_thin_st_psites(concat_col) indextype is ctxsys.context ' ||
1901 'parameters (''storage '||g_schema_name || '.HZ_DQM_STORAGE datastore '||g_schema_name || '.hz_party_site_uds ' ||
1902 'SECTION GROUP '||l_section_grp||' STOPLIST CTXSYS.EMPTY_STOPLIST LEXER '||g_schema_name || '.dqm_lexer memory ' ||
1903 p_idx_mem /* || ' sync (every "sysdate+(5/1440)")' */ || ''')';
1904
1905 UPDATE HZ_DQM_STAGE_LOG set END_FLAG = 'Y', END_TIME=SYSDATE
1906 WHERE operation = 'SHADOW_CREATE_INDEXES' AND step ='HZ_PARTY_SITES';
1907 COMMIT;
1908 END IF;
1909 log(' Completed at ' || to_char(SYSDATE, 'HH24:MI:SS'));
1910 END IF;
1911 END IF;
1912 log('');
1913
1914
1915 -- CONTACT INDEX CREATION
1916 IF CREATE_CONTACT_INDEXES THEN
1917 create_btree_indexes ('CONTACTS');
1918 BEGIN
1919 SELECT start_flag, end_flag INTO l_start_flag, l_end_flag
1920 FROM HZ_DQM_STAGE_LOG
1921 WHERE OPERATION = 'SHADOW_CREATE_INDEXES'
1922 AND step = 'HZ_ORG_CONTACTS';
1923 EXCEPTION
1924 WHEN no_data_found THEN
1925 l_start_flag:=NULL;
1926 l_end_flag:=NULL;
1927 END;
1928
1929 -- Continue From Previous Run of Staging
1930 IF nvl(l_end_flag,'N') = 'N' THEN
1931 BEGIN
1932 execute immediate 'begin ctx_output.start_log(''contact_index''); end;';
1933 EXCEPTION
1934 WHEN OTHERS THEN
1935 NULL;
1936 END;
1937
1938 IF nvl(l_start_flag,'N') = 'Y' THEN
1939 BEGIN
1940 log('Attempting restart build of index '||l_index_owner || '.hz_thin_st_contacts_t1');
1941 EXECUTE IMMEDIATE 'ALTER INDEX ' || l_index_owner ||
1942 '.hz_thin_st_contacts_t1 rebuild parameters(''resume memory ' || p_idx_mem || ''')';
1943 log('Index Successfully built');
1944
1945 UPDATE HZ_DQM_STAGE_LOG set END_FLAG = 'Y', END_TIME=SYSDATE
1946 WHERE operation = 'SHADOW_CREATE_INDEXES' AND step ='HZ_ORG_CONTACTS';
1947 COMMIT;
1948
1949 EXCEPTION
1950 WHEN OTHERS THEN
1951 log('Restart unsuccessful. Recreating Index');
1952 BEGIN
1953 EXECUTE IMMEDIATE 'DROP INDEX ' || l_index_owner || '.hz_thin_st_contacts_t1 FORCE';
1954 log('Dropped hz_thin_st_contacts_t1 ');
1955 EXCEPTION
1956 WHEN OTHERS THEN
1957 NULL;
1958 END;
1959 l_start_flag := 'N';
1960 l_command := 'STAGE_ALL_DATA';
1961
1962 END;
1963 END IF;
1964
1968 COMMIT;
1965 IF nvl(l_start_flag,'N') = 'N' THEN
1966 UPDATE HZ_DQM_STAGE_LOG set START_FLAG = 'Y', START_TIME=SYSDATE
1967 WHERE operation = 'SHADOW_CREATE_INDEXES' AND step ='HZ_ORG_CONTACTS';
1969
1970 l_section_grp := g_schema_name || '.HZ_DQM_CONTACT_GRP';
1971
1972
1973 log(' Creating hz_thin_st_contacts_t1 on hz_thin_st_contacts. ');
1974 log(' Index Memory ' || p_idx_mem);
1975 log(' Starting at ' || to_char(SYSDATE, 'HH24:MI:SS'));
1976
1977 -- commenting the sync part || 'sync (every "sysdate+(1/24)")'
1978 EXECUTE IMMEDIATE 'CREATE INDEX ' || l_index_owner || '.hz_thin_st_contacts_t1 ON ' ||
1979 'hz_thin_st_contacts(concat_col) indextype is ctxsys.context ' ||
1980 'parameters (''storage '||g_schema_name || '.HZ_DQM_STORAGE datastore '||g_schema_name || '.hz_contact_uds ' ||
1981 'SECTION GROUP '||l_section_grp||' STOPLIST CTXSYS.EMPTY_STOPLIST LEXER '||g_schema_name || '.dqm_lexer memory '
1982 || p_idx_mem /* || ' sync (every "sysdate+(5/1440)")' */ || ''')';
1983 UPDATE HZ_DQM_STAGE_LOG set END_FLAG = 'Y', END_TIME=SYSDATE
1984 WHERE operation = 'SHADOW_CREATE_INDEXES' AND step ='HZ_ORG_CONTACTS';
1985 COMMIT;
1986 END IF;
1987 log(' Completed at ' || to_char(SYSDATE, 'HH24:MI:SS'));
1988 END IF;
1989 END IF;
1990 log('');
1991
1992 -- CONTACT POINT INDEX CREATION
1993 IF CREATE_CPT_INDEXES THEN
1994 create_btree_indexes ('CONTACT_POINTS');
1995 BEGIN
1996 SELECT start_flag, end_flag INTO l_start_flag, l_end_flag
1997 FROM HZ_DQM_STAGE_LOG
1998 WHERE OPERATION = 'SHADOW_CREATE_INDEXES'
1999 AND step = 'HZ_CONTACT_POINTS';
2000 EXCEPTION
2001 WHEN no_data_found THEN
2002 l_start_flag:=NULL;
2003 l_end_flag:=NULL;
2004 END;
2005
2006 -- Continue From Previous Run of Staging
2007 IF nvl(l_end_flag,'N') = 'N' THEN
2008 BEGIN
2009 execute immediate 'begin ctx_output.start_log(''contact_point_index''); end;';
2010 EXCEPTION
2011 WHEN OTHERS THEN
2012 NULL;
2013 END;
2014
2015 IF nvl(l_start_flag,'N') = 'Y' THEN
2016 BEGIN
2017 log('Attempting restart build of index '||l_index_owner || '.hz_thin_st_cpts_t1');
2018 EXECUTE IMMEDIATE 'ALTER INDEX ' || l_index_owner ||
2019 '.hz_thin_st_cpts_t1 rebuild parameters(''resume memory ' || p_idx_mem /* || 'sync (every "sysdate+(1/24)")' */ || ''')';
2020
2021 log('Index Successfully built');
2022 UPDATE HZ_DQM_STAGE_LOG set END_FLAG = 'Y', END_TIME=SYSDATE
2023 WHERE operation = 'SHADOW_CREATE_INDEXES' AND step ='HZ_CONTACT_POINTS';
2024 COMMIT;
2025
2026 EXCEPTION
2027 WHEN OTHERS THEN
2028 log('Restart unsuccessful. Rebuilding index.');
2029 BEGIN
2030 EXECUTE IMMEDIATE 'DROP INDEX ' || l_index_owner || '.hz_thin_st_cpts_t1 FORCE';
2031 log('Dropped hz_thin_st_cpts_t1');
2032 EXCEPTION
2033 WHEN OTHERS THEN
2034 NULL;
2035 END;
2036 l_start_flag := 'N';
2037 l_command := 'STAGE_ALL_DATA';
2038
2039 END;
2040 END IF;
2041
2042 IF nvl(l_start_flag,'N') = 'N' THEN
2043 UPDATE HZ_DQM_STAGE_LOG set START_FLAG = 'Y', START_TIME=SYSDATE
2044 WHERE operation = 'SHADOW_CREATE_INDEXES' AND step ='HZ_CONTACT_POINTS';
2045 COMMIT;
2046
2047 l_section_grp := g_schema_name || '.HZ_DQM_CPT_GRP';
2048
2049
2050 log(' Creating hz_thin_st_cpts_t1 on hz_thin_st_cpts . ');
2051 log(' Index Memory ' || p_idx_mem);
2052 log(' Starting at ' || to_char(SYSDATE, 'HH24:MI:SS'));
2053 -- commenting the sync part || ' sync (every "sysdate+(1/24)")'
2054 EXECUTE IMMEDIATE 'CREATE INDEX ' || l_index_owner || '.hz_thin_st_cpts_t1 ON ' ||
2055 'hz_thin_st_cpts(concat_col) indextype is ctxsys.context ' ||
2056 'parameters (''storage '||g_schema_name || '.HZ_DQM_STORAGE datastore '||g_schema_name || '.hz_contact_point_uds ' ||
2057 'SECTION GROUP '||l_section_grp||' STOPLIST CTXSYS.EMPTY_STOPLIST LEXER '||g_schema_name || '.dqm_lexer memory '
2058 || p_idx_mem /* || ' sync (every "sysdate+(5/1440)")' */ || ''')';
2059
2060 UPDATE HZ_DQM_STAGE_LOG set END_FLAG = 'Y', END_TIME=SYSDATE
2061 WHERE operation = 'SHADOW_CREATE_INDEXES' AND step ='HZ_CONTACT_POINTS';
2062 COMMIT;
2063 END IF;
2064 log(' Completed at ' || to_char(SYSDATE, 'HH24:MI:SS'));
2065 END IF;
2066 END IF;
2067
2068 log('');
2069 log('Concurrent Program Execution completed ');
2070 log('End Time : '|| TO_CHAR(SYSDATE, 'MM-DD-YY HH24:MI:SS'));
2071
2072 EXCEPTION
2073 WHEN FND_API.G_EXC_ERROR THEN
2074 outandlog('Error: ' || FND_MESSAGE.GET);
2075 log('Error ' || SQLERRM);
2076 retcode := 2;
2077 errbuf := errbuf || logerror;
2078 outandlog('Aborting concurrent program execution');
2079 FND_FILE.close;
2080 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2081 outandlog('Error: ' || FND_MESSAGE.GET);
2082 log('Error ' || SQLERRM);
2083 retcode := 2;
2084 errbuf := errbuf || logerror;
2085 outandlog('Aborting concurrent program execution');
2086 FND_FILE.close;
2087 WHEN OTHERS THEN
2088 outandlog('Error: ' || FND_MESSAGE.GET);
2089 log('SQL Error ' || SQLERRM);
2090 retcode := 2;
2091 errbuf := errbuf || logerror || SQLERRM;
2092 outandlog('Aborting concurrent program execution');
2093 FND_FILE.close;
2094 END;
2095
2096 PROCEDURE generate_map_proc (
2097 p_entity IN VARCHAR2,
2098 p_proc_name IN VARCHAR2,
2102 FIRST BOOLEAN;
2099 p_command IN VARCHAR2
2100 ) IS
2101 l_update_str VARCHAR2(4000);
2103 BEGIN
2104
2105
2106 l('FUNCTION ' || p_proc_name || '( ');
2107 l(' p_record_id NUMBER,');
2108 IF p_entity = 'PARTY' THEN
2109 l(' p_search_rec HZ_PARTY_SEARCH.party_search_rec_type');
2110 l(' ) RETURN HZ_PARTY_STAGE.party_stage_rec_type IS ');
2111 l('l_stage_rec HZ_PARTY_STAGE.party_stage_rec_type;');
2112 l('BEGIN');
2113 l(' l_stage_rec.party_id := p_record_id;');
2114 l(' l_stage_rec.status := p_search_rec.STATUS;');
2115 ELSIF p_entity = 'PARTY_SITES' THEN
2116 l(' p_party_id NUMBER,');
2117 l(' p_search_rec HZ_PARTY_SEARCH.party_site_search_rec_type');
2118 l(' ) RETURN HZ_PARTY_STAGE.party_site_stage_rec_type IS ');
2119 l('l_stage_rec HZ_PARTY_STAGE.party_site_stage_rec_type;');
2120 l('BEGIN');
2121 l(' l_stage_rec.party_id := p_party_id;');
2122 l(' l_stage_rec.party_site_id := p_record_id;');
2123 ELSIF p_entity = 'CONTACTS' THEN
2124 l(' p_party_id NUMBER,');
2125 l(' p_search_rec HZ_PARTY_SEARCH.contact_search_rec_type');
2126 l(' ) RETURN HZ_PARTY_STAGE.contact_stage_rec_type IS ');
2127 l('l_stage_rec HZ_PARTY_STAGE.contact_stage_rec_type;');
2128 l('BEGIN');
2129 l(' l_stage_rec.party_id := p_party_id;');
2130 l(' l_stage_rec.org_contact_id := p_record_id;');
2131 ELSIF p_entity = 'CONTACT_POINTS' THEN
2132 l(' p_party_id NUMBER,');
2133 l(' p_search_rec HZ_PARTY_SEARCH.contact_point_search_rec_type');
2134 l(' ) RETURN HZ_PARTY_STAGE.contact_pt_stage_rec_type IS ');
2135 l('l_stage_rec HZ_PARTY_STAGE.contact_pt_stage_rec_type;');
2136 l('BEGIN');
2137 l(' l_stage_rec.party_id := p_party_id;');
2138 l(' l_stage_rec.contact_point_id := p_record_id;');
2139 l(' l_stage_rec.contact_point_type := p_search_rec.CONTACT_POINT_TYPE;');
2140 END IF;
2141
2142 IF p_command = 'STAGE_NEW_TRANSFORMATIONS' THEN
2143 FIRST := TRUE;
2144 l_update_str := null;
2145
2146 for ATTRS IN (SELECT ATTRIBUTE_ID, ATTRIBUTE_NAME
2147 FROM HZ_TRANS_ATTRIBUTES_VL
2148 WHERE ENTITY_NAME = p_entity)
2149
2150 LOOP
2151 for FUNCS IN (SELECT PROCEDURE_NAME, STAGED_ATTRIBUTE_COLUMN
2152 FROM HZ_TRANS_FUNCTIONS_VL
2153 WHERE ATTRIBUTE_ID = ATTRS.ATTRIBUTE_ID
2154 AND nvl(ACTIVE_FLAG,'Y') = 'Y'
2155 AND NVL(STAGED_FLAG,'N') <> 'Y')
2156 LOOP
2157 l(' l_stage_rec.'||FUNCS.STAGED_ATTRIBUTE_COLUMN || ' := ');
2158 l(' ' || FUNCS.PROCEDURE_NAME ||'(');
2159 l(' p_search_rec.'||ATTRS.ATTRIBUTE_NAME);
2160 l(' ,null,''' || ATTRS.ATTRIBUTE_NAME || '''');
2161 l(' ,''' ||p_entity||''');');
2162 IF FIRST THEN
2163 l_update_str := ' '|| FUNCS.STAGED_ATTRIBUTE_COLUMN || ' = ' ||
2164 ' l_stage_rec.'||FUNCS.STAGED_ATTRIBUTE_COLUMN || ' ';
2165 FIRST := FALSE;
2166 ELSE
2167 l_update_str := l_update_str || ','|| FUNCS.STAGED_ATTRIBUTE_COLUMN || ' = ' ||
2168 ' l_stage_rec.'||FUNCS.STAGED_ATTRIBUTE_COLUMN || ' ';
2169 END IF;
2170 END LOOP;
2171 END LOOP;
2172
2173 IF l_update_str IS NOT NULL THEN
2174 IF p_entity = 'PARTY' THEN
2175 l(' UPDATE HZ_SHADOW_ST_PARTIES SET ');
2176 l(' ' || l_update_str);
2177 l(' WHERE party_id = p_record_id;');
2178 ELSIF p_entity = 'PARTY_SITES' THEN
2179 l(' UPDATE HZ_SHADOW_ST_PSITES SET ');
2180 l(' ' || l_update_str);
2181 l(' WHERE party_site_id = p_record_id;');
2182 ELSIF p_entity = 'CONTACTS' THEN
2183 l(' UPDATE HZ_SHADOW_ST_CONTACTS SET ');
2184 l(' ' || l_update_str);
2185 l(' WHERE org_contact_id = p_record_id;');
2186 ELSIF p_entity = 'CONTACT_POINTS' THEN
2187 l(' UPDATE HZ_SHADOW_ST_CPTS SET ');
2188 l(' ' || l_update_str);
2189 l(' WHERE contact_point_id = p_record_id;');
2190 END IF;
2191 END IF;
2192
2193 ELSE
2194
2195 for ATTRS IN (SELECT ATTRIBUTE_ID, ATTRIBUTE_NAME
2196 FROM HZ_TRANS_ATTRIBUTES_VL
2197 WHERE ENTITY_NAME = p_entity)
2198 LOOP
2199 for FUNCS IN (SELECT PROCEDURE_NAME, STAGED_ATTRIBUTE_COLUMN
2200 FROM HZ_TRANS_FUNCTIONS_VL
2201 WHERE ATTRIBUTE_ID = ATTRS.ATTRIBUTE_ID
2202 AND nvl(ACTIVE_FLAG,'Y') = 'Y')
2203 LOOP
2204 l(' l_stage_rec.'||FUNCS.STAGED_ATTRIBUTE_COLUMN || ' := ');
2205 l(' ' || FUNCS.PROCEDURE_NAME ||'(');
2206 l(' p_search_rec.'||ATTRS.ATTRIBUTE_NAME);
2207
2208 -- Temporary fix for bug 2265498
2209 -- Will be fixed when bug 2269873
2210 l(' ,null,''' || ATTRS.ATTRIBUTE_NAME || '''');
2211 l(' ,''' ||p_entity||''');');
2212 END LOOP;
2213 END LOOP;
2214 END IF;
2215
2216 l(' RETURN l_stage_rec;');
2217 l('EXCEPTION');
2218 l(' WHEN OTHERS THEN');
2219 l(' FND_MESSAGE.SET_NAME(''AR'', ''HZ_MAP_PROC_ERROR'');');
2220 l(' FND_MESSAGE.SET_TOKEN(''PROC'' ,''' || p_proc_name||''');');
2221 l(' FND_MESSAGE.SET_TOKEN(''ERROR'' ,SQLERRM);');
2222 l(' FND_MSG_PUB.ADD;');
2223 l(' RAISE FND_API.G_EXC_UNEXPECTED_ERROR;');
2224
2225 l('END;');
2226
2227 EXCEPTION
2228 WHEN OTHERS THEN
2229 FND_MESSAGE.SET_NAME('AR', 'HZ_STAGE_SQL_ERROR');
2230 FND_MESSAGE.SET_TOKEN('PROC' ,'generate_transform_proc');
2231 FND_MESSAGE.SET_TOKEN('ERROR' ,SQLERRM);
2232 FND_MSG_PUB.ADD;
2236 PROCEDURE generate_declarations IS
2233 RAISE FND_API.G_EXC_ERROR;
2234 END;
2235
2237
2238 BEGIN
2239 l(' TYPE NumberList IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;');
2240 l(' TYPE Char1List IS TABLE OF VARCHAR2(1) INDEX BY BINARY_INTEGER;');
2241 l(' TYPE Char2List IS TABLE OF VARCHAR2(30) INDEX BY BINARY_INTEGER;');
2242 l(' TYPE CharList IS TABLE OF VARCHAR2(2000) INDEX BY BINARY_INTEGER;');
2243 l(' ');
2244 l(' H_PERSON_PARTY_ID NumberList;');
2245 l(' H_P_PARTY_ID NumberList;');
2246 l(' H_PS_DEN CharList;');
2247 l(' H_CT_DEN CharList;');
2248 l(' H_CPT_DEN CharList;');
2249 l(' H_PARTY_INDEX NumberList;');
2250 l(' H_PARTY_ID NumberList;');
2251 l(' H_C_PARTY_ID NumberList;');
2252 l(' H_PS_PARTY_ID NumberList;');
2253 l(' H_CPT_PARTY_ID NumberList;');
2254 l(' H_R_PARTY_ID NumberList;');
2255 l(' H_STATUS Char1List;');
2256 l(' H_PARTY_SITE_ID NumberList;');
2257 l(' H_CPT_PARTY_SITE_ID NumberList;');
2258 l(' H_ORG_CONTACT_ID NumberList;');
2259 l(' H_PS_ORG_CONTACT_ID NumberList;');
2260 l(' H_CPT_ORG_CONTACT_ID NumberList;');
2261 l(' H_CONTACT_POINT_ID NumberList;');
2262 l(' H_CONTACT_POINT_TYPE Char2List;');
2263 l(' H_TX1 CharList;');
2264 l(' H_TX2 CharList;');
2265 l(' H_TX3 CharList;');
2266 l(' H_TX4 CharList;');
2267 l(' H_TX5 CharList;');
2268 l(' H_TX6 CharList;');
2269 l(' H_TX7 CharList;');
2270 l(' H_TX8 CharList;');
2271 l(' H_TX9 CharList;');
2272 l(' H_TX10 CharList;');
2273 l(' H_TX11 CharList;');
2274 l(' H_TX12 CharList;');
2275 l(' H_TX13 CharList;');
2276 l(' H_TX14 CharList;');
2277 l(' H_TX15 CharList;');
2278 l(' H_TX16 CharList;');
2279 l(' H_TX17 CharList;');
2280 l(' H_TX18 CharList;');
2281 l(' H_TX19 CharList;');
2282 l(' H_TX20 CharList;');
2283 l(' H_TX21 CharList;');
2284 l(' H_TX22 CharList;');
2285 l(' H_TX23 CharList;');
2286 l(' H_TX24 CharList;');
2287 l(' H_TX25 CharList;');
2288 l(' H_TX26 CharList;');
2289 l(' H_TX27 CharList;');
2290 l(' H_TX28 CharList;');
2291 l(' H_TX29 CharList;');
2292 l(' H_TX30 CharList;');
2293 l(' H_TX31 CharList;');
2294 l(' H_TX32 CharList;');
2295 l(' H_TX33 CharList;');
2296 l(' H_TX34 CharList;');
2297 l(' H_TX35 CharList;');
2298 l(' H_TX36 CharList;');
2299 l(' H_TX37 CharList;');
2300 l(' H_TX38 CharList;');
2301 l(' H_TX39 CharList;');
2302 l(' H_TX40 CharList;');
2303 l(' H_TX41 CharList;');
2304 l(' H_TX42 CharList;');
2305 l(' H_TX43 CharList;');
2306 l(' H_TX44 CharList;');
2307 l(' H_TX45 CharList;');
2308 l(' H_TX46 CharList;');
2309 l(' H_TX47 CharList;');
2310 l(' H_TX48 CharList;');
2311 l(' H_TX49 CharList;');
2312 l(' H_TX50 CharList;');
2313 l(' H_TX51 CharList;');
2314 l(' H_TX52 CharList;');
2315 l(' H_TX53 CharList;');
2316 l(' H_TX54 CharList;');
2317 l(' H_TX55 CharList;');
2318 l(' H_TX56 CharList;');
2319 l(' H_TX57 CharList;');
2320 l(' H_TX58 CharList;');
2321 l(' H_TX59 CharList;');
2322 l(' H_TX60 CharList;');
2323 l(' H_TX61 CharList;');
2324 l(' H_TX62 CharList;');
2325 l(' H_TX63 CharList;');
2326 l(' H_TX64 CharList;');
2327 l(' H_TX65 CharList;');
2328 l(' H_TX66 CharList;');
2329 l(' H_TX67 CharList;');
2330 l(' H_TX68 CharList;');
2331 l(' H_TX69 CharList;');
2332 l(' H_TX70 CharList;');
2333 l(' H_TX71 CharList;');
2334 l(' H_TX72 CharList;');
2335 l(' H_TX73 CharList;');
2336 l(' H_TX74 CharList;');
2337 l(' H_TX75 CharList;');
2338 l(' H_TX76 CharList;');
2339 l(' H_TX77 CharList;');
2340 l(' H_TX78 CharList;');
2341 l(' H_TX79 CharList;');
2342 l(' H_TX80 CharList;');
2343 l(' H_TX81 CharList;');
2344 l(' H_TX82 CharList;');
2345 l(' H_TX83 CharList;');
2346 l(' H_TX84 CharList;');
2347 l(' H_TX85 CharList;');
2348 l(' H_TX86 CharList;');
2349 l(' H_TX87 CharList;');
2350 l(' H_TX88 CharList;');
2351 l(' H_TX89 CharList;');
2352 l(' H_TX90 CharList;');
2353 l(' H_TX91 CharList;');
2354 l(' H_TX92 CharList;');
2355 l(' H_TX93 CharList;');
2356 l(' H_TX94 CharList;');
2357 l(' H_TX95 CharList;');
2358 l(' H_TX96 CharList;');
2359 l(' H_TX97 CharList;');
2360 l(' H_TX98 CharList;');
2361 l(' H_TX99 CharList;');
2362 l(' H_TX100 CharList;');
2363 l(' H_TX101 CharList;');
2364 l(' H_TX102 CharList;');
2365 l(' H_TX103 CharList;');
2366 l(' H_TX104 CharList;');
2367 l(' H_TX105 CharList;');
2368 l(' H_TX106 CharList;');
2369 l(' H_TX107 CharList;');
2370 l(' H_TX108 CharList;');
2371 l(' H_TX109 CharList;');
2372 l(' H_TX110 CharList;');
2373 l(' H_TX111 CharList;');
2374 l(' H_TX112 CharList;');
2375 l(' H_TX113 CharList;');
2376 l(' H_TX114 CharList;');
2377 l(' H_TX115 CharList;');
2378 l(' H_TX116 CharList;');
2379 l(' H_TX117 CharList;');
2380 l(' H_TX118 CharList;');
2381 l(' H_TX119 CharList;');
2382 l(' H_TX120 CharList;');
2383 l(' H_TX121 CharList;');
2384 l(' H_TX122 CharList;');
2385 l(' H_TX123 CharList;');
2386 l(' H_TX124 CharList;');
2387 l(' H_TX125 CharList;');
2388 l(' H_TX126 CharList;');
2389 l(' H_TX127 CharList;');
2390 l(' H_TX128 CharList;');
2391 l(' H_TX129 CharList;');
2392 l(' H_TX130 CharList;');
2393 l(' H_TX131 CharList;');
2394 l(' H_TX132 CharList;');
2395 l(' H_TX133 CharList;');
2396 l(' H_TX134 CharList;');
2397 l(' H_TX135 CharList;');
2398 l(' H_TX136 CharList;');
2402 l(' H_TX140 CharList;');
2399 l(' H_TX137 CharList;');
2400 l(' H_TX138 CharList;');
2401 l(' H_TX139 CharList;');
2403 l(' H_TX141 CharList;');
2404 l(' H_TX142 CharList;');
2405 l(' H_TX143 CharList;');
2406 l(' H_TX144 CharList;');
2407 l(' H_TX145 CharList;');
2408 l(' H_TX146 CharList;');
2409 l(' H_TX147 CharList;');
2410 l(' H_TX148 CharList;');
2411 l(' H_TX149 CharList;');
2412 l(' H_TX150 CharList;');
2413 l(' H_TX151 CharList;');
2414 l(' H_TX152 CharList;');
2415 l(' H_TX153 CharList;');
2416 l(' H_TX154 CharList;');
2417 l(' H_TX155 CharList;');
2418 l(' H_TX156 CharList;');
2419 l(' H_TX157 CharList;');
2420 l(' H_TX158 CharList;');
2421 l(' H_TX159 CharList;');
2422 l(' H_TX160 CharList;');
2423 l(' H_TX161 CharList;');
2424 l(' H_TX162 CharList;');
2425 l(' H_TX163 CharList;');
2426 l(' H_TX164 CharList;');
2427 l(' H_TX165 CharList;');
2428 l(' H_TX166 CharList;');
2429 l(' H_TX167 CharList;');
2430 l(' H_TX168 CharList;');
2431 l(' H_TX169 CharList;');
2432 l(' H_TX170 CharList;');
2433 l(' H_TX171 CharList;');
2434 l(' H_TX172 CharList;');
2435 l(' H_TX173 CharList;');
2436 l(' H_TX174 CharList;');
2437 l(' H_TX175 CharList;');
2438 l(' H_TX176 CharList;');
2439 l(' H_TX177 CharList;');
2440 l(' H_TX178 CharList;');
2441 l(' H_TX179 CharList;');
2442 l(' H_TX180 CharList;');
2443 l(' H_TX181 CharList;');
2444 l(' H_TX182 CharList;');
2445 l(' H_TX183 CharList;');
2446 l(' H_TX184 CharList;');
2447 l(' H_TX185 CharList;');
2448 l(' H_TX186 CharList;');
2449 l(' H_TX187 CharList;');
2450 l(' H_TX188 CharList;');
2451 l(' H_TX189 CharList;');
2452 l(' H_TX190 CharList;');
2453 l(' H_TX191 CharList;');
2454 l(' H_TX192 CharList;');
2455 l(' H_TX193 CharList;');
2456 l(' H_TX194 CharList;');
2457 l(' H_TX195 CharList;');
2458 l(' H_TX196 CharList;');
2459 l(' H_TX197 CharList;');
2460 l(' H_TX198 CharList;');
2461 l(' H_TX199 CharList;');
2462 l(' H_TX200 CharList;');
2463 l(' H_TX201 CharList;');
2464 l(' H_TX202 CharList;');
2465 l(' H_TX203 CharList;');
2466 l(' H_TX204 CharList;');
2467 l(' H_TX205 CharList;');
2468 l(' H_TX206 CharList;');
2469 l(' H_TX207 CharList;');
2470 l(' H_TX208 CharList;');
2471 l(' H_TX209 CharList;');
2472 l(' H_TX210 CharList;');
2473 l(' H_TX211 CharList;');
2474 l(' H_TX212 CharList;');
2475 l(' H_TX213 CharList;');
2476 l(' H_TX214 CharList;');
2477 l(' H_TX215 CharList;');
2478 l(' H_TX216 CharList;');
2479 l(' H_TX217 CharList;');
2480 l(' H_TX218 CharList;');
2481 l(' H_TX219 CharList;');
2482 l(' H_TX220 CharList;');
2483 l(' H_TX221 CharList;');
2484 l(' H_TX222 CharList;');
2485 l(' H_TX223 CharList;');
2486 l(' H_TX224 CharList;');
2487 l(' H_TX225 CharList;');
2488 l(' H_TX226 CharList;');
2489 l(' H_TX227 CharList;');
2490 l(' H_TX228 CharList;');
2491 l(' H_TX229 CharList;');
2492 l(' H_TX230 CharList;');
2493 l(' H_TX231 CharList;');
2494 l(' H_TX232 CharList;');
2495 l(' H_TX233 CharList;');
2496 l(' H_TX234 CharList;');
2497 l(' H_TX235 CharList;');
2498 l(' H_TX236 CharList;');
2499 l(' H_TX237 CharList;');
2500 l(' H_TX238 CharList;');
2501 l(' H_TX239 CharList;');
2502 l(' H_TX240 CharList;');
2503 l(' H_TX241 CharList;');
2504 l(' H_TX242 CharList;');
2505 l(' H_TX243 CharList;');
2506 l(' H_TX244 CharList;');
2507 l(' H_TX245 CharList;');
2508 l(' H_TX246 CharList;');
2509 l(' H_TX247 CharList;');
2510 l(' H_TX248 CharList;');
2511 l(' H_TX249 CharList;');
2512 l(' H_TX250 CharList;');
2513 l(' H_TX251 CharList;');
2514 l(' H_TX252 CharList;');
2515 l(' H_TX253 CharList;');
2516 l(' H_TX254 CharList;');
2517 l(' H_TX255 CharList;');
2518 END;
2519
2520
2521 PROCEDURE generate_party_query_proc IS
2522 cur_col_num NUMBER := 1;
2523
2524 TYPE coltab IS TABLE OF VARCHAR2(2000) INDEX BY BINARY_INTEGER;
2525
2526 l_org_select coltab;
2527 l_per_select coltab;
2528 l_oth_select coltab;
2529
2530 l_forall_list coltab;
2531 l_custom_list coltab;
2532 l_mincol_list coltab;
2533 l_min_colnum NUMBER;
2534 idx NUMBER :=1;
2535 is_first boolean := true;
2536
2537 -- VJN Introduced for conditional word replacements
2538 l_cond_attrib_list coltab ;
2539 l_idx number ;
2540 l_attr_name varchar2(2000); --Bug No: 4954701
2541 l_org_attr_name varchar2(2000); --Bug No: 4954701
2542 l_per_attr_name varchar2(2000); --Bug No: 4954701
2543 BEGIN
2544 l(' PROCEDURE open_party_cursor( ');
2545 l(' p_select_type IN VARCHAR2,');
2546 l(' p_party_type IN VARCHAR2,');
2547 l(' p_worker_number IN NUMBER,');
2548 l(' p_num_workers IN NUMBER,');
2549 l(' p_party_id IN NUMBER,');
2550 l(' p_continue IN VARCHAR2,');
2551 l(' x_party_cur IN OUT HZ_PARTY_STAGE.StageCurTyp) IS ');
2552 l('');
2553 l(' l_party_type VARCHAR2(255);');
2554 l(' BEGIN');
2555
2556 FOR ATTRS IN (SELECT a.ATTRIBUTE_ID,
2557 a.ATTRIBUTE_NAME,
2558 a.SOURCE_TABLE,
2559 a.CUSTOM_ATTRIBUTE_PROCEDURE,
2560 f.PROCEDURE_NAME,
2561 f.STAGED_ATTRIBUTE_COLUMN,
2565 WHERE ENTITY_NAME = 'PARTY'
2562 to_number(substrb(STAGED_ATTRIBUTE_COLUMN, 3)) COLNUM,
2563 nvl(TAG,'C') column_data_type --Bug No: 4954701
2564 FROM HZ_TRANS_ATTRIBUTES_VL a, HZ_TRANS_FUNCTIONS_VL f,FND_LOOKUP_VALUES_VL lkp --Bug No: 4954701
2566 AND nvl(f.ACTIVE_FLAG,'Y') = 'Y'
2567 AND f.ATTRIBUTE_ID = a.ATTRIBUTE_ID
2568 AND lkp.LOOKUP_TYPE = 'PARTY_LOGICAL_ATTRIBUTE_LIST' --Bug No: 4954701
2569 AND lkp.LOOKUP_CODE = a.ATTRIBUTE_NAME --Bug No: 4954701
2570 ORDER BY COLNUM) LOOP
2571
2572 IF cur_col_num<ATTRS.COLNUM THEN
2573 FOR I in cur_col_num..ATTRS.COLNUM-1 LOOP
2574 l_mincol_list(I) := 'N';
2575 l_forall_list(I) := 'N';
2576 l_custom_list(I) := 'N';
2577 END LOOP;
2578 END IF;
2579 cur_col_num:=ATTRS.COLNUM+1;
2580 IF has_trx_context(ATTRS.PROCEDURE_NAME) THEN
2581 l_forall_list(ATTRS.COLNUM) := ATTRS.PROCEDURE_NAME || '(H_TX'||ATTRS.COLNUM||'(I),NULL, ''' || ATTRS.ATTRIBUTE_NAME || ''',''PARTY'',''STAGE'')';
2582 ELSE
2583 l_forall_list(ATTRS.COLNUM) := ATTRS.PROCEDURE_NAME || '(H_TX'||ATTRS.COLNUM||'(I),NULL, ''' || ATTRS.ATTRIBUTE_NAME || ''',''PARTY'')';
2584 END IF;
2585 l_mincol_list(ATTRS.COLNUM) := 'N';
2586
2587 SELECT min(to_number(substrb(STAGED_ATTRIBUTE_COLUMN, 3))) INTO l_min_colnum
2588 FROM HZ_TRANS_ATTRIBUTES_VL a, HZ_TRANS_FUNCTIONS_VL f
2589 WHERE ENTITY_NAME = 'PARTY'
2590 AND nvl(f.ACTIVE_FLAG,'Y') = 'Y'
2591 AND f.ATTRIBUTE_ID = a.ATTRIBUTE_ID
2592 AND a.ATTRIBUTE_NAME=ATTRS.ATTRIBUTE_NAME;
2593
2594 IF ATTRS.colnum>l_min_colnum THEN
2595 l_mincol_list(ATTRS.COLNUM) := 'N';
2596 IF has_trx_context(ATTRS.PROCEDURE_NAME) THEN
2597 l_forall_list(ATTRS.COLNUM) := ATTRS.PROCEDURE_NAME || '(H_TX'||l_min_colnum||'(I),NULL, ''' || ATTRS.ATTRIBUTE_NAME || ''',''PARTY'',''STAGE'')';
2598 ELSE
2599 l_forall_list(ATTRS.COLNUM) := ATTRS.PROCEDURE_NAME || '(H_TX'||l_min_colnum||'(I),NULL, ''' || ATTRS.ATTRIBUTE_NAME || ''',''PARTY'')';
2600 END IF;
2601 ELSE
2602 l_mincol_list(ATTRS.COLNUM) := 'Y';
2603 END IF;
2604
2605 IF ATTRS.CUSTOM_ATTRIBUTE_PROCEDURE IS NULL THEN
2606 IF l_mincol_list(ATTRS.COLNUM) = 'Y' THEN
2607 -----Start of Bug No: 4954701----------
2608 l_attr_name := ATTRS.ATTRIBUTE_NAME;
2609 IF(ATTRS.column_data_type ='D') THEN
2610 l_org_attr_name := 'TO_CHAR(op.'||l_attr_name||',''DD-MON-YYYY'') '||l_attr_name;
2611 l_per_attr_name := 'TO_CHAR(pe.'||l_attr_name||',''DD-MON-YYYY'') '||l_attr_name;
2612 l_attr_name := 'TO_CHAR(p.'||l_attr_name||',''DD-MON-YYYY'') '||l_attr_name;
2613 ELSE
2614 l_org_attr_name := 'op.'||l_attr_name;
2615 l_per_attr_name := 'pe.'||l_attr_name;
2616 l_attr_name := 'p.'||l_attr_name;
2617 END IF;
2618 -----End of Bug No: 4954701------------
2619 IF ATTRS.SOURCE_TABLE='HZ_ORGANIZATION_PROFILES' THEN
2620 l_org_select(idx) := l_org_attr_name;
2621 l_per_select(idx) := 'NULL';
2622 l_oth_select(idx) := 'NULL';
2623 ELSIF ATTRS.SOURCE_TABLE='HZ_PERSON_PROFILES' THEN
2624 l_per_select(idx) := l_per_attr_name;
2625 l_org_select(idx) := 'NULL';
2626 l_oth_select(idx) := 'NULL';
2627 ELSIF ATTRS.SOURCE_TABLE='HZ_PERSON_PROFILES, HZ_ORGANIZATION_PROFILES' OR
2628 ATTRS.SOURCE_TABLE='HZ_ORGANIZATION_PROFILES, HZ_PERSON_PROFILES' THEN
2629 l_org_select(idx) := l_org_attr_name;
2630 l_per_select(idx) := l_per_attr_name;
2631 l_oth_select(idx) := 'NULL';
2632 ELSE
2633 l_org_select(idx) := l_attr_name;
2634 l_per_select(idx) := l_attr_name;
2635 l_oth_select(idx) := l_attr_name;
2636 END IF;
2637 ELSE
2638 l_org_select(idx):='N';
2639 l_per_select(idx):='N';
2640 l_oth_select(idx):='N';
2641 END IF;
2642
2643 l_custom_list(ATTRS.COLNUM) := 'N';
2644 ELSE
2645 l_org_select(idx):='N';
2646 l_per_select(idx):='N';
2647 l_oth_select(idx):='N';
2648 l_custom_list(ATTRS.COLNUM) := 'N';
2649 IF ATTRS.ATTRIBUTE_NAME = 'PARTY_ALL_NAMES' THEN
2650 IF l_mincol_list(ATTRS.COLNUM) = 'Y' THEN
2651 l_org_select(idx) := 'p.PARTY_NAME || '' '' || p.KNOWN_AS || '' '' || p.KNOWN_AS2 || '' '' || p.KNOWN_AS3 || '' ''|| p.KNOWN_AS4 || '' ''|| p.KNOWN_AS5';
2652 l_per_select(idx) := 'p.PARTY_NAME || '' '' || p.KNOWN_AS || '' '' || p.KNOWN_AS2 || '' '' || p.KNOWN_AS3 || '' ''|| p.KNOWN_AS4 || '' ''|| p.KNOWN_AS5';
2653 l_oth_select(idx) := 'p.PARTY_NAME || '' '' || p.KNOWN_AS || '' '' || p.KNOWN_AS2 || '' '' || p.KNOWN_AS3 || '' ''|| p.KNOWN_AS4 || '' ''|| p.KNOWN_AS5';
2654 END IF;
2655 ELSE
2656 IF l_mincol_list(ATTRS.COLNUM) = 'Y' THEN
2657 IF has_context(ATTRS.custom_attribute_procedure) THEN
2658 l_custom_list(ATTRS.COLNUM) := ATTRS.custom_attribute_procedure || '(H_P_PARTY_ID(I),''PARTY'','''||ATTRS.ATTRIBUTE_NAME|| ''', ''STAGE'')';
2659 ELSE
2660 l_custom_list(ATTRS.COLNUM) := ATTRS.custom_attribute_procedure || '(H_P_PARTY_ID(I),''PARTY'','''||ATTRS.ATTRIBUTE_NAME|| ''')';
2661 END IF;
2662 END IF;
2663 END IF;
2664 END IF;
2665 idx := idx+1;
2666
2667 -- VJN ADDED CODE FOR TRACKING CONDITION ATTRIBUTES AT THIS ENTITY LEVEL
2668 IF HZ_WORD_CONDITIONS_PKG.is_a_cond_attrib( ATTRS.attribute_id)
2669 THEN
2670 l_cond_attrib_list(ATTRS.COLNUM) := ATTRS.attribute_id ;
2671 END IF;
2672
2673
2674 END LOOP;
2675
2676 IF cur_col_num<255 THEN
2677 FOR I in cur_col_num..255 LOOP
2678 l_mincol_list(I) := 'N';
2679 l_forall_list(I) := 'N';
2680 l_custom_list(I) := 'N';
2681 END LOOP;
2682 END IF;
2683
2687 l(' IF p_continue IS NULL OR p_continue<>''Y'' THEN');
2684 l(' IF p_select_type = ''SINGLE_PARTY'' THEN');
2685 l(' NULL;');
2686 l(' ELSIF p_select_type = ''ALL_PARTIES'' THEN');
2688 l(' IF p_party_type = ''ORGANIZATION'' THEN');
2689 l(' open x_party_cur FOR ' );
2690 l(' SELECT p.PARTY_ID, p.STATUS, NULL AS PERSON_PARTY_ID '); -- Fix for bug 5155761
2691 FOR I in 1..l_org_select.COUNT LOOP
2692 IF l_org_select(I) <> 'N' THEN
2693 l(' ,' || l_org_select(I));
2694 END IF;
2695 END LOOP;
2696 l(' FROM HZ_PARTIES p, HZ_ORGANIZATION_PROFILES op ');
2697 l(' WHERE mod(p.PARTY_ID, p_num_workers) = p_worker_number ');
2698 l(' AND p.party_id = op.party_id ');
2699 l(' AND op.effective_end_date is NULL ');
2700 l(' AND p.PARTY_TYPE =''ORGANIZATION'' ');
2701 l(' ORDER BY p.PARTY_NAME;');
2702 l(' ELSIF p_party_type = ''PERSON'' THEN');
2703 l(' open x_party_cur FOR ' );
2704 l(' SELECT p.PARTY_ID, p.STATUS, p.PARTY_ID AS PERSON_PARTY_ID '); -- Fix for bug 5155761
2705 FOR I in 1..l_per_select.COUNT LOOP
2706 IF l_per_select(I) <> 'N' THEN
2707 l(' ,' || l_per_select(I));
2708 END IF;
2709 END LOOP;
2710 l(' FROM HZ_PARTIES p, HZ_PERSON_PROFILES pe ');
2711 l(' WHERE mod(p.PARTY_ID, p_num_workers) = p_worker_number ');
2712 l(' AND p.party_id = pe.party_id ');
2713 l(' AND pe.effective_end_date is NULL ');
2714 l(' AND p.PARTY_TYPE =''PERSON'' ');
2715 l(' ORDER BY p.PARTY_NAME;');
2716 l(' ELSE');
2717 l(' open x_party_cur FOR ' );
2718 l(' SELECT p.PARTY_ID, p.STATUS, NULL AS PERSON_PARTY_ID '); -- Fix for bug 5155761
2719 FOR I in 1..l_oth_select.COUNT LOOP
2720 IF l_oth_select(I) <> 'N' THEN
2721 l(' ,' || l_oth_select(I));
2722 END IF;
2723 END LOOP;
2724 l(' FROM HZ_PARTIES p ');
2725 l(' WHERE mod(p.PARTY_ID, p_num_workers) = p_worker_number ');
2726 l(' AND p.party_type <> ''PERSON'' ');
2727 l(' AND p.party_type <> ''ORGANIZATION'' ');
2728 l(' AND p.party_type <> ''PARTY_RELATIONSHIP'' ');
2729 l(' ORDER BY p.PARTY_NAME;');
2730 l(' END IF;');
2731 l(' ELSE');
2732 l(' IF p_party_type = ''ORGANIZATION'' THEN');
2733 l(' open x_party_cur FOR ' );
2734 l(' SELECT p.PARTY_ID, p.STATUS, NULL AS PERSON_PARTY_ID '); -- Fix for bug 5155761
2735 FOR I in 1..l_org_select.COUNT LOOP
2736 IF l_org_select(I) <> 'N' THEN
2737 l(' ,' || l_org_select(I));
2738 END IF;
2739 END LOOP;
2740
2741 l(' FROM HZ_PARTIES p, HZ_ORGANIZATION_PROFILES op ');
2742 l(' WHERE mod(p.PARTY_ID, p_num_workers) = p_worker_number ');
2743 l(' AND NOT EXISTS (select 1 FROM HZ_SHADOW_ST_PARTIES sp ');
2744 l(' WHERE sp.party_id = p.party_id) ' );
2745 l(' AND p.party_id = op.party_id ');
2746 l(' AND op.effective_end_date is NULL ');
2747 l(' AND p.PARTY_TYPE =''ORGANIZATION'' ');
2748 l(' ORDER BY p.PARTY_NAME;');
2749 l(' ELSIF p_party_type = ''PERSON'' THEN');
2750 l(' open x_party_cur FOR ' );
2751 l(' SELECT p.PARTY_ID, p.STATUS, p.PARTY_ID AS PERSON_PARTY_ID '); -- Fix for bug 5155761
2752 FOR I in 1..l_per_select.COUNT LOOP
2753 IF l_per_select(I) <> 'N' THEN
2754 l(' ,' || l_per_select(I));
2755 END IF;
2756 END LOOP;
2757 l(' FROM HZ_PARTIES p, HZ_PERSON_PROFILES pe ');
2758 l(' WHERE mod(p.PARTY_ID, p_num_workers) = p_worker_number ');
2759 l(' AND NOT EXISTS (select 1 FROM HZ_SHADOW_ST_PARTIES sp ');
2760 l(' WHERE sp.party_id = p.party_id) ' );
2761 l(' AND p.party_id = pe.party_id ');
2762 l(' AND pe.effective_end_date is NULL ');
2763 l(' AND p.PARTY_TYPE =''PERSON'' ');
2764 l(' ORDER BY p.PARTY_NAME;');
2765 l(' ELSE');
2766 l(' open x_party_cur FOR ' );
2767 l(' SELECT p.PARTY_ID, p.STATUS, NULL AS PERSON_PARTY_ID'); -- Fix for bug 5155761
2768 FOR I in 1..l_oth_select.COUNT LOOP
2769 IF l_oth_select(I) <> 'N' THEN
2770 l(' ,' || l_oth_select(I));
2771 END IF;
2772 END LOOP;
2773 l(' FROM HZ_PARTIES p ');
2774 l(' WHERE mod(p.PARTY_ID, p_num_workers) = p_worker_number ');
2775 l(' AND NOT EXISTS (select 1 FROM HZ_SHADOW_ST_PARTIES sp ');
2776 l(' WHERE sp.party_id = p.party_id) ' );
2777 l(' AND p.party_type <> ''PERSON'' ');
2778 l(' AND p.party_type <> ''ORGANIZATION'' ');
2779 l(' AND p.party_type <> ''PARTY_RELATIONSHIP'' ');
2780 l(' ORDER BY p.PARTY_NAME;');
2781 l(' END IF;');
2782 l(' END IF;');
2783 l(' END IF;');
2784 l(' END;');
2785
2786 l(' PROCEDURE insert_stage_parties ( ');
2787 l(' p_continue IN VARCHAR2, ');
2788 l(' p_party_cur IN HZ_PARTY_STAGE.StageCurTyp) IS ');
2789 l(' l_limit NUMBER := ' || g_batch_size || ';');
2790 l(' l_contact_cur HZ_PARTY_STAGE.StageCurTyp;');
2791 l(' l_cpt_cur HZ_PARTY_STAGE.StageCurTyp;');
2792 l(' l_party_site_cur HZ_PARTY_STAGE.StageCurTyp;');
2793 l(' l_last_fetch BOOLEAN := FALSE;');
2794 l(' call_status BOOLEAN;');
2795 l(' rphase varchar2(255);');
2796 l(' rstatus varchar2(255);');
2797 l(' dphase varchar2(255);');
2798 l(' dstatus varchar2(255);');
2799 l(' message varchar2(255);');
2803 l(' H_PERSON_PARTY_ID NumberList;'); -- Fix for bug 5155761
2800 l(' req_id NUMBER;');
2801 l(' l_st number; ');
2802 l(' l_en number; ');
2804 l(' USER_TERMINATE EXCEPTION;');
2805 l('');
2806 l(' BEGIN');
2807 l(' req_id := FND_GLOBAL.CONC_REQUEST_ID;');
2808 l(' LOOP');
2809 l(' call_status := FND_CONCURRENT.GET_REQUEST_STATUS(');
2810 l(' req_id, null,null,rphase,rstatus,dphase,dstatus,message);');
2811 l(' IF dstatus = ''TERMINATING'' THEN');
2812 l(' FND_FILE.put_line(FND_FILE.log,''Aborted by User'');');
2813 l(' RAISE USER_TERMINATE;');
2814 l(' END IF;');
2815 l(' FETCH p_party_cur BULK COLLECT INTO');
2816 l(' H_P_PARTY_ID');
2817 l(' , H_STATUS');
2818 l(' , H_PERSON_PARTY_ID'); -- Fix for bug 5155761
2819 FOR I IN 1..255 LOOP
2820 IF l_forall_list(I) <> 'N' AND l_mincol_list(I) = 'Y' AND
2821 l_custom_list(I) = 'N' THEN
2822 l(' ,H_TX'||I);
2823 END IF;
2824 END LOOP;
2825 l(' LIMIT l_limit;');
2826 l('');
2827 l(' IF p_party_cur%NOTFOUND THEN');
2828 l(' l_last_fetch:=TRUE;');
2829 l(' END IF;');
2830
2831 l(' IF H_P_PARTY_ID.COUNT=0 AND l_last_fetch THEN');
2832 l(' EXIT;');
2833 l(' END IF;');
2834
2835 l(' FOR I in H_P_PARTY_ID.FIRST..H_P_PARTY_ID.LAST LOOP');
2836
2837 -- VJN INTRODUCED FOR CONDITIONAL REPLACEMENTS
2838 -- CYCLE THROUGH THE CONDITON LIST AND GENERATE THE CODE
2839 -- FOR POPULATING THE GLOBAL CONDITION RECORD
2840
2841 l_idx := l_cond_attrib_list.FIRST ;
2842 IF l_idx IS NOT NULL
2843 THEN
2844 l('----------- SETTING GLOBAL CONDITION RECORD AT THE PARTY LEVEL ---------');
2845 END IF ;
2846
2847
2848 WHILE l_cond_attrib_list.EXISTS(l_idx)
2849 LOOP
2850
2851 l(' HZ_WORD_CONDITIONS_PKG.set_gbl_condition_rec ('||l_cond_attrib_list(l_idx)||','||'H_TX'||l_idx||'(I));');
2852 l_idx := l_idx+1;
2853 END LOOP;
2854 l(' HZ_TRANS_PKG.next_gen_dqm := ''Y'';');
2855 l('');
2856
2857
2858 FOR I IN 1..255 LOOP
2859 IF l_forall_list(I) <> 'N' THEN
2860 IF l_custom_list(I) <> 'N' AND l_mincol_list(I) = 'Y' THEN
2861 l(' H_TX'||I||'(I):='||l_custom_list(I)||';');
2862 END IF;
2863 END IF;
2864 END LOOP;
2865 FOR I IN 1..255 LOOP
2866 IF l_forall_list(I) <> 'N' THEN
2867 IF l_mincol_list(I) <> 'Y' THEN
2868 l(' H_TX'||I||'(I):='||l_forall_list(I)||';');
2869 END IF;
2870 END IF;
2871 END LOOP;
2872 FOR I IN 1..255 LOOP
2873 IF l_forall_list(I) <> 'N' THEN
2874 IF l_mincol_list(I) = 'Y' THEN
2875 l(' H_TX'||I||'(I):='||l_forall_list(I)||';');
2876 END IF;
2877 END IF;
2878 END LOOP;
2879 l(' H_PARTY_INDEX(I) := I;');
2880 l(' END LOOP;');
2881
2882 l(' SAVEPOINT party_batch;');
2883 l(' BEGIN ');
2884 l(' l_st := 1; ');
2885 l(' l_en := H_P_PARTY_ID.COUNT; ');
2886 l(' LOOP ');
2887 l(' BEGIN ');
2888 l(' FORALL I in l_st..l_en');
2889 l(' INSERT INTO HZ_SHADOW_ST_PARTIES (');
2890 l(' PARTY_ID');
2891 l(' ,STATUS');
2892 FOR I IN 1..255 LOOP
2893 IF l_forall_list(I) <> 'N' THEN
2894 l(' , TX'||I);
2895 END IF;
2896 END LOOP;
2897 l(' ) VALUES (');
2898 l(' H_P_PARTY_ID(I)');
2899 l(' ,H_STATUS(I)');
2900 FOR I IN 1..255 LOOP
2901 IF l_forall_list(I) <> 'N' THEN
2902 l(' , decode(H_TX'||I||'(I),null,H_TX'||I||'(I),H_TX'||I||'(I)||'' '')');
2903 END IF;
2904 END LOOP;
2905 l(' );');
2906 l(' EXIT; ');
2907 l(' EXCEPTION WHEN OTHERS THEN ');
2908 l(' l_st:= l_st+SQL%ROWCOUNT+1;');
2909 l(' END; ');
2910 l(' END LOOP; ');
2911 l(' FORALL I in H_P_PARTY_ID.FIRST..H_P_PARTY_ID.LAST');
2912 l(' INSERT INTO HZ_DQM_STAGE_GT ( PARTY_ID, OWNER_ID, PARTY_INDEX, PERSON_PARTY_ID) VALUES ('); -- Fix for bug 5155761
2913 l(' H_P_PARTY_ID(I),H_P_PARTY_ID(I),H_PARTY_INDEX(I),H_PERSON_PARTY_ID(I));'); -- Fix for bug 5155761
2914
2915 l(' insert_stage_contacts;');
2916 l(' insert_stage_party_sites;');
2917 l(' insert_stage_contact_pts;');
2918 l(' EXCEPTION ');
2919 l(' WHEN OTHERS THEN');
2920 l(' ROLLBACK to party_batch;');
2921 l(' RAISE;');
2922 l(' END;');
2923 l(' IF l_last_fetch THEN');
2924 l(' FND_CONCURRENT.AF_Commit;');
2925 l(' EXIT;');
2926 l(' END IF;');
2927 l(' FND_CONCURRENT.AF_Commit;');
2928 l(' END LOOP;');
2929 l(' END;');
2930
2931 l(' PROCEDURE sync_single_party (');
2932 l(' p_party_id NUMBER,');
2933 l(' p_party_type VARCHAR2,');
2934 l(' p_operation VARCHAR2) IS');
2935 l('');
2936 l(' l_tryins BOOLEAN;');
2937 l(' l_tryupd BOOLEAN;');
2938 l(' BEGIN');
2939 l(' IF p_party_type = ''ORGANIZATION'' THEN');
2940 l(' SELECT p.PARTY_ID, p.STATUS ');
2941 FOR I in 1..l_org_select.COUNT LOOP
2942 IF l_org_select(I) <> 'N' THEN
2943 l(' ,' || l_org_select(I));
2944 END IF;
2945 END LOOP;
2946 l(' INTO H_P_PARTY_ID(1), H_STATUS(1)');
2947 FOR I IN 1..255 LOOP
2948 IF l_forall_list(I) <> 'N' AND l_mincol_list(I) = 'Y' AND
2949 l_custom_list(I) = 'N' THEN
2950 l(' , H_TX'||I||'(1)');
2951 END IF;
2952 END LOOP;
2953 l(' FROM HZ_PARTIES p, HZ_ORGANIZATION_PROFILES op ');
2954 l(' WHERE p.party_id = p_party_id ');
2955 l(' AND p.party_id = op.party_id ');
2956 l(' AND (p.status = ''M'' or op.effective_end_date is NULL) AND ROWNUM=1; ');
2957 l(' ELSIF p_party_type = ''PERSON'' THEN');
2958 l(' SELECT p.PARTY_ID, p.STATUS ');
2959 FOR I in 1..l_per_select.COUNT LOOP
2960 IF l_per_select(I) <> 'N' THEN
2961 l(' ,' || l_per_select(I));
2962 END IF;
2963 END LOOP;
2964 l(' INTO H_P_PARTY_ID(1), H_STATUS(1)');
2965 FOR I IN 1..255 LOOP
2966 IF l_forall_list(I) <> 'N' AND l_mincol_list(I) = 'Y' AND
2967 l_custom_list(I) = 'N' THEN
2968 l(' , H_TX'||I||'(1)');
2969 END IF;
2970 END LOOP;
2971 l(' FROM HZ_PARTIES p, HZ_PERSON_PROFILES pe ');
2972 l(' WHERE p.party_id = p_party_id ');
2973 l(' AND p.party_id = pe.party_id ');
2974 l(' AND (p.status = ''M'' or pe.effective_end_date is NULL) AND ROWNUM=1;');
2975 l(' ELSE');
2976 l(' SELECT p.PARTY_ID, p.STATUS ');
2977 FOR I in 1..l_oth_select.COUNT LOOP
2978 IF l_per_select(I) <> 'N' THEN
2979 l(' ,' || l_oth_select(I));
2980 END IF;
2981 END LOOP;
2982
2983 l(' INTO H_P_PARTY_ID(1), H_STATUS(1)');
2984 FOR I IN 1..255 LOOP
2985 IF l_forall_list(I) <> 'N' AND l_mincol_list(I) = 'Y' AND
2986 l_custom_list(I) = 'N' THEN
2987 l(' , H_TX'||I||'(1)');
2988 END IF;
2989 END LOOP;
2990 l(' FROM HZ_PARTIES p ');
2991 l(' WHERE p.party_id = p_party_id;');
2992 l(' END IF;');
2993 -- VJN INTRODUCED CODE FOR GENERATING THE POPULATION
2994 -- OF THE GLOBAL CONDITION RECORD
2995
2996 l_idx := l_cond_attrib_list.FIRST ;
2997 IF l_idx IS NOT NULL
2998 THEN
2999 l('----------- SETTING GLOBAL CONDITION RECORD AT THE PARTY LEVEL ---------');
3000 END IF ;
3001
3002
3003 WHILE l_cond_attrib_list.EXISTS(l_idx)
3004 LOOP
3005
3006 l(' HZ_WORD_CONDITIONS_PKG.set_gbl_condition_rec ('||l_cond_attrib_list(l_idx)||','||'H_TX'||l_idx||'(1));');
3007 l_idx := l_idx+1;
3008 END LOOP;
3009 l(' HZ_TRANS_PKG.next_gen_dqm := ''Y'';');
3010
3011
3012 FOR I IN 1..255 LOOP
3013 IF l_forall_list(I) <> 'N' THEN
3014 IF l_custom_list(I) <> 'N' AND l_mincol_list(I) = 'Y' THEN
3015 l(' H_TX'||I||'(1):='||replace(l_custom_list(I),'(I)','(1)')||';');
3016 END IF;
3017 END IF;
3018 END LOOP;
3019 FOR I IN 1..255 LOOP
3020 IF l_forall_list(I) <> 'N' THEN
3021 IF l_mincol_list(I) <> 'Y' THEN
3022 l(' H_TX'||I||'(1):='||replace(l_forall_list(I),'(I)','(1)')||';');
3023 END IF;
3024 END IF;
3025 END LOOP;
3026 FOR I IN 1..255 LOOP
3027 IF l_forall_list(I) <> 'N' THEN
3028 IF l_mincol_list(I) = 'Y' THEN
3029 l(' H_TX'||I||'(1):='||replace(l_forall_list(I),'(I)','(1)')||';');
3030 END IF;
3031 END IF;
3032 END LOOP;
3033
3034 l(' l_tryins := FALSE;');
3035 l(' l_tryupd := FALSE;');
3036 l(' IF p_operation=''C'' THEN');
3037 l(' l_tryins:=TRUE;');
3038 l(' ELSE ');
3039 l(' l_tryupd:=TRUE;');
3040 l(' END IF;');
3041 l(' WHILE (l_tryins OR l_tryupd) LOOP');
3042 l(' IF l_tryins THEN');
3043 l(' BEGIN');
3044 l(' l_tryins:=FALSE;');
3045 l(' INSERT INTO HZ_SHADOW_ST_PARTIES (');
3046 l(' PARTY_ID');
3047 l(' ,STATUS');
3048 l(' ,D_PS');
3049 l(' ,D_CT');
3050 l(' ,D_CPT');
3051 FOR I IN 1..255 LOOP
3052 IF l_forall_list(I) <> 'N' THEN
3053 l(' , TX'||I);
3054 END IF;
3055 END LOOP;
3056 l(' ) VALUES (');
3057 l(' H_P_PARTY_ID(1)');
3058 l(' ,H_STATUS(1)');
3059 l(' ,''SYNC''');
3060 l(' ,''SYNC''');
3061 l(' ,''SYNC''');
3062 FOR I IN 1..255 LOOP
3063 IF l_forall_list(I) <> 'N' THEN
3064 l(' , decode(H_TX'||I||'(1),null,H_TX'||I||'(1),H_TX'||I||'(1)||'' '')');
3065 END IF;
3066 END LOOP;
3067 l(' );');
3068 l(' EXCEPTION');
3069 l(' WHEN DUP_VAL_ON_INDEX THEN');
3070 l(' IF p_operation=''C'' THEN');
3071 l(' l_tryupd:=TRUE;');
3072 l(' END IF;');
3073 l(' END;');
3074 l(' END IF;');
3075 l(' IF l_tryupd THEN');
3076 l(' BEGIN');
3077 l(' l_tryupd:=FALSE;');
3078 l(' UPDATE HZ_SHADOW_ST_PARTIES SET ');
3079 l(' status =H_STATUS(1) ');
3080 FOR I IN 1..255 LOOP
3081 IF l_forall_list(I) <> 'N' THEN
3082 l(' ,TX'||I||'=decode(H_TX'||I||'(1),null,H_TX'||I||'(1),H_TX'||I||'(1)||'' '')');
3083 END IF;
3084 END LOOP;
3085 l(' WHERE PARTY_ID=H_P_PARTY_ID(1);');
3086 l(' IF SQL%ROWCOUNT=0 AND p_operation=''U'' THEN');
3087 l(' l_tryins := TRUE;');
3088 l(' END IF;');
3089 l(' EXCEPTION ');
3090 l(' WHEN NO_DATA_FOUND THEN');
3091 l(' IF p_operation=''U'' THEN');
3092 l(' l_tryins := TRUE;');
3093 l(' END IF;');
3094 l(' END;');
3095 l(' END IF;');
3096 l(' END LOOP;');
3097 l(' END;');
3098
3099 END;
3100
3101 PROCEDURE generate_party_site_query_proc IS
3102 cur_col_num NUMBER := 1;
3103
3104 TYPE coltab IS TABLE OF VARCHAR2(2000) INDEX BY BINARY_INTEGER;
3105
3106 l_select coltab;
3107 FIRST BOOLEAN := FALSE;
3108
3109 l_forall_list coltab;
3110 l_custom_list coltab;
3114 is_first boolean := true;
3111 l_mincol_list coltab;
3112 l_min_colnum NUMBER;
3113 idx NUMBER :=1;
3115
3116 -- VJN Introduced for conditional word replacements
3117 l_cond_attrib_list coltab ;
3118 l_idx number ;
3119 l_attr_name varchar2(2000); --Bug No: 4954701
3120 l_ps_attr_name varchar2(2000); --Bug No: 4954701
3121 l_loc_attr_name varchar2(2000); --Bug No: 4954701
3122 BEGIN
3123
3124 l(' PROCEDURE insert_stage_party_sites IS ');
3125 l(' l_limit NUMBER := ' || g_batch_size || ';');
3126 l(' l_last_fetch BOOLEAN := FALSE;');
3127 l(' l_denorm VARCHAR2(2000);');
3128 l(' l_st number; ');
3129 l(' l_en number; ');
3130
3131 l(' ');
3132
3133 FOR ATTRS IN (SELECT a.ATTRIBUTE_ID,
3134 a.ATTRIBUTE_NAME,
3135 a.SOURCE_TABLE,
3136 a.CUSTOM_ATTRIBUTE_PROCEDURE,
3137 f.PROCEDURE_NAME,
3138 f.STAGED_ATTRIBUTE_COLUMN,
3139 to_number(substrb(STAGED_ATTRIBUTE_COLUMN, 3)) COLNUM,
3140 nvl(lkp.tag,'C') column_data_type
3141 FROM HZ_TRANS_ATTRIBUTES_VL a, HZ_TRANS_FUNCTIONS_VL f,FND_LOOKUP_VALUES_VL lkp --Bug No: 4954701
3142 WHERE ENTITY_NAME = 'PARTY_SITES'
3143 AND nvl(f.ACTIVE_FLAG,'Y') = 'Y'
3144 AND f.ATTRIBUTE_ID = a.ATTRIBUTE_ID
3145 AND lkp.lookup_type = 'PARTY_SITE_LOGICAL_ATTRIB_LIST'
3146 and lkp.lookup_code = a.ATTRIBUTE_NAME
3147 ORDER BY COLNUM) LOOP
3148 IF cur_col_num<ATTRS.COLNUM THEN
3149 FOR I in cur_col_num..ATTRS.COLNUM-1 LOOP
3150 l_mincol_list(I) := 'N';
3151 l_forall_list(I) := 'N';
3152 l_custom_list(I) := 'N';
3153 END LOOP;
3154 END IF;
3155 cur_col_num:=ATTRS.COLNUM+1;
3156 IF has_trx_context(ATTRS.PROCEDURE_NAME) THEN
3157 l_forall_list(ATTRS.COLNUM) := ATTRS.PROCEDURE_NAME || '(H_TX'||ATTRS.COLNUM||'(I),NULL, ''' || ATTRS.ATTRIBUTE_NAME || ''',''PARTY_SITES'',''STAGE'')';
3158 ELSE
3159 l_forall_list(ATTRS.COLNUM) := ATTRS.PROCEDURE_NAME || '(H_TX'||ATTRS.COLNUM||'(I),NULL, ''' || ATTRS.ATTRIBUTE_NAME || ''',''PARTY_SITES'')';
3160 END IF;
3161
3162 SELECT min(to_number(substrb(STAGED_ATTRIBUTE_COLUMN, 3))) INTO l_min_colnum
3163 FROM HZ_TRANS_ATTRIBUTES_VL a, HZ_TRANS_FUNCTIONS_VL f
3164 WHERE ENTITY_NAME = 'PARTY_SITES'
3165 AND nvl(f.ACTIVE_FLAG,'Y') = 'Y'
3166 AND f.ATTRIBUTE_ID = a.ATTRIBUTE_ID
3167 AND a.ATTRIBUTE_NAME=ATTRS.ATTRIBUTE_NAME;
3168
3169 IF ATTRS.colnum>l_min_colnum THEN
3170 l_mincol_list(ATTRS.COLNUM) := 'N';
3171 IF has_trx_context(ATTRS.PROCEDURE_NAME) THEN
3172 l_forall_list(ATTRS.COLNUM) := ATTRS.PROCEDURE_NAME || '(H_TX'||l_min_colnum||'(I),NULL, ''' || ATTRS.ATTRIBUTE_NAME || ''',''PARTY_SITES'',''STAGE'')';
3173 ELSE
3174 l_forall_list(ATTRS.COLNUM) := ATTRS.PROCEDURE_NAME || '(H_TX'||l_min_colnum||'(I),NULL, ''' || ATTRS.ATTRIBUTE_NAME || ''',''PARTY_SITES'')';
3175 END IF;
3176 ELSE
3177 l_mincol_list(ATTRS.COLNUM) := 'Y';
3178 END IF;
3179
3180 IF ATTRS.CUSTOM_ATTRIBUTE_PROCEDURE IS NULL THEN
3181 IF l_mincol_list(ATTRS.COLNUM) = 'Y' THEN
3182 -----Start of Bug No: 4954701----------
3183 l_attr_name := ATTRS.ATTRIBUTE_NAME;
3184 IF(ATTRS.column_data_type ='D') THEN
3185 l_ps_attr_name := 'TO_CHAR(ps.'||l_attr_name||',''DD-MON-YYYY'') '||l_attr_name;
3186 l_loc_attr_name := 'TO_CHAR(l.'||l_attr_name||',''DD-MON-YYYY'') '||l_attr_name;
3187 ELSE
3188 l_ps_attr_name := 'ps.'||l_attr_name;
3189 l_loc_attr_name := 'l.'||l_attr_name;
3190 END IF;
3191 -----End of Bug No: 4954701------------
3192 IF ATTRS.SOURCE_TABLE='HZ_LOCATIONS' THEN
3193 l_select(idx) := l_loc_attr_name;
3194 ELSIF ATTRS.SOURCE_TABLE='HZ_PARTY_SITES' THEN
3195 l_select(idx) := l_ps_attr_name;
3196 END IF;
3197 ELSE
3198 l_select(idx) := 'N';
3199 END IF;
3200
3201 l_custom_list(ATTRS.COLNUM) := 'N';
3202 ELSE
3203 l_select(idx) := 'N';
3204 l_custom_list(ATTRS.COLNUM) := 'N';
3205 IF ATTRS.ATTRIBUTE_NAME = 'ADDRESS' THEN
3206 IF l_mincol_list(ATTRS.COLNUM) = 'Y' THEN
3207 l_select(idx) := 'rtrim(l.address1 || '' '' || l.address2 || '' '' || l.address3 || '' '' || l.address4)';
3208 END IF;
3209 ELSE
3210 IF l_mincol_list(ATTRS.COLNUM) = 'Y' THEN
3211 IF has_context(ATTRS.custom_attribute_procedure) THEN
3212 l_custom_list(ATTRS.COLNUM) := ATTRS.custom_attribute_procedure || '(H_PARTY_SITE_ID(I),''PARTY_SITES'','''||ATTRS.ATTRIBUTE_NAME|| ''', ''STAGE'')';
3213 ELSE
3214 l_custom_list(ATTRS.COLNUM) := ATTRS.custom_attribute_procedure || '(H_PARTY_SITE_ID(I), ''PARTY_SITES'','''||ATTRS.ATTRIBUTE_NAME|| ''')';
3215 END IF;
3216 END IF;
3217 END IF;
3218 END IF;
3219 idx := idx+1;
3220
3221 -- VJN ADDED CODE FOR TRACKING CONDITION ATTRIBUTES AT THIS ENTITY LEVEL
3222 IF HZ_WORD_CONDITIONS_PKG.is_a_cond_attrib( ATTRS.attribute_id)
3223 THEN
3224 l_cond_attrib_list(ATTRS.COLNUM) := ATTRS.attribute_id ;
3225 END IF;
3226
3227 END LOOP;
3228
3229 IF cur_col_num<255 THEN
3230 FOR I in cur_col_num..255 LOOP
3231 l_mincol_list(I) := 'N';
3232 l_forall_list(I) := 'N';
3233 l_custom_list(I) := 'N';
3234 END LOOP;
3235 END IF;
3236
3237 l(' CURSOR party_site_cur IS');
3238 l(' SELECT /*+ cardinality(g 200) use_nl(g ps l) */ ps.PARTY_SITE_ID, g.party_id, g.org_contact_id, g.PARTY_INDEX, g.PERSON_PARTY_ID, ps.status '); -- Bug No: 4299785
3239 FOR I in 1..l_select.COUNT LOOP
3240 IF l_select(I) <> 'N' THEN
3241 l(' ,' || l_select(I));
3242 END IF;
3246 l(' WHERE ps.PARTY_ID = g.owner_id ');
3243 END LOOP;
3244
3245 l(' FROM HZ_DQM_STAGE_GT g, HZ_PARTY_SITES ps, HZ_LOCATIONS l');
3247 l(' AND (ps.status is null OR ps.status = ''A'' OR ps.status = ''I'') ');
3248 l(' AND ps.location_id = l.location_id; ');
3249
3250 l(' BEGIN');
3251 l(' OPEN party_site_cur;');
3252 l(' LOOP');
3253 l(' FETCH party_site_cur BULK COLLECT INTO');
3254 l(' H_PARTY_SITE_ID');
3255 l(' ,H_PS_PARTY_ID');
3256 l(' ,H_PS_ORG_CONTACT_ID');
3257 l(' ,H_PARTY_INDEX');
3258 l(' ,H_PERSON_PARTY_ID');
3259 l(' ,H_STATUS'); -- Bug No: 4299785
3260 FOR I IN 1..255 LOOP
3261 IF l_forall_list(I) <> 'N' AND l_mincol_list(I) = 'Y' AND
3262 l_custom_list(I) = 'N' THEN
3263 l(' ,H_TX'||I);
3264 END IF;
3265 END LOOP;
3266 l(' LIMIT l_limit;');
3267 l('');
3268 l(' IF party_site_cur%NOTFOUND THEN');
3269 l(' l_last_fetch:=TRUE;');
3270 l(' END IF;');
3271
3272 l(' IF H_PS_PARTY_ID.COUNT=0 AND l_last_fetch THEN');
3273 l(' EXIT;');
3274 l(' END IF;');
3275
3276 l(' FOR I in H_PS_PARTY_ID.FIRST..H_PS_PARTY_ID.LAST LOOP');
3277
3278 -- VJN INTRODUCED FOR CONDITIONAL REPLACEMENTS
3279 -- CYCLE THROUGH THE CONDITON LIST AND GENERATE THE CODE
3280 -- FOR POPULATING THE GLOBAL CONDITION RECORD
3281
3282 l_idx := l_cond_attrib_list.FIRST ;
3283 IF l_idx IS NOT NULL
3284 THEN
3285 l('----------- SETTING GLOBAL CONDITION RECORD AT THE PARTY SITE LEVEL ---------');
3286 END IF ;
3287
3288
3289 l('');
3290
3291
3292 WHILE l_cond_attrib_list.EXISTS(l_idx)
3293 LOOP
3294
3295 l(' HZ_WORD_CONDITIONS_PKG.set_gbl_condition_rec ('||l_cond_attrib_list(l_idx)||','||'H_TX'||l_idx||'(I));');
3296 l_idx := l_idx+1;
3297 END LOOP;
3298 l(' HZ_TRANS_PKG.next_gen_dqm := ''Y'';');
3299
3300 FOR I IN 1..255 LOOP
3301 IF l_forall_list(I) <> 'N' THEN
3302 IF l_custom_list(I) <> 'N' AND l_mincol_list(I) = 'Y' THEN
3303 l(' H_TX'||I||'(I):='||l_custom_list(I)||';');
3304 END IF;
3305 END IF;
3306 END LOOP;
3307 FOR I IN 1..255 LOOP
3308 IF l_forall_list(I) <> 'N' THEN
3309 IF l_mincol_list(I) <> 'Y' THEN
3310 l(' H_TX'||I||'(I):='||l_forall_list(I)||';');
3311 END IF;
3312 END IF;
3313 END LOOP;
3314 FOR I IN 1..255 LOOP
3315 IF l_forall_list(I) <> 'N' THEN
3316 IF l_mincol_list(I) = 'Y' THEN
3317 l(' H_TX'||I||'(I):='||l_forall_list(I)||';');
3318 END IF;
3319 END IF;
3320 END LOOP;
3321
3322
3323 l(' END LOOP;');
3324 l(' l_st := 1; ');
3325 l(' l_en := H_PS_PARTY_ID.COUNT; ');
3326 l(' LOOP ');
3327 l(' BEGIN ');
3328 l(' FORALL I in l_st..l_en');
3329 l(' INSERT INTO HZ_SHADOW_ST_PSITES (');
3330 l(' PARTY_SITE_ID');
3331 l(' ,PARTY_ID');
3332 l(' ,ORG_CONTACT_ID');
3333 l(' ,PERSON_PARTY_ID');
3334 l(' ,STATUS_FLAG');-- Bug No: 4299785
3335 FOR I IN 1..255 LOOP
3336 IF l_forall_list(I) <> 'N' THEN
3337 l(' , TX'||I);
3338 END IF;
3339 END LOOP;
3340 l(' ,QKEY');
3341 l(' ) VALUES (');
3342 l(' H_PARTY_SITE_ID(I)');
3343 l(' ,H_PS_PARTY_ID(I)');
3344 l(' ,H_PS_ORG_CONTACT_ID(I)');
3345 l(' ,H_PERSON_PARTY_ID(I)');
3346 l(' ,H_STATUS(I)'); -- Bug No: 4299785
3347 FOR I IN 1..255 LOOP
3348 IF l_forall_list(I) <> 'N' THEN
3349 l(' , decode(H_TX'||I||'(I),null,H_TX'||I||'(I),H_TX'||I||'(I)||'' '')');
3350 END IF;
3351 END LOOP;
3352 l(' ,'||G_PS_QKEY_STR);
3353 l(' );');
3354 l(' EXIT; ');
3355 l(' EXCEPTION WHEN OTHERS THEN ');
3356 l(' l_st:= l_st+SQL%ROWCOUNT+1;');
3357 l(' END; ');
3358 l(' END LOOP; ');
3359 l(' FORALL I in H_PS_PARTY_ID.FIRST..H_PS_PARTY_ID.LAST ');
3360 l(' INSERT INTO HZ_DQM_STAGE_GT (PARTY_ID, OWNER_ID, OWNER_TABLE, PARTY_SITE_ID,');
3361 l(' PERSON_PARTY_ID, ORG_CONTACT_ID,PARTY_INDEX) VALUES (');
3362 l(' H_PS_PARTY_ID(I),H_PARTY_SITE_ID(I),''HZ_PARTY_SITES'',H_PARTY_SITE_ID(I),');
3363 l(' H_PERSON_PARTY_ID(I), H_PS_ORG_CONTACT_ID(I),H_PARTY_INDEX(I));'); -- Fix for bug 5155761
3364
3365 l(' IF l_last_fetch THEN');
3366 l(' EXIT;');
3367 l(' END IF;');
3368 l(' END LOOP;');
3369 l(' CLOSE party_site_cur;');
3370 l(' END;');
3371
3372 l(' PROCEDURE sync_single_party_site (');
3373 l(' p_party_site_id NUMBER,');
3374 l(' p_operation VARCHAR2) IS');
3375 l('');
3376 l(' l_tryins BOOLEAN;');
3377 l(' l_tryupd BOOLEAN;');
3378 l(' BEGIN');
3379
3380 l(' SELECT ps.PARTY_SITE_ID, d.party_id, d.org_contact_id, ps.status '); -- Bug No: 4299785
3381 FOR I in 1..l_select.COUNT LOOP
3382 IF l_select(I) <> 'N' THEN
3383 l(' ,' || l_select(I));
3384 END IF;
3385 END LOOP;
3386 l(' INTO H_PARTY_SITE_ID(1), H_PARTY_ID(1), H_ORG_CONTACT_ID(1),H_STATUS(1) ');-- Bug No: 4299785
3387 FOR I IN 1..255 LOOP
3388 IF l_forall_list(I) <> 'N' AND l_mincol_list(I) = 'Y' AND
3389 l_custom_list(I) = 'N' THEN
3390 l(' , H_TX'||I||'(1)');
3391 END IF;
3395 l(' AND ps.party_site_id = p_party_site_id');
3392 END LOOP;
3393 l(' FROM HZ_PARTY_SITES ps, HZ_DQM_SYNC_INTERFACE d, HZ_LOCATIONS l ');
3394 l(' WHERE d.ENTITY=''PARTY_SITES'' ');
3396 l(' AND d.record_id = ps.party_site_id ');
3397 l(' AND ps.location_id = l.location_id ');
3398 l(' AND (ps.status is null OR ps.status = ''A'' OR ps.status = ''I'') ');
3399 l(' AND ROWNUM=1;');
3400
3401 -- VJN INTRODUCED CODE FOR GENERATING THE POPULATION
3402 -- OF THE GLOBAL CONDITION RECORD
3403
3404 l_idx := l_cond_attrib_list.FIRST ;
3405 IF l_idx IS NOT NULL
3406 THEN
3407 l('----------- SETTING GLOBAL CONDITION RECORD AT THE PARTY SITE LEVEL ---------');
3408 END IF ;
3409
3410
3411 WHILE l_cond_attrib_list.EXISTS(l_idx)
3412 LOOP
3413
3414 l(' HZ_WORD_CONDITIONS_PKG.set_gbl_condition_rec ('||l_cond_attrib_list(l_idx)||','||'H_TX'||l_idx||'(1));');
3415 l_idx := l_idx+1;
3416 END LOOP;
3417 l(' HZ_TRANS_PKG.next_gen_dqm := ''Y'';');
3418
3419 FOR I IN 1..255 LOOP
3420 IF l_forall_list(I) <> 'N' THEN
3421 IF l_custom_list(I) <> 'N' AND l_mincol_list(I) = 'Y' THEN
3422 l(' H_TX'||I||'(1):='||replace(l_custom_list(I),'(I)','(1)')||';');
3423 END IF;
3424 END IF;
3425 END LOOP;
3426 FOR I IN 1..255 LOOP
3427 IF l_forall_list(I) <> 'N' THEN
3428 IF l_mincol_list(I) <> 'Y' THEN
3429 l(' H_TX'||I||'(1):='||replace(l_forall_list(I),'(I)','(1)')||';');
3430 END IF;
3431 END IF;
3432 END LOOP;
3433 FOR I IN 1..255 LOOP
3434 IF l_forall_list(I) <> 'N' THEN
3435 IF l_mincol_list(I) = 'Y' THEN
3436 l(' H_TX'||I||'(1):='||replace(l_forall_list(I),'(I)','(1)')||';');
3437 END IF;
3438 END IF;
3439 END LOOP;
3440
3441 l(' l_tryins := FALSE;');
3442 l(' l_tryupd := FALSE;');
3443 l(' IF p_operation=''C'' THEN');
3444 l(' l_tryins:=TRUE;');
3445 l(' ELSE ');
3446 l(' l_tryupd:=TRUE;');
3447 l(' END IF;');
3448 l(' WHILE (l_tryins OR l_tryupd) LOOP');
3449 l(' IF l_tryins THEN');
3450 l(' BEGIN');
3451 l(' l_tryins:=FALSE;');
3452 l(' INSERT INTO HZ_SHADOW_ST_PSITES (');
3453 l(' PARTY_SITE_ID');
3454 l(' ,PARTY_ID');
3455 l(' ,ORG_CONTACT_ID');
3456 l(' ,STATUS_FLAG');-- Bug No: 4299785
3457 FOR I IN 1..255 LOOP
3458 IF l_forall_list(I) <> 'N' THEN
3459 l(' , TX'||I);
3460 END IF;
3461 END LOOP;
3462 l(' ) VALUES (');
3463 l(' H_PARTY_SITE_ID(1)');
3464 l(' ,H_PARTY_ID(1)');
3465 l(' ,H_ORG_CONTACT_ID(1)');
3466 l(' ,H_STATUS(1) '); -- Bug No: 4299785
3467 FOR I IN 1..255 LOOP
3468 IF l_forall_list(I) <> 'N' THEN
3469 l(' , decode(H_TX'||I||'(1),null,H_TX'||I||'(1),H_TX'||I||'(1)||'' '')');
3470 END IF;
3471 END LOOP;
3472 l(' );');
3473 l(' EXCEPTION');
3474 l(' WHEN DUP_VAL_ON_INDEX THEN');
3475 l(' IF p_operation=''C'' THEN');
3476 l(' l_tryupd:=TRUE;');
3477 l(' END IF;');
3478 l(' END;');
3479 l(' END IF;');
3480 l(' IF l_tryupd THEN');
3481 l(' BEGIN');
3482 l(' l_tryupd:=FALSE;');
3483 l(' UPDATE HZ_SHADOW_ST_PSITES SET ');
3484
3485 FOR I IN 1..255 LOOP
3486 IF l_forall_list(I) <> 'N' THEN
3487 IF (is_first) THEN
3488 is_first := false;
3489 l(' TX'||I||'=decode(H_TX'||I||'(1),null,H_TX'||I||'(1),H_TX'||I||'(1)||'' '')');
3490 ELSE
3491 l(' ,TX'||I||'=decode(H_TX'||I||'(1),null,H_TX'||I||'(1),H_TX'||I||'(1)||'' '')');
3492 END IF;
3493 END IF;
3494 END LOOP;
3495 l(' ,STATUS_FLAG = H_STATUS(1) ');-- Bug No: 4299785
3496 l(' WHERE PARTY_SITE_ID=H_PARTY_SITE_ID(1);');
3497 l(' IF SQL%ROWCOUNT=0 AND p_operation=''U'' THEN');
3498 l(' l_tryins := TRUE;');
3499 l(' END IF;');
3500 l(' EXCEPTION ');
3501 l(' WHEN NO_DATA_FOUND THEN');
3502 l(' IF p_operation=''U'' THEN');
3503 l(' l_tryins := TRUE;');
3504 l(' END IF;');
3505 l(' END;');
3506 l(' END IF;');
3507 l(' END LOOP;');
3508 l(' UPDATE HZ_SHADOW_ST_PARTIES set');
3509 l(' D_PS = ''SYNC''');
3510 l(' WHERE PARTY_ID = H_PARTY_ID(1);');
3511 l(' END;');
3512 END;
3513
3514 PROCEDURE generate_contact_query_proc IS
3515 cur_col_num NUMBER := 1;
3516
3517 TYPE coltab IS TABLE OF VARCHAR2(2000) INDEX BY BINARY_INTEGER;
3518
3519 l_select coltab;
3520 FIRST BOOLEAN := FALSE;
3521
3522 l_forall_list coltab;
3523 l_custom_list coltab;
3524 l_mincol_list coltab;
3525 l_min_colnum NUMBER;
3526 idx NUMBER :=1;
3527 is_first boolean := true;
3528
3529 -- VJN Introduced for conditional word replacements
3530 l_cond_attrib_list coltab ;
3531 l_idx number ;
3532 l_attr_name varchar2(2000); --Bug No: 4954701
3533 l_pp_attr_name varchar2(2000); --Bug No: 4954701
3534 l_oc_attr_name varchar2(2000); --Bug No: 4954701
3535 BEGIN
3536
3537 l(' PROCEDURE insert_stage_contacts IS ');
3538 l(' l_limit NUMBER := ' || g_batch_size || ';');
3539 l(' l_last_fetch BOOLEAN := FALSE;');
3540 l(' l_denorm VARCHAR2(2000);');
3541 l(' l_st number; ');
3542 l(' l_en number; ');
3543 FOR ATTRS IN (SELECT a.ATTRIBUTE_ID,
3547 f.PROCEDURE_NAME,
3544 a.ATTRIBUTE_NAME,
3545 a.SOURCE_TABLE,
3546 a.CUSTOM_ATTRIBUTE_PROCEDURE,
3548 f.STAGED_ATTRIBUTE_COLUMN,
3549 to_number(substrb(STAGED_ATTRIBUTE_COLUMN, 3)) COLNUM,
3550 nvl(lkp.tag,'C') column_data_type
3551 FROM HZ_TRANS_ATTRIBUTES_VL a, HZ_TRANS_FUNCTIONS_VL f,FND_LOOKUP_VALUES_VL lkp --Bug No: 4954701
3552 WHERE ENTITY_NAME = 'CONTACTS'
3553 AND nvl(f.ACTIVE_FLAG,'Y') = 'Y'
3554 AND f.ATTRIBUTE_ID = a.ATTRIBUTE_ID
3555 AND lkp.LOOKUP_TYPE='CONTACT_LOGICAL_ATTRIB_LIST'
3556 AND lkp.LOOKUP_CODE = a.ATTRIBUTE_NAME
3557 ORDER BY COLNUM) LOOP
3558 IF cur_col_num<ATTRS.COLNUM THEN
3559 FOR I in cur_col_num..ATTRS.COLNUM-1 LOOP
3560 l_mincol_list(I) := 'N';
3561 l_forall_list(I) := 'N';
3562 l_custom_list(I) := 'N';
3563 END LOOP;
3564 END IF;
3565 cur_col_num:=ATTRS.COLNUM+1;
3566 IF has_trx_context(ATTRS.PROCEDURE_NAME) THEN
3567 l_forall_list(ATTRS.COLNUM) := ATTRS.PROCEDURE_NAME || '(H_TX'||ATTRS.COLNUM||'(I),NULL, ''' || ATTRS.ATTRIBUTE_NAME || ''',''CONTACTS'',''STAGE'')';
3568 ELSE
3569 l_forall_list(ATTRS.COLNUM) := ATTRS.PROCEDURE_NAME || '(H_TX'||ATTRS.COLNUM||'(I),NULL, ''' || ATTRS.ATTRIBUTE_NAME || ''',''CONTACTS'')';
3570 END IF;
3571
3572 SELECT min(to_number(substrb(STAGED_ATTRIBUTE_COLUMN, 3))) INTO l_min_colnum
3573 FROM HZ_TRANS_ATTRIBUTES_VL a, HZ_TRANS_FUNCTIONS_VL f
3574 WHERE ENTITY_NAME = 'CONTACTS'
3575 AND nvl(f.ACTIVE_FLAG,'Y') = 'Y'
3576 AND f.ATTRIBUTE_ID = a.ATTRIBUTE_ID
3577 AND a.ATTRIBUTE_NAME=ATTRS.ATTRIBUTE_NAME;
3578
3579 IF ATTRS.colnum>l_min_colnum THEN
3580 l_mincol_list(ATTRS.COLNUM) := 'N';
3581 IF has_trx_context(ATTRS.PROCEDURE_NAME) THEN
3582 l_forall_list(ATTRS.COLNUM) := ATTRS.PROCEDURE_NAME || '(H_TX'||l_min_colnum||'(I),NULL, ''' || ATTRS.ATTRIBUTE_NAME || ''',''CONTACTS'',''STAGE'')';
3583 ELSE
3584 l_forall_list(ATTRS.COLNUM) := ATTRS.PROCEDURE_NAME || '(H_TX'||l_min_colnum||'(I),NULL, ''' || ATTRS.ATTRIBUTE_NAME || ''',''CONTACTS'')';
3585 END IF;
3586
3587 ELSE
3588 l_mincol_list(ATTRS.COLNUM) := 'Y';
3589 END IF;
3590
3591 IF ATTRS.CUSTOM_ATTRIBUTE_PROCEDURE IS NULL THEN
3592 IF l_mincol_list(ATTRS.COLNUM) = 'Y' THEN
3593 -----Start of Bug No: 4954701----------
3594 l_attr_name := ATTRS.ATTRIBUTE_NAME;
3595 IF(ATTRS.column_data_type ='D') THEN
3596 l_pp_attr_name := 'TO_CHAR(pp.'||l_attr_name||',''DD-MON-YYYY'') '||l_attr_name;
3597 l_oc_attr_name := 'TO_CHAR(oc.'||l_attr_name||',''DD-MON-YYYY'') '||l_attr_name;
3598 l_attr_name := 'TO_CHAR(r.'||l_attr_name||',''DD-MON-YYYY'') '||l_attr_name;
3599 ELSE
3600 l_pp_attr_name := 'pp.'||l_attr_name;
3601 l_oc_attr_name := 'oc.'||l_attr_name;
3602 l_attr_name := 'r.'||l_attr_name;
3603 END IF;
3604 -----End of Bug No: 4954701------------
3605 IF ATTRS.SOURCE_TABLE='HZ_PERSON_PROFILES' THEN
3606 l_select(idx) := l_pp_attr_name;
3607 ELSIF ATTRS.SOURCE_TABLE='HZ_ORG_CONTACTS' THEN
3608 l_select(idx) := l_oc_attr_name;
3609 ELSIF ATTRS.SOURCE_TABLE='HZ_RELATIONSHIPS' THEN
3610 l_select(idx) := l_attr_name;
3611 END IF;
3612 ELSE
3613 l_select(idx) := 'N';
3614 END IF;
3615
3616 l_custom_list(ATTRS.COLNUM) := 'N';
3617 ELSE
3618 l_select(idx) := 'N';
3619 l_custom_list(ATTRS.COLNUM) := 'N';
3620 IF ATTRS.ATTRIBUTE_NAME = 'CONTACT_NAME' THEN
3621 IF l_mincol_list(ATTRS.COLNUM) = 'Y' THEN
3622 l_select(idx) := 'rtrim(pp.person_first_name || '' '' || pp.person_last_name)';
3623 END IF;
3624 ELSE
3625 IF l_mincol_list(ATTRS.COLNUM) = 'Y' THEN
3626 IF has_context(ATTRS.custom_attribute_procedure) THEN
3627 l_custom_list(ATTRS.COLNUM) := ATTRS.custom_attribute_procedure || '(H_ORG_CONTACT_ID(I),''CONTACTS'','''||ATTRS.ATTRIBUTE_NAME|| ''', ''STAGE'')';
3628 ELSE
3629 l_custom_list(ATTRS.COLNUM) := ATTRS.custom_attribute_procedure || '(H_ORG_CONTACT_ID(I), ''CONTACTS'','''||ATTRS.ATTRIBUTE_NAME|| ''')';
3630 END IF;
3631 END IF;
3632 END IF;
3633 END IF;
3634 idx := idx+1;
3635 -- VJN ADDED CODE FOR TRACKING CONDITION ATTRIBUTES AT THIS ENTITY LEVEL
3636 IF HZ_WORD_CONDITIONS_PKG.is_a_cond_attrib( ATTRS.attribute_id)
3637 THEN
3638 l_cond_attrib_list(ATTRS.COLNUM) := ATTRS.attribute_id ;
3639 END IF;
3640 END LOOP;
3641
3642 IF cur_col_num<255 THEN
3643 FOR I in cur_col_num..255 LOOP
3644 l_mincol_list(I) := 'N';
3645 l_forall_list(I) := 'N';
3646 l_custom_list(I) := 'N';
3647 END LOOP;
3648 END IF;
3649
3650 l(' CURSOR contact_cur IS');
3651 l(' SELECT ');
3652 l(' /*+ cardinality(g 200) use_nl(g r oc pp) */');
3653 l(' oc.ORG_CONTACT_ID , r.OBJECT_ID, r.PARTY_ID, g.PARTY_INDEX,r.SUBJECT_ID, r.status '); -- Bug No: 4299785
3654 FOR I in 1..l_select.COUNT LOOP
3655 IF l_select(I) <> 'N' THEN
3656 l(' ,' || l_select(I));
3657 END IF;
3658 END LOOP;
3659 l(' FROM HZ_DQM_STAGE_GT g, HZ_RELATIONSHIPS r,');
3660 l(' HZ_ORG_CONTACTS oc, HZ_PERSON_PROFILES pp');
3661 l(' WHERE oc.party_relationship_id = r.relationship_id ');
3662 l(' AND r.object_id = g.party_id ');
3663 l(' AND r.subject_id = pp.party_id ');
3664 l(' AND r.SUBJECT_TABLE_NAME = ''HZ_PARTIES''');
3665 l(' AND r.OBJECT_TABLE_NAME = ''HZ_PARTIES''');
3666 l(' AND DIRECTIONAL_FLAG= ''F'' ');
3670 l('');
3667 l(' AND pp.effective_end_date is NULL ');
3668 l(' AND (oc.status is null OR oc.status = ''A'' or oc.status = ''I'')');
3669 l(' AND (r.status is null OR r.status = ''A'' or r.status = ''I'');');
3671 l(' BEGIN');
3672 l(' OPEN contact_cur;');
3673 l(' LOOP');
3674 l(' FETCH contact_cur BULK COLLECT INTO');
3675 l(' H_ORG_CONTACT_ID');
3676 l(' ,H_C_PARTY_ID');
3677 l(' ,H_R_PARTY_ID');
3678 l(' ,H_PARTY_INDEX');
3679 l(' ,H_PERSON_PARTY_ID');
3680 l(' ,H_STATUS');-- Bug No: 4299785
3681 FOR I IN 1..255 LOOP
3682 IF l_forall_list(I) <> 'N' AND l_mincol_list(I) = 'Y' AND
3683 l_custom_list(I) = 'N' THEN
3684 l(' ,H_TX'||I);
3685 END IF;
3686 END LOOP;
3687 l(' LIMIT l_limit;');
3688 l('');
3689 l(' IF contact_cur%NOTFOUND THEN');
3690 l(' l_last_fetch:=TRUE;');
3691 l(' END IF;');
3692
3693 l(' IF H_C_PARTY_ID.COUNT=0 AND l_last_fetch THEN');
3694 l(' EXIT;');
3695 l(' END IF;');
3696
3697 l(' FOR I in H_C_PARTY_ID.FIRST..H_C_PARTY_ID.LAST LOOP');
3698
3699
3700 -- VJN INTRODUCED CODE FOR GENERATING THE POPULATION
3701 -- OF THE GLOBAL CONDITION RECORD
3702 l_idx := l_cond_attrib_list.FIRST ;
3703 IF l_idx IS NOT NULL
3704 THEN
3705 l('----------- SETTING GLOBAL CONDITION RECORD AT THE CONTACT LEVEL ---------');
3706 END IF ;
3707
3708
3709 WHILE l_cond_attrib_list.EXISTS(l_idx)
3710 LOOP
3711
3712 l(' HZ_WORD_CONDITIONS_PKG.set_gbl_condition_rec ('||l_cond_attrib_list(l_idx)||','||'H_TX'||l_idx||'(I));');
3713 l_idx := l_idx+1;
3714 END LOOP;
3715 l(' HZ_TRANS_PKG.next_gen_dqm := ''Y'';');
3716
3717 l('');
3718
3719 FOR I IN 1..255 LOOP
3720 IF l_forall_list(I) <> 'N' THEN
3721 IF l_custom_list(I) <> 'N' AND l_mincol_list(I) = 'Y' THEN
3722 l(' H_TX'||I||'(I):='||l_custom_list(I)||';');
3723 END IF;
3724 END IF;
3725 END LOOP;
3726 FOR I IN 1..255 LOOP
3727 IF l_forall_list(I) <> 'N' THEN
3728 IF l_mincol_list(I) <> 'Y' THEN
3729 l(' H_TX'||I||'(I):='||l_forall_list(I)||';');
3730 END IF;
3731 END IF;
3732 END LOOP;
3733 FOR I IN 1..255 LOOP
3734 IF l_forall_list(I) <> 'N' THEN
3735 IF l_mincol_list(I) = 'Y' THEN
3736 l(' H_TX'||I||'(I):='||l_forall_list(I)||';');
3737 END IF;
3738 END IF;
3739 END LOOP;
3740
3741 l(' END LOOP;');
3742 l(' l_st := 1; ');
3743 l(' l_en := H_C_PARTY_ID.COUNT; ');
3744 l(' LOOP ');
3745 l(' BEGIN ');
3746 l(' FORALL I in l_st..l_en');
3747 l(' INSERT INTO HZ_SHADOW_ST_CONTACTS (');
3748 l(' ORG_CONTACT_ID');
3749 l(' ,PARTY_ID');
3750 l(' ,PERSON_PARTY_ID');
3751 l(' ,STATUS_FLAG');-- Bug No: 4299785
3752 FOR I IN 1..255 LOOP
3753 IF l_forall_list(I) <> 'N' THEN
3754 l(' , TX'||I);
3755 END IF;
3756 END LOOP;
3757 l(' ,QKEY');
3758 l(' ) VALUES (');
3759 l(' H_ORG_CONTACT_ID(I)');
3760 l(' ,H_C_PARTY_ID(I)');
3761 l(' ,H_PERSON_PARTY_ID(I)');
3762 l(' ,H_STATUS(I)'); -- Bug No: 4299785
3763 FOR I IN 1..255 LOOP
3764 IF l_forall_list(I) <> 'N' THEN
3765 l(' , decode(H_TX'||I||'(I),null,H_TX'||I||'(I),H_TX'||I||'(I)||'' '')');
3766 END IF;
3767 END LOOP;
3768 l(' ,'||G_C_QKEY_STR);
3769 l(' );');
3770 l(' EXIT; ');
3771 l(' EXCEPTION WHEN OTHERS THEN ');
3772 l(' l_st:= l_st+SQL%ROWCOUNT+1;');
3773 l(' END; ');
3774 l(' END LOOP; ');
3775 l(' FORALL I in H_C_PARTY_ID.FIRST..H_C_PARTY_ID.LAST ');
3776 l(' INSERT INTO HZ_DQM_STAGE_GT(PARTY_ID,OWNER_ID,ORG_CONTACT_ID,PARTY_INDEX, PERSON_PARTY_ID) ');
3777 l(' SELECT H_C_PARTY_ID(I), H_R_PARTY_ID(I), H_ORG_CONTACT_ID(I), H_PARTY_INDEX(I), H_PERSON_PARTY_ID(I)');
3778 l(' FROM DUAL WHERE H_R_PARTY_ID(I) IS NOT NULL;');
3779 l(' IF l_last_fetch THEN');
3780 l(' EXIT;');
3781 l(' END IF;');
3782 l(' END LOOP;');
3783 l(' CLOSE contact_cur;');
3784 l(' END;');
3785
3786 l(' PROCEDURE sync_single_contact (');
3787 l(' p_org_contact_id NUMBER,');
3788 l(' p_operation VARCHAR2) IS');
3789 l('');
3790 l(' l_tryins BOOLEAN;');
3791 l(' l_tryupd BOOLEAN;');
3792 l(' BEGIN');
3793 l(' SELECT oc.ORG_CONTACT_ID , d.PARTY_ID, r.status '); -- Bug No: 4299785
3794 FOR I in 1..l_select.COUNT LOOP
3795 IF l_select(I) <> 'N' THEN
3796 l(' ,' || l_select(I));
3797 END IF;
3798 END LOOP;
3799 l(' INTO H_ORG_CONTACT_ID(1), H_PARTY_ID(1), H_STATUS(1) '); --Bug No: 4299785
3800 FOR I IN 1..255 LOOP
3801 IF l_forall_list(I) <> 'N' AND l_mincol_list(I) = 'Y' AND
3802 l_custom_list(I) = 'N' THEN
3803 l(' , H_TX'||I||'(1)');
3804 END IF;
3805 END LOOP;
3806
3807 l(' FROM HZ_ORG_CONTACTS oc, HZ_DQM_SYNC_INTERFACE d, ');
3808 l(' HZ_RELATIONSHIPS r, HZ_PERSON_PROFILES pp');
3809 l(' WHERE d.ENTITY = ''CONTACTS'' ');
3810 l(' AND oc.org_contact_id = p_org_contact_id');
3811 l(' AND oc.org_contact_id = d.RECORD_ID');
3812 l(' AND oc.party_relationship_id = r.relationship_id ');
3813 l(' AND r.subject_id = pp.party_id ');
3814 l(' AND r.SUBJECT_TABLE_NAME = ''HZ_PARTIES''');
3815 l(' AND r.OBJECT_TABLE_NAME = ''HZ_PARTIES''');
3816 l(' AND DIRECTIONAL_FLAG= ''F'' ');
3820 l(' AND ROWNUM=1;');
3817 l(' AND pp.effective_end_date is NULL ');
3818 l(' AND (oc.status is null OR oc.status = ''A'' or oc.status = ''I'')');
3819 l(' AND (r.status is null OR r.status = ''A'' or r.status = ''I'')');
3821
3822 -- VJN INTRODUCED CODE FOR GENERATING THE POPULATION
3823 -- OF THE GLOBAL CONDITION RECORD
3824
3825 l_idx := l_cond_attrib_list.FIRST ;
3826 IF l_idx IS NOT NULL
3827 THEN
3828 l('----------- SETTING GLOBAL CONDITION RECORD AT THE CONTACT LEVEL ---------');
3829 END IF ;
3830
3831
3832 WHILE l_cond_attrib_list.EXISTS(l_idx)
3833 LOOP
3834
3835 l(' HZ_WORD_CONDITIONS_PKG.set_gbl_condition_rec ('||l_cond_attrib_list(l_idx)||','||'H_TX'||l_idx||'(1));');
3836 l_idx := l_idx+1;
3837 END LOOP;
3838 l(' HZ_TRANS_PKG.next_gen_dqm := ''Y'';');
3839 FOR I IN 1..255 LOOP
3840 IF l_forall_list(I) <> 'N' THEN
3841 IF l_custom_list(I) <> 'N' AND l_mincol_list(I) = 'Y' THEN
3842 l(' H_TX'||I||'(1):='||replace(l_custom_list(I),'(I)','(1)')||';');
3843 END IF;
3844 END IF;
3845 END LOOP;
3846 FOR I IN 1..255 LOOP
3847 IF l_forall_list(I) <> 'N' THEN
3848 IF l_mincol_list(I) <> 'Y' THEN
3849 l(' H_TX'||I||'(1):='||replace(l_forall_list(I),'(I)','(1)')||';');
3850 END IF;
3851 END IF;
3852 END LOOP;
3853 FOR I IN 1..255 LOOP
3854 IF l_forall_list(I) <> 'N' THEN
3855 IF l_mincol_list(I) = 'Y' THEN
3856 l(' H_TX'||I||'(1):='||replace(l_forall_list(I),'(I)','(1)')||';');
3857 END IF;
3858 END IF;
3859 END LOOP;
3860
3861 l(' l_tryins := FALSE;');
3862 l(' l_tryupd := FALSE;');
3863 l(' IF p_operation=''C'' THEN');
3864 l(' l_tryins:=TRUE;');
3865 l(' ELSE ');
3866 l(' l_tryupd:=TRUE;');
3867 l(' END IF;');
3868 l(' WHILE (l_tryins OR l_tryupd) LOOP');
3869 l(' IF l_tryins THEN');
3870 l(' BEGIN');
3871 l(' l_tryins:=FALSE;');
3872 l(' INSERT INTO HZ_SHADOW_ST_CONTACTS (');
3873 l(' ORG_CONTACT_ID');
3874 l(' ,PARTY_ID');
3875 l(' ,STATUS_FLAG '); -- Bug No: 4299785
3876 FOR I IN 1..255 LOOP
3877 IF l_forall_list(I) <> 'N' THEN
3878 l(' , TX'||I);
3879 END IF;
3880 END LOOP;
3881 l(' ) VALUES (');
3882 l(' H_ORG_CONTACT_ID(1)');
3883 l(' , H_PARTY_ID(1)');
3884 l(' , H_STATUS(1)');-- Bug No: 4299785
3885 FOR I IN 1..255 LOOP
3886 IF l_forall_list(I) <> 'N' THEN
3887 l(' , decode(H_TX'||I||'(1),null,H_TX'||I||'(1),H_TX'||I||'(1)||'' '')');
3888 END IF;
3889 END LOOP;
3890 l(' );');
3891 l(' EXCEPTION');
3892 l(' WHEN DUP_VAL_ON_INDEX THEN');
3893 l(' IF p_operation=''C'' THEN');
3894 l(' l_tryupd:=TRUE;');
3895 l(' END IF;');
3896 l(' END;');
3897 l(' END IF;');
3898 l(' IF l_tryupd THEN');
3899 l(' BEGIN');
3900 l(' l_tryupd:=FALSE;');
3901 l(' UPDATE HZ_SHADOW_ST_CONTACTS SET ');
3902 FOR I IN 1..255 LOOP
3903 IF l_forall_list(I) <> 'N' THEN
3904 IF (is_first) THEN
3905 is_first := false;
3906 l(' TX'||I||'=decode(H_TX'||I||'(1),null,H_TX'||I||'(1),H_TX'||I||'(1)||'' '')');
3907 ELSE
3908 l(' ,TX'||I||'=decode(H_TX'||I||'(1),null,H_TX'||I||'(1),H_TX'||I||'(1)||'' '')');
3909 END IF;
3910 END IF;
3911 END LOOP;
3912 l(' ,STATUS_FLAG = H_STATUS(1) ');-- Bug No: 4299785
3913 l(' WHERE ORG_CONTACT_ID=H_ORG_CONTACT_ID(1);');
3914 l(' IF SQL%ROWCOUNT=0 AND p_operation=''U'' THEN');
3915 l(' l_tryins := TRUE;');
3916 l(' END IF;');
3917 l(' EXCEPTION ');
3918 l(' WHEN NO_DATA_FOUND THEN');
3919 l(' IF p_operation=''U'' THEN');
3920 l(' l_tryins := TRUE;');
3921 l(' END IF;');
3922 l(' END;');
3923 l(' END IF;');
3924 l(' END LOOP;');
3925 l(' UPDATE HZ_SHADOW_ST_PARTIES set');
3926 l(' D_CT = ''SYNC''');
3927 l(' WHERE PARTY_ID = H_PARTY_ID(1);');
3928 l(' END;');
3929 END;
3930
3931
3932 PROCEDURE generate_contact_pt_query_proc IS
3933 cur_col_num NUMBER := 1;
3934
3935 TYPE coltab IS TABLE OF VARCHAR2(2000) INDEX BY BINARY_INTEGER;
3936
3937 l_select coltab;
3938 FIRST BOOLEAN := FALSE;
3939
3940 l_forall_list coltab;
3941 l_custom_list coltab;
3942 l_mincol_list coltab;
3943 l_min_colnum NUMBER;
3944 idx NUMBER :=1;
3945 is_first boolean := true;
3946
3947 -- VJN Introduced for conditional word replacements
3948 l_cond_attrib_list coltab ;
3949 l_idx number ;
3950 l_attr_name varchar2(2000);
3951
3952 BEGIN
3953 l(' PROCEDURE insert_stage_contact_pts IS ');
3954 l(' l_limit NUMBER := ' || g_batch_size || ';');
3955 l(' l_last_fetch BOOLEAN := FALSE;');
3956 l(' l_denorm VARCHAR2(2000);');
3957 l(' l_st number; ');
3958 l(' l_en number; ');
3959
3960 l('');
3961
3962 FOR ATTRS IN (SELECT a.ATTRIBUTE_ID,
3963 a.ATTRIBUTE_NAME,
3964 a.SOURCE_TABLE,
3965 a.CUSTOM_ATTRIBUTE_PROCEDURE,
3966 f.PROCEDURE_NAME,
3967 f.STAGED_ATTRIBUTE_COLUMN,
3968 to_number(substrb(STAGED_ATTRIBUTE_COLUMN, 3)) COLNUM,
3969 nvl(lkp.tag,'C') column_data_type
3970 FROM HZ_TRANS_ATTRIBUTES_VL a, HZ_TRANS_FUNCTIONS_VL f,FND_LOOKUP_VALUES_VL lkp --Bug No: 4954701
3974 AND lkp.LOOKUP_TYPE = 'CONTACT_PT_LOGICAL_ATTRIB_LIST'
3971 WHERE ENTITY_NAME = 'CONTACT_POINTS'
3972 AND nvl(f.ACTIVE_FLAG,'Y') = 'Y'
3973 AND f.ATTRIBUTE_ID = a.ATTRIBUTE_ID
3975 AND lkp.lookup_code = a.ATTRIBUTE_NAME
3976 ORDER BY COLNUM) LOOP
3977 IF cur_col_num<ATTRS.COLNUM THEN
3978 FOR I in cur_col_num..ATTRS.COLNUM-1 LOOP
3979 l_mincol_list(I) := 'N';
3980 l_forall_list(I) := 'N';
3981 END LOOP;
3982 END IF;
3983 cur_col_num:=ATTRS.COLNUM+1;
3984 IF has_trx_context(ATTRS.PROCEDURE_NAME) THEN
3985 l_forall_list(ATTRS.COLNUM) := ATTRS.PROCEDURE_NAME || '(H_TX'||ATTRS.COLNUM||'(I),NULL, ''' || ATTRS.ATTRIBUTE_NAME || ''',''CONTACT_POINTS'',''STAGE'')';
3986 ELSE
3987 l_forall_list(ATTRS.COLNUM) := ATTRS.PROCEDURE_NAME || '(H_TX'||ATTRS.COLNUM||'(I),NULL, ''' || ATTRS.ATTRIBUTE_NAME || ''',''CONTACT_POINTS'')';
3988 END IF;
3989
3990 SELECT min(to_number(substrb(STAGED_ATTRIBUTE_COLUMN, 3))) INTO l_min_colnum
3991 FROM HZ_TRANS_ATTRIBUTES_VL a, HZ_TRANS_FUNCTIONS_VL f
3992 WHERE ENTITY_NAME = 'CONTACT_POINTS'
3993 AND nvl(f.ACTIVE_FLAG,'Y') = 'Y'
3994 AND f.ATTRIBUTE_ID = a.ATTRIBUTE_ID
3995 AND a.ATTRIBUTE_NAME=ATTRS.ATTRIBUTE_NAME;
3996
3997 IF ATTRS.colnum>l_min_colnum THEN
3998 l_mincol_list(ATTRS.COLNUM) := 'N';
3999 IF has_trx_context(ATTRS.PROCEDURE_NAME) THEN
4000 l_forall_list(ATTRS.COLNUM) := ATTRS.PROCEDURE_NAME || '(H_TX'||l_min_colnum||'(I),NULL, ''' || ATTRS.ATTRIBUTE_NAME || ''',''CONTACT_POINTS'',''STAGE'')';
4001 ELSE
4002 l_forall_list(ATTRS.COLNUM) := ATTRS.PROCEDURE_NAME || '(H_TX'||l_min_colnum||'(I),NULL, ''' || ATTRS.ATTRIBUTE_NAME || ''',''CONTACT_POINTS'')';
4003 END IF;
4004
4005 ELSE
4006 l_mincol_list(ATTRS.COLNUM) := 'Y';
4007 END IF;
4008
4009 IF ATTRS.CUSTOM_ATTRIBUTE_PROCEDURE IS NULL THEN
4010 -----Start of Bug No: 4954701----------
4011 l_attr_name := ATTRS.ATTRIBUTE_NAME;
4012 IF(ATTRS.column_data_type ='D') THEN
4013 l_attr_name := 'TO_CHAR(cp.'||l_attr_name||',''DD-MON-YYYY'') '||l_attr_name;
4014 ELSE
4015 l_attr_name := 'cp.'||l_attr_name;
4016 END IF;
4017 -----End of Bug No: 4954701------------
4018 IF l_mincol_list(ATTRS.COLNUM) = 'Y' THEN
4019 l_select(idx) := l_attr_name;
4020 ELSE
4021 l_select(idx) := 'N';
4022 END IF;
4023
4024 l_custom_list(ATTRS.COLNUM) := 'N';
4025 ELSE
4026 l_select(idx) := 'N';
4027 l_custom_list(ATTRS.COLNUM) := 'N';
4028 IF ATTRS.ATTRIBUTE_NAME = 'FLEX_FORMAT_PHONE_NUMBER' THEN
4029 IF l_mincol_list(ATTRS.COLNUM) = 'Y' THEN
4030 l_select(idx) := 'translate(phone_number,''0123456789ABCDEFGHIJKLMNOPQRSTUV'||
4031 'WXYZabcdefghijklmnopqrstuvwxyz!"#$%&()''''*+,-./:;<=>?@[\]^'||
4032 '_`{|}~ '',''0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZABCDEFGHIJKLMNOPQRSTUVWXYZ'') || '' '' || ' ||
4033 'translate(phone_area_code||'' '' || phone_number,''0123456789ABCDEFGHIJKLMNOPQRSTUV'||
4034 'WXYZabcdefghijklmnopqrstuvwxyz!"#$%&()''''*+,-./:;<=>?@[\]^'||
4035 '_`{|}~ '',''0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZABCDEFGHIJKLMNOPQRSTUVWXYZ'') || '' '' || '||
4036 ' translate(phone_country_code|| '' '' || phone_area_code||'' '' || phone_number,''0123456789ABCDEFGHIJKLMNOPQRSTUV'||
4037 'WXYZabcdefghijklmnopqrstuvwxyz!"#$%&()''''*+,-./:;<=>?@[\]^'||
4038 '_`{|}~ '',''0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZABCDEFGHIJKLMNOPQRSTUVWXYZ'')';
4039 END IF;
4040 ELSIF ATTRS.ATTRIBUTE_NAME = 'RAW_PHONE_NUMBER' THEN
4041 IF l_mincol_list(ATTRS.COLNUM) = 'Y' THEN
4042 l_select(idx) := 'translate(phone_country_code|| '' '' || phone_area_code||'' '' || phone_number,''0123456789ABCDEFGHIJKLMNOPQRSTUV'||
4043 'WXYZabcdefghijklmnopqrstuvwxyz!"#$%&()''''*+,-./:;<=>?@[\]^_'||
4044 '`{|}~ '',''0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZABCDEFGHIJKLMNOPQRSTUVWXYZ'')';
4045
4046 END IF;
4047 ELSE
4048 IF l_mincol_list(ATTRS.COLNUM) = 'Y' THEN
4049 IF has_context(ATTRS.custom_attribute_procedure) THEN
4050 l_custom_list(ATTRS.COLNUM) := ATTRS.custom_attribute_procedure || '(H_CONTACT_POINT_ID(I),''CONTACT_POINTS'','''||ATTRS.ATTRIBUTE_NAME|| ''', ''STAGE'')';
4051 ELSE
4052 l_custom_list(ATTRS.COLNUM) := ATTRS.custom_attribute_procedure || '(H_CONTACT_POINT_ID(I), ''CONTACT_POINTS'','''||ATTRS.ATTRIBUTE_NAME|| ''')';
4053 END IF;
4054 END IF;
4055 END IF;
4056 END IF;
4057 idx := idx+1;
4058
4059 -- VJN ADDED CODE FOR TRACKING CONDITION ATTRIBUTES AT THIS ENTITY LEVEL
4060 IF HZ_WORD_CONDITIONS_PKG.is_a_cond_attrib( ATTRS.attribute_id)
4061 THEN
4062 l_cond_attrib_list(ATTRS.COLNUM) := ATTRS.attribute_id ;
4063 END IF;
4064
4065 END LOOP;
4066
4067 IF cur_col_num<255 THEN
4068 FOR I in cur_col_num..255 LOOP
4069 l_mincol_list(I) := 'N';
4070 l_forall_list(I) := 'N';
4071 l_custom_list(I) := 'N';
4072 END LOOP;
4073 END IF;
4074
4075 l(' CURSOR contact_pt_cur IS');
4076 l(' SELECT /*+ cardinality(g 200) use_nl(g cp) */ cp.CONTACT_POINT_ID, g.party_id, g.party_site_id, g.org_contact_id, cp.CONTACT_POINT_TYPE, PARTY_INDEX, g.PERSON_PARTY_ID, cp.status'); -- Bug No: 4299785
4077 FOR I in 1..l_select.COUNT LOOP
4078 IF l_select(I) <> 'N' THEN
4079 l(' ,' || l_select(I));
4080 END IF;
4081 END LOOP;
4082 l(' FROM HZ_DQM_STAGE_GT g,HZ_CONTACT_POINTS cp');
4083 l(' WHERE cp.owner_table_id = g.owner_id ');
4084 l(' AND cp.OWNER_TABLE_NAME = nvl(g.owner_table,''HZ_PARTIES'') ');
4085 l(' AND (cp.status is null OR cp.status = ''A'' or cp.status = ''I''); ');
4086 l('');
4087 l(' BEGIN');
4088 l(' OPEN contact_pt_cur;');
4089 l(' LOOP');
4093 l(' ,H_CPT_PARTY_SITE_ID');
4090 l(' FETCH contact_pt_cur BULK COLLECT INTO');
4091 l(' H_CONTACT_POINT_ID');
4092 l(' ,H_CPT_PARTY_ID');
4094 l(' ,H_CPT_ORG_CONTACT_ID');
4095 l(' ,H_CONTACT_POINT_TYPE');
4096 l(' ,H_PARTY_INDEX');
4097 l(' ,H_PERSON_PARTY_ID');
4098 l(' ,H_STATUS'); -- Bug No: 4299785
4099 FOR I IN 1..255 LOOP
4100 IF l_forall_list(I) <> 'N' AND l_mincol_list(I) = 'Y' AND
4101 l_custom_list(I) = 'N' THEN
4102 l(' ,H_TX'||I);
4103 END IF;
4104 END LOOP;
4105 l(' LIMIT l_limit;');
4106 l('');
4107 l(' IF contact_pt_cur%NOTFOUND THEN');
4108 l(' l_last_fetch:=TRUE;');
4109 l(' END IF;');
4110
4111 l(' IF H_CPT_PARTY_ID.COUNT=0 AND l_last_fetch THEN');
4112 l(' EXIT;');
4113 l(' END IF;');
4114
4115 l(' FOR I in H_CPT_PARTY_ID.FIRST..H_CPT_PARTY_ID.LAST LOOP');
4116
4117 -- VJN INTRODUCED CODE FOR GENERATING THE POPULATION
4118 -- OF THE GLOBAL CONDITION RECORD
4119
4120 l_idx := l_cond_attrib_list.FIRST ;
4121 IF l_idx IS NOT NULL
4122 THEN
4123 l('----------- SETTING GLOBAL CONDITION RECORD AT THE CONTACT POINT LEVEL ---------');
4124 END IF ;
4125
4126 l('');
4127
4128
4129 WHILE l_cond_attrib_list.EXISTS(l_idx)
4130 LOOP
4131
4132 l(' HZ_WORD_CONDITIONS_PKG.set_gbl_condition_rec ('||l_cond_attrib_list(l_idx)||','||'H_TX'||l_idx||'(I));');
4133 l_idx := l_idx+1;
4134 END LOOP;
4135 l(' HZ_TRANS_PKG.next_gen_dqm := ''Y'';');
4136 FOR I IN 1..255 LOOP
4137 IF l_forall_list(I) <> 'N' THEN
4138 IF l_custom_list(I) <> 'N' AND l_mincol_list(I) = 'Y' THEN
4139 l(' H_TX'||I||'(I):='||l_custom_list(I)||';');
4140 END IF;
4141 END IF;
4142 END LOOP;
4143 FOR I IN 1..255 LOOP
4144 IF l_forall_list(I) <> 'N' THEN
4145 IF l_mincol_list(I) <> 'Y' THEN
4146 l(' H_TX'||I||'(I):='||l_forall_list(I)||';');
4147 END IF;
4148 END IF;
4149 END LOOP;
4150 FOR I IN 1..255 LOOP
4151 IF l_forall_list(I) <> 'N' THEN
4152 IF l_mincol_list(I) = 'Y' THEN
4153 l(' H_TX'||I||'(I):='||l_forall_list(I)||';');
4154 END IF;
4155 END IF;
4156 END LOOP;
4157 l(' END LOOP;');
4158 l(' l_st := 1; ');
4159 l(' l_en := H_CPT_PARTY_ID.COUNT; ');
4160 l(' LOOP ');
4161 l(' BEGIN ');
4162 l(' FORALL I in l_st..l_en');
4163 l(' INSERT INTO HZ_SHADOW_ST_CPTS (');
4164 l(' CONTACT_POINT_ID');
4165 l(' ,PARTY_ID');
4166 l(' ,PARTY_SITE_ID');
4167 l(' ,ORG_CONTACT_ID');
4168 l(' ,CONTACT_POINT_TYPE');
4169 l(' ,PERSON_PARTY_ID');
4170 l(' ,STATUS_FLAG');-- Bug No: 4299785
4171 FOR I IN 1..255 LOOP
4172 IF l_forall_list(I) <> 'N' THEN
4173 l(' , TX'||I);
4174 END IF;
4175 END LOOP;
4176 l(' ,QKEY');
4177 l(' ) VALUES (');
4178 l(' H_CONTACT_POINT_ID(I)');
4179 l(' ,H_CPT_PARTY_ID(I)');
4180 l(' ,H_CPT_PARTY_SITE_ID(I)');
4181 l(' ,H_CPT_ORG_CONTACT_ID(I)');
4182 l(' ,H_CONTACT_POINT_TYPE(I)');
4183 l(' ,H_PERSON_PARTY_ID(I)');
4184 l(' ,H_STATUS(I)');-- Bug No: 4299785
4185 FOR I IN 1..255 LOOP
4186 IF l_forall_list(I) <> 'N' THEN
4187 l(' , decode(H_TX'||I||'(I),null,H_TX'||I||'(I),H_TX'||I||'(I)||'' '')');
4188 END IF;
4189 END LOOP;
4190 l(' ,'||G_CPT_QKEY_STR);
4191 l(' );');
4192 l(' EXIT; ');
4193 l(' EXCEPTION WHEN OTHERS THEN ');
4194 l(' l_st:= l_st+SQL%ROWCOUNT+1;');
4195 l(' END; ');
4196 l(' END LOOP; ');
4197 l(' IF l_last_fetch THEN');
4198 l(' EXIT;');
4199 l(' END IF;');
4200 l(' END LOOP;');
4201 l(' CLOSE contact_pt_cur;');
4202 l(' END;');
4203
4204 l(' PROCEDURE sync_single_contact_point (');
4205 l(' p_contact_point_id NUMBER,');
4206 l(' p_operation VARCHAR2) IS');
4207 l('');
4208 l(' l_tryins BOOLEAN;');
4209 l(' l_tryupd BOOLEAN;');
4210 l(' BEGIN');
4211 l(' SELECT cp.CONTACT_POINT_ID, d.PARTY_ID, d.PARTY_SITE_ID, d.ORG_CONTACT_ID, cp.CONTACT_POINT_TYPE, cp.STATUS '); -- Bug No: 4299785
4212 FOR I in 1..l_select.COUNT LOOP
4213 IF l_select(I) <> 'N' THEN
4214 l(' ,' || l_select(I));
4215 END IF;
4216 END LOOP;
4217 l(' INTO H_CONTACT_POINT_ID(1),H_PARTY_ID(1), H_PARTY_SITE_ID(1),H_ORG_CONTACT_ID(1),H_CONTACT_POINT_TYPE(1), H_STATUS(1) '); -- Bug No: 4299785
4218 FOR I IN 1..255 LOOP
4219 IF l_forall_list(I) <> 'N' AND l_mincol_list(I) = 'Y' AND
4220 l_custom_list(I) = 'N' THEN
4221 l(' , H_TX'||I||'(1)');
4222 END IF;
4223 END LOOP;
4224 l(' FROM HZ_CONTACT_POINTS cp, HZ_DQM_SYNC_INTERFACE d ');
4225 l(' WHERE d.ENTITY = ''CONTACT_POINTS'' ');
4226 l(' AND cp.contact_point_id = p_contact_point_id ');
4227 l(' AND cp.contact_point_id = d.RECORD_ID ');
4228 l(' AND (cp.status is null OR cp.status = ''A'' or cp.status = ''I'') and rownum = 1 ; ');
4229 -- VJN INTRODUCED CODE FOR GENERATING THE POPULATION
4230 -- OF THE GLOBAL CONDITION RECORD
4231
4232 l_idx := l_cond_attrib_list.FIRST ;
4233 IF l_idx IS NOT NULL
4234 THEN
4235 l('----------- SETTING GLOBAL CONDITION RECORD AT THE CONTACT POINT LEVEL ---------');
4236 END IF ;
4237
4238
4242 l(' HZ_WORD_CONDITIONS_PKG.set_gbl_condition_rec ('||l_cond_attrib_list(l_idx)||','||'H_TX'||l_idx||'(1));');
4239 WHILE l_cond_attrib_list.EXISTS(l_idx)
4240 LOOP
4241
4243 l_idx := l_idx+1;
4244 END LOOP;
4245 l(' HZ_TRANS_PKG.next_gen_dqm := ''Y'';');
4246
4247 FOR I IN 1..255 LOOP
4248 IF l_forall_list(I) <> 'N' THEN
4249 IF l_custom_list(I) <> 'N' AND l_mincol_list(I) = 'Y' THEN
4250 l(' H_TX'||I||'(1):='||replace(l_custom_list(I),'(I)','(1)')||';');
4251 END IF;
4252 END IF;
4253 END LOOP;
4254 FOR I IN 1..255 LOOP
4255 IF l_forall_list(I) <> 'N' THEN
4256 IF l_mincol_list(I) <> 'Y' THEN
4257 l(' H_TX'||I||'(1):='||replace(l_forall_list(I),'(I)','(1)')||';');
4258 END IF;
4259 END IF;
4260 END LOOP;
4261 FOR I IN 1..255 LOOP
4262 IF l_forall_list(I) <> 'N' THEN
4263 IF l_mincol_list(I) = 'Y' THEN
4264 l(' H_TX'||I||'(1):='||replace(l_forall_list(I),'(I)','(1)')||';');
4265 END IF;
4266 END IF;
4267 END LOOP;
4268 l(' l_tryins := FALSE;');
4269 l(' l_tryupd := FALSE;');
4270 l(' IF p_operation=''C'' THEN');
4271 l(' l_tryins:=TRUE;');
4272 l(' ELSE ');
4273 l(' l_tryupd:=TRUE;');
4274 l(' END IF;');
4275 l(' WHILE (l_tryins OR l_tryupd) LOOP');
4276 l(' IF l_tryins THEN');
4277 l(' BEGIN');
4278 l(' l_tryins:=FALSE;');
4279 l(' INSERT INTO HZ_SHADOW_ST_CPTS (');
4280 l(' CONTACT_POINT_ID');
4281 l(' ,PARTY_ID');
4282 l(' ,PARTY_SITE_ID');
4283 l(' ,ORG_CONTACT_ID');
4284 l(' ,CONTACT_POINT_TYPE');
4285 l(' ,PERSON_PARTY_ID');
4286 l(' ,STATUS_FLAG');-- Bug No: 4299785
4287 FOR I IN 1..255 LOOP
4288 IF l_forall_list(I) <> 'N' THEN
4289 l(' , TX'||I);
4290 END IF;
4291 END LOOP;
4292 l(' ) VALUES (');
4293 l(' H_CONTACT_POINT_ID(1)');
4294 l(' ,H_PARTY_ID(1)');
4295 l(' ,H_PARTY_SITE_ID(1)');
4296 l(' ,H_ORG_CONTACT_ID(1)');
4297 l(' ,H_CONTACT_POINT_TYPE(1)');
4298 l(' ,H_PERSON_PARTY_ID(1)');
4299 l(' ,H_STATUS(1)'); -- Bug No: 4299785
4300 FOR I IN 1..255 LOOP
4301 IF l_forall_list(I) <> 'N' THEN
4302 l(' , decode(H_TX'||I||'(1),null,H_TX'||I||'(1),H_TX'||I||'(1)||'' '')');
4303 END IF;
4304 END LOOP;
4305 l(' );');
4306 l(' EXCEPTION');
4307 l(' WHEN DUP_VAL_ON_INDEX THEN');
4308 l(' IF p_operation=''C'' THEN');
4309 l(' l_tryupd:=TRUE;');
4310 l(' END IF;');
4311 l(' END;');
4312 l(' END IF;');
4313 l(' IF l_tryupd THEN');
4314 l(' BEGIN');
4315 l(' l_tryupd:=FALSE;');
4316 l(' UPDATE HZ_SHADOW_ST_CPTS SET ');
4317 FOR I IN 1..255 LOOP
4318 IF l_forall_list(I) <> 'N' THEN
4319 IF (is_first) THEN
4320 is_first := false;
4321 l(' TX'||I||'=decode(H_TX'||I||'(1),null,H_TX'||I||'(1),H_TX'||I||'(1)||'' '')');
4322 ELSE
4323 l(' ,TX'||I||'=decode(H_TX'||I||'(1),null,H_TX'||I||'(1),H_TX'||I||'(1)||'' '')');
4324 END IF;
4325 END IF;
4326 END LOOP;
4327 l(' ,STATUS_FLAG = H_STATUS(1) ');-- Bug No: 4299785
4328 l(' WHERE CONTACT_POINT_ID=H_CONTACT_POINT_ID(1);');
4329 l(' IF SQL%ROWCOUNT=0 AND p_operation=''U'' THEN');
4330 l(' l_tryins := TRUE;');
4331 l(' END IF;');
4332 l(' EXCEPTION ');
4333 l(' WHEN NO_DATA_FOUND THEN');
4334 l(' IF p_operation=''U'' THEN');
4335 l(' l_tryins := TRUE;');
4336 l(' END IF;');
4337 l(' END;');
4338 l(' END IF;');
4339 l(' END LOOP;');
4340 l(' UPDATE HZ_SHADOW_ST_PARTIES set');
4341 l(' D_CPT = ''SYNC''');
4342 l(' WHERE PARTY_ID = H_PARTY_ID(1);');
4343 l(' END;');
4344 END;
4345
4346
4347
4348 PROCEDURE create_btree_indexes (p_entity VARCHAR2)
4349 IS
4350 l_index_owner VARCHAR2(255);
4351
4352 CURSOR indexes_reqd IS
4353 SELECT decode(a.entity_name,'PARTY','HZ_SHADOW_ST_PARTIES',
4354 'PARTY_SITES','HZ_SHADOW_ST_PSITES','CONTACTS','HZ_SHADOW_ST_CONTACTS',
4355 'CONTACT_POINTS','HZ_SHADOW_ST_CPTS')||'_N0'||substrb(staged_attribute_column,3) index_name,
4356 decode(a.entity_name,'PARTY','HZ_SHADOW_ST_PARTIES',
4357 'PARTY_SITES','HZ_SHADOW_ST_PSITES','CONTACTS','HZ_SHADOW_ST_CONTACTS',
4358 'CONTACT_POINTS','HZ_SHADOW_ST_CPTS') table_name,
4359 decode(a.entity_name,'PARTY','HZ_SRCH_PARTIES',
4360 'PARTY_SITES','HZ_SRCH_PSITES','CONTACTS','HZ_SRCH_CONTACTS',
4361 'CONTACT_POINTS','HZ_SRCH_CPTS')||'_N0'||substrb(staged_attribute_column,3) srch_index_name,
4362 decode(a.entity_name,'PARTY','HZ_SRCH_PARTIES',
4363 'PARTY_SITES','HZ_SRCH_PSITES','CONTACTS','HZ_SRCH_CONTACTS',
4364 'CONTACT_POINTS','HZ_SRCH_CPTS') srch_table_name,
4365 f.staged_attribute_column column_name
4366 FROM hz_trans_attributes_vl a, hz_trans_functions_vl f
4367 WHERE f.attribute_id = a.attribute_id
4368 AND f.index_required_flag = 'Y'
4369 AND a.entity_name = p_entity;
4370
4371 CURSOR check_index(cp_index_name VARCHAR2,cp_table_name VARCHAR2) IS
4372 SELECT 1 FROM sys.all_indexes
4373 WHERE INDEX_NAME=cp_index_name
4374 AND TABLE_NAME=cp_table_name and owner = l_index_owner;
4375
4376 l_index_name VARCHAR2(255);
4380 l_column_name VARCHAR2(255);
4377 l_table_name VARCHAR2(255);
4378 l_srch_index_name VARCHAR2(255);
4379 l_srch_table_name VARCHAR2(255);
4381
4382 ar_index_tbsp VARCHAR2(255);
4383 l_storage_params VARCHAR2(2000);
4384 tmp NUMBER;
4385
4386 l_status VARCHAR2(255);
4387 l_temp VARCHAR2(255);
4388
4389 l_bool BOOLEAN;
4390
4391 BEGIN
4392
4393 l_bool := fnd_installation.GET_APP_INFO('AR',l_status,l_temp,l_index_owner);
4394
4395 select index_tablespace
4396 into ar_index_tbsp
4397 from fnd_product_installations
4398 where application_id = '222';
4399
4400 l_storage_params := 'LOGGING STORAGE (INITIAL 4K NEXT 1M MINEXTENTS 1 '||
4401 'MAXEXTENTS unlimited PCTINCREASE 0 FREELIST GROUPS 4 '||
4402 'FREELISTS 4) PCTFREE 10 INITRANS 4 MAXTRANS 255 '||
4403 'COMPUTE STATISTICS TABLESPACE '||ar_index_tbsp;
4404
4405 OPEN indexes_reqd;
4406 LOOP
4407 FETCH indexes_reqd INTO l_index_name, l_table_name,l_srch_index_name, l_srch_table_name, l_column_name;
4408 EXIT WHEN indexes_reqd%NOTFOUND;
4409
4410 OPEN check_index(l_index_name,l_table_name);
4411 FETCH check_index INTO tmp;
4412 IF check_index%NOTFOUND THEN
4413 EXECUTE IMMEDIATE 'CREATE INDEX '||l_index_owner||'.'||l_index_name||' ON '||l_table_name||'('||l_column_name||') '||
4414 l_storage_params||' LOCAL '; -- for shadow create local indexes
4415 END IF;
4416 CLOSE check_index;
4417
4418 OPEN check_index(l_srch_index_name,l_srch_table_name);
4419 FETCH check_index INTO tmp;
4420 IF check_index%NOTFOUND THEN
4421 EXECUTE IMMEDIATE 'CREATE INDEX '||l_index_owner||'.'||l_srch_index_name||' ON '||l_srch_table_name||'('||l_column_name||') '||
4422 l_storage_params;
4423 END IF;
4424 CLOSE check_index;
4425 END LOOP;
4426 CLOSE indexes_reqd;
4427 END;
4428
4429 FUNCTION has_trx_context(proc VARCHAR2) RETURN BOOLEAN IS
4430
4431 l_sql VARCHAR2(255);
4432 l_entity VARCHAR2(255);
4433 l_procedure VARCHAR2(255);
4434 l_attribute VARCHAR2(255);
4435 c NUMBER;
4436 n NUMBER;
4437 l_custom BOOLEAN;
4438
4439 BEGIN
4440 c := dbms_sql.open_cursor;
4441 l_sql := 'select ' || proc ||
4442 '(:attrval,:lang,:attr,:entity,:ctx) from dual';
4443 dbms_sql.parse(c,l_sql,2);
4444 DBMS_SQL.BIND_VARIABLE(c,':attrval','x');
4445 DBMS_SQL.BIND_VARIABLE(c,':lang','x');
4446 DBMS_SQL.BIND_VARIABLE(c,':attr','x');
4447 DBMS_SQL.BIND_VARIABLE(c,':entity','x');
4448 DBMS_SQL.BIND_VARIABLE(c,':ctx','x');
4449 n:=DBMS_SQL.execute(c);
4450 dbms_sql.close_cursor(c);
4451 RETURN TRUE;
4452 EXCEPTION
4453 WHEN OTHERS THEN
4454 dbms_sql.close_cursor(c);
4455 RETURN FALSE;
4456 END;
4457
4458 FUNCTION has_context(proc VARCHAR2) RETURN BOOLEAN IS
4459
4460 l_sql VARCHAR2(255);
4461 l_entity VARCHAR2(255);
4462 l_procedure VARCHAR2(255);
4463 l_attribute VARCHAR2(255);
4464 c NUMBER;
4465 n NUMBER;
4466 l_custom BOOLEAN;
4467
4468 BEGIN
4469 c := dbms_sql.open_cursor;
4470 l_sql := 'select ' || proc ||
4471 '(:record_id,:entity,:attr,:ctx) from dual';
4472 dbms_sql.parse(c,l_sql,2);
4473 DBMS_SQL.BIND_VARIABLE(c,':record_id','x');
4474 DBMS_SQL.BIND_VARIABLE(c,':entity','x');
4475 DBMS_SQL.BIND_VARIABLE(c,':attr','x');
4476 DBMS_SQL.BIND_VARIABLE(c,':ctx','x');
4477 n:=DBMS_SQL.execute(c);
4478 dbms_sql.close_cursor(c);
4479 RETURN TRUE;
4480 EXCEPTION
4481 WHEN OTHERS THEN
4482 dbms_sql.close_cursor(c);
4483 RETURN FALSE;
4484 END;
4485
4486
4487 PROCEDURE verify_all_procs IS
4488
4489 l_sql VARCHAR2(255);
4490 l_entity VARCHAR2(255);
4491 l_procedure VARCHAR2(255);
4492 l_attribute VARCHAR2(255);
4493 l_trans_name VARCHAR2(255);
4494 c NUMBER;
4495 l_custom BOOLEAN;
4496 BEGIN
4497 log('---------------------------------------------------');
4498 log('Calling verify_all_procs');
4499 FOR FUNCS IN (SELECT PROCEDURE_NAME, a.ENTITY_NAME, a.ATTRIBUTE_NAME, f.TRANSFORMATION_NAME
4500 FROM HZ_TRANS_FUNCTIONS_VL f, HZ_TRANS_ATTRIBUTES_VL a
4501 WHERE a.ATTRIBUTE_ID = f.ATTRIBUTE_ID
4502 AND nvl(f.ACTIVE_FLAG,'Y') = 'Y' )
4503 LOOP
4504 BEGIN
4505 l_custom := FALSE;
4506 l_entity := FUNCS.ENTITY_NAME;
4507 l_attribute := FUNCS.ATTRIBUTE_NAME;
4508 l_procedure := FUNCS.PROCEDURE_NAME;
4509 l_trans_name := FUNCS.TRANSFORMATION_NAME;
4510 c := dbms_sql.open_cursor;
4511 l_sql := 'select ' || FUNCS.PROCEDURE_NAME ||
4512 '(:attrval,:lang,:attr,:entity) from dual';
4513 dbms_sql.parse(c,l_sql,2);
4514 dbms_sql.close_cursor(c);
4515 EXCEPTION WHEN OTHERS THEN
4516 FND_MESSAGE.SET_NAME('AR', 'HZ_TRANS_PROC_ERROR1');
4517 FND_MESSAGE.SET_TOKEN('PROC' ,l_procedure);
4518 FND_MESSAGE.SET_TOKEN('ENTITY' ,l_entity);
4519 FND_MESSAGE.SET_TOKEN('ATTRIBUTE' ,l_attribute);
4520 FND_MESSAGE.SET_TOKEN('TRANS' ,l_trans_name);
4521 FND_MSG_PUB.ADD;
4522 END;
4523 END LOOP;
4524 FOR FUNCS IN (SELECT custom_attribute_procedure, ENTITY_NAME, ATTRIBUTE_NAME
4525 FROM HZ_TRANS_ATTRIBUTES_VL a
4526 WHERE source_table = 'CUSTOM' OR
4527 custom_attribute_procedure is NOT NULL
4528 AND EXISTS (select 1 from HZ_TRANS_FUNCTIONS_VL f
4529 WHERE f.attribute_id = a.attribute_id
4530 AND nvl(f.ACTIVE_FLAG,'Y') = 'Y'))
4531 LOOP
4532 BEGIN
4533 l_custom := TRUE;
4534 l_entity := FUNCS.ENTITY_NAME;
4535 l_attribute := FUNCS.ATTRIBUTE_NAME;
4536 l_procedure := FUNCS.custom_attribute_procedure;
4537 c := dbms_sql.open_cursor;
4538 l_sql := 'select ' || FUNCS.custom_attribute_procedure ||
4539 '(:record_id,:entity,:attr) from dual';
4540 dbms_sql.parse(c,l_sql,2);
4541 dbms_sql.close_cursor(c);
4542 EXCEPTION WHEN OTHERS THEN
4543 FND_MESSAGE.SET_NAME('AR', 'HZ_CUSTOM_PROC_ERROR1');
4544 FND_MESSAGE.SET_TOKEN('PROC' ,l_procedure);
4545 FND_MESSAGE.SET_TOKEN('ENTITY' ,l_entity);
4546 FND_MESSAGE.SET_TOKEN('ATTRIBUTE' ,l_attribute);
4547 FND_MSG_PUB.ADD;
4548 END;
4549 END LOOP;
4550 EXCEPTION
4551 WHEN OTHERS THEN
4552 dbms_sql.close_cursor(c);
4553 RAISE FND_API.G_EXC_ERROR;
4554 END ;
4555
4556
4557
4558 /**
4559 * Procedure to write a message to the out file
4560 **/
4561 PROCEDURE out(
4562 message IN VARCHAR2,
4563 newline IN BOOLEAN DEFAULT TRUE) IS
4564 BEGIN
4565 IF message = 'NEWLINE' THEN
4566 FND_FILE.NEW_LINE(FND_FILE.OUTPUT, 1);
4567 ELSIF (newline) THEN
4568 FND_FILE.put_line(fnd_file.output,message);
4569 ELSE
4570 FND_FILE.put(fnd_file.output,message);
4571 END IF;
4572 END out;
4573
4574 /**
4575 * Procedure to write a message to the log file
4576 **/
4577 PROCEDURE log(
4578 message IN VARCHAR2,
4579 newline IN BOOLEAN DEFAULT TRUE) IS
4580 BEGIN
4581
4582 IF message = 'NEWLINE' THEN
4583 FND_FILE.NEW_LINE(FND_FILE.LOG, 1);
4584 ELSIF (newline) THEN
4585 FND_FILE.put_line(fnd_file.log,message);
4586 ELSE
4587 FND_FILE.put(fnd_file.log,message);
4588 END IF;
4589 END log;
4590
4591 /**
4592 * Procedure to write a message to the out and log files
4593 **/
4594 PROCEDURE outandlog(
4595 message IN VARCHAR2,
4596 newline IN BOOLEAN DEFAULT TRUE) IS
4597 BEGIN
4598 out(message, newline);
4599 log(message, newline);
4600 END outandlog;
4601
4602 /**
4603 * Function to fetch messages of the stack and log the error
4604 * Also returns the error
4605 **/
4606 FUNCTION logerror(SQLERRM VARCHAR2 DEFAULT NULL)
4607 RETURN VARCHAR2 IS
4608
4609 l_msg_data VARCHAR2(2000);
4610 BEGIN
4611 FND_MSG_PUB.Reset;
4612
4613 FOR I IN 1..FND_MSG_PUB.Count_Msg LOOP
4614 l_msg_data := l_msg_data || FND_MSG_PUB.Get(p_encoded => FND_API.G_FALSE );
4615 END LOOP;
4616 IF (SQLERRM IS NOT NULL) THEN
4617 l_msg_data := l_msg_data || SQLERRM;
4618 END IF;
4619 log(l_msg_data);
4620 RETURN l_msg_data;
4621 END logerror;
4622
4623 FUNCTION wait_for_request(
4624 p_request_id NUMBER) RETURN VARCHAR2 IS
4625
4626 uphase VARCHAR2(255);
4627 dphase VARCHAR2(255);
4628 ustatus VARCHAR2(255);
4629 dstatus VARCHAR2(255);
4630 message VARCHAR2(32000);
4631
4632 l_bool BOOLEAN;
4633
4634 BEGIN
4635 l_bool := FND_CONCURRENT.wait_for_request(p_request_id,
4636 60, 144000, uphase, ustatus, dphase, dstatus, message);
4637
4638 IF dphase <> 'COMPLETE' and dstatus <> 'NORMAL' THEN
4639 return 'ERROR';
4640 ELSE
4641 return 'SUCCESS';
4642 END IF;
4643 END wait_for_request;
4644
4645 BEGIN
4646 g_schema_name := hz_utility_v2pub.get_appsschemaname;
4647
4648 END HZ_PARTY_STAGE_SHADOW;