CODEWITHSUNDEEP
sqloracle11goracle-ebs
Caffeinated
Caffeinated

Reputation: 12484

How to locate Oracle EBS fields in the database?

I have a Oracle EBS form like so:

enter image description here

And I want to locate what are the names of the columns in the DB that pertain to these fields.

I have this so far, for a "Requst ID" column:

select * from HHS_UMX_REG_REQUESTS WHERE reg_request_id = 261499

Does anyone have any general tips/tricks to do this kind of thing?

Upvotes: 1

Views: 2091

Answers (1)

mmmmmpie
mmmmmpie

Reputation: 3039

I don't have access to an 11 system but I think the same on R12:
When you create a worklist access grant it will add a record to the FND_GRANTS table.
The Name field is querying both FND_USER and PER_ALL_PEOPLE_F because you have to have a valid employee assignment to be eligible for workflow.
Start Date would come out of the FND_GRANTS table START_DATE column.
If you don't specify an END_DATE it will place a null in that column.
You can also delete the worklist access privilege from the grantee which doesn't place a date in the END_DATE column it actually deletes the record from FND_GRANTS.

Now for protips on how to find what tables your OA Framework pages are hitting:
Enable FND Diagnostics for your user (and your user alone)
In the bottom left part of the screen you'll see a link called About this page, click that.
When you are in the about screen in the middle you'll see something similar to Business Component References Details
In here you'll see objects like oracle.apps.fnd.wf.worklist.server.GrantersListVO for example
Click on that and you'll actually get the version and SQL its using to query the data.
Example: 

SELECT GRANT_GUID,
  GRANTEE_KEY,
  START_DATE,
  END_DATE,
  PARAMETER1 AS granter_key,
  PARAMETER2,
  PARAMETER3,
  PARAMETER4,
  PARAMETER5,
  PARAMETER6,
  PARAMETER7,
  PARAMETER8,
  PARAMETER9,
  PARAMETER10,
  wf_directory.GetRoleDisplayName(PARAMETER1) AS granter_display_name
FROM FND_GRANTS
WHERE MENU_ID                = :1
AND OBJECT_ID                = :2
AND INSTANCE_SET_ID          = :3
AND GRANTEE_KEY              = :4
AND INSTANCE_TYPE            = 'SET'
AND START_DATE              <= sysdate
AND NVL(END_DATE, sysdate ) >= sysdate
UNION
SELECT SYS_GUID(),
  NULL,
  TO_DATE(NULL),
  TO_DATE(NULL),
  FND_GLOBAL.USER_NAME,
  NULL,
  NULL,
  NULL,
  NULL,
  NULL,
  NULL,
  NULL,
  NULL,
  NULL,
  wf_directory.GetRoleDisplayName(FND_GLOBAL.USER_NAME)
FROM dual
;

Upvotes: 2

Related Questions