Just now written a function in oracle which is used for JD Edwards Unit of Measure Conversion. This function convert a value from one unit of measure to another. This is based on table F41002 & F41003. I'm doing this by converting from unit of measure [FromUOM] to Primary [PrimaryUOM] and later to unit of measure [ToUOM].
Following is the oracle function which is written and working properly. Do share if you had better / shorter code than this.
CREATE OR REPLACE FUNCTION FN_GET_CONVERSION(nItem NUMBER, sFromUOM VARCHAR2, sToUOM VARCHAR2, sProb VARCHAR2)
RETURN NUMBER
IS
nPrimaryUnits NUMBER;
nToUnits NUMBER :=0;
nProbabilities NUMBER;
fExit VARCHAR2(1) := 'N';
sINFromUOM VARCHAR2(4);
sINToUOM VARCHAR2(4);
sINProb VARCHAR2(1);
sSQL Long;
nCFCONV NUMBER;
nCFCNV1 NUMBER;
nCFRCONV NUMBER;
nCTCONV NUMBER;
nCTCNV1 NUMBER;
nCTRCONV NUMBER;
--------------------------------------------------------------------------------------------------------------------------------
-- Main Processing
--------------------------------------------------------------------------------------------------------------------------------
BEGIN
--------------------------------------------------------------------------------------------------------------------------------
sINFromUOM := UPPER(sFromUOM);
sINToUOM := UPPER(sToUOM);
sINProb := UPPER(sProb);
-- If From and To UOM are same then return with 1
IF sINFromUOM = sINToUOM THEN
IF sProb = 'Y' THEN
RETURN 901;
ELSE
nProbabilities := 0;
RETURN nProbabilities;
END IF;
END IF;
-- Following is the Simple Query which will match exact data in from and to column in table F41002.
FOR cfF41002 IN
(
SELECT UMUM, UMRUM,
CASE WHEN UMCONV <> 0 THEN ROUND(UMCONV/10000000,9) ELSE 0.000000001 END CONV,
CASE WHEN UMCNV1 <> 0 THEN ROUND(UMCNV1/10000000,9) ELSE 0.000000001 END CNV1,
CASE WHEN UMCONV <> 0 THEN ROUND((1/(UMCONV/10000000)),9) ELSE 0.000000001 END RCNV1
FROM TNBIDVDT.F41002
WHERE UMITM = nItem AND
((UMUM = sINFromUOM AND UMRUM = sINToUOM) OR (UMUM = sINToUOM AND UMRUM = sINFromUOM)) AND ROWNUM=1
)
LOOP
-- Following lines will be useful during debuging of code using SET SERVEROUTPUT ON
-- DBMS_OUTPUT.PUT_LINE('FUM (FROM1)-' || cfF41002.UMUM || ' FRUM (TO1)-' || cfF41002.UMRUM);
-- DBMS_OUTPUT.PUT_LINE('FCONV-' || cfF41002.CONV || ' FCNV1-' || cfF41002.CNV1 || ' FRCONV-' || cfF41002.RCNV1 );
-- DBMS_OUTPUT.PUT_LINE(' Fm-' || sINFromUOM || ' To-' || sINToUOM );
IF cfF41002.UMUM = sINFromUOM AND cfF41002.UMRUM = sINToUOM THEN
IF sProb = 'Y' THEN
nProbabilities := 1;
RETURN nProbabilities;
ELSE
RETURN cfF41002.CONV;
END IF;
ELSE
IF sProb = 'Y' THEN
nProbabilities := 2;
RETURN nProbabilities;
ELSE
RETURN cfF41002.RCNV1;
END IF;
END IF;
END LOOP;
-- Following is little complex one which will get data from table F41002
FOR cfF41002 IN
(
SELECT
TO_CHAR(I.IMUOM1) IMUOM1, F.UMUM FUM, F.UMRUM FRUM, ROUND(F.UMCONV/10000000,9) FCONV, ROUND(F.UMCNV1/10000000,9) FCNV1,
ROUND((1/F.UMCONV)*10000000,9) FRCONV,
TO_CHAR(T.UMUM) TUM, TO_CHAR(T.UMRUM) TRUM, ROUND(T.UMCONV/10000000,9) TCONV, ROUND(T.UMCNV1/10000000,9) TCNV1, ROUND((1/T.UMCONV)*10000000,9) TRCONV
FROM TNBIDVDT.F41002 F, TNBIDVDT.F41002 T, TNBIDVDT.F4101 I
WHERE F.UMITM = I.IMITM AND T.UMITM=I.IMITM AND T.UMITM = F.UMITM
AND (F.UMCONV <> 0 AND F.UMCNV1 <> 0 AND T.UMCONV <> 0 AND T.UMCNV1 <> 0)
AND F.UMITM = nItem
AND sINFromUOM IN (F.UMUM, F.UMRUM) -- OR sINToUOM IN (F.UMUM, F.UMRUM))
AND sINToUOM IN (T.UMUM, T.UMRUM) AND ROWNUM=1
)
LOOP
-- Following lines will be useful during debuging of code using SET SERVEROUTPUT ON
-- DBMS_OUTPUT.PUT_LINE('FUM (FROM1)-' || cfF41002.FUM || ' FRUM (TO1)-' || cfF41002.FRUM || ' TUM-FROM2' || cfF41002.TUM || ' TRUM-TO2' || cfF41002.TRUM);
-- DBMS_OUTPUT.PUT_LINE('FCONV-' || cfF41002.FCONV || ' FCNV1-' || cfF41002.FCNV1 || ' FRCONV-' || cfF41002.FRCONV || ' TCONV-' || cfF41002.TCONV || ' TCNV1-' || cfF41002.TCNV1 || ' TRCONV-' || cfF41002.TRCONV);
-- DBMS_OUTPUT.PUT_LINE(' Fm-' || sINFromUOM || ' To-' || sINToUOM );
nCFCONV := cfF41002.FCONV;
nCFCNV1 := cfF41002.FCNV1;
nCFRCONV := cfF41002.FRCONV;
nCTCONV := cfF41002.TCONV;
nCTCNV1 := cfF41002.TCNV1;
nCTRCONV := cfF41002.TRCONV;
CASE
WHEN cfF41002.FUM = sINFromUOM THEN
CASE
WHEN cfF41002.IMUOM1 = sINToUOM THEN
nProbabilities := 101;
nToUnits := nCFCNV1;
fExit := 'Y';
WHEN cfF41002.FRUM = sINToUOM THEN
nProbabilities := 102;
nToUnits := nCFCONV;
fExit := 'Y';
WHEN cfF41002.TUM = sINToUOM THEN
IF cfF41002.FUM = cfF41002.IMUOM1 THEN
nProbabilities := 103;
nToUnits := (1/nCTCNV1);
ELSE
nToUnits := (1/nCTCNV1)*nCFCNV1;
nProbabilities := 104;
END IF;
WHEN cfF41002.TRUM = sINToUOM THEN
IF cfF41002.TUM = sINFromUOM THEN
nProbabilities := 105;
nToUnits := nCTCONV;
ELSE
IF sINFromUOM = cfF41002.IMUOM1 THEN
nProbabilities := 106;
nToUnits := (1/(nCTCNV1*nCTRCONV));
ELSE
nProbabilities := 107;
nToUnits := nCFCNV1/(nCTCNV1*nCTRCONV);
END IF;
END IF;
fExit := 'Y';
WHEN cfF41002.TUM = cfF41002.IMUOM1 THEN
nProbabilities := 108;
nToUnits := nCFCNV1 * nCTCONV;
fExit := 'Y';
END CASE;
WHEN cfF41002.FRUM = sINFromUOM THEN
CASE
WHEN cfF41002.FUM = sINToUOM THEN
nProbabilities := 201;
nToUnits := nCFRCONV;
WHEN cfF41002.TUM = sINToUOM THEN
IF cfF41002.FUM = cfF41002.TRUM THEN
nProbabilities := 202;
nToUnits := nCTRCONV * nCFRCONV;
END IF;
IF cfF41002.FUM <> cfF41002.IMUOM1 AND cfF41002.TRUM = cfF41002.IMUOM1 THEN
nProbabilities := 203;
nToUnits := (nCFCNV1*nCFRCONV) / nCTCONV;
ELSE
nProbabilities := 204;
nToUnits := (nCFCNV1*nCFRCONV) / nCTCNV1;
END IF;
WHEN cfF41002.TRUM = sINToUOM THEN
IF cfF41002.FUM = cfF41002.TUM THEN
IF cfF41002.FUM = cfF41002.IMUOM1 AND cfF41002.TUM = cfF41002.IMUOM1 THEN
nToUnits := nCFRCONV/nCTRCONV;
nProbabilities := 205;
ELSE
IF sINFromUOM = cfF41002.IMUOM1 THEN
nToUnits := nCTCONV * (1/nCTCNV1);
nProbabilities := 206;
ELSE
nToUnits := nCFCNV1/nCTCNV1;
nProbabilities := 207;
END IF;
END IF;
ELSE
IF sINFromUOM = cfF41002.IMUOM1 THEN
IF cfF41002.FRUM = sINFromUOM THEN
nToUnits := (1/(nCTCNV1*nCTRCONV));
nProbabilities := 208;
ELSE
nToUnits := (1/nCTRCONV);
nProbabilities := 209;
END IF;
ELSE
nToUnits := (nCFCNV1*nCFRCONV)/(nCTCNV1*nCTRCONV);
nProbabilities := 210;
END IF;
END IF;
fExit := 'Y';
END CASE;
WHEN cfF41002.TUM = sINFromUOM THEN
CASE
WHEN cfF41002.FUM = sINToUOM THEN
nToUnits := nCTCNV1/nCFCNV1;
nProbabilities := 301;
WHEN cfF41002.FRUM = sINToUOM THEN
IF cfF41002.TUM = cfF41002.IMUOM1 THEN
nToUnits := nCFCONV/nCFCNV1;
nProbabilities := 302;
ELSE
nProbabilities := 303;
END IF;
WHEN cfF41002.TRUM = sINToUOM THEN
nToUnits := nCTCONV;
nProbabilities := 304;
END CASE;
WHEN cfF41002.TRUM = sINFromUOM THEN
CASE
WHEN cfF41002.FUM = sINToUOM THEN
nToUnits := nCTRCONV/nCFCNV1;
nProbabilities := 401;
WHEN cfF41002.FRUM = sINToUOM THEN
nToUnits := ((1/nCFRCONV)/nCFCNV1)*nCTRCONV;
nProbabilities := 402;
WHEN cfF41002.TUM = sINToUOM THEN
nProbabilities := 403;
nToUnits := nCTRCONV;
END CASE;
END CASE;
EXIT WHEN fExit = 'Y';
END LOOP;
IF nToUnits <> 0 THEN
IF sProb = 'Y' THEN
RETURN nProbabilities;
ELSE
RETURN nToUnits;
END IF;
END IF;
-- If there are no output from above two cursors then data will be fetched using F41003 in combining F41002
IF nToUnits = 0 THEN
-- Following is the Simple Query which will match exact data in from and to column in table F41003
FOR cfF41002 IN
(
SELECT UCUM, UCRUM,
CASE WHEN UCCONV <> 0 THEN ROUND(UCCONV/10000000,9) ELSE 0.000000001 END CONV,
CASE WHEN UCCONV <> 0 THEN ROUND((1/(UCCONV/10000000)),9) ELSE 0.000000001 END RCNV1
FROM TNBIDVDT.F41003
WHERE ((UCUM = sINFromUOM AND UCRUM = sINToUOM) OR (UCUM = sINToUOM AND UCRUM = sINFromUOM)) AND ROWNUM=1
)
LOOP
-- Following lines will be useful during debuging of code using SET SERVEROUTPUT ON
-- DBMS_OUTPUT.PUT_LINE('FUM (FROM1)-' || cfF41002.UCUM || ' FRUM (TO1)-' || cfF41002.UCRUM );
-- DBMS_OUTPUT.PUT_LINE('FCONV-' || cfF41002.CONV || ' FRCONV-' || cfF41002.RCNV1);
-- DBMS_OUTPUT.PUT_LINE(' Fm-' || sINFromUOM || ' To-' || sINToUOM );
IF cfF41002.UCUM = sINFromUOM AND cfF41002.UCRUM = sINToUOM THEN
IF sProb = 'Y' THEN
nProbabilities := 1001;
RETURN nProbabilities;
ELSE
RETURN cfF41002.CONV;
END IF;
ELSE
IF sProb = 'Y' THEN
nProbabilities := 1002;
RETURN nProbabilities;
ELSE
RETURN cfF41002.RCNV1;
END IF;
END IF;
END LOOP;
-- Following is little complex one which will get data from table F41002 and F41003
FOR cfF41002 IN
(
SELECT
F.IMUOM1 IMUOM1, F.UMUM FUM, F.UMRUM FRUM, F.UMCONV FCONV, F.UMCNV1 FCNV1, F.URCONV FRCONV,
T.UMUM TUM, T.UMRUM TRUM, T.UMCONV TCONV, T.UMCNV1 TCNV1, T.URCONV TRCONV
FROM
(SELECT UMITM, IMUOM1, UMUM, UMRUM, ROUND(UMCONV/10000000,9) UMCONV, ROUND(UMCNV1/10000000,9) UMCNV1, ROUND((1/UMCONV)*10000000,9) URCONV
FROM TNBIDVDT.F41002, TNBIDVDT.F4101 WHERE UMITM = IMITM AND UMITM = nItem
AND (UMCONV <> 0 AND UMCNV1 <> 0)
UNION ALL
SELECT nItem UMITM, IMUOM1, UCUM UMUM, UCRUM UMRUM, ROUND(UCCONV/10000000,9) UMCONV, (1/ROUND(UCCONV/10000000,9)) UMCNV1,
COALESCE( (SELECT ROUND(UCCONV/10000000,9) FROM TNBIDVDT.F41003 WHERE UCUM = UCUM AND UCRUM = IMUOM1 AND ROWNUM=1),1) URCONV
FROM TNBIDVDT.F41003, TNBIDVDT.F4101 WHERE IMITM = nItem) F,
(SELECT UMITM, IMUOM1, UMUM, UMRUM, ROUND(UMCONV/10000000,9) UMCONV, ROUND(UMCNV1/10000000,9) UMCNV1, ROUND((1/UMCONV)*10000000,9) URCONV
FROM TNBIDVDT.F41002, TNBIDVDT.F4101 WHERE UMITM = IMITM AND UMITM = nItem
AND (UMCONV <> 0 AND UMCNV1 <> 0)
UNION ALL
SELECT nItem UMITM, IMUOM1, UCUM UMUM, UCRUM UMRUM, ROUND(UCCONV/10000000,9) UMCONV, (1/ROUND(UCCONV/10000000,9)) UMCNV1,
COALESCE( (SELECT ROUND(UCCONV/10000000,9) FROM TNBIDVDT.F41003 WHERE UCUM = UCUM AND UCRUM = IMUOM1 AND ROWNUM=1),1) URCONV
FROM TNBIDVDT.F41003, TNBIDVDT.F4101 WHERE IMITM = nItem) T
WHERE F.UMITM = T.UMITM
AND sINFromUOM IN (F.UMUM, F.UMRUM)
AND sINToUOM IN (T.UMUM, T.UMRUM) AND ROWNUM=1
)
LOOP
-- Following lines will be useful during debuging of code using SET SERVEROUTPUT ON
-- DBMS_OUTPUT.PUT_LINE('FUM (FROM1)-' || cfF41002.FUM || ' FRUM (TO1)-' || cfF41002.FRUM || ' TUM-FROM2' || cfF41002.TUM || ' TRUM-TO2' || cfF41002.TRUM);
-- DBMS_OUTPUT.PUT_LINE('FCONV-' || cfF41002.FCONV || ' FCNV1-' || cfF41002.FCNV1 || ' FRCONV-' || cfF41002.FRCONV || ' TCONV-' || cfF41002.TCONV || ' TCNV1-' || cfF41002.TCNV1 || ' TRCONV-' || cfF41002.TRCONV);
-- DBMS_OUTPUT.PUT_LINE(' Fm-' || sINFromUOM || ' To-' || sINToUOM );
nCFCONV := cfF41002.FCONV;
IF nCFCONV = 0 THEN
nCFCONV := 0.000000001;
END IF;
nCFCNV1 := cfF41002.FCNV1;
IF nCFCNV1 = 0 THEN
nCFCNV1 := 0.000000001;
END IF;
nCFRCONV := cfF41002.FRCONV;
IF nCFRCONV = 0 THEN
nCFRCONV := 0.000000001;
END IF;
nCTCONV := cfF41002.TCONV;
IF nCTCONV = 0 THEN
nCTCONV := 0.000000001;
END IF;
nCTCNV1 := cfF41002.TCNV1;
IF nCTCNV1 = 0 THEN
nCTCNV1 := 0.000000001;
END IF;
nCTRCONV := cfF41002.TRCONV;
IF nCTRCONV = 0 THEN
nCTRCONV := 0.000000001;
END IF;
CASE
WHEN cfF41002.FUM = sINFromUOM THEN
CASE
WHEN cfF41002.IMUOM1 = sINToUOM THEN
nProbabilities := 151;
nToUnits := nCFCNV1;
fExit := 'Y';
WHEN cfF41002.FRUM = sINToUOM THEN
nProbabilities := 152;
nToUnits := nCFCONV;
fExit := 'Y';
WHEN cfF41002.TUM = sINToUOM THEN
IF cfF41002.FUM = cfF41002.IMUOM1 THEN
nProbabilities := 153;
nToUnits := (1/nCTCNV1);
ELSE
nToUnits := (1/nCTCNV1)*nCFCNV1;
nProbabilities := 154;
END IF;
WHEN cfF41002.TRUM = sINToUOM THEN
IF cfF41002.TUM = sINFromUOM THEN
nProbabilities := 155;
nToUnits := nCTCONV;
ELSE
IF sINFromUOM = cfF41002.IMUOM1 THEN
nProbabilities := 156;
nToUnits := (1/(nCTCNV1*nCTRCONV));
ELSE
nProbabilities := 157;
nToUnits := nCFCNV1/(nCTCNV1*nCTRCONV);
END IF;
END IF;
fExit := 'Y';
WHEN cfF41002.TUM = cfF41002.IMUOM1 THEN
nProbabilities := 158;
nToUnits := nCFCNV1 * nCTCONV;
fExit := 'Y';
END CASE;
WHEN cfF41002.FRUM = sINFromUOM THEN
CASE
WHEN cfF41002.FUM = sINToUOM THEN
nProbabilities := 251;
nToUnits := nCFRCONV;
WHEN cfF41002.TUM = sINToUOM THEN
IF cfF41002.FUM = cfF41002.TRUM THEN
nProbabilities := 252;
nToUnits := nCTRCONV * nCFRCONV;
END IF;
IF cfF41002.FUM <> cfF41002.IMUOM1 AND cfF41002.TRUM = cfF41002.IMUOM1 THEN
nProbabilities := 253;
nToUnits := (nCFCNV1*nCFRCONV) / nCTCONV;
ELSE
nProbabilities := 254;
nToUnits := (nCFCNV1*nCFRCONV) / nCTCNV1;
END IF;
WHEN cfF41002.TRUM = sINToUOM THEN
IF cfF41002.FUM = cfF41002.TUM THEN
IF cfF41002.FUM = cfF41002.IMUOM1 AND cfF41002.TUM = cfF41002.IMUOM1 THEN
nToUnits := nCFRCONV/nCTRCONV;
nProbabilities := 255;
ELSE
nToUnits := nCFCNV1/nCTCNV1;
nProbabilities := 256;
END IF;
ELSE
IF sINFromUOM = cfF41002.IMUOM1 THEN
IF cfF41002.FRUM = sINFromUOM THEN
nToUnits := (1/(nCTCNV1*nCTRCONV));
nProbabilities := 257;
ELSE
nToUnits := (1/nCTRCONV);
nProbabilities := 258;
END IF;
ELSE
nToUnits := (nCFCNV1*nCFRCONV)/(nCTCNV1*nCTRCONV);
nProbabilities := 259;
END IF;
END IF;
fExit := 'Y';
END CASE;
WHEN cfF41002.TUM = sINFromUOM THEN
CASE
WHEN cfF41002.FUM = sINToUOM THEN
nToUnits := nCTCNV1/nCFCNV1;
nProbabilities := 351;
WHEN cfF41002.FRUM = sINToUOM THEN
IF cfF41002.TUM = cfF41002.IMUOM1 THEN
nToUnits := nCFCONV/nCFCNV1;
nProbabilities := 352;
ELSE
nProbabilities := 353;
END IF;
WHEN cfF41002.TRUM = sINToUOM THEN
nToUnits := nCTCONV;
nProbabilities := 354;
END CASE;
WHEN cfF41002.TRUM = sINFromUOM THEN
CASE
WHEN cfF41002.FUM = sINToUOM THEN
nToUnits := nCTRCONV/nCFCNV1;
nProbabilities := 451;
WHEN cfF41002.FRUM = sINToUOM THEN
nToUnits := ((1/nCFRCONV)/nCFCNV1)*nCTRCONV;
nProbabilities := 452;
WHEN cfF41002.TUM = sINToUOM THEN
nProbabilities := 453;
nToUnits := nCTRCONV;
END CASE;
END CASE;
EXIT WHEN fExit = 'Y';
END LOOP;
END IF;
IF sProb = 'Y' THEN
RETURN nProbabilities;
ELSE
RETURN nToUnits;
END IF;
END FN_GET_CONVERSION;
--*************************************************************************************************************
-- End PROCDURE
--*************************************************************************************************************
/
Following is the oracle function which is written and working properly. Do share if you had better / shorter code than this.
CREATE OR REPLACE FUNCTION FN_GET_CONVERSION(nItem NUMBER, sFromUOM VARCHAR2, sToUOM VARCHAR2, sProb VARCHAR2)
RETURN NUMBER
IS
nPrimaryUnits NUMBER;
nToUnits NUMBER :=0;
nProbabilities NUMBER;
fExit VARCHAR2(1) := 'N';
sINFromUOM VARCHAR2(4);
sINToUOM VARCHAR2(4);
sINProb VARCHAR2(1);
sSQL Long;
nCFCONV NUMBER;
nCFCNV1 NUMBER;
nCFRCONV NUMBER;
nCTCONV NUMBER;
nCTCNV1 NUMBER;
nCTRCONV NUMBER;
--------------------------------------------------------------------------------------------------------------------------------
-- Main Processing
--------------------------------------------------------------------------------------------------------------------------------
BEGIN
--------------------------------------------------------------------------------------------------------------------------------
sINFromUOM := UPPER(sFromUOM);
sINToUOM := UPPER(sToUOM);
sINProb := UPPER(sProb);
-- If From and To UOM are same then return with 1
IF sINFromUOM = sINToUOM THEN
IF sProb = 'Y' THEN
RETURN 901;
ELSE
nProbabilities := 0;
RETURN nProbabilities;
END IF;
END IF;
-- Following is the Simple Query which will match exact data in from and to column in table F41002.
FOR cfF41002 IN
(
SELECT UMUM, UMRUM,
CASE WHEN UMCONV <> 0 THEN ROUND(UMCONV/10000000,9) ELSE 0.000000001 END CONV,
CASE WHEN UMCNV1 <> 0 THEN ROUND(UMCNV1/10000000,9) ELSE 0.000000001 END CNV1,
CASE WHEN UMCONV <> 0 THEN ROUND((1/(UMCONV/10000000)),9) ELSE 0.000000001 END RCNV1
FROM TNBIDVDT.F41002
WHERE UMITM = nItem AND
((UMUM = sINFromUOM AND UMRUM = sINToUOM) OR (UMUM = sINToUOM AND UMRUM = sINFromUOM)) AND ROWNUM=1
)
LOOP
-- Following lines will be useful during debuging of code using SET SERVEROUTPUT ON
-- DBMS_OUTPUT.PUT_LINE('FUM (FROM1)-' || cfF41002.UMUM || ' FRUM (TO1)-' || cfF41002.UMRUM);
-- DBMS_OUTPUT.PUT_LINE('FCONV-' || cfF41002.CONV || ' FCNV1-' || cfF41002.CNV1 || ' FRCONV-' || cfF41002.RCNV1 );
-- DBMS_OUTPUT.PUT_LINE(' Fm-' || sINFromUOM || ' To-' || sINToUOM );
IF cfF41002.UMUM = sINFromUOM AND cfF41002.UMRUM = sINToUOM THEN
IF sProb = 'Y' THEN
nProbabilities := 1;
RETURN nProbabilities;
ELSE
RETURN cfF41002.CONV;
END IF;
ELSE
IF sProb = 'Y' THEN
nProbabilities := 2;
RETURN nProbabilities;
ELSE
RETURN cfF41002.RCNV1;
END IF;
END IF;
END LOOP;
-- Following is little complex one which will get data from table F41002
FOR cfF41002 IN
(
SELECT
TO_CHAR(I.IMUOM1) IMUOM1, F.UMUM FUM, F.UMRUM FRUM, ROUND(F.UMCONV/10000000,9) FCONV, ROUND(F.UMCNV1/10000000,9) FCNV1,
ROUND((1/F.UMCONV)*10000000,9) FRCONV,
TO_CHAR(T.UMUM) TUM, TO_CHAR(T.UMRUM) TRUM, ROUND(T.UMCONV/10000000,9) TCONV, ROUND(T.UMCNV1/10000000,9) TCNV1, ROUND((1/T.UMCONV)*10000000,9) TRCONV
FROM TNBIDVDT.F41002 F, TNBIDVDT.F41002 T, TNBIDVDT.F4101 I
WHERE F.UMITM = I.IMITM AND T.UMITM=I.IMITM AND T.UMITM = F.UMITM
AND (F.UMCONV <> 0 AND F.UMCNV1 <> 0 AND T.UMCONV <> 0 AND T.UMCNV1 <> 0)
AND F.UMITM = nItem
AND sINFromUOM IN (F.UMUM, F.UMRUM) -- OR sINToUOM IN (F.UMUM, F.UMRUM))
AND sINToUOM IN (T.UMUM, T.UMRUM) AND ROWNUM=1
)
LOOP
-- Following lines will be useful during debuging of code using SET SERVEROUTPUT ON
-- DBMS_OUTPUT.PUT_LINE('FUM (FROM1)-' || cfF41002.FUM || ' FRUM (TO1)-' || cfF41002.FRUM || ' TUM-FROM2' || cfF41002.TUM || ' TRUM-TO2' || cfF41002.TRUM);
-- DBMS_OUTPUT.PUT_LINE('FCONV-' || cfF41002.FCONV || ' FCNV1-' || cfF41002.FCNV1 || ' FRCONV-' || cfF41002.FRCONV || ' TCONV-' || cfF41002.TCONV || ' TCNV1-' || cfF41002.TCNV1 || ' TRCONV-' || cfF41002.TRCONV);
-- DBMS_OUTPUT.PUT_LINE(' Fm-' || sINFromUOM || ' To-' || sINToUOM );
nCFCONV := cfF41002.FCONV;
nCFCNV1 := cfF41002.FCNV1;
nCFRCONV := cfF41002.FRCONV;
nCTCONV := cfF41002.TCONV;
nCTCNV1 := cfF41002.TCNV1;
nCTRCONV := cfF41002.TRCONV;
CASE
WHEN cfF41002.FUM = sINFromUOM THEN
CASE
WHEN cfF41002.IMUOM1 = sINToUOM THEN
nProbabilities := 101;
nToUnits := nCFCNV1;
fExit := 'Y';
WHEN cfF41002.FRUM = sINToUOM THEN
nProbabilities := 102;
nToUnits := nCFCONV;
fExit := 'Y';
WHEN cfF41002.TUM = sINToUOM THEN
IF cfF41002.FUM = cfF41002.IMUOM1 THEN
nProbabilities := 103;
nToUnits := (1/nCTCNV1);
ELSE
nToUnits := (1/nCTCNV1)*nCFCNV1;
nProbabilities := 104;
END IF;
WHEN cfF41002.TRUM = sINToUOM THEN
IF cfF41002.TUM = sINFromUOM THEN
nProbabilities := 105;
nToUnits := nCTCONV;
ELSE
IF sINFromUOM = cfF41002.IMUOM1 THEN
nProbabilities := 106;
nToUnits := (1/(nCTCNV1*nCTRCONV));
ELSE
nProbabilities := 107;
nToUnits := nCFCNV1/(nCTCNV1*nCTRCONV);
END IF;
END IF;
fExit := 'Y';
WHEN cfF41002.TUM = cfF41002.IMUOM1 THEN
nProbabilities := 108;
nToUnits := nCFCNV1 * nCTCONV;
fExit := 'Y';
END CASE;
WHEN cfF41002.FRUM = sINFromUOM THEN
CASE
WHEN cfF41002.FUM = sINToUOM THEN
nProbabilities := 201;
nToUnits := nCFRCONV;
WHEN cfF41002.TUM = sINToUOM THEN
IF cfF41002.FUM = cfF41002.TRUM THEN
nProbabilities := 202;
nToUnits := nCTRCONV * nCFRCONV;
END IF;
IF cfF41002.FUM <> cfF41002.IMUOM1 AND cfF41002.TRUM = cfF41002.IMUOM1 THEN
nProbabilities := 203;
nToUnits := (nCFCNV1*nCFRCONV) / nCTCONV;
ELSE
nProbabilities := 204;
nToUnits := (nCFCNV1*nCFRCONV) / nCTCNV1;
END IF;
WHEN cfF41002.TRUM = sINToUOM THEN
IF cfF41002.FUM = cfF41002.TUM THEN
IF cfF41002.FUM = cfF41002.IMUOM1 AND cfF41002.TUM = cfF41002.IMUOM1 THEN
nToUnits := nCFRCONV/nCTRCONV;
nProbabilities := 205;
ELSE
IF sINFromUOM = cfF41002.IMUOM1 THEN
nToUnits := nCTCONV * (1/nCTCNV1);
nProbabilities := 206;
ELSE
nToUnits := nCFCNV1/nCTCNV1;
nProbabilities := 207;
END IF;
END IF;
ELSE
IF sINFromUOM = cfF41002.IMUOM1 THEN
IF cfF41002.FRUM = sINFromUOM THEN
nToUnits := (1/(nCTCNV1*nCTRCONV));
nProbabilities := 208;
ELSE
nToUnits := (1/nCTRCONV);
nProbabilities := 209;
END IF;
ELSE
nToUnits := (nCFCNV1*nCFRCONV)/(nCTCNV1*nCTRCONV);
nProbabilities := 210;
END IF;
END IF;
fExit := 'Y';
END CASE;
WHEN cfF41002.TUM = sINFromUOM THEN
CASE
WHEN cfF41002.FUM = sINToUOM THEN
nToUnits := nCTCNV1/nCFCNV1;
nProbabilities := 301;
WHEN cfF41002.FRUM = sINToUOM THEN
IF cfF41002.TUM = cfF41002.IMUOM1 THEN
nToUnits := nCFCONV/nCFCNV1;
nProbabilities := 302;
ELSE
nProbabilities := 303;
END IF;
WHEN cfF41002.TRUM = sINToUOM THEN
nToUnits := nCTCONV;
nProbabilities := 304;
END CASE;
WHEN cfF41002.TRUM = sINFromUOM THEN
CASE
WHEN cfF41002.FUM = sINToUOM THEN
nToUnits := nCTRCONV/nCFCNV1;
nProbabilities := 401;
WHEN cfF41002.FRUM = sINToUOM THEN
nToUnits := ((1/nCFRCONV)/nCFCNV1)*nCTRCONV;
nProbabilities := 402;
WHEN cfF41002.TUM = sINToUOM THEN
nProbabilities := 403;
nToUnits := nCTRCONV;
END CASE;
END CASE;
EXIT WHEN fExit = 'Y';
END LOOP;
IF nToUnits <> 0 THEN
IF sProb = 'Y' THEN
RETURN nProbabilities;
ELSE
RETURN nToUnits;
END IF;
END IF;
-- If there are no output from above two cursors then data will be fetched using F41003 in combining F41002
IF nToUnits = 0 THEN
-- Following is the Simple Query which will match exact data in from and to column in table F41003
FOR cfF41002 IN
(
SELECT UCUM, UCRUM,
CASE WHEN UCCONV <> 0 THEN ROUND(UCCONV/10000000,9) ELSE 0.000000001 END CONV,
CASE WHEN UCCONV <> 0 THEN ROUND((1/(UCCONV/10000000)),9) ELSE 0.000000001 END RCNV1
FROM TNBIDVDT.F41003
WHERE ((UCUM = sINFromUOM AND UCRUM = sINToUOM) OR (UCUM = sINToUOM AND UCRUM = sINFromUOM)) AND ROWNUM=1
)
LOOP
-- Following lines will be useful during debuging of code using SET SERVEROUTPUT ON
-- DBMS_OUTPUT.PUT_LINE('FUM (FROM1)-' || cfF41002.UCUM || ' FRUM (TO1)-' || cfF41002.UCRUM );
-- DBMS_OUTPUT.PUT_LINE('FCONV-' || cfF41002.CONV || ' FRCONV-' || cfF41002.RCNV1);
-- DBMS_OUTPUT.PUT_LINE(' Fm-' || sINFromUOM || ' To-' || sINToUOM );
IF cfF41002.UCUM = sINFromUOM AND cfF41002.UCRUM = sINToUOM THEN
IF sProb = 'Y' THEN
nProbabilities := 1001;
RETURN nProbabilities;
ELSE
RETURN cfF41002.CONV;
END IF;
ELSE
IF sProb = 'Y' THEN
nProbabilities := 1002;
RETURN nProbabilities;
ELSE
RETURN cfF41002.RCNV1;
END IF;
END IF;
END LOOP;
-- Following is little complex one which will get data from table F41002 and F41003
FOR cfF41002 IN
(
SELECT
F.IMUOM1 IMUOM1, F.UMUM FUM, F.UMRUM FRUM, F.UMCONV FCONV, F.UMCNV1 FCNV1, F.URCONV FRCONV,
T.UMUM TUM, T.UMRUM TRUM, T.UMCONV TCONV, T.UMCNV1 TCNV1, T.URCONV TRCONV
FROM
(SELECT UMITM, IMUOM1, UMUM, UMRUM, ROUND(UMCONV/10000000,9) UMCONV, ROUND(UMCNV1/10000000,9) UMCNV1, ROUND((1/UMCONV)*10000000,9) URCONV
FROM TNBIDVDT.F41002, TNBIDVDT.F4101 WHERE UMITM = IMITM AND UMITM = nItem
AND (UMCONV <> 0 AND UMCNV1 <> 0)
UNION ALL
SELECT nItem UMITM, IMUOM1, UCUM UMUM, UCRUM UMRUM, ROUND(UCCONV/10000000,9) UMCONV, (1/ROUND(UCCONV/10000000,9)) UMCNV1,
COALESCE( (SELECT ROUND(UCCONV/10000000,9) FROM TNBIDVDT.F41003 WHERE UCUM = UCUM AND UCRUM = IMUOM1 AND ROWNUM=1),1) URCONV
FROM TNBIDVDT.F41003, TNBIDVDT.F4101 WHERE IMITM = nItem) F,
(SELECT UMITM, IMUOM1, UMUM, UMRUM, ROUND(UMCONV/10000000,9) UMCONV, ROUND(UMCNV1/10000000,9) UMCNV1, ROUND((1/UMCONV)*10000000,9) URCONV
FROM TNBIDVDT.F41002, TNBIDVDT.F4101 WHERE UMITM = IMITM AND UMITM = nItem
AND (UMCONV <> 0 AND UMCNV1 <> 0)
UNION ALL
SELECT nItem UMITM, IMUOM1, UCUM UMUM, UCRUM UMRUM, ROUND(UCCONV/10000000,9) UMCONV, (1/ROUND(UCCONV/10000000,9)) UMCNV1,
COALESCE( (SELECT ROUND(UCCONV/10000000,9) FROM TNBIDVDT.F41003 WHERE UCUM = UCUM AND UCRUM = IMUOM1 AND ROWNUM=1),1) URCONV
FROM TNBIDVDT.F41003, TNBIDVDT.F4101 WHERE IMITM = nItem) T
WHERE F.UMITM = T.UMITM
AND sINFromUOM IN (F.UMUM, F.UMRUM)
AND sINToUOM IN (T.UMUM, T.UMRUM) AND ROWNUM=1
)
LOOP
-- Following lines will be useful during debuging of code using SET SERVEROUTPUT ON
-- DBMS_OUTPUT.PUT_LINE('FUM (FROM1)-' || cfF41002.FUM || ' FRUM (TO1)-' || cfF41002.FRUM || ' TUM-FROM2' || cfF41002.TUM || ' TRUM-TO2' || cfF41002.TRUM);
-- DBMS_OUTPUT.PUT_LINE('FCONV-' || cfF41002.FCONV || ' FCNV1-' || cfF41002.FCNV1 || ' FRCONV-' || cfF41002.FRCONV || ' TCONV-' || cfF41002.TCONV || ' TCNV1-' || cfF41002.TCNV1 || ' TRCONV-' || cfF41002.TRCONV);
-- DBMS_OUTPUT.PUT_LINE(' Fm-' || sINFromUOM || ' To-' || sINToUOM );
nCFCONV := cfF41002.FCONV;
IF nCFCONV = 0 THEN
nCFCONV := 0.000000001;
END IF;
nCFCNV1 := cfF41002.FCNV1;
IF nCFCNV1 = 0 THEN
nCFCNV1 := 0.000000001;
END IF;
nCFRCONV := cfF41002.FRCONV;
IF nCFRCONV = 0 THEN
nCFRCONV := 0.000000001;
END IF;
nCTCONV := cfF41002.TCONV;
IF nCTCONV = 0 THEN
nCTCONV := 0.000000001;
END IF;
nCTCNV1 := cfF41002.TCNV1;
IF nCTCNV1 = 0 THEN
nCTCNV1 := 0.000000001;
END IF;
nCTRCONV := cfF41002.TRCONV;
IF nCTRCONV = 0 THEN
nCTRCONV := 0.000000001;
END IF;
CASE
WHEN cfF41002.FUM = sINFromUOM THEN
CASE
WHEN cfF41002.IMUOM1 = sINToUOM THEN
nProbabilities := 151;
nToUnits := nCFCNV1;
fExit := 'Y';
WHEN cfF41002.FRUM = sINToUOM THEN
nProbabilities := 152;
nToUnits := nCFCONV;
fExit := 'Y';
WHEN cfF41002.TUM = sINToUOM THEN
IF cfF41002.FUM = cfF41002.IMUOM1 THEN
nProbabilities := 153;
nToUnits := (1/nCTCNV1);
ELSE
nToUnits := (1/nCTCNV1)*nCFCNV1;
nProbabilities := 154;
END IF;
WHEN cfF41002.TRUM = sINToUOM THEN
IF cfF41002.TUM = sINFromUOM THEN
nProbabilities := 155;
nToUnits := nCTCONV;
ELSE
IF sINFromUOM = cfF41002.IMUOM1 THEN
nProbabilities := 156;
nToUnits := (1/(nCTCNV1*nCTRCONV));
ELSE
nProbabilities := 157;
nToUnits := nCFCNV1/(nCTCNV1*nCTRCONV);
END IF;
END IF;
fExit := 'Y';
WHEN cfF41002.TUM = cfF41002.IMUOM1 THEN
nProbabilities := 158;
nToUnits := nCFCNV1 * nCTCONV;
fExit := 'Y';
END CASE;
WHEN cfF41002.FRUM = sINFromUOM THEN
CASE
WHEN cfF41002.FUM = sINToUOM THEN
nProbabilities := 251;
nToUnits := nCFRCONV;
WHEN cfF41002.TUM = sINToUOM THEN
IF cfF41002.FUM = cfF41002.TRUM THEN
nProbabilities := 252;
nToUnits := nCTRCONV * nCFRCONV;
END IF;
IF cfF41002.FUM <> cfF41002.IMUOM1 AND cfF41002.TRUM = cfF41002.IMUOM1 THEN
nProbabilities := 253;
nToUnits := (nCFCNV1*nCFRCONV) / nCTCONV;
ELSE
nProbabilities := 254;
nToUnits := (nCFCNV1*nCFRCONV) / nCTCNV1;
END IF;
WHEN cfF41002.TRUM = sINToUOM THEN
IF cfF41002.FUM = cfF41002.TUM THEN
IF cfF41002.FUM = cfF41002.IMUOM1 AND cfF41002.TUM = cfF41002.IMUOM1 THEN
nToUnits := nCFRCONV/nCTRCONV;
nProbabilities := 255;
ELSE
nToUnits := nCFCNV1/nCTCNV1;
nProbabilities := 256;
END IF;
ELSE
IF sINFromUOM = cfF41002.IMUOM1 THEN
IF cfF41002.FRUM = sINFromUOM THEN
nToUnits := (1/(nCTCNV1*nCTRCONV));
nProbabilities := 257;
ELSE
nToUnits := (1/nCTRCONV);
nProbabilities := 258;
END IF;
ELSE
nToUnits := (nCFCNV1*nCFRCONV)/(nCTCNV1*nCTRCONV);
nProbabilities := 259;
END IF;
END IF;
fExit := 'Y';
END CASE;
WHEN cfF41002.TUM = sINFromUOM THEN
CASE
WHEN cfF41002.FUM = sINToUOM THEN
nToUnits := nCTCNV1/nCFCNV1;
nProbabilities := 351;
WHEN cfF41002.FRUM = sINToUOM THEN
IF cfF41002.TUM = cfF41002.IMUOM1 THEN
nToUnits := nCFCONV/nCFCNV1;
nProbabilities := 352;
ELSE
nProbabilities := 353;
END IF;
WHEN cfF41002.TRUM = sINToUOM THEN
nToUnits := nCTCONV;
nProbabilities := 354;
END CASE;
WHEN cfF41002.TRUM = sINFromUOM THEN
CASE
WHEN cfF41002.FUM = sINToUOM THEN
nToUnits := nCTRCONV/nCFCNV1;
nProbabilities := 451;
WHEN cfF41002.FRUM = sINToUOM THEN
nToUnits := ((1/nCFRCONV)/nCFCNV1)*nCTRCONV;
nProbabilities := 452;
WHEN cfF41002.TUM = sINToUOM THEN
nProbabilities := 453;
nToUnits := nCTRCONV;
END CASE;
END CASE;
EXIT WHEN fExit = 'Y';
END LOOP;
END IF;
IF sProb = 'Y' THEN
RETURN nProbabilities;
ELSE
RETURN nToUnits;
END IF;
END FN_GET_CONVERSION;
--*************************************************************************************************************
-- End PROCDURE
--*************************************************************************************************************
/
