Unit of Measure Conversion formula for EnterpriseOne

rscholten

Member
I'm building reports for a company that is implementing PeopleSoft EnterpriseOne. We're using Crystal Reports and Enterprise 10. Right now i'm stuck at the unit of measure conversion needed in a couple of reports. Most likely this is done before by many people. Who can help me with this formula in Crystal Reports?

nb: proud to start the first discussion in this thread
cool.gif
. Let me know if i can be of any help with Crystal
 
At this moment i've got the conversion written in JAVA. But now i have to put it into a Custom Function in Crystal. I understand of course that nobody will donate the UoM conversion to me, but can someone give me some advice on how to approach it or point me in a certain direction?
 
Don't ask me specifics on UOM conversion since that hasn't been an issue for us.

As far as creating a custom function in Crystal basically what you need to do is create a COM object / DLL whose name is prefixed by CRUFL.
Attached is Business Objects instructions on how to do this. Supposedly these instructions were still current as of version 9.

Good Luck,
 

Attachments

  • 79616-scr_user_defined_functions.pdf
    176.7 KB · Views: 997
Thank you Larry.
Visual Basic is not really my thing but after reading your enclosed document it seems the best solution for the conversion function. I'll give it a shot.
 
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;
/
 
Thanxs Michel. These are the kind of posts that are really useful.
But I notice you don't use the F41003 table for an Item UoM. Don't you look at the Item UoM conversion when using Business Objects?
 
Back
Top