Query for to get old request ID's with Responsibility wise for a specific CP in oracle apps

-- Concurrent Request Status
SELECT a.request_id,
  a.user_concurrent_program_name,
  a.requestor,
  d.description,
  SUBSTR(a.completion_text,1,20) status,
  a.request_date,
  a.actual_start_date,
  a.actual_completion_date ,
  DECODE(a.STATUS_CODE, 'A', 'Waiting','B', 'Resuming','C', 'Normal','D', 'Cancelled','E', 'Error','F', 'Scheduled','G', 'Warning','H', 'On Hold','I', 'Normal','M', 'No Manager', 'Q', 'Standby','R', 'Normal','S', 'Suspended','T', 'Terminating','U', 'Disabled','W', 'Paused','X', 'Terminated','Z', 'Waiting') cp_status,
  DECODE(a.PHASE_CODE, 'C', 'Completed','I', 'Inactive', 'P', 'Pending','R', 'Running') PHASE,
  mod( TRUNC( ( a.actual_completion_date - a.actual_start_date ) * 24 ), 24) HOURS ,
  mod( TRUNC( ( a.actual_completion_date - a.actual_start_date ) * 1440 ), 60 ) MINUTES,
  mod( TRUNC( ( a.actual_completion_date - a.actual_start_date ) * 86400 ), 60 ) SECONDS,
  ( a.actual_completion_date             - a.actual_start_date ) * 24 * 60 * 60 total_seconds,
  a.argument_text,
  a.hold_flag,
  a.program_short_name,
--  b.logfile_name,
--  b.logfile_node_name,
--  b.outfile_name,
--  b.outfile_node_name ,
  a.responsibility_id,
  c.responsibility_name,
  a.responsibility_application_id,
--  c.description,
  fav.application_short_name,
  fav.application_name,
  fnd_profile.VALUE_SPECIFIC(NAME => 'GL_SET_OF_BKS_ID', USER_ID => NULL, RESPONSIBILITY_ID => a.responsibility_id, APPLICATION_ID => a.responsibility_application_id, ORG_ID => NULL, SERVER_ID => NULL) GL_Set_of_Books_id,
  fnd_profile.VALUE_SPECIFIC(NAME => 'GL_SET_OF_BKS_NAME', USER_ID => NULL, RESPONSIBILITY_ID => a.responsibility_id, APPLICATION_ID => a.responsibility_application_id, ORG_ID => NULL, SERVER_ID => NULL) GL_Set_of_Books_Name,
  fnd_profile.VALUE_SPECIFIC(NAME => 'ORG_ID', USER_ID => NULL, RESPONSIBILITY_ID => a.responsibility_id, APPLICATION_ID => a.responsibility_application_id, ORG_ID => NULL, SERVER_ID => NULL) org_id_profile,
  (Select name from hr_operating_units where organization_id = (fnd_profile.VALUE_SPECIFIC(NAME => 'ORG_ID', USER_ID => NULL, RESPONSIBILITY_ID => a.responsibility_id, APPLICATION_ID => a.responsibility_application_id, ORG_ID => NULL, SERVER_ID => NULL))) operating_unit_name
--  a.phase_code,
--  a.status_code
FROM apps.fnd_conc_req_summary_v a,
  apps.fnd_concurrent_requests b,
  apps.fnd_responsibility_vl c,
  fnd_user d,
  fnd_application_vl fav
WHERE 1                 = 1
AND a.responsibility_id = c.responsibility_id
AND a.request_id        = b.request_id
AND b.requested_by      = d.user_id
AND fav.application_id  = a.responsibility_application_id
AND a.user_concurrent_program_name = <Concurrent Program Name>
 --'Workflow%Back%'-- --'Workflow Directory Services User/Role Validation'-- --'CII IPCM Insert Transactions' -- --'CGT Test Certificate Report' -- --'CGT 2111 Staging Updation'--'CGT Test Certificate Report'
--and (a.user_concurrent_program_name <> 'Workflow Background Process' or a.user_concurrent_program_name <> 'OAM Applications Dashboard Collection' )
   ---AND A.argument_text like '%RM0281-12232011%'
  --and trunc(a.actual_start_date) = '08-Feb-2013'
  --and to_char(a.actual_start_date,'dd-mon-rrrr hh24:mi:ss')  between '08-feb-2013 19:00:00' and '08-feb-2013 23:00:00'
--  AND A.request_id = 81567639--BETWEEN 4740415 AND 4740424
  --AND A.request_id in ('')-- Concurrent Request Status
  --AND a.argument_text LIKE '%489742%'
  --and responsibility_name = 'XPOS Custom Applications'
  --and a.requestor = 'KC807'
  --and a.STATUS_CODE = 'R'
--ORDER BY a.request_id DESC;
;

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;

Xml Publisher ( How to Delete Data Definition & Template from Back end )

Script for deleting the datadefinition
------------------------------------------------>
BEGIN
XDO_DS_DEFINITIONS_PKG.DELETE_ROW('INV','<DataDefinationCode>');  
END;
--------------------------------------------------------
--------------------------------------------------------
script for deleting the template 
------------------------------------------------------>
BEGIN
XDO_TEMPLATES_PKG.DELETE_ROW('INV','<DataDefinationCode>');
END

UNION Tips

SELECT BATCH_NUMBER,FULL_NAME,LEAVE_TYPE
,DECODE(LEAVE_TYPE,'Casual',12,'Special',15,'Medical',10,'Unpaid Leave',SUM(NO_OF_DAYS)) TOTAL_DAYS
,SUM(NO_OF_DAYS) LEAVE_DAYS,
(DECODE(LEAVE_TYPE,'Casual',12,'Special',15,'Medical',10))-(SUM(NO_OF_DAYS)) available
FROM XXMPCD_AT_LEAVE_TYPE_DTLS WHERE FULL_NAME='RajaShekar'
GROUP BY BATCH_NUMBER,FULL_NAME,LEAVE_TYPE
UNION
SELECT NULL,NULL,
LOOKUP_CODE,(SELECT DECODE(LOOKUP_CODE,'Casual',12,'Special',15,'Medical',10) FROM
DUAL ) TOTAL_DAYS, 0 LEAVE_DAYS,(SELECT DECODE(LOOKUP_CODE,'Casual',12,'Special',15,'Medical',10) FROM
DUAL ) available FROM FND_LOOKUP_VALUES WHERE LOOKUP_TYPE='XXMPCD_LEAVE_TYPE' AND ENABLED_FLAG='Y' AND END_DATE_ACTIVE IS NULL
AND LOOKUP_CODE NOT IN  (
SELECT LEAVE_TYPE
FROM XXMPCD_AT_LEAVE_TYPE_DTLS WHERE FULL_NAME='RajaShekar'
GROUP BY BATCH_NUMBER,FULL_NAME,LEAVE_TYPE);