[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT 'Duplicate'
FROM GL_JE_BATCHES jeb
WHERE jeb.name = batch_name
AND jeb.default_period_name = period_name
AND jeb.chart_of_accounts_id = coa_id
AND jeb.period_set_name = cal_name
AND jeb.accounted_period_type = per_type
AND ( row_id is null
OR jeb.rowid <> row_id);
SELECT gl_je_batches_s.NEXTVAL
FROM dual;
SELECT 'Has Lines'
FROM dual
WHERE EXISTS (SELECT 'Found Line'
FROM gl_je_headers jeh, gl_je_lines jel
WHERE jeh.je_batch_id = batch_id
AND jel.je_header_id = jeh.je_header_id);
SELECT 'Needs Approval'
FROM dual
WHERE EXISTS (SELECT 'Needs Approval'
FROM gl_je_headers jeh, gl_ledgers lgr,
gl_je_sources src
WHERE jeh.je_batch_id = batch_id
AND lgr.ledger_id = jeh.ledger_id
AND lgr.enable_je_approval_flag = 'Y'
AND src.je_source_name = jeh.je_source
AND src.journal_approval_flag = 'Y');
SELECT 'Needs Tax'
FROM dual
WHERE EXISTS (SELECT 'Needs Tax'
FROM gl_je_headers jeh, gl_ledgers lgr
WHERE jeh.je_batch_id = batch_id
AND jeh.tax_status_code = 'R'
AND lgr.ledger_id = jeh.ledger_id
AND lgr.enable_automatic_tax_flag = 'Y');
SELECT
decode(count(*),
sum(decode(JH.currency_code, 'STAT', 1, 0)), 'All STAT',
'Not all STAT')
FROM
GL_JE_HEADERS JH
WHERE
JH.je_batch_id = X_je_batch_id
AND (JH.display_alc_journal_flag is null or JH.display_alc_journal_flag = 'Y');
SELECT
DISTINCT JH.ledger_id
FROM
GL_JE_HEADERS JH, GL_LEDGERS LGR
WHERE
JH.je_batch_id = X_je_batch_id
AND (JH.display_alc_journal_flag is null
or JH.display_alc_journal_flag = 'Y')
AND LGR.ledger_id = JH.ledger_id
AND LGR.ledger_category_code IN ('PRIMARY', 'SECONDARY')
AND LGR.enable_budgetary_control_flag = 'Y';
SELECT max(ledger_id), nvl(max(je_source), 'Manual'),
nvl(max(decode(reversed_je_header_id, NULL, NULL, 'Y')),'N')
INTO one_of_ledgers_in_batch, x_je_source_name, reversal_flag
FROM gl_je_headers
WHERE je_batch_id = x_je_batch_id
AND rownum = 1;
SELECT override_edits_flag
INTO frozen_source_flag
FROM gl_je_sources
WHERE je_source_name = x_je_source_name;
PROCEDURE Insert_Row(X_Rowid IN OUT NOCOPY VARCHAR2,
X_Je_Batch_Id IN OUT NOCOPY NUMBER,
X_Name VARCHAR2,
X_Chart_of_Accounts_ID NUMBER,
X_Period_Set_Name VARCHAR2,
X_Accounted_Period_Type VARCHAR2,
X_Status VARCHAR2,
X_Budgetary_Control_Status VARCHAR2,
X_Approval_Status_Code VARCHAR2,
X_Status_Verified VARCHAR2,
X_Actual_Flag VARCHAR2,
X_Default_Period_Name VARCHAR2,
X_Default_Effective_Date DATE,
X_Posted_Date DATE,
X_Date_Created DATE,
X_Control_Total IN OUT NOCOPY NUMBER,
X_Running_Total_Dr IN OUT NOCOPY NUMBER,
X_Running_Total_Cr IN OUT NOCOPY NUMBER,
X_Running_Total_Accounted_Dr NUMBER,
X_Running_Total_Accounted_Cr NUMBER,
X_Average_Journal_Flag VARCHAR2,
X_Org_Id NUMBER,
X_Posting_Run_Id NUMBER,
X_Request_Id NUMBER,
X_Packet_Id NUMBER,
X_Unreservation_Packet_Id NUMBER,
X_Creation_Date DATE,
X_Created_By NUMBER,
X_Last_Update_Date DATE,
X_Last_Updated_By NUMBER,
X_Last_Update_Login NUMBER
) IS
CURSOR C IS SELECT rowid FROM GL_JE_BATCHES
WHERE je_batch_id = X_Je_Batch_Id;
SELECT 'Y'
INTO has_je
FROM gl_je_headers
WHERE je_batch_id = X_Je_Batch_Id
AND rownum = 1;
INSERT INTO GL_JE_BATCHES(
je_batch_id,
name,
chart_of_accounts_id,
period_set_name,
accounted_period_type,
status,
budgetary_control_status,
approval_status_code,
status_verified,
actual_flag,
default_period_name,
default_effective_date,
posted_date,
date_created,
posting_run_id,
request_id,
packet_id,
unreservation_packet_id,
running_total_dr,
running_total_cr,
running_total_accounted_dr,
running_total_accounted_cr,
average_journal_flag,
org_id,
creation_date,
created_by,
last_update_date,
last_updated_by,
last_update_login
) VALUES (
X_Je_Batch_Id,
X_Name,
X_chart_of_accounts_id,
X_period_set_name,
X_accounted_period_type,
X_Status,
X_Budgetary_Control_Status,
X_Approval_Status_Code,
X_Status_Verified,
X_Actual_Flag,
X_Default_Period_Name,
X_Default_Effective_Date,
X_Posted_Date,
X_Date_Created,
X_Posting_Run_Id,
X_Request_Id,
X_Packet_Id,
X_Unreservation_Packet_Id,
X_Running_Total_Dr,
X_Running_Total_Cr,
X_Running_Total_Accounted_Dr,
X_Running_Total_Accounted_Cr,
X_Average_Journal_Flag,
X_Org_Id,
X_Creation_Date,
X_Created_By,
X_Last_Update_Date,
X_Last_Updated_By,
X_Last_Update_Login
);
END Insert_Row;
SELECT *
FROM GL_JE_BATCHES
WHERE rowid = X_Rowid
FOR UPDATE of Je_Batch_Id NOWAIT;
FND_MESSAGE.SET_NAME('FND', 'FORM_RECORD_DELETED');
PROCEDURE Update_Row(X_Rowid VARCHAR2,
X_Je_Batch_Id NUMBER,
X_Name VARCHAR2,
X_Chart_of_Accounts_ID NUMBER,
X_Period_Set_Name VARCHAR2,
X_Accounted_Period_Type VARCHAR2,
X_Status VARCHAR2,
X_Budgetary_Control_Status VARCHAR2,
X_Approval_Status_Code IN OUT NOCOPY VARCHAR2,
X_Status_Verified VARCHAR2,
X_Actual_Flag VARCHAR2,
X_Default_Period_Name VARCHAR2,
X_Default_Effective_Date DATE,
X_Posted_Date DATE,
X_Date_Created DATE,
X_Control_Total IN OUT NOCOPY NUMBER,
X_Running_Total_Dr IN OUT NOCOPY NUMBER,
X_Running_Total_Cr IN OUT NOCOPY NUMBER,
X_Average_Journal_Flag VARCHAR2,
X_Posting_Run_Id NUMBER,
X_Request_Id NUMBER,
X_Packet_Id NUMBER,
X_Unreservation_Packet_Id NUMBER,
X_Last_Update_Date DATE,
X_Last_Updated_By NUMBER,
X_Last_Update_Login NUMBER,
Update_Effective_Date_Flag VARCHAR2,
Update_Approval_Stat_Flag VARCHAR2
) IS
X_Running_Total_Accounted_Dr NUMBER;
SELECT 'Y'
INTO has_je
FROM gl_je_headers
WHERE je_batch_id = X_Je_Batch_Id
AND rownum = 1;
IF (Update_Effective_Date_Flag = 'Y') THEN
GL_JE_HEADERS_PKG.change_effective_date(X_Je_Batch_Id,
X_Default_Effective_Date);
IF (Update_Approval_Stat_Flag = 'Y') THEN
SELECT approval_status_code
INTO X_Approval_Status_Code
FROM gl_je_batches
WHERE rowid = X_RowId;
ELSIF (Update_Approval_Stat_Flag = 'D') THEN
-- If a journal was deleted, the batch shouldn't have been
-- posted or approved, but check anyway.
IF ( (X_status <> 'P')
AND (X_Approval_Status_Code <> 'A')
) THEN
IF (gl_je_batches_pkg.needs_approval(X_Je_Batch_Id)) THEN
IF (X_Approval_Status_Code = 'Z') THEN
X_Approval_Status_Code := 'R';
UPDATE GL_JE_BATCHES
SET
je_batch_id = X_Je_Batch_Id,
name = X_Name,
chart_of_accounts_id = X_Chart_of_Accounts_id,
period_set_name = X_Period_Set_Name,
accounted_period_type = X_Accounted_Period_Type,
status = X_Status,
budgetary_control_status = X_Budgetary_Control_Status,
approval_status_code = X_Approval_Status_Code,
status_verified = decode(X_Status_Verified,
'Y', status_verified,
X_Status_Verified),
actual_flag = X_Actual_Flag,
default_period_name = X_Default_Period_Name,
default_effective_date = X_Default_Effective_Date,
date_created = X_Date_Created,
posting_run_id = X_Posting_Run_Id,
request_id = X_Request_Id,
packet_id = X_Packet_Id,
unreservation_packet_id = X_Unreservation_Packet_Id,
control_total = X_Control_Total,
running_total_dr = X_Running_Total_Dr,
running_total_cr = X_Running_Total_Cr,
running_total_accounted_dr = X_Running_Total_Accounted_Dr,
running_total_accounted_cr = X_Running_Total_Accounted_Cr,
average_journal_flag = X_Average_Journal_Flag,
last_update_date = X_Last_Update_Date,
last_updated_by = X_Last_Updated_By,
last_update_login = X_Last_Update_Login
WHERE rowid = X_rowid;
END Update_Row;
PROCEDURE Insert_Row(X_Rowid IN OUT NOCOPY VARCHAR2,
X_Je_Batch_Id IN OUT NOCOPY NUMBER,
X_Last_Update_Date DATE,
X_Last_Updated_By NUMBER,
X_Name VARCHAR2,
X_Chart_of_Accounts_ID NUMBER,
X_Period_Set_Name VARCHAR2,
X_Accounted_Period_Type VARCHAR2,
X_Status VARCHAR2,
X_Status_Verified VARCHAR2,
X_Actual_Flag VARCHAR2,
X_Default_Effective_Date DATE,
X_Creation_Date DATE,
X_Created_By NUMBER,
X_Last_Update_Login NUMBER,
X_Status_Reset_Flag VARCHAR2,
X_Default_Period_Name VARCHAR2,
X_Unique_Date VARCHAR2,
X_Earliest_Postable_Date DATE,
X_Posted_Date DATE,
X_Date_Created DATE,
X_Description VARCHAR2,
X_Control_Total NUMBER,
X_Running_Total_Dr NUMBER,
X_Running_Total_Cr NUMBER,
X_Running_Total_Accounted_Dr NUMBER,
X_Running_Total_Accounted_Cr NUMBER,
X_Average_Journal_Flag VARCHAR2,
X_Org_Id NUMBER,
X_Attribute1 VARCHAR2,
X_Attribute2 VARCHAR2,
X_Attribute3 VARCHAR2,
X_Attribute4 VARCHAR2,
X_Attribute5 VARCHAR2,
X_Attribute6 VARCHAR2,
X_Attribute7 VARCHAR2,
X_Attribute8 VARCHAR2,
X_Attribute9 VARCHAR2,
X_Attribute10 VARCHAR2,
X_Context VARCHAR2,
X_Budgetary_Control_Status VARCHAR2,
X_Approval_Status_Code VARCHAR2,
X_Posting_Run_Id NUMBER,
X_Request_Id NUMBER,
X_Packet_Id NUMBER,
X_Ussgl_Transaction_Code VARCHAR2,
X_Context2 VARCHAR2,
X_Unreservation_Packet_Id NUMBER,
X_Global_Attribute_Category VARCHAR2,
X_Global_Attribute1 VARCHAR2,
X_Global_Attribute2 VARCHAR2,
X_Global_Attribute3 VARCHAR2,
X_Global_Attribute4 VARCHAR2,
X_Global_Attribute5 VARCHAR2,
X_Global_Attribute6 VARCHAR2,
X_Global_Attribute7 VARCHAR2,
X_Global_Attribute8 VARCHAR2,
X_Global_Attribute9 VARCHAR2,
X_Global_Attribute10 VARCHAR2,
X_Global_Attribute11 VARCHAR2,
X_Global_Attribute12 VARCHAR2,
X_Global_Attribute13 VARCHAR2,
X_Global_Attribute14 VARCHAR2,
X_Global_Attribute15 VARCHAR2,
X_Global_Attribute16 VARCHAR2,
X_Global_Attribute17 VARCHAR2,
X_Global_Attribute18 VARCHAR2,
X_Global_Attribute19 VARCHAR2,
X_Global_Attribute20 VARCHAR2
) IS
CURSOR C IS SELECT rowid FROM GL_JE_BATCHES
WHERE je_batch_id = X_Je_Batch_Id;
SELECT 'Y'
INTO has_je
FROM gl_je_headers
WHERE je_batch_id = X_Je_Batch_Id
AND rownum = 1;
INSERT INTO GL_JE_BATCHES(
je_batch_id,
last_update_date,
last_updated_by,
name,
chart_of_accounts_id,
period_set_name,
accounted_period_type,
status,
status_verified,
actual_flag,
default_effective_date,
creation_date,
created_by,
last_update_login,
status_reset_flag,
default_period_name,
unique_date,
earliest_postable_date,
posted_date,
date_created,
description,
control_total,
running_total_dr,
running_total_cr,
running_total_accounted_dr,
running_total_accounted_cr,
average_journal_flag,
org_id,
attribute1,
attribute2,
attribute3,
attribute4,
attribute5,
attribute6,
attribute7,
attribute8,
attribute9,
attribute10,
context,
budgetary_control_status,
approval_status_code,
posting_run_id,
request_id,
packet_id,
ussgl_transaction_code,
context2,
unreservation_packet_id,
Global_Attribute_Category,
Global_Attribute1,
Global_Attribute2,
Global_Attribute3,
Global_Attribute4,
Global_Attribute5,
Global_Attribute6,
Global_Attribute7,
Global_Attribute8,
Global_Attribute9,
Global_Attribute10,
Global_Attribute11,
Global_Attribute12,
Global_Attribute13,
Global_Attribute14,
Global_Attribute15,
Global_Attribute16,
Global_Attribute17,
Global_Attribute18,
Global_Attribute19,
Global_Attribute20
) VALUES (
X_Je_Batch_Id,
X_Last_Update_Date,
X_Last_Updated_By,
X_Name,
X_chart_of_accounts_id,
X_period_set_name,
X_accounted_period_type,
X_Status,
X_Status_Verified,
X_Actual_Flag,
X_Default_Effective_Date,
X_Creation_Date,
X_Created_By,
X_Last_Update_Login,
X_Status_Reset_Flag,
X_Default_Period_Name,
X_Unique_Date,
X_Earliest_Postable_Date,
X_Posted_Date,
X_Date_Created,
X_Description,
X_Control_Total,
X_Running_Total_Dr,
X_Running_Total_Cr,
X_Running_Total_Accounted_Dr,
X_Running_Total_Accounted_Cr,
X_Average_Journal_Flag,
X_Org_Id,
X_Attribute1,
X_Attribute2,
X_Attribute3,
X_Attribute4,
X_Attribute5,
X_Attribute6,
X_Attribute7,
X_Attribute8,
X_Attribute9,
X_Attribute10,
X_Context,
X_Budgetary_Control_Status,
X_Approval_Status_Code,
X_Posting_Run_Id,
X_Request_Id,
X_Packet_Id,
X_Ussgl_Transaction_Code,
X_Context2,
X_Unreservation_Packet_Id,
X_Global_Attribute_Category,
X_Global_Attribute1,
X_Global_Attribute2,
X_Global_Attribute3,
X_Global_Attribute4,
X_Global_Attribute5,
X_Global_Attribute6,
X_Global_Attribute7,
X_Global_Attribute8,
X_Global_Attribute9,
X_Global_Attribute10,
X_Global_Attribute11,
X_Global_Attribute12,
X_Global_Attribute13,
X_Global_Attribute14,
X_Global_Attribute15,
X_Global_Attribute16,
X_Global_Attribute17,
X_Global_Attribute18,
X_Global_Attribute19,
X_Global_Attribute20
);
END Insert_Row;
SELECT *
FROM GL_JE_BATCHES
WHERE rowid = X_Rowid
FOR UPDATE of Je_Batch_Id NOWAIT;
FND_MESSAGE.SET_NAME('FND', 'FORM_RECORD_DELETED');
PROCEDURE Update_Row(X_Rowid VARCHAR2,
X_Je_Batch_Id NUMBER,
X_Last_Update_Date DATE,
X_Last_Updated_By NUMBER,
X_Name VARCHAR2,
X_Chart_of_Accounts_ID NUMBER,
X_Period_Set_Name VARCHAR2,
X_Accounted_Period_Type VARCHAR2,
X_Status VARCHAR2,
X_Status_Verified VARCHAR2,
X_Actual_Flag VARCHAR2,
X_Default_Effective_Date DATE,
X_Last_Update_Login NUMBER,
X_Status_Reset_Flag VARCHAR2,
X_Default_Period_Name VARCHAR2,
X_Unique_Date VARCHAR2,
X_Earliest_Postable_Date DATE,
X_Posted_Date DATE,
X_Date_Created DATE,
X_Description VARCHAR2,
X_Control_Total NUMBER,
X_Running_Total_Dr NUMBER,
X_Running_Total_Cr NUMBER,
X_Running_Total_Accounted_Dr NUMBER,
X_Running_Total_Accounted_Cr NUMBER,
X_Average_Journal_Flag VARCHAR2,
X_Attribute1 VARCHAR2,
X_Attribute2 VARCHAR2,
X_Attribute3 VARCHAR2,
X_Attribute4 VARCHAR2,
X_Attribute5 VARCHAR2,
X_Attribute6 VARCHAR2,
X_Attribute7 VARCHAR2,
X_Attribute8 VARCHAR2,
X_Attribute9 VARCHAR2,
X_Attribute10 VARCHAR2,
X_Context VARCHAR2,
X_Budgetary_Control_Status VARCHAR2,
X_Approval_Status_Code IN OUT NOCOPY VARCHAR2,
X_Posting_Run_Id NUMBER,
X_Request_Id NUMBER,
X_Packet_Id NUMBER,
X_Ussgl_Transaction_Code VARCHAR2,
X_Context2 VARCHAR2,
X_Unreservation_Packet_Id NUMBER,
Update_Effective_Date_Flag VARCHAR2,
Update_Approval_Stat_Flag VARCHAR2,
X_Global_Attribute_Category VARCHAR2,
X_Global_Attribute1 VARCHAR2,
X_Global_Attribute2 VARCHAR2,
X_Global_Attribute3 VARCHAR2,
X_Global_Attribute4 VARCHAR2,
X_Global_Attribute5 VARCHAR2,
X_Global_Attribute6 VARCHAR2,
X_Global_Attribute7 VARCHAR2,
X_Global_Attribute8 VARCHAR2,
X_Global_Attribute9 VARCHAR2,
X_Global_Attribute10 VARCHAR2,
X_Global_Attribute11 VARCHAR2,
X_Global_Attribute12 VARCHAR2,
X_Global_Attribute13 VARCHAR2,
X_Global_Attribute14 VARCHAR2,
X_Global_Attribute15 VARCHAR2,
X_Global_Attribute16 VARCHAR2,
X_Global_Attribute17 VARCHAR2,
X_Global_Attribute18 VARCHAR2,
X_Global_Attribute19 VARCHAR2,
X_Global_Attribute20 VARCHAR2
) IS
current_average_journal VARCHAR2(1);
SELECT 'Y'
INTO has_je
FROM gl_je_headers
WHERE je_batch_id = X_Je_Batch_Id
AND rownum = 1;
IF (Update_Effective_Date_Flag = 'Y') THEN
GL_JE_HEADERS_PKG.change_effective_date(X_Je_Batch_Id,
X_Default_Effective_Date);
IF (Update_Approval_Stat_Flag = 'Y') THEN
SELECT approval_status_code
INTO X_Approval_Status_Code
FROM gl_je_batches
WHERE rowid = X_RowId;
ELSIF (Update_Approval_Stat_Flag = 'D') THEN
-- If a journal was deleted, the batch shouldn't have been
-- posted or approved, but check anyway.
IF ( (X_status <> 'P')
AND (X_Approval_Status_Code <> 'A')
) THEN
IF (gl_je_batches_pkg.needs_approval(X_Je_Batch_Id)) THEN
IF (X_Approval_Status_Code = 'Z') THEN
X_Approval_Status_Code := 'R';
UPDATE GL_JE_BATCHES
SET
je_batch_id = X_Je_Batch_Id,
last_update_date = X_Last_Update_Date,
last_updated_by = X_Last_Updated_By,
name = X_Name,
chart_of_accounts_id = X_Chart_of_Accounts_id,
period_set_name = X_Period_Set_Name,
accounted_period_type = X_Accounted_Period_Type,
status = X_Status,
status_verified = X_Status_Verified,
actual_flag = X_Actual_Flag,
default_effective_date = X_Default_Effective_Date,
last_update_login = X_Last_Update_Login,
status_reset_flag = X_Status_Reset_Flag,
default_period_name = X_Default_Period_Name,
unique_date = X_Unique_Date,
earliest_postable_date = X_Earliest_Postable_Date,
date_created = X_Date_Created,
description = X_Description,
control_total = X_Control_Total,
running_total_dr = X_Running_Total_Dr,
running_total_cr = X_Running_Total_Cr,
running_total_accounted_dr = X_Running_Total_Accounted_Dr,
running_total_accounted_cr = X_Running_Total_Accounted_Cr,
average_journal_flag = X_Average_Journal_Flag,
attribute1 = X_Attribute1,
attribute2 = X_Attribute2,
attribute3 = X_Attribute3,
attribute4 = X_Attribute4,
attribute5 = X_Attribute5,
attribute6 = X_Attribute6,
attribute7 = X_Attribute7,
attribute8 = X_Attribute8,
attribute9 = X_Attribute9,
attribute10 = X_Attribute10,
context = X_Context,
budgetary_control_status = X_Budgetary_Control_Status,
approval_status_code = X_Approval_Status_Code,
posting_run_id = X_Posting_Run_Id,
request_id = X_Request_Id,
packet_id = X_Packet_Id,
ussgl_transaction_code = X_Ussgl_Transaction_Code,
context2 = X_Context2,
unreservation_packet_id = X_Unreservation_Packet_Id,
global_attribute_category = X_Global_Attribute_Category,
global_attribute1 = X_Global_Attribute1,
global_attribute2 = X_Global_Attribute2,
global_attribute3 = X_Global_Attribute3,
global_attribute4 = X_Global_Attribute4,
global_attribute5 = X_Global_Attribute5,
global_attribute6 = X_Global_Attribute6,
global_attribute7 = X_Global_Attribute7,
global_attribute8 = X_Global_Attribute8,
global_attribute9 = X_Global_Attribute9,
global_attribute10 = X_Global_Attribute10,
global_attribute11 = X_Global_Attribute11,
global_attribute12 = X_Global_Attribute12,
global_attribute13 = X_Global_Attribute13,
global_attribute14 = X_Global_Attribute14,
global_attribute15 = X_Global_Attribute15,
global_attribute16 = X_Global_Attribute16,
global_attribute17 = X_Global_Attribute17,
global_attribute18 = X_Global_Attribute18,
global_attribute19 = X_Global_Attribute19,
global_attribute20 = X_Global_Attribute20
WHERE rowid = X_rowid;
END Update_Row;
PROCEDURE Delete_Row(X_Rowid VARCHAR2, Je_Batch_Id NUMBER) IS
bc_status VARCHAR2(1);
SELECT budgetary_control_status,
approval_status_code,
status,
request_id
INTO bc_status, approval_status,
batch_status, request_id
FROM gl_je_batches
WHERE rowid = X_Rowid;
gl_je_headers_pkg.delete_headers(Je_Batch_Id);
DELETE FROM GL_JE_BATCHES
WHERE rowid = X_Rowid;
END Delete_Row;