Sunday, 3 July 2016

Losing Data During Validation In Apex Tabular Form !!! - Check In

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