Tuesday, 19 July 2016

Search bar for Tabular form with auto complete option for Date values

Requirement:
Tabular form Search bar

Challenges faced: 
On default, in tabular form Oracle apex didn’t provided the Search option. 

Solution:
1. Created a page level select list item to display all the column names.
2. Created a text item (which will act as select list also on selecting date value in previous item) for user to enter the search value.
3. Created 2 buttons, one for searching and other to clear the previously entered value.
4. Modified the existing report query as 
SELECT NULL, delivery_item_id, master_item_id, delivery_pdvd_id, item_id,
       program_name, output_name, title, "Item_type", "Theme",
       "Unique_Replicate_Indicator", "Replicate_of", rerun, validation_type,
       "Hold", "Hold Status", developer_id, dev_proj_complete_date,
       dev_status, dev_pending_reason, dev_act_complete_date,
       dev_status_comments, validator_id, valid_proj_complete_date,
       valid_status, valid_pending_reason, valid_act_complete_date,
       valid_status_comments, reviewer_id, review_proj_complete_date,
       review_status, review_pending_reason, review_act_complete_date,
       review_status_comments, validation_program_name, dev_check,
       valid_check, review_check, overall_check, cdars_standards, "Role",
       general_comments, SEQUENCE, gc
  FROM (SELECT ROWNUM rnum, NULL, delivery_item_id, master_item_id,
               delivery_pdvd_id, item_id, program_name, output_name, title,
               "Item_type", "Theme", "Unique_Replicate_Indicator",
               "Replicate_of", rerun, validation_type, "Hold", "Hold Status",
               developer_id, dev_proj_complete_date, dev_status,
               dev_pending_reason, dev_act_complete_date, dev_status_comments,
               validator_id, valid_proj_complete_date, valid_status,
               valid_pending_reason, valid_act_complete_date,
               valid_status_comments, reviewer_id, review_proj_complete_date,
               review_status, review_pending_reason, review_act_complete_date,
               review_status_comments, validation_program_name, dev_check,
               valid_check, review_check, overall_check, cdars_standards,
               "Role", general_comments, SEQUENCE, gc
          FROM (SELECT   NULL, delivery_item_id, master_item_id,
                         delivery_pdvd_id, item_id, program_name, output_name,
                         title,
                         (SELECT lov_values
                            FROM pdvd_tb_lov_dict
                           WHERE lov_id = ptdid.item_type
                             AND lov_type = 'Item Type') "Item_type",
                         (SELECT lov_values
                            FROM pdvd_tb_lov_dict
                           WHERE lov_id = ptdid.theme
                             AND lov_type = 'Theme') "Theme",
                         DECODE
                            (ptdid.uniq_rep_indicator,
                             'U', 'Unique',
                             'R', 'Replicate'
                            ) "Unique_Replicate_Indicator",
                         replicate_of_name "Replicate_of", rerun,
                         validation_type, item_hold "Hold",
                         item_hold "Hold Status",
                         (SELECT first_name || ' ' || last_name
                            FROM pdvd_tb_user_defn
                           WHERE user_id = ptdid.developer_id) developer_id,
                         dev_proj_complete_date, dev_status,
                         (SELECT DECODE
                                    (lov_values,
                                     'Other', 'Other /'
                                      || ptdid.dev_status_oth_comments,
                                     lov_values
                                    )
                            FROM pdvd_tb_lov_dict
                           WHERE lov_id = ptdid.dev_pending_reason
                             AND lov_type = 'Pending Reasons'
                             AND dev_status = 'Pending') dev_pending_reason,
                         dev_act_complete_date, dev_status_comments,
                         (SELECT first_name || ' ' || last_name
                            FROM pdvd_tb_user_defn
                           WHERE user_id = ptdid.validator_id) validator_id,
                         valid_proj_complete_date, valid_status,
                         (SELECT DECODE
                                    (lov_values,
                                     'Other', 'Other /'
                                      || ptdid.valid_status_oth_comments,
                                     lov_values
                                    )
                            FROM pdvd_tb_lov_dict
                           WHERE lov_id = ptdid.valid_pending_reason
                             AND lov_type = 'Pending Reasons'
                             AND valid_status = 'Pending')
                                                         valid_pending_reason,
                         valid_act_complete_date, valid_status_comments,
                         (SELECT first_name || ' ' || last_name
                            FROM pdvd_tb_user_defn
                           WHERE user_id = ptdid.reviewer_id) reviewer_id,
                         review_proj_complete_date, review_status,
                         (SELECT DECODE
                                    (lov_values,
                                     'Other', 'Other /'
                                      || ptdid.review_status_oth_comments,
                                     lov_values
                                    )
                            FROM pdvd_tb_lov_dict
                           WHERE lov_id = ptdid.review_pending_reason
                             AND lov_type = 'Pending Reasons'
                             AND review_status = 'Pending')
                                                        review_pending_reason,
                         review_act_complete_date, review_status_comments,
                         validation_program_name,
                         CASE
                            WHEN dev_proj_complete_date <= SYSDATE
                            AND UPPER (dev_status) NOT IN
                                                         ('COMPLETED', 'NEW')
                               THEN 'red'
                         END dev_check,
                         CASE
                            WHEN valid_proj_complete_date <=
                                                          SYSDATE
                            AND UPPER (valid_status) NOT IN
                                                         ('COMPLETED', 'NEW')
                               THEN 'red'
                         END valid_check,
                         CASE
                            WHEN review_proj_complete_date <=
                                                         SYSDATE
                            AND UPPER (review_status) NOT IN
                                                         ('COMPLETED', 'NEW')
                               THEN 'red'
                         END review_check,
                         CASE
                            WHEN (    dev_proj_complete_date <= SYSDATE
                                  AND UPPER (dev_status) NOT IN
                                                         ('COMPLETED', 'NEW')
                                  AND (   developer_id = :ai_user
                                       OR pdvd_pk_get_values.pdvd_fn_get_dlvry_pdvd_usrrole
                                                             (:ai_user,
                                                              delivery_pdvd_id
                                                             ) <> 'N'
                                      )
                                 )
                             OR (    valid_proj_complete_date <= SYSDATE
                                 AND UPPER (valid_status) NOT IN
                                                         ('COMPLETED', 'NEW')
                                 AND (   validator_id = :ai_user
                                      OR pdvd_pk_get_values.pdvd_fn_get_dlvry_pdvd_usrrole
                                                             (:ai_user,
                                                              delivery_pdvd_id
                                                             ) <> 'N'
                                     )
                                )
                             OR (    review_proj_complete_date <= SYSDATE
                                 AND UPPER (review_status) NOT IN
                                                         ('COMPLETED', 'NEW')
                                 AND (   reviewer_id = :ai_user
                                      OR pdvd_pk_get_values.pdvd_fn_get_dlvry_pdvd_usrrole
                                                             (:ai_user,
                                                              delivery_pdvd_id
                                                             ) <> 'N'
                                     )
                                )
                               THEN 'red'
                         END overall_check,
                         cdars_standards,
                         pdvd_pk_get_values.pdvd_fn_get_dlyusr_prior_role
                            (iv_n_user_id               => :ai_user,
                             iv_n_delivery_pdvd_id      => delivery_pdvd_id,
                             iv_n_delivery_item_id      => delivery_item_id
                            ) "Role",
                         NULL general_comments,
                         (SELECT display_order
                            FROM pdvd_tb_lov_dict
                           WHERE lov_id = item_type) SEQUENCE,
                         ptdid.general_comments gc
                    FROM pdvd_tb_delivery_item_dtls ptdid
                   WHERE delivery_pdvd_id = :p86_delivery_pdvd_id
                     AND (   (   ptdid.developer_id = :ai_user
                              OR ptdid.validator_id = :ai_user
                              OR ptdid.reviewer_id = :ai_user
                             )
                          OR (pdvd_pk_get_values.pdvd_fn_get_dlvry_pdvd_usrrole
                                                             (:ai_user,
                                                              delivery_pdvd_id
                                                             ) <> 'N'
                             )
                         )
                     AND (   (    UPPER(
                                     trim(DECODE
                                         (:p86_search,
                                          'Program Name', program_name,
                                          'Output Name', output_name,
                                          'Title', title,
                                          'Item Type', pdvd_pk_get_values.pdvd_get_lov_value
                                                                    (item_type),
                                          'Rerun', DECODE (NVL (rerun, 'N'),
                                                           'Y', 'Yes',
                                                           'N', 'No'
                                                          ),
                                          'Validation Type', validation_type,
                                          'Prod Programmer', pdvd_pk_get_values.pdvd_fn_get_user_name
                                                                 (developer_id),
                                          'Prod Status', dev_status,
                                          'Validator', pdvd_pk_get_values.pdvd_fn_get_user_name
                                                                 (validator_id),
                                          'Validator Status', valid_status,
                                          'Reviewer', pdvd_pk_get_values.pdvd_fn_get_user_name
                                                                  (reviewer_id),
                                          'Review Status', review_status,
                                          'Hold', DECODE (NVL (item_hold, 'N'),
                                                          'Y', 'Yes',
                                                          'N', 'No'
                                                         ),
                                          'Prod. Proj. Completion Date', dev_proj_complete_date,
                                          'Val Proj. Completion Date', valid_proj_complete_date,
                                          'Rev Proj. Completion Date', review_proj_complete_date,
                                          'Prod. Act. Completion Date', dev_act_complete_date,
                                          'Val Act. Completion Date', valid_act_complete_date,
                                          'Rev Act. Completion Date', review_act_complete_date,
                                          'Theme', pdvd_pk_get_values.pdvd_get_lov_value
                                                                        (theme),
                                          'Unique/Replicate', DECODE
                                                          (uniq_rep_indicator,
                                                           'U', 'Unique',
                                                           'R', 'Replicate'
                                                          ),
                                          'Replicate of', replicate_of_name
                                         )
                                     )) LIKE '%' || UPPER (trim(:p86_value)) || '%'
                              AND :p86_search IS NOT NULL
                             )
                          OR :p86_search IS NULL
                         )
                ORDER BY SEQUENCE, output_name ))
 WHERE rnum >= :p86_from AND rnum <= :p86_to
5. Created a page level process for searching the data
DECLARE
   lv_n_max          NUMBER;
   lv_v_pagination   VARCHAR2 (100);
BEGIN
   BEGIN
      SELECT COUNT (*)
        INTO lv_n_max
        FROM (SELECT   NULL, delivery_item_id, master_item_id,
                       delivery_pdvd_id, item_id, program_name, output_name,
                       title, ptdid.item_type "Item_type",
                       ptdid.uniq_rep_indicator "Unique_Replicate_Indicator",
                       ptdid.replicate_of "Replicate_of", rerun,
                       validation_type, item_hold "Hold",
                       item_hold "Hold Status",
                       ptdid.developer_id developer_id,
                       dev_proj_complete_date, dev_status,
                       ptdid.dev_pending_reason dev_pending_reason,
                       dev_act_complete_date, dev_status_comments,
                       ptdid.validator_id validator_id,
                       valid_proj_complete_date, valid_status,
                       ptdid.valid_pending_reason valid_pending_reason,
                       valid_act_complete_date, valid_status_comments,
                       ptdid.reviewer_id reviewer_id,
                       review_proj_complete_date, review_status,
                       ptdid.review_pending_reason review_pending_reason,
                       review_act_complete_date, review_status_comments,
                       validation_program_name, cdars_standards,
                       pdvd_pk_get_values.pdvd_fn_get_dlyusr_prior_role
                           (iv_n_user_id               => :ai_user,
                            iv_n_delivery_pdvd_id      => delivery_pdvd_id,
                            iv_n_delivery_item_id      => delivery_item_id
                           ) "Role",
                       NULL general_comments,
                       (SELECT display_order
                          FROM pdvd_tb_lov_dict
                         WHERE lov_id = item_type) SEQUENCE
                  FROM pdvd_tb_delivery_item_dtls ptdid
                 WHERE delivery_pdvd_id = :p86_delivery_pdvd_id
                   AND (   (   ptdid.developer_id = :ai_user
                            OR ptdid.validator_id = :ai_user
                            OR ptdid.reviewer_id = :ai_user
                           )
                        OR (pdvd_pk_get_values.pdvd_fn_get_dlvry_pdvd_usrrole
                                                             (:ai_user,
                                                              delivery_pdvd_id
                                                             ) <> 'N'
                           )
                       )
                    AND (   (    UPPER(
                                     trim(DECODE
                                         (:p86_search,
                                          'Program Name', program_name,
                                          'Output Name', output_name,
                                          'Title', title,
                                          'Item Type', pdvd_pk_get_values.pdvd_get_lov_value
                                                                    (item_type),
                                          'Rerun', DECODE (NVL (rerun, 'N'),
                                                           'Y', 'Yes',
                                                           'N', 'No'
                                                          ),
                                          'Validation Type', validation_type,
                                          'Prod Programmer', pdvd_pk_get_values.pdvd_fn_get_user_name
                                                                 (developer_id),
                                          'Prod Status', dev_status,
                                          'Validator', pdvd_pk_get_values.pdvd_fn_get_user_name
                                                                 (validator_id),
                                          'Validator Status', valid_status,
                                          'Reviewer', pdvd_pk_get_values.pdvd_fn_get_user_name
                                                                  (reviewer_id),
                                          'Review Status', review_status,
                                          'Hold', DECODE (NVL (item_hold, 'N'),
                                                          'Y', 'Yes',
                                                          'N', 'No'
                                                         ),
                                          'Prod. Proj. Completion Date', dev_proj_complete_date,
                                          'Val Proj. Completion Date', valid_proj_complete_date,
                                          'Rev Proj. Completion Date', review_proj_complete_date,
                                          'Prod. Act. Completion Date', dev_act_complete_date,
                                          'Val Act. Completion Date', valid_act_complete_date,
                                          'Rev Act. Completion Date', review_act_complete_date,
                                          'Theme', pdvd_pk_get_values.pdvd_get_lov_value
                                                                        (theme),
                                          'Unique/Replicate', DECODE
                                                          (uniq_rep_indicator,
                                                           'U', 'Unique',
                                                           'R', 'Replicate'
                                                          ),
                                          'Replicate of', replicate_of_name
                                         )
                                     )) LIKE '%' || UPPER (trim(:p86_value)) || '%'
                              AND :p86_search IS NOT NULL
                             )
                          OR :p86_search IS NULL
                         )
              ORDER BY SEQUENCE, item_id);
   EXCEPTION
      WHEN OTHERS
      THEN
         lv_n_max := NULL;
   END;

   :p86_max := lv_n_max;

   IF lv_n_max < TO_NUMBER(:p86_pagination)
   THEN
     -- raise_application_error(-20001,'Error'||:p86_pagination);
      :p86_to := lv_n_max;
      :p86_from := 1;
   ELSIF lv_n_max = 0
   THEN
      :p86_to := 0;
      :p86_from := 0;
      :p86_max := 0;
   ELSIF lv_n_max > TO_NUMBER(:p86_pagination)
   THEN
      :p86_to := :p86_pagination;
      :p86_from := (:p86_to - :p86_pagination) + 1;
   END IF;
END;
6. Created an application for getting the date values.
7. Created 2 java script functions for displaying the dates when date value is selected and display as a text field when remaining columns are selected.
$(function()
{
$("#P86_VALUES").focus(function()
                      {
var a=$("#P86_SEARCH").val();
//alert(a);
if (a=='Prod. Proj. Completion Date'
|| a=='Val Proj. Completion Date'
||a=='Rev Proj. Completion Date'
||a=='Prod. Act. Completion Date'
|| a=='Val Act. Completion Date'
||a=='Rev Act. Completion Date')
{
                       call_list();
}
                       });
});

function call_list()
{
   // alert("Inside function");
    var list = document.getElementById('languages');
     var get = new htmldb_Get(null,$x('pFlowId').value,
              'APPLICATION_PROCESS=AP_PROJ_DATE',0);
    get.add('AI_PROJ_DATE',$("#P86_DELIVERY_PDVD_ID").val());
    get.add ('AI_SEL_DATE',$("#P86_SEARCH").val());
    gReturn = get.get();
   // alert(gReturn);
   // alert("Search"+$("#P86_SEARCH").val());
    //alert("Search"+$("#P86_SEARCH").val());
    var temp = new Array();
    var c = gReturn.trim();
    temp = c.split(",");
    $("#languages").empty();
   temp.forEach(function(item){
   var option = document.createElement('option');
   option.value = item;
   list.appendChild(option);
   });
}


OUTPUT


1 comment:

  1. Thank you for sharing such a nice and interesting blog with us. I have seen that all will say the same thing repeatedly. But in your blog, I had a chance to get some useful and unique information.

    Oracle Fusion HCM Online Training
    Oracle Fusion HCM Training

    ReplyDelete