--order 502689

delete DBCONSISTENCYCHECK;

insert into DBCONSISTENCYCHECK
      (DBCONSISTENCYCHECK_ID,
       DESCRIPTION,
       SQLSTATEMENT,
       ERRORMESSAGE,
       EXACTLYONEROW,
       ADDITIONALERRORMESSAGE,
       ACTIVE)
    values
      (1,
       'All debtors have CustomerBillingInfo records',
       'select d.customer_id debtor_id, d.customernumber debtornumber, c.customer_id customer_id, c.customernumber customernumber' ||
       chr(10) || 'from customer c, customer d' || chr(10) ||
       'where c.debtor_id = d.customer_id' || chr(10) ||
       'and not exists(select b.customer_id from customerbillinginfo b where d.customer_id= b.customer_id)',
       'There are debtors without CustomerBillingInfo records!',
       'N',
       null,
       'Y');
  
    insert into DBCONSISTENCYCHECK
      (DBCONSISTENCYCHECK_ID,
       DESCRIPTION,
       SQLSTATEMENT,
       ERRORMESSAGE,
       EXACTLYONEROW,
       ADDITIONALERRORMESSAGE,
       ACTIVE)
    values
      (2,
       'Invoice address does not exists for Customer or Debtor ',
       'select c.customer_id customerid, c.customernumber customernumber,' ||
       chr(10) || ' d.customer_id debtorid, d.customernumber debtornumber' ||
       chr(10) || 'from customer c, customer d' || chr(10) ||
       'where c.debtor_id = d.customer_id' || chr(10) ||
       'and c.invoiceaddress_id is  null' || chr(10) ||
       'and d.invoiceaddress_id is  null',
       'There should be at least one invoice address defined for customer and debtor!',
       'N',
       'Please define invoiceaddress on customer level or debtor level!',
       'Y');
  
    insert into DBCONSISTENCYCHECK
      (DBCONSISTENCYCHECK_ID,
       DESCRIPTION,
       SQLSTATEMENT,
       ERRORMESSAGE,
       EXACTLYONEROW,
       ADDITIONALERRORMESSAGE,
       ACTIVE)
    values
      (3,
       'Customers that are not debtors don''t have CustomerBillingInfo records',
       'select c.customer_id customerid, c.customernumber customernumber, c.debtor_id debtorid' ||
       chr(10) || 'from customer c, customerbillinginfo b' || chr(10) ||
       'where c.customer_id = b.customer_id' || chr(10) ||
       'and not exists (select cc.customer_id from customer cc, customer d' ||
       chr(10) || 'where cc.debtor_id = d.customer_id' || chr(10) ||
       'and cc.debtor_id = c.customer_id)',
       'Customers that are not debtors should not have CustomerBillingInfo records',
       'N',
       null,
       'Y');
  
    insert into DBCONSISTENCYCHECK
      (DBCONSISTENCYCHECK_ID,
       DESCRIPTION,
       SQLSTATEMENT,
       ERRORMESSAGE,
       EXACTLYONEROW,
       ADDITIONALERRORMESSAGE,
       ACTIVE)
    values
      (4,
       'Products from productlistpercustomer has corresponding records in productgroups per customer',
       'select a.productlistpercustomer_id, a.product_id, b.code, b.productgroup_id' ||
       chr(10) || 'from productlistpercustomer a, product b' || chr(10) ||
       'where a.product_id = b.product_id ' || chr(10) ||
       ' and not exists (select pgpc.productgroup_id ' || chr(10) ||
       'from productgrouppercustomer pgpc ' || chr(10) ||
       'where pgpc.customer_id = a.customer_id and ' || chr(10) ||
       'pgpc.productgroup_id = b.productgroup_id) ',
       'There are productlistpercustomer records with products that do not have productgroups in productgrouppercustomer table',
       'N',
       null,
       'Y');
  
    insert into DBCONSISTENCYCHECK
      (DBCONSISTENCYCHECK_ID,
       DESCRIPTION,
       SQLSTATEMENT,
       ERRORMESSAGE,
       EXACTLYONEROW,
       ADDITIONALERRORMESSAGE,
       ACTIVE)
    values
      (5,
       'Correspondence plpc billingmethod with type of merchandise',
       'select a.productlistpercustomer_id,(select cu.customernumber  from customer cu where cu.customer_id = a.customer_id) customernumber,
        a.billingmethod, a.changesperweek, b.product_id, b.code,
        (select sz.code from sizedefinition sz where sz.sizedefinition_id = a.sizedefinition_id) SizedefinitionCode,
        (select fm.code from finishingmethod fm where fm.finishingmethod_id = a.finishingmethod_id) finishingmethodCode,
        b.typeofmerchandise, a.dateactive, a.dateinactive' ||
       chr(10) || 'from productlistpercustomer a, product b' || chr(10) ||
       'where a.product_id = b.product_id' || chr(10) ||
       'and not exists (select plpc.productlistpercustomer_id' || chr(10) ||
       'from productlistpercustomer plpc, product p' || chr(10) ||
       'where plpc.customer_id = a.customer_id and ' || chr(10) ||
       'plpc.product_id = a.product_id and ' || chr(10) ||
       ' plpc.dateactive = a.dateactive and ' || chr(10) ||
       ' plpc.dateinactive = a.dateinactive and ' || chr(10) ||
       ' plpc.product_id = p.product_id' || chr(10) || 'and (' || chr(10) ||
       '(plpc.billingmethod = 1 and ' || chr(10) || '(' || chr(10) ||
       '(plpc.changesperweek = ''N''and p.typeofmerchandise in(1,2)) or' ||
       chr(10) ||
       '(plpc.changesperweek = ''Y''and p.typeofmerchandise = 2)' ||
       chr(10) || ')' || chr(10) || ')' || chr(10) ||
       'or (plpc.billingmethod = 2 and p.typeofmerchandise in (1,2))' ||
       chr(10) ||
       'or (plpc.billingmethod = 3 and p.typeofmerchandise in (1,2))' ||
       chr(10) ||
       'or (plpc.billingmethod = 4 and p.typeofmerchandise in (1,2,3,5))' ||
       chr(10) || 'or (plpc.billingmethod = 5 and (' || chr(10) ||
       '(plpc.changesperweek = ''N''and p.typeofmerchandise in(1,2)) or' ||
       chr(10) ||
       '(plpc.changesperweek = ''Y''and p.typeofmerchandise = 2)' ||
       chr(10) || '))' || chr(10) || '))',
       'There are wrong combination of billingmethod with type of merchandise in product list per customer records!',
       'N',
       null,
       'Y');
  
    insert into DBCONSISTENCYCHECK
      (DBCONSISTENCYCHECK_ID,
       DESCRIPTION,
       SQLSTATEMENT,
       ERRORMESSAGE,
       EXACTLYONEROW,
       ADDITIONALERRORMESSAGE,
       ACTIVE)
    values
      (6,
       'CUSTOMER_LINK table is correctly filled',
       'select customernumber, name custName, customer_id, timestamp' ||
       chr(10) || 'from customer a' || chr(10) ||
       'where not exists (select x.customer_id from customer_link x ' ||
       chr(10) || '                  where x.customer_id = a.customer_id)' ||
       chr(10) || 'order by 1' || chr(9) || '' || chr(9) || '' || chr(9) || '' ||
       chr(9) || '' || chr(9) || '' || chr(9) || '' || chr(9) || '' ||
       chr(9) || '' || chr(9) || '',
       'There are records in CUSTOMER table with no related record in CUSTOMER_LINK table!',
       'N',
       null,
       'Y');
  
    insert into DBCONSISTENCYCHECK
      (DBCONSISTENCYCHECK_ID,
       DESCRIPTION,
       SQLSTATEMENT,
       ERRORMESSAGE,
       EXACTLYONEROW,
       ADDITIONALERRORMESSAGE,
       ACTIVE)
    values
      (7,
       'DEPARTMENT_LINK table is correctly filled',
       'select b.customernumber, b.name customerName, departmentnumber, a.description departmentDesc, department_id, a.timestamp' ||
       chr(10) || 'from department a, customer b' || chr(10) ||
       'where a.customer_id = b.customer_id and' || chr(10) ||
       '      not exists (select x.department_id from department_link x where x.department_id = a.department_id)' ||
       chr(10) || 'order by 1, 3',
       'There are records in DEPARTMENT table with no related record in DEPARTMENT_LINK table!',
       'N',
       null,
       'Y');
  
    insert into DBCONSISTENCYCHECK
      (DBCONSISTENCYCHECK_ID,
       DESCRIPTION,
       SQLSTATEMENT,
       ERRORMESSAGE,
       EXACTLYONEROW,
       ADDITIONALERRORMESSAGE,
       ACTIVE)
    values
      (8,
       'DISTRIBUTIONPOINT_LINK table is correctly filled',
       'select customernumber, c.name customerName, departmentnumber, distributionpointnumber, distributionpoint_id, a.timestamp' ||
       chr(10) || 'from distributionpoint a, department b, customer c' ||
       chr(10) || 'where a.department_id = b.department_id and' || chr(10) ||
       '      b.customer_id = c.customer_id and' || chr(10) ||
       '      not exists (select x.distributionpoint_id from distributionpoint_link x where x.distributionpoint_id = a.distributionpoint_id)' ||
       chr(10) || 'order by 1, 3, 4',
       'There are records in DISTRIBUTIONPOINT table with no related record in DISTRIBUTIONPOINT_LINK table!',
       'N',
       null,
       'Y');
  
    insert into DBCONSISTENCYCHECK
      (DBCONSISTENCYCHECK_ID,
       DESCRIPTION,
       SQLSTATEMENT,
       ERRORMESSAGE,
       EXACTLYONEROW,
       ADDITIONALERRORMESSAGE,
       ACTIVE)
    values
      (9,
       'COSTCENTER_LINK table is correctly filled',
       'select customernumber, c.name customerName, a.code costCenterCode, a.description costCenterDesc, costcenter_id, a.timestamp' ||
       chr(10) || 'from costcenter a, customer c' || chr(10) ||
       'where a.customer_id = c.customer_id and' || chr(10) ||
       '      not exists (select x.costcenter_id from costcenter_link x where a.costcenter_id = x.costcenter_id)' ||
       chr(10) || 'order by 1, 3',
       'There are records in COSTCENTER table with no related record in COSTCENTER_LINK table!',
       'N',
       null,
       'Y');
  
    insert into DBCONSISTENCYCHECK
      (DBCONSISTENCYCHECK_ID,
       DESCRIPTION,
       SQLSTATEMENT,
       ERRORMESSAGE,
       EXACTLYONEROW,
       ADDITIONALERRORMESSAGE,
       ACTIVE)
    values
      (10,
       'DELIVERYPOINT_LINK table is correctly filled',
       'select customernumber, c.name customerName, a.code deliveryPointCode, a.description deliveryPointDesc, deliverypoint_id, a.timestamp' ||
       chr(10) || 'from deliverypoint a, customer c' || chr(10) ||
       'where a.customer_id = c.customer_id and' || chr(10) ||
       '      not exists (select x.deliverypoint_id from deliverypoint_link x where a.deliverypoint_id = x.deliverypoint_id)' ||
       chr(10) || 'order by 1, 3',
       'There are records in DELIVERYPOINT table with no related record in DELIVERYPOINT_LINK table!',
       'N',
       null,
       'Y');
  
    insert into DBCONSISTENCYCHECK
      (DBCONSISTENCYCHECK_ID,
       DESCRIPTION,
       SQLSTATEMENT,
       ERRORMESSAGE,
       EXACTLYONEROW,
       ADDITIONALERRORMESSAGE,
       ACTIVE)
    values
      (11,
       'PRODUCT_LINK table is correctly filled',
       'select a.code productCode, product_id, a.timestamp' || chr(10) ||
       'from product a' || chr(10) ||
       'where not exists (select x.product_id from product_link x where x.product_id = a.product_id)' ||
       chr(10) || 'order by 1',
       'There are records in PRODUCT table with no related record in PRODUCT_LINK table!',
       'N',
       null,
       'Y');
  
    insert into DBCONSISTENCYCHECK
      (DBCONSISTENCYCHECK_ID,
       DESCRIPTION,
       SQLSTATEMENT,
       ERRORMESSAGE,
       EXACTLYONEROW,
       ADDITIONALERRORMESSAGE,
       ACTIVE)
    values
      (12,
       'SIZEDEFINITION_LINK table is correctly filled',
       'select code sizeDefinitionCode, sizedefinition_id, timestamp' ||
       chr(10) || 'from sizedefinition a' || chr(10) ||
       'where not exists (select x.sizedefinition_id from sizedefinition_link x where x.sizedefinition_id = a.sizedefinition_id)' ||
       chr(10) || 'order by 1',
       'There are records in SIZEDEFINITION table with no related record in SIZEDEFINITION_LINK table!',
       'N',
       null,
       'Y');
  
    insert into DBCONSISTENCYCHECK
      (DBCONSISTENCYCHECK_ID,
       DESCRIPTION,
       SQLSTATEMENT,
       ERRORMESSAGE,
       EXACTLYONEROW,
       ADDITIONALERRORMESSAGE,
       ACTIVE)
    values
      (13,
       'WEAREREMPLOYMENT_LINK table is correctly filled',
       'select customernumber, c.name customerName, wearernumber, b.fullname wearerName, a.sequencenumber employment, weareremployment_id, a.timestamp' ||
       chr(10) || 'from weareremployment a, wearer b, customer c' ||
       chr(10) || 'where a.wearer_id = b.wearer_id and' || chr(10) || '' ||
       chr(9) || '' || chr(9) || '' || chr(9) ||
       'b.customer_id = c.customer_id and' || chr(10) ||
       '      not exists (select x.weareremployment_id from weareremployment_link x where x.weareremployment_id = a.weareremployment_id)' ||
       chr(10) || 'order by 1, 3, 5',
       'There are records in WEAREREMPLOYMENT table with no related record in WEAREREMPLOYMENT_LINK table!',
       'N',
       null,
       'Y');
  
    insert into DBCONSISTENCYCHECK
      (DBCONSISTENCYCHECK_ID,
       DESCRIPTION,
       SQLSTATEMENT,
       ERRORMESSAGE,
       EXACTLYONEROW,
       ADDITIONALERRORMESSAGE,
       ACTIVE)
    values
      (14,
       'WEIGHINGCATEGORY_LINK table is correctly filled',
       'select code weighingCategoryCode, weighingcategory_id, timestamp' ||
       chr(10) || 'from weighingcategory a' || chr(10) ||
       'where not exists (select x.weighingcategory_id from weighingcategory_link x where x.weighingcategory_id = a.weighingcategory_id)' ||
       chr(10) || 'order by 1',
       'There are records in WEIGHINGCATEGORY table with no related record in WEIGHINGCATEGORY_LINK table!',
       'N',
       null,
       'Y');
  
    insert into DBCONSISTENCYCHECK
      (DBCONSISTENCYCHECK_ID,
       DESCRIPTION,
       SQLSTATEMENT,
       ERRORMESSAGE,
       EXACTLYONEROW,
       ADDITIONALERRORMESSAGE,
       ACTIVE)
    values
      (15,
       'CLIENT_LINK table is correctly filled',
       'select customernumber, b.name customerName, clientnumber, a.name clientName, client_id, a.timestamp' ||
       chr(10) || 'from client a, customer b' || chr(10) ||
       'where a.customer_id = b.customer_id and' || chr(10) ||
       '      not exists (select x.client_id from client_link x where x.client_id = a.client_id)' ||
       chr(10) || 'order by 1, 3',
       'There are records in CLIENT table with no related record in CLIENT_LINK table!',
       'N',
       null,
       'Y');
  
    insert into DBCONSISTENCYCHECK
      (DBCONSISTENCYCHECK_ID,
       DESCRIPTION,
       SQLSTATEMENT,
       ERRORMESSAGE,
       EXACTLYONEROW,
       ADDITIONALERRORMESSAGE,
       ACTIVE)
    values
      (16,
       'Every consumption point has at least one standard delivery record',
       'select customernumber, name customername, departmentnumber, distributionpointnumber' ||
       chr(10) || 'from distributionpoint d, department dep, customer c' ||
       chr(10) || 'where d.department_id = dep.department_id and' ||
       chr(10) || '      dep.customer_id = c.customer_id and' || chr(10) ||
       '      not exists (select s.standarddelivery_id from standarddelivery s where s.distributionpoint_id = d.distributionpoint_id)' ||
       chr(10) || 'order by 1, 3, 4',
       'There ate consumption points with no standard delivery!',
       'N',
       null,
       'Y');
  
    insert into DBCONSISTENCYCHECK
      (DBCONSISTENCYCHECK_ID,
       DESCRIPTION,
       SQLSTATEMENT,
       ERRORMESSAGE,
       EXACTLYONEROW,
       ADDITIONALERRORMESSAGE,
       ACTIVE)
    values
      (17,
       'Every customer has at least one department',
       'select customernumber, c.name customerName' || chr(10) ||
       'from customer c ' || chr(10) ||
       'where not exists (select d.department_id from department d where d.customer_id = c.customer_id)' ||
       chr(10) || 'order by 1',
       'There are customers with no department!',
       'N',
       null,
       'Y');
  
    insert into DBCONSISTENCYCHECK
      (DBCONSISTENCYCHECK_ID,
       DESCRIPTION,
       SQLSTATEMENT,
       ERRORMESSAGE,
       EXACTLYONEROW,
       ADDITIONALERRORMESSAGE,
       ACTIVE)
    values
      (18,
       'Every department has at least one consumption point',
       'select customernumber, c.name customerName, departmentnumber' ||
       chr(10) || 'from customer c, department d' || chr(10) ||
       'where c.customer_id = d.customer_id and' || chr(10) ||
       '      not exists (select dip.distributionpoint_id from distributionpoint dip ' ||
       chr(10) ||
       '                  where dip.department_id = d.department_id)' ||
       chr(10) || 'order by 1, 3',
       'There are departments with no consumption point!',
       'N',
       null,
       'Y');
  
    insert into DBCONSISTENCYCHECK
      (DBCONSISTENCYCHECK_ID,
       DESCRIPTION,
       SQLSTATEMENT,
       ERRORMESSAGE,
       EXACTLYONEROW,
       ADDITIONALERRORMESSAGE,
       ACTIVE)
    values
      (19,
       'One default quality grade exists',
       'select count(q.qualitygrade_id)' || chr(10) ||
       'from qualitygrade q ' || chr(10) ||
       'where q.defaultqualitygrade = ''Y'' and (select count(*) from qualitygrade) > 0',
       'There is no default quality grade!',
       'Y',
       'There is more than one default quality grade!',
       'Y');
  
    insert into DBCONSISTENCYCHECK
      (DBCONSISTENCYCHECK_ID,
       DESCRIPTION,
       SQLSTATEMENT,
       ERRORMESSAGE,
       EXACTLYONEROW,
       ADDITIONALERRORMESSAGE,
       ACTIVE)
    values
      (20,
       'Each product has at least one product size',
       'select p.code productcode' || chr(10) || 'from product p' ||
       chr(10) ||
       'where not exists (select ps.product_id from productsize ps where ps.product_id = p.product_id)' ||
       chr(10) || 'order by 1',
       'There are products with no product size!',
       'N',
       null,
       'Y');
  
    insert into DBCONSISTENCYCHECK
      (DBCONSISTENCYCHECK_ID,
       DESCRIPTION,
       SQLSTATEMENT,
       ERRORMESSAGE,
       EXACTLYONEROW,
       ADDITIONALERRORMESSAGE,
       ACTIVE)
    values
      (21,
       'Each defined tax group has a turnover group and a not allocable turnover group defined when UseTurnverGroup = ''Y''',
       'select tx.code taxCode, tx.description taxDescription, tx.turnovergroup_id, tx.notallocatableturnover_id' ||
       chr(10) ||
       'from taxgroup tx where tx.turnovergroup_id is null or tx.notallocatableturnover_id is null' ||
       chr(10) || 'order by 1',
       'There are tax groups with no turnover group or not allocable turnovergroup defined!',
       'N',
       null,
       'Y');
  
    insert into DBCONSISTENCYCHECK
      (DBCONSISTENCYCHECK_ID,
       DESCRIPTION,
       SQLSTATEMENT,
       ERRORMESSAGE,
       EXACTLYONEROW,
       ADDITIONALERRORMESSAGE,
       ACTIVE)
    values
      (22,
       'Product groups per customer have corresponding record in activities per customer',
       'select a.customer_id, a.productgroup_id, b.code, c.activity_id, c.code' ||
       chr(10) ||
       ' from productgrouppercustomer a, productgroup b, activity c' ||
       chr(10) ||
       ' where a.productgroup_id = b.productgroup_id and b.activity_id = c.activity_id and' ||
       chr(10) || ' not exists ' || chr(10) ||
       ' (select pgpc.productgroup_id' || chr(10) ||
       ' from productgrouppercustomer pgpc, productgroup pg, customeractivities ca' ||
       chr(10) || ' where pgpc.productgroup_id = pg.productgroup_id and' ||
       chr(10) || ' pg.activity_id = ca.activity_id and' || chr(10) ||
       ' pgpc.customer_id = ca.customer_id and' || chr(10) ||
       ' a.productgroup_id = pgpc.productgroup_id and' || chr(10) ||
       ' a.customer_id = pgpc.customer_id)',
       'There are productgrouppercustomer records with productsgroups that do not have activities in customeractivities table!',
       'N',
       null,
       'Y');
  
    insert into DBCONSISTENCYCHECK
      (DBCONSISTENCYCHECK_ID,
       DESCRIPTION,
       SQLSTATEMENT,
       ERRORMESSAGE,
       EXACTLYONEROW,
       ADDITIONALERRORMESSAGE,
       ACTIVE)
    values
      (23,
       'Type of merchandise should be equal for product and its models',
       'select p.product_id, p.code, p.typeofmerchandise, m.product_id, m.code, m.typeofmerchandise' ||
       chr(10) || ' from product p, product m' || chr(10) ||
       ' where p.product_id = m.parentproduct_id and' || chr(10) ||
       ' m.product_id != m.parentproduct_id and' || chr(10) ||
       ' p.typeofmerchandise != m.typeofmerchandise' || chr(10) ||
       ' order by 2, 5',
       'There are models with a different type of merchandise than parent product!',
       'N',
       null,
       'Y');
  
    insert into DBCONSISTENCYCHECK
      (DBCONSISTENCYCHECK_ID,
       DESCRIPTION,
       SQLSTATEMENT,
       ERRORMESSAGE,
       EXACTLYONEROW,
       ADDITIONALERRORMESSAGE,
       ACTIVE)
    values
      (24,
       'One default transport article exists',
       'select count(t.transportarticle_id)' || chr(10) ||
       'from transportarticle t' || chr(10) ||
       'where t.defaulttransportarticle = ''Y'' and (select count(*) from transportarticle) > 0',
       'There is no default transport article!',
       'Y',
       'There is more than one default transport article!',
       'Y');
  
    insert into DBCONSISTENCYCHECK
      (DBCONSISTENCYCHECK_ID,
       DESCRIPTION,
       SQLSTATEMENT,
       ERRORMESSAGE,
       EXACTLYONEROW,
       ADDITIONALERRORMESSAGE,
       ACTIVE)
    values
      (25,
       'One default payment terms exists',
       'select count(pt.paymentterms_id)' || chr(10) ||
       'from paymentterms pt' || chr(10) ||
       'where pt.defaultpaymentterm = ''Y'' and (select count(*) from paymentterms) > 0',
       'There is no default payment terms!',
       'Y',
       'There is more than one default payment terms!',
       'Y');
  
    insert into DBCONSISTENCYCHECK
      (DBCONSISTENCYCHECK_ID,
       DESCRIPTION,
       SQLSTATEMENT,
       ERRORMESSAGE,
       EXACTLYONEROW,
       ADDITIONALERRORMESSAGE,
       ACTIVE)
    values
      (26,
       'Only one country has the flag for define tax set',
       'select count(c.country_id)' || chr(10) || 'from country c' ||
       chr(10) ||
       'where c.definetax = ''Y'' and (select count(*) from country) > 0',
       'There is no country which has the flag for define tax set!',
       'Y',
       'There is more than one country which has the flag for define tax set!',
       'Y');
  
    insert into DBCONSISTENCYCHECK
      (DBCONSISTENCYCHECK_ID,
       DESCRIPTION,
       SQLSTATEMENT,
       ERRORMESSAGE,
       EXACTLYONEROW,
       ADDITIONALERRORMESSAGE,
       ACTIVE)
    values
      (27,
       'One default invoice frequency exists',
       'select count(f.invoicefrequency_id)' || chr(10) ||
       'from invoicefrequency f' || chr(10) ||
       'where f.defaultfrequency = ''Y'' and (select count(*) from invoicefrequency) > 0',
       'There is no default invoice frequency!',
       'Y',
       'There is more than one default invoice frequency!',
       'Y');
  
    insert into DBCONSISTENCYCHECK
      (DBCONSISTENCYCHECK_ID,
       DESCRIPTION,
       SQLSTATEMENT,
       ERRORMESSAGE,
       EXACTLYONEROW,
       ADDITIONALERRORMESSAGE,
       ACTIVE)
    values
      (28,
       'Check if turnover group is defined for invoice line type - when it is a must',
       'select i.type, i.factorydescription' || chr(10) ||
       'from invoicelinetype i ' || chr(10) ||
       'where i.defineturnovergroup = ''Y'' and i.turnovergroup_id is null and i.invoicelinetype_id <> 15',
       'There are invoice line types where turnover group is not defined!',
       'N',
       null,
       'Y');
  
    insert into DBCONSISTENCYCHECK
      (DBCONSISTENCYCHECK_ID,
       DESCRIPTION,
       SQLSTATEMENT,
       ERRORMESSAGE,
       EXACTLYONEROW,
       ADDITIONALERRORMESSAGE,
       ACTIVE)
    values
      (29,
       'Check if taxgroup is defined for invoice line type - when it is a must',
       'select ilt.type, ilt.factorydescription' || chr(10) ||
       'from invoicelinetype ilt' || chr(10) ||
       'where ilt.taxtype != 0 and ' || chr(10) ||
       'not exists (select iltt.invoicelinetype_id from invoicelinetypetax iltt, country c' ||
       chr(10) ||
       '            where ilt.invoicelinetype_id = iltt.invoicelinetype_id and' ||
       chr(10) ||
       '                  iltt.country_id = c.country_id and c.definetax = ''Y'')',
       'There are invoice line types where taxgroup is not defined!',
       'N',
       null,
       'Y');
  
    insert into DBCONSISTENCYCHECK
      (DBCONSISTENCYCHECK_ID,
       DESCRIPTION,
       SQLSTATEMENT,
       ERRORMESSAGE,
       EXACTLYONEROW,
       ADDITIONALERRORMESSAGE,
       ACTIVE)
    values
      (30,
       'Check if a turnover group for credit squeeze is defined when UseTurnverGroup = ''Y''',
       'select p.code plantcode from plant p where p.creditsqueezeturnovergroup_id is null',
       'There are plants where no turnover group for credit squeeze is defined!',
       'N',
       null,
       'Y');
     
    insert into DBCONSISTENCYCHECK
      (DBCONSISTENCYCHECK_ID,
       DESCRIPTION,
       SQLSTATEMENT,
       ERRORMESSAGE,
       EXACTLYONEROW,
       ADDITIONALERRORMESSAGE,
       ACTIVE)
    values
      (31,
       'Check if per plant at least one invoice line type is defined',
       'select p.code plantcode from plant p ' || chr(10) ||
       'where not exists (select pilt.plant_id from plantinvoicelinetype pilt where p.plant_id = pilt.plant_id)',
       'There are plants where no invoice line type is defined!',
       'N',
       null,
       'Y');
  
    insert into DBCONSISTENCYCHECK
      (DBCONSISTENCYCHECK_ID,
       DESCRIPTION,
       SQLSTATEMENT,
       ERRORMESSAGE,
       EXACTLYONEROW,
       ADDITIONALERRORMESSAGE,
       ACTIVE)
    values
      (32,
       'Check if per plant at least one invoice frequency is defined',
       'select p.code plantcode from plant p ' || chr(10) ||
       'where not exists (select pif.plant_id from plantinvoicefrequency pif ' ||
       chr(10) ||
       '                  where p.plant_id = pif.plant_id and pif.documenttype = 1)',
       'There are plants where no invoice frequency is defined!',
       'N',
       null,
       'Y');
  
    insert into DBCONSISTENCYCHECK
      (DBCONSISTENCYCHECK_ID,
       DESCRIPTION,
       SQLSTATEMENT,
       ERRORMESSAGE,
       EXACTLYONEROW,
       ADDITIONALERRORMESSAGE,
       ACTIVE)
    values
      (33,
       'Check if per plant at least one statement frequency is defined',
       'select p.code plantcode from plant p ' || chr(10) ||
       'where not exists (select pif.plant_id from plantinvoicefrequency pif ' ||
       chr(10) ||
       '                  where p.plant_id = pif.plant_id and pif.documenttype = 2)',
       'There are plants where no invoice statement is defined!',
       'N',
       null,
       'Y');
     
    
    insert into DBCONSISTENCYCHECK
      (DBCONSISTENCYCHECK_ID,
       DESCRIPTION,
       SQLSTATEMENT,
       ERRORMESSAGE,
       EXACTLYONEROW,
       ADDITIONALERRORMESSAGE,
       ACTIVE)
    values
      (34,
       'If there are emblem templates defined check if the fabric has the flag UsedForEmblem checked',
       'select e.code emblemtemplatecode, e.description, c.customernumber, ff.code' ||
       chr(10) ||
       'from emblemtemplate e, emblemtemplateline el, customer c, fabric ff' ||
       chr(10) || 'where e.emblemtemplate_id = el.emblemtemplate_id and ' ||
       chr(10) || '      e.customer_id = c.customer_id and' || chr(10) ||
       '      el.fabric_id = ff.fabric_id and' || chr(10) ||
       '      ff.usedforemblem = ''N'' ' || chr(10) || 'order by 1, 2, 4 ',
       'There are emblem templates having UsedForEmblem flag unchecked for the fabric!',
       'N',
       null,
       'Y');
  
    insert into DBCONSISTENCYCHECK
      (DBCONSISTENCYCHECK_ID,
       DESCRIPTION,
       SQLSTATEMENT,
       ERRORMESSAGE,
       EXACTLYONEROW,
       ADDITIONALERRORMESSAGE,
       ACTIVE)
    values
      (35,
       'If there are emblem templates defined check if the size definition has the flag UsedForEmblem checked',
       'select c.customernumber, e.code emblemtemplatecode, e.description, ss.code' ||
       chr(10) ||
       'from emblemtemplate e, emblemtemplateline el, customer c, sizedefinition ss' ||
       chr(10) || 'where e.emblemtemplate_id = el.emblemtemplate_id and ' ||
       chr(10) || '      e.customer_id = c.customer_id and' || chr(10) ||
       '      el.sizedefinition_id = ss.sizedefinition_id and' || chr(10) ||
       '      ss.usedforemblem =  ''N'' ' || chr(10) || 'order by 1, 2, 4 ',
       'There are emblem templates having UsedForEmblem flag unchecked for the size definition!',
       'N',
       null,
       'Y');
  
    insert into DBCONSISTENCYCHECK
      (DBCONSISTENCYCHECK_ID,
       DESCRIPTION,
       SQLSTATEMENT,
       ERRORMESSAGE,
       EXACTLYONEROW,
       ADDITIONALERRORMESSAGE,
       ACTIVE)
    values
      (36,
       'If there are emblem templates defined check if the text color has the flag UsedForEmblem checked',
       'select e.code emblemtemplatecode, e.description, c.customernumber, cc.code' ||
       chr(10) ||
       'from emblemtemplate e, emblemtemplateline el, customer c, color cc' ||
       chr(10) || 'where e.emblemtemplate_id = el.emblemtemplate_id and ' ||
       chr(10) || '      e.customer_id = c.customer_id and' || chr(10) ||
       '      el.textcolor_id = cc.color_id and' || chr(10) ||
       '      cc.usedforemblem = ''N'' ',
       'There are emblem templates having UsedForEmblem flag unchecked for the text color!',
       'N',
       null,
       'Y');
  
    insert into DBCONSISTENCYCHECK
      (DBCONSISTENCYCHECK_ID,
       DESCRIPTION,
       SQLSTATEMENT,
       ERRORMESSAGE,
       EXACTLYONEROW,
       ADDITIONALERRORMESSAGE,
       ACTIVE)
    values
      (37,
       'If there are emblem templates defined check if the border color has the flag UsedForEmblem checked',
       'select e.code emblemtemplatecode, e.description, c.customernumber, cc.code' ||
       chr(10) ||
       'from emblemtemplate e, emblemtemplateline el, customer c, color cc' ||
       chr(10) || 'where e.emblemtemplate_id = el.emblemtemplate_id and ' ||
       chr(10) || '      e.customer_id = c.customer_id and' || chr(10) ||
       '      el.bordercolor_id = cc.color_id and' || chr(10) ||
       '      cc.usedforemblem = ''N'' ',
       'There are emblem templates having UsedForEmblem flag unchecked for the border color!',
       'N',
       null,
       'Y');
  
    insert into DBCONSISTENCYCHECK
      (DBCONSISTENCYCHECK_ID,
       DESCRIPTION,
       SQLSTATEMENT,
       ERRORMESSAGE,
       EXACTLYONEROW,
       ADDITIONALERRORMESSAGE,
       ACTIVE)
    values
      (38,
       'If there are emblem templates defined check if the background color has the flag UsedForEmblem checked',
       'select e.code emblemtemplatecode, e.description, c.customernumber, cc.code' ||
       chr(10) ||
       'from emblemtemplate e, emblemtemplateline el, customer c, color cc' ||
       chr(10) || 'where e.emblemtemplate_id = el.emblemtemplate_id and ' ||
       chr(10) || '      e.customer_id = c.customer_id and' || chr(10) ||
       '      el.bkgcolor_id = cc.color_id and' || chr(10) ||
       '      cc.usedforemblem = ''N'' ',
       'There are emblem templates having UsedForEmblem flag unchecked for the background color!',
       'N',
       null,
       'Y');
  --section break for pack_Script
    insert into DBCONSISTENCYCHECK
      (DBCONSISTENCYCHECK_ID,
       DESCRIPTION,
       SQLSTATEMENT,
       ERRORMESSAGE,
       EXACTLYONEROW,
       ADDITIONALERRORMESSAGE,
       ACTIVE)
    values
      (39,
       'Check if for active wearer inventory lines active plpc records exist (also size *)',
       'select c.customernumber, w.wearernumber, we.sequencenumber, p.code productCode, s.code sizeCode, f.code finishingmethodCode,' ||
       'to_char(plpc.dateactive,''DD-MON-yyyy'') plpcDateactive, to_char(plpc.dateinactive,''DD-MON-yyyy'') plpcDateinactive, plpc.productlistpercustomer_id ' ||
       chr(10) ||
       'from wearerinventory wi, weareremployment we, wearer w, customer c, product p, sizedefinition s, finishingmethod f, productlistpercustomer plpc' ||
       chr(10) ||
       'where wi.weareremployment_id = we.weareremployment_id and ' ||
       chr(10) || '      we.wearer_id = w.wearer_id and' || chr(10) ||
       '      w.customer_id = c.customer_id and' || chr(10) ||
       '      wi.product_id = p.product_id and' || chr(10) ||
       '      wi.sizedefinition_id = s.sizedefinition_id and' || chr(10) ||
       '      wi.finishingmethod_id = f.finishingmethod_id and' || chr(10) ||
       '      sysdate between wi.dateactive and wi.dateinactive and' ||
       chr(10) ||
       '       plpc.customer_id = c.customer_id and ' ||
       chr(10) ||
       '       plpc.product_id = p.product_id and' ||
       chr(10) ||
       '       plpc.sizedefinition_id = s.sizedefinition_id ' ||
       chr(10) ||
       '       and not exists ' ||
       chr(10) || ' (select productlistpercustomer_id ' ||
       chr(10) || ' from productlistpercustomer ' ||
       chr(10) || ' where customer_id = c.customer_id ' ||
       chr(10) || ' and product_id = p.product_id ' ||
       chr(10) || ' and sizedefinition_id = ' ||
       chr(10) || ' (select ss.sizedefinition_id ' ||
       chr(10) || '  from sizedefinition ss ' ||
       chr(10) || '  where ss.code = ''*'')  ' ||
       chr(10) || '  and sysdate between dateactive and dateinactive) and' ||
       chr(10) ||
       '        plpc.finishingmethod_id = f.finishingmethod_id and' ||
       chr(10) ||
       '        sysdate not between plpc.dateactive and plpc.dateinactive' ||
       chr(10) ||
'union ' ||
       'select c.customernumber, w.wearernumber, we.sequencenumber, p.code productCode, s.code sizeCode, f.code finishingmethodCode,' ||
       'to_char(plpc.dateactive,''DD-MON-yyyy'') plpcDateactive, to_char(plpc.dateinactive,''DD-MON-yyyy'') plpcDateinactive, plpc.productlistpercustomer_id ' ||
       chr(10) ||
       'from wearerinventory wi, weareremployment we, wearer w, customer c, product p, sizedefinition s, finishingmethod f, productlistpercustomer plpc' ||
       chr(10) ||
       'where wi.weareremployment_id = we.weareremployment_id and ' ||
       chr(10) || '      we.wearer_id = w.wearer_id and' || chr(10) ||
       '      w.customer_id = c.customer_id and' || chr(10) ||
       '      wi.product_id = p.product_id and' || chr(10) ||
       '      wi.sizedefinition_id = s.sizedefinition_id and' || chr(10) ||
       '      wi.finishingmethod_id = f.finishingmethod_id and' || chr(10) ||
       '      sysdate between wi.dateactive and wi.dateinactive and' ||
       chr(10) ||
       '       plpc.customer_id = c.customer_id and ' ||
       chr(10) ||
       '       plpc.product_id = p.product_id and' ||
       chr(10) ||
       '       plpc.sizedefinition_id = (select ss.sizedefinition_id from sizedefinition ss where ss.code = ''*'') ' ||
       chr(10) ||
       '       and not exists ' ||
       chr(10) || ' (select productlistpercustomer_id ' ||
       chr(10) || ' from productlistpercustomer ' ||
       chr(10) || ' where customer_id = c.customer_id ' ||
       chr(10) || ' and product_id = p.product_id ' ||
       chr(10) || ' and sizedefinition_id =  s.sizedefinition_id ' ||
       chr(10) || ' and sysdate between dateactive and dateinactive) and' ||
       chr(10) ||
       '        plpc.finishingmethod_id = f.finishingmethod_id and' ||
       chr(10) ||
       '        sysdate not between plpc.dateactive and plpc.dateinactive' ||
       chr(10) ||
'union ' ||
       chr(10) ||
'select c.customernumber, w.wearernumber, we.sequencenumber, p.code productCode, s.code sizeCode, f.code finishingmethodCode,
       null, null, null ' ||
       chr(10) ||
       'from wearerinventory wi, weareremployment we, wearer w, customer c, product p, sizedefinition s, finishingmethod f' ||
       chr(10) ||
       'where wi.weareremployment_id = we.weareremployment_id and ' ||
       chr(10) || '      we.wearer_id = w.wearer_id and' || chr(10) ||
       '      w.customer_id = c.customer_id and' || chr(10) ||
       '      wi.product_id = p.product_id and' || chr(10) ||
       '      wi.sizedefinition_id = s.sizedefinition_id and' || chr(10) ||
       '      wi.finishingmethod_id = f.finishingmethod_id and' || chr(10) ||
       '      sysdate between wi.dateactive and wi.dateinactive and' ||
       chr(10) ||
       '      not exists (select plpc.productlistpercustomer_id from productlistpercustomer plpc' ||
       chr(10) ||
       '                  where plpc.customer_id = c.customer_id and ' ||
       chr(10) ||
       '                        plpc.product_id = p.product_id and' ||
       chr(10) ||
       '                        (plpc.sizedefinition_id = s.sizedefinition_id or' ||
       chr(10) ||
       '                         plpc.sizedefinition_id = (select ss.sizedefinition_id from sizedefinition ss where ss.code = ''*'')) and' ||
       chr(10) ||
       '                        plpc.finishingmethod_id = f.finishingmethod_id )',
       'There are active wearer inventories line with no corresponding active product list per customer!',
       'N',
       null,
       'Y');
  
    insert into DBCONSISTENCYCHECK
      (DBCONSISTENCYCHECK_ID,
       DESCRIPTION,
       SQLSTATEMENT,
       ERRORMESSAGE,
       EXACTLYONEROW,
       ADDITIONALERRORMESSAGE,
       ACTIVE)
    values
      (40,
       'Check for overlapping product list per customer',
       'select c.customernumber, p.code productcode, s.code sizecode, f.code finishingmethodcode,' ||
       chr(10) ||
       'a.productlistpercustomer_id, a.dateactive, a.dateinactive, ' ||
       chr(10) ||
       'b.productlistpercustomer_id, b.dateactive, b.dateinactive, ' ||
       chr(10) || 'a.timestamp, b.timestamp' || chr(10) ||
       'from productlistpercustomer a, productlistpercustomer b, customer c, product p, sizedefinition s, finishingmethod f' ||
       chr(10) || 'where a.customer_id = b.customer_id and' || chr(10) ||
       'a.product_id = b.product_id and' || chr(10) ||
       'a.sizedefinition_id = b.sizedefinition_id and' || chr(10) ||
       'a.finishingmethod_id = b.finishingmethod_id and' || chr(10) ||
       'a.productlistpercustomer_id != b.productlistpercustomer_id and' ||
       chr(10) ||
       '(a.dateactive between b.dateactive and b.dateinactive or' ||
       chr(10) ||
       'a.dateinactive between b.dateactive and b.dateinactive) and' ||
       chr(10) || 'a.customer_id = c.customer_id and' || chr(10) ||
       'a.product_id = p.product_id and' || chr(10) ||
       'a.sizedefinition_id = s.sizedefinition_id and' || chr(10) ||
       'a.finishingmethod_id = f.finishingmethod_id' || chr(10) ||
       'order by c.customernumber, p.code, s.code, f.code',
       'There are overlapping product list per customer!',
       'N',
       null,
       'Y');
   
    insert into DBCONSISTENCYCHECK
      (DBCONSISTENCYCHECK_ID,
       DESCRIPTION,
       SQLSTATEMENT,
       ERRORMESSAGE,
       EXACTLYONEROW,
       ADDITIONALERRORMESSAGE,
       ACTIVE)
    values
      (41,
       'Check for overlapping standard delivery lines',
       'select cust.customernumber, dep.departmentnumber, dip.distributionpointnumber, ' ||
       chr(10) ||
       '       p.code productcode, s.code sizecode, fm.code finishingmethodcode,' ||
       chr(10) ||
       '       sdl1.standarddeliveryline_id, sdl1.dateactive, sdl1.dateinactive, ' ||
       chr(10) ||
       '       sdl2.standarddeliveryline_id, sdl2.dateactive, sdl2.dateinactive ' ||
       chr(10) ||
       'from standarddeliveryline sdl1, standarddeliveryline sdl2, productlistperdistribpoint plpdp, ' ||
       chr(10) || '     product p, sizedefinition s, finishingmethod fm, ' ||
       chr(10) ||
       '     distributionpoint dip, department dep, customer cust' ||
       chr(10) ||
       'where sdl1.standarddelivery_id = sdl2.standarddelivery_id and' ||
       chr(10) ||
       '      sdl1.productlistperdistribpoint_id = sdl2.productlistperdistribpoint_id and' ||
       chr(10) ||
       '      sdl1.standarddeliveryline_id < sdl2.standarddeliveryline_id and' ||
       chr(10) ||
       '      (((sdl1.dateactive <= sdl2.dateactive) and (sdl2.dateactive <= sdl1.dateinactive)) or' ||
       chr(10) ||
       '       ((sdl2.dateactive <= sdl1.dateactive) and (sdl1.dateactive <= sdl2.dateinactive))) and' ||
       chr(10) ||
       '      sdl1.productlistperdistribpoint_id = plpdp.productlistperdistribpoint_id and' ||
       chr(10) ||
       '      plpdp.distributionpoint_id = dip.distributionpoint_id and' ||
       chr(10) || '      dip.department_id = dep.department_id and' ||
       chr(10) || '      dep.customer_id = cust.customer_id and' || chr(10) ||
       '      plpdp.product_id = p.product_id and' || chr(10) ||
       '      plpdp.sizedefinition_id = s.sizedefinition_id and' || chr(10) ||
       '      plpdp.finishingmethod_id = fm.finishingmethod_id ',
       'There are overlapping standard delivery lines!',
       'N',
       null,
       'Y');
  
    insert into DBCONSISTENCYCHECK
      (DBCONSISTENCYCHECK_ID,
       DESCRIPTION,
       SQLSTATEMENT,
       ERRORMESSAGE,
       EXACTLYONEROW,
       ADDITIONALERRORMESSAGE,
       ACTIVE)
    values
      (42,
       'Check if there are wearer employments linked to a wrong distribution point (linked to another customer than customer of the wearer)',
       'select c.customernumber, w.wearernumber, we.sequencenumber, cc.customernumber' ||
       chr(10) ||
       'from weareremployment we, wearer w, customer c, distributionpoint dip, department dep, customer cc' ||
       chr(10) || 'where we.wearer_id = w.wearer_id and' || chr(10) ||
       '      w.customer_id = c.customer_id and' || chr(10) ||
       '      we.distributionpoint_id = dip.distributionpoint_id and' ||
       chr(10) || '      dip.department_id = dep.department_id and' ||
       chr(10) || '      dep.customer_id = cc.customer_id and' || chr(10) ||
       '      c.customer_id != cc.customer_id',
       'There are wearer employments linked to a wrong distribution point!',
       'N',
       null,
       'Y');
  
    insert into DBCONSISTENCYCHECK
      (DBCONSISTENCYCHECK_ID,
       DESCRIPTION,
       SQLSTATEMENT,
       ERRORMESSAGE,
       EXACTLYONEROW,
       ADDITIONALERRORMESSAGE,
       ACTIVE)
    values
      (43,
       'Check if in wearer inventory only products with type of merchandise garments exist',
       'select c.customernumber, w.wearernumber, we.sequencenumber, p.code, p.typeofmerchandise' ||
       chr(10) ||
       'from wearerinventory wi, weareremployment we, wearer w, customer c, product p' ||
       chr(10) ||
       'where wi.weareremployment_id = we.weareremployment_id and' ||
       chr(10) || '      we.wearer_id = w.wearer_id and' || chr(10) ||
       '      w.customer_id = c.customer_id and' || chr(10) ||
       '      wi.product_id = p.product_id and' || chr(10) ||
       '      p.typeofmerchandise != 2',
       'There are products in wearer inventory with another type of merchandise than garments',
       'N',
       null,
       'Y');
  
    insert into DBCONSISTENCYCHECK
      (DBCONSISTENCYCHECK_ID,
       DESCRIPTION,
       SQLSTATEMENT,
       ERRORMESSAGE,
       EXACTLYONEROW,
       ADDITIONALERRORMESSAGE,
       ACTIVE)
    values
      (44,
       'Check if wearer employments have only one of the following links filled: delivery point, locker, garment dispenser',
       'select c.customernumber, w.wearernumber, we.sequencenumber' ||
       chr(10) || 'from weareremployment we, wearer w, customer c' ||
       chr(10) || 'where we.wearer_id = w.wearer_id and' || chr(10) ||
       '      w.customer_id = c.customer_id and' || chr(10) ||
       '      ((we.deliverypoint_id is not null and we.locker_id is not null) or' ||
       chr(10) ||
       '       (we.deliverypoint_id is not null and we.garmentdispenser_id is not null) or ' ||
       chr(10) ||
       '       (we.locker_id is not null and we.garmentdispenser_id is not null))',
       'There are wearer employments having more than only one of the following links filled: delivery point, locker, garment dispenser!',
       'N',
       null,
       'Y');
  
    insert into DBCONSISTENCYCHECK
      (DBCONSISTENCYCHECK_ID,
       DESCRIPTION,
       SQLSTATEMENT,
       ERRORMESSAGE,
       EXACTLYONEROW,
       ADDITIONALERRORMESSAGE,
       ACTIVE)
    values
      (45,
       'Check if wearer employments have at least one of the following links filled: delivery point, locker, garment dispenser',
       'select c.customernumber, w.wearernumber, we.sequencenumber' ||
       chr(10) || 'from weareremployment we, wearer w, customer c' ||
       chr(10) || 'where we.wearer_id = w.wearer_id and' || chr(10) ||
       '      w.customer_id = c.customer_id and' || chr(10) ||
       '      we.deliverypoint_id is null and we.locker_id is null and we.garmentdispenser_id is null',
       'There are wearer employments having none of the following links filled: delivery point, locker, garment dispenser!',
       'N',
       null,
       'Y');
  
    insert into DBCONSISTENCYCHECK
      (DBCONSISTENCYCHECK_ID,
       DESCRIPTION,
       SQLSTATEMENT,
       ERRORMESSAGE,
       EXACTLYONEROW,
       ADDITIONALERRORMESSAGE,
       ACTIVE)
    values
      (46,
       'Check if every product group is linked to a packing department',
       'select pg.code prodgroupcode, pl.code plantcode' || chr(10) ||
       'from productgroup pg, plant pl' || chr(10) ||
       'where exists (select pd.productgroup_id' || chr(10) ||
       'from packingdepartmentdefinition pd' || chr(10) ||
       'where pd.productgroup_id = pg.productgroup_id and' || chr(10) ||
       'pd.plant_id = pl.plant_id and' || chr(10) ||
       'pd.plantdepartment_id is null) OR not exists' || chr(10) ||
       '(select pdd.productgroup_id' || chr(10) ||
       'from packingdepartmentdefinition pdd' || chr(10) ||
       'where pdd.productgroup_id = pg.productgroup_id and' || chr(10) ||
       'pdd.plant_id = pl.plant_id)',
       'There are product groups that are not linked to a packing department',
       'N',
       null,
       'Y');
  
    insert into DBCONSISTENCYCHECK
      (DBCONSISTENCYCHECK_ID,
       DESCRIPTION,
       SQLSTATEMENT,
       ERRORMESSAGE,
       EXACTLYONEROW,
       ADDITIONALERRORMESSAGE,
       ACTIVE)
    values
      (47,
       'Check for non pool unique items the existence of UniqueItemNonPool table',
       'select u.primaryid' || chr(10) || 'from uniqueitem u' || chr(10) ||
       'where u.poolitem = 2 and' || chr(10) ||
       '      not exists (select uinp.uniqueitem_id from uniqueitemnonpool uinp where u.uniqueitem_id = uinp.uniqueitem_id)',
       'There are non pool items without a corresponding record in UniqueItemNonPoolTable!',
       'N',
       null,
       'Y');
  
    insert into DBCONSISTENCYCHECK
      (DBCONSISTENCYCHECK_ID,
       DESCRIPTION,
       SQLSTATEMENT,
       ERRORMESSAGE,
       EXACTLYONEROW,
       ADDITIONALERRORMESSAGE,
       ACTIVE)
    values
      (48,
       'Check correct filling of field poolitem regarding stay stock',
       'select u.primaryid, s.staytype, s.staynr, s.factorydescription' ||
       chr(10) || 'from uniqueitem u, stay s' || chr(10) ||
       'where u.poolitem = 0 and u.stay_id = s.stay_id and ' || chr(10) ||
       '      (s.staytype != 1 or s.staynr != 10)',
       'There are unique items in stock where stay is not filled correctly!',
       'N',
       null,
       'Y');
  
     
    insert into DBCONSISTENCYCHECK
      (DBCONSISTENCYCHECK_ID,
       DESCRIPTION,
       SQLSTATEMENT,
       ERRORMESSAGE,
       EXACTLYONEROW,
       ADDITIONALERRORMESSAGE,
       ACTIVE)
    values
      (49,
       'Check if there are route controls with settled and not settled invoices',
       'select r.routenumber, r.drivingday_id, rc.drivingdate ' || chr(10) ||
       'from routecontrol rc, route r' || chr(10) ||
       'where rc.route_id = r.route_id and' || chr(10) ||
       '      exists (select ih.invoiceheader_id from invoiceheader ih, status s' ||
       chr(10) || '              where ih.status_id = s.status_id and ' ||
       chr(10) ||
       '                    ih.routecontrol_id = rc.routecontrol_id and' ||
       chr(10) || '                    s.code = 2) and' || chr(10) ||
       '      exists (select ih.invoiceheader_id from invoiceheader ih, status s' ||
       chr(10) || '              where ih.status_id = s.status_id and ' ||
       chr(10) ||
       '                    ih.routecontrol_id = rc.routecontrol_id and' ||
       chr(10) || '                    s.code = 1)',
       'There are route controls with settled and not settled invoices!',
       'N',
       null,
       'Y');
  
    insert into DBCONSISTENCYCHECK
      (DBCONSISTENCYCHECK_ID,
       DESCRIPTION,
       SQLSTATEMENT,
       ERRORMESSAGE,
       EXACTLYONEROW,
       ADDITIONALERRORMESSAGE,
       ACTIVE)
    values
      (50,
       'Check if there is a product list per customer record for unique items in circulation',
       'select b.code businessunitcode, count(ui.primaryid), ''Non pool'' as "NonPool/Inscanned/Outscanned",' || 
        '(select pr.code from product pr where pr.product_id = ui.product_id) productCode,  '||
        '(select cu.customernumber from customer cu where cu.customer_id = w.customer_id) customerNo,  '||
        '(select sz.code from sizedefinition sz where sz.sizedefinition_id = ui.sizedefinition_id) sizeCode  '||
        'from uniqueitem ui, uniqueitemnonpool uinp, weareremployment we, wearer w, status, businessunit b  '||
        'where ui.uniqueitem_id = uinp.uniqueitem_id and ui.poolitem = 2 and  '||
        'ui.status_id = status.status_id and status.code = ''20'' and '||
        'uinp.weareremployment_id = we.weareremployment_id and '||
        'we.wearer_id = w.wearer_id and '||
        'ui.businessunit_id = b.businessunit_id and '||
        'not exists (select plpc.productlistpercustomer_id from productlistpercustomer plpc '||
        '            where w.customer_id = plpc.customer_id and '||
        '                  ui.product_id = plpc.product_id and '||
        '                  (ui.sizedefinition_id = plpc.sizedefinition_id or  '||
        '                   plpc.sizedefinition_id = (select s.sizedefinition_id from sizedefinition s where s.code = ''*'')) and '||
        '                  ui.finishingmethod_id = plpc.finishingmethod_id ) '||
        'group by b.code , b.description, ui.product_id, w.customer_id, ui.sizedefinition_id '||
        'union all '||
        'select b.code businessunitcode, count(ui.primaryid), ''Pool inscanned'', '||
        '(select pr.code from product pr where pr.product_id = ui.product_id) productCode, '||
        '(select cu.customernumber from customer cu where cu.customer_id = dep.customer_id) customerNo, '||
        '(select sz.code from sizedefinition sz where sz.sizedefinition_id = ui.sizedefinition_id) sizeCode  '||
        'from uniqueitem ui, distributionpoint dip, department dep, status, businessunit b  '||
        'where ui.lastdistributionpoint_link_id = dip.distributionpoint_id and  '||
        'dip.department_id = dep.department_id and ui.poolitem = 1 and  '||
        'ui.status_id = status.status_id and status.code = ''20'' and  '||
        'ui.businessunit_id = b.businessunit_id and '||
        'not exists (select plpc.productlistpercustomer_id from productlistpercustomer plpc '||
        '            where dep.customer_id = plpc.customer_id and '||
        '                  ui.product_id = plpc.product_id and '||
        '                 (ui.sizedefinition_id = plpc.sizedefinition_id or  '||
        '                   plpc.sizedefinition_id = (select s.sizedefinition_id from sizedefinition s where s.code = ''*'')) and '||
        '                  ui.finishingmethod_id = plpc.finishingmethod_id ) '||
        'group by b.code , b.description, ui.product_id, dep.customer_id, ui.sizedefinition_id '||
        'union all '||
        'select b.code businessunitcode, count(ui.primaryid), ''Pool outscanned'', '||
        '(select pr.code from product pr where pr.product_id = ui.product_id) productCode, '||
        '(select cu.customernumber from customer cu where cu.customer_id = dep.customer_id) customerNo, '||
        '(select sz.code from sizedefinition sz where sz.sizedefinition_id = ui.sizedefinition_id) sizeCode '||
        'from uniqueitem ui, distributionpoint dip, department dep, status, businessunit b '||
        'where ui.distributionpoint_id = dip.distributionpoint_id and '||
        'dip.department_id = dep.department_id and ui.poolitem = 1 and  '||
        'ui.status_id = status.status_id and status.code = ''20'' and '||
        'ui.businessunit_id = b.businessunit_id and '||
        'not exists (select plpc.productlistpercustomer_id from productlistpercustomer plpc '||
        '            where dep.customer_id = plpc.customer_id and '||
        '                  ui.product_id = plpc.product_id and '||
        '                  (ui.sizedefinition_id = plpc.sizedefinition_id or  '||
        '                   plpc.sizedefinition_id = (select s.sizedefinition_id from sizedefinition s where s.code = ''*'')) and '||
        '                  ui.finishingmethod_id = plpc.finishingmethod_id ) '||
        'group by b.code , b.description, ui.product_id, dep.customer_id, ui.sizedefinition_id '||
        'order by 1, 5, 4, 6',
       'There is no active product list per customer record for the following unique items in circulation!',
       'N',
       null,
       'Y');
  
    insert into DBCONSISTENCYCHECK
      (DBCONSISTENCYCHECK_ID,
       DESCRIPTION,
       SQLSTATEMENT,
       ERRORMESSAGE,
       EXACTLYONEROW,
       ADDITIONALERRORMESSAGE,
       ACTIVE)
    values
      (51,
       'Check if there are missing customer delivery schemes (check in product list per customer, standard delivery line and standard delivery amount)',
       'select distinct c.customernumber, c.name, ds.code deliveryschemecode, sdlamnt.day_id' ||
       chr(10) ||
       'from productlistpercustomer plpc, standarddeliveryline sdl, standarddeliveryamount sdlamnt, deliveryscheme ds, customer c' ||
       chr(10) ||
       'where plpc.productlistpercustomer_id = sdl.productlistpercustomer_id and' ||
       chr(10) ||
       'sdl.standarddeliveryline_id = sdlamnt.standarddeliveryline_id and' ||
       chr(10) || 'sdlamnt.amount != 0 and' || chr(10) ||
       'sdl.deliveryscheme_id = ds.deliveryscheme_id and' || chr(10) ||
       'plpc.customer_id = c.customer_id and' || chr(10) ||
       'not exists (select cds.customer_id from customerdeliveryscheme cds' ||
       chr(10) ||
       '            where plpc.customer_id = cds.customer_id and' ||
       chr(10) ||
       '                  sdl.deliveryscheme_id = cds.deliveryscheme_id and' ||
       chr(10) || '                  sdlamnt.day_id = cds.day_id)',
       'There are misssing customer delivery schemes!',
       'N',
       null,
       'Y');
  
    insert into DBCONSISTENCYCHECK
      (DBCONSISTENCYCHECK_ID,
       DESCRIPTION,
       SQLSTATEMENT,
       ERRORMESSAGE,
       EXACTLYONEROW,
       ADDITIONALERRORMESSAGE,
       ACTIVE)
    values
      (52,
       'Check if there are missing customer delivery days.',
       'select distinct c.customernumber, c.name, cds.day_id' || chr(10) ||
       'from customerdeliveryscheme cds, customer c' || chr(10) ||
       'where cds.customer_id = c.customer_id and' || chr(10) ||
       'not exists (select cdd.customer_id from customerdeliveryday cdd ' ||
       chr(10) ||
       '            where cds.customer_id = cdd.customer_id and cds.day_id = cdd.day_id and cdd.typeofmerchadise = 1)' ||
       chr(10) || 'order by 1, 3',
       'There are missing customer delivery days!',
       'N',
       null,
       'Y');
  
    insert into DBCONSISTENCYCHECK
      (DBCONSISTENCYCHECK_ID,
       DESCRIPTION,
       SQLSTATEMENT,
       ERRORMESSAGE,
       EXACTLYONEROW,
       ADDITIONALERRORMESSAGE,
       ACTIVE)
    values
      (53,
       'Check if there are missing customer delivery stops.',
       'select distinct c.customernumber, c.name, ds.code deliveryschemecode, r.routenumber, cds.day_id, rs.stopnumber' ||
       chr(10) ||
       'from customerdeliveryscheme cds, routestop rs, route r, customer c, deliveryscheme ds' ||
       chr(10) || 'where cds.customer_id = rs.customer_id and' || chr(10) ||
       'rs.route_id = r.route_id and' || chr(10) ||
       'cds.customer_id = c.customer_id and' || chr(10) ||
       'cds.deliveryscheme_id = ds.deliveryscheme_id and' || chr(10) ||
       'cds.day_id = r.drivingday_id and' || chr(10) ||
       'not exists (select dss.customer_id' || chr(10) ||
       '            from deliveryschemesperstop dss, routestop rss' ||
       chr(10) ||
       '            where dss.routestop_id = rss.routestop_id and' ||
       chr(10) || '                  dss.customer_id = rss.customer_id and' ||
       chr(10) ||
       '                  cds.deliveryscheme_id = dss.deliveryscheme_id and' ||
       chr(10) || '                  cds.customer_id = rss.customer_id)',
       'There are missing customer delivery stops.',
       'N',
       null,
       'Y');
  
    insert into DBCONSISTENCYCHECK
      (DBCONSISTENCYCHECK_ID,
       DESCRIPTION,
       SQLSTATEMENT,
       ERRORMESSAGE,
       EXACTLYONEROW,
       ADDITIONALERRORMESSAGE,
       ACTIVE)
    values
      (54,
       'If there are product emblems defined check if the fabric has the flag UsedForEmblem checked',
       'select p.code productemblemcode, ff.code fabriccode' || chr(10) ||
       'from product p, fabric ff' || chr(10) ||
       'where p.fabric_id = ff.fabric_id and p.typeofmerchandise = 4 and ' ||
       chr(10) || '      ff.usedforemblem = ''N'' ' || chr(10) ||
       'order by 1, 2 ',
       'There are product emblems having UsedForEmblem flag unchecked for the fabric!',
       'N',
       null,
       'Y');
  
    insert into DBCONSISTENCYCHECK
      (DBCONSISTENCYCHECK_ID,
       DESCRIPTION,
       SQLSTATEMENT,
       ERRORMESSAGE,
       EXACTLYONEROW,
       ADDITIONALERRORMESSAGE,
       ACTIVE)
    values
      (55,
       'If there are product emblems defined check if the background color has the flag UsedForEmblem checked',
       'select p.code productemblemcode, cc.code colorcode' || chr(10) ||
       'from product p, color cc' || chr(10) ||
       'where p.color_id = cc.color_id and ' || chr(10) ||
       '      p.typeofmerchandise = 4 and' || chr(10) ||
       '      cc.usedforemblem = ''N'' ',
       'There are product emblems having UsedForEmblem flag unchecked for the background color!',
       'N',
       null,
       'Y');
  
    insert into DBCONSISTENCYCHECK
      (DBCONSISTENCYCHECK_ID,
       DESCRIPTION,
       SQLSTATEMENT,
       ERRORMESSAGE,
       EXACTLYONEROW,
       ADDITIONALERRORMESSAGE,
       ACTIVE)
    values
      (56,
       'If there are product emblems defined check if the text color has the flag UsedForEmblem checked',
       'select p.code productemblemcode, cc.code colorcode' || chr(10) ||
       'from product p, emblemproduct ep, color cc' || chr(10) ||
       'where p.product_id = ep.product_id and p.typeofmerchandise = 4 and ' ||
       chr(10) ||
       '      ep.textcolor_id = cc.color_id and cc.usedforemblem = ''N'' ' ||
       chr(10) || 'order by 1, 2 ',
       'There are product emblems having UsedForEmblem flag unchecked for the text color!',
       'N',
       null,
       'Y');
  
    insert into DBCONSISTENCYCHECK
      (DBCONSISTENCYCHECK_ID,
       DESCRIPTION,
       SQLSTATEMENT,
       ERRORMESSAGE,
       EXACTLYONEROW,
       ADDITIONALERRORMESSAGE,
       ACTIVE)
    values
      (57,
       'If there are product emblems defined check if the border color has the flag UsedForEmblem checked',
       'select p.code productemblemcode, cc.code colorcode' || chr(10) ||
       'from product p, emblemproduct ep, color cc' || chr(10) ||
       'where p.product_id = ep.product_id and p.typeofmerchandise = 4 and ' ||
       chr(10) ||
       '      ep.bordercolor_id= cc.color_id and cc.usedforemblem = ''N'' ' ||
       chr(10) || 'order by 1, 2 ',
       'There are product emblems having UsedForEmblem flag unchecked for the border color!',
       'N',
       null,
       'Y');
  
    insert into DBCONSISTENCYCHECK
      (DBCONSISTENCYCHECK_ID,
       DESCRIPTION,
       SQLSTATEMENT,
       ERRORMESSAGE,
       EXACTLYONEROW,
       ADDITIONALERRORMESSAGE,
       ACTIVE)
    values
      (58,
       'Check if there are wearer inventory lines (circinventory > 0) without linked unique items or non unique items',
       'select c.customernumber, w.wearernumber, p.code productcode, s.code sizecode, f.code finishingmethodcode,
             sum(wi.circinventory) circInv, 
             (select count(n.nonuniqueitem_id)
                           from   nonuniqueitem n
                           where  n.wearerinventory_id = wi.wearerinventory_id and
                                  n.freeitem = ''N'') +
                          (select count(u.uniqueitem_id)
                           from   uniqueitemnonpool u
                           where  u.wearerinventory_id = wi.wearerinventory_id and
                                  u.freeextragarment = ''N'') currentInv
        from wearerinventory wi, weareremployment we, wearer w, customer c, product p, sizedefinition s, finishingmethod f
        where wi.weareremployment_id = we.weareremployment_id and
              we.wearer_id = w.wearer_id and 
              w.customer_id = c.customer_id and
              wi.product_id = p.product_id and
              wi.sizedefinition_id = s.sizedefinition_id and
              wi.finishingmethod_id = f.finishingmethod_id 
        group by c.customernumber, w.wearernumber, p.code , s.code , f.code , wi.wearerinventory_id
        having sum(wi.circinventory) <> (select count(n.nonuniqueitem_id)
                           from   nonuniqueitem n
                           where  n.wearerinventory_id = wi.wearerinventory_id and
                                  n.freeitem = ''N'') +
                          (select count(u.uniqueitem_id)
                           from   uniqueitemnonpool u
                           where  u.wearerinventory_id = wi.wearerinventory_id and
                                  u.freeextragarment = ''N'')  
        order by 1, 2, 3, 4, 5',
       'There are wearer inventory lines (circinventory > 0) without linked unique items or non unique items!',
       'N',
       null,
       'Y');
  --section break for pack_Script
    insert into DBCONSISTENCYCHECK
      (DBCONSISTENCYCHECK_ID,
       DESCRIPTION,
       SQLSTATEMENT,
       ERRORMESSAGE,
       EXACTLYONEROW,
       ADDITIONALERRORMESSAGE,
       ACTIVE)
    values
      (59,
       'Check if a sales journal group exists when turnover group used',
       'select salesjournalgroup_id, code from salesjournalgroup',
       'There is no sales journal group!',
       'Y',
       'OK',
       'Y');
  
      --LCO, todo 9311
      insert into DBCONSISTENCYCHECK
      (DBCONSISTENCYCHECK_ID,
       DESCRIPTION,
       SQLSTATEMENT,
       ERRORMESSAGE,
       EXACTLYONEROW,
       ADDITIONALERRORMESSAGE,
       ACTIVE)
    values
      (60,
       '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!',
       'N',
       null,
       'Y');

--systemsettings
insert into DBCHECKONSYSTEMSETTING
      (DBCONSISTENCYCHECK_ID, NAME, VALUE, TYPEOFVALUE)
    values
      (21, 'TAXDEFINITION', 'EU', 'S');

insert into DBCHECKONSYSTEMSETTING
      (DBCONSISTENCYCHECK_ID, NAME, VALUE, TYPEOFVALUE)
    values
      (21, 'USETURNOVERGROUP', 'Y', 'B');

insert into DBCHECKONSYSTEMSETTING
      (DBCONSISTENCYCHECK_ID, NAME, VALUE, TYPEOFVALUE)
    values
      (30, 'USETURNOVERGROUP', 'Y', 'B');

insert into DBCHECKONSYSTEMSETTING
      (DBCONSISTENCYCHECK_ID, NAME, VALUE, TYPEOFVALUE)
    values
      (33, 'TAXDEFINITION', 'EU', 'S');

insert into DBCHECKONSYSTEMSETTING
      (DBCONSISTENCYCHECK_ID, NAME, VALUE, TYPEOFVALUE)
    values
      (33, 'USETURNOVERGROUP', 'Y', 'B');

insert into dbcheckonsystemsetting values
    ((select db.dbconsistencycheck_id from dbconsistencycheck db where db.description = 'Check for overlapping standard delivery lines'),
     'ALLOWDUPLICATECPL', 'N', 'B');

insert into DBCHECKONSYSTEMSETTING
      (DBCONSISTENCYCHECK_ID, NAME, VALUE, TYPEOFVALUE)
    values
      (26, 'TAXDEFINITION', 'EU', 'S');
