Inventory
One of the most common business requirements in Inventory Management is the ability to view on-hand inventory at a detailed level- specifically by Subinventory, Locator, and Lot. This level of visibility helps business users clearly understand current stock positions and make informed operational decisions. Inventory is not always immediately available for use. While some stock … Read more
Receiving can be a one-step or two-steps or three steps process and the process is governed by the Receipt Routing Method in the Purchase Order. Receipt Routing Methods are Inventory Purchase Orders are received first into the warehouse/receiving dock, then put away into the stock. Here is the SQL Query to get all the PO … Read more
A freight carrier is a commercial company that transports shipment/deliveries to and from customers, suppliers and internal organizations. Freight Carriers are setup commonly for the Order Management, Inventory and Procurement. The freight carriers are setup with their model of transport like Air, Ground, Ocean, Service levels like 1 Day, 2 day and Overnight etc., and … Read more
Here is the SQL Query to get Back2Back Sales Orders and their corresponding Supply Order details. SELECT sh.SUPPLY_ORDER_REFERENCE_NUMBER “Sales Order”,sl.SUPPLY_ORDER_REF_LINE_NUMBER “SOLine_Num”,sh.SUPPLY_ORDER_NUMBER,sh.status_code hdr_status,sh.SUPPLY_ORDER_SOURCE,sl.LINE_NUMBER “Supply_Lines”,sl.EXEC_SYSTEM_ITEM_NUMBER “Item”,sl.quantity Requested_Qty,stl.status_code status,–stl.quantity tl_qty , stl.available_quantity avail_qty ,sl.need_by_date Requested_Delivery_Date,sl.supply_type supply_type,sl.DESTINATION_ORGANIZATION_CODE Supply_Destination, –sl.DESTINATION_BU_ID , sl.DESTINATION_ORGANIZATION_ID ,sl.MOVEMENT_REQUEST_FLAG,sl.contract_manufacturing_flag CM_FLAG,sl.back_to_back_flag B2B_FLAG,sl.CONFIG_ITEM_FLAG,sl.OUTSIDE_PROCESSING_FLAG OSP_FLAG,DECODE (stld.document_type,‘ORA_PR’, ‘Purchase Requisition’,‘ORA_PO’, ‘Purchase Order’,‘ORA_RSV’, ‘Reservation’,‘ORA_RCPT’, ‘Receipt’,‘ORA_TO’, ‘Transfer Order’,‘ORA_WO’, ‘Work Order’,‘ORA_SH’, ‘Shipment’) “Document Type”,stld.document_number … Read more
Here is the SQL Query to get all the open shipment lines and their statuses. This will help shipping manager/shipping user to find out all the open shipment lines, including awaiting to be released, Released to Warehouse, but not yet picked, Staged and Shipped etc., We can get the same info from Manage Shipment lines … Read more
Business has a common requirement to generate Shipping Documents (Packing List, Bill of Lading and Commercial Invoice) for the drop ship sales order from Oracle and share with Supplier(s), So supplier can use the selling organization’s shipping documents when shipping the products/items. This wasn’t feasible in the Oracle E-Business Suite world and the documentation was … Read more
Transfer Orders are used for intercompany and intracompany inventory transfers. Internally Receipt Routing is defaulted and stored while shipping the transfer order from the sourcing organization. Here is the Receipt Routing Hierarchy for Transfer Orders receiving. Receipt Routing from the Item at Organization level takes highest precedence. If the receipt routing is blank at the … Read more
We have a common requirement from most of the businesses to get inventory on-hand balances and available to reserve quantities by item and sub-inventory for their operation(s). This will help them to understand the current inventory to fulfill the sales order(s)/work order(s) demand, commiting the delivery dates with the customers and planning to source the … Read more
Transfers Orders are used in Oracle Fusion SCM for the intercompany material transfers, similar to the Internal Requisition/Internal Sales Order process in the Oracle EBS. No cost markup for the material transfers within the BU. Across the Business Units/Legal entities, Transfer Pricing policies can be setup based on the Cost Plus markup / List Less … Read more
Here is the Item extract SQL which i used for the item data analysis and item updates thru FBDI. SELECT ESI.INVENTORY_ITEM_ID,ESI.ORGANIZATION_ID,ESI.ITEM_NUMBER,IOP.ORGANIZATION_CODE INV_ORG,— EIC.ITEM_CLASS_ID,EIC.ITEM_CLASS_NAME ITEM_CLASS,ESI.ITEM_TYPE ITEM_TYPE,(SELECT MEANINGFROM FND_LOOKUP_VALUES_VLWHERE LOOKUP_TYPE = ‘EGP_ITEM_TYPE’AND lookup_code = ESI.ITEM_TYPE) User_Item_type,ESI.PRIMARY_UOM_CODE PRIMARY_UNIT_OF_MEASURE,— ESI.WIP_SUPPLY_TYPE BUILD_IN_WIP,ESI.BUILD_IN_WIP_FLAG BUILD_IN_WIP,ESI.COSTING_ENABLED_FLAG COSTING_ENABLED,ESI.INVENTORY_ASSET_FLAG INVENTORY_ASSET_VALUE,DECODE (ESI.WIP_SUPPLY_TYPE,1, ‘Push’,2, ‘Assembly Pull’,3, ‘Operation Pull’,4, ‘Bulk’,5, ‘Vendor’,6, ‘Phantom’) PIM_SUPPLY_TYPE,(SELECT MEANINGFROM FND_LOOKUP_VALUES_VLWHERE LOOKUP_TYPE = ‘EGP_SERIAL_NUMBER_CONTROL_TYPE’AND … Read more