Anybody have any experience of OpenText, Livelink and/or Oracle HR?

[BarryS]

I need to know how easy it is to use OpenText/Livelink cms to get data out of Oracle HR dynamically, and if access levels will be respected when doing so.

tia

Barry

[cjolley]

I didn't see anything on their web site pre-defined for Oracle except PeopleSoft on Oracle.
You mean Oracle HR? Or People Soft HR?
Oracle Financials databases are amazingly obtuse.
I have had some experience querying them though.
What are you looking to do?

[BarryS]

Hi Chuck, thanks for the response,

I was asked to look at replacing an intranet 'Company Contacts' directory, and thought that I would expand the proposition in such a way that the whole company hierarchy could be exposed, using a treeview, with the options of showing not just the people in the company, but also stuff like headcount and where the dept/cost centre fits into the company structure and also where it's physically located.

I've been told that the company uses Oracle HR, so I need to investigate whether this functionality is already built-in and how easy it is to get out to a browser and style it so that it fits in with the corporate style. Doing it this way will probably mean that access levels are respected fairly easily.

I understand that the company uses Opentext for its cms/intranet and I know that they have Livelink, but like you, I couldn't see anything for the HR side, and the website doesn't give any clues as to how to write pages to access databases.

If the above two options aren't available, I'll be looking at asking the Oracle DBAs to write me some views that I can query in order to retrieve that data I want. (Ha!)

Barry

[cjolley]

Do you know what version of Financials they are on?

PS. I also think that info is not in HR.
I'll poke around when I get to work on Monday and let you know at least which modules it's in.

[BarryS]

That's great, thanks. At the moment I've no idea what version of financials they're using, I'll ask my colleague to find out.

[cjolley]

Hmmm....
This is going to be complicated
Keep in mind that I know Goverment Financials, regular are a little different
so your milage may vary.

Your employee data will be in the views APPS.HR_EMPLOYEE*

The organization stuff will be in views APPS.GL_*
(GL_CODE_COMBINATIONS contains the account numbers)

Or you can look in the tables themselves in HR and GL.

The permissions thing though is more complex.
It involves setting a context using DBMS_SESSION.set_context
(you must have the CREATE ANY CONTEXT system privilege)

This would be set to the organization, cost center, what ever level you want to include/exclude.

So for example you obtain the user id of the person viewing the report and use a query on their position in the organization to get the range of values you want them limited to.

Here is an example from Oracle's docs:

[b][font=Arial, Helvetica, sans-serif]Creating an Application Context: Example[/font][/b]

     This example uses the PL/SQL package emp_mgmt, created in [url="http://oraclesvca2.oracle.com/docs/cd/B10501_01/server.920/a96540/statements_67a.htm#2065355"]"Creating a Package: Example"[/url], which validates and secures the hr application. The following statement creates the context namespace hr_context and associates it with the package emp_mgmt:

  CREATE CONTEXT hr_context USING emp_mgmt;
You can control data access based on this context using the SYS_CONTEXT function. For example, suppose your emp_mgmt package has defined an attribute new_empno as a particular employee identifier. You can secure the base table employees by creating a view that restricts access based on the value of new_empno, as follows:

  CREATE VIEW hr_org_secure_view AS
   SELECT * FROM employees
   WHERE employee_id = SYS_CONTEXT('hr_context', 'new_empno');

Or, you can try to find functions or procedures that do this for you in the database.
But APPS is VERY complicated and most of these things in the database are specific to the way Oracle has written the program, so are difficult to use in home grown code.

To give you an example of what you are looking at, here is a query I wrote a few years ago against APPS.
It gets balances on our accounts.
Our data is all public by law, so there is no filtering by viewer in the query, but you can see where it would be in the fund, cost center, account, organization, etc column ranges in the where clause.

 select glcc.segment1 segment1, 
	   glcc.segment2 segment2, 
	   substr(glcc.segment3, 1, 2) budget_group, 
	   glcc.segment3 segment3, 
	   '*' post_status, 
	   sysdate report_date, 
	   glcc.code_combination_id ccid, 
	   sum(decode(pac.actual_flag, 'B', nvl(pac.accounted_dr, 0) - nvl(pac.accounted_cr, 0), 0)) budget_amt, 
	   sum(decode(pac.actual_flag, 'A', nvl(pac.accounted_dr, 0) - nvl(pac.accounted_cr, 0), 0)) actual_amt, 
	   sum(decode(pac.actual_flag, 'A', nvl(pac.accounted_dr, 0) - nvl(pac.accounted_cr, 0), 0)) actual_ptd, 
	   sum(decode(pac.actual_flag, 'E', nvl(pac.accounted_dr, 0) - nvl(pac.accounted_cr, 0), 0)) encumbrance_amt, 
	   sum(decode(pac.actual_flag, 'E', decode(pac.encumbrance_type_id, '1000', (nvl(pac.accounted_dr, 0) - nvl(pac.accounted_cr, 0)), 0), 0)) encumbrance_commited, 
	   sum(decode(pac.actual_flag, 'E', decode(pac.encumbrance_type_id, '1001', (nvl(pac.accounted_dr, 0) - nvl(pac.accounted_cr, 0)), 0), 0)) encumbrance_obligated 
  from gl_code_combinations glcc, 
	   gl_bc_packet_arrival_order arr, 
	   gl_bc_packets pac,
	   (select v.budget_version_id bvid, trim(to_char(p.period_year)) bud_year
		  from gl_budget_versions v,
			   gl_budgets b,
			   gl_periods p
		 where b.budget_name = :ls_budget_name
		   and v.budget_name = b.budget_name
		   and p.period_name = b.first_valid_period_name) bud
 where nvl(pac.budget_version_id, bud.bvid) = bud.bvid
   and arr.affect_funds_flag = 'Y' 
   and arr.packet_id = pac.packet_id 
   and pac.set_of_books_id = arr.set_of_books_id 
   and pac.code_combination_id = glcc.code_combination_id 
   and glcc.summary_flag = 'N' 
   and glcc.segment1 between :fund_low and :fund_high
   and glcc.segment2 between :center_low and :center_high
   and glcc.segment3 between '50000' and '59999' 
   and glcc.segment4 = bud.bud_year
 group by glcc.code_combination_id, 
		  glcc.segment1, 
		  glcc.segment2, 
		  substr(glcc.segment3, 1, 2), 
		  glcc.segment3

	 union all
 select /*+ ordered */glcc.segment1 segment1, 
	   glcc.segment2 segment2, 
	   substr(glcc.segment3, 1, 2) budget_group, 
	   glcc.segment3 segment3, 
	   '' post_status, 
	   sysdate report_date, 
	   glcc.code_combination_id ccid, 
	   sum(decode(glb.actual_flag, 'B', nvl(glb.project_to_date_dr, 0) - nvl(glb.project_to_date_cr, 0) + nvl(glb.period_net_dr, 0) - nvl(glb.period_net_cr, 0), 0)) budget_amt, 
	   sum(decode(glb.actual_flag, 'A', nvl(glb.project_to_date_dr, 0) - nvl(glb.project_to_date_cr, 0) + nvl(glb.period_net_dr, 0) - nvl(glb.period_net_cr, 0), 0)) actual_amt, 
	   sum(decode(glb.actual_flag, 'A', nvl(glb.period_net_dr, 0) - nvl(glb.period_net_cr, 0), 0)) actual_ptd, 
	   sum(decode(glb.actual_flag, 'E', nvl(glb.project_to_date_dr, 0) - nvl(glb.project_to_date_cr, 0) + nvl(glb.period_net_dr, 0) - nvl(glb.period_net_cr, 0), 0)) encumbrance_amt, 
	   sum(decode(glb.actual_flag, 'E', decode(glb.encumbrance_type_id, '1000', nvl(glb.project_to_date_dr, 0) - nvl(glb.project_to_date_cr, 0) + nvl(glb.period_net_dr, 0) - nvl(glb.period_net_cr, 0)), 0)) encumbrance_commited, 
	   sum(decode(glb.actual_flag, 'E', decode(glb.encumbrance_type_id, '1001', nvl(glb.project_to_date_dr, 0) - nvl(glb.project_to_date_cr, 0) + nvl(glb.period_net_dr, 0) - nvl(glb.period_net_cr, 0)), 0)) encumbrance_obligated 
  from (select v.budget_version_id bvid, trim(to_char(p.period_year)) bud_year
		  from gl_budget_versions v,
			   gl_budgets b,
			   gl_periods p
		 where b.budget_name = :ls_budget_name
		   and v.budget_name = b.budget_name
		   and p.period_name = b.first_valid_period_name) bud,
		gl_code_combinations glcc,		   
		gl_balances glb
 where decode(glb.actual_flag, 'B', glb.budget_version_id, bud.bvid)  = bud.bvid
   and glb.period_name = :period_name 
   and glb.code_combination_id = glcc.code_combination_id 
   and glcc.summary_flag = 'N' 
   and glcc.segment1 between :fund_low and :fund_high
   and glcc.segment2 between :center_low and :center_high
   and glcc.segment3 between '50000' and '59999'
   and glcc.segment4 = bud.bud_year
 group by glcc.code_combination_id, 
		  glcc.segment1, 
		  glcc.segment2, 
		  substr(glcc.segment3, 1, 2), 
		  glcc.segment3 
 order by 1 asc, 2 asc, 3 asc, 4 asc, 5

[cjolley]

also,
Some stuff in APPLSYS
For example, to get the description of a a fund in our account structure you would join segment1 in GL.GL_CODE_COMBINATIONS to the flex_value column of this view.

[font=Courier New] select ffvtl.description,
		 ffv.flex_value	 
  from applsys.fnd_flex_values ffv,
		  applsys.fnd_flex_values_tl ffvtl
 where ffv.flex_value_id = ffvtl.flex_value_id
	and ffv.flex_value_set_id = (select ffvs.flex_value_set_id
								     from applsys.fnd_flex_value_sets ffvs	 
								  where ffvs.flex_value_set_name = 'OKC_FUND'  )   [/font]

For cost center it would be segment2 from glcc and ffvs.flex_value_set_name = 'OKC_COST_CENTER'


and so on (we use 4 segments fund, cost_center, account, budget year)

If you have multi org (you most likely do) then you have to account for that too.

This is bringing the memories flooding back
If any of this helps, I can probably think of more.

[BarryS]

Wow! Thanks Chuck, that's really gives me an insight as to the sort of work that's required. Looks like it might be easier to ask the DBAs to produce extracts each night so that I can stuff it all in to a database of my own.

[cjolley]

Well, if they can produce the quiries to create your extracts then just get the queries and use them in an asp or php based page.
Maybe they could create views for you in a custom schema to hide some of the complexity.

When I first started working on reports in APPS there was really no one to call to ask where in all those thousands of tables and views to look for stuff.
It was fun. But time consuming.

11i now has even more objects. Way more objects

At least the tech docs are online now: http://www.oracle.com/technology/doc...lications.html

We spent thousands of dollars on them and never seemed to have quite what we needed.