This discussion is archived
2 Replies Latest reply: Feb 5, 2013 6:02 AM by User406158 RSS

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

User406158 Explorer
Currently Being Moderated
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 Guru
    Currently Being Moderated
    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 Explorer
    Currently Being Moderated
    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

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points