eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.

APPS.HZ_DQM_DIAGNOSTICS_XML SQL Statements

The following lines contain the word 'select', 'insert', 'update' or 'delete':

Line: 57


qryCtx := dbms_xmlgen.newContext('select operation, number_of_workers,
decode(step,''STEP1'',''ORGANIZATION'',''STEP2'',''PERSON'',''STEP3'',''ALL OTHER PARTY TYPES'',''STEP4'',''CONTACTS'',''STEP5'',''PARTY SITES'',''STEP6'',''CONTACT POINTS'',step) steps,
worker_number,
to_char(start_time,''DD-MON-YY HH24:MI:SS'') start_time, to_char(end_time,''DD-MON-YY HH24:MI:SS'') end_time from hz_dqm_stage_log
where operation in (''STAGE_NEW_TRANSFORMATIONS'',''STAGE_ALL_DATA'',''CREATE_INDEXES'')
group by operation,step,worker_number,number_of_workers,start_time,end_time');

Line: 64


q1Ctx := dbms_xmlgen.newcontext('select vl.match_rule_id,vl.rule_name,vl.active_flag,nvl(vl.match_rule_type,''SINGLE'') match_rule_type,
ar.meaning rule_purpose, nvl(vl.automerge_flag,''N'') automerge_flag from hz_match_rules_vl vl, ar_lookups ar
where ar.lookup_type=''HZ_DQM_MATCH_RULE_PURPOSE'' and ar.lookup_code=vl.rule_purpose order by vl.creation_date');

Line: 68


queryCtx := dbms_xmlgen.newcontext('select attr.attribute_id,attr.attribute_name,attr.entity_name,attr.user_defined_attribute_name,
attr.custom_attribute_procedure,attr.source_table, nvl(attr.denorm_flag,''N'') denorm_flag,
trn.function_id, trn.transformation_name,trn.procedure_name,trn.staged_attribute_column,trn.staged_attribute_table,
nvl(trn.staged_flag,''N'') staged_flag,nvl(trn.active_flag,''Y'') active_flag,nvl(trn.index_required_flag,''Y'') index_required_flag,
nvl(trn.primary_flag,''Y'') primary_flag
from HZ_TRANS_ATTRIBUTES_VL attr,hz_trans_functions_vl trn
where attr.attribute_id =trn.attribute_id
group by attr.attribute_id,attr.attribute_name,attr.entity_name,attr.user_defined_attribute_name,
attr.custom_attribute_procedure,attr.source_table, attr.denorm_flag,
trn.function_id, trn.transformation_name,trn.procedure_name,trn.staged_attribute_column,trn.staged_attribute_table,
trn.staged_flag,trn.active_flag,trn.index_required_flag,trn.primary_flag
order by attribute_id asc');

Line: 81


select (max(end_time)-min(start_time)) into l_time from hz_dqm_stage_log
where operation='STAGE_ALL_DATA';

Line: 84


select (max(end_time)-min(start_time)) into l_index_time from hz_dqm_stage_log where operation = 'CREATE_INDEXES'
order by start_time;

Line: 112


     select count(party_id) into l_entity_cnt from hz_parties where party_type = 'ORGANIZATION';

Line: 114


     select count(party_id) into l_staged_cnt from hz_staged_parties where TX36 = 'ORGANIZATION ';

Line: 116


	select count(distinct(party_id)) into l_sync_cnt from hz_dqm_sync_interface where entity='PARTY'
	and party_id in (select party_id from hz_parties where party_type = 'ORGANIZATION' );

Line: 120


  	 select count(party_id) into l_entity_cnt from hz_parties where party_type = 'PERSON';

Line: 122


     select count(party_id) into l_staged_cnt from hz_staged_parties where TX36 = 'PERSON ' ;

Line: 124


	select count(distinct(party_id)) into l_sync_cnt from hz_dqm_sync_interface where entity='PARTY'
	and party_id in (select party_id from hz_parties where party_type = 'PERSON');

Line: 130


  	 select count(party_id) into l_entity_cnt from hz_parties where party_type in ('ORGANIZATION','PERSON');

Line: 132


  	 select count(party_id) into l_staged_cnt from hz_staged_parties where TX36 in ('ORGANIZATION ','PERSON ');

Line: 134


	select count(distinct(party_id)) into l_sync_cnt from hz_dqm_sync_interface where entity='PARTY'
	and party_id in (select party_id from hz_parties where party_type in ('ORGANIZATION','PERSON'));

Line: 138


     select count(party_site_id) into l_entity_cnt from hz_party_sites where party_id in
	 (select party_id from hz_parties where party_type in ('ORGANIZATION','PERSON'));

Line: 141


     select count(party_site_id) into l_staged_cnt from hz_staged_party_sites where org_contact_id IS NULL and party_id in
     (select party_id from hz_staged_parties where TX36 in ('ORGANIZATION ','PERSON ')) ;

Line: 144


	select count(distinct(party_id)) into l_sync_cnt from hz_dqm_sync_interface where entity='PARTY_SITES'
	and party_id in (select party_id from hz_parties where party_type in ('ORGANIZATION','PERSON'));

Line: 150


	select count(*) into l_entity_cnt from hz_org_contacts where party_relationship_id in
	(select relationship_id from hz_relationships where subject_table_name='HZ_PARTIES'
		and object_table_name='HZ_PARTIES' and subject_type in ('ORGANIZATION','PERSON')
		and object_type in ('ORGANIZATION','PERSON'));

Line: 156


	select count(*) into l_staged_cnt from hz_staged_contacts where org_contact_id in
	(select org_contact_id  from hz_org_contacts where party_relationship_id in
	(select relationship_id from hz_relationships where subject_table_name='HZ_PARTIES'
		and object_table_name='HZ_PARTIES' and subject_type in ('ORGANIZATION','PERSON')
		and object_type in ('ORGANIZATION','PERSON')));

Line: 163


	select count(distinct(party_id)) into l_sync_cnt from hz_dqm_sync_interface where entity='CONTACTS'
	and party_id in (select party_id from hz_parties where party_type in ('ORGANIZATION','PERSON'));

Line: 169


 	select count(contact_point_id) into l_entity_cnt from hz_contact_points where owner_table_name in ('HZ_PARTIES','HZ_PARTY_SITES');

Line: 172


	select count(contact_point_id) into l_staged_cnt from hz_staged_contact_points where contact_point_id in
    (select contact_point_id  from hz_contact_points where owner_table_name in ('HZ_PARTIES','HZ_PARTY_SITES'));

Line: 175


	select count(distinct(party_id)) into l_sync_cnt from hz_dqm_sync_interface where entity='CONTACT_POINTS'
	and party_id in (select party_id from hz_parties where party_type in ('ORGANIZATION','PERSON'));

Line: 182


    select count(*) into l_schedule_cnt from fnd_concurrent_requests where concurrent_program_id=44464
	and phase_code='P';

Line: 230


	qryCtx := dbms_xmlgen.newContext('select operation,to_char(max(end_time)-min(start_time)) time_diff from hz_dqm_stage_log
								  where (start_time is not null OR end_time is not null)
								  and operation = ''STAGE_NEW_TRANSFORMATIONS'' group by operation ');

Line: 244


 	qryCtx := dbms_xmlgen.newContext('select operation,to_char(max(end_time)-min(start_time)) time_diff from hz_dqm_stage_log
								  where (start_time is not null OR end_time is not null)
								  and operation = ''STAGE_ALL_DATA'' group by operation ');

Line: 257


	qryCtx := dbms_xmlgen.newContext('select operation,to_char(max(end_time)-min(start_time)) time_diff from hz_dqm_stage_log
								  where (start_time is not null OR end_time is not null)
								  and operation = ''CREATE_INDEXES'' group by operation ');

Line: 273


	qryCtx := dbms_xmlgen.newContext('select ''CREATE INTERMEDIA INDEXES'', step table_name from hz_dqm_stage_log where operation = ''CREATE_INDEXES'' order by step asc');

Line: 285


	qryCtx := dbms_xmlgen.newContext(' select ind.index_name,ind.table_name,ind.status,ind.index_type, substr(ind.parameters,instr(ind.parameters,''memory'')+6) memory, '||
					' (st.end_time-st.start_time) total_time,a.pnd_syncs,b.index_errors from sys.all_indexes ind, hz_dqm_stage_log st, '||
					' (select ''HZ_STAGE_PARTIES_T1'' index_name, count(*) pnd_syncs from ctxsys.ctx_pending where PND_INDEX_OWNER=''AR'' and PND_INDEX_NAME like ''HZ_STAGE_PARTIES_T1'' '||
					' union select ''HZ_STAGE_PARTY_SITES_T1'' index_name, count(*) pnd_syncs from ctxsys.ctx_pending where PND_INDEX_OWNER=''AR'' and PND_INDEX_NAME like ''HZ_STAGE_PARTY_SITES_T1'' '||
					' union select ''HZ_STAGE_CONTACT_T1'' index_name, count(*) pnd_syncs from ctxsys.ctx_pending where PND_INDEX_OWNER=''AR'' and PND_INDEX_NAME like ''HZ_STAGE_CONTACT_T1'' '||
					' union select ''HZ_STAGE_CPT_T1'' index_name, count(*) pnd_syncs from ctxsys.ctx_pending where PND_INDEX_OWNER=''AR'' and PND_INDEX_NAME like ''HZ_STAGE_CPT_T1'' '||
					' ) a, (select ''HZ_STAGE_PARTIES_T1'' index_name, count(*) index_errors from ctxsys.ctx_index_errors where err_index_name like ''HZ_STAGE_PARTIES_T1'' '||
					' union select ''HZ_STAGE_PARTY_SITES_T1'' index_name, count(*) index_errors from ctxsys.ctx_index_errors where err_index_name like ''HZ_STAGE_PARTY_SITES_T1'' '||
					' union select ''HZ_STAGE_CONTACT_T1'' index_name, count(*) index_errors from ctxsys.ctx_index_errors where err_index_name like ''HZ_STAGE_CONTACT_T1'' '||
					' union select ''HZ_STAGE_CPT_T1'' index_name, count(*) index_errors from ctxsys.ctx_index_errors where err_index_name like ''HZ_STAGE_CPT_T1'' '||
					' ) b where ind.index_name like ''HZ_STAGE%T1'' '||
					' and ind.table_name in(''HZ_STAGED_PARTIES'',''HZ_STAGED_PARTY_SITES'',''HZ_STAGED_CONTACTS'',''HZ_STAGED_CONTACT_POINTS'') '||
					' and st.operation=''CREATE_INDEXES'' and st.step in (''HZ_PARTIES'',''HZ_PARTY_SITES'',''HZ_ORG_CONTACTS'',''HZ_CONTACT_POINTS'') '||
					' and (decode(ind.table_name,''HZ_STAGED_PARTIES'',''HZ_PARTIES'') = st.step '||
    				' OR decode(ind.table_name,''HZ_STAGED_PARTY_SITES'',''HZ_PARTY_SITES'') = st.step '||
    				' OR decode(ind.table_name,''HZ_STAGED_CONTACTS'',''HZ_ORG_CONTACTS'') = st.step '||
    				' OR decode(ind.table_name,''HZ_STAGED_CONTACT_POINTS'',''HZ_CONTACT_POINTS'') = st.step) '||
					' and a.index_name = b.index_name '||
					' and a.index_name = ind.index_name ');    */

Line: 305


	qryCtx := dbms_xmlgen.newContext(' SELECT I.INDEX_NAME, I.table_name, I.status, I.index_type, substr(I.parameters, instr(I.parameters, ''memory'')+6) memory, '||
	' (st.end_time-st.start_time) total_time, NVL(P.PND_CNT,0) pnd_syncs, NVL(E.ERR_CNT,0) index_errors '||
	' FROM  DBA_INDEXES I, '||
 	' (SELECT   u.name pnd_index_owner , i.idx_name pnd_index_name, COUNT(*) PND_CNT '||
    ' from ctxsys.dr$pending dr,ctxsys.dr$index i, sys.user$ u '||
    ' where I.idx_owner# = u.user# and dr.pnd_pid = 0 and dr.pnd_cid = I.idx_id '||
    ' GROUP BY u.name, i.idx_name ) P, '||
    ' (SELECT  err_index_owner, err_index_name, COUNT(*) ERR_CNT '||
    ' FROM CTXSYS.CTX_INDEX_ERRORS '||
    ' GROUP BY err_index_owner,err_index_name ) E, '||
  	' hz_dqm_stage_log st '||
	' WHERE I.INDEX_NAME IN (''HZ_STAGE_PARTIES_T1'',''HZ_STAGE_PARTY_SITES_T1'', '||
    ' ''HZ_STAGE_CONTACT_T1'',''HZ_STAGE_CPT_T1'') '||
	' AND   I.INDEX_NAME = P.pnd_index_name(+) '||
	' AND   I.OWNER = P.pnd_index_owner(+) '||
	' AND   I.INDEX_NAME = E.err_index_name(+) '||
	' AND   I.OWNER = E.err_index_owner(+) '||
	' AND   st.operation=''CREATE_INDEXES'' '||
	' AND (decode(I.table_name, ''HZ_STAGED_PARTIES'', ''HZ_PARTIES'') =  st.step '||
    ' OR decode(I.table_name, ''HZ_STAGED_PARTY_SITES'', ''HZ_PARTY_SITES'' ) = st.step '||
    ' OR decode(I.table_name, ''HZ_STAGED_CONTACTS'', ''HZ_ORG_CONTACTS'') = st.step '||
    ' OR decode(I.table_name, ''HZ_STAGED_CONTACT_POINTS'', ''HZ_CONTACT_POINTS'') =  st.step) ');

Line: 339


	qryCtx := dbms_xmlgen.newContext('select * from ctxsys.ctx_pending where PND_INDEX_OWNER=''AR'' and PND_INDEX_NAME like ''HZ%STAGE%T1''');

Line: 351


	qryCtx := dbms_xmlgen.newContext('select * from ctxsys.ctx_index_errors where err_index_name like ''HZ%STAGE%T1''');

Line: 363


    	qryCtx := dbms_xmlgen.newContext('select col.* from user_synonyms syn, dba_ind_columns col where col.table_owner=''AR'' and
		              col.table_name in (''HZ_STAGED_PARTIES'',''HZ_STAGED_PARTY_SITES'',''HZ_STAGED_CONTACTS'',''HZ_STAGED_CONTACT_POINTS'') and
					  col.index_owner = syn.table_owner and col.table_name = syn.synonym_name');

Line: 379


	qryCtx := dbms_xmlgen.newContext('select ''HZ_DQM_ENABLE_REALTIME_SYNC'' sync_profile, meaning sync_type from ar_lookups where lookup_type =  ''HZ_DQM_SYNC_VALUES'' and
									lookup_code in (select nvl(FND_PROFILE.VALUE(''HZ_DQM_ENABLE_REALTIME_SYNC''),''Y'') from dual)');

Line: 392


    qryCtx := dbms_xmlgen.newContext('select COMPONENT_STATUS,COMPONENT_STATUS_INFO from FND_SVC_COMPONENTS SC where SC.COMPONENT_TYPE = ''WF_AGENT_LISTENER'' and component_name = ''Workflow Deferred Agent Listener''');

Line: 404


	qryCtx := dbms_xmlgen.newContext('select ''PARTY'' entity,''Processing'' stage_status, nvl(sum(count(1)),0) SYNC_COUNT from hz_dqm_sync_interface '||
		 ' where entity=''PARTY'' and staged_flag=''P'' group by entity,staged_flag '||
		 ' UNION '||
		 ' select ''PARTY'' entity,''Pending'' stage_status, nvl(sum(count(1)),0) SYNC_COUNT from hz_dqm_sync_interface '||
		 ' where entity=''PARTY'' and staged_flag=''N'' group by entity,staged_flag '||
		 ' UNION '||
		 ' select ''PARTY'' entity,''Staged but not Indexed'' stage_status, nvl(sum(count(1)),0) SYNC_COUNT from hz_dqm_sync_interface '||
		 ' where entity=''PARTY'' and staged_flag=''Y'' group by entity,staged_flag '||
		 ' UNION '||
		 ' select ''PARTY'' entity,''Error'' stage_status, nvl(sum(count(1)),0) SYNC_COUNT from hz_dqm_sync_interface '||
		 ' where entity=''PARTY'' and staged_flag=''E'' group by entity,staged_flag '||
		 ' UNION '||
		 ' select ''PARTY_SITES'' entity,''Processing'' stage_status, nvl(sum(count(1)),0) SYNC_COUNT from hz_dqm_sync_interface '||
		 ' where entity=''PARTY_SITES'' and staged_flag=''P'' group by entity,staged_flag '||
		 ' UNION '||
		 ' select ''PARTY_SITES'' entity,''Pending'' stage_status, nvl(sum(count(1)),0) SYNC_COUNT from hz_dqm_sync_interface '||
		 ' where entity=''PARTY_SITES'' and staged_flag=''N'' group by entity,staged_flag '||
		 ' UNION '||
		 ' select ''PARTY_SITES'' entity,''Staged but not Indexed'' stage_status, nvl(sum(count(1)),0) SYNC_COUNT from hz_dqm_sync_interface '||
		 ' where entity=''PARTY_SITES'' and staged_flag=''Y'' group by entity,staged_flag '||
		 ' UNION '||
		 ' select ''PARTY_SITES'' entity,''Error'' stage_status, nvl(sum(count(1)),0) SYNC_COUNT from hz_dqm_sync_interface '||
		 ' where entity=''PARTY_SITES'' and staged_flag=''E'' group by entity,staged_flag '||
		 ' UNION '||
		 ' select ''CONTACTS'' entity,''Processing'' stage_status, nvl(sum(count(1)),0) SYNC_COUNT from hz_dqm_sync_interface '||
		 ' where entity=''CONTACTS'' and staged_flag=''P'' group by entity,staged_flag '||
		 ' UNION '||
		 ' select ''CONTACTS'' entity,''Pending'' stage_status, nvl(sum(count(1)),0) SYNC_COUNT from hz_dqm_sync_interface '||
		 ' where entity=''CONTACTS'' and staged_flag=''N'' group by entity,staged_flag '||
		 ' UNION '||
		 ' select ''CONTACTS'' entity,''Staged but not Indexed'' stage_status, nvl(sum(count(1)),0) SYNC_COUNT from hz_dqm_sync_interface '||
		 ' where entity=''CONTACTS'' and staged_flag=''Y'' group by entity,staged_flag '||
		 ' UNION '||
		 ' select ''CONTACTS'' entity,''Error'' stage_status, nvl(sum(count(1)),0) SYNC_COUNT from hz_dqm_sync_interface '||
		 ' where entity=''CONTACTS'' and staged_flag=''E'' group by entity,staged_flag '||
		 ' UNION '||
		 ' select ''CONTACT_POINTS'' entity,''Processing'' stage_status, nvl(sum(count(1)),0) SYNC_COUNT from hz_dqm_sync_interface '||
		 ' where entity=''CONTACT_POINTS'' and staged_flag=''P'' group by entity,staged_flag '||
		 ' UNION '||
		 ' select ''CONTACT_POINTS'' entity,''Pending'' stage_status, nvl(sum(count(1)),0) SYNC_COUNT from hz_dqm_sync_interface '||
		 ' where entity=''CONTACT_POINTS'' and staged_flag=''N'' group by entity,staged_flag '||
		 ' UNION '||
		 ' select ''CONTACT_POINTS'' entity,''Staged but not Indexed'' stage_status, nvl(sum(count(1)),0) SYNC_COUNT from hz_dqm_sync_interface '||
		 ' where entity=''CONTACT_POINTS'' and staged_flag=''Y'' group by entity,staged_flag '||
		 ' UNION '||
		 ' select ''CONTACT_POINTS'' entity,''Error'' stage_status, nvl(sum(count(1)),0) SYNC_COUNT from hz_dqm_sync_interface '||
		 ' where entity=''CONTACT_POINTS'' and staged_flag=''E'' group by entity,staged_flag ');

Line: 461


	qryCtx := dbms_xmlgen.newContext('select party_id,record_id,entity,decode(operation,''U'',''Update'',''C'',''Create'',operation) operation,''Error'' staged_flag ,org_contact_id,party_site_id,error_data
										from hz_dqm_sync_interface where staged_flag=''E''
										group by entity,entity,operation,party_id,record_id,staged_flag,org_contact_id,party_site_id,error_data ');

Line: 475


	qryCtx := dbms_xmlgen.newContext('select request_id,last_update_date,request_date, phase_code,status_code,to_char(requested_start_date,''DD-MON-YY HH24:MI:SS'') requested_start_date,concurrent_program_id
								from FND_CONCURRENT_REQUESTS where status_code=''Q'' and phase_code=''P'' and program_application_id=222
								and concurrent_program_id in(select concurrent_program_id from fnd_concurrent_programs where concurrent_program_name=''ARHDQSYN'')');

Line: 489


	qryCtx := dbms_xmlgen.newContext('select a.profile_option_id, b.user_profile_option_name,b.description,a.profile_option_value,
						a.level_id,a.level_context,a.last_update_date, a.last_updated_by from
						(select val.application_id, val.profile_option_id,''SITE'' level_id, null level_context,
						 val.last_update_date,val.last_updated_by, val.profile_option_value
						from fnd_profile_option_values val where val.level_id=10001
					UNION
						select val.application_id, val.profile_option_id,''Application'' level_id, appl.application_name level_context,
 						val.last_update_date,val.last_updated_by, val.profile_option_value
						from fnd_profile_option_values val,fnd_application_tl appl
						where appl.application_id = val.level_value and appl.language=''US'' and val.level_id=10002
					UNION
						select val.application_id, val.profile_option_id,''Responsibility'' level_id, resp.responsibility_name level_context,
						val.last_update_date,val.last_updated_by, val.profile_option_value
						from fnd_profile_option_values val,fnd_responsibility_tl resp
						where resp.responsibility_id = val.level_value and resp.language=''US'' and val.level_id=10003
					UNION
						select val.application_id, val.profile_option_id,''User'' level_id, usr.user_name level_context,
 						val.last_update_date,val.last_updated_by, val.profile_option_value
						from fnd_profile_option_values val,fnd_user usr
						where usr.user_id = val.level_value and val.level_id=10004
					UNION
						select val.application_id, val.profile_option_id,decode(val.level_id,10005,''Server'',10006,''Organization'') level_id,
						null level_context,val.last_update_date,val.last_updated_by, val.profile_option_value
						from fnd_profile_option_values val where val.level_id in (10005,10006)) a,
					(select tl.user_profile_option_name,op.profile_option_id,tl.description
					from fnd_profile_options op,fnd_profile_options_tl tl
					where tl.profile_option_name = op.profile_option_name
					and tl.language = ''US'' and op.profile_option_id in
			  		(select profile_option_id from Fnd_Profile_Cat_Options where category_name in
			 		(''HZ_DQM_DEPLOYMENT'',''HZ_DL_DEPLOYMENT'',''HZ_DL_IMPORT_SETUP'',''HZ_DL_MAPPING_SETUP'',''HZ_DL_SETUP''))) b
					where a.profile_option_id=b.profile_option_id	order by a.profile_option_id asc ');

Line: 601


                  SELECT blocks - empty_blocks
                  FROM sys.dba_tables
                  WHERE table_name = t_name and owner = l_own1;

Line: 604


   CURSOR  c_db_block_size is  SELECT value
                  FROM v$parameter
                  WHERE name = 'db_block_size' ;