--order 502689

update dbconsistencycheck db
set db.sqlstatement = 
'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) || '))'
where db.description = 'Correspondence plpc billingmethod with type of merchandise';

commit;

update dbconsistencycheck db
set db.sqlstatement = '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'
where db.description = 'Check if turnover group is defined for invoice line type - when it is a must';

commit;

update dbconsistencycheck db
set db.sqlstatement = '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 
       left outer join productlistpercustomer plpc on (plpc.product_id = wi.product_id and 
                                                     plpc.sizedefinition_id = wi.sizedefinition_id and
                                                     plpc.finishingmethod_id = wi.finishingmethod_id )
       , 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) ||
       '      (plpc.customer_id = w.customer_id or plpc.dateactive is null) 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 and' ||
       chr(10) ||
       '                        sysdate between plpc.dateactive and plpc.dateinactive)'
where db.description = 'Check if for active wearer inventory lines active plpc records exist (also size *)';

commit;

delete dbconsistencycheck db
where db.description = 'Check if there are invoices without VAT line but with taxable lines';

update dbconsistencycheck db
set db.sqlstatement = 'select b.code businessunitcode, b.description, 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, b.description, 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, b.description, 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 3, 1',
 db.description = 'Check if there is a product list per customer record for unique items in circulation'
where db.description = 'Check if there is an active product list per customer record for unique items in circulation';

commit;

update dbconsistencycheck db
set db.sqlstatement = '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'
where db.description = 'Check if there are wearer inventory lines (circinventory > 0) without linked unique items or non unique items';

commit;

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

commit;