ODS Load Completing Normal Even When The Custom Hook MSC_CL_CLEANSE.CLEANSE Ends In Warning

Last updated on JUNE 28, 2017

Applies to:

Symptoms

VCP 12.1.3.9 Patch 16974659 in Production and Test

PROBLEM
-------------
Planning ODS Load will not finish with status Warning when you use custom Hook code msc_cl_cleanse
and return code G_WARNING

STEPS
--------
the status are defined in the package SPEC - file MSCCLCAB.pls
  G_SUCCESS CONSTANT NUMBER := 0;
  G_WARNING CONSTANT NUMBER := 1;
  G_ERROR CONSTANT NUMBER := 2;

When G_SUCCESS - 0 is returned - there will not be any error and normal completion is expected.
When G_WARNING - 1 is returned - there will not be any error and Warning completion is expected - but get we normal completion.
When G_ERROR - 2 is returned - then error is returned for ODS Load and processing stops

1. BEGIN
  RETCODE:= G_SUCCESS;
  END ;

replaced by

  BEGIN
  RETCODE:= G_ERROR;
  END ;
- Get error

2. BEGIN
  RETCODE:= G_SUCCESS;
  END ;

replaced by

  BEGIN
  RETCODE:= G_WARNING;
  END ;
- do not get warning status for ODS Load

EXPECTED BEHAVIOR
----------------------------
ODS load will finish with warning

BUSINESS IMPACT
----------------------
cannot implement custom hook and get back required results when warning condition is triggered in the custom hook

Cause