Oracle apps EBS r12 Report for the HRMS and Payroll Report - Report Name - Show Cause Letter

Oracle apps EBS r12 Report for the HRMS and Payroll Report - Report Name - Show Cause Letter

Template File - RTF File Design :

Download the Format of the RTF File means Template File:

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

Format Like This.

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

EMP_NAME

EMPNO

DESIG

DEPT Department

LE

LOC

Subject: Show Cause Letter

Dear Name

According to the attendance report it is found that you have been absent from your duty since DATE without prior permission.

This is a misconduct and punishable offence.

In view of above, you are called upon to explain in writing within 10 (ten) days from the receipt of this show cause letter as to why appropriate disciplinary action will not be taken against you.

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

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

---------------------------------- XXBEXSHOWCAUSELETTER.xml --------------------------

Data Definition File :

Download the Format of the Data Definition File :

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

<?xml version="1.0" encoding="UTF-8" ?>

          <dataTemplate name="XXBEXSHOWCAUSELETTER" 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_SHOW">

<![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 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,

  TO_CHAR(ppos.actual_termination_date,'DD-Mon-YYYY') retire_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 to_date(:P_IN_EFFECTIVE_DATE,'YYYY/MM/DD HH24:MI:SS') 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 to_date(:P_IN_EFFECTIVE_DATE,'YYYY/MM/DD HH24:MI:SS') BETWEEN ppf.effective_start_date AND ppf.effective_end_date

  AND to_date(:P_IN_EFFECTIVE_DATE,'YYYY/MM/DD HH24:MI:SS') BETWEEN paf.effective_start_date AND paf.effective_end_date

  AND rownum=1

  ) hr_head_desig,

  to_char(to_date(:P_IN_EFFECTIVE_DATE,'YYYY/MM/DD HH24:MI:SS'),'DD-Mon-YYYY') effect_date,

  (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 to_date(:P_IN_EFFECTIVE_DATE,'YYYY/MM/DD HH24:MI:SS') 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 to_date(:P_IN_EFFECTIVE_DATE,'YYYY/MM/DD HH24:MI:SS') 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 to_date(:P_IN_EFFECTIVE_DATE,'YYYY/MM/DD HH24:MI:SS') 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 to_date(:P_IN_EFFECTIVE_DATE,'YYYY/MM/DD HH24:MI:SS') 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 to_date(:P_IN_EFFECTIVE_DATE,'YYYY/MM/DD HH24:MI:SS') 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.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 papf.effective_start_date AND papf.effective_end_date

AND to_date(:P_IN_EFFECTIVE_DATE,'YYYY/MM/DD HH24:MI:SS') BETWEEN paaf.effective_start_date AND paaf.effective_end_date

AND papf.person_id =NVL(:P_IN_PERSON_ID,papf.person_id)

  ]]>

                                      </sqlStatement>

                                     

                   </dataQuery>

                  

                   <dataStructure>

                             <group name="G_SHOW" dataType="varchar2" source="Q_SHOW">

                                 <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="RETIRE_DATE" dataType="varchar2" value="RETIRE_DATE"/>

                                      <element name="HR_HEAD" dataType="varchar2" value="HR_HEAD" />

                                      <element name="HR_HEAD_DESIG" dataType="varchar2" value="HR_HEAD_DESIG"/>

                                      <element name="EFFECT_DATE" dataType="varchar2" value="EFFECT_DATE"/>

                                      <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 XXBEXSHOWCAUSELETTER.rtf::

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

Download the Format of the XXBEXSHOWCAUSELETTER.xml ::

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

Download the Format of the 

XXBEXSHOWCAUSELETTER_CP.ldt::

https://drive.google.com/file/d/1XheD7WdzY7RAAFFH6Y-7nXJZkPU9JZPZ/view?usp=sharing

Download the Format of the 

XXBEXSHOWCAUSELETTER_DD.ldt ::

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

Download the Format of the 

XXBEXSHOWCAUSELETTER_REQ.ldt::

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