[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT text
FROM ece_output
WHERE run_id = p_run_id
ORDER BY line_id;
SELECT ece_output_runs_s.NEXTVAL
INTO p_run_id
FROM sys.dual;
** Open the cursor to select the actual file output from ece_output.
**
*/
xProgress := 'ADVO-10-1060';
** Assume everything went ok so delete the records from ece_output.
**
*/
xProgress := 'ADVO-10-1090';
DELETE
FROM ece_output
WHERE run_id = p_run_id;
'ECE_NO_ROW_DELETED',
'PROGRESS_LEVEL',
xProgress,
'TABLE_NAME',
'ECE_OUTPUT' );
cHeader_select VARCHAR2(32000);
cLine_select VARCHAR2(32000);
** The 'select_clause' procedure will build the SELECT, FROM and WHERE
** clauses in preparation for the dynamic SQL call using the EDI data
** dictionary for the build. Any necessary customizations to these
** clauses need to be made *after* the clause is built, but *before*
** the SQL call.
**
*/
xProgress := 'ADVOB-10-1040';
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 );
** Build the complete SELECT statement for each level.
**
*/
xProgress := 'ADVOB-10-1180';
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 );
** Open a cursor for each of the SELECT calls. This tells the
** database to reserve space for the data returned by the SELECT
** statement.
**
*/
xProgress := 'ADVOB-10-1200';
** Parse each SELECT statement so the database understands the
** command. If the parse fails, trap and print the point of
** failure and exit the procedure with an error.
**
*/
xProgress := 'ADVOB-10-1220';
cHeader_select,
dbms_sql.native );
cHeader_select );
cLine_select,
dbms_sql.native );
cLine_select );
** Define the data type for every column in each SELECT statement
** so the database understands how to populate it.
**
*/
xProgress := 'ADVOB-10-1260';
cHeader_select,
ece_extract_utils_PUB.G_MaxColWidth );
cLine_select,
ece_extract_utils_PUB.G_MaxColWidth );
** Execute the Header level SELECT statement.
**
*/
xProgress := 'ADVOB-10-1302';
** Update ECE_ADVO_HEADERS to archive the current Advice header.
**
*/
UPDATE ece_advo_headers
SET edi_processed_flag = 'Y',
edi_process_date = SYSDATE
WHERE advice_header_id = l_header_tbl(n_advice_header_id_pos).value;
'ECE_NO_ROW_UPDATED',
'PROGRESS_LEVEL',
xProgress,
'INFO',
'EDI_PROCESSED_FLAG',
'TABLE_NAME',
'ECE_ADVO_HEADERS' );
** insert this record into the Header interface table.
**
*/
xProgress := 'ADVOB-10-1350';
SELECT ece_advo_headers_interface_s.nextval
INTO l_header_fkey
FROM sys.dual;
ece_Extract_Utils_PUB.insert_into_interface_tbl ( iRun_id,
cTransaction_Type,
cCommunication_Method,
cHeader_Interface,
l_header_tbl,
l_header_fkey );
** the SELECT clause of the Line level.
**
*/
xProgress := 'ADVOB-10-1380';
** Execute the Line level SELECT statement.
**
*/
xProgress := 'ADVOB-10-1390';
** insert this record into the Line interface table.
**
*/
xProgress := 'ADVOB-10-1430';
SELECT ece_advo_details_interface_s.nextval
INTO l_line_fkey
FROM sys.dual;
ece_Extract_Utils_PUB.insert_into_interface_tbl ( iRun_id,
cTransaction_Type,
cCommunication_Method,
cLine_Interface,
l_line_tbl,
l_line_fkey );
cHeader_select VARCHAR2(32000);
cLine_select VARCHAR2(32000);
cHeader_delete1 VARCHAR2(32000);
cLine_delete1 VARCHAR2(32000);
cHeader_delete2 VARCHAR2(32000);
cLine_delete2 VARCHAR2(32000);
** The 'select_clause' procedure will build the SELECT, FROM and WHERE
** clauses in preparation for the dynamic SQL call using the EDI data
** dictionary for the build. Any necessary customizations to these
** clauses need to be made *after* the clause is built, but *before*
** the SQL call.
**
*/
xProgress := 'ADVOB-20-1020';
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 );
** customize the SELECT clauses to include the ROWID. Records
** will be deleted from the interface tables using these ROWID
** values.
**
*/
xProgress := 'ADVOB-20-1040';
cHeader_select := cHeader_select ||
',' ||
cHeader_Interface ||
'.ROWID, ' ||
cHeader_X_Interface ||
'.ROWID';
cLine_select := cLine_select ||
',' ||
cLine_Interface ||
'.ROWID,' ||
cLine_X_Interface ||
'.ROWID';
** Build the complete SELECT and DELETE statements
** for each level.
**
*/
xProgress := 'ADVOB-20-1080';
cHeader_select := cHeader_select ||
cHeader_from ||
cHeader_where ||
' FOR UPDATE';
ec_debug.pl ( 3, 'cHeader_select: ',cHeader_select );
cLine_select := cLine_select ||
cLine_from ||
cLine_where ||
' FOR UPDATE';
ec_debug.pl ( 3, 'cLine_select: ',cLine_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 );
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 );
** Open a cursor for each SELECT and DELETE call. This tells
** the database to reserve space for the data returned by the
** SELECT and DELETE statements.
**
*/
xProgress := 'ADVOB-20-1140';
** Parse each SELECT and DELETE statement so the database understands
** the command.
**
*/
xProgress := 'ADVOB-20-1200';
cHeader_select,
dbms_sql.native );
cHeader_select );
cLine_select,
dbms_sql.native );
cLine_select );
cHeader_delete1,
dbms_sql.native );
cHeader_delete1 );
cLine_delete1,
dbms_sql.native );
cLine_delete1 );
cHeader_delete2,
dbms_sql.native );
cHeader_delete2 );
cLine_delete2,
dbms_sql.native );
cLine_delete2 );
** SELECT statement so the database understands how to
** populate it.
**
*/
xProgress := 'ADVOB-20-1280';
cHeader_select,
ece_flatfile_pvt.G_MaxColWidth );
** DELETE statements.
**
*/
xProgress := 'ADVOB-20-1290';
** SELECT statement so the database understands how to
** populate it.
**
*/
xProgress := 'ADVOB-20-1310';
cLine_select,
ece_flatfile_pvt.G_MaxColWidth );
** DELETE statements.
**
*/
xProgress := 'ADVOB-20-1320';
** Execute the Header level SELECT statement.
**
*/
xProgress := 'ADVOB-20-1380';
** SELECT clause.
**
*/
xProgress := 'ADVOB-20-1460';
** Execute the Line level SELECT statement.
**
*/
xProgress := 'ADVOB-20-1470';
** Bind the variables (ROWIDs) in the DELETE statements.
**
*/
xProgress := 'ADVOB-20-1540';
** Delete the rows from the interface table.
**
*/
xProgress := 'ADVOB-20-1560';
** Bind the variables (ROWIDs) in the DELETE statements.
**
*/
xProgress := 'ADVOB-20-1580';
** Delete the rows from the interface table.
**
*/
xProgress := 'ADVOB-20-1600';