Forum Stats

  • 3,722,817 Users
  • 2,244,418 Discussions
  • 7,850,110 Comments

Discussions

Howdy, Stranger!

It looks like you're new here. If you want to get involved, click one of these buttons!

Oracle forms tabulal records checkbox

user10991018
user10991018 Member Posts: 118 Blue Ribbon

Hi

I have a requirement in oracle forms as follows

i will have a multirecord blcok with sample data and sec_suprvisory is the UI item to check if that client has more than one supervisor

i wrote function but it is always returning the count as more than 1 but i want to show for same client if have more than one primary supervisor for eg 1315

only two secsup checkbox to be checked and the second one should not be checked as second row is primary supervisor. can you please advise.

thanks very much


sample data

CREATE TABLE "EMP_SUP" 

  ( "CLIENT_ID" NUMBER(4,0), 

"PRIMARY_SUP" VARCHAR2(20 BYTE)

  )


Insert into EMP_SUP (CLIENT_ID,PRIMARY_SUP) values (1315,'N');

Insert into EMP_SUP (CLIENT_ID,PRIMARY_SUP) values (1315,'Y');

Insert into EMP_SUP (CLIENT_ID,PRIMARY_SUP) values (1314,'Y');

Insert into EMP_SUP (CLIENT_ID,PRIMARY_SUP) values (1315,'Y');

Insert into EMP_SUP (CLIENT_ID,PRIMARY_SUP) values (1315,'N');

create or replace FUNCTION  SECSUP_IND

RETURN  VARCHAR2  IS


ln_SECSUP_cnt NUMBER;

lv_SECSUP_ind VARCHAR2(2);

lv_SUP_IND VARCHAR2(2);

BEGIN

 SELECT 

  count(1)

   INTO ln_SECSUP_cnt

   FROM EMP_SUP  

  WHERE EMP_SUP.CLIENT_ID=:client_id   

    AND PRIMARY_SUP='N';

         

     IF ln_SECSUP_cnt >= 1 THEN

lv_SECSUP_ind := 'Y';

  ELSE

lv_SECSUP_ind := 'N';

  END IF;

  RETURN ( lv_SECSUP_ind );

END SECSUP_IND;





   

   

Answers

  • Michael Ferrante-Oracle
    Michael Ferrante-Oracle Member Posts: 6,490 Employee

    It seems what you might be missing is an IF statement around the code that is checking the lv_SECSUP_ind item to see if the current row is PRIMARY_SUP.

    If PRIMARY_SUP = 'N' Then
         IF ln_SECSUP_cnt >= 1 THEN
              lv_SECSUP_ind := 'Y';
         ELSE
              lv_SECSUP_ind := 'N';
         END IF;
    End if;
    

    The result should be that only rows that are not "Primary" can be checked.

    Is that what you were trying to accomplish??

  • user10991018
    user10991018 Member Posts: 118 Blue Ribbon

    thanks this is working ok at forms level

    but i am trying to write this function at database level so that i can call the function in reports to show the sec supervisor information

    i tried to use cursor and pass client id as a parameter but the result is not coming correctly.

    can you please advise.

    thanks.

  • Michael Ferrante-Oracle
    Michael Ferrante-Oracle Member Posts: 6,490 Employee

    I don't understand. You started this thread suggesting you couldn't get it to work in Forms. Did you change something in order to make it work in Forms? Also, if you are trying to do this as a DB proc why duplicate the code in Forms? Have you considered doing this in a PL/SQL library (PLL) and attaching to your report? If you create it generic enough the same could be used by the form too.

  • user10991018
    user10991018 Member Posts: 118 Blue Ribbon

    thanks for the response.

    yes initially I started to put the code only in forms but now it is required in reports also

    I am trying to write a generic procedure in a package to use both in forms and reports.


    thanks.

Sign In or Register to comment.