--todo 9311

update productlistpercustomer l
set    l.deedeliveryscheme_id = null
where  l.deedeliveryscheme_id is not null
and    l.delayedevenexchange <> 7;

commit;

alter table productlistpercustomer add constraint chk_plpc_ddedeliveryid check((deedeliveryscheme_id is null and
                                                                                delayedevenexchange <> 7) or
                                                                               (delayedevenexchange = 7));

insert into DBCONSISTENCYCHECK
      (DBCONSISTENCYCHECK_ID,
       DESCRIPTION,
       SQLSTATEMENT,
       ERRORMESSAGE,
       EXACTLYONEROW,
       ADDITIONALERRORMESSAGE,
       ACTIVE)
values
      (SEQ_DBCONSISTENCYCHECK.nextval,
       'Check if exists records in PLPC with Delayedevenexchange = 7 and Deedeliveryscheme_id = null',
       'select c.customernumber, p.code productcode
        from   productlistpercustomer l, customer c, product p
        where  l.deedeliveryscheme_id is null
        and    l.delayedevenexchange = 7
        and    l.customer_id = c.customer_id
        and    l.product_id = p.product_id
        order  by 1, 2',
       'Delayedevenexchange = 7 and deedeliveryscheme_id = null is not allowed, for the following customer, product combinations!',
       'Y',
       null,
       'Y');
