SET TERM ^ ;

create or alter procedure monthly_pack_slip (
    datefrom date,
    dateto date)
returns (
    pack_slip_no varchar(10),
    invoiceno integer,
    orderno integer,
    suborderno integer,
    date_ship date,
    custno integer,
    company_name varchar(45),
    purchase_order_no varchar(20),
    part_no varchar(10),
    total_qty_ship integer,
    price float,
    total float)
as
BEGIN
FOR
 SELECT
  SHIPPING.PACK_SLIP_NO,
  INVOICES.INVOICENO,
  SHIPPING.ORDERNO,
  SHIPPING.SUBORDERNO,
  SHIPPING.DATE_SHIP,
  ORDERS.CUSTNO,
  COMPANY.COMPANY_NAME,
  ORDERS.PURCHASE_ORDER_NO,
  ORDERS_PRODUCT.PART_NO,
  ORDERS_PRODUCT.TOTAL_QTY_SHIP,
  INVOICES_PART.PRICE,
  ORDERS_PRODUCT.TOTAL_QTY_SHIP*INVOICES_PART.PRICE AS TOTAL
 FROM ORDERS join SHIPPING on SHIPPING.ORDERNO=ORDERS.ORDERNO   
  join ORDERS_PRODUCT on SHIPPING.ORDERNO=ORDERS_PRODUCT.ORDER_NO
  join COMPANY on ORDERS.CUSTNO=COMPANY.CUST_NO 
  join INVOICES on SHIPPING.ORDERNO=INVOICES.ORDERNO
  join INVOICES_PART on INVOICES_PART.INVOICENO=INVOICES.INVOICENO 
       and INVOICES_PART.PART_NO=ORDERS_PRODUCT.PART_NO
 WHERE
  SHIPPING.SUBORDERNO IS NULL AND
  SHIPPING.DATE_SHIP>=:DATEFROM AND
  SHIPPING.DATE_SHIP<=:DATETO
UNION
 SELECT
  SHIPPING.PACK_SLIP_NO,
  cast (null as INTEGER) INVOICENO,
  SHIPPING.ORDERNO,
  SHIPPING.SUBORDERNO,
  SHIPPING.DATE_SHIP,
  ORDERS.CUSTNO,
  COMPANY.COMPANY_NAME,
  ORDERS.PURCHASE_ORDER_NO,
  ORDERS_PRODUCT.PART_NO,
  ORDERS_PRODUCT.TOTAL_QTY_SHIP,
  ORDERS_PRODUCT.PRICE,
  ORDERS_PRODUCT.TOTAL_QTY_SHIP*ORDERS_PRODUCT.PRICE AS TOTAL
 FROM ORDERS join SHIPPING on SHIPPING.ORDERNO=ORDERS.ORDERNO   
  join ORDERS_PRODUCT on SHIPPING.ORDERNO=ORDERS_PRODUCT.ORDER_NO
  join COMPANY on ORDERS.CUSTNO=COMPANY.CUST_NO 
 WHERE
  SHIPPING.SUBORDERNO IS NULL AND
  not SHIPPING.ORDERNO in (select orderno from invoices) AND
  SHIPPING.DATE_SHIP>=:DATEFROM AND
  SHIPPING.DATE_SHIP<=:DATETO
UNION
 SELECT 
  SHIPPING.PACK_SLIP_NO,
  INVOICES.INVOICENO,
  SHIPPING.ORDERNO,
  SHIPPING.SUBORDERNO,
  SHIPPING.DATE_SHIP,
  ORDERS.CUSTNO,
  COMPANY.COMPANY_NAME,
  ORDERS.PURCHASE_ORDER_NO,
  ORDERS_PRODUCT.PART_NO,
  SUBORDERS_PRODUCT.QTY_SHIP,
  INVOICES_PART.PRICE,
  SUBORDERS_PRODUCT.QTY_SHIP*INVOICES_PART.PRICE AS TOTAL
 FROM ORDERS JOIN SHIPPING ON SHIPPING.ORDERNO=ORDERS.ORDERNO
 JOIN ORDERS_PRODUCT ON SHIPPING.ORDERNO=ORDERS_PRODUCT.ORDER_NO
 JOIN SUBORDERS_PRODUCT ON SHIPPING.ORDERNO    = SUBORDERS_PRODUCT.ORDERNO AND
                           SHIPPING.SUBORDERNO = SUBORDERS_PRODUCT.SUBORDERNO AND
                        ORDERS_PRODUCT.PART_NO = SUBORDERS_PRODUCT.PART_NO
 JOIN COMPANY ON ORDERS.CUSTNO=COMPANY.CUST_NO 
 JOIN INVOICES ON SHIPPING.ORDERNO    = INVOICES.ORDERNO AND
                  SHIPPING.SUBORDERNO = INVOICES.SUBORDERNO 
 join INVOICES_PART on INVOICES_PART.INVOICENO=INVOICES.INVOICENO 
       and INVOICES_PART.PART_NO=ORDERS_PRODUCT.PART_NO
 WHERE
  SHIPPING.SUBORDERNO IS NOT NULL AND
  SHIPPING.DATE_SHIP>=:DATEFROM AND
  SHIPPING.DATE_SHIP<=:DATETO
UNION
 SELECT 
  SHIPPING.PACK_SLIP_NO,
  cast (null as INTEGER) INVOICENO,
  SHIPPING.ORDERNO,
  SHIPPING.SUBORDERNO,
  SHIPPING.DATE_SHIP,
  ORDERS.CUSTNO,
  COMPANY.COMPANY_NAME,
  ORDERS.PURCHASE_ORDER_NO,
  ORDERS_PRODUCT.PART_NO,
  SUBORDERS_PRODUCT.QTY_SHIP,
  ORDERS_PRODUCT.PRICE,
  SUBORDERS_PRODUCT.QTY_SHIP*ORDERS_PRODUCT.PRICE AS TOTAL
 FROM ORDERS JOIN SHIPPING ON SHIPPING.ORDERNO=ORDERS.ORDERNO
 JOIN ORDERS_PRODUCT ON SHIPPING.ORDERNO=ORDERS_PRODUCT.ORDER_NO
 JOIN SUBORDERS_PRODUCT ON SHIPPING.ORDERNO    = SUBORDERS_PRODUCT.ORDERNO AND
                           SHIPPING.SUBORDERNO = SUBORDERS_PRODUCT.SUBORDERNO AND
                        ORDERS_PRODUCT.PART_NO = SUBORDERS_PRODUCT.PART_NO
 JOIN COMPANY ON ORDERS.CUSTNO=COMPANY.CUST_NO 
 WHERE
  SHIPPING.SUBORDERNO IS NOT NULL AND
  NOT SHIPPING.SUBORDERNO IN 
   (SELECT INVOICES.SUBORDERNO FROM INVOICES WHERE INVOICES.ORDERNO=SHIPPING.ORDERNO) AND
  SHIPPING.DATE_SHIP>=:DATEFROM AND
  SHIPPING.DATE_SHIP<=:DATETO
 INTO :PACK_SLIP_NO, :INVOICENO, :ORDERNO, :SUBORDERNO, :DATE_SHIP , :CUSTNO,
      :COMPANY_NAME, :PURCHASE_ORDER_NO, :PART_NO, :TOTAL_QTY_SHIP,
      :PRICE, :TOTAL
DO SUSPEND;
END^

SET TERM ; ^

/* Following GRANT statements are generated automatically */

GRANT SELECT ON ORDERS TO PROCEDURE MONTHLY_PACK_SLIP;
GRANT SELECT ON SHIPPING TO PROCEDURE MONTHLY_PACK_SLIP;
GRANT SELECT ON ORDERS_PRODUCT TO PROCEDURE MONTHLY_PACK_SLIP;
GRANT SELECT ON COMPANY TO PROCEDURE MONTHLY_PACK_SLIP;
GRANT SELECT ON INVOICES TO PROCEDURE MONTHLY_PACK_SLIP;
GRANT SELECT ON INVOICES_PART TO PROCEDURE MONTHLY_PACK_SLIP;
GRANT SELECT ON SUBORDERS_PRODUCT TO PROCEDURE MONTHLY_PACK_SLIP;

/* Existing privileges on this procedure */

GRANT EXECUTE ON PROCEDURE MONTHLY_PACK_SLIP TO SYSDBA;

CREATE TABLE ORDERS
(
  ORDERNO Integer NOT NULL,
  CUSTNO Integer,
  SHIPTOCOMPANY Varchar(45),
  SHIPTOADDR1 Varchar(30),
  SHIPTOADDR2 Varchar(30),
  SHIPTOADDR3 Varchar(30),
  SHIPTOSTATE Varchar(20),
  SHIPTOZIP Varchar(10),
  SHIPTOCOUNTRY Varchar(20),
  SHIPTOCONTACT Varchar(35),
  ORDER_DATE Timestamp,
  PURCHASE_ORDER_NO Varchar(20),
  TERMS Varchar(15),
  DUE_DATE Timestamp,
  SHIP_DATE Timestamp,
  SHIP_VIA Varchar(7),
  SHIP_METHOD Varchar(15),
  PACK_SLIP_NO Varchar(10),
  SHIPTOPHONE Varchar(15),
  STATUS Varchar(8),
  STATUS_DATE Timestamp,
  SALESPERSON Varchar(6),
  MARK_FOR Varchar(35),
  SALETAX Varchar(1),
  SHIPTOADDRTYPE Varchar(10),
  REMARKS Blob sub_type 1,
  SOME_NAME Varchar(15),
  SHIPPED_DATE Timestamp,
  USE_MAIN4BILL Char(1),
  REP_CREDIT Char(1),
  INDUSTRY Char(1),
  SR_NO Integer,
  SHIPTOCITY Varchar(25),
  COMMENTS Blob sub_type 1,
  BILLTOCOMPANY Varchar(45),
  BILLTOADDR1 Varchar(30),
  BILLTOADDR2 Varchar(30),
  BILLTOADDR3 Varchar(30),
  BILLTOSTATE Varchar(20),
  BILLTOZIP Varchar(10),
  BILLTOCOUNTRY Varchar(20),
  BILLTOCITY Varchar(25),
  BILLTOADDRTYPE Varchar(10),
  CONSTRAINT PK_ORDERS PRIMARY KEY (ORDERNO)
);

CREATE UNIQUE INDEX ORDERS_OC ON ORDERS (ORDERNO,CUSTNO);
CREATE UNIQUE INDEX ORDERS_ORDNO ON ORDERS (ORDERNO);
GRANT DELETE, INSERT, REFERENCES, SELECT, UPDATE
 ON ORDERS TO  "PUBLIC";

GRANT DELETE, INSERT, REFERENCES, SELECT, UPDATE
 ON ORDERS TO  SYSDBA WITH GRANT OPTION;

CREATE TABLE SHIPPING
(
  PACK_SLIP_NO Varchar(10) NOT NULL,
  ORDERNO Integer,
  SUBORDERNO Integer,
  DATE_SHIP Timestamp,
  FREIGHT Float,
  SHIPHANDLE Float,
  PRIMARY KEY (PACK_SLIP_NO)
);
CREATE UNIQUE INDEX SHIPPING_PSN ON SHIPPING (PACK_SLIP_NO);
GRANT DELETE, INSERT, REFERENCES, SELECT, UPDATE
 ON SHIPPING TO  "PUBLIC";

CREATE TABLE ORDERS_PRODUCT
(
  ORDER_NO Integer NOT NULL,
  PART_NO Varchar(10) NOT NULL,
  YOUR_PART_NO Varchar(20),
  TOTAL_QTY Integer,
  TOTAL_QTY_SHIP Integer,
  PRICE Float,
  OPCHANGE Varchar(1),
  PRIMARY KEY (ORDER_NO,PART_NO)
);

CREATE UNIQUE INDEX ORDERS_PRODUCT_ORDPART ON ORDERS_PRODUCT (ORDER_NO,PART_NO);
GRANT DELETE, INSERT, REFERENCES, SELECT, UPDATE
 ON ORDERS_PRODUCT TO  "PUBLIC";

CREATE TABLE COMPANY
(
  CUST_NO Integer,
  COMPANY_NAME Varchar(45),
  CONTACT_FIRST Varchar(30),
  CONTACT_SECOND Varchar(30),
  PHONE_NO Varchar(20),
  STREET_1 Varchar(30),
  STREET_2 Varchar(30),
  CITY Varchar(25),
  STATE_PROVINCE Varchar(20),
  COUNTRY Varchar(15),
  POSTAL_CODE Varchar(12),
  FAX_NO Varchar(20),
  STREET_3 Varchar(30),
  E_MAIL Varchar(45),
  CUST_TYPE Varchar(10),
  BUS_TYPE Varchar(25),
  LAST_ORDER_DAY Timestamp,
  THIS_YEAR_SALES Double precision,
  LAST_YEAR_SALES Double precision,
  YEAR_BEFORE_LAST_SALES Double precision,
  REMARKS Varchar(35),
  LABEL Varchar(3),
  PERCENT_DISCOUNT Float,
  CREDIT_APPROVED Varchar(3),
  REP Varchar(30),
  CREDIT_PROBLEMS Varchar(3),
  DAY_START Timestamp DEFAULT "TODAY",
  CATEGORY Integer,
  BILL_COMPANY_NAME Varchar(45),
  BILL_STREET_1 Varchar(30),
  BILL_STREET_2 Varchar(30),
  BILL_STREET_3 Varchar(30),
  BILL_CITY Varchar(25),
  BILL_STATE_PROVINCE Varchar(20),
  BILL_POSTAL_CODE Varchar(12),
  BILL_COUNTRY Varchar(15),
  BILL_PHONE_NO Varchar(20),
  BILL_FAX_NO Varchar(20),
  BILL_CONTACT_FIRST Varchar(30),
  BILL_CONTACT_SECOND Varchar(30),
  CUST_ACTIVE Char(1),
  USE_MAIN4BILL Char(1),
  REP_CREDIT Char(1),
  INDUSTRY Char(1),
  SHIP_TYPE Char(1),
  SHIP_NUMBER Varchar(25),
  SR_NO Integer,
  CREDIT_LINE Integer,
  CREDIT_APPROVED_DAY Timestamp,
  CONSTRAINT PK_COMPANY PRIMARY KEY (CUST_NO)
);

ALTER TABLE COMPANY ADD CONSTRAINT COMPANY_CATEGORY
  FOREIGN KEY (CATEGORY) REFERENCES CUST_CATEGORY (CC_NO) ON UPDATE CASCADE ON DELETE SET NULL;
CREATE INDEX COMPANY_NM ON COMPANY (COMPANY_NAME);
CREATE UNIQUE INDEX COMPANY_NO ON COMPANY (CUST_NO);
GRANT DELETE, INSERT, REFERENCES, SELECT, UPDATE
 ON COMPANY TO  "PUBLIC";

CREATE TABLE INVOICES
(
  INVOICENO Integer NOT NULL,
  DATE_INV Timestamp,
  ORDERNO Integer,
  SUBORDERNO Integer,
  PRICE_TYPE Varchar(15),
  SALE_AMOUNT Float,
  FREIGHT Float,
  SALES_TAX Float,
  TOTAL Float,
  TRANSFERRED Varchar(1),
  PRIMARY KEY (INVOICENO)
);

CREATE UNIQUE INDEX INVOICES_INVO ON INVOICES (INVOICENO);
GRANT DELETE, INSERT, REFERENCES, SELECT, UPDATE
 ON INVOICES TO  "PUBLIC";

CREATE TABLE INVOICES_PART
(
  INVOICENO Integer NOT NULL,
  PART_NO Varchar(10) NOT NULL,
  PRICE Float,
  AVG_COST Float,
  INCL_LABOR Float,
  CONSTRAINT INVOICES_PART_PRIMARYKEY1 PRIMARY KEY (INVOICENO,PART_NO)
);
GRANT DELETE, INSERT, REFERENCES, SELECT, UPDATE
 ON INVOICES_PART TO  "PUBLIC";
GRANT DELETE, INSERT, REFERENCES, SELECT, UPDATE
 ON INVOICES_PART TO  SYSDBA WITH GRANT OPTION;

CREATE TABLE SUBORDERS_PRODUCT
(
  ORDERNO Integer NOT NULL,
  SUBORDERNO Integer NOT NULL,
  PART_NO Varchar(10) NOT NULL,
  QTY Integer,
  DUE_DATE Timestamp,
  QTY_SHIP Integer,
  SHIP_DATE Timestamp,
  SHIP_VIA Varchar(15),
  SHIP_METHOD Varchar(15),
  PACK_SLIP_NO Varchar(10),
  SHIPPED_DATE Timestamp,
  PRIMARY KEY (ORDERNO,SUBORDERNO,PART_NO)
);
CREATE UNIQUE INDEX SUBORDERS_PRODUCT_OSP ON SUBORDERS_PRODUCT (ORDERNO,SUBORDERNO,PART_NO);
GRANT DELETE, INSERT, REFERENCES, SELECT, UPDATE
 ON SUBORDERS_PRODUCT TO  "PUBLIC";

SET TERM ^ ;
ALTER PROCEDURE MONTHLY_PACK_SLIP (
    DATEFROM Timestamp,
    DATETO Timestamp )
RETURNS (
    PACK_SLIP_NO Varchar(10),
    INVOICENO Integer,
    ORDERNO Integer,
    SUBORDERNO Integer,
    DATE_SHIP Timestamp,
    CUSTNO Integer,
    COMPANY_NAME Varchar(45),
    PURCHASE_ORDER_NO Varchar(20),
    PART_NO Varchar(10),
    TOTAL_QTY_SHIP Integer,
    PRICE Float,
    TOTAL Float )
AS
BEGIN
FOR
 SELECT
  SHIPPING.PACK_SLIP_NO,
  INVOICES.INVOICENO,
  SHIPPING.ORDERNO,
  SHIPPING.SUBORDERNO,
  SHIPPING.DATE_SHIP,
  ORDERS.CUSTNO,
  COMPANY.COMPANY_NAME,
  ORDERS.PURCHASE_ORDER_NO,
  ORDERS_PRODUCT.PART_NO,
  ORDERS_PRODUCT.TOTAL_QTY_SHIP,
  INVOICES_PART.PRICE,
  ORDERS_PRODUCT.TOTAL_QTY_SHIP*INVOICES_PART.PRICE AS TOTAL
 FROM ORDERS join SHIPPING on SHIPPING.ORDERNO=ORDERS.ORDERNO   
  join ORDERS_PRODUCT on SHIPPING.ORDERNO=ORDERS_PRODUCT.ORDER_NO
  join COMPANY on ORDERS.CUSTNO=COMPANY.CUST_NO 
  join INVOICES on SHIPPING.ORDERNO=INVOICES.ORDERNO
  join INVOICES_PART on INVOICES_PART.INVOICENO=INVOICES.INVOICENO 
       and INVOICES_PART.PART_NO=ORDERS_PRODUCT.PART_NO
 WHERE
  SHIPPING.SUBORDERNO IS NULL AND
  SHIPPING.DATE_SHIP>=:DATEFROM AND
  SHIPPING.DATE_SHIP<=:DATETO
UNION
 SELECT
  SHIPPING.PACK_SLIP_NO,
  cast (null as INTEGER) INVOICENO,
  SHIPPING.ORDERNO,
  SHIPPING.SUBORDERNO,
  SHIPPING.DATE_SHIP,
  ORDERS.CUSTNO,
  COMPANY.COMPANY_NAME,
  ORDERS.PURCHASE_ORDER_NO,
  ORDERS_PRODUCT.PART_NO,
  ORDERS_PRODUCT.TOTAL_QTY_SHIP,
  ORDERS_PRODUCT.PRICE,
  ORDERS_PRODUCT.TOTAL_QTY_SHIP*ORDERS_PRODUCT.PRICE AS TOTAL
 FROM ORDERS join SHIPPING on SHIPPING.ORDERNO=ORDERS.ORDERNO   
  join ORDERS_PRODUCT on SHIPPING.ORDERNO=ORDERS_PRODUCT.ORDER_NO
  join COMPANY on ORDERS.CUSTNO=COMPANY.CUST_NO 
 WHERE
  SHIPPING.SUBORDERNO IS NULL AND
  NOT EXISTS(SELECT 1 FROM INVOICES where INVOICES.ORDERNO = SHIPPING.ORDERNO) AND
  SHIPPING.DATE_SHIP>=:DATEFROM AND
  SHIPPING.DATE_SHIP<=:DATETO
UNION
 SELECT 
  SHIPPING.PACK_SLIP_NO,
  INVOICES.INVOICENO,
  SHIPPING.ORDERNO,
  SHIPPING.SUBORDERNO,
  SHIPPING.DATE_SHIP,
  ORDERS.CUSTNO,
  COMPANY.COMPANY_NAME,
  ORDERS.PURCHASE_ORDER_NO,
  ORDERS_PRODUCT.PART_NO,
  SUBORDERS_PRODUCT.QTY_SHIP,
  INVOICES_PART.PRICE,
  SUBORDERS_PRODUCT.QTY_SHIP*INVOICES_PART.PRICE AS TOTAL
 FROM ORDERS JOIN SHIPPING ON SHIPPING.ORDERNO=ORDERS.ORDERNO
 JOIN ORDERS_PRODUCT ON SHIPPING.ORDERNO=ORDERS_PRODUCT.ORDER_NO
 JOIN SUBORDERS_PRODUCT ON SHIPPING.ORDERNO    = SUBORDERS_PRODUCT.ORDERNO AND
                           SHIPPING.SUBORDERNO = SUBORDERS_PRODUCT.SUBORDERNO AND
                        ORDERS_PRODUCT.PART_NO = SUBORDERS_PRODUCT.PART_NO
 JOIN COMPANY ON ORDERS.CUSTNO=COMPANY.CUST_NO 
 JOIN INVOICES ON SHIPPING.ORDERNO    = INVOICES.ORDERNO AND
                  SHIPPING.SUBORDERNO = INVOICES.SUBORDERNO 
 join INVOICES_PART on INVOICES_PART.INVOICENO=INVOICES.INVOICENO 
       and INVOICES_PART.PART_NO=ORDERS_PRODUCT.PART_NO
 WHERE
  SHIPPING.SUBORDERNO IS NOT NULL AND
  SHIPPING.DATE_SHIP>=:DATEFROM AND
  SHIPPING.DATE_SHIP<=:DATETO
UNION
 SELECT 
  SHIPPING.PACK_SLIP_NO,
  cast (null as INTEGER) INVOICENO,
  SHIPPING.ORDERNO,
  SHIPPING.SUBORDERNO,
  SHIPPING.DATE_SHIP,
  ORDERS.CUSTNO,
  COMPANY.COMPANY_NAME,
  ORDERS.PURCHASE_ORDER_NO,
  ORDERS_PRODUCT.PART_NO,
  SUBORDERS_PRODUCT.QTY_SHIP,
  ORDERS_PRODUCT.PRICE,
  SUBORDERS_PRODUCT.QTY_SHIP*ORDERS_PRODUCT.PRICE AS TOTAL
 FROM ORDERS JOIN SHIPPING ON SHIPPING.ORDERNO=ORDERS.ORDERNO
 JOIN ORDERS_PRODUCT ON SHIPPING.ORDERNO=ORDERS_PRODUCT.ORDER_NO
 JOIN SUBORDERS_PRODUCT ON SHIPPING.ORDERNO    = SUBORDERS_PRODUCT.ORDERNO AND
                           SHIPPING.SUBORDERNO = SUBORDERS_PRODUCT.SUBORDERNO AND
                        ORDERS_PRODUCT.PART_NO = SUBORDERS_PRODUCT.PART_NO
 JOIN COMPANY ON ORDERS.CUSTNO=COMPANY.CUST_NO 
 WHERE
  SHIPPING.SUBORDERNO IS NOT NULL AND
  NOT EXISTS(SELECT 1 FROM INVOICES WHERE INVOICES.ORDERNO=SHIPPING.ORDERNO AND INVOICES.SUBORDERNO = SHIPPING.SUBORDERNO) AND
  SHIPPING.DATE_SHIP>=:DATEFROM AND
  SHIPPING.DATE_SHIP<=:DATETO
 INTO :PACK_SLIP_NO, :INVOICENO, :ORDERNO, :SUBORDERNO, :DATE_SHIP , :CUSTNO,
      :COMPANY_NAME, :PURCHASE_ORDER_NO, :PART_NO, :TOTAL_QTY_SHIP,
      :PRICE, :TOTAL
DO SUSPEND;
END^
SET TERM ; ^


GRANT EXECUTE
 ON PROCEDURE MONTHLY_PACK_SLIP TO  SYSDBA;