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_to_Address_Line_1",          apsite_rmt.ADDRESS_LINE2 "Supp_Remit_to_Address_Line_2",          apsite_rmt.ADDRESS_LINE3 "Supp_Remit_to_Address_Line_3",    

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'      

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'                                                     , 2, 'BUY'                        ,msib2.PLANNING_MAKE_BUY_CODE ) MAKE_BUY                         , decode (msib2.item_type,'SUPPORT_ITEM','SUPPORT ITEM'                                        ,'BULK_ITEM','BULK ITEM'            

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]