Quick and overly simple script to determine status of GL periods. The example looks for period names ending in ’11’ (ie, from 2011). SELECT gps.period_num, gps.period_name, gps.set_of_books_id, gsob.period_set_name, DECODE (gps.closing_status, ‘O’, ‘Open’, ‘C’, ‘Closed’, ‘F’, ‘Future’, ‘N’, ‘Never’) status FROM gl_period_statuses gps, gl_sets_of_books gsob where gps.period_name like ‘%11’ and gps.application_id = 101 and gsob.set_of_books_id = […]
While poking around on the My Oracle Support site (formerly Metalink), I ran across a script called WMS_health_check.sql (document 1086783.1) that contains 30 queries to find certain kinds of data corruption in Oracle Apps. Most of the checks are for WMS, but there are also several relating to Order Management, Shipping Execution, and Inventory/Purchasing/Receiving. Some of […]
I recently needed to look up the order number associated with a delivery_detail_id, so that I could run diagnostics on the sales order. Here’s a quick script that I whipped up to find the info: select unique wdd.source_header_number order_number, wdd.source_header_id, wdd.source_header_type_name order_type, wdd.source_line_id line_id, msi.segment1 item from wsh_delivery_details wdd, mtl_system_items_b msi where wdd.delivery_detail_id in ( […]
Just a quick SQL script that I wrote to check for concurrent requests that failed in the past 24 hours. The script is for E-Business Suite 220.127.116.11; it may or may not work on other versions of Oracle Apps.