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

Line 7: qryCtx1 dbms_xmlgen.ctxType;

3:
4: g_request_id NUMBER(15, 0);
5:
6: FUNCTION generate_approver_header_xml (p_request_id IN NUMBER DEFAULT NULL) RETURN CLOB IS
7: qryCtx1 dbms_xmlgen.ctxType;
8: query1 varchar2(4000);
9: xmlresult1 CLOB;
10: l_xml CLOB DEFAULT empty_clob();
11: l_resultOffset int;

Line 81: qryCtx1 := dbms_xmlgen.newContext(query1);

77: || 'AND flv7.lookup_code (+) = prth.parameter_value_11 '
78: || 'AND flv8.lookup_code (+) = prth.parameter_value_12 '
79: || 'AND xtt.language = ''' || l_language_code || ''' '
80: || 'AND xtt.template_code = prth.report_template_code AND xtt.application_short_name = ''PSP''';
81: qryCtx1 := dbms_xmlgen.newContext(query1);
82: dbms_xmlgen.setRowTag(qryCtx1, NULL);
83: dbms_xmlgen.setRowSetTag(qryCtx1, 'G_REPORT_INFO');
84: xmlresult1 := dbms_xmlgen.getXML(qryCtx1, dbms_xmlgen.NONE);
85: dbms_xmlgen.closecontext(qryctx1);

Line 82: dbms_xmlgen.setRowTag(qryCtx1, NULL);

78: || 'AND flv8.lookup_code (+) = prth.parameter_value_12 '
79: || 'AND xtt.language = ''' || l_language_code || ''' '
80: || 'AND xtt.template_code = prth.report_template_code AND xtt.application_short_name = ''PSP''';
81: qryCtx1 := dbms_xmlgen.newContext(query1);
82: dbms_xmlgen.setRowTag(qryCtx1, NULL);
83: dbms_xmlgen.setRowSetTag(qryCtx1, 'G_REPORT_INFO');
84: xmlresult1 := dbms_xmlgen.getXML(qryCtx1, dbms_xmlgen.NONE);
85: dbms_xmlgen.closecontext(qryctx1);
86: l_xml := xmlresult1;

Line 83: dbms_xmlgen.setRowSetTag(qryCtx1, 'G_REPORT_INFO');

79: || 'AND xtt.language = ''' || l_language_code || ''' '
80: || 'AND xtt.template_code = prth.report_template_code AND xtt.application_short_name = ''PSP''';
81: qryCtx1 := dbms_xmlgen.newContext(query1);
82: dbms_xmlgen.setRowTag(qryCtx1, NULL);
83: dbms_xmlgen.setRowSetTag(qryCtx1, 'G_REPORT_INFO');
84: xmlresult1 := dbms_xmlgen.getXML(qryCtx1, dbms_xmlgen.NONE);
85: dbms_xmlgen.closecontext(qryctx1);
86: l_xml := xmlresult1;
87: dbms_lob.write(l_xml, length(' '), 1, ' ');

Line 84: xmlresult1 := dbms_xmlgen.getXML(qryCtx1, dbms_xmlgen.NONE);

80: || 'AND xtt.template_code = prth.report_template_code AND xtt.application_short_name = ''PSP''';
81: qryCtx1 := dbms_xmlgen.newContext(query1);
82: dbms_xmlgen.setRowTag(qryCtx1, NULL);
83: dbms_xmlgen.setRowSetTag(qryCtx1, 'G_REPORT_INFO');
84: xmlresult1 := dbms_xmlgen.getXML(qryCtx1, dbms_xmlgen.NONE);
85: dbms_xmlgen.closecontext(qryctx1);
86: l_xml := xmlresult1;
87: dbms_lob.write(l_xml, length(' '), 1, ' ');
88: l_resultOffset := DBMS_LOB.INSTR(xmlresult1,'>');

Line 85: dbms_xmlgen.closecontext(qryctx1);

81: qryCtx1 := dbms_xmlgen.newContext(query1);
82: dbms_xmlgen.setRowTag(qryCtx1, NULL);
83: dbms_xmlgen.setRowSetTag(qryCtx1, 'G_REPORT_INFO');
84: xmlresult1 := dbms_xmlgen.getXML(qryCtx1, dbms_xmlgen.NONE);
85: dbms_xmlgen.closecontext(qryctx1);
86: l_xml := xmlresult1;
87: dbms_lob.write(l_xml, length(' '), 1, ' ');
88: l_resultOffset := DBMS_LOB.INSTR(xmlresult1,'>');
89: dbms_lob.copy(l_xml, xmlresult1, dbms_lob.getlength(xmlresult1) - l_resultOffset, length(' '), l_resultOffset +1);

Line 114: qryCtx1 dbms_xmlgen.ctxType;

110: END generate_approver_header_xml;
111:
112: FUNCTION generate_approver_xml (p_wf_item_key IN NUMBER,
113: p_request_id IN NUMBER DEFAULT NULL) RETURN CLOB IS
114: qryCtx1 dbms_xmlgen.ctxType;
115: query1 varchar2(4000);
116: query2 varchar2(4000);
117: xmlresult1 CLOB;
118: l_xml CLOB DEFAULT empty_clob();

Line 357: qryCtx1 := dbms_xmlgen.newContext(query1);

353: || ''') start_date, TO_CHAR(fnd_date.canonical_to_date(fnd_date.date_to_canonical(prth.parameter_value_3)), '''
354: || l_icx_date_format || ''') end_date, ''' || l_display_flag || ''' display_flag, ''' || l_gl_header || ''' gl_header, '
355: || 'TO_CHAR(SYSDATE, ''' || l_icx_date_format || ''') run_date '
356: || 'FROM psp_report_templates_h prth WHERE prth.request_id = ' || TO_CHAR(l_request_id);
357: qryCtx1 := dbms_xmlgen.newContext(query1);
358: dbms_xmlgen.setRowTag(qryCtx1, NULL);
359: dbms_xmlgen.setRowSetTag(qryCtx1, 'G_REPORT_INFO');
360: xmlresult1 := dbms_xmlgen.getXML(qryCtx1, dbms_xmlgen.NONE);
361: dbms_xmlgen.closecontext(qryctx1);

Line 358: dbms_xmlgen.setRowTag(qryCtx1, NULL);

354: || l_icx_date_format || ''') end_date, ''' || l_display_flag || ''' display_flag, ''' || l_gl_header || ''' gl_header, '
355: || 'TO_CHAR(SYSDATE, ''' || l_icx_date_format || ''') run_date '
356: || 'FROM psp_report_templates_h prth WHERE prth.request_id = ' || TO_CHAR(l_request_id);
357: qryCtx1 := dbms_xmlgen.newContext(query1);
358: dbms_xmlgen.setRowTag(qryCtx1, NULL);
359: dbms_xmlgen.setRowSetTag(qryCtx1, 'G_REPORT_INFO');
360: xmlresult1 := dbms_xmlgen.getXML(qryCtx1, dbms_xmlgen.NONE);
361: dbms_xmlgen.closecontext(qryctx1);
362: l_xml := xmlresult1;

Line 359: dbms_xmlgen.setRowSetTag(qryCtx1, 'G_REPORT_INFO');

355: || 'TO_CHAR(SYSDATE, ''' || l_icx_date_format || ''') run_date '
356: || 'FROM psp_report_templates_h prth WHERE prth.request_id = ' || TO_CHAR(l_request_id);
357: qryCtx1 := dbms_xmlgen.newContext(query1);
358: dbms_xmlgen.setRowTag(qryCtx1, NULL);
359: dbms_xmlgen.setRowSetTag(qryCtx1, 'G_REPORT_INFO');
360: xmlresult1 := dbms_xmlgen.getXML(qryCtx1, dbms_xmlgen.NONE);
361: dbms_xmlgen.closecontext(qryctx1);
362: l_xml := xmlresult1;
363: dbms_lob.write(l_xml, length(l_report_info), 1, l_report_info);

Line 360: xmlresult1 := dbms_xmlgen.getXML(qryCtx1, dbms_xmlgen.NONE);

356: || 'FROM psp_report_templates_h prth WHERE prth.request_id = ' || TO_CHAR(l_request_id);
357: qryCtx1 := dbms_xmlgen.newContext(query1);
358: dbms_xmlgen.setRowTag(qryCtx1, NULL);
359: dbms_xmlgen.setRowSetTag(qryCtx1, 'G_REPORT_INFO');
360: xmlresult1 := dbms_xmlgen.getXML(qryCtx1, dbms_xmlgen.NONE);
361: dbms_xmlgen.closecontext(qryctx1);
362: l_xml := xmlresult1;
363: dbms_lob.write(l_xml, length(l_report_info), 1, l_report_info);
364: l_resultOffset := DBMS_LOB.INSTR(xmlresult1,'>');

Line 361: dbms_xmlgen.closecontext(qryctx1);

357: qryCtx1 := dbms_xmlgen.newContext(query1);
358: dbms_xmlgen.setRowTag(qryCtx1, NULL);
359: dbms_xmlgen.setRowSetTag(qryCtx1, 'G_REPORT_INFO');
360: xmlresult1 := dbms_xmlgen.getXML(qryCtx1, dbms_xmlgen.NONE);
361: dbms_xmlgen.closecontext(qryctx1);
362: l_xml := xmlresult1;
363: dbms_lob.write(l_xml, length(l_report_info), 1, l_report_info);
364: l_resultOffset := DBMS_LOB.INSTR(xmlresult1,'>');
365: dbms_lob.copy(l_xml, xmlresult1, dbms_lob.getlength(xmlresult1) - l_resultOffset, length(l_report_info), l_resultOffset +1);

Line 512: qryCtx1 := dbms_xmlgen.newContext(query2);

508: ||' AND wfna.NAME(+) =''WF_NOTE'' AND wfna.NOTIFICATION_ID(+) = pera.NOTIFICATION_ID '
509: || ' ORDER BY approver_order_num DESC';
510: --Bug 4334816: END
511:
512: qryCtx1 := dbms_xmlgen.newContext(query2);
513: dbms_xmlgen.setRowTag(qryCtx1, 'G_APPROVER');
514: dbms_xmlgen.setRowSetTag(qryCtx1, 'LIST_G_APPROVER');
515: xmlresult1 := dbms_xmlgen.getXML(qryCtx1, dbms_xmlgen.NONE);
516: dbms_xmlgen.closecontext(qryctx1);

Line 513: dbms_xmlgen.setRowTag(qryCtx1, 'G_APPROVER');

509: || ' ORDER BY approver_order_num DESC';
510: --Bug 4334816: END
511:
512: qryCtx1 := dbms_xmlgen.newContext(query2);
513: dbms_xmlgen.setRowTag(qryCtx1, 'G_APPROVER');
514: dbms_xmlgen.setRowSetTag(qryCtx1, 'LIST_G_APPROVER');
515: xmlresult1 := dbms_xmlgen.getXML(qryCtx1, dbms_xmlgen.NONE);
516: dbms_xmlgen.closecontext(qryctx1);
517: IF (dbms_lob.getlength(xmlresult1) > 0) THEN

Line 514: dbms_xmlgen.setRowSetTag(qryCtx1, 'LIST_G_APPROVER');

510: --Bug 4334816: END
511:
512: qryCtx1 := dbms_xmlgen.newContext(query2);
513: dbms_xmlgen.setRowTag(qryCtx1, 'G_APPROVER');
514: dbms_xmlgen.setRowSetTag(qryCtx1, 'LIST_G_APPROVER');
515: xmlresult1 := dbms_xmlgen.getXML(qryCtx1, dbms_xmlgen.NONE);
516: dbms_xmlgen.closecontext(qryctx1);
517: IF (dbms_lob.getlength(xmlresult1) > 0) THEN
518: l_resultOffset := DBMS_LOB.INSTR(xmlresult1,'>');

Line 515: xmlresult1 := dbms_xmlgen.getXML(qryCtx1, dbms_xmlgen.NONE);

511:
512: qryCtx1 := dbms_xmlgen.newContext(query2);
513: dbms_xmlgen.setRowTag(qryCtx1, 'G_APPROVER');
514: dbms_xmlgen.setRowSetTag(qryCtx1, 'LIST_G_APPROVER');
515: xmlresult1 := dbms_xmlgen.getXML(qryCtx1, dbms_xmlgen.NONE);
516: dbms_xmlgen.closecontext(qryctx1);
517: IF (dbms_lob.getlength(xmlresult1) > 0) THEN
518: l_resultOffset := DBMS_LOB.INSTR(xmlresult1,'>');
519: dbms_lob.copy(l_xml, xmlresult1, dbms_lob.getlength(xmlresult1) - l_resultOffset, dbms_lob.getlength(l_xml), l_resultOffset +1);

Line 516: dbms_xmlgen.closecontext(qryctx1);

512: qryCtx1 := dbms_xmlgen.newContext(query2);
513: dbms_xmlgen.setRowTag(qryCtx1, 'G_APPROVER');
514: dbms_xmlgen.setRowSetTag(qryCtx1, 'LIST_G_APPROVER');
515: xmlresult1 := dbms_xmlgen.getXML(qryCtx1, dbms_xmlgen.NONE);
516: dbms_xmlgen.closecontext(qryctx1);
517: IF (dbms_lob.getlength(xmlresult1) > 0) THEN
518: l_resultOffset := DBMS_LOB.INSTR(xmlresult1,'>');
519: dbms_lob.copy(l_xml, xmlresult1, dbms_lob.getlength(xmlresult1) - l_resultOffset, dbms_lob.getlength(l_xml), l_resultOffset +1);
520: END IF;

Line 565: qryCtx1 dbms_xmlgen.ctxType;

561: p_emp_primary_org_name IN VARCHAR2,
562: p_emp_primary_org_id IN NUMBER,
563: p_currency_code IN VARCHAR2
564: ) RETURN CLOB IS
565: qryCtx1 dbms_xmlgen.ctxType;
566: qryCtx2 dbms_xmlquery.ctxType;
567: query1 VARCHAR2(8000);
568: xmlresult1 CLOB;
569: l_xml CLOB;

Line 738: xmlresult1 := dbms_xmlquery.getXML(qryCtx2, dbms_xmlgen.NONE);

734:
735: qryCtx2 := dbms_xmlquery.newContext(query1);
736: dbms_xmlquery.setRowTag(qryCtx2, NULL);
737: dbms_xmlquery.setRowSetTag(qryCtx2, 'EMP_DFF');
738: xmlresult1 := dbms_xmlquery.getXML(qryCtx2, dbms_xmlgen.NONE);
739: dbms_xmlquery.closecontext(qryctx2);
740: l_xml := xmlresult1;
741: dbms_lob.write(l_xml, length(l_employee_info), 1, l_employee_info);
742: l_resultOffset := DBMS_LOB.INSTR(xmlresult1,'>');

Line 757: qryCtx1 := dbms_xmlgen.newContext(query1);

753: || 'TRIM(TO_CHAR(SUM(NVL(pera.actual_cost_share, 0)), ''999G990D00'')) total_emp_actual_cost_share '
754: || 'FROM psp_eff_report_details perd, psp_eff_report_approvals pera where '
755: || 'perd.effort_report_detail_id = pera.effort_report_detail_id (+) AND perd.effort_report_id = '
756: || TO_CHAR(p_effort_report_id) || ' AND NVL(pera.approval_status, ''A'') IN ( ''P'', ''A'')' || l_er_check;
757: qryCtx1 := dbms_xmlgen.newContext(query1);
758: dbms_xmlgen.setRowTag(qryCtx1, NULL);
759: dbms_xmlgen.setRowSetTag(qryCtx1, 'TOTAL_EMP');
760: xmlresult1 := dbms_xmlgen.getXML(qryCtx1, dbms_xmlgen.NONE);
761: dbms_xmlgen.closecontext(qryctx1);

Line 758: dbms_xmlgen.setRowTag(qryCtx1, NULL);

754: || 'FROM psp_eff_report_details perd, psp_eff_report_approvals pera where '
755: || 'perd.effort_report_detail_id = pera.effort_report_detail_id (+) AND perd.effort_report_id = '
756: || TO_CHAR(p_effort_report_id) || ' AND NVL(pera.approval_status, ''A'') IN ( ''P'', ''A'')' || l_er_check;
757: qryCtx1 := dbms_xmlgen.newContext(query1);
758: dbms_xmlgen.setRowTag(qryCtx1, NULL);
759: dbms_xmlgen.setRowSetTag(qryCtx1, 'TOTAL_EMP');
760: xmlresult1 := dbms_xmlgen.getXML(qryCtx1, dbms_xmlgen.NONE);
761: dbms_xmlgen.closecontext(qryctx1);
762: l_resultOffset := DBMS_LOB.INSTR(xmlresult1,'>');

Line 759: dbms_xmlgen.setRowSetTag(qryCtx1, 'TOTAL_EMP');

755: || 'perd.effort_report_detail_id = pera.effort_report_detail_id (+) AND perd.effort_report_id = '
756: || TO_CHAR(p_effort_report_id) || ' AND NVL(pera.approval_status, ''A'') IN ( ''P'', ''A'')' || l_er_check;
757: qryCtx1 := dbms_xmlgen.newContext(query1);
758: dbms_xmlgen.setRowTag(qryCtx1, NULL);
759: dbms_xmlgen.setRowSetTag(qryCtx1, 'TOTAL_EMP');
760: xmlresult1 := dbms_xmlgen.getXML(qryCtx1, dbms_xmlgen.NONE);
761: dbms_xmlgen.closecontext(qryctx1);
762: l_resultOffset := DBMS_LOB.INSTR(xmlresult1,'>');
763: dbms_lob.copy(l_xml, xmlresult1, dbms_lob.getlength(xmlresult1) - l_resultOffset, dbms_lob.getlength(l_xml), l_resultOffset +1);

Line 760: xmlresult1 := dbms_xmlgen.getXML(qryCtx1, dbms_xmlgen.NONE);

756: || TO_CHAR(p_effort_report_id) || ' AND NVL(pera.approval_status, ''A'') IN ( ''P'', ''A'')' || l_er_check;
757: qryCtx1 := dbms_xmlgen.newContext(query1);
758: dbms_xmlgen.setRowTag(qryCtx1, NULL);
759: dbms_xmlgen.setRowSetTag(qryCtx1, 'TOTAL_EMP');
760: xmlresult1 := dbms_xmlgen.getXML(qryCtx1, dbms_xmlgen.NONE);
761: dbms_xmlgen.closecontext(qryctx1);
762: l_resultOffset := DBMS_LOB.INSTR(xmlresult1,'>');
763: dbms_lob.copy(l_xml, xmlresult1, dbms_lob.getlength(xmlresult1) - l_resultOffset, dbms_lob.getlength(l_xml), l_resultOffset +1);
764: dbms_lob.writeappend(l_xml, length(' '), ' ');

Line 761: dbms_xmlgen.closecontext(qryctx1);

757: qryCtx1 := dbms_xmlgen.newContext(query1);
758: dbms_xmlgen.setRowTag(qryCtx1, NULL);
759: dbms_xmlgen.setRowSetTag(qryCtx1, 'TOTAL_EMP');
760: xmlresult1 := dbms_xmlgen.getXML(qryCtx1, dbms_xmlgen.NONE);
761: dbms_xmlgen.closecontext(qryctx1);
762: l_resultOffset := DBMS_LOB.INSTR(xmlresult1,'>');
763: dbms_lob.copy(l_xml, xmlresult1, dbms_lob.getlength(xmlresult1) - l_resultOffset, dbms_lob.getlength(l_xml), l_resultOffset +1);
764: dbms_lob.writeappend(l_xml, length(' '), ' ');
765:

Line 791: qryCtx1 := dbms_xmlgen.newContext(query1);

787: || 'TRIM(TO_CHAR(SUM(NVL(pera.actual_cost_share, 0)), ''999G990D00'')) total_asg_actual_cost_share '
788: || 'FROM psp_eff_report_details perd, psp_eff_report_approvals pera where '
789: || 'perd.effort_report_detail_id = pera.effort_report_detail_id (+) AND perd.effort_report_id = '
790: || TO_CHAR(p_effort_report_id) || ' AND NVL(pera.approval_status, ''A'') IN (''P'', ''A'')' || l_assignment_check;
791: qryCtx1 := dbms_xmlgen.newContext(query1);
792: dbms_xmlgen.setRowTag(qryCtx1, NULL);
793: dbms_xmlgen.setRowSetTag(qryCtx1, 'TOTAL_ASG');
794: xmlresult1 := dbms_xmlgen.getXML(qryCtx1, dbms_xmlgen.NONE);
795: dbms_xmlgen.closecontext(qryctx1);

Line 792: dbms_xmlgen.setRowTag(qryCtx1, NULL);

788: || 'FROM psp_eff_report_details perd, psp_eff_report_approvals pera where '
789: || 'perd.effort_report_detail_id = pera.effort_report_detail_id (+) AND perd.effort_report_id = '
790: || TO_CHAR(p_effort_report_id) || ' AND NVL(pera.approval_status, ''A'') IN (''P'', ''A'')' || l_assignment_check;
791: qryCtx1 := dbms_xmlgen.newContext(query1);
792: dbms_xmlgen.setRowTag(qryCtx1, NULL);
793: dbms_xmlgen.setRowSetTag(qryCtx1, 'TOTAL_ASG');
794: xmlresult1 := dbms_xmlgen.getXML(qryCtx1, dbms_xmlgen.NONE);
795: dbms_xmlgen.closecontext(qryctx1);
796: l_resultOffset := DBMS_LOB.INSTR(xmlresult1,'>');

Line 793: dbms_xmlgen.setRowSetTag(qryCtx1, 'TOTAL_ASG');

789: || 'perd.effort_report_detail_id = pera.effort_report_detail_id (+) AND perd.effort_report_id = '
790: || TO_CHAR(p_effort_report_id) || ' AND NVL(pera.approval_status, ''A'') IN (''P'', ''A'')' || l_assignment_check;
791: qryCtx1 := dbms_xmlgen.newContext(query1);
792: dbms_xmlgen.setRowTag(qryCtx1, NULL);
793: dbms_xmlgen.setRowSetTag(qryCtx1, 'TOTAL_ASG');
794: xmlresult1 := dbms_xmlgen.getXML(qryCtx1, dbms_xmlgen.NONE);
795: dbms_xmlgen.closecontext(qryctx1);
796: l_resultOffset := DBMS_LOB.INSTR(xmlresult1,'>');
797: dbms_lob.copy(l_xml, xmlresult1, dbms_lob.getlength(xmlresult1) - l_resultOffset, dbms_lob.getlength(l_xml), l_resultOffset +1);

Line 794: xmlresult1 := dbms_xmlgen.getXML(qryCtx1, dbms_xmlgen.NONE);

790: || TO_CHAR(p_effort_report_id) || ' AND NVL(pera.approval_status, ''A'') IN (''P'', ''A'')' || l_assignment_check;
791: qryCtx1 := dbms_xmlgen.newContext(query1);
792: dbms_xmlgen.setRowTag(qryCtx1, NULL);
793: dbms_xmlgen.setRowSetTag(qryCtx1, 'TOTAL_ASG');
794: xmlresult1 := dbms_xmlgen.getXML(qryCtx1, dbms_xmlgen.NONE);
795: dbms_xmlgen.closecontext(qryctx1);
796: l_resultOffset := DBMS_LOB.INSTR(xmlresult1,'>');
797: dbms_lob.copy(l_xml, xmlresult1, dbms_lob.getlength(xmlresult1) - l_resultOffset, dbms_lob.getlength(l_xml), l_resultOffset +1);
798:

Line 795: dbms_xmlgen.closecontext(qryctx1);

791: qryCtx1 := dbms_xmlgen.newContext(query1);
792: dbms_xmlgen.setRowTag(qryCtx1, NULL);
793: dbms_xmlgen.setRowSetTag(qryCtx1, 'TOTAL_ASG');
794: xmlresult1 := dbms_xmlgen.getXML(qryCtx1, dbms_xmlgen.NONE);
795: dbms_xmlgen.closecontext(qryctx1);
796: l_resultOffset := DBMS_LOB.INSTR(xmlresult1,'>');
797: dbms_lob.copy(l_xml, xmlresult1, dbms_lob.getlength(xmlresult1) - l_resultOffset, dbms_lob.getlength(l_xml), l_resultOffset +1);
798:
799:

Line 833: qryCtx1 := dbms_xmlgen.newContext(query1);

829: || 'AND prth.request_id = ' || p_request_id
830: || ' AND perd.effort_report_detail_id = pera.effort_report_detail_id (+) AND effort_report_id = '
831: || TO_CHAR(p_effort_report_id) || ' AND NVL(pera.approval_status, ''A'') IN (''P'', ''A'')' || l_assignment_check;
832:
833: qryCtx1 := dbms_xmlgen.newContext(query1);
834: dbms_xmlgen.setRowTag(qryCtx1, 'G_CATEGORY');
835: dbms_xmlgen.setRowSetTag(qryCtx1, NULL);
836: xmlresult1 := dbms_xmlgen.getXML(qryCtx1, dbms_xmlgen.NONE);
837: dbms_xmlgen.closecontext(qryctx1);

Line 834: dbms_xmlgen.setRowTag(qryCtx1, 'G_CATEGORY');

830: || ' AND perd.effort_report_detail_id = pera.effort_report_detail_id (+) AND effort_report_id = '
831: || TO_CHAR(p_effort_report_id) || ' AND NVL(pera.approval_status, ''A'') IN (''P'', ''A'')' || l_assignment_check;
832:
833: qryCtx1 := dbms_xmlgen.newContext(query1);
834: dbms_xmlgen.setRowTag(qryCtx1, 'G_CATEGORY');
835: dbms_xmlgen.setRowSetTag(qryCtx1, NULL);
836: xmlresult1 := dbms_xmlgen.getXML(qryCtx1, dbms_xmlgen.NONE);
837: dbms_xmlgen.closecontext(qryctx1);
838: xmlresult1 := SUBSTR(xmlresult1, 1, LENGTH(xmlresult1) - 15);

Line 835: dbms_xmlgen.setRowSetTag(qryCtx1, NULL);

831: || TO_CHAR(p_effort_report_id) || ' AND NVL(pera.approval_status, ''A'') IN (''P'', ''A'')' || l_assignment_check;
832:
833: qryCtx1 := dbms_xmlgen.newContext(query1);
834: dbms_xmlgen.setRowTag(qryCtx1, 'G_CATEGORY');
835: dbms_xmlgen.setRowSetTag(qryCtx1, NULL);
836: xmlresult1 := dbms_xmlgen.getXML(qryCtx1, dbms_xmlgen.NONE);
837: dbms_xmlgen.closecontext(qryctx1);
838: xmlresult1 := SUBSTR(xmlresult1, 1, LENGTH(xmlresult1) - 15);
839: -- l_resultOffset := DBMS_LOB.INSTR(xmlresult1,'>');

Line 836: xmlresult1 := dbms_xmlgen.getXML(qryCtx1, dbms_xmlgen.NONE);

832:
833: qryCtx1 := dbms_xmlgen.newContext(query1);
834: dbms_xmlgen.setRowTag(qryCtx1, 'G_CATEGORY');
835: dbms_xmlgen.setRowSetTag(qryCtx1, NULL);
836: xmlresult1 := dbms_xmlgen.getXML(qryCtx1, dbms_xmlgen.NONE);
837: dbms_xmlgen.closecontext(qryctx1);
838: xmlresult1 := SUBSTR(xmlresult1, 1, LENGTH(xmlresult1) - 15);
839: -- l_resultOffset := DBMS_LOB.INSTR(xmlresult1,'>');
840: dbms_lob.copy(l_xml, xmlresult1, dbms_lob.getlength(xmlresult1) - l_resultOffset, dbms_lob.getlength(l_xml), l_resultOffset +1);

Line 837: dbms_xmlgen.closecontext(qryctx1);

833: qryCtx1 := dbms_xmlgen.newContext(query1);
834: dbms_xmlgen.setRowTag(qryCtx1, 'G_CATEGORY');
835: dbms_xmlgen.setRowSetTag(qryCtx1, NULL);
836: xmlresult1 := dbms_xmlgen.getXML(qryCtx1, dbms_xmlgen.NONE);
837: dbms_xmlgen.closecontext(qryctx1);
838: xmlresult1 := SUBSTR(xmlresult1, 1, LENGTH(xmlresult1) - 15);
839: -- l_resultOffset := DBMS_LOB.INSTR(xmlresult1,'>');
840: dbms_lob.copy(l_xml, xmlresult1, dbms_lob.getlength(xmlresult1) - l_resultOffset, dbms_lob.getlength(l_xml), l_resultOffset +1);
841:

Line 861: qryCtx1 := dbms_xmlgen.newContext(query1);

857: || 'psp_eff_report_approvals pera WHERE perd.effort_report_detail_id = pera.effort_report_detail_id (+) AND effort_report_id = '
858: || TO_CHAR(p_effort_report_id) || ' AND NVL(pera.approval_status, ''A'') IN (''P'', ''A'')'
859: || 'AND perd.grouping_category = ''' || l_grouping_category || ''' '
860: || l_assignment_check || l_sort_option1;
861: qryCtx1 := dbms_xmlgen.newContext(query1);
862: dbms_xmlgen.setRowTag(qryCtx1, 'G_CATEGORYDETAILS');
863: dbms_xmlgen.setRowSetTag(qryCtx1, 'LIST_G_CATEGORYDETAILS');
864: xmlresult1 := dbms_xmlgen.getXML(qryCtx1, dbms_xmlgen.NONE);
865: dbms_xmlgen.closecontext(qryctx1);

Line 862: dbms_xmlgen.setRowTag(qryCtx1, 'G_CATEGORYDETAILS');

858: || TO_CHAR(p_effort_report_id) || ' AND NVL(pera.approval_status, ''A'') IN (''P'', ''A'')'
859: || 'AND perd.grouping_category = ''' || l_grouping_category || ''' '
860: || l_assignment_check || l_sort_option1;
861: qryCtx1 := dbms_xmlgen.newContext(query1);
862: dbms_xmlgen.setRowTag(qryCtx1, 'G_CATEGORYDETAILS');
863: dbms_xmlgen.setRowSetTag(qryCtx1, 'LIST_G_CATEGORYDETAILS');
864: xmlresult1 := dbms_xmlgen.getXML(qryCtx1, dbms_xmlgen.NONE);
865: dbms_xmlgen.closecontext(qryctx1);
866: IF (dbms_lob.getlength(xmlresult1) > 0) THEN

Line 863: dbms_xmlgen.setRowSetTag(qryCtx1, 'LIST_G_CATEGORYDETAILS');

859: || 'AND perd.grouping_category = ''' || l_grouping_category || ''' '
860: || l_assignment_check || l_sort_option1;
861: qryCtx1 := dbms_xmlgen.newContext(query1);
862: dbms_xmlgen.setRowTag(qryCtx1, 'G_CATEGORYDETAILS');
863: dbms_xmlgen.setRowSetTag(qryCtx1, 'LIST_G_CATEGORYDETAILS');
864: xmlresult1 := dbms_xmlgen.getXML(qryCtx1, dbms_xmlgen.NONE);
865: dbms_xmlgen.closecontext(qryctx1);
866: IF (dbms_lob.getlength(xmlresult1) > 0) THEN
867: l_resultOffset := DBMS_LOB.INSTR(xmlresult1,'>');

Line 864: xmlresult1 := dbms_xmlgen.getXML(qryCtx1, dbms_xmlgen.NONE);

860: || l_assignment_check || l_sort_option1;
861: qryCtx1 := dbms_xmlgen.newContext(query1);
862: dbms_xmlgen.setRowTag(qryCtx1, 'G_CATEGORYDETAILS');
863: dbms_xmlgen.setRowSetTag(qryCtx1, 'LIST_G_CATEGORYDETAILS');
864: xmlresult1 := dbms_xmlgen.getXML(qryCtx1, dbms_xmlgen.NONE);
865: dbms_xmlgen.closecontext(qryctx1);
866: IF (dbms_lob.getlength(xmlresult1) > 0) THEN
867: l_resultOffset := DBMS_LOB.INSTR(xmlresult1,'>');
868: dbms_lob.copy(l_xml, xmlresult1, dbms_lob.getlength(xmlresult1) - l_resultOffset, dbms_lob.getlength(l_xml), l_resultOffset +1);

Line 865: dbms_xmlgen.closecontext(qryctx1);

861: qryCtx1 := dbms_xmlgen.newContext(query1);
862: dbms_xmlgen.setRowTag(qryCtx1, 'G_CATEGORYDETAILS');
863: dbms_xmlgen.setRowSetTag(qryCtx1, 'LIST_G_CATEGORYDETAILS');
864: xmlresult1 := dbms_xmlgen.getXML(qryCtx1, dbms_xmlgen.NONE);
865: dbms_xmlgen.closecontext(qryctx1);
866: IF (dbms_lob.getlength(xmlresult1) > 0) THEN
867: l_resultOffset := DBMS_LOB.INSTR(xmlresult1,'>');
868: dbms_lob.copy(l_xml, xmlresult1, dbms_lob.getlength(xmlresult1) - l_resultOffset, dbms_lob.getlength(l_xml), l_resultOffset +1);
869: END IF;

Line 894: qryCtx1 := dbms_xmlgen.newContext(query1);

890: || '(SELECT 1 FROM pa_projects_all ppa, gms_project_types_all gpta WHERE gpta.project_type = ppa.project_type AND '
891: || 'ppa.project_id = perd.project_id AND ppa.project_type <> ''AWARD_PROJECT'' AND NVL(gpta.sponsored_flag, ''N'') ='
892: || '''Y'') AND perd.effort_report_detail_id = pera.effort_report_detail_id (+) AND effort_report_id = '
893: || TO_CHAR(p_effort_report_id) || ' AND NVL(pera.approval_status, ''A'') IN (''P'', ''A'')' || l_assignment_check;
894: qryCtx1 := dbms_xmlgen.newContext(query1);
895: dbms_xmlgen.setRowTag(qryCtx1, NULL);
896: dbms_xmlgen.setRowSetTag(qryCtx1, 'TOTAL_SPONSORED');
897: xmlresult1 := dbms_xmlgen.getXML(qryCtx1, dbms_xmlgen.NONE);
898: dbms_xmlgen.closecontext(qryctx1);

Line 895: dbms_xmlgen.setRowTag(qryCtx1, NULL);

891: || 'ppa.project_id = perd.project_id AND ppa.project_type <> ''AWARD_PROJECT'' AND NVL(gpta.sponsored_flag, ''N'') ='
892: || '''Y'') AND perd.effort_report_detail_id = pera.effort_report_detail_id (+) AND effort_report_id = '
893: || TO_CHAR(p_effort_report_id) || ' AND NVL(pera.approval_status, ''A'') IN (''P'', ''A'')' || l_assignment_check;
894: qryCtx1 := dbms_xmlgen.newContext(query1);
895: dbms_xmlgen.setRowTag(qryCtx1, NULL);
896: dbms_xmlgen.setRowSetTag(qryCtx1, 'TOTAL_SPONSORED');
897: xmlresult1 := dbms_xmlgen.getXML(qryCtx1, dbms_xmlgen.NONE);
898: dbms_xmlgen.closecontext(qryctx1);
899: l_resultOffset := DBMS_LOB.INSTR(xmlresult1,'>');

Line 896: dbms_xmlgen.setRowSetTag(qryCtx1, 'TOTAL_SPONSORED');

892: || '''Y'') AND perd.effort_report_detail_id = pera.effort_report_detail_id (+) AND effort_report_id = '
893: || TO_CHAR(p_effort_report_id) || ' AND NVL(pera.approval_status, ''A'') IN (''P'', ''A'')' || l_assignment_check;
894: qryCtx1 := dbms_xmlgen.newContext(query1);
895: dbms_xmlgen.setRowTag(qryCtx1, NULL);
896: dbms_xmlgen.setRowSetTag(qryCtx1, 'TOTAL_SPONSORED');
897: xmlresult1 := dbms_xmlgen.getXML(qryCtx1, dbms_xmlgen.NONE);
898: dbms_xmlgen.closecontext(qryctx1);
899: l_resultOffset := DBMS_LOB.INSTR(xmlresult1,'>');
900: dbms_lob.copy(l_xml, xmlresult1, dbms_lob.getlength(xmlresult1) - l_resultOffset, dbms_lob.getlength(l_xml), l_resultOffset +1);

Line 897: xmlresult1 := dbms_xmlgen.getXML(qryCtx1, dbms_xmlgen.NONE);

893: || TO_CHAR(p_effort_report_id) || ' AND NVL(pera.approval_status, ''A'') IN (''P'', ''A'')' || l_assignment_check;
894: qryCtx1 := dbms_xmlgen.newContext(query1);
895: dbms_xmlgen.setRowTag(qryCtx1, NULL);
896: dbms_xmlgen.setRowSetTag(qryCtx1, 'TOTAL_SPONSORED');
897: xmlresult1 := dbms_xmlgen.getXML(qryCtx1, dbms_xmlgen.NONE);
898: dbms_xmlgen.closecontext(qryctx1);
899: l_resultOffset := DBMS_LOB.INSTR(xmlresult1,'>');
900: dbms_lob.copy(l_xml, xmlresult1, dbms_lob.getlength(xmlresult1) - l_resultOffset, dbms_lob.getlength(l_xml), l_resultOffset +1);
901:

Line 898: dbms_xmlgen.closecontext(qryctx1);

894: qryCtx1 := dbms_xmlgen.newContext(query1);
895: dbms_xmlgen.setRowTag(qryCtx1, NULL);
896: dbms_xmlgen.setRowSetTag(qryCtx1, 'TOTAL_SPONSORED');
897: xmlresult1 := dbms_xmlgen.getXML(qryCtx1, dbms_xmlgen.NONE);
898: dbms_xmlgen.closecontext(qryctx1);
899: l_resultOffset := DBMS_LOB.INSTR(xmlresult1,'>');
900: dbms_lob.copy(l_xml, xmlresult1, dbms_lob.getlength(xmlresult1) - l_resultOffset, dbms_lob.getlength(l_xml), l_resultOffset +1);
901:
902: query1 := 'select TRIM(TO_CHAR(SUM(NVL(proposed_salary_amt, 0)), ''' || l_num25_fmask || ''')) total_nspon_proposed_salary, '

Line 916: qryCtx1 := dbms_xmlgen.newContext(query1);

912: || '(SELECT 1 FROM pa_projects_all ppa, gms_project_types_all gpta WHERE gpta.project_type (+) = ppa.project_type AND '
913: || 'ppa.project_id = perd.project_id AND ppa.project_type <> ''AWARD_PROJECT'' AND NVL(gpta.sponsored_flag, ''N'') ='
914: || '''N'') AND perd.effort_report_detail_id = pera.effort_report_detail_id (+) AND effort_report_id = '
915: || TO_CHAR(p_effort_report_id) || ' AND NVL(pera.approval_status, ''A'') IN (''P'', ''A'')' || l_assignment_check;
916: qryCtx1 := dbms_xmlgen.newContext(query1);
917: dbms_xmlgen.setRowTag(qryCtx1, NULL);
918: dbms_xmlgen.setRowSetTag(qryCtx1, 'TOTAL_NON_SPONSORED');
919: xmlresult1 := dbms_xmlgen.getXML(qryCtx1, dbms_xmlgen.NONE);
920: dbms_xmlgen.closecontext(qryctx1);

Line 917: dbms_xmlgen.setRowTag(qryCtx1, NULL);

913: || 'ppa.project_id = perd.project_id AND ppa.project_type <> ''AWARD_PROJECT'' AND NVL(gpta.sponsored_flag, ''N'') ='
914: || '''N'') AND perd.effort_report_detail_id = pera.effort_report_detail_id (+) AND effort_report_id = '
915: || TO_CHAR(p_effort_report_id) || ' AND NVL(pera.approval_status, ''A'') IN (''P'', ''A'')' || l_assignment_check;
916: qryCtx1 := dbms_xmlgen.newContext(query1);
917: dbms_xmlgen.setRowTag(qryCtx1, NULL);
918: dbms_xmlgen.setRowSetTag(qryCtx1, 'TOTAL_NON_SPONSORED');
919: xmlresult1 := dbms_xmlgen.getXML(qryCtx1, dbms_xmlgen.NONE);
920: dbms_xmlgen.closecontext(qryctx1);
921: l_resultOffset := DBMS_LOB.INSTR(xmlresult1,'>');

Line 918: dbms_xmlgen.setRowSetTag(qryCtx1, 'TOTAL_NON_SPONSORED');

914: || '''N'') AND perd.effort_report_detail_id = pera.effort_report_detail_id (+) AND effort_report_id = '
915: || TO_CHAR(p_effort_report_id) || ' AND NVL(pera.approval_status, ''A'') IN (''P'', ''A'')' || l_assignment_check;
916: qryCtx1 := dbms_xmlgen.newContext(query1);
917: dbms_xmlgen.setRowTag(qryCtx1, NULL);
918: dbms_xmlgen.setRowSetTag(qryCtx1, 'TOTAL_NON_SPONSORED');
919: xmlresult1 := dbms_xmlgen.getXML(qryCtx1, dbms_xmlgen.NONE);
920: dbms_xmlgen.closecontext(qryctx1);
921: l_resultOffset := DBMS_LOB.INSTR(xmlresult1,'>');
922: dbms_lob.copy(l_xml, xmlresult1, dbms_lob.getlength(xmlresult1) - l_resultOffset, dbms_lob.getlength(l_xml), l_resultOffset +1);

Line 919: xmlresult1 := dbms_xmlgen.getXML(qryCtx1, dbms_xmlgen.NONE);

915: || TO_CHAR(p_effort_report_id) || ' AND NVL(pera.approval_status, ''A'') IN (''P'', ''A'')' || l_assignment_check;
916: qryCtx1 := dbms_xmlgen.newContext(query1);
917: dbms_xmlgen.setRowTag(qryCtx1, NULL);
918: dbms_xmlgen.setRowSetTag(qryCtx1, 'TOTAL_NON_SPONSORED');
919: xmlresult1 := dbms_xmlgen.getXML(qryCtx1, dbms_xmlgen.NONE);
920: dbms_xmlgen.closecontext(qryctx1);
921: l_resultOffset := DBMS_LOB.INSTR(xmlresult1,'>');
922: dbms_lob.copy(l_xml, xmlresult1, dbms_lob.getlength(xmlresult1) - l_resultOffset, dbms_lob.getlength(l_xml), l_resultOffset +1);
923:

Line 920: dbms_xmlgen.closecontext(qryctx1);

916: qryCtx1 := dbms_xmlgen.newContext(query1);
917: dbms_xmlgen.setRowTag(qryCtx1, NULL);
918: dbms_xmlgen.setRowSetTag(qryCtx1, 'TOTAL_NON_SPONSORED');
919: xmlresult1 := dbms_xmlgen.getXML(qryCtx1, dbms_xmlgen.NONE);
920: dbms_xmlgen.closecontext(qryctx1);
921: l_resultOffset := DBMS_LOB.INSTR(xmlresult1,'>');
922: dbms_lob.copy(l_xml, xmlresult1, dbms_lob.getlength(xmlresult1) - l_resultOffset, dbms_lob.getlength(l_xml), l_resultOffset +1);
923:
924: query1 := 'select TRIM(TO_CHAR(SUM(NVL(proposed_salary_amt, 0)), ''' || l_num25_fmask || ''')) total_gl_proposed_salary, '

Line 944: qryCtx1 := dbms_xmlgen.newContext(query1);

940: || 'perd.gl_segment24 IS NOT NULL OR perd.gl_segment25 IS NOT NULL OR perd.gl_segment26 IS NOT NULL OR '
941: || 'perd.gl_segment27 IS NOT NULL OR perd.gl_segment28 IS NOT NULL OR perd.gl_segment29 IS NOT NULL OR '
942: || 'perd.gl_segment30 IS NOT NULL) AND effort_report_id = ' || TO_CHAR(p_effort_report_id)
943: || ' AND NVL(pera.approval_status, ''A'') IN (''P'', ''A'')' || l_assignment_check;
944: qryCtx1 := dbms_xmlgen.newContext(query1);
945: dbms_xmlgen.setRowTag(qryCtx1, NULL);
946: dbms_xmlgen.setRowSetTag(qryCtx1, 'TOTAL_GL');
947: xmlresult1 := dbms_xmlgen.getXML(qryCtx1, dbms_xmlgen.NONE);
948: dbms_xmlgen.closecontext(qryctx1);

Line 945: dbms_xmlgen.setRowTag(qryCtx1, NULL);

941: || 'perd.gl_segment27 IS NOT NULL OR perd.gl_segment28 IS NOT NULL OR perd.gl_segment29 IS NOT NULL OR '
942: || 'perd.gl_segment30 IS NOT NULL) AND effort_report_id = ' || TO_CHAR(p_effort_report_id)
943: || ' AND NVL(pera.approval_status, ''A'') IN (''P'', ''A'')' || l_assignment_check;
944: qryCtx1 := dbms_xmlgen.newContext(query1);
945: dbms_xmlgen.setRowTag(qryCtx1, NULL);
946: dbms_xmlgen.setRowSetTag(qryCtx1, 'TOTAL_GL');
947: xmlresult1 := dbms_xmlgen.getXML(qryCtx1, dbms_xmlgen.NONE);
948: dbms_xmlgen.closecontext(qryctx1);
949: l_resultOffset := DBMS_LOB.INSTR(xmlresult1,'>');

Line 946: dbms_xmlgen.setRowSetTag(qryCtx1, 'TOTAL_GL');

942: || 'perd.gl_segment30 IS NOT NULL) AND effort_report_id = ' || TO_CHAR(p_effort_report_id)
943: || ' AND NVL(pera.approval_status, ''A'') IN (''P'', ''A'')' || l_assignment_check;
944: qryCtx1 := dbms_xmlgen.newContext(query1);
945: dbms_xmlgen.setRowTag(qryCtx1, NULL);
946: dbms_xmlgen.setRowSetTag(qryCtx1, 'TOTAL_GL');
947: xmlresult1 := dbms_xmlgen.getXML(qryCtx1, dbms_xmlgen.NONE);
948: dbms_xmlgen.closecontext(qryctx1);
949: l_resultOffset := DBMS_LOB.INSTR(xmlresult1,'>');
950: dbms_lob.copy(l_xml, xmlresult1, dbms_lob.getlength(xmlresult1) - l_resultOffset, dbms_lob.getlength(l_xml), l_resultOffset +1);

Line 947: xmlresult1 := dbms_xmlgen.getXML(qryCtx1, dbms_xmlgen.NONE);

943: || ' AND NVL(pera.approval_status, ''A'') IN (''P'', ''A'')' || l_assignment_check;
944: qryCtx1 := dbms_xmlgen.newContext(query1);
945: dbms_xmlgen.setRowTag(qryCtx1, NULL);
946: dbms_xmlgen.setRowSetTag(qryCtx1, 'TOTAL_GL');
947: xmlresult1 := dbms_xmlgen.getXML(qryCtx1, dbms_xmlgen.NONE);
948: dbms_xmlgen.closecontext(qryctx1);
949: l_resultOffset := DBMS_LOB.INSTR(xmlresult1,'>');
950: dbms_lob.copy(l_xml, xmlresult1, dbms_lob.getlength(xmlresult1) - l_resultOffset, dbms_lob.getlength(l_xml), l_resultOffset +1);
951:

Line 948: dbms_xmlgen.closecontext(qryctx1);

944: qryCtx1 := dbms_xmlgen.newContext(query1);
945: dbms_xmlgen.setRowTag(qryCtx1, NULL);
946: dbms_xmlgen.setRowSetTag(qryCtx1, 'TOTAL_GL');
947: xmlresult1 := dbms_xmlgen.getXML(qryCtx1, dbms_xmlgen.NONE);
948: dbms_xmlgen.closecontext(qryctx1);
949: l_resultOffset := DBMS_LOB.INSTR(xmlresult1,'>');
950: dbms_lob.copy(l_xml, xmlresult1, dbms_lob.getlength(xmlresult1) - l_resultOffset, dbms_lob.getlength(l_xml), l_resultOffset +1);
951:
952: query1 := 'SELECT TRIM(TO_CHAR(perd.ACTUAL_SALARY_AMT, ''' || l_num25_fmask || ''')) actual_salary_amt, '

Line 967: qryCtx1 := dbms_xmlgen.newContext(query1);

963: || '(SELECT 1 FROM pa_projects_all ppa, gms_project_types_all gpta WHERE gpta.project_type = ppa.project_type AND '
964: || 'ppa.project_id = perd.project_id AND ppa.project_type <> ''AWARD_PROJECT'' AND NVL(gpta.sponsored_flag, ''N'') ='
965: || '''Y'') AND perd.effort_report_detail_id = pera.effort_report_detail_id (+) AND effort_report_id = '
966: || TO_CHAR(p_effort_report_id) || ' AND NVL(pera.approval_status, ''A'') IN (''P'', ''A'')' || l_assignment_check || l_sort_option1;
967: qryCtx1 := dbms_xmlgen.newContext(query1);
968: dbms_xmlgen.setRowTag(qryCtx1, 'G_SPONSORED');
969: dbms_xmlgen.setRowSetTag(qryCtx1, 'LIST_G_SPONSORED');
970: xmlresult1 := dbms_xmlgen.getXML(qryCtx1, dbms_xmlgen.NONE);
971: dbms_xmlgen.closecontext(qryctx1);

Line 968: dbms_xmlgen.setRowTag(qryCtx1, 'G_SPONSORED');

964: || 'ppa.project_id = perd.project_id AND ppa.project_type <> ''AWARD_PROJECT'' AND NVL(gpta.sponsored_flag, ''N'') ='
965: || '''Y'') AND perd.effort_report_detail_id = pera.effort_report_detail_id (+) AND effort_report_id = '
966: || TO_CHAR(p_effort_report_id) || ' AND NVL(pera.approval_status, ''A'') IN (''P'', ''A'')' || l_assignment_check || l_sort_option1;
967: qryCtx1 := dbms_xmlgen.newContext(query1);
968: dbms_xmlgen.setRowTag(qryCtx1, 'G_SPONSORED');
969: dbms_xmlgen.setRowSetTag(qryCtx1, 'LIST_G_SPONSORED');
970: xmlresult1 := dbms_xmlgen.getXML(qryCtx1, dbms_xmlgen.NONE);
971: dbms_xmlgen.closecontext(qryctx1);
972: IF (dbms_lob.getlength(xmlresult1) > 0) THEN

Line 969: dbms_xmlgen.setRowSetTag(qryCtx1, 'LIST_G_SPONSORED');

965: || '''Y'') AND perd.effort_report_detail_id = pera.effort_report_detail_id (+) AND effort_report_id = '
966: || TO_CHAR(p_effort_report_id) || ' AND NVL(pera.approval_status, ''A'') IN (''P'', ''A'')' || l_assignment_check || l_sort_option1;
967: qryCtx1 := dbms_xmlgen.newContext(query1);
968: dbms_xmlgen.setRowTag(qryCtx1, 'G_SPONSORED');
969: dbms_xmlgen.setRowSetTag(qryCtx1, 'LIST_G_SPONSORED');
970: xmlresult1 := dbms_xmlgen.getXML(qryCtx1, dbms_xmlgen.NONE);
971: dbms_xmlgen.closecontext(qryctx1);
972: IF (dbms_lob.getlength(xmlresult1) > 0) THEN
973: l_resultOffset := DBMS_LOB.INSTR(xmlresult1,'>');

Line 970: xmlresult1 := dbms_xmlgen.getXML(qryCtx1, dbms_xmlgen.NONE);

966: || TO_CHAR(p_effort_report_id) || ' AND NVL(pera.approval_status, ''A'') IN (''P'', ''A'')' || l_assignment_check || l_sort_option1;
967: qryCtx1 := dbms_xmlgen.newContext(query1);
968: dbms_xmlgen.setRowTag(qryCtx1, 'G_SPONSORED');
969: dbms_xmlgen.setRowSetTag(qryCtx1, 'LIST_G_SPONSORED');
970: xmlresult1 := dbms_xmlgen.getXML(qryCtx1, dbms_xmlgen.NONE);
971: dbms_xmlgen.closecontext(qryctx1);
972: IF (dbms_lob.getlength(xmlresult1) > 0) THEN
973: l_resultOffset := DBMS_LOB.INSTR(xmlresult1,'>');
974: dbms_lob.copy(l_xml, xmlresult1, dbms_lob.getlength(xmlresult1) - l_resultOffset, dbms_lob.getlength(l_xml), l_resultOffset +1);

Line 971: dbms_xmlgen.closecontext(qryctx1);

967: qryCtx1 := dbms_xmlgen.newContext(query1);
968: dbms_xmlgen.setRowTag(qryCtx1, 'G_SPONSORED');
969: dbms_xmlgen.setRowSetTag(qryCtx1, 'LIST_G_SPONSORED');
970: xmlresult1 := dbms_xmlgen.getXML(qryCtx1, dbms_xmlgen.NONE);
971: dbms_xmlgen.closecontext(qryctx1);
972: IF (dbms_lob.getlength(xmlresult1) > 0) THEN
973: l_resultOffset := DBMS_LOB.INSTR(xmlresult1,'>');
974: dbms_lob.copy(l_xml, xmlresult1, dbms_lob.getlength(xmlresult1) - l_resultOffset, dbms_lob.getlength(l_xml), l_resultOffset +1);
975: END IF;

Line 992: qryCtx1 := dbms_xmlgen.newContext(query1);

988: || '(SELECT 1 FROM pa_projects_all ppa, gms_project_types_all gpta WHERE gpta.project_type(+) = ppa.project_type AND '
989: || 'ppa.project_id = perd.project_id AND ppa.project_type <> ''AWARD_PROJECT'' AND NVL(gpta.sponsored_flag, ''N'') ='
990: || '''N'') AND perd.effort_report_detail_id = pera.effort_report_detail_id (+) AND effort_report_id = '
991: || TO_CHAR(p_effort_report_id) || ' AND NVL(pera.approval_status, ''A'') IN (''P'', ''A'')' || l_assignment_check || l_sort_option1;
992: qryCtx1 := dbms_xmlgen.newContext(query1);
993: dbms_xmlgen.setRowTag(qryCtx1, 'G_NON_SPONSORED');
994: dbms_xmlgen.setRowSetTag(qryCtx1, 'LIST_G_NON_SPONSORED');
995: xmlresult1 := dbms_xmlgen.getXML(qryCtx1, dbms_xmlgen.NONE);
996: dbms_xmlgen.closecontext(qryctx1);

Line 993: dbms_xmlgen.setRowTag(qryCtx1, 'G_NON_SPONSORED');

989: || 'ppa.project_id = perd.project_id AND ppa.project_type <> ''AWARD_PROJECT'' AND NVL(gpta.sponsored_flag, ''N'') ='
990: || '''N'') AND perd.effort_report_detail_id = pera.effort_report_detail_id (+) AND effort_report_id = '
991: || TO_CHAR(p_effort_report_id) || ' AND NVL(pera.approval_status, ''A'') IN (''P'', ''A'')' || l_assignment_check || l_sort_option1;
992: qryCtx1 := dbms_xmlgen.newContext(query1);
993: dbms_xmlgen.setRowTag(qryCtx1, 'G_NON_SPONSORED');
994: dbms_xmlgen.setRowSetTag(qryCtx1, 'LIST_G_NON_SPONSORED');
995: xmlresult1 := dbms_xmlgen.getXML(qryCtx1, dbms_xmlgen.NONE);
996: dbms_xmlgen.closecontext(qryctx1);
997: IF (dbms_lob.getlength(xmlresult1) > 0) THEN

Line 994: dbms_xmlgen.setRowSetTag(qryCtx1, 'LIST_G_NON_SPONSORED');

990: || '''N'') AND perd.effort_report_detail_id = pera.effort_report_detail_id (+) AND effort_report_id = '
991: || TO_CHAR(p_effort_report_id) || ' AND NVL(pera.approval_status, ''A'') IN (''P'', ''A'')' || l_assignment_check || l_sort_option1;
992: qryCtx1 := dbms_xmlgen.newContext(query1);
993: dbms_xmlgen.setRowTag(qryCtx1, 'G_NON_SPONSORED');
994: dbms_xmlgen.setRowSetTag(qryCtx1, 'LIST_G_NON_SPONSORED');
995: xmlresult1 := dbms_xmlgen.getXML(qryCtx1, dbms_xmlgen.NONE);
996: dbms_xmlgen.closecontext(qryctx1);
997: IF (dbms_lob.getlength(xmlresult1) > 0) THEN
998: l_resultOffset := DBMS_LOB.INSTR(xmlresult1,'>');

Line 995: xmlresult1 := dbms_xmlgen.getXML(qryCtx1, dbms_xmlgen.NONE);

991: || TO_CHAR(p_effort_report_id) || ' AND NVL(pera.approval_status, ''A'') IN (''P'', ''A'')' || l_assignment_check || l_sort_option1;
992: qryCtx1 := dbms_xmlgen.newContext(query1);
993: dbms_xmlgen.setRowTag(qryCtx1, 'G_NON_SPONSORED');
994: dbms_xmlgen.setRowSetTag(qryCtx1, 'LIST_G_NON_SPONSORED');
995: xmlresult1 := dbms_xmlgen.getXML(qryCtx1, dbms_xmlgen.NONE);
996: dbms_xmlgen.closecontext(qryctx1);
997: IF (dbms_lob.getlength(xmlresult1) > 0) THEN
998: l_resultOffset := DBMS_LOB.INSTR(xmlresult1,'>');
999: dbms_lob.copy(l_xml, xmlresult1, dbms_lob.getlength(xmlresult1) - l_resultOffset, dbms_lob.getlength(l_xml), l_resultOffset +1);

Line 996: dbms_xmlgen.closecontext(qryctx1);

992: qryCtx1 := dbms_xmlgen.newContext(query1);
993: dbms_xmlgen.setRowTag(qryCtx1, 'G_NON_SPONSORED');
994: dbms_xmlgen.setRowSetTag(qryCtx1, 'LIST_G_NON_SPONSORED');
995: xmlresult1 := dbms_xmlgen.getXML(qryCtx1, dbms_xmlgen.NONE);
996: dbms_xmlgen.closecontext(qryctx1);
997: IF (dbms_lob.getlength(xmlresult1) > 0) THEN
998: l_resultOffset := DBMS_LOB.INSTR(xmlresult1,'>');
999: dbms_lob.copy(l_xml, xmlresult1, dbms_lob.getlength(xmlresult1) - l_resultOffset, dbms_lob.getlength(l_xml), l_resultOffset +1);
1000: END IF;

Line 1024: qryCtx1 := dbms_xmlgen.newContext(query1);

1020: || 'perd.gl_segment25 IS NOT NULL OR perd.gl_segment26 IS NOT NULL OR perd.gl_segment27 IS NOT NULL OR '
1021: || 'perd.gl_segment28 IS NOT NULL OR perd.gl_segment29 IS NOT NULL OR perd.gl_segment30 IS NOT NULL) '
1022: || 'AND effort_report_id = ' || TO_CHAR(p_effort_report_id) || ' AND NVL(pera.approval_status, ''A'') IN (''P'', ''A'')'
1023: || l_assignment_check || l_sort_option1;
1024: qryCtx1 := dbms_xmlgen.newContext(query1);
1025: dbms_xmlgen.setRowTag(qryCtx1, 'G_GL');
1026: dbms_xmlgen.setRowSetTag(qryCtx1, 'LIST_G_GL');
1027: xmlresult1 := dbms_xmlgen.getXML(qryCtx1, dbms_xmlgen.NONE);
1028: dbms_xmlgen.closecontext(qryctx1);

Line 1025: dbms_xmlgen.setRowTag(qryCtx1, 'G_GL');

1021: || 'perd.gl_segment28 IS NOT NULL OR perd.gl_segment29 IS NOT NULL OR perd.gl_segment30 IS NOT NULL) '
1022: || 'AND effort_report_id = ' || TO_CHAR(p_effort_report_id) || ' AND NVL(pera.approval_status, ''A'') IN (''P'', ''A'')'
1023: || l_assignment_check || l_sort_option1;
1024: qryCtx1 := dbms_xmlgen.newContext(query1);
1025: dbms_xmlgen.setRowTag(qryCtx1, 'G_GL');
1026: dbms_xmlgen.setRowSetTag(qryCtx1, 'LIST_G_GL');
1027: xmlresult1 := dbms_xmlgen.getXML(qryCtx1, dbms_xmlgen.NONE);
1028: dbms_xmlgen.closecontext(qryctx1);
1029: IF (dbms_lob.getlength(xmlresult1) > 0) THEN

Line 1026: dbms_xmlgen.setRowSetTag(qryCtx1, 'LIST_G_GL');

1022: || 'AND effort_report_id = ' || TO_CHAR(p_effort_report_id) || ' AND NVL(pera.approval_status, ''A'') IN (''P'', ''A'')'
1023: || l_assignment_check || l_sort_option1;
1024: qryCtx1 := dbms_xmlgen.newContext(query1);
1025: dbms_xmlgen.setRowTag(qryCtx1, 'G_GL');
1026: dbms_xmlgen.setRowSetTag(qryCtx1, 'LIST_G_GL');
1027: xmlresult1 := dbms_xmlgen.getXML(qryCtx1, dbms_xmlgen.NONE);
1028: dbms_xmlgen.closecontext(qryctx1);
1029: IF (dbms_lob.getlength(xmlresult1) > 0) THEN
1030: l_resultOffset := DBMS_LOB.INSTR(xmlresult1,'>');

Line 1027: xmlresult1 := dbms_xmlgen.getXML(qryCtx1, dbms_xmlgen.NONE);

1023: || l_assignment_check || l_sort_option1;
1024: qryCtx1 := dbms_xmlgen.newContext(query1);
1025: dbms_xmlgen.setRowTag(qryCtx1, 'G_GL');
1026: dbms_xmlgen.setRowSetTag(qryCtx1, 'LIST_G_GL');
1027: xmlresult1 := dbms_xmlgen.getXML(qryCtx1, dbms_xmlgen.NONE);
1028: dbms_xmlgen.closecontext(qryctx1);
1029: IF (dbms_lob.getlength(xmlresult1) > 0) THEN
1030: l_resultOffset := DBMS_LOB.INSTR(xmlresult1,'>');
1031: dbms_lob.copy(l_xml, xmlresult1, dbms_lob.getlength(xmlresult1) - l_resultOffset, dbms_lob.getlength(l_xml), l_resultOffset +1);

Line 1028: dbms_xmlgen.closecontext(qryctx1);

1024: qryCtx1 := dbms_xmlgen.newContext(query1);
1025: dbms_xmlgen.setRowTag(qryCtx1, 'G_GL');
1026: dbms_xmlgen.setRowSetTag(qryCtx1, 'LIST_G_GL');
1027: xmlresult1 := dbms_xmlgen.getXML(qryCtx1, dbms_xmlgen.NONE);
1028: dbms_xmlgen.closecontext(qryctx1);
1029: IF (dbms_lob.getlength(xmlresult1) > 0) THEN
1030: l_resultOffset := DBMS_LOB.INSTR(xmlresult1,'>');
1031: dbms_lob.copy(l_xml, xmlresult1, dbms_lob.getlength(xmlresult1) - l_resultOffset, dbms_lob.getlength(l_xml), l_resultOffset +1);
1032: END IF;

Line 1077: qryCtx1 := dbms_xmlgen.newContext(query1);

1073:
1074: END IF;
1075: --Bug 4334816: END
1076:
1077: qryCtx1 := dbms_xmlgen.newContext(query1);
1078: dbms_xmlgen.setRowTag(qryCtx1, 'G_APPROVER');
1079: dbms_xmlgen.setRowSetTag(qryCtx1, 'LIST_G_APPROVER');
1080: xmlresult1 := dbms_xmlgen.getXML(qryCtx1, dbms_xmlgen.NONE);
1081: dbms_xmlgen.closecontext(qryctx1);

Line 1078: dbms_xmlgen.setRowTag(qryCtx1, 'G_APPROVER');

1074: END IF;
1075: --Bug 4334816: END
1076:
1077: qryCtx1 := dbms_xmlgen.newContext(query1);
1078: dbms_xmlgen.setRowTag(qryCtx1, 'G_APPROVER');
1079: dbms_xmlgen.setRowSetTag(qryCtx1, 'LIST_G_APPROVER');
1080: xmlresult1 := dbms_xmlgen.getXML(qryCtx1, dbms_xmlgen.NONE);
1081: dbms_xmlgen.closecontext(qryctx1);
1082: IF (dbms_lob.getlength(xmlresult1) > 0) THEN

Line 1079: dbms_xmlgen.setRowSetTag(qryCtx1, 'LIST_G_APPROVER');

1075: --Bug 4334816: END
1076:
1077: qryCtx1 := dbms_xmlgen.newContext(query1);
1078: dbms_xmlgen.setRowTag(qryCtx1, 'G_APPROVER');
1079: dbms_xmlgen.setRowSetTag(qryCtx1, 'LIST_G_APPROVER');
1080: xmlresult1 := dbms_xmlgen.getXML(qryCtx1, dbms_xmlgen.NONE);
1081: dbms_xmlgen.closecontext(qryctx1);
1082: IF (dbms_lob.getlength(xmlresult1) > 0) THEN
1083: l_resultOffset := DBMS_LOB.INSTR(xmlresult1,'>');

Line 1080: xmlresult1 := dbms_xmlgen.getXML(qryCtx1, dbms_xmlgen.NONE);

1076:
1077: qryCtx1 := dbms_xmlgen.newContext(query1);
1078: dbms_xmlgen.setRowTag(qryCtx1, 'G_APPROVER');
1079: dbms_xmlgen.setRowSetTag(qryCtx1, 'LIST_G_APPROVER');
1080: xmlresult1 := dbms_xmlgen.getXML(qryCtx1, dbms_xmlgen.NONE);
1081: dbms_xmlgen.closecontext(qryctx1);
1082: IF (dbms_lob.getlength(xmlresult1) > 0) THEN
1083: l_resultOffset := DBMS_LOB.INSTR(xmlresult1,'>');
1084: dbms_lob.copy(l_xml, xmlresult1, dbms_lob.getlength(xmlresult1) - l_resultOffset, dbms_lob.getlength(l_xml), l_resultOffset +1);

Line 1081: dbms_xmlgen.closecontext(qryctx1);

1077: qryCtx1 := dbms_xmlgen.newContext(query1);
1078: dbms_xmlgen.setRowTag(qryCtx1, 'G_APPROVER');
1079: dbms_xmlgen.setRowSetTag(qryCtx1, 'LIST_G_APPROVER');
1080: xmlresult1 := dbms_xmlgen.getXML(qryCtx1, dbms_xmlgen.NONE);
1081: dbms_xmlgen.closecontext(qryctx1);
1082: IF (dbms_lob.getlength(xmlresult1) > 0) THEN
1083: l_resultOffset := DBMS_LOB.INSTR(xmlresult1,'>');
1084: dbms_lob.copy(l_xml, xmlresult1, dbms_lob.getlength(xmlresult1) - l_resultOffset, dbms_lob.getlength(l_xml), l_resultOffset +1);
1085: END IF;

Line 1111: qryCtx1 := dbms_xmlgen.newContext(query1);

1107: || 'psp_eff_report_approvals pera WHERE perd.effort_report_detail_id = pera.effort_report_detail_id (+) AND er.effort_report_id = perd.effort_Report_id and '
1108: || ' perd.effort_report_id = ' || TO_CHAR(p_effort_report_id) || ' AND NVL(pera.approval_status, ''A'') IN (''P'', ''A'')'
1109: || l_er_check || l_sort_option1;
1110:
1111: qryCtx1 := dbms_xmlgen.newContext(query1);
1112: dbms_xmlgen.setRowsetTag(qryCtx1, 'LIST_G_PERSON');
1113: dbms_xmlgen.setRowTag(qryCtx1, 'G_PERSON');
1114: --dbms_xmlgen.setRowsetTag(qryCtx1, 'LIST_G_PERSON');
1115: l_xml := dbms_xmlgen.getXML(qryCtx1, dbms_xmlgen.NONE);

Line 1112: dbms_xmlgen.setRowsetTag(qryCtx1, 'LIST_G_PERSON');

1108: || ' perd.effort_report_id = ' || TO_CHAR(p_effort_report_id) || ' AND NVL(pera.approval_status, ''A'') IN (''P'', ''A'')'
1109: || l_er_check || l_sort_option1;
1110:
1111: qryCtx1 := dbms_xmlgen.newContext(query1);
1112: dbms_xmlgen.setRowsetTag(qryCtx1, 'LIST_G_PERSON');
1113: dbms_xmlgen.setRowTag(qryCtx1, 'G_PERSON');
1114: --dbms_xmlgen.setRowsetTag(qryCtx1, 'LIST_G_PERSON');
1115: l_xml := dbms_xmlgen.getXML(qryCtx1, dbms_xmlgen.NONE);
1116: l_resultOffset := DBMS_LOB.INSTR(l_xml,'>');

Line 1113: dbms_xmlgen.setRowTag(qryCtx1, 'G_PERSON');

1109: || l_er_check || l_sort_option1;
1110:
1111: qryCtx1 := dbms_xmlgen.newContext(query1);
1112: dbms_xmlgen.setRowsetTag(qryCtx1, 'LIST_G_PERSON');
1113: dbms_xmlgen.setRowTag(qryCtx1, 'G_PERSON');
1114: --dbms_xmlgen.setRowsetTag(qryCtx1, 'LIST_G_PERSON');
1115: l_xml := dbms_xmlgen.getXML(qryCtx1, dbms_xmlgen.NONE);
1116: l_resultOffset := DBMS_LOB.INSTR(l_xml,'>');
1117: dbms_lob.write(l_xml, LENGTH(RPAD(' ', l_resultOffset, ' ')), 1, RPAD(' ', l_resultOffset, ' '));

Line 1114: --dbms_xmlgen.setRowsetTag(qryCtx1, 'LIST_G_PERSON');

1110:
1111: qryCtx1 := dbms_xmlgen.newContext(query1);
1112: dbms_xmlgen.setRowsetTag(qryCtx1, 'LIST_G_PERSON');
1113: dbms_xmlgen.setRowTag(qryCtx1, 'G_PERSON');
1114: --dbms_xmlgen.setRowsetTag(qryCtx1, 'LIST_G_PERSON');
1115: l_xml := dbms_xmlgen.getXML(qryCtx1, dbms_xmlgen.NONE);
1116: l_resultOffset := DBMS_LOB.INSTR(l_xml,'>');
1117: dbms_lob.write(l_xml, LENGTH(RPAD(' ', l_resultOffset, ' ')), 1, RPAD(' ', l_resultOffset, ' '));
1118: -- dbms_lob.copy(l_xml, xmlresult1, dbms_lob.getlength(xmlresult1) - l_resultOffset, 1, l_resultOffset +1);

Line 1115: l_xml := dbms_xmlgen.getXML(qryCtx1, dbms_xmlgen.NONE);

1111: qryCtx1 := dbms_xmlgen.newContext(query1);
1112: dbms_xmlgen.setRowsetTag(qryCtx1, 'LIST_G_PERSON');
1113: dbms_xmlgen.setRowTag(qryCtx1, 'G_PERSON');
1114: --dbms_xmlgen.setRowsetTag(qryCtx1, 'LIST_G_PERSON');
1115: l_xml := dbms_xmlgen.getXML(qryCtx1, dbms_xmlgen.NONE);
1116: l_resultOffset := DBMS_LOB.INSTR(l_xml,'>');
1117: dbms_lob.write(l_xml, LENGTH(RPAD(' ', l_resultOffset, ' ')), 1, RPAD(' ', l_resultOffset, ' '));
1118: -- dbms_lob.copy(l_xml, xmlresult1, dbms_lob.getlength(xmlresult1) - l_resultOffset, 1, l_resultOffset +1);
1119: dbms_xmlgen.closecontext(qryctx1);

Line 1119: dbms_xmlgen.closecontext(qryctx1);

1115: l_xml := dbms_xmlgen.getXML(qryCtx1, dbms_xmlgen.NONE);
1116: l_resultOffset := DBMS_LOB.INSTR(l_xml,'>');
1117: dbms_lob.write(l_xml, LENGTH(RPAD(' ', l_resultOffset, ' ')), 1, RPAD(' ', l_resultOffset, ' '));
1118: -- dbms_lob.copy(l_xml, xmlresult1, dbms_lob.getlength(xmlresult1) - l_resultOffset, 1, l_resultOffset +1);
1119: dbms_xmlgen.closecontext(qryctx1);
1120: END IF;
1121:
1122: RETURN l_xml;
1123: EXCEPTION

Line 2143: --- function for uva issues.. this can be replace dbms_xmlgen.convert in future.

2139: p_return_status => l_return_status);
2140: END IF;
2141: p_return_status := fnd_api.g_ret_sts_error;
2142: END COPY_PTAOE_FROM_GL_SEGMENTS;
2143: --- function for uva issues.. this can be replace dbms_xmlgen.convert in future.
2144: ---- fix for bug 4429787
2145: function convert_xml_controls(p_string varchar2) return varchar2 is
2146: begin
2147: return replace(replace(replace(replace(replace(p_string, '&', '&'),'''','''),'"','"'),'<','<'),'>','>');