-- Data Output By Use of REF CURSOR and Pipeline Function

CREATE REPLACE PACKAGE edb_out 
IS

TYPE typ_edb_row IS RECORD (
  trans_id          edb_tran.trans_id%TYPE,
  location_id       edb_trans.location_id%TYPE,
  trans_type_id     edb_tran.trans_type_id%TYPE,
  trans_date        edb_tran.trans_date%TYPE,
  trans_date_fm     NUMBER,
  trans_date_fy     NUMBER,
  invoice_num       edb_tran.invoice_num%TYPE,
  trans_amount      edb_tran.trans_amount%TYPE,
  trans_status      edb_tran.trans_status%TYPE
);

TYPE typ_trans_tbl   IS TABLE OF typ_edb_row;

rec_trans_data       typ_edb_row;

FUNCTION fn_trans_out(
       i_src_type    IN  VARCHAR2,
       i_loc_id      IN  edb_trans.location_id%TYPE,
       i_start_date  IN  DATE,
       i_end_date    IN  DATE
  )
     RETURN  typ_trans_tbl  PIPELINED;

edb_out;
/

CREATE REPLACE PACKAGE BODY edb_out 
IS

FUNCTION fn_trans_out(
       i_src_type    IN  VARCHAR2,
       i_loc_id      IN  edb_trans.location_id%TYPE,
       i_start_date  IN  DATE,
       i_end_date    IN  DATE
  )
     RETURN  typ_trans_tbl  PIPELINED AS

  TYPE      tran_typ  IS REF CURSOR;
  tran_cur  tran_typ;

  v_sdate   VARCHAR2(10) := TO_CHAR(i_start_date,'mm/dd/yyyy');
  v_edate   VARCHAR2(10) := TO_CHAR(i_end_date,'mm/dd/yyyy');
  v_tab     VARCHAR2(128);

BEGIN
  v_tab := CASE i_src_type
            WHEN 'INIT' THEN ' edb_stg_sales_tran'
            WHEN 'OPEN' THEN ' edb_opn_sales_tran'
            ELSE 'edb_sales_tran_arch'
           END;

 rec_trans_data := NULL;

 OPEN tran_cur FOR 
   'SELECT 
    trans_id, location_id, trans_type_id, trans_date,
    invoice_num,trans_amount,trans_status FROM '||v_tab||
    ' WHERE location_id = '||i_loc_id||
    ' AND trans_date BETWEEN '||
    'TO_DATE('||''''||v_sdate||''''||','||'''MM/DD/YYYY'''||')'|| 
    ' AND '||
    'TO_DATE('||''''||v_edate||''''||','||'''MM/DD/YYYY'''||')';
  LOOP
   
    FETCH tran_cur  INTO  rec_trans_data;

      --Fiscal Month in 201405, etc.
      rec_trans_data.trans_date_fm = f_Get_fm(rec_trans_data.trans_date); 

      --Fiscal Year 2014, etc.
      rec_trans_data.trans_date_fy = f_Get_fy(rec_trans_data.trans_date); 

    EXIT WHEN tran_cur%NOTFOUND;

    PIPE ROW (rec_trans_data);
  
  END LOOP;
  CLOSE tran_cur;

  RETURN;

EXCEPTION
  WHEN OTHERS THEN
    IF tran_cur%ISOPEN THEN
      CLOSE tran_cur;
    END IF;
    rec_trans_data := NULL;
   RETURN;

END fn_trans_out;

edb_out;
/