create or replace TRIGGER DT_VOUCH_IBCA 
BEFORE INSERT OR UPDATE OF confirmed ON voucher referencing old as old new as new FOR EACH ROW
DECLARE
   --PRAGMA AUTONOMOUS_TRANSACTION;
   ll_ibcacount number(3) := 0;
   ls_sitecodepay char(5);
   ll_mcount number := 0;

BEGIN
   if :new.confirmed = 'Y' and :new.net_amt <> 0 then
      -- 1. check pay site
      begin
         select count(1) into  ll_mcount from  site_supplier
         where site_code = :new.site_code and supp_code = :new.supp_code;
      exception when others then
         ll_mcount := 0 ;
      end;
      if ll_mcount > 0 then
          begin
			 select site_code__pay into  ls_sitecodepay from  site_supplier
			 where site_code = :new.site_code and supp_code = :new.supp_code;
          exception when others then
             null;
          end;
      else
          begin
             select s.site_code__pay into  ls_sitecodepay from  supplier s
             where s.supp_code = :new.supp_code;
          exception when others then
             ls_sitecodepay := :new.site_code;
          end;
    end if;
      if ls_sitecodepay is null or length(trim(ls_sitecodepay)) = 0 then
        ls_sitecodepay := :new.site_code;
      end if;
      if trim(:new.site_code) <> trim(ls_sitecodepay) then
          begin
          select count(1) into ll_ibcacount from  misc_payables
             where tran_ser = 'P-IBCA'
             and ref_no = :new.tran_id
             and site_code = ls_sitecodepay ;
          exception when others then
             ll_ibcacount := 0;
          end;
		if  ll_ibcacount = 0 then
			 raise_application_error( -20601, 'IBCA not created pay site [' || ls_sitecodepay || '] voucher site  [' || :new.site_code || ']'  );
		end if;
      end if;


  end if;

END;

/



create or replace TRIGGER DT_MVOUC_IBCA 
BEFORE INSERT OR UPDATE OF confirmed ON misc_voucher referencing old as old new as new FOR EACH ROW
DECLARE
   --PRAGMA AUTONOMOUS_TRANSACTION;
   ll_ibcacount number(3) := 0;
   ls_sitecodepay char(5);
   

BEGIN
   if :new.confirmed = 'Y' and :new.net_amt <> 0 then
      -- 1. check pay site
      ls_sitecodepay := :new.SITE_CODE__PAY;
      if  ls_sitecodepay is null or length(trim(ls_sitecodepay)) = 0 then
        ls_sitecodepay := :new.site_code;
      end if;
      if trim(:new.site_code) <> trim(ls_sitecodepay) then
          begin
          select count(1) into ll_ibcacount from  misc_payables
             where tran_ser = 'P-IBCA'
             and ref_no = :new.tran_id
             and site_code = ls_sitecodepay ;
          exception when others then
             ll_ibcacount := 0;
          end;
		if  ll_ibcacount = 0 then
			 raise_application_error( -20601, 'IBCA not created pay site [' || ls_sitecodepay || '] voucher site  [' || :new.site_code || ']'  );
		end if;
      end if;


  end if;

END;

/


create or replace TRIGGER DT_DRCRPAY_IBCA 
BEFORE INSERT OR UPDATE OF confirmed ON drcr_pay referencing old as old new as new FOR EACH ROW
DECLARE
   --PRAGMA AUTONOMOUS_TRANSACTION;
   ll_ibcacount number(3) := 0;
   ls_sitecodepay char(5);
   ll_mcount number := 0;
   

BEGIN
   if :new.confirmed = 'Y' and :new.amount <> 0 then
      -- 1. check pay site
      begin
         select count(1) into  ll_mcount from  site_supplier
         where site_code = :new.site_code and supp_code = :new.supp_code;
      exception when others then
         ll_mcount := 0 ;
      end;
      if ll_mcount > 0 then
          begin
         select site_code__pay into  ls_sitecodepay from  site_supplier
         where site_code = :new.site_code and supp_code = :new.supp_code;
        exception when others then
            null;
        end;
      else
          begin
             select s.site_code__pay into  ls_sitecodepay from  supplier s
             where s.supp_code = :new.supp_code;
          exception when others then
             ls_sitecodepay := :new.site_code;
          end;
    end if;
      if ls_sitecodepay is null or length(trim(ls_sitecodepay)) = 0 then
        ls_sitecodepay := :new.site_code;
      end if;
      if trim(:new.site_code) <> trim(ls_sitecodepay) then
          begin
          select count(1) into ll_ibcacount from  misc_payables
             where tran_ser = 'P-IBCA'
             and ref_no = :new.tran_id
             and site_code = ls_sitecodepay ;
          exception when others then
             ll_ibcacount := 0;
          end;
		if  ll_ibcacount = 0 then
			 raise_application_error( -20601, 'IBCA not created pay site [' || ls_sitecodepay || '] voucher site  [' || :new.site_code || ']'  );
		end if;
      end if;


  end if;

END;

/



create or replace TRIGGER DT_DRCRRCP_IBCA 
BEFORE INSERT OR UPDATE OF confirmed ON drcr_rcp referencing old as old new as new FOR EACH ROW
DECLARE
   --PRAGMA AUTONOMOUS_TRANSACTION;
   ll_ibcacount number(3) := 0;
   ls_sitecodercp char(5);
   ls_ibcagen varchar2(600);
    ll_mcount number := 0;   

BEGIN
   if :new.confirmed = 'Y' and :new.amount <> 0 then
      begin
         select trim(var_value) into  ls_ibcagen from  finparm
         where prd_code = '999999' and var_name = 'INV_IBCA_GEN';
      exception when others then
         ls_ibcagen := 'Y';
      end;
      if ls_ibcagen is null then
        ls_ibcagen := 'Y';
      end if;
      if ls_ibcagen =  'Y' then
          begin
             select count(1) into  ll_mcount from  site_customer 
             where site_code = :new.site_code and cust_code = :new.cust_code;
          exception when others then
             ll_mcount := 0;
          end;
          if ll_mcount > 0 then
          begin
             select site_code__rcp into  ls_sitecodercp from  site_customer 
             where site_code = :new.site_code and cust_code = :new.cust_code;
          exception when others then
             null;
          end;
          else
              begin
                 select c.site_code__rcp into  ls_sitecodercp from  customer c
                 where c.cust_code = :new.cust_code;
              exception when others then
                 ls_sitecodercp := :new.site_code;
              end;
        end if;
          if ls_sitecodercp is null or length(trim(ls_sitecodercp)) = 0 then
            ls_sitecodercp := :new.site_code;
          end if;
          
          if trim(:new.site_code) <> trim(ls_sitecodercp) then
              begin
              select count(1) into ll_ibcacount from  receivables
                 where tran_ser = 'R-IBCA'
                 and ref_no = :new.tran_id
                 and site_code = ls_sitecodercp ;
              exception when others then
                 ll_ibcacount := 0;
              end;
            if  ll_ibcacount = 0 then
                 raise_application_error( -20601, 'IBCA not created receipt site [' || ls_sitecodercp || '] voucher site  [' || :new.site_code || ']'  );
            end if;
          end if;
        end if;


  end if;

END;

/


create or replace TRIGGER DT_MDRCRRCP_IBCA 
BEFORE INSERT OR UPDATE OF confirmed ON misc_drcr_rcp referencing old as old new as new FOR EACH ROW
DECLARE
   --PRAGMA AUTONOMOUS_TRANSACTION;
   ll_ibcacount number(3) := 0;
   ls_sitecodercp char(5);
   ls_ibcagen varchar2(600);
    ll_mcount number := 0;   

BEGIN
   if :new.confirmed = 'Y' and :new.amount <> 0 then
      begin
         select trim(var_value) into  ls_ibcagen from  finparm
         where prd_code = '999999' and var_name = 'INV_IBCA_GEN';
      exception when others then
         ls_ibcagen := 'Y';
      end;
      if ls_ibcagen is null then
        ls_ibcagen := 'Y';
      end if;
      if ls_ibcagen =  'Y' then
          begin
             select count(1) into  ll_mcount from  site_customer 
             where site_code = :new.site_code and cust_code = :new.sundry_code;
          exception when others then
             ll_mcount := 0;
          end;
          if ll_mcount > 0 then
          begin
             select site_code__rcp into  ls_sitecodercp from  site_customer 
             where site_code = :new.site_code and cust_code = :new.sundry_code;
          exception when others then
             null;
          end;
          else
              begin
                 select c.site_code__rcp into  ls_sitecodercp from  customer c
                 where c.cust_code = :new.sundry_code;
              exception when others then
                 ls_sitecodercp := :new.site_code;
              end;
        end if;
          if ls_sitecodercp is null or length(trim(ls_sitecodercp)) = 0 then
            ls_sitecodercp := :new.site_code;
          end if;
          
          if trim(:new.site_code) <> trim(ls_sitecodercp) then
              begin
              select count(1) into ll_ibcacount from  receivables
                 where tran_ser = 'R-IBCA'
                 and ref_no = :new.tran_id
                 and site_code = ls_sitecodercp ;
              exception when others then
                 ll_ibcacount := 0;
              end;
            if  ll_ibcacount = 0 then
                 raise_application_error( -20601, 'IBCA not created receipt site [' || ls_sitecodercp || '] voucher site  [' || :new.site_code || ']'  );
            end if;
          end if;
        end if;


  end if;

END;

/


create or replace TRIGGER DT_INVOICE_IBCA 
BEFORE INSERT OR UPDATE OF confirmed ON invoice referencing old as old new as new FOR EACH ROW
DECLARE
   --PRAGMA AUTONOMOUS_TRANSACTION;
   ll_ibcacount number(3) := 0;
   ls_sitecodercp char(5);
   ls_ibcagen varchar2(600);
    ll_mcount number := 0;   

BEGIN
   if :new.confirmed = 'Y' and :new.net_amt <> 0 then
      begin
         select trim(var_value) into  ls_ibcagen from  finparm
         where prd_code = '999999' and var_name = 'INV_IBCA_GEN';
      exception when others then
         ls_ibcagen := 'Y';
      end;
      if ls_ibcagen is null then
        ls_ibcagen := 'Y';
      end if;
      if ls_ibcagen =  'Y' then
          begin
             select count(1) into  ll_mcount from  site_customer 
             where site_code = :new.site_code and cust_code = :new.cust_code;
          exception when others then
             ll_mcount := 0;
          end;
          if ll_mcount > 0 then
          begin
             select site_code__rcp into  ls_sitecodercp from  site_customer 
             where site_code = :new.site_code and cust_code = :new.cust_code;
          exception when others then
             null;
          end;
          else
              begin
                 select c.site_code__rcp into  ls_sitecodercp from  customer c
                 where c.cust_code = :new.cust_code;
              exception when others then
                 ls_sitecodercp := :new.site_code;
              end;
        end if;
          if ls_sitecodercp is null or length(trim(ls_sitecodercp)) = 0 then
            ls_sitecodercp := :new.site_code;
          end if;
          
          if trim(:new.site_code) <> trim(ls_sitecodercp) then
              begin
              select count(1) into ll_ibcacount from  receivables
                 where tran_ser = 'R-IBCA'
                 and ref_no = :new.invoice_id
                 and site_code = ls_sitecodercp ;
              exception when others then
                 ll_ibcacount := 0;
              end;
            if  ll_ibcacount = 0 then
                 raise_application_error( -20601, 'IBCA not created receipt site [' || ls_sitecodercp || '] voucher site  [' || :new.site_code || ']'  );
            end if;
          end if;
        end if;


  end if;

END;

/



