Oracle eBusiness Suite Electronic Technical Reference Manual - eTRM

Refer to eTRM (http://etrm.oracle.com) available for all the tables above.

Query to find Responsibility for the Concurrent Program

SELECT frt.responsibility_name,
               frg.request_group_name,
               frg.description
  FROM fnd_request_groups frg
             ,fnd_request_group_units frgu
             ,fnd_concurrent_programs fcp
             ,fnd_concurrent_programs_tl fcpt
             ,fnd_responsibility_tl frt
             ,fnd_responsibility frs
 WHERE frgu.unit_application_id = fcp.application_id
 AND   frgu.request_unit_id = fcp.concurrent_program_id
 AND   frg.request_group_id = frgu.request_group_id
 AND   frg.application_id = frgu.application_id
 AND   fcpt.source_lang = USERENV('LANG')
 AND   fcp.application_id = fcpt.application_id
 AND   fcp.concurrent_program_id = fcpt.concurrent_program_id
 AND   frs.application_id = frt.application_id
 AND   frs.responsibility_id = frt.responsibility_id
 AND   frt.source_lang = USERENV('LANG')
 AND   frs.request_group_id = frg.request_group_id
 AND   frs.application_id = frg.application_id
 AND   fcp.concurrent_program_name = <Short Name>
 AND   fcpt.user_concurrent_program_name LIKE <Concurrent Program Name>

Query for supplier Details In oracle APPS

SELECT   asp.vendor_id ,
asp.segment1 "Supplier Num" ,
asp.vendor_name "Supplier Name" ,
ass.vendor_site_code "site name" ,
hou.name "Operating Unit Name" ,
ass.address_line1 ,
ass.address_line2,
ass.city ,
ass.state,
ass.zip ,
decode(ass.country,
              'IN',
              'India',
              ass.country) COUNTRY,
ass.phone ,
person.person_first_name ,
person.person_last_name ,
pty_rel.primary_phone_number,
jat.PAN_NO,
pty_rel.email_address,
hcp1.phone_country_code fax_country_code,
hcp1.phone_area_code fax_areacode, hcp1.phone_number fax
From
ap_suppliers asp   --5515
,ap_supplier_sites_all ass,
ap_supplier_contacts apsc
,hz_parties person
,hz_parties pty_rel
,hz_contact_points hcp1
,hr_operating_units hou
,JAI_AP_TDS_VENDOR_HDRS jat
Where
ass.vendor_id = asp.vendor_id
AND apsc.org_party_site_id(+) = ass.party_site_id
AND apsc.per_party_id = person.party_id(+)
AND apsc.rel_party_id = pty_rel.party_id(+)
AND ass.org_id = hou.organization_id
And ass.VENDOR_ID = jat.VENDOR_ID
AND ass.VENDOR_SITE_ID = jat.VENDOR_SITE_ID(+)
------------------------------------------
AND person.party_id = hcp1.owner_table_id(+)
AND hcp1.owner_table_name(+) = 'HZ_PARTIES'
AND hcp1.contact_point_type(+) = 'PHONE'
AND hcp1.phone_line_type(+) = 'FAX'
------------------------------------------------
AND asp.VENDOR_ID = nvl(:P_SUPPLIER_ID,asp.VENDOR_ID)
And asp.segment1  = nvl(:P_SUPLIER_NUM,asp.segment1)
And UPPER(ass.CITY)  = UPPER(nvl(:P_SULIER_SIT_NAME, ass.CITY))
And jat.PAN_NO   = nvl(:P_PAN_NUM,jat.PAN_NO)
Order by asp.segment1 desc

Oracle Workflow Tables with links

---------Get Process details------------------
SELECT distinct wa_process.NAME, wat_process.display_name, wa_process.item_type,wa_process.TYPE,wa_process.FUNCTION
,trunc(wa_process.BEGIN_DATE),trunc(wa_process.END_DATE),wa_process.VERSION
  FROM wf_activities wa_process, wf_activities_tl wat_process
WHERE wa_process.item_type IN (select distinct item_type from wf_activities where item_type in
('XXMOM_QA'))
   AND wa_process.TYPE = 'PROCESS'
--   AND wa_process.VERSION = (SELECT MAX (VERSION)
--                       FROM wf_activities
--                      WHERE item_type =:A AND TYPE = 'PROCESS')
   AND wat_process.item_type = wa_process.item_type
   AND wat_process.VERSION = wa_process.VERSION
   AND wat_process.LANGUAGE = 'US'
   AND wat_process.NAME = wa_process.NAME
   And to_char(trunc(wa_process.BEGIN_DATE),'DD_MON_YYYY') > '01-JAN-1947'
   And (trunc(wa_process.END_DATE) is null or trunc(wa_process.END_DATE) > sysdate)
UNION
---------Get Event details------------------
SELECT distinct wa_event.NAME, wat_event.display_name, wa_event.item_type,wa_event.TYPE,wa_event.FUNCTION
,trunc(wa_event.BEGIN_DATE),trunc(wa_event.END_DATE),wa_event.VERSION
  FROM wf_activities wa_event, wf_activities_tl wat_event
WHERE wa_event.item_type IN (select distinct item_type from wf_activities where item_type in
('XXMOM_QA'))
   AND wa_event.TYPE = 'EVENT'
--   AND wa_event.VERSION = (SELECT MAX (VERSION)
--                       FROM wf_activities
--                      WHERE item_type = :A AND TYPE = 'EVENT')
   AND wat_event.item_type = wa_event.item_type
   AND wat_event.VERSION = wa_event.VERSION
   AND wat_event.LANGUAGE = 'US'
   AND wat_event.NAME = wa_event.NAME
   And to_char(trunc(wa_event.BEGIN_DATE),'DD_MON_YYYY') > '01-JAN-1947'
   And (trunc(wa_event.END_DATE) is null or trunc(wa_event.END_DATE) > sysdate)
UNION
---------Get Function details------------------
SELECT distinct wa_func.NAME, wat_func.display_name, wa_func.item_type,wa_func.TYPE,wa_func.FUNCTION
,trunc(wa_func.BEGIN_DATE),trunc(wa_func.END_DATE),wa_func.VERSION
  FROM wf_activities wa_func, wf_activities_tl wat_func
WHERE wa_func.item_type IN (select distinct item_type from wf_activities where item_type in
('XXMOM_QA'))
   AND wa_func.TYPE = 'FUNCTION'
--   AND wa_func.VERSION = (SELECT MAX (VERSION)
--                       FROM wf_activities
--                      WHERE item_type = :A AND TYPE = 'FUNCTION')
   AND wat_func.item_type = wa_func.item_type
   AND wat_func.VERSION = wa_func.VERSION
   AND wat_func.LANGUAGE = 'US'
   AND wat_func.NAME = wa_func.NAME
   And to_char(trunc(wa_func.BEGIN_DATE),'DD_MON_YYYY') > '01-JAN-1947'
   And (trunc(wa_func.END_DATE) is null or trunc(wa_func.END_DATE) > sysdate)
UNION  
---------Get Notification details------------------
SELECT distinct wa_not.NAME, wat_not.display_name, wa_not.item_type,wa_not.TYPE,wa_not.FUNCTION
,trunc(wa_not.BEGIN_DATE),trunc(wa_not.END_DATE),wa_not.VERSION
  FROM wf_activities wa_not, wf_activities_tl wat_not
WHERE wa_not.item_type IN (select distinct item_type from wf_activities where item_type in
('XXMOM_QA'))
   AND wa_not.TYPE = 'NOTICE'
--   AND wa_not.VERSION = (SELECT MAX (VERSION)
--                       FROM wf_activities
--                      WHERE item_type = :A AND TYPE = 'NOTICE')
   AND wat_not.item_type = wa_not.item_type
   AND wat_not.VERSION = wa_not.VERSION
   AND wat_not.LANGUAGE = 'US'
   AND wat_not.NAME = wa_not.NAME
   And to_char(trunc(wa_not.BEGIN_DATE),'DD_MON_YYYY') > '01-JAN-1947'
   And (trunc(wa_not.END_DATE) is null or trunc(wa_not.END_DATE) > sysdate)
UNION
---------Get Message  details------------------
SELECT ws.name,wst.display_name,ws.TYPE,'Message',null,null,null,null FROM wf_messages ws,WF_messages_TL wst
Where ws.TYPE IN (select distinct item_type from wf_activities where item_type in
('XXMOM_QA'))
And ws.type=wst.TYPE
And ws.name=wst.name
And wst.language='US'
UNION
---------Get Message Attribute details------------------
Select ws.NAME,wst.DISPLAY_NAME,ws.message_type,'Message Attribute',null,null,null,null
from  WF_MESSAGE_ATTRIBUTES ws,WF_MESSAGE_ATTRIBUTES_TL wst
Where ws.message_type  IN (select distinct item_type from wf_activities where item_type in
('XXMOM_QA'))
And ws.NAME=wst.NAME
And ws.message_TYPE=wst.MESSAGE_TYPE
And wst.language='US'
Union
---------Get Lookup  details------------------
Select  wl.LOOKUP_TYPE NAME,wlt.DISPLAY_NAME,wl.item_type,'Lookup Type',null,null,null,null
from wf_lookup_types wl,
      wf_lookup_types_TL wlt
  Where wl.item_type IN (select distinct item_type from wf_activities where item_type in
('XXMOM_QA'))
And wl.LOOKUP_TYPE=wlt.LOOKUP_TYPE
And wl.ITEM_TYPE=wlt.ITEM_TYPE
And wlt.language='US'
UNION
---------Get Item Attribute details------------------
Select WIA.NAME,wit.DISPLAY_NAME,WIA.item_type,'Item Attribute',null,null,null,null
FROM
WF_ITEM_ATTRIBUTES WIA,
WF_ITEM_ATTRIBUTES_TL WIT
Where WIA.item_type =WIT.item_type
And WIA.name=WIT.name
And language='US'
And WIA.item_type IN (select distinct item_type from wf_activities where item_type in
('XXMOM_QA'))
order by 3,4;