Thursday, January 7, 2016

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 = prha.requisition_header_id
       AND action_code = 'CANCEL'
       AND pah.object_type_code = 'REQUISITION';


Relation Between Requisition and PO


SELECT prha.segment1 "REQ NUM", pha.segment1 "PO NUM"
  FROM po_headers_all pha,
       po_distributions_all pda,
       po_req_distributions_all prda,
       po_requisition_lines_all prla,
       po_requisition_headers_all prha
 WHERE     pha.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


Monday, November 2, 2015

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);

Friday, September 4, 2015

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


Friday, January 30, 2015

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.