5th July 2021

Introduction:

The HR Operations department constantly receives requests from employees to furnish letters or certificates on behalf of the organization. Employees may need to submit these official letters such as Employment Certificates, Experience Letters, Embassy Letters, and more to the government or private entities.

While other organizations may prefer creating these letters/certificates manually each time a request is raised by an employee, this approach may not be sustainable for a company with a large employee base. For such instances, a digital solution is a great help.

This blog illustrates how to configure a document type that supports generating letters for various business purposes on Oracle ERP.  The generated letter is available as an attachment and is automatically stored as a PDF file under the same documents of record (DoR).

Case Study

Let’s take an example to comprehend the functionality of Generate Letter based on the Documents of Record (DoR) to make your understanding much clearer.

Suppose that an employee of an organization is applying for a loan, he/she will be required to provide a Salary Certificate. This feature comes in handy to facilitate the generation of such letters for the employees.

Solution Overview

You need to create the document type and additionally specify a BI publisher report path to generate the letter. You need to create a document descriptive flex field context that includes the fields required in the letter. You then associate this context with the document type you created.

Note: This feature is available from Release 20B (Release 20.04)

Solution Steps

ِِA. Report Configuration

Step 1: Data Model

  • Navigate to Tools > Reports and Analytics > Click Catalog > Folders > Shared Folders > Human Capital Management > Workforce Management > Manage Document Records > Data Models
  • Click on the Data Model DocumentReport.dx and click Copy.
  • Navigate to your custom folder and click on Paste and Modify the data model name.
  • Click on the Edit hyperlink for the copied data model and make the necessary changes. Save the data model.

 

 

 

Example:

select emp.*,SUBSTR(

NVL(:DESTINATION_NAME,NVL(:DESTINATION_OTHER,’To Whom May Concern/من يهمه الأمر’)),

1,

INSTR(NVL(:DESTINATION_NAME,NVL(:DESTINATION_OTHER,’To Whom May Concern/من يهمه الأمر’)),’/’) – 1

)

DESTINATION_EN

,SUBSTR(

NVL(:DESTINATION_NAME,NVL(:DESTINATION_OTHER,’To Whom May Concern/من يهمه الأمر’)),

INSTR(NVL(:DESTINATION_NAME,NVL(:DESTINATION_OTHER,’To Whom May Concern/من يهمه الأمر’)),’/’) + 1

)

DESTINATION_AR

from

(SELECT DISTINCT papf.person_number, paaf.person_id, haoul.NAME org_name,  haoul.organization_id,

NVL (hap.NAME, ‘Not Defined’) POSITION,

NVL (hapae.NAME, ‘Not Defined’) position_ae,

(SELECT ppn1.first_name || ‘ ‘

|| ppn1.last_name

FROM per_person_names_f ppn1

WHERE 1 = 1

AND ppn1.person_id = papf.person_id

AND ppn1.person_id = paaf.person_id

AND TRUNC (SYSDATE) BETWEEN ppn1.effective_start_date

AND ppn1.effective_end_date

AND ppn1.name_type = ‘GLOBAL’) employee_name,

(SELECT    ppn1.first_name

|| ‘ ‘

|| ppn1.last_name

FROM per_person_names_f ppn1

WHERE 1 = 1

AND ppn1.person_id = papf.person_id

AND ppn1.person_id = paaf.person_id

AND TRUNC (SYSDATE) BETWEEN ppn1.effective_start_date

AND ppn1.effective_end_date

AND ppn1.name_type = ‘AE’) employee_name_ae,

(SELECT ppn1.full_name

FROM per_person_names_f ppn1

WHERE 1 = 1

AND ppn1.person_id = papf.person_id

AND ppn1.person_id = paaf.person_id

AND TRUNC (SYSDATE) BETWEEN ppn1.effective_start_date

AND ppn1.effective_end_date

AND ppn1.name_type = ‘GLOBAL’) full_name,

(SELECT ppna.full_name

FROM per_person_names_f ppna

WHERE 1 = 1

AND ppna.person_id = papf.person_id

AND ppna.person_id = paaf.person_id

AND TRUNC (SYSDATE) BETWEEN ppna.effective_start_date

AND ppna.effective_end_date

AND ppna.name_type = ‘AE’) full_name_ae,

(SELECT TO_CHAR (SYSDATE, ‘DD/MM/YYYY’, ‘NLS_DATE_LANGUAGE = AMERICAN’ )  FROM DUAL) sysdat,

(select flv.meaning from PER_CITIZENSHIPS PC,fnd_lookup_values flv

where flv.lookup_code=pc.LEGISLATION_CODE

and flv.lookup_type=’NATIONALITY’

and PC.person_id=papf.person_id

and flv.language=’US’

and pc.CITIZENSHIP_STATUS=’A’

and flv.enabled_flag=’Y’

) nat ,

(select flv.meaning from PER_CITIZENSHIPS PC,fnd_lookup_values flv

where flv.lookup_code=pc.LEGISLATION_CODE

and flv.lookup_type=’NATIONALITY’

and PC.person_id=papf.person_id

and flv.language=’AR’

and pc.CITIZENSHIP_STATUS=’A’

and flv.enabled_flag=’Y’

) nat_ae,

‘Query to get sign person ‘ sign_name,

(select to_char(max(ppos.date_start),’DD/MM/YYYY’)

from per_periods_of_service ppos , per_all_assignments_m paam

where ppos.person_id=papf.person_id

and ppos.period_of_service_id=paam.period_of_service_id

AND SYSDATE BETWEEN paam.effective_start_date   AND paam.effective_end_date) Hire,

nvl((select round(cs.salary_amount,2) from CMP_SALARY CS

where cs.person_id=papf.person_id

and trunc(sysdate) between cs.date_from and cs.date_to),0) amt,

papo.PASSPORT_NUMBER pass

FROM per_all_people_f papf,

per_all_assignments_m paaf,

hr_all_organization_units haoul,

(SELECT hapf.NAME, paam.person_id,paam.legal_entity_id

FROM hr_all_positions_f_tl hapf,

per_all_assignments_m paam,

per_all_people_f papf

WHERE 1 = 1

AND hapf.position_id = paam.position_id

AND papf.person_id = paam.person_id

AND hapf.source_lang = ‘US’

AND paam.ASSIGNMENT_STATUS_TYPE in (‘ACTIVE’)

AND SYSDATE BETWEEN hapf.effective_start_date

AND hapf.effective_end_date

AND SYSDATE BETWEEN paam.effective_start_date

AND paam.effective_end_date

AND SYSDATE BETWEEN papf.effective_start_date

AND papf.effective_end_date

AND hapf.LANGUAGE = ‘US’) hap,

(SELECT hapf.NAME, paam.person_id,paam.legal_entity_id

FROM hr_all_positions_f_tl hapf,

per_all_assignments_m paam,

per_all_people_f papf

WHERE 1 = 1

AND hapf.position_id = paam.position_id

AND papf.person_id = paam.person_id

AND hapf.source_lang = ‘AR’

AND paam.ASSIGNMENT_STATUS_TYPE in (‘ACTIVE’)

AND SYSDATE BETWEEN hapf.effective_start_date

AND hapf.effective_end_date

AND SYSDATE BETWEEN paam.effective_start_date

AND paam.effective_end_date

AND SYSDATE BETWEEN papf.effective_start_date

AND papf.effective_end_date

AND hapf.LANGUAGE = ‘AR’) hapae,

(SELECT max(issue_Date),PASSPORT_NUMBER,papf.person_id FROM PER_PASSPORTS PP,per_all_people_f papf

WHERE PAPF.PERSON_ID=PP.PERSON_ID

AND TRUNC(SYSDATE) BETWEEN papf.effective_start_date  AND papf.effective_end_date

group by passport_number,papf.person_id

)PApo

WHERE 1 = 1

AND paaf.legal_entity_id = haoul.organization_id

—          AND hap.legal_entity_id = haoul.organization_id

—          AND hapae.legal_entity_id = haoul.organization_id

AND TRUNC (SYSDATE) BETWEEN paaf.effective_start_date

AND paaf.effective_end_date

AND TRUNC (SYSDATE) BETWEEN papf.effective_start_date

AND papf.effective_end_date

AND papf.person_id = paaf.person_id

AND paaf.person_id = hap.person_id(+)

AND paaf.person_id = hapae.person_id(+)

AND papf.person_id = papo.person_id(+)

and paaF.PRIMARY_FLAG=’Y’

and paaF.EFFECTIVE_LATEST_CHANGE=’Y’

and exists (select 1

from PER_LEGAL_EMPLOYERS pe

where  status=’A’

and organization_id=paaf.legal_entity_id)

AND papf.person_id = :PERSONID

)emp

order by lpad(PERSON_NUMBER,10,’0′)

 

Step 2: Create RTF Template
  • Extracting sample XML file that will be used to create RTF
    1. To get the Transaction ID, submit a transaction after configuring all the required flex field attributes Navigate >> Transaction Console >>Search for the Manage Document Records process with the status In Progress.

 

2. Click on transaction name and copy Transaction ID which will be the parameter for the data model and report.

 

3. Click on Edit for the data model which is copied to your custom folder.

4. Click on View Data, enter the Transaction ID noted above.

5. Click on Export to save the output sample XML.

 

 

2.  Create latter RTF template

Use BI Publisher word tool to build RTF template and load sample XML

 

 

Step 3: Create Report
  1. Navigate to Tools > Reports and Analytics > New > Report.
  2. Click on Cancel on the Create Report pop-up and click OK.
  3. Click on the Search icon next to Data Model and select the data model edited in the first step.

 

 

4.  In the Upload or Generate Layout section, click Upload. Enter the layout name, select the template file, Select Type “RTF Template”, Select Locale “English”. Click Upload.

 

 

5. Save the report.

6. Click View Report to review if the layout is correct.

7. Make a note of the report path, as that needs to be configured as part of the document type configuration.

 

Step 4: Report Security

After the report is configured, click on More > Permissions. Add or modify the required access so make sure that end users can access the report.

 

 

B.  Document Type Configuration

Step 1: Create Document type and Descriptive Flex Field

Please refer to the below blog URL to create DOR

https://www.tahaluf.ae/blog/document-of-record-dor-in-oracle-erp-cloud/

Step 2: Attach Report Path Document type

  1. In the Setup and Maintenance work area, go to the Document Types task.
  2. On the Document Types page, click search, click on Document Type Name.
  3. In the Report Path, specify the Custom report path.

 

 

4. Click Submit.

C.   End User Interface

Login as a worker who needs to request a letter to apply for a visa for business travel. Navigate to Me > Document Records > Add. Select the Salary Certificate document type.

 

  1. The worker enters the document record details and clicks View Document. Doing so will get the associated BIP template, merge the data entered by the worker and display the draft letter.

 

3. After the worker reviews the draft letter, they submit the document record for approval.

 

 

4. When the document record is approved, the final letter is generated. This final version in PDF format is stored in the worker’s document records for this document type.

 

 

 

 

Author:  Ahmad Wahban, Oracle senior HR consultant