Pending Material Query

   Select 'Pending Material' ||'~'||
       ood.Organization_code ||'~'||
       to_char(Mti.Creation_date,'DD-MON-RRRR HH24:MI:SS') ||'~'||
            Mti.Transaction_interface_id ||'~'||
       Msi.Segment1 ||'~'||
       Mti.Transaction_quantity ||'~'||
       Mtst.Transaction_source_type_name ||'~'||
       Mtt.Transaction_type_name ||'~'||
       Mti.Error_code ||'~'||
       mti.created_by ||'~'||
       mti.request_id||'~'||
       mti.subinventory_code||'~'||
       Mti.Process_flag ||'~'||
       Mti.Lock_flag||'~'||
       Mti.Transaction_mode||'~'||
       Mti.Validation_required||'~'||
       replace(Mti.Error_explanation,chr(10),' ') ||'~'||
       Mti.Organization_id  l_out
  From Mtl_transactions_interface Mti,
       Org_organization_definitions Ood,
       Mtl_system_items_b Msi,
       Mtl_txn_source_types Mtst,
       Mtl_transaction_types Mtt
 Where Mti.Organization_id = Ood.Organization_id
   And Msi.Inventory_item_id = Mti.Inventory_item_id
   And Msi.Organization_id = Mti.Organization_id
   And Mtst.Transaction_source_type_id = Mti.Transaction_source_type_id
   And Mtt.Transaction_type_id = Mti.Transaction_type_id
   And Mti.process_flag <> 9
   and ood.organization_id = nvl(p_org_id,ood.organization_id)
   and (:p_minutes is null or mti.creation_date < SYSDATE - (:p_minutes/(24*60)) )
   order by ood.organization_code, mti.creation_date;

Unprocessed Material Query

  SELECT 'Unprocessed Material' ||'~'||
       Ood.Organization_code ||'~'||
       to_char(Mmtt.Creation_date,'DD-MON-RRRR HH24:MI:SS') ||'~'||
       decode(trunc(sysdate)-trunc(mmtt.Creation_date),0,'',1,'',2,'','Escalate')||'~'||
       Mmtt.Transaction_temp_id ||'~'||
       Msi.Segment1 ||'~'||
       Mmtt.Transaction_quantity ||'~'||
       Mtst.Transaction_source_type_name ||'~'||
       Mtt.Transaction_type_name ||'~'||
       Mmtt.Error_code ||'~'||
       replace(Mmtt.Error_explanation,chr(10),' ') ||'~'||
       Mmtt.Process_flag ||'~'||
       Mmtt.Lock_flag ||'~'||
       Mmtt.Transaction_mode ||'~'||
       Mmtt.Organization_id  l_out
  From Mtl_material_transactions_temp Mmtt,
       Org_organization_definitions Ood,
       Mtl_system_items_b Msi,
       Mtl_txn_source_types Mtst,
       Mtl_transaction_types Mtt
 Where Mmtt.Organization_id = Ood.Organization_id
   And Msi.Inventory_item_id = Mmtt.Inventory_item_id
   And Msi.Organization_id = Mmtt.Organization_id
   And Mtst.Transaction_source_type_id = Mmtt.Transaction_source_type_id
   And Mtt.Transaction_type_id = Mmtt.Transaction_type_id
   and ood.organization_id     = nvl (p_org_id,ood.organization_id)
   And Mmtt.Process_flag in ('Y','E')
   and nvl(mmtt.transaction_status,0) <> 2
   and (:p_minutes is null or mmtt.creation_date < SYSDATE - (:p_minutes/(24*60)) )
   order by ood.organization_code, Mmtt.creation_date;

Uncosted Material Query

Select 'Uncosted Material' ||'~'||
       Ood.Organization_code ||'~'||
       to_char(Mmt.Creation_date,'DD-MON-RRRR HH24:MI:SS') ||'~'||
       decode(trunc(sysdate)-trunc(mmt.Creation_date),0,'',1,'',2,'','Escalate')||'~'||
       Mmt.Transaction_id||'~'||
       Msi.Segment1 ||'~'||
       Mmt.Transaction_quantity ||'~'||
       Mtst.Transaction_source_type_name ||'~'||
       Mtt.Transaction_type_name ||'~'||
       Mmt.Error_code ||'~'||
       replace(Mmt.Error_explanation,chr(10),' ') ||'~'||
       Mmt.Costed_flag ||'~'||
       Mmt.Cost_update_id ||'~'||
       Mmt.Transaction_group_id ||'~'||
       Mmt.Organization_id  l_out
 From Mtl_material_transactions Mmt,
       Org_organization_definitions Ood,
       Mtl_system_items_b Msi,
       Mtl_txn_source_types Mtst,
       Mtl_transaction_types Mtt
 Where Mmt.Organization_id             = Ood.Organization_id
   And Msi.Inventory_item_id           = Mmt.Inventory_item_id
   And Msi.Organization_id             = Mmt.Organization_id
   And Mtst.Transaction_source_type_id = Mmt.Transaction_source_type_id
   And Mtt.Transaction_type_id         = Mmt.Transaction_type_id
   and ood.organization_id             = nvl (p_org_id,ood.organization_id)
   And Mmt.costed_flag                 in ('E','N')
   and (:p_minutes is null or  mmt.creation_date < SYSDATE - (:p_minutes/(24*60)) )
   order by ood.organization_code, Mmt.creation_date;

Item Category and Set Details

SELECT
DISTINCT msi.segment1,
                   msi.item_type,
                   mcb.segment1 category_name,
                   mcst.category_set_name,
                   msi.comms_nl_trackable_flag,
                   msi.serviceable_product_flag
  FROM apps.mtl_system_items msi,
       apps.mtl_category_sets mcst,
       apps.mtl_categories_b mcb,
       apps.mtl_item_categories mic
 WHERE     msi.inventory_item_id = mic.inventory_item_id
       AND mic.category_set_id = mcst.category_set_id
       AND mic.category_id = mcb.category_id
       AND msi.organization_id = mic.organization_id
       AND mcst.structure_id = mcb.structure_id
       AND msi.segment1 = '21138299'

On Hand Quantity Thru API

SET SERVEROUTPUT ON;

DECLARE

v_api_return_status VARCHAR2 (1);

v_qty_oh NUMBER;

v_qty_res_oh NUMBER;

v_qty_res NUMBER;

v_qty_sug NUMBER;

v_qty_att NUMBER;

v_qty_atr NUMBER;

v_msg_count NUMBER;

v_msg_data VARCHAR2(1000);

v_inventory_item_id VARCHAR2(250) := '1234';

v_organization_id VARCHAR2(10) := '567';

BEGIN

inv_quantity_tree_grp.clear_quantity_cache;

DBMS_OUTPUT.put_line ('Transaction Mode');

DBMS_OUTPUT.put_line ('Onhand For the Item :'|| v_inventory_item_id );

DBMS_OUTPUT.put_line ('Organization :'|| v_organization_id);

INV_QUANTITY_TREE_PUB.QUERY_QUANTITIES

(p_api_version_number => 1.0,

p_init_msg_lst => apps.fnd_api.g_false,

x_return_status => v_api_return_status,

x_msg_count => v_msg_count,

x_msg_data => v_msg_data,

p_organization_id => v_organization_id,

p_inventory_item_id => v_inventory_item_id,

p_tree_mode => apps.inv_quantity_tree_pub.g_transaction_mode,

p_onhand_source => 3,

p_is_revision_control => FALSE,

p_is_lot_control => FALSE,

p_is_serial_control => FALSE,

p_revision => NULL,

p_lot_number => NULL,

p_subinventory_code => NULL,

p_locator_id => NULL,

x_qoh => v_qty_oh,

x_rqoh => v_qty_res_oh,

x_qr => v_qty_res,

x_qs => v_qty_sug,

x_att => v_qty_att,

x_atr => v_qty_atr);

DBMS_OUTPUT.put_line ('on hand Quantity:'|| v_qty_oh);

DBMS_OUTPUT.put_line ('Reservable quantity on hand :'|| v_qty_res_oh);

DBMS_OUTPUT.put_line ('Quantity reserved :'|| v_qty_res);

DBMS_OUTPUT.put_line ('Quantity suggested :'|| v_qty_sug);

DBMS_OUTPUT.put_line ('Quantity Available To Transact :'|| v_qty_att);

DBMS_OUTPUT.put_line ('Quantity Available To Reserve :'|| v_qty_atr);

Item & Pricing Details

SELECT
MSI.INVENTORY_ITEM_ID,
MSI.ORGANIZATION_ID,
MSI.SEGMENT1 SKU,
MSI.ITEM_TYPE,
MCB.SEGMENT1 CATEGORY_NAME,
MCST.CATEGORY_SET_NAME,
MSI.DESCRIPTION,
QLH.NAME,
QLL.CREATION_DATE,
QLL.LIST_LINE_TYPE_CODE,
QLL.OPERAND,
QPA.PRODUCT_ATTRIBUTE_CONTEXT,
QPA.PRODUCT_ATTRIBUTE,
QPA.PRODUCT_ATTR_VALUE,
QPA.PRODUCT_UOM_CODE,
QPA.PRICING_ATTRIBUTE_CONTEXT,
QPA.PRICING_ATTRIBUTE,
QPA.PRICING_ATTR_VALUE_FROM
FROM
MTL_CATEGORY_SETS MCST,
MTL_CATEGORIES_B MCB,
MTL_ITEM_CATEGORIES MIC,
MTL_SYSTEM_ITEMS MSI,
QP_PRICING_ATTRIBUTES QPA,
QP_LIST_LINES QLL,
QP_LIST_HEADERS_TL QLH
WHERE
MIC.CATEGORY_SET_ID = MCST.CATEGORY_SET_ID
AND MIC.CATEGORY_ID = MCB.CATEGORY_ID
AND MSI.INVENTORY_ITEM_ID = MIC.INVENTORY_ITEM_ID
AND MSI.ORGANIZATION_ID = MIC.ORGANIZATION_ID
AND MCST.STRUCTURE_ID = MCB.STRUCTURE_ID
AND QPA.LIST_LINE_ID = QLL.LIST_LINE_ID
AND QPA.LIST_HEADER_ID = QLH.LIST_HEADER_ID
AND QLH.LIST_HEADER_ID = QLL.LIST_HEADER_ID
AND MSI.INVENTORY_ITEM_ID = QPA.PRODUCT_ATTR_VALUE
AND QLH.LANGUAGE = 'US'
--AND QLH.NAME = 'US USD PRICE LIST' -- PRICE LIST NAME
--AND QPA.PRODUCT_ATTR_VALUE = '1928882' -- INVENTORY ITEM ID
--AND QPA.PRICING_ATTRIBUTE_CONTEXT = 'ITEM'
--AND MCST.CATEGORY_SET_NAME = ('PURCHASING') -- CATEGORY SET NAME
--AND MCB.SEGMENT1 = ('PURCHASING') -- CATEGORY NAME
--AND MSI.ORGANIZATION_ID = 7
--MSI.SEGMENT1 = '21212903-CWS' -- ITEM NUMBER
ORDER BY ITEM_TYPE DESC

INV Open Periods

SELECT
opu.name as operating_unit
, per.organization_id as inv_org_id
, par.organization_code as inv_org_code
, per.period_name
, per.period_year
, per.period_num
, flv.meaning as status
, per.period_start_date
, per.schedule_close_date
FROM
org_acct_periods per
, fnd_lookup_values flv
, mtl_parameters par
, hr_all_organization_units org1
, hr_all_organization_units_tl otl
, hr_organization_information org2
, hr_organization_information org3
, hr_operating_units opu
WHERE 1=1
AND flv.lookup_type(+) = 'MTL_ACCT_PERIOD_STATUS'
AND flv.enabled_flag(+) = 'Y'
AND per.organization_id = par.organization_id
AND flv.lookup_code(+) = decode(nvl(per.period_close_date,sysdate), per.period_close_date, decode(per.open_flag, 'N', decode(summarized_flag,'N',65,66), 'Y', 4, 'P', 2, 4), 3 )
AND flv.language = 'US'
AND upper(flv.meaning) != 'CLOSED'
AND per.organization_id = org1.organization_id
AND org1.organization_id = otl.organization_id
AND org1.organization_id = org2.organization_id
AND org1.organization_id = org3.organization_id
AND org2.org_information_context = 'Accounting Information'
AND org3.org_information_context = 'CLASS'
AND org3.org_information1 = 'INV'
AND org3.org_information2 = 'Y'
AND org2.org_information3 = opu.organization_id
AND otl.language = 'US'
ORDER BY opu.name, per.organization_id, per.period_year desc, per.period_num desc

INV Item Category API

Following APIs can be used to Create/Update/Delete Category Combinations

INV_ITEM_CATEGORY_PUB.CREATE_CATEGORY
(P_API_VERSION IN NUMBER,
P_INIT_MSG_LIST IN VARCHAR2 := FND_API.G_FALSE,
P_COMMIT IN VARCHAR2 := FND_API.G_FALSE,
X_RETURN_STATUS OUT VARCHAR2,
X_ERRORCODE OUT NUMBER,
X_MSG_COUNT OUT NUMBER,
X_MSG_DATA OUT VARCHAR2,
P_CATEGORY_REC IN INV_ITEM_CATEGORY_PUB.CATEGORY_REC_TYPE, X_CATEGORY_ID OUT NUMBER)

INV_ITEM_CATEGORY_PUB.UPDATE_CATEGORY
(P_API_VERSION IN NUMBER,
P_INIT_MSG_LIST IN VARCHAR2 := FND_API.G_FALSE,
P_COMMIT IN VARCHAR2 := FND_API.G_FALSE,
X_RETURN_STATUS OUT VARCHAR2,X_ERRORCODE OUT NUMBER,
X_MSG_COUNT OUT NUMBER,
X_MSG_DATA OUT VARCHAR2,
P_CATEGORY_REC IN INV_ITEM_CATEGORY_PUB.CATEGORY_REC_TYPE)

INV_ITEM_CATEGORY_PUB.DELETE_CATEGORY
(P_API_VERSION IN NUMBER,
P_INIT_MSG_LIST IN VARCHAR2 := FND_API.G_FALSE,
P_COMMIT IN VARCHAR2 := FND_API.G_FALSE,
X_RETURN_STATUS OUT VARCHAR2,
X_ERRORCODE OUT NUMBER,
X_MSG_COUNT OUT NUMBER,
X_MSG_DATA OUT VARCHAR2,
P_CATEGORY_ID IN NUMBER)

Following API is used for assigning a Category to a Category Set. A Category will be available in the list of valid categories for a category set only if it is assigned to the category set. This is a required step for categories if enforce list is checked on
INV_ITEM_CATEGORY_PUB.CREATE_VALID_CATEGORY
(P_API_VERSION IN NUMBER,
P_INIT_MSG_LIST IN VARCHAR2 := FND_API.G_FALSE,
P_COMMIT IN VARCHAR2 := FND_API.G_FALSE,
P_CATEGORY_SET_ID IN NUMBER,
P_CATEGORY_ID IN NUMBER,
P_PARENT_CATEGORY_ID IN NUMBER,
X_RETURN_STATUS OUT VARCHAR2,
X_ERRORCODE OUT NUMBER,
X_MSG_COUNT OUT NUMBER,
X_MSG_DATA OUT VARCHAR2)

Following APIs can be used to Create/Update/Delete Item Category Assignments

INV_ITEM_CATEGORY_PUB.CREATE_CATEGORY_ASSIGNMENT
(P_API_VERSION IN NUMBER,
P_INIT_MSG_LIST IN VARCHAR2 := FND_API.G_FALSE,
P_COMMIT IN VARCHAR2 := FND_API.G_FALSE,
X_RETURN_STATUS OUT VARCHAR2,
X_ERRORCODE OUT NUMBER,
X_MSG_COUNT OUT NUMBER,
X_MSG_DATA OUT VARCHAR2,
P_CATEGORY_ID IN NUMBER,
P_CATEGORY_SET_ID IN NUMBER,
P_INVENTORY_ITEM_ID IN NUMBER,
P_ORGANIZATION_ID IN NUMBER)

INV_ITEM_CATEGORY_PUB.UPDATE_CATEGORY_DESCRIPTION
(P_API_VERSION IN NUMBER,
P_INIT_MSG_LIST IN VARCHAR2 := FND_API.G_FALSE,
P_COMMIT IN VARCHAR2 := FND_API.G_FALSE,
X_RETURN_STATUS OUT VARCHAR2,
X_ERRORCODE OUT NUMBER,
X_MSG_COUNT OUT NUMBER,
X_MSG_DATA OUT VARCHAR2,
P_CATEGORY_ID IN NUMBER,
P_DESCRIPTION IN VARCHAR2)

INV_ITEM_CATEGORY_PUB.DELETE_CATEGORY_ASSIGNMENT
(P_API_VERSION IN NUMBER,
P_INIT_MSG_LIST IN VARCHAR2 := FND_API.G_FALSE,
P_COMMIT IN VARCHAR2 := FND_API.G_FALSE,
X_RETURN_STATUS OUT VARCHAR2,
X_ERRORCODE OUT NUMBER,
X_MSG_COUNT OUT NUMBER,
X_MSG_DATA OUT VARCHAR2,
P_CATEGORY_ID IN NUMBER,
P_CATEGORY_SET_ID IN NUMBER,
P_INVENTORY_ITEM_ID IN NUMBER,
P_ORGANIZATION_ID IN NUMBER)