--THIS FILE IS NEEDED ONLY FOR THE ONLINE UPDATE!!!

--PLEASE RUN THE FOLLOWING SCRIPT IN ORDER TO CREATE NEW SPEED FIELDS/VIEWS NEEDED FOR A/R (REQUIRED IF A/R OR BACK OFFICE INTERFACE IS IN USE)

---------------------------------------------------------------------------------------------
-- data redundancy 
---------------------------------------------------------------------------------------------

-----------------------------------------------------------------
-- used to fill speed fields
-----------------------------------------------------------------

create or replace view opayments
as 
select 
 ainv_id
,decode(ainv_id,ainv_original_ainv_id,AINV_PAYM_ZPOS_ID,getOrigPaymentId(AINV_ID)) oainv_paym_zpos_id
,decode(AINV_ID,AINV_ORIGINAL_AINV_ID,AINV_MANUALPOST, getOrigManualpostFlag(AINV_ADJUST_AINV_ID)) oAINV_MANUALPOST
from ainv;

/*create or replace view v8_sys_ar_calc_ainv_bal_old as
select ainv_id
  ,decode(ainv_cd,1,ainv_amount,0) DebitAmount
  ,  decode(ainv_cd,5,-ainv_amount,0)
   + decode(ainv_cd,5,-1,1)
   * sum(decode(ainv_cd
                ,1,aipl_amount
                ,5,aipl_amount*decode(AIPL_credit_AINV_ID,AINV_ID,1,-1) --* to make it easier to read
               )   
   ) CreditAmount
from ainv,aipl 
where AIPL_DEBIT_AINV_ID=AINV_ID or AIPL_credit_AINV_ID=AINV_ID
group by ainv_id,ainv_cd,ainv_amount
union all
select ainv_id
  ,decode(ainv_cd,1,ainv_amount,0) DebitAmount
  ,decode(ainv_cd,5,-ainv_amount,0) CreditAmount 
from ainv 
where not exists (select 1 from aipl where AIPL_DEBIT_AINV_ID=AINV_ID or AIPL_credit_AINV_ID=AINV_ID);*/

create or replace view v8_sys_ar_calc_ainv_bal as
select ainv_id,sum(DebitAmount) DebitAmount,sum(CreditAmount) CreditAmount
from (
select ainv_id
  ,0 DebitAmount
  ,decode(ainv_cd,5,-1,1)*decode(ainv_cd,1,aipl_amount,5,aipl_amount*decode(AIPL_credit_AINV_ID,AINV_ID,1,-1),0) CreditAmount
from ainv,aipl 
where AIPL_DEBIT_AINV_ID=AINV_ID or AIPL_credit_AINV_ID=AINV_ID
union all
select ainv_id
  ,decode(ainv_cd,1,ainv_amount,0) DebitAmount
  ,decode(ainv_cd,5,-ainv_amount,0) CreditAmount 
from ainv
) 
group by ainv_id;

---------------------------------------------------------------------------------------------------

alter table ainv add ainv_cr number(30,4) default 0 not null;

alter table ainv add ainv_db number(30,4) default 0 not null;

update ainv set
   ainv_cr=(select creditamount from v8_sys_ar_calc_ainv_bal v where v.ainv_id=ainv.ainv_id)
  ,ainv_db=(select debitamount from v8_sys_ar_calc_ainv_bal v where v.ainv_id=ainv.ainv_id);

alter table ainv add ainv_original_zpos_id number(30);

alter table ainv add ainv_original_zpos_zdco_id number(30);

alter table ainv add ainv_original_manualpost number(1);

---------------------------------------------------------------------------------------------------
--need to make sure that there are no invalid objects in the database (all views, functions, etc. need to be compiled)

begin
DECLARE 
   sc varchar(20);
   begin
         select sys_context('USERENV','CURRENT_SCHEMA') into sc from dual; 
         DBMS_UTILITY.COMPILE_SCHEMA( sc, FALSE ); 
   end;
end;
/

commit;

---------------------------------------------------------------------------------------------------

drop table ainv2;

create table ainv2 (ainv_id number(30) primary key,ainv_ozpos_id number(30),ainv_omanualpost number(1));

insert into ainv2 select ainv.ainv_id,oainv_paym_zpos_id,oainv_manualpost from opayments,ainv where ainv.ainv_id=opayments.ainv_id; 

drop index ainv_original_zpos_id;

drop index ainv_original_zpos_zdco_id;

update ainv set
   ainv_original_zpos_id= (select ainv_ozpos_id from ainv2 where ainv2.ainv_id=ainv.ainv_id )
   ,ainv_original_zpos_zdco_id=(select zpos_zdco_id from ainv2,zpos where ainv2.ainv_id=ainv.ainv_id and zpos_id=ainv_ozpos_id)
   ,ainv_original_manualpost=(select ainv_omanualpost from ainv2 where ainv2.ainv_id=ainv.ainv_id)
   ;

create index ainv_original_zpos_id on ainv(ainv_original_zpos_id) tablespace v8index;

create index ainv_original_zpos_zdco_id on ainv(ainv_original_zpos_zdco_id) tablespace v8index;

drop table ainv2;

