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 ;
