Receiving Transaction Errors (rcv_transactions_interface)

We had some receiving errors and I learnt an interesting point that the detailed errors can be found in po_interface_errors.


select * from PO_INTERFACE_ERRORS where batch_id
in (select group_id from rcv_transactions_interface)

Oracle Shipping Exceptions.

There might be scenarios when Pick Release is called you are not sure where to look into. WSH_EXCEPTIONS does the job of tracking the exceptions.

select * from WSH_EXCEPTIONS

Some More Date functions.

What day is today in SQL?
select to_char(sysdate,'DAY') from dual;

Extending the above to other useful stuff:

First Day of the Month:
select to_char(trunc(sysdate,'MM'),'DAY' ) from dual;
Ex: WEDNESDAY

Last Day of the Month:
select to_char(last_day(sysdate),'DAY') from dual;
Ex: MONDAY

Last Date of the month :
select last_day(sysdate) from dual
Ex:31-Aug-2011

Add time to a given date:
select to_char(trunc(last_day(sysdate))+13/12,'DD-MON-RRRR HH24:MI:SS') from dual

Difference between two dates in hourss/minutes in Oracle.

We might need to find the difference between two dates columns in hours/minutes in many cases. Like trying to find how long some program ran. One of the easiest way I found is

SELECT to_number( to_char(to_date('1','J') +
(date1 - date2), 'J') - 1) days,
to_char(to_date('00:00:00','HH24:MI:SS') +
(date1 - date2), 'HH24:MI:SS') time
FROM mytable;

Progress Order Workflow Automatically.

In any implementation using Order Management Order Workflow errors are a common thing. And once you customize the workflow the pain is even more.

Sometimes the workflow gets struck of the errors might be momentary. Something like some service not available at that point of time.

Oracle provides a Automatic Retry program which does the job for us.

Program Name : Retry Activities in Error
Item Type : In most cases this happens to be 'OM Order Line' or else select accordingly.
Mode : Preview/Execute ,select Execute to commit/action the retry.

Get Timestamp value for Date column in SQL Developer.

Most of the times when we look at Oracle tables from SQL Developer we find that the date column not showing the timestamp. A simple way to get the timestamp is using CAST function.

select cast(last_update_date as timestamp) from table_name.

Also there is a mroe easier way to see the timestamp in the settings of SQL Developer.

Tools -> Preferences -> Database -> NLS Parameters
Change Date Format to DD-MON-RRRR HH24:MI:SS

:)

SQL Loader Failing to load more than 255 characters.

This was one of my observations.

We had a sample dataload file defined as :

,mesg_type CHAR "ltrim(rtrim(:mesg_type))"
,mesg_priority CHAR "ltrim(rtrim(:mesg_priority))"
,mesg_description CHAR "ltrim(rtrim(:mesg_description))"


The program was failing to load the data when the length of the field is more than 254 characters.

Issue:
NOTE: The default data type in SQL*Loader is CHAR(255). To load character fields longer than 255 characters, code the type and length in your control file. By doing this, Oracle will allocate a big enough buffer to hold the entire column, thus eliminating potential "Field in data file exceeds maximum length" errors. Example:

Fix:
,mesg_description CHAR(4000) "ltrim(rtrim(:mesg_description))"