How to Create a Duplicate Record In Interactive/Classic Report Oracle APEX

How to Create a Duplicate Record In Interactive/Classic Report Oracle APEX

Here is Small Tip How to Create a Duplicate Record In Interactive/Classic Report Oracle APEX. For that first wee need to create a Apex Collection which will be fire on page Load.

Dynamic Action on page load

DECLARE
   v_collection_name   VARCHAR2 (30) := 'DUPLICATE';
BEGIN
      apex_collection.create_or_truncate_collection
                                      (p_collection_name      => v_collection_name);
      FOR x IN (SELECT *  FROM emp)    LOOP
         apex_collection.add_member (p_collection_name      => v_collection_name,
                                     p_c001                 => x.empno,
                                     p_c002                 => x.ename,
                                     p_c003                 => x.deptno,
                                     p_c004                 => x.sal
                                    );
         END LOOP;
END;

• Now Based on Collection "DUPLICATE", we will Create an Interactive Report. Define column attribute in the Property Editor in security section and OFF Escape special characters.

SELECT seq_id,
APEX_ITEM.TEXT(p_idx =>  1,p_value =>  seq_id,p_item_id =>'f01_'||c001||'') seq_id_display,
c001 empno,
APEX_ITEM.TEXT(p_idx =>  2,p_value=>  c002,p_item_id =>   'f02_'||c001||'') ENAME,
APEX_ITEM.TEXT( p_idx=>3,p_value=>c003,p_item_id=>'f03_'||c001||'')  dept ,
APEX_ITEM.TEXT(p_idx =>  4,p_value  =>  c004,p_item_id =>   'f04_'||c001||'')  sal,
seq_id COPY_LINK
FROM apex_collections
WHERE collection_name = 'DUPLICATE'
ORDER BY seq_id

Report Query

• Next step is to create a page item "P1_SEQ_ID"  which will perform a dynamic action for duplicating a record. 
• Define another column SEQ_ID as COPY_LINK and the column type will be link.

Target URL:= javascript:$s("P1_SEQ_ID","#SEQ_ID#");

Link Text:=  <div class="tooltip fa fa-files-o call_collection" ria-hidden="true">                    <span class="tooltiptext">Duplicate Record</span>  </div>

• Create Item Change event dynamic action for generating duplicate record.

Onchange Dynamic Action

DECLARE
   v_collection_name   VARCHAR2 (30) := 'DUPLICATE';
   v_empno NUMBER;
BEGIN
SELECT EMP_SEQ.NEXTVAL
     INTO v_empno
     FROM DUAL;
    FOR x IN (SELECT *  FROM apex_collections where collection_name= 'DUPLICATE' and seq_id= :P1_SEQ_ID)
      LOOP
      apex_collection.add_member (p_collection_name      => v_collection_name,
                                  p_c001                 => v_empno,
                                  p_c002                 => x.c002,
                                  p_c003                 => x.c003,
                                  p_c004                 => x.c004
                                 );
                               
        end loop;
END;

• Copy and paste below CSS in page inline. This is only use for showing tooltip on duplicate link column.

.tooltip {
  position: relative;
  display: inline-block;
  border-bottom: 1px dotted black;
}
.tooltip .tooltiptext {
  visibility: hidden;
  width: 120px;
  background-color: black;
  color: #fff;
  text-align: center;
  border-radius: 6px;
  padding: 5px 0;
font-size: 12px;
  /* Position the tooltip */
  position: absolute;
  z-index: 1;
        LEFT: 20;
}
.tooltip:hover .tooltiptext {
  visibility: visible;
}

Demo