BACK ORDERS QUERY in Oracle Apps


SELECT ooha.header_id,
       line_id,
       released_status,
       ooha.*
  FROM oe_order_headers_all ooha,
       wsh_delivery_details wdd,
       oe_order_lines_all oola
 WHERE     ooha.header_id = oola.header_id
       AND ooha.header_id = wdd.source_header_id
       AND wdd.released_status = 'B'
       AND ooha.ordered_date > SYSDATE - 1
       --       AND oola.ordered_item = 'NBR' -- SKU
       AND NOT EXISTS
                  (SELECT 1
                     FROM wsh_delivery_details wdd1
                    WHERE     wdd1.released_status != 'B'
                          AND wdd1.source_header_id = ooha.header_id);

How to Check Header/Line WORKFLOW status from Back end?

We can check the workflow status from back end also for Header or Line with below query.

    SELECT wias.item_key,
         wpa.activity_name,
         wias.activity_status,
         wias.activity_result_code,
         wias.assigned_user,
         wias.begin_date,
         wias.end_date
    FROM WF_ITEM_ACTIVITY_STATUSES wias, WF_PROCESS_ACTIVITIES wpa
   WHERE     wias.process_activity = wpa.instance_id(+)
         AND wpa.process_item_type = wias.item_type
         AND wias.ACTIVITY_STATUS = 'ERROR'
         AND wias.end_date IS NULL
         AND wias.item_key = TO_CHAR (12345)           -- Header_id or Line_id
ORDER BY begin_date DESC


How To Add new columns into Standard Work Order Report in eAM



  1. Download the report (rdf & rtf) from server. 
  2.  Rename the report by prefixing xx or anything you wish. 
  3.  Add the required columns in rdf by changing the query.
  4. Add the columns in rtf and give the xml tags.
  5. Register the concurrent program as custom name, attach to request group, add to responsibility and check the report.

How to find all cancel Requisitions

SELECT prha . *   FROM po_Requisition_headers_all prha , po_action_history pah   WHERE      1 = 1        AND pah . object_id ...