Oracle EBS11i: IC_LOTS_MST and PO_VEND_MST

EBS11i: IC_LOTS_MST and PO_VEND_MST

Today I have been asked to extract data from 'LOT/SUBLOT  Function' from OPM Inventory responsibility e.g.

I have to show Item_no, Lot, Vendor and Creation_Date where onhand quantity of that particular item must exists.

IC_LOTS_MST and PO_VEND_MST

I wrote query:

select item_no,lot_no,ilm.vendor_lot_no,

       (select vendor_no

        from po_vend_mst pv

       where pv.VENDOR_ID= shipvend_id)vendor_no,

       ilm.LOT_CREATED

  from ic_item_mst iim,

       ic_loct_inv ili,

       ic_lots_mst ilm

 where iim.ITEM_ID = ili.ITEM_ID

   and iim.ITEM_ID = ilm.ITEM_ID

   and ilm.LOT_ID = ili.LOT_ID

   and ili.loct_onhand <> 0 -- 

   and iim.ITEM_NO = '15000009'

Main challenge was the vendor number actually in the following lov you can see the vendor_id (which is not from PO_VENDORS table) when I join this vendor_id with PO_VENDORS table it returns nothing. What I figured out from the LOV was that under 'Vendor' heading, vendor_id and VENDOR_SITE_CODE is concatenated and I can find out these values from PO_VENDOR_SITES_ALL but I was unable to find the table where concatenated values already exists. So I googled it and asked from other champs but no cure so I started checking value sets individually and found that this LOV is showing data from PO_VEND_MST table. So I joined the "shipvend_id" with PO_VEND_MST table and problem solved. In addition we can further join the PO_VEND_MST with PO_VENDORS.

IC_LOTS_MST and PO_VEND_MST

Cheers :).

Your feedback/comment is quite important for improving this blog so feel free to leave your comments & suggestions