Item Regulatory Interface
Procedure: Item Regulatory Interface Objective The purpose of this document is to outline the process for loading and updating Item Regulatory Information into Oracle using an API, based on data…
Read MoreItem Where-Used Analysis Report (Excel – PL/SQL)
Introduction: This blog has the SQL query that can be used for Item where used Report in Oracle Apps R12. Cause of the issue: Business wants a PLSQL excel based report…
Read MoreQuery 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 MoreQuery 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 MoreUpdating 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 MoreDelete 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 MoreInventory 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 MoreOracle 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 MoreQuery 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 MoreUpdate 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