 
/*********************************************\
*   ABSSOLUTE Oracle Objects                  *
*                                             *
*   Objects:           TRIGGERS               *
*   Database version:  09.09.00               *
*   Date:              15-APR-2026            *
\*********************************************/
 
set define off 
 
create or replace trigger after_create_clientdelnote
       after insert on clientdeliverynote
       for each row

declare

       v_crs_onetimecharge customerresidentialsettings.onetimecharge%type;
       v_crs_onetimechargeproductid customerresidentialsettings.onetimechargeproduct_id%type;
       v_client_onetimecharged client.onetimecharged%type;
       v_client_allowonetimecharge client.allowonetimecharge%type;

       v_plpc_sizedefinitionid sizedefinition.sizedefinition_id%type;
       v_plpc_finishingmethodid finishingmethod.finishingmethod_id%type;

       v_invoicebusinessunitid businessunit.businessunit_id%type;
       v_productgroupid productgroup.productgroup_id%type;
begin

       select crs.onetimecharge, crs.onetimechargeproduct_id
       into v_crs_onetimecharge, v_crs_onetimechargeproductid
       from customerresidentialsettings crs
       where crs.customer_id = :new.customer_link_id;

       select c.onetimecharged, c.allowonetimecharge
       into v_client_onetimecharged, v_client_allowonetimecharge
       from client c
       where c.client_id = :new.client_link_id;


       if (v_crs_onetimecharge = 1 and v_crs_onetimechargeproductid is not null and v_client_allowonetimecharge = 1 and v_client_onetimecharged = 0) then
         begin --determine sizedefinition_id
           select plpc.sizedefinition_id
           into v_plpc_sizedefinitionid
           from productlistpercustomer plpc
           where plpc.customer_id = :new.customer_link_id
           and plpc.product_id = v_crs_onetimechargeproductid
           and plpc.dateactive <= :new.deliverydate
           and plpc.dateinactive < :new.deliverydate;
           exception
             when no_data_found then
               begin
                 select min(ps.sizedefinition_id)
                 into v_plpc_sizedefinitionid
                 from productsize ps
                 where ps.product_id = v_crs_onetimechargeproductid
                 order by ps.sequencenumber;
               end;
         end;
         begin --determine finishingmethod_id
           select plpc.finishingmethod_id
           into v_plpc_finishingmethodid
           from productlistpercustomer plpc
           where plpc.customer_id = :new.customer_link_id
           and plpc.product_id = v_crs_onetimechargeproductid
           and plpc.dateactive <= :new.deliverydate
           and plpc.dateinactive < :new.deliverydate;
           exception
             when no_data_found then
               begin
                 select min(pfm.finishingmethod_id)
                 into v_plpc_finishingmethodid
                 from productfinishingmethod pfm
                 where pfm.product_id = v_crs_onetimechargeproductid
                 order by pfm.finishingmethod_id;
               end;
         end;
         select p.productgroup_id
         into v_productgroupid
         from product p
         where p.product_id = v_crs_onetimechargeproductid;

         select min(ca.invoicebusinessunit_id) --determine invoicebusinessunit_id
         into v_invoicebusinessunitid
         from customeractivities ca
         where ca.customer_id = :new.customer_link_id
         and ca.activity_id = (select pg.activity_id from productgroup pg where pg.productgroup_id = v_productgroupid);

         insert into clientdeliverynoteline (clientdeliverynoteline_id, clientdeliverinote_id, product_link_id, sizedefinition_link_id, finishingmethod_id, qtyscannedin, qtycountedin, qtyscannedout, origin, invoicebusinessunit_id, systemuser_id, timestamp, invoiceline_id, price, priceondelnote, qtycountedout, selfpayment)
         select seq_clientdeliverynoteline.nextval,
         :new.clientdeliverynote_id,
         v_crs_onetimechargeproductid,
         v_plpc_sizedefinitionid,
         v_plpc_finishingmethodid,
         0,
         1,
         0,
         1,
         v_invoicebusinessunitid,
         :new.systemuser_id,
         :new.timestamp,
         null,
         null,
         null,
         1,
         0
         from dual;

         update client c set c.onetimecharged = 2 where c.client_id = :new.client_link_id;

       else if (v_crs_onetimecharge = 0 and v_client_onetimecharged = 0) then
            update client cl set cl.onetimecharged = 1 where cl.client_id = :new.client_link_id;
            end if;
       end if;

end after_create_clientdelnote;
/
create or replace trigger after_create_rentalqtyadj
  AFTER INSERT  ON rentalqtyadjustment
  FOR EACH ROW

DECLARE
dbCODE databasesettings.code%type;

yearnumber integer;
weeknumber integer;
currentyearnumber integer;
currentweeknumber integer;

BEGIN
  dbCODE := getDatabaseCode;
  if (dbCODE = 'BDE' or dbCode = 'LST' ) then
     yearnumber:=cast(to_char(:new.adjustmentdate, 'IYYY') as numeric);
     weeknumber:=getisoweek(:new.adjustmentdate);

     currentyearnumber:=cast(to_char(sysdate, 'IYYY') as numeric);
     currentweeknumber:=getisoweek(sysdate);

     if  (yearnumber = currentyearnumber and weeknumber = currentweeknumber) then
         PACK_SQMaxItemInCirculation.t_createOrStoreOnTrigger_RQA(:new.productlistperdistribpoint_id, :new.quantity, to_date('1900-01-01','yyyy-mm-dd'));
     end if;

  end if;

END after_create_rentalqtyadj;
/
create or replace trigger after_create_uniqueitem
  AFTER INSERT ON uniqueitemnonpool
  FOR EACH ROW

DECLARE
dbCODE databasesettings.code%type;
statusidInCirculation status.status_id%type;
statusID status.status_id%type;
productID product.product_id%type;
sizedefID sizedefinition.sizedefinition_id%type;
finishingMethodID finishingmethod.finishingmethod_id%type;
customerowned uniqueitem.customerowned%type;

BEGIN
  dbCODE := getDatabaseCode;
  if (dbCODE = 'BDE') then
     --status in circulation
     BEGIN
     SELECT st.status_id
        INTO statusidInCirculation
        FROM status st
       WHERE st.statustype = 'UI'
             AND st.code = '20';
     EXCEPTION
        WHEN no_data_found THEN
          statusidInCirculation := -1;
     END;

     select ui.status_id, ui.product_id, ui.sizedefinition_id, ui.finishingmethod_id, ui.customerowned
     into statusID, productID, sizedefID, finishingMethodID, customerowned
     from uniqueitem ui
     where ui.uniqueitem_id = :new.uniqueitem_id;

     if  (statusidInCirculation = statusID and
          customerowned = 'N' and (:new.freeextragarment='N' or :new.enddatefreeextra < trunc(sysdate))) then
         PACK_SQMaxItemInCirculation.t_createOrStoreOnTrigger_UI(:new.uniqueitem_id, 1, to_date('1900-01-01','yyyy-mm-dd'),
                                                                 productID, sizedefID, finishingMethodID, :new.weareremployment_id);
     end if;

  end if;

END after_create_uniqueitem;
/
create or replace trigger AFTER_INSERT_FIELDSPERDIALOG
AFTER INSERT ON FIELDSPERDIALOG
FOR EACH ROW
  BEGIN
    MERGE INTO ACCESSRIGHTFIELDLISTS ALIST
USING (SELECT :new.fieldsperdialog_id, AL.ACCESSRIGHTSLIST_ID
         FROM  ACCESSRIGHTSLIST AL
        WHERE :new.dialog_id = AL.DIALOG_ID
          AND :new.fieldsperdialog_id NOT IN
              (SELECT AFL.FIELDSPERDIALOG_ID
                 FROM ACCESSRIGHTFIELDLISTS AFL
                WHERE AL.ACCESSRIGHTSLIST_ID = AFL.ACCESSRIGHTSLIST_ID)) LL
ON (ALIST.FIELDSPERDIALOG_ID = :new.fieldsperdialog_id AND ALIST.ACCESSRIGHTSLIST_ID = LL.ACCESSRIGHTSLIST_ID)
WHEN NOT MATCHED THEN INSERT(ALIST.FIELDSPERDIALOG_ID, ALIST.ACCESSRIGHTSLIST_ID, ALIST.ACCESSRIGHT, ALIST.SYSTEMUSER_ID, ALIST.TIMESTAMP) VALUES(:new.fieldsperdialog_id, LL.ACCESSRIGHTSLIST_ID, 0, 1, SYSDATE);
END AFTER_INSERT_FIELDSPERDIALOG;
/
create or replace trigger AFTER_INSERT_MASTERAGREEMENT
  AFTER INSERT ON MASTERAGREEMENT
  FOR EACH ROW
DECLARE

BEGIN
  INSERT INTO MASTERAGREEMENT_HISTORY
    (MASTERAGREEMENT_ID,
     CODE,
     DESCRIPTION,
     USERGROUP_ID,
     CUSTOMER_LINK_ID,
     OWNER,
     ADDRESS_ID,
     COUNTRY_ID,
     PHONENUMBER,
     EMAIL,
     SIGNDATE,
     STARTDATE,
     ENDDATE,
     RENEGOTIATIONDATE,
     DURATION,
     TERMOFNOTICE,
     TERMINATIONDATE,
     AUTEXTENSIONPERIOD,
     REMARK,
     TRANSACTIONNUMBER,
     TRANSACTIONTYPE_ID,
     SYSTEMUSER_ID,
     TIMESTAMP)
  VALUES
    (:NEW.MASTERAGREEMENT_ID,
     :NEW.CODE,
     :NEW.DESCRIPTION,
     :NEW.USERGROUP_ID,
     :NEW.CUSTOMER_LINK_ID,
     :NEW.OWNER,
     :NEW.ADDRESS_ID,
     :NEW.COUNTRY_ID,
     :NEW.PHONENUMBER,
     :NEW.EMAIL,
     :NEW.SIGNDATE,
     :NEW.STARTDATE,
     :NEW.ENDDATE,
     :NEW.RENEGOTIATIONDATE,
     :NEW.DURATION,
     :NEW.TERMOFNOTICE,
     :NEW.TERMINATIONDATE,
     :NEW.AUTEXTENSIONPERIOD,
     :NEW.REMARK,
     1,
     701,
     :NEW.SYSTEMUSER_ID,
     SYSDATE);
END AFTER_INSERT_MASTERAGREEMENT;
/
create or replace trigger after_insert_stop
  after insert on stop
  for each row

declare
  lv_routeid     routecontrol.route_id%type;
  lv_drivingdate routecontrol.drivingdate%type;
begin
  select rc.route_id, rc.drivingdate
    into lv_routeid, lv_drivingdate
    from routecontrol rc
   where rc.routecontrol_id = :new.routecontrol_id;

  --update lastest information in uniqueitemroute
  merge into UNIQUEITEMROUTE UIR_UPD
  using (select UINP.UNIQUEITEM_ID
           from UNIQUEITEMNONPOOL UINP,
                WEAREREMPLOYMENT  WEMP,
                WEARER            W,
                UNIQUEITEMROUTE   UIR
          where UINP.WEAREREMPLOYMENT_ID = WEMP.WEAREREMPLOYMENT_ID
            and WEMP.WEARER_ID = W.WEARER_ID
            and UIR.UNIQUEITEM_ID = UINP.UNIQUEITEM_ID
            and W.CUSTOMER_ID = :NEW.CUSTOMER_LINK_ID
            and :NEW.DELIVERYPOINT_ID = nvl(WEMP.DELIVERYPOINT_ID, :NEW.DELIVERYPOINT_ID)
         ) SEL_MERGE
  on (SEL_MERGE.UNIQUEITEM_ID = UIR_UPD.UNIQUEITEM_ID)
  when matched then
    update
       set UIR_UPD.LASTINSTOP_ID = case
                                     when (UIR_UPD.LASTINSTOP_ID is null and
                                          UIR_UPD.LASTINROUTE_ID = LV_ROUTEID and
                                          UIR_UPD.LASTINDATE = LV_DRIVINGDATE) then
                                      :NEW.STOP_ID
                                     else
                                      UIR_UPD.LASTINSTOP_ID
                                   end,
           UIR_UPD.LASTOUTSTOP_ID = case
                                      when (UIR_UPD.LASTOUTSTOP_ID is null and
                                           UIR_UPD.LASTOUTROUTE_ID = LV_ROUTEID and
                                           UIR_UPD.LASTOUTDATE = LV_DRIVINGDATE) then
                                       :NEW.STOP_ID
                                      else
                                       UIR_UPD.LASTOUTSTOP_ID
                                    end,
           UIR_UPD.PLANNEDOUTSTOP_ID = case
                                         when (UIR_UPD.PLANNEDOUTSTOP_ID is null and
                                              UIR_UPD.PLANNEDOUTROUTE_ID = LV_ROUTEID and
                                              UIR_UPD.PLANNEDOUTDATE = LV_DRIVINGDATE) then
                                          :NEW.STOP_ID
                                         else
                                          UIR_UPD.PLANNEDOUTSTOP_ID
                                       end;
end after_insert_stop;

/
create or replace trigger "AFTER_UPDATE_CUSTOMER"
  after update on customer
  for each row



declare
  --define cursor
  cursor ui_cursor IS
    select ui.uniqueitem_id,
           we.distributionpoint_id,
           ui.businessunit_id,
           ui.primaryid,
           sui.secondaryid,
           uinp.weareremployment_id,
           ui.product_id,
           ui.sizedefinition_id,
           ui.stay_id,
           ui.status_id,
           uifi.lastissuedate,
           ui.customerowned,
           uifi.qualitygrade_id,
           cl.customer_id
    from uniqueitem ui left outer join secondaryuniqueitem sui on ui.uniqueitem_id = sui.uniqueitem_id,
         uniqueitemfixedinfo uifi, uniqueitemnonpool uinp, weareremployment we, wearer w, customer_link cl
         where ui.uniqueitem_id = uinp.uniqueitem_id
           and ui.uniqueitem_id = uifi.uniqueitem_id
             and uinp.weareremployment_id = we.weareremployment_id
                 and we.wearer_id = w.wearer_id
                   and w.customer_id = cl.customer_id
                     and cl.customer_id = :old.customer_id
                         and ui.relatedcustomer_id is not null;
  --local variables
  lv_ui_id uniqueitem.uniqueitem_id%type;
  lv_dp_id distributionpoint.distributionpoint_id%type;
  lv_bu_id businessunit.businessunit_id%type;
  lv_primary_id uniqueitem.primaryid%type;
  lv_sec_id secondaryuniqueitem.secondaryid%type;
  lv_we_id weareremployment.weareremployment_id%type;
  lv_product_id product.product_id%type;
  lv_sizedef_id sizedefinition.sizedefinition_id%type;
  lv_stay_id stay.stay_id%type;
  lv_status_id status.status_id%type;
  lv_lastissuedate uniqueitemfixedinfo.lastissuedate%type;
  lv_customerowned uniqueitem.customerowned%type;
  lv_qualitygrade_id qualitygrade.qualitygrade_id%type;
  lv_customer_id customer.customer_id%type;
  lv_b customer.customer_id%type;

begin
  if (:old.customerrelated <> 0 and :new.customerrelated <> :old.customerrelated and :new.customerrelated <> 0) then
    begin
     open ui_cursor;
     fetch ui_cursor
        into
        lv_ui_id,
        lv_dp_id,
        lv_bu_id,
        lv_primary_id,
        lv_sec_id,
        lv_we_id,
        lv_product_id,
        lv_sizedef_id,
        lv_stay_id,
        lv_status_id,
        lv_lastissuedate,
        lv_customerowned,
        lv_qualitygrade_id,
        lv_customer_id;
     while ui_cursor%found loop
      --determine value for relatedcustomer_id
      if :new.customerrelated = 1 then lv_b := :new.customer_id;
        else if :new.customerrelated = 2 then lv_b := :new.debtor_id;
          else if :new.customerrelated = 3 then lv_b := :new.masteraccount_id;
			else if :new.customerrelated = 4 then lv_b := :new.commoncustomer_id;
              else if :new.customerrelated = 5 then lv_b := :new.othercustomer_id;
				end if;
				end if;
				end if;
				end if;
				end if;
      --update uniqueitem.relatedcustomer_id
      update uniqueitem ui set ui.relatedcustomer_id = lv_b
      where ui.uniqueitem_id = lv_ui_id;
      --insert new scan record
      insert into scan
        (scan_id,
         businessunit_id,
         scantimestamp,
         primaryid,
         customer_link_id,
         weareremployment_link_id,
         distributionpoint_link_id,
         product_link_id,
         sizedefinition_link_id,
         stay_id,
         status_id,
         scanstation,
         issuedate,
         route_id,
         reasoncode_id,
         customerowned,
         year,
         week,
         dayno,
         qualitygrade_id,
         systemuser_id,
         timestamp,
         prevstay_id,
         transactiontype_id,
         dayslate,
         uniqueitem_link_id,
         secondaryid)
       select
             seq_scan.nextval,
             lv_bu_id,
             sysdate,
             lv_primary_id,
             lv_customer_id,
             lv_we_id,
             lv_dp_id,
             lv_product_id,
             lv_sizedef_id,
             lv_stay_id,
             lv_status_id,
             null,
             lv_lastissuedate,
             null,
             null,
             lv_customerowned,
             year(sysdate),
             getweeknumber(sysdate),
             to_char(sysdate,'D'),
             lv_qualitygrade_id,
             getminsystemuser(),
             sysdate,
             lv_stay_id,
             case when :new.customerrelated = 1 then 77
               when :new.customerrelated = 2 then 78
                 when :new.customerrelated = 3 then 79
					when :new.customerrelated = 4 then 80
						when :new.customerrelated = 5 then 81
             end,
             0,
             lv_ui_id,
             lv_sec_id
        from dual;
      fetch ui_cursor
        into
        lv_ui_id,
        lv_dp_id,
        lv_bu_id,
        lv_primary_id,
        lv_sec_id,
        lv_we_id,
        lv_product_id,
        lv_sizedef_id,
        lv_stay_id,
        lv_status_id,
        lv_lastissuedate,
        lv_customerowned,
        lv_qualitygrade_id,
        lv_customer_id;
  end loop;
  close ui_cursor;
  end;
  end if;
end AFTER_UPDATE_CUSTOMER;
/
create or replace trigger AFTER_UPDATE_MASTERAGREEMENT
  AFTER UPDATE ON MASTERAGREEMENT
  FOR EACH ROW
DECLARE

BEGIN
  INSERT INTO MASTERAGREEMENT_HISTORY
    (MASTERAGREEMENT_ID,
     CODE,
     DESCRIPTION,
     USERGROUP_ID,
     CUSTOMER_LINK_ID,
     OWNER,
     ADDRESS_ID,
     COUNTRY_ID,
     PHONENUMBER,
     EMAIL,
     SIGNDATE,
     STARTDATE,
     ENDDATE,
     RENEGOTIATIONDATE,
     DURATION,
     TERMOFNOTICE,
     TERMINATIONDATE,
     AUTEXTENSIONPERIOD,
     REMARK,
     TRANSACTIONNUMBER,
     TRANSACTIONTYPE_ID,
     SYSTEMUSER_ID,
     TIMESTAMP)
  VALUES
    (:NEW.MASTERAGREEMENT_ID,
     :NEW.CODE,
     :NEW.DESCRIPTION,
     :NEW.USERGROUP_ID,
     :NEW.CUSTOMER_LINK_ID,
     :NEW.OWNER,
     :NEW.ADDRESS_ID,
     :NEW.COUNTRY_ID,
     :NEW.PHONENUMBER,
     :NEW.EMAIL,
     :NEW.SIGNDATE,
     :NEW.STARTDATE,
     :NEW.ENDDATE,
     :NEW.RENEGOTIATIONDATE,
     :NEW.DURATION,
     :NEW.TERMOFNOTICE,
     :NEW.TERMINATIONDATE,
     :NEW.AUTEXTENSIONPERIOD,
     :NEW.REMARK,
     (SELECT MAX(M.TRANSACTIONNUMBER)+1 FROM MASTERAGREEMENT_HISTORY M WHERE M.MASTERAGREEMENT_ID = :NEW.MASTERAGREEMENT_ID),
     700,
     :NEW.SYSTEMUSER_ID,
     SYSDATE);
END AFTER_UPDATE_MASTERAGREEMENT;
/
create or replace trigger after_update_rentalqtyadj
  AFTER UPDATE  ON rentalqtyadjustment
  FOR EACH ROW

DECLARE
dbCODE databasesettings.code%type;
qty integer;

yearnumber integer;
weeknumber integer;
currentyear integer;
currentweek integer;
oldyearnumber integer;
oldweeknumber integer;

BEGIN
  dbCODE := getDatabaseCode;
  if (dbCODE = 'BDE' or dbCode = 'LST') then
     yearnumber:=cast(to_char(:new.adjustmentdate, 'IYYY') as numeric);
     weeknumber:=getisoweek(:new.adjustmentdate);

     oldyearnumber:=cast(to_char(:old.adjustmentdate, 'IYYY') as numeric);
     oldweeknumber:=getisoweek(:old.adjustmentdate);

     currentYear:=cast(to_char(sysdate, 'IYYY') as numeric);
     currentWeek:=getisoweek(sysdate);

     -- old.adjustmentdate is in current week; new.adjustmentdate is in current week; (old.quantity > 0 and new.quantity <= 0) or (new.quantity - old.quantity > 0)
    if (oldweeknumber = currentWeek and oldyearnumber = currentYear and
        weeknumber = currentWeek and yearnumber = currentYear and
        ((:old.quantity > 0 and :new.quantity <= 0) or (:new.quantity - :old.quantity > 0))
       ) then
        qty := 0;
        if (:old.quantity > 0 and :new.quantity <= 0) then qty := (-1)*:old.quantity; end if;
        if (:new.quantity - :old.quantity > 0 and :old.quantity > 0) then qty := :new.quantity - :old.quantity; end if;
        if (:new.quantity - :old.quantity > 0 and :old.quantity <= 0) then qty := :new.quantity; end if;
        PACK_SQMaxItemInCirculation.t_createOrStoreOnTrigger_RQA(:new.productlistperdistribpoint_id, qty, to_date('1900-01-01','yyyy-mm-dd'));
    end if;
    -- old.adjustmentdate is in a future week; new.adjustmentdate is in current week; new.quantity > 0
    if (((oldweeknumber > currentWeek and oldyearnumber = currentYear) or
         (oldyearnumber > currentYear)) and
        weeknumber = currentWeek and yearnumber = currentYear and
        :new.quantity > 0
        ) then
        PACK_SQMaxItemInCirculation.t_createOrStoreOnTrigger_RQA(:new.productlistperdistribpoint_id, :new.quantity, to_date('1900-01-01','yyyy-mm-dd'));
    end if;
    -- new.adjustmentdate is in a future week; old.adjustmentdate is in current week; old.quantity > 0
    if (((weeknumber > currentWeek and yearnumber = currentYear) or
         (yearnumber > currentYear)) and
        oldweeknumber = currentWeek and oldyearnumber = currentYear and
        :old.quantity > 0
        ) then
      PACK_SQMaxItemInCirculation.t_createOrStoreOnTrigger_RQA(:new.productlistperdistribpoint_id, (-1)*:old.quantity, to_date('1900-01-01','yyyy-mm-dd'));
    end if;

 end if;

END after_update_rentalqtyadj;
/
create or replace trigger after_update_uniqueitem
  AFTER UPDATE ON uniqueitem
  FOR EACH ROW



DECLARE
dbCODE                databasesettings.code%type;
statusidInCirculation status.status_id%type;
statusidInStock       status.status_id%type;
wearerEmploymentID    uniqueitemnonpool.weareremployment_id%type;
freeextragarment      uniqueitemnonpool.freeextragarment%type;
enddatefreeextra      uniqueitemnonpool.enddatefreeextra%type;
returnFromCustomerDate date;

BEGIN
  dbCODE := getDatabaseCode;
  if (dbCODE = 'BDE') then
     --status in circulation
     BEGIN
     SELECT st.status_id
        INTO statusidInCirculation
        FROM status st
       WHERE st.statustype = 'UI'
             AND st.code = '20';
     EXCEPTION
        WHEN no_data_found THEN
          statusidInCirculation := -1;
     END;

     BEGIN
      SELECT st.status_id
        INTO statusidInStock
        FROM status st
       WHERE st.statustype = 'UI'
             AND st.code = '10';
     EXCEPTION
        WHEN no_data_found THEN
          statusidInCirculation := -1;
     END;

     BEGIN
       select un.weareremployment_id, un.freeextragarment, un.enddatefreeextra
       into wearerEmploymentID, freeextragarment,enddatefreeextra
       from uniqueitemnonpool un
       where un.uniqueitem_id = :new.uniqueitem_id;
     EXCEPTION
        WHEN no_data_found THEN
          wearerEmploymentID := -1;
     END;
    --new status in circulation
    if  (statusidInCirculation = :new.status_id and :old.status_id <> :new.status_id and
          :new.customerowned = 'N' and (freeextragarment = 'N' or enddatefreeextra < trunc(sysdate)))then
         PACK_SQMaxItemInCirculation.t_createOrStoreOnTrigger_UI(:new.uniqueitem_id, 1, to_date('1900-01-01','yyyy-mm-dd'),
                                                                 :new.product_id, :new.sizedefinition_id, :new.finishingmethod_id, wearerEmploymentID);
     end if;
    --new status in stock , same date of changing status
    if  (statusidInCirculation = :old.status_id and trunc(nvl(:old.statuschangedate, getabsmindate)) = trunc(:new.statuschangedate) and
         statusidInStock = :new.status_id and :new.customerowned = 'N' and (freeextragarment = 'N' or enddatefreeextra < trunc(sysdate))) then
         PACK_SQMaxItemInCirculation.t_createOrStoreOnTrigger_UI(:new.uniqueitem_id, -1, to_date('1900-01-01','yyyy-mm-dd'),
                                                                 :new.product_id, :new.sizedefinition_id, :new.finishingmethod_id, wearerEmploymentID);
     end if;
    --new status in stock , differet dates of changing statuses
    if  (statusidInCirculation = :old.status_id AND trunc(nvl(:old.statuschangedate, getabsmindate)) <> trunc(:new.statuschangedate) and
         statusidInStock = :new.status_id AND :new.customerowned = 'N'and (freeextragarment = 'N' or enddatefreeextra < trunc(sysdate))) then
         returnFromCustomerDate := to_date('1900-01-01','yyyy-mm-dd');
         if (:old.lastscandate is not null  AND :old.lastinscandate is not null AND trunc(:old.lastscandate) = trunc(:old.lastinscandate)) then
            returnFromCustomerDate := :old.lastscandate;
         else
           BEGIN
             SELECT MAX(RC.DRIVINGDATE)
             into returnFromCustomerDate
               FROM ROUTECONTROL      RC,
                    UNIQUEITEMNONPOOL U,
                    DISTRIBUTIONPOINT DIS,
                    STOP              S
               LEFT OUTER JOIN(ROUTESTOP RS
               LEFT OUTER JOIN DELIVERYPOINT DP
                 ON RS.DELIVERYPOINT_ID = DP.DELIVERYPOINT_ID) ON S.ROUTESTOP_ID = RS.ROUTESTOP_ID, WEAREREMPLOYMENT WE, WEARER W
              WHERE U.WEAREREMPLOYMENT_ID = WE.WEAREREMPLOYMENT_ID
                AND WE.WEARER_ID = W.WEARER_ID
                AND W.CUSTOMER_ID = S.CUSTOMER_LINK_ID
                AND S.STATUS in (0, --Stop.STATUS_NORMAL ,
                                 3, --Stop.STATUS_MOVED_IN ,
                                 4) --Stop.STATUS_INACTIVE
                AND WE.DISTRIBUTIONPOINT_ID = DIS.DISTRIBUTIONPOINT_ID
                AND S.ROUTECONTROL_ID = RC.ROUTECONTROL_ID
                AND (nvl(S.ROUTESTOP_ID, 0) = 0 OR
                    nvl(RS.DELIVERYPOINT_ID, 0) = 0 OR
                    DIS.DELIVERYPOINT_ID = RS.DELIVERYPOINT_ID)
                AND U.UNIQUEITEM_ID = :new.uniqueitem_id
                AND RC.DRIVINGDATE < :old.statuschangedate;
              exception when NO_DATA_FOUND then
                returnFromCustomerDate := to_date('1900-01-01','yyyy-mm-dd');
              END;
         end if;
         PACK_SQMaxItemInCirculation.t_createOrStoreOnTrigger_UI(:new.uniqueitem_id, -1, returnFromCustomerDate,
                                                                 :new.product_id, :new.sizedefinition_id, :new.finishingmethod_id, wearerEmploymentID);
     end if;

  end if;

END after_update_uniqueitem;
/
create or replace trigger AFT_INS_SOILTICKETLINE
  AFTER INSERT ON SOILTICKETLINE
  FOR EACH ROW
DECLARE
BEGIN
  insert into soilticketlineperuser
    (soilticketlineperuser_id,
     soilticketline_id,
     countkeypad,
     countconverted,
     countlightframe,
     countabused,
     countloss,
     systemuser_id,
     timestamp)
  values
    (seq_soilticketlineperuser.nextval,
     :new.soilticketline_id,
     nvl(:new.countkeypad, 0),
     nvl(:new.countconverted, 0),
     nvl(:new.countlightframe, 0),
     nvl(:new.countabused, 0),
     nvl(:new.countloss, 0),
     :new.systemuser_id,
     sysdate);
END AFT_INS_SOILTICKETLINE;
/
create or replace trigger aft_ins_standardlocationpprod
  after insert on standardlocationperproduct
  for each row
begin
  if :new.definepersize = 'N' then
    add_product_stockroom(:new.stockroomlocation_id,
                          :new.product_id,
                          :new.customerowned,
                          :new.customer_id,
                          :new.allowedgrade,
                          :new.definepersize,
                          null);
  end if;
end aft_ins_standardlocationpprod;
/
create or replace trigger aft_ins_standardlocationpsize
  after insert on standardlocationpersize
  for each row
declare
  lv_stockroomlocationid standardlocationperproduct.stockroomlocation_id%type;
  lv_productid product.product_id%type;
  lv_customerowned standardlocationperproduct.customerowned%type;
  lv_customerid standardlocationperproduct.customer_id%type;
  lv_allowedgrade standardlocationperproduct.allowedgrade%type;
begin
  select sl.stockroomlocation_id,sl.product_id,sl.customerowned,sl.customer_id,
         sl.allowedgrade
  into lv_stockroomlocationid,lv_productid,lv_customerowned,lv_customerid,
       lv_allowedgrade
  from standardlocationperproduct sl
  where sl.standardlocationperproduct_id = :new.standardlocationperproduct_id;
  add_product_stockroom(lv_stockroomlocationid,
                        lv_productid,
                        lv_customerowned,
                        lv_customerid,
                        lv_allowedgrade,
                        'Y',
                        :new.sizedefinition_id);
end aft_ins_standardlocationpsize;
/
create or replace trigger AFT_UPDATE_SOILTICKETLINE
  AFTER UPDATE ON SOILTICKETLINE
  FOR EACH ROW
DECLARE

BEGIN
  insert into soilticketlineperuser
    (soilticketlineperuser_id,
     soilticketline_id,
     countkeypad,
     countconverted,
     countlightframe,
     countabused,
     countloss,
     systemuser_id,
     timestamp)
  values
    (seq_soilticketlineperuser.nextval,
     :new.soilticketline_id,
     nvl(:new.countkeypad, 0)-nvl(:old.countkeypad, 0),
     nvl(:new.countconverted, 0)-nvl(:old.countconverted, 0),
     nvl(:new.countlightframe, 0)-nvl(:old.countlightframe, 0),
     nvl(:new.countabused, 0)-nvl(:old.countabused, 0),
     nvl(:new.countloss, 0)-nvl(:old.countloss, 0),
     :new.systemuser_id,
     sysdate);
END AFT_UPDATE_SOILTICKETLINE;
/
create or replace trigger aft_upd_standardlocationpprod
  after update on standardlocationperproduct
  for each row
begin
  if :old.definepersize = 'Y' and :new.definepersize = 'N' then
    add_product_stockroom(:new.stockroomlocation_id,
                          :new.product_id,
                          :new.customerowned,
                          :new.customer_id,
                          :new.allowedgrade,
                          :new.definepersize,
                          null);
  end if;
end aft_upd_standardlocationpprod;
/
create or replace trigger "BEFORE_CREATE_INVOICEHEADER"
  BEFORE INSERT  ON INVOICEHEADER
  FOR EACH ROW


BEGIN

  :NEW.CREATIONSYSTEMUSER_ID := :NEW.SYSTEMUSER_ID;
  :NEW.CREATIONTIMESTAMP := SYSDATE;

END BEFORE_CREATE_INVOICEHEADER;
/
create or replace trigger "BEFORE_CREATE_PACKINGNOTE"
  BEFORE INSERT ON PACKINGNOTE
  FOR EACH ROW

BEGIN

  IF :NEW.CREATIONSYSTEMUSER_ID IS NULL THEN
    :NEW.CREATIONSYSTEMUSER_ID := :NEW.SYSTEMUSER_ID;
  END IF;

END BEFORE_CREATE_PACKINGNOTE;
/
create or replace trigger "BEFORE_CREATE_WEIGHINGTICKET"
  BEFORE INSERT  ON WEIGHINGTICKET
  FOR EACH ROW


BEGIN

  :NEW.CREATIONSYSTEMUSER_ID := :NEW.SYSTEMUSER_ID;
  :NEW.CREATIONTIMESTAMP := SYSDATE;

END BEFORE_CREATE_WEIGHINGTICKET;
/
create or replace trigger before_delete_workstation
  BEFORE DELETE ON workstation
  FOR EACH ROW

DECLARE
BEGIN
  update systemuser s
     set s.lastloginatworkstation = null
   where s.lastloginatworkstation = :old.workstation_id;
END before_delete_workstation;
/
create or replace trigger BEFORE_INSERT_CRENTDISCOUNT
       BEFORE INSERT ON CUSTOMERRENTDISCOUNT
       FOR EACH ROW
BEGIN
  :NEW.CREATIONUSER_ID := :NEW.SYSTEMUSER_ID;
  :NEW.CREATIONTIMESTAMP := :NEW.TIMESTAMP;
END;
/
create or replace trigger BEFORE_INSERT_CRENTPRICE
       BEFORE INSERT ON CUSTOMERRENTPRICE
       FOR EACH ROW
BEGIN
  :NEW.CREATIONUSER_ID := :NEW.SYSTEMUSER_ID;
  :NEW.CREATIONTIMESTAMP := :NEW.TIMESTAMP;
END;
/
create or replace trigger BEFORE_INSERT_CREPAIRPRICE
       BEFORE INSERT ON CUSTOMERREPAIRPRICE
       FOR EACH ROW
BEGIN
  :NEW.CREATIONUSER_ID := :NEW.SYSTEMUSER_ID;
  :NEW.CREATIONTIMESTAMP := :NEW.TIMESTAMP;
END;
/
create or replace trigger BEFORE_INSERT_CREPLACEPRICE
       BEFORE INSERT ON CUSTOMERREPLACEMENTPRICE
       FOR EACH ROW
BEGIN
  :NEW.CREATIONUSER_ID := :NEW.SYSTEMUSER_ID;
  :NEW.CREATIONTIMESTAMP := :NEW.TIMESTAMP;
END;
/
create or replace trigger BEFORE_INSERT_CRESIDRENTPRICE
       BEFORE INSERT ON CUSTOMERRESIDENTIALRENTPRICE
       FOR EACH ROW
BEGIN
  :NEW.CREATIONUSER_ID := :NEW.SYSTEMUSER_ID;
  :NEW.CREATIONTIMESTAMP := :NEW.TIMESTAMP;
END;
/
create or replace trigger BEFORE_INSERT_CSALESPRICE
       BEFORE INSERT ON CUSTOMERSALESPRICE
       FOR EACH ROW
BEGIN
  :NEW.CREATIONUSER_ID := :NEW.SYSTEMUSER_ID;
  :NEW.CREATIONTIMESTAMP := :NEW.TIMESTAMP;
END;
/
create or replace trigger BEFORE_INSERT_CSPECIALCHPRICE
       BEFORE INSERT ON CUSTOMERSPECIALCHARGEPRICE
       FOR EACH ROW
BEGIN
  :NEW.CREATIONUSER_ID := :NEW.SYSTEMUSER_ID;
  :NEW.CREATIONTIMESTAMP := :NEW.TIMESTAMP;
END;
/
create or replace trigger BEFORE_INSERT_CUSTOMER
  BEFORE INSERT ON CUSTOMER
  FOR EACH ROW WHEN (
   (NEW.SICCODE_ID IS NOT NULL) OR
   (NEW.CREATEDBYSYSTEMUSER_ID IS NULL) OR
   (NEW.CREATEDTIMESTAMP IS NULL))
DECLARE
  -- TRIGGER BEFORE_INSERT_CUSTOMER VERSION 0.2
  CURSOR GET_SICCODE IS
    SELECT S.CODE
      FROM SICCODE S
     WHERE S.SICCODE_ID = :NEW.SICCODE_ID;
BEGIN
  IF (:NEW.SICCODE_ID IS NOT NULL) THEN
    FOR SIC_REC IN GET_SICCODE LOOP
      IF (:NEW.SICCODE <> SIC_REC.CODE) THEN
        :NEW.SICCODE := SIC_REC.CODE;
      END IF;
    END LOOP;
  END IF;
  :NEW.CREATEDBYSYSTEMUSER_ID := NVL(:NEW.CREATEDBYSYSTEMUSER_ID, :NEW.SYSTEMUSER_ID);
  :NEW.CREATEDTIMESTAMP := NVL(:NEW.CREATEDTIMESTAMP, :NEW.TIMESTAMP);
END BEFORE_INSERT_CUSTOMER;
/
create or replace trigger BEFORE_INSERT_CWASHDISCOUNT
       BEFORE INSERT ON CUSTOMERWASHDISCOUNT
       FOR EACH ROW
BEGIN
  :NEW.CREATIONUSER_ID := :NEW.SYSTEMUSER_ID;
  :NEW.CREATIONTIMESTAMP := :NEW.TIMESTAMP;
END;
/
create or replace trigger BEFORE_INSERT_CWASHPRICE
       BEFORE INSERT ON CUSTOMERWASHPRICE
       FOR EACH ROW
BEGIN
  :NEW.CREATIONUSER_ID := :NEW.SYSTEMUSER_ID;
  :NEW.CREATIONTIMESTAMP := :NEW.TIMESTAMP;
END;
/
create or replace trigger BEFORE_INSERT_CWEIGHTDISCOUNT
       BEFORE INSERT ON CUSTOMERWEIGHTDISCOUNT
       FOR EACH ROW
BEGIN
  :NEW.CREATIONUSER_ID := :NEW.SYSTEMUSER_ID;
  :NEW.CREATIONTIMESTAMP := :NEW.TIMESTAMP;
END;
/
create or replace trigger BEFORE_INSERT_CWEIGHTPRICE
       BEFORE INSERT ON CUSTOMERWEIGHTPRICE
       FOR EACH ROW
BEGIN
  :NEW.CREATIONUSER_ID := :NEW.SYSTEMUSER_ID;
  :NEW.CREATIONTIMESTAMP := :NEW.TIMESTAMP;
END;
/
create or replace trigger before_insert_dv
  before insert on deliveryvariations
  for each row
declare
  v_procbu_id         businessunit.businessunit_id%type;
  v_internalprocbu_id businessunit.businessunit_id%type;
  v_plantid           plant.plant_id%type;
begin
  if :new.processbusinessunit_id is null then
    begin
   select ca.processbusinessunit_id, ca.internalprocessingbu_id
     into v_procbu_id, v_internalprocbu_id
     from product            p,
          productgroup       pg,
          customeractivities ca,
          distributionpoint  dp,
          department         d,
          customer           c
    where p.product_id = :new.product_id
          and p.productgroup_id = pg.productgroup_id
          and pg.activity_id = ca.activity_id
          and dp.distributionpoint_id = :new.distributionpoint_id
          and dp.department_id = d.department_id
          and d.customer_id = ca.customer_id
          and d.customer_id = c.customer_id
          and c.customer_id = ca.customer_id
    order by case
               when ca.processbusinessunit_id = c.defaultbusinessunit_id then
                1
               else
                0
             end desc,
             case
               when ca.internalprocessingbu_id = c.internalbusinessunit_id then
                1
               else
                0
             end desc
    fetch first 1 row only;
      select bu.plant_id
      into v_plantid
      from businessunit bu
      where bu.businessunit_id = v_procbu_id;
      if v_procbu_id > 0 then
        :new.processbusinessunit_id := v_procbu_id;
      end if;
      if (v_internalprocbu_id > 0) then
        :new.internalprocessingbu_id := v_internalprocbu_id;
      end if;
      exception
        when others then
          abslogwrite.abslog_write(5,'trigger before_insert_dv', v_plantid, 1,'Error on executing trigger trigger before_insert_dv', :new.systemuser_id);
    end;
  end if;
end before_insert_dv;
/
create or replace trigger BEFORE_INSERT_GEMBLEMPRICE
       BEFORE INSERT ON GENERALEMBLEMPRICE
       FOR EACH ROW
BEGIN
  :NEW.CREATIONUSER_ID := :NEW.SYSTEMUSER_ID;
  :NEW.CREATIONTIMESTAMP := :NEW.TIMESTAMP;
END;
/
create or replace trigger BEFORE_INSERT_GINSURANCEPRICE
       BEFORE INSERT ON GENERALINSURANCEPRICE
       FOR EACH ROW
BEGIN
  :NEW.CREATIONUSER_ID := :NEW.SYSTEMUSER_ID;
  :NEW.CREATIONTIMESTAMP := :NEW.TIMESTAMP;
END;
/
create or replace trigger BEFORE_INSERT_GRENTDISCOUNT
       BEFORE INSERT ON GENERALRENTDISCOUNT
       FOR EACH ROW
BEGIN
  :NEW.CREATIONUSER_ID := :NEW.SYSTEMUSER_ID;
  :NEW.CREATIONTIMESTAMP := :NEW.TIMESTAMP;
END;
/
create or replace trigger BEFORE_INSERT_GRENTPRICE
       BEFORE INSERT ON GENERALRENTPRICE
       FOR EACH ROW
BEGIN
  :NEW.CREATIONUSER_ID := :NEW.SYSTEMUSER_ID;
  :NEW.CREATIONTIMESTAMP := :NEW.TIMESTAMP;
END;
/
create or replace trigger BEFORE_INSERT_GREPAIRPRICE
       BEFORE INSERT ON GENERALREPAIRPRICE
       FOR EACH ROW
BEGIN
  :NEW.CREATIONUSER_ID := :NEW.SYSTEMUSER_ID;
  :NEW.CREATIONTIMESTAMP := :NEW.TIMESTAMP;
END;
/
create or replace trigger BEFORE_INSERT_GREPLACEPRICE
       BEFORE INSERT ON GENERALREPLACEMENTPRICE
       FOR EACH ROW
BEGIN
  :NEW.CREATIONUSER_ID := :NEW.SYSTEMUSER_ID;
  :NEW.CREATIONTIMESTAMP := :NEW.TIMESTAMP;
END;
/
create or replace trigger BEFORE_INSERT_GSALESPRICE
       BEFORE INSERT ON GENERALSALESPRICE
       FOR EACH ROW
BEGIN
  :NEW.CREATIONUSER_ID := :NEW.SYSTEMUSER_ID;
  :NEW.CREATIONTIMESTAMP := :NEW.TIMESTAMP;
END;
/
create or replace trigger BEFORE_INSERT_GSPECIALCHPRICE
       BEFORE INSERT ON GENERALSPECIALCHARGEPRICE
       FOR EACH ROW
BEGIN
  :NEW.CREATIONUSER_ID := :NEW.SYSTEMUSER_ID;
  :NEW.CREATIONTIMESTAMP := :NEW.TIMESTAMP;
END;
/
create or replace trigger BEFORE_INSERT_GWASHDISCOUNT
       BEFORE INSERT ON GENERALWASHDISCOUNT
       FOR EACH ROW
BEGIN
  :NEW.CREATIONUSER_ID := :NEW.SYSTEMUSER_ID;
  :NEW.CREATIONTIMESTAMP := :NEW.TIMESTAMP;
END;
/
create or replace trigger BEFORE_INSERT_GWASHPRICE
       BEFORE INSERT ON GENERALWASHPRICE
       FOR EACH ROW
BEGIN
  :NEW.CREATIONUSER_ID := :NEW.SYSTEMUSER_ID;
  :NEW.CREATIONTIMESTAMP := :NEW.TIMESTAMP;
END;
/
create or replace trigger BEFORE_INSERT_GWEIGHTDISCOUNT
       BEFORE INSERT ON GENERALWEIGHTDISCOUNT
       FOR EACH ROW
BEGIN
  :NEW.CREATIONUSER_ID := :NEW.SYSTEMUSER_ID;
  :NEW.CREATIONTIMESTAMP := :NEW.TIMESTAMP;
END;
/
create or replace trigger BEFORE_INSERT_GWEIGHTPRICE
       BEFORE INSERT ON GENERALWEIGHTPRICE
       FOR EACH ROW
BEGIN
  :NEW.CREATIONUSER_ID := :NEW.SYSTEMUSER_ID;
  :NEW.CREATIONTIMESTAMP := :NEW.TIMESTAMP;
END;
/
create or replace trigger BEFORE_INSERT_STOCKLOCATION
  BEFORE INSERT ON STOCKROOMLOCATION
  FOR EACH ROW
DECLARE
  -- local variables here
  LV_PLANT_ID PLANT.PLANT_ID%TYPE;
  LV_AUTOFILLUNIQUEID NUMBER := 0;
  DB_CODE DATABASESETTINGS.CODE%TYPE;
BEGIN
  -- determine plant of stockroom
  SELECT B.PLANT_ID INTO LV_PLANT_ID
  FROM STOCKROOM S, BUSINESSUNIT B
  WHERE B.BUSINESSUNIT_ID = S.MANAGEDBYBU_ID
  AND S.STOCKROOM_ID = :NEW.STOCKROOM_ID;
  -- determine value of system setting
  SELECT TO_NUMBER(S.VALUE) INTO LV_AUTOFILLUNIQUEID
  FROM SYSTEMSETTING S
  WHERE S.PLANT_ID = LV_PLANT_ID
  AND S.NAME = 'AUTOFILLLOCATIONUNIQUEID';
  -- Only when system setting AUTOFILLLOCATIONUNIQUEID = 1
  IF (LV_AUTOFILLUNIQUEID = 1) THEN
    -- Determine environment for any customized behavior
     SELECT DB.CODE INTO DB_CODE
      FROM DATABASESETTINGS DB;
    --
    IF (DB_CODE = 'DFD') THEN
      BEGIN
        IF ((:NEW.UNIQUEID IS NULL)
          OR(:NEW.UNIQUEID <> ('LP'||:NEW.AREACODE||:NEW.ROWCODE||:NEW.LOCATIONCODE))) THEN
          :NEW.UNIQUEID := 'LP'||:NEW.AREACODE||:NEW.ROWCODE||:NEW.LOCATIONCODE;
        END IF;
      END;
    ELSE
      BEGIN
        IF ((:NEW.UNIQUEID IS NULL)
          OR(:NEW.UNIQUEID <> (:NEW.AREACODE||:NEW.ROWCODE||:NEW.LOCATIONCODE))) THEN
          :NEW.UNIQUEID := :NEW.AREACODE||:NEW.ROWCODE||:NEW.LOCATIONCODE;
        END IF;
      END;
    END IF;
  END IF;
END BEFORE_INSERT_STOCKLOCATION;
/
create or replace trigger BEFORE_INS_PLPCINSURANCEPRICE
	BEFORE INSERT ON PLPCINSURANCEPRICE
	FOR EACH ROW
BEGIN
	:NEW.CREATIONUSER_ID := :NEW.SYSTEMUSER_ID;
	:NEW.CREATIONTIMESTAMP := :NEW.TIMESTAMP;
END;
/
create or replace trigger "BEFORE_UPDATE_ADDRESS" 
  BEFORE UPDATE ON ADDRESS
 FOR EACH ROW

BEGIN
  IF upper(nvl(:OLD.STREET,'-1')) <> upper(nvl(:NEW.STREET,'-1')) OR
     nvl(:OLD.HOUSENUMBER,-1) <> nvl(:NEW.HOUSENUMBER,-1) OR
     upper(nvl(:OLD.HOUSENUMBEREXT,'-1')) <> upper(nvl(:NEW.HOUSENUMBEREXT,'-1')) OR
     upper(nvl(:OLD.ZIPCODE,'-1')) <> upper(nvl(:NEW.ZIPCODE,'-1')) OR
     upper(nvl(:OLD.CITY,'-1')) <> upper(nvl(:NEW.CITY,'-1')) OR
     upper(nvl(:OLD.COUNTY,'-1')) <> upper(nvl(:NEW.COUNTY,'-1')) OR
     upper(nvl(:OLD.STATE,'-1')) <> upper(nvl(:NEW.STATE,'-1')) THEN
    BEGIN

      -- clear gps info for deliverypoints that have this (customer or specific) address
      UPDATE DELIVERYPOINT P
         SET P.GPSLATITUDE  = :NEW.LATITUDE,
             P.GPSLONGITUDE = :NEW.LONGITUDE,
             P.GPSTIMESTAMP = SYSDATE,
             P.TIMESTAMP = SYSDATE,
             P.SYSTEMUSER_ID = :NEW.SYSTEMUSER_ID
       WHERE P.DELIVERYADDRESS_ID = :NEW.ADDRESS_ID;

    END;
  END IF;
END BEFORE_UPDATE_ADDRESS;

/
create or replace trigger BEFORE_UPDATE_CUSTOMER
  BEFORE UPDATE ON CUSTOMER
  FOR EACH ROW
DECLARE
  -- LOCAL VARIABLES HERE
  CURSOR GET_SICCODE IS
    SELECT S.CODE
      FROM SICCODE S
     WHERE S.SICCODE_ID = :NEW.SICCODE_ID;
BEGIN
  IF (:NEW.SICCODE_ID IS NOT NULL) THEN
    FOR SIC_REC IN GET_SICCODE LOOP
      IF (:NEW.SICCODE <> SIC_REC.CODE) THEN
        :NEW.SICCODE := SIC_REC.CODE;
      END IF;
    END LOOP;
  END IF;
end BEFORE_UPDATE_CUSTOMER;
/
create or replace trigger "BEFORE_UPDATE_DELIVERYPOINT" 
    BEFORE UPDATE ON DELIVERYPOINT
 FOR EACH ROW

BEGIN
      -- clear gps info for deliverypoint when changed from customer address to specific address or vice versa
      IF (:OLD.GPSLATITUDE IS NOT NULL OR :OLD.GPSLONGITUDE IS NOT NULL)
        AND (:OLD.DELIVERYADDRESS_ID <> :NEW.DELIVERYADDRESS_ID) THEN
        :NEW.GPSLATITUDE  := NULL;
        :NEW.GPSLONGITUDE := NULL;
        :NEW.GPSTIMESTAMP := NULL;
      END IF;
END BEFORE_UPDATE_DELIVERYPOINT;

/
create or replace trigger before_update_delscan
  before update on deliveryscan
  for each row

declare
  lv_planneddeliverydate uniqueitem.planneddeliverydate%type;
begin
  if (:new.routecontrol_id is null) then
    begin
      if (:new.planneddeliverydate is null) then
        begin
          select UI.PLANNEDDELIVERYDATE
            into LV_PLANNEDDELIVERYDATE
            from BUSINESSUNIT BU, UNIQUEITEM UI
           where UI.BUSINESSUNIT_ID = BU.BUSINESSUNIT_ID
             and UI.UNIQUEITEM_ID = :NEW.UNIQUEITEM_LINK_ID;
        exception
          when others then
            null;
        end;
        if (lv_planneddeliverydate is not null) then
          :new.planneddeliverydate := lv_planneddeliverydate;
        end if;
      end if;
    end;
  elsif (nvl(:old.routecontrol_id, -1) <> :new.routecontrol_id or :new.planneddeliverydate is null) then
    begin
      --use new routecontrol.drivingdate as planneddeliverydate
      select max(rc.drivingdate)
      into   :new.planneddeliverydate
      from   routecontrol rc
      where  rc.routecontrol_id = :new.routecontrol_id;
      --check garmentbatch/scan records as well
      if :new.garmentbatch_id is not null and :new.planneddeliverydate is not null then
        begin
          update garmentbatchscan gbs
          set    gbs.routecontrol_id = :new.routecontrol_id,
                 gbs.deliverydate = :new.planneddeliverydate, gbs.timestamp = sysdate,
                 gbs.systemuser_id = :new.systemuser_id
          where  gbs.routecontrol_id = :old.routecontrol_id
          and    gbs.uniqueitem_id = :new.uniqueitem_link_id
          and    gbs.garmentbatch_id = :new.garmentbatch_id
          and    gbs.scantype = 1;
          --update to lowest garmentbatchscan.deliverydate the garmentbatch.deliverydate
          update garmentbatch gb
          set    gb.deliverydate =
                  (select min(gbs.deliverydate)
                   from   garmentbatchscan gbs
                   where  gbs.garmentbatch_id = gb.garmentbatch_id), gb.timestamp = sysdate,
                 gb.systemuser_id = :new.systemuser_id
          where  gb.garmentbatch_id = :new.garmentbatch_id
          and    gb.deliverydate <>
                 (select min(gbs.deliverydate)
                   from   garmentbatchscan gbs
                   where  gbs.garmentbatch_id = gb.garmentbatch_id);
        end;
      end if;
    end;
  end if;
end before_update_delscan;
/
create or replace trigger before_update_insert_rqa
  before update or insert on rentalqtyadjustment
  for each row
  WHEN (nvl(old.recalcstatus, 0) = new.recalcstatus)
declare
  gv_tom                        product.typeofmerchandise%type;
  gv_deliverytobillrelationship product.deliverytobillrelationship%type;
  gv_deliverypercentage         productlistperdistribpoint.deliverypercentage%type;
  gv_billingpercentage          productlistperdistribpoint.billingpercentage%type;
begin
  /*
  before_update_insert_rqa version 0.2
  ABS-50995, ABS-56069 CUS - Del/bill % for non-garment items
  */
  select p.typeofmerchandise, p.deliverytobillrelationship, plpd.deliverypercentage,
         plpd.billingpercentage
  into   gv_tom, gv_deliverytobillrelationship, gv_deliverypercentage, gv_billingpercentage
  from   productlistperdistribpoint plpd
  join   product p
  on     p.product_id = plpd.product_id
  where  plpd.productlistperdistribpoint_id = :new.productlistperdistribpoint_id;
  if (gv_tom = 1 and gv_deliverytobillrelationship = 'Y' and
     (gv_deliverypercentage > 0 or gv_billingpercentage > 0)) then
    begin
      :new.recalcstatus := 1;
    end;
  else
    begin
      :new.recalcstatus := 0;
    end;
  end if;
exception
  when no_data_found then
    begin
      :new.recalcstatus := 0;
    end;
end before_update_insert_rqa;
/
create or replace trigger "BEFORE_UPDATE_INVOICEHEADER"
  BEFORE UPDATE  ON INVOICEHEADER
  FOR EACH ROW


DECLARE
  v_statusid status.status_id%type;

BEGIN

  select s.status_id
    into v_statusid
    from status s
   where s.code = 2
     and s.statustype = 'IN';

  IF :NEW.STATUS_ID = v_statusid AND :OLD.STATUS_ID <> v_statusid THEN
    :NEW.FINALIZATIONSYSTEMUSER_ID := :NEW.SYSTEMUSER_ID;
    :NEW.FINALIZATIONTIMESTAMP := SYSDATE;
  END IF;

END BEFORE_UPDATE_INVOICEHEADER;
/
create or replace trigger "BEFORE_UPDATE_PACKINGNOTE"
  BEFORE UPDATE  ON PACKINGNOTE
  FOR EACH ROW


DECLARE
  v_statusid            status.status_id%type;
  v_statusid_pknote     status.status_id%type;
  v_statusid_inprogress status.status_id%type;
  v_statusid_packed     status.status_id%type;

BEGIN

  select s.status_id
    into v_statusid
    from status s
   where s.code = 30
     and s.statustype = 'PN';

  select s.status_id
    into v_statusid_pknote
    from status s
   where s.code = 10
     and s.statustype = 'PN';

  select s.status_id
    into v_statusid_inprogress
    from status s
   where s.code = 20
     and s.statustype = 'PN';

  select s.status_id
    into v_statusid_packed
    from status s
   where s.code = 25
     and s.statustype = 'PN';

  IF :NEW.STATUS_ID = v_statusid and (:OLD.STATUS_ID = v_statusid_pknote or :OLD.STATUS_ID = v_statusid_inprogress or :OLD.STATUS_ID = v_statusid_packed) THEN
    :NEW.PROCESSEDSYSTEMUSER_ID := :NEW.SYSTEMUSER_ID;
    :NEW.PROCESSEDTIMESTAMP := :NEW.TIMESTAMP;
  END IF;

END BEFORE_UPDATE_PACKINGNOTE;
/
create or replace trigger BEFORE_UPDATE_STOCKLOCATION
  BEFORE UPDATE ON STOCKROOMLOCATION
  FOR EACH ROW
DECLARE
  -- local variables here
  LV_PLANT_ID PLANT.PLANT_ID%TYPE;
  LV_AUTOFILLUNIQUEID NUMBER := 0;
  DB_CODE DATABASESETTINGS.CODE%TYPE;
BEGIN
  -- determine plant of stockroom
  SELECT B.PLANT_ID INTO LV_PLANT_ID
  FROM STOCKROOM S, BUSINESSUNIT B
  WHERE B.BUSINESSUNIT_ID = S.MANAGEDBYBU_ID
  AND S.STOCKROOM_ID = :NEW.STOCKROOM_ID;
  -- determine value of system setting
  SELECT TO_NUMBER(S.VALUE) INTO LV_AUTOFILLUNIQUEID
  FROM SYSTEMSETTING S
  WHERE S.PLANT_ID = LV_PLANT_ID
  AND S.NAME = 'AUTOFILLLOCATIONUNIQUEID';
  -- Only when system setting AUTOFILLLOCATIONUNIQUEID = 1
  IF (LV_AUTOFILLUNIQUEID = 1) THEN
    -- Determine environment for any customized behavior
    SELECT DB.CODE INTO DB_CODE
      FROM DATABASESETTINGS DB;
    --
    IF (DB_CODE = 'DFD') THEN
      BEGIN
        IF ((:NEW.UNIQUEID IS NULL)
          OR(:NEW.UNIQUEID <> ('LP'||:NEW.AREACODE||:NEW.ROWCODE||:NEW.LOCATIONCODE))) THEN
          :NEW.UNIQUEID := 'LP'||:NEW.AREACODE||:NEW.ROWCODE||:NEW.LOCATIONCODE;
        END IF;
      END;
    ELSE
      BEGIN
        IF ((:NEW.UNIQUEID IS NULL)
          OR(:NEW.UNIQUEID <> (:NEW.AREACODE||:NEW.ROWCODE||:NEW.LOCATIONCODE))) THEN
          :NEW.UNIQUEID := :NEW.AREACODE||:NEW.ROWCODE||:NEW.LOCATIONCODE;
        END IF;
      END;
    END IF;
  END IF;
END BEFORE_UPDATE_STOCKLOCATION;
/
create or replace trigger "BEFORE_UPDATE_UNIQUEITEM"
  BEFORE UPDATE ON  uniqueitem
  FOR EACH ROW

DECLARE
  customerID      customer.customer_id%type;
  deliveryPointID deliverypoint.deliverypoint_id%type;
  gdCode          garmentdispenser.code%type;
  statusNewID     status.status_id%type;
  secondaryID     secondaryuniqueitem.secondaryid%type;
  usesecondaryID  garmentdispenser.usesecondaryid%type;

BEGIN
  if (:new.customerowned = 'Y' and :old.customerowned = 'N' and (:new.residualvalue <> 0 or nvl(:new.residualvalueamount, 0) <> 0)) then
    :new.residualvalue := 0;
    :new.residualvalueamount := 0;
  end if;

  if (:old.lastinscandate <> :new.lastinscandate) then

    if (:old.poolitem = 1) then
      BEGIN
        select d.customer_id, dp.deliverypoint_id
          into customerID, deliveryPointID
          from distributionpoint dp, department d
         where dp.department_id = d.department_id
           and dp.distributionpoint_id = :old.distributionpoint_id;
      EXCEPTION
        WHEN no_data_found THEN
          customerID := -1;
      END;

      BEGIN
        select gd.code
          into gdCode
          from GarmentDispenser GD
         where gd.customer_id = customerID
           and gd.deliverypoint_id = deliveryPointID
           and gd.usedforpool = 'Y';
      EXCEPTION
        WHEN no_data_found THEN
          gdCode := '';
      END;

      BEGIN
        select cgd.customer_id
          into customerID
          from customergarmentdispenser cgd
         where cgd.customer_id = customerID
           and cgd.interface = 6;
      EXCEPTION
        WHEN no_data_found THEN
          customerID := -1;
      END;
	secondaryID := '';

    else
      -- PoolItem <> 1

      BEGIN
        select gd.code
          into gdCode
          from uniqueitemnonpool np,
               weareremployment  we,
               garmentdispenser  gd
         where np.uniqueitem_id = :old.uniqueitem_id
           and we.weareremployment_id = np.weareremployment_id
           and gd.garmentdispenser_id = we.garmentdispenser_id;
      EXCEPTION
        WHEN no_data_found THEN
          gdCode := '';
      END;

      BEGIN
        select cgd.customer_id, gd.usesecondaryid
          into customerID, usesecondaryID
          from customergarmentdispenser cgd,
               uniqueitemnonpool        np,
               weareremployment         we,
               wearer                   w,
               garmentdispenser         gd
         where np.uniqueitem_id = :old.uniqueitem_id
           and we.weareremployment_id = np.weareremployment_id
           and w.wearer_id = we.wearer_id
           and cgd.customer_id = w.customer_id
           and gd.customer_id = cgd.customer_id
           and (gd.interface = 6 or cgd.interface = 6);
      EXCEPTION
        WHEN no_data_found THEN
          customerID := -1;
		  usesecondaryID := 'N';
      END;

      BEGIN
       select sui.secondaryid
        into secondaryID
         from secondaryuniqueitem     sui
        where sui.uniqueitem_id = :old.uniqueitem_id
          and usesecondaryID = 'Y';
      EXCEPTION
        WHEN no_data_found THEN
          secondaryID := '';
      END;

    end if;

    if (customerID > 0) then
      select s.status_id
        into statusNewID
        from status s
       where s.statustype = 'GD'
         and s.code = '10';

      insert into gdtransaction
        (gdtransaction_id,
         garmentdispensercode,
         customer_link_id,
         gdtransctiontype,
         creationdate,
         status_id,
         primaryid,
         secondaryid,
         numberofwashings,
         systemuser_id,
         timestamp,
         businessunit_id,
         changesperweek,
         personalizeditem,
         credit)
      values
        (seq_gdtransaction.nextval,
         gdCode,
         customerID,
         92,
         sysdate,
         statusNewID,
         :old.primaryid,
         secondaryID,
         :old.WASHESTOTAL,
         :new.systemuser_id,
         sysdate,
         :old.businessunit_id,
         0,
         'N',
         0);

    end if;

  end if;

END after_update_uniqueitem;
/
create or replace trigger "BEFORE_UPDATE_WEIGHINGTICKET"
  BEFORE UPDATE  ON WEIGHINGTICKET
  FOR EACH ROW

DECLARE
  v_statusid status.status_id%type;

BEGIN

  select s.status_id
    into v_statusid
    from status s
   where s.code = 30
     and s.statustype = 'WN';

  IF :NEW.STATUS_ID = v_statusid THEN
    :NEW.PROCESSEDSYSTEMUSER_ID := :NEW.SYSTEMUSER_ID;
    :NEW.PROCESSEDTIMESTAMP := getSysdateForTimezone;
  END IF;

END BEFORE_UPDATE_WEIGHINGTICKET;
/
create or replace trigger before_update_workorder
BEFORE UPDATE ON workorder
for each row
begin
  if (:NEW.processingstockroom_id <> :OLD.processingstockroom_id) THEN
    :NEW.STOCKROOMTRANSFERTIMESTAMP := SYSDATE;
    update_initialissued(:NEW.WORKORDER_ID);
  END IF;
END;
/
create or replace trigger CHECK_BOXNR_AFTER_CREATE_CDN
AFTER INSERT ON CLIENTDELIVERYNOTE
FOR EACH ROW

DECLARE
pragma autonomous_transaction;
  counter NUMBER;
  ex_custom EXCEPTION;
  PRAGMA EXCEPTION_INIT(ex_custom, -20001);

BEGIN
  IF :new.boxnumber > 0 THEN
    SELECT COUNT(*)
    INTO counter
    FROM CLIENTDELIVERYNOTE cdn
    WHERE cdn.BATCH_ID = :new.batch_id
    AND cdn.boxnumber = :new.boxnumber
    AND cdn.client_link_id <> :new.client_link_id
		AND cdn.presortnumber = :new.presortnumber;
  END IF;

  IF counter > 0 THEN
    RAISE_APPLICATION_ERROR( -20001,'CHECK_BOXNR_AFTER_CREATE_CDN: You tried to insert a client delivery note with same box number as one used by a different client in the same batch!' );
  END IF;

  END;
/
create or replace trigger contractusage_update_0
  after insert on contractspercustomer
   for each row
begin
  update customer
  set customer.contractusage = 0
  where customer.customer_id = :new.customer_id;
end contractusage_update_0;
/
create or replace trigger contractusage_update_1
  after insert on contractcustomer
  for each row
begin
  update customer
  set customer.contractusage = 1
  where customer.customer_id = :new.customer_link_id;
end contractusage_update_1;
/
create or replace trigger CUSTOMER_CHECK_CONTRACT_USAGE
    BEFORE
    UPDATE ON customer
    FOR EACH ROW






DECLARE
  V_exists Integer;
BEGIN
  IF :new.ContractUsage = 0 THEN
     V_exists:=0;
     select count(1) INTO v_exists From contractcustomer c Where c.customer_link_id = :new.customer_id
                                                                and c.enddate > sysdate  ;
     IF v_exists >0 THEN
       RAISE_APPLICATION_ERROR(-20007, 'Customer has active contracts of type predefined');
     END IF;
  END IF;

  IF :new.ContractUsage = 1 THEN
     V_exists:=0;
     select count(1) INTO v_exists From contractspercustomer c Where c.customer_id = :new.customer_id ;
     IF v_exists >0 THEN
       RAISE_APPLICATION_ERROR(-20008, 'Customer has contracts of type single contract');
     END IF;
  END IF;
END;
/
create or replace trigger customer_coc_length_check
  BEFORE UPDATE ON systemsetting
  FOR EACH ROW
WHEN ((NEW.NAME = 'COCDEFINITION') AND (TO_NUMBER(NEW.VALUE) < TO_NUMBER(OLD.VALUE)))
DECLARE
  -- LOCAL VARIABLES HERE
BEGIN
    RAISE_APPLICATION_ERROR(-20004,
                            'This might lead to problems, because the old length '||to_char(:old.value * 10)
                            ||' was greater than the new length '||to_char(:new.value * 10)||'!');
END customer_coc_length_check;
/
create or replace trigger customer_has_contractcustomer
    BEFORE
    INSERT ON contractcustomer
    FOR EACH ROW

DECLARE
  V_exists Integer;
 BEGIN
      V_exists:=0;
    select count(1) INTO v_exists From contractspercustomer c Where c.customer_id = :new.customer_link_id ;
 IF v_exists >0 THEN
    RAISE_APPLICATION_ERROR(-20005, 'Customer has defined contracts of type single contract');
  END IF;
END;
/
create or replace trigger customer_has_contractspercust
    BEFORE
    INSERT ON contractspercustomer
    FOR EACH ROW

DECLARE
  V_exists Integer;
 BEGIN
      V_exists:=0;
    select count(1) INTO v_exists From contractcustomer c Where c.customer_link_id = :new.customer_id
                                                                and c.enddate > sysdate  ;
 IF v_exists >0 THEN
    RAISE_APPLICATION_ERROR(-20006, 'Customer has defined contracts of type predefined');
  END IF;
END;
/
create or replace trigger customer_name_length_check
  BEFORE UPDATE ON systemsetting
  FOR EACH ROW

WHEN ((new.name = 'CUSTOMERNAMELENGTH') AND (to_number(new.value) < to_number(old.value)))
DECLARE
  -- LOCAL VARIABLES HERE
  v_customer_code databasesettings.code%TYPE;
BEGIN
    v_customer_code := getDatabaseCode;

    IF (v_customer_code = 'LST') THEN
      IF (:old.value != 40) THEN
        RAISE_APPLICATION_ERROR(-20004,
                               'This might lead to problems, because the old length '||:old.value
                               ||' was greater than the new length '||:new.value||'!');
      END IF;
    ELSE
        RAISE_APPLICATION_ERROR(-20004,
                                'This might lead to problems, because the old length '||:old.value
                                ||' was greater than the new length '||:new.value||'!');
    END IF;
END customer_name_length_check;
/
create or replace trigger customer_remark_length_check
  BEFORE UPDATE ON systemsetting
  FOR EACH ROW

WHEN ((new.name = 'CUSTOMERREMARKLENGTH') and (to_number(new.value) < to_number(old.value)))
DECLARE
  -- LOCAL VARIABLES HERE
BEGIN
    RAISE_APPLICATION_ERROR(-20004,
                            'This might lead to problems, because the old length '||:old.value
                            ||' was greater than the new length '||:new.value||'!');
END customer_remark_length_check;
/
create or replace trigger debtor_has_BillInfo_check
    BEFORE
    --INSERT OR UPDATE OF debtor_id ON customer
    UPDATE OF debtor_id ON customer
    FOR EACH ROW

WHEN (new.customer_id =  new.debtor_id)
DECLARE
  V_exists VARCHAR2(1);
  cursor cust_cur(b_id1 IN NUMBER) IS
    select 'Y' From customerbillinginfo Where customer_id = b_id1;
BEGIN
  OPEN cust_cur(b_id1 => :new.debtor_id);
  FETCH cust_cur
    INTO v_exists;
  CLOSE cust_cur;
------O POVESTE
  IF v_exists IS NULL THEN
    RAISE_APPLICATION_ERROR(-20004,
                            'Debtor must have billing info defined');
  END IF;
END;
/
create or replace trigger delto_has_not_BillInfo_check
    BEFORE
    INSERT OR UPDATE OF debtor_id ON customer
    FOR EACH ROW

WHEN (new.customer_id <>  new.debtor_id)
DECLARE
  V_exists VARCHAR2(1);
  cursor cust_cur(b_id1 IN NUMBER) IS
    select 'Y' From customerbillinginfo Where customer_id = b_id1;
BEGIN
  OPEN cust_cur(b_id1 => :new.customer_id);
  FETCH cust_cur
    INTO v_exists;
  CLOSE cust_cur;

  IF v_exists IS NOT NULL THEN
    RAISE_APPLICATION_ERROR(-20004,
                            'Del to customer must not have billing info');
  END IF;
END;
/
create or replace trigger FREEOFCHARGEFREQUENCY
    AFTER
   --  UPDATE OF spliInvoices ON customer
   UPDATE OF SplitInvoices ON customer for each row
  declare splita number;
  cpl varchar2(1);
BEGIN
 select ss.value into cpl
 from  businessunit bu, plant p, systemsetting ss
 where  bu.plant_id=p.plant_id
       and p.plant_id=ss.plant_id
       and ss.name='FREEOFCHARGEINCPLPERDATE'
       and bu.businessunit_id=:new.defaultbusinessunit_id;
  splita:=:new.splitinvoices;
  if splita in (2, 3, 4, 5) then splita:=1; end if;
  if cpl='Y' then
      update productlistperdistribpoint plpd
         set plpd.freeqtyfrequency = splita
         where plpd.productlistperdistribpoint_id in
            (select plpd.productlistperdistribpoint_id from productlistperdistribpoint plpd, distributionpoint dp, department dep
            where plpd.distributionpoint_id=dp.distributionpoint_id
            and dp.department_id=dep.department_id
            and plpd.freeqtyfrequency <> 2
            and dep.customer_id=:new.customer_id);
  End if;
END;
/
create or replace trigger FREEOFCHARGEFREQUENCY_PLPC
    AFTER
   UPDATE OF invoicefreqrelatedrentprice ON productlistpercustomer for each row

declare invfreqrelatedrentprice varchar2(1);
  cpl varchar2(1);
  freeqtyfreq number;
BEGIN

 select ss.value into cpl
 from  businessunit bu, plant p, systemsetting ss, customer c
 where  bu.plant_id=p.plant_id
       and p.plant_id=ss.plant_id
       and ss.name='FREEOFCHARGEINCPLPERDATE'
       and bu.businessunit_id=c.defaultbusinessunit_id
       and c.customer_id=:new.customer_id;
  invfreqrelatedrentprice:=:new.invoicefreqrelatedrentprice;
  if invfreqrelatedrentprice='Y' then
    freeqtyfreq:=0;
  else
    freeqtyfreq:=1;
  end if;
  if cpl='Y' then
      update productlistperdistribpoint plpd
         set plpd.freeqtyfrequency = freeqtyfreq
         where  plpd.productlistperdistribpoint_id in
            (select sdl.productlistperdistribpoint_id from standarddeliveryline sdl
            where sdl.productlistpercustomer_id=:new.productlistpercustomer_id);
  End if;
END;
/
create or replace trigger opportunity_length_check
  BEFORE UPDATE ON systemsetting
  FOR EACH ROW
WHEN ((NEW.NAME = 'OPPORTUNITYIDMAXLENGTH') AND (TO_NUMBER(NEW.VALUE) < TO_NUMBER(OLD.VALUE)))
DECLARE
  -- LOCAL VARIABLES HERE
BEGIN
    RAISE_APPLICATION_ERROR(-20004,
                            'This might lead to problems, because the old length '||:old.value
                            ||' was greater than the new length '||:new.value||'!');
END opportunity_length_check;
/
create or replace trigger trg_address_streetlength_check
  before insert or update on address
  for each row
declare
  --trg_address_streetlength_check version 0.1
  gv_value systemsetting.value%type;
begin
  select max(st.value) into gv_value from systemsetting st where st.name = 'STREETMAXLENGTH';
  if nvl(gv_value, 0) = 0 then
    begin
      if length(:new.street) > 30 then
        begin
          :new.street := substr(trim(:new.street), 1, 30);
        end;
      end if;
      if length(:new.street2) > 30 then
        begin
          :new.street2 := substr(trim(:new.street2), 1, 30);
        end;
      end if;
      if length(:new.street3) > 30 then
        begin
          :new.street3 := substr(trim(:new.street3), 1, 30);
        end;
      end if;
    end;
  end if;
end trg_address_streetlength_check;
/
create or replace trigger trg_chk_employee_unique
  AFTER UPDATE ON databasesettings
  FOR EACH ROW
  WHEN (OLD.overalluniqueemployee <> NEW.overalluniqueemployee)
DECLARE
  lv_count NUMBER;
BEGIN
  --trg_chk_employee_unique version 1.0
  IF :new.overalluniqueemployee = 'Y' THEN
    BEGIN
      SELECT count(1)
        INTO lv_count
        FROM (SELECT e.employeenumber
                FROM employee e
               GROUP BY e.employeenumber
              HAVING count(1) > 1) x;
      IF lv_count > 0 THEN
        BEGIN
          --there are employee records  that have the same EMPLOYEENUMBER  for
          --different plants   so we cannot set overalluniquemployee to Y
          Raise_application_error(-20144,
                                  'overalluniquemployee cannot be set to unique!! Because there are ' ||
                                  lv_count ||
                                  ' duplicate employee numbers !');
        END;
      ELSE
        BEGIN
          --check if the index consists of more then 1 column

          SELECT count(1)
            INTO lv_count
            FROM user_indexes t
           WHERE t.index_name = 'XIE1EMPLOYEE'
             AND t.uniqueness = 'UNIQUE';
          IF lv_count = 0 THEN
            BEGIN
              --raise application error that the index is not unique!!
              Raise_application_error(-20144,
                                      '-->Please run the scripts to make the index XIE1EMPLOYEE UNIQUE');
            END;
          END IF;
        END;
      END IF;
    END;
  ELSE
    BEGIN
      --check if the index consists of more then 1 column

      SELECT Count(1)
        INTO lv_count
        FROM user_indexes t
       WHERE t.index_name = 'XIE1EMPLOYEE'
         AND t.uniqueness = 'UNIQUE';
      IF lv_count > 0 THEN
        BEGIN
          --we warn in the log to update the unique key XIE1EMPLOYEE to NOT unique
          Raise_application_error(-20144,
                                  '-->Please run the scripts to make the index XIE1EMPLOYEE NOT unique');
        END;
      END IF;
    END;
  END IF;
END trg_chk_employee_unique;
/
create or replace trigger trg_chk_uniqueitem_unique
  after update on databasesettings
  for each row
  WHEN (nvl (old.overalluniquegarmentid, 'N') <> nvl (new.overalluniquegarmentid, 'N'))
declare
  lv_count number;
begin
  --trg_chk_uniqueitem_unique version 1.1
  if :new.overalluniquegarmentid = 'Y' then
    begin
      select count(1)
      into   lv_count
      from   (select u.primaryid from uniqueitem u group by u.primaryid having count(1) > 1) x;
      if lv_count > 0 then
        begin
          --there are uniqueitems that have the same PRIMARYID for
          --different businessunits so we cannot set overaluniquegarment to Y
          raise_application_error(-20144,
                                  'Overalluniquegarmentid cannot be set to unique!! Because there are ' ||
                                   lv_count || ' duplciate uniqueitems !');
        end;
      else
        begin
          --check if the index consists of more then 1 column
          select count(1)
          into   lv_count
          from   user_indexes t
          where  t.index_name = 'XIE1UNIQUEITEM'
          and    t.uniqueness = 'UNIQUE';
          if lv_count = 0 then
            begin
              --raise application error that the index is not unique!!
              raise_application_error(-20144,
                                      '-->Please run the scripts to make the index XIE1UNIQUEITEM UNIQUE');
            end;
          end if;
        end;
      end if;
    end;
  else
    begin
      --check if the index consists of more then 1 column
      select count(1)
      into   lv_count
      from   user_indexes t
      where  t.index_name = 'XIE1UNIQUEITEM'
      and    t.uniqueness = 'UNIQUE';
      if lv_count > 0 then
        begin
          --we warn in the log to update the unique key XIE1UNIQUEITEM to NOT unique
          raise_application_error(-20144,
                                  '-->Please run the scripts to make the index XIE1UNIQUEITEM NOT unique');
        end;
      end if;
    end;
  end if;
end trg_chk_uniqueitem_unique;
/
create or replace trigger trg_outscan
  before insert on scan
  for each row

declare
  lv_syssetting          systemsetting.value%type;
  lv_plantid             plant.plant_id%type;
  lv_planneddeliverydate uniqueitem.planneddeliverydate%type;
  lv_uir_planneddate      uniqueitemroute.plannedoutdate%type;
begin
  if :new.transactiontype_id = 2 then
    begin
      --trigger trg_outscan version 0.1
      --outscan
      select bu.plant_id, ui.planneddeliverydate
      into lv_plantid, lv_planneddeliverydate
      from businessunit bu, uniqueitem ui
      where ui.businessunit_id = bu.businessunit_id
      and ui.uniqueitem_id = :new.uniqueitem_link_id;

      begin
        select coalesce(uir.plannedoutdate, uir.lastoutdate)
        into lv_uir_planneddate
        from uniqueitemroute uir
        where uir.uniqueitem_id = :new.uniqueitem_link_id;
      exception when no_data_found then
        lv_uir_planneddate := lv_planneddeliverydate;
      end;

    if (coalesce(lv_uir_planneddate, lv_planneddeliverydate) is not null and coalesce(lv_uir_planneddate, lv_planneddeliverydate) >=trunc(sysdate))
        then
          begin

      update /*+ INDEX(ds XIE3DELIVERYSCAN)*/ deliveryscan ds set ds.planneddeliverydate = coalesce(lv_uir_planneddate, lv_planneddeliverydate)
      where ds.uniqueitem_link_id = :new.uniqueitem_link_id
      and ds.planneddeliverydate is null
      and nvl(ds.packingnote_id, 0) = 0;
     end;
      end if;

      select ss.value
      into lv_syssetting
      from systemsetting ss
      where ss.name = 'PROCESSDELIVERYSCAN'
      and ss.plant_id = lv_plantid;

      if lv_syssetting = 1 then
        begin
          delete /*+ INDEX(ds XIE3DELIVERYSCAN)*/ from deliveryscan ds
          where ds.uniqueitem_link_id = :new.uniqueitem_link_id
          and nvl(ds.packingnote_id, 0) = 0
          and ds.outscannodelivery in (0, 2)
          and ds.timestamp < (select max(a.timestamp) from deliveryscan a where a.uniqueitem_link_id = ds.uniqueitem_link_id);
       end;
      end if;

    end;
  end if;
end trg_outscan;
/
create or replace trigger trg_temp_gln_customer
  after update on customer
  for each row
begin
  --trigger trg_temp_gln_customer version 0.2
  update customergeneral cl
  set    cl.globallocationnumber = get_flexfield_string(cl.customer_id, 'Customer', 'GLN')
  where  cl.customer_id = :new.customer_id;
  if sql%rowcount = 0 then
    begin
      insert into customergeneral
        (customer_id, globallocationnumber, systemuser_id, timestamp)
      values
        (:new.customer_id, get_flexfield_string(:new.customer_id, 'Customer', 'GLN'),
         :new.systemuser_id, sysdate);
    end;
  end if;
end trg_temp_gln_customer;
/
create or replace trigger trg_validate_packcontents
  before update on packcontents
  for each row

WHEN ((old.rownumber is null and new.rownumber is not null) or
       (old.packsequence is null and new.packsequence is not null))
begin
  if :old.rownumber is null and :new.rownumber is not null then
    :new.packsequence := null; --row location is used
  end if;
  if :old.packsequence is null and :new.packsequence is not null then
    begin
      :new.rownumber      := null; --sequence is used
      :new.positionnumber := null;
    end;
  end if;
end trg_validate_packcontents;
/
create or replace trigger webordergracetime_check
  BEFORE UPDATE ON systemsetting
  FOR EACH ROW
WHEN ((NEW.NAME = 'WEBORDERGRACETIME') AND (MOD(NEW.VALUE,5)!=0) )
DECLARE
  -- LOCAL VARIABLES HERE
BEGIN
  -- if the value is not a multiple of 5 it will be rounded to the next multiple
  :new.value := (ceil(:new.value/5)*5);
END webordergracetime_check;
/

