• Este debate está vacío.
Viendo 2 entradas - de la 1 a la 2 (de un total de 2)
  • Autor
    Entradas
  • #32891
    Gabriel Bocalandro
    Participante

    Gente, tengo un error y no se como hay que hacer para poder arreglarlo.

    Me podrán dar una mano?

    cuando esta actualizando el componente da error.

    more than one row returned by a subquery used as an expresión…..

    y muestra todo el SQL

    Copio abajo la ventana de DOS donde se queda.

    Muchas Gracias


    v_PaidAmt NUMERIC := 0;
    v_Remaining NUMERIC := 0;
    v_Precision NUMERIC := 0;
    v_Min NUMERIC := 0.01;
    s RECORD;
    v_ConversionType_ID INTEGER;
    BEGIN

    SELECT C_Currency_ID, GrandTotal, C_ConversionType_ID,
    (SELECT StdPrecision FROM C_Currency C WHERE C.C_Currency_ID = I.C_Curre
    ncy_ID)
    AS StdPrecision
    INTO v_Currency_ID, v_TotalOpenAmt, v_ConversionType_ID,v_Precision
    FROM C_Invoice I
    WHERE I.C_Invoice_ID = p_C_Invoice_ID;
    IF NOT FOUND THEN
    RAISE NOTICE ‘Invoice no econtrada – %’, p_C_Invoice_ID;
    RETURN NULL;
    END IF;

    v_PaidAmt := getAllocatedAmt(p_C_Invoice_ID,v_Currency_ID,v_ConversionTy
    pe_ID,1);

    IF (p_C_InvoicePaySchedule_ID > 0) THEN
    v_Remaining := v_PaidAmt;
    FOR s IN
    SELECT C_InvoicePaySchedule_ID, DueAmt, sign(DueAmt) as signo
    FROM C_InvoicePaySchedule
    WHERE C_Invoice_ID = p_C_Invoice_ID
    AND IsValid=’Y’
    ORDER BY DueDate
    LOOP
    IF (s.C_InvoicePaySchedule_ID = p_C_InvoicePaySchedule_ID) THEN
    v_TotalOpenAmt := abs(s.DueAmt) – v_Remaining;
    IF (v_TotalOpenAmt < 0) THEN
    v_TotalOpenAmt := 0;
    END IF;
    v_TotalOpenAmt := s.signo * v_TotalOpenAmt;
    EXIT;
    ELSE
    v_Remaining := v_Remaining – abs(s.DueAmt);
    IF (v_Remaining < 0) THEN
    v_Remaining := 0;
    END IF;
    END IF;
    END LOOP;
    ELSE
    v_TotalOpenAmt := v_TotalOpenAmt – v_PaidAmt;
    END IF;

    IF (v_TotalOpenAmt >= -v_Min AND v_TotalOpenAmt <= v_Min) THEN
    v_TotalOpenAmt := 0;
    END IF;

    v_TotalOpenAmt := ROUND(COALESCE(v_TotalOpenAmt,0), v_Precision);
    RETURN v_TotalOpenAmt;
    END;
    $BODY$
    LANGUAGE plpgsql VOLATILE
    COST 100;
    ALTER FUNCTION invoiceopen(integer, integer)
    OWNER TO libertya;

    CREATE OR REPLACE FUNCTION invoiceopen(p_c_invoice_id integer, p_c_invoicepaysch
    edule_id integer, p_c_currency_id integer, p_c_conversiontype_id integer)
    RETURNS numeric AS
    $BODY$
    /*************************************************************************
    * The contents of this file are subject to the Compiere License. You may
    * obtain a copy of the License at http://www.compiere.org/license.html
    * Software is on an “AS IS” basis, WITHOUT WARRANTY OF ANY KIND, either
    * express or implied. See the License for details. Code: Compiere ERP+CRM
    * Copyright (C) 1999-2001 Jorg Janke, ComPiere, Inc. All Rights Reserved.
    *
    * converted to postgreSQL by Karsten Thiemann (Schaeffer AG),
    * kthiemann@adempiere.org
    *************************************************************************
    ***
    * Title: Calculate Open Item Amount in Invoice Currency
    * Description:
    * Add up total amount open for C_Invoice_ID if no split payment.
    * Grand Total minus Sum of Allocations in Invoice Currency
    *
    * For Split Payments:
    * Allocate Payments starting from first schedule.
    * Cannot be used for IsPaid as mutating
    *
    * Test:
    * SELECT C_InvoicePaySchedule_ID, DueAmt FROM C_InvoicePaySchedule WHERE C
    _Invoice_ID=109 ORDER BY DueDate;
    * SELECT invoiceOpen (109, null) FROM AD_System; – converted to default cl
    ient currency
    * SELECT invoiceOpen (109, 11) FROM AD_System; – converted to default clie
    nt currency
    * SELECT invoiceOpen (109, 102) FROM AD_System;
    * SELECT invoiceOpen (109, 103) FROM AD_System;
    ***
    * Pasado a Libertya a partir de Adempiere 360LTS
    * – ids son de tipo integer, no numeric
    * – TODO : tema de las zonas en los timestamp
    * – Excepciones en SELECT INTO requieren modificador STRICT bajo PostGreSQL o u
    sar
    * NOT FOUND
    * – Por ahora, el “ignore rounding” se hace como en libertya (-0.01,0.01),
    * en vez de usar la precisi├│n de la moneda
    * – Se toma el tipo de conversion de la factura, auqneu esto es dudosamente cor
    recto
    * ya que otras funciones , en particular currencyBase nunca tiene en cuenta
    * este valor
    * – Como en Libertya se tiene en cuenta tambien C_Invoice_Credit_ID para calcul
    ar
    * la cantidad alocada a una factura (aunque esto es medio dudoso….)
    * – No se soporta la fecha como 3er parametro (en realidad, tampoco se esta
    * usando actualmente, y se deberia poder resolver de otra manera)
    * – Libertya parece tener un bug al filtrar por C_InvoicePaySchedule_ID al calc
    ular
    * el granTotal (el granTotal SIEMPRE es el total de la factura, tomada directam
    ente
    * de C_Invoice.GranTotal o a partir de la suma de los DueAmt en C_InvoicePaySch
    edule);
    * se usa la sentencia como esta en Adempeire (esto es, solo se filtra por C_Inv
    oice_ID)
    * – Nuevo enfoque: NO se usa ni la vista C_Invoice_V ni multiplicadores
    * se asume todo positivo…
    * – El resultado SIEMPRE deberia ser positivo y en el intervalo [0..GrandTotal]

    * – 03 julio: se pasa a usar getAllocatedAmt para hacer esta funcion consistent
    e
    * con invoicePaid
    * – 03 julio: se pasa de usar STRICT a NOT FOUND; es mas eficiente
    ************************************************************************/
    DECLARE
    v_Currency_ID INTEGER := p_c_currency_id;
    v_TotalOpenAmt NUMERIC := 0;
    v_PaidAmt NUMERIC := 0;
    v_Remaining NUMERIC := 0;
    v_Precision NUMERIC := 0;
    v_Min NUMERIC := 0.01;
    s RECORD;
    v_ConversionType_ID INTEGER := p_c_conversiontype_id;
    v_Date timestamp with time zone := (‘now’::text)::timestamp(6);

    BEGIN

    SELECT currencyConvert(GrandTotal, I.c_currency_id, v_Currency_ID, v_Da
    te, v_ConversionType_ID, I.AD_Client_ID, I.AD_Org_ID) as GrandTotal,
    (SELECT StdPrecision FROM C_Currency C WHERE C.C_Currency_ID = I.C_Curre
    ncy_ID)
    AS StdPrecision
    INTO v_TotalOpenAmt, v_Precision
    FROM C_Invoice I
    WHERE I.C_Invoice_ID = p_C_Invoice_ID;
    IF NOT FOUND THEN
    RAISE NOTICE ‘Invoice no econtrada – %’, p_C_Invoice_ID;
    RETURN NULL;
    END IF;

    v_PaidAmt := getAllocatedAmt(p_C_Invoice_ID,v_Currency_ID,v_ConversionTy
    pe_ID,1);

    IF (p_C_InvoicePaySchedule_ID > 0) THEN
    v_Remaining := v_PaidAmt;
    FOR s IN
    SELECT ips.C_InvoicePaySchedule_ID, currencyConvert(ips.DueAmt,
    i.c_currency_id, v_Currency_ID, v_Date, v_ConversionType_ID, i.AD_Client_ID, i.
    AD_Org_ID) as DueAmt
    FROM C_InvoicePaySchedule ips
    INNER JOIN C_Invoice i on (ips.C_Invoice_ID = i.C_Invoice_ID)
    WHERE ips.C_Invoice_ID = p_C_Invoice_ID
    AND ips.IsValid=’Y’
    ORDER BY ips.DueDate
    LOOP
    IF (s.C_InvoicePaySchedule_ID = p_C_InvoicePaySchedule_ID) THEN
    v_TotalOpenAmt := s.DueAmt – v_Remaining;
    IF (v_TotalOpenAmt < 0) THEN
    v_TotalOpenAmt := 0;
    END IF;
    EXIT;
    ELSE
    v_Remaining := v_Remaining – s.DueAmt;
    IF (v_Remaining < 0) THEN
    v_Remaining := 0;
    END IF;
    END IF;
    END LOOP;
    ELSE
    v_TotalOpenAmt := v_TotalOpenAmt – v_PaidAmt;
    END IF;

    IF (v_TotalOpenAmt >= -v_Min AND v_TotalOpenAmt <= v_Min) THEN
    v_TotalOpenAmt := 0;
    END IF;

    v_TotalOpenAmt := ROUND(COALESCE(v_TotalOpenAmt,0), v_Precision);
    RETURN v_TotalOpenAmt;
    END;
    $BODY$
    LANGUAGE ‘plpgsql’ VOLATILE
    COST 100;
    ALTER FUNCTION invoiceopen(integer, integer) OWNER TO libertya;
    CREATE OR REPLACE FUNCTION invoiceopen(p_c_invoice_id integer, p_c_invoicepaysch
    edule_id integer)
    RETURNS numeric AS
    $BODY$
    /*************************************************************************
    * The contents of this file are subject to the Compiere License. You may
    * obtain a copy of the License at http://www.compiere.org/license.html
    * Software is on an “AS IS” basis, WITHOUT WARRANTY OF ANY KIND, either
    * express or implied. See the License for details. Code: Compiere ERP+CRM
    * Copyright (C) 1999-2001 Jorg Janke, ComPiere, Inc. All Rights Reserved.
    *
    * converted to postgreSQL by Karsten Thiemann (Schaeffer AG),
    * kthiemann@adempiere.org
    *************************************************************************
    ***
    * Title: Calculate Open Item Amount in Invoice Currency
    * Description:
    * Add up total amount open for C_Invoice_ID if no split payment.
    * Grand Total minus Sum of Allocations in Invoice Currency
    *
    * For Split Payments:
    * Allocate Payments starting from first schedule.
    * Cannot be used for IsPaid as mutating
    *
    * Test:
    * SELECT C_InvoicePaySchedule_ID, DueAmt FROM C_InvoicePaySchedule WHERE C
    _Invoice_ID=109 ORDER BY DueDate;
    * SELECT invoiceOpen (109, null) FROM AD_System; – converted to default cl
    ient currency
    * SELECT invoiceOpen (109, 11) FROM AD_System; – converted to default clie
    nt currency
    * SELECT invoiceOpen (109, 102) FROM AD_System;
    * SELECT invoiceOpen (109, 103) FROM AD_System;
    ***
    * Pasado a Libertya a partir de Adempiere 360LTS
    * – ids son de tipo integer, no numeric
    * – TODO : tema de las zonas en los timestamp
    * – Excepciones en SELECT INTO requieren modificador STRICT bajo PostGreSQL o u
    sar
    * NOT FOUND
    * – Por ahora, el “ignore rounding” se hace como en libertya (-0.01,0.01),
    * en vez de usar la precisi├│n de la moneda
    * – Se toma el tipo de conversion de la factura, auqneu esto es dudosamente cor
    recto
    * ya que otras funciones , en particular currencyBase nunca tiene en cuenta
    * este valor
    * – Como en Libertya se tiene en cuenta tambien C_Invoice_Credit_ID para calcul
    ar
    * la cantidad alocada a una factura (aunque esto es medio dudoso….)
    * – No se soporta la fecha como 3er parametro (en realidad, tampoco se esta
    * usando actualmente, y se deberia poder resolver de otra manera)
    * – Libertya parece tener un bug al filtrar por C_InvoicePaySchedule_ID al calc
    ular
    * el granTotal (el granTotal SIEMPRE es el total de la factura, tomada directam
    ente
    * de C_Invoice.GranTotal o a partir de la suma de los DueAmt en C_InvoicePaySch
    edule);
    * se usa la sentencia como esta en Adempeire (esto es, solo se filtra por C_Inv
    oice_ID)
    * – Nuevo enfoque: NO se usa ni la vista C_Invoice_V ni multiplicadores
    * se asume todo positivo…
    * – El resultado SIEMPRE deberia ser positivo y en el intervalo [0..GrandTotal]

    * – 03 julio: se pasa a usar getAllocatedAmt para hacer esta funcion consistent
    e
    * con invoicePaid
    * – 03 julio: se pasa de usar STRICT a NOT FOUND; es mas eficiente
    ************************************************************************/
    DECLARE
    v_Currency_ID INTEGER;
    v_ConversionType_ID INTEGER;
    BEGIN

    SELECT C_Currency_ID, C_ConversionType_ID
    INTO v_Currency_ID, v_ConversionType_ID
    FROM C_Invoice I
    WHERE I.C_Invoice_ID = p_C_Invoice_ID;
    IF NOT FOUND THEN
    RAISE NOTICE ‘Invoice no econtrada – %’, p_C_Invoice_ID;
    RETURN NULL;
    END IF;
    RETURN invoiceOpen(p_c_invoice_id, p_c_invoicepayschedule_id, v_Currenc
    y_ID, v_ConversionType_ID);
    END;
    $BODY$
    LANGUAGE ‘plpgsql’ VOLATILE
    COST 100;
    ALTER FUNCTION invoiceopen(integer, integer) OWNER TO libertya;

    UPDATE ad_system SET version = ’24-02-2014′ WHERE ad_system_id = 0;
    [11]

    #38905
    AMEBIS
    Participante

    Estas migrando desde una version con desarrollos o modificaciones? No deberia darte ningun error en un instalacion previa bien creada.

Viendo 2 entradas - de la 1 a la 2 (de un total de 2)
  • Debes estar registrado para responder a este debate.