Lot Genealogy Report
Introduction: This blog has the SQL query that retrieves the complete lot genealogy, detailing the full lifecycle of product creation. Cause of the issue: Business wants a report that contains…
Read MoreQuality Test Sample Throughput Report
Introduction: This blog has the SQL query that retrieves all Quality test samples along with their corresponding results. Cause of the issue: Business wants a report that contains the details…
Read MoreAPI to Update Item Status in Oracle E-Business Suite
API to Update Item Status in Oracle E-Business Suite Introduction In Oracle E-Business Suite (EBS), Item Status controls key aspects of how an item behaves across different modules such as…
Read MoreAPI to Update the Item Categories in Oracle EBS
API to Update the Item Categories in Oracle EBS Introduction In Oracle E-Business Suite (EBS), item categories play a crucial role in organizing and classifying items for inventory, costing, and…
Read MoreQuery to find the historical Quantity for an item
SELECT SUM (target_qty) FROM (SELECT segment1, description, mtl.primary_uom_code, moqv.subinventory_code subinv, moqv.inventory_item_id item_id, SUM (transaction_quantity) target_qty FROM apps.mtl_onhand_qty_cost_v moqv, apps.mtl_system_items_vl mtl WHERE moqv.organization_id = :p_organization_id AND moqv.inventory_item_id = :p_inventory_item_id AND moqv.inventory_item_id…
Read MorePre-requisite for the Sales Orders purging
Introduction In the oracle EBS the concept of Purging and before purging the order management related tables, pre-requisite to be done for the smooth purging process. The below script will…
Read MoreOPM Uncompleted batches query
Introduction/ Issue: This SQL query is designed to extract Batch Header and Material Details from Oracle Process Manufacturing (OPM). It consolidates information such as company code, plant, batch number, recipe,…
Read MoreShipping Purge (Purge SRS) Step by Step process
Step 1: Deploy Oracle SR Package → Description: Apply the Oracle-provided script Compile the package /*===========================================================================+ | Copyright (c) 1999, 2025 Oracle Corporation | | …
Read MoreShipping Process Archival – Step-by-Step
Step 1: Take Table Counts Before Purging Before performing any purge, get the record counts of the affected tables to ensure you can verify the archival later. Affected Tables: WSH_TRIPS WSH_TRIP_STOPS WSH_DELIVERY_LEGS WSH_NEW_DELIVERIES WSH_DELIVERY_DETAILS WSH_DELIVERY_ASSIGNMENTS SQL statement: SELECT COUNT(*) FROM WSH_TRIPS; SELECT COUNT(*) FROM WSH_TRIP_STOPS; SELECT COUNT(*) FROM WSH_DELIVERY_LEGS; SELECT COUNT(*) FROM WSH_NEW_DELIVERIES; SELECT COUNT(*) FROM WSH_DELIVERY_DETAILS; SELECT COUNT(*) FROM WSH_DELIVERY_ASSIGNMENTS; Step 2: Configure Archival Setup Insert configuration metadata into a custom configuration table XXXX ARCHIVE_CONFIG_TBL. SQL statement: INSERT INTO XXXX_ARCHIVE_CONFIG_TBL (SNO, MODULE_SHORT_NAME, MODULE_LONG_NAME, ARCHIVE_REQUIRED, ORIG_TABLE_NAME, ARCHIVE_TABLE_NAME, LAST_ARCHIVED_ON) VALUES (XXXX_ARCHIVE_SEQ.NEXTVAL, ‘WSH’, ‘SHIPPING’, ‘Y’, ‘WSH_TRIP_STOPS’, ‘XXXX_WSH_TRIP_STOPS_ARCHIVE’, SYSDATE) (SNO, MODULE_SHORT_NAME, MODULE_LONG_NAME, ARCHIVE_REQUIRED, ORIG_TABLE_NAME, ARCHIVE_TABLE_NAME, LAST_ARCHIVED_ON) VALUES (XXXX_ARCHIVE_SEQ.NEXTVAL, ‘WSH’, ‘SHIPPING’, ‘Y’, ‘WSH_TRIPS’, ‘XXXX_WSH_TRIPS_ARCHIVE’, SYSDATE) (SNO, MODULE_SHORT_NAME, MODULE_LONG_NAME, ARCHIVE_REQUIRED, ORIG_TABLE_NAME, ARCHIVE_TABLE_NAME, LAST_ARCHIVED_ON) VALUES (XXXX_ARCHIVE_SEQ.NEXTVAL, ‘WSH’, ‘SHIPPING’, ‘Y’, ‘WSH_DELIVERY_LEGS’, ‘XXXX_WSH_’WSH_DELIVERY_LEGS _ARCHIVE’, SYSDATE) (SNO, MODULE_SHORT_NAME, MODULE_LONG_NAME, ARCHIVE_REQUIRED, ORIG_TABLE_NAME, ARCHIVE_TABLE_NAME, LAST_ARCHIVED_ON) VALUES (XXXX_ARCHIVE_SEQ.NEXTVAL, ‘WSH’, ‘SHIPPING’, ‘Y’, ‘WSH_DELIVERY_DETAILS’, ‘XXXX_WSH_’WSH_DELIVERY_DETAILS’_ARCHIVE’, SYSDATE)…
Read MoreFrozen Inventory Value Report–All Types in Oracle Apps R12.
Introduction: This blog has the SQL query and View query that can be used for Frozen Inventory Value Report–All Types in Oracle Apps R12. Cause of the issue: Business wants a PLSQL-based…
Read More