By Tom Tisdale

When coding for specific conditions, it is easy to take a shortcut and hardcode a series of literal values. The problem is, if this condition needs to exist for another scenario this would result in a code change each time.

Here is an example:
You need your code to execute for specific inventory orgs only.
…
IF p_organization_code IN (‘CHI’, ‘DAL’) THEN

<code>;

END IF;
…
If another inventory code is added and needs to execute the same code, this would result in a code change for each new organization.

Solution
An alternative approach would be to create a common lookup that holds each of the organization codes that need to execute the specific code. This can be maintained from Application Developer responsibility from front end.
Navigate to Application Developer -> Application -> Lookups -> Common.
Create your new Lookup type with each of the new values as shown below:

 

As you can see, we have added a new inventory organization called SFO.

Then update your code once to allow future org values:

DECLARE

v_flag VARCHAR2(1);

BEGIN

SELECT ‘Y’

INTO v_flag

FROM fnd_lookup_values

WHERE lookup_type = ‘XXBPI_INV_EXEC_ORG_CODE’

AND language = ‘US’

AND enabled_flag = ‘Y’

AND SYSDATE BETWEEN start_date_active AND NVL(end_date_active, ’31-DEC-4712′)

AND lookup_code = p_organization_code;  — Org Parameter

EXCEPTION WHEN OTHERS THEN

v_flag := ‘N’;

END;

IF v_flag = ‘Y’ THEN

<code>

END IF;

…

Make sure you document the new lookup along with code change!