Monday, 1 April 2013

Mass Item Cross References Update

For Mass update on Item cross reference there is no any standard program, which can be done by inserting data into table mtl_cross_references. Which can be perform by below query.

INSERT INTO mtl_cross_references
            (inventory_item_id, organization_id, cross_reference_type,
             cross_reference, last_update_date, last_updated_by,
             creation_date, created_by, description, org_independent_flag
            )
     VALUES (102, NULL, 'Vendor',
             'ssg', SYSDATE, 1370,
             SYSDATE, 1370, 'test by ssg', 'Y'
            )

Mass Item Sub Inventory Assignment

We can Directly populate details into mtl_item_sub_inventories using the following query.

INSERT INTO mtl_item_sub_inventories
                        (
                         inventory_item_id
                       , organization_id
                       , secondary_inventory
                       , last_update_date
                       , last_updated_by
                       , creation_date
                       , created_by
                       , inventory_planning_code
                        )
                 VALUES (
                         l_inventory_item_id
                       , l_organization_id
                       , p_subinventory_code
                       , SYSDATE
                       , fnd_global.user_id
                       , SYSDATE
                       , fnd_global.user_id
                       , 6
                        );

Diagnostic Scripts to analyse "You cannot update this field when " 1) there is on-hand quantity OR 2) transactions exist OR 3) lots exist.

1) Determine the control level of your attributes in Inventory / Setup / Items / Attribute Controls.

2) Discover the organization_id, using the following sql:

select organization_id, organization_code
from mtl_parameters
where organization_code = '&orgcode';

3) Discover the inventory_item_id, using the following sql:

select inventory_item_id, organization_id, segment1, Lot_control_code
from mtl_system_items_b
where segment1 = '&itemname';

4) Check files for OPEN transactions:

select *
from mtl_material_transactions
where inventory_item_id = &item_id
and costed_flag is NOT NULL;

SELECT *
FROM MTL_MATERIAL_TRANSACTIONS_TEMP
where inventory_item_id = &item_id;

SELECT *
FROM MTL_TRANSACTIONS_INTERFACE
where inventory_item_id = &item_id;

SELECT *
FROM MTL_SUPPLY
where ITEM_ID = &item_id;
SEE 11) BELOW if MTL_SUPPLY.


SELECT *
From MTL_DEMAND
where inventory_item_iD = &item_id;

5) Check for all open sales orders:

select h.order_number,l.line_id, l.ordered_item,
l.inventory_item_id, l.promise_date,l.Flow_status_code
from oe_order_lines_all l, oe_order_headers_all h
where inventory_item_id = &itemid
and l.open_flag = 'Y'
and nvl(l.shipping_interfaced_flag,'N') = 'N'
and l.header_id = h.header_id
order by h.order_number;

6) Check for any open delivery details lines:

select wdd.source_header_id,wdd.source_header_number order_number, wdd.inventory_item_id itemid, wdd.source_line_number line_no,
wdd.source_line_id, wdd.delivery_detail_id,
wdd.released_status, ol.FLOW_STATUS_CODE
from wsh_delivery_details wdd, oe_order_lines_all ol
where wdd.source_code = 'OE'
and wdd.inv_interfaced_flag in ('N','P')
and wdd.released_status <> 'D'
and wdd.source_line_id = ol.line_id
and wdd.inventory_item_id = &itemid
order by wdd.source_header_number;

7) Check for on-hand quantities:

select *
from mtl_onhand_quantities_detail
where inventory_item_id = &item
and organization_id = &org;

8) Check for  open cycle count or physical inventory entries:

SELECT ADJUSTMENT_ID,
INVENTORY_ITEM_ID,
SUBINVENTORY_NAME,
LOCATOR_ID,
LOT_NUMBER,
SERIAL_NUMBER,
REVISION,
ADJUSTMENT_QUANTITY
FROM MTL_PHYSICAL_ADJUSTMENTS
WHERE inventory_item_id = &item
AND ORGANIZATION_ID = &org
AND ADJUSTMENT_QUANTITY != 0
AND ( APPROVAL_STATUS NOT IN (2, 3)
OR APPROVAL_STATUS IS NULL )
ORDER BY ADJUSTMENT_QUANTITY;

select *
from mtl_cycle_count_entries
WHERE inventory_item_id = &item
AND ORGANIZATION_ID = &org
and entry_status_code NOT IN (4,5);

9) If changing lot controlled item, check for enabled  lot numbers for the Item / Organization combination:

select *
from mtl_lot_numbers
where inventory_item_id = &item
and organization_id = &org
and ( disable_flag is NULL
or expiration_date > sysdate);

10) Transact pending transactions, reject obsolete counts, issue on-hand quantities, disable lot numbers as needed.

11) If row returned, with supply_type_code = REQ, find associate Requisition number:
      a) Find REQUISITION number
      Select * from po.po_requisition_headers_all
      where requistion_header_id  = &req_header_id_from_mtl_supply;

      b) In application query requisition number returned in segment1 and close the line / requisition as appropriate.

      c) Attempt item update again.