Oracle apps EBS r12 Report for the HRMS and Payroll Report - Reinstatement of Position Letter

Oracle apps EBS r12 Report for the HRMS and Payroll Report - Reinstatement of Position Letter

Template File - RTF File Design :

Format Like This.

Ref: XX/HRD/HO/DIS/XXX/XX DATE


EMP_NAME
EMPNO
DESIG
DEPT Department
LE
LOC


Subject: Reinstatement of Position Letter
Ref: Demotion Letter dated DATE


Dear Name

This is to inform you that the management has decided to reinstate your position as DESIG with effect from DATE.

Management expects that you will continue with utmost sincerity in your present position.





Sincerely yours
LE




(HR_HEAD)
HOD



Copy: IFC1EF

IF
-POS1EF

IF
C2EF

IF
-POS2EF

IF
C3EF

IF
-POS3EF

IF
C4EF

IF
-POS4EF

IF
C5EF

IF
-POS5EF

File




-----------------------------------------------------------------------------------------------

---------------- XXBEXREINSTATEPOSLETTER.xml --------------------------

Data Definition File :

<?xml version="1.0" encoding="UTF-8" ?>
<dataTemplate name="XXBEXREINSTATEPOSLETTER" version="1.0">
<properties>
<property name="xml_tag_case" value="upper" />
</properties>

<parameters>
<parameter name="P_IN_PERSON_ID" dataType = "VARCHAR2"></parameter>
<parameter name="P_IN_EFFECTIVE_DATE" dataType = "VARCHAR2"></parameter>
<parameter name="P_IN_DEMOTION_DATE" 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_POSITION">
<![CDATA[
SELECT TO_CHAR(SYSDATE,'DD-Mon-YYYY') server_date,
  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,
  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 designation,
  ppd.segment3 department,
  hl.location_code,
  TO_CHAR(to_date(:P_IN_EFFECTIVE_DATE,'YYYY/MM/DD HH24:MI:SS'),'DD-Mon-YYYY') effect_date,
  TO_CHAR(to_date(:P_IN_DEMOTION_DATE,'YYYY/MM/DD HH24:MI:SS'),'DD-Mon-YYYY') demotion_date,
  to_char(paaf.effective_start_date,'DD-Mon-YYYY') pos_reinstatement_date,
  (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
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,
  per_periods_of_service ppos
WHERE 1                       =1
AND papf.person_id            = paaf.person_id
AND paaf.change_reason        = 'EMP_REIN_POS'
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 papf.person_id            = ppos.person_id
AND paaf.period_of_service_id =ppos.period_of_service_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
AND papf.person_id =NVL(:P_IN_PERSON_ID,papf.person_id)
  ]]>
</sqlStatement>

</dataQuery>

<dataStructure>
<group name="G_POSITION" dataType="varchar2" source="Q_POSITION">
    <element name="SERVER_DATE" dataType="varchar2" value="SERVER_DATE"/>
<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="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="EFFECT_DATE" dataType="varchar2" value="EFFECT_DATE"/>
<element name="DEMOTION_DATE" dataType="varchar2" value="DEMOTION_DATE"/>
<element name="POS_REINSTATEMENT_DATE" dataType="varchar2" value="POS_REINSTATEMENT_DATE"/>
<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 XXBEXREINSTATEPOSLETTER.rtf::

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

Download the Format of the XXBEXREINSTATEPOSLETTER.xml ::

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

Download the Format of the 

XXBEXREINSTATEPOSLETTER_CP.ldt::

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

Download the Format of the 

XXBEXREINSTATEPOSLETTER_DD.ldt ::

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

Download the Format of the 

XXBEXREINSTATEPOSLETTER_REQ.ldt::

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