The Active Start date of Party Account DD-MON-YY is either greater than active end date or less than active start date of Instance Party to which it is associated (Doc ID 1506020.1)

Last updated on MARCH 08, 2017

Applies to:

Oracle Install Base - Version 12.1.3 and later
Information in this document applies to any platform.

Symptoms

On : 12.1.3 version, Support Codes

ACTUAL BEHAVIOR
---------------
During the serialized item instance creation, Oracle needs to validate the instance-related account's Start_Date in the procedure CSI_Instance_Parties_Vld_Pvt.Is_Acct_StartDate_Valid().

This procedure was working in Oracle ERP 11i, but been modified in R12, which introduced a code-bug in the following code block:
– Modified date comparison for bug 7333900, ignore difference in seconds
IF (TO_DATE(p_start_date,'DD-MM-YY HH24:MI') < TO_DATE(l_inst_party_start_date,'DD-MM-YY HH24:MI'))
  OR (TO_DATE(p_start_date,'DD-MM-YY HH24:MI') > TO_DATE(SYSDATE,'DD-MM-YY HH24:MI')) THEN
  l_return_value := FALSE;
  IF ( p_stack_err_msg = TRUE ) THEN
  fnd_message.set_name('CSI','CSI_API_INV_ACCT_START_DATE');
  fnd_message.set_token('ACTIVE_START_DATE',p_start_date);
  fnd_msg_pub.ADD;
  END IF;
END IF;

The error is on the second line: (TO_DATE(p_start_date,'DD-MM-YY HH24:MI') > TO_DATE(SYSDATE,'DD-MM-YY HH24:MI'), where the passed dates as below:

p_start_date: '22-DEC-1998 15:32:13'

SYSDATE: '19-SEP-2012 11:44:12'

But, the result is: p_start_date > SYSDATE, which is wrong.


EXPECTED BEHAVIOR
-----------------------
Date should be treated correctly and year should not change to 2098

STEPS
-----------------------
The issue can be reproduced at will with the following steps:
1. the parameter p_start_date is a DATE-formatted, with a date passed-in as '22-DEC-1998 15:32:13'.
2. Oracle uses a TO_DATE() function on this date, which ends up with Year 2098 as below:
SQL> SELECT TO_DATE(TO_DATE('22-DEC-1998 15:32:13', 'DD-MON-YYYY HH24:MI:SS'),'DD-MM-YY HH24:MI') Start_Date
 FROM DUAL;

START_DATE
---------
22/DEC/98

SQL> SELECT TO_CHAR(TO_DATE(TO_DATE('22-DEC-1998 15:32:13', 'DD-MON-YYYY HH24:MI:SS'), 'DD-MM-YY HH24:MI'), 'DD-MON-YYYY HH24:MI:SS') Start_Date
 FROM DUAL;

START_DATE
--------------------------
22-DEC-2098 00:00:00


Cause

Sign In with your My Oracle Support account

Don't have a My Oracle Support account? Click to get started

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms