How to calculate working days for an organization , if the organization works for only a particular number of week in a month

How to calculate working days for an organization , if the organization works for only a particular number of week in a month:-


We will create a function which will take current date of calendar as input and check if this input date's week is same as the one which is required.If yes then we will return '1' else
we will return '0'.

we are removing saturday and sunday as weekends and also we are removing the week with minimum number of working days and then again re-numbering the weeks as per number of days .

IS_DEPARTMENT_WORKING(V_DATE NUMBER,wnum number) RETURN NUMBER IS

cursor c_rec is Select CNT,week_of_month,row_number() over (order by count(*) desc,week_of_month)  from
(select cnt,week_of_month,
MIN(week_of_month) KEEP (DENSE_RANK FIRST ORDER BY cnt) OVER () Lowest
from(select count(*) cnt ,week_of_month ,row_number() over (order by count(*) desc,week_of_month)
from (
select x dt,
(to_char( x+1, 'iw') - to_char(to_date('01/'||to_char(x,'mon/')||to_char(x,'yyyy'),'dd/mon/yyyy') +
1,'iw') + 1)
 Week_of_month
from
(select TRUNC(TO_DATE((V_DATE ,'YYYYMMDD'), 'MONTH')+rownum-1 x
from all_objects
where rownum <= 50
)
where to_char(x,'dy','nls_date_language=english') not in ('sat','sun')
and to_char(x,'mon')=to_char(TO_DATE((V_DATE ,'YYYYMMDD'),'mon'))
group by week_of_month) )
where ((week_of_month<>nvl(lowest,0) AND CNT <5) OR (CNT=5))
GROUP BY CNT,WEEK_OF_MONTH;

cursor c_rec2 is select x dt,
(to_char( x+1, 'iw') - to_char(to_date('01/'||to_char(x,'mon/')||to_char(x,'yyyy'),'dd/mon/yyyy') +
1,'iw') + 1)
 Week_of_month
from
(select TRUNC(TO_DATE((V_DATE ,'YYYYMMDD'), 'MONTH')+rownum-1 x
from all_objects
where rownum <= 35
)
where to_char(x,'dy','nls_date_language=english') not in ('sat','sun')
and to_char(x,'mon')=to_char(TO_DATE((V_DATE ,'YYYYMMDD'),'mon');


cnt_wdays number(5);
wnumber number(2);
worgnumber number(2);
date1 DATE;
wnumber1 number(2);
flg number(1);

BEGIN
                       
           
            open c_rec ;
            open c_rec2;
            loop
            fetch c_rec into cnt_wdays,worgnumber,wnumber;
         
           
             if wnum=wnumber then
                loop
                fetch c_rec2 into date1,wnumber1;
               
                if TO_NUMBER(TO_CHAR(date1,'YYYYMMDD'))=trunc((V_DATE ) and wnumber1=worgnumber
                then
               
                flg:=1;
                exit;
                end if;
               exit when c_rec2%notfound;
                end loop;
                     
             end if;
           
           
            exit when c_rec%notfound;
            end loop;
           
            if flg=1
            then
            RETURN 1;
            else
            return 0;
            end if;
           
END;
/



The first cursor of the function is calculating the week number and removing the smallest week and again reorganizing and renumbering the week.

The second cursor is giving all the dates removing saturday and sunday and their respective week numbers.

How to escape message no changes to save in Oracle Forms;

There are various ways to solve this issue:-

KEY-COMMIT  trigger :-

BEGIN
  :System.Message_Level := 25;
  COMMIT;
  :System.Message_Level := 0;
END;
and second is at on-error trigger:-
DECLARE
  V_Error_Code       NUMBER;
  V_Error_Text       VARCHAR2 (2000);
 
BEGIN
  V_Error_Code      := Error_Code;
  V_Error_Text      := Error_Text;


  IF V_Error_Code IN (40401, 40405) THEN
   
    NULL;
 
  ELSE
 
    Show_Error (V_Error_Code);
  END IF;
END;

Enquiring the database block based on a text item:

For example you want to enquire a employee record by employee name .
then set the following value in the database where clause and we can enquire the employee record by employee name.

(REGEXP_LIKE (regexp_replace(employee_name,'[[:space:]]*',''),regexp_replace(:b_parameter.employee_name,'[[:space:]]*',''))
or :b_parameter.employee_name is null)

here the :b_parameter.employee_name is the field entered by user for enquiry and employee_name is the dabase column
for the block employee.While executing query it will match the enquired text with the employee name and will
bring the output based on it.

How to use edit_textitem in Oracle forms

For a LOB or CLOB fields its difficult to display the full content in small text box. So in that case
we can always define an EDITOR under editors section of forms and
attach this editor to the text item which is CLOB or LOB.

To call this editor we can write below code:-

Go_Item('block_name.textitem');
Edit_TextItem;

Calling a loop inside loop for two non-database blocks in form:-

Here the block1 and block2 are non database items and they are not related to each other.
And we have to consider the block1 as master and block2 as detail block. so while reading the data from both the blocks we have to read all the records of block2 for each record of block1 and to achieve that we should write the below loop statement:-

go_block('block1');
first_record;
loop
rec_num:=:system.cursor_record;
if :rnum is not null then
go_block('block2');
first_record;
loop

exit when :block.2from_amt is null;
cnt_case:=GET_TOTAL_COUNT(:block1.rnum,:block2.FROM_AMT,:block2.TO_AMT);

next_record;
end loop;
go_block('block1');
go_record(rec_num);
exit when :system.last_record='TRUE';
next_record;


end if;
end loop;

How to import data from excel-sheet to a oracle table using toad

How to import data from excel-sheet to a oracle table using toad:-

Connect to the database and create a table where you want to import the data.

go to the table and right-click ,you will get an option for import data from file.go to execute wizard.

select the file.

select the delimited -character.

see the list of fields.

map the excel fields to database-table columns.

 Atlast execute,and commit. you will find the data will be transferred to the table.

Oracle report sub query selection based on parameter value

Oracle report sub query selection based on parameter value:-

minus
IF parameter_value = 'E'THEN
(RUN THIS SELECT QUERY
union
another select query)
ELSE
(RUN THIS SELECT QUERY)
END IF

I tried to use case when but it also gives error in this case

minus
SELECT CASE WHEN parameter_value = 'E' THEN (RUN THIS SELECT QUERY
union
another select query)
ELSE (RUN THIS SELECT QUERY)
END X


so, I found another way for this

minus
(RUN THIS SELECT QUERY
union
another select query where parameter_value='E')

and it works.