Login
Register

Home

Trainings

Fusion

R12

Blogs

Contact Us

Fusion PayRoll
  • 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 Click here to raise Support Ticket. Get reply within 48 hours.

Search Courses

User Rating: 5 / 5

Star ActiveStar ActiveStar ActiveStar ActiveStar Active
 

Introduction

One of the most common and yet most desired report in any ERP Application would be system generated reports reason being for every individual hired into an organization there should always be a printed document having a standard format which would contain the most relevant details to be used by the hired individual.  Typical Examples could be “Offer Letter” (pre-joining), “Appointment Letter” (on-joining), “Welcome Letter” (post-joining) …… to name a few.

In this example, we would try to understand the following:

  1. What is a Welcome letter?

  2. What information does it comprises of?

  3. Why is it Required?

  4. How to prepare a Welcome Letter?

So without further delay, let us try to understand the same.

  1. What is a Welcome letter?

Welcome Letter as the word suggests is a letter of gratitude from the Organization to the newcomer stating his/her role, details about the person to contact in case of any need and most importantly a mode/ medium via which the Organization reaches out to the individual and tries to build a healthy relationship thereby initiating a conversation/discussion.

  1. What does a Welcome letter comprises-of?

Different organizations may have a very different template for the same but all of them (organizations) would definitely have the following areas covered in the same:

  1. Congratulating the individual on joining the organization

  2. Job Role of the individual

  3. An overview of how does an employee plays a role in the organization’s growth

  4. Details about contact person / team the individual should reach out to for any kind of help required for a smooth transition enabling the individual to become an integral part of the organization.

  1. Why is it required?

As already stated in point B above it serves two-fold purpose. First it gives the organization an opportunity to connect with the individual whereas the individual gets information about the team / individual whom he/she needs to reach out for any help / assistance required.

  1. How-to prepare a Welcome Letter?

We would have to perform the following activities in order to prepare a Welcome letter:

Step 1: Create a BIP Data Model

Step2: Create a RTF Template

Step3: Generate the Report

We would elaborate on each of the above step (with a brief summary and a screen-shot)

Step1: Create a BIP Data Model

For this example, we would need 2 queries

#

Query

1

SELECT

 initcap(ppn.title) title, :Emp, upper(ppn.first_name||'.'||ppn.last_name) PASSWORD,

 ppn.first_name||' '||ppn.last_name Person_Display_Name,

ppn.first_name,

 ppn.last_name,

 (CASE when pad.country in ('DK', 'SE',  'FR' , 'DE' ,'BE',  'FR', 'GB') then (pad.address_line_1 ||chr(10)||

pad.postal_code||' '|| pad.town_or_city||chr(10)||

ter1.territory_short_name)

WHEN pad.country = 'US' then (pad.address_line_1||chr(10)||

pad.town_or_city||', '||upper(pad.REGION_2)||pad.postal_code||chr(10)||

pad.REGION_1||chr(10)||

upper(ter1.territory_short_name))

WHEN pad.country = 'CN' then (upper(ter1.territory_short_name)||chr(10)||

pad.postal_code ||' '|| upper(pad.REGION_3)||chr(10)||

', '||pad.ADDL_ADDRESS_ATTRIBUTE2||chr(10)||

pad.address_line_1||chr(10)

)

WHEN pad.country in ('AE', 'SA', 'LY', 'SY') then (pad.address_line_1||chr(10)||

pad.ADDRESS_LINE_2||chr(10)||

pad.town_or_city||chr(10)||

pad.postal_code||chr(10)||

ter1.territory_short_name)

ELSE (pad.address_line_1 ||chr(10)||

pad.postal_code||' '|| pad.town_or_city||chr(10)||

ter1.territory_short_name)

END) Address_Emp,

 TO_CHAR(pp.date_of_birth,'DD-MON-YYYY') Person_Date_of_Birth,

 pj.name Extract_Assignment_Name,

 pg.name Assignment_Grade_Code,

 csa.salary_amount*csb.salary_annualization_factor Assignment_Salary,

 pam.normal_hours Assignment_Standard,

(select name from hr_all_organization_units where organization_id = pam.organization_id) ORG_NAME,

ppf.person_id,

pam.assignment_id,

pam.legal_entity_id,

csa.salary_id,

csb.salary_basis_id,

(select nm.DISPLAY_NAME from PER_PERSON_NAMES_F_V nm, PER_ASSIGNMENT_SUPERVISORS_F sup

where nm.PERSON_ID = sup.MANAGER_ID and nm.name_type = 'GLOBAL'

and sup.PERSON_ID = ppf.PERSON_ID and sup.ASSIGNMENT_ID = pam.ASSIGNMENT_ID

and sysdate between sup.effective_start_date and nvl(sup.effective_end_date, sysdate+1)) MGR_NAME

FROM

 per_all_people_f ppf,

 per_person_names_f ppn,

 per_all_assignments_m pam,

 PER_JOBS_F_VL pj,

 per_grades_f_vl pg,

 cmp_salary csa ,

 cmp_salary_bases_vl csb,

 per_addresses_f pad,

 per_persons pp, per_national_identifiers pni,

fnd_territories_vl ter1

WHERE

 ppf.person_id  =pam.person_id

AND(pad.COUNTRY = ter1.territory_code(+))

AND ppn.person_id=ppf.person_id

AND sysdate BETWEEN ppf.effective_start_date(+) AND ppf.effective_end_date(+)

AND sysdate BETWEEN ppn.effective_start_date(+) AND ppn.effective_end_date(+)

AND sysdate BETWEEN pam.effective_start_date(+) AND pam.effective_end_date(+)

AND sysdate BETWEEN pj.effective_start_date(+) AND pj.effective_end_date(+)

AND sysdate BETWEEN pg.effective_start_date(+) AND pg.effective_end_date(+)

AND sysdate BETWEEN pad.effective_start_date(+) AND pad.effective_end_date(+)

AND sysdate BETWEEN csa.date_from(+) AND csa.date_to(+)

AND pam.effective_latest_change ='Y'

AND pam.assignment_id           = csa.assignment_id(+)

AND csa.salary_basis_id         = csb.salary_basis_id(+)

AND pj.job_id(+)              =pam.job_id

AND pp.person_id           =ppf.person_id

AND ppf.mailing_address_id = pad.address_id(+)

and pg.grade_id(+)=pam.grade_id

and pni.person_id(+)=ppf.person_id

and ppn.name_type='GLOBAL' and ppf.person_id = (select person_id from per_person_names_f where name_type = 'GLOBAL' and upper(display_name)=upper(:Emp))

and pj.name is not null and pam.primary_flag='Y'

2

select nm.DISPLAY_NAME, RESP.PERSON_ID, resp.ASSIGNMENT_ID, resp.LEGAL_ENTITY_ID,

(select EMAIL_ADDRESS from PER_EMAIL_ADDRESSES

where person_id = nm.PERSON_ID and sysdate between DATE_FROM and nvl(DATE_TO, SYSDATE+1)) EMAIL

from PER_ASG_RESPONSIBILITIES resp, PER_PERSON_NAMES_F_V nm

where nm.PERSON_ID = resp.PERSON_ID and nm.name_type = 'GLOBAL'

AND SYSDATE BETWEEN RESP.START_DATE AND NVL(resp.END_DATE, SYSDATE+1)

and RESPONSIBILITY_TYPE = 'HR_REP' AND upper(STATUS) ='ACTIVE'

and LEGAL_ENTITY_ID is not null

Now we would also need a Link between the above two queries.

Assuming Query 1 is represented by Data Set G_1 and second via Data Set G_2 the link would be represented as:

G_1. LEGAL_ENTITY_ID = G_2. LEGAL_ENTITY_ID

When the same details are used to create a Data Model in Fusion Applications we get something which looks as below:

In the above screenshot you must have noticed that we have a Parameter defined (this is based on a List of Value) which would need to be fined too as shown below:

Query for LOV:

SELECT distinct display_name FROM per_person_names_f ppn WHERE name_type = 'GLOBAL' order by display_name asc

With this we come to end of step1

Step2: Create a RTF Template

Next we would need to create a RTF Template for this Welcome letter and it would look like

The texts having a grey background (for example ABCD ) is the one which holds dynamic values (XML Tags) and would change depending on the individual selected (while running the report via user input). All other text in the template are static.

You may create the RTF Template in offline mode and in-case you are not aware about how-to create a RTF Template please refer to below link:

Article Link from Apps2Fusion about ‘How-To create a RTF Template’

This brings me to end of step2.

Step3: Generate the Report

Now, we have already created the Data Model (Step1) and the RTF Template (Step2) and you may refer to the article “How-To Create a Custom BIP Report in Fusion Applications”

(Complete article at: http://www.apps2fusion.com/oracle-fusion-online-training/fusion-applications/fusion-payroll/1148-how-to-create-a-custom-bip-report-in-fusion-applications )

You would be able to create a Report as per instructions given in above link and the Final output would look as:

We now have reached the end of Step3.

Associated Files:

We have now reached the end of article and I would leave you all with the hope that you all would try to replicate and create the same in your POD (Fusion Instance) and verify the results.

Have a Nice Day!!!!


Ashish Harbhajanka

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

........

More articles from this author

Overall Rating (0)

0 out of 5 stars
  • No comments found

Search Trainings

Fully verifiable testimonials

Apps2Fusion - Event List

<<  Aug 2017  >>
 Mon  Tue  Wed  Thu  Fri  Sat  Sun 
   1  2  3  4  5  6
  7  8  910111213
14151617181920
21222324252627
28293031   

Enquire For Training

Fusion Training Packages

Get Email Updates


Powered by Google FeedBurner