Proport Failing OR Running Long After 12.2.7 or 12.2.6.x Upgrade . Error : ORA-20003: Error in PROCEDURE PROPORT (Doc ID 2325798.1)

Last updated on JANUARY 05, 2018

Applies to:

Symptoms

On : 12.2.6.x or 12.2.7. version, Proport jobs fails after Demantra 12.2.7 upgrade or upgrade to 12.2.6.x



ERROR
-----------------------
####################################
2017-08-28 12:31:41,869 GMT [WFProcess_11] ERROR dpweb.general: Process ID: 1351375
Schema ID: 3577
Schema name: 'RFL Proport Data'
Step ID: 'Step1'
Step name: 'Proport'

Error description 'Program internal error:'
2017-08-28 12:31:41,869 GMT [WFProcess_11] ERROR dpweb.general: ######################################################################################
2017-08-28 12:31:41,884 GMT [Mail_Thread] ERROR workflow.steps: MailThread:run()::No Sender or Reciever
2017-08-28 12:31:41,978 GMT [Mail_Thread] ERROR workflow.steps: MailThread:run()::No Sender or Reciever
2017-08-28 13:21:24,896 GMT [WFProcess_12] FATAL workflow.general: ORA-20002: -20003 ORA-20003: Error in PROCEDURE PROPORT line 1220
  executing dynamic_proc('DYN_PROPORT_SALES_1(' WHERE prop_changes = 1')')
ORA-06512: at "DEMANTRA73.PROPORT", line 1677
ORA-06512: at line 1

java.sql.SQLException: ORA-20002: -20003 ORA-20003: Error in PROCEDURE PROPORT line 1220
  executing dynamic_proc('DYN_PROPORT_SALES_1(' WHERE prop_changes = 1')')
ORA-06512: at "DEMANTRA73.PROPORT", line 1677
ORA-06512: at line 1

Error in DB Exception Log :

"-20002 ORA-20002: -907 ORA-00907: missing right parenthesis Error in PROCEDURE line 403

  dynamic_ddl( 'MERGE INTO MDP_MATRIX t
  USING (SELECT p.item_id AS item_id, p.location_id AS location_id
  , MAX( delta) AS delta
  , MIN( hgp_start_date ) As hgp_start_date
  , MIN( hgp_last_date ) AS hgp_last_date
  , SUM( safe_division(p.sum_all, ( p.hgp_last_date , NVL( p.first_s BEGIN DYN_PROPORT_SALES_1(' WHERE prop_changes = 1'); END;"




STEPS
-----------------------
The issue can be reproduced at will with the following steps:
1. Upgrade Demantra tp 12.2.6.x or 12.2.7
2. Run proport

BUSINESS IMPACT
-----------------------
The issue has the following business impact:
Due to this issue, users cannot get forecast impacting planners work

Cause

My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.