All the SQL queries and content of this blog are based on my experience and as per requirements of my projects and may not be tailor made for your need. One may need to change as per their requirements.
Query to get PO & Receipt Data: select aps.segment1 "Supplier Number", poh.segment1 "Purchase Order Number", poh.AUTHORIZATION_STATUS "Purchase Order Status", Null "Purchase Order Release Number", poh.START_DATE "Purchase Order Date", papf.full_name "Buyer", msi.segment1 "Purchase Order Item Number", pll.closed_code "Purchase Order Item Status", pol.PURCHASE_BASIS "Purchase Order Item Type", msi.segment1 "Location Part Number", msi.description "Location Part Description", pol.VENDOR_PRODUCT_NUM "Supplier Part Number", POL.QUANTITY "Item Order Quantity", pol.unit_price "Location Item Price", poh.CURRENCY_CODE "Location Currency Code", pol.unit_price "Supplier Item Price", aps.PAYMENT_CURRENCY_CODE "Supplier Currency Code", msi.PRIMARY_UNIT_OF_MEASURE "Inventory Unit of Me
Query to get safety stock Quantity of inventory items: select msib.segment1,c.INVENTORY_ITEM_ID,SAFETY_STOCK_QUANTITY from mtl_safety_stocks c, mtl_system_items_b msib where c.INVENTORY_ITEM_ID = msib.INVENTORY_ITEM_ID and c.ORGANIZATION_ID = msib.ORGANIZATION_ID and c.SAFETY_STOCK_QUANTITY <> 0 and msib.ORGANIZATION_ID = 12 and msib.segment1 in ('98765'); Query to get the inventory item vendor and costs : select msi.inventory_item_id "Item ID", msi.segment1 "Part Number", msi.description "Part Description - Internal", decode (msi.item_type,'XX_SUPPORT_ITEM','SUPPORT ITEM' ,'XX_BULK_ITEM','BULK ITEM' ,'P','PURCHASED ITEM' ,'SA','SUBASSEMBLY' ,'PH','PHANTOM ITEM'
Comments
Post a Comment