-- Allocation Reversal Patch -- Summary of all sql modifications --------- DDL CREATE OR REPLACE TRIGGER C_Allocation_Trg AFTER INSERT OR UPDATE OF IsActive, C_BPartner_ID ON C_Allocation FOR EACH ROW DECLARE /************************************************************************* * The contents of this file are subject to the Compiere License. You may * obtain a copy of the License at http://www.compiere.org/license.html * Software is on an "AS IS" basis, WITHOUT WARRANTY OF ANY KIND, either * express or implied. See the License for details. Code: Compiere ERP+CPM * Copyright (C) 1999-2002 Jorg Janke, ComPiere, Inc. All Rights Reserved. ************************************************************************* * $Id: C_Allocation_Trg.sql,v 1.11 2004/04/22 02:13:47 jjanke Exp $ *** * Title: Allocation Processing * Description: * When inserting (from creating CashBook, Payment or Allocation) * - Update BPartner (SO_CreditUsed). * The amount has the correct sign * When updating * - Update BPartner when changed * This is reversing the creditline increase in C_Invoice_Post * Issue: * - If the amount is not allocated, CreditUsed is not changed ************************************************************************/ v_IsSOTrx C_Invoice.IsSOTrx%TYPE; v_DocBaseType C_DocType.DocBaseType%TYPE; v_amt NUMBER; BEGIN IF (INSERTING OR UPDATING) THEN IF (:new.C_Invoice_ID IS NOT NULL) THEN -- Is it a Sales Transaction / credit memo SELECT i.IsSOTrx, d.DocBaseType INTO v_IsSOTrx, v_DocBaseType FROM C_DocType d, C_Invoice i WHERE d.C_DocType_ID=i.C_DocType_ID AND i.C_Invoice_ID = :new.C_Invoice_ID; END IF; END IF; -- Only Sales Trx IF (v_IsSOTrx = 'N') THEN RETURN; END IF; v_amt := :new.Amount + :new.DiscountAmt + :new.WriteOffAmt; -- Reversal IF (:new.IsActive='N') THEN v_amt := v_amt * -1; END IF; IF (INSERTING) THEN -- Update BPartner CreditUsed IF (:new.C_BPartner_ID IS NOT NULL) THEN UPDATE C_BPartner SET SO_CreditUsed = SO_CreditUsed - C_Base_Convert (v_amt, :new.C_Currency_ID, :new.AD_Client_ID, :new.DateTrx, :new.AD_Org_ID) WHERE C_BPartner_ID = :new.C_BPartner_ID; END IF; END IF; -- Inserting -- IF (UPDATING) THEN -- Reversal IF (:old.IsActive <> :new.IsActive) THEN UPDATE C_BPartner SET SO_CreditUsed = SO_CreditUsed - C_Base_Convert (v_amt, :new.C_Currency_ID, :new.AD_Client_ID, :new.DateTrx, :new.AD_Org_ID) WHERE C_BPartner_ID = :new.C_BPartner_ID; -- Reset Flag for Invoice/Payment UPDATE C_Invoice SET IsPaid='N' WHERE C_Invoice_ID=:old.C_Invoice_ID; UPDATE C_Payment SET IsAllocated='N' WHERE C_Payment_ID=:old.C_Payment_ID; -- BPartner changed ELSIF (:old.C_BPartner_ID <> :new.C_BPartner_ID) THEN IF (:old.C_BPartner_ID IS NOT NULL) THEN UPDATE C_BPartner SET SO_CreditUsed = SO_CreditUsed + C_Base_Convert (v_amt, :old.C_Currency_ID, :old.AD_Client_ID, :old.DateTrx, :old.AD_Org_ID) WHERE C_BPartner_ID = :old.C_BPartner_ID; END IF; -- IF (:new.C_BPartner_ID IS NOT NULL) THEN UPDATE C_BPartner SET SO_CreditUsed = SO_CreditUsed - C_Base_Convert (v_amt, :new.C_Currency_ID, :new.AD_Client_ID, :new.DateTrx, :new.AD_Org_ID) WHERE C_BPartner_ID = :new.C_BPartner_ID; END IF; END IF; END IF; -- Updating END C_Allocation_Trg; / CREATE OR REPLACE FUNCTION C_Invoice_Open ( p_C_Invoice_ID IN NUMBER, p_C_InvoicePaySchedule_ID IN NUMBER DEFAULT 0 ) RETURN NUMBER /************************************************************************* * The contents of this file are subject to the Compiere License. You may * obtain a copy of the License at http://www.compiere.org/license.html * Software is on an "AS IS" basis, WITHOUT WARRANTY OF ANY KIND, either * express or implied. See the License for details. Code: Compiere ERP+CPM * Copyright (C) 1999-2001 Jorg Janke, ComPiere, Inc. All Rights Reserved. ************************************************************************* * $Id: C_Invoice_Open.sql,v 1.10 2004/04/22 01:59:38 jjanke Exp $ *** * Title: Calculate Open Item Amount in Invoice Currency * Description: * Add up total amount open for C_Invoice_ID if no split payment. * Grand Total minus Sum of Allocations in Invoice Currency * * For Split Payments: * Allocate Payments starting from first schedule. SELECT C_Invoice_Open (109) FROM DUAL; SELECT C_Invoice_Open (109, null) FROM DUAL; SELECT C_Invoice_Open (109, 11) FROM DUAL; SELECT C_Invoice_Open (109, 102) FROM DUAL; SELECT C_Invoice_Open (109, 103) FROM DUAL; SELECT * FROM RV_OpenItem WHERE C_Invoice_ID=109; SELECT C_InvoicePaySchedule_ID, DueAmt FROM C_InvoicePaySchedule WHERE C_Invoice_ID=109 ORDER BY DueDate; * ************************************************************************/ AS v_Currency_ID NUMBER(10); v_TotalOpenAmt NUMBER := 0; v_PaidAmt NUMBER := 0; v_Remaining NUMBER := 0; -- CURSOR Cur_Alloc IS SELECT AD_Client_ID, AD_Org_ID, Amount, DisCountAmt, WriteOffAmt, C_Currency_ID, DateTrx FROM C_Allocation WHERE C_Invoice_ID = p_C_Invoice_ID AND IsActive='Y'; CURSOR Cur_PaySchedule IS SELECT C_InvoicePaySchedule_ID, DueAmt FROM C_InvoicePaySchedule WHERE C_Invoice_ID = p_C_Invoice_ID ORDER BY DueDate; BEGIN -- Get Currency BEGIN SELECT MAX(C_Currency_ID), SUM(GrandTotal) INTO v_Currency_ID, v_TotalOpenAmt FROM C_Invoice_v -- corrected for CM / Split Payment WHERE C_Invoice_ID = p_C_Invoice_ID; EXCEPTION -- Invoice in draft form WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE('InvoiceOpen - ' || SQLERRM); RETURN NULL; END; -- Calculate Allocated Amount FOR a IN Cur_Alloc LOOP v_PaidAmt := v_PaidAmt -- Allocation + C_Currency_Convert(a.Amount + a.DisCountAmt + a.WriteOffAmt, a.C_Currency_ID, v_Currency_ID, a.DateTrx, null, a.AD_Client_ID, a.AD_Org_ID); END LOOP; -- Do we have a Payment Schedule ? IF (p_C_InvoicePaySchedule_ID > 0) THEN -- if not valid = lists invoice amount v_Remaining := v_PaidAmt; FOR s IN Cur_PaySchedule LOOP IF (s.C_InvoicePaySchedule_ID = p_C_InvoicePaySchedule_ID) THEN v_TotalOpenAmt := s.DueAmt - v_Remaining; IF (v_TotalOpenAmt < 0) THEN v_TotalOpenAmt := 0; END IF; -- DBMS_OUTPUT.PUT_LINE('Total=' || v_TotalOpenAmt || ', Due=' || s.DueAmt); ELSE -- calculate amount, which can be allocated to next schedule v_Remaining := v_Remaining - s.DueAmt; IF (v_Remaining < 0) THEN v_Remaining := 0; END IF; -- DBMS_OUTPUT.PUT_LINE('Remaining=' || v_Remaining); END IF; END LOOP; ELSE v_TotalOpenAmt := v_TotalOpenAmt - v_PaidAmt; END IF; -- Ignore Rounding IF (v_TotalOpenAmt BETWEEN -0.00999 AND 0.00999) THEN v_TotalOpenAmt := 0; END IF; -- Round to penny v_TotalOpenAmt := ROUND(COALESCE(v_TotalOpenAmt,0), 2); RETURN v_TotalOpenAmt; END C_Invoice_Open; / CREATE OR REPLACE FUNCTION C_Invoice_Paid ( p_C_Invoice_ID IN NUMBER, p_C_Currency_ID IN NUMBER, p_Multiplier IN NUMBER -- DEFAULT 1 ) RETURN NUMBER /************************************************************************* * The contents of this file are subject to the Compiere License. You may * obtain a copy of the License at http://www.compiere.org/license.html * Software is on an "AS IS" basis, WITHOUT WARRANTY OF ANY KIND, either * express or implied. See the License for details. Code: Compiere ERP+CPM * Copyright (C) 1999-2001 Jorg Janke, ComPiere, Inc. All Rights Reserved. ************************************************************************* * $Id: C_Invoice_Paid.sql,v 1.10 2004/04/22 01:59:38 jjanke Exp $ *** * Title: Calculate Paid/Allocated amount in Currency * Description: * Add up total amount paid for for C_Invoice_ID. * Split Payments are ignored. * all allocation amounts converted to invoice C_Currency_ID * round it to the nearest cent * and adjust for CreditMemos with the multiplier (-1, 1) * * As C_Invoice_Paid is mutating, use: * IsPaid = DECODE(C_Invoice_Paid(C_Invoice_ID, C_Currency_ID, Multiplier), * GrandTotal, 'Y', 'N') * ************************************************************************/ AS v_Multiplier NUMBER := 1; v_PaymentAmt NUMBER := 0; CURSOR Cur_Alloc IS SELECT AD_Client_ID, AD_Org_ID, Amount, DisCountAmt, WriteOffAmt, C_Currency_ID, DateTrx FROM C_Allocation WHERE C_Invoice_ID = p_C_Invoice_ID AND IsActive='Y'; BEGIN -- Default IF (p_Multiplier IS NOT NULL) THEN v_Multiplier := p_Multiplier; END IF; -- Calculate Allocated Amount FOR a IN Cur_Alloc LOOP v_PaymentAmt := v_PaymentAmt + C_Currency_Convert(a.Amount + a.DisCountAmt + a.WriteOffAmt, a.C_Currency_ID, p_C_Currency_ID, a.DateTrx, null, a.AD_Client_ID, a.AD_Org_ID); END LOOP; -- RETURN ROUND(NVL(v_PaymentAmt,0), 2) * v_Multiplier; END C_Invoice_Paid; / CREATE OR REPLACE FUNCTION C_Payment_Allocated ( p_C_Payment_ID IN NUMBER, p_C_Currency_ID IN NUMBER ) RETURN NUMBER /************************************************************************* * The contents of this file are subject to the Compiere License. You may * obtain a copy of the License at http://www.compiere.org/license.html * Software is on an "AS IS" basis, WITHOUT WARRANTY OF ANY KIND, either * express or implied. See the License for details. Code: Compiere ERP+CRM * Copyright (C) 1999-2001 Jorg Janke, ComPiere, Inc. All Rights Reserved. ************************************************************************* * $Id: C_Payment_Allocated.sql,v 1.4 2004/04/22 01:59:38 jjanke Exp $ *** * Title: Calculate Allocated Payment Amount in Payment Currency * Description: * Use * SET IsAllocated = DECODE(C_Payment_Allocated(C_Payment_ID, C_Currency_ID), PayAmt, 'Y', 'N') * similar to C_Invoice_Paid ************************************************************************/ AS v_AllocatedAmt NUMBER := 0; CURSOR Cur_Alloc IS SELECT AD_Client_ID, AD_Org_ID, Amount, C_Currency_ID, DateTrx FROM C_Allocation WHERE C_Payment_ID = p_C_Payment_ID AND IsActive='Y' AND C_Invoice_ID IS NOT NULL; BEGIN -- Calculate Allocated Amount FOR a IN Cur_Alloc LOOP v_AllocatedAmt := v_AllocatedAmt + C_Currency_Convert(a.Amount, a.C_Currency_ID, p_C_Currency_ID, a.DateTrx, null, a.AD_Client_ID, a.AD_Org_ID); END LOOP; -- Round to penny v_AllocatedAmt := ROUND(NVL(v_AllocatedAmt,0), 2); RETURN v_AllocatedAmt; END C_Payment_Allocated; / CREATE OR REPLACE FUNCTION C_Payment_Available ( p_C_Payment_ID IN NUMBER ) RETURN NUMBER /************************************************************************* * The contents of this file are subject to the Compiere License. You may * obtain a copy of the License at http://www.compiere.org/license.html * Software is on an "AS IS" basis, WITHOUT WARRANTY OF ANY KIND, either * express or implied. See the License for details. Code: Compiere ERP+CRM * Copyright (C) 1999-2001 Jorg Janke, ComPiere, Inc. All Rights Reserved. ************************************************************************* * $Id: C_Payment_Available.sql,v 1.4 2004/04/22 01:59:38 jjanke Exp $ *** * Title: Calculate Available Payment Amount in Payment Currency * Description: * similar to C_Invoice_Open ************************************************************************/ AS v_Currency_ID NUMBER(10); v_AvailableAmt NUMBER := 0; CURSOR Cur_Alloc IS SELECT AD_Client_ID, AD_Org_ID, Amount, C_Currency_ID, DateTrx FROM C_Allocation WHERE C_Payment_ID = p_C_Payment_ID AND IsActive='Y' AND C_Invoice_ID IS NOT NULL; BEGIN -- Get Currency SELECT C_Currency_ID, PayAmt INTO v_Currency_ID, v_AvailableAmt FROM C_Payment WHERE C_Payment_ID = p_C_Payment_ID; -- Calculate Allocated Amount FOR a IN Cur_Alloc LOOP v_AvailableAmt := v_AvailableAmt - C_Currency_Convert(a.Amount, a.C_Currency_ID, v_Currency_ID, a.DateTrx, null, a.AD_Client_ID, a.AD_Org_ID); END LOOP; -- Ignore Rounding IF (v_AvailableAmt BETWEEN -0.00999 AND 0.00999) THEN v_AvailableAmt := 0; END IF; -- Round to penny v_AvailableAmt := ROUND(NVL(v_AvailableAmt,0), 2); RETURN v_AvailableAmt; END C_Payment_Available; / --------- Data fix UPDATE AD_Column Set IsUpdateable='Y' WHERE AD_Column_ID=4877 AND IsUpdateable<>'Y' / -- Invoices UPDATE C_Invoice i SET IsPaid = DECODE(C_Invoice_Paid(C_Invoice_ID, C_Currency_ID, 1), GrandTotal, 'Y', 'N') WHERE EXISTS (SELECT * FROM C_DocType dt WHERE i.C_DocType_ID=dt.C_DocType_ID AND SUBSTR(dt.DocBaseType,3,1)<>'C') AND IsPaid <> DECODE(C_Invoice_Paid(C_Invoice_ID, C_Currency_ID, 1), GrandTotal, 'Y', 'N') / -- Credit Memos UPDATE C_Invoice i SET IsPaid = DECODE(C_Invoice_Paid(C_Invoice_ID, C_Currency_ID, -1), GrandTotal, 'Y', 'N') WHERE EXISTS (SELECT * FROM C_DocType dt WHERE i.C_DocType_ID=dt.C_DocType_ID AND SUBSTR(dt.DocBaseType,3,1)='C') AND IsPaid <> DECODE(C_Invoice_Paid(C_Invoice_ID, C_Currency_ID, -1), GrandTotal, 'Y', 'N') / -- Payments UPDATE C_Payment SET IsAllocated = DECODE(C_Payment_Allocated(C_Payment_ID, C_Currency_ID), PayAmt, 'Y', 'N') WHERE IsAllocated <> DECODE(C_Payment_Allocated(C_Payment_ID, C_Currency_ID), PayAmt, 'Y', 'N') AND TenderType<>'X' / -- BPartner UPDATE C_BPartner bp SET SO_CreditUsed = (SELECT SUM(C_Base_Convert(C_Invoice_Open(C_Invoice_ID), C_Currency_ID, AD_Client_ID, SysDate)) FROM C_Invoice i -- invoice_open returns CM corrected amt WHERE i.C_BPartner_ID=bp.C_BPartner_ID AND i.IsSOTrx='Y') WHERE EXISTS (SELECT * FROM C_Invoice i WHERE i.C_BPartner_ID=bp.C_BPartner_ID AND i.IsSOTrx='Y') / COMMIT /