[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT text
FROM ece_output
WHERE run_id = p_RunID
ORDER BY line_id;
SELECT ece_output_runs_s.NEXTVAL
INTO p_RunID
FROM sys.dual;
SELECT COUNT(*)
INTO v_OutputRecordCount
FROM ece_output
WHERE run_id = p_RunID;
DELETE FROM ece_output
WHERE run_id = p_RunID;
'ECE_NO_ROW_DELETED',
'PROGRESS_LEVEL',
xProgress,
'TABLE_NAME',
'ECE_OUTPUT' );
v_SHP_Select VARCHAR2(32000);
v_STX_Select VARCHAR2(32000);
v_ORD_Select VARCHAR2(32000);
v_OAC_Select VARCHAR2(32000);
v_OTX_Select VARCHAR2(32000);
v_DTL_Select VARCHAR2(32000);
v_DAC_Select VARCHAR2(32000);
v_DTX_Select VARCHAR2(32000);
v_ALL_Select VARCHAR2(32000);
SELECT
assignment_type,
format_size,
nvl(pad_char,' ')
INTO
v_assignment_type,
v_format_size,
v_pad_char
FROM
sy_docs_seq
WHERE
orgn_code=v_Orgn_Code AND
doc_type='OPSP'
;
SELECT
lpad(p_BOL_No_From, v_format_size, v_pad_char)
INTO
v_BOL_No_From
FROM
dual
;
SELECT
lpad(p_BOL_No_To, v_format_size, v_pad_char)
INTO
v_BOL_No_To
FROM
dual
;
/* 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 := 'GASNOB-10-1220';
ece_extract_utils_pub.select_clause ( p_TransactionType,
p_CommunicationMethod,
p_SHP_Interface,
v_SHP_Table,
v_SHP_Select,
v_SHP_From,
v_SHP_Where );
ece_extract_utils_pub.select_clause ( p_TransactionType,
p_CommunicationMethod,
p_STX_Interface,
v_STX_Table,
v_STX_Select,
v_STX_From,
v_STX_Where );
ece_extract_utils_pub.select_clause ( p_TransactionType,
p_CommunicationMethod,
p_ORD_Interface,
v_ORD_Table,
v_ORD_Select,
v_ORD_From,
v_ORD_Where );
ece_extract_utils_pub.select_clause ( p_TransactionType,
p_CommunicationMethod,
p_OAC_Interface,
v_OAC_Table,
v_OAC_Select,
v_OAC_From,
v_OAC_Where );
ece_extract_utils_pub.select_clause ( p_TransactionType,
p_CommunicationMethod,
p_OTX_Interface,
v_OTX_Table,
v_OTX_Select,
v_OTX_From,
v_OTX_Where );
ece_extract_utils_pub.select_clause ( p_TransactionType,
p_CommunicationMethod,
p_DTL_Interface,
v_DTL_Table,
v_DTL_Select,
v_DTL_From,
v_DTL_Where );
ece_extract_utils_pub.select_clause ( p_TransactionType,
p_CommunicationMethod,
p_DAC_Interface,
v_DAC_Table,
v_DAC_Select,
v_DAC_From,
v_DAC_Where );
ece_extract_utils_pub.select_clause ( p_TransactionType,
p_CommunicationMethod,
P_DTX_Interface,
v_DTX_Table,
v_DTX_Select,
v_DTX_From,
v_DTX_Where );
ece_extract_utils_pub.select_clause ( p_TransactionType,
p_CommunicationMethod,
p_ALL_Interface,
v_ALL_Table,
v_ALL_Select,
v_ALL_From,
v_ALL_Where );
'( SELECT DISTINCT Order_ID ' ||
' FROM ' || p_DTL_Interface || '_V ' ||
' WHERE ' || p_DTL_Interface || '_V.BOL_ID = :BOL_ID ) ';
/* Build the complete SELECT statement for each level. */
/* */
xProgress := 'GASNOB-10-1360';
v_SHP_Select := v_SHP_Select ||
v_SHP_From ||
v_SHP_Where;
ec_debug.pl ( 3, 'v_SHP_Select: ', v_SHP_Select );
v_STX_Select := v_STX_Select ||
v_STX_From ||
v_STX_Where;
ec_debug.pl ( 3, 'v_STX_Select: ', v_STX_Select );
v_ORD_Select := v_ORD_Select ||
v_ORD_From ||
v_ORD_Where;
ec_debug.pl ( 3, 'v_ORD_Select: ', v_ORD_Select );
v_OAC_Select := v_OAC_Select ||
v_OAC_From ||
v_OAC_Where;
ec_debug.pl ( 3, 'v_OAC_Select: ', v_OAC_Select );
v_OTX_Select := v_OTX_Select ||
v_OTX_From ||
v_OTX_Where;
ec_debug.pl ( 3, 'v_OTX_Select: ', v_OTX_Select );
v_DTL_Select := v_DTL_Select ||
v_DTL_From ||
v_DTL_Where;
ec_debug.pl ( 3, 'v_DTL_Select: ', v_DTL_Select );
v_DAC_Select := v_DAC_Select ||
v_DAC_From ||
v_DAC_Where;
ec_debug.pl ( 3, 'v_DAC_Select: ', v_DAC_Select );
v_DTX_Select := v_DTX_Select ||
v_DTX_From ||
v_DTX_Where;
ec_debug.pl ( 3, 'v_DTX_Select: ', v_DTX_Select );
v_ALL_Select := v_ALL_Select ||
v_ALL_From ||
v_ALL_Where;
/* 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 := 'GASNOB-10-1370';
/* Parse each SELECT statement so the database understands the */
/* command. */
/* */
xProgress := 'GASNOB-10-1390';
v_SHP_Select,
dbms_sql.native );
v_SHP_Select );
v_STX_Select,
dbms_sql.native );
v_STX_Select );
/* dbms_output.put_line( ' Length of ORD Select = ' || length( v_ORD_Select)); */
v_ORD_Select,
dbms_sql.native );
v_ORD_Select );
v_OAC_Select,
dbms_sql.native );
v_OAC_Select );
v_OTX_Select,
dbms_sql.native );
v_OTX_Select );
dbms_output.put_line( ' Length of DTL Select = ' || length( v_DTL_Select));
dbms_output.put_line( substr( v_DTL_Select,1,100));
dbms_output.put_line( substr( v_DTL_Select,101,100));
dbms_output.put_line( substr( v_DTL_Select,201,100));
dbms_output.put_line( substr( v_DTL_Select,301,100));
dbms_output.put_line( substr( v_DTL_Select,401,100));
dbms_output.put_line( substr( v_DTL_Select,501,100));
dbms_output.put_line( substr( v_DTL_Select,601,100));
dbms_output.put_line( substr( v_DTL_Select,701,100));
dbms_output.put_line( substr( v_DTL_Select,801,100));
dbms_output.put_line( substr( v_DTL_Select,901,100));
dbms_output.put_line( substr( v_DTL_Select,1001,100));
dbms_output.put_line( substr( v_DTL_Select,1101,100));
dbms_output.put_line( substr( v_DTL_Select,1201,100));
dbms_output.put_line( substr( v_DTL_Select,1301,100));
dbms_output.put_line( substr( v_DTL_Select,1401,100));
dbms_output.put_line( substr( v_DTL_Select,1501,100));
dbms_output.put_line( substr( v_DTL_Select,1601,100));
dbms_output.put_line( substr( v_DTL_Select,1701,100));
dbms_output.put_line( substr( v_DTL_Select,1801,100));
dbms_output.put_line( substr( v_DTL_Select,1901,100));
dbms_output.put_line( substr( v_DTL_Select,2001,100));
dbms_output.put_line( substr( v_DTL_Select,2101,100));
dbms_output.put_line( substr( v_DTL_Select,2201,100));
dbms_output.put_line( substr( v_DTL_Select,2301,100));
dbms_output.put_line( substr( v_DTL_Select,2401,100));
dbms_output.put_line( substr( v_DTL_Select,2501,100));
dbms_output.put_line( substr( v_DTL_Select,2601,100));
dbms_output.put_line( substr( v_DTL_Select,2701,100));
dbms_output.put_line( substr( v_DTL_Select,2801,100));
dbms_output.put_line( substr( v_DTL_Select,2901,100));
dbms_output.put_line( substr( v_DTL_Select,3001,100));
v_DTL_Select,
dbms_sql.native );
v_DTL_Select );
v_DAC_Select,
dbms_sql.native );
v_DAC_Select );
v_DTX_Select,
dbms_sql.native );
v_DTX_Select );
v_ALL_Select,
dbms_sql.native );
v_ALL_Select );
/* Define the data type for every column in each SELECT statement */
/* so the database understands how to populate it. Using the */
/* K.I.S.S. principle, every data type will be converted to */
/* VARCHAR2. */
/* */
xProgress := 'GASNOB-10-1466';
v_SHP_Select,
ece_extract_utils_pub.G_MaxColWidth );
v_STX_Select,
ece_extract_utils_pub.G_MaxColWidth );
v_ORD_Select,
ece_extract_utils_pub.G_MaxColWidth );
v_OAC_Select,
ece_extract_utils_pub.G_MaxColWidth );
v_OTX_Select,
ece_extract_utils_pub.G_MaxColWidth );
v_DTL_Select,
ece_extract_utils_pub.G_MaxColWidth );
v_DAC_Select,
ece_extract_utils_pub.G_MaxColWidth );
v_DTX_Select,
ece_extract_utils_pub.G_MaxColWidth );
v_ALL_Select,
ece_extract_utils_pub.G_MaxColWidth );
/* Bind the variables in the Delivery level SELECT clause. */
/* */
xProgress := 'GASNOB-10-1600';
/* Execute the SHP level SELECT statement. */
/* */
xProgress := 'GASNOB-10-1610';
/* the SELECT clauses of the other levels. */
/* */
xProgress := 'GASNOB-10-1650';
/* insert this record into the Ship interface table. */
/* */
xProgress := 'GASNOB-10-1810';
SELECT GML_GASNO_SHIPMENTS_S.nextval
INTO v_SHP_Key
FROM sys.dual;
ece_extract_utils_pub.insert_into_interface_tbl ( p_RunID,
p_TransactionType,
p_CommunicationMethod,
p_SHP_Interface,
v_SHP_Table,
v_SHP_Key );
/* Execute the Ship Text level SELECT statement. */
/* */
xProgress := 'GASNOB-10-1670';
/* level table, use the same key value to insert this record into */
/* the Ship Text table. */
/* */
v_STX_Key := v_SHP_Key;
ece_extract_utils_pub.insert_into_interface_tbl ( p_RunID,
p_TransactionType,
p_CommunicationMethod,
p_STX_Interface,
v_STX_Table,
v_STX_Key );
/* Execute the Order level SELECT statement. */
/* */
xProgress := 'GASNOB-10-1900';
/* the SELECT clauses of the other levels. */
/* */
xProgress := 'GASNOB-10-1935';
/* insert this record into the Order interface table. */
/* */
xProgress := 'GASNOB-10-1950';
SELECT GML_GASNO_ORDERS_S.nextval
INTO v_ORD_Key
FROM sys.dual;
ece_extract_utils_pub.insert_into_interface_tbl ( p_RunID,
p_TransactionType,
p_CommunicationMethod,
p_ORD_Interface,
v_ORD_Table,
v_ORD_Key );
/* Execute the Charge/Allowance level SELECT statement. */
/* */
xProgress := 'GASNOB-10-1710';
ece_extract_utils_pub.insert_into_interface_tbl ( p_RunID,
p_TransactionType,
p_CommunicationMethod,
p_OAC_Interface,
v_OAC_Table,
v_OAC_Key );
ece_extract_utils_pub.insert_into_interface_tbl ( p_RunID,
p_TransactionType,
p_CommunicationMethod,
p_OTX_Interface,
v_OTX_Table,
v_OTX_Key );
/* Execute the Detail level SELECT statement. */
/* */
xProgress := 'GASNOB-10-2140';
/* insert this record into the Detail interface table. */
/* */
xProgress := 'GASNOB-10-2190';
SELECT GML_GASNO_DETAILS_S.nextval
INTO v_DTL_Key
FROM sys.dual;
ece_extract_utils_pub.insert_into_interface_tbl ( p_RunID,
p_TransactionType,
p_CommunicationMethod,
p_DTL_Interface,
v_DTL_Table,
v_DTL_Key );
/* Execute the Detail Charge/Allowance level SELECT statement. */
/* */
xProgress := 'GASNOB-10-1760';
ece_extract_utils_pub.insert_into_interface_tbl ( p_RunID,
p_TransactionType,
p_CommunicationMethod,
p_DAC_Interface,
v_DAC_Table,
v_DAC_Key );
/* Execute the Detail Text level SELECT statement. */
/* */
xProgress := 'GASNOB-10-2300';
ece_extract_utils_pub.insert_into_interface_tbl ( p_RunID,
p_TransactionType,
p_CommunicationMethod,
p_DTX_Interface,
v_DTX_Table,
v_DTX_Key );
/* Execute the Allocations level SELECT statement. */
/* */
xProgress := 'GASNOB-10-2380';
ece_extract_utils_pub.insert_into_interface_tbl ( p_RunID,
p_TransactionType,
p_CommunicationMethod,
p_ALL_Interface,
v_ALL_Table,
v_ALL_Key );
/* Commit the interface table inserts. */
/* */
xProgress := 'GASNOB-10-2300';
the Gateway interface tables and inserts the sequenced
data into the Gateway output table.
===========================================================================*/
PROCEDURE Put_Data_To_Output_Table ( p_CommunicationMethod IN VARCHAR2,
p_TransactionType IN VARCHAR2,
p_Orgn_Code IN VARCHAR2,
p_BOL_No_From IN VARCHAR2,
p_BOL_No_To IN VARCHAR2,
p_Creation_Date_From IN VARCHAR2,
p_Creation_Date_To IN VARCHAR2,
p_Customer_Name IN VARCHAR2,
p_RunID IN INTEGER,
p_OutputWidth IN INTEGER,
p_SHP_Interface IN VARCHAR2,
p_STX_Interface IN VARCHAR2,
p_ORD_Interface IN VARCHAR2,
p_OAC_Interface IN VARCHAR2,
p_OTX_Interface IN VARCHAR2,
p_DTL_Interface IN VARCHAR2,
p_DAC_Interface IN VARCHAR2,
p_DTX_Interface IN VARCHAR2,
p_ALL_Interface IN VARCHAR2 )
IS
/* */
/* Variable definitions. 'Interface_tbl_type' is a PL/SQL table */
/* typedef with the following structure: */
/* */
/* table_name VARCHAR2(50) */
/* column_name VARCHAR2(50) */
/* record_num NUMBER */
/* position NUMBER */
/* data_type VARCHAR2(50) */
/* data_length NUMBER */
/* value VARCHAR2(400) */
/* layout_code VARCHAR2(2) */
/* record_qualifier VARCHAR2(3) */
/* */
xProgress VARCHAR2(30);
v_SHP_SelectCursor NUMBER;
v_STX_SelectCursor NUMBER;
v_ORD_SelectCursor NUMBER;
v_OAC_SelectCursor NUMBER;
v_OTX_SelectCursor NUMBER;
v_DTL_SelectCursor NUMBER;
v_DAC_SelectCursor NUMBER;
v_DTX_SelectCursor NUMBER;
v_ALL_SelectCursor NUMBER;
v_SHP_DeleteCursor NUMBER;
v_STX_DeleteCursor NUMBER;
v_ORD_DeleteCursor NUMBER;
v_OAC_DeleteCursor NUMBER;
v_OTX_DeleteCursor NUMBER;
v_DTL_DeleteCursor NUMBER;
v_DAC_DeleteCursor NUMBER;
v_DTX_DeleteCursor NUMBER;
v_ALL_DeleteCursor NUMBER;
v_SHP_XDeleteCursor NUMBER;
v_STX_XDeleteCursor NUMBER;
v_ORD_XDeleteCursor NUMBER;
v_OAC_XDeleteCursor NUMBER;
v_OTX_XDeleteCursor NUMBER;
v_DTL_XDeleteCursor NUMBER;
v_DAC_XDeleteCursor NUMBER;
v_DTX_XDeleteCursor NUMBER;
v_ALL_XDeleteCursor NUMBER;
v_SHP_Select VARCHAR2(32000);
v_STX_Select VARCHAR2(32000);
v_ORD_Select VARCHAR2(32000);
v_OAC_Select VARCHAR2(32000);
v_OTX_Select VARCHAR2(32000);
v_DTL_Select VARCHAR2(32000);
v_DAC_Select VARCHAR2(32000);
v_DTX_Select VARCHAR2(32000);
v_ALL_Select VARCHAR2(32000);
v_SHP_Delete VARCHAR2(32000);
v_STX_Delete VARCHAR2(32000);
v_ORD_Delete VARCHAR2(32000);
v_OAC_Delete VARCHAR2(32000);
v_OTX_Delete VARCHAR2(32000);
v_DTL_Delete VARCHAR2(32000);
v_DAC_Delete VARCHAR2(32000);
v_DTX_Delete VARCHAR2(32000);
v_ALL_Delete VARCHAR2(32000);
v_SHP_XDelete VARCHAR2(32000);
v_STX_XDelete VARCHAR2(32000);
v_ORD_XDelete VARCHAR2(32000);
v_OAC_XDelete VARCHAR2(32000);
v_OTX_XDelete VARCHAR2(32000);
v_DTL_XDelete VARCHAR2(32000);
v_DAC_XDelete VARCHAR2(32000);
v_DTX_XDelete VARCHAR2(32000);
v_ALL_XDelete 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 */
/* the SQL call. */
/* */
v_RunID := p_RunID;
ece_flatfile_pvt.select_clause ( p_TransactionType,
p_CommunicationMethod,
p_SHP_Interface,
v_SHP_XInterface,
v_SHP_Table,
v_SHP_CommonKeyName,
v_SHP_Select,
v_SHP_From,
v_SHP_Where );
ece_flatfile_pvt.select_clause ( p_TransactionType,
p_CommunicationMethod,
p_STX_Interface,
v_STX_XInterface,
v_STX_Table,
v_STX_CommonKeyName,
v_STX_Select,
v_STX_From,
v_STX_Where );
ece_flatfile_pvt.select_clause ( p_TransactionType,
p_CommunicationMethod,
p_ORD_Interface,
v_ORD_XInterface,
v_ORD_Table,
v_ORD_CommonKeyName,
v_ORD_Select,
v_ORD_From,
v_ORD_Where );
ece_flatfile_pvt.select_clause ( p_TransactionType,
p_CommunicationMethod,
p_OAC_Interface,
v_OAC_XInterface,
v_OAC_Table,
v_OAC_CommonKeyName,
v_OAC_Select,
v_OAC_From,
v_OAC_Where );
ece_flatfile_pvt.select_clause ( p_TransactionType,
p_CommunicationMethod,
p_OTX_Interface,
v_OTX_XInterface,
v_OTX_Table,
v_OTX_CommonKeyName,
v_OTX_Select,
v_OTX_From,
v_OTX_Where );
ece_flatfile_pvt.select_clause ( p_TransactionType,
p_CommunicationMethod,
p_DTL_Interface,
v_DTL_XInterface,
v_DTL_Table,
v_DTL_CommonKeyName,
v_DTL_Select,
v_DTL_From,
v_DTL_Where );
ece_flatfile_pvt.select_clause ( p_TransactionType,
p_CommunicationMethod,
p_DAC_Interface,
v_DAC_XInterface,
v_DAC_Table,
v_DAC_CommonKeyName,
v_DAC_Select,
v_DAC_From,
v_DAC_Where );
ece_flatfile_pvt.select_clause ( p_TransactionType,
p_CommunicationMethod,
p_DTX_Interface,
v_DTX_XInterface,
v_DTX_Table,
v_DTX_CommonKeyName,
v_DTX_Select,
v_DTX_From,
v_DTX_Where );
ece_flatfile_pvt.select_clause ( p_TransactionType,
p_CommunicationMethod,
p_ALL_Interface,
v_ALL_XInterface,
v_ALL_Table,
v_ALL_CommonKeyName,
v_ALL_Select,
v_ALL_From,
v_ALL_Where );
/* Customize the SELECT clauses to include the ROWID. Records */
/* will be deleted from the interface tables using these values. */
/* Also add any columns that do not appear in the flatfile, but */
/* will be needed for internal processing (i.e. ID values). */
/* */
xProgress := 'GASNOB-40-1080';
v_SHP_Select := v_SHP_Select ||
', ' ||
p_SHP_Interface ||
'.ROWID, ' ||
v_SHP_XInterface ||
'.ROWID, ' ||
p_SHP_Interface ||
'.TRANSACTION_RECORD_ID';
v_STX_Select := v_STX_Select ||
', ' ||
p_STX_Interface ||
'.ROWID, ' ||
v_STX_XInterface ||
'.ROWID, ' ||
p_STX_Interface ||
'.TRANSACTION_RECORD_ID';
v_ORD_Select := v_ORD_Select ||
', ' ||
p_ORD_Interface ||
'.ROWID, ' ||
v_ORD_XInterface ||
'.ROWID, ' ||
p_ORD_Interface ||
'.TRANSACTION_RECORD_ID';
v_OAC_Select := v_OAC_Select ||
', ' ||
p_OAC_Interface ||
'.ROWID, ' ||
v_OAC_XInterface ||
'.ROWID, ' ||
p_OAC_Interface ||
'.TRANSACTION_RECORD_ID';
v_OTX_Select := v_OTX_Select ||
', ' ||
p_OTX_Interface ||
'.ROWID, ' ||
v_OTX_XInterface ||
'.ROWID, ' ||
p_OTX_Interface ||
'.TRANSACTION_RECORD_ID';
v_DTL_Select := v_DTL_Select ||
', ' ||
p_DTL_Interface ||
'.ROWID, ' ||
v_DTL_XInterface ||
'.ROWID, ' ||
p_DTL_Interface ||
'.TRANSACTION_RECORD_ID';
v_DAC_Select := v_DAC_Select ||
', ' ||
p_DAC_Interface ||
'.ROWID, ' ||
v_DAC_XInterface ||
'.ROWID, ' ||
p_DAC_Interface ||
'.TRANSACTION_RECORD_ID';
v_DTX_Select := v_DTX_Select ||
', ' ||
p_DTX_Interface ||
'.ROWID, ' ||
v_DTX_XInterface ||
'.ROWID, ' ||
p_DTX_Interface ||
'.TRANSACTION_RECORD_ID';
v_ALL_Select := v_ALL_Select ||
', ' ||
p_ALL_Interface ||
'.ROWID, ' ||
v_ALL_XInterface ||
'.ROWID, ' ||
p_ALL_Interface ||
'.TRANSACTION_RECORD_ID';
'( SELECT DISTINCT Order_ID ' ||
' FROM ' || p_DTL_Interface ||
' WHERE ' || p_DTL_Interface || '.BOL_ID = :BOL_ID) '||
' ORDER BY ' ||
p_ORD_Interface || '.ORDER_NO';
/* Build the complete SELECT statement for each level. */
/* */
xProgress := 'GASNOB-40-1100';
v_SHP_Select := v_SHP_Select ||
v_SHP_From ||
v_SHP_Where ||
' FOR UPDATE';
ec_debug.pl ( 3, 'v_SHP_Select: ', v_SHP_Select );
v_STX_Select := v_STX_Select ||
v_STX_From ||
v_STX_Where ||
' FOR UPDATE';
ec_debug.pl ( 3, 'v_STX_Select: ', v_STX_Select );
v_ORD_Select := v_ORD_Select ||
v_ORD_From ||
v_ORD_Where ||
' FOR UPDATE';
ec_debug.pl ( 3, 'v_ORD_Select: ', v_ORD_Select );
v_OAC_Select := v_OAC_Select ||
v_OAC_From ||
v_OAC_Where ||
' FOR UPDATE';
ec_debug.pl ( 3, 'v_OAC_Select: ', v_OAC_Select );
v_OTX_Select := v_OTX_Select ||
v_OTX_From ||
v_OTX_Where ||
' FOR UPDATE';
ec_debug.pl ( 3, 'v_OTX_Select: ', v_OTX_Select );
v_DTL_Select := v_DTL_Select ||
v_DTL_From ||
v_DTL_Where ||
' FOR UPDATE';
ec_debug.pl ( 3, 'v_DTL_Select: ', v_DTL_Select );
v_DAC_Select := v_DAC_Select ||
v_DAC_From ||
v_DAC_Where ||
' FOR UPDATE';
ec_debug.pl ( 3, 'v_DAC_Select: ', v_DAC_Select );
v_DTX_Select := v_DTX_Select ||
v_DTX_From ||
v_DTX_Where ||
' FOR UPDATE';
ec_debug.pl ( 3, 'v_DTX_Select: ', v_DTX_Select );
v_ALL_Select := v_ALL_Select ||
v_ALL_From ||
v_ALL_Where ||
' FOR UPDATE';
ec_debug.pl ( 3, 'v_ALL_Select: ', v_ALL_Select );
/* Build the DELETE clauses for each interface and extension table. */
/* */
xProgress := 'GASNOB-40-1110';
v_SHP_Delete := 'DELETE FROM ' ||
p_SHP_Interface ||
' WHERE ROWID = :Row_ID';
ec_debug.pl ( 3, 'v_SHP_Delete: ', v_SHP_Delete );
v_SHP_XDelete := 'DELETE FROM ' ||
v_SHP_XInterface ||
' WHERE ROWID = :Row_ID';
ec_debug.pl ( 3, 'v_SHP_XDelete: ', v_SHP_XDelete );
v_STX_Delete := 'DELETE FROM ' ||
p_STX_Interface ||
' WHERE ROWID = :Row_ID';
ec_debug.pl ( 3, 'v_STX_Delete: ', v_STX_Delete );
v_STX_XDelete := 'DELETE FROM ' ||
v_STX_XInterface ||
' WHERE ROWID = :Row_ID';
ec_debug.pl ( 3, 'v_STX_XDelete: ', v_STX_XDelete );
v_ORD_Delete := 'DELETE FROM ' ||
p_ORD_Interface ||
' WHERE ROWID = :Row_ID';
ec_debug.pl ( 3, 'v_ORD_Delete: ', v_ORD_Delete );
v_ORD_XDelete := 'DELETE FROM ' ||
v_ORD_XInterface ||
' WHERE ROWID = :Row_ID';
ec_debug.pl ( 3, 'v_ORD_XDelete: ', v_ORD_XDelete );
v_OAC_Delete := 'DELETE FROM ' ||
p_OAC_Interface ||
' WHERE ROWID = :Row_ID';
ec_debug.pl ( 3, 'v_OAC_Delete: ', v_OAC_Delete );
v_OAC_XDelete := 'DELETE FROM ' ||
v_OAC_XInterface ||
' WHERE ROWID = :Row_ID';
ec_debug.pl ( 3, 'v_OAC_XDelete: ', v_OAC_XDelete );
v_OTX_Delete := 'DELETE FROM ' ||
p_OTX_Interface ||
' WHERE ROWID = :Row_ID';
ec_debug.pl ( 3, 'v_OTX_Delete: ', v_OTX_Delete );
v_OTX_XDelete := 'DELETE FROM ' ||
v_OTX_XInterface ||
' WHERE ROWID = :Row_ID';
ec_debug.pl ( 3, 'v_OTX_XDelete: ', v_OTX_XDelete );
v_DTL_Delete := 'DELETE FROM ' ||
p_DTL_Interface ||
' WHERE ROWID = :Row_ID';
ec_debug.pl ( 3, 'v_DTL_Delete: ', v_DTL_Delete );
v_DTL_XDelete := 'DELETE FROM ' ||
v_DTL_XInterface ||
' WHERE ROWID = :Row_ID';
ec_debug.pl ( 3, 'v_DTL_XDelete: ', v_DTL_XDelete );
v_DAC_Delete := 'DELETE FROM ' ||
p_DAC_Interface ||
' WHERE ROWID = :Row_ID';
ec_debug.pl ( 3, 'v_DAC_Delete: ', v_DAC_Delete );
v_DAC_XDelete := 'DELETE FROM ' ||
v_DAC_XInterface ||
' WHERE ROWID = :Row_ID';
ec_debug.pl ( 3, 'v_DAC_XDelete: ', v_DAC_XDelete );
v_DTX_Delete := 'DELETE FROM ' ||
p_DTX_Interface ||
' WHERE ROWID = :Row_ID';
ec_debug.pl ( 3, 'v_DTX_Delete: ', v_DTX_Delete );
v_DTX_XDelete := 'DELETE FROM ' ||
v_DTX_XInterface ||
' WHERE ROWID = :Row_ID';
ec_debug.pl ( 3, 'v_DTX_XDelete: ', v_DTX_XDelete );
v_ALL_Delete := 'DELETE FROM ' ||
p_ALL_Interface ||
' WHERE ROWID = :Row_ID';
ec_debug.pl ( 3, 'v_ALL_Delete: ', v_ALL_Delete );
v_ALL_XDelete := 'DELETE FROM ' ||
v_ALL_XInterface ||
' WHERE ROWID = :Row_ID';
ec_debug.pl ( 3, 'v_ALL_XDelete: ', v_ALL_XDelete );
/* 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 := 'GASNOB-40-1120';
v_SHP_SelectCursor := dbms_sql.open_cursor;
v_STX_SelectCursor := dbms_sql.open_cursor;
v_ORD_SelectCursor := dbms_sql.open_cursor;
v_OAC_SelectCursor := dbms_sql.open_cursor;
v_OTX_SelectCursor := dbms_sql.open_cursor;
v_DTL_SelectCursor := dbms_sql.open_cursor;
v_DAC_SelectCursor := dbms_sql.open_cursor;
v_DTX_SelectCursor := dbms_sql.open_cursor;
v_ALL_SelectCursor := dbms_sql.open_cursor;
v_SHP_DeleteCursor := dbms_sql.open_cursor;
v_STX_DeleteCursor := dbms_sql.open_cursor;
v_ORD_DeleteCursor := dbms_sql.open_cursor;
v_OAC_DeleteCursor := dbms_sql.open_cursor;
v_OTX_DeleteCursor := dbms_sql.open_cursor;
v_DTL_DeleteCursor := dbms_sql.open_cursor;
v_DAC_DeleteCursor := dbms_sql.open_cursor;
v_DTX_DeleteCursor := dbms_sql.open_cursor;
v_ALL_DeleteCursor := dbms_sql.open_cursor;
v_SHP_XDeleteCursor := dbms_sql.open_cursor;
v_STX_XDeleteCursor := dbms_sql.open_cursor;
v_ORD_XDeleteCursor := dbms_sql.open_cursor;
v_OAC_XDeleteCursor := dbms_sql.open_cursor;
v_OTX_XDeleteCursor := dbms_sql.open_cursor;
v_DTL_XDeleteCursor := dbms_sql.open_cursor;
v_DAC_XDeleteCursor := dbms_sql.open_cursor;
v_DTX_XDeleteCursor := dbms_sql.open_cursor;
v_ALL_XDeleteCursor := dbms_sql.open_cursor;
/* Parse each SELECT and DELETE statement so the database understands */
/* the command. */
/* */
xProgress := 'GASNOB-40-1170';
dbms_sql.parse ( v_SHP_SelectCursor,
v_SHP_Select,
dbms_sql.native );
v_SHP_Select) ;
dbms_sql.parse ( v_STX_SelectCursor,
v_STX_Select,
dbms_sql.native );
v_STX_Select) ;
dbms_sql.parse ( v_ORD_SelectCursor,
v_ORD_Select,
dbms_sql.native );
v_ORD_Select) ;
dbms_sql.parse ( v_OAC_SelectCursor,
v_OAC_Select,
dbms_sql.native );
v_OAC_Select) ;
dbms_sql.parse ( v_OTX_SelectCursor,
v_OTX_Select,
dbms_sql.native );
v_OTX_Select) ;
dbms_sql.parse ( v_DTL_SelectCursor,
v_DTL_Select,
dbms_sql.native );
v_DTL_Select) ;
dbms_sql.parse ( v_DAC_SelectCursor,
v_DAC_Select,
dbms_sql.native );
v_DAC_Select) ;
dbms_sql.parse ( v_DTX_SelectCursor,
v_DTX_Select,
dbms_sql.native );
v_DTX_Select) ;
dbms_sql.parse ( v_ALL_SelectCursor,
v_ALL_Select,
dbms_sql.native );
v_ALL_Select) ;
dbms_sql.parse ( v_SHP_DeleteCursor,
v_SHP_Delete,
dbms_sql.native );
v_SHP_Delete) ;
dbms_sql.parse ( v_STX_DeleteCursor,
v_STX_Delete,
dbms_sql.native );
v_STX_Delete) ;
dbms_sql.parse ( v_ORD_DeleteCursor,
v_ORD_Delete,
dbms_sql.native );
v_ORD_Delete) ;
dbms_sql.parse ( v_OAC_DeleteCursor,
v_OAC_Delete,
dbms_sql.native );
v_OAC_Delete) ;
dbms_sql.parse ( v_OTX_DeleteCursor,
v_OTX_Delete,
dbms_sql.native );
v_OTX_Delete) ;
dbms_sql.parse ( v_DTL_DeleteCursor,
v_DTL_Delete,
dbms_sql.native );
v_DTL_Delete) ;
dbms_sql.parse ( v_DAC_DeleteCursor,
v_DAC_Delete,
dbms_sql.native );
v_DAC_Delete) ;
dbms_sql.parse ( v_DTX_DeleteCursor,
v_DTX_Delete,
dbms_sql.native );
v_DTX_Delete) ;
dbms_sql.parse ( v_ALL_DeleteCursor,
v_ALL_Delete,
dbms_sql.native );
v_ALL_Delete) ;
dbms_sql.parse ( v_SHP_XDeleteCursor,
v_SHP_XDelete,
dbms_sql.native );
v_SHP_XDelete) ;
dbms_sql.parse ( v_STX_XDeleteCursor,
v_STX_XDelete,
dbms_sql.native );
v_STX_XDelete) ;
dbms_sql.parse ( v_ORD_XDeleteCursor,
v_ORD_XDelete,
dbms_sql.native );
v_ORD_XDelete) ;
dbms_sql.parse ( v_OAC_XDeleteCursor,
v_OAC_XDelete,
dbms_sql.native );
v_OAC_XDelete) ;
dbms_sql.parse ( v_OTX_XDeleteCursor,
v_OTX_XDelete,
dbms_sql.native );
v_OTX_XDelete) ;
dbms_sql.parse ( v_DTL_XDeleteCursor,
v_DTL_XDelete,
dbms_sql.native );
v_DTL_XDelete) ;
dbms_sql.parse ( v_DAC_XDeleteCursor,
v_DAC_XDelete,
dbms_sql.native );
v_DAC_XDelete) ;
dbms_sql.parse ( v_DTX_XDeleteCursor,
v_DTX_XDelete,
dbms_sql.native );
v_DTX_XDelete) ;
dbms_sql.parse ( v_ALL_XDeleteCursor,
v_ALL_XDelete,
dbms_sql.native );
v_SHP_Delete) ;
/* Define the data type for every column in each SELECT statement */
/* so the database understands how to populate it. Using the */
/* K.I.S.S. principle, every data type will be converted to */
/* VARCHAR2. */
/* */
xProgress := 'GASNOB-40-1430';
dbms_sql.define_column ( v_SHP_SelectCursor,
v_LoopCount,
v_SHP_Select,
ece_extract_utils_pub.G_MaxColWidth );
/* Define the ROWIDs for the DELETE statements. */
/* */
xProgress := 'GASNOB-40-1450';
dbms_sql.define_column_rowid ( v_SHP_SelectCursor,
v_SHP_Count + 1,
v_SHP_RowID );
dbms_sql.define_column_rowid ( v_SHP_SelectCursor,
v_SHP_Count + 2,
v_SHP_XRowID );
dbms_sql.define_column ( v_SHP_SelectCursor,
v_SHP_Count + 3,
v_TransactionRecordID );
dbms_sql.define_column ( v_STX_SelectCursor,
v_LoopCount,
v_STX_Select,
ece_extract_utils_pub.G_MaxColWidth );
/* Define the ROWID for the DELETE statements. */
/* */
xProgress := 'GASNOB-40-1510';
dbms_sql.define_column_rowid ( v_STX_SelectCursor,
v_STX_Count + 1,
v_STX_RowID );
dbms_sql.define_column_rowid ( v_STX_SelectCursor,
v_STX_Count + 2,
v_STX_XRowID );
dbms_sql.define_column ( v_STX_SelectCursor,
v_STX_Count + 3,
v_TransactionRecordID );
dbms_sql.define_column ( v_ORD_SelectCursor,
v_LoopCount,
v_ORD_Select,
ece_extract_utils_pub.G_MaxColWidth );
/* Define the ROWIDs for the DELETE statements. */
/* */
xProgress := 'GASNOB-40-1550';
dbms_sql.define_column_rowid ( v_ORD_SelectCursor,
v_ORD_Count + 1,
v_ORD_RowID );
dbms_sql.define_column_rowid ( v_ORD_SelectCursor,
v_ORD_Count + 2,
v_ORD_XRowID );
dbms_sql.define_column ( v_ORD_SelectCursor,
v_ORD_Count + 3,
v_TransactionRecordID );
dbms_sql.define_column ( v_OAC_SelectCursor,
v_LoopCount,
v_OAC_Select,
ece_extract_utils_pub.G_MaxColWidth );
/* Define the ROWIDs for the DELETE statements. */
/* */
xProgress := 'GASNOB-40-1590';
dbms_sql.define_column_rowid ( v_OAC_SelectCursor,
v_OAC_Count + 1,
v_OAC_RowID );
dbms_sql.define_column_rowid ( v_OAC_SelectCursor,
v_OAC_Count + 2,
v_OAC_XRowID );
dbms_sql.define_column ( v_OAC_SelectCursor,
v_OAC_Count + 3,
v_TransactionRecordID );
dbms_sql.define_column ( v_OTX_SelectCursor,
v_LoopCount,
v_OTX_Select,
ece_extract_utils_pub.G_MaxColWidth );
/* Define the ROWIDs for the DELETE statements. */
/* */
xProgress := 'GASNOB-40-1630';
dbms_sql.define_column_rowid ( v_OTX_SelectCursor,
v_OTX_Count + 1,
v_OTX_RowID );
dbms_sql.define_column_rowid ( v_OTX_SelectCursor,
v_OTX_Count + 2,
v_OTX_XRowID );
dbms_sql.define_column ( v_OTX_SelectCursor,
v_OTX_Count + 3,
v_TransactionRecordID );
dbms_sql.define_column ( v_DTL_SelectCursor,
v_LoopCount,
v_DTL_Select,
ece_extract_utils_pub.G_MaxColWidth );
/* Define the ROWIDs for the DELETE statements. */
/* */
xProgress := 'GASNOB-40-1680';
dbms_sql.define_column_rowid ( v_DTL_SelectCursor,
v_DTL_Count + 1,
v_DTL_RowID );
dbms_sql.define_column_rowid ( v_DTL_SelectCursor,
v_DTL_Count + 2,
v_DTL_XRowID );
dbms_sql.define_column ( v_DTL_SelectCursor,
v_DTL_Count + 3,
v_TransactionRecordID );
dbms_sql.define_column ( v_DAC_SelectCursor,
v_LoopCount,
v_DAC_Select,
ece_extract_utils_pub.G_MaxColWidth );
/* Define the ROWIDs for the DELETE statements. */
/* */
xProgress := 'GASNOB-40-1730';
dbms_sql.define_column_rowid ( v_DAC_SelectCursor,
v_DAC_Count + 1,
v_DAC_RowID );
dbms_sql.define_column_rowid ( v_DAC_SelectCursor,
v_DAC_Count + 2,
v_DAC_XRowID );
dbms_sql.define_column ( v_DAC_SelectCursor,
v_DAC_Count + 3,
v_TransactionRecordID );
dbms_sql.define_column ( v_DTX_SelectCursor,
v_LoopCount,
v_DTX_Select,
ece_extract_utils_pub.G_MaxColWidth );
/* Define the ROWIDs for the DELETE statements. */
/* */
xProgress := 'GASNOB-40-1790';
dbms_sql.define_column_rowid ( v_DTX_SelectCursor,
v_DTX_Count + 1,
v_DTX_RowID );
dbms_sql.define_column_rowid ( v_DTX_SelectCursor,
v_DTX_Count + 2,
v_DTX_XRowID );
dbms_sql.define_column ( v_DTX_SelectCursor,
v_DTX_Count + 3,
v_TransactionRecordID );
dbms_sql.define_column ( v_ALL_SelectCursor,
v_LoopCount,
v_ALL_Select,
ece_extract_utils_pub.G_MaxColWidth );
/* Define the ROWIDs for the DELETE statements. */
/* */
xProgress := 'GASNOB-40-1812';
dbms_sql.define_column_rowid ( v_ALL_SelectCursor,
v_ALL_Count + 1,
v_ALL_RowID );
dbms_sql.define_column_rowid ( v_ALL_SelectCursor,
v_ALL_Count + 2,
v_ALL_XRowID );
dbms_sql.define_column ( v_ALL_SelectCursor,
v_ALL_Count + 3,
v_TransactionRecordID );
/* Bind the variable in the SHP level SELECT clause. */
/* */
xProgress := 'GASNOB-40-1810';
dbms_sql.bind_variable ( v_SHP_SelectCursor,
'Run_ID',
p_RunID );
/* Execute the Shipment level SELECT statement. */
/* */
xProgress := 'GASNOB-40-1820';
v_Dummy := dbms_sql.execute ( v_SHP_SelectCursor );
WHILE dbms_sql.fetch_rows ( v_SHP_SelectCursor ) > 0
LOOP
/* */
/* Store the returned values in the PL/SQL table. */
/* */
xProgress := 'GASNOB-40-1840';
dbms_sql.column_value ( v_SHP_SelectCursor,
v_LoopCount,
v_SHP_Table(v_LoopCount).value );
dbms_sql.column_value ( v_SHP_SelectCursor,
v_SHP_Count + 1,
v_SHP_RowID );
dbms_sql.column_value ( v_SHP_SelectCursor,
v_SHP_Count + 2,
v_SHP_XRowID );
/* and Transaction_Record_ID) in the Shipment level SELECT clause */
/* into local variables and use the values to bind the variables */
/* in the SELECT clauses of the lower levels to come */
/* */
xProgress := 'GASNOB-40-1940';
dbms_sql.column_value ( v_SHP_SelectCursor,
v_SHP_Count + 3,
v_TransactionRecordID );
/* Execute the Shipment Text SELECT statement. */
/* */
xProgress := 'GASNOB-40-2025';
dbms_sql.bind_variable ( v_STX_SelectCursor,
'RUN_ID',
v_RunID );
dbms_sql.bind_variable ( v_STX_SelectCursor,
'BOL_ID',
v_BOL_ID );
v_Dummy := dbms_sql.execute ( v_STX_SelectCursor );
WHILE dbms_sql.fetch_rows(v_STX_SelectCursor) > 0
LOOP
FOR v_LoopCount IN 1..v_STX_Count
LOOP
xProgress := 'GASNOB-40-2060';
dbms_sql.column_value ( v_STX_SelectCursor,
v_LoopCount,
v_STX_Table(v_LoopCount).value );
dbms_sql.column_value ( v_STX_SelectCursor,
v_STX_Count + 1,
v_STX_RowID );
dbms_sql.column_value ( v_STX_SelectCursor,
v_STX_Count + 2,
v_STX_XRowID );
dbms_sql.bind_variable ( v_STX_DeleteCursor,
'Row_ID',
v_STX_RowID );
dbms_sql.bind_variable ( v_STX_XDeleteCursor,
'Row_ID',
v_STX_XRowID );
v_Dummy := dbms_sql.execute ( v_STX_DeleteCursor );
v_Dummy := dbms_sql.execute ( v_STX_XDeleteCursor );
/* Execute the Order level SELECT statement. */
/* */
xProgress := 'GASNOB-40-2095';
dbms_sql.bind_variable ( v_ORD_SelectCursor,
'RUN_ID',
v_RunID );
dbms_sql.bind_variable ( v_ORD_SelectCursor,
'BOL_ID',
v_BOL_ID );
v_Dummy := dbms_sql.execute ( v_ORD_SelectCursor );
WHILE dbms_sql.fetch_rows ( v_ORD_SelectCursor ) > 0
LOOP
xProgress := 'GASNOB-40-2120';
dbms_sql.column_value ( v_ORD_SelectCursor,
v_LoopCount,
v_ORD_Table(v_LoopCount).value );
dbms_sql.column_value ( v_ORD_SelectCursor,
v_ORD_Count + 1,
v_ORD_RowID );
dbms_sql.column_value ( v_ORD_SelectCursor,
v_ORD_Count + 2,
v_ORD_XRowID );
/* Bind the variables (ROWIDs) in the DELETE statements. */
/* */
xProgress := 'GASNOB-40-2170';
dbms_sql.bind_variable ( v_ORD_DeleteCursor,
'Row_ID',
v_ORD_RowID );
dbms_sql.bind_variable ( v_ORD_XDeleteCursor,
'Row_ID',
v_ORD_XRowID );
/* Delete the rows from the interface table. */
/* */
xProgress := 'GASNOB-40-2190';
v_Dummy := dbms_sql.execute ( v_ORD_DeleteCursor );
v_Dummy := dbms_sql.execute ( v_ORD_XDeleteCursor );
/* Execute the Order DAC SELECT statement. */
/* */
xProgress := 'GASNOB-40-2205';
dbms_sql.bind_variable ( v_OAC_SelectCursor,
'RUN_ID',
v_RunID );
dbms_sql.bind_variable ( v_OAC_SelectCursor,
'Order_ID',
v_Order_ID );
v_Dummy := dbms_sql.execute ( v_OAC_SelectCursor );
WHILE dbms_sql.fetch_rows ( v_OAC_SelectCursor ) > 0
LOOP
xProgress := 'GASNOB-40-2230';
dbms_sql.column_value ( v_OAC_SelectCursor,
v_LoopCount,
v_OAC_Table(v_LoopCount).value );
dbms_sql.column_value ( v_OAC_SelectCursor,
v_OAC_Count + 1,
v_OAC_RowID );
dbms_sql.column_value ( v_OAC_SelectCursor,
v_OAC_Count + 2,
v_OAC_XRowID );
dbms_sql.bind_variable ( v_OAC_DeleteCursor,
'Row_ID',
v_OAC_RowID );
dbms_sql.bind_variable ( v_OAC_XDeleteCursor,
'Row_ID',
v_OAC_XRowID );
v_Dummy := dbms_sql.execute ( v_OAC_DeleteCursor );
v_Dummy := dbms_sql.execute ( v_OAC_XDeleteCursor );
/* Execute the Order Text SELECT statement. */
/* */
xProgress := 'GASNOB-40-2350';
dbms_sql.bind_variable ( v_OTX_SelectCursor,
'RUN_ID',
v_RunID );
dbms_sql.bind_variable ( v_OTX_SelectCursor,
'Order_ID',
v_Order_ID );
v_Dummy := dbms_sql.execute ( v_OTX_SelectCursor );
WHILE dbms_sql.fetch_rows ( v_OTX_SelectCursor ) > 0
LOOP
xProgress := 'GASNOB-40-2380';
dbms_sql.column_value ( v_OTX_SelectCursor,
v_LoopCount,
v_OTX_Table(v_LoopCount).value );
dbms_sql.column_value ( v_OTX_SelectCursor,
v_OTX_Count + 1,
v_OTX_RowID );
dbms_sql.column_value ( v_OTX_SelectCursor,
v_OTX_Count + 2,
v_OTX_XRowID );
dbms_sql.bind_variable ( v_OTX_DeleteCursor,
'Row_ID',
v_OTX_RowID );
dbms_sql.bind_variable ( v_OTX_XDeleteCursor,
'Row_ID',
v_OTX_XRowID );
v_Dummy := dbms_sql.execute ( v_OTX_DeleteCursor );
v_Dummy := dbms_sql.execute ( v_OTX_XDeleteCursor );
/* Execute the Detail level SELECT statement. */
/* */
xProgress := 'GASNOB-40-2500';
dbms_sql.bind_variable ( v_DTL_SelectCursor,
'RUN_ID',
v_RunID );
dbms_sql.bind_variable ( v_DTL_SelectCursor,
'BOL_ID',
v_BOL_ID );
dbms_sql.bind_variable ( v_DTL_SelectCursor,
'Order_ID',
v_Order_ID );
v_Dummy := dbms_sql.execute ( v_DTL_SelectCursor );
WHILE dbms_sql.fetch_rows ( v_DTL_SelectCursor ) > 0
LOOP
xProgress := 'GASNOB-40-2530';
dbms_sql.column_value ( v_DTL_SelectCursor,
v_LoopCount,
v_DTL_Table(v_LoopCount).value);
dbms_sql.column_value ( v_DTL_SelectCursor,
v_DTL_Count + 1,
v_DTL_RowID );
dbms_sql.column_value ( v_DTL_SelectCursor,
v_DTL_Count + 2,
v_DTL_XRowID );
dbms_sql.bind_variable ( v_DTL_DeleteCursor,
'Row_ID',
v_DTL_RowID );
dbms_sql.bind_variable ( v_DTL_XDeleteCursor,
'Row_ID',
v_DTL_XRowID );
/* Delete the rows from the interface table. */
/* */
xProgress := 'GASNOB-40-2630';
v_Dummy := dbms_sql.execute ( v_DTL_DeleteCursor );
v_Dummy := dbms_sql.execute ( v_DTL_XDeleteCursor );
/* Execute the Detail DAC SELECT statement. */
/* */
xProgress := 'GASNOB-40-2650';
dbms_sql.bind_variable ( v_DAC_SelectCursor,
'RUN_ID',
v_RunID );
dbms_sql.bind_variable ( v_DAC_SelectCursor,
'Line_ID',
v_Line_ID );
v_Dummy := dbms_sql.execute ( v_DAC_SelectCursor );
WHILE dbms_sql.fetch_rows ( v_DAC_SelectCursor ) > 0
LOOP
xProgress := 'GASNOB-40-2700';
dbms_sql.column_value ( v_DAC_SelectCursor,
v_LoopCount,
v_DAC_Table(v_LoopCount).value );
dbms_sql.column_value ( v_DAC_SelectCursor,
v_DAC_Count + 1,
v_DAC_RowID );
dbms_sql.column_value ( v_DAC_SelectCursor,
v_DAC_Count + 2,
v_DAC_XRowID );
dbms_sql.bind_variable ( v_DAC_DeleteCursor,
'Row_ID',
v_DAC_RowID );
dbms_sql.bind_variable ( v_DAC_XDeleteCursor,
'Row_ID',
v_DAC_XRowID );
v_Dummy := dbms_sql.execute ( v_DAC_DeleteCursor );
v_Dummy := dbms_sql.execute ( v_DAC_XDeleteCursor );
dbms_sql.bind_variable ( v_DTX_SelectCursor,
'RUN_ID',
v_RunID );
dbms_sql.bind_variable ( v_DTX_SelectCursor,
'Line_ID',
v_Line_ID );
v_Dummy := dbms_sql.execute ( v_DTX_SelectCursor );
WHILE dbms_sql.fetch_rows ( v_DTX_SelectCursor ) > 0
LOOP
xProgress := 'GASNOB-40-2870';
dbms_sql.column_value ( v_DTX_SelectCursor,
v_LoopCount,
v_DTX_Table(v_LoopCount).value );
dbms_sql.column_value ( v_DTX_SelectCursor,
v_DTX_Count + 1,
v_DTX_RowID );
dbms_sql.column_value ( v_DTX_SelectCursor,
v_DTX_Count + 2,
v_DTX_XRowID );
dbms_sql.bind_variable ( v_DTX_DeleteCursor,
'Row_ID',
v_DTX_RowID );
dbms_sql.bind_variable ( v_DTX_XDeleteCursor,
'Row_ID',
v_DTX_XRowID );
v_Dummy := dbms_sql.execute ( v_DTX_DeleteCursor );
v_Dummy := dbms_sql.execute ( v_DTX_XDeleteCursor );
dbms_sql.bind_variable ( v_ALL_SelectCursor,
'RUN_ID',
v_RunID );
dbms_sql.bind_variable ( v_ALL_SelectCursor,
'Line_ID',
v_Line_ID );
v_Dummy := dbms_sql.execute ( v_ALL_SelectCursor );
WHILE dbms_sql.fetch_rows ( v_ALL_SelectCursor ) > 0
LOOP
xProgress := 'GASNOB-40-2993';
dbms_sql.column_value ( v_ALL_SelectCursor,
v_LoopCount,
v_ALL_Table(v_LoopCount).value );
dbms_sql.column_value ( v_ALL_SelectCursor,
v_ALL_Count + 1,
v_ALL_RowID );
dbms_sql.column_value ( v_ALL_SelectCursor,
v_ALL_Count + 2,
v_ALL_XRowID );
dbms_sql.bind_variable ( v_ALL_DeleteCursor,
'Row_ID',
v_ALL_RowID );
dbms_sql.bind_variable ( v_ALL_XDeleteCursor,
'Row_ID',
v_ALL_XRowID );
v_Dummy := dbms_sql.execute ( v_ALL_DeleteCursor );
v_Dummy := dbms_sql.execute ( v_ALL_XDeleteCursor );
dbms_sql.bind_variable ( v_SHP_DeleteCursor,
'Row_ID',
v_SHP_RowID );
dbms_sql.bind_variable ( v_SHP_XDeleteCursor,
'Row_ID',
v_SHP_XRowID );
v_Dummy := dbms_sql.execute ( v_SHP_DeleteCursor );
v_Dummy := dbms_sql.execute ( v_SHP_XDeleteCursor );
/* Commit the interface table DELETEs. */
/* */
xProgress := 'GASNOB-40-3071';
dbms_sql.close_cursor ( v_SHP_SelectCursor );
dbms_sql.close_cursor ( v_STX_SelectCursor );
dbms_sql.close_cursor ( v_ORD_SelectCursor );
dbms_sql.close_cursor ( v_OAC_SelectCursor );
dbms_sql.close_cursor ( v_OTX_SelectCursor );
dbms_sql.close_cursor ( v_DTL_SelectCursor );
dbms_sql.close_cursor ( v_DAC_SelectCursor );
dbms_sql.close_cursor ( v_DTX_SelectCursor );
dbms_sql.close_cursor ( v_ALL_SelectCursor );
dbms_sql.close_cursor ( v_SHP_DeleteCursor );
dbms_sql.close_cursor ( v_STX_DeleteCursor );
dbms_sql.close_cursor ( v_ORD_DeleteCursor );
dbms_sql.close_cursor ( v_OAC_DeleteCursor );
dbms_sql.close_cursor ( v_OTX_DeleteCursor );
dbms_sql.close_cursor ( v_DTL_DeleteCursor );
dbms_sql.close_cursor ( v_DAC_DeleteCursor );
dbms_sql.close_cursor ( v_DTX_DeleteCursor );
dbms_sql.close_cursor ( v_ALL_DeleteCursor );
dbms_sql.close_cursor ( v_SHP_XDeleteCursor );
dbms_sql.close_cursor ( v_STX_XDeleteCursor );
dbms_sql.close_cursor ( v_ORD_XDeleteCursor );
dbms_sql.close_cursor ( v_OAC_XDeleteCursor );
dbms_sql.close_cursor ( v_OTX_XDeleteCursor );
dbms_sql.close_cursor ( v_DTL_XDeleteCursor );
dbms_sql.close_cursor ( v_DAC_XDeleteCursor );
dbms_sql.close_cursor ( v_DTX_XDeleteCursor );
dbms_sql.close_cursor ( v_ALL_XDeleteCursor );