[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
i_select_cursor INTEGER;
ec_utils.g_stack.DELETE;
ec_utils.g_insert_failed := 0;
select_stage ( i_select_cursor );
i_select_cursor
);
IF dbms_sql.IS_OPEN(i_select_cursor)
then
dbms_sql.close_cursor(i_select_cursor);
select document_id
from ece_stage
where run_id = p_run_id
and transaction_type = p_transaction_type
and transaction_level = 1
and line_number = 1
for update of Document_Id NOWAIT;
i_select_cursor INTEGER;
ec_utils.g_stack.DELETE;
ec_utils.g_insert_failed := 0;
select_stage ( i_select_cursor );
i_select_cursor
);
IF dbms_sql.IS_OPEN(i_select_cursor)
then
dbms_sql.close_cursor(i_select_cursor);
select document_id
from ece_stage
where run_id = p_run_id
and transaction_type = p_transaction_type
and transaction_level = 1
and line_number = 1
for update of Document_Id NOWAIT;
i_select_cursor number;
ec_utils.g_insert_failed := 0;
select_stage ( i_select_cursor );
i_select_cursor
);
or (ec_utils.g_ext_levels(1).Status = 'INSERT_FAILED')
then
rollback to Document_start;
update_document_status;
inserted into the production open Interface tables. Check whether
the document is ready for Insert.
If yes then save the Document and delete from the Staging table.
**/
if ( ec_utils.g_ext_levels(1).Status = 'INSERT'
or ec_utils.g_ext_levels(1).Status = 'NEW'
or ec_utils.g_ext_levels(1).Status = 'RE_PROCESS'
)
then
delete from ece_rule_violations
where document_id = c1.document_id;
delete from ece_stage
where document_id = c1.document_id;
ec_debug.pl(1,'EC','ECE_DELETE_FAILED_STAGING','DOCUMENT_ID',c1.document_id);
Insert_Into_Violations(c1.Document_Id);
IF dbms_sql.IS_OPEN(i_select_cursor)
then
dbms_sql.close_cursor(i_select_cursor);
select document_id
from ece_stage
where transaction_type = p_transaction_type
and status = p_status
and transaction_level = 1
and line_number = 1
for update of Document_Id NOWAIT;
i_select_cursor number;
ec_utils.g_stack.DELETE;
ec_utils.g_insert_failed := 0;
select_stage ( i_select_cursor );
i_select_cursor
);
IF dbms_sql.IS_OPEN(i_select_cursor)
then
dbms_sql.close_cursor(i_select_cursor);
select document_id
from ece_stage
where transaction_type = p_transaction_type
and transaction_level = 1
and line_number = 1
for update of Document_Id NOWAIT;
i_select_cursor number;
ec_utils.g_stack.DELETE;
ec_utils.g_insert_failed := 0;
select_stage ( i_select_cursor );
i_select_cursor
);
IF dbms_sql.IS_OPEN(i_select_cursor)
then
dbms_sql.close_cursor(i_select_cursor);
select document_id
from ece_stage
where transaction_type = p_transaction_type
and tp_code = p_tp_code
and status = p_status
and transaction_level = 1
and line_number = 1
for update of Document_Id NOWAIT;
select document_id
from ece_stage
where transaction_type = p_transaction_type
and tp_code is null
and status = p_status
and transaction_level = 1
and line_number = 1
for update of Document_Id NOWAIT;
i_select_cursor number;
ec_utils.g_stack.DELETE;
ec_utils.g_insert_failed := 0;
select_stage ( i_select_cursor );
i_select_cursor
);
i_select_cursor
);
IF dbms_sql.IS_OPEN(i_select_cursor)
then
dbms_sql.close_cursor(i_select_cursor);
i_select_cursor IN integer
)
IS
l_return_status VARCHAR2(1);
i_insert_ok BOOLEAN := FALSE;
i_insert BOOLEAN := FALSE;
i_last_insert_level number := 0;
select stage_id,rule_id,interface_column_id
from ece_rule_violations
where document_id = p_document_id and
violation_level = 'COLUMN' and
nvl(ignore_flag,'N') = 'Y';
ec_debug.pl(3,'i_select_cursor',i_select_cursor);
dbms_sql.bind_variable(i_select_cursor,'i_document_id',i_document_id);
dummy := dbms_sql.execute(i_select_cursor);
while dbms_sql.fetch_rows(i_select_cursor) > 0
loop
dbms_sql.column_value(i_select_cursor,1,i_stage_id);
dbms_sql.column_value(i_select_cursor,2,i_document_number);
dbms_sql.column_value(i_select_cursor,3,i_level);
dbms_sql.column_value(i_select_cursor,4,i_line_number);
dbms_sql.column_value(i_select_cursor,5,i_status);
dbms_sql.column_value(i_select_cursor,6,i_map_id);
previous level and the previous data wasn't inserted into the
open interface table, then we have to insert the previous data
before continue processing the new fetched data.
**/
if (i_level <= i_previous_level) and NOT (i_insert) then
if ec_debug.G_debug_level = 3 then
ec_debug.pl(3,'Ready to insert into open interface table');
If the last_insert_level is a lower level, then we have to
make sure we clean up all the lower level data so that it would
not carry over data from previous insert.
**/
if (i_last_insert_level > i_previous_level) then
for k in ec_utils.g_ext_levels(i_previous_level+1).file_start_pos..ec_utils.g_ext_levels(i_last_insert_level).file_end_pos
loop
ec_utils.g_file_tbl(k).value := m_file_tbl_empty(k).value;
i_last_insert_level := i_previous_level;
--Insert_into_prod_interface;
i_insert_ok := Insert_Into_prod_Interface
(
ec_utils.g_int_levels(i_interface_level).Cursor_handle,
i_interface_level
);
-- if Insert Failed then
if NOT ( i_insert_ok) then
ec_utils.g_ext_levels(1).Status := 'INSERT_FAILED';
ec_utils.g_insert_failed := ec_utils.g_insert_failed + 1;
ec_debug.pl(1,'EC','ECE_INSERT_SKIPPED','DOCUMENT_ID',i_document_id);
end if; --- Insert Check
Update the Level Info table for Latest Document Number , Stage Id and Level
**/
ec_utils.g_ext_levels(i_level).Stage_Id := i_stage_id;
Update Global variable to hold the Current level of the Record.
**/
ec_utils.g_current_level := i_level;
e.g. select statement build for Extract is
select Stage_Id,Document_Number,transaction_level,Line_number,Status,map_id,
Field1,Field2,Field3........Field500 from ece_stage;
in the Select Statement which is 6 + 3 ( 3 for Field3 , 5 for Field5).
Pass this to the DBMS_SQL call to get the Column Value.
**/
if ec_debug.G_debug_level = 3 then
ec_debug.pl(3,'EC','ECE_FIELDS_EXTRACTED_STAGING',null);
i_select_cursor,
i_field_number+6,
ec_utils.g_file_tbl(i).value
);
i_select_cursor,
ec_utils.g_file_tbl(i).staging_column_no+6,
ec_utils.g_file_tbl(i).value
);
g_col_rule_viol_tbl.delete;
The Exception processing does not update the Status of a record
if there are no Process rules or Column Rules defined .The Status
remains New, or Re-Process.
**/
(
ec_utils.g_ext_levels(i_level).Status = 'INSERT'
or ec_utils.g_ext_levels(i_level).Status = 'NEW'
or ec_utils.g_ext_levels(i_level).Status = 'RE_PROCESS'
)
then
/**
This is the new flexible hierarchy feature. It loops thru the
g_int_ext_levels and make sure all the data is completed
before it writes to the open interface table.
**/
for i in 1..ec_utils.g_int_ext_levels.COUNT
loop
if ec_utils.g_int_ext_levels(i).external_level = i_level then
i_interface_level := ec_utils.g_int_ext_levels(i).interface_level;
i_insert := FALSE;
i_insert := TRUE;
i_insert := TRUE;
if i_insert then
i_last_insert_level := i_level;
ec_debug.pl(3,'Ready to insert into open interface table');
--Insert_into_prod_interface;
i_insert_ok := Insert_Into_prod_Interface
(
ec_utils.g_int_levels(i_interface_level).Cursor_handle,
i_interface_level
);
-- if Insert Failed then
if NOT ( i_insert_ok)
then
ec_utils.g_ext_levels(1).Status := 'INSERT_FAILED';
ec_utils.g_insert_failed := ec_utils.g_insert_failed + 1;
ec_debug.pl(1,'EC','ECE_INSERT_SKIPPED','DOCUMENT_ID',i_document_id);
end if; --- Insert Check
end if; -- i_insert
if (i_insert) and not (i_insert_ok) then
-- Exit the processing for the document.
exit;
if (ec_utils.g_ext_levels(i_level).Status = 'INSERT'
or ec_utils.g_ext_levels(i_level).Status = 'NEW'
or ec_utils.g_ext_levels(i_level).Status = 'RE_PROCESS') and
Not (i_insert) then
if (i_last_insert_level > i_level) then
for k in ec_utils.g_ext_levels(i_level+1).file_start_pos..ec_utils.g_ext_levels(i_last_insert_level).file_end_pos
loop
ec_utils.g_file_tbl(k).value := m_file_tbl_empty(k).value;
ec_debug.pl(3,'Ready to insert into open interface table');
i_insert_ok := Insert_Into_prod_Interface
(
ec_utils.g_int_levels(i_interface_level).Cursor_handle,
i_interface_level
);
if NOT ( i_insert_ok) then
ec_utils.g_ext_levels(1).Status := 'INSERT_FAILED';
ec_utils.g_insert_failed := ec_utils.g_insert_failed + 1;
ec_debug.pl(1,'EC','ECE_INSERT_SKIPPED','DOCUMENT_ID',i_document_id);
procedure update_document_status
is
BEGIN
if ec_debug.G_debug_level >= 2 then
ec_debug.push('ECE_INBOUND.UPDATE_DOCUMENT_STATUS');
Update Header record in the Staging Table.
**/
update ece_stage
set status = ec_utils.g_ext_levels(1).Status
where document_id = ec_utils.g_ext_levels(1).Document_id
and transaction_level = 1
and line_number = 1;
ec_debug.pl(0,'EC','ECE_STATUS_UPDATE_FAILED','DOCUMENT_ID',
ec_utils.g_ext_levels(ec_utils.g_current_level).Document_Id);
Need to update all the previous processed line to have status
'INSERT' so that it will show the 'GREEN' icon in View Staged
Document form.
**/
update ece_stage
set status = 'INSERT'
where (stage_id > ec_utils.g_ext_levels(1).stage_id) and
(stage_id < ec_utils.g_ext_levels(ec_utils.g_current_level).stage_id);
Update the Status of the Line where error encountered in
the Staging Table.
**/
update ece_stage
set status = ec_utils.g_ext_levels(ec_utils.g_current_level).Status
where stage_id = ec_utils.g_ext_levels(ec_utils.g_current_level).stage_id;
ec_debug.pl(0,'EC','ECE_STATUS_UPDATE_FAILED','DOCUMENT_ID',
ec_utils.g_ext_levels(ec_utils.g_current_level).Document_Id);
ec_debug.pop('ECE_INBOUND.UPDATE_DOCUMENT_STATUS');
ec_debug.pl(0,'EC','ECE_PROGRAM_ERROR','PROGRESS_LEVEL','ECE_INBOUND.UPDATE_DOCUMENT_STATUS');
END update_document_status;
ec_utils.g_file_tbl.DELETE;
ec_utils.g_int_levels.DELETE;
ec_utils.g_ext_levels.DELETE;
ec_utils.g_int_ext_levels.DELETE;
ec_utils.g_stack_pointer.DELETE;
ec_utils.g_stage_data.DELETE;
ec_utils.g_stack.DELETE;
ece_rules_pkg.g_rule_violation_tbl.DELETE;
This helps in improving the perfromance , as un-necessary selects are saved.
**/
ec_utils.get_tran_stage_data ( i_transaction_type, i_map_id);
-- Initialize the g_stack instead of performing a delete.
-- ec_utils.g_stack.DELETE;
ec_utils.g_stack_pointer.DELETE;
ece_rules_pkg.g_rule_violation_tbl.DELETE;
if ec_utils.g_insert_failed > 0
then
ec_debug.pl(0,'EC','ECE_TOTAL_FAILED','FAILED',ec_utils.g_insert_failed);
The Cursors for the Insert into Open Interface table are not closed
in the Insert_Into_Prod_Interface function 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..ec_utils.g_ext_levels.COUNT
loop
IF dbms_sql.IS_OPEN(ec_utils.g_ext_levels(i).Cursor_Handle)
then
dbms_sql.Close_cursor(ec_utils.g_ext_levels(i).Cursor_Handle);
procedure Insert_into_violations
(
i_document_id IN number
)
is
begin
if ec_debug.G_debug_level >= 2 then
ec_debug.push('ECE_INBOUND.INSERT_INTO_VIOLATIONS');
Delete the Old violations for this Document
**/
Delete from ece_rule_violations
where document_id = i_document_id
and ignore_flag = 'N';
INSERT into ece_rule_violations
(
violation_id,
document_id,
stage_id,
interface_column_id,
rule_id,
transaction_type,
document_number,
violation_level,
ignore_flag,
message_text,
creation_date,
created_by,
last_update_date,
last_updated_by,
last_update_login
)
VALUES
(
ece_rules_pkg.g_rule_violation_tbl(i).violation_id,
ece_rules_pkg.g_rule_violation_tbl(i).document_id,
ece_rules_pkg.g_rule_violation_tbl(i).stage_id,
ece_rules_pkg.g_rule_violation_tbl(i).interface_column_id,
ece_rules_pkg.g_rule_violation_tbl(i).rule_id,
ece_rules_pkg.g_rule_violation_tbl(i).transaction_type,
ece_rules_pkg.g_rule_violation_tbl(i).document_number,
ece_rules_pkg.g_rule_violation_tbl(i).violation_level,
ece_rules_pkg.g_rule_violation_tbl(i).ignore_flag,
ece_rules_pkg.g_rule_violation_tbl(i).message_text,
sysdate,
fnd_global.user_id,
sysdate,
fnd_global.user_id,
fnd_global.login_id
);
ece_rules_pkg.g_rule_violation_tbl.DELETE;
ec_debug.pop('ECE_INBOUND.INSERT_INTO_VIOLATIONS');
ec_debug.pl(0,'EC','ECE_PROGRAM_ERROR','PROGRESS_LEVEL','ECE_INBOUND.INSERT_INTO_VIOLATIONS');
end Insert_Into_Violations;
i_select_cursor IN INTEGER
)
is
BEGIN
if ec_debug.G_debug_level >= 2 then
ec_debug.push('ECE_INBOUND.PROCESS_DOCUMENTS');
ec_debug.pl(3,'i_select_cursor',i_select_cursor);
i_select_cursor
);
or (ec_utils.g_ext_levels(1).Status = 'INSERT_FAILED')
then
rollback to Document_start;
update_document_status;
inserted into the production open Interface tables. Check whether
the document is ready for Insert.
If yes then save the Document and delete from the Staging table.
**/
if (
ec_utils.g_ext_levels(1).Status = 'INSERT'
or ec_utils.g_ext_levels(1).Status = 'NEW'
or ec_utils.g_ext_levels(1).Status = 'RE_PROCESS'
)
then
delete from ece_rule_violations
where document_id = i_document_id;
delete from ece_stage
where document_id = i_document_id;
ec_debug.pl(1,'EC','ECE_DELETE_FAILED_STAGING','DOCUMENT_ID',i_document_id);
Insert_Into_Violations(i_Document_Id);
procedure select_stage
(
i_select_cursor OUT NOCOPY integer
)
is
i_Select_Stmt varchar2(32000);
ec_debug.push('ECE_INBOUND.SELECT_STAGE');
i_Select_Stmt := 'select Stage_Id ,Document_Number ,transaction_level ,line_number ,Status , map_id, ';
Include all the 500 Columns in the Select Clause.
**/
for i in 1..500
loop
i_Select_Stmt := i_Select_Stmt ||'FIELD'||i||',';
i_Select_Stmt := RTRIM(i_Select_Stmt,',');
i_Select_Stmt := i_Select_Stmt ||' from ECE_STAGE where Document_Id = :i_document_id order by stage_id for update of Document_id NOWAIT';
dbms_sql.parse(i_Cursor_handle,i_Select_Stmt,dbms_sql.native);
ec_debug.pl(0,'EC','ECE_PROGRAM_ERROR','PROGRESS_LEVEL','ECE_INBOUND.SELECT_STAGE');
ece_error_handling_pvt.print_parse_error (error_position,i_Select_Stmt);
Define Columns used in the Select Clause
**/
dbms_sql.define_column(i_Cursor_Handle,1,i_stage_id);
ec_debug.pl(3,'Select Statement',i_select_stmt);
i_Select_Cursor := i_Cursor_Handle;
ec_debug.pl(3,'i_select_cursor',i_select_cursor);
ec_debug.pop('ECE_INBOUND.SELECT_STAGE');
ec_debug.pl(0,'EC','ECE_PROGRAM_ERROR','PROGRESS_LEVEL','ECE_INBOUND.SELECT_STAGE');
end select_stage;
function insert_into_prod_interface
(
i_Insert_cursor IN OUT NOCOPY INTEGER,
i_level IN NUMBER
)
return boolean
IS
cInsert_stmt VARCHAR2(32000) := 'INSERT INTO ';
c_Insert_cur pls_INTEGER ;
ec_debug.push('ECE_INBOUND.INSERT_INTO_PROD_INTERFACE');
ec_debug.pl(3,'i_Insert_Cursor',i_Insert_Cursor);
if i_Insert_Cursor = 0
then
i_Insert_Cursor := -911;
ec_debug.pl(3,'i_Insert_Cursor',i_Insert_Cursor);
if i_Insert_Cursor < 0
then
cInsert_Stmt := cInsert_Stmt||' '||ec_utils.g_int_levels(i_level).Base_Table_Name||' (';
cInsert_stmt :=cInsert_stmt||' '||ec_utils.g_file_tbl(i).base_column_name || ',';
cInsert_stmt := RTRIM (cInsert_stmt, ',') || ') ';
cInsert_stmt := cInsert_stmt || cValue_stmt;
ec_debug.pl(3,'Insert_Statement',cInsert_stmt);
i_Insert_Cursor := dbms_sql.open_cursor;
dbms_sql.parse(i_Insert_Cursor, cInsert_stmt, dbms_sql.native);
'ECE_INBOUND.INSERT_INTO_PROD_INTERFACE');
ece_error_handling_pvt.print_parse_error (error_position,cInsert_stmt);
ec_debug.pop('ECE_INBOUND.INSERT_INTO_PROD_INTERFACE');
if i_Insert_Cursor > 0
then
begin
for k in ec_utils.g_int_levels(i_level).file_start_pos..ec_utils.g_int_levels(i_level).file_end_pos
loop
if ec_utils.g_file_tbl(k).base_column_name is not null
then
BEGIN
-- This Begin is to trap the Data Type Conversion problem on a field.
if 'DATE' = ec_utils.g_file_tbl(k).data_type
Then
if ec_utils.g_file_tbl(k).value is not NULL
then
d_date := to_date(ec_utils.g_file_tbl(k).value,'YYYYMMDD HH24MISS');
dbms_sql.bind_variable(i_Insert_Cursor, 'b'||k, d_date);
dbms_sql.bind_variable(i_Insert_Cursor, 'b'||k, n_number);
dbms_sql.bind_variable(i_Insert_Cursor, 'b'||k,ec_utils.g_file_tbl(k).value);
dummy := dbms_sql.execute(i_Insert_Cursor);
ec_debug.pop('ECE_INBOUND.INSERT_INTO_PROD_INTERFACE');
'ECE_INBOUND.INSERT_INTO_PROD_INTERFACE');
ec_debug.pl(0,'EC','ECE_INSERT_FAILED',null);
ec_debug.pop('ECE_INBOUND.INSERT_INTO_PROD_INTERFACE');
END insert_into_prod_interface;