Oracle apps EBS r12 Report for the HRMS and Payroll Report - Relocation Letter

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::

https://drive.google.com/file/d/1_CAXbn5mNyEsUMLYcrWLid_DHpSqVw7R/view?usp=sharing