2 Replies Latest reply: Feb 5, 2013 8:02 AM by User406158 RSS

    How to link 2 sql query inside XML Data Template (payroll query) ?

    User406158
      Hi All,

      I am newbie in Oracle XML Reporting. I wanted to know how we can link the 2 sql query inside XML Data Template. I creating a XML report for payroll related purpose. The below query is created but the output which is in excel shows only for Sql query 1 and for SQL query 2 , the output shows blank cell. Please let me know , if i am missing anything in the below mentioned query. Your feedback is greatly appreciated !!!!!

      Thanks & Regards,
      SKC

      <?xml version="1.0" encoding="WINDOWS-1252" ?>
      <dataTemplate name="XXTZ_EMP_SALENT_REP" Version="1.0">
      <parameters>
      <parameter name="P_PAYROLL_ID" dataType="character"/>
      <parameter name="P_PERIOD" dataType="character"/>
      <parameter name="P_DEPARTMENT_ID" dataType="character"/>
      </parameters>
      <dataQuery>
      <sqlStatement name="Q1">
      <![CDATA[SELECT
      PAPF.PERSON_ID PERSON_ID,
      PAPF.EMPLOYEE_NUMBER "EMP_CODE" ,
      PAPF.FIRST_NAME
      || ' '
      || PAPF.PER_INFORMATION1
      || ' '
      || PAPF.LAST_NAME "EMP_NAME" ,
      PAAF.ASS_ATTRIBUTE1 "DIVISION" ,
      PAAF.ASS_ATTRIBUTE2 "DEPARTMENT" ,
      PAAF.ASS_ATTRIBUTE3 "SECTION" ,
      APPS.HR_GENERAL.DECODE_JOB(PAAF.JOB_ID) "JOB_TITLE" ,
      APPS.HR_GENERAL.DECODE_POSITION_CURRENT_NAME(PAAF.POSITION_ID) "POSITION",
      (SELECT DECODE(SUBSTR(NAME,1,3),'TEC',SUBSTR(NAME,5),SUBSTR(NAME,1,INSTR(NAME,'.')-1))
      FROM per_grades pg
      WHERE paaf.grade_id = pg.grade_id(+)
      ) "GRADE" ,
      HR_PERSON_TYPE_USAGE_INFO.GET_USER_PERSON_TYPE(SYSDATE,PAPF.PERSON_ID) "EMPLOYMENT_STATUS" ,
      TO_CHAR (PPOS.DATE_START, 'DD-Mon-YYYY') "DATE_OF_JOINING" ,
      DECODE(PAPF.SEX,'M','Male','F','Female') "GENDER" ,
      (SELECT HL.MEANING
      FROM HR_LOOKUPS HL
      WHERE HL.LOOKUP_TYPE = 'MAR_STATUS'
      AND HL.ENABLED_FLAG = 'Y'
      AND HL.LOOKUP_CODE =PAPF.MARITAL_STATUS
      ) "MARITAL_STATUS" ,
      (SELECT hl.meaning
      FROM HR_LOOKUPS HL
      WHERE hl.lookup_type = 'AE_NATIONALITY'
      AND HL.ENABLED_FLAG = 'Y'
      AND SYSDATE BETWEEN NVL(HL. START_DATE_ACTIVE,SYSDATE) AND NVL(END_DATE_ACTIVE,SYSDATE)
      AND LOOKUP_CODE=PAPF.PER_INFORMATION18
      ) "NATIONALITY",
      NVL(XXTEC_HR_REPORTS_PKG.XXTEC_PAY_ELEMENT_F ( PAA.ASSIGNMENT_ACTION_ID , SYSDATE, 'Basic Salary', 'Pay Value'),0) BASIC_SALARY ,
      NVL(XXTEC_HR_REPORTS_PKG.XXTEC_PAY_ELEMENT_F ( PAA.ASSIGNMENT_ACTION_ID , SYSDATE, 'Utilities Allowance', 'Pay Value'),0)
      + NVL(XXTEC_HR_REPORTS_PKG.XXTEC_PAY_ELEMENT_F ( PAA.ASSIGNMENT_ACTION_ID , SYSDATE, 'Utilities Allowance Actual', 'Pay Value'),0)
      + NVL(XXTEC_HR_REPORTS_PKG.XXTEC_PAY_ELEMENT_F ( PAA.ASSIGNMENT_ACTION_ID , SYSDATE, 'Utilities Allowance Other Grades', 'Pay Value'),0) "MONTHLY_UTILITIES_ALLOWANCE" ,
      NVL(XXTEC_HR_REPORTS_PKG.XXTEC_PAY_ELEMENT_F ( PAA.ASSIGNMENT_ACTION_ID , SYSDATE, 'Transportation Allowance', 'Pay Value'),0)
      + NVL(XXTEC_HR_REPORTS_PKG.XXTEC_PAY_ELEMENT_F ( PAA.ASSIGNMENT_ACTION_ID , SYSDATE, 'Transportation Allowance Other Grades', 'Pay Value'),0) "TRANS_ALLOWANCE" ,
      NVL(XXTEC_HR_REPORTS_PKG.XXTEC_PAY_ELEMENT_F ( PAA.ASSIGNMENT_ACTION_ID , SYSDATE, 'Housing Allowance', 'Pay Value'),0)
      + NVL(XXTEC_HR_REPORTS_PKG.XXTEC_PAY_ELEMENT_F ( PAA.ASSIGNMENT_ACTION_ID , SYSDATE, 'Housing Allowance Other Grades', 'Pay Value'),0) "HOUSING_ALLOWANCE",
      NVL(XXTEC_HR_REPORTS_PKG.XXTEC_PAY_ELEMENT_F ( PAA.ASSIGNMENT_ACTION_ID , SYSDATE, 'Social Allowance', 'Pay Value'),0)
      + NVL(XXTEC_HR_REPORTS_PKG.XXTEC_PAY_ELEMENT_F ( PAA.ASSIGNMENT_ACTION_ID , SYSDATE, 'Social Allowance Other Grades', 'Pay Value'),0) "SOCIAL_ALLOWANCE" ,
      NVL(XXTEC_HR_REPORTS_PKG.XXTEC_PAY_ELEMENT_F ( PAA.ASSIGNMENT_ACTION_ID , SYSDATE, 'Children Allowance', 'Pay Value'),0) CHILDREN_ALLOWANCE ,
      NVL(XXTEC_HR_REPORTS_PKG.XXTEC_PAY_ELEMENT_F ( PAA.ASSIGNMENT_ACTION_ID , SYSDATE, 'Surplus Allowance', 'Pay Value'),0) SURPLUS_ALLOWANCE,
      NVL(XXTEC_HR_REPORTS_PKG.XXTEC_PAY_ELEMENT_F ( PAA.ASSIGNMENT_ACTION_ID , SYSDATE, 'Domestic Driver Allowance', 'Pay Value'),0) DRVR_ALWNC_GRD_1,
      NVL(XXTEC_HR_REPORTS_PKG.XXTEC_PAY_ELEMENT_F ( PAA.ASSIGNMENT_ACTION_ID , SYSDATE, 'Supplemental Allowance', 'Pay Value'),0) SPLMNTL_ALWNC,
      NVL(XXTEC_HR_REPORTS_PKG.XXTEC_PAY_ELEMENT_F ( PAA.ASSIGNMENT_ACTION_ID , SYSDATE, 'Lumpsum Allowance', 'Pay Value'),0) LMP_SM_ALWNC,
      NVL(XXTEC_HR_REPORTS_PKG.XXTEC_PAY_ELEMENT_F ( PAA.ASSIGNMENT_ACTION_ID , SYSDATE, 'Personal Loan', 'Pay Value'),0) PRSNL_LN,
      NVL(XXTEC_HR_REPORTS_PKG.XXTEC_PAY_ELEMENT_F ( PAA.ASSIGNMENT_ACTION_ID , SYSDATE, 'Personal Pension Deductions', 'Pay Value'),0) PRSNL_PNSN_DEDCTN,
      NVL(XXTEC_HR_REPORTS_PKG.XXTEC_PAY_ELEMENT_F ( PAA.ASSIGNMENT_ACTION_ID , SYSDATE, 'Car Loan', 'Pay Value'),0) CAR_LN,
      NVL(XXTEC_HR_REPORTS_PKG.XXTEC_PAY_ELEMENT_F ( PAA.ASSIGNMENT_ACTION_ID , SYSDATE, 'Special Loan', 'Pay Value'),0) SPCL_LN,
      NVL(XXTEC_HR_REPORTS_PKG.XXTEC_PAY_ELEMENT_F ( PAA.ASSIGNMENT_ACTION_ID , SYSDATE, 'Housing Advance Recovery', 'Pay Value'),0)
      + NVL(XXTEC_HR_REPORTS_PKG.XXTEC_PAY_ELEMENT_F ( PAA.ASSIGNMENT_ACTION_ID , SYSDATE, 'Housing Advance Recovery Other Grades', 'Pay Value'),0) "HSNG_ADVNC_RCVRY",
      NVL(XXTEC_HR_REPORTS_PKG.XXTEC_PAY_ELEMENT_F ( PAA.ASSIGNMENT_ACTION_ID , SYSDATE, 'Accomodation Recovery', 'Pay Value'),0) TEC_ACMDTN_RCVRY,
      NVL(XXTEC_HR_REPORTS_PKG.XXTEC_PAY_ELEMENT_F ( PAA.ASSIGNMENT_ACTION_ID , SYSDATE, 'Oman Pension', 'Pay Value'),0)
      + NVL(XXTEC_HR_REPORTS_PKG.XXTEC_PAY_ELEMENT_F ( PAA.ASSIGNMENT_ACTION_ID , SYSDATE, 'Saudi Pension', 'Pay Value'),0)
      + NVL(XXTEC_HR_REPORTS_PKG.XXTEC_PAY_ELEMENT_F ( PAA.ASSIGNMENT_ACTION_ID , SYSDATE, 'UAE Pension', 'Pay Value'),0)
      + NVL(XXTEC_HR_REPORTS_PKG.XXTEC_PAY_ELEMENT_F ( PAA.ASSIGNMENT_ACTION_ID , SYSDATE, 'UAE Pension Employee Amount', 'Pay Value'),0) "PENSION"
      FROM APPS.PER_ALL_PEOPLE_F PAPF ,
      APPS.PER_ALL_ASSIGNMENTS_F PAAF ,
      PER_PERIODS_OF_SERVICE PPOS ,
      PAY_PAYROLLS_F PPF ,
      PAY_PAYROLL_ACTIONS PAY_ACT ,
      PAY_ASSIGNMENT_ACTIONS PAA
      WHERE papf.person_id = paaf.person_id
      AND paaf.primary_flag = 'Y'
      AND PPOS.PERSON_ID = PAPF.PERSON_ID
      AND TRUNC (SYSDATE) BETWEEN PAPF.EFFECTIVE_START_DATE AND PAPF.EFFECTIVE_END_DATE
      AND TRUNC (SYSDATE) BETWEEN paaf.effective_start_date AND PAAF.EFFECTIVE_END_DATE
      AND PPF.PAYROLL_ID = PAAF.PAYROLL_ID
      AND PAY_ACT.PAYROLL_ACTION_ID = PAA.PAYROLL_ACTION_ID
      AND PAA.ASSIGNMENT_ID = PAAF.ASSIGNMENT_ID
      AND PAY_ACT.PAYROLL_ID = PAAF.PAYROLL_ID
      AND PAY_ACT.ACTION_TYPE IN ('R', 'Q')
      AND PAPF.BUSINESS_GROUP_ID = TO_NUMBER (APPS.FND_PROFILE.VALUE ('PER_BUSINESS_GROUP_ID'))
      AND PAY_ACT.PAYROLL_ACTION_ID =
      (SELECT MAX (PPA.PAYROLL_ACTION_ID)
      FROM pay_payroll_actions ppa ,
      PAY_ASSIGNMENT_ACTIONS PAA
      WHERE UPPER (TO_CHAR (effective_date , 'Mon-YYYY' ) ) LIKE UPPER (:P_PERIOD)
      AND action_type IN ('R', 'Q')
      AND payroll_id = pay_act.payroll_id
      AND PPA.PAYROLL_ACTION_ID = PAA.PAYROLL_ACTION_ID
      AND PAA.ASSIGNMENT_ID = PAAF.ASSIGNMENT_ID
      AND ppa.element_set_id IS NULL
      )
      AND PPF.PAYROLL_ID = :P_PAYROLL_ID
      AND PAAF.ORGANIZATION_ID = NVL(:P_DEPARTMENT_ID,PAAF.ORGANIZATION_ID)
      ORDER BY PAPF.EMPLOYEE_NUMBER ASC
      ]]>
      </sqlStatement>
      <sqlStatement name="Q2">
      <![CDATA[SELECT
      PAPF.EMPLOYEE_NUMBER "EMP_NO",
      PEEV1.SCREEN_ENTRY_VALUE "BASIC_G",
      PEEV2.SCREEN_ENTRY_VALUE "SUPPLEMENTAL_G",
      PEEV3.SCREEN_ENTRY_VALUE "SURPLUS_G",
      PEEV4.SCREEN_ENTRY_VALUE "LUMPSUM_G",
      PEEV5.SCREEN_ENTRY_VALUE "HOUSING_G",
      PEEV6.SCREEN_ENTRY_VALUE "TRANSPORT_G",
      PEEV7.SCREEN_ENTRY_VALUE "UTILITIES_G",
      PEEV8.SCREEN_ENTRY_VALUE "CHILDREN_G",
      PEEV9.SCREEN_ENTRY_VALUE "SOCIAL_G",
      PEEV10.SCREEN_ENTRY_VALUE "COLA_G",
      PEEV11.SCREEN_ENTRY_VALUE "FURNITURE_G"
      FROM
      PAY_ELEMENT_TYPES_F PET,
      PAY_ELEMENT_LINKS_F PEL,
      PAY_ELEMENT_ENTRIES_F PEE,
      PAY_ELEMENT_ENTRY_VALUES_F PEEV1,
      PAY_ELEMENT_ENTRY_VALUES_F PEEV2,
      PAY_ELEMENT_ENTRY_VALUES_F PEEV3,
      PAY_ELEMENT_ENTRY_VALUES_F PEEV4,
      PAY_ELEMENT_ENTRY_VALUES_F PEEV5,
      PAY_ELEMENT_ENTRY_VALUES_F PEEV6,
      PAY_ELEMENT_ENTRY_VALUES_F PEEV7,
      PAY_ELEMENT_ENTRY_VALUES_F PEEV8,
      PAY_ELEMENT_ENTRY_VALUES_F PEEV9,
      PAY_ELEMENT_ENTRY_VALUES_F PEEV10,
      PAY_ELEMENT_ENTRY_VALUES_F PEEV11,
      pay_input_values_f pivf1,
      pay_input_values_f pivf2,
      pay_input_values_f pivf3,
      pay_input_values_f pivf4,
      pay_input_values_f pivf5,
      pay_input_values_f pivf6,
      pay_input_values_f pivf7,
      pay_input_values_f pivf8,
      pay_input_values_f pivf9,
      pay_input_values_f pivf10,
      pay_input_values_f pivf11,
      per_all_people_f PAPF,
      Per_ALL_ASSIGNMENTS_F PAAF
      WHERE
      PET.ELEMENT_TYPE_ID = PEL.ELEMENT_TYPE_ID
      and PEL.ELEMENT_LINK_ID = PEE.ELEMENT_LINK_ID
      and pee.ELEMENT_ENTRY_ID = peev1.ELEMENT_ENTRY_ID
      and pee.ELEMENT_ENTRY_ID = peev2.ELEMENT_ENTRY_ID
      and pee.ELEMENT_ENTRY_ID = peev3.ELEMENT_ENTRY_ID
      and pee.ELEMENT_ENTRY_ID = peev4.ELEMENT_ENTRY_ID
      and pee.ELEMENT_ENTRY_ID = peev5.ELEMENT_ENTRY_ID
      and pee.ELEMENT_ENTRY_ID = peev6.ELEMENT_ENTRY_ID
      and pee.ELEMENT_ENTRY_ID = peev7.ELEMENT_ENTRY_ID
      and pee.ELEMENT_ENTRY_ID = peev8.ELEMENT_ENTRY_ID
      and pee.ELEMENT_ENTRY_ID = peev9.ELEMENT_ENTRY_ID
      and pee.ELEMENT_ENTRY_ID = peev10.ELEMENT_ENTRY_ID
      and pee.ELEMENT_ENTRY_ID = peev11.ELEMENT_ENTRY_ID
      ---
      and PET.ELEMENT_TYPE_ID = Pivf1.ELEMENT_TYPE_ID
      and pivf1.INPUT_VALUE_ID = peev1.INPUT_VALUE_ID
      and pivf1.INPUT_VALUE_ID = 1001
      ---
      and PET.ELEMENT_TYPE_ID = Pivf2.ELEMENT_TYPE_ID
      and pivf2.INPUT_VALUE_ID = peev2.INPUT_VALUE_ID
      and pivf2.INPUT_VALUE_ID = 1002
      ---
      and PET.ELEMENT_TYPE_ID = Pivf3.ELEMENT_TYPE_ID
      and pivf3.INPUT_VALUE_ID = peev3.INPUT_VALUE_ID
      and pivf3.INPUT_VALUE_ID = 1003
      ---
      and PET.ELEMENT_TYPE_ID = Pivf4.ELEMENT_TYPE_ID
      and pivf4.INPUT_VALUE_ID = peev4.INPUT_VALUE_ID
      and pivf4.INPUT_VALUE_ID = 1004
      ---
      and PET.ELEMENT_TYPE_ID = Pivf5.ELEMENT_TYPE_ID
      and pivf5.INPUT_VALUE_ID = peev5.INPUT_VALUE_ID
      and pivf5.INPUT_VALUE_ID = 1005
      ---
      and PET.ELEMENT_TYPE_ID = Pivf6.ELEMENT_TYPE_ID
      and pivf6.INPUT_VALUE_ID = peev6.INPUT_VALUE_ID
      and pivf6.INPUT_VALUE_ID = 1006
      ---
      and PET.ELEMENT_TYPE_ID = Pivf7.ELEMENT_TYPE_ID
      and pivf7.INPUT_VALUE_ID = peev7.INPUT_VALUE_ID
      and pivf7.INPUT_VALUE_ID = 1007
      ---
      and PET.ELEMENT_TYPE_ID = Pivf8.ELEMENT_TYPE_ID
      and pivf8.INPUT_VALUE_ID = peev8.INPUT_VALUE_ID
      and pivf8.INPUT_VALUE_ID = 1008
      ---
      and PET.ELEMENT_TYPE_ID = Pivf9.ELEMENT_TYPE_ID
      and pivf9.INPUT_VALUE_ID = peev9.INPUT_VALUE_ID
      and pivf9.INPUT_VALUE_ID = 1009
      ---
      and PET.ELEMENT_TYPE_ID = Pivf10.ELEMENT_TYPE_ID
      and pivf10.INPUT_VALUE_ID = peev10.INPUT_VALUE_ID
      and pivf10.INPUT_VALUE_ID = 1010
      ---
      and PET.ELEMENT_TYPE_ID = Pivf11.ELEMENT_TYPE_ID
      and pivf11.INPUT_VALUE_ID = peev11.INPUT_VALUE_ID
      and pivf11.INPUT_VALUE_ID = 1011
      ---
      and papf.PERSON_ID = paaf.PERSON_ID
      -- AND PAPF.PERSON_ID = :PERSON_ID
      AND PAPF.BUSINESS_GROUP_ID = TO_NUMBER (APPS.FND_PROFILE.VALUE ('PER_BUSINESS_GROUP_ID'))
      and paaf.payroll_id = :P_PAYROLL_ID
      AND PAAF.ORGANIZATION_ID = NVL(:P_DEPARTMENT_ID,PAAF.ORGANIZATION_ID)
      and PAAF.ASSIGNMENT_ID = PEE.ASSIGNMENT_ID
      And PET.PROCESSING_TYPE = 'R'
      -- and pee.effective_start_date >= to_date('01/01/2012','dd/mm/yyyy')
      and pet.element_name = 'Salary Entitlement'
      and pet.business_group_id = 81
      and papf.current_employee_flag = 'Y'
      and sysdate between papf.effective_start_date and papf.effective_end_date
      and sysdate between paaf.effective_start_date and paaf.effective_end_date
      -- and sysdate between PEE.effective_start_date and PEE.effective_end_date
      and pee.effective_start_date = (SELECT max(effective_date)
      FROM pay_payroll_actions ppa ,
      PAY_ASSIGNMENT_ACTIONS PAA
      WHERE UPPER (TO_CHAR (effective_date , 'Mon-YYYY' ) ) LIKE UPPER (:P_PERIOD)
      AND action_type IN ('R', 'Q')
      AND payroll_id = paaf.payroll_id
      AND PPA.PAYROLL_ACTION_ID = PAA.PAYROLL_ACTION_ID
      AND PAA.ASSIGNMENT_ID = PAAF.ASSIGNMENT_ID
      AND ppa.element_set_id IS NULL
      )
      and UPPER (TO_CHAR (pee.effective_start_date , 'Mon-YYYY' ) ) LIKE UPPER (:P_PERIOD)
      and sysdate between PEEV1.effective_start_date and PEEV1.effective_end_date
      and sysdate between PEEV2.effective_start_date and PEEV2.effective_end_date
      and sysdate between PEEV3.effective_start_date and PEEV3.effective_end_date
      and sysdate between PEEV4.effective_start_date and PEEV4.effective_end_date
      and sysdate between PEEV5.effective_start_date and PEEV5.effective_end_date
      and sysdate between PEEV6.effective_start_date and PEEV6.effective_end_date
      and sysdate between PEEV7.effective_start_date and PEEV7.effective_end_date
      and sysdate between PEEV8.effective_start_date and PEEV8.effective_end_date
      and sysdate between PEEV9.effective_start_date and PEEV9.effective_end_date
      and sysdate between PEEV10.effective_start_date and PEEV10.effective_end_date
      and sysdate between PEEV11.effective_start_date and PEEV11.effective_end_date
      ORDER BY PAPF.EMPLOYEE_NUMBER ASC
      ]]>
      </sqlStatement>
      <sqlStatement name="Q3">
      <![CDATA[SELECT
      (:BASIC_SALARY+:MONTHLY_UTILITIES_ALLOWANCE+:TRANS_ALLOWANCE+:HOUSING_ALLOWANCE+:SOCIAL_ALLOWANCE+:CHILDREN_ALLOWANCE+:SURPLUS_ALLOWANCE+:DRVR_ALWNC_GRD_1+:SPLMNTL_ALWNC+:LMP_SM_ALWNC) "GROSS_EARNINGS",
      (:PRSNL_LN+:PRSNL_PNSN_DEDCTN+:CAR_LN+:SPCL_LN+:HSNG_ADVNC_RCVRY+:TEC_ACMDTN_RCVRY+:PENSION) "GROSS_DEDUCTIONS",
      (:BASIC_SALARY+:MONTHLY_UTILITIES_ALLOWANCE+:TRANS_ALLOWANCE+:HOUSING_ALLOWANCE+:SOCIAL_ALLOWANCE+:CHILDREN_ALLOWANCE+:SURPLUS_ALLOWANCE+:DRVR_ALWNC_GRD_1+:SPLMNTL_ALWNC+:LMP_SM_ALWNC) - (:PRSNL_LN+:PRSNL_PNSN_DEDCTN+:CAR_LN+:SPCL_LN+:HSNG_ADVNC_RCVRY+:TEC_ACMDTN_RCVRY+:PENSION) "NET_SALARY"
      FROM DUAL
      ]]>
      </sqlStatement>
      </dataQuery>
      <dataStructure>
      <Group name ="G_Salary_Details" source="Q1">
      <element name="EMP_CODE" value="EMP_CODE"/>
      <element name="EMP_NAME" value="EMP_NAME"/>
      <element name="DIVISION" value="DIVISION"/>
      <element name="DEPARTMENT" value="DEPARTMENT"/>
      <element name="SECTION" value="SECTION"/>
      <element name="JOB_TITLE" value="JOB_TITLE"/>
      <element name="POSITION" value="POSITION"/>
      <element name="GRADE" value="GRADE"/>
      <element name="EMPLOYMENT_STATUS" value="EMPLOYMENT_STATUS"/>
      <element name="DATE_OF_JOINING" value="DATE_OF_JOINING"/>
      <element name="GENDER" value="GENDER"/>
      <element name="MARITAL_STATUS" value="MARITAL_STATUS"/>
      <element name="NATIONALITY" value="NATIONALITY"/>
      <element name="BASIC_SALARY" value="BASIC_SALARY"/>
      <element name="MONTHLY_UTILITIES_ALLOWANCE" value="MONTHLY_UTILITIES_ALLOWANCE"/>
      <element name="TRANS_ALLOWANCE" value="TRANS_ALLOWANCE"/>
      <element name="HOUSING_ALLOWANCE" value="HOUSING_ALLOWANCE"/>
      <element name="SOCIAL_ALLOWANCE" value="SOCIAL_ALLOWANCE"/>
      <element name="CHILDREN_ALLOWANCE" value="CHILDREN_ALLOWANCE"/>
      <element name="SURPLUS_ALLOWANCE" value="SURPLUS_ALLOWANCE"/>
      <element name="DRVR_ALWNC_GRD_1" value="DRVR_ALWNC_GRD_1"/>
      <element name="SPLMNTL_ALWNC" value="SPLMNTL_ALWNC"/>
      <element name="LMP_SM_ALWNC" value="LMP_SM_ALWNC"/>
      <element name="PRSNL_LN" value="PRSNL_LN"/>
      <element name="PRSNL_PNSN_DEDCTN" value="PRSNL_PNSN_DEDCTN"/>
      <element name="CAR_LN" value="CAR_LN"/>
      <element name="SPCL_LN" value="SPCL_LN"/>
      <element name="HSNG_ADVNC_RCVRY" value="HSNG_ADVNC_RCVRY"/>
      <element name="TEC_ACMDTN_RCVRY" value="TEC_ACMDTN_RCVRY"/>
      <element name="PENSION" value="PENSION"/>
      <Group name ="G_Net_Salary" source="Q3">
      <element name="GROSS_DEDUCTIONS" value="GROSS_DEDUCTIONS"/>
      <element name="GROSS_EARNINGS" value="GROSS_EARNINGS"/>
      <element name="NET_SALARY" value="NET_SALARY"/>
      </Group>
      </Group>
      <Group name ="G_Salary_Gross" source="Q2">
      <element name="EMP_NO" value="EMP_NO"/>
      <element name="BASIC_G" value="BASIC_G"/>
      <element name="SUPPLEMENTAL_G" value="SUPPLEMENTAL_G"/>
      <element name="SURPLUS_G" value="SURPLUS_G"/>
      <element name="LUMPSUM_G" value="LUMPSUM_G"/>
      <element name="HOUSING_G" value="HOUSING_G"/>
      <element name="TRANSPORT_G" value="TRANSPORT_G"/>
      <element name="UTILITIES_G" value="UTILITIES_G"/>
      <element name="CHILDREN_G" value="CHILDREN_G"/>
      <element name="SOCIAL_G" value="SOCIAL_G"/>
      <element name="COLA_G" value="COLA_G"/>
      <element name="FURNITURE_G" value="FURNITURE_G"/>
      </Group>
      </dataStructure>
      </dataTemplate>
        • 1. Re: How to link 2 sql query inside XML Data Template (payroll query) ?
          AlexAnd
          in the next time use appropriate forum
          BI Publisher - BI Publisher
          E-Business Suite - https://forums.oracle.com/forums/category.jspa?categoryID=3

          btw
          >
          for SQL query 2 , the output shows blank cell.
          >
          are you sure what data exists?
          pls re-check

          based on Q2 looks like what Q1 and Q2 not linked between
          >
          -- AND PAPF.PERSON_ID = :PERSON_ID
          >
          but they have "P_PAYROLL_ID" so it may be interpreted as related

          >
          I wanted to know how we can link the 2 sql query inside XML Data Template.
          >
          search by "How to Define a Data Link Between Queries" in http://docs.oracle.com/cd/E10415_01/doc/bi.1013/e12187/T421739T434255.htm
          >
          there are two methods for linking queries: using bind variables or using the <link> element to define the link between queries.
          • 2. Re: How to link 2 sql query inside XML Data Template (payroll query) ?
            User406158
            Dear AlexAnd,

            First of all,sorry for putting the question on the wrong forum. Second, thanks for making my day :) , as I was able to solve the issue. Before posting the above question, I tried using "AND PAPF.PERSON_ID = :PERSON_ID" but it didn't work. Again I tried the same , and this time added "<element name="PERSON_ID" value="PERSON_ID"/>" under the <Group name ="G_Salary_Details" source="Q1"> . This time it worked without any issues.

            Again thanks a lot for support.

            Thanks & Regards,
            SKC