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 112: qryCtx1 dbms_xmlgen.ctxType;

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

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

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

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

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

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

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

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

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

Line 359: dbms_xmlgen.closecontext(qryctx1);

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

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

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

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

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

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

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

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

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

Line 514: dbms_xmlgen.closecontext(qryctx1);

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

Line 561: qryCtx1 dbms_xmlgen.ctxType;

557: p_emp_primary_org_name IN VARCHAR2,
558: p_emp_primary_org_id IN NUMBER,
559: p_currency_code IN VARCHAR2
560: ) RETURN CLOB IS
561: qryCtx1 dbms_xmlgen.ctxType;
562: qryCtx2 dbms_xmlquery.ctxType;
563: query1 VARCHAR2(8000);
564: xmlresult1 CLOB;
565: l_xml CLOB;

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

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

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

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

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

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

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

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

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

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

Line 757: dbms_xmlgen.closecontext(qryctx1);

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

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

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

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

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

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

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

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

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

Line 791: dbms_xmlgen.closecontext(qryctx1);

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

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

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

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

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

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

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

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

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

Line 833: dbms_xmlgen.closecontext(qryctx1);

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

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

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

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

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

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

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

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

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

Line 861: dbms_xmlgen.closecontext(qryctx1);

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

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

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

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

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

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

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

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

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

Line 894: dbms_xmlgen.closecontext(qryctx1);

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

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

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

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

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

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

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

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

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

Line 916: dbms_xmlgen.closecontext(qryctx1);

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

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

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

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

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

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

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

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

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

Line 944: dbms_xmlgen.closecontext(qryctx1);

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

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

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

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

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

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

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

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

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

Line 967: dbms_xmlgen.closecontext(qryctx1);

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

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

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

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

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

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

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

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

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

Line 992: dbms_xmlgen.closecontext(qryctx1);

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

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

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

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

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

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

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

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

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

Line 1024: dbms_xmlgen.closecontext(qryctx1);

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

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

1069:
1070: END IF;
1071: --Bug 4334816: END
1072:
1073: qryCtx1 := dbms_xmlgen.newContext(query1);
1074: dbms_xmlgen.setRowTag(qryCtx1, 'G_APPROVER');
1075: dbms_xmlgen.setRowSetTag(qryCtx1, 'LIST_G_APPROVER');
1076: xmlresult1 := dbms_xmlgen.getXML(qryCtx1, dbms_xmlgen.NONE);
1077: dbms_xmlgen.closecontext(qryctx1);

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

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

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

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

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

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

Line 1077: dbms_xmlgen.closecontext(qryctx1);

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

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

1103: || '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 '
1104: || ' perd.effort_report_id = ' || TO_CHAR(p_effort_report_id) || ' AND NVL(pera.approval_status, ''A'') IN (''P'', ''A'')'
1105: || l_er_check || l_sort_option1;
1106:
1107: qryCtx1 := dbms_xmlgen.newContext(query1);
1108: dbms_xmlgen.setRowsetTag(qryCtx1, 'LIST_G_PERSON');
1109: dbms_xmlgen.setRowTag(qryCtx1, 'G_PERSON');
1110: --dbms_xmlgen.setRowsetTag(qryCtx1, 'LIST_G_PERSON');
1111: l_xml := dbms_xmlgen.getXML(qryCtx1, dbms_xmlgen.NONE);

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

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

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

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

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

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

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

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

Line 1115: dbms_xmlgen.closecontext(qryctx1);

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

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

1980: p_return_status => l_return_status);
1981: END IF;
1982: p_return_status := fnd_api.g_ret_sts_error;
1983: END COPY_PTAOE_FROM_GL_SEGMENTS;
1984: --- function for uva issues.. this can be replace dbms_xmlgen.convert in future.
1985: ---- fix for bug 4429787
1986: function convert_xml_controls(p_string varchar2) return varchar2 is
1987: begin
1988: return replace(replace(replace(replace(replace(p_string, '&', '&'),'''','''),'"','"'),'<','<'),'>','>');