Erro de Versão do Banco de Dados


#1

Bom Dia a Todos,

Tive que reinstalar tudo que se refere ao adempiere na minha máquina, sendo assim, reinstalei o jre-6u16, jdk-6u16, postgresql-8.3.7-1, configurei as variáveis de ambiente e por fim o AdempiereLBR_3.4.2s, e tudo isso ok sem nenhum erro, fui para o próximo passo, restaurar o último backup que realizei com o o comando pg_dumpall -p 5432 -o -c -U adempiere > adempiere.out, para isso executei o comando psql -f template1 e até ai tudo beleza porém, quando fui entrar no adempiere pela primeira vez após a restauração do backup, o sistema exibe a mensagem abaixo:
O programa esperava a versão do banco de dados 2008-12-08, mas o banco de dados tem a versão 2008-03-26, Isto irá causar erros difíceis de reparar. Por favor pare imediatamente e faça a migração do banco de dados e logo abaixo os botões No e Migrate, depois que clico no botão Migrate ele executa o arquivo c:\adempiere\utils\RUN_Migrate.bat que inicia o navegador e me leva para o endereço http://www.adempiere.com/index.php/Migration. Bem, a partir daqui não sei mais como prosseguir, sendo assim peço ajuda de vocês e desde já agradeço pela sempre eficaz e valorosa ajuda.

Atc.,
Luiz Gonzaga


#2

Observe que a msg que esta dando esta dizendo q a versao do banco e’ posterior a esperada… Logo nao 'e um problema de migracao/atualizacao do banco e sim da aplicacao. Atualize sua aplicacao e verifique se falta aplicar algum script no banco.

Em tempo: tivemos problemas com o jre-6u16, parece q tinha uma pequena incompatibilidade com alguns componentes, que ja foi corrigida, mais acredito que ainda nao foi postada. Causava um erro nos campos de busca, abria a janela de busca mais nao retornava o valor selecionado. Temporariamente solucionado deixando uma versao anterior do java.


#3

Oi Eliercio, Tudo Bem?

Quanto ao jre já estou colocando a versão anterior a que estou utilizando conforme sua orientação, em relação a aplicação já mudei para a versão mais nova disponível no sourceforge (Adempiere_342s.zip e ExpDat.jar), porém a mesma mensagem de erro de versão do banco de dados continua sendo exibida. Agora, quanto aos scripts, me perdõe a ignorância mais não sei onde se encontram nem como devo rodá-los, você poderia me orientar de forma mais detalhada quanto a esses passos por favor?

Desde já muito obrigado e um forte abraço.
Luiz Gonzaga


#4

Olá Luiz, o Adempiere já vem com o banco na versão esperada 2008-12-08. Tente rodar o script RUN_ImportAdempiere para ter a base correta.

Att.
Ricardo


#5

Olá sr Luiz. Tive o mesmo problema estes dias, só que eu rodo em servidor linux. Ao se verifcar percebi que estava faltando uma parte das bibliotecas do postgresql, corrigi e os scripts funcionaram perfeito, por favor, verifica se não é o mesmo caso.


#6

Olá a todos,

Golombieski, conforme sua dica eu reinstalei a partir do zero o meu postgresql porém, não obtive exito, continua dando a mesma mensagem “O programa esperava a versão do banco de dados 2008-12-08, mas o banco de dados tem a versão 2008-03-26” depois que eu faço a restauração de um backup que tenho do meu banco, mesmo assim muito obrigado pela dica. Ricardo, conforme sua orientação quando eu rodo o script RUN_ImportAdempiere a fim de corrigir o banco ele exibe a mensagem abaixo e depois acaba por apagar o banco.

C:\Adempiere\utils>run_importadempiere
Setting myEnvironment …
Re-Create Adempiere User and import C:\Adempiere\data\Adempiere.dmp - (adempiere
)
O volume na unidade C não tem nome.
O número de série do volume é 0C64-F626

Pasta de C:\Adempiere\data

26/03/2008 11:53 28.860.416 Adempiere.dmp
1 arquivo(s) 28.860.416 bytes
0 pasta(s) 5.100.838.912 bytes disponíveis
== The import will show warnings. This is OK ==
Pressione qualquer tecla para continuar. . .
Adempiere Database Import $Revision: 1.3 $
Importing Adempiere DB from C:\Adempiere\data\Adempiere_pg.dmp (adempiere)

Re-Create user and database

CREATE ROLE
createdb: too many command-line arguments (first is “UNICODE”)
Try “createdb --help” for more information.

Import Adempiere_pg.dmp

psql: FATAL: database “adempiere” does not exist
psql: FATAL: database “adempiere” does not exist
psql: FATAL: database “adempiere” does not exist
Pressione qualquer tecla para continuar. . .
C:\Adempiere\utils>

Caso você esteja vendo algum procedimento errado acima ou tenha outra orientação me encontro no aguardo a fim de corrigir este problema e poder voltar a usar o Adempiere.

Atc.,
Luiz Gonzaga


#7

Olá Luiz,

Este é um erro com a versão do Postgres. Para resolver faça o seguinte:

  1. Entre na pasta: %ADEMPIERE_HOME%\utils\postgresql
  2. Substitua os seguintes arquivos: DBRestore.bat, DBRestore.sh, ImportAdempiere.bat, ImportAdempiere.sh por estes:

adempiere.svn.sourceforge.net/vi … estore.bat
adempiere.svn.sourceforge.net/vi … Restore.sh
adempiere.svn.sourceforge.net/vi … mpiere.bat
adempiere.svn.sourceforge.net/vi … empiere.sh

Fonte: adempiere.com/index.php/Release_342s (Detected installation problem in postgresql 8.3.5)

Um Abraço,
Ricardo Santana


#8

Boa Tarde a Todos,

Olá Ricardo, minha configuração atual é WIN-XP PRO SP3, jdk-6u15, jre-6u15 e postgresql 8.3.7-1, para ter certeza que estaria fazendo tudo certo após sua orientação vou detalhar os meus passos. Apaguei meu diretório adempiere, apaguei o banco de dados adempiere depois peguei a pasta zipada do Adempiere que tá no sourceforge (Adempiere_342s.zip) descompactei, adicionei a pasta C:\Adempiere\data o arquivo ExpDat-RC110.jar descompactado, também adicionei a pasta C:\Adempiere\lib o arquivo Customization-RC110.zip descompactado e por fim sobrescrevi os arquivos ImportAdempiere.bat, ImportAdempiere.sh, DBRestore.bat e DBRestore.sh da pasta C:\Adempiere\utils\postgresql pelos arquivos corrigidos que você indicou, recriei o banco de dados adempiere e com tudo já na mão comecei a instalação, executei o comando “psql -U adempiere -d adempiere -f ExpDat.dmp >> dump_errors.log”, rodei o “RUN_setup” depois iniciei o servidor com “RUN_Server2” logos executei o “RUN_Server2Stop” e finalmente executei minha nova instalação com o banco de dados sem as informações da minha empresa, até aqui tudo belezinha funcionou sem problema, então restaurei o meu backup do banco que tenho, rodei o comando “psql -U adempiere - f “meubackup.dmp” template1” logo após executei o AdempiereLBR e exibiu como de costume a mensagem em português de erro da versão do banco de dados, então conforme sua dica rodei o script “RUN_ImportAdempiere” e dessa vez realmente não não deu o erro “createdb: too many command-line arguments (first is “UNICODE”)” terminou sem problemas então executei o AdempiereLBR, e ai Ricardo a mesma mensagem de erro da versão do banco continua sendo exibida só que antes era em português num box retangular que ia de um lado ao outro da tela e agora é a mesma mensagem só que em inglês num pequeno box centralizado no meio da tela, resumindo, pelo que pude notar o além do script não ter corrigido o banco ele ainda apagou as informações que eu havia restaurado do meu backup, bem e isso ai que aconteceu, sendo assim peço mais uma sua ajuda como da comunidade na resolução desse problema pois estou realmente necessitando voltar a usar o AdempiereLBR. Mais uma vez muito obrigado pela valorosa ajuda de sempre.

Atc.,
Luiz Gonzaga


#9

Olá Luiz,

Então, para restaurar o backup use o processo RUN_DBRestore. Este processo pega o arquivo %ADEMPIERE_HOME%\data\ExpDat.dmp e restaura para o Banco configurado no RUN_setup.

Se você restaurou o arquivo do ExpDat-RC110.jar rodou o RUN_Server2, entrou na página de instalação do adempiere (host_do_servidor:porta) executou o processo do botão ‘click here to webstart-me now’ e tudo funcionou sem erro, a sua versão do código é a 342s.

Tente fazer o seguinte, pegue o seu arquivo “meubackup.dmp” e renomeie para ExpDat.dmp e coloque na pasta %ADEMPIERE_HOME%\data. Rode o RUN_DBRestore, depois execute o RUN_Server2, entre na página de instalação do adempiere e instale, se agora aparecer o erro de versão de banco de dados, então todos os seus dados foram feitos numa versão mais antiga, então o seu arquivo “meubackup.dmp” está numa versão incompatível com a 342s.

Considere 2 alternativas:
1 - Recomeçar pelo dmp da RC-110
2 - Migrar seu banco atual para 342s

Caso opte para a segunda opção, dê uma olhada na pasta migration do SVN do adempiere que tem os scripts (ex: 340 > 342s), dai é só executar.

Se não funcionar, pode colocar o que deu errado que a gente tenta descobrir o que acontece.

Um abraço,
Ricardo Santana


#10

Oi Ricardo,

Você já me ajudou tanto que já estou lhe considerando meu amigão (rsrsrs) então, vou tentar todas as opções que você indicou mas, indo por partes assim, tentei a primeira opção que seria renomear meu arquivo “meubackup.dmp” e colocar no lugar do C:\Adempiere\data\Expdat.dmp e restaurar via script “RUN_DBRestore” e meu sistema exibiu de cara a mensagem de erro abaixo:

C:\Adempiere\utils>run_dbrestore
Setting myEnvironment …
Re-Create Adempiere User and import C:\Adempiere\data\ExpDat.dmp
O volume na unidade C não tem nome.
O número de série do volume é 0C64-F626

Pasta de C:\Adempiere\data

30/09/2009 20:03 26.588.039 ExpDat.dmp
1 arquivo(s) 26.588.039 bytes
0 pasta(s) 5.147.779.072 bytes disponíveis
== The import will show warnings. This is OK ==
Pressione qualquer tecla para continuar. . .
Adempiere Database Import $Revision: 1.3 $
Importing Adempiere DB from C:\Adempiere\data\ExpDat.dmp (adempiere)
< foi inesperado neste momento.

C:\Adempiere\utils>@echo Usage: postgresql\DBRestore

C:\Adempiere\utils>

Atc.,

Luiz Gonzaga


#11

Bom Dia a Todos,

Olá Ricardo, pensando no conselho que você deu, achei melhor a segunda opção de migrar o banco agora visto que isso terá que ser feito mais cedo ou mais tarde, bem, nesta página " http://www.adempiere.com/index.php/Release_342#Migration_from_3.4.0 tem a opção de migração da qual você falou, só que como não tenho certeza do que executar e em que ordem fazê-lo, vou listar abaixo as opções e você me diz por favor o que fazer e em que ordem. (Nota: A tradução e do google)

1°) Na parte Solução de Problemas mais abaixo está dizendo Alguns problemas foram detectados em 3.4.2s liberação e eles estão fixados através de instalação de patches (descrito abaixo) e aplicar os scripts recomendado seguinte: e após isso o link leva pra o endereço SourceForge.net > Find Software > adempiere > SCM Repositories > adempiere > branches > adempiere343 > migration > 342s-trunk onde tem a opção de migração 340 > 342s que você falou, devo executa-los ? em que ordem?

2º) Na parte Patches logo abaixo diz Faça o download patches último arquivo do SourceForge aqui: ADempiere 3.4.2 Patches Instruções de instalação aqui: Patches_Installation, devo aplicar? em que ordem? antes os depois dos scripts?

3º) Na parte Migração do 3.4.0 logo abaixo diz Você precisará aplicar o script de migração prevista aqui para Oracle ou postgresql é quando clico no nome postgresql baixo o script All340s-342s_pg.sql que pelo nome da a entender que tem nele todos os scripts necessários para migração, devo executa-los? em que ordem? Bem essas são as minhas dúvidas, aguardo sua orientação.

Desde já muito Obrigado.
Atc.,
Luiz


#12

Olá Luiz,

Os scripts ficam na pasta migration:

adempiere.svn.sourceforge.net/vi … migration/

Se a sua versão é a 340, você pode usar tanto os scripts da pasta 340s-342s ou da All340s-342s, a única diferença que o segundo tem todos os scripts em um só arquivo.

Att.
Ricardo


#13

Boa Tarde a toda comunidade,

Olá Ricardo, executei os scripts do endereço que você indicou e apesar do script script_315_replace_pljava_for34.sql ter gerado o erro abaixo, a mensagem de Erro de Versão do Banco de Dados que aparecia sempre que iniciava o AdempiereLBR sumiu, graças a Deus e a você, muito obrigado, acredito que este erro no script seja algo simples, não entendo muito de SQL mais pelo que pude observar apesar do log informar o erro na 1.754 do script parece que está faltando a palavra “body” não sei também se faz diferença maiúsculo ou minusculo entre os dois símbolos $$ que aparecem nas linhas 1.752 e 1.756, você poderia dar uma olhada por favor? vou colocar o log todo gerado pelo phpPgAdmin como também o script 315_replace_pljava_for34.sql logo abaixo:

LOG DO ERRO GERADO PELO phpPgAdmin

PostgreSQL 8.3.7 rodando em 127.0.0.1:5432 -- Você está logado como usuário "adempiere", 15th Dec, 2009 4:17PM	

    * SQL
    * History
    * Find
    * Deslogar

Database RootphpPgAdmin: 	ServidorPostgreSQL?: 	Banco de dadosadempiere?:
Resultados da pesquisa
DROP FUNCTION
DROP FUNCTION
DROP FUNCTION
DROP FUNCTION
DROP FUNCTION
DROP FUNCTION
DROP FUNCTION
CREATE FUNCTION
CREATE FUNCTION
ALTER FUNCTION
CREATE FUNCTION
ALTER FUNCTION
CREATE FUNCTION
ALTER FUNCTION
CREATE FUNCTION
CREATE FUNCTION
CREATE FUNCTION
CREATE FUNCTION
CREATE FUNCTION
CREATE FUNCTION
CREATE FUNCTION
CREATE FUNCTION
CREATE FUNCTION
CREATE FUNCTION
CREATE FUNCTION
CREATE FUNCTION
CREATE FUNCTION
CREATE FUNCTION
CREATE FUNCTION
CREATE FUNCTION
CREATE FUNCTION
CREATE FUNCTION
CREATE FUNCTION
CREATE FUNCTION
315_replace_pljava_for34.sql:1754: ERROR: unterminated dollar-quoted string at or near "$$

BEGIN

RETURN ROUND($1, cast($2 as integer));"
LINE 9: $$
^
COMMIT
315_replace_pljava_for34.sql:1789: ERROR: unterminated dollar-quoted string at or near "$$ LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION currencyRound(

p_Amount NUMERIC,

p_CurTo_ID NUMERIC,

p_Costing VARCHAR -- Default 'N'

)

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: Round amount for Traget Currency

* Description:

* Round Amount using Costing or Standard Precision

* Returns unmodified amount if currency not found

* Test:

* SELECT currencyRound(currencyConvert(100,116,100,null,null),100,null) FROM AD_System => 64.72

************************************************************************/

DECLARE

v_StdPrecision NUMERIC;"
LINE 3: $$ LANGUAGE plpgsql;
^
315_replace_pljava_for34.sql:1790: ERROR: syntax error at or near "v_CostPrecision"
LINE 3: v_CostPrecision NUMERIC;
^
315_replace_pljava_for34.sql:1795: ERROR: syntax error at or near "IF"
LINE 6: IF (p_Amount IS NULL OR p_CurTo_ID IS NULL) THEN
^
315_replace_pljava_for34.sql:1796: ERROR: syntax error at or near "IF"
LINE 3: END IF;
^
315_replace_pljava_for34.sql:1802: ERROR: syntax error at or near ","
LINE 6: INTO v_StdPrecision, v_CostPrecision
^
315_replace_pljava_for34.sql:1805: ERROR: syntax error at or near "IF"
LINE 4: IF (v_StdPrecision IS NULL) THEN
^
315_replace_pljava_for34.sql:1806: ERROR: syntax error at or near "IF"
LINE 3: END IF;
^
315_replace_pljava_for34.sql:1809: ERROR: syntax error at or near "IF"
LINE 3: IF (p_Costing = 'Y') THEN
^
315_replace_pljava_for34.sql:1810: ERROR: syntax error at or near "IF"
LINE 3: END IF;
^
315_replace_pljava_for34.sql:1812: ERROR: syntax error at or near "RETURN"
LINE 3: RETURN ROUND (p_Amount, v_StdPrecision);
^
COMMIT
315_replace_pljava_for34.sql:1820: ERROR: unterminated dollar-quoted string at or near "$body$ LANGUAGE plpgsql;
"
LINE 3: $body$ LANGUAGE plpgsql;
^

Total runtime: 822.980 ms

SQL executado.

    * Edit SQL

SCRIPT 315_replace_pljava_for34.sql

DROP FUNCTION adempiereproperties();

DROP FUNCTION adempiereproperty(p_key character varying);

DROP FUNCTION adempiereversion();

DROP FUNCTION bomqtyavailable(m_product_id numeric, m_attributesetinstance_id numeric, m_warehouse_id numeric, m_locator_id numeric);

DROP FUNCTION bomqtyonhand(m_product_id numeric, m_attributesetinstance_id numeric, m_warehouse_id numeric, m_locator_id numeric);

DROP FUNCTION bomqtyordered(m_product_id numeric, m_attributesetinstance_id numeric, m_warehouse_id numeric, m_locator_id numeric);

DROP FUNCTION bomqtyreserved(m_product_id numeric, m_attributesetinstance_id numeric, m_warehouse_id numeric, m_locator_id numeric);

-- DROP FUNCTION documentno(p_mpc_mrp_id numeric);   -- you can enable this line for 340


CREATE OR REPLACE FUNCTION acctBalance(p_Account_ID numeric, p_AmtDr numeric, p_AmtCr numeric) RETURNS numeric AS $body$
DECLARE
	v_balance	NUMERIC;
	v_AccountType   C_ElementValue.AccountType%TYPE;
    	v_AccountSign   C_ElementValue.AccountSign%TYPE;

BEGIN
	    v_balance := p_AmtDr - p_AmtCr;
	    --  
	    IF (p_Account_ID > 0) THEN
	        SELECT AccountType, AccountSign
	          INTO v_AccountType, v_AccountSign
	        FROM C_ElementValue
	        WHERE C_ElementValue_ID=p_Account_ID;
	   --   DBMS_OUTPUT.PUT_LINE('Type=' || v_AccountType || ' - Sign=' || v_AccountSign);
	        --  Natural Account Sign
	        IF (v_AccountSign='N') THEN
	            IF (v_AccountType IN ('A','E')) THEN
	                v_AccountSign := 'D';
	            ELSE
	                v_AccountSign := 'C';
	            END IF;
	        --  DBMS_OUTPUT.PUT_LINE('Type=' || v_AccountType || ' - Sign=' || v_AccountSign);
	        END IF;
	        --  Debit Balance
	        IF (v_AccountSign = 'C') THEN
	            v_balance := p_AmtCr - p_AmtDr;
	        END IF;
	    END IF;
	    --
	    RETURN v_balance;
	EXCEPTION WHEN OTHERS THEN
	    -- In case Acct not found
    	RETURN  p_AmtDr - p_AmtCr;
	
END;

$body$ LANGUAGE plpgsql;
  

 	  	 


CREATE OR REPLACE FUNCTION bompricelimit(Product_id numeric, Pricelist_version_id numeric) RETURNS numeric AS $bompricelimit$
DECLARE
	v_Price	NUMERIC;
	v_ProductPrice	NUMERIC;
	bom RECORD;

BEGIN
	--	Try to get price from PriceList directly
	SELECT	COALESCE (SUM(PriceLimit), 0)
      	INTO	v_Price
   	FROM	M_ProductPrice
	WHERE M_PriceList_Version_ID=PriceList_Version_ID AND M_Product_ID=Product_ID;

	--	No Price - Check if BOM
	IF (v_Price = 0) THEN
		FOR bom IN  
			SELECT b.M_ProductBOM_ID, b.BOMQty, p.IsBOM
			FROM M_Product_BOM b, M_Product p
			WHERE b.M_ProductBOM_ID=p.M_Product_ID
		  	AND b.M_Product_ID=Product_ID
		LOOP
			v_ProductPrice := bomPriceLimit (bom.M_ProductBOM_ID, PriceList_Version_ID);
			v_Price := v_Price + (bom.BOMQty * v_ProductPrice);
		END LOOP;
	END IF;
	--
	RETURN v_Price;
	
END;

$bompricelimit$ LANGUAGE plpgsql;
  
  
  
  
ALTER FUNCTION bompricelimit(m_product_id numeric, m_pricelist_version_id numeric) OWNER TO adempiere;


CREATE OR REPLACE FUNCTION bompricelist(Product_id numeric, Pricelist_version_id numeric) RETURNS numeric AS $bompricelist$
DECLARE
	v_Price	NUMERIC;
	v_ProductPrice	NUMERIC;
	bom RECORD;

BEGIN
	--	Try to get price from pricelist directly
	SELECT	COALESCE (SUM(PriceList), 0)
	INTO	v_Price
	FROM	M_ProductPrice
	WHERE M_PriceList_Version_ID=PriceList_Version_ID AND M_Product_ID=Product_ID;

	--	No Price - Check if BOM
	IF (v_Price = 0) THEN
		FOR bom IN  
			SELECT b.M_ProductBOM_ID, b.BOMQty, p.IsBOM
			FROM M_Product_BOM b, M_Product p
			WHERE b.M_ProductBOM_ID=p.M_Product_ID
		  	AND b.M_Product_ID=Product_ID
		LOOP
			v_ProductPrice := bomPriceList (bom.M_ProductBOM_ID, PriceList_Version_ID);
			v_Price := v_Price + (bom.BOMQty * v_ProductPrice);
		END LOOP;
	END IF;
	--
	RETURN v_Price;
	
END;

$bompricelist$ LANGUAGE plpgsql;
  
  
ALTER FUNCTION bompricelist(m_product_id numeric, m_pricelist_version_id numeric) OWNER TO adempiere;

CREATE OR REPLACE FUNCTION bompricestd(Product_id numeric, Pricelist_version_id numeric) RETURNS numeric AS $bompricestd$
DECLARE
	v_Price	NUMERIC;
	v_ProductPrice	NUMERIC;
	bom RECORD;

BEGIN
	--	Try to get price from PriceList directly
	SELECT	COALESCE(SUM(PriceStd), 0)
	INTO	v_Price
	FROM	M_ProductPrice
	WHERE M_PriceList_Version_ID=PriceList_Version_ID AND M_Product_ID=Product_ID;

	--	No Price - Check if BOM
	IF (v_Price = 0) THEN
		FOR bom IN  
			SELECT b.M_ProductBOM_ID, b.BOMQty, p.IsBOM
			FROM M_Product_BOM b, M_Product p
			WHERE b.M_ProductBOM_ID=p.M_Product_ID
		  	AND b.M_Product_ID=Product_ID
		LOOP
			v_ProductPrice := bomPriceStd (bom.M_ProductBOM_ID, PriceList_Version_ID);
			v_Price := v_Price + (bom.BOMQty * v_ProductPrice);
		END LOOP;
	END IF;
	--
	RETURN v_Price;
	
END;

$bompricestd$ LANGUAGE plpgsql;
  
ALTER FUNCTION bompricestd(m_product_id numeric, m_pricelist_version_id numeric) OWNER TO adempiere;

CREATE OR REPLACE FUNCTION bomqtyavailable(Product_ID numeric, Attributesetinstance_id numeric, Warehouse_ID numeric, Locator_ID numeric)
  RETURNS numeric AS $bomqtyavailable$

BEGIN
	
	RETURN bomQtyOnHand(Product_ID, Attributesetinstance_id, Warehouse_ID, Locator_ID)
		- bomQtyReserved(Product_ID, Attributesetinstance_id, Warehouse_ID, Locator_ID);
	
END;

$bomqtyavailable$ LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION bomqtyavailable(Product_id numeric, Warehouse_id numeric, Locator_id numeric)
  RETURNS numeric AS $bomqtyavailable$
DECLARE
	v_Price	NUMERIC;
	v_ProductPrice	NUMERIC;
	bom RECORD;

BEGIN
	
		RETURN bomQtyOnHand(Product_ID, Warehouse_ID, Locator_ID)
		- bomQtyReserved(Product_ID, Warehouse_ID, Locator_ID);
	
END;

$bomqtyavailable$ LANGUAGE plpgsql;


CREATE OR REPLACE FUNCTION bomqtyonhand(p_Product_ID numeric, p_Warehouse_ID numeric, p_Locator_ID numeric)
  RETURNS numeric AS $bomqtyonhand$
DECLARE
	v_Warehouse_ID		NUMERIC;
	v_Quantity		NUMERIC := 99999;	--	unlimited
	v_IsBOM			CHAR(1);
	v_IsStocked		CHAR(1);
	v_ProductType		CHAR(1);
	v_ProductQty		NUMERIC;
	v_StdPrecision		NUMERIC;
	bom RECORD;

BEGIN
	
	--	Check Parameters
		v_Warehouse_ID := p_Warehouse_ID;
		IF (v_Warehouse_ID IS NULL) THEN
			IF (p_Locator_ID IS NULL) THEN
				RETURN 0;
			ELSE
				SELECT 	MAX(M_Warehouse_ID) INTO v_Warehouse_ID
				FROM	M_Locator
				WHERE	M_Locator_ID=p_Locator_ID;
			END IF;
		END IF;
		IF (v_Warehouse_ID IS NULL) THEN
			RETURN 0;
		END IF;
	--	DBMS_OUTPUT.PUT_LINE('Warehouse=' || v_Warehouse_ID);
	
		--	Check, if product exists and if it is stocked
		BEGIN
			SELECT	IsBOM, ProductType, IsStocked
			  INTO	v_IsBOM, v_ProductType, v_IsStocked
			FROM M_Product
			WHERE M_Product_ID=p_Product_ID;
			--
		
		EXCEPTION	--	not found
			WHEN OTHERS THEN
				RETURN 0;
		END;
	
		--	No reservation for non-stocked
		IF (v_IsBOM='N' AND (v_ProductType<>'I' OR v_IsStocked='N')) THEN
			RETURN 0;
		--	Stocked item
		ELSIF (v_IsStocked='Y') THEN
			--	Get ProductQty
			SELECT 	COALESCE(SUM(QtyOnHand), 0)
			  INTO	v_ProductQty
			FROM 	M_Storage s
			WHERE M_Product_ID=p_Product_ID
			  AND EXISTS (SELECT * FROM M_Locator l WHERE s.M_Locator_ID=l.M_Locator_ID
			  	AND l.M_Warehouse_ID=v_Warehouse_ID);
			--
			RETURN v_ProductQty;
		END IF;
	
		--	Go though BOM

		FOR bom IN  
			SELECT b.M_ProductBOM_ID, b.BOMQty, p.IsBOM, p.IsStocked, p.ProductType
			FROM M_Product_BOM b, M_Product p
			WHERE b.M_ProductBOM_ID=p.M_Product_ID
		  	AND b.M_Product_ID=p_Product_ID
		LOOP
			--	Stocked Items "leaf node"
			IF (bom.ProductType = 'I' AND bom.IsStocked = 'Y') THEN
				--	Get ProductQty
				SELECT 	COALESCE(SUM(QtyOnHand), 0)
				  INTO	v_ProductQty
				FROM 	M_Storage s
				WHERE 	M_Product_ID=bom.M_ProductBOM_ID
				  AND EXISTS (SELECT * FROM M_Locator l WHERE s.M_Locator_ID=l.M_Locator_ID
				  	AND l.M_Warehouse_ID=v_Warehouse_ID);
				--	Get Rounding Precision
				SELECT 	COALESCE(MAX(u.StdPrecision), 0)
				  INTO	v_StdPrecision
				FROM 	C_UOM u, M_Product p 
				WHERE 	u.C_UOM_ID=p.C_UOM_ID AND p.M_Product_ID=bom.M_ProductBOM_ID;
				--	How much can we make with this product
				v_ProductQty := ROUND (v_ProductQty/bom.BOMQty, v_StdPrecision);
				--	How much can we make overall
				IF (v_ProductQty < v_Quantity) THEN
					v_Quantity := v_ProductQty;
				END IF;
			--	Another BOM
			ELSIF (bom.IsBOM = 'Y') THEN
				v_ProductQty := bomQtyOnHand (bom.M_ProductBOM_ID, v_Warehouse_ID, p_Locator_ID);
				--	How much can we make overall
				IF (v_ProductQty < v_Quantity) THEN
					v_Quantity := v_ProductQty;
				END IF;
			END IF;
		END LOOP;	--	BOM
	
	
		IF (v_Quantity > 0) THEN
			--	Get Rounding Precision for Product
			SELECT 	COALESCE(MAX(u.StdPrecision), 0)
			  INTO	v_StdPrecision
			FROM 	C_UOM u, M_Product p 
			WHERE 	u.C_UOM_ID=p.C_UOM_ID AND p.M_Product_ID=p_Product_ID;
			--
			RETURN ROUND (v_Quantity, v_StdPrecision);
		END IF;
	RETURN 0;
	
END;

$bomqtyonhand$ LANGUAGE plpgsql;



-- ?? CREATE OR REPLACE FUNCTION BomqtyonhandASI

CREATE OR REPLACE FUNCTION bomqtyordered(p_Product_ID numeric, p_Warehouse_ID numeric, p_Locator_ID numeric)
  RETURNS numeric AS $bomqtyordered$
DECLARE
	v_Warehouse_ID		NUMERIC;
	v_Quantity		NUMERIC := 99999;	--	unlimited
	v_IsBOM			CHAR(1);
	v_IsStocked		CHAR(1);
	v_ProductType		CHAR(1);
	v_ProductQty		NUMERIC;
	v_StdPrecision		NUMERIC;
	bom RECORD;

BEGIN
	
	--	Check Parameters
		v_Warehouse_ID := p_Warehouse_ID;
		IF (v_Warehouse_ID IS NULL) THEN
			IF (p_Locator_ID IS NULL) THEN
				RETURN 0;
			ELSE
				SELECT 	MAX(M_Warehouse_ID) INTO v_Warehouse_ID
				FROM	M_Locator
				WHERE	M_Locator_ID=p_Locator_ID;
			END IF;
		END IF;
		IF (v_Warehouse_ID IS NULL) THEN
			RETURN 0;
		END IF;
	--	DBMS_OUTPUT.PUT_LINE('Warehouse=' || v_Warehouse_ID);
	
		--	Check, if product exists and if it is stocked
		BEGIN
			SELECT	IsBOM, ProductType, IsStocked
			  INTO	v_IsBOM, v_ProductType, v_IsStocked
			FROM M_Product
			WHERE M_Product_ID=p_Product_ID;
			--
		
		EXCEPTION	--	not found
			WHEN OTHERS THEN
				RETURN 0;
		END;
	
		--	No reservation for non-stocked
		IF (v_IsBOM='N' AND (v_ProductType<>'I' OR v_IsStocked='N')) THEN
			RETURN 0;
		--	Stocked item
		ELSIF (v_IsStocked='Y') THEN
			--	Get ProductQty
			SELECT 	COALESCE(SUM(QtyOrdered), 0)
			  INTO	v_ProductQty
			FROM 	M_Storage s
			WHERE M_Product_ID=p_Product_ID
			  AND EXISTS (SELECT * FROM M_Locator l WHERE s.M_Locator_ID=l.M_Locator_ID
			  	AND l.M_Warehouse_ID=v_Warehouse_ID);
			--
			RETURN v_ProductQty;
		END IF;
	
		--	Go though BOM

		FOR bom IN  
			SELECT b.M_ProductBOM_ID, b.BOMQty, p.IsBOM, p.IsStocked, p.ProductType
			FROM M_Product_BOM b, M_Product p
			WHERE b.M_ProductBOM_ID=p.M_Product_ID
		  	AND b.M_Product_ID=p_Product_ID
		LOOP
			--	Stocked Items "leaf node"
			IF (bom.ProductType = 'I' AND bom.IsStocked = 'Y') THEN
				--	Get ProductQty
				SELECT 	COALESCE(SUM(QtyOrdered), 0)
				  INTO	v_ProductQty
				FROM 	M_Storage s
				WHERE 	M_Product_ID=bom.M_ProductBOM_ID
				  AND EXISTS (SELECT * FROM M_Locator l WHERE s.M_Locator_ID=l.M_Locator_ID
				  	AND l.M_Warehouse_ID=v_Warehouse_ID);
				--	Get Rounding Precision
				SELECT 	COALESCE(MAX(u.StdPrecision), 0)
				  INTO	v_StdPrecision
				FROM 	C_UOM u, M_Product p 
				WHERE 	u.C_UOM_ID=p.C_UOM_ID AND p.M_Product_ID=bom.M_ProductBOM_ID;
				--	How much can we make with this product
				v_ProductQty := ROUND (v_ProductQty/bom.BOMQty, v_StdPrecision);
				--	How much can we make overall
				IF (v_ProductQty < v_Quantity) THEN
					v_Quantity := v_ProductQty;
				END IF;
			--	Another BOM
			ELSIF (bom.IsBOM = 'Y') THEN
				v_ProductQty := bomQtyOrdered (bom.M_ProductBOM_ID, v_Warehouse_ID, p_Locator_ID);
				--	How much can we make overall
				IF (v_ProductQty < v_Quantity) THEN
					v_Quantity := v_ProductQty;
				END IF;
			END IF;
		END LOOP;	--	BOM
	
		--	Unlimited (e.g. only services)
		IF (v_Quantity = 99999) THEN
			RETURN 0;
		END IF;
	
		IF (v_Quantity > 0) THEN
			--	Get Rounding Precision for Product
			SELECT 	COALESCE(MAX(u.StdPrecision), 0)
			  INTO	v_StdPrecision
			FROM 	C_UOM u, M_Product p 
			WHERE 	u.C_UOM_ID=p.C_UOM_ID AND p.M_Product_ID=p_Product_ID;
			--
			RETURN ROUND (v_Quantity, v_StdPrecision);
		END IF;
	RETURN 0;
	
END;

$bomqtyordered$ LANGUAGE plpgsql;



-- ?? CREATE OR REPLACE FUNCTION BomqtyorderedASI

CREATE OR REPLACE FUNCTION bomqtyreserved(p_Product_ID numeric, p_Warehouse_ID numeric, p_Locator_ID numeric)
  RETURNS numeric AS $bomqtyreserved$
DECLARE
	v_Warehouse_ID		NUMERIC;
	v_Quantity		NUMERIC := 99999;	--	unlimited
	v_IsBOM			CHAR(1);
	v_IsStocked		CHAR(1);
	v_ProductType		CHAR(1);
	v_ProductQty		NUMERIC;
	v_StdPrecision		NUMERIC;
	bom RECORD;

BEGIN
	
	--	Check Parameters
		v_Warehouse_ID := p_Warehouse_ID;
		IF (v_Warehouse_ID IS NULL) THEN
			IF (p_Locator_ID IS NULL) THEN
				RETURN 0;
			ELSE
				SELECT 	MAX(M_Warehouse_ID) INTO v_Warehouse_ID
				FROM	M_Locator
				WHERE	M_Locator_ID=p_Locator_ID;
			END IF;
		END IF;
		IF (v_Warehouse_ID IS NULL) THEN
			RETURN 0;
		END IF;
	--	DBMS_OUTPUT.PUT_LINE('Warehouse=' || v_Warehouse_ID);
	
		--	Check, if product exists and if it is stocked
		BEGIN
			SELECT	IsBOM, ProductType, IsStocked
			  INTO	v_IsBOM, v_ProductType, v_IsStocked
			FROM M_Product
			WHERE M_Product_ID=p_Product_ID;
			--
		
		EXCEPTION	--	not found
			WHEN OTHERS THEN
				RETURN 0;
		END;
	
		--	No reservation for non-stocked
		IF (v_IsBOM='N' AND (v_ProductType<>'I' OR v_IsStocked='N')) THEN
			RETURN 0;
		--	Stocked item
		ELSIF (v_IsStocked='Y') THEN
			--	Get ProductQty
			SELECT 	COALESCE(SUM(QtyReserved), 0)
			  INTO	v_ProductQty
			FROM 	M_Storage s
			WHERE M_Product_ID=p_Product_ID
			  AND EXISTS (SELECT * FROM M_Locator l WHERE s.M_Locator_ID=l.M_Locator_ID
			  	AND l.M_Warehouse_ID=v_Warehouse_ID);
			--
			RETURN v_ProductQty;
		END IF;
	
		--	Go though BOM

		FOR bom IN  
			SELECT b.M_ProductBOM_ID, b.BOMQty, p.IsBOM, p.IsStocked, p.ProductType
			FROM M_Product_BOM b, M_Product p
			WHERE b.M_ProductBOM_ID=p.M_Product_ID
		  	AND b.M_Product_ID=p_Product_ID
		LOOP
			--	Stocked Items "leaf node"
			IF (bom.ProductType = 'I' AND bom.IsStocked = 'Y') THEN
				--	Get ProductQty
				SELECT 	COALESCE(SUM(QtyReserved), 0)
				  INTO	v_ProductQty
				FROM 	M_Storage s
				WHERE 	M_Product_ID=bom.M_ProductBOM_ID
				  AND EXISTS (SELECT * FROM M_Locator l WHERE s.M_Locator_ID=l.M_Locator_ID
				  	AND l.M_Warehouse_ID=v_Warehouse_ID);
				--	Get Rounding Precision
				SELECT 	COALESCE(MAX(u.StdPrecision), 0)
				  INTO	v_StdPrecision
				FROM 	C_UOM u, M_Product p 
				WHERE 	u.C_UOM_ID=p.C_UOM_ID AND p.M_Product_ID=bom.M_ProductBOM_ID;
				--	How much can we make with this product
				v_ProductQty := ROUND (v_ProductQty/bom.BOMQty, v_StdPrecision);
				--	How much can we make overall
				IF (v_ProductQty < v_Quantity) THEN
					v_Quantity := v_ProductQty;
				END IF;
			--	Another BOM
			ELSIF (bom.IsBOM = 'Y') THEN
				v_ProductQty := bomQtyReserved (bom.M_ProductBOM_ID, v_Warehouse_ID, p_Locator_ID);
				--	How much can we make overall
				IF (v_ProductQty < v_Quantity) THEN
					v_Quantity := v_ProductQty;
				END IF;
			END IF;
		END LOOP;	--	BOM
	
		--	Unlimited (e.g. only services)
		IF (v_Quantity = 99999) THEN
			RETURN 0;
		END IF;
	
		IF (v_Quantity > 0) THEN
			--	Get Rounding Precision for Product
			SELECT 	COALESCE(MAX(u.StdPrecision), 0)
			  INTO	v_StdPrecision
			FROM 	C_UOM u, M_Product p 
			WHERE 	u.C_UOM_ID=p.C_UOM_ID AND p.M_Product_ID=p_Product_ID;
			--
			RETURN ROUND (v_Quantity, v_StdPrecision);
		END IF;
	RETURN 0;
	
END;

$bomqtyreserved$ LANGUAGE plpgsql;


-- ?? CREATE OR REPLACE FUNCTION BomqtyreservedASI

CREATE OR REPLACE FUNCTION bpartnerRemitLocation(p_C_BPartner_ID C_BPartner.C_BPartner_ID%TYPE) 
RETURNS numeric AS $body$

DECLARE
	v_C_Location_ID	NUMERIC := NULL;
	l RECORD;

BEGIN
	FOR l IN 
		SELECT	IsRemitTo, C_Location_ID
		FROM	C_BPartner_Location
		WHERE	C_BPartner_ID=p_C_BPartner_ID
		ORDER BY IsRemitTo DESC
	LOOP
		IF (v_C_Location_ID IS NULL) THEN
			v_C_Location_ID := l.C_Location_ID;
		END IF;
	END LOOP;
	RETURN v_C_Location_ID;
	
END;

$body$ LANGUAGE plpgsql;
  


create or replace FUNCTION currencyBase
(
	p_Amount	NUMERIC,
	p_CurFrom_ID	NUMERIC,
	p_ConvDate	timestamp with time zone,
	p_Client_ID	NUMERIC,
	p_Org_ID	NUMERIC
)
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:	Convert Amount to Base Currency of Client
 * Description:
 *		Get CurrencyTo from Client
 *		Returns NULL, if conversion not found
 *		Standard Rounding
 * Test:
 *		SELECT currencyBase(100,116,null,11,null) FROM AD_System; => 64.72
 ************************************************************************/
DECLARE
	v_CurTo_ID	NUMERIC;
BEGIN
	--	Get Currency
	SELECT	MAX(ac.C_Currency_ID)
	  INTO	v_CurTo_ID
	FROM	AD_ClientInfo ci, C_AcctSchema ac
	WHERE	ci.C_AcctSchema1_ID=ac.C_AcctSchema_ID
	  AND	ci.AD_Client_ID=p_Client_ID;
	--	Same as Currency_Conversion - if currency/rate not found - return 0
	IF (v_CurTo_ID IS NULL) THEN
		RETURN NULL;
	END IF;
	--	Same currency
	IF (p_CurFrom_ID = v_CurTo_ID) THEN
		RETURN p_Amount;
	END IF;

	RETURN currencyConvert (p_Amount, p_CurFrom_ID, v_CurTo_ID, p_ConvDate, null, p_Client_ID, p_Org_ID);
END;

$body$ LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION currencyConvert(
	p_Amount		NUMERIC,
	p_CurFrom_ID		NUMERIC,
	p_CurTo_ID		NUMERIC,
	p_ConvDate		timestamp with time zone,
	p_ConversionType_ID IN	NUMERIC,
	p_Client_ID		NUMERIC,
	p_Org_ID		NUMERIC
	) 

RETURNS numeric AS $body$
	
/*************************************************************************
 *************************************************************************
 ***
 * Title:	Convert Amount (using IDs)
 * Description:
 *		from CurrencyFrom_ID to CurrencyTo_ID
 *		Returns NULL, if conversion not found
 *		Standard Rounding
 * Test:
 *	SELECT currencyConvert(100,116,100,null,null,null,null) FROM AD_System;  => 64.72
 ************************************************************************/	
	
	
DECLARE
	v_Rate				NUMERIC;

BEGIN
	--	Return Amount
		IF (p_Amount = 0 OR p_CurFrom_ID = p_CurTo_ID) THEN
			RETURN p_Amount;
		END IF;
		--	Return NULL
		IF (p_Amount IS NULL OR p_CurFrom_ID IS NULL OR p_CurTo_ID IS NULL) THEN
			RETURN NULL;
		END IF;
	
		--	Get Rate
		v_Rate := currencyRate (p_CurFrom_ID, p_CurTo_ID, p_ConvDate, p_ConversionType_ID, p_Client_ID, p_Org_ID);
		IF (v_Rate IS NULL) THEN
			RETURN NULL;
		END IF;
	
		--	Standard Precision
	RETURN currencyRound(p_Amount * v_Rate, p_CurTo_ID, null);
	
END;

$body$ LANGUAGE plpgsql;






CREATE OR REPLACE FUNCTION currencyRate(
	p_CurFrom_ID		NUMERIC,
	p_CurTo_ID		NUMERIC,
	p_ConvDate		timestamp with time zone,
	p_ConversionType_ID	NUMERIC,
	p_Client_ID		NUMERIC,
	p_Org_ID		NUMERIC
	) 

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:	Return Conversion Rate
 * Description:
 *		from CurrencyFrom_ID to CurrencyTo_ID
 *		Returns NULL, if rate not found
 * Test
 *		SELECT currencyrate(116, 100, null, null, null, null) FROM AD_System;  => .647169
 ************************************************************************/
	
	
DECLARE
	--	Currency From variables
	cf_IsEuro		CHAR(1);
	cf_IsEMUMember		CHAR(1);
	cf_EMUEntryDate		timestamp with time zone;
	cf_EMURate		NUMERIC;
	--	Currency To variables
	ct_IsEuro		CHAR(1);
	ct_IsEMUMember		CHAR(1);
	ct_EMUEntryDate	DATE;
	ct_EMURate		NUMERIC;
	--	Triangle
	v_CurrencyFrom		NUMERIC;
	v_CurrencyTo		NUMERIC;
	v_CurrencyEuro		NUMERIC;
	--
	v_ConvDate		timestamp with time zone := now();
	v_ConversionType_ID	NUMERIC := 0;
	v_Rate			NUMERIC;
	c			RECORD;			

BEGIN
--	No Conversion
	IF (p_CurFrom_ID = p_CurTo_ID) THEN
		RETURN 1;
	END IF;
	--	Default Date Parameter
	IF (p_ConvDate IS NOT NULL) THEN
		v_ConvDate := p_ConvDate;   --  SysDate
	END IF;
    --  Default Conversion Type
	IF (p_ConversionType_ID IS NULL OR p_ConversionType_ID = 0) THEN
		BEGIN
		    SELECT C_ConversionType_ID 
		      INTO v_ConversionType_ID
		    FROM C_ConversionType 
		    WHERE IsDefault='Y'
		      AND AD_Client_ID IN (0,p_Client_ID)
		    ORDER BY AD_Client_ID DESC
		    LIMIT 1;
		EXCEPTION WHEN OTHERS THEN
		    RAISE NOTICE 'Conversion Type Not Found';
		END;
    	ELSE
        	v_ConversionType_ID := p_ConversionType_ID;
	END IF;

	--	Get Currency Info
	SELECT	MAX(IsEuro), MAX(IsEMUMember), MAX(EMUEntryDate), MAX(EMURate)
	  INTO	cf_IsEuro, cf_IsEMUMember, cf_EMUEntryDate, cf_EMURate
	FROM		C_Currency
	  WHERE	C_Currency_ID = p_CurFrom_ID;
	-- Not Found
	IF (cf_IsEuro IS NULL) THEN
		RAISE NOTICE 'From Currency Not Found';
		RETURN NULL;
	END IF;
	SELECT	MAX(IsEuro), MAX(IsEMUMember), MAX(EMUEntryDate), MAX(EMURate)
	  INTO	ct_IsEuro, ct_IsEMUMember, ct_EMUEntryDate, ct_EMURate
	FROM		C_Currency
	  WHERE	C_Currency_ID = p_CurTo_ID;
	-- Not Found
	IF (ct_IsEuro IS NULL) THEN
		RAISE NOTICE 'To Currency Not Found';
		RETURN NULL;
	END IF;

	--	Fixed - From Euro to EMU
	IF (cf_IsEuro = 'Y' AND ct_IsEMUMember ='Y' AND v_ConvDate >= ct_EMUEntryDate) THEN
		RETURN ct_EMURate;
	END IF;

	--	Fixed - From EMU to Euro
	IF (ct_IsEuro = 'Y' AND cf_IsEMUMember ='Y' AND v_ConvDate >= cf_EMUEntryDate) THEN
		RETURN 1 / cf_EMURate;
	END IF;

	--	Fixed - From EMU to EMU
	IF (cf_IsEMUMember = 'Y' AND cf_IsEMUMember ='Y'
			AND v_ConvDate >= cf_EMUEntryDate AND v_ConvDate >= ct_EMUEntryDate) THEN
		RETURN ct_EMURate / cf_EMURate;
	END IF;

	--	Flexible Rates
	v_CurrencyFrom := p_CurFrom_ID;
	v_CurrencyTo := p_CurTo_ID;

	-- if EMU Member involved, replace From/To Currency
	IF ((cf_isEMUMember = 'Y' AND v_ConvDate >= cf_EMUEntryDate)
	  OR (ct_isEMUMember = 'Y' AND v_ConvDate >= ct_EMUEntryDate)) THEN
		SELECT	MAX(C_Currency_ID)
		  INTO	v_CurrencyEuro
		FROM		C_Currency
		WHERE	IsEuro = 'Y';
		-- Conversion Rate not Found
		IF (v_CurrencyEuro IS NULL) THEN
			RAISE NOTICE 'Euro Not Found';
			RETURN NULL;
		END IF;
		IF (cf_isEMUMember = 'Y' AND v_ConvDate >= cf_EMUEntryDate) THEN
			v_CurrencyFrom := v_CurrencyEuro;
		ELSE
			v_CurrencyTo := v_CurrencyEuro;
		END IF;
	END IF;

	--	Get Rate

	BEGIN
		FOR c IN SELECT	MultiplyRate
			FROM	C_Conversion_Rate
			WHERE	C_Currency_ID=v_CurrencyFrom AND C_Currency_ID_To=v_CurrencyTo
			  AND	C_ConversionType_ID=v_ConversionType_ID
			  AND	v_ConvDate BETWEEN ValidFrom AND ValidTo
			  AND	AD_Client_ID IN (0,p_Client_ID) AND AD_Org_ID IN (0,p_Org_ID)
			ORDER BY AD_Client_ID DESC, AD_Org_ID DESC, ValidFrom DESC
		LOOP
			v_Rate := c.MultiplyRate;
			EXIT;	--	only first
		END LOOP;
	END;
	--	Not found
	IF (v_Rate IS NULL) THEN
		RAISE NOTICE 'Conversion Rate Not Found';
		RETURN NULL;
	END IF;

	--	Currency From was EMU
	IF (cf_isEMUMember = 'Y' AND v_ConvDate >= cf_EMUEntryDate) THEN
		RETURN v_Rate / cf_EMURate;
	END IF;

	--	Currency To was EMU
	IF (ct_isEMUMember = 'Y' AND v_ConvDate >= ct_EMUEntryDate) THEN
		RETURN v_Rate * ct_EMURate;
	END IF;

	RETURN v_Rate;

EXCEPTION WHEN OTHERS THEN
	RAISE NOTICE '%', SQLERRM;
	RETURN NULL;

	
END;

$body$ LANGUAGE plpgsql;


CREATE OR REPLACE FUNCTION invoiceDiscount
(
	p_C_Invoice_ID		       	NUMERIC,
	p_paydate 			timestamp with time zone,
	p_C_InvoicePaySchedule_ID	NUMERIC
)
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 Payment Discount Amount
 * Description:
 *			- Calculate discountable amount (i.e. with or without tax)
 *			- Calculate and return payment discount
 * Test:
 * 		select invoiceDiscount(109, now(), 103) from ad_system; => 0
 ************************************************************************/
DECLARE
	v_Amount		NUMERIC;
	v_IsDiscountLineAmt	CHAR(1);
	v_GrandTotal		NUMERIC;
	v_TotalLines		NUMERIC;
	v_C_PaymentTerm_ID	NUMERIC(10);
	v_DocDate		timestamp with time zone;
	v_PayDate		timestamp with time zone := now();
    	v_IsPayScheduleValid    CHAR(1);

BEGIN
	SELECT 	ci.IsDiscountLineAmt, i.GrandTotal, i.TotalLines,
		i.C_PaymentTerm_ID, i.DateInvoiced, i.IsPayScheduleValid
	INTO 	v_IsDiscountLineAmt, v_GrandTotal, v_TotalLines,
		v_C_PaymentTerm_ID, v_DocDate, v_IsPayScheduleValid
	FROM 	AD_ClientInfo ci, C_Invoice i
	WHERE 	ci.AD_Client_ID=i.AD_Client_ID
	  AND 	i.C_Invoice_ID=p_C_Invoice_ID;
	  
	--	What Amount is the Discount Base?
 	IF (v_IsDiscountLineAmt = 'Y') THEN
		v_Amount := v_TotalLines;
	ELSE
		v_Amount := v_GrandTotal;
	END IF;

	--	Anything to discount?
	IF (v_Amount = 0) THEN
		RETURN 0;
   	END IF;
	IF (p_PayDate IS NOT NULL) THEN
		v_PayDate := p_PayDate;
  	END IF;

    --  Valid Payment Schedule
    IF (v_IsPayScheduleValid='Y' AND p_C_InvoicePaySchedule_ID > 0) THEN
        SELECT COALESCE(MAX(DiscountAmt),0)
          INTO v_Amount
        FROM C_InvoicePaySchedule
        WHERE C_InvoicePaySchedule_ID=p_C_InvoicePaySchedule_ID
          AND DiscountDate <= v_PayDate;
        --
        RETURN v_Amount;
    END IF;

	--	return discount amount	
	RETURN paymentTermDiscount (v_Amount, 0, v_C_PaymentTerm_ID, v_DocDate, p_PayDate);

--	Most likely if invoice not found
EXCEPTION
	WHEN OTHERS THEN
		RETURN NULL;
END;

$body$ LANGUAGE plpgsql;

 	  	 


CREATE OR REPLACE FUNCTION invoiceOpen
(
	p_C_Invoice_ID	            IN	NUMERIC,
    	p_C_InvoicePaySchedule_ID   IN  NUMERIC
)
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 client currency
 * 	SELECT invoiceOpen (109, 11) FROM AD_System; - converted to default client currency
 * 	SELECT invoiceOpen (109, 102) FROM AD_System;
 * 	SELECT invoiceOpen (109, 103) FROM AD_System;
 ************************************************************************/
DECLARE
	v_Currency_ID		NUMERIC(10);
	v_TotalOpenAmt  	NUMERIC := 0;
	v_PaidAmt  	        NUMERIC := 0;
	v_Remaining	        NUMERIC := 0;
    	v_MultiplierAP      	NUMERIC := 0;
    	v_MultiplierCM      	NUMERIC := 0;
    	v_Temp              	NUMERIC := 0;
    	ar			RECORD;
    	s			RECORD;

BEGIN
	--	Get Currency
	BEGIN
		SELECT	MAX(C_Currency_ID), SUM(GrandTotal), MAX(MultiplierAP), MAX(Multiplier)
		INTO	v_Currency_ID, v_TotalOpenAmt, v_MultiplierAP, v_MultiplierCM
		FROM	C_Invoice_v		--	corrected for CM / Split Payment
		WHERE	C_Invoice_ID = p_C_Invoice_ID;
	EXCEPTION	--	Invoice in draft form
		WHEN OTHERS THEN
            	RAISE NOTICE 'InvoiceOpen - %', SQLERRM;
			RETURN NULL;
	END;

	--	Calculate Allocated Amount
	FOR ar IN 
		SELECT	a.AD_Client_ID, a.AD_Org_ID,
		al.Amount, al.DiscountAmt, al.WriteOffAmt,
		a.C_Currency_ID, a.DateTrx
		FROM	C_AllocationLine al
		INNER JOIN C_AllocationHdr a ON (al.C_AllocationHdr_ID=a.C_AllocationHdr_ID)
		WHERE	al.C_Invoice_ID = p_C_Invoice_ID
          	AND   a.IsActive='Y'
	LOOP
        v_Temp := ar.Amount + ar.DisCountAmt + ar.WriteOffAmt;
		v_PaidAmt := v_PaidAmt
        -- Allocation
			+ currencyConvert(v_Temp * v_MultiplierAP,
				ar.C_Currency_ID, v_Currency_ID, ar.DateTrx, null, ar.AD_Client_ID, ar.AD_Org_ID);
      	RAISE NOTICE '   PaidAmt=% , Allocation= % * %', v_PaidAmt, v_Temp, v_MultiplierAP;
	END LOOP;

    --  Do we have a Payment Schedule ?
    IF (p_C_InvoicePaySchedule_ID > 0) THEN --   if not valid = lists invoice amount
        v_Remaining := v_PaidAmt;
        FOR s IN 
        	SELECT  C_InvoicePaySchedule_ID, DueAmt
	        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 := (s.DueAmt*v_MultiplierCM) - v_Remaining;
                IF (s.DueAmt - v_Remaining < 0) THEN
                    v_TotalOpenAmt := 0;
                END IF;
            ELSE -- calculate amount, which can be allocated to next schedule
                v_Remaining := v_Remaining - s.DueAmt;
                IF (v_Remaining < 0) THEN
                    v_Remaining := 0;
                END IF;
            END IF;
        END LOOP;
    ELSE
        v_TotalOpenAmt := v_TotalOpenAmt - v_PaidAmt;
    END IF;
--  RAISE NOTICE '== Total=' || v_TotalOpenAmt;

	--	Ignore Rounding
	IF (v_TotalOpenAmt BETWEEN -0.00999 AND 0.00999) THEN
		v_TotalOpenAmt := 0;
	END IF;

	--	Round to penny
	v_TotalOpenAmt := ROUND(COALESCE(v_TotalOpenAmt,0), 2);
	RETURN	v_TotalOpenAmt;
END;

$body$ LANGUAGE plpgsql;





 	  	 


CREATE OR REPLACE FUNCTION invoicePaid
(
	p_C_Invoice_ID		NUMERIC,
	p_C_Currency_ID	    	NUMERIC,
	p_MultiplierAP		NUMERIC	-- DEFAULT 1
)
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 Paid/Allocated amount in Currency
 * Description:
 *	Add up total amount paid for for C_Invoice_ID.
 *  Split Payments are ignored.
 *  all allocation amounts  converted to invoice C_Currency_ID
 *	round it to the nearest cent
 *	and adjust for CreditMemos by using C_Invoice_v
 *  and for Payments with the multiplierAP (-1, 1)
 *
 *
 * Test:
    SELECT C_Invoice_ID, IsPaid, IsSOTrx, GrandTotal, 
    invoicePaid (C_Invoice_ID, C_Currency_ID, MultiplierAP)
    FROM C_Invoice_v;
 *	
 ************************************************************************/
DECLARE
	v_MultiplierAP		NUMERIC := 1;
	v_PaymentAmt		NUMERIC := 0;
	ar			RECORD;

BEGIN
	--	Default
	IF (p_MultiplierAP IS NOT NULL) THEN
		v_MultiplierAP := p_MultiplierAP;
	END IF;
	--	Calculate Allocated Amount
	FOR ar IN 
		SELECT	a.AD_Client_ID, a.AD_Org_ID, 
		al.Amount, al.DiscountAmt, al.WriteOffAmt, 
		a.C_Currency_ID, a.DateTrx
		FROM	C_AllocationLine al
		INNER JOIN C_AllocationHdr a ON (al.C_AllocationHdr_ID=a.C_AllocationHdr_ID)
		WHERE	al.C_Invoice_ID = p_C_Invoice_ID
		AND   a.IsActive='Y'
	LOOP
		v_PaymentAmt := v_PaymentAmt
			+ currencyConvert(ar.Amount + ar.DisCountAmt + ar.WriteOffAmt,
				ar.C_Currency_ID, p_C_Currency_ID, ar.DateTrx, null, ar.AD_Client_ID, ar.AD_Org_ID);
	END LOOP;
	--
	RETURN	ROUND(COALESCE(v_PaymentAmt,0), 2) * v_MultiplierAP;
END;

$body$ LANGUAGE plpgsql;
 	  	 


CREATE OR REPLACE FUNCTION nextid(
	p_AD_Sequence_ID 	IN 	INTEGER, 
	p_System 		IN 	VARCHAR,
	o_NextID		OUT	INTEGER
)
  RETURNS INTEGER 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-2005 Jorg Janke, ComPiere, Inc. All Rights Reserved.
 *
 * converted to postgreSQL by Karsten Thiemann (Schaeffer AG), 
 * kthiemann@adempiere.org
 *************************************************************************
 ***
 * Title:	Get Next ID - no Commit
 * Description: Returns the next id of the sequence.
 * Test:
 *	select * from nextid((select ad_sequence_id from ad_sequence where name = 'Test')::Integer, 'Y'::Varchar);
 * 
 ************************************************************************/

BEGIN
    IF (p_System = 'Y') THEN
	RAISE NOTICE 'system';
        SELECT CurrentNextSys
            INTO o_NextID
        FROM AD_Sequence
        WHERE AD_Sequence_ID=p_AD_Sequence_ID;
        --
        UPDATE AD_Sequence
          SET CurrentNextSys = CurrentNextSys + IncrementNo
        WHERE AD_Sequence_ID=p_AD_Sequence_ID;
    ELSE
        SELECT CurrentNext
            INTO o_NextID
        FROM AD_Sequence
        WHERE AD_Sequence_ID=p_AD_Sequence_ID;
        --
        UPDATE AD_Sequence
          SET CurrentNext = CurrentNext + IncrementNo
        WHERE AD_Sequence_ID=p_AD_Sequence_ID;
    END IF;
    --
EXCEPTION
    WHEN  OTHERS THEN
    	RAISE NOTICE '%',SQLERRM;
END;

$body$ LANGUAGE plpgsql;


 	  	 
CREATE OR REPLACE FUNCTION nextidfunc(
	p_AD_Sequence_ID 	IN 	INTEGER, 
	p_System 		IN 	VARCHAR
)
  RETURNS INTEGER AS $body$
DECLARE
          o_NextIDFunc INTEGER;
	  dummy INTEGER;
BEGIN
    o_NextIDFunc := nextid(p_AD_Sequence_ID, p_System);
    RETURN o_NextIDFunc;
END;
$body$ LANGUAGE plpgsql;


create or replace FUNCTION paymentAllocated
(
	p_C_Payment_ID	IN	NUMERIC,
	p_C_Currency_ID	IN	NUMERIC
)
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 Allocated Payment Amount in Payment Currency
 * Description:
    --
    SELECT paymentAllocated(C_Payment_ID,C_Currency_ID), PayAmt, IsAllocated
    FROM C_Payment_v 
    WHERE C_Payment_ID<1000000;
    --
    UPDATE C_Payment_v 
    SET IsAllocated=CASE WHEN paymentAllocated(C_Payment_ID, C_Currency_ID)=PayAmt THEN 'Y' ELSE 'N' END
    WHERE C_Payment_ID>=1000000;
 
 ************************************************************************/
DECLARE
	v_AllocatedAmt		NUMERIC := 0;
    	v_PayAmt        	NUMERIC;
    	r   			RECORD;
BEGIN
    --  Charge - nothing available
    SELECT 
      INTO v_PayAmt MAX(PayAmt) 
    FROM C_Payment 
    WHERE C_Payment_ID=p_C_Payment_ID AND C_Charge_ID > 0;
    
    IF (v_PayAmt IS NOT NULL) THEN
        RETURN v_PayAmt;
    END IF;
    
	--	Calculate Allocated Amount
	FOR r IN
		SELECT	a.AD_Client_ID, a.AD_Org_ID, al.Amount, a.C_Currency_ID, a.DateTrx
			FROM	C_AllocationLine al
	          INNER JOIN C_AllocationHdr a ON (al.C_AllocationHdr_ID=a.C_AllocationHdr_ID)
			WHERE	al.C_Payment_ID = p_C_Payment_ID
          	AND   a.IsActive='Y'
	LOOP
		v_AllocatedAmt := v_AllocatedAmt
			+ currencyConvert(r.Amount, r.C_Currency_ID, p_C_Currency_ID, r.DateTrx, null, r.AD_Client_ID, r.AD_Org_ID);
	END LOOP;
	--	Round to penny
	v_AllocatedAmt := ROUND(COALESCE(v_AllocatedAmt,0), 2);
	RETURN	v_AllocatedAmt;
END;

$body$ LANGUAGE plpgsql;

 	  	 


create or replace FUNCTION  paymentAvailable
(
	p_C_Payment_ID	IN	NUMERIC
)
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 Available Payment Amount in Payment Currency
 * Description:
 *		similar to C_Invoice_Open
 ************************************************************************/
DECLARE
	v_Currency_ID		NUMERIC(10);
	v_AvailableAmt		NUMERIC := 0;
    	v_IsReceipt         	C_Payment.IsReceipt%TYPE;
    	v_Amt               	NUMERIC := 0;
    	r   			RECORD;

BEGIN
    --  Charge - fully allocated
    SELECT MAX(PayAmt) 
      INTO v_Amt
    FROM C_Payment 
    WHERE C_Payment_ID=p_C_Payment_ID AND C_Charge_ID > 0;
    IF (v_Amt IS NOT NULL) THEN
        RETURN 0;
    END IF;

	--	Get Currency
	SELECT	C_Currency_ID, PayAmt, IsReceipt
	  INTO	v_Currency_ID, v_AvailableAmt, v_IsReceipt
	FROM	C_Payment_v     -- corrected for AP/AR
	WHERE	C_Payment_ID = p_C_Payment_ID;
--  DBMS_OUTPUT.PUT_LINE('== C_Payment_ID=' || p_C_Payment_ID || ', PayAmt=' || v_AvailableAmt || ', Receipt=' || v_IsReceipt);

	--	Calculate Allocated Amount
	FOR r IN
		SELECT	a.AD_Client_ID, a.AD_Org_ID, al.Amount, a.C_Currency_ID, a.DateTrx
		FROM	C_AllocationLine al
	        INNER JOIN C_AllocationHdr a ON (al.C_AllocationHdr_ID=a.C_AllocationHdr_ID)
		WHERE	al.C_Payment_ID = p_C_Payment_ID
          	AND   a.IsActive='Y'
	LOOP
        v_Amt := currencyConvert(r.Amount, r.C_Currency_ID, v_Currency_ID, r.DateTrx, null, r.AD_Client_ID, r.AD_Org_ID);
	    v_AvailableAmt := v_AvailableAmt - v_Amt;
--      DBMS_OUTPUT.PUT_LINE('  Allocation=' || a.Amount || ' - Available=' || v_AvailableAmt);
	END LOOP;
	--	Ignore Rounding
	IF (v_AvailableAmt BETWEEN -0.00999 AND 0.00999) THEN
		v_AvailableAmt := 0;
	END IF;
	--	Round to penny
	v_AvailableAmt := ROUND(COALESCE(v_AvailableAmt,0), 2);
	RETURN	v_AvailableAmt;
END;

$body$ LANGUAGE plpgsql;


create or replace FUNCTION  paymenttermDiscount
(
	Amount			NUMERIC,
    	Currency_ID     	NUMERIC,
	PaymentTerm_ID		NUMERIC,
	DocDate			timestamp with time zone,
	PayDate			timestamp with time zone
)
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 Discount
 * Description:
 *	Calculate the allowable Discount Amount of the Payment Term
 *
 *	Test:	SELECT paymenttermDiscount(110, 103, 106, now(), now()) FROM TEST; => 2.20
 ************************************************************************/

DECLARE
	Discount		NUMERIC := 0;
	Discount1Date		timestamp with time zone;
	Discount2Date		timestamp with time zone;
	Add1Date		NUMERIC := 0;
	Add2Date		NUMERIC := 0;
	p   			RECORD;
BEGIN
	--	No Data - No Discount
	IF (Amount IS NULL OR PaymentTerm_ID IS NULL OR DocDate IS NULL) THEN
		RETURN 0;
	END IF;

	FOR p IN 
		SELECT	*
		FROM	C_PaymentTerm
		WHERE	C_PaymentTerm_ID = PaymentTerm_ID
	LOOP	--	for convineance only
		Discount1Date := TRUNC(DocDate + p.DiscountDays + p.GraceDays);
		Discount2Date := TRUNC(DocDate + p.DiscountDays2 + p.GraceDays);

		--	Next Business Day
		IF (p.IsNextBusinessDay='Y') THEN
			Discount1Date := nextBusinessDay(Discount1Date, p.AD_Client_ID);
			Discount2Date := nextBusinessDay(Discount2Date, p.AD_Client_ID);
		END IF;

		--	Discount 1
		IF (Discount1Date >= TRUNC(PayDate)) THEN
			Discount := Amount * p.Discount / 100;
		--	Discount 2
		ELSIF (Discount2Date >= TRUNC(PayDate)) THEN
			Discount := Amount * p.Discount2 / 100;
		END IF;	
	END LOOP;
	--
    RETURN ROUND(COALESCE(Discount,0), 2);	--	fixed rounding
END;

$body$ LANGUAGE plpgsql;
 	  	 


create or replace FUNCTION    paymenttermDueDate
(
	PaymentTerm_ID	IN	NUMERIC,
	DocDate			IN	timestamp with time zone
)
RETURNS timestamp with time zone 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:	Get Due timestamp with time zone
 * Description:
 *	Returns the due timestamp with time zone
 * Test:
 *	select paymenttermDueDate(106, now()) from Test; => now()+30 days
 ************************************************************************/
DECLARE
 	Days				NUMERIC := 0;
	DueDate				timestamp with time zone := TRUNC(DocDate);
	--
	FirstDay			timestamp with time zone;
	NoDays				NUMERIC;
	p   			RECORD;
BEGIN
	FOR p IN 
		SELECT	*
		FROM	C_PaymentTerm
		WHERE	C_PaymentTerm_ID = PaymentTerm_ID
	LOOP	--	for convineance only
		--	Due 15th of following month
		IF (p.IsDueFixed = 'Y') THEN		
			FirstDay := TRUNC(DocDate, 'MM');
			NoDays := TRUNC(DocDate) - FirstDay;
			DueDate := FirstDay + (p.FixMonthDay-1);	--	starting on 1st
			DueDate := ADD_MONTHS(DueDate, p.FixMonthOffset);
			IF (NoDays > p.FixMonthCutoff) THEN
				DueDate := ADD_MONTHS(DueDate, 1);
			END IF;
		ELSE
			DueDate := TRUNC(DocDate) + p.NetDays;
		END IF;
	END LOOP;

	RETURN DueDate;
END;

$body$ LANGUAGE plpgsql;

 	  	 


create or replace FUNCTION   paymenttermDueDays 
(
	PaymentTerm_ID	IN	NUMERIC,
	DocDate			IN	timestamp with time zone,
	PayDate			IN	timestamp with time zone
)
RETURNS INTEGER 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:	Get Due Days
 * Description:
 *	Returns the days due (positive) or the days till due (negative)
 *	Grace days are not considered!
 *	If record is not found it assumes due immediately
 *
 *	Test:	SELECT paymenttermDueDays(103, now(), now());
 *
 * Contributor(s): Carlos Ruiz - globalqss - match with SQLJ version
 ************************************************************************/
DECLARE
 	Days			NUMERIC := 0;
	DueDate			timestamp with time zone := NULL;
	calDueDate		timestamp with time zone;
	FixMonthOffset		C_PaymentTerm.FixMonthOffset%TYPE;
	MaxDayCut		NUMERIC;
	MaxDay			NUMERIC;
	v_PayDate		timestamp with time zone;
	p   			RECORD;
	--
	FirstDay			timestamp with time zone;
	NoDays				NUMERIC;
BEGIN

    	IF PaymentTerm_ID = 0 OR DocDate IS NULL THEN
	    RETURN 0;
	END IF;

    	v_PayDate := PayDate;
	IF v_PayDate IS NULL THEN
	    v_PayDate := TRUNC(now());
	END IF;

	FOR p IN 
		SELECT	*
		FROM	C_PaymentTerm
		WHERE	C_PaymentTerm_ID = PaymentTerm_ID
	LOOP	--	for convineance only

		--	Due 15th of following month
		IF (p.IsDueFixed = 'Y') THEN
			FirstDay := TRUNC(DocDate, 'MM');
			NoDays := extract (day from (TRUNC(DocDate) - FirstDay));
			DueDate := FirstDay + (p.FixMonthDay-1);	--	starting on 1st
			DueDate := DueDate + (p.FixMonthOffset || ' month')::interval;
			
			IF (NoDays > p.FixMonthCutoff) THEN
				DueDate := DueDate + '1 month'::interval;
			END IF;
			-- raise notice 'FirstDay: %, NoDays: %, DueDate: %', FirstDay, NoDays, DueDate;
			
			calDueDate := TRUNC(DocDate);
			MaxDayCut := extract (day from (cast(date_trunc('month', calDueDate) + '1 month'::interval as date) - 1));
			-- raise notice 'last day(MaxDayCut): %' , MaxDayCut;

			IF p.FixMonthCutoff > MaxDayCut THEN
				-- raise notice 'p.FixMonthCutoff > MaxDayCut';
			    calDueDate := cast(date_trunc('month', TRUNC(calDueDate)) + '1 month'::interval as date) - 1;
				-- raise notice 'last day(calDueDate): %' , calDueDate;
			ELSE
			    -- set day fixmonthcutoff on duedate
			    calDueDate := TRUNC(calDueDate, 'MM') + (((p.FixMonthCutoff-1)|| ' days')::interval);
			    -- raise notice 'calDueDate: %' , calDueDate;
			    
			END IF;
			FixMonthOffset := p.FixMonthOffset;
			IF DocDate > calDueDate THEN
			    FixMonthOffset := FixMonthOffset + 1;
				raise notice 'FixMonthOffset: %' , FixMonthOffset;
			END IF;

			calDueDate := calDueDate + (FixMonthOffset || ' month')::interval;
			-- raise notice 'calDueDate: %' , calDueDate;

			MaxDay := extract (day from (cast(date_trunc('month', calDueDate) + '1 month'::interval as date) - 1));


			IF    (p.FixMonthDay > MaxDay)    --	32 -> 28
			   OR (p.FixMonthDay >= 30 AND MaxDay > p.FixMonthDay) THEN  	--	30 -> 31
				calDueDate := TRUNC(calDueDate, 'MM') + (((MaxDay-1)|| ' days')::interval);
				-- raise notice 'calDueDate: %' , calDueDate;			
			ELSE
				calDueDate := TRUNC(calDueDate, 'MM') + (((p.FixMonthDay-1)|| ' days')::interval);
				-- raise notice 'calDueDate: %' , calDueDate;			
			END IF;
			DueDate := calDueDate; 

		ELSE
			DueDate := TRUNC(DocDate) + p.NetDays;
		END IF;
	END LOOP;

    IF DueDate IS NULL THEN
	    RETURN 0;
	END IF;


	Days := EXTRACT(day from (TRUNC(v_PayDate) - DueDate));
	RETURN Days;
END;

$body$ LANGUAGE plpgsql;


 	  	 


CREATE OR REPLACE FUNCTION      ProductAttribute 
(
    p_M_AttributeSetInstance_ID     NUMERIC
)
RETURNS VARCHAR 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: Return Instance Attribute Info
 * Description:
 *  
 * Test:
    SELECT ProductAttribute (M_AttributeSetInstance_ID) 
    FROM M_InOutLine WHERE M_AttributeSetInstance_ID > 0
    --
    SELECT p.Name
    FROM C_InvoiceLine il LEFT OUTER JOIN M_Product p ON (il.M_Product_ID=p.M_Product_ID);
    SELECT p.Name || ProductAttribute (il.M_AttributeSetInstance_ID) 
    FROM C_InvoiceLine il LEFT OUTER JOIN M_Product p ON (il.M_Product_ID=p.M_Product_ID);
    
 ************************************************************************/

	
DECLARE

    v_Name          VARCHAR(2000) := '';
    v_NameAdd       VARCHAR(2000) := '';
    --
    v_Lot           M_AttributeSetInstance.Lot%TYPE;
    v_LotStart      M_AttributeSet.LotCharSOverwrite%TYPE;
    v_LotEnd        M_AttributeSet.LotCharEOverwrite%TYPE;
    v_SerNo         M_AttributeSetInstance.SerNo%TYPE;
    v_SerNoStart    M_AttributeSet.SerNoCharSOverwrite%TYPE;
    v_SerNoEnd      M_AttributeSet.SerNoCharEOverwrite%TYPE;
    v_GuaranteeDate M_AttributeSetInstance.GuaranteeDate%TYPE;
    
    r   RECORD;
    --

BEGIN
    --  Get Product Attribute Set Instance
    IF (p_M_AttributeSetInstance_ID > 0) THEN
        SELECT asi.Lot, asi.SerNo, asi.GuaranteeDate,
            COALESCE(a.SerNoCharSOverwrite, '#'::CHAR(1)), COALESCE(a.SerNoCharEOverwrite, ''::CHAR(1)),
            COALESCE(a.LotCharSOverwrite, '«'::CHAR(1)), COALESCE(a.LotCharEOverwrite, '»'::CHAR(1))
          INTO v_Lot, v_SerNo, v_GuaranteeDate,
            v_SerNoStart, v_SerNoEnd, v_LotStart, v_LotEnd
        FROM M_AttributeSetInstance asi
          INNER JOIN M_AttributeSet a ON (asi.M_AttributeSet_ID=a.M_AttributeSet_ID)
        WHERE asi.M_AttributeSetInstance_ID=p_M_AttributeSetInstance_ID;
        --
        IF (v_SerNo IS NOT NULL) THEN
            v_NameAdd := v_NameAdd || v_SerNoStart || v_SerNo || v_SerNoEnd || ' ';
        END IF;
        IF (v_Lot IS NOT NULL) THEN
            v_NameAdd := v_NameAdd || v_LotStart || v_Lot || v_LotEnd || ' ';
        END IF;
        IF (v_GuaranteeDate IS NOT NULL) THEN
            v_NameAdd := v_NameAdd || v_GuaranteeDate || ' ';
        END IF;
        --
        
        FOR r IN
	     SELECT ai.Value, a.Name
	        FROM M_AttributeInstance ai
	        INNER JOIN M_Attribute a ON (ai.M_Attribute_ID=a.M_Attribute_ID AND a.IsInstanceAttribute='Y')
        	WHERE ai.M_AttributeSetInstance_ID=p_M_AttributeSetInstance_ID
    	LOOP
            v_NameAdd := v_NameAdd || r.Name || ':' || r.Value || ' ';
        END LOOP;
        --
        IF (LENGTH(v_NameAdd) > 0) THEN
            v_Name := v_Name || ' (' || TRIM(v_NameAdd) || ')';
	ELSE 
	    v_Name := NULL;
        END IF;
    END IF;
    RETURN v_Name;
END;

$body$ LANGUAGE plpgsql;
 	  	 


CREATE OR REPLACE FUNCTION currencyround (in p_amount numeric, in p_curto_id numeric, in p_costing varchar) 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:	Round amount for Traget Currency
 * Description:
 *		Round Amount using Costing or Standard Precision
 *		Returns unmodified amount if currency not found
 * Test:
 *		SELECT currencyRound(currencyConvert(100,116,100,null,null),100,null) FROM AD_System => 64.72 
 ************************************************************************/
	
	
DECLARE
	v_StdPrecision		NUMERIC;
	v_CostPrecision		NUMERIC;

BEGIN
	--	Nothing to convert
	IF (p_Amount IS NULL OR p_CurTo_ID IS NULL) THEN
		RETURN p_Amount;
	END IF;

	--	Ger Precision
	SELECT	MAX(StdPrecision), MAX(CostingPrecision)
	  INTO	v_StdPrecision, v_CostPrecision
	FROM	C_Currency
	  WHERE	C_Currency_ID = p_CurTo_ID;
	--	Currency Not Found
	IF (v_StdPrecision IS NULL) THEN
		RETURN p_Amount;
	END IF;

	IF (p_Costing = 'Y') THEN
		RETURN ROUND (p_Amount, v_CostPrecision);
	END IF;

	RETURN ROUND (p_Amount, v_StdPrecision);
	
END;

$BODY$
LANGUAGE 'plpgsql'
;



CREATE OR REPLACE FUNCTION round (
 IN NUMERIC, -- $1 numeric
 IN NUMERIC -- $2 numeric
) RETURNS NUMERIC AS
$$
 BEGIN
	RETURN ROUND($1, cast($2 as integer));
 END;
$$ LANGUAGE plpgsql;



CREATE OR REPLACE FUNCTION currencyRound(
	p_Amount	NUMERIC,
	p_CurTo_ID	NUMERIC,
	p_Costing	VARCHAR		--	Default 'N'
) 

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:	Round amount for Traget Currency
 * Description:
 *		Round Amount using Costing or Standard Precision
 *		Returns unmodified amount if currency not found
 * Test:
 *		SELECT currencyRound(currencyConvert(100,116,100,null,null),100,null) FROM AD_System => 64.72 
 ************************************************************************/
	
	
DECLARE
	v_StdPrecision		NUMERIC;
	v_CostPrecision		NUMERIC;

BEGIN
	--	Nothing to convert
	IF (p_Amount IS NULL OR p_CurTo_ID IS NULL) THEN
		RETURN p_Amount;
	END IF;

	--	Ger Precision
	SELECT	MAX(StdPrecision), MAX(CostingPrecision)
	  INTO	v_StdPrecision, v_CostPrecision
	FROM	C_Currency
	  WHERE	C_Currency_ID = p_CurTo_ID;
	--	Currency Not Found
	IF (v_StdPrecision IS NULL) THEN
		RETURN p_Amount;
	END IF;

	IF (p_Costing = 'Y') THEN
		RETURN ROUND (p_Amount, v_CostPrecision);
	END IF;

	RETURN ROUND (p_Amount, v_StdPrecision);
	
END;

$body$ LANGUAGE plpgsql;	 

Atc.,
Luiz Gonzaga