ORACLE ERP & APPS TECH

Query to get Supplier data from Oracle R12 SELECT   aps.segment1 "SupplierNum",          assa.vendor_site_code "Address Book Identifier ",          aps.vendor_name "Supplier Company Name ",          --Hou.name "Operating Unit Name",          Assa.address_line1 "Supplier Address Line1",          Assa.address_line2 "Supplier Address Line2",          assa.address_line3 "Supplier Address Line3",          assa.address_line4 "Supplier Address Line4",          assa.city "Supplier city",          assa.state || assa.PROVINCE "Supplier State/Province",          assa.country "Supp_country_Code",          assa.zip "Supp_postal_Code",          apsite_rmt.ADDRESS_LINE1 "Supp_Remit_...

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','PURCHAS...

Query to get Tree structure of the indented BOM along with Vendor for the Purchased items and Item type SELECT DISTINCT LPAD (' ', LEVEL * 2) || LEVEL ORDER_LEVEL               , LPAD (' ', LEVEL * 1) || msib.segment1 ASSEMBLY_ITEM               , LPAD (' ', LEVEL * 4) || msib2.segment1 AS COMPONENT_ITEM               , msib2.description COMPONENT_ITEM_DESCRIPTION               , bic.bill_sequence_id c, bom.bill_sequence_id b                           , msib.description ASSEMBLY_DESCRIPTION               , msib.inventory_item_status_code ASSEMBLY_ITEM_STATUS                 , decode (msib2.PLANNING_MAKE_BUY_CODE, 1, 'MAKE'                       ...

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...

To update a data base column to a NULL value, you must enter the following values: Column Data Type        Required value to create NULL column For Char columns            char (12) For Date columns            TO_DATE (’1’,’j’) For Number columns       9.99E125 Ex: To update number column type to NULL Update po_headers set agent_id = 9.99E125 where agent_id = 101 and org_id = 100; Reference: How to Enter a Null Value (Clear a Value) For Any Column in Any BOM Interface Table [ID 264198.1]