[Home] [Help]
PACKAGE BODY: APPS.HZ_DQM_DIAGNOSTICS_XML
Source
1 PACKAGE BODY HZ_DQM_DIAGNOSTICS_XML AS
2 /*$Header: ARHDXMLB.pls 120.4.12020000.3 2012/11/29 09:03:38 amstephe ship $ */
3
4 PROCEDURE DQM_SETUP_OVERVIEW_XML IS
5 qryCtx DBMS_XMLGEN.ctxHandle;
6 --qCtx DBMS_XMLGEN.ctxHandle;
7 queryCtx DBMS_XMLGEN.ctxHandle;
8 q1Ctx DBMS_XMLGEN.ctxHandle;
9
10 result CLOB;
11 result1 CLOB;
12 result2 CLOB;
13
14
15 l_xml_header varchar2(5000);
16 l_xml_header_length number;
17 l_result_length number;
18 l_xml_query VARCHAR2(32767);
19 l_new_line VARCHAR2(1);
20 l_close_tag VARCHAR2(100);
21 l_rows_processed NUMBER;
22 l_result CLOB;
23 l_attrib_result CLOB;
24 l_trans_result CLOB;
25 l_mrule_result CLOB;
26 l_resultOffset number;
27 l_trans_resultOffset number;
28 l_attrib_resultOffset number;
29 l_mrule_resultOffset number;
30 l_clob_size NUMBER;
31 --l_offset NUMBER;
32 l_offset INTEGER;
33 l_chunk_size INTEGER;
34 l_chunk VARCHAR2(32767);
35 l_time NUMBER;
36 l_st_parties_size NUMBER;
37 l_st_psites_size NUMBER;
38 l_st_pcnts_size NUMBER;
39 l_st_pcnpts_size NUMBER;
40 l_total_size NUMBER;
41 l_total_parties NUMBER;
42 l_entity_cnt NUMBER;
43 l_staged_cnt NUMBER;
44 l_sync_cnt NUMBER;
45 l_index_time NUMBER;
46 l_schedule_cnt NUMBER;
47
48 l_status VARCHAR2(255);
49 l_owner VARCHAR2(255);
50 l_temp VARCHAR2(255);
51 l_bloc_result BLOB;
52 l_bloc_subset RAW(32767);
53 l_new_length number;
54 l_xml_query VARCHAR2(5000);
55
56 BEGIN
57 qryCtx := dbms_xmlgen.newContext('select operation, number_of_workers,
58 decode(step,''STEP1'',''ORGANIZATION'',''STEP2'',''PERSON'',''STEP3'',''ALL OTHER PARTY TYPES'',''STEP4'',''CONTACTS'',''STEP5'',''PARTY SITES'',''STEP6'',''CONTACT POINTS'',step) steps,
59 worker_number,
60 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
61 where operation in (''STAGE_NEW_TRANSFORMATIONS'',''STAGE_ALL_DATA'',''CREATE_INDEXES'')
62 group by operation,step,worker_number,number_of_workers,start_time,end_time');
63
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,
65 ar.meaning rule_purpose, nvl(vl.automerge_flag,''N'') automerge_flag from hz_match_rules_vl vl, ar_lookups ar
66 where ar.lookup_type=''HZ_DQM_MATCH_RULE_PURPOSE'' and ar.lookup_code=vl.rule_purpose order by vl.creation_date');
67
68 queryCtx := dbms_xmlgen.newcontext('select attr.attribute_id,attr.attribute_name,attr.entity_name,attr.user_defined_attribute_name,
69 attr.custom_attribute_procedure,attr.source_table, nvl(attr.denorm_flag,''N'') denorm_flag,
70 trn.function_id, trn.transformation_name,trn.procedure_name,trn.staged_attribute_column,trn.staged_attribute_table,
71 nvl(trn.staged_flag,''N'') staged_flag,nvl(trn.active_flag,''Y'') active_flag,nvl(trn.index_required_flag,''Y'') index_required_flag,
72 nvl(trn.primary_flag,''Y'') primary_flag
73 from HZ_TRANS_ATTRIBUTES_VL attr,hz_trans_functions_vl trn
74 where attr.attribute_id =trn.attribute_id
75 group by attr.attribute_id,attr.attribute_name,attr.entity_name,attr.user_defined_attribute_name,
76 attr.custom_attribute_procedure,attr.source_table, attr.denorm_flag,
77 trn.function_id, trn.transformation_name,trn.procedure_name,trn.staged_attribute_column,trn.staged_attribute_table,
78 trn.staged_flag,trn.active_flag,trn.index_required_flag,trn.primary_flag
79 order by attribute_id asc');
80
81 select (max(end_time)-min(start_time)) into l_time from hz_dqm_stage_log
82 where operation='STAGE_ALL_DATA';
83
84 select (max(end_time)-min(start_time)) into l_index_time from hz_dqm_stage_log where operation = 'CREATE_INDEXES'
85 order by start_time;
86
87
88 -- set the row header to be EMPLOYEE
89 DBMS_XMLGEN.setRowTag(qryCtx, 'DQM Stage From LOG');
90 --DBMS_XMLGEN.setRowTag(qCtx, 'DQM Transformations');
91 DBMS_XMLGEN.setRowTag(queryCtx, 'DQM Transformation Attributes');
92 DBMS_XMLGEN.setRowTag(q1Ctx, 'Match Rules');
93
94 -- now get the result
95 l_result := DBMS_XMLGEN.getXML(qryCtx);
96 --l_trans_result := DBMS_XMLGEN.getXML(qCtx);
97 l_attrib_result := DBMS_XMLGEN.getXML(queryCtx);
98 l_mrule_result := DBMS_XMLGEN.getXML(q1Ctx);
99
100 l_new_line := '
101 ';
102 l_xml_header := '<?xml version="1.0" encoding="UTF-8"?>';
103 l_xml_header := l_xml_header ||l_new_line||'<HZTESTXML>';
104 l_xml_header := l_xml_header ||l_new_line||'<TOTAL_TIME>'||l_time||'</TOTAL_TIME>';
105 l_st_parties_size := GET_TABLE_SIZE('HZ_STAGED_PARTIES');
106 l_st_psites_size := GET_TABLE_SIZE('HZ_STAGED_PARTY_SITES');
107 l_st_pcnts_size := GET_TABLE_SIZE('HZ_STAGED_CONTACTS');
108 l_st_pcnpts_size := GET_TABLE_SIZE('HZ_STAGED_CONTACT_POINTS');
109 l_total_size := l_st_parties_size+l_st_psites_size+l_st_pcnts_size+l_st_pcnpts_size;
110 l_xml_header := l_xml_header ||l_new_line||'<STAGING_DISC_SPACE>'||l_total_size||'</STAGING_DISC_SPACE>';
111
112 select count(party_id) into l_entity_cnt from hz_parties where party_type = 'ORGANIZATION';
113 l_xml_header := l_xml_header ||l_new_line||'<PARTY_ORGS>'||to_char(l_entity_cnt)||'</PARTY_ORGS>';
114 select count(party_id) into l_staged_cnt from hz_staged_parties where TX36 = 'ORGANIZATION ';
115 l_xml_header := l_xml_header ||l_new_line||'<ORG_PARTIES_IN_STAGE>'||to_char(l_staged_cnt)||'</ORG_PARTIES_IN_STAGE>';
116 select count(distinct(party_id)) into l_sync_cnt from hz_dqm_sync_interface where entity='PARTY'
117 and party_id in (select party_id from hz_parties where party_type = 'ORGANIZATION' );
118 l_xml_header := l_xml_header ||l_new_line||'<ORG_PARTIES_TO_STAGE>'||to_char(l_sync_cnt)||'</ORG_PARTIES_TO_STAGE>';
119
120 select count(party_id) into l_entity_cnt from hz_parties where party_type = 'PERSON';
121 l_xml_header := l_xml_header ||l_new_line||'<PARTY_PERS>'||to_char(l_entity_cnt)||'</PARTY_PERS>';
122 select count(party_id) into l_staged_cnt from hz_staged_parties where TX36 = 'PERSON ' ;
123 l_xml_header := l_xml_header ||l_new_line||'<PER_PARTIES_IN_STAGE>'||to_char(l_staged_cnt)||'</PER_PARTIES_IN_STAGE>';
124 select count(distinct(party_id)) into l_sync_cnt from hz_dqm_sync_interface where entity='PARTY'
125 and party_id in (select party_id from hz_parties where party_type = 'PERSON');
126 l_xml_header := l_xml_header ||l_new_line||'<PER_PARTIES_TO_STAGE>'||to_char(l_sync_cnt)||'</PER_PARTIES_TO_STAGE>';
127 l_xml_header := l_xml_header ||l_new_line||'<PARTY_STAGE_DISK_SPACE>'||to_char(l_st_parties_size)||'</PARTY_STAGE_DISK_SPACE>';
128 l_xml_header := l_xml_header ||l_new_line||'<PARTY_DISK_SPACE>'||GET_TABLE_SIZE('HZ_PARTIES')||'</PARTY_DISK_SPACE>';
129
130 select count(party_id) into l_entity_cnt from hz_parties where party_type in ('ORGANIZATION','PERSON');
131 l_xml_header := l_xml_header ||l_new_line||'<TOTAL_PARTIES_IN_TCA>'||to_char(l_entity_cnt)||'</TOTAL_PARTIES_IN_TCA>';
132 select count(party_id) into l_staged_cnt from hz_staged_parties where TX36 in ('ORGANIZATION ','PERSON ');
133 l_xml_header := l_xml_header ||l_new_line||'<TOTAL_PARTIES_IN_STAGE>'||to_char(l_staged_cnt)||'</TOTAL_PARTIES_IN_STAGE>';
134 select count(distinct(party_id)) into l_sync_cnt from hz_dqm_sync_interface where entity='PARTY'
135 and party_id in (select party_id from hz_parties where party_type in ('ORGANIZATION','PERSON'));
136 l_xml_header := l_xml_header ||l_new_line||'<TOTAL_PARTIES_TO_SYNC>'||to_char(l_sync_cnt)||'</TOTAL_PARTIES_TO_SYNC>';
137
138 select count(party_site_id) into l_entity_cnt from hz_party_sites where party_id in
139 (select party_id from hz_parties where party_type in ('ORGANIZATION','PERSON'));
140 l_xml_header := l_xml_header ||l_new_line||'<PARTY_SITE_ENTITY>'||to_char(l_entity_cnt)||'</PARTY_SITE_ENTITY>';
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
142 (select party_id from hz_staged_parties where TX36 in ('ORGANIZATION ','PERSON ')) ;
143 l_xml_header := l_xml_header ||l_new_line||'<PARTY_SITES_IN_STAGE>'||to_char(l_staged_cnt)||'</PARTY_SITES_IN_STAGE>';
144 select count(distinct(party_id)) into l_sync_cnt from hz_dqm_sync_interface where entity='PARTY_SITES'
145 and party_id in (select party_id from hz_parties where party_type in ('ORGANIZATION','PERSON'));
146 l_xml_header := l_xml_header ||l_new_line||'<PSITES_TO_SYNC>'||to_char(l_sync_cnt)||'</PSITES_TO_SYNC>';
147 l_xml_header := l_xml_header ||l_new_line||'<PARTY_SITE_STAGE_DISK_SPACE>'||to_char(l_st_psites_size)||'</PARTY_SITE_STAGE_DISK_SPACE>';
148 l_xml_header := l_xml_header ||l_new_line||'<PARTY_SITE_DISK_SPACE>'||GET_TABLE_SIZE('HZ_PARTY_SITES')||'</PARTY_SITE_DISK_SPACE>';
149
150 select count(*) into l_entity_cnt from hz_org_contacts where party_relationship_id in
151 (select relationship_id from hz_relationships where subject_table_name='HZ_PARTIES'
152 and object_table_name='HZ_PARTIES' and subject_type in ('ORGANIZATION','PERSON')
153 and object_type in ('ORGANIZATION','PERSON'));
154 l_xml_header := l_xml_header ||l_new_line||'<PARTY_CONTACTS>'||to_char(l_entity_cnt)||'</PARTY_CONTACTS>';
155
156 select count(*) into l_staged_cnt from hz_staged_contacts where org_contact_id in
157 (select org_contact_id from hz_org_contacts where party_relationship_id in
158 (select relationship_id from hz_relationships where subject_table_name='HZ_PARTIES'
159 and object_table_name='HZ_PARTIES' and subject_type in ('ORGANIZATION','PERSON')
160 and object_type in ('ORGANIZATION','PERSON')));
161
162 l_xml_header := l_xml_header ||l_new_line||'<PARTY_CONTACTS_IN_STAGE>'||to_char(l_staged_cnt)||'</PARTY_CONTACTS_IN_STAGE>';
163 select count(distinct(party_id)) into l_sync_cnt from hz_dqm_sync_interface where entity='CONTACTS'
164 and party_id in (select party_id from hz_parties where party_type in ('ORGANIZATION','PERSON'));
165 l_xml_header := l_xml_header ||l_new_line||'<PCONTACTS_TO_SYNC>'||to_char(l_sync_cnt)||'</PCONTACTS_TO_SYNC>';
166 l_xml_header := l_xml_header ||l_new_line||'<PARTY_CONTACTS_STAGE_DISK_SPACE>'||to_char(l_st_pcnts_size)||'</PARTY_CONTACTS_STAGE_DISK_SPACE>';
167 l_xml_header := l_xml_header ||l_new_line||'<PARTY_CONTACTS_DISK_SPACE>'||GET_TABLE_SIZE('HZ_ORG_CONTACTS')||'</PARTY_CONTACTS_DISK_SPACE>';
168
169 select count(contact_point_id) into l_entity_cnt from hz_contact_points where owner_table_name in ('HZ_PARTIES','HZ_PARTY_SITES');
170 l_xml_header := l_xml_header ||l_new_line||'<PARTY_CONTACT_POINT>'||to_char(l_entity_cnt)||'</PARTY_CONTACT_POINT>';
171
172 select count(contact_point_id) into l_staged_cnt from hz_staged_contact_points where contact_point_id in
173 (select contact_point_id from hz_contact_points where owner_table_name in ('HZ_PARTIES','HZ_PARTY_SITES'));
174 l_xml_header := l_xml_header ||l_new_line||'<PARTY_CNTPTS_IN_STAGE>'||to_char(l_staged_cnt)||'</PARTY_CNTPTS_IN_STAGE>';
175 select count(distinct(party_id)) into l_sync_cnt from hz_dqm_sync_interface where entity='CONTACT_POINTS'
176 and party_id in (select party_id from hz_parties where party_type in ('ORGANIZATION','PERSON'));
177 l_xml_header := l_xml_header ||l_new_line||'<CNTPNTS_TO_SYNC>'||to_char(l_sync_cnt)||'</CNTPNTS_TO_SYNC>';
178 l_xml_header := l_xml_header ||l_new_line||'<PARTY_CNTPNTS_STAGE_DISK_SPACE>'||to_char(l_st_pcnpts_size)||'</PARTY_CNTPNTS_STAGE_DISK_SPACE>';
179 l_xml_header := l_xml_header || l_new_line||'<PARTY_CNTPNTS_DISK_SPACE>'||to_char(GET_TABLE_SIZE('HZ_CONTACT_POINTS'))||'</PARTY_CNTPNTS_DISK_SPACE>';
180 l_xml_header := l_xml_header ||l_new_line||'<TOTAL_INDEX_TIME>'||l_index_time||'</TOTAL_INDEX_TIME>';
181
182 select count(*) into l_schedule_cnt from fnd_concurrent_requests where concurrent_program_id=44464
183 and phase_code='P';
184
185 if(l_schedule_cnt>0) then
186 l_xml_header := l_xml_header ||l_new_line||'<SCHEDULED_SYNC>Yes</SCHEDULED_SYNC>';
187 else
188 l_xml_header := l_xml_header ||l_new_line||'<SCHEDULED_SYNC>No</SCHEDULED_SYNC>';
189 end if;
190
191 l_xml_header_length := length(l_xml_header);
192 dbms_lob.createtemporary(result,FALSE,DBMS_LOB.CALL);
193 dbms_lob.open(result,dbms_lob.lob_readwrite);
194 dbms_lob.writeAppend(result, length(l_xml_header), l_xml_header);
195 --dbms_lob.write(result,l_xml_header_length,1,l_xml_header);
196
197 --DQM STAGING LOG INFORMATION
198
199 l_rows_processed := DBMS_XMLGEN.getNumRowsProcessed(qryCtx);
200 IF l_rows_processed <> 0 THEN
201 l_resultOffset := DBMS_LOB.INSTR(l_result,'>');
202 l_result_length := dbms_lob.getlength(result);
203 dbms_lob.copy(result,l_result,dbms_lob.getlength(l_result)-l_resultOffset,
204 l_result_length,l_resultOffset);
205 END IF;
206
207
208 -- DQM ATTRIBUTE DETAILS
209
210 l_rows_processed := DBMS_XMLGEN.getNumRowsProcessed(queryCtx);
211 IF l_rows_processed <> 0 THEN
212 l_result_length := dbms_lob.getlength(result);
213 l_attrib_resultOffset := DBMS_LOB.INSTR(l_attrib_result ,'>');
214 dbms_lob.copy(result,l_attrib_result,dbms_lob.getlength(l_attrib_result)-l_attrib_resultOffset,
215 l_result_length,l_attrib_resultOffset);
216 END IF;
217
218 -- DQM MATCH RULES DETAILS
219
220 l_rows_processed := DBMS_XMLGEN.getNumRowsProcessed(q1Ctx);
221 IF l_rows_processed <> 0 THEN
222 l_result_length := dbms_lob.getlength(result);
223 l_mrule_resultOffset := DBMS_LOB.INSTR(l_mrule_result ,'>');
224 dbms_lob.copy(result,l_mrule_result,dbms_lob.getlength(l_mrule_result)-l_mrule_resultOffset,
225 l_result_length,l_mrule_resultOffset);
226 END IF;
227
228 --Time for each operation in Staging Log Table
229
230 qryCtx := dbms_xmlgen.newContext('select operation,to_char(max(end_time)-min(start_time)) time_diff from hz_dqm_stage_log
231 where (start_time is not null OR end_time is not null)
232 and operation = ''STAGE_NEW_TRANSFORMATIONS'' group by operation ');
233 DBMS_XMLGEN.setRowTag(qryCtx, 'Time for STAGE_NEW_TRANSFORMATIONS in Staging Log Table');
234 l_result := DBMS_XMLGEN.getXML(qryCtx);
235 l_rows_processed := DBMS_XMLGEN.getNumRowsProcessed(qryCtx);
236 IF l_rows_processed <> 0 THEN
237 l_result_length := dbms_lob.getlength(result);
238 l_resultOffset := DBMS_LOB.INSTR(l_result ,'>');
239 dbms_lob.copy(result,l_result,dbms_lob.getlength(l_result)-l_resultOffset,
240 l_result_length,l_resultOffset);
241 END IF;
242
243
244 qryCtx := dbms_xmlgen.newContext('select operation,to_char(max(end_time)-min(start_time)) time_diff from hz_dqm_stage_log
245 where (start_time is not null OR end_time is not null)
246 and operation = ''STAGE_ALL_DATA'' group by operation ');
250 IF l_rows_processed <> 0 THEN
247 DBMS_XMLGEN.setRowTag(qryCtx, 'Time for STAGE_ALL_DATA in Staging Log Table');
248 l_result := DBMS_XMLGEN.getXML(qryCtx);
249 l_rows_processed := DBMS_XMLGEN.getNumRowsProcessed(qryCtx);
251 l_result_length := dbms_lob.getlength(result);
252 l_resultOffset := DBMS_LOB.INSTR(l_result ,'>');
253 dbms_lob.copy(result,l_result,dbms_lob.getlength(l_result)-l_resultOffset,
254 l_result_length,l_resultOffset);
255 END IF;
256
257 qryCtx := dbms_xmlgen.newContext('select operation,to_char(max(end_time)-min(start_time)) time_diff from hz_dqm_stage_log
258 where (start_time is not null OR end_time is not null)
259 and operation = ''CREATE_INDEXES'' group by operation ');
260 DBMS_XMLGEN.setRowTag(qryCtx, 'Time for CREATE_INDEXES in Staging Log Table');
261 l_result := DBMS_XMLGEN.getXML(qryCtx);
262 l_rows_processed := DBMS_XMLGEN.getNumRowsProcessed(qryCtx);
263 IF l_rows_processed <> 0 THEN
264 l_result_length := dbms_lob.getlength(result);
265 l_resultOffset := DBMS_LOB.INSTR(l_result ,'>');
266 dbms_lob.copy(result,l_result,dbms_lob.getlength(l_result)-l_resultOffset,
267 l_result_length,l_resultOffset);
268 END IF;
269
270
271 /* INDEX DETAILS */
272
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');
274 DBMS_XMLGEN.setRowTag(qryCtx, 'Time to build intermedia index by entity');
275 l_result := DBMS_XMLGEN.getXML(qryCtx);
276 l_rows_processed := DBMS_XMLGEN.getNumRowsProcessed(qryCtx);
277 IF l_rows_processed <> 0 THEN
278 l_result_length := dbms_lob.getlength(result);
279 l_resultOffset := DBMS_LOB.INSTR(l_result ,'>');
280 dbms_lob.copy(result,l_result,dbms_lob.getlength(l_result)-l_resultOffset,
281 l_result_length,l_resultOffset);
282 END IF;
283
284 /*
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, '||
286 ' (st.end_time-st.start_time) total_time,a.pnd_syncs,b.index_errors from sys.all_indexes ind, hz_dqm_stage_log st, '||
287 ' (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'' '||
288 ' 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'' '||
289 ' 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'' '||
290 ' 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'' '||
291 ' ) 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'' '||
292 ' 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'' '||
293 ' 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'' '||
294 ' 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'' '||
295 ' ) b where ind.index_name like ''HZ_STAGE%T1'' '||
296 ' and ind.table_name in(''HZ_STAGED_PARTIES'',''HZ_STAGED_PARTY_SITES'',''HZ_STAGED_CONTACTS'',''HZ_STAGED_CONTACT_POINTS'') '||
297 ' and st.operation=''CREATE_INDEXES'' and st.step in (''HZ_PARTIES'',''HZ_PARTY_SITES'',''HZ_ORG_CONTACTS'',''HZ_CONTACT_POINTS'') '||
298 ' and (decode(ind.table_name,''HZ_STAGED_PARTIES'',''HZ_PARTIES'') = st.step '||
299 ' OR decode(ind.table_name,''HZ_STAGED_PARTY_SITES'',''HZ_PARTY_SITES'') = st.step '||
300 ' OR decode(ind.table_name,''HZ_STAGED_CONTACTS'',''HZ_ORG_CONTACTS'') = st.step '||
301 ' OR decode(ind.table_name,''HZ_STAGED_CONTACT_POINTS'',''HZ_CONTACT_POINTS'') = st.step) '||
302 ' and a.index_name = b.index_name '||
303 ' and a.index_name = ind.index_name '); */
304
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, '||
306 ' (st.end_time-st.start_time) total_time, NVL(P.PND_CNT,0) pnd_syncs, NVL(E.ERR_CNT,0) index_errors '||
307 ' FROM DBA_INDEXES I, '||
308 ' (SELECT u.name pnd_index_owner , i.idx_name pnd_index_name, COUNT(*) PND_CNT '||
309 ' from ctxsys.dr$pending dr,ctxsys.dr$index i, sys.user$ u '||
310 ' where I.idx_owner# = u.user# and dr.pnd_pid = 0 and dr.pnd_cid = I.idx_id '||
311 ' GROUP BY u.name, i.idx_name ) P, '||
312 ' (SELECT err_index_owner, err_index_name, COUNT(*) ERR_CNT '||
313 ' FROM CTXSYS.CTX_INDEX_ERRORS '||
314 ' GROUP BY err_index_owner,err_index_name ) E, '||
315 ' hz_dqm_stage_log st '||
316 ' WHERE I.INDEX_NAME IN (''HZ_STAGE_PARTIES_T1'',''HZ_STAGE_PARTY_SITES_T1'', '||
317 ' ''HZ_STAGE_CONTACT_T1'',''HZ_STAGE_CPT_T1'') '||
318 ' AND I.INDEX_NAME = P.pnd_index_name(+) '||
319 ' AND I.OWNER = P.pnd_index_owner(+) '||
320 ' AND I.INDEX_NAME = E.err_index_name(+) '||
321 ' AND I.OWNER = E.err_index_owner(+) '||
322 ' AND st.operation=''CREATE_INDEXES'' '||
323 ' AND (decode(I.table_name, ''HZ_STAGED_PARTIES'', ''HZ_PARTIES'') = st.step '||
324 ' OR decode(I.table_name, ''HZ_STAGED_PARTY_SITES'', ''HZ_PARTY_SITES'' ) = st.step '||
325 ' OR decode(I.table_name, ''HZ_STAGED_CONTACTS'', ''HZ_ORG_CONTACTS'') = st.step '||
326 ' OR decode(I.table_name, ''HZ_STAGED_CONTACT_POINTS'', ''HZ_CONTACT_POINTS'') = st.step) ');
327
328 DBMS_XMLGEN.setRowTag(qryCtx, 'Intermedia index details');
329 l_result := DBMS_XMLGEN.getXML(qryCtx);
333 l_resultOffset := DBMS_LOB.INSTR(l_result ,'>');
330 l_rows_processed := DBMS_XMLGEN.getNumRowsProcessed(qryCtx);
331 IF l_rows_processed <> 0 THEN
332 l_result_length := dbms_lob.getlength(result);
334 dbms_lob.copy(result,l_result,dbms_lob.getlength(l_result)-l_resultOffset,
335 l_result_length,l_resultOffset);
336 END IF;
337
338
339 qryCtx := dbms_xmlgen.newContext('select * from ctxsys.ctx_pending where PND_INDEX_OWNER=''AR'' and PND_INDEX_NAME like ''HZ%STAGE%T1''');
340 DBMS_XMLGEN.setRowTag(qryCtx, 'Pending Syncs on Intermedia Index');
341 l_result := DBMS_XMLGEN.getXML(qryCtx);
342 l_rows_processed := DBMS_XMLGEN.getNumRowsProcessed(qryCtx);
343 IF l_rows_processed <> 0 THEN
344 l_result_length := dbms_lob.getlength(result);
345 l_resultOffset := DBMS_LOB.INSTR(l_result ,'>');
346 dbms_lob.copy(result,l_result,dbms_lob.getlength(l_result)-l_resultOffset,
347 l_result_length,l_resultOffset);
348 END IF;
349
350
351 qryCtx := dbms_xmlgen.newContext('select * from ctxsys.ctx_index_errors where err_index_name like ''HZ%STAGE%T1''');
352 DBMS_XMLGEN.setRowTag(qryCtx, 'Number of records with errors');
353 l_result := DBMS_XMLGEN.getXML(qryCtx);
354 l_rows_processed := DBMS_XMLGEN.getNumRowsProcessed(qryCtx);
355 IF l_rows_processed <> 0 THEN
356 l_result_length := dbms_lob.getlength(result);
357 l_resultOffset := DBMS_LOB.INSTR(l_result ,'>');
358 dbms_lob.copy(result,l_result,dbms_lob.getlength(l_result)-l_resultOffset,
359 l_result_length,l_resultOffset);
360 END IF;
361
362 if(fnd_installation.GET_APP_INFO('AR',l_status,l_temp,l_owner)) then
363 qryCtx := dbms_xmlgen.newContext('select col.* from user_synonyms syn, dba_ind_columns col where col.table_owner=''AR'' and
364 col.table_name in (''HZ_STAGED_PARTIES'',''HZ_STAGED_PARTY_SITES'',''HZ_STAGED_CONTACTS'',''HZ_STAGED_CONTACT_POINTS'') and
365 col.index_owner = syn.table_owner and col.table_name = syn.synonym_name');
366 DBMS_XMLGEN.setRowTag(qryCtx, 'Bulk Indexes');
367 l_result := DBMS_XMLGEN.getXML(qryCtx);
368 l_rows_processed := DBMS_XMLGEN.getNumRowsProcessed(qryCtx);
369 IF l_rows_processed <> 0 THEN
370 l_result_length := dbms_lob.getlength(result);
371 l_resultOffset := DBMS_LOB.INSTR(l_result ,'>');
372 dbms_lob.copy(result,l_result,dbms_lob.getlength(l_result)-l_resultOffset,
373 l_result_length,l_resultOffset);
374 END IF;
375 end if;
376
377 --DQM SYNCHRONIZATION PROGRAM DETAILS
378
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
380 lookup_code in (select nvl(FND_PROFILE.VALUE(''HZ_DQM_ENABLE_REALTIME_SYNC''),''Y'') from dual)');
381 DBMS_XMLGEN.setRowTag(qryCtx, 'Sync type from profile');
382 l_result := DBMS_XMLGEN.getXML(qryCtx);
383 l_rows_processed := DBMS_XMLGEN.getNumRowsProcessed(qryCtx);
384 IF l_rows_processed <> 0 THEN
385 l_result_length := dbms_lob.getlength(result);
386 l_resultOffset := DBMS_LOB.INSTR(l_result ,'>');
387 dbms_lob.copy(result,l_result,dbms_lob.getlength(l_result)-l_resultOffset,
388 l_result_length,l_resultOffset);
389 END IF;
390
391
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''');
393 DBMS_XMLGEN.setRowTag(qryCtx, 'Workflow Agent Listener Status');
394 l_result := DBMS_XMLGEN.getXML(qryCtx);
395 l_rows_processed := DBMS_XMLGEN.getNumRowsProcessed(qryCtx);
396 IF l_rows_processed <> 0 THEN
397 l_result_length := dbms_lob.getlength(result);
398 l_resultOffset := DBMS_LOB.INSTR(l_result ,'>');
399 dbms_lob.copy(result,l_result,dbms_lob.getlength(l_result)-l_resultOffset,
400 l_result_length,l_resultOffset);
401 END IF;
402
403
404 qryCtx := dbms_xmlgen.newContext('select ''PARTY'' entity,''Processing'' stage_status, nvl(sum(count(1)),0) SYNC_COUNT from hz_dqm_sync_interface '||
405 ' where entity=''PARTY'' and staged_flag=''P'' group by entity,staged_flag '||
406 ' UNION '||
407 ' select ''PARTY'' entity,''Pending'' stage_status, nvl(sum(count(1)),0) SYNC_COUNT from hz_dqm_sync_interface '||
408 ' where entity=''PARTY'' and staged_flag=''N'' group by entity,staged_flag '||
409 ' UNION '||
410 ' select ''PARTY'' entity,''Staged but not Indexed'' stage_status, nvl(sum(count(1)),0) SYNC_COUNT from hz_dqm_sync_interface '||
411 ' where entity=''PARTY'' and staged_flag=''Y'' group by entity,staged_flag '||
412 ' UNION '||
413 ' select ''PARTY'' entity,''Error'' stage_status, nvl(sum(count(1)),0) SYNC_COUNT from hz_dqm_sync_interface '||
414 ' where entity=''PARTY'' and staged_flag=''E'' group by entity,staged_flag '||
415 ' UNION '||
416 ' select ''PARTY_SITES'' entity,''Processing'' stage_status, nvl(sum(count(1)),0) SYNC_COUNT from hz_dqm_sync_interface '||
417 ' where entity=''PARTY_SITES'' and staged_flag=''P'' group by entity,staged_flag '||
418 ' UNION '||
419 ' select ''PARTY_SITES'' entity,''Pending'' stage_status, nvl(sum(count(1)),0) SYNC_COUNT from hz_dqm_sync_interface '||
420 ' where entity=''PARTY_SITES'' and staged_flag=''N'' group by entity,staged_flag '||
421 ' UNION '||
422 ' select ''PARTY_SITES'' entity,''Staged but not Indexed'' stage_status, nvl(sum(count(1)),0) SYNC_COUNT from hz_dqm_sync_interface '||
423 ' where entity=''PARTY_SITES'' and staged_flag=''Y'' group by entity,staged_flag '||
424 ' UNION '||
425 ' select ''PARTY_SITES'' entity,''Error'' stage_status, nvl(sum(count(1)),0) SYNC_COUNT from hz_dqm_sync_interface '||
426 ' where entity=''PARTY_SITES'' and staged_flag=''E'' group by entity,staged_flag '||
427 ' UNION '||
431 ' select ''CONTACTS'' entity,''Pending'' stage_status, nvl(sum(count(1)),0) SYNC_COUNT from hz_dqm_sync_interface '||
428 ' select ''CONTACTS'' entity,''Processing'' stage_status, nvl(sum(count(1)),0) SYNC_COUNT from hz_dqm_sync_interface '||
429 ' where entity=''CONTACTS'' and staged_flag=''P'' group by entity,staged_flag '||
430 ' UNION '||
432 ' where entity=''CONTACTS'' and staged_flag=''N'' group by entity,staged_flag '||
433 ' UNION '||
434 ' select ''CONTACTS'' entity,''Staged but not Indexed'' stage_status, nvl(sum(count(1)),0) SYNC_COUNT from hz_dqm_sync_interface '||
435 ' where entity=''CONTACTS'' and staged_flag=''Y'' group by entity,staged_flag '||
436 ' UNION '||
437 ' select ''CONTACTS'' entity,''Error'' stage_status, nvl(sum(count(1)),0) SYNC_COUNT from hz_dqm_sync_interface '||
438 ' where entity=''CONTACTS'' and staged_flag=''E'' group by entity,staged_flag '||
439 ' UNION '||
440 ' select ''CONTACT_POINTS'' entity,''Processing'' stage_status, nvl(sum(count(1)),0) SYNC_COUNT from hz_dqm_sync_interface '||
441 ' where entity=''CONTACT_POINTS'' and staged_flag=''P'' group by entity,staged_flag '||
442 ' UNION '||
443 ' select ''CONTACT_POINTS'' entity,''Pending'' stage_status, nvl(sum(count(1)),0) SYNC_COUNT from hz_dqm_sync_interface '||
444 ' where entity=''CONTACT_POINTS'' and staged_flag=''N'' group by entity,staged_flag '||
445 ' UNION '||
446 ' select ''CONTACT_POINTS'' entity,''Staged but not Indexed'' stage_status, nvl(sum(count(1)),0) SYNC_COUNT from hz_dqm_sync_interface '||
447 ' where entity=''CONTACT_POINTS'' and staged_flag=''Y'' group by entity,staged_flag '||
448 ' UNION '||
449 ' select ''CONTACT_POINTS'' entity,''Error'' stage_status, nvl(sum(count(1)),0) SYNC_COUNT from hz_dqm_sync_interface '||
450 ' where entity=''CONTACT_POINTS'' and staged_flag=''E'' group by entity,staged_flag ');
451 DBMS_XMLGEN.setRowTag(qryCtx, 'Interface table Data');
452 l_result := DBMS_XMLGEN.getXML(qryCtx);
453 l_rows_processed := DBMS_XMLGEN.getNumRowsProcessed(qryCtx);
454 IF l_rows_processed <> 0 THEN
455 l_result_length := dbms_lob.getlength(result);
456 l_resultOffset := DBMS_LOB.INSTR(l_result ,'>');
457 dbms_lob.copy(result,l_result,dbms_lob.getlength(l_result)-l_resultOffset,
458 l_result_length,l_resultOffset);
459 END IF;
460
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
462 from hz_dqm_sync_interface where staged_flag=''E''
463 group by entity,entity,operation,party_id,record_id,staged_flag,org_contact_id,party_site_id,error_data ');
464 DBMS_XMLGEN.setRowTag(qryCtx, 'Sync Interface table Errors per Entity');
465 l_result := DBMS_XMLGEN.getXML(qryCtx);
466 l_rows_processed := DBMS_XMLGEN.getNumRowsProcessed(qryCtx);
467 IF l_rows_processed <> 0 THEN
468 l_result_length := dbms_lob.getlength(result);
469 l_resultOffset := DBMS_LOB.INSTR(l_result ,'>');
470 dbms_lob.copy(result,l_result,dbms_lob.getlength(l_result)-l_resultOffset,
471 l_result_length,l_resultOffset);
472 END IF;
473
474
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
476 from FND_CONCURRENT_REQUESTS where status_code=''Q'' and phase_code=''P'' and program_application_id=222
477 and concurrent_program_id in(select concurrent_program_id from fnd_concurrent_programs where concurrent_program_name=''ARHDQSYN'')');
478 DBMS_XMLGEN.setRowTag(qryCtx, 'Sync program Schedule');
479 l_result := DBMS_XMLGEN.getXML(qryCtx);
480 l_rows_processed := DBMS_XMLGEN.getNumRowsProcessed(qryCtx);
481 IF l_rows_processed <> 0 THEN
482 l_result_length := dbms_lob.getlength(result);
483 l_resultOffset := DBMS_LOB.INSTR(l_result ,'>');
484 dbms_lob.copy(result,l_result,dbms_lob.getlength(l_result)-l_resultOffset,
485 l_result_length,l_resultOffset);
486 END IF;
487
488
489 qryCtx := dbms_xmlgen.newContext('select a.profile_option_id, b.user_profile_option_name,b.description,a.profile_option_value,
490 a.level_id,a.level_context,a.last_update_date, a.last_updated_by from
491 (select val.application_id, val.profile_option_id,''SITE'' level_id, null level_context,
492 val.last_update_date,val.last_updated_by, val.profile_option_value
493 from fnd_profile_option_values val where val.level_id=10001
494 UNION
495 select val.application_id, val.profile_option_id,''Application'' level_id, appl.application_name level_context,
496 val.last_update_date,val.last_updated_by, val.profile_option_value
497 from fnd_profile_option_values val,fnd_application_tl appl
498 where appl.application_id = val.level_value and appl.language=''US'' and val.level_id=10002
499 UNION
500 select val.application_id, val.profile_option_id,''Responsibility'' level_id, resp.responsibility_name level_context,
501 val.last_update_date,val.last_updated_by, val.profile_option_value
502 from fnd_profile_option_values val,fnd_responsibility_tl resp
503 where resp.responsibility_id = val.level_value and resp.language=''US'' and val.level_id=10003
504 UNION
505 select val.application_id, val.profile_option_id,''User'' level_id, usr.user_name level_context,
506 val.last_update_date,val.last_updated_by, val.profile_option_value
507 from fnd_profile_option_values val,fnd_user usr
508 where usr.user_id = val.level_value and val.level_id=10004
509 UNION
510 select val.application_id, val.profile_option_id,decode(val.level_id,10005,''Server'',10006,''Organization'') level_id,
511 null level_context,val.last_update_date,val.last_updated_by, val.profile_option_value
512 from fnd_profile_option_values val where val.level_id in (10005,10006)) a,
513 (select tl.user_profile_option_name,op.profile_option_id,tl.description
517 (select profile_option_id from Fnd_Profile_Cat_Options where category_name in
514 from fnd_profile_options op,fnd_profile_options_tl tl
515 where tl.profile_option_name = op.profile_option_name
516 and tl.language = ''US'' and op.profile_option_id in
518 (''HZ_DQM_DEPLOYMENT'',''HZ_DL_DEPLOYMENT'',''HZ_DL_IMPORT_SETUP'',''HZ_DL_MAPPING_SETUP'',''HZ_DL_SETUP''))) b
519 where a.profile_option_id=b.profile_option_id order by a.profile_option_id asc ');
520
521 DBMS_XMLGEN.setRowTag(qryCtx, 'DQM Profiles');
522 l_result := DBMS_XMLGEN.getXML(qryCtx);
523 l_rows_processed := DBMS_XMLGEN.getNumRowsProcessed(qryCtx);
524 IF l_rows_processed <> 0 THEN
525 l_result_length := dbms_lob.getlength(result);
526 l_resultOffset := DBMS_LOB.INSTR(l_result ,'>');
527 dbms_lob.copy(result,l_result,dbms_lob.getlength(l_result)-l_resultOffset,
528 l_result_length,l_resultOffset);
529 END IF;
530
531
532
533 l_close_tag := l_new_line||'</HZTESTXML>'||l_new_line;
534 dbms_lob.writeAppend(result, length(l_close_tag), l_close_tag);
535
536
537 fnd_file.put_line (
538 which => fnd_file.log,
539 buff => 'DQM Setup Snapshot XML');
540
541 -- get length of internal lob and open the dest. file.
542 l_clob_size := dbms_lob.getlength(result);
543
544 IF (l_clob_size = 0) THEN
545 fnd_file.put_line (
546 which => fnd_file.log,
547 buff => 'CLOB is empty');
548 RETURN;
549 END IF;
550
551 l_offset := 1;
552 l_chunk_size := 3000;
553
554 fnd_file.put_line (
555 which => fnd_file.log,
556 buff => 'Unloading... ' || l_clob_size);
557
558 WHILE (l_clob_size > 0) LOOP
559
560 fnd_file.put_line (
561 which => fnd_file.log,
562 buff => 'Off Set: ' || l_offset);
563
564 l_chunk := dbms_lob.substr (result, l_chunk_size, l_offset);
565
566 fnd_file.put_line (
567 which => fnd_file.log,
568 buff => l_chunk);
569
570 fnd_file.put(
571 which => fnd_file.output,
572 buff => l_chunk);
573
574 l_bloc_subset := UTL_RAW.CAST_TO_RAW (l_chunk);
575 l_new_length := UTL_RAW.LENGTH(l_bloc_subset);
576
577 dbms_lob.createtemporary(l_bloc_result,FALSE,DBMS_LOB.CALL);
578 dbms_lob.open(l_bloc_result,dbms_lob.lob_readwrite);
579 dbms_lob.write(l_bloc_result,l_new_length,l_offset,l_bloc_subset);
580
581 l_clob_size := l_clob_size - l_chunk_size;
582 l_offset := l_offset + l_chunk_size;
583
584 END LOOP;
585
586 --close context
587 DBMS_XMLGEN.closeContext(qryCtx);
588 --DBMS_XMLGEN.closeContext(qCtx);
589 DBMS_XMLGEN.closeContext(queryCtx);
590 DBMS_XMLGEN.closeContext(q1Ctx);
591 END DQM_SETUP_OVERVIEW_XML;
592
593
594 FUNCTION GET_TABLE_SIZE(p_table_name VARCHAR2) RETURN NUMBER IS
595 l_status VARCHAR2(255);
596 l_owner1 VARCHAR2(255);
597 l_temp VARCHAR2(255);
598 l_size NUMBER;
599
600 CURSOR c_number_of_blocks(t_name varchar2, l_own1 varchar2) is
601 SELECT blocks - empty_blocks
602 FROM sys.dba_tables
603 WHERE table_name = t_name and owner = l_own1;
604 CURSOR c_db_block_size is SELECT value
605 FROM v$parameter
606 WHERE name = 'db_block_size' ;
607 l_db_block_size NUMBER;
608 l_number_of_blocks NUMBER;
609
610 BEGIN
611 IF (fnd_installation.GET_APP_INFO('AR',l_status,l_temp,l_owner1)) THEN
612 OPEN c_number_of_blocks(p_table_name,l_owner1);
613 FETCH c_number_of_blocks into l_number_of_blocks;
614 CLOSE c_number_of_blocks;
615 OPEN c_db_block_size;
616 FETCH c_db_block_size into l_db_block_size;
617 CLOSE c_db_block_size;
618 END IF;
619 l_size := (l_number_of_blocks * l_db_block_size) / 1000000;
620 RETURN l_size;
621 EXCEPTION
622 WHEN OTHERS THEN
623 RETURN 0;
624 END GET_TABLE_SIZE;
625
626
627 PROCEDURE GENERATE_XML(errbuf OUT NOCOPY VARCHAR2, retcode OUT NOCOPY VARCHAR2, whichXml varchar2) IS
628 BEGIN
629 if(whichxml='SETUP') then
630 DQM_SETUP_OVERVIEW_XML();
631 end if;
632 EXCEPTION
633 WHEN OTHERS THEN
634 Raise;
635 END GENERATE_XML;
636
637 END HZ_DQM_DIAGNOSTICS_XML;