Oracle PL/SQL Report

CREATE OR REPLACE PROCEDURE XXAA_XML_PUBLISHER (

   errbuf          VARCHAR2,

   retcode         NUMBER,

   v_customer_id   VARCHAR2

)

AS

/*Cursor to fetch Customer Records*/

   CURSOR xml_parent

   IS

      SELECT customer_name,

             customer_id

        FROM ra_customers

       WHERE customer_id = TO_NUMBER (v_customer_id);

/*Cursor to fetch customer invoice records*/

   CURSOR xml_detail (p_customer_id1 NUMBER)

   IS

      SELECT ra.customer_trx_id customer_trx_id,

             ra.ship_to_customer_id ship_to_customer_id,

             ra.trx_number trx_number,

             aps.amount_due_original ams

        FROM ra_customer_trx_all ra,

             ar_payment_schedules_all aps

       WHERE ra.ship_to_customer_id = p_customer_id1

         AND aps.customer_trx_id = ra.customer_trx_id

         AND ROWNUM < 4;

BEGIN

/*First line of XML data should be <?xml version="1.0??>*/

   fnd_file.put_line (fnd_file.output, '<?xml version="1.0??>');

   fnd_file.put_line (fnd_file.output, '<CUSTOMERINFO>');

   FOR v_customer IN xml_parent

   LOOP

/*For each record create a group tag <P_CUSTOMER> at the start*/

      fnd_file.put_line (fnd_file.output, '<P_CUSTOMER>');

     

/*Embed data between XML tags for ex:- <CUSTOMER_NAME>ABCD</CUSTOMER_NAME>*/

      fnd_file.put_line (fnd_file.output,'<CUSTOMER_NAME>' || v_customer.customer_name || '</CUSTOMER_NAME>' );

      fnd_file.put_line (fnd_file.output,'<CUSTOMER_ID>' || v_customer.customer_id || '</CUSTOMER_ID>');

      FOR v_details IN xml_detail (v_customer.customer_id)

      LOOP

/*For  customer invoices create a group tag <P_INVOICES> at the start*/

         fnd_file.put_line (fnd_file.output, '<P_INVOICES>');

         fnd_file.put_line (fnd_file.output, '<CUSTOMER_TRX_ID>' || v_details.customer_trx_id || '</CUSTOMER_TRX_ID>' );

         fnd_file.put_line (fnd_file.output, '<CUSTOMER_ID>' || v_details.ship_to_customer_id || '</CUSTOMER_ID>' );

         fnd_file.put_line (fnd_file.output, '<INVOICE_NUMBER>' || v_details.trx_number || '</INVOICE_NUMBER>');

         fnd_file.put_line (fnd_file.output, '<AMOUNT_DUE_ORIGINAL>' || v_details.trx_number || '</AMOUNT_DUE_ORIGINAL>');

/*Close the group tag </P_INVOICES> at the end of customer invoices*/

         fnd_file.put_line (fnd_file.output, '</P_INVOICES>');

      END LOOP;

/*Close the group tag </P_CUSTOMER> at the end of customer record*/

      fnd_file.put_line (fnd_file.output, '</P_CUSTOMER>');

   END LOOP;

/*Finally Close the starting Report tag*/

   fnd_file.put_line (fnd_file.output, '</CUSTOMERINFO>');

EXCEPTION

   WHEN OTHERS

   THEN

      fnd_file.put_line (fnd_file.LOG, 'Entered into exception');

END XXAA_XML_PUBLISHER;