[Home] [Help]
PACKAGE BODY: APPS.HZ_DQM_DIAGNOSTICS_XML
Source
1 PACKAGE BODY HZ_DQM_DIAGNOSTICS_XML AS
2 /*$Header: ARHDXMLB.pls 120.4 2006/02/22 01:20:10 schitrap noship $ */
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
165 l_xml_header := l_xml_header ||l_new_line||'<PCONTACTS_TO_SYNC>'||to_char(l_sync_cnt)||'</PCONTACTS_TO_SYNC>';
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'));
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 ');
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);
250 IF l_rows_processed <> 0 THEN
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
261 l_result := DBMS_XMLGEN.getXML(qryCtx);
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');
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);
330 l_rows_processed := DBMS_XMLGEN.getNumRowsProcessed(qryCtx);
331 IF l_rows_processed <> 0 THEN
332 l_result_length := dbms_lob.getlength(result);
333 l_resultOffset := DBMS_LOB.INSTR(l_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);
345 l_resultOffset := DBMS_LOB.INSTR(l_result ,'>');
342 l_rows_processed := DBMS_XMLGEN.getNumRowsProcessed(qryCtx);
343 IF l_rows_processed <> 0 THEN
344 l_result_length := dbms_lob.getlength(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 * from sys.dba_ind_columns where table_owner=''AR'' and table_name in(''HZ_STAGED_PARTIES'',''HZ_STAGED_PARTY_SITES'',''HZ_STAGED_CONTACTS'',''HZ_STAGED_CONTACT_POINTS'') ');
364 DBMS_XMLGEN.setRowTag(qryCtx, 'Bulk Indexes');
365 l_result := DBMS_XMLGEN.getXML(qryCtx);
366 l_rows_processed := DBMS_XMLGEN.getNumRowsProcessed(qryCtx);
367 IF l_rows_processed <> 0 THEN
368 l_result_length := dbms_lob.getlength(result);
369 l_resultOffset := DBMS_LOB.INSTR(l_result ,'>');
370 dbms_lob.copy(result,l_result,dbms_lob.getlength(l_result)-l_resultOffset,
371 l_result_length,l_resultOffset);
372 END IF;
373 end if;
374
375 --DQM SYNCHRONIZATION PROGRAM DETAILS
376
377 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
378 lookup_code in (select nvl(FND_PROFILE.VALUE(''HZ_DQM_ENABLE_REALTIME_SYNC''),''Y'') from dual)');
379 DBMS_XMLGEN.setRowTag(qryCtx, 'Sync type from profile');
380 l_result := DBMS_XMLGEN.getXML(qryCtx);
381 l_rows_processed := DBMS_XMLGEN.getNumRowsProcessed(qryCtx);
382 IF l_rows_processed <> 0 THEN
383 l_result_length := dbms_lob.getlength(result);
384 l_resultOffset := DBMS_LOB.INSTR(l_result ,'>');
385 dbms_lob.copy(result,l_result,dbms_lob.getlength(l_result)-l_resultOffset,
386 l_result_length,l_resultOffset);
387 END IF;
388
389
390 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''');
391 DBMS_XMLGEN.setRowTag(qryCtx, 'Workflow Agent Listener Status');
392 l_result := DBMS_XMLGEN.getXML(qryCtx);
393 l_rows_processed := DBMS_XMLGEN.getNumRowsProcessed(qryCtx);
394 IF l_rows_processed <> 0 THEN
395 l_result_length := dbms_lob.getlength(result);
396 l_resultOffset := DBMS_LOB.INSTR(l_result ,'>');
397 dbms_lob.copy(result,l_result,dbms_lob.getlength(l_result)-l_resultOffset,
398 l_result_length,l_resultOffset);
399 END IF;
400
401
402 qryCtx := dbms_xmlgen.newContext('select ''PARTY'' entity,''Processing'' stage_status, nvl(sum(count(1)),0) SYNC_COUNT from hz_dqm_sync_interface '||
403 ' where entity=''PARTY'' and staged_flag=''P'' group by entity,staged_flag '||
404 ' UNION '||
405 ' select ''PARTY'' entity,''Pending'' stage_status, nvl(sum(count(1)),0) SYNC_COUNT from hz_dqm_sync_interface '||
406 ' where entity=''PARTY'' and staged_flag=''N'' group by entity,staged_flag '||
407 ' UNION '||
408 ' select ''PARTY'' entity,''Staged but not Indexed'' stage_status, nvl(sum(count(1)),0) SYNC_COUNT from hz_dqm_sync_interface '||
409 ' where entity=''PARTY'' and staged_flag=''Y'' group by entity,staged_flag '||
410 ' UNION '||
411 ' select ''PARTY'' entity,''Error'' stage_status, nvl(sum(count(1)),0) SYNC_COUNT from hz_dqm_sync_interface '||
412 ' where entity=''PARTY'' and staged_flag=''E'' group by entity,staged_flag '||
413 ' UNION '||
414 ' select ''PARTY_SITES'' entity,''Processing'' stage_status, nvl(sum(count(1)),0) SYNC_COUNT from hz_dqm_sync_interface '||
415 ' where entity=''PARTY_SITES'' and staged_flag=''P'' group by entity,staged_flag '||
416 ' UNION '||
417 ' select ''PARTY_SITES'' entity,''Pending'' stage_status, nvl(sum(count(1)),0) SYNC_COUNT from hz_dqm_sync_interface '||
418 ' where entity=''PARTY_SITES'' and staged_flag=''N'' group by entity,staged_flag '||
419 ' UNION '||
420 ' select ''PARTY_SITES'' entity,''Staged but not Indexed'' stage_status, nvl(sum(count(1)),0) SYNC_COUNT from hz_dqm_sync_interface '||
421 ' where entity=''PARTY_SITES'' and staged_flag=''Y'' group by entity,staged_flag '||
422 ' UNION '||
423 ' select ''PARTY_SITES'' entity,''Error'' stage_status, nvl(sum(count(1)),0) SYNC_COUNT from hz_dqm_sync_interface '||
424 ' where entity=''PARTY_SITES'' and staged_flag=''E'' group by entity,staged_flag '||
425 ' UNION '||
426 ' select ''CONTACTS'' entity,''Processing'' stage_status, nvl(sum(count(1)),0) SYNC_COUNT from hz_dqm_sync_interface '||
427 ' where entity=''CONTACTS'' and staged_flag=''P'' group by entity,staged_flag '||
428 ' UNION '||
429 ' select ''CONTACTS'' entity,''Pending'' stage_status, nvl(sum(count(1)),0) SYNC_COUNT from hz_dqm_sync_interface '||
430 ' where entity=''CONTACTS'' and staged_flag=''N'' group by entity,staged_flag '||
431 ' UNION '||
432 ' select ''CONTACTS'' entity,''Staged but not Indexed'' stage_status, nvl(sum(count(1)),0) SYNC_COUNT from hz_dqm_sync_interface '||
433 ' where entity=''CONTACTS'' and staged_flag=''Y'' group by entity,staged_flag '||
434 ' UNION '||
435 ' select ''CONTACTS'' entity,''Error'' stage_status, nvl(sum(count(1)),0) SYNC_COUNT from hz_dqm_sync_interface '||
439 ' where entity=''CONTACT_POINTS'' and staged_flag=''P'' group by entity,staged_flag '||
436 ' where entity=''CONTACTS'' and staged_flag=''E'' group by entity,staged_flag '||
437 ' UNION '||
438 ' select ''CONTACT_POINTS'' entity,''Processing'' stage_status, nvl(sum(count(1)),0) SYNC_COUNT from hz_dqm_sync_interface '||
440 ' UNION '||
441 ' select ''CONTACT_POINTS'' entity,''Pending'' stage_status, nvl(sum(count(1)),0) SYNC_COUNT from hz_dqm_sync_interface '||
442 ' where entity=''CONTACT_POINTS'' and staged_flag=''N'' group by entity,staged_flag '||
443 ' UNION '||
444 ' select ''CONTACT_POINTS'' entity,''Staged but not Indexed'' stage_status, nvl(sum(count(1)),0) SYNC_COUNT from hz_dqm_sync_interface '||
445 ' where entity=''CONTACT_POINTS'' and staged_flag=''Y'' group by entity,staged_flag '||
446 ' UNION '||
447 ' select ''CONTACT_POINTS'' entity,''Error'' stage_status, nvl(sum(count(1)),0) SYNC_COUNT from hz_dqm_sync_interface '||
448 ' where entity=''CONTACT_POINTS'' and staged_flag=''E'' group by entity,staged_flag ');
449 DBMS_XMLGEN.setRowTag(qryCtx, 'Interface table Data');
450 l_result := DBMS_XMLGEN.getXML(qryCtx);
451 l_rows_processed := DBMS_XMLGEN.getNumRowsProcessed(qryCtx);
452 IF l_rows_processed <> 0 THEN
453 l_result_length := dbms_lob.getlength(result);
454 l_resultOffset := DBMS_LOB.INSTR(l_result ,'>');
455 dbms_lob.copy(result,l_result,dbms_lob.getlength(l_result)-l_resultOffset,
456 l_result_length,l_resultOffset);
457 END IF;
458
459 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
460 from hz_dqm_sync_interface where staged_flag=''E''
461 group by entity,entity,operation,party_id,record_id,staged_flag,org_contact_id,party_site_id,error_data ');
462 DBMS_XMLGEN.setRowTag(qryCtx, 'Sync Interface table Errors per Entity');
463 l_result := DBMS_XMLGEN.getXML(qryCtx);
464 l_rows_processed := DBMS_XMLGEN.getNumRowsProcessed(qryCtx);
465 IF l_rows_processed <> 0 THEN
466 l_result_length := dbms_lob.getlength(result);
467 l_resultOffset := DBMS_LOB.INSTR(l_result ,'>');
468 dbms_lob.copy(result,l_result,dbms_lob.getlength(l_result)-l_resultOffset,
469 l_result_length,l_resultOffset);
470 END IF;
471
472
473 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
474 from FND_CONCURRENT_REQUESTS where status_code=''Q'' and phase_code=''P'' and program_application_id=222
475 and concurrent_program_id in(select concurrent_program_id from fnd_concurrent_programs where concurrent_program_name=''ARHDQSYN'')');
476 DBMS_XMLGEN.setRowTag(qryCtx, 'Sync program Schedule');
477 l_result := DBMS_XMLGEN.getXML(qryCtx);
478 l_rows_processed := DBMS_XMLGEN.getNumRowsProcessed(qryCtx);
479 IF l_rows_processed <> 0 THEN
480 l_result_length := dbms_lob.getlength(result);
481 l_resultOffset := DBMS_LOB.INSTR(l_result ,'>');
482 dbms_lob.copy(result,l_result,dbms_lob.getlength(l_result)-l_resultOffset,
483 l_result_length,l_resultOffset);
484 END IF;
485
486
487 qryCtx := dbms_xmlgen.newContext('select a.profile_option_id, b.user_profile_option_name,b.description,a.profile_option_value,
488 a.level_id,a.level_context,a.last_update_date, a.last_updated_by from
489 (select val.application_id, val.profile_option_id,''SITE'' level_id, null level_context,
490 val.last_update_date,val.last_updated_by, val.profile_option_value
491 from fnd_profile_option_values val where val.level_id=10001
492 UNION
493 select val.application_id, val.profile_option_id,''Application'' level_id, appl.application_name level_context,
494 val.last_update_date,val.last_updated_by, val.profile_option_value
495 from fnd_profile_option_values val,fnd_application_tl appl
496 where appl.application_id = val.level_value and appl.language=''US'' and val.level_id=10002
497 UNION
498 select val.application_id, val.profile_option_id,''Responsibility'' level_id, resp.responsibility_name level_context,
499 val.last_update_date,val.last_updated_by, val.profile_option_value
500 from fnd_profile_option_values val,fnd_responsibility_tl resp
501 where resp.responsibility_id = val.level_value and resp.language=''US'' and val.level_id=10003
502 UNION
503 select val.application_id, val.profile_option_id,''User'' level_id, usr.user_name level_context,
504 val.last_update_date,val.last_updated_by, val.profile_option_value
505 from fnd_profile_option_values val,fnd_user usr
506 where usr.user_id = val.level_value and val.level_id=10004
507 UNION
508 select val.application_id, val.profile_option_id,decode(val.level_id,10005,''Server'',10006,''Organization'') level_id,
509 null level_context,val.last_update_date,val.last_updated_by, val.profile_option_value
510 from fnd_profile_option_values val where val.level_id in (10005,10006)) a,
511 (select tl.user_profile_option_name,op.profile_option_id,tl.description
512 from fnd_profile_options op,fnd_profile_options_tl tl
513 where tl.profile_option_name = op.profile_option_name
514 and tl.language = ''US'' and op.profile_option_id in
515 (select profile_option_id from Fnd_Profile_Cat_Options where category_name in
516 (''HZ_DQM_DEPLOYMENT'',''HZ_DL_DEPLOYMENT'',''HZ_DL_IMPORT_SETUP'',''HZ_DL_MAPPING_SETUP'',''HZ_DL_SETUP''))) b
517 where a.profile_option_id=b.profile_option_id order by a.profile_option_id asc ');
518
519 DBMS_XMLGEN.setRowTag(qryCtx, 'DQM Profiles');
520 l_result := DBMS_XMLGEN.getXML(qryCtx);
521 l_rows_processed := DBMS_XMLGEN.getNumRowsProcessed(qryCtx);
522 IF l_rows_processed <> 0 THEN
523 l_result_length := dbms_lob.getlength(result);
527 END IF;
524 l_resultOffset := DBMS_LOB.INSTR(l_result ,'>');
525 dbms_lob.copy(result,l_result,dbms_lob.getlength(l_result)-l_resultOffset,
526 l_result_length,l_resultOffset);
528
529
530
531 l_close_tag := l_new_line||'</HZTESTXML>'||l_new_line;
532 dbms_lob.writeAppend(result, length(l_close_tag), l_close_tag);
533
534
535 fnd_file.put_line (
536 which => fnd_file.log,
537 buff => 'DQM Setup Snapshot XML');
538
539 -- get length of internal lob and open the dest. file.
540 l_clob_size := dbms_lob.getlength(result);
541
542 IF (l_clob_size = 0) THEN
543 fnd_file.put_line (
544 which => fnd_file.log,
545 buff => 'CLOB is empty');
546 RETURN;
547 END IF;
548
549 l_offset := 1;
550 l_chunk_size := 3000;
551
552 fnd_file.put_line (
553 which => fnd_file.log,
554 buff => 'Unloading... ' || l_clob_size);
555
556 WHILE (l_clob_size > 0) LOOP
557
558 fnd_file.put_line (
559 which => fnd_file.log,
560 buff => 'Off Set: ' || l_offset);
561
562 l_chunk := dbms_lob.substr (result, l_chunk_size, l_offset);
563
564 fnd_file.put_line (
565 which => fnd_file.log,
566 buff => l_chunk);
567
568 fnd_file.put(
569 which => fnd_file.output,
570 buff => l_chunk);
571
572 l_bloc_subset := UTL_RAW.CAST_TO_RAW (l_chunk);
573 l_new_length := UTL_RAW.LENGTH(l_bloc_subset);
574
575 dbms_lob.createtemporary(l_bloc_result,FALSE,DBMS_LOB.CALL);
576 dbms_lob.open(l_bloc_result,dbms_lob.lob_readwrite);
577 dbms_lob.write(l_bloc_result,l_new_length,l_offset,l_bloc_subset);
578
579 l_clob_size := l_clob_size - l_chunk_size;
580 l_offset := l_offset + l_chunk_size;
581
582 END LOOP;
583
584 --close context
585 DBMS_XMLGEN.closeContext(qryCtx);
586 --DBMS_XMLGEN.closeContext(qCtx);
587 DBMS_XMLGEN.closeContext(queryCtx);
588 DBMS_XMLGEN.closeContext(q1Ctx);
589 END DQM_SETUP_OVERVIEW_XML;
590
591
592 FUNCTION GET_TABLE_SIZE(p_table_name VARCHAR2) RETURN NUMBER IS
593 l_status VARCHAR2(255);
594 l_owner1 VARCHAR2(255);
595 l_temp VARCHAR2(255);
596 l_size NUMBER;
597
598 CURSOR c_number_of_blocks(t_name varchar2, l_own1 varchar2) is
599 SELECT blocks - empty_blocks
600 FROM sys.dba_tables
601 WHERE table_name = t_name and owner = l_own1;
602 CURSOR c_db_block_size is SELECT value
603 FROM v$parameter
604 WHERE name = 'db_block_size' ;
605 l_db_block_size NUMBER;
606 l_number_of_blocks NUMBER;
607
608 BEGIN
609 IF (fnd_installation.GET_APP_INFO('AR',l_status,l_temp,l_owner1)) THEN
610 OPEN c_number_of_blocks(p_table_name,l_owner1);
611 FETCH c_number_of_blocks into l_number_of_blocks;
612 CLOSE c_number_of_blocks;
613 OPEN c_db_block_size;
614 FETCH c_db_block_size into l_db_block_size;
615 CLOSE c_db_block_size;
616 END IF;
617 l_size := (l_number_of_blocks * l_db_block_size) / 1000000;
618 RETURN l_size;
619 EXCEPTION
620 WHEN OTHERS THEN
621 RETURN 0;
622 END GET_TABLE_SIZE;
623
624
625 PROCEDURE GENERATE_XML(errbuf OUT NOCOPY VARCHAR2, retcode OUT NOCOPY VARCHAR2, whichXml varchar2) IS
626 BEGIN
627 if(whichxml='SETUP') then
628 DQM_SETUP_OVERVIEW_XML();
629 end if;
630 EXCEPTION
631 WHEN OTHERS THEN
632 Raise;
633 END GENERATE_XML;
634
635 END HZ_DQM_DIAGNOSTICS_XML;