We need to identify the following three points from this scenario...

We need to identify the following three points from this scenario:
1. Why do we see this issue in this scenario?
2. How to over come this issue?
3. How can we resolve this issue?

The issue is related to notification. When the subscriber balance less than or equal to 50 then it will generate a notification. The issue here is it is generating notification but one value is not getting populated in notification. Need to identify the issue, why it’s not populating that particular value. The value for the token Total sum of currency balance is [] is blank.
Please find the generated notification below:
SUB CORE LTEQ TO 50: Subscriber 7865555553 current State is Active , core balance is 45.0000 . Core expire date is 2011-10-05 23:59:59, Core max/min is 1000.0000 / 0.0000 , Core Major/Minor is 45 / 0000 , Total sum of currency balance is [] . Core balance type is PREPAID , Balance expire date in YYYY is 2011, YY is 11, in MM is 10, in DD is 06, in HH24 is 00, in MIN is 00, in SECONDS is 00. Account no 14432 Primary Offer is SV GROUP MEMBER SL PO 02 .

You're showing pseudo code instead of the actual SQL, so it's hard to be specific. I'm guessing that blank is actually NULL. If so, the reason you are missing those rows is that Oracle does not treat NULL as =, >, or < any value. NULL is the absence of any value and can't be compared. I think you want to treat NULL like zero (0). Oracle provides the NVL function to facilitate that. To compare a column value to 50, do something like this.

WHERE NVL(mycolumn,0) <= 50

If you are using SUM and want NULL to add zero (0) to the total, I recommend something like this.

SELECT SUM(NVL(mycolumn,0)) mycolumntotal, ...
FROM ...
GROUP BY ...
HAVING SUM(NVL(mycolumn,0)) <= 50

Help the community by fixing grammatical or spelling errors, summarizing or clarifying the solution, and adding supporting information or resources. Always respect the original author.

You're showing pseudo code instead of the actual SQL, so it's hard to be specific. I'm guessing that blank is actually NULL. If so, the reason you are missing those rows is that Oracle does not treat NULL as =, >, or < any value. NULL is the absence of any value and can't be compared. I think you want to treat NULL like zero (0). Oracle provides the NVL function to facilitate that. To compare a column value to 50, do something like this.

WHERE NVL(mycolumn,0) <= 50

If you are using SUM and want NULL to add zero (0) to the total, I recommend something like this.

SELECT SUM(NVL(mycolumn,0)) mycolumntotal, ...
FROM ...
GROUP BY ...
HAVING SUM(NVL(mycolumn,0)) <= 50

TYPE offer_network_notif_tr IS TABLE OF offer_network_notif_r INDEX BY PLS_INTEGER;
TYPE offer_network_notif_ttr IS TABLE OF offer_network_notif_tr INDEX BY PLS_INTEGER;
TYPE offer_network_notif_tttr IS TABLE OF offer_network_notif_ttr INDEX BY PLS_INTEGER;

gv_subs_prev_state account_subscriber.previous_state%TYPE := 0; --CBS00110409
-----
gv_Shadow_Bal BOOLEAN := FALSE;
-----
/*
|| Local Function added for CBS00093463
|| Here the function accepts number in string format and process as below
|| This Function is intended for formating a number string 'x' such that -1 < x < 1; x!=0.
|| Here, when the number within this range, for example, 0.75 is converted to
|| character for the display, it is shown as '.75' and not '0.75'.
|| Similarly, -0.75 is be shown as -.75.
|| So, this function will return desired formatted string as 0.75 or -0.75.
*/
FUNCTION format_number(i_val IN VARCHAR2)
RETURN VARCHAR2
IS
lv_format_val VARCHAR2(100);
lv_num NUMBER;
BEGIN
lv_num := TO_NUMBER(i_val);
IF lv_num > -1 AND lv_num < 1
THEN

/*
|| If invoked by BATCH module, then
|| Open the cursor to fetch all the network notification configurations
|| Else
|| Open the cursor to fetch the network notification configurations for a specific notification id
|| Load the data into the global collections
*/

END IF;
--
IF lt_rows.COUNT > 0
THEN
FOR i IN lt_rows.FIRST .. lt_rows.LAST
LOOP
gt_data_unit_name (lt_rows(i).notif_ivr_language_id) (lt_rows(i).default_text) : lt_rows(i).unicode_text;
END LOOP;
END IF;

-- CQ CBS00098726 change starts
-- Test is failing if the o_cflag go with a NULL value so Setting o_cflag to false if its NULL
IF o_cflag IS NULL
THEN
o_cflag := FALSE;
END IF;
-- CQ CBS00098726 change ends

-- CQ CBS00098726 change starts
-- Test is failing if the o_cflag go with a NULL value so Setting o_cflag to false if its NULL
IF o_cflag IS NULL
THEN
o_cflag := FALSE;
END IF;
-- CQ CBS00098726 change ends

END test;

------
-- FUNCTION: split
-- NOTES : This procedure splits a string in two parts - left and right using
-- provided delimiter. It returns TRUE if delemiter has been found
-- and FALSE if not.
------
FUNCTION strsplit
( i_str IN VARCHAR2, -- string to be processed
i_dlm IN VARCHAR2, -- Delimiter
o_lstr OUT NOCOPY VARCHAR2, -- Left part of the string - before first delimiter
o_rstr OUT NOCOPY VARCHAR2, -- Right part of the string - after second delimiter
i_left_search IN BOOLEAN DEFAULT TRUE -- Start searching from left to right
) RETURN BOOLEAN IS
------
lv_pos PLS_INTEGER;
--
BEGIN
IF i_str IS NULL OR i_dlm IS NULL
THEN RETURN FALSE;
END IF;
--
IF i_left_search
THEN lv_pos := INSTR( i_str, i_dlm, 1, 1 );
ELSE lv_pos := LENGTH(i_str) - (LENGTH(i_str) - INSTR( i_str, i_dlm,-1, 1 ));
END IF;
--
IF lv_pos > 0 -- first delimiter has not been found
THEN o_lstr := SUBSTR( i_str, 1, lv_pos - 1 );
o_rstr := SUBSTR( i_str, lv_pos + 1 );
RETURN TRUE;
ELSE RETURN FALSE;
END IF;
--
END strsplit;

TYPE offer_network_notif_tr IS TABLE OF offer_network_notif_r INDEX BY PLS_INTEGER;
TYPE offer_network_notif_ttr IS TABLE OF offer_network_notif_tr INDEX BY PLS_INTEGER;
TYPE offer_network_notif_tttr IS TABLE OF offer_network_notif_ttr INDEX BY PLS_INTEGER;

/*
|| Local Function added for CBS00093463
|| Here the function accepts number in string format and process as below
|| This Function is intended for formating a number string 'x' such that -1 < x < 1; x!=0.
|| Here, when the number within this range, for example, 0.75 is converted to
|| character for the display, it is shown as '.75' and not '0.75'.
|| Similarly, -0.75 is be shown as -.75.
|| So, this function will return desired formatted string as 0.75 or -0.75.
*/
FUNCTION format_number(i_val IN VARCHAR2)
RETURN VARCHAR2
IS
lv_format_val VARCHAR2(100);
lv_num NUMBER;
BEGIN
lv_num := TO_NUMBER(i_val);
IF lv_num > -1 AND lv_num < 1
THEN