Inventory

Query to get Shipping and Inventory Material Transactions in fusion.

SELECT dha.source_order_number, dha.order_number, dfla.status_code, dfla.source_line_number   FROM  fusion.doo_headers_all dha, fusion.doo_fulfill_lines_all dfla, fusion.wsh_delivery_assignments wda, fusion.wsh_new_deliveries wnd, fusion.wsh_delivery_details wdd, fusion.inv_material_txns imt  WHERE dha.header_id=dfla.header_id    AND dfla.fulfill_line_id=wdd.source_shipment_id    AND wdd.delivery_detail_id=wda.delivery_detail_id    AND wda.delivery_id=wnd.delivery_id(+)…

Read More

Query to get Inventory on hand quantity details in fusion.

SELECT esi.inventory_item_id, esi.item_number, esi.organization_id, inv.organization_code, esi.enabled_flag, esi.end_date_active, ohq.transaction_quantity onhand_qty   FROM fusion.egp_system_items_b esi, fusion.inv_org_parameters inv, fusion.inv_onhand_quantities_detail ohq  WHERE  inv.organization_id=esi.organization_id    AND inv.organization_id=ohq.organization_id    AND esi.inventory_item_id=ohq.inventory_item_id

Read More

Updating bulk records pending under Inventory Transactions using Oracle Visual Builder Add-in

Introduction: The Oracle Visual Builder Add-in for Excel connects Excel spreadsheets with REST services, enabling you to retrieve, analyze, and edit business data directly from the service. You can download…

Read More

Delete ITEM Category in EBS using API

           Delete ITEM Category in EBS using API. Introduction: To delete an Item Category in Oracle E-Business Suite (EBS) using an API, you’ll typically interact with an endpoint specifically designed for deleting or managing item categories. Prepare the Request: The request will generally involve sending the unique identifier of the item category you wish to delete. Script Code: DECLARE l_return_status VARCHAR2(80); l_error_code    NUMBER; l_msg_count     NUMBER; l_msg_data      VARCHAR2(80); l_category_id   NUMBER; BEGIN SELECT mcb.CATEGORY_ID INTO l_category_id FROM mtl_categories_b mcb WHERE mcb.SEGMENT1=’RED’ AND mcb.STRUCTURE_ID = (SELECT mcs_b.STRUCTURE_ID FROM mtl_category_sets_b mcs_b WHERE mcs_b.CATEGORY_SET_ID = (SELECT mcs_tl.CATEGORY_SET_ID FROM mtl_category_sets_tl mcs_tl WHERE CATEGORY_SET_NAME =’INV_COLORS_SET’ ) );   INV_ITEM_CATEGORY_PUB.Delete_Category ( p_api_version     => 1.0,…

Read More

Inventory Item Product Variant Setups Creation

This document provides detailed steps on how to create the Style Item and SKU Items from UI. The Style items can be used to group similar items (called SKUs or…

Read More

Oracle EBS – Query to get Price list details for 5 Years

 Execute the below query into EBS database. SELECT line, empty, pricing, item, from_date, TO_DATE, total, uom, attirubute, org FROM (SELECT ‘NL’ line, ” empty, ‘INTERCOMPANY PRICING’ pricing, item.segment1 item,…

Read More

Query to fetch Routing Details

Introduction: Routing is a set of operations that will be performed in sequence to manufacture an assembly Item. The below query will give the operation sequence details.   Query to fetch…

Read More

Update Item Using SOAP Webservice – Oracle Fusion

Introduction: This blog has the SOAP Webservice details that can be used to update the item data in Oracle Cloud application. Cause of the issue: Business wants to integrate their…

Read More

Item Import Using SOAP Webservice – Oracle Fusion

Introduction: This blog has the SOAP Webservice details that can be used to import the item data into Oracle Cloud application.   Cause of the issue: Business wants to integrate…

Read More

Item Ranging – Oracle Retail Merchandising System Module

  Item Maintenance RMS is responsible for the creation and maintenance of all items. RMS uses a flexible data hierarchy for an item, with levels that allow you to model…

Read More