--
-- 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\COST_CHARGES'
prompt SPOOLING TO &DEST_DIR
SET TERMOUT OFF 
SET NEWPAGE 0 
SET SPACE 0 
set trimspool on
set linesize 288
SET PAGESIZE 0 
SET ECHO OFF 
SET FEEDBACK OFF 
set heading off
SPOOL  &DEST_DIR.\CSTS_CHRGS&FY..CSV
select
'PROVIDER_NUMBER'
||','||'FYB'
||','||'FYE'
||','||'STATUS'
||','||'C000001_20000_00500'
||','||'C000001_20000_00600'
||','||'C000001_20000_00700'
from dual;
select 
PRVDR_NUM
||','||FY_BGN_DT
||','||FY_END_DT
||','||RPT_STUS_CD
||','||l1
||','||l2
||','||l3
from 
(SELECT   prvdr_num, fy_bgn_dt, fy_end_dt, rpt_stus_cd,
         MAX (DECODE (lc, 'C000001_20000_00500', itm_val_num, NULL)) l1,
	 MAX (DECODE (lc, 'C000001_20000_00600', itm_val_num, NULL)) l2,
         MAX (DECODE (lc, 'C000001_20000_00700', itm_val_num, NULL)) l3
                  FROM (SELECT HOSP10_rpt_expy_tbl.prvdr_num, HOSP10_rpt_expy_tbl.fy_bgn_dt,
       HOSP10_rpt_expy_tbl.fy_end_dt,
       DECODE (HOSP10_rpt_expy_tbl.rpt_stus_cd,
               '1', 'As Submitted',
               '2', 'Settled',
               '3', 'Settled w/Audit',
               '4', 'Reopened',
               '5', 'Amended',
               '_NONE'
              ) as rpt_stus_cd,
          HOSP10_rpt_nmrc_expy_vw.wksht_cd
       || '_'
       || HOSP10_rpt_nmrc_expy_vw.line_num
       || '_'
       || HOSP10_rpt_nmrc_expy_vw.clmn_num as lc,
       HOSP10_rpt_nmrc_expy_vw.itm_val_num
  FROM HOSP10.HOSP10_rpt_nmrc_expy_tbl HOSP10_rpt_nmrc_expy_vw,
       HOSP10.HOSP10_rpt_expy_tbl HOSP10_rpt_expy_tbl
 WHERE ((HOSP10_rpt_expy_tbl.rpt_rec_num = HOSP10_rpt_nmrc_expy_vw.rpt_rec_num)
       )
   AND ((DECODE
            ((ADD_MONTHS (HOSP10_rpt_expy_tbl.fy_bgn_dt, 3)),
             NULL, TO_DATE (NULL, 'MMDDYYYY'),
             TO_DATE
                (   TO_CHAR
                           (TRUNC ((ADD_MONTHS (HOSP10_rpt_expy_tbl.fy_bgn_dt,
                                                3)
                                   ),
                                   'YYYY'
                                  ),
                            'YYYY'
                           )
                 || '01',
                 'YYYYMM'
                )
            )
        ) = TO_DATE ('&FY.0101000000', 'YYYYMMDDHH24MISS')
       )
   AND (    HOSP10_rpt_nmrc_expy_vw.wksht_cd = 'C000001'
        AND HOSP10_rpt_nmrc_expy_vw.line_num = '20000'
        AND HOSP10_rpt_nmrc_expy_vw.clmn_num IN ('00500', '00600', '00700')
       ))
GROUP BY prvdr_num, fy_bgn_dt, fy_end_dt, rpt_stus_cd
ORDER BY prvdr_num);
SPOOL OFF