Oracle apps EBS r12 Report for the HRMS and
Payroll Report –
Relocation Letter
Data Definition XML File :
Format Like This.
---------------- XXBEXRELOCLETTER.xml --------------------------
<?xml version="1.0" encoding="UTF-8" ?>
<dataTemplate name="XXBEXRELOCLETTER" version="1.0">
<properties>
<property name="xml_tag_case" value="upper" />
</properties>
<parameters>
<parameter name="P_IN_EFFECTIVE_DATE" dataType = "VARCHAR2"></parameter>
<parameter name="P_IN_PERSON_ID" dataType = "VARCHAR2"></parameter>
<parameter name="P_IN_CONCEN1" dataType = "varchar2"></parameter>
<parameter name="P_IN_CONCEN2" dataType = "varchar2"></parameter>
<parameter name="P_IN_CONCEN3" dataType = "varchar2"></parameter>
<parameter name="P_IN_CONCEN4" dataType = "varchar2"></parameter>
<parameter name="P_IN_CONCEN5" dataType = "varchar2"></parameter>
</parameters>
<dataQuery>
<sqlStatement name="Q_RELOCATION">
<![CDATA[
SELECT TO_CHAR(SYSDATE,'DD-Mon-YYYY') server_date,
trim(initcap(papf.title
||' '
||papf.first_name
||' '
||papf.middle_names
||DECODE(papf.middle_names,NULL,'',' ')
||papf.last_name)) employee_name,
papf.employee_number,
initcap(papf.title
||' '
||papf.last_name) last_name,
pjd.segment2 new_des,
ppd.segment3 new_dep,
hl.location_code new_loc,
TO_CHAR(paaf.effective_start_date,'DD-Mon-YYYY') date_of_reloc,
DECODE( substr(haou.name,1,(instr(haou.name,'.',1,1)-1)),'BAIL','Bangladesh Antibiotic Industries Limited','BPL','Beximco Pharmaceuticals Limited','IIS','I'
||'&'
||'I Services Limited','PCL','Pharmatek Chemicals Limited','SPL', 'Shuktara Printers Limited') new_leg_ent,
(SELECT trim(initcap(ppf.first_name
||' '
||ppf.middle_names
||DECODE(ppf.middle_names,NULL,'',' ')
||ppf.last_name)) person_name
FROM per_people_extra_info pei ,
per_all_people_f ppf ,
fnd_user usr ,
pqh_roles rls
WHERE information_type = 'PQH_ROLE_USERS'
AND pei.person_id = ppf.person_id
AND TRUNC(sysdate) BETWEEN ppf.effective_start_date AND ppf.effective_end_date
AND usr.employee_id = ppf.person_id
AND rls.role_name = 'HR Letters Signatory'
AND rls.role_id = to_number(pei.pei_information3)
AND rownum =1
) hr_head,
(SELECT jobd.segment2
FROM per_people_extra_info pei ,
per_all_people_f ppf ,
fnd_user usr ,
pqh_roles rls,
per_all_assignments_f paf,
per_jobs pjob,
per_job_definitions jobd
WHERE information_type = 'PQH_ROLE_USERS'
AND pei.person_id = ppf.person_id
AND usr.employee_id = ppf.person_id
AND paf.person_id =ppf.person_id
AND rls.role_name ='HR Letters Signatory'
AND paf.job_id = pjob.job_id(+)
AND pjob.job_definition_id = jobd.job_definition_id(+)
AND rls.role_id = to_number(pei.pei_information3)
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 rownum=1
) hr_head_desig,
(SELECT trim(initcap(peop.first_name
||' '
||peop.middle_names
||DECODE(peop.middle_names,NULL,'',' ')
||peop.last_name))
FROM per_all_people_f peop
WHERE ROWNUM =1
AND person_id=:P_IN_CONCEN1
) concern1,
(SELECT SUBSTR(pp.NAME,1,instr(pp.NAME,'|')-1)
FROM per_all_assignments_f asgn,
per_all_positions pp
WHERE 1 =1
AND asgn.person_id =:P_IN_CONCEN1
AND asgn.position_id=pp.position_id
AND SYSDATE BETWEEN asgn.effective_start_date AND asgn.effective_end_date
) pos1,
(SELECT trim(initcap(peop.first_name
||' '
||peop.middle_names
||DECODE(peop.middle_names,NULL,'',' ')
||peop.last_name))
FROM per_all_people_f peop
WHERE ROWNUM =1
AND person_id=:P_IN_CONCEN2
) concern2,
(SELECT SUBSTR(pp.NAME,1,instr(pp.NAME,'|')-1)
FROM per_all_assignments_f asgn,
per_all_positions pp
WHERE 1 =1
AND asgn.person_id =:P_IN_CONCEN2
AND asgn.position_id=pp.position_id
AND SYSDATE BETWEEN asgn.effective_start_date AND asgn.effective_end_date
) pos2,
(SELECT trim(initcap(peop.first_name
||' '
||peop.middle_names
||DECODE(peop.middle_names,NULL,'',' ')
||peop.last_name))
FROM per_all_people_f peop
WHERE ROWNUM =1
AND person_id=:P_IN_CONCEN3
) concern3,
(SELECT SUBSTR(pp.NAME,1,instr(pp.NAME,'|')-1)
FROM per_all_assignments_f asgn,
per_all_positions pp
WHERE 1 =1
AND asgn.person_id =:P_IN_CONCEN3
AND asgn.position_id=pp.position_id
AND SYSDATE BETWEEN asgn.effective_start_date AND asgn.effective_end_date
) pos3,
(SELECT trim(initcap(peop.first_name
||' '
||peop.middle_names
||DECODE(peop.middle_names,NULL,'',' ')
||peop.last_name))
FROM per_all_people_f peop
WHERE ROWNUM =1
AND person_id=:P_IN_CONCEN4
) concern4,
(SELECT SUBSTR(pp.NAME,1,instr(pp.NAME,'|')-1)
FROM per_all_assignments_f asgn,
per_all_positions pp
WHERE 1 =1
AND asgn.person_id =:P_IN_CONCEN4
AND asgn.position_id=pp.position_id
AND SYSDATE BETWEEN asgn.effective_start_date AND asgn.effective_end_date
) pos4,
(SELECT trim(initcap(peop.first_name
||' '
||peop.middle_names
||DECODE(peop.middle_names,NULL,'',' ')
||peop.last_name))
FROM per_all_people_f peop
WHERE ROWNUM =1
AND person_id=:P_IN_CONCEN5
) concern5,
(SELECT SUBSTR(pp.NAME,1,instr(pp.NAME,'|')-1)
FROM per_all_assignments_f asgn,
per_all_positions pp
WHERE 1 =1
AND asgn.person_id =:P_IN_CONCEN5
AND asgn.position_id=pp.position_id
AND SYSDATE BETWEEN asgn.effective_start_date AND asgn.effective_end_date
) pos5 ,
old_assgn.designation,
old_assgn.department,
old_assgn.location_code,
old_assgn.legal_entity
FROM per_all_people_f papf,
per_all_assignments_f paaf,
per_jobs pj,
per_job_definitions pjd,
per_positions pp,
per_position_definitions ppd,
hr_locations hl,
per_grades pg,
per_grade_definitions pgd,
(SELECT paaf.person_id,
pjd.segment2 designation,
ppd.segment3 department,
hl.location_code,
DECODE(pgd.segment1,'BAIL','Bangladesh Antibiotic Industries Limited','BPL','Beximco Pharmaceuticals Limited','IIS','I'
||'&'
||'I Services Limited','PCL','Pharmatek Chemicals Limited','SPL', 'Shuktara Printers Limited') legal_entity
FROM per_all_assignments_f paaf,
per_jobs pj,
per_job_definitions pjd,
per_positions pp,
per_position_definitions ppd,
hr_locations hl,
per_grades pg,
per_grade_definitions pgd
WHERE 1 =1
AND paaf.job_id = pj.job_id(+)
AND pj.job_definition_id = pjd.job_definition_id(+)
AND paaf.position_id = pp.position_id (+)
AND pp.position_definition_id = ppd.position_definition_id(+)
AND paaf.location_id = hl.location_id(+)
AND paaf.grade_id = pg.grade_id(+)
AND pg.grade_definition_id = pgd.grade_definition_id(+)
AND TRUNC(paaf.effective_end_date) =
(SELECT TRUNC(asgn.effective_start_date)-1
FROM per_all_assignments_f asgn
WHERE asgn.change_reason='EMP_REL'
AND asgn.person_id = paaf.person_id
AND to_date(:P_IN_EFFECTIVE_DATE,'YYYY/MM/DD HH24:MI:SS') BETWEEN asgn.effective_start_date AND asgn.effective_end_date
AND rownum=1
)
AND paaf.person_id =NVL(:P_IN_PERSON_ID,paaf.person_id)
) old_assgn,
hr_all_organization_units haou
WHERE 1 =1
AND papf.person_id = paaf.person_id
AND paaf.job_id = pj.job_id(+)
and paaf.organization_id = haou.organization_id
AND pj.job_definition_id = pjd.job_definition_id(+)
AND paaf.position_id = pp.position_id (+)
AND pp.position_definition_id = ppd.position_definition_id(+)
AND paaf.location_id = hl.location_id(+)
AND paaf.grade_id = pg.grade_id(+)
AND pg.grade_definition_id = pgd.grade_definition_id(+)
AND papf.person_id =old_assgn.person_id
AND papf.current_employee_flag='Y'
AND paaf.change_reason ='EMP_REL'
AND paaf.person_id =NVL(:P_IN_PERSON_ID,paaf.person_id)
AND to_date(:P_IN_EFFECTIVE_DATE,'YYYY/MM/DD HH24:MI:SS') BETWEEN paaf.effective_start_date AND paaf.effective_end_date
AND to_date(:P_IN_EFFECTIVE_DATE,'YYYY/MM/DD HH24:MI:SS') BETWEEN papf.effective_start_date AND papf.effective_end_date
]]>
</sqlStatement>
</dataQuery>
<dataStructure>
<group name="G_RELOCATION" dataType="varchar2" source="Q_RELOCATION">
<element name="SERVER_DATE" dataType="varchar2" value="SERVER_DATE"/>
<element name="EMPLOYEE_NAME" dataType="varchar2" value="EMPLOYEE_NAME"/>
<element name="EMPLOYEE_NUMBER" dataType="varchar2" value="EMPLOYEE_NUMBER"/>
<element name="LAST_NAME" dataType="varchar2" value="LAST_NAME"/>
<element name="DESIGNATION" dataType="varchar2" value="DESIGNATION"/>
<element name="DEPARTMENT" dataType="varchar2" value="DEPARTMENT"/>
<element name="LOCATION_CODE" dataType="varchar2" value="LOCATION_CODE"/>
<element name="LEGAL_ENTITY" dataType="varchar2" value="LEGAL_ENTITY"/>
<element name="NEW_DES" dataType="varchar2" value="NEW_DES"/>
<element name="NEW_DEP" dataType="varchar2" value="NEW_DEP"/>
<element name="NEW_LOC" dataType="varchar2" value="NEW_LOC"/>
<element name="NEW_LEG_ENT" dataType="varchar2" value="NEW_LEG_ENT"/>
<element name="DATE_OF_RELOC" dataType="varchar2" value="DATE_OF_RELOC"/>
<element name="HR_HEAD" dataType="varchar2" value="HR_HEAD" />
<element name="HR_HEAD_DESIG" dataType="varchar2" value="HR_HEAD_DESIG"/>
<element name="CONCERN1" dataType="varchar2" value="CONCERN1"/>
<element name="POS1" dataType="varchar2" value="POS1"/>
<element name="CONCERN2" dataType="varchar2" value="CONCERN2"/>
<element name="POS2" dataType="varchar2" value="POS2"/>
<element name="CONCERN3" dataType="varchar2" value="CONCERN3"/>
<element name="POS3" dataType="varchar2" value="POS3"/>
<element name="CONCERN4" dataType="varchar2" value="CONCERN4"/>
<element name="POS4" dataType="varchar2" value="POS4"/>
<element name="CONCERN5" dataType="varchar2" value="CONCERN5"/>
<element name="POS5" dataType="varchar2" value="POS5"/>
</group>
</dataStructure>
</dataTemplate>
----------------- END -------------------------
Download the Format of
the Template
XXBEXRELOCLETTER.rtf::
https://drive.google.com/file/d/1J1diGHzK56SRdhUIuivsPCuEOSSkgffy/view?usp=sharing
Download the Format of
the Data Definition XML File
XXBEXRELOCLETTER.xml ::
https://drive.google.com/file/d/1K5N6KbwSl-93IK23ao6iuZR3RvNrtbiq/view?usp=sharing
Download the Format of
the Concurrent Program
XXBEXRELOCLETTER_CP.ldt::
https://drive.google.com/file/d/1KPJJgeyc09FpQ6_SuAGqmhyENl_AOnAA/view?usp=sharing
Download the Format of
the Data Definition Program
XXBEXRELOCLETTER_DD.ldt ::
https://drive.google.com/file/d/1NK0FZsgaaRy6JZ0EwUE-YrwO1tnTpLNP/view?usp=sharing
Download the Format of
the Request Group Assign Program
XXBEXRELOCLETTER_REQ.ldt::
No comments:
Post a Comment