UOM Conversion

CREATE OR REPLACE PACKAGE XX_UOM_PKG AS
PROCEDURE MAIN(ERRBUF OUT VARCHAR2,RETCODE OUT NUMBER);
PROCEDURE INSERT_DATA_STG;
PROCEDURE INSERT_COLUMN_STG;
PROCEDURE VALIDATE_UOM_STG;
PROCEDURE XX_UOM_IMPORT;
END XX_UOM_PKG;
/


CREATE OR REPLACE PACKAGE BODY XX_UOM_PKG AS
L_ERROR_MESSAGE VARCHAR2(1000);
PROCEDURE MAIN(ERRBUF OUT VARCHAR2,RETCODE OUT NUMBER)
AS
BEGIN
INSERT_DATA_STG;
INSERT_COLUMN_STG;
VALIDATE_UOM_STG;
XX_UOM_IMPORT;
EXCEPTION
WHEN OTHERS
THEN
RAISE_APPLICATION_ERROR(-20000,SQLERRM);
END;
PROCEDURE INSERT_DATA_STG
IS
CURSOR CUR_STG
IS
SELECT UNIT_OF_MEASURE,
UOM_CODE,
UOM_CLASS,
BASE_UOM_FLAG,
UNIT_OF_MEASURE_TL,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATED_BY,
CREATION_DATE,
LAST_UPDATE_LOGIN,
DISABLE_DATE,
DESCRIPTION,
LANGUAGE,
SOURCE_LANG,
ATTRIBUTE_CATEGORY,
ATTRIBUTE1,
ATTRIBUTE2,
ATTRIBUTE3,
ATTRIBUTE4,
ATTRIBUTE5,
ATTRIBUTE6,
ATTRIBUTE7,
ATTRIBUTE8,
ATTRIBUTE9,
ATTRIBUTE10,
ATTRIBUTE11,
ATTRIBUTE12,
ATTRIBUTE13,
ATTRIBUTE14,
ATTRIBUTE15,
REQUEST_ID,
PROGRAM_APPLICATION_ID,
PROGRAM_ID,
PROGRAM_UPDATE_DATE
FROM MTL_UNITS_OF_MEASURE
WHERE UOM_CODE='IN';
BEGIN
DELETE XX_UOM_CONV_STG;
COMMIT;
FOR REC_CUR_STG IN CUR_STG
LOOP
INSERT INTO XX_UOM_CONV_STG
(UNIT_OF_MEASURE,
UOM_CODE,
UOM_CLASS,
BASE_UOM_FLAG,
UNIT_OF_MEASURE_TL,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATED_BY,
CREATION_DATE,
LAST_UPDATE_LOGIN,
DISABLE_DATE,
DESCRIPTION,
LANGUAGE,
SOURCE_LANG,
ATTRIBUTE_CATEGORY,
ATTRIBUTE1,
ATTRIBUTE2,
ATTRIBUTE3,
ATTRIBUTE4,
ATTRIBUTE5,
ATTRIBUTE6,
ATTRIBUTE7,
ATTRIBUTE8,
ATTRIBUTE9,
ATTRIBUTE10,
ATTRIBUTE11,
ATTRIBUTE12,
ATTRIBUTE13,
ATTRIBUTE14,
ATTRIBUTE15,
REQUEST_ID,
PROGRAM_APPLICATION_ID,
PROGRAM_ID,
PROGRAM_UPDATE_DATE,
STATUS_FLAG)
VALUES
(REC_CUR_STG.UNIT_OF_MEASURE,
REC_CUR_STG.UOM_CODE,
REC_CUR_STG.UOM_CLASS,
REC_CUR_STG.BASE_UOM_FLAG,
REC_CUR_STG.UNIT_OF_MEASURE_TL,
SYSDATE,
FND_PROFILE.VALUE('USER_ID'),
FND_PROFILE.VALUE('USER_ID'),
SYSDATE,
'-1',
NULL,
REC_CUR_STG.DESCRIPTION,
REC_CUR_STG.LANGUAGE,
REC_CUR_STG.SOURCE_LANG,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
'N');
END LOOP;
END INSERT_DATA_STG ;

PROCEDURE INSERT_COLUMN_STG IS
BEGIN
INSERT INTO XX_UOM_CONV_STG
(UNIT_OF_MEASURE,
UOM_CODE,
UOM_CLASS,
BASE_UOM_FLAG,
UNIT_OF_MEASURE_TL,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATED_BY,
CREATION_DATE,
LAST_UPDATE_LOGIN,
DISABLE_DATE,
DESCRIPTION,
LANGUAGE,
SOURCE_LANG,
ATTRIBUTE_CATEGORY,
ATTRIBUTE1,
ATTRIBUTE2,
ATTRIBUTE3,
ATTRIBUTE4,
ATTRIBUTE5,
ATTRIBUTE6,
ATTRIBUTE7,
ATTRIBUTE8,
ATTRIBUTE9,
ATTRIBUTE10,
ATTRIBUTE11,
ATTRIBUTE12,
ATTRIBUTE13,
ATTRIBUTE14,
ATTRIBUTE15,
REQUEST_ID,
PROGRAM_APPLICATION_ID,
PROGRAM_ID,
PROGRAM_UPDATE_DATE,
STATUS_FLAG)
VALUES
('POISE',
'PO',
'VISCOSITY',
'N',
'POISE',
SYSDATE,
FND_PROFILE.VALUE('USER_ID'),
FND_PROFILE.VALUE('USER_ID'),
SYSDATE,
'-1',
NULL,
'POISE',
'US',
'US',
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
'N');
COMMIT;
END INSERT_COLUMN_STG;

PROCEDURE VALIDATE_UOM_STG
IS
L_UNIT_OF_MEASURE MTL_UNITS_OF_MEASURE.UNIT_OF_MEASURE%TYPE;
L_UOM_CODE MTL_UNITS_OF_MEASURE.UOM_CODE%TYPE;
L_UOM_CLASS MTL_UOM_CLASSES.UOM_CLASS%TYPE;
L_UNIT_OF_MEASURE_TL MTL_UNITS_OF_MEASURE.UNIT_OF_MEASURE_TL%TYPE;
L_LANGUAGE FND_LANGUAGES.LANGUAGE_CODE%TYPE;
L_ERROR_MESSAGE VARCHAR2(4000);
CURSOR CUR_VAL_UOM
IS
SELECT A.*,ROWID
FROM XX_UOM_CONV_STG A
WHERE STATUS_FLAG='N';
BEGIN
FOR REC_CUR_VAL_UOM IN CUR_VAL_UOM
LOOP
BEGIN
SELECT UNIT_OF_MEASURE
INTO L_UNIT_OF_MEASURE
FROM MTL_UNITS_OF_MEASURE
WHERE UNIT_OF_MEASURE = REC_CUR_VAL_UOM.UNIT_OF_MEASURE;
UPDATE XX_UOM_CONV_STG
SET UNIT_OF_MEASURE=L_UNIT_OF_MEASURE
WHERE ROWID= REC_CUR_VAL_UOM.ROWID;
EXCEPTION
WHEN OTHERS THEN
L_ERROR_MESSAGE:=L_ERROR_MESSAGE || ' ERROR UNIT OF MEASURE';
END;
BEGIN
SELECT LANGUAGE_CODE
INTO L_LANGUAGE
FROM FND_LANGUAGES
WHERE LANGUAGE_CODE=REC_CUR_VAL_UOM.LANGUAGE;
UPDATE XX_UOM_CONV_STG
SET LANGUAGE=L_LANGUAGE
WHERE ROWID=REC_CUR_VAL_UOM.ROWID;
EXCEPTION
WHEN OTHERS THEN
L_ERROR_MESSAGE:=L_ERROR_MESSAGE || 'LANGUAGE DOES NOT EXIST';
END;
BEGIN
SELECT UOM_CLASS
INTO L_UOM_CLASS
FROM MTL_UOM_CLASSES
WHERE UOM_CLASS = REC_CUR_VAL_UOM.UOM_CLASS;
UPDATE XX_UOM_CONV_STG
SET UOM_CLASS=L_UOM_CLASS
WHERE ROWID=REC_CUR_VAL_UOM.ROWID;
EXCEPTION
WHEN OTHERS THEN
L_ERROR_MESSAGE:=L_ERROR_MESSAGE || 'UNIT OF MEASURE CLASS DOES NOT EXIST';
END;
IF L_ERROR_MESSAGE IS NULL THEN
UPDATE XX_UOM_CONV_STG
SET STATUS_FLAG = 'P'
WHERE ROWID=REC_CUR_VAL_UOM.ROWID;
ELSE
UPDATE XX_UOM_CONV_STG
SET STATUS_FLAG='E' ,
ERROR_MESSAGE = ERROR_MESSAGE || L_ERROR_MESSAGE
WHERE ROWID=REC_CUR_VAL_UOM.ROWID;
END IF;
COMMIT;
END LOOP;
UPDATE XX_UOM_CONV_STG SET UNIT_OF_MEASURE='SRI',UOM_CODE='SRI';
COMMIT;
END VALIDATE_UOM_STG;

PROCEDURE XX_UOM_IMPORT
IS
L_ROW_ID VARCHAR2(200):= NULL;
L_DISABLE_DATE DATE:=NULL;
L_ATTRIBUTE_CATEGORY VARCHAR2(200):= NULL;
L_ATTRIBUTE1 VARCHAR2(200):= NULL;
L_ATTRIBUTE2 VARCHAR2(200):= NULL;
L_ATTRIBUTE3 VARCHAR2(200):= NULL;
L_ATTRIBUTE4 VARCHAR2(200):= NULL;
L_ATTRIBUTE5 VARCHAR2(200):= NULL;
L_ATTRIBUTE6 VARCHAR2(200):= NULL;
L_ATTRIBUTE7 VARCHAR2(200):= NULL;
L_ATTRIBUTE8 VARCHAR2(200):= NULL;
L_ATTRIBUTE9 VARCHAR2(200):= NULL;
L_ATTRIBUTE10 VARCHAR2(200):= NULL;
L_ATTRIBUTE11 VARCHAR2(200):= NULL;
L_ATTRIBUTE12 VARCHAR2(200):= NULL;
L_ATTRIBUTE13 VARCHAR2(200):= NULL;
L_ATTRIBUTE14 VARCHAR2(200):= NULL;
L_ATTRIBUTE15 VARCHAR2(200):= NULL;
L_REQUEST_ID NUMBER:= 1;
L_PROGRAM_ID NUMBER:= 1;
L_PROGRAM_APPLICATION_ID NUMBER:= 1;
L_PROGRAM_UPDATE_DATE DATE:= SYSDATE;
L_LAST_UPDATE_LOGIN NUMBER:=1;
L_COUNT1 NUMBER;
L_COUNT2 NUMBER;
L_COUNT3 NUMBER;
CURSOR CUR_IMP_UOM IS SELECT * FROM XX_UOM_CONV_STG;
BEGIN
FOR REC_CUR_IMP_UOM IN CUR_IMP_UOM LOOP
SELECT COUNT(*) INTO L_COUNT1 FROM MTL_UOM_CLASSES_TL WHERE UOM_CLASS=REC_CUR_IMP_UOM.UOM_CLASS ;
SELECT COUNT(*) INTO L_COUNT2 FROM MTL_UNITS_OF_MEASURE_TL WHERE UNIT_OF_MEASURE=REC_CUR_IMP_UOM.UNIT_OF_MEASURE;
SELECT COUNT(*) INTO L_COUNT3 FROM MTL_UNITS_OF_MEASURE_TL WHERE UOM_CLASS=REC_CUR_IMP_UOM.UOM_CLASS AND BASE_UOM_FLAG='Y';
IF L_COUNT3 =0 OR (L_COUNT3 IS NOT NULL AND REC_CUR_IMP_UOM.BASE_UOM_FLAG!='Y') THEN
IF L_COUNT1 IS NOT NULL AND L_COUNT2 = 0 THEN
MTL_UNITS_OF_MEASURE_TL_PKG.INSERT_ROW (L_ROW_ID,
REC_CUR_IMP_UOM.UNIT_OF_MEASURE,
REC_CUR_IMP_UOM.UNIT_OF_MEASURE_TL,
L_ATTRIBUTE_CATEGORY,
L_ATTRIBUTE1,
L_ATTRIBUTE2,
L_ATTRIBUTE3,
L_ATTRIBUTE4,
L_ATTRIBUTE5,
L_ATTRIBUTE6,
L_ATTRIBUTE7,
L_ATTRIBUTE8,
L_ATTRIBUTE9,
L_ATTRIBUTE10,
L_ATTRIBUTE11,
L_ATTRIBUTE12,
L_ATTRIBUTE13,
L_ATTRIBUTE14,
L_ATTRIBUTE15,
L_REQUEST_ID,
L_DISABLE_DATE,
REC_CUR_IMP_UOM.BASE_UOM_FLAG,
REC_CUR_IMP_UOM.UOM_CODE,
REC_CUR_IMP_UOM.UOM_CLASS,
REC_CUR_IMP_UOM.DESCRIPTION,
SYSDATE,
'1318',
SYSDATE,
'1318',
'-1',
L_PROGRAM_APPLICATION_ID,
L_PROGRAM_ID,
L_PROGRAM_UPDATE_DATE,
REC_CUR_IMP_UOM.LANGUAGE );
ELSIF L_COUNT1 =0 AND L_COUNT2 IS NOT NULL THEN
FND_FILE.PUT_LINE(FND_FILE.OUTPUT,REC_CUR_IMP_UOM.UOM_CLASS||' UOM CLASS DOES NOT EXIST
AND ' || REC_CUR_IMP_UOM.UNIT_OF_MEASURE||' UNIT OF MEASURE ALREADY EXISTS');
ELSIF L_COUNT1 =0 AND L_COUNT2 =0 THEN
FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'UOM CLASS DOES NOT EXIST');
ELSIF L_COUNT1 IS NOT NULL AND L_COUNT2 IS NOT NULL THEN
FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'UNIT OF MEASURE ALREADY EXISTS');
END IF;
ELSE
FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'BASE UNIT OF MEASURE ALREADY EXISTS');
END IF;
END LOOP;
COMMIT;
END XX_UOM_IMPORT;
END XX_UOM_PKG;
/

=====================================================
CONCURRENT PROGRAM FOR UOM CONVERSION:
=============================================


MAIN PRG OF CONVERSION(UOM)

==========================================================