Manual Scrollable Master Detail Page in oracle apex part-2

 ** Please find the previous part-1 text instruction in :


https://learnoracle21.blogspot.com/2024/01/manual-scrollable-master-detail-page-in.html


Manual Scrollable Master Detail Page in oracle apex part-2


Add a plugins named Select2 in shared component for item visualization.


Download plugins using url: http://apex.oracle.com/pls/apex/f?p=64237:20


Step-1


Create a static region names

Employee Detail Info and add a server side condition =ITEM IS NOT NUll item = P16_ID (To display the region)) 


add the following item in employee_Detail_info region:


P16_TEMP_ID 

type=hidden value protected= no

add a dynamic action called store_temp_id:

action= execute server side code:


begin

for i in (SELECT  ID,EMPLOYEE_ID,EMPLOYEE_BASIC_ID,DEPT_ID,SAL_GRA_BAS_ID,SBU_ID,UNIT_ID,DIVISSION_ID,DISTRICT_ID,THANA_ID,

          DESIGNATION_ID,GRADE_ID,EMPLOYEE_TYPE_ID,SHIFT_ID,ZONE_ID,BRANCH_ID ,IS_ACTIVE,SYSTEM_DATE,service_type ,service_catagory,emp_cat,EMP_DUTY_TYPE

from EMPLOYEE_BASIC_CONTEMPORARY

where id = :P16_TEMP_ID)

loop

 :P16_TEMP_EMP_ID := i.EMPLOYEE_ID;

 :P16_EMP_BA_ID := i.EMPLOYEE_BASIC_ID;

 :P16_TEM_DEPT_ID := i.DEPT_ID;

 :P16_TEM_SALARY_BAS_ID := i.SAL_GRA_BAS_ID;

 :P16_TEM_SBU_ID := i.SBU_ID;

 :P16_TEM_UNIT_ID := i.UNIT_ID;

 :P16_TEM_DIVISSION_ID := i.DIVISSION_ID;

 :P16_TEM_DISTRICT_ID := i.DISTRICT_ID;

 :P16_TEM_THANA_ID := i.THANA_ID;

 :P16_TEM_DESIG_ID := i.DESIGNATION_ID;

 :P16_TEMP_GRA_ID := i.GRADE_ID;

 :P16_TEMP_EMP_TYPE_ID := i.EMPLOYEE_TYPE_ID;

 :P16_TEMP_SHIFT_ID := i.SHIFT_ID;

 :P16_TEMP_ZONE_ID := i.ZONE_ID;

 :P16_TEMP_BRANCH_ID := i.BRANCH_ID;

 :P16_IS_ACTIVE := i.IS_ACTIVE;

 :P16_SYSTEM_DATE := i.SYSTEM_DATE;

 :P16_SER_TYPE := i.service_type;

 :P16_SER_CATG := i.service_catagory;

 :P16_EMP_CAT  := i.emp_cat;

 :P16_EMP_DUTY_TYPE:=i.EMP_DUTY_TYPE;

end loop;

end;


items to submit = P16_TEMP_ID

items to return = P16_TEMP_EMP_ID,P16_EMP_BA_ID,P16_TEM_DEPT_ID,P16_TEM_SALARY_BAS_ID,P16_TEM_SBU_ID,P16_TEM_UNIT_ID,P16_TEM_DIVISSION_ID,P16_TEM_DISTRICT_ID,P16_TEM_THANA_ID,P16_TEM_DESIG_ID,P16_TEMP_GRA_ID,P16_TEMP_EMP_TYPE_ID,P16_TEMP_SHIFT_ID,P16_TEMP_ZONE_ID,P16_TEMP_BRANCH_ID,P16_IS_ACTIVE,P16_SYSTEM_DATE,P16_SER_TYPE,P16_SER_CATG,P16_EMP_CAT,P16_EMP_DUTY_TYPE


P16_EMP_BA_ID as select list

LOV= sql query:

select  FAST_NAME||' '||MNAME||' '||LNAME d,ID r

from employee_basic

where id = :P16_ID;


P16_TEM_DEPT_ID 

as select2 plugins

LOV= sql query:

select name,id from department_info;


P16_TEM_DESIG_ID 

as select2 plugins

label= Designation

LOV= sql query:

select name,id from designation

order by grade_id;


P16_TEM_SBU_ID 

as select2 plugins

label= Office

LOV= sql query:

select name,id from sbu;


P16_TEM_UNIT_ID

as select2 plugins

Label= Unit

LOV= sql query:

select name,id from unit;


P16_TEMP_GRA_ID

as select2 plugins

Label= Grade

LOV= sql query:

 select G.DISPLAY_NAME , G.ID

 from designation d, GRADE G

 where D.GRADE_ID=G.ID

 and D.ID=:P16_TEM_DESIG_ID;

 

Cascading List of Values:

Parent Item(s)=P16_TEM_DESIG_ID

Items to Submit=P16_TEMP_GRA_ID

Parent Required=yes


P16_TEM_DIVISSION_ID

as select2 plugins

Label= Division

LOV= sql query:

select name,id from division;


P16_TEM_DISTRICT_ID

as select2 plugins

Label= District

LOV= sql query:

select name,id from district

where DIVISION_ID=:P16_TEM_DIVISSION_ID;

Cascading List of Values:

Parent Item(s)=P16_TEM_DIVISSION_ID

Parent Required=yes


P16_TEM_THANA_ID

as select2 plugins

Label= Thana

LOV= sql query:

select name,id from thana

where DISTRICT_ID=:P12_TEM_DISTRICT_ID;

Cascading List of Values:

Parent Item(s)=P12_TEM_DISTRICT_ID

Parent Required=yes


P16_TEMP_BRANCH_ID

as select2 plugins

Label= Branch

LOV= sql query:

select name,id from branch_info;


P16_TEMP_ZONE_ID

as select2 plugins

Label= Zone

LOV= sql query:

select name,id from zone_info;


P16_EMP_DUTY_TYPE

as select2 plugins

Label= Duty Type

LOV= Static value: General-G, Shift-S.


P16_TEMP_SHIFT_ID

as select2 plugins

Label= Shift Type

LOV= sql query:

select SHIFT_NAME,SHIFT_ID 

from ATT_shift_info

WHERE DUTY_TYPE=:P16_EMP_DUTY_TYPE;

Cascading List of Values:

Parent Item(s)=P16_EMP_DUTY_TYPE

Parent Required=yes


P16_SER_TYPE

as select2 plugins

Label= Service Type

LOV= Static value: In-Service - IS, Retirement=R, LPR=L, Terminate=T


P16_SER_CATG

as select2 plugins

Label= Service Category

LOV= Static value: Permanent - P, Intern=I, Temporary=T


P16_EMP_CAT

as select2 plugins

Label= Employee Category

LOV= Static value: Officer - O, Staff=S


P16_TEMP_EMP_TYPE_ID as hidden

as hidden

Default static=1


P16_TEM_SALARY_BAS_ID 

as hidden


P16_IS_ACTIVE 

as hidden

Default static=Y


P16_SYSTEM_DATE 

as hidden default 

PLSQL: sysdate


Also a sub region type interactive report named temp_view using this SQL Query:


select ID,EMPLOYEE_ID,

(select  FAST_NAME||' '||MNAME||' '||LNAME "Full Name" from employee_basic where id = a.EMPLOYEE_BASIC_ID) employee,

--(select FNAME||'  '||MIDLENAME||'  '|| LNAME as name from EMPLOYEE_INFORMATION where id=a.EMPLOYEE_BASIC_ID) employee,

(select name from department_info  where id=a.DEPT_ID) department,

(select BASIC_VALUE from salary_grade_basic where id=a.SAL_GRA_BAS_ID) "salary grade basic",

(select name from sbu where id=a.SBU_ID) sbu,

(select name from unit where id=a.UNIT_ID) unit,

(select name from division where id=a.DIVISSION_ID) division,

(select name from district where id=a.DISTRICT_ID) district,

(select name from thana where id=a.THANA_ID) thana,

(select name from designation where id=a.DESIGNATION_ID) designation,

(select name from grade where id=a.GRADE_ID) grade,

(select EMP_TYPE_DIS from employee_type where sl=a.EMPLOYEE_TYPE_ID) "employee type",

decode(service_type,'IS','In-Service','R','Retirement','L','LPR','R','RPR','T','Terminate',service_type) service_type,

decode(service_catagory, 'P','Permanent','PV','Provition', 'I','Intern','T','Temporary','D','Daly Basis',service_catagory) service_catagory,

decode(emp_cat,'O','Officer','S','Staff',emp_cat) Employee_Catagory,

decode(EMP_DUTY_TYPE,'G','General','S','Shift') "Duty TYPE",

(select SHIFT_NAME from ATT_shift_info where SHIFT_ID=a.SHIFT_ID) shift,

SYSTEM_DATE,--IS_ACTIVE,

decode(IS_ACTIVE,'Y','Is active','N','Inactive') is_active,

ZONE_ID,BRANCH_ID 

from EMPLOYEE_BASIC_CONTEMPORARY a

where a.EMPLOYEE_BASIC_ID = :P16_ID


IN Employee Detail Info region:


add 4 Buttons for transaction


Button1: temp_clear


add a dynamic action name: temp_clear

event=click

selection type=button

true action=clear

Affected Elements= P16_TEMP_EMP_ID, P16_EMP_BA_ID, P16_TEM_DEPT_ID, P16_TEM_SALARY_BAS_ID, P16_TEM_SBU_ID, P16_TEM_UNIT_ID, P16_TEM_DIVISSION_ID, P16_TEM_DISTRICT_ID, P16_TEM_THANA_ID, P16_TEM_DESIG_ID, P16_TEMP_GRA_ID, P16_TEMP_EMP_TYPE_ID, P16_TEMP_SHIFT_ID, P16_TEMP_ZONE_ID, P16_TEMP_BRANCH_ID,P16_SYSTEM_DATE,P16_SER_TYPE,P16_SER_CATG,P16_EMP_CAT,P16_EMP_DUTY_TYPE


Button2: temp_insert


add a dynamic action name: temp_create

event=click

selection type=button


true action section add 4 true action


Act-1- Action= Execute Server Side Code

Language= PL-SQL Code: 


begin

if :P16_TEMP_EMP_ID Is not NULL THEN

INSERT INTO EMPLOYEE_BASIC_CONTEMPORARY(

    ID,

    EMPLOYEE_ID,

    EMPLOYEE_BASIC_ID,

    DEPT_ID,

    SAL_GRA_BAS_ID,

    SBU_ID,

    UNIT_ID,

    DIVISSION_ID,

    DISTRICT_ID,

    THANA_ID,

    DESIGNATION_ID,

    GRADE_ID,

    EMPLOYEE_TYPE_ID,

    SHIFT_ID,

    ZONE_ID,

    BRANCH_ID,

    SYSTEM_DATE,

    IS_ACTIVE,service_type ,service_catagory,emp_cat,EMP_DUTY_TYPE,JOINING_DATE

)

VALUES(

    SEQ_EMPLOYEE_BASIC_CONT.nextval,

    :P16_TEMP_EMP_ID,

    :P16_EMP_BA_ID,

    :P16_TEM_DEPT_ID,

    :P16_TEM_SALARY_BAS_ID,

    :P16_TEM_SBU_ID,

    :P16_TEM_UNIT_ID,

    :P16_TEM_DIVISSION_ID,

    :P16_TEM_DISTRICT_ID,

    :P16_TEM_THANA_ID,

    :P16_TEM_DESIG_ID,

    :P16_TEMP_GRA_ID,

    :P16_TEMP_EMP_TYPE_ID,

    :P16_TEMP_SHIFT_ID,

    :P16_TEMP_ZONE_ID,

    :P16_TEMP_BRANCH_ID,

    :P16_SYSTEM_DATE,

    :P16_IS_ACTIVE,:P16_SER_TYPE,:P16_SER_CATG, :P16_EMP_CAT, :P16_EMP_DUTY_TYPE,:P16_JOINING_DATE

);

ELSE 

RAISE_APPLICATION_ERROR(-29863,'Some item data is missing....!');

END IF;

end;


Items to submit= P16_TEMP_EMP_ID, P16_EMP_BA_ID, P16_TEM_DEPT_ID, P16_TEM_SALARY_BAS_ID, P16_TEM_SBU_ID, P16_TEM_UNIT_ID, P16_TEM_DIVISSION_ID, P16_TEM_DISTRICT_ID,  P16_TEM_THANA_ID, P16_TEM_DESIG_ID,  P16_TEMP_GRA_ID, P16_TEMP_EMP_TYPE_ID,  P16_TEMP_SHIFT_ID, P16_TEMP_ZONE_ID, P16_TEMP_BRANCH_ID,P16_SER_TYPE,P16_SER_CATG,P16_EMP_CAT,P16_IS_ACTIVE,P16_SYSTEM_DATE,P16_EMP_DUTY_TYPE


* Act-2- add a dynamic action name action:clear

selection type=items

Affected Elements= P16_TEMP_EMP_ID, P16_EMP_BA_ID, P16_TEM_DEPT_ID, P16_TEM_SALARY_BAS_ID, P16_TEM_SBU_ID, P16_TEM_UNIT_ID, P16_TEM_DIVISSION_ID, P16_TEM_DISTRICT_ID,  P16_TEM_THANA_ID, P16_TEM_DESIG_ID,  P16_TEMP_GRA_ID, P16_TEMP_EMP_TYPE_ID,  P16_TEMP_SHIFT_ID, P16_TEMP_ZONE_ID, P16_TEMP_BRANCH_ID,P16_SER_TYPE,P16_SER_CATG,P16_EMP_CAT,P16_IS_ACTIVE,P16_SYSTEM_DATE


Act-3- add a dynamic action name action:refresh

affected elements section: selection type=region

region=temp_view


Act-4- add a dynamic action name 

action:submit page


Button3: temp_update

Add a dynamic action name: temp_update


true action section add 3 true action


* Act-1- Action= Execute Server Side Code

Language= PL-SQL Code: 


begin

if :P16_TEMP_ID is not null then

update EMPLOYEE_BASIC_CONTEMPORARY

set 

EMPLOYEE_ID         = :P16_TEMP_EMP_ID,

EMPLOYEE_BASIC_ID   = :P16_EMP_BA_ID,

DEPT_ID             = :P16_TEM_DEPT_ID,

SAL_GRA_BAS_ID      = :P16_TEM_SALARY_BAS_ID,

SBU_ID              = :P16_TEM_SBU_ID,

UNIT_ID             = :P16_TEM_UNIT_ID,

DIVISSION_ID        = :P16_TEM_DIVISSION_ID,

DISTRICT_ID         = :P16_TEM_DISTRICT_ID,

THANA_ID            = :P16_TEM_THANA_ID,

DESIGNATION_ID      = :P16_TEM_DESIG_ID,

GRADE_ID            = :P16_TEMP_GRA_ID,

EMPLOYEE_TYPE_ID    = :P16_TEMP_EMP_TYPE_ID,

SHIFT_ID            = :P16_TEMP_SHIFT_ID,

ZONE_ID             = :P16_TEMP_ZONE_ID,

BRANCH_ID           = :P16_TEMP_BRANCH_ID,

SYSTEM_DATE         = :P16_SYSTEM_DATE,

IS_ACTIVE           = :P16_IS_ACTIVE,

service_type        = :P16_SER_TYPE,

service_catagory    = :P16_SER_CATG,

emp_cat             = :P16_EMP_CAT,

EMP_DUTY_TYPE       = :P16_EMP_DUTY_TYPE

where 

id = :P16_TEMP_ID;

else

raise_application_error(-20456, 'this is not updated value....');

end if;

end;


items to submit= P16_TEMP_EMP_ID, P16_EMP_BA_ID, P16_TEM_DEPT_ID, P16_TEM_SALARY_BAS_ID, P16_TEM_SBU_ID, P16_TEM_UNIT_ID, P16_TEM_DIVISSION_ID, P16_TEM_DISTRICT_ID, P16_TEM_THANA_ID, P16_TEM_DESIG_ID, P16_TEMP_GRA_ID, P16_TEMP_EMP_TYPE_ID, P16_TEMP_SHIFT_ID, P16_TEMP_ZONE_ID, P16_TEMP_BRANCH_ID,P16_IS_ACTIVE,P16_SYSTEM_DATE,P16_SER_CATG,P16_SER_TYPE,P16_EMP_CAT,P16_EMP_DUTY_TYPE


* Act-2- add a dynamic action name action:clear

selection type=items

Affected Elements= P16_TEMP_EMP_ID, P16_EMP_BA_ID, P16_TEM_DEPT_ID, P16_TEM_SALARY_BAS_ID, P16_TEM_SBU_ID, P16_TEM_UNIT_ID, P16_TEM_DIVISSION_ID, P16_TEM_DISTRICT_ID, P16_TEM_THANA_ID, P16_TEM_DESIG_ID, P16_TEMP_GRA_ID, P16_TEMP_EMP_TYPE_ID, P16_TEMP_SHIFT_ID, P16_TEMP_ZONE_ID, P16_TEMP_BRANCH_ID,P16_SYSTEM_DATE,P16_SER_TYPE,P16_SER_CATG,P16_EMP_CAT


Act-3- add a dynamic action name action:refresh

affected elements section: selection type=region

region=temp_view



Button4: temp_delete


Add a dynamic action name: temp_delete


true action section add 4 true action


Act-1: Execute server side code:


begin

if :P16_TEMP_ID is not null then

delete from EMPLOYEE_BASIC_CONTEMPORARY

where id = :P16_TEMP_ID ;

end if;

end;


items to submit= P16_TEMP_ID


* Act-2- add a dynamic action name action:clear

selection type=items

Affected Elements= P16_TEMP_EMP_ID, P16_EMP_BA_ID, P16_TEM_DEPT_ID, P16_TEM_SALARY_BAS_ID, P16_TEM_SBU_ID, P16_TEM_UNIT_ID, P16_TEM_DIVISSION_ID, P16_TEM_DISTRICT_ID, P16_TEM_THANA_ID, P16_TEM_DESIG_ID, P16_TEMP_GRA_ID, P16_TEMP_EMP_TYPE_ID, P16_TEMP_SHIFT_ID, P16_TEMP_ZONE_ID, P16_TEMP_BRANCH_ID,P16_SYSTEM_DATE,P16_SER_TYPE,P16_SER_CATG,P16_EMP_CAT


Act-3- add a dynamic action name action:refresh

affected elements section: selection type=region

region=temp_view


Act-4- add a dynamic action name 

action:submit page


Now save and run the page.

Comments

Post a Comment

Popular posts from this blog

Image
  Jasper Server and studio complete installation step by step   Step -1   Download 1. jaspersoft studio 2. jasperserver from official TIBCO website   Step-2   Install jaspersoft studio Install jasperserver   Step 3   Jasper Report Integration   First Check your System Environment Variable (For Windows same as linux)   Right clock on: This PC/My Computer icon ->Properties ->Advance System ->Advance ->Environment Variable ->System Variable ->select Path ->edit ->new ->C:\Windows\SysWOW64 ->move up under C:\Windows\System32 position ->apply ok ->ok   Install JDK latest version (if not installed)   again add jre path to environment variable   Right clock on: This PC/My Computer icon ->Properties ->Advance System ->Advance ->Environment Variable ->System Variable ->select Path ->edit -...
Read more

Oracle apex login page beautification Tutorial

Image
  Oracle apex login page beautification Tutorial Script: 1. First create or modify your login page 2. Change login page template as go to page level property -> Appearance -> Template Options -> set template page layout as split 3. Download a background image from freepik.com 4. upload this background image in your apex application shared folder: 4. Add the below javascript code to your login page -> page level -> inline -> paste the code Javascript code: .t-PageBody--login form#wwvFlowForm{ background:url( #WORKSPACE_FILES #Capture.jpg); background-repeat:no-repeat; background-Size:cover; } Span.t-Login-logo{ background-image : url( #WORKSPACE_FILES #logo_learn_oracle.png); background-Size :cover; width :75px; height :75px; } .t-Login-region{ position:relative; width:400px; --height:300px; margin:0Auto; background:rgba(165, 145, 145, 0.3); padding:20px 22px; border:1px solid; border-top-color: #fbce4a ; border-l...
Read more
  Manual Scrollable Master Detail Page in Oracle Apex part-1 Step -1 create a page classic Report Page (All Employees) using this sql query : select aa.ID, aa.FAST_NAME||' '||aa.MNAME||' '||aa.LNAME "Full Name", aa.FATHER_NAME, aa.MOTHER_NAME, aa.JOINING_DATE, aa.NID, AA.TIN, aa.EMAIL, aa.PASSPORT, aa.MOBILE, (SELECT CASE WHEN NVL (DBMS_LOB.getlength (EMPLOYEE_IMAGE), 0) = 0 THEN NULL ELSE '<img width="80px" src="' || APEX_UTIL. get_blob_file_src ('P16_EMPLOYEE_IMAGE', ID) || '" />' END FROM EMPLOYEE_BASIC where id=aa.id) AS PICTURE from EMPLOYEE_BASIC aa Step-3 add a button called CREATE as level (New Employee) In Button Behaviour property redirect this page to form page (Page 23). Step-4 In form Page: *in ...
Read more