Introduction
This post describes the steps required to stop losing the data during validation in tabular form.
Steps to follow
Step:1 On Submit, Before Computation and Validation – Insert into Collections.
Example
BEGIN
apex_collection.add_member (
p_collection_name => ‘XX_MASTER_ITEM_DTLS’,
p_c001 => apex_application.g_f03 (i),
p_c002 => NVL (:p77_master_XX_id, NULL),
p_c003 => apex_application.g_f04 (i),
p_c004 => apex_application.g_f05 (i),
p_c005 => apex_application.g_f06 (i),
p_c006 => apex_application.g_f07 (i),
p_c007 => apex_application.g_f08 (i),
p_c008 => apex_application.g_f09 (i),
p_c009 => apex_application.g_f10 (i),
p_c010 => lv_v_master_repli_of,
p_c011 => lv_v_delivery_types,
p_c012 => apex_application.g_f15 (i)
);
EXCEPTION
WHEN OTHERS
THEN
raise_application_error (-20001, ‘Error’ || lv_v_exists);
END;
END LOOP;
COMMIT;
END;
Step:2: On Submit, after computation and validation, Insert into Collections to Main table.
/* Formatted on 11/21/2019 11:09:16 AM (QP5 v5.114.809.3010) */
BEGIN
FOR i IN ( SELECT c001,
c002,
c003,
c004,
c005,
c006,
c007,
c008,
c009,
c010,
c011,
c012
FROM apex_collections
WHERE UPPER (collection_name) = ‘XX_MASTER_ITEM_DTLS’
ORDER BY c001)
LOOP
BEGIN
SELECT ‘Y’, item_type, item_id
INTO lv_v_exst, lv_n_item_type, lv_v_item_iden
FROM XX_tb_master_item_dtls
WHERE master_item_id = i.c001;
EXCEPTION
WHEN NO_DATA_FOUND
THEN
lv_v_exst := ‘N’;
END;
IF NVL (lv_v_exst, ‘N’) = ‘N’
THEN
BEGIN
lv_v_item_iden :=
XX_pk_get_values.XX_fn_get_item_identifier (
iv_n_master_XX_id => TO_NUMBER (:p77_master_XX_id),
iv_n_item_type => i.c008
);
EXCEPTION
WHEN NO_DATA_FOUND
THEN
lv_v_item_iden := NULL;
WHEN OTHERS
THEN
raise_application_error (-20001,
‘Error1’ || SQLERRM || SQLCODE);
END;
END IF;
BEGIN
XX_pk_dml_master.XX_pr_ins_master_item (
iv_n_master_item_id => i.c001,
iv_n_master_XX_id => :p77_master_XX_id,
iv_v_item_id => lv_v_item_iden,
iv_v_program_name => i.c004,
iv_v_output_name => i.c005,
iv_v_title => i.c006,
iv_n_theme => TO_NUMBER (i.c007),
iv_n_item_type => TO_NUMBER (i.c008),
iv_v_uniq_rep_indicator => i.c009,
iv_v_replicate_of => i.c010,
iv_v_delivery_types => i.c011,
iv_n_created_by => :ai_user,
iv_d_created_date => SYSDATE,
iv_v_record_source => ‘N’,
ov_v_error_msg => lv_v_error_msg,
ov_v_error_code => lv_v_error_code
);
END;
END LOOP;
END;
Step:3: On Page Load, Delete from Collections and move table data to collections and display report from collections
DECLARE
l_query VARCHAR2 (32767);
l_exists BOOLEAN;
lv_n_user_id NUMBER;
BEGIN
:AI_DEL_CHECK := ‘N’;
lv_n_user_id := XX_pk_get_values.XX_fn_get_user_id (:app_user);
BEGIN
l_exists :=
apex_collection.collection_exists (
p_collection_name => ‘XX_MASTER_ITEM_DTLS’
);
END;
IF (l_exists)
THEN
apex_collection.delete_collection (
p_collection_name => ‘XX_MASTER_ITEM_DTLS’
);
END IF;
BEGIN
l_query :=
‘SELECT MASTER_ITEM_ID,
MASTER_XX_ID,
ITEM_ID,
PROGRAM_NAME,
OUTPUT_NAME,
TITLE,
THEME,
ITEM_TYPE,
UNIQ_REP_INDICATOR,
REPLICATE_OF_NAME,
DELIVERY_TYPES,
ACTIVE_IND
FROM XX_tb_master_item_dtls
WHERE MASTER_XX_ID = ‘
|| :p77_master_XX_id
|| ‘ UNION
SELECT master_item_id,master_XX_id,item_id,
program_name, output_name, title ,theme ,
item_type , uniq_rep_indicator ,
replicate_of_name,delivery_types, active_ind
FROM XX_pt_master_item_dtls ppmid
WHERE master_XX_id =’
|| :p77_master_XX_id
|| ‘ AND user_id =’
|| lv_n_user_id
|| ‘ AND session_id=’
|| :app_session;
apex_collection.create_collection_from_query (
p_collection_name => ‘XX_MASTER_ITEM_DTLS’,
p_query => l_query,
p_generate_md5 => ‘YES’
);
END;
END;
Step:4: Include newly added row at top to avoid losing newly added records,
javascript:addRowTop();
function addRowTop()
{
apex.widget.tabular.addRow();
apex.jQuery(apex.widget.tabular.gTabForm).find(“tr”).last().insertBefore(apex.jQuery(apex.widget.tabular.gTabForm).find(“tr”).first());
}
Call To Action:
For Oracle apex development and customization please do contact our company website https://doyensys.com/
Conclusion
Taking the above example as a reference, you can stop losing data in tabular form.
