Oracle apps EBS r12 Report for the HRMS and
Payroll Report –
Overseas Travel Details Report
---------------- XXBEXOVERSEASTRAVELDETAILREP.xml
--------------------------
Data
Definition File :
<?xml
version="1.0" encoding="UTF-8" ?>
<dataTemplate
name="XXBEXOVERSEASTRAVELDETAILREP" version="1.0">
<properties>
<property
name="xml_tag_case" value="upper" />
</properties>
<parameters>
<parameter
name="P_IN_CHR_LEGAL_ENTITY" dataType =
"VARCHAR2"></parameter>
<parameter
name="P_IN_CHR_DEPT" dataType =
"VARCHAR2"></parameter>
<parameter
name="P_IN_NUM_PERSON_ID" dataType =
"varchar2"></parameter>
<parameter name="P_IN_NUM_LOCATION"
dataType = "varchar2"></parameter>
<parameter
name="P_IN_CHR_COUNTRY" dataType =
"varchar2"></parameter>
<parameter
name="P_IN_CHR_TRAVEL_PURPOSE" dataType =
"varchar2"></parameter>
</parameters>
<dataQuery>
<sqlStatement
name="Q_OVERSEAS">
<![CDATA[
SELECT papf.person_id,
papf.employee_number,
initcap(papf.title
||' '
||papf.first_name
||' '
||papf.middle_names
||' '
||papf.last_name) employee_name,
pgd.segment1 legal_entity,
pgd.segment2 grade,
SUBSTR(pp.NAME,1,instr(pp.NAME,'|') -1) designation,
SUBSTR(haou.NAME,instr(haou.NAME,'.',1,1)+1)
sub_dept,
ppd.segment3 department,
hl.location_code work_location,
TO_CHAR(ppos.date_start,'DD-Mon-YYYY')
joining_date,
ROUND((TRUNC(SYSDATE)-ppos.date_start)/365,2)
job_tenure,
TO_CHAR(to_date(ppei.pei_information1,'YYYY/MM/DD
HH24:MI:SS'),'DD-Mon-YYYY') travel_date_from,
TO_CHAR(to_date(ppei.pei_information2,'YYYY/MM/DD
HH24:MI:SS'),'DD-Mon-YYYY') travel_date_to,
ppei.pei_information3 travel_duration,
ppei.pei_information4 travel_country,
ppei.pei_information5 travel_purpose,
ppei.pei_information6 travel_cost,
ppei.pei_information7 company_cost,
ppei.pei_information8 external_cost,
ppei.pei_information9 external_body,
ppei.pei_information10 remarks
FROM
per_people_extra_info ppei,
per_all_people_f papf,
per_all_assignments_f paaf,
per_grades pg,
per_grade_definitions pgd,
per_positions pp,
per_position_definitions ppd,
hr_all_organization_units haou,
hr_locations hl,
per_periods_of_service ppos
WHERE 1 =1
AND ppei.person_id = papf.person_id
AND papf.person_id = paaf.person_id
AND
paaf.primary_flag ='Y'
AND paaf.grade_id = pg.grade_id(+)
AND
pg.grade_definition_id =
pgd.grade_definition_id(+)
AND
paaf.position_id =
pp.position_id(+)
AND
pp.position_definition_id = ppd.position_definition_id(+)
AND
paaf.organization_id =
haou.organization_id(+)
AND
paaf.location_id =
hl.location_id(+)
AND papf.person_id = ppos.person_id
AND
paaf.period_of_service_id = ppos.period_of_service_id
AND ppei.creation_date
BETWEEN papf.effective_start_date AND papf.effective_end_date
AND ppei.creation_date
BETWEEN paaf.effective_start_date AND paaf.effective_end_date
AND
ppei.pei_information_category =
'BEX_OVERSEAS_DETAILS'
--Parameter Section
AND
NVL(pgd.segment1,'N') =
NVL(:P_IN_CHR_LEGAL_ENTITY,NVL(pgd.segment1,'N'))
AND NVL(ppd.segment3,'N') =
NVL(:P_IN_CHR_DEPT,NVL(ppd.segment3,'N'))
AND papf.person_id =
NVL(:P_IN_NUM_PERSON_ID,papf.person_id)
AND
NVL(hl.location_code,'N') =
NVL(:P_IN_NUM_LOCATION,NVL(hl.location_code,'N'))
AND
NVL(ppei.pei_information4,'N') =
NVL(:P_IN_CHR_COUNTRY,NVL(ppei.pei_information4,'N'))
AND
NVL(ppei.pei_information5,'N') =
NVL(:P_IN_CHR_TRAVEL_PURPOSE,NVL(ppei.pei_information5,'N'))
ORDER BY
papf.employee_number,
to_date(ppei.pei_information1,'YYYY/MM/DD
HH24:MI:SS')
]]>
</sqlStatement>
</dataQuery>
<dataStructure>
<group name="G_OVERSEAS"
dataType="varchar2" source="Q_OVERSEAS">
<element name="PERSON_ID" dataType="varchar2"
value="PERSON_ID"/>
<element
name="LEGAL_ENTITY" dataType="varchar2" value="LEGAL_ENTITY"/>
<element
name="EMPLOYEE_NAME" dataType="varchar2"
value="EMPLOYEE_NAME"/>
<element
name="EMPLOYEE_NUMBER" dataType="varchar2"
value="EMPLOYEE_NUMBER"/>
<element
name="GRADE" dataType="varchar2"
value="GRADE"/>
<element name="DESIGNATION"
dataType="varchar2" value="DESIGNATION"/>
<element
name="DEPARTMENT" dataType="varchar2"
value="DEPARTMENT"/>
<element
name="SUB_DEPT" dataType="varchar2"
value="SUB_DEPT"/>
<element
name="WORK_LOCATION" dataType="varchar2"
value="WORK_LOCATION"/>
<element
name="JOINING_DATE" dataType="varchar2"
value="JOINING_DATE"/>
<element
name="JOB_TENURE" dataType="varchar2"
value="JOB_TENURE" />
<element
name="TRAVEL_DATE_FROM" dataType="varchar2"
value="TRAVEL_DATE_FROM"/>
<element
name="TRAVEL_DATE_TO" dataType="varchar2"
value="TRAVEL_DATE_TO"/>
<element
name="TRAVEL_DURATION" dataType="varchar2"
value="TRAVEL_DURATION"/>
<element
name="TRAVEL_COUNTRY" dataType="varchar2"
value="TRAVEL_COUNTRY"/>
<element
name="TRAVEL_PURPOSE" dataType="varchar2"
value="TRAVEL_PURPOSE"/>
<element
name="TRAVEL_COST" dataType="varchar2"
value="TRAVEL_COST"/>
<element
name="COMPANY_COST" dataType="varchar2" value="COMPANY_COST"/>
<element
name="EXTERNAL_COST" dataType="varchar2"
value="EXTERNAL_COST"/>
<element
name="EXTERNAL_BODY" dataType="varchar2"
value="EXTERNAL_BODY"/>
<element
name="REMARKS" dataType="varchar2"
value="REMARKS"/>
</group>
</dataStructure>
</dataTemplate>
----------------------------------
End----------------
Download the Format of
the XXBEXOVERSEASTRAVELDETAILREP.rtf::
https://drive.google.com/file/d/1NXwJbigodrlMYloJGcJGzE9qtxSsFT_G/view?usp=sharing
Download the Format of
the XXBEXOVERSEASTRAVELDETAILREP.xml ::
https://drive.google.com/file/d/1GQ6bGyIynCVPOhH6-zp_WxLUbm9-fQfO/view?usp=sharing
Download the Format of
the
XXBEXOVERSEASTRAVELDETAILREP_CP.ldt::
https://drive.google.com/file/d/1NkMzDvbPSo2_DaD-iEoexRK6JYY6XMd-/view?usp=sharing
Download the Format of
the
XXBEXOVERSEASTRAVELDETAILREP_DD.ldt ::
https://drive.google.com/file/d/1AXS1LVrcGJ2mNsP3DzJ1r4VB6yIzX56r/view?usp=sharing
Download the Format of
the
XXBEXOVERSEASTRAVELDETAILREP_REQ.ldt::
https://drive.google.com/file/d/1vsAiOpggM0KT2-8vNo8M9tBsvpwQa--z/view?usp=sharing
No comments:
Post a Comment