--todo 21126

create or replace procedure update_sq is
begin
execute immediate ('create table temp_sq as
                    (select sq.stockreservationpergrade_id,
                           sq.stockroomlocation_id,
                           count(1) qtyUnique
                      from stockquantity            sq,
                           stockroomlocation        srl,
                           stockreservationpergrade srpg,
                           uniqueitemfixedinfo      uifi,
                           uniqueitem               ui,
                           stockdefinitionperitem   sdi
                     where sq.stockreservationpergrade_id = srpg.stockreservationpergrade_id
                       and sdi.stockdefinitionperitem_id = srpg.stockdefinitionperitem_id
                       and sq.stockroomlocation_id = srl.stockroomlocation_id
                       and srl.stockroomlocation_id = uifi.stockroomlocation_id
                       and uifi.uniqueitem_id = ui.uniqueitem_id
                       and ui.product_id = sdi.product_id
                       and ui.sizedefinition_id = sdi.sizedefinition_id
                       and ui.customerowned = sdi.customerowned
                       and sdi.stockroom_id = srl.stockroom_id
                       and srpg.qualitygrade_id = uifi.qualitygrade_id
                       and nvl(ui.relatedcustomer_id, 0) = nvl(sdi.customer_id, 0)
                       and (select count(1)
                              from uniqueitemmodification um, modification m
                             where um.uniqueitem_id = ui.uniqueitem_id
                               and um.modification_id = m.modification_id
                               and m.stockwithstandardsize =''Y'') = 0
                     group by sq.stockreservationpergrade_id, sq.stockroomlocation_id)');
                           
                           
execute immediate ('update stockquantity sq
                       set sq.qtyunique =
                           (select temp_sq.qtyUnique
                              from temp_sq
                             where temp_sq.stockreservationpergrade_id =
                                   sq.stockreservationpergrade_id
                               and temp_sq.stockroomlocation_id = sq.stockroomlocation_id)
                     where (sq.stockreservationpergrade_id, sq.stockroomlocation_id) in
                           (select t.stockreservationpergrade_id, t.stockroomlocation_id
                              from temp_sq t)');
end update_sq;
/
execute update_sq;

commit;

drop procedure update_sq;  
begin                  
     execute immediate ('drop table temp_sq');
exception when others then null;
end;
/

create or replace procedure update_sq is
begin
execute immediate ('create table temp_sq as
                    (select sq.stockreservationpergrade_id,
                           sq.stockroomlocation_id,
                           count(1) qtyUnique
                      from stockquantity            sq,
                           stockroomlocation        srl,
                           stockreservationpergrade srpg,
                           uniqueitemfixedinfo      uifi,
                           uniqueitem               ui,
                           stockdefinitionperitem   sdi
                     where sq.stockreservationpergrade_id = srpg.stockreservationpergrade_id
                       and sdi.stockdefinitionperitem_id = srpg.stockdefinitionperitem_id
                       and sq.stockroomlocation_id = srl.stockroomlocation_id
                       and srl.stockroomlocation_id = uifi.stockroomlocation_id
                       and uifi.uniqueitem_id = ui.uniqueitem_id
                       and ui.product_id = sdi.product_id
                       and ui.sizedefinition_id = sdi.sizedefinition_id
                       and ui.customerowned = sdi.customerowned
                       and sdi.stockroom_id = srl.stockroom_id
                       and srpg.qualitygrade_id = uifi.qualitygrade_id
                       and nvl(ui.relatedcustomer_id, 0) = nvl(sdi.customer_id, 0)
                       and (select count(1)
                              from uniqueitemmodification um, modification m
                             where um.uniqueitem_id = ui.uniqueitem_id
                               and um.modification_id = m.modification_id
                               and m.stockwithstandardsize = ''Y'') > 0
                     group by sq.stockreservationpergrade_id, sq.stockroomlocation_id)');
                           
                           
execute immediate ('update stockquantity sq
                       set sq.qtyuniquewithmodifications =
                           (select temp_sq.qtyUnique
                              from temp_sq
                             where temp_sq.stockreservationpergrade_id =
                                   sq.stockreservationpergrade_id
                               and temp_sq.stockroomlocation_id = sq.stockroomlocation_id)
                     where (sq.stockreservationpergrade_id, sq.stockroomlocation_id) in
                           (select t.stockreservationpergrade_id, t.stockroomlocation_id
                              from temp_sq t)');
                    
end update_sq;
/

execute update_sq;

commit;

drop procedure update_sq;  

begin                  
     execute immediate ('drop table temp_sq');
exception when others then null;
end;
/
