Find workflows not processed by mailer after ORA-29532

By
May 18, 2012
One Comment

I recently ran into a problem where inbound Workflow approvals were being “stuck” in processing. The notification messages were being delivered to the instance’s INBOX and transferred into the PROCESS folder, but were not being processed. The only error message was a notice sent to the sysadmin. In the error notification was an ORA-29532: Event […]

Open GL periods in Oracle Apps 11i

By
Jan 23, 2012
No Comments

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 = […]

WMS Health Check scripts for Oracle Apps 11i and R12

By
Aug 24, 2011
4 Comments

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 […]

Determine order number from delivery_detail_id in Oracle Apps 11i

By
Aug 24, 2011
No Comments

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 ( […]

Script to count LPNs by context in Oracle Apps 11i

By
Aug 4, 2011
No Comments

Need to count how many LPNs are in each context for a given organization in Oracle E-Business Suite 11i? Here’s a quick script: select count(*), lpn_context, decode(lpn_context, ‘1’, ‘Resides in Inventory’, ‘2’, ‘Resides in WIP’, ‘3’, ‘Resides in Receiving’, ‘4’, ‘Issued out of Stores’, ‘5’, ‘Defined but not used’, ‘6’, ‘Resides in Intransit’, ‘7’, ‘Resides […]

Script to check for scheduled concurrent requests in Oracle Apps 11i

By
Jul 6, 2011
No Comments

Just a quick script that I wrote to report on concurrent requests that are scheduled to run in the future: select fcr.request_id, fcr.parent_request_id, fu.user_name requestor, to_char(fcr.requested_start_date, ‘MON-DD-YYYY HH24:MM:SS’) START_DATE, fr.responsibility_key responsibility, fcp.concurrent_program_name, fcpt.user_concurrent_program_name, decode(fcr.status_code, ‘A’, ‘Waiting’, ‘B’, ‘Resuming’, ‘C’, ‘Normal’, ‘D’, ‘Cancelled’, ‘E’, ‘Error’, ‘F’, ‘Scheduled’, ‘G’, ‘Warning’, ‘H’, ‘On Hold’, ‘I’, ‘Normal’, ‘M’, ‘No […]

Check for failed concurrent requests in Oracle EBS 11i

By
Jun 30, 2011
No Comments

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 11.5.10.2; it may or may not work on other versions of Oracle Apps.

Disconnecting users from Oracle MWA

By
Jan 26, 2011
No Comments

These instructions are for E-Business Suite 11.5.10 CU2; they may or may not work in other versions. For an MWA telnet server, or Listener, at a given port number, there is a command channel listening at the next highest port number.  For example, if the Listener is on port 21001, the command channel is on […]