Programming Check

1---formula column  report-------------------------
function CF_mastercodeFormula return Char is
v varchar2(150);
begin
    select  :itemname||'('|| BR_ITEM_MAPPING.MASTERCODE ||')' into v 
    from br_item_mapping
  where itemcode=:itemcode;
  return(v);
exception
    when others then
    return (:itemname);
end;

--------------
2----button level change
-------------------------------
--must be control block no
--item key board/item navigator no


if :SYSTEM.MODE='NORMAL' then
    CLEAR_FORM(NO_VALIDATE);
     set_item_property('CONTROL.QRY_BTN',label,'Query');
     --do_key('enter_query');
     enter_query;
else
    go_block('BR_CUSTOMER');
        --do_key('execute_query');
    execute_query;
    set_item_property('CONTROL.QRY_BTN',label,'Search');
end if;


-------------------

3----item level when-mouse-double-click

declare
     ed_id editor;
     ed_name varchar2(40);
     val varchar2(32000);
     ed_ok boolean:=true;
begin
     ed_name:='ED_ADDR';
     ed_id:=find_editor(ed_name);
     val:=:BR_CUSTOMER.ADDRESS;
     show_editor(ed_id,val,10,14,val,ed_ok);
     :BR_CUSTOMER.ADDRESS:=val;
end;

------------------------------------

4---enable/disable
---------------------

set_item_property ('pre_btn', visible, property_true);
set_item_property ('nxt_btn', visible, property_true);
set_item_property('pre_btn',enabled,property_true);
set_item_property('nxt_btn',enabled,property_true);

5----------------------
---post-change tirgger

if :lv_cd is not null then
    select lv_nm into :lv_nm from level_info where lv_cd=:lv_cd;
    select LPAD(NVL(max(exam_cd),0)+1,3,0) into :grd_cd from exam_info;
else
    message('Select exam level ');
    raise form_trigger_failure;
end if;


6-----------------------
------program unit or key next item trigger

------DATA ALREADY INSERTED

PROCEDURE DIVS IS
dnm char(40);
BEGIN
begin
  select cost_desc into dnm
  from
  cost
  where cost_sl=:emp_div.cost_cd;
exception
    when no_data_found
    then
    dnm :=null;
END;
if dnm is not null
    then :emp_div.cost_nm :=dnm;
      message('DATA ALREADY INSERTED');
        message('DATA ALREADY INSERTED');
    raise form_trigger_failure;
else
    :emp_div.cost_nm :=dnm;
    next_field;
end if;
end;



7---------
-------WHEN-NEW-BLOCK-INSTANCE  THANA HRM

execute_query;
last_record;
next_record;


8--------
---------Invalid Examination Code


declare
    bb char(40);
begin
    select exam_name into bb
    from exam_info
    where exam_cd=:grd_cd and lv_cd = :lv_cd;
if bb is not null
    then :grd:=bb;
    next_field;
else
        raise form_trigger_failure;
    :grd_cd :=null; 
    :grd :=null;
    message('Invalid Examination Code');
    end if;
end;



9--------
-------form_success

PROCEDURE grd_code IS
BEGIN
 if form_success then
         copy('5','system.message_level');
         COMMIT_FORM; next_record;
         message('Data commited !');
         message('Data commited !');
 else
     message('Problem in Data ! Check your Data !');
     message('Problem in Data ! Check your Data !');
end if;
END;

10------------

----------INVALID COST CENTRE CODE

PROCEDURE cost_code IS
    bb char(45);
 begin
null;
    select cost_desc into bb
    from cost
    where cost_sl=:emp_desg.cost_cd;
if bb is not null
    then :emp_desg.COST_NM :=bb;
    next_field;
else
        raise form_trigger_failure;
    :emp_desg.cost_cd :=null; 
:emp_desg.cost_nm :=null;
    message('INVALID COST CENTRE CODE');
    end if;
end;


11-------

when-validate-item
-------------------

begin
if :emp_mast.div_cd is not null then
        message('Are you sure Employee Division is Correct...??');
        message('Are you sure Employee Division is Correct...??');
    else
    null;
    raise form_trigger_failure;
end if;
end;

--message


12---------------
---INVALID EMPLOYEE ID

PROCEDURE EMP_CODE2 IS
BEGIN
begin
select a.emp_nm,a.desg_cd, c.desg_nm,a.dept_cd,f.dept_nm,a.sec_cd,g.sec_nm,a.cost_sl,h.cost_desc
into
:emp_desg.NM,:emp_desg.deg,:emp_desg.deptnm1,:emp_desg.dept_cd,:emp_desg.dept,
:emp_desg.sec_cd,:emp_desg.sec,:emp_desg.cost_cd,:emp_desg.cost_nm
from emp_mast a,emp_desg c,emp_dept f,emp_sec g,cost h
where a.emp_id=:emp_id and
        a.desg_cd=c.desg_cd and
      a.dept_cd=f.dept_cd(+)  and
      a.dept_cd=g.dept_cd(+) and
      a.sec_cd=g.sec_cd(+)  and
      a.cost_sl=h.cost_sl;
      --pbas;
next_field;
EXCEPTION
    WHEN NO_DATA_FOUND
    THEN
        message('INVALID EMPLOYEE ID !  ');
raise form_trigger_failure;
END;
 end;   


----13 DATA ALRADY POSTED TRY WITH NEW ONE
---------------------------------------------

PROCEDURE ptp_code IS
BEGIN
  select a.dept_cd,c.dept_nm,a.sec_cd,d.sec_nm,
         a.cost_sl,e.cost_desc,a.pt_dt,a.flg_tp
into :emp_desg.dept_cd,:emp_desg.dept,
:emp_desg.sec_cd,:emp_desg.sec,:emp_desg.cost_cd,:emp_desg.cost_nm,
:emp_desg.birth_dt,:emp_desg.tp
from emp_posting a,emp_dept c,emp_sec d,cost e
where a.com_cd = :com_cd and a.emp_id=:emp_desg.emp_id and
      a.cir_no=:emp_desg.circ_no and
      a.cir_dt=:emp_desg.doj and
      a.dept_cd=c.dept_cd and
      a.dept_cd=d.dept_cd and
      a.sec_cd=d.sec_cd and
      a.cost_sl=e.cost_sl;
 message('DATA ALRADY POSTED TRY WITH NEW ONE');
 raise form_trigger_failure;
exception
    when no_data_found
    then
    next_field;
END;



----------------
error set in oracle forms 10g
------------------------------
---ON-ERROR trigger form level


DECLARE
   lv_errcod  NUMBER       := ERROR_CODE;
   lv_errtyp  VARCHAR2(3)  := ERROR_TYPE;
   lv_errtxt  VARCHAR2(80) := ERROR_TEXT;
BEGIN
   IF (lv_errcod = 40508) THEN
       message('UNABLE TO INSERT RECORD: DUPLICATE CODE ENTRY');
       message('UNABLE TO INSERT RECORD: DUPLICATE CODE ENTRY');
       RAISE Form_Trigger_Failure;
    ELSIF (lv_errcod = 40510) THEN
        message('UNABLE TO DELETE RECORD: DATA EXISTS IN SUB GROUP');
       message('UNABLE TO DELETE RECORD: DATA EXISTS IN SUB GROUP');
       RAISE Form_Trigger_Failure;
    ELSIF (lv_errcod = 40509) THEN
       message('UNABLE TO UPDATE RECORD: DATA EXISTS IN SUB GROUP / DUPLICATE RECORD');
       message('UNABLE TO UPDATE RECORD: DATA EXISTS IN SUB GROUP / DUPLICATE RECORD');
       RAISE Form_Trigger_Failure;
    ELSE
   Message(lv_errtyp||'-'||to_char(lv_errcod)||': '||lv_errtxt);
   Message(lv_errtyp||'-'||to_char(lv_errcod)||': '||lv_errtxt);
   RAISE Form_Trigger_Failure;
    END IF;
END;



/

---------------------------------------
values set

---WHEN-VALIDATE-ITEM

if :slcode is not null then
    select nvl(max(mlink),0)+1 into :mlink from sl_code;        -----mlink number
else
    null;
end if;


--------
HRM(star)
----
when-validate_item

declare
    v number;
begin
    select count(distinct at_dt) into v from at_proc_mast where at_Dt =:dtd;
   
    if v>0 then
        message('Data Already Process...');
        RAISE FORM_TRIGGER_FAILURE;
    else
        null;
    end if;
   
end;



----set pre insert/ auto numner lpad 0001
--------------------------------------------

begin
    select lpad(nvl(to_number(max(mv_no)+1),1),4,('0'))
into :mov_info.mv_no
    from tour_info
    where at_dt=:mov_info.at_dt;
exception
    when no_data_found then
    null;
end;




end dt is grater then st_dt/at_dt then null
----------------------------------------------


begin
if :mov_info.edt<:mov_info .at_dt="" br="" then="">    raise form_trigger_failure;
else
    null;
    end if;
end;



----------------------------------------------
----validate date

declare
    v number;
begin
    select count(distinct at_dt) into v from at_proc_mast where at_Dt =:dtd;
   
    if v>0 then
        message('Data Already Process...');
        raise form_trigger_failure;
    else
        null;
    end if;
end;


--------


declare
    v number;
begin

    SELECT count(cust_id) INTO V FROM br_customer WHERE cust_id=:BR_CUSTOMER.cust_id;
    if v>0 then
            MESSAGE('Cusomer Id Already Exist !!');
          MESSAGE('Cusomer Id Already Exist !!');   
        raise form_trigger_failure;
    else
    next_field;
    end if;
end;

-------------------------------------------------


pre_insert
-------------

field varchar2
SELECT NVL(MAX(TO_NUMBER(recpt_no)),0)+1 into :br_receipt_master.recpt_no from br_receipt_master;


id restriction
---------------
declare
    v varchar2(6);
begin
    select emp_id into v from hrm.emp_mast where emp_id='001032';
    if :auth_by=v then
select emp_nm into :auth_nm from hrm.emp_mast where emp_id = :auth_by;
    else
        :auth_nm:='';
        :auth_by:='';
    end if;
    end;
next_field;


------------

set field data form an others field
----------------------------------

go_block('emp_attn_data');
FIRST_RECORD;
-- WHILE TRUE LOOP
WHILE :System.Last_Record <> 'TRUE' LOOP
    if :emp_attn_data.flag is not null then
        null;
    elsif  :emp_attn_data.fst_in<>'000000' then
        null;
    else
        :emp_attn_data.fst_in:=:con.in_time;
        :emp_attn_data.fst_out:=:con.out_time;
    end if;
NEXT_RECORD;
IF :System.Last_Record = 'TRUE' THEN
    if :emp_attn_data.flag is not null then
        null;
    elsif  :emp_attn_data.fst_in<>'000000' then
        null;
    else
    :emp_attn_data.fst_in:=:con.in_time;
    :emp_attn_data.fst_out:=:con.out_time;
    end if;
EXIT;
END IF;
END LOOP;
first_record;



/

when-new-form_instarnce
-----------------------
:clock:=to_char(sysdate,'HH12:MI:SS');


-----------valid & invalid -----------

declare
    v number;
begin
    select dept_cd into v  from emp_dept where dept_cd=:dept_cd;
   
if v>0 then
  select dept_nm into :dept_nm from emp_dept where dept_cd=:dept_cd;

else
    message('Invalid Department id !!');
end if;
exception
    when others then
    raise form_trigger_failure;
end;


/

between date check
-----------------------
table data
-----------
26-JUL-17    25-AUG-17
26-AUG-17    25-SEP-17
26-SEP-17    25-OCT-17



declare
v number;

begin
    select count(*)  into v from month where end_dt>=:emp_desg.st_dt and end_dt <:emp_desg .end_dt="" br="">
if v>0  then
    message('End Date must be less then or equal End Month ');
    message('End Date must be less then or equal End Month ');
    clear_form;
else   
:emp_desg.ap_day:=(:emp_desg.end_dt-:emp_desg.st_dt)+1;
next_item;
end if;
end;