Run Item Import Program with the generated Process id from the below program after program is completed successfull.
Get Field
-----
FUNCTION get_field(p_line IN VARCHAR2, p_delimiter IN VARCHAR2, p_field_no IN NUMBER) RETURN VARCHAR2 IS
l_start NUMBER :=0;
l_end NUMBER :=0;
lc_delimiter VARCHAR2(10);
BEGIN
lc_delimiter := p_delimiter;
/*****-- Determine start position--*****/
IF p_field_no = 1 THEN
l_start :=0;
ELSE
l_start :=INSTR(p_line,p_delimiter,1,(p_field_no - 1));
IF l_start = 0 THEN
RETURN NULL;
END IF;
END IF;
/*****-- Determine end position--*****/
l_end :=INSTR(p_line,p_delimiter,1,p_field_no);
IF l_end = 0 THEN
l_end := LENGTH(p_line) + 1;
END IF;
/*****-- Extract the field data --*****/
IF (l_end - l_start) = 1 THEN
RETURN NULL;
ELSE
RETURN SUBSTR(p_line,(l_start + 1),((l_end - l_start) - 1));
END IF;
EXCEPTION
WHEN OTHERS THEN
RETURN NULL;
END get_field;
--------------------------------------------------------------------------------------------------------
PROCEDURE mtl_inv_acct_upload(Errbuf OUT VARCHAR2,Retcode OUT NUMBER,p_file_name IN VARCHAR2,P_LINE_NUM IN NUMBER)
IS
reqfiletype UTL_FILE.FILE_TYPE;
req_line VARCHAR2(3000);
req_delim VARCHAR2(1):=',';
rec_count NUMBER;
v_File_Name VARCHAR2(200):=P_File_Name;
v_date_processed DATE; -- (Q)
ln_user_id NUMBER:=FND_GLOBAL.USER_ID;
ln_resp_id NUMBER:=FND_GLOBAL.RESP_ID;
ln_resp_app_id NUMBER:=FND_GLOBAL.RESP_APPL_ID;
v_request_id NUMBER:=0;
ln_batch_id NUMBER;
Lc_Transaction_type VARCHAR2(500);
ln_line_no NUMBER;
ln_record_id NUMBER;
ln_last_updated_by NUMBER;
ln_conc_req_id NUMBER;
ln_login_id NUMBER;
lc_error_flag VARCHAR2(10);
lc_errors_all VARCHAR2(500);
ln_cnt NUMBER;
lc_error_message VARCHAR2(500);
ln_operating_org_id NUMBER;
lc_status VARCHAR2(50);
ln_org_id NUMBER;
lc_error_msg VARCHAR2(500);
ln_request_id NUMBER;
lc_item_no VARCHAR2(50);
ln_item_id NUMBER;
lc_item_desc VARCHAR2(500);
lc_whse_code VARCHAR2(10);
ln_whse_id NUMBER;
lc_acct_comb VARCHAR2(500);
lc_loc VARCHAR2(10);
ln_rule_id NUMBER;
ln_process_id NUMBER;
ln_ccid NUMBER;
lc_rev_concat_segments VARCHAR2(500);
ln_chart_of_account_id NUMBER;
lc_record_status VARCHAR2(20);
lc_item_sts_code VARCHAR2(20);
lc_item_type VARCHAR2(20);
lc_product_code VARCHAR2(20);
lc_lob VARCHAR2(20);
BEGIN
v_file_name:=p_file_name;
reqfiletype := UTL_FILE.FOPEN('MTL_INV_ACCT_UPD', v_File_Name, 'r');
ln_line_no :=0;
---Process id for import items interface
Begin
Select to_char(sysdate,'DDMMYYYYHH24MISS') into ln_process_id from dual;
End;
LOOP
ln_line_no := ln_line_no + 1;
BEGIN
UTL_FILE.GET_LINE(reqfiletype , req_line);
EXCEPTION
WHEN NO_DATA_FOUND THEN
EXIT;
END;
IF ln_line_no > p_line_num THEN
---------------------------------------------------------------------------------------------
--- Mapping with csv fields
-----------------------------------------------------------------------------------------------
--Item Code
BEGIN
lc_item_no := TRIM(get_field(req_line,req_delim,1)); -- (A)
EXCEPTION
WHEN OTHERs THEN
RAISE_APPLICATION_ERROR(-20057,'Item Number '||SQLERRM);
END;
-----------------------------------------------------------------------------------------------
--Item Description
BEGIN
lc_item_desc := TRIM(get_field(req_line,req_delim,2)); -- (B)
EXCEPTION
WHEN OTHERs THEN
RAISE_APPLICATION_ERROR(-20057,'Item Description '||SQLERRM);
END;
-----------------------------------------------------------------
-- Whse Code
BEGIN
lc_whse_code := TRIM(get_field(req_line,req_delim,3)); -- (C)
EXCEPTION
WHEN OTHERs THEN
RAISE_APPLICATION_ERROR(-20057,'Whse Code '||SQLERRM);
END;
-----------------------------------------------------------------
-- Account
BEGIN
lc_acct_comb := TRIM(get_field(req_line,req_delim,4)); -- (D)
EXCEPTION
WHEN OTHERs THEN
RAISE_APPLICATION_ERROR(-20057,'Acct Comb '||SQLERRM);
END;
-----------------------------------------------------------------
---- Record Status
BEGIN
lc_record_status := TRIM(get_field(req_line,req_delim,5)); -- (E)
EXCEPTION
WHEN OTHERs THEN
RAISE_APPLICATION_ERROR(-20057,'Start Period '||SQLERRM);
END;
----------------------------------------------------------------
BEGIN
ln_user_id := apps.fnd_profile.value('USER_ID');
IF ln_user_id IS NULL THEN
ln_user_id := -1;
END IF;
ln_last_updated_by := APPS.FND_GLOBAL.USER_ID;
-- To get the Conc Request ID
ln_conc_req_id := APPS.FND_GLOBAL.CONC_REQUEST_ID;
IF ln_conc_req_id IS NULL THEN
ln_conc_req_id := -1;
END IF;
-- To get the Login ID
ln_login_id := APPS.FND_GLOBAL.LOGIN_ID;
IF ln_login_id IS NULL THEN
ln_login_id := -1;
END IF;
-- To get the Set of Book ID
--ln_set_of_book_id := fnd_profile.VALUE ('GL_SET_OF_BKS_ID');
-- To get the Operating Uni ID
ln_operating_org_id := fnd_profile.VALUE ('ORG_ID');
-- Start to Process the Cursor Records
---Validations for Account update in MTL Item Master
/******************************************************/
-- Validation for organization code
/******************************************************/
Begin
SELECT organization_id into ln_whse_id
FROM mtl_parameters
WHERE organization_code = lc_whse_code;
Exception When Others Then
apps.fnd_file.put_line(apps.fnd_file.LOG,'Whse Code Either Does Not Exist or is inactive in Inventory Organization Master -'||lc_whse_code );
Retcode := 2;
Return;
End;
--
/******************************************************/
-- Validation for item code
/******************************************************/
Begin
Select inventory_item_id into ln_item_id
From mtl_system_items_b
Where organization_id = ln_whse_id
And segment1 = lc_item_no;
Exception
When Others then
apps.fnd_file.put_line(apps.fnd_file.LOG,'Item Code - '||ln_item_id||' either does not exist or is inactive in Inventory Item master for warehouse - '||lc_whse_code);
retcode:=2;
Return;
End;
/******************************************************/
-- Product code validation
/******************************************************/
Begin
Select flex_value into lc_product_code
From fnd_flex_values_vl
Where flex_value_set_id = 1005239
And Trim(substr(Description,instr(Description,'-',-1)+1)) = Trim(lc_item_no);
Exception
When Others then
apps.fnd_file.put_line(apps.fnd_file.LOG,'Item Product Code does not exist in key flexfield setup -'||lc_item_no);
retcode:=2;
Return;
End;
/******************************************************/
--Cross Validation checkinf for LOB and PRODUCT
/******************************************************/
Begin
Select rule_line_id into ln_rule_id
From fnd_flex_validation_rule_lines
Where flex_validation_rule_name like 'LOB & Product'
And substr(concatenated_segments_high,3,3) = Substr(lc_acct_comb,6,3)
And substr(concatenated_segments_high,instr(concatenated_segments_high,'.',-2)+1,5) = Substr(lc_acct_comb,25,5);
Exception
When Others then
apps.fnd_file.put_line(apps.fnd_file.LOG,'Cross validation rule does not exist for lob - '||Substr(lc_acct_comb,6,3)||' and product code - '||Substr(lc_acct_comb,25,5));
retcode:=2;
Return;
End;
/******************************************************/
-- Location Validation
/******************************************************/
Begin
Select substr(acctg_unit_no,6) into lc_loc
From gl_accu_mst gat,
gl_accu_map gap
Where gap.acctg_unit_id = gat.acctg_unit_id
And gap.whse_code = lc_whse_code;
Exception
When Others then
apps.fnd_file.put_line(apps.fnd_file.LOG,'Please check LOC in MAC account combination - '||lc_whse_code);
retcode:=2;
Return;
End;
--
If lc_loc <> Substr(lc_acct_comb,10,3) Then
apps.fnd_file.put_line(apps.fnd_file.LOG,'Please check LOC in account combination,Either it does not exist in MAC or wrong in sheet - '||lc_whse_code);
retcode:=2;
Return;
End If;
/******************************************************/
-- LOB validation
/******************************************************/
Begin
Select segment5 into lc_lob
From MTL_ITEM_CATEGORIES_V
Where CATEGORY_SET_ID = 1100000021
And inventory_item_id = ln_item_id
And organization_id = 23;
Exception
When Others then
apps.fnd_file.put_line(apps.fnd_file.LOG,'LOB does not exist in Item Category -'||ln_item_id);
retcode:=2;
Return;
End;
--
IF lc_lob <> Substr(lc_acct_comb,6,3) THEN
apps.fnd_file.put_line(apps.fnd_file.LOG,'LOB Mismatch with LOB in Item Category of Inventory Item Master -'||lc_lob||' for item id -'||ln_item_id);
retcode:=2;
Return;
End If;
/******************************************************/
--Account combination creation using API
/******************************************************/
Begin
ln_ccid := fnd_flex_ext.get_ccid
(application_short_name => 'SQLGL',
key_flex_code => 'GL#',
structure_number => 50186,
validation_date => TO_CHAR(SYSDATE,'DD-MON-YYYY'),
concatenated_segments => lc_acct_comb);
Exception
When Others then
apps.fnd_file.put_line(apps.fnd_file.LOG,'Account Code Does Not Exist in Master -'||lc_acct_comb);
retcode:=2;
Return;
End;
--Cross validation failure check
If ln_ccid = 0 Then
apps.fnd_file.put_line(apps.fnd_file.LOG,'Account Code Cannot be created please check cross validation rules -'||lc_acct_comb);
retcode:=2;
Return;
End If;
-- End of Vlidations for sales Account creation in Inventory Item Master
IF ln_item_id > 0 Then
fnd_file.put_line(fnd_file.log,'In If Before Insert of interface -'||lc_item_no||' and warehouse - '||lc_whse_code);
INSERT INTO mtl_system_items_interface( inventory_item_id
, organization_id
, last_update_date
, last_updated_by
, creation_date
, created_by
, last_update_login
, segment1
, process_flag
, item_number
, transaction_type
, sales_account
, set_process_id
)
VALUES ( ln_item_id
, ln_whse_id
, SYSDATE
, ln_user_id
, SYSDATE
, ln_user_id
, ln_login_id
, lc_item_no
, 1
, lc_item_no
, 'UPDATE'
, ln_ccid
, ln_process_id
);
END IF;
EXCEPTION
WHEN OTHERS THEN
fnd_file.put_line(fnd_file.log,'Data Not inserted into tables -'||ln_item_id);
RAISE_APPLICATION_ERROR(-20057,'Error While Insert into Stg Table '||SQLERRM);
END;
END IF;
-----------------------------------------------------------------
-- END IF;
END LOOP;
COMMIT;
Fnd_File.put_line(apps.fnd_file.LOG,'File Uploaded into interface table succussfully');
Fnd_File.put_line(apps.fnd_file.LOG,'Process id for this upload is - '||ln_process_id);
UTL_FILE.FCLOSE_ALL;
EXCEPTION
WHEN UTL_FILE.INVALID_OPERATION THEN
Fnd_File.put_line(Fnd_File.output,'Invalid Operation: '||' File Not Exit in OTM budget Directory ');
UTL_FILE.FCLOSE_ALL;
RAISE_APPLICATION_ERROR(-20051,'File Not Exit in MTL Inv Directory');
WHEN UTL_FILE.INVALID_PATH THEN
Fnd_File.put_line(Fnd_File.output,'Invalid Path');
UTL_FILE.FCLOSE_ALL;
RAISE_APPLICATION_ERROR(-20052,'Invalid Path');
WHEN UTL_FILE.INVALID_MODE THEN
Fnd_File.put_line(Fnd_File.output,'Invalid Mode');
UTL_FILE.FCLOSE_ALL;
RAISE_APPLICATION_ERROR(-20053,'Invalid Mode');
WHEN UTL_FILE.INVALID_FILEHANDLE THEN
Fnd_File.put_line(Fnd_File.output,'Invalid File Handle ');
UTL_FILE.FCLOSE_ALL;
RAISE_APPLICATION_ERROR(-20054,'Invalid File Handle');
WHEN UTL_FILE.WRITE_ERROR THEN
Fnd_File.put_line(Fnd_File.output,'Invalid Write Error ');
UTL_FILE.FCLOSE_ALL;
RAISE_APPLICATION_ERROR(-20055,'Invalid Write Error');
WHEN UTL_FILE.READ_ERROR THEN
Fnd_File.put_line(Fnd_File.output,'Read Error ');
UTL_FILE.FCLOSE_ALL;
RAISE_APPLICATION_ERROR(-20056,'Read Error');
WHEN OTHERS THEN
RAISE_APPLICATION_ERROR(-20057,'Check the File Data Format '||SQLERRM);
UTL_FILE.FCLOSE_ALL;
END mtl_inv_acct_upload;
Get Field
-----
FUNCTION get_field(p_line IN VARCHAR2, p_delimiter IN VARCHAR2, p_field_no IN NUMBER) RETURN VARCHAR2 IS
l_start NUMBER :=0;
l_end NUMBER :=0;
lc_delimiter VARCHAR2(10);
BEGIN
lc_delimiter := p_delimiter;
/*****-- Determine start position--*****/
IF p_field_no = 1 THEN
l_start :=0;
ELSE
l_start :=INSTR(p_line,p_delimiter,1,(p_field_no - 1));
IF l_start = 0 THEN
RETURN NULL;
END IF;
END IF;
/*****-- Determine end position--*****/
l_end :=INSTR(p_line,p_delimiter,1,p_field_no);
IF l_end = 0 THEN
l_end := LENGTH(p_line) + 1;
END IF;
/*****-- Extract the field data --*****/
IF (l_end - l_start) = 1 THEN
RETURN NULL;
ELSE
RETURN SUBSTR(p_line,(l_start + 1),((l_end - l_start) - 1));
END IF;
EXCEPTION
WHEN OTHERS THEN
RETURN NULL;
END get_field;
--------------------------------------------------------------------------------------------------------
PROCEDURE mtl_inv_acct_upload(Errbuf OUT VARCHAR2,Retcode OUT NUMBER,p_file_name IN VARCHAR2,P_LINE_NUM IN NUMBER)
IS
reqfiletype UTL_FILE.FILE_TYPE;
req_line VARCHAR2(3000);
req_delim VARCHAR2(1):=',';
rec_count NUMBER;
v_File_Name VARCHAR2(200):=P_File_Name;
v_date_processed DATE; -- (Q)
ln_user_id NUMBER:=FND_GLOBAL.USER_ID;
ln_resp_id NUMBER:=FND_GLOBAL.RESP_ID;
ln_resp_app_id NUMBER:=FND_GLOBAL.RESP_APPL_ID;
v_request_id NUMBER:=0;
ln_batch_id NUMBER;
Lc_Transaction_type VARCHAR2(500);
ln_line_no NUMBER;
ln_record_id NUMBER;
ln_last_updated_by NUMBER;
ln_conc_req_id NUMBER;
ln_login_id NUMBER;
lc_error_flag VARCHAR2(10);
lc_errors_all VARCHAR2(500);
ln_cnt NUMBER;
lc_error_message VARCHAR2(500);
ln_operating_org_id NUMBER;
lc_status VARCHAR2(50);
ln_org_id NUMBER;
lc_error_msg VARCHAR2(500);
ln_request_id NUMBER;
lc_item_no VARCHAR2(50);
ln_item_id NUMBER;
lc_item_desc VARCHAR2(500);
lc_whse_code VARCHAR2(10);
ln_whse_id NUMBER;
lc_acct_comb VARCHAR2(500);
lc_loc VARCHAR2(10);
ln_rule_id NUMBER;
ln_process_id NUMBER;
ln_ccid NUMBER;
lc_rev_concat_segments VARCHAR2(500);
ln_chart_of_account_id NUMBER;
lc_record_status VARCHAR2(20);
lc_item_sts_code VARCHAR2(20);
lc_item_type VARCHAR2(20);
lc_product_code VARCHAR2(20);
lc_lob VARCHAR2(20);
BEGIN
v_file_name:=p_file_name;
reqfiletype := UTL_FILE.FOPEN('MTL_INV_ACCT_UPD', v_File_Name, 'r');
ln_line_no :=0;
---Process id for import items interface
Begin
Select to_char(sysdate,'DDMMYYYYHH24MISS') into ln_process_id from dual;
End;
LOOP
ln_line_no := ln_line_no + 1;
BEGIN
UTL_FILE.GET_LINE(reqfiletype , req_line);
EXCEPTION
WHEN NO_DATA_FOUND THEN
EXIT;
END;
IF ln_line_no > p_line_num THEN
---------------------------------------------------------------------------------------------
--- Mapping with csv fields
-----------------------------------------------------------------------------------------------
--Item Code
BEGIN
lc_item_no := TRIM(get_field(req_line,req_delim,1)); -- (A)
EXCEPTION
WHEN OTHERs THEN
RAISE_APPLICATION_ERROR(-20057,'Item Number '||SQLERRM);
END;
-----------------------------------------------------------------------------------------------
--Item Description
BEGIN
lc_item_desc := TRIM(get_field(req_line,req_delim,2)); -- (B)
EXCEPTION
WHEN OTHERs THEN
RAISE_APPLICATION_ERROR(-20057,'Item Description '||SQLERRM);
END;
-----------------------------------------------------------------
-- Whse Code
BEGIN
lc_whse_code := TRIM(get_field(req_line,req_delim,3)); -- (C)
EXCEPTION
WHEN OTHERs THEN
RAISE_APPLICATION_ERROR(-20057,'Whse Code '||SQLERRM);
END;
-----------------------------------------------------------------
-- Account
BEGIN
lc_acct_comb := TRIM(get_field(req_line,req_delim,4)); -- (D)
EXCEPTION
WHEN OTHERs THEN
RAISE_APPLICATION_ERROR(-20057,'Acct Comb '||SQLERRM);
END;
-----------------------------------------------------------------
---- Record Status
BEGIN
lc_record_status := TRIM(get_field(req_line,req_delim,5)); -- (E)
EXCEPTION
WHEN OTHERs THEN
RAISE_APPLICATION_ERROR(-20057,'Start Period '||SQLERRM);
END;
----------------------------------------------------------------
BEGIN
ln_user_id := apps.fnd_profile.value('USER_ID');
IF ln_user_id IS NULL THEN
ln_user_id := -1;
END IF;
ln_last_updated_by := APPS.FND_GLOBAL.USER_ID;
-- To get the Conc Request ID
ln_conc_req_id := APPS.FND_GLOBAL.CONC_REQUEST_ID;
IF ln_conc_req_id IS NULL THEN
ln_conc_req_id := -1;
END IF;
-- To get the Login ID
ln_login_id := APPS.FND_GLOBAL.LOGIN_ID;
IF ln_login_id IS NULL THEN
ln_login_id := -1;
END IF;
-- To get the Set of Book ID
--ln_set_of_book_id := fnd_profile.VALUE ('GL_SET_OF_BKS_ID');
-- To get the Operating Uni ID
ln_operating_org_id := fnd_profile.VALUE ('ORG_ID');
-- Start to Process the Cursor Records
---Validations for Account update in MTL Item Master
/******************************************************/
-- Validation for organization code
/******************************************************/
Begin
SELECT organization_id into ln_whse_id
FROM mtl_parameters
WHERE organization_code = lc_whse_code;
Exception When Others Then
apps.fnd_file.put_line(apps.fnd_file.LOG,'Whse Code Either Does Not Exist or is inactive in Inventory Organization Master -'||lc_whse_code );
Retcode := 2;
Return;
End;
--
/******************************************************/
-- Validation for item code
/******************************************************/
Begin
Select inventory_item_id into ln_item_id
From mtl_system_items_b
Where organization_id = ln_whse_id
And segment1 = lc_item_no;
Exception
When Others then
apps.fnd_file.put_line(apps.fnd_file.LOG,'Item Code - '||ln_item_id||' either does not exist or is inactive in Inventory Item master for warehouse - '||lc_whse_code);
retcode:=2;
Return;
End;
/******************************************************/
-- Product code validation
/******************************************************/
Begin
Select flex_value into lc_product_code
From fnd_flex_values_vl
Where flex_value_set_id = 1005239
And Trim(substr(Description,instr(Description,'-',-1)+1)) = Trim(lc_item_no);
Exception
When Others then
apps.fnd_file.put_line(apps.fnd_file.LOG,'Item Product Code does not exist in key flexfield setup -'||lc_item_no);
retcode:=2;
Return;
End;
/******************************************************/
--Cross Validation checkinf for LOB and PRODUCT
/******************************************************/
Begin
Select rule_line_id into ln_rule_id
From fnd_flex_validation_rule_lines
Where flex_validation_rule_name like 'LOB & Product'
And substr(concatenated_segments_high,3,3) = Substr(lc_acct_comb,6,3)
And substr(concatenated_segments_high,instr(concatenated_segments_high,'.',-2)+1,5) = Substr(lc_acct_comb,25,5);
Exception
When Others then
apps.fnd_file.put_line(apps.fnd_file.LOG,'Cross validation rule does not exist for lob - '||Substr(lc_acct_comb,6,3)||' and product code - '||Substr(lc_acct_comb,25,5));
retcode:=2;
Return;
End;
/******************************************************/
-- Location Validation
/******************************************************/
Begin
Select substr(acctg_unit_no,6) into lc_loc
From gl_accu_mst gat,
gl_accu_map gap
Where gap.acctg_unit_id = gat.acctg_unit_id
And gap.whse_code = lc_whse_code;
Exception
When Others then
apps.fnd_file.put_line(apps.fnd_file.LOG,'Please check LOC in MAC account combination - '||lc_whse_code);
retcode:=2;
Return;
End;
--
If lc_loc <> Substr(lc_acct_comb,10,3) Then
apps.fnd_file.put_line(apps.fnd_file.LOG,'Please check LOC in account combination,Either it does not exist in MAC or wrong in sheet - '||lc_whse_code);
retcode:=2;
Return;
End If;
/******************************************************/
-- LOB validation
/******************************************************/
Begin
Select segment5 into lc_lob
From MTL_ITEM_CATEGORIES_V
Where CATEGORY_SET_ID = 1100000021
And inventory_item_id = ln_item_id
And organization_id = 23;
Exception
When Others then
apps.fnd_file.put_line(apps.fnd_file.LOG,'LOB does not exist in Item Category -'||ln_item_id);
retcode:=2;
Return;
End;
--
IF lc_lob <> Substr(lc_acct_comb,6,3) THEN
apps.fnd_file.put_line(apps.fnd_file.LOG,'LOB Mismatch with LOB in Item Category of Inventory Item Master -'||lc_lob||' for item id -'||ln_item_id);
retcode:=2;
Return;
End If;
/******************************************************/
--Account combination creation using API
/******************************************************/
Begin
ln_ccid := fnd_flex_ext.get_ccid
(application_short_name => 'SQLGL',
key_flex_code => 'GL#',
structure_number => 50186,
validation_date => TO_CHAR(SYSDATE,'DD-MON-YYYY'),
concatenated_segments => lc_acct_comb);
Exception
When Others then
apps.fnd_file.put_line(apps.fnd_file.LOG,'Account Code Does Not Exist in Master -'||lc_acct_comb);
retcode:=2;
Return;
End;
--Cross validation failure check
If ln_ccid = 0 Then
apps.fnd_file.put_line(apps.fnd_file.LOG,'Account Code Cannot be created please check cross validation rules -'||lc_acct_comb);
retcode:=2;
Return;
End If;
-- End of Vlidations for sales Account creation in Inventory Item Master
IF ln_item_id > 0 Then
fnd_file.put_line(fnd_file.log,'In If Before Insert of interface -'||lc_item_no||' and warehouse - '||lc_whse_code);
INSERT INTO mtl_system_items_interface( inventory_item_id
, organization_id
, last_update_date
, last_updated_by
, creation_date
, created_by
, last_update_login
, segment1
, process_flag
, item_number
, transaction_type
, sales_account
, set_process_id
)
VALUES ( ln_item_id
, ln_whse_id
, SYSDATE
, ln_user_id
, SYSDATE
, ln_user_id
, ln_login_id
, lc_item_no
, 1
, lc_item_no
, 'UPDATE'
, ln_ccid
, ln_process_id
);
END IF;
EXCEPTION
WHEN OTHERS THEN
fnd_file.put_line(fnd_file.log,'Data Not inserted into tables -'||ln_item_id);
RAISE_APPLICATION_ERROR(-20057,'Error While Insert into Stg Table '||SQLERRM);
END;
END IF;
-----------------------------------------------------------------
-- END IF;
END LOOP;
COMMIT;
Fnd_File.put_line(apps.fnd_file.LOG,'File Uploaded into interface table succussfully');
Fnd_File.put_line(apps.fnd_file.LOG,'Process id for this upload is - '||ln_process_id);
UTL_FILE.FCLOSE_ALL;
EXCEPTION
WHEN UTL_FILE.INVALID_OPERATION THEN
Fnd_File.put_line(Fnd_File.output,'Invalid Operation: '||' File Not Exit in OTM budget Directory ');
UTL_FILE.FCLOSE_ALL;
RAISE_APPLICATION_ERROR(-20051,'File Not Exit in MTL Inv Directory');
WHEN UTL_FILE.INVALID_PATH THEN
Fnd_File.put_line(Fnd_File.output,'Invalid Path');
UTL_FILE.FCLOSE_ALL;
RAISE_APPLICATION_ERROR(-20052,'Invalid Path');
WHEN UTL_FILE.INVALID_MODE THEN
Fnd_File.put_line(Fnd_File.output,'Invalid Mode');
UTL_FILE.FCLOSE_ALL;
RAISE_APPLICATION_ERROR(-20053,'Invalid Mode');
WHEN UTL_FILE.INVALID_FILEHANDLE THEN
Fnd_File.put_line(Fnd_File.output,'Invalid File Handle ');
UTL_FILE.FCLOSE_ALL;
RAISE_APPLICATION_ERROR(-20054,'Invalid File Handle');
WHEN UTL_FILE.WRITE_ERROR THEN
Fnd_File.put_line(Fnd_File.output,'Invalid Write Error ');
UTL_FILE.FCLOSE_ALL;
RAISE_APPLICATION_ERROR(-20055,'Invalid Write Error');
WHEN UTL_FILE.READ_ERROR THEN
Fnd_File.put_line(Fnd_File.output,'Read Error ');
UTL_FILE.FCLOSE_ALL;
RAISE_APPLICATION_ERROR(-20056,'Read Error');
WHEN OTHERS THEN
RAISE_APPLICATION_ERROR(-20057,'Check the File Data Format '||SQLERRM);
UTL_FILE.FCLOSE_ALL;
END mtl_inv_acct_upload;
No comments:
Post a Comment