--test table
create table t_try1 (v1 number, v2 number, v3 number);

INSERT ALL
  INTO t_try1 VALUES (1, 15, 67)
  INTO t_try1 VALUES (2, 16, 3)
  INTO t_try1 VALUES (11, 85, 49)
  INTO t_try1 VALUES (22, 56, 13)
  INTO t_try1 VALUES (13, 25, 7)
  INTO t_try1 VALUES (42, 156, 31)
  INTO t_try1 VALUES (51, 35, 47)
  INTO t_try1 VALUES (2, 656, 43)
  INTO t_try1 VALUES (10, 95, 647)
  INTO t_try1 VALUES (20, 596, 443)
SELECT * FROM DUAL;


--function approach
 CREATE OR REPLACE FUNCTION F_try(Req_No in number, exch_rate_date in char default 'TODAY')   
    Return Amount2 As  
    Trans_Amts Amount2;
       
    Amt1 Number;    
    Amt2 Number; 
    STAT NUMBER;   
   
    begin
   
   SELECT V2 * 6,V1 *8
     INTO AMT1,
          AMT2
     FROM T_TRY1 
    where V3 = Req_No ; --the where clause
    Trans_Amts := AMOUNT2(AMT1,AMT2);
   DBMS_OUTPUT.PUT_LINE('Fire!');
    RETURN (Trans_Amts);
   
   END;


 SELECT  1,
           p.*
      FROM  TABLE(AMOUNT2_TBL(f_try(49))) p;



create or replace  procedure p_try
  ( param1 in number, CUR_REF     OUT SYS_REFCURSOR) as
  begin
  OPEN CUR_REF FOR SELECT 1, 
                          p.amt1 zz, p.*
  FROM TABLE (AMOUNT2_TBL(f_try(param1))) p;
 END;

CREATE OR REPLACE FUNCTION F_try3(Req_No in number, exch_rate_date in char default 'TODAY')   
    Return Amount2_tbl As  
    Trans_Amts Amount2_tbl;
    --Amt1 Number;    
    --Amt2 Number; 
    --CUR_REF                 OUT SYS_REFCURSOR;   
   
    begin

    SELECT V2 * 6,V1 *8
      into Trans_Amts(1) --<-- Iam using index = 1 for testing but I need to fill all select rows into my defined table
     FROM T_TRY1;
    --where V3 = Req_No ; --the where clause
    --Trans_Amts := AMOUNT2(AMT1,AMT2);
   DBMS_OUTPUT.PUT_LINE('Fire!');
    RETURN (Trans_Amts);
   

SQL> CREATE OR REPLACE FUNCTION F_try3(Req_No in number, exch_rate_date in char default 'TODAY')
  2      Return Amount2_tbl As
  3      Trans_Amts Amount2_tbl;
  4     begin
  5
  6      SELECT AMOUNT2(V2 * 6,V1 *8)
  7  bulk collect into Trans_Amts
  8  FROM T_TRY1;
  9  RETURN (Trans_Amts);
 10  end;
 11  /

Function created.

SQL> select * from table(F_try3(1));
      AMT1       AMT2
---------- ----------
        90          8
        96         16
       510         88
       336        176
       150        104
       936        336
       210        408
      3936         16
       570         80
      3576        160

10 rows selected.

CREATE OR REPLACE FUNCTION F_try3(Req_No in number, exch_rate_date in char default 'TODAY')
  Return Amount2_tbl
  PIPELINED
  As
  begin
      FOR v_rec IN (SELECT AMOUNT2(V2 * 6,V1 *8) amt FROM T_TRY1) LOOP
        PIPE ROW(v_rec.amt);
end;
/

SQL> CREATE OR REPLACE FUNCTION F_try3(Req_No in number, exch_rate_date in char default 'TODAY')
  2    Return Amount2_tbl
  3    PIPELINED
  4    As
  5    begin
  6        FOR v_rec IN (SELECT AMOUNT2(V2 * 6,V1 *8) amt FROM T_TRY1) LOOP
  7          PIPE ROW(v_rec.amt);
  8        end loop;
  9  end;
 10  /

Function created.

SQL> select * from table(F_try3(1));
      AMT1       AMT2
---------- ----------
        90          8
        96         16
       510         88
       336        176
       150        104
       936        336
       210        408
      3936         16
       570         80
      3576        160

10 rows selected.