DECLARE
   QUEUE_TABLE_EXISTS EXCEPTION;
   PRAGMA EXCEPTION_INIT(QUEUE_TABLE_EXISTS, -24001);
BEGIN
  BEGIN
    DBMS_AQADM.CREATE_QUEUE_TABLE(
      QUEUE_TABLE        => '"&1".RECEIVE_FROM_SOLACE_QUEUE_TABLE',
      SORT_LIST          => 'PRIORITY,ENQ_TIME', 
      QUEUE_PAYLOAD_TYPE => 'SYS.AQ$_JMS_TEXT_MESSAGE');

    DBMS_AQADM.CREATE_QUEUE(
      QUEUE_NAME         => '"&1".RECEIVE_FROM_SOLACE_QUEUE',
      QUEUE_TABLE        => '"&1".RECEIVE_FROM_SOLACE_QUEUE_TABLE');

    DBMS_AQADM.START_QUEUE(
      QUEUE_NAME         => '"&1".RECEIVE_FROM_SOLACE_QUEUE');
  EXCEPTION
    WHEN QUEUE_TABLE_EXISTS THEN
      NULL;
  END;

  BEGIN
    DBMS_AQADM.CREATE_QUEUE_TABLE(
      QUEUE_TABLE        => '"&1".SEND_TO_SOLACE_QUEUE_TABLE',
      QUEUE_PAYLOAD_TYPE => 'SYS.AQ$_JMS_TEXT_MESSAGE');

    DBMS_AQADM.CREATE_QUEUE(
      QUEUE_NAME         => '"&1".SEND_TO_SOLACE_QUEUE',
      QUEUE_TABLE        => '"&1".SEND_TO_SOLACE_QUEUE_TABLE');

    DBMS_AQADM.START_QUEUE(
      QUEUE_NAME         => '"&1".SEND_TO_SOLACE_QUEUE');
  EXCEPTION
    WHEN QUEUE_TABLE_EXISTS THEN
      NULL;
  END;
END;
/
-------------
ALTER SESSION SET CURRENT_SCHEMA = "&1";
DECLARE
   TABLE_EXISTS EXCEPTION;
   PRAGMA EXCEPTION_INIT(TABLE_EXISTS, -00955);
   STMT VARCHAR2(5000);

BEGIN
  BEGIN
    STMT := 'CREATE TABLE MESSAGE_FROM_SOLACE_ERROR
            (
              MESSAGE   CLOB,
              TOPIC     VARCHAR2(100),
              ERROR     VARCHAR2(1000),
              TIMESTAMP DATE
            )';
    EXECUTE IMMEDIATE STMT;
  EXCEPTION
    WHEN TABLE_EXISTS THEN
      NULL;
  END;

  BEGIN
    STMT := 'CREATE TABLE MESSAGE_FROM_SOLACE_NOT_PROCESSED
            (
              MESSAGE   CLOB NOT NULL,
              TOPIC     VARCHAR2(100) NOT NULL,
              TIMESTAMP DATE NOT NULL
            )';
    EXECUTE IMMEDIATE STMT;
  EXCEPTION
    WHEN TABLE_EXISTS THEN
      NULL;
  END;

  BEGIN
    STMT := 'CREATE TABLE MESSAGE_FROM_SOLACE_PROCESSED
              (
                MESSAGE   CLOB NOT NULL,
                TOPIC     VARCHAR2(100) NOT NULL,
                TIMESTAMP DATE NOT NULL
              )';
    EXECUTE IMMEDIATE STMT;
  EXCEPTION
    WHEN TABLE_EXISTS THEN
      NULL;
  END;


  BEGIN
    STMT := 'CREATE TABLE SOLACE_TOPIC
              (
                SOLACE_TOPIC_IN   VARCHAR2(100) NOT NULL,
                PRIORITY          NUMBER NOT NULL,
                PROCESSING_NUMBER NUMBER DEFAULT 0 NOT NULL,
                SOLACE_TOPIC_OUT  VARCHAR2(100),
                CONSTRAINT PROCESSING_ORDER UNIQUE (PROCESSING_NUMBER)
              )';
    EXECUTE IMMEDIATE STMT;
  EXCEPTION
    WHEN TABLE_EXISTS THEN
      NULL;
  END;
  
  BEGIN
    STMT := 'ALTER TABLE SOLACE_TOPIC MODIFY SOLACE_TOPIC_IN NULL';
    EXECUTE IMMEDIATE STMT;    
  EXCEPTION
    WHEN OTHERS THEN
      NULL; --its already null ignore error
  END;
  
  BEGIN
    STMT := 'CREATE TABLE MESSAGE_TO_SOLACE_PROCESSED
(MESSAGE   CLOB NOT NULL,
 TOPIC     VARCHAR2(100) NOT NULL,
 TIMESTAMP DATE NOT NULL)';
    EXECUTE IMMEDIATE STMT;    
  EXCEPTION
    WHEN TABLE_EXISTS THEN
      NULL;    
  END;
  
END;
/
ALTER SESSION SET CURRENT_SCHEMA = "&3";
DECLARE
  STMT CLOB := Q'{
  ALTER TABLE ERP_I_CUSTOMER ADD CRMCUSTOMERNUMBER VARCHAR2(18);
  ALTER TABLE ERP_I_CUSTOMER ADD SEND_RESPONSE VARCHAR2(1);
  DROP INDEX XAK3CUSTOMER;
  CREATE INDEX XIE6CUSTOMER ON CUSTOMER (CRMCUSTOMERNUMBER);

  CREATE OR REPLACE TRIGGER CSOL_AFTER_INSERT_ERP_I_CUST_LOG
  -- Version : 0.1
    AFTER INSERT ON ERP_I_CUSTOMER_LOG
    FOR EACH ROW
  DECLARE
  BEGIN
      "&1".PACK_CONNSOLACE.MESSAGE_ENQUEUE_TO_SOLACE(:NEW.TRANSFER_ID);
  END CSOL_AFTER_INSERT_ERP_I_CUST_LOG;

  CREATE OR REPLACE TRIGGER CSOL_BEFORE_INSERT_CUSTOMER
  -- Version : 0.1
    BEFORE INSERT ON CUSTOMER
    FOR EACH ROW
  DECLARE
  L_CUSTNR_FOUND NUMBER;

  BEGIN
    L_CUSTNR_FOUND := 0;
    SELECT 1 INTO L_CUSTNR_FOUND FROM ERP_I_CUSTOMER ERP WHERE ERP.CUSTOMERNUMBER = :NEW.CUSTOMERNUMBER;
    EXCEPTION
      WHEN no_data_found THEN
       RAISE_APPLICATION_ERROR(-20031,'Customer can''t be created from scratch in ABS. E-Force must deliver the customer to ABS');
  END CSOL_BEFORE_INSERT_CUSTOMER;
}';

BEGIN
  PACK_SCRIPT.EXECSCRIPTFILE(STMT);
END;
/

DECLARE
  STMT CLOB := Q'{create or replace procedure connsolace_containerscan(rec_containerscan containerscan%rowtype) is
  le_user_exception exception;
  lv_message        varchar2(4000);
  l_country         varchar2(3);
  l_plant           varchar2(6);
  l_processnumber   number;
  message_text      clob;
  --procedure connsolace_containerscan version 0.2

  procedure containerpackedscan is
  begin
    --Process 6 - Container Packed Scan
    lv_message := 'Process 6 - Container Packed Scan';
    --get scan info to send to Solace queue
    select ct.code countrycode, pt.code plantcode,
           json_object('cageCode' value rec_containerscan.primaryid,
                        'scan' value
                         json_object('logisticCenterId' value
                                     ct.code || '-' || pt.code || '-' || bu.code,
                                     'workstationSource' value rec_containerscan.scanstation,
                                     'scanTimestamp' value rec_containerscan.scantimestamp,
                                     'devicetype' value 'PROD'),
                        'customer' value
                         json_object('customerId' value ct.code || '-' || to_char(c.customer_id),
                                     'elisCustomerId' value nvl(c.crmcustomernumber, c.customernumber),
                                     'pointOfDeliveryId' value
                                      ct.code || '-' || to_char(t.department_id),
                                     'PointOfServiceId' value
                                      ct.code || '-' || to_char(dp.distributionpoint_id)),
                        'packing' value
                         json_object('deliveryDate' value pk.deliverydate,
                                     'routeId' value r.routenumber, 'deliverynoteId' value pk.notenumber,
                                     'mainPackingLabelId' value null,
                                     'packingLabelsIds' value '[]') returning clob) message_text
    into   l_country, l_plant, message_text
    from   businessunit bu
    join   plant pt
    on     pt.plant_id = bu.plant_id
    join   country ct
    on     ct.country_id = pt.country_id
    left   join (select pnc.container_id, max(pnc.packingnote_id) packingnote_id
                 from   packingnotecontainer pnc
                 group  by pnc.container_id) px
    on     px.container_id = rec_containerscan.container_id
    left   join packingnote pk
    on     pk.packingnote_id = px.packingnote_id
    left   join(distributionpoint dp
    join   department t
    on     t.department_id = dp.department_id
    join   customer c
    on     c.customer_id = t.customer_id) on dp.distributionpoint_id = rec_containerscan.distributionpoint_link_id
    left   join route r
    on     r.route_id = pk.route_id
    where  rec_containerscan.transactiontype_id in (540, 541, 542, 543, 545)
    and    bu.businessunit_id = rec_containerscan.businessunit_id;
  end containerpackedscan;

  procedure containerreturnedscan is
  begin
    --Process 7 - Container Returned Scan
    lv_message := 'Process 7 - Container Returned Scan';
    --get scan info to send to Solace queue
    select ct.code countrycode, pt.code plantcode,
           json_object('cageCode' value rec_containerscan.primaryid,
                        'scan' value
                         json_object('logisticCenterId' value
                                     ct.code || '-' || pt.code || '-' || bu.code,
                                     'workstationSource' value rec_containerscan.scanstation,
                                     'scanTimestamp' value rec_containerscan.scantimestamp,
                                     'devicetype' value 'PROD'),
                        'customer' value
                         json_object('customerId' value ct.code || '-' || to_char(c.customer_id),
                                     'elisCustomerId' value nvl(c.crmcustomernumber, c.customernumber),
                                     'pointOfDeliveryId' value
                                      ct.code || '-' || to_char(t.department_id),
                                     'PointOfServiceId' value
                                      ct.code || '-' || to_char(dp.distributionpoint_id)),
                        'packing' value
                         json_object('deliveryDate' value pk.deliverydate,
                                     'routeId' value null, 'deliverynoteId' value null,
                                     'mainPackingLabelId' value null,
                                     'packingLabelsIds' value '[]') returning clob) message_text
    into   l_country, l_plant, message_text
    from   businessunit bu
    join   plant pt
    on     pt.plant_id = bu.plant_id
    join   country ct
    on     ct.country_id = pt.country_id
    left   join (select pnc.container_id, max(pnc.packingnote_id) packingnote_id
                 from   packingnotecontainer pnc
                 group  by pnc.container_id) px --previous del.note so previous customer
    on     px.container_id = rec_containerscan.container_id
    left   join packingnote pk
    on     pk.packingnote_id = px.packingnote_id
    left   join(distributionpoint dp
    join   department t
    on     t.department_id = dp.department_id
    join   customer c
    on     c.customer_id = t.customer_id) on dp.distributionpoint_id = pk.distributionpoint_id
    where  rec_containerscan.transactiontype_id = 511
    and    bu.businessunit_id = rec_containerscan.businessunit_id;
  end containerreturnedscan;

  procedure createcontainer is
  begin
    --Process 8 - Create Container
    lv_message := 'Process 8 - Create Container';
    select ct.code countrycode, pt.code plantcode,
           json_object('cage' value
                        json_object('code' value rec_containerscan.primaryid,
                                    'epc' value rec_containerscan.secondaryid,
                                    'weight' value c.tareweight, 'weightunit' value 'Kg',
                                    'isrealweight' value 0,
                                    'comissionDate' value
                                     to_char(rec_containerscan.scantimestamp, 'YYYY-MM-DD'),
                                    'status' value 'Active', 'statusUpdateAt' value sysdate,
                                    'genericCategoryCode' value null,
                                    'lmsCategory' value
                                     json_object('lmsCategoryCode' value ctt.code,
                                                 'lmsProductCode' value null,
                                                 'lmsSystemCode' value '',
                                                 'lmsCountryCode' value ct.code),
                                    'plantCreatedIn' value
                                     json_object('elisPlantId' value null,
                                                 'lmsCode' value bu.code, 'CountryCode' value ct.code)),
                        'statusChanged' value
                         json_object('newStatus' value 'active', 'transition' value 'created',
                                     'comment' value null,
                                     'updatedAt' value rec_containerscan.scantimestamp) returning clob) message_text
    into   l_country, l_plant, message_text
    from   businessunit bu
    join   plant pt
    on     pt.plant_id = bu.plant_id
    join   country ct
    on     ct.country_id = pt.country_id
    join   container c
    on     c.container_id = rec_containerscan.container_id
    join   containertype ctt
    on     ctt.containertype_id = c.containertype_id
    where  rec_containerscan.transactiontype_id = 550
    and    bu.businessunit_id = rec_containerscan.businessunit_id;
  end createcontainer;

  procedure updatecontainer is
  begin
    --Process 9 - Update Container
    lv_message := 'Process 9 - Update Container';
    select ct.code countrycode, pt.code plantcode,
           json_object('cage' value
                       json_object('code' value rec_containerscan.primaryid,
                                   'epc' value rec_containerscan.secondaryid,
                                   'weight' value c.tareweight, 'weightunit' value 'Kg',
                                   'isrealweight' value case
                                      when rec_containerscan.scantimestamp between c.dateactive and c.dateinactive - 1 then
                                       1 -- not inactivated so weight change
                                      else
                                       0
                                    end,
                                   'comissionDate' value
                                    to_char(rec_containerscan.scantimestamp, 'YYYY-MM-DD'),
                                   'status' value case
                                      when rec_containerscan.scantimestamp between c.dateactive and c.dateinactive - 1 then
                                       'Active'
                                      else
                                       'inactive'
                                    end, 'statusUpdateAt' value sysdate,
                                   'genericCategoryCode' value null,
                                   'lmsCategory' value
                                    json_object('lmsCategoryCode' value ctt.code,
                                                'lmsProductCode' value null,
                                                'lmsSystemCode' value '',
                                                'lmsCountryCode' value ct.code),
                                   'plantCreatedIn' value
                                    json_object('elisPlantId' value null,
                                                'lmsCode' value bu.code, 'CountryCode' value ct.code)),
                       'statusChanged' value
                        json_object('newStatus' value case
                                      when rec_containerscan.scantimestamp not between c.dateactive and c.dateinactive - 1 then
                                       'inactive'
                                      else
                                       null
                                    end,
                                    'transition' value case
                                       when rec_containerscan.scantimestamp not between c.dateactive and c.dateinactive - 1 then
                                        'lost'
                                       else
                                        null
                                     end, 'comment' value null,
                                    'updatedAt' value rec_containerscan.scantimestamp) returning clob) message_text
    into   l_country, l_plant, message_text
    from   businessunit bu
    join   plant pt
    on     pt.plant_id = bu.plant_id
    join   country ct
    on     ct.country_id = pt.country_id
    join   container c
    on     c.container_id = rec_containerscan.container_id
    join   containertype ctt
    on     ctt.containertype_id = c.containertype_id
    where  rec_containerscan.transactiontype_id in (560, 580)
    and    bu.businessunit_id = rec_containerscan.businessunit_id;
  end updatecontainer;

begin
  --process data
  case
    when rec_containerscan.transactiontype_id in (540, 541, 542, 543, 545) then
      begin
        --Process 6 - Container Packed Scan
        containerpackedscan;
        l_processnumber := 6;
      end;
    when rec_containerscan.transactiontype_id = 511 then
      begin
        --Process 7 - Container Returned Scan
        containerreturnedscan;
        l_processnumber := 7;
      end;
    when rec_containerscan.transactiontype_id = 550 then
      begin
        --Process 8 - Create Container
        createcontainer;
        l_processnumber := 8;
      end;
    when rec_containerscan.transactiontype_id in (560, 580) then
      begin
        --Process 9 - Update Container
        updatecontainer;
        l_processnumber := 9;
      end;
    else
      begin
        l_processnumber := -1;
        null; --do nothing
      end;
  end case;
  "&1".pack_connsolace.process_message_to_solace(fp_message_text => replace(message_text,
                                                                                       ':null',
                                                                                       ':""'),
                                                            l_processnumber => l_processnumber,
                                                            l_country => l_country,
                                                            l_plant => l_plant,
                                                            l_containerid => rec_containerscan.primaryid);
end connsolace_containerscan;
}';

BEGIN
  PACK_SCRIPT.EXECSCRIPTFILE(STMT);
END;
/

DECLARE
  STMT CLOB := Q'{create or replace trigger trg_csol_containerscan
  after insert on containerscan
  for each row
  when (new.systemuser_id <> 1 and
       new.transactiontype_id in (511, 540, 541, 542, 543, 545, 550, 560, 580))
--trigger trg_csol_containerscan version 0.2
declare
  lv_rec containerscan%rowtype;
begin
  lv_rec.containerscan_id          := :new.containerscan_id;
  lv_rec.scantimestamp             := :new.scantimestamp;
  lv_rec.businessunit_id           := :new.businessunit_id;
  lv_rec.primaryid                 := :new.primaryid;
  lv_rec.secondaryid               := :new.secondaryid;
  lv_rec.distributionpoint_link_id := :new.distributionpoint_link_id;
  lv_rec.containertype_id          := :new.containertype_id;
  lv_rec.stay_id                   := :new.stay_id;
  lv_rec.prevstay_id               := :new.prevstay_id;
  lv_rec.transactiontype_id        := :new.transactiontype_id;
  lv_rec.scanstation               := :new.scanstation;
  lv_rec.customerowned             := :new.customerowned;
  lv_rec.year                      := :new.year;
  lv_rec.week                      := :new.week;
  lv_rec.dayno                     := :new.dayno;
  lv_rec.container_id              := :new.container_id;
  lv_rec.tareweight                := :new.tareweight;
  lv_rec.systemuser_id             := :new.systemuser_id;
  lv_rec.timestamp                 := :new.timestamp;
  lv_rec.markdelete                := :new.markdelete;
  lv_rec.routecontrol_id           := :new.routecontrol_id;
  lv_rec.shipmentlistmat_id        := :new.shipmentlistmat_id;

  connsolace_containerscan(lv_rec);
end trg_csol_containerscan;
}';

BEGIN
  PACK_SCRIPT.EXECSCRIPTFILE(STMT);
END;
/

DECLARE
   STMT CLOB := Q'{CREATE TABLE SOLACE_SCANS (SCAN_ID NUMBER NOT NULL);}';
BEGIN
  PACK_SCRIPT.EXECSCRIPTFILE(STMT);
END;
/

DECLARE
STMT CLOB := Q'{create or replace trigger trg_csol_after_insert_scan 
 after insert on scan
 for each row

begin
  --version 0.1
  
  if (:new.transactiontype_id in (1, 2, 20, 21, 22, 23, 27, 42, 43, 65, 76, 81)) then
   insert into solace_scans (scan_id) values (:new.scan_id);
  end if;

end trg_csol_after_insert_scan;
}';

BEGIN
  PACK_SCRIPT.EXECSCRIPTFILE(STMT);
END;
/


DECLARE
   STMT CLOB := Q'{INSERT INTO databasescript (databasescript_id, code, usage, description, script, active, executionhistory, systemuser_id, timestamp, storelocation)
									  values (seq_databasescript.nextval, 'CNSL', 1, 'Process scans to Solace', 'begin
  -- Call the procedure
  "&1".PACK_CONNSOLACE.process_scan_to_solace;
end;', 'Y', 'Y', 1, sysdate, 0);
commit;}';

BEGIN
  PACK_SCRIPT.EXECSCRIPTFILE(STMT);
END;
/


exit;
