<?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>
<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>
No comments:
Post a Comment