/*
|| Script:  APInitialization.sql
|| Version: 10.2.0.1.0
|| Object:  Loads prototype Accounts Payable (AP) schema with 
||          test data
|| Author:  Jim Czuprynski (G+R)
*/

-----
-- Deactivate constraints before table reloading
-----
ALTER TABLE ap.invoices 
    DISABLE CONSTRAINT invoices_vendor_fk;
ALTER TABLE ap.invoice_items 
    DISABLE CONSTRAINT invoice_items_invoice_fk;
TRUNCATE TABLE ap.invoice_items;
TRUNCATE TABLE ap.invoices;
TRUNCATE TABLE ap.vendors;

-----
-- Load test data into AP.VENDORS
-----
INSERT INTO ap.vendors 
VALUES(
     101
    ,'Y'
    ,'FacesRUs, Inc.'
    ,'1313 Mockingbird Lane'
    ,NULL
    ,NULL
    ,'Seattle'
    ,'WA'
    ,3428179310003762
    ,100000.00
);
    
INSERT INTO ap.vendors 
VALUES(
     102
    ,'Y'
    ,'Scared Silly Makeup Gmbh'
    ,'1221 Frightful Plaza'
    ,'Suite 221'
    ,NULL
    ,'Redmond'
    ,'CA'
    ,5968100071234567
    ,75000.00
);
    
INSERT INTO ap.vendors 
VALUES(
     103
    ,'Y'
    ,'Blood Imitation Products, LLC'
    ,'1313 Gorey Lane'
    ,NULL
    ,NULL
    ,'Reston'
    ,'VA'
    ,6879110044283978
    ,35000.00
);

INSERT INTO ap.vendors 
VALUES(
     104
    ,'Y'
    ,'Tri-Color Corn NA'
    ,'1414 Epicurean Blvd.'
    ,NULL
    ,NULL
    ,'San Francisco'
    ,'CA'
    ,5967100039131004
    ,22500.00
);

INSERT INTO ap.vendors 
VALUES(
     105
    ,'Y'
    ,'Superhero Costumes Corporation'
    ,'1505 Clark Kent Drive'
    ,'Suite 200'
    ,NULL
    ,'Metropolis'
    ,'IL'
    ,38271780012103
    ,100000.00
);

COMMIT;

ALTER TABLE ap.invoices 
    ENABLE CONSTRAINT invoices_vendor_fk;

-----
-- Load test data into AP.INVOICES AND AP.INVOICE_ITEMS
-----

INSERT INTO ap.invoices 
VALUES(501, 'Y', 'C', 101, 0, 0);
INSERT INTO ap.invoice_items 
VALUES(501, 1, 'Y', 750, 50, 0.50, 25.00, 'N');
INSERT INTO ap.invoice_items 
VALUES(501, 2, 'Y', 775, 10, 21.50, 215.00, 'N');
INSERT INTO ap.invoice_items 
VALUES(501, 3, 'Y', 203, 10, 17.50, 175.00, 'N');
COMMIT;

INSERT INTO ap.invoices 
VALUES(502, 'Y', 'C', 101, 0, 0);
INSERT INTO ap.invoice_items 
VALUES(502, 1, 'Y', 298, 25, 10.00, 250.00, 'Y');
INSERT INTO ap.invoice_items 
VALUES(502, 2, 'Y', 809, 1700, 0.10, 170.00, 'Y');
COMMIT;

INSERT INTO ap.invoices 
VALUES(503, 'Y', 'C', 102, 0, 0);
INSERT INTO ap.invoice_items 
VALUES(503, 1, 'Y', 391, 10, 21.50, 215.00, 'Y');
INSERT INTO ap.invoice_items 
VALUES(503, 2, 'Y', 603, 5, 5.00, 25.00, 'Y');
INSERT INTO ap.invoice_items 
VALUES(503, 3, 'Y', 781, 1, 139.95, 139.95, 'Y');
INSERT INTO ap.invoice_items 
VALUES(503, 4, 'Y', 782, 1400, 0.09, 126.00, 'Y');
INSERT INTO ap.invoice_items 
VALUES(503, 5, 'Y', 795, 12, 5.95, 71.40, 'Y');
COMMIT;

INSERT INTO ap.invoices 
VALUES(504, 'Y', 'C', 102, 0, 0);
INSERT INTO ap.invoice_items 
VALUES(504, 1, 'Y', 503, 1, 122.50, 122.50, 'Y');
INSERT INTO ap.invoice_items 
VALUES(504, 2, 'Y', 897, 10, 21.50, 215.00, 'Y');
COMMIT;

INSERT INTO ap.invoices 
VALUES(505, 'Y', 'C', 102, 0, 0);
INSERT INTO ap.invoice_items 
VALUES(505, 1, 'Y', 507, 10000, 0.07, 7000.00, 'Y');
COMMIT;

INSERT INTO ap.invoices 
VALUES(506, 'Y', 'C', 103, 0, 0);
INSERT INTO ap.invoice_items 
VALUES(506, 1, 'Y', 901, 10, 21.50, 215.00, 'Y');
INSERT INTO ap.invoice_items 
VALUES(506, 2, 'Y', 103, 1, 193.81, 193.81, 'Y');
INSERT INTO ap.invoice_items 
VALUES(506, 3, 'Y', 212, 140, 0.47, 66.22, 'N');
COMMIT;

INSERT INTO ap.invoices 
VALUES(507, 'Y', 'C', 104, 0, 0);
INSERT INTO ap.invoice_items 
VALUES(507, 1, 'Y', 293, 17, 23.95, 407.15, 'Y');
INSERT INTO ap.invoice_items 
VALUES(507, 2, 'Y', 503, 340, 1.99, 676.60, 'Y');
COMMIT;

INSERT INTO ap.invoices 
VALUES(508, 'Y', 'C', 105, 0, 0);
INSERT INTO ap.invoice_items 
VALUES(508, 1, 'Y', 400, 100, 12.50, 1250.00, 'Y');
INSERT INTO ap.invoice_items 
VALUES(508, 2, 'Y', 401, 10000, 0.95, 9500.00, 'Y');
INSERT INTO ap.invoice_items 
VALUES(508, 3, 'Y', 402, 100, 31.37, 3137.00, 'Y');
INSERT INTO ap.invoice_items 
VALUES(508, 4, 'Y', 403, 1, 129.85, 129.85, 'Y');
COMMIT;

INSERT INTO ap.invoices 
VALUES(509, 'Y', 'C', 105, 0, 0);
COMMIT;
INSERT INTO ap.invoice_items 
VALUES(509, 1, 'Y', 641, 813, 1.95, 1585.35, 'N');
INSERT INTO ap.invoice_items 
VALUES(509, 2, 'Y', 976, 1626, 0.98, 1593.48, 'N');
INSERT INTO ap.invoice_items 
VALUES(509, 3, 'Y', 221, 405, 2.98, 1206.90, 'Y');
COMMIT;

ALTER TABLE ap.invoice_items 
    ENABLE CONSTRAINT invoice_items_invoice_fk;