Query UOM Conversion Rate from PO to Inventory

Hello,

Today i will share about Oracle query that useful for UOM conversion.
I have used this query for show some data’s with UOM conversion from Purchase Order document to Item Master in Inventory module. I have run this query in Oracle E Business Suite R12.

Check them out.


/* Formatted on 5/13/2014 5:56:47 PM (QP5 v5.163.1008.3004)            *
* Purpose : show conversion rate from UOM in purchase order document   *
*               to UOM in mtl_system_items_b                           */

SELECT plla.ROWID l_rowid_plla,
       msib.segment1 item_code,
       msib.description item_description,
       pha.segment1 quotation_no,
       (SELECT aps.vendor_name
          FROM ap_suppliers aps
         WHERE aps.vendor_id = pha.vendor_id)
          supplier,
       (SELECT mcb.segment1
          FROM mtl_categories_b mcb
         WHERE EXISTS
                  (SELECT 'x'
                     FROM mtl_item_categories mic
                    WHERE     mic.inventory_item_id = pla.item_id
                          AND mic.organization_id = msib.organization_id
                          AND mcb.category_id = mic.category_id))
       category_item,
       pla.unit_price unit_price,
       pla.unit_price hna,
       pha.attribute1 ppn_flag,
       plla.start_date effective_from,
       plla.end_date effective_to,
       pha.org_id org_id,
       plla.po_header_id,
       plla.po_line_id,
       plla.line_location_id,
       pla.unit_meas_lookup_code quotation_uom,
       msib.primary_unit_of_measure item_master_uom,
       CASE
          WHEN pla.unit_meas_lookup_code = msib.primary_unit_of_measure 
          THEN
             1
          WHEN mtl1.uom_class != mtl2.uom_class --conversion for uom class level
          THEN
             (SELECT conversion_rate
                FROM mtl_uom_class_conversions
               WHERE     to_uom_class = mtl1.uom_class
                     AND from_uom_class = mtl2.uom_class
                     AND inventory_item_id = pla.item_id)
          WHEN UPPER (mtl1.uom_class) = 'BERAT'                 --conversion for uom class spesific (in this case, class name = BERAT)
                         AND UPPER (mtl2.uom_class) = 'BERAT'
          THEN
                         CASE
                            WHEN pla.unit_meas_lookup_code = 'GRAM'
                                 AND msib.primary_unit_of_measure = 'MILLIGRAM'
                            THEN
                               1000
                            WHEN pla.unit_meas_lookup_code = 'KG'
                                 AND msib.primary_unit_of_measure = 'MILLIGRAM'
                            THEN
                               1000000
                         END
          ELSE
             (SELECT conversion_rate
                FROM mtl_uom_conversions
               WHERE unit_of_measure = pla.unit_meas_lookup_code
                     AND ROWNUM = 1)
       END conversion
  FROM po_headers_all pha,
       po_lines_all pla,
       po_line_locations_all plla,
       mtl_system_items_b msib,
       mtl_units_of_measure_tl mtl1,
       mtl_units_of_measure_tl mtl2
 WHERE     pha.po_header_id = pla.po_header_id
       AND pla.po_header_id = plla.po_header_id
       AND pla.po_line_id = plla.po_line_id
       AND plla.ship_to_organization_id = msib.organization_id
       AND pla.item_id = msib.inventory_item_id
       AND pha.type_lookup_code = 'QUOTATION'
       AND pla.unit_meas_lookup_code = mtl1.unit_of_measure
       AND msib.primary_unit_of_measure = mtl2.unit_of_measure

Yup, i hope this useful for you too🙂
Regards.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s