-- Run this scrpt from sqlplus to create the Names and Addresses report for hosptital.
-- edit the spool destination to create the csv file in the desired location.
--drop general table

drop table  gena_rpt_tbl;

--recreate general table based on year

CREATE TABLE GENA_RPT_TBL
(
  ALPHNMRC_ITM_TXT    CHAR(40 BYTE),
  RPT_REC_NUM         NUMBER(7),
  PRVDR_CTRL_TYPE_CD  CHAR(2 BYTE),
  PRVDR_NUM           CHAR(6 BYTE),
  FY_BGN_DT           DATE,
  FY_END_DT           DATE,
  RPT_STUS_CD         VARCHAR2(15 BYTE),
  WKSHT_CD            CHAR(7 BYTE),
  LINE_NUM            CHAR(5 BYTE),
  CLMN_NUM            CHAR(4 BYTE)
);
CREATE INDEX GA1 ON GENA_RPT_TBL
(CLMN_NUM);

CREATE INDEX GA2 ON GENA_RPT_TBL
(LINE_NUM);

CREATE INDEX GA3 ON GENA_RPT_TBL
(PRVDR_NUM);

CREATE INDEX GA4 ON GENA_RPT_TBL
(RPT_REC_NUM);

CREATE INDEX GA5 ON GENA_RPT_TBL
(WKSHT_CD);

insert into gena_rpt_tbl (SELECT hosp_rpt_alphnmrc_expy_vw.alphnmrc_itm_txt,
       hosp_rpt_expy_tbl1.rpt_rec_num, hosp_rpt_expy_tbl1.prvdr_ctrl_type_cd,
       hosp_rpt_expy_tbl1.prvdr_num, hosp_rpt_expy_tbl1.fy_bgn_dt,
       hosp_rpt_expy_tbl1.fy_end_dt,
       DECODE (hosp_rpt_expy_tbl1.rpt_stus_cd,
               '1', 'As Submitted',
               '2', 'Settled',
               '3', 'Settled w/Audit',
               '4', 'Reopened',
               '5', 'Amended',
               '_NONE'
              ) AS rpt_stus_cd,
       hosp_rpt_alphnmrc_expy_vw.wksht_cd, hosp_rpt_alphnmrc_expy_vw.line_num,
       hosp_rpt_alphnmrc_expy_vw.clmn_num
  FROM hosp.hosp_rpt_alphnmrc_expy_tbl hosp_rpt_alphnmrc_expy_vw,
       hosp.hosp_rpt_expy_tbl hosp_rpt_expy_tbl1
 WHERE ((hosp_rpt_expy_tbl1.rpt_rec_num =
                                         hosp_rpt_alphnmrc_expy_vw.rpt_rec_num
        )
       )
   AND (((       hosp_rpt_alphnmrc_expy_vw.wksht_cd = 'S200000'
             AND hosp_rpt_alphnmrc_expy_vw.line_num IN ('00100', '00101')
          OR     hosp_rpt_alphnmrc_expy_vw.wksht_cd = 'S200000'
             AND hosp_rpt_alphnmrc_expy_vw.line_num IN ('00200', '02100')
             AND hosp_rpt_alphnmrc_expy_vw.clmn_num = '0100'
         )
        )
       )
   AND (hosp_rpt_expy_tbl1.fy_bgn_dt =
           ANY (SELECT   MAX (hosp_rpt_expy_tbl.fy_bgn_dt)
                    FROM hosp.hosp_rpt_expy_tbl hosp_rpt_expy_tbl
                   WHERE (hosp_rpt_expy_tbl.prvdr_num =
                                                  hosp_rpt_expy_tbl1.prvdr_num
                         )
                GROUP BY hosp_rpt_expy_tbl.prvdr_num)
       ));



-- truncate the cross tab table

truncate table na_rpt_tbl;

-- insert data into first part of na_rpt_tbl

INSERT INTO na_rpt_tbl
            (rpt_rec_num,provider_number, fy_bgn_dt, fy_end_dt, rpt_stus_cd,prvdr_ctrl_type_cd)
   SELECT distinct rpt_rec_num,prvdr_num, fy_bgn_dt, fy_end_dt, rpt_stus_cd,prvdr_ctrl_type_cd
        FROM (SELECT * from gena_rpt_tbl)
				  group by (rpt_rec_num,prvdr_num,fy_bgn_dt, fy_end_dt, rpt_stus_cd,prvdr_ctrl_type_cd)
				  order by rpt_rec_num
;
commit
/
DECLARE
   CURSOR curcolumns
   IS
      SELECT *
        FROM na_col_tbl
       WHERE label IS NOT NULL;

   cmd   VARCHAR2 (4000);
BEGIN
   FOR y IN curcolumns
   LOOP
      cmd :=
            'begin update na_rpt_tbl dest set '
         || y.label
         || '= ('
         || 'SELECT src.alphnmrc_itm_txt FROM gena_rpt_tbl src, na_col_tbl lkup WHERE (src.wksht_cd = lkup.wksht_cd '
         || 'AND src.line_num = lkup.line_num '
         || 'AND src.clmn_num = lkup.clmn_num ) '
         || 'AND (lkup.label '
         || '='
         || ''''
		 ||y.label
		 ||''''
         || ')'
         || 'AND dest.RPT_REC_NUM = src.RPT_REC_NUM ) '
         || ';commit;end;';
      -- DBMS_OUTPUT.put_line ('cmd= ' || cmd);
     EXECUTE IMMEDIATE cmd;
   END LOOP;
COMMIT;
END;
/		  
SPOOL OFF
