Search bar for Tabular form with auto complete option for Date values
Introduction
On default, in tabular form Oracle apex did n’t provided the Search option. We provide solution to add search option in Oracle apex.
Step1:
Created a page level select list item to display all the column names.
Step2:
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.
Step3:
Created 2 buttons, one for searching and other to clear the previously entered value.
Step4:
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
Step5:
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;
Step6:
Created an application for getting the date values.
Step7:
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);
});
}
Call To Action
For Oracle apex development and customization please contact us.Our company website https://doyensys.com/
Conclusion
By Following above steps we can add search option in Oracle apex.