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;
566: l_resultOffset INT;
727: || ' psp_eff_report_details perd2 '
728: || ' WHERE pera2.effort_report_detail_id = perd2.effort_report_detail_id '
729: || ' AND perd2.effort_report_id = perd.effort_report_id) ';
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);
728: || ' WHERE pera2.effort_report_detail_id = perd2.effort_report_detail_id '
729: || ' AND perd2.effort_report_id = perd.effort_report_id) ';
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;
729: || ' AND perd2.effort_report_id = perd.effort_report_id) ';
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);
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,'>');
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,'>');
739: dbms_lob.copy(l_xml, xmlresult1, dbms_lob.getlength(xmlresult1) - l_resultOffset, length(l_employee_info), l_resultOffset +1);