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.
