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

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

Important Oracle Apps OM Back to Back orders queries

Important Oracle Apps OM Back to Back orders queries

Back to Back order Requisition:

When the order line status moves to PO-ReqRequested (flow_status_code PO_REQ_REQUESTED). OM will insert a record in the PO requisitions interface table.

Checking the data in the PO requisitions interface table:

SELECT *

  FROM po_requisitions_interface_all

 WHERE interface_source_line_id = &order_line_id

       AND interface_source_code = 'CTO';

Relation between Sales Order Line and Purchase Order Line:

Check the table MTL_RESERVATIONS, after PO created, the system will automatically reserve the Sales Order Line to Purchase Order Line.

DEMAND_SOURCE_HEADER_ID = Sales Order Header Id
DEMAND_SOURCE_LINE_ID = Sales Order Line Id

SUPPLY_SOURCE_HEADER_ID = Purchase Order Header Id
SUPPLY_SOURCE_LINE_ID = Purchase Order Line Id

When the PO is received into inventory, the reservation is automatically transferred into Inventory, and it now looks like any other reservation from a sales order to on-hand stock.
At that time, SUPPLY_SOURCE_HEADER_ID = Purchase Order Header Id, but SUPPLY_SOURCE_LINE_ID = Null, as the reservation is transferred into Inventory.

The following SQL could be used as a reference, but only when Sales Order not picked or shipped.

SELECT UNIQUE

       TO_CHAR (res.demand_source_line_id) line,

       TO_CHAR (rcv.transaction_date, 'DD-MON-RR_HH24:MI:SS') trans_date,

       rcv.location_id loc_id,

       rcv.organization_id org_id,

       rcv.oe_order_header_id oe_head_id,

       rcv.oe_order_line_id oe_line_id,

       poh.segment1 po_number,

       poh.po_header_id po_head_id,

       rcv.po_line_id po_line_id,

       rcv.po_line_location_id line_loc_id,

       rcv.po_unit_price unit_price,

       rcv.primary_unit_of_measure uom,

       rcv.quantity qty

  FROM rcv_transactions rcv, mtl_reservations res, po_headers_all poh

 WHERE     res.demand_source_line_id = &so_line_id

       AND res.demand_source_type_id = 2

       AND res.supply_source_type_id IN (1, 13)

       AND res.supply_source_header_id = poh.po_header_id

       AND poh.po_header_id = rcv.po_header_id(+);

Query For find Last Query executed on the form

List the invoices for Trading Partner 'CDS, Inc' from the application.

Payables Manager > Invoices > Inquiry > Invoices > give "CDS, Inc" in the Trading Partner Name field > Find

Now we want to find the database query executed in the backend to show this data for you. Then goto

NAVIGATION:

Help > Diagnostics > Examine

Enter the following values:
Block: SYSTEM
Field: LAST_QUERY

Value filed displays the Query for you.
Copy the query and executed in SQL Developer/TOAD/SQL Plus to check the same data!

Queries Related to Concurrent Requests in 11i Applications

As part of day to day work, we need to use lot of queries to check the information about concurrent requests. Here are few queries which can be frequently used for day to day works and troubleshooting concurrent request / manager issues.

Note: These queries  needs to be run from APPS schema.

Scheduled concurrent requests
Lot of times we need to find out the concurrent programs scheduled. Users can schedule the concurrent requests in three ways (To run once at a specified time / To run periodically / To run on specific days of the month or week).

The below query will return all the concurrent requests which are scheduled using any of the above methods: 

SELECT cr.request_id,
DECODE (cp.user_concurrent_program_name,
'Report Set', 'Report Set:' || cr.description,
cp.user_concurrent_program_name
) NAME,
argument_text, cr.resubmit_interval,
NVL2 (cr.resubmit_interval,
'PERIODICALLY',
NVL2 (cr.release_class_id, 'ON SPECIFIC DAYS', 'ONCE')
) schedule_type,
DECODE (NVL2 (cr.resubmit_interval,
'PERIODICALLY',
NVL2 (cr.release_class_id, 'ON SPECIFIC DAYS', 'ONCE')
),
'PERIODICALLY', 'EVERY '
|| cr.resubmit_interval
|| ' '
|| cr.resubmit_interval_unit_code
|| ' FROM '
|| cr.resubmit_interval_type_code
|| ' OF PREV RUN',
'ONCE', 'AT :'
|| TO_CHAR (cr.requested_start_date, 'DD-MON-RR HH24:MI'),
'EVERY: ' || fcr.class_info
) schedule,
fu.user_name, requested_start_date
FROM apps.fnd_concurrent_programs_tl cp,
apps.fnd_concurrent_requests cr,
apps.fnd_user fu,
apps.fnd_conc_release_classes fcr
WHERE cp.application_id = cr.program_application_id
AND cp.concurrent_program_id = cr.concurrent_program_id
AND cr.requested_by = fu.user_id
AND cr.phase_code = 'P'
AND cr.requested_start_date > SYSDATE
AND cp.LANGUAGE = 'US'
AND fcr.release_class_id(+) = cr.release_class_id
AND fcr.application_id(+) = cr.release_class_app_id;

Note: The "SCHEDULE" column in the above query returns a string of zeros and ones for the requests which are scheduled on specific days of the month or week.
 Positions 1 through 31: Specific day of the month.
Position 32: Last day of the month
Positions 33 through 39: Sunday through Saturday

Checking the duplicated schedules of the same program with the same arguments
The below query can be used to check the duplicated schedule of the same program with the same arguments. This can be used to alert the users to cancel these duplicated schedules.

Note: This query will return even though the request was submitted using a different responsibility. 

SELECT request_id, NAME, argument_text, user_name
FROM (SELECT cr.request_id,
DECODE (cp.user_concurrent_program_name,
'Report Set', 'Report Set:' || cr.description,
cp.user_concurrent_program_name
) NAME,
argument_text, fu.user_name
FROM apps.fnd_concurrent_programs_tl cp,
apps.fnd_concurrent_requests cr,
apps.fnd_user fu
WHERE cp.application_id = cr.program_application_id
AND cp.concurrent_program_id = cr.concurrent_program_id
AND cr.requested_by = fu.user_id
AND cr.phase_code = 'P'
AND cr.requested_start_date > SYSDATE
AND cp.LANGUAGE = 'US'
AND fu.user_name NOT LIKE 'PPG%') t1
WHERE EXISTS (
SELECT 1
FROM (SELECT cr.request_id,
DECODE (cp.user_concurrent_program_name,
'Report Set', 'Report Set:'
|| cr.description,
cp.user_concurrent_program_name
) NAME,
argument_text, fu.user_name
FROM apps.fnd_concurrent_programs_tl cp,
apps.fnd_concurrent_requests cr,
apps.fnd_user fu
WHERE cp.application_id = cr.program_application_id
AND cp.concurrent_program_id =
cr.concurrent_program_id
AND cr.requested_by = fu.user_id
AND cr.phase_code = 'P'
AND cr.requested_start_date > SYSDATE
AND cp.LANGUAGE = 'US'
AND fu.user_name NOT LIKE 'PPG%') t2
WHERE t1.NAME = t2.NAME
AND t1.argument_text = t2.argument_text
AND t1.user_name = t2.user_name
GROUP BY NAME, argument_text, user_name
HAVING COUNT (*) > 1)
ORDER BY user_name, NAME 

Average pending time per request
This is a very useful query to check the performance of the concurrent managers.

Average pending time for a request is calculated like below:
("Highest of Requested_start_date or Date_submitted" - Actual_start_date ) / Total requests

A Request can be in Pending state for variety of reasons like conflict with other requests, improperly tuned managers (sleep seconds / cache size / number of managers etc)

We can schedule this script to gather data regularly for historical analysis as we normally purge the concurrent requests regularly.

SELECT TO_CHAR (actual_start_date, 'DD-MON-YYYY') DAY,
concurrent_queue_name,
(SUM ( ( actual_start_date
- (CASE
WHEN requested_start_date > request_date
THEN requested_start_date
ELSE request_date
END
)
)
* 24
* 60
* 60
)
)
/ COUNT (*) "Wait_Time_per_Req_in_Secs"
FROM apps.fnd_concurrent_requests cr,
apps.fnd_concurrent_processes fcp,
apps.fnd_concurrent_queues fcq
WHERE cr.phase_code = 'C'
AND cr.actual_start_date IS NOT NULL
AND cr.requested_start_date IS NOT NULL
AND cr.controlling_manager = fcp.concurrent_process_id
AND fcp.queue_application_id = fcq.application_id
AND fcp.concurrent_queue_id = fcq.concurrent_queue_id
GROUP BY TO_CHAR (actual_start_date, 'DD-MON-YYYY'), concurrent_queue_name
ORDER BY 2

Note: Depending on the purging schedules some requests might miss if the corresponding data in fnd_concurrent_processes is purged.

Checking which manager is going to execute a program

The below query identifies the manager which will be executing a given program. This query is based on the specialization rules set for the managers.

SELECT user_concurrent_program_name, user_concurrent_queue_name
FROM apps.fnd_concurrent_programs_tl cp,
apps.fnd_concurrent_queue_content cqc,
apps.fnd_concurrent_queues_tl cq
WHERE cqc.type_application_id(+) = cp.application_id
AND cqc.type_id(+) = cp.concurrent_program_id
AND cqc.type_code(+) = 'P'
AND cqc.include_flag(+) = 'I'
AND cp.LANGUAGE = 'US'
AND cp.user_concurrent_program_name = '&USER_CONCURRENT_PROGRAM_NAME' AND NVL (cqc.concurrent_queue_id, 0) = cq.concurrent_queue_id
AND NVL (cqc.queue_application_id, 0) = cq.application_id
AND cq.LANGUAGE = 'US'

To see all the pending / Running requests per each manager wise

SELECT request_id, phase_code, status_code, user_name,
user_concurrent_queue_name
FROM apps.fnd_concurrent_worker_requests cwr,
apps.fnd_concurrent_queues_tl cq,
apps.fnd_user fu
WHERE (cwr.phase_code = 'P' OR cwr.phase_code = 'R')
AND cwr.hold_flag != 'Y'
AND cwr.requested_start_date <= SYSDATE
AND cwr.concurrent_queue_id = cq.concurrent_queue_id
AND cwr.queue_application_id = cq.application_id
AND cq.LANGUAGE = 'US'
AND cwr.requested_by = fu.user_id
ORDER BY 5

Note: The same information can be seen in Administer Concurrent Manager form for each manager.

Checking the incompatibilities between the programs
The below query can be used to find all incompatibilities in an application instance. 

SELECT a2.application_name, a1.user_concurrent_program_name,
DECODE (running_type,
'P', 'Program',
'S', 'Request set',
'UNKNOWN'
) "Type",
b2.application_name "Incompatible App",
b1.user_concurrent_program_name "Incompatible_Prog",
DECODE (to_run_type,
'P', 'Program',
'S', 'Request set',
'UNKNOWN'
) incompatible_type
FROM apps.fnd_concurrent_program_serial cps,
apps.fnd_concurrent_programs_tl a1,
apps.fnd_concurrent_programs_tl b1,
apps.fnd_application_tl a2,
apps.fnd_application_tl b2
WHERE a1.application_id = cps.running_application_id
AND a1.concurrent_program_id = cps.running_concurrent_program_id
AND a2.application_id = cps.running_application_id
AND b1.application_id = cps.to_run_application_id
AND b1.concurrent_program_id = cps.to_run_concurrent_program_id
AND b2.application_id = cps.to_run_application_id
AND a1.language = 'US'
AND a2.language = 'US'
AND b1.language = 'US'
AND b2.language = 'US' 

The table apps.fnd_concurrent_program_serial has the information about incompatibilities.