[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT text
FROM ece_output
WHERE run_id = iRun_id
ORDER BY line_id;
SELECT ece_output_runs_s.NEXTVAL
INTO iRun_id
FROM dual;
DELETE
FROM ece_output
WHERE run_id = iRun_id;
'ECE_NO_ROW_DELETED',
'PROGRESS_LEVEL',
xProgress,
'TABLE_NAME',
'ECE_OUTPUT');
cHeader_select VARCHAR2(32000);
cLine_select VARCHAR2(32000);
cLocation_select VARCHAR2(32000);
ece_extract_utils_pub.select_clause(cTransaction_Type,
cCommunication_Method,
cHeader_Interface,
l_header_tbl,
cHeader_select,
cHeader_from,
cHeader_where);
ece_extract_utils_pub.select_clause(cTransaction_Type,
cCommunication_Method,
cLine_Interface,
l_line_tbl,
cLine_select,
cLine_from,
cLine_where);
ece_extract_utils_pub.select_clause(cTransaction_Type,
cCommunication_Method,
cLocation_Interface,
l_location_tbl,
cLocation_select,
cLocation_from,
cLocation_where);
cHeader_select := cHeader_select ||
cHeader_from ||
cHeader_where;
ec_debug.pl(3, 'cHeader_select: ',cHeader_select);
cLine_select := cLine_select ||
cLine_from ||
cLine_where;
ec_debug.pl(3, 'cLine_select: ',cLine_select);
cLocation_select := cLocation_select ||
cLocation_from ||
cLocation_where;
ec_debug.pl(3, 'cLocation_select: ',cLocation_select);
cHeader_select,
DBMS_SQL.NATIVE);
cHeader_select);
cLine_select,
DBMS_SQL.NATIVE);
cLine_select);
cLocation_select,
DBMS_SQL.NATIVE);
cLocation_select);
cHeader_select,
ece_extract_utils_PUB.G_MaxColWidth,
l_Header_tbl);
cLine_select,
ece_extract_utils_PUB.G_MaxColWidth,
l_Line_tbl);
cLocation_select,
ece_extract_utils_PUB.G_MaxColWidth,
l_Location_tbl);
SELECT ece_MVSTO_headers_s.NEXTVAL
INTO l_header_fkey
FROM DUAL;
ece_Extract_Utils_PUB.insert_into_interface_tbl(iRun_id,
cTransaction_Type,
cCommunication_Method,
cHeader_Interface,
l_header_tbl,
l_header_fkey);
SELECT ece_MVSTO_details_s.NEXTVAL
INTO l_line_fkey
FROM DUAL;
ece_Extract_Utils_PUB.insert_into_interface_tbl(iRun_id,
cTransaction_Type,
cCommunication_Method,
cLine_Interface,
l_line_tbl,
l_line_fkey);
SELECT ece_MVSTO_locations_s.NEXTVAL
INTO l_location_fkey
FROM DUAL;
ece_Extract_Utils_PUB.insert_into_interface_tbl(iRun_id,
cTransaction_Type,
cCommunication_Method,
cLine_Interface,
l_location_tbl,
l_location_fkey);
UPDATE mtl_movement_statistics
SET EDI_TRANSACTION_REFERENCE = v_EdiTransactionRef,
EDI_TRANSACTION_DATE = SYSDATE,
EDI_SENT_FLAG = 'Y'
WHERE movement_id = n_movement_id;
'ECE_NO_ROW_UPDATED',
'PROGRESS_LEVEL',
xProgress,
'INFO',
'TIME STAMP',
'TABLE_NAME',
'MTL_MOVEMENT_STATISTICS' );
cHeader_select VARCHAR2(32000);
cLine_select VARCHAR2(32000);
cLocation_select VARCHAR2(32000);
cHeader_delete1 VARCHAR2(32000);
cLine_delete1 VARCHAR2(32000);
cLocation_delete1 VARCHAR2(32000);
cHeader_delete2 VARCHAR2(32000);
cLine_delete2 VARCHAR2(32000);
cLocation_delete2 VARCHAR2(32000);
ece_flatfile_pvt.select_clause(cTransaction_Type,
cCommunication_Method,
cHeader_Interface,
cHeader_X_Interface,
l_header_tbl,
c_header_common_key_name,
cHeader_select,
cHeader_from,
cHeader_where);
ece_flatfile_pvt.select_clause(cTransaction_Type,
cCommunication_Method,
cLine_Interface,
cLine_X_Interface,
l_line_tbl,
c_line_common_key_name,
cLine_select,
cLine_from,
cLine_where);
ece_flatfile_pvt.select_clause(cTransaction_Type,
cCommunication_Method,
cLocation_Interface,
cLocation_X_Interface,
l_location_tbl,
c_location_key_name,
cLocation_select,
cLocation_from,
cLocation_where);
cHeader_select := cHeader_select ||
',' ||
cHeader_Interface ||
'.ROWID,' ||
cHeader_X_Interface ||
'.ROWID ';
cLine_select := cLine_select ||
',' ||
cLine_Interface ||
'.ROWID,' ||
cLine_X_Interface ||
'.ROWID ';
cLocation_select := cLocation_select ||
',' ||
cLocation_Interface ||
'.ROWID,' ||
cLocation_X_Interface ||
'.ROWID ';
cHeader_select := cHeader_select ||
cHeader_from ||
cHeader_where;
ec_debug.pl(3, 'cHeader_select: ',cHeader_select);
cLine_select := cLine_select ||
cLine_from ||
cLine_where ||
' ORDER BY ' ||
cLine_Interface ||
'.MOVEMENT_ID ';
ec_debug.pl(3, 'cLine_select: ',cLine_select);
cLocation_select := cLocation_select ||
cLocation_from ||
cLocation_where;
ec_debug.pl(3, 'cLocation_select: ',cLocation_select);
cHeader_delete1 := 'DELETE FROM ' ||
cHeader_Interface ||
' WHERE ROWID = :col_rowid';
ec_debug.pl(3, 'cHeader_delete1: ',cHeader_delete1);
cLine_delete1 := 'DELETE FROM ' ||
cLine_Interface ||
' WHERE ROWID = :col_rowid';
ec_debug.pl(3, 'cLine_delete1: ',cLine_delete1);
cLocation_delete1 := 'DELETE FROM ' ||
cLocation_Interface ||
' WHERE ROWID = :col_rowid';
ec_debug.pl(3, 'cLocation_delete1: ',cLocation_delete1);
cHeader_delete2 := 'DELETE FROM ' ||
cHeader_X_Interface ||
' WHERE ROWID = :col_rowid';
ec_debug.pl(3, 'cHeader_delete2: ',cHeader_delete2);
cLine_delete2 := 'DELETE FROM ' ||
cLine_X_Interface ||
' WHERE ROWID = :col_rowid';
ec_debug.pl(3, 'cLine_delete2: ',cLine_delete2);
cLocation_delete2 := 'DELETE FROM ' ||
cLocation_X_Interface ||
' WHERE ROWID = :col_rowid';
ec_debug.pl(3, 'cLocation_delete2: ',cLocation_delete2);
cHeader_select,
DBMS_SQL.NATIVE);
cHeader_select);
cLine_select,
DBMS_SQL.NATIVE);
cLine_select);
cLocation_select,
DBMS_SQL.NATIVE);
cLocation_select);
DBMS_SQL.PARSE(Header_del_c1 ,cHeader_delete1 ,DBMS_SQL.NATIVE);
cHeader_delete1);
DBMS_SQL.PARSE(Line_del_c1 ,cLine_delete1 ,DBMS_SQL.NATIVE);
cLine_delete1);
DBMS_SQL.PARSE(Location_del_c1 ,cLocation_delete1 ,DBMS_SQL.NATIVE);
cLocation_delete1);
DBMS_SQL.PARSE(Header_del_c2 ,cHeader_delete2 ,DBMS_SQL.NATIVE);
cHeader_delete2);
DBMS_SQL.PARSE(Line_del_c2 ,cLine_delete2 ,DBMS_SQL.NATIVE);
cLine_delete2);
DBMS_SQL.PARSE(Location_del_c2 ,cLocation_delete2 ,DBMS_SQL.NATIVE);
cLocation_delete2);
cHeader_select,
ece_flatfile_pvt.G_MaxColWidth,
l_header_tbl);
cLine_select,
ece_flatfile_pvt.G_MaxColWidth,
l_line_tbl);
cLocation_select,
ece_flatfile_pvt.G_MaxColWidth,
l_location_tbl);