-- Lab 7
-- Source instructor files.
-- @..\lab7\lab7.sql

SPOOL lab8.log

--    _____ __                ___
--   / ___// /____  ____     <  /
--   \__ \/ __/ _ \/ __ \    / /
--  ___/ / /_/  __/ /_/ /   / /
-- /____/\__/\___/ .___/   /_/
--              /_/

BEGIN
FOR i IN (SELECT TABLE_NAME
            FROM   user_tables
            WHERE  TABLE_NAME IN ('CALENDAR')) LOOP
    EXECUTE IMMEDIATE 'DROP TABLE '||i.TABLE_NAME||' CASCADE CONSTRAINTS';
END LOOP;
END;
/
DROP SEQUENCE CALENDAR_s1;


-- Create table.
CREATE TABLE CALENDAR
( CALENDAR_ID                 INT
, CALENDAR_NAME               VARCHAR(10)   CONSTRAINT nn_CALENDAR_1  NOT NULL
, CALENDAR_SHORT_NAME         VARCHAR(3)    CONSTRAINT nn_CALENDAR_2  NOT NULL
, START_DATE                  DATE          CONSTRAINT nn_CALENDAR_3  NOT NULL
, END_DATE                    DATE          CONSTRAINT nn_CALENDAR_4  NOT NULL
, created_by                  INT           CONSTRAINT nn_CALENDAR_8  NOT NULL
, creation_date               DATE          CONSTRAINT nn_CALENDAR_9  NOT NULL
, last_updated_by             INT           CONSTRAINT nn_CALENDAR_10 NOT NULL
, last_update_date            DATE          CONSTRAINT nn_CALENDAR_11 NOT NULL
, CONSTRAINT fk_CALENDAR_4 FOREIGN KEY(created_by)       REFERENCES system_user(system_user_id)
, CONSTRAINT fk_CALENDAR_5 FOREIGN KEY(last_updated_by)  REFERENCES system_user(system_user_id));

-- Create sequence.
CREATE SEQUENCE CALENDAR_s1 START WITH 1;



--    _____ __                ___
--   / ___// /____  ____     |__ \
--   \__ \/ __/ _ \/ __ \    __/ /
--  ___/ / /_/  __/ /_/ /   / __/
-- /____/\__/\___/ .___/   /____/
--              /_/
-- Seeding the calendar

DECLARE
  -- Create local collection data types.
  TYPE smonth IS TABLE OF VARCHAR2(3);
  TYPE lmonth IS TABLE OF VARCHAR2(9);

  -- Declare month arrays.
  CALENDAR_NAME SMONTH := smonth('JAN','FEB','MAR','APR','MAY','JUN'
                              ,'JUL','AUG','SEP','OCT','NOV','DEC');
  CALENDAR_SHORT_NAME  LMONTH := lmonth('January','February','March','April','May','June'
                              ,'July','August','September','October','November','December');

  -- Declare base dates.
  START_DATE DATE := '01-JAN-09';
  END_DATE   DATE := '31-JAN-09';

  -- Declare years.
  years      NUMBER := 1;

BEGIN

  -- Loop through years and months.
  FOR i IN 1..years LOOP
    FOR j IN 1..CALENDAR_SHORT_NAME.COUNT LOOP
      INSERT INTO CALENDAR VALUES
      ( CALENDAR_s1.NEXTVAL
      , CALENDAR_SHORT_NAME(j)
      , CALENDAR_NAME(j)
      , add_months(start_date,(j-1)+(12*(i-1)))
      , add_months(end_date,(j-1)+(12*(i-1)))
      , 1
      , SYSDATE
      , 1
      , SYSDATE
      );
    END LOOP;
  END LOOP;

END;
/


--    _____ __                _____
--   / ___// /____  ____     |__  /
--   \__ \/ __/ _ \/ __ \     /_ <
--  ___/ / /_/  __/ /_/ /   ___/ /
-- /____/\__/\___/ .___/   /____/
--              /_/


BEGIN
FOR i IN (SELECT TABLE_NAME
            FROM   user_tables
            WHERE  TABLE_NAME IN ('TRANSACTION_REVERSAL')) LOOP

    EXECUTE IMMEDIATE 'DROP TABLE '||i.TABLE_NAME||' CASCADE CONSTRAINTS';
END LOOP;
END;
/
DROP SEQUENCE TRANSACTION_REVERSAL_s1;


-- Create table.
CREATE TABLE TRANSACTION_REVERSAL
( TRANSACTION_ID              INT
, TRANSACTION_ACCOUNT         VARCHAR(15)
, TRANSACTION_TYPE            INT
, TRANSACTION_DATE            DATE
, TRANSACTION_AMOUNT          FLOAT
, RENTAL_ID                   INT
, PAYMENT_METHOD_TYPE         INT
, PAYMENT_ACCOUNT_NUMBER      VARCHAR(19)
, created_by                  INT
, creation_date               DATE
, last_updated_by             INT
, last_update_date            DATE
) ORGANIZATION EXTERNAL
   ( TYPE oracle_loader
    DEFAULT DIRECTORY download
    ACCESS PARAMETERS
    ( RECORDS DELIMITED BY NEWLINE CHARACTERSET US7ASCII
      NOBADFILE
      NODISCARDFILE
      NOLOGFILE
      FIELDS TERMINATED BY ','
      OPTIONALLY ENCLOSED BY "'"
      MISSING FIELD VALUES ARE NULL )
    LOCATION ('transaction_upload2.csv'))
REJECT LIMIT 0;
);



-- Create sequence.
CREATE SEQUENCE TRANSACTION_REVERSAL_s1 START WITH 1;

SET linesize 30;
DESC TRANSACTION_REVERSAL;

SET linesize 1000;




INSERT INTO TRANSACTION
( transaction_id
, transaction_account
, transaction_type
, transaction_date
, transaction_amount
, rental_id
, payment_method_type
, payment_account_number
, created_by
, creation_date
, last_updated_by
, last_update_date)
SELECT
  transaction_s1.NEXTVAL
, transaction_account
, transaction_type
, transaction_date
, transaction_amount
, rental_id
, payment_method_type
, payment_account_number
, created_by
, creation_date
, last_updated_by
, last_update_date
FROM transaction_reversal;



SET PAGESIZE 24

COLUMN "Debit Transactions"  FORMAT A20
COLUMN "Credit Transactions" FORMAT A20
COLUMN "All Transactions"    FORMAT A20

-- Check current contents of the model.
SELECT 'SELECT record counts' AS "Statement" FROM dual;
SELECT   LPAD(TO_CHAR(c1.transaction_count,'99,999'),19,' ') AS "Debit Transactions"
,        LPAD(TO_CHAR(c2.transaction_count,'99,999'),19,' ') AS "Credit Transactions"
,        LPAD(TO_CHAR(c3.transaction_count,'99,999'),19,' ') AS "All Transactions"
FROM    (SELECT COUNT(*) AS transaction_count FROM TRANSACTION WHERE transaction_account = '111-111-111-111') c1 CROSS JOIN
        (SELECT COUNT(*) AS transaction_count FROM TRANSACTION WHERE transaction_account = '222-222-222-222') c2 CROSS JOIN
        (SELECT COUNT(*) AS transaction_count FROM TRANSACTION) c3;


UPDATE TRANSACTION
     SET    transaction_type = 1024
     WHERE  transaction_type = 1026;





--    _____ __                __ __
--   / ___// /____  ____     / // /
--   \__ \/ __/ _ \/ __ \   / // /_
--  ___/ / /_/  __/ /_/ /  /__  __/
-- /____/\__/\___/ .___/     /_/
--              /_/


SELECT
  il.TRANSACTION_ACCOUNT
, il.MONTH1 AS "Jan"
, il.MONTH2 AS "Feb"
, il.MONTH3 AS "Mar"
, il.F1Q AS "F1Q"
, il.MONTH4 AS "Apr"
, il.MONTH5 AS "May"
, il.MONTH6 AS "Jun"
, il.F2Q AS "F2Q"
, il.MONTH7 AS "Jul"
, il.MONTH8 AS "Aug"
, il.MONTH9 AS "Sep"
, il.F3Q AS "F3Q"
, il.MONTH10 AS "Oct"
, il.MONTH11 AS "Nov"
, il.MONTH12 AS "Dec"
, il.F4Q AS "F4Q"
, il.YEARTODATE AS "YTD"
FROM (SELECT
CASE
           WHEN t.transaction_account = '111-111-111-111' THEN 'Debit'
           WHEN t.transaction_account = '222-222-222-222' THEN 'Credit'
         END AS "TRANSACTION_ACCOUNT"
,        CASE
           WHEN t.transaction_account = '111-111-111-111' THEN 1
           WHEN t.transaction_account = '222-222-222-222' THEN 2
         END AS "SORTKEY"
,        TO_CHAR
        (SUM(CASE
               WHEN EXTRACT(MONTH FROM transaction_date) = 1 AND
                    EXTRACT(YEAR FROM transaction_date) = 2009 THEN
                 CASE
                   WHEN cl.common_lookup_type = 'DEBIT'
                   THEN t.transaction_amount
                   ELSE t.transaction_amount * -1
                 END
             END),'99,999.00') AS MONTH1
,        TO_CHAR
        (SUM(CASE
               WHEN EXTRACT(MONTH FROM transaction_date) = 2 AND
                    EXTRACT(YEAR FROM transaction_date) = 2009 THEN
                 CASE
                   WHEN cl.common_lookup_type = 'DEBIT'
                   THEN t.transaction_amount
                   ELSE t.transaction_amount * -1
                 END
             END),'99,999.00') AS MONTH2
,       TO_CHAR
        (SUM(CASE
               WHEN EXTRACT(MONTH FROM transaction_date) = 2 AND
                    EXTRACT(YEAR FROM transaction_date) = 2009 THEN
                 CASE
                   WHEN cl.common_lookup_type = 'DEBIT'
                   THEN t.transaction_amount
                   ELSE t.transaction_amount * -1
                 END
             END),'99,999.00') AS MONTH3
,       TO_CHAR
        (SUM(CASE
               WHEN EXTRACT(MONTH FROM transaction_date) IN (1, 2, 3) AND
                    EXTRACT(YEAR FROM transaction_date) = 2009 THEN
                 CASE
                   WHEN cl.common_lookup_type = 'DEBIT'
                   THEN t.transaction_amount
                   ELSE t.transaction_amount * -1
                 END
             END),'99,999.00') AS "F1Q"
,        TO_CHAR
        (SUM(CASE
               WHEN EXTRACT(MONTH FROM transaction_date) = 4 AND
                    EXTRACT(YEAR FROM transaction_date) = 2009 THEN
                 CASE
                   WHEN cl.common_lookup_type = 'DEBIT'
                   THEN t.transaction_amount
                   ELSE t.transaction_amount * -1
                 END
             END),'99,999.00') AS MONTH4
,        TO_CHAR
        (SUM(CASE
               WHEN EXTRACT(MONTH FROM transaction_date) = 5 AND
                    EXTRACT(YEAR FROM transaction_date) = 2009 THEN
                 CASE
                   WHEN cl.common_lookup_type = 'DEBIT'
                   THEN t.transaction_amount
                   ELSE t.transaction_amount * -1
                 END
             END),'99,999.00') AS MONTH5
,       TO_CHAR
        (SUM(CASE
               WHEN EXTRACT(MONTH FROM transaction_date) = 6 AND
                    EXTRACT(YEAR FROM transaction_date) = 2009 THEN
                 CASE
                   WHEN cl.common_lookup_type = 'DEBIT'
                   THEN t.transaction_amount
                   ELSE t.transaction_amount * -1
                 END
             END),'99,999.00') AS MONTH6
,       TO_CHAR
        (SUM(CASE
               WHEN EXTRACT(MONTH FROM transaction_date) IN (4, 5, 6) AND
                    EXTRACT(YEAR FROM transaction_date) = 2009 THEN
                 CASE
                   WHEN cl.common_lookup_type = 'DEBIT'
                   THEN t.transaction_amount
                   ELSE t.transaction_amount * -1
                 END
             END),'99,999.00') AS "F2Q"
,        TO_CHAR
        (SUM(CASE
               WHEN EXTRACT(MONTH FROM transaction_date) = 7 AND
                    EXTRACT(YEAR FROM transaction_date) = 2009 THEN
                 CASE
                   WHEN cl.common_lookup_type = 'DEBIT'
                   THEN t.transaction_amount
                   ELSE t.transaction_amount * -1
                 END
             END),'99,999.00') AS MONTH7
,        TO_CHAR
        (SUM(CASE
               WHEN EXTRACT(MONTH FROM transaction_date) = 8 AND
                    EXTRACT(YEAR FROM transaction_date) = 2009 THEN
                 CASE
                   WHEN cl.common_lookup_type = 'DEBIT'
                   THEN t.transaction_amount
                   ELSE t.transaction_amount * -1
                 END
             END),'99,999.00') AS MONTH8
,       TO_CHAR
        (SUM(CASE
               WHEN EXTRACT(MONTH FROM transaction_date) = 9 AND
                    EXTRACT(YEAR FROM transaction_date) = 2009 THEN
                 CASE
                   WHEN cl.common_lookup_type = 'DEBIT'
                   THEN t.transaction_amount
                   ELSE t.transaction_amount * -1
                 END
             END),'99,999.00') AS MONTH9
,       TO_CHAR
        (SUM(CASE
               WHEN EXTRACT(MONTH FROM transaction_date) IN (7, 8, 9) AND
                    EXTRACT(YEAR FROM transaction_date) = 2009 THEN
                 CASE
                   WHEN cl.common_lookup_type = 'DEBIT'
                   THEN t.transaction_amount
                   ELSE t.transaction_amount * -1
                 END
             END),'99,999.00') AS "F3Q"
,        TO_CHAR
        (SUM(CASE
               WHEN EXTRACT(MONTH FROM transaction_date) = 10 AND
                    EXTRACT(YEAR FROM transaction_date) = 2009 THEN
                 CASE
                   WHEN cl.common_lookup_type = 'DEBIT'
                   THEN t.transaction_amount
                   ELSE t.transaction_amount * -1
                 END
             END),'99,999.00') AS MONTH10
,        TO_CHAR
        (SUM(CASE
               WHEN EXTRACT(MONTH FROM transaction_date) = 11 AND
                    EXTRACT(YEAR FROM transaction_date) = 2009 THEN
                 CASE
                   WHEN cl.common_lookup_type = 'DEBIT'
                   THEN t.transaction_amount
                   ELSE t.transaction_amount * -1
                 END
             END),'99,999.00') AS MONTH11
,       TO_CHAR
        (SUM(CASE
               WHEN EXTRACT(MONTH FROM transaction_date) = 12 AND
                    EXTRACT(YEAR FROM transaction_date) = 2009 THEN
                 CASE
                   WHEN cl.common_lookup_type = 'DEBIT'
                   THEN t.transaction_amount
                   ELSE t.transaction_amount * -1
                 END
             END),'99,999.00') AS MONTH12
,       TO_CHAR
        (SUM(CASE
               WHEN EXTRACT(MONTH FROM transaction_date) IN (10, 11, 12) AND
                    EXTRACT(YEAR FROM transaction_date) = 2009 THEN
                 CASE
                   WHEN cl.common_lookup_type = 'DEBIT'
                   THEN t.transaction_amount
                   ELSE t.transaction_amount * -1
                 END
             END),'99,999.00') AS "F4Q"
,       TO_CHAR
        (SUM(CASE
               WHEN EXTRACT(YEAR FROM transaction_date) = 2009 THEN
                 CASE
                   WHEN cl.common_lookup_type = 'DEBIT'
                   THEN t.transaction_amount
                   ELSE t.transaction_amount * -1
                 END
             END),'99,999.00') AS "YEARTODATE"
     FROM TRANSACTION t
     INNER JOIN common_lookup cl
     ON       t.transaction_type = cl.common_lookup_id
     AND    cl.common_lookup_table = 'TRANSACTION'
     AND      cl.common_lookup_column = 'TRANSACTION_TYPE'
     INNER JOIN calendar c
     ON       t.transaction_date BETWEEN c.start_date AND c.end_date
     GROUP BY CASE
                WHEN t.transaction_account = '111-111-111-111' THEN 'Debit'
                WHEN t.transaction_account = '222-222-222-222' THEN 'Credit'
              END
     ,        CASE
                WHEN t.transaction_account = '111-111-111-111' THEN 1
                WHEN t.transaction_account = '222-222-222-222' THEN 2
              END
     ORDER BY CASE
           WHEN t.transaction_account = '111-111-111-111' THEN 1
           WHEN t.transaction_account = '222-222-222-222' THEN 2
         END  ) il
UNION ALL
SELECT
  il.TRANSACTION_ACCOUNT
, il.MONTH1 AS "Jan"
, il.MONTH2 AS "Feb"
, il.MONTH3 AS "Mar"
, il.F1Q AS "F1Q"
, il.MONTH4 AS "Apr"
, il.MONTH5 AS "May"
, il.MONTH6 AS "Jun"
, il.F2Q AS "F2Q"
, il.MONTH7 AS "Jul"
, il.MONTH8 AS "Aug"
, il.MONTH9 AS "Sep"
, il.F3Q AS "F3Q"
, il.MONTH10 AS "Oct"
, il.MONTH11 AS "Nov"
, il.MONTH12 AS "Dec"
, il.F4Q AS "F4Q"
, il.YEARTODATE AS "YTD"
FROM (SELECT
CASE
           WHEN t.transaction_account = '111-111-111-111' THEN 'Total'
           WHEN t.transaction_account = '222-222-222-222' THEN 'Total'
         END AS "TRANSACTION_ACCOUNT"
,        CASE
           WHEN t.transaction_account = '111-111-111-111' THEN 3
           WHEN t.transaction_account = '222-222-222-222' THEN 3
         END AS "SORTKEY"
,        TO_CHAR
        (SUM(CASE
               WHEN EXTRACT(MONTH FROM transaction_date) = 1 AND
                    EXTRACT(YEAR FROM transaction_date) = 2009 THEN
                 CASE
                   WHEN cl.common_lookup_type = 'DEBIT'
                   THEN t.transaction_amount
                   ELSE t.transaction_amount * -1
                 END
             END),'99,999.00') AS MONTH1
,        TO_CHAR
        (SUM(CASE
               WHEN EXTRACT(MONTH FROM transaction_date) = 2 AND
                    EXTRACT(YEAR FROM transaction_date) = 2009 THEN
                 CASE
                   WHEN cl.common_lookup_type = 'DEBIT'
                   THEN t.transaction_amount
                   ELSE t.transaction_amount * -1
                 END
             END),'99,999.00') AS MONTH2
,       TO_CHAR
        (SUM(CASE
               WHEN EXTRACT(MONTH FROM transaction_date) = 2 AND
                    EXTRACT(YEAR FROM transaction_date) = 2009 THEN
                 CASE
                   WHEN cl.common_lookup_type = 'DEBIT'
                   THEN t.transaction_amount
                   ELSE t.transaction_amount * -1
                 END
             END),'99,999.00') AS MONTH3
,       TO_CHAR
        (SUM(CASE
               WHEN EXTRACT(MONTH FROM transaction_date) IN (1, 2, 3) AND
                    EXTRACT(YEAR FROM transaction_date) = 2009 THEN
                 CASE
                   WHEN cl.common_lookup_type = 'DEBIT'
                   THEN t.transaction_amount
                   ELSE t.transaction_amount * -1
                 END
             END),'99,999.00') AS "F1Q"
,        TO_CHAR
        (SUM(CASE
               WHEN EXTRACT(MONTH FROM transaction_date) = 4 AND
                    EXTRACT(YEAR FROM transaction_date) = 2009 THEN
                 CASE
                   WHEN cl.common_lookup_type = 'DEBIT'
                   THEN t.transaction_amount
                   ELSE t.transaction_amount * -1
                 END
             END),'99,999.00') AS MONTH4
,        TO_CHAR
        (SUM(CASE
               WHEN EXTRACT(MONTH FROM transaction_date) = 5 AND
                    EXTRACT(YEAR FROM transaction_date) = 2009 THEN
                 CASE
                   WHEN cl.common_lookup_type = 'DEBIT'
                   THEN t.transaction_amount
                   ELSE t.transaction_amount * -1
                 END
             END),'99,999.00') AS MONTH5
,       TO_CHAR
        (SUM(CASE
               WHEN EXTRACT(MONTH FROM transaction_date) = 6 AND
                    EXTRACT(YEAR FROM transaction_date) = 2009 THEN
                 CASE
                   WHEN cl.common_lookup_type = 'DEBIT'
                   THEN t.transaction_amount
                   ELSE t.transaction_amount * -1
                 END
             END),'99,999.00') AS MONTH6
,       TO_CHAR
        (SUM(CASE
               WHEN EXTRACT(MONTH FROM transaction_date) IN (4, 5, 6) AND
                    EXTRACT(YEAR FROM transaction_date) = 2009 THEN
                 CASE
                   WHEN cl.common_lookup_type = 'DEBIT'
                   THEN t.transaction_amount
                   ELSE t.transaction_amount * -1
                 END
             END),'99,999.00') AS "F2Q"
,        TO_CHAR
        (SUM(CASE
               WHEN EXTRACT(MONTH FROM transaction_date) = 7 AND
                    EXTRACT(YEAR FROM transaction_date) = 2009 THEN
                 CASE
                   WHEN cl.common_lookup_type = 'DEBIT'
                   THEN t.transaction_amount
                   ELSE t.transaction_amount * -1
                 END
             END),'99,999.00') AS MONTH7
,        TO_CHAR
        (SUM(CASE
               WHEN EXTRACT(MONTH FROM transaction_date) = 8 AND
                    EXTRACT(YEAR FROM transaction_date) = 2009 THEN
                 CASE
                   WHEN cl.common_lookup_type = 'DEBIT'
                   THEN t.transaction_amount
                   ELSE t.transaction_amount * -1
                 END
             END),'99,999.00') AS MONTH8
,       TO_CHAR
        (SUM(CASE
               WHEN EXTRACT(MONTH FROM transaction_date) = 9 AND
                    EXTRACT(YEAR FROM transaction_date) = 2009 THEN
                 CASE
                   WHEN cl.common_lookup_type = 'DEBIT'
                   THEN t.transaction_amount
                   ELSE t.transaction_amount * -1
                 END
             END),'99,999.00') AS MONTH9
,       TO_CHAR
        (SUM(CASE
               WHEN EXTRACT(MONTH FROM transaction_date) IN (7, 8, 9) AND
                    EXTRACT(YEAR FROM transaction_date) = 2009 THEN
                 CASE
                   WHEN cl.common_lookup_type = 'DEBIT'
                   THEN t.transaction_amount
                   ELSE t.transaction_amount * -1
                 END
             END),'99,999.00') AS "F3Q"
,        TO_CHAR
        (SUM(CASE
               WHEN EXTRACT(MONTH FROM transaction_date) = 10 AND
                    EXTRACT(YEAR FROM transaction_date) = 2009 THEN
                 CASE
                   WHEN cl.common_lookup_type = 'DEBIT'
                   THEN t.transaction_amount
                   ELSE t.transaction_amount * -1
                 END
             END),'99,999.00') AS MONTH10
,        TO_CHAR
        (SUM(CASE
               WHEN EXTRACT(MONTH FROM transaction_date) = 11 AND
                    EXTRACT(YEAR FROM transaction_date) = 2009 THEN
                 CASE
                   WHEN cl.common_lookup_type = 'DEBIT'
                   THEN t.transaction_amount
                   ELSE t.transaction_amount * -1
                 END
             END),'99,999.00') AS MONTH11
,       TO_CHAR
        (SUM(CASE
               WHEN EXTRACT(MONTH FROM transaction_date) = 12 AND
                    EXTRACT(YEAR FROM transaction_date) = 2009 THEN
                 CASE
                   WHEN cl.common_lookup_type = 'DEBIT'
                   THEN t.transaction_amount
                   ELSE t.transaction_amount * -1
                 END
             END),'99,999.00') AS MONTH12
,       TO_CHAR
        (SUM(CASE
               WHEN EXTRACT(MONTH FROM transaction_date) IN (10, 11, 12) AND
                    EXTRACT(YEAR FROM transaction_date) = 2009 THEN
                 CASE
                   WHEN cl.common_lookup_type = 'DEBIT'
                   THEN t.transaction_amount
                   ELSE t.transaction_amount * -1
                 END
             END),'99,999.00') AS "F4Q"
,       TO_CHAR
        (SUM(CASE
               WHEN EXTRACT(YEAR FROM transaction_date) = 2009 THEN
                 CASE
                   WHEN cl.common_lookup_type = 'DEBIT'
                   THEN t.transaction_amount
                   ELSE t.transaction_amount * -1
                 END
             END),'99,999.00') AS "YEARTODATE"
     FROM TRANSACTION t
     INNER JOIN common_lookup cl
     ON       t.transaction_type = cl.common_lookup_id
     AND    cl.common_lookup_table = 'TRANSACTION'
     AND      cl.common_lookup_column = 'TRANSACTION_TYPE'
     INNER JOIN calendar c
     ON       t.transaction_date BETWEEN c.start_date AND c.end_date
     GROUP BY CASE
                WHEN t.transaction_account = '111-111-111-111' THEN 'Total'
                WHEN t.transaction_account = '222-222-222-222' THEN 'Total'
              END
     ,        CASE
                WHEN t.transaction_account = '111-111-111-111' THEN 3
                WHEN t.transaction_account = '222-222-222-222' THEN 3
              END) il;


SET linesize 1000;
SPOOL OFF