--
create index XINSTALLARTRANS01 on ARTRANSACTION (ARTRANSACTIONTYPE_ID, NVL(ORIGINALTRANSACTION_ID,0));
create index XINSTALLARTRANS02 on ARTRANSACTION (NVL(INVOICENUMBER,-1), ARTRANSACTIONDATE, BILLTOCUSTOMER_LINK_ID);

-- Create temporary indexes
create index HXSPEED4ARTRANS on ARTRANSACTION (TO_CHAR(ARTRANSACTIONDATE,'YYYYMM'), ARTRANSACTIONTYPE_ID)
  pctfree 10
  initrans 2
  maxtrans 255
  storage
  (
    initial 64K
    next 1M
    minextents 1
    maxextents unlimited
  );

create index HXSPEED5ARTRANS on ARTRANSACTION (nvl(remitidentification,-1), BILLTOCUSTOMER_LINK_ID, debitcredit)
  pctfree 10
  initrans 2
  maxtrans 255
  storage
  (
    initial 64K
    next 1M
    minextents 1
    maxextents unlimited
  );

create index HXSPEED6ARTRANS on ARTRANSACTION (ARTRANSACTIONTYPE_ID, (NVL(CLOSINGTRANSACTION_ID,(-1))))
  pctfree 10
  initrans 2
  maxtrans 255
  storage
  (
    initial 64K
    next 1M
    minextents 1
    maxextents unlimited
  );

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

-- Create temporary table
create table ARTEMP
(
  ARTRANSACTION_ID       NUMBER not null,
  ORIGINALTRANSACTION_ID NUMBER not null
)
  pctfree 10
  initrans 1
  maxtrans 255
  storage
  (
    initial 64K
    next 1M
    minextents 1
    maxextents unlimited
  );

-- Create/Recreate primary, unique and foreign key constraints
alter table ARTEMP
  add constraint XPKARTEMP primary key (ARTRANSACTION_ID)
  using index
  pctfree 10
  initrans 2
  maxtrans 255
  storage
  (
    initial 64K
    next 1M
    minextents 1
    maxextents unlimited
  );

-- Disable trigger  IMPORTANT !!
begin    
    execute immediate ('ALTER TRIGGER AMM_AR_LOG_TGR DISABLE');
exception
   when others then null;
end;
/

create or replace function mywriteabslog(PARAM_MESSAGE IN VARCHAR2, PARAM_CALLER IN VARCHAR2) return number is
v_abslodId number;
begin
  insert into ABSLOG
    select seq_abslog.nextval,
           5,
           PARAM_CALLER,
           null,
           1,
           SYSDATE,
           0,
           PARAM_MESSAGE
      from dual;
   commit;
   select seq_abslog.currval into v_abslodId from dual;
   return(v_abslodId);
end mywriteabslog;
/

create or replace procedure myupdateabslog(PARAM_ID IN NUMBER,
                       PARAM_UPDATEDROWS IN NUMBER) is
begin
  update ABSLOG a set a.message = a.message || ' ' || to_char(PARAM_UPDATEDROWS) || ' Rows'
   where a.abslog_id = PARAM_ID;
  commit;
end myupdateabslog;
/

create or replace procedure mycommit(log_id number) is
updated number;
begin
 updated := SQL%ROWCOUNT;
 commit;
 myupdateabslog(log_id, updated);
end mycommit;
/

create or replace procedure update_remitID_open_diff_cred(fp_extension varchar2, log_id out number) is
updated number;
begin
    updated:=1;
    while updated>0 loop
      log_id := mywriteabslog('Add missing extension ' ||fp_extension || ' to remitID for open difference credits', 'UPDATE_ORG_TRANS');
      update artransaction a
         set a.remitidentification = a.remitidentification || fp_extension,
             a.remark = substr(a.remark || '(' || fp_extension || ')',1,30)
      where a.artransaction_id in 
      (
      select (select min(ar1.artransaction_id) 
                from artransaction ar1
               where ar1.billtocustomer_link_id = a.billtocustomer_link_id
                 and ar1.artransactiondate = a.artransactiondate
                 and ar1.debitcredit = 'C'
                 and ar1.artransactiontype_id = 6
                 and ar1.remitidentification  || fp_extension = a.remitidentification
                 and ar1.invoiceheader_id = a.invoiceheader_id                 
                 and ar1.amount = a.amount - a.closingamount
                 and ar1.artransaction_id <> a.artransaction_id) opendiff_id
        from artransaction a
       where a.remitidentification like '%' || fp_extension
         and a.debitcredit = 'C'
         and a.amount <> a.closingamount
         and exists
       (select 1
                from artransaction ar
               where ar.billtocustomer_link_id = a.billtocustomer_link_id
                 and ar.artransactiondate = a.artransactiondate
                 and ar.debitcredit = 'C'
                 and a.remitidentification = ar.remitidentification || fp_extension
                 and ar.invoiceheader_id = a.invoiceheader_id
                 and ar.artransaction_id <> a.artransaction_id)
      and exists
       (select 1
                from artransaction ar1
               where ar1.billtocustomer_link_id = a.billtocustomer_link_id
                 and ar1.artransactiondate = a.artransactiondate
                 and ar1.debitcredit = 'C'
                 and ar1.artransactiontype_id = 6
                 and ar1.remitidentification  || fp_extension = a.remitidentification
                 and ar1.invoiceheader_id = a.invoiceheader_id                 
                 and ar1.amount = a.amount - a.closingamount
                 and ar1.artransaction_id <> a.artransaction_id)
      and not exists
       (select 1
                from artransaction ar1
               where ar1.billtocustomer_link_id = a.billtocustomer_link_id
                 and ar1.artransactiondate = a.artransactiondate
                 and ar1.debitcredit = 'C'
                 and ar1.artransactiontype_id = 6
                 and ar1.remitidentification = a.remitidentification
                 and ar1.amount = a.amount - a.closingamount
                 and ar1.artransaction_id <> a.artransaction_id) );
      updated := SQL%ROWCOUNT;
      mycommit(log_id);
    end loop;
end update_remitID_open_diff_cred; 
/

create or replace procedure update_remitID_discount_chg(fp_extension varchar2, log_id out number) is
begin
    log_id := mywriteabslog('Add missing extension ' ||fp_extension || ' to remitID for open discount charge', 'UPDATE_ORG_TRANS');
    update artransaction a
       set a.remitidentification = a.remitidentification || fp_extension,
           a.remark = substr(a.remark || '(' || fp_extension || ')',1,30)
    where a.artransaction_id in 
    (
    select (select min(ar1.artransaction_id) 
              from artransaction ar1
             where ar1.billtocustomer_link_id = a.billtocustomer_link_id
               and ar1.artransactiondate = a.artransactiondate
               and ar1.debitcredit = 'D'
               and ar1.artransactiontype_id = 4
               and ar1.remitidentification  || fp_extension = a.remitidentification
               and ar1.invoiceheader_id = a.invoiceheader_id                 
               and ar1.amount = a.amount - a.closingamount
               and ar1.artransaction_id <> a.artransaction_id) opendiff_id
      from artransaction a
     where a.remitidentification like '%' || fp_extension
       and a.debitcredit = 'C'
       and a.amount <> a.closingamount
       and exists
     (select 1
              from artransaction ar
             where ar.billtocustomer_link_id = a.billtocustomer_link_id
               and ar.artransactiondate = a.artransactiondate
               and ar.debitcredit = 'C'
               and a.remitidentification = ar.remitidentification || fp_extension
               and ar.invoiceheader_id = a.invoiceheader_id
               and ar.artransaction_id <> a.artransaction_id)
    and exists
     (select 1
              from artransaction ar1
             where ar1.billtocustomer_link_id = a.billtocustomer_link_id
               and ar1.artransactiondate = a.artransactiondate
               and ar1.debitcredit = 'D'
               and ar1.artransactiontype_id = 4
               and ar1.remitidentification  || fp_extension = a.remitidentification
               and ar1.invoiceheader_id = a.invoiceheader_id                 
               and ar1.amount = a.amount - a.closingamount
               and ar1.artransaction_id <> a.artransaction_id)
    and not exists
     (select 1
              from artransaction ar1
             where ar1.billtocustomer_link_id = a.billtocustomer_link_id
               and ar1.artransactiondate = a.artransactiondate
               and ar1.debitcredit = 'D'
               and ar1.artransactiontype_id = 4
               and ar1.remitidentification = a.remitidentification
               and ar1.amount = a.amount - a.closingamount
               and ar1.artransaction_id <> a.artransaction_id) );
    mycommit(log_id);
end update_remitID_discount_chg; 
/

create or replace procedure UPDATE_ORG_TRANS1 is
log_id  number;
begin
    log_id := mywriteabslog('START UPDATE_ORG_TRANS.', 'UPDATE_ORG_TRANS');

    -- insert new artransactiontype for converted open differences to give them their own transaction
    INSERT INTO artransactiontype
            (artransactiontype_id,
              typenumber,
              debitcredit,
              code,
              legacycode,
              predefined,
              factorydescription,
              systemuser_id,
              timestamp)
      select 0,
             0,
             'D',
             'CNV',
             '',
             'Y',
             'Converted open invoice/charge',
             1,
             sysdate
        from dual 
       where not exists (select 1 from artransactiontype a where a.artransactiontype_id = 0);
    commit;
    INSERT INTO artransactiontype_desc
               (artransactiontype_id,
                language_id,
                description,
                systemuser_id,
                timestamp)
        select 0,
               s.language_id,
               'Converted open invoice/charge',
               1,
               sysdate
          from languages s 
         where s.translatedescription='Y'
           and not exists (select 1 from artransactiontype_desc a where a.artransactiontype_id = 0);
    commit;

    -- Do some cleanup first
    -- update amount and closing amount to update amounts like 5.89999999999999   to 5.90
    log_id := mywriteabslog('Clean up unrounded closing amounts', 'UPDATE_ORG_TRANS');
    update artransaction
       set closingamount = round(closingamount*100)/100
     where closingamount <> round(closingamount*100)/100;
    mycommit(log_id);

    log_id := mywriteabslog('Clean up unrounded amounts', 'UPDATE_ORG_TRANS');
    update artransaction
       set amount = round(amount*100)/100
     where amount <> round(amount*100)/100;
    mycommit(log_id);

    -- open difference credits that had wrong transaction type
    log_id := mywriteabslog('Update open difference credits that have wrong transactiontype', 'UPDATE_ORG_TRANS');
    update artransaction a
       set a.artransactiontype_id = 6
     where a.artransactiontype_id = 2
       and a.debitcredit = 'C';
    mycommit(log_id);

    -- open differences that had wrong transaction type
    log_id := mywriteabslog('Update open differences that have wrong transactiontype', 'UPDATE_ORG_TRANS');
    update artransaction a
       set a.artransactiontype_id = 2
     where a.artransactiontype_id = 6
       and a.debitcredit = 'D';
    mycommit(log_id);

    -- set debitcredit according to transactiontype
    log_id := mywriteabslog('Set debit/credit according to transactiontype', 'UPDATE_ORG_TRANS');
    update artransaction a
       set a.debitcredit = (select at.debitcredit from artransactiontype at where at.artransactiontype_id=a.artransactiontype_id)
     where a.debitcredit <> (select at.debitcredit from artransactiontype at where at.artransactiontype_id=a.artransactiontype_id);
    mycommit(log_id);

    -- update open difference credits, that have wrong remit ID (they miss the extension that prevents duplicates)
    update_remitID_open_diff_cred('-1', log_id);    
    update_remitID_open_diff_cred('-2', log_id);    
    update_remitID_open_diff_cred('-3', log_id);  
    -- same for discount charge
    update_remitID_discount_chg('-1', log_id);
    update_remitID_discount_chg('-2', log_id);
    update_remitID_discount_chg('-3', log_id);
    -- End clean up
end UPDATE_ORG_TRANS1;
/

create or replace procedure UPDATE_ORG_TRANS2 is
log_id  number;
    cursor vv is
      select distinct to_char(a.artransactiondate,'YYYYMM') mnth
        from artransaction a
    order by to_char(a.artransactiondate,'YYYYMM');
begin
    for v in vv loop
      -- invoices
      log_id := mywriteabslog('Update Invoices, Statements and Payments for month ' || v.mnth, 'UPDATE_ORG_TRANS');
      update artransaction a set a.originaltransaction_id=a.artransaction_id
       where a.artransactiontype_id = 1
         and to_char(a.artransactiondate,'YYYYMM') = v.mnth;
      -- statements
      update artransaction a set a.originaltransaction_id=a.artransaction_id
       where a.artransactiontype_id = 3
         and to_char(a.artransactiondate,'YYYYMM') = v.mnth;
      -- payments
      update artransaction a set a.originaltransaction_id=a.artransaction_id
       where a.artransactiontype_id = 9
         and to_char(a.artransactiondate,'YYYYMM') = v.mnth;
      mycommit(log_id);
    end loop;

    -- Update Miscellaneous credits linked to invoices
    log_id := mywriteabslog('Update miscellaneous credits linked to invoices', 'UPDATE_ORG_TRANS');
    update artransaction ar
       set ar.originaltransaction_id = (select min(b.artransaction_id)
                                          from artransaction b
                                         where b.artransactiontype_id <= 1
                                           and b.invoicenumber = ar.invoicenumber)
     where ar.artransaction_id in
           (select a.artransaction_id
              from artransaction a, artransactiontype art
             where a.debitcredit = 'C'
               and art.artransactiontype_id = a.artransactiontype_id
               and art.predefined = 'N'
               and a.invoicenumber <> 0
               and exists (select 1
                      from artransaction b
                     where b.artransactiontype_id <= 1
                       and b.invoicenumber = a.invoicenumber)
               and exists
             (select 1
                      from artransaction b
                     where b.artransactiontype_id <= 2
                       and b.invoicenumber = a.invoicenumber
                       and b.amount - b.closingamount = a.amount));
    mycommit(log_id);  

    -- other miscelleneous transactions
    log_id := mywriteabslog('Update other miscellaneous transactions', 'UPDATE_ORG_TRANS');
    update  /*+index(a XINSTALLARTRANS01)*/ artransaction a set a.originaltransaction_id=a.artransaction_id
       where nvl(a.originaltransaction_id,0)=0
         and a.artransactiontype_id in
       (select t.artransactiontype_id
          from artransactiontype t
         where t.predefined = 'N');
    mycommit(log_id);

   -- Correct 0 closing amount for discounts to amount
   log_id := mywriteabslog('Correct 0 closing amount for discounts to amount', 'UPDATE_ORG_TRANS');
   update artransaction ar set ar.closingamount = ar.amount, ar.remark = substr(ar.remark + '(#)',1,30)
    where ar.artransaction_id in
    (select /*+index(a XINSTALLARTRANS01) index(c XINSTALLARTRANS01)*/
            c.artransaction_id from artransaction a, artransaction c
      where c.artransaction_id = a.closingtransaction_id
        and a.artransactiontype_id = 4 -- discount charge
        and c.artransactiontype_id = 8 -- discount
        and nvl(a.originaltransaction_id,0)=0
        and nvl(c.originaltransaction_id,0)=0
        and a.amount = c.amount
        and c.closingamount = 0
        and a.amount = a.closingamount);
    mycommit(log_id);
end UPDATE_ORG_TRANS2;
/

create or replace procedure UPDATE_ORG_TRANS3 is
log_id  number;
begin
    -- discounts that are unapplied
    log_id := mywriteabslog('Update discounts that are unapplied', 'UPDATE_ORG_TRANS');
    update artransaction ar set ar.originaltransaction_id = ar.artransaction_id
    where ar.artransaction_id in
     (select a.artransaction_id from artransaction a, artransaction c
      where c.artransaction_id = a.closingtransaction_id
        and a.artransactiontype_id = 4 -- discount charge
        and c.artransactiontype_id = 8 -- discount
        and a.amount = a.closingamount
        and c.amount = c.closingamount
        and a.amount = c.amount);
    mycommit(log_id);

    -- discounts that are created during unapply
    log_id := mywriteabslog('Update discounts that are created during unapply', 'UPDATE_ORG_TRANS');
    update artransaction ar set ar.originaltransaction_id = ar.artransaction_id
    where ar.artransaction_id in
     (select c.artransaction_id from artransaction a, artransaction c
      where c.artransaction_id = a.closingtransaction_id
        and a.artransactiontype_id = 4 -- discount charge
        and c.artransactiontype_id = 8 -- discount
        and a.amount = a.closingamount
        and c.amount = c.closingamount
        and a.amount = c.amount);
    mycommit(log_id);

    -- discounts closing a discount charge with 0 amount
    log_id := mywriteabslog('Update discount that are unapplied with 0 amount (!)', 'UPDATE_ORG_TRANS');
    update artransaction ar set ar.originaltransaction_id = ar.artransaction_id
    where ar.artransaction_id in
     (select /*+index(a XINSTALLARTRANS01)*/ c.artransaction_id from artransaction a, artransaction c
      where c.artransaction_id = a.closingtransaction_id
        and a.artransactiontype_id = 4 -- discount charge
        and c.artransactiontype_id = 8 -- discount
        and nvl(a.originaltransaction_id,0)=0
        and a.amount = 0
        and c.amount = c.closingamount
        and a.closingamount = c.closingamount);
    mycommit(log_id);

    -- discounts charges with 0 amount. 0.00 amount probably not correct, consider updating to closing amount (!!!!)
    log_id := mywriteabslog('Update discount charges that are unapplied  with 0 amount (!)', 'UPDATE_ORG_TRANS');
    update artransaction ar set ar.originaltransaction_id = ar.artransaction_id,
                                    ar.remark = substr(ar.remark + '(!)',1,30)
    where ar.artransaction_id in
     (select  /*+index(a XINSTALLARTRANS01)*/ a.artransaction_id from artransaction a, artransaction c
      where c.artransaction_id = a.closingtransaction_id
        and a.artransactiontype_id = 4 -- discount charge
        and c.artransactiontype_id = 8 -- discount
        and nvl(a.originaltransaction_id,0)=0
        and a.amount = 0
        and c.amount = c.closingamount
        and a.closingamount = c.closingamount);
    mycommit(log_id);
end UPDATE_ORG_TRANS3;
/

create or replace procedure UPDATE_ORG_TRANS4 is
log_id  number;
begin
    -- open differences from invoices and statements (based on invoiceheader_ID) getting org_id from themselves
    -- because invoice or statement transaction does not exist (converted partly paid invoices)
    -- !! ALSO UPDATE THEIR TRANSACTION TYPE TO 0 !!
    log_id := mywriteabslog('Update (converted) open differences on invoiceheader_ID which get there own original ID', 'UPDATE_ORG_TRANS');
    update artransaction ar
       set ar.originaltransaction_id = ar.artransaction_id,
           ar.artransactiontype_id = 0
     where ar.artransactiontype_id=2
       and ar.artransaction_id in
     (select  /*+index(a XINSTALLARTRANS01)*/ a.artransaction_id
        from artransaction a
       where a.artransactiontype_id=2 -- open difference
         and a.invoiceheader_id is not null
         and not exists (select 1 from artransaction i2 where i2.invoiceheader_id=a.invoiceheader_id and i2.artransaction_id < a.artransaction_id)
         and nvl(a.originaltransaction_id,0)=0);
    mycommit(log_id);

    -- open differences from invoices and statements (based on invoiceheader_ID) getting org_id from invoices or statement or misc
    log_id := mywriteabslog('Update open differences based on invoiceheader_ID', 'UPDATE_ORG_TRANS');
    -- step 1 write in temp table
    insert into artemp (artransaction_id, originaltransaction_id)
    select a.artransaction_id,
           (select min(i.artransaction_id)
              from artransaction i
             where i.invoiceheader_id = a.invoiceheader_id
               and i.artransaction_id = i.originaltransaction_id
               and i.debitcredit= 'D') org_id
      from artransaction a
     where a.artransactiontype_id = 2 -- open difference
       and a.invoiceheader_id is not null
       and exists (select 1
              from artransaction i2
             where i2.invoiceheader_id = a.invoiceheader_id
               and i2.artransaction_id = i2.originaltransaction_id
               and i2.debitcredit= 'D');
     commit;          
end UPDATE_ORG_TRANS4;
/

--section break for pack_Script

create or replace procedure UPDATE_ORG_TRANS5 is
log_id  number;
begin
    -- step 2 update artransaction table
    update artransaction a
       set a.originaltransaction_id = (select ar.originaltransaction_id
                                         from artemp ar
                                        where ar.artransaction_id =
                                              a.artransaction_id)
     where a.artransaction_id in (select at.artransaction_id from artemp at);
    mycommit(log_id);
    execute immediate 'TRUNCATE TABLE "ARTEMP"';

    -- reconnect open differences from invoices and statements which were connected wrong in prev. step because of
    -- duplicate entries
    log_id := mywriteabslog('Reconnect open differences based on invoiceheader_ID due to duplicate invoice transactions', 'UPDATE_ORG_TRANS');
    -- step 1 write in temp table
    insert into artemp (artransaction_id, originaltransaction_id)
    select
       (select min(c.artransaction_id)
          from artransaction c 
         where c.invoiceheader_id = a.invoiceheader_id 
           and c.artransactiontype_id = 2 
           and c.amount = b.amount - b.closingamount and c.originaltransaction_id=a.originaltransaction_id) ar_ID,
        b.originaltransaction_id org_id
      from artransaction a, artransaction b
     where a.invoiceheader_id = b.invoiceheader_id
       and a.artransactiontype_id = 1
       and b.artransactiontype_id = 1
       and b.closingamount <> b.amount
       and a.amount = a.closingamount
       and exists 
       (select 1 
          from artransaction c 
         where c.invoiceheader_id = a.invoiceheader_id 
           and c.artransactiontype_id = 2 
           and c.amount = b.amount - b.closingamount and c.originaltransaction_id=a.originaltransaction_id);
    commit;
    -- step 2 update artransaction table
    update artransaction a
       set a.originaltransaction_id = (select ar.originaltransaction_id
                                         from artemp ar
                                        where ar.artransaction_id =
                                              a.artransaction_id)
     where a.artransaction_id in (select at.artransaction_id from artemp at);
    mycommit(log_id);
    execute immediate 'TRUNCATE TABLE "ARTEMP"';

    -- discounts from invoices and statements (based on invoiceheader_ID) getting org_id from invoices or statement
    log_id := mywriteabslog('Update discounts based on invoiceheader_ID', 'UPDATE_ORG_TRANS');
    -- step 1 write in temp table
    insert into artemp (artransaction_id, originaltransaction_id)
    select  /*+index(a XINSTALLARTRANS01)*/ a.artransaction_id,
           (select min(i.artransaction_id)
              from artransaction i
             where i.invoiceheader_id = a.invoiceheader_id
               and i.artransaction_id=i.originaltransaction_id
               and i.debitcredit= 'D') org_id
      from artransaction a
     where a.artransactiontype_id = 8 -- discount
       and a.invoiceheader_id is not null
       and nvl(a.originaltransaction_id,0)=0
       and exists (select 1
              from artransaction i2
             where i2.invoiceheader_id = a.invoiceheader_id
               and i2.artransaction_id=i2.originaltransaction_id
               and i2.debitcredit= 'D');
    commit;
    -- step 2 update artransaction table
    update artransaction a
       set a.originaltransaction_id = (select ar.originaltransaction_id
                                         from artemp ar
                                        where ar.artransaction_id =
                                              a.artransaction_id)
     where a.artransaction_id in (select at.artransaction_id from artemp at);
    mycommit(log_id);
    execute immediate 'TRUNCATE TABLE "ARTEMP"';
end UPDATE_ORG_TRANS5;
/

create or replace procedure UPDATE_ORG_TRANS6 is
log_id  number;
begin
    -- open differences credit from payments and misc credits where 1 parent can be found
    log_id := mywriteabslog('Update open differences credits from payments and misc credits where 1 parent can be found', 'UPDATE_ORG_TRANS');
    -- step 1 write in temp table
    insert into artemp (artransaction_id, originaltransaction_id)
    select  /*+index(a XINSTALLARTRANS01)*/ a.artransaction_id,
         (select min(p.artransaction_id)
              from artransaction p
             where nvl(p.remitidentification,-1) = nvl(a.remitidentification, -1)
               and p.artransactiondate = a.artransactiondate
               and p.billtocustomer_link_id = a.billtocustomer_link_id
               and p.debitcredit = 'C'
               and p.artransaction_id = p.originaltransaction_id) org_id
      from artransaction a
     where a.artransactiontype_id=6 -- open difference credit
       and nvl(a.originaltransaction_id,0)=0
       and
         (select min(p2.artransaction_id)
            from artransaction p2
           where nvl(p2.remitidentification,-1) = nvl(a.remitidentification, -1)
             and p2.artransactiondate = a.artransactiondate
             and p2.billtocustomer_link_id = a.billtocustomer_link_id
             and p2.debitcredit = 'C'
             and p2.artransaction_id = p2.originaltransaction_id) =
         (select max(p3.artransaction_id)
            from artransaction p3
           where nvl(p3.remitidentification,-1) = nvl(a.remitidentification, -1)
             and p3.artransactiondate = a.artransactiondate
             and p3.billtocustomer_link_id = a.billtocustomer_link_id
             and p3.debitcredit = 'C'
             and p3.artransaction_id = p3.originaltransaction_id);
    commit;
    -- step 2 update artransaction table
    update artransaction a
       set a.originaltransaction_id = (select ar.originaltransaction_id
                                         from artemp ar
                                        where ar.artransaction_id =
                                              a.artransaction_id)
     where a.artransaction_id in (select at.artransaction_id from artemp at);
    mycommit(log_id);
    execute immediate 'TRUNCATE TABLE "ARTEMP"';
end UPDATE_ORG_TRANS6;
/

create or replace procedure UPDATE_ORG_TRANS7 is
log_id  number;
begin
    -- discount charge (small balance write off) from payments and misc credits where 1 parent can be found
    log_id := mywriteabslog('Update discount charge from payments and misc credits where 1 parent can be found', 'UPDATE_ORG_TRANS');
    -- step 1 write in temp table
    insert into artemp (artransaction_id, originaltransaction_id)
    select  /*+index(a XINSTALLARTRANS01)*/ a.artransaction_id,
           (select min(p.artransaction_id)
              from artransaction p
             where nvl(p.remitidentification,-1) = nvl(a.remitidentification, -1)
               and p.artransactiondate = a.artransactiondate
               and p.billtocustomer_link_id = a.billtocustomer_link_id
               and p.debitcredit = 'C'
               and p.artransaction_id = p.originaltransaction_id) org_id
      from artransaction a
     where a.artransactiontype_id=4 -- discount charge
       and nvl(a.originaltransaction_id,0)=0
       and
         (select min(p2.artransaction_id)
            from artransaction p2
           where nvl(p2.remitidentification,-1) = nvl(a.remitidentification, -1)
             and p2.artransactiondate = a.artransactiondate
             and p2.billtocustomer_link_id = a.billtocustomer_link_id
             and p2.debitcredit = 'C'
             and p2.artransaction_id = p2.originaltransaction_id) =
         (select max(p3.artransaction_id)
            from artransaction p3
           where nvl(p3.remitidentification,-1) = nvl(a.remitidentification, -1)
             and p3.artransactiondate = a.artransactiondate
             and p3.billtocustomer_link_id = a.billtocustomer_link_id
             and p3.debitcredit = 'C'
             and p3.artransaction_id = p3.originaltransaction_id);
    commit;
    -- step 2 update artransaction table
    update artransaction a
       set a.originaltransaction_id = (select ar.originaltransaction_id
                                         from artemp ar
                                        where ar.artransaction_id =
                                              a.artransaction_id)
     where a.artransaction_id in (select at.artransaction_id from artemp at);
    mycommit(log_id);
    execute immediate 'TRUNCATE TABLE "ARTEMP"';
end UPDATE_ORG_TRANS7;
/

create or replace procedure UPDATE_ORG_TRANS8 is
log_id  number;
begin
    -- discount charge (small balance write off) from payments and misc credits where 1 parent can be found for other date
    log_id := mywriteabslog('Update discount charge from payments and misc credits where 1 parent can be found for other date', 'UPDATE_ORG_TRANS');
    -- step 1 write in temp table
    insert into artemp (artransaction_id, originaltransaction_id)
    select  /*+index(a XINSTALLARTRANS01)*/  a.artransaction_id,
         (select /*+ index(p XIF948ARTRANSACTION) */ min(p.artransaction_id)
           from artransaction p
               where nvl(p.remitidentification,-1) = nvl(a.remitidentification, -1)
                 and nvl(p.invoicenumber,-1) = nvl(a.invoicenumber,-1)
                 and p.billtocustomer_link_id = a.billtocustomer_link_id
                 and p.debitcredit = 'C'
                 and p.artransaction_id = p.originaltransaction_id
                 and p.artransaction_id < a.artransaction_id)
          from artransaction a
         where a.artransactiontype_id=4 -- discount charge
           and nvl(a.originaltransaction_id,0)=0
           and
           (select /*+ index(p2 XIF948ARTRANSACTION) */  min(p2.artransaction_id)
              from artransaction p2
             where nvl(p2.remitidentification,-1) = nvl(a.remitidentification, -1)
               and nvl(p2.invoicenumber,-1) = nvl(a.invoicenumber,-1)
               and p2.billtocustomer_link_id = a.billtocustomer_link_id
               and p2.debitcredit = 'C'
               and p2.artransaction_id = p2.originaltransaction_id
               and p2.artransaction_id < a.artransaction_id) =
           (select /*+ index(p3 XIF948ARTRANSACTION) */ max(p3.artransaction_id)
              from artransaction p3
             where nvl(p3.remitidentification,-1) = nvl(a.remitidentification, -1)
               and nvl(p3.invoicenumber,-1) = nvl(a.invoicenumber,-1)
               and p3.billtocustomer_link_id = a.billtocustomer_link_id
               and p3.debitcredit = 'C'
               and p3.artransaction_id = p3.originaltransaction_id
               and p3.artransaction_id < a.artransaction_id);
    commit;
    -- step 2 update artransaction table
    update artransaction a
       set a.originaltransaction_id = (select ar.originaltransaction_id
                                         from artemp ar
                                        where ar.artransaction_id =
                                              a.artransaction_id)
     where a.artransaction_id in (select at.artransaction_id from artemp at);
    mycommit(log_id);
    execute immediate 'TRUNCATE TABLE "ARTEMP"';
end UPDATE_ORG_TRANS8;
/

create or replace procedure UPDATE_ORG_TRANS9 is
log_id  number;
begin    
    -- open differences where 1 parent can be found
    log_id := mywriteabslog('Update open differences where 1 parent can be found, invoiceheader_id null', 'UPDATE_ORG_TRANS');
    update artransaction ar
       set ar.originaltransaction_id = (select min(p.artransaction_id)
                                         from artransaction p
                                             where nvl(p.invoicenumber,-1) = nvl(ar.invoicenumber, -1)
                                               and p.artransactiondate = ar.artransactiondate
                                               and p.billtocustomer_link_id = ar.billtocustomer_link_id
                                               and p.debitcredit = 'D'
                                               and p.invoiceheader_id is null
                                               and p.artransaction_id = p.originaltransaction_id
                                               and p.artransaction_id < ar.artransaction_id)
     where ar.artransaction_id in
     (select  /*+index(a XINSTALLARTRANS01)*/ a.artransaction_id
        from artransaction a
       where a.artransactiontype_id=2 -- open difference
         and a.invoiceheader_id is null
         and nvl(a.originaltransaction_id,0)=0
         and
         (select min(p2.artransaction_id)
            from artransaction p2
           where nvl(p2.invoicenumber,-1) = nvl(a.invoicenumber, -1)
             and p2.artransactiondate = a.artransactiondate
             and p2.billtocustomer_link_id = a.billtocustomer_link_id
             and p2.debitcredit = 'D'
             and p2.invoiceheader_id is null
             and p2.artransaction_id = p2.originaltransaction_id
             and p2.artransaction_id < a.artransaction_id) =
         (select max(p3.artransaction_id)
            from artransaction p3
           where nvl(p3.invoicenumber,-1) = nvl(a.invoicenumber, -1)
             and p3.artransactiondate = a.artransactiondate
             and p3.billtocustomer_link_id = a.billtocustomer_link_id
             and p3.debitcredit = 'D'
             and p3.invoiceheader_id is null
             and p3.artransaction_id = p3.originaltransaction_id
             and p3.artransaction_id < a.artransaction_id));
    mycommit(log_id);

    -- open differences where 1 parent can be found
    log_id := mywriteabslog('Update open differences where 1 parent can be found', 'UPDATE_ORG_TRANS');
    update artransaction ar
       set ar.originaltransaction_id = (select min(p.artransaction_id)
                                         from artransaction p
                                             where nvl(p.invoicenumber,-1) = nvl(ar.invoicenumber, -1)
                                               and p.artransactiondate = ar.artransactiondate
                                               and p.billtocustomer_link_id = ar.billtocustomer_link_id
                                               and p.debitcredit = 'D'
                                               and p.artransaction_id = p.originaltransaction_id)
     where ar.artransaction_id in
     (select  /*+index(a XINSTALLARTRANS01)*/ a.artransaction_id
        from artransaction a
       where a.artransactiontype_id=2 -- open difference
         and nvl(a.originaltransaction_id,0)=0
         and
         (select min(p2.artransaction_id)
            from artransaction p2
           where nvl(p2.invoicenumber,-1) = nvl(a.invoicenumber, -1)
             and p2.artransactiondate = a.artransactiondate
             and p2.billtocustomer_link_id = a.billtocustomer_link_id
             and p2.debitcredit = 'D'
             and p2.artransaction_id = p2.originaltransaction_id) =
         (select max(p3.artransaction_id)
            from artransaction p3
           where nvl(p3.invoicenumber,-1) = nvl(a.invoicenumber, -1)
             and p3.artransactiondate = a.artransactiondate
             and p3.billtocustomer_link_id = a.billtocustomer_link_id
             and p3.debitcredit = 'D'
             and p3.artransaction_id = p3.originaltransaction_id));
    mycommit(log_id);
end UPDATE_ORG_TRANS9;
/

create or replace procedure UPDATE_ORG_TRANS10 is
log_id  number;
begin
    -- discounts where 1 parent can be found
    log_id := mywriteabslog('Update discounts where 1 parent can be found', 'UPDATE_ORG_TRANS');
    update artransaction ar
       set ar.originaltransaction_id = (select min(p.artransaction_id)
                                         from artransaction p
                                             where nvl(p.invoicenumber,-1) = nvl(ar.invoicenumber, -1)
                                               and p.artransactiondate = ar.artransactiondate
                                               and p.billtocustomer_link_id = ar.billtocustomer_link_id
                                               and p.debitcredit = 'D'
                                               and p.invoiceheader_id is null
                                               and p.artransaction_id = p.originaltransaction_id)
     where ar.artransaction_id in
     (select  /*+index(a XINSTALLARTRANS01)*/ a.artransaction_id
        from artransaction a
       where a.artransactiontype_id=8 -- discount
         and a.invoiceheader_id is null
         and nvl(a.originaltransaction_id,0)=0
         and
         (select min(p2.artransaction_id)
            from artransaction p2
           where nvl(p2.invoicenumber,-1) = nvl(a.invoicenumber, -1)
             and p2.artransactiondate = a.artransactiondate
             and p2.billtocustomer_link_id = a.billtocustomer_link_id
             and p2.debitcredit = 'D'
             and p2.invoiceheader_id is null
             and p2.artransaction_id = p2.originaltransaction_id) =
         (select max(p3.artransaction_id)
            from artransaction p3
           where nvl(p3.invoicenumber,-1) = nvl(a.invoicenumber, -1)
             and p3.artransactiondate = a.artransactiondate
             and p3.billtocustomer_link_id = a.billtocustomer_link_id
             and p3.debitcredit = 'D'
             and p3.invoiceheader_id is null
             and p3.artransaction_id = p3.originaltransaction_id));
    mycommit(log_id);

    -- delete any zero amount open differences, as they do not have any use.
    log_id := mywriteabslog('Delete any zero amount open differences, as they do not have any use', 'UPDATE_ORG_TRANS');
    delete  /*+index(a XINSTALLARTRANS01)*/ from artransaction a
       where a.artransactiontype_id=2 -- open difference
         and a.amount = 0
         and a.closingamount = 0
         and nvl(a.originaltransaction_id,0)=0
         and not exists (select artransaction_id from artransaction c where c.closingtransaction_id = a.artransaction_id);
    mycommit(log_id);

    -- delete any zero amount open difference credits, as they do not have any use.
    log_id := mywriteabslog('Delete any zero amount open difference credits, as they do not have any use', 'UPDATE_ORG_TRANS');
    delete  /*+index(a XINSTALLARTRANS01)*/ from artransaction a
       where a.artransactiontype_id=6 -- open difference credit
         and a.amount = 0
         and a.closingamount = 0
         and nvl(a.originaltransaction_id,0)=0
         and not exists (select artransaction_id from artransaction c where c.closingtransaction_id = a.artransaction_id);
    mycommit(log_id);
end UPDATE_ORG_TRANS10;
/

create or replace procedure UPDATE_ORG_TRANS11 is
log_id  number;
updated number;
begin
    -- open differences where more than 1 parent can be found, but only one with the same value for amount - closing amount.
    -- since there can be open diferences of open differences a loop is needed and iterate until all are done
    -- this is based on condition "originaltransaction_id is not null":
    -- with each iteration more rows (open diff) will have an original transaction_id filled.
    -- this causes that open differences that initially could not be done can be done in a next iteration
    updated:=1;
    while updated>0 loop
      log_id := mywriteabslog('Update open differences where more than 1 parent can be found, but only one with the same value for amount - closing amount.', 'UPDATE_ORG_TRANS');
      update  /*+index(a XINSTALLARTRANS01)*/ artransaction a
      set a.originaltransaction_id = (select p.originaltransaction_id
                                        from artransaction p
                                       where nvl(p.invoicenumber,-1) = nvl(a.invoicenumber, -1)
                                         and p.artransactiondate = a.artransactiondate
                                         and p.billtocustomer_link_id = a.billtocustomer_link_id
                                         and p.debitcredit = 'D'
                                         and p.invoiceheader_id is null
                                         and p.originaltransaction_id is not null
                                         and p.amount - p.closingamount = a.amount)
       where a.artransactiontype_id=2 -- open difference
         and nvl(a.originaltransaction_id,0)=0
         and a.invoiceheader_id is null
         and exists
         (select 1
            from artransaction p0
           where nvl(p0.invoicenumber,-1) = nvl(a.invoicenumber, -1)
             and p0.artransactiondate = a.artransactiondate
             and p0.billtocustomer_link_id = a.billtocustomer_link_id
             and p0.debitcredit = 'D'
             and p0.invoiceheader_id is null
             and p0.artransaction_id = p0.originaltransaction_id)
         and exists
         (select 1
            from artransaction p1
           where nvl(p1.invoicenumber,-1) = nvl(a.invoicenumber, -1)
             and p1.artransactiondate = a.artransactiondate
             and p1.billtocustomer_link_id = a.billtocustomer_link_id
             and p1.debitcredit = 'D'
             and p1.invoiceheader_id is null
             and p1.originaltransaction_id is not null
             and p1.amount - p1.closingamount = a.amount)
         and
         (select min(p2.artransaction_id)
            from artransaction p2
           where nvl(p2.invoicenumber,-1) = nvl(a.invoicenumber, -1)
             and p2.artransactiondate = a.artransactiondate
             and p2.billtocustomer_link_id = a.billtocustomer_link_id
             and p2.debitcredit = 'D'
             and p2.invoiceheader_id is null
             and p2.originaltransaction_id is not null
             and p2.amount - p2.closingamount = a.amount) =
         (select max(p3.artransaction_id)
            from artransaction p3
           where nvl(p3.invoicenumber,-1) = nvl(a.invoicenumber, -1)
             and p3.artransactiondate = a.artransactiondate
             and p3.billtocustomer_link_id = a.billtocustomer_link_id
             and p3.debitcredit = 'D'
             and p3.invoiceheader_id is null
             and p3.originaltransaction_id is not null
             and p3.amount - p3.closingamount = a.amount);
      updated := SQL%ROWCOUNT;
      mycommit(log_id);
    end loop;
end UPDATE_ORG_TRANS11;
/

--section break for pack_Script

create or replace procedure UPDATE_ORG_TRANS12 is
log_id  number;
updated number;
begin
    -- open differences credits where more than 1 parent can be found, but only one with the same value for amount - closing amount.
    -- same mechanism as for open differences.
    updated:=1;
    while updated>0 loop
      log_id := mywriteabslog('Update open difference credits where more than 1 parent can be found, but only one with the same value for amount - closing amount.', 'UPDATE_ORG_TRANS');
      update  /*+index(a XINSTALLARTRANS01)*/ artransaction a
      set a.originaltransaction_id = (select p.originaltransaction_id
                                        from artransaction p
                                       where  nvl(p.remitidentification,-1) = nvl(a.remitidentification, -1)
                                         and p.artransactiondate = a.artransactiondate
                                         and p.billtocustomer_link_id = a.billtocustomer_link_id
                                         and p.debitcredit = 'C'
                                         and p.originaltransaction_id is not null
                                         and p.artransaction_id < a.artransaction_id
                                         and p.amount - p.closingamount = a.amount)
       where a.artransactiontype_id=6 -- open difference credit
         and nvl(a.originaltransaction_id,0)=0
         and exists
         (select 1
            from artransaction p0
           where  nvl(p0.remitidentification,-1) = nvl(a.remitidentification, -1)
             and p0.artransactiondate = a.artransactiondate
             and p0.billtocustomer_link_id = a.billtocustomer_link_id
             and p0.debitcredit = 'C'
             and p0.artransaction_id = p0.originaltransaction_id
             and p0.artransaction_id < a.artransaction_id
             )
         and exists
         (select 1
            from artransaction p1
           where  nvl(p1.remitidentification,-1) = nvl(a.remitidentification, -1)
             and p1.artransactiondate = a.artransactiondate
             and p1.billtocustomer_link_id = a.billtocustomer_link_id
             and p1.debitcredit = 'C'
             and p1.originaltransaction_id is not null
             and p1.artransaction_id < a.artransaction_id
             and p1.amount - p1.closingamount = a.amount
             )
         and
         (select min(p2.artransaction_id)
            from artransaction p2
           where  nvl(p2.remitidentification,-1) = nvl(a.remitidentification, -1)
             and p2.artransactiondate = a.artransactiondate
             and p2.billtocustomer_link_id = a.billtocustomer_link_id
             and p2.debitcredit = 'C'
             and p2.originaltransaction_id is not null
             and p2.amount - p2.closingamount = a.amount
             and p2.artransaction_id < a.artransaction_id) =
         (select max(p3.artransaction_id)
            from artransaction p3
           where  nvl(p3.remitidentification,-1) = nvl(a.remitidentification, -1)
             and p3.artransactiondate = a.artransactiondate
             and p3.billtocustomer_link_id = a.billtocustomer_link_id
             and p3.debitcredit = 'C'
             and p3.originaltransaction_id is not null
             and p3.amount - p3.closingamount = a.amount
             and p3.artransaction_id < a.artransaction_id);
      updated := SQL%ROWCOUNT;
      mycommit(log_id);
    end loop;
end UPDATE_ORG_TRANS12;
/

create or replace procedure UPDATE_ORG_TRANS13 is
log_id  number;
updated number;
begin
    -- same loop again for open difference credit, but without this condition: pX.artransaction_id < a.artransaction_id
    -- same mechanism as for open differences.
    updated:=1;
    while updated>0 loop
      log_id := mywriteabslog('Update open difference credits where more than 1 parent can be found, but only one with the same value for amount - closing amount.', 'UPDATE_ORG_TRANS');
      update  /*+index(a XINSTALLARTRANS01)*/ artransaction a
      set a.originaltransaction_id = (select p.originaltransaction_id
                                        from artransaction p
                                       where  nvl(p.remitidentification,-1) = nvl(a.remitidentification, -1)
                                         and p.artransactiondate = a.artransactiondate
                                         and p.billtocustomer_link_id = a.billtocustomer_link_id
                                         and p.debitcredit = 'C'
                                         and p.originaltransaction_id is not null
                                         and p.amount - p.closingamount = a.amount)
       where a.artransactiontype_id=6 -- open difference credit
         and nvl(a.originaltransaction_id,0)=0
         and exists
         (select 1
            from artransaction p0
           where  nvl(p0.remitidentification,-1) = nvl(a.remitidentification, -1)
             and p0.artransactiondate = a.artransactiondate
             and p0.billtocustomer_link_id = a.billtocustomer_link_id
             and p0.debitcredit = 'C'
             and p0.artransaction_id = p0.originaltransaction_id
             )
         and exists
         (select 1
            from artransaction p1
           where  nvl(p1.remitidentification,-1) = nvl(a.remitidentification, -1)
             and p1.artransactiondate = a.artransactiondate
             and p1.billtocustomer_link_id = a.billtocustomer_link_id
             and p1.debitcredit = 'C'
             and p1.originaltransaction_id is not null
             and p1.amount - p1.closingamount = a.amount
             )
         and
         (select min(p2.artransaction_id)
            from artransaction p2
           where  nvl(p2.remitidentification,-1) = nvl(a.remitidentification, -1)
             and p2.artransactiondate = a.artransactiondate
             and p2.billtocustomer_link_id = a.billtocustomer_link_id
             and p2.debitcredit = 'C'
             and p2.originaltransaction_id is not null
             and p2.amount - p2.closingamount = a.amount) =
         (select max(p3.artransaction_id)
            from artransaction p3
           where  nvl(p3.remitidentification,-1) = nvl(a.remitidentification, -1)
             and p3.artransactiondate = a.artransactiondate
             and p3.billtocustomer_link_id = a.billtocustomer_link_id
             and p3.debitcredit = 'C'
             and p3.originaltransaction_id is not null
             and p3.amount - p3.closingamount = a.amount);
      updated := SQL%ROWCOUNT;
      mycommit(log_id);
    end loop;
end UPDATE_ORG_TRANS13;
/

create or replace procedure UPDATE_ORG_TRANS14 is
log_id  number;
begin
    -- open differences where one parent can be found, for another date
    log_id := mywriteabslog('Update open differences where one parent can be found, for another date', 'UPDATE_ORG_TRANS');
    update  /*+index(a XINSTALLARTRANS01)*/ artransaction a
       set a.originaltransaction_id =
           (select min(p.artransaction_id)
              from artransaction p
             where nvl(p.invoicenumber,-1) = nvl(a.invoicenumber, -1)
               and p.billtocustomer_link_id = a.billtocustomer_link_id
               and p.debitcredit = a.debitcredit
               and p.invoiceheader_id is null
               and p.artransaction_id = p.originaltransaction_id)
       where a.artransactiontype_id=2 -- open difference
         and nvl(a.originaltransaction_id,0)=0
         and a.invoiceheader_id is null
         and
         (select min(p2.artransaction_id)
            from artransaction p2
           where nvl(p2.invoicenumber,-1) = nvl(a.invoicenumber, -1)
             and p2.billtocustomer_link_id = a.billtocustomer_link_id
             and p2.debitcredit = a.debitcredit
             and p2.invoiceheader_id is null
             and p2.artransaction_id = p2.originaltransaction_id) =
         (select max(p3.artransaction_id)
            from artransaction p3
           where nvl(p3.invoicenumber,-1) = nvl(a.invoicenumber, -1)
             and p3.billtocustomer_link_id = a.billtocustomer_link_id
             and p3.debitcredit = a.debitcredit
             and p3.invoiceheader_id is null
             and p3.artransaction_id = p3.originaltransaction_id);
     mycommit(log_id);
end UPDATE_ORG_TRANS14;
/

create or replace procedure UPDATE_ORG_TRANS15 is
log_id  number;
begin
    -- discounts from open differences and misc.transactions
    log_id := mywriteabslog('Update discounts from open differences and miscellaneous transactions', 'UPDATE_ORG_TRANS');
    -- step 1 write in temp table
    insert into artemp (artransaction_id, originaltransaction_id)
    select  /*+index(a XINSTALLARTRANS01)*/ a.artransaction_id,
         (select p2.originaltransaction_id
            from artransaction p2
           where nvl(p2.invoicenumber,-1) = nvl(a.invoicenumber, -1)
             and p2.artransactiondate = a.artransactiondate
             and p2.billtocustomer_link_id = a.billtocustomer_link_id
             and p2.debitcredit = 'D'
             and p2.artransaction_id = p2.originaltransaction_id) org_id
        from artransaction a
       where a.artransactiontype_id=8 -- discount
         and nvl(a.originaltransaction_id,0)=0
         and
         (select min(p2.artransaction_id)
            from artransaction p2
           where nvl(p2.invoicenumber,-1) = nvl(a.invoicenumber, -1)
             and p2.artransactiondate = a.artransactiondate
             and p2.billtocustomer_link_id = a.billtocustomer_link_id
             and p2.debitcredit = 'D'
             and p2.artransaction_id = p2.originaltransaction_id
             and p2.originaltransaction_id is not null) =
         (select max(p3.artransaction_id)
            from artransaction p3
           where nvl(p3.invoicenumber,-1) = nvl(a.invoicenumber, -1)
             and p3.artransactiondate = a.artransactiondate
             and p3.billtocustomer_link_id = a.billtocustomer_link_id
             and p3.debitcredit = 'D'
             and p3.artransaction_id = p3.originaltransaction_id
             and p3.originaltransaction_id is not null);
    commit;
    -- step 2 update artransaction table
    update artransaction a
       set a.originaltransaction_id = (select ar.originaltransaction_id
                                         from artemp ar
                                        where ar.artransaction_id =
                                              a.artransaction_id)
     where a.artransaction_id in (select at.artransaction_id from artemp at);
    mycommit(log_id);
    execute immediate 'TRUNCATE TABLE "ARTEMP"';
end UPDATE_ORG_TRANS15;
/

create or replace procedure UPDATE_ORG_TRANS16 is
log_id  number;
begin
    -- discounts from open differences and misc.transactions same remit id
    log_id := mywriteabslog('Update discounts from open differences and miscellaneous transactions same remitid', 'UPDATE_ORG_TRANS');
    -- step 1 write in temp table
    insert into artemp (artransaction_id, originaltransaction_id)
    select  /*+index(a XINSTALLARTRANS01)*/ a.artransaction_id,
         (select min(p2.originaltransaction_id)
            from artransaction p2
           where nvl(p2.invoicenumber,-1) = nvl(a.invoicenumber, -1)
             and nvl(p2.remitidentification,-1) = nvl(a.remitidentification, -1)
             and p2.artransactiondate = a.artransactiondate
             and p2.billtocustomer_link_id = a.billtocustomer_link_id
             and p2.debitcredit = 'D'
             and p2.artransaction_id = p2.originaltransaction_id) org_id
        from artransaction a
       where a.artransactiontype_id=8 -- discount
         and nvl(a.originaltransaction_id,0)=0
         and
         (select min(p2.artransaction_id)
            from artransaction p2
           where nvl(p2.invoicenumber,-1) = nvl(a.invoicenumber, -1)
             and nvl(p2.remitidentification,-1) = nvl(a.remitidentification, -1)
             and p2.artransactiondate = a.artransactiondate
             and p2.billtocustomer_link_id = a.billtocustomer_link_id
             and p2.debitcredit = 'D'
             and p2.artransaction_id = p2.originaltransaction_id
             and p2.originaltransaction_id is not null) =
         (select max(p3.artransaction_id)
            from artransaction p3
           where nvl(p3.invoicenumber,-1) = nvl(a.invoicenumber, -1)
             and nvl(p3.remitidentification,-1) = nvl(a.remitidentification, -1)
             and p3.artransactiondate = a.artransactiondate
             and p3.billtocustomer_link_id = a.billtocustomer_link_id
             and p3.debitcredit = 'D'
             and p3.artransaction_id = p3.originaltransaction_id
             and p3.originaltransaction_id is not null);
    commit;
    -- step 2 update artransaction table
    update artransaction a
       set a.originaltransaction_id = (select ar.originaltransaction_id
                                         from artemp ar
                                        where ar.artransaction_id =
                                              a.artransaction_id)
     where a.artransaction_id in (select at.artransaction_id from artemp at);
    mycommit(log_id);
    execute immediate 'TRUNCATE TABLE "ARTEMP"';
end UPDATE_ORG_TRANS16;
/

create or replace procedure UPDATE_ORG_TRANS17 is
log_id  number;
begin
    -- open difference credits where one parent can be found, for another date
    log_id := mywriteabslog('Update open difference credits where one parent can be found, for another date', 'UPDATE_ORG_TRANS');
    update  /*+index(a XINSTALLARTRANS01)*/ artransaction a
      set a.originaltransaction_id =
         (select /*+ index(p HXSPEED5ARTRANS) */ min(p.artransaction_id)
            from artransaction p
           where  nvl(p.remitidentification,-1) = nvl(a.remitidentification, -1)
             and p.billtocustomer_link_id = a.billtocustomer_link_id
             and p.debitcredit = a.debitcredit
             and p.artransaction_id = p.originaltransaction_id
             and p.artransaction_id < a.artransaction_id)
       where a.artransactiontype_id=6 -- open difference credit
         and nvl(a.originaltransaction_id,0)=0
         and
         (select /*+ index(p2 HXSPEED5ARTRANS) */ min(p2.artransaction_id)
            from artransaction p2
           where nvl(p2.remitidentification,-1) = nvl(a.remitidentification, -1)
             and p2.billtocustomer_link_id = a.billtocustomer_link_id
             and p2.debitcredit = a.debitcredit
             and p2.artransaction_id = p2.originaltransaction_id
             and p2.artransaction_id < a.artransaction_id) =
         (select /*+ index(p3 HXSPEED5ARTRANS) */ max(p3.artransaction_id)
            from artransaction p3
           where nvl(p3.remitidentification,-1) = nvl(a.remitidentification, -1)
             and p3.billtocustomer_link_id = a.billtocustomer_link_id
             and p3.debitcredit = a.debitcredit
             and p3.artransaction_id = p3.originaltransaction_id
             and p3.artransaction_id < a.artransaction_id);
    mycommit(log_id);

    -- open difference credits where one parent can be found, for another date
    log_id := mywriteabslog('Update open difference credits where one parent can be found, for another date, but with higher ID', 'UPDATE_ORG_TRANS');
    update  /*+index(a XINSTALLARTRANS01)*/ artransaction a
      set a.originaltransaction_id =
         (select /*+ index(p HXSPEED5ARTRANS) */ min(p.artransaction_id)
            from artransaction p
           where  nvl(p.remitidentification,-1) = nvl(a.remitidentification, -1)
             and p.billtocustomer_link_id = a.billtocustomer_link_id
             and p.debitcredit = a.debitcredit
             and p.artransaction_id = p.originaltransaction_id)
       where a.artransactiontype_id=6 -- open difference credit
         and nvl(a.originaltransaction_id,0)=0
         and
         (select /*+ index(p2 HXSPEED5ARTRANS) */ min(p2.artransaction_id)
            from artransaction p2
           where nvl(p2.remitidentification,-1) = nvl(a.remitidentification, -1)
             and p2.billtocustomer_link_id = a.billtocustomer_link_id
             and p2.debitcredit = a.debitcredit
             and p2.artransaction_id = p2.originaltransaction_id) =
         (select /*+ index(p3 HXSPEED5ARTRANS) */ max(p3.artransaction_id)
            from artransaction p3
           where nvl(p3.remitidentification,-1) = nvl(a.remitidentification, -1)
             and p3.billtocustomer_link_id = a.billtocustomer_link_id
             and p3.debitcredit = a.debitcredit
             and p3.artransaction_id = p3.originaltransaction_id);
    mycommit(log_id);
end UPDATE_ORG_TRANS17;
/

create or replace procedure UPDATE_ORG_TRANS18 is
log_id  number;
begin
    -- open different credit other date multiple found but only 1 with the same invoice#
    log_id := mywriteabslog('Update open difference credits at other date multiple found but only 1 with the same invoice#', 'UPDATE_ORG_TRANS');
    update  /*+index(a XINSTALLARTRANS01)*/ artransaction a
    set a.originaltransaction_id =
         (select /*+ index(p HXSPEED5ARTRANS) */ min(p.artransaction_id)
            from artransaction p
           where nvl(p.remitidentification,-1) = nvl(a.remitidentification, -1)
             and nvl(p.invoicenumber,-1) = nvl(a.invoicenumber, -1)
             and p.billtocustomer_link_id = a.billtocustomer_link_id
             and p.debitcredit = a.debitcredit
             and p.originaltransaction_id is not null
             and p.artransaction_id < a.artransaction_id)
       where a.artransactiontype_id=6 -- open difference credit
         and nvl(a.originaltransaction_id,0)=0
         and
         (select /*+ index(p2 HXSPEED5ARTRANS) */ min(p2.artransaction_id)
            from artransaction p2
           where nvl(p2.remitidentification,-1) = nvl(a.remitidentification, -1)
             and nvl(p2.invoicenumber,-1) = nvl(a.invoicenumber, -1)
             and p2.billtocustomer_link_id = a.billtocustomer_link_id
             and p2.debitcredit = a.debitcredit
             and p2.originaltransaction_id is not null
             and p2.artransaction_id < a.artransaction_id) =
         (select /*+ index(p3 HXSPEED5ARTRANS) */ max(p3.artransaction_id)
            from artransaction p3
           where nvl(p3.remitidentification,-1) = nvl(a.remitidentification, -1)
             and nvl(p3.invoicenumber,-1) = nvl(a.invoicenumber, -1)
             and p3.billtocustomer_link_id = a.billtocustomer_link_id
             and p3.debitcredit = a.debitcredit
             and p3.originaltransaction_id is not null
             and p3.artransaction_id < a.artransaction_id);
    mycommit(log_id);

    -- same date 1 found by invoice# instead of by remitID (different remitID)
    log_id := mywriteabslog('Update open difference credits with parent at same date by invoice# instead of by remitID (different remitID)', 'UPDATE_ORG_TRANS');
    update  /*+index(a XINSTALLARTRANS01)*/ artransaction a
       set a.originaltransaction_id =
         (select min(p.artransaction_id)
            from artransaction p
           where nvl(p.invoicenumber,-1) = nvl(a.invoicenumber, -1)
             and p.artransactiondate = a.artransactiondate
             and p.billtocustomer_link_id = a.billtocustomer_link_id
             and p.debitcredit = a.debitcredit
             and p.originaltransaction_id is not null
             and p.artransaction_id < a.artransaction_id)
       where a.artransactiontype_id=6 -- open difference credit
         and nvl(a.originaltransaction_id,0)=0
         and
         (select min(p2.artransaction_id)
            from artransaction p2
           where nvl(p2.invoicenumber,-1) = nvl(a.invoicenumber, -1)
             and p2.artransactiondate = a.artransactiondate
             and p2.billtocustomer_link_id = a.billtocustomer_link_id
             and p2.debitcredit = a.debitcredit
             and p2.originaltransaction_id is not null
             and p2.artransaction_id < a.artransaction_id) =
         (select max(p3.artransaction_id)
            from artransaction p3
           where nvl(p3.invoicenumber,-1) = nvl(a.invoicenumber, -1)
             and p3.artransactiondate = a.artransactiondate
             and p3.billtocustomer_link_id = a.billtocustomer_link_id
             and p3.debitcredit = a.debitcredit
             and p3.originaltransaction_id is not null
             and p3.artransaction_id < a.artransaction_id);
    mycommit(log_id);
end UPDATE_ORG_TRANS18;
/

create or replace procedure UPDATE_ORG_TRANS19 is
log_id  number;
begin
    -- Link discounts credits and debits that could not be linked to themselves
    log_id := mywriteabslog('Link unlinked discounts to themselves.', 'UPDATE_ORG_TRANS');
    update  /*+index(a XINSTALLARTRANS01)*/ artransaction a 
       set a.originaltransaction_id  = a.artransaction_id,
           a.remark = substr('!!' || a.remark,1,30) 
     where nvl(a.originaltransaction_id,0)=0
       and a.artransactiontype_id in (4,8);
    mycommit(log_id);

    log_id := mywriteabslog('FINISHED UPDATE_ORG_TRANS.', 'UPDATE_ORG_TRANS');
end UPDATE_ORG_TRANS19;
/

execute UPDATE_ORG_TRANS1;
execute UPDATE_ORG_TRANS2;
execute UPDATE_ORG_TRANS3;
execute UPDATE_ORG_TRANS4;
execute UPDATE_ORG_TRANS5;
execute UPDATE_ORG_TRANS6;
execute UPDATE_ORG_TRANS7;
execute UPDATE_ORG_TRANS8;
execute UPDATE_ORG_TRANS9;
execute UPDATE_ORG_TRANS10;
execute UPDATE_ORG_TRANS11;
execute UPDATE_ORG_TRANS12;
execute UPDATE_ORG_TRANS13;
execute UPDATE_ORG_TRANS14;
execute UPDATE_ORG_TRANS15;
execute UPDATE_ORG_TRANS16;
execute UPDATE_ORG_TRANS17;
execute UPDATE_ORG_TRANS18;
execute UPDATE_ORG_TRANS19;

drop procedure UPDATE_ORG_TRANS1;
drop procedure UPDATE_ORG_TRANS2;
drop procedure UPDATE_ORG_TRANS3;
drop procedure UPDATE_ORG_TRANS4;
drop procedure UPDATE_ORG_TRANS5;
drop procedure UPDATE_ORG_TRANS6;
drop procedure UPDATE_ORG_TRANS7;
drop procedure UPDATE_ORG_TRANS8;
drop procedure UPDATE_ORG_TRANS9;
drop procedure UPDATE_ORG_TRANS10;
drop procedure UPDATE_ORG_TRANS11;
drop procedure UPDATE_ORG_TRANS12;
drop procedure UPDATE_ORG_TRANS13;
drop procedure UPDATE_ORG_TRANS14;
drop procedure UPDATE_ORG_TRANS15;
drop procedure UPDATE_ORG_TRANS16;
drop procedure UPDATE_ORG_TRANS17;
drop procedure UPDATE_ORG_TRANS18;
drop procedure UPDATE_ORG_TRANS19;

drop procedure update_remitID_discount_chg;
drop procedure update_remitID_open_diff_cred;
drop procedure mycommit;
drop procedure myupdateabslog;
drop function mywriteabslog;

-- drop temporary indexes
DROP index HXSPEED4ARTRANS;
DROP index HXSPEED5ARTRANS;
DROP index HXSPEED6ARTRANS;

DROP index XINSTALLARTRANS01;
DROP index XINSTALLARTRANS02;
-- drop temporary table
DROP table ARTEMP;
