Saturday, June 4, 2016

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;

No comments:

Post a Comment

Best Blogger TipsGet Flower Effect