XML Report

1. Develop the SQL.
2. Write the required package for beforeReport Trigger
3. Write the Data Template.
4. Attach the data template under data definition.
5. register the conc program as executable XDO%EXE.
6. conc program short name = data definition code
7. register the parameters in conc program.
8. run the program.
9. save the output in xml
10. define the rtf template and attach it template definition.
11. run the program again, get the output in excel/pdf any format.


Detailed Step

<dataTemplate name="COUNTER_REPORT" description="Expense Counter Report" Version="1.0" defaultPackage="xx_xml_reports">
    <parameters>
        <parameter name="p_employee_id" dataType="number"/>
        <parameter name="p_counter_type" dataType="character"/>
    </parameters>
    <dataQuery>
        <sqlStatement name="Q1">
            <![CDATA[SELECT   c_submitter, c_company_id, c_counter, counter_type,
               organization_name
          FROM (SELECT ppf.full_name c_submitter,
                       ppf.employee_number c_company_id,
                       slec.counter c_counter, 'Function' AS counter_type,
                       a.NAME organization_name
                  FROM apps.xx_function_counter slec,
                       apps.per_all_people_f ppf,
                       apps.per_all_assignments_f paf,
                       apps.per_person_type_usages_f pptu,
                       apps.per_person_types ppt,
                       apps.per_assignment_status_types past,
                       hr_organization_units_v a,                     -- added
                       hr_organization_information_v b                 --added
                 WHERE 1 = 1
                   AND ppf.person_id = paf.person_id
                   AND slec.party_id = ppf.party_id
                   AND &pwhereclause1                  
                   AND paf.effective_end_date > SYSDATE
                   AND slec.ROWID = (SELECT MAX (ROWID)
                                       FROM xx_function_counter x
                                      WHERE x.party_id = slec.party_id)
                   AND ppf.person_id = pptu.person_id
                   AND pptu.person_type_id = ppt.person_type_id
                   AND ppf.person_id = pptu.person_id
                   AND pptu.person_type_id = ppt.person_type_id
                   AND SYSDATE BETWEEN pptu.effective_start_date
                                   AND pptu.effective_end_date
                   AND SYSDATE BETWEEN ppf.effective_start_date
                                   AND ppf.effective_end_date
                   AND paf.assignment_status_type_id =
                                                past.assignment_status_type_id
                   AND past.user_status NOT LIKE '%Intern%'
                   AND ppf.party_id = slec.party_id
                   AND ppt.system_person_type IN ('EMP')
                   AND paf.effective_end_date > SYSDATE - 1
                   AND a.organization_id = b.organization_id           --added
                   AND a.organization_id = paf.ass_attribute6
                   AND a.date_to IS NULL                               --added
                   AND b.org_information1_meaning = 'Operating Unit'   --added
                                                                    )
         WHERE &pwhereclause2
     ORDER BY c_company_id ]]>
        </sqlStatement>
    </dataQuery>
    <dataTrigger name="beforereporttrigger" source="xx_xml_reports.beforereporttrigger"/>
</dataTemplate>

Register Parameters

        <parameter name="p_employee_id" dataType="number"/>
        <parameter name="p_counter_type" dataType="character"/>


CREATE OR REPLACE PACKAGE xx_xml_reports
AS
   p_employee_id   NUMBER;
   p_counter_type  varchar2(100);
   pwhereclause1   VARCHAR2 (500);
   pwhereclause2   VARCHAR2 (500);
   FUNCTION beforereporttrigger
      RETURN BOOLEAN;
END;



CREATE OR REPLACE PACKAGE BODY xx_xml_reports
AS
   FUNCTION beforereporttrigger
      RETURN BOOLEAN
   IS
   BEGIN
      IF (p_employee_id IS NOT NULL)
      THEN
         pwhereclause1 := 'ppf.person_id LIKE :p_employee_id';
      ELSIF (p_employee_id IS NULL)
      THEN
         pwhereclause1 := '1=1';
      END IF;

      IF (p_counter_type IS NULL)
      THEN
         pwhereclause2 := '1=1';
      ELSIF (p_counter_type IS NOT NULL AND p_counter_type = 'Travelocity')
      THEN
         --pwhereclause2 := 'counter_type ='||'Travelocity';
         pwhereclause2 := '1=1';
      ELSIF (p_counter_type IS NOT NULL AND p_counter_type = 'Late ER')
      THEN
         pwhereclause2 := 'counter_type =' || 'Late ER';
      END IF;

      RETURN TRUE;
   END;
END xx_xml_reports;












FNDLOAD $APPS_LOGIN_ID 0 Y UPLOAD $XDO_TOP/patch/115/import/xdotmpl.lct  $XX_AP_TOP/patch/115/import/US/EXPDETRPT_DDF.ldt


java oracle.apps.xdo.oa.util.XDOLoader UPLOAD -DB_USERNAME $APPS_LOGIN -DB_PASSWORD $APPS_PWD -JDBC_CONNECTION $TNS_DETAIL -LOB_TYPE TEMPLATE -APPS_SHORT_NAME XXAP -LOB_CODE XXEXPDETRPT -LANGUAGE en -XDO_FILE_TYPE RTF -FILE_NAME $XX_AP_TOP/reports/US/TEMPLATE_SOURCE_XXAP_XXEXPDETRPT_en.rtf