[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
select eel.start_element,
eel.external_level,
eit.interface_table_id,
eit.key_column_name,
eit.primary_address_type
from ece_interface_tables eit,
ece_level_matrices elm,
ece_external_levels eel
where eit.transaction_type = p_transaction_type
and eit.interface_table_id = elm.interface_table_id
and elm.external_level_id = eel.external_level_id
and eel.map_id = p_map_id
order by to_number(external_level);
select ece_stage_id_s.NEXTVAL
from dual;
select ece_document_id_s.NEXTVAL
from dual;
i_insert_cursor number :=0;
This table is updated after each record is read i.e.
Document Id , Stage Id , Line Number,Parent Stage id etc.
and is used while inserting a record in the Staging table for a level.
i_stage_record_type Stores the Mapping information for the Flat File
Level,Record Number,Position,Staging Column,Width etc.
**/
-- Initialize PL/SQL tables.
i_level_info.DELETE;
i_stage_record_type.DELETE;
ece_flatfile_pvt.t_tran_attribute_tbl.DELETE;
select ece_output_runs_s.NEXTVAL
into i_run_id
from dual;
i_level_info(i_level).Insert_Cursor := 0;
Make a copy of the PL/SQL table and save it. After Inserting the Data into the
staging table , initialize the PL/SQL table with values from saved PL/SQL table.
**/
i_empty_tbl := i_stage_record_type;
select value
into ec_inbound_stage.g_source_charset
from v$nls_parameters
where parameter='NLS_CHARACTERSET';
select value,decode(value,ec_inbound_stage.g_source_charset,'Y','N')
into i_db_charset,i_db_charset_flag
from v$nls_parameters
where parameter = 'NLS_CHARACTERSET';
select 'Y'
into i_fnd_charset_flag
from fnd_lookups
where lookup_type = 'FND_ISO_CHARACTER_SET_MAP'
and lookup_code = ec_inbound_stage.g_source_charset;
If matches , then Insert the Data for the previous level.
**/
For i in 1..i_level_info.COUNT
loop
if ( next_rec_number = i_level_info(i).start_record_number )
or ( end_of_file )
then
i_current_level := i;
Insert Data into Staging table for Previous Level of Document .
The value of the Insert Cursor for the First Call should be zero
, and the rest of the calls can take the returned Cursor handle.
This helps avoiding the Expensive Parsing for subsequent calls.
**/
Insert_into_Stage_table
(
i_previous_level,
i_map_id,
i_level_info(i_previous_level).Insert_Cursor
);
The Cursors for the Insert into Stage table are not closed in the Insert_Into_Stage_table
procedure call. Since the Cursor handles are maintained in the I_LEVEL_INFO PL/SQL table ,
Cursors for the all the Level are closed using these Cursor handles.
**/
For i in 1..i_level_info.COUNT
loop
IF dbms_sql.IS_OPEN(i_level_info(i).Insert_Cursor)
then
dbms_sql.Close_cursor(i_level_info(i).Insert_Cursor);
SELECT eic.interface_column_name,
eic.staging_column,
eic.record_number,
eic.position,
eic.width
FROM ece_interface_columns eic
WHERE eic.external_level = p_level
AND eic.map_id = p_map_id
AND eic.record_number IS NOT NULL
AND eic.position IS NOT NULL
AND eic.staging_column IS NOT NULL
ORDER BY eic.record_number, eic.position;
The Data loaded in the Local PL/SQL table is inserted into the Staging table.
This procedures takes Transaction Level and the Cursor handle as the parameter.
The Cursor handle is passed as 0 in the First call , and the subsequent calls
uses the Cursor Handle returned by the Procedure. This helps in avoiding the
expensive parsing of the SQL Statement again and again for the Same level.
**/
procedure Insert_Into_Stage_Table
(
i_level IN NUMBER,
i_map_id IN NUMBER,
i_insert_cursor IN OUT NOCOPY NUMBER
)
is
c_Insert_Cursor INTEGER;
cInsert_stmt varchar2(32000) := 'INSERT INTO ECE_STAGE ( ';
ec_debug.push('EC_INBOUND_STAGE.INSERT_INTO_STAGE_TABLE');
ec_debug.pl(3,'i_insert_cursor',i_insert_cursor);
if i_insert_cursor = 0
then
i_insert_cursor := -911;
if i_insert_cursor < 0
then
--- Add Mandatory Columns for the Record - includes the MAP_ID column
cInsert_stmt := cInsert_stmt||' Stage_id ,Document_Id ,Transaction_type ,Transaction_Level ,';
cInsert_stmt := cInsert_stmt||' Line_Number ,Parent_Stage_Id ,Run_Id ,Document_Number ,Status ,Tp_Code ,Map_ID ,';
-- cInsert_stmt := cInsert_stmt||' Parent_Stage_id ,Document_Id ,Transaction_type ,Transaction_Level ,';
-- cInsert_stmt := cInsert_stmt||' Line_Number ,Stage_Id ,Run_Id ,Document_Number ,Status ,Tp_Code ,Map_ID ,';
cInsert_stmt := cInsert_stmt||' creation_date ,created_by ,last_update_date ,last_updated_by ,';
--- Build Insert Statement
cInsert_stmt := cInsert_stmt||' '||i_stage_record_type(i).staging_column|| ',';
cInsert_stmt := RTRIM(cInsert_stmt,',')||')';
cInsert_stmt := cInsert_stmt||cValue_stmt;
ec_debug.pl(3,'EC','ECE_STAGE_INSERT_LEVEL','LEVEL',i_level,null);
ec_debug.pl(3,cInsert_stmt);
i_Insert_Cursor := dbms_sql.Open_Cursor;
dbms_sql.parse(i_Insert_Cursor,cInsert_stmt,dbms_sql.native);
ec_debug.pl(0,'EC','ECE_PROGRAM_ERROR','PROGRESS_LEVEL','EC_INBOUND_STAGE.INSERT_INTO_STAGE_TABLE');
ece_error_handling_pvt.print_parse_error (error_position,cInsert_stmt);
if i_Insert_Cursor > 0
then
begin
-- Bug 2164672
if i_data_status_flag then
i_level_info(i_level).Status := 'NEW';
dbms_sql.bind_variable (i_Insert_Cursor,'a1',to_number(i_level_info(i_level).Stage_Id));
dbms_sql.bind_variable (i_Insert_Cursor,'a6',i_level_info(i_level).Parent_Stage_Id);
dbms_sql.bind_variable (i_Insert_Cursor,'a2',to_number(i_level_info(i_level).Document_Id));
dbms_sql.bind_variable (i_Insert_Cursor,'a3',i_level_info(i_level).Transaction_Type);
dbms_sql.bind_variable (i_Insert_Cursor,'a4',to_number(i_level));
dbms_sql.bind_variable (i_Insert_Cursor,'a5',to_number(i_level_info(i_level).Line_Number));
dbms_sql.bind_variable (i_Insert_Cursor,'a7',to_number(i_level_info(i_level).Run_Id));
dbms_sql.bind_variable (i_Insert_Cursor,'a8',i_level_info(i_level).Document_Number);
dbms_sql.bind_variable (i_Insert_Cursor,'a9',i_level_info(i_level).Status);
dbms_sql.bind_variable (i_Insert_Cursor,'a10',i_level_info(i_level).Tp_Code);
dbms_sql.bind_variable (i_Insert_Cursor,'a11',i_map_id);
dbms_sql.bind_variable (i_Insert_Cursor,'w1',sysdate);
dbms_sql.bind_variable (i_Insert_Cursor,'w2',fnd_global.user_id);
dbms_sql.bind_variable (i_Insert_Cursor,'w3',sysdate);
dbms_sql.bind_variable (i_Insert_Cursor,'w4',fnd_global.user_id);
ec_debug.pl(3,'LAST_UPDATE_DATE',sysdate);
ec_debug.pl(3,'LAST_UPDATED_BY',fnd_global.user_id);
i_Insert_Cursor,
'b'||k,
i_stage_record_type(k).value
);
dummy := dbms_sql.execute(i_Insert_Cursor);
ec_debug.pl(3,'EC','ECE_STAGE_INSERTED',null);
'EC_INBOUND_STAGE.INSERT_INTO_STAGE_TABLE');
ec_debug.pl(0,cInsert_stmt);
ec_debug.pl(3,'i_insert_cursor',i_insert_cursor);
ec_debug.pop('EC_INBOUND_STAGE.INSERT_INTO_STAGE_TABLE');
IF dbms_sql.IS_OPEN(i_insert_cursor)
then
dbms_sql.close_cursor(i_insert_cursor);
ec_debug.pl(0,'EC','ECE_PROGRAM_ERROR','PROGRESS_LEVEL','EC_INBOUND_STAGE.INSERT_INTO_STAGE_TABLE');
END Insert_Into_Stage_Table;
select tp_code
from ece_tp_details td,
hz_cust_acct_sites_all hcas,
ece_tp_headers th
where td.translator_code = i_translator_code and
hcas.ece_tp_location_code = i_location_code and
hcas.tp_header_id = td.tp_header_id and
td.tp_header_id = th.tp_header_id and
td.document_id = i_transaction_type and
NVL(hcas.ORG_ID,NVL(TO_NUMBER(DECODE(SUBSTRB(USERENV('CLIENT_INFO'),1 ,1),
' ',NULL,SUBSTRB(USERENV('CLIENT_INFO'),1,10))),-99))
= NVL(TO_NUMBER(DECODE( SUBSTRB(USERENV('CLIENT_INFO'),1,1),
' ',NULL,SUBSTRB(USERENV('CLIENT_INFO'),1,10))),-99) and
rownum = 1;
select tp_code
from ece_tp_details td,
ra_addresses ra,
ece_tp_headers th
where td.translator_code = i_translator_code and
ra.ece_tp_location_code = i_location_code and
ra.tp_header_id = td.tp_header_id and
td.tp_header_id = th.tp_header_id and
td.document_id = i_transaction_type and
rownum = 1;
select tp_code
from ece_tp_details td,
po_vendor_sites pvs,
ece_tp_headers th
where td.translator_code = i_translator_code and
pvs.ece_tp_location_code = i_location_code and
pvs.tp_header_id = td.tp_header_id and
th.tp_header_id = td.tp_header_id and
td.document_id = i_transaction_type and
rownum = 1;
select tp_code
from ece_tp_details td,
ap_bank_branches abb,
ece_tp_headers th
where td.translator_code = i_translator_code and
abb.ece_tp_location_code = i_location_code and
abb.tp_header_id = td.tp_header_id and
th.tp_header_id = td.tp_header_id and
td.document_id = i_transaction_type and
rownum = 1;
select tp_code
from ece_tp_details td,
hr_locations hrl,
ece_tp_headers th
where td.translator_code = i_translator_code and
hrl.ece_tp_location_code = i_location_code and
hrl.tp_header_id = td.tp_header_id and
th.tp_header_id = td.tp_header_id and
td.document_id = i_transaction_type and
rownum = 1;