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 dependencies on DBMS_XMLGEN

Line 5: qryCtx DBMS_XMLGEN.ctxHandle;

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:

Line 6: --qCtx DBMS_XMLGEN.ctxHandle;

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;

Line 7: queryCtx DBMS_XMLGEN.ctxHandle;

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;

Line 8: q1Ctx DBMS_XMLGEN.ctxHandle;

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;

Line 57: qryCtx := dbms_xmlgen.newContext('select operation, number_of_workers,

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'')

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,

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,

Line 68: queryCtx := dbms_xmlgen.newcontext('select attr.attribute_id,attr.attribute_name,attr.entity_name,attr.user_defined_attribute_name,

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

Line 89: DBMS_XMLGEN.setRowTag(qryCtx, 'DQM Stage From LOG');

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:

Line 90: --DBMS_XMLGEN.setRowTag(qCtx, 'DQM Transformations');

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

Line 91: DBMS_XMLGEN.setRowTag(queryCtx, 'DQM Transformation Attributes');

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);

Line 92: DBMS_XMLGEN.setRowTag(q1Ctx, 'Match Rules');

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);

Line 95: l_result := DBMS_XMLGEN.getXML(qryCtx);

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:

Line 96: --l_trans_result := DBMS_XMLGEN.getXML(qCtx);

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 := '

Line 97: l_attrib_result := DBMS_XMLGEN.getXML(queryCtx);

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: ';

Line 98: l_mrule_result := DBMS_XMLGEN.getXML(q1Ctx);

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 := '';

Line 199: l_rows_processed := DBMS_XMLGEN.getNumRowsProcessed(qryCtx);

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,

Line 210: l_rows_processed := DBMS_XMLGEN.getNumRowsProcessed(queryCtx);

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,

Line 220: l_rows_processed := DBMS_XMLGEN.getNumRowsProcessed(q1Ctx);

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,

Line 230: qryCtx := dbms_xmlgen.newContext('select operation,to_char(max(end_time)-min(start_time)) time_diff from hz_dqm_stage_log

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);

Line 233: DBMS_XMLGEN.setRowTag(qryCtx, 'Time for STAGE_NEW_TRANSFORMATIONS 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);

Line 234: l_result := DBMS_XMLGEN.getXML(qryCtx);

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 ,'>');

Line 235: l_rows_processed := DBMS_XMLGEN.getNumRowsProcessed(qryCtx);

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,

Line 244: qryCtx := dbms_xmlgen.newContext('select operation,to_char(max(end_time)-min(start_time)) time_diff from hz_dqm_stage_log

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);

Line 247: DBMS_XMLGEN.setRowTag(qryCtx, 'Time for STAGE_ALL_DATA in Staging Log Table');

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);

Line 248: l_result := DBMS_XMLGEN.getXML(qryCtx);

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 ,'>');

Line 249: l_rows_processed := DBMS_XMLGEN.getNumRowsProcessed(qryCtx);

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,

Line 257: qryCtx := dbms_xmlgen.newContext('select operation,to_char(max(end_time)-min(start_time)) time_diff from hz_dqm_stage_log

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);

Line 260: DBMS_XMLGEN.setRowTag(qryCtx, 'Time for CREATE_INDEXES in Staging Log Table');

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);

Line 261: l_result := DBMS_XMLGEN.getXML(qryCtx);

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 ,'>');

Line 262: l_rows_processed := DBMS_XMLGEN.getNumRowsProcessed(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');
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,

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');

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

Line 274: DBMS_XMLGEN.setRowTag(qryCtx, 'Time to build intermedia index by entity');

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);

Line 275: l_result := DBMS_XMLGEN.getXML(qryCtx);

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 ,'>');

Line 276: l_rows_processed := DBMS_XMLGEN.getNumRowsProcessed(qryCtx);

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,

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, '||

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'' '||

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, '||

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 '||

Line 328: DBMS_XMLGEN.setRowTag(qryCtx, 'Intermedia index details');

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);

Line 329: l_result := DBMS_XMLGEN.getXML(qryCtx);

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 ,'>');

Line 330: l_rows_processed := DBMS_XMLGEN.getNumRowsProcessed(qryCtx);

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,

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

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

Line 340: DBMS_XMLGEN.setRowTag(qryCtx, 'Pending Syncs on Intermedia Index');

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);

Line 341: l_result := DBMS_XMLGEN.getXML(qryCtx);

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 ,'>');

Line 342: l_rows_processed := DBMS_XMLGEN.getNumRowsProcessed(qryCtx);

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,

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

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

Line 352: DBMS_XMLGEN.setRowTag(qryCtx, 'Number of records with errors');

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);

Line 353: l_result := DBMS_XMLGEN.getXML(qryCtx);

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 ,'>');

Line 354: l_rows_processed := DBMS_XMLGEN.getNumRowsProcessed(qryCtx);

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,

Line 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'') ');

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

Line 364: DBMS_XMLGEN.setRowTag(qryCtx, 'Bulk Indexes');

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);

Line 365: l_result := DBMS_XMLGEN.getXML(qryCtx);

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 ,'>');

Line 366: l_rows_processed := DBMS_XMLGEN.getNumRowsProcessed(qryCtx);

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,

Line 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

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);

Line 379: DBMS_XMLGEN.setRowTag(qryCtx, 'Sync type from profile');

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);

Line 380: l_result := DBMS_XMLGEN.getXML(qryCtx);

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 ,'>');

Line 381: l_rows_processed := DBMS_XMLGEN.getNumRowsProcessed(qryCtx);

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,

Line 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''');

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

Line 391: DBMS_XMLGEN.setRowTag(qryCtx, 'Workflow Agent Listener Status');

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);

Line 392: l_result := DBMS_XMLGEN.getXML(qryCtx);

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 ,'>');

Line 393: l_rows_processed := DBMS_XMLGEN.getNumRowsProcessed(qryCtx);

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,

Line 402: qryCtx := dbms_xmlgen.newContext('select ''PARTY'' entity,''Processing'' stage_status, nvl(sum(count(1)),0) SYNC_COUNT from hz_dqm_sync_interface '||

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 '||

Line 449: DBMS_XMLGEN.setRowTag(qryCtx, 'Interface table Data');

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);

Line 450: l_result := DBMS_XMLGEN.getXML(qryCtx);

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 ,'>');

Line 451: l_rows_processed := DBMS_XMLGEN.getNumRowsProcessed(qryCtx);

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,

Line 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

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);

Line 462: DBMS_XMLGEN.setRowTag(qryCtx, 'Sync Interface table Errors per Entity');

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);

Line 463: l_result := DBMS_XMLGEN.getXML(qryCtx);

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 ,'>');

Line 464: l_rows_processed := DBMS_XMLGEN.getNumRowsProcessed(qryCtx);

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,

Line 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

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);

Line 476: DBMS_XMLGEN.setRowTag(qryCtx, 'Sync program Schedule');

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);

Line 477: l_result := DBMS_XMLGEN.getXML(qryCtx);

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 ,'>');

Line 478: l_rows_processed := DBMS_XMLGEN.getNumRowsProcessed(qryCtx);

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,

Line 487: qryCtx := dbms_xmlgen.newContext('select a.profile_option_id, b.user_profile_option_name,b.description,a.profile_option_value,

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

Line 519: DBMS_XMLGEN.setRowTag(qryCtx, 'DQM Profiles');

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);

Line 520: l_result := DBMS_XMLGEN.getXML(qryCtx);

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);
524: l_resultOffset := DBMS_LOB.INSTR(l_result ,'>');

Line 521: l_rows_processed := DBMS_XMLGEN.getNumRowsProcessed(qryCtx);

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);
524: l_resultOffset := DBMS_LOB.INSTR(l_result ,'>');
525: dbms_lob.copy(result,l_result,dbms_lob.getlength(l_result)-l_resultOffset,

Line 585: DBMS_XMLGEN.closeContext(qryCtx);

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;

Line 586: --DBMS_XMLGEN.closeContext(qCtx);

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:

Line 587: DBMS_XMLGEN.closeContext(queryCtx);

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:

Line 588: DBMS_XMLGEN.closeContext(q1Ctx);

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