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;