Description:
Create items to set count of rows and display
Step:1: Create items to set count of rows and display
Item to represent pagination. Also, create buttons to navigate different pages.
PXX_FROM – From Value
PXX_TO – To Value
PXX_MAX – Max Value
PXX_PAGINATION – Rows per page
PXX_PAGINATION_DISPLAY – Pagination Display
Buttons: Save and Next,Save and Previous,
First Page,Last Page
Step:2: Restrict data with specified range in report query, Query is displayed as follows
SELECT master_item_id, master_item_id_display, master_pdvd_id, item_id, col1,
col2, col3, col4, col5, col6, col7, col8, col9, NULL, “Check”,
active_ind, SEQUENCE, “replicate_of”
FROM (SELECT ROWNUM rm, master_item_id, master_item_id_display,
master_pdvd_id, item_id, col1, col2, col3, col4, col5, col6,
col7, col8, col9, NULL, “Check”, active_ind, SEQUENCE,
“replicate_of”
FROM (SELECT c001 master_item_id, seq_id master_item_id_display,
c002 master_pdvd_id, c003 item_id, c004 col1,
c005 col2, c006 col3, c007 col4, c008 col5,
c009 col6, b.c010 col7, c011 col8, NULL col9, NULL,
NULL “Check”, c012 active_ind,
(SELECT display_order
FROM pdvd_tb_lov_dict
WHERE lov_id = b.c008) SEQUENCE,
c010 “replicate_of”
FROM apex_collections b
WHERE UPPER (collection_name) = ‘PDVD_MASTER_ITEM_DTLS’
AND ( ( UPPER
(DECODE
(TRIM (:p77_search),
‘Item / Identifier’, c003,
‘Program Name’, c004,
‘Output_Name’, c005,
‘Title’, c006,
‘Theme’, pdvd_pk_get_values.pdvd_get_lov_value
(c007),
‘Item Type’, pdvd_pk_get_values.pdvd_get_lov_value
(c008),
‘Unique_Replicate’, DECODE
(c009,
‘U’, ‘Unique’,
‘R’, ‘Replicate’
),
‘Replicate_of’, c010,
‘Delivery Types’, c011
)
) LIKE
‘%’ || UPPER (:p77_act_value)
|| ‘%’
AND :p77_search IS NOT NULL
)
OR :p77_search IS NULL
)
ORDER BY SEQUENCE,c005))
WHERE rm >= :p77_from AND rm <= :p77_to
ORDER BY SEQUENCE, LPAD(col2,100)
Step:3:
I have to set values of from and to according to change of rows per page,
Change of Item: P77_PAGINATION
Execute PL/SQL Code :
BEGIN
IF :P77_max = 0
THEN
: P77_FROM: = 0;
ELSE
:P77_FROM := 1;
END IF;
IF to_number(:P77_max) > NVL (to_number(:P77_pagination), 10)
THEN
–raise_application_error(-20002, ‘Pagination ‘||:P77_pagination);
:P77_TO := NVL ( to_number(:P77_pagination), 10);
ELSE
–raise_application_error(-20002, ‘Max ‘||:p86_max);
:P77_TO := :P77_max;
END IF;
END;
Step:4 :
During First Page, Last Page, Delete, Next and Previous button click, execute following PL/SQL Code
First Page:
BEGIN
:P77_FROM := 1;
:P77_TO := :P77_PAGINATION;
END;
Last Page:
DECLARE
lv_n_to NUMBER;
BEGIN
lv_n_to := TRUNC(:P77_MAX / :P77_PAGINATION);
:P77_FROM := ( lv_n_to * :P77_PAGINATION ) + 1;
:P77_TO := :P77_MAX;
END;
Next Process:
DECLARE
lv_n_from NUMBER;
lv_n_to NUMBER;
BEGIN
lv_n_to := (:P77_TO + :P77_PAGINATION );
lv_n_from := (lv_n_to – :P77_PAGINATION) + 1;
IF lv_n_to > :P77_MAX
THEN
lv_n_to := :P77_MAX;
END IF;
:P77_TO := lv_n_to;
:P77_FROM := lv_n_from;
END;
Previous Process:
DECLARE
lv_n_from NUMBER;
lv_n_to NUMBER;
BEGIN
lv_n_from := NVL (:P77_FROM, 0) – :P77_PAGINATION ;
lv_n_to := ( NVL (lv_n_from, 1) + :P77_PAGINATION ) – 1;
:P77_TO := lv_n_to;
:P77_FROM := lv_n_from;
END;
Delete Process:
DECLARE
lv_n_count NUMBER;
BEGIN
BEGIN
SELECT COUNT (*)
INTO lv_n_count
FROM apex_collections b
WHERE UPPER (collection_name) = ‘PDVD_MASTER_ITEM_DTLS’
AND ( ( UPPER
(DECODE
(TRIM(:p77_search),
‘Item / Identifier’,c003,
‘Program Name’, c004,
‘Output_Name’, c005,
‘Title’, c006,
‘Theme’, pdvd_pk_get_values.pdvd_get_lov_value(c007),
‘Item Type’, pdvd_pk_get_values.pdvd_get_lov_value
(c008),
‘Unique_Replicate’,DECODE(c009,’U’,’Unique’,’R’,’Replicate’),
‘Replicate_of’, c010,
‘Delivery Types’,c011
)
) LIKE ‘%’ || UPPER (:p77_act_value) || ‘%’
AND :p77_search IS NOT NULL
)
OR :p77_search IS NULL
);
END;
IF :p77_max = :p77_to
THEN
— raise_application_error(-20001,’Max:’||:P77_MAX||’ , To:’||:P77_TO);
:p77_to := lv_n_count;
END IF;
:p77_max := lv_n_count;
IF (TO_NUMBER (lv_n_count) <= TO_NUMBER (:p77_from))
AND lv_n_count > :p77_pagination
THEN
:p77_to := :p77_to – :p77_pagination;
:p77_from := :p77_from – :p77_pagination;
END IF;
IF lv_n_count <= :p77_pagination
THEN
:p77_to := lv_n_count;
:p77_from := 1;
END IF;
END;
After Delete, take MAX COUNT and set from and to values accordingly.
Step:5:
Set the value for Pagination Display using the following PL/SQL code during Page Load
BEGIN
IF TO_NUMBER(:P77_MAX) < TO_NUMBER(:P77_PAGINATION) AND TO_NUMBER(:P77_MAX) != 0
THEN
RETURN ‘Rows from ‘|| :P77_FROM ||’ – ‘||:P77_MAX ||’ of ‘||:P77_MAX;
ELSIF TO_NUMBER(:P77_MAX) = 0
THEN
RETURN ‘Rows from ‘|| :P77_MAX ||’ – ‘||:P77_MAX ||’ of ‘||:P77_MAX;
ELSE
RETURN ‘Rows from ‘|| :P77_FROM ||’ – ‘||:P77_TO ||’ of ‘||:P77_MAX;
END IF;
END;
Step:6:
Show buttons conditionally using following Javascript on page load
Event : Page Load
Action : Execute Javascript Code ,
var a = Number($(“#P77_TO”).val());
var b = Number($(“#P77_MAX”).val());
var c = Number($(“#P77_FROM”).val());
var d = Number($(“#P77_PAGINATION”).val());
if (a >= b && b>d)
{
$(“#NEXT”).hide();
$(“#SAVE”).show();
$(“#LAST_PAGE”).hide();
$(“#FIRST_PAGE”).show();
}
if (a < b)
{
$(“#SAVE”).hide();
$(“#NEXT”).show();
$(“#LAST_PAGE”).show();
$(“#FIRST_PAGE”).show();
}
if (c==1)
{
$(“#PREVIOUS”).hide();
$(“#FIRST_PAGE”).hide();
$(“#SAVE”).hide();
}
if ( b<d )
{
$(“#SAVE”).show();
$(“#PREVIOUS”).hide();
$(“#FIRST_PAGE”).hide();
$(“#LAST_PAGE”).hide();
$(“#NEXT”).hide();
}
Summary: This post explains about Create items to set count of rows and display.
Queries?
Do drop a note by writing us at contact@doyensys.com or use the comment section below to ask your questions.
