[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
FUNCTION online_finsert(p_docid IN NUMBER,
p_doctyp IN VARCHAR2,
p_docsubtyp IN VARCHAR2,
p_lineid IN NUMBER,
p_shipid IN NUMBER,
p_message IN VARCHAR2,
p_reportid IN NUMBER,
p_numtokens IN NUMBER,
p_sqlstring IN VARCHAR2,
p_sequence IN NUMBER,
p_action_date IN DATE,
p_return_code IN OUT NOCOPY VARCHAR2) RETURN BOOLEAN;
FUNCTION online_sinsert(p_docid IN NUMBER,
p_doctyp IN VARCHAR2,
p_docsubtyp IN VARCHAR2,
p_lineid IN NUMBER,
p_shipid IN NUMBER,
p_message IN VARCHAR2,
p_reportid IN NUMBER,
p_numtokens IN NUMBER,
p_sqlstring IN VARCHAR2,
p_sequence IN NUMBER,
p_action_date IN DATE,
p_return_code IN OUT NOCOPY VARCHAR2) RETURN BOOLEAN;
/* Determines how Multiple Inserts into the Online Reporting table are */
/* to be handled */
/* */
/* Multiple Inserts are handled differently for Messages with Tokens and */
/* Messages without Tokens */
/* */
/* For Messages with Tokens, Inserts are done using Array Fetch */
/* */
/* For Messages without Tokens, Inserts are done using a Subquery */
/* */
/* ----------------------------------------------------------------------- */
-- Parameters :
-- p_docid : Header ID
-- p_doctyp : Document Type
-- p_docsubtyp : Document Subtype
-- p_lineid : Line ID
-- p_shipid : Shipment ID
-- p_message : Message Name
-- p_reportid : Online Reporting ID
-- p_numtokens : Number of Tokens
-- p_sqlstring : SQL String
-- p_sequence : Sequence
-- p_action_date : Action Date
-- p_return_code : Return Code
FUNCTION insert_many(p_docid IN NUMBER,
p_doctyp IN VARCHAR2,
p_docsubtyp IN VARCHAR2,
p_lineid IN NUMBER,
p_shipid IN NUMBER,
p_message IN VARCHAR2,
p_reportid IN NUMBER,
p_numtokens IN NUMBER,
p_sqlstring IN VARCHAR2,
p_sequence IN NUMBER,
p_action_date IN DATE,
p_return_code IN OUT NOCOPY VARCHAR2) RETURN BOOLEAN IS
BEGIN
g_dbug := 'Starting Online Insert' || g_delim;
if not online_finsert(p_docid => p_docid,
p_doctyp => p_doctyp,
p_docsubtyp => p_docsubtyp,
p_lineid => p_lineid,
p_shipid => p_shipid,
p_message => p_message,
p_reportid => p_reportid,
p_numtokens => p_numtokens,
p_sqlstring => p_sqlstring,
p_sequence => p_sequence,
p_action_date => p_action_date,
p_return_code => p_return_code) then
PO_MESSAGE_S.APP_ERROR(error_name => 'PO_ALL_TRACE_ERROR',
token1 => 'FILE',
value1 => 'PO_ONLINE_REPORT',
token2 => 'ERR_NUMBER',
value2 => '005',
token3 => 'SUBROUTINE',
value3 => 'INSERT_MANY()');
if not online_sinsert(p_docid => p_docid,
p_doctyp => p_doctyp,
p_docsubtyp => p_docsubtyp,
p_lineid => p_lineid,
p_shipid => p_shipid,
p_message => p_message,
p_reportid => p_reportid,
p_numtokens => p_numtokens,
p_sqlstring => p_sqlstring,
p_sequence => p_sequence,
p_action_date => p_action_date,
p_return_code => p_return_code) then
PO_MESSAGE_S.APP_ERROR(error_name => 'PO_ALL_TRACE_ERROR',
token1 => 'FILE',
value1 => 'PO_ONLINE_REPORT',
token2 => 'ERR_NUMBER',
value2 => '010',
token3 => 'SUBROUTINE',
value3 => 'INSERT_MANY()');
END insert_many;
/* Determines how Single Inserts into the Online Reporting table are */
/* to be handled */
/* */
/* ----------------------------------------------------------------------- */
-- Parameters :
-- p_linenum : Line ID
-- p_shipnum : Shipment ID
-- p_distnum : Distribution ID
-- p_message : Message Name
-- p_reportid : Online Reporting ID
-- p_sequence : Sequence
-- p_return_code : Return Code
FUNCTION insert_single(p_linenum IN NUMBER,
p_shipnum IN NUMBER,
p_distnum IN NUMBER,
p_message IN VARCHAR2,
p_reportid IN NUMBER,
p_sequence IN NUMBER,
p_return_code IN OUT NOCOPY VARCHAR2) RETURN BOOLEAN IS
l_userid po_lines.last_updated_by%TYPE;
l_loginid po_lines.last_update_login%TYPE;
g_dbug := 'Starting Online Insert' || g_delim;
value3 => 'INSERT_SINGLE()',
token4 => 'ERROR_MSG',
value4 => 'CANNOT FIND USER ID');
value3 => 'INSERT_SINGLE()',
token4 => 'ERROR_MSG',
value4 => 'CANNOT FIND LAST LOGIN ID');
insert into po_online_report_text(online_report_id,
last_update_login,
last_updated_by,
last_update_date,
created_by,
creation_date,
line_num,
shipment_num,
distribution_num,
sequence,
text_line)
values (p_reportid,
l_loginid,
l_userid,
sysdate,
l_userid,
sysdate,
p_linenum,
p_shipnum,
p_distnum,
p_sequence,
l_textline);
'Inserted into Online Report table' || g_delim;
END insert_single;
FUNCTION online_finsert(p_docid IN NUMBER,
p_doctyp IN VARCHAR2,
p_docsubtyp IN VARCHAR2,
p_lineid IN NUMBER,
p_shipid IN NUMBER,
p_message IN VARCHAR2,
p_reportid IN NUMBER,
p_numtokens IN NUMBER,
p_sqlstring IN VARCHAR2,
p_sequence IN NUMBER,
p_action_date IN DATE,
p_return_code IN OUT NOCOPY VARCHAR2) RETURN BOOLEAN IS
l_docid po_lines.line_num%TYPE;
l_userid po_lines.last_updated_by%TYPE;
l_loginid po_lines.last_update_login%TYPE;
cur_insert INTEGER;
num_insert INTEGER;
value3 => 'ONLINE_FINSERT()',
token4 => 'ERROR_MSG',
value4 => 'CANNOT FIND USER ID');
value3 => 'ONLINE_FINSERT()',
token4 => 'ERROR_MSG',
value4 => 'CANNOT FIND LAST LOGIN ID');
cur_insert := dbms_sql.open_cursor;
dbms_sql.parse(cur_insert, p_sqlstring, dbms_sql.v7);
dbms_sql.bind_variable(cur_insert, 'docid', l_docid);
dbms_sql.define_column(cur_insert, 1, l_linenum);
dbms_sql.define_column(cur_insert, 2, l_shipnum);
dbms_sql.define_column(cur_insert, 3, l_distnum);
dbms_sql.define_column(cur_insert, 4, l_tokenval1);
dbms_sql.define_column(cur_insert, 5, l_tokenval2);
num_insert := dbms_sql.execute(cur_insert);
if dbms_sql.fetch_rows(cur_insert) > 0 then
dbms_sql.column_value(cur_insert, 1, l_linenum);
dbms_sql.column_value(cur_insert, 2, l_shipnum);
dbms_sql.column_value(cur_insert, 3, l_distnum);
dbms_sql.column_value(cur_insert, 4, l_tokenval1);
dbms_sql.column_value(cur_insert, 5, l_tokenval2);
insert into po_online_report_text
(online_report_id,
last_update_login,
last_updated_by,
last_update_date,
created_by,
creation_date,
line_num,
shipment_num,
distribution_num,
sequence,
text_line)
values (p_reportid,
l_loginid,
l_userid,
sysdate,
l_userid,
sysdate,
l_linenum,
l_shipnum,
l_distnum,
p_sequence,
l_textline);
dbms_sql.close_cursor(cur_insert);
'Inserted into Online Report table' || g_delim;
if dbms_sql.is_open(cur_insert) then
dbms_sql.close_cursor(cur_insert);
END online_finsert;
FUNCTION online_sinsert(p_docid IN NUMBER,
p_doctyp IN VARCHAR2,
p_docsubtyp IN VARCHAR2,
p_lineid IN NUMBER,
p_shipid IN NUMBER,
p_message IN VARCHAR2,
p_reportid IN NUMBER,
p_numtokens IN NUMBER,
p_sqlstring IN VARCHAR2,
p_sequence IN NUMBER,
p_action_date IN DATE,
p_return_code IN OUT NOCOPY VARCHAR2) RETURN BOOLEAN IS
l_docid po_lines.line_num%TYPE;
l_userid po_lines.last_updated_by%TYPE;
l_loginid po_lines.last_update_login%TYPE;
sql_insert VARCHAR2(1200);
cur_insert INTEGER;
num_insert INTEGER;
value3 => 'ONLINE_SINSERT()',
token4 => 'ERROR_MSG',
value4 => 'CANNOT FIND USER ID');
value3 => 'ONLINE_SINSERT()',
token4 => 'ERROR_MSG',
value4 => 'CANNOT FIND LAST LOGIN ID');
sql_insert := 'insert into po_online_report_text ' ||
'(online_report_id, ' ||
'last_update_login, ' ||
'last_updated_by, ' ||
'last_update_date, ' ||
'created_by, ' ||
'creation_date, ' ||
'line_num, ' ||
'shipment_num, ' ||
'distribution_num, ' ||
'sequence, ' ||
'text_line) ';
sql_insert := sql_insert ||
p_sqlstring;
cur_insert := dbms_sql.open_cursor;
dbms_sql.parse(cur_insert, sql_insert, dbms_sql.v7);
dbms_sql.bind_variable(cur_insert, 'online_report_id', p_reportid);
dbms_sql.bind_variable(cur_insert, 'last_update_login', l_loginid);
dbms_sql.bind_variable(cur_insert, 'last_user_id', l_userid);
dbms_sql.bind_variable(cur_insert, 'sequence', p_sequence);
dbms_sql.bind_variable(cur_insert, 'msg_text', l_textline);
dbms_sql.bind_variable(cur_insert, 'docid', l_docid);
if INSTR(sql_insert, ':line_heading', 1) > 0 then
dbms_sql.bind_variable(cur_insert, 'line_heading', l_linemsg);
if INSTR(sql_insert, ':ship_heading', 1) > 0 then
dbms_sql.bind_variable(cur_insert, 'ship_heading', l_shipmsg);
if INSTR(sql_insert, ':dist_heading', 1) > 0 then
dbms_sql.bind_variable(cur_insert, 'dist_heading', l_distmsg);
if INSTR(sql_insert, ':action_date', 1) > 0 then
dbms_sql.bind_variable(cur_insert, 'action_date', p_action_date);
num_insert := dbms_sql.execute(cur_insert);
dbms_sql.close_cursor(cur_insert);
if num_insert <> 0 then
g_dbug := g_dbug ||
'Inserted ' || num_insert || ' Records from online_sinsert' ||
g_delim;
if dbms_sql.is_open(cur_insert) then
dbms_sql.close_cursor(cur_insert);
END online_sinsert;