Oracle Data ( by weeks) restricting to 12 weeks.

I get the header for 12 weeks. Report is always run on a sunday date.

date from = 01-jan-2015'  and ym = 201501

CREATE OR REPLACE PROCEDURE load_hosp_usage_05072015(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 ih.invoice_date = TO_DATE(ym, 'YYYYMM')
                     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(
                               CASE MOD(LEVEL,8)  -- every 8e column
                               WHEN 0 THEN
                               TO_CHAR(
                                   NEXT_DAY(TO_DATE(ym, '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
                                   null--'WW-YYYY'
                               )
                               ELSE
                               TO_CHAR(
                                   NEXT_DAY(TO_DATE(ym, '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;

    -- 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_05072015;
/

Open in new window


Header is restricted to 12 weeks. Data goes beyond 12 weeks. Header stops at 28-mar-2015. How can I restrict data for 12 weeks? Can I use CONNECT BY LEVEL <= 12 and if so where should the condition be?
header-and-data.JPG
LVL 6
anumosesAsked:
Who is Participating?
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:
AND ih.invoice_date < ADD_MONTHS(TO_DATE(ym, 'YYYYMM'), 3)  
--3 months can be more then 12 weeks   (your select goes until week 15 !)
                    AND  ih.invoice_date <=  iwd + (12*7) --  limit to 12 weeks
or --  get more header data
     -- CONNECT BY LEVEL <= 12 * 8                
         CONNECT BY LEVEL <= 15 * 8   -- 15 weeks of header data will be to much because there are only 2 days in week 15
-- for 115 columns
SELECT REPLACE(days, ',', CHR(9)) data
      FROM (    SELECT SUBSTR(
                     SYS_CONNECT_BY_PATH(
                           CASE LEVEL
                               WHEN 115  THEN -- last colunn week_15_total =  (14 * 8 +  1 * 3)
                               TO_CHAR(
                                   NEXT_DAY(TO_DATE('201505', 'YYYYMM') - 1, 'Sun') + LEVEL - 1
                                   - TRUNC(LEVEL/8) - 2, -- 2 day less to get back on sunday
                                   'ww-yyyy'
                               )
                           ELSE
                               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) - 7, -- 7 day less to get back on sunday of previous week
                                   '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
                            END,
                               ','
                           ),
                           2
                       )
                           days
                  FROM DUAL
            CONNECT BY LEVEL <= 115
              ORDER BY LEVEL DESC)
     WHERE ROWNUM = 1
0

Experts Exchange Solution brought to you by ConnectWise

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:
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, 'YYY:ymM')
                    -- AND ih.invoice_date < ADD_MONTHS(TO_DATE(:ym, 'YYY:ymM'), 3)
                     AND ih.invoice_date < ADD_MONTHS(TO_DATE(:ym, 'YYY:ymM'), 3)  
--3 months can be more then 12 weeks   (your select goes until week 15 !)
                    AND  ih.invoice_date <=  iwd + (12*7) --  limit to 12 weeks
or --  get more header data
     -- CONNECT BY LEVEL <= 12 * 8                
         CONNECT BY LEVEL <= 15 * 8   -- 15 weeks of header data will be to much because there are only 2 days in week 15
-- for 115 columns
SELECT REPLACE(days, ',', CHR(9)) data
      FROM (    SELECT SUBSTR(
                     SYS_CONNECT_BY_PATH(
                           CASE LEVEL
                               WHEN 115  THEN -- last colunn week_15_total =  (14 * 8 +  1 * 3)
                               TO_CHAR(
                                   NEXT_DAY(TO_DATE(:ym, 'YYYYMM') - 1, 'Sun') + LEVEL - 1
                                   - TRUNC(LEVEL/8) - 2, -- 2 day less to get back on sunday
                                   'ww-yyyy'
                               )
                           ELSE
                               CASE MOD(LEVEL,8)  -- every 8e column
                               WHEN 0 THEN
                               TO_CHAR(
                                   NEXT_DAY(TO_DATE(:ym, 'YYYYMM') - 1, 'Sun') + LEVEL - 1
                                   - TRUNC(LEVEL/8) - 7, -- 7 day less to get back on sunday of previous week
                                   'ww-yyyy'
                               )
                               ELSE
                               TO_CHAR(
                                   NEXT_DAY(TO_DATE(:ym, '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
                            END,
                               ','
                           ),
                           2
                       )
                           days
                  FROM DUAL
            CONNECT BY LEVEL <= 115
              ORDER BY LEVEL DESC)
     WHERE ROWNUM = 1 
					 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
);

Open in new window


I am getting an error. I think paste is not correct on  my side.

ORA-00936: missing expression
sample-date.txt
0
 
anumosesAuthor Commented:
Dont worry I got it.
0
 
anumosesAuthor Commented:
Thanks
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.