--
-- usage @genrnlepu.sql  <YYYY>
-- This script creates all the csv FTE Reports for a specific  year --
-- MODIFY DEST_DIR TO PLACE CSV FILES IN ANOTHER DIRECTORY

PROMPT 'Enter value for FY  '
SET TERMOUT OFF 
DEFINE FY = &1
SET TERMOUT ON
set verify off

define DEST_DIR = 'E:\reports\HOSP10\IME_GME'
prompt SPOOLING TO &DEST_DIR
SET TERMOUT OFF 
SET NEWPAGE 0 
SET SPACE 0 
set trimspool on
set linesize 500
SET PAGESIZE 0 
SET ECHO OFF 
SET FEEDBACK OFF 
set heading off
drop table new_gme;
CREATE TABLE new_gme
AS
   SELECT   *
  FROM   (SELECT   i103719 AS RPT_REC_NUM,
                   PRVDR_NUM AS PRVDR_NUM,
                   FY_BGN_DT AS FY_BGN_DT,
                   FY_END_DT AS FY_END_DT,
                   RPT_STUS_CD AS RPT_STUS_CD,
                   i158684 AS ALPHNMRC_ITM_TXT,
                   i193225 AS WLC,
                   i158685 AS ITM_VAL_NUM
            FROM   (SELECT   RPT_REC_NUM AS i103719,
                             RPT_STUS_CD AS i103720,
                             PRVDR_CTRL_TYPE_CD AS i103721,
                             PRVDR_NUM AS PRVDR_NUM,
                             FY_BGN_DT AS FY_BGN_DT,
                             FY_END_DT AS FY_END_DT,
                             PROC_DT AS i103725,
                             INITL_RPT_SW AS i103726,
                             LAST_RPT_SW AS i103727,
                             FI_NUM AS i103728,
                             TRNSMTL_NUM AS i103729,
                             ADR_VNDR_CD AS i103730,
                             EXP_CD AS i103731,
                             FI_CREAT_DT AS i103732,
                             UTIL_CD AS i103733,
                             NPR_DT AS i103734,
                             SPEC_IND AS i103735,
                             FI_RCPT_DT AS i103736,
                             DECODE (RPT_STUS_CD,
                                     '1', 'As Submitted',
                                     '2', 'Settled',
                                     '3', 'Settled w/Audit',
                                     '4', 'Reopened',
                                     '5', 'Amended',
                                     '_NONE')
                                AS RPT_STUS_CD,
                             DECODE (
                                (ADD_MONTHS (FY_BGN_DT, 3)),
                                NULL,
                                TO_DATE (NULL, 'MMDDYYYY'),
                                TO_DATE (
                                   TO_CHAR (
                                      TRUNC ( (ADD_MONTHS (FY_BGN_DT, 3)),
                                             'YYYY'),
                                      'YYYY'
                                   )
                                   || '01',
                                   'YYYYMM'
                                )
                             )
                                AS i104261,
                             ADD_MONTHS (FY_BGN_DT, 3) AS i104233,
                                RPT_REC_NUM
                             || ','
                             || PRVDR_NUM
                             || ','
                             || FI_NUM
                             || ','
                             || (DECODE (RPT_STUS_CD,
                                         '1', 'As Submitted',
                                         '2', 'Settled',
                                         '3', 'Settled w/Audit',
                                         '4', 'Reopened',
                                         '5', 'Amended',
                                         '_NONE'))
                             || ','
                             || TO_CHAR (FY_BGN_DT, 'MM/DD/YYYY')
                             || ','
                             || TO_CHAR (FY_END_DT, 'MM/DD/YYYY')
                                AS i104076,
                             FY AS i142844,
                             NPI AS i190105
                      FROM   HOSP10.HOSP10_RPT_EXPY_TBL) o103718,
                   (SELECT   CLMN_NUM AS i158680,
                             LINE_NUM AS i158681,
                             FY AS i158682,
                             ITM_VAL_NUM AS i158683,
                             ALPHNMRC_ITM_TXT AS i158684,
                             DECODE (ITM_VAL_NUM, '', 0, ITM_VAL_NUM)
                                AS i158685,
                             RPT_REC_NUM AS i158678,
                             WKSHT_CD AS i158679,
                             WKSHT_CD || '_' || LINE_NUM || '_' || CLMN_NUM
                                AS i193225
                      FROM   (SELECT   RPT_REC_NUM,
                                       WKSHT_CD,
                                       CLMN_NUM,
                                       LINE_NUM,
                                       FY,
                                       0 AS itm_val_num,
                                       ALPHNMRC_ITM_TXT
                                FROM   HOSP10.HOSP10_Rpt_Alphnmrc_expy_tbl
                              UNION
                              SELECT   RPT_REC_NUM,
                                       WKSHT_CD,
                                       CLMN_NUM,
                                       LINE_NUM,
                                       FY,
                                       ITM_VAL_NUM,
                                       ' ' AS alphnmrc_itm_txt
                                FROM   HOSP10.HOSP10_Rpt_Nmrc_expy_tbl)
                             CUO158675) o158675
           WHERE   ( (i103719 = i158678))
                   AND (o103718.i104261 =
                           TO_DATE ('&FY.0101000000', 'YYYYMMDDHH24MISS'))
                   AND ( (o158675.i158679 = 'S200001'
                          AND o158675.i158681 IN ('00300', '00200', '00100')
                          --AND o158675.i158680 = '00100'
                          OR o158675.i158679 = 'S200001'
                            AND o158675.i158681 IN ('00100', '00101')
                          OR o158675.i158679 = 'E00A18A'
                            AND o158675.i158681 IN
                                     ('03400',
                                      '03300',
                                      '02900',
                                      '05200',
                                      '00324')
                          OR o158675.i158679 = 'S300001'
                            AND o158675.i158680 IN
                                     ('00100',
                                      '00200',
                                      '00300',
                                      '00400',
                                      '00600',
                                      '00700',
                                      '00900',
                                      '00800',
                                      '01000',
                                      '01100',
                                      '01300',
                                      '01400',
                                      '01500')
                            AND o158675.i158681 = '01400'
                          OR o158675.i158679 = 'E00A18B'
                            AND o158675.i158681 IN ('02800', '02500')
                          OR o158675.i158679 = 'S300001'
                            AND o158675.i158681 = '01400'
                            AND o158675.i158680 IN
                                     ('01000',
                                      '01100',
                                      '01400',
                                      '01500',
                                      '01300'))));
SPOOL  &DEST_DIR.\IME_GME&FY..CSV
select
'PROVIDER_NUMBER'
||','||'FYB'
||','||'FYE'
||','||'STATUS'
||','||'HOSPITAL_Name'
||','||'Street_Addr'
||','||'Po_BOx'
||','||'City'
||','||'State'
||','||'Zip_Code'
||','||'County'
||','||'IME1'
||','||'IME2'
||','||'IME3'
||','||'DSH1'
||','||'DSH2'
||','||'DSH3'
||','||'DSH_SHARE_PERCENTAGE'
||','||'GME_PART_A'
||','||'GME_PART_B'
||','||'TOTAL_HOSPITAL_BEDS'
||','||'TOTAL_HOSPITAL_BED_DAYS_AVAILABLE'
||','||'TOTAL_HOSPITAL_MEDICARE_DAYS'
||','||'TOTAL_HOSPITAL_MEDICAID_DAYS'
||','||'TOTAL_HOSPITAL_DAYS'
||','||'INTERNS_AND_RESIDENTS'	
||','||'TOTAL_HOSPITAL_EMPLOYEES_ON_PAYROL'
||','||'TOTAL_HOSPITAL_NON_PAID_WORKERS'
||','||'TOTAL_HOSPITAL_MEDICARE_DISCHARGES'
||','||'TOTAL_HOSPITAL_MEDICAID_DISCHARGES'
||','||'TOTAL_HOSPITAL_DISCHARGES'
from dual;
select 
PRVDR_NUM
||','||FY_BGN_DT
||','||FY_END_DT
||','||RPT_STUS_CD
||','||l23
||','||l17
||','||l18
||','||l19
||','||l20
||','||l21
||','||l22
||','||l1
||','||l2
||','||l3
||','||l4
||','||l5
||','||l6
||','||l27
||','||l7
||','||l8
||','||l9
||','||l10
||','||l11
||','||l25
||','||l12
||','||l26
||','||l13
||','||l14
||','||l24
||','||l15
||','||l16
from 
(SELECT   prvdr_num,
         fy_bgn_dt,
         fy_end_dt,
         rpt_stus_cd,
         MAX (DECODE (wlc, 'S200001_00300_00100', ALPHNMRC_ITM_TXT, NULL)) l23,							
         MAX (DECODE (wlc, 'S200001_00100_00100', ALPHNMRC_ITM_TXT, NULL)) l17,							
         MAX (DECODE (wlc, 'S200001_00100_00200', ALPHNMRC_ITM_TXT, NULL)) l18,							
         MAX (DECODE (wlc, 'S200001_00200_00100', ALPHNMRC_ITM_TXT, NULL)) l19,							
         MAX (DECODE (wlc, 'S200001_00200_00200', ALPHNMRC_ITM_TXT, NULL)) l20,							
         MAX (DECODE (wlc, 'S200001_00200_00300', ALPHNMRC_ITM_TXT, NULL)) l21,							
         MAX (DECODE (wlc, 'S200001_00200_00400', ALPHNMRC_ITM_TXT, NULL)) l22,							
         MAX (DECODE (wlc, 'E00A18A_02900_00100', itm_val_num, NULL)) l1,							
         MAX (DECODE (wlc, 'E00A18A_02900_00101', itm_val_num, NULL)) l2,							
         MAX (DECODE (wlc, 'E00A18A_02900_00102', itm_val_num, NULL)) l3,							
         MAX (DECODE (wlc, 'E00A18A_03400_00100', itm_val_num, NULL)) l4,							
         MAX (DECODE (wlc, 'E00A18A_03400_00101', itm_val_num, NULL)) l5,							
         MAX (DECODE (wlc, 'E00A18A_03400_00102', itm_val_num, NULL)) l6,
         MAX (DECODE (wlc, 'E00A18A_03300_00100', itm_val_num, NULL)) l27,							
         MAX (DECODE (wlc, 'E00A18A_05200_00100', itm_val_num, NULL)) l7,							
         MAX (DECODE (wlc, 'E00A18B_02800_00100', itm_val_num, NULL)) l8,							
         MAX (DECODE (wlc, 'S300001_01400_00200', itm_val_num, NULL)) l9,							
         MAX (DECODE (wlc, 'S300001_01400_00300', itm_val_num, NULL)) l10,
         MAX (DECODE (wlc, 'S300001_01400_00600', itm_val_num, NULL)) l11,
         MAX (DECODE (wlc, 'S300001_01400_00700', itm_val_num, NULL)) l25,								
         MAX (DECODE (wlc, 'S300001_01400_00800', itm_val_num, NULL)) l12,
         MAX (DECODE (wlc, 'S300001_01400_00900', itm_val_num, NULL)) l26,							
         MAX (DECODE (wlc, 'S300001_01400_01000', itm_val_num, NULL)) l13,							
         MAX (DECODE (wlc, 'S300001_01400_01100', itm_val_num, NULL)) l14,							
         MAX (DECODE (wlc, 'S300001_01400_01300', itm_val_num, NULL)) l24,							
         MAX (DECODE (wlc, 'S300001_01400_01400', itm_val_num, NULL)) l15,							
         MAX (DECODE (wlc, 'S300001_01400_01500', itm_val_num, NULL)) l16							
  FROM   new_gme
          GROUP BY   prvdr_num,
                     fy_bgn_dt,
                     fy_end_dt,
                     rpt_stus_cd
          ORDER BY   prvdr_num);
SPOOL OFF