[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT text
FROM ece_output
WHERE run_id = v_RunID
ORDER BY line_id;
SELECT ece_output_runs_s.NEXTVAL
INTO v_RunID
FROM sys.dual;
SELECT COUNT(*)
INTO v_OutputRecordCount
FROM ece_output
WHERE run_id = v_RunID;
DELETE FROM ece_output
WHERE run_id = v_RunID;
'ECE_NO_ROW_DELETED',
'PROGRESS_LEVEL',
xProgress,
'TABLE_NAME',
'ECE_OUTPUT' );
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='OPSO'
;
SELECT
lpad(p_Order_No_From, v_format_size, v_pad_char)
INTO
v_Order_No_From
FROM
dual
;
SELECT
lpad(p_Order_No_To, v_format_size, v_pad_char)
INTO
v_Order_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 := 'GPOAOB-10-1210';
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 );
/* Build the complete SELECT statement for each level. */
/* */
xProgress := 'GPOAOB-10-1400';
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;
v_OTX_Select := v_OTX_Select ||
v_OTX_From ||
v_OTX_Where;
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;
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 := 'GPOAOB-10-1410';
/* Parse each SELECT statement so the database understands the */
/* command. */
/* */
xProgress := 'GPOAOB-10-1500';
v_ORD_Select,
dbms_sql.native );
ec_debug.pl (3, 'v_ORD_Select:', v_ORD_Select);
v_OAC_Select,
dbms_sql.native );
v_OTX_Select,
dbms_sql.native );
v_DTL_Select,
dbms_sql.native );
v_DAC_Select,
dbms_sql.native );
v_DTX_Select,
dbms_sql.native );
v_ALL_Select,
dbms_sql.native );
/* 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 := 'GPOAOB-10-1600';
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 Order level SELECT clause. */
/* */
xProgress := 'GPOAOB-10-1700';
/* Execute the Order level SELECT statement. */
/* */
xProgress := 'GPOAOB-10-1710';
/* the SELECT clauses of the other levels. */
/* */
xProgress := 'GPOAOB-10-1750';
/* insert this record into the Order interface table. */
/* */
xProgress := 'GPOAOB-10-1810';
SELECT GML_GPOAO_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 Order Charges level SELECT statement. */
/* */
xProgress := 'GPOAOB-10-1754';
/* level table, use the same key value to insert this record into */
/* the Order Charges table. */
/* */
v_OAC_Key := v_ORD_Key;
ece_extract_utils_pub.insert_into_interface_tbl ( p_RunID,
p_TransactionType,
p_CommunicationMethod,
p_OAC_Interface,
v_OAC_Table,
v_OAC_Key );
/* Execute the Order Text level SELECT statement. */
/* */
xProgress := 'GPOAOB-10-1670';
/* level table, use the same key value to insert this record into */
/* the Order Text table. */
/* */
v_OTX_Key := v_ORD_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 := 'GPOAOB-10-1980';
/* the SELECT clauses of the other levels. */
/* */
xProgress := 'GPOAOB-10-2020';
/* insert this record into the Detail interface table. */
/* */
xProgress := 'GPOAOB-10-2070';
SELECT GML_GPOAO_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 Charges level SELECT statement. */
/* */
xProgress := 'GPOAOB-10-2040';
/* level table, use the same key value to insert this record into */
/* the Order Charges table. */
/* */
v_DAC_Key := v_DTL_Key;
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 := 'GPOAOB-10-2160';
/* level table, use the same key value to insert this record into */
/* the Order Text table. */
/* */
v_DTX_Key := v_DTL_Key;
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 := 'GPOAOB-10-2160';
/* level table, use the same key value to insert this record into */
/* the Order Text table. */
/* */
v_ALL_Key := v_DTL_Key;
ece_extract_utils_pub.insert_into_interface_tbl ( p_RunID,
p_TransactionType,
p_CommunicationMethod,
p_ALL_Interface,
v_ALL_Table,
v_ALL_Key );
/* update edi count in the op_ordr_hdr table */
/* */
xProgress := 'GPOAOB-10-2160';
UPDATE
op_ordr_hdr
SET
edi_trans_count = edi_trans_count+1,
last_update_date = sysdate,
last_updated_by = fnd_global.user_id
WHERE
order_id = v_order_id;
/* 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_Order_No_From IN VARCHAR2,
p_Order_No_To IN VARCHAR2,
p_Creation_Date_From IN DATE,
p_Creation_Date_To IN DATE,
p_Customer_Name IN VARCHAR2,
p_RunID IN INTEGER,
p_OutputWidth IN INTEGER,
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_ORD_Select_Cursor INTEGER;
v_OAC_Select_Cursor INTEGER;
v_OTX_Select_Cursor INTEGER;
v_DTL_Select_Cursor INTEGER;
v_DAC_Select_Cursor INTEGER;
v_DTX_Select_Cursor INTEGER;
v_ALL_Select_Cursor INTEGER;
v_ORD_Delete_Cursor INTEGER;
v_OAC_Delete_Cursor INTEGER;
v_OTX_Delete_Cursor INTEGER;
v_DTL_Delete_Cursor INTEGER;
v_DAC_Delete_Cursor INTEGER;
v_DTX_Delete_Cursor INTEGER;
v_ALL_Delete_Cursor INTEGER;
v_ORD_Delete_XCursor INTEGER;
v_OAC_Delete_XCursor INTEGER;
v_OTX_Delete_XCursor INTEGER;
v_DTL_Delete_XCursor INTEGER;
v_DAC_Delete_XCursor INTEGER;
v_DTX_Delete_XCursor INTEGER;
v_ALL_Delete_XCursor INTEGER;
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_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_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. */
/* */
xProgress := 'GPOAOB-40-1010';
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 := 'GPOAOB-40-1080';
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';
/* Build the complete SELECT statement for each level. */
/* */
xProgress := 'GPOAOB-40-1100';
v_ORD_Select := v_ORD_Select ||
v_ORD_From ||
v_ORD_Where ||
' FOR UPDATE';
v_OAC_Select := v_OAC_Select ||
v_OAC_From ||
v_OAC_Where ||
' FOR UPDATE';
v_OTX_Select := v_OTX_Select ||
v_OTX_From ||
v_OTX_Where ||
' FOR UPDATE';
v_DTL_Select := v_DTL_Select ||
v_DTL_From ||
v_DTL_Where ||
' FOR UPDATE';
v_DAC_Select := v_DAC_Select ||
v_DAC_From ||
v_DAC_Where ||
' FOR UPDATE';
v_DTX_Select := v_DTX_Select ||
v_DTX_From ||
v_DTX_Where ||
' FOR UPDATE';
v_ALL_Select := v_ALL_Select ||
v_ALL_From ||
v_ALL_Where ||
' FOR UPDATE';
/* Build the DELETE clauses for each interface and extension table. */
/* */
xProgress := 'GPOAOB-40-1110';
v_ORD_Delete := 'DELETE FROM ' ||
p_ORD_Interface ||
' WHERE ROWID = :Row_ID';
v_OAC_Delete := 'DELETE FROM ' ||
p_OAC_Interface ||
' WHERE ROWID = :Row_ID';
v_OTX_Delete := 'DELETE FROM ' ||
p_OTX_Interface ||
' WHERE ROWID = :Row_ID';
v_DTL_Delete := 'DELETE FROM ' ||
p_DTL_Interface ||
' WHERE ROWID = :Row_ID';
v_DAC_Delete := 'DELETE FROM ' ||
p_DAC_Interface ||
' WHERE ROWID = :Row_ID';
v_DTX_Delete := 'DELETE FROM ' ||
p_DTX_Interface ||
' WHERE ROWID = :Row_ID';
v_ALL_Delete := 'DELETE FROM ' ||
p_ALL_Interface ||
' WHERE ROWID = :Row_ID';
v_ORD_XDelete := 'DELETE FROM ' ||
v_ORD_XInterface ||
' WHERE ROWID = :Row_ID';
v_OAC_XDelete := 'DELETE FROM ' ||
v_OAC_XInterface ||
' WHERE ROWID = :Row_ID';
v_OTX_XDelete := 'DELETE FROM ' ||
v_OTX_XInterface ||
' WHERE ROWID = :Row_ID';
v_DTL_XDelete := 'DELETE FROM ' ||
v_DTL_XInterface ||
' WHERE ROWID = :Row_ID';
v_DAC_XDelete := 'DELETE FROM ' ||
v_DAC_XInterface ||
' WHERE ROWID = :Row_ID';
v_DTX_XDelete := 'DELETE FROM ' ||
v_DTX_XInterface ||
' WHERE ROWID = :Row_ID';
v_ALL_XDelete := 'DELETE FROM ' ||
v_ALL_XInterface ||
' WHERE ROWID = :Row_ID';
/* 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 := 'GPOAOB-40-1120';
v_ORD_Select_Cursor := dbms_sql.open_cursor;
v_OAC_Select_Cursor := dbms_sql.open_cursor;
v_OTX_Select_Cursor := dbms_sql.open_cursor;
v_DTL_Select_Cursor := dbms_sql.open_cursor;
v_DAC_Select_Cursor := dbms_sql.open_cursor;
v_DTX_Select_Cursor := dbms_sql.open_cursor;
v_ALL_Select_Cursor := dbms_sql.open_cursor;
v_ORD_Delete_Cursor := dbms_sql.open_cursor;
v_OAC_Delete_Cursor := dbms_sql.open_cursor;
v_OTX_Delete_Cursor := dbms_sql.open_cursor;
v_DTL_Delete_Cursor := dbms_sql.open_cursor;
v_DAC_Delete_Cursor := dbms_sql.open_cursor;
v_DTX_Delete_Cursor := dbms_sql.open_cursor;
v_ALL_Delete_Cursor := dbms_sql.open_cursor;
v_ORD_Delete_XCursor := dbms_sql.open_cursor;
v_OAC_Delete_XCursor := dbms_sql.open_cursor;
v_OTX_Delete_XCursor := dbms_sql.open_cursor;
v_DTL_Delete_XCursor := dbms_sql.open_cursor;
v_DAC_Delete_XCursor := dbms_sql.open_cursor;
v_DTX_Delete_XCursor := dbms_sql.open_cursor;
v_ALL_Delete_XCursor := dbms_sql.open_cursor;
/* Parse each SELECT and DELETE statement so the database understands */
/* the command. */
/* */
xProgress := 'GPOAOB-40-1170';
dbms_sql.parse ( v_ORD_Select_Cursor,
v_ORD_Select,
dbms_sql.native );
dbms_sql.parse ( v_OAC_Select_Cursor,
v_OAC_Select,
dbms_sql.native );
dbms_sql.parse ( v_OTX_Select_Cursor,
v_OTX_Select,
dbms_sql.native );
dbms_sql.parse ( v_DTL_Select_Cursor,
v_DTL_Select,
dbms_sql.native );
dbms_sql.parse ( v_DAC_Select_Cursor,
v_DAC_Select,
dbms_sql.native );
dbms_sql.parse ( v_DTX_Select_Cursor,
v_DTX_Select,
dbms_sql.native );
dbms_sql.parse ( v_ALL_Select_Cursor,
v_ALL_Select,
dbms_sql.native );
dbms_sql.parse ( v_ORD_Delete_Cursor,
v_ORD_Delete,
dbms_sql.native );
dbms_sql.parse ( v_OAC_Delete_Cursor,
v_OAC_Delete,
dbms_sql.native );
dbms_sql.parse ( v_OTX_Delete_Cursor,
v_OTX_Delete,
dbms_sql.native );
dbms_sql.parse ( v_DTL_Delete_Cursor,
v_DTL_Delete,
dbms_sql.native );
dbms_sql.parse ( v_DAC_Delete_Cursor,
v_DAC_Delete,
dbms_sql.native );
dbms_sql.parse ( v_DTX_Delete_Cursor,
v_DTX_Delete,
dbms_sql.native );
dbms_sql.parse ( v_ALL_Delete_Cursor,
v_ALL_Delete,
dbms_sql.native );
dbms_sql.parse ( v_ORD_Delete_XCursor,
v_ORD_Delete,
dbms_sql.native );
dbms_sql.parse ( v_OAC_Delete_XCursor,
v_OAC_Delete,
dbms_sql.native );
dbms_sql.parse ( v_OTX_Delete_XCursor,
v_OTX_Delete,
dbms_sql.native );
dbms_sql.parse ( v_DTL_Delete_XCursor,
v_DTL_Delete,
dbms_sql.native );
dbms_sql.parse ( v_DAC_Delete_XCursor,
v_DAC_Delete,
dbms_sql.native );
dbms_sql.parse ( v_DTX_Delete_XCursor,
v_DTX_Delete,
dbms_sql.native );
dbms_sql.parse ( v_ALL_Delete_XCursor,
v_ALL_Delete,
dbms_sql.native );
/* 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 := 'GPOAOB-40-1500';
dbms_sql.define_column ( v_ORD_Select_Cursor,
v_LoopCount,
v_ORD_Select,
ece_extract_utils_pub.G_MaxColWidth );
/* Define the ROWIDs for the DELETE statements. */
/* */
xProgress := 'GPOAOB-40-1520';
dbms_sql.define_column_rowid ( v_ORD_Select_Cursor,
v_ORD_Count + 1,
v_ORD_RowID );
dbms_sql.define_column_rowid ( v_ORD_Select_Cursor,
v_ORD_Count + 2,
v_ORD_XRowID );
dbms_sql.define_column ( v_ORD_Select_Cursor,
v_ORD_Count + 3,
v_TransactionRecordID );
dbms_sql.define_column ( v_OAC_Select_Cursor,
v_LoopCount,
v_OAC_Select,
ece_extract_utils_pub.G_MaxColWidth );
/* Define the ROWIDs for the DELETE statements. */
/* */
xProgress := 'GPOAOB-40-1620';
dbms_sql.define_column_rowid ( v_OAC_Select_Cursor,
v_OAC_Count + 1,
v_OAC_RowID );
dbms_sql.define_column_rowid ( v_OAC_Select_Cursor,
v_OAC_Count + 2,
v_OAC_XRowID );
dbms_sql.define_column ( v_OAC_Select_Cursor,
v_OAC_Count + 3,
v_TransactionRecordID );
dbms_sql.define_column ( v_OTX_Select_Cursor,
v_LoopCount,
v_OTX_Select,
ece_extract_utils_pub.G_MaxColWidth );
/* Define the ROWIDs for the DELETE statements. */
/* */
xProgress := 'GPOAOB-40-1720';
dbms_sql.define_column_rowid ( v_OTX_Select_Cursor,
v_OTX_Count + 1,
v_OTX_RowID );
dbms_sql.define_column_rowid ( v_OTX_Select_Cursor,
v_OTX_Count + 2,
v_OTX_XRowID );
dbms_sql.define_column ( v_OTX_Select_Cursor,
v_OTX_Count + 3,
v_TransactionRecordID );
dbms_sql.define_column ( v_DTL_Select_Cursor,
v_LoopCount,
v_DTL_Select,
ece_extract_utils_pub.G_MaxColWidth );
/* Define the ROWIDs for the DELETE statements. */
/* */
xProgress := 'GPOAOB-40-1820';
dbms_sql.define_column_rowid ( v_DTL_Select_Cursor,
v_DTL_Count + 1,
v_DTL_RowID );
dbms_sql.define_column_rowid ( v_DTL_Select_Cursor,
v_DTL_Count + 2,
v_DTL_XRowID );
dbms_sql.define_column ( v_DTL_Select_Cursor,
v_DTL_Count + 3,
v_TransactionRecordID );
dbms_sql.define_column ( v_DAC_Select_Cursor,
v_LoopCount,
v_DAC_Select,
ece_extract_utils_pub.G_MaxColWidth );
/* Define the ROWIDs for the DELETE statements. */
/* */
xProgress := 'GPOAOB-40-1920';
dbms_sql.define_column_rowid ( v_DAC_Select_Cursor,
v_DAC_Count + 1,
v_DAC_RowID );
dbms_sql.define_column_rowid ( v_DAC_Select_Cursor,
v_DAC_Count + 2,
v_DAC_XRowID );
dbms_sql.define_column ( v_DAC_Select_Cursor,
v_DAC_Count + 3,
v_TransactionRecordID );
dbms_sql.define_column ( v_DTX_Select_Cursor,
v_LoopCount,
v_DTX_Select,
ece_extract_utils_pub.G_MaxColWidth );
/* Define the ROWIDs for the DELETE statements. */
/* */
xProgress := 'GPOAOB-40-2020';
dbms_sql.define_column_rowid ( v_DTX_Select_Cursor,
v_DTX_Count + 1,
v_DTX_RowID );
dbms_sql.define_column_rowid ( v_DTX_Select_Cursor,
v_DTX_Count + 2,
v_DTX_XRowID );
dbms_sql.define_column ( v_DTX_Select_Cursor,
v_DTX_Count + 3,
v_TransactionRecordID );
dbms_sql.define_column ( v_ALL_Select_Cursor,
v_LoopCount,
v_ALL_Select,
ece_extract_utils_pub.G_MaxColWidth );
/* Define the ROWIDs for the DELETE statements. */
/* */
xProgress := 'GPOAOB-40-2120';
dbms_sql.define_column_rowid ( v_ALL_Select_Cursor,
v_ALL_Count + 1,
v_ALL_RowID );
dbms_sql.define_column_rowid ( v_ALL_Select_Cursor,
v_ALL_Count + 2,
v_ALL_XRowID );
dbms_sql.define_column ( v_ALL_Select_Cursor,
v_ALL_Count + 3,
v_TransactionRecordID );
/* Bind columns needed for order select */
/* */
xProgress := 'GPOAOB-40-2170';
dbms_sql.bind_variable ( v_ORD_Select_Cursor,
'RUN_ID',
p_RunID );
/* Execute the Order level SELECT statement. */
/* */
xProgress := 'GPOAOB-40-2200';
v_Dummy := dbms_sql.execute ( v_ORD_Select_Cursor );
WHILE dbms_sql.fetch_rows ( v_ORD_Select_Cursor ) > 0
LOOP
/* */
/* Store the returned values in the PL/SQL table. */
/* */
xProgress := 'GPOAOB-40-2220';
dbms_sql.column_value ( v_ORD_Select_Cursor,
v_LoopCount,
v_ORD_Table(v_LoopCount).value );
dbms_sql.column_value ( v_ORD_Select_Cursor,
v_ORD_Count + 1,
v_ORD_RowID );
dbms_sql.column_value ( v_ORD_Select_Cursor,
v_ORD_Count + 2,
v_ORD_XRowID );
/* and Transaction_Record_ID) in the Order level SELECT clause */
/* into local variables and use the values to bind the variables */
/* in the SELECT clauses of the Order Charges, Text, Detail levels */
/* */
xProgress := 'GPOAOB-40-2320';
dbms_sql.column_value ( v_ORD_Select_Cursor,
v_ORD_Count + 3,
v_TransactionRecordID );
dbms_sql.bind_variable ( v_OAC_Select_Cursor,
'RUN_ID',
p_RunID );
dbms_sql.bind_variable ( v_OAC_Select_Cursor,
'Order_ID',
v_Order_ID );
dbms_sql.bind_variable ( v_OTX_Select_Cursor,
'RUN_ID',
p_RunID );
dbms_sql.bind_variable ( v_OTX_Select_Cursor,
'Order_ID',
v_Order_ID );
/* Execute the Order Charges SELECT statement. */
/* */
xProgress := 'GPOAOB-40-2330';
v_Dummy := dbms_sql.execute ( v_OAC_Select_Cursor );
WHILE dbms_sql.fetch_rows ( v_OAC_Select_Cursor ) > 0
LOOP
xProgress := 'GPOAOB-40-2350';
dbms_sql.column_value ( v_OAC_Select_Cursor,
v_LoopCount,
v_OAC_Table(v_LoopCount).value );
dbms_sql.column_value ( v_OAC_Select_Cursor,
v_OAC_Count + 1,
v_OAC_RowID );
dbms_sql.column_value ( v_OAC_Select_Cursor,
v_OAC_Count + 2,
v_OAC_XRowID );
/* Update Common Key */
/* */
xProgress := 'GPOAOB-40-2382';
/* Bind the variables (ROWIDs) in the DELETE statements for the */
/* OAC interface tables. */
/* */
xProgress := 'GPOAOB-40-2391';
dbms_sql.bind_variable ( v_OAC_Delete_Cursor,
'Row_ID',
v_OAC_RowID );
dbms_sql.bind_variable ( v_OAC_Delete_XCursor,
'Row_ID',
v_OAC_XRowID );
/* Delete the rows from the interface tables. */
/* */
xProgress := 'GPOAOB-40-2393';
v_Dummy := dbms_sql.execute ( v_OAC_Delete_Cursor );
v_Dummy := dbms_sql.execute ( v_OAC_Delete_XCursor );
/* Execute the Order Text SELECT statement. */
/* */
xProgress := 'GPOAOB-40-2330';
v_Dummy := dbms_sql.execute ( v_OTX_Select_Cursor );
WHILE dbms_sql.fetch_rows ( v_OTX_Select_Cursor ) > 0
LOOP
xProgress := 'GPOAOB-40-2450';
dbms_sql.column_value ( v_OTX_Select_Cursor,
v_LoopCount,
v_OTX_Table(v_LoopCount).value );
dbms_sql.column_value ( v_OTX_Select_Cursor,
v_OTX_Count + 1,
v_OTX_RowID );
dbms_sql.column_value ( v_OTX_Select_Cursor,
v_OTX_Count + 2,
v_OTX_XRowID );
/* Update Common Key */
/* */
xProgress := 'GPOAOB-40-2482';
/* Bind the variables (ROWIDs) in the DELETE statements for the */
/* OTX interface tables. */
/* */
xProgress := 'GPOAOB-40-2491';
dbms_sql.bind_variable ( v_OTX_Delete_Cursor,
'Row_ID',
v_OTX_RowID );
dbms_sql.bind_variable ( v_OTX_Delete_XCursor,
'Row_ID',
v_OTX_XRowID );
/* Delete the rows from the interface tables. */
/* */
xProgress := 'GPOAOB-40-2493';
v_Dummy := dbms_sql.execute ( v_OTX_Delete_Cursor );
v_Dummy := dbms_sql.execute ( v_OTX_Delete_XCursor );
/* Bind the variables (ROWIDs) in the DELETE statements for the */
/* Delivery and Delivery Attribute interface tables. */
/* */
/* */
/* Execute the Detail level SELECT statement. */
/* */
xProgress := 'GPOAOB-40-2500';
dbms_sql.bind_variable ( v_DTL_Select_Cursor,
'RUN_ID',
p_RunID );
dbms_sql.bind_variable ( v_DTL_Select_Cursor,
'Order_ID',
v_Order_ID );
v_Dummy := dbms_sql.execute ( v_DTL_Select_Cursor );
WHILE dbms_sql.fetch_rows ( v_DTL_Select_Cursor ) > 0
LOOP
xProgress := 'GPOAOB-40-2530';
dbms_sql.column_value ( v_DTL_Select_Cursor,
v_LoopCount,
v_DTL_Table(v_LoopCount).value);
dbms_sql.column_value ( v_DTL_Select_Cursor,
v_DTL_Count + 1,
v_DTL_RowID );
dbms_sql.column_value ( v_DTL_Select_Cursor,
v_DTL_Count + 2,
v_DTL_XRowID );
dbms_sql.bind_variable ( v_DTL_Delete_Cursor,
'Row_ID',
v_DTL_RowID );
dbms_sql.bind_variable ( v_DTL_Delete_XCursor,
'Row_ID',
v_DTL_XRowID );
/* Delete the rows from the interface table. */
/* */
xProgress := 'GPOAOB-40-2630';
v_Dummy := dbms_sql.execute ( v_DTL_Delete_Cursor );
v_Dummy := dbms_sql.execute ( v_DTL_Delete_XCursor );
/* Execute the Detail DAC SELECT statement. */
/* */
xProgress := 'GPOAOB-40-2650';
dbms_sql.bind_variable ( v_DAC_Select_Cursor,
'RUN_ID',
p_RunID );
dbms_sql.bind_variable ( v_DAC_Select_Cursor,
'Line_ID',
v_Line_ID );
v_Dummy := dbms_sql.execute ( v_DAC_Select_Cursor );
WHILE dbms_sql.fetch_rows ( v_DAC_Select_Cursor ) > 0
LOOP
xProgress := 'GPOAOB-40-2700';
dbms_sql.column_value ( v_DAC_Select_Cursor,
v_LoopCount,
v_DAC_Table(v_LoopCount).value );
dbms_sql.column_value ( v_DAC_Select_Cursor,
v_DAC_Count + 1,
v_DAC_RowID );
dbms_sql.column_value ( v_DAC_Select_Cursor,
v_DAC_Count + 2,
v_DAC_XRowID );
dbms_sql.bind_variable ( v_DAC_Delete_Cursor,
'Row_ID',
v_DAC_RowID );
dbms_sql.bind_variable ( v_DAC_Delete_XCursor,
'Row_ID',
v_DAC_XRowID );
v_Dummy := dbms_sql.execute ( v_DAC_Delete_Cursor );
v_Dummy := dbms_sql.execute ( v_DAC_Delete_XCursor );
dbms_sql.bind_variable ( v_DTX_Select_Cursor,
'RUN_ID',
p_RunID );
dbms_sql.bind_variable ( v_DTX_Select_Cursor,
'Line_ID',
v_Line_ID );
v_Dummy := dbms_sql.execute ( v_DTX_Select_Cursor );
WHILE dbms_sql.fetch_rows ( v_DTX_Select_Cursor ) > 0
LOOP
xProgress := 'GPOAOB-40-2700';
dbms_sql.column_value ( v_DTX_Select_Cursor,
v_LoopCount,
v_DTX_Table(v_LoopCount).value );
dbms_sql.column_value ( v_DTX_Select_Cursor,
v_DTX_Count + 1,
v_DTX_RowID );
dbms_sql.column_value ( v_DTX_Select_Cursor,
v_DTX_Count + 2,
v_DTX_XRowID );
dbms_sql.bind_variable ( v_DTX_Delete_Cursor,
'Row_ID',
v_DTX_RowID );
dbms_sql.bind_variable ( v_DTX_Delete_XCursor,
'Row_ID',
v_DTX_XRowID );
v_Dummy := dbms_sql.execute ( v_DTX_Delete_Cursor );
v_Dummy := dbms_sql.execute ( v_DTX_Delete_XCursor );
dbms_sql.bind_variable ( v_ALL_Select_Cursor,
'RUN_ID',
p_RunID );
dbms_sql.bind_variable ( v_ALL_Select_Cursor,
'Line_ID',
v_Line_ID );
v_Dummy := dbms_sql.execute ( v_ALL_Select_Cursor );
WHILE dbms_sql.fetch_rows ( v_ALL_Select_Cursor ) > 0
LOOP
xProgress := 'GPOAOB-40-2700';
dbms_sql.column_value ( v_ALL_Select_Cursor,
v_LoopCount,
v_ALL_Table(v_LoopCount).value );
dbms_sql.column_value ( v_ALL_Select_Cursor,
v_ALL_Count + 1,
v_ALL_RowID );
dbms_sql.column_value ( v_ALL_Select_Cursor,
v_ALL_Count + 2,
v_ALL_XRowID );
dbms_sql.bind_variable ( v_ALL_Delete_Cursor,
'Row_ID',
v_ALL_RowID );
dbms_sql.bind_variable ( v_ALL_Delete_XCursor,
'Row_ID',
v_ALL_XRowID );
v_Dummy := dbms_sql.execute ( v_ALL_Delete_Cursor );
v_Dummy := dbms_sql.execute ( v_ALL_Delete_XCursor );
dbms_sql.bind_variable ( v_ORD_Delete_Cursor,
'Row_ID',
v_ORD_RowID );
dbms_sql.bind_variable ( v_ORD_Delete_XCursor,
'Row_ID',
v_ORD_XRowID );
/* Delete the rows from the interface tables. */
/* */
xProgress := 'GPOAOB-40-3030';
v_Dummy := dbms_sql.execute ( v_ORD_Delete_Cursor );
v_Dummy := dbms_sql.execute ( v_ORD_Delete_XCursor );
/* Commit the interface table DELETEs. */
/* */
xProgress := 'GASNOB-40-3070';
dbms_sql.close_cursor ( v_ORD_Select_Cursor );
dbms_sql.close_cursor ( v_OAC_Select_Cursor );
dbms_sql.close_cursor ( v_OTX_Select_Cursor );
dbms_sql.close_cursor ( v_DTL_Select_Cursor );
dbms_sql.close_cursor ( v_DAC_Select_Cursor );
dbms_sql.close_cursor ( v_DTX_Select_Cursor );
dbms_sql.close_cursor ( v_ALL_Select_Cursor );
dbms_sql.close_cursor ( v_ORD_Delete_Cursor );
dbms_sql.close_cursor ( v_OAC_Delete_Cursor );
dbms_sql.close_cursor ( v_OTX_Delete_Cursor );
dbms_sql.close_cursor ( v_DTL_Delete_Cursor );
dbms_sql.close_cursor ( v_DAC_Delete_Cursor );
dbms_sql.close_cursor ( v_DTX_Delete_Cursor );
dbms_sql.close_cursor ( v_ALL_Delete_Cursor );
dbms_sql.close_cursor ( v_ORD_Delete_XCursor );
dbms_sql.close_cursor ( v_OAC_Delete_XCursor );
dbms_sql.close_cursor ( v_OTX_Delete_XCursor );
dbms_sql.close_cursor ( v_DTL_Delete_XCursor );
dbms_sql.close_cursor ( v_DAC_Delete_XCursor );
dbms_sql.close_cursor ( v_DTX_Delete_XCursor );
dbms_sql.close_cursor ( v_ALL_Delete_XCursor );