[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT *
FROM GL_BC_PACKETS
WHERE rowid = X_Rowid
FOR UPDATE of Packet_Id NOWAIT;
SELECT gl_bc_packets_s.NEXTVAL
FROM dual;
FUNCTION insert_je_packet(batch_id NUMBER,
lgr_id NUMBER,
mode_code VARCHAR2,
user_id NUMBER,
x_session_id NUMBER,
x_serial_id NUMBER) RETURN NUMBER IS
new_packet_id NUMBER;
insert_mode VARCHAR2(1);
insert_mode := 'P';
insert_mode := 'C';
INSERT INTO gl_bc_packets
(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, status_code,
last_update_date, last_updated_by,
budget_version_id, encumbrance_type_id,
entered_dr, entered_cr, accounted_dr, accounted_cr,
ussgl_transaction_code, je_batch_id, je_header_id, je_line_num,
application_id, session_id, serial_id)
SELECT new_packet_id, jeh.ledger_id, jeh.je_source,
jeh.je_category, jel.code_combination_id, jeb.actual_flag,
per.period_name, per.period_year, per.period_num, per.quarter_num,
jeh.currency_code, insert_mode,
sysdate, user_id,
jeh.budget_version_id, jeh.encumbrance_type_id,
jel.entered_dr, jel.entered_cr, jel.accounted_dr,jel.accounted_cr,
jel.ussgl_transaction_code, jeh.je_batch_id, jeh.je_header_id,
jel.je_line_num, 101, x_session_id, x_serial_id
FROM gl_je_batches jeb, gl_period_statuses per, gl_je_headers jeh,
gl_je_lines jel
WHERE jeb.je_batch_id = batch_id
AND per.application_id = 101
AND per.ledger_id = jeh.ledger_id
AND per.period_name = jeb.default_period_name
AND jeh.je_batch_id = jeb.je_batch_id
AND jeh.ledger_id = lgr_id
AND jel.je_header_id = jeh.je_header_id;
fnd_message.set_token('PROCEDURE', 'gl_bc_packets_pkg.insert_je_packet');
END insert_je_packet;
SELECT 'Has packet'
FROM dual
WHERE EXISTS (SELECT 'Has packet'
FROM gl_bc_packets
WHERE packet_id = xpacket_id);
SELECT ledger_id
FROM gl_bc_packets
WHERE packet_id = xpacket_id;
PROCEDURE Insert_Budget_Transfer_Row(
X_From_Rowid IN OUT NOCOPY VARCHAR2,
X_To_Rowid IN OUT NOCOPY VARCHAR2,
X_Status_Code VARCHAR2,
X_Packet_Id NUMBER,
X_Ledger_Id NUMBER,
X_Je_Source_Name VARCHAR2,
X_Je_Category_Name VARCHAR2,
X_Budget_Version_Id NUMBER,
X_Je_Batch_Name VARCHAR2,
X_Currency_Code VARCHAR2,
X_From_Code_Combination_Id NUMBER,
X_To_Code_Combination_Id NUMBER,
X_Combination_Number NUMBER,
X_Period_Name VARCHAR2,
X_Period_Year NUMBER,
X_Period_Num NUMBER,
X_Quarter_Num NUMBER,
X_From_Entered_Dr NUMBER,
X_From_Entered_Cr NUMBER,
X_To_Entered_Dr NUMBER,
X_To_Entered_Cr NUMBER,
X_Last_Update_Date DATE,
X_Last_Updated_By NUMBER,
X_Session_Id NUMBER,
X_Serial_Id NUMBER) IS
CURSOR C (ccid IN NUMBER, unique_value IN VARCHAR2) IS
SELECT rowid FROM GL_BC_PACKETS
WHERE packet_id = X_Packet_Id
AND ledger_id = X_Ledger_Id
AND reference2 = unique_value
AND code_combination_id = ccid
AND reference1 = to_char(X_Combination_Number)
AND period_name = X_Period_Name;
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,
application_id,
session_id,
serial_id,
reference1,
reference2
) VALUES (
X_Status_Code,
X_Packet_Id,
X_Ledger_Id,
X_Je_Source_Name,
X_Je_Category_Name,
X_From_Code_Combination_Id,
'B',
X_Period_Name,
X_Period_Year,
X_Period_Num,
X_Quarter_Num,
X_Currency_Code,
X_Last_Update_Date,
X_Last_Updated_By,
X_Budget_Version_Id,
X_From_Entered_Dr,
X_From_Entered_Cr,
X_From_Entered_Dr,
X_From_Entered_Cr,
X_Je_Batch_Name,
101,
X_Session_Id,
X_Serial_Id,
X_Combination_Number,
'New Budget Transfer Row');
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,
application_id,
session_id,
serial_id,
reference1,
reference2
) VALUES (
X_Status_Code,
X_Packet_Id,
X_Ledger_Id,
X_Je_Source_Name,
X_Je_Category_Name,
X_To_Code_Combination_Id,
'B',
X_Period_Name,
X_Period_Year,
X_Period_Num,
X_Quarter_Num,
X_Currency_Code,
X_Last_Update_Date,
X_Last_Updated_By,
X_Budget_Version_Id,
X_To_Entered_Dr,
X_To_Entered_Cr,
X_To_Entered_Dr,
X_To_Entered_Cr,
X_Je_Batch_Name,
101,
X_Session_Id,
X_Serial_Id,
X_Combination_Number,
X_From_RowId);
UPDATE GL_BC_PACKETS
SET reference2 = X_To_RowId
WHERE packet_id = X_Packet_Id
AND ledger_id = X_Ledger_Id
AND reference2 = 'New Budget Transfer Row'
AND code_combination_id = X_From_Code_Combination_Id
AND reference1 = to_char(X_Combination_Number)
AND period_name = X_Period_Name;
END Insert_Budget_Transfer_Row;
PROCEDURE Update_Budget_Transfer_Row(
X_From_Rowid VARCHAR2,
X_To_Rowid VARCHAR2,
X_Status_Code VARCHAR2,
X_Packet_Id NUMBER,
X_Ledger_Id NUMBER,
X_Je_Source_Name VARCHAR2,
X_Je_Category_Name VARCHAR2,
X_Budget_Version_Id NUMBER,
X_Je_Batch_Name VARCHAR2,
X_Currency_Code VARCHAR2,
X_From_Code_Combination_Id NUMBER,
X_To_Code_Combination_Id NUMBER,
X_Combination_Number NUMBER,
X_Period_Name VARCHAR2,
X_Period_Year NUMBER,
X_Period_Num NUMBER,
X_Quarter_Num NUMBER,
X_From_Entered_Dr NUMBER,
X_From_Entered_Cr NUMBER,
X_To_Entered_Dr NUMBER,
X_To_Entered_Cr NUMBER,
X_Last_Update_Date DATE,
X_Last_Updated_By NUMBER) IS
BEGIN
UPDATE GL_BC_PACKETS
SET
status_code = X_Status_Code,
packet_id = X_Packet_Id,
ledger_id = X_Ledger_Id,
je_source_name = X_Je_Source_Name,
je_category_name = X_Je_Category_Name,
code_combination_id = decode(rowid,
X_From_Rowid, X_From_Code_Combination_Id,
X_To_Rowid, X_To_Code_Combination_Id),
actual_flag = 'B',
period_name = X_Period_Name,
period_year = X_Period_Year,
period_num = X_Period_Num,
quarter_num = X_Quarter_Num,
currency_code = X_Currency_Code,
last_update_date = X_Last_Update_Date,
last_updated_by = X_Last_Updated_By,
budget_version_id = X_Budget_Version_Id,
entered_dr = decode(rowid,
X_From_Rowid, X_From_Entered_Dr,
X_To_Rowid, X_To_Entered_Dr),
entered_cr = decode(rowid,
X_From_Rowid, X_From_Entered_Cr,
X_To_Rowid, X_To_Entered_Cr),
accounted_dr = decode(rowid,
X_From_Rowid, X_From_Entered_Dr,
X_To_Rowid, X_To_Entered_Dr),
accounted_cr = decode(rowid,
X_From_Rowid, X_From_Entered_Cr,
X_To_Rowid, X_To_Entered_Cr),
je_batch_name = X_Je_Batch_Name,
reference1 = X_Combination_Number
WHERE rowid IN (X_From_Rowid, X_To_RowId);
END Update_Budget_Transfer_Row;
PROCEDURE Delete_Budget_Transfer_Row(X_From_Rowid VARCHAR2,
X_To_Rowid VARCHAR2) IS
BEGIN
DELETE FROM GL_BC_PACKETS
WHERE rowid IN (X_From_Rowid, X_To_Rowid);
END Delete_Budget_Transfer_Row;
PROCEDURE Delete_Packet(Packet_Id NUMBER,
Reference1 NUMBER DEFAULT NULL) IS
BEGIN
DELETE gl_bc_packets
WHERE packet_id = Delete_Packet.packet_id
AND status_code IN ('P', 'C')
AND nvl(reference1,'XZYXZ')
= nvl(Delete_Packet.reference1, nvl(reference1, 'XZYXZ'));
END Delete_Packet;
insert_mode VARCHAR2(1);
insert_mode := 'P';
insert_mode := 'C';
INSERT INTO gl_bc_packets
(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, status_code,
last_update_date, last_updated_by, budget_version_id,
entered_dr, entered_cr, accounted_dr, accounted_cr,
ussgl_transaction_code, je_batch_name,
application_id, session_id, serial_id)
SELECT new_packet_id, bc.ledger_id, bc.je_source_name,
bc.je_category_name, bc.code_combination_id, bc.actual_flag,
bc.period_name, bc.period_year, bc.period_num, bc.quarter_num,
bc.currency_code, insert_mode,
sysdate, user_id, bc.budget_version_id,
bc.entered_dr, bc.entered_cr, bc.accounted_dr, bc.accounted_cr,
bc.ussgl_transaction_code, bc.je_batch_name,
101, x_session_id, x_serial_id
FROM gl_bc_packets bc
WHERE bc.packet_id = copy_packet.packet_id;
DELETE FROM PSA_BC_REPORT_EVENTS_GT;
INSERT INTO PSA_BC_REPORT_EVENTS_GT(packet_id) VALUES (packet_id);
SELECT PSA_BC_XML_REPORT_S.nextval
INTO seq_id
FROM dual;