Demantra 12.2.3 Procedure EP_LOAD_ITEMS Not Loading All Items. Error In PLSQL Code (Doc ID 1917396.1)

Last updated on SEPTEMBER 08, 2016

Applies to:

Oracle Demantra Demand Management - Version 12.2.3 and later
Information in this document applies to any platform.

Goal

Demantra 12.2.3 procedure EP_LOAD_ITEMS not loading all items. Error in PLSQL code

Problem Description
---------------------------------------------------
There is an error in the PLSQL code in EP_LOAD_ITEMS which is preventing some items from loading.

Line# 606
vs_sql := 'SELECT t_ep_item_brand_ep_id, item_brand_desc FROM t_ep_item_brand WHERE item_brand = || vs_brand ||';

Line# 624
vs_sql := 'SELECT t_ep_department_ep_id, department_desc FROM t_ep_department WHERE department = || vs_department ||';


Both code blocks attempt to execute the dynamic sql's above, but the where clause does not have an apostrophe before and after the vs_department. This is causing a select statement to always return no results. Which in turn causes vi_t_ep_item_brand_ep_id and vi_t_ep_department_ep_id to always return 0 (see a couple lines of code down in the procedure).


As a workaround, I've just changed it to
vs_sql := 'SELECT t_ep_department_ep_id, department_desc FROM t_ep_department WHERE department = ' || vs_department ;
 

Solution

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