Tuesday, April 22, 2008

Items Conversion

======================================================================================
CREATE TABLE XX_INV_ITEM_STG
(INVENTORY_ITEM_ID NUMBER,
ORGANIZATION_ID NUMBER,
LAST_UPDATE_DATE DATE,
LAST_UPDATED_BY NUMBER,
CREATION_DATE DATE,
CREATED_BY NUMBER,
LAST_UPDATE_LOGIN NUMBER,
SUMMARY_FLAG VARCHAR2 (1),
ENABLED_FLAG VARCHAR2 (1),
DESCRIPTION VARCHAR2 (240),
SEGMENT1 VARCHAR2 (40),
PURCHASING_ITEM_FLAG VARCHAR2 (1),
SHIPPABLE_ITEM_FLAG VARCHAR2 (1),
CUSTOMER_ORDER_FLAG VARCHAR2 (1),
INTERNAL_ORDER_FLAG VARCHAR2 (1),
SERVICE_ITEM_FLAG VARCHAR2 (1),
INVENTORY_ITEM_FLAG VARCHAR2 (1),
ENG_ITEM_FLAG VARCHAR2 (1),
INVENTORY_ASSET_FLAG VARCHAR2 (1),
PURCHASING_ENABLED_FLAG VARCHAR2 (1),
CUSTOMER_ORDER_ENABLED_FLAG VARCHAR2 (1),
INTERNAL_ORDER_ENABLED_FLAG VARCHAR2 (1),
SO_TRANSACTIONS_FLAG VARCHAR2 (1),
MTL_TRANSACTIONS_ENABLED_FLAG VARCHAR2 (1),
STOCK_ENABLED_FLAG VARCHAR2 (1),
BOM_ENABLED_FLAG VARCHAR2 (1),
BUILD_IN_WIP_FLAG VARCHAR2 (1),
LIST_PRICE_PER_UNIT NUMBER,
EXPENSE_ACCOUNT NUMBER,
BOM_ITEM_TYPE NUMBER,
PICK_COMPONENTS_FLAG VARCHAR2 (1),
REPLENISH_TO_ORDER_FLAG VARCHAR2 (1),
ATP_COMPONENTS_FLAG VARCHAR2 (1),
ATP_FLAG VARCHAR2 (1),
PRIMARY_UOM_CODE VARCHAR2 (3),
INVENTORY_ITEM_STATUS_CODE VARCHAR2 (10),
VENDOR_WARRANTY_FLAG VARCHAR2 (1),
SERVICEABLE_PRODUCT_FLAG VARCHAR2 (1),
INVOICEABLE_ITEM_FLAG VARCHAR2 (1),
INVOICE_ENABLED_FLAG VARCHAR2 (1),
MUST_USE_APPROVED_VENDOR_FLAG VARCHAR2 (1),
REQUEST_ID NUMBER,
PROGRAM_APPLICATION_ID NUMBER,
PROGRAM_ID NUMBER,
PROGRAM_UPDATE_DATE DATE,
OUTSIDE_OPERATION_FLAG VARCHAR2 (1),
OUTSIDE_OPERATION_UOM_TYPE VARCHAR2 (25),
COSTING_ENABLED_FLAG VARCHAR2 (1),
AUTO_CREATED_CONFIG_FLAG VARCHAR2 (1),
CYCLE_COUNT_ENABLED_FLAG VARCHAR2 (1),
STATUS_FLAG VARCHAR2(1),
ERROR_MESSAGE VARCHAR2(4000));


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

=====================================================================================
/*XX_INV_ITEM_PROC*/

CREATE OR REPLACE PACKAGE XX_INV_ITEM_PKG AS
PROCEDURE MAIN(ERRBUF OUT VARCHAR2,RETCODE OUT NUMBER);
PROCEDURE XX_INV_ITEM_PROC;
PROCEDURE XX_ITEM_VALIDATE;
PROCEDURE XX_ITEM_LOAD;
END XX_INV_ITEM_PKG;

==============================================================================================
CREATE OR REPLACE PACKAGE BODY XX_INV_ITEM_PKG AS
L_ERROR_MESSAGE VARCHAR2(1000);
PROCEDURE MAIN(ERRBUF OUT VARCHAR2,RETCODE OUT NUMBER)
AS
BEGIN
XX_INV_ITEM_PROC;
XX_ITEM_VALIDATE;
XX_ITEM_LOAD ;
EXCEPTION
WHEN OTHERS
THEN
RAISE_APPLICATION_ERROR(-20000,SQLERRM);
END MAIN;

PROCEDURE XX_INV_ITEM_PROC
IS
CURSOR CUR_MTL_SYS_ITEM
IS
SELECT
MSI.INVENTORY_ITEM_ID,
MP.ORGANIZATION_ID,
MP.LAST_UPDATE_DATE,
MP.LAST_UPDATED_BY,
MP.CREATION_DATE,
MP.CREATED_BY,
MP.LAST_UPDATE_LOGIN,
MSI.SUMMARY_FLAG,
MSI.ENABLED_FLAG,
MSI.DESCRIPTION,
MSI.SEGMENT1,
MSI.PURCHASING_ITEM_FLAG,
MSI.SHIPPABLE_ITEM_FLAG,
MSI.CUSTOMER_ORDER_FLAG,
MSI.INTERNAL_ORDER_FLAG,
MSI.SERVICE_ITEM_FLAG,
MSI.INVENTORY_ITEM_FLAG,
MSI.ENG_ITEM_FLAG,
MSI.INVENTORY_ASSET_FLAG,
MSI.PURCHASING_ENABLED_FLAG,
MSI.CUSTOMER_ORDER_ENABLED_FLAG,
MSI.INTERNAL_ORDER_ENABLED_FLAG,
MSI.SO_TRANSACTIONS_FLAG,
MSI.MTL_TRANSACTIONS_ENABLED_FLAG,
MSI.STOCK_ENABLED_FLAG,
MSI.BOM_ENABLED_FLAG,
MSI.BUILD_IN_WIP_FLAG,
MSI.LIST_PRICE_PER_UNIT,
MSI.EXPENSE_ACCOUNT,
MSI.BOM_ITEM_TYPE,
MSI.PICK_COMPONENTS_FLAG,
MSI.REPLENISH_TO_ORDER_FLAG,
MSI.ATP_COMPONENTS_FLAG,
MSI.ATP_FLAG,
MSI.PRIMARY_UOM_CODE,
MSI.INVENTORY_ITEM_STATUS_CODE,
MSI.VENDOR_WARRANTY_FLAG,
MSI.SERVICEABLE_PRODUCT_FLAG,
MSI.INVOICEABLE_ITEM_FLAG,
MSI.INVOICE_ENABLED_FLAG,
MSI.MUST_USE_APPROVED_VENDOR_FLAG,
MSI.REQUEST_ID,
MSI.PROGRAM_APPLICATION_ID,
MSI.PROGRAM_ID,
MSI.PROGRAM_UPDATE_DATE,
MSI.OUTSIDE_OPERATION_FLAG,
MSI.OUTSIDE_OPERATION_UOM_TYPE,
MSI.COSTING_ENABLED_FLAG,
MSI.AUTO_CREATED_CONFIG_FLAG,
MSI.CYCLE_COUNT_ENABLED_FLAG
FROM MTL_PARAMETERS MP,MTL_SYSTEM_ITEMS_B MSI
WHERE MP.ORGANIZATION_ID=MSI.ORGANIZATION_ID
AND MP.ORGANIZATION_ID=FND_PROFILE.VALUE('ORG_ID')
AND MSI.SEGMENT1='AS54999';
BEGIN
DELETE XX_INV_ITEM_STG;
COMMIT;
FOR REC_CUR_MTL_SYS_ITEM IN CUR_MTL_SYS_ITEM
LOOP
INSERT INTO XX_INV_ITEM_STG
(INVENTORY_ITEM_ID,
ORGANIZATION_ID,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_LOGIN,
SUMMARY_FLAG,
ENABLED_FLAG,
DESCRIPTION,
SEGMENT1,
PURCHASING_ITEM_FLAG,
SHIPPABLE_ITEM_FLAG,
CUSTOMER_ORDER_FLAG,
INTERNAL_ORDER_FLAG,
SERVICE_ITEM_FLAG,
INVENTORY_ITEM_FLAG,
ENG_ITEM_FLAG,
INVENTORY_ASSET_FLAG,
PURCHASING_ENABLED_FLAG,
CUSTOMER_ORDER_ENABLED_FLAG,
INTERNAL_ORDER_ENABLED_FLAG,
SO_TRANSACTIONS_FLAG,
MTL_TRANSACTIONS_ENABLED_FLAG,
STOCK_ENABLED_FLAG,
BOM_ENABLED_FLAG,
BUILD_IN_WIP_FLAG,
LIST_PRICE_PER_UNIT,
EXPENSE_ACCOUNT,
BOM_ITEM_TYPE,
PICK_COMPONENTS_FLAG,
REPLENISH_TO_ORDER_FLAG,
ATP_COMPONENTS_FLAG,
ATP_FLAG,
PRIMARY_UOM_CODE,
INVENTORY_ITEM_STATUS_CODE,
VENDOR_WARRANTY_FLAG,
SERVICEABLE_PRODUCT_FLAG,
INVOICEABLE_ITEM_FLAG,
INVOICE_ENABLED_FLAG,
MUST_USE_APPROVED_VENDOR_FLAG,
REQUEST_ID,
PROGRAM_APPLICATION_ID,
PROGRAM_ID,
PROGRAM_UPDATE_DATE,
OUTSIDE_OPERATION_FLAG,
OUTSIDE_OPERATION_UOM_TYPE,
COSTING_ENABLED_FLAG,
AUTO_CREATED_CONFIG_FLAG,
CYCLE_COUNT_ENABLED_FLAG,
STATUS_FLAG)
VALUES
(REC_CUR_MTL_SYS_ITEM.INVENTORY_ITEM_ID,
REC_CUR_MTL_SYS_ITEM.ORGANIZATION_ID,
SYSDATE,
FND_PROFILE.VALUE('USER_ID'),
SYSDATE,
FND_PROFILE.VALUE('USER_ID'),
'-1',
REC_CUR_MTL_SYS_ITEM.SUMMARY_FLAG,
REC_CUR_MTL_SYS_ITEM.ENABLED_FLAG,
REC_CUR_MTL_SYS_ITEM.DESCRIPTION,
REC_CUR_MTL_SYS_ITEM.SEGMENT1,
REC_CUR_MTL_SYS_ITEM.PURCHASING_ITEM_FLAG,
REC_CUR_MTL_SYS_ITEM.SHIPPABLE_ITEM_FLAG,
REC_CUR_MTL_SYS_ITEM.CUSTOMER_ORDER_FLAG,
REC_CUR_MTL_SYS_ITEM.INTERNAL_ORDER_FLAG,
REC_CUR_MTL_SYS_ITEM.SERVICE_ITEM_FLAG,
REC_CUR_MTL_SYS_ITEM.INVENTORY_ITEM_FLAG,
REC_CUR_MTL_SYS_ITEM.ENG_ITEM_FLAG,
REC_CUR_MTL_SYS_ITEM.INVENTORY_ASSET_FLAG,
REC_CUR_MTL_SYS_ITEM.PURCHASING_ENABLED_FLAG,
REC_CUR_MTL_SYS_ITEM.CUSTOMER_ORDER_ENABLED_FLAG,
REC_CUR_MTL_SYS_ITEM.INTERNAL_ORDER_ENABLED_FLAG,
REC_CUR_MTL_SYS_ITEM.SO_TRANSACTIONS_FLAG,
REC_CUR_MTL_SYS_ITEM.MTL_TRANSACTIONS_ENABLED_FLAG,
REC_CUR_MTL_SYS_ITEM.STOCK_ENABLED_FLAG,
REC_CUR_MTL_SYS_ITEM.BOM_ENABLED_FLAG,
REC_CUR_MTL_SYS_ITEM.BUILD_IN_WIP_FLAG,
REC_CUR_MTL_SYS_ITEM.LIST_PRICE_PER_UNIT,
REC_CUR_MTL_SYS_ITEM.EXPENSE_ACCOUNT,
REC_CUR_MTL_SYS_ITEM.BOM_ITEM_TYPE,
REC_CUR_MTL_SYS_ITEM.PICK_COMPONENTS_FLAG,
REC_CUR_MTL_SYS_ITEM.REPLENISH_TO_ORDER_FLAG,
REC_CUR_MTL_SYS_ITEM.ATP_COMPONENTS_FLAG,
REC_CUR_MTL_SYS_ITEM.ATP_FLAG,
REC_CUR_MTL_SYS_ITEM.PRIMARY_UOM_CODE,
REC_CUR_MTL_SYS_ITEM.INVENTORY_ITEM_STATUS_CODE,
REC_CUR_MTL_SYS_ITEM.VENDOR_WARRANTY_FLAG,
REC_CUR_MTL_SYS_ITEM.SERVICEABLE_PRODUCT_FLAG,
REC_CUR_MTL_SYS_ITEM.INVOICEABLE_ITEM_FLAG,
REC_CUR_MTL_SYS_ITEM.INVOICE_ENABLED_FLAG,
REC_CUR_MTL_SYS_ITEM.MUST_USE_APPROVED_VENDOR_FLAG,
REC_CUR_MTL_SYS_ITEM.REQUEST_ID,
REC_CUR_MTL_SYS_ITEM.PROGRAM_APPLICATION_ID,
REC_CUR_MTL_SYS_ITEM.PROGRAM_ID,
SYSDATE,
REC_CUR_MTL_SYS_ITEM.OUTSIDE_OPERATION_FLAG,
REC_CUR_MTL_SYS_ITEM.OUTSIDE_OPERATION_UOM_TYPE,
REC_CUR_MTL_SYS_ITEM.COSTING_ENABLED_FLAG,
REC_CUR_MTL_SYS_ITEM.AUTO_CREATED_CONFIG_FLAG,
REC_CUR_MTL_SYS_ITEM.CYCLE_COUNT_ENABLED_FLAG,
'N');
END LOOP;
END XX_INV_ITEM_PROC;

PROCEDURE XX_ITEM_VALIDATE
IS
L_INVENTORY_ITEM_ID MTL_SYSTEM_ITEMS_B.INVENTORY_ITEM_ID%TYPE;
L_ORGANIZATION_ID MTL_SYSTEM_ITEMS_B.ORGANIZATION_ID%TYPE;
L_ORGANIZATION_CODE MTL_PARAMETERS.ORGANIZATION_CODE%TYPE;
L_SEGMENT1 MTL_SYSTEM_ITEMS_B.SEGMENT1%TYPE;
L_PRIMARY_UOM_CODE MTL_SYSTEM_ITEMS_B.PRIMARY_UOM_CODE%TYPE;
L_ERROR_MESSAGE VARCHAR2(4000);
CURSOR CUR_ITEM
IS
SELECT A.*,ROWID
FROM XX_INV_ITEM_STG A
WHERE STATUS_FLAG='N';
BEGIN
FOR REC_CUR_ITEM IN CUR_ITEM
LOOP
L_ORGANIZATION_ID:=NULL;
L_INVENTORY_ITEM_ID:=NULL;
BEGIN
SELECT ORGANIZATION_ID
INTO L_ORGANIZATION_ID
FROM MTL_PARAMETERS
WHERE ORGANIZATION_ID = REC_CUR_ITEM.ORGANIZATION_ID;
UPDATE XX_INV_ITEM_STG
SET ORGANIZATION_ID=L_ORGANIZATION_ID
WHERE ROWID= REC_CUR_ITEM.ROWID;
EXCEPTION
WHEN OTHERS THEN
L_ERROR_MESSAGE:=L_ERROR_MESSAGE || ' ERROR ORGANIZATION ID';
END;

BEGIN
SELECT INVENTORY_ITEM_ID
INTO L_INVENTORY_ITEM_ID
FROM MTL_SYSTEM_ITEMS_B
WHERE SEGMENT1=REC_CUR_ITEM.SEGMENT1
AND ORGANIZATION_ID= L_ORGANIZATION_ID
AND DESCRIPTION = REC_CUR_ITEM.DESCRIPTION ;
UPDATE XX_INV_ITEM_STG
SET INVENTORY_ITEM_ID=L_INVENTORY_ITEM_ID
WHERE ROWID=REC_CUR_ITEM.ROWID;
EXCEPTION
WHEN OTHERS THEN
L_ERROR_MESSAGE:=L_ERROR_MESSAGE || 'ITEM DOES NOT EXIST';
END;
BEGIN
SELECT PRIMARY_UOM_CODE
INTO L_PRIMARY_UOM_CODE
FROM MTL_SYSTEM_ITEMS_B
WHERE INVENTORY_ITEM_ID= REC_CUR_ITEM.INVENTORY_ITEM_ID
AND ORGANIZATION_ID=REC_CUR_ITEM.ORGANIZATION_ID;
EXCEPTION
WHEN OTHERS THEN
L_ERROR_MESSAGE:=L_ERROR_MESSAGE || 'UNIT OF MEASURE DOES NOT EXIST';
END;

IF L_ERROR_MESSAGE IS NULL THEN
UPDATE XX_INV_ITEM_STG
SET STATUS_FLAG = 'P'
WHERE ROWID=REC_CUR_ITEM.ROWID;
ELSE
UPDATE XX_INV_ITEM_STG
SET STATUS_FLAG='E' ,
ERROR_MESSAGE = ERROR_MESSAGE || L_ERROR_MESSAGE
WHERE ROWID=REC_CUR_ITEM.ROWID;
END IF;
END LOOP;
UPDATE XX_INV_ITEM_STG SET SEGMENT1='VOLTAS1' WHERE SEGMENT1='AS54999';
COMMIT;
END XX_ITEM_VALIDATE;

PROCEDURE XX_ITEM_LOAD
IS
L_CREATED_BY NUMBER;
L_UPDATED_BY NUMBER;
L_CREATION_DATE DATE := SYSDATE;
L_LAST_UPDATE_DATE DATE := SYSDATE;
L_USER NUMBER;
L_ORGANIZATION_ID MTL_SYSTEM_ITEMS_B.ORGANIZATION_ID%TYPE;
L_INVENTORY_ITEM_ID MTL_SYSTEM_ITEMS_B.INVENTORY_ITEM_ID%TYPE;
L_REC NUMBER := 0;
L_ERROR_MESSAGE VARCHAR2(4000);

CURSOR CUR_ITEM_LOAD
IS
SELECT ROWID,A.*
FROM XX_INV_ITEM_STG A
WHERE STATUS_FLAG='P';

BEGIN
SELECT USER_ID
INTO L_USER
FROM FND_USER
WHERE UPPER (USER_NAME) = 'OPERATIONS';

DELETE MTL_SYSTEM_ITEMS_INTERFACE;
COMMIT;

FOR REC_CUR_ITEM_LOAD IN CUR_ITEM_LOAD
LOOP
L_ERROR_MESSAGE :=NULL;
BEGIN
INSERT INTO MTL_SYSTEM_ITEMS_INTERFACE
(ORGANIZATION_ID,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_LOGIN,
SUMMARY_FLAG,
ENABLED_FLAG,
DESCRIPTION,
SEGMENT1,
PURCHASING_ITEM_FLAG,
SHIPPABLE_ITEM_FLAG,
CUSTOMER_ORDER_FLAG,
INTERNAL_ORDER_FLAG,
SERVICE_ITEM_FLAG,
INVENTORY_ITEM_FLAG,
ENG_ITEM_FLAG,
INVENTORY_ASSET_FLAG,
PURCHASING_ENABLED_FLAG,
CUSTOMER_ORDER_ENABLED_FLAG,
INTERNAL_ORDER_ENABLED_FLAG,
SO_TRANSACTIONS_FLAG,
MTL_TRANSACTIONS_ENABLED_FLAG,
STOCK_ENABLED_FLAG,
BOM_ENABLED_FLAG,
BUILD_IN_WIP_FLAG,
LIST_PRICE_PER_UNIT,
EXPENSE_ACCOUNT,
BOM_ITEM_TYPE,
PICK_COMPONENTS_FLAG,
REPLENISH_TO_ORDER_FLAG,
ATP_COMPONENTS_FLAG,
ATP_FLAG,
PRIMARY_UOM_CODE,
INVENTORY_ITEM_STATUS_CODE,
VENDOR_WARRANTY_FLAG,
SERVICEABLE_PRODUCT_FLAG,
INVOICEABLE_ITEM_FLAG,
INVOICE_ENABLED_FLAG,
MUST_USE_APPROVED_VENDOR_FLAG,
REQUEST_ID,
PROGRAM_APPLICATION_ID,
PROGRAM_ID,
PROGRAM_UPDATE_DATE,
OUTSIDE_OPERATION_FLAG,
OUTSIDE_OPERATION_UOM_TYPE,
COSTING_ENABLED_FLAG,
AUTO_CREATED_CONFIG_FLAG,
CYCLE_COUNT_ENABLED_FLAG,
PROCESS_FLAG,
TRANSACTION_TYPE)
VALUES
(REC_CUR_ITEM_LOAD.ORGANIZATION_ID,
SYSDATE,
FND_PROFILE.VALUE('USER_ID'),
SYSDATE,
FND_PROFILE.VALUE('USER_ID'),
'-1',
REC_CUR_ITEM_LOAD.SUMMARY_FLAG,
REC_CUR_ITEM_LOAD.ENABLED_FLAG,
REC_CUR_ITEM_LOAD.DESCRIPTION,
REC_CUR_ITEM_LOAD.SEGMENT1,
REC_CUR_ITEM_LOAD.PURCHASING_ITEM_FLAG,
REC_CUR_ITEM_LOAD.SHIPPABLE_ITEM_FLAG,
REC_CUR_ITEM_LOAD.CUSTOMER_ORDER_FLAG,
REC_CUR_ITEM_LOAD.INTERNAL_ORDER_FLAG,
REC_CUR_ITEM_LOAD.SERVICE_ITEM_FLAG,
REC_CUR_ITEM_LOAD.INVENTORY_ITEM_FLAG,
REC_CUR_ITEM_LOAD.ENG_ITEM_FLAG,
REC_CUR_ITEM_LOAD.INVENTORY_ASSET_FLAG,
REC_CUR_ITEM_LOAD.PURCHASING_ENABLED_FLAG,
REC_CUR_ITEM_LOAD.CUSTOMER_ORDER_ENABLED_FLAG,
REC_CUR_ITEM_LOAD.INTERNAL_ORDER_ENABLED_FLAG,
REC_CUR_ITEM_LOAD.SO_TRANSACTIONS_FLAG,
REC_CUR_ITEM_LOAD.MTL_TRANSACTIONS_ENABLED_FLAG,
REC_CUR_ITEM_LOAD.STOCK_ENABLED_FLAG,
REC_CUR_ITEM_LOAD.BOM_ENABLED_FLAG,
REC_CUR_ITEM_LOAD.BUILD_IN_WIP_FLAG,
REC_CUR_ITEM_LOAD.LIST_PRICE_PER_UNIT,
REC_CUR_ITEM_LOAD.EXPENSE_ACCOUNT,
REC_CUR_ITEM_LOAD.BOM_ITEM_TYPE,
REC_CUR_ITEM_LOAD.PICK_COMPONENTS_FLAG,
REC_CUR_ITEM_LOAD.REPLENISH_TO_ORDER_FLAG,
REC_CUR_ITEM_LOAD.ATP_COMPONENTS_FLAG,
REC_CUR_ITEM_LOAD.ATP_FLAG,
REC_CUR_ITEM_LOAD.PRIMARY_UOM_CODE,
REC_CUR_ITEM_LOAD.INVENTORY_ITEM_STATUS_CODE,
REC_CUR_ITEM_LOAD.VENDOR_WARRANTY_FLAG,
REC_CUR_ITEM_LOAD.SERVICEABLE_PRODUCT_FLAG,
REC_CUR_ITEM_LOAD.INVOICEABLE_ITEM_FLAG,
REC_CUR_ITEM_LOAD.INVOICE_ENABLED_FLAG,
REC_CUR_ITEM_LOAD.MUST_USE_APPROVED_VENDOR_FLAG,
REC_CUR_ITEM_LOAD.REQUEST_ID,
REC_CUR_ITEM_LOAD.PROGRAM_APPLICATION_ID,
REC_CUR_ITEM_LOAD.PROGRAM_ID,
SYSDATE,
REC_CUR_ITEM_LOAD.OUTSIDE_OPERATION_FLAG,
REC_CUR_ITEM_LOAD.OUTSIDE_OPERATION_UOM_TYPE,
REC_CUR_ITEM_LOAD.COSTING_ENABLED_FLAG,
REC_CUR_ITEM_LOAD.AUTO_CREATED_CONFIG_FLAG,
REC_CUR_ITEM_LOAD.CYCLE_COUNT_ENABLED_FLAG,
'1',
'CREATE');
EXCEPTION
WHEN OTHERS
THEN
L_ERROR_MESSAGE := L_ERROR_MESSAGE || ' LOAD ERROR' || '-' || SQLERRM;
END;
IF L_ERROR_MESSAGE IS NOT NULL
THEN
UPDATE XX_INV_ITEM_STG
SET STATUS_FLAG = 'E',
ERROR_MESSAGE = ERROR_MESSAGE || L_ERROR_MESSAGE
WHERE ROWID=REC_CUR_ITEM_LOAD.ROWID;
ELSE
UPDATE XX_INV_ITEM_STG
SET STATUS_FLAG='P'
WHERE ROWID=REC_CUR_ITEM_LOAD.ROWID;
END IF;
COMMIT;
L_REC:=L_REC+1;
IF L_REC = 1000
THEN
COMMIT;
L_REC:=0;
END IF;
END LOOP;
END XX_ITEM_LOAD;
END XX_INV_ITEM_PKG;
=======================================================================================

RUN THE IMPORT PROGRAM:
==========================


IMPORT ITEMS

=========================================================================================
Posted by at

No comments:

Post a Comment

Subscribe to: Post Comments (Atom)