--order 502689
delete dbconsistencycheck db
where db.description = 'Check if turnover group is filled  for invoice line type when UseTurnoverGroup = ''Y''';

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, 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 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 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, 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) ||
       '      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 )'
where db.description = 'Check if for active wearer inventory lines active plpc records exist (also size *)';

commit;

update dbconsistencycheck db
set db.exactlyonerow = 'N'
where db.description='Check if exists records in PLPC with Delayedevenexchange = 7 and Deedeliveryscheme_id = null';

commit;

update dbconsistencycheck db
set db.sqlstatement = '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'
where db.description = 'Check if there is a product list per customer record for unique items in circulation';

commit;





