Hi List,
nice to have that forum. I am a Business Objects user, but that should almost be the same as of today.
Here is my solution for the UOM conversion and other usefull stuff. Basically the idea is to have stored procedures within the database (Oracle) to do the job.
-------------------------------------------------------------------
create or replace package bo_package
AS
function convert_julien(Arg1 NUMBER)
RETURN DATE;
PRAGMA RESTRICT_REFERENCES (convert_julien, WNDS);
FUNCTION convert_qty(Arg1 NUMBER, Arg2 VARCHAR2, Arg3 VARCHAR2, Arg4 VARCHAR2)
RETURN NUMBER;
PRAGMA RESTRICT_REFERENCES (convert_qty, WNDS);
FUNCTION convert_qty_MCU(itm number, mcu varchar2, um varchar2,rum varchar2, qty number)
RETURN NUMBER;
PRAGMA RESTRICT_REFERENCES (convert_qty_MCU, WNDS);
FUNCTION getUDCDesc(sy VARCHAR2, rt VARCHAR2, ky VARCHAR2, lang VARCHAR2)
RETURN VARCHAR2;
PRAGMA RESTRICT_REFERENCES (getUDCDesc, WNDS);
END bo_package;
/
create or replace package body bo_package
AS
function convert_julien(Arg1 number)
RETURN date IS
BEGIN
DECLARE
retourdate date;
BEGIN
-- Date captured at zero
If substr(to_char(arg1),1,1) = 0 then
retourdate:=to_date('');
Else
retourdate:=to_date(1900000 + arg1,'YYYYDDD');
End if;
RETURN retourdate;
END;
END convert_julien;
FUNCTION convert_qty(Arg1 number, Arg2 varchar2,Arg3 varchar2, Arg4 varchar2)
RETURN number IS
BEGIN
DECLARE
retourconversion number;
BEGIN
select decode(a.umum||a.umrum, arg2||arg4, a.umconv/10000000, arg4||arg2, 10000000/a.umconv)
* decode(b.umum||b.umrum, arg3||arg4, 10000000 /b.umconv, arg4||arg3, b.umconv/10000000)
into retourconversion
from prodDTA.F41002 A,
prodDTA.F41002 b
where a.umitm=Arg1 and (b.umitm=arg1 and a.umitm=b.umitm)
and ((a.umum= arg2 and a.umrum=arg4)
or (a.umum= arg4 and a.umrum=arg2))
and ((b.umum= arg3 and b.umrum=arg4)
or (b.umum= arg4 and b.umrum=arg3))
and (a.umum = b.umum or a.umrum= b.umum or a.umum =b.umrum or a.umrum=b.umrum);
RETURN retourconversion;
EXCEPTION
WHEN NO_DATA_FOUND then
if arg2=arg3 then
retourconversion:= 1;
RETURN RETOURCONVERSION;
else
select decode(b.umum||b.umrum, arg3||arg4, 10000000 /b.umconv, arg4||arg3, b.umconv/10000000)
into retourconversion
from prodDTA.F41002 b
where (b.umitm=arg1 )
and ((b.umum= arg3 and b.umrum=arg4)
or (b.umum= arg4 and b.umrum=arg3));
RETURN RETOURCONVERSION;
end if;
END;
END convert_qty;
FUNCTION convert_qty_MCU(itm number, mcu varchar2, um varchar2,rum varchar2, qty number)
RETURN number IS
BEGIN
DECLARE
convQty number;
BEGIN
if um = rum then
convQty:=qty;
return convQty;
else
select decode(umum||umrum, um||rum, umconv/10000000, rum||um, 10000000/umconv)
into convQty
from PRODDTA.F41002
where umitm=itm and ummcu=mcu
and ((umum=um and umrum=rum) or (umum=rum and umrum=um));
convQty := convQty * qty;
RETURN convQty;
end if;
END;
END convert_qty_MCU;
function getUDCDesc(sy VARCHAR2, rt VARCHAR2, ky VARCHAR2, lang VARCHAR2)
RETURN VARCHAR2 IS
description VARCHAR(30);
BEGIN
select drdl01
into description
from prodctl.f0005D
where drsy=sy and drrt=rt and drlngp=lang and drky=lpad(ky,10);
RETURN description;
EXCEPTION
WHEN NO_DATA_FOUND then
description:=' ';
if description = ' ' then
select drdl01
into description
from prodctl.f0005
where drsy=sy and drrt=rt and drky=lpad(ky,10);
RETURN description;
end if;
END getUDCDesc;
END bo_package;
/