CREATE OR REPLACE PACKAGE nilesh_XML AS
PROCEDURE put_xml_tags(i_tag_type_flag IN VARCHAR2
,i_tag_name IN VARCHAR2 DEFAULT NULL
);
PROCEDURE put_xml_data ( i_xml_tags IN VARCHAR2
, i_output IN VARCHAR2
)
PROCEDURE XX_XML (i_employee_number IN VARCHAR2);
END;
CREATE OR REPLACE PACKAGE nilesh_XML AS
PROCEDURE put_xml_tags(i_tag_type_flag IN VARCHAR2
,i_tag_name IN VARCHAR2 DEFAULT NULL
)
IS
--
l_strng VARCHAR2(32376);
--
BEGIN
--
CASE
WHEN i_tag_type_flag = 'S' THEN
--
l_strng := '';
FND_FILE.PUT_LINE(FND_FILE.OUTPUT,l_strng||CHR(10));
--
l_strng := '
FND_FILE.PUT_LINE(FND_FILE.OUTPUT,l_strng||CHR(10));
--
l_strng := '
FND_FILE.PUT_LINE(FND_FILE.OUTPUT,l_strng||CHR(10));
--
l_strng := '
FND_FILE.PUT_LINE(FND_FILE.OUTPUT,l_strng||CHR(10));
--
WHEN i_tag_type_flag = 'E' THEN
--
l_strng := '
FND_FILE.PUT_LINE(FND_FILE.OUTPUT,l_strng||CHR(10));
--
WHEN i_tag_type_flag = 'G' THEN
--
l_strng := '<'||i_tag_name||'>';
FND_FILE.PUT_LINE(FND_FILE.OUTPUT,l_strng||CHR(10));
--
END CASE;
--
EXCEPTION
WHEN OTHERS THEN
FND_FILE.PUT_LINE(FND_FILE.LOG,'PERC1428-put_xml_tags Function Error '||SQLERRM);
--
END put_xml_tags;
--
PROCEDURE put_xml_data ( i_xml_tags IN VARCHAR2
, i_output IN VARCHAR2
)
IS
i_occ NUMBER(2):=1;
l_xml_tag VARCHAR2(32767);
l_output VARCHAR2(32767) ;
l_strng VARCHAR2(32767);
BEGIN
--
WHILE (INSTR(i_xml_tags,'#',1,i_occ)!=0) LOOP
BEGIN
SELECT SUBSTR(i_xml_tags,DECODE(i_occ,1,1,INSTR(i_xml_tags,'#',1,i_occ-1)+1),INSTR(i_xml_tags,'#',1,i_occ)-DECODE(i_occ,1,1,INSTR(i_xml_tags,'#',1,i_occ-1)+1))
INTO l_xml_tag
FROM dual;
EXCEPTION
WHEN OTHERS THEN
l_xml_tag :='END';
END;
--
BEGIN
SELECT SUBSTR(i_output,DECODE(i_occ,1,1,INSTR(i_output,'#',1,i_occ-1)+1),INSTR(i_output,'#',1,i_occ)-DECODE(i_occ,1,1,INSTR(i_output,'#',1,i_occ-1)+1))
INTO l_output
FROM dual;
EXCEPTION
WHEN OTHERS THEN
l_output :='';
END;
--
l_strng:= ('<'||l_xml_tag||'>'||''||'');
FND_FILE.PUT_LINE(FND_FILE.OUTPUT,l_strng||CHR(10));
--
i_occ := i_occ+1;
--
END LOOP;
--
EXCEPTION
WHEN OTHERS THEN
FND_FILE.PUT_LINE(FND_FILE.LOG,'PERC1428- put_xml_data Function Error '||SQLERRM);
END put_xml_data;
PROCEDURE XX_XML( o_errbuf OUT VARCHAR2 -- Error Message
, o_retcode OUT VARCHAR2 -- Error Code
, i_employee_number IN VARCHAR2
AS
l_xml_tags VARCHAR2(32376);
l_xml_data VARCHAR2(32376);
CURSOR c_emp_info ( i_employee_number IN NUMBER
)
IS
Select first_name, last_name, full_name, employee_number from per_all_people_f where employee_number=i_employee_number;
BEGIN
put_xml_tags('S');
put_xml_tags('G','G_EMP_INFO');
BEGIN
FOR r_emp_info IN c_emp_info(i_employee_number)
LOOP
l_xml_tags := 'LAST_NAME#FIRST_NAME#FULL_NAME#EMPLOYEE_NUMBER#';
l_xml_data := r_emp_info.last_name || '#' ||
r_emp_info.first_name || '#' ||
r_emp_info.full_name || '#' ||
r_emp_info.employee_number || '#' ;
put_xml_data(l_xml_tags,l_xml_data);
END LOOP;
put_xml_tags('G','/G_EMP_INFO');
put_xml_tags('E');
EXCEPTION
WHEN OTHERS THEN
FND_FILE.PUT_LINE(FND_FILE.LOG,'Error occured IN PERC1435' || SQLERRM);
END XX_XML;
END nilesh_XML;
/
