/*order 502361*/
      /*
      ACTION is CREATE Table ACTIVITYPERINVOICE
      */

CREATE TABLE ACTIVITYPERINVOICE (
       ACTIVITY_ID          NUMBER NOT NULL,
       INVOICEHEADER_ID     NUMBER NOT NULL,
       SYSTEMUSER_ID        NUMBER NOT NULL,
       TIMESTAMP            DATE NOT NULL
);

CREATE INDEX XIF1ACTIVITYPERINVOICE ON ACTIVITYPERINVOICE
(
       ACTIVITY_ID                    ASC
);

CREATE INDEX XIF2ACTIVITYPERINVOICE ON ACTIVITYPERINVOICE
(
       INVOICEHEADER_ID               ASC
);


ALTER TABLE ACTIVITYPERINVOICE
       ADD  ( CONSTRAINT XPKACTIVITYPERINVOICE PRIMARY KEY (
              ACTIVITY_ID, INVOICEHEADER_ID) ) ;

ALTER TABLE ACTIVITYPERINVOICE
       ADD  ( CONSTRAINT ACTIVITYPERINVOICE_INVOICEHEAD
              FOREIGN KEY (INVOICEHEADER_ID)
                             REFERENCES INVOICEHEADER
                             ON DELETE CASCADE ) ;


ALTER TABLE ACTIVITYPERINVOICE
       ADD  ( CONSTRAINT ACTIVITYPERINVOICE_ACTIVITY_ID
              FOREIGN KEY (ACTIVITY_ID)
                             REFERENCES ACTIVITY ) ;


-----------------------------------------------PACKINGNOTELINE
ALTER TABLE PACKINGNOTELINE ADD (INVOICEHEADER_ID     NUMBER NULL);

UPDATE PACKINGNOTELINE
 SET INVOICEHEADER_ID =
  (SELECT PN.INVOICEHEADER_ID
    FROM PACKINGNOTE PN
   WHERE PACKINGNOTELINE.PACKINGNOTE_ID = PN.PACKINGNOTE_ID);

CREATE INDEX XIF8PACKINGNOTELINE ON PACKINGNOTELINE
(
       INVOICEHEADER_ID               ASC
);

-----------------------------------------------PACKINGNOTECONTAINER

ALTER TABLE PACKINGNOTECONTAINER ADD (INVOICEHEADER_ID     NUMBER NULL);

UPDATE PACKINGNOTECONTAINER
  SET INVOICEHEADER_ID =
  (SELECT PN.INVOICEHEADER_ID
    FROM PACKINGNOTE PN
   WHERE PACKINGNOTECONTAINER.PACKINGNOTE_ID = PN.PACKINGNOTE_ID);

CREATE INDEX XIF5PACKINGNOTECONTAINER ON PACKINGNOTECONTAINER
(
       INVOICEHEADER_ID               ASC
);

---------------------------------------------------------PACKINGNOTE
ALTER TABLE PACKINGNOTE ADD (INVOICESTATUS     NUMBER DEFAULT 0 NOT NULL);

COMMENT ON COLUMN PACKINGNOTE.INVOICESTATUS IS '0 = Completely not invoiced
1 = Partially invoiced
2 = Completely invoiced';

UPDATE PACKINGNOTE
  SET INVOICESTATUS = 0
WHERE PACKINGNOTE.INVOICEHEADER_ID IS NULL;
       
UPDATE PACKINGNOTE
  SET INVOICESTATUS = 2
WHERE PACKINGNOTE.INVOICEHEADER_ID IS NOT NULL;

DROP INDEX XIF976PACKINGNOTE;

ALTER TABLE PACKINGNOTE RENAME COLUMN INVOICEHEADER_ID TO OLDINVOICEHEADER_ID;

-------------------------------------------------------WEIGHINGTICKETLINE
ALTER TABLE WEIGHINGTICKETLINE ADD (INVOICEHEADER_ID     NUMBER NULL);

CREATE INDEX XIF7WEIGHINGTICKETLINE ON WEIGHINGTICKETLINE
(
       INVOICEHEADER_ID               ASC
);

UPDATE WEIGHINGTICKETLINE
  SET INVOICEHEADER_ID =
  (SELECT WT.INVOICEHEADER_ID
    FROM WEIGHINGTICKET WT
   WHERE WT.INVOICEHEADER_ID IS NOT NULL
     AND WEIGHINGTICKETLINE.WEIGHINGTICKET_ID = WT.WEIGHINGTICKET_ID);


-------------------------------------------------------WEIGHINGTICKET
DROP INDEX XIF977WEIGHINGTICKET;

ALTER TABLE WEIGHINGTICKET RENAME COLUMN INVOICEHEADER_ID TO OLDINVOICEHEADER_ID;
---------------------------------------------------------------------

--fill activityperinvoice
insert into activityperinvoice
  select distinct ca.activity_id, ih.invoiceheader_id, getminsystemuser(), sysdate
    from customeractivities ca, invoiceheader ih
   where ca.customer_id = ih.deltocustomer_link_id
     and ih.status_id = (select s.status_id
                           from status s
                          where s.code = 1
                            and s.statustype = 'IN');
