Query to Extract Inactive Items from Activity BOM

-

Preventive Maintenance Schedule gives Error for inactive BOM lines when Work Orders are implemented. The following query can be used to identify and extract Inactive Items in BOM Lines for Activities.

SELECT msi.concatenated_segments, bic.item_num, bic.operation_seq_num,
       msi2.concatenated_segments, bic.component_quantity
  FROM bom_bill_of_materials_v bbm,
       mtl_system_items_kfv msi,
       mtl_system_items_kfv msi2,
       bom_inventory_components_v bic
 WHERE bbm.assembly_item_id = msi.inventory_item_id
   AND bbm.bill_sequence_id = bic.bill_sequence_id
   AND msi2.inventory_item_id = bic.component_item_id
   AND msi.segment1 LIKE 'AB'
   AND msi.organization_id = XY
   AND bbm.organization_id = XY
   AND msi2.organization_id = XY
   AND msi2.inventory_item_status_code = 'Inactive'

 Replace "AB" with your relevant SEGMENT 1 of Activity.
Replace "XY" with your relevant Org ID.

Comments

Post a Comment

Popular posts from this blog

Invoice Price Variance Process Accounting

-
Image
Invoice Price Variance Process Accounting: Note: Below Working and results are only applicable where Purchase Orders and Invoice are not routed through LCM Module. For LCM Enabled Items IPVs are routed and transferred to Inventory directly from LCM. Invoice Price Variance is variance between the invoice price and the purchase price for all inventory and work in process related invoice distributions. Payables records invoice price variances when the invoices are matched, approved, and posted. This Article covers the detail Accounting for Oracle Invoice Price Variance Process and also its effect on item Unit Cost in Inventory. Let's Put up a scenario here. We will be receiving an item in Inventory which has currently no On Hand Balance or Unit Cost in Inventory. We Booked a Purchase Order for Quantity 100 at Unit Price of BDT 10 . We did the Receipt of the booked Quantity 100 at 01-August-2014 and receipt delivery was made on same date with all quantity acce...
Read more

Query to Find Out Move Order Number for Work Order with Transacted Quantity

-
Query to Find Out Move Order Number for Work Order with Transacted Quantity: There could be scenarios where for some reason you need to know Move Order number for Work Order Material Line which has be transacted. Once quantity is transacted, no record exists for that move order number to be found. The below query can be used to find out All Move Order Numbers generated for that relevant Work Order. SELECT a.REQUEST_NUMBER, mmt.TRANSACTION_SOURCE_ID, mmt.TRANSACTION_SOURCE_NAME, mmt.* FROM mtl_material_transactions mmt, mtl_txn_request_lines b, mtl_txn_request_headers a, wip_entities w WHERE --mmt.transaction_id = 55356941 --AND mmt.move_order_line_id = b.line_id AND b.header_id = a.header_id and mmt.TRANSACTION_SOURCE_ID = w.WIP_ENTITY_ID and w.WIP_ENTITY_NAME = ' WO30683'       "Change Work Order Number to Relevant Work Order"  Special Thanks to Waqas Munir (PwC Pakistan)  
Read more