/*Typically, wrappers goes through following steps for each item:
1. Load the data from the flat file into PL/SQL table or record.
2. Call the Item Cost API for each item.
3. Check the return status.
4. Retrieve the messages from the message stack and write them into a log file.
Wrapper logic
1. Lines 66-69, declares PL/SQL variables to hold the data coming from flat file.
These variables gets passed to the public API, so that data type should
correspond with the data types specified in the API specification.
2. Lines 125-132, get the first line from the flat file and extract the first value. In
our case it is a type of the record. Following are the possible values:
10 : Item Cost Header record
20 : This Level record for the Item
30 : Lower Level record for the Item.
3. Lines 143-261, populate the item details into PL/SQL tables based on the type
of record.
4. Lines 263-281, error handling. If error is raised while populating header record
then skip all the detail records. If in case of detail record then skip only that
detail record and continue populating other detail records.
5. Lines 283-335, get the next line fromthe file and check type of record. Following
scenarios may occur :
n If the record type is of detail, then continue populating the record.
n If the record type is header record then call the public API for the previous
Item Cost Detail already read.
n If the fetch fails (i.e., end of file is reached - No_Data_Found error), then call
the public API for the previous Item Cost Detail already read.
6. Lines 343-373, read all the messages from the message stack and write it into a
log file.
7. Lines 376-383, check the out parameter l_costcmpnt_ids PL/SQL table, if any
records are inserted then get the information for what detail records have been
inserted.
8. Lines 385-389, check whether to continue the loop or not. If yes, then start over
again from step 2.*/
--+==========================================================================+
/* FUNCTION NAME
|
3 --| Create_Item_Cost
|
4 --|
|
5 --| TYPE
|
6 --| Public
|
7 --|
|
8 --| USAGE
|
9 --| Create item Cost
|
10 --|
|
11 --| DESCRIPTION
|
12 --| This is a PL/SQL wrapper function to call the
|
13 --| Create_Item_Cost API.
|
14 --| It reads item data from a flat file and outputs any error
|
15 --| messages to a second flat file. It also generates a log file
|
16 --| called gmf_api_cric_wrapper<session_id>.log in the p_dir directory.
|
17 --|
|
18 --| PARAMETERS
|
19 --| p_dir IN VARCHAR2 - Working directory for input
|
20 --| and output files.
|
21 --| p_input_file IN VARCHAR2 - Name of input file
|
22 --| p_output_file IN VARCHAR2 - Name of output file
|
23 --| p_delimiter IN VARCHAR2 - Delimiter character
|
24 --|
|
25 --| RETURNS
|
26 --| VARCHAR2 - 'S' All records processed successfully
|
27 --| 'E' 1 or more records errored
|
28 --| 'U' 1 or more record unexpected error
|
29 --|
|
30 --| HISTORY
|
31 --|
|
32*/
--Api end of comments
FUNCTION Create_Item_Cost
( p_dir IN VARCHAR2
, p_input_file IN VARCHAR2
, p_output_file IN VARCHAR2
, p_delimiter IN VARCHAR240)
RETURN VARCHAR2
IS
/* Local variables */
l_status VARCHAR2(11);
l_return_status VARCHAR2(11) :=FND_API.G_RET_STS_SUCCESS;
l_count NUMBER(10) ;
l_record_count NUMBER(10) :=0;
l_loop_cnt NUMBER(10) :=0;
l_dummy_cnt NUMBER(10) :=0;
l_data VARCHAR2(1000);
l_p_dir VARCHAR2(150);
l_output_file VARCHAR2(120);
l_outfile_handle UTL_FILE.FILE_TYPE;
l_input_file VARCHAR2(120);
l_infile_handle UTL_FILE.FILE_TYPE;
l_line VARCHAR2(1000);
l_delimiter VARCHAR(11);
l_log_dir VARCHAR2(150);
l_log_name VARCHAR2(120) :='gmf_api_cric_wrapper';
l_log_handle UTL_FILE.FILE_TYPE;
l_global_file VARCHAR2(120);
l_header_rec GMF_ItemCost_PUB.Header_Rec_Type;
l_this_lvl_tbl GMF_ItemCost_PUB.This_Level_Dtl_Tbl_Type;
l_lower_lvl_tbl GMF_ItemCost_PUB.Lower_Level_Dtl_Tbl_Type;
l_costcmpnt_ids GMF_ItemCost_PUB.costcmpnt_ids_tbl_type;
l_idx NUMBER(10);
l_idx1 NUMBER(10);
l_type NUMBER(10);
l_continue VARCHAR2(1) := 'Y' ;
l_skip_details VARCHAR2(1) := 'N' ;
l_session_id VARCHAR2(110);
BEGIN
/* Enable The Buffer */
DBMS_OUTPUT.ENABLE(1000000);
l_p_dir :=p_dir;
l_input_file :=p_input_file;
l_output_file :=p_output_file;
l_delimiter :=p_delimiter;
l_global_file :=l_input_file;
/* Obtain The SessionId To Append To wrapper File Name. */
l_session_id := USERENV('sessionid');
l_log_name := CONCAT(l_log_name,l_session_id);
l_log_name := CONCAT(l_log_name,'.log');
/* Directory is now the same same as for the out file */
l_log_dir := p_dir;
/* Open The Wrapper File For Output And The Input File for Input. */
l_log_handle :=UTL_FILE.FOPEN(l_log_dir, l_log_name, 'w');
l_infile_handle :=UTL_FILE.FOPEN(l_p_dir, l_input_file, 'r');
/* Loop thru flat file and call Item Cost API */
DBMS_OUTPUT.PUT_LINE('Process Started at ' || to_char(SYSDATE,'DD-MON-YY HH24:MI:SS'));
DBMS_OUTPUT.PUT_LINE('Input Directory ' || l_p_dir );
DBMS_OUTPUT.PUT_LINE('Input File ' || l_input_file );
DBMS_OUTPUT.PUT_LINE('Delimiter ' || l_delimiter );
DBMS_OUTPUT.PUT_LINE('Output File ' || l_output_file );
DBMS_OUTPUT.PUT_LINE('Start Processing');
UTL_FILE.PUT_LINE(l_log_handle, 'Process Started at ' || to_char(SYSDATE,'DD-MON-YY HH24:MI:SS'));
UTL_FILE.NEW_LINE(l_log_handle);
UTL_FILE.PUT_LINE(l_log_handle, 'Input Directory ' || l_p_dir );
UTL_FILE.PUT_LINE(l_log_handle, 'Input File ' || l_input_file );
UTL_FILE.PUT_LINE(l_log_handle, 'Record Type ' || l_delimiter );
UTL_FILE.PUT_LINE(l_log_handle, 'Output File ' || l_output_file );
l_outfile_handle :=UTL_FILE.FOPEN(l_p_dir, l_output_file, 'w');
/* Get the first record from the file */
BEGIN
UTL_FILE.GET_LINE(l_infile_handle, l_line);
l_record_count :=l_record_count+1;
l_type := Get_Field(l_line,l_delimiter,1) ; -- 10 : header rec, 20 :this level, 30 : lower level
EXCEPTION
WHEN NO_DATA_FOUND THEN
raise;
END;
/* Populate appropriate pl/sql record or table based on the type of record */
LOOP
BEGIN
UTL_FILE.PUT_LINE(l_log_handle, 'Reading Record...' || l_record_count);
UTL_FILE.PUT_LINE(l_log_handle, 'Type = ' || l_type) ;
/* Header record */
IF l_type = 10 THEN
-- empty the pl/sql tables to remove details of the previous item
-- and initialize the index
l_this_lvl_tbl.delete ;
l_lower_lvl_tbl.delete ;
l_costcmpnt_ids.delete;
l_skip_details := 'N' ;
l_idx := 0 ;
l_idx1 := 0 ;
l_header_rec.calendar_code := Get_Field(l_line,l_delimiter,2) ;
l_header_rec.period_code := Get_Field(l_line,l_delimiter,3) ;
l_header_rec.cost_mthd_code := Get_Field(l_line,l_delimiter,4) ;
l_header_rec.whse_code := Get_Field(l_line,l_delimiter,5) ;
l_header_rec.item_id := Get_Field(l_line,l_delimiter,6) ;
l_header_rec.item_no := Get_Field(l_line,l_delimiter,7) ;
l_header_rec.user_name := Get_Field(l_line,l_delimiter,8) ;
UTL_FILE.PUT_LINE(l_log_handle, 'Type = ' || l_type) ;
UTL_FILE.PUT_LINE(l_log_handle, 'calendar_code = ' || l_header_rec.calendar_code) ;
UTL_FILE.PUT_LINE(l_log_handle, 'period_code = ' || l_header_rec.period_code) ;
UTL_FILE.PUT_LINE(l_log_handle, 'cost_mthd_code = ' || l_header_rec.cost_mthd_code) ;
UTL_FILE.PUT_LINE(l_log_handle, 'whse_code = ' || l_header_rec.whse_code) ;
UTL_FILE.PUT_LINE(l_log_handle, 'item_id = ' || l_header_rec.item_id) ;
UTL_FILE.PUT_LINE(l_log_handle, 'item_no = ' || l_header_rec.item_no) ;
UTL_FILE.PUT_LINE(l_log_handle, 'user_name = ' || l_header_rec.user_name) ;
/* This Level Detail record. Skip details in case errors in loading header record */
ELSIF l_type = 20 AND l_skip_details = 'Y' THEN
UTL_FILE.PUT_LINE(l_log_handle, 'Error : Skipping this record...');
ELSIF l_type = 20 AND l_skip_details = 'N' THEN
l_idx := l_idx + 1 ;
l_this_lvl_tbl(l_idx).cmpntcost_id := Get_Field(l_line,l_delimiter,2) ;
l_this_lvl_tbl(l_idx).cost_cmpntcls_id := Get_Field(l_line,l_delimiter,3) ;
l_this_lvl_tbl(l_idx).cost_cmpntcls_code := Get_Field(l_line,l_delimiter,4) ;
l_this_lvl_tbl(l_idx).cost_analysis_code := Get_Field(l_line,l_delimiter,5) ;
l_this_lvl_tbl(l_idx).cmpnt_cost := Get_Field(l_line,l_delimiter,6) ;
l_this_lvl_tbl(l_idx).burden_ind := Get_Field(l_line,l_delimiter,7) ;
l_this_lvl_tbl(l_idx).total_qty := Get_Field(l_line,l_delimiter,8) ;
l_this_lvl_tbl(l_idx).costcalc_orig := Get_Field(l_line,l_delimiter,9) ;
l_this_lvl_tbl(l_idx).rmcalc_type := Get_Field(l_line,l_delimiter,10) ;
l_this_lvl_tbl(l_idx).delete_mark := Get_Field(l_line,l_delimiter,11) ;
l_this_lvl_tbl(l_idx).attribute1 := Get_Field(l_line,l_delimiter,12) ;
l_this_lvl_tbl(l_idx).attribute2 := Get_Field(l_line,l_delimiter,13) ;
l_this_lvl_tbl(l_idx).attribute3 := Get_Field(l_line,l_delimiter,14) ;
l_this_lvl_tbl(l_idx).attribute4 := Get_Field(l_line,l_delimiter,15) ;
l_this_lvl_tbl(l_idx).attribute5 := Get_Field(l_line,l_delimiter,16) ;
l_this_lvl_tbl(l_idx).attribute6 := Get_Field(l_line,l_delimiter,17) ;
l_this_lvl_tbl(l_idx).attribute7 := Get_Field(l_line,l_delimiter,18) ;
l_this_lvl_tbl(l_idx).attribute8 := Get_Field(l_line,l_delimiter,19) ;
l_this_lvl_tbl(l_idx).attribute9 := Get_Field(l_line,l_delimiter,20) ;
l_this_lvl_tbl(l_idx).attribute10 := Get_Field(l_line,l_delimiter,21) ;
l_this_lvl_tbl(l_idx).attribute11 := Get_Field(l_line,l_delimiter,22) ;
l_this_lvl_tbl(l_idx).attribute12 := Get_Field(l_line,l_delimiter,23) ;
l_this_lvl_tbl(l_idx).attribute13 := Get_Field(l_line,l_delimiter,24) ;
l_this_lvl_tbl(l_idx).attribute14 := Get_Field(l_line,l_delimiter,25) ;
l_this_lvl_tbl(l_idx).attribute15 := Get_Field(l_line,l_delimiter,26) ;
l_this_lvl_tbl(l_idx).attribute16 := Get_Field(l_line,l_delimiter,27) ;
l_this_lvl_tbl(l_idx).attribute17 := Get_Field(l_line,l_delimiter,28) ;
l_this_lvl_tbl(l_idx).attribute18 := Get_Field(l_line,l_delimiter,29) ;
l_this_lvl_tbl(l_idx).attribute19 := Get_Field(l_line,l_delimiter,30) ;
l_this_lvl_tbl(l_idx).attribute20 := Get_Field(l_line,l_delimiter,31) ;
l_this_lvl_tbl(l_idx).attribute21 := Get_Field(l_line,l_delimiter,32) ;
l_this_lvl_tbl(l_idx).attribute22 := Get_Field(l_line,l_delimiter,33) ;
l_this_lvl_tbl(l_idx).attribute23 := Get_Field(l_line,l_delimiter,34) ;
l_this_lvl_tbl(l_idx).attribute24 := Get_Field(l_line,l_delimiter,35) ;
l_this_lvl_tbl(l_idx).attribute25 := Get_Field(l_line,l_delimiter,36) ;
l_this_lvl_tbl(l_idx).attribute26 := Get_Field(l_line,l_delimiter,37) ;
l_this_lvl_tbl(l_idx).attribute27 := Get_Field(l_line,l_delimiter,38) ;
l_this_lvl_tbl(l_idx).attribute28 := Get_Field(l_line,l_delimiter,39) ;
l_this_lvl_tbl(l_idx).attribute29 := Get_Field(l_line,l_delimiter,40) ;
l_this_lvl_tbl(l_idx).attribute30 := Get_Field(l_line,l_delimiter,41) ;
l_this_lvl_tbl(l_idx).attribute_category := Get_Field(l_line,l_delimiter,42) ;
UTL_FILE.PUT_LINE(l_log_handle,'cmpntcost_id('||l_idx||') = '
||
l_this_lvl_tbl(l_idx).cmpntcost_id) ;
UTL_FILE.PUT_LINE(l_log_handle,'cost_cmpntcls_id('||l_idx||') = '
||
l_this_lvl_tbl(l_idx).cost_cmpntcls_id) ;
UTL_FILE.PUT_LINE(l_log_handle,'cost_cmpntcls_code('||l_idx||') = '
||
l_this_lvl_tbl(l_idx).cost_cmpntcls_code) ;
UTL_FILE.PUT_LINE(l_log_handle,'cost_analysis_code('||l_idx||') = '
||
l_this_lvl_tbl(l_idx).cost_analysis_code) ;
UTL_FILE.PUT_LINE(l_log_handle,'cmpnt_cost('||l_idx||') = '
||
l_this_lvl_tbl(l_idx).cmpnt_cost) ;
UTL_FILE.PUT_LINE(l_log_handle,'burden_ind('||l_idx||') = '
||
l_this_lvl_tbl(l_idx).burden_ind) ;
UTL_FILE.PUT_LINE(l_log_handle,'total_qty('||l_idx||') = '
||
l_this_lvl_tbl(l_idx).total_qty) ;
UTL_FILE.PUT_LINE(l_log_handle,'costcalc_orig('||l_idx||') = '
||
l_this_lvl_tbl(l_idx).costcalc_orig) ;
UTL_FILE.PUT_LINE(l_log_handle,'rmcalc_type('||l_idx||') = '
||
l_this_lvl_tbl(l_idx).rmcalc_type) ;
UTL_FILE.PUT_LINE(l_log_handle,'delete_mark('||l_idx||') = '
||
l_this_lvl_tbl(l_idx).delete_mark) ;
/* Lower Level Detail record. Skip details in case errors in loading header record */
ELSIF l_type = 30 AND l_skip_details = 'Y' THEN
UTL_FILE.PUT_LINE(l_log_handle, 'Error : Skipping this record...');
ELSIF l_type = 30 AND l_skip_details = 'N' THEN
l_idx1 := l_idx1 + 1 ;
l_type := Get_Field(l_line,l_delimiter,1) ;
l_lower_lvl_tbl(l_idx1).cmpntcost_id := Get_Field(l_line,l_delimiter,2) ;
l_lower_lvl_tbl(l_idx1).cost_cmpntcls_id := Get_Field(l_line,l_delimiter,3) ;
l_lower_lvl_tbl(l_idx1).cost_cmpntcls_code := Get_Field(l_line,l_delimiter,4) ;
l_lower_lvl_tbl(l_idx1).cost_analysis_code := Get_Field(l_line,l_delimiter,5) ;
l_lower_lvl_tbl(l_idx1).cmpnt_cost := Get_Field(l_line,l_delimiter,6) ;
UTL_FILE.PUT_LINE(l_log_handle,'ll cmpntcost_id('||l_idx1||') ='
|| l_lower_lvl_tbl(l_idx1).cmpntcost_id) ;
UTL_FILE.PUT_LINE(l_log_handle,'ll cost_cmpntcls_id('||l_idx1||') ='
|| l_lower_lvl_tbl(l_idx1).cost_cmpntcls_id) ;
UTL_FILE.PUT_LINE(l_log_handle,'ll cost_cmpntcls_code('||l_idx1||') ='
|| l_lower_lvl_tbl(l_idx1).cost_cmpntcls_code) ;
UTL_FILE.PUT_LINE(l_log_handle,'ll cost_analysis_code('||l_idx1||') ='
|| l_lower_lvl_tbl(l_idx1).cost_analysis_code) ;
UTL_FILE.PUT_LINE(l_log_handle,'ll cmpnt_cost('||l_idx1||') =' || l_lower_lvl_tbl(l_idx1).cmpnt_cost) ;
END IF ;
EXCEPTION
WHEN OTHERS THEN
/* in case of any errors log the error and continue with the next record
* in case of header, skip all the detail record
* in case details, skip that particular record */
UTL_FILE.PUT_LINE(l_outfile_handle, 'Error : ' || to_char(SQLCODE) || '' || SQLERRM);
UTL_FILE.PUT_LINE(l_log_handle, 'Error : ' || to_char(SQLCODE) || '' || SQLERRM);
IF l_type = 10 THEN
l_skip_details := 'Y' ;
UTL_FILE.PUT_LINE(l_log_handle, 'Error : Skip detail records.');
ELSIF l_type = 20 THEN
l_this_lvl_tbl.delete(l_idx);
ELSIF l_type = 30 THEN
l_lower_lvl_tbl.delete(l_idx1);
END IF ;
END ;
BEGIN
UTL_FILE.GET_LINE(l_infile_handle, l_line);
l_record_count :=l_record_count+1;
UTL_FILE.NEW_LINE(l_log_handle);
l_type := Get_Field(l_line,l_delimiter,1) ; -- 10 : header rec, 20: this level, 30 : lower level
EXCEPTION
/* End of file */
WHEN NO_DATA_FOUND THEN
IF l_skip_details = 'N' THEN
GMF_ItemCost_PUB.Create_Item_Cost
( p_api_version => 1.0
, p_init_msg_list => FND_API.G_TRUE
, p_commit => FND_API.G_TRUE
, x_return_status => l_status
, x_msg_count => l_count
, x_msg_data => l_data
, p_header_rec => l_header_rec
, p_this_level_dtl_tbl => l_this_lvl_tbl
, p_lower_level_dtl_Tbl => l_lower_lvl_tbl
, x_costcmpnt_ids => l_costcmpnt_ids );
l_continue := 'N' ;
goto GET_MSG_STACK ;
END IF ;
END;
IF (l_type = 10 AND l_record_count != 1 AND l_skip_details = 'N') THEN
DBMS_OUTPUT.PUT_LINE('Calling Create_Item_Cost API...');
GMF_ItemCost_PUB.Create_Item_Cost
( p_api_version => 1.0
, p_init_msg_list => FND_API.G_TRUE
, p_commit => FND_API.G_TRUE
, x_return_status => l_status
, x_msg_count => l_count
, x_msg_data => l_data
, p_header_rec => l_header_rec
, p_this_level_dtl_tbl => l_this_lvl_tbl
, p_lower_level_dtl_Tbl => l_lower_lvl_tbl
, x_costcmpnt_ids => l_costcmpnt_ids
);
DBMS_OUTPUT.PUT_LINE('after API call. status := ' || l_status ||' cnt
:= ' || l_count );
END IF;
<<GET_MSG_STACK>>
NULL;
/* Check if any messages generated. If so then decode and */
/* output to error message flat file */
IF l_count > 0 THEN
l_loop_cnt :=1;
LOOP
FND_MSG_PUB.Get(
p_msg_index => l_loop_cnt,
p_data => l_data,
p_encoded => FND_API.G_FALSE,
p_msg_index_out => l_dummy_cnt);
--DBMS_OUTPUT.PUT_LINE(l_data );
UTL_FILE.PUT_LINE(l_log_handle, l_data);
/* Update error status */
IF (l_status = 'U') THEN
l_return_status :=l_status;
ELSIF (l_status = 'E' and l_return_status <> 'U') THEN
l_return_status :=l_status;
ELSE
l_return_status :=l_status;
END IF;
l_loop_cnt := l_loop_cnt + 1;
IF l_loop_cnt > l_count THEN
EXIT;
END IF;
END LOOP; -- msg stack loop
l_count := 0 ;
END IF;-- if count of msg stack > 0
DBMS_OUTPUT.PUT_LINE('# of CostIds inserted : ' || l_costcmpnt_ids.count);
FOR i in 1..l_costcmpnt_ids.count
LOOP
UTL_FILE.PUT_LINE(l_log_handle,
'CmpntClsId : ' || l_costcmpnt_ids(i).cost_cmpntcls_id ||
' Analysis Code : ' || l_costcmpnt_ids(i).cost_analysis_code ||
' Cost Level : ' || l_costcmpnt_ids(i).cost_level ||
' CostId : ' || l_costcmpnt_ids(i).cmpntcost_id);
END LOOP ;
IF l_continue = 'N' THEN
EXIT ;
END IF ;
END LOOP;
UTL_FILE.NEW_LINE(l_log_handle);
UTL_FILE.PUT_LINE(l_log_handle, 'Process Completed at ' || to_char(SYSDATE,'DD-MON-YY HH24:MI:SS'));
UTL_FILE.FCLOSE_ALL;
RETURN l_return_status;
EXCEPTION
WHEN UTL_FILE.INVALID_OPERATION THEN
/* DBMS_OUTPUT.PUT_LINE('Invalid Operation For '|| l_global_file); */
UTL_FILE.FCLOSE_ALL;
RETURN l_return_status;
WHEN UTL_FILE.INVALID_PATH THEN
/* DBMS_OUTPUT.PUT_LINE('Invalid Path For '|| l_global_file); */
UTL_FILE.FCLOSE_ALL;
RETURN l_return_status;
WHEN UTL_FILE.INVALID_MODE THEN
/* DBMS_OUTPUT.PUT_LINE('Invalid Mode For '|| l_global_file); */
UTL_FILE.FCLOSE_ALL;
RETURN l_return_status;
WHEN UTL_FILE.INVALID_FILEHANDLE THEN
/* DBMS_OUTPUT.PUT_LINE('Invalid File Handle '|| l_global_file); */
UTL_FILE.FCLOSE_ALL;
RETURN l_return_status;
WHEN UTL_FILE.WRITE_ERROR THEN
/* DBMS_OUTPUT.PUT_LINE('Invalid Write Error '|| l_global_file); */
UTL_FILE.FCLOSE_ALL;
RETURN l_return_status;
WHEN UTL_FILE.READ_ERROR THEN
/* DBMS_OUTPUT.PUT_LINE('Invalid Read Error '|| l_global_file); */
UTL_FILE.FCLOSE_ALL;
RETURN l_return_status;
WHEN UTL_FILE.INTERNAL_ERROR THEN
/* DBMS_OUTPUT.PUT_LINE('Internal Error'); */
UTL_FILE.FCLOSE_ALL;
RETURN l_return_status;
WHEN OTHERS THEN
/* DBMS_OUTPUT.PUT_LINE('Other Error'); */
UTL_FILE.PUT_LINE(l_outfile_handle, 'Error : ' || to_char(SQLCODE) || ' '|| SQLERRM);
UTL_FILE.PUT_LINE(l_log_handle, 'Error : ' || to_char(SQLCODE) || ' ' ||SQLERRM);
UTL_FILE.PUT_LINE(l_log_handle, 'Process Completed at ' || to_char(SYSDATE,'DD-MON-YY HH24:MI:SS'));
UTL_FILE.FCLOSE_ALL;
l_return_status := 'U' ;
RETURN l_return_status;
END Create_Item_Cost;
--+==========================================================================+
--| FUNCTION NAME
--| Get_Field
--| TYPE
--| Public
--|
--| USAGE
--| Get value of field n from a delimited line of ASCII data
--|
--| DESCRIPTION
--| This utility function will return the value of a field from
--| a delimited line of ASCII text
--|
--| PARAMETERS
--| p_line IN VARCHAR2 - line of data
--| p_delimiter IN VARCHAR2 - Delimiter character
--| p_field_no IN NUMBER - Field occurance to be
--| returned
--|
--| RETURNS
--| VARCHAR2 - Value of field
--| HISTORY
--+==========================================================================+
-- Api end of comments
FUNCTION Get_Field
( p_line IN VARCHAR2
, p_delimiter IN VARCHAR2
, p_field_no IN NUMBER
)
RETURN VARCHAR2
IS
/* Local variables */
l_start NUMBER :=0;
l_end NUMBER :=0;
BEGIN
/* 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;
--+==========================================================================+
--| FUNCTION NAME
--| Get_Substring
--| TYPE
--| Public
--| USAGE
--| Get value of Sub-string from formatted ASCII data file record
--|
--| DESCRIPTION
--| This utility function will return the value of a passed sub-string
--| of a formatted ASCII data file record
--|
--| PARAMETERS
--| p_substring IN VARCHAR2 - substring data
--|
--| RETURNS
--| VARCHAR2 - Value of field
--|
--| HISTORY
--|
--+==========================================================================+
-- Api end of comments
FUNCTION Get_Substring
( p_substring IN VARCHAR2
)
RETURN VARCHAR2
IS
/* Local variables */
l_string_value VARCHAR2(200) :=' ';
BEGIN
/* Determine start position */
l_string_value :=NVL(RTRIM(LTRIM(p_substring)),' ');
RETURN l_string_value;
EXCEPTION
WHEN OTHERS
THEN
RETURN ' ';
END Get_Substring;
No comments:
Post a Comment