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.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:

Line 6: --qCtx DBMS_XMLGEN.ctxHandle;

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;

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 col.* from user_synonyms syn, dba_ind_columns col where col.table_owner=''AR'' and

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

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

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

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

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

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

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,

Line 379: qryCtx := dbms_xmlgen.newContext('select ''HZ_DQM_ENABLE_REALTIME_SYNC'' sync_profile, meaning sync_type from ar_lookups where lookup_type = ''HZ_DQM_SYNC_VALUES'' and

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

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

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

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

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

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

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,

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

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

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

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

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

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

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

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,

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

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

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

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

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

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

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

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,

Line 461: qryCtx := dbms_xmlgen.newContext('select party_id,record_id,entity,decode(operation,''U'',''Update'',''C'',''Create'',operation) operation,''Error'' staged_flag ,org_contact_id,party_site_id,error_data

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

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

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

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

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

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

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,

Line 475: qryCtx := dbms_xmlgen.newContext('select request_id,last_update_date,request_date, phase_code,status_code,to_char(requested_start_date,''DD-MON-YY HH24:MI:SS'') requested_start_date,concurrent_program_id

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

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

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

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

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

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

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,

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

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

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

517: (select profile_option_id from Fnd_Profile_Cat_Options where category_name 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);

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

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

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

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,

Line 587: DBMS_XMLGEN.closeContext(qryCtx);

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;

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

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:

Line 589: DBMS_XMLGEN.closeContext(queryCtx);

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:

Line 590: DBMS_XMLGEN.closeContext(q1Ctx);

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