Login
Register

Home

Trainings

Fusion Blog

EBS Blog

Authors

CONTACT US

Fusion Blog
  • Register

Oracle Gold Partners, our very popular training packages, training schedule is listed here
Designed by Five Star Rated Oracle Press Authors & Oracle ACE's.

webinar new

Search Courses

Introduction

Starting Release 20B there is a new enhancement in the Document of Records work area. It is a very common requirement that employees might need certain documents from organization like Address Proof Letter, Employment Letter, Appointment Letter, Offer Letter, Visa Request Letter etc.

While some organization prefer creating letters manually each time a request is raised by a employee but the solution will not be sustainable if you have a large employee base. In such cases having a digital solution helps.

Something like, you enter the relevant details and a draft document should be created based on the data input provided.

Luckily the same feature is available starting Release 13 20B.

In this write-up, I will try to demonstrate how this works.

So, without further ado let’s get started.

Pre-Requisite

You would need to make a copy of delivered Data Model located at /Shared Folders/Human Capital Management/Workforce Management/Manage Document Records/Data Models/Document Report

For this example, I made a copy of Document Report data model and also made some changes in the data model. The delivered Data Model has three data sets namely:

  1. DocumentDetails

Contains details about the Document of Record document type consists of fields like Document Name, Document Number, Document Code, Issuing Country, Issue Date etc 

  1. DocumentFlexDetails

Contains specific data elements specific to document type. There could be some flexfields defined for a document type and this data set fetches values from the flexfield segments.

  1. DocumentPersonDetails

Contains person specific details like person id, person name etc. This data set is of particular interest to us in this example as we will modify the data set query and fetch other details which we would need to generate the latter.

 

We would keep the underlying data set query for DocumentDetails and DocumentFlexFields as-is and will only modify the DocumentPersonDetails data set query. The new query is mentioned in below table for ready reference:

SQL for DocumentPersonDetails

select ppnfv.person_id, 

ppnfv.display_name, 

nvl(NULL, '1234 USD Monthly') Sal,

ppos.original_date_of_hire hire_date,

pjft.name job,

hauft.name department

  from per_person_names_f_v ppnfv, 

per_all_assignments_f paaf,

per_periods_of_service ppos,

per_jobs_f_tl pjft,

hr_org_unit_classifications_f houcf,

hr_all_organization_units_f haouf, 

hr_organization_units_f_tl hauft

 where 1=1

   and ppnfv.person_id = paaf.person_id

   and paaf.person_id = ppos.person_id

   and paaf.primary_flag = 'Y'

   and trunc(sysdate) between ppnfv.effective_start_date and ppnfv.effective_end_date

   and trunc(sysdate) between paaf.effective_start_date and paaf.effective_end_date

   and paaf.job_id = pjft.job_id

   and pjft.language = 'US'

   and trunc(sysdate) between pjft.effective_start_date and pjft.effective_end_date

   and haouf.ORGANIZATION_ID = houcf.ORGANIZATION_ID 

AND haouf.ORGANIZATION_ID = hauft.ORGANIZATION_ID 

AND haouf.EFFECTIVE_START_DATE BETWEEN houcf.EFFECTIVE_START_DATE AND houcf.EFFECTIVE_END_DATE 

AND hauft.LANGUAGE = 'US'

AND hauft.EFFECTIVE_START_DATE = haouf.EFFECTIVE_START_DATE 

AND     hauft.EFFECTIVE_END_DATE = haouf.EFFECTIVE_END_DATE 

AND houcf.CLASSIFICATION_CODE = 'DEPARTMENT' 

AND     SYSDATE BETWEEN houcf.effective_start_date AND houcf.effective_end_date

AND     SYSDATE BETWEEN haouf.effective_start_date AND haouf.effective_end_date

AND     SYSDATE BETWEEN hauft.effective_start_date AND hauft.effective_end_date

AND     hauft.organization_id = paaf.organization_id

 

We will save the new Data Model with name as EmploymentLetter_dm

Also we would need to create a RTF template which is as below:

 

We will save the report at /Custom/Practice Samples/EmploymentLetter location.

 

Now, we would need to define a new document type Titled “Employment Letter”. 

Navigation: Setup and Maintenance-> Document Types -> Create 

A screenshot of a social media post

Description automatically generated

 

A screenshot of a cell phone

Description automatically generated

 

And with this setup part is complete.

Note: Please ensure to provide the exact report path in the Report Path attribute.

 

Creating a DOR and Verifying Results

We would create a new document of record for an employee and verify results.

We would choose employee number 42 for this example

 

And once you click on “View Document” you should view a pdf document downloaded which would appear as shown below:

 

Downloadable Files are as below:

EmploymentLetter.xdo.catalog

EmploymentLetterDOR_dm.xdm.catalog


Ashish Harbhajanka

Comments   

0 #1 Ankur Bansal 2021-11-18 12:24
Please guide me on how to add parameters in this report
Quote

Add comment


Security code
Refresh

About the Author

Ashish Harbhajanka

 

Oracle Fusion HCM Techno Functional Consultant with overall 10 years of Experience in software industry with 5 years in EBS HRMS and rest 5 in Fusion HCM.

My areas of intesrest in Fusion HCM include :

a) Inbound Outbound Integration using FBL/HDL or BIP/HCM Extracts.

b) Fast Formula

c) BIP Reports

d) OTBI Reports

e) RESTFUL API / Web Service Call

f) Functional Setup

g) End to End Testing

h) Regression Testing

i) Preparing COnfiguration Workbooks

j) Creating Speed Solutions

k) Preparing User Guides

l) UPK

........

Search Trainings

Fully verifiable testimonials

Apps2Fusion - Event List

<<  Mar 2024  >>
 Mon  Tue  Wed  Thu  Fri  Sat  Sun 
      1  2  3
  4  5  6  7  8  910
11121314151617
18192021222324
25262728293031

Enquire For Training

Fusion Training Packages

Get Email Updates


Powered by Google FeedBurner