-- ABS-11726/ ABS-17918

---STOCKCOUNTSCHEDULE_LINK table---

CREATE SEQUENCE SEQ_STOCKCOUNTSCHEDULE_LINK
START WITH 1
INCREMENT BY 1;

CREATE TABLE STOCKCOUNTSCHEDULE_LINK
(
  STOCKCOUNTSCHEDULE_LINK_ID NUMBER NOT NULL,
  STOCKCOUNTSCHEDULE_ID      NUMBER NOT NULL,
  CODE                       VARCHAR2(6) NOT NULL,
  DESCRIPTION                VARCHAR2(30) NOT NULL,
  CUSTOMER_LINK_ID           NUMBER NOT NULL,
  SYSTEMUSER_ID              NUMBER NOT NULL,
  TIMESTAMP                  DATE NOT NULL
);

ALTER TABLE STOCKCOUNTSCHEDULE_LINK
  ADD CONSTRAINT XPKSTOCKCOUNTSCHEDULE_LINK PRIMARY KEY (STOCKCOUNTSCHEDULE_LINK_ID);

CREATE INDEX XIE1STOCKCOUNTSCHEDULE_LINK ON STOCKCOUNTSCHEDULE_LINK (CUSTOMER_LINK_ID ASC, CODE ASC);

CREATE INDEX XIF1STOCKCOUNTSCHEDULE_LINK ON STOCKCOUNTSCHEDULE_LINK
(STOCKCOUNTSCHEDULE_ID   ASC);

CREATE INDEX XIF2STOCKCOUNTSCHEDULE_LINK ON STOCKCOUNTSCHEDULE_LINK
(CUSTOMER_LINK_ID   ASC);

ALTER TABLE STOCKCOUNTSCHEDULE_LINK
  ADD CONSTRAINT STOCKCOUNTSCHEDULE_LINK_ST FOREIGN KEY (STOCKCOUNTSCHEDULE_ID)
  REFERENCES STOCKCOUNTSCHEDULE (STOCKCOUNTSCHEDULE_ID);
  
ALTER TABLE STOCKCOUNTSCHEDULE_LINK
  ADD CONSTRAINT STOCKCOUNTSCHEDULE_LINK_CU FOREIGN KEY (CUSTOMER_LINK_ID)
  REFERENCES CUSTOMER_LINK (CUSTOMER_LINK_ID);

insert into stockcountschedule_link scsl
  select scs.stockcountschedule_id,
         scs.stockcountschedule_id,
         scs.code,
         scs.description,
         scs.customer_id,
         getminsystemuser(),
         sysdate
    from stockcountschedule scs
   where scs.stockcountschedule_id not in
         (select scsl1.stockcountschedule_id
            from stockcountschedule_link scsl1
           where scs.stockcountschedule_id = scsl1.stockcountschedule_link_id);

commit;

---STOCKCOUNTSCHEDULEDIST table---
CREATE SEQUENCE SEQ_STOCKCOUNTSCHEDULEDIST
START WITH 1
INCREMENT BY 1;

CREATE TABLE STOCKCOUNTSCHEDULEDIST
(
  STOCKCOUNTSCHEDULEDIST_ID  NUMBER NOT NULL,
  STOCKCOUNTSCHEDULE_ID      NUMBER NOT NULL,
  DISTRIBUTIONPOINT_ID       NUMBER NOT NULL,
  COUNTSEQUENCE              NUMBER NOT NULL,
  SYSTEMUSER_ID              NUMBER NOT NULL,
  TIMESTAMP                  DATE NOT NULL
);

COMMENT ON COLUMN STOCKCOUNTSCHEDULEDIST.COUNTSEQUENCE IS 
'Sequence number the distribution point is intended to be processed within schedule';

ALTER TABLE STOCKCOUNTSCHEDULEDIST
  ADD CONSTRAINT XPKSTOCKCOUNTSCHEDULEDIST PRIMARY KEY (STOCKCOUNTSCHEDULEDIST_ID);

CREATE UNIQUE INDEX XAK1STOCKCOUNTSCHEDULEDIST ON STOCKCOUNTSCHEDULEDIST (STOCKCOUNTSCHEDULE_ID ASC, COUNTSEQUENCE ASC);

CREATE UNIQUE INDEX XAK2STOCKCOUNTSCHEDULEDIST ON STOCKCOUNTSCHEDULEDIST (STOCKCOUNTSCHEDULE_ID ASC, DISTRIBUTIONPOINT_ID ASC);

CREATE INDEX XIF1STOCKCOUNTSCHEDULEDIST ON STOCKCOUNTSCHEDULEDIST
(STOCKCOUNTSCHEDULE_ID   ASC);

CREATE INDEX XIF2STOCKCOUNTSCHEDULEDIST ON STOCKCOUNTSCHEDULEDIST
(DISTRIBUTIONPOINT_ID   ASC);

ALTER TABLE STOCKCOUNTSCHEDULEDIST
  ADD CONSTRAINT STOCKCOUNTSCHEDULEDIST_ST FOREIGN KEY (STOCKCOUNTSCHEDULE_ID)
  REFERENCES STOCKCOUNTSCHEDULE (STOCKCOUNTSCHEDULE_ID) ON DELETE CASCADE;
  
ALTER TABLE STOCKCOUNTSCHEDULEDIST
  ADD CONSTRAINT STOCKCOUNTSCHEDULEDIST_DP FOREIGN KEY (DISTRIBUTIONPOINT_ID)
  REFERENCES DISTRIBUTIONPOINT (DISTRIBUTIONPOINT_ID) ON DELETE CASCADE;


--insert into STOCKCOUNTSCHEDULEDIST table
create or replace procedure insertintoscsdist is
  lv_errormessage     varchar2(500);
  cursor get_scdept is
    select x.*, rownum new_countsequence
    from
    (select scsd.stockcountscheduledept_id, scsd.department_id, scsd.stockcountschedule_id, scsd.countsequence,
           dp.distributionpoint_id, dp.distributionpointnumber, d.departmentnumber, scs.code
      from stockcountscheduledept scsd, distributionpoint dp, department d, stockcountschedule scs
     where dp.department_id = scsd.department_id
       and d.department_id = dp.department_id
       and scs.stockcountschedule_id = scsd.stockcountschedule_id
     order by scsd.stockcountschedule_id, scsd.countsequence, dp.distributionpointnumber) x;
begin
  for scd_rec in get_scdept loop
    begin
      insert into stockcountscheduledist
             (stockcountscheduledist_id, stockcountschedule_id, distributionpoint_id, countsequence,systemuser_id, timestamp)
           values
             (seq_stockcountscheduledist.nextval, scd_rec.stockcountschedule_id, scd_rec.distributionpoint_id,
              scd_rec.new_countsequence, 0, sysdate);
      exception
        when others then
          null;
          lv_errormessage := 'Insert failed for stock count schedule '||scd_rec.code;
          lv_errormessage := lv_errormessage ||', department '||scd_rec.departmentnumber;
          lv_errormessage := lv_errormessage ||', cons. point '||scd_rec.distributionpointnumber;
          lv_errormessage := lv_errormessage ||' due to ';
          dbms_output.put_line(lv_errormessage || sqlerrm);
      end;
  end loop;
  commit;
end insertintoscsdist;
/

execute insertintoscsdist;

drop procedure insertintoscsdist;


--ALLOCATIONTRANSACTION table--

CREATE SEQUENCE SEQ_ALLOCATIONTRANSACTION
START WITH 1
INCREMENT BY 1;

CREATE TABLE ALLOCATIONTRANSACTION
(
  ALLOCATIONTRANSACTION_ID      NUMBER NOT NULL,
  ALLOCATIONDATE                DATE NOT NULL,
  TODISTRIBUTIONPOINT_LINK_ID   NUMBER NOT NULL,
  PRODUCT_LINK_ID               NUMBER NOT NULL,
  SIZEDEFINITION_LINK_ID        NUMBER NOT NULL,
  FINISHINGMETHOD_ID            NUMBER NOT NULL,
  STOCKCOUNTSCHEDULE_LINK_ID    NUMBER NULL,
  FROMDISTRIBUTIONPOINT_LINK_ID NUMBER NOT NULL,
  TRANSACTIONTYPE               NUMBER DEFAULT 1 NOT NULL CONSTRAINT TRANSACTIONTYPE_VALID1_2
  				CHECK (TRANSACTIONTYPE IN (1, 2, 3)),
  PARLEVEL                      NUMBER NULL,
  ONHAND                        NUMBER NULL,
  STOCKED                       NUMBER NULL,
  EMERGENCY                     NUMBER NULL,
  RETURNED                      NUMBER NULL,
  ORIGIN                        NUMBER DEFAULT 1 NOT NULL CONSTRAINT ORIGIN_VALID1_1
  				CHECK (ORIGIN IN (1, 2)),
  WEBUSER_ID                    NUMBER NULL,
  UPLOADTIMESTAMP               DATE NULL,
  SYSTEMUSER_ID                 NUMBER NOT NULL,
  TIMESTAMP                     DATE NOT NULL
)
;
 
COMMENT ON COLUMN ALLOCATIONTRANSACTION.TRANSACTIONTYPE
  IS '1 = Allocation
2 = Emergency
3 = Return
';
COMMENT ON COLUMN ALLOCATIONTRANSACTION.PARLEVEL
  IS 'May only be filled when TransactionType = 1';
COMMENT ON COLUMN ALLOCATIONTRANSACTION.ONHAND
  IS 'May only be filled when TransactionType = 1';
COMMENT ON COLUMN ALLOCATIONTRANSACTION.STOCKED
  IS 'May only be filled when TransactionType = 1';
COMMENT ON COLUMN ALLOCATIONTRANSACTION.EMERGENCY
  IS 'May only be filled when TransactionType = 2';
COMMENT ON COLUMN ALLOCATIONTRANSACTION.RETURNED
  IS 'May only be filled when TransactionType = 3';
COMMENT ON COLUMN ALLOCATIONTRANSACTION.ORIGIN
  IS '1 = Web portal
2 = PDA
';

ALTER TABLE ALLOCATIONTRANSACTION
  ADD CONSTRAINT XPKALLOCATIONTRANSACTION PRIMARY KEY (ALLOCATIONTRANSACTION_ID);

CREATE UNIQUE INDEX XAK1ALLOCATIONTRANSACTION ON ALLOCATIONTRANSACTION (ALLOCATIONDATE ASC, TODISTRIBUTIONPOINT_LINK_ID ASC, PRODUCT_LINK_ID ASC, SIZEDEFINITION_LINK_ID ASC, FINISHINGMETHOD_ID ASC, FROMDISTRIBUTIONPOINT_LINK_ID ASC, STOCKCOUNTSCHEDULE_LINK_ID ASC, TRANSACTIONTYPE ASC);

CREATE INDEX XIF1ALLOCATIONTRANSACTION ON ALLOCATIONTRANSACTION (TODISTRIBUTIONPOINT_LINK_ID ASC);
CREATE INDEX XIF2ALLOCATIONTRANSACTION ON ALLOCATIONTRANSACTION (PRODUCT_LINK_ID ASC);
CREATE INDEX XIF3ALLOCATIONTRANSACTION ON ALLOCATIONTRANSACTION (SIZEDEFINITION_LINK_ID ASC);
CREATE INDEX XIF4ALLOCATIONTRANSACTION ON ALLOCATIONTRANSACTION (FINISHINGMETHOD_ID ASC);
CREATE INDEX XIF5ALLOCATIONTRANSACTION ON ALLOCATIONTRANSACTION (STOCKCOUNTSCHEDULE_LINK_ID ASC);
CREATE INDEX XIF6ALLOCATIONTRANSACTION ON ALLOCATIONTRANSACTION (FROMDISTRIBUTIONPOINT_LINK_ID ASC);

ALTER TABLE ALLOCATIONTRANSACTION
  ADD CONSTRAINT ALLOCATIONTRANSACTION_TODP FOREIGN KEY (TODISTRIBUTIONPOINT_LINK_ID)
  REFERENCES DISTRIBUTIONPOINT_LINK (DISTRIBUTIONPOINT_LINK_ID);
ALTER TABLE ALLOCATIONTRANSACTION
  ADD CONSTRAINT ALLOCATIONTRANSACTION_PROD_L FOREIGN KEY (PRODUCT_LINK_ID)
  REFERENCES PRODUCT_LINK (PRODUCT_LINK_ID);
ALTER TABLE ALLOCATIONTRANSACTION
  ADD CONSTRAINT ALLOCATIONTRANSACTION_SIZE_L FOREIGN KEY (SIZEDEFINITION_LINK_ID)
  REFERENCES SIZEDEFINITION_LINK (SIZEDEFINITION_LINK_ID);
ALTER TABLE ALLOCATIONTRANSACTION
  ADD CONSTRAINT ALLOCATIONTRANSACTION_FM FOREIGN KEY (FINISHINGMETHOD_ID)
  REFERENCES FINISHINGMETHOD (FINISHINGMETHOD_ID);
ALTER TABLE ALLOCATIONTRANSACTION
  ADD CONSTRAINT ALLOCATIONTRANSACTION_SCS_L FOREIGN KEY (STOCKCOUNTSCHEDULE_LINK_ID)
  REFERENCES STOCKCOUNTSCHEDULE_LINK (STOCKCOUNTSCHEDULE_LINK_ID);
ALTER TABLE ALLOCATIONTRANSACTION
  ADD CONSTRAINT ALLOCATIONTRANSACTION_FROMDP FOREIGN KEY (FROMDISTRIBUTIONPOINT_LINK_ID)
  REFERENCES DISTRIBUTIONPOINT_LINK (DISTRIBUTIONPOINT_LINK_ID);


ALTER TABLE ALLOCATIONTRANSACTION
  ADD CONSTRAINT ALLOCATIONTRANSACTION_VALID1
  CHECK ((PARLEVEL IS NOT NULL AND TRANSACTIONTYPE = 1) OR (PARLEVEL IS NULL AND TRANSACTIONTYPE != 1));
ALTER TABLE ALLOCATIONTRANSACTION
  ADD CONSTRAINT ALLOCATIONTRANSACTION_VALID2
  CHECK (ONHAND IS NULL AND TRANSACTIONTYPE != 1);
ALTER TABLE ALLOCATIONTRANSACTION
  ADD CONSTRAINT ALLOCATIONTRANSACTION_VALID3
  CHECK ((STOCKED IS NOT NULL AND TRANSACTIONTYPE = 1) OR (STOCKED IS NULL AND TRANSACTIONTYPE != 1));
ALTER TABLE ALLOCATIONTRANSACTION
  ADD CONSTRAINT ALLOCATIONTRANSACTION_VALID4
  CHECK ((EMERGENCY IS NOT NULL AND TRANSACTIONTYPE = 2) OR (EMERGENCY IS NULL AND TRANSACTIONTYPE != 2));
ALTER TABLE ALLOCATIONTRANSACTION
  ADD CONSTRAINT ALLOCATIONTRANSACTION_VALID5
  CHECK ((RETURNED IS NOT NULL AND TRANSACTIONTYPE = 3) OR (RETURNED IS NULL AND TRANSACTIONTYPE != 3));

--ALLOCATIONSTATUS table--

CREATE SEQUENCE SEQ_ALLOCATIONSTATUS
START WITH 1
INCREMENT BY 1;

CREATE TABLE ALLOCATIONSTATUS
(
  ALLOCATIONSTATUS_ID        NUMBER NOT NULL,
  ALLOCATIONDATE             DATE NOT NULL,
  DISTRIBUTIONPOINT_LINK_ID  NUMBER NOT NULL,
  STOCKCOUNTSCHEDULE_LINK_ID NUMBER NOT NULL,
  STATUS                     NUMBER DEFAULT 1 NOT NULL CONSTRAINT STATUS_VALID7_1
  				CHECK (STATUS IN (1, 2)),
  ORIGIN                     NUMBER DEFAULT 1 NOT NULL CONSTRAINT ORIGIN_VALID6_1
  				CHECK (ORIGIN IN (1, 2)),
  WEBUSER_ID                 NUMBER NULL,
  UPLOADTIMESTAMP            DATE NULL,
  SYSTEMUSER_ID              NUMBER NOT NULL,
  TIMESTAMP                  DATE NOT NULL
)
;

COMMENT ON COLUMN ALLOCATIONSTATUS.STATUS
  IS '1 = In progress
2 = Done -
In case no records exists yet in database, in the web portal a virtual status ‘Not Started’ will be shown';
COMMENT ON COLUMN ALLOCATIONSTATUS.ORIGIN
  IS '1 = Web portal
2 = PDA
';

ALTER TABLE ALLOCATIONSTATUS
  ADD CONSTRAINT XPKALLOCATIONSTATUS PRIMARY KEY (ALLOCATIONSTATUS_ID);

CREATE UNIQUE INDEX XAK1ALLOCATIONSTATUS ON ALLOCATIONSTATUS (ALLOCATIONDATE ASC, DISTRIBUTIONPOINT_LINK_ID ASC, STOCKCOUNTSCHEDULE_LINK_ID ASC);

CREATE INDEX XIF1ALLOCATIONSTATUS ON ALLOCATIONSTATUS (DISTRIBUTIONPOINT_LINK_ID ASC);
CREATE INDEX XIF2ALLOCATIONSTATUS ON ALLOCATIONSTATUS (STOCKCOUNTSCHEDULE_LINK_ID ASC);

ALTER TABLE ALLOCATIONSTATUS
  ADD CONSTRAINT ALLOCATIONSTATUS_DP FOREIGN KEY (DISTRIBUTIONPOINT_LINK_ID)
  REFERENCES DISTRIBUTIONPOINT_LINK (DISTRIBUTIONPOINT_LINK_ID);
ALTER TABLE ALLOCATIONSTATUS
  ADD CONSTRAINT ALLOCATIONSTATUS_SCS_L FOREIGN KEY (STOCKCOUNTSCHEDULE_LINK_ID)
  REFERENCES STOCKCOUNTSCHEDULE_LINK (STOCKCOUNTSCHEDULE_LINK_ID);