-- ABS-83374/ ABS-83675

create or replace procedure updategarmentrentspecification is
begin
  for x in (select ccgc.contract_id, ss.value from contractcomponentgrmcharges ccgc
                join contract ct on ccgc.contract_id = ct.contract_id
                join customer c on ct.customer_link_id = c.customer_id
                join businessunit bu on c.defaultbusinessunit_id = bu.businessunit_id
                join plant p on bu.plant_id = p.plant_id
                join systemsetting ss on p.plant_id = ss.plant_id
                where ss.name = 'SETTLEMENTACTIVE'
                and ccgc.garmentrentspecification is null) loop

                if x.value = 'Y' then
                begin
                  begin
                    update contractcomponentgrmcharges ccgc1 set ccgc1.garmentrentspecification = 1
                    where ccgc1.contract_id = x.contract_id;
                  commit;
                  exception when others then
                    dbms_output.put(sqlerrm);
                  end;
                  end loop;
                else
                  begin
                    update contractcomponentgrmcharges ccgc1 set ccgc1.garmentrentspecification = 4
                    where ccgc1.contract_id = x.contract_id;
                    commit;
                  exception when others then
                    dbms_output.put_line(sqlerrm);
                  end;
               end if;
  end loop;
end updategarmentrentspecification;
/

execute updategarmentrentspecification;

drop procedure updategarmentrentspecification;
