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

How to find Last Query executed on the form

Business Case:
Imagine we have Queried/Searched something on the form and data is displayed. If you are curious to find database query executed for this search then this article explains the steps to find the same.

Solution:
Imagine i want to 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

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 TOAD/Sql Plus etc to see 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.

XML SEQUENCE SQL Function

How to use and what is XMLSEQUENCE SQL Function?
SQL function XMLSequence returns an XMLSequenceType value (a varray of XMLType instances). Because it returns a collection, this function can be used in the FROM clause of SQL queries.
Example 1:
SELECT value(T).getstringval() Attribute_Value
  FROM table(XMLSequence(extract(XMLType('V1V2V3'),
                                 '/A/B'))) T;

Example 2:
CREATE TABLE emp_xml_tab OF XMLType;

Table created.
INSERT INTO emp_xml_tab VALUES( XMLType('
                                           112Joe50000217
                                             Jane
                                             60000412Jack40000'));
1 row created.
COMMIT;

To create a new XML document containing only employees who earn $50,000 or more, you can use the following query:

SELECT sys_XMLAgg(value(em), XMLFormat('EMPLOYEES'))
  FROM emp_xml_tab doc, table(XMLSequence(extract(value(doc),
                                          '/EMPLOYEES/EMP'))) em
  WHERE extractValue(value(em), '/EMP/SALARY') >= 50000;

These are the steps involved in this query:
Function extract returns a fragment of EMP elements.
Function XMLSequence gathers a collection of these top-level elements into XMLType instances and returns that.
Function table makes a table value from the collection. The table value is then used in the query FROM clause.
Example 3:
XMLSEQUENCE: Generate a Document for Each Row of a Cursor
In this example, SQL function XMLSequence is used to create an XML document for each row of a cursor expression, and it returns an XMLSequenceType value (a varray of XMLType instances).
SELECT value(em).getClobVal() AS "XMLTYPE"
  FROM table(XMLSequence(Cursor(SELECT *
                                  FROM hr.employees
                                  WHERE employee_id = 104))) em;
Example 4:
XMLSEQUENCE: Unnesting Collections in XML Documents into SQL Rows

CREATE TABLE dept_xml_tab OF XMLType;
Table created.
INSERT INTO dept_xml_tab
  VALUES(
    XMLType('Sports
               John33333
               Jack333444')

);

1 row created.

INSERT INTO dept_xml_tab
  VALUES (
    XMLType('Sports
               Marlin20000')

);
1 row created.
COMMIT;

SELECT extractValue(OBJECT_VALUE, '/Department/@deptno') AS deptno,
       extractValue(value(em), '/Employee/@empno') AS empno,
       extractValue(value(em), '/Employee/Ename') AS ename
  FROM dept_xml_tab,
       table(XMLSequence(extract(OBJECT_VALUE,
                                 '/Department/EmployeeList/Employee'))) em;

Export and Import Data from XML Schema Database

Username:  sys as dba   

Password:   oracle
SQL> show parameter db_name;
It will shows database name
SQL> exit;

Q> How to setup my SID?
A> set oracle_sid <2nd DBname>

Export from user oe to user scott
Export: Release 10.1.0.4.2 - Production on Fri Nov 11 17:41:39 2011
Copyright (c) 1982, 2005, Oracle.  All rights reserved.

Username: oe/oe
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 – Production With the Partitioning, OLAP and Data Mining options
Enter array fetch buffer size: 4096 >
Export file: EXPDAT.DMP > c:\ware.dmp
(2)U(sers), or (3)T(ables): (2)U > t
Export table data (yes/no): yes > Y
Compress extents (yes/no): yes > Y
Export done in WE8MSWIN1252 character set and AL16UTF16 NCHAR character set
TA
About to export specified tables via Conventional Path ...
Table(T) or Partition(T:P) to be exported: (RETURN to quit) > WAREHOUSES . . Exporting table  WAREHOUSES     9 rows exported
Table(T) or Partition(T:P) to be exported: (RETURN to quit) >
Export terminated successfully without warnings.
C:\

***************************************************
TO IMPORT DATA FROM C:\WARE.DMP FILE TO USER SCOTT
C :\> IMP SCOTT/TIGER FILE=C:\WARE.DMP FULL=Y

SQL Loader with XML DATA

1. Conn hr/hr
2. Create table load_test of xmltype;
3. Exit fom user
4. Create a control file test.ctl

LOAD DATA
INFILE *
TRUNCATE INTO TABLE load_test
XMLType(xmldata)
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
(
xmldata
)
BEGINDATA
KING<  ./name><  ./  EMP>,
SCOTT<. /name><  ./EMP>,
SMITH< . /name><  ./EMP>

5. c:\> sqlldr hr/hr control= c:\test.ctl
6. sqlplus
   hr/hr
   select * from load_test;
Example to store XML type data from sql*loader

1. conn hr/hr
2. create table load_test of xmltype;
3. create a data file c:\person.dat - contains XML data
4. create a control file c:\load.ctl
LOAD DATA
INFILE *
INTO TABLE test_load
APPEND XMLType(XMLDATA)
(
 lobfn FILLER CHAR TERMINATED BY ',',
 XMLDATA LOBFILE(lobfn) TERMINATED BY ''
 )
BEGINDATA
c:\person.dat
5. c:\> sqlldr hr/hr
control=c:\load.ctl
6.sqlplus
hr/hr
select * from load_test;

mPurchase Order Detail Query :

 h.segment1 "PO NUM",

h.authorization_status "STATUS",
l.line_num "SEQ NUM",
ll.line_location_id,
d.po_distribution_id ,
h.type_lookup_code "TYPE"
from
po.po_headers_all h,
po.po_lines_all l,
po.po_line_locations_all ll,
po.po_distributions_all d
where h.po_header_id = l.po_header_id
and ll.po_line_id = l.po_Line_id
and ll.line_location_id = d.line_location_id
and h.closed_date is null
and h.type_lookup_code not in ('QUOTATION')

Oracle APIs and Script to find Oracle APIs

You can find all oracle standard API at following link.  http://irep.oracle.com/index.html

Following script and get all the packages related to API in Oracle applications, from which you can select APIs that pertain to AP. You can change the name like to PA or AR and can check for different modules

select substr(a.OWNER,1,20)
, substr(a.NAME,1,30)
, substr(a.TYPE,1,20)
, substr(u.status,1,10) Stat
, u.last_ddl_time
, substr(text,1,80) Description
from dba_source a, dba_objects u
WHERE 2=2
and u.object_name = a.name
and a.text like ‘%Header%’
and a.type = u.object_type
and a.name like ‘PA_%API%’
order by
a.owner, a.name;

Oracle EBS R12 Purchasing, Inventory, Order Management Queries

To Find Duplicate Item Category Code

SELECT category_set_name, category_concat_segments, COUNT (*)
FROM mtl_category_set_valid_cats_v
WHERE (category_set_id = 1)
GROUP BY category_set_name, category_concat_segments
HAVING COUNT (*) > 1
ORDER BY category_concat_segments

Get Number Of canceled requisition

SELECT a.AUTHORIZATION_STATUS,(a.ORG_ID),(SELECT distinct hr.per_all_people_f.first_name|| ‘ ‘|| hr.per_all_people_f.middle_names|| ‘ ‘|| hr.per_all_people_f.last_name “Employee Name”
FROM hr.per_all_people_f
where hr.per_all_people_f.PERSON_ID in
(select employee_id from fnd_user fu where fu.user_id = a.CREATED_BY)) CREATED_BY,count(SEGMENT1 )
FROM
po_requisition_headers_all a
WHERE
a.creation_date BETWEEN TO_DATE(’01/01/2007′, ‘DD/MM/YYYY’)
and TO_DATE(’30/05/2007′, ‘DD/MM/YYYY’)
and a.AUTHORIZATION_STATUS = ‘CANCELLED’
group by a.AUTHORIZATION_STATUS,a.ORG_ID,a.CREATED_BY

Number of line processed in Order Management

SELECT COUNT (line_id) “Order Line Processed”
FROM oe_order_lines_all
WHERE creation_date BETWEEN TO_DATE (:Fdate, ‘DD/MM/YYYY’)
AND TO_DATE (:tdate, ‘DD/MM/YYYY’)
AND flow_status_code = ‘CLOSED’;

To Check Item Catogry For Inventory master (No Of Segments May Varry)

SELECT ood.organization_name,
segment1|| ‘-’|| segment2|| ‘-’|| segment3 catgory
FROM org_organization_definitions ood,
mtl_categories_vl mcv,
mtl_category_sets mcs
WHERE mcs.structure_id = mcv.structure_id
ORDER BY ood.organization_name

Check Locators for inventory Inventory Org Wise(Number of segment may varry)

SELECT mil.segment1 loc_seg1, mil.segment11 loc_seg11, mil.segment2 loc_seg2,
mil.segment3 loc_seg3, mil.segment4 loc_seg4, mil.segment5 loc_seg5,
mil.segment6 loc_seg6,ood.ORGANIZATION_NAME,mil.SUBINVENTORY_CODE
FROM mtl_item_locations mil,org_organization_definitions ood
where mil.ORGANIZATION_ID = ood.ORGANIZATION_ID

Display All Subinventories Setup
select msi.secondary_inventory_name, MSI.SECONDARY_INVENTORY_NAME “Subinventory”, MSI.DESCRIPTION “Description”,
MSI.DISABLE_DATE “Disable Date”, msi.PICKING_ORDER “Picking Order”,
gcc1.concatenated_segments “Material Account”,
gcc2.concatenated_segments “Material Overhead Account”,
gcc3.concatenated_segments “Resource Account”,
gcc4.concatenated_segments “Overhead Account”,
gcc5.concatenated_segments “Outside Processing Account”,
gcc6.concatenated_segments “Expense Account”,
gcc7.concatenated_segments “Encumbrance Account”,
msi.material_overhead_account,
msi.resource_account,
msi.overhead_account,
msi.outside_processing_account,
msi.expense_account,
msi.encumbrance_account
from mtl_secondary_inventories msi,
gl_code_combinations_kfv gcc1,
gl_code_combinations_kfv gcc2,
gl_code_combinations_kfv gcc3,
gl_code_combinations_kfv gcc4,
gl_code_combinations_kfv gcc5,
gl_code_combinations_kfv gcc6,
gl_code_combinations_kfv gcc7
where msi.material_account = gcc1.CODE_COMBINATION_ID(+)
and msi.material_overhead_account = gcc2.CODE_COMBINATION_ID(+)
and msi.resource_account = gcc3.CODE_COMBINATION_ID(+)
and msi.overhead_account = gcc4.CODE_COMBINATION_ID(+)
and msi.outside_processing_account = gcc5.CODE_COMBINATION_ID(+)
and msi.expense_account = gcc6.CODE_COMBINATION_ID(+)
and msi.encumbrance_account = gcc7.CODE_COMBINATION_ID(+)
order by msi.secondary_inventory_name

To Select Unit Of measure exist in ebusiness suite

select uom_code,unit_of_measure
from mtl_units_of_measure

Query to find out Customer Master Information. Customer Name, Account Number, Adress etc.
select p.PARTY_NAME,ca.ACCOUNT_NUMBER,loc.address1,loc.address2,loc.address3,loc.city,loc.postal_code,
loc.country,ca.CUST_ACCOUNT_ID
from apps.ra_customer_trx_all I,
apps.hz_cust_accounts CA,
apps.hz_parties P,
apps.hz_locations Loc,
apps.hz_cust_site_uses_all CSU,
apps.hz_cust_acct_sites_all CAS,
apps.hz_party_sites PS
where I.COMPLETE_FLAG =’Y’
and I.bill_TO_CUSTOMER_ID= CA.CUST_ACCOUNT_ID
and ca.PARTY_ID=p.PARTY_ID
and I.bill_to_site_use_id=csu.site_use_id
and csu.CUST_ACCT_SITE_ID=cas.CUST_ACCT_SITE_ID
and cas.PARTY_SITE_ID=ps.party_site_id
and ps.location_id=loc.LOCATION_ID

Query to find on Hand Quantity

select sum(transaction_quantity) from MTL_ONHAND_QUANTITIES
where inventory_item_id=9
and organization_id=188

Qunatity on order, Expected Deliver

select sum(ordered_quantity),a.SCHEDULE_SHIP_DATE
from oe_order_lines_all a
where inventory_item_id=10
and ship_from_org_id=188
group by a.SCHEDULE_SHIP_DATE

Query to find Item Code, Item Description Oracle Item Master Query

select item, description from mtl_system_items_b
where inventory_item_id=&your_item
and organization_id=&organization_id) item

Query to Find out On Hand Quantity of specific Item Oracle inventory
select sum(transaction_quantity) from mtl_onhand_quantity_details
where inventory_item_id=&your_item
and organization_id=&organization_id

Qty On Order,

Expected deivery date(select sum(ordered_quantity),

scheduled_ship_date from oe_order_lines_all
where inventory_item_id=&your_item
and ship_from_org_id=&organization_id
group by scheduled_ship_date) order_info

–Total Received Qty
select sum(transaction_quantity) from mtl_material_transactions
inventory_item_id=&your_item
and organization_id=&organization_id
and transaction_quantity>0)

Total received Qty in 9 months
select sum(transaction_quantity) from mtl_material_transactions
inventory_item_id=&your_item
and organization_id=&organization_id
and transaction_quantity>0
and transaction_date between trunc(sysdate) and trunc(sysdate-270))

Total issued quantity in 9 months

select sum(transaction_quantity) from mtl_material_transactions
inventory_item_id=&your_item
and organization_id=&organization_id
and transaction_quantity<0 and transaction_date between trunc(sysdate) and trunc(sysdate-270)) tot_iss_qty_9mths, –Average monthly consumption
(select sum(transaction_quantity)/30 from mtl_material_transactions
inventory_item_id=&your_item
and organization_id=&organization_id
and transaction_quantity<0) ;

Display all categories that the Item Belongs
SELECTunique micv.CATEGORY_SET_NAME “Category Set”,
micv.CATEGORY_SET_ID “Category Set ID”,
decode( micv.CONTROL_LEVEL,
1, ‘Master’,
2, ‘Org’,
‘Other’) “Control Level”,
micv.CATEGORY_ID “Category ID”,
micv.CATEGORY_CONCAT_SEGS “Category”
FROM
MTL_ITEM_CATEGORIES_V micv

Another Query to Get Onhand Qty With Oranization ID, Item Code, Quantity
SELECT organization_id,
(SELECT ( msib.segment1|| ‘-’|| msib.segment2|| ‘-’|| msib.segment3|| ‘-’|| msib.segment4)
FROM mtl_system_items_b msib
WHERE msib.inventory_item_id = moq.inventory_item_id
AND msib.organization_id = moq.organization_id) “Item Code”,
(SELECT description
FROM mtl_system_items_b msib
WHERE msib.inventory_item_id =
moq.inventory_item_id
AND msib.organization_id = moq.organization_id)
“item Description”,
SUM (moq.transaction_quantity) onhandqty
FROM mtl_onhand_quantities moq
GROUP BY moq.organization_id, (moq.inventory_item_id)

Script to get all the Concurrent Program Request details

Script to get all the Concurrent Program Request details

Below is the script to get the Concurrent Program Request details by Various Users in a Particular Day.

view source

print?

select
    request_id,

    parent_request_id,

    fcpt.user_concurrent_program_name Request_Name,

    fcpt.user_concurrent_program_name program_name,

    DECODE(fcr.phase_code,

            'C','Completed',

            'I','Incactive',

            'P','Pending',

            'R','Running') phase,

    DECODE(fcr.status_code,

            'D','Cancelled',

            'U','Disabled',

            'E','Error',

            'M','No Manager',

            'R','Normal',

            'I','Normal',

            'C','Normal',

            'H','On Hold',

            'W','Paused',

            'B','Resuming',

            'P','Scheduled',

            'Q','Standby',

            'S','Suspended',

            'X','Terminated',

            'T','Terminating',

            'A','Waiting',

            'Z','Waiting',

            'G','Warning','N/A') status,

    round((fcr.actual_completion_date - fcr.actual_start_date),3) * 1440 as Run_Time,

    round(avg(round(to_number(actual_start_date - fcr.requested_start_date),3) * 1440),2) wait_time,

    fu.User_Name Requestor,

    fcr.argument_text parameters,

    to_char (fcr.requested_start_date, 'MM/DD HH24:mi:SS') requested_start,

    to_char(actual_start_date, 'MM/DD/YY HH24:mi:SS') ACT_START,

    to_char(actual_completion_date, 'MM/DD/YY HH24:mi:SS') ACT_COMP,

    fcr.completion_text

From

    apps.fnd_concurrent_requests fcr,

    apps.fnd_concurrent_programs fcp,

    apps.fnd_concurrent_programs_tl fcpt,

    apps.fnd_user fu

Where 1=1

    -- and fu.user_name = 'DJKOCH' '

    -- and fcr.request_id = 1565261

    -- and fcpt.user_concurrent_program_name = 'Payables Open Interface Import''

    and fcr.concurrent_program_id = fcp.concurrent_program_id

    and fcp.concurrent_program_id = fcpt.concurrent_program_id

    and fcr.program_application_id = fcp.application_id

    and fcp.application_id = fcpt.application_id

    and fcr.requested_by = fu.user_id

    and fcpt.language = 'US'

    and fcr.actual_start_date like sysdate

    -- and fcr.phase_code = 'C'

    -- and hold_flag = 'Y'

    -- and fcr.status_code = 'C'

GROUP BY

    request_id,

    parent_request_id,

    fcpt.user_concurrent_program_name,

    fcr.requested_start_date,

    fu.User_Name,

    fcr.argument_text,

    fcr.actual_completion_date,

    fcr.actual_start_date,

    fcr.phase_code,

    fcr.status_code,

    fcr.resubmit_interval,

    fcr.completion_text,

    fcr.resubmit_interval,

    fcr.resubmit_interval_unit_code,

    fcr.description

Order by 1 desc;