Example Of Type 2 of XML publisher Report:Gratuity Report Of Core HR

<?xml version="1.0" encoding="utf-8"?>
 <dataTemplate name="DATA" version="1.0">
   <parameters> 
  <parameter name="P_FROM_DATE" dataType="date" />
  <parameter name="P_TO_DATE" dataType="date" />
  <parameter name="p_org_id" dataType="number" />
  <parameter name="p_person_id" dataType="number" />
  
  </parameters>
 <dataQuery>
 <sqlStatement name="Q1">
 <![CDATA[ 
SELECT PPF.person_id emp_person_id,
  PPF.FIRST_NAME || ' '|| PPF.MIDDLE_NAMES ||' ' || PPF.LAST_NAME EMPLOYEE_NAME,
  DECODE(PPF.SEX,   'M',   'Male',   'F',   'Female',   NULL) GENDER,
  LOOK.MEANING MARITAL_STATUS,
  HR_GENERAL.DECODE_ORGANIZATION(PAF.ORGANIZATION_ID) DEPARTMENT,
  HOI.ORG_INFORMATION4 LEGAL_DEPARTMENT,
  PPF.EMPLOYEE_NUMBER EMP_NUMBER,
  TO_CHAR(PPF.ORIGINAL_DATE_OF_HIRE,'DD-MON-YYYY') DATE_OF_APPOINTMENT,
  XXTEST_GET_ADDRESS_FNC(PPF.PERSON_ID) EMP_ADDRESS,
  INFO.PEI_INFORMATION1 RELIGION
FROM PER_PEOPLE_F PPF,
 PER_PEOPLE_EXTRA_INFO INFO,
  HR_LOOKUPS LOOK,
  PER_ASSIGNMENTS_F PAF,
  HR_ALL_ORGANIZATION_UNITS HAOU,
  HR_ORGANIZATION_INFORMATION HOI
WHERE 1 = 1
 AND INFO.PEI_INFORMATION_CATEGORY(+) = 'IN_MISCELLANEOUS'
 AND INFO.INFORMATION_TYPE(+) = 'IN_MISCELLANEOUS'
 AND INFO.PERSON_ID(+) = PPF.PERSON_ID
 AND LOOK.LOOKUP_TYPE(+) = 'MAR_STATUS'
 AND LOOK.LOOKUP_CODE(+) = PPF.MARITAL_STATUS
 AND LOOK.ENABLED_FLAG(+) = 'Y'
 AND PPF.PERSON_ID = PAF.PERSON_ID
 AND PAF.PRIMARY_FLAG = 'Y'
 AND PPF.CURRENT_EMPLOYEE_FLAG = 'Y'
 AND PPF.BUSINESS_GROUP_ID = PAF.BUSINESS_GROUP_ID
 AND PPF.BUSINESS_GROUP_ID = FND_PROFILE.VALUE('PER_BUSINESS_GROUP_ID')
 AND HAOU.ORGANIZATION_ID = PAF.ORGANIZATION_ID
 AND HOI.ORGANIZATION_ID(+) = HAOU.BUSINESS_GROUP_ID
 AND HOI.ORG_INFORMATION_CONTEXT(+) = 'PER_IN_COMPANY_DF'
 AND TRUNC(SYSDATE) BETWEEN PPF.EFFECTIVE_START_DATE
 AND PPF.EFFECTIVE_END_DATE
 AND TRUNC(SYSDATE) BETWEEN PAF.EFFECTIVE_START_DATE
 AND PAF.EFFECTIVE_END_DATE
 AND EXISTS (SELECT 'X' FROM PER_CONTACT_RELATIONSHIPS PCR
                            ,PER_CONTACT_EXTRA_INFO_F PCEIF
             WHERE PCR.PERSON_ID = PPF.PERSON_ID
             AND PCEIF.CONTACT_RELATIONSHIP_ID = PCR.CONTACT_RELATIONSHIP_ID
             AND PCEIF.information_type = 'IN_NOMINATION_DETAILS'
             AND PCEIF.cei_information3 = 'DRG'
             )
 AND PAF.ORGANIZATION_ID = NVL(:P_ORG_ID,   PAF.ORGANIZATION_ID)
 AND PPF.PERSON_ID = NVL(:p_person_id,   PPF.PERSON_ID)
 AND PPF.ORIGINAL_DATE_OF_HIRE BETWEEN :P_FROM_DATE AND :P_TO_DATE
 ORDER BY PPF.EMPLOYEE_NUMBER

  ]]>
  </sqlStatement>
<sqlStatement name="Q2">
 <![CDATA[ 
SELECT PCR.PERSON_ID,
  PCR.CONTACT_PERSON_ID,
  PPF.FIRST_NAME || ' '|| PPF.MIDDLE_NAMES ||' ' || PPF.LAST_NAME CONTACT_NAME,
  ROUND((TRUNC(SYSDATE) -TRUNC(PPF.DATE_OF_BIRTH)) / 365) CONTACT_AGE,
  LOOK.MEANING CONT_RELATIONSHIP,
  PCEF.CEI_INFORMATION2 GRAT_PERCENT,
  PCR.PRIMARY_CONTACT_FLAG,
  DECODE(PCR.RLTD_PER_RSDS_W_DSGNTR_FLAG,   'N',   XXTEST_GET_ADDRESS_FNC(PCR.CONTACT_PERSON_ID),   'Y',   XXTEST_GET_ADDRESS_FNC(PCR.PERSON_ID))
  CONTACT_ADDRESS,
  PCEF.CEI_INFORMATION3
FROM PER_CONTACT_RELATIONSHIPS PCR,
  PER_CONTACT_EXTRA_INFO_F PCEF,
  HR_LOOKUPS LOOK,
  PER_PEOPLE_F PPF
WHERE 1 = 1
 AND PCEF.CONTACT_RELATIONSHIP_ID = PCR.CONTACT_RELATIONSHIP_ID
 AND LOOK.LOOKUP_TYPE(+) = 'CONTACT'
 AND LOOK.LOOKUP_CODE(+) = PCR.CONTACT_TYPE
 AND LOOK.ENABLED_FLAG(+) = 'Y'
 AND PPF.PERSON_ID = PCR.CONTACT_PERSON_ID
 AND PCEF.information_type = 'IN_NOMINATION_DETAILS'
 AND PCEF.cei_information3 = 'DRG'
 AND TRUNC(SYSDATE) BETWEEN PPF.EFFECTIVE_START_DATE
 AND PPF.EFFECTIVE_END_DATE
 AND PCR.PERSON_ID = :emp_person_id
ORDER BY PCEF.CEI_INFORMATION2 DESC

  ]]>
  </sqlStatement>

  </dataQuery>
  <link name="CONT_LINK" parentQuery="Q1" parentColumn="emp_person_id"
   childQuery="Q2" childColumn="person_id" condition = "="/>
 <dataStructure>
 <group name="G1" source="Q1">
  <element name="EMPLOYEE_NAME" value="EMPLOYEE_NAME" />
  <element name="GENDER" value="GENDER" />
  <element name="MARITAL_STATUS" value="MARITAL_STATUS" />  
  <element name="DEPARTMENT" value="DEPARTMENT" />  
  <element name="LEGAL_DEPARTMENT" value="LEGAL_DEPARTMENT" />
  <element name="EMP_NUMBER" value="EMP_NUMBER" />
  <element name="DATE_OF_APPOINTMENT" value="DATE_OF_APPOINTMENT" />
  <element name="EMP_ADDRESS" value="EMP_ADDRESS" />
  <element name="RELIGION" value="RELIGION" />

 <group name="G2" source="Q2">
   <element name="CONTACT_NAME" value="CONTACT_NAME" />
          <element name="CONTACT_AGE" value="CONTACT_AGE" />
   <element name="CONT_RELATIONSHIP" value="CONT_RELATIONSHIP" />
   <element name="GRAT_PERCENT" value="GRAT_PERCENT" />
   <element name="CONTACT_ADDRESS" value="CONTACT_ADDRESS" />
        </group>
 </group>
 </dataStructure>
 </dataTemplate>