 
-- effort to void ar cm apply , so that invoice void works.. 
 
-- it basically creates a DM/CM pair and moves the application to that..
  
-- verify that curr_id on both source and target are the esame, if they are not, then we can not do this..



-- NEED TO VERIFY THIS WILL WORK

-- process : create invoice , create check payment and credit it- look at all gltrans that have been made..


-- before start last gltrans_id = 656889

-- GLTRANS:
-- Invoice  SALES (ADD) / AR (DEDUCT)
-- Check  AR (ADD) / Bank (DEDUCT)


-- AROPEN:
-- Only one item created.. - paid is filled in... (I)

-- ARAPPLY (id = 7475)
-- Funds type 'C'  sourcedoc = 'K' targetdoc = 'I'
--?? JOURNAL ENTRY?? - which one..

-- now try our CM/DM process.
-- Credit MEMO
-- aropon = doctype = 'C'
-- gltrans : AR (ADD) / Customer Credit (DEDUCT)

-- >> DM - set Prepaid account to Customer Credits...
-- aropen = doctype = 'D'
-- gltrans : AR( MINUS) / Customer Credit (ADD)

-- fundstype ==> 'emty'
-- refnumber ==> empty
-- journalnumber=> 0

-- reftype => blank
-- ref_id => null





CREATE OR REPLACE FUNCTION voidapcreditmemoapplication(integer)
  RETURNS integer AS
$BODY$
-- Copyright (c) 1999-2011 by OpenMFG LLC, d/b/a xTuple. 
-- See www.xtuple.com/CPAL for the full text of the software license.
DECLARE
    i_id ALIAS FOR $1;
    
    r_apply RECORD;
    
    v_source_curr_id  INTEGER;
    v_target_curr_id  INTEGER;
    
  
    v_debit_id  INTEGER;
    v_credit_id INTEGER;
    v_credit_jid INTEGER;
    v_id INTEGER;

    is_rcpt_void BOOLEAN;
    
BEGIN
  
    -- the record we are voiding..
       
    SELECT
          *
        INTO
            r_apply
        FROM
            apapply
        WHERE
            apapply_id = i_id;
            
    IF NOT FOUND THEN
        RAISE EXCEPTION 'voidapcreditmemoapplication: apapply % not found', i_id;
    END IF;
    
    -- see if we have already done this..
    
    
    SELECT
            apopen_id
        INTO
            v_id
        FROM
            apopen
        WHERE
            apopen_docnumber = 'AP-APPLICATION-VOID-DEBIT-' || i_id
        LIMIT 1;
    
    IF FOUND THEN
        RAISE EXCEPTION 'this application has already been voided';
    END IF;
        
    
    
    -- fetch the currencies from the affected records..
    is_rcpt_void := false;
    
    IF r_apply.apapply_source_doctype = 'K' THEN
    
        -- still need to fix unposted applications ??? -
        -- in voidinvoice?
        
        -- verify that cshrcpt is void.
        
        -- RAISE EXCEPTION ' a payment has been made on this invoice - void the payment first.';
            
        -- if source is a 
        
        -- if it's a cash reciept void then we have to -ve the number being applied...
        
        -- cary on and create a pair of Cm/DM
        
        SELECT 
                cashrcpt_curr_id,
                cashrcpt_void
            INTO
                v_source_curr_id,
                is_rcpt_void
            FROM
                cashrcpt
            WHERE
                cashrcpt_number = r_apply.apapply_source_docnumber;
        
        
        IF (NOT is_rcpt_void) THEN
            RAISE EXCEPTION ' a payment has been made on this invoice - void the payment first.';
        END IF;
        
    ELSE
        IF r_apply.apapply_source_apopen_id < 0 THEN
            RAISE EXCEPTION 'source_apopen_id < 0 - can not handle this.';
        END IF;
    
        SELECT
                apopen_curr_id
            INTO
                v_source_curr_id
            FROM
                apopen
            WHERE
                apopen_id = r_apply.apapply_source_apopen_id;
      
    
    END IF;
    
    IF r_apply.apapply_target_doctype = 'R' THEN
    
        -- there is a GL transaction for 'R' in applyarapplication....
        -- so we need to add that in later...
        RAISE EXCEPTION 'Target doctype = R not supported yet';
    END IF;
    
    IF r_apply.apapply_target_doctype = 'K' THEN
             RAISE EXCEPTION ' a payment has been made on this invoice - void the payment first.';
            SELECT
                cashrcpt_curr_id
            INTO
                v_target_curr_id
            FROM
                cashrcpt
            WHERE
                cashrcpt_number = r_apply.apapply_target_docnumber;
                
    ELSE
        IF r_apply.apapply_target_apopen_id < 0 THEN
            RAISE EXCEPTION 'source_apopen_id < 0 - can not handle this.';
        END IF;
    
    
        SELECT
              apopen_curr_id
            INTO
                v_target_curr_id
            FROM
                apopen
            WHERE
                apopen_id = r_apply.apapply_target_apopen_id;
    
    END IF;
    
    
   
    --IF v_target_curr_id !=  v_source_curr_id THEN
    --    RAISE EXCEPTION 'voiding a multi currency application does not work yet...';
    --END IF;

    
    -- now create a debit and credit to move the application to.. (equivlant to the payment..) = source...
    
    
    SELECT createapCreditMemo (
        NULL,
        r_apply.apapply_vend_id,
        NULL, -- journaln o.
       'AP-APPLICATION-VOID-CREDIT-' || i_id,
         '', -- po nm
        r_apply.apapply_postdate,
        ABS(r_apply.apapply_target_paid),
        'AP Application Voided (Credit) - ' || r_apply.apapply_source_docnumber || ' - ' || r_apply.apapply_target_docnumber,  
        -1, -- account
        r_apply.apapply_postdate,
        (SELECT vend_terms_id FROM vendinfo where vend_id = r_apply.apapply_vend_id),
         r_apply.apapply_curr_id
    ) INTO  v_credit_jid;
    
    IF (v_credit_jid < 1) THEN
        RAISE EXCEPTION 'createapdebitmemo FAILED';
    END IF;
    
    SELECT apopen_id INTO v_credit_id FROM apopen where apopen_journalnumber  = v_credit_jid;
    
    IF NOT FOUND OR (v_credit_id < 1) THEN
        RAISE EXCEPTION 'createapdebitmemo FAILED';
    END IF;
    
    
    SELECT createapdebitmemo(
        NULL,
        r_apply.apapply_vend_id,
        NULL,
        'AP-APPLICATION-VOID-DEBIT-' || i_id,
        '',
        r_apply.apapply_postdate,
        ABS(r_apply.apapply_target_paid),
        'AP Application Voided (Debit) - ' || r_apply.apapply_source_docnumber || ' - ' || r_apply.apapply_target_docnumber,  
        (SELECT apopen_accnt_id FROM apopen where apopen_id = v_credit_id),
        r_apply.apapply_postdate,
         (SELECT vend_terms_id FROM vendinfo where vend_id = r_apply.apapply_vend_id),
        r_apply.apapply_curr_id
    ) INTO v_debit_id;
 
    IF (v_debit_id < 1) THEN
        RAISE EXCEPTION 'createapdebitmemo FAILED';
    END IF;
    
    
    
    
    UPDATE apapply SET
       apapply_source_apopen_id = v_credit_id,
       apapply_source_doctype = 'C',
       apapply_source_docnumber = 'AP-APPLICATION-VOID-CREDIT-' || i_id,
       
       apapply_target_apopen_id = v_debit_id, 
       apapply_target_doctype = 'D',
       apapply_target_docnumber =  'AP-APPLICATION-VOID-DEBIT-' || i_id,
       
       apapply_journalnumber = 0 ,
       -- fix the applied amounts..
        apapply_target_paid = ABS(r_apply.apapply_target_paid),
        apapply_amount = ABS(r_apply.apapply_amount)
    WHERE
       apapply_id     =  i_id;
 
 
    -- not sure if record will be updated by the above...
    -- hope not.. it would be a bit wierd...
    -- remove the payments from the aropen account - note that cashrecipts will
    -- not have a aropen_id (it's '-1' ) so they will get ignored.
    
    UPDATE
        apopen
    SET
        apopen_paid = apopen_paid - currtocurr(r_apply.apapply_curr_id, apopen_curr_id, ABS(r_apply.apapply_target_paid) , r_apply.apapply_postdate),
        apopen_open = true,
        apopen_closedate = NULL
    WHERE
        apopen_id IN (r_apply.apapply_target_apopen_id  ,  r_apply.apapply_source_apopen_id) ;
    
    -- FOR OUR NEW ONES...
    UPDATE
        apopen
    SET
        apopen_paid = apopen_amount,
        apopen_open = false,
        apopen_closedate = r_apply.apapply_postdate
    WHERE
        apopen_id IN (v_credit_id, v_debit_id) ;
    
    
    -- if it's a cash reciept, then we need to change the cashrcptitem
    IF (is_rcpt_void) THEN
    
        --RAISE EXCEPTION 'attempt to set cashrcpt aropen_id to % WHERE cashrcptitem_cashrcpt_id  IN (cashrcptitem_aropen_id = %',
        --   v_debit_id,r_apply.apapply_target_aropen_id;
            
        ALTER TABLE cashrcptitem DISABLE TRIGGER USER;
        UPDATE
                cashrcptitem
            SET
                cashrcptitem_apopen_id = v_debit_id
        WHERE
           
                cashrcptitem_apopen_id = r_apply.apapply_target_aropen_id;
            
        
        ALTER TABLE cashrcptitem ENABLE TRIGGER USER;
    
    END IF;
    
    
    RETURN i_id;
 
END;
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;
ALTER FUNCTION voidapcreditmemoapplication(integer)
  OWNER TO admin;
  
  
  
--select voidapcreditmemoapplication(7475);
--select voidapcreditmemoapplication(7477);
