XML Publisher Report with PLSQL Code

XML Publisher Report with PLSQL Code

1. Create the PLSQL EXECUTABLE of PLSQL Pakage type
2. write the plsql proc, which generate the output in xml.
3. Set the Concurrent Program output as XML.
4. Configure the conc program.
5. Get the program output by running it, save it as xml file
6. Create the rtf template using the xml file.
7. Create the XML Data definition using the XML Publisher responsibility.
8. Create the XML Template using XML Publisher responsibility.
9. Attach the rtf template in XML Template Definition.
10 Run the program, you get the output in excel.

CREATE OR REPLACE PROCEDURE apps.xx_PLSQL_XML_report
(
   errbuf           OUT      VARCHAR2,
   retcode          OUT      VARCHAR2,
   p_geography     IN       NUMBER,
   p_report_type   IN       VARCHAR2
)
AS
   CURSOR c1
   IS
      SELECT REPLACE
             (SELECT papf.full_name FROM apps.per_all_people_f papf WHERE papf.person_id = a.employee_id AND ROWNUM = 1) full_name,
             a.invoice_num, a.description, a.total,
             TRUNC (a.creation_date) creation_date, a.report_submitted_date,
             a.expense_status_code,
             (SELECT s.end_date
                FROM apps.wf_item_activity_statuses s,
                     apps.wf_process_activities p
               WHERE s.item_type = 'APEXP'
                 AND p.activity_name = 'MANAGER_APPROVES'
                 AND p.process_name = 'AP_MANAGER_APPROVAL_PROCESS'
                 AND p.activity_item_type = 'APEXP'
                 AND s.item_key = TO_CHAR (a.report_header_id)
                 AND p.instance_id = s.process_activity)
                                                       manager_approval_date
        FROM apps.ap_expense_report_headers_all a
       WHERE NOT EXISTS (
                SELECT '1'
                  FROM apps.ap_expense_report_lines_all b
                 WHERE a.report_header_id = b.report_header_id
                   AND b.credit_card_trx_id IS NOT NULL)
         AND a.org_id = p_geography
         AND a.vouchno = 0
         AND a.expense_status_code = 'MGRAPPR'
         AND a.report_submitted_date >= '01-JAN-2008';

 
BEGIN
   fnd_file.put_line (fnd_file.LOG, '-------------------------');
   fnd_file.put_line (fnd_file.LOG, 'Program Execution Starts');
   fnd_file.put_line (fnd_file.LOG, '-------------------------');
   fnd_file.put_line (fnd_file.LOG, 'p_geography: ' || p_geography);
   fnd_file.put_line (fnd_file.LOG, 'p_report_type : ' || p_report_type);


FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'<?xml version="1.0" encoding="UTF-8"?>');
FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'<ROOT>');

    FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'<HEADER>');
            FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'<REP_NAME>'          || 'XX Amex Ready to Pay Expense Reports' || '</REP_NAME>'    );
            FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'<ORG_ID>'          || p_geography            || '</ORG_ID>'    );
            FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'<TRANSACTION_TYPE>'      || p_report_type          || '</TRANSACTION_TYPE>'   );
    FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'</HEADER>');


FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'<AMEX_TRX_INFO>');

   IF (p_report_type = 'ONLY_CASH')
   THEN
      fnd_file.put_line (fnd_file.LOG, '-------------------------');
      fnd_file.put_line (fnd_file.LOG, 'Only Cash Trx Report');
      fnd_file.put_line (fnd_file.LOG, '-------------------------');
      FOR c1_rec IN c1
      LOOP
    FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'<AMEX_CASH_ONLY>');
            FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'<FULL_NAME>'          || c1_rec.FULL_NAME             || '</FULL_NAME>'      );
            FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'<INVOICE_NUM>'         || c1_rec.INVOICE_NUM           || '</INVOICE_NUM>'      );
            FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'<DESCRIPTION>'         || c1_rec.DESCRIPTION           || '</DESCRIPTION>'      );
            FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'<TOTAL>'           || c1_rec.TOTAL                 || '</TOTAL>'       );
            FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'<CREATION_DATE>'         || c1_rec.CREATION_DATE         || '</CREATION_DATE>'     );
            FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'<REPORT_SUBMITTED_DATE>' || c1_rec.REPORT_SUBMITTED_DATE || '</REPORT_SUBMITTED_DATE>'   );
            FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'<EXPENSE_STATUS_CODE>'   || c1_rec.EXPENSE_STATUS_CODE   || '</EXPENSE_STATUS_CODE>'    );
            FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'<MANAGER_APPROVAL_DATE>' || c1_rec.MANAGER_APPROVAL_DATE || '</MANAGER_APPROVAL_DATE>'   );
    FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'</AMEX_CASH_ONLY>');
      END LOOP;
   END IF;

    FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'</AMEX_TRX_INFO>'); 
    FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'</ROOT>');
    fnd_file.put_line (fnd_file.LOG, '-------------------------');
    fnd_file.put_line (fnd_file.LOG, 'Program Execution Completed');
    fnd_file.put_line (fnd_file.LOG, '-------------------------');
  
  
END xx_PLSQL_XML_report ;