[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
select distinct cat.xref_category_code
into cCategory_code
from ece_interface_tables t,
ece_interface_columns col,
ece_xref_categories cat
where t.interface_table_id = col.interface_table_id
and cat.xref_category_id = col.xref_category_id
and t.transaction_type = cTransaction_code
and col.base_table_name = cView_name
and col.base_column_name = cView_column;
should so a select from ECE_OUTPUT where run_id = iRun_id
to see the errors.
The calling routine should also clean up the ECE_OUTPUT
table when finished by deleting the records where
run_id = iRun_id.
*********************************************************/
PROCEDURE SEED_DATA_CHECK (
cTransaction_code IN VARCHAR2,
bErrors_found OUT NOCOPY BOOLEAN,
iRun_id OUT NOCOPY NUMBER,
bCheckLength IN BOOLEAN DEFAULT FALSE,
bCheckDatatype IN BOOLEAN DEFAULT FALSE,
bInsertErrors IN BOOLEAN DEFAULT FALSE)
is
cursor c_atc(my_table VARCHAR2, my_column VARCHAR2) is
select data_length,
data_type
from user_tab_columns
where column_name = my_column
and table_name = my_table;
select
eit.interface_table_name,
eit.key_column_name,
eic.interface_column_name,
eic.base_table_name,
eic.base_column_nAme,
eic.data_type,
eic.width
from ece_interface_tables eit,
ece_interface_columns eic
where eit.interface_table_id = eic.interface_table_id
and eit.transaction_type = UPPER(cTransaction_code)
order by eit.interface_table_name;
select
eit.interface_table_name,
eit.start_number,
eit.output_level,
eit.key_column_name
from
ece_interface_tables eit
where
eit.transaction_type = UPPER(cTransaction_code);
select lookup_type
from ece_lookup_values
where lookup_type = 'OUTPUT_LEVEL_'||xTrans
and lookup_code = xLevel;
select ece_output_runs_s.nextval
into iRun_id from dual;
select direction
into xDirection
from ece_interface_tables
where transaction_type = cTransaction_code
and output_level = '1';
select lookup_code
into xTemp
from ece_lookup_values
where lookup_type = 'DOCUMENT'
and lookup_code = cTransaction_code;
if bInsertErrors then
insert into ece_output (run_id,line_id,text)
values (iRun_id,
ece_output_lines_s.nextval,
x_msg);
select lookup_code
into xTemp
from ece_lookup_values
where lookup_type = cTransaction_code||':DOCUMENT_TYPE';
if bInsertErrors then
x_msg := 'SEED: Document types for '||cTransaction_code||' not defined in ece_lookup_values';
insert into ece_output (run_id,line_id,text)
values (iRun_id,
ece_output_lines_s.nextval,
x_msg);
-- This is because the select could return more than one row causing
-- an exception. We want to trap that exception because returning
-- more than one row is a good thing.
null;
if bInsertErrors then
x_msg := 'SEED: Output level not defined in ECE_LOOKUP_VALUES: '||eit_rec.output_level;
insert into ece_output (run_id,line_id,text)
values (iRun_id,
ece_output_lines_s.nextval,
x_msg);
if bInsertErrors then
x_msg := 'SEED: Err in output_level check: '||SQLERRM;
insert into ece_output (run_id,line_id,text)
values (iRun_id,
ece_output_lines_s.nextval,
x_msg);
if bInsertErrors then
x_errtbl := eic_rec.base_table_name;
insert into ece_output (run_id,line_id,text)
values (iRun_id,
ece_output_lines_s.nextval,
x_msg);
if bInsertErrors then
x_errtbl := eic_rec.interface_table_name;
insert into ece_output (run_id,line_id,text)
values (iRun_id,
ece_output_lines_s.nextval,
x_msg);
if bInsertErrors then
x_errtbl := eic_rec.interface_table_name;
insert into ece_output (run_id,line_id,text)
values (iRun_id,
ece_output_lines_s.nextval,
x_msg);
if bInsertErrors then
x_errtbl := eic_rec.interface_table_name;
insert into ece_output (run_id,line_id,text)
values (iRun_id,
ece_output_lines_s.nextval,
x_msg);
if bInsertErrors then
xCurTable := eic_rec.interface_table_name;
insert into ece_output (run_id,line_id,text)
values (iRun_id,
ece_output_lines_s.nextval,
x_msg);
if bInsertErrors then
x_errtbl := eic_rec.base_table_name;
insert into ece_output (run_id,line_id,text)
values (iRun_id,
ece_output_lines_s.nextval,
x_msg);
if bInsertErrors then
x_errtbl := eic_rec.base_table_name;
insert into ece_output (run_id,line_id,text)
values (iRun_id,
ece_output_lines_s.nextval,
x_msg);
if bInsertErrors then
x_errtbl := eic_rec.base_table_name;
insert into ece_output (run_id,line_id,text)
values (iRun_id,
ece_output_lines_s.nextval,
x_msg);
l_insert_stmt VARCHAR2(2000);
SELECT eic.interface_column_name,
eic.base_table_name,
eic.base_column_name,
eic.record_number,
eic.position,
eic.conversion_sequence conversion_seq,
eic.record_layout_code,
eic.record_layout_qualifier,
eic.conversion_group_id,
eic.data_type,
eic.width data_length
FROM ece_interface_columns eic,
ece_interface_tables eit,
ece_level_matrices elm,
ece_external_levels eel
WHERE eit.interface_table_id = eic.interface_table_id AND
eit.transaction_type = p_trans_type AND
eit.interface_table_id = elm.interface_table_id AND
elm.external_level_id = eel.external_level_id AND
eel.external_level = eic.external_level AND
eel.map_id = p_map_id AND
eic.record_number = p_record_num
ORDER BY eic.record_number,eic.position;
l_insert_stmt :=
LPAD(interface_data_rec.Record_number,4,'0') ||' '||
LPAD(interface_data_rec.Position,4) ||' '||
LPAD(interface_data_rec.data_length,4) ||' '||
LPAD(l_start_position,5,' ') ||' '|| -- Bug # 948754 fix
RPAD(interface_data_rec.interface_column_name,45,' ') ||' '||
l_data_value;
EC_DEBUG.PL(3, 'l_insert_stmt: ', l_insert_stmt);
INSERT into ece_output( run_id, line_id, text)
VALUES(p_run_id, ece_output_runs_s.nextval, l_insert_stmt);
l_insert_stmt := NULL;
It does not delete any records, it simply sets flags.
Parameters:
p_transaction - The EDI Transaction Code (i.e. POI,INO)
p_short_name - The Short Name of the Concurrent program
defined for the transaction.
p_status - Status you want to set. Valid values
are ENABLE or DISABLE
**************************************************************/
/* Application Id for EDI Gateway is 175 */
p_application_id NUMBER := 175;
SELECT installed_flag INTO c_installed_flag
FROM ece_interface_tables
WHERE transaction_Type = p_transaction AND
output_level = 1 AND
ROWNUM = 1;
SELECT installed_flag INTO c_installed_flag
FROM ece_interface_tbls_upg
WHERE transaction_Type = p_transaction AND
output_level = 1 AND
ROWNUM = 1;
SELECT enabled_flag INTO temp_enabled_flag
FROM fnd_concurrent_programs
WHERE application_id = p_application_id AND
concurrent_program_name = UPPER(p_short_name);
/* Now go and update the EDI tables */
xProgress := 'UTILB-10-1070';
UPDATE ece_interface_tbls_upg
SET installed_flag = new_status
WHERE transaction_type = p_transaction;
UPDATE ece_interface_tables
SET installed_flag = new_status
WHERE transaction_type = p_transaction;
UPDATE ece_lookup_values
SET enabled_flag = new_status
WHERE lookup_type = 'DOCUMENT' AND
lookup_code = p_transaction;