create or replace type limitS as object
     ( lIMIT1 NUMBER,
       LIMIT2 NUMBER,
      LIMIT3 NUMBER
     )

create or replace function CALCULATE_EMP_AMOUNT_MEDICINE(vEMP_ID in varchar, vCATEOGRY_ID NUMBER )

return LIMITS

is
vMEDICAL_PROGRAM NUMBER;
vLIMIT1 NUMBER;
vLIMIT2 NUMBER;
vLIMIT3 NUMBER;
vPROGRAM_ID NUMBER;
begin


-- GET MEDICAL PROGRAM OF THE EMPLOYEE
SELECT MEDICAL_PROGRAM INTO vMEDICAL_PROGRAM FROM RA_CUSTOMER_DETAILS_ALL RD
WHERE RD.CUSTOMER_ID = vEMP_ID;


-- GET PROGRAM ID FROM THE HEADER
SELECT ID INTO vPROGRAM_ID FROM MED_PROGRAMS_HEADER
WHERE PROGRAM_CODE = vMEDICAL_PROGRAM;

-- GET THE LIMIT OF THE PROGRAM
SELECT NVL(LIMIT1,0),NVL(LIMIT2,0),NVL(LIMIT3,0) INTO vLIMIT1,vLIMIT2,vLIMIT3 FROM MED_PROGRAMS_DETAILS
WHERE MED_PROGRAM_ID = vPROGRAM_ID
AND CATEOGRY_ID = vCATEOGRY_ID;


return LIMITS(vLIMIT1,vLIMIT2,vLIMIT3);
end CALCULATE_EMP_AMOUNT_MEDICINE;

select CALCULATE_EMP_AMOUNT_MEDICINE('00035310','7') from dual;

CALCULATE_EMP_AMOUNT_MEDICINE(.LIMIT1	CALCULATE_EMP_AMOUNT_MEDICINE(.LIMIT2	CALCULATE_EMP_AMOUNT_MEDICINE(.LIMIT3
300	                                              650	                                  0

DECLARE 
vLIMIT1 NUMBER;
vLIMIT2 NUMBER;
vLIMIT3 NUMBER;

BEGIN
	
select CALCULATE_EMP_AMOUNT_MEDICINE('00000704','7') INTO  vLIMIT1,vLIMIT2,vLIMIT3 from dual;

END;

DECLARE 
vLIMITS LIMITS;
vLIMIT1 NUMBER;
vLIMIT2 NUMBER;
vLIMIT3 NUMBER;

BEGIN
	
-- select CALCULATE_EMP_AMOUNT_MEDICINE('00000704','7') INTO  vLIMITS from dual;
-- but no need of SELECT
vLIMITS := CALCULATE_EMP_AMOUNT_MEDICINE('00000704','7');
vLIMIT1 := vLIMITS.LIMIT1;
vLIMIT2 := vLIMITS.LIMIT2;
vLIMIT3 := vLIMITS.LIMIT3;

END;

CREATE OR REPLACE FUNCTION calculate_emp_amount_medicine(vemp_id      IN ra_customer_details_all,customer_id%TYPE, 
                                                         vcateogry_id IN med_programs_details.cateogry_id%TYPE)
RETURN limits

IS

  vlimit1 med_programs_details.limit2%TYPE;
  vlimit2 med_programs_details.limit2%TYPE;
  vlimit3 med_programs_details.limit2%TYPE;

BEGIN

  -- Get the limits of the employees medical program
  SELECT nvl(mpd.limit1,0), nvl(mpd.limit2,0), nvl(mpd.limit3,0) 
  INTO vlimit1, vlimit2, vlimit3 
  FROM med_programs_details mpd
  JOIN med_programs_header mph ON mpd.med_program_id = mph.id
  JOIN ra_customer_details_all rd ON mph.program_code = rd.medical_program
  WHERE mpd.cateogry_id = vcateogry_id
  AND rd.customer_id = vemp_id;

  RETURN limits(vlimit1,vlimit2,vlimit3);
  
END calculate_emp_amount_medicine;