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

Queries to verify Oracle configuration

By
Jan 5, 2012
No Comments

I wrote up this fairly basic cheat sheet for myself several years ago. At the time, I was not very familiar with the Oracle database, and was working with a DBA whom we suspected was not setting things up correctly or even consistently. So, I put together a set of queries to verify basic parameters […]

Using SQL*Loader to get around ORA-01795

By
Sep 13, 2011
No Comments

So one of your higher-ups just sent you an Excel spreadsheet with tens of thousands of items, and he or she wants you to query Oracle and get the results right away to resolve the crisis of the day.  Unfortunately, trying to query against this huge list of values gets you this fun error: ORA-01795: […]

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

Oracle and TSM for Databases: Part 2

By
Jun 30, 2011
4 Comments

In the first installment of this series, I discussed some of the basic concepts in using Tivoli Storage Manager for Databases to backup and recover Oracle databases.  In this installment, we’ll look at how to keep TSM and Oracle in sync. When backing up a typical filespace, the TSM backup-archive (BA) client compares what files […]

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.

Load More