-- ABS-17584 / ABS-69162
-------   INVOICEHEADEREXTRAINFO   --------
ALTER TABLE INVOICEHEADEREXTRAINFO ADD (SENDINVOICEBY NUMBER DEFAULT 0 NOT NULL 
                                          CONSTRAINT SENDINVOICEBY_VALID2_1 CHECK (SENDINVOICEBY IN (0,1,2,3)));

COMMENT ON COLUMN INVOICEHEADEREXTRAINFO.SENDINVOICEBY IS '0 = Send by Mail
1 = Send by Fax
2 = Send by E-mail
3 = Send by EDI';

create or replace procedure update_sendinvoiceby as
begin
  for x in (select /*+ index(cbi  XPKCUSTOMERBILLINGINFO)*/
             cbi.sendinvoiceby, ih.invoiceheader_id
             from customerbillinginfo cbi, INVOICEHEADER IH
             where cbi.customer_id = ih.billtocustomer_link_id
             and cbi.sendinvoiceby <> 0
            ) loop
    begin
      update invoiceheaderextrainfo ihei
         set ihei.sendinvoiceby = x.sendinvoiceby
       where ihei.invoiceheader_id = x.invoiceheader_id;
      commit; --rollback
    exception
      when others then
        rollback;
    end;
  end loop;
end update_sendinvoiceby;
/

execute update_sendinvoiceby;
drop procedure update_sendinvoiceby;