Hi,
i am getting an error ORA-06531: Reference to uninitialized collection when i am trying to test the below procedure.
PROCEDURE PRC_FULL_DATA_PUBLISH (
x_value_set_id IN TEST_DEMO_TBL.VALUE_SET_ID%TYPE,
x_value_set_name IN TEST_DEMO_TBL.VALUE_SET_NAME%TYPE,
x_gl_flex_values_obj_tbl IN OUT apps.GL_FLEX_VALUES_OBJ_TBL,
x_gl_flex_values_nh_obj_tbl OUT apps.GL_FLEX_VALUES_NH_OBJ_TBL)
AS
msg_count NUMBER;
return_status VARCHAR2 (200);
msg_data VARCHAR2 (200);
x_gl_flex_values_status VARCHAR2 (200);
x_gl_flex_values_nh_status VARCHAR2 (200);
x NUMBER;
TYPE ltab_full_data
IS VARRAY (1000) OF XXHIG_WRK_FAH_FULL_CF_SYNC%ROWTYPE;
ltab_tb ltab_full_data;
x_gl_flex_values_obj_tbl1 apps.gl_coa_seg_val_imp_pub.gl_flex_values_tbl_type;
--x_gl_flex_values_nh_obj_tbl apps.gl_coa_seg_val_imp_pub.gl_flex_values_nh_tbl_type;
CURSOR c1
IS
SELECT *
--INTO x_gl_flex_values_obj_tbl
FROM TEST_DEMO_TBL
WHERE VALUE_SET_NAME = x_value_set_name
AND VALUE_SET_ID = x_value_set_id
AND transfer_status = 'I'
AND flex_value IN ('247900', '248100');
--
-- open c1;
-- loop
-- fetch c1 bulk collect into ltab_tb limit 100;
-- exit when c1%notfound;
--
BEGIN
ltab_tb := ltab_full_data ();
SELECT *
BULK COLLECT INTO ltab_tb
FROM XXHIG_WRK_FAH_FULL_CF_SYNC
WHERE VALUE_SET_NAME = x_value_set_name
AND VALUE_SET_ID = x_value_set_id
AND transfer_status = 'I'
AND flex_value IN ('247900', '248100');
DBMS_OUTPUT.put_line ('Before loop :');
FOR i IN 1 .. ltab_tb.LAST
LOOP
DBMS_OUTPUT.put_line ('Just inside the loop :');
BEGIN
DBMS_OUTPUT.put_line ('Test one---- ' || ltab_tb (I).flex_value);
x_gl_flex_values_obj_tbl (I).value_set_name :=
ltab_tb (I).value_set_name;
DBMS_OUTPUT.put_line ('Test two---- ');
x_gl_flex_values_obj_tbl (I).flex_value := ltab_tb (I).flex_value;
x_gl_flex_values_obj_tbl (I).flex_desc := ltab_tb (I).flex_desc;
x_gl_flex_values_obj_tbl (I).parent_flex_value :=
ltab_tb (I).parent_flex_value;
x_gl_flex_values_obj_tbl (I).summary_flag :=
ltab_tb (I).summary_flag;
x_gl_flex_values_obj_tbl (I).roll_up_group :=
ltab_tb (I).roll_up_group;
x_gl_flex_values_obj_tbl (I).hierarchy_level :=
ltab_tb (I).hierarchy_level;
x_gl_flex_values_obj_tbl (I).allow_budgeting :=
ltab_tb (I).allow_budgeting;
x_gl_flex_values_obj_tbl (I).allow_posting :=
ltab_tb (I).allow_posting;
x_gl_flex_values_obj_tbl (I).account_type :=
ltab_tb (I).account_type;
x_gl_flex_values_obj_tbl (I).reconcile := ltab_tb (I).reconcile;
x_gl_flex_values_obj_tbl (I).third_party_control_account :=
ltab_tb (I).third_party_control_account;
x_gl_flex_values_obj_tbl (I).enabled_flag :=
ltab_tb (I).enabled_flag;
x_gl_flex_values_obj_tbl (I).effective_from :=
ltab_tb (I).effective_from;
x_gl_flex_values_obj_tbl (I).effective_to :=
ltab_tb (I).effective_to;
DBMS_OUTPUT.put_line ('Inside loop :');
EXCEPTION
WHEN OTHERS
THEN
DBMS_OUTPUT.put_line ('The error is : ' || SQLERRM);
END;
x_gl_flex_values_status := NULL;
x_gl_flex_values_nh_status := NULL;
msg_count := NULL;
return_status := NULL;
msg_data := NULL;
INSERT INTO test_data (VALUE_SET_NAME, FLEX_VALUE)
VALUES (
x_gl_flex_values_obj_tbl (I).value_set_name,
x_gl_flex_values_obj_tbl (I).flex_value);
COMMIT;
apps.fnd_global.apps_initialize (1130, 20434, 101);
APPS.gl_coa_seg_val_imp_pub.coa_segment_val_imp (
p_api_version => 1.0, --api_version,
p_init_msg_list => 'F', -- init_msg_list,
p_commit => 'F',
p_validation_level => 100,
x_return_status => return_status,
x_msg_count => msg_count,
x_msg_data => msg_data,
p_gl_flex_values_obj_tbl => x_gl_flex_values_obj_tbl,
p_gl_flex_values_nh_obj_tbl => x_gl_flex_values_nh_obj_tbl,
p_gl_flex_values_status => x_gl_flex_values_status,
p_gl_flex_values_nh_status => x_gl_flex_values_nh_status);
END LOOP;
EXCEPTION
WHEN NO_DATA_FOUND
THEN
return_status := 'E';
msg_data := 'No Data to insert/update';
WHEN OTHERS
THEN
msg_data := 'Other Error' || msg_data || SQLCODE || SQLERRM;
END;
Regards,
BS.