Compiere ERP has following decleration as ORACLE;

How can I convert this to FB?



Is it possible to write an UDF for achieving to write a TEXT line inside Firebird. For Example,

"DBMS_OUTPUT.PUT_LINE('Updating PInstance - Finished');"





CREATE OR REPLACE PROCEDURE M_Product_BOM_Check

(

PInstance_ID IN NUMBER

)

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









* Copyright (C) 1999-2001 Jorg Janke, ComPiere, Inc. All Rights Reserved.

*************************************************************************

* $Id: DatabaseBuild.sql,v 1.96 2003/12/01 03:51:22 jjanke Exp $

***

* Title: Check BOM Structure (free of cycles)

* Description:

* Tree cannot contain BOMs which are already referenced

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

AS

-- Logistice

ResultStr VARCHAR2(2000);

Message VARCHAR2(2000);

Record_ID NUMBER;

-- Parameter

CURSOR Cur_Parameter (PInstance NUMBER) IS

SELECT i.Record_ID, p.ParameterName, p.P_String, p.P_Number, p.P_Date

FROM AD_PInstance i, AD_PInstance_Para p

WHERE i.AD_PInstance_ID=PInstance

AND i.AD_PInstance_ID=p.AD_PInstance_ID(+)

ORDER BY p.SeqNo;

-- Variables

Verified CHAR(1) := 'Y';

IsBOM CHAR(1);

CountNo NUMBER;



BEGIN

-- Update AD_PInstance

DBMS_OUTPUT.PUT_LINE('Updating PInstance - Processing ' || PInstance_ID);

ResultStr := 'PInstanceNotFound';

UPDATE AD_PInstance

SET Created = SysDate,

IsProcessing = 'Y'

WHERE AD_PInstance_ID=PInstance_ID;

COMMIT;



-- Get Parameters

ResultStr := 'ReadingParameters';

FOR p IN Cur_Parameter (PInstance_ID) LOOP

Record_ID := p.Record_ID;

END LOOP; -- Get Parameter

DBMS_OUTPUT.PUT_LINE(' Record_ID=' || Record_ID);



-- Record ID is M_Product_ID of product to be tested

SELECT IsBOM

INTO IsBOM

FROM M_Product

WHERE M_Product_ID=Record_ID;



-- No BOM - should not happen, but no problem

IF (IsBOM = 'N') THEN

GOTO FINISH_PROCESS;

-- Did not find product

ELSIF (IsBOM <> 'Y') THEN

RETURN;

END IF;



-- Checking BOM Structure

ResultStr := 'InsertingRoot';

-- Table to put all BOMs - duplicate will cause exception

DELETE FROM T_Selection2 WHERE Query_ID = 0;

INSERT INTO T_Selection2 (Query_ID, T_Selection_ID) VALUES (0, Record_ID);

-- Table of root modes

DELETE FROM T_Selection;

INSERT INTO T_Selection (T_Selection_ID) VALUES (Record_ID);



LOOP

-- How many do we have?

SELECT COUNT(*)

INTO CountNo

FROM T_Selection;

-- Nothing to do

EXIT WHEN (CountNo = 0);



-- Insert BOM Nodes into "All" table

INSERT INTO T_Selection2 (Query_ID, T_Selection_ID)

SELECT 0, p.M_Product_ID

FROM M_Product p

WHERE IsBOM='Y'

AND EXISTS (SELECT * FROM M_Product_BOM b WHERE p.M_Product_ID=b.M_ProductBOM_ID

AND b.M_Product_ID IN (SELECT T_Selection_ID FROM T_Selection));



-- Insert BOM Nodes into temporary table

DELETE FROM T_Selection2 WHERE Query_ID = 1;

INSERT INTO T_Selection2 (Query_ID, T_Selection_ID)

SELECT 1, p.M_Product_ID

FROM M_Product p

WHERE IsBOM='Y'

AND EXISTS (SELECT * FROM M_Product_BOM b WHERE p.M_Product_ID=b.M_ProductBOM_ID

AND b.M_Product_ID IN (SELECT T_Selection_ID FROM T_Selection));



-- Copy into root table

DELETE FROM T_Selection;

INSERT INTO T_Selection (T_Selection_ID)

SELECT T_Selection_ID

FROM T_Selection2

WHERE Query_ID = 1;



END LOOP;



<<FINISH_PROCESS>>

-- OK

Message := 'OK';

UPDATE M_Product

SET IsVerified = 'Y'

WHERE M_Product_ID=Record_ID;



-- Update AD_PInstance

DBMS_OUTPUT.PUT_LINE('Updating PInstance - Finished ' || Message);

UPDATE AD_PInstance

SET Updated = SysDate,

IsProcessing = 'N',

Result = 1, -- success

ErrorMsg = Message

WHERE AD_PInstance_ID=PInstance_ID;

COMMIT;

RETURN;



EXCEPTION

WHEN OTHERS THEN

ResultStr := ResultStr || ': ' || SQLERRM || ' - ' || Message;

DBMS_OUTPUT.PUT_LINE(ResultStr);

UPDATE AD_PInstance

SET Updated = SysDate,

IsProcessing = 'N',

Result = 0, -- failure

ErrorMsg = ResultStr

WHERE AD_PInstance_ID=PInstance_ID;

COMMIT;

--

UPDATE M_Product

SET IsVerified = 'N'

WHERE M_Product_ID=Record_ID;

COMMIT;

--

RETURN;



END M_Product_BOM_Check;

/

[Non-text portions of this message have been removed]