Develop an XML report from PL/SQL procedure.

Step 1: Create a database package comprising of the following two PL/SQL procedures “b_report” and “xml_transfer” and compile it successfully.

/********************************************************************************/

CREATE OR REPLACE PACKAGE apps.b_test_report
AS
PROCEDURE b_report (
errbuf OUT NOCOPY VARCHAR2,
retcode OUT NOCOPY VARCHAR2,
p_organization_id IN NUMBER,
p_from_order IN VARCHAR2,
p_to_order IN VARCHAR2
);

PROCEDURE xml_transfer (p_xml_clob IN CLOB);
END b_test_report;
/

CREATE OR REPLACE PACKAGE apps.b_test_report
AS
PROCEDURE b_report (
errbuf OUT NOCOPY VARCHAR2,
retcode OUT NOCOPY VARCHAR2,
p_organization_id IN NUMBER,
p_from_order IN VARCHAR2,
p_to_order IN VARCHAR2
);

PROCEDURE xml_transfer (p_xml_clob IN CLOB);
END b_test_report;
/

CREATE OR REPLACE PACKAGE BODY apps.b_test_report
AS
g_xml_encoding
/*ADVICE(32): This item should be defined in a deeper scope [558] */
VARCHAR2 (100) := ‘-9999’;

PROCEDURE b_report (
Errbuf OUT NOCOPY VARCHAR2,
Retcode OUT NOCOPY VARCHAR2,
p_organization_id IN NUMBER,
p_from_order IN VARCHAR2,
p_to_order IN VARCHAR2
)
IS
qryctx DBMS_XMLGEN.ctxhandle;
v_result CLOB;
QUERY VARCHAR2 (32000);
BEGIN




QUERY :=
‘Select org.ORGANIZATION_CODE
, Sysdate t_date
,Cursor (Select E.ORDER_NUMBER ORDER_NUMBER
,Cursor (Select m.SEGMENT1 Item_code
, e.ORDER_NUMBER Order_No
, m.DESCRIPTION Description
, SUM(d.ORDERED_QUANTITY) Quantity
, d.UNIT_SELLING_PRICE Price
, Sum(Nvl(d.ORDERED_QUANTITY,0) * nvl(d.UNIT_SELLING_PRICE, 0)) Total_Amount
From Oe_Order_Lines_All d
, Mtl_System_Items_b m
Where m.INVENTORY_ITEM_ID = d.INVENTORY_ITEM_ID
And m.ORGANIZATION_ID = d.SHIP_FROM_ORG_ID
And e.HEADER_ID = d.HEADER_ID
Group by m.SEGMENT1
, m.DESCRIPTION
, d.UNIT_SELLING_PRICE
)Line_Details
FROM Oe_Order_Headers_All e
Where e.SHIP_FROM_ORG_ID = Org.ORGANIZATION_ID’;

IF (p_from_order IS NOT NULL AND p_to_order IS NOT NULL)
THEN
QUERY :=
QUERY
|| ‘ AND (e.ORDER_NUMBER >= ‘’’
|| p_from_order
|| ‘’’ AND e.ORDER_NUMBER <= ‘’’
|| p_to_order
|| ‘’’)’;
ELSIF (p_from_order IS NOT NULL)
THEN
QUERY :=
QUERY || ‘ AND e.ORDER_NUMBER >= ‘’’ || p_from_order || ‘’’’;
ELSIF (p_to_order IS NOT NULL)
THEN
QUERY := QUERY || ‘ AND e.ORDER_NUMBER <= ‘’’ || p_to_order || ‘’’’;
END IF;

QUERY :=
QUERY
|| ‘ AND EXISTS ( Select ‘’x’’
From Oe_Order_Headers_all H
Where h.SHIP_FROM_ORG_ID = Org.ORGANIZATION_ID )
) Order_Details
From Org_Organization_Definitions org
Where Org.ORGANIZATION_ID =’
|| p_organization_id;
qryctx := DBMS_XMLGEN.newcontext (QUERY);

LOOP
v_result := DBMS_XMLGEN.getxml (qryctx);

IF v_result IS NULL
THEN
EXIT;
END IF;

xml_transfer (p_xml_clob => v_result);
END LOOP;

--close context
DBMS_XMLGEN.closecontext (qryctx);
EXCEPTION
WHEN OTHERS
THEN
fnd_file.put_line (fnd_file.LOG, ‘Exception in procedure B_TEST_REPORT ‘
|| SQLCODE
|| ‘ ‘
|| SQLERRM
) ;
END b_report;

PROCEDURE xml_transfer (p_xml_clob IN CLOB)
IS
l_file CLOB;
file_varchar2 VARCHAR2 (32767);
l_len NUMBER;
m_len NUMBER;

l_limit NUMBER;

m_file CLOB;
l_xml_header VARCHAR2 (300);
BEGIN
IF g_xml_encoding = ‘-9999’
THEN
g_xml_encoding := fnd_profile.VALUE (‘ICX_CLIENT_IANA_ENCODING’);
END IF;

l_xml_header :=
‘’;
l_file := p_xml_clob;
l_limit := 1;
l_len := DBMS_LOB.getlength (l_file);

LOOP
m_file := DBMS_LOB.SUBSTR (l_file, 4000, l_limit);
file_varchar2 := TRIM (m_file);
file_varchar2 :=
REPLACE (file_varchar2, ‘’, l_xml_header);
fnd_file.put (fnd_file.output , file_varchar2);
file_varchar2 := NULL;
m_file := NULL;

IF l_len > l_limit
THEN
l_limit := l_limit + 4000;
ELSE
EXIT;
END IF;
END LOOP;
EXCEPTION
WHEN OTHERS
THEN
fnd_file.put_line (fnd_file.LOG

,
‘Exception in procedure XML_TRANSFER ‘
|| SQLCODE
|| ‘ ‘
|| SQLERRM
);

END xml_transfer;
END b_test_report;
/
WHEN OTHERS THEN
fnd_file.put_line (fnd_file.LOG, ‘Exception in procedure TEST_REPORT ‘ || SQLCODE || ‘ ‘ || SQLERRM);
End test_reports;

PROCEDURE xml_transfer (
p_xml_clob IN CLOB) IS

l_file CLOB;
--bug 8238368 kbanddyo increased length of file_varchar2 from 4000 to 32767
file_varchar2 VARCHAR2 (32767);
l_len NUMBER;
m_len NUMBER;
l_limit NUMBER;
m_file CLOB;
l_xml_header VARCHAR2(300); -- Bug 8425698

BEGIN

-- Bug 8425698: determine XML encoding
IF g_xml_encoding = ‘-9999’ THEN
g_xml_encoding := fnd_profile.value(‘ICX_CLIENT_IANA_ENCODING’);
END IF;
l_xml_header := ‘’;

l_file := p_xml_clob;
l_limit := 1;
l_len := DBMS_LOB.getlength (l_file);

LOOP
m_file := DBMS_LOB.SUBSTR(l_file, 4000, l_limit);
file_varchar2 := TRIM(m_file);
file_varchar2 := REPLACE(file_varchar2,’’,l_xml_header);
fnd_file.put(fnd_file.output, file_varchar2);
file_varchar2 := NULL;
m_file := NULL;

IF l_len > l_limit THEN
l_limit := l_limit + 4000;
ELSE
EXIT;
END IF;
END LOOP;

EXCEPTION
WHEN OTHERS THEN
fnd_file.put_line (fnd_file.LOG, ‘Exception in procedure XML_TRANSFER ‘ || SQLCODE || ‘ ‘ || SQLERRM);
END xml_transfer;

END test_xml_reports;
/
/***********************************************************************************/
Step 2: Now register the procedure “b_report” from the front end Apps screen.

Step 3: Generate the XML Code by running the created Concurrent Program “XML_TEST_REPORT”.

Step 4: After generating the XML code design the Report layout in rtf format and register the rtf in Oracle Apps. The output obtained after running the report.