Oracle apps EBS r12 Report for the HRMS and
Payroll Report –
Demotion 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: Demotion
Dear
Name
An inquiry committee was formed to
investigate about an allegation against you. The inquiry committee found that
you have violated discipline and company rules. Management is highly
disappointed on your misdeeds. Considering the gravity of your misdeeds the
management has decided to demote you for 1 (one) year from DESIG
to NEW_DESIG
with effect from DATE.
You are hereby severely warned for the incident.
Management will take serious action against you if you do not correct yourself
in future.
Sincerely
yours
LE
-----------------------------------------------------------------------------------------------
------------------------- XXBEXDEMOTIONLETTER.xml
--------------------------
Data
Definition File :
<?xml
version="1.0" encoding="UTF-8" ?>
<dataTemplate name="XXBEXDEMOTIONLETTER"
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_DEMOTION">
<![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,
old_assgn.designation,
old_assgn.department,
old_assgn.location_code,
old_assgn.legal_entity,
pjd.segment2 new_des,
TO_CHAR(paaf.effective_start_date,'DD-Mon-YYYY') date_of_demotion,
(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,
(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_DEM'
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
WHERE 1 =1
AND papf.person_id = paaf.person_id
AND paaf.job_id = pj.job_id(+)
AND
pj.job_definition_id =
pjd.job_definition_id(+)
AND papf.person_id =old_assgn.person_id
AND
papf.current_employee_flag='Y'
AND
paaf.change_reason ='EMP_DEM'
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_DEMOTION"
dataType="varchar2" source="Q_DEMOTION">
<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="DATE_OF_DEMOTION" dataType="varchar2"
value="DATE_OF_DEMOTION"/>
<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 XXBEXDEMOTIONLETTER.rtf::
Download the Format of
the XXBEXDEMOTIONLETTER.xml ::
https://drive.google.com/file/d/1jPmMOXR5Ra8o2tUg5uBDGOyFwn-JUKf2/view?usp=sharing
Download the Format of
the
XXBEXDEMOTIONLETTER_CP.ldt::
https://drive.google.com/file/d/16tUmyQzFmYDHVJezCvm8tTrJc6zNv__A/view?usp=sharing
Download the Format of
the
XXBEXDEMOTIONLETTER_DD.ldt ::
Download the Format of
the
XXBEXDEMOTIONLETTER_REQ.ldt::
No comments:
Post a Comment