Query to Extract Purchase Order (PO) and related Purchase Requisition (PR) in Oracle EBS

SELECT poh.org_id                  circle
     , poh.segment1                po_number
     , prha.segment1               req_number
FROM PO_HEADERS_ALL POH, 
       PO_DISTRIBUTIONS_ALL PDA ,
       PO_REQ_DISTRIBUTIONS_ALL PRDA ,
       PO_REQUISITION_LINES_ALL PRLA ,
       PO_REQUISITION_HEADERS_ALL PRHA
WHERE POH.PO_HEADER_ID = PDA. PO_HEADER_ID 
  AND PDA.REQ_DISTRIBUTION_ID = PRDA.DISTRIBUTION_ID
  AND PRDA.REQUISITION_LINE_ID = PRLA. REQUISITION_LINE_ID
  AND PRLA.REQUISITION_HEADER_ID = PRHA. REQUISITION_HEADER_ID
  AND prha.authorization_status   = 'APPROVED'
  AND poh.authorization_status    = 'APPROVED'
  AND POH.creation_date BETWEEN TO_DATE( '15-OCT-2018', 'DD-MON-YYYY' )AND TO_DATE( '21-OCT-2018', 'DD-MON-YYYY' );

1) How to extract 1-10 counting using Oracle SQL - 2) How to select data for more than 1000 using in clause

with dummy(id) as ( select 50 from dual union all select id + 1 from dummy where id < 60)  
select id from dummy
/

SELECT
   * 
FROM
   fnd_user 
WHERE
   (user_id,0) IN ((83196,0),(83196,0),(83196,0),(83196,0),(83196,0),(83196,0),(83196,0),(83196,0),(83196,0),(83196,0),(83196,0),(83196,0),(83196,0),(83196,0),(83196,0),(83196,0),(83196,0),(83196,0),(83196,0),(83196,0),(83196,0),(83196,0),(83196,0),(83196,0),(83196,0),(83196,0),(83196,0),(83196,0),(83196,0),(83196,0),(83196,0),(83196,0),(83196,0),(83196,0),(83196,0),(83196,0),(83196,0),(83196,0),(83196,0),(83196,0),(83196,0),(83196,0),(83196,0),(83196,0),(83196,0),(83196,0),(83196,0),(83196,0),(83196,0),(83196,0),(83196,0),(83196,0),(83196,0),(83196,0),(83196,0),(83196,0),(83196,0),(83196,0),(83196,0),(83196,0),(83196,0),(83196,0),(83196,0),(83196,0),(83196,0),(83196,0),(83196,0),(83196,0),(83196,0),(83196,0),(83196,0)


DB2

with dummy(id) as ( select 55 from SYSIBM.SYSDUMMY1 union all select id + 1 from dummy where id < 60)  
select id from dummy

Query to extract PO-PR-Which are Timed-Out

select poh.object_id, prha.segment1 REQUISITION_NUMBER,
(Select name from apps.hr_operating_units where ORGANIZATION_ID = prha.org_id ) Circle_name,
(select  max(wfn.notification_id)
from apps.wf_item_activity_statuses wias,
apps.wf_notifications wfn
where wias.notification_id is not null
and wias.notification_id = wfn.group_id
and wfn.status = 'OPEN'
and wias.item_type = 'REQAPPRV'
and wias.item_key in (select max(wi.item_key)
                       from apps.wf_items wi
                      where wi.parent_item_key = prha.wf_item_key
                        and end_date is null
                    )) Notification_id ,
poh.object_type_code,
poh.object_sub_type_code,
poh.sequence_num,
poh.action_code,
poh.action_date,
poh.employee_id,
papf.full_name
from apps.po_action_history poh, apps.per_all_people_f papf,apps.po_requisition_headers_all prha
 where poh.object_type_code='REQUISITION'
   and poh.employee_id = papf.person_id
   and prha.requisition_header_id=poh.object_id
   and prha.wf_item_key is not null
   and sysdate between papf.effective_start_date and papf.effective_end_date
   and poh.sequence_num = (select max(pah1.sequence_num) from apps.po_action_history pah1 where pah1.object_id = poh.object_id)
   and exists (select '1' from apps.po_action_history where object_id = poh.object_id and action_code ='NO ACTION')
   and poh.object_id in (select requisition_header_id from apps.po_requisition_headers_all where authorization_status ='IN PROCESS' and creation_date between '01-Mar-2019' and '14-Jun-2019')
union all
select poh.object_id, prha.segment1 po_NUMBER,
(Select name from apps.hr_operating_units where ORGANIZATION_ID = prha.org_id ) Circle_name,
(select  max(wfn.notification_id)
from apps.wf_item_activity_statuses wias,
apps.wf_notifications wfn
where wias.notification_id is not null
and wias.notification_id = wfn.group_id
and wfn.status = 'OPEN'
and wias.item_type = 'POAPPRV'
and wias.item_key = (select max(wi.item_key)
                       from apps.wf_items wi
                      where wi.item_key = prha.wf_item_key ) 
                    ) Notfication_id ,
poh.object_type_code,
poh.object_sub_type_code,
poh.sequence_num,
poh.action_code,
poh.action_date,
poh.employee_id,
papf.full_name
from apps.po_action_history poh, apps.per_all_people_f papf,apps.po_headers_all prha
 where poh.object_type_code='PO'
   and poh.employee_id = papf.person_id
   and prha.po_header_id=poh.object_id
   and prha.wf_item_key is not null
   and sysdate between papf.effective_start_date and papf.effective_end_date
   and poh.sequence_num = (select max(pah1.sequence_num) from apps.po_action_history pah1 where pah1.object_id = poh.object_id)
   and exists (select '1' from apps.po_action_history where object_id = poh.object_id and action_code ='NO ACTION')
   and poh.object_id in (select po_header_id from apps.po_headers_all where authorization_status ='IN PROCESS' and creation_date between '01-Mar-2019' and '14-Jun-2019')
   order by 5,3,2,1;

Query to Get AP Invoice "Approval Due On" Information

SELECT DISTINCT aih.invoice_id, aia.invoice_num
     , aia.invoice_date, hou.name  circle
     , fu.employee_id, ppf.full_name
     , wf.recipient_role   recipient_aprover_name
     , wf.notification_id
     , wf.begin_date  notification_start_date
     , wf.due_date notification_due_date
     , wf.item_key
  FROM apps.ap_inv_aprvl_hist_all   aih
     , apps.hr_operating_units      hou
     , apps.ap_invoices_all         aia
     , apps.wf_notifications        wf
     , apps.fnd_user                fu
     , apps.per_all_people_f        ppf
WHERE 1 = 1
  AND aih.org_id               = hou.organization_id
  AND aih.org_id               = aia.org_id
  AND aih.invoice_id           = aia.invoice_id
  AND aih.invoice_id IN( SELECT invoice_id 
                           FROM apps.ap_inv_aprvl_hist_all 
                          WHERE response = 'TIMEOUT')
  AND substr( wf.item_key, 1, 8 )  = TO_CHAR( aih.invoice_id )
  AND wf.recipient_role        = fu.user_name
  AND fu.employee_id           = ppf.person_id
  AND SYSDATE BETWEEN ppf.effective_start_date 
                  AND ppf.effective_end_date
  AND aih.creation_date BETWEEN '01-Mar-2019' 
                            AND '15-Jun-2019'
  AND wf.message_type          = 'APINVAPR'
  AND wf.status                = 'OPEN'
  AND wf.due_date >= SYSDATE
ORDER BY 10, 3, 2;

Query to get Oracle Alerts email contents and Email Stuck

Query to get Oracle Alerts email contents and Email Stuck

This will help in testing alert email in test instance. This query provide XML generated by oracle - which can be extracted to review the contents to be send in email.




select corrid 
     , enq_time 
     , decode(wno.state
          , 0, '0 = Pending in mailer queue',
            1, '1 = Pending in mailer queue',
            2, '2 = Sent by mailer on '||to_char(DEQ_TIME),
            3, '3 = Exception', 4,'4 = Wait', to_char(state)) State
      , to_char(DEQ_TIME)
      , wno.user_data.TEXT_VC
  from wf_notification_out wno
 where corrid like 'APPS:ALR%'
   and upper(wno.user_data.TEXT_VC) like upper('%Alert Subject%')
  order by enq_time desc