Oracle apps EBS r12 Report for the HRMS and Payroll Report - Report Name - Training Status Report

Oracle apps EBS r12 Report for the HRMS and Payroll Report - Report Name - Training Status Report

Template File - RTF File Design :

Download the Format of the RTF File means Template File:

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

Format Like This.

Training Status

Sl.

Legal Entity

Department

Sub-Department

Employee Number

Employee Name

Designation

Grade

Working Location

Date of Joining

Job Tenure

Gender

Last Degree of Education

Training Title

Training Type

Training Location

Start Date of Training

End Date of Training

Training Institute

Total Hours of Training

Cost of Training

ROI of Training

FESl

LE

DP

DEP

EMP

NAM

DSG

GRD

LOC

DAT

EXP

SEX

QUAL

TTL

TYP

LOC

DAT

DAT

INST

HRS

COST

ROIE

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

---------------------------------- XXBEXTIMETOHIREREP.xml --------------------------

Data Definition File :

Download the Format of the Data Definition File :

https://drive.google.com/file/d/1PTjZh21kYbRqoX5gc-oNZCtfuocCJ1MH/view?usp=sharing

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

          <dataTemplate name="XXBEXTRAININGSTATUSREP" version="1.0">

                   <properties>

                             <property name="xml_tag_case" value="upper" />

                   </properties>

                  

                   <parameters>

                             <parameter name="P_IN_CHR_LEGAL_ENTITY" dataType = "VARCHAR2"></parameter>

                             <parameter name="P_IN_CHR_DEPT" dataType = "VARCHAR2"></parameter>

                             <parameter name="P_IN_NUM_PERSON_ID" dataType = "varchar2"></parameter>

                             <parameter name="P_IN_CHR_LOCATION" dataType = "varchar2"></parameter>

                             <parameter name="P_IN_CHR_TRAINING_TYPE" dataType = "varchar2"></parameter>

                   </parameters>

                   <dataQuery>

                             <sqlStatement name="Q_TRAINING">

<![CDATA[

SELECT papf.person_id,

  papf.employee_number,

  initcap(papf.title

  ||' '

  ||papf.first_name

  ||' '

  ||papf.middle_names

  ||' '

  ||papf.last_name) employee_name,

  pgd.segment1 legal_entity,

  ppd.segment3 department,

  SUBSTR(haou.NAME,instr(haou.NAME,'.',1,1)+1) sub_dept,

  SUBSTR(pp.NAME,1,instr(pp.NAME,'|')      -1) designation,

  pgd.segment2 grade,

  hl.location_code work_location,

  TO_CHAR(ppos.date_start,'DD-Mon-YYYY') joining_date,

  ROUND((TRUNC(SYSDATE)-ppos.date_start)/365,2) job_tenure,

  DECODE(papf.sex,'M','Male','Female') gender,

  (SELECT pei_information1 highest_degree

  FROM per_people_extra_info ppe

  WHERE ppe.person_id      = papf.person_id

  AND ppe.information_type ='BEX_QUAL_DETAILS'

  AND ppe.pei_information5 ='Y'

  AND rownum               =1

  ) highest_qual,

  ppei.pei_information1 training_title ,

  pei_information2 training_type,

  pei_information3 training_loc,

  TO_CHAR(to_date(pei_information4,'YYYY/MM/DD HH24:MI:SS'),'DD-Mon-YYYY') training_date_from,

  TO_CHAR(to_date(pei_information5,'YYYY/MM/DD HH24:MI:SS'),'DD-Mon-YYYY') training_date_to,

  pei_information6 training_institute,

  pei_information7 training_hours,

  pei_information9 training_cost,

  pei_information10 training_roi

FROM per_people_extra_info ppei,

  per_all_people_f papf,

  per_all_assignments_f paaf,

  per_grades pg,

  per_grade_definitions pgd,

  per_positions pp,

  per_position_definitions ppd,

  hr_all_organization_units haou,

  hr_locations hl,

  per_periods_of_service ppos

WHERE 1                       =1

AND ppei.person_id            = papf.person_id

AND papf.person_id            = paaf.person_id

AND paaf.primary_flag         ='Y'

AND paaf.grade_id             = pg.grade_id(+)

AND pg.grade_definition_id    = pgd.grade_definition_id(+)

AND paaf.position_id          = pp.position_id(+)

AND pp.position_definition_id = ppd.position_definition_id(+)

AND paaf.organization_id      = haou.organization_id(+)

AND paaf.location_id          = hl.location_id(+)

AND papf.person_id            = ppos.person_id

AND paaf.period_of_service_id = ppos.period_of_service_id

AND ppei.creation_date BETWEEN papf.effective_start_date AND papf.effective_end_date

AND ppei.creation_date BETWEEN paaf.effective_start_date AND paaf.effective_end_date

AND ppei.pei_information_category = 'BEX_EMP_TRAINING_DETAILS'

--Parameter Section

AND NVL(pgd.segment1,'N')          = NVL(:P_IN_CHR_LEGAL_ENTITY,NVL(pgd.segment1,'N'))

AND NVL(ppd.segment3,'N')          = NVL(:P_IN_CHR_DEPT,NVL(ppd.segment3,'N'))

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

AND NVL(hl.location_code,'N')      = NVL(:P_IN_CHR_LOCATION,NVL(hl.location_code,'N'))

AND NVL(ppei.pei_information2,'N') = NVL(:P_IN_CHR_TRAINING_TYPE,NVL(ppei.pei_information2,'N'))

ORDER BY papf.employee_number,

  to_date(ppei.pei_information4,'YYYY/MM/DD HH24:MI:SS')

  ]]>

                                      </sqlStatement>

                                     

                   </dataQuery>

                  

                   <dataStructure>

                             <group name="G_TRAINING" dataType="varchar2" source="Q_TRAINING">

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

                             </group>

                   </dataStructure>

          </dataTemplate>

---------------------------------- End----------------

Download the Format of the XXBEXTRAININGSTATUSREP.rtf::

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

Download the Format of the XXBEXTRAININGSTATUSREP.xml ::

https://drive.google.com/file/d/1PTjZh21kYbRqoX5gc-oNZCtfuocCJ1MH/view?usp=sharing

Download the Format of the 

XXBEXTRAININGSTATUSREP_CP.ldt::

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

Download the Format of the 

XXBEXTRAININGSTATUSREP_DD.ldt ::

https://drive.google.com/file/d/1EDJJWYtDq13-8C9fGAz18VDmmpP8kAF6/view?usp=sharing

Download the Format of the 

XXBEXTRAININGSTATUSREP_REQ.ldt::

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