CREATE OR REPLACE PACKAGE APPS.ZNINV_ITEM_CONVERSION_PKG AS
PROCEDURE ZNinv_item_validate_proc;
PROCEDURE ZNinv_item_insert_proc;
PROCEDURE ZNINV_ITEM_CONV_MAIN_PROC ( errbuf OUT VARCHAR2,
retcode OUT VARCHAR2,
P_BATCH VARCHAR2
);
END ZNINV_ITEM_CONVERSION_PKG;
/
CREATE OR REPLACE PACKAGE BODY APPS.ZNINV_ITEM_CONVERSION_PKG AS
-- Global Variables
g_user_id fnd_user.user_id%TYPE ;
g_login_id NUMBER(15):=0;
g_item_found NUMBER:=0;
g_item_processed NUMBER:=0;
g_item_rejected NUMBER:=0;
/* -----------------------------------------------------------------------------------------------------------------
Name : ZNinv_item_validate_proc
Description : This is the procedure called by the main procedure to validate the records in the staging tables.
Parameters : None
Returns : None
------------------------------------------------------------------------------------------------------------------*/
PROCEDURE ZNinv_item_validate_proc
AS
/*--------------------------------------------------------------------------------------------------
This cursor will fetch all the records from the staging table ZNinv_mtl_system_items_b_stg
---------------------------------------------------------------------------------------------------*/
CURSOR c_staging_item_create
IS
-- SELECT ROWID msirowid, xmsi.*
-- FROM zninv_mtl_system_items_b_stg xmsi
-- WHERE xmsi.process_status IS NULL;
SELECT BATCH_NAME
,ITEM_NUMBER
,DESCRIPTION
,TEMPLATE_NAME
,ATTRIBUTE_CATEGORY
,ATTRIBUTE1
,ATTRIBUTE2
,ATTRIBUTE3
,ORGANIZATION_CODE
,PRIMARY_UNIT_OF_MEASURE
,ITEM_TYPE
,INVENTORY_ITEM_FLAG
,STOCK_ENABLED_FLAG
,MTL_TRANSACTIONS_ENABLED_FLAG
-- ,RESERVABLE_TYPE --- added for Niger
,SO_TRANSACTIONS_FLAG
,CYCLE_COUNT_ENABLED_FLAG
,COSTING_ENABLED_FLAG
,INVENTORY_ASSET_FLAG
,DEFAULT_INCLUDE_IN_ROLLUP_FLAG
,PURCHASING_ITEM_FLAG
,PURCHASING_ENABLED_FLAG
,ALLOW_ITEM_DESC_UPDATE_FLAG --- added for Niger
,RFQ_REQUIRED_FLAG --- added for Niger
,TAXABLE_FLAG --- added for Niger
,PURCHASING_TAX_CODE --- added for Niger
,RECEIPT_REQUIRED_FLAG --- added for Niger
,UNIT_OF_ISSUE --- added for Niger
,ALLOW_SUBSTITUTE_RECEIPTS_FLAG --- added for Niger
,REPLENISH_TO_ORDER_FLAG --- added for Niger
,SERIAL_NUM_GENERATION
,LIST_PRICE_PER_UNIT
,COST_OF_SALES_ACCOUNT
,ENCUMBRANCE_ACCOUNT
,EXPENSE_ACCOUNT
,SALES_ACCOUNT
,ASSET_CATEGORY_CODE
,RECEIVING_ROUTING
-- ,ENFORCE_SHIP_TO_LOCATION_CODE --- added for Niger
-- ,WEIGHT_UOM_CODE --- added for Niger
-- ,UNIT_WEIGHT --- added for Niger
-- ,VOLUME_UOM_CODE --- added for Niger
--- ,UNIT_VOLUME --- added for Niger
-- ,PLANNING_MAKE_BUY_CODE --- added for Niger
-- ,DEFAULT_SO_SOURCE_TYPE --- added for Niger
,INVENTORY_PLANNING_CODE
,MIN_MINMAX_QUANTITY
,MAX_MINMAX_QUANTITY
,MINIMUM_ORDER_QUANTITY
,MAXIMUM_ORDER_QUANTITY
,SOURCE_TYPE
,SOURCE_ORGANIZATION
,SOURCE_SUBINVENTORY
,PLANNER_CODE
,CUSTOMER_ORDER_FLAG
,CUSTOMER_ORDER_ENABLED_FLAG
,SHIPPABLE_ITEM_FLAG
,INTERNAL_ORDER_FLAG
,INTERNAL_ORDER_ENABLED_FLAG
,RETURNABLE_FLAG
,INVOICEABLE_ITEM_FLAG
,INVOICE_ENABLED_FLAG
,TAX_CODE
,CATEGORY_SET_NAME
,CATEGORY_NAME
,PROCESS_STATUS
,ERROR_MESSAGE
FROM zninv_mtl_system_items_b_stg
WHERE PROCESS_STATUS IS NULL;
---AND BATCH_NAME = P_BATCH;
-- FOR UPDATE OF PROCESS_STATUS, ERROR_MESSAGE;
-- Local Variables
x_process_id NUMBER;
x_error_flag VARCHAR2(1):='N';
x_error_message VARCHAR2(4000);
x_item_err_details VARCHAR2(4000);
x_uom_code NUMBER;
x_uom NUMBER;
x_weight_uom_code NUMBER;
x_item_type fnd_common_lookups.lookup_code%TYPE;
x_cat_set_nm mtl_category_sets.category_set_name%TYPE;
x_cat_set_id mtl_category_sets.category_set_id%TYPE;
x_cat_id NUMBER;
x_structure_id NUMBER;
x_cat_segment1 VARCHAR2(40);
x_organization_code VARCHAR2(3);
x_category_name VARCHAR2(400);
x_organization_id NUMBER;
x_item_count NUMBER;
x_cnt_temp_name NUMBER;
x_cnt_uom NUMBER;
x_cnt_item_type NUMBER;
X_ENCUMB_ACC_ID NUMBER;
X_EXP_ACC_ID NUMBER;
X_SALES_ACC_ID NUMBER;
X_COST_OF_SALES_ACC_ID NUMBER;
x_debug_point NUMBER;
x_cnt_INV_PLAN_CODE NUMBER;
X_CNT_ASSET_CAT_CODE NUMBER;
x_cnt_tax_code NUMBER;
BEGIN --Item Validate Procedure
-- Update zninv_mtl_system_items_b_stg Set category_name = Replace(category_name, chr(13), '')
-- Where process_status IS NULL;
COMMIT;
BEGIN
SELECT COUNT(1)
INTO g_item_found
FROM zninv_mtl_system_items_b_stg
WHERE process_status IS NULL;
EXCEPTION
WHEN OTHERS THEN
fnd_file.put_line (fnd_file.log,'Error: '|| SQLCODE || ' '|| SQLERRM);
END;
---fnd_file.put_line (fnd_file.log,'Number of Items for Migration ='||g_item_found);
g_item_processed :=0;
g_item_rejected :=0;
FOR i IN c_staging_item_create LOOP
x_error_flag:='N';
x_error_message :=NULL;
x_item_err_details:=NULL;
x_uom_code:=0;
x_uom:=0;
x_weight_uom_code:=0;
x_cat_set_nm:=NULL;
x_cat_segment1:=NULL;
x_organization_code:=NULL;
X_ENCUMB_ACC_ID :=NULL;
X_EXP_ACC_ID :=NULL;
X_SALES_ACC_ID :=NULL;
X_COST_OF_SALES_ACC_ID :=NULL;
x_cnt_INV_PLAN_CODE :=NULL;
X_CNT_ASSET_CAT_CODE :=0;
x_organization_id :=0;
x_item_count :=0;
x_cnt_temp_name :=0;
x_cnt_uom :=0;
x_cnt_item_type :=0;
x_cnt_tax_code :=0;
-- x_category_name:=i.cat_seg1||'.'||i.cat_seg2||i.cat_seg3||i.cat_seg4;
x_debug_point:=1;
---fnd_file.put_line(FND_FILE.LOG,'Before Validation starts '|| ' at debug point : ' ||x_debug_point||' '||'x_error_flag -'|| x_error_flag);
/*-------------------------Validation starts for Items-------------------------------*/
x_debug_point:= 2;
---fnd_file.put_line(FND_FILE.LOG,'Before organization code Validation starts '|| ' at debug point : ' ||x_debug_point||' '||'x_error_flag -'|| x_error_flag);
---------------------------Validation for organization code and Fetch the value of x_organization_id ---------------------------
IF i.ORGANIZATION_CODE IS NOT NULL THEN
BEGIN
SELECT organization_id
INTO x_organization_id
FROM ORG_ORGANIZATION_DEFINITIONS
WHERE organization_code = i.ORGANIZATION_CODE;
EXCEPTION
WHEN OTHERS THEN
fnd_file.put_line (fnd_file.log,'organization_id is not defined for the given organization_code -'||i.ORGANIZATION_CODE);
END;
ELSE
x_error_flag := 'Y';
x_error_message := x_error_message||'There is no ORGANIZATION_CODE';
x_item_err_details := 'Error in batch: ' || i.batch_name || ' - ORGANIZATION_CODE: ' || i.ORGANIZATION_CODE || ' - ' || 'is Null';
---fnd_file.put_line (fnd_file.LOG, x_item_err_details);
END IF ;
x_debug_point:= 3;
---fnd_file.put_line(FND_FILE.LOG,'Before Item_number Validation starts '|| ' at debug point : ' ||x_debug_point||' '||'x_error_flag -'|| x_error_flag);
---------------------------Validation for Item_number---------------------------
IF i.item_number IS NULL THEN
x_error_flag := 'Y';
x_error_message := x_error_message||'There is no item_number.';
x_item_err_details := 'Error in batch: ' || i.batch_name || ' -item_number: ' || i.item_number || ' - ' || ' is Null';
---fnd_file.put_line (fnd_file.LOG, x_item_err_details);
ELSE
SELECT COUNT (1)
INTO x_item_count
FROM mtl_system_items_b msi
WHERE msi.organization_id = x_organization_id
AND msi.segment1 = i.item_number;
IF x_item_count>0 THEN
x_error_flag := 'Y';
x_error_message := x_error_message||'Item_number already exists';
x_item_err_details := 'Error in batch: ' || i.batch_name || ' -Item_number: ' || i.Item_number || ' - ' || 'already exists.';
---fnd_file.put_line (fnd_file.LOG, x_item_err_details);
END IF;
END IF ;
---fnd_file.put_line (fnd_file.log,'x_error_flag'||x_error_flag);
x_debug_point:= 4;
---fnd_file.put_line(FND_FILE.LOG,'Before Description Validation starts '|| ' at debug point : ' ||x_debug_point||' '||'x_error_flag - '|| x_error_flag);
--comment here for items updation
---------------------------Validation for Description---------------------------
IF i.DESCRIPTION IS NULL THEN
x_error_flag := 'Y';
x_error_message := x_error_message||'There is no DESCRIPTION.';
x_item_err_details := 'Error in batch: ' || i.batch_name || '- DESCRIPTION: ' || i.DESCRIPTION || ' - ' || ' is Null';
---fnd_file.put_line (fnd_file.LOG, x_item_err_details);
end if;
x_debug_point:= 5;
---fnd_file.put_line(FND_FILE.LOG,'Before ATTRIBUTE_CATEGORY Validation starts '|| ' at debug point : ' ||x_debug_point||' '||'x_error_flag - '|| x_error_flag);
/*-----------------------------------------------------------------------------------------------------------------------------------------------------------
---------------------------Validation for ATTRIBUTE_CATEGORY---------------------------
IF i.ATTRIBUTE_CATEGORY IS NULL THEN
x_error_flag := 'Y';
x_error_message := x_error_message||'There is no ATTRIBUTE_CATEGORY.';
x_item_err_details := 'Error in batch: ' || i.batch_name || '- ATTRIBUTE_CATEGORY: ' || i.ATTRIBUTE_CATEGORY || ' - ' || ' is Null';
---fnd_file.put_line (fnd_file.LOG, x_item_err_details);
END IF;
x_debug_point:= 6;
---fnd_file.put_line(FND_FILE.LOG,'Before ATTRIBUTE1 Validation starts '|| ' at debug point : ' ||x_debug_point||' '||'x_error_flag - '|| x_error_flag);
---------------------------Validation for ATTRIBUTE1---------------------------
IF i.ATTRIBUTE1 IS NULL THEN
x_error_flag := 'Y';
x_error_message := x_error_message||'There is no ATTRIBUTE1 Value.';
x_item_err_details := 'Error in batch: ' || i.batch_name || '- ATTRIBUTE1: ' || i.ATTRIBUTE1 || ' - ' || ' is Null';
---fnd_file.put_line (fnd_file.LOG, x_item_err_details);
END IF;
x_debug_point:= 7;
---fnd_file.put_line(FND_FILE.LOG,'Before ATTRIBUTE2 Validation starts '|| ' at debug point : ' ||x_debug_point||' '||'x_error_flag - '|| x_error_flag);
---------------------------Validation for ATTRIBUTE2---------------------------
IF i.ATTRIBUTE2 IS NULL THEN
x_error_flag := 'Y';
x_error_message := x_error_message||'There is no ATTRIBUTE1 Value.';
x_item_err_details := 'Error in batch: ' || i.batch_name || '- ATTRIBUTE2: ' || i.ATTRIBUTE2 || ' - ' || ' is Null';
---fnd_file.put_line (fnd_file.LOG, x_item_err_details);
END IF;
x_debug_point:= 8;
---fnd_file.put_line(FND_FILE.LOG,'Before ATTRIBUTE2 Validation starts '|| ' at debug point : ' ||x_debug_point||' '||'x_error_flag - '|| x_error_flag);
---------------------------Validation for ATTRIBUTE3---------------------------
IF i.ATTRIBUTE3 IS NULL THEN
x_error_flag := 'Y';
x_error_message := x_error_message||'There is no ATTRIBUTE1 Value.';
x_item_err_details := 'Error in batch: ' || i.batch_name || '- ATTRIBUTE3: ' || i.ATTRIBUTE3 || ' - ' || ' is Null';
---fnd_file.put_line (fnd_file.LOG, x_item_err_details);
END IF;
x_debug_point:= 10;
---fnd_file.put_line(FND_FILE.LOG,'Before Primary Unit of Measure Validation starts '|| ' at debug point : ' ||x_debug_point||' '||'x_error_flag - '|| x_error_flag);
************************************************************************************************************************************************************/
---------------------------Validation for Primary Unit of Measure---------------------------
IF i.PRIMARY_UNIT_OF_MEASURE IS NULL THEN
x_error_flag := 'Y';
x_error_message := x_error_message||'Primary Unit of Measure cannot be NULL.';
x_item_err_details := 'Error in batch: ' || i.batch_name || ' - PRIMARY_UNIT_OF_MEASURE : ' || i.PRIMARY_UNIT_OF_MEASURE || ' - ' || ' is Null';
---fnd_file.put_line (fnd_file.LOG, x_item_err_details);
ELSE SELECT count(1)
INTO x_cnt_uom
FROM MTL_UNITS_OF_MEASURE
WHERE upper(UNIT_OF_MEASURE) = upper(rtrim(ltrim(i.PRIMARY_UNIT_OF_MEASURE)))
AND NVL(TRUNC(disable_date),TRUNC(SYSDATE+1))>TRUNC(SYSDATE);
IF x_cnt_uom = 0 THEN
x_error_flag := 'Y';
x_error_message := x_error_message||'UOM is Invalid';
x_item_err_details := 'Error in batch: ' || i.batch_name || ' -PRIMARY_UNIT_OF_MEASURE: ' || i.PRIMARY_UNIT_OF_MEASURE || ' - ' || 'is Invalid.';
---fnd_file.put_line (fnd_file.LOG, x_item_err_details);
END IF;
END IF;
---fnd_file.put_line (fnd_file.log,'x_error_flag'||x_error_flag);
x_debug_point:= 11;
---fnd_file.put_line(FND_FILE.LOG,'Before ITEM_TYPE Validation starts '|| ' at debug point : ' ||x_debug_point||' '||'x_error_flag - '|| x_error_flag);
---------------------------Validation for Item Type------------------------------
IF i.ITEM_TYPE IS NOT NULL THEN
SELECT COUNT(1)
INTO x_cnt_item_type
FROM FND_COMMON_LOOKUPS
WHERE lookup_type ='ITEM_TYPE'
AND enabled_flag = 'Y'
AND UPPER(TRIM(MEANING)) = UPPER(TRIM(i.ITEM_TYPE))
AND SYSDATE BETWEEN NVL(START_DATE_ACTIVE, SYSDATE-1) AND NVL(END_DATE_ACTIVE, SYSDATE+1);
x_debug_point:= 888;
---fnd_file.put_line(FND_FILE.LOG,'Before Template name Validation starts '|| ' at debug point : ' ||x_debug_point||' '||'x_cnt_item_type - '|| x_cnt_item_type||i.ITEM_TYPE);
IF x_cnt_item_type = 0 THEN
x_error_flag :='Y';
x_error_message := x_error_message ||'Invalid Item Type Lookup Code';
x_item_err_details := 'Error in batch: ' || i.batch_name || ' - Item Type: ' ||i.ITEM_TYPE || ' - ' || ' No Data Found for Item Type Lookup Code';
---fnd_file.put_line (fnd_file.log, x_item_err_details);
END IF;
END IF;
---fnd_file.put_line (fnd_file.log,'x_error_flag'||x_error_flag);
x_debug_point:= 12;
---fnd_file.put_line(FND_FILE.LOG,'Before COST_OF_SALES_ACCOUNT Validation starts '|| ' at debug point : ' ||x_debug_point||' '||'x_error_flag - '|| x_error_flag);
---------------------------Validation for COST_OF_SALES_ACCOUNT------------------------------
IF i.COST_OF_SALES_ACCOUNT IS NOT NULL THEN
BEGIN
SELECT CODE_COMBINATION_ID
INTO X_COST_OF_SALES_ACC_ID
FROM gl_code_combinations
WHERE SEGMENT1||'.'||SEGMENT2||'.'||SEGMENT3||'.'|| SEGMENT4||'.'||SEGMENT5||'.'||SEGMENT6||'.'||SEGMENT7 = i.COST_OF_SALES_ACCOUNT
AND enabled_flag = 'Y'
AND summary_flag = 'N'
AND TRUNC(SYSDATE) BETWEEN TRUNC(NVL(start_date_active,SYSDATE)) AND TRUNC(NVL(end_date_active,SYSDATE));
EXCEPTION
WHEN NO_DATA_FOUND THEN
x_error_flag := 'Y';
x_error_message := x_error_message||'Code Combination ID does not exists for the given COST_OF_SALES_ACCOUNT Value.';
x_item_err_details := 'Error in batch: ' || i.batch_name || '- Code Combination ID does not exists for the given Item: ' || i.Item_number;
WHEN OTHERS THEN
x_error_flag := 'Y';
x_error_message := x_error_message||'Code Combination ID does not exists for the given COST_OF_SALES_ACCOUNT Value.';
x_item_err_details := 'Error in batch: ' || i.batch_name || '- Code Combination ID does not exists for the given Item: ' || i.Item_number;
END;
-- ELSE --- i.COST_OF_SALES_ACCOUNT IS NULL
-- x_error_flag := 'Y';
-- x_error_message := x_error_message||'There is no COST_OF_SALES_ACCOUNT Value.';
-- x_item_err_details := 'Error in batch: ' || i.batch_name || '- COST_OF_SALES_ACCOUNT: ' || i.COST_OF_SALES_ACCOUNT|| ' - ' || ' is Null';
END IF;
x_debug_point:= 13;
---fnd_file.put_line(FND_FILE.LOG,'Before LIST_PRICE_PER_UNIT Validation starts '|| ' at debug point : ' ||x_debug_point||' '||'x_error_flag - '|| x_error_flag);
---------------------------Validation for LIST_PRICE_PER_UNIT------------------------------
/******************************************************************************************
IF i.LIST_PRICE_PER_UNIT IS NULL THEN
x_error_flag := 'Y';
x_error_message := x_error_message||'There is no LIST_PRICE_PER_UNIT.';
x_item_err_details := 'Error in batch: ' || i.batch_name || ' -LIST_PRICE_PER_UNIT: ' || i.LIST_PRICE_PER_UNIT || ' - ' || ' is Null';
---fnd_file.put_line (fnd_file.LOG, x_item_err_details);
END IF;
x_debug_point:= 14;
---fnd_file.put_line(FND_FILE.LOG,'Before ENCUMBRANCE_ACCOUNT Validation starts '|| ' at debug point : ' ||x_debug_point||' '||'x_error_flag - '|| x_error_flag);
******************************************************************************************/
---------------------------Validation for ENCUMBRANCE_ACCOUNT------------------------------
IF i.ENCUMBRANCE_ACCOUNT IS NOT NULL THEN
BEGIN
SELECT CODE_COMBINATION_ID
INTO X_ENCUMB_ACC_ID
FROM gl_code_combinations
WHERE SEGMENT1||'.'||SEGMENT2||'.'||SEGMENT3||'.'|| SEGMENT4||'.'||SEGMENT5||'.'||SEGMENT6||'.'||SEGMENT7 = i.ENCUMBRANCE_ACCOUNT
AND enabled_flag = 'Y'
AND summary_flag = 'N'
AND TRUNC(SYSDATE) BETWEEN TRUNC(NVL(start_date_active,SYSDATE)) AND TRUNC(NVL(end_date_active,SYSDATE));
EXCEPTION
WHEN NO_DATA_FOUND THEN
x_error_flag := 'Y';
x_error_message := x_error_message||'Code Combination ID does not exists for the given ENCUMBRANCE_ACCOUNT Value.';
x_item_err_details := 'Error in batch: ' || i.batch_name || '- Code Combination ID does not exists for the given Item: ' || i.Item_number;
WHEN OTHERS THEN
x_error_flag := 'Y';
x_error_message := x_error_message||'Code Combination ID does not exists for the given ENCUMBRANCE_ACCOUNT Value.';
x_item_err_details := 'Error in batch: ' || i.batch_name || '- Code Combination ID does not exists for the given Item: ' || i.Item_number;
END;
-- ELSE --- i.ENCUMBRANCE_ACCOUNT IS NULL
-- x_error_flag := 'Y';
-- x_error_message := x_error_message||'There is no ENCUMBRANCE_ACCOUNT Value.';
-- x_item_err_details := 'Error in batch: ' || i.batch_name || '- ENCUMBRANCE_ACCOUNT: ' || i.ENCUMBRANCE_ACCOUNT|| ' - ' || ' is Null';
END IF;
x_debug_point:= 15;
---fnd_file.put_line(FND_FILE.LOG,'Before EXPENSE_ACCOUNT Validation starts '|| ' at debug point : ' ||x_debug_point||' '||'x_error_flag - '|| x_error_flag);
---------------------------Validation for EXPENSE_ACCOUNT------------------------------
IF i.EXPENSE_ACCOUNT IS NOT NULL THEN
BEGIN
SELECT CODE_COMBINATION_ID
INTO X_EXP_ACC_ID
FROM gl_code_combinations
WHERE SEGMENT1||'.'||SEGMENT2||'.'||SEGMENT3||'.'|| SEGMENT4||'.'||SEGMENT5||'.'||SEGMENT6||'.'||SEGMENT7 = i.EXPENSE_ACCOUNT
AND enabled_flag = 'Y'
AND summary_flag = 'N'
AND TRUNC(SYSDATE) BETWEEN TRUNC(NVL(start_date_active,SYSDATE)) AND TRUNC(NVL(end_date_active,SYSDATE));
EXCEPTION
WHEN NO_DATA_FOUND THEN
x_error_flag := 'Y';
x_error_message := x_error_message||'Code Combination ID does not exists for the given EXPENSE_ACCOUNT Value.';
x_item_err_details := 'Error in batch: ' || i.batch_name || '- Code Combination ID does not exists for the given Item: ' || i.Item_number;
WHEN OTHERS THEN
x_error_flag := 'Y';
x_error_message := x_error_message||'Code Combination ID does not exists for the given EXPENSE_ACCOUNT Value.';
x_item_err_details := 'Error in batch: ' || i.batch_name || '- Code Combination ID does not exists for the given Item: ' || i.Item_number;
END;
-- ELSE --- i.EXPENSE_ACCOUNT IS NULL
-- x_error_flag := 'Y';
-- x_error_message := x_error_message||'There is no EXPENSE_ACCOUNT Value.';
-- x_item_err_details := 'Error in batch: ' || i.batch_name || '- EXPENSE_ACCOUNT: ' || i.EXPENSE_ACCOUNT|| ' - ' || ' is Null';
END IF;
x_debug_point:= 16;
---fnd_file.put_line(FND_FILE.LOG,'Before ASSET_CATEGORY_CODE Validation starts '|| ' at debug point : ' ||x_debug_point||' '||'x_error_flag - '|| x_error_flag);
---------------------------Validation for ASSET_CATEGORY_CODE------------------------------
IF i.ASSET_CATEGORY_CODE IS NOT NULL THEN
SELECT COUNT(1)
INTO X_CNT_ASSET_CAT_CODE
FROM fa_categories_b
WHERE UPPER(SEGMENT1)||'.'||UPPER(SEGMENT2)||'.'||UPPER(SEGMENT3)||'.'||UPPER(SEGMENT4) = UPPER(I.ASSET_CATEGORY_CODE)
AND enabled_flag = 'Y'
AND summary_flag = 'N'
AND TRUNC(SYSDATE) BETWEEN TRUNC(NVL(start_date_active,SYSDATE)) AND TRUNC(NVL(end_date_active,SYSDATE));
/**********************************************************************************
IF X_CNT_ASSET_CAT_CODE = 0 THEN
x_error_flag := 'Y';
x_error_message := x_error_message||'Category ID does not exists for the given ASSET_CATEGORY_CODE Value.';
x_item_err_details := 'Error in batch: ' || i.batch_name || '- Category ID does not exists for the given ASSET_CATEGORY_CODE: ' || i.ASSET_CATEGORY_CODE;
end if;
***********************************************************************************/
END IF;
x_debug_point:= 222;
---fnd_file.put_line(FND_FILE.LOG,'Before INVENTORY_PLANNING_CODE Validation starts '|| ' at debug point : ' ||x_debug_point||' '||'x_error_flag - '|| x_error_flag);
---------------------------Validation for INVENTORY_PLANNING_CODE-----------------------
-- IF i.INVENTORY_PLANNING_CODE IS NULL THEN
-- x_error_flag := 'Y';
-- x_error_message := x_error_message||'There is no INVENTORY_PLANNING_CODE.';
-- x_item_err_details := 'Error in batch: ' || i.batch_name || ' -INVENTORY_PLANNING_CODE: ' || i.INVENTORY_PLANNING_CODE || ' - ' || ' is Null';
-- fnd_file.put_line (fnd_file.LOG, x_item_err_details);
IF i.INVENTORY_PLANNING_CODE IS NOT NULL THEN
SELECT COUNT(1)
INTO x_cnt_INV_PLAN_CODE
FROM fnd_lookup_values_vl lvl
WHERE UPPER(TRIM(lvl.lookup_type)) = UPPER(TRIM('MTL_MATERIAL_PLANNING'))
AND UPPER(TRIM(lvl.MEANING)) = UPPER(TRIM(I.INVENTORY_PLANNING_CODE))
AND ENABLED_FLAG='Y'
AND SYSDATE BETWEEN NVL(lvl.START_DATE_ACTIVE, SYSDATE-1) AND NVL(lvl.END_DATE_ACTIVE, SYSDATE+1);
IF x_cnt_INV_PLAN_CODE = 0 THEN
x_error_flag := 'Y';
x_error_message := x_error_message||'Given INVENTORY_PLANNING_CODE is not defined in the lookups.';
x_item_err_details := 'Error in batch: ' || i.batch_name || '- INVENTORY_PLANNING_CODE is not defined in the lookups: ' || i.INVENTORY_PLANNING_CODE;
END IF;
END IF;
x_debug_point:= 17;
---fnd_file.put_line(FND_FILE.LOG,'Before SALES_ACCOUNT Validation starts '|| ' at debug point : ' ||x_debug_point||' '||'x_error_flag - '|| x_error_flag);
--------------------------- Validation for PLANNER_CODE -----------------------
IF i.PLANNER_CODE IS NOT NULL THEN
SELECT COUNT(1)
INTO x_cnt_INV_PLAN_CODE
FROM MTL_PLANNERS
WHERE PLANNER_CODE = i.PLANNER_CODE
AND ORGANIZATION_ID = x_organization_id
AND SYSDATE < NVL(DISABLE_DATE, SYSDATE+1);
IF x_cnt_INV_PLAN_CODE = 0 THEN
x_error_flag := 'Y';
x_error_message := x_error_message||'PLANNER_CODE is not defined for the organization ' || i.PLANNER_CODE ;
x_item_err_details := 'Error in batch: ' || i.batch_name || '- PLANNER_CODE is not defined for the organization: ' || i.PLANNER_CODE;
END IF;
END IF;
--------------------------- Validation for TAX_CODE -----------------------
IF i.TAX_CODE IS NOT NULL THEN
SELECT COUNT(1)
INTO x_cnt_tax_code
FROM ZX_RATES_B ZRB
WHERE ZRB.TAX_JURISDICTION_CODE IN ('DIRECTION GENERALE DE GE')
AND ZRB.TAX_RATE_CODE = i.TAX_CODE
AND ZRB.ACTIVE_FLAG = 'Y'
AND TRUNC(SYSDATE) BETWEEN ZRB.EFFECTIVE_FROM AND NVL(ZRB.EFFECTIVE_TO, TRUNC(SYSDATE));
IF x_cnt_tax_code = 0 THEN
x_error_flag := 'Y';
x_error_message := x_error_message||'TAX_CODE is not defined for the organization ' || i.TAX_CODE;
x_item_err_details := 'Error in batch: ' || i.batch_name || '- TAX_CODE is not defined for the organization: ' || i.TAX_CODE;
END IF;
END IF;
---------------------------Validation for SALES_ACCOUNT------------------------------
IF i.SALES_ACCOUNT IS NOT NULL THEN
BEGIN
SELECT CODE_COMBINATION_ID
INTO X_SALES_ACC_ID
FROM gl_code_combinations
WHERE SEGMENT1||'.'||SEGMENT2||'.'||SEGMENT3||'.'|| SEGMENT4||'.'||SEGMENT5||'.'||SEGMENT6||'.'||SEGMENT7 = i.SALES_ACCOUNT
AND enabled_flag = 'Y'
AND summary_flag = 'N'
AND TRUNC(SYSDATE) BETWEEN TRUNC(NVL(start_date_active,SYSDATE)) AND TRUNC(NVL(end_date_active,SYSDATE));
EXCEPTION
WHEN NO_DATA_FOUND THEN
x_error_flag := 'Y';
x_error_message := x_error_message||'Code Combination ID does not exists for the given SALES_ACCOUNT Value.';
x_item_err_details := 'Error in batch: ' || i.batch_name || '- Code Combination ID does not exists for the given Item: ' || i.Item_number;
WHEN OTHERS THEN
x_error_flag := 'Y';
x_error_message := x_error_message||'Code Combination ID does not exists for the given SALES_ACCOUNT Value.';
x_item_err_details := 'Error in batch: ' || i.batch_name || '- Code Combination ID does not exists for the given Item: ' || i.Item_number;
END;
END IF;
x_debug_point:= 18;
---fnd_file.put_line(FND_FILE.LOG,'Before Category Set Name Validation starts '|| ' at debug point : ' ||x_debug_point||' '||'x_error_flag - '|| x_error_flag);
---------------------------Validation for Category Set Name------------------------------
IF i.CATEGORY_SET_NAME IS NOT NULL THEN
BEGIN
SELECT category_set_name,
category_set_id,
structure_id
INTO x_cat_set_nm,x_cat_set_id,x_structure_id
FROM mtl_category_sets
WHERE UPPER(TRIM(category_set_name))= UPPER(TRIM(i.CATEGORY_SET_NAME));
EXCEPTION
WHEN NO_DATA_FOUND THEN
x_error_flag :='Y';
x_error_message := x_error_message ||'Invalid Category Set Name';
x_item_err_details := 'Error in batch: ' || i.batch_name || ' - Category Set Name: ' ||i.CATEGORY_SET_NAME || ' - ' || ' No Data Found for Category Set Name';
---fnd_file.put_line (fnd_file.log,x_item_err_details);
WHEN OTHERS THEN
x_error_flag :='Y';
x_error_message := x_error_message || '\' ||SQLCODE || ' '|| SQLERRM;
x_item_err_details := 'Error in batch cat set name: ' || i.batch_name || '-' ||SQLCODE || ' '|| SQLERRM;
---fnd_file.put_line (fnd_file.log,x_item_err_details);
END;
END IF;
x_debug_point:= 19;
---fnd_file.put_line(FND_FILE.LOG,'Before Category Name Validation starts '|| ' at debug point : ' ||x_debug_point||' '||'x_error_flag - '|| x_error_flag);
---------------------------Validation for Category Name------------------------------
/***************************************************************************************************************************************
IF i.CATEGORY_NAME IS NULL THEN
x_error_flag :='Y';
x_error_message := x_error_message || '-'||'Invalid i.CATEGORY_NAME : ' ||x_category_name;
ELSE
BEGIN
SELECT category_id
INTO x_cat_id
FROM mtl_categories_b
-- WHERE UPPER(segment1)||'.'||UPPER(segment2) ||'.'||UPPER(segment3)||'.'||UPPER(segment4) = UPPER(TRIM(i.category_name))
WHERE UPPER(segment1)||'.'||UPPER(segment2) ||'.'||UPPER(segment3)||'.'||UPPER(segment4) LIKE UPPER(TRIM(i.category_name))
AND structure_id = x_structure_id;
EXCEPTION
WHEN NO_DATA_FOUND THEN
x_debug_point:= 999111;
---fnd_file.put_line(FND_FILE.LOG,'iN eXCEPTION '|| ' at debug point : ' ||x_debug_point||' '||'x_cat_id - '|| x_cat_id||i.category_name);
x_error_flag :='Y';
x_error_message := x_error_message ||'Invalid Category Name';
x_item_err_details := 'Error in batch: ' || i.batch_name || ' - Category Name: ' ||i.CATEGORY_NAME || ' No Data Found for Category Name';
---fnd_file.put_line (fnd_file.log,x_item_err_details);
WHEN OTHERS THEN
x_error_flag :='Y';
x_error_message := x_error_message || '\' ||SQLCODE || ' '|| SQLERRM;
x_item_err_details := 'Error in batch cat name: ' || i.batch_name || '-' ||SQLCODE || ' '|| SQLERRM;
---fnd_file.put_line (fnd_file.log, x_item_err_details);
END;
END IF;
x_debug_point:= 20;
---fnd_file.put_line(FND_FILE.LOG,'End of Item Validations '|| ' at debug point : ' ||x_debug_point||' '||'x_error_flag - '|| x_error_flag);
*******************************************************************************************************************************************/
IF i.CATEGORY_NAME IS NOT NULL THEN
BEGIN
SELECT category_id
INTO x_cat_id
FROM mtl_categories_kfv
WHERE UPPER(concatenated_segments) = UPPER(i.category_name)
-- WHERE UPPER(segment1)||'.'||UPPER(segment2) ||'.'||UPPER(segment3)||'.'||UPPER(segment4) LIKE UPPER(TRIM(i.category_name))
AND structure_id = x_structure_id;
EXCEPTION
WHEN NO_DATA_FOUND THEN
x_debug_point:= 999111;
---fnd_file.put_line(FND_FILE.LOG,'iN eXCEPTION '|| ' at debug point : ' ||x_debug_point||' '||'x_cat_id - '|| x_cat_id||i.category_name);
x_error_flag :='Y';
x_error_message := x_error_message ||'Invalid Category Name';
x_item_err_details := 'Error in batch: ' || i.batch_name || ' - Category Name: ' ||i.CATEGORY_NAME || ' No Data Found for Category Name';
---fnd_file.put_line (fnd_file.log,x_item_err_details);
WHEN OTHERS THEN
x_error_flag :='Y';
x_error_message := x_error_message || '\' ||SQLCODE || ' '|| SQLERRM;
x_item_err_details := 'Error in batch cat name: ' || i.batch_name || '-' ||SQLCODE || ' '|| SQLERRM;
---fnd_file.put_line (fnd_file.log, x_item_err_details);
END;
END IF;
x_debug_point:= 20;
---fnd_file.put_line(FND_FILE.LOG,'End of Item Validations '|| ' at debug point : ' ||x_debug_point||' '||'x_error_flag - '|| x_error_flag);
---------------------------Validation Ends for Items----------------------------------
-----*** If there is an error in Items then update Items staging tables*****----
---fnd_file.put_line (fnd_file.log,'x_error_flag'||x_error_flag);
IF NVL(x_error_flag,'N')='Y' THEN
UPDATE zninv_mtl_system_items_b_stg
SET PROCESS_STATUS = 'E',
ERROR_MESSAGE = SUBSTR(x_item_err_details,1,2000)
-- WHERE ROWID = i.msirowid;
-- WHERE CURRENT OF c_staging_item_create;
WHERE BATCH_NAME = i.BATCH_NAME
AND ITEM_NUMBER = i.ITEM_NUMBER
and organization_code = i.organization_code;
g_item_rejected := g_item_rejected + 1;
ELSE -- IF x_error_flag = 'N'
UPDATE zninv_mtl_system_items_b_stg
SET PROCESS_STATUS ='I'
-- WHERE ROWID = i.msirowid;
-- WHERE CURRENT OF c_staging_item_create;
WHERE BATCH_NAME = i.BATCH_NAME
AND ITEM_NUMBER = i.ITEM_NUMBER
and organization_code = i.organization_code;
g_item_processed := g_item_processed + 1;
END IF;
x_debug_point:= 21;
---fnd_file.put_line(FND_FILE.LOG,'After updating staging table with Process Flag : '|| ' at debug point : ' ||x_debug_point);
COMMIT;
x_debug_point:= 22;
---fnd_file.put_line(FND_FILE.LOG,'After commiting : '|| ' at debug point : ' ||x_debug_point);
END LOOP;
x_debug_point:= 23;
---fnd_file.put_line(FND_FILE.LOG,'After looping though all records : '|| ' at debug point : ' ||x_debug_point);
END ZNinv_item_validate_proc;
PROCEDURE ZNinv_item_insert_proc AS
/*--------------------------------------------------------------------------------------
Name : ZNinv_item_insert_proc
Description : This is the procedure called by the main procedure to insert
records into the interface tables after validation.
Parameters : None
Returns : None
---------------------------------------------------------------------------------------*/
g_user_id NUMBER := fnd_global.user_id;
g_login_id NUMBER := fnd_global.user_id;
--x_item_err_details VARCHAR2(4000);
-- CURSOR c_insert_item IS
-- SELECT xmsi.ROWID msirowid, xmsi.*
-- FROM zninv_mtl_system_items_b_stg xmsi
-- WHERE xmsi.PROCESS_STATUS ='I';
CURSOR c_insert_item IS
SELECT DISTINCT BATCH_NAME
,ITEM_NUMBER
,DESCRIPTION
,TEMPLATE_NAME
,ATTRIBUTE_CATEGORY
,ATTRIBUTE1
,ATTRIBUTE2
,ATTRIBUTE3
,ORGANIZATION_CODE
,PRIMARY_UNIT_OF_MEASURE
,ITEM_TYPE
,INVENTORY_ITEM_FLAG
,STOCK_ENABLED_FLAG
,MTL_TRANSACTIONS_ENABLED_FLAG
-- ,RESERVABLE_TYPE --- added for Niger
,SO_TRANSACTIONS_FLAG
,CYCLE_COUNT_ENABLED_FLAG
,COSTING_ENABLED_FLAG
,INVENTORY_ASSET_FLAG
,DEFAULT_INCLUDE_IN_ROLLUP_FLAG
,PURCHASING_ITEM_FLAG
,PURCHASING_ENABLED_FLAG
,ALLOW_ITEM_DESC_UPDATE_FLAG --- added for Niger
,RFQ_REQUIRED_FLAG --- added for Niger
,TAXABLE_FLAG --- added for Niger
,PURCHASING_TAX_CODE --- added for Niger
,RECEIPT_REQUIRED_FLAG --- added for Niger
,UNIT_OF_ISSUE --- added for Niger
,ALLOW_SUBSTITUTE_RECEIPTS_FLAG --- added for Niger
,REPLENISH_TO_ORDER_FLAG --- added for Niger
,SERIAL_NUM_GENERATION
,LIST_PRICE_PER_UNIT
,COST_OF_SALES_ACCOUNT
,ENCUMBRANCE_ACCOUNT
,EXPENSE_ACCOUNT
,SALES_ACCOUNT
,ASSET_CATEGORY_CODE
,RECEIVING_ROUTING
-- ,ENFORCE_SHIP_TO_LOCATION_CODE --- added for Niger
-- ,WEIGHT_UOM_CODE --- added for Niger
-- ,UNIT_WEIGHT --- added for Niger
-- ,VOLUME_UOM_CODE --- added for Niger
-- ,UNIT_VOLUME --- added for Niger
-- ,PLANNING_MAKE_BUY_CODE --- added for Niger
-- ,DEFAULT_SO_SOURCE_TYPE --- added for Niger
,INVENTORY_PLANNING_CODE
,MIN_MINMAX_QUANTITY
,MAX_MINMAX_QUANTITY
,MINIMUM_ORDER_QUANTITY
,MAXIMUM_ORDER_QUANTITY
,SOURCE_TYPE
,SOURCE_ORGANIZATION
,SOURCE_SUBINVENTORY
,PLANNER_CODE
,CUSTOMER_ORDER_FLAG
,CUSTOMER_ORDER_ENABLED_FLAG
,SHIPPABLE_ITEM_FLAG
,INTERNAL_ORDER_FLAG
,INTERNAL_ORDER_ENABLED_FLAG
,RETURNABLE_FLAG
,INVOICEABLE_ITEM_FLAG
,INVOICE_ENABLED_FLAG
,TAX_CODE
,PROCESS_STATUS
-- ,MRP_PLANNING_CODE,ASSEMBLE_TO_ORDER_FLAG
FROM zninv_mtl_system_items_b_stg
WHERE NVL(PROCESS_STATUS,'X') ='I';
---and BATCH_NAME = P_BATCH_NAME;
-- FOR UPDATE OF PROCESS_STATUS;
CURSOR c_insert_item_catg (P_BATCH_NAME VARCHAR2, P_ITEM_NUMBER VARCHAR2) IS
SELECT BATCH_NAME
,ITEM_NUMBER
,ORGANIZATION_CODE
,CATEGORY_SET_NAME
,CATEGORY_NAME
FROM zninv_mtl_system_items_b_stg
WHERE BATCH_NAME = P_BATCH_NAME
AND ITEM_NUMBER = P_ITEM_NUMBER
AND CATEGORY_SET_NAME IS NOT NULL
AND CATEGORY_NAME IS NOT NULL;
------------------------------ Local Variables --------------------------------------
x_ins_item_error VARCHAR2(4000);
x_organization_id NUMBER;
X_pri_uom_code VARCHAR2(4000);
X_TEMPLATE_ID VARCHAR2(4000);
X_COST_OF_SALES_ACC_ID NUMBER;
X_ENCUMB_ACC_ID NUMBER;
X_EXP_ACC_ID NUMBER;
X_SALES_ACC_ID NUMBER;
x_cat_set_id NUMBER;
x_structure_id NUMBER;
x_cat_id NUMBER;
x_debug_point NUMBER;
x_inv_plan_code NUMBER;
X_ASSET_CATEGORY_ID NUMBER;
x_item_type_code FND_COMMON_LOOKUPS.LOOKUP_CODE%TYPE;
x_srl_num_control_code MTL_SYSTEM_ITEMS_B.SERIAL_NUMBER_CONTROL_CODE%TYPE;
x_receiving_routing_id MTL_SYSTEM_ITEMS_B.RECEIVING_ROUTING_ID%TYPE;
x_source_type MTL_SYSTEM_ITEMS_B.SOURCE_TYPE%TYPE;
x_src_organization_id NUMBER;
BEGIN
FOR i IN c_insert_item
LOOP
x_ins_item_error := NULL;
x_organization_id := 0;
X_pri_uom_code := NULL;
x_template_id := NULL;
X_COST_OF_SALES_ACC_ID := NULL;
X_ENCUMB_ACC_ID := NULL;
X_EXP_ACC_ID := NULL;
X_SALES_ACC_ID := NULL;
x_inv_plan_code := NULL;
X_ASSET_CATEGORY_ID := NULL;
x_item_type_code := NULL;
x_srl_num_control_code := NULL;
x_receiving_routing_id := NULL;
x_source_type := NULL;
x_src_organization_id := NULL;
x_debug_point:= 31;
---fnd_file.put_line(FND_FILE.LOG,'Loop Begins '|| ' at debug point : ' ||x_debug_point);
--------------------------- Fetch the value of organization_id---------------------------
BEGIN
SELECT organization_id
INTO x_organization_id
FROM ORG_ORGANIZATION_DEFINITIONS
WHERE organization_code = i.ORGANIZATION_CODE;
EXCEPTION
WHEN OTHERS THEN
fnd_file.put_line (fnd_file.log,'organization_id is not defined for the given organization_code -'||i.ORGANIZATION_CODE);
END;
x_debug_point:= 32;
---fnd_file.put_line(FND_FILE.LOG,'After Ftech the value of x_organization_id '|| ' at debug point : ' ||x_debug_point||'-'|| x_organization_id);
--------------------------- Fetch the value of primary_uom_code---------------------------
BEGIN
SELECT UOM_CODE
INTO X_pri_uom_code
FROM MTL_UNITS_OF_MEASURE
WHERE upper(UNIT_OF_MEASURE) = upper(rtrim(ltrim(i.PRIMARY_UNIT_OF_MEASURE)))
AND NVL(TRUNC(disable_date),TRUNC(SYSDATE+1))>TRUNC(SYSDATE);
EXCEPTION
WHEN OTHERS THEN
fnd_file.put_line (fnd_file.log,'primary_uom_code is not defined for the given PRIMARY_UNIT_OF_MEASURE -'||i.PRIMARY_UNIT_OF_MEASURE);
END;
x_debug_point:= 33;
---fnd_file.put_line(FND_FILE.LOG,'After Ftech the value of X_pri_uom_code '|| ' at debug point : ' ||x_debug_point||'-'|| X_pri_uom_code);
/*--------------------------- Fetch the value of x_template_id---------------------------
IF i.template_name IS NOT NULL THEN
BEGIN
SELECT TEMPLATE_ID
INTO x_template_id
FROM mtl_item_templates
WHERE UPPER(TRIM(template_name)) = UPPER(TRIM(i.template_name));
EXCEPTION
WHEN OTHERS THEN
fnd_file.put_line (fnd_file.log,'There is no TEMPLATE_ID for the given template_name -'||i.template_name);
END;
END IF;
x_debug_point:= 34;
---fnd_file.put_line(FND_FILE.LOG,'After Ftech the value of x_template_id '|| ' at debug point : ' ||x_debug_point||'-'||x_template_id);
*/
----------------------------- Fetch the value of X_COST_OF_SALES_ACC_ID---------------------------
IF i.COST_OF_SALES_ACCOUNT IS NOT NULL THEN
BEGIN
SELECT CODE_COMBINATION_ID
INTO X_COST_OF_SALES_ACC_ID
FROM gl_code_combinations
WHERE SEGMENT1||'.'||SEGMENT2||'.'||SEGMENT3||'.'|| SEGMENT4||'.'||SEGMENT5||'.'||SEGMENT6||'.'||SEGMENT7 = i.COST_OF_SALES_ACCOUNT
AND enabled_flag = 'Y'
AND summary_flag = 'N'
AND TRUNC(SYSDATE) BETWEEN TRUNC(NVL(start_date_active,SYSDATE)) AND TRUNC(NVL(end_date_active,SYSDATE));
EXCEPTION
WHEN NO_DATA_FOUND THEN
fnd_file.put_line (fnd_file.log,'Code combination ID does not exists for the given COST_OF_SALES_ACCOUNT -'||i.COST_OF_SALES_ACCOUNT);
WHEN OTHERS THEN
fnd_file.put_line (fnd_file.log,'Code combination ID does not exists for the given COST_OF_SALES_ACCOUNT -'||i.COST_OF_SALES_ACCOUNT);
END;
END IF;
x_debug_point:= 35;
---fnd_file.put_line(FND_FILE.LOG,'After Ftech the value of X_COST_OF_SALES_ACC_ID '|| ' at debug point : ' ||x_debug_point||'-'||X_COST_OF_SALES_ACC_ID);
--------------------------- Fetch the value of X_ENCUMB_ACC_ID---------------------------
IF i.ENCUMBRANCE_ACCOUNT IS NOT NULL THEN
BEGIN
SELECT CODE_COMBINATION_ID
INTO X_ENCUMB_ACC_ID
FROM gl_code_combinations
WHERE SEGMENT1||'.'||SEGMENT2||'.'||SEGMENT3||'.'|| SEGMENT4||'.'||SEGMENT5||'.'||SEGMENT6||'.'||SEGMENT7 = i.ENCUMBRANCE_ACCOUNT
AND enabled_flag = 'Y'
AND summary_flag = 'N'
AND TRUNC(SYSDATE) BETWEEN TRUNC(NVL(start_date_active,SYSDATE)) AND TRUNC(NVL(end_date_active,SYSDATE));
EXCEPTION
WHEN NO_DATA_FOUND THEN
fnd_file.put_line (fnd_file.log,'Code combination ID does not exists for the given ENCUMBRANCE_ACCOUNT -'||i.ENCUMBRANCE_ACCOUNT);
WHEN OTHERS THEN
fnd_file.put_line (fnd_file.log,'Code combination ID does not exists for the given ENCUMBRANCE_ACCOUNT -'||i.ENCUMBRANCE_ACCOUNT);
END;
END IF;
x_debug_point:= 36;
---fnd_file.put_line(FND_FILE.LOG,'After Ftech the value of X_ENCUMB_ACC_ID '|| ' at debug point : ' ||x_debug_point||'-'||X_ENCUMB_ACC_ID);
--------------------------- Fetch the value of X_EXP_ACC_ID---------------------------
IF i.EXPENSE_ACCOUNT IS NOT NULL THEN
BEGIN
SELECT CODE_COMBINATION_ID
INTO X_EXP_ACC_ID
FROM gl_code_combinations
WHERE SEGMENT1||'.'||SEGMENT2||'.'||SEGMENT3||'.'|| SEGMENT4||'.'||SEGMENT5||'.'||SEGMENT6||'.'||SEGMENT7 = i.EXPENSE_ACCOUNT
AND enabled_flag = 'Y'
AND summary_flag = 'N'
AND TRUNC(SYSDATE) BETWEEN TRUNC(NVL(start_date_active,SYSDATE)) AND TRUNC(NVL(end_date_active,SYSDATE));
EXCEPTION
WHEN NO_DATA_FOUND THEN
fnd_file.put_line (fnd_file.log,'Code combination ID does not exists for the given EXPENSE_ACCOUNT -'||i.EXPENSE_ACCOUNT);
WHEN OTHERS THEN
fnd_file.put_line (fnd_file.log,'Code combination ID does not exists for the given EXPENSE_ACCOUNT -'||i.EXPENSE_ACCOUNT);
END;
END IF;
x_debug_point:= 37;
---fnd_file.put_line(FND_FILE.LOG,'After Ftech the value of X_EXP_ACC_ID '|| ' at debug point : ' ||x_debug_point||'-'||X_EXP_ACC_ID);
--------------------------- Fetch the value of ASSET_CATEGORY_ID---------------------------
IF i.ASSET_CATEGORY_CODE IS NOT NULL THEN
BEGIN
SELECT CATEGORY_ID
INTO X_ASSET_CATEGORY_ID
FROM fa_categories_b
WHERE UPPER(SEGMENT1)||'.'||UPPER(SEGMENT2) ||'.'||UPPER(SEGMENT3) ||'.'||UPPER(SEGMENT4) = UPPER(I.ASSET_CATEGORY_CODE)
AND enabled_flag = 'Y'
AND summary_flag = 'N'
AND TRUNC(SYSDATE) BETWEEN TRUNC(NVL(start_date_active,SYSDATE)) AND TRUNC(NVL(end_date_active,SYSDATE));
EXCEPTION
WHEN NO_DATA_FOUND THEN
fnd_file.put_line (fnd_file.log,'CATEGORY_ID does not exists for the given ASSET_CATEGORY_CODE -'||i.ASSET_CATEGORY_CODE);
WHEN OTHERS THEN
fnd_file.put_line (fnd_file.log,'CATEGORY_IDdoes not exists for the given ASSET_CATEGORY_CODE -'||i.ASSET_CATEGORY_CODE);
END;
ELSE
X_ASSET_CATEGORY_ID := NULL;
END IF;
--------------------------- Fetch the value of INVENTORY_PLANNING_CODE---------------------------
IF I.INVENTORY_PLANNING_CODE IS NOT NULL THEN
BEGIN
SELECT lookup_code
INTO x_inv_plan_code
FROM fnd_lookup_values_vl lvl
WHERE UPPER(TRIM(lvl.lookup_type)) = UPPER(TRIM('MTL_MATERIAL_PLANNING'))
AND UPPER(TRIM(lvl.MEANING)) = UPPER(TRIM(I.INVENTORY_PLANNING_CODE))
AND ENABLED_FLAG='Y'
AND SYSDATE BETWEEN NVL(lvl.START_DATE_ACTIVE, SYSDATE-1) AND NVL(lvl.END_DATE_ACTIVE, SYSDATE+1);
EXCEPTION
WHEN NO_DATA_FOUND THEN
fnd_file.put_line (fnd_file.log,'INVENTORY_PLAN_CODE does not exists for the given INVENTORY_PLANNING_CODE -'||i.INVENTORY_PLANNING_CODE);
WHEN OTHERS THEN
fnd_file.put_line (fnd_file.log,'INVENTORY_PLAN_CODE does not exists for the given INVENTORY_PLANNING_CODE -'||i.INVENTORY_PLANNING_CODE);
END;
END IF;
--------------------------- Fetch the value of X_SALES_ACC_ID---------------------------
IF i.SALES_ACCOUNT IS NOT NULL THEN
BEGIN
SELECT CODE_COMBINATION_ID
INTO X_SALES_ACC_ID
FROM gl_code_combinations
WHERE SEGMENT1||'.'||SEGMENT2||'.'||SEGMENT3||'.'|| SEGMENT4||'.'||SEGMENT5||'.'||SEGMENT6||'.'||SEGMENT7 = i.SALES_ACCOUNT
AND enabled_flag = 'Y'
AND summary_flag = 'N'
AND TRUNC(SYSDATE) BETWEEN TRUNC(NVL(start_date_active,SYSDATE)) AND TRUNC(NVL(end_date_active,SYSDATE));
EXCEPTION
WHEN NO_DATA_FOUND THEN
fnd_file.put_line (fnd_file.log,'Code combination ID does not exists for the given SALES_ACCOUNT -'||i.SALES_ACCOUNT);
WHEN OTHERS THEN
fnd_file.put_line (fnd_file.log,'Code combination ID does not exists for the given SALES_ACCOUNT -'||i.SALES_ACCOUNT);
END;
ELSE
X_SALES_ACC_ID := NULL;
END IF;
--------------------------- Fetch the value of x_item_type_code ---------------------------
IF i.ITEM_TYPE IS NOT NULL THEN
BEGIN
SELECT lookup_code
INTO x_item_type_code
FROM FND_COMMON_LOOKUPS
WHERE lookup_type ='ITEM_TYPE'
AND enabled_flag = 'Y'
AND UPPER(TRIM(MEANING)) = UPPER(TRIM(i.ITEM_TYPE))
AND SYSDATE BETWEEN NVL(START_DATE_ACTIVE, SYSDATE-1) AND NVL(END_DATE_ACTIVE, SYSDATE+1);
EXCEPTION
WHEN NO_DATA_FOUND THEN
fnd_file.put_line (fnd_file.log,'Item Type does not exists -'||i.ITEM_TYPE);
WHEN OTHERS THEN
fnd_file.put_line (fnd_file.log,'Invalid Item Type -'||i.ITEM_TYPE);
END;
END IF;
--------------------------- Fetch the value of X_srl_num_control_code ---------------------------
IF i.SERIAL_NUM_GENERATION IS NOT NULL THEN
BEGIN
SELECT TO_NUMBER(LOOKUP_CODE)
INTO x_srl_num_control_code
FROM FND_LOOKUP_VALUES_VL
WHERE LOOKUP_TYPE = 'MTL_SERIAL_NUMBER'
AND ENABLED_FLAG = 'Y'
AND UPPER(TRIM(MEANING)) = UPPER(TRIM(i.SERIAL_NUM_GENERATION))
AND SYSDATE BETWEEN NVL(START_DATE_ACTIVE, SYSDATE-1) AND NVL(END_DATE_ACTIVE, SYSDATE+1);
EXCEPTION
WHEN NO_DATA_FOUND THEN
fnd_file.put_line (fnd_file.log,'Item Serial Number Generation code does not exists -'||i.SERIAL_NUM_GENERATION);
WHEN OTHERS THEN
fnd_file.put_line (fnd_file.log,'Invalid Item Serial Number Generation code -'||i.SERIAL_NUM_GENERATION);
END;
END IF;
--------------------------- Fetch the value of x_receiving_routing_id ---------------------------
IF i.RECEIVING_ROUTING IS NOT NULL THEN
BEGIN
SELECT TO_NUMBER(LOOKUP_CODE)
INTO x_receiving_routing_id
FROM FND_LOOKUP_VALUES_VL
WHERE LOOKUP_TYPE = 'RCV_ROUTING_HEADERS'
AND ENABLED_FLAG = 'Y'
AND UPPER(TRIM(MEANING)) = UPPER(TRIM(i.RECEIVING_ROUTING))
AND SYSDATE BETWEEN NVL(START_DATE_ACTIVE, SYSDATE-1) AND NVL(END_DATE_ACTIVE, SYSDATE+1);
EXCEPTION
WHEN NO_DATA_FOUND THEN
fnd_file.put_line (fnd_file.log,'Receipt Routing code does not exists -'||i.RECEIVING_ROUTING);
WHEN OTHERS THEN
fnd_file.put_line (fnd_file.log,'Invalid Receipt Routing code -'||i.RECEIVING_ROUTING);
END;
END IF;
--------------------------- Fetch the value of x_source_type ---------------------------
IF i.SOURCE_TYPE IS NOT NULL THEN
BEGIN
SELECT TO_NUMBER(LOOKUP_CODE)
INTO x_source_type
FROM FND_LOOKUP_VALUES_VL
WHERE LOOKUP_TYPE = 'MTL_SOURCE_TYPES'
AND ENABLED_FLAG = 'Y'
AND UPPER(TRIM(MEANING)) = UPPER(TRIM(i.SOURCE_TYPE))
AND SYSDATE BETWEEN NVL(START_DATE_ACTIVE, SYSDATE-1) AND NVL(END_DATE_ACTIVE, SYSDATE+1);
EXCEPTION
WHEN NO_DATA_FOUND THEN
fnd_file.put_line (fnd_file.log,'Source Type does not exists -'||i.SOURCE_TYPE);
WHEN OTHERS THEN
fnd_file.put_line (fnd_file.log,'Invalid Source Type -'||i.SOURCE_TYPE);
END;
END IF;
x_debug_point:= 444;
---fnd_file.put_line(FND_FILE.LOG,'Before Inserting the records into mtl_system_items_interface '|| ' at debug point : ' ||x_debug_point);
--------------------------- Fetch the value of x_src_organization_id ---------------------------
BEGIN
SELECT organization_id
INTO x_src_organization_id
FROM ORG_ORGANIZATION_DEFINITIONS
WHERE organization_code = i.SOURCE_ORGANIZATION;
EXCEPTION
WHEN OTHERS THEN
fnd_file.put_line (fnd_file.log,'organization_id is not defined for the given organization_code -'||i.ORGANIZATION_CODE);
END;
-------------------------Inserting into mtl_system_items_interface table--------------------
INSERT INTO mtl_system_items_interface
(organization_id,
organization_code,
last_update_date,
last_updated_by,
creation_date,
created_by,
last_update_login,
description,
segment1,
primary_uom_code,
primary_unit_of_measure,
item_number,
item_type,
-- TEMPLATE_NAME,
-- TEMPLATE_ID,
ATTRIBUTE_CATEGORY,
ATTRIBUTE1,
ATTRIBUTE2,
ATTRIBUTE3,
COST_OF_SALES_ACCOUNT,
LIST_PRICE_PER_UNIT,
ENCUMBRANCE_ACCOUNT,
EXPENSE_ACCOUNT,
ASSET_CATEGORY_ID,
INVENTORY_PLANNING_CODE,
MIN_MINMAX_QUANTITY,
MAX_MINMAX_QUANTITY,
MINIMUM_ORDER_QUANTITY,
MAXIMUM_ORDER_QUANTITY,
SOURCE_TYPE,
SOURCE_ORGANIZATION_ID,
SOURCE_SUBINVENTORY,
PLANNER_CODE,
TAX_CODE,
---PURCHASING_TAX_CODE, ----Added by Hassan to populate Input Tax code in Item Master Screen
SALES_ACCOUNT,
SERIAL_NUMBER_CONTROL_CODE,
RECEIVING_ROUTING_ID,
INVENTORY_ITEM_FLAG,
STOCK_ENABLED_FLAG,
MTL_TRANSACTIONS_ENABLED_FLAG,
-- RESERVABLE_TYPE, --- added for Niger
SO_TRANSACTIONS_FLAG,
CYCLE_COUNT_ENABLED_FLAG,
COSTING_ENABLED_FLAG,
INVENTORY_ASSET_FLAG,
DEFAULT_INCLUDE_IN_ROLLUP_FLAG,
PURCHASING_ITEM_FLAG,
PURCHASING_ENABLED_FLAG,
ALLOW_ITEM_DESC_UPDATE_FLAG, --- added for Niger
RFQ_REQUIRED_FLAG, --- added for Niger
TAXABLE_FLAG, --- added for Niger
PURCHASING_TAX_CODE, --- added for Niger
RECEIPT_REQUIRED_FLAG, --- added for Niger
UNIT_OF_ISSUE, --- added for Niger
ALLOW_SUBSTITUTE_RECEIPTS_FLAG, --- added for Niger
---REPLENISH_TO_ORDER_FLAG,
CUSTOMER_ORDER_FLAG,
CUSTOMER_ORDER_ENABLED_FLAG,
SHIPPABLE_ITEM_FLAG,
INTERNAL_ORDER_FLAG,
INTERNAL_ORDER_ENABLED_FLAG,
RETURNABLE_FLAG,
INVOICEABLE_ITEM_FLAG,
INVOICE_ENABLED_FLAG,
set_process_id,
process_flag,
transaction_type
-- ENFORCE_SHIP_TO_LOCATION_CODE, --- added for Niger
-- WEIGHT_UOM_CODE, --- added for Niger
-- UNIT_WEIGHT, --- added for Niger
-- VOLUME_UOM_CODE, --- added for Niger
-- UNIT_VOLUME, --- added for Niger
-- PLANNING_MAKE_BUY_CODE, --- added for Niger
-- DEFAULT_SO_SOURCE_TYPE, --- added for Niger
--MRP_PLANNING_CODE ---Added for Niger
)
VALUES(x_organization_id,
i.organization_code,
SYSDATE,
g_user_id,
SYSDATE,
g_user_id,
g_login_id,
i.description,
i.ITEM_NUMBER,
X_pri_uom_code,
I.primary_unit_of_measure,
I.ITEM_NUMBER,
-- i.item_type,
x_item_type_code,
-- I.TEMPLATE_NAME,
---x_template_id,
I.ATTRIBUTE_CATEGORY,
I.ATTRIBUTE1,
I.ATTRIBUTE2,
I.ATTRIBUTE3,
X_COST_OF_SALES_ACC_ID,
round(I.LIST_PRICE_PER_UNIT,2),
X_ENCUMB_ACC_ID,
X_EXP_ACC_ID,
X_ASSET_CATEGORY_ID,
x_inv_plan_code,
I.MIN_MINMAX_QUANTITY,
I.MAX_MINMAX_QUANTITY,
I.MINIMUM_ORDER_QUANTITY,
I.MAXIMUM_ORDER_QUANTITY,
-- I.SOURCE_TYPE,
x_source_type,
-- I.SOURCE_ORGANIZATION_ID,
x_src_organization_id,
I.SOURCE_SUBINVENTORY,
I.PLANNER_CODE,
I.TAX_CODE,
---I.TAX_CODE,
X_SALES_ACC_ID,
x_srl_num_control_code,
x_receiving_routing_id,
I.INVENTORY_ITEM_FLAG,
I.STOCK_ENABLED_FLAG,
I.MTL_TRANSACTIONS_ENABLED_FLAG,
---I.RESERVABLE_TYPE,
I.SO_TRANSACTIONS_FLAG,
I.CYCLE_COUNT_ENABLED_FLAG,
I.COSTING_ENABLED_FLAG,
I.INVENTORY_ASSET_FLAG,
I.DEFAULT_INCLUDE_IN_ROLLUP_FLAG,
I.PURCHASING_ITEM_FLAG,
I.PURCHASING_ENABLED_FLAG,
I.ALLOW_ITEM_DESC_UPDATE_FLAG,
I.RFQ_REQUIRED_FLAG,
I.TAXABLE_FLAG,
I.PURCHASING_TAX_CODE,
I.RECEIPT_REQUIRED_FLAG,
I.UNIT_OF_ISSUE,
I.ALLOW_SUBSTITUTE_RECEIPTS_FLAG,
-- I.ASSEMBLE_TO_ORDER_FLAG ,
I.CUSTOMER_ORDER_FLAG,
I.CUSTOMER_ORDER_ENABLED_FLAG,
I.SHIPPABLE_ITEM_FLAG,
I.INTERNAL_ORDER_FLAG,
I.INTERNAL_ORDER_ENABLED_FLAG,
I.RETURNABLE_FLAG,
I.INVOICEABLE_ITEM_FLAG,
I.INVOICE_ENABLED_FLAG,
1,
1,
-- 'UPDATE');
'CREATE'
-- I.ENFORCE_SHIP_TO_LOCATION_CODE,
-- I.WEIGHT_UOM_CODE,
-- I.UNIT_WEIGHT,
-- I.VOLUME_UOM_CODE,
-- I.UNIT_VOLUME,
-- I.PLANNING_MAKE_BUY_CODE,
-- I.DEFAULT_SO_SOURCE_TYPE,I.MRP_PLANNING_CODE
);
x_debug_point:= 666;
---fnd_file.put_line(FND_FILE.LOG, 'After Inserting the records into mtl_system_items_interface '|| ' at debug point : ' ||x_debug_point);
FOR ic IN c_insert_item_catg (I.BATCH_NAME, I.ITEM_NUMBER) LOOP
x_cat_set_id := NULL;
x_structure_id := NULL;
x_cat_id := NULL;
--------------------------- Fetch the value of x_cat_set_id---------------------------
BEGIN
SELECT category_set_id,structure_id
INTO x_cat_set_id, x_structure_id
FROM mtl_category_sets
WHERE category_set_name = ic.CATEGORY_SET_NAME;
EXCEPTION
WHEN NO_DATA_FOUND THEN
fnd_file.put_line (fnd_file.log,'category_set_id does not exists for the given CATEGORY_SET_NAME -'||ic.CATEGORY_SET_NAME);
WHEN OTHERS THEN
fnd_file.put_line (fnd_file.log,'category_set_id does not exists for the given CATEGORY_SET_NAME -'||ic.CATEGORY_SET_NAME);
END;
--------------------------- Fetch the value of x_cat_id---------------------------
BEGIN
/***********************************************************************************************************************************************
SELECT category_id
INTO x_cat_id
FROM mtl_categories_b
WHERE UPPER(segment1)||'.'||UPPER(segment2) ||'.'||UPPER(segment3)||'.'||UPPER(segment4) = UPPER(TRIM(ic.category_name))
AND structure_id = x_structure_id;
***********************************************************************************************************************************************/
SELECT category_id
INTO x_cat_id
FROM mtl_categories_kfv
WHERE UPPER(concatenated_segments) = UPPER(ic.category_name)
-- WHERE UPPER(segment1)||'.'||UPPER(segment2) ||'.'||UPPER(segment3)||'.'||UPPER(segment4) LIKE UPPER(TRIM(i.category_name))
AND structure_id = x_structure_id;
EXCEPTION
WHEN NO_DATA_FOUND THEN
fnd_file.put_line (fnd_file.log,'category_id does not exists for the given CATEGORY_SET_NAME -'||ic.CATEGORY_SET_NAME);
WHEN OTHERS THEN
fnd_file.put_line (fnd_file.log,'category_id does not exists for the given CATEGORY_SET_NAME -'||ic.CATEGORY_SET_NAME);
END;
-------------------------Inserting into mtl_item_categories_interface table--------------------
INSERT INTO mtl_item_categories_interface
(inventory_item_id,
category_set_id,
category_id,
last_update_date,
last_updated_by,
creation_date,
created_by,
last_update_login,
-- request_id,
-- program_application_id,
-- program_id,
-- program_update_date,
organization_id,
transaction_id,
category_set_name,
category_name,
organization_code,
item_number,
transaction_type,
process_flag,
set_process_id
)
VALUES (NULL,
x_cat_set_id,
x_cat_id,
SYSDATE,
g_user_id,
SYSDATE,
g_user_id,
g_login_id,
-- NULL,
-- NULL,
-- NULL,
-- NULL,
x_organization_id,
NULL,
ic.category_set_name,
ic.category_name,
ic.organization_code,
ic.item_number,
'CREATE',
1,
1);
x_debug_point:= 777;
---fnd_file.put_line(FND_FILE.LOG,'After Inserting the records into mtl_item_categories_interface '|| ' at debug point : ' ||x_debug_point);
END LOOP; -- End of Item Category Loop
UPDATE ZNinv_mtl_system_items_b_stg
SET PROCESS_STATUS ='C'
-- WHERE ROWID = i.msirowid;
-- WHERE CURRENT OF c_insert_item;
WHERE ITEM_NUMBER = I.ITEM_NUMBER
AND BATCH_NAME = I.BATCH_NAME;
COMMIT;
END LOOP; -- End of Item Loop
END ZNinv_item_insert_proc;
----------------------------------Main Procedure----------------------------------
PROCEDURE ZNINV_ITEM_CONV_MAIN_PROC (
errbuf OUT VARCHAR2,
retcode OUT VARCHAR2,
P_BATCH VARCHAR2
) AS
BEGIN
-- Call the validate procedure
ZNinv_item_validate_proc;
-- Call the Insert Procedure
ZNinv_item_insert_proc;
fnd_file.put_line(fnd_file.OUTPUT,'Number of Records found for items : '||g_item_found);
fnd_file.put_line(fnd_file.OUTPUT,'Number of Records processed for items : '||g_item_processed);
fnd_file.put_line(fnd_file.OUTPUT,'Number of Records rejected for items : '||g_item_rejected);
END ZNINV_ITEM_CONV_MAIN_PROC;
END ZNINV_ITEM_CONVERSION_PKG;
/
