Step:1: On Submit, Before Computation and Validation - Insert into Collections.
BEGIN
apex_collection.add_member
(p_collection_name => 'PDVD_MASTER_ITEM_DTLS',
p_c001 => apex_application.g_f03
(i),
p_c002 => NVL
(:p77_master_pdvd_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
BEGIN
FOR i IN (SELECT c001, c002, c003, c004, c005, c006, c007, c008, c009,
c010, c011, c012
FROM apex_collections
WHERE UPPER (collection_name) = 'PDVD_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 pdvd_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 :=
pdvd_pk_get_values.pdvd_fn_get_item_identifier
(iv_n_master_pdvd_id => TO_NUMBER
(:p77_master_pdvd_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;
BEGIN
/* raise_application_error(-20001,'Inside Main Insertion'||i.c001||':'||i.c002||':'||i.c003
||i.c004||':'||i.c005||':'||i.c006
||i.c007||':'||i.c008||':'||i.c009
||i.c010||':'||i.c011);*/
pdvd_pk_dml_master.pdvd_pr_ins_master_item
(iv_n_master_item_id => i.c001,
iv_n_master_pdvd_id => :p77_master_pdvd_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 := pdvd_pk_get_values.pdvd_fn_get_user_id (:app_user);
BEGIN
l_exists :=
apex_collection.collection_exists
(p_collection_name => 'PDVD_MASTER_ITEM_DTLS');
END;
IF (l_exists)
THEN
apex_collection.delete_collection
(p_collection_name => 'PDVD_MASTER_ITEM_DTLS');
END IF;
BEGIN
l_query :=
'SELECT MASTER_ITEM_ID,
MASTER_PDVD_ID,
ITEM_ID,
PROGRAM_NAME,
OUTPUT_NAME,
TITLE,
THEME,
ITEM_TYPE,
UNIQ_REP_INDICATOR,
REPLICATE_OF_NAME,
DELIVERY_TYPES,
ACTIVE_IND
FROM PDVD_tb_master_item_dtls
WHERE MASTER_PDVD_ID = '
|| :p77_master_pdvd_id
|| ' UNION
SELECT master_item_id,master_pdvd_id,item_id,
program_name, output_name, title ,theme ,
item_type , uniq_rep_indicator ,
replicate_of_name,delivery_types, active_ind
FROM pdvd_pt_master_item_dtls ppmid
WHERE master_pdvd_id ='
|| :p77_master_pdvd_id
|| ' AND user_id ='
|| lv_n_user_id
|| ' AND session_id='
|| :app_session;
apex_collection.create_collection_from_query
(p_collection_name => 'PDVD_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());
}
No comments:
Post a Comment