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