Procedures Parameter Modes

Parameter Modes

Query to find Parameters and Value Sets associated with a Concurrent Program

SELECT
        fcpl.user_concurrent_program_name "Concurrent Program Name",
        fcp.concurrent_program_name "Short Name",
        fdfcuv.column_seq_num "Column Seq Number",
        fdfcuv.end_user_column_name "Parameter Name",
        fdfcuv.form_left_prompt "Prompt",
        fdfcuv.enabled_flag " Enabled Flag",
        fdfcuv.required_flag "Required Flag",
        fdfcuv.display_flag "Display Flag",
        fdfcuv.flex_value_set_id "Value Set Id",
        ffvs.flex_value_set_name "Value Set Name",
        flv.meaning "Default Type",
        fdfcuv.DEFAULT_VALUE "Default Value"
FROM
        fnd_concurrent_programs fcp,
        fnd_concurrent_programs_tl fcpl,
        fnd_descr_flex_col_usage_vl fdfcuv,
        fnd_flex_value_sets ffvs,
        fnd_lookup_values flv
WHERE
        fcp.concurrent_program_id = fcpl.concurrent_program_id
        AND    fcpl.user_concurrent_program_name = :conc_prg_name
        AND    fdfcuv.descriptive_flexfield_name = '$SRS$.'
                 || fcp.concurrent_program_name
        AND    ffvs.flex_value_set_id = fdfcuv.flex_value_set_id
        AND    flv.lookup_type(+) = 'FLEX_DEFAULT_TYPE'
        AND    flv.lookup_code(+) = fdfcuv.default_type
        AND    fcpl.LANGUAGE = USERENV ('LANG')
        AND    FLV.LANGUAGE(+) = USERENV ('LANG')
ORDER BY fdfcuv.column_seq_num;

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 ;