/****************************************************************************************/
/*                                                                                      */
/*  Kroenke, Auer, Vandenberg, and Yoder - Database Concepts (8th Edition) Chapter 08   */
/*                                                                                      */
/*  Heather Sweeney Designs Data Warehouse Database Insert Data (PAYMENT_TYPE) and    */
/*			PRODUCT_SALES updates						*/
/*                                                                                      */
/*  These are the Oracle XE code solutions                              */
/*                                                                                      */
/****************************************************************************************/

INSERT INTO PAYMENT_TYPE VALUES(1, 'VISA');
INSERT INTO PAYMENT_TYPE VALUES(2, 'MasterCard');
INSERT INTO PAYMENT_TYPE VALUES(3, 'American Express');
INSERT INTO PAYMENT_TYPE VALUES(4, 'Check');
INSERT INTO PAYMENT_TYPE VALUES(5, 'Cash');
INSERT INTO PAYMENT_TYPE VALUES(6, 'Unknown');


/********************************************************************************/
/*																				*/
/*  HSD-DW PRODUCT_SALES Data PaymentType Data									*/
/*																				*/
/*  PRIMARY KEY = (TimeID, CustomerID, ProductNumber)							*/
/*																				*/
/*																				*/
/********************************************************************************/


/* Update PRODUCT_SALES table with PAYMENT-TYPE data from INVOICE and LINE_ITEM tables */
/*****   Invoice 35000   **************************************************/

UPDATE PRODUCT_SALES SET PaymentTypeID = 1
    WHERE       TimeID = 42658
        AND CustomerID = 3
        AND ProductNumber = 'VB001';
 
UPDATE PRODUCT_SALES SET PaymentTypeID = 1
    WHERE       TimeID = 42658
        AND CustomerID = 3
        AND     ProductNumber = 'VK001';
 
/*****   Invoice 35001   **************************************************/

 
UPDATE PRODUCT_SALES SET PaymentTypeID = 2
    WHERE       TimeID = 42668
        AND CustomerID = 4
        AND ProductNumber = 'VK001';
 
UPDATE PRODUCT_SALES SET PaymentTypeID = 2
    WHERE       TimeID = 42668
        AND CustomerID = 4
        AND     ProductNumber = 'VB001';
 
 UPDATE PRODUCT_SALES SET PaymentTypeID = 2
    WHERE       TimeID = 42668
        AND CustomerID = 4
        AND ProductNumber = 'BK001';
 
/*****   Invoice 35002   **************************************************/

 
UPDATE PRODUCT_SALES SET PaymentTypeID = 1
    WHERE       TimeID = 42724
        AND CustomerID = 7
        AND     ProductNumber = 'VK004';
 
/*****   Invoice 35003   **************************************************/

 UPDATE PRODUCT_SALES SET PaymentTypeID = 2
    WHERE       TimeID = 42819
        AND CustomerID = 4
        AND ProductNumber = 'VK002';
 
UPDATE PRODUCT_SALES SET PaymentTypeID = 2
    WHERE       TimeID = 42819
        AND CustomerID = 4
        AND     ProductNumber = 'BK002';
 
 UPDATE PRODUCT_SALES SET PaymentTypeID = 2
    WHERE       TimeID = 42819
        AND CustomerID = 4
        AND ProductNumber = 'VK004';
 
/*****   Invoice 35004   ***************************************************/

UPDATE PRODUCT_SALES SET PaymentTypeID = 2
    WHERE       TimeID = 42821
        AND CustomerID = 6
        AND     ProductNumber = 'VK002';
 
 UPDATE PRODUCT_SALES SET PaymentTypeID = 2
    WHERE       TimeID = 42821
        AND CustomerID = 6
        AND ProductNumber = 'BK002';
 
UPDATE PRODUCT_SALES SET PaymentTypeID = 2
    WHERE       TimeID = 42821
        AND CustomerID = 6
        AND     ProductNumber = 'VK003';
 
 UPDATE PRODUCT_SALES SET PaymentTypeID = 2
    WHERE       TimeID = 42821
        AND CustomerID = 6
        AND ProductNumber = 'VB003';
 
UPDATE PRODUCT_SALES SET PaymentTypeID = 2
    WHERE       TimeID = 42821
        AND CustomerID = 6
        AND     ProductNumber = 'VK004';
 
/*****   Invoice 35005   ***************************************************/

 UPDATE PRODUCT_SALES SET PaymentTypeID = 2
    WHERE       TimeID = 42821
        AND CustomerID = 7
        AND ProductNumber = 'BK001';
 
UPDATE PRODUCT_SALES SET PaymentTypeID = 2
    WHERE       TimeID = 42821
        AND CustomerID = 7
        AND     ProductNumber = 'BK002';
 
 UPDATE PRODUCT_SALES SET PaymentTypeID = 2
    WHERE       TimeID = 42821
        AND CustomerID = 7
        AND ProductNumber = 'VK003';
 
UPDATE PRODUCT_SALES SET PaymentTypeID = 2
    WHERE       TimeID = 42821
        AND CustomerID = 7
        AND     ProductNumber = 'VK004';
 
/*****   Invoice 35006   ***************************************************/

 
 UPDATE PRODUCT_SALES SET PaymentTypeID = 1
    WHERE       TimeID = 42825
        AND CustomerID = 9
        AND ProductNumber = 'BK001';
 
UPDATE PRODUCT_SALES SET PaymentTypeID = 1
    WHERE       TimeID = 42825
        AND CustomerID = 9
        AND     ProductNumber = 'VK001';
 
 UPDATE PRODUCT_SALES SET PaymentTypeID = 1
    WHERE       TimeID = 42825
        AND CustomerID = 9
        AND ProductNumber = 'VB001';
 
/*****   Invoice 35007   ***************************************************/

UPDATE PRODUCT_SALES SET PaymentTypeID = 2
    WHERE       TimeID = 42828
        AND CustomerID = 11
        AND     ProductNumber = 'VK003';
 
UPDATE PRODUCT_SALES SET PaymentTypeID = 2
    WHERE       TimeID = 42828
        AND CustomerID = 11
        AND     ProductNumber = 'VB003';
 
UPDATE PRODUCT_SALES SET PaymentTypeID = 2
    WHERE       TimeID = 42828
        AND CustomerID = 11
        AND     ProductNumber = 'VK004';
 
/*****   Invoice 35008   ***************************************************/

 
UPDATE PRODUCT_SALES SET PaymentTypeID = 2
    WHERE       TimeID = 42833
        AND CustomerID = 5
        AND     ProductNumber = 'BK001';
 
UPDATE PRODUCT_SALES SET PaymentTypeID = 2
    WHERE       TimeID = 42833
        AND CustomerID = 5
        AND     ProductNumber = 'VK001';
 
UPDATE PRODUCT_SALES SET PaymentTypeID = 2
    WHERE       TimeID = 42833
        AND CustomerID = 5
        AND     ProductNumber = 'VB001';
 
/*****   Invoice 35009   ***************************************************/

UPDATE PRODUCT_SALES SET PaymentTypeID = 1
    WHERE       TimeID = 42833
        AND CustomerID = 1
        AND     ProductNumber = 'BK001';
 
UPDATE PRODUCT_SALES SET PaymentTypeID = 1
    WHERE       TimeID = 42833
        AND CustomerID = 1
        AND     ProductNumber = 'VK001';
 
UPDATE PRODUCT_SALES SET PaymentTypeID = 1
    WHERE       TimeID = 42833
        AND CustomerID = 1
        AND     ProductNumber = 'VB001';
 
/*****   Invoice 35010   ***************************************************/

UPDATE PRODUCT_SALES SET PaymentTypeID = 1
    WHERE       TimeID = 42848
        AND CustomerID = 3
        AND     ProductNumber = 'BK001';
 
/*****   Invoice 35011   ***************************************************/

UPDATE PRODUCT_SALES SET PaymentTypeID = 1
    WHERE       TimeID = 42862
        AND CustomerID = 9
        AND     ProductNumber = 'VK002';
 
UPDATE PRODUCT_SALES SET PaymentTypeID = 1
    WHERE       TimeID = 42862
        AND CustomerID = 9
        AND     ProductNumber = 'VB002';
 
/*****   Invoice 35012   ***************************************************/

UPDATE PRODUCT_SALES SET PaymentTypeID = 2
    WHERE       TimeID = 42876
        AND CustomerID = 8
        AND     ProductNumber = 'VK003';
 
UPDATE PRODUCT_SALES SET PaymentTypeID = 2
    WHERE       TimeID = 42876
        AND CustomerID = 8
        AND     ProductNumber = 'VB003';
 
UPDATE PRODUCT_SALES SET PaymentTypeID = 2
    WHERE       TimeID = 42876
        AND CustomerID = 8
        AND     ProductNumber = 'VK004';
 
/*****   Invoice 35013 and 35016 processed together, merge two sales of VK002 and VB002 for */
/* Same customer 3 and same Date 42891, thus we have 48 rows in PRODUCT_SALES and 50 LINE_ITEMS *******/
/* we have 7 line items, and 5 inserts since we skip the duplicates. */ 
/* The Quantity is correct in the Product_Sales table */ 
/*****   Invoice 35013   ***************************************************/

/*****   Invoice 35016   ***************************************************/

UPDATE PRODUCT_SALES SET PaymentTypeID = 1
    WHERE       TimeID = 42891
        AND CustomerID = 3
        AND     ProductNumber = 'VK002';
 
UPDATE PRODUCT_SALES SET PaymentTypeID = 1
    WHERE       TimeID = 42891
        AND CustomerID = 3
        AND     ProductNumber = 'VB002';
 
UPDATE PRODUCT_SALES SET PaymentTypeID = 1
    WHERE       TimeID = 42891
        AND CustomerID = 3
        AND     ProductNumber = 'BK002';
 
UPDATE PRODUCT_SALES SET PaymentTypeID = 1
    WHERE       TimeID = 42891
        AND CustomerID = 3
        AND     ProductNumber = 'VK001';
 
UPDATE PRODUCT_SALES SET PaymentTypeID = 1
    WHERE       TimeID = 42891
        AND CustomerID = 3
        AND     ProductNumber = 'VB001';
 
/*****   Invoice 35014   ***************************************************/

UPDATE PRODUCT_SALES SET PaymentTypeID = 1
    WHERE       TimeID = 42891
        AND CustomerID = 11
        AND     ProductNumber = 'VK002';
 
UPDATE PRODUCT_SALES SET PaymentTypeID = 1
    WHERE       TimeID = 42891
        AND CustomerID = 11
        AND     ProductNumber = 'VB002';
 
 
/*****   Invoice 35015   ***************************************************/

UPDATE PRODUCT_SALES SET PaymentTypeID = 2
    WHERE       TimeID = 42891
        AND CustomerID = 12
        AND     ProductNumber = 'VK002';
 
UPDATE PRODUCT_SALES SET PaymentTypeID = 2
    WHERE       TimeID = 42891
        AND CustomerID = 12
        AND     ProductNumber = 'BK002';
 
UPDATE PRODUCT_SALES SET PaymentTypeID = 2
    WHERE       TimeID = 42891
        AND CustomerID = 12
        AND     ProductNumber = 'VK003';
 
UPDATE PRODUCT_SALES SET PaymentTypeID = 2
    WHERE       TimeID = 42891
        AND CustomerID = 12
        AND     ProductNumber = 'VB003';
 
UPDATE PRODUCT_SALES SET PaymentTypeID = 2
    WHERE       TimeID = 42891
        AND CustomerID = 12
        AND     ProductNumber = 'VK004';

/********************************************************************************/
