Skip to Main Content

Oracle Forms

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Oracle forms tabulal records checkbox

user10991018Feb 19 2021

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
image.png
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;

Comments

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

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.

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

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.

1 - 4

Post Details

Added on Feb 19 2021
4 comments
289 views