[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
/* ================== INSERT_ROW ========================= */
PROCEDURE insert_row( P_packet_id NUMBER,
P_sob_id NUMBER,
P_code_combination_id NUMBER,
P_period_name VARCHAR2,
P_period_year number,
P_period_num number,
P_quarter_num number,
P_currency_code varchar2,
P_user_id varchar2,
P_source_amount number,
P_dossier_id number,
P_trx_id number,
P_encumbrance_type_id number,
P_budget_version_id number,
P_source_trx_id number ) AS
PRAGMA AUTONOMOUS_TRANSACTION;
SELECT *
FROM igi_dos_trx_dest
WHERE trx_id = P_trx_id
AND source_trx_id = p_source_trx_id;
select s.audsid, s.serial# into l_session_id, l_serial_id
from v$session s, v$process p
where s.paddr = p.addr
and s.audsid = USERENV('SESSIONID');
INSERT INTO gl_bc_packets (status_code,
packet_id,
ledger_id, je_source_name,
je_category_name,
code_combination_id,
actual_flag,
period_name,
period_year,
period_num,
quarter_num,
currency_code,
last_update_date,
last_updated_by,
budget_version_id,
entered_dr,
entered_cr,
accounted_dr,
accounted_cr,
je_batch_name,
reference1,
reference2,
encumbrance_type_id,
session_id,serial_id,application_id )
VALUES
('P',
P_packet_id,
P_sob_id,
'Transfer',
'Budget',
P_code_combination_id,
'E',
P_period_name,
P_period_year,
P_period_num,
P_quarter_num,
P_currency_code,
sysdate,
P_user_id,
null,
null,
P_source_amount,
null,
P_source_amount,
'Budget '||P_dossier_id,
P_dossier_id || '..' || /* Bug 3466463 */
P_trx_id, /* packet_rec.trx_id, */
P_source_trx_id,
P_encumbrance_type_id,
l_session_id,l_serial_id,101);
' INSERT INTO gl_bc_packets --> ' || SQL%ROWCOUNT);
INSERT INTO gl_bc_packets
(status_code,
packet_id,
-- Set_of_books_id, /* Commented for bug 6126275 */
ledger_id, /* Added for bug 6126275 */
je_source_name,
je_category_name,
code_combination_id,
actual_flag,
period_name,
period_year,
period_num,
quarter_num,
currency_code,
last_update_date,
last_updated_by,
budget_version_id,
entered_dr,
entered_cr,
accounted_dr,
accounted_cr,
je_batch_name,
reference1,
reference2,
session_id,serial_id,application_id )
VALUES
('P',
P_packet_id,
P_sob_id,
'Transfer',
'Budget',
P_code_combination_id,
'B',
P_period_name,
P_period_year,
P_period_num,
P_quarter_num,
P_currency_code,
sysdate,
P_user_id,
P_budget_version_id,
null,
P_source_amount,
null,
P_source_amount,
'Budget '||P_dossier_id,
P_dossier_id || '..' || /* Bug 3466463 */
P_trx_id, /* packet_rec.trx_id, */
P_source_trx_id,
l_session_id,l_serial_id,101);
' INSERTED --> ' || SQL%ROWCOUNT);
' INSERTING INTO gl_bc_packets for budget');
' INSERTING INTO gl_bc_packets for budget');
INSERT INTO gl_bc_packets
(status_code,
packet_id,
ledger_id, /* Added for bug 6126275 */
je_source_name,
je_category_name,
code_combination_id,
actual_flag,
period_name,
period_year,
period_num,
quarter_num,
currency_code,
last_update_date,
last_updated_by,
budget_version_id,
entered_dr,
entered_cr,
accounted_dr,
accounted_cr,
je_batch_name,
reference1,
reference2,
session_id,serial_id,application_id )
VALUES ('P',
P_packet_id,
dest_rec.sob_id,
'Transfer',
'Budget',
dest_rec.code_combination_id,
'B',
dest_rec.period_name,
dest_rec.period_year,
dest_rec.period_num,
dest_rec.quarter_num,
P_currency_code,
sysdate,
P_user_id,
dest_rec.budget_version_id,
dest_rec.budget_amount,
null,
dest_rec.budget_amount,
null,
'Dossier ' ||ltrim(to_char(dest_rec.dossier_id))|| ' ' || dest_rec.period_name,
to_char(dest_rec.dossier_id) || '..' || dest_rec.trx_id,
dest_rec.dest_trx_id,
l_session_id,l_serial_id,101);
' INSERTED --> ' || SQL%ROWCOUNT);
END insert_row;
/* =================== REJECT_INSERT_ROW ======================= */
PROCEDURE reject_insert_row ( P_packet_id NUMBER,
P_sob_id NUMBER,
P_code_combination_id NUMBER,
P_period_name VARCHAR2,
P_period_year number,
P_period_num number,
P_quarter_num number,
P_currency_code varchar2,
P_user_id number,
P_source_amount number,
P_dossier_id number,
P_trx_id number,
P_encumbrance_type_id number,
P_source_trx_id number ) AS
PRAGMA AUTONOMOUS_TRANSACTION;
select s.audsid, s.serial# into l_session_id, l_serial_id
from v$session s, v$process p
where s.paddr = p.addr
and s.audsid = USERENV('SESSIONID');
INSERT INTO gl_bc_packets (status_code,
packet_id,
ledger_id,
je_source_name,
je_category_name,
code_combination_id,
actual_flag,
period_name,
period_year,
period_num,
quarter_num,
currency_code,
last_update_date,
last_updated_by,
budget_version_id,
entered_dr,
entered_cr,
accounted_dr,
accounted_cr,
je_batch_name,
reference1,
reference2,
encumbrance_type_id,
session_id,serial_id,application_id)
VALUES ('P',
P_packet_id,
P_sob_id,
'Transfer',
'Budget',
P_code_combination_id,
'E',
P_period_name,
P_period_year,
P_period_num,
P_quarter_num,
P_currency_code,
sysdate,
P_user_id,
null,
null,
P_source_amount,
null,
P_source_amount,
'Budget '||P_dossier_id,
P_dossier_id || '..' || P_trx_id, P_source_trx_id,
P_encumbrance_type_id,
l_session_id,l_serial_id,101);
' INSERT INTO gl_bc_packets --> ' || SQL%ROWCOUNT);
END reject_insert_row;
SELECT trx_id
FROM igi_dos_trx_headers
WHERE trx_number = P_TRX_NUMBER;
SELECT *
FROM igi_dos_trx_sources
WHERE trx_id = l_trx_id;
SELECT *
FROM igi_dos_trx_dest
WHERE trx_id = l_trx_id
AND source_trx_id = p_source_trx_id;
SELECT currency_code
FROM gl_sets_of_books
WHERE set_of_books_id = l_sob_id;
' CALLING GL_JE_SOURCES_PKG.select_columns ');
GL_JE_SOURCES_PKG.select_columns( X_JE_SOURCE_NAME => temp,
X_USER_JE_SOURCE_NAME => user_transfer,
X_EFFECTIVE_DATE_RULE_CODE => effective_date_rule_code,
X_FROZEN_SOURCE_FLAG => frozen_source_flag,
X_JOURNAL_APPROVAL_FLAG => approval_flag);
' AFTER GL_JE_SOURCES_PKG.select_columns ');
' CALLING GL_JE_CATEGORIES_PKG.select_columns ');
GL_JE_CATEGORIES_PKG.select_columns ( X_JE_CATEGORY_NAME => temp,
X_USER_JE_CATEGORY_NAME => user_budget);
' AFTER GL_JE_CATEGORIES_PKG.select_columns ');
SELECT encumbrance_type_id
INTO v_encumbrance_type_id
FROM gl_encumbrance_types
WHERE upper(encumbrance_type) = 'DOSSIER' ;
SELECT gl_bc_packets_s.nextval
INTO v_packet_id
FROM dual;
SELECT nvl(sum(dest.budget_amount),0)
INTO v_source_amount
FROM igi_dos_trx_dest dest
WHERE dest.source_trx_id = packet_rec.source_trx_id;
' INSERTING INTO gl_bc_packets for source enc ');
/* INSERT INTO gl_bc_packets (status_code,
packet_id,
-- Set_of_books_id, -- Commented for bug 6126275
ledger_id, -- Added for bug 6126275
je_source_name,
je_category_name,
code_combination_id,
actual_flag,
period_name,
period_year,
period_num,
quarter_num,
currency_code,
last_update_date,
last_updated_by,
budget_version_id,
entered_dr,
entered_cr,
accounted_dr,
accounted_cr,
je_batch_name,
reference1,
reference2,
encumbrance_type_id )
VALUES
('P',
v_packet_id,
packet_rec.sob_id,
'Transfer',
'Budget',
packet_rec.code_combination_id,
'E',
packet_rec.period_name,
packet_rec.period_year,
packet_rec.period_num,
packet_rec.quarter_num,
l_currency_code,
sysdate,
l_user_id,
null,
null,
v_source_amount,
null,
v_source_amount,
'Budget '||packet_rec.dossier_id,
packet_rec.dossier_id || '..' || -- Bug 3466463
packet_rec.trx_id, -- packet_rec.trx_id,
packet_rec.source_trx_id,
v_encumbrance_type_id);
' INSERT INTO gl_bc_packets --> ' || SQL%ROWCOUNT);
INSERT INTO gl_bc_packets
(status_code,
packet_id,
-- Set_of_books_id, -- Commented for bug 6126275
ledger_id, -- Added for bug 6126275
je_source_name,
je_category_name,
code_combination_id,
actual_flag,
period_name,
period_year,
period_num,
quarter_num,
currency_code,
last_update_date,
last_updated_by,
budget_version_id,
entered_dr,
entered_cr,
accounted_dr,
accounted_cr,
je_batch_name,
reference1,
reference2 )
VALUES
('P',
v_packet_id,
packet_rec.sob_id,
'Transfer',
'Budget',
packet_rec.code_combination_id,
'B',
packet_rec.period_name,
packet_rec.period_year,
packet_rec.period_num,
packet_rec.quarter_num,
l_currency_code,
sysdate,
l_user_id,
packet_rec.budget_version_id,
null,
v_source_amount,
null,
v_source_amount,
'Budget '||packet_rec.dossier_id,
packet_rec.dossier_id || '..' || -- Bug 3466463
packet_rec.trx_id, -- packet_rec.trx_id,
packet_rec.source_trx_id);
' INSERTED --> ' || SQL%ROWCOUNT);
' INSERTING INTO gl_bc_packets for budget');
' INSERTING INTO gl_bc_packets for budget');
INSERT INTO gl_bc_packets
(status_code,
packet_id,
-- Set_of_books_id, -- Commented for bug 6126275
ledger_id, -- Added for bug 6126275
je_source_name,
je_category_name,
code_combination_id,
actual_flag,
period_name,
period_year,
period_num,
quarter_num,
currency_code,
last_update_date,
last_updated_by,
budget_version_id,
entered_dr,
entered_cr,
accounted_dr,
accounted_cr,
je_batch_name,
reference1,
reference2 )
VALUES ('P',
v_packet_id,
dest_rec.sob_id,
'Transfer',
'Budget',
dest_rec.code_combination_id,
'B',
dest_rec.period_name,
dest_rec.period_year,
dest_rec.period_num,
dest_rec.quarter_num,
l_currency_code,
sysdate,
l_user_id,
dest_rec.budget_version_id,
dest_rec.budget_amount,
null,
dest_rec.budget_amount,
null,
'Dossier ' ||ltrim(to_char(dest_rec.dossier_id))
|| ' ' || dest_rec.period_name,
to_char(dest_rec.dossier_id) || '..' || -- Bug 3466463
dest_rec.trx_id, -- ltrim(to_char(dest_rec.dossier_id)),
dest_rec.dest_trx_id); -- 'New Budget Tranfer Row' );
' INSERTED --> ' || SQL%ROWCOUNT);
insert_row (v_packet_id,
packet_rec.sob_id,
packet_rec.code_combination_id,
packet_rec.period_name,
packet_rec.period_year,
packet_rec.period_num,
packet_rec.quarter_num,
l_currency_code,
l_user_id,
v_source_amount,
packet_rec.dossier_id,
packet_rec.trx_id,
v_encumbrance_type_id,
packet_rec.budget_version_id,
packet_rec.source_trx_id );
SELECT *
FROM igi_dos_trx_dest
WHERE trx_id = l_trx_id
AND source_trx_id = p_source_trx_id;
SELECT trx_id
FROM igi_dos_trx_headers
WHERE trx_number = p_trx_NUMBER;
SELECT *
FROM igi_dos_trx_sources
WHERE trx_id = l_trx_id;
SELECT currency_code
FROM gl_sets_of_books
WHERE set_of_books_id = l_sob_id;
' CALLING GL_JE_SOURCES_PKG.select_columns ');
GL_JE_SOURCES_PKG.select_columns( X_JE_SOURCE_NAME => temp,
X_USER_JE_SOURCE_NAME => user_transfer,
X_EFFECTIVE_DATE_RULE_CODE => effective_date_rule_code,
X_FROZEN_SOURCE_FLAG => frozen_source_flag,
X_JOURNAL_APPROVAL_FLAG => approval_flag);
' AFTER GL_JE_SOURCES_PKG.select_columns ');
' CALLING GL_JE_CATEGORIES_PKG.select_columns ');
GL_JE_CATEGORIES_PKG.select_columns ( X_JE_CATEGORY_NAME => temp,
X_USER_JE_CATEGORY_NAME => user_budget);
' AFTER GL_JE_CATEGORIES_PKG.select_columns ');
SELECT encumbrance_type_id
INTO v_encumbrance_type_id
FROM gl_encumbrance_types
WHERE upper(encumbrance_type) = 'DOSSIER' ;
SELECT gl_bc_packets_s.nextval
INTO v_packet_id
FROM dual;
SELECT nvl(sum(dest.budget_amount),0)
INTO v_source_amount
FROM igi_dos_trx_dest dest
WHERE dest.source_trx_id = packet_rec.source_trx_id;
' INSERTING INTO gl_bc_packets ');
INSERT INTO gl_bc_packets (status_code,
packet_id,
-- Set_of_books_id, -- Commented for bug 6126275
ledger_id, --Added for bug 6126275
je_source_name,
je_category_name,
code_combination_id,
actual_flag,
period_name,
period_year,
period_num,
quarter_num,
currency_code,
last_update_date,
last_updated_by,
budget_version_id,
entered_dr,
entered_cr,
accounted_dr,
accounted_cr,
je_batch_name,
reference1,
reference2,
encumbrance_type_id)
VALUES
('P',
v_packet_id,
packet_rec.sob_id,
'Transfer',
'Budget',
packet_rec.code_combination_id,
'E',
packet_rec.period_name,
packet_rec.period_year,
packet_rec.period_num,
packet_rec.quarter_num,
l_currency_code,
sysdate,
v_user_id,
null,
null,
v_source_amount,
null,
v_source_amount,
'Budget '||packet_rec.dossier_id,
packet_rec.dossier_id || '..' || -- Bug 3466463
packet_rec.trx_id, -- packet_rec.trx_id,
packet_rec.source_trx_id,
v_encumbrance_type_id);
' INSERT INTO gl_bc_packets --> ' || SQL%ROWCOUNT);
reject_insert_row ( v_packet_id,
packet_rec.sob_id,
packet_rec.code_combination_id,
packet_rec.period_name,
packet_rec.period_year,
packet_rec.period_num,
packet_rec.quarter_num,
l_currency_code,
v_user_id,
v_source_amount,
packet_rec.dossier_id,
packet_rec.trx_id,
v_encumbrance_type_id,
packet_rec.source_trx_id);