Query to find AP Bank details at Suppliers and Supplier Sites

Query:

SELECT 'Bank Account At Supplier Site Level' Bank_Account_Level
     , sup.segment1
     , sup.vendor_name
     , epa.org_id
     , ss.vendor_site_code
     , NULL   Party_Site_Code
     , eba.bank_account_num
     , piu.order_of_preference priority
     , eba.ext_bank_account_id
  FROM ap_suppliers sup
     , ap_supplier_sites_all       ss
     , iby_external_payees_all epa
     , iby_pmt_instr_uses_all  piu
     , iby_ext_bank_accounts   eba
 WHERE sup.vendor_id     = ss.vendor_id
   AND ss.vendor_site_id = epa.supplier_site_id
   AND epa.ext_payee_id  = piu.ext_pmt_party_id    
   AND piu.instrument_id = eba.ext_bank_account_id
   AND sup.segment1      = '10824'
UNION
SELECT 'Bank Account at Supplier Level'
     , sup.segment1
     , sup.vendor_name
     , epa.org_id
     , NULL
     , NULL
     , eba.bank_account_num
     , piu.order_of_preference priority
     , eba.ext_bank_account_id
  FROM ap_suppliers sup
     , iby_external_payees_all epa
     , iby_pmt_instr_uses_all  piu
     , iby_ext_bank_accounts   eba
 WHERE sup.party_id        = epa.payee_party_id
   AND epa.ext_payee_id    = piu.ext_pmt_party_id    
   AND piu.instrument_id   = eba.ext_bank_account_id
   AND sup.segment1        = '10824'
   AND supplier_site_id    IS NULL
   AND party_site_id       IS NULL
   ;

Query to find SR's assigned to specific user in Oracle Apps R12

Query:

select * from
(SELECT cia.incident_number       SR_NUMBER
,       papf.full_name             SR_Person
,       fu.user_name               SR_user
,       'OWNER'                    Record_Type
,       cia.INCIDENT_DATE          SR_Creation_date
,       cia.INCIDENT_LAST_MODIFIED_DATE
,       NULL                       old_status
,       cidv.NEW_STATUS_NAME       current_status
,       cidv.NEW_TYPE_NAME         SR_type
,       'NULL'   Notes
,       cidv.INCIDENT_DATE        Date1
,       cia.summary
,       cia.resolution_summary
FROM    cs_incidents_all          cia
,       jtf_rs_resource_extns     jrre
,       per_all_people_f          papf
,       fnd_user                  fu
,       CS_INCIDENTS_DIARY_V      cidv
WHERE   cia.INCIDENT_OWNER_ID = jrre.RESOURCE_ID
AND     jrre.SOURCE_ID        = papf.person_id
AND     papf.person_id        = fu.employee_id
AND     cia.incident_id       = cidv.incident_id
AND     cidv.INCIDENT_OWNER_FLAG = 'Y'
AND     cidv.INCIDENT_TYPE_FLAG  ='Y'
UNION
select  cia.incident_number SR_NUMBER
,       papf.full_name     SR_Person
,       cidv.owner         SR_Owner
,       'STATUS UPDATE'       Record_Type
,       cia.INCIDENT_DATE  SR_Creation_date
,       cia.INCIDENT_LAST_MODIFIED_DATE
,       cidv.OLD_STATUS_NAME  old_status
,       cidv.NEW_STATUS_NAME  current_status
,       cidv.NEW_TYPE_NAME   SR_type
,       'NULL'   Notes
,       cidv.INCIDENT_DATE  Date1
,       cia.summary
,       cia.resolution_summary
FROM    cs_incidents_all          cia
,       CS_INCIDENTS_DIARY_V      cidv
,       CS_INCIDENT_STATUSES_tL   cstt
,       CS_INCIDENT_SEVERITIES_tl cist
,       fnd_user                  fu
,       per_all_people_f          papf
WHERE   cia.incident_id            = cidv.incident_id
AND     cidv.INCIDENT_OWNER_FLAG  != 'Y'
AND     cidv.OLD_STATUS_NAME      != cidv.NEW_STATUS_NAME
AND     cia.INCIDENT_STATUS_ID     = cstt.INCIDENT_STATUS_ID
AND     cstt.language              = 'US'
AND     cia.INCIDENT_SEVERITY_ID   = cist.INCIDENT_SEVERITY_ID
AND     cist.language              = 'US'
AND     cidv.owner                = fu.USER_NAME
AND     fu.employee_id            = papf.person_id
UNION
select  cia.incident_number SR_NUMBER
,       papf.full_name     SR_Person
,       fu.user_name       SR_Owner
,       'NOTE'             Record_Type
,       cia.INCIDENT_DATE  SR_Creation_date
,       cia.INCIDENT_LAST_MODIFIED_DATE
,       NULL  old_stus
,       cist.name  current_status
,       citt.name   SR_type
,       jnv.NOTES Notes
,       jnv.ENTERED_DATE  Date1
,       cia.summary
,       cia.resolution_summary
FROM    cs_incidents_all cia
,       JTF_NOTES_VL  jnv
,       cs_incident_types_tl citt
,       cs_incident_statuses_tl cist
,       fnd_user      fu
,       per_all_people_f   papf
WHERE   cia.incident_id     = jnv.source_object_id
AND     jnv.SOURCE_OBJECT_CODE ='SR'
and     cia.INCIDENT_TYPE_ID = citt.INCIDENT_TYPE_ID
AND     citt.language              = 'US'
AND     cia.INCIDENT_STATUS_ID = cist.INCIDENT_STATUS_ID
AND     cist.language              = 'US'
AND     jnv.created_by      = fu.user_id
AND     fu.employee_id =papf.person_id
UNION
SELECT  incident_number            SR_NUMBER
,       papf.full_name             SR_Person
,       fu.user_name               SR_user
,       'RE ASSIGN'                Record_Type
,       cia.INCIDENT_DATE          SR_Creation_date
,       cia.INCIDENT_LAST_MODIFIED_DATE
,       cidv.OLD_STATUS_NAME       old_status
,       cidv.NEW_STATUS_NAME       current_status
,       cidv.NEW_TYPE_NAME         SR_type
,       NULL                Notes
,       cidv.INCIDENT_DATE         Date1
,       cia.summary
,       cia.resolution_summary
FROM    cs_incidents_all          cia
,       jtf_rs_resource_extns     jrre
,       per_all_people_f          papf
,       fnd_user                  fu
,       CS_INCIDENTS_DIARY_V      cidv
,       jtf_rs_resource_extns     old_res
,       per_all_people_f          old_per
,       fnd_user                  old_user
WHERE   cia.INCIDENT_OWNER_ID = jrre.RESOURCE_ID
AND     jrre.SOURCE_ID        = papf.person_id
AND     papf.person_id        = fu.employee_id
AND     cia.incident_id       = cidv.incident_id
AND     cidv.OWNER_OLD        != OWNER_NEW
AND     cidv.OWNER_OLD        = old_res.RESOURCE_ID
AND     old_res.SOURCE_ID     = old_per.person_id
AND     old_per.person_id     = old_user.employee_id
AND     cidv.INCIDENT_OWNER_FLAG = 'Y'
)
where SR_user = 'SATISHV'
--AND   trunc(Date1) > trunc(sysdate-365)
order by SR_NUMBER
;







Query Credits : Siva Gollapalle