1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

create xml report

Discussion in 'SQL PL/SQL' started by dayakar, Dec 9, 2013.

  1. dayakar

    dayakar Active Member

    Messages:
    1
    Likes Received:
    1
    Trophy Points:
    85
    Hi ,

    please let me how to create XML report with out using RDF file.

    Thanks & Regards,

    Dayakar
     
  2. jagadekara

    jagadekara Forum Guru

    Messages:
    1,126
    Likes Received:
    145
    Trophy Points:
    1,805
    Location:
    Pulivendula
    Hi, I done sample report with out rdf like below.

    First I created one stored procedure.

    CREATE OR REPLACE
    PROCEDURE Demo_XML_Publisher
    (
    errbuf VARCHAR2,
    retcode NUMBER,
    v_invoice_id VARCHAR2)
    AS
    /*Cursor to fetch Customer Records*/
    CURSOR c1
    IS
    SELECT asp.vendor_name ,
    asp.segment1 vendor_number,
    ass.vendor_site_code,
    aia.invoice_num,
    aia.invoice_date,
    aia.invoice_amount
    FROM ap_suppliers asp,
    ap_supplier_sites_all ass,
    ap_invoices_all aia
    WHERE asp.vendor_id=ass.vendor_id
    and asp.vendor_id=aia.vendor_id
    and ass.vendor_site_id=aia.vendor_site_id
    and aia.invoice_id=nvl(v_invoice_id,aia.invoice_id)
    and rownum<20
    --AND ROWNUM < 10
    ;
    /*Cursor to fetch customer invoice records
    CURSOR xml_detail(v_customer_id 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 =nvl(v_customer_id,ra.ship_to_customer_id)
    AND aps.customer_trx_id = ra.customer_trx_id
    AND ROWNUM < 20;*/
    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,'<VENDORINFO>');
    FOR i IN c1
    LOOP
    /*For each record create a group tag <P_CUSTOMER> at the start*/
    FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'<P_VENDOR>');
    /*Embed data between XML tags for ex:- <CUSTOMER_NAME>ABCD</CUSTOMER_NAME>*/
    FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'<VENDOR_NAME>' || i.vendor_name || '</VENDOR_NAME>');
    FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'<VENDOR_NUMBER>' || i.vendor_number || '</VENDOR_NUMBER>');
    FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'<VENDOR_SITE_CODE>' || i.vendor_site_code || '</VENDOR_SITE_CODE>');
    FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'<INVOICE_NUMBER>' || i.invoice_num || '</INVOICE_NUMBER>');
    FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'<INVOICE_DATE>' || i.invoice_date || '</INVOICE_DATE>');
    FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'<INVOICE_AMOUNT>' || i.invoice_amount || '</INVOICE_AMOUNT>');

    /*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_VENDOR>');
    END LOOP;
    /*Finally Close the starting Report tag*/
    FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'</VENDORINFO>');
    EXCEPTION
    WHEN OTHERS THEN
    FND_FILE.PUT_LINE(FND_FILE.log,'Entered INTO exception');
    END Demo_XML_Publisher;


    Then create a concurrent program method as pl/sql stored procedure
    file as Demo_XML_Publisher and create one parameter as v_invoice_id.

    Then created one SampleXmlReport.rtf(see the attachment)
    In that i used Re-grouping.

    Now run the report and check the output.
     
  3. jagadekara

    jagadekara Forum Guru

    Messages:
    1,126
    Likes Received:
    145
    Trophy Points:
    1,805
    Location:
    Pulivendula
    While uploading rtf it throws an error. So i am uploading it as .doc file. Please save it as .rtf

    Hope this will help you..
     

    Attached Files: