utl_file.fopen in oracle

CREATE OR REPLACE PROCEDURE load_hosp_usage(date_from IN VARCHAR, ym IN VARCHAR)
AS
    v_header               VARCHAR2(32767);
    filename               UTL_FILE.file_type;
    filename1              VARCHAR2(10000);
    v_procedure_c CONSTANT VARCHAR2(30) := 'load_hosp_usage';
    v_location_i           INTEGER;
    V_error_code NUMBER;
    V_error_message VARCHAR2(255);
    CURSOR c1
    IS
select inv_product_type,
         customer_id,
         patient_name,
Sun_1,
Mon_1,
Tue_1,
wed_1,
thu_1,
fri_1,
Sat_1,
Sun_1+Mon_1+Tue_1+wed_1+thu_1+fri_1+sat_1 as week_1_total,
Sun_2,
Mon_2,
Tue_2,
wed_2,
thu_2,
fri_2,
Sat_2,
Sun_2+Mon_2+Tue_2+wed_2+thu_2+fri_2+sat_2 as week_2_total,
Sun_3,
Mon_3,
Tue_3,
wed_3,
thu_3,
fri_3,
Sat_3,
Sun_3+Mon_3+Tue_3+wed_3+thu_3+fri_3+sat_3 as week_3_total,
Sun_4,
Mon_4,
Tue_4,
wed_4,
thu_4,
fri_4,
Sat_4,
Sun_4+Mon_4+Tue_4+wed_4+thu_4+fri_4+sat_4 as week_4_total,
Sun_5,
Mon_5,
Tue_5,
wed_5,
thu_5,
fri_5,
Sat_5,
Sun_5+Mon_5+Tue_5+wed_5+thu_5+fri_5+sat_5 as week_5_total,
Sun_6,
Mon_6,
Tue_6,
wed_6,
thu_6,
fri_6,
Sat_6,
Sun_6+Mon_6+Tue_6+wed_6+thu_6+fri_6+sat_6 as week_6_total,
Sun_7,
Mon_7,
Tue_7,
wed_7,
thu_7,
fri_7,
Sat_7,
Sun_7+Mon_7+Tue_7+wed_7+thu_7+fri_7+sat_7 as week_7_total,
Sun_8,
Mon_8,
Tue_8,
wed_8,
thu_8,
fri_8,
Sat_8,
Sun_8+Mon_8+Tue_8+wed_8+thu_8+fri_8+sat_8 as week_8_total,
Sun_9,
Mon_9,
Tue_9,
wed_9,
thu_9,
fri_9,
Sat_9,
Sun_9+Mon_9+Tue_9+wed_9+thu_9+fri_9+sat_9 as week_9_total,
Sun_10,
Mon_10,
Tue_10,
wed_10,
thu_10,
fri_10,
Sat_10,
Sun_10+Mon_10+Tue_10+wed_10+thu_10+fri_10+sat_10 as week_10_total,
Sun_11,
Mon_11,
Tue_11,
wed_11,
thu_11,
fri_11,
Sat_11,
Sun_11+Mon_11+Tue_11+wed_11+thu_11+fri_11+sat_11 as week_11_total,
Sun_12,
Mon_12,
Tue_12,
wed_12,
thu_12,
fri_12,
Sat_12,
Sun_12+Mon_12+Tue_12+wed_12+thu_12+fri_12+sat_12 as week_12_total,
Sun_13,
Mon_13,
Tue_13,
wed_13,
thu_13,
fri_13,
Sat_13,
Sun_13+Mon_13+Tue_13+wed_13+thu_13+fri_13+sat_13 as week_13_total,
Sun_14,
Mon_14,
Tue_14,
wed_14,
thu_14,
fri_14,
Sat_14,
Sun_14+Mon_14+Tue_14+wed_14+thu_14+fri_14+sat_14 as week_14_total,
Sun_15,
Mon_15,
Sun_15+Mon_15 as week_15_total
from (          
		  SELECT inv_product_type,
                 customer_id,
                 patient_name,
                 nvl(SUM(
                     CASE
                         WHEN iwd = TRUNC(invoice_date, 'mm')
                         THEN
                             CASE WHEN iwd = invoice_date THEN 1 ELSE 0 END
                     END
                 ),0)
                     sun_1,
                 nvl(SUM(
                     CASE
                         WHEN iwd + 1 >= TRUNC(invoice_date, 'mm')
                         THEN
                             CASE WHEN iwd + 1 = invoice_date THEN 1 ELSE 0 END
                     END
                 ),0)
                     mon_1,
                 nvl(SUM(
                     CASE
                         WHEN iwd + 2 >= TRUNC(invoice_date, 'mm')
                         THEN
                             CASE WHEN iwd + 2 = invoice_date THEN 1 ELSE 0 END
                     END
                 ),0)
                     tue_1,
                 nvl(SUM(
                     CASE
                         WHEN iwd + 3 >= TRUNC(invoice_date, 'mm')
                         THEN
                             CASE WHEN iwd + 3 = invoice_date THEN 1 ELSE 0 END
                     END
                 ),0)
                     wed_1,
                 nvl(SUM(
                     CASE
                         WHEN iwd + 4 >= TRUNC(invoice_date, 'mm')
                         THEN
                             CASE WHEN iwd + 4 = invoice_date THEN 1 ELSE 0 END
                     END
                 ),0)
                     thu_1,
                 nvl(SUM(
                     CASE
                         WHEN iwd + 5 >= TRUNC(invoice_date, 'mm')
                         THEN
                             CASE WHEN iwd + 5 = invoice_date THEN 1 ELSE 0 END
                     END
                 ),0)
                     fri_1,
                 NVL(SUM(CASE WHEN iwd + 6 = invoice_date THEN 1 ELSE 0 END), 0) sat_1,
                 NVL(SUM(CASE WHEN iwd + 7 = invoice_date THEN 1 ELSE 0 END), 0) sun_2,
                 NVL(SUM(CASE WHEN iwd + 8 = invoice_date THEN 1 ELSE 0 END), 0) mon_2,
                 NVL(SUM(CASE WHEN iwd + 9 = invoice_date THEN 1 ELSE 0 END), 0) tue_2,
                 NVL(SUM(CASE WHEN iwd + 10 = invoice_date THEN 1 ELSE 0 END), 0) wed_2,
                 NVL(SUM(CASE WHEN iwd + 11 = invoice_date THEN 1 ELSE 0 END), 0) thu_2,
                 NVL(SUM(CASE WHEN iwd + 12 = invoice_date THEN 1 ELSE 0 END), 0) fri_2,
                 NVL(SUM(CASE WHEN iwd + 13 = invoice_date THEN 1 ELSE 0 END), 0) sat_2,
                 NVL(SUM(CASE WHEN iwd + 14 = invoice_date THEN 1 ELSE 0 END), 0) sun_3,
                 NVL(SUM(CASE WHEN iwd + 15 = invoice_date THEN 1 ELSE 0 END), 0) mon_3,
                 NVL(SUM(CASE WHEN iwd + 16 = invoice_date THEN 1 ELSE 0 END), 0) tue_3,
                 NVL(SUM(CASE WHEN iwd + 17 = invoice_date THEN 1 ELSE 0 END), 0) wed_3,
                 NVL(SUM(CASE WHEN iwd + 18 = invoice_date THEN 1 ELSE 0 END), 0) thu_3,
                 NVL(SUM(CASE WHEN iwd + 19 = invoice_date THEN 1 ELSE 0 END), 0) fri_3,
                 NVL(SUM(CASE WHEN iwd + 20 = invoice_date THEN 1 ELSE 0 END), 0) sat_3,
                 NVL(SUM(CASE WHEN iwd + 21 = invoice_date THEN 1 ELSE 0 END), 0) sun_4,
                 NVL(SUM(CASE WHEN iwd + 22 = invoice_date THEN 1 ELSE 0 END), 0) mon_4,
                 NVL(SUM(CASE WHEN iwd + 23 = invoice_date THEN 1 ELSE 0 END), 0) tue_4,
                 NVL(SUM(CASE WHEN iwd + 24 = invoice_date THEN 1 ELSE 0 END), 0) wed_4,
                 NVL(SUM(CASE WHEN iwd + 25 = invoice_date THEN 1 ELSE 0 END), 0) thu_4,
                 NVL(SUM(CASE WHEN iwd + 26 = invoice_date THEN 1 ELSE 0 END), 0) fri_4,
                 NVL(SUM(CASE WHEN iwd + 27 = invoice_date THEN 1 ELSE 0 END), 0) sat_4,
                 NVL(SUM(CASE WHEN iwd + 28 = invoice_date THEN 1 ELSE 0 END), 0) sun_5,
                 NVL(SUM(CASE WHEN iwd + 29 = invoice_date THEN 1 ELSE 0 END), 0) mon_5,
                 NVL(SUM(CASE WHEN iwd + 30 = invoice_date THEN 1 ELSE 0 END), 0) tue_5,
                 NVL(SUM(CASE WHEN iwd + 31 = invoice_date THEN 1 ELSE 0 END), 0) wed_5,
                 NVL(SUM(CASE WHEN iwd + 32 = invoice_date THEN 1 ELSE 0 END), 0) thu_5,
                 NVL(SUM(CASE WHEN iwd + 33 = invoice_date THEN 1 ELSE 0 END), 0) fri_5,
                 NVL(SUM(CASE WHEN iwd + 34 = invoice_date THEN 1 ELSE 0 END), 0) sat_5,
                 NVL(SUM(CASE WHEN iwd + 35 = invoice_date THEN 1 ELSE 0 END), 0) sun_6,
                 NVL(SUM(CASE WHEN iwd + 36 = invoice_date THEN 1 ELSE 0 END), 0) mon_6,
                 NVL(SUM(CASE WHEN iwd + 37 = invoice_date THEN 1 ELSE 0 END), 0) tue_6,
                 NVL(SUM(CASE WHEN iwd + 38 = invoice_date THEN 1 ELSE 0 END), 0) wed_6,
                 NVL(SUM(CASE WHEN iwd + 39 = invoice_date THEN 1 ELSE 0 END), 0) thu_6,
                 NVL(SUM(CASE WHEN iwd + 40 = invoice_date THEN 1 ELSE 0 END), 0) fri_6,
                 NVL(SUM(CASE WHEN iwd + 41 = invoice_date THEN 1 ELSE 0 END), 0) sat_6,
                 NVL(SUM(CASE WHEN iwd + 42 = invoice_date THEN 1 ELSE 0 END), 0) sun_7,
                 NVL(SUM(CASE WHEN iwd + 43 = invoice_date THEN 1 ELSE 0 END), 0) mon_7,
                 NVL(SUM(CASE WHEN iwd + 44 = invoice_date THEN 1 ELSE 0 END), 0) tue_7,
                 NVL(SUM(CASE WHEN iwd + 45 = invoice_date THEN 1 ELSE 0 END), 0) wed_7,
                 NVL(SUM(CASE WHEN iwd + 46 = invoice_date THEN 1 ELSE 0 END), 0) thu_7,
                 NVL(SUM(CASE WHEN iwd + 47 = invoice_date THEN 1 ELSE 0 END), 0) fri_7,
                 NVL(SUM(CASE WHEN iwd + 48 = invoice_date THEN 1 ELSE 0 END), 0) sat_7,
                 NVL(SUM(CASE WHEN iwd + 49 = invoice_date THEN 1 ELSE 0 END), 0) sun_8,
                 NVL(SUM(CASE WHEN iwd + 50 = invoice_date THEN 1 ELSE 0 END), 0) mon_8,
                 NVL(SUM(CASE WHEN iwd + 51 = invoice_date THEN 1 ELSE 0 END), 0) tue_8,
                 NVL(SUM(CASE WHEN iwd + 52 = invoice_date THEN 1 ELSE 0 END), 0) wed_8,
                 NVL(SUM(CASE WHEN iwd + 53 = invoice_date THEN 1 ELSE 0 END), 0) thu_8,
                 NVL(SUM(CASE WHEN iwd + 54 = invoice_date THEN 1 ELSE 0 END), 0) fri_8,
                 NVL(SUM(CASE WHEN iwd + 55 = invoice_date THEN 1 ELSE 0 END), 0) sat_8,
                 NVL(SUM(CASE WHEN iwd + 56 = invoice_date THEN 1 ELSE 0 END), 0) sun_9,
                 NVL(SUM(CASE WHEN iwd + 57 = invoice_date THEN 1 ELSE 0 END), 0) mon_9,
                 NVL(SUM(CASE WHEN iwd + 58 = invoice_date THEN 1 ELSE 0 END), 0) tue_9,
                 NVL(SUM(CASE WHEN iwd + 59 = invoice_date THEN 1 ELSE 0 END), 0) wed_9,
                 NVL(SUM(CASE WHEN iwd + 60 = invoice_date THEN 1 ELSE 0 END), 0) thu_9,
                 NVL(SUM(CASE WHEN iwd + 61 = invoice_date THEN 1 ELSE 0 END), 0) fri_9,
                 NVL(SUM(CASE WHEN iwd + 62 = invoice_date THEN 1 ELSE 0 END), 0) sat_9,
                 NVL(SUM(CASE WHEN iwd + 63 = invoice_date THEN 1 ELSE 0 END), 0) sun_10,
                 NVL(SUM(CASE WHEN iwd + 64 = invoice_date THEN 1 ELSE 0 END), 0) mon_10,
                 NVL(SUM(CASE WHEN iwd + 65 = invoice_date THEN 1 ELSE 0 END), 0) tue_10,
                 NVL(SUM(CASE WHEN iwd + 66 = invoice_date THEN 1 ELSE 0 END), 0) wed_10,
                 NVL(SUM(CASE WHEN iwd + 67 = invoice_date THEN 1 ELSE 0 END), 0) thu_10,
                 NVL(SUM(CASE WHEN iwd + 68 = invoice_date THEN 1 ELSE 0 END), 0) fri_10,
                 NVL(SUM(CASE WHEN iwd + 69 = invoice_date THEN 1 ELSE 0 END), 0) sat_10,
                 NVL(SUM(CASE WHEN iwd + 70 = invoice_date THEN 1 ELSE 0 END), 0) sun_11,
                 NVL(SUM(CASE WHEN iwd + 71 = invoice_date THEN 1 ELSE 0 END), 0) mon_11,
                 NVL(SUM(CASE WHEN iwd + 72 = invoice_date THEN 1 ELSE 0 END), 0) tue_11,
                 NVL(SUM(CASE WHEN iwd + 73 = invoice_date THEN 1 ELSE 0 END), 0) wed_11,
                 NVL(SUM(CASE WHEN iwd + 74 = invoice_date THEN 1 ELSE 0 END), 0) thu_11,
                 NVL(SUM(CASE WHEN iwd + 75 = invoice_date THEN 1 ELSE 0 END), 0) fri_11,
                 NVL(SUM(CASE WHEN iwd + 76 = invoice_date THEN 1 ELSE 0 END), 0) sat_11,
                 NVL(SUM(CASE WHEN iwd + 77 = invoice_date THEN 1 ELSE 0 END), 0) sun_12,
                 NVL(SUM(CASE WHEN iwd + 78 = invoice_date THEN 1 ELSE 0 END), 0) mon_12,
                 NVL(SUM(CASE WHEN iwd + 79 = invoice_date THEN 1 ELSE 0 END), 0) tue_12,
                 NVL(SUM(CASE WHEN iwd + 80 = invoice_date THEN 1 ELSE 0 END), 0) wed_12,
                 NVL(SUM(CASE WHEN iwd + 81 = invoice_date THEN 1 ELSE 0 END), 0) thu_12,
                 NVL(SUM(CASE WHEN iwd + 82 = invoice_date THEN 1 ELSE 0 END), 0) fri_12,
                 NVL(SUM(CASE WHEN iwd + 83 = invoice_date THEN 1 ELSE 0 END), 0) sat_12,
                 NVL(SUM(CASE WHEN iwd + 84 = invoice_date THEN 1 ELSE 0 END), 0) sun_13,
                 NVL(SUM(CASE WHEN iwd + 85 = invoice_date THEN 1 ELSE 0 END), 0) mon_13,
                 NVL(SUM(CASE WHEN iwd + 86 = invoice_date THEN 1 ELSE 0 END), 0) tue_13,
                 NVL(SUM(CASE WHEN iwd + 87 = invoice_date THEN 1 ELSE 0 END), 0) wed_13,
                 NVL(SUM(CASE WHEN iwd + 88 = invoice_date THEN 1 ELSE 0 END), 0) thu_13,
                 NVL(SUM(CASE WHEN iwd + 89 = invoice_date THEN 1 ELSE 0 END), 0) fri_13,
                 NVL(SUM(CASE WHEN iwd + 90 = invoice_date THEN 1 ELSE 0 END), 0) sat_13,
                 NVL(SUM(CASE WHEN iwd + 91 = invoice_date THEN 1 ELSE 0 END), 0) sun_14,
                 NVL(SUM(CASE WHEN iwd + 92 = invoice_date THEN 1 ELSE 0 END), 0) mon_14,
                 NVL(SUM(CASE WHEN iwd + 93 = invoice_date THEN 1 ELSE 0 END), 0) tue_14,
                 NVL(SUM(CASE WHEN iwd + 94 = invoice_date THEN 1 ELSE 0 END), 0) wed_14,
                 NVL(SUM(CASE WHEN iwd + 95 = invoice_date THEN 1 ELSE 0 END), 0) thu_14,
                 NVL(SUM(CASE WHEN iwd + 96 = invoice_date THEN 1 ELSE 0 END), 0) fri_14,
                 NVL(SUM(CASE WHEN iwd + 97 = invoice_date THEN 1 ELSE 0 END), 0) sat_14,
                 NVL(SUM(CASE WHEN iwd + 98 = invoice_date THEN 1 ELSE 0 END), 0) sun_15,
                 NVL(SUM(CASE WHEN iwd + 99 = invoice_date THEN 1 ELSE 0 END), 0) mon_15
            FROM (SELECT TO_CHAR(TRUNC(ih.invoice_date, 'mm'), 'yyyymm') ym,
                         NEXT_DAY(TRUNC(TO_DATE(date_from, 'DD-MON-YYYY'), 'mm'), 'Sun') iwd,
                         CASE
                             WHEN vp.inv_product_type = 'RBC' THEN 'Red Blood Cells'
                             WHEN vp.inv_product_type = 'LRBC' THEN 'Leukoreduced Red Blood Cells'
                             WHEN vp.inv_product_type = 'LPHER' THEN 'SDP'
                             WHEN vp.inv_product_type = 'PHER' THEN 'SDP'
                             WHEN vp.inv_product_type = 'FFP' THEN 'Fresh Frozen Plasma'
                             WHEN vp.inv_product_type = 'FP24' THEN 'Plasma Frozen withing 24hrs'
                             WHEN vp.inv_product_type = 'CRYO' THEN 'Cryoprecipitate'
                         END
                             inv_product_type,
                         ih.customer_id,
                         id.patient_name,
                         ih.invoice_date
                    FROM tab1 ih,
                 		 tab2 id,
                 		 tab4 ip,
                 		 tab3 vp
                   WHERE id.invoice_number = ih.invoice_number
                     AND id.item_id = vp.product_code
                     AND id.item_id = ip.item_id
                     AND ip.item_type IN ('P')
                     AND ih.customer_id = 'WAD-EX0128'
                     AND ih.invoice_date >= TO_DATE(ym, 'YYYYMM')
                     AND ih.invoice_date < ADD_MONTHS(TO_DATE(ym, 'YYYYMM'), 3)
                     AND vp.inv_product_type IN ('RBC',
                                                 'LRBC',
                                                 'LPHER',
                                                 'PHER',
                                                 'FFP',
                                                 'FP24',
                                                 'CRYO'))
        GROUP BY inv_product_type, customer_id, patient_name
        ORDER BY inv_product_type, customer_id, patient_name--;
);
    varc1                  c1%ROWTYPE;
BEGIN
    SELECT REPLACE(days, ',', CHR(9)) data
      INTO v_header
      FROM (    SELECT SUBSTR(
                           SYS_CONNECT_BY_PATH(
                               TO_CHAR(
                                   NEXT_DAY(TO_DATE(ym, 'YYYYMM') - 1, 'Sun') + LEVEL - 1,
                                   'dd-Mon-yyyy'
                               ),
                               ','
                           ),
                           2
                       )
                           days
                  FROM DUAL
            CONNECT BY LEVEL <= 12 * 7
              ORDER BY LEVEL DESC)
     WHERE ROWNUM = 1;

    -- DBMS_OUTPUT.put_line(v_header);

    v_location_i := 1000;

    --FILENAME1   := 'hosp_usage' || '_' || SYSDATE || '.CSV';
    filename1 := 'hosp_usage' || '_' || SYSDATE || '.xls';
    v_location_i := 2000;
    filename := UTL_FILE.fopen('HOSP_USAGE_DIR', filename1, 'W',max_linesize => 32767);

    /* THIS WILL CREATE THE HEADING IN EXCEL SHEET */
    v_location_i := 3000;
    UTL_FILE.put_line(
        filename,
        'INV PRODUCT TYPE' || CHR(9) || 'CUSTOMER ID' || CHR(9) || 'NAME' || CHR(9) || v_header
    );

    OPEN c1;

    v_location_i := 4000;

    LOOP
        FETCH c1 INTO varc1;

        v_location_i := 5000;
        EXIT WHEN c1%NOTFOUND;
        /*  THIS WILL PRINT THE RECORDS IN EXCEL SHEET AS PER THE QUERY IN CURSOR */
        v_location_i := 6000;
		
        UTL_FILE.put_line(
            filename,
               varc1.inv_product_type
            || CHR(9)
            || varc1.customer_id
            || CHR(9)
            || varc1.patient_name
            || CHR(9)
            || varc1.sun_1
            || CHR(9)
            || varc1.mon_1
            || CHR(9)
            || varc1.tue_1
            || CHR(9)
            || varc1.wed_1
            || CHR(9)
            || varc1.thu_1
            || CHR(9)
            || varc1.fri_1
            || CHR(9)
            || varc1.sat_1
            || CHR(9)
			|| varc1.week_1_total
			|| CHR(9)
            || varc1.sun_2
            || CHR(9)
            || varc1.mon_2
            || CHR(9)
            || varc1.tue_2
            || CHR(9)
            || varc1.wed_2
            || CHR(9)
            || varc1.thu_2
            || CHR(9)
            || varc1.fri_2
            || CHR(9)
            || varc1.sat_2
            || CHR(9)
			|| varc1.week_2_total
			|| CHR(9)
            || varc1.sun_3
            || CHR(9)
            || varc1.mon_3
            || CHR(9)
            || varc1.tue_3
            || CHR(9)
            || varc1.wed_3
            || CHR(9)
            || varc1.thu_3
            || CHR(9)
            || varc1.fri_3
            || CHR(9)
            || varc1.sat_3
            || CHR(9)
			|| varc1.week_3_total
			|| CHR(9)
            || varc1.sun_4
            || CHR(9)
            || varc1.mon_4
            || CHR(9)
            || varc1.tue_4
            || CHR(9)
            || varc1.wed_4
            || CHR(9)
            || varc1.thu_4
            || CHR(9)
            || varc1.fri_4
            || CHR(9)
            || varc1.sat_4
            || CHR(9)
			|| varc1.week_4_total
			|| CHR(9)
            || varc1.sun_5
            || CHR(9)
            || varc1.mon_5
            || CHR(9)
            || varc1.tue_5
            || CHR(9)
            || varc1.wed_5
            || CHR(9)
            || varc1.thu_5
            || CHR(9)
            || varc1.fri_5
            || CHR(9)
            || varc1.sat_5
            || CHR(9)
			|| varc1.week_5_total
			|| CHR(9)
            || varc1.sun_6
            || CHR(9)
            || varc1.mon_6
            || CHR(9)
            || varc1.tue_6
            || CHR(9)
            || varc1.wed_6
            || CHR(9)
            || varc1.thu_6
            || CHR(9)
            || varc1.fri_6
            || CHR(9)
            || varc1.sat_6
            || CHR(9)
			|| varc1.week_6_total
			|| CHR(9)
            || varc1.sun_7
            || CHR(9)
            || varc1.mon_7
            || CHR(9)
            || varc1.tue_7
            || CHR(9)
            || varc1.wed_7
            || CHR(9)
            || varc1.thu_7
            || CHR(9)
            || varc1.fri_7
            || CHR(9)
            || varc1.sat_7
            || CHR(9)
			|| varc1.week_7_total
			|| CHR(9)
            || varc1.sun_8
            || CHR(9)
            || varc1.mon_8
            || CHR(9)
            || varc1.tue_8
            || CHR(9)
            || varc1.wed_8
            || CHR(9)
            || varc1.thu_8
            || CHR(9)
            || varc1.fri_8
            || CHR(9)
            || varc1.sat_8
            || CHR(9)
			|| varc1.week_8_total
			|| CHR(9)
            || varc1.sun_9
            || CHR(9)
            || varc1.mon_9
            || CHR(9)
            || varc1.tue_9
            || CHR(9)
            || varc1.wed_9
            || CHR(9)
            || varc1.thu_9
            || CHR(9)
            || varc1.fri_9
            || CHR(9)
            || varc1.sat_9
            || CHR(9)
			|| varc1.week_9_total
			|| CHR(9)
            || varc1.sun_10
            || CHR(9)
            || varc1.mon_10
            || CHR(9)
            || varc1.tue_10
            || CHR(9)
            || varc1.wed_10
            || CHR(9)
            || varc1.thu_10
            || CHR(9)
            || varc1.fri_10
            || CHR(9)
            || varc1.sat_10
            || CHR(9)|| varc1.week_10_total
			|| CHR(9)
            || varc1.sun_11
            || CHR(9)
            || varc1.mon_11
            || CHR(9)
            || varc1.tue_11
            || CHR(9)
            || varc1.wed_11
            || CHR(9)
            || varc1.thu_11
            || CHR(9)
            || varc1.fri_11
            || CHR(9)
            || varc1.sat_11
            || CHR(9)
			|| varc1.week_11_total
			|| CHR(9)
            || varc1.sun_12
            || CHR(9)
            || varc1.mon_12
            || CHR(9)
            || varc1.tue_12
            || CHR(9)
            || varc1.wed_12
            || CHR(9)
            || varc1.thu_12
            || CHR(9)
            || varc1.fri_12
            || CHR(9)
            || varc1.sat_12
            || CHR(9)
			|| varc1.week_12_total
			|| CHR(9)
            || varc1.sun_13
            || CHR(9)
            || varc1.mon_13
            || CHR(9)
            || varc1.tue_13
            || CHR(9)
            || varc1.wed_13
            || CHR(9)
            || varc1.thu_13
            || CHR(9)
            || varc1.fri_13
            || CHR(9)
            || varc1.sat_13
            || CHR(9)
			|| varc1.week_13_total
			|| CHR(9)
            || varc1.sun_14
            || CHR(9)
            || varc1.mon_14
            || CHR(9)
            || varc1.tue_14
            || CHR(9)
            || varc1.wed_14
            || CHR(9)
            || varc1.thu_14
            || CHR(9)
            || varc1.fri_14
            || CHR(9)
            || varc1.sat_14
            || CHR(9)
			|| varc1.week_14_total
			|| CHR(9)
            || varc1.sun_15
            || CHR(9)
            || varc1.mon_15
			|| CHR(9)
			|| varc1.week_15_total
        );
    END LOOP;
       v_location_i := 6000;
    v_location_i := 7000;
    UTL_FILE.fclose(filename);
    v_location_i := 8000;
EXCEPTION
    WHEN OTHERS
    THEN
        DBMS_OUTPUT.put_line('Error in ' || v_procedure_c || ' at line: ' || v_location_i);
END load_hosp_usage;
/

Open in new window


SELECT REPLACE(days, ',', CHR(9)) data
      INTO v_header
      FROM (    SELECT SUBSTR(
                           SYS_CONNECT_BY_PATH(
                               TO_CHAR(
                                   NEXT_DAY(TO_DATE(ym, 'YYYYMM') - 1, 'Sun') + LEVEL - 1,
                                   'dd-Mon-yyyy'
                               ),
                               ','
                           ),
                           2
                       )
                           days
                  FROM DUAL
            CONNECT BY LEVEL <= 12 * 7
              ORDER BY LEVEL DESC)
     WHERE ROWNUM = 1;

Open in new window


Wanted to know if there is a way to add an additional column for weekly totals between the days
Attached the output file the totals has come as 11-Jan-15. After totals the week should continue. I am not sure this can be accomplished as header, but posting to see if anyone can help.
weekly-totals-header.JPG
LVL 6
anumosesAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

flow01Commented:
check if this does what you want  (basically i treat every 8e column different),  and  assumed a format for the week
SELECT REPLACE(days, ',', CHR(9)) data
      FROM (    SELECT SUBSTR(
                           SYS_CONNECT_BY_PATH(
                               CASE MOD(LEVEL,8)  -- every 8e column
                               WHEN 0 THEN
                               TO_CHAR(
                                   NEXT_DAY(TO_DATE('201505', 'YYYYMM') - 1, 'Sun') + LEVEL - 1
                                   - TRUNC(LEVEL/8) -1, -- 1 day less then level for each 8 values sets its back to the week of level 7
                                   'ww-yyyy'
                               )
                               ELSE
                               TO_CHAR(
                                   NEXT_DAY(TO_DATE('201505', 'YYYYMM') - 1, 'Sun') + LEVEL - 1
                                   - TRUNC(LEVEL/8),  -- 1 day less then level for each 8 values (else 1 day will be skipped each week
                                   'dd-Mon-yyyy'
                               ) END,
                               ','
                           ),
                           2
                       )
                           days
                  FROM DUAL
            CONNECT BY LEVEL <= 12 * 8
              ORDER BY LEVEL DESC)
     WHERE ROWNUM = 1
/
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
anumosesAuthor Commented:
Thanks
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.