Create XML Publisher report without RDF

Create XML Publisher report without RDF

Create Oracle Report in just 4 simple easy steps.

1. Create Concurrent Program by using standard Executable ‘XDODTEXE’
Pls note: XDODTEXE  is XML Publisher Data Template Executable

2. Create XML file based on below sample layout
Pls note: It will be used as Data Template File for XML Publisher Data Definition

3. Create Data Definition using XML Publisher Administrator Responsibility.

Pls note:

-          Data Definition Code should be same as Concurrent program Short name

-          Upload the XML File as Data Template

   4. Create and upload RTF file as Template under XML Publisher Administrator Responsibility.

   Pls note: Template code should be same as Data Definition code 

Finish !!
-----------------------------------------------------------------------------------------------------------------------

Sample XML file for Data Template

-----------------------------------------------------------------------------------------------------------------------

<?xml version="1.0" encoding="UTF-8" ?>

- <dataTemplate name="XXADLQ_PR" version="1.0">

- <properties>

  <property name="debug_mode" value="on" />

  </properties>

- <parameters>

  <parameter name="P_EMP_NUMBER" dataType="character" />

  <parameter name="P_FROM_DATE" dataType="date" />

  <parameter name="P_TO_DATE" dataType="date" />

  <parameter name="P_TIME_PERIOD" dataType="character" />

  <parameter name="P_DEPARTMENT" dataType="character" />

  </parameters>

- <dataQuery>

  <sqlStatement name="Q_PAYROLL">SELECT TO_CHAR(PPA.EFFECTIVE_DATE,'MON-YYYY') TIME_PERIOD, EMPLOYEE_NUMBER, FULL_NAME, PAP.NAME "POSITION_NAME", NATIONALITY, HAOU.NAME "DEPARTMENT",to_char(ORIGINAL_DATE_OF_HIRE,'DD-MM-RRRR')ORIGINAL_DATE_OF_HIRE, PETF.ELEMENT_NAME,SUM(ROUND(RESULT_VALUE,0)) RESULT_VALUE FROM PER_ALL_PEOPLE_F PAPF, PER_ALL_POSITIONS PAP, HR_ALL_ORGANIZATION_UNITS_TL HAOU, PER_ALL_ASSIGNMENTS_F PAAF, PAY_ASSIGNMENT_ACTIONS PAA, PAY_ELEMENT_TYPES_F PETF, PAY_INPUT_VALUES_F PIVF, PAY_RUN_RESULTS PRR, PAY_RUN_RESULT_VALUES PRRV, PAY_PAYROLL_ACTIONS PPA, PAY_ELEMENT_CLASSIFICATIONS PEC WHERE PAPF.PERSON_ID = PAAF.PERSON_ID AND PAAF.ASSIGNMENT_ID = PAA.ASSIGNMENT_ID AND PAAF.ORGANIZATION_ID = HAOU.ORGANIZATION_ID AND PEC.CLASSIFICATION_ID = PETF.CLASSIFICATION_ID AND PETF.ELEMENT_TYPE_ID = PIVF.ELEMENT_TYPE_ID AND PIVF.ELEMENT_TYPE_ID = PRR.ELEMENT_TYPE_ID AND PIVF.INPUT_VALUE_ID = PRRV.INPUT_VALUE_ID AND PRR.RUN_RESULT_ID = PRRV.RUN_RESULT_ID AND PAA.ASSIGNMENT_ACTION_ID = PRR.ASSIGNMENT_ACTION_ID AND PAA.PAYROLL_ACTION_ID = PPA.PAYROLL_ACTION_ID AND TRUNC(SYSDATE) BETWEEN PAAF.EFFECTIVE_START_DATE AND PAAF.EFFECTIVE_END_DATE AND TRUNC(SYSDATE) BETWEEN PAPF.EFFECTIVE_START_DATE AND PAPF.EFFECTIVE_END_DATE AND PAPF.EMPLOYEE_NUMBER = nvl(:P_EMP_NUMBER,PAPF.EMPLOYEE_NUMBER) --AND PPA.EFFECTIVE_DATE BETWEEN :P_FROM_DATE and :P_TO_DATE AND PPA.EFFECTIVE_DATE = (select max(EFFECTIVE_DATE) from PAY_PAYROLL_ACTIONS where to_char(effective_date,'MON-RRRR') like :P_TIME_PERIOD) AND RESULT_VALUE IS NOT NULL AND PIVF.NAME LIKE 'Pay Value%' AND PEC.CLASSIFICATION_NAME LIKE 'Earning%' AND PAAF.POSITION_ID = PAP.POSITION_ID AND HAOU.NAME = nvl(:P_DEPARTMENT,HAOU.NAME) AND HAOU.LANGUAGE = 'US' GROUP BY EMPLOYEE_NUMBER, PETF.ELEMENT_NAME, FULL_NAME,NATIONALITY, PAP.NAME, HAOU.NAME, ORIGINAL_DATE_OF_HIRE, PPA.EFFECTIVE_DATE ORDER BY EMPLOYEE_NUMBER</sqlStatement>

  </dataQuery>

  -----if we want one more sql query we add here......

- <dataStructure>

- <group name="G_PAYROLL" source="Q_PAYROLL">

  <element name="EMPLOYEE_NUMBER" value="EMPLOYEE_NUMBER" />

  <element name="FULL_NAME" value="FULL_NAME" />

  <element name="POSITION_NAME" value="POSITION_NAME" />

  <element name="NATIONALITY" value="NATIONALITY" />

  <element name="DEPARTMENT" value="DEPARTMENT" />

  <element name="ORIGINAL_DATE_OF_HIRE" value="ORIGINAL_DATE_OF_HIRE" />

  <element name="ELEMENT_NAME" value="ELEMENT_NAME" />

  <element name="RESULT_VALUE" value="RESULT_VALUE" />

  <element name="PAYROLL_RUN" value="PAYROLL_RUN" />

  <element name="TIME_PERIOD" value="TIME_PERIOD" />

  -----if we have one more group we can add here.....

  </group>

  </dataStructure>

  </dataTemplate>