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
--*************************************************************************************************************
/
You are an excellent blogger. I am highly impressed by your work and will recommend your tips to everyone in my circle so that they could also take the benefits. Edge polishing, angle polishing and facet optical polishing.
ReplyDeleteIt was great to see someone write on this topic. Thanks for sharing your thoughts. Custom Optical Coatings and Thin Film Coatings.
ReplyDeleteYou’ve provided great information in your blog. Many thanks for sharing the information in your blog. Aluminum Nitride Machining Services.
ReplyDeleteWhat a nice article! You’ve shared very useful information. You really did an awesome job. Thank you so much. Full Service CNC and Manual Ceramic Machining.
ReplyDeleteThanks for helping us understand this topic. You have written it in a way that makes it very simple to understand. Thank you so much. Ceramic Substrates and Technical Ceramics.
ReplyDelete