-- Function: c_invoice_post(numeric, numeric) -- DROP FUNCTION c_invoice_post(numeric, numeric); CREATE OR REPLACE FUNCTION c_invoice_post(p_pinstance_id numeric, p_invoice_id numeric) RETURNS void AS $BODY$ DECLARE /************************************************************************* * The contents of this file are subject to the Compiere Public * License 1.1 ("License"); You may not use this file except in * compliance with the License. You may obtain a copy of the License in * the legal folder of your Openbravo installation. * Software distributed under the License is distributed on an * "AS IS" basis, WITHOUT WARRANTY OF ANY KIND, either express or * implied. See the License for the specific language governing rights * and limitations under the License. * The Original Code is Compiere ERP & Business Solution * The Initial Developer of the Original Code is Jorg Janke and ComPiere, Inc. * Portions created by Jorg Janke are Copyright (C) 1999-2001 Jorg Janke, * parts created by ComPiere are Copyright (C) ComPiere, Inc.; * All Rights Reserved. * Contributor(s): Openbravo SL * Contributions are Copyright (C) 2001-2008 Openbravo, S.L. * * Specifically, this derivative work is based upon the following Compiere * file and version. ************************************************************************* * $Id: C_Invoice_Post.sql,v 1.32 2003/07/22 05:41:27 jjanke Exp $ *** * Title: Post single Invoice * Description: * Actions: COmplete, APprove, Reverse Correction, Void * * OpenItem Amount: * - C_BPartner.SO_CreditUsed is increased * - if C_CashLine entry is created * - C_Cash_Post creates C_Allocation * - C_Allocation_Trg decreases C_BPartner.SO_CreditUsed * ************************************************************************/ -- Logistice v_ResultStr VARCHAR(2000):=''; v_Message VARCHAR(2000):=''; v_Record_ID NUMERIC; v_Result NUMERIC:=1; -- Success v_TOTAL NUMERIC; v_C_Settlement_Cancel_ID NUMERIC; -- Parameter --TYPE RECORD IS REFCURSOR; Cur_Parameter RECORD; Cur_line RECORD; -- Record Info v_Client_ID NUMERIC; v_Org_ID NUMERIC; v_UpdatedBy C_INVOICE.UpdatedBy%TYPE; v_Processing C_INVOICE.Processing%TYPE; v_Processed C_INVOICE.Processed%TYPE; v_DocAction C_INVOICE.DocAction%TYPE; v_DocStatus C_INVOICE.DocStatus%TYPE; v_DoctypeReversed_ID NUMERIC; v_DocType_ID NUMERIC; v_DocTypeTarget_ID NUMERIC; v_IsApproved C_INVOICE.IsApproved%TYPE; v_IsTransferred C_INVOICE.IsTransferred%TYPE; v_PaymentRule C_INVOICE.PaymentRule%TYPE; v_PaymentTerm C_INVOICE.C_PaymentTerm_ID%TYPE; v_Order_ID NUMERIC; v_DateAcct TIMESTAMP; v_DateInvoiced TIMESTAMP; v_DocumentNo C_INVOICE.DocumentNo%TYPE; v_BPartner_ID NUMERIC; v_BPartner_User_ID NUMERIC; v_IsSOTrx C_INVOICE.IsSOTrx%TYPE; v_Posted C_INVOICE.Posted%TYPE; --Added by P.SAROBE v_documentno_Settlement VARCHAR(40); v_dateSettlement TIMESTAMP; v_Cancel_Processed CHAR(1); v_nameBankstatement VARCHAR (60); v_dateBankstatement TIMESTAMP; v_nameCash VARCHAR (60); v_dateCash TIMESTAMP; v_Bankstatementline_ID NUMERIC; v_Debtpayment_ID NUMERIC; v_CashLine_ID NUMERIC; v_ispaid CHAR(1); v_Settlement_Cancel_ID NUMERIC; --Finish added by P.Sarobe -- v_GrandTotal NUMERIC:=0; v_TotalLines NUMERIC:=0; v_Currency_ID NUMERIC; v_Multiplier NUMERIC:=1; -- v_RInvoice_ID NUMERIC; v_RDocumentNo C_INVOICE.DocumentNo%TYPE; v_NextNo NUMERIC; v_count NUMERIC; v_AD_Org_ID NUMERIC; v_POReference VARCHAR(40) ; -- v_SettlementDocType_ID NUMERIC(10) ; v_SDocumentNo C_SETTLEMENT.DocumentNo%TYPE; v_settlementID NUMERIC(10):=NULL; -- v_FirstSales C_BPARTNER.FirstSale%TYPE; v_REInOutStatus M_INOUT.DocStatus%TYPE; v_RECount NUMERIC:=0; v_REDateInvoiced TIMESTAMP; v_REtotalQtyInvoiced NUMERIC:=0; v_REdeliveredQty NUMERIC:=0; -- v_CumDiscount NUMERIC; v_OldCumDiscount NUMERIC; v_InvoiceLineSeqNo NUMERIC; v_InvoiceLine NUMERIC; v_Discount NUMERIC; v_Line NUMERIC; v_InvoiceDiscount NUMERIC; v_C_Project_Id NUMERIC; v_acctAmount NUMERIC; v_realAmount NUMERIC; Cur_InvoiceLine RECORD; Cur_Discount RECORD; Cur_CInvoiceDiscount RECORD; Cur_TaxDiscount RECORD; Cur_ReactivateInvoiceLine RECORD; Cur_LastContact RECORD; FINISH_PROCESS BOOLEAN:=FALSE; END_PROCESSING BOOLEAN:=FALSE; V_Aux NUMERIC; v_TargetDocBaseType C_DOCTYPE.DocBaseType%TYPE; v_MultiplierARC NUMERIC:=1; BEGIN IF(p_PInstance_ID IS NOT NULL) THEN -- Update AD_PInstance RAISE NOTICE '%','Updating PInstance - Processing ' || p_PInstance_ID; v_ResultStr:='PInstanceNotFound'; PERFORM AD_UPDATE_PINSTANCE(p_PInstance_ID, NULL, 'Y', NULL, NULL) ; -- Get Parameters v_ResultStr:='ReadingParameters'; FOR Cur_Parameter IN (SELECT i.Record_ID, i.AD_User_ID, p.ParameterName, p.P_String, p.P_Number, p.P_Date FROM AD_PINSTANCE i LEFT JOIN AD_PINSTANCE_PARA p ON i.AD_PInstance_ID=p.AD_PInstance_ID WHERE i.AD_PInstance_ID=p_PInstance_ID ORDER BY p.SeqNo ) LOOP v_Record_ID:=Cur_Parameter.Record_ID; END LOOP; -- Get Parameter RAISE NOTICE '%',' v_Record_ID=' || v_Record_ID; ELSE RAISE NOTICE '%','---- <>'; v_Record_ID:=p_Invoice_ID; END IF; BEGIN --BODY /*Invoiceline acct dimension*/ SELECT C_INVOICE.ISSOTRX INTO v_IsSOTrx FROM C_INVOICE WHERE C_INVOICE_ID = v_Record_ID; IF (v_IsSOTrx = 'N') THEN FOR Cur_line IN (SELECT C_INVOICELINE.C_InvoiceLine_ID, C_INVOICELINE.LinenetAmt FROM C_INVOICELINE WHERE C_Invoice_ID = v_Record_ID ) LOOP SELECT SUM(Amt) INTO v_acctAmount FROM C_INVOICELINE_ACCTDIMENSION WHERE C_InvoiceLine_ID = Cur_line.C_InvoiceLine_ID; IF (v_acctAmount <> Cur_line.LinenetAmt) THEN v_Message:='@QuantitiesNotMatch@'; RAISE EXCEPTION '%', '@QuantitiesNotMatch@'; END IF; END LOOP; END IF; /** * Read Invoice */ v_ResultStr:='ReadingInvoice'; SELECT Processing, Processed, DocAction, DocStatus, C_DocType_ID, C_DocTypeTarget_ID, IsApproved, IsTransferred, PaymentRule, C_PaymentTerm_ID, DateAcct, DateInvoiced, AD_Client_ID, AD_Org_ID, UpdatedBy, DocumentNo, C_Order_ID, IsSOTrx, C_BPartner_ID, AD_User_ID, C_Currency_ID, AD_Org_ID, POReference, Posted, c_Project_Id INTO v_Processing, v_Processed, v_DocAction, v_DocStatus, v_DocType_ID, v_DocTypeTarget_ID, v_IsApproved, v_IsTransferred, v_PaymentRule, v_PaymentTerm, v_DateAcct, v_DateInvoiced, v_Client_ID, v_Org_ID, v_UpdatedBy, v_DocumentNo, v_Order_ID, v_IsSOTrx, v_BPartner_ID, v_BPartner_User_ID, v_Currency_ID, v_AD_Org_ID, v_POReference, v_Posted, v_C_Project_Id FROM C_INVOICE WHERE C_Invoice_ID=v_Record_ID FOR UPDATE; RAISE NOTICE '%','Invoice_ID=' || v_Record_ID ||', DocAction=' || v_DocAction || ', DocStatus=' || v_DocStatus || ', DocType_ID=' || v_DocType_ID || ', DocTypeTarget_ID=' || v_DocTypeTarget_ID || ', IsApproved=' || v_IsApproved; /** * Invoice Voided, Closed, or Reversed - No Action */ IF(v_DocStatus IN('VO', 'CL', 'RE')) THEN RAISE EXCEPTION '%', '@AlreadyPosted@'; END IF; IF(NOT FINISH_PROCESS) THEN /** * Unlock */ IF(v_DocAction='XL') THEN UPDATE C_INVOICE SET Processing='N', DocAction='--', Updated=TO_DATE(NOW()) WHERE C_Invoice_ID=v_Record_ID; FINISH_PROCESS:=TRUE; END IF; END IF;--FINISH_PROCESS IF(NOT FINISH_PROCESS) THEN IF(v_Processing='Y') THEN RAISE EXCEPTION '%', '@OtherProcessActive@'; END IF; END IF;--FINISH_PROCESS IF(NOT FINISH_PROCESS) THEN /** * Everything done */ IF(v_Processed='Y' AND v_DocAction NOT IN('RC', 'RE')) THEN RAISE EXCEPTION '%', '@AlreadyPosted@'; END IF; END IF;--FINISH_PROCESS IF(NOT FINISH_PROCESS) THEN /** * Void if Document not processed */ IF(v_DocAction='VO' AND v_DocStatus NOT IN('CO', 'RE')) THEN SELECT COUNT(*) INTO v_Aux FROM C_DEBT_PAYMENT WHERE C_Invoice_ID = v_Record_ID; IF V_Aux>0 THEN RAISE EXCEPTION '%', '@InvoiceWithManualDP@'; ELSE -- Reset Lines to 0 UPDATE C_INVOICELINE SET QtyInvoiced=0, LineNetAmt=0 WHERE C_Invoice_ID=v_Record_ID; -- UPDATE C_INVOICE SET DocStatus='VO', DocAction='--', Processed='Y', Updated=TO_DATE(NOW()) WHERE C_Invoice_ID=v_Record_ID; -- END IF; FINISH_PROCESS:=TRUE; END IF; END IF;--FINISH_PROCESS IF(NOT FINISH_PROCESS) THEN /************************************************************************** * Start Processing ------------------------------------------------------ *************************************************************************/ v_ResultStr:='LockingInvoice'; BEGIN -- FOR COMMIT UPDATE C_INVOICE SET Processing='Y' WHERE C_Invoice_ID=v_Record_ID; -- Now, needs to go to END_PROCESSING to unlock -- This Commit must remanin due differences between PL execution in Oracle and Postgres IF(p_PInstance_ID IS NOT NULL) THEN END IF; EXCEPTION WHEN OTHERS THEN RAISE EXCEPTION '%',DATA_EXCEPTION; END;--FOR COMMIT BEGIN -- FOR COMMIT -- Set org lines like the headear UPDATE C_INVOICELINE SET AD_ORG_ID = (SELECT AD_ORG_ID FROM C_INVOICE WHERE C_INVOICE_ID = v_Record_ID) WHERE C_INVOICE_ID = v_Record_ID; SELECT COUNT(*) INTO v_Count FROM C_INVOICE C, C_DOCTYPE WHERE C_DOCTYPE.DocBaseType IN ('ARI', 'API','ARC','APC') AND C_DOCTYPE.IsSOTrx=C.ISSOTRX AND Ad_Isorgincluded(C.AD_Org_ID,C_DOCTYPE.AD_Org_ID, C.AD_Client_ID) <> -1 AND C.C_DOCTYPETARGET_ID = C_DOCTYPE.C_DOCTYPE_ID AND C.C_INVOICE_ID = V_RECORD_ID; IF v_Count=0 THEN RAISE EXCEPTION '%', '@NotCorrectOrgDoctypeInvoice@'; END IF; /** * Reverse Correction requires completes invoice ======================== */ IF(v_DocAction='RC' AND v_DocStatus='CO') THEN v_ResultStr:='ReverseCorrection'; -- Copy Invoice with reverese Quantities (or Amounts) SELECT * INTO v_RInvoice_ID FROM Ad_Sequence_Next('C_Invoice', v_Record_ID) ; SELECT COALESCE(C_DOCTYPE_REVERSED_ID, C_DOCTYPE_ID) INTO v_DoctypeReversed_ID FROM C_DOCTYPE WHERE C_DOCTYPE_ID=v_DocType_ID; SELECT * INTO v_RDocumentNo FROM Ad_Sequence_Doctype(v_DoctypeReversed_ID, v_Record_ID, 'Y') ; IF(v_RDocumentNo IS NULL) THEN SELECT * INTO v_RDocumentNo FROM Ad_Sequence_Doc('DocumentNo_C_Invoice', v_Client_ID, 'Y') ; END IF; v_Message:='@ReversedBy@: ' || v_RDocumentNo; -- RAISE NOTICE '%','Reversal Invoice_ID=' || v_RInvoice_ID || ' DocumentNo=' || v_RDocumentNo; v_ResultStr:='InsertInvoice ID=' || v_RInvoice_ID; -- Don't copy C_Payment_ID or C_CashLine_ID INSERT INTO C_INVOICE ( C_Invoice_ID, C_Order_ID, AD_Client_ID, AD_Org_ID, IsActive, Created, CreatedBy, Updated, UpdatedBy, IsSOTrx, DocumentNo, DocStatus, DocAction, Processing, Processed, C_DocType_ID, C_DocTypeTarget_ID, Description, IsApproved, IsTransferred, SalesRep_ID, DateInvoiced, DatePrinted, IsPrinted, DateAcct, C_PaymentTerm_ID, C_BPartner_ID, C_BPartner_Location_ID, AD_User_ID, POReference, DateOrdered, IsDiscountPrinted, C_Currency_ID, PaymentRule, C_Charge_ID, ChargeAmt, TotalLines, GrandTotal, M_PriceList_ID, C_Campaign_ID, C_Project_ID, C_Activity_ID, AD_OrgTrx_ID, User1_ID, User2_ID ) SELECT v_RInvoice_ID, C_Order_ID, AD_Client_ID, AD_Org_ID, IsActive, TO_DATE(NOW()), UpdatedBy, TO_DATE(NOW()), UpdatedBy, IsSOTrx, v_RDocumentNo, 'DR', 'CO', 'N', 'N', v_DoctypeReversed_ID, v_DoctypeReversed_ID, '(*R*: ' || DocumentNo || ') ' || Description, IsApproved, 'N', SalesRep_ID, DateInvoiced, NULL, 'N', DateAcct, C_PaymentTerm_ID, C_BPartner_ID, C_BPartner_Location_ID, AD_User_ID, POReference, DateOrdered, IsDiscountPrinted, C_Currency_ID, PaymentRule, C_Charge_ID, ChargeAmt * -1, TotalLines * -1, GrandTotal * -1, M_PriceList_ID, C_Campaign_ID, C_Project_ID, C_Activity_ID, AD_OrgTrx_ID, User1_ID, User2_ID FROM C_INVOICE WHERE C_Invoice_ID=v_Record_ID; -- Delete C_Invoice_Discounts inserted by the trigger DELETE FROM C_INVOICE_DISCOUNT WHERE C_Invoice_ID=v_RInvoice_ID; -- Insert discounts as in the reversed invoice FOR Cur_Discount IN (SELECT * FROM C_INVOICE_DISCOUNT WHERE C_Invoice_ID=v_Record_ID ORDER BY LINE ) LOOP SELECT * INTO v_NextNo FROM Ad_Sequence_Next('C_Invoice_Discount', v_Record_ID) ; INSERT INTO C_INVOICE_DISCOUNT ( C_INVOICE_DISCOUNT_ID, AD_CLIENT_ID, AD_ORG_ID, ISACTIVE, CREATED, CREATEDBY, UPDATED, UPDATEDBY, C_INVOICE_ID, C_DISCOUNT_ID, LINE, CASCADE ) VALUES ( v_NextNo, Cur_Discount.AD_Client_ID, Cur_Discount.AD_Org_ID, 'Y', TO_DATE(NOW()), v_UpdatedBy, TO_DATE(NOW()), v_UpdatedBy, v_RInvoice_ID, Cur_Discount.C_Discount_ID, Cur_Discount.Line, Cur_Discount.CASCADE ) ; END LOOP; -- Create Reversal Invoice Lines FOR Cur_InvoiceLine IN (SELECT * FROM C_INVOICELINE WHERE C_Invoice_ID=v_Record_ID AND C_INVOICE_DISCOUNT_ID IS NULL ORDER BY Line ) LOOP SELECT * INTO v_NextNo FROM Ad_Sequence_Next('C_InvoiceLine', v_Record_ID) ; INSERT INTO C_INVOICELINE ( C_InvoiceLine_ID, AD_Client_ID, AD_Org_ID, IsActive, Created, CreatedBy, Updated, UpdatedBy, C_Invoice_ID, C_OrderLine_ID, M_InOutLine_ID, Line, Description, M_Product_ID, QtyInvoiced, PriceList, PriceActual, LineNetAmt, C_Charge_ID, ChargeAmt, C_UOM_ID, C_Tax_ID, PriceStd) VALUES ( v_NextNo, Cur_InvoiceLine.AD_Client_ID, Cur_InvoiceLine.AD_Org_ID, 'Y', TO_DATE(NOW()), v_UpdatedBy, TO_DATE(NOW()), v_UpdatedBy, v_RInvoice_ID, Cur_InvoiceLine.C_OrderLine_ID, Cur_InvoiceLine.M_InoutLine_ID, Cur_InvoiceLine.Line, '*R*: ' || Cur_InvoiceLine.Description, Cur_InvoiceLine.M_Product_ID, Cur_InvoiceLine.QtyInvoiced * -1, Cur_InvoiceLine.PriceList, Cur_InvoiceLine.PriceActual, Cur_InvoiceLine.LineNetAmt * -1, Cur_InvoiceLine.C_Charge_ID, Cur_InvoiceLine.ChargeAmt * -1, Cur_InvoiceLine.C_UOM_ID, Cur_InvoiceLine.C_Tax_ID, Cur_InvoiceLine.PriceStd) ; /* OrderLine.qtyInvoiced is updated in c_invoicePost UPDATE C_OrderLine SET QtyInvoiced = QtyInvoiced - Cur_InvoiceLine.QtyInvoiced, Updated = TO_DATE(NOW()) WHERE C_OrderLine_ID=Cur_InvoiceLine.C_OrderLine_ID; */ END LOOP; -- Create Reversal Invoice Lines -- Close Invoice UPDATE C_INVOICE SET DocStatus='RE', -- it IS reversed Description=COALESCE(TO_CHAR(Description), '') || ' (*R* -> ' || v_RDocumentNo || ')', DocAction='--', Processed='Y', Updated=TO_DATE(NOW()) WHERE C_Invoice_ID=v_Record_ID; -- Post Reversal PERFORM C_INVOICE_POST(NULL, v_RInvoice_ID) ; -- Reversal Transaction is closed UPDATE C_INVOICE SET DocStatus='CL', -- the reversal transaction DocAction='--', Processed='Y' WHERE C_Invoice_ID=v_RInvoice_ID; -- If Payments from both invoices are pending, create a new settlement and cancel them SELECT COUNT(*) INTO v_count FROM C_DEBT_PAYMENT dp WHERE C_Debt_Payment_Status(dp.C_Settlement_Cancel_ID, dp.Cancel_Processed, dp.Generate_Processed, dp.IsPaid, dp.IsValid, dp.C_CashLine_ID, dp.C_BankStatementLine_ID)<>'P' AND(dp.C_Invoice_ID=v_Record_ID OR dp.C_Invoice_ID=v_RInvoice_ID) ; -- To cancel, the sum of amounts should be 0 IF(v_count=0) THEN SELECT SUM(AMOUNT) INTO v_count FROM C_DEBT_PAYMENT dp WHERE dp.C_Invoice_ID=v_Record_ID OR dp.C_Invoice_ID=v_RInvoice_ID; IF(v_count=0) THEN v_SettlementDocType_ID:=Ad_Get_Doctype(v_Client_ID, v_AD_Org_ID, TO_CHAR('STT')) ; SELECT * INTO v_settlementID FROM Ad_Sequence_Next('C_Settlement', v_Record_ID) ; SELECT * INTO v_SDocumentNo FROM Ad_Sequence_Doctype(v_SettlementDocType_ID, v_Record_ID, 'Y') ; IF(v_SDocumentNo IS NULL) THEN SELECT * INTO v_SDocumentNo FROM Ad_Sequence_Doc('DocumentNo_C_Settlement', v_Client_ID, 'Y') ; END IF; INSERT INTO C_SETTLEMENT ( C_SETTLEMENT_ID, AD_CLIENT_ID, AD_ORG_ID, ISACTIVE, CREATED, CREATEDBY, UPDATED, UPDATEDBY, DOCUMENTNO, DATETRX, DATEACCT, SETTLEMENTTYPE, C_DOCTYPE_ID, PROCESSING, PROCESSED, POSTED, C_CURRENCY_ID, C_PROJECT_ID, C_CAMPAIGN_ID, C_ACTIVITY_ID, USER1_ID, USER2_ID, CREATEFROM, ISGENERATED ) SELECT v_settlementID, AD_Client_ID, AD_Org_ID, 'Y', TO_DATE(NOW()), UpdatedBy, TO_DATE(NOW()), UpdatedBy, '*RE*'||v_SDocumentNo, TRUNC(TO_DATE(NOW())), TRUNC(TO_DATE(NOW())), 'C', v_SettlementDocType_ID, 'N', 'N', 'N', C_Currency_ID, C_PROJECT_ID, C_CAMPAIGN_ID, C_ACTIVITY_ID, USER1_ID, USER2_ID, 'N', 'Y' FROM C_INVOICE WHERE C_Invoice_ID=v_Record_ID; UPDATE C_DEBT_PAYMENT SET C_Settlement_Cancel_id=v_settlementID, UPDATED=TO_DATE(NOW()), UPDATEDBY=v_UpdatedBy WHERE C_DEBT_PAYMENT.C_Invoice_ID=v_Record_ID OR C_DEBT_PAYMENT.C_Invoice_ID=v_RInvoice_ID; PERFORM C_SETTLEMENT_POST(NULL, v_settlementID) ; END IF; END IF; END_PROCESSING:=TRUE; END IF; EXCEPTION WHEN OTHERS THEN RAISE EXCEPTION '%',DATA_EXCEPTION; END; -- FOR COMMIT END IF;--FINISH_PROCESS IF(NOT FINISH_PROCESS AND NOT END_PROCESSING) THEN /************************************************************************** * Credit Multiplier *************************************************************************/ DECLARE v_DocBaseType C_DOCTYPE.DocBaseType%TYPE; BEGIN -- Is it a Credit Memo? SELECT DocBaseType INTO v_DocBaseType FROM C_DOCTYPE WHERE C_DocType_ID=v_DocType_ID; IF(v_DocBaseType IN('ARC', 'API')) THEN v_Multiplier:=-1; END IF; END; END IF;--FINISH_PROCESS IF(NOT FINISH_PROCESS AND NOT END_PROCESSING) THEN /************************************************************************ * Actions allowed: Reactivate * Modified by Jon Alegria */ IF(v_DocAction='RE') THEN IF(v_DocStatus<>'CO') THEN RAISE EXCEPTION '%', '@NotCompletedInvoice@'; END IF; IF(NOT END_PROCESSING) THEN IF(v_Posted='Y') THEN RAISE EXCEPTION '%', '@InvoiceDocumentPosted@'; END IF; END IF;--END_PROCESSING IF(NOT END_PROCESSING) THEN SELECT COUNT(*), MAX(C_DEBT_PAYMENT_ID) INTO v_RECount, v_Debtpayment_ID FROM C_DEBT_PAYMENT WHERE C_DEBT_PAYMENT.C_Invoice_ID=v_Record_ID AND C_Debt_Payment_Status(C_Settlement_Cancel_ID, Cancel_Processed, C_DEBT_PAYMENT.Generate_Processed, IsPaid, IsValid, C_Cashline_ID, C_BankstatementLine_ID)<>'P' AND C_ORDER_ID IS NULL; IF(v_RECount<>0) THEN --Added by P.Sarobe. New messages SELECT c_Bankstatementline_Id, c_cashline_id, c_settlement_cancel_id, ispaid, cancel_processed INTO v_Bankstatementline_ID, v_CashLine_ID, v_Settlement_Cancel_ID, v_ispaid, v_Cancel_Processed FROM C_DEBT_PAYMENT WHERE C_Debt_Payment_ID = v_Debtpayment_ID; IF v_Bankstatementline_ID IS NOT NULL THEN SELECT C_BANKSTATEMENT.NAME, C_BANKSTATEMENT.STATEMENTDATE INTO v_nameBankstatement, v_dateBankstatement FROM C_BANKSTATEMENT, C_BANKSTATEMENTLINE WHERE C_BANKSTATEMENT.C_BANKSTATEMENT_ID = C_BANKSTATEMENTLINE.C_BANKSTATEMENT_ID AND C_BANKSTATEMENTLINE.C_BANKSTATEMENTLINE_ID = v_Bankstatementline_ID; RAISE EXCEPTION '%', '@ManagedDebtPaymentInvoiceBank@'||v_nameBankstatement||' '||'@Bydate@'||v_dateBankstatement; END IF; IF v_CashLine_ID IS NOT NULL THEN SELECT C_CASH.NAME, C_CASH.STATEMENTDATE INTO v_nameCash, v_dateCash FROM C_CASH, C_CASHLINE WHERE C_CASH.C_CASH_ID = C_CASHLINE.C_CASH_ID AND C_CASHLINE.C_CASHLINE_ID = v_CashLine_ID; RAISE EXCEPTION '%', '@ManagedDebtPaymentInvoiceCash@'||v_nameCash||' '||'@Bydate@'||v_dateCash; END IF; IF v_Cancel_Processed='Y' AND v_ispaid='N' THEN SELECT documentno, datetrx INTO v_documentno_Settlement, v_dateSettlement FROM C_SETTLEMENT WHERE C_SETTLEMENT_ID = v_Settlement_Cancel_ID; RAISE EXCEPTION '%', '@ManagedDebtPaymentOrderCancel@'||v_documentno_Settlement||' '||'@Bydate@'||v_dateSettlement; END IF; END IF; END IF;--END_PROCESSING--To be fixed or deprecated IF(NOT END_PROCESSING) THEN SELECT COUNT(*) INTO v_RECount FROM C_DP_MANAGEMENTLINE ml, C_DP_MANAGEMENT m, C_DEBT_PAYMENT dp WHERE ml.C_DP_Management_ID=m.C_DP_Management_ID AND ml.C_Debt_Payment_ID=dp.C_Debt_Payment_ID AND dp.C_Invoice_ID=v_Record_ID AND m.processed='Y'; IF v_RECount!=0 THEN RAISE EXCEPTION '%', '@DPInvoiceManaged@'; END IF; END IF;--END_PROCESSING IF(NOT FINISH_PROCESS AND NOT END_PROCESSING) THEN -- Pending undo not Stocked BOM's -- Undo BP Statictis --Undo first sale SELECT MIN(DateAcct) INTO v_FirstSales FROM C_INVOICE WHERE C_Invoice_ID<>v_Record_ID AND C_BPartner_ID=v_BPartner_ID; UPDATE C_BPARTNER SET FirstSale=v_FirstSales WHERE C_BPartner_ID=v_BPartner_ID; -- Undo Last contact FOR Cur_LastContact IN (SELECT Updated, DocumentNo, Ad_User_ID FROM C_INVOICE WHERE C_Invoice_ID<>v_Record_ID AND Ad_User_ID=v_BPartner_User_ID ORDER BY Updated DESC ) LOOP UPDATE AD_USER SET LastContact=Cur_LastContact.Updated, LastResult=Cur_LastContact.DocumentNo WHERE AD_User_ID=Cur_LastContact.Ad_User_ID; EXIT; END LOOP; IF(v_IsSOTrx='Y') THEN -- Undo revenue and credit limit UPDATE C_BPARTNER SET ActualLifeTimeValue=ActualLifeTimeValue -(v_Multiplier * C_Base_Convert(v_GrandTotal, v_Currency_ID, v_Client_ID, v_DateAcct, v_Org_ID)) WHERE C_BPartner_ID=v_BPartner_ID; FOR Cur_ReactivateInvoiceLine IN (SELECT C_InvoiceLine_ID, C_Orderline_ID, M_InoutLine_ID, QtyInvoiced FROM C_INVOICELINE WHERE C_Invoice_ID=v_Record_ID ) LOOP IF(Cur_ReactivateInvoiceLine.C_OrderLine_ID IS NOT NULL) THEN SELECT MAX(C_INVOICE.DateInvoiced) INTO v_REDateInvoiced FROM C_INVOICE, C_INVOICELINE WHERE C_INVOICE.C_Invoice_ID=C_INVOICELINE.C_INVOICE_ID AND C_INVOICELINE.C_ORDERLINE_ID=Cur_ReactivateInvoiceLine.C_ORDERLINE_ID AND C_INVOICELINE.C_InvoiceLine_ID<>Cur_ReactivateInvoiceLine.C_InvoiceLine_ID; UPDATE C_ORDERLINE SET QtyInvoiced=QtyInvoiced - Cur_ReactivateInvoiceLine.QtyInvoiced, DateInvoiced=v_REDateInvoiced WHERE C_ORDERLINE.C_OrderLine_ID=Cur_ReactivateInvoiceLine.C_OrderLine_ID; END IF; IF(Cur_ReactivateInvoiceLine.M_InOutLine_ID IS NOT NULL) THEN SELECT m.DOCSTATUS INTO v_REInOutStatus FROM M_INOUT m, M_INOUTLINE ml WHERE M.M_InOut_ID=ml.M_InOut_ID AND ml.M_InOutLine_ID=Cur_ReactivateInvoiceLine.M_InOutLine_ID; IF(v_REInOutStatus<>'RE') THEN SELECT COALESCE(SUM(C_INVOICELINE.QTYINVOICED), 0) INTO v_REtotalQtyInvoiced FROM C_INVOICELINE, C_INVOICE WHERE C_INVOICE.C_Invoice_ID=C_INVOICELINE.C_Invoice_ID AND C_INVOICE.Processed='Y' AND C_INVOICELINE.M_InOutLine_ID=Cur_ReactivateInvoiceLine.M_InOutLine_ID; v_REtotalQtyInvoiced:=v_REtotalQtyInvoiced - Cur_ReactivateInvoiceLine.QtyInvoiced; SELECT MovementQty INTO v_REdeliveredQty FROM M_INOUTLINE WHERE M_InOutLine_ID=Cur_ReactivateInvoiceLine.M_InOutLine_ID; UPDATE M_INOUTLINE SET IsInvoiced=( CASE v_REtotalQtyInvoiced WHEN 0 THEN 'N' ELSE 'Y' END ) WHERE M_InOutLine_ID=Cur_ReactivateInvoiceLine.M_InOutLine_ID; END IF; END IF; END LOOP; ELSE DELETE FROM M_MATCHPO WHERE C_InvoiceLine_ID IN (SELECT C_InvoiceLine_ID FROM C_INVOICELINE WHERE C_Invoice_ID=v_Record_ID) ; DELETE FROM M_MATCHINV WHERE C_InvoiceLine_ID IN (SELECT C_InvoiceLine_ID FROM C_INVOICELINE WHERE C_Invoice_ID=v_Record_ID) ; END IF; UPDATE C_INVOICE SET Processed='N', DocStatus='DR', DocAction='CO' WHERE C_Invoice_Id=v_Record_ID; --Delete automatically created records ... DELETE FROM C_CASHLINE WHERE ISGENERATED='Y' AND C_DEBT_PAYMENT_ID IN (SELECT C_DEBT_PAYMENT_ID FROM C_DEBT_PAYMENT WHERE C_INVOICE_ID=v_Record_ID) AND C_CASH_ID IN (SELECT C_CASH_ID FROM C_CASH WHERE PROCESSED='N') ; -- Updates the debt-payments of the cash, to make them not to point to the invoice UPDATE C_DEBT_PAYMENT SET C_INVOICE_ID=NULL WHERE C_Invoice_ID=v_Record_ID AND C_Order_ID IS NOT NULL; DELETE FROM C_DEBT_PAYMENT WHERE C_Invoice_ID=v_Record_ID AND COALESCE(IsAutomaticGenerated, 'Y')='Y' AND C_ORDER_ID IS NULL; UPDATE C_DEBT_PAYMENT SET IsValid='N' WHERE C_Invoice_ID=v_Record_ID AND COALESCE(IsAutomaticGenerated, 'Y')='N' AND C_ORDER_ID IS NULL; PERFORM C_BP_SOCREDITUSED_REFRESH(v_BPartner_ID) ; DELETE FROM C_INVOICELINE WHERE C_INVOICE_DISCOUNT_ID IS NOT NULL AND C_INVOICE_ID=v_Record_ID; END_PROCESSING:=TRUE; END IF;--END_PROCESSING END IF; END IF;--FINISH_PROCESS IF(NOT FINISH_PROCESS AND NOT END_PROCESSING) THEN /************************************************************************** * Actions allowed: COmplete, APprove */ IF(v_DocAction='AP' OR v_DocAction='CO') THEN SELECT COUNT(*) INTO v_count FROM C_INVOICE c, C_BPARTNER bp WHERE c.C_BPARTNER_ID=bp.C_BPARTNER_ID AND Ad_Isorgincluded(c.AD_ORG_ID, bp.AD_ORG_ID, bp.AD_CLIENT_ID)=-1 AND c.C_Invoice_ID=v_Record_ID; IF v_count>0 THEN RAISE EXCEPTION '%', '@NotCorrectOrgBpartnerInvoice@'; END IF; IF(NOT FINISH_PROCESS) THEN WHILE(v_DocType_ID<>v_DocTypeTarget_ID) LOOP BEGIN v_ResultStr:='UpdateDocType'; UPDATE C_INVOICE SET C_DocType_ID=C_DocTypeTarget_ID WHERE C_Invoice_ID=v_Record_ID; v_DocType_ID:=v_DocTypeTarget_ID; EXCEPTION WHEN OTHERS THEN v_ResultStr:='UpdateDocumentNo'; UPDATE C_INVOICE SET DocumentNo=DocumentNo || '.' WHERE C_Invoice_ID=v_Record_ID; END; END LOOP; END IF;--FINISH_PROCESS ELSE v_Message:='@ActionNotAllowedHere@ (I-' || v_DocAction || ')'; RAISE EXCEPTION '%', v_Message; END_PROCESSING:=TRUE; END IF; END IF;--FINISH_PROCESS IF(NOT FINISH_PROCESS AND NOT END_PROCESSING) THEN /************************************************************************** * Resolve not-stocked BOMs *************************************************************************/ DECLARE -- Invoice Lines with non-stocked BOMs CUR_BOM_Line RECORD; CUR_BOM RECORD; -- CountNo NUMERIC; v_PriceList_Version_ID NUMERIC; v_NextNo NUMERIC; v_Line NUMERIC; v_ChargeAmt NUMERIC; -- v_Offer_ID NUMERIC; v_DateInvoiced TIMESTAMP; v_BPartner_ID NUMERIC; v_PriceList_ID NUMERIC; -- CUR_PriceList_Version RECORD; BEGIN v_ResultStr:='ResolveBOM'; LOOP -- How many BOMs do we have:1 SELECT COUNT(*) INTO CountNo FROM C_INVOICELINE l WHERE l.C_Invoice_ID=v_Record_ID AND EXISTS (SELECT * FROM M_PRODUCT p WHERE l.M_Product_ID=p.M_Product_ID AND p.IsBOM='Y' AND p.IsStocked='N' AND p.productType='I' ) ; -- Nothing to do:2 EXIT WHEN CountNo=0; RAISE NOTICE '%',' BOMs to resolve=' || CountNo; -- Get Price List Version FOR CUR_PriceList_Version IN (SELECT COALESCE(SUM(v.M_PriceList_Version_ID), 0) AS PriceList_Version_ID, o.M_PriceList_ID AS M_PriceLIst_ID FROM M_PRICELIST_VERSION v, C_INVOICE o WHERE v.M_PriceList_ID=o.M_PriceList_ID AND v.ValidFrom<=o.DateOrdered AND v.IsActive='Y' AND o.C_Invoice_ID=v_Record_ID ORDER BY v.ValidFrom DESC ) LOOP v_PriceList_Version_ID:=CUR_PriceList_Version.PriceList_Version_ID; v_PriceList_ID := CUR_PriceList_Version.M_PriceList_ID; EXIT; END LOOP; --Reference TIMESTAMP for price DateOrdered SELECT C_BPARTNER_ID, DATEORDERED INTO v_BPartner_ID, v_DateInvoiced FROM C_INVOICE WHERE C_INVOICE_ID=v_Record_ID; -- Replace Lines FOR CUR_BOM_Line IN (SELECT * FROM C_INVOICELINE l WHERE l.C_Invoice_ID=v_Record_ID AND IsActive='Y' AND EXISTS (SELECT * FROM M_PRODUCT p WHERE l.M_Product_ID=p.M_Product_ID AND p.IsBOM='Y' AND p.IsStocked='N' AND p.productType='I' ) ORDER BY l.Line FOR UPDATE ) LOOP v_Line:=CUR_BOM_Line.Line; -- One Time variables v_ChargeAmt:=CUR_BOM_Line.ChargeAmt; -- Create New Lines FOR CUR_BOM IN (SELECT b.M_ProductBOM_ID, p.C_UOM_ID, b.BOMQty, b.Description FROM M_PRODUCT_BOM b, M_PRODUCT p WHERE b.M_Product_ID=CUR_BOM_Line.M_Product_ID AND b.M_ProductBOM_ID=p.M_Product_ID ORDER BY Line ) LOOP SELECT * INTO v_NextNo FROM Ad_Sequence_Next('C_InvoiceLine', CUR_BOM_Line.AD_Client_ID) ; v_Line:=v_Line + 10; -- v_Offer_ID := M_GET_OFFER(v_DateInvoiced, v_BPartner_ID, CUR_BOM.M_ProductBOM_ID); /* INSERT INTO C_INVOICELINE (C_InvoiceLine_ID, AD_Client_ID,AD_Org_ID,IsActive,Created,CreatedBy,Updated,UpdatedBy, C_Invoice_ID,Line, Description, M_Product_ID,C_UOM_ID, QtyInvoiced, PriceList,PriceActual,PriceLimit,LineNetAmt, C_Charge_ID,ChargeAmt, C_Tax_ID, PriceStd) VALUES (v_NextNo, CUR_BOM_Line.AD_Client_ID,CUR_BOM_Line.AD_Org_ID,CUR_BOM_Line.IsActive,TO_DATE(NOW()),v_UpdatedBy,TO_DATE(NOW()),v_UpdatedBy, CUR_BOM_Line.C_Invoice_ID,v_Line, CUR_BOM.Description, CUR_BOM.M_ProductBOM_ID, CUR_BOM.C_UOM_ID, CUR_BOM_Line.QtyInvoiced*CUR_BOM.BOMQty, Bom_Pricelist(CUR_BOM.M_ProductBOM_ID, PriceList_Version_ID),M_GET_OFFER_PRICE(v_Offer_ID, Bom_Pricestd(CUR_BOM.M_ProductBOM_ID, PriceList_Version_ID)td), Bom_Pricelimit(CUR_BOM.M_ProductBOM_ID, PriceList_Version_ID), M_GET_OFFER_PRICE(v_Offer_ID, Bom_Pricestd(CUR_BOM.M_ProductBOM_ID, PriceList_Version_ID)) * CUR_BOM_Line.QtyInvoiced*CUR_BOM.BOMQty, CUR_BOM_Line.C_Charge_ID,v_ChargeAmt, CUR_BOM_Line.C_Tax_ID); */ -- Bom_Pricestd(CUR_BOM.M_ProductBOM_ID, PriceList_Version_ID), v_Offer_ID); -- One Time variables INSERT INTO C_INVOICELINE ( C_InvoiceLine_ID, AD_Client_ID, AD_Org_ID, IsActive, Created, CreatedBy, Updated, UpdatedBy, C_Invoice_ID, Line, Description, M_Product_ID, C_UOM_ID, QtyInvoiced, PriceList, PriceActual, PriceLimit, LineNetAmt, C_Charge_ID, ChargeAmt, C_Tax_ID, PriceStd ) VALUES ( v_NextNo, CUR_BOM_Line.AD_Client_ID, CUR_BOM_Line.AD_Org_ID, CUR_BOM_Line.IsActive, TO_DATE(NOW()), v_UpdatedBy, TO_DATE(NOW()), v_UpdatedBy, CUR_BOM_Line.C_Invoice_ID, v_Line, CUR_BOM.Description, CUR_BOM.M_ProductBOM_ID, CUR_BOM.C_UOM_ID, CUR_BOM_Line.QtyInvoiced*CUR_BOM.BOMQty, Bom_Pricelist(CUR_BOM.M_ProductBOM_ID, v_PriceList_Version_ID), M_Get_Offers_Price(v_DateInvoiced, v_BPartner_ID, CUR_BOM.M_ProductBOM_ID, Bom_Pricestd(CUR_BOM.M_ProductBOM_ID, v_PriceList_Version_ID),CUR_BOM_Line.QtyInvoiced*CUR_BOM.BOMQty, v_PriceList_ID), Bom_Pricelimit(CUR_BOM.M_ProductBOM_ID, v_PriceList_Version_ID), M_Get_Offers_Price(v_DateInvoiced, v_BPartner_ID, CUR_BOM.M_ProductBOM_ID, Bom_Pricestd(CUR_BOM.M_ProductBOM_ID, v_PriceList_Version_ID),CUR_BOM_Line.QtyInvoiced*CUR_BOM.BOMQty, v_PriceList_ID) * CUR_BOM_Line.QtyInvoiced*CUR_BOM.BOMQty, CUR_BOM_Line.C_Charge_ID, v_ChargeAmt, CUR_BOM_Line.C_Tax_ID, Bom_Pricestd(CUR_BOM.M_ProductBOM_ID, v_PriceList_Version_ID) ) ; v_ChargeAmt:=0; END LOOP; -- Create New Lines -- Convert into Comment Line UPDATE C_INVOICELINE SET M_Product_ID=NULL, PriceList=0, PriceActual=0, PriceLimit=0, LineNetAmt=0, ChargeAmt=0, Description= (SELECT p.NAME || ' ' || C_INVOICELINE.Description FROM M_PRODUCT p WHERE p.M_Product_ID=CUR_BOM_Line.M_Product_ID ) , PriceStd=0, M_Offer_ID=NULL WHERE C_InvoiceLine_ID=CUR_BOM_Line.C_InvoiceLine_ID; END LOOP; -- Replace Lines END LOOP; -- BOM Loop END; END IF;--FINISH_PROCESS IF(NOT FINISH_PROCESS AND NOT END_PROCESSING) THEN /************************************************************************** * Calculate Discounts *************************************************************************/ v_CumDiscount:=0; v_OldCumDiscount:=0; v_Line:=10; SELECT MAX(LINE) +10 INTO v_InvoiceLineSeqNo FROM C_INVOICELINE WHERE C_INVOICE_ID=v_Record_ID; FOR Cur_CInvoiceDiscount IN (SELECT C_INVOICE_DISCOUNT.C_INVOICE_DISCOUNT_ID, C_DISCOUNT.DISCOUNT, C_DISCOUNT.M_PRODUCT_ID, C_DISCOUNT.NAME, C_INVOICE_DISCOUNT.CASCADE, C_DISCOUNT.C_DISCOUNT_ID, M_PRODUCT.C_UOM_ID FROM C_INVOICE_DISCOUNT, C_DISCOUNT, M_PRODUCT WHERE C_INVOICE_DISCOUNT.C_DISCOUNT_ID=C_DISCOUNT.C_DISCOUNT_ID AND C_DISCOUNT.M_PRODUCT_ID=M_PRODUCT.M_PRODUCT_ID AND C_INVOICE_DISCOUNT.C_INVOICE_ID=v_Record_ID AND C_INVOICE_DISCOUNT.ISACTIVE='Y' ORDER BY C_INVOICE_DISCOUNT.LINE ) LOOP v_CumDiscount:=(1-v_OldCumDiscount) * Cur_CInvoiceDiscount.Discount/100; v_OldCumDiscount:=v_OldCumDiscount + v_CumDiscount; FOR Cur_TaxDiscount IN (SELECT C_INVOICELINE.C_TAX_ID, SUM(C_INVOICELINE.LINENETAMT) AS LINENETAMT FROM C_INVOICELINE WHERE C_INVOICE_ID=v_Record_ID AND C_INVOICELINE.LINENETAMT<>0 AND C_INVOICE_DISCOUNT_ID IS NULL GROUP BY C_TAX_ID ) LOOP IF(Cur_CInvoiceDiscount.CASCADE='Y') THEN v_Discount:=(-1) * Cur_TaxDiscount.LINENETAMT * v_CumDiscount; ELSE v_Discount:=(-1) * Cur_TaxDiscount.LINENETAMT * Cur_CInvoiceDiscount.Discount/100; END IF; v_InvoiceLineSeqNo:=10 + v_InvoiceLineSeqNo; v_InvoiceLine:=Ad_Sequence_Nextno('C_InvoiceLine') ; INSERT INTO C_INVOICELINE ( C_INVOICELINE_ID, AD_CLIENT_ID, AD_ORG_ID, ISACTIVE, CREATED, CREATEDBY, UPDATED, UPDATEDBY, C_INVOICE_ID, C_ORDERLINE_ID, M_INOUTLINE_ID, LINE, DESCRIPTION, M_PRODUCT_ID, QTYINVOICED, PRICELIST, PRICEACTUAL, PRICELIMIT, LINENETAMT, C_CHARGE_ID, CHARGEAMT, C_UOM_ID, C_TAX_ID, S_RESOURCEASSIGNMENT_ID, A_ASSET_ID, TAXAMT, M_ATTRIBUTESETINSTANCE_ID, ISDESCRIPTION, QUANTITYORDER, M_PRODUCT_UOM_ID, PRICESTD ) VALUES ( v_InvoiceLine, v_Client_ID, v_Org_ID, 'Y', TO_DATE(NOW()), v_UpdatedBy, TO_DATE(NOW()), v_UpdatedBy, v_Record_ID, NULL, NULL, v_InvoiceLineSeqNo, Cur_CInvoiceDiscount.NAME, Cur_CInvoiceDiscount.M_PRODUCT_ID, 1, v_Discount, v_Discount, v_Discount, v_Discount, NULL, 0, Cur_CInvoiceDiscount.C_UOM_ID, Cur_TaxDiscount.C_TAX_ID, NULL, NULL, NULL, NULL, 'N' , NULL, NULL, v_Discount ) ; --v_InvoiceDiscount:=Ad_Sequence_Nextno('C_InvoiceLine'); UPDATE C_INVOICELINE SET C_INVOICE_DISCOUNT_ID=Cur_CInvoiceDiscount.C_INVOICE_DISCOUNT_ID WHERE C_INVOICELINE_ID=v_InvoiceLine; END LOOP; v_Line:=v_Line + 10; END LOOP; END IF;--FINISH_PROCESS IF(NOT FINISH_PROCESS AND NOT END_PROCESSING) THEN /************************************************************************** * Calculate Invoice Taxes and Totals *************************************************************************/ DECLARE Cur_Tax RECORD; Cur_MultiTax RECORD; xTaxBaseAmt NUMERIC:=0; xTaxAmt NUMERIC:=0; v_TaxNoRecalculable NUMERIC:=0; HeaderNotAdded BOOLEAN:=TRUE; v_LineNo NUMERIC:=0; v_ActualBaseAmt NUMERIC; v_FirstLine BOOLEAN; v_C_InvoiceTax_ID NUMERIC(10); BEGIN v_ResultStr:='DeleteOldTaxes'; DELETE FROM C_INVOICETAX WHERE C_Invoice_ID=v_Record_ID AND Recalculate = 'Y'; -- For all Tax Rates v_ResultStr:='InsertNewTaxes'; FOR Cur_Tax IN (SELECT l.C_Tax_ID, i.IsTaxIncluded, COALESCE(SUM(l.LineNetAmt), 0) + COALESCE(SUM(l.ChargeAmt), 0) AS LineNet, COALESCE(SUM(i.ChargeAmt), 0) AS HeaderNet, t.Rate, t.IsSummary, c.StdPrecision FROM C_INVOICE i, C_INVOICELINE l, C_TAX t, C_CURRENCY c WHERE i.C_Invoice_ID=l.C_Invoice_ID AND i.C_Invoice_ID=v_Record_ID -- Parameter AND l.C_Tax_ID=t.C_Tax_ID AND i.C_Currency_ID=c.C_Currency_ID GROUP BY l.C_Tax_ID, i.IsTaxIncluded, t.Rate, t.IsSummary, c.StdPrecision ORDER BY 3 DESC ) LOOP xTaxBaseAmt:=Cur_Tax.LineNet; v_TotalLines:=v_TotalLines + xTaxBaseAmt; -- w/o Header Freight/Charge IF(HeaderNotAdded) THEN -- add header to first HeaderNotAdded:=FALSE; xTaxBaseAmt:=xTaxBaseAmt + Cur_Tax.HeaderNet; END IF; IF(Cur_Tax.IsSummary='N') THEN v_LineNo := v_LineNo + 10; xTaxAmt:=ROUND(xTaxBaseAmt * Cur_Tax.Rate / 100, Cur_Tax.StdPrecision) ; v_GrandTotal:=v_GrandTotal + xTaxBaseAmt + xTaxAmt; -- SELECT * INTO v_C_InvoiceTax_ID FROM Ad_Sequence_Next('C_InvoiceTax', v_Org_ID); INSERT INTO C_INVOICETAX ( C_InvoiceTax_ID, C_Invoice_ID, C_Tax_ID, AD_Client_ID, AD_Org_ID, IsActive, Created, CreatedBy, Updated, UpdatedBy, TaxBaseAmt, TaxAmt, Line, Recalculate ) VALUES ( v_C_InvoiceTax_ID, v_Record_ID, Cur_Tax.C_Tax_ID, v_Client_ID, v_Org_ID, 'Y', TO_DATE(NOW()), v_UpdatedBy, TO_DATE(NOW()), v_UpdatedBy, xTaxBaseAmt, xTaxAmt, v_LineNo,'Y' ) ; ELSE -- Multiple Taxes v_GrandTotal:=v_GrandTotal + xTaxBaseAmt; v_FirstLine := TRUE; FOR Cur_MultiTax IN (SELECT C_Tax_ID, Rate, CASCADE FROM C_TAX WHERE Parent_Tax_ID=Cur_Tax.C_Tax_ID ORDER BY Line) LOOP v_LineNo := v_LineNo + 10; -- scf IF ((Cur_MultiTax.CASCADE = 'Y') AND (NOT v_FirstLine)) THEN --cannot apply cascade to the first line -- scf v_ActualBaseAmt := v_GrandTotal; -- scfELSE v_ActualBaseAmt := xTaxBaseAmt; -- scf END IF; v_FirstLine := FALSE; xTaxAmt:=ROUND(v_ActualBaseAmt * Cur_MultiTax.Rate / 100, Cur_Tax.StdPrecision) ; v_GrandTotal:=v_GrandTotal + xTaxAmt; IF (Cur_MultiTax.CASCADE = 'N') THEN -- if scf xTaxBaseAmt := xTaxBaseAmt + xTaxAmt; END IF; -- SELECT * INTO v_C_InvoiceTax_ID FROM Ad_Sequence_Next('C_InvoiceTax', v_Org_ID); INSERT INTO C_INVOICETAX ( C_InvoiceTax_ID, C_Invoice_ID, C_Tax_ID, AD_Client_ID, AD_Org_ID, IsActive, Created, CreatedBy, Updated, UpdatedBy, TaxBaseAmt, TaxAmt, Line, Recalculate ) VALUES ( v_C_InvoiceTax_ID, v_Record_ID, Cur_MultiTax.C_Tax_ID, v_Client_ID, v_Org_ID, 'Y', TO_DATE(NOW()), v_UpdatedBy, TO_DATE(NOW()), v_UpdatedBy, v_ActualBaseAmt, xTaxAmt, v_LineNo, 'Y' ) ; END LOOP; END IF; END LOOP; -- Insert New Taxes -- Update Header SELECT COALESCE(SUM(TaxAmt),0) INTO v_TaxNoRecalculable FROM C_INVOICETAX WHERE C_Invoice_ID = v_Record_ID AND Recalculate = 'N'; v_GrandTotal:=C_Currency_Round(v_GrandTotal+v_TaxNoRecalculable, v_Currency_ID, NULL) ; UPDATE C_INVOICE SET TotalLines=v_TotalLines, GrandTotal=v_GrandTotal WHERE C_Invoice_ID=v_Record_ID; RAISE NOTICE '%','GrandTotal=' || v_GrandTotal; END; -- Calculate Tax and Totals -- Synchronize Client/Org Ownership v_ResultStr:='SyncOwnershipClient'; UPDATE C_INVOICELINE SET AD_Client_ID=v_Client_ID WHERE C_Invoice_ID=v_Record_ID AND AD_Client_ID<>v_Client_ID; v_ResultStr:='SyncOwnershipOrg'; UPDATE C_INVOICELINE SET AD_Org_ID=v_Org_ID WHERE C_Invoice_ID=v_Record_ID AND C_Charge_ID IS NULL -- sync for products AND AD_Org_ID<>v_Org_ID; END IF;--FINISH_PROCESS IF(NOT FINISH_PROCESS AND NOT END_PROCESSING) THEN /************************************************************************** * Is Approved:3 *************************************************************************/ RAISE NOTICE '%','Approval - IsApproved=' || v_IsApproved; IF(v_IsApproved='N') THEN -- Check if it needs to be approved v_ResultStr:='TestApproval DocType_ID=' || v_DocType_ID; SELECT IsApproved INTO v_IsApproved FROM C_DOCTYPE WHERE C_DocType_ID=v_DocType_ID; RAISE NOTICE '%','IsApproved=' || v_IsApproved; IF(v_IsApproved<>'N') THEN -- Approval needed IF(v_DocAction<>'AP') THEN UPDATE C_INVOICE SET DocStatus='NA' WHERE C_Invoice_ID=v_Record_ID; v_Message:='NotApproved'; RAISE NOTICE '%','Approval needed'; END_PROCESSING:=TRUE; END IF; ELSE UPDATE C_INVOICE SET IsApproved='Y' WHERE C_Invoice_ID=v_Record_ID; END IF; END IF; END IF;--FINISH_PROCESS IF(NOT FINISH_PROCESS AND NOT END_PROCESSING) THEN /************************************************************************** * Update BP Statistics *************************************************************************/ v_ResultStr:='Updating BPartners'; -- First Sale UPDATE C_BPARTNER SET FirstSale=v_DateAcct WHERE C_BPartner_ID=v_BPartner_ID AND FirstSale IS NULL; -- Last Contact, Result UPDATE AD_USER SET LastContact=TO_DATE(NOW()), LastResult=v_DocumentNo WHERE AD_User_ID=v_BPartner_User_ID; -- Update total revenue and credit limit -- It is reversed in C_Allocation_Trg IF(v_IsSOTrx='Y') THEN UPDATE C_BPARTNER SET ActualLifeTimeValue=ActualLifeTimeValue +(v_Multiplier * C_Base_Convert(v_GrandTotal, v_Currency_ID, v_Client_ID, v_DateAcct, v_Org_ID)) WHERE C_BPartner_ID=v_BPartner_ID; END IF; END IF;--FINISH_PROCESS IF(NOT FINISH_PROCESS AND NOT END_PROCESSING) THEN /************************************************************************** * Matching *************************************************************************/ v_ResultStr:='Matching'; IF(v_IsSOTrx='N') THEN DECLARE -- Invoice-Receipt Match Cur_ILines_Receipt RECORD; -- Invoice-PO Match Cur_ILines_PO RECORD; v_Qty NUMERIC; v_MatchInv_ID NUMERIC(10) ; v_MatchPO_ID NUMERIC(10) ; BEGIN v_ResultStr:='MatchInv-Receipt'; FOR Cur_ILines_Receipt IN (SELECT il.AD_Client_ID, il.AD_Org_ID, il.C_InvoiceLine_ID, ml.M_InOutLine_ID, ml.M_Product_ID, ml.MovementQty, il.QtyInvoiced, i.DateAcct FROM C_INVOICELINE il INNER JOIN M_INOUTLINE ml ON(il.M_InOutLine_ID=ml.M_InOutLine_ID) INNER JOIN C_INVOICE i ON(il.C_Invoice_ID=i.C_Invoice_ID) WHERE il.M_Product_ID=ml.M_Product_ID AND il.C_Invoice_ID=v_Record_ID ) LOOP -- The min qty. Modified by Ismael Ciordia --v_Qty := Cur_ILines_Receipt.MovementQty; --IF (ABS(Cur_ILines_Receipt.MovementQty) > ABS(Cur_ILines_Receipt.QtyInvoiced)) THEN v_Qty:=Cur_ILines_Receipt.QtyInvoiced; --END IF; SELECT * INTO v_MatchInv_ID FROM Ad_Sequence_Next('M_MatchInv', Cur_ILines_Receipt.AD_Org_ID) ; v_ResultStr:='InsertMatchInv ' || v_MatchInv_ID; RAISE NOTICE '%',' M_MatchInv_ID=' || v_MatchInv_ID || ' - ' || v_Qty; INSERT INTO M_MATCHINV ( M_MatchInv_ID, AD_Client_ID, AD_Org_ID, IsActive, Created, CreatedBy, Updated, UpdatedBy, M_InOutLine_ID, C_InvoiceLine_ID, M_Product_ID, DateTrx, Qty, Processing, Processed, Posted ) VALUES ( v_MatchInv_ID, Cur_ILines_Receipt.AD_Client_ID, Cur_ILines_Receipt.AD_Org_ID, 'Y', TO_DATE(NOW()), 0, TO_DATE(NOW()), 0, Cur_ILines_Receipt.M_InOutLine_ID, Cur_ILines_Receipt.C_InvoiceLine_ID, Cur_ILines_Receipt.M_Product_ID, Cur_ILines_Receipt.DateAcct, v_Qty, 'N', 'Y', 'N' ) ; END LOOP; v_ResultStr:='MatchInv-PO'; FOR Cur_ILines_PO IN (SELECT il.AD_Client_ID, il.AD_Org_ID, il.C_InvoiceLine_ID, ol.C_OrderLine_ID, ol.M_Product_ID, ol.C_Charge_ID, ol.QtyOrdered, il.QtyInvoiced, i.DateAcct FROM C_INVOICELINE il INNER JOIN C_ORDERLINE ol ON(il.C_OrderLine_ID=ol.C_OrderLine_ID) INNER JOIN C_INVOICE i ON(il.C_Invoice_ID=i.C_Invoice_ID) WHERE(il.M_Product_ID=ol.M_Product_ID OR il.C_Charge_ID=ol.C_Charge_ID) AND il.C_Invoice_ID=v_Record_ID ) LOOP -- The min qty. Modified by Ismael Ciordia --v_Qty := Cur_ILines_PO.QtyOrdered; --IF (ABS(Cur_ILines_PO.QtyOrdered) > ABS(Cur_ILines_PO.QtyInvoiced)) THEN v_Qty:=Cur_ILines_PO.QtyInvoiced; --END IF; SELECT * INTO v_MatchPO_ID FROM Ad_Sequence_Next('M_MatchPO', Cur_ILines_PO.AD_Org_ID) ; v_ResultStr:='InsertMatchPO ' || v_MatchPO_ID; RAISE NOTICE '%',' M_MatchPO_ID=' || v_MatchPO_ID || ' - ' || v_Qty; INSERT INTO M_MATCHPO ( M_MatchPO_ID, AD_Client_ID, AD_Org_ID, IsActive, Created, CreatedBy, Updated, UpdatedBy, C_OrderLine_ID, M_InOutLine_ID, C_InvoiceLine_ID, M_Product_ID, DateTrx, Qty, Processing, Processed, Posted ) VALUES ( v_MatchPO_ID, Cur_ILines_PO.AD_Client_ID, Cur_ILines_PO.AD_Org_ID, 'Y', TO_DATE(NOW()), 0, TO_DATE(NOW()), 0, Cur_ILines_PO.C_OrderLine_ID, NULL, Cur_ILines_PO.C_InvoiceLine_ID, Cur_ILines_PO.M_Product_ID, Cur_ILines_PO.DateAcct, v_Qty, 'N', 'Y', 'N' ) ; END LOOP; END; ELSE -- Actualiza las cantidades facturadas de los pedidos de venta, y las lineas de albaryn facturadas DECLARE CurLines RECORD; p_DateInvoiced TIMESTAMP; v_totalQtyInvoiced NUMERIC; v_deliveredQty NUMERIC; v_inOutStatus CHAR(2) ; BEGIN SELECT DateInvoiced INTO p_DateInvoiced FROM C_INVOICE WHERE C_Invoice_ID=v_Record_ID; FOR CurLines IN (SELECT * FROM C_INVOICELINE WHERE C_INVOICE_ID=v_Record_ID ORDER BY line) LOOP IF(CurLines.C_OrderLine_ID IS NOT NULL) THEN UPDATE C_ORDERLINE SET QtyInvoiced=QtyInvoiced + CurLines.QtyInvoiced, DateInvoiced=p_DateInvoiced, Updated=TO_DATE(NOW()) WHERE C_OrderLine_ID=CurLines.C_OrderLine_ID; END IF; IF(CurLines.M_InOutLine_ID IS NOT NULL) THEN SELECT m.DOCSTATUS INTO v_inOutStatus FROM M_INOUT m, M_INOUTLINE ml WHERE M.M_InOut_ID=ml.M_InOut_ID AND ml.M_InOutLine_ID=CurLines.M_InOutLine_ID; IF(v_inOutStatus<>'RE') THEN SELECT COALESCE(SUM(C_INVOICELINE.QTYINVOICED), 0) INTO v_totalQtyInvoiced FROM C_INVOICELINE, C_INVOICE WHERE C_INVOICE.C_Invoice_ID=C_INVOICELINE.C_Invoice_ID AND C_INVOICE.Processed='Y' AND C_INVOICELINE.M_InOutLine_ID=CurLines.M_InOutLine_ID; v_totalQtyInvoiced:=v_totalQtyInvoiced + CurLines.QtyInvoiced; SELECT MovementQty INTO v_deliveredQty FROM M_INOUTLINE WHERE M_InOutLine_ID=CurLines.M_InOutLine_ID; UPDATE M_INOUTLINE SET IsInvoiced=( CASE v_totalQtyInvoiced WHEN 0 THEN 'N' ELSE 'Y' END ) WHERE M_InOutLine_ID=CurLines.M_InOutLine_ID; END IF; END IF; END LOOP; END; END IF; END IF;--FINISH_PROCESS IF(NOT FINISH_PROCESS AND NOT END_PROCESSING) THEN -- Modified by Ismael Ciordia -- Generate C_Debt_Payment linked to this invoice DECLARE v_totalCash NUMERIC:=0; v_processed CHAR(1):='N'; v_debtPaymentID NUMERIC(10) ; v_amount NUMERIC; v_cashBook NUMERIC(10) ; v_bankAccount NUMERIC(10) ; v_cash NUMERIC(10) ; v_IsoCode C_CURRENCY.ISO_CODE%TYPE; v_CashLine NUMERIC(10) ; v_line NUMERIC(10) ; v_BPartnerName C_BPARTNER.NAME%TYPE; v_GenDP_Org NUMERIC(10); BEGIN v_ResultStr:='Generating C_Debt_Payment'; UPDATE C_DEBT_PAYMENT SET C_INVOICE_ID=v_Record_ID WHERE EXISTS (SELECT 1 FROM C_ORDERLINE ol, C_INVOICELINE il WHERE ol.C_ORDERLINE_ID=il.C_ORDERLINE_ID AND il.C_INVOICE_ID=v_Record_ID AND ol.C_ORDER_ID=C_DEBT_PAYMENT.C_ORDER_ID ) AND C_INVOICE_ID IS NULL; UPDATE C_DEBT_PAYMENT SET IsValid='Y' WHERE C_INVOICE_ID=v_Record_ID AND IsValid!='Y'; -- Is it a Credit Memo:4 SELECT DocBaseType INTO v_TargetDocBaseType FROM C_DOCTYPE WHERE C_DocType_ID=v_DocTypeTarget_ID; IF v_TargetDocBaseType in ('ARC','APC') THEN v_MultiplierARC:=-1; END IF; --Sums debt payments from the order and the ones that have been inserted manually, added by ALO SELECT COALESCE(SUM(C_Currency_Round(C_Currency_Convert((Amount + WriteOffAmt)*v_MultiplierARC, C_Currency_ID, v_Currency_ID, v_DateInvoiced, NULL, v_Client_ID, v_Org_ID), v_Currency_ID, NULL)), 0) INTO v_totalCash FROM C_DEBT_PAYMENT_V dp WHERE C_INVOICE_ID=v_Record_ID; --Insert C_Debt_Payment if GrandTotal - v_totalCash <> 0; IF(v_GrandTotal<>v_totalCash) THEN DECLARE CUR_PAYMENTS RECORD; v_plannedDate TIMESTAMP; v_pendingAmount NUMERIC; v_paymentAmount NUMERIC; v_GenDebt_PaymentID NUMERIC; v_SettlementDocTypeID NUMERIC(10) ; v_settlement_ID NUMERIC(10) ; v_CB_Curr NUMERIC; v_SDocument_No C_SETTLEMENT.DocumentNo%TYPE; BEGIN IF v_IsSOTrx ='Y' THEN v_pendingAmount:=v_GrandTotal - v_totalCash; ELSE v_pendingAmount:=v_GrandTotal + v_totalCash; END IF; FOR CUR_PAYMENTS IN (SELECT LINE, PERCENTAGE, ONREMAINDER, EXCLUDETAX, COALESCE(PAYMENTRULE, v_PaymentRule) AS PAYMENTRULE, FIXMONTHDAY, FIXMONTHDAY2, FIXMONTHDAY3, NETDAYS, FIXMONTHOFFSET, NETDAY, ISNEXTBUSINESSDAY FROM C_PAYMENTTERMLINE WHERE C_PAYMENTTERM_ID=v_PaymentTerm UNION -- Header of paymentTerm is processed at last SELECT 9999 AS LINE, 100 AS PERCENTAGE, 'Y' AS ONREMAINDER, 'N' AS EXCLUDETAX, v_PaymentRule AS PAYMENTRULE, FIXMONTHDAY, FIXMONTHDAY2, FIXMONTHDAY3, NETDAYS, FIXMONTHOFFSET, NETDAY, ISNEXTBUSINESSDAY FROM C_PAYMENTTERM WHERE C_PAYMENTTERM_ID=v_PaymentTerm ORDER BY LINE ) LOOP IF(CUR_PAYMENTS.PaymentRule IN('B', 'C')) THEN SELECT MAX(C_CashBook_ID) INTO v_cashBook FROM C_CASHBOOK WHERE AD_Client_ID=v_Client_ID AND isDefault='Y'; IF v_cashBook IS NULL THEN RAISE EXCEPTION '%', '@NoDefaultCashBook@'; END IF; v_bankAccount:=NULL; ELSE SELECT COALESCE(( CASE v_IsSOTrx WHEN 'Y' THEN SO_BankAccount_ID ELSE PO_BankAccount_ID END ), (SELECT MAX(C_BankAccount_ID) FROM C_BANKACCOUNT WHERE AD_Client_ID=v_Client_ID AND isDefault='Y' ) ) INTO v_bankAccount FROM C_BPARTNER WHERE c_BPartner_ID=v_BPartner_ID; v_cashBook:=NULL; END IF; v_plannedDate:=C_Paymentduedate(v_BPartner_ID, v_IsSOTrx, CUR_PAYMENTS.FixMonthDay, CUR_PAYMENTS.FixMonthDay2, CUR_PAYMENTS.FixMonthDay3, CUR_PAYMENTS.NetDays, CUR_PAYMENTS.FixMonthOffset, CUR_PAYMENTS.NetDay, CUR_PAYMENTS.IsNextbusinessday, v_DateInvoiced) ; IF(CUR_PAYMENTS.EXCLUDETAX='Y') THEN -- if excludeTax = 'Y', percentage is aplied on the TotalLines v_paymentAmount:=C_Currency_Round((v_TotalLines) *CUR_PAYMENTS.PERCENTAGE/100, v_Currency_ID, NULL) ; ELSIF(CUR_PAYMENTS.ONREMAINDER='N') THEN -- if onRemainder = 'N', percentage is aplied on the GrandTotal v_paymentAmount:=C_Currency_Round((v_GrandTotal) *CUR_PAYMENTS.PERCENTAGE/100, v_Currency_ID, NULL) ; ELSE v_paymentAmount:=C_Currency_Round((v_pendingAmount) *CUR_PAYMENTS.PERCENTAGE/100, v_Currency_ID, NULL) ; END IF; v_pendingAmount:=v_pendingAmount - v_paymentAmount; SELECT * INTO v_debtPaymentID FROM Ad_Sequence_Next('C_Debt_Payment', v_Record_ID) ; INSERT INTO C_DEBT_PAYMENT ( C_DEBT_PAYMENT_ID, AD_CLIENT_ID, AD_ORG_ID, ISACTIVE, CREATED, CREATEDBY, UPDATED, UPDATEDBY, ISRECEIPT, C_SETTLEMENT_CANCEL_ID, C_SETTLEMENT_GENERATE_ID, DESCRIPTION, C_INVOICE_ID, C_BPARTNER_ID, C_CURRENCY_ID, C_CASHLINE_ID, C_BANKACCOUNT_ID, C_CASHBOOK_ID, PAYMENTRULE, ISPAID, AMOUNT, WRITEOFFAMT, DATEPLANNED, ISMANUAL, ISVALID, C_BANKSTATEMENTLINE_ID, CHANGESETTLEMENTCANCEL, CANCEL_PROCESSED, GENERATE_PROCESSED, c_project_id, status, status_initial ) VALUES ( v_debtPaymentID, v_Client_ID, v_Org_ID, 'Y', TO_DATE(NOW()), v_UpdatedBy, TO_DATE(NOW()), v_UpdatedBy, v_IsSOTrx, NULL, NULL, '* ' || v_DocumentNo || ' * (' || v_BPartnerName ||( CASE WHEN v_POReference IS NULL THEN '' ELSE ' .Ref:'||TO_CHAR(v_POReference) END) || ' )', v_Record_ID, v_BPartner_ID, v_Currency_ID, NULL, v_bankAccount, v_cashBook, CUR_PAYMENTS.PaymentRule, 'N', v_paymentAmount*v_MultiplierARC, 0, v_plannedDate, 'N', 'Y', NULL, 'N', 'N', 'N', v_C_Project_Id, 'DE', 'DE' ) ; --AL --Looking for autogenerated debt-payments SELECT MAX(c_Debt_Payment_Id), MAX(ad_Org_ID) INTO v_GenDebt_PaymentID, v_GenDP_Org FROM C_DEBT_PAYMENT DP WHERE C_BPartner_ID=v_BPartner_ID AND C_Debt_Payment_Status(C_Settlement_Cancel_ID, Cancel_Processed, Generate_Processed, IsPaid, IsValid, C_Cashline_ID, C_BankstatementLine_ID)='P' AND(-1) *Amount=v_paymentAmount AND c_currency_ID=v_Currency_ID AND C_SETTLEMENT_GENERATE_ID IS NOT NULL AND Ad_Isorgincluded(v_ad_Org_id, dp.ad_org_id,v_Client_ID) != -1 AND ad_client_id = v_Client_ID AND EXISTS (SELECT 1 FROM C_SETTLEMENT S WHERE DP.C_SETTLEMENT_GENERATE_ID = S.C_Settlement_ID AND IsGenerated = 'Y') AND NOT EXISTS (SELECT 1 FROM C_REMITTANCELINE rl WHERE rl.C_DEBT_PAYMENT_CANCELLED = DP.C_DEBT_PAYMENT_ID); IF v_GenDebt_PaymentID IS NOT NULL THEN v_SettlementDocTypeID:=Ad_Get_Doctype(v_Client_ID, v_GenDP_Org, TO_CHAR('STT')) ; SELECT * INTO v_settlement_ID FROM Ad_Sequence_Next('C_Settlement', v_Record_ID) ; SELECT * INTO v_SDocument_No FROM Ad_Sequence_Doctype(v_SettlementDocTypeID, v_Record_ID, 'Y') ; IF(v_SDocument_No IS NULL) THEN SELECT * INTO v_SDocument_No FROM Ad_Sequence_Doc('DocumentNo_C_Settlement', v_Client_ID, 'Y') ; END IF; INSERT INTO C_SETTLEMENT ( C_SETTLEMENT_ID, AD_CLIENT_ID, AD_ORG_ID, ISACTIVE, CREATED, CREATEDBY, UPDATED, UPDATEDBY, DOCUMENTNO, DATETRX, DATEACCT, SETTLEMENTTYPE, C_DOCTYPE_ID, PROCESSING, PROCESSED, POSTED, C_CURRENCY_ID, ISGENERATED ) /*, C_PROJECT_ID, C_CAMPAIGN_ID, C_ACTIVITY_ID, USER1_ID, USER2_ID, CREATEFROM)*/ VALUES ( v_Settlement_ID, v_Client_ID, v_GenDP_Org, 'Y', TO_DATE(NOW()), v_UpdatedBy, TO_DATE(NOW()), v_UpdatedBy, '*C*'||v_SDocument_No, TRUNC(TO_DATE(NOW())), TRUNC(TO_DATE(NOW())), 'C', v_SettlementDocTypeID, 'N', 'N', 'N', v_Currency_ID, 'Y' ) ; UPDATE C_DEBT_PAYMENT SET C_Settlement_Cancel_Id=v_Settlement_ID, Updated=TO_DATE(NOW()), UpdatedBy=v_UpdatedBy WHERE c_Debt_Payment_ID IN(v_genDebt_PaymentID, v_debtPaymentID) ; PERFORM C_SETTLEMENT_POST(NULL, v_Settlement_ID) ; END IF; --If Invoice.paymentRule = 'B', insert de cashline de tipo efecto apuntando al efecto IF(v_cashBook IS NOT NULL AND CUR_PAYMENTS.PaymentRule='B') THEN SELECT MAX(C.C_CASH_ID) INTO v_Cash FROM C_CASH C WHERE C.C_CASHBOOK_ID=v_cashBook AND C.DATEACCT=v_DateAcct AND C.PROCESSED='N'; SELECT C_CURRENCY_ID INTO v_CB_Curr FROM C_CASHBOOK WHERE C_CASHBOOK_ID = v_cashBook; IF(v_Cash IS NULL) THEN v_ResultStr:='Creating C_Cash'; SELECT ISO_CODE INTO v_IsoCode FROM C_CURRENCY WHERE C_Currency_ID=v_CB_Curr; SELECT * INTO v_Cash FROM Ad_Sequence_Next('C_Cash', v_Record_ID) ; INSERT INTO C_CASH ( C_Cash_ID, AD_Client_ID, AD_Org_ID, IsActive, Created, CreatedBy, Updated, UpdatedBy, C_CashBook_ID, NAME, StatementDate, DateAcct, BeginningBalance, EndingBalance, StatementDifference, Processing, Processed, Posted ) VALUES (v_Cash, v_Client_ID, v_Org_ID, 'Y', TO_DATE(NOW()), v_UpdatedBy, TO_DATE(NOW()), v_UpdatedBy, v_cashBook, (TO_CHAR(v_DateAcct, 'YYYY-MM-DD') || ' ' || v_IsoCode), v_DateAcct, v_DateAcct, 0, 0, 0, 'N', 'N', 'N'); END IF; -- v_Cash IS NULL v_ResultStr:='Creating C_CashLine'; SELECT * INTO v_CashLine FROM Ad_Sequence_Next('C_CashLine', v_Record_ID) ; SELECT COALESCE(MAX(LINE), 0) + 10 INTO v_line FROM C_CASHLINE WHERE C_CASH_ID=v_Cash; INSERT INTO C_CASHLINE ( C_CashLine_ID, AD_Client_ID, AD_Org_ID, IsActive, Created, CreatedBy, Updated, UpdatedBy, C_Cash_ID, C_Debt_Payment_ID, Line, Description, Amount, CashType, C_Currency_ID, DiscountAmt, WriteOffAmt, IsGenerated ) VALUES ( v_CashLine, v_Client_ID, v_Org_ID, 'Y', TO_DATE(NOW()), v_UpdatedBy, TO_DATE(NOW()), v_UpdatedBy, v_Cash, v_debtPaymentID, v_line, v_BPartnerName, C_Currency_Convert((CASE v_IsSOTrx WHEN 'Y' THEN v_paymentAmount ELSE -v_paymentAmount END), v_Currency_ID, v_CB_Curr,v_DateAcct,NULL,v_Client_ID, v_Org_ID), 'P', v_Currency_ID, 0, 0, 'Y' ) ; END IF; -- v_cashBook IS NOT NULL END LOOP; END; END IF; -- v_GrandTotal <> v_totalCash END; IF(NOT FINISH_PROCESS) THEN PERFORM C_BP_SOCREDITUSED_REFRESH(v_BPartner_ID) ; END IF; END IF;--FINISH_PROCESS IF(NOT FINISH_PROCESS AND NOT END_PROCESSING) THEN -- Finish up ------------------------------------------------------------- UPDATE C_INVOICE SET DocStatus='CO', Processed='Y', DocAction='RE', Updated=TO_DATE(NOW()) WHERE C_Invoice_ID=v_Record_ID; -- Post it IF(v_IsTransferred='N') THEN PERFORM C_INVOICE_ACCTGEN(NULL, v_Record_ID) ; END IF; END IF;--FINISH_PROCESS IF(NOT FINISH_PROCESS) THEN -- End Processing -------------------------------------------------------- ---- <> v_ResultStr:='UnLockingInvoice'; UPDATE C_INVOICE SET Processing='N', Updated=TO_DATE(NOW()), UpdatedBy=v_UpdatedBy WHERE C_Invoice_ID=v_Record_ID; -- Commented by cromero 19102006 IF(p_PInstance_ID IS NOT NULL) THEN -- Commented by cromero 19102006 -- Commented by cromero 19102006 END IF; END IF;--FINISH_PROCESS ---- <> IF(p_PInstance_ID IS NOT NULL) THEN -- Update AD_PInstance RAISE NOTICE '%','Updating PInstance - Finished - ' || v_Message; PERFORM AD_UPDATE_PINSTANCE(p_PInstance_ID, v_UpdatedBy, 'N', v_Result, v_Message) ; ELSE RAISE NOTICE '%','---- <> ' || v_Message; END IF; RETURN; END; --BODY EXCEPTION WHEN OTHERS THEN RAISE NOTICE '%',v_ResultStr; v_ResultStr:= '@ERROR=' || SQLERRM; RAISE NOTICE '%',v_ResultStr; IF(p_PInstance_ID IS NOT NULL) THEN PERFORM AD_UPDATE_PINSTANCE(p_PInstance_ID, NULL, 'N', 0, v_ResultStr) ; ELSE DECLARE v_Code NUMERIC:=SQLSTATE; BEGIN INSERT INTO DBA_ERRORLOG ( DBA_ErrorLog_ID, Created, Code, Msg, Info ) VALUES ( NEXTVAL('DBA_ErrorLog_Seq'), TO_DATE(NOW()), v_Code, v_ResultStr, 'C_Invoice_Post' ) ; END; RAISE EXCEPTION '%', ''; END IF; -- Commented by cromero 19102006 RETURN; END ; $BODY$ LANGUAGE 'plpgsql' VOLATILE; ALTER FUNCTION c_invoice_post(numeric, numeric) OWNER TO tad;