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

Getting under the hood with DB2 for TSM Admins

By
Dec 21, 2011
No Comments

Over at the TSMADmin blog, Chad Small recently posted “DB2 Doesn’t Make a Difference.”  Chad was concerned that he was not seeing a performance boost in his database queries after upgrading from TSM 5.5 to 6.x. As you know, one of the biggest changes in TSM 6.x is that it is now backed by DB2. […]

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

TSM 6.2 shut down, ADM6017E in db2diag.log

By
Aug 11, 2011
No Comments

This morning, I noticed that one of my TSM 6.2 servers had stopped running over night.  No DB2 processes or dsmserv were running. I checked the TSM logs, but only found errors from the dsmadmc client: 08/11/11 09:08:52 ANS5216E Could not establish a TCP/IP connection with address ‘TSM1.LANIGERA.COM:1500’. The TCP/IP error is ‘Connection refused’ (errno […]

TSM 6.2.3 released

By
Aug 5, 2011
2 Comments

IBM has released Tivoli Storage Manager v6.2 Fix Pack 2 (6.2.3). Looks like a lot of bugfixes, in particular a DB2 reorg bug. As usual, I’ll let everyone else go first and see what breaks.  Thanks, dudes!

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

Load More