--Order 20011971.260 & 20011971.310
----------------------ARTRANSACTION------------------------
ALTER TABLE ARTRANSACTION
	MODIFY (CLOSINGAMOUNT NULL);

ALTER TABLE ARTRANSACTION
	ADD (TOTALAPPLIEDAMOUNT NUMBER(15,6)  DEFAULT 0 NOT NULL);

ALTER TABLE ARTRANSACTION
	ADD (ISOPEN_YN VARCHAR2(1)  DEFAULT 'Y' NOT NULL CONSTRAINT YN_ValidationField58 CHECK (ISOPEN_YN IN ('Y', 'N')));

COMMENT ON COLUMN ARTRANSACTION.CLOSINGTRANSACTION_ID IS 'If filled then this means transaction is closed.
If not filled then print this transaction on statement.
This attribute is obsolete in the redesigned ARTransaction mechanism and will be removed in the future.';

COMMENT ON COLUMN ARTRANSACTION.CLOSINGDATE IS 'Must by filled when CLOSINGTRANS_ID is filled.
Must be empty when CLOSINGTRANS_ID is empty.
This attribute is obsolete in the redesigned ARTransaction mechanism and will be removed in the future.';

COMMENT ON COLUMN ARTRANSACTION.CLOSINGAMOUNT IS 'This attribute is obsolete in the redesigned ARTransaction mechanism and will be removed in the future.';

COMMENT ON COLUMN ARTRANSACTION.TOTALAPPLIEDAMOUNT IS 'Total of applied amounts for this transaction
Is maintained via trigger on cashapplication table.
';

COMMENT ON COLUMN ARTRANSACTION.ISOPEN_YN IS 'If set to Yes then TotalAppliedAmount = Amount
Maintained via trigger on cashapplication table.';


------------------------CASHAPPLICATION----------------------
CREATE SEQUENCE SEQ_CASHAPPLICATION
START WITH 1
INCREMENT BY 1;

CREATE TABLE CASHAPPLICATION
(
	CASHAPPLICATION_ID   NUMBER NOT NULL ,
	DEBITARTRANSACTION_ID NUMBER NOT NULL ,
	CREDITARTRANSACTION_ID NUMBER NOT NULL ,
	APPLYDATE            DATE NULL ,
	APPLYTIMESTAMP       DATE NOT NULL ,
	APPLYSYSTEMUSER_ID   NUMBER NOT NULL ,
	AMOUNT               NUMBER(15,6) NULL ,
	UNAPPLIED_YN         VARCHAR2(1) DEFAULT  'N'  NOT NULL  CONSTRAINT  YN_ValidationField59 CHECK (UNAPPLIED_YN IN ('Y', 'N')),
	UNAPPLYDATE          DATE NULL ,
	UNAPPLYTIMESTAMP     DATE NULL ,
	UNAPPLYSYSTEMUSER_ID NUMBER NULL ,
	ORIGIN               NUMBER NOT NULL CONSTRAINT  Origin_valid3 CHECK (ORIGIN IN (0, 1, 2, 3, 4, 5))
);

COMMENT ON COLUMN CASHAPPLICATION.DEBITARTRANSACTION_ID IS 'Must be ARTransaction of type Debit ((DebitCredit = ‘D’)';

COMMENT ON COLUMN CASHAPPLICATION.CREDITARTRANSACTION_ID IS 'Must be ARTransaction of type Credit ((DebitCredit = ‘C’)';

COMMENT ON COLUMN CASHAPPLICATION.ORIGIN IS '0 = Undefined (default)
1 = Cash application
2 = Settlement
3 = Credit card interface
4 = Conversion
5 = Import payments
';


ALTER TABLE CASHAPPLICATION
ADD CONSTRAINT  XPKCASHAPPLICATION PRIMARY KEY (CASHAPPLICATION_ID);

ALTER TABLE CASHAPPLICATION
ADD CONSTRAINT CASHAPPLICATION_ARTRANSDEBIT FOREIGN KEY (DEBITARTRANSACTION_ID) REFERENCES ARTRANSACTION (ARTRANSACTION_ID) ON DELETE CASCADE;

ALTER TABLE CASHAPPLICATION
ADD CONSTRAINT CASHAPPLICATION_ARTRANSCREDIT FOREIGN KEY (CREDITARTRANSACTION_ID) REFERENCES ARTRANSACTION (ARTRANSACTION_ID) ON DELETE CASCADE;


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

create index HXSPEED4ARTRANS on ARTRANSACTION (TO_CHAR(ARTRANSACTIONDATE,'YYYYMM'), ARTRANSACTIONTYPE_ID)
  tablespace INDX
  pctfree 10
  initrans 2
  maxtrans 255
  storage
  (
    initial 64K
    next 1M
    minextents 1
    maxextents unlimited
  );
  
  
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;
/

--inserts
create or replace procedure LOAD_CASHAPP_PART1 is

updated number;
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
    log_id := mywriteabslog('Fix empty closingdate for closed transactions.', 'LOAD_CASHAPP_PART1');
    update artransaction a 
       set a.closingdate = trunc(a.timestamp)
     where a.closingtransaction_id is not null and a.closingdate is null;
    mycommit(log_id);

    for v in vv loop
      -- NEW index needed on ARTRANSACTIONDATE, ARTRANSACTIONTYPE_ID !!
      log_id := mywriteabslog('Load cashapp for month ' || v.mnth, 'LOAD_CASHAPP_PART1');
      -- insert for all straight forward payments.
      INSERT INTO cashapplication
            (cashapplication_id,
             debitartransaction_id,
             creditartransaction_id,
             applydate,
             amount,
             applytimestamp,
             applysystemuser_id,
             origin)
      select  /*+index(a HXSPEED4ARTRANS) index(c XPKARTRANSACTION)*/
             seq_cashapplication.nextval,
             a.originaltransaction_id debitartransaction_id,
             c.originaltransaction_id creditartransaction_id,
             a.closingdate applydate,
             a.closingamount amount,
             a.timestamp,
             a.systemuser_id,
             4
        from artransaction a, artransaction c
       where a.artransaction_id <> a.closingtransaction_id
         and a.closingtransaction_id is not null
         and a.closingtransaction_id = c.artransaction_id
         and a.artransactiontype_id <> 8
         and c.artransactiontype_id <> 8
         and a.artransactiontype_id <> 4
         and a.originaltransaction_id is not null --
         and c.originaltransaction_id is not null --
         and to_char(a.artransactiondate,'YYYYMM') = v.mnth;
      mycommit(log_id);
    end loop;
    
    log_id := mywriteabslog('Load cashapp: delete incorrect transactions.', 'LOAD_CASHAPP_PART1');
    delete from cashapplication ca
    where ca.debitartransaction_id = ca.creditartransaction_id;
    mycommit(log_id);
    
    -- insert for discounts debits
    log_id := mywriteabslog('Load cashapp: insert for discount debits.', 'LOAD_CASHAPP_PART1');
    INSERT INTO cashapplication
          (cashapplication_id,
           debitartransaction_id,
           creditartransaction_id,
           applydate,
           amount,
           applytimestamp,
           applysystemuser_id,
           origin)
    select seq_cashapplication.nextval,
           a.artransaction_id debitartransaction_id,   -- not the original because that is from the payment, this is dif with above
           c.originaltransaction_id creditartransaction_id,
           a.closingdate applydate,
           a.closingamount amount,
           a.timestamp,
           a.systemuser_id,
           4
      from artransaction a, artransaction c
     where a.closingtransaction_id is not null
       and a.closingtransaction_id = c.artransaction_id
       and a.artransactiontype_id = 4 -- D 4
       and a.artransactiontype_id <> 8 -- C 4
       and c.originaltransaction_id is not null;
    mycommit(log_id);
    -- discounts  (they will always close themselves, never another transaction) :
    log_id := mywriteabslog('Load cashapp: insert for discounts.', 'LOAD_CASHAPP_PART1');
    INSERT INTO cashapplication
          (cashapplication_id,
           debitartransaction_id,
           creditartransaction_id,
           applydate,
           amount,
           applytimestamp,
           applysystemuser_id,
           origin)
    select seq_cashapplication.nextval,
           d.originaltransaction_id debitartransaction_id,
           d.artransaction_id creditartransaction_id,
           d.closingdate applydate,
           d.closingamount amount,
           d.timestamp,
           d.systemuser_id,
           4
      from artransaction d
     where d.closingtransaction_id is not null
       and d.artransaction_id = d.closingtransaction_id    -- this is always for C 4
       and d.artransactiontype_id = 8 -- C 4 discounts
       and d.originaltransaction_id <> d.artransaction_id
       and d.originaltransaction_id is not null;
    mycommit(log_id);
    -- Insert for write off credits  (they will always close themselves, but are linked to the invoice via originaltransaction)
    log_id := mywriteabslog('Load cashapp: insert for write off credits.', 'LOAD_CASHAPP_PART1');
    INSERT INTO cashapplication
          (cashapplication_id,
           debitartransaction_id,
           creditartransaction_id,
           applydate,
           amount,
           applytimestamp,
           applysystemuser_id,
           origin)
    select seq_cashapplication.nextval,
           d.originaltransaction_id debitartransaction_id,
           d.artransaction_id creditartransaction_id,
           d.closingdate applydate,
           d.closingamount amount,
           d.timestamp,
           d.systemuser_id,
           4
      from artransaction d, artransactiontype at
     where d.closingtransaction_id is not null
       and d.artransaction_id = d.closingtransaction_id
       and at.artransactiontype_id = d.artransactiontype_id
       and d.originaltransaction_id <> d.artransaction_id
       and at.predefined ='N'
       and at.debitcredit='C'
       and d.originaltransaction_id is not null;
    mycommit(log_id);

    -- update cash application amount for negative invoices that were not applied in full, but partly with a misc debit
    log_id := mywriteabslog('Load cashapp: update amounts for negative invoices not applied in full.', 'LOAD_CASHAPP_PART1');
    update artransaction a
       set a.isopen_yn = 'N'
     where a.amount = a.totalappliedamount
       and a.billtocustomer_link_id in
        (select a.billtocustomer_link_id
          from artransaction a
         where a.amount < 0
           and a.artransaction_id = a.originaltransaction_id
           and a.closingamount > a.amount
           and a.closingtransaction_id is not null
           and exists
         (select 1
                  from artransaction m, artransactiontype art
                 where m.closingtransaction_id = a.closingtransaction_id
                   and m.artransactiontype_id = art.artransactiontype_id
                   and art.predefined = 'N'
                   and m.debitcredit = 'D'
                   and m.amount = m.closingamount
                   and m.amount = a.closingamount - a.amount));
    mycommit(log_id);       
    
    log_id := mywriteabslog('Finished LOAD_CASHAPP_PART1', 'LOAD_CASHAPP_PART1');
end LOAD_CASHAPP_PART1;
/


execute LOAD_CASHAPP_PART1;

drop procedure LOAD_CASHAPP_PART1;

-- Create index after bulk insert for performance reasons
CREATE  INDEX XIF1CASHAPPLICATION ON CASHAPPLICATION
(DEBITARTRANSACTION_ID   ASC);

CREATE  INDEX XIF2CASHAPPLICATION ON CASHAPPLICATION
(CREDITARTRANSACTION_ID   ASC);

CREATE  INDEX XIE1CASHAPPLICATION ON CASHAPPLICATION
(DEBITARTRANSACTION_ID   ASC,UNAPPLIED_YN   ASC);

CREATE  INDEX XIE2CASHAPPLICATION ON CASHAPPLICATION
(CREDITARTRANSACTION_ID   ASC,UNAPPLIED_YN   ASC);


create or replace procedure LOAD_CASHAPP_PART2 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
    -- update totalAppliedAmount because triggers are not active yet (for performance reasons)
    for v in vv loop
      log_id := mywriteabslog('Update TotalAppliedAmount for month ' || v.mnth, 'LOAD_CASHAPP_PART2');
      update /*+index(a HXSPEED4ARTRANS)*/ artransaction a
         set a.totalappliedamount =
         nvl(case when a.debitcredit = 'C'
             then (select sum(c.amount) from cashapplication c where c.creditartransaction_id=a.artransaction_id)
             else (select sum(c.amount) from cashapplication c where c.debitartransaction_id=a.artransaction_id)
            end,0)
       where a.closingtransaction_id is not null
         and to_char(a.artransactiondate,'YYYYMM') = v.mnth;
      mycommit(log_id);
    end loop;

    -- update IsOpenYN because triggers are not active yet (for performance reasons)
    for v in vv loop
      log_id := mywriteabslog('Update IsOpen_YN for month ' || v.mnth, 'LOAD_CASHAPP_PART2');
      update artransaction a
         set a.isopen_yn = 'N'
       where a.amount = a.totalappliedamount
         and to_char(a.artransactiondate,'YYYYMM') = v.mnth;
      mycommit(log_id);
    end loop;

    -- close all transactions that do not have any open transactions at all.
    log_id := mywriteabslog('Close all transactions without any open transaction.', 'LOAD_CASHAPP_PART2');
    update artransaction a 
       set a.isopen_yn = 'N'
     where a.isopen_yn = 'Y'
       and a.amount <> a.totalappliedamount
       and a.artransaction_id = a.originaltransaction_id
       and not exists
     (select 1
              from artransaction c
             where c.originaltransaction_id = a.originaltransaction_id
               and c.closingtransaction_id is null);
    mycommit(log_id);

    -- update unneeded open diffences , set amount to zero
    log_id := mywriteabslog('Update unneeded open diffences , set amount to zero.', 'LOAD_CASHAPP_PART2');
    update artransaction ar
       set ar.remark = substr('Set to 0. Org.amnt=' || ar.amount,1,30), 
           ar.amount = 0
     where ar.artransaction_id in
           (select (select max(c.artransaction_id)
                      from artransaction c
                     where c.originaltransaction_id = a.originaltransaction_id
                       and c.closingtransaction_id is null
                       and c.amount <> 0
                       and c.artransaction_id <> c.originaltransaction_id) open_id
              from artransaction a
             where a.artransaction_id = a.originaltransaction_id
               and a.amount <> a.closingamount
               and a.amount > 0
               and a.amount =
                   a.closingamount +
                   nvl((select sum(c.closingamount)
                         from artransaction c
                        where c.originaltransaction_id =
                              a.originaltransaction_id
                          and c.closingtransaction_id is not null
                          and c.artransaction_id <> c.originaltransaction_id),0) -- means already fully paid
               and exists
             (select 1
                      from artransaction c
                     where c.originaltransaction_id = a.originaltransaction_id
                       and c.artransaction_id <> c.originaltransaction_id
                       and c.closingtransaction_id is null
                       and c.amount <> 0));
    mycommit(log_id);
    
    log_id := mywriteabslog('Finished LOAD_CASHAPP_PART2', 'LOAD_CASHAPP_PART2');
end LOAD_CASHAPP_PART2;
/

execute LOAD_CASHAPP_PART2;

drop procedure LOAD_CASHAPP_PART2;

drop procedure mycommit;

drop procedure myupdateabslog;

drop function mywriteabslog;

--section break for pack_Script

-- in future we could delete ALL open difference transactions, but not done now (too risky)

/*-- A) CHECK DIFF for debits
select x.originaltransaction_id,
       b.code unit,
       a.billtocustomer_link_id,
       c.customernumber,
       c.name customer,
       a.artransactiondate ardate,
       a.amount,
       x.paid paid_old,
       nvl((select sum(ca.amount)
             from cashapplication ca
            where ca.debitartransaction_id = a.artransaction_id),0) paid_new,
       x.paid - 
       nvl((select sum(ca.amount)
              from cashapplication ca
             where ca.debitartransaction_id = a.artransaction_id),0) diff
  from artransaction a, artransactiontype art, customer c, businessunit b,
       (select a.originaltransaction_id, sum(a.closingamount) paid
          from artransaction a
         group by a.originaltransaction_id) x
 where x.originaltransaction_id = a.artransaction_id
   and c.customer_id = a.billtocustomer_link_id
   and b.businessunit_id = a.businessunit_id
   and art.artransactiontype_id = a.artransactiontype_id
   and a.debitcredit = 'D'
   and art.typenumber <> 4
  and x.paid <> nvl((select sum(ca.amount)
                       from cashapplication ca
                      where ca.debitartransaction_id = a.artransaction_id),0)

-- B) CHECK DIFF for credits
select a.originaltransaction_id,
       a.amount,
       (select sum(nvl(a2.closingamount,0))
          from artransaction a2, artransaction a3
         where a2.closingtransaction_id = a3.artransaction_id
           and a3.originaltransaction_id=a.originaltransaction_id
           and a2.artransaction_id <> a3.artransaction_id
       ) applied_old,
       a.totalappliedamount applied_new, 
       (select sum(nvl(a2.closingamount,0))
          from artransaction a2, artransaction a3
         where a2.closingtransaction_id = a3.artransaction_id
           and a3.originaltransaction_id=a.originaltransaction_id
           and a2.artransaction_id <> a3.artransaction_id
       ) - a.totalappliedamount diff
  from artransaction a, artransactiontype art
 where art.artransactiontype_id = a.artransactiontype_id
  and a.originaltransaction_id = a.artransaction_id
  and a.debitcredit = 'C'
  and art.typenumber <> 4
and (select sum(nvl(a2.closingamount,0))
          from artransaction a2, artransaction a3
         where a2.closingtransaction_id = a3.artransaction_id
           and a3.originaltransaction_id=a.originaltransaction_id
           and a2.artransaction_id <> a3.artransaction_id
       ) <> a.totalappliedamount


-- C) select cash app with equal debit/credit  SHOULD BE NONE !!
select c.debitartransaction_id debit_id, c.creditartransaction_id credit_id, c.applydate, c.amount, art.debitcredit, art.code, a.invoicenumber, c.customernumber, c.name
  from cashapplication c, artransactiontype art, artransaction a, customer c
 where c.debitartransaction_id = c.creditartransaction_id
   and art.artransactiontype_id = a.artransactiontype_id
   and a.artransaction_id = c.debitartransaction_id
   and c.customer_id = a.billtocustomer_link_id


-- D) select Total amount open per customer old and new. 
select x.billtocustomer_link_id,
       b.code unit,
       c.customernumber,
       c.name,
       sum(old_open),
       sum(new_open),
       sum(old_open) - sum(new_open) diff
  from (select a.businessunit_id,
               a.billtocustomer_link_id, 
               sum(case when a.debitcredit = 'D' then a.amount else a.amount*-1 end) old_open, 
               0 new_open
          from artransaction a
         where a.closingtransaction_id is null
         group by a.businessunit_id, a.billtocustomer_link_id
        union
        select b.businessunit_id, 
               b.billtocustomer_link_id,
               0 old_open,
               sum(case when b.debitcredit = 'D' 
                        then b.amount - b.totalappliedamount 
                        else (b.amount - b.totalappliedamount )*-1 end) new_open
          from artransaction b, artransactiontype art
         where b.isopen_yn = 'Y'
           and art.artransactiontype_id = b.artransactiontype_id
           and art.typenumber <> 2 -- no open differences
         group by b.businessunit_id, b.billtocustomer_link_id) x,
       customer c, businessunit b
 where c.customer_id = x.billtocustomer_link_id
   and b.businessunit_id = x.businessunit_id
 group by x.billtocustomer_link_id, b.code, c.customernumber, c.name
having sum(old_open) <> sum(new_open)
order by b.code, customernumber


-- E) select Total amount open per customer old and new per invoice/payment. same as D) but more detailed.
select --x.billtocustomer_link_id c_linkid,
       b.code unit,
       c.customernumber cust#,
       c.name,
       x.artransactiondate ardate, 
       x.debitcredit dc, 
       x.invoicenumber inv#,
       x.remitidentification remitID,
       sum(old_open) Open_Old,
       sum(new_open) Open_New,
       sum(old_open) - sum(new_open) diff
  from (select a.businessunit_id, a.billtocustomer_link_id, a.artransactiondate, a.debitcredit,  a.invoicenumber, a.remitidentification,
               sum(case when a.debitcredit = 'D' then a.amount else a.amount*-1 end) old_open, 
               0 new_open
          from artransaction a
         where a.closingtransaction_id is null
         group by a.businessunit_id, a.billtocustomer_link_id, a.artransactiondate, a.debitcredit, a.invoicenumber,a.remitidentification
        union
        select b.businessunit_id, b.billtocustomer_link_id, b.artransactiondate, b.debitcredit, b.invoicenumber,b.remitidentification,
               0 old_open,
               sum(case when b.debitcredit = 'D' 
                        then b.amount - b.totalappliedamount 
                        else (b.amount - b.totalappliedamount )*-1 end) new_open
          from artransaction b, artransactiontype art
         where b.isopen_yn = 'Y'
           and art.artransactiontype_id = b.artransactiontype_id
           and art.typenumber <> 2 -- no open differences
         group by b.businessunit_id, b.billtocustomer_link_id, b.artransactiondate, b.debitcredit, b.invoicenumber, b.remitidentification) x,
       customer c, businessunit b
 where c.customer_id = x.billtocustomer_link_id
   and b.businessunit_id = x.businessunit_id
 group by x.billtocustomer_link_id, b.code, c.customernumber, c.name, x.artransactiondate, x.debitcredit, x.invoicenumber, x.remitidentification
having sum(old_open) <> sum(new_open)
order by b.code, c.customernumber, x.artransactiondate          

-- F) select where too high closing amount
select b.code unit, c.customernumber cust#, c.name, a.billtocustomer_link_id, a.artransactiondate ardate, a.debitcredit dc, a.invoicenumber inv#, a.remitidentification remit_id,
   a.amount, 
   a.closingamount +
     nvl((select sum(c.closingamount)
          from artransaction c
         where c.originaltransaction_id = a.originaltransaction_id
           and c.closingtransaction_id is not null
           and c.artransaction_id <> c.originaltransaction_id),0)
     +
     nvl((select sum(c.amount)
          from artransaction c
         where c.originaltransaction_id = a.originaltransaction_id
           and c.closingtransaction_id is null
           and c.artransaction_id <> c.originaltransaction_id),0) applied_amt,
   a.closingamount +
     nvl((select sum(c.closingamount)
          from artransaction c
         where c.originaltransaction_id = a.originaltransaction_id
           and c.closingtransaction_id is not null
           and c.artransaction_id <> c.originaltransaction_id),0)
     +
     nvl((select sum(c.amount)
          from artransaction c
         where c.originaltransaction_id = a.originaltransaction_id
           and c.closingtransaction_id is null
           and c.artransaction_id <> c.originaltransaction_id),0)
     - a.amount diff,
     nvl((select sum(c.amount)
          from artransaction c
         where c.originaltransaction_id = a.originaltransaction_id
           and c.closingtransaction_id is null
           and c.artransaction_id <> c.originaltransaction_id),0) open_amt           
  from artransaction a, customer c, businessunit b
 where a.artransaction_id = a.originaltransaction_id
   and a.amount <> a.closingamount
   and c.customer_id = a.billtocustomer_link_id
   and b.businessunit_id = a.businessunit_id
   and a.amount > 0
   and a.amount <
       a.closingamount +
     nvl((select sum(c.closingamount)
          from artransaction c
         where c.originaltransaction_id = a.originaltransaction_id
           and c.closingtransaction_id is not null
           and c.artransaction_id <> c.originaltransaction_id),0)
     +
     nvl((select sum(c.amount)
          from artransaction c
         where c.originaltransaction_id = a.originaltransaction_id
           and c.closingtransaction_id is null
           and c.artransaction_id <> c.originaltransaction_id),0)
   and exists
 (select 1
          from artransaction c
         where c.originaltransaction_id = a.originaltransaction_id
           and c.artransaction_id <> c.originaltransaction_id
           and c.closingtransaction_id is null)
order by b.code, c.customernumber

-- G) No original transaction could be determined for 
select  a.artransaction_id,
        b.code unit,
        c.customernumber,
        c.name customer,
        a.artransactiondate,
        a.debitcredit,
        at.code transactioncode,
        atd.description  transactiontype,
        a.remitidentification,
        a.invoicenumber,
        a.amount,
        a.closingamount,
        a.remark
 from artransaction a, artransactiontype at, artransactiontype_desc atd, customer c, businessunit b
where a.originaltransaction_id is  null
and at.artransactiontype_id = a.artransactiontype_id
and atd.artransactiontype_id = a.artransactiontype_id
and atd.language_id=1
and c.customer_id = a.billtocustomer_link_id
and b.businessunit_id = a.businessunit_id
order by b.code,
        c.customernumber
*/

---triggers

CREATE OR REPLACE TRIGGER AFTER_CREATE_CASHAPPLICATION
  after insert on cashapplication
  for each row

begin
  if :new.unapplied_yn='N' then
    update artransaction d
       set d.totalappliedamount = d.totalappliedamount + :new.amount,
           d.isopen_yn = case when d.totalappliedamount + :new.amount = d.amount then 'N' else 'Y' end,
           d.timestamp = :new.applytimestamp,
           d.systemuser_id = :new.applysystemuser_id
     where d.artransaction_id = :new.debitartransaction_id;
    update artransaction c
       set c.totalappliedamount = c.totalappliedamount + :new.amount,
           c.isopen_yn = case when c.totalappliedamount + :new.amount  = c.amount then 'N' else 'Y' end,
           c.timestamp = :new.applytimestamp,
           c.systemuser_id = :new.applysystemuser_id
     where c.artransaction_id = :new.creditartransaction_id;
  end if;
end AFTER_CREATE_CASHAPPLICATION;
/

CREATE OR REPLACE TRIGGER AFTER_UPDATE_CASHAPPLICATION
  after update on cashapplication
  for each row

begin
  case
  when :old.unapplied_yn='N' and :new.unapplied_yn='N' then -- update of applied amount, normally never happens
      update artransaction d
         set d.totalappliedamount = d.totalappliedamount - :old.amount + :new.amount,
             d.isopen_yn = case when d.totalappliedamount - :old.amount + :new.amount  = d.amount then 'N' else 'Y' end,
             d.timestamp = :new.applytimestamp,
             d.systemuser_id = :new.applysystemuser_id
       where d.artransaction_id = :new.debitartransaction_id;
      update artransaction c
         set c.totalappliedamount = c.totalappliedamount - :old.amount + :new.amount,
             c.isopen_yn = case when c.totalappliedamount - :old.amount + :new.amount  = c.amount then 'N' else 'Y' end,
             c.timestamp = :new.applytimestamp,
             c.systemuser_id = :new.applysystemuser_id
       where c.artransaction_id = :new.creditartransaction_id;
  when :old.unapplied_yn='N' and :new.unapplied_yn='Y' then  -- unapplied: decrease with old applied amount
      update artransaction d
         set d.totalappliedamount = d.totalappliedamount - :old.amount,
             d.isopen_yn = case when d.totalappliedamount - :old.amount = d.amount then 'N' else 'Y' end,
             d.timestamp = :new.unapplytimestamp,
             d.systemuser_id = :new.unapplysystemuser_id
       where d.artransaction_id = :new.debitartransaction_id;
      update artransaction c
         set c.totalappliedamount = c.totalappliedamount - :old.amount,
             c.isopen_yn = case when c.totalappliedamount - :old.amount = c.amount then 'N' else 'Y' end,
             c.timestamp = :new.unapplytimestamp,
             c.systemuser_id = :new.unapplysystemuser_id
       where c.artransaction_id = :new.creditartransaction_id;
  when :old.unapplied_yn='Y' and :new.unapplied_yn='N' then  -- unapplied -> applied, normally never happens
      update artransaction d
         set d.totalappliedamount = d.totalappliedamount + :new.amount,
             d.isopen_yn = case when d.totalappliedamount + :new.amount  = d.amount then 'N' else 'Y' end,
             d.timestamp = :new.applytimestamp,
             d.systemuser_id = :new.applysystemuser_id
       where d.artransaction_id = :new.debitartransaction_id;
      update artransaction c
         set c.totalappliedamount = c.totalappliedamount + :new.amount,
             c.isopen_yn = case when c.totalappliedamount + :new.amount  = c.amount then 'N' else 'Y' end,
             c.timestamp = :new.applytimestamp,
             c.systemuser_id = :new.applysystemuser_id
       where c.artransaction_id = :new.creditartransaction_id;
--  when :old.unapplied_yn='Y' and :new.unapplied_yn='Y' then  update of unapplied amount, normally never happens
--   action: do nothing
  end case;
end AFTER_UPDATE_CASHAPPLICATION;
/


CREATE OR REPLACE TRIGGER AFTER_DELETE_CASHAPPLICATION
  after delete on cashapplication
  for each row

begin
  if :old.unapplied_yn='N' then
    update artransaction d
       set d.totalappliedamount = d.totalappliedamount - :old.amount,
           d.isopen_yn = case when d.totalappliedamount - :old.amount = d.amount then 'N' else 'Y' end,
           d.timestamp = sysdate
     where d.artransaction_id = :old.debitartransaction_id;
    update artransaction c
       set c.totalappliedamount = c.totalappliedamount - :old.amount,
           c.isopen_yn = case when c.totalappliedamount - :old.amount = c.amount then 'N' else 'Y' end,
           c.timestamp = sysdate
     where c.artransaction_id = :old.creditartransaction_id;
  end if;  
end AFTER_DELETE_CASHAPPLICATION;
/

-- Create index after bulk insert for performance reasons
CREATE  INDEX XIE3ARTRANSACTION ON ARTRANSACTION
(BILLTOCUSTOMER_LINK_ID   ASC,ISOPEN_YN   ASC,DEBITCREDIT   ASC);


-- drop temporary indexes
DROP index HXSPEED4ARTRANS;

ALTER TABLE CASHAPPLICATION MODIFY ( APPLYDATE NOT NULL);

ALTER TABLE CASHAPPLICATION MODIFY ( AMOUNT NOT NULL);
